<?php session_start();
?>
<?php date_default_timezone_set('Asia/Colombo'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>Checker Report</title>
        <style>
            body{
                font-family:Arial, Helvetica, sans-serif;
                font-size:16px;
            }
            table
            {
                border-collapse:collapse;
            }
            table, td, th
            {
                border:1px solid black;
                font-family:Arial, Helvetica, sans-serif;
                padding:5px;
            }
            th
            {
                font-weight:bold;
                font-size:15px;

            }
            td
            {
                font-size:15px;
                border-bottom:thick;
                border-left:none;
                border-right:none;


            }
        </style>
        <style type="text/css">
            <!--
            .style1 {
                color: #0000FF;
                font-weight: bold;
                font-size: 24px;
            }
            -->
        </style>
    </head>

    <body>
        <!-- Progress bar holder -->


        <?php
        require_once("connection_sql.php");

        $dt1 = $_GET['dtfrom'];
        $dt2 = $_GET['dtto'];

  // if($_GET['brand']!="All"){
  //                 $rst_sql .= " and Brand='".$_GET['brand']."'";
  //           }

        echo "<center>
   		<table class=\"table table-bordered\" border=\"0\" >";
        echo "<tr>";
        echo "<td><b>CUSTOMER</b></td>";
         echo "<td><b>BEFORE OUT</b></td>";
        echo "<td><b>SALE</b></td>";
        echo "<td><b>RECEIPT</b></td>";
        echo "<td><b>OVER</b></td>";
         echo "<td><b>SETTLE</b></td>";
        echo "<td><b>CURRENT OUT</b></td>";
         echo "<td><b>DIFF</b></td>"; 


        echo "</tr>";

        echo "<tr>";
        $diff=0;
        $outtotbal=0;
        $salestot=0;
        $recetot=0;
        $utitot=0;
        $over=0;
        $outtot=0;
        $difftot=0;

         $sql12 = "delete from tmp_cus";
        $result12 = $conn->query($sql12);

        $sql11 = "Select  * from s_salma where CANCELL='0' and SDATE >='" . $dt1 . "' and SDATE <='" .$dt2 . "'    "; 
        if($_GET['Txtcusco'] !=""){
             $sql11.=" and C_CODE='".$_GET['Txtcusco']."'"; 
        }
        $sql11.="  GROUP BY C_CODE"; 
        foreach ($conn->query($sql11) as $row11) {
             $sql10 = "insert into tmp_cus(CODE) values ('" . $row11['C_CODE'] . "')"; 
             $result10 = $conn->query($sql10); 
        }

        $sql13 = "Select  * from tmp_out  ";
        if($_GET['Txtcusco'] !=""){
             $sql13.=" where   cuscode='".$_GET['Txtcusco']."'"; 
        }
        $sql13.="  GROUP BY cuscode"; 
        foreach ($conn->query($sql13) as $row13) {
             $sql14 = "insert into tmp_cus(CODE) values ('" . $row13['cuscode'] . "')";
             $result14 = $conn->query($sql14); 
        }

         

    $sql = "select * from tmp_cus group by CODE"; 
     
      
    foreach ($conn->query($sql) as $row) {

        

        $sql1 = "Select sum(amount) as TOT from tmp_out where   cuscode='".$row['CODE']."'  GROUP BY cuscode";
        $result1 = $conn->query($sql1);
        $row1 = $result1->fetch();

        $sql2 = "Select sum(GRAND_TOT) as TOT from s_salma where CANCELL='0' and SDATE >='" . $dt1 . "' and SDATE <='" .$dt2 . "' and C_CODE='".$row['CODE']."'  GROUP BY C_CODE";
        $result2 = $conn->query($sql2);
        $row2 = $result2->fetch();

        $sql3 = "Select sum(CA_AMOUNT) as TOT from s_crec where CANCELL='0' and FLAG='REC' and CA_DATE >='" . $dt1 . "' and CA_DATE <='" .$dt2 . "' and CA_CODE='".$row['CODE']."'  GROUP BY CA_CODE";
        $result3 = $conn->query($sql3); 
        $row3 = $result3->fetch();

         
        $sql16 = "SELECT sum(AMOUNT) as TOT from c_bal where CUSCODE='".$row['CODE']."' and   SDATE >='" . $dt1 . "' and SDATE <='" .$dt2 . "' and   trn_type='REC'   and CANCELL='0'";  
        $result16 = $conn->query($sql16); 
        $row16 = $result16->fetch(); 
 

        $sql4 = "Select sum(C_PAYMENT) as TOT from view_s_ut where  C_DATE >='" . $dt1 . "' and C_DATE <='" .$dt2 . "' and C_CODE='".$row['CODE']."' and TYPE='INV'  GROUP BY C_CODE";
        $result4 = $conn->query($sql4);
        $row4 = $result4->fetch();

        $sql5 = "Select sum(GRAND_TOT-TOTPAY) as TOT from s_salma where CANCELL='0'   and C_CODE='".$row['CODE']."' and GRAND_TOT>TOTPAY and SDATE>='2014-01-01'  GROUP BY C_CODE"; 
        $result5 = $conn->query($sql5);
        $row5 = $result5->fetch();
        
        $diff=$row1['TOT']+$row2['TOT']-$row3['TOT']-$row4['TOT']-$row5['TOT'];
 

        if($diff>1){
            $color="red";
        }else{ 
            $color="";
        }
 
        echo "<tr bgcolor='".$color."''> ";
        echo "<td>" .  $row['CODE'] . "</td>"; 
        echo "<td>" .  number_format($row1['TOT'],2) . "</td>"; 
        echo "<td>" .  number_format($row2['TOT'],2) . "</td>"; 
        echo "<td>" .  number_format($row3['TOT']+$row16['TOT'],2) . "</td>"; 
        echo "<td>" .  number_format($row16['TOT'],2) . "</td>"; 
        echo "<td>" .  number_format($row4['TOT'],2) . "</td>"; 
        echo "<td>" .  number_format($row5['TOT'],2) . "</td>"; 
        echo "<td>" .   number_format($diff,2) . "</td>"; 
        echo "</tr>";
          
          $outtotbal=$outtotbal+$row1['TOT'] ;
          $salestot=$salestot+$row2['TOT'] ; 
          $recetot=$recetot+($row3['TOT']+$row16['TOT']) ; 
          $over=$over+$row16['TOT'] ; 
          $utitot=$utitot+$row4['TOT'] ; 
          $outtot=$outtot+$row5['TOT'] ; 
          $difftot=$difftot+($diff) ;  
    }

            echo "<tr>";
            echo "<td></td>"; 
            echo "<td>" . number_format($outtotbal,2). "</td>";
            echo "<td>" . number_format($salestot,2). "</td>";
            echo "<td>" . number_format($recetot,2). "</td>";
            echo "<td>" . number_format($over,2). "</td>";
             echo "<td>" . number_format($utitot,2). "</td>";
            echo "<td>" . number_format($outtot,2). "</td>";
            echo "<td>" . number_format($difftot,2). "</td>"; 
            echo "</tr>"; 

        echo "</table>";
        ?>
    </body>
</html>
 