Tranfer category
INSERT into magnus_aip_shoppee.aip_sh_product_category(`parent_category_id`,`category_name`,`category_name_lang`,`image`,`old_id`,`old_parent_category_id`)
SELECT 0,cat.category_name1,cat.category_name2,IF(cat.image='' or cat.image is null,'img',cat.image) as image,cat.id,cat.parent_category_id FROM aipsof_salla.aip_product_category cat INNER JOIN aipsof_salla.aip_product p ON p.product_category_id = cat.id INNER JOIN aipsof_salla.aip_product_unit pu ON pu.product_id = p.id WHERE pu.rsp_with_tax > 0 GROUP by cat.id
INSERT into magnus_aip_shoppee.aip_sh_product_category(`parent_category_id`,`category_name`,`category_name_lang`,`image`,`old_id`,`old_parent_category_id`)
SELECT 0,cat.category_name1,cat.category_name2,IF(cat.image='' or cat.image is null,'img',cat.image)as image,cat.id,cat.parent_category_id FROM aipsof_salla.aip_product_category cat INNER JOIN aipsof_salla.aip_product p ON p.product_category_id2 = cat.id INNER JOIN aipsof_salla.aip_product_unit pu ON pu.product_id = p.id WHERE pu.rsp_with_tax > 0 GROUP by cat.id
Update Parent Category
UPDATE aip_sh_product_category TPU
INNER JOIN aip_sh_product_category PU ON TPU.old_parent_category_id = PU.old_id
SET TPU.parent_category_id = PU.id
WHERE TPU.old_parent_category_id != 0 && TPU.inserted = 0
Transfer Products
SELECT `aip_product`.`product_name1_long`, `aip_product`.`product_name2`, IF(`aip_product`.`image`='' or `aip_product`.`image` IS NULL,'img',`aip_product`.`image`) as image, 0, 0, aip_product.id, `aip_product`.`product_category_id`, `aip_product`.`product_category_id2`, `aip_product_unit`.`rsp_with_tax` FROM `aipsof_salla`.`aip_product` INNER JOIN `aipsof_salla`.`aip_product_unit` ON ( `aip_product`.`id` = `aip_product_unit`.`product_id` ) WHERE `aip_product_unit`.`rsp_with_tax` > 0 GROUP BY aip_product.id
Insert products to shop, change shop id
INSERT INTO `aip_sh_shop_to_product` (`shop_id`, `product_id`)
SELECT 2, id FROM `aip_sh_product` WHERE inserted = 0
Updates Category to products
UPDATE
aip_sh_product P
INNER JOIN aip_sh_product_category C ON
P.old_category_id = C.old_id
SET
P.cat_id = C.id
WHERE
P.inserted = 0
Updates Sub-Category to products
UPDATE
aip_sh_product P
INNER JOIN aip_sh_product_category C ON
P.old_sub_category_id = C.old_parent_category_id
SET
P.sub_cat_id = C.id
WHERE
P.inserted = 0
Assign category to the shop, change shop id
INSERT INTO `aip_sh_cat_to_shop` (`id`, `cat_id`, `shop_id`, `app_id`, `image`)
SELECT NULL, aip_sh_product_category.id, 2, 1, 'img' FROM aip_sh_product_category WHERE aip_sh_product_category.inserted = 0
Assign price to the shop, change shop id
INSERT INTO aip_sh_price( app_id, shop_id, product_id, unit_id, price, currency_id )
SELECT 1, 2, id, 1, old_price, 1 FROM aip_sh_product WHERE inserted = 0
Assign images to the shop
UPDATE `aip_sh_product` SET `product_img` = CONCAT('https://magnus.aipsoft.com/userdatas/clients/salla/product_images/small/',product_img) WHERE inserted = 0 AND product_img IS NOT NULL
For Laundry -- ## insert all laundry product unit to temp database ##--
INSERT INTO `temp_product_unit_price_db` (`id`, `main_db_product_id`, `main_db_unit_id`, `price`)
SELECT NULL, aip_product_unit.product_id, aip_product_unit.unit_id, aip_product_unit.rsp_with_tax FROM aipsof_bayad.aip_product_unit
//update as per the unit id from main db and shopping db change value for each
UPDATE `temp_product_unit_price_db` SET `this_db_option_id` = '1' WHERE main_db_unit_id = 1;
UPDATE `temp_product_unit_price_db` SET `this_db_option_id` = '2' WHERE main_db_unit_id = 2;
UPDATE `temp_product_unit_price_db` SET `this_db_option_id` = '3' WHERE main_db_unit_id = 3;
//insert deafult checked item to options, change shop id and unit id on 3 places
INSERT INTO `aip_sh_option_to_shop_category` (`id`, `shop_id`, `category_id`, `option_id`, `product_id`, `default_checked`, `display_rate`, `rate`)
SELECT NULL, '1', '1', TEMP.this_db_option_id, TEMP.this_db_product_id, '1', TEMP.price, TEMP.price-PPR.price FROM temp_product_unit_price_db TEMP
INNER JOIN aip_sh_price PPR ON (TEMP.this_db_product_id = PPR.product_id AND PPR.shop_id = '1') WHERE TEMP.this_db_option_id = 1
//insert not un checked item to options, change shop id and unit id on 3 places
INSERT INTO `aip_sh_option_to_shop_category` (`id`, `shop_id`, `category_id`, `option_id`, `product_id`, `default_checked`, `display_rate`, `rate`)
SELECT NULL, '1', '1', TEMP.this_db_option_id, TEMP.this_db_product_id, '0', TEMP.price, TEMP.price-PPR.price FROM temp_product_unit_price_db TEMP
INNER JOIN aip_sh_price PPR ON (TEMP.this_db_product_id = PPR.product_id AND PPR.shop_id = '1') WHERE TEMP.this_db_option_id != 1