<?php
 
 include('db_con.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();
}
 
$dated = date('Y-m-d');
$fdate = $_GET['fdate'];
 
 $tdate = $_GET['tdate'];
 $sm_id = $_GET['sm_id'];
 
 $count = 0;
 $balance = 0;
 $tot_debit = 0;
 $tot_credit = 0;
 $dated = date('Y-m-d');
   $tot_recovery = 0;
  
  $tot_first_month = 0;
  $tot_second_month =0;
  $tot_third_month = 0;
  $tot_forth_month = 0;
  $tot_short_target = 0;
  $tot_sale_target = 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));
          
$forth_month_fdate = date('Y-m-1');
$forth_month_name = date('Y-M');
  
 
//Last date of current month.
$forth_month_tdate = date("Y-m-t", strtotime($forth_month_fdate));
?>
<!DOCTYPE html>
<html>
<head>
  <title>Sales Target</title>
  <style>
    * {
      font-family: arial;
    }
table thead td {
  font-size: 13px;
  border-bottom: 1px solid gray;
  border-top: : 1px solid gray;
}
table tbody td {
  font-size: 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;
}
</style>
</head>
<body>
  <table width="100%" class="head_row">
     <tr>
  <td colspan="2" style="text-align: center;font-size: 12px"><b>Sales Target</b></td>
  </tr>
  <tr><td colspan="2">Printed at : <?php echo $dated .' -- Sales Officer: '.$sm_name .'--- Logged as : '.$user_name;?></td></tr>
       <tr>
      <td width="70%" class="rightside">Recovery From :
      </td>
      
      <td  width="30%" class="rightside"> <?php echo $fdate;?>
      </td>
     
    </tr>
      <tr>
      <td width="70%" class="rightside">To :
      </td>
      
      <td  width="30%" class="rightside"> <?php echo $tdate;?>
      </td>
     
    </tr>
  </table>
<table width="100%">
<thead class="head_row">
  
  
 
 
  
  
  <tr>
  <td width="2%">#</td>
  
  <td width="18%">Name</td> 
   
  <td width="10%" class="rightside"><?php echo $first_month_name;?></td>
  <td width="10%" class="rightside"><?php echo $second_month_name;?></td>
  <td width="10%" class="rightside"><?php echo $third_month_name;?></td>
  <td width="10%" class="rightside"><?php echo $forth_month_name;?></td>
  
  <td width="8%" class="rightside">Credit Limit</td>
  <td width="8%" class="rightside">balance</td>
  <td width="24%" colspan="2" class="rightside">Telly Date - Balacne</td>
  
  </tr>
</thead>
 
<?php
      
$cust_sql = "select cust_id from customers where sm_id ='".$sm_id."' and status = 'Active'";
$cust_statement = $connect->prepare($cust_sql);
$cust_statement->execute();
$cust_result = $cust_statement->fetchAll();
foreach($cust_result as $cust_row)
 {
  $cust_id = $cust_row['cust_id'];
  $acc_no = "104".$cust_id;

  $statement = $connect->prepare("select LEFT(a.acc_name,30) acc_name ,cr_limit, a.first_month,a.second_month,a.third_month,a.forth_month,a.sale_target,a.balance, a.short_target, a.recovery
    from(
    select b.acc_name,cr_limit, sum(first_month) first_month, sum(second_month) second_month,sum(third_month) third_month, sum(forth_month) forth_month,sum(sale_target) sale_target ,sum(balance) balance, sum(short_target) short_target,sum(recovery) recovery
    from (
    select name acc_name,cr_limit,0 as first_month, 0 as second_month, 0 as third_month, 0 as forth_month,sale_target,0 as balance,0 as short_target,0 as recovery from customers where cust_id = '".$cust_id."'
    union all
    select  a.acc_name,0 cr_limit,0 as first_month, 0 as second_month, 0 as third_month, 0 as forth_month,0 as sale_target,sum(d.debit-d.credit) as balance,0 as short_target,0 as recovery from jv_detail d, accounts a where a.acc_no=d.acc_no and a.acc_no = '".$acc_no."' group by acc_name
    union all
    select a.acc_name,0 cr_limit, sum(d.debit-d.credit) as first_month, 0 as second_month, 0 as third_month, 0 as forth_month,0 as sale_target,0 as balance, 0 as short_target,0 recovery from jv_detail d, jv m , accounts a where m.jv_id=d.jv_id and m.source = 'AR Inv' and a.acC_no=d.acc_no and a.acc_no = '".$acc_no."' and m.dated between '".$first_month_fdate."' AND '".$first_month_tdate."' group by acc_name
   union all
    select a.acc_name,0 cr_limit, 0 as first_month, sum(d.debit-d.credit) as second_month, 0 as third_month, 0 as forth_month,0 as sale_target,0 as balance, 0 as short_target,0 recovery from jv_detail d, jv m , accounts a where m.jv_id=d.jv_id and m.source = 'AR Inv' and a.acC_no=d.acc_no and a.acc_no = '".$acc_no."' and m.dated between '".$second_month_fdate."' AND '".$second_month_tdate."' group by acc_name
     union all
      select a.acc_name, 0 cr_limit,0 as first_month, 0 as second_month, sum(d.debit-d.credit) as third_month, 0 as forth_month,0 as sale_target,0 as balance, 0 as short_target,0 recovery from jv_detail d, jv m , accounts a where m.jv_id=d.jv_id and m.source = 'AR Inv' and a.acC_no=d.acc_no and a.acc_no = '".$acc_no."' and m.dated between '".$third_month_fdate."' AND '".$third_month_tdate."'  group by acc_name
       union all
    select a.acc_name,0 cr_limit, 0 as first_month, 0 as second_month, 0 as third_month, sum(d.debit-d.credit) as forth_month,0 as sale_target,0 as balance, 0 as short_target,0 recovery from jv_detail d, jv m , accounts a where m.jv_id=d.jv_id and m.source = 'AR Inv' and a.acc_no=d.acc_no and a.acc_no = '".$acc_no."' and m.dated between '".$forth_month_fdate."' AND '".$forth_month_tdate."'  group by acc_name
    union all
    
    select a.acc_name, 0 cr_limit,0 as first_month, 0 as second_month, 0 as third_month, 0 as forth_month,0 as sale_target,0 as balance, 0 as short_target,sum(d.credit-d.debit) recovery from jv_detail d, jv m , accounts a where m.jv_id=d.jv_id and m.source = 'AR Pay' and a.acc_no=d.acc_no and a.acc_no = '".$acc_no."' and m.dated between :fdate and :tdate group by acc_name
    union all
    
    select a.acc_name, 0 cr_limit,0 as first_month, 0 as second_month, 0 as third_month, 0 as forth_month,0 as sale_target,0 as balance, 0 as short_target,sum(d.credit-d.debit) recovery from jv_detail d, jv m , accounts a where m.jv_id=d.jv_id and m.source = 'GL Jv' and a.acc_no=d.acc_no and a.acc_no = '".$acc_no."' and m.dated between :fdate and :tdate group by acc_name
    )b group by acc_name) a where  balance <> 0
    ");
 $statement->execute(
  array(
   
   ':fdate'       =>  $fdate,
   ':tdate'       =>  $tdate
  )
 );
 $result = $statement->fetchAll();
 $srSQL = "select IFNULL(sum(d.credit-d.debit),0) from jv_detail d, jv m where m.jv_id=d.jv_id and m.dated between '".$first_month_fdate."' and '".$first_month_tdate."' and d.acc_no = '".$acc_no."' and m.source = 'AR InvR'";

 $srstatement = $connect->prepare($srSQL);
 $srstatement->execute();
 $first_month_return = $srstatement->fetchcolumn();
 $srSQL = "select IFNULL(sum(d.credit-d.debit),0) from jv_detail d, jv m where m.jv_id=d.jv_id and m.dated between '".$second_month_fdate."' and '".$second_month_tdate."' and d.acc_no = '".$acc_no."' and m.source = 'AR InvR'";

 $srstatement = $connect->prepare($srSQL);
 $srstatement->execute();
 $second_month_return = $srstatement->fetchcolumn();
 $srSQL = "select IFNULL(sum(d.credit-d.debit),0) from jv_detail d, jv m where m.jv_id=d.jv_id and m.dated between '".$third_month_fdate."' and '".$third_month_tdate."' and d.acc_no = '".$acc_no."' and m.source = 'AR InvR'";

 $srstatement = $connect->prepare($srSQL);
 $srstatement->execute();
 $third_month_return = $srstatement->fetchcolumn();
 $srSQL = "select IFNULL(sum(d.credit-d.debit),0) from jv_detail d, jv m where m.jv_id=d.jv_id and m.dated between '".$forth_month_fdate."' and '".$forth_month_tdate."' and d.acc_no = '".$acc_no."' and m.source = 'AR InvR'";

 $srstatement = $connect->prepare($srSQL);
 $srstatement->execute();
 $forth_month_return = $srstatement->fetchcolumn();
foreach($result as $row)
 {
  
  $first_month = $row["first_month"] - $first_month_return;
  $second_month = $row["second_month"] - $second_month_return;
  $third_month = $row["third_month"] - $third_month_return;
  $forth_month = $row["forth_month"] - $forth_month_return;
  $sale_target = $row["sale_target"];
  $cr_limit = $row["cr_limit"];
  $balance = $row["balance"];
  $short_target = $sale_target - $forth_month;
  $recovery = $row["recovery"];
  
  $tot_recovery = $tot_recovery + $recovery;
  
  $tot_first_month = $tot_first_month + $first_month;
  $tot_second_month = $tot_second_month + $second_month;
  $tot_third_month = $tot_third_month + $third_month;
  $tot_forth_month = $tot_forth_month + $forth_month;
$tot_sale_target = $tot_sale_target + $sale_target;
  $tot_short_target = $tot_short_target + $short_target;
  $tot_balance = $tot_balance + $balance;
 
   $count = $count+1;
     // if($count == 1){$stock = $opening + $qty_in - $qty_out; }else{$stock = $stock + $qty_in - $qty_out;}
  
 echo '
  
    
  
  <tbody>
<tr>
<td width="2%">'.$count.'</td>
  
<td width="18%">'.$row["acc_name"].'</td>
    
    
  <td width="10%" class="rightside">'.number_format($first_month,0).'</td>
  <td width="10%" class="rightside">'.number_format($second_month,0).'</td>
  <td width="10%" class="rightside">'.number_format($third_month,0).'</td>
  <td width="10%" class="rightside">'.number_format($forth_month,0).'</td>
  
  <td width="8%" class="rightside">'.number_format($cr_limit,0).'</td>
  <td width="8%" class="rightside">'.number_format($balance,0).'</td>
  <td width="24%" colspan="2" class="rightside"></td>
  
  
   
    
   </tr>
   
</tbody>
  
        ';
    
}
}
?>
<tfoot>
  <div class="footer">
  
  <table width="100%" class="print_table">
  
  <td colospan="2" width="20%" class="rightside bor">Total :</td>
  <td width="10%" style="border-top:1px solid black" class="rightside"><?php echo''.number_format($tot_first_month, 0).'';?></td>
  <td width="10%" style="border-top:1px solid black" class="rightside"><?php echo''.number_format($tot_second_month, 0).'';?></td>
  <td width="10%" style="border-top:1px solid black" class="rightside"><?php echo''.number_format($tot_third_month, 0).'';?></td>
  <td width="10%" style="border-top:1px solid black" class="rightside"><?php echo''.number_format($tot_forth_month, 0).'';?></td>
  
  <td width="8%" style="border-top:1px solid black" class="rightside"></td>
  <td width="8%" style="border-top:1px solid black" class="rightside"><?php echo''.number_format($tot_balance, 0).'';?></td>
  <td colspan="2" width="24%" style="border-top:1px solid black" class="rightside"></td>
  
 
  </tr>
  </tfoot>
    
  
 
 
 
    
 </tfoot>
</table>
</body>
 </html>