*************

SQL code: Upload Copy Format Clear

CREATE TABLE CatItemsNDC_Staging ( Code VARCHAR(255), depot INT, Name VARCHAR(255), Description VARCHAR(255), EAN1 VARCHAR(255), EAN2 VARCHAR(255), EAN3 VARCHAR(255), EAN4 VARCHAR(255), EAN5 VARCHAR(255), ComGroup VARCHAR(255), Price DECIMAL(10, 2), SupplierID INT, Carrier VARCHAR(255), SupplierCode VARCHAR(255), SupplierProductCode VARCHAR(255), CaseSize INT, active CHAR(1), RRP DECIMAL(10, 2), Units INT, OwnBrand CHAR(1), Vatrate DECIMAL(4, 2), HierCode VARCHAR(255), StockRange VARCHAR(255), WSP1 DECIMAL(10, 2), WSP2 DECIMAL(10, 2), WSP3 DECIMAL(10, 2), WSP4 DECIMAL(10, 2), WSP5 DECIMAL(10, 2), RRP1 DECIMAL(10, 2), RRP2 DECIMAL(10, 2), RRP3 DECIMAL(10, 2), RRP4 DECIMAL(10, 2), RRP5 DECIMAL(10, 2), RetailUnits INT, nett_cost DECIMAL(10, 2), p_cater VARCHAR(255), hand_type VARCHAR(255), p_split CHAR(1), NewListing CHAR(1), Brand VARCHAR(255), UnitMeasures VARCHAR(255), TUC1 DECIMAL(10, 2), TUC2 DECIMAL(10, 2), TUC3 DECIMAL(10, 2), TUC4 DECIMAL(10, 2), TUC5 DECIMAL(10, 2), DRSContainerUnits VARCHAR(255) ); CREATE TABLE Depots ( depot_ID INT AUTO_INCREMENT PRIMARY KEY, depot_name VARCHAR(255) ); CREATE TABLE CatItemsDepotName ( Code VARCHAR(255), Name VARCHAR(255), Description VARCHAR(255), EAN1 VARCHAR(255), EAN2 VARCHAR(255), EAN3 VARCHAR(255), EAN4 VARCHAR(255), EAN5 VARCHAR(255), ComGroup VARCHAR(255), Price DECIMAL(10, 2), SupplierID INT, Carrier VARCHAR(255), SupplierCode VARCHAR(255), SupplierProductCode VARCHAR(255), CaseSize INT, active CHAR(1), RRP DECIMAL(10, 2), Units INT, OwnBrand CHAR(1), Vatrate DECIMAL(4, 2), HierCode VARCHAR(255), StockRange VARCHAR(255), WSP1 DECIMAL(10, 2), WSP2 DECIMAL(10, 2), WSP3 DECIMAL(10, 2), WSP4 DECIMAL(10, 2), WSP5 DECIMAL(10, 2), RRP1 DECIMAL(10, 2), RRP2 DECIMAL(10, 2), RRP3 DECIMAL(10, 2), RRP4 DECIMAL(10, 2), RRP5 DECIMAL(10, 2), RetailUnits INT, nett_cost DECIMAL(10, 2), p_cater VARCHAR(255), hand_type VARCHAR(255), p_split CHAR(1), NewListing CHAR(1), Brand VARCHAR(255), UnitMeasures VARCHAR(255), TUC1 DECIMAL(10, 2), TUC2 DECIMAL(10, 2), TUC3 DECIMAL(10, 2), TUC4 DECIMAL(10, 2), TUC5 DECIMAL(10, 2) );

SQL Server: Preserve result

Result: Copy Clear

PHP code: Copy Format Clear

<?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: \

PHP version:

Show: Warnings Notice

Result: Copy Clear

Like the site? Share it!

Have a questions? Ask in our telegram chat!

PHP versions: PHP 5.6, PHP 7.4, PHP 8.0, PHP 8.1, PHP 8.2.13
RDBMS versions: MariaDB 11.4, MariaDB 11.5, MariaDB 10, MariaDB 10 Sakila (ReadOnly), MySQL 5.7, MySQL 5.7 Sakila (ReadOnly), MySQL 8.0, MySQL 8.0 Sakila (ReadOnly), SQLite 3, SQLite 3 Preloaded, PostgreSQL 10 Bookings (ReadOnly), PostgreSQL 11, PostgreSQL 12, PostgreSQL 13, PostgreSQL 14, PostgreSQL 15, MS SQL Server 2017, MS SQL Server 2019, MS SQL Server 2022, MS SQL Server 2022 AdventureWorks (ReadOnly), Firebird 4.0, Firebird 4.0 (Employee), Oracle Database 19c (HR), Oracle Database 21c, Oracle Database 23c Free, SOQOL

PHPize Online / SQLize Online / SQLtest Online