<?php
require("includes/check_auth_user.php");
require("includes/dbconnect.php");
include('includes/function.php');

$zdb = $_SESSION["zdbyear_" . $dbname];
$admin = $_SESSION["admin_" . $dbname];

$formname = "Ageing Report";
$grid = "ageingamt.php";
$file = "ageingamt.php";

$acc   = "accmast";

$ztit = $_SESSION["tit_" . $dbname];
$zroot = $_SESSION["zdir"];

$accmast = $acc ;

$billdb = "billsett$ztit";
$zsdate = $_SESSION["zdbyear_" . $zsdate];
$zedate = $_SESSION["zdbyear_" . $zedate];

$abal = 'accmast';
$accbal = $abal ;

$tran = "actran";
$trandb = $tran ;

mysqli_select_db($conn, $zdb);

$compcond = '' ;
//if (!empty($_POST['company'])) {
//    foreach ($_POST['company'] as $check) {
//        $mycheck[$count++] .= " OR `company` = '" . $check . "' ";
//    }
//
//    foreach ($mycheck as $g => $values) {
//        $mcheck .= $values;
//    }
//
//    $compcond = " AND (`company` = '' " . $mcheck . " )";
//}

$custcond = '' ;
if (!empty($_POST["customer"])) {
    $customer = trim($_POST["customer"]);
    $custcond = " AND a.`accode`= '$customer' ";
}


//$partytype = $_POST['partytype'];
//$partytypecond = "";
//if (!empty($partytype)) {
//    $partytypecond = " AND b.`partytype` = '$partytype' ";
//}

//$salesman = $_POST['salesman'];
//$salesmancond = "";
//if (!empty($salesman)) {
//    $salesmancond = " AND b.`salesman` = '$salesman' ";
//}

$mtodate = trim($_POST["tdate"]);
$range = $_POST["range"];

$datecond = " and a.`date`<= '$mtodate' ";

if (isset($_POST["excel"])) {

    $contents .= "Sr., Party, Opening, > 180 Days, 151-180 Days, 121-150 Days, 91-120 Days, 61-90 Days, 31-60 days, < 31 days, Total \n ";

    mysqli_query($conn, "drop temporary table if exists cmain") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE cmain 
                                   SELECT a.vouchno, a.date as vouchdt, a.accode as name, abs(a.amount) as netamt, b.name as partyname
                                          from $trandb a, $accmast b
                                          where a.vouchno != '' AND a.accode = b.accode and a.amount < 0 and a.retdate = '0000-00-00'
                                          and b.grpcode = '010' $datecond $compcond $custcond 
                                   union 
                                   SELECT 0 as vouchno , '0000-00-00' as  vouchdt, a.accode as name, 0 as netamt, b.name as partyname
                                          from $accbal a, $accmast b
                                          where a.accode = b.accode and b.grpcode = '010' $compcond $custcond ") or die(mysqli_error($conn));

    $mainqry = mysqli_query($conn, "SELECT  * from cmain group by name order by partyname") or die(mysqli_error($conn));
    
    $outstanding = 0;

    $ctr = 0;
    $opentot = 0;
    $tot6 = $tot5 = $tot4 = $tot3 = $tot2 = $tot1 = $tot = 0;
    $gbal = 0;

    $date = $mtodate;
    $date1 = date('Y-m-d', strtotime("-30 day", strtotime($mtodate)));
    $date2 = date('Y-m-d', strtotime("-60 day", strtotime($mtodate)));
    $date3 = date('Y-m-d', strtotime("-90 day", strtotime($mtodate)));
    $date4 = date('Y-m-d', strtotime("-120 day", strtotime($mtodate)));
    $date5 = date('Y-m-d', strtotime("-150 day", strtotime($mtodate)));
    $date6 = date('Y-m-d', strtotime("-180 day", strtotime($mtodate)));

    mysqli_query($conn, "drop temporary table if exists actamtp") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE actamtp 
                                SELECT sum(amount) as amtp, accode from $trandb where date <= '$date' and amount > 0 and retdate = '0000-00-00' 
                                $compcond group by accode ") or die(mysqli_error($conn));

    
    mysqli_query($conn, "drop temporary table if exists cmain6") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE cmain6 
                                SELECT sum(netamt) as amt6, name from cmain where vouchdt <= '$date6' $compcond group by name ") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists cmain5") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE cmain5 
                                SELECT sum(netamt) as amt5, name  from cmain where vouchdt > '$date6' and vouchdt <= '$date5' $compcond group by name ") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists cmain4") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE cmain4 
                                SELECT sum(netamt) as amt4, name from cmain where vouchdt > '$date5' and vouchdt <= '$date4' $compcond group by name ") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists cmain3") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE cmain3 
                                SELECT sum(netamt) as amt3, name  from cmain where vouchdt > '$date4' and vouchdt <= '$date3' $compcond group by name ") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists cmain2") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE cmain2 
                                SELECT sum(netamt) as amt2, name  from cmain where vouchdt > '$date3' and vouchdt <= '$date2' $compcond group by name ") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists cmain1") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE cmain1 
                                SELECT sum(netamt) as amt1, name  from cmain where vouchdt > '$date2' and vouchdt <= '$date1' $compcond group by name ") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists cmain0") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE cmain0 
                                SELECT sum(netamt) as amt0, name  from cmain where vouchdt > '$date1' and vouchdt <= '$date' $compcond group by name ") or die(mysqli_error($conn));
    
    while ($fmain = mysqli_fetch_array($mainqry)) {
        $ctr++;
        $name = $fmain["name"];
        $partyname = $fmain["partyname"];
        
        $openamt = 0;
        $openqry = mysqli_query($conn, "SELECT openbal, dc from $accbal where accode = '$name' $compcond ") or die(mysqli_error($conn));
        while ($fopen = mysqli_fetch_array($openqry)) {
            $oamt = $fopen['openbal'];
            $odc = $fopen['dc'];

            if ($odc == 'C') {
                $oamt = $oamt * -1;
            }
            $openamt = $openamt + $oamt;
        }

        
        $qry6 = mysqli_query($conn, "SELECT amt6 from cmain6 where name = '$name' ") or die(mysqli_error($conn));
        $fet6 = mysqli_fetch_array($qry6);
        $amt6 = $fet6['amt6'];

        $qry5 = mysqli_query($conn, "SELECT amt5 from cmain5 where name = '$name' ") or die(mysqli_error($conn));
        $fet5 = mysqli_fetch_array($qry5);
        $amt5 = $fet5['amt5'];

        $qry4 = mysqli_query($conn, "SELECT amt4 from cmain4 where name = '$name' ") or die(mysqli_error($conn));
        $fet4 = mysqli_fetch_array($qry4);
        $amt4 = $fet4['amt4'];

        $qry3 = mysqli_query($conn, "SELECT amt3 from cmain3 where name = '$name' ") or die(mysqli_error($conn));
        $fet3 = mysqli_fetch_array($qry3);
        $amt3 = $fet3['amt3'];

        $qry2 = mysqli_query($conn, "SELECT amt2 from cmain2 where name = '$name' ") or die(mysqli_error($conn));
        $fet2 = mysqli_fetch_array($qry2);
        $amt2 = $fet2['amt2'];

        $qry1 = mysqli_query($conn, "SELECT amt1 from cmain1 where name = '$name' ") or die(mysqli_error($conn));
        $fet1 = mysqli_fetch_array($qry1);
        $amt1 = $fet1['amt1'];

        $qry = mysqli_query($conn, "SELECT amt0 from cmain0 where name = '$name' ") or die(mysqli_error($conn));
        $fet = mysqli_fetch_array($qry);
        $amt = $fet['amt0'];
        
        $qryp = mysqli_query($conn, "SELECT amtp from actamtp where accode = '$name' ") or die(mysqli_error($conn));
        $fetp = mysqli_fetch_array($qryp);
        $amtp = $fetp['amtp'];
        
        $ttamt = $amt6 + $amt5 + $amt4 + $amt3 + $amt2 + $amt1 + $amt  ;
        
        $total = $openamt + $ttamt ;
        
//        browse($amt6) ;
//        browse($amt5) ;
//        browse($amt4) ;
//        browse($amt3) ;
//        browse($amt2) ;
//        browse($amt1) ;
//        browse($amt) ;
//        browse($name) ;
//        browse($ttamt) ;
//        browse($total) ;
//        browse($amtp) ;
        
        $bal = $total - $amtp;
//
//        browse($amtp);
        
        
        if ($bal > 0) {
            $paidb = $amtp;
            if ($paidb > $openamt) {
                $opensh = 0;
                $paidb = $paidb - $openamt;
            } else {
                $opensh = $openamt - $paidb;
                $paidb = 0 ;
            }

            if ($paidb > $amt6) {
                $amt6sh = 0;
                $paidb = $paidb - $amt6;
            } else {
                $amt6sh = $amt6 - $paidb;
                $paidb = 0 ;
            }
            
            if ($paidb > $amt5) {
                $amt5sh = 0;
                $paidb = $paidb - $amt5;
            } else {
                $amt5sh = $amt5 - $paidb;
                $paidb = 0 ;
            }

            if ($paidb > $amt4) {
                $amt4sh = 0;
                $paidb = $paidb - $amt4;
            } else {
                $amt4sh = $amt4 - $paidb;
                $paidb = 0 ;
            }

            if ($paidb > $amt3) {
                $amt3sh = 0;
                $paidb = $paidb - $amt3;
            } else {
                $amt3sh = $amt3 - $paidb;
                $paidb = 0 ;
            }

            if ($paidb > $amt2) {
                $amt2sh = 0;
                $paidb = $paidb - $amt2;
            } else {
                $amt2sh = $amt2 - $paidb;
                $paidb = 0 ;
            }

            if ($paidb > $amt1) {
                $amt1sh = 0;
                $paidb = $paidb - $amt1;
            } else {
                $amt1sh = $amt1 - $paidb;
                $paidb = 0 ;
            }
            
            if ($paidb > $amt) {
                $amtsh = 0;
                $paidb = $paidb - $amt;
            } else {
                $amtsh = $amt - $paidb;
                $paidb = 0 ;
            }
            
           
            $totalsh = $opensh + $amt6sh + $amt5sh + $amt4sh + $amt3sh + $amt2sh + $amt1sh + $amtsh ;
            
            $contents .= $ctr . ",";
            $contents .= $partyname . ",";
            $contents .= $opensh . ",";
            
            $contents .= $amt6sh . ",";
            $contents .= $amt5sh . ",";
            $contents .= $amt4sh . ",";
            $contents .= $amt3sh . ",";
            $contents .= $amt2sh . ",";
            $contents .= $amt1sh . ",";
            $contents .= $amtsh . ",";
            $contents .= $totalsh . ",";
//            $contents .= $bal . "\n";
            $contents .= "" . "\n";

            $opentot = $opentot + $opensh;
            $tot6 = $tot6 + $amt6sh;
            $tot5 = $tot5 + $amt5sh;
            $tot4 = $tot4 + $amt4sh;
            $tot3 = $tot3 + $amt3sh;
            $tot2 = $tot2 + $amt2sh;
            $tot1 = $tot1 + $amt1sh;
            $tot  = $tot  + $amtsh;
            $gtot = $gtot + $totalsh;
//            $gbal = $gbal + $bal;
        }
    }
    $contents .= ",Total" . ",";
    $contents .= $opentot . ",";
    $contents .= $tot6 . ",";
    $contents .= $tot5 . ",";
    $contents .= $tot4 . ",";
    $contents .= $tot3 . ",";
    $contents .= $tot2 . ",";
    $contents .= $tot1 . ",";
    $contents .= $tot . ",";
    $contents .= $gtot . ",";
//    $contents .= $gbal . "\n";
    $contents .= "" . "\n";

    $contents = strip_tags($contents);

    if ($_POST["dtype"] == 1) {
        header("Content-Disposition: attachment; filename=$formname" . "_Summary" . date('d-m-Y') . ".csv");
        print $contents;
    } else {
        header("Content-Disposition: attachment; filename=$formname" . "_Detail" . date('d-m-Y') . ".csv");
        print $contents;
    }
}
?>

