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

$maindb = "consultmain";
$formname = "GST Report";
$file = "gstrevenue.php";
$zroot = $_SESSION["zdir"];
$zcomp = $_SESSION["comp_" . $dbname];
$bseries = $_SESSION["user_series"];

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

$mfrmdate = trim($_POST["frmdt"]);
$mtodate = trim($_POST["todt"]);

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

if (isset($_POST["view"])) {
    include("includes/linkshead.php");

//    $getpmain = mysqli_query($conn, "SELECT p.medtotal, p.meddiscamt, p.consultfees, p.vouchno AS pax_voucher,DATE_FORMAT(p.vouchdt, '%d-%m-%Y') AS mydt,
//                                    GROUP_CONCAT(t.name SEPARATOR ', ') AS treatment_names, a.name AS paxname, 
//                                    (SELECT SUM(amount)  FROM actran WHERE slvouch = p.vouchno AND accode = p.accode) AS tamt,
//                                    (SELECT paidfor FROM actran WHERE slvouch = p.vouchno AND accode = p.accode LIMIT 1) AS paidfor FROM $maindb p 
//                                    LEFT JOIN accmast a ON p.accode = a.accode 
//                                    LEFT JOIN treatbill b ON p.vouchno = b.vouchno 
//                                    LEFT JOIN treatment t ON b.treatment = t.code WHERE p.vouchdt >= '$mfrmdate' AND p.vouchdt <= '$mtodate' GROUP BY p.vouchno ORDER BY p.vouchno") or die(mysqli_error($conn));
//    $getpmain = mysqli_query($conn, "Select * from accmast where grpcode = '010' and accode in (Select accode from actran where date >= '$mfrmdate' AND date <= '$mtodate') order by name") or die(mysqli_error($conn));
    $getpmain = mysqli_query($conn, "Select a.*,b.name,date_format(a.date,'%d-%m-%Y') as mydt  from actran a, accmast b where a.grpcode = '010' and a.accode = b.accode 
                                     and a.date >= '$mfrmdate' AND a.date <= '$mtodate' and a.amount > 0 group by a.date,a.accode order by a.date ASC") or die(mysqli_error($conn));
//    print table($getpmain);
    ?>

    </head>

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

                <div class="col-lg-12" style="width:100%;">
                    <div class="box">
                        <div id="sortableTable" class="body collapse in">
                            <table style="background: white;width: 100%" class="table table-bordered sktb">
                                <thead class="mySearchTr">
                                    <tr>
                                        <th width="5%;">Date</th>
                                        <th width="13%;">Patient Name</th>
                                        <th width="13%;">Treatment Name</th>
                                        <th width="4%;" class="text-right">Consultation(₹)</th>
                                        <th width="5%;" class="text-right">Product(₹)</th>
                                        <th width="12%;" class="text-right">Homeopathy Medicines(₹)</th>
                                        <th width="10%;" class="text-right">Paid Treatment Amt(₹)</th>
                                        <th width="8%;" class="text-right">Paid Amount</th>
                                        <th width="8%;">Payment Mode</th>
                                        <th width="8%;">Bank/Cash</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $paidamt = 0;
                                    while ($pmain = mysqli_fetch_array($getpmain)) {
                                        $maccode = $pmain['accode'];

                                        $getcons = mysqli_query($conn, "Select purpvisit,sum(consultfees) as tconsultfees, sum(medtotal) as medtotal, sum(meddiscamt) as meddiscamt,
                                                                        sum(treattotal) as treattotal, sum(trdiscamt) as trdiscamt from consultmain where vouchdt = '$pmain[date]'
                                                                        and accode='$maccode'") or die(mysqli_error($conn));
                                        $fetcons = mysqli_fetch_array($getcons);
                                        $consfees = $fetcons['tconsultfees'];
                                        $medamount = $fetcons['medtotal'] - $fetcons['meddiscamt'];
                                        $treatamt = $fetcons['treattotal'] - $fetcons['trdiscamt'];

                                        $getpaid = mysqli_query($conn, "Select sum(amount) as tpaidamt,source, bankinit from actran where date = '$pmain[date]' and accode='$maccode' and amount > 0") or die(mysqli_error($conn));
                                        $fetpaid = mysqli_fetch_array($getpaid);
                                        $paidamt = $fetpaid['tpaidamt'];
                                        $ptype = $fetpaid['source'];
                                        if ($ptype == "BR") {
                                            $paytype = "Bank";
                                        } else {
                                            $paytype = "Cash";
                                        }
                                        $pmode = $fetpaid['bankinit'];
                                        if ($pmode == "GPAY") {
                                            $paymode = "GPAY";
                                        } else {
                                            $paymode = "Cash";
                                        }

                                        $ctr++;
                                        echo "<tr>";
                                        echo "<td class='text-uppercase'>$pmain[mydt]</td>";
                                        echo "<td class='text-uppercase'>$pmain[name]</td>";
                                        echo "<td class='text-uppercase'>$fetcons[purpvisit]</td>";
                                        echo "<td class='text-right text-uppercase'>$consfees</td>";
                                        echo "<td class='text-right'>$medamount</td>";
                                        echo "<td class='text-right'></td>";
                                        echo "<td class='text-right'>$treatamt</td>";
                                        echo "<td class='text-right'>$paidamt</td>";
                                        echo "<td class='text-center'>$paytype</td>";
                                        echo "<td class='text-center'>$paymode</td>";
//                                        echo "<td class='text-uppercase'>$pmain[cmpltdt]</td>";
//                                        echo "<td class='text-uppercase'>$pmain[notes1] $pmain[notes2] $pmain[notes3] $pmain[notes4]</td>";
                                        echo "</tr>";
                                    }
                                    ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
                <!-- /.col-lg-6 -->
            </div>
            <?php
            include("includes/linksfoot.php");
            ?>
        </div>
    </body>

    </html>
    <?php
}

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

    $getpmain = mysqli_query($conn, "Select a.*,b.name,date_format(a.date,'%d-%m-%Y') as mydt  from actran a, accmast b where a.grpcode = '010' and a.accode = b.accode 
                                     and a.date >= '$mfrmdate' AND a.date <= '$mtodate' and a.amount > 0 group by a.date,a.accode order by a.date ASC") or die(mysqli_error($conn));

    $contents .= "Date, Patient Name, Treatment Name, Consultation, Product, Homeopathy Medicines, Paid Treatment Amt, Paid Amount, Payment Mode, Bank/Cash \n";

    $paidamt = 0;
    while ($pmain = mysqli_fetch_array($getpmain)) {
        $maccode = $pmain['accode'];

        $getcons = mysqli_query($conn, "Select purpvisit,sum(consultfees) as tconsultfees, sum(medtotal) as medtotal, sum(meddiscamt) as meddiscamt,
                                                                        sum(treattotal) as treattotal, sum(trdiscamt) as trdiscamt from consultmain where vouchdt = '$pmain[date]'
                                                                        and accode='$maccode'") or die(mysqli_error($conn));
        $fetcons = mysqli_fetch_array($getcons);
        $consfees = $fetcons['tconsultfees'];
        $medamount = $fetcons['medtotal'] - $fetcons['meddiscamt'];
        $treatamt = $fetcons['treattotal'] - $fetcons['trdiscamt'];

        $getpaid = mysqli_query($conn, "Select sum(amount) as tpaidamt,source, bankinit from actran where date = '$pmain[date]' and accode='$maccode' and amount > 0") or die(mysqli_error($conn));
        $fetpaid = mysqli_fetch_array($getpaid);
        $paidamt = $fetpaid['tpaidamt'];
        $ptype = $fetpaid['source'];
        if ($ptype == "BR") {
            $paytype = "Bank";
        } else {
            $paytype = "Cash";
        }
        $pmode = $fetpaid['bankinit'];
        if ($pmode == "GPAY") {
            $paymode = "GPAY";
        } else {
            $paymode = "Cash";
        }

        $ctr++;

        $contents .= "$pmain[mydt]" . ",";
        $contents .= "$pmain[name]" . ",";
        $contents .= "$fetcons[purpvisit]" . ",";
        $contents .= "$consfees" . ",";
        $contents .= "$medamount" . ",";
        $contents .= "" . ",";
        $contents .= "$treatamt" . ",";
        $contents .= "$paidamt" . ",";
        $contents .= "$paytype" . ",";
        $contents .= "$paymode" . "\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;
    }
}


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

    if ($a == 0) {
        echo "<script>window.close();</script>";
    } else {
        $gettran = mysqli_query($conn, "SELECT *, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') AS mydate FROM `$trandb` WHERE `itemcode` = '$itemname' 
                                        and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $godowncond order by vouchdt, ordno, vouchno") or die(mysqli_error($conn));
        $getno = mysqli_num_rows($gettran);

        $getitem = mysqli_query($conn, "Select code, name from item where code = '$itemname'") or die(mysqli_error($conn));
        $fetitem = mysqli_fetch_array($getitem);

        require("library/fpdf/fpdf.php");

        class pdf extends fpdf {

            //            function header() {
            //                $this->SetX(15);
            //                $this->SetFont('Arial', 'I', 8);
            //                $this->Cell(0, 10, "$fetitem[name]", 1);
            //            }

            function Footer() {

                //Position at 1.5 cm from bottom

                $this->SetY(-15);

                //Arial italic 8

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

                //Page number

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

        }

        $pdf = new pdf('P', 'mm', 'A4');

        $pdf->AliasNbPages();

        $pdf->AddPage();
        $pdf->SetFont('Arial', 'I', 10);
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetFillColor(96, 130, 191);
        $pdf->Cell(8, 7, "Sr.", 1, 0, '', 1);
        $pdf->Cell(20, 7, "Date", 1, 0, '', 1);
        $pdf->Cell(20, 7, "Voucher", 1, 0, '', 1);
        $pdf->Cell(30, 7, "Add", 1, 0, '', 1, 'R');
        $pdf->Cell(30, 7, "Less", 1, 0, '', 1, 'R');
        $pdf->Cell(30, 7, "Rate", 1, 0, '', 1, 'R');
        $pdf->Cell(30, 7, "Balance", 1, 0, '', 1, 'R');
        $pdf->SetFont('Arial', '', 1, 0);

        $pdf->SetTextColor(0, 0, 0);
        $pdf->Ln();
        $gradd = 0;
        $grless = 0;

        //        $getopen = mysqli_query($conn, "SELECT sum(opening) as sqty FROM `$maindb` WHERE `code` = '$itemname'") or die(mysqli_error($conn));
        //        $iopen = mysqli_fetch_array($getopen);
        //        $openqty1 = $iopen['sqty'];

        $getop = mysqli_query($conn, "SELECT sum(qty) as balqty FROM `$trandb` WHERE   `itemcode` = '$itemname' and
                                                          `vouchdt` < '$mfrmdate' $godowncond") or die(mysqli_error($conn));
        $iop = mysqli_fetch_array($getop);
        $opqty = $iop['balqty'];
        $openqty = $openqty1 + $opqty;

        $ctr = 1;
        $balqty = $openqty;
        $pdf->SetFont('Arial', '', 8);
        $pdf->Cell(8, 7, "$ctr", 1);
        $pdf->Cell(20, 7, " ", 1);
        $pdf->Cell(20, 7, "Opening", 1);

        if ($balqty > 0) {
            $pdf->Cell(30, 7, "$balqty", 1, 0, 'R');
            $pdf->Cell(30, 7, " ", 1);
            $gradd = $gradd + $balqty;
        } else {
            $lqty = abs($balqty);
            $pdf->Cell(30, 7, " ", 1);
            $pdf->Cell(30, 7, "$lqty", 1, 0, 'R');
            $grless = $grless + $lqty;
        }
        $pdf->Cell(30, 7, "$balqty", 1, 0, 'R');
        $pdf->Ln();

        while ($row = mysqli_fetch_object($gettran)) {
            $qty = $row->qty;

            $ctr++;
            $pdf->Cell(8, 7, "$ctr", 1);
            $pdf->Cell(20, 7, "$row->mydt", 1);
            $pdf->Cell(20, 7, "$row->source" . "$row->vouchno", 1);
            if ($qty > 0) {
                $pdf->Cell(30, 7, "$qty", 1, 0, 'R');
                $pdf->Cell(30, 7, " ", 1);
                $balqty = $balqty + $qty;
                $gradd = $gradd + $qty;
            } else {
                $lqty = abs($qty);
                $pdf->Cell(30, 7, " ", 1, 0, 'R');
                $pdf->Cell(30, 7, "$lqty", 1, 0, 'R');
                $balqty = $balqty - $lqty;
                $grless = $grless + $lqty;
            }
            $pdf->Cell(30, 7, "$row->rate", 1, 0, 'R');
            $pdf->Cell(30, 7, "$balqty", 1, 0, 'R');
            $pdf->Ln();
        }
        $grbal = $balqty;
        $pdf->Cell(8, 7, " ", 1);
        $pdf->Cell(20, 7, " ", 1);
        $pdf->Cell(20, 7, "Total", 1, 0, 'R');
        $pdf->Cell(30, 7, "$gradd", 1, 0, 'R');
        $pdf->Cell(30, 7, "$grless", 1, 0, 'R');
        $pdf->Cell(30, 7, " ", 1, 0, 'R');
        $pdf->Cell(30, 7, "$grbal", 1, 0, 'R');
        $pdf->SetFont('Arial', '', 1, 0);

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

        $pdf->Output("Inward/Outward.pdf", "D");
    }
}
?>