<?php
$page_title = 'Draft Plan';
require_once('includes/load.php');
require_once('draftplan_load1.php');

// Checkin What level user has permission to view this page
page_require_level(2);
$products = join_salesplan_table();

$insertadd = 0;
$trigbom = selectbomtocreate();

if (!empty($trigbom)) {
  $PlanId = $trigbom[0]['PlanId'];
  $unitid = $trigbom[0]['UnitId'];
  $article = $trigbom[0]['Father'];

  $spdetails = salesplandetailsforarticle($PlanId, $unitid, $article);

  $plan = find_by_columnnew('plandetails', $PlanId, 'PlanId');
  $planName = isset($plan['PlanName']) ? $plan['PlanName'] : '';

  $unit = find_by_columnnew('unitdetails', $unitid, 'UnitId');
  $unitname = isset($unit['UnitName']) ? $unit['UnitName'] : '';
 
  $db1 = getDatabaseConnection();
  $query = "SELECT * FROM draftplandetails WHERE ArticleSAPCode='" . $article . "'";
  $avlqtyarray = $db1->query($query);
 
  foreach ($avlqtyarray as $avl) {
    $avlQty = 0;
    $db1 = getDatabaseConnection();
    $stmt = $db1->prepare("CALL GetMaterialDetailsByArticleCode(?, ?)");
    $stmt->bind_param("ss", $avl['SAPCode'], $unitname);
    $stmt->execute();
    $result = $stmt->get_result();
    $sapdatafetchavl = $result->fetch_assoc();
    $stmt->close();
    $db1->close();
   
    if (isset($sapdatafetchavl['avlstock'])) {
      $avlQty = $sapdatafetchavl['avlstock'];
    } 
   
    $db1 = getDatabaseConnection();
    $stmt = $db1->prepare("CALL UpdateDraftPlanDetailsAvlStock(?, ?, ?, ?)");
    $stmt->bind_param("iisd", $PlanId, $unitid, $avl['SAPCode'], $avlQty);
    $stmt->execute();
    $stmt->close();
    $db1->close();

  }

  //intermediatestagecalculation($PlanId, $unitid, $article,$unitname);
    
   foreach($avlqtyarray as $avl1) {
    $allocQty = 0;
    $db1 = getDatabaseConnection();
    $stmt = $db1->prepare("CALL GetAllocatedQty(?, ?)");
    $stmt->bind_param("si", $avl1['SAPCode'], $unitid);
    $stmt->execute();
    $result = $stmt->get_result();
    $allocdatafetch = $result->fetch_assoc();
    $stmt->close();
    $db1->close();

    if (isset($allocdatafetch['AllocQty'])) {
      $allocQty = $allocdatafetch['AllocQty'];
    } 

    $db1 = getDatabaseConnection();
    $stmt = $db1->prepare("CALL UpdateDraftPlanDetailsAllocStock(?, ?, ?, ?)");
    $stmt->bind_param("iisd", $PlanId, $unitid, $avl1['SAPCode'], $allocQty);
    $stmt->execute();
    $stmt->close();
    $db1->close();
    
    $POQty = 0;
    $db1 = getDatabaseConnection();
    $stmt = $db1->prepare("CALL GetPOdetails(?, ?)");
    $stmt->bind_param("ss", $avl1['SAPCode'], $unitname);
    $stmt->execute();
    $result = $stmt->get_result();
    $poqtydatafetch = $result->fetch_assoc();
    $stmt->close();
    $db1->close();
    
    if (isset($poqtydatafetch['postock'])) {
      $POQty = $poqtydatafetch['postock'];
    } 
    $db1 = getDatabaseConnection();
    $stmt = $db1->prepare("CALL UpdateDraftPlanDetailsPOStock(?, ?, ?, ?)");
    $stmt->bind_param("iisd", $PlanId, $unitid, $avl1['SAPCode'], $POQty);
    $stmt->execute();
    $stmt->close();
    $db1->close();

    $allocPOQty = 0;
    $db1 = getDatabaseConnection();
    $stmt = $db1->prepare("CALL GetAllocatedPOQty(?, ?)");
    $stmt->bind_param("si", $avl1['SAPCode'], $unitid);
    $stmt->execute();
    $result = $stmt->get_result();
    $allocpodatafetch = $result->fetch_assoc();
    $stmt->close();
    $db1->close();

    if (isset($allocpodatafetch['AllocPOQty'])) {
      $allocPOQty = $allocpodatafetch['AllocPOQty'];
    } 

    $db1 = getDatabaseConnection();
    $stmt = $db1->prepare("CALL UpdateDraftPlanDetailsAllocPOStock(?, ?, ?, ?)");
    $stmt->bind_param("iisd", $PlanId, $unitid, $avl1['SAPCode'], $allocPOQty);
    $stmt->execute();
    $stmt->close();
    $db1->close();
    
  }
  
}

function getDatabaseConnection() {
  $mysqli = new mysqli("localhost", "root", "", "vishnum1_materials_requirement_managementfull");
  if ($mysqli->connect_errno) {
    die("Database connection failed: " . $mysqli->connect_error);
  }
  return $mysqli;
}