<?php
ini_set('display_errors', 1);
/*

1. Initialize variables:
   - total_purchase_cost = 0
   - total_purchase_qty = 0
   - average_cost = 0
 
2. For each transaction (purchase) in chronological order:
   - Read transaction date, transaction type, quantity, unit price
 
   If transaction type is "purchase":
   	- Increment total_purchase_qty by quantity
   	- Increment total_purchase_cost by (quantity * unit price)
 
   Calculate average cost:
   - average_cost = total_purchase_cost / total_purchase_qty (if total_purchase_qty > 0)
 
3. Read specific date for closing stock calculation.
 
4. For each transaction (sale) on or before the specific date:
   - Read transaction date, transaction type, quantity, unit price
 
   If transaction type is "sales":
   	- Decrement total_purchase_qty by quantity
 
5. Calculate closing stock value on the specific date:
   - closing_stock_value = total_purchase_qty * average_cost
 
6. Display closing stock value on the specific date.

*/


function getAvgCpSummaryClosingStock($specific_db_conn, $mindate, $maxdate, $workspace_id, $company_id, $branch_id, $book_id)
{

   // this function also returns the non boolean + boolean stock summary closing and opening total

   // this function only gives one closing_stock_value for the maxdate for only inventory_applicable items
   $closing_stock = 0.00;

   $opening_stock = 0.00;

   // preparing basic queries 
   $sql_fetch_transactions_total_stock = "Select SUM(total_amount) as total_amount, type from inventory_stocktransaction where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and stock_id = ? and date <= ? group by type";
   $fetch_transactions_total_stock = $specific_db_conn->prepare($sql_fetch_transactions_total_stock);

   $sql_fetch_transactions_chronological = "Select SUM(qty) as qty, SUM(total_amount) as total_amount, type from inventory_stocktransaction where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and stock_id = ? and date <= ? group by type";
   $fetch_transactions_chronologial = $specific_db_conn->prepare($sql_fetch_transactions_chronological);

   $sql_fetch_stock_items = "Select id, opening_balance, boolean_inventory from inventory_stockdetails where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ?";
   $fetch_stock_items = $specific_db_conn->prepare($sql_fetch_stock_items);
   $fetch_stock_items->execute(array($workspace_id, $company_id, $branch_id, $book_id));
   $result_stock_items = $fetch_stock_items->fetchAll();
   if (count($result_stock_items) <= 0) {
      return array('status' => 1, 'closing_stock' => $closing_stock, 'opening_stock' => $opening_stock);
   }


   foreach ($result_stock_items as $row_stock_items) {
      $stock_id = $row_stock_items['id'];

      $opening_stock += $row_stock_items['opening_balance'];
      $closing_stock += $row_stock_items['opening_balance'];

      if ($row_stock_items['boolean_inventory'] == 0) {
         $fetch_transactions_total_stock->execute(array($workspace_id, $company_id, $branch_id, $book_id, $stock_id, $maxdate));
         $result_transactions_total_stock = $fetch_transactions_total_stock->fetchAll();
         foreach ($result_transactions_total_stock as $row_transactions_total_stock) {
            if ($row_transactions_total_stock['type'] == 'purchase') {
               $closing_stock += $row_transactions_total_stock['total_amount'];
            } else if ($row_transactions_total_stock['type'] == 'sales') {
               $closing_stock -= $row_transactions_total_stock['total_amount'];
            }
         }
      } else {
         // starting for inventory boolean 1 items
         $total_purchase_qty = 0.00; // initially set to 0 for each item
         $total_purchase_cost = 0.0; // initially set to 0 for each item

         $total_sales_qty = 0.00;
         $total_sales_amount = 0.00;

         $fetch_transactions_chronologial->execute(array($workspace_id, $company_id, $branch_id, $book_id, $stock_id, $maxdate));
         $result_transactions_chronological = $fetch_transactions_chronologial->fetchAll();
         foreach ($result_transactions_chronological as $row_transaction) {
            if ($row_transaction['type'] == 'purchase') {
               $total_purchase_qty += $row_transaction['qty'];
               $total_purchase_cost += $row_transaction['total_amount'];
            }
            if ($row_transaction['type'] == 'sales') {

               $total_sales_qty += $row_transaction['qty'];
               $total_sales_amount = $row_transaction['total_amount'];
            }
         }

         if ($total_purchase_qty == 0) {
            $closing_stock -= $total_sales_amount;
         } else {

            $average_cost = $total_purchase_cost / $total_purchase_qty;
            $closing_stock += ($total_purchase_qty - $total_sales_qty) * $average_cost;
         }
      }
   }
   return array('status' => 1, 'closing_stock' => $closing_stock, 'opening_stock' => $opening_stock);
}


function getAvgCpSummaryClosingStockSpecific($specific_db_conn, $mindate, $maxdate, $workspace_id, $company_id, $branch_id, $book_id, $items_array)
{
   // preparing basic queries 
   $sql_fetch_transactions_total_stock = "Select SUM(total_amount) as total_amount, type from inventory_stocktransaction where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and stock_id = ? and date <= ? group by type";
   $fetch_transactions_total_stock = $specific_db_conn->prepare($sql_fetch_transactions_total_stock);

   $sql_fetch_transactions_chronological = "Select SUM(qty) as qty, sum(total_amount) as total_amount, type from inventory_stocktransaction where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and stock_id = ? and date <= ? group by type";
   $fetch_transactions_chronologial = $specific_db_conn->prepare($sql_fetch_transactions_chronological);

   $sql_fetch_stock_items = "Select id, stock_name, opening_balance, opening_qty, boolean_inventory, unit_name from inventory_stockdetails where  id = ? and workspace_id = ? and company_id = ? and branch_id = ? and book_id = ?";
   $fetch_stock_items = $specific_db_conn->prepare($sql_fetch_stock_items);

   $stock_info = [];

   // this function only gives one closing_stock_value for the maxdate for only inventory_applicable items
   foreach ($items_array as $stock_id) {

      $fetch_stock_items->execute(array($stock_id, $workspace_id, $company_id, $branch_id, $book_id));
      $result_stock_items = $fetch_stock_items->fetchAll();
      foreach ($result_stock_items as $row_stock_items) {
         $closing_stock = 0.00;
         $opening_stock = 0.00;
         $opening_qty = 0;
         $closing_qty = 0;
         $average_cost = 0;

         $stock_name = $row_stock_items['stock_name'];
         $unit_name = $row_stock_items['unit_name'];

         $opening_stock += $row_stock_items['opening_balance'];
         $closing_stock += $row_stock_items['opening_balance'];


         if ($row_stock_items['boolean_inventory'] == 0) {

            $fetch_transactions_total_stock->execute(array($workspace_id, $company_id, $branch_id, $book_id, $stock_id, $maxdate));
            $result_transactions_total_stock = $fetch_transactions_total_stock->fetchAll();
            foreach ($result_transactions_total_stock as $row_transactions_total_stock) {
               if ($row_transactions_total_stock['type'] == 'purchase') {
                  $closing_stock += $row_transactions_total_stock['total_amount'];
               } else if ($row_transactions_total_stock['type'] == 'sales') {
                  $closing_stock -= $row_transactions_total_stock['total_amount'];
               }
            }
         } else {

            $opening_qty = $row_stock_items['opening_qty'];

            $total_purchase_cost = $opening_stock;
            $total_purchase_qty = $opening_qty;

            $total_sales_qty = 0.00;
            $total_sales_amount = 0.00;

            $fetch_transactions_chronologial->execute(array($workspace_id, $company_id, $branch_id, $book_id, $stock_id, $maxdate));
            $result_transactions_chronological = $fetch_transactions_chronologial->fetchAll();
            foreach ($result_transactions_chronological as $row_transaction) {
               if ($row_transaction['type'] == 'purchase') {
                  $total_purchase_qty += $row_transaction['qty'];
                  $total_purchase_cost += $row_transaction['total_amount'];
               }
               if ($row_transaction['type'] == 'sales') {
                  $total_sales_qty += $row_transaction['qty'];
                  $total_sales_amount = $row_transaction['total_amount'];
               }
            }

            if ($total_purchase_qty == 0) {
               $closing_stock -= $total_sales_amount;
               $closing_qty -= $total_sales_qty;
               if($total_sales_qty != 0 and $total_sales_amount != 0) {
                  $average_cost = $total_sales_amount / $total_sales_qty;
               } else {
                  $average_cost = 0;
               }
            } else {
               if($total_purchase_cost != 0 and $total_purchase_qty != 0) {
                  $average_cost = $total_purchase_cost / $total_purchase_qty;
               } else {
                  $average_cost = 0;
               }
               $average_cost = $total_purchase_cost / $total_purchase_qty;

               $closing_stock = ($total_purchase_qty - $total_sales_qty) * $average_cost;
               $closing_qty = $total_purchase_qty - $total_sales_qty;
            }
         }

         $stock_info[] = array(
            'id' => $stock_id,
            'stock_name' => $stock_name,
            'opening_stock' => $opening_stock,
            'closing_stock' => $closing_stock,
            'opening_qty' => $opening_qty,
            'closing_qty' => $closing_qty,
            'rate' => $average_cost,
            'unit_name' => $unit_name
         );
      }
   }
   return $stock_info;
}
