<?php
require("admin/include/defs.php");
require("admin/include/dbconnect.php");
try {
$db = new CDatabase();
ini_set('memory_limit', '1024M');
$ScriptName = basename($_SERVER['PHP_SELF']);
echo "\r\n\r\nWorking with CatItems NDC (Ambient, Bond, Chill) at " . date('d/m/Y : H:i:s', time()) . "\n";
#####################################################################
## Start validation for:
## File: All_Active_Lines_BWG_Extract_RW.csv
## Table: CatItemsNDC_Staging
$FileName = $eOrderFolder . "All_Active_Lines_BWG_Extract_RW.csv";
$StagingTableName = "CatItemsNDC_Staging";
$IsError = ValidateImportFile($ScriptName, $FileName, $StagingTableName);
#####################################################################
if (!$IsError) {
$items = file($FileName);
echo "Started...\n";
$count = 0;
// Clear the staging table and reset auto-increment
$sql = "DELETE FROM $StagingTableName";
$db->query($sql);
$sql = "ALTER TABLE $StagingTableName AUTO_INCREMENT = 1";
$db->query($sql);
// Load data from the CSV file without headers into the staging table
// Removing the following fields: Keyword1, Keyword2, Keyword3, value_projection, IsChillFruitVeg, p_country, and all drs_* fields (DRSProduct, DRSCharge, DRSContainerSize, DRSContainerUnits)
$query = "LOAD DATA LOCAL INFILE '$FileName' INTO TABLE $StagingTableName
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\\n'
(Code, depot, Name, Description, EAN1, EAN2, EAN3, EAN4, EAN5, ComGroup, Price, SupplierID, Carrier, SupplierCode, SupplierProductCode, CaseSize, active, RRP, Units, OwnBrand, Vatrate, HierCode, StockRange, WSP1, WSP2, WSP3, WSP4, WSP5, RRP1, RRP2, RRP3, RRP4, RRP5, RetailUnits, nett_cost, p_cater, hand_type, p_split, NewListing, Brand, UnitMeasures, TUC1, TUC2, TUC3, TUC4, TUC5, @var)
SET DRSContainerUnits = TRIM(TRAILING '\r' FROM @var);";
$db->query($query);
// Retrieve distinct depots from the staging table
$query = "SELECT DISTINCT d.depot_id, d.depot_name
FROM $StagingTableName c
JOIN Depots d ON c.depot = d.depot_ID
WHERE d.depot_name <> 'Preorder'";
$depots_to_update = $db->getData($query);
// Adding ChillFruitVeg depot manually, if needed
$chill_fruit_veg = array("depot_id" => 25, "depot_name" => "ChillFruitVeg");
array_push($depots_to_update, $chill_fruit_veg);
// Loop through each depot and process the data
foreach ($depots_to_update as $depot) {
$current_depot_id = $depot['depot_id'];
$current_depot_name = $depot['depot_name'];
$chill_split_filter = "";
echo "\r\n\r\nUpdating products for depot: $current_depot_id ($current_depot_name)\r\n";
if ($current_depot_id == 25) {
if ($current_depot_name == "Chill") {
$chill_split_filter = " AND IsChillFruitVeg <> 'y' ";
} else if ($current_depot_name == "ChillFruitVeg") {
$chill_split_filter = " AND IsChillFruitVeg = 'y' ";
}
}
# Get count of active items before the update
$countBefore = $db->getData("SELECT COUNT(*) as c FROM CatItems$current_depot_name WHERE active = 'y'")[0]['c'];
WriteImportJobLog($ScriptName, $FileName, $StagingTableName, $IsError, "Count of active products in CatItems$current_depot_name before import: $countBefore.");
#### Update Existing Items ###
$updateQuery = "UPDATE CatItems$current_depot_name c
INNER JOIN $StagingTableName c1
ON c.Code = c1.Code
SET c.Name = c1.Name,
c.Description = c1.Description,
c.EAN1 = c1.EAN1,
c.EAN2 = c1.EAN2,
c.EAN3 = c1.EAN3,
c.EAN4 = c1.EAN4,
c.EAN5 = c1.EAN5,
c.ComGroup = c1.ComGroup,
c.Price = c1.Price,
c.SupplierID = c1.SupplierID,
c.Carrier = c1.Carrier,
c.SupplierCode = c1.SupplierCode,
c.SupplierProductCode = c1.SupplierProductCode,
c.CaseSize = c1.CaseSize,
c.active = c1.active,
c.RRP = c1.RRP,
c.Units = c1.Units,
c.OwnBrand = c1.OwnBrand,
c.Vatrate = c1.Vatrate,
c.HierCode = c1.HierCode,
c.StockRange = c1.StockRange,
c.WSP1 = c1.WSP1, c.WSP2 = c1.WSP2, c.WSP3 = c1.WSP3, c.WSP4 = c1.WSP4, c.WSP5 = c1.WSP5,
c.RRP1 = c1.RRP1, c.RRP2 = c1.RRP2, c.RRP3 = c1.RRP3, c.RRP4 = c1.RRP4, c.RRP5 = c1.RRP5,
c.RetailUnits = c1.RetailUnits,
c.nett_cost = c1.nett_cost,
c.p_cater = c1.p_cater,
c.hand_type = c1.hand_type,
c.p_split = c1.p_split,
c.NewListing = c1.NewListing,
c.Brand = c1.Brand,
c.UnitMeasures = c1.UnitMeasures,
c.TUC1 = c1.TUC1, c.TUC2 = c1.TUC2, c.TUC3 = c1.TUC3, c.TUC4 = c1.TUC4, c.TUC5 = c1.TUC5
WHERE c1.depot = '$current_depot_id';";
echo "\r\nUpdate existing: \r\n$updateQuery\r\n";
$db->query($updateQuery);
#### Insert New Items ####
$insertQuery = "INSERT INTO CatItems$current_depot_name
(Code, Name, Description, EAN1, EAN2, EAN3, EAN4, EAN5, ComGroup, Price, SupplierID, Carrier, SupplierCode, SupplierProductCode, CaseSize, active, RRP, Units, OwnBrand, Vatrate, HierCode, StockRange, WSP1, WSP2, WSP3, WSP4, WSP5, RRP1, RRP2, RRP3, RRP4, RRP5, RetailUnits, nett_cost, p_cater, hand_type, p_split, NewListing, Brand, UnitMeasures, TUC1, TUC2, TUC3, TUC4, TUC5)
SELECT Code, Name, Description, EAN1, EAN2, EAN3, EAN4, EAN5, ComGroup, Price, SupplierID, Carrier, SupplierCode, SupplierProductCode, CaseSize, active, RRP, Units, OwnBrand, Vatrate, HierCode, StockRange, WSP1, WSP2, WSP3, WSP4, WSP5, RRP1, RRP2, RRP3, RRP4, RRP5, RetailUnits, nett_cost, p_cater, hand_type, p_split, NewListing, Brand, UnitMeasures, TUC1, TUC2, TUC3, TUC4, TUC5
FROM $StagingTableName
WHERE depot = '$current_depot_id'
AND Code NOT IN (SELECT Code FROM CatItems$current_depot_name);";
echo "\r\nInsert new: \r\n$insertQuery\r\n";
$db->query($insertQuery);
#### Deactivate Products ####
$deactivateQuery = "UPDATE CatItems$current_depot_name
SET active = 'n'
WHERE Code NOT IN (SELECT Code FROM $StagingTableName WHERE depot = '$current_depot_id' $chill_split_filter );";
echo "\r\nDeactivate products: \