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

$zdb = $_SESSION["zdbyear_" . $dbname];
$formname = "Loan Status";
mysqli_select_db($conn, $zdb);

$cond = "";
$ccond = "";
$cocond = "";
$rtit = "";

if (!empty($_POST["cmbemp"])) {
    $party = trim($_POST["cmbemp"]);
    $ccond = "AND a.`accode`='$party'";
    $rtit = "Single Employee";
}


$todate = $_POST['tdate'];


if (!empty($_POST["cmbdept"])) {
    $sup = trim($_POST["cmbdept"]);
    $cocond = "AND b.`deptcode`='$sup'";
    $getdept = mysqli_query($conn, "SELECT * FROM `dept` WHERE code='$_POST[cmbdept]'") or die(mysqli_error($conn));
    $dept = mysqli_fetch_array($getdept);
    $rtit = "<b>Department Wise : " . $dept["name"] . "</b>";
}

$monno = $_POST["month"];

$get = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mvouchdt, b.name as partyname FROM `loan` a, accmast b 
                                   WHERE a.`vouchdt`<= '$todate' and a.accode = b.accode $ccond $cocond 
                                   ORDER BY a.`vouchno`") or die(mysqli_error($conn));
                                   

//$get2 = mysqli_query($conn, "SELECT *, SUM(salary)as totsalary, SUM(basic)as totbasic, SUM(convey)as totconvey, SUM(trav)as tottrav,
//                     SUM(otamt)as tototamt, SUM(miscamt)as totmiscamt, SUM(monsalary)as totmonsal, SUM(loan)as totloan,
//                     SUM(advance)as totadvance, SUM(miscdedn)as totmiscdedn, SUM(total_ded)as totdedn, SUM(net_Sal)as totnet FROM `payproc` WHERE monthno='$monno' $ccond $cond $cocond ORDER BY accode") or die(mysqli_error($conn));

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

    include("includes/linkshead.php");
    ?>

    <style>
        @media print
        {    
            .no-print, .no-print *
            {
                display: none !important;
            }
        }
        .th1{
            background-color: #037D95;            
            /*background-color: #00AAFC;*/           
        }
        .th2{
            background-color: #88D8BF;            
        }

        .black{
            color: black;
        }

        .grey{
            color: grey;
        }

    </style>
    </head>

    </head>
    <body>
        <div class="container">
            <?php require("includes/head_rep.php"); ?>
            <div class="row1">
                <table style="width:100%;background: white;" class="table table-condensed table-hover table-striped table-bordered">
                    <thead class="mySearchTr">
                        <!--<tr style="">-->
                        <tr id="font">
                            <th style="text-align:left;"  width="5%"><b>Sr.</b></th>
                            <th style="text-align:left;"  width="30%"><b>Employee Name</b></th>
                            <th style="text-align:center;" width="10%"><b>Loan No.</b></th>
                            <th style="text-align:center;" width="10%"><b>Loan Date</b></th>
                            <th style="text-align:right;" width="10%"><b>Loan Amount</b></th>
                            <th style="text-align:right;" width="10%"><b>Installment</b></th>
                            <th style="text-align:right;" width="10%"><b>Paid</b></th>
                            <th style="text-align:right;" width="10%"><b>Balance</b></th>
                        </tr>
                    </thead>
                    
                    <tbody>
                        <?php
                        $ctr = 0;
                        $totbasic = $totda = $tothra = $totta = $totoa = $totsal = $totpfund = $totproftax = $totloan = $totadv = $totesic = $totded = $totnetsal = 0;

                        while ($row = mysqli_fetch_assoc($get)) {
                            $ctr++;
                            $getloanpaid = mysqli_query($conn, "SELECT sum(paidamt) as paidamt FROM `loanpay` WHERE vouchno='$row[vouchno]' ORDER BY accode") or die(mysqli_error($conn));
                            $floanpaid = mysqli_fetch_array($getloanpaid);

                            $balloan = $row['amount'] - $floanpaid['paidamt'];

                            echo "<tr>";
                            echo "<th style='color:#5D5F63; text-align:left;'  >$ctr</th>";
                            echo "<th style='color:#5D5F63; text-align:left;'  >$row[partyname]</th>";
                            echo "<th style='color:#5D5F63; text-align:center;' >$row[vouchno]</th>";
                            echo "<th style='color:#5D5F63; text-align:center;' >$row[mvouchdt]</th>";
                            echo "<th style='color:#5D5F63; text-align:right;' >$row[amount]</th>";
                            echo "<th style='color:#5D5F63; text-align:right;' >$row[insamt]</th>";
                            echo "<th style='color:#5D5F63; text-align:right;' >$floanpaid[paidamt]</th>";
                            echo "<th style='color:#5D5F63; text-align:right;' >$balloan</th>";
                            echo "</tr>";

                            $totloan = $totloan + $row['amount'];
                            $totinst = $totinst + $row['insamt'];
                            $totpaid = $totpaid + $floanpaid['paidamt'];
                            $totballoan = $totballoan + $balloan;

                            if ($_POST['type'] == 2) {
                                echo "<td colspan='3'>";
                                echo "<tr>";
                                echo "<th class='black'>Month</th>";
                                echo "<th class='black'>Year</th>";
                                echo "<th class='black'>Paid Amount</th>";
                                echo "</tr>";

                                $getlpaid = mysqli_query($conn, "SELECT * FROM `loanpay` WHERE vouchno='$row[vouchno]' order by loandt") or die(mysqli_error($conn));
                                $total = 0;
                                while ($lpaid = mysqli_fetch_array($getlpaid)) {

                                    $date = strtotime($lpaid['loandt']);
                                    $monthname = date("F", $date);
                                    $year = date('Y', $date);
                                    echo "<tr >";
                                    echo "<th class='grey'>$monthname</th>";
                                    echo "<th class='grey'>$year</th>";
                                    echo "<th class='grey text-right'>$lpaid[paidamt]</th>";
                                    echo "</tr>";
                                    $total = $total + $lpaid['paidamt'];
                                }
                                echo "<tr>";
                                echo "<th class='grey text-right' colspan='2'>Total</th>";
                                echo "<th class='grey text-right'>$total</th>";
                                echo "</tr>";
                            }
                            echo "</td>";
                            $totnetsal = $totnetsal + $row['net_sal'];
                        }
                        
                        echo "<tr>";
                        echo "<th style='text-align:right;'></th>";
                        echo "<th style='text-align:right;'  ></th>";
                        echo "<th style='text-align:right;'  ></th>";
                        echo "<th style='text-align:right;  color:red'  valign='top' scope='col' width='80'>Total</th>";
                        echo "<th style='text-align:right; color:red'>$totloan</th>";
                        echo "<th style='text-align:right; color:red'>$totinst</th>";
                        echo "<th style='text-align:right; color:red'>$totpaid</th>";
                        echo "<th style='text-align:right; color:red'>$totballoan</th>";
                        echo "</tr>";
                        ?>


                    </tbody>
                </table>
            </div>
        </div>
    </body>
    </html>

    <?php
}

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


    //$contents .= "Employee Name,Basic,DA,HRA,TA,OA,Total Salary,Prov. Fund,Prof. Tax,Loan,Advance,ESIC,Total Deduction,Net Salary\n";
    $contents .= "Sr.,Employee Name,Loan No.,Loan Date,Loan Amount,Installment,Paid,Balance\n";

    $ctr = 0;
    $totbasic = $totda = $tothra = $totta = $totoa = $totsal = $totpfund = $totproftax = $totloan = $totadv = $totesic = $totded = $totnetsal = 0;

    while ($row = mysqli_fetch_assoc($get)) {
        $getname = mysqli_query($conn, "SELECT * FROM `accmast` WHERE accode=$row[accode] ORDER BY accode") or die(mysqli_error($conn));
        $party = mysqli_fetch_array($getname);

        $ctr++;
        $getloanpaid = mysqli_query($conn, "SELECT sum(loanamt) as paidamt FROM `loanpay` WHERE vouchno='$row[vouchno]' ORDER BY accode") or die(mysqli_error($conn));
        $floanpaid = mysqli_fetch_array($getloanpaid);

        $balloan = $row['loanamt'] - $floanpaid['paidamt'];

//                        mysqli_select_db($conn, $zdb);
        $getname = mysqli_query($conn, "SELECT * FROM `accmast` WHERE accode='$row[accode]' ORDER BY accode") or die(mysqli_error($conn));
        $party = mysqli_fetch_array($getname);



        $contents .= "$ctr" . ",";
        $contents .= "$party[name]" . ",";
        $contents .= "$row[vouchno]" . ",";
        $contents .= "$row[mvouchdt]" . ",";
        $contents .= "$row[loanamt]" . ",";
        $contents .= "$row[instamt]" . ",";
        $contents .= "$floanpaid[paidamt]" . ",";
        $contents .= "$balloan" . "\n";


        $totloan = $totloan + $row['loanamt'];
        $totinst = $totinst + $row['instamt'];
        $totpaid = $totpaid + $floanpaid['paidamt'];
        $totballoan = $totballoan + $balloan;


        if ($_POST['type'] == 2) {

            $contents .= "Month,Year,Paid Amount\n";

            $getlpaid = mysqli_query($conn, "SELECT * FROM `loanpay` WHERE vouchno='$row[vouchno]' order by loandt") or die(mysqli_error($conn));
            $total = 0;
            while ($lpaid = mysqli_fetch_array($getlpaid)) {

                $date = strtotime($lpaid['loandt']);
                $monthname = date("F", $date);
                $year = date('Y', $date);


                $contents .= "$monthname" . ",";
                $contents .= "$year" . ",";
                $contents .= "$lpaid[loanamt]" . "\n";

                $total = $total + $lpaid['loanamt'];
            }

            $contents .= "" . ",";
            $contents .= "Total" . ",";
            $contents .= "$total" . "\n";
        }



        $totnetsal = $totnetsal + $row['net_sal'];
    }
//                        echo "
//                        <tr>
//                          <td colspan='15'>
//                              <hr style='border-top: 1px solid #039;'/>
//                          </td>
//                       </tr>";
    // $row2 = mysqli_fetch_array($get2);

    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "Total" . ",";
    $contents .= "$totloan" . ",";
    $contents .= "$totinst" . ",";
    $contents .= "$totpaid" . ",";
    $contents .= "$totballoan" . "\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;
    }
}


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

    if (!empty($_POST["cmbemp"])) {
        $party = trim($_POST["cmbemp"]);
        $ccond = "AND `accode`='$party'";
        $rtit = "Single Employee";
    }


    $todate = $_POST['tdate'];


    if (!empty($_POST["cmbdept"])) {
        $sup = trim($_POST["cmbdept"]);
        $cocond = "AND `deptcode`='$sup'";
        $getdept = mysqli_query($conn, "SELECT * FROM `dept` WHERE code='$_POST[cmbdept]'") or die(mysqli_error($conn));
        $dept = mysqli_fetch_array($getdept);
        $rtit = "<b>Department Wise : " . $dept["name"] . "</b>";
    }



    $monno = $_POST["month"];

    $get = mysqli_query($conn, "SELECT *, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mvouchdt FROM `loan` WHERE `vouchdt`<='$todate' $ccond $cocond  ORDER BY `vouchno`") or die(mysqli_error($conn));

//$get2 = mysqli_query($conn, "SELECT *, SUM(salary)as totsalary, SUM(basic)as totbasic, SUM(convey)as totconvey, SUM(trav)as tottrav,
//                     SUM(otamt)as tototamt, SUM(miscamt)as totmiscamt, SUM(monsalary)as totmonsal, SUM(loan)as totloan,
//                     SUM(advance)as totadvance, SUM(miscdedn)as totmiscdedn, SUM(total_ded)as totdedn, SUM(net_Sal)as totnet FROM `payproc` WHERE monthno='$monno' $ccond $cond $cocond ORDER BY accode") or die(mysqli_error($conn));

    require("library/fpdf/fpdf.php");

    class pdf extends fpdf {

        function Footer() {

//Position at 1.5 cm from bottom

            $this->SetY(-15);

//Arial italic 8

            $this->SetFont('Arial', 'I', 8);

//Page number

            $this->Cell(0, 10, 'Page ' . $this->PageNo() . '/{nb}', 0, 0, 'C');
        }

    }

    $pdf = new pdf('L', 'mm', 'A4');

    $pdf->AliasNbPages();

    $pdf->AddPage();
//    include(include/head_rep_pdf2.php"");
    $pdf->SetFont('Arial', '', 18);
    $pdf->Cell(100, 7, "$comp[compname]", 0, 'L');
    $pdf->Ln();
    $pdf->SetFont('Arial', '', 9);
    $pdf->Cell(100, 7, "OCEAN HOUSE, RAJENDRA GURU NAGAR, NEAR NEW NERAL POLICE STATION NERAL DIST-RAIGAD", 0, 'L');
    $address = str_replace('       ', ' ', $comp[add1] . $comp[add2] . $comp[add3]);
    $pdf->Cell(100, 7, $address, 0, 'L');
    $pdf->Ln();
    $pdf->SetFont('Arial', 'I', 10);
    $pdf->SetTextColor(000, 000, 000);
    $pdf->SetFillColor(249, 244, 245);

    $pdf->Cell(200, 7, "Print - " . $formname, 0, 'L');
    $pdf->Cell(0, 7, "Report Date - " . Date("d-m-y"), 0, 'L');
    $pdf->Ln();
    $pdf->SetFont('Arial', '', 10);
    $pdf->SetTextColor(000, 000, 000);
    $pdf->Cell(50, 10, 'From Date-' . $fdate, 0);
    $pdf->SetFont('Arial', '', 10);
    $pdf->SetTextColor(000, 000, 000);
    $pdf->Cell(50, 10, 'To Date-' . $tdate, 0);
    $pdf->Ln();
    
    
    $pdf->SetFont('Arial', '', 8);
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFillColor(3, 125, 149);
    $pdf->Cell(13, 7, "Sr.", 1, 0, '', 1);
    $pdf->Cell(120, 7, "Employee Name", 1, 0, '', 1);
    $pdf->Cell(25, 7, "Loan No.", 1, 0, 'R', 1);
    $pdf->Cell(25, 7, "Loan Date", 1, 0, 'R',1);
    $pdf->Cell(25, 7, "Loan Amount", 1, 0, 'R', 1);
    $pdf->Cell(25, 7, "Installment", 1, 0, 'R', 1);
    $pdf->Cell(25, 7, "Paid", 1, 0, 'R', 1);
    $pdf->Cell(25, 7, "Balance", 1, 0, 'R', 1);

    $pdf->SetTextColor(0, 0, 0);
    $pdf->Ln();

    $ctr = 0;
    $totbasic = $totda = $tothra = $totta = $totoa = $totsal = $totpfund = $totproftax = $totloan = $totadv = $totesic = $totded = $totnetsal = 0;

    while ($row = mysqli_fetch_assoc($get)) {
        $ctr++;
        $getloanpaid = mysqli_query($conn, "SELECT sum(loanamt) as paidamt FROM `loanpay` WHERE vouchno='$row[vouchno]' ORDER BY accode") or die(mysqli_error($conn));
        $floanpaid = mysqli_fetch_array($getloanpaid);

        $balloan = $row['loanamt'] - $floanpaid['paidamt'];

//                        mysqli_select_db($conn, $zdb);
        $getname = mysqli_query($conn, "SELECT * FROM `accmast` WHERE accode='$row[accode]' ORDER BY accode") or die(mysqli_error($conn));
        $party = mysqli_fetch_array($getname);

        $totloan = $totloan + $row['loanamt'];
        $totinst = $totinst + $row['instamt'];
        $totpaid = $totpaid + $floanpaid['paidamt'];
        $totballoan = $totballoan + $balloan;

        $pdf->SetFont('Arial', '', 8);
        $pdf->Cell(13, 7, "$ctr", 1);
        $pdf->Cell(120, 7, "$party[name]", 1);
        $pdf->Cell(25, 7, "$row[vouchno]", 1, 0, 'R');
        $pdf->Cell(25, 7, "$row[mvouchdt]", 1, 0, 'R');
        $pdf->Cell(25, 7, "$row[loanamt]", 1, 0, 'R');
        $pdf->Cell(25, 7, "$row[instamt]", 1, 0, 'R');
        $pdf->Cell(25, 7, "$floanpaid[paidamt]", 1, 0, 'R');
        $pdf->Cell(25, 7, "$balloan", 1, 0, 'R');
        $pdf->Ln();
        $ctr++;
    }

    $pdf->SetFont('Arial', '', 8);
    $pdf->Cell(158, 7, "Total", 1, 0, 'R');
    $pdf->Cell(25, 7, "",  1);
    $pdf->Cell(25, 7, "$totloan", 1, 0, 'R');
    $pdf->Cell(25, 7, "$totinst", 1, 0, 'R');
    $pdf->Cell(25, 7, "$totpaid", 1, 0, 'R');
    $pdf->Cell(25, 7, "$totpaid", 1, 0, 'R');
    $pdf->Ln();
    $ctr++;

    $pdf->Output("Loan Status.pdf", "D");
}
