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

$maindb = 'purchexpmain';
$trandb = 'purchexptran';
$formname = 'Tour P/L';
$file = "tourp_l_fr.php";

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$tourcode = $_SESSION['tourcode'];
$tourcond = "AND tourno = '$tourcode'";
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);


$mfrmdate = $_POST["fromdt"];
$mtodate = $_POST["todt"];

$class = $_POST["class"];
$classcond = "";

if (!empty($class)) {
    $classcond = "AND `batchno` = '$class'";
}

$getpmain2 = mysqli_query($conn, "SELECT *, DATE_FORMAT(`vouchdt` ,'%d-%m-%Y') AS vouchdt FROM bookform 
                                             WHERE batchno != '' and vouchno != '' $classcond $tourcond group by batchno order by vouchdt ") or die(mysqli_error($conn));


/// for collate -- fields mismatch type
//$getpmain2 = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt` ,'%d-%m-%Y') AS vouchdt,b.batchno, sum(netamt)as totslamt FROM $maindb a, bookform b
//                                        WHERE  a.class = b.batchno COLLATE utf8_unicode_ci  group by b.batchno order by a.vouchdt desc") or die(mysqli_error($conn));

if (isset($_POST["view"])) {
    include("includes/linkshead.php");
?>
    <style type="text/css">
        #tabital {
            font-style: italic;
        }

        #font1 {
            font-style: italic;
        }

        #boldfont {
            font-weight: bold;
        }
    </style>

    </head>

    <body>

        <div class="container">
            <div class="row">
                <div class="col-lg-11 ">
                    <?php include("includes/head_rep.php"); ?>
                    <table class="table table-condensed table-bordered" style="width:100%;">
                        <tr class="mySearchTr" style="height: 30px;">
                            <td style="font-size:18px;width:25%">CLASS </td>
                            <td style="font-size:18px;width:25%;text-align:right">SALE </td>
                            <td style="font-size:18px;width:25%;text-align:right">PURCHASE </td>
                            <td style="font-size:18px;width:25%;text-align:right">P/L</td>
                        </tr>

                        <?php
                        $ttotamt = 0;
                        $totsaleamt = 0;
                        $totpnl = 0;
                        while ($pmain = mysqli_fetch_array($getpmain2)) {

                            $getsale = mysqli_query($conn, " SELECT sum(amount) as totslamt FROM `actran` WHERE amount > 0 and grpcode = '005' 
                                                                and `regnno` in (select regnno from bookform where batchno = '$pmain[batchno]') ") or die(mysqli_error($conn));
                            $fetsale = mysqli_fetch_array($getsale);

                            $getpurchase = mysqli_query($conn, " SELECT totamt FROM `purchexpmain` WHERE class = '$pmain[batchno]' ") or die(mysqli_error($conn));
                            $fetpurch = mysqli_fetch_array($getpurchase);

                            $tourpnl = $fetsale['totslamt'] - $fetpurch['totamt'];
                        ?>

                            <tr style="height: 30px;">
                                <td style="font-size:15px;width:25%;"><?= $pmain['batchno']; ?> </td>
                                <td style="font-size:15px;width:25%;text-align:right"><?= $fetsale['totslamt']; ?></td>
                                <td style="font-size:15px;width:25%;text-align:right"><?= $fetpurch['totamt']; ?></td>
                                <td style="font-size:15px;width:25%;text-align:right"><?= $tourpnl; ?></td>
                            </tr>

                        <?php
                            $totsaleamt = $totsaleamt + $fetsale['totslamt'];
                            $totpuramt = $totpuramt + $fetpurch['totamt'];
                            $totpnl = $totpnl + $tourpnl;
                        }
                        ?>

                        <tr style="height: 30px;color:#0065b3;font-weight:bold">
                            <td style="font-size:18px;width:25%;text-align:right">Total </td>
                            <td style="font-size:18px;width:25%;text-align:right"><?= $totsaleamt; ?></td>
                            <td style="font-size:18px;width:25%;text-align:right"><?= $totpuramt; ?></td>
                            <td style="font-size:18px;width:25%;text-align:right"><?= $totpnl; ?></td>
                        </tr>
                    </table>
                </div>
            </div>
        </div>

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

    </html>
<?php
}
if (isset($_POST["excel"])) {

    $contents .= "Voucher, Date, For, Pax, Supplier, Invoice No., Invoice Date. Amount\n";

    $ctr = 0;
    $sontot = 0;
    $dautot = 0;

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

        $getsupp = mysqli_query($conn, "SELECT accode,name FROM accmast WHERE accode = '$pmain[tiktsupp]' ") or die(mysqli_error($conn));
        $fetsupp = mysqli_fetch_array($getsupp);

        $tiktinvdt = date('d-m-Y', strtotime($pmain['tiktinvdt']));
        $fullname = $pmain['surname'] . " " . $pmain['name'] . " " . $pmain['midname'];

        $ctr++;

        $contents .= "$pmain[vouchno]" . ",";
        $contents .= "$pmain[date]" . ",";
        $contents .= "$pmain[visitingfor]" . ",";
        $contents .= "$fullname" . ",";
        $contents .= "$fetsupp[name]" . ",";
        $contents .= "$pmain[tiktinvno]" . ",";
        $contents .= "$tiktinvdt" . ",";
        $contents .= "$pmain[tiktpuramt]" . "\n";

        $tottiktpuramt = $pmain['tiktpuramt'] + $tottiktpuramt;
        $ttiktpuramt = number_format($tottiktpuramt, 2);
    }

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