<?php

include "../root.class.php";

$db = new db_safeguard();

require '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'stock_data.xlsx';

$spreadsheet = IOFactory::load($inputFileName);
$worksheet = $spreadsheet->getActiveSheet();
$data = [];

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);

    $rowData = [];

    if ($row->getRowIndex() === 1) {
        continue;
    }
    foreach ($cellIterator as $cell) {
        $rowData[] = preg_replace('/[^A-Za-z0-9\-\/]/', ' ', $cell->getValue());
    }
    var_dump($rowData);
    echo "<br>";
    echo "<br>";
    // get category id
    $category_res = $db->query("stock_categories", "SELECT `record_id` FROM `stock_categories` WHERE `name` = '{$rowData[1]}'");
    if ($category_res->num_rows > 0) {
        $category_id = $category_res->fetch_assoc()['record_id'];
    }else{
        $category_id = $db->query("stock_categories","INSERT INTO stock_categories (`name`) VALUES ('{$rowData[0]}')");
    }


    // get supplier id
    $supplier_res = $db->query("suppliers", "SELECT `record_id` FROM `suppliers` WHERE `name` = '{$rowData[10]}'");
    if ($supplier_res->num_rows > 0) {
        $supplier_id = $supplier_res->fetch_assoc()['record_id'];
    }else{
        $supplier_id = $db->query("suppliers","INSERT INTO suppliers (`name`) VALUES ('{$rowData[10]}')");
    }


    $description = $rowData[2] ? $rowData[2] : '';
    $packaging_size = $rowData[3] ? $rowData[3] : '';
    $cost_price = $rowData[7] ? $rowData[7] : 0;
    $stock_type = $rowData[12] ? $rowData[12] : 0;
    $item_code = $rowData[0] ? $rowData[0] : 0;
    $max_stock = $rowData[8] ? $rowData[8] : 0;
    $min_stock = $rowData[9] ? $rowData[9] : 0;
    $multiply = $rowData[5] ? $rowData[5] : 0;
    $multiply_by = $rowData[6] ? $rowData[6] : 0;
    $adjustment_type = '';
    $amount = '';
    $db->query("stock", "INSERT INTO `stock`(`category_id`, `supplier_id`, `description`, `packaging_size`, `cost_price`, `max`, `min`, `unit_type_id`, `barcode`, `stock_type`, `item_code`, `adjustment_type`, `amount`) VALUES ($category_id,$supplier_id,'$description','$packaging_size', $cost_price,$max_stock,$min_stock,0,0,$stock_type,'$item_code','$multiply','$multiply_by')");
}
