<?php
 include('db_con.php');
 include('function.php');
 
 
  if (isset($_SESSION['user_id'])) {
    $user_type = $_SESSION['user_type'];
    $user_id = $_SESSION['user_id'];
    $user_name = $_SESSION['user_name'];
    $branch_id = $_SESSION['branch_id'];
    $branch_name = $_SESSION['branch_name'];
    $store_id = $_SESSION['store_id'];
    $store_name = $_SESSION['store_name'];
}else{
  header('Location: login');
  die();
}
accounts_update($connect);


 $html = '';
 $fdate = $_GET['fdate'];
 
 $tdate = $_GET['tdate'];

 $sm_id = $_GET['sm_id'];

 $sql = "select sname from sales_man where sm_id = '".$sm_id."'";
 $statement = $connect->prepare($sql);
 $statement->execute();
 $sm_name = $statement->fetchColumn();

 $rpt_type = $_GET['rpt_type'];
 $tot_balance = 0;

 if($rpt_type==0)
 {
  $rpt_type_status = 'Payment';
 }else{$rpt_type_status='Sale';}
 
 $count = 0;
 $balance = 0;
 
 $dated = date('d-m-Y'); 
 $payment = 0;
 $sale = 0;

 if($rpt_type==0)
 {


     
          $sql = "select z.acc_no,z.acc_name,z.balance,z.sale,z.payment from(
    select y.acc_no,y.acc_name,sum(y.balance) balance,sum(sale) sale,sum(payment) payment from (
        select a.acc_no,a.acc_name,a.op_balance balance,0 sale, 0 payment from accounts a where a.acc_type='DEBTOR' and a.status = 'Active'
        union ALL
        select a.acc_no,a.acc_name,0 balance,sum(d.debit) sale ,0 payment from jv_detail d, jv m, accounts a where m.jv_id=d.jv_id and  a.acc_no=d.acc_no and m.source = 'AR Inv' and m.dated between '".$fdate."' and '".$tdate."' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
     union ALL
        select a.acc_no,a.acc_name,0 balance,0 sale,sum(d.credit) payment from jv_detail d, jv m, accounts a where m.jv_id=d.jv_id and  a.acc_no=d.acc_no and m.source = 'AR Pay' and m.dated between '".$fdate."' and '".$tdate."' and a.acc_type='DEBTOR' group by a.acc_no)y group by acc_no)z where balance > 0 and payment = 0
";
}
else
{
 $sql = "select z.acc_no,z.acc_name,z.balance,z.sale,z.payment from(
    select y.acc_no,y.acc_name,sum(y.balance) balance,sum(sale) sale,sum(payment) payment from (
        select a.acc_no,a.acc_name,a.op_balance balance,0 sale, 0 payment from accounts a where a.acc_type='DEBTOR' and a.status = 'Active'
        union ALL
        select a.acc_no,a.acc_name,0 balance,sum(d.debit) sale ,0 payment from jv_detail d, jv m, accounts a where m.jv_id=d.jv_id and  a.acc_no=d.acc_no and m.source = 'AR Inv' and m.dated between '".$fdate."' and '".$tdate."' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
     union ALL
        select a.acc_no,a.acc_name,0 balance,0 sale,sum(d.credit) payment from jv_detail d, jv m, accounts a where m.jv_id=d.jv_id and  a.acc_no=d.acc_no and m.source = 'AR Pay' and m.dated between '".$fdate."' and '".$tdate."' and a.acc_type='DEBTOR' group by a.acc_no)y group by acc_no)z where sale = 0
        "; 
}

            $statement = $connect->prepare($sql);
          
          
  
 $statement->execute();

 $result = $statement->fetchAll();
?>
<!DOCTYPE html>
<html>
<head>
   <title><?php echo basename(__FILE__, '.php'); ?></title>
  <style>
* {
  font-size: 12px;
  font-family: arial;
}
@media print {
  @page { margin: 0.5cm; }
  body { margin: 0.1cm;}
}
  .rightside {text-align: right;}
  table td{   
    border-bottom: 1px solid gray;
  }
  .head_row {
    font-weight: bold;
  }
  thead td {
    font-weight: bold;
     border-bottom: 1px solid gray;
      border-top: 1px solid gray;
       background-color: :  gray;
  }
  .total {
    font-weight: bold;
  }
  </style>
</head>
<body>
  <table width="100%">
<tr>
  <td colspan="3" width="100%"><?php echo '<b>Customer Status By : '.$rpt_type_status.'</b> | Printed at '.$dated .' | Sales Man : '.$sm_name;?></td>
  </tr>
  
  <tr>
  <td colspan="3" width="100%"><?php echo '<b>From : </b>'.$fdate.' <b>To : </b>'.$tdate;?></td>
  </tr>
  
  </table>
  <table width="100%" id="table_header">
<thead>
  <tr>
  <td width="5%">#</td>
  
  
  
  <td width="65%">Name</td>
  
  
  <td width="15%" class="rightside">Balance</td>
  <td width="15%" class="rightside">SalesTarget</td>
  </tr>
</thead>
  
<?php
foreach($result as $row)
 {
  $balance = $row["balance"];
  $acc_no = $row['acc_no'];
  $newacc_no = substr($acc_no, 3);

  $subsql = "select * from customers where cust_id = '".$newacc_no."'
        "; 


            $substatement = $connect->prepare($subsql);
          
          
  
 $substatement->execute();

 $subresult = $substatement->fetchAll();

 foreach ($subresult as $subrow) {
   # code...
 
  
if($subrow['sm_id']==$sm_id)
{

  if($rpt_type==0)
  {

    if($balance > 0)
    {



$sale_target = $subrow['sale_target'];

$count = $count+1;

echo '
<tr>
<td width="5%" class="dataline">'.$count.'</td>
  
<td width="65%" class="dataline">'.$row["acc_name"].' -- '.$subrow['phone'].'</td>

   <td width="15%" class="rightside dataline">'.number_format($balance, 2).'</td>
   <td width="15%" class="rightside dataline">'.number_format($sale_target, 2).'</td>
  
   
    
   </tr>
';
$tot_balance = $tot_balance + $balance;
}
}
}

}

}
?>
<tfoot>
<?php echo '
  <tr>
  <td colspan="2" width="85%" class="rightside total">Total: </td>
  
  <td width="15%" class="rightside total">'.number_format($tot_balance, 2).'</td>
  <td width="15%" class="rightside total"></td>
  </tr>
  
';
?>
</tfoot>
</table>
</body>
</html>
