
<!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: 1000px;
           border: 1px solid black;


           border-collapse: collapse;
       }
        
       .tb td  {

           border-top: 1px solid black;
           border-left: 1px solid black;
           font-size:14px;
           line-height:20px;

           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>
<script language="javascript" type="text/javascript">
        <!--
            /****************************************************
             Author: Eric King
             Url: http://redrival.com/eak/index.shtml
             This script is free to use as long as this info is left in
             Featured on Dynamic Drive script library (http://www.dynamicdrive.com)
             ****************************************************/
             var win = null;
             function NewWindow(mypage, myname, w, h, scroll, pos) {
                if (pos == "random") {
                    LeftPosition = (screen.width) ? Math.floor(Math.random() * (screen.width - w)) : 100;
                    TopPosition = (screen.height) ? Math.floor(Math.random() * ((screen.height - h) - 75)) : 100;
                }
                if (pos == "center") {
                    LeftPosition = (screen.width) ? (screen.width - w) / 2 : 100;
                    TopPosition = (screen.height) ? (screen.height - h) / 2 : 100;
                } else if ((pos != "center" && pos != "random") || pos == null) {
                    LeftPosition = 0;
                    TopPosition = 20
                }
                settings = 'width=' + w + ',height=' + h + ',top=' + TopPosition + ',left=' + LeftPosition + ',scrollbars=' + scroll + ',location=no,directories=no,status=no,menubar=no,toolbar=no,resizable=no';
                win = window.open(mypage, myname, settings);
            }
            // -->
        </script>
</head>
<?php
require_once ("connection_sql.php");

 

$sql_invpara = "SELECT * from invpara where flag='".$_SESSION['company']."'";  
$result_invpara = $conn->query($sql_invpara);
$row_invpara = $result_invpara->fetch(); 



?>
<body>
    <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'> <?php echo $_GET['type']?> Outstanding Report - (<?php echo date('Y-m-d H:i:s');?>)</th> 
        </tr>
         
        <tr> 
        <?php 
         if($_GET['rep'] !="ALL"){
                $sql_rep = "SELECT * from s_salrep where REPCODE='".$_GET['rep']."'";  
                $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> 
        <?php 
         if($_GET['dtfrom'] !=""){?>
            <th class='center' colspan='5'>Date  - (<?php echo $_GET['dtfrom']?> ) </th> 
           <?php } ?>
        </tr> 
          <tr> 
        <?php 
         if($_GET['txtcusname'] !=""){?>
            <th class='center' colspan='5'>Customer  - (<?php echo $_GET['txtcusname']?> ) </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']=="Detail"){
             
        $sql = "delete from tmpoutinv ";
        $result = $conn->query($sql);

		$sql_inv= "Select C_CODE, NAME, REF_NO, SDATE, GRAND_TOT, SAL_EX, sum(ST_PAID) as PAID from  view_sttr_salma1 where TRN_TYPE='INV'   AND  SDATE <= '" . $_GET["dtfrom"] . "' and ST_DATE <= '" . $_GET["dtfrom"] . "' and DEV='0'  ";
		if ($_GET["rep"] != "ALL") {
            $sql_inv .=" and SAL_EX='" . $_GET["rep"] . "'";
        }
         if ($_GET["Txtcusco"] != "") {
            $sql_inv .=" and c_code1='" . $_GET["Txtcusco"] . "'";
        }
         if ($_GET["company"] != "ALL") {
               $sql_inv .=" and company='" . $_GET["company"]  . "'";
            }
        $sql_inv .="   group by C_CODE, NAME, REF_NO, SDATE, GRAND_TOT order by C_CODE "; 
        foreach ($conn->query($sql_inv) as $row_inv) {
		 
			
			$sql_tmpinv = "insert into tmpoutinv(REF_NO, Customer, Cus_Code, AMOUNT, paid, SDATE, sal_ex,odr) values 
            ('" . $row_inv["REF_NO"] . "', '" . $row_inv["NAME"] . "', '" . $row_inv["C_CODE"] . "', '" . $row_inv["GRAND_TOT"] . "','" . $row_inv["PAID"] . "', '" . $row_inv["SDATE"] . "', '" . $row_inv["SAL_EX"] . "','0')";
           $result_tmpinv = $conn->query($sql_tmpinv);
			
		}
		
		$sql_inv= "Select C_CODE, NAME, REF_NO, SDATE, GRAND_TOT, SAL_EX, sum(ST_PAID) as PAID from  view_sttr_salma1 where TRN_TYPE='INV'   AND  SDATE <= '" . $_GET["dtfrom"] . "' and ST_DATE > '" . $_GET["dtfrom"] . "'   and DEV='0' ";
		if ($_GET["rep"] != "ALL") {
            $sql_inv .=" and SAL_EX='" . $_GET["rep"] . "'";
        }
         if ($_GET["Txtcusco"] != "") {
            $sql_inv .=" and c_code1='" . $_GET["Txtcusco"] . "'";
        }
        if ($_GET["company"] != "ALL") {
               $sql_inv .=" and company='" . $_GET["company"]  . "'";
            }
        $sql_inv .="group by C_CODE, NAME, REF_NO, SDATE, GRAND_TOT order by C_CODE";
	    foreach ($conn->query($sql_inv) as $row_inv) {
 
				$sql_tmpinv = "insert into tmpoutinv(REF_NO, Customer, Cus_Code, AMOUNT, paid, SDATE, sal_ex,odr) values 
            ('" . $row_inv["REF_NO"] . "', '" . $row_inv["NAME"] . "', '" . $row_inv["C_CODE"] . "', '" . $row_inv["GRAND_TOT"] . "','0', '" . $row_inv["SDATE"] . "', '" . $row_inv["SAL_EX"] . "','0')";
           $result_tmpinv = $conn->query($sql_tmpinv);
			
		}
		
		
		$sql_inv= "Select C_CODE, CUS_NAME, REF_NO, SDATE, SAL_EX, GRAND_TOT from s_salma where TRN_TYPE='INV'   AND SDATE <= '" . $_GET["dtfrom"] . "' and CANCELL = '0' and TOTPAY = '0' and GRAND_TOT > '1' AND DEV = '0' ";
	    if ($_GET["rep"] != "ALL") {
            $sql_inv .=" and SAL_EX='" . $_GET["rep"] . "'";
        }
         if ($_GET["Txtcusco"] != "") {
            $sql_inv .=" and c_code1='" . $_GET["Txtcusco"] . "'";
        }
         if ($_GET["company"] != "ALL") {
               $sql_inv .=" and company='" . $_GET["company"]  . "'";
            }
            foreach ($conn->query($sql_inv) as $row_inv) {
			
			$rst_cus="select * from vendor where CODE='".$row["Cus_Code"]."'";
				$result_cus = $conn->query($rst_cus);
                $row_cus = $result_cus->fetch();
			
				$sql_tmpinv = "insert into tmpoutinv(REF_NO, Customer, Cus_Code, AMOUNT, paid, SDATE, sal_ex,odr) values 
            ('" . $row_inv["REF_NO"] . "', '" . $row_cus["NAME"] . "', '" . $row_inv["C_CODE"] . "', '" . $row_inv["GRAND_TOT"] . "','0', '" . $row_inv["SDATE"] . "', '" . $row_inv["SAL_EX"] . "','0')";
           $result_tmpinv = $conn->query($sql_tmpinv);
			 
			
		}
		
		
		

            // $sql_inv = "Select c_code1,  REF_NO, SDATE, GRAND_TOT, SAL_EX, sum(ST_PAID) as PAID from  view_salma_sttr_vendor where TRN_TYPE='INV' AND CANCELL='0' AND   SDATE <= '" . $_GET["dtfrom"] . "' and (ST_DATE <= '" . $_GET["dtfrom"] . "' OR ST_DATE  is null  OR ST_DATE  = '')   and DEV='0'";
            // if ($_GET["rep"] != "ALL") {
            //     $sql_inv .=" and SAL_EX='" . $_GET["rep"] . "'";
            // }
            //  if ($_GET["Txtcusco"] != "") {
            //     $sql_inv .=" and c_code1='" . $_GET["Txtcusco"] . "'";
            // }
            // $sql_inv .=" and company='" . $_SESSION['company'] . "'";
            // $sql_inv .="  group by C_CODE,  REF_NO, SDATE, GRAND_TOT order by c_code1  ";  
            // foreach ($conn->query($sql_inv) as $row_inv) {
            //     $sql_tmpinv = "insert into tmpoutinv(REF_NO, Customer, Cus_Code, AMOUNT, paid, SDATE, sal_ex,odr) values 
            //     ('" . $row_inv["REF_NO"] . "', '" . $row_inv["NAME"] . "', '" . $row_inv["c_code1"] . "', '" . $row_inv["GRAND_TOT"] . "','" . $row_inv["PAID"] . "', '" . $row_inv["SDATE"] . "', '" . $row_inv["SAL_EX"] . "','0')";
            //   $result_tmpinv = $conn->query($sql_tmpinv);
            // }
            
            
             
            // $sql_cheq = "SELECT * from view_scheq_chsttr_vendor where  CR_FLAG='0' and  CR_CHEVAL-PAID>1 and CR_DATE <= '" . $_GET["dtfrom"] . "'";
            // if ($_GET["rep"] != "ALL") {
            //     $sql_cheq .= " and S_REF='" . $_GET["rep"] . "'";
            // }
            // if ($_GET["Txtcusco"] != "") {
            //     $sql_cheq .= " and CR_C_CODE='" . $_GET["Txtcusco"] . "'";
            // }

            // $sql_cheq .=" and company='" . $_SESSION['company'] . "'"; 
            // $sql_cheq .="  group by CR_C_CODE,  CR_REFNO, CR_DATE, CR_CHEVAL order by CR_C_CODE  ";  
          
            // foreach ($conn->query($sql_cheq) as $row_cheq) {
            //     $sql_tmpinv = "insert into tmpoutinv(REF_NO, Customer, Cus_Code, AMOUNT, paid, SDATE, sal_ex,odr) values 
            //     ('" . ($row_cheq["CR_REFNO"]." - ".$row_cheq["CR_CHNO"]) . "', '" . $row_cheq["CR_C_NAME"] . "', '" . $row_cheq["CR_C_CODE"] . "', '" . $row_cheq["CR_CHEVAL"] . "','" . $row_cheq["PAID"] . "', '" . $row_cheq["CR_DATE"] . "', '" . $row_cheq["S_REF"] . "','1')";
            //   $result_tmpinv = $conn->query($sql_tmpinv);
            // }
            
            
    	$sql_cheq= "Select CR_REFNO, CR_CHNO,CR_C_NAME,CR_C_CODE,CR_CHEVAL,CR_DATE,S_REF, sum(ST_PAID) as PAID from  view_scheq_chsttr_vendor where CR_FLAG='0' and  CR_DATE <= '" . $_GET["dtfrom"] . "' and ST_DATE <= '" . $_GET["dtfrom"] . "'    ";
		if ($_GET["rep"] != "ALL") {
            $sql_cheq .= " and S_REF='" . $_GET["rep"] . "'";
        }
        if ($_GET["Txtcusco"] != "") {
            $sql_cheq .= " and CR_C_CODE='" . $_GET["Txtcusco"] . "'";
        }
        
         if ($_GET["company"] != "ALL") {
               $sql_cheq .=" and company='" . $_GET["company"]  . "'";
            }
 
        $sql_cheq .=" group by CR_C_CODE, CR_C_NAME, CR_REFNO, CR_DATE, CR_CHEVAL order by CR_C_CODE ";  
        foreach ($conn->query($sql_cheq) as $row_cheq) {
            $sql_tmpinv = "insert into tmpoutinv(REF_NO, Customer, Cus_Code, AMOUNT, paid, SDATE, sal_ex,odr) values 
            ('" . ($row_cheq["CR_REFNO"]." - ".$row_cheq["CR_CHNO"]) . "', '" . $row_cheq["CR_C_NAME"] . "', '" . $row_cheq["CR_C_CODE"] . "', '" . $row_cheq["CR_CHEVAL"] . "','" . $row_cheq["PAID"] . "', '" . $row_cheq["CR_DATE"] . "', '" . $row_cheq["S_REF"] . "','1')";
           $result_tmpinv = $conn->query($sql_tmpinv);
        }
        
        $sql_cheq= "Select CR_REFNO, CR_CHNO,CR_C_NAME,CR_C_CODE,CR_CHEVAL,CR_DATE,S_REF, sum(ST_PAID) as PAID from  view_scheq_chsttr_vendor where CR_FLAG='0' and  CR_DATE <= '" . $_GET["dtfrom"] . "' and ST_DATE > '" . $_GET["dtfrom"] . "'    ";
		if ($_GET["rep"] != "ALL") {
            $sql_cheq .= " and S_REF='" . $_GET["rep"] . "'";
        }
        if ($_GET["Txtcusco"] != "") {
            $sql_cheq .= " and CR_C_CODE='" . $_GET["Txtcusco"] . "'";
        }
        
         if ($_GET["company"] != "ALL") {
               $sql_cheq .=" and company='" . $_GET["company"]  . "'";
            }
 
        $sql_cheq .="group by CR_C_CODE, CR_C_NAME, CR_REFNO, CR_DATE, CR_CHEVAL order by CR_C_CODE "; 
        foreach ($conn->query($sql_cheq) as $row_cheq) {
            $sql_tmpinv = "insert into tmpoutinv(REF_NO, Customer, Cus_Code, AMOUNT, paid, SDATE, sal_ex,odr) values 
            ('" . ($row_cheq["CR_REFNO"]." - ".$row_cheq["CR_CHNO"]) . "', '" . $row_cheq["CR_C_NAME"] . "', '" . $row_cheq["CR_C_CODE"] . "', '" . $row_cheq["CR_CHEVAL"] . "','0', '" . $row_cheq["CR_DATE"] . "', '" . $row_cheq["S_REF"] . "','1')";
           $result_tmpinv = $conn->query($sql_tmpinv);
        }
        
        $sql_cheq= "Select * from  s_cheq where  CR_DATE <= '" . $_GET["dtfrom"] . "' and CR_FLAG = '0' and PAID = '0' and CR_CHEVAL > '1'   ";
		if ($_GET["rep"] != "ALL") {
            $sql_cheq .= " and S_REF='" . $_GET["rep"] . "'";
        }
        if ($_GET["Txtcusco"] != "") {
            $sql_cheq .= " and CR_C_CODE='" . $_GET["Txtcusco"] . "'";
        }

         if ($_GET["company"] != "ALL") {
               $sql_cheq .=" and company='" . $_GET["company"]  . "'";
            }
        foreach ($conn->query($sql_cheq) as $row_cheq) {
            $sql_tmpinv = "insert into tmpoutinv(REF_NO, Customer, Cus_Code, AMOUNT, paid, SDATE, sal_ex,odr) values 
            ('" . ($row_cheq["CR_REFNO"]." - ".$row_cheq["CR_CHNO"]) . "', '" . $row_cheq["CR_C_NAME"] . "', '" . $row_cheq["CR_C_CODE"] . "', '" . $row_cheq["CR_CHEVAL"] . "','0', '" . $row_cheq["CR_DATE"] . "', '" . $row_cheq["S_REF"] . "','1')";
           $result_tmpinv = $conn->query($sql_tmpinv);
        }


 
         
        echo "<table class='tb' width='1000px;' >
           <tr > 
                <th>Rep No</th>
        		<th>Invoice Date</th>
        		<th>Delivery date</th>
        		<th>Due Date</th>
        		<th>Invoice No</th>
        		<th>Po No</th>
        		<th>Vehicle No</th>
        		<th>Days</th>
        		<th>D.Days</th>
        		<th>Grand Tot</th>
        		<th>Paid</th>
        		<th>Balance</th>
            </tr>";
        
        $ccode="";
		$ccode1="";
	 
		$totbal=0;
		$balance=0;
		$i=0;
// 		$sql="SELECT * from view_salma_vendor where Accname != 'NON STOCK' and GRAND_TOT - TOTPAY >0 and SAL_EX='1'and (SDATE<'2023-10-23'or SDATE='2023-10-23')and CANCELL='0' and DEV='0' order by C_CODE, REF_NO asc";
	    $sql="SELECT sum(paid) as paid,AMOUNT,Cus_Code,odr,REF_NO,sal_ex,SDATE from tmpoutinv where  AMOUNT-paid >1 group by REF_NO ORDER by Cus_Code,sdate,odr ";
		foreach ($conn->query($sql) as $row) {
		    $type=$row["odr"];
		     if($type =='0'){
		         $color="";
		     }else{
		         $color="red";
		     }
		     
			if (strtoupper($row["Cus_Code"])!=strtoupper($ccode)) {
				if ($i!=0){
					echo "<tr>
					<td colspan=11 align=right> </td>
					<td colspan=1 align=right><b>".number_format($balance, 2, ".", ",")."</b></td>
					</tr>";
				} 
				$i=1;	
				
				$rst_cus="select * from vendor where CODE='".$row["Cus_Code"]."'";
				$result_cus = $conn->query($rst_cus);
                $row_cus = $result_cus->fetch();
 
				
				echo "<tr>
				
				<th colspan=12 align=left style='font-size:14px'><b>".$row["Cus_Code"]." ".$row_cus["NAME"]. "    -" .$row_cus["ADD1"]." -" .$row_cus["TELE1"]."</b></th>
				</tr>";
				$balance=$row["AMOUNT"]-$row["paid"];
			} else {
				$balance=$balance+$row["AMOUNT"]-$row["paid"];
			}
			
 
			
			$ccode=$row["Cus_Code"];
			$ccode1 =$row["Cus_Code"];
			
			$salma="select * from s_salma where REF_NO='".$row["REF_NO"]."'";
			$result_salma = $conn->query($salma);
            $row_salma = $result_salma->fetch();
			

			echo "<tr style='color:$color'>
			<td align=center>".$row["sal_ex"]."</td>
			<td align=center>".$row["SDATE"]."</td>";
			$sdate = $row['SDATE'];
			if (($row_salma["deli_date"]=="0000-00-00") or (is_null($row_salma["deli_date"])==true) or ($row_salma["deli_date"]=="")){
				echo "<td align=center><font color=\"#FF0000\"></font></td>";
			} else {
			    if($type=='0'){
			        echo "<td align=center><font color=\"#FF0000\">".$row_salma["deli_date"]."</font></td>";
				    $sdate = $row_salma["deli_date"];
			    }else{
			        echo "<td align=center><font color=\"#FF0000\">".$row["SDATE"]."</font></td>";
				    $sdate = $row["SDATE"];
			    }
				
			}	
			
		    if($type=='0'){
    			$per = $row_salma["cre_pe"];
    			$dtdue = date('Y-m-d', strtotime($sdate .  " +" . $per . " days"));	
    			echo "<td align=center>".$dtdue."</td>";	
                echo "<td><a onfocus=\"this.blur()\" onclick=\"NewWindow('../sales_inv_display.php?refno=" . trim($row["REF_NO"]) . "&amp;trn_type=INV','mywin','900','700','yes','center');return false\" class=\"INV\" href=\"\">" . trim($row["REF_NO"]) . "</a></td>";
                echo "<td>".$row_salma["ORD_NO"]."</td>";
                echo "<td>".$row_salma["veheno"]."</td>";
		    }else{
		        echo "<td></td>";
                echo "<td><a onfocus=\"this.blur()\" onclick=\"NewWindow('../sales_inv_display.php?refno=" . trim($row["REF_NO"]) . "&amp;trn_type=INV','mywin','900','700','yes','center');return false\" class=\"INV\" href=\"\">" . trim($row["REF_NO"]) . "</a> </td>";
                echo "<td></td>";
                echo "<td></td>";
		    }
			
		
			
			$date1 = date("Y-m-d");
			$date2 = $row["SDATE"];
			
			$diff = abs(strtotime($date2) - strtotime($date1));
			$days = floor($diff / (60*60*24));
			
			
			echo "<td align=\"right\">".$days."</td>";
			
			$date1 = date("Y-m-d");
			
			if($type=='0'){
    			$date2 = $row_salma["deli_date"];
    
    			$diff = abs(strtotime($date2) - strtotime($date1));
    			$days = floor($diff / (60*60*24));
    			
    			if (($row_salma["deli_date"]=="0000-00-00") or (is_null($row_salma["deli_date"])==true) or ($row_salma["deli_date"]=="")){
    				$days="";
    			}
    			echo "<td align=\"right\"><font color=\"#FF0000\">".$days."</font></td>";
			}else{
			    	echo "<td align=\"right\"><font color=\"#FF0000\">".$days."</font></td>";
			}
			
			echo "<td align=\"right\">".number_format($row["AMOUNT"], 2, ".", ",")."</td>
			<td align=\"right\">".number_format($row["paid"], 2, ".", ",")."</td>
				<td align=\"right\">".number_format(($row["AMOUNT"]-$row["paid"]), 2, ".", ",")."</td>";
			
			
			
			echo "</tr>";
			$totbal=$totbal+$row["AMOUNT"]-$row["paid"];
 
  
		}
		
		echo "<tr>
		<td colspan=11 align=right></td>
		<td colspan=11 align=right><b>".number_format($balance, 2, ".", ",")."</b></td>
		</tr>";
		echo "<tr>
		<td colspan=11></td><td align=right><b>".number_format($totbal, 2, ".", ",")."</b></td></tr>";
		echo "<table>";
       
        }
        ?>
   
                 
 
    
   
 
<!-- ************************************************************************** -->
</center>
</body>

</html>
