<?php
session_start();
include('../accesscheck2.php');
require_once('../Connections/courier.php');
require_once '../functions/functions.php';
require_once(dirname(__FILE__) . "/../assets/PHPExcel/Classes/PHPExcel.php");
$column = array(
    "A" => 1,
    "B" => 2,
    "C" => 3,
    "D" => 4,
    "E" => 5,
    "F" => 6,
    "G" => 7,
    "H" => 8,
    "I" => 9,
    "J" => 10,
    "K" => 11,
    "L" => 12,
    "M" => 13,
    "N" => 14,
    "O" => 15,
    "P" => 16,
    "Q" => 17,
    "R" => 18,
    "S" => 19,
    "T" => 20,
    "U" => 21,
    "V" => 22,
    "W" => 23,
    "X" => 24,
    "Y" => 25,
    "Z" => 26,
    "AA" => 27,
    "AB" => 28,
    "AC" => 29,
    "AD" => 30,
    "AE" => 31,
    "AF" => 32,
    "AG" => 33,
    "AH" => 34,
    "AI" => 35,
    "AJ" => 36,
    "AK" => 37,
    "AL" => 38,
    "AM" => 39,
    "AN" => 40,
    "AO" => 41,
    "AP" => 42,
    "AQ" => 43,
    "AR" => 44,
    "AS" => 45,
    "AT" => 46,
    "AU" => 47,
    "AV" => 48,
    "AW" => 49,
    "AX" => 50,
    "AY" => 51,
    "AZ" => 52
);

$lowestRow = $_POST['start_row'];
$highestRow = $_POST['end_row'];
$sheet_no = 1;
$item_code = $column[strtoupper($_POST['item_code'])] - 1;
$item_name = $column[strtoupper($_POST['item_name'])] - 1;
$item_category = $column[strtoupper($_POST['item_category'])] - 1;
$item_price = $column[strtoupper($_POST['item_price'])] - 1;
$min_re_order_qty = $column[strtoupper($_POST['min_re_order_qty'])] - 1;
$re_order_qty = $column[strtoupper($_POST['re_order_qty'])] - 1;
$qty_ordered = $column[strtoupper($_POST['qty_ordered'])] - 1;

$store_id = mysql_fetch_assoc(mysql_query("SELECT id FROM branches WHERE type='store'"));

if (isset($_POST) && ($_SERVER['REQUEST_METHOD'] == "POST")) {
    $name = $_FILES['importfile_items']['name'];
    if (strlen($name)) {
        $tmp = $_FILES['importfile_items']['tmp_name'];
        $path_info = pathinfo($name);
        $ext = $path_info['extension'];
        $valid_formats = array("xlsx", "xls");
        if (in_array(strtolower($ext), $valid_formats)) {
            $inputFileName = $tmp;
            $objPHPExcel = new PHPExcel();
            try {
                $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
                $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                $objPHPExcel = $objReader->load($inputFileName);
            } catch (Exception $e) {
                die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME)
                        . '": ' . $e->getMessage());
            }
            $sheet = $objPHPExcel->getSheet($sheet_no - 1);
            $highestColumn = $sheet->getHighestColumn();
            for ($row = $lowestRow; $row <= $highestRow; $row++) {
                $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
                $item_code_import = empty($rowData[0][$item_code]) ? "" : $rowData[0][$item_code];
                $item_name_import = empty($rowData[0][$item_name]) ? "" : $rowData[0][$item_name];
                $item_category_import = empty($rowData[0][$item_category]) ? "" : $rowData[0][$item_category];
                $item_price_import = empty($rowData[0][$item_price]) ? "" : $rowData[0][$item_price];
                $min_re_order_qty_import = empty($rowData[0][$min_re_order_qty]) ? "" : $rowData[0][$min_re_order_qty];
                $re_order_qty_import = empty($rowData[0][$re_order_qty]) ? "" : $rowData[0][$re_order_qty];
                $qty_ordered_import = empty($rowData[0][$qty_ordered]) ? "" : $rowData[0][$qty_ordered];

                //CHECK IF ITEM EXIST
                if ($qty_ordered_import > 0) {
                    $check = mysql_query("SELECT * FROM stock_items WHERE id='$item_code_import'");
                    if (mysql_num_rows($check) > 0) {
                        $row = mysql_fetch_assoc($check);
                        $quantity_available = $row['quantity_available'] + $qty_ordered_import;
                        mysql_query("UPDATE stock_items SET rx_item_name='$item_name_import',item='$item_name_import',service='$item_category_import',price='$item_price_import',quantity_available='$quantity_available',min_re_order_qty='$min_re_order_qty_import',re_order_qty='$re_order_qty_import' WHERE id='$item_code_import'");
                    } else {
                        mysql_query("INSERT INTO stock_items (rx_item_name,item,service,price,quantity_available,min_re_order_qty,re_order_qty,provider_id,provider_id_master,branch_id) VALUES('$item_name_import','$item_name_import','$item_category_import','$item_price_import','$qty_ordered_import','$min_re_order_qty_import','$re_order_qty_import','" . $_SESSION['provider_id'] . "','" . $_SESSION['provider_id'] . "','".$store_id['id']."')");
                        mysql_query("UPDATE stock_items SET item_id = '" . mysql_insert_id() . "' WHERE id = '" . mysql_insert_id() . "'");
                    }
                }
            }

            header("Location:../");
        } else {
            echo "failed";
        }
    } else {
        echo "Please select Excel File..!";
    }
}