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

$maindb = 'salemain';
$trandb = 'saletran';

$formname = "Itemwise P/L";
$file = "itemwise.php";

//browse('work in progress....');

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$compcd = $_SESSION["comp_" . $dbname];

$myyear = $_SESSION[$myyear];

mysqli_select_db($conn, $zdb);
$zroot = $_SESSION["zdir"];
//browse($zroot);

$zroot1 = substr($zroot, 0, -4);

mysqli_set_charset($conn, "utf8mb4");

$getprevyear = mysqli_query($conn, "SELECT cono, finyear FROM $zroot.`compyear` where `cono`='$compcd' 
                                           and `finyear`!='$myyear' ORDER BY `lastdate` DESC LIMIT 0,1 ")or die(mysqli_error($conn));
$showprevyear = mysqli_fetch_array($getprevyear);
$prevyear = $showprevyear['finyear'];

//browse($prevyear);

if (!empty($prevyear)) {
    $olddb = $zroot1 . $compcd . '_' . $prevyear;
} else {
    $olddb = "";
}
//browse($olddb);

$name = $_POST['name'];
$salesman = $_POST['salesman'];
$dtype = $_POST['rtype'];

$namecond = '';

//    $godown = trim($_POST["godown"]);
$godowncond = "";

if (!empty($name)) {
    $itemcond = " AND b.itemname = '$name' ";
    $itemcond1 = " AND a.itemname = '$name' ";
    $itemcond2 = " AND `itemname` ='$name' ";
}

if (!empty($salesman)) {
    $salescond = " and a.`salesman`='$salesman' ";
    $salescond1 = " and b.`salesman`='$salesman' ";
}

if (!empty($itemgrp)) {
    $itemgrpcond = " where `prodgrp`='$itemgrp' ";
    $itemgrpcond1 = " and b.`prodgrp`='$itemgrp' ";
}

if (empty($_POST["frmdt"])) {
    browse("From Date Cannot Be Empty");
    header("refresh:1; itemwise.php");
}
if (empty($_POST["todt"])) {
    browse("To Date Cannot Be Empty");
    header("refresh:1; itemwise.php");
}

$mfrmdate = $_POST['frmdt'];
$mtodate = $_POST['todt'];

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

mysqli_query($conn, "drop temporary table if exists itdata") or die(mysqli_error($conn));
mysqli_query($conn, "create temporary table itdata 
                             SELECT 'S' as type, $myyear as yearno, a.vouchdt, DATE_FORMAT(a.`vouchdt`,'%d-%m-%Y') as  mydt, a.addpack as cartage, b.puramt,
                                    a.cartpaid, a.`party`, a.`vouchno`, b.qty, b.sqft, b.calby, b.rate, b.cost, b.`amount`, b.itemcode, b.size, 
                                    b.itemname, b.srno, a.salesman, b.amount as itemdisc, b.amount as itemgst, a.totdisc, a.grossamt, a.addgst, b.supp
                                    FROM $maindb a, $trandb b  WHERE  a.`vouchdt`>= '$mfrmdate'  AND  a.`vouchdt` <='$mtodate' and b.puramt > 0
                                    AND a.`vouchno` = b.`vouchno` $itemcond $salescond 
                             union all
                             SELECT 'R' as type, $myyear as yearno, a.vouchdt, DATE_FORMAT(a.`vouchdt`,'%d-%m-%Y') as  mydt, a.addpack as cartage, b.puramt,
                                    0000000.00 as cartpaid, a.`party`, a.`vouchno`, b.qty, b.sqft, b.calby, b.rate, b.cost,  b.`amount`, b.itemcode, b.size, 
                                    b.itemname, b.srno, '' as salesman, b.amount as itemdisc, b.amount as itemgst, a.totdisc, a.grossamt, a.addgst, b.supp
                                    FROM `credmain` a, `credtran` b  WHERE a.`vouchdt`>= '$mfrmdate' AND  a.`vouchdt` <='$mtodate' 
                                    AND a.`vouchno` = b.`vouchno` $itemcond $salescond") or die(mysqli_error($conn));


mysqli_query($conn, "update itdata set itemdisc = 0, itemgst = 0 ") or die(mysqli_error($conn));

// discount part

$getmain1 = mysqli_query($conn, "SELECT * FROM itdata where totdisc > 0 order by vouchdt, vouchno ") or die(mysqli_error($conn));

while ($r = mysqli_fetch_array($getmain1)) {
    $qvouch = $r['vouchno'];
    $qyear = $r['yearno'];
    $qitem = $r['itemname'];
    $qsize = $r['size'];

    $qamt = $r['amount'];
    $qgross = $r['grossamt'];
    $qdisc = $r['totdisc'];

    $idisc = ($qamt / $qgross) * $qdisc;

    mysqli_query($conn, "update itdata set itemdisc = '$idisc' where vouchno = '$qvouch' AND `yearno` = '$qyear' 
                                AND `itemname` = '$qitem' AND `size` = '$qsize'  ") or die(mysqli_error($conn));
}

// gst part

$getmain1 = mysqli_query($conn, "SELECT * FROM itdata where addgst > 0 order by vouchdt, vouchno ") or die(mysqli_error($conn));
while ($r = mysqli_fetch_array($getmain1)) {
    $qvouch = $r['vouchno'];
    $qyear = $r['yearno'];
    $qitem = $r['itemname'];
    $qsize = $r['size'];

    $qamt = $r['amount'];
    $qgross = $r['grossamt'];
    $qgst = $r['addgst'];

    $igst = ($qamt / $qgross) * $qgst;

    mysqli_query($conn, "update itdata set itemgst = '$igst' where vouchno = '$qvouch' AND `yearno` = '$qyear' 
                                AND `itemname` = '$qitem' AND `size` = '$qsize'  ") or die(mysqli_error($conn));
}

$getpmain = mysqli_query($conn, "SELECT * FROM itdata group by type, vouchno order by vouchdt, type, vouchno ") or die(mysqli_error($conn));
//print table($getpmain);
//die();
//mysqli_query($conn, "drop temporary table if exists costdata") or die(mysqli_error($conn));
//mysqli_query($conn, "create temporary table costdata 
//                            SELECT salevouch, purvouch, itemname, size, purcost, sum(recdqty) as recdqty, sum(recdsqft) as recdsqft 
//                                   FROM salepurch WHERE `billdate` != '0000-00-00'  $itemcond2  group by salevouch, itemname, size ") or die(mysqli_error($conn));
//
//
//$getcost = mysqli_query($conn, "SELECT * FROM `costdata` order by salevouch, itemname, size ") or die(mysqli_error($conn));
//print table($getcost);
//die();

if (isset($_POST["view"])) {
    include("includes/linkshead.php");
    ?>
    <style>
        @media print
        {    
            .no-print, .no-print *
            {
                display: none !important;
            }
        }
    </style>
    </head>
    <body>
        <div class="container">
    <?php include("includes/head_rep.php"); ?>
            <table class="table table-bordered">
                <thead class="mySearchTr">
                    <tr style=" background-color: #8F4131;">
                        <th style="width:3%;">Sr. No.</th>
                        <th style="width:8%;">Date</th>
                        <th style="width:6%;">Sale / Purchase</th>
                        <th style="width:10%;">Sales Executive</th>
                        <th style="width:17%;">Customer / Supplier</th>
                        <th style="width:12%;">Item</th>
                        <th style="width:5%;">Size</th>
                        <th style="width:6%;" class='text-right'>Rate</th>
                        <th style="width:7%;" class='text-right'>Sale Amount</th>
                        <th style="width:8%;" class='text-right'>Cost Amount</th>
                        <th style="width:5%;" class='text-right'>Qty.</th>
                        <th style="width:5%;" class='text-right'>Sq.Ft.</th>
                        <th style="width:8%;" class='text-right'>P/L Amount</th>
                    </tr>
                </thead>

    <?php
    $ctr = 0;
    $gqty = 0;
    $gsqft = 0;
    $gpl = 0;

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

        $type = $rowg['type'];
        $vouchno = $rowg['vouchno'];
        $party = $rowg['party'];
        $staff = $rowg['salesman'];
        $cartage = $rowg['cartage'];
        $cartpaid = $rowg['cartpaid'];

        $partyname = '';
        $getparty = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE  `accode`='$party' ") or die(mysqli_error($conn));
        $fetparty = mysqli_fetch_array($getparty);
        $partyname = $fetparty['name'];

        $staffname = '';
        $getstaff = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE  `accode`='$staff' ") or die(mysqli_error($conn));
        $fetstaff = mysqli_fetch_array($getstaff);
        $staffname = $fetstaff['name'];


        $ctr = 0;
        $tpl = 0;
        $tqty = 0;
        $tsqft = 0;
        $purvouch = 0;
//        $purparty = "";

        $getvouch = mysqli_query($conn, "SELECT * FROM itdata WHERE  type = '$type' and `vouchno`= '$vouchno' ") or die(mysqli_error($conn));
        while ($rowm = mysqli_fetch_array($getvouch)) {

            $qty = $rowm['qty'];
            $sqft = $rowm['sqft'];
            $rate = $rowm['rate'];
            $cost = $rowm['cost'];
            $puramt = $rowm['puramt'];
            $saleamt = $rowm['amount'] - $rowm['itemdisc'] + $rowm['itemgst'];
            $itemname = $rowm['itemname'];
            $size = $rowm['size'];
            $calby = $rowm['calby'];
            $supp = $rowm['supp'];
            
            $gparty1 = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE  `accode`='$supp' ") or die(mysqli_error($conn));
            $fparty1 = mysqli_fetch_array($gparty1);
            $purparty = $fparty1['name'];
            

//                        if ($type == 'S') {
//                            $purcost = 0;
//                            $purvouch = 0;
//                            $purparty = "";
//
//                            if ($calby == 'S') {
//                                $getcost = mysqli_query($conn, "SELECT purvouch, `recdsqft` as recd,  `purcost` FROM `costdata` 
//                                                                WHERE `itemname`='$itemname' and size = '$size' and salevouch = '$vouchno' ") or die(mysqli_error($conn));
//                            } else {
//                                $getcost = mysqli_query($conn, "SELECT purvouch, `recdqty` as recd,  `purcost`  FROM `costdata` 
//                                                                WHERE `itemname`='$itemname' and size = '$size' and salevouch = '$vouchno' ") or die(mysqli_error($conn));
//                            }
//
//                            $fetcost = mysqli_fetch_array($getcost);
//                            $purvouch = $fetcost['purvouch'];
//                            $purcost = $fetcost['purcost'];
//                            $purqty = $fetcost['recd'];
//
//                            $purparty = '';
//                            $gparty = mysqli_query($conn, "SELECT `party` FROM `purmain` WHERE  `vouchno`='$purvouch' ") or die(mysqli_error($conn));
//                            $fparty = mysqli_fetch_array($gparty);
//                            $puracc = $fparty['party'];
//
//                            $gparty1 = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE  `accode`='$puracc' ") or die(mysqli_error($conn));
//                            $fparty1 = mysqli_fetch_array($gparty1);
//                            $purparty = $fparty1['name'];
//                            
//                            $calqty = 0 ;
//                            $calsqft = 0 ;
//                                    
//                            if ($calby == 'S') {
//                                if ($sqft <= $purqty) {
//                                    $calsqft = $sqft ;                                            
//                                } else {
//                                    $calsqft = $purqty ;                                            
//                                }
//                            } else {
//                                if ($qty <= $purqty) {
//                                    $calqty = $qty ;                                            
//                                } else {
//                                    $calqty = $purqty ;                                            
//                                }
//                            }
//
//                            if ($calby == 'S') {
//                                $puramt = $purcost * $calsqft;
//                            } else {
//                                $puramt = $purcost * $calqty;
//                            }
//
//                            if ($puramt == 0 || $puramt == '') {
//                                $puramt = $cost * $calqty;
//                            }
//                        } else {
//                            if ($calby == 'S') {
//                                $puramt = $cost * $sqft;
//                            } else {
//                                $puramt = $cost * $qty;
//                            }
//                        } 
            $url = "sale.php?mode=edit&vouchno=$rowm[vouchno]";
 

            $plamt = round($saleamt - $puramt, 0);

            $ctr++;
            echo "<tr class='clickable-row2' data-href='$url' >";
            echo "<td>$ctr</td>";
            echo "<td>$rowm[mydt]</td>";
//                        echo "<td>$rowm[vouchno]  <br> <span style='color:brown' > $purvouch</span></td>";
            echo "<td>$rowm[vouchno]</td>";
            echo "<td>$staffname</td>";
            echo "<td>$partyname  <br> <span style='color:brown'> $purparty</span></td>";
            echo "<td>$itemname</td>";
            echo "<td>$size</td>";
            echo "<td class='text-right'>" . number_format($rate, 2) . "</td>";
            echo "<td class='text-right'>" . number_format($saleamt, 2) . "</td>";
            echo "<td class='text-right'>" . number_format($puramt, 2) . "</td>";
            echo "<td class='text-right'>" . number_format($qty, 2) . "</td>";
            echo "<td class='text-right'>" . number_format($sqft, 2) . "</td>";
            echo "<td class='text-right'>" . number_format($plamt, 2) . "</td>";
            echo "</tr>";

            $tqty = $tqty + $qty;
            $tsqft = $tsqft + $sqft;
            $tpl = $tpl + $plamt;

            $gqty = $gqty + $qty;
            $gsqft = $gsqft + $sqft;
            $gpl = $gpl + $plamt;
        }

//        if ($cartage + $cartpaid > 0) {
//            $plcart = $cartage - $cartpaid;
//
//            $ctr++;
//            echo "<tr>";
//            echo "<td>$ctr</td>";
//            echo "<td>$rowm[mydt]</td>";
//            echo "<td>$rowm[vouchno]</td>";
//            echo "<td>$staffname</td>";
//            echo "<td>$partyname</td>";
////            echo "<td style=' color: brown;'>Expense</td>";
//            echo "<td></td>";
//            echo "<td class='text-right'></td>";
////            echo "<td class='text-right'>" . number_format($cartage, 2) . "</td>";
////            echo "<td class='text-right'>" . number_format($cartpaid, 2) . "</td>";
//            echo "<td class='text-right'></td>";
//            echo "<td class='text-right'></td>";
////            echo "<td class='text-right'>" . number_format($plcart, 2) . "</td>";
//            echo "</tr>";
//
////            $tpl = $tpl + $plcart;
////            $gpl = $gpl + $plcart;
//            
//        }

        echo "<tr> ";
        echo "<td  ></td>";
        echo "<td  ></td>";
        echo "<td  ></td>";
        echo "<td  ></td>";
        echo "<td  ></td>";
        echo "<td  ></td>";
        echo "<td  ></td>";
        echo "<td  ></td>";
        echo "<td  ></td>";
        echo "<td class='text-right' style=' color: #006300;'><b>Total</b></td>";
        echo "<td class='text-right' style=' color: #006300;'><b>" . number_format($tqty, 2) . "</b></td>";
        echo "<td class='text-right' style=' color: #006300;'><b>" . number_format($tsqft, 2) . "</b></td>";
        echo "<td class='text-right' style=' color: #006300;' ><b>" . number_format($tpl, 2) . "</b></td>";
        echo "</tr>";
    }
    echo "<tr> ";
    echo "<td  ></td>";
    echo "<td  ></td>";
    echo "<td  ></td>";
    echo "<td  ></td>";
    echo "<td  ></td>";
    echo "<td  ></td>";
    echo "<td  ></td>";
    echo "<td  ></td>";
    echo "<td  ></td>";
    echo "<td class='text-right' style=' color: blue;'><b>Total</b></td>";
    echo "<td class='text-right' style=' color: blue;'><b>" . number_format($gqty, 2) . "</b></td>";
    echo "<td class='text-right' style=' color: blue;'><b>" . number_format($gsqft, 2) . "</b></td>";
    echo "<td class='text-right' style=' color: blue;' ><b>" . number_format($gpl, 2) . "</b></td>";
    echo "</tr>";
    ?> 
            </table>
        </div>
    <?php
    include("includes/linksfoot.php");
    ?>
    </body>
    </html>
    <?php
}


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

    $contents .= "Sr.No.,Date,Sale/Purchase,Sales Executive,Customer / Supplier,Item,Size,Rate,Sale Amount,Cost Amount,Qty.,Sq.Ft.,P/L Amount\n";

    $ctr = 0;
    $gqty = 0;
    $gsqft = 0;
    $gpl = 0;

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

        $type = $rowg['type'];
        $vouchno = $rowg['vouchno'];
        $party = $rowg['party'];
        $staff = $rowg['salesman'];
        $cartage = $rowg['cartage'];
        $cartpaid = $rowg['cartpaid'];

        $partyname = '';
        $getparty = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE  `accode`='$party' ") or die(mysqli_error($conn));
        $fetparty = mysqli_fetch_array($getparty);
        $partyname = $fetparty['name'];

        $staffname = '';
        $getstaff = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE  `accode`='$staff' ") or die(mysqli_error($conn));
        $fetstaff = mysqli_fetch_array($getstaff);
        $staffname = $fetstaff['name'];
        
        $ctr = 0;
        $tpl = 0;
        $tqty = 0;
        $tsqft = 0;
        $purvouch = 0;

        $getvouch = mysqli_query($conn, "SELECT * FROM itdata WHERE  type = '$type' and `vouchno`= '$vouchno' ") or die(mysqli_error($conn));
        while ($rowm = mysqli_fetch_array($getvouch)) {

            $qty = $rowm['qty'];
            $sqft = $rowm['sqft'];
            $rate = $rowm['rate'];
            $cost = $rowm['cost'];
            $puramt = $rowm['puramt'];
            $saleamt = $rowm['amount'] - $rowm['itemdisc'] + $rowm['itemgst'];
            $itemname = $rowm['itemname'];
            $size = $rowm['size'];
            $calby = $rowm['calby'];
            $supp = $rowm['supp'];
            
            $gparty1 = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE  `accode`='$supp' ") or die(mysqli_error($conn));
            $fparty1 = mysqli_fetch_array($gparty1);
            $purparty = $fparty1['name'];
            
//                        if ($type == 'S') {
//                            $purcost = 0;
//                            $purvouch = 0;
//                            $purparty = "";
//
//                            if ($calby == 'S') {
//                                $getcost = mysqli_query($conn, "SELECT purvouch, `recdsqft` as recd,  `purcost` FROM `costdata` 
//                                                                WHERE `itemname`='$itemname' and size = '$size' and salevouch = '$vouchno' ") or die(mysqli_error($conn));
//                            } else {
//                                $getcost = mysqli_query($conn, "SELECT purvouch, `recdqty` as recd,  `purcost`  FROM `costdata` 
//                                                                WHERE `itemname`='$itemname' and size = '$size' and salevouch = '$vouchno' ") or die(mysqli_error($conn));
//                            }
//
//                            $fetcost = mysqli_fetch_array($getcost);
//                            $purvouch = $fetcost['purvouch'];
//                            $purcost = $fetcost['purcost'];
//                            $purqty = $fetcost['recd'];
//
//                            $purparty = '';
//                            $gparty = mysqli_query($conn, "SELECT `party` FROM `purmain` WHERE  `vouchno`='$purvouch' ") or die(mysqli_error($conn));
//                            $fparty = mysqli_fetch_array($gparty);
//                            $puracc = $fparty['party'];
//
//                            $gparty1 = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE  `accode`='$puracc' ") or die(mysqli_error($conn));
//                            $fparty1 = mysqli_fetch_array($gparty1);
//                            $purparty = $fparty1['name'];
//                            
//                            $calqty = 0 ;
//                            $calsqft = 0 ;
//                                    
//                            if ($calby == 'S') {
//                                if ($sqft <= $purqty) {
//                                    $calsqft = $sqft ;                                            
//                                } else {
//                                    $calsqft = $purqty ;                                            
//                                }
//                            } else {
//                                if ($qty <= $purqty) {
//                                    $calqty = $qty ;                                            
//                                } else {
//                                    $calqty = $purqty ;                                            
//                                }
//                            }
//
//                            if ($calby == 'S') {
//                                $puramt = $purcost * $calsqft;
//                            } else {
//                                $puramt = $purcost * $calqty;
//                            }
//
//                            if ($puramt == 0 || $puramt == '') {
//                                $puramt = $cost * $calqty;
//                            }
//                        } else {
//                            if ($calby == 'S') {
//                                $puramt = $cost * $sqft;
//                            } else {
//                                $puramt = $cost * $qty;
//                            }
//                        } 
            
            $plamt = round($saleamt - $puramt, 0);

            $ctr++;
       
            $contents .= "$ctr" . ",";
            $contents .= "$rowm[mydt]" . ",";
            $contents .= "$rowm[vouchno]" . ",";
            $contents .= "$staffname" . ",";
            $contents .= "$partyname" . " / " . "$purparty". ",";
            $contents .= "$itemname" . ",";
            $contents .= "$size" . ",";
            $contents .= "$rate" . ",";
            $contents .= "$saleamt" . ",";
            $contents .= "$puramt" . ",";
            $contents .= "$qty" . ",";
            $contents .= "$sqft" . ",";
            $contents .= "$plamt" . "\n";

            $tqty = $tqty + $qty;
            $tsqft = $tsqft + $sqft;
            $tpl = $tpl + $plamt;

            $gqty = $gqty + $qty;
            $gsqft = $gsqft + $sqft;
            $gpl = $gpl + $plamt;
        }

//        if ($cartage + $cartpaid > 0) {
//            $plcart = $cartage - $cartpaid;
//
//            $ctr++;
//            echo "<tr>";
//            echo "<td>$ctr</td>";
//            echo "<td>$rowm[mydt]</td>";
//            echo "<td>$rowm[vouchno]</td>";
//            echo "<td>$staffname</td>";
//            echo "<td>$partyname</td>";
////            echo "<td style=' color: brown;'>Expense</td>";
//            echo "<td></td>";
//            echo "<td class='text-right'></td>";
////            echo "<td class='text-right'>" . number_format($cartage, 2) . "</td>";
////            echo "<td class='text-right'>" . number_format($cartpaid, 2) . "</td>";
//            echo "<td class='text-right'></td>";
//            echo "<td class='text-right'></td>";
////            echo "<td class='text-right'>" . number_format($plcart, 2) . "</td>";
//            echo "</tr>";
//
////            $tpl = $tpl + $plcart;
////            $gpl = $gpl + $plcart;
//            
//        }

        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "Total" . ",";
        $contents .= "$tqty" . ",";
        $contents .= "$tsqft" . ",";
        $contents .= "$tpl" . "\n";
   
    }

    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "Total" . ",";
    $contents .= "$gqty" . ",";
    $contents .= "$gsqft" . ",";
    $contents .= "$gpl" . "\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;
    }
}
?>