{"id":1482,"date":"2016-04-22T14:27:22","date_gmt":"2016-04-22T13:27:22","guid":{"rendered":"http:\/\/doc.atlantisevo.com\/?post_type=manual_documentation&p=1482"},"modified":"2021-01-28T09:43:47","modified_gmt":"2021-01-28T08:43:47","slug":"operazioni-preliminari","status":"publish","type":"manual_documentation","link":"https:\/\/www.atlantisevo.com\/doc\/manuale\/e-commerce-terzi\/virtuemart2\/operazioni-preliminari\/","title":{"rendered":"Operazioni preliminari"},"content":{"rendered":"
<\/div>

Creare le seguenti tabelle:<\/h4>\n\n\n\n
##
\n## Product
\n##CREATE TABLE IF NOT EXISTS `sync_prod` (
\n`id` int(11) NOT NULL AUTO_INCREMENT,
\n`op` char(1) NOT NULL,
\n`product_id` int(11) NOT NULL,
\n`product_sku` varchar(64) NOT NULL,
\nPRIMARY KEY (`id`),
\nUNIQUE KEY `sync_prod_unique` (`op`,`product_id`,`product_sku`)
\n) ENGINE=MyISAM\u00a0 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
##
\n## order item
\n##CREATE TABLE IF NOT EXISTS `sync_orditem` (
\n`id` int(11) NOT NULL AUTO_INCREMENT,
\n`op` char(1) NOT NULL,
\n`order_id` int(11) NOT NULL,
\nPRIMARY KEY (`id`),
\nUNIQUE KEY `sync_orditem_unique` (`op`,`order_id`)
\n) ENGINE=MyISAM\u00a0 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
##
\n## User
\n##CREATE TABLE IF NOT EXISTS `sync_user` (
\n`id` int(11) NOT NULL AUTO_INCREMENT,
\n`op` char(1) NOT NULL,
\n`user_id` int(11) NOT NULL,
\nPRIMARY KEY (`id`),
\nUNIQUE KEY `sync_user_unique` (`op`,`user_id`)
\n) ENGINE=MyISAM\u00a0 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Trigger da attivare su MySql per le tabelle appena create:<\/h4>\n\n\n\n
CREATE TRIGGER product_insert AFTER INSERT ON jos_vm_product
\nFOR EACH ROW
\nINSERT INTO sync_prod (op,product_id,product_sku) VALUES (‘I’,NEW.product_id,NEW.product_sku);CREATE TRIGGER product_delete AFTER DELETE ON jos_vm_product
\nFOR EACH ROW
\nINSERT INTO sync_prod (op,product_id,product_sku) VALUES (‘D’,OLD.product_id,OLD.product_sku);DELIMITER $$
\nCREATE TRIGGER product_update AFTER UPDATE ON jos_vm_product
\nFOR EACH ROW
\nBEGIN
\nIF NEW.product_in_stock=OLD.product_in_stock THEN
\nREPLACE INTO sync_prod (op,product_id,product_sku) VALUES (‘U’,NEW.product_id,NEW.product_sku);
\nEND IF;
\nEND$$
\nDELIMITER ;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
CREATE TRIGGER orderitem_insert AFTER INSERT ON jos_vm_order_item
\nFOR EACH ROW
\nINSERT INTO sync_orditem (op,order_id) VALUES (‘I’,NEW.order_id);CREATE TRIGGER orderitem_delete AFTER DELETE ON jos_vm_order_item
\nFOR EACH ROW
\nINSERT INTO sync_orditem (op,order_id) VALUES (‘D’,OLD.order_id);DELIMITER $$
\nCREATE TRIGGER orderitem_update AFTER UPDATE ON jos_vm_order_item
\nFOR EACH ROW
\nBEGIN
\nREPLACE INTO sync_orditem (op,order_id) VALUES (‘U’,NEW.order_id);
\nEND$$
\nDELIMITER ;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
CREATE TRIGGER user_insert AFTER INSERT ON jos_vm_user_info
\nFOR EACH ROW
\nINSERT INTO sync_user (op,user_id) VALUES (‘I’,NEW.user_id);CREATE TRIGGER user_delete AFTER DELETE ON jos_vm_user_info
\nFOR EACH ROW
\nINSERT INTO sync_user (op,user_id) VALUES (‘D’,OLD.user_id);DELIMITER $$
\nCREATE TRIGGER user_update AFTER UPDATE ON jos_vm_user_info
\nFOR EACH ROW
\nBEGIN
\nREPLACE INTO sync_user (op,user_id) VALUES (‘U’,NEW.user_id);
\nEND$$
\nDELIMITER ;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Esempio di sync_prod:<\/h4>\n\n\n\n\n\n\n
id<\/th>\nop<\/th>\nproduct_id<\/th>\nproduct_sku<\/th>\n<\/tr>\n
1<\/td>\nD<\/td>\n1<\/td>\nZZZ<\/td>\n<\/tr>\n
2<\/td>\nI<\/td>\n810<\/td>\nA011M<\/td>\n<\/tr>\n
3<\/td>\nI<\/td>\n811<\/td>\nP22<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Esempio di sync_orditem:<\/h4>\n\n\n\n\n\n\n
id<\/th>\nop<\/th>\norder_id<\/th>\n<\/tr>\n
1<\/td>\nI<\/td>\n1<\/td>\n<\/tr>\n
2<\/td>\nU<\/td>\n1<\/td>\n<\/tr>\n
3<\/td>\nD<\/td>\n1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Query per riempire \u00a0tabelle prima installazione:<\/h4>\n\n\n\n
INSERT INTO sync_user (op, user_id) SELECT ‘U’,ui.user_id FROM jos_vm_user_info ui,jos_users u,jos_vm_shopper_vendor_xref rf WHERE address_type =’BT’ AND ui.user_id=u.id AND rf.user_id=u.id GROUP BY ui.user_id
\nINSERT INTO sync_prod (op,product_id,product_sku) SELECT distinct ‘U’,product_id,product_sku from jos_vm_productINSERT INTO\u00a0sync_orditem (op,order_id) SELECT distinct ‘U’,order_id from\u00a0jos_vm_order_item
\noppure
\nINSERT INTO\u00a0sync_orditem (op,order_id) SELECT DISTINCT ‘U’, order_id FROM jos_vm_orders WHERE left( FROM_UNIXTIME( cdate ) , 4 ) = ‘2012’ ORDER BY order_id<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"author":1,"featured_media":0,"parent":1479,"menu_order":2,"template":"","manualdocumentationcategory":[55],"manual_doc_tag":[],"_links":{"self":[{"href":"https:\/\/www.atlantisevo.com\/doc\/wp-json\/wp\/v2\/manual_documentation\/1482"}],"collection":[{"href":"https:\/\/www.atlantisevo.com\/doc\/wp-json\/wp\/v2\/manual_documentation"}],"about":[{"href":"https:\/\/www.atlantisevo.com\/doc\/wp-json\/wp\/v2\/types\/manual_documentation"}],"author":[{"embeddable":true,"href":"https:\/\/www.atlantisevo.com\/doc\/wp-json\/wp\/v2\/users\/1"}],"version-history":[{"count":14,"href":"https:\/\/www.atlantisevo.com\/doc\/wp-json\/wp\/v2\/manual_documentation\/1482\/revisions"}],"predecessor-version":[{"id":1496,"href":"https:\/\/www.atlantisevo.com\/doc\/wp-json\/wp\/v2\/manual_documentation\/1482\/revisions\/1496"}],"up":[{"embeddable":true,"href":"https:\/\/www.atlantisevo.com\/doc\/wp-json\/wp\/v2\/manual_documentation\/1479"}],"wp:attachment":[{"href":"https:\/\/www.atlantisevo.com\/doc\/wp-json\/wp\/v2\/media?parent=1482"}],"wp:term":[{"taxonomy":"manualdocumentationcategory","embeddable":true,"href":"https:\/\/www.atlantisevo.com\/doc\/wp-json\/wp\/v2\/manualdocumentationcategory?post=1482"},{"taxonomy":"manual_doc_tag","embeddable":true,"href":"https:\/\/www.atlantisevo.com\/doc\/wp-json\/wp\/v2\/manual_doc_tag?post=1482"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}