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

$maindb = "rawmain";
$trandb = "rawtran";

ini_set('display_errors', 1);
error_reporting(E_ERROR);

$zroot = $_SESSION["zdir"];
$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);

$invno = $_POST["invno"];
$party = $_POST["party"];
$dtype = $_POST["dtype"];
$scope = $_POST["scope"];
$printnar = $_POST["prna"];
$shop = $_POST["shop"];

$dtypecond = "";
$mscopecond = "";
$itemcond = "";
$gstcond = "";
$prodtypecond = "";
$shopcond = "";

$mfrmdate = trim($_POST["fdate"]);
$mtodate = trim($_POST["tdate"]);


if (!empty($party)) {
    $partycond = " AND `accode`='$party' ";
}

if (!empty($dtype)) {
    $dtypecond = " AND `dtype`='$dtype' ";
}

if (!empty($scope)) {
    $mscopecond = " AND `type`='$scope' ";
}

if ($shop == 1) {
    $shopcond = " AND a.`shop`='1' ";
} elseif ($shop == 2) {
    $shopcond = " AND a.`shop`='2' ";
} elseif ($shop == 3) {
    $shopcond = " AND a.`shop`='3' ";
} else {
    $shopcond = "";
}

if (empty($_POST["fdate"])) {
    browse("From Date Cannot Be Empty");
    echo "<script>window.close();</script>";
}
if (empty($_POST["tdate"])) {
    browse("To Date Cannot Be Empty");
    echo "<script>window.close();</script>";
}
    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));

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

    include("includes/linkshead.php");
    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));
    ?>
    <style type="text/css">
        #tabital {
            font-style: italic;
        }

        #font1 {
            font-style: italic;
        }

        #boldfont {
            font-weight: bold;
        }

        .type2-table {
            width: 100%;
            border-collapse: collapse;
            font-family: Arial, sans-serif;
            margin-bottom: 25px;
        }

        .type2-table th,
        .type2-table td {
            border: 1px solid #222;
            padding: 8px 6px;
            font-size: 12px;
            color: #000;
        }

        .type2-head {
            background: #ead4c8;
            font-weight: bold;
            font-size: 22px;
        }

        .type2-head-left {
            text-align: center;
            font-size: 22px;
            font-weight: 700;
            padding: 12px;
        }

        .type2-head-right {
            text-align: center;
            font-size: 18px;
            font-weight: 700;
            padding: 12px;
        }

        .type2-table thead tr:nth-child(2) {
            background: #efefef;
        }

        .type2-table th {
            text-transform: uppercase;
            text-align: center;
        }

        .type2-table td {
            background: #fff;
        }

        .numeric {
            text-align: right;
        }

        .center {
            text-align: center;
        }

        .total-row td {
            background: #cfe6f1;
            font-weight: bold;
            font-size: 16px;
        }

        .material-col {
            width: 30%;
        }

        .remark-col {
            width: 15%;
        }
    </style>
    </head>

    <body>
        <div class="container">
            <?php include("includes/head_rep.php"); ?>

            <div class="row">
                <div class="col-lg-12 pad">
                    <?php
                    $getparty = mysqli_query($conn, "SELECT accode,name,transporter FROM `accmast` 
                                                            WHERE accode != '' AND grpcode = '010'
                                                            AND accode IN (SELECT party FROM `$maindb` WHERE vouchdt >= '$mfrmdate' AND vouchdt <= '$mtodate')
                                                            $partycond order by name ") or die(mysqli_error($conn));

                    while ($fetparty = mysqli_fetch_array($getparty)) {

                        $accode = $fetparty["accode"];
                        $name = $fetparty["name"];
                        $transporter = $fetparty["transporter"];


                        if ($_POST["dtype"] == 2) {

                            echo "<table class='type2-table'>";

                            // HEADER
                            echo "<thead>";

                            echo "<tr class='type2-head'>";
                            echo "<th colspan='5' class='type2-head-left'>$name</th>";
                            echo "<th colspan='4' class='type2-head-right'>FROM $mfdt TO $mtdt</th>";
                            echo "</tr>";

                            // COLUMN HEADER
                            echo "<tr>";
                            echo "<th width='5%'>SR. NO.</th>";
                            echo "<th width='8%'>INVOICE NO.</th>";
                            echo "<th width='8%'>DATE</th>";
                            echo "<th class='material-col'>MATERIAL NAME</th>";
                            echo "<th width='8%'>COLOR</th>";
                            echo "<th width='6%' style='text-align:right'>QTY</th>";
                            echo "<th width='6%' style='text-align:right'>RATE</th>";
                            echo "<th width='9%' style='text-align:right'>AMOUNT</th>";
                            echo "<th class='remark-col'>REMARKS</th>";
                            echo "</tr>";

                            echo "</thead>";
                            echo "<tbody>";

                            $ctr2 = 0;
                            $totqty = 0;
                            $totamt = 0;

                            $gettran = mysqli_query($conn, "SELECT a.*, b.item, b.color, b.qty, b.rate, b.amount 
                                    FROM `$maindb` a 
                                    LEFT JOIN `$trandb` b ON a.vouchno = b.vouchno 
                                    WHERE a.vouchno != '' 
                                    AND a.party = '$accode'
                                    AND a.`vouchdt` >= '$mfrmdate' 
                                    AND a.`vouchdt` <= '$mtodate' $shopcond ")
                                or die(mysqli_error($conn));

                            while ($tran = mysqli_fetch_array($gettran)) {

                                $getitem = mysqli_query($conn, "SELECT `name` FROM `rawmat`  WHERE `code`='$tran[item]'") or die(mysqli_error($conn));
                                $item = mysqli_fetch_array($getitem);

                                $getcolor = mysqli_query($conn, "SELECT `name` FROM `color` WHERE `code`='$tran[color]'") or die(mysqli_error($conn));
                                $color = mysqli_fetch_array($getcolor);

                                $ctr2++;

                                $vdt = date("d-m-Y", strtotime($tran['vouchdt']));

                                echo "<tr>";
                                echo "<td class='center'>$ctr2</td>";
                                echo "<td class='center'>$tran[vouchno]</td>";
                                echo "<td class='center'>$vdt</td>";
                                echo "<td>$item[name]</td>";
                                echo "<td>$color[name]</td>";
                                echo "<td class='numeric'>" . number_format($tran['qty'], 2) . "</td>";
                                echo "<td class='numeric'>" . number_format($tran['rate'], 2) . "</td>";
                                echo "<td class='numeric'>" . number_format($tran['amount'], 2) . "</td>";
                                echo "<td>$tran[remark1] $tran[remark2]</td>";
                                echo "</tr>";

                                $totqty += $tran['qty'];
                                $totamt += $tran['amount'];
                            }

                            // TOTAL ROW
                            echo "<tr class='total-row'>";
                            echo "<td colspan='5' align='right'>TOTAL :</td>";
                            echo "<td class='numeric'>" . number_format($totqty, 2) . "</td>";
                            echo "<td></td>";
                            echo "<td class='numeric'>" . number_format($totamt, 2) . "</td>";
                            echo "<td></td>";
                            echo "</tr>";

                            echo "</tbody>";
                            echo "</table>";
                        }


                    }


                    if ($_POST["dtype"] == 1) {

                        echo "<br><br>";
                        echo "<table class='type2-table'>";

                        // HEADER
                        echo "<thead>";
                        echo "<tr class='type2-head'>";
                        echo "<th colspan='5' class='type2-head-left'>PARTY WISE SUMMARY</th>";
                        echo "</tr>";

                        // COLUMN HEADER
                        echo "<tr>";
                        echo "<th width='8%'>SR. NO.</th>";
                        echo "<th width='15%'>SHOP WORKER</th>";
                        echo "<th width='37%'>CUSTOMER NAME</th>";
                        echo "<th width='15%'>TOTAL BILLS</th>";
                        echo "<th width='20%'>TOTAL BILLS AMOUNT</th>";
                        echo "</tr>";

                        echo "</thead>";
                        echo "<tbody>";

                        $ctr = 0;
                        $grandBills = 0;
                        $grandAmt = 0;

                        $getsummary = mysqli_query($conn, "SELECT a.party, b.name as partyname, sw.name as shopworker,
                                                                  COUNT(a.vouchno) as totalbills, SUM(a.netamt) as totalamt
                                                                  FROM `$maindb` a
                                                                  LEFT JOIN `accmast` b ON a.party = b.accode
                                                                  LEFT JOIN `transport` sw ON b.transporter = sw.code
                                                                  WHERE a.vouchno != ''
                                                                  AND a.vouchdt >= '$mfrmdate'
                                                                  AND a.vouchdt <= '$mtodate'
                                                                  $partycond
                                                                  $shopcond
                                                                  GROUP BY a.party
                                                                  ORDER BY b.name") or die(mysqli_error($conn));

                        while ($row = mysqli_fetch_array($getsummary)) {

                            $ctr++;

                            echo "<tr>";
                            echo "<td class='center'>$ctr</td>";
                            echo "<td class='center'>$row[shopworker]</td>";
                            echo "<td>$row[partyname]</td>";
                            echo "<td class='center'>" . number_format($row['totalbills']) . "</td>";
                            echo "<td class='numeric'>" . number_format($row['totalamt'], 2) . "</td>";
                            echo "</tr>";

                            $grandBills += $row['totalbills'];
                            $grandAmt += $row['totalamt'];
                        }

                        // TOTAL ROW
                        echo "<tr class='total-row'>";
                        echo "<td colspan='3' align='right'>TOTAL :</td>";
                        echo "<td class='center'>" . number_format($grandBills) . "</td>";
                        echo "<td class='numeric'>" . number_format($grandAmt, 2) . "</td>";
                        echo "</tr>";

                        echo "</tbody>";
                        echo "</table>";
                    }


                    ?>
                </div>
            </div>
        </div>

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

    </html>
    <?php
}


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

    $getparty = mysqli_query($conn, "SELECT accode,name,transporter FROM `accmast` 
                                            WHERE accode != '' AND grpcode = '010'
                                            AND accode IN (SELECT party FROM `$maindb` WHERE vouchdt >= '$mfrmdate' AND vouchdt <= '$mtodate')
                                            $partycond $shopcond  order by name ") or die(mysqli_error($conn));

    while ($fetparty = mysqli_fetch_array($getparty)) {

        $accode = $fetparty["accode"];
        $name = $fetparty["name"];
        $transporter = $fetparty["transporter"];


        if ($_POST["dtype"] == 2) {

            $contents .= ",,$name  FROM $mfdt TO $mtdt \n";

            // COLUMN HEADER
            $contents .= "SR. NO., INVOICE NO., DATE, MATERIAL NAME, COLOR, QTY, RATE, AMOUNT, REMARKS \n";

            $ctr2 = 0;
            $totqty = 0;
            $totamt = 0;

            $gettran = mysqli_query($conn, "SELECT a.*, b.item, b.color, b.qty, b.rate, b.amount 
                                    FROM `$maindb` a 
                                    LEFT JOIN `$trandb` b ON a.vouchno = b.vouchno 
                                    WHERE a.vouchno != '' 
                                    AND a.party = '$accode'
                                    AND a.`vouchdt` >= '$mfrmdate' 
                                    AND a.`vouchdt` <= '$mtodate' ")
                or die(mysqli_error($conn));

            while ($tran = mysqli_fetch_array($gettran)) {

                $getitem = mysqli_query($conn, "SELECT `name` FROM `rawmat`  WHERE `code`='$tran[item]'") or die(mysqli_error($conn));
                $item = mysqli_fetch_array($getitem);

                $getcolor = mysqli_query($conn, "SELECT `name` FROM `color` WHERE `code`='$tran[color]'") or die(mysqli_error($conn));
                $color = mysqli_fetch_array($getcolor);

                $ctr2++;

                $vdt = date("d-m-Y", strtotime($tran['vouchdt']));

                $contents .= "$ctr2" . ",";
                $contents .= "$tran[vouchno]" . ",";
                $contents .= "$vdt" . ",";
                $contents .= "$item[name]" . ",";
                $contents .= "$color[name]" . ",";
                $contents .= "$tran[qty]" . ",";
                $contents .= "$tran[rate]" . ",";
                $contents .= "$tran[amount]" . ",";
                $contents .= "$tran[remark1] $tran[remark2]" . "\n";

                $totqty += $tran['qty'];
                $totamt += $tran['amount'];
            }

            // TOTAL ROW
            $contents .= "" . ",";
            $contents .= "" . ",";
            $contents .= "" . ",";
            $contents .= "" . ",";
            $contents .= "TOTAL" . ",";
            $contents .= "$totqty" . ",";
            $contents .= "" . ",";
            $contents .= "$totamt" . ",";
            $contents .= "" . "\n";
        }


    }


    if ($_POST["dtype"] == 1) {

        $contents .= ",,PARTY WISE SUMMARY \n";

        // COLUMN HEADER
        $contents .= "SR. NO., SHOP WORKER, CUSTOMER NAME, TOTAL BILLS, TOTAL BILLS AMOUNT \n";


        $ctr = 0;
        $grandBills = 0;
        $grandAmt = 0;

        $getsummary = mysqli_query($conn, "SELECT a.party, b.name as partyname, sw.name as shopworker,
                                                  COUNT(a.vouchno) as totalbills, SUM(a.netamt) as totalamt
                                                  FROM `$maindb` a
                                                  LEFT JOIN `accmast` b ON a.party = b.accode
                                                  LEFT JOIN `transport` sw ON b.transporter = sw.code
                                                  WHERE a.vouchno != ''
                                                  AND a.vouchdt >= '$mfrmdate'
                                                  AND a.vouchdt <= '$mtodate'
                                                  $partycond
                                                  $shopcond
                                                  GROUP BY a.party
                                                  ORDER BY b.name") or die(mysqli_error($conn));

        while ($row = mysqli_fetch_array($getsummary)) {

            $ctr++;

            $contents .= "$ctr" . ",";
            $contents .= "$row[shopworker]" . ",";
            $contents .= "$row[partyname]" . ",";
            $contents .= "$row[totalbills]" . ",";
            $contents .= "$row[totalamt]" . "\n";

            $grandBills += $row['totalbills'];
            $grandAmt += $row['totalamt'];
        }

        // TOTAL ROW
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "TOTAL" . ",";
        $contents .= "$grandBills" . ",";
        $contents .= "$grandAmt" . "\n";
    }


    $contents = strip_tags($contents);

    if ($_POST["dtype"] == 1) {
        header("Content-Disposition: attachment; filename=$formname" . "CUSTOMER_SUMMARY_" . date('d-m-Y') . ".csv");
        print $contents;
    } else {
        header("Content-Disposition: attachment; filename=$formname" . "CUSTOMER_WEEKLY_DETAIL_" . date('d-m-Y') . ".csv");
        print $contents;
    }
}


if (isset($_POST['subpdf'])) {
    require('library/fpdf/fpdf.php');

    class PDF extends FPDF
    {

        function Header()
        {

            global $formname, $mfdt, $mtdt, $dtype;

            $this->SetFont('Arial', 'B', 14);

            if ($dtype == 1) {
                $title = "CUSTOMER WISE ALL SUMMARY";
            } else {
                $title = "CUSTOMER WEEKLY REPORT";
            }

            $this->Cell(0, 8, $title, 0, 1, 'C');

            $this->SetFont('Arial', 'B', 10);
            $this->Cell(0, 6, "FROM $mfdt TO $mtdt", 0, 1, 'C');

            $this->Ln(3);
        }

        function Footer()
        {

            $this->SetY(-12);

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

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

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

    $pdf->AliasNbPages();
    $pdf->AddPage();

    $pdf->SetAutoPageBreak(true, 15);


    // ============================
// DTYPE 1 SUMMARY
// ============================

    if ($_POST["dtype"] == 1) {

        $w = array(15, 35, 110, 35, 45);

        $pdf->SetFont('Arial', 'B', 10);

        $pdf->Cell($w[0], 8, 'SR.', 1, 0, 'C');
        $pdf->Cell($w[1], 8, 'SHOP WORKER', 1, 0, 'C');
        $pdf->Cell($w[2], 8, 'CUSTOMER NAME', 1, 0, 'C');
        $pdf->Cell($w[3], 8, 'TOTAL BILLS', 1, 0, 'C');
        $pdf->Cell($w[4], 8, 'TOTAL BILL AMOUNT', 1, 1, 'C');

        $pdf->SetFont('Arial', '', 9);

        $ctr = 0;
        $grandBills = 0;
        $grandAmt = 0;

        $getsummary = mysqli_query($conn, "
        SELECT a.party,
               b.name as partyname,
               sw.name as shopworker,
               COUNT(a.vouchno) as totalbills,
               SUM(a.netamt) as totalamt
        FROM `$maindb` a
        LEFT JOIN `accmast` b ON a.party = b.accode
        LEFT JOIN `transport` sw ON b.transporter = sw.code
        WHERE a.vouchno != ''
        AND a.vouchdt >= '$mfrmdate'
        AND a.vouchdt <= '$mtodate'
        $partycond
        $shopcond
        GROUP BY a.party
        ORDER BY b.name
    ") or die(mysqli_error($conn));

        while ($row = mysqli_fetch_array($getsummary)) {

            $ctr++;

            $pdf->Cell($w[0], 7, $ctr, 1, 0, 'C');
            $pdf->Cell($w[1], 7, $row['shopworker'], 1, 0, 'C');
            $pdf->Cell($w[2], 7, $row['partyname'], 1, 0, 'L');
            $pdf->Cell($w[3], 7, number_format($row['totalbills']), 1, 0, 'C');
            $pdf->Cell($w[4], 7, number_format($row['totalamt'], 2), 1, 1, 'R');

            $grandBills += $row['totalbills'];
            $grandAmt += $row['totalamt'];
        }

        // TOTAL
        $pdf->SetFont('Arial', 'B', 10);

        $pdf->Cell($w[0] + $w[1] + $w[2], 8, 'TOTAL', 1, 0, 'R');
        $pdf->Cell($w[3], 8, number_format($grandBills), 1, 0, 'C');
        $pdf->Cell($w[4], 8, number_format($grandAmt, 2), 1, 1, 'R');
    }



    // ============================
// DTYPE 2 DETAIL
// ============================

    if ($_POST["dtype"] == 2) {

        $getparty = mysqli_query($conn, "
        SELECT accode,name
        FROM `accmast`
        WHERE accode != ''
        AND accode IN (
            SELECT party
            FROM `$maindb`
            WHERE vouchdt >= '$mfrmdate'
            AND vouchdt <= '$mtodate'
        )
        $partycond
        $shopcond
        ORDER BY name
    ") or die(mysqli_error($conn));

        while ($fetparty = mysqli_fetch_array($getparty)) {

            $accode = $fetparty['accode'];
            $name = $fetparty['name'];

            $pdf->SetFont('Arial', 'B', 12);

            $pdf->Cell(170, 9, $name, 1, 0, 'C');
            $pdf->Cell(95, 9, "FROM $mfdt TO $mtdt", 1, 1, 'C');

            $w = array(12, 22, 24, 72, 26, 20, 20, 28, 41);

            $pdf->SetFont('Arial', 'B', 9);

            $pdf->Cell($w[0], 8, 'SR', 1, 0, 'C');
            $pdf->Cell($w[1], 8, 'INV NO', 1, 0, 'C');
            $pdf->Cell($w[2], 8, 'DATE', 1, 0, 'C');
            $pdf->Cell($w[3], 8, 'MATERIAL', 1, 0, 'C');
            $pdf->Cell($w[4], 8, 'COLOR', 1, 0, 'C');
            $pdf->Cell($w[5], 8, 'QTY', 1, 0, 'R');
            $pdf->Cell($w[6], 8, 'RATE', 1, 0, 'R');
            $pdf->Cell($w[7], 8, 'AMOUNT', 1, 0, 'R');
            $pdf->Cell($w[8], 8, 'REMARKS', 1, 1, 'C');

            $ctr2 = 0;
            $totqty = 0;
            $totamt = 0;

            $pdf->SetFont('Arial', '', 8);

            $gettran = mysqli_query($conn, "
            SELECT a.*, b.item, b.color, b.qty, b.rate, b.amount
            FROM `$maindb` a
            LEFT JOIN `$trandb` b ON a.vouchno = b.vouchno
            WHERE a.party = '$accode'
            AND a.vouchdt >= '$mfrmdate'
            AND a.vouchdt <= '$mtodate'
        ") or die(mysqli_error($conn));

            while ($tran = mysqli_fetch_array($gettran)) {

                $getitem = mysqli_query($conn, "
                SELECT name
                FROM rawmat
                WHERE code='$tran[item]'
            ");
                $item = mysqli_fetch_array($getitem);

                $getcolor = mysqli_query($conn, "
                SELECT name
                FROM color
                WHERE code='$tran[color]'
            ");
                $color = mysqli_fetch_array($getcolor);

                $ctr2++;

                $vdt = date("d-m-Y", strtotime($tran['vouchdt']));

                $pdf->Cell($w[0], 7, $ctr2, 1, 0, 'C');
                $pdf->Cell($w[1], 7, $tran['vouchno'], 1, 0, 'C');
                $pdf->Cell($w[2], 7, $vdt, 1, 0, 'C');
                $pdf->Cell($w[3], 7, substr($item['name'], 0, 35), 1, 0, 'L');
                $pdf->Cell($w[4], 7, $color['name'], 1, 0, 'L');
                $pdf->Cell($w[5], 7, number_format($tran['qty'], 2), 1, 0, 'R');
                $pdf->Cell($w[6], 7, number_format($tran['rate'], 2), 1, 0, 'R');
                $pdf->Cell($w[7], 7, number_format($tran['amount'], 2), 1, 0, 'R');
                $pdf->Cell(
                    $w[8],
                    7,
                    substr($tran['remark1'] . " " . $tran['remark2'], 0, 20),
                    1,
                    1,
                    'L'
                );

                $totqty += $tran['qty'];
                $totamt += $tran['amount'];

                if ($pdf->GetY() > 180) {

                    $pdf->AddPage();

                    $pdf->SetFont('Arial', 'B', 9);

                    $pdf->Cell($w[0], 8, 'SR', 1, 0, 'C');
                    $pdf->Cell($w[1], 8, 'INV NO', 1, 0, 'C');
                    $pdf->Cell($w[2], 8, 'DATE', 1, 0, 'C');
                    $pdf->Cell($w[3], 8, 'MATERIAL', 1, 0, 'C');
                    $pdf->Cell($w[4], 8, 'COLOR', 1, 0, 'C');
                    $pdf->Cell($w[5], 8, 'QTY', 1, 0, 'R');
                    $pdf->Cell($w[6], 8, 'RATE', 1, 0, 'R');
                    $pdf->Cell($w[7], 8, 'AMOUNT', 1, 0, 'R');
                    $pdf->Cell($w[8], 8, 'REMARKS', 1, 1, 'C');

                    $pdf->SetFont('Arial', '', 8);
                }
            }

            // TOTAL ROW
            $pdf->SetFont('Arial', 'B', 9);

            $pdf->Cell(
                $w[0] + $w[1] + $w[2] + $w[3] + $w[4],
                8,
                'TOTAL',
                1,
                0,
                'R'
            );

            $pdf->Cell($w[5], 8, number_format($totqty, 2), 1, 0, 'R');
            $pdf->Cell($w[6], 8, '', 1, 0, 'R');
            $pdf->Cell($w[7], 8, number_format($totamt, 2), 1, 0, 'R');
            $pdf->Cell($w[8], 8, '', 1, 1, 'R');

            $pdf->Ln(5);
        }
    }

    $pdf->Output(
        'I',
        $formname . "_" . date('d-m-Y') . ".pdf"
    );

    exit;
}


?>