DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetItemBranches`(__ITEMID INT)
BEGIN
	SELECT
		*
	FROM
		itemsBranchesLink
	WHERE
		ITEMID = __ITEMID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetItemCategories` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetItemCategories`(__ITEMID INT)
BEGIN
	SELECT
		*
	FROM
		itemsSubcategoryLink
	WHERE
		ITEMID = __ITEMID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetItemData` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetItemData`(__ITEMID INT)
BEGIN
	SELECT * FROM items WHERE ID = __ITEMID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetItemDataByConfirmCode` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetItemDataByConfirmCode`(__CODE VARCHAR(200))
BEGIN
	SELECT * FROM items WHERE APPROVALLINK = __CODE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetItemPricing` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetItemPricing`(__ITEMID INT)
BEGIN


	SELECT
		*,
        ROUND((LENGTH*WIDTH*HEIGHT)/172 * PIECES,0) AS DIMWEIGHT
	FROM
		itemPricing
	WHERE
		ITEMID = __ITEMID
	ORDER BY 
		CASE WHEN QTY = -2 THEN 9999 ELSE QTY END;  
        
        
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetItemsBySearchTerm` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetItemsBySearchTerm`(__TERM VARCHAR(50))
BEGIN

SELECT
	ID,
    CONCAT(CODE, ' - ', DESCRIPTION) AS ITEMNAME
FROM
	items
WHERE
	(
	CODE LIKE CONCAT('%',__TERM,'%') OR 
    DESCRIPTION LIKE CONCAT('%',__TERM,'%')
    ) AND
    TYPE NOT IN (3)
ORDER BY 
	CODE,DESCRIPTION
LIMIT 20;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetItemZonePricing` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetItemZonePricing`(__ITEMID INT, __QTY INT, __BRANCH VARCHAR(20), __ITEMOPTION VARCHAR(30))
BEGIN

 SET @BRANCHZONE := (SELECT SHIPZONE FROM branches WHERE CODE = __BRANCH);
 SET @ITEMWEIGHT := (SELECT CASE WHEN ROUND((LENGTH*WIDTH*HEIGHT)/172 * PIECES,0) > WEIGHT THEN ROUND((LENGTH*WIDTH*HEIGHT)/172 * PIECES,0) ELSE WEIGHT END AS WEIGHT FROM itemPricing WHERE ITEMID = __ITEMID AND (QTY = __QTY OR QTY = -1 OR QTY = -2) AND ITEMOPTION = __ITEMOPTION ORDER BY QTY DESC LIMIT 1);

 SELECT
 @BRANCHZONE AS BRANCHZONE,
		ZONECOSTS.*
	FROM
		shipZoneCosts ZONECOSTS
	WHERE
		ZONECOSTS.WEIGHT <= @ITEMWEIGHT
	ORDER BY
		WEIGHT DESC;	

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetNewsItem` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetNewsItem`(__ID INTEGER)
BEGIN
	SELECT * FROM newsItems WHERE ID = __ID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetOrderDataForShipment` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetOrderDataForShipment`(__ORDERID INT)
BEGIN
	SELECT
		HDR.*,
        DTL.*,
        ITEM.*,
        DATE(ORDERDATE) AS FMTORDERDATE,
        DATE(EXPDATE) AS FMTEXPDATE,
		USER.FIRSTNAME AS USER_FIRSTNAME,
		USER.LASTNAME AS USER_LASTNAME,
		USER.EMAIL AS USER_EMAIL,
		USER.LANGUAGE AS USER_LANGUAGE
	FROM
		orderHeader HDR
	INNER JOIN
		orderDetail DTL ON 
			HDR.ORDERID = DTL.ORDERID
	LEFT OUTER JOIN
		items ITEM ON 
			DTL.ITEMID = ITEM.ID
	INNER JOIN
		user USER ON 
			HDR.USERNAME = USER.USERNAME            
	WHERE 
		HDR.ORDERID = __ORDERID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetOrderInformationForEmail` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetOrderInformationForEmail`(__USERNAME VARCHAR(100), __ORDERID INT)
BEGIN

SELECT
	HDR.*,
    DTL.*,
    ITEM.TYPE,
    USER.FIRSTNAME AS USER_FIRSTNAME,
    USER.LASTNAME AS USER_LASTNAME,
    USER.EMAIL AS USER_EMAIL,
    USER.LANGUAGE AS USER_LANGUAGE,
    BRANCH.NAME AS BRANCH_NAME,
    BRANCH.MANAGEMENTEMAIL AS BRANCH_MANAGEMENTEMAIL,
    COALESCE(BRANCHADDRESS.LANGUAGE,'EN') AS BRANCH_LANGUAGE,
    ORDERSTATUS AS REALSTATUS
FROM
	orderHeader HDR
INNER JOIN
	orderDetail DTL ON 
		HDR.ORDERID = DTL.ORDERID
INNER JOIN
	items ITEM ON 
		DTL.ITEMID = ITEM.ID
INNER JOIN
	user USER ON 
		HDR.USERNAME = USER.USERNAME
INNER JOIN
	branches BRANCH ON 
		USER.BRANCH = BRANCH.CODE
LEFT OUTER JOIN
	(SELECT * FROM branchesAddresses WHERE ISMAIN = 1) BRANCHADDRESS ON 
		BRANCH.CODE = BRANCHADDRESS.CODE
WHERE 
	HDR.USERNAME = __USERNAME AND 
    HDR.ORDERID = __ORDERID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetPaypalPaymentInformation` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPaypalPaymentInformation`(__USERNAME VARCHAR(100))
BEGIN

SELECT
	HDR.ORDERID,
	SHIPCONTACTFIRSTNAME AS FIRSTNAME,
    SHIPCONTACTLASTNAME AS LASTNAME,
    SHIPCONTACTEMAIL AS EMAIL,
    SPECIALINSTRUCTIONS AS SPECINSTR,
    RECEIVERINSTRUCTIONS AS RECINSTR,
    SHIPPINGMETHOD AS SHIPPINGMETHOD,
    SHIPPINGCOST AS SHIPPINGCOST,
	SHIPNAME AS SHIPNAME,
    BILLINGCATEGORY AS BILLINGCATEGORY,
    PONUMBER AS PONUMBER,
    ROUND(COALESCE(PROMODISC,0)+COALESCE(ITEMTOT,0)+SHIPPINGCOST+SHIPPINGCOSTTAXAMT1+SHIPPINGCOSTTAXAMT2+SHIPPINGCOSTTAXAMT3+SHIPPINGCOSTTAXAMT4+SHIPPINGCOSTTAXAMT5,2) AS AMT
FROM
	orderHeader HDR
INNER JOIN
	( SELECT ORDERID,SUM(EXTPRICE+TAXAMT1+TAXAMT2+TAXAMT3+TAXAMT4+TAXAMT5) AS ITEMTOT FROM orderDetail GROUP BY ORDERID) DTL ON 
		HDR.ORDERID = DTL.ORDERID
LEFT OUTER JOIN
	( SELECT ORDERID,SUM(CALCDISCOUNT+TAXAMT1+TAXAMT2+TAXAMT3+TAXAMT4+TAXAMT5) AS PROMODISC FROM 
      orderPromos GROUP BY ORDERID ) PROMOS ON 
		HDR.ORDERID = PROMOS.ORDERID
WHERE 
	USERNAME  = __USERNAME AND 
    ORDERSTATUS = 'CART';
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetPendingOrders` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPendingOrders`(__USERNAME VARCHAR(20))
BEGIN

	SELECT
		HDR.*,
        CAST(HDR.ORDERDATE AS DATE) AS SHORTDATE,
        DTL.TOTAMT+HDR.SHIPPINGCOST+HDR.SHIPPINGCOSTTAXAMT1+HDR.SHIPPINGCOSTTAXAMT2+HDR.SHIPPINGCOSTTAXAMT3+HDR.SHIPPINGCOSTTAXAMT4+HDR.SHIPPINGCOSTTAXAMT5 AS ORDERAMOUNT
	FROM
		orderHeader HDR
	INNER JOIN
		(SELECT ORDERID, SUM(EXTPRICE+TAXAMT1+TAXAMT2+TAXAMT3+TAXAMT4+TAXAMT5) AS TOTAMT FROM orderDetail GROUP BY ORDERID) DTL ON 
			HDR.ORDERID = DTL.ORDERID
	WHERE
		USERNAME = __USERNAME AND
        ORDERSTATUS IN ('WAITMA');
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetPromoProfile` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPromoProfile`(__CODE VARCHAR(50))
BEGIN
	SELECT * FROM promos WHERE CODE = __CODE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetPromoProfileCategories` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `GetPromoProfileCategories`(__CODE VARCHAR(50))
BEGIN

  SELECT
    PROMOS.CATID,
        CONCAT(CATS.NAME, ' - ', CATS.DESCRIPTION) AS CATNAME
  FROM
    promosCategories PROMOS
  INNER JOIN 
    categories CATS ON 
      PROMOS.CATID = CATS.CATID
  WHERE
    PROMOS.CODE = __CODE;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetPromoProfileItems` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPromoProfileItems`(__CODE VARCHAR(50))
BEGIN
	SELECT 
		PROMOS.ITEMID,
		CONCAT(ITEMS.CODE,' - ',DESCRIPTION) AS ITEMNAME
	FROM 
		promosItems PROMOS
	INNER JOIN 
		items ITEMS ON 
			PROMOS.ITEMID = ITEMS.ID
	WHERE 
		PROMOS.CODE = __CODE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetSearchItems` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `GetSearchItems`(__USERNAME VARCHAR(50), __TERM NVARCHAR(200))
BEGIN
    SELECT DISTINCT
        ITEMS.ID AS ITEMID,
        ITEMS.CODE AS ITEMCODE,
        ITEMS.DESCRIPTION AS ITEMDESC,
        ITEMS.DISPLAYIMAGEPATH,
        ITEMS.PROOFPDFNAME,
        ITEMS.SIZE,
        ITEMS.NUMPAGES,
        ITEMS.MINLEVEL,
        ITEMS.STATUS,
        ITEMS.TYPE AS ITEMTYPE,
        COALESCE(ONORDERSTAT.TOTORDERQTY,0) AS ONORDER, 
        COALESCE(DATE(ONORDERSTAT.ORDERDATE),'') AS LASTORDERED,
        COALESCE(ITEMINV.QTYONHAND,0) AS INSTOCK,
        MIN(SUBCATS.CATID) AS CATID,
        MIN(SUBCATS.SUBCATID) AS SUBCATID
    FROM
        categories CATS
    LEFT OUTER JOIN
        subcategories SUBCATS ON 
            CATS.CATID = SUBCATS.CATID
    LEFT OUTER JOIN
        itemsSubcategoryLink SCTOI ON
            SCTOI.SUBCATID = SUBCATS.SUBCATID
    LEFT OUTER JOIN
        items ITEMS ON 
            ITEMS.ID = SCTOI.ITEMID
    LEFT OUTER JOIN
        (SELECT ITEMID, SUM(CASE WHEN ORDERQTY-SHIPTODATEQTY < 0 THEN 0 ELSE ORDERQTY-SHIPTODATEQTY END) AS TOTORDERQTY, MAX(ORDERDATE) AS ORDERDATE FROM orderHeader HDR INNER JOIN orderDetail DTL ON HDR.ORDERID = DTL.ORDERID WHERE ORDERSTATUS NOT IN ('WAITMA','CART', 'CANCELLED') GROUP BY ITEMID) ONORDERSTAT ON
            ONORDERSTAT.ITEMID = SCTOI.ITEMID  
    LEFT OUTER JOIN
        itemsInventory ITEMINV ON 
            ITEMS.ID = ITEMINV.ITEMID
    INNER JOIN 
        (SELECT DISTINCT 
            ITEMID 
         FROM
            (
                SELECT 
                    ITOB.ITEMID 
                FROM 
                    user USERS 
                INNER JOIN 
                    itemsBranchesLink ITOB ON USERS.BRANCH = ITOB.BRANCHCODE 
                WHERE 
                    USERS.USERNAME = __USERNAME
                    
                UNION ALL 
                SELECT ITOB.ITEMID FROM itemsBranchesLink ITOB WHERE ITOB.BRANCHCODE = 'ALL_BRANCHES'
                
                UNION ALL
                SELECT ITOB.ITEMID FROM user UU INNER JOIN branches BB ON UU.BRANCH = BB.CODE
                INNER JOIN itemsBranchesLink ITOB ON CONCAT('BRN_',BB.SKIN) = ITOB.BRANCHCODE 
                WHERE UU.USERNAME = __USERNAME                
            ) LINKS
        )  BRANCHLINK ON 
                BRANCHLINK.ITEMID = ITEMS.ID  
    CROSS JOIN
        ( SELECT * FROM user WHERE USERNAME = __USERNAME) USERS                        
    WHERE
        (
            ITEMS.CODE COLLATE utf8_unicode_ci LIKE CONCAT('%',__TERM,'%') OR 
            ITEMS.DESCRIPTION COLLATE utf8_unicode_ci LIKE CONCAT('%',__TERM,'%')
        ) AND 
        ITEMS.ARCHIVED = 0 AND
        ITEMS.ASSETAPPROVED = 1 AND
        CATS.CATID IN (
                        SELECT CC.CATID 
                        FROM categories CC 
                        INNER JOIN categoriesBrand CCC ON CC.CATID = CCC.CATID
                        INNER JOIN ( SELECT SKIN FROM branches BB INNER JOIN user UU ON BB.CODE = UU.BRANCH WHERE USERNAME COLLATE utf8_unicode_ci = __USERNAME) UBS ON 
                            (UBS.SKIN = CCC.BRANDNAME OR CCC.BRANDNAME = 'ALL')
                            ) AND
        CATS.USERLEVEL <= COALESCE((SELECT USERLEVEL FROM user WHERE USERNAME = __USERNAME LIMIT 1),0) AND
        USERS.USERLEVEL >= SUBCATS.USERLEVEL
    GROUP BY
        ITEMS.ID ,
        ITEMS.CODE ,
        ITEMS.DESCRIPTION ,
        ITEMS.DISPLAYIMAGEPATH,
        ITEMS.PROOFPDFNAME,
        ITEMS.SIZE,
        ITEMS.NUMPAGES,
        ITEMS.MINLEVEL,
        ITEMS.STATUS,
        ITEMS.TYPE ,
        COALESCE(ONORDERSTAT.TOTORDERQTY,0) , 
        COALESCE(DATE(ONORDERSTAT.ORDERDATE),'') ,
        COALESCE(ITEMINV.QTYONHAND,0) 
    ORDER BY
        ITEMS.CODE
    ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetSettingsValue` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSettingsValue`(SETTING_KEY VARCHAR(100))
BEGIN
	SELECT 
		*
	FROM
		settings
	WHERE
		`KEY` = SETTING_KEY;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetShipmentInfo` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetShipmentInfo`(__SHIPID INT)
BEGIN

	SELECT
		SHIPHDR.*,
        SHIPDTL.*,
        ORDHDR.*,
        ORDDTL.*,
        ORDDTL.ORDERQTY - ORDDTL.SHIPTODATEQTY AS BOQTY,
		USER.EMAIL AS USER_EMAIL,
		USER.LANGUAGE AS USER_LANGUAGE,
		BRANCH.MANAGEMENTEMAIL AS BRANCH_MANAGEMENTEMAIL,
		COALESCE(BRANCHADDRESS.LANGUAGE,'EN') AS BRANCH_LANGUAGE,
        ITEM.TYPE
	FROM
		shipmentHeader SHIPHDR
	INNER JOIN
		shipmentDetail SHIPDTL ON
			SHIPHDR.SHIPID = SHIPDTL.SHIPID
	INNER JOIN
		orderDetail ORDDTL ON 
			SHIPDTL.ORDERID = ORDDTL.ORDERID AND
            SHIPDTL.ORDERLINE = ORDDTL.ITEMLINE
	INNER JOIN
		items ITEM ON 
			ORDDTL.ITEMID = ITEM.ID
	INNER JOIN
		orderHeader ORDHDR ON 
			SHIPHDR.ORDERID = ORDHDR.ORDERID 
	INNER JOIN
		user USER ON 
			ORDHDR.USERNAME = USER.USERNAME 
	INNER JOIN
		branches BRANCH ON 
			USER.BRANCH = BRANCH.CODE 
	LEFT OUTER JOIN
		(SELECT * FROM branchesAddresses WHERE ISMAIN = 1) BRANCHADDRESS ON 
			BRANCH.CODE = BRANCHADDRESS.CODE
	WHERE
		SHIPHDR.SHIPID = __SHIPID;            

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetShipToAddress` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetShipToAddress`(__ID VARCHAR(50), __USEBRANCHES BOOLEAN)
BEGIN
	SELECT
		NAME,ADDRESS,ADDRESS2,CITY,PROVINCE,COUNTRY,POSTAL,PHONE,FAX
	FROM
    (
	SELECT
		'S' AS RECTYPE,ID,NAME,ADDRESS,ADDRESS2,CITY,PROVINCE,COUNTRY,POSTAL,PHONE,FAX
	FROM
		shipToAddresses
        
	UNION ALL
    
    SELECT
		'B' AS RECTYPE,BRANCH.CODE,NAME,ADDRESS,ADDRESS2,CITY,PROVINCE,COUNTRY,POSTALCODE,PHONE,FAX
    FROM
		branches BRANCH
	INNER JOIN
		branchesAddresses BADDR ON 
			BRANCH.CODE = BADDR.CODE
	WHERE
		__USEBRANCHES = 1 AND
        BRANCH.STATUS = 1 AND
        BADDR.ISMAIN = 1
    ) ADDR
    WHERE
		CONCAT(ADDR.RECTYPE,'-',ADDR.ID) = __ID
    ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetShipToAddressInformation` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetShipToAddressInformation`(__ID INT)
BEGIN

	SELECT
		*
	FROM
		shipToAddresses
	WHERE
		ID = __ID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetShipToAddressUsersList` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `GetShipToAddressUsersList`(__SHIPTOID INT)
BEGIN
	SELECT 
		USERS.USERNAME,
		USERS.FIRSTNAME,
		USERS.LASTNAME,
		CASE WHEN USERADDR.ID IS NOT NULL THEN 1 ELSE 0 END AS SELECTED
	FROM
		( SELECT 'ALL' AS USERNAME, 'All' AS FIRSTNAME, 'Users' AS LASTNAME UNION ALL
          SELECT USERNAME,FIRSTNAME,LASTNAME FROM user
        ) USERS 
	LEFT OUTER JOIN 
		(SELECT * FROM shipToAddressesUserList WHERE ID = __SHIPTOID) USERADDR ON 
			USERS.USERNAME = USERADDR.USERNAME
	ORDER BY 
		USERS.USERNAME;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetShipTosBySearchTerm` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `GetShipTosBySearchTerm`(__USEBRANCHES BOOLEAN, __TERM VARCHAR(50), __USERNAME VARCHAR(30))
BEGIN

	SELECT
		CONCAT(RECTYPE,'-',ID) AS ID,
        CONCAT(NAME, ' - ', ADDRESS) AS SHIPDESC
	FROM
    (
	SELECT
		'S' AS RECTYPE,
        ID, 
        NAME,
        ADDRESS,
        ADDRESS2,
        CITY,
        POSTAL AS POSTALCODE,        
        CASE WHEN IFNULL(STOREID,'') = '' THEN 'NA' ELSE STOREID END AS STOREID
	FROM
		shipToAddresses
	WHERE
		ID IN (SELECT ID FROM shipToAddressesUserList WHERE USERNAME = '**ALL**' OR USERNAME = __USERNAME) 
        
	UNION ALL
    
    SELECT
		'B' AS RECTYPE,
        BRANCH.CODE,
        BRANCH.NAME,
        BADDR.ADDRESS,
        BADDR.ADDRESS2,
        BADDR.CITY,
        BADDR.POSTALCODE,
        BRANCH.CODE AS STOREID
    FROM
		branches BRANCH
	INNER JOIN
		branchesAddresses BADDR ON 
			BRANCH.CODE = BADDR.CODE
	WHERE
		__USEBRANCHES = 1 AND
        BRANCH.STATUS = 1 AND
        BADDR.ISMAIN = 1
    ) ADDR
    WHERE
	(
	ADDR.NAME LIKE CONCAT('%',__TERM,'%') OR 
    ADDR.ADDRESS LIKE CONCAT('%',__TERM,'%') OR
    ADDR.ADDRESS2 LIKE CONCAT('%',__TERM,'%') OR
    ADDR.CITY LIKE CONCAT('%',__TERM,'%') OR
    ADDR.POSTALCODE LIKE CONCAT('%',__TERM,'%') OR 
    ADDR.STOREID LIKE CONCAT('%',__TERM,'%')
    ) 
    ORDER BY
		NAME;	

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetSkinOptions` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSkinOptions`()
BEGIN
	SELECT
		*
	FROM
		branchSkinDefinition
	ORDER BY
		SKINNAME;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetSubCategoryInformation` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSubCategoryInformation`(__SUBCATID INTEGER)
BEGIN
	SELECT * FROM subcategories WHERE SUBCATID = __SUBCATID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetSupportTicketAttachments` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSupportTicketAttachments`(__TICKETID INT)
BEGIN
	SELECT * FROM ticketAttachment WHERE TICKETID = __TICKETID ORDER BY
    ID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetSupportTicketData` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `GetSupportTicketData`(__TICKETID INT, __USERNAME VARCHAR(50))
BEGIN

	SELECT
	HDR.TITLE,
	HDR.STATUS,
		DTL.*,
        USR.FIRSTNAME,
        USR.LASTNAME,
        UNIX_TIMESTAMP(DTL.DATEENTERED) AS UNIX_DATEENTERED
	FROM
		ticketHeader HDR
	INNER JOIN
		ticketDetail DTL ON 
			HDR.ID = DTL.TICKETID
	LEFT OUTER JOIN
		user USR ON 
			DTL.ENTEREDBY = USR.USERNAME
	WHERE
        ( HDR.ENTEREDBY = __USERNAME OR __USERNAME IN (SELECT USERNAME FROM user WHERE USERLEVEL >= 81)) AND
        ( DTL.TICKETID = __TICKETID )
	ORDER BY
		DTL.DATEENTERED DESC;
		

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetSupportTicketList` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `GetSupportTicketList`(__USERNAME VARCHAR(50))
BEGIN

	SELECT
		*,
        UNIX_TIMESTAMP(HDR.DATEENTERED) AS UNIX_DATEENTERED
	FROM
		ticketHeader HDR
	WHERE
		( HDR.STATUS = 1 OR (HDR.STATUS = 0 AND DATECLOSED >= DATE_ADD(NOW(), INTERVAL -14 DAY)) ) AND
        ( HDR.ENTEREDBY = __USERNAME OR __USERNAME IN (SELECT USERNAME FROM user WHERE USERLEVEL >= 81))
	ORDER BY
		HDR.STATUS DESC,
		HDR.DATEENTERED DESC;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetTaxRecords` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetTaxRecords`()
BEGIN
	SELECT * FROM taxes;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetUserBranchAssets` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetUserBranchAssets`(__CODE VARCHAR(20))
BEGIN
	SELECT
		BAD.TAG,
        BAD.TYPE,
        BAD.NAME,
	IFNULL(BAD.VALIDATEPATTERN,'') AS VALIDATEPATTERN,
	IFNULL(BAD.VALIDATEERRORMSG,'') AS VALIDATEERRORMSG,
        IFNULL(BDATA.VALUE,'') AS VALUE
	FROM
		branchAssetDefinition BAD
	LEFT OUTER JOIN
		(SELECT * FROM branchesAssetData WHERE BRANCHCODE = __CODE) BDATA ON 
			BAD.TAG = BDATA.TAG
	WHERE
		BAD.USERACCESSIBLE = 1
	ORDER BY
		BAD.DISPLAYORDER;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetUserInformation` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetUserInformation`(UNAME VARCHAR(50))
BEGIN
	SELECT * FROM user WHERE USERNAME=UNAME;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetZoneCostByWeight` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetZoneCostByWeight`(__WEIGHT DECIMAL, __BRANCH VARCHAR(20))
BEGIN

	SELECT
		BRANCHZONE.SHIPZONE AS BRANCHZONE,
		ZONECOSTS.*
	FROM
		shipZoneCosts ZONECOSTS
	CROSS JOIN
		(SELECT SHIPZONE FROM branches WHERE CODE = __BRANCH) BRANCHZONE
	WHERE
		ZONECOSTS.WEIGHT <= __WEIGHT 
	ORDER BY
		WEIGHT DESC;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchAssetDefinitionDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchAssetDefinitionDelete`(__TAG VARCHAR(100))
BEGIN
	DELETE FROM branchAssetDefinition WHERE TAG = __TAG;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchAssetDefinitionInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchAssetDefinitionInsert`(__TAG VARCHAR(100), __TYPE VARCHAR(30), __NAME VARCHAR(100), __VALIDATEPATTERN VARCHAR(100), __DISPLAYORDER INT, __VALIDATEERRORMSG VARCHAR(100),__USERACCESSIBLE INT)
BEGIN
	INSERT INTO  
		branchAssetDefinition
        (TAG,TYPE,NAME,VALIDATEPATTERN,DISPLAYORDER,VALIDATEERRORMSG,USERACCESSIBLE)
	VALUES
		(__TAG,__TYPE,__NAME,__VALIDATEPATTERN,__DISPLAYORDER,__VALIDATEERRORMSG,__USERACCESSIBLE);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchAssetDefinitionUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchAssetDefinitionUpdate`(__TAG VARCHAR(100), __TYPE VARCHAR(30), __NAME VARCHAR(100), __VALIDATEPATTERN VARCHAR(100), __DISPLAYORDER INT, __VALIDATEERRORMSG VARCHAR(100),__USERACCESSIBLE INT   )
BEGIN

	UPDATE 
		branchAssetDefinition
	SET
		 TYPE = __TYPE, 
         NAME = __NAME,
         VALIDATEPATTERN = __VALIDATEPATTERN, 
         DISPLAYORDER = __DISPLAYORDER,
         VALIDATEERRORMSG = __VALIDATEERRORMSG,
         USERACCESSIBLE = __USERACCESSIBLE
    WHERE
		TAG = __TAG;
    

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchesAddressesInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchesAddressesInsert`(__CODE VARCHAR(20), __LANGUAGE VARCHAR(2), __ISMAIN INT, __ADDRESS VARCHAR(1000), __ADDRESS2 VARCHAR(1000), __CITY VARCHAR(100), __PROVINCE VARCHAR(100), __COUNTRY VARCHAR(100), __POSTALCODE VARCHAR(20), __PHONE VARCHAR(20), __FAX VARCHAR(20))
BEGIN

	INSERT INTO
		branchesAddresses
        (CODE,LANGUAGE,ISMAIN,ADDRESS,ADDRESS2,CITY,PROVINCE,COUNTRY,POSTALCODE,PHONE,FAX)
	VALUES
		(__CODE,__LANGUAGE,__ISMAIN,__ADDRESS,__ADDRESS2,__CITY,__PROVINCE,__COUNTRY,__POSTALCODE,__PHONE,__FAX);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchesAddressesUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchesAddressesUpdate`(__CODE VARCHAR(20), __LANGUAGE VARCHAR(2), __ISMAIN INT, __ADDRESS VARCHAR(1000), __ADDRESS2 VARCHAR(1000), __CITY VARCHAR(100), __PROVINCE VARCHAR(100), __COUNTRY VARCHAR(100), __POSTALCODE VARCHAR(20), __PHONE VARCHAR(20), __FAX VARCHAR(20))
BEGIN

UPDATE
	branchesAddresses
SET
    ISMAIN = __ISMAIN,
    ADDRESS = __ADDRESS,
    ADDRESS2 = __ADDRESS2,
    CITY = __CITY,
    PROVINCE = __PROVINCE,
    COUNTRY = __COUNTRY,
    POSTALCODE = __POSTALCODE,
    PHONE = __PHONE,
    FAX = __FAX
WHERE
	CODE = __CODE AND
    LANGUAGE = __LANGUAGE;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchesAssetDataDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchesAssetDataDelete`(__CODE VARCHAR(20), __TAG VARCHAR(100))
BEGIN

	DELETE FROM branchesAssetData WHERE BRANCHCODE = __CODE AND TAG = __TAG;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchesAssetDataInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchesAssetDataInsert`(__CODE VARCHAR(20), __TAG VARCHAR(100), __VALUE VARCHAR(1000))
BEGIN

	INSERT INTO
		branchesAssetData
			(BRANCHCODE,TAG,VALUE)
	VALUES
			(__CODE,__TAG,__VALUE);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchesAssetDataUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchesAssetDataUpdate`(__CODE VARCHAR(20), __TAG VARCHAR(100), __VALUE VARCHAR(1000))
BEGIN

	UPDATE 
		branchesAssetData
	SET
		VALUE = __VALUE
	WHERE
		BRANCHCODE = __CODE AND
        TAG = __TAG;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchesDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchesDelete`(__CODE VARCHAR(20), __LANGUAGE VARCHAR(2))
BEGIN

	DELETE FROM branchesAddresses WHERE CODE = __CODE AND LANGUAGE = __LANGUAGE;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchesInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchesInsert`(__CODE VARCHAR(20), __NAME VARCHAR(100), __MANAGEMENTEMAIL VARCHAR(100), __TREASUREREMAIL VARCHAR(100), __TAXGROUP VARCHAR(6), __SKIN VARCHAR(20), __STATUS INT, __COMMENTS VARCHAR(1000), __SHIPZONE VARCHAR(6))
BEGIN

	INSERT INTO
		branches
        (CODE,NAME,MANAGEMENTEMAIL,TREASUREREMAIL,TAXGROUP,SKIN,STATUS,COMMENTS,SHIPZONE)
	VALUES
		(__CODE,__NAME,__MANAGEMENTEMAIL,__TREASUREREMAIL,__TAXGROUP,__SKIN,__STATUS,__COMMENTS,__SHIPZONE);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchesUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchesUpdate`(__CODE VARCHAR(20), __NAME VARCHAR(100), __MANAGEMENTEMAIL VARCHAR(100), __TREASUREREMAIL VARCHAR(100), __TAXGROUP VARCHAR(6), __SKIN VARCHAR(20), __STATUS INT, __COMMENTS VARCHAR(1000), __SHIPZONE VARCHAR(6))
BEGIN

	UPDATE
		branches
	SET
		NAME = __NAME,
        MANAGEMENTEMAIL = __MANAGEMENTEMAIL,
        TREASUREREMAIL = __TREASUREREMAIL,
        TAXGROUP = __TAXGROUP,
        SKIN = __SKIN,
        STATUS = __STATUS,
        COMMENTS = __COMMENTS,
        SHIPZONE = __SHIPZONE
	WHERE
		CODE = __CODE;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchSkinDefinitionDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchSkinDefinitionDelete`(__SKIN VARCHAR(20))
BEGIN
	DELETE FROM branchSkinDefinition WHERE SKINNAME = __SKIN;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchSkinDefinitionInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchSkinDefinitionInsert`(__SKIN VARCHAR(20), __STYLEFILE VARCHAR(45))
BEGIN
	INSERT INTO
		branchSkinDefinition (SKINNAME, STYLEFILENAME)
	VALUES
		(__SKIN,__STYLEFILE);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_BranchSkinDefinitionUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_BranchSkinDefinitionUpdate`(__SKIN VARCHAR(20), __STYLEFILE VARCHAR(45))
BEGIN
	UPDATE
		branchSkinDefinition
	SET
		STYLEFILENAME = __STYLEFILE
	WHERE
		SKINNAME = __SKIN;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CategoriesCheckCATID` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CategoriesCheckCATID`(__CATID INT)
BEGIN

	SELECT CATID FROM categories WHERE CATID = __CATID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CategoriesDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CategoriesDelete`(__CATID INT)
BEGIN

	DELETE FROM categories WHERE CATID = __CATID;
	DELETE FROM subcategories WHERE CATID = __CATID;
	DELETE FROM itemsSubcategoryLink WHERE 
		SUBCATID NOT IN (SELECT SUBCATID FROM subcategories);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CategoriesInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CategoriesInsert`(__CATID INT, __NAME VARCHAR(50), __DESCRIPTION VARCHAR(200), __ICONURL VARCHAR(200), __LANGUAGE VARCHAR(2), __USERLEVEL INT,__COMMENTS VARCHAR(1000))
BEGIN

	INSERT INTO
		categories
        (CATID,NAME,DESCRIPTION,ICONURL,LANGUAGE,USERLEVEL,COMMENTS,CREATEDDATE,MODIFIEDDATE)
	VALUES
		(__CATID,__NAME,__DESCRIPTION,__ICONURL,__LANGUAGE,__USERLEVEL,__COMMENTS,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CategoriesUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CategoriesUpdate`(__CATID INT, __NAME VARCHAR(50), __DESCRIPTION VARCHAR(200), __ICONURL VARCHAR(200), __LANGUAGE VARCHAR(2), __USERLEVEL INT,__COMMENTS VARCHAR(1000))
BEGIN

	UPDATE
		categories
	SET
		NAME = __NAME,
        DESCRIPTION = __DESCRIPTION,
        ICONURL = __ICONURL,
        LANGUAGE = __LANGUAGE,
        USERLEVEL = __USERLEVEL,
        COMMENTS = __COMMENTS,
        MODIFIEDDATE = CURRENT_TIMESTAMP
	WHERE
		CATID = __CATID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckBRANCHASSETTAG` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckBRANCHASSETTAG`(__TAG VARCHAR(100))
BEGIN
	SELECT * FROM branchAssetDefinition WHERE TAG = __TAG;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckBRANCHCODE` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckBRANCHCODE`(__CODE VARCHAR(20))
BEGIN

	SELECT
		CODE 
	FROM
		branches
	WHERE
		CODE = __CODE;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckITEMID` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckITEMID`(__ITEMID INT)
BEGIN

	SELECT ID FROM items WHERE ID = __ITEMID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckNEWSID` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckNEWSID`(__NEWSID INT)
BEGIN

	SELECT ID FROM newsItems WHERE ID = __NEWSID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckPROMOCODE` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckPROMOCODE`(__CODE VARCHAR(50))
BEGIN
	SELECT * FROM promos WHERE CODE = __CODE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckSHIPID` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckSHIPID`(__SHIPID INT)
BEGIN
	SELECT ID FROM shipToAddresses WHERE ID = __SHIPID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckSHIPTOUSER` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `importTableData_CheckSHIPTOUSER`(__shipid INT, __username VARCHAR(30))
BEGIN
	SELECT * FROM shipToAddressesUserList WHERE ID = __shipid AND USERNAME = __username;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckSHIPWEIGHT` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckSHIPWEIGHT`(__WEIGHT INT)
BEGIN

	SELECT WEIGHT FROM shipZoneCosts WHERE WEIGHT = __WEIGHT;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckSHIPZONE` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckSHIPZONE`(__SHIPZONE VARCHAR(6))
BEGIN

	SELECT 
		COLUMN_NAME 
	FROM 
		information_schema.COLUMNS 
	WHERE 
		TABLE_NAME = 'shipZoneCosts' AND 
        COLUMN_NAME COLLATE utf8_unicode_ci = __SHIPZONE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckSKINNAME` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckSKINNAME`(__SKIN VARCHAR(20))
BEGIN

	SELECT * FROM branchSkinDefinition WHERE SKINNAME = __SKIN;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckSUBCATID` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckSUBCATID`(__SUBCATID INT)
BEGIN
	SELECT SUBCATID FROM subcategories WHERE SUBCATID = __SUBCATID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckTAXGROUP` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckTAXGROUP`(__TAXGROUP VARCHAR(6))
BEGIN

	SELECT TAXCODE FROM taxes WHERE TAXCODE = __TAXGROUP;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_CheckUSERNAME` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_CheckUSERNAME`(__USERNAME VARCHAR(50))
BEGIN

	SELECT USERNAME FROM user WHERE USERNAME = __USERNAME;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ItemPricingDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ItemPricingDelete`(__ITEMID INT, __QTY INT, __ITEMOPTION VARCHAR(30))
BEGIN

 DELETE FROM itemPricing WHERE ITEMID = __ITEMID AND QTY = __QTY AND ITEMOPTION = __ITEMOPTION;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ItemPricingInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ItemPricingInsert`(__ITEMID INT, __QTY INT, __ITEMOPTION VARCHAR(30), __PRICE DECIMAL(19,4), __LENGTH DECIMAL(19,4), __WIDTH DECIMAL(19,4), __HEIGHT DECIMAL(19,4), __WEIGHT DECIMAL(19,6), __PIECES INT)
BEGIN

 INSERT INTO 
 itemPricing
 (ITEMID,QTY,PRICE,LENGTH,WIDTH,HEIGHT,WEIGHT,PIECES,ITEMOPTION)
 VALUES
 (__ITEMID,__QTY,__PRICE,__LENGTH,__WIDTH,__HEIGHT,__WEIGHT,__PIECES,__ITEMOPTION);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ItemPricingUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ItemPricingUpdate`(__ITEMID INT, __QTY INT, __ITEMOPTION VARCHAR(30), __PRICE DECIMAL(19,4),  __LENGTH DECIMAL(19,4), __WIDTH DECIMAL(19,4), __HEIGHT DECIMAL(19,4), __WEIGHT DECIMAL(19,6), __PIECES INT)
BEGIN

 UPDATE
		itemPricing
	SET
		PRICE = __PRICE,
        LENGTH = __LENGTH,
        WIDTH = __WIDTH,
        HEIGHT = __HEIGHT,
        WEIGHT = __WEIGHT,
        PIECES = __PIECES,
        ITEMOPTION = __ITEMOPTION
	WHERE
		ITEMID = __ITEMID AND
        QTY = __QTY AND
        ITEMOPTION = __ITEMOPTION;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ItemPrintDefinitionDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ItemPrintDefinitionDelete`(__ITEMID INT)
BEGIN
  DELETE FROM itemsPrintDefinition WHERE ID = __ITEMID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ItemPrintDefinitionInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ItemPrintDefinitionInsert`(__ITEMID INT, __PRESS VARCHAR(100),__NUMPAGES VARCHAR(100),__BLEEDS INT, __COLOURS VARCHAR(100),
__FLATSIZE VARCHAR(100), __FINISHSIZE VARCHAR(100), __STOCK VARCHAR(100), __PARENTSHEETSIZE VARCHAR(100),
__PRESSSHEETSIZE VARCHAR(100), __NUMUP VARCHAR(100), __LAYOUTORIENTATION VARCHAR(100),
__COATING VARCHAR(100), __CONTOURCUT VARCHAR(100), __LAMINATION VARCHAR(100), __FINISHING VARCHAR(200),
__ACCESSORY VARCHAR(200), __SPECIALINSTRUCTIONS VARCHAR(200), __MEMO VARCHAR(200), __PACKAGELOCATION VARCHAR(200),
__OUT VARCHAR(100), __FINISHED VARCHAR(100), __OVERS VARCHAR(100), __GROSS VARCHAR(100))
BEGIN

INSERT INTO itemsPrintDefinition
	(ID,PRESS,NUMPAGES,BLEEDS,COLOURS,FLATSIZE,FINISHSIZE,STOCK,PARENTSHEETSIZE,
	 PRESSSHEETSIZE,NUMUP,LAYOUTORIENTATION,COATING,CONTOURCUT,LAMINATION,FINISHING,
	 ACCESSORY,SPECIALINSTRUCTIONS,MEMO,PACKAGELOCATION,`OUT`,FINISHED,OVERS,GROSS)
	VALUES 
	(__ITEMID,__PRESS,__NUMPAGES,__BLEEDS,__COLOURS,__FLATSIZE,__FINISHSIZE,__STOCK,__PARENTSHEETSIZE,
	 __PRESSSHEETSIZE,__NUMUP,__LAYOUTORIENTATION,__COATING,__CONTOURCUT,__LAMINATION,__FINISHING,
	 __ACCESSORY,__SPECIALINSTRUCTIONS,__MEMO,__PACKAGELOCATION,__OUT,__FINISHED,__OVERS,__GROSS);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ItemPrintDefinitionUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ItemPrintDefinitionUpdate`(__ITEMID INT, __PRESS VARCHAR(100),__NUMPAGES VARCHAR(100),__BLEEDS INT, __COLOURS VARCHAR(100),
__FLATSIZE VARCHAR(100), __FINISHSIZE VARCHAR(100), __STOCK VARCHAR(100), __PARENTSHEETSIZE VARCHAR(100),
__PRESSSHEETSIZE VARCHAR(100), __NUMUP VARCHAR(100), __LAYOUTORIENTATION VARCHAR(100),
__COATING VARCHAR(100), __CONTOURCUT VARCHAR(100), __LAMINATION VARCHAR(100), __FINISHING VARCHAR(200),
__ACCESSORY VARCHAR(200), __SPECIALINSTRUCTIONS VARCHAR(200), __MEMO VARCHAR(200), __PACKAGELOCATION VARCHAR(200),
__OUT VARCHAR(100), __FINISHED VARCHAR(100), __OVERS VARCHAR(100), __GROSS VARCHAR(100)
)
BEGIN
	UPDATE itemsPrintDefinition
	SET 
		PRESS = __PRESS,
		NUMPAGES = __NUMPAGES,
		BLEEDS = __BLEEDS,
		COLOURS = __COLOURS,
		FLATSIZE = __FLATSIZE,
		FINISHSIZE = __FINISHSIZE,
		STOCK = __STOCK,
		PARENTSHEETSIZE = __PARENTSHEETSIZE,
		PRESSSHEETSIZE = __PRESSSHEETSIZE,
		NUMUP = __NUMUP,
		LAYOUTORIENTATION = __LAYOUTORIENTATION,
		COATING = __COATING,
		CONTOURCUT = __CONTOURCUT,
		LAMINATION = __LAMINATION,
		FINISHING = __FINISHING,
		ACCESSORY = __ACCESSORY,
		SPECIALINSTRUCTIONS = __SPECIALINSTRUCTIONS,
        MEMO = __MEMO,
		PACKAGELOCATION = __PACKAGELOCATION,
        `OUT` = __OUT , 
        FINISHED = __FINISHED, 
        OVERS = __OVERS, 
        GROSS = __GROSS
	WHERE ID = __ITEMID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_itemsBranchesLinkDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_itemsBranchesLinkDelete`(__ITEMID INT, __BRANCHCODE VARCHAR(20))
BEGIN

	DELETE FROM itemsBranchesLink WHERE ITEMID = __ITEMID AND BRANCHCODE = __BRANCHCODE;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_itemsBranchesLinkInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_itemsBranchesLinkInsert`(__ITEMID INT, __BRANCHCODE VARCHAR(20))
BEGIN

	INSERT INTO 
		itemsBranchesLink
        (ITEMID,BRANCHCODE)
	VALUES
		(__ITEMID,__BRANCHCODE);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ItemsDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ItemsDelete`(__ID INT)
BEGIN

  DELETE FROM items WHERE ID = __ID;
    DELETE FROM itemPricing WHERE ITEMID = __ID;
    DELETE FROM itemsBranchesLink WHERE ITEMID = __ID;
  DELETE FROM itemsSubcategoryLink WHERE ITEMID = __ID;
  DELETE FROM itemsInventory WHERE ITEMID = __ID;
    DELETE FROM itemsPrintDefinition WHERE ID = __ID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ItemsInsert` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `importTableData_ItemsInsert`(__ID INT, __TYPE INT, __CODE VARCHAR(50),
__DESCRIPTION VARCHAR(200), __PRODUCEDETAIL VARCHAR(1000), __SIZE VARCHAR(50), __NUMPAGES INT, __STATUS INT, __MINLEVEL INT,
__LEADTIME INT, __DISPLAYIMAGENAME VARCHAR(200), __PROOFPDFNAME VARCHAR(200), __PARENTITEM INT,
__ARCHIVED INT, __GENERATEDBY VARCHAR(200), __ASSETAPPROVED INT, __PARAMETERSFILENAME VARCHAR(200),
__UPLOADDIR VARCHAR(200),__PREVIEWIMAGENAME VARCHAR(200), __SHIPBOXINSLEEVE INT, __SKIPPDFPERMISSIONCHECK INT, __DLSUGGESTIONS VARCHAR(1000))
BEGIN

DECLARE __DISPNAME VARCHAR(200) DEFAULT NULL;
DECLARE __PROOFNAME VARCHAR(200) DEFAULT NULL;
DECLARE __PREVIEWNAME VARCHAR(200) DEFAULT NULL;
DECLARE __PARAMETERSNAME VARCHAR(200) DEFAULT NULL;
DECLARE __DISPPATH VARCHAR(200) DEFAULT NULL;
DECLARE __PROOFPATH VARCHAR(200) DEFAULT NULL;
DECLARE __PREVIEWPATH VARCHAR(200) DEFAULT NULL;
DECLARE __PARAMETERSPATH VARCHAR(200) DEFAULT NULL;

IF LENGTH(__DISPLAYIMAGENAME) > 0 THEN
  SET __DISPNAME = __DISPLAYIMAGENAME;
    SET __DISPPATH = CONCAT(__UPLOADDIR,__DISPLAYIMAGENAME);
END IF;
IF LENGTH(__PROOFPDFNAME) > 0 THEN
  SET __PROOFNAME = __PROOFPDFNAME;
    SET __PROOFPATH = CONCAT(__UPLOADDIR,__PROOFPDFNAME);
END IF;
IF LENGTH(__PREVIEWIMAGENAME) > 0 THEN
  SET __PREVIEWNAME = __PREVIEWIMAGENAME;
    SET __PREVIEWPATH = CONCAT(__UPLOADDIR,__PREVIEWIMAGENAME);
END IF;
IF LENGTH(__PARAMETERSFILENAME) > 0 THEN
  SET __PARAMETERSNAME = __PARAMETERSFILENAME;
    SET __PARAMETERSPATH = CONCAT(__UPLOADDIR,__PARAMETERSFILENAME);
END IF;

INSERT
  items
    (ID,TYPE,CODE,DESCRIPTION,PRODUCEDETAIL,SIZE,NUMPAGES,STATUS,MINLEVEL,LEADTIME,DISPLAYIMAGENAME,
   DISPLAYIMAGEPATH,PROOFPDFNAME,PROOFPDFPATH,PARENTITEM,ARCHIVED,GENERATEDBY,
     ASSETAPPROVED,PARAMETERSFILENAME,PARAMETERSFILEPATH, PREVIEWIMAGENAME,PREVIEWIMAGEPATH,
     SHIPBOXINSLEEVE,SKIPPDFPERMISSIONCHECK,DLSUGGESTIONS)
VALUES
  (__ID,__TYPE,__CODE,__DESCRIPTION,__PRODUCEDETAIL,__SIZE,__NUMPAGES,__STATUS,__MINLEVEL,__LEADTIME,
     __DISPNAME,__DISPPATH,__PROOFNAME,__PROOFPATH,__PARENTITEM,__ARCHIVED,__GENERATEDBY,
     __ASSETAPPROVED,__PARAMETERSNAME,__PARAMETERSPATH, __PREVIEWNAME, __PREVIEWPATH,
     __SHIPBOXINSLEEVE,__SKIPPDFPERMISSIONCHECK,__DLSUGGESTIONS);
     
IF __TYPE = 0 THEN
  INSERT INTO 
    itemsInventory
    (ITEMID,QTYONHAND)
  VALUES (__ID,0);
END IF;     

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_itemsInventoryUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_itemsInventoryUpdate`(__ITEMID INT, __QTY INT)
BEGIN

	UPDATE itemsInventory 
    SET QTYONHAND = __QTY 
    WHERE 
		ITEMID = __ITEMID;
        
	INSERT INTO
		itemsAdjustmentLog
			(ITEMID,QTY,ADJDATE,REASON)
	SELECT
		ITEMID,
        __QTY,
        CURRENT_TIMESTAMP,
        'Import File, Set Value Absolutely'
	FROM
		itemsInventory
	WHERE
		ITEMID = __ITEMID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_itemsSubcategoryLinkDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_itemsSubcategoryLinkDelete`(__ITEMID INT, __SUBCATID INT)
BEGIN
	
    DELETE FROM itemsSubcategoryLink WHERE ITEMID = __ITEMID AND SUBCATID = __SUBCATID;
    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_itemsSubcategoryLinkInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_itemsSubcategoryLinkInsert`(__ITEMID INT, __SUBCATID INT)
BEGIN
	
    INSERT INTO 
		itemsSubcategoryLink
        (ITEMID,SUBCATID)
	VALUES
		(__ITEMID,__SUBCATID);
       
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ItemsUpdate` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `importTableData_ItemsUpdate`(__ID INT, __TYPE INT, __CODE VARCHAR(50),
__DESCRIPTION VARCHAR(200), __PRODUCEDETAIL VARCHAR(1000), __SIZE VARCHAR(50), __NUMPAGES INT, __STATUS INT, __MINLEVEL INT,
__LEADTIME INT, __DISPLAYIMAGENAME VARCHAR(200), __PROOFPDFNAME VARCHAR(200), __PARENTITEM INT,
__ARCHIVED INT, __GENERATEDBY VARCHAR(200), __ASSETAPPROVED INT, __PARAMETERSFILENAME VARCHAR(200),
__UPLOADDIR VARCHAR(200), __PREVIEWIMAGENAME VARCHAR(200), __SHIPBOXINSLEEVE INT, __SKIPPDFPERMISSIONCHECK INT, __DLSUGGESTIONS VARCHAR(1000))
BEGIN

DECLARE __DISPNAME VARCHAR(200) DEFAULT NULL;
DECLARE __PROOFNAME VARCHAR(200) DEFAULT NULL;
DECLARE __PREVIEWNAME VARCHAR(200) DEFAULT NULL;
DECLARE __PARAMETERSNAME VARCHAR(200) DEFAULT NULL;
DECLARE __DISPPATH VARCHAR(200) DEFAULT NULL;
DECLARE __PROOFPATH VARCHAR(200) DEFAULT NULL;
DECLARE __PREVIEWPATH VARCHAR(200) DEFAULT NULL;
DECLARE __PARAMETERSPATH VARCHAR(200) DEFAULT NULL;

IF LENGTH(__DISPLAYIMAGENAME) > 0 THEN
  SET __DISPNAME = __DISPLAYIMAGENAME;
    SET __DISPPATH = CONCAT(__UPLOADDIR,__DISPLAYIMAGENAME);
END IF;
IF LENGTH(__PROOFPDFNAME) > 0 THEN
  SET __PROOFNAME = __PROOFPDFNAME;
    SET __PROOFPATH = CONCAT(__UPLOADDIR,__PROOFPDFNAME);
END IF;
IF LENGTH(__PREVIEWIMAGENAME) > 0 THEN
  SET __PREVIEWNAME = __PREVIEWIMAGENAME;
    SET __PREVIEWPATH = CONCAT(__UPLOADDIR,__PREVIEWIMAGENAME);
END IF;
IF LENGTH(__PARAMETERSFILENAME) > 0 THEN
  SET __PARAMETERSNAME = __PARAMETERSFILENAME;
    SET __PARAMETERSPATH = CONCAT(__UPLOADDIR,__PARAMETERSFILENAME);
END IF;

UPDATE
  items
SET
  CODE = __CODE,
    DESCRIPTION = __DESCRIPTION,
    PRODUCEDETAIL = __PRODUCEDETAIL,
    SIZE = __SIZE,
    NUMPAGES = __NUMPAGES,
    STATUS = __STATUS,
    MINLEVEL = __MINLEVEL,
    LEADTIME = __LEADTIME,
    DISPLAYIMAGENAME = __DISPNAME,
    DISPLAYIMAGEPATH = __DISPPATH,
    PROOFPDFNAME = __PROOFNAME,
    PROOFPDFPATH = __PROOFPATH,
    PREVIEWIMAGENAME = __PREVIEWNAME,
    PREVIEWIMAGEPATH = __PREVIEWPATH,    
    PARENTITEM = __PARENTITEM,
    ARCHIVED = __ARCHIVED,
    GENERATEDBY = __GENERATEDBY,
    ASSETAPPROVED = __ASSETAPPROVED,
    PARAMETERSFILENAME = __PARAMETERSNAME,
    PARAMETERSFILEPATH = __PARAMETERSPATH,
    SHIPBOXINSLEEVE = __SHIPBOXINSLEEVE,
    SKIPPDFPERMISSIONCHECK = __SKIPPDFPERMISSIONCHECK,
    DLSUGGESTIONS = __DLSUGGESTIONS
WHERE
  ID = __ID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_NewsDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_NewsDelete`(__NEWSID INT)
BEGIN

	DELETE FROM newsItems WHERE ID = __NEWSID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_NewsInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_NewsInsert`(__NEWSID INT, __STARTDATE DATE, __ENDDATE DATE,__LANGUAGE VARCHAR(2), __TITLE VARCHAR(1000), __MESSAGE VARCHAR(1000))
BEGIN

	INSERT INTO
		newsItems
        (ID,STARTDATE,ENDDATE,LANGUAGE,TITLE,MESSAGE)
	VALUES
		(__NEWSID,__STARTDATE,__ENDDATE,__LANGUAGE,__TITLE,__MESSAGE);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_NewsUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_NewsUpdate`(__NEWSID INT, __STARTDATE DATE, __ENDDATE DATE,__LANGUAGE VARCHAR(2), __TITLE VARCHAR(1000), __MESSAGE VARCHAR(1000))
BEGIN

	UPDATE
		newsItems
	SET
		STARTDATE = __STARTDATE,
        ENDDATE = __ENDDATE,
        LANGUAGE = __LANGUAGE,
        TITLE = __TITLE,
        MESSAGE = __MESSAGE
	WHERE
		ID = __NEWSID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_PromoDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_PromoDelete`(__CODE VARCHAR(50))
BEGIN
	DELETE FROM promos WHERE CODE = __CODE;
    DELETE FROM promosItems WHERE CODE = __CODE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_PromoInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_PromoInsert`(__CODE VARCHAR(50), __NAME VARCHAR(100), __CODETYPE VARCHAR(8), __STARTDATE DATE, __ENDDATE DATE, __SINGLEUSE INT, __REDEEMED INT, __PROMOVALUE DECIMAL(19,2), __MAXQTY INT, __SINGLEITEM INT)
BEGIN
 INSERT INTO 
 promos
 (CODE,NAME,CODETYPE,STARTDATE,ENDDATE,SINGLEUSE,REDEEMED,PROMOVALUE,MAXQTY,SINGLEITEM)
 VALUES
 (__CODE, __NAME, __CODETYPE, __STARTDATE, __ENDDATE, __SINGLEUSE, __REDEEMED, __PROMOVALUE,__MAXQTY, __SINGLEITEM);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_PromoItemsDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_PromoItemsDelete`(__CODE VARCHAR(50), __ITEMID INT)
BEGIN
	DELETE FROM promosItems WHERE CODE = __CODE AND ITEMID = __ITEMID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_PromoItemsInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_PromoItemsInsert`(__CODE VARCHAR(50), __ITEMID INT)
BEGIN
	INSERT INTO promosItems
    (CODE,ITEMID)
    VALUES
    (__CODE,__ITEMID);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_PromoItemsUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_PromoItemsUpdate`(__CODE VARCHAR(50), __ITEMID INT)
BEGIN
	UPDATE
		promosItems
	SET
		CODE = __CODE,
        ITEMID = __ITEMID
	WHERE
		CODE = __CODE AND
        ITEMID = __ITEMID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_PromoUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_PromoUpdate`(__CODE VARCHAR(50), __NAME VARCHAR(100), __CODETYPE VARCHAR(8), __STARTDATE DATE, __ENDDATE DATE, __SINGLEUSE INT, __REDEEMED INT, __PROMOVALUE DECIMAL(19,2), __MAXQTY INT, __SINGLEITEM INT)
BEGIN
 UPDATE
 promos
 SET
 NAME = __NAME,
        CODETYPE = __CODETYPE,
        STARTDATE = __STARTDATE,
        ENDDATE = __ENDDATE,
        SINGLEUSE = __SINGLEUSE,
        REDEEMED = __REDEEMED,
        PROMOVALUE = __PROMOVALUE,
        MAXQTY = __MAXQTY,
        SINGLEITEM = __SINGLEITEM
	WHERE
		CODE = __CODE;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ShippingCostsDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ShippingCostsDelete`(__WEIGHT INT)
BEGIN

	DELETE FROM shipZoneCosts WHERE WEIGHT = __WEIGHT;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ShippingCostsInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ShippingCostsInsert`(__WEIGHT INT, __NONE DECIMAL(16,2),
 __ZONE1 DECIMAL(16,2), __ZONE2 DECIMAL(16,2), __ZONE3 DECIMAL(16,2), __ZONE4 DECIMAL(16,2),
 __ZONE5 DECIMAL(16,2), __ZONE6 DECIMAL(16,2), __ZONE7 DECIMAL(16,2), __ZONE8 DECIMAL(16,2),
 __ZONE9 DECIMAL(16,2), __ZONE10 DECIMAL(16,2), __ZONE11 DECIMAL(16,2), __ZONE12 DECIMAL(16,2),
 __ZONE13 DECIMAL(16,2), __ZONE14 DECIMAL(16,2), __ZONE15 DECIMAL(16,2), __ZONE16 DECIMAL(16,2),
 __ZONE17 DECIMAL(16,2), __ZONE18 DECIMAL(16,2), __ZONE19 DECIMAL(16,2))
BEGIN

	INSERT INTO
		shipZoneCosts
        (WEIGHT,None,Zone1,Zone2,Zone3,Zone4,Zone5,Zone6,Zone7,Zone8,Zone9,Zone10,
         Zone11,Zone12,Zone13,Zone14,Zone15,Zone16,Zone17,Zone18,Zone19)
	VALUES
		(__WEIGHT,__NONE,__ZONE1,__ZONE2,__ZONE3,__ZONE4,__ZONE5,__ZONE6,__ZONE7,__ZONE8,
        __ZONE9,__ZONE10,__ZONE11,__ZONE12,__ZONE13,__ZONE14,__ZONE15,__ZONE16,__ZONE17,
        __ZONE18,__ZONE19);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ShippingCostsUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ShippingCostsUpdate`(__WEIGHT INT, __NONE DECIMAL(16,2),
 __ZONE1 DECIMAL(16,2), __ZONE2 DECIMAL(16,2), __ZONE3 DECIMAL(16,2), __ZONE4 DECIMAL(16,2),
 __ZONE5 DECIMAL(16,2), __ZONE6 DECIMAL(16,2), __ZONE7 DECIMAL(16,2), __ZONE8 DECIMAL(16,2),
 __ZONE9 DECIMAL(16,2), __ZONE10 DECIMAL(16,2), __ZONE11 DECIMAL(16,2), __ZONE12 DECIMAL(16,2),
 __ZONE13 DECIMAL(16,2), __ZONE14 DECIMAL(16,2), __ZONE15 DECIMAL(16,2), __ZONE16 DECIMAL(16,2),
 __ZONE17 DECIMAL(16,2), __ZONE18 DECIMAL(16,2), __ZONE19 DECIMAL(16,2))
BEGIN

	UPDATE
		shipZoneCosts
	SET
		None  = __NONE,
        Zone1 = __ZONE1,
        Zone2 = __ZONE2,
        Zone3 = __ZONE3,
        Zone4 = __ZONE4,
        Zone5 = __ZONE5,
        Zone6 = __ZONE6,
        Zone7 = __ZONE7,
        Zone8 = __ZONE8,
        Zone9 = __ZONE9,
        Zone10 = __ZONE10,
        Zone11 = __ZONE11,
        Zone12 = __ZONE12,
        Zone13 = __ZONE13,
        Zone14 = __ZONE14,
        Zone15 = __ZONE15,
        Zone16 = __ZONE16,
        Zone17 = __ZONE17,
        Zone18 = __ZONE18,
        Zone19 = __ZONE19
	WHERE
		WEIGHT = __WEIGHT;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ShipToDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ShipToDelete`(__SHIPID INT)
BEGIN

	DELETE FROM shipToAddresses WHERE ID = __SHIPID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ShipToInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ShipToInsert`(__SHIPID INT, __NAME VARCHAR(100), __STOREID VARCHAR(20), __ADDRESS VARCHAR(1000), __ADDRESS2 VARCHAR(1000), __CITY VARCHAR(100), __PROVINCE VARCHAR(100), __COUNTRY VARCHAR(100), __POSTAL VARCHAR(20), __PHONE VARCHAR(20), __FAX VARCHAR(20), __TAXGROUP VARCHAR(6), __SHIPZONE VARCHAR(6))
BEGIN

	INSERT INTO
		shipToAddresses
			(ID,NAME,STOREID,ADDRESS,ADDRESS2,CITY,PROVINCE,COUNTRY,POSTAL,PHONE,FAX,TAXGROUP,SHIPZONE)
	VALUES
		(__SHIPID,__NAME,__STOREID,__ADDRESS,__ADDRESS2,__CITY,__PROVINCE,__COUNTRY,__POSTAL,__PHONE,__FAX,__TAXGROUP,__SHIPZONE);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ShipToUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_ShipToUpdate`(__SHIPID INT, __NAME VARCHAR(100), __STOREID VARCHAR(20), __ADDRESS VARCHAR(1000), __ADDRESS2 VARCHAR(1000), __CITY VARCHAR(100), __PROVINCE VARCHAR(100), __COUNTRY VARCHAR(100), __POSTAL VARCHAR(20), __PHONE VARCHAR(20), __FAX VARCHAR(20), __TAXGROUP VARCHAR(6), __SHIPZONE VARCHAR(6))
BEGIN

	UPDATE
		shipToAddresses
	SET
		NAME = __NAME,
        STOREID = __STOREID,
        ADDRESS = __ADDRESS,
        ADDRESS2 = __ADDRESS2,
        CITY = __CITY,
        PROVINCE = __PROVINCE,
        COUNTRY = __COUNTRY,
        POSTAL = __POSTAL,
        PHONE = __PHONE,
        FAX = __FAX,
        TAXGROUP = __TAXGROUP,
        SHIPZONE = __SHIPZONE       
	WHERE
		ID = __SHIPID;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ShipToUserListDelete` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `importTableData_ShipToUserListDelete`(__shipid INT, __username VARCHAR(30))
BEGIN
	DELETE FROM shipToAddressesUserList WHERE ID = __shipid AND USERNAME = __username; 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_ShipToUserListInsert` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `importTableData_ShipToUserListInsert`(__shipid INT, __username VARCHAR(30))
BEGIN
	INSERT INTO shipToAddressesUserList (ID,USERNAME) VALUES (__shipid,__username); 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_SubcategoriesDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_SubcategoriesDelete`(__SUBCATID INT)
BEGIN

	DELETE FROM subcategories WHERE SUBCATID = __SUBCATID;
	DELETE FROM itemsSubcategoryLink WHERE 
		SUBCATID NOT IN (SELECT SUBCATID FROM subcategories);    

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_SubcategoriesInsert` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `importTableData_SubcategoriesInsert`(__SUBCATID INT, __CATID INT, __PARENTSUBCATID INT, __NAME VARCHAR(50), __DESCRIPTION VARCHAR(200), __ICONURL VARCHAR(200), __USERLEVEL INT)
BEGIN

    INSERT INTO
        subcategories
        (SUBCATID,CATID,PARENTSUBCATID,NAME,DESCRIPTION,ICONURL,USERLEVEL)
    VALUES
        (__SUBCATID,__CATID,__PARENTSUBCATID,__NAME,__DESCRIPTION,__ICONURL,__USERLEVEL);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_SubcategoriesUpdate` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `importTableData_SubcategoriesUpdate`(__SUBCATID INT, __CATID INT, __NAME VARCHAR(50), __DESCRIPTION VARCHAR(200), __ICONURL VARCHAR(200), __USERLEVEL INT)
BEGIN

    UPDATE
        subcategories
    SET
        CATID = __CATID,
        NAME = __NAME,
        DESCRIPTION = __DESCRIPTION,
        ICONURL = __ICONURL,
        USERLEVEL = __USERLEVEL
    WHERE
        SUBCATID = __SUBCATID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_TaxesDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_TaxesDelete`(__TAXCODE VARCHAR(6))
BEGIN

	DELETE FROM taxes WHERE TAXCODE = __TAXCODE;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_TaxesInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_TaxesInsert`(__TAXCODE VARCHAR(6), __TAXGROUPNAME VARCHAR(100), __TAXNAME1 VARCHAR(100), __TAXNAME2 VARCHAR(100), __TAXNAME3 VARCHAR(100), __TAXNAME4 VARCHAR(100), __TAXNAME5 VARCHAR(100), __TAXRATE1 DECIMAL(19,5), __TAXRATE2 DECIMAL(19,5), __TAXRATE3 DECIMAL(19,5), __TAXRATE4 DECIMAL(19,5), __TAXRATE5 DECIMAL(19,5))
BEGIN

	INSERT INTO 
		taxes
        (TAXCODE,TAXGROUPNAME,TAXNAME1,TAXNAME2,TAXNAME3,TAXNAME4,TAXNAME5,TAXRATE1,TAXRATE2,TAXRATE3,TAXRATE4,TAXRATE5)
	VALUES
		(__TAXCODE,__TAXGROUPNAME,__TAXNAME1,__TAXNAME2,__TAXNAME3,__TAXNAME4,__TAXNAME5,__TAXRATE1,__TAXRATE2,__TAXRATE3,__TAXRATE4,__TAXRATE5);
        
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_TaxesUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_TaxesUpdate`(__TAXCODE VARCHAR(6), __TAXGROUPNAME VARCHAR(100), __TAXNAME1 VARCHAR(100), __TAXNAME2 VARCHAR(100), __TAXNAME3 VARCHAR(100), __TAXNAME4 VARCHAR(100), __TAXNAME5 VARCHAR(100), __TAXRATE1 DECIMAL(19,5), __TAXRATE2 DECIMAL(19,5), __TAXRATE3 DECIMAL(19,5), __TAXRATE4 DECIMAL(19,5), __TAXRATE5 DECIMAL(19,5))
BEGIN

	UPDATE
		taxes
	SET
		TAXGROUPNAME = __TAXGROUPNAME,
        TAXNAME1 = __TAXNAME1,
        TAXNAME2 = __TAXNAME2,
        TAXNAME3 = __TAXNAME3,
        TAXNAME4 = __TAXNAME4,
        TAXNAME5 = __TAXNAME5,
        TAXRATE1 = __TAXRATE1,
        TAXRATE2 = __TAXRATE2,
        TAXRATE3 = __TAXRATE3,
        TAXRATE4 = __TAXRATE4,
        TAXRATE5 = __TAXRATE5
	WHERE
		TAXCODE = __TAXCODE;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_UserDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_UserDelete`(__USERNAME VARCHAR(20))
BEGIN

	DELETE FROM user WHERE USERNAME = __USERNAME;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_UserInsert` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_UserInsert`(__USERNAME VARCHAR(50), __BRANCH VARCHAR(20), __PLAINPASSWORD VARCHAR(50), __FIRSTNAME VARCHAR(100), __LASTNAME VARCHAR(100), __EMAIL VARCHAR(100), __LANGUAGE VARCHAR(2), __USERLEVEL INT, __STATUS INT, __COMMENTS VARCHAR(1000))
BEGIN

	INSERT INTO
		user
        (USERNAME,BRANCH,PLAINPASSWORD,PASSWORD,FIRSTNAME,LASTNAME,EMAIL,LANGUAGE,
         USERLEVEL,STATUS,COMMENTS,CREATEDDATE,MODIFIEDDATE)
	VALUES
		(__USERNAME,__BRANCH,__PLAINPASSWORD,CONCAT('*', UPPER(SHA1(UNHEX(SHA1(__PLAINPASSWORD))))),__FIRSTNAME,
        __LASTNAME,__EMAIL,__LANGUAGE,__USERLEVEL,__STATUS,__COMMENTS,CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP);


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `importTableData_UserUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `importTableData_UserUpdate`(__USERNAME VARCHAR(50), __BRANCH VARCHAR(20), __PLAINPASSWORD VARCHAR(50), __FIRSTNAME VARCHAR(100), __LASTNAME VARCHAR(100), __EMAIL VARCHAR(100), __LANGUAGE VARCHAR(2), __USERLEVEL INT, __STATUS INT, __COMMENTS VARCHAR(1000))
BEGIN

	UPDATE
		user
	SET
		BRANCH = __BRANCH,
        PLAINPASSWORD = __PLAINPASSWORD,
        PASSWORD = CONCAT('*', UPPER(SHA1(UNHEX(SHA1(__PLAINPASSWORD))))),
        FIRSTNAME = __FIRSTNAME,
        LASTNAME = __LASTNAME,
        EMAIL = __EMAIL,
        LANGUAGE = __LANGUAGE,
        USERLEVEL = __USERLEVEL,
        STATUS = __STATUS,
        COMMENTS = __COMMENTS
	WHERE
		USERNAME = __USERNAME;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertPromoCategory` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `InsertPromoCategory`(__CODE VARCHAR(50), __CATID INT)
BEGIN
  INSERT INTO 
    promosCategories 
  (CODE,CATID)
    VALUES 
  (__CODE,__CATID);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertPromoItems` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertPromoItems`(__CODE VARCHAR(50), __ITEMID INT)
BEGIN
	INSERT INTO 
		promosItems 
	(CODE,ITEMID)
    VALUES 
	(__CODE,__ITEMID);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `MakeGeneratedItem` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `MakeGeneratedItem`(__USERNAME VARCHAR(50), __ITEMID INT, __UPLOADDIR VARCHAR(500), __DESCR VARCHAR(500), __MGRAPPROVAL BIT, __PREFIX VARCHAR(10))
BEGIN

DECLARE __NEXTCOUNT INT;
DECLARE __CHKCODE INT;
DECLARE __APPROVALCODE VARCHAR(50);
SELECT COUNT(*)+1 FROM items WHERE PARENTITEM = __ITEMID LIMIT 1 INTO __NEXTCOUNT;

SET __CHKCODE = 1;
WHILE __CHKCODE >= 1 DO
  SET __APPROVALCODE = MD5(UUID());
    SET __CHKCODE = (SELECT COUNT(*) FROM items WHERE APPROVALLINK = @__APPROVALCODE);
END WHILE;

INSERT INTO
  items
    (TYPE,CODE,DESCRIPTION,PRODUCEDETAIL,SIZE,NUMPAGES,STATUS,MINLEVEL,LEADTIME,
     DISPLAYIMAGENAME,DISPLAYIMAGEPATH,PROOFPDFNAME,PROOFPDFPATH,
     PARENTITEM,ARCHIVED,GENERATEDBY,ASSETAPPROVED,APPROVALLINK,PARAMETERSFILENAME,
     PARAMETERSFILEPATH,SHIPBOXINSLEEVE,SKIPPDFPERMISSIONCHECK,DLSUGGESTIONS)
SELECT
  3 AS TYPE,
    CONCAT(CODE,'-', RIGHT(CONCAT('00000',__NEXTCOUNT),5)) AS CODE,
    CASE 
    WHEN LENGTH(__DESCR) > 0 THEN CONCAT(DESCRIPTION,'  (',__DESCR,')')
        ELSE CONCAT(DESCRIPTION,'')
  END  AS DESCRIPTION,
    PRODUCEDETAIL AS PRODUCEDETAIL,
    SIZE AS SIZE,
    NUMPAGES AS NUMPAGES,
    STATUS AS STATUS,
    MINLEVEL AS MINLEVEL,
    LEADTIME AS LEADTIME,
    'PLACEHOLDER' AS DISPLAYIMAGENAME,
    'PLACEHOLDER' AS DISPLAYIMAGEPATH,
    'PLACEHOLDER' AS PROOFPDFNAME,
    'PLACEHOLDER' AS PROOFPDFPATH,
    ID AS PARENTITEM,
    0 AS ARCHIVED,
    __USERNAME AS GENERATEDBY,
    CASE WHEN __MGRAPPROVAL = 0 THEN 1 ELSE 0 END AS ASSETAPPROVED,
  CASE WHEN __MGRAPPROVAL = 0 THEN NULL ELSE __APPROVALCODE END AS APPROVALLINK,
    '' AS PARAMETERSFILENAME,
    '' AS PARAMETERSFILEPATH,
    SHIPBOXINSLEEVE,
    SKIPPDFPERMISSIONCHECK,
    DLSUGGESTIONS
FROM
  items ITEMDATA
WHERE
  ID = __ITEMID;
    
SET @INSERTID = LAST_INSERT_ID();
SET @BRANCHEMAIL = (SELECT MANAGEMENTEMAIL FROM branches WHERE CODE IN (SELECT BRANCH FROM user WHERE USERNAME = __USERNAME));
SET @BRANCHCODE = (SELECT BRANCH FROM user WHERE USERNAME = __USERNAME);
SELECT ID AS INSERTID,APPROVALLINK, @BRANCHEMAIL AS BEMAIL, CODE FROM items WHERE ID = @INSERTID;

UPDATE
  items
SET
    DISPLAYIMAGENAME = CONCAT(__PREFIX,@INSERTID,'-',@BRANCHCODE,'-',CODE,'.jpg'),
    DISPLAYIMAGEPATH = CONCAT(__UPLOADDIR,__PREFIX,@INSERTID,'-',@BRANCHCODE,'-',CODE,'.jpg'),
    PROOFPDFNAME = CONCAT(__PREFIX,@INSERTID,'-',@BRANCHCODE,'-',CODE,'.pdf'),
    PROOFPDFPATH = CONCAT(__UPLOADDIR,__PREFIX,@INSERTID,'-',@BRANCHCODE,'-',CODE,'.pdf')
WHERE
  ID = @INSERTID;

INSERT INTO
  itemPricing
    (ITEMID,QTY,PRICE,LENGTH,WIDTH,HEIGHT,WEIGHT,PIECES,ITEMOPTION)
SELECT
  @INSERTID AS ITEMID,
    QTY,
    PRICE,
    LENGTH,
    WIDTH,
    HEIGHT,
    WEIGHT,
    PIECES,
    ITEMOPTION
FROM
  itemPricing
WHERE
  ITEMID = __ITEMID;
    
INSERT INTO
  itemsBranchesLink
    (ITEMID,BRANCHCODE)
SELECT
  @INSERTID AS ITEMID,
    BRANCH AS BRANCHCODE
FROM
  user
WHERE
  USERNAME = __USERNAME;
    
INSERT INTO
  itemsSubcategoryLink
    (ITEMID,SUBCATID)
SELECT
  @INSERTID AS ITEMID,
    SUBCATID
FROM
  itemsSubcategoryLink
WHERE
  ITEMID = __ITEMID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ManualChangeOrderShipToAddress` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ManualChangeOrderShipToAddress`(__USERNAME VARCHAR(50), __NAME VARCHAR(100), __STOREID VARCHAR(20), __ADDRESS VARCHAR(1000), __ADDRESS2 VARCHAR(1000), __CITY VARCHAR(100), __PROVINCE VARCHAR(100), __COUNTRY VARCHAR(100), __POSTAL VARCHAR(20), __PHONE VARCHAR(20), __FAX VARCHAR(20))
BEGIN


SET SQL_SAFE_UPDATES = 0;


SET @TGROUPCHECK = (SELECT COUNT(*) FROM taxes WHERE TAXCODE = __PROVINCE);

IF @TGROUPCHECK <= 0 THEN
	SIGNAL SQLSTATE '45000'
	SET MESSAGE_TEXT = 'No Tax group matching province';	
ELSE 
	UPDATE
		orderHeader
	SET
		SHIPNAME = __NAME,
        SHIPSTOREID = __STOREID,
		SHIPADDRESS = __ADDRESS,
        SHIPADDRESS2 = __ADDRESS2,
		SHIPCITY = __CITY,
		SHIPPROVINCE = __PROVINCE,
		SHIPCOUNTRY = __COUNTRY,
		SHIPPOSTAL = __POSTAL,
		SHIPCONTACTPHONE = __PHONE,
		SHIPCONTACTFAX = __FAX,
		SHIPTAXGROUP = __PROVINCE
	WHERE 
		USERNAME  = __USERNAME AND 
		ORDERSTATUS = 'CART';
END IF;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ModifyAdItem` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ModifyAdItem`(__ID INT, __NAME VARCHAR(1000), __STARTDATE DATE, __ENDDATE DATE, __LANGUAGE VARCHAR(2), __SORTORDER INT, __URL VARCHAR(500), __ADPATH VARCHAR(200))
BEGIN

	UPDATE 
		AdItems
	SET
		NAME = __NAME,
        STARTDATE = __STARTDATE,
        ENDDATE = __ENDDATE,
        LANGUAGE = __LANGUAGE,
        SORTORDER = __SORTORDER,
        IMAGEPATH = CASE WHEN __ADPATH = 'NOUPDATE' THEN IMAGEPATH ELSE __ADPATH END,
        URL = __URL
	WHERE
		ID = __ID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ModifyBranchAddress` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ModifyBranchAddress`(__CODE VARCHAR(20), __LANG VARCHAR(2), __ISMAIN TINYINT(1), __ADDRESS VARCHAR(1000), __ADDRESS2 VARCHAR(1000), __CITY VARCHAR(100), __PROVINCE VARCHAR(100), __COUNTRY VARCHAR(100), __POSTALCODE VARCHAR(20), __PHONE VARCHAR(20), __FAX VARCHAR(20))
BEGIN

	SET @FOUNDMATCH := 0;
    SELECT @FOUNDMATCH := COUNT(*) FROM branchesAddresses WHERE CODE = __CODE AND LANGUAGE = __LANG;

	IF @FOUNDMATCH > 0 THEN
		UPDATE
			branchesAddresses
		SET
			ISMAIN = __ISMAIN, 
			ADDRESS = __ADDRESS, 
            ADDRESS2 = __ADDRESS2,
			CITY = __CITY, 
			PROVINCE = __PROVINCE, 
			COUNTRY = __COUNTRY, 
			POSTALCODE = __POSTALCODE, 
			PHONE = __PHONE, 
			FAX = __FAX
		WHERE
			CODE = __CODE AND
			LANGUAGE = __LANG;  
		UPDATE 
			orderHeader 
		SET 
			SHIPCONTACTPHONE = __PHONE, 
            SHIPCONTACTFAX = __FAX, 
            SHIPADDRESS = __ADDRESS, 
            SHIPADDRESS2 = __ADDRESS2, 
            SHIPCITY = __CITY, 
            SHIPPROVINCE = __PROVINCE, 
            SHIPCOUNTRY = __COUNTRY, 
            SHIPPOSTAL = __POSTALCODE, 
			REQCONTACTPHONE = __PHONE, 
            REQCONTACTFAX = __FAX, 
            REQADDRESS = __ADDRESS, 
            REQADDRESS2 = __ADDRESS2, 
            REQCITY = __CITY, 
            REQPROVINCE = __PROVINCE, 
            REQCOUNTRY = __COUNTRY, 
            REQPOSTAL = __POSTALCODE
		WHERE 
			USERNAME IN (SELECT USERNAME FROM user WHERE branch = __CODE) AND 
			ORDERSTATUS = 'CART';
    ELSE
		INSERT INTO
			branchesAddresses
			(CODE, LANGUAGE, ISMAIN, ADDRESS, ADDRESS2, CITY, PROVINCE, COUNTRY, POSTALCODE, PHONE, FAX)
		VALUES
			(__CODE, __LANG, __ISMAIN, __ADDRESS, __ADDRESS2, __CITY, __PROVINCE, __COUNTRY, __POSTALCODE, __PHONE, __FAX);
	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ModifyBranchProfile` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ModifyBranchProfile`(__CODE VARCHAR(20), __NAME VARCHAR(100), __MGREMAIL VARCHAR(100), __TREEMAIL VARCHAR(100), __TAXGROUP VARCHAR(6), __SKIN VARCHAR(20), __STATUS INTEGER, __COMMENTS VARCHAR(1000), __SHIPZONE VARCHAR(20))
BEGIN

UPDATE
	branches
SET
	CODE = __CODE,
    NAME = __NAME,
    MANAGEMENTEMAIL = __MGREMAIL,
    TREASUREREMAIL = __TREEMAIL,
    TAXGROUP = __TAXGROUP,
    SKIN = __SKIN,
    STATUS = __STATUS,
    COMMENTS = __COMMENTS,
    SHIPZONE = __SHIPZONE,
    MODIFIEDDATE = CURRENT_TIMESTAMP
WHERE
	CODE = __CODE;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ModifyCategory` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ModifyCategory`(__CATID INTEGER, __NAME VARCHAR(50), __DESCRIPTION VARCHAR(200), __LANGUAGE VARCHAR(2), __ICON VARCHAR(200), __USERLEVEL INTEGER, __COMMENTS VARCHAR(1000))
BEGIN
	UPDATE
		categories
	SET
		NAME = __NAME,
        DESCRIPTION = __DESCRIPTION,
        ICONURL = __ICON,
        LANGUAGE = __LANGUAGE,
        USERLEVEL = __USERLEVEL,
        COMMENTS  = __COMMENTS,
        MODIFIEDDATE = CURRENT_TIMESTAMP
	WHERE
		CATID = __CATID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ModifyItem` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `ModifyItem`(IN `__ITEMID` INT, IN `__TYPE` INT, IN `__CODE` VARCHAR(50), IN `__DESCRIPTION` VARCHAR(200), IN `__PRODUCEDETAIL` VARCHAR(1000), IN `__SIZE` VARCHAR(50), IN `__NUMPAGES` VARCHAR(50), IN `__PRINTINGTYPE` VARCHAR(50), IN `__STOCK` VARCHAR(50), IN `__STATUS` INT, IN `__MINLEVEL` INT, IN `__LEADTIME` INT, IN `__DISPLAYNAME` VARCHAR(1000), IN `__PROOFNAME` VARCHAR(1000), IN `__PARAMETERSNAME` VARCHAR(1000), IN `__UPLOADDIR` VARCHAR(1000), IN `__PREVIEWNAME` VARCHAR(1000), IN `__SHIPBOXINSLEEVE` INT, IN `__SKIPPDFPERMISSIONCHECK` INT, IN `__DLSUGGESTIONS` VARCHAR(1000))
BEGIN
	UPDATE
		items
	SET
		TYPE = __TYPE,
        CODE = __CODE,
        DESCRIPTION = __DESCRIPTION,
        PRODUCEDETAIL = __PRODUCEDETAIL,
        SIZE = __SIZE,
        NUMPAGES = __NUMPAGES,
        STATUS = __STATUS,
        MINLEVEL = __MINLEVEL,
        LEADTIME = __LEADTIME,
        DISPLAYIMAGENAME = __DISPLAYNAME,
        PROOFPDFNAME = __PROOFNAME,
        PREVIEWIMAGENAME = __PREVIEWNAME,
        PARAMETERSFILENAME = __PARAMETERSNAME,
        DISPLAYIMAGEPATH = CASE WHEN LENGTH(__DISPLAYNAME) > 0 THEN CONCAT(__UPLOADDIR,__DISPLAYNAME) ELSE '' END,
        PROOFPDFPATH = CASE WHEN LENGTH(__PROOFNAME) > 0 THEN CONCAT(__UPLOADDIR,__PROOFNAME) ELSE '' END,
        PARAMETERSFILEPATH = CASE WHEN LENGTH(__PARAMETERSNAME) > 0 THEN CONCAT(__UPLOADDIR,__PARAMETERSNAME) ELSE '' END,
        PREVIEWIMAGEPATH = CASE WHEN LENGTH(__PREVIEWNAME) > 0 THEN CONCAT(__UPLOADDIR,__PREVIEWNAME) ELSE '' END,
        SHIPBOXINSLEEVE = __SHIPBOXINSLEEVE,
        SKIPPDFPERMISSIONCHECK = __SKIPPDFPERMISSIONCHECK,
        DLSUGGESTIONS = __DLSUGGESTIONS,
        STOCK=__STOCK,
        PRINTINGTYPE=__PRINTINGTYPE
	WHERE
        ID = __ITEMID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ModifyNewsItem` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ModifyNewsItem`(__ID INTEGER, __STARTDATE DATE, __ENDDATE DATE, __LANGUAGE VARCHAR(2), __TITLE VARCHAR(1000), __MESSAGE VARCHAR(1000))
BEGIN
	UPDATE
		newsItems
	SET
		STARTDATE = __STARTDATE,
        ENDDATE = __ENDDATE,
        LANGUAGE = __LANGUAGE,
        TITLE = __TITLE,
        MESSAGE = __MESSAGE
	WHERE
		ID = __ID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ModifyPromo` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `ModifyPromo`(__NAME VARCHAR(100), __CODE VARCHAR(50), __CODETYPE VARCHAR(12), __PROMOVALUE DECIMAL(19,2), __QTYTYPE VARCHAR(12), __MAXQTY INT, __MINQTY INT, __EXACTQTY INT, __STARTDATE DATE, __ENDDATE DATE, __SINGLEUSE INT, __REDEEMED INT, __SINGLEITEM INT)
BEGIN

 UPDATE 
 promos
 SET
 NAME = __NAME,
 CODETYPE = __CODETYPE,
 PROMOVALUE = __PROMOVALUE,
 QTYTYPE = __QTYTYPE,
 MAXQTY = __MAXQTY,
 MINQTY = __MINQTY,
 EXACTQTY = __EXACTQTY,
 STARTDATE = __STARTDATE,
 ENDDATE = __ENDDATE,
 SINGLEUSE = __SINGLEUSE,
 REDEEMED = __REDEEMED,
 SINGLEITEM = __SINGLEITEM
 WHERE
 CODE = __CODE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ModifyShipToAddress` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ModifyShipToAddress`(__ID INT, __NAME VARCHAR(100), __STOREID VARCHAR(20), __ADDRESS VARCHAR(1000), __ADDRESS2 VARCHAR(1000), __CITY VARCHAR(100),
__PROVINCE VARCHAR(100), __COUNTRY VARCHAR(100), __POSTAL VARCHAR(20), __PHONE VARCHAR(20), __FAX VARCHAR(20),
__TAXGROUP VARCHAR(6), __SHIPZONE VARCHAR(6))
BEGIN

UPDATE
	shipToAddresses
SET
	NAME = __NAME,
    STOREID = __STOREID,
    ADDRESS = __ADDRESS,
    ADDRESS2 = __ADDRESS2,
    CITY = __CITY,
    PROVINCE = __PROVINCE,
    COUNTRY = __COUNTRY,
    POSTAL = __POSTAL,
    PHONE = __PHONE,
    FAX = __FAX,
    TAXGROUP = __TAXGROUP,
    SHIPZONE = __SHIPZONE
WHERE
	ID = __ID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ModifySubCategory` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `ModifySubCategory`(__SUBCATID INTEGER, __CATID INTEGER, __NAME VARCHAR(50), __DESCRIPTION VARCHAR(200), __ICONURL VARCHAR(200), __USERLEVEL INT)
BEGIN
    UPDATE
        subcategories
    SET
        NAME = __NAME,
        DESCRIPTION = __DESCRIPTION,
        ICONURL = __ICONURL,
        USERLEVEL = __USERLEVEL
    WHERE
        SUBCATID = __SUBCATID AND
        CATID = __CATID ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ModifyUserProfile` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ModifyUserProfile`(UNAME VARCHAR(50), __BRANCH VARCHAR(20), PASS VARCHAR(50), FNAME VARCHAR(50), LNAME VARCHAR(50), LANG VARCHAR(2), STAT INTEGER, USERLVL INTEGER, COMMENTS VARCHAR(1000), EMAIL VARCHAR(100))
BEGIN

UPDATE
	`user`
SET
	`BRANCH` = __BRANCH, 
	`PASSWORD` = CONCAT('*', UPPER(SHA1(UNHEX(SHA1(PASS))))),
    `PLAINPASSWORD` = PASS,
    `FIRSTNAME` = FNAME,
    `LASTNAME` = LNAME,
    `LANGUAGE` = LANG,
    `STATUS` = STAT,
    `USERLEVEL` = USERLVL,
    `COMMENTS` = COMMENTS,
    `EMAIL` = EMAIL,
    `MODIFIEDDATE` = CURRENT_TIMESTAMP
WHERE
	`USERNAME` = UNAME;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RejectItem` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RejectItem`(__CODE VARCHAR(200))
BEGIN
	SELECT
		ITEM.ID AS ITEMID,
        USERS.EMAIL AS USEREMAIL,
        ITEM.DISPLAYIMAGEPATH
	FROM
		items ITEM 
	INNER JOIN
		user USERS ON 
			USERS.USERNAME = ITEM.GENERATEDBY
	WHERE 
		APPROVALLINK = __CODE;

	UPDATE
		items
	SET
		STATUS = 0,
		ARCHIVED = 1,
		ASSETAPPROVED = 0,
        APPROVALLINK = NULL
	WHERE
		APPROVALLINK = __CODE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RejectOrder` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RejectOrder`(__APPROVALLINK NVARCHAR(100))
BEGIN


SELECT
	HDR.*,
    DTL.*,
    ITEM.TYPE,
    USER.FIRSTNAME AS USER_FIRSTNAME,
    USER.LASTNAME AS USER_LASTNAME,
    USER.EMAIL AS USER_EMAIL,
    USER.LANGUAGE AS USER_LANGUAGE,
    BRANCH.NAME AS BRANCH_NAME,
    BRANCH.MANAGEMENTEMAIL AS BRANCH_MANAGEMENTEMAIL,
    COALESCE(BRANCHADDRESS.LANGUAGE,'EN') AS BRANCH_LANGUAGE,
    'CANCELLED' AS REALSTATUS
FROM
	orderHeader HDR
INNER JOIN
	orderDetail DTL ON 
		HDR.ORDERID = DTL.ORDERID
INNER JOIN
	items ITEM ON
		DTL.ITEMID = ITEM.ID
INNER JOIN
	user USER ON 
		HDR.USERNAME = USER.USERNAME
INNER JOIN
	branches BRANCH ON 
		USER.BRANCH = BRANCH.CODE
LEFT OUTER JOIN
	(SELECT * FROM branchesAddresses WHERE ISMAIN = 1) BRANCHADDRESS ON 
		BRANCH.CODE = BRANCHADDRESS.CODE
WHERE
	HDR.APPROVALLINK  COLLATE utf8_unicode_ci = __APPROVALLINK AND
    HDR.APPROVALLINK IS NOT NULL;
    
UPDATE
	orderHeader
SET
	APPROVALLINK = NULL,
    ORDERSTATUS = 'REJECTED',
    ORDERMODIFIED = CURRENT_TIMESTAMP
WHERE
	APPROVALLINK  COLLATE utf8_unicode_ci = __APPROVALLINK AND
    APPROVALLINK IS NOT NULL;	

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RemoveItemFromCart` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RemoveItemFromCart`(__USERNAME NVARCHAR(20), __ITEMLINE INT)
BEGIN
	
    DELETE
    FROM
		orderDetail
	WHERE
		ITEMLINE = __ITEMLINE AND
        ORDERID IN (SELECT ORDERID FROM orderHeader WHERE USERNAME COLLATE utf8_unicode_ci = __USERNAME AND ORDERSTATUS = 'CART')
	;
    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RemoveOldCartOrders` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RemoveOldCartOrders`()
BEGIN

DELETE FROM orderDetail WHERE ORDERID IN (
SELECT ORDERID FROM orderHeader WHERE ORDERSTATUS = 'CART' AND ORDERMODIFIED < DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -24 HOUR)
);

DELETE FROM orderHeader WHERE ORDERSTATUS = 'CART' AND ORDERMODIFIED < DATE_ADD(CURRENT_TIMESTAMP,INTERVAL -24 HOUR);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RemovePromoFromCart` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RemovePromoFromCart`(__USERNAME VARCHAR(20), __PROMO VARCHAR(50))
BEGIN
	
    DELETE
    FROM
		orderPromos
	WHERE
		PROMOCODE = __PROMO AND
        ORDERID IN (SELECT ORDERID FROM orderHeader WHERE USERNAME COLLATE utf8_unicode_ci = __USERNAME AND ORDERSTATUS = 'CART')
	;

    UPDATE
		promos
	SET
		REDEEMED = 0
	WHERE
		CODE = __PROMO AND
        SINGLEUSE = 1;
    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ReportOrderBranches` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `ReportOrderBranches`(IN `__STARTDATE` DATE, IN `__ENDDATE` DATE, IN `__ACCESS` BOOLEAN, IN `__USERNAME` VARCHAR(50))
BEGIN

	SELECT
		HDR.ORDERID,
        HDR.ORDERREF,
        HDR.PAYMENTREF,
        HDR.ORDERSTATUS,
        HDR.PONUMBER,
        HDR.BILLINGCATEGORY,
        HDR.SPECIALINSTRUCTIONS,
        CASE WHEN USER.USERNAME IS NOT NULL THEN CONCAT(USER.FIRSTNAME,' ',USER.LASTNAME) ELSE 'Unknown User' END AS ORDERBY,
        DATE(ORDERDATE) AS ORDERDATE,
        DATE(APPROVALDATE) AS APPROVALDATE,
        HDR.SHIPNAME,
        CONCAT(HDR.SHIPCONTACTFIRSTNAME,' ',HDR.SHIPCONTACTLASTNAME) AS SHIPCONTACT,
        HDR.SHIPCONTACTEMAIL,
        HDR.SHIPADDRESS,
        HDR.SHIPCITY,
        HDR.SHIPPROVINCE,
        HDR.SHIPPOSTAL,
        HDR.SHIPPINGCOST,
        COALESCE(DTLSUM.EXTPRICE,0) AS EXTPRICE,
        COALESCE(DTLSUM.TAXAMT,0) + HDR.SHIPPINGCOSTTAXAMT1 + HDR.SHIPPINGCOSTTAXAMT2 + HDR.SHIPPINGCOSTTAXAMT3 + HDR.SHIPPINGCOSTTAXAMT4 + HDR.SHIPPINGCOSTTAXAMT5 AS TAXAMT,
        COALESCE(DTLSUM.EXTPRICE,0) + COALESCE(DTLSUM.TAXAMT,0) + HDR.SHIPPINGCOST + HDR.SHIPPINGCOSTTAXAMT1 + HDR.SHIPPINGCOSTTAXAMT2 + HDR.SHIPPINGCOSTTAXAMT3 + HDR.SHIPPINGCOSTTAXAMT4 + HDR.SHIPPINGCOSTTAXAMT5 AS GRANDTOTAL,
        COALESCE(SHDR.TRACKNUM,'No Tracking #') AS TRACKNUM,
        COALESCE(DATE(SHDR.SHIPDATE),'') AS SHIPDATE,
        DTL.ITEMID,
        COALESCE(DTL.ITEMCODE,'Unknown Item') AS ITEMCODE,
        COALESCE(DTL.ITEMDESC,'Unknown Item') AS ITEMDESC,
        COALESCE(DTL.ORDERQTY,0) AS ORDERQTY,
        COALESCE(DTL.SHIPTODATEQTY,0) AS SHIPTODATEQTY,
        COALESCE(DTL.EXTPRICE,0) AS ITEMEXTPRICE,
        COALESCE(DTL.TAXAMT1 + DTL.TAXAMT2 + DTL.TAXAMT3 + DTL.TAXAMT4 + DTL.TAXAMT5,0) AS ITEMTAXES,
        COALESCE(DTL.EXTPRICE + DTL.TAXAMT1 + DTL.TAXAMT2 + DTL.TAXAMT3 + DTL.TAXAMT4 + DTL.TAXAMT5,0) AS ITEMTOTAL,
        BRANCH.NAME AS BRANCHNAME,
        BRANCH.CODE AS BRANCHCODE,
        COALESCE(BRANCH.SKIN,'NA') AS SKINBRAND
	FROM
		orderHeader HDR
	LEFT OUTER JOIN
		( SELECT ORDERID,SUM(EXTPRICE) AS EXTPRICE, SUM(TAXAMT1+TAXAMT2+TAXAMT3+TAXAMT4+TAXAMT5) AS TAXAMT FROM orderDetail GROUP BY ORDERID) DTLSUM ON 
			HDR.ORDERID = DTLSUM.ORDERID
	LEFT OUTER JOIN
		orderDetail DTL ON 
			HDR.ORDERID = DTL.ORDERID
	LEFT OUTER JOIN
		( SELECT ORDERID,MAX(TRACKNUM) AS TRACKNUM, MAX(SHIPDATE) AS SHIPDATE FROM shipmentHeader GROUP BY ORDERID ) SHDR ON 
			HDR.ORDERID = SHDR.ORDERID
	LEFT OUTER JOIN
		user USER ON 
			HDR.USERNAME = USER.USERNAME
	LEFT OUTER JOIN
		branches BRANCH ON 
			USER.BRANCH = BRANCH.CODE
	WHERE
		HDR.ORDERDATE BETWEEN __STARTDATE AND DATE_ADD(__ENDDATE,INTERVAL 1 DAY) AND
		HDR.ORDERSTATUS NOT IN ('WAITMA','CART') AND
        (__ACCESS = 1 OR USER.BRANCH IN (SELECT BRANCH FROM user WHERE USERNAME = __USERNAME))
	ORDER BY
		COALESCE(BRANCH.SKIN,'NA'),
		BRANCH.NAME,
		ORDERDATE,
        HDR.ORDERID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ReportOrderDetailed` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `ReportOrderDetailed`(IN `__STARTDATE` DATE, IN `__ENDDATE` DATE, IN `__ACCESS` BOOLEAN, IN `__USERNAME` VARCHAR(50))
BEGIN

	SELECT
		HDR.ORDERID,
        HDR.ORDERREF,
        HDR.ORDERSTATUS,
        HDR.PAYMENTREF,
		HDR.PONUMBER,
        HDR.BILLINGCATEGORY,
        CASE WHEN USER.USERNAME IS NOT NULL THEN CONCAT(USER.FIRSTNAME,' ',USER.LASTNAME) ELSE 'Unknown User' END AS ORDERBY,
        DATE(ORDERDATE) AS ORDERDATE,
        HDR.SHIPNAME,
        CONCAT(HDR.SHIPCONTACTFIRSTNAME,' ',HDR.SHIPCONTACTLASTNAME) AS SHIPCONTACT,
        HDR.SHIPCONTACTEMAIL,
        HDR.SHIPADDRESS,
        HDR.SHIPCITY,
        HDR.SHIPPROVINCE,
        HDR.SHIPPOSTAL,
        HDR.SHIPPINGCOST,
        COALESCE(DTLSUM.EXTPRICE,0) AS EXTPRICE,
        COALESCE(DTLSUM.TAXAMT,0) + HDR.SHIPPINGCOSTTAXAMT1 + HDR.SHIPPINGCOSTTAXAMT2 + HDR.SHIPPINGCOSTTAXAMT3 + HDR.SHIPPINGCOSTTAXAMT4 + HDR.SHIPPINGCOSTTAXAMT5 AS TAXAMT,
        COALESCE(DTLSUM.EXTPRICE,0) + COALESCE(DTLSUM.TAXAMT,0) + HDR.SHIPPINGCOST + HDR.SHIPPINGCOSTTAXAMT1 + HDR.SHIPPINGCOSTTAXAMT2 + HDR.SHIPPINGCOSTTAXAMT3 + HDR.SHIPPINGCOSTTAXAMT4 + HDR.SHIPPINGCOSTTAXAMT5 AS GRANDTOTAL,
        COALESCE(SHDR.TRACKNUM,'No Tracking #') AS TRACKNUM,
        COALESCE(DATE(SHDR.SHIPDATE),'') AS SHIPDATE,
        DTL.ITEMID,
        COALESCE(DTL.ITEMCODE,'Unknown Item') AS ITEMCODE,
        COALESCE(DTL.ITEMDESC,'Unknown Item') AS ITEMDESC,
        COALESCE(DTL.ORDERQTY,0) AS ORDERQTY,
        COALESCE(DTL.SHIPTODATEQTY,0) AS SHIPTODATEQTY,
        COALESCE(DTL.EXTPRICE,0) AS ITEMEXTPRICE,
        COALESCE(DTL.TAXAMT1 + DTL.TAXAMT2 + DTL.TAXAMT3 + DTL.TAXAMT4 + DTL.TAXAMT5,0) AS ITEMTAXES,
        COALESCE(DTL.EXTPRICE + DTL.TAXAMT1 + DTL.TAXAMT2 + DTL.TAXAMT3 + DTL.TAXAMT4 + DTL.TAXAMT5,0) AS ITEMTOTAL,
        COALESCE(BRANCH.SKIN,'N/A') AS SKINBRAND,
	   BRANCH.CODE AS BRANCHCODE
	FROM
		orderHeader HDR
	LEFT OUTER JOIN
		( SELECT ORDERID,SUM(EXTPRICE) AS EXTPRICE, SUM(TAXAMT1+TAXAMT2+TAXAMT3+TAXAMT4+TAXAMT5) AS TAXAMT FROM orderDetail GROUP BY ORDERID) DTLSUM ON 
			HDR.ORDERID = DTLSUM.ORDERID
	LEFT OUTER JOIN
		orderDetail DTL ON 
			HDR.ORDERID = DTL.ORDERID
	LEFT OUTER JOIN
		( SELECT ORDERID,MAX(TRACKNUM) AS TRACKNUM, MAX(SHIPDATE) AS SHIPDATE FROM shipmentHeader GROUP BY ORDERID ) SHDR ON 
			HDR.ORDERID = SHDR.ORDERID
	LEFT OUTER JOIN
		user USER ON 
			HDR.USERNAME = USER.USERNAME
	LEFT OUTER JOIN
		branches BRANCH ON 
			USER.BRANCH = BRANCH.CODE            
	WHERE
		HDR.ORDERDATE BETWEEN __STARTDATE AND DATE_ADD(__ENDDATE,INTERVAL 1 DAY) AND
		HDR.ORDERSTATUS NOT IN ('WAITMA','CART') AND
        (__ACCESS = 1 OR USER.BRANCH IN (SELECT BRANCH FROM user WHERE USERNAME = __USERNAME))
ORDER BY HDR.ORDERREF;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ReportOrderSummary` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ReportOrderSummary`(__STARTDATE DATE, __ENDDATE DATE, __ACCESS BOOLEAN, __USERNAME VARCHAR(50))
BEGIN

	SELECT
		HDR.ORDERID,
        HDR.ORDERREF,
        HDR.PAYMENTREF,
        HDR.ORDERSTATUS,
        CASE WHEN USER.USERNAME IS NOT NULL THEN CONCAT(USER.FIRSTNAME,' ',USER.LASTNAME) ELSE 'Unknown User' END AS ORDERBY,
        DATE(ORDERDATE) AS ORDERDATE,
        HDR.SHIPNAME,
        CONCAT(HDR.SHIPCONTACTFIRSTNAME,' ',HDR.SHIPCONTACTLASTNAME) AS SHIPCONTACT,
        HDR.SHIPADDRESS,
        HDR.SHIPCITY,
        HDR.SHIPPROVINCE,
        HDR.SHIPPOSTAL,
        HDR.SHIPPINGCOST,
        HDR.SHIPSTOREID,
        COALESCE(DTL.EXTPRICE,0) AS EXTPRICE,
        COALESCE(DTL.TAXAMT,0) + HDR.SHIPPINGCOSTTAXAMT1 + HDR.SHIPPINGCOSTTAXAMT2 + HDR.SHIPPINGCOSTTAXAMT3 + HDR.SHIPPINGCOSTTAXAMT4 + HDR.SHIPPINGCOSTTAXAMT5 AS TAXAMT,
        COALESCE(DTL.EXTPRICE,0) + COALESCE(DTL.TAXAMT,0) + HDR.SHIPPINGCOST + HDR.SHIPPINGCOSTTAXAMT1 + HDR.SHIPPINGCOSTTAXAMT2 + HDR.SHIPPINGCOSTTAXAMT3 + HDR.SHIPPINGCOSTTAXAMT4 + HDR.SHIPPINGCOSTTAXAMT5 AS GRANDTOTAL,
        COALESCE(SHDR.TRACKNUM,'No Tracking #') AS TRACKNUM,
        COALESCE(DATE(SHDR.SHIPDATE),'') AS SHIPDATE,
        COALESCE(BRANCH.SKIN,'N/A') AS SKINBRAND,
        BRANCH.CODE AS BRANCHCODE
	FROM
		orderHeader HDR
	LEFT OUTER JOIN
		( SELECT ORDERID,SUM(EXTPRICE) AS EXTPRICE, SUM(TAXAMT1+TAXAMT2+TAXAMT3+TAXAMT4+TAXAMT5) AS TAXAMT FROM orderDetail GROUP BY ORDERID) DTL ON 
			HDR.ORDERID = DTL.ORDERID
	LEFT OUTER JOIN
		( SELECT ORDERID,MAX(TRACKNUM) AS TRACKNUM, MAX(SHIPDATE) AS SHIPDATE FROM shipmentHeader GROUP BY ORDERID ) SHDR ON 
			HDR.ORDERID = SHDR.ORDERID
	LEFT OUTER JOIN
		user USER ON 
			HDR.USERNAME = USER.USERNAME
	LEFT OUTER JOIN
		branches BRANCH ON 
			USER.BRANCH = BRANCH.CODE
	WHERE
		HDR.ORDERDATE BETWEEN __STARTDATE AND DATE_ADD(__ENDDATE,INTERVAL 1 DAY) AND
		HDR.ORDERSTATUS NOT IN ('WAITMA','CART') AND
        (__ACCESS = 1 OR USER.BRANCH IN (SELECT BRANCH FROM user WHERE USERNAME = __USERNAME));

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ResendAssetEmail` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ResendAssetEmail`(__ITEMID INT)
BEGIN

SELECT
	ITEMS.ID AS ITEMID,
    ITEMS.APPROVALLINK,
    BRANCHES.MANAGEMENTEMAIL AS BEMAIL,
    ITEMS.DISPLAYIMAGEPATH
FROM
	items ITEMS
INNER JOIN
	user USER ON 
		ITEMS.GENERATEDBY = USER.USERNAME
INNER JOIN
	branches BRANCHES ON 
		USER.BRANCH = BRANCHES.CODE
WHERE
	ITEMS.ID = __ITEMID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SaveDiscountCalc` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SaveDiscountCalc`(__ORDERID INT, __PROMO VARCHAR(50), __CALCAMT DECIMAL(19,2), __TAXAMT1 DECIMAL(19,2), __TAXAMT2 DECIMAL(19,2), __TAXAMT3 DECIMAL(19,2), __TAXAMT4 DECIMAL(19,2), __TAXAMT5 DECIMAL(19,2))
BEGIN

	UPDATE 
		orderPromos
	SET
		CALCDISCOUNT = __CALCAMT,
        TAXAMT1 = __TAXAMT1,
        TAXAMT2 = __TAXAMT2,
        TAXAMT3 = __TAXAMT3,
        TAXAMT4 = __TAXAMT4,
        TAXAMT5 = __TAXAMT5
	WHERE
		ORDERID = __ORDERID AND
        PROMOCODE = __PROMO;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SearchForBranches` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SearchForBranches`(SEARCH VARCHAR(1000), SEARCHBY INTEGER)
BEGIN
	SELECT CODE,NAME FROM branches WHERE
    (
 		(CODE LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,1)) OR
        (NAME LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,2)) 
    );
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SearchForCategories` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SearchForCategories`(SEARCH VARCHAR(1000), SEARCHBY INTEGER)
BEGIN
	SELECT
		CATID,NAME,DESCRIPTION,LANGUAGE
	FROM
		categories
	WHERE
    (
		(NAME LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,1)) OR
        (DESCRIPTION LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,2)) OR
        (LANGUAGE LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,3))
    );
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SearchForItems` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SearchForItems`(__SEARCH VARCHAR(1000), __SEARCHBY INTEGER)
BEGIN
	SELECT DISTINCT
		ITEMS.ID,
        ITEMS.CODE,
        ITEMS.DESCRIPTION,
        CONCAT(CAT.NAME,'-',SUBCAT.NAME) AS CATNAME,
        ITEMS.TYPE,
        COALESCE(STOCK.QTYONHAND,0) AS QTYONHAND
	FROM
		items ITEMS
	LEFT OUTER JOIN itemsSubcategoryLink CATLINK ON 
		ITEMS.ID = CATLINK.ITEMID
	LEFT OUTER JOIN subcategories SUBCAT ON 
		CATLINK.SUBCATID = SUBCAT.SUBCATID
	LEFT OUTER JOIN categories CAT ON 
		SUBCAT.CATID = CAT.CATID
	LEFT OUTER JOIN itemsInventory STOCK ON
		ITEMS.ID = STOCK.ITEMID
	WHERE
    (
		(ITEMS.ID LIKE CONCAT('%',__SEARCH,'%') AND __SEARCHBY IN (0,1)) OR
        (ITEMS.CODE LIKE CONCAT('%',__SEARCH,'%') AND __SEARCHBY IN (0,2)) OR
        (ITEMS.DESCRIPTION LIKE CONCAT('%',__SEARCH,'%') AND __SEARCHBY IN (0,3)) OR
        (CONCAT(CAT.NAME,'-',SUBCAT.NAME) LIKE CONCAT('%',__SEARCH,'%') AND __SEARCHBY IN (0,4))
    )
    ORDER BY ITEMS.ID, ITEMS.CODE
    ;    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SearchForPromos` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SearchForPromos`(SEARCH VARCHAR(1000), SEARCHBY INTEGER)
BEGIN
	SELECT 
		CODE,
        NAME,
        CODETYPE,
        DATE_FORMAT(STARTDATE,'%Y-%m-%d')  AS STARTDATE,
        DATE_FORMAT(ENDDATE,'%Y-%m-%d') AS ENDDATE
	FROM promos 
    WHERE
    (
		(CODE LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,1)) OR
        (NAME LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,2))
    )
    ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SearchForShipOrders` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SearchForShipOrders`(IN `__SEARCH` VARCHAR(1000), IN `__SEARCHBY` INT)
BEGIN
	SELECT
		HDR.ORDERID,
        HDR.ORDERREF,
        DATE(HDR.ORDERDATE) AS ORDERDATE,
        HDR.SHIPNAME,
        CONCAT(HDR.SHIPCONTACTFIRSTNAME, ' - ', HDR.SHIPADDRESS, ' ', HDR.SHIPCITY, ' ', HDR.SHIPPROVINCE) AS DESTINATION,
        DATE(HDR.EXPDATE) AS EXPDATE,
        HDR.ORDERSTATUS,
        COALESCE(HDR.PAYMENTREF, '') AS PAYMENTREF,
        COALESCE(SHP.TRACKNUM, '') AS TRACKNUM
	FROM
		orderHeader HDR
		LEFT JOIN shipmentHeader SHP ON SHP.ORDERID = HDR.ORDERID
	WHERE
		(
			CONCAT(HDR.SHIPCONTACTFIRSTNAME, ' - ', HDR.SHIPADDRESS, ' ', HDR.SHIPCITY, ' ', HDR.SHIPPROVINCE) LIKE CONCAT('%', __SEARCH, '%') OR
            HDR.SHIPNAME LIKE CONCAT('%', __SEARCH, '%') OR 
            HDR.ORDERREF LIKE CONCAT('%', __SEARCH, '%') OR 
            HDR.ORDERDATE LIKE CONCAT('%', __SEARCH, '%') OR 
            HDR.EXPDATE LIKE CONCAT('%', __SEARCH, '%')
		) 
        AND (
			(__SEARCHBY = 1 AND HDR.ORDERSTATUS = 'ACTIVE') OR 
            (__SEARCHBY = 2 AND HDR.ORDERSTATUS = 'ACTIVE' AND NOW() >= DATE(HDR.EXPDATE) - INTERVAL 1 DAY) OR 
            (__SEARCHBY = 3 AND HDR.ORDERSTATUS = 'ACTIVE' AND NOW() >= DATE(HDR.EXPDATE) - INTERVAL 2 DAY) OR
            (__SEARCHBY = 4 AND HDR.ORDERSTATUS = 'WAITMA') OR 
            (__SEARCHBY = 5 AND HDR.ORDERSTATUS = 'SHIPPED') OR
            (__SEARCHBY = 6 AND HDR.ORDERSTATUS = 'CANCELLED') OR 
            (__SEARCHBY = 7 AND HDR.ORDERSTATUS = 'REJECTED')
        )
	ORDER BY HDR.ORDERREF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SearchForShipToAddresses` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SearchForShipToAddresses`(SEARCH VARCHAR(1000), SEARCHBY INTEGER)
BEGIN
	SELECT ID,NAME,ADDRESS,STOREID FROM shipToAddresses WHERE
    (
 		(NAME LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,1)) OR
        (ADDRESS LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,2)) OR 
        (STOREID LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,3))
    );
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SearchForSubCategories` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SearchForSubCategories`(__CATID INTEGER)
BEGIN
	SELECT * FROM subcategories WHERE CATID = __CATID ORDER BY NAME;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SearchForUsers` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `SearchForUsers`(SEARCH VARCHAR(1000), SEARCHBY INTEGER, ACCESSLEVEL INTEGER)
BEGIN
    SELECT USERNAME,FIRSTNAME,LASTNAME FROM user WHERE
    (
        (USERNAME LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,1)) OR
        (FIRSTNAME LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,2)) OR
        (LASTNAME LIKE CONCAT('%',SEARCH,'%') AND SEARCHBY IN (0,3))
    ) AND USERLEVEL <= ACCESSLEVEL
    ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SubmitOrder` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`localhost` PROCEDURE `SubmitOrder`(__USERNAME NVARCHAR(50), __FIRSTNAME NVARCHAR(200), __LASTNAME NVARCHAR(200), __EMAIL NVARCHAR(45), __SPECINSTR NVARCHAR(1000), __RECEIVERINSTR NVARCHAR(1000), __SHIPPINGMETHOD VARCHAR(45), __SHIPPINGCOST DECIMAL(19,4), __MGRAPPROVAL INT, __PAYMENTREF VARCHAR(100), __PONUMBER VARCHAR(100),  __BILLINGCATEGORY VARCHAR(100))
BEGIN


SET @ORDERREF = IFNULL((SELECT MAX(ORDERREF)+1 FROM orderHeader),10000);


UPDATE 
  orderHeader
SET
  SHIPCONTACTFIRSTNAME = __FIRSTNAME,
    SHIPCONTACTLASTNAME = __LASTNAME,
    SHIPCONTACTEMAIL = __EMAIL,
    SPECIALINSTRUCTIONS = __SPECINSTR,
    RECEIVERINSTRUCTIONS = __RECEIVERINSTR,
    SHIPPINGMETHOD = __SHIPPINGMETHOD,
    SHIPPINGCOST = __SHIPPINGCOST,
    SHIPPINGCOSTTAXAMT1 = ROUND(__SHIPPINGCOST * TAXRATE1,2),
    SHIPPINGCOSTTAXAMT2 = ROUND(__SHIPPINGCOST * TAXRATE2,2),
    SHIPPINGCOSTTAXAMT3 = ROUND(__SHIPPINGCOST * TAXRATE3,2),
    SHIPPINGCOSTTAXAMT4 = ROUND(__SHIPPINGCOST * TAXRATE4,2),
    SHIPPINGCOSTTAXAMT5 = ROUND(__SHIPPINGCOST * TAXRATE5,2),
    APPROVALLINK = CASE WHEN __MGRAPPROVAL = 0 THEN NULL ELSE CONCAT(USERNAME,ORDERID,MD5(MD5(NOW())+ORDERID)) END,
    ORDERREF = CASE WHEN __MGRAPPROVAL = 0 THEN @ORDERREF ELSE 0 END,
    PAYMENTREF = __PAYMENTREF,
    PONUMBER = __PONUMBER,
    BILLINGCATEGORY = __BILLINGCATEGORY


WHERE 
  USERNAME COLLATE utf8_unicode_ci = __USERNAME AND 
    ORDERSTATUS = 'CART';
    
UPDATE orderDetail
  SET 
  SHIPTODATEQTY = ORDERQTY,
  DOWNLOADLINK = CONCAT(ORDERID,'-',ITEMLINE,'-',MD5(MD5(MD5(NOW())+ORDERID)+ITEMLINE))
WHERE
  ORDERQTY = -2 AND
  ORDERID IN (SELECT ORDERID FROM orderHeader WHERE USERNAME COLLATE utf8_unicode_ci = __USERNAME AND ORDERSTATUS = 'CART');   
    
SELECT
  HDR.*,
    DTL.*,
    ITEM.TYPE,
    USER.FIRSTNAME AS USER_FIRSTNAME,
    USER.LASTNAME AS USER_LASTNAME,
    USER.EMAIL AS USER_EMAIL,
    USER.LANGUAGE AS USER_LANGUAGE,
    BRANCH.NAME AS BRANCH_NAME,
    BRANCH.MANAGEMENTEMAIL AS BRANCH_MANAGEMENTEMAIL,
    COALESCE(BRANCHADDRESS.LANGUAGE,'EN') AS BRANCH_LANGUAGE,
    CASE WHEN __MGRAPPROVAL = 0 THEN 'ACTIVE' ELSE 'WAITMA' END AS REALSTATUS,
    COALESCE(ITEM.DLSUGGESTIONS,'') AS DLSUGGESTIONS
FROM
  orderHeader HDR
INNER JOIN
  orderDetail DTL ON 
    HDR.ORDERID = DTL.ORDERID
INNER JOIN
  items ITEM ON 
    DTL.ITEMID = ITEM.ID
INNER JOIN
  user USER ON 
    HDR.USERNAME = USER.USERNAME
INNER JOIN
  branches BRANCH ON 
    USER.BRANCH = BRANCH.CODE
LEFT OUTER JOIN
  (SELECT * FROM branchesAddresses WHERE ISMAIN = 1) BRANCHADDRESS ON 
    BRANCH.CODE = BRANCHADDRESS.CODE
WHERE 
  HDR.USERNAME COLLATE utf8_unicode_ci = __USERNAME AND 
    ORDERSTATUS = 'CART';

IF __MGRAPPROVAL = 0 THEN

        
    SELECT @SHIPLINECOUNT := COUNT(*) FROM orderDetail 
    WHERE ORDERQTY <> -2 AND
        ORDERID IN (SELECT ORDERID FROM orderHeader WHERE USERNAME COLLATE utf8_unicode_ci = __USERNAME AND ORDERSTATUS = 'CART');  
        
  UPDATE 
    orderHeader
  SET
    ORDERSTATUS = CASE WHEN @SHIPLINECOUNT = 0 THEN 'SHIPPED' ELSE 'ACTIVE' END
  WHERE 
    USERNAME COLLATE utf8_unicode_ci = __USERNAME AND 
    ORDERSTATUS = 'CART';   
    
ELSE

  UPDATE 
    orderHeader
  SET
    ORDERSTATUS = CASE WHEN __MGRAPPROVAL = 0 THEN 'ACTIVE' ELSE 'WAITMA' END
  WHERE 
    USERNAME COLLATE utf8_unicode_ci = __USERNAME AND 
    ORDERSTATUS = 'CART'; 

END IF;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateBranchAsset` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateBranchAsset`(__BRANCHCODE VARCHAR(20), __TAGNAME VARCHAR(100), __TAGVALUE VARCHAR(1000))
BEGIN

	SET @ROWCNT = (SELECT COUNT(*) FROM branchesAssetData WHERE BRANCHCODE = __BRANCHCODE AND TAG = __TAGNAME);

	IF @ROWCNT = 0 THEN
		INSERT INTO
			branchesAssetData
            (BRANCHCODE,TAG,VALUE)
		VALUES
			(__BRANCHCODE,__TAGNAME,__TAGVALUE);
    ELSE
    
		UPDATE 
			branchesAssetData
		SET
			VALUE = __TAGVALUE
		WHERE
			BRANCHCODE = __BRANCHCODE AND
            TAG = __TAGNAME;
    END IF;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateItemDisplayImage` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateItemDisplayImage`(__ITEMID INT, __DISPLAYNAME VARCHAR(200), __DISPLAYPATH VARCHAR(200))
BEGIN
	UPDATE
		items
	SET
		DISPLAYIMAGENAME = __DISPLAYNAME,
        DISPLAYIMAGEPATH = __DISPLAYPATH
	WHERE
		ID = __ITEMID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateItemParametersFile` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateItemParametersFile`(__ITEMID INT, __PARAMETERSFILE VARCHAR(200), __PARAMETERSPATH VARCHAR(200))
BEGIN
	UPDATE
		items
	SET
		PARAMETERSFILENAME = __PARAMETERSFILE,
        PARAMETERSFILEPATH = __PARAMETERSPATH
	WHERE
		ID = __ITEMID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateItemPreviewImage` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateItemPreviewImage`(__ITEMID INT, __PREVIEWNAME VARCHAR(200), __PREVIEWPATH VARCHAR(200))
BEGIN
	UPDATE
		items
	SET
		PREVIEWIMAGENAME = __PREVIEWNAME,
        PREVIEWIMAGEPATH = __PREVIEWPATH
	WHERE
		ID = __ITEMID;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateItemProofPDF` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateItemProofPDF`(__ITEMID INT, __PDFNAME VARCHAR(200), __PDFPATH VARCHAR(200))
BEGIN
	UPDATE
		items
	SET
		PROOFPDFNAME = __PDFNAME,
        PROOFPDFPATH = __PDFPATH
	WHERE
		ID = __ITEMID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateOrderInformation` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateOrderInformation`(__USERNAME NVARCHAR(50), __FIRSTNAME NVARCHAR(200), __LASTNAME NVARCHAR(200), __EMAIL NVARCHAR(45), __SPECINSTR NVARCHAR(1000), __RECEIVERINSTR NVARCHAR(1000), __SHIPPINGMETHOD VARCHAR(45), __SHIPPINGCOST DECIMAL(19,4), __PONUMBER VARCHAR(100), __BILLINGCATEGORY VARCHAR(100))
BEGIN


UPDATE 
	orderHeader
SET
	SHIPCONTACTFIRSTNAME = __FIRSTNAME,
    SHIPCONTACTLASTNAME = __LASTNAME,
    SHIPCONTACTEMAIL = __EMAIL,
    SPECIALINSTRUCTIONS = __SPECINSTR,
    RECEIVERINSTRUCTIONS = __RECEIVERINSTR,
    SHIPPINGMETHOD = __SHIPPINGMETHOD,
    SHIPPINGCOST = __SHIPPINGCOST,
    SHIPPINGCOSTTAXAMT1 = __SHIPPINGCOST * TAXRATE1,
    SHIPPINGCOSTTAXAMT2 = __SHIPPINGCOST * TAXRATE2,
    SHIPPINGCOSTTAXAMT3 = __SHIPPINGCOST * TAXRATE3,
    SHIPPINGCOSTTAXAMT4 = __SHIPPINGCOST * TAXRATE4,
    SHIPPINGCOSTTAXAMT5 = __SHIPPINGCOST * TAXRATE5,
	PONUMBER = __PONUMBER,
    BILLINGCATEGORY = __BILLINGCATEGORY
WHERE 
	USERNAME COLLATE utf8_unicode_ci = __USERNAME AND 
    ORDERSTATUS = 'CART';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UserCancelOrder` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UserCancelOrder`(__USERNAME VARCHAR(100), __ORDERID INT)
BEGIN
 UPDATE
 orderHeader
 SET
 APPROVALLINK = NULL,
 ORDERSTATUS = 'CANCELLED',
 ORDERMODIFIED = CURRENT_TIMESTAMP
 WHERE
 ORDERID = __ORDERID AND
 USERNAME = __USERNAME; 
 
 UPDATE 
 orderDetail
 SET
 ORDERQTY = 0,
 SHIPTODATEQTY = 0
 WHERE
 ORDERID = __ORDERID AND
 ORDERID IN (SELECT ORDERID FROM orderHeader WHERE USERNAME = __USERNAME);
END ;;
DELIMITER ;


