<?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();


}


 $html = '';


 $fdate = $_GET['fdate'];


 $v_id = $_GET['sv_id'];


 $acc_no = $_GET['acc_no'];


 $tdate = $_GET['tdate'];


 $opening = 0;


 $count = 0;


 $balance = 0;


 $tot_debit = 0;


 $tot_credit = 0;


 $dated = date('d-m-Y');    


  $sql = "SELECT IFNULL(sum(d.debit-d.credit),0)as opening FROM jv m,jv_detail d


  WHERE m.jv_id=d.jv_id and m.dated < '".$fdate."' and d.acc_no ='".$acc_no."' and m.status ='Active' ";


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


            $statement->execute(); // no need to add `$sql` here, you can take that ou


           $opening_balance = $statement->fetchColumn();


          


           $opening = $opening + $opening_balance;


            


           //$closing = $closing + $opening;


           $sql = "SELECT acc_name FROM accounts WHERE acc_no ='".$acc_no."' ";


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


            $statement->execute(); // no need to add `$sql` here, you can take that out


           $acc_name = $statement->fetchColumn();


  $statement = $connect->prepare("select m.dated,m.source,m.ref_id,m.description,


    d.debit,d.credit from jv m, jv_detail d where m.jv_id=d.jv_id 


    and m.dated between :fdate and :tdate and d.acc_no  = :acc_no and m.status = 'Active' 


    order by dated");


 $statement->execute(


  array(


   ':acc_no'       =>  $acc_no,


   ':fdate'       =>  $fdate,


   ':tdate'       =>  $tdate


  )


 );


 $result = $statement->fetchAll();


?>


<!DOCTYPE html>


<html>


<head>


  <title>


  </title>


  <style>


* {


  font-size: 11px;


  font-family: arial;


}


@media print {


  @page { margin: 0; }


  body { margin: 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;


  }


  .detail_row {


    font-weight: normal;


  }


  .imp {


    font-weight: bold;


  }


  .small-font {


    font-weight: normal;


    font-family: arial;


    font-size: 9px;


    


  }


  </style>


</head>


<body>


  <table width="100%">


<tr>


  <td colspan="4" width="25%"><b>Vendor Ledger</b> as on : <?php echo $dated;?></td>


  </tr>


  <tr>


  <td width="25%">Acc No :</td><td width="35%"><?php echo $acc_no;?></td><td width="15%" class="rightside">From :</td><td width="25%" class="rightside"><?php echo $fdate;?></td>


  </tr>


  <tr>


  <td width="25%" class="total">Name : </td><td width="35%" class="total"><?php echo $acc_name;?></td><td width="15%" class="rightside">To :</td><td width="25%" class="rightside"><?php echo $tdate;?></td>


  </tr>


  <tr>


  <td  colspan="3" width="80%" class="rightside">Opening Balance : </td><td width="20%" class="rightside"><?php echo $opening;?></td>


  </tr>


  </table>


  <table width="100%" id="table_header">


<thead>


  <tr>


  <td width="3%">#</td>


  <td width="10%">Date</td>


  <td width="15%" class="dataline">Source</td>


  


  <td width="34%">Description</td>


  


  <td width="12%" class="rightside">Debit</td>


  <td width="12%" class="rightside">Credit</td>


  <td width="14%" class="rightside">Balance</td>


  </tr>


</thead>


  <tbody>


<?php


foreach($result as $row)


 {


  $debit = $row["debit"];


  $source = $row["source"];


  $ref_id = $row["ref_id"];


  $credit = $row["credit"];


  $tot_debit = $tot_debit + $debit;


  $tot_credit = $tot_credit + $credit;


  $count = $count+1;


  if($count == 1){$balance = $opening + $debit - $credit; }else{$balance = $balance + $debit - $credit;}


echo '


<tr>


<td width="3%" class="dataline">'.$count.'</td>


  


<td width="10%" class="dataline imp">'.$row["dated"].'</td>


<td width="15%" class="dataline imp">'.$row["source"].' - '.$row["ref_id"].'</td>


<td width="34%" class="dataline imp">'.$row["description"].'</td>


<td width="12%" class="rightside dataline imp">'.number_format($debit, 2).'</td>


   <td width="12%" class="rightside dataline imp">'.number_format($credit, 2).'</td>


   <td width="14%" class="rightside dataline imp">'.number_format($balance, 2).'</td>


  


   


    


   </tr>


';


if($source=='AP Inv')


{


  $sub_statement = $connect->prepare("select m.p_id,m.inv_no,m.po_no,m.description,m.discount,m.freight,m.gst_pr from purchase m where m.p_id = '".$ref_id."'");


$sub_statement->execute();


$sub_result = $sub_statement->fetchAll();


$tot_qty = 0;


$tot_ctn = 0;


$tot_amt = 0;


foreach($sub_result as $sub_row)


{


  $p_id = $sub_row['p_id'];


  $amount = 0;


$discount = $sub_row["discount"];


$freight = $sub_row["freight"];


$gst_pr = $sub_row["gst_pr"];


  echo '


  


  <thead>


    <tr>


    <td colspan="7" width="100%">Purchase ID: '.$p_id.' --- Inv No.: '.$sub_row['inv_no'].' --- PO No.: '.$sub_row['po_no'].' --- Discount : '.$discount.' --- freight : '.$freight.' ---- GST(%): '.$gst_pr.'</td>


    </tr>


    </thead>


    <tr>


  <th colspan="7"width="60%" class="small-font">


  <table width="60%"  class="detail_table">


   <tr class="detail_row small-font">


  <td width="3%">Sr</td>


  <td width="32%" class="small-font">Item Name</td>


<td width="5%" class="small-font">UOM</td>


<td width="3%" class="rightside small-font">ctn</td>


<td width="5%" class="rightside small-font">Qty</td>


   <td width="5%" class="rightside small-font">Rate</td>


   <td width="7%" class="rightside small-font">Discount</td>


   <td width="3%" class="rightside small-font"></td>


   <td width="7%" class="rightside small-font">Amount</td>


</tr>


  


  ';


  $detail_statement = $connect->prepare("select CONCAT(i.item_name,' ',t.type_name,' ',b.brand_name,' ',c.color_name) item_name,u.uom_name,d.ctn,d.qty,d.rate,d.dis_rate,d.dis_pr from purchase_detail d, items i,item_type t,item_brand b,item_color c,item_uom u where i.item_id=d.item_no and i.type_id=t.type_id and b.brand_id=i.brand_id and i.color_id=c.color_id and u.uom_id=i.uom and d.p_id  = '".$p_id."'");


  $detail_statement->execute();


  $detail_result = $detail_statement->fetchAll();


    


$sr = 0;


  foreach($detail_result as $detail_row)


  {


    $sr++;


  $qty = $detail_row["qty"];


  $ctn = $detail_row["ctn"];


  $rate = $detail_row["rate"];


  $dis_rate = $detail_row["dis_rate"];


  $dis_pr = $detail_row["dis_pr"];


  if($dis_rate > 0)


  {


    $dis_item = $qty * $dis_rate; 


    $dis_type = "PP";


    $dis_given = $dis_rate;


  }else{


    $dis_rate = $rate * $dis_pr / 100;


    $dis_item = $qty * $dis_rate;


    $dis_given = $dis_pr;


    $dis_type="%";}


  


  


  $amount = $qty * $rate - $dis_item;


  $tot_qty = $tot_qty + $qty;


  $tot_ctn = $tot_ctn + $ctn;


  $tot_amt = $tot_amt + $amount;


  echo '


  <tr class="detail_row">


  <td width="3%">'.$sr.'</td>


  <td width="32%" class="small-font">'.$detail_row["item_name"].'</td>


<td width="5%" class="small-font">'.$detail_row["uom_name"].'</td>


<td width="3%" class="rightside small-font">'.$detail_row["ctn"].'</td>


<td width="5%" class="rightside small-font">'.number_format($qty, 2).'</td>


   <td width="5%" class="rightside small-font">'.number_format($rate, 2).'</td>


   <td width="7%" class="rightside small-font">'.number_format($dis_given, 2).'</td>


   <td width="3%" class="rightside small-font">'.$dis_type.'</td>


   <td width="7%" class="rightside small-font">'.number_format($amount, 2).'</td>


</tr>


';


  }


}


?></table>


  </th>


  </tr>


  <?php


}


if($source=='AP InvR')


{


  $sub_statement = $connect->prepare("select m.pr_id p_id,m.inv_no,m.po_no,m.description,m.discount,m.freight,m.gst_pr from pr m where m.pr_id = '".$ref_id."'");


$sub_statement->execute();


$sub_result = $sub_statement->fetchAll();


$tot_qty = 0;


$tot_ctn = 0;


$tot_amt = 0;


foreach($sub_result as $sub_row)


{


  $p_id = $sub_row['p_id'];


  $amount = 0;


$discount = $sub_row["discount"];


$freight = $sub_row["freight"];


$gst_pr = $sub_row["gst_pr"];


  echo '


  


  <thead>


    <tr>


    <td colspan="7" width="100%">PurchaseReturn ID: '.$p_id.' --- Inv No.: '.$sub_row['inv_no'].' --- PO No.: '.$sub_row['po_no'].' --- Discount : '.$discount.' --- freight : '.$freight.' ---- GST(%): '.$gst_pr.'</td>


    </tr>


    </thead>


    <tr>


  <th colspan="7"width="60%" class="small-font">


  <table width="60%"  class="detail_table">


   <tr class="detail_row small-font">


  <td width="3%">Sr</td>


  <td width="32%" class="small-font">Item Name</td>


<td width="5%" class="small-font">UOM</td>


<td width="3%" class="rightside small-font">ctn</td>


<td width="5%" class="rightside small-font">Qty</td>


   <td width="5%" class="rightside small-font">Rate</td>


   <td width="7%" class="rightside small-font">Discount</td>


   <td width="3%" class="rightside small-font"></td>


   <td width="7%" class="rightside small-font">Amount</td>


</tr>


  


  ';


  $detail_statement = $connect->prepare("select CONCAT(i.item_name,' ',t.type_name,' ',b.brand_name,' ',c.color_name) item_name,u.uom_name,d.ctn,d.qty,d.rate,d.dis_rate,d.dis_pr from pr_detail d, items i,item_type t,item_brand b,item_color c,item_uom u where i.item_id=d.item_no and i.type_id=t.type_id and b.brand_id=i.brand_id and i.color_id=c.color_id and u.uom_id=i.uom and d.pr_id  = '".$p_id."'");


  $detail_statement->execute();


  $detail_result = $detail_statement->fetchAll();


    


$sr = 0;


  foreach($detail_result as $detail_row)


  {


    $sr++;


  $qty = $detail_row["qty"];


  $ctn = $detail_row["ctn"];


  $rate = $detail_row["rate"];


  $dis_rate = $detail_row["dis_rate"];


  $dis_pr = $detail_row["dis_pr"];


  if($dis_rate > 0)


  {


    $dis_item = $qty * $dis_rate; 


    $dis_type = "PP";


    $dis_given = $dis_rate;


  }else{


    $dis_rate = $rate * $dis_pr / 100;


    $dis_item = $qty * $dis_rate;


    $dis_given = $dis_pr;


    $dis_type="%";}


  


  


  $amount = $qty * $rate - $dis_item;


  $tot_qty = $tot_qty + $qty;


  $tot_ctn = $tot_ctn + $ctn;


  $tot_amt = $tot_amt + $amount;


  echo '


  <tr class="detail_row">


  <td width="3%">'.$sr.'</td>


  <td width="32%" class="small-font">'.$detail_row["item_name"].'</td>


<td width="5%" class="small-font">'.$detail_row["uom_name"].'</td>


<td width="3%" class="rightside small-font">'.$detail_row["ctn"].'</td>


<td width="5%" class="rightside small-font">'.number_format($qty, 2).'</td>


   <td width="5%" class="rightside small-font">'.number_format($rate, 2).'</td>


   <td width="7%" class="rightside small-font">'.number_format($dis_given, 2).'</td>


   <td width="3%" class="rightside small-font">'.$dis_type.'</td>


   <td width="7%" class="rightside small-font">'.number_format($amount, 2).'</td>


</tr>


';


  }


}


?></table>


  </th>


  </tr>


  <?php


}


}


?>


</tbody>


</table>


<table width="100%">


  <tr>


  <td colspan="4" width="62%" class="rightside total">Total: </td>


  <td width="12%" class="rightside total"><?php echo number_format($tot_debit, 2) ;?></td>


  <td width="12%" class="rightside total"><?php echo number_format($tot_credit, 2);?></td>


  <td width="14%" class="rightside total"><?php echo number_format($balance, 2);?></td>


  


  </tr>


</table>


</body>


</html>


