'2023-12-18'");
while($set_snaps = mysqli_fetch_array($get_snaps, MYSQLI_ASSOC)){
$start_date = $set_snaps['snaps'];
$date = new DateTime($set_snaps['snaps']);
$yesterday= $date->modify("-1 days")->format('Y-m-d');
$disp_open = calc_open($pid, $yesterday, $conn);
$disp_pending = 0;
$arr = array();
$arr_1 = array();
$arr_2 = array();
?>
| DATE |
REF ID |
FROM |
OPEN |
SOLD |
ADDED |
RETURN |
BALANCE |
STATUS |
INSTOCK |
'.date('d-m-Y', strtotime($start_date)).' |
|
|
'.$disp_open.' |
|
|
|
'.$disp_open.' |
|
'.($disp_open).' |
';
$not_show_open = 0;
$give_add = 0;
$get_add = mysqli_query($conn, "SELECT * FROM tbl_india_update_stock WHERE product_id = $pid AND DATE(stock_date) = '$start_date'");
$check_add = mysqli_num_rows($get_add);
if($check_add > 0){
$not_show_open = $not_show_open + 1;
while($set_add = mysqli_fetch_array($get_add, MYSQLI_ASSOC)){
$added = $set_add['new_stock'];
$give_add+=($set_add['new_stock'] + $disp_open);
$from_name = $set_add['from_name'];
echo ' | '.date('d-m-Y', strtotime($start_date)).' |
|
'.$from_name.' |
|
|
'.$added.' |
|
'.$give_add.' |
|
'. $give_add.' |
';
$disp_open = 0;
}
}
$get_orid = mysqli_query($conn,"Select * from tbl_confirmorder WHERE DATE_FORMAT(order_date_time, '%Y-%m-%d') = '$start_date' AND refid NOT LIKE '%SYP%' and company_name not like 'Siddhayoga Pharma' and orderstatus not like '90'");
while($set_orid = mysqli_fetch_array($get_orid, MYSQLI_ASSOC))
{
$pattern1 = "/CO/i";
$pattern2 = "/Siddhayoga/i";
$pattern3 = "/Cash/i";
$pattern4 = "/HC/i";
$pattern5 = "/Energy/i";
array_push($arr, $set_orid['orderid']);
}
ksort($arr);
foreach($arr as $arrays){
$check_sy = mysqli_query($conn, "select * from tbl_confirmorder where refid like '%HC%' and company_name='Energy Foods' and userid='7444' and orderid='$arrays'");
$check_syp = mysqli_num_rows($check_sy);
if($check_syp == 0){
array_push($arr_2, $arrays);
}
}
foreach($arr_2 as $arr_zee){
$check_yv = mysqli_query($conn, "select * from tbl_order where orderid='$arr_zee'");
$yv_count = mysqli_num_rows($check_yv);
$check_yvs = mysqli_query($conn, "SELECT * FROM tbl_order td INNER JOIN shop_product sp on td.productid = sp.product_id WHERE td.orderid='$arr_zee' and sp.cloud_url<>'' ORDER BY td.ordid desc;");
$yvs_count = mysqli_num_rows($check_yvs);
if($yv_count != $yvs_count){
array_push($arr_1, $arr_zee);
}
}
ksort($arr_1);
$i=0;
$iter = count($arr_1);
$bool = 0;
foreach($arr_1 as $get_or){
$pending = 0;
$packing = 0;
$new = 0;
$check_if = mysqli_query($conn, "select * from tbl_order where orderid = '$get_or' and productid='$pid'");
$myrows = mysqli_num_rows($check_if);
if($myrows > 0){
$iter = $iter-1;
$i=$i+1;
$get_refund_count =0;
$set_if = mysqli_fetch_array($check_if, MYSQLI_ASSOC);
$get_ref = mysqli_query($conn, "select * from tbl_confirmorder where orderid='$get_or'");
$set_ref = mysqli_fetch_array($get_ref, MYSQLI_ASSOC);
//added
$get_add = mysqli_query($conn, "SELECT * FROM tbl_india_update_stock WHERE product_id = $pid AND DATE(stock_date) = '$start_date'");
$check_add = mysqli_num_rows($get_add);
if($check_add == 0){
$added = 0;
$from_name = '';
}else{
$my_add = mysqli_query($conn, "SELECT sum(new_stock) as new_stock, CONCAT_WS(' ',from_name) as from_name FROM tbl_india_update_stock WHERE product_id = $pid AND DATE(stock_date) = '$start_date'");
$set_add = mysqli_fetch_array($my_add, MYSQLI_ASSOC);
$added = $set_add['new_stock'];
$from_name = $set_add['from_name'];
$bool = $bool + 1;
}
//opening balance
// echo "select * from tbl_cron_stock_report where product_id ='$pid' and snap_date='$yesterday'";
$open_bal = mysqli_query($conn, "select * from tbl_cron_stock_report where product_id ='$pid' and snap_date='$yesterday'");
$check_bal_rows = mysqli_num_rows($open_bal);
$set_bal = mysqli_fetch_array($open_bal, MYSQLI_ASSOC);
$opening_balance = $set_bal['balance'];
//refund count
$get_refu = mysqli_query($conn, "SELECT ts.qty + ts.freeqty as qtys FROM tbl_confirmorder tb inner join tbl_order ts on tb.orid = ts.orderid where tb.orderstatus = 4 AND ts.productid='$pid' and ts.orderid = '$get_or' and DATE(tb.refund_time) = '$start_date'");
$check_rows_2 = mysqli_num_rows($get_refu);
if($check_rows_2 > 0){
$get_refund = mysqli_fetch_array($get_refu, MYSQLI_ASSOC);
$get_refund_count = $get_refund_count + $get_refund['qtys'];
}
if($i==1){
$balance = $opening_balance - ($set_if['qty'] + $set_if['freeqty']) + $added + $get_refund_count;
}else{
$balance = $balance - ($set_if['qty'] + $set_if['freeqty']) + $get_refund_count;
$opening_balance = '';
}
$get_packing = mysqli_query($conn, "SELECT sum(tb.qty + tb.freeqty) AS fqty FROM tbl_confirmorder tc INNER JOIN tbl_order tb ON tc.orderid = tb.orderid WHERE tc.orderstatus = 5 AND tc.varstatus!='Cash' and tc.userid!=7444 and tc.company_name='Energy Foods' and tb.productid='$pid' and tc.refid='$set_ref[refid]'");
$pack_rows = mysqli_num_rows($get_packing);
if($pack_rows > 0){
$set_packing = mysqli_fetch_array($get_packing, MYSQLI_ASSOC);
$packing = $set_packing['fqty'];
}else{
$packing = 0;
}
$get_new = mysqli_query($conn, "SELECT sum(tb.qty + tb.freeqty) AS fqty FROM tbl_confirmorder tc INNER JOIN tbl_order tb ON tc.orderid = tb.orderid WHERE tc.orderstatus = 0 AND tc.varstatus!='Cash' and tc.userid!=7444 and tc.company_name='Energy Foods' and tb.productid='$pid' and tc.refid='$set_ref[refid]'");
$new_rows = mysqli_num_rows($get_new);
if($new_rows > 0){
$set_new = mysqli_fetch_array($get_new, MYSQLI_ASSOC);
$new = $set_new['fqty'];
}else{
$new = 0;
}
$pending = $packing + $new;
if(!isset($pending)){
$pending = 0;
}
$instock = $balance + $pending;
echo ' | '.date('d-m-Y', strtotime($start_date)).' |
'.$set_ref['refid'].' |
| ';
echo '';
if($not_show_open > 0 || $myrows > 0){
echo '';
}else{
echo $opening_balance;
}
echo ' | ';
echo '
'.($set_if['qty'] + $set_if['freeqty']).' | ';
echo ' | ';
echo '';
if($get_refund_count == 0){
echo '';
}else{
echo $get_refund_count;
}
echo ' | ';
echo '
'.$balance.' | ';
echo '';
if(strpos($set_ref['refid'], 'CO') !== false){
echo 'SENT';
}elseif($pending == 0){
echo 'SENT';
}else{
echo $pending;
}
echo ' | ';
echo '
'.($balance + $set_if['qty'] + $set_if['freeqty']).' |
';
}
}
$bool1 = 0;
if(count($arr_1) == $iter){
$open_bal = mysqli_query($conn, "select * from tbl_cron_stock_report where product_id ='$pid' and snap_date='$yesterday'");
$set_bal = mysqli_fetch_array($open_bal, MYSQLI_ASSOC);
$opening_balance = $set_bal['balance'];
$my_add = mysqli_query($conn, "SELECT sum(new_stock) as new_stock, CONCAT_WS(' ',from_name) as from_name FROM tbl_india_update_stock WHERE product_id = $pid AND DATE(stock_date) = '$start_date'");
$set_add = mysqli_fetch_array($my_add, MYSQLI_ASSOC);
$added = $set_add['new_stock'];
if(isset($added)){
$added = $set_add['new_stock'];
$bool1 = $bool1 + 1;
}else{
$added = 0;
}
$get_refu = mysqli_query($conn, "SELECT ts.qty + ts.freeqty as qtys FROM tbl_confirmorder tb inner join tbl_order ts on tb.orid = ts.orderid where tb.orderstatus = 4 AND ts.productid='$pid' and ts.orderid = '$get_or' and DATE(tb.refund_time) = '$start_date'");
$check_rows_2 = mysqli_num_rows($get_refu);
if($check_rows_2 > 0){
$get_refund = mysqli_fetch_array($get_refu, MYSQLI_ASSOC);
$get_refund_count = $get_refund_count + $get_refund['qtys'];
}
if(!isset($get_refund_count)){
$get_refund_count = 0;
}
$get_packing = mysqli_query($conn, "SELECT sum(tb.qty + tb.freeqty) AS fqty FROM tbl_confirmorder tc INNER JOIN tbl_order tb ON tc.orderid = tb.orderid WHERE tc.orderstatus = 5 AND tc.varstatus!='Cash' and tc.userid!=7444 and tc.company_name='Energy Foods' and tb.productid='$pid'");
$pack_rows = mysqli_num_rows($get_packing);
if($pack_rows > 0){
$set_packing = mysqli_fetch_array($get_packing, MYSQLI_ASSOC);
$packing = $set_packing['fqty'];
}else{
$packing = 0;
}
$new = 0;
$pending = $packing + $new;
// echo ' | '.date('d-m-Y', strtotime($start_date)).' |
// |
// |
// '.$opening_balance.' |
// 0 | ';
// echo '';
// if($bool1 == 1){
// echo $added;
// }
// echo ' | ';
// echo ''.$get_refund_count.' |
// '.($opening_balance+$added).' |
// '.$pending.' |
// '.($opening_balance + $pending + $added).' |
//
';
}
}
$from_name = '';
//this part for the today
$start_date1 = date('Y-m-d');
$date1 = new DateTime($start_date1);
$yesterday1= $date1->modify("-1 days")->format('Y-m-d');
$disp_open = calc_open($pid, $yesterday1, $conn);
$arr = array();
$arr_1 = array();
$arr_2 = array();
?>
| DATE |
REF ID |
FROM |
OPEN |
SOLD |
ADDED |
RETURN |
BALANCE |
STATUS |
INSTOCK |
'.date('d-m-Y', strtotime($start_date1)).' |
|
|
'.$disp_open.' |
|
|
|
'.$disp_open.' |
|
'.($disp_open).' |
';
$get_add = mysqli_query($conn, "SELECT * FROM tbl_india_update_stock WHERE product_id = $pid AND DATE(stock_date) = '$start_date1'");
$check_add = mysqli_num_rows($get_add);
$not_show_open = 0;
$give_add = 0;
if($check_add > 0){
while($set_add = mysqli_fetch_array($get_add, MYSQLI_ASSOC)){
$not_show_open = $not_show_open + 1;
$give_add+=($set_add['new_stock'] + $disp_open);
$added = $set_add['new_stock'];
$from_name = $set_add['from_name'];
echo ' | '.date('d-m-Y', strtotime($start_date1)).' |
|
'.$from_name.' |
|
|
'.$added.' |
|
'.$give_add.' |
|
'.$give_add.' |
';
$disp_open = 0;
}
}
$get_orid = mysqli_query($conn,"Select * from tbl_confirmorder WHERE DATE_FORMAT(order_date_time, '%Y-%m-%d') = '$start_date1' AND refid NOT LIKE '%SYP%' and company_name not like 'Siddhayoga Pharma' and orderstatus not like '90'");
while($set_orid = mysqli_fetch_array($get_orid, MYSQLI_ASSOC))
{
$pattern1 = "/CO/i";
$pattern2 = "/Siddhayoga/i";
$pattern3 = "/Cash/i";
$pattern4 = "/HC/i";
$pattern5 = "/Energy/i";
array_push($arr, $set_orid['orderid']);
}
ksort($arr);
foreach($arr as $arrays){
$check_sy = mysqli_query($conn, "select * from tbl_confirmorder where refid like '%HC%' and company_name='Energy Foods' and userid='7444' and orderid='$arrays'");
$check_syp = mysqli_num_rows($check_sy);
if($check_syp == 0){
array_push($arr_2, $arrays);
}
}
foreach($arr_2 as $arr_zee){
$check_yv = mysqli_query($conn, "select * from tbl_order where orderid='$arr_zee'");
$yv_count = mysqli_num_rows($check_yv);
$check_yvs = mysqli_query($conn, "SELECT * FROM tbl_order td INNER JOIN shop_product sp on td.productid = sp.product_id WHERE td.orderid='$arr_zee' and sp.cloud_url<>'' ORDER BY td.ordid desc;");
$yvs_count = mysqli_num_rows($check_yvs);
if($yv_count != $yvs_count){
array_push($arr_1, $arr_zee);
}
}
ksort($arr_1);
$i=0;
$iter = count($arr_1);
foreach($arr_1 as $get_or){
$pending = 0;
$packing = 0;
$new = 0;
$check_if = mysqli_query($conn, "select * from tbl_order where orderid = '$get_or' and productid='$pid'");
$myrows = mysqli_num_rows($check_if);
if($myrows > 0){
$iter = $iter - 1;
$i=$i+1;
$get_refund_count =0;
$set_if = mysqli_fetch_array($check_if, MYSQLI_ASSOC);
$get_ref = mysqli_query($conn, "select * from tbl_confirmorder where orderid='$get_or'");
$set_ref = mysqli_fetch_array($get_ref, MYSQLI_ASSOC);
//added
$get_add = mysqli_query($conn, "SELECT * FROM tbl_india_update_stock WHERE product_id = $pid AND DATE(stock_date) = '$start_date1'");
$check_add = mysqli_num_rows($get_add);
if($check_add == 0){
$added = 0;
$from_name = '';
}else{
$my_add = mysqli_query($conn, "SELECT sum(new_stock) as new_stock, CONCAT_WS(' ',from_name) as from_name FROM tbl_india_update_stock WHERE product_id = $pid AND DATE(stock_date) = '$start_date1'");
$set_add = mysqli_fetch_array($my_add, MYSQLI_ASSOC);
$added = $set_add['new_stock'];
$from_name = $set_add['from_name'];
}
//opening balance
$open_bal = mysqli_query($conn, "select * from tbl_cron_stock_report where product_id ='$pid' and snap_date='$yesterday1'");
$set_bal = mysqli_fetch_array($open_bal, MYSQLI_ASSOC);
$opening_balance = $set_bal['balance'];
//refund count
$get_refu = mysqli_query($conn, "SELECT ts.qty + ts.freeqty as qtys FROM tbl_confirmorder tb inner join tbl_order ts on tb.orid = ts.orderid where tb.orderstatus = 4 AND ts.productid='$pid' and ts.orderid = '$get_or' and DATE(tb.refund_time) = '$start_date1'");
$check_rows_2 = mysqli_num_rows($get_refu);
if($check_rows_2 > 0){
$get_refund = mysqli_fetch_array($get_refu, MYSQLI_ASSOC);
$get_refund_count = $get_refund_count + $get_refund['qtys'];
}
if($i==1){
$balance = $opening_balance - ($set_if['qty'] + $set_if['freeqty']) + $added + $get_refund_count;
}else{
$balance = $balance - ($set_if['qty'] + $set_if['freeqty']) + $get_refund_count;
$opening_balance = '';
}
$get_packing = mysqli_query($conn, "SELECT sum(tb.qty + tb.freeqty) AS fqty FROM tbl_confirmorder tc INNER JOIN tbl_order tb ON tc.orderid = tb.orderid WHERE tc.orderstatus = 5 AND tc.varstatus!='Cash' and tc.userid!=7444 and tc.company_name='Energy Foods' and tb.productid='$pid' AND tc.refid='$set_ref[refid]'");
$pack_rows = mysqli_num_rows($get_packing);
if($pack_rows > 0){
$set_packing = mysqli_fetch_array($get_packing, MYSQLI_ASSOC);
$packing = $set_packing['fqty'];
}else{
$packing = 0;
}
$get_new = mysqli_query($conn, "SELECT sum(tb.qty + tb.freeqty) AS fqty FROM tbl_confirmorder tc INNER JOIN tbl_order tb ON tc.orderid = tb.orderid WHERE tc.orderstatus = 0 AND tc.varstatus!='Cash' and tc.userid!=7444 and tc.company_name='Energy Foods' and tb.productid='$pid'AND tc.refid='$set_ref[refid]'");
$new_rows = mysqli_num_rows($get_new);
if($new_rows > 0){
$set_new = mysqli_fetch_array($get_new, MYSQLI_ASSOC);
$new = $set_new['fqty'];
}else{
$new = 0;
}
$pending = $packing + $new;
if(!isset($pending)){
$pending = 0;
}
$instock = $balance + $pending;
echo ' | '.date('d-m-Y').' |
'.$set_ref['refid'].' |
| ';
echo '';
if($not_show_open > 0 || $myrows > 0){
echo '';
}else{
echo $opening_balance;
}
echo ' | ';
echo '
'.($set_if['qty'] + $set_if['freeqty']).' |
| ';
echo '';
if($get_refund_count == 0){
echo '';
}else{
echo $get_refund_count;
}
echo ' | ';
echo '
'.$balance.' | ';
echo '';
if(strpos($set_ref['refid'], 'CO') !== false){
echo 'SENT';
}elseif($pending == 0){
echo 'SENT';
}else{
echo $pending;
}
echo ' | ';
echo '
'.($balance + $set_if['qty'] + $set_if['freeqty']).' |
';
}
}
if(count($arr_1) == $iter){
$open_bal = mysqli_query($conn, "select * from tbl_cron_stock_report where product_id ='$pid' and snap_date='$yesterday1'");
$set_bal = mysqli_fetch_array($open_bal, MYSQLI_ASSOC);
$opening_balance = $set_bal['balance'];
$my_add = mysqli_query($conn, "SELECT sum(new_stock) as new_stock, CONCAT_WS(' ',from_name) as from_name FROM tbl_india_update_stock WHERE product_id = $pid AND DATE(stock_date) = '$start_date1'");
$set_add = mysqli_fetch_array($my_add, MYSQLI_ASSOC);
$added = $set_add['new_stock'];
$from_name = $set_add['from_name'];
if(isset($added)){
$added = $set_add['new_stock'];
}else{
$added = 0;
}
$get_refu = mysqli_query($conn, "SELECT ts.qty + ts.freeqty as qtys FROM tbl_confirmorder tb inner join tbl_order ts on tb.orid = ts.orderid where tb.orderstatus = 4 AND ts.productid='$pid' and ts.orderid = '$get_or' and DATE(tb.refund_time) = '$start_date1'");
$check_rows_2 = mysqli_num_rows($get_refu);
if($check_rows_2 > 0){
$get_refund = mysqli_fetch_array($get_refu, MYSQLI_ASSOC);
$get_refund_count = $get_refund_count + $get_refund['qtys'];
}
if(!isset($get_refund_count)){
$get_refund_count = 0;
}
$get_packing = mysqli_query($conn, "SELECT sum(tb.qty + tb.freeqty) AS fqty FROM tbl_confirmorder tc INNER JOIN tbl_order tb ON tc.orderid = tb.orderid WHERE tc.orderstatus = 5 AND tc.varstatus!='Cash' and tc.userid!=7444 and tc.company_name='Energy Foods' and tb.productid='$pid'");
$pack_rows = mysqli_num_rows($get_packing);
if($pack_rows > 0){
$set_packing = mysqli_fetch_array($get_packing, MYSQLI_ASSOC);
$packing = $set_packing['fqty'];
}else{
$packing = 0;
}
$new = 0;
$pending = $packing + $new;
// echo ' | '.date('d-m-Y', strtotime($start_date1)).' |
// |
// |
// '.$opening_balance.' |
// 0 |
// '.$added.' |
// '.$get_refund_count.' |
// '.($opening_balance+$added).' |
// '.$pending.' |
// '.($opening_balance + $pending + $added).' |
//
';
}
?>