
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<?php

session_start();
date_default_timezone_set('Asia/Colombo');
?>
<html>
<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">


    <title><?php echo $_GET['type']?> REPORT</title>

    <style>
        .center {
            text-align: center;
        }

        .right {
            text-align: right;
        }

        .tb  {
           width: 1100px;
           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;
       }
       .tb2  {
           
           border: 1px solid black;


           border-collapse: collapse;
       }
       .tb2 td  {

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

           border-collapse: collapse;
       }
       .tb2 th  {

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


           border-collapse: collapse;
       }

       .tb1 {

           border: 1px solid #000000;
       }
 

   </style>

</head>
<?php
require_once ("connection_sql.php");

if ($_SESSION['company'] == "") { 
    exit("Please Login");
}
if ($_SESSION['CURRENT_USER'] == "") { 
    exit("Please Login");
}
 

$sql_para = "select * from invpara where  flag='".$_SESSION['company']."'"; 
$result_para = $conn->query($sql_para);
$row_para = $result_para->fetch(); 






?>
<body>
    <center>
    <table width="1100px;" cellspacing="0" border="0">

        <tr>
            <th class="center"  colspan=5 ><?php echo $row_para['COMPANY']; ?></th>
        </tr> 
        
        <tr> 
            <th class='center' colspan='5'> <?php echo $_GET['type']?> QTY SALES REPORT - (<?php echo date('Y-m-d H:i:s');?>)</th> 
        </tr>
         
        <tr> 
            <th class='center' colspan='5'> <?php echo $_GET['dtfrom']?> To - <?php echo $_GET['dtto']?></th> 
        </tr>
           <tr> 
        <?php 
         if($_GET['Txtcusco'] !=""){
            
?>
            <th class='center' colspan='5'>Customer Wise - (<?php echo $_GET['txtcusname']?> ) </th> 
           <?php } ?>
        </tr> 
        <tr> 
        <?php 
         if($_GET['salesrep'] !="ALL"){
            $sql_rep = "SELECT * from s_salrep where REPCODE='".$_GET['salesrep']."'";
            $result_rep= $conn->query($sql_rep);
            $row_rep = $result_rep->fetch(); 
?>
            <th class='center' colspan='5'>Rep Wise - (<?php echo $row_rep['Name']?> ) </th> 
           <?php } ?>
        </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>
<!--==================================================================================-->
   <?php if($_GET['type'] == "SUMMERY"){?>
                <table class="tb" width="1100px;" >
                <tr >
        
                   <th width="30px;">NO</th> 
                    <th width="100px;">COMPANY</th> 
                    <th width="120px;">GRAND TOTAL</th> 
                    <th width="100px;">PAID</th> 
                    <th width="100px;">BALANCE</th> 
                </tr>
                <?php
                $i=1;  
                $part="";
                $BAL=0;
                $sql1 = "Select sum(GRAND_TOT) AS GRAND_TOT,sum(TOTPAY) AS TOTPAY,sum(GRAND_TOT-TOTPAY) AS BAL,DEPARTMENT from s_salma  where CANCELL='0' and TRN_TYPE='INV' AND SDATE >='" . $_GET["dtfrom"] . "' and SDATE <='" . $_GET["dtto"] . "'";
                  
                if($_GET['company'] !=""){
                     $sql1.=" and DEPARTMENT='".$_GET['company']."'"; 
                }
                  if($_GET['Txtcusco'] !=""){
                     $sql1.=" and C_CODE='".$_GET['Txtcusco']."'"; 
                }
                
                $sql1.=" group by DEPARTMENT"; 
                
     
                $i=1;
                foreach ($conn->query($sql1) as $row1) {
                    $sql_sto1 = "SELECT * from s_stomas where CODE='".$row1['DEPARTMENT']."'";
                    $result_sto1= $conn->query($sql_sto1);
                    $row_sto1 = $result_sto1->fetch(); 
                    ?>
                    <tr style="font-size:13px;">
           
                        <td class="center" style='font-size:20px;'><?php echo $i ?></td>
                        <td class="left"  style='font-size:20px;'><?php echo $row_sto1['DESCRIPTION']; ?> </td> 
                        <td align="right" style='font-size:20px;'><?php echo number_format($row1['GRAND_TOT'], 2, ".", ","); ?></td> 
                        <td align="right" style='font-size:20px;'><?php echo number_format($row1['TOTPAY'], 2, ".", ","); ?></td> 
                        <td align="right" style='font-size:20px;'><?php echo number_format($row1['BAL'], 2, ".", ","); ?></td> 
                    </tr>
                    <?php
                     $i=$i+1;
                     $company=$row1['DEPARTMENT']; 
                     
                     $GTOT=$GTOT+$row1['GRAND_TOT'];
                     $TOTPAY=$TOTPAY+$row1['TOTPAY'];
                     $BAL=$BAL+$row1['BAL'] ;
                }
                ?>
           
           
            <tr> 
                <td  align="right" colspan='2' style='font-size:20px;'><b></b></td> 
                <td align="right" style='font-size:24px;'><b><?php   echo number_format($GTOT, 2, ".", ",");  ?><b/></td>
                <td align="right" style='font-size:24px;'><b><?php   echo number_format($TOTPAY, 2, ".", ",");  ?><b/></td>
                <td align="right" style='font-size:24px;'><b><?php   echo number_format($BAL, 2, ".", ",");  ?><b/></td>
            </tr>
            
            
        </table>
    
<?php }else{
 
        $insert = "delete from tmprepsale where user_id='" . $_SESSION["CURRENT_USER"] . "'";
        $result = $conn->query($insert);
        
        $sql_inv = "Select sum(Qty) as invqty,SAL_EX,MAS_BRAND,Cat1 from viewinv1 where   sdate>='" . $_GET['dtfrom'] . "' and sdate<='" . $_GET['dtto'] . "' and cancel_m = '0'  ";
        if($_GET['salesrep'] !="ALL"){
             $sql_inv.=" and SAL_EX='".$_GET['salesrep']."'"; 
        }
        if($_GET['Txtcusco'] !=""){
             $sql_inv.=" and c_code1='".$_GET['Txtcusco']."'"; 
        }
        if($_SESSION['company'] !="M"){
             $sql_inv.=" and company='".$_SESSION['company']."'"; 
        }
        
        $sql_inv.=" GROUP BY SAL_EX,MAS_BRAND,Cat1 ORDER BY SAL_EX,MAS_BRAND,Cat1"; 
                    
        foreach ($conn->query($sql_inv) as $row_inv) { 
                
            $sql_rep = "select Name from s_salrep where REPCODE='".$row_inv["SAL_EX"]."'  "; 
            $result_rep = $conn->query($sql_rep);
            $row_rep = $result_rep->fetch(); 
    
            $invoice[] = "('" . $row_inv["SAL_EX"] . "',
            '" . $row_rep["Name"] . "',
            '" . $row_inv["MAS_BRAND"] . "',
            '" . $row_inv["Cat1"] . "',
            '" . $row_inv["invqty"] . "',
            '0', 
            '" . $_SESSION["CURRENT_USER"] . "')";
            
        }
        if (isset($invoice)) {
            $insert = "insert into tmprepsale(rep, Name,brand,cat,invqty,rtnqty,user_id) values  
            " . implode($invoice, ","); 
             $result = $conn->query($insert);
        }
        
        // =======================================
        
        $sql_grn = "Select sum(Qty) as rtnqty,SAL_EX,Brand,Cat1 from viewcrntrn where   sdate>='" . $_GET['dtfrom'] . "' and sdate<='" . $_GET['dtto'] . "' and cancell = '0'  ";
        if($_GET['salesrep'] !="ALL"){
             $sql_grn.=" and SAL_EX='".$_GET['salesrep']."'"; 
        }
        if($_GET['Txtcusco'] !=""){
             $sql_grn.=" and C_CODE='".$_GET['Txtcusco']."'"; 
        }
        if($_SESSION['company'] !="M"){
             $sql_grn.=" and company='".$_SESSION['company']."'"; 
        }
        
        $sql_grn.=" GROUP BY SAL_EX,Brand,Cat1 ORDER BY SAL_EX,Brand,Cat1";  
               
        foreach ($conn->query($sql_grn) as $row_grn) { 
                
            $sql_rep = "select Name from s_salrep where REPCODE='".$row_grn["SAL_EX"]."'  "; 
            $result_rep = $conn->query($sql_rep);
            $row_rep = $result_rep->fetch(); 
    
            $return[] = "('" . $row_grn["SAL_EX"] . "',
            '" . $row_rep["Name"] . "',
            '" . $row_grn["Brand"] . "',
            '" . $row_grn["Cat1"] . "',
            '0', 
            '" . $row_grn["rtnqty"] . "',
            '" . $_SESSION["CURRENT_USER"] . "')";
            
        }
        if (isset($return)) {
            $insert1 = "insert into tmprepsale(rep, Name,brand,cat,invqty,rtnqty,user_id) values  
            " . implode($return, ","); 
             $result = $conn->query($insert1);
        }
        
        
        $sql3= "select target,brand,cat,rep from rep_targetbrand where  cancel='0' and month>='" . $_GET["dtfrom"] . "' and month<='" . $_GET["dtto"] . "'and type='QTY WISE'";
        foreach ($conn->query($sql3) as $row3) {
            $sql_rep = "SELECT * from s_salrep where REPCODE='".$row3['rep']."'";
            $result_rep= $conn->query($sql_rep);
            $row_rep = $result_rep->fetch(); 
            
            
            $sql_tarqty1= "Select * from  tmprepsale where user_id='" . $_SESSION["CURRENT_USER"] . "' and rep='" . $row3['rep'] . "' and brand='" . $row3['brand'] . "' and cat='" . $row3['cat'] . "'"; 
            $result_tarqty1 = $conn->query($sql_tarqty1);
            if (!$row_tarqty1 = $result_tarqty1->fetch()) {
                $insert1 = "insert into tmprepsale(rep, Name,brand,cat,invqty,rtnqty,user_id) values  
                ('" . $row3['rep'] . "', '" . $row_rep["Name"] . "','" . $row3["brand"] . "','" . $row3["cat"] . "',0,0,'" . $_SESSION["CURRENT_USER"] . "')";
                $result = $conn->query($insert1);
                
            }
             
            
        }
     
  
        $table .= "<table class='tb' width='1000px;' >
          <tr style='background-color:#d0e8ed'>  
                <th width=130px>Brand</th>
                <th width=90px>Target Qty</th>
                <th width=90px>Inv Qty</th>
                <th width=90px>Rtn Qty</th>
                <th width=90px>Actual Qty</th>
                <th width=90px>Actual Per(%)</th>
                <th width=90px>Bal Qty</th> 
            </tr>";
        
    
        $i=0;
        $J=0;
        $ii=1;
        $rep="";

        $grandtot=0;
        $grandtotx=0;
        $vat=0;
        $net=0;
        $netx=0;
        
        $sql= "Select sum(invqty) as invqty,sum(rtnqty) as rtnqty,cat,rep,Name,brand from  tmprepsale where user_id='" . $_SESSION["CURRENT_USER"] . "'   "; 
        if($_GET['salesrep'] !="ALL"){
             $sql.=" and rep='".$_GET['salesrep']."'"; 
        }
        $sql.=" group by rep,brand,cat order by rep,brand,cat"; 
        foreach ($conn->query($sql) as $row) {
            
            
            $text="";
            $color="";
            $grandtottarget=0;
            
            
            $sql_tarqty = "select target from rep_targetbrand where rep='" . $row['rep'] . "' and brand='" . $row['brand'] . "' and cat='" . $row['cat'] . "' and cancel='0' and month>='" . $_GET["dtfrom"] . "' and month<='" . $_GET["dtto"] . "'and type='QTY WISE' ";
            $result_tarqty = $conn->query($sql_tarqty);
            if ($row_tarqty = $result_tarqty->fetch()) {
                $grandtottarget=$row_tarqty["target"];
            }
            
            $grandtotx = $row["invqty"];
            $grandtot = $row["rtnqty"];
            $vat = $row["invqty"] - $row["rtnqty"];
            $net = ($row["invqty"] - $row["rtnqty"])/$grandtottarget*100;
            $netx=$grandtottarget-$row["invqty"] - $row["rtnqty"];
            
            
            
            if($rep!=$row['rep']){
              if($i !=0){
                    $table .= "<tr style='background-color:#eff9b5'>
                        <td colspan=1>&nbsp;</td>
                        <th  class='right'>" . number_format($sub_grandtottarget_cus, 2, ".", ",") . "</th>
                        <th  class='right'>" . number_format($sub_grandtot_cusx, 2, ".", ",") . "</th>
                        <th  class='right'>" . number_format($sub_grandtot_cus, 2, ".", ",") . "</th>
                        <th  class='right'>" . number_format($sub_vat_tot_cus, 2, ".", ",") . "</th>
                        <th  class='right'>" . number_format($sub_net_tot_cus, 2, ".", ",") . "</th>"; 
                        if($sub_net_tot_cusx>0){
                            $table .= "<td  align=\"right\"><b>".number_format($sub_net_tot_cusx, 2, ".", ",")."</b></td>";
                        }else{
                            $table .= "<td  align=\"right\" ></td>";
                        }
                    $table .= "</tr>"; 
                    
                    $table .= "<tr style='background-color:#cdebcd'>
                        <td colspan=1>&nbsp;</td>
                        <td  align=right><b>  ".number_format($sub_grandtottarget, 2, ".", ",") ."   </b></td>
                        <td  align=right><b>  ".number_format($sub_grandtotx, 2, ".", ",") ."   </b></td>
                        <td  align=right><b>  ".number_format($sub_grandtot, 2, ".", ",") ."   </b></td>
                        <td  align=right><b>  ". number_format($sub_vat_tot, 2, ".", ",") ."   </b></td>
                        <td  align=right><b>  ". number_format($sub_net_tot, 2, ".", ",") ."   </b></td>";
                         if($sub_net_totx>0){
                            $table .= "<td  align=\"right\"><b>".number_format($sub_net_totx, 2, ".", ",")."</b></td>";
                        }else{
                            $table .= "<td  align=\"right\" ></td>";
                        }
                    $table .= "</tr>";  
                    
              } 
                $i = 1;
                $J = 0;
                 
                $table .= "<tr style='background-color:#7bbfd9'><td colspan='11'><b>  ". $row["rep"] ." - ". $row["Name"] ."   </b></td> </tr>";
                $sub_grandtottarget =$grandtottarget;
                $sub_grandtotx =$grandtotx;
                $sub_grandtot =$grandtot;
                $sub_vat_tot =$vat;
                $sub_net_tot =$net;
                $sub_net_totx =$netx;
                
                
                
                if($brand!=$row['brand']){
                  if($J !=0){
                      $table .= "<tr style='background-color:#eff9b5'>
                            <td colspan='1'><b>   </b></td>
                            <td  align=right><b>  ".number_format($sub_grandtottarget_cus, 2, ".", ",") ."   </b></td>
                            <td  align=right><b>  ".number_format($sub_grandtot_cusx, 2, ".", ",") ."   </b></td>
                            <td  align=right><b>  ".number_format($sub_grandtot_cus, 2, ".", ",") ."   </b></td>
                            <td  align=right><b>  ". number_format($sub_vat_tot_cus, 2, ".", ",") ."   </b></td>
                            <td  align=right><b>  ". number_format($sub_net_tot_cus, 2, ".", ",") ."   </b></td>";
                            if($sub_net_tot_cusx>0){
                                $table .= "<td  align=\"right\"><b>".number_format($sub_net_tot_cusx, 2, ".", ",")."</b></td>";
                            }else{
                                $table .= "<td  align=\"right\" ></td>";
                            }
                        $table .= "</tr>"; 
                        
                  } 
                    $J = 1;
                    $ii=1;
                    $table .= "<tr style='background-color:#d7d9d0'><td colspan='11'><b> &nbsp;&nbsp;&nbsp; ". $row['brand'] ."   </b></td> </tr>";
                    $sub_grandtottarget_cus =$grandtottarget;
                    $sub_grandtot_cusx =$grandtotx;
                    $sub_grandtot_cus =$grandtot;
                    $sub_vat_tot_cus =$vat;
                    $sub_net_tot_cus =$net;
                    $sub_net_tot_cusx =$netx;
                     
                }else{
                    $sub_grandtottarget_cus +=$grandtottarget;
                    $sub_grandtot_cusx +=$grandtotx;
                    $sub_grandtot_cus +=$grandtot;
                    $sub_vat_tot_cus +=$vat;
                    $sub_net_tot_cus +=$net;
                    $sub_net_tot_cusx +=$netx;
                }
                 
            }else{
               
                $sub_grandtottarget +=$grandtottarget;
                $sub_grandtotx +=$grandtotx;
                $sub_grandtot +=$grandtot;
                $sub_vat_tot +=$vat;
                $sub_net_tot +=$net;
                $sub_net_totx +=$netx;
                
                if($brand!=$row['brand']){
                  if($J !=0){
                      $table .= "<tr style='background-color:#eff9b5'>
                            <td  align=right><b> </b></td>
                            <td  align=right><b>  ".number_format($sub_grandtottarget_cus, 2, ".", ",") ."   </b></td>
                            <td  align=right><b>  ".number_format($sub_grandtot_cusx, 2, ".", ",") ."   </b></td>
                            <td  align=right><b>  ".number_format($sub_grandtot_cus, 2, ".", ",") ."   </b></td>
                            <td  align=right><b>  ". number_format($sub_vat_tot_cus, 2, ".", ",") ."   </b></td>
                            <td  align=right><b>  ". number_format($sub_net_tot_cus, 2, ".", ",") ."   </b></td>";
                            if($sub_net_tot_cusx>0){
                                $table .= "<td  align=\"right\"><b>".number_format($sub_net_tot_cusx, 2, ".", ",")."</b></td>";
                            }else{
                                $table .= "<td  align=\"right\" ></td>";
                            }
                    $table .= "</tr>"; 
                        
                  } 
                    $J = 1;
                    $ii=1;
                    $table .= "<tr style='background-color:#d7d9d0'><td colspan='7'><b>&nbsp;&nbsp;&nbsp;  ". $row['brand'] ."   </b></td> </tr>";
                    $sub_grandtottarget_cus =$grandtottarget;
                    $sub_grandtot_cusx =$grandtotx;
                    $sub_grandtot_cus =$grandtot;
                    $sub_vat_tot_cus =$vat;
                    $sub_net_tot_cus =$net;
                    $sub_net_tot_cusx =$netx;
                     
                }else{
                    $sub_grandtottarget_cus +=$grandtottarget;
                    $sub_grandtot_cusx +=$grandtotx;
                    $sub_grandtot_cus +=$grandtot;
                    $sub_vat_tot_cus +=$vat;
                    $sub_net_tot_cus +=$net;
                    $sub_net_tot_cusx +=$netx;
                }
                
            }
            
                
            $table .= "<tr style='background-color:#eeefdd;text-decoration:$text;color:$color'>
                <td  align=left>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;".$row["cat"]."</td>
                <td  align=\"right\">".number_format($grandtottarget, 2, ".", ",")."</td> 
                <td  align=\"right\">".number_format($grandtotx, 2, ".", ",")."</td>
                <td  align=\"right\">".number_format($grandtot, 2, ".", ",")."</td>
                <td  align=\"right\">".number_format($vat, 2, ".", ",")."</td>
                <td  align=\"right\">".number_format($net, 2, ".", ",")."</td>";
                if($netx>0){
                    $table .= "<td  align=\"right\">".number_format($netx, 2, ".", ",")."</td>";
                }else{
                    $table .= "<td  align=\"right\" ></td>";
                }
            $table .= "</tr>";


            $rep= $row["rep"];
            $brand= $row["brand"]; 
            
            $main_grandtottarget +=$grandtottarget;
            $main_grandtotx +=$grandtotx;
            $main_grandtot +=$grandtot;
            $main_vat_tot +=$vat;
            $main_net_tot +=$net;
            $main_net_totx +=$netx;
            $ii+=1;
        }
     
    
        $table .= "<tr style='background-color:#eff9b5'>
            <td colspan=1>&nbsp;</td>
            <th  class='right'>" . number_format($sub_grandtottarget_cus, 2, ".", ",") . "</th> 
            <th  class='right'>" . number_format($sub_grandtot_cusx, 2, ".", ",") . "</th>
            <th  class='right'>" . number_format($sub_grandtot_cus, 2, ".", ",") . "</th>
            <th  class='right'>" . number_format($sub_vat_tot_cus, 2, ".", ",") . "</th>
            <th  class='right'>" . number_format($sub_net_tot_cus, 2, ".", ",") . "</th>";
            if($sub_net_tot_cusx>0){
                $table .= "<th  class='right'>" . number_format($sub_net_tot_cusx, 2, ".", ",") . "</th>";
            }else{
                $table .= "<th  class='right' ></th>";
            }
        $table .= "</tr>";
        
        $table .= "<tr style='background-color:#cdebcd'>
            <td colspan=1>&nbsp;</td>
            <th  class='right'>" . number_format($sub_grandtottarget, 2, ".", ",") . "</th>
            <th  class='right'>" . number_format($sub_grandtotx, 2, ".", ",") . "</th>
            <th  class='right'>" . number_format($sub_grandtot, 2, ".", ",") . "</th>
            <th  class='right'>" . number_format($sub_vat_tot, 2, ".", ",") . "</th>
            <th  class='right'>" . number_format($sub_net_tot, 2, ".", ",") . "</th>";
            
            if($sub_net_totx>0){
                $table .= "<th  class='right'>" . number_format($sub_net_totx, 2, ".", ",") . "</th> ";
            }else{
                $table .= "<th  class='right' ></th> ";
            }
        $table .= "</tr>";
        
        $table .= "<tr style='background-color:#99bb99'>
            <td colspan=1>&nbsp;</td>
            <th  class='right'>" . number_format($main_grandtottarget, 2, ".", ",") . "</th>
            <th  class='right'>" . number_format($main_grandtotx, 2, ".", ",") . "</th>
            <th  class='right'>" . number_format($main_grandtot, 2, ".", ",") . "</th>
            <th  class='right'>" . number_format($main_vat_tot, 2, ".", ",") . "</th>
            <th  class='right'>" . number_format($main_net_tot, 2, ".", ",") . "</th> ";
            
            if($main_net_totx>0){
                $table .= "<th  class='right'>" . number_format($main_net_totx, 2, ".", ",") . "</th>  ";
            }else{
                $table .= "<th  class='right' ></th> ";
            }
        $table .= "</tr>";
         
        $table .= "</table>";
   
        echo $table;
}
?>

 
  
      
    <!--===================================================================================-->
    
    
    
 
<!-- ************************************************************************** -->
</center>
</body>

</html>
