<?php 



include('db_coni.php');



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



$sql = "SELECT date_sub(curdate(),interval 10 day)";



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



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



$tendays = $statement->fetchColumn();



$sql = "SELECT date_sub(curdate(),interval 30 day)";



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



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



$thirtydays = $statement->fetchColumn();



$sql = "SELECT date_sub(curdate(),interval 1 day)";



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



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



$lastday = $statement->fetchColumn();



$query = "SELECT m.dated,sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)   + sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)  * gst_pr / 100 -m.discount + m.freight as sale,sum(d.qty*d.cost)as cost,sum(d.qty*d.rate-d.qty*d.cost)-m.discount as profit



from sale_detail d, sale m where m.s_id=d.s_id and m.dated BETWEEN '".$thirtydays."' and '".$cur_date."'  and m.branch_id <> 5 group by m.dated";



$result = mysqli_query($connecti, $query);



$chart_data = '';



while($row = mysqli_fetch_array($result))



{



 $chart_data .= "{ dated:'".$row["dated"]."', profit:".$row["profit"].", cost:".$row["cost"].", sale:".$row["sale"]."}, ";



}



$chart_data = substr($chart_data, 0, -2);



$query = "SELECT i.item_name,sum(d.qty)as qty,sum(d.qty*d.cost)as cost,sum(d.qty*d.rate-d.qty*d.cost)-m.discount as profit from sale_detail d,sale m, items i where m.s_id=d.s_id and i.item_id=d.item_no and m.dated BETWEEN '".$thirtydays."' and '".$cur_date."'  and m.branch_id <> 5 group by i.item_name order by qty desc LIMIT 10";



$result = mysqli_query($connecti, $query);



$itemchart_data = '';



while($row = mysqli_fetch_array($result))



{



 $itemchart_data .= "{ item_name:'".$row["item_name"]."', qty:".$row["qty"].",cost:".$row["cost"].",profit:".$row["profit"]."}, ";



}



$itemchart_data = substr($itemchart_data, 0, -2);



$query = "SELECT b.branch_name,sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)   + sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)  * gst_pr / 100 -m.discount + m.freight as sale,sum(d.qty*d.cost)as cost,sum(d.qty*d.rate-d.qty*d.cost)-m.discount as profit



from sale_detail d, sale m,branch b where m.s_id=d.s_id and m.branch_id=b.branch_id and m.dated ='".$lastday."' group by b.branch_name";



$result = mysqli_query($connecti, $query);



$branchchart_data = '';



while($row = mysqli_fetch_array($result))



{



 $branchchart_data .= "{ branch:'".$row["branch_name"]."', profit:".$row["profit"].", cost:".$row["cost"].", sale:".$row["sale"]."}, ";



}



$branchchart_data = substr($branchchart_data, 0, -2);



$query = "SELECT b.branch_name,sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)   + sum(d.qty*d.rate - d.qty*d.dis_rate - d.rate * d.dis_pr / 100 * d.qty)  * gst_pr / 100 -m.discount + m.freight as sale,sum(d.qty*d.cost)as cost,sum(d.qty*d.rate-d.qty*d.cost)-m.discount as profit



from sale_detail d, sale m,branch b where m.s_id=d.s_id and m.branch_id=b.branch_id and m.dated between '".$thirtydays."' and '".$cur_date."' group by b.branch_name";



$result = mysqli_query($connecti, $query);



$thirtybranchchart_data = '';



while($row = mysqli_fetch_array($result))



{



 $thirtybranchchart_data .= "{ branch:'".$row["branch_name"]."', profit:".$row["profit"].", cost:".$row["cost"].", sale:".$row["sale"]."}, ";



}



$thirtybranchchart_data = substr($thirtybranchchart_data, 0, -2);



$query = "SELECT i.item_name,IFNULL(sum(d.qty),0)as qty from sale_detail d,sale m, items i where m.s_id=d.s_id and i.item_id=d.item_no group by i.item_name order by qty desc LIMIT 5";



$result = mysqli_query($connecti, $query);



$singleitemchart_data = array();



while($row = mysqli_fetch_array($result))



{



 $singleitemchart_data[] = array(



      'label' => $row["item_name"],



      'value' => $row["qty"]



  );



}



$singleitemchart_data = json_encode($singleitemchart_data);



?>