
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<?php

session_start();
date_default_timezone_set('Asia/Colombo');

if ($_SESSION["CURRENT_USER"] == "") { 
    exit('Please Logging Again..');
} 
if ($_SESSION['company'] == "") { 
    exit('Please Logging Again..');
}
?>
<html>
<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">


    <title>LEDGER REPORT</title>

    <style>
        .center {
            text-align: center;
        }

        .right {
            text-align: right;
        }

        .tb  {
           width: 1000px;
           border: 1px solid black;


           border-collapse: collapse;
       }
        
       .tb td  {

           border-top: 1px solid black;
           border-left: 1px solid black;
           font-size:18px;

           border-collapse: collapse;
       }
       .tb th  {

           border-top: 1px solid black;
           border-left: 1px solid black;


           border-collapse: collapse;
       }
       .tb2  {
           
           border: 1px solid black;


           border-collapse: collapse;
       }
       .tb2 td  {

           border-top: 1px solid black;
           border-left: 1px solid black;
           font-size:18px;

           border-collapse: collapse;
       }
       .tb2 th  {

           border-top: 1px solid black;
           border-left: 1px solid black;


           border-collapse: collapse;
       }

       .tb1 {

           border: 1px solid #000000;
       }
 

   </style>
<script language="javascript" type="text/javascript">
        <!--
            /****************************************************
             Author: Eric King
             Url: http://redrival.com/eak/index.shtml
             This script is free to use as long as this info is left in
             Featured on Dynamic Drive script library (http://www.dynamicdrive.com)
             ****************************************************/
             var win = null;
             function NewWindow(mypage, myname, w, h, scroll, pos) {
                if (pos == "random") {
                    LeftPosition = (screen.width) ? Math.floor(Math.random() * (screen.width - w)) : 100;
                    TopPosition = (screen.height) ? Math.floor(Math.random() * ((screen.height - h) - 75)) : 100;
                }
                if (pos == "center") {
                    LeftPosition = (screen.width) ? (screen.width - w) / 2 : 100;
                    TopPosition = (screen.height) ? (screen.height - h) / 2 : 100;
                } else if ((pos != "center" && pos != "random") || pos == null) {
                    LeftPosition = 0;
                    TopPosition = 20
                }
                settings = 'width=' + w + ',height=' + h + ',top=' + TopPosition + ',left=' + LeftPosition + ',scrollbars=' + scroll + ',location=no,directories=no,status=no,menubar=no,toolbar=no,resizable=no';
                win = window.open(mypage, myname, settings);
            }
            // -->
        </script>
</head>
<?php
require_once ("connection_sql.php");
 
function ac_year($date) { 
     $month1 = date("m", strtotime($date));
     $month1_y = date("Y", strtotime($date));
    
     if ($month1<=3) {
         $ac_year = $month1_y -1;
     } else {
         $ac_year = $month1_y;
     } 
     return $ac_year; 
}
?>
<body>
    <center>
    <table width="1000px;" cellspacing="0" border="0">

        <tr>
            <th class="center"  colspan=5 >COMPANY -<?php 
            if($_GET['company'] !="ALL"){ 
            $sql_invpara = "SELECT * from invpara where flag='".$_GET['company']."'"; 
            $result_invpara = $conn->query($sql_invpara);
            $row_invpara = $result_invpara->fetch(); 
            }else{
                 echo "ALL"; 
            }
           ?></th>
        </tr> 
        
        <tr> 
            <th class='center' colspan='5'> <?php echo $_GET['type']?> LEDGER - (<?php echo date('Y-m-d H:i:s');?>)</th> 
        </tr>
         
        <tr> 
            <th class='center' colspan='5'> <?php echo $_GET['dtfrom']?> To - <?php echo $_GET['dtfrom']?></th> 
        </tr>
       
        
        <tr>

            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
        </tr>
       
    </table>
    
    
<!--==================================================================================-->
   <?php if($_GET['type'] == "Sales"){?>
                <table class="tb" width="1000px;" >
                <tr> 
                   <th width="30px;">NO</th> 
                    <th width="100px;">REFNO</th>  
                    <th width="200px;">CUSTOMER</th> 
                    <th width="100px;">AMOUNT</th>  
                </tr>
                <?php
                 
                $sqlmain = "Select SDATE,REF_NO,CUS_NAME,C_CODE,company,GRAND_TOT,GST,VAT from s_salma  where CANCELL='0'  AND SDATE >='" . $_GET["dtfrom"] . "' and SDATE <='" . $_GET["dtto"] . "' AND TRN_TYPE='INV'";
                  
                if($_GET['company'] !="ALL"){
                     $sqlmain.=" and company='".$_GET['company']."'"; 
                }
                $sqlmain.=" order by SDATE,company,C_CODE";   
                $i=1;   
                $x=1;   
                foreach ($conn->query($sqlmain) as $rowmain) {
                    
                    $docname='INVOICE'; 
                    $ldate = $rowmain["SDATE"]; 
                    $customername=$rowmain["CUS_NAME"]; 
                    $customercode=$rowmain["C_CODE"]; 
                    $recno=$rowmain["REF_NO"];  
                    $company=$rowmain["company"]; 
                    $ayear = ac_year($rowmain["SDATE"]); 
                    $l_lmem = $docname ."-" . trim($customername);
                    $mvatrate=$rowmain["GST"];  
                    $vat=$rowmain["VAT"];  
                    $gtot=$rowmain["GRAND_TOT"];
                    
                     
                    $vatAmount = $gtot*$mvatrate/(100+$mvatrate);
                    $vatAmount = number_format($vatAmount, "2", ".", "");
                    $orgAmount = $gtot - $vatAmount;
                    
                    $sql_ledger="delete from a_ledger where L_REFNO='".$recno."'";
			        $result_ledger = $conn->query($sql_ledger);   
                      
                    // ==========================================================================================================================================================================================================
                    if($vat == 0){  
                        $sql_post = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'SALE'";    
                        $result_post = $conn->query($sql_post);
                        $row_post = $result_post->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post["L_CODE"].'-'.$company)."' ,'" . $l_lmem . "', '" . $orgAmount . "', 'INV', '" . $row_post['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    } 
                    
                    if($vat == 1){ 
                        $sql_post1 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'VAT CONTROL'";    
                        $result_post1 = $conn->query($sql_post1); 
                        $row_post1 = $result_post1->fetch();
                         
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE,L_LMEM, L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post1["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $vatAmount . "', 'INV', '" . $row_post1['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);  
                        
                        $sql_post2 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'VAT SALE'";    
                        $result_post2 = $conn->query($sql_post2);
                        $row_post2 = $result_post2->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post2["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $orgAmount . "', 'INV', '" . $row_post2['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    }
                        
                    if($vat == 2){ 
                        $sql_post3 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'SVAT CONTROL'";    
                        $result_post3 = $conn->query($sql_post3); 
                        $row_post3 = $result_post3->fetch();
                         
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE,L_LMEM, L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post3["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $vatAmount . "', 'INV', '" . $row_post3['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                        
                        $sql_post4 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'SVAT SALE'";    
                        $result_post4 = $conn->query($sql_post4);
                        $row_post4 = $result_post4->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post4["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $orgAmount . "', 'INV', '" . $row_post4['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    }
                     
                    
                    // ==========================================================================================================================================================================================================
                    
                    $sql_post5 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'ACCOUNT RECEIVABLE'";    
                    $result_post5 = $conn->query($sql_post5);
                    $row_post5 = $result_post5->fetch();
                    
                    $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                    ('" . $recno . "', '" . $ldate . "', '" . ($row_post5["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $gtot . "', 'INV', '" . $row_post5['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                    $result_ledger = $conn->query($sql_ledger);   
                    // ==========================================================================================================================================================================================================
                   
                   if($invdate !=$ldate){
                        echo "<tr>  
                            <td colspan='4' align='left'><b>" . $ldate . "</b></td>  
                        </tr>";
                        $x=1;
                   }
               
                echo "<tr> 
                        <td width='30px;'>".$x."</td> 
                        <td width='100px;' align='left'>".$recno."</th>  
                        <td width='200px;' align='left'>" . $customername . "</td> 
                        <td width='100px;' align='right'> " . number_format($gtot,2) . "</td>  
                </tr>";
                    $i =$i+ 1;
                    $x =$x+ 1;
                    $totgtot=$totgtot+$gtot;
                    $invdate =$ldate;
                }
                echo "<h1> ( Updated ".($i-1)." Records....  Total Is - ".number_format($totgtot,2).' )</h1>';
               
                ?>
        </table>
    
 <?php }  ?>
    <!--===================================================================================-->
    <?php if($_GET['type'] == "CRN/PAY/GRN/DGRN"){?>
                <table class="tb" width="1000px;" >
                <tr> 
                   <th width="30px;">NO</th> 
                    <th width="100px;">REFNO</th>  
                    <th width="200px;">CUSTOMER</th> 
                    <th width="100px;">AMOUNT</th>  
                </tr>
                <?php
                 
                $sqlmain = "Select SDATE,REFNO,CUSCODE,company,AMOUNT,vatrate,VAT,trn_type from c_bal  where CANCELL='0'  AND SDATE >='" . $_GET["dtfrom"] . "' and SDATE <='" . $_GET["dtto"] . "' and trn_type !='ARN'";
                  
                if($_GET['company'] !="ALL"){
                     $sqlmain.=" and company='".$_GET['company']."'"; 
                }
                $sqlmain.=" order by SDATE,company,CUSCODE";    
                $i=1;   
                $x=1;   
                foreach ($conn->query($sqlmain) as $rowmain) {
                    $sql_ven = "select * from vendor where CODE = '".$rowmain["CUSCODE"]."'  ";    
                    $result_ven = $conn->query($sql_ven);
                    $row_ven = $result_ven->fetch();
                    
                    $trntype=$rowmain["trn_type"]; 
                    if($trntype=="GRN"){
                        $docname='SALES RETURN';
                    }else if($trntype=="DGRN"){
                        $docname='DEFECTIVE';
                    }else if($trntype=="REC"){
                        $docname='RECEIPT';
                    }else if($trntype=="PAY"){
                        $docname='ADVANCE';
                    }else{
                        $docname='OTHER';
                    } 
                    $ldate = $rowmain["SDATE"]; 
                    $customername=$row_ven["NAME"]; 
                    $customercode=$rowmain["CUSCODE"]; 
                    $recno=$rowmain["REFNO"];  
                    $company=$rowmain["company"]; 
                    $ayear = ac_year($rowmain["SDATE"]); 
                    $l_lmem = $docname ."-" . trim($customername);
                    $mvatrate=$rowmain["vatrate"];  
                    $vat=$rowmain["VAT"];  
                    $gtot=$rowmain["AMOUNT"];
                    
                    
                    
                    
                    
                    $vatAmount = $gtot*$mvatrate/(100+$mvatrate);
                    $vatAmount = number_format($vatAmount, "2", ".", "");
                    $orgAmount = $gtot - $vatAmount;
                    
                    $sql_ledger="delete from a_ledger where L_REFNO='".$recno."'"; 
			        $result_ledger = $conn->query($sql_ledger);   
                   
                    // ==========================================================================================================================================================================================================
                    if($vat == 0){  
                        $sql_post = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'SALE'";   
                        $result_post = $conn->query($sql_post);
                        $row_post = $result_post->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $orgAmount . "', '".$trntype."', '" . $row_post['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    } 
                    
                    if($vat == 1){ 
                        $sql_post1 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'VAT CONTROL'";    
                        $result_post1 = $conn->query($sql_post1); 
                        $row_post1 = $result_post1->fetch();
                         
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE,L_LMEM, L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post1["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $vatAmount . "', '".$trntype."', '" . $row_post1['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);  
                        
                        $sql_post2 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'VAT SALE'";    
                        $result_post2 = $conn->query($sql_post2);
                        $row_post2 = $result_post2->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post2["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $orgAmount . "', '".$trntype."', '" . $row_post2['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    }
                        
                    if($vat == 2){ 
                        $sql_post3 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'SVAT CONTROL'";    
                        $result_post3 = $conn->query($sql_post3); 
                        $row_post3 = $result_post3->fetch();
                         
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE,L_LMEM, L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post3["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $vatAmount . "', '".$trntype."', '" . $row_post3['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                        
                        $sql_post4 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'SVAT SALE'";    
                        $result_post4 = $conn->query($sql_post4);
                        $row_post4 = $result_post4->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post4["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $orgAmount . "', '".$trntype."', '" . $row_post4['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    }
                     
                    
                    // ==========================================================================================================================================================================================================
                    
                    $sql_post5 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'ACCOUNT RECEIVABLE'";    
                    $result_post5 = $conn->query($sql_post5);
                    $row_post5 = $result_post5->fetch();
                    
                    $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                    ('" . $recno . "', '" . $ldate . "', '" . ($row_post5["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $gtot . "', '".$trntype."', '" . $row_post5['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                    $result_ledger = $conn->query($sql_ledger);   
                    // ==========================================================================================================================================================================================================
                   
                   if($invdate !=$ldate){
                        echo "<tr>  
                            <td colspan='4' align='left'><b>" . $ldate . "</b></td>  
                        </tr>";
                        $x=1;
                   }
               
                echo "<tr> 
                        <td width='30px;'>".$x."</td> 
                        <td width='100px;' align='left'>".$recno."</th>  
                        <td width='200px;' align='left'>" . $customername . "</td> 
                        <td width='100px;' align='right'> " . number_format($gtot,2) . "</td>  
                </tr>";
                    $i =$i+ 1;
                    $x =$x+ 1;
                    $totgtot=$totgtot+$gtot;
                    $invdate =$ldate;
                }
                echo "<h1> ( Updated ".($i-1)." Records....  Total Is - ".number_format($totgtot,2).' )</h1>';
               
                ?>
        </table>
    
 <?php }  ?>
 <!-- ************************************************************************** -->   
 <?php if($_GET['type'] == "Purchase"){?>
                <table class="tb" width="1000px;" >
                <tr> 
                   <th width="30px;">NO</th> 
                    <th width="100px;">REFNO</th>  
                    <th width="200px;">CUSTOMER</th> 
                    <th width="100px;">AMOUNT</th>  
                </tr>
                <?php
                 
                $sqlmain = "Select SDATE,REFNO,SUP_CODE,company,AMOUNT,VATRATE,VAT,TYPE from s_purmas  where CANCEL='0'  AND SDATE >='" . $_GET["dtfrom"] . "' and SDATE <='" . $_GET["dtto"] . "'";
                  
                if($_GET['company'] !="ALL"){
                     $sqlmain.=" and company='".$_GET['company']."'"; 
                }
                $sqlmain.=" order by SDATE,company,SUP_CODE";    
                $i=1;   
                $x=1;   
                foreach ($conn->query($sqlmain) as $rowmain) {
                    $sql_ven = "select * from vendor where CODE = '".$rowmain["SUP_CODE"]."'  ";    
                    $result_ven = $conn->query($sql_ven);
                    $row_ven = $result_ven->fetch();
                     
                    $docname='PURCHASE';
                   
                    
                    $ldate = $rowmain["SDATE"]; 
                    $customername=$row_ven["NAME"]; 
                    $customercode=$rowmain["SUP_CODE"]; 
                    $recno=$rowmain["REFNO"];  
                    $company=$rowmain["company"]; 
                    $ayear = ac_year($rowmain["SDATE"]); 
                    $l_lmem = $docname ."-" . trim($customername);
                    $mvatrate=$rowmain["VATRATE"];  
                    $vat=$rowmain["VAT"];  
                    $gtot=$rowmain["AMOUNT"];
                    $type=$rowmain["TYPE"];
                    
                    if($rowmain["TYPE"]=="Import"){
                        $action='PURCHASE ACCOUNT IMPORT';
                    }else{
                         $action='PURCHASE ACCOUNT LOCAL';
                    }
                    
                    
                    
                    $vatAmount = $gtot*$mvatrate/(100+$mvatrate);
                    $vatAmount = number_format($vatAmount, "2", ".", "");
                    $orgAmount = $gtot - $vatAmount;
                    
                    $sql_ledger="delete from a_ledger where L_REFNO='".$recno."'"; 
			        $result_ledger = $conn->query($sql_ledger);   
                   
                    // ==========================================================================================================================================================================================================
                    if($vat == 0){  
                        $sql_post = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = '".$action."'";   
                        $result_post = $conn->query($sql_post);
                        $row_post = $result_post->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $orgAmount . "', 'ARN', '" . $row_post['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    } 
                    
                    if($vat == 1){ 
                        $sql_post1 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'VAT CONTROL'";    
                        $result_post1 = $conn->query($sql_post1); 
                        $row_post1 = $result_post1->fetch();
                         
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE,L_LMEM, L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post1["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $vatAmount . "', 'ARN', '" . $row_post1['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);  
                        
                        $sql_post2 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = '".$action."'";   
                        $result_post2 = $conn->query($sql_post2);
                        $row_post2 = $result_post2->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post2["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $orgAmount . "', 'ARN', '" . $row_post2['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    }
                        
                    if($vat == 2){ 
                        $sql_post3 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'SVAT CONTROL'";    
                        $result_post3 = $conn->query($sql_post3); 
                        $row_post3 = $result_post3->fetch();
                         
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE,L_LMEM, L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post3["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $vatAmount . "', 'ARN', '" . $row_post3['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                        
                        $sql_post4 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = '".$action."'";   
                        $result_post4 = $conn->query($sql_post4);
                        $row_post4 = $result_post4->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post4["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $orgAmount . "', 'ARN', '" . $row_post4['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    }
                     
                    
                    // ==========================================================================================================================================================================================================
                    
                    $sql_post5 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'ACCOUNT PAYABLE'";    
                    $result_post5 = $conn->query($sql_post5);
                    $row_post5 = $result_post5->fetch();
                    
                    $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                    ('" . $recno . "', '" . $ldate . "', '" . ($row_post5["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $gtot . "', 'ARN', '" . $row_post5['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                    $result_ledger = $conn->query($sql_ledger);   
                    // ==========================================================================================================================================================================================================
                   
                   if($invdate !=$ldate){
                        echo "<tr>  
                            <td colspan='4' align='left'><b>" . $ldate . "</b></td>  
                        </tr>";
                        $x=1;
                   }
               
                echo "<tr> 
                        <td width='30px;'>".$x."</td> 
                        <td width='100px;' align='left'>".$recno."</th>  
                        <td width='200px;' align='left'>" . $customername . "</td> 
                        <td width='100px;' align='right'> " . number_format($gtot,2) . "</td>  
                </tr>";
                    $i =$i+ 1;
                    $x =$x+ 1;
                    $totgtot=$totgtot+$gtot;
                    $invdate =$ldate;
                }
                echo "<h1> ( Updated ".($i-1)." Records....  Total Is - ".number_format($totgtot,2).' )</h1>';
               
                ?>
        </table>
    
 <?php }  ?>
       
       
    <!-- ************************************************************************** -->   
 <?php if($_GET['type'] == "Purchase Return"){?>
                <table class="tb" width="1000px;" >
                <tr> 
                   <th width="30px;">NO</th> 
                    <th width="100px;">REFNO</th>  
                    <th width="200px;">CUSTOMER</th> 
                    <th width="100px;">AMOUNT</th>  
                </tr>
                <?php
                 
                $sqlmain = "Select SDATE,REFNO,SUP_CODE,company,AMOUNT,VATRATE,VAT,ORDNO from s_purrmas  where CANCEL='0'  AND SDATE >='" . $_GET["dtfrom"] . "' and SDATE <='" . $_GET["dtto"] . "'";
                  
                if($_GET['company'] !="ALL"){
                     $sqlmain.=" and company='".$_GET['company']."'"; 
                }
                $sqlmain.=" order by SDATE,company,SUP_CODE";    
                $i=1;   
                $x=1;   
                foreach ($conn->query($sqlmain) as $rowmain) {
                    $sql_ven = "select * from vendor where CODE = '".$rowmain["SUP_CODE"]."'  ";    
                    $result_ven = $conn->query($sql_ven);
                    $row_ven = $result_ven->fetch();
                    
                    $sql_pur = "select * from s_purmas where CODE = '".$rowmain["ORDNO"]."'  ";    
                    $result_pur = $conn->query($sql_pur);
                    $row_pur = $result_pur->fetch();
                     
                    $docname='PURCHASE RETURN';
                   
                    
                    $ldate = $rowmain["SDATE"]; 
                    $customername=$row_ven["NAME"]; 
                    $customercode=$rowmain["SUP_CODE"]; 
                    $recno=$rowmain["REFNO"];  
                    $company=$rowmain["company"]; 
                    $ayear = ac_year($rowmain["SDATE"]); 
                    $l_lmem = $docname ."-" . trim($customername);
                    $mvatrate=$rowmain["VATRATE"];  
                    $vat=$rowmain["VAT"];  
                    $gtot=$rowmain["AMOUNT"];
                    $type=$rowmain["TYPE"];
                    
                    if($row_pur["TYPE"]=="Import"){
                        $action='PURCHASE ACCOUNT IMPORT';
                    }else{
                         $action='PURCHASE ACCOUNT LOCAL';
                    }
                    
                    
                    
                    $vatAmount = $gtot*$mvatrate/(100+$mvatrate);
                    $vatAmount = number_format($vatAmount, "2", ".", "");
                    $orgAmount = $gtot - $vatAmount;
                    
                    $sql_ledger="delete from a_ledger where L_REFNO='".$recno."'"; 
			        $result_ledger = $conn->query($sql_ledger);   
                   
                    // ==========================================================================================================================================================================================================
                    if($vat == 0){  
                        $sql_post = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = '".$action."'";   
                        $result_post = $conn->query($sql_post);
                        $row_post = $result_post->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $orgAmount . "', 'ARR', '" . $row_post['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    } 
                    
                    if($vat == 1){ 
                        $sql_post1 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'VAT CONTROL'";    
                        $result_post1 = $conn->query($sql_post1); 
                        $row_post1 = $result_post1->fetch();
                         
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE,L_LMEM, L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post1["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $vatAmount . "', 'ARR', '" . $row_post1['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);  
                        
                        $sql_post2 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = '".$action."'";   
                        $result_post2 = $conn->query($sql_post2);
                        $row_post2 = $result_post2->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post2["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $orgAmount . "', 'ARR', '" . $row_post2['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    }
                        
                    if($vat == 2){ 
                        $sql_post3 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'SVAT CONTROL'";    
                        $result_post3 = $conn->query($sql_post3); 
                        $row_post3 = $result_post3->fetch();
                         
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE,L_LMEM, L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" .($row_post3["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $vatAmount . "', 'ARR', '" . $row_post3['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                        
                        $sql_post4 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = '".$action."'";   
                        $result_post4 = $conn->query($sql_post4);
                        $row_post4 = $result_post4->fetch();
                        
                        $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                        ('" . $recno . "', '" . $ldate . "', '" . ($row_post4["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $orgAmount . "', 'ARR', '" . $row_post4['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                        $result_ledger = $conn->query($sql_ledger);   
                    }
                     
                    
                    // ==========================================================================================================================================================================================================
                    
                    $sql_post5 = "select * from a_gl_posting where DOCNAME = '".$docname."' and ACTION = 'ACCOUNT PAYABLE'";    
                    $result_post5 = $conn->query($sql_post5);
                    $row_post5 = $result_post5->fetch();
                    
                    $sql_ledger = "Insert into a_ledger(L_REFNO, L_DATE, L_CODE, L_LMEM,L_AMOUNT, L_FLAG, L_FLAG1, ACYEAR, COMPANY, C_CODE) Values 
                    ('" . $recno . "', '" . $ldate . "', '" . ($row_post5["L_CODE"].'-'.$company) . "','" . $l_lmem . "', '" . $gtot . "', 'ARR', '" . $row_post5['FLAG'] . "', '".$ayear."', '" . $company . "', '".$customercode."')";
                    $result_ledger = $conn->query($sql_ledger);   
                    // ==========================================================================================================================================================================================================
                   
                   if($invdate !=$ldate){
                        echo "<tr>  
                            <td colspan='4' align='left'><b>" . $ldate . "</b></td>  
                        </tr>";
                        $x=1;
                   }
               
                echo "<tr> 
                        <td width='30px;'>".$x."</td> 
                        <td width='100px;' align='left'>".$recno."</th>  
                        <td width='200px;' align='left'>" . $customername . "</td> 
                        <td width='100px;' align='right'> " . number_format($gtot,2) . "</td>  
                </tr>";
                    $i =$i+ 1;
                    $x =$x+ 1;
                    $totgtot=$totgtot+$gtot;
                    $invdate =$ldate;
                }
                echo "<h1> ( Updated ".($i-1)." Records....  Total Is - ".number_format($totgtot,2).' )</h1>';
               
                ?>
        </table>
    
 <?php }  ?>
 
<!-- ************************************************************************** -->
</center>
</body>

</html>
