<?php
include('includes/config/config.php');
include('includes/functions.php');
//$conn = mysqli_connect("localhost","root","","phpsamples");
require_once('excel/vendor/php-excel-reader/excel_reader2.php');
require_once('excel/vendor/SpreadsheetReader.php');

$inter_procurement_id = array();

$response_array = array();
$response_mssages = "";

begin();


$allowedFileType = ['application/vnd.ms-excel', 'text/xls', 'text/xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];

if (in_array($_FILES["file"]["type"], $allowedFileType)) {

  $targetPath = 'excel/uploads/' . $_FILES['file']['name'];
  move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

  $Reader = new SpreadsheetReader($targetPath);

  $sheetCount = count($Reader->sheets());

  for ($i = 0; $i < 1; $i++) {

    $Reader->ChangeSheet($i);
    $j = 0;
    foreach ($Reader as $Row) {
      if ($j == 0) {
        $j = 1;
        continue;
      }

      error_log("CURRENT ROW-> " . print_r($Row, true));

      error_log("again here");

      $procurement_reference_no = ""; // it will go  in invoice no field now we are auto generating procurement_ref_no
      if (isset($Row[0])) {
        $procurement_reference_no = mysqli_real_escape_string($conn, $Row[0]);
      }

      $procurement_date = ""; // this will go in invoice date column 
      if (isset($Row[1])) {
        $procurement_date = getDateInDbFormat(mysqli_real_escape_string($conn, $Row[1]));
      }
      $item_code = "";
      if (isset($Row[2])) {
        $item_code = mysqli_real_escape_string($conn, $Row[2]);
      }
      $quantity = 0;
      if (isset($Row[3])) {
        $quantity = mysqli_real_escape_string($conn, $Row[3]);
      }
      $net_value = "";
      if (isset($Row[4])) {
        $net_value = mysqli_real_escape_string($conn, $Row[4]);
      }
      $batch = "";
      if (isset($Row[5])) {
        $batch = mysqli_real_escape_string($conn, $Row[5]);
      }

      $per_unit_rate = "";
      if (isset($Row[6])) {
        $per_unit_rate = mysqli_real_escape_string($conn, $Row[6]);
      }

      $tax = "";
      if (isset($Row[7])) {
        $tax = mysqli_real_escape_string($conn, $Row[7]);
      }

      $expiry = "";
      if (isset($Row[8])) {
        $expiry = getDateInDbFormat(mysqli_real_escape_string($conn, $Row[8]));
      }

      $item_remarks = "";
      if (isset($Row[9])) {
        $item_remarks = mysqli_real_escape_string($conn, $Row[9]);
      }

      $ohc_id = $_SESSION['current_ohcttype'];

      $per_unit_rate_calculated = ($net_value / $quantity);

      $per_unit_rate = number_format($per_unit_rate_calculated, 2, '.', '');

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

        // check if this invoice exists in interim table or not


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

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

        $exists_sql = "select * from interm_procurement where invoice_no='" . trim($procurement_reference_no) . "' and ohc_type_id='" . $ohc_id . "'";

        $result_exist = mysqli_query($conn, $exists_sql);
        if (mysqli_num_rows($result_exist) > 0) {
          $row_exist = mysqli_fetch_assoc($result_exist);
          $proc_ref_no = $row_exist['procurement_refno'];

          $init_query = "update interm_procurement set ";
          $end_query = " where interm_procurement_id = '" . $row_exist['interm_procurement_id'] . "' and ohc_type_id='" . $ohc_id . "'";
        } else {
          $proc_ref_no = creatingProcurementNo();

          $init_query = "insert into interm_procurement set procurement_refno='" . $proc_ref_no . "',ohc_type_id='" . $ohc_id . "',invoice_no='" . $procurement_reference_no . "',";
          $end_query = "";
        }

        $query_procurement = $init_query . " 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);


        $result_id = mysqli_query($conn, "select max(interm_procurement_id) as pid from interm_procurement");

        $procurement_id_row = mysqli_fetch_assoc($result_id);
        $procurement_id = $procurement_id_row['pid'];

        error_log("inserted id " . $procurement_id);

        array_push($inter_procurement_id, $procurement_id);

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

        $item_exist_sql = "select * from interm_procurement_items where item_id = '" . $item_id . "' and interm_procurement_id='" . $procurement_id . "' and batch = '" . $batch . "'";

        error_log("get item query " . $item_exist_sql);

        $result_item_sql = mysqli_query($conn, $item_exist_sql);


        if (mysqli_num_rows($result_item_sql) > 0) {
          // if its already there than delete that entry and add new one updated entry 
          $del_query = "delete from interm_procurement_items where item_id = '" . $item_id . "' and interm_procurement_id='" . $procurement_id . "' and batch = '" . $batch . "' ";

          error_log("delete item query " . $del_query);

          if (!$result_del = mysqli_query($conn, $del_query)) {
            error_log("delete item query with error " . $del_query . "  error " . mysqli_error($conn));
          }
        }

        $query2 = " insert into interm_procurement_items set interm_procurement_id='" . $procurement_id . "',item_id='" . $item_id . "',qty='" . $quantity . "',net_value='" . $net_value . "',batch='" . $batch . "',per_unit_rate ='" . $per_unit_rate . "', tax = '" . $tax . "', ohc_type_id='" . $ohc_id . "',expiry=STR_TO_DATE('" . $expiry . "','%Y-%m-%d')";

        error_log("insert query for procurement items " . $query2);
        $result2 = mysqli_query($conn, $query2);
      }
    }
  }
}
$type = '';
if (!empty($result1)  &&  !empty($result2)) {
  $ids = array_unique($inter_procurement_id);
  error_log('iidd' . max(array_keys($ids)) . 'iidd' . print_r($ids, true));
  for ($i = 0; $i <= max(array_keys($ids)); $i++) {

    if ($ids[$i] != null || $ids[$i] != '' || $ids[$i] != 0) {
      $type .= $ids[$i] . ',';
    }
  }
  //$message = "Excel Data Imported Into the Database";
} else {
  $type = "error";
  //$message = "Problem In Importing Excel Data";
}
commit();

$encrypted_data = base64_encode($ids[0]);
error_log('data ====' . $encrypted_data . " original data " . $ids[0]);

echo json_encode($encrypted_data);