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

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

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


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

if (!empty($_POST["depart"])) {
    $sup = trim($_POST["depart"]);
    $deptcond = "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>";
}

$month = trim($_POST["month"]);
$year = trim($_POST["year"]);

if (!empty($_POST["status"])) {
    $status = trim($_POST["status"]);
    $statuscond = "AND `paid`='$status'";
}

$monno = $_POST["month"];

$get = mysqli_query($conn, "SELECT *  FROM `payproc` WHERE `monthno`='$monno' GROUP BY `deptcode` ORDER BY accode") or die(mysqli_error($conn));

$get2 = mysqli_query($conn, "SELECT *, SUM(salary) as totsalary, SUM(basic) as totbasic, SUM(da) as totda, SUM(hra) as tothra,
                        SUM(oa) as totoa, SUM(ta) as totta, SUM(salary) as totsalary, SUM(provfund) as totprovfund,sum(totalamt) as tottotalamt,
                        SUM(proftax)as totproftax, SUM(advance)as totadvance, SUM(loan)as totloan, SUM(total_ded)as tot_ded,sum(food) as totfood,
                        SUM(net_Sal)as totnet, SUM(esic) AS totesic,sum(sunsal) as totsun,sum(hoamt) as totho,sum(pramt) as totpr,sum(otamt) as totot
                        FROM `payproc` WHERE monthno = $month and yearno = '$year' $empcond $deptcond 
                        $statuscond GROUP BY `deptcode` ORDER BY divcode") or die(mysqli_error($conn));

$getno = mysqli_num_rows($get);
//print table($get2);
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;
        }
    </style>
    </head>



    </head>

    <body>
        <div class="container-fluid">
            <?php require("includes/head_rep.php"); ?>
            <div class="row1">
                <table style="background: white;" class="table table-condensed  table-bordered">
                    <thead class="mySearchTr">
                        <!--<tr>-->
                        <tr id="font">
                            <th style="text-align:left;" width="80"><b>Department</b></th>
                            <!--<th style="text-align:left;"  width="80"><b>Divsion</b></th>-->
                            <th style="text-align:right;" width="6"><b>No. Emp.</b></th>
                            <!--                            <th style="text-align:right;" width="6"><b>Basic</b></th>
                            <th style="text-align:right;" width="6"><b>DA</b></th>
                            <th style="text-align:right;" width="6"><b>HRA</b></th>
                            <th style="text-align:right;" width="6"><b>TA</b></th>
                            <th style="text-align:right;" width="4"><b>OA</b></th>-->
                            <!-- <th style="text-align:right;" width="6"><b>Salary</b></th>
                            <th style="text-align:right;" width="6"><b>Sun</b></th>
                            <th style="text-align:right;" width="6"><b>Ho</b></th>
                            <th style="text-align:right;" width="6"><b>Pr</b></th>
                            <th style="text-align:right;" width="6"><b>OT/Nt.</b></th> -->

                            <th style="text-align:right;" width="6"><b>Total Salary</b></th>
                            <!-- <th style="text-align:right;" width="6"><b>Prov. Fund</b></th>
                            <th style="text-align:right;" width="6"><b>Prof. Tax</b></th> -->
                            <th style="text-align:right;" width="6"><b>Loan</b></th>
                            <th style="text-align:right;" width="6"><b>Advance</b></th>
                            <!-- <th style="text-align:right;" width="6"><b>Food</b></th>
                            <th style="text-align:right;" width="6"><b>ESIC</b></th> -->
                            <th style="text-align:right;" width="6"><b>Total Deduction</b></th>
                            <th style="text-align:right;" width="6"><b>Net Salary</b></th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        $ctr = 0;
                        $totempno = $totbasic = $totfood = $totda = $tothra = $totta = $totoa = $totsal = $totpfund = $totproftax = $totloan = $totadv = $totesic = $totded = $totnetsal = 0;

                        while ($row = mysqli_fetch_assoc($get2)) {

                            //                        mysqli_select_db($conn, $zdb);
                            $getdept = mysqli_query($conn, "SELECT `name` FROM `dept` WHERE code='$row[deptcode]'") or die(mysqli_error($conn));
                            $fdept = mysqli_fetch_array($getdept);

                            //                            $getdiv = mysqli_query($conn, "SELECT `name` FROM `division` WHERE code='$row[divcode]'") or die(mysqli_error($conn));
                            //                            $fdiv = mysqli_fetch_array($getdiv);

                            $getemp = mysqli_query($conn, "SELECT count(*) as norec FROM `payproc` WHERE `monthno`='$monno' and deptcode='$row[deptcode]' and yearno = '$year' GROUP BY `deptcode`") or die(mysqli_error($conn));
                            $fetemp = mysqli_fetch_array($getemp);
                            $empno = $fetemp['norec'];

                            echo "<tr>";
                            echo "<th style='color:#5D5F63; text-align:left;'   width='80'>$fdept[name]</th>";
                            //                            echo "<th style='color:#5D5F63; text-align:left;'   width='80'>$fdiv[name]</th>";
                            echo "<th style='color:#5D5F63; text-align:right;'  width='6'><b>$empno</b></th>";
                            // echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totsalary]</th>";
                            // echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totsun]</th>";
                            // echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totho]</th>";
                            // echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totpr]</th>";
                            // echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totot]</th>";

                            echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[tottotalamt]</th>";
                            // echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totprovfund]</th>";
                            // echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totproftax]</th>";
                            echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totloan]</th>";
                            echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totadvance]</th>";
                            // echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totfood]</th>";
                            // echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totesic]</th>";
                            echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[tot_ded]</th>";
                            echo "<th style='color:#5D5F63; text-align:right;'  width='6'>$row[totnet]</th>";
                            echo "</tr>";

                            $totempno = $totempno + $empno;
                            $totbasic = $totbasic + $row['totbasic'];
                            $totda = $totda + $row['totda'];
                            $tothra = $tothra + $row['tothra'];
                            $totta = $totta + $row['totta'];
                            $totoa = $totoa + $row['totoa'];
                            $totsal = $totsal + $row['totsalary'];
                            $tottottotalamt = $tottottotalamt + $row['tottotalamt'];
                            $totsun = $totsun + $row['totsun'];
                            $totho = $totho + $row['totho'];
                            $totpr = $totpr + $row['totpr'];
                            $totot = $totot + $row['totot'];
                            $totpfund = $totpfund + $row['totprovfund'];
                            $totproftax = $totproftax + $row['totproftax'];
                            $totloan = $totloan + $row['totloan'];
                            $totadv = $totadv + $row['totadvance'];
                            $totfood = $totfood + $row['totfood'];
                            $totesic = $totesic + $row['totesic'];
                            $totded = $totded + $row['tot_ded'];
                            $totnetsal = $totnetsal + $row['totnet'];
                        }
                        $row2 = mysqli_fetch_array($get2);
                        echo "<tr>";
                        echo "<th style='text-align:right;  color:red'  valign='top' scope='col' width='80'>Total</th>";
                        echo "<th style='text-align:right; color:red'>$totempno</th>";
                        // echo "<th style='text-align:right; color:red'>$totsal</th>";
                        // echo "<th style='text-align:right; color:red'>$totsun</th>";
                        // echo "<th style='text-align:right; color:red'>$totho</th>";
                        // echo "<th style='text-align:right; color:red'>$totpr</th>";
                        // echo "<th style='text-align:right; color:red'>$totot</th>";
                        echo "<th style='text-align:right; color:red'>$tottottotalamt</th>";
                        // echo "<th style='text-align:right; color:red'>$totpfund</th>";
                        // echo "<th style='text-align:right; color:red'>$totproftax</th>";
                        echo "<th style='text-align:right; color:red'>$totloan</th>";
                        echo "<th style='text-align:right; color:red'>$totadv</th>";
                        // echo "<th style='text-align:right; color:red'>$totfood</th>";
                        // echo "<th style='text-align:right; color:red'>$totesic</th>";
                        echo "<th style='text-align:right; color:red'>$totded</th>";
                        echo "<th style='text-align:right; color:red'>$totnetsal</th>";
                        echo "</tr>";
                        ?>


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

    </html>

<?php
}

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

    $contents .= "Department, No. Emp., Salary, Sun, Ho, Pr, OT/Nt., Total Salary, Prov. Fund, Prof. Tax, Loan, Advance, Food, ESIC, Total Deduction, Net Salary \n";

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

    while ($row = mysqli_fetch_assoc($get2)) {

        $getdept = mysqli_query($conn, "SELECT `name` FROM `dept` WHERE code='$row[deptcode]'") or die(mysqli_error($conn));
        $fdept = mysqli_fetch_array($getdept);

        $getemp = mysqli_query($conn, "SELECT count(*) as norec FROM `payproc` WHERE `monthno`='$monno' and deptcode='$row[deptcode]' and yearno = '$year' GROUP BY `deptcode`") or die(mysqli_error($conn));
        $fetemp = mysqli_fetch_array($getemp);
        $empno = $fetemp['norec'];

        $contents .= "$fdept[name]" . ",";
        $contents .= "$empno" . ",";
        $contents .= "$row[totsalary]" . ",";
        $contents .= "$row[totsun]" . ",";
        $contents .= "$row[totho]" . ",";
        $contents .= "$row[totpr]" . ",";
        $contents .= "$row[totot]" . ",";
        $contents .= "$row[tottotalamt]" . ",";
        $contents .= "$row[totprovfund]" . ",";
        $contents .= "$row[totproftax]" . ",";
        $contents .= "$row[totloan]" . ",";
        $contents .= "$row[totadvance]" . ",";
        $contents .= "$row[totfood]" . ",";
        $contents .= "$row[totesic]" . ",";
        $contents .= "$row[tot_ded]" . ",";
        $contents .= "$row[totnet]" . "\n";

        $totempno = $totempno + $empno;
        $totbasic = $totbasic + $row['totbasic'];
        $totda = $totda + $row['totda'];
        $tothra = $tothra + $row['tothra'];
        $totta = $totta + $row['totta'];
        $totoa = $totoa + $row['totoa'];
        $totsal = $totsal + $row['totsalary'];
        $tottottotalamt = $tottottotalamt + $row['tottotalamt'];
        $totsun = $totsun + $row['totsun'];
        $totho = $totho + $row['totho'];
        $totpr = $totpr + $row['totpr'];
        $totot = $totot + $row['totot'];
        $totpfund = $totpfund + $row['totprovfund'];
        $totproftax = $totproftax + $row['totproftax'];
        $totloan = $totloan + $row['totloan'];
        $totadv = $totadv + $row['totadvance'];
        $totfood = $totfood + $row['totfood'];
        $totesic = $totesic + $row['totesic'];
        $totded = $totded + $row['tot_ded'];
        $totnetsal = $totnetsal + $row['totnet'];
    }

    $contents .= "Total" . ",";
    $contents .= "$totempno" . ",";
    $contents .= "$totsal" . ",";
    $contents .= "$totsun" . ",";
    $contents .= "$totoh" . ",";
    $contents .= "$totpr" . ",";
    $contents .= "$totot" . ",";
    $contents .= "$tottottotalamt" . ",";
    $contents .= "$totpfund" . ",";
    $contents .= "$totproftax" . ",";
    $contents .= "$totloan" . ",";
    $contents .= "$totadv" . ",";
    $contents .= "$totfood" . ",";
    $contents .= "$totesic" . ",";
    $contents .= "$totded" . ",";
    $contents .= "$totnetsal" . "\n";

    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"])) {

    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(53, 7, "Department", 1, 0, '', 1);
    $pdf->Cell(18, 7, "Basic", 1, 0, 'R', 1);
    $pdf->Cell(15, 7, "DA", 1, 0, 'R', 1);
    $pdf->Cell(18, 7, "HRA", 1, 0, 'R', 1);
    $pdf->Cell(15, 7, "TA", 1, 0, 'R', 1);
    $pdf->Cell(15, 7, "OA", 1, 0, 'R', 1);
    $pdf->Cell(18, 7, "Total Salary", 1, 0, 'R', 1);
    $pdf->Cell(18, 7, "Prov. Fund", 1, 0, 'R', 1);
    $pdf->Cell(20, 7, "Prof. Tax", 1, 0, 'R', 1);
    $pdf->Cell(15, 7, "Loan", 1, 0, 'R', 1);
    $pdf->Cell(18, 7, "Advance", 1, 0, 'R', 1);
    $pdf->Cell(15, 7, "ESIC", 1, 0, 'R', 1);
    $pdf->Cell(25, 7, "Total Deduction", 1, 0, 'R', 1);
    $pdf->Cell(20, 7, "Net Salary", 1, 0, 'R', 1);
    $pdf->SetFont('Arial', '', 1, 0);

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

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

    while ($row = mysqli_fetch_assoc($get)) {
        //                        mysqli_select_db($conn, $zdb);
        $getdept = mysqli_query($conn, "SELECT `name` FROM `dept` WHERE code='$row[deptcode]'") or die(mysqli_error($conn));
        $fdept = mysqli_fetch_array($getdept);


        $pdf->SetFont('Arial', '', 8);
        $pdf->Cell(53, 7, "$fdept[name]", 1);
        $pdf->Cell(18, 7, "$row[basic]", 1, 0, 'R');
        $pdf->Cell(15, 7, "$row[da]", 1, 0, 'R');
        $pdf->Cell(18, 7, "$row[hra]", 1, 0, 'R');
        $pdf->Cell(15, 7, "$row[ta]", 1, 0, 'R');
        $pdf->Cell(15, 7, "$row[oa]", 1, 0, 'R');
        $pdf->Cell(18, 7, "$row[salary]", 1, 0, 'R');
        $pdf->Cell(18, 7, "$row[provfund]", 1, 0, 'R');
        $pdf->Cell(20, 7, "$row[proftax]", 1, 0, 'R');
        $pdf->Cell(15, 7, "$row[loan]", 1, 0, 'R');
        $pdf->Cell(18, 7, "$row[advance]", 1, 0, 'R');
        $pdf->Cell(15, 7, "$row[esic]", 1, 0, 'R');
        $pdf->Cell(25, 7, "$row[total_ded]", 1, 0, 'R');
        $pdf->Cell(20, 7, "$row[net_sal]", 1, 0, 'R');
        $pdf->Ln();
        $ctr++;

        $totbasic = $totbasic + $row['basic'];
        $totda = $totda + $row['da'];
        $tothra = $tothra + $row['hra'];
        $totta = $totta + $row['ta'];
        $totoa = $totoa + $row['oa'];
        $totsal = $totsal + $row['salary'];
        $totpfund = $totpfund + $row['provfund'];
        $totproftax = $totproftax + $row['proftax'];
        $totloan = $totloan + $row['loan'];
        $totadv = $totadv + $row['advance'];
        $totesic = $totesic + $row['esic'];
        $totded = $totded + $row['total_ded'];
        $totnetsal = $totnetsal + $row['net_sal'];
    }



    $pdf->SetFont('Arial', 'B', 8);
    $pdf->SetTextColor(247, 21, 5);
    $pdf->Cell(53, 7, "Total", 1, 0, 'R');
    $pdf->Cell(18, 7, "$totbasic", 1, 0, 'R');
    $pdf->Cell(15, 7, "$totda", 1, 0, 'R');
    $pdf->Cell(18, 7, "$tothra", 1, 0, 'R');
    $pdf->Cell(15, 7, "$totta", 1, 0, 'R');
    $pdf->Cell(15, 7, "$totoa", 1, 0, 'R');
    $pdf->Cell(18, 7, "$totsal", 1, 0, 'R');
    $pdf->Cell(18, 7, "$totpfund", 1, 0, 'R');
    $pdf->Cell(20, 7, "$totproftax", 1, 0, 'R');
    $pdf->Cell(15, 7, "$totloan", 1, 0, 'R');
    $pdf->Cell(18, 7, "$totadv", 1, 0, 'R');
    $pdf->Cell(15, 7, "$totesic", 1, 0, 'R');
    $pdf->Cell(25, 7, "$totded", 1, 0, 'R');
    $pdf->Cell(20, 7, "$totnetsal", 1, 0, 'R');

    $pdf->Ln();
    $ctr++;

    $pdf->Output("Summary Register.pdf", "D");
}
