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

$maindb = 'bankpay';
$actran = 'actran';
$formname = "Expense report";
$file = "expenserep_fr.php";



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



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

$group = $_POST["group"];
$acname = $_POST["acname"];
$cust = $_POST["cust"];
$staff = $_POST["staff"];
$fdate = $_POST["fdate"];
$tdate = $_POST["tdate"];
$dtype = $_POST["dtype"];

$gcond = '';
$mcond = '';
$cucond = '';
$stcond = '';
$fdatecond = '';
$tdatecond = '';

if (!empty($group)) {
$gcond = " AND `grpcode`='$group' ";
}

if (!empty($acname)) {
$mcond = " AND `accode`='$acname' ";
}

if (!empty($cust)) {
$cucond = " AND `accode`='$cust' ";
}
if (!empty($staff)) {
$stcond = " AND `stafcode`='$staff' ";
}

$mord = " orde by accode, date ";
$dcond = " and `date` >= '$fdate' and `date` <= '$tdate' ";

if (empty($fdate)) {
browse("From Date Cannot Be Empty");
header("refresh:1; expenserep.php");
}

if (empty($tdate)) {
browse("To Date Cannot Be Empty");
header("refresh:1; expenserep.php");
}



$mfrmdate = date("d-m-Y", strtotime($fdate));
$mtodate = date("d-m-Y", strtotime($tdate));

$getcash = mysqli_query($conn, "select * from `accmast` where accode != '' $gcond $mcond and  
                            grpcode in (select grpcode from grpmast where UPPER(type) = 'E' and UPPER(subtype) = 'E' )")or die(mysqli_error($conn));

$mfdt = date("d-m-Y", strtotime($mfrmdate));
$mtdt = date("d-m-Y", strtotime($mtodate));
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");
        ?>
        <div class="row">
            <div class="col-lg-4 col-md-2 col-sm-2 col-xs-2 pad">
                <label> <?php echo $cashsale; ?></label>
            </div>
        </div>
        <div class="row">
            <?php
            if ($dtype == 1) {
            ?>
            <div class="col-lg-12 pad">
                <table style="width:100%;background: white;" class="table table-condensed table-striped table-bordered table-hover">
                    <thead>
                        <tr style="background-color:#78909C">
                            <th style="width:10%;">Sr.No</th>
                            <th style="width:30%;">Name </th>
                            <th style="width:10%;" class='text-right'>Amount</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        $ctr = 0;
                        $totamt = 0;


                        while ($pmain = mysqli_fetch_array($getcash)) {
                        $getpart = mysqli_query($conn, "Select accode, sum(amount) as tamt from `actran` where `accode` = '$pmain[accode]' GROUP BY accode")or die(mysqli_error($conn));


                        while ($cmain = mysqli_fetch_array($getpart)) {

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


                        $mamt = abs($cmain[tamt]);
                        $ctr++;
                        Echo "<tr>";
                        Echo "<td>$ctr</td>";
                        Echo "<td>$showparty[name]</td>";
                        Echo "<td class='text-right'>$mamt</td>";
                        Echo "</tr>";
                        $totamt = $totamt + $mamt;
                        }
                        }

                        Echo "<tr>";
                        Echo "<td></td>";
                        Echo "<td class='text-right'><b>Total</b></td>";
                        Echo "<td class='text-right'><b>$totamt</b></td>";
                        Echo "</tr>";
                        ?>
                    </tbody>
                </table>
            </div>
            <?php } else { ?>
            <div class="col-lg-12 pad">
                <table style="width:100%;background: white;" class="table table-condensed table-striped table-bordered table-hover">
                    <thead>
                        <tr style="background-color:#78909C">
                            <th style="width:10%;">Sr.</th>
                            <th style="width:15%;">Date</th>
                            <th style="width:15%;">Voucher</th>
                            <th style="width:35%;">Name </th>
                            <th style="width:15%;" class='text-right'>Amount</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        $ctr = 0;
                        $totamt = 0;


                        while ($pmain = mysqli_fetch_array($getcash)) {
                        $getpart = mysqli_query($conn, "Select *, date_format(`date`,'%d/%m/%Y') as mydt from `actran` where `accode` = '$pmain[accode]' ")or die(mysqli_error($conn));
//                                    $getpart = mysqli_query($conn, "select * from `actran` where amount!='' $dcond $cucond $stcond and accode in (select accode from $getcash) $mord")or die(mysqli_error($conn));
//        browse(mysqli_num_rows($getpart));


                        while ($cmain = mysqli_fetch_array($getpart)) {

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


                        $mamt = abs($cmain[amount]);
                        $ctr++;
                        Echo "<tr>";
                        Echo "<td>$ctr</td>";
                        Echo "<td>$cmain[mydt]</td>";
                        Echo "<td>$cmain[source] $cmain[vouchno]</td>";
                        Echo "<td>$showparty[name]</td>";
                        Echo "<td class='text-right'>$mamt</td>";
                        Echo "</tr>";
                        $totamt = $totamt + $mamt;
                        }
                        }

                        Echo "<tr>";
                        Echo "<td></td>";
                        Echo "<td></td>";
                        Echo "<td></td>";
                        Echo "<td class='text-right'><b>Total</b></td>";
                        Echo "<td class='text-right'><b>$totamt</b></td>";
                        Echo "</tr>";
                        ?>
                    </tbody>
                </table>
            </div>
            <?php } ?>
        </div>
    </div>
    <?php
    include("includes/linksfoot.php");
    ?>
</body>
</html>
<?php
}

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

$group = $_POST["group"];
$acname = $_POST["acname"];
$cust = $_POST["cust"];
$staff = $_POST["staff"];
$fdate = $_POST["fdate"];
$tdate = $_POST["tdate"];
$dtype = $_POST["dtype"];

$gcond = '';
$mcond = '';
$cucond = '';
$stcond = '';
$fdatecond = '';
$tdatecond = '';

if (!empty($group)) {
$gcond = " AND `grpcode`='$group' ";
}

if (!empty($acname)) {
$mcond = " AND `accode`='$acname' ";
}

if (!empty($cust)) {
$cucond = " AND `accode`='$cust' ";
}
if (!empty($staff)) {
$stcond = " AND `stafcode`='$staff' ";
}

$mord = " orde by accode, date ";
$dcond = " and `date` >= '$fdate' and `date` <= '$tdate' ";

if (empty($fdate)) {
browse("From Date Cannot Be Empty");
header("refresh:1; expenserep.php");
}

if (empty($tdate)) {
browse("To Date Cannot Be Empty");
header("refresh:1; expenserep.php");
}



$mfrmdate = date("d-m-Y", strtotime($fdate));
$mtodate = date("d-m-Y", strtotime($tdate));

$getcash = mysqli_query($conn, "select * from `accmast` where accode != '' $gcond $mcond and  
                            grpcode in (select grpcode from grpmast where UPPER(type) = 'E' and UPPER(subtype) = 'E' )")or die(mysqli_error($conn));

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

 if ($dtype == 1) {

$contents.= "Sr.No,Name,Amount,\n";

$ctr = 1;
$totamt = 0;


while ($pmain = mysqli_fetch_array($getcash)) {
$getpart = mysqli_query($conn, "Select accode, sum(amount) as tamt from `actran` where `accode` = '$pmain[accode]' GROUP BY accode")or die(mysqli_error($conn));


while ($cmain = mysqli_fetch_array($getpart)) {

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


$mamt = abs($cmain[tamt]);

$spname = $showparty[name];

$contents.= $ctr . ",";
$contents.= $spname . ",";
$contents.= $mamt . "\n";
$ctr++;

$totamt = $totamt + $mamt;
}
}
$contents.= " " . ",";
$contents.= "Total" . ",";
$contents.= $totamt . "\n";

} else { 
    


$contents.= "Sr.No,Date,Voucher,Name,Amount,\n";

            $ctr = 1;
            $totamt = 0;


            while ($pmain = mysqli_fetch_array($getcash)) {
            $getpart = mysqli_query($conn, "Select *, date_format(`date`,'%d/%m/%Y') as mydt from `actran` where `accode` = '$pmain[accode]' ")or die(mysqli_error($conn));


            while ($cmain = mysqli_fetch_array($getpart)) {

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


            $mamt = abs($cmain[amount]);
            
            $cmydt = $cmain[mydt];
            $csourcev = $cmain[source] . $cmain[vouchno];
            $spartyname = $showparty[name];
            
            $contents.= $ctr . ",";
            $contents.= $cmydt . ",";
            $contents.= $csourcev . ",";
            $contents.= $spartyname . ",";
            $contents.= $mamt . "\n";
            $ctr++;
            
            $totamt = $totamt + $mamt;
            }
            }
            $contents.= " " . ",";
            $contents.= " " . ",";
            $contents.= " " . ",";
            $contents.= "Total" . ",";
            $contents.= $totamt . "\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;
    }
}
?>