INSERT INTO
  `flaggroups` (`flagGroupName`, `detail`, `parentFlagGroupId`)
VALUES
  ('Currency', 'Currency', '1');

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  (
    '5',
    '1',
    'currencySymbol',
    'Currency Symbol',
    '₹'
  );

UPDATE `systemflags`
SET
  `autoRender` = '1'
WHERE
  (`id` = '21');

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`,
    `autoRender`
  )
VALUES
  (
    '5',
    '1',
    'currencyCode',
    'currencyCode',
    'INR',
    '1'
  );

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`,
    `autoRender`
  )
VALUES
  (
    '5',
    '1',
    'currencyDecimalPoint',
    'Currency Decimal Point',
    '.12',
    '1'
  );

UPDATE `systemflags`
SET
  `displayName` = 'Currency Code'
WHERE
  (`id` = '22');

INSERT INTO
  `flaggroups` (`flagGroupName`, `detail`, `parentFlagGroupId`)
VALUES
  ('AWS Credential', 'AWS Credential', '1');

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  ('1', '7', 'isUseAwsBucket', 'Use AWS Bucket', '0');

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  (
    '6',
    '1',
    'AWS_ID',
    'AWS_ID',
    'AKIA4UJZ7TJYTMTOL35N'
  );

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  (
    '6',
    '1',
    'AWS_SECRET',
    'AWS_SECRET',
    'CoKsXS8nMzNOqcaehSdyKvFypmxPqsS1kiqnGJSC'
  );

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  ('6', '1', 'region', 'Region', 'us-west-2');

UPDATE `systemflags`
SET
  `flagGroupId` = '6',
  `valueTypeId` = '1'
WHERE
  (`id` = '27');

UPDATE `flaggroups`
SET
  `flagGroupName` = 'AWS',
  `detail` = 'AWS',
  `parentFlagGroupId` = NULL
WHERE
  (`id` = '6');

INSERT INTO
  `flaggroups` (`flagGroupName`, `detail`, `parentFlagGroupId`)
VALUES
  ('AWS Credential', 'AWS Credential', '6');

INSERT INTO
  `flaggroups` (`flagGroupName`, `detail`, `parentFlagGroupId`)
VALUES
  ('Buckets', 'Buckets', '6');

UPDATE `systemflags`
SET
  `flagGroupId` = '7'
WHERE
  (`id` = '26');

UPDATE `systemflags`
SET
  `flagGroupId` = '7'
WHERE
  (`id` = '25');

UPDATE `systemflags`
SET
  `flagGroupId` = '7'
WHERE
  (`id` = '28');

DELETE FROM `systemflags`
WHERE
  (`id` = '27');

DELETE FROM `systemflags`
WHERE
  (`id` = '24');

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  (
    '8',
    '1',
    'bankDocument',
    'Bank Document',
    'creditappbank'
  );

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  ('8', '1', 'banner', 'Banner', 'creditappbanner');

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  (
    '8',
    '1',
    'visitingCard',
    'Visiting Card',
    'creditappvisitingcard'
  );

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  (
    '8',
    '1',
    'loanDocuments',
    'Loan Documents',
    'creditapploandocuments'
  );

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  (
    '8',
    '1',
    'training',
    'Training',
    'creditapptraining'
  );

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  (
    '8',
    '1',
    'dsaSignupDocument',
    'DSA Signup Document',
    'creditappdsasignupdocument'
  );

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  (
    '8',
    '1',
    'userProfilePic',
    'User Profile Pic',
    'creditappuserprofilepic'
  );

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  (
    '8',
    '1',
    'serviceType',
    'ServiceType',
    'creditappservicetype'
  );

INSERT INTO
  `systemflags` (
    `flagGroupId`,
    `valueTypeId`,
    `name`,
    `displayName`,
    `value`
  )
VALUES
  (
    '8',
    '1',
    'products',
    'Products',
    'creditapp-gift-product'
  );

ALTER TABLE `documentmasters`
ADD COLUMN `allowFileUpload` TINYINT NULL AFTER `modifiedBy`,
ADD COLUMN `allowDocumentField` TINYINT NULL AFTER `allowFileUpload`,
ADD COLUMN `fieldRegExpression` VARCHAR(400) NULL AFTER `allowDocumentField`,
ADD COLUMN `hint` LONGTEXT NULL AFTER `fieldRegExpression`,
ADD COLUMN `documentFieldName` VARCHAR(200) NULL AFTER `hint`;

CREATE TABLE
  `serviceuserfield` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `userId` INT NULL,
    `createdDate` DATETIME NULL DEFAULT CURRENT_TIMESTAMP(),
    `isActive` TINYINT NULL DEFAULT 1,
    `isDelete` TINYINT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `fk_serviceuserfield_users_idx` (`userId` ASC) VISIBLE,
    CONSTRAINT `fk_serviceuserfield_users` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  );

ALTER TABLE `serviceuserfield`
ADD COLUMN `createdBy` INT NULL AFTER `isDelete`,
ADD COLUMN `modifiedBy` INT NULL AFTER `createdBy`;

CREATE TABLE
  `languages` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `language` VARCHAR(45) NULL,
    `code` VARCHAR(45) NULL,
    `isActive` TINYINT NULL DEFAULT 1,
    PRIMARY KEY (`id`)
  );

INSERT INTO
  `pages` (
    `name`,
    `displayName`,
    `url`,
    `parentId`,
    `icon`,
    `type`,
    `displayOrder`
  )
VALUES
  (
    'languages',
    'Languages',
    '/languages',
    '95',
    'globe',
    'link',
    NULL
  );


UPDATE `pages` SET `displayName` = 'Personal Loans', `url` = '/personal-loans',`displayOrder` = '1' WHERE (`id` = '63');
UPDATE `pages` SET `displayName` = 'Instant Loans', `url` = '/instant-loans',`displayOrder` = '2' WHERE (`id` = '64');
UPDATE `pages` SET `displayName` = 'Business Loans', `url` = '/business-loans',`displayOrder` = '3' WHERE (`id` = '65');
UPDATE `pages` SET `displayName` = 'Home Loans', `url` = '/home-loans',`displayOrder` = '4' WHERE (`id` = '66');
UPDATE `pages` SET `url` = '/employees',`displayName` = 'Employees',`parentId` = '68', `displayOrder` = '3' WHERE (`id` = '71');
UPDATE `pages` SET `url` = '/connectors',`parentId` = '68', `displayOrder` = '4',`displayName` = 'Connectors' WHERE (`id` = '72');
UPDATE `pages` SET `url` = '/adminUsers',`displayName` = 'Admin Users',`parentId` = '95', `displayOrder` = '7' WHERE (`id` = '75');
UPDATE `pages` SET `url` = '/payouts',`parentId` = '95', `displayOrder` = '3' WHERE (`id` = '73');
UPDATE `pages` SET `displayName` = 'Trainings', `url` = '/trainings',`displayOrder` = '2' WHERE (`id` = '79');
UPDATE `pages` SET `url` = '/rewardCoins',`displayName` = 'Reward Coins' WHERE (`id` = '81');
UPDATE `pages` SET `url` = '/bankCreditCards',`displayName` = 'Bank Credit Cards',`parentId` = '118' WHERE (`id` = '83');
UPDATE `pages` SET `url` = '/bankCreditCardPolicies',`displayName` = 'Bank Credit Card Policies',`parentId` = '118' WHERE (`id` = '84');
UPDATE `pages` SET `url` = '/creditCards',`displayName` = 'Credit Cards' WHERE (`id` = '85');
UPDATE `pages` SET `url` = '/otherLoans',`displayName` = 'Other Loans',`parentId` = '62', `displayOrder` = '6' WHERE (`id` = '89');

UPDATE `pages` SET `url` = '/userScratchCards',`displayName` = 'User Scratch Cards',`parentId` = '82', `displayOrder` = '2' WHERE (`id` = '92');
UPDATE `pages` SET `url` = '/visitingCards',`displayName` = 'Visiting Cards',`parentId` = '82', `displayOrder` = '3' WHERE (`id` = '93');
UPDATE `pages` SET `url` = '/products',`parentId` = '80', `displayOrder` = '2' WHERE (`id` = '94');
UPDATE `pages` SET `url` = '/serviceDocuments',`displayName` = 'Service Documents',`displayOrder` = '6' WHERE (`id` = '100');
UPDATE `pages` SET `url` = '/documentMasters',`displayName` = 'Document Masters',`displayOrder` = '5' WHERE (`id` = '101');
UPDATE `pages` SET `url` = '/residencetypes',`displayName` = 'Residence Types',`displayOrder` = '13' WHERE (`id` = '103');
UPDATE `pages` SET `url` = '/businessannualprofits',`displayName` = 'Business Annual Profits',`displayOrder` = '7' WHERE (`id` = '105');
UPDATE `pages` SET `url` = '/businessannualsales',`displayOrder` = '8' WHERE (`id` = '106');
UPDATE `pages` SET `url` = '/industrytypes',`displayOrder` = '11' WHERE (`id` = '109');
UPDATE `pages` SET `url` = '/propertyTypes',`displayName` = 'Property Types',`displayOrder` = '12' WHERE (`id` = '111');
UPDATE `pages` SET `url` = '/employmentServiceTypes',`displayName` = 'Employment Service Types',`displayOrder` = '3' WHERE (`id` = '113');
UPDATE `pages` SET `url` = '/addressTypes',`displayName` = 'AddressTypes',`displayOrder` = '14' WHERE (`id` = '114');
UPDATE `pages` SET `url` = '/coApplicantRelations',`displayOrder` = '16' WHERE (`id` = '115');
UPDATE `pages` SET `url` = '/banners',`displayName` = 'Banners',`displayOrder` = '21' WHERE (`id` = '117');
UPDATE `pages` SET `url` = '/banks',`displayName` = 'Banks',`displayOrder` = '1' WHERE (`id` = '119');
UPDATE `pages` SET `url` = '/bankLoans',`displayName` = 'Bank Loans',`displayOrder` = '4' WHERE (`id` = '120');
UPDATE `pages` SET `url` = '/bankCommissions',`displayName` = 'Bank Commissions',`displayOrder` = '5' WHERE (`id` = '121');
UPDATE `pages` SET `url` = '/bankLoanPolicies',`displayName` = 'Bank Loan Policies',`displayOrder` = '6' WHERE (`id` = '122');
UPDATE `pages` SET `url` = '/commissionTemplates',`displayName` = 'Commission Templates',`displayOrder` = '7' WHERE (`id` = '123');
UPDATE `pages` SET `url` = '/orders',`parentId` = '80', `displayOrder` = '3' WHERE (`id` = '125');
UPDATE `pages` SET `url` = '/adminCommissions',`displayName` = 'Admin Commissions',`parentId` = '95',`displayOrder` = '2' WHERE (`id` = '126');
UPDATE `pages` SET `url` = '/becomePartnerRequests',`displayName` = 'Become Partner Requests',`parentId` = '95', `displayOrder` = '1' WHERE (`id` = '130');
UPDATE `pages` SET `url` = '/loanstatuses',`displayName` = 'Loan Statuses',`displayOrder` = '18' WHERE (`id` = '131');
UPDATE `pages` SET `url` = '/permissionGroups',`displayName` = 'Page Permission Groups',`parentId` = '95', `displayOrder` = '6' WHERE (`id` = '132');
UPDATE `pages` SET `url` = '/newsLetterSubscriptions',`displayName` = 'NewsLetterSubscriptions',`parentId` = '96', `displayOrder` = '22' WHERE (`id` = '133');
UPDATE `pages` SET `url` = '/contactRequests',`displayName` = 'Contact Requests',`parentId` = '95', `displayOrder` = '5' WHERE (`id` = '134');
UPDATE `pages` SET `displayName` = 'Trainings', `displayOrder` = '8' WHERE (`id` = '77');
UPDATE `pages` SET `displayName` = 'Training Categories',`displayOrder` = '1' WHERE (`id` = '78');
UPDATE `pages` SET `displayName` = 'Service Employment Types',`displayOrder` = '4' WHERE (`id` = '99');
UPDATE `pages` SET `displayName` = 'Settings',`parentId` = '95', `displayOrder` = '8' WHERE (`id` = '124');
UPDATE `pages` SET `displayOrder` = '17' WHERE (`id` = '104');
UPDATE `pages` SET `displayOrder` = '19' WHERE (`id` = '129');
UPDATE `pages` SET `displayOrder` = '20' WHERE (`id` = '116');
UPDATE `pages` SET `displayOrder` = '21',`parentId` = '96' WHERE (`id` = '91');
UPDATE `pages` SET `parentId` = '95',`displayOrder` = '4' WHERE (`id` = '136');
UPDATE `pages` SET `displayName` = 'Admin Approvals', `displayOrder` = '10' WHERE (`id` = '95');
DELETE FROM `pages` WHERE (`id` = '61');
UPDATE `pages` SET `displayName` = 'Manage Client', `displayOrder` = '2' WHERE (`id` = '60');
UPDATE `pages` SET `displayName` = 'Loan Coordinates', `displayOrder` = '3' WHERE (`id` = '68');
UPDATE `pages` SET `displayOrder` = '4' WHERE (`id` = '62');
UPDATE `pages` SET `parentId` = '62', `displayOrder` = '7',`displayName` = 'Other Services',`url` = '/otherServices' WHERE (`id` = '90');
UPDATE `pages` SET `displayName` = 'Other Utilities', `displayOrder` = '5' WHERE (`id` = '82');
UPDATE `pages` SET `displayName` = 'Reward Management', `displayOrder` = '7' WHERE (`id` = '80');
UPDATE `pages` SET `displayName` = 'Admin Setup', `displayOrder` = '9',`icon` = 'settings' WHERE (`id` = '96');
UPDATE `pages` SET `displayOrder` = '6',`displayName` = 'Manage Bank' WHERE (`id` = '118');
DELETE FROM `pages` WHERE (`id` = '74');
INSERT INTO `pages` (`name`, `displayName`, `url`, `parentId`, `type`, `displayOrder`) VALUES ('leads', 'Leads', '/leads', '60', 'link', '1');
INSERT INTO `pages` (`id`, `name`, `displayName`, `url`, `parentId`, `icon`, `type`, `displayOrder`, `isActive`, `isDelete`) VALUES (NULL, 'payout', 'Manage Payout', '/payouts', '95', 'database', 'link', '3', '1', '0');
INSERT INTO `pages` (`id`, `name`, `displayName`, `url`, `parentId`, `type`, `displayOrder`, `isActive`, `isDelete`) VALUES (NULL, 'adminUser', 'Admin Users', '/adminUsers', '95', 'link', '7', '1', '0');
UPDATE `faqtypes` SET `name` = 'partner' WHERE (`id` = '2');
INSERT INTO `pages` (`id`, `name`, `displayName`, `url`, `parentId`, `type`, `displayOrder`, `isActive`, `isDelete`) VALUES (NULL, 'bankCreditCard', 'Bank Credit Cards', '/bankCreditCards', '118', 'link', '2', '1', '0');
INSERT INTO `pages` (`id`, `name`, `displayName`, `url`, `parentId`, `type`, `displayOrder`, `isActive`, `isDelete`) VALUES (NULL, 'bankCreditCardPolicy', 'Bank Credit Card Policies', '/bankCreditCardPolicies', '118', 'link', '3', '1', '0');
INSERT INTO `pages` (`name`, `displayName`, `url`, `parentId`, `type`, `displayOrder`) VALUES ('region', 'Region', '/region', '95', 'link', '9');
UPDATE `pages` SET `displayOrder` = '1' WHERE (`id` = '69');
UPDATE `pages` SET `displayOrder` = '2' WHERE (`id` = '70');
UPDATE `pages` SET `displayOrder` = '5' WHERE (`id` = '67');
UPDATE `pages` SET `displayOrder` = '1' WHERE (`id` = '97');
UPDATE `pages` SET `displayOrder` = '2' WHERE (`id` = '112');
UPDATE `pages` SET `displayOrder` = '9' WHERE (`id` = '107');
UPDATE `pages` SET `displayOrder` = '10' WHERE (`id` = '108');
UPDATE `pages` SET `displayOrder` = '15' WHERE (`id` = '102');
UPDATE `pages` SET `displayOrder` = '4' WHERE (`id` = '135');

ALTER TABLE `serviceuserfield` 
ADD COLUMN `leadId` INT NULL AFTER `modifiedBy`,
ADD INDEX `fk_serviceuserfield_leads_idx` (`leadId` ASC);

ALTER TABLE `serviceuserfield` 
ADD CONSTRAINT `fk_serviceuserfield_leads`
  FOREIGN KEY (`leadId`)
  REFERENCES `leads` (`id`)
  ON DELETE RESTRICT
  ON UPDATE RESTRICT;

DROP procedure IF EXISTS `adminInsertDocumentMasters`;

INSERT INTO `valuetypes` (`valueTypeName`, `description`, `isActive`, `isDelete`) VALUES ('LongText', 'LongText', '1', '0');

INSERT INTO `systemflags` (`flagGroupId`, `valueTypeId`, `name`, `displayName`, `autoRender`) VALUES ('1', '9', 'firebaseadminsdk', 'Firebase AdminSDK', '1');

DELIMITER ;;

CREATE PROCEDURE `adminInsertDocumentMasters`(IN _name VARCHAR(200),IN _maxSize VARCHAR(20), IN _userId INT,IN _allowFileUpload BOOLEAN,IN _allowDocumentField BOOLEAN,IN _fieldRegExpression VARCHAR(400),IN _hint LONGTEXT,IN _documentFieldName VARCHAR(200))
BEGIN
DECLARE exist INT DEFAULT 0;
    DECLARE _existName VARCHAR(200) DEFAULT ''; 
    DECLARE finished INT DEFAULT 0;
	IF 	EXISTS(SELECT 1 FROM documentmasters) THEN
		BEGIN
			DECLARE cur_checkexist cursor for select lower(name) FROM documentmasters WHERE isDelete =0;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
			OPEN cur_checkexist;
            SET @exist =0;
				getDocumentMaster: LOOP
					FETCH cur_checkexist INTO _existName;
					IF finished = 1 THEN 
						LEAVE getDocumentMaster;
					END IF;
					IF _existName = lower(_name) THEN
						SET @exist = 1;	
					END IF;
                    END LOOP;
		    CLOSE cur_checkexist;
		END;
		IF @exist = 1 THEN
			SELECT @exist as nameExist;				
		ELSE
	INSERT INTO documentmasters(name,maxSize,createdBy,modifiedBy,allowFileUpload,allowDocumentField,fieldRegExpression,hint,documentFieldName) VALUES(_name,_maxSize, _userId, _userId,_allowFileUpload,_allowDocumentField,_fieldRegExpression,_hint,_documentFieldName);
    END IF;
	ELSE
		INSERT INTO documentmasters(name,maxSize,createdBy,modifiedBy,allowFileUpload,allowDocumentField,fieldRegExpression,hint,documentFieldName) VALUES(_name,_maxSize, _userId, _userId,_allowFileUpload,_allowDocumentField,_fieldRegExpression,_hint,_documentFieldName);
     END IF;
     IF _allowDocumentField = true THEN
     SET @alterTableQuery = '';
	 SET @alterTableQuery = CONCAT_WS('','ALTER TABLE serviceuserfield ADD ',_documentfieldname,' VARCHAR(200) NULL');
	 PREPARE stmt FROM @alterTableQuery;
	 EXECUTE stmt;
	 DEALLOCATE PREPARE stmt;
     END IF;
END;
DELIMITER ;


DROP procedure IF EXISTS `adminUpdateDocumentMaster`;

DELIMITER ;;
CREATE  PROCEDURE `adminUpdateDocumentMaster`(IN _id INT, IN _name VARCHAR(200),IN _maxSize VARCHAR(40), IN _userId INT,IN _allowFileUpload BOOLEAN,IN _allowDocumentField BOOLEAN,IN _fieldRegExpression VARCHAR(400),IN _hint LONGTEXT,IN _documentFieldName VARCHAR(200),IN _oldFieldName VARCHAR(200))
BEGIN
	DECLARE exist INT DEFAULT 0;
    DECLARE _existName VARCHAR(200) DEFAULT '';
    DECLARE _existColumn VARCHAR(200) DEFAULT '';
    DECLARE finished INT DEFAULT 0;
	IF 	EXISTS(SELECT 1 FROM documentmasters) THEN
		BEGIN
			DECLARE cur_checkexist cursor for select lower(name) FROM documentmasters WHERE isDelete =0 AND id != _id;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
			OPEN cur_checkexist;
            SET @exist =0;
				getDocumentMaster: LOOP
					FETCH cur_checkexist INTO _existName;
					IF finished = 1 THEN 
						LEAVE getDocumentMaster;
					END IF;                    
					IF _existName = lower(_name) THEN
						SET @exist = 1;	
					END IF;
                    END LOOP;
		    CLOSE cur_checkexist;
		END;
		IF @exist = 1 THEN
			SELECT @exist as nameExist;				
		ELSE
	UPDATE documentmasters SET name = _name,maxSize = _maxSize, modifiedBy = _userId, modifiedDate = CURRENT_TIMESTAMP(),allowFileUpload = _allowFileUpload,allowDocumentField = _allowDocumentField,fieldRegExpression = _fieldRegExpression,hint = _hint,documentFieldName = _documentFieldName  WHERE id = _id;
    END IF;
	ELSE
    UPDATE documentmasters SET name = _name,maxSize = _maxSize, modifiedBy = _userId, modifiedDate = CURRENT_TIMESTAMP(),allowFileUpload = _allowFileUpload,allowDocumentField = _allowDocumentField,fieldRegExpression = _fieldRegExpression,hint = _hint,documentFieldName = _documentFieldName  WHERE id = _id;
    END IF;
	IF _allowDocumentField = false && _oldFieldName != '' THEN
     SET @alterTableQuery = '';
	 SET @alterTableQuery = CONCAT_WS('','ALTER TABLE serviceuserfield DROP COLUMN ',_oldFieldName,';');
	 PREPARE stmt FROM @alterTableQuery;
	 EXECUTE stmt;
	 DEALLOCATE PREPARE stmt;
     END IF;
     
     IF _allowDocumentField = true THEN
     BEGIN
			DECLARE cur_columncheckexist cursor for SELECT _documentFieldName FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'serviceuserfield' AND COLUMN_NAME = _documentFieldName;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
			OPEN cur_columncheckexist;
            SET @exist =0;
				checkColumnExist: LOOP
					FETCH cur_columncheckexist INTO _existColumn;
					IF finished = 1 THEN 
						LEAVE checkColumnExist;
					END IF;                  
                    END LOOP;
		    CLOSE cur_columncheckexist;
		END;
  
        IF _existColumn != _documentfieldname THEN
			SET @alterTableQuery = '';
			SET @alterTableQuery = CONCAT_WS('','ALTER TABLE serviceuserfield ADD ',_documentfieldname,' VARCHAR(200) NULL');
			PREPARE stmt FROM @alterTableQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
     END IF;
     END IF;
END;
DELIMITER ;

DROP procedure IF EXISTS `adminGetServiceTypeDocuments`;

DELIMITER ;;

CREATE PROCEDURE `adminGetServiceTypeDocuments`(IN _startIndex INT, IN _fetchRecords INT,IN _serviceId INT)
BEGIN
	DECLARE SqlQuery LONGTEXT DEFAULT "";
    DECLARE SQlQueryCount LONGTEXT DEFAULT "";
    DECLARE FilterQuery LONGTEXT DEFAULT "";
	IF _startIndex >= 0 AND _fetchRecords>0 THEN 
		SET @StartIndexQuery = CONCAT_WS('', " LIMIT ",_fetchRecords, " OFFSET ", _startIndex);
	END IF;
    SET @FilterQuery = "";
    IF _serviceId > 0 THEN 
		SET @FilterQuery = CONCAT_WS(''," AND servicetypedocuments.serviceId = ",_serviceId);
    END IF;
	SET @SqlQueryCount = CONCAT_WS('',"SELECT COUNT(servicetypedocuments.id) as totalCount
	FROM servicetypedocuments 
	INNER JOIN services ON services.id = servicetypedocuments.serviceId
	INNER JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId LEFT JOIN employmenttypes ON employmenttypes.id = servicetypedocuments.employmentTypeId WHERE servicetypedocuments.isDelete = 0 ",@FilterQuery);
	PREPARE countStmt FROM @SqlQueryCount;
    EXECUTE countStmt;
    DEALLOCATE PREPARE countStmt;
	SET @SqlQuery = CONCAT_WS('', "SELECT servicetypedocuments.*, services.displayName as serviceDisplayName, services.name as servicesName, documentmasters.name as documentName,documentmasters.maxSize,documentmasters.allowFileUpload,documentmasters.allowDocumentField,employmenttypes.name as employmentType
	FROM servicetypedocuments 
	INNER JOIN services ON services.id = servicetypedocuments.serviceId
	INNER JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId 
    LEFT JOIN employmenttypes ON employmenttypes.id = servicetypedocuments.employmentTypeId WHERE servicetypedocuments.isDelete = 0 AND documentmasters.allowFileUpload = true",@FilterQuery," ORDER BY id desc ", @StartIndexQuery);
	PREPARE stmt FROM @SqlQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

DELIMITER ;


DROP procedure IF EXISTS `dsaBazarInsertPartner`;


DELIMITER ;;

CREATE PROCEDURE `dsaBazarInsertPartner`(IN _roleId INT, IN _temporaryCode VARCHAR(45), IN _fullName VARCHAR(200), IN _gender VARCHAR(20), IN _email VARCHAR(256), IN _countryCode VARCHAR(6), IN _contactNo VARCHAR(45)
, IN _aadhaarCardNo VARCHAR(20), IN _panCardNo VARCHAR(20), IN _cityId INT, IN _companyName VARCHAR(100), IN _professionTypeId INT, IN _workExperience DECIMAL(5,2), IN _haveOffice BOOLEAN
, IN _businessName VARCHAR(100) , IN _businessAddress LONGTEXT,  IN _addressLine1 LONGTEXT, IN _addressLine2 LONGTEXT, IN _pincode VARCHAR(10), IN _workAddressCityId INT,IN _jobType VARCHAR(45), IN _gstNo VARCHAR(20), IN _commitment DECIMAL(13,3), IN _referralCode VARCHAR(45), IN _parentPartnerId INT, IN _designationId INT,IN _userId INT)
BEGIN
	DECLARE finished INT DEFAULT 0;
    DECLARE cUserId INT DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
	END; 
	START TRANSACTION;
    BEGIN
		DECLARE cur_user CURSOR FOR SELECT users.id FROM users WHERE users.contactNo = _contactNo;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
        OPEN  cur_user;
			cur_user_loop: LOOP
				FETCH cur_user INTO cUserId;
                IF finished = 1 THEN
					LEAVE cur_user_loop;
				END IF;
            END LOOP cur_user_loop;
        CLOSE cur_user;
        IF cUserId>0 THEN
			SELECT 'Contact No Already Exist' as message;
        ELSE
			INSERT INTO users(fullName, gender, email, countryCode, contactNo, isDisabled,createdBy,modifiedBy,currentRoleId) VALUES(_fullName, _gender, _email, _countryCode, _contactNo, true,_userId,_userId,_roleId);
			SET @userId = @@IDENTITY;
			INSERT INTO userroles(userId, roleId) VALUES(@userId, _roleId);
			INSERT INTO 
			partners(userId, temporaryCode, fullName, gender, contactNo, aadhaarCardNo, panCardNo, cityId, companyName, professionTypeId, workExperience, haveOffice, businessName, businessAddress, addressLine1, addressLine2, pincode, workAddressCityId, jobType,  gstNo, commitment, referralCode,currentBadgeId,parentPartnerId, designationId,createdBy,modifiedBy) 
			VALUES(@userId, _temporaryCode, _fullName, _gender, _contactNo, _aadhaarCardNo, _panCardNo, _cityId, _companyName, _professionTypeId, _workExperience, _haveOffice, _businessName, _businessAddress, _addressLine1, _addressLine2, _pincode, _workAddressCityId , _jobType, _gstNo, _commitment, _referralCode,1, _parentPartnerId, _designationId,_userId,_userId);
			SET @insertId = @@IDENTITY;
            SELECT @insertId as insertId,@userId as userId;
            INSERT INTO partnerbadges (partnerId,badgeId,createdBy,modifiedBy) VALUES (@insertId,1,_userId,_userId);
            SELECT 1 as currentBadgeId;
           
            SELECT roles.id, roles.name From userroles INNER JOIN roles ON roles.id = userroles.roleId WHERE userroles.userId = @userId;
            SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, partners.* FROM users 
            INNER JOIN userroles ON userroles.userId = users.id INNER JOIN partners ON partners.userId = users.id 
            WHERE users.id = @userId;
			SELECT partnerdocuments.url as profilePicUrl FROM partnerdocuments 
            WHERE partnerId = @insertId;
            IF _parentPartnerId>0 THEN
				SELECT partners.* FROM partners WHERE id = _parentPartnerId;             
            END IF;
           
        END IF;
    END;
    COMMIT;
END;

DELIMITER ;


DROP procedure IF EXISTS `customerGetMasterDataByServiceId`;


DELIMITER ;;

CREATE PROCEDURE `customerGetMasterDataByServiceId`(IN _serviceId INT)
BEGIN
	SELECT * FROM maritalstatuses;
    
    SELECT servicetypedocuments.*, services.displayName as serviceDisplayName, services.name as servicesName, documentmasters.name as documentName,documentmasters.maxSize as maxSize,documentmasters.allowFileUpload,documentmasters.allowDocumentField,documentmasters.documentFieldName
	FROM servicetypedocuments 
	INNER JOIN services ON services.id = servicetypedocuments.serviceId
	INNER JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId
	WHERE serviceId = _serviceId AND documentmasters.allowFileUpload = 1;
    
    SELECT employmenttypes.*,parentType.id  as parentTypeId, parentType.name as parentTypeName FROM employmenttypes 
	LEFT JOIN employmenttypes as parentType ON parentType.id = employmenttypes.parentId 
	INNER JOIN servicesemploymenttypes ON servicesemploymenttypes.employmentTypeId = employmenttypes.id 
    WHERE servicesemploymenttypes.serviceId = _serviceId AND servicesemploymenttypes.isActive = 1 AND servicesemploymenttypes.isDelete = 0 AND employmenttypes.isActive = 1 AND employmenttypes.isDelete = 0;
    
    SELECT * FROM companytypes; 
    
    SELECT * FROM industrytypes;
    
    SELECT * FROM residenttypes;
    
    SELECT * FROM banks;
    
    SELECT * FROM loanagainstcollteral;
    
    SELECT * FROM businessannualprofits;
    
    SELECT * FROM businessannualsales;
    
    SELECT * FROM businessexperience;
    
    SELECT * FROM businessnatures;
    
	SELECT * FROM propertytypes;
    
    SELECT * FROM employmentservicetypes;
    
    SELECT * FROM employmentnatures;
    
    select * from coapplicantrelations;
    
    select * from addresstypes;
    
    select * from tenure;
    
    SELECT * FROM documentmasters WHERE allowDocumentField = 1;
    
END;

DELIMITER ;

DROP procedure IF EXISTS `adminGetServiceTypeDocuments`;

DELIMITER ;;

CREATE PROCEDURE `adminGetServiceTypeDocuments`(IN _startIndex INT, IN _fetchRecords INT,IN _serviceId INT)
BEGIN
	DECLARE SqlQuery LONGTEXT DEFAULT "";
    DECLARE SQlQueryCount LONGTEXT DEFAULT "";
    DECLARE FilterQuery LONGTEXT DEFAULT "";
	IF _startIndex >= 0 AND _fetchRecords>0 THEN 
		SET @StartIndexQuery = CONCAT_WS('', " LIMIT ",_fetchRecords, " OFFSET ", _startIndex);
	END IF;
    SET @FilterQuery = "";
    IF _serviceId > 0 THEN  
		SET @FilterQuery = CONCAT_WS(''," AND servicetypedocuments.serviceId = ",_serviceId);
    END IF;
	SET @SqlQueryCount = CONCAT_WS('',"SELECT COUNT(servicetypedocuments.id) as totalCount
	FROM servicetypedocuments 
	INNER JOIN services ON services.id = servicetypedocuments.serviceId
	INNER JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId LEFT JOIN employmenttypes ON employmenttypes.id = servicetypedocuments.employmentTypeId WHERE servicetypedocuments.isDelete = 0 ",@FilterQuery);
	PREPARE countStmt FROM @SqlQueryCount;
    EXECUTE countStmt;
    DEALLOCATE PREPARE countStmt;
	SET @SqlQuery = CONCAT_WS('', "SELECT servicetypedocuments.*, services.displayName as serviceDisplayName, services.name as servicesName, documentmasters.name as documentName,documentmasters.maxSize,documentmasters.allowFileUpload,documentmasters.allowDocumentField,employmenttypes.name as employmentType
	FROM servicetypedocuments 
	INNER JOIN services ON services.id = servicetypedocuments.serviceId
	INNER JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId 
    LEFT JOIN employmenttypes ON employmenttypes.id = servicetypedocuments.employmentTypeId WHERE servicetypedocuments.isDelete = 0 AND documentmasters.allowFileUpload = true",@FilterQuery," ORDER BY id desc ", @StartIndexQuery);
	PREPARE stmt FROM @SqlQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

DELIMITER ;

DROP procedure IF EXISTS `customerGetMasterDataByServiceId`;

DELIMITER ;;

CREATE PROCEDURE `customerGetMasterDataByServiceId`(IN _serviceId INT)
BEGIN
	SELECT * FROM maritalstatuses;
    
    SELECT servicetypedocuments.*, services.displayName as serviceDisplayName, services.name as servicesName, documentmasters.name as documentName,documentmasters.maxSize as maxSize,documentmasters.allowFileUpload,documentmasters.allowDocumentField,documentmasters.documentFieldName
	FROM servicetypedocuments 
	INNER JOIN services ON services.id = servicetypedocuments.serviceId
	INNER JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId
	WHERE serviceId = _serviceId AND documentmasters.allowFileUpload = 1;
     
    SELECT employmenttypes.*,parentType.id  as parentTypeId, parentType.name as parentTypeName FROM employmenttypes 
	LEFT JOIN employmenttypes as parentType ON parentType.id = employmenttypes.parentId 
	INNER JOIN servicesemploymenttypes ON servicesemploymenttypes.employmentTypeId = employmenttypes.id 
    WHERE servicesemploymenttypes.serviceId = _serviceId AND servicesemploymenttypes.isActive = 1 AND servicesemploymenttypes.isDelete = 0 AND employmenttypes.isActive = 1 AND employmenttypes.isDelete = 0;
    
    SELECT * FROM companytypes;
    
    SELECT * FROM industrytypes;
    
    SELECT * FROM residenttypes;
    
    SELECT * FROM banks;
    
    SELECT * FROM loanagainstcollteral;
    
    SELECT * FROM businessannualprofits;
    
    SELECT * FROM businessannualsales;
    
    SELECT * FROM businessexperience;
    
    SELECT * FROM businessnatures;
    
	SELECT * FROM propertytypes;
    
    SELECT * FROM employmentservicetypes;
    
    SELECT * FROM employmentnatures;
    
    select * from coapplicantrelations;
    
    select * from addresstypes;
    
    select * from tenure;
    
    SELECT * FROM documentmasters WHERE allowDocumentField = 1;
    
END;

DELIMITER ;

DROP procedure IF EXISTS `customerGetIncompletePersonalLoanRequest`;

DELIMITER ;;

CREATE PROCEDURE `customerGetIncompletePersonalLoanRequest`(IN _customerId INT, IN _serviceId INT)
BEGIN
	DECLARE finished INT DEFAULT 0;
    DECLARE cUserId INT DEFAULT 0;
    DECLARE dateDiff INT DEFAULT 0;
    DECLARE cCustomerLoanId INT DEFAULT 0;
    DECLARE  rejectedDate date;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN 
		ROLLBACK;
	END;
	START TRANSACTION;
    BEGIN 
      DECLARE cur_getdate CURSOR FOR SELECT customerloanstatushistory.transactionDate FROM customerloanstatushistory LEFT JOIN customerloans ON customerloanstatushistory.customerLoanId = customerloans.id LEFT JOIN customers On customers.id = customerloans.customerId WHERE customerloanstatushistory.loanstatusId = 14 AND customers.id = _customerId AND customerloans.serviceId = _serviceId ORDER BY customerloans.id DESC limit 1 ;
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
        OPEN  cur_getdate;
        cur_getdate_loop: LOOP
				FETCH cur_getdate INTO rejectedDate;
                IF finished = 1 THEN
					LEAVE cur_getdate_loop;
				END IF;
            END LOOP cur_getdate_loop;
        CLOSE cur_getdate;
        IF rejectedDate THEN
           SET @dateDiff = rejectedDate;
		ELSE 
	       	 SET @dateDiff = CURRENT_TIMESTAMP();
        END IF;
	END;
    BEGIN
		DECLARE cur_customerloan CURSOR FOR SELECT customerloans.id FROM customerloans 
											inner join loancompletescreenhistory on loancompletescreenhistory.customerLoanId = customerloans.id
                                            LEFT join customerloanstatushistory on customerloanstatushistory.customerLoanId = customerloans.id
											LEFT join loanstatuses on loanstatuses.id = customerloanstatushistory.loanStatusId
											WHERE customerloans.serviceId = _serviceId AND customerId = _customerId AND customerloans.isDelete = 0
                                            AND (loancompletescreenhistory.isCompleted = false OR customerloans.statusId !=15 )
                                          AND (customerloans.statusId != 14 OR datediff(CURRENT_TIMESTAMP(), @dateDiff) <= 90)
                                          AND customerloans.isDelete = 0;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
        OPEN  cur_customerloan;
			cur_customerloan_loop: LOOP
				FETCH cur_customerloan INTO cCustomerLoanId;
                IF finished = 1 THEN
					LEAVE cur_customerloan_loop;
				END IF;
            END LOOP cur_customerloan_loop;
        CLOSE cur_customerloan;
        IF cCustomerLoanId > 0 THEN
        
			SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.* 
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND userroles.roleId IN(2);
            
			SELECT customerloans.*, employmenttypes.name as employmentType,services.displayName as displayName FROM customerloans 
            LEFT JOIN employmenttypes ON  employmenttypes.id = customerloans.employmentTypeId
            LEFT JOIN services ON  services.id = customerloans.serviceId
            WHERE customerloans.serviceId = _serviceId AND customerloans.id = cCustomerLoanId;
            
			SELECT customerloanemploymentdetails.*, companytypes.name AS companyTypeName, companyaddresses.addressTypeId, companyaddresses.label, companyaddresses.addressLine1
            , companyaddresses.addressLine2, companyaddresses.pincode, companyaddresses.cityId, companyaddresses.city, companyaddresses.district, companyaddresses.state, addresstypes.name as addressType
            FROM customerloanemploymentdetails 
            LEFT JOIN companytypes ON  companytypes.id = customerloanemploymentdetails.companyTypeId
            LEFT JOIN companyaddresses ON  companyaddresses.id = customerloanemploymentdetails.companyAddressId
            LEFT JOIN addresstypes ON  addresstypes.id = companyaddresses.addressTypeId
            WHERE customerloanemploymentdetails.customerloanId = cCustomerLoanId;
			
			SELECT * FROM customerloanspouses WHERE customerloanId = cCustomerLoanId;
            
			SELECT customerloandocuments.* FROM customerloandocuments 
            LEFT JOIN servicetypedocuments ON servicetypedocuments.id = customerloandocuments.serviceTypeDocumentId
            LEFT JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId
            WHERE customerloandocuments.customerloanId = cCustomerLoanId;		
			
			SELECT customerloanreferences.*,customerreferenceaddresses.* FROM customerloanreferences
			LEFT JOIN customerreferenceaddresses ON  customerreferenceaddresses.customerLoanReferenceId = customerloanreferences.id
			WHERE customerloanId = cCustomerLoanId;
            
            
			SELECT * FROM loancompletescreenhistory WHERE customerloanId = cCustomerLoanId;
            
            SELECT customerloanstatushistory.*, loanstatuses.status AS loanStatus, loanstatuses.isDataEditable AS isDataEditable 
            FROM customerloanstatushistory 
            INNER JOIN loanstatuses ON loanstatuses.id = customerloanstatushistory.loanStatusId 
            WHERE customerloanId IN(cCustomerLoanId);
            
			 SELECT clf.*,banks.name as bankName FROM customerloandetail as clf INNER JOIN banks ON clf.bankId = banks.id WHERE clf.isActive = 1 AND clf.isDelete = 0 AND clf.customerLoanId = cCustomerLoanId AND clf.isShared = 1 AND clf.status IS NULL;
            
            SELECT  customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId,customeraddresses.city,customeraddresses.state,customeraddresses.district
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id
            WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND customeraddresses.addresstypeId = 5 AND userroles.roleId IN(2);
            
            SELECT customerloantransferpropertydetails.* FROM customerloantransferpropertydetails WHERE customerLoanId = cCustomerLoanId;
            
            SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
            SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM customers WHERE id = _customerId);
		ELSE
			SELECT "Data Not Available" as message;
        END IF;
    END;
    COMMIT;
END;

DELIMITER ;

DROP procedure IF EXISTS `adminGetPersonalLoansByFilter`;


DELIMITER ;;

CREATE PROCEDURE `adminGetPersonalLoansByFilter`(IN _customerLoanId LONGTEXT)
BEGIN
	SET @sql = CONCAT('SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.*
			, maritalstatuses.status as maritalStatus
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN maritalstatuses ON maritalstatuses.id = customers.maritalStatusId
            WHERE customers.id IN (	SELECT customerloans.customerId FROM customerloans 
									LEFT JOIN employmenttypes ON  employmenttypes.id = customerloans.employmentTypeId
									LEFT JOIN services ON  services.id = customerloans.serviceId
				 					WHERE customerloans.id IN(', _customerLoanId, ')) AND users.isDelete = 0 AND users.isActive = 1 AND userroles.roleId IN(2)');
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
    
    SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "SELECT customerloans.*, employmenttypes.name as employmentType,services.displayName as displayName, users.fullName as rmFullName,loanstatuses.status,tenure.name as tenure FROM customerloans 
            LEFT JOIN employmenttypes ON  employmenttypes.id = customerloans.employmentTypeId
            LEFT JOIN users ON users.id = customerloans.assignRMId
            LEFT JOIN services ON  services.id = customerloans.serviceId
            LEFT JOIN loanstatuses ON loanstatuses.id = customerloans.statusId
            LEFT JOIN tenure ON customerloans.tenureId = tenure.id
            WHERE customerloans.id IN(", _customerLoanId,") ORDER BY customerloans.id DESC");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
            SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "SELECT customerloanemploymentdetails.*, companytypes.name AS companyTypeName, companyaddresses.addressTypeId, companyaddresses.label, companyaddresses.addressLine1
            , companyaddresses.addressLine2, companyaddresses.pincode, companyaddresses.cityId, companyaddresses.city, companyaddresses.district, companyaddresses.state, addresstypes.name as addressType
            FROM customerloanemploymentdetails 
            LEFT JOIN companytypes ON  companytypes.id = customerloanemploymentdetails.companyTypeId
            LEFT JOIN companyaddresses ON  companyaddresses.id = customerloanemploymentdetails.companyAddressId
            LEFT JOIN addresstypes ON  addresstypes.id = companyaddresses.addressTypeId
            WHERE customerloanemploymentdetails.customerloanId IN(", _customerLoanId,")");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
            SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "SELECT * FROM customerloanspouses WHERE customerloanId IN(", _customerLoanId,")");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
			SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "SELECT customerloandocuments.*, documentmasters.name as documentName, servicetypedocuments.isPdf FROM customerloandocuments 
            LEFT JOIN servicetypedocuments ON servicetypedocuments.id = customerloandocuments.serviceTypeDocumentId
            LEFT JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId
            WHERE customerloandocuments.customerloanId IN(", _customerLoanId,")");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
			SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "SELECT customerloanreferences.*,customerreferenceaddresses.id as customerreferenceaddressId,customerreferenceaddresses.label,customerreferenceaddresses.addressLine1,customerreferenceaddresses.addressLine2,customerreferenceaddresses.pincode,customerreferenceaddresses.cityId,customerreferenceaddresses.city,customerreferenceaddresses.district,customerreferenceaddresses.state FROM customerloanreferences LEFT JOIN customerreferenceaddresses ON  customerreferenceaddresses.customerLoanReferenceId = customerloanreferences.id WHERE customerloanId IN(", _customerLoanId ,")");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
			SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "SELECT * FROM loancompletescreenhistory WHERE customerloanId IN(", _customerLoanId ,")");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
			SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "SELECT customerloanstatushistory.*, loanstatuses.status AS loanStatus, loanstatuses.isDataEditable AS isDataEditable 
            FROM customerloanstatushistory 
            INNER JOIN loanstatuses ON loanstatuses.id = customerloanstatushistory.loanStatusId 
            WHERE customerloanId IN(", _customerLoanId,")  ORDER BY loanstatuses.displayOrder");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
            SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "select partners.id,partners.permanentCode,partners.fullName,partners.contactNo,partners.parentPartnerId, partnerscustomerloans.customerLoanId,partners.gender,roles.name as roleName from partnerscustomerloans
			INNER JOIN customerloans ON customerloans.id = partnerscustomerloans.customerLoanId
			INNER JOIN partners ON partners.id = partnerscustomerloans.partnerId
            INNER JOIN userroles ON userroles.userId = partners.userId
            INNER JOIN roles ON userroles.roleId = roles.id
			WHERE partnerscustomerloans.customerLoanId IN(", _customerLoanId,")");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;                 
            
               SET @getQuery ="";
            SET @getQuery = CONCAT_WS(''," SELECT customerloanoffers.*,banks.name as bankName FROM customerloanoffers INNER JOIN banks ON customerloanoffers.bankId = banks.id WHERE customerloanoffers.isDelete = 0 AND customerloanoffers.customerLoanId IN(", _customerLoanId,")");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
            SET @getQuery ="";
            SET @getQuery = CONCAT_WS(''," SELECT customerloandetail.*,banks.name as bankName FROM customerloandetail INNER JOIN banks ON customerloandetail.bankId = banks.id WHERE customerloandetail.isDelete = 0 AND customerloandetail.customerLoanId IN(", _customerLoanId,")");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
            SET @getQuery ="";
            SET @getQuery = CONCAT_WS(''," SELECT customerLoanId,reason FROM customerloanrejectionreason WHERE isDelete = 0 AND customerLoanId IN(", _customerLoanId,")");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
              SET @getQuery ="";
            SET @getQuery = CONCAT_WS(''," SELECT reason,customerLoanId,description FROM reasons WHERE isDelete = 0 AND customerLoanId IN(", _customerLoanId,")");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
			SET @getQuery ="";
            SET @getQuery = CONCAT_WS(''," SELECT customeraddresses.*  FROM users  INNER JOIN userroles ON userroles.userId = users.id  
            INNER JOIN customers ON customers.userId = users.id   
            INNER JOIN customeraddresses ON customeraddresses.customerId = customers.id
            WHERE customers.id IN (	SELECT customerloans.customerId FROM customerloans 
					WHERE customerloans.id IN(", _customerLoanId, ")) AND users.isDelete = 0 AND users.isActive = 1 AND userroles.roleId IN(2) AND customeraddresses.addressTypeId = 5");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
           SET @getQuery ="";
            SET @getQuery = CONCAT_WS('','SELECT customerloantransferpropertydetails.*,banks.name as bankName FROM customerloantransferpropertydetails INNER JOIN banks ON customerloantransferpropertydetails.bankId = banks.id WHERE customerLoanId IN(',_customerLoanId,')');
            PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
			 SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
            SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM customers INNER JOIN customerloans ON customerloans.customerId = customers.id WHERE customerloans.id = _customerLoanId);
            
		END;

DELIMITER ;


DROP procedure IF EXISTS `customerGetIncompleteBusinessLoanRequest`;

DELIMITER ;;

CREATE PROCEDURE `customerGetIncompleteBusinessLoanRequest`(IN _customerId INT, IN _serviceId INT)
BEGIN
	DECLARE finished INT DEFAULT 0;
    DECLARE cUserId INT DEFAULT 0;
    DECLARE cCustomerLoanId INT DEFAULT 0;
      DECLARE dateDiff INT DEFAULT 0;
        DECLARE  rejectedDate date;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK; 
	END;
	START TRANSACTION;
       BEGIN 
      DECLARE cur_getdate CURSOR FOR SELECT customerloanstatushistory.transactionDate FROM customerloanstatushistory LEFT JOIN customerloans ON customerloanstatushistory.customerLoanId = customerloans.id LEFT JOIN customers On customers.id = customerloans.customerId WHERE customerloanstatushistory.loanstatusId = 14 AND customers.id = _customerId AND customerloans.serviceId = _serviceId ORDER BY customerloans.id DESC limit 1 ;
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
        OPEN  cur_getdate;
        cur_getdate_loop: LOOP
				FETCH cur_getdate INTO rejectedDate;
                IF finished = 1 THEN
					LEAVE cur_getdate_loop;
				END IF;
            END LOOP cur_getdate_loop;
        CLOSE cur_getdate;
        IF rejectedDate THEN
           SET @dateDiff = rejectedDate;
		ELSE 
	       	 SET @dateDiff = CURRENT_TIMESTAMP();
        END IF;
	END;
    BEGIN
		DECLARE cur_customerloan CURSOR FOR SELECT customerloans.id FROM customerloans 
											inner join loancompletescreenhistory on loancompletescreenhistory.customerLoanId = customerloans.id
                                            LEFT join customerloanstatushistory on customerloanstatushistory.customerLoanId = customerloans.id
											LEFT join loanstatuses on loanstatuses.id = customerloanstatushistory.loanStatusId
											WHERE customerloans.serviceId = _serviceId AND customerId = _customerId  AND customerloans.isDelete = 0
                                            AND (loancompletescreenhistory.isCompleted = false OR  customerloans.statusId !=15)
                                             AND (customerloans.statusId != 14 OR datediff(CURRENT_TIMESTAMP(), @dateDiff) <= 90)
                                             AND customerloans.isDelete = 0;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
        OPEN  cur_customerloan;
			cur_customerloan_loop: LOOP
				FETCH cur_customerloan INTO cCustomerLoanId;
                IF finished = 1 THEN
					LEAVE cur_customerloan_loop;
				END IF;
            END LOOP cur_customerloan_loop;
        CLOSE cur_customerloan;
        IF cCustomerLoanId > 0 THEN
			SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.* 
            , customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id 
            WHERE customers.id = _customerId AND users.isDelete = 0 AND customeraddresses.addressTypeId = 1  AND users.isActive = 1 AND userroles.roleId IN(2);
            
			SELECT customerloans.*, employmenttypes.name as employmentType,services.displayName as displayName FROM customerloans 
            LEFT JOIN employmenttypes ON  employmenttypes.id = customerloans.employmentTypeId
            LEFT JOIN services ON  services.id = customerloans.serviceId
            WHERE customerloans.serviceId = _serviceId AND customerloans.id = cCustomerLoanId;
            
            SELECT customerloandocuments.* FROM customerloandocuments 
            LEFT JOIN servicetypedocuments ON servicetypedocuments.id = customerloandocuments.serviceTypeDocumentId
            LEFT JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId
            WHERE customerloandocuments.customerloanId = cCustomerLoanId;
            
            SELECT customerloanbusinessdetails.*, companytypes.name as companyType, industrytypes.name as industryTypes, businessexperience.name as businessExperience
			, businessnatures.name as businessNatures, businessannualprofits.name businessAnnualProfits, banks.name banks FROM customerloanbusinessdetails
			LEFT JOIN companytypes ON companytypes.id = customerloanbusinessdetails.companyTypeId
			LEFT JOIN industrytypes ON industrytypes.id = customerloanbusinessdetails.industryTypeId
			LEFT JOIN businessexperience ON businessexperience.id = customerloanbusinessdetails.businessExperienceId
			LEFT JOIN businessnatures ON businessnatures.id = customerloanbusinessdetails.businessNatureId
			LEFT JOIN businessannualprofits ON businessannualprofits.id = customerloanbusinessdetails.businessAnnualProfitId
			LEFT JOIN banks ON banks.id = customerloanbusinessdetails.primaryBankId
            WHERE customerloanbusinessdetails.customerloanId = cCustomerLoanId;
            
            SELECT customerloancurrentresidentdetails.*, residenttypes.name residentType FROM customerloancurrentresidentdetails
			LEFT JOIN residenttypes ON residenttypes.id = customerloancurrentresidentdetails.residentTypeId WHERE customerloancurrentresidentdetails.customerLoanId = cCustomerLoanId;
            
            SELECT * FROM loancompletescreenhistory WHERE customerloanId = cCustomerLoanId;
            
            SELECT customerloanstatushistory.*, loanstatuses.status AS loanStatus, loanstatuses.isDataEditable AS isDataEditable 
            FROM customerloanstatushistory 
            INNER JOIN loanstatuses ON loanstatuses.id = customerloanstatushistory.loanStatusId 
            WHERE customerloanId IN(cCustomerLoanId);
            
 SELECT clf.*,banks.name as bankName FROM customerloandetail as clf INNER JOIN banks ON clf.bankId = banks.id WHERE clf.isActive = 1 AND clf.isDelete = 0 AND clf.customerLoanId = cCustomerLoanId AND clf.isShared = 1 AND clf.status IS NULL;
            
           SELECT customerloantransferpropertydetails.* FROM customerloantransferpropertydetails WHERE customerLoanId = cCustomerLoanId;
			SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
            SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM customers WHERE id = _customerId);
		ELSE
			SELECT "Data Not Available" as message;
        END IF;
    END;
    COMMIT;
END;

DELIMITER ;

DROP procedure IF EXISTS `adminGetHomeLoanByCustomerLoanId`;

DELIMITER ;;

CREATE PROCEDURE `adminGetHomeLoanByCustomerLoanId`(IN _customerId INT,IN _customerLoanId INT)
BEGIN
	SET @sql = CONCAT('SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.*
			, maritalstatuses.status as maritalStatus,customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId,cities.name as cityName
            FROM users 
            LEFT    JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN maritalstatuses ON maritalstatuses.id = customers.maritalStatusId
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id 
            LEFT JOIN cities ON cities.id = customeraddresses.cityId
            WHERE customers.id IN (	SELECT customerloans.customerId FROM customerloans 
									LEFT JOIN employmenttypes ON  employmenttypes.id = customerloans.employmentTypeId
									LEFT JOIN services ON  services.id = customerloans.serviceId
									WHERE customerloans.id = ',_customerLoanId,' AND customeraddresses.addresstypeId = 1 AND users.isDelete = 0 AND users.isActive = 1)');
    PREPARE stmt FROM @sql; 
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
    
    SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', " SELECT customerloans.*, employmenttypes.name as employmentType,services.displayName as displayName, users.fullName as rmFullName,loanstatuses.status FROM customerloans 
            LEFT JOIN employmenttypes ON  employmenttypes.id = customerloans.employmentTypeId
            LEFT JOIN users ON users.id = customerloans.assignRMId
            LEFT JOIN services ON  services.id = customerloans.serviceId
            LEFT JOIN loanstatuses ON loanstatuses.id = customerloans.statusId
            WHERE customerloans.id  = ",_customerLoanId," ORDER BY customerloans.id DESC");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
	SET @getQuery = "";
			SET @getQuery = CONCAT_WS(''," SELECT customerloanemploymentdetails.*, companytypes.name AS companyTypeName, companyaddresses.addressTypeId, companyaddresses.label, companyaddresses.addressLine1
		, companyaddresses.addressLine2, companyaddresses.pincode, companyaddresses.cityId, companyaddresses.city, companyaddresses.district, companyaddresses.state, addresstypes.name as addressType,industrytypes.name as industryType
		FROM customerloanemploymentdetails 
		LEFT JOIN companytypes ON  companytypes.id = customerloanemploymentdetails.companyTypeId
		LEFT JOIN companyaddresses ON  companyaddresses.id = customerloanemploymentdetails.companyAddressId
		LEFT JOIN addresstypes ON  addresstypes.id = companyaddresses.addressTypeId
        LEFT JOIN industrytypes ON  industrytypes.id = customerloanemploymentdetails.industryTypeId
		WHERE customerloanemploymentdetails.customerloanId = ", _customerLoanId);
        PREPARE stmt FROM @getQuery;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
        
	SET @getQuery = "";
		SET @getQuery = CONCAT_WS('',"SELECT customerloancoapplicants.*, maritalstatuses.status as maritalStatus, coapplicantrelations.name as coApplicantRelation FROM customerloancoapplicants 
	INNER JOIN maritalstatuses ON maritalstatuses.id = customerloancoapplicants.maritalStatusId
	INNER JOIN coapplicantrelations ON coapplicantrelations.id = customerloancoapplicants.coApplicantRelationId
	WHERE customerloancoapplicants.customerloanId =", _customerLoanId);
     PREPARE stmt FROM @getQuery;
	 EXECUTE stmt;
	 DEALLOCATE PREPARE stmt;
     
     SET @getQuery = "";
		SET @getQuery = CONCAT_WS('',"SELECT customerloancoapplicantemploymentdetails.*, companyaddresses.addressTypeId, companyaddresses.label, companyaddresses.addressLine1
	, companyaddresses.addressLine2, companyaddresses.pincode, companyaddresses.cityId, companyaddresses.city, companyaddresses.district, companyaddresses.state, addresstypes.name as addressType
	, designations.name as designation, employmentnatures.name employmentNature, employmentservicetypes.name as employmentServiceType
	FROM customerloancoapplicantemploymentdetails
	INNER JOIN employmenttypes ON employmenttypes.id = customerloancoapplicantemploymentdetails.employmentTypeId
	LEFT JOIN companyaddresses ON  companyaddresses.id = customerloancoapplicantemploymentdetails.companyAddressId
	LEFT JOIN addresstypes ON  addresstypes.id = companyaddresses.addressTypeId
	LEFT JOIN designations ON designations.id = customerloancoapplicantemploymentdetails.designationId
	LEFT JOIN employmentnatures ON employmentnatures.id = customerloancoapplicantemploymentdetails.employmentNatureId
	LEFT JOIN employmentservicetypes ON employmentservicetypes.id = customerloancoapplicantemploymentdetails.employmentServiceTypeId
	WHERE customerloancoapplicantemploymentdetails.customerloanId = ", _customerLoanId);
    PREPARE stmt FROM @getQuery;
	 EXECUTE stmt;
	 DEALLOCATE PREPARE stmt;
     
     SET @getQuery = "";
     SET @getQuery = CONCAT_WS('',"SELECT customerloancurrentresidentdetails.*,residenttypes.name as residentType FROM customerloancurrentresidentdetails
	INNER JOIN residenttypes ON residenttypes.id = customerloancurrentresidentdetails.residentTypeId
	WHERE customerloancurrentresidentdetails.customerloanId = ",_customerLoanId);
    PREPARE stmt FROM @getQuery;
	 EXECUTE stmt;
	 DEALLOCATE PREPARE stmt;
     
     SET @getQuery = "";
     SET @getQuery = CONCAT_WS(''," SELECT  customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId
            FROM customeraddresses 
          WHERE customeraddresses.customerId = ",_customerId, " AND customeraddresses.addresstypeId = 3;");
	 PREPARE stmt FROM @getQuery;
	 EXECUTE stmt;
	 DEALLOCATE PREPARE stmt;
     
	SET @getQuery = "";
    SET @getQuery = CONCAT_WS(''," 	SELECT customerloandocuments.* ,documentmasters.name as documentName, servicetypedocuments.isPdf FROM customerloandocuments 
	LEFT JOIN servicetypedocuments ON servicetypedocuments.id = customerloandocuments.serviceTypeDocumentId
	LEFT JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId
	WHERE customerloandocuments.customerloanId =", _customerLoanId);
    PREPARE stmt FROM @getQuery;
	 EXECUTE stmt;
	 DEALLOCATE PREPARE stmt;
     
     SET @getQuery = "";
     SET @getQuery = CONCAT_WS('',"	SELECT * FROM loancompletescreenhistory WHERE customerloanId = ",_customerLoanId);
	PREPARE stmt FROM @getQuery;
	 EXECUTE stmt;
	 DEALLOCATE PREPARE stmt;
     
    SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "SELECT customerloanstatushistory.*, loanstatuses.status AS loanStatus, loanstatuses.isDataEditable AS isDataEditable 
            FROM customerloanstatushistory 
            INNER JOIN loanstatuses ON loanstatuses.id = customerloanstatushistory.loanStatusId 
            WHERE customerloanId = ",_customerLoanId,"  ORDER BY loanstatuses.displayOrder");
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
      SET @getQuery ="";
            SET @getQuery = CONCAT_WS(''," SELECT customerloanoffers.*,banks.name as bankName FROM customerloanoffers INNER JOIN banks ON customerloanoffers.bankId = banks.id WHERE customerloanoffers.isDelete = 0 AND customerloanoffers.customerLoanId = ",_customerLoanId);
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
            SET @getQuery ="";
            SET @getQuery = CONCAT_WS(''," SELECT customerloandetail.*,banks.name as bankName FROM customerloandetail INNER JOIN banks ON customerloandetail.bankId = banks.id WHERE customerloandetail.isDelete = 0 AND customerloandetail.customerLoanId =",_customerLoanId);
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
            SET @getQuery ="";
            SET @getQuery = CONCAT_WS(''," SELECT customerLoanId,reason FROM customerloanrejectionreason WHERE isDelete = 0 AND customerLoanId =",_customerLoanId);
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
              SET @getQuery ="";
            SET @getQuery = CONCAT_WS(''," SELECT reason,customerLoanId,description FROM reasons WHERE isDelete = 0 AND customerLoanId =",_customerLoanId);
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
              
            SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "select partners.id, partners.permanentCode,partners.fullName,partners.contactNo,partners.gender,roles.name as roleName, partnerscustomerloans.customerLoanId from partnerscustomerloans
			INNER JOIN customerloans ON customerloans.id = partnerscustomerloans.customerLoanId
			INNER JOIN partners ON partners.id = partnerscustomerloans.partnerId
			INNER JOIN userroles ON userroles.userId = partners.userId
            INNER JOIN roles ON userroles.roleId = roles.id
			WHERE partnerscustomerloans.customerLoanId =",_customerLoanId);
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
			SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "SELECT customerloanpropertydetails.*, propertytypes.name as propertyType FROM customerloanpropertydetails 
	INNER JOIN propertytypes ON propertytypes.id = customerloanpropertydetails.propertyTypeId 
	WHERE customerloanpropertydetails.customerloanId = ", _customerLoanId);
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
			SET @getQuery = "";
            SET @getQuery = CONCAT_WS(' ', "SELECT customerloantransferpropertydetails.*, banks.name as bankName FROM customerloantransferpropertydetails 
	INNER JOIN banks ON banks.id = customerloantransferpropertydetails.bankId 
	WHERE customerloantransferpropertydetails.customerloanId = ", _customerLoanId);
			PREPARE stmt FROM @getQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            
             SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
            SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM customers WHERE customers.id = _customerId);
            
END;

DELIMITER ;

DROP procedure IF EXISTS `getDocumentMaster`;

DELIMITER ;;

CREATE PROCEDURE `getDocumentMaster`()
BEGIN
	 SELECT * FROM documentmasters WHERE allowDocumentField = 1; 
END;

DELIMITER ;

DROP procedure IF EXISTS `adminGetBusinessLoanById`;

DELIMITER ;;

CREATE PROCEDURE `adminGetBusinessLoanById`(IN _customerLoanId INT)
BEGIN
SET @sql = CONCAT('SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.*
			, maritalstatuses.status as maritalStatus ,customeraddresses.id as customerAddressId, customeraddresses.label,customeraddresses.addressLine1,customeraddresses.addressLine2,customeraddresses.cityId,customeraddresses.city,customeraddresses.district,customeraddresses.state,customeraddresses.pincode
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN maritalstatuses ON maritalstatuses.id = customers.maritalStatusId
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id
            WHERE customers.id IN (	SELECT customerloans.customerId FROM customerloans 
									LEFT JOIN employmenttypes ON  employmenttypes.id = customerloans.employmentTypeId
									LEFT JOIN services ON  services.id = customerloans.serviceId
									WHERE customerloans.id IN(', _customerLoanId, ') ) AND users.isDelete = 0 AND users.isActive = 1 AND userroles.roleId IN(2)');
	PREPARE stmt FROM @sql; 
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
    SELECT customerloans.*, employmenttypes.name as employmentType, services.displayName as displayName, services.name as serviceName,loanagainstcollteral.name as  loanagainstcollteralName,designations.name as designation,loanstatuses.status FROM customerloans 
	LEFT JOIN employmenttypes ON  employmenttypes.id = customerloans.employmentTypeId
	LEFT JOIN services ON  services.id = customerloans.serviceId
    LEFT JOIN customerloanemploymentdetails ON customerloanemploymentdetails.customerLoanId = customerloans.id
    LEFT JOIN designations ON customerloanemploymentdetails.designationId = designations.id
    LEFT JOIN loanagainstcollteral ON  loanagainstcollteral.id = customerloans.loanAgainstCollateralId
     LEFT JOIN loanstatuses ON loanstatuses.id = customerloans.statusId
    
	WHERE customerloans.id = _customerLoanId;
	
	SELECT customerloandocuments.*, documentmasters.name as documentName, servicetypedocuments.isPdf FROM customerloandocuments 
	LEFT JOIN servicetypedocuments ON servicetypedocuments.id = customerloandocuments.serviceTypeDocumentId
	LEFT JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId
	WHERE customerloandocuments.customerloanId = _customerLoanId;
	
	SELECT customerloanbusinessdetails.*, companytypes.name as companyType, industrytypes.name as industryTypes, businessexperience.name as businessExperience
	, businessnatures.name as businessNatures, businessannualprofits.name businessAnnualProfits, banks.name banks FROM customerloanbusinessdetails
	LEFT JOIN companytypes ON companytypes.id = customerloanbusinessdetails.companyTypeId
	LEFT JOIN industrytypes ON industrytypes.id = customerloanbusinessdetails.industryTypeId
	LEFT JOIN businessexperience ON businessexperience.id = customerloanbusinessdetails.businessExperienceId
	LEFT JOIN businessnatures ON businessnatures.id = customerloanbusinessdetails.businessNatureId
	LEFT JOIN businessannualprofits ON businessannualprofits.id = customerloanbusinessdetails.businessAnnualProfitId
	LEFT JOIN banks ON banks.id = customerloanbusinessdetails.primaryBankId
	WHERE customerloanbusinessdetails.customerloanId = _customerLoanId;
	
	SELECT customerloancurrentresidentdetails.*, residenttypes.name as residentType FROM customerloancurrentresidentdetails
	LEFT JOIN residenttypes ON residenttypes.id = customerloancurrentresidentdetails.residentTypeId WHERE customerloancurrentresidentdetails.customerLoanId = _customerLoanId;

	SELECT * FROM loancompletescreenhistory WHERE customerloanId = _customerLoanId;
            
	SELECT customerloanstatushistory.*, loanstatuses.status AS loanStatus, loanstatuses.isDataEditable AS isDataEditable 
	FROM customerloanstatushistory 
	INNER JOIN loanstatuses ON loanstatuses.id = customerloanstatushistory.loanStatusId 
	WHERE customerloanId = _customerLoanId;
    
    SELECT customerloanoffers.*,banks.name as bankName FROM customerloanoffers INNER JOIN banks ON customerloanoffers.bankId = banks.id WHERE customerloanoffers.isDelete = 0 AND customerloanoffers.customerLoanId AND customerloanoffers.customerLoanId = _customerLoanId;
   
   SELECT customerloandetail.*,banks.name as bankName FROM customerloandetail INNER JOIN banks ON customerloandetail.bankId = banks.id WHERE customerloandetail.isDelete = 0 AND customerloandetail.customerLoanId AND customerloandetail.customerLoanId = _customerLoanId;
   
   SELECT customerLoanId,reason FROM customerloanrejectionreason WHERE isDelete = 0 AND customerLoanId = _customerLoanId;
		
   SELECT reason,customerLoanId,description FROM reasons WHERE isDelete = 0 AND customerLoanId = _customerLoanId;
   
   select partners.permanentCode,partners.id,partners.fullName,partners.contactNo,partners.gender,roles.name as roleName, partnerscustomerloans.customerLoanId,partners.parentPartnerId from partnerscustomerloans
			INNER JOIN customerloans ON customerloans.id = partnerscustomerloans.customerLoanId
			INNER JOIN partners ON partners.id = partnerscustomerloans.partnerId
             INNER JOIN userroles ON userroles.userId = partners.userId
            INNER JOIN roles ON userroles.roleId = roles.id
			WHERE partnerscustomerloans.customerLoanId = _customerLoanId;
           
	SELECT customerloantransferpropertydetails.*, banks.name as bankName FROM customerloantransferpropertydetails INNER JOIN banks ON customerloantransferpropertydetails.bankId = banks.id WHERE customerLoanId = _customerLoanId;
	
    SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
	SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM customers INNER JOIN customerloans ON customerloans.customerId = customers.id WHERE customerloans.id = _customerLoanId);
END;

DELIMITER ;

DROP procedure IF EXISTS `customerGetIncompleteCreditCardRequest`;

DELIMITER ;;

CREATE PROCEDURE `customerGetIncompleteCreditCardRequest`(IN _customerId INT, IN _serviceId INT)
BEGIN
	DECLARE finished INT DEFAULT 0;
    DECLARE cUserId INT DEFAULT 0;
    DECLARE cCustomerCreditCardId INT DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
	END;
	START TRANSACTION;
    BEGIN 
		DECLARE cur_customercreditcard CURSOR FOR SELECT customercreditcards.id FROM customercreditcards 
											   inner join creditcardcompletescreenhistory on creditcardcompletescreenhistory.customerCreditCardId = customercreditcards.id
                                               LEFT join customercreditcardstatushistory on customercreditcardstatushistory.customerCreditCardId = customercreditcards.id
										       LEFT join creditcardstatuses on creditcardstatuses.id = customercreditcardstatushistory.creditCardStatusId
                                               WHERE customercreditcards.customerId = _customerId 
                                               AND (creditcardcompletescreenhistory.isCompleted = false OR customercreditcards.statusId != 7);
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
        OPEN  cur_customercreditcard;
			cur_customercreditcard: LOOP
				FETCH cur_customercreditcard INTO cCustomerCreditCardId;
                IF finished = 1 THEN
					LEAVE cur_customercreditcard;
				END IF;
            END LOOP cur_customercreditcard;
        CLOSE cur_customercreditcard;
        IF cCustomerCreditCardId > 0 THEN
		
            
            SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.* 
            , customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id
            WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND customeraddresses.addresstypeId = 1 AND userroles.roleId IN(2);
            
			SELECT customercreditcards.* FROM customercreditcards 
            WHERE customercreditcards.id = cCustomerCreditCardId ;
		
            SELECT customercreditcardemploymentdetail.*
            FROM customercreditcardemploymentdetail 
            WHERE customercreditcardemploymentdetail.creditCardId = cCustomerCreditCardId;
            
             SELECT  customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id
            WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND customeraddresses.addresstypeId = 3 AND userroles.roleId IN(2);
            
            SELECT  customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id
            WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND customeraddresses.addresstypeId =2 AND userroles.roleId IN(2);			
          
		        SELECT * FROM creditcardcompletescreenhistory WHERE customerCreditCardId = cCustomerCreditCardId;
            
           SELECT customercreditcardstatushistory.*, creditcardstatuses.status AS creditCardStatus, creditcardstatuses.isDataEditable AS isDataEditable 
            FROM customercreditcardstatushistory 
            INNER JOIN creditcardstatuses ON creditcardstatuses.id = customercreditcardstatushistory.creditCardStatusId 
            WHERE customerCreditCardId IN(cCustomerCreditCardId);
            
              SELECT customercreditcardoffer.*, banks.id as bankId,banks.name as bankName FROM customercreditcardoffer
            INNER JOIN bankcreditcard ON bankcreditcard.id =  customercreditcardoffer.bankCreditCardId
			INNER JOIN banks ON banks.id = bankcreditcard.bankId WHERE customerCreditCardId IN(cCustomerCreditCardId);
            
             SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
            SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM customers WHERE customers.id = _customerId);
			
		ELSE
			SELECT "Data Not Available" as message;
        END IF;
    END;
    COMMIT;
END;

DELIMITER ;

DROP procedure IF EXISTS `adminGetPartnerById`;


DELIMITER ;;

CREATE PROCEDURE `adminGetPartnerById`(IN _partnerId INT)
BEGIN
	SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, roles.name as roleName, partners.*
    , partneraddress.id as partnerAddressId, partneraddress.addressTypeId as addressTypeId, partneraddress.label as label, partneraddress.addressLine1 as partnerAddressLine1
	, partneraddress.addressLine2 as partnerAddressLine2, partneraddress.pincode as partnerPincode, partneraddress.cityId as cityId, cities.name as city,districts.name as district,states.name as state, addresstypes.name as addressType, partnereducations.id as partnerEducationId
	, partnereducations.educationTypeId, partnereducations.id as partnerEducationId, educationtypes.name as educationType, partnereducations.resume as resume, partnereducations.passingYear as passingYear, partnereducations.otherDetail as otherDetail,designations.name as designation,professiontypes.name as professionType
    FROM users 
	INNER JOIN userroles ON userroles.userId = users.id 
    INNER JOIN roles ON roles.id = userroles.roleId 
	INNER JOIN partners ON partners.userId = users.id
	LEFT JOIN partneraddress ON partneraddress.partnerId = partners.id 
    LEFT JOIN cities ON cities.id = partneraddress.cityId
    LEFT JOIN districts ON cities.districtId = districts.id
    LEFT JOIN states ON states.id = districts.stateId
	LEFT JOIN addresstypes ON addresstypes.id = partneraddress.addressTypeId
	LEFT JOIN partnereducations ON partnereducations.partnerId = partners.id 
	LEFT JOIN educationtypes ON educationtypes.id = partnereducations.educationTypeId
    LEFT JOIN designations ON partners.designationId = designations.id
    LEFT JOIN professiontypes ON professiontypes.id = partners.professionTypeId
    WHERE partners.id = _partnerId;

	SELECT partnereducations.*, educationtypes.name FROM partnereducations 
	INNER JOIN educationtypes ON educationtypes.id = partnereducations.educationTypeId WHERE partnereducations.partnerId = _partnerId;

	SELECT partnerdocuments.*, documentmasters.name from partnerdocuments INNER JOIN documentmasters ON documentmasters.id = partnerdocuments.documentId WHERE partnerdocuments.partnerId = _partnerId;

	SELECT bankloanpartnercommissions.*, banks.name as bankName, commissiontypes.name as commissionType, services.name as serviceName FROM bankloanpartnercommissions 
	INNER JOIN banks ON banks.id = bankloanpartnercommissions.bankId
	INNER JOIN commissiontypes ON commissiontypes.id = bankloanpartnercommissions.commissionTypeId
	INNER JOIN services ON services.id = bankloanpartnercommissions.serviceId WHERE bankloanpartnercommissions.partnerId = _partnerId;
    
	SELECT customerloans.*, services.name as serviceName, customers.fullName, users.profilePicUrl FROM partnerscustomerloans 
    INNER JOIN customerloans ON customerloans.id =  partnerscustomerloans.customerLoanId  AND customerloans.isDelete = 0
    INNER JOIN customers ON customers.id = customerloans.customerId
    INNER JOIN users ON users.id = customers.userId
	INNER JOIN services ON services.Id = customerloans.serviceId
    /*INNER JOIN customerloanstatushistory on customerloanstatushistory.customerLoanId = customerloans.id*/
    WHERE partnerscustomerloans.partnerId = _partnerId
    ORDER BY customerloans.id DESC;
    
	SELECT customers.*, users.profilePicUrl FROM partnercustomers 
    INNER JOIN customers ON customers.id = partnercustomers.customerId
    INNER JOIN users ON users.id = customers.userId
	WHERE partnercustomers.partnerId = _partnerId  GROUP BY customers.id
    ORDER BY customers.id DESC ;
    
    select assignuserstraining.*, trainings.title as title, trainings.url as url, trainings.fileName as fileName, trainings.documentType as documentType,trainings.trainingCategoryId,trainings.trainingSubCategoryId,triningcategories.name as categoryName
	from assignuserstraining INNER JOIN trainings ON trainings.id = assignuserstraining.trainingId INNER JOIN triningcategories ON triningcategories.id = trainings.trainingCategoryId WHERE assignuserstraining.partnerId = _partnerId ORDER BY assignuserstraining.id DESC;
    
    SELECT partneraddress.*, addresstypes.name as addressType  from partneraddress INNER JOIN addresstypes ON addresstypes.id = partneraddress.addressTypeId 
    WHERE partneraddress.partnerId = _partnerId;
    
    SELECT visitingCardId,location FROM partnervisitingcards WHERE partnerId = _partnerId;
    
    SELECT partners.id,partners.fullName,partners.contactNo,partners.gender,partners.panCardNo,partners.cityId,partners.temporaryCode,partners.permanentCode,partners.companyName,partners.companyTypeId,partners.businessName,partners.isDelete,partners.createdDate,partners.isActive,cities.name as city,companytypes.name as companyType FROM partners 
    INNER JOIN users ON partners.userId = users.id 
    INNER JOIN userroles ON userroles.userId = partners.userId 
    LEFT JOIN companytypes ON companytypes.id = partners.companyTypeId 
    LEFT JOIN cities ON cities.id = partners.cityId 
    WHERE userroles.roleId = 5 AND partners.createdBy = (SELECT userId FROM partners WHERE id = _partnerId); 
    
    SELECT partners.id,partners.fullName,partners.contactNo,partners.gender,partners.panCardNo,partners.cityId,partners.temporaryCode,partners.permanentCode,partners.companyName,partners.companyTypeId,partners.businessName,partners.isDelete,partners.createdDate,partners.isActive,cities.name as city,companytypes.name as companyType,roles.name as roleName FROM partners 
    INNER JOIN userroles ON partners.userId = userroles.userId 
    INNER JOIN roles ON userroles.roleId = roles.id 
	LEFT JOIN companytypes ON companytypes.id = partners.companyTypeId 
    LEFT JOIN cities ON cities.id = partners.cityId 
    WHERE partners.parentPartnerId = _partnerId AND userroles.roleId NOT IN (5);
    
    SELECT userwallet.coin as totalCoin,userwallethistory.*,rewardtypes.name FROM userwallethistory
    LEFT JOIN rewardtypes ON userwallethistory.rewardType = rewardtypes.id
    LEFT JOIN users ON users.id = userwallethistory.userId
    LEFT JOIN partners ON partners.userId = users.id
    LEFT JOIN userwallet ON userwallethistory.userWalletId = userwallet.id
	WHERE partners.id = _partnerId;
    
    SELECT partnerbankdetails.id as partnerBankDetailId,partnerbankdetails.bankId,partnerbankdetails.accountHolderName,partnerbankdetails.accountNo,partnerbankdetails.ifscCode,banks.name as bankName FROM partnerbankdetails LEFT JOIN banks ON partnerbankdetails.bankId = banks.id WHERE partnerId = _partnerId;

	SELECT partnercommission.commission as PendingCommission,partnercommissionhistory.* FROM partnercommissionhistory INNER JOIN partnercommission ON partnercommission.id = partnercommissionhistory.partnerCommissionId WHERE partnercommission.partnerId = _partnerId;

	SELECT roles.name as parentRole,p1.fullName as parentPartner,p1.contactNo as parentContactNo,p1.permanentCode as parentCode,p2.id as parentParentPartnerId,r1.name as parentParentRole,p2.fullName as parentParentPartnerName,
p2.contactNo as parentParentPartnerContactNo,p2.permanentCode as parentParentCode  FROM partners as p1 LEFT JOIN partners as p2 ON p1.parentPartnerId = p2.id 
LEFT JOIN userroles ON userroles.userId = p1.userId LEFT JOIN userroles u2 ON u2.userId = p2.userId
LEFT JOIN roles ON roles.id = userroles.roleId
LEFT JOIN roles r1 ON r1.id = u2.roleId
WHERE p1.id = (SELECT parentPartnerId FROM partners WHERE id = _partnerId);

 SELECT orders.*,products.name as productName,orderstatuses.status FROM orders INNER JOIN products ON orders.productId = products.id INNER JOIN orderstatuses ON  orderstatuses.id = orders.orderStatusId WHERE orders.userId IN (SELECT partners.userId FROM partners WHERE partners.id = _partnerId);

 SELECT leads.*,leads.id as leadId,services.name as serviceName, leadaddresses.id as leadaddressesid, leadaddresses.addressTypeId as addressTypeId
					, leadaddresses.label as label, leadaddresses.addressLine1 as addressLine1, leadaddresses.addressLine2 as addressLine2, leadaddresses.pincode as pincode, leadaddresses.cityId as cityId
					, leadaddresses.district as district, leadaddresses.state as state, leadstatuses.name as leadstatus,partners.fullName as partnerName,partners.contactNo as partnerContactNo
					FROM leads 
					INNER JOIN services ON services.id = leads.serviceId 
                    LEFT JOIN leadaddresses ON leadaddresses.leadId = leads.id
	                LEFT JOIN leadstatuses ON leadstatuses.id = leads.leadStatusId
                    LEFT JOIN partners ON partners.id = leads.assignToPartnerId
					WHERE leads.createdBy IN  (SELECT userId FROM partners WHERE id = _partnerId) AND leads.isDelete = 0;
                    
SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM partners WHERE partners.id = _partnerId);      
                    
END;

DELIMITER ;

DROP procedure IF EXISTS `customerVerifyContactNo`;


DELIMITER ;;

CREATE PROCEDURE `customerVerifyContactNo`(IN _countryCode VARCHAR(6), IN _contactNo VARCHAR(15), IN _email VARCHAR(256), IN _temporaryCode VARCHAR(45), IN _appId INT
, IN _deviceId VARCHAR(300), IN _fcmToken VARCHAR(500), IN _deviceManufacturer VARCHAR(300), IN _deviceModel VARCHAR(100), IN _sessionToken LONGTEXT, IN _appVersion VARCHAR(100)
, IN _partnerUserId INT, IN _rewardTypeId INT, IN _rewarsCoin DECIMAL(15,3))
BEGIN
	DECLARE finished_check INT DEFAULT 0;
	DECLARE cUserId INT DEFAULT 0;
	DECLARE cRoleId INT DEFAULT 0;
    
    DECLARE finished INT DEFAULT 0;
    DECLARE session_done INT DEFAULT 0;
    DECLARE deviceDetail_done INT DEFAULT 0;
    DECLARE cIsDisabled INT DEFAULT 1;
    DECLARE cRoleName VARCHAR(45) DEFAULT NULL;
    DECLARE cAppId INT DEFAULT 0;
    DECLARE dAppId INT DEFAULT 0;
	DECLARE dfcmToken VARCHAR(500) DEFAULT NULL;
	DECLARE dDeviceId VARCHAR(100) DEFAULT NULL;
	
    DECLARE insertDeviceDetailFlag INT DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
	  ROLLBACK;
	END;
    START TRANSACTION;
		BEGIN
			DECLARE cur_user_check CURSOR FOR SELECT users.id as userId, userroles.roleId as roleId FROM users LEFT JOIN userroles ON userroles.userId = users.id WHERE users.contactNo = _contactNo;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished_check = 1;
			OPEN  cur_user_check;
				cur_user_check_loop: LOOP
					FETCH cur_user_check INTO cUserId, cRoleId;
                    
                    IF finished_check = 1 THEN
						LEAVE cur_user_check_loop;
					END IF;
                    
                END LOOP cur_user_check_loop;
			CLOSE cur_user_check;
            IF cUserId = 0 THEN
				INSERT INTO users(countryCode,contactNo,isDisabled,email,currentRoleId) VALUES(_countryCode,_contactNo,0,_email,2);
                SET @userId = @@IDENTITY;
                SET @createdBy = @userId;
                SET cUserId = @userId;
                                
                IF _partnerUserId > 0 THEN
					SET @createdBy = _partnerUserId;
                END IF;
                
                INSERT INTO userroles(userId,roleId,createdBy,modifiedBy) VALUES(@userId, 2, @createdBy, @createdBy);                
                INSERT INTO customers(userId, temporaryCode, contactNo, createdBy, modifiedBy,currentRoleId) VALUES(@userId, _temporaryCode, _contactNo, @createdBy, @createdBy,2);
                SET @customerId = @@IDENTITY;
                INSERT INTO customeraddresses(customerId,createdBy,modifiedBy,addressTypeId) VALUES(@customerId, @createdBy, @createdBy,1);
                
                INSERT INTO userscratchcards(userId, value, rewardType, createdBy, modifiedBy) VALUES(@userId, _rewarsCoin, _rewardTypeId, @userId, @userId);
                
                INSERT INTO userdevicedetail (userId, appId, deviceId, fcmToken, deviceLocation, deviceManufacturer, deviceModel, appVersion)
				VALUES(cUserId, _appId, _deviceId, _fcmToken, NULL, _deviceManufacturer, _deviceModel, _appVersion);
            END IF;
            SET insertDeviceDetailFlag = 0;
            BEGIN
				DECLARE cur_user CURSOR FOR SELECT users.id, users.isDisabled, roles.id as roleId, roles.name as roleName FROM users 
											INNER JOIN userroles ON userroles.userId = users.id 
											INNER JOIN roles ON roles.id = userroles.roleId
											INNER JOIN customers ON customers.userId = users.Id
											WHERE users.contactNo = _contactNo AND userroles.roleId IN(2);
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
				OPEN  cur_user;
					cur_user_loop: LOOP
						FETCH cur_user INTO cUserId, cIsDisabled, cRoleId, cRoleName;
						IF finished = 1 THEN
							LEAVE cur_user_loop;
						END IF;
						IF cUserId>0 AND cRoleId >0 AND cIsDisabled=0 THEN
							BEGIN
								DECLARE cur_session CURSOR FOR SELECT usersessions.appId FROM users INNER JOIN usersessions ON users.id = usersessions.userId WHERE usersessions.sessionToken = (SELECT se.sessionToken FROM usersessions se WHERE se.userId = cUserId  ORDER BY userId DESC LIMIT 1)  AND usersessions.expireAt > CURDATE();
								DECLARE CONTINUE HANDLER FOR NOT FOUND SET session_done = 1;
								OPEN  cur_session;
									cur_get_session: LOOP
										FETCH cur_session INTO cAppId;
										IF session_done = 1 THEN
											LEAVE cur_get_session;
										END IF;
										IF cRoleId = 2 THEN
											BEGIN
												DECLARE cur_deviceDetails CURSOR FOR SELECT userdevicedetail.appId, userdevicedetail.deviceId, userdevicedetail.fcmToken FROM userdevicedetail  WHERE userdevicedetail.userId = cUserId;
												DECLARE CONTINUE HANDLER FOR NOT FOUND SET deviceDetail_done = 1;
												OPEN  cur_deviceDetails;
													cur_get_deviceDetails:LOOP
														FETCH cur_deviceDetails INTO dAppId, dDeviceId, dfcmToken;
														IF deviceDetail_done = 1 THEN
															IF (@insertDeviceDetailFlag = 0 OR @insertDeviceDetailFlag IS NULL) THEN
																INSERT INTO userdevicedetail (userId, appId, deviceId, fcmToken, deviceLocation, deviceManufacturer, deviceModel, appVersion)
																VALUES(cUserId, _appId, _deviceId, _fcmToken, NULL, _deviceManufacturer, _deviceModel, _appVersion);
                                                                SET @insertDeviceDetailFlag = 1;
                                                            END IF;
															LEAVE cur_get_deviceDetails;
														END IF;
														IF dAppId > 0 THEN
															IF cAppId > 0 THEN
																IF cAppId != dAppId THEN
																	INSERT INTO userdevicedetail (userId, appId, deviceId, fcmToken, deviceLocation, deviceManufacturer, deviceModel, appVersion)
																	VALUES(cUserId, _appId, _deviceId, _fcmToken, NULL, _deviceManufacturer, _deviceModel, _appVersion);
                                                                    SET @insertDeviceDetailFlag = 1;
																ELSE 
																	UPDATE userdevicedetail SET deviceId = _deviceId,fcmToken = _fcmToken , deviceManufacturer = _deviceManufacturer, deviceModel = _deviceModel, modifiedDate = current_timestamp()  WHERE userId= cUserId;
                                                                    SET @insertDeviceDetailFlag = 1;
                                                                     
																END IF;
															ELSE
																IF dDeviceId != _deviceId OR dfcmToken != _fcmToken THEN
																	UPDATE userdevicedetail SET deviceId = _deviceId,fcmToken = _fcmToken , deviceManufacturer = _deviceManufacturer, deviceModel = _deviceModel, modifiedDate = current_timestamp() WHERE userId= cUserId;
                                                                    SET @insertDeviceDetailFlag  = 1;
																END IF;
															END IF;
														ELSE
															INSERT INTO userdevicedetail (userId, appId, deviceId, fcmToken, deviceLocation, deviceManufacturer, deviceModel, appVersion)
															VALUES(cUserId, _appId, _deviceId, _fcmToken, NULL, _deviceManufacturer, _deviceModel, _appVersion);
                                                            SET @insertDeviceDetailFlag  = 1;
														END IF;
													END LOOP cur_get_deviceDetails;
												CLOSE cur_deviceDetails;
											END;
										END IF;
									END LOOP cur_get_session;
								CLOSE cur_session;
							END;
						ELSE 
							LEAVE cur_user_loop;
						END IF;
					END LOOP cur_user_loop;
				CLOSE cur_user;
				IF cIsDisabled = 0 THEN
					SELECT roles.id, roles.name From userroles INNER JOIN roles ON roles.id = userroles.roleId WHERE userroles.userId = cUserId;
					
					INSERT INTO usersessions(sessionToken, appId, expireAt, userId) VALUES(_sessionToken, _appId, TIMESTAMPADD(DAY,60,CURRENT_TIMESTAMP()), cUserId);
					
					SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.*
                    , customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId
                    FROM users 
                    INNER JOIN userroles ON userroles.userId = users.id 
                    INNER JOIN customers ON customers.userId = users.id 
                    LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id 
                    WHERE users.contactNo = _contactNo  AND customeraddresses.addressTypeId = 1 AND users.isDelete = 0 AND users.isActive = 1 AND userroles.roleId IN(2);
				
                SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
				SELECT * FROM serviceuserfield WHERE userId = (SELECT id FROM users WHERE users.contactNo = _contactNo);    
                ELSE
					SELECT 'User Not Verified' as message;
				END IF;
			END;
            
        END;
    COMMIT;
END;

DELIMITER ;

DROP procedure IF EXISTS `customerVerifySessionToken`;

DELIMITER ;;

CREATE PROCEDURE `customerVerifySessionToken`(IN _sessionToken LONGTEXT, IN _appId INT, IN _deviceId VARCHAR(300), IN _fcmToken VARCHAR(500), IN _deviceManufacturer VARCHAR(300), IN _deviceModel VARCHAR(100), IN _appVersion VARCHAR(100))
BEGIN
	DECLARE finished INT DEFAULT 0;
	DECLARE cUserId INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    
    SET @LastUpdateID := 0;
    BEGIN
	  ROLLBACK; 
	END;
    START TRANSACTION;
    UPDATE usersessions SET usersessions.modifiedDate = CURRENT_TIMESTAMP(), expireAt=TIMESTAMPADD(DAY,60,CURRENT_TIMESTAMP()), id = (SELECT @LastUpdateID := id)  WHERE sessionToken = _sessionToken;
    BEGIN
		DECLARE cur_user CURSOR FOR SELECT users.id FROM users INNER JOIN usersessions ON users.id = usersessions.userId WHERE usersessions.sessionToken = _sessionToken AND usersessions.expireAt > CURDATE() AND users.isDisabled = 0;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
		OPEN  cur_user;
			cur_user_loop: LOOP
				FETCH cur_user INTO cUserId;
					IF finished = 1 THEN
						LEAVE cur_user_loop;
					END IF;
			END LOOP cur_user_loop;
		CLOSE cur_user;
        IF cUserId>0 THEN
			SELECT roles.id, roles.name From userroles INNER JOIN roles ON roles.id = userroles.roleId WHERE userroles.userId = cUserId;
            SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.* 
            , customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id 
            WHERE users.id = cUserId AND customeraddresses.addressTypeId = 1  AND users.isDelete = 0 AND users.isActive = 1 AND userroles.roleId IN(2);
      
			SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
			SELECT * FROM serviceuserfield WHERE userId = cUserId;    
      ELSE
			SELECT 'User Not Verified' as message;
        END IF;
    END;
    COMMIT;
END;

DELIMITER ;

DROP procedure IF EXISTS `adminGetCustomerById`;

DELIMITER ;;

CREATE PROCEDURE `adminGetCustomerById`(IN _customerId INT)
BEGIN
	SELECT customers.*,users.email,users.profilePicUrl,customeraddresses.id as customerAddressId,customeraddresses.label,customeraddresses.addressLine1,customeraddresses.addressLine2,customeraddresses.pincode,customeraddresses.cityId,customeraddresses.city,customeraddresses.district,customeraddresses.state,maritalstatuses.status as maritalStatus FROM customers INNER JOIN users ON customers.userId = users.id 
    LEFT JOIN customeraddresses  ON customeraddresses.customerId = customers.id
    LEFT JOIN maritalstatuses ON maritalstatuses.id = customers.maritalStatusId
    WHERE customers.isDelete = 0 AND customers.id = _customerId ;
	
	SELECT customerloans.*, services.name as serviceName,employmenttypes.name as employmentType,loanstatuses.status as loanStatus,customerloandetail.amountDisbursed,customerloandetail.ROI,customerloandetail.tenure,customerloandetail.emi,customerloandetail.totalInterestpayable FROM customerloans
    LEFT JOIN services ON customerloans.serviceId = services.id 
    LEFT JOIN employmenttypes ON employmenttypes.id = customerloans.employmentTypeId
    LEFT JOIN loanstatuses ON loanstatuses.id = customerloans.statusId
    LEFT JOIN customerloandetail ON customerloandetail.customerLoanId = customerloans.id
    WHERE customerId = _customerId; 
    
	SELECT leads.*,services.name as serviceName,employmenttypes.name as employmentType,partners.fullName as assignPartnerName,partners.contactNo as assignPartnerContactNo,leadstatuses.name as leadStatus FROM leads
	LEFT JOIN leadstatuses ON leadstatuses.id = leads.leadStatusId
    LEFT JOIN services ON leads.serviceId = services.id 
    LEFT JOIN employmenttypes ON employmenttypes.id = leads.employmentTypeId
    LEFT JOIN partners ON partners.id = leads.assignToPartnerId
    WHERE leads.contactNo = (SELECT contactNo FROM customers WHERE id  = _customerId);
    
    SELECT userwallet.coin as totalCoin,userwallethistory.*,rewardtypes.name FROM userwallethistory
    LEFT JOIN rewardtypes ON userwallethistory.rewardType = rewardtypes.id
    LEFT JOIN users ON users.id = userwallethistory.userId
    LEFT JOIN customers ON customers.userId = users.id
    LEFT JOIN userwallet ON userwallethistory.userWalletId = userwallet.id
	WHERE customers.id = _customerId;
    
    SELECT partners.parentPartnerId,partners.fullName,partners.contactNo,partners.temporaryCode,partners.permanentCode,partners.gender,partners.panCardNo,roles.name as partnerRoleName,partners.userId FROM partners
    LEFT JOIN users ON users.id = partners.userId
    LEFT JOIN userroles ON partners.userId = userroles.id
    LEFT JOIN roles ON roles.id = userroles.roleId
    LEFT JOIN customers ON customers.createdBy = users.id
    WHERE customers.id = _customerId AND customers.createdBy != _customerId;
    
      SELECT orders.*,products.name as productName,orderstatuses.status FROM orders INNER JOIN products ON orders.productId = products.id INNER JOIN orderstatuses ON  orderstatuses.id = orders.orderStatusId WHERE userId IN (SELECT userId FROM customers WHERE id = _customerId);
    
    SELECT customerotherloans.* ,employmenttypes.name as employmentType,servicetypes.name as serviceType,services.name as serviceName FROM customerotherloans
	INNER JOIN servicetypes ON servicetypes.id = customerotherloans.serviceTypeId
	INNER JOIN services ON services.id = customerotherloans.serviceId
	INNER JOIN customers ON customers.userId = customerotherloans.userId
	INNER JOIN employmenttypes ON employmenttypes.id = customerotherloans.employmentTypeId
	WHERE customerotherloans.userId = (SELECT customers.userId FROM customers WHERE customers.id = _customerId);

  SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM customers WHERE id = _customerId);
END;

DELIMITER ;

DROP procedure IF EXISTS `adminInsertCustomer`;

DELIMITER ;;

CREATE PROCEDURE `adminInsertCustomer`(IN _customerId INT,IN _customerUserId INT,IN _temporyCode VARCHAR(45),IN _permanentCode VARCHAR(45),IN _fullName VARCHAR(200),IN _birthDate datetime,IN _gender VARCHAR(15),IN _contactNo VARCHAR(15),IN _email VARCHAR(200),IN _alterNativeContactNo VARCHAR(15),IN _partnerId INT,IN _panCardNo VARCHAR(20),IN _aadharCardNo VARCHAR(20),IN _maritalStatusId INT,IN _userId INT,IN _customerAddressId INT,IN _label LONGTEXT,IN _addressLine1 LONGTEXT,IN _addressLine2 LONGTEXT,IN _cityId INT,IN _city VARCHAR(100),IN _district VARCHAR(100),IN _state VARCHAR(100),IN _profilePicUrl LONGTEXT,IN _pincode VARCHAR(20),IN _cibilScore decimal(15,2))
BEGIN
	DECLARE sqlQuery LONGTEXT DEFAULT "";
    DECLARE userUpdateQuery LONGTEXT DEFAULT "";
    DECLARE customerAddressQuery LONGTEXT DEFAULT "";
    DECLARE finished INT DEFAULT 0;
    DECLARE _rewardCoin INT DEFAULT 0;
    DECLARE insertUserId INT DEFAULT 0;
    DECLARE customerUserId INT DEFAULT 0;
    SET @sqlQuery = ""; 
    SET @userUpdateQuery = "";
     SET @customerAddressQuery = "";
	IF(_customerId > 0) THEN 
			SET @sqlQuery= CONCAT_WS(''," UPDATE customers SET temporaryCode = '",_temporyCode,"', fullName = '",_fullName,"',birthDate = '",_birthDate,"', gender = '",_gender,"', contactNo = '",_contactNo,"', panCardNo = '",_panCardNo,"', aadhaarCardNo = '",_aadharCardNo,"' ,maritalStatusId = ",_maritalStatusId,",modifiedBy = ",_userId," ,modifiedDate = '",CURRENT_TIMESTAMP(),"',currentRoleId = 2,cibilScore = ",_cibilScore,"");
			SET @userUpdateQuery = CONCAT_WS(''," UPDATE users SET fullName = '",_fullName,"', gender = '", _gender,"', contactNo = '",_contactNo,"',modifiedBy = ",_userId,",modifiedDate = '",CURRENT_TIMESTAMP(),"',currentRoleId = 2");
	ELSE 
		SET @sqlQuery = CONCAT_WS(''," INSERT INTO customers SET temporaryCode = '",_temporyCode,"', fullName = '",_fullName,"', birthDate = '",_birthDate,"', gender = '",_gender,"' ,contactNo = '",_contactNo,"', panCardNo = '",_panCardNo,"',aadhaarCardNo = '",_aadharCardNo,"', maritalStatusId = ",_maritalStatusId,",modifiedBy = ",_userId,",modifiedDate = '",CURRENT_TIMESTAMP() ,"',createdBy = ",_userId," ,createdDate = '",CURRENT_TIMESTAMP(),"',currentRoleId = 2,cibilScore = ",_cibilScore,"");
		SET @userUpdateQuery = CONCAT_WS(''," INSERT INTO users SET fullName = '",_fullName,"', gender = '", _gender,"', contactNo = '",_contactNo,"',modifiedBy = ",_userId,",modifiedDate = '",CURRENT_TIMESTAMP(), "', createdBy = ",_userId,",createdDate = '",CURRENT_TIMESTAMP(),"',countryCode = '+91',isDisabled = false,currentRoleId = 2");
		 
    END IF;
		IF(_permanentCode != '') THEN
			SET @sqlQuery = CONCAT_WS('',@sqlQuery," , permanentCode = '",_permanentCode,"'");
		END IF;
		IF(_alterNativeContactNo != '') THEN
			SET @sqlQuery = CONCAT_WS('',@sqlQuery," , alterNativeContactNo = '",_alterNativeContactNo,"'");
		END IF;
		IF(_partnerId > 0) THEN
			SET @sqlQuery = CONCAT_WS('',@sqlQuery," ,partnerId = ",__partnerId);
		END IF;
		IF (_profilePicUrl != '') THEN
			SET @userUpdateQuery = CONCAT_WS('', @userUpdateQuery," , profilePicUrl = '",_profilePicUrl,"'");
		END IF;
		IF(_email != '') THEN
			SET @userUpdateQuery = CONCAT_WS('', @userUpdateQuery," , email = '",_email,"'");
		END IF;
        IF(_customerId > 0) THEN 
			SET @userUpdateQuery = CONCAT_WS('',@userUpdateQuery," WHERE id = ",_customerUserId);
        END IF;
        
		PREPARE userStmt FROM @userUpdateQuery;
        EXECUTE userStmt;
        DEALLOCATE PREPARE userStmt;
        IF(_customerUserId = 0) THEN
        SET _customerUserId = @@IDENTITY;
        END IF;
		IF(_customerUserId > 0)THEN
			IF(_customerId = 0) THEN
				INSERT INTO userroles (userId,roleId,createdBy,modifiedBy) VALUES (_customerUserId,2,_userId,_userId);
				IF 	EXISTS(SELECT 1 FROM rewardcoin) THEN
				BEGIN
				DECLARE cur_checkexist cursor for select rewardCoin FROM rewardcoin WHERE isDelete =0 AND rewardTypeId = 2;
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
				OPEN cur_checkexist;
					SET @exist =0;
					getRewardCoin: LOOP
						FETCH cur_checkexist INTO _rewardCoin;
						IF finished = 1 THEN 
							LEAVE getRewardCoin;
						END IF;
						IF _rewardCoin THEN
							INSERT INTO userscratchcards(userId, value, createdBy, modifiedBy,rewardType) VALUES(_customerUserId, _rewardCoin,_userId, _userId,2);
						END IF;
                    END LOOP;
				CLOSE cur_checkexist;
				END;
			END IF;
			END IF;
			SET @sqlQuery = CONCAT_WS('',@sqlQuery," ,userId = ",_customerUserId);
			IF(_customerId > 0) THEN 
				SET @sqlQuery = CONCAT_WS('',@sqlQuery," WHERE id = ",_customerId);
			END IF;
			PREPARE stmt FROM @sqlQuery;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
            IF(_customerId = 0) THEN
			SET _customerId = @@IDENTITY;
			END IF;
        END IF;
        IF (_customerId > 0) THEN
        IF(_cityId > 0 AND _pincode != '') THEN
			IF(_customerAddressId > 0)THEN 
				SET @customerAddressQuery = CONCAT_WS(''," UPDATE customeraddresses SET cityId = ",_cityId,", pincode = '",_pincode,"',modifiedDate = '",CURRENT_TIMESTAMP(),"',modifiedBy = ",_userId,",addressLine2 = '",_addressLine2,"'");
			ELSE
				SET @customerAddressQuery = CONCAT_WS(''," INSERT INTO customeraddresses SET customerId = ",_customerId,",cityId = ",_cityId,", pincode = '",_pincode,"',modifiedDate = '",CURRENT_TIMESTAMP(),"', modifiedBy = ",_userId,", createdBy = ",_userId," ,createdDate = '",CURRENT_TIMESTAMP(),"', addressTypeId = 1 ");
			END IF;
			IF(_addressLine1 != '') THEN
				SET @customerAddressQuery = CONCAT_WS('', @customerAddressQuery," , addressLine1 = '",_addressLine1,"'");
			END IF;
			/*IF(_addressLine2 != '') THEN
				SET @customerAddressQuery = CONCAT_WS('', @customerAddressQuery," , addressLine2 = '",_addressLine2,"'");
			END IF;*/
			IF(_label != '') THEN
				SET @customerAddressQuery = CONCAT_WS('', @customerAddressQuery," , label = '",_label,"'");
			END IF;
			IF(_city != '') THEN
				SET @customerAddressQuery = CONCAT_WS('', @customerAddressQuery," , city = '",_city,"'");
			END IF;
			IF(_district != '') THEN
				SET @customerAddressQuery = CONCAT_WS('', @customerAddressQuery," , district = '",_district,"'");
			END IF;
			IF(_state != '') THEN
				SET @customerAddressQuery = CONCAT_WS('', @customerAddressQuery," , state = '",_state,"'");
			END IF;       
            IF(_customerAddressId > 0) THEN 
				SET @customerAddressQuery = CONCAT_WS('', @customerAddressQuery," WHERE id = ",_customerAddressId);
			END IF;
		END IF;
        PREPARE stmt FROM @customerAddressQuery;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT _customerUserId as customerUserId;
        END IF;
END;

DELIMITER ;

DROP procedure IF EXISTS `adminConvertLeadIntoLoan`;

DELIMITER ;;

CREATE  PROCEDURE `adminConvertLeadIntoLoan`(IN _leadId INT, IN _customerId INT,IN _serviceId INT, IN _loanAmount decimal(15,3),IN _employmentTypeId INT, IN _userId INT,IN _assignPartnerId INT,IN _contactNo VARCHAR(20),IN _fullName VARCHAR(200),IN _countryCode VARCHAR(20),IN _temporaryCode VARCHAR(50),IN _addressTypeId INT, IN _label LONGTEXT, IN _addressLine1 LONGTEXT
, IN _addressLine2 LONGTEXT, IN _pincode VARCHAR(10), IN _cityId INT, IN _city VARCHAR(100), IN _district VARCHAR(100), IN _state VARCHAR(50),IN _aadharCardNo VARCHAR(20),IN _panCardNo VARCHAR(20))
BEGIN
	DECLARE finished INT DEFAULT 0;
	DECLARE cUserId INT DEFAULT 0;
	DECLARE cRoleId INT DEFAULT 0;
    DECLARE customerLoanId INT DEFAULT 0;
	BEGIN
		DECLARE cur_user CURSOR FOR SELECT users.id as userId, userroles.roleId as roleId FROM users LEFT JOIN userroles ON userroles.userId = users.id WHERE users.contactNo = _contactNo;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
		OPEN  cur_user;
			cur_user_loop: LOOP 
				FETCH cur_user INTO cUserId, cRoleId;
                    
				IF finished = 1 THEN
					LEAVE cur_user_loop;
				END IF;
                    
			END LOOP cur_user_loop;
		CLOSE cur_user;
            
	
        END;
        	IF cUserId = 0 THEN
			INSERT INTO users(fullName, countryCode, contactNo, isDisabled, createdBy, modifiedBy,currentRoleId) VALUES(_fullName, _countryCode, _contactNo, 0, _userId, _userId,2);
			SET @userId = @@IDENTITY;
			INSERT INTO userroles (userId,roleId,createdBy,modifiedBy) VALUES (@userId,2,_userId,_userId);
			INSERT INTO customers(userId, temporaryCode, fullName, contactNo,aadhaarCardNo,panCardNo, createdBy, modifiedBy,currentRoleId) VALUES(@userId, _temporaryCode, _fullName, _contactNo,_aadharCardNo,_panCardNo, _userId, _userId,2);
			SET _customerId = @@IDENTITY;
			INSERT INTO customeraddresses(customerId,addressTypeId,label,addressLine1,addressLine2,pincode,cityId,city,district,state,createdBy,modifiedBy) VALUES(_customerId,_addressTypeId,_label,_addressLine1,_addressLine2 ,_pincode,_cityId,
			_city, _district, _state,_userId,_userId); SET @customerAddressId = @@IDENTITY;
       		END IF;
			IF _customerId > 0 THEN
			INSERT INTO customerloans(leadId,serviceId,loanAmount,customerId,employmentTypeId,statusId,createdBy,modifiedBy) VALUES (_leadId,_serviceId,_loanAmount,_customerId,_employmentTypeId,1,_userId,_userId);
			SET @customerLoanId =  @@IDENTITY;
            UPDATE leads SET leadStatusId = 5,modifiedDate = CURRENT_TIMESTAMP(),modifiedBy = _userId WHERE id = _leadId;
            INSERT INTO leadstatushistory(leadId,leadStatusId,transactionDate,createdBy,modifiedBy) VALUES (_leadId,5,CURRENT_TIMESTAMP(),_userId,_userId);
            INSERT INTO customerloanstatushistory(customerLoanId,loanStatusId,transactionDate,createdBy,modifiedBy) VALUES (@customerLoanId,1,CURRENT_TIMESTAMP(),_userId,_userId);
            INSERT INTO partnerscustomerloans (customerLoanId,partnerId,createdBy,modifiedBy) VALUES (@customerLoanId,_assignPartnerId,_userId,_userId);
            END IF;
            
            UPDATE serviceuserfield SET userId = @userId WHERE leadId = _leadId;
END;

DELIMITER ;

DROP procedure IF EXISTS `adminGetLeads`;

DELIMITER ;;

CREATE PROCEDURE `adminGetLeads`(IN _startIndex INT,IN _fetchRecords INT,IN _leadId INT,IN _serviceId LONGTEXT,IN _partnerId LONGTEXT)
BEGIN
	DECLARE SqlQuery LONGTEXT DEFAULT "";
    DECLARE SqlQueryCount LONGTEXT DEFAULT "";
    DECLARE FilterQuery LONGTEXT DEFAULT "";
    DECLARE serviceFilterQuery LONGTEXT DEFAULT "";
    SET @FilterQuery = "";
    SET @serviceFilterQuery =""; 
    IF (_serviceId != '') THEN
		SET @serviceFilterQuery = CONCAT_WS(''," AND leads.serviceId IN (",_serviceId,")");
    END IF;
     IF (_partnerId != '') THEN
		SET @serviceFilterQuery = CONCAT_WS('',@serviceFilterQuery," AND leads.assignToPartnerId IN (",_partnerId,")");
    END IF;
   	IF(_leadId > 0) THEN
		SET @FilterQuery = CONCAT_WS(''," AND leads.id = ",_leadId);
	END IF;
       	SET @SqlQueryCount = "";
        SET @SqlQueryCount = CONCAT_WS('',"SELECT COUNT(leads.id) as totalCount FROM leads LEFT JOIN services ON leads.serviceId = services.id 
		LEFT JOIN employmenttypes ON employmenttypes.id = leads.employmentTypeId 
		LEFT JOIN professiontypes ON professiontypes.id = leads.professionTypeId 
		LEFT JOIN banks ON banks.id = leads.primaryBankId
		LEFT JOIN cities ON leads.cityId = cities.id
         LEFT JOIN users ON users.id = leads.createdBy
        LEFT JOIN customers ON customers.contactNo = leads.contactNo
		LEFT JOIN leadaddresses ON leadaddresses.leadId = leads.id
		LEFT JOIN partners ON partners.id = leads.assignToPartnerId WHERE leads.isDelete = 0 ",@FilterQuery,@serviceFilterQuery);
      
       PREPARE countStmt FROM @SqlQueryCount;
		EXECUTE countStmt;
		DEALLOCATE PREPARE countStmt;
		SET @StartIndexQuery = '';
		IF _startIndex >= 0 AND _fetchRecords>0 THEN
		SET @StartIndexQuery = CONCAT_WS('', " LIMIT ",_fetchRecords, " OFFSET ", _startIndex);
		END IF;
        
		SET @SqlQuery = CONCAT_WS('',"SELECT leads.*,employmenttypes.name,professiontypes.name as professionType,banks.name as bankName,services.name as serviceName,cities.name as cityName,partners.fullName as partnerName,partners.contactNo as partnerContactNo,users.fullName as createdByUser,users.contactNo as createdByContactNo,customers.id as customerId,leadaddresses.id as customerAddressId,leadaddresses.label,leadaddresses.addressLine1,leadaddresses.addressLine2,leadaddresses.pincode,leadaddresses.cityId,leadaddresses.district,leadaddresses.state FROM leads 
        LEFT JOIN services ON leads.serviceId = services.id 
		LEFT JOIN employmenttypes ON employmenttypes.id = leads.employmentTypeId 
		LEFT JOIN professiontypes ON professiontypes.id = leads.professionTypeId 
		LEFT JOIN banks ON banks.id = leads.primaryBankId
        LEFT JOIN cities ON leads.cityId = cities.id
        LEFT JOIN users ON users.id = leads.createdBy
        LEFT JOIN customers ON customers.contactNo = leads.contactNo
		LEFT JOIN leadaddresses ON leadaddresses.leadId = leads.id
		LEFT JOIN partners ON partners.id = leads.assignToPartnerId WHERE leads.isDelete = 0 ",@FilterQuery,@serviceFilterQuery," ORDER BY leads.id DESC",@StartIndexQuery);

		PREPARE stmt FROM @SqlQuery;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
        
	IF _leadId >0 THEN
		SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
		SELECT * FROM serviceuserfield WHERE leadId = _leadId;
	END IF;
END;

DELIMITER ;

DROP procedure IF EXISTS `dsaBazarInsertPartner`;

DELIMITER ;;

CREATE PROCEDURE `dsaBazarInsertPartner`(IN _roleId INT, IN _temporaryCode VARCHAR(45), IN _fullName VARCHAR(200), IN _gender VARCHAR(20), IN _email VARCHAR(256), IN _countryCode VARCHAR(6), IN _contactNo VARCHAR(45)
, IN _aadhaarCardNo VARCHAR(20), IN _panCardNo VARCHAR(20), IN _cityId INT, IN _companyName VARCHAR(100), IN _professionTypeId INT, IN _workExperience DECIMAL(5,2), IN _haveOffice BOOLEAN
, IN _businessName VARCHAR(100) , IN _businessAddress LONGTEXT,  IN _addressLine1 LONGTEXT, IN _addressLine2 LONGTEXT, IN _pincode VARCHAR(10), IN _workAddressCityId INT,IN _jobType VARCHAR(45), IN _gstNo VARCHAR(20), IN _commitment DECIMAL(13,3), IN _referralCode VARCHAR(45), IN _parentPartnerId INT, IN _designationId INT,IN _userId INT)
BEGIN
	DECLARE finished INT DEFAULT 0;
    DECLARE cUserId INT DEFAULT 0; 
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
	END;
	START TRANSACTION;
    BEGIN
		DECLARE cur_user CURSOR FOR SELECT users.id FROM users WHERE users.contactNo = _contactNo;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
        OPEN  cur_user;
			cur_user_loop: LOOP
				FETCH cur_user INTO cUserId;
                IF finished = 1 THEN
					LEAVE cur_user_loop;
				END IF;
            END LOOP cur_user_loop;
        CLOSE cur_user;
        IF cUserId>0 THEN
			SELECT 'Contact No Already Exist' as message;
        ELSE
			INSERT INTO users(fullName, gender, email, countryCode, contactNo, isDisabled,createdBy,modifiedBy,currentRoleId) VALUES(_fullName, _gender, _email, _countryCode, _contactNo, true,_userId,_userId,_roleId);
			SET @userId = @@IDENTITY;
			INSERT INTO userroles(userId, roleId) VALUES(@userId, _roleId);
			INSERT INTO 
			partners(userId, temporaryCode, fullName, gender, contactNo, aadhaarCardNo, panCardNo, cityId, companyName, professionTypeId, workExperience, haveOffice, businessName, businessAddress, addressLine1, addressLine2, pincode, workAddressCityId, jobType,  gstNo, commitment, referralCode,currentBadgeId,parentPartnerId, designationId,createdBy,modifiedBy) 
			VALUES(@userId, _temporaryCode, _fullName, _gender, _contactNo, _aadhaarCardNo, _panCardNo, _cityId, _companyName, _professionTypeId, _workExperience, _haveOffice, _businessName, _businessAddress, _addressLine1, _addressLine2, _pincode, _workAddressCityId , _jobType, _gstNo, _commitment, _referralCode,1, _parentPartnerId, _designationId,_userId,_userId);
			SET @insertId = @@IDENTITY;
            SELECT @insertId as insertId,@userId as userId;
            INSERT INTO partnerbadges (partnerId,badgeId,createdBy,modifiedBy) VALUES (@insertId,1,_userId,_userId);
            SELECT 1 as currentBadgeId;
           
            SELECT roles.id, roles.name From userroles INNER JOIN roles ON roles.id = userroles.roleId WHERE userroles.userId = @userId;
            SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, partners.* FROM users 
            INNER JOIN userroles ON userroles.userId = users.id INNER JOIN partners ON partners.userId = users.id 
            WHERE users.id = @userId;
			SELECT partnerdocuments.url as profilePicUrl FROM partnerdocuments 
            WHERE partnerId = @insertId;
            IF _parentPartnerId>0 THEN
				SELECT partners.* FROM partners WHERE id = _parentPartnerId;             
            END IF;
           
        END IF;
    END;
    COMMIT;
END;

DELIMITER ;

DROP procedure IF EXISTS `adminGetCustomerCreditCardById`;

DELIMITER ;;

CREATE PROCEDURE `adminGetCustomerCreditCardById`(IN _customerId INT,IN _customerCreditCardId INT)
BEGIN
	SELECT customercreditcards.*,banks.name as bankName,creditcardstatuses.status FROM customercreditcards 
    LEFT JOIN banks ON customercreditcards.otherCreditCardBankId = banks.id
    LEFT JOIN creditcardstatuses ON creditcardstatuses.id = customercreditcards.statusId
    WHERE customercreditcards.id = _customerCreditCardId; 
	
	 SELECT customercreditcardemploymentdetail.*,employmenttypes.name as employmentType,educationtypes.name as educationType,banks.name as bankName 
            FROM customercreditcardemploymentdetail LEFT JOIN employmenttypes ON customercreditcardemploymentdetail.employmentTypeId  = employmenttypes.id
            LEFT JOIN educationtypes ON customercreditcardemploymentdetail.educationTypeId = educationtypes.id
            LEFT JOIN banks ON customercreditcardemploymentdetail.bankId = banks.id
            WHERE customercreditcardemploymentdetail.creditCardId = _customerCreditCardId;
	
	
	
	 SELECT  customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId,customeraddresses.city,customeraddresses.district,customeraddresses.state
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id
            WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND customeraddresses.addresstypeId = 3 AND userroles.roleId IN(2);
            
            SELECT  customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId,customeraddresses.city,customeraddresses.district,customeraddresses.state
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id
            WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND customeraddresses.addresstypeId =2 AND userroles.roleId IN(2);
	
           SELECT * FROM creditcardcompletescreenhistory WHERE customerCreditCardId = _customerCreditCardId;
            
           SELECT customercreditcardstatushistory.*, creditcardstatuses.status AS creditCardStatus, creditcardstatuses.isDataEditable AS isDataEditable 
            FROM customercreditcardstatushistory 
            INNER JOIN creditcardstatuses ON creditcardstatuses.id = customercreditcardstatushistory.creditCardStatusId 
            WHERE customerCreditCardId IN(_customerCreditCardId);
            
            SELECT customercreditcardoffer.*, banks.id as bankId,banks.name as bankName,bankcreditcard.creditCardName,bankcreditcard.joiningfee,bankcreditcard.renualfee,bankcreditcard.creditCardUrl,bankcreditcard.keyFeatures,bankcreditcard.benifitDescription FROM customercreditcardoffer
            INNER JOIN bankcreditcard ON bankcreditcard.id =  customercreditcardoffer.bankCreditCardId
			INNER JOIN banks ON banks.id = bankcreditcard.bankId WHERE customerCreditCardId IN(_customerCreditCardId);
            
		SELECT users.id as userId, users.email,customers.birthDate, customers.fullName,customers.contactNo,customers.gender,customers.maritalStatusId,maritalstatuses.status as maritalStatus,customers.panCardNo
	, customeraddresses.id as customerAddressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId,customeraddresses.city,customeraddresses.district,customeraddresses.state
	FROM users 
	INNER JOIN userroles ON userroles.userId = users.id 
	INNER JOIN customers ON customers.userId = users.id 
	LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id 
    LEFT JOIN maritalstatuses ON maritalstatuses.id = customers.maritalStatusId
	WHERE customers.id = _customerId AND users.isDelete = 0   AND customeraddresses.addressTypeId = 1 AND userroles.roleId IN(2);
    
    SELECT id,reason FROM customercreditcardrejectionreason WHERE isDelete = 0 AND customerCreditCardId = _customerCreditCardId;
    
    SELECT reason,description,id FROM reasons WHERE customerCreditCardId = _customerCreditCardId;
    
     SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
	SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM customers WHERE customers.id = _customerId);
END;

DELIMITER ;

DROP procedure IF EXISTS `getPartnerCustomers`;

DELIMITER ;;

CREATE PROCEDURE `getPartnerCustomers`(IN _partnerId INT, IN _startIndex INt, IN _fetchRecords INT)
BEGIN
	SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.*
	, customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId
    FROM users 
    INNER JOIN userroles ON userroles.userId = users.id 
    INNER JOIN customers ON customers.userId = users.id 
    LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id 
    WHERE customeraddresses.addressTypeId = 1 AND users.isDelete = 0 AND users.isActive = 1 AND userroles.roleId IN(2)
    AND customers.id IN(select customerId from partnercustomers WHERE partnerId = _partnerId) 
    LIMIT _fetchRecords OFFSET _startIndex;
    
   SELECT * FROM documentmasters WHERE allowDocumentField = 1; 
END;

DELIMITER ;

DROP procedure IF EXISTS `getLeadGeneratedByUserId`;

DELIMITER ;;

CREATE  PROCEDURE `getLeadGeneratedByUserId`(IN _userIds LONGTEXT, IN _startIndex INt, IN _fetchRecords INT)
BEGIN
	SET @sql = CONCAT('SELECT leads.*,leads.id as leadId,services.name, leadaddresses.id as leadaddressesid, leadaddresses.addressTypeId as addressTypeId
					, leadaddresses.label as label, leadaddresses.addressLine1 as addressLine1, leadaddresses.addressLine2 as addressLine2, leadaddresses.pincode as pincode, leadaddresses.cityId as cityId
					, leadaddresses.district as district, leadaddresses.state as state, leadstatuses.name as leadstatus 
					FROM leads 
					INNER JOIN services ON services.id = leads.serviceId 
                    LEFT JOIN leadaddresses ON leadaddresses.leadId = leads.id
	                LEFT JOIN leadstatuses ON leadstatuses.id = leads.leadStatusId
					WHERE leads.createdBy IN  (', _userIds,') AND leads.leadStatusId = 1 AND leads.isDelete = 0 LIMIT ',_fetchRecords,' OFFSET ',_startIndex);
	
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
    
    SELECT * FROM documentmasters WHERE allowDocumentField = 1; 
    
END;

DELIMITER ;

DROP procedure IF EXISTS `getCustomerPersonalLoanByCustomerLoanId`;

DELIMITER ;;

CREATE PROCEDURE `getCustomerPersonalLoanByCustomerLoanId`(IN _customerId INT, IN _customerLoanId INT)
BEGIN
	SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.* 
	FROM users 
	INNER JOIN userroles ON userroles.userId = users.id 
	INNER JOIN customers ON customers.userId = users.id 
	WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND userroles.roleId IN(2); 
            
	SELECT customerloans.*, employmenttypes.name as employmentType,services.displayName as displayName,services.name as serviceName FROM customerloans 
	LEFT JOIN employmenttypes ON  employmenttypes.id = customerloans.employmentTypeId
	LEFT JOIN services ON  services.id = customerloans.serviceId
	WHERE customerloans.id = _customerLoanId;
    
    SELECT customerloanemploymentdetails.*, companytypes.name AS companyTypeName, companyaddresses.addressTypeId, companyaddresses.label, companyaddresses.addressLine1
	, companyaddresses.addressLine2, companyaddresses.pincode, companyaddresses.cityId, companyaddresses.city, companyaddresses.district, companyaddresses.state, addresstypes.name as addressType
	FROM customerloanemploymentdetails 
	LEFT JOIN companytypes ON  companytypes.id = customerloanemploymentdetails.companyTypeId
	LEFT JOIN companyaddresses ON  companyaddresses.id = customerloanemploymentdetails.companyAddressId
	LEFT JOIN addresstypes ON  addresstypes.id = companyaddresses.addressTypeId
	WHERE customerloanemploymentdetails.customerloanId = _customerLoanId;
    
    SELECT * FROM customerloanspouses WHERE customerloanId = _customerLoanId;
    
    SELECT customerloandocuments.*, documentmasters.name as documentName, servicetypedocuments.isPdf FROM customerloandocuments 
	LEFT JOIN servicetypedocuments ON servicetypedocuments.id = customerloandocuments.serviceTypeDocumentId
	LEFT JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId
	WHERE customerloandocuments.customerloanId = _customerLoanId;
    
    SELECT customerloanreferences.*,customerreferenceaddresses.* FROM customerloanreferences
    LEFT JOIN customerreferenceaddresses ON  customerreferenceaddresses.customerLoanReferenceId = customerloanreferences.id
    WHERE customerloanId = _customerLoanId;
    
    SELECT * FROM loancompletescreenhistory WHERE customerloanId = _customerLoanId;
     
    SELECT customerloanstatushistory.*, loanstatuses.status AS loanStatus, loanstatuses.isDataEditable AS isDataEditable 
	FROM customerloanstatushistory 
	INNER JOIN loanstatuses ON loanstatuses.id = customerloanstatushistory.loanStatusId 
	WHERE customerloanId = _customerLoanId;
    
    SELECT clf.*,banks.name as bankName FROM customerloandetail as clf INNER JOIN banks ON clf.bankId = banks.id WHERE clf.isActive = 1 AND clf.isDelete = 0 AND clf.customerLoanId = _customerLoanId AND clf.isShared = 1 AND clf.status IS NULL;
   
    SELECT  customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId,customeraddresses.city,customeraddresses.state,customeraddresses.district
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id
            WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND customeraddresses.addresstypeId = 5 AND userroles.roleId IN(2);
            
   SELECT customerloantransferpropertydetails.* FROM customerloantransferpropertydetails WHERE customerLoanId = _customerLoanId;
   
    SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
	SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM customers WHERE id = _customerId);
END;

DELIMITER ;

DROP procedure IF EXISTS `customerGetBusinessLoanByCustomerLoanId`;

DELIMITER ;;

CREATE PROCEDURE `customerGetBusinessLoanByCustomerLoanId`(IN _customerId INT, IN _customerLoanId INT)
BEGIN

	SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.* 
    , customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId
	FROM users  
	INNER JOIN userroles ON userroles.userId = users.id 
	INNER JOIN customers ON customers.userId = users.id 
	LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id 
	WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND userroles.roleId IN(2) AND customeraddresses.addressTypeId = 1;
    
    SELECT customerloans.*, employmenttypes.name as employmentType, services.displayName as displayName, services.name as serviceName FROM customerloans 
	LEFT JOIN employmenttypes ON  employmenttypes.id = customerloans.employmentTypeId
	LEFT JOIN services ON  services.id = customerloans.serviceId
	WHERE customerloans.id = _customerLoanId;
	
	SELECT customerloandocuments.*, documentmasters.name as documentName, servicetypedocuments.isPdf FROM customerloandocuments 
	LEFT JOIN servicetypedocuments ON servicetypedocuments.id = customerloandocuments.serviceTypeDocumentId
	LEFT JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId
	WHERE customerloandocuments.customerloanId = _customerLoanId;
	
	SELECT customerloanbusinessdetails.*, companytypes.name as companyType, industrytypes.name as industryTypes,businessexperience.name as businessExperience
	, businessnatures.name as businessNatures, businessannualprofits.name businessAnnualProfits, banks.name banks FROM customerloanbusinessdetails
	LEFT JOIN companytypes ON companytypes.id = customerloanbusinessdetails.companyTypeId
	LEFT JOIN industrytypes ON industrytypes.id = customerloanbusinessdetails.industryTypeId
	LEFT JOIN businessexperience ON businessexperience.id = customerloanbusinessdetails.businessExperienceId
	LEFT JOIN businessnatures ON businessnatures.id = customerloanbusinessdetails.businessNatureId
	LEFT JOIN businessannualprofits ON businessannualprofits.id = customerloanbusinessdetails.businessAnnualProfitId
	LEFT JOIN banks ON banks.id = customerloanbusinessdetails.primaryBankId
	WHERE customerloanbusinessdetails.customerloanId = _customerLoanId;
	
	SELECT customerloancurrentresidentdetails.*, residenttypes.name residentType FROM customerloancurrentresidentdetails
	LEFT JOIN residenttypes ON residenttypes.id = customerloancurrentresidentdetails.residentTypeId WHERE customerloancurrentresidentdetails.customerLoanId = _customerLoanId;

	SELECT * FROM loancompletescreenhistory WHERE customerloanId = _customerLoanId;
            
	SELECT customerloanstatushistory.*, loanstatuses.status AS loanStatus, loanstatuses.isDataEditable AS isDataEditable 
	FROM customerloanstatushistory 
	INNER JOIN loanstatuses ON loanstatuses.id = customerloanstatushistory.loanStatusId 
	WHERE customerloanId = _customerLoanId;
    
     SELECT offers.*,banks.name as bankName FROM offers 
	INNER JOIN banks ON offers.bankId = banks.id 
	WHERE offers.isShared = 1 AND offers.customerLoanId = _customerLoanId;
    
	SELECT customerloantransferpropertydetails.* FROM customerloantransferpropertydetails WHERE customerLoanId = _customerLoanId;
    
     SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
	SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM customers WHERE id = _customerId);
	
END;

DELIMITER ;

DROP procedure IF EXISTS `customerGetHomeLoanByCustomerLoanId`;

DELIMITER ;;

CREATE PROCEDURE `customerGetHomeLoanByCustomerLoanId`(IN _customerId INT, IN _customerLoanId INT)
BEGIN
    SELECT customerloanpropertydetails.*, propertytypes.name as propertyType FROM customerloanpropertydetails 
	INNER JOIN propertytypes ON propertytypes.id = customerloanpropertydetails.propertyTypeId 
	WHERE customerloanpropertydetails.customerloanId = _customerLoanId; 
    
	SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, customers.* 
	, customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId
	FROM users 
	INNER JOIN userroles ON userroles.userId = users.id 
	INNER JOIN customers ON customers.userId = users.id 
	LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id 
	WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND customeraddresses.addresstypeId = 1 AND userroles.roleId IN(2);
	
	SELECT customerloans.*, employmenttypes.name as employmentType,services.displayName as displayName FROM customerloans 
	LEFT JOIN employmenttypes ON  employmenttypes.id = customerloans.employmentTypeId
	LEFT JOIN services ON  services.id = customerloans.serviceId
	WHERE customerloans.id = _customerLoanId;
	
	SELECT customerloanemploymentdetails.*, companytypes.name AS companyTypeName, companyaddresses.addressTypeId, companyaddresses.label, companyaddresses.addressLine1
	, companyaddresses.addressLine2, companyaddresses.pincode, companyaddresses.cityId, companyaddresses.city, companyaddresses.district, companyaddresses.state, addresstypes.name as addressType
	FROM customerloanemploymentdetails 
	LEFT JOIN companytypes ON  companytypes.id = customerloanemploymentdetails.companyTypeId
	LEFT JOIN companyaddresses ON  companyaddresses.id = customerloanemploymentdetails.companyAddressId
	LEFT JOIN addresstypes ON  addresstypes.id = companyaddresses.addressTypeId
	WHERE customerloanemploymentdetails.customerloanId = _customerLoanId;
	
	
	
	SELECT customerloancoapplicants.*, maritalstatuses.status as maritalStatus, coapplicantrelations.name as coApplicantRelation FROM customerloancoapplicants 
	INNER JOIN maritalstatuses ON maritalstatuses.id = customerloancoapplicants.maritalStatusId
	INNER JOIN coapplicantrelations ON coapplicantrelations.id = customerloancoapplicants.coApplicantRelationId
	WHERE customerloancoapplicants.customerloanId = _customerLoanId;
	
	SELECT customerloancoapplicantemploymentdetails.*, companyaddresses.addressTypeId, companyaddresses.label, companyaddresses.addressLine1
	, companyaddresses.addressLine2, companyaddresses.pincode, companyaddresses.cityId, companyaddresses.city, companyaddresses.district, companyaddresses.state, addresstypes.name as addressType
	, designations.name as designation, employmentnatures.name employmentNature, employmentservicetypes.name as employmentServiceType
	FROM customerloancoapplicantemploymentdetails
	INNER JOIN employmenttypes ON employmenttypes.id = customerloancoapplicantemploymentdetails.employmentTypeId
	LEFT JOIN companyaddresses ON  companyaddresses.id = customerloancoapplicantemploymentdetails.companyAddressId
	LEFT JOIN addresstypes ON  addresstypes.id = companyaddresses.addressTypeId
	LEFT JOIN designations ON designations.id = customerloancoapplicantemploymentdetails.designationId
	LEFT JOIN employmentnatures ON employmentnatures.id = customerloancoapplicantemploymentdetails.employmentNatureId
	LEFT JOIN employmentservicetypes ON employmentservicetypes.id = customerloancoapplicantemploymentdetails.employmentServiceTypeId
	WHERE customerloancoapplicantemploymentdetails.customerloanId = _customerLoanId;
	
	SELECT customerloancurrentresidentdetails.*,residenttypes.name as residentType FROM customerloancurrentresidentdetails
	INNER JOIN residenttypes ON residenttypes.id = customerloancurrentresidentdetails.residentTypeId
	WHERE customerloancurrentresidentdetails.customerloanId = _customerLoanId;
    
    SELECT  customeraddresses.id as addressId, customeraddresses.addressTypeId, customeraddresses.label, customeraddresses.addressLine1, customeraddresses.addressLine2, customeraddresses.pincode, customeraddresses.cityId
            FROM users 
            INNER JOIN userroles ON userroles.userId = users.id 
            INNER JOIN customers ON customers.userId = users.id 
            LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id
            WHERE customers.id = _customerId AND users.isDelete = 0 AND users.isActive = 1 AND customeraddresses.addresstypeId = 3 AND userroles.roleId IN(2);
	
	SELECT customerloandocuments.* ,documentmasters.name as documentName, servicetypedocuments.isPdf FROM customerloandocuments 
	LEFT JOIN servicetypedocuments ON servicetypedocuments.id = customerloandocuments.serviceTypeDocumentId
	LEFT JOIN documentmasters ON documentmasters.id = servicetypedocuments.documentId
	WHERE customerloandocuments.customerloanId = _customerLoanId;
	
	SELECT * FROM loancompletescreenhistory WHERE customerloanId = _customerLoanId;
	
	SELECT customerloanstatushistory.*, loanstatuses.status AS loanStatus, loanstatuses.isDataEditable AS isDataEditable 
	FROM customerloanstatushistory 
	INNER JOIN loanstatuses ON loanstatuses.id = customerloanstatushistory.loanStatusId 
	WHERE customerloanId IN(_customerLoanId);
    
	SELECT offers.*,banks.name as bankName FROM offers 
	INNER JOIN banks ON offers.bankId = banks.id 
	WHERE offers.isShared = 1 AND offers.customerLoanId = _customerLoanId;
    
    SELECT customerloantransferpropertydetails.* FROM customerloantransferpropertydetails
	WHERE customerloantransferpropertydetails.customerloanId = _customerLoanId;
    
    SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
	SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM customers WHERE id = _customerId);
END;

DELIMITER ;

DROP procedure IF EXISTS `dsaBazarGetPartnerById`;

DELIMITER ;;

CREATE PROCEDURE `dsaBazarGetPartnerById`(IN _partnerId INT)
BEGIN
	 SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, roles.name as roleName, partners.*
    , partneraddress.id as partnerAddressId, partneraddress.addressTypeId as addressTypeId, partneraddress.label as label, partneraddress.addressLine1 as addressLine1
	, partneraddress.addressLine2 as addressLine2, partneraddress.pincode as pincode, partneraddress.cityId as cityId, cities.name as city,districts.name as district,states.name as state, addresstypes.name as addressType, partnereducations.id as partnerEducationId
	, partnereducations.educationTypeId, partnereducations.id as partnerEducationId, educationtypes.name as educationType, partnereducations.resume as resume, partnereducations.passingYear as passingYear, partnereducations.otherDetail as otherDetail
    FROM users  
	INNER JOIN userroles ON userroles.userId = users.id 
    INNER JOIN roles ON roles.id = userroles.roleId
	INNER JOIN partners ON partners.userId = users.id
	LEFT JOIN partneraddress ON partneraddress.partnerId = partners.id 
    LEFT JOIN cities ON cities.id = partneraddress.cityId
    LEFT JOIN districts ON cities.districtId = districts.id
    LEFT JOIN states ON states.id = districts.stateId
	LEFT JOIN addresstypes ON addresstypes.id = partneraddress.addressTypeId
	LEFT JOIN partnereducations ON partnereducations.partnerId = partners.id 
	LEFT JOIN educationtypes ON educationtypes.id = partnereducations.educationTypeId
    WHERE partners.id = _partnerId;

	SELECT partnereducations.*, educationtypes.name FROM partnereducations 
	INNER JOIN educationtypes ON educationtypes.id = partnereducations.educationTypeId WHERE partnereducations.partnerId = _partnerId;

	SELECT partnerdocuments.*, documentmasters.name from partnerdocuments INNER JOIN documentmasters ON documentmasters.id = partnerdocuments.documentId WHERE partnerdocuments.partnerId = _partnerId;

	SELECT bankloanpartnercommissions.*, banks.name as bankName, commissiontypes.name as commissionType, services.name as serviceName FROM bankloanpartnercommissions 
	INNER JOIN banks ON banks.id = bankloanpartnercommissions.bankId
	INNER JOIN commissiontypes ON commissiontypes.id = bankloanpartnercommissions.commissionTypeId
	INNER JOIN services ON services.id = bankloanpartnercommissions.serviceId WHERE bankloanpartnercommissions.partnerId = _partnerId;
    
	SELECT customerloans.*, services.name as serviceName, customers.fullName, users.profilePicUrl FROM partnerscustomerloans 
    INNER JOIN customerloans ON customerloans.id =  partnerscustomerloans.customerLoanId  AND customerloans.isDelete = 0
    INNER JOIN customers ON customers.id = customerloans.customerId
    INNER JOIN users ON users.id = customers.userId
	INNER JOIN services ON services.Id = customerloans.serviceId
    /*INNER JOIN customerloanstatushistory on customerloanstatushistory.customerLoanId = customerloans.id*/
    WHERE partnerscustomerloans.partnerId = _partnerId
    ORDER BY customerloans.id DESC;
    
	SELECT customers.*, users.profilePicUrl FROM partnerscustomerloans 
    INNER JOIN customerloans ON customerloans.id =  partnerscustomerloans.customerLoanId
    INNER JOIN customers ON customers.id = customerloans.customerId
    INNER JOIN users ON users.id = customers.userId
	INNER JOIN services ON services.Id = customerloans.serviceId
    /*INNER JOIN customerloanstatushistory on customerloanstatushistory.customerLoanId = customerloans.id*/
    WHERE partnerscustomerloans.partnerId = _partnerId GROUP BY customers.id
    ORDER BY customers.id DESC;
    
    select assignuserstraining.*, trainings.title as title, trainings.url as url, trainings.fileName as fileName, trainings.documentType as documentType,trainings.trainingCategoryId,trainings.trainingSubCategoryId,triningcategories.name as categoryName
	from assignuserstraining INNER JOIN trainings ON trainings.id = assignuserstraining.trainingId INNER JOIN triningcategories ON triningcategories.id = trainings.trainingCategoryId WHERE assignuserstraining.partnerId = _partnerId ORDER BY assignuserstraining.id DESC;
    
    SELECT partneraddress.*, addresstypes.name as addressType  from partneraddress INNER JOIN addresstypes ON addresstypes.id = partneraddress.addressTypeId 
    WHERE partneraddress.partnerId = _partnerId;
    
    SELECT visitingCardId,location FROM partnervisitingcards WHERE partnerId = _partnerId;
    
    SELECT partners.id,partners.fullName,partners.contactNo,partners.gender,partners.panCardNo,partners.cityId,partners.temporaryCode,partners.permanentCode,partners.companyName,partners.companyTypeId,partners.businessName,partners.isDelete,partners.createdDate,partners.isActive,cities.name as city,companytypes.name as companyType FROM partners 
    INNER JOIN users ON partners.userId = users.id 
    INNER JOIN userroles ON userroles.userId = partners.userId 
    LEFT JOIN companytypes ON companytypes.id = partners.companyTypeId 
    LEFT JOIN cities ON cities.id = partners.cityId 
    WHERE userroles.roleId = 5 AND partners.createdBy = (SELECT userId FROM partners WHERE id = _partnerId); 
    
    SELECT partners.id,partners.fullName,partners.contactNo,partners.gender,partners.panCardNo,partners.cityId,partners.temporaryCode,partners.permanentCode,partners.companyName,partners.companyTypeId,partners.businessName,partners.isDelete,partners.createdDate,partners.isActive,cities.name as city,companytypes.name as companyType,roles.name as roleName FROM partners 
    INNER JOIN userroles ON partners.userId = userroles.userId 
    INNER JOIN roles ON userroles.roleId = roles.id 
	LEFT JOIN companytypes ON companytypes.id = partners.companyTypeId 
    LEFT JOIN cities ON cities.id = partners.cityId 
    WHERE partners.parentPartnerId = _partnerId AND userroles.roleId NOT IN (5);
    
    SELECT userwallet.coin as totalCoin,userwallethistory.*,rewardtypes.name FROM userwallethistory
    LEFT JOIN rewardtypes ON userwallethistory.rewardType = rewardtypes.id
    LEFT JOIN users ON users.id = userwallethistory.userId
    LEFT JOIN partners ON partners.userId = users.id
    LEFT JOIN userwallet ON userwallethistory.userWalletId = userwallet.id
	WHERE partners.id = _partnerId;  
    
	SELECT partnercommissionhistory.*,partnerinvoice.url,customers.fullName as customerName,services.name as serviceName,customerloans.createdDate as applyDate FROM partnercommissionhistory LEFT JOIN partnerinvoice ON partnerinvoice.partnerCommissionHistoryId = partnercommissionhistory.id LEFT JOIN customerloandetail ON customerloandetail.id = partnercommissionhistory.loanDetailId LEFT JOIN customerloans ON customerloans.id = customerloandetail.customerLoanId LEFT JOIN customers ON customers.id = customerloans.customerId LEFT JOIN services ON services.id = customerloans.serviceId WHERE partnercommissionhistory.partnerId = _partnerId AND partnercommissionhistory.type = 'IN';

	SELECT * FROM documentmasters WHERE allowDocumentField = 1;
            
	SELECT * FROM serviceuserfield WHERE userId = (SELECT userId FROM partners WHERE id = _partnerId);
END;

DELIMITER ;

DROP procedure IF EXISTS `dsaBazarGetPartnerByRoleIdandPartnerId`;

DELIMITER ;;

CREATE PROCEDURE `dsaBazarGetPartnerByRoleIdandPartnerId`(IN _partnerId INT,IN _roleIds LONGTEXT,IN _startIndex INT,IN _fetchrecords INT)
BEGIN
        SET @sql = CONCAT_WS('',"SELECT users.id as userId, users.email, users.countryCode, users.password, users.profilePicUrl, users.isDisabled, userroles.roleId as roleId, roles.name as roleName, partners.* 
        , partneraddress.id as partnerAddressId, partneraddress.addressTypeId as addressTypeId, partneraddress.label as label, partneraddress.addressLine1 as addressLine1
        , partneraddress.addressLine2 as addressLine2, partneraddress.pincode as pincode, partneraddress.cityId as cityId, cities.name as city, addresstypes.name as addressType
    , partnereducations.id as partnerEducationId, partnereducations.educationTypeId, partnereducations.id as partnerEducationId, educationtypes.name as educationType
    , partnereducations.resume as resume, partnereducations.instituteName as instituteName, partnereducations.passingYear as passingYear, partnereducations.otherDetail as otherDetail FROM users 
        INNER JOIN userroles ON userroles.userId = users.id 
        INNER JOIN roles ON roles.id = userroles.roleId
        INNER JOIN partners ON partners.userId = users.id
        LEFT JOIN partneraddress ON partneraddress.partnerId = partners.id 
    LEFT JOIN cities ON cities.id = partneraddress.cityId
        LEFT JOIN addresstypes ON addresstypes.id = partneraddress.addressTypeId
        LEFT JOIN partnereducations ON partnereducations.partnerId = partners.id 
        LEFT JOIN educationtypes ON educationtypes.id = partnereducations.educationTypeId
        WHERE partners.parentPartnerId =", _partnerId, " AND partners.isDelete = 0 AND  userroles.roleId IN(",_roleIds,")");
    IF _startIndex >= 0 AND _fetchRecords >0 THEN
    SET @sql = CONCAT_WS('',@sql," LIMIT ", _fetchRecords," OFFSET ", _startIndex); 
    END IF;
    PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
    SELECT * FROM documentmasters WHERE allowDocumentField = 1;
END;

DELIMITER ;

DROP procedure IF EXISTS `getLeadAssignByPartnerId`;

DELIMITER ;;

CREATE PROCEDURE `getLeadAssignByPartnerId`(IN _partnerId VARCHAR(200), IN _startIndex INt, IN _fetchRecords INT)
BEGIN
	SET @sql = CONCAT('SELECT leads.*,leads.id as leadId,services.name, customers.id as customerId, customers.temporaryCode as temporaryCode, customers.permanentCode as permanentCode, customers.fullName as fullName, customers.birthdate as birthdate
					, customers.gender as gender, customers.alternativeContactNo as alternativeContactNo, customers.maritalStatusId as maritalStatusId,customeraddresses.id as customerAddressId, customeraddresses.addressTypeId as addressTypeId
					, customeraddresses.label as label, customeraddresses.addressLine1 as addressLine1, customeraddresses.addressLine2 as addressLinez2, customeraddresses.pincode as customerAddressPincode, customeraddresses.cityId as customerAddressCityId
					, customeraddresses.city as city, customeraddresses.district as district, customeraddresses.state as CustomerAddressStateId
					FROM leads 
					INNER JOIN services ON services.id = leads.serviceId 
					LEFT JOIN customers ON customers.contactNo = leads.contactNo
					LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id
					WHERE leads.assignToPartnerId  IN (', _partnerId,') AND leads.isDelete = 0 AND (customeraddresses.addressTypeId is null OR customeraddresses.addressTypeId = 1)   LIMIT ',_fetchRecords,' OFFSET ',_startIndex);
	
	PREPARE stmt FROM @sql; 
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
    	SELECT * FROM documentmasters WHERE allowDocumentField = 1;
END;

DELIMITER ;

DROP procedure IF EXISTS `getLeadAssignByPartnerToNetwork`;

DELIMITER ;;

CREATE PROCEDURE `getLeadAssignByPartnerToNetwork`(IN _userId VARCHAR(200), IN _startIndex INt, IN _fetchRecords INT)
BEGIN
	SET @sql = CONCAT('SELECT leads.*,leads.id as leadId,services.name, customers.id as customerId, customers.temporaryCode as temporaryCode, customers.permanentCode as permanentCode, customers.fullName as fullName, customers.birthdate as birthdate
					, customers.gender as gender, customers.alternativeContactNo as alternativeContactNo, customers.maritalStatusId as maritalStatusId,customeraddresses.id as customerAddressId, customeraddresses.addressTypeId as addressTypeId
					, customeraddresses.label as label, customeraddresses.addressLine1 as addressLine1, customeraddresses.addressLine2 as addressLine2, customeraddresses.pincode as pincode, customeraddresses.cityId as cityId
					, customeraddresses.city as city, customeraddresses.district as district, customeraddresses.state as state ,partners.fullName as partnername , roles.name as rolename 
					FROM leads 
					INNER JOIN services ON services.id = leads.serviceId 
					LEFT JOIN customers ON customers.contactNo = leads.contactNo
                    LEFT JOIN partners ON partners.id = leads.assignToPartnerId
					LEFT JOIN customeraddresses ON customeraddresses.customerId = customers.id
                    INNER JOIN userroles ON userroles.userId = partners.userId 
	                INNER JOIN roles ON roles.id = userroles.roleId
					WHERE  leads.assignedById  IN (', _userId,') LIMIT ',_fetchRecords,' OFFSET ',_startIndex);
	
	PREPARE stmt FROM @sql; 
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
    
    SELECT * FROM documentmasters WHERE allowDocumentField = 1;
END;

DELIMITER ;

DROP procedure IF EXISTS `GetOtherLoansByuserId`;

DELIMITER ;;

CREATE  PROCEDURE `GetOtherLoansByuserId`(IN _userId INT,IN _serviceTypeId INT,IN _startIndex INT,IN _fetchRecord INT)
BEGIN
	SELECT customerotherloans.*, employmenttypes.name as employementtypename FROM customerotherloans INNER JOIN employmenttypes ON employmenttypes.id = customerotherloans.employmentTypeId WHERE customerotherloans.userId =  _userId AND customerotherloans.serviceTypeId = _serviceTypeId LIMIT _fetchRecord OFFSET _startIndex;
SELECT * FROM documentmasters WHERE allowDocumentField = 1; 

END;

DELIMITER ;



















































