<?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>CUSTOMER SUMMERY REPORT</title>
        
    <style>
        .center {
            text-align: center;
        }

        .right {
            text-align: right;
        }

        .tb  { 
           border: 1px solid black;


           border-collapse: collapse;
       }
       .tb td  {

           border-top: 1px solid black;
           border-left: 1px solid black;
        font-size:18px;

           border-collapse: collapse;
       }
       .tb th  {

           border-top: 1px solid black;
           border-left: 1px solid black;


           border-collapse: collapse;
       }

       .tb1 {

           border: 1px solid #000000;
       }

   </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");
        $sql_invpara = "SELECT * from invpara";
            $result_invpara = $conn->query($sql_invpara);
            $row_invpara = $result_invpara->fetch(); 
        ?>
<center>
 <table width="1000px;" cellspacing="0" border="0">

        <tr>
            <th class="center"  colspan=5 ><?php echo $row_invpara['COMPANY']?></th>
        </tr> 
        
         
        <tr> 
         
            <th class='center' colspan='5'>DATE  <?php echo $_GET['dtfrom']?> To  <?php echo $_GET['dtto']?> </th> 
           
        </tr>
         <tr> 
        <?php 
         if($_GET['Txtcusco']!=""){?>
            <th class='center' colspan='5'>Customer - <?php echo $_GET['Txtcusco']?>  </th> 
           <?php }else{ ?>
            <th class='center' colspan='5'>Customer - ALL  </th> 
            <?php } ?>
        </tr>
         <tr>  
            <th class='center' colspan='5'>Brand - <?php echo $_GET['brand']?>  </th>  
        </tr>
        <tr>  
            <th class='center' colspan='5'>Rep - <?php echo $_GET['salesrep']?>  </th>  
        </tr>
         
        <tr>

            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td align="right">&nbsp;</td>
        </tr>
        
        <tr>

            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
        </tr>
        <tr>

            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
        </tr>
        <tr>

            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
        </tr>
        
        <tr>

            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
        </tr>
       
    </table>
    </center>
        <?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=\"1\" >";
        echo "<tr>";
        echo "<td><b>CODE</b></td>"; 
        echo "<td><b>CUSTOMER</b></td>"; 
        echo "<td><b>SALE</b></td>";
        echo "<td><b>CASH</b></td>";
        echo "<td><b>CHEQUE</b></td>";
        echo "<td><b>CASH TT</b></td>";
        echo "<td><b>J/ENTRY</b></td>";
        echo "<td><b>UTILIZE</b></td>";
        echo "<td><b>TOTAL SETTLEMENT</b></td>";
        // echo "<td><b>CREDIT NOTE</b></td>";
        echo "<td><b>BALANCE</b></td>";
        // echo "<td><b>TOTAL OUTSTANDING</b></td>"; 


        echo "</tr>";

        echo "<tr>";
          
        $totsettle=0;
        $gtotsettle=0;
        $tot2=0;
        $tot3=0;
        $tot4=0;
        $tot5=0;
        $tot6=0;
        $tot7=0;
        $tot8=0;
        $tot9=0;
        $tot10=0;

      

    $sql = "Select  * from s_salma where CANCELL='0' and SDATE >='" . $dt1 . "' and SDATE <='" .$dt2 . "'    "; 
    if($_GET['Txtcusco'] !=""){
         $sql.=" and C_CODE='".$_GET['Txtcusco']."'"; 
    }
    if($_GET['brand'] !="ALL"){
         $sql.=" and Brand='".$_GET['brand']."'"; 
    }
    if($_GET['salesrep'] !="ALL"){
         $sql.=" and SAL_EX='".$_GET['salesrep']."'"; 
    }
     $sql.=" group by C_CODE";   
    //   echo $sql;
    foreach ($conn->query($sql) as $row) {
        
        $sql1 = "Select * from vendor where  CODE='".$row['C_CODE']."'  ";
        $result1 = $conn->query($sql1);
        $row1 = $result1->fetch();
         

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

        $sql3 = "Select sum(ST_PAID) as TOT from view_s_salma_s_sttr where  ST_FLAG='CAS'  and C_CODE='".$row['C_CODE']."'  and  CANCELL='0'  and SDATE >='" . $dt1 . "' and SDATE <='" .$dt2 . "' ";
        if($_GET['salesrep'] !="ALL"){
             $sql3.=" and SAL_EX='".$_GET['salesrep']."'"; 
        }
        $result3 = $conn->query($sql3); 
        $row3 = $result3->fetch();
        
        $sql4 = "Select sum(ST_PAID) as TOT from view_s_salma_s_sttr where  ST_FLAG='CHK'  and C_CODE='".$row['C_CODE']."'  and  CANCELL='0'  and SDATE >='" . $dt1 . "' and SDATE <='" .$dt2 . "' ";
        if($_GET['salesrep'] !="ALL"){
             $sql4.=" and SAL_EX='".$_GET['salesrep']."'"; 
        }
        $result4 = $conn->query($sql4); 
        $row4 = $result4->fetch();

        $sql5 = "Select sum(ST_PAID) as TOT from view_s_salma_s_sttr where  ST_FLAG='Cash TT'  and C_CODE='".$row['C_CODE']."'  and  CANCELL='0'  and SDATE >='" . $dt1 . "' and SDATE <='" .$dt2 . "' ";
        if($_GET['salesrep'] !="ALL"){
             $sql5.=" and SAL_EX='".$_GET['salesrep']."'"; 
        }
        $result5 = $conn->query($sql5); 
        $row5 = $result5->fetch();
        
        $sql6 = "Select sum(ST_PAID) as TOT from view_s_salma_s_sttr where  ST_FLAG='J/Entry'  and C_CODE='".$row['C_CODE']."'  and  CANCELL='0'  and SDATE >='" . $dt1 . "' and SDATE <='" .$dt2 . "' ";
        if($_GET['salesrep'] !="ALL"){
             $sql6.=" and SAL_EX='".$_GET['salesrep']."'"; 
        }
        $result6 = $conn->query($sql6); 
        $row6 = $result6->fetch();
        
        $sql7 = "Select sum(ST_PAID) as TOT from view_s_salma_s_sttr where  ST_FLAG='UT'  and C_CODE='".$row['C_CODE']."'  and  CANCELL='0'  and SDATE >='" . $dt1 . "' and SDATE <='" .$dt2 . "' ";
        if($_GET['salesrep'] !="ALL"){
             $sql7.=" and SAL_EX='".$_GET['salesrep']."'"; 
        }
        $result7 = $conn->query($sql7); 
        $row7 = $result7->fetch();
        
        $sql9 = "Select sum(GRAND_TOT-TOTPAY) as TOT from s_salma where  C_CODE='".$row['C_CODE']."' and  CANCELL='0'  and SDATE >='" . $dt1 . "' and SDATE <='" .$dt2 . "' ";
        if($_GET['salesrep'] !="ALL"){
             $sql9.=" and SAL_EX='".$_GET['salesrep']."'"; 
        }
        $result9 = $conn->query($sql9);
        $row9 = $result9->fetch();
        
        
        if($_GET['brand'] !="ALL"){
            $sql2.=" and Brand='".$_GET['brand']."'"; 
            $sql3.=" and Brand='".$_GET['brand']."'"; 
            $sql4.=" and Brand='".$_GET['brand']."'"; 
            $sql5.=" and Brand='".$_GET['brand']."'"; 
            $sql6.=" and Brand='".$_GET['brand']."'"; 
            $sql7.=" and Brand='".$_GET['brand']."'";  
            $sql9.=" and Brand='".$_GET['brand']."'";  
        }
        if($_GET['salesrep'] !="ALL"){
            $sql2.=" and SAL_EX='".$_GET['salesrep']."'"; 
            $sql3.=" and SAL_EX='".$_GET['salesrep']."'"; 
            $sql4.=" and SAL_EX='".$_GET['salesrep']."'"; 
            $sql5.=" and SAL_EX='".$_GET['salesrep']."'"; 
            $sql6.=" and SAL_EX='".$_GET['salesrep']."'"; 
            $sql7.=" and SAL_EX='".$_GET['salesrep']."'"; 
            $sql9.=" and SAL_EX='".$_GET['salesrep']."'"; 
        }
        $sql2.=" group by C_CODE"; 
        $sql3.=" group by C_CODE"; 
        $sql4.=" group by C_CODE"; 
        $sql5.=" group by C_CODE"; 
        $sql6.=" group by C_CODE"; 
        $sql7.=" group by C_CODE";  
        $sql9.=" group by C_CODE";  
        
        
        $totsettle=$row3['TOT']+$row4['TOT']+$row5['TOT']+$row6['TOT']+$row7['TOT'];
         
        // $sql8 = "SELECT sum(AMOUNT) as TOT from c_bal where CUSCODE='".$row['C_CODE']."' and   SDATE >='" . $dt1 . "' and SDATE <='" .$dt2 . "' and   trn_type='CNT'  ";  
        // $result8 = $conn->query($sql8); 
        // $row8 = $result8->fetch();  
         
        
        
        // $sql10 = "Select sum(GRAND_TOT-TOTPAY) as TOT from s_salma where CANCELL='0'   and C_CODE='".$row['C_CODE']."' and GRAND_TOT>TOTPAY and SDATE>='2014-01-01'  GROUP BY C_CODE"; 
        // if($_GET['salesrep'] !="ALL"){
        //      $sql10.=" and SAL_EX='".$_GET['salesrep']."'"; 
        // }
        // $result10 = $conn->query($sql10);
        // $row10 = $result10->fetch();
        
 

        // if($diff>1){
        //     $color="red";
        // }else{ 
        //     $color="";
        // }
//  bgcolor='".$color."''
        echo "<tr > ";
        echo "<td>" .  $row1['CODE'] . "</td>";  
        echo "<td>" .  $row1['NAME'] . "</td>";  
        echo "<td align='right'>" .  number_format($row2['TOT'],2) . "</td>"; 
        echo "<td align='right'>" .  number_format($row3['TOT'],2) . "</td>"; 
        echo "<td align='right'>" .  number_format($row4['TOT'],2) . "</td>"; 
        echo "<td align='right'>" .  number_format($row5['TOT'],2) . "</td>"; 
        echo "<td align='right'>" .  number_format($row6['TOT'],2) . "</td>"; 
        echo "<td align='right'>" .  number_format($row7['TOT'],2) . "</td>"; 
        echo "<td align='right'>" .  number_format($totsettle,2) . "</td>";  
        // echo "<td align='right'>" .  number_format($row8['TOT'],2) . "</td>"; 
        echo "<td align='right'>" .  number_format($row9['TOT'],2) . "</td>"; 
        // echo "<td align='right'>" .  number_format($row10['TOT'],2) . "</td>"; 
        echo "</tr>";
           
         $tot2=$tot2+$row2['TOT'] ;
         $tot3=$tot3+$row3['TOT'] ;
         $tot4=$tot4+$row4['TOT'] ; 
         $tot5=$tot5+$row5['TOT'] ;
         $tot6=$tot6+$row6['TOT'] ;
         $tot7=$tot7+$row7['TOT'] ;
         $gtotsettle=$gtotsettle+$totsettle;
        //  $tot8=$tot8+$row8['TOT'] ; 
         $tot9=$tot9+$row9['TOT'] ; 
        //  $tot10=$tot10+$row10['TOT'] ; 
    }

            echo "<tr style='font-size:18px;'>";
            echo "<td></td>";  
             echo "<td></td>";  
            echo "<td align='right'><b>" . number_format($tot2,2). "</b></td>";
            echo "<td align='right'><b>" . number_format($tot3,2). "</b></td>";
            echo "<td align='right'><b>" . number_format($tot4,2). "</b></td>";
            echo "<td align='right'><b>" . number_format($tot5,2). "</b></td>";
            echo "<td align='right'><b>" . number_format($tot6,2). "</b></td>";
            echo "<td align='right'><b>" . number_format($tot7,2). "</b></td>"; 
            echo "<td align='right'><b>" . number_format($gtotsettle,2). "</b></td>"; 
            // echo "<td align='right'><b>" . number_format($tot8,2). "</b></td>"; 
            echo "<td align='right'><b>" . number_format($tot9,2). "</b></td>"; 
            // echo "<td align='right'><b>" . number_format($tot10,2). "</b></td>"; 
            echo "</tr>"; 

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