<?php

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

ini_set('display_errors', 1);
error_reporting(E_ERROR);

$maindb = "actran";
$formname = "Bank / Cash / Contra Tally Report";
$file = "bcctally.php";
$zroot = $_SESSION["zdir"];
$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);

$scope = $_POST["scope"];
$recppay = $_POST["recppay"];
$mfrmdate = trim($_POST["fdate"]);
$mtodate = trim($_POST["tdate"]);

if ($scope == 'C') {
    $scopecond = "('CR', 'CP')";
} else if ($scope == 'B') {
    $scopecond = "('BR', 'BP')";
}

if (empty($_POST["fdate"])) {
    browse("From Date Cannot Be Empty");
    header("refresh:1; slgstreg.php");
}
if (empty($_POST["tdate"])) {
    browse("To Date Cannot Be Empty");
    header("refresh:1; slgstreg.php");
}
$mfdt = date("d-m-Y", strtotime($mfrmdate));
$mtdt = date("d-m-Y", strtotime($mtodate));

$sourcecond = "AND (`source` IN $scopecond)";
if ($recppay == 'R') {
    if ($scope == 'C') {
        $recppaycond = "AND source IN ('CR')";
    } else if ($scope == 'B') {
        $recppaycond = "AND source IN ('BR')";
    }
} else if ($recppay == 'P') {
    if ($scope == 'C') {
        $recppaycond = "AND source IN ('CP')";
    } else if ($scope == 'B') {
        $recppaycond = "AND source IN ('BP')";
    }
}


if ($scope != 'D') {
    $getpmain = mysqli_query(
                    $conn, "SELECT *, DATE_FORMAT(`date`, '%d-%m-%Y') as mydt,DATE_FORMAT(`chqdate`, '%d-%m-%Y') as chqdt FROM `$maindb` WHERE grpcode != '001' AND grpcode != '002' 
     $sourcecond $recppaycond AND `date` BETWEEN '$mfrmdate' AND '$mtodate' ORDER BY `vouchno` ASC"
            ) or die(mysqli_error($conn));
} else {
    $getpmain = mysqli_query(
                    $conn, "SELECT *, DATE_FORMAT(`date`, '%d-%m-%Y') as mydt,DATE_FORMAT(`chqdate`, '%d-%m-%Y') as chqdt FROM `$maindb` WHERE accode != '' AND grpcode = '001' 
         AND source='TR' AND `date` BETWEEN '$mfrmdate' AND '$mtodate' $recppaycond ORDER BY `vouchno` ASC"
            ) or die(mysqli_error($conn));
}


if (isset($_POST["tally"])) {
    include("includes/head_excel_rep.php");

    $contents .= "Date, Party Name, Vch Type, Debit, Credit, Ref. No., Txn No., Txn. Date, Narration \n";

    $ctr = $totcramt = $totdramt = 0;

    $chk = 1;
    while ($pmain = mysqli_fetch_array($getpmain)) {
        $cramt = $dramt = 0; // Reset every loop
        $getsale = mysqli_query($conn, "SELECT `accode`,`name` FROM `accmast` WHERE `accode`='{$pmain['accode']}'") or die(mysqli_error($conn));
        $sale = mysqli_fetch_array($getsale);
        if ($pmain['source'] == "BR") {
            $source = "Bank Receipt";
            $cramt = abs($pmain['amount']);
        } elseif ($pmain['source'] == "BP") {
            $source = "Bank Payment";
            $dramt = abs($pmain['amount']);
        } elseif ($pmain['source'] == "CR") {
            $source = "Cash Receipt";
            $cramt = abs($pmain['amount']);
        } elseif ($pmain['source'] == "CP") {
            $source = "Cash Payment";
            $dramt = abs($pmain['amount']);
        } elseif ($pmain['source'] == "TR") {
            $source = "Contra";
            if ($pmain['amount'] > 0) {
                $cramt = abs($pmain['amount']);
            } else {
                $dramt = abs($pmain['amount']);
            }
        }


        $contents .= "{$pmain['mydt']},";
        $contents .= "{$sale['name']},";
        $contents .= "$source,";
        $contents .= "$dramt,";
        $contents .= "$cramt,";
        $contents .= "{$pmain['vouchno']},";
        $contents .= "{$pmain['chqno']},";
        $contents .= "{$pmain['chqdt']},";
        $contents .= "\n";

        $totdramt += $dramt;
        $totcramt += $cramt;
    }


    $contents .= ",,Grand Total,{$totdramt},{$totcramt}\n";

    $contents = strip_tags($contents);

    $filename = "{$formname}_Tally_Format_" . date('d-m-Y') . ".csv";

    header("Content-Type: text/csv");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    echo $contents;
    exit;
}

if (isset($_POST["tally2"])) {
    include("includes/head_excel_rep.php");

    if ($recppay == 'R') {
        $maindb = "$recpdb";
    } else if ($recppay == 'P') {
        $maindb = "$paydb";
    }

    $contents .= "Date,Voucher, Description, Cheque No, Bill No, Debit, Credit, Balance, Account\n";

    $ctr = $totcramt = $totdramt = 0;

    $lastVouchNo = '';
    $voucherDr = 0;
    $voucherCr = 0;

    while ($pmain = mysqli_fetch_array($getpmain)) {

        $dramt = 0;
        $cramt = 0;
        $roundoffcrdr = '';

        $getsale = mysqli_query($conn, "SELECT `accode`,`name` FROM `accmast` WHERE `accode`='{$pmain['accode']}'") or die(mysqli_error($conn));
        $sale = mysqli_fetch_array($getsale);
        $remfld = "remark1";

        if ($pmain['source'] == "BR") {
            $source = "Bank Receipt";
            $billdb = "bankrecp";
            $cramt = abs($pmain['amount']);
            $type = "CR";
        } elseif ($pmain['source'] == "BP") {
            $source = "Bank Payment";
            $billdb = "bankpay";
            $dramt = abs($pmain['amount']);
            $type = "DR";
        } elseif ($pmain['source'] == "CR") {
            $source = "Cash Receipt";
            $billdb = "cashrecp";
            $cramt = abs($pmain['amount']);
            $type = "CR";
        } elseif ($pmain['source'] == "CP") {
            $source = "Cash Payment";
            $billdb = "cashpay";
            $dramt = abs($pmain['amount']);
            $type = "DR";
        } elseif ($pmain['source'] == "TR") {
            $source = "Contra";
            $billdb = "contra";
            $remfld = "narr1";

            if ($pmain['amount'] > 0) {
                $cramt = abs($pmain['amount']);
                $roundoffcrdr = "CR";
                $type = "CR";
            } else {
                $dramt = abs($pmain['amount']);
                $roundoffcrdr = "DR";
                $type = "DR";
            }
        }

        $balamt = 0;

        $getbillno = mysqli_query($conn, "SELECT `$remfld` as remark1,`bankname`,`chqno` FROM `$billdb` WHERE vouchno = '{$pmain['vouchno']}'") or die(mysqli_error($conn));
        $fetbillno = mysqli_fetch_array($getbillno);

        $getbildet = mysqli_query($conn, "SELECT * FROM billsett 
                                          WHERE bnkvouch = '{$pmain['vouchno']}' 
                                          AND source ='{$pmain['source']}'") or die(mysqli_error($conn));

        $totalSetoff = 0;

        if (mysqli_num_rows($getbildet) > 0) {

            while ($fetbildet = mysqli_fetch_array($getbildet)) {

                if ($lastVouchNo != '' && $lastVouchNo != $pmain['vouchno']) {
                    $contents .= "\n";
//                    $contents .= ",,,,Voucher Total,$voucherDr,$voucherCr,,\n";
                    $voucherDr = 0;
                    $voucherCr = 0;
                }

                $setoff = abs($fetbildet['setoff']);
                $totalSetoff += $setoff;

                $debit = 0;
                $credit = 0;

                if ($type == "CR") {
                    $credit = $setoff;
                    $voucherCr += $credit;
                } else {
                    $debit = $setoff;
                    $voucherDr += $debit;
                }

                if ($lastVouchNo != $pmain['vouchno']) {
                    $contents .= "{$pmain['mydt']},{$pmain['vouchno']},{$sale['name']},{$fetbillno['chqno']},";
                } else {
                    $contents .= ",,,,";
                }

                $contents .= "{$fetbildet['billno']},$debit,$credit,0,{$account['name']}\n";

                $lastVouchNo = $pmain['vouchno'];
            }

            // ✅ Handle remaining (On Account)
            $totalAmount = abs($pmain['amount']);
            $remaining = $totalAmount - $totalSetoff;

            if ($remaining > 0) {

                $debit = 0;
                $credit = 0;

                if ($type == "CR") {
                    $credit = $remaining;
                    $voucherCr += $credit;
                } else {
                    $debit = $remaining;
                    $voucherDr += $debit;
                }

                $contents .= ",,,,On Account,$debit,$credit,0,{$account['name']}\n";
            }
        } else {

            // ✅ No bills at all (full On Account)
            $amount = abs($pmain['amount']);

            $debit = 0;
            $credit = 0;

            if ($type == "CR") {
                $credit = $amount;
                $voucherCr += $credit;
            } else {
                $debit = $amount;
                $voucherDr += $debit;
            }

            $contents .= "{$pmain['mydt']},{$pmain['vouchno']},{$sale['name']},{$fetbillno['chqno']},";
            $contents .= "On Account,$debit,$credit,0,{$account['name']}\n";

            $lastVouchNo = $pmain['vouchno'];
        }

        $totdramt += $dramt;
        $totcramt += $cramt;
    }

// ? Last voucher total
//    if ($lastVouchNo != '') {
//        $contents .= ",,,,Voucher Total,$voucherDr,$voucherCr,,\n";
//    }
// ? Grand Total
    $contents .= ",,,,Grand Total,$totdramt,$totcramt,,\n";

    $contents = strip_tags($contents);

    $filename = "{$formname}_Tally_Format_" . date('d-m-Y') . ".csv";

    header("Content-Type: text/csv");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    echo $contents;
    exit;
}
?>
