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

$formname = 'Generate';
$maindb = 'billmain';
$trandb = 'billtran';
$accdb = 'actran';
$file = 'generate_monthly.php';
$grid = 'genbill_monthly.php';
$keyfld = "billno";
$source = 'BILL';

$series = $_SESSION["society_" . $dbname];

$adduser = $_SESSION["username_" . $dbname];
date_default_timezone_set("Asia/Kolkata");
$adddate = date("Y-m-d");
$addtime = date("h:i:sa");


if (empty($series)) {
    browse("Invalid Society Credentials Forced Logout.");
    print '<script type="text/javascript">';
    print " top.window.location = 'index.php'";
    PRINT '</script>';
}

$year = $_SESSION[$myyear];
$zroot = $_SESSION["zdbroot"];
$keycond = " AND `series`='$series'";
//browse($series);

$getcomp = mysqli_query($conn, "SELECT code, compname, acinterest, intrate,billpayd,billpaym FROM $zroot.`company` WHERE `code`='$series' ") or die(mysqli_error($conn));
$comp = mysqli_fetch_array($getcomp);
$compname = $comp['compname'];
$irate12 = $comp['intrate'];
$billpayd = $comp['billpayd'];
$billpaym = $comp['billpaym'];
$irate = $irate12 / 12;
$zacinterest = $comp['acinterest'];

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

$zyear = right($zdb, 4);
$mfrdate = getzdates($zyear, 'S');
$mtodate = getzdates($zyear, 'E');

mysqli_select_db($conn, $zdb);
//delete start
if (isset($_POST["delete"])) {
    $billcode = $_POST["billcode"];
    $billcycle = $_POST['billcycle'];
    $yearno = $_POST['yearno'];

    $member = $_POST["member"];
    $date = $_POST["trandate"];
    
    if (!empty($_POST["flatno"])) {
        $flatno = $_POST["flatno"];
        $flatcond = " AND flatno = '$flatno' ";
        
        $gcode = mysqli_query($conn, "SELECT accode from accmast where accode != '' $flatcond ")or die(mysqli_error($conn));
        $fcode = mysqli_fetch_array($gcode);
        $member = $fcode['accode'];
    }


    if ($billcode == "") {
        browse("Bill Period Cannot Be Empty");
        header("Location:  $grid");
    }
    if ($yearno == "") {
        browse("Year Cannot Be Empty");
        header("Location:  $grid");
    }

    $gbilcode = mysqli_query($conn, "SELECT * FROM $zroot.`billingcycle` Where `billcycle`='$billcycle' and `code` = '$billcode' ") or die(mysqli_error($conn));
    $fetbilcode = mysqli_fetch_array($gbilcode);
    $cyclecode = $fetbilcode['cyclecode'];


    $memcond = '';

    if (!empty($member)) {

        $vouchno = $member . $cyclecode . $billcode;

        $memcond = "and `vouchno` = '$vouchno' ";
        $memcond1 = "and `accode` = '$member' ";
    }


    mysqli_query($conn, "LOCK TABLES `$maindb` WRITE, `$trandb` WRITE, `$accdb` WRITE, `accmast` WRITE, `memtran` WRITE") or die(mysqli_error($conn));

    mysqli_query($conn, "DELETE FROM `$maindb` WHERE  `society`='$series' and `billcycle`='$billcycle' and `billcode`='$billcode' and yearno = '$yearno' $memcond") or die(mysqli_error($conn));
    mysqli_query($conn, "DELETE FROM `$trandb` WHERE  `society`='$series' and `billcycle`='$billcycle' and `billcode`='$billcode' and yearno = '$yearno' $memcond") or die(mysqli_error($conn));
    mysqli_query($conn, "DELETE FROM `$accdb` WHERE `series`='$series' and `billcycle`='$billcycle' and `billcode`='$billcode'  AND `source`='$source' and yearno = '$yearno' $memcond") or die(mysqli_error($conn));
    mysqli_query($conn, "DELETE FROM `$accdb` WHERE `series`='$series' and `billcycle`='$billcycle' and `billcode`='$billcode'  AND `source`='INT' and yearno = '$yearno' $memcond") or die(mysqli_error($conn));

    mysqli_query($conn, "UNLOCK TABLES");
    browse('Deleted Successfully');
}
//delete end
//
//save start
if (isset($_POST["submit"])) {
    $billcode = $_POST["billcode"];
    $billcycle = $_POST["billcycle"];
    $yearno = $_POST["yearno"];

    $member = $_POST["member"];
    $date = $_POST["trandate"];
    

    if ($billcode == "") {
        browse("Bill Period Cannot Be Empty");
        header("Location:  $grid");
    }
    
    if (!empty($_POST["flatno"])) {
        $flatno = $_POST["flatno"];
        $flatcond = " AND flatno = '$flatno' ";
        
        $gcode = mysqli_query($conn, "SELECT accode from accmast where `accode`!='' $flatcond ")or die(mysqli_error($conn));
        $fcode = mysqli_fetch_array($gcode);
        $member = $fcode['accode'];
    }

    
    $a = 1;

    if ($date == '0000-00-00' || $date == '') {
        $a = 0;
        browse('Date Is Invalid');
    }

    if ($a == 1) {

        $memcond = '';

        $gbilcode = mysqli_query($conn, "SELECT * FROM $zroot.`billingcycle` Where `billcycle` = '$billcycle' and `code` = '$billcode' ") or die(mysqli_error($conn));
        $fetbilcode = mysqli_fetch_array($gbilcode);
        $cyclecode = $fetbilcode['cyclecode'];

//        switch ($billcycle) {
//            case 'M': if ($billcode < 4) {
//                    $cdate = strtotime($mtodate);
//                } else {
//                    $cdate = strtotime($mfrdate);
//                }
//
//                $chkdate = date("Y", $cdate);
//                break;
//            case 'BM': if ($billcode == 6) {
//                    $cdate = strtotime($mtodate);
//                } else {
//                    $cdate = strtotime($mfrdate);
//                }
//
//                $chkdate = date("Y", $cdate);
//                break;
//            case 'Q': if ($billcode == 4) {
//                    $cdate = strtotime($mtodate);
//                } else {
//                    $cdate = strtotime($mfrdate);
//                }
//
//                $chkdate = date("Y", $cdate);
//                break;
//            case 'H': if ($billcode == 2) {
//                    $cdate = strtotime($mtodate);
//                } else {
//                    $cdate = strtotime($mfrdate);
//                }
//
//                $chkdate = date("Y", $cdate);
//                break;
//            default: $chkdate = '';
//        }


        if (!empty($member)) {
            $vouchno = $member . $cyclecode . $billcode;

            $memcond = "and `vouchno` = '$vouchno' ";
            $memcond1 = "and `accode` = '$member' ";
        }


        mysqli_query($conn, "LOCK TABLES `$maindb` WRITE, `$trandb` WRITE, `$accdb` WRITE, `accmast` WRITE, `memtran` WRITE, inttran WRITE") or die(mysqli_error($conn));

        mysqli_query($conn, "DELETE FROM `$maindb` WHERE  `society`='$series' and `billcycle`='$billcycle' and `billcode`='$billcode' and yearno = '$yearno'  $memcond") or die(mysqli_error($conn));

        mysqli_query($conn, "DELETE FROM `$trandb` WHERE  `society`='$series' and `billcycle`='$billcycle' and `billcode`='$billcode' and yearno = '$yearno'  $memcond") or die(mysqli_error($conn));

        mysqli_query($conn, "DELETE FROM `$accdb` WHERE `series`='$series' and `billcycle`='$billcycle' and `billcode`='$billcode'  AND `source`='$source' and yearno = '$yearno'  $memcond") or die(mysqli_error($conn));
        mysqli_query($conn, "DELETE FROM `$accdb` WHERE `series`='$series' and `billcycle`='$billcycle' and `billcode`='$billcode'  AND `source`='INT' and yearno = '$yearno'  $memcond") or die(mysqli_error($conn));

        $gen1 = mysqli_query($conn, "SELECT MAX(newbill) AS maxcode FROM `$maindb` WHERE `society` = '$series' LIMIT 0,1 ") or die(mysqli_error($conn));

        if (mysqli_num_rows($gen1) > 0) {
            $vbill = mysqli_fetch_assoc($gen1);
            $qbill = $vbill["maxcode"] + 1;
        } else {
            $qbill = '1';
        }

        $chkdt = $fetbilcode['checkdate'] . $yearno;
        $checkdate = date("Y-m-d", strtotime($chkdt));
        $gmember = mysqli_query($conn, "SELECT `accode`,`name`,`flatno`,`openbal`,dc,`prinamt`,`advance`, `interest`, `latefee`
                                FROM `accmast` Where `grpcode`='010'  $memcond1 order by flatno") or die(mysqli_error($conn));
        
        while ($fmember = mysqli_fetch_assoc($gmember)) {
            $member = $fmember['accode'];
            $flatno = $fmember['flatno'];
            $openbal = $fmember['openbal'];
            $intopen = $fmember['interest'];
            $arropen = $fmember['latefee'];
            $openprin = $fmember['prinamt'];
            $openadv = $fmember['advance'];
            $dc = $fmember['dc'];

            if ($dc == 'D') {
                $mopen = abs($openprin) * -1;
            } else {
                $mopen = abs($openadv);
            }

            $intprev = 0;
            $intamt = 0;
            $amtprev = 0;

            $billno = $member . '-' . $flatno . '/' . $billcycle . '/' . $billcode . '/' . $yearno;
            $vouchno = $member . $cyclecode . $billcode;

//            for checking previous  month receip for intprev

            if ($billcode == 1) {
                $mbilcode = 12;
            } else {
                $mbilcode = $billcode - 1;
            }

            $gint3 = mysqli_query($conn, "SELECT sum(amount) as amt FROM `actran` WHERE `accode`='$member' and `series` ='$series'
                                and month(date) =  '$mbilcode' and amount > 0 and `yearno` = '$yearno' ") or die(mysqli_error($conn));
            $fint3 = mysqli_fetch_array($gint3);
            $amtprev = $fint3['amt'];

//            end

            $garreas = mysqli_query($conn, "SELECT sum(amount) as amt FROM `actran` WHERE `accode`='$member' and `series` ='$series'
                                    and date < '$date' and `source`!='INT' and `yearno` = '$yearno' ") or die(mysqli_error($conn));
            $farreas = mysqli_fetch_array($garreas);
            $arreas = $farreas['amt'];
            $tbal = $mopen + $arreas;

//            browse($mopen);
//            browse($arreas);
//            browse($tbal);




            $chkac = mysqli_query($conn, "SELECT sum(amount) as amt FROM `actran` WHERE `accode`='$member' and `series` ='$series'
                                and date <  '$date' and `yearno` = '$yearno' ") or die(mysqli_error($conn));
            $fetac = mysqli_fetch_array($chkac);
            $amtac = $fetac['amt'] + $mopen;
//            browse($amtac);



            $gint = mysqli_query($conn, "SELECT sum(amount) as amt FROM `actran` WHERE `accode`='$member' and `series` ='$series'
                                and date < '$date' and `source` ='INT' and `yearno` = '$yearno' ") or die(mysqli_error($conn));
            $fint = mysqli_fetch_array($gint);
            $intacc = $fint['amt'] * -1;

            $gint2 = mysqli_query($conn, "SELECT sum(amount) as amt FROM `inttran` WHERE `accode`='$member' and `series` ='$series'
                                and date < '$date' and `yearno` = '$yearno' ") or die(mysqli_error($conn));
            $fint2 = mysqli_fetch_array($gint2);
            $intpaid = $fint2['amt'];



            $intprev = $intopen + $intacc - $intpaid;

            if ($intprev < 0) {
                $intprev = 0;
            }

            if ($tbal <= 0) {
                $arrears = $tbal - $intpaid;
                $arrears = abs($arrears);
                $intamt = round(($arrears * $irate) / 100, 0);
                $adv = 0;
                $abal = $tbal * -1;
            } else {
                $adv = $tbal - $intacc;
                $adv = abs($adv);
                $arrears = $adv * -1;
                $abal = $adv * -1;
            }

            if ($arrears < 0) {
                $adv = abs($arrears);
                $arrears = 0;
            }

            $ctr = 0;
            $totamt = 0;

//      tran part - first

            $gmemtran = mysqli_query($conn, "SELECT * FROM `memtran` WHERE `accode`='$member' and amount > 0 and `cono` ='$series' order by srno") or die(mysqli_error($conn));
            while ($fmemtran = mysqli_fetch_array($gmemtran)) {
                $ctr++;
                $maintype = $fmemtran['maintype'];
                $amount = $fmemtran['amount'];
                mysqli_query($conn, "INSERT INTO `$trandb`(yearno, `srno`, `billno`, `trandate`, `billdate`, `newbill`, `vouchno`, `billcode`, `billcycle`, `amount`, `accode`, `maintcode`,`society`)
                                            VALUES('$yearno', '$ctr', '$billno', '$checkdate', '$billdate', '$qbill', '$vouchno', '$billcode', '$billcycle', '$amount',  '$member','$maintype','$series')") or die(mysqli_error($conn));
                $totamt = $totamt + $amount;

                // cash part
                $mctr1 = 9000 + $ctr;
                $mctr2 = (string) $mctr1;
                $mctr = right($mctr2, 3);

                mysqli_query($conn, "INSERT INTO `$accdb`(yearno, forac, series, date, newbill, vouchno, billno, source, trnno, accode,  grpcode, amount, `billcycle`, `billcode`)
                        VALUES ('$yearno', 'M', '$series','$date', '$qbill', '$vouchno','$billno', '$source', '$mctr', '$maintype',  '011', '$amount','$billcycle', '$billcode')") or die(mysqli_error($conn));
            }
            $total = $totamt;

            $ctr++;
            $mctr1 = 9000 + $ctr;
            $mctr2 = (string) $mctr1;
            $mctr = right($mctr2, 3);

            // party part
            $minus_amt = $total * -1;
            mysqli_query($conn, "INSERT INTO `$accdb`(yearno, forac, series, date, flatno, newbill, vouchno, billno, source, trnno, accode, grpcode, amount, `billcycle`, `billcode`)
                     VALUES ('$yearno', 'M', '$series','$date', '$flatno', '$qbill','$vouchno', '$billno', '$source', '$mctr', '$member',  '010', '$minus_amt','$billcycle', '$billcode')") or die(mysqli_error($conn));

//      interest part in actran


            if ($intamt > 0) {
                mysqli_query($conn, "INSERT INTO `$accdb`(yearno, forac, series, date, newbill, vouchno, billno, source, trnno, accode, grpcode, amount, `billcycle`, `billcode`)
                     VALUES ('$yearno', 'M', '$series', '$date', '$qbill', '$vouchno', '$billno', 'INT', '001', '$zacinterest',  '011', '$intamt','$billcycle', '$billcode')") or die(mysqli_error($conn));

                $n_intamt = $intamt * -1;
                mysqli_query($conn, "INSERT INTO `$accdb`(yearno, forac, series, date, flatno, newbill, vouchno, billno, source, trnno, accode, grpcode, amount, `billcycle`, `billcode`)
                     VALUES ('$yearno', 'M', '$series', '$date', '$qbill', '$flatno', '$vouchno', '$billno', 'INT', '002', '$member',  '010', '$n_intamt','$billcycle', '$billcode')") or die(mysqli_error($conn));
            }
//      main part 
//            browse($amtac);
//            browse($amtprev);
//            browse($intprev);
//            browse($arrears);

            if ($amtac != 0) {
                if ($amtprev >= $intprev) {
                    $arrears2 = abs($amtac);
                    $intprev2 = 0;
                } else {
                    $intprev2 = $intprev - $amtprev;
                    $arrears2 = $arrears + $amtprev;
                }
            } else {
                $arrears2 = 0;
                $intprev2 = 0;
            }


            if ($adv > 0) {
                $arrears2 = 0;
                $intprev2 = 0;
            }

//
//            browse($totamt);
//            browse($intamt);
//            browse($arrears2);
//            browse($intprev2);
//            browse($intpaid);
//            browse($intamt);
//            $tot = $total + $abal + $intprev2 + $intpaid + $intamt;
//        browse($billno);

            $totnew = $totamt;

            $tot = $totnew + $intamt + $arrears2 + $intprev2 - $adv;

            mysqli_query($conn, "INSERT INTO `$maindb`(yearno, `billno`, `newbill`, `trandate`, `vouchno`,`society`, `accode`, `flatno`, `billdate`, `billcode`, `billcycle`, `billamt`, 
                                           `arrears`, `intprev`, `total`, `intamt`, `advance`,`billpayd`,`billpaym`, `adduser`, `adddate`,`addtime` )
                                        VALUES('$yearno', '$billno', '$qbill', '$checkdate', '$vouchno', '$series', '$member', '$flatno', '$date', '$billcode', '$billcycle', '$totnew',
                                               '$arrears2', '$intprev2', '$tot', '$intamt', '$adv','$billpayd','$billpaym', '$adduser', '$adddate', '$addtime')") or die(mysqli_error($conn));
            $qbill = $qbill + 1;
        }

        mysqli_query($conn, "UNLOCK TABLES");
        browse('BILL GENERATED SUCCESSFULLY');
//    header('Location:  genbill.php');
    }
}
?>

