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

$zdb = $_SESSION["zdbyear_" . $dbname];
$admin = $_SESSION["admin_" . $dbname];
mysqli_select_db($conn, $zdb);

$zyear = $_SESSION["tit_" . $dbname];
//$admin = $_SESSION["admin_" . $dbname];
$maindb = 'accmast';

$trandb = "actran";
$bankrecp = "bankrecp";
$bankpay = "bankpay";
$cashrecp = "cashrecp";
$cashpay = "cashpay";
$contra = "contra";

$dispname = 'Cash';
$formname = 'Unsettle Vouchers';

$file = "unsettle_voucher.php";

if (isset($_POST["submit"]) || ($_GET["view"])) {
    $repname = "";

    $acond = "";
    $pcond = "";
    if (!empty($_POST["name"])) {
        $code = $_POST["name"];
        $acond = "AND `accode`='$code'";
        $pcond = "AND `accode`='$code'";
    } elseif (!empty($_GET["acc"])) {
        $code = $_GET["acc"];
        $acond = "AND `accode`='$code'";
        $pcond = "AND `accode`='$code'";
    }

    if ($_GET["fdt"] != '') {
        $mfrmdate = trim($_GET["fdt"]);
    } else {
        $mfrmdate = trim($_POST["frmdate"]);
        if (empty($_POST["frmdate"])) {
            browse("From Date Cannot Be Empty");
            echo "<script>window.close()</script>";
        }
    }

    if ($_GET["tdt"] != '') {
        $mtodate = trim($_GET["tdt"]);
    } else {
        $mtodate = trim($_POST["todate"]);
        if (empty($_POST["todate"])) {
            browse("To Date Cannot Be Empty");
            echo "<script>window.close()</script>";
        }
    }

    $scope = $_POST["scope"];
//    browse($scope);
//    if ($_GET["rpt2"] != '') {
//        $prntype = $_GET["rpt2"];
//    } else {
//        $prntype = $_POST["rtype"];
//    }
//    $prnnarr = $_POST["prinarr"];
//    if ($prntype == 1) {
//        $repname = $dispname . " Book";
//    } elseif ($prntype == 2) {
//        $repname = $dispname . " Book - Only Debits";
//    } else {
//        $repname = $dispname . " Book - Only Credits";
//    }

    mysqli_query($conn, "drop temporary table if exists unsettdbf ") or die(mysqli_error($conn));
    if ($scope == 'B') {
        mysqli_query($conn, "create temporary table unsettdbf
                                SELECT 'BR' as source, 'bankrecp.php' as linknm, vouchno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, bankcode as bankcode, 
                                          accode, amount, chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as chqdt from bankrecp
                                          WHERE vouchdt >= '$mfrmdate' and vouchdt <= '$mtodate' and `retdate` = '0000-00-00'  $pcond  
                                          and accode in (select accode from accmast where grpcode = '010') and 
                                          vouchno not in (select bnkvouch from billsett where source = 'BR' )
                                union all          
                                SELECT 'BP' as source, 'bankpay.php' as linknm, vouchno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, bankcode as bankcode, 
                                          accode, amount, chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as chqdt from bankpay
                                          WHERE vouchdt >= '$mfrmdate' and vouchdt <= '$mtodate' and `retdate` = '0000-00-00'  $pcond  
                                          and accode in (select accode from accmast where grpcode = '009') and 
                                          vouchno not in (select bnkvouch from billsett where source = 'BP' )
                                          ") or die(mysqli_error($conn));
    }

    if ($scope == 'C') {
        mysqli_query($conn, "create temporary table unsettdbf
                                SELECT 'CR' as source, 'cashrecp.php' as linknm, vouchno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, bankcode as bankcode, 
                                          party as accode, amount, '' as chqno, '' as chqdt from cashrecp
                                          WHERE vouchdt >= '$mfrmdate' and vouchdt <= '$mtodate'   $pcond  
                                          and party in (select accode from accmast where grpcode = '010') and 
                                          vouchno not in (select bnkvouch from billsett where source = 'CR' )
                                union all          
                                SELECT 'CP' as source, 'cashpay.php' as linknm, vouchno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, bankcode as bankcode, 
                                          accode, amount, '' as chqno, '' as chqdt from cashpay
                                          WHERE vouchdt >= '$mfrmdate' and vouchdt <= '$mtodate' $pcond  
                                          and accode in (select accode from accmast where grpcode = '009') and 
                                          vouchno not in (select bnkvouch from billsett where source = 'CP' )
                                          ") or die(mysqli_error($conn));
    }
    
    // if ($scope == 'R') {
    //     mysqli_query($conn, "create temporary table unsettdbf
    //                             SELECT 'RD' as source, 'credothgst.php' as linknm, vouchno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, 
    //                                       party as accode,netamt as amount, '' as chqno, '' as chqdt from cdmn  
    //                                       WHERE vouchdt >= '$mfrmdate' and vouchdt <= '$mtodate'   $pcond  
    //                                       and party in (select accode from accmast where grpcode = '010') and 
    //                                       vouchno not in (select bnkvouch from billsett where source = 'RD' )
    //                                       ") or die(mysqli_error($conn));
    // }
    
    // if ($scope == 'G') {
    //     mysqli_query($conn, "create temporary table unsettdbf
    //                             SELECT 'GR' as source, 'credgr.php' as linknm, vouchno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, 
    //                                       party as accode,netamt as amount, '' as chqno, '' as chqdt from grmn
    //                                       WHERE vouchdt >= '$mfrmdate' and vouchdt <= '$mtodate'   $pcond  
    //                                       and party in (select accode from accmast where grpcode = '010') and 
    //                                       vouchno not in (select bnkvouch from billsett where source = 'GR' )
    //                                       ") or die(mysqli_error($conn));
    // }
    
    
    // if ($scope == 'S') {
    //     mysqli_query($conn, "create temporary table unsettdbf
    //                             SELECT 'SRWH' as source, 'saleretwh.php' as linknm, vouchno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, 
    //                                       party as accode,netamt as amount, '' as chqno, '' as chqdt from credmainwh
    //                                       WHERE vouchdt >= '$mfrmdate' and vouchdt <= '$mtodate'   $pcond  
    //                                       and party in (select accode from accmast where grpcode = '010') and 
    //                                       vouchno not in (select bnkvouch from billsett where source = 'SRWH' )
    //                             union all          
    //                             SELECT 'SRRT' as source, 'saleretrt.php' as linknm, vouchno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, 
    //                                       party as accode,netamt as amount, '' as chqno, '' as chqdt from credmainrt
    //                                       WHERE vouchdt >= '$mfrmdate' and vouchdt <= '$mtodate'   $pcond  
    //                                       and party in (select accode from accmast where grpcode = '010') and 
    //                                       vouchno not in (select bnkvouch from billsett where source = 'SRRT' )
    //                                       ") or die(mysqli_error($conn));
    // }



    // if ($scope == 'J') {
    //     mysqli_query($conn, "create temporary table unsettdbf
    //                             SELECT 'JV' as source, 'jv.php' as linknm, vouchno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, '' as bankcode, 
    //                                       accode, inramt as amount, '' as chqno, '' as chqdt from jvtran 
    //                                       WHERE vouchdt >= '$mfrmdate' and vouchdt <= '$mtodate'  and bs = 'B' $acond
    //                                       and (debit + credit > 0)    
    //                                       and accode in (select accode from accmast where (grpcode = '010' or grpcode = '009')) and 
    //                                       vouchno not in (select bnkvouch from billsett where source = 'JV' )
    //                                       ") or die(mysqli_error($conn));
    // }


    $acc = mysqli_query($conn, "SELECT a.*, b.name FROM unsettdbf a, `$maindb` b WHERE a.accode = b.accode ") or die(mysqli_error($conn));
//    print table($acc);
//    die();

    include("includes/linkshead.php");
    ?>
    <head>
        <style>
            @media print {
                .noPrint {
                    display:none;
                }
            }

        </style>
    </head>
    <body>
        <div class="container">
            <?php include('includes/head_rep.php') ?>
            <div class="row">
                <div class="col-lg-12">

                    <table class=" table table-condensed table-bordered table-striped" summary="Unsettle Vouchers" style="width:100%" border="0">    
                        <thead>
                            <tr style="height:10px; " class="mySearchTr">
                                <th style="text-align:left;width:10%"><b>Date</b></th>
                                <th style="text-align:left;width:5%"><b>Source</b></th>
                                <th style="text-align:left;width:10%"><b>Voucher</b></th>
                                <th style="text-align:right;width:10%"><b>Amount</b></th>
                                <th style="text-align:left;width:40%"><b>A/c. Name</b></th>
                                <th style="text-align:left;width:10%"><b>Cheque No.</b></th>
                                <th style="text-align:left;width:10%"><b>Cheque Date</b></th>
                            </tr>
                        </thead>

                        <tbody>
                            <?php
                            mysqli_select_db($conn, $zdb);
                            $totbal = 0;

                            while ($getac = mysqli_fetch_array($acc)) {
                                $totbal = 0;
                                $dtot = 0;
                                $ctot = 0;

                                $mvouch = $getac["vouchno"];
                                $maccode = $getac["accode"];
                                $macname = $getac["name"];
                                $mydt = $getac["mydt"];
                                $mchqno = $getac["chqno"];
                                $mchqdt = $getac["chqdt"];
                                $msource = $getac["source"];
                                $mamount = abs($getac["amount"]);
                                $mlink1 = $getac["linknm"];
                                $mlink = $mlink1 . "?vouchno=$mvouch&mode=edit";

                                echo "<tr class='clickable-row2' data-href='$mlink'>";
                                echo "<td>$mydt</td>";
                                echo "<td>$msource</td>";
                                echo "<td>$mvouch</td>";
                                echo "<td class='text-right'>" . number_format($mamount, 2) . "</td>";
                                echo "<td>$macname</td>";
                                echo "<td>$mchqno</td>";
                                echo "<td>$mchqdt</td>";
                                echo "</tr>";
                            }
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
        <?php
        include("includes/linksfoot.php");
    }

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

        $acond = "";
        if (!empty($_POST["name"])) {
            $code = $_POST["name"];
            $acond = "AND `accode`='$code'";
        } elseif (!empty($_GET["acc"])) {
            $code = $_GET["acc"];
            $acond = "AND `accode`='$code'";
        } else {
            if ($mpara == "B") {
                browse("Bank Name Cannot Be Empty.");
            } else {
                browse("Cash Name Cannot Be Empty.");
            }
            header("refresh: 1; ledger.php");
            exit();
        }

        if ($_GET["fdt"] != '') {
            $mfrmdate = trim($_GET["fdt"]);
        } else {
            $mfrmdate = trim($_POST["frmdate"]);
            if (empty($_POST["frmdate"])) {
                browse("From Date Cannot Be Empty");
                header("refresh:1; bankbook.php");
            }
        }
        if ($_GET["tdt"] != '') {
            $mtodate = trim($_GET["tdt"]);
        } else {
            $mtodate = trim($_POST["todate"]);
            if (empty($_POST["todate"])) {
                browse("To Date Cannot Be Empty");
                header("refresh:1; bankbook.php");
            }
        }

        if ($_GET["rpt2"] != '') {
            $prntype = $_GET["rpt2"];
        } else {
            $prntype = $_POST["rtype"];
        }
        $prnnarr = $_POST["prinarr"];

        if ($prntype == 1) {
            $repname = $dispname . " Book";
        } elseif ($prntype == 2) {
            $repname = $dispname . " Book - Only Debits";
        } else {
            $repname = $dispname . " Book - Only Credits";
        }

        $acc = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE accode!='' $acond") or die(mysqli_error($conn));

        mysqli_select_db($conn, $zdb);

        $totbal = 0;

        while ($getac = mysqli_fetch_array($acc)) {
            /** Detail Part* */
            $totbal = 0;
            $dtot = 0;
            $ctot = 0;

            $maccode = $getac["accode"];
            $mbankinit = $getac["bankinit"];
//                                browse($mbankinit);
            $macname = $getac["name"];
            $mopbal = $getac["openbal"];
            $mopdc = $getac["dc"];

            if ($mopdc == "D") {
                $mopbal = $mopbal * - 1;
            }


            $ctran1 = mysqli_query($conn, "SELECT *, DATE_FORMAT(`cashdate`, '%d-%m-%Y') as mydt FROM `actran` 
                                                       WHERE accode != '$maccode' and 
                                                       (source = '$msource1' or source = '$msource2') and  
                                                       concat(source,vouchno) in (select concat(source,vouchno) from `actran` 
                                                       where accode = '$maccode' and 
                                                       cashdate >= '$mfrmdate' and cashdate <= '$mtodate' and `retdate` = '0000-00-00'    
                                                       and (source = '$msource1' or source = '$msource2'))  
                                                       union all
                                                       SELECT *, DATE_FORMAT(`cashdate`, '%d-%m-%Y') as mydt FROM `actran` 
                                                       WHERE accode != '$maccode' and source = 'TR' and  
                                                       concat(source,vouchno) in (select concat(source,vouchno) from `actran` 
                                                       where accode = '$maccode'  and `retdate` = '0000-00-00' and
                                                       cashdate >= '$mfrmdate' and cashdate <= '$mtodate' and source = 'TR' )    
                                                       order by cashdate, source, vouchno ") or die(mysqli_error($conn));

//                                print table($ctran1);

            $getbal = mysqli_query($conn, "SELECT SUM(amount) as amt FROM `$trandb` WHERE accode = '$maccode' AND 
                                                           `cashdate` < '$mfrmdate' and `retdate` = '0000-00-00' ") or die(mysqli_error($conn));
            $getamt = mysqli_fetch_array($getbal);
            $amt = $getamt['amt'];

            $mobal = $mopbal + $amt;
            $mdebit = 0;
            $mcredit = 0;

            if ($mobal < 0) {
                $mdc = "DR";
                $mdebit = abs($mobal);
//                                    $dtot = $dtot + $mdebit;
            } else {
                $mdc = "CR";
                $mcredit = abs($mobal);
//                                    $ctot = $ctot + $mcredit;
            }
            $vdate1 = strtotime($mfrmdate);
            $vdate = date('d-m-Y', $vdate1);
            $mvouch = "";
            $mdescrip = "Opening Balance";
            $totbal = $totbal + $mobal;

            if ($totbal < 0) {
                $mdctot = "DR";
                $colr = " color:black";
            } else {
                $mdctot = "CR";
                $colr = " color:red";
            }
            $contents .= "$macname \n";


            $contents .= "Sr., Date, Voucher, Description, Debit, Credit, Balance\n";

            $ctr++;
            $contents .= "$ctr" . ",";
            $contents .= "$vdate" . ",";
            $contents .= "$mvouch" . ",";
            $contents .= "$mdescrip" . ",";

            if ($prntype == 1) {
                $btotbal = abs($totbal);
                $contents .= "$mdebit" . ",";
                $contents .= "$mcredit" . ",";
                $contents .= "$btotbal" . "$mdctot" . "\n";
            } elseif ($prntype == 2) {
                $contents .= "$mdedit" . ",";
                $contents .= "" . ",";
                $contents .= "" . ",";
            } else {
                $contents .= "" . ",";
                $contents .= "$mcredit" . ",";
                $contents .= "" . "\n";
            }

            if ($mcredit > 0) {
                $opening = round($mcredit);
            } else {
                $opening = round($mdebit);
            }
            while ($row = mysqli_fetch_assoc($ctran1)) {

                $vdate = $row["mydt"];
                $msource = $row["source"];
                $mvouch = $row["vouchno"];
                $mamount = $row["amount"];
                $daccode = $row["accode"];
                $mdebit = 0;
                $mcredit = 0;

                if ($mamount > 0) {
                    $mdc = "DR";
                    $mdebit = abs($mamount);
                    $dtot = $dtot + $mdebit;
                    $btotbal = $btotbal - abs($mamount);
                } else {
                    $mdc = "CR";
                    $mcredit = abs($mamount);
                    $ctot = $ctot + $mcredit;
                    $btotbal = $btotbal + abs($mamount);
                }

                if ($btotbal < 0) {
                    $mdctot = "DR";
                    $colr = " color:black";
                } else {
                    $mdctot = "CR";
                    $colr = " color:red";
                }
                $mdescrip = "";
                $mchqdtl = "";
                $mnarr = "";

                $nqry1 = mysqli_query($conn, "SELECT name FROM `accmast` WHERE accode = '$daccode'") or die(mysqli_error($conn));
                $nqry = mysqli_fetch_array($nqry1);

                $head = $nqry["name"];

                switch ($msource) {

                    case "BR";
                        if ($prnnarr == 1) {
                            $nqry1 = mysqli_query($conn, "SELECT chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as mbilldate, remark1
                                                                        FROM `$bankrecp`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                            $nqry = mysqli_fetch_array($nqry1);

                            $mbill = $nqry["chqno"];
                            $mbilldt = $nqry["mbilldate"];
                            $mnarr = $nqry["remark1"];
                            $mchqdtl = "Cheque No. : " . + trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                        }
                        break;

                    case "BP";

                        if ($prnnarr == 1) {
                            $nqry1 = mysqli_query($conn, "SELECT chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as mbilldate, remark1 
                                                                         FROM `$bankpay`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                            $nqry = mysqli_fetch_array($nqry1);

                            $mbill = $nqry["chqno"];
                            $mbilldt = $nqry["mbilldate"];
                            $mnarr = $nqry["remark1"];
                            $mchqdtl = "Cheque No. : " . + trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                        }
                        break;

                    case "CR";

                        if ($prnnarr == 1) {
                            $nqry1 = mysqli_query($conn, "SELECT `remark1` FROM `$cashrecp`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                            $nqry = mysqli_fetch_array($nqry1);

                            $mbill = '';
                            $mbilldt = '';
                            $mnarr = $nqry["remark1"];
//$mchqdtl = "Cheque No. : ".+ trim($mbill)." &nbsp;&nbsp;Dated : ".$mbilldt;  
                        }
                        break;

                    case "CP";

                        if ($prnnarr == 1) {
                            $nqry1 = mysqli_query($conn, "SELECT `remark1` FROM `$cashpay`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                            $nqry = mysqli_fetch_array($nqry1);

                            $mbill = $nqry[""];
                            $mbilldt = $nqry[""];
                            $mnarr = $nqry["remark1"];
//$mchqdtl = "Cheque No. : ".+ trim($mbill)." &nbsp;&nbsp;Dated : ".$mbilldt;  
                        }
                        break;

                    case "TR";
//                                            $head = "Contra / Transfer";
                        if ($mamount > 0) {
                            $head = "To " . $head;
                        } else {
                            $head = "From " . $head;
                        }
                        if ($prnnarr == 1) {
                            $nqry1 = mysqli_query($conn, "SELECT `narr1`, `narr2` FROM `$contra`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                            $nqry = mysqli_fetch_array($nqry1);

                            $mnarr = trim($nqry["narr1"]) . ' ' . trim($nqry["narr2"]);
                        }
                        break;
                    default :
                        $head = "Other";
                }
                $contents .= "$vdate" . ",";
                $contents .= "$msource " . "$mvouch" . ",";
                $contents .= "$head" . ",";

                if ($prnnarr == 1) {

                    if (!empty($mchqdtl)) {
                        $contents .= "$mchqdtl" . ",";
                        if (!empty($mnarr)) {
                            $contents .= "$mnarr" . ",";
                        }
                    }
                }
                if ($prntype == 1) {
                    $contents .= "$mdebit" . ",";
                    $contents .= "$mcredit" . ",";
                    $contents .= "$btotbal" . "$mdctot" . ",";
                } elseif ($prntype == 2) {
                    $contents .= "$mdebit" . ",";
                    $contents .= "" . ",";
                    $contents .= "" . ",";
                } else {

                    $contents .= "" . ",";
                    $contents .= "$mcredit" . ",";
                    $contents .= "" . "\n";
                }
            }

            if ($opening > 0) {
                $dtot = $dtot + $opening;
            } else {

                $ctot = $ctot + $opening;
            }
            $contents .= "" . ",";
            $contents .= "" . ",";
            $contents .= "" . ",";
            $contents .= "Total" . ",";

            if ($prntype == 1) {
                $contents .= "$dtot" . ",";
                $contents .= "$ctot" . ",";

                if ($btotbal < 0) {
                    $contents .= "$btotbal" . "$mdctot" . ",";
                } else {
                    $contents .= "$btotbal" . "$mdctot" . ",";
                }
            } elseif ($prntype == 2) {
                $contents .= "$dtot" . ",";
                $contents .= "" . ",";
            } else {

                $contents .= "$ctot" . "\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;
        }
    }

        