gistfile1.sql

BEGIN
	DECLARE cursorEnd INT DEFAULT FALSE;

	DECLARE clientID INT(11);
	DECLARE clientStateAgentCode, tempStateAgentCode VARCHAR(255);
	DECLARE clientClientID, tempClientID VARCHAR(255);
	DECLARE clientNamePrefix, tempNamePrefix VARCHAR(255);
	DECLARE clientFirstName, tempFirstName VARCHAR(255);
	DECLARE clientMiddleName, tempMiddleName VARCHAR(255);
	DECLARE clientLastName, tempLastName VARCHAR(255);
	DECLARE clientNameSuffix, tempNameSuffix VARCHAR(255);
	DECLARE clientGender, tempGender VARCHAR(255);
	DECLARE clientBirthdate, tempBirthdate DATE;
	DECLARE clientDeceased, tempDeceased TINYINT(1);
	DECLARE clientHOH, tempHOH TINYINT(1);
	DECLARE clientHomePhone, tempHomePhone VARCHAR(255);
	DECLARE clientWorkPhone, tempWorkPhone VARCHAR(255);
	DECLARE clientCellPhone, tempCellPhone VARCHAR(255);
	DECLARE clientStreet, tempStreet VARCHAR(255);
	DECLARE clientCity, tempCity VARCHAR(255);
	DECLARE clientState, tempState VARCHAR(255);
	DECLARE clientCountry, tempCountry VARCHAR(255);
	DECLARE clientPostal, tempPostal VARCHAR(255);
	DECLARE clientGroupID, tempGroupID VARCHAR(255);
	DECLARE clientRegionalCode, tempRegionalCode VARCHAR(255);
	DECLARE clientAfoCode, tempAfoCode VARCHAR(255);
	DECLARE clientNextReview, tempNextReview VARCHAR(10);
	DECLARE clientLastReview, tempLastReview VARCHAR(10);
	DECLARE clientEchoHOH, tempEchoHOH VARCHAR(255);
	DECLARE clientAccounting, tempAccounting VARCHAR(10);
	DECLARE clientFormat, tempFormat VARCHAR(8);
	DECLARE clientProcessing, tempProcessing VARCHAR(10);
	DECLARE clientPiiCode, tempPiiCode VARCHAR(8);
	DECLARE tempProdCode VARCHAR(255);
	DECLARE clientProductLines VARCHAR(255);
	DECLARE clientImportStatus INT(11);
	DECLARE clientAgentID INT(11);

	DECLARE tkzAgentID INT(11);
	DECLARE insertProductLines VARCHAR(255);

	DECLARE recordChange INT DEFAULT FALSE;
	DECLARE addressChange INT DEFAULT FALSE;

	DECLARE tempCursor CURSOR FOR SELECT state_agent_code, client_id_sf, name_prefix, first_name, middle_name, last_name, name_suffix, gender, birth_date, deceased_notification_status, head_of_household_status, home_phone, work_phone, cell_phone, street, city, state, policy_country, postal_code, client_group_id_sf, prod_line_code, regional_office_code, afo_code, next_insurance_financial_review_date_sf, last_insurance_financial_review_date_sf, echo_house_hold_key, accounting_date_sf, record_format_number_sf, processing_date_sf, pii_agent_code FROM temp_client_data;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursorEnd = TRUE;

	OPEN tempCursor;
	tempLoop: LOOP
		FETCH tempCursor INTO tempStateAgentCode, tempClientID, tempNamePrefix, tempFirstName, tempMiddleName, tempLastName, tempNameSuffix, tempGender, tempBirthdate, tempDeceased, tempHOH, tempHomePhone, tempWorkPhone, tempCellPhone, tempStreet, tempCity, tempState, tempCountry, tempPostal, tempGroupID, tempProdCode, tempRegionalCode, tempAfoCode, tempNextReview, tempLastReview, tempEchoHOH, tempAccounting, tempFormat, tempProcessing, tempPiiCode;
		IF cursorEnd THEN
			LEAVE tempLoop;
		END IF;

		SELECT id, state_agent_code, client_id_sf, name_prefix, first_name, middle_name, last_name, name_suffix, gender, birth_date, deceased_notification_status, head_of_household_status, home_phone, work_phone, cell_phone, street, city, state, policy_country, postal_code, client_group_id_sf, regional_office_code, afo_code, next_insurance_financial_review_date_sf, last_insurance_financial_review_date_sf, echo_house_hold_key, accounting_date_sf, record_format_number_sf, processing_date_sf, pii_agent_code, import_status_code_id, product_lines
			FROM clients
			WHERE client_id_sf = tempClientID AND client_group_id_sf = tempGroupID
			INTO clientID, clientStateAgentCode, clientClientID, clientNamePrefix, clientFirstName, clientMiddleName, clientLastName, clientNameSuffix, clientGender, clientBirthdate, clientDeceased, clientHOH, clientHomePhone, clientWorkPhone, clientCellPhone, clientStreet, clientCity, clientState, clientCountry, clientPostal, clientGroupID, clientRegionalCode, clientAfoCode, clientNextReview, clientLastReview, clientEchoHOH, clientAccounting, clientFormat, clientProcessing, clientPiiCode, clientImportStatus, clientProductLines;
			
		IF clientID != NULL THEN
			/* record already exists */
			SET recordChange = FALSE;
			SET addressChange = FALSE;

			CASE
				WHEN tempNamePrefix != clientNamePrefix THEN SET addressChange = TRUE;
				WHEN tempFirstName != clientFirstName THEN SET addressChange = TRUE;
				WHEN tempMiddleName != clientMiddleName THEN SET addressChange = TRUE;
				WHEN tempLastName != clientLastName THEN SET addressChange = TRUE;
				WHEN tempNameSuffix != clientNameSuffix THEN SET addressChange = TRUE;
				WHEN tempStreet != clientStreet THEN SET addressChange = TRUE;
				WHEN tempCity != clientCity THEN SET addressChange = TRUE;
				WHEN tempState != clientState THEN SET addressChange = TRUE;
				WHEN tempCountry != clientCountry THEN SET addressChange = TRUE;
				WHEN tempPostal != clientPostal THEN SET addressChange = TRUE;

				WHEN tempStateAgentCode != clientStateAgentCode THEN SET recordChange = TRUE;
				WHEN tempClientID != clientClientID THEN SET recordChange = TRUE;
				WHEN tempGender != clientGender THEN SET recordChange = TRUE;
				WHEN tempBirthdate != clientBirthdate THEN SET recordChange = TRUE;
				WHEN tempDeceased != clientDeceased THEN SET recordChange = TRUE;
				WHEN tempHOH != clientHOH THEN SET recordChange = TRUE;
				WHEN tempHomePhone != clientHomePhone THEN SET recordChange = TRUE;
				WHEN tempWorkPhone != clientWorkPhone THEN SET recordChange = TRUE;
				WHEN tempCellPhone != clientCellPhone THEN SET recordChange = TRUE;
				WHEN tempGroupID != clientGroupID THEN SET recordChange = TRUE;
				WHEN tempRegionalCode != clientRegionalCode THEN SET recordChange = TRUE;
				WHEN tempAfoCode != clientAfoCode THEN SET recordChange = TRUE;
				WHEN tempNextReview != clientNextReview THEN SET recordChange = TRUE;
				WHEN tempLastReview != clientLastReview THEN SET recordChange = TRUE;
				WHEN tempEchoHOH != clientEchoHOH THEN SET recordChange = TRUE;
				WHEN tempAccounting != clientAccounting THEN SET recordChange = TRUE;
				WHEN tempFormat != clientFormat THEN SET recordChange = TRUE;
				WHEN tempProcessing != clientProcessing THEN SET recordChange = TRUE;
				WHEN tempPiiCode != clientPiiCode THEN SET recordChange = TRUE;
			END CASE;

			IF clientProductLines = NULL THEN
				SET insertProductLines = CONCAT('--- \n-', tempProdCode, '\n');
			ELSE
				SET insertProductLines = CONCAT(clientProductLines, '-', tempProdCode, '\n');
			END IF;

			CASE
				WHEN addressChange = TRUE THEN
					/* update record, set import_status_code_id = 1 */
					UPDATE clients SET state_agent_code = tempStateAgentCode, client_id_sf = tempClientID, name_prefix = tempNamePrefix, first_name = tempFirstName, middle_name = tempMiddleName, last_name = tempLastName, name_suffix = tempNameSuffix, gender = tempGender, birth_date = tempBirthdate, deceased_notification_status = tempDeceased, head_of_household_status = tempHOH, home_phone = tempHomePhone, work_phone = tempWorkPhone, cell_phone = tempCellPhone, street = tempStreet, city = tempCity, state = tempState, policy_country = tempCountry, postal_code = tempPostal, client_group_id_sf = tempGroupID, regional_office_code = tempRegionalCode, afo_code = tempAfoCode, next_insurance_financial_review_date_sf = tempNextReview, last_insurance_financial_review_date_sf = tempLastReview, echo_house_hold_key = tempEchoHOH, accounting_date_sf = tempAccounting, record_format_number_sf = tempFormat, processing_date_sf = tempProcessing, pii_agent_code = tempPiiCode, import_status_code_id = 1, product_lines = insertProductLines, updated_at = CURTIME() WHERE id = clientID;
				WHEN recordChange = TRUE THEN
					IF clientImportStatus = 1 THEN
						/* address already changed, update record but don't reset status code */
						UPDATE clients SET state_agent_code = tempStateAgentCode, client_id_sf = tempClientID, name_prefix = tempNamePrefix, first_name = tempFirstName, middle_name = tempMiddleName, last_name = tempLastName, name_suffix = tempNameSuffix, gender = tempGender, birth_date = tempBirthdate, deceased_notification_status = tempDeceased, head_of_household_status = tempHOH, home_phone = tempHomePhone, work_phone = tempWorkPhone, cell_phone = tempCellPhone, street = tempStreet, city = tempCity, state = tempState, policy_country = tempCountry, postal_code = tempPostal, client_group_id_sf = tempGroupID, regional_office_code = tempRegionalCode, afo_code = tempAfoCode, next_insurance_financial_review_date_sf = tempNextReview, last_insurance_financial_review_date_sf = tempLastReview, echo_house_hold_key = tempEchoHOH, accounting_date_sf = tempAccounting, record_format_number_sf = tempFormat, processing_date_sf = tempProcessing, pii_agent_code = tempPiiCode, product_lines = insertProductLines, updated_at = CURTIME() WHERE id = clientID;
					ELSE
						/* update record, set import_status_code_id = 2 */
						UPDATE clients SET state_agent_code = tempStateAgentCode, client_id_sf = tempClientID, name_prefix = tempNamePrefix, first_name = tempFirstName, middle_name = tempMiddleName, last_name = tempLastName, name_suffix = tempNameSuffix, gender = tempGender, birth_date = tempBirthdate, deceased_notification_status = tempDeceased, head_of_household_status = tempHOH, home_phone = tempHomePhone, work_phone = tempWorkPhone, cell_phone = tempCellPhone, street = tempStreet, city = tempCity, state = tempState, policy_country = tempCountry, postal_code = tempPostal, client_group_id_sf = tempGroupID, regional_office_code = tempRegionalCode, afo_code = tempAfoCode, next_insurance_financial_review_date_sf = tempNextReview, last_insurance_financial_review_date_sf = tempLastReview, echo_house_hold_key = tempEchoHOH, accounting_date_sf = tempAccounting, record_format_number_sf = tempFormat, processing_date_sf = tempProcessing, pii_agent_code = tempPiiCode, import_status_code_id = 2, product_lines = insertProductLines, updated_at = CURTIME() WHERE id = clientID;
					END IF;
				ELSE
					IF clientImportStatus = NULL THEN
						/* update product_lines, set import_status_code_id = 4 */
						UPDATE clients SET product_lines = insertProductLines, import_status_code_id = 4, updated_at = CURTIME() WHERE id = clientID;
					ELSE
						/* update product_lines, leave status_code as is */
						UPDATE clients SET product_lines = insertProductLines, updated_at = CURTIME() WHERE id = clientID;
					END IF;
			END CASE;
		ELSE
			/* new record */
			SELECT id FROM elibrary_state_agent_codes WHERE state_agent_code = tempStateAgentCode INTO tkzAgentID;

			INSERT INTO clients (state_agent_code, client_id_sf, name_prefix, first_name, middle_name, last_name, name_suffix, gender, birth_date, deceased_notification_status, head_of_household_status, home_phone, work_phone, cell_phone, street, city, state, policy_country, postal_code, client_group_id_sf, regional_office_code, afo_code, next_insurance_financial_review_date_sf, last_insurance_financial_review_date_sf, echo_house_hold_key, accounting_date_sf, record_format_number_sf, processing_date_sf, pii_agent_code, import_status_code_id, product_lines, tkz_agent_id, created_at, updated_at)
				VALUES (tempStateAgentCode, tempClientID, tempNamePrefix, tempFirstName, tempMiddleName, tempLastName, tempNameSuffix, tempGender, tempBirthdate, tempDeceased, tempHOH, tempHomePhone, tempWorkPhone, tempCellPhone, tempStreet, tempCity, tempState, tempCountry, tempPostal, tempGroupID, tempRegionalCode, tempAfoCode, tempNextReview, tempLastReview, tempEchoHOH, tempAccounting, tempFormat, tempProcessing, tempPiiCode, 3, CONCAT('--- \n-', tempProdCode, '\n'), tkzAgentID, CURTIME(), CURTIME());
		END IF;
	END LOOP;
	CLOSE tempCursor;
END