<?php 
include "../config.php";
$customer=$_POST['customer'];
$from_date = $_POST['fromdate'];
$to_date = $_POST['todate'];
if(empty($from_date)&&empty($to_date)){
$sales_sql = "select date,(ifnull((select tripno from ticket where ticket.ticket = second_transaction.ticket && status!='deleted'),'-'))as tripno,customer,salescode as entrycode,totalamount,('Sales')as process,productname,netqty,('-') as collectionby,('-')as cash,('-')as bank,('-')as collection,financialyear,remark from second_transaction where customer='$customer'&& status!='deleted'
union
select date,(ifnull((select tripno from ticket where ticket.ticket = second_transaction_est.ticket && status!='deleted'),'-'))as tripno,customer,salescode as entrycode,totalamount,('Sale')as process,productname,netqty,('-') as collectionby,('-')as cash,('-')as bank,('-')as collection,financialyear,remark from second_transaction_est where customer='$customer'&& status!='deleted'
union
select date,('-')as tripno,customer,code as entrycode,('-')as totalamount,('Collection')as process,('-')as productname,('-')as netqty,paymentmethod as collectionby,amount as cash, otheramount as bank,(amount+otheramount)as collection,financialyear,remark from creditdetails where customer = '$customer'&& status!='deleted' order by date asc
";
$sales_data = mysqli_query($connect,$sales_sql);
while($sales_statement=mysqli_fetch_array($sales_data))
{
    $customer = $sales_statement['customer'];
    $date_from = $sales_statement['date'];
    $date_change = date_create($date_from);
    $date_format = date_format($date_change,"d/m/y");
$cus=mysqli_query($connect,"select name from customerdetails where code='$customer' && status !='deleted'");
while($cusshow = mysqli_fetch_array($cus))
{
   $customer_name = $cusshow['name'];
}
    $statement[]=(object)["customercode"=>$customer,"tripno"=>$sales_statement['tripno'],"customername"=>$customer_name,"salescode"=>$sales_statement['entrycode'],"process"=>$sales_statement['process'],"product"=>$sales_statement['productname'],"quantity"=>$sales_statement['netqty'],"salesamount"=>$sales_statement['totalamount'],"collectionby"=>$sales_statement['collectionby'],"cash"=>$sales_statement['cash'],"bank"=>$sales_statement['bank'],"collection"=>$sales_statement['collection'],"financialyear"=>$sales_statement['financialyear'],"remark"=>$sales_statement['remark'],"date"=>$date_format];
}
$opening_balance = mysqli_query($connect,"select salesopening from customerdetails where code='$customer'&& status!='deleted'");
$dis_opening = mysqli_fetch_assoc($opening_balance);
$opening_value = $dis_opening['salesopening'];
$sales_balance = mysqli_query($connect,"select ifnull(sum(totalamount),0)as sales from second_transaction where customer='$customer'&& status!='deleted'");
$dis_sales = mysqli_fetch_assoc($sales_balance);
$sales_value = $dis_sales['sales'];
$sales_balance_est = mysqli_query($connect,"select ifnull(sum(totalamount),0)as sales from second_transaction_est where customer='$customer'&& status!='deleted'");
$dis_sales_est = mysqli_fetch_assoc($sales_balance_est);
$sales_value_est = $dis_sales_est['sales'];
$cash_balance = mysqli_query($connect,"select ifnull(sum(amount),0)as amount,ifnull(sum(otheramount),0)as otheramount from creditdetails where customer='$customer'&& status!='deleted'");
$dis_cash = mysqli_fetch_assoc($cash_balance);
$cash_value = $dis_cash['amount'];
$other_value = $dis_cash['otheramount'];
$total_amount = $opening_value + $sales_value + $sales_value_est;
$pending_amount = $total_amount-$cash_value-$other_value;
$mini_statement= (object)["name"=>$customer_name,"opening"=>$opening_value,"sales"=>$sales_value + $sales_value_est,"collection"=>$cash_value + $other_value,"pending"=>$pending_amount];
$final_output = (object)["statement"=>$statement,"ministatement"=>$mini_statement];
echo json_encode($final_output);
}
// for date wise filterz
if(!empty($customer)&& !empty($from_date)&& !empty($to_date))
{
    $sales_sql = "select date,(ifnull((select tripno from ticket where ticket.ticket = second_transaction.ticket && status!='deleted'),'-'))as tripno,customer,salescode as entrycode,totalamount,('Sales')as process,productname,netqty,('-') as collectionby,('-')as cash,('-')as bank,('-')as collection,financialyear,remark from second_transaction where customer='$customer' && date between '$from_date' and '$to_date'&& status!='deleted'
union
select date,(ifnull((select tripno from ticket where ticket.ticket = second_transaction_est.ticket && status!='deleted'),'-'))as tripno,customer,salescode as entrycode,totalamount,('Sale')as process,productname,netqty,('-') as collectionby,('-')as cash,('-')as bank,('-')as collection,financialyear,remark from second_transaction_est where customer='$customer' && date between '$from_date' and '$to_date'&& status!='deleted'
union
select date,('-') as tripno,customer,code as entrycode,('-')as totalamount,('Collection')as process,('-') as productname,('-') as netqty,paymentmethod as collectionby,amount as cash, otheramount as bank,(amount+otheramount)as collection,financialyear,remark from creditdetails where customer = '$customer'&& date between '$from_date' and '$to_date'&& status!='deleted' order by date asc
";
$sales_data = mysqli_query($connect,$sales_sql);
while($sales_statement=mysqli_fetch_array($sales_data))
{
    $customer = $sales_statement['customer'];
    $date_from = $sales_statement['date'];
    $date_change = date_create($date_from);
    $date_format = date_format($date_change,"d/m/y");
$cus=mysqli_query($connect,"select name from customerdetails where code='$customer' && status !='deleted'");
while($cusshow = mysqli_fetch_array($cus))
{
   $customer_name = $cusshow['name'];
}
$statement[]=(object)["customercode"=>$customer,"tripno"=>$sales_statement['tripno'],"customername"=>$customer_name,"salescode"=>$sales_statement['entrycode'],"process"=>$sales_statement['process'],"product"=>$sales_statement['productname'],"quantity"=>$sales_statement['netqty'],"salesamount"=>$sales_statement['totalamount'],"collectionby"=>$sales_statement['collectionby'],"cash"=>$sales_statement['cash'],"bank"=>$sales_statement['bank'],"collection"=>$sales_statement['collection'],"financialyear"=>$sales_statement['financialyear'],"remark"=>$sales_statement['remark'],"date"=>$date_format];
}
$opening_balance_date = mysqli_query($connect,"select salesopening from customerdetails where code='$customer'&& status!='deleted'");
$dis_opening_date = mysqli_fetch_assoc($opening_balance_date);
$opening_value_date = $dis_opening_date['salesopening'];
$sales_balance_date= mysqli_query($connect,"select ifnull(sum(totalamount),0)as sales from second_transaction where customer='$customer'&& status!='deleted' && date < '$from_date'");
$dis_sales_date = mysqli_fetch_assoc($sales_balance_date);
$sales_value_date = $dis_sales_date['sales'];
$sales_balance_date_est= mysqli_query($connect,"select ifnull(sum(totalamount),0)as sales from second_transaction_est where customer='$customer'&& status!='deleted' && date < '$from_date'");
$dis_sales_date_est = mysqli_fetch_assoc($sales_balance_date_est);
$sales_value_date_est = $dis_sales_date_est['sales'];
$cash_balance_date = mysqli_query($connect,"select ifnull(sum(amount),0)as amount,ifnull(sum(otheramount),0)as otheramount from creditdetails where customer='$customer'&& status!='deleted' && date < '$from_date'");
$dis_cash_date = mysqli_fetch_assoc($cash_balance_date);
$cash_value_date = $dis_cash_date['amount'];
$other_value_date = $dis_cash_date['otheramount'];
$total_amount_date = $opening_value_date + $sales_value_date + $sales_value_date_est;
$pending_amount_date = $total_amount_date-$cash_value_date-$other_value_date;
$sales_balance = mysqli_query($connect,"select ifnull(sum(totalamount),0)as sales from second_transaction where customer='$customer'&& status!='deleted' && date between '$from_date' and '$to_date'");
$dis_sales = mysqli_fetch_assoc($sales_balance);
$sales_value = $dis_sales['sales'];
$sales_balance_est = mysqli_query($connect,"select ifnull(sum(totalamount),0)as sales from second_transaction_est where customer='$customer'&& status!='deleted' && date between '$from_date' and '$to_date'");
$dis_sales_est = mysqli_fetch_assoc($sales_balance_est);
$sales_value_est = $dis_sales_est['sales'];
$cash_balance = mysqli_query($connect,"select ifnull(sum(amount),0)as amount,ifnull(sum(otheramount),0)as otheramount from creditdetails where customer='$customer'&& status!='deleted'&& date between '$from_date' and '$to_date'");
$dis_cash = mysqli_fetch_assoc($cash_balance);
$cash_value = $dis_cash['amount'];
$other_value = $dis_cash['otheramount'];
$total_amount = $pending_amount_date + $sales_value + $sales_value_est;
$pending_amount = $total_amount-$cash_value-$other_value;
$mini_statement= (object)["name"=>$customer_name,"opening"=>$pending_amount_date,"sales"=>$sales_value + $sales_value_est,"collection"=>$cash_value+$other_value,"pending"=>$pending_amount];
$final_output = (object)["statement"=>$statement,"ministatement"=>$mini_statement];
echo json_encode($final_output);
}
?>