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

$formname = "Shift Report";
$file = "shiftrep.php";

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
$zyear = right($zdb, 4);
$zsdate = getzdates($zyear, 'S');
$mtodate = getzdates($zyear, 'E');
$zroot = $_SESSION["zdbroot"];

$getpdg = mysqli_query($conn, "SELECT * FROM `compyear`  where finyear= '$myyear'")or die(mysqli_error($conn));
$fetpdg = mysqli_fetch_array($getpdg);
$zprod1 = $fetpdg["prod1"];
$zprod2 = $fetpdg["prod2"];
$zprod3 = $fetpdg["prod3"];
$zprod4 = $fetpdg["prod4"];
mysqli_select_db($conn, $zdb);

$dtype = $_POST["reptype"];
$name = $_POST["account"];
$shift = $_POST["shift"];
$product = $_POST["product"];
$para = $_POST["para"];

//$fromdate = $_POST["fromdate"];


$comp = $_SESSION["comp_" . $dbname];

$shiftcond = " AND `shift`='$shift' ";
if ($shift == "") {
    browse("Shift Cannot be Empty");
    echo "<script>;"
    . "window.close();"
    . "</script>";
}

$fromdate = $_POST["todate"];
//browse($fromdate);




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

    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold;
        }
        th{
            background: #6082BF;
        }

    </style>
    <!--<script src="MyScript/item.js" type="text/javascript"></script>-->
    </head>
    <body>
        <div class="container">
            <?php include("includes/head_rep.php"); ?>
            <div class="container">
                <div class="row">
                    <div class="col-lg-12 pad">
                        <?php if ($dtype == 2) { ?>
                            <table style="background: white;width: 100%" class="table table-condensed table-hover table-bordered">
                                <h4>Pump Reading Summary    Date : <?= $fromdate ?>    Shift : <?= $shift ?></h4>
                                <thead class="mySearchTr">
                                    <tr>
                                        <th style="width:5%">Pump No.</th>
                                        <th style="width:10%">Product</th>
                                        <th style="width:10%" class="text-right">Opening</th>
                                        <th style="width:10%" class="text-right">Closing</th>
                                        <th style="width:10%" class="text-right">Net Sale</th>

                                    </tr>
                                </thead>
                                <?php
                                $getpms1 = mysqli_query($conn, "SELECT a.*,b.*
                                                           from prdmain a, prdtran b where a.vouchno=b.vouchno and a.vouchdt = '$fromdate' and a.shift='$shift' 
                                                           order by b.pumpno")or die(mysqli_error($conn));

                                $tpumpamt = 0;
                                while ($fetpms1 = mysqli_fetch_array($getpms1)) {

                                    $getpdname = mysqli_query($conn, "select name from product where prodcode = '$fetpms1[prodcode]'")or die(mysqli_error($conn));
                                    $fetpdname = mysqli_fetch_array($getpdname);

//                                    $getpdgrp = mysqli_query($conn, "select sum(credsale) as credsale, sum(cashsale) as cashsale, sum(cashamt) as cashamt from   pumpgrp
//                                                                 where vouchno = '$fetpms[vouchno]' and prodcode = '$fetpms[prodcode]' group by prodcode")or die(mysqli_error($conn));
//                                    $fetpdgrp = mysqli_fetch_array($getpdgrp);
                                    echo "<tr>";
                                    echo "<td>$fetpms1[pumpno]</td>";
                                    echo "<td>$fetpdname[name]</td>";
                                    echo "<td class='text-right'>$fetpms1[opening]</td>";
                                    echo "<td class='text-right'>$fetpms1[closing]</td>";
                                    echo "<td class='text-right'>$fetpms1[sale]</td>";
                                    echo "</tr>";
                                    $tpumpamt = $tpumpamt + $fetpms1['sale'];

//                                $mtankno = $ttankno;
                                }
                                echo "<tr style='height:25px'>";
                                echo "<td></td>";
                                echo "<td></td>";
                                echo "<td class='text-right'></td>";
                                echo "<td class='text-right'>Total</td>";
                                echo "<td class='text-right'>$tpumpamt</td>";
                                echo "</tr>";
                                ?>
                            </table>
                            <br>
                        <?php } ?>




                        <table style="background: white;width: 100%" class="table table-condensed table-hover table-bordered">
                            <h4>Pump Reading Summary    Date : <?= $fromdate ?>    Shift : <?= $shift ?></h4>
                            <thead class="mySearchTr">
                                <tr>
                                    <th style="width:10%">Product</th>
                                    <th style="width:10%" class="text-right">Total</th>
                                    <th style="width:10%" class="text-right">Account</th>
                                    <th style="width:10%" class="text-right">Testing</th>
                                    <th style="width:10%" class="text-right">Cash</th>
                                    <th style="width:10%" class="text-right">Cash Amount</th>

                                </tr>
                            </thead>
                            <?php
                            $getpms = mysqli_query($conn, "SELECT a.*,b.prodcode,sum(b.sale) as total, sum(b.testing) as testing
                                                           from prdmain a, prdtran b where a.vouchno=b.vouchno and a.vouchdt = '$fromdate' and a.shift='$shift' 
                                                           group by b.prodcode")or die(mysqli_error($conn));


                            $tpumpamt = 0;
                            while ($fetpms = mysqli_fetch_array($getpms)) {

                                $getpdname = mysqli_query($conn, "select name from product where prodcode = '$fetpms[prodcode]'")or die(mysqli_error($conn));
                                $fetpdname = mysqli_fetch_array($getpdname);

                                $getpdgrp = mysqli_query($conn, "select sum(credsale) as credsale, sum(cashsale) as cashsale, sum(cashamt) as cashamt from   pumpgrp
                                                                 where vouchno = '$fetpms[vouchno]' and prodcode = '$fetpms[prodcode]' group by prodcode")or die(mysqli_error($conn));
                                $fetpdgrp = mysqli_fetch_array($getpdgrp);
                                echo "<tr>";
                                echo "<td>$fetpdname[name]</td>";
                                echo "<td class='text-right'>$fetpms[total]</td>";
                                echo "<td class='text-right'>$fetpdgrp[credsale]</td>";
                                echo "<td class='text-right'>$fetpms[testing]</td>";
                                echo "<td class='text-right'>$fetpdgrp[cashsale]</td>";
                                echo "<td class='text-right'>$fetpdgrp[cashamt]</td>";
                                echo "</tr>";
                                $tpumpamt = $tpumpamt + $fetpdgrp['cashamt'];

//                                $mtankno = $ttankno;
                            }
                            echo "<tr style='height:25px'>";
                            echo "</tr>";
                            ?>
                        </table>
                        <br>
                        <table style="background: white;width: 100%" class="table table-condensed table-hover table-bordered">
                            <h4>Stock Reading Summary    Date : <?= $fromdate ?>    Shift : <?= $shift ?></h4>

                            <thead class="mySearchTr">
                                <tr>
                                    <th style="width:10%">Product</th>
                                    <th style="width:10%" class="text-right">Total</th>
                                    <th style="width:10%" class="text-right">Account</th>
                                    <th style="width:10%" class="text-right">Testing</th>
                                    <th style="width:10%" class="text-right">Cash</th>
                                    <th style="width:10%" class="text-right">Cash Amount</th>

                                </tr>
                            </thead>
                            <?php
                            $getsd1 = mysqli_query($conn, "select * from stkmain where vouchdt = '$fromdate' $shiftcond")or die(mysqli_error($conn));
                            $fetsd1 = mysqli_fetch_array($getsd1);
                            $stkcode = $fetsd1['vouchno'];


                            $getnoprod = mysqli_query($conn, "select * from stktran where prodcode NOT IN ('$zprod1','$zprod2','$zprod3','$zprod4') and vouchno = '$stkcode' group by prodcode")or die(mysqli_error($conn));
                            $tstkamt = 0;
                            while ($fetnoprod = mysqli_fetch_array($getnoprod)) {

                                $getpdname = mysqli_query($conn, "select name from product where prodcode = '$fetnoprod[prodcode]'")or die(mysqli_error($conn));
                                $fetpdname = mysqli_fetch_array($getpdname);

                                echo "<tr>";
                                echo "<td>$fetpdname[name]</td>";
                                echo "<td class='text-right'>$fetnoprod[sale]</td>";
                                echo "<td class='text-right'>$fetnoprod[acctsale]</td>";
                                echo "<td class='text-right'></td>";
                                echo "<td class='text-right'>$fetnoprod[cashsale]</td>";
                                echo "<td class='text-right'>$fetnoprod[cashamt]</td>";
                                echo "</tr>";
//                                $mtankno = $ttankno;
                                $tstkamt = $tstkamt + $fetnoprod['cashamt'];
                            }

                            echo "<tr style='height:25px'>";
                            echo "</tr>";

//                             echo "<h4>Expense    Date : $fromdate</h4>";
                            Echo "<tr id='boldfont' style='color:blue;padding-top:20px'>";
                            Echo "<td>Expense</td>";
                            Echo "<td>Date : $fromdate</td>";
                            Echo "</tr>";
                            Echo "<tr id='boldfont' style=''>";
                            Echo "<td colspan='2'>Description</td>";
                            Echo "<td  class='text-right'>Amount</td>";
//                            Echo "<td></td>";
                            Echo "</tr>";
                            $texp = 0;
                            $getcashpay = mysqli_query($conn, "select sum(amount) as tamt, party from cashpay where vouchdt = '$fromdate' $shiftcond and party in (select accode from accmast where grpcode = '012') group by party")or die(mysqli_error($conn));
                            while ($fetcashpay = mysqli_fetch_array($getcashpay)) {

                                $pcashamt = $fetcashpay['tamt'];
                                if ($pcashamt > 0) {
                                    $ctr = $ctr + 1;
                                    $getpname = mysqli_query($conn, "select name from accmast where accode = '$fetcashpay[party]'")or die(mysqli_error($conn));
                                    $fetpname = mysqli_fetch_array($getpname);

                                    Echo "<tr id='boldfont'>";
                                    Echo "<td colspan='2'>$fetpname[name]</td>";
                                    Echo "<td  class='text-right'>$pcashamt</td>";
//                                    Echo "<td></td>";
                                    Echo "</tr>";
                                    $texp = $texp + $pcashamt;
                                }
                            }
                            ?>
                        </table>

                        <table style="background: white;width: 100%" class="table table-condensed table-hover table-bordered">
                            <thead class="mySearchTr">
                                <tr>
                                    <th style="width:50%">Particulars</th>
                                    <th style="width:25%" class="text-right">Amount</th>
                                    <th style="width:25%"></th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                //****************  for opening balance

                                IF ($fromdate == $zsdate && $shift == 1) {
                                    $getcomp = mysqli_query($conn, "SELECT * FROM $zroot.`compyear` WHERE `cono`='$comp' and finyear='$myyear'")or die(mysqli_error($conn));
                                    $fetcomp = mysqli_fetch_array($getcomp);
                                    $opbal = $fetcomp['rmcash'];
                                } else {
                                    $getshf = mysqli_query($conn, "select min(code) as shift from shift")or die(mysqli_error($conn));
                                    $fetshf = mysqli_fetch_array($getshf);
                                    $minshift = $fetshf['shift'];
                                    if ($shift == $minshift) {
                                        $getshfbal = mysqli_query($conn, "select max(trandate) as trandate from shiftbal where
                                                                  trandate < '$fromdate'")or die(mysqli_error($conn));
                                        $fetshfbal = mysqli_fetch_array($getshfbal);
                                        $ttrandate = $fetshfbal['trandate'];

                                        $getshfbal2 = mysqli_query($conn, "select max(shift) as shift from shiftbal where
                                                                  trandate = '$ttrandate'")or die(mysqli_error($conn));
                                        $fetshfbal2 = mysqli_fetch_array($getshfbal2);
                                        $pshift = $fetshfbal2['shift'];
                                    } else {
                                        $ttrandate = $fromdate;
                                        $getshfbal2 = mysqli_query($conn, "select max(shift) as shift from shiftbal where
                                                                  trandate = '$ttrandate' and shift < $shift")or die(mysqli_error($conn));
                                        $fetshfbal2 = mysqli_fetch_array($getshfbal2);
                                        $fetshfbaln = mysqli_num_rows($getshfbal2);
                                        if ($fetshfbaln <= 0) {
                                            $getshfbal3 = mysqli_query($conn, "select max(trandate) as trandate from shiftbal where
                                                                  trandate < '$fromdate'")or die(mysqli_error($conn));
                                            $fetshfbal3 = mysqli_fetch_array($getshfbal3);
                                            $ttrandate = $fetshfbal3['trandate'];
                                            $getshfbal4 = mysqli_query($conn, "select max(shift) as shift from shiftbal where
                                                                  trandate = '$ttrandate'")or die(mysqli_error($conn));
                                            $fetshfbal4 = mysqli_fetch_array($getshfbal4);
                                            $pshift = $fetshfbal4['shift'];
                                        }

                                        $getacopen = mysqli_query($conn, "SELECT balance FROM `shiftbal`  where trandate = '$ttrandate' and shift = '$pshift'")or die(mysqli_error($conn));
                                        $fetacopen = mysqli_fetch_array($getacopen);

                                        $balance = $fetacopen["balance"];
                                    }
                                }

                                $ctr = 0;
                                $totqty = 0;


                                $ctr++;
                                $tbalamt = $tpumpamt + $tstkamt;
                                Echo "<tr id='boldfont'>";
                                Echo "<td colspan='2'>Opening Balance</td>";
                                Echo "<td  class='text-right'>$balance</td>";
                                Echo "</tr>";
                                Echo "<tr id='boldfont'>";
                                Echo "<td>Pump Amount</td>";
                                Echo "<td  class='text-right'>$tpumpamt</td>";
                                Echo "</tr>";
                                Echo "<tr id='boldfont'>";
                                Echo "<td>Stock Amount</td>";
                                Echo "<td  class='text-right'>$tstkamt</td>";
                                Echo "<td  class='text-right'>$tbalamt</td>";
                                Echo "</tr>";
                                $tsubtot = $balance + $tbalamt;
                                Echo "<tr id='boldfont'>";
                                Echo "<td style='color:blue'> Sub Total</td>";
                                Echo "<td  class='text-right'></td>";
                                Echo "<td style='color:blue' class='text-right'>$tsubtot</td>";
                                Echo "</tr>";
                                Echo "<tr id='boldfont'>";
                                Echo "<td style=''> Add Pending Cash Recd.</td>";
                                Echo "<td  class='text-right'></td>";
                                Echo "<td style='color:blue' class='text-right'></td>";
                                Echo "</tr>";
                                Echo "<tr id='boldfont'>";
                                Echo "<td style=''>Add Cash Recd.</td>";
                                Echo "<td  class='text-right'></td>";
                                Echo "<td style='' class='text-right'></td>";
                                Echo "</tr>";
                                Echo "<tr id='boldfont'>";
                                Echo "<td style='color:green'>Total</td>";
                                Echo "<td  class='text-right'></td>";
                                Echo "<td style='color:green' class='text-right'>$tsubtot</td>";
                                Echo "</tr>";
                                Echo "<tr id='boldfont'>";
                                Echo "<td style=''>Less Expense</td>";
                                Echo "<td  class='text-right'></td>";
                                Echo "<td style='' class='text-right'>$texp</td>";
                                Echo "</tr>";
                                Echo "<tr id='boldfont'>";
                                Echo "<td style=''>Less Pending Cash</td>";
                                Echo "<td  class='text-right'></td>";
                                Echo "<td style='' class='text-right'></td>";
                                Echo "</tr>";
                                $netamt = $tsubtot - $texp;
                                Echo "<tr id='boldfont'>";
                                Echo "<td style='color:green'>Net Receivable</td>";
                                Echo "<td  class='text-right'></td>";
                                Echo "<td style='color:green' class='text-right'>$netamt</td>";
                                Echo "</tr>";
                                Echo "<tr id='boldfont'>";
                                Echo "<td style=''>Less Advance Transferred</td>";
                                Echo "<td  class='text-right'></td>";
                                Echo "<td style='' class='text-right'>$contamt</td>";
                                Echo "</tr>";
                                $balanceamt = $netamt - $contamt;
                                Echo "<tr id='boldfont'>";
                                Echo "<td style='color:green'>Balance</td>";
                                Echo "<td  class='text-right'></td>";
                                Echo "<td style='color:green' class='text-right'>$balanceamt</td>";
                                Echo "</tr>";
                                ?>
                        </table>


                    </div>
                </div>
            </div>
            <?php include("includes/linksfoot.php");
            ?>
    </body>
    </html>
    <?php
}

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

    $contents .= "Sr,Voucher No.,Date,Vehicle No.,Account Name,Product,Shift\n";

    $ctr = 0;
    $totqty = 0;

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

        $getname = mysqli_query($conn, "SELECT * FROM `accmast` where accode='$pmain[party]'")or die(mysqli_error($conn));
        $fetname = mysqli_fetch_array($getname);

        $nname = mysqli_query($conn, "SELECT * FROM `product` where code='$pmain[product]'")or die(mysqli_error($conn));
        $godwn = mysqli_fetch_array($nname);

        $ctr++;

        $contents .= "$ctr" . ",";
        $contents .= "$pmain[vouchno]" . ",";
        $contents .= "$pmain[mydt]" . ",";
        $contents .= "$pmain[vehicleno]" . ",";
        $contents .= "$fetname[name]" . ",";
        $contents .= "$godwn[name]" . ",";
        $contents .= "$pmain[shift]" . "\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;
    }
}
?>