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

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);
$maindb = 'transacn';
$zroot = $_SESSION["zdbroot"];
$formname = 'Product Inward / Outward History';
$file = 'prodhist.php';

if ($_GET["pcode"] != '') {
    $prodcode = $_GET["pcode"];
} else {
    $prodcode = $_POST["prodcode"];
}

$prodcode = $_POST["product"];
$godown = $_POST["godown"];
$type = $_POST["type"];

$pcodecond = '';
$prodcond = '';
$grpcond = '';
$godwncond = '';

$godwncond = '';
$locncond = '';

$godown = $_POST["godown"];
$location = $_POST["location"];
if (!empty($godown)) {
    $godwncond = " AND `godown` = '$godown' ";
}
if (!empty($location)) {
    $locncond = " AND `location` = '$location' ";
}


if (!empty($prodcode)) {
    $pcodecond = " AND `code` = '$prodcode' ";
    $pcodecond2 = " AND `itemcode` = '$prodcode' ";
}

if (!empty($prodgrp)) {
    $grpcond = " AND `prodgrp` = '$prodgrp' ";
}


if (!empty($_GET['todate'])) {
    $todate = trim($_GET["todate"]);
} else {
    $todate = $_POST["todt"];
}
$mfrmdate = $_POST['frmdt'];
$as_on_date = date("d-m-Y", strtotime($todate));

$mfdt = date("d-m-Y", strtotime($mfrmdate));
$mtdt = date("d-m-Y", strtotime($todate));

$tcond = '';

$mainqry = mysqli_query($conn, "SELECT SUM(qty) AS `sqty`, itemcode, source, vouchno, godown, location, DATE_FORMAT(vouchdt , '%d-%m-%Y') as mydt FROM `transacn`
                                WHERE `itemcode`='$prodcode' AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$todate' $godwncond $locncond
                                group by vouchno, source,godown,location order by vouchdt, ordno")or die(mysqli_error($conn));
//print table($mainqry);
$gproduct2 = mysqli_query($conn, "SELECT code , name FROM `product` WHERE code = '$prodcode' ")or die(mysqli_error($conn));
$fproduct = mysqli_fetch_array($gproduct2);

$getno = mysqli_num_rows($mainqry);
if (isset($_POST["view"]) || ($_GET['view'])) {

    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
        th{
            background: #7E827E;
        }
        .tsize{
            font-size:18px;
            padding : 6px;
            color:white
        }
        tr td{
            font-size:14px;
        }

    </style>
    </head>
    <body>
        <div class="container" style="width:70%">
            <?php include("includes/head_rep.php"); ?>
            <div class="row">
                <div class="col-lg-12 pad">
                    <table class="table table-bordered">
                        <tr class="mySearchTr">
                            <td style="color: white"><b>Product : <?php echo $fproduct['name']; ?></b></td>
                        </tr>
                    </table>


                    <table style="background: white;width: 100%" class="table table-condensed table-hover table-striped table-bordered">
                        <thead class="mySearchTr">
                            <tr>
                                <th style="width:3%;">Sr</th>
                                <th style="width:12%;">Date</th>
                                <th style="width:4%;"></th>
                                <th style="width:6%;">Vouchno</th>
                                <th style="width:15%;">Description</th>
                                <th style="width:15%;">Godown</th>
                                <th style="width:15%;">Location</th>
                                <th style="width:10%;">Contractor</th>
                                <th style="width:9%;" class="text-right">Add</th>
                                <th style="width:9%;" class="text-right">Less</th>
                                <th style="width:9%;" class="text-right">Balance</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $add = 0;
                            $less = 0;
                            $balance = $totinwd = $totadd = $totless = 0;

                            while ($fproduct = mysqli_fetch_array($mainqry)) {
                                $prodcode = $fproduct['itemcode'];
                                $gocode = $fproduct['godown'];
                                $loca = $fproduct['location'];
                                $date = $fproduct['mydt'];
                                $src = $fproduct['source'];
                                $vouchno = $fproduct['vouchno'];

                                $add = "";
                                $less = "";
                                $faccode = $partyname = "";

                                if ($fproduct['sqty'] > 0) {
                                    $add = $fproduct["sqty"];
                                } else if ($fproduct['sqty'] < 0) {
                                    $less = abs($fproduct['sqty']);
//                                    $less2 = $fproduct['sqty'];
                                }
                                $accode = "";
                                $source = "";
                                switch ($src) {
                                    case "OP":
                                        $source = "OPENING";
                                        $faccode = "";
                                        break;

                                    case "SL":
                                        $source = "SALE";
                                        $getaccode = mysqli_query($conn, "SELECT `party` FROM `salemain` WHERE vouchno = '$vouchno'") or die(mysqli_error($conn));
                                        $faccode = mysqli_fetch_array($getaccode);
                                        $accode = $faccode['party'];
                                        break;

                                    case "PU":
                                        $source = "PURCHASE";
                                        $getaccode = mysqli_query($conn, "SELECT `party` FROM `purmain` WHERE vouchno = '$vouchno'") or die(mysqli_error($conn));
                                        $faccode = mysqli_fetch_array($getaccode);
                                        $accode = $faccode['party'];
                                        break;

                                    case "PR":
                                        $source = "DEBIT NOTE";
                                        $getaccode = mysqli_query($conn, "SELECT `party` FROM `debmain` WHERE vouchno = '$vouchno'") or die(mysqli_error($conn));
                                        $faccode = mysqli_fetch_array($getaccode);
                                        $accode = $faccode['party'];
                                        break;

                                    case "SR":
                                        $source = "CREDIT NOTE";
                                        $getaccode = mysqli_query($conn, "SELECT `party` FROM `credmain` WHERE vouchno = '$vouchno'") or die(mysqli_error($conn));
                                        $faccode = mysqli_fetch_array($getaccode);
                                        $accode = $faccode['party'];
                                        break;

                                    case "OU":
                                        $source = "OUTWARD";
                                        $accode = "";
                                        break;

                                    case "EX":
                                        $source = "EXCESS";
                                        $accode = "";
                                        break;

                                    case "IN":
                                        $source = "INWARD";
                                        $accode = "";
                                        break;

                                    case "DM":
                                        $source = "DAMAGED";
                                        $accode = "";
                                        break;
                                    case "TR":
                                        $source = "TRANSFER";
                                        $accode = "";
                                        break;
                                    default :
                                        $source = "";
                                        $accode = "";
                                        break;
                                    
                                }

//                                if (!empty($accode)) {
//                                    $getpartyname = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE `accode`='$accode'")or die(mysqli_error($conn));
//                                    $fparty = mysqli_fetch_array($getpartyname);
//                                    $partyname = $fparty['name'];
//                                } else {
//                                    $partyname = '';
//                                }

                                $ctr++;

                                if ($ctr == 1) {
                                    $getopening = mysqli_query($conn, "SELECT qty from transacn where source = 'OP' $pcodecond2 ")or die(mysqli_error($conn));
                                    $fopening = mysqli_fetch_array($getopening);
                                    $openqty = $fopening['qty'];


                                    $getopen = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `transacn` WHERE `vouchdt` < '$mfrmdate' $pcodecond2 $godwncond $locncond") or die(mysqli_error($conn));
                                    $iopen = mysqli_fetch_array($getopen);
                                    $openqty2 = $iopen['sqty'] + $openqty;

                                    Echo"<tr>";
                                    Echo"<td class=''>$ctr</td>";
                                    Echo"<td class=''></td>";
                                    Echo"<td class=''>OP</td>";
                                    Echo"<td class=''></td>";
                                    Echo"<td>OPENING</td>";
                                    Echo"<td></td>";
                                    Echo"<td></td>";
                                    if ($openqty2 > 0) {
                                        Echo"<td class='text-right'>$openqty2</td>";
                                        Echo"<td class='text-right'></td>";
                                        $totadd = $totadd + $openqty2;
                                    } else {
                                        Echo"<td class='text-right'></td>";
                                        Echo"<td class='text-right'>" . abs($openqty2) . "</td>";
                                        $totless = $totless + abs($openqty2);
                                    }
                                    Echo"<td class='text-right'></td>";
                                    Echo"</tr>";
                                    $ctr++;
                                    $balance = $openqty2;
                                }

                                $getgodw = mysqli_query($conn, "SELECT `name` FROM `godown` WHERE code = '$gocode'") or die(mysqli_error($conn));
                                $fetgodw = mysqli_fetch_array($getgodw);
                                $godown = $fetgodw['name'];

                                $getloca = mysqli_query($conn, "SELECT `name` FROM `location` WHERE code = '$loca'") or die(mysqli_error($conn));
                                $fetloca = mysqli_fetch_array($getloca);
                                $locaname = $fetloca['name'];

                                $contractor = '';
                                if ($src == 'OU') {
                                    $getout = mysqli_query($conn, "SELECT a.accode, a.name, b.staff FROM `accmast` a, `outmain_mi` b WHERE a.accode = b.staff 
                                                               and b.vouchno = '$vouchno' ")or die(mysqli_error($conn));
                                    $fout = mysqli_fetch_array($getout);
                                    $contractor = $fout['name'];
                                }

                                $balance = $balance + $add - $less;

                                Echo"<tr>";
                                Echo"<td class=''>$ctr</td>";
                                Echo"<td class=''>$date</td>";
                                Echo"<td class=''>$src</td>";
                                Echo"<td class=''>$vouchno</td>";
                                Echo"<td>$source</td>";
                                Echo"<td>$godown</td>";
                                Echo"<td>$locaname</td>";
                                Echo"<td>$contractor</td>";
                                Echo"<td class='text-right'>$add</td>";
                                Echo"<td class='text-right'>$less</td>";
                                Echo"<td class='text-right'>" . $balance . "</td>";
                                Echo"</tr>";

                                $totadd = $totadd + $add;
                                $totless = $totless + $less;
//                                $totless2 = $totless2 + $less2;
                            }
//                            if ($openqty2 > 0) {
//                                
//                            }
//                            $totadd = $openqty2;
                            $totbal = $totadd - $totless;

                            Echo "<tr style='background-color:#7E827E'>";
                            Echo "<td class='text-right tsize' colspan = '8'>Total</td>";
                            Echo "<td class='text-right tsize'><b>$totadd</b></td>";
                            Echo "<td class='text-right tsize'><b>$totless</b></td>";
                            Echo "<td class='text-right tsize'><b>$totbal</b></td>";
                            Echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div> 
            </div>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>
    </html>
    <?php
}
if (isset($_POST["excel"])) {

    $contents .= "Producr :  $fproduct[name]\n";

    $contents .= "Sr,Date,,Vouchno,Description,Party,Add,Less,Balance\n";
    $ctr = 0;
    $add = 0;
    $less = 0;
    $balance = $totinwd = $totadd = $totless = 0;
    while ($fproduct = mysqli_fetch_array($mainqry)) {
        $prodcode = $fproduct['itemcode'];
        $date = $fproduct['mydt'];
        $src = $fproduct['source'];
        $vouchno = $fproduct['vouchno'];

        $add = "";
        $less = "";
        $faccode = $partyname = "";

        if ($fproduct['sqty'] > 0) {
            $add = $fproduct["sqty"];
        } else if ($fproduct['sqty'] < 0) {
            $less = abs($fproduct['sqty']);
            $less2 = $fproduct['sqty'];
        }

        switch ($src) {
            case "OP":
                $source = "OPENING";
                $faccode = "";
                break;

            case "SL":
                $source = "SALE";
                $getaccode = mysqli_query($conn, "SELECT `party` FROM `salemain` WHERE vouchno = '$vouchno'") or die(mysqli_error($conn));
                $faccode = mysqli_fetch_array($getaccode);
                $accode = $faccode['party'];
                break;

            case "SLDE":
                $source = "DEEMED SALE";
                $getaccode = mysqli_query($conn, "SELECT `party` FROM `deexmain` WHERE vouchno = '$vouchno'") or die(mysqli_error($conn));
                $faccode = mysqli_fetch_array($getaccode);
                $accode = $faccode['party'];
                break;


            case "SLEX":
                $source = "EXPORT SALE";
                $getaccode = mysqli_query($conn, "SELECT `partyname` FROM `salemainexp` WHERE vouchno = '$vouchno'") or die(mysqli_error($conn));
                $faccode = mysqli_fetch_array($getaccode);
                $accode = $faccode['partyname'];
                break;

            case "SLON":
                $source = "ONLINE SALE";
                $getaccode = mysqli_query($conn, "SELECT `party` FROM `slonlmain` WHERE vouchno = '$vouchno'") or die(mysqli_error($conn));
                $faccode = mysqli_fetch_array($getaccode);
                $accode = $faccode['party'];
                break;

            case "IN":
                $source = "INWARD";
                $accode = "";
                break;

            case "INDE":
                $source = "INWARD DEEMED";
                $accode = "";
                break;

            case "INEX":
                $source = "INWARD EXPORT";
                $accode = "";
                break;

            case "DM":
                $source = "DAMAGED";
                $accode = "";
                break;

            case "TR":
                $source = "TRANSFER";
                $accode = "";
                break;
        }
        if (!empty($accode)) {
            $getpartyname = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE `accode`='$accode'")or die(mysqli_error($conn));
            $fparty = mysqli_fetch_array($getpartyname);
            $partyname = $fparty['name'];
        }

        $ctr++;

        if ($ctr == 1) {
            $getopening = mysqli_query($conn, "SELECT qty from transacn where source = 'OP' $pcodecond2 $godwncond ")or die(mysqli_error($conn));
            $fopening = mysqli_fetch_array($getopening);
            $openqty = $fopening['qty'];


            $getopen = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `transacn` WHERE vouchno != ''  
                                                             and `vouchdt` < '$mfrmdate' $pcodecond2 $godwncond  ") or die(mysqli_error($conn));
            $iopen = mysqli_fetch_array($getopen);
            $openqty2 = $iopen['sqty'] + $openqty;


            if ($openqty2 > 0) {

                $contents .= "$ctr" . ",";
                $contents .= "" . ",";
                $contents .= "OP" . ",";
                $contents .= "" . ",";
                $contents .= "Opening" . ",";
                $contents .= "" . ",";
                $contents .= "$openqty2" . ",";
                $contents .= "" . ",";
                $contents .= "" . "\n";
                $ctr++;
                $balance = $openqty;
                $totadd = $totadd + $openqty2;
            }
        }


        $balance = $balance + $add - $less;

        $contents .= $ctr . ",";
        $contents .= $date . ",";
        $contents .= $src . ",";
        $contents .= $vouchno . ",";
        $contents .= $source . ",";
        $contents .= $partyname . ",";
        $contents .= $add . ",";
        $contents .= $less . ",";
        $contents .= $balance . "\n";


        $totadd = $totadd + $add;
        $totless = $totless + $less;
        $totless2 = $totless2 + $less2;
    }

    $totbal = $totadd - $totless;


    $contents .= ",,,,,Total" . ",";
    $contents .= $totadd . ",";
    $contents .= $totless . ",";
    $contents .= $totbal . "\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;
    }
}
?>
