<?php

require_once ("connection_sql.php");

$sql = "delete from ledger where l_flag = 'INV' and l_date <='2017-12-18' and comcode = 'B'";
$result = $conn->query($sql);

$sql = "delete from ledger where l_flag = 'GRN' and l_date <='2017-12-18' and comcode = 'B'";
$result = $conn->query($sql);

$sql = "delete from ledger where l_flag = 'CNT' and l_date <='2017-12-18' and comcode = 'B'";
$result = $conn->query($sql);

$sql = "delete from ledger where l_flag = 'REC' and l_date <='2017-12-18' and comcode = 'B'";
$result = $conn->query($sql);


$sql = "Select month(SDATE) AS Mon,C_CODE ,SUM(GRAND_TOT) AS TOT,type  from s_salma where sdate>='2017-04-01' and sdate <='2017-12-18' and cancell='0' and company = 'B' group by month(sdate) , c_code,type";

foreach ($conn->query($sql) as $row) {
  

$gtot = $row['TOT'];
$vat = $gtot-($row['TOT']/1.15);
$ntot = $row['TOT'] -$vat;


$month = "2017-" . $row['Mon'] . "-01";

$lrefno = $row['C_CODE'] . "-" . $month;



$user[] = "('" . $lrefno . "','" . $month . "','4002','" . $ntot . "','INV','CRE','2017','B','" . $row['C_CODE'] . "','" . $row['C_CODE'] . " " . $row['type']  . "-NET SALES')";
$user[] = "('" . $lrefno . "','" . $month . "','6004','" . $vat . "','INV','CRE','2017','B','" . $row['C_CODE'] . "','" . $row['C_CODE'] . " " . $row['type']  . "-SALES')";
$user[] = "('" . $lrefno . "','" . $month . "','1013','" . $gtot . "','INV','DEB','2017','B','" . $row['C_CODE'] . "','" . $row['C_CODE'] . " " . $row['type']  . "-SALES')";



}

$sql = "Select month(SDATE) AS Mon,CUSCODE ,SUM(AMOUNT) AS TOT,trn_type  from c_bal where sdate>='2017-04-01' and sdate <='2017-12-18' and cancell='0' and company = 'B' and trn_type<>'ARN' and trn_type <> 'REC' group by month(sdate) , CUSCODE,trn_type";
  
foreach ($conn->query($sql) as $row) {


$gtot = $row['TOT'];
$vat = $gtot-($row['TOT']/1.15);
$ntot = $row['TOT'] -$vat;


$month = "2017-" . $row['Mon'] . "-01";

$lrefno = $row['CUSCODE'] . "-" . $month;


if ($row['trn_type']=="CNT") {
$user1[] = "('" . $lrefno . "','" . $month . "','5082','" . $ntot . "','CNT','DEB','2017','B','" . $row['CUSCODE'] . "','" . $row['CUSCODE']  . "-NET CRED.')";
$user1[] = "('" . $lrefno . "','" . $month . "','6004','" . $vat . "','CNT','DEB','2017','B','" . $row['CUSCODE'] . "','" . $row['CUSCODE'] . "-CRED.')";
$user1[] = "('" . $lrefno . "','" . $month . "','1013','" . $gtot . "','CNT','CRE','2017','B','" . $row['CUSCODE'] . "','" . $row['CUSCODE'] . "-CRED.')";
}

if ($row['trn_type']=="GRN") {
$user2[] = "('" . $lrefno . "','" . $month . "','5081','" . $ntot . "','GRN','DEB','2017','B','" . $row['CUSCODE'] . "','" . $row['CUSCODE']  . "-NET SALES RTN')";
$user2[] = "('" . $lrefno . "','" . $month . "','6004','" . $vat . "','GRN','DEB','2017','B','" . $row['CUSCODE'] . "','" . $row['CUSCODE'] . "-SALES RTN')";
$user2[] = "('" . $lrefno . "','" . $month . "','1013','" . $gtot . "','GRN','CRE','2017','B','" . $row['CUSCODE'] . "','" . $row['CUSCODE'] . "-SALES RTN')";    
}


}



$sql = "Select *  from s_crec where ca_date>='2017-04-01' and ca_date <='2017-12-18' and cancell='0' and company = 'B' order by ca_date,ca_refno";
  
foreach ($conn->query($sql) as $row) {

$month = $row['CA_DATE'];

$lrefno =  $row['CA_REFNO'];

if ($row['CA_CASSH']>0) {
$user3[] = "('" . $lrefno . "','" . $month . "','1010','" . $row['CA_CASSH'] . "','REC','DEB','2017','B','" . $row['CA_CODE'] . "','" . $row['CA_CODE']  . " " . $row['pay_type'] . "')";
$user3[] = "('" . $lrefno . "','" . $month . "','1013','" . $row['CA_CASSH'] . "','REC','CRE','2017','B','" . $row['CA_CODE'] . "','" . $row['CA_CODE']  . " " . $row['pay_type'] . "')";
}

if ($row['CA_AMOUNT']-$row['CA_CASSH']>1) {
$user4[] = "('" . $lrefno . "','" . $month . "','1035','" . ($row['CA_AMOUNT']-$row['CA_CASSH']) . "','REC','DEB','2017','B','" . $row['CA_CODE'] . "','" . $row['CA_CODE']  . " " . $row['pay_type'] . "')";
$user4[] = "('" . $lrefno . "','" . $month . "','1013','" . ($row['CA_AMOUNT']-$row['CA_CASSH']) . "','REC','CRE','2017','B','" . $row['CA_CODE'] . "','" . $row['CA_CODE'] . " " . $row['pay_type'] . "')";
}

 

}


 try {
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->beginTransaction();
        
$sqli = "insert into ledger (l_refno,l_date,l_code,l_amount,l_flag,l_flag1,acyear,comcode,c_remarks,l_lmem)values " . implode($user, ",");
 $result = $conn->query($sqli);

$sqli1 = "insert into ledger (l_refno,l_date,l_code,l_amount,l_flag,l_flag1,acyear,comcode,c_remarks,l_lmem)values " . implode($user1, ",");
 $result = $conn->query($sqli1);


$sqli2 = "insert into ledger (l_refno,l_date,l_code,l_amount,l_flag,l_flag1,acyear,comcode,c_remarks,l_lmem)values " . implode($user2, ",");
 $result = $conn->query($sqli2);
 
$sqli2 = "insert into ledger (l_refno,l_date,l_code,l_amount,l_flag,l_flag1,acyear,comcode,c_remarks,l_lmem)values " . implode($user3, ",");
 $result = $conn->query($sqli2); 

$sqli2 = "insert into ledger (l_refno,l_date,l_code,l_amount,l_flag,l_flag1,acyear,comcode,c_remarks,l_lmem)values " . implode($user4, ",");
 $result = $conn->query($sqli2); 
 
 
 $conn->commit();
 
} catch (Exception $e) {
        $conn->rollBack();
        echo $e;
    }
    
    
    
?>