<?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'] : '';
  //$bomrow="yes";
  $sql = "CALL rowmaterialcalculation('{$article}', {$spdetails[0]['CaseCount']}, '{$unitname}')";
  $bomrow = $db->query($sql);

  $inserted = 0;
  if (!empty($bomrow)) {
    $db1 = getDatabaseConnection();

    $stmt = $db1->prepare("CALL InsertDraftPlanDetails(?, ?, ?, ?, ?)");

    foreach ($bomrow as $bomr) {
      $stmt->bind_param("isiss", $PlanId, $article, $unitid, $bomr['child'], $bomr['ReqQty']);
      if ($stmt->execute()) {
        $inserted++;
      } else {
        printf("Error: %s\n", $stmt->error);
      }
    }

    $stmt->close();
    $db1->close();
  }

  echo "Inserted $inserted rows into draftplandetails.";
 
  $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);

  if ($inserted > 0) {
    $db1 = getDatabaseConnection();
    $stmt = $db1->prepare("CALL UpdateFinishedBOMs(?, ?)");
    $stmt->bind_param("ii", $PlanId, $unitid);
    $stmt->execute();
    $stmt->close();
    $db1->close();
  }
}

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