ESH/upload_excel_procurement_script.php

<?php
include('includes/config/config.php');
include('includes/functions.php');
$response_array = array();
$response_mssages = "";
//  echo "shu".$Row[0];


$interm_proc_id = $_REQUEST['proc_id'];

error_log("interm procurement id final " . print_r($interm_proc_id, true));

for ($i = 0; $i < count($interm_proc_id); $i++) {

  error_log("interm procurement id final " . $interm_proc_id[$i]);

  if ($interm_proc_id[$i] != null || $interm_proc_id[$i] != '') {

    $data_q = "select a.invoice_no, a.procurement_refno,a.procurement_date,b.* from interm_procurement a left join interm_procurement_items b on a. interm_procurement_id=b.interm_procurement_id where a.interm_procurement_id='$interm_proc_id[$i]' and a.ohc_type_id = '" . $_SESSION['current_ohcttype'] . "' and b.ohc_type_id = '" . $_SESSION['current_ohcttype'] . "' ";

    error_log('query to get data from interim tables to finally upload in procurement tables ' . $data_q);
    $result_data_q = mysqli_query($conn, $data_q);
    while ($row_data_q = mysqli_fetch_array($result_data_q)) {


      $procurement_reference_no = $row_data_q['procurement_refno'];

      $invoice_no = $row_data_q['invoice_no'];

      $procurement_date = $row_data_q['procurement_date'];

      $item_code = getTableFieldValue('tbl_items', 'item_code', 'item_id', $row_data_q['item_id']);

      $quantity = $row_data_q['qty'];

      $net_value = $row_data_q['net_value'];

      $batch = $row_data_q['batch'];

      $expiry = $row_data_q['expiry'];

      $item_remarks = $row_data_q['remarks'];

      $per_unit_rate = $row_data_q['per_unit_rate'];

      $tax = $row_data_q['tax'];

      $ohc_id = $_SESSION['current_ohcttype'];

      error_log("data for one item " . $procurement_reference_no . "  date " . $procurement_date . "  item code " . $item_code . "  quantity " . $quantity . " batch " . $batch . "  expiry " . $expiry . " invoice no " . $invoice_no .  " per unit " . $per_unit_rate . " tax" . $tax);


      if (!empty($procurement_reference_no) && !empty($procurement_date) && !empty($item_code) && !empty($quantity) && !empty($batch)) {


        error_log("procurement_date:" . $procurement_date);
        error_log("expiry date " . $expiry);

        $init_query = "";
        $end_query = "";

        $procurement_id_sql = "select procurement_id from procurement where invoice_no='" . trim($invoice_no) . "' and ohc_type_id='" . $ohc_id . "'";

        error_log("query to get procurement id if it is present " . $procurement_id_sql);

        $result_id_sql = mysqli_query($conn, $procurement_id_sql);
        $row_id = mysqli_fetch_array($result_id_sql);
        $procurement_id = $row_id['procurement_id'];

        error_log("procurement id ::" . $procurement_id);


        if ($procurement_id != "" && $procurement_id != null && $procurement_id != 0) {
          $init_query = "update procurement set  ";
          $end_query = " where procurement_id = '" . $procurement_id . "' and ohc_type_id='" . $ohc_id . "'";
          $update_counter++;
        } else {
          $init_query = "insert into procurement set procurement_refno = '" . $procurement_reference_no . "', ohc_type_id='" . $ohc_id . "',";
          $end_query = "";
          $insert_counter++;
        }
        $query_procurement = $init_query . " invoice_no='" . trim($invoice_no) . "', procurement_date = STR_TO_DATE('" . $procurement_date . "','%Y-%m-%d'),modified_by='" . $_SESSION['user_id'] . "'" . $end_query;

        error_log("procurement  query::" . $query_procurement);
        $result1 = mysqli_query($conn, $query_procurement);

        if ($procurement_id == null or $procurement_id == "" or $procurement_id == 0) {
          $procurement_id = mysqli_insert_id($conn);
        }

        $item_id = getTableFieldValue('tbl_items', 'item_id', 'item_code', "'" . $item_code . "'");

        // to reset stock in previous qty before procurement
        if (!empty($procurement_id) && $procurement_id != 0) {

          $stock_qty = getTableFieldValue('item_stock', 'stock_qty', 'item_id', $item_id, 'item_batch_no', '"' . $batch . '"', 'ohc_type_id', $ohc_id);

          error_log("pre existig " . $stock_qty);

          $prev_qty  = getTableFieldValue('procurement_items', 'qty', 'item_id', $item_id, 'procurement_id', $procurement_id, 'batch', '"' . $batch . '"', 'ohc_type_id', $ohc_id);

          if ($stock_qty != null && $stock_qty != '' && $stock_qty != 0.00) {
            $current_stock_level_items_qty = $stock_qty - $prev_qty;
            $current_stock_query = "update item_stock  set stock_qty='" . $current_stock_level_items_qty . "' where item_id='" . $item_id . "' and item_batch_no='" . $batch . "' and ohc_type_id='" . $ohc_id . "' ";

            error_log("current_stock_query " . $current_stock_query);

            if (!@mysqli_query($conn, $current_stock_query)) {
              rollback();
              header('HTTP/1.1 500 Internal Server Error');
              echo json_encode(die('failed!' . mysqli_error($conn)));
            }
          }
        }


        $procurement_item_id = getTableFieldValue('procurement_items', 'procurement_item_id', 'item_id', $item_id, 'procurement_id', $procurement_id, 'batch', "'" . $batch . "'", 'ohc_type_id', $ohc_id);

        error_log("procurement item id " . $procurement_item_id);

        $final_item = "";
        $qty = 0.0;
        if ($procurement_item_id != null && $procurement_item_id = !"") {
          $query2 = " update procurement_items set qty='" . $quantity . "',net_value='" . $net_value . "' ,per_unit_rate='" . $per_unit_rate . "',tax ='" . $tax . "',expiry=STR_TO_DATE('" . $expiry . "','%Y-%m-%d'),modified_by='" . $_SESSION['user_id'] . "' where procurement_id='" . $procurement_id . "' and item_id='" . $item_id . "' and batch='" . $batch . "' and ohc_type_id='" . $ohc_id . "' ";
        } else {
          $query2 = " insert into procurement_items set procurement_id='" . $procurement_id . "',item_id='" . $item_id . "',qty='" . $quantity . "',net_value='" . $net_value . "',batch='" . $batch . "', ohc_type_id='" . $ohc_id . "',expiry=STR_TO_DATE('" . $expiry . "','%Y-%m-%d'),per_unit_rate='" . $per_unit_rate . "',tax ='" . $tax . "',modified_by='" . $_SESSION['user_id'] . "' ";
        }

        error_log("upload  procurement query ::" . $query2);

        $result2 = mysqli_query($conn, $query2);

        $stock_qty = getTableFieldValue('item_stock', 'stock_qty', 'item_id', $item_id, 'item_batch_no', "'" . $batch . "'", 'ohc_type_id', $ohc_id);

        error_log("stock before inserting/updating " . $stock_qty);

        $current_stock_query = "";

        if ($stock_qty != null && $stock_qty != "") {
          $stock_qty = $stock_qty + $quantity;

          error_log("stock while inserting/updating in if " . $stock_qty);

          $current_stock_query = "update item_stock  set stock_qty='" . $stock_qty . "' where item_id='" . $item_id . "' and item_batch_no='" . $batch . "' and ohc_type_id='" . $ohc_id . "'";
        } else {
          $stock_qty = $stock_qty + $quantity;
          error_log("stock while inserting/updating in else " . $stock_qty);
          $current_stock_query = "insert into item_stock set stock_qty='" . $stock_qty . "',item_id='" . $item_id . "',item_batch_no='" . $batch . "', ohc_type_id='" . $ohc_id . "',expiry_date=STR_TO_DATE('" . $expiry . "','%Y-%m-%d') ";
        }

        error_log("final query update for item quantity " . $current_stock_query);
        $result3 = mysqli_query($conn, $current_stock_query);

        if (!empty($result1)  &&  !empty($result2) && !empty($result3)) {
          $type = "success";
          //$message = "Excel Data Imported Into the Database";
        } else {
          $type = "error";
          //$message = "Problem In Importing Excel Data";
        }
      }
    }
  }
}


commit();

// remove interim entry from interim tables 
if (!$result_del_inter = mysqli_query($conn, "delete from interm_procurement where 1")) {
  error_log("something went wrong while deleting the interim data " . mysqli_error($conn));
}
if (!$result_del_inter_items = mysqli_query($conn, "delete from interm_procurement_items where 1")) {
  error_log("something went wrong while deleting the interim items data " . mysqli_error($conn));
}
echo json_encode($type);