getActiveSheet();
$data = [];
?>
query("suppliers", "SELECT * FROM suppliers WHERE `name` = '$supplier_name'");
if ($res->num_rows > 0) {
$data = $res->fetch_assoc();
return $data['record_id'];
} else {
// create supplier
return $db->query("suppliers", "INSERT INTO suppliers (`name`) VALUES ('$supplier_name')");
}
}
function check_stock($stock_name, $item_code, $supplier_id, $cost_price)
{
$stock_name = str_replace("'", "", $stock_name);
$db = new db_safeguard();
$stock_type = check_stock_type($item_code);
// check if desctiption exists
$res = $db->query("stock", "SELECT * FROM stock WHERE description = '$stock_name'");
if ($res->num_rows > 0) {
$data = $res->fetch_assoc();
echo "ITEM EXISTS UPDATED STOCK TYPE";
$db->query("stock", "UPDATE stock SET stock_type = $stock_type WHERE record_id = {$data['record_id']}");
return $data['record_id'];
} else {
echo "ITEM CREATED";
// create that stock item
return $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`) VALUES (-1,$supplier_id,'$stock_name','',$cost_price,0,0,0,'',$stock_type,'$item_code')");
}
}
function check_stock_type($item_code)
{
$db = new db_safeguard();
$res = $db->query("item_code_alocation", "SELECT * FROM item_code_alocation WHERE 1");
while ($row = $res->fetch_assoc()) {
if (strpos($item_code, (string) $row['code']) !== false) {
echo "
" . $row['code'] . " : " . $row['stock_type'] . " : " . $item_code . "
";
return $row['stock_type'];
}
}
// if (strpos($item_code, "UNIT") !== false || strpos($item_code, "UNT") !== false) {
// $stock_type = 2;
// echo "
UNITS
";
// } else if (strpos($item_code, "FAN") !== false || strpos($item_code, "MT C") !== false || strpos($item_code, "SPARES") !== false || strpos($item_code, "DUCT") !== false || strpos($item_code, "SILPOD") !== false || strpos($item_code, "HIT") !== false || strpos($item_code, "TB") !== false || strpos($item_code, "TD") !== false|| strpos($item_code, "GRA") !== false || strpos($item_code, "HXM") !== false || strpos($item_code, "FEET") !== false || strpos($item_code, "FLAN") !== false || strpos($item_code, "OTHER") !== false || strpos($item_code, "moun") !== false || strpos($item_code, "TECSPA") !== false|| strpos($item_code, "MOUN01") !== false) {
// echo "
NON STOCK
";
// $stock_type = 1;
// } else {
// echo "
STOCK
";
// $stock_type = 0;
// }
echo "
STOCK : " . $item_code . "
";
return 0;
}
foreach ($worksheet->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$rowIndex = $row->getRowIndex();
if ($rowIndex >= 1 && $rowIndex <= 6) {
continue;
}
$data[$rowIndex] = [];
foreach ($cellIterator as $cell) {
$data[$rowIndex][$cell->getColumn()] = $cell->getValue();
}
}
function remove_special_char($string)
{
$string = preg_replace('/\s+/u', ' ', trim($string));
$string = str_replace('Ø', 'O', trim($string));
$string = str_replace(array("\r", "\n"), '', $string);
$string = str_replace("'", "", trim($string));
return $string;
}
function replace_negative($string)
{
return str_replace('-', '', $string);
}
$index = 0;
foreach ($data as $index => $row) {
if ($row["A"] === null) {
echo "IN LOOP BY PASS
";
continue;
}
echo "OUT OF BY PASS
";
echo "OUT OF BY PASS";
if (!is_int($data[$index]["A"]) && strpos($data[$index]["A"], 'Total') === false && $data[$index]["A"] != "Invoice Date") {
$po_number = $data[$index]["A"];
// this is where the index goes out to get all po data
$item_index_amount = $index + 1;
$array_index = 0;
while ($data[$item_index_amount]["A"] > 1) {
if (strpos($data[$item_index_amount]["A"], "Total")) {
echo "TOTAL FOUND BREAKING
";
break;
}
if ($data[$item_index_amount]["F"] != "" || $data[$item_index_amount]["F"] != Null) {
$date = excelDateToDate($data[$item_index_amount]["A"]);
echo $supplier_name = $data[$item_index_amount]["B"];
$reference = $data[$item_index_amount]["D"];
$items_array[$array_index]["item_code"] = $data[$item_index_amount]["E"];
$items_array[$array_index]["description"] = remove_special_char($data[$item_index_amount]["F"]);
$items_array[$array_index]["quantity"] = replace_negative($data[$item_index_amount]["G"]);
if ($items_array[$array_index]["description"] == "PROMO DISCOUNT") {
$items_array[$array_index]["price"] = $data[$item_index_amount]["H"];
} else {
$items_array[$array_index]["price"] = replace_negative($data[$item_index_amount]["H"]) * 1;
}
}
$array_index++;
$item_index_amount++;
}
var_dump($items_array);
echo $po_number . "
";
echo " supplier: " . $supplier_name . "
";
echo " date: " . $date . "
";
echo " reference: " . (isset($reference) && $reference != "" ? $reference : 0) . "
";
$supplier_id = (isset($supplier_name) && $supplier_name != "") ? supplier_check($supplier_name) : 0;
// check if PO exists
$po_res = $db->query("orders", "SELECT * FROM orders WHERE po_number = '$po_number'");
if ($po_res->num_rows > 0) {
$po_data = $po_res->fetch_assoc();
$order_id = $po_data['record_id'];
echo "ORDER EXISTS
";
} else {
$order_sql = "INSERT INTO `orders`(`po_number`, `jc_number`, `date_time_created`, `date_time_recieved`, `supplier_id`, `status`, `user_id`, `job_card_account_id`, `warrenty`, `user_created`, `reference`) VALUES ('$po_number','','$date',0,'$supplier_id',0,'{$_SESSION['user_id']}',0,0,'{$_SESSION['user_id']}','$reference')";
echo $order_sql . "
";
$order_id = $db->query("orders", $order_sql);
}
// delete all order_items from order_table where this order_exists
// echo "DELETE FROM order_items WHERE order_id = $order_id";
$db->query("order_items", "DELETE FROM order_items WHERE order_id = $order_id");
echo "
ITEMS
";
foreach ($items_array as $item) {
if (strlen($item['item_code']) > 2) {
$stock_id = check_stock($item['description'], $item['item_code'], $supplier_id, 0);
$stock_type = check_stock_type($item['item_code']);
echo "
" . $item['item_code'] . "
";
// get item multilication adjustment
$item_res_stock = $db->query("stock", "SELECT * FROM stock WHERE record_id = $stock_id");
$item_data_stock = $item_res_stock->fetch_assoc();
$operator = $item_data_stock['adjustment_type'];
$amount_adjsuted = $item_data_stock['amount'];
if (strtoupper($operator) == "X") {
$quantity = round($item['quantity'] * $amount_adjsuted, 3);
$price = round($item['price'] / $amount_adjsuted, 3);
}
if (strtoupper($operator) == "/") {
$quantity = round($item['quantity'] / $amount_adjsuted, 3);
$price = round($item['price'] * $amount_adjsuted, 3);
}
$order_item_sql = "INSERT INTO `order_items`(`name`, `stock_id`, `quantity`, `purchase_price_ex`, `order_id`, `user_id_created`, `user_id_received`, `stock_type`) VALUES ('{$item['description']}',$stock_id,$quantity,$price,$order_id,{$_SESSION['user_id']},0,$stock_type)";
echo $order_item_sql . "
";
$db->query("order_items", $order_item_sql);
} else {
echo "
SKIPPED NO ITEM CODE : ".$item['description'].' CODE : '. $item['item_code'];
}
echo "
";
}
$items_array = [];
echo "
";
}
$index++;
}
// echo "";