WEB_SPS_PROJECTS_FOR_CALENDAR_EX.sql

DELIMITER $$
DROP PROCEDURE IF EXISTS WEB_SPS_PROJECTS_FOR_CALENDAR_EX $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `SalesViewStage`.`WEB_SPS_PROJECTS_FOR_CALENDAR_EX`(IN_USER_ID BIGINT, IN_CONTACT_ID BIGINT, 
						IN_STATUS BIGINT, IN_MONTH INT, IN_YEAR INT, IN_TYPE SMALLINT, IN_DATE VARCHAR(50))
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
	SET @USER_CONTACT_ID := (SELECT CONTACT_ID FROM USERS_EX 
					WHERE USER_ID = IN_USER_ID 
					AND USER_MODIFIED_DATE <= IN_DATE
					AND USER_DELETED_DATE IS NULL
					ORDER BY USER_MODIFIED_DATE DESC
					LIMIT 1);

	SELECT
		PROJECTS_EX.PROJECT_ID,
		PROJECTS_EX.PROJECT_NUMBER,
		PROJECTS_EX.PROJECT_NAME,  		
		PROJECT_ITEM_QUOTE_STATUS,
		PROJECT_ITEM_FOLLOWUP_STATUS,
		(SELECT LOOKUP_TEXT FROM lookup_values_EX
			WHERE LOOKUP_TYPE = 3 AND LOOKUP_VALUE = PROJECT_ITEM_STATUS
			AND LOOKUP_MODIFIED_DATE <= IN_DATE
			AND LOOKUP_DELETED_DATE IS NULL
			ORDER BY LOOKUP_MODIFIED_DATE DESC
			LIMIT 1) AS PROJECT_STATUS_PER_TYPE,
		(SELECT LOOKUP_TEXT FROM lookup_values_EX
			WHERE LOOKUP_TYPE = 2 AND LOOKUP_VALUE = PROJECTS_ITEMS.INVOLVED_SPECS
			AND LOOKUP_MODIFIED_DATE <= IN_DATE
			AND LOOKUP_DELETED_DATE IS NULL
			ORDER BY LOOKUP_MODIFIED_DATE DESC
			LIMIT 1) AS INVOLVED_SPECS,
		PROJECTS_ITEMS_EX.PROJECT_ITEM_ID,
		PROJECTS_ITEMS_EX.BID_DATE AS PROJECT_ITEM_BID_DATE,
		PROJECTS_ITEMS_EX.FOLLOWUP_DATE,
		(SELECT PARTNER_NAME FROM PARTNERS_EX
			WHERE PARTNER_ID = projects_items_EX.MANUFACTURER
			AND PARTNER_MODIFIED_DATE <= IN_DATE
			AND PARTNER_DELETED_DATE IS NULL
			ORDER BY PARTNER_MODIFIED_DATE DESC
			LIMIT 1
			) AS MANUFACTURER
	FROM (SELECT * FROM PROJECTS_EX
			WHERE PROJECT_MODIFIED_DATE <= IN_DATE 
			AND PROJECT_DELETED_DATE IS NULL
			) AS PROJECTS_EX 
	LEFT JOIN PROJECTS_ITEMS_EX ON projects_EX.PROJECT_ID = PROJECTS_ITEMS_EX.PROJECT_ID
	WHERE 
	(PROJECT_ITEM_STATUS = IN_STATUS OR IN_STATUS = -1)
	AND (IN_YEAR = -1 OR (
	(MONTH(BID_DATE) = IN_MONTH AND YEAR(BID_DATE) = IN_YEAR AND IN_TYPE = 1)
	OR (MONTH(PROJECTS_ITEMS_EX.FOLLOWUP_DATE) = IN_MONTH AND YEAR(PROJECTS_ITEMS_EX.FOLLOWUP_DATE) = IN_YEAR AND IN_TYPE = 2) -- AND ((IN_TYPE = 2 AND PROJECT_ITEM_FOLLOWUP_STATUS > 3) OR (IN_TYPE = -1  AND PROJECT_ITEM_FOLLOWUP_STATUS > 3)))
	))																									-- SHOW TYPE FOLLOWUP ONLY WITH QUOTE SENT
	AND ((IN_CONTACT_ID = -1) OR (
	(IN_CONTACT_ID IN (PROJECT_MANAGER, PROJECT_ESTIMATOR, MANUFACTURER, SUB_CONTRACTOR, GENERAL_CONTRACTOR, ARCHITECT, DISTRIBUTOR))
	OR (PROJECT_MANAGER IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = IN_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (PROJECT_ESTIMATOR IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = IN_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL)
	OR (MANUFACTURER IN (SELECT CONTACT_ID FROM CONTACTS_EX 
					WHERE CONTACT_ACCOUNT_REP = IN_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (SUB_CONTRACTOR IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = IN_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (GENERAL_CONTRACTOR IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = IN_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (ARCHITECT IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = IN_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (DISTRIBUTOR IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = IN_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (EXISTS(SELECT 1 FROM PROJECTS_CONTACTS_EX 
			WHERE PROJECT_ID = PROJECTS.PROJECT_ID AND CONTACT_ID = IN_CONTACT_ID
			AND MODIFIED_DATE <= IN_DATE
			AND DELETED_DATE IS NULL
			))
	))
	AND ((@USER_CONTACT_ID = NULL) OR (
	(@USER_CONTACT_ID IN (PROJECT_MANAGER, PROJECT_ESTIMATOR, MANUFACTURER, SUB_CONTRACTOR, GENERAL_CONTRACTOR, ARCHITECT, DISTRIBUTOR))
	OR (PROJECT_MANAGER IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = @USER_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (PROJECT_ESTIMATOR IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = @USER_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (MANUFACTURER IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = @USER_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (SUB_CONTRACTOR IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = @USER_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (GENERAL_CONTRACTOR IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = @USER_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (ARCHITECT IN (SELECT CONTACT_ID FROM CONTACTS_EX 
					WHERE CONTACT_ACCOUNT_REP = @USER_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (DISTRIBUTOR IN (SELECT CONTACT_ID FROM CONTACTS_EX
					WHERE CONTACT_ACCOUNT_REP = @USER_CONTACT_ID
					AND CONTACT_MODIFIED_DATE <= IN_DATE
					AND CONTACT_DELETED_DATE IS NULL))
	OR (EXISTS(SELECT 1 FROM PROJECTS_CONTACTS 
					WHERE PROJECT_ID = PROJECTS.PROJECT_ID 
					AND CONTACT_ID = @USER_CONTACT_ID
					AND MODIFIED_DATE <= IN_DATE
					AND DELETED_DATE IS NULL))
	OR (IN_USER_ID = PROJECT_CREATED_BY)
	OR (@USER_LEVEL = 2) -- ADMIN
	))
	AND NOT EXISTS (SELECT ID FROM (SELECT * FROM EXCLUDES_EX
						WHERE MODIFIED_DATE <= IN_DATE
						AND DELETED_DATE IS NULL) AS EXCLUDES_EX
		LEFT JOIN (SELECT * FROM LOOKUP_VALUES_EX 
				WHERE LOOKUP_MODIFIED_DATE <= IN_DATE
				AND LOOKUP_DELETED_DATE IS NULL) AS LOOKUP_VALUES_EX 
			ON EXCLUDES_EX.PROJECT_STATUS_PER_TYPE = LOOKUP_VALUES_EX.LOOKUP_TEXT 
			WHERE LOOKUP_VALUES_EX.LOOKUP_VALUE = PROJECT_ITEM_STATUS AND EXCLUDES_EX.CALENDAR_TYPE = IN_TYPE);
    END$$

DELIMITER ;