<?
set_time_limit(0);
function get_stock($pid, $conn){

            //this part is for the previous dates
            $get_prod = mysqli_query($conn, "select * from shop_product where product_id = '$pid'");
            $set_prod = mysqli_fetch_array($get_prod, MYSQLI_ASSOC);
            $med_name = $set_prod['name'].' '.$set_prod['product_url'];
            $get_open = mysqli_query($conn, "select * from tbl_cron_stock_report where product_id='$pid' and snap_date='2023-12-19'");
            $set_open = mysqli_fetch_array($get_open, MYSQLI_ASSOC);
            $first_date = '2023-12-19';
            $your_date = strtotime("1 day", strtotime($first_date));
            $second_date = date("Y-m-d", $your_date);
            $open = $set_open['balance'];

$startDate = new \DateTime($second_date);
$endDate = new \DateTime();

$interval = new \DateInterval('P1D');
$period = new \DatePeriod ($startDate, $interval, $endDate);

$dates = array();
foreach ($period as $key) {
    array_push($dates,$key->format('Y-m-d'));
}

         foreach ($dates as $date_arr){
            $arr = array();
$arr_1 = array();
$arr_2 = array();
$added = 0;
$returned = 0;
            //set new stock values first
            $get_new_stock = mysqli_query($conn, "select * from tbl_india_update_stock where product_id = '$pid' and DATE(stock_date)='$date_arr'");

            while($set_new_stock = mysqli_fetch_array($get_new_stock, MYSQLI_ASSOC)){
                $added+=$set_new_stock['new_stock'];
                
              
            }
            $get_return = mysqli_query($conn, "select * from tbl_record_returned where product_id = '$pid' and DATE(date_of_return)='$date_arr'");
          
            
             while($set_return = mysqli_fetch_array($get_return, MYSQLI_ASSOC)){
                 $returned+=$set_return['returned_qty'];
               
             }
             $get_revision = mysqli_query($conn, "select * from tbl_record_revised where product_id = '$pid' and DATE(date_of_revision)='$date_arr'");
          
            
             while($set_revision = mysqli_fetch_array($get_revision, MYSQLI_ASSOC)){
                 $revised+=$set_revision['revised_qty'];

                 
               
             }
         
                
$get_orid = mysqli_query($conn,"Select * from tbl_confirmorder WHERE DATE_FORMAT(order_date_time, '%Y-%m-%d') = '$date_arr' 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);
$open=$open+$added+$returned+$revised;
foreach($arr_1 as $get_or){

$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){
    $set_if = mysqli_fetch_array($check_if, MYSQLI_ASSOC);
    $sold = $set_if['qty'] + $set_if['freeqty'];
    $get_ref = mysqli_query($conn, "select * from tbl_confirmorder where orderid='$get_or'");
    $set_ref = mysqli_fetch_array($get_ref, MYSQLI_ASSOC);
    $status='';
    $pending = 0;
    $open = $open - $sold;

    //unpacked orders check

    $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;
    $pending_mas+=($packing + $new);
		
		if(!isset($pending)){
			$pending = 0;
		}
    if(strpos($set_ref['refid'], 'CO') !== false){
        $status= 'SENT';
    }elseif($pending == 0){
        $status= 'SENT';
    }else{
        $status = $sold;
    }
    if($status=='SENT'){
        $instock = $open+$pending_mas;
    }else{
        $instock = $open+$pending_mas;
    }

}
$arr_methane = array("balance"=>$open, "instock"=>$instock);
}

         }
//          $arr = array();
//          $arr_1 = array();
//          $arr_2 = array();
//          $sold1 = 0;

//  //calculate the sold items today
//  $get_orid = mysqli_query($conn,"Select * from tbl_confirmorder WHERE DATE_FORMAT(order_date_time, '%Y-%m-%d') = '".date('Y-m-d')."' 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);
//  foreach($arr_1 as $get_or){
 
//  $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){
//      $set_if = mysqli_fetch_array($check_if, MYSQLI_ASSOC);
//      $sold1+= $set_if['qty'] + $set_if['freeqty'];    
//  }
// }
       
         return $arr_methane;
}
         ?>