<?php
 
 include('db_con.php');
 include('function.php');
 include('default_settings.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);
$dated = date('Y-m-d');
$cur_date = date('d-m-Y h:i:s A');
 
$area = '';
 $sm_id = $_GET['sm_id'];
 $city = $_GET['city'];
 $area = $_GET['area'];
 $count = 0;
 $balance = 0;
 $tot_debit = 0;
 $tot_credit = 0;
 $tot_recovery = 0;
  $tot_first_sale = 0;
  $tot_second_sale =0;
  $tot_third_sale = 0;
  $tot_fourth_sale =0;
  $tot_first_pay = 0;
  $tot_second_pay =0;
  $tot_third_pay = 0;
  $tot_fourth_pay =0;
  $tot_balance =0;
  $sm_name = 'ALL';
  if($sm_id > 0)
  {
    $sql = "select sname from sales_man where sm_id = '".$sm_id."'";
    $statement = $connect->prepare($sql);
    $statement->execute();
    $sm_name = $statement->fetchcolumn();
  }

 $first_month_fdate = date('Y-m-1', strtotime('-3 months'));
 $first_month_name = date('Y-M', strtotime('-3 months'));
  
 
//Last date of current month.
$first_month_tdate = date("Y-m-t", strtotime($first_month_fdate));
 
$second_month_fdate = date('Y-m-1', strtotime('-2 months'));
$second_month_name = date('Y-M', strtotime('-2 months'));  
 
//Last date of current month.
$second_month_tdate = date("Y-m-t", strtotime($second_month_fdate));
$third_month_fdate = date('Y-m-1', strtotime('-1 months'));
  $third_month_name = date('Y-M', strtotime('-1 months'));
 
//Last date of current month.
$third_month_tdate = date("Y-m-t", strtotime($third_month_fdate));
          
$fourth_month_fdate = date('Y-m-1');
$fourth_month_name = date('Y-M');
  
 
//Last date of current month.
$fourth_month_tdate = date("Y-m-t", strtotime($fourth_month_fdate));

 $sql = "select z.name,z.phone,z.first_sale,z.first_pay,z.second_sale,z.second_pay,z.third_sale, z.third_pay,z.fourth_sale,z.fourth_pay,z.cur_balance from (
 select b.name,b.phone, sum(b.first_sale) first_sale, sum(b.first_pay) first_pay, sum(b.second_sale) second_sale, sum(b.second_pay) second_pay,sum(b.third_sale) third_sale, sum(b.third_pay) third_pay,sum(b.fourth_sale) fourth_sale, sum(b.fourth_pay) fourth_pay, b.cur_balance from (
select a.acc_name name,a.phone,0 first_sale,0 first_pay, 0 second_sale,0 second_pay, 0 third_sale, 0 third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a where a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,sum(d.debit) first_sale,0 first_pay, 0 second_sale,0 second_pay, 0 third_sale, 0 third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR Inv' and m.dated between '".$first_month_fdate."' and '".$first_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, sum(d.debit) second_sale,0 second_pay, 0 third_sale, 0 third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR Inv' and m.dated between '".$second_month_fdate."' and '".$second_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, 0 second_sale,0 second_pay, sum(d.debit) third_sale, 0 third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR Inv' and m.dated between '".$third_month_fdate."' and '".$third_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, 0 second_sale,0 second_pay, 0 third_sale, 0 third_pay, sum(d.debit) fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR Inv' and m.dated between '".$fourth_month_fdate."' and '".$fourth_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no

union all
select a.acc_name name,a.phone,sum(d.credit) * -1 first_sale,0 first_pay, 0 second_sale,0 second_pay, 0 third_sale, 0 third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR InvR' and m.dated between '".$first_month_fdate."' and '".$first_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, sum(d.credit) * -1 second_sale,0 second_pay, 0 third_sale, 0 third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR InvR' and m.dated between '".$second_month_fdate."' and '".$second_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, 0 second_sale,0 second_pay, sum(d.credit) * -1 third_sale, 0 third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR InvR' and m.dated between '".$third_month_fdate."' and '".$third_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, 0 second_sale,0 second_pay, 0 third_sale, 0 third_pay, sum(d.credit) * -1 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR InvR' and m.dated between '".$fourth_month_fdate."' and '".$fourth_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no

union all
select a.acc_name name,a.phone,0  first_sale,sum(d.credit) first_pay, 0 second_sale,0 second_pay, 0 third_sale, 0 third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR Pay' and m.dated between '".$first_month_fdate."' and '".$first_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, 0 second_sale,sum(d.credit) second_pay, 0 third_sale, 0 third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR Pay' and m.dated between '".$second_month_fdate."' and '".$second_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, 0 second_sale,0 second_pay,0 third_sale, sum(d.credit) third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR Pay' and m.dated between '".$third_month_fdate."' and '".$third_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, 0 second_sale,0 second_pay, 0 third_sale, 0 third_pay, 0 fourth_sale, sum(d.credit) fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'AR Pay' and m.dated between '".$fourth_month_fdate."' and '".$fourth_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0  first_sale,sum(d.credit) first_pay, 0 second_sale,0 second_pay, 0 third_sale, 0 third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'GL jv' and m.dated between '".$first_month_fdate."' and '".$first_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, 0 second_sale,sum(d.credit) second_pay, 0 third_sale, 0 third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'GL jv' and m.dated between '".$second_month_fdate."' and '".$second_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, 0 second_sale,0 second_pay,0 third_sale, sum(d.credit) third_pay, 0 fourth_sale, 0 fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'GL jv' and m.dated between '".$third_month_fdate."' and '".$third_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no
union all
select a.acc_name name,a.phone,0 first_sale,0 first_pay, 0 second_sale,0 second_pay, 0 third_sale, 0 third_pay, 0 fourth_sale, sum(d.credit) fourth_pay,a.balance cur_balance from accounts a, jv_detail d, jv m  where m.jv_id=d.jv_id and d.acc_no=a.acc_no and m.source = 'GL jv' and m.dated between '".$fourth_month_fdate."' and '".$fourth_month_tdate."' and a.city LIKE '%".$city."%' and a.area LIKE '%".$area."%' and a.sm_id LIKE '%".$sm_id."%' and a.acc_type='DEBTOR' and a.status = 'Active' group by a.acc_no

)b GROUP by name
)z
  ";

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

?>
<!DOCTYPE html>
<html>
<head>
  <title>Sales and Recovery Report</title>
  <style>
    * {
      font-family: arial;
      font-size: 12px;
      font-weight: bold;
      padding: 3px;
    }
table thead td {
  font-size.: 13px;
  border-bottom: 2px solid black;
  border-top: : 2px solid black;
}
table tbody td {
  font-sia.: 12px;
 border-bottom: 1px solid gray;
  border-top: : 1px solid gray; 
}
.rightside {
  text-align: right;
}
}
@page {margin:0;}
@page {margin-bottom: :0.1cm;}
.head_row {
  font-weight: bold;
}
a {text-decoration: none;}

.small-font {
  font-size: 11px;
}
</style>
</head>
<body>
  <table width="100%" class="head_row">
     <tr>
  <td colspan="2" style="text-align: center;font-size: 14px"><b>Sales and Recovery Report</b></td>
  </tr>
  <tr><td colspan="2"><?php echo 'Sales Officer: '.$sm_name .' |  City : '.$city . ' | Area : '.$area;?> </td></tr>
     
  </table>
<table width="100%">
<thead class="head_row">
  
  
 
 
  
  
  <tr>
  <td width="2%">#</td>
  
  <td width="25%">Name</td>
  <td width="5%">Contact</td> 
   
  <td width="6%" class="rightside small-font">Sale <?php echo $first_month_name;?></td>
  <td width="6%" class="rightside small-font">Pay <?php echo $first_month_name;?></td>
  <td width="6%" class="rightside small-font">Sale <?php echo $second_month_name;?></td>
  <td width="6%" class="rightside small-font">Pay <?php echo $second_month_name;?></td>
  <td width="6%" class="rightside small-font">Sale <?php echo $third_month_name;?></td>
  <td width="6%" class="rightside small-font">Pay <?php echo $third_month_name;?></td>
  <td width="6%" class="rightside small-font">Sale <?php echo $fourth_month_name;?></td>
  <td width="6%" class="rightside small-font">Pay <?php echo $fourth_month_name;?></td>
  <td width="6%" class="rightside small-font">Balance</td>
  <td width="6%" class="rightside small-font">Recovery</td>
  
  
  </tr>
</thead>
<tbody>
  

<?php
$tot_first_sale =0;
$tot_first_pay =0;
$tot_second_sale =0;
$tot_second_pay =0;
foreach ($result as $row) {
$balance = $row['cur_balance'];
$tot_balance = $tot_balance + $balance ;
$first_sale = $row["first_sale"];
$first_pay = $row["first_pay"];
$second_sale = $row["second_sale"];
$second_pay = $row["second_pay"];
$tot_first_sale =$tot_first_sale + $first_sale;
$tot_first_pay =$tot_first_pay + $first_pay;
$tot_second_sale =$tot_second_sale + $second_sale;
$tot_second_pay =$tot_second_pay + $second_pay;
$third_sale = $row["third_sale"];
$third_pay = $row["third_pay"];
$fourth_sale = $row["fourth_sale"];
$fourth_pay = $row["fourth_pay"];
$tot_third_sale =$tot_third_sale + $third_sale;
$tot_third_pay =$tot_third_pay + $third_pay;
$tot_fourth_sale =$tot_fourth_sale + $fourth_sale;
$tot_fourth_pay =$tot_fourth_pay + $fourth_pay;
$count++;
echo '
<tr>
  <td >'.$count.'</td>
  
  <td >'.$row["name"].'</td> 
  <td >'.$row["phone"].'</td> 
  <td class="rightside">'.number_format($row["first_sale"],0).'</td>
  <td class="rightside">'.number_format($row["first_pay"],0).'</td>
  <td class="rightside">'.number_format($row["second_sale"],0).'</td>
  <td class="rightside">'.number_format($row["second_pay"],0).'</td>
  <td class="rightside">'.number_format($row["third_sale"],0).'</td>
  <td class="rightside">'.number_format($row["third_pay"],0).'</td>
  <td class="rightside">'.number_format($row["fourth_sale"],0).'</td>
  <td class="rightside">'.number_format($row["fourth_pay"],0).'</td>
  <td class="rightside">'.number_format($balance,0).'</td>
  <td class="rightside"></td>
  
  
  </tr>
';
  
}
echo '<tr>
  
  
  <td colspan="3"  class="rightside">Total :</td> 
   
  <td class="rightside">'.number_format($tot_first_sale,0).'</td>
  <td class="rightside">'.number_format($tot_first_pay,0).'</td>
  <td class="rightside">'.number_format($tot_second_sale,0).'</td>
  <td class="rightside">'.number_format($tot_second_pay,0).'</td>
  <td class="rightside">'.number_format($tot_third_sale,0).'</td>
  <td class="rightside">'.number_format($tot_third_pay,0).'</td>
  <td class="rightside">'.number_format($tot_fourth_sale,0).'</td>
  <td class="rightside">'.number_format($tot_fourth_pay,0).'</td>
  <td class="rightside">'.number_format($tot_balance,0).'</td>
  <td class="rightside"></td>
  
  
  </tr>';
  echo '<tr>
  
  
  <td colspan="13" width="100%">Printed at: '.$cur_date.'</td> 
   
  
  
  </tr>';
?>
</tbody>

  
</table>
</body>
 </html>