Get Serious CRM. Professional products, articles and focus from the top providers.

12/14/2012

Opencart > Vtiger - SyncMyShop - Part 3 - Trigger Products

Here's the code to make a trigger on the products:

/*Cascata*/
CREATE TRIGGER `3-01_oc_product_split` BEFORE INSERT ON `oc_product`
 FOR EACH ROW UPDATE oc_product_trigger SET
product_id = NEW.product_id,
model = NEW.model,
sku = NEW.sku,
upc = NEW.upc,
location = NEW.location,
quantity = NEW.quantity,
stock_status_id = NEW.stock_status_id,
image = NEW.image,
manufacturer_id = NEW.manufacturer_id,
shipping = NEW.shipping,
price = NEW.price,
points = NEW.points,
tax_class_id = NEW.tax_class_id,
date_available = NEW.date_available,
weight = NEW.weight,
weight_class_id = NEW.weight_class_id,
length = NEW.length,
width = NEW.width,
height = NEW.height,
length_class_id = NEW.length_class_id,
subtract = NEW.subtract,
minimum = NEW.minimum,
sort_order = NEW.sort_order,
status = NEW.status,
date_added = NEW.date_added,
date_modified = NEW.date_modified,
viewed = NEW.viewed

/*Cascata*/
CREATE TRIGGER `3-02_oc_product_split_2` AFTER INSERT ON `oc_product`
 FOR EACH ROW UPDATE oc_product_trigger_2 SET
product_id = NEW.product_id,
model = NEW.model,
sku = NEW.sku,
upc = NEW.upc,
location = NEW.location,
quantity = NEW.quantity,
stock_status_id = NEW.stock_status_id,
image = NEW.image,
manufacturer_id = NEW.manufacturer_id,
shipping = NEW.shipping,
price = NEW.price,
points = NEW.points,
tax_class_id = NEW.tax_class_id,
date_available = NEW.date_available,
weight = NEW.weight,
weight_class_id = NEW.weight_class_id,
length = NEW.length,
width = NEW.width,
height = NEW.height,
length_class_id = NEW.length_class_id,
subtract = NEW.subtract,
minimum = NEW.minimum,
sort_order = NEW.sort_order,
status = NEW.status,
date_added = NEW.date_added,
date_modified = NEW.date_modified,
viewed = NEW.viewed

/* Aggiorna tabella delle entità CRM */
CREATE TRIGGER `3-03_oc_product_trigger_to_vtiger_entity` BEFORE UPDATE ON `oc_product_trigger`
 FOR EACH ROW INSERT INTO `vtiger_crmentity` (
`crmid`,
`smcreatorid`,
`smownerid`,
`modifiedby`,
`setype`,
`description`,
`createdtime`,
`modifiedtime`,
`viewedtime`,
`status`,
`version`,
`presence`,
`deleted`
)
VALUES
((SELECT id FROM vtiger_crmentity_seq LIMIT 0,1)+1,'1','1','1','Products','Product from shop',NOW(),NOW(),NOW(),NULL,'0','1','0')

/* Aggiorna la tabella prodotti*/
CREATE TRIGGER `3-04_oc_product_to_vt_product` AFTER UPDATE ON `oc_product_trigger`
 FOR EACH ROW INSERT INTO `vtiger_products` (
productid,
product_no,
productname,
productcode,
productcategory,
manufacturer,
qty_per_unit,
unit_price,
weight,
pack_size,
sales_start_date,
sales_end_date,
start_date,
expiry_date,
cost_factor,
commissionrate,
commissionmethod,
discontinued,
usageunit,
reorderlevel,
website,
taxclass,
mfr_part_no,
vendor_part_no,
serialno,
qtyinstock,
productsheet,
qtyindemand,
glacct,
vendor_id,
imagename,
currency_id
)
VALUES
(
(SELECT id FROM vtiger_crmentity_seq LIMIT 0,1)+1,
CONCAT((SELECT `prefix` FROM `vtiger_modentity_num` WHERE `semodule` = "Products" AND `active` = 1),(SELECT `cur_id` FROM `vtiger_modentity_num` WHERE `semodule` = "Products" AND `active` = 1 LIMIT 0,1)),
'producttempname',
NEW.sku,
'Select category',
(SELECT name FROM oc_manufacturer WHERE manufacturer_id = NEW.manufacturer_id LIMIT 0,1) ,
NEW.minimum,
NEW.price,
NEW.weight,
'',
NEW.date_available,
NULL,
NEW.date_available,
NULL,
NULL,
'0.0000',
'0',
'1',
NULL,
NULL,
NULL,
'',
'',
'',
NEW.upc,
NEW.quantity,
'',
0,
'',
0,
'',
(SELECT `id` FROM `vtiger_currency_info` WHERE `currency_code` = (SELECT `value` FROM `oc_setting` WHERE `key` = "config_currency" LIMIT 0,1))
)

/* Riporta i dati aggiuntivi di Opencart */
CREATE TRIGGER `3-05_update_product_data` BEFORE UPDATE ON `oc_product`
 FOR EACH ROW UPDATE vtiger_products 
SET
productcode=NEW.sku,
manufacturer=(SELECT name FROM oc_manufacturer WHERE manufacturer_id = NEW.manufacturer_id) ,
qty_per_unit=NEW.minimum,
unit_price=NEW.price,
weight=NEW.weight,
sales_start_date=NEW.date_available,
start_date=NEW.date_available,
qtyinstock=NEW.quantity

WHERE productid = (SELECT vt FROM syncmyshop WHERE oc = NEW.product_id AND type = 'PRO')

/*Cascata*/
CREATE TRIGGER `3-06_oc_product_split_3` BEFORE UPDATE ON `oc_product_trigger_2`
 FOR EACH ROW UPDATE oc_product_trigger_3 SET
product_id = NEW.product_id,
model = NEW.model,
sku = NEW.sku,
upc = NEW.upc,
location = NEW.location,
quantity = NEW.quantity,
stock_status_id = NEW.stock_status_id,
image = NEW.image,
manufacturer_id = NEW.manufacturer_id,
shipping = NEW.shipping,
price = NEW.price,
points = NEW.points,
tax_class_id = NEW.tax_class_id,
date_available = NEW.date_available,
weight = NEW.weight,
weight_class_id = NEW.weight_class_id,
length = NEW.length,
width = NEW.width,
height = NEW.height,
length_class_id = NEW.length_class_id,
subtract = NEW.subtract,
minimum = NEW.minimum,
sort_order = NEW.sort_order,
status = NEW.status,
date_added = NEW.date_added,
date_modified = NEW.date_modified,
viewed = NEW.viewed

/*Aggiorna sequenza entità CRM*/
CREATE TRIGGER `3-07_update_vtiger_crm_entity_seq` AFTER UPDATE ON `oc_product_trigger_2`
 FOR EACH ROW UPDATE vtiger_crmentity_seq SET id = id + 1

/*Aggiorna la descrizione del prodotto inserita in maniera temporanea*/ CREATE TRIGGER `3-08_oc_product_desc_to_vtiger_product` BEFORE INSERT ON `oc_product_description`
 FOR EACH ROW UPDATE vtiger_products SET productname = NEW.name WHERE productid = (SELECT vt FROM syncmyshop WHERE oc = NEW.product_id AND type = "PRO" LIMIT 0,1)

/*Aggiorna progressivo prodotti*/ CREATE TRIGGER `3-09_update_product_scf` AFTER INSERT ON `oc_product_description`
 FOR EACH ROW BEGIN
IF ((SELECT productid FROM `vtiger_productcf` ORDER BY productid DESC LIMIT 0,1) < (SELECT vt FROM syncmyshop WHERE oc = NEW.product_id AND type = "PRO" LIMIT 0,1)) THEN
INSERT INTO vtiger_productcf (productid) VALUES ((SELECT vt from syncmyshop WHERE oc = NEW.product_id AND type = "PRO" LIMIT 0,1));
END IF;
END

/*Aggiorna tabella Sync My Shop */
CREATE TRIGGER `3-10_Update_SyncMyShop` BEFORE UPDATE ON `oc_product_trigger_3`
 FOR EACH ROW INSERT INTO `syncmyshop` (
`oc`,
`vt`,
`type`)
VALUES
(NEW.product_id,(SELECT id FROM vtiger_crmentity_seq)+1,"PRO")

/*Aggiorna entità modulo*/
CREATE TRIGGER `3-11_Update_product_mod_entity` AFTER UPDATE ON `oc_product_trigger_3`
 FOR EACH ROW UPDATE  vtiger_modentity_num SET  cur_id =  cur_id +1 WHERE semodule =  "Products" AND  active =1


This script will copy each Opencart product on CRM, and will update it, if changes are made, but not vice versa.

In our opinion it is important to separate the two operations, this is because the elimination of a product of vTiger "abruptly" can cause many problems..

In the next script will analyze how to manage the creation of companies and contacts.

0 commenti :

Posta un commento