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

$formname = "REVENUE";
$file = "debntreg_fr.php";
$keycond = " and `credtype` = 'G'";
$zroot = $_SESSION["zdir"];
$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysql_select_db($zdb);

$agentcond = "";
if (!empty($_POST["agent"])) {
    $agent = trim($_POST["agent"]);
    $agentcond = " AND `a`.`agent` = '$agent' ";
}

$disp = "";
$ncond = "";
if (!empty($_POST["name"])) {
    $name = trim($_POST["name"]);
    $ncond = " AND `a`.`name` LIKE '%$name%' ";
}

$paxcond = "";
if (!empty($_POST["frompax"]) && !empty($_POST["topax"])) {
    $frompax = $_POST["frompax"];
    $topax = $_POST["topax"];
    $paxcond = " AND `a`.`paxid` BETWEEN '$frompax' AND '$topax'";
    $disp = "/ Pax No From $frompax AND $topax ";
}

$hajicond = "";
if (!empty($_POST["fromhaji"]) && !empty($_POST["tohaji"])) {
    $fromhaji = $_POST["fromhaji"];
    $tohaji = $_POST["tohaji"];
    $paxcond = " AND `a`.`hajino` BETWEEN '$fromhaji' AND '$tohaji'";
    $disp = "/ Haji No From $fromhaji AND $tohaji ";
}


$surcond = "";
if (!empty($_POST["surname"])) {
    $surname = trim($_POST["surname"]);
    $ncond = " AND `a`.`surname` LIKE '%$surname%' ";
}

$compcond = "";
if (!empty($_POST["siscono"])) {
    $siscono = trim($_POST["siscono"]);
    $compcond = " AND `a`.`company` = '$siscono' ";
}

$dcond = "";
if (!empty($_POST["district"])) {
    $district = trim($_POST["district"]);
    $dcond = " AND `a`.`district` = '$district' ";
    $disp .= "/ District - $district ";
}

$psnocond = "";
if (!empty($_POST["passno"])) {
    $passno = trim($_POST["passno"]);
    $passptcond = " AND `a`.`passportno` = '$passno' ";
}

$scond = "";
if (!empty($_POST["state"])) {
    $state = trim($_POST["state"]);
    $scond = " AND `a`.`state` = '$state' ";
    $disp .= "/ State - $state ";
}

$fcond = "";
if (!empty($_POST["for"])) {
    $for = trim($_POST["for"]);
    $fcond = " AND `a`.`visitingfor` = '$for' ";
    $disp .= "/ For - $for ";
}

$ccond = "";
if (!empty($_POST["class"])) {
    $class = trim($_POST["class"]);
    $ccond = " AND `b`.`class` LIKE '%$class%' ";
}

$bcond = "";
if (!empty($_POST["batch"])) {
    $batch = $_POST["batch"];
    $bcond = " AND `b`.`batchno` = '$batch' ";
    $disp .= "/ batch - $batch ";
}

$joincond = "AND `b`.`srno` = 1";
$acond = "";
if (!empty($_POST["airline"])) {
    $airline = trim($_POST["airline"]);
    $acond = " AND `b`.`airline_code` = '$airline' ";
    $joincond = "";
}

$ddcond = "";
if (!empty($_POST["deptdate"])) {
    $deptdate = trim($_POST["deptdate"]);
    $ddcond = " AND `b`.`dept_date` = '$deptdate' ";
    $disp .= "/ B/J Date - " . mydt2d($deptdate);
}

$adcond = "";
if (!empty($_POST["arvdate"])) {
    $arvdate = trim($_POST["arvdate"]);
    $adcond = " AND `b`.`dept_date` = '$arvdate' ";
    $disp .= " / J/B Date - " . mydt2d($arvdate);
    $joincond = "AND `b`.`srno` = 2";
}

$agcond = "";
if (!empty($_POST["agent"])) {
    $agent = trim($_POST["agent"]);
    $agcond = " AND `a`.`agent` = '$agent' ";
    $disp .= " / Agent - " . $agent;
}


$getcomp = mysql_query("SELECT `compname`, `address`, `city`, `pin`, `tel`, `email`, `fax` FROM $zroot.`company` WHERE `cono` = '" . $_SESSION["cono"] . "'") or die(mysql_error());
$comp = mysql_fetch_array($getcomp);
mysql_free_result($getcomp);

$tourdb = DBSUFFIX . $_SESSION["cono"] . "_" . $_SESSION["tourname"];
mysql_select_db($tourdb);

$sortby = $_POST["sortby"];
if ($sortby == '2') {
    $sortord = "`a`.`hajino`";
} else {
    $sortord = "`a`.`paxid`";
}


$get = mysql_query("SELECT `a`.`surname`,`a`.`name`,`a`.`midname`, `a`.`suffix`, `a`.`paxid`, `a`.`hajino`, `a`.`agent` FROM `pax`
                        a LEFT JOIN `airline_detail` b  ON `a`.`paxid` = `b`.`paxno` $joincond WHERE `name` IS NOT NULL $paxcond
                        $hajicond $ncond $dcond $scond $fcond $ccond $bcond $acond $ddcond $adcond $agcond $surcond $compcond $passptcond
                        $agentcond ORDER BY $sortord, `b`.`dept_date`  ") or die(mysql_error());
$getno = mysql_num_rows($get);

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


    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
        table td{
            font-size: 14px;
        }
    </style>
    </head>
    <body>
        <div class="container">
            <?php include("includes/head_rep_siscono.php"); ?>
            <div class="row" style="background: white;">
                <div class="col-lg-12 pad" id="HTMLtoPDF">
                    <table style="background: white;width: 100%" class="table table-condensed table-hover table-bordered ">
                        <thead class="mySearchTr">
                            <tr>
                                <th>Sr.</th>
                                <?php if ($sortby === "1"): ?>
                                    <th>P. No</th>
                                <?php elseif ($sortby === "2"): ?>
                                    <th>H. No</th>
                                <?php else: ?>
                                    <th>H. No</th>
                                    <th>P. No</th>
                                <?php endif; ?>
                                <th style="text-align:left;">Full Name</th>
                                <th style="text-align:right;">Amount</th>
                                <!--<th style="text-align:right;">Recd. Amt</th>-->
                                <th style="text-align:right;">Bank. Amt</th>
                                <th style="text-align:right;">Cash. Amt</th>
                                <th style="text-align:right;">Balance</th>
                                <th>Agent</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $totbal = 0;
                            $totbank = 0;
                            $totcash = 0;
                            $totnet = 0;
                            while ($pax = mysql_fetch_assoc($get)) {
                                $ctr++;
                                echo "<tr> \n";
                                echo "<td style=\"text-align:left;\"> \n <span> $ctr </span> \n </td> \n";
                                if ($sortby === "1") {
                                    echo "<td style=\"text-align:left;\"> \n <span> $pax[paxid] </span> \n </td> \n";
                                } elseif ($sortby === "2") {
                                    echo "<td style=\"text-align:left;\"> \n <span> $pax[hajino] </span> \n </td> \n";
                                } else {
                                    echo "<td style=\"text-align:left;\"> \n <span> $pax[hajino] </span> \n </td> \n";
                                    echo "<td style=\"text-align:left;\"> \n <span> $pax[paxid] </span> \n </td> \n";
                                }
                                echo "<td style=\"text-align:left;\"> \n <span> $pax[suffix] $pax[surname] $pax[name] $pax[midname] </span> \n </td> \n";
                                $getnetamt = mysql_query("SELECT `netamt` FROM `tour_amount` WHERE `paxno` = '$pax[paxid]' LIMIT 0,1");
                                $amt = mysql_fetch_assoc($getnetamt);
                                mysql_free_result($getnetamt);
                                $paid = 0;
                                $bal = 0;
                                $getcamt = mysql_query("SELECT sum(`amount`) as `amt` FROM `cashrecp` WHERE `paxno` = '$pax[paxid]'");
//                                if (mysql_num_rows($getcamt)) {
                                $camt = mysql_fetch_array($getcamt);
                                $cpaid = $camt["amt"];
//                                }

                                $getbamt = mysql_query("SELECT sum(`amount`) as `amt` FROM `bankrecp` WHERE `paxno` = '$pax[paxid]' LIMIT 0,1");
//                                if (mysql_num_rows($getbamt)) {
                                $bamt = mysql_fetch_array($getbamt);
                                $bpaid = $bamt["amt"];
//                                }
                                $bal = $amt["netamt"] - $bpaid - $cpaid;
                                echo "<td style=\"text-align:right;\"> \n <span> $amt[netamt]</span> \n </td> \n";
                                echo "<td style=\"text-align:right;\"> \n <span> $bpaid </span> \n </td> \n";
                                echo "<td style=\"text-align:right;\"> \n <span> $cpaid </span> \n </td> \n";
                                echo "<td style=\"text-align:right;\"> \n <span> $bal </span> \n </td> \n";
                                echo "<td > \n <span> $pax[agent] </span> \n </td> \n";
                                echo "</tr> \n ";

                                $totnet += $amt["netamt"];
                                $totbal += $bal;
                                $totbank += $bpaid;
                                $totcash += $cpaid;
                            }
                            ?>
                            <tr>
                                <td colspan="4" class='text-right h4' style='color:red;'>
                                    Grand Total
                                </td>
                                <td class='text-right h4' style='color:red;'>
                                    <?php echo $totnet; ?>
                                </td>
                                <td class='text-right h4' style='color:red;'>
                                    <?php echo $totbank; ?>
                                </td>
                                <td class='text-right h4' style='color:red;'>
                                    <?php echo $totcash; ?>
                                </td>
                                <td class='text-right h4' style='color:red;'>
                                    <?php echo $totbal; ?>
                                </td>
                                <td class='text-right h4'>
                                </td>

                            </tr>
                        </tbody>
                    </table>
                </div>


            </div>
        </div>
        <script src="js/jspdf.js"></script>
        <script src="js/jquery-2.1.3.js"></script>
        <script src="js/pdfFromHTML.js"></script>
        <?php include("includes/linksfoot.php");
        ?>
    </body>
    </html>
    <?php
}
if (isset($_POST["subpdf"])) {


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

    class pdf extends fpdf {

        function Header() {
            
        }

        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('L', 'mm', 'A4');



    $pdf->AliasNbPages();

    $pdf->AddPage();

    $pdf->SetMargins(10, 10, 10);

    $pdf->SetXY(5, 5);



    if (!empty($_POST["siscono"])) {

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

        $getcomp = mysql_query("SELECT `name` as `compname`, `address`, `city`, `pin`, `tel`, `email`, `fax` FROM $zroot.`sisterconcern` WHERE `siscono` = '$siscono'") or die(mysql_error());

        $comp = mysql_fetch_array($getcomp);

        //Title



        $pdf->Cell(0, 0, $comp["compname"], 0, 0, 'C');

        $pdf->Ln(10);

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

        $pdf->Cell(0, 0, $comp["address"] . " " . $comp["city"] . " - " . $comp["pin"] . ".", 0, 0, 'C');

        $pdf->Ln(10);

        $pdf->Cell(0, 0, "Tel :" . $comp["tel"] . ", Fax :" . $comp["fax"] . ". Email : " . $comp["email"], 0, 0, 'C');

        $pdf->Ln(10);

        $pdf->Cell(160, 0, "$formname - " . $disp . "Total Paxes : " . $getpaxno, 0, 0, 'L');

        $pdf->Cell(160, 0, "Report Date- " . Date("d-m-Y"), 0, 0, 'L');
    } else {

        
        $getcomp = mysql_query("SELECT `name` as `compname`, `address`, `city`, `pin`, `tel`, `email`, `fax` FROM $zroot.`sisterconcern` WHERE `siscono` = '$siscono'") or die(mysql_error());

        $comp = mysql_fetch_array($getcomp);


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

        $pdf->Cell(160, 0, "$formname - " . $disp . "Total Paxes : " . $getpaxno, 0, 0, 'L');

        $pdf->Cell(160, 0, "Report Date- " . Date("d-m-Y"), 0, 0, 'L');
    }

    $pdf->Ln(10);


//$pdf->SetMargins(1,1,1);
//$pdf->SetXY(5,5);

    $pdf->SetFont('Arial', 'B', 11);
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFillColor(96, 130, 191);
    $pdf->Cell(8, 8, 'SR.', 1, 0, '', 1);
    if ($sortby == "1") {
        $pdf->Cell(12, 8, 'P.No', 1, 0, '', 1);
    } elseif ($sortby == "2") {
        $pdf->Cell(12, 8, 'H.No', 1, 0, '', 1);
    } else {
        $pdf->Cell(12, 8, 'H.No', 1, 0, '', 1);
        $pdf->Cell(12, 8, 'P.No', 1, 0, '', 1);
    }
    $pdf->Cell(120, 8, 'Full Name', 1, 0, '', 1);
    $pdf->Cell(24, 8, 'Amount', 1, 0, '', 1);
    $pdf->Cell(12, 8, 'Bank.Amt', 1, 0, '', 1);
    $pdf->Cell(12, 8, 'Cash.Amt', 1, 0, '', 1);
    $pdf->Cell(24, 8, 'Balance', 1, 0, '', 1);
    $pdf->Cell(33, 8, 'Agent', 1, 0, '', 1);

    $pdf->SetFont('Arial', '', 10);
    $pdf->SetTextColor(0, 0, 0);
    $pdf->Ln();

    $ctr = 0;
    $totbal = 0;
    $totpaid = 0;
    $totnet = 0;

    while ($pax = mysql_fetch_assoc($get)) {
        $ctr++;


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

        $pdf->Cell(8, 8, $ctr, 1);
        if ($sortby === "1") {

            $pdf->Cell(12, 8, $pax[paxid], 1);
        } elseif ($sortby === "2") {
            $pdf->Cell(12, 8, $pax[hajino], 1);
        } else {
            $pdf->Cell(12, 8, $pax[hajino], 1);
            $pdf->Cell(12, 8, $pax[paxid], 1);
        }
        $pdf->Cell(120, 8, $pax[suffix] . " " . $pax[surname] . " " . $pax[name] . " " . $pax[midname], 1);
        $getnetamt = mysql_query("SELECT `netamt` FROM `tour_amount` WHERE `paxno` = '$pax[paxid]' LIMIT 0,1");
        $amt = mysql_fetch_assoc($getnetamt);
        mysql_free_result($getnetamt);
        $paid = 0;
        $bal = 0;
        $getcamt = mysql_query("SELECT sum(`amount`) as `amt` FROM `cashrecp` WHERE `paxno` = '$pax[paxid]' LIMIT 0,1");
        if (mysql_num_rows($getcamt)) {
            $camt = mysql_fetch_assoc($getcamt);
            mysql_free_result($getcamt);
            $cpaid += $camt["amt"];
        }

        $getbamt = mysql_query("SELECT sum(`amount`) as `amt` FROM `bankrecp` WHERE `paxno` = '$pax[paxid]' LIMIT 0,1");
        if (mysql_num_rows($getbamt)) {
            $bamt = mysql_fetch_assoc($getbamt);
            mysql_free_result($getbamt);
            $bpaid += $bamt["amt"];
        }

        $bal = $amt["netamt"] - $paid;
        $pdf->Cell(24, 8, $amt[netamt], 1, 0, 'R');
        $pdf->Cell(12, 8, $bpaid, 1, 0, 'R');
        $pdf->Cell(12, 8, $cpaid, 1, 0, 'R');
        $pdf->Cell(24, 8, $bal, 1, 0, 'R');
        $pdf->Cell(33, 8, $pax[agent], 1);


        $totnet += $amt["netamt"];
        $totbal += $bal;
        $totpaid += $paid;
        $pdf->Ln();
    }

    $pdf->SetFont('Arial', 'B', 10);
    $pdf->SetTextColor(255, 0, 0);
    if ($sortby === "1") {
        $pdf->Cell(88, 13, 'Grand Total', 'B,L');
    } elseif ($sortby === "2") {
        $pdf->Cell(88, 13, 'Grand Total', 'B,L');
    } else {
        $pdf->Cell(100, 13, 'Grand Total', 'B,L');
    }

    $pdf->Cell(25, 13, '', 'B', 0, 'R');
    $pdf->Cell(25, 13, '', 'B', 0, 'R');
    $pdf->Cell(23, 13, $totnet, 'B', 0, 'R');
    $pdf->Cell(25, 13, $totpaid, 'B', 0, 'R');
    $pdf->Cell(24, 13, $totbal, 'B', 0, 'R');
    $pdf->Cell(35, 13, '', 'B,R', 0, 'R');



    $pdf->Output('Revenue.pdf', 'D');
}

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


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


    if ($sortby === "1") {
        $contents .= "Sr,P.No,Full Name,Amount,Bank.Amt,Cash.Amt,Balance,Agent,\n";
    } elseif ($sortby === "2") {
        $contents .= "Sr,H.No,Full Name,Amount,Bank.Amt,Cash.Amt,Balance,Agent,\n";
    } else {
        $contents .= "Sr,P.No,H.No,Full Name,Amount,Bank.Amt,Cash.Amt,Balance,Agent,\n";
    }



    $ctr = 0;
    $totbal = 0;
    $totpaid = 0;
    $totnet = 0;
    while ($pax = mysql_fetch_assoc($get)) {
        $ctr++;
        $contents .= $ctr . ",";


        if ($sortby === "1") {
            $contents .= $pax['paxid'] . ",";
        } elseif ($sortby === "2") {
            $contents .= $pax['hajino'] . ",";
        } else {
            $contents .= $pax['paxid'] . ",";
            $contents .= $pax['hajino'] . ",";
        }
        $contents .= $pax['suffix'] . " " . $pax['surname'] . " " . $pax['name'] . " " . $pax['midname'] . ",";

        $getnetamt = mysql_query("SELECT `netamt` FROM `tour_amount` WHERE `paxno` = '$pax[paxid]' LIMIT 0,1");
        $amt = mysql_fetch_assoc($getnetamt);
        mysql_free_result($getnetamt);
        $paid = 0;
        $bal = 0;
        $getcamt = mysql_query("SELECT sum(`amount`) as `amt` FROM `cashrecp` WHERE `paxno` = '$pax[paxid]' LIMIT 0,1");
        if (mysql_num_rows($getcamt)) {
            $camt = mysql_fetch_assoc($getcamt);
            mysql_free_result($getcamt);
            $cpaid += $camt["amt"];
        }

        $getbamt = mysql_query("SELECT sum(`amount`) as `amt` FROM `bankrecp` WHERE `paxno` = '$pax[paxid]' LIMIT 0,1");
        if (mysql_num_rows($getbamt)) {
            $bamt = mysql_fetch_assoc($getbamt);
            mysql_free_result($getbamt);
            $bpaid += $bamt["amt"];
        }
        $bal = $amt["netamt"] - $paid;
        $contents .= $amt['netamt'] . ",";
        $contents .= $bpaid . ",";
        $contents .= $cpaid . ",";
        $contents .= $bal . ",";
        $contents .= $pax['agent'] . "\n";


        $totnet += $amt["netamt"];
        $totbal += $bal;
        $totpaid += $paid;
    }
    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= "Grand Total" . ",";
    $contents .= $totnet . ",";
    $contents .= $totpaid . ",";
    $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;
    }
}
?>  
