-- 2024-07-09
ALTER TABLE `resident_profile_settings` CHANGE `data_type` `data_type` ENUM('string','number','phone','email','date','dob','boolean','image','pdf','both','') NOT NULL DEFAULT 'string';


-- 2024-07-13
UPDATE `settings` SET rule_type='boolean' WHERE rule_type='boolen';

-- 2024-07-15

INSERT INTO `complain_status` (`complain_status_id`, `complain_status_name`, `is_active`) VALUES ('6', 'Work assigned', '1'), ('7', 'Work accepted', '1');
INSERT INTO `complain_activity_type` (`id`, `activity_name`) VALUES (29, 'Work accepted');
INSERT INTO `msg_transaction_type` (`transaction_type`, `Description`, `isActive`, `sms`, `email`) VALUES (62, 'complain work accepted', 1, 0, 0);
INSERT INTO `msg_templates` (`template_id`, `template`, `anonymous_template`, `description`, `email_header`, `template_hi`, `template_mr`, `template_ar`) VALUES (62, 'Dear %complainant_name$s, We wanted to inform you that your complaint no %complaint_no$s accepted by %member_name%.', NULL, 'Complain work accepted', 'Your complain #%complaint_no$s in FretBox has been accepted by %member_name%', 'Dear %complainant_name$s, We wanted to inform you that your complaint no %complaint_no$s accepted by %member_name%.', NULL, NULL)

-- 2024-07-24

UPDATE `msg_templates` SET `email_header` = 'Your complain #%complaint_no$s in FretBox has been accepted by %member_name$s', `template_hi` = 'Dear %complainant_name$s, We wanted to inform you that your complaint no %complaint_no$s accepted by %member_name$s.' WHERE `msg_templates`.`template_id` = 62;

DROP PROCEDURE IF EXISTS `getTeamMemberMobileAppRegistration`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getTeamMemberMobileAppRegistration`(IN `member_id` INT(8))
    NO SQL
SELECT app_reg_key from user_app_registration WHERE user_id in (SELECT id from user where user.relatedId=member_id and user.userRole=9) and isActive=1$$
DELIMITER ;


DROP PROCEDURE IF EXISTS `getAllComplaintStats`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllComplaintStats` (IN `in_user_id` INT, IN `input_role_id` INT(3), IN `related_id` INT(3))  NO SQL BEGIN 
if input_role_id<6 then 
SELECT count(complain_id) as total_complaints,
	SUM(CASE WHEN complaint_status=0  THEN 1 ELSE 0 END) AS 'pending_complaints',
	SUM(CASE WHEN complaint_status=1 THEN 1 ELSE 0 END) AS 'complete_complaints' ,
	SUM(CASE WHEN complaint_status=2 THEN 1 ELSE 0 END) AS 'reopen_complaints' ,
	SUM(CASE WHEN complaint_status=3 THEN 1 ELSE 0 END) AS 'closed_complaints',
    SUM(CASE WHEN complaint_status=5 THEN 1 ELSE 0 END) AS 'rejected_complaints',
    SUM(CASE WHEN complaint_status=6 THEN 1 ELSE 0 END) AS 'work_assigned_complaints',
    SUM(CASE WHEN complaint_status=7 THEN 1 ELSE 0 END) AS 'work_accepted_complaints',
	SUM(CASE WHEN complaint_status=4 THEN 1 ELSE 0 END) AS 'long_term_complaints'	FROM `complaints` ;
ELSEIF input_role_id<8 then 
SELECT count(complain_id) as total_complaints,
	SUM(CASE WHEN complaint_status=0  THEN 1 ELSE 0 END) AS 'pending_complaints',
	SUM(CASE WHEN complaint_status=1 THEN 1 ELSE 0 END) AS 'complete_complaints' ,
	SUM(CASE WHEN complaint_status=2 THEN 1 ELSE 0 END) AS 'reopen_complaints' ,
	SUM(CASE WHEN complaint_status=3 THEN 1 ELSE 0 END) AS 'closed_complaints',
    SUM(CASE WHEN complaint_status=5 THEN 1 ELSE 0 END) AS 'rejected_complaints',
    SUM(CASE WHEN complaint_status=6 THEN 1 ELSE 0 END) AS 'work_assigned_complaints',
    SUM(CASE WHEN complaint_status=7 THEN 1 ELSE 0 END) AS 'work_accepted_complaints',
	SUM(CASE WHEN complaint_status=4 THEN 1 ELSE 0 END) AS 'long_term_complaints'	FROM `complaints` 
    INNER join building_residence on building_residence.id=complaints.residence_id where building_residence.building_id in (SELECT building_users.building_id FROM building_users where building_users.user_id=in_user_id and building_users.isActive=1);
ELSEIF input_role_id=8 THEN
	SELECT count(complain_id) as total_complaints, 
	SUM(CASE WHEN complaint_status=0  THEN 1 ELSE 0 END) AS 'pending_complaints',
	SUM(CASE WHEN complaint_status=1 THEN 1 ELSE 0 END) AS 'complete_complaints' , 
	SUM(CASE WHEN complaint_status=2 THEN 1 ELSE 0 END) AS 'reopen_complaints' , 
	SUM(CASE WHEN complaint_status=3 THEN 1 ELSE 0 END) AS 'closed_complaints', 
	SUM(CASE WHEN complaint_status=5 THEN 1 ELSE 0 END) AS 'rejected_complaints', 
    SUM(CASE WHEN complaint_status=6 THEN 1 ELSE 0 END) AS 'work_assigned_complaints',
    SUM(CASE WHEN complaint_status=7 THEN 1 ELSE 0 END) AS 'work_accepted_complaints',
	SUM(CASE WHEN complaint_status=4 THEN 1 ELSE 0 END) AS 'long_term_complaints' 
	FROM `complaints` 
    INNER join building_residence on building_residence.id=complaints.residence_id where building_residence.building_id in (SELECT building_users.building_id FROM building_users where building_users.user_id=in_user_id and building_users.isActive=1) and CASE WHEN related_id>0 THEN department_id = related_id ELSE TRUE END   ;
ELSEIF input_role_id=9 THEN
    SELECT count(complain_id) as total_complaints,
	SUM(CASE WHEN complaint_status=0  THEN 1 ELSE 0 END) AS 'pending_complaints',
	SUM(CASE WHEN complaint_status=1 THEN 1 ELSE 0 END) AS 'complete_complaints' ,
	SUM(CASE WHEN complaint_status=2 THEN 1 ELSE 0 END) AS 'reopen_complaints' ,
	SUM(CASE WHEN complaint_status=3 THEN 1 ELSE 0 END) AS 'closed_complaints',
    SUM(CASE WHEN complaint_status=5 THEN 1 ELSE 0 END) AS 'rejected_complaints',
    SUM(CASE WHEN complaint_status=6 THEN 1 ELSE 0 END) AS 'work_assigned_complaints',
    SUM(CASE WHEN complaint_status=7 THEN 1 ELSE 0 END) AS 'work_accepted_complaints',
	SUM(CASE WHEN complaint_status=4 THEN 1 ELSE 0 END) AS 'long_term_complaints'
	FROM `complaints`
    INNER join building_residence on building_residence.id=complaints.residence_id 
    where building_residence.building_id in (SELECT building_users.building_id FROM building_users where building_users.user_id=in_user_id and building_users.isActive=1) 
    and CASE WHEN related_id>0 THEN team_member_id = related_id ELSE TRUE END  ;
  
END IF;

END$$


DROP PROCEDURE IF EXISTS `GetComplainsByRole`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainsByRole`(IN `in_user_id` INT, IN `input_role_id` INT(3), IN `in_status` INT, IN `daterangearray` VARCHAR(500), IN `in_status_group` LONGTEXT)   BEGIN
SET @sql = "SELECT complaint_view.* from complaint_view WHERE 1 =1 ";

IF ( in_status is not null) THEN
	SET @sql = CONCAT(@sql,' AND complaint_view.complain_status_id =',in_status);
END IF;

IF (in_status_group IS NOT NULL AND in_status_group != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.complain_status_id IN (', in_status_group, ')');
END IF;

if (input_role_id>5) then 
	SET @sql = CONCAT(@sql,' AND building_id in (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1)');
	if(input_role_id=8) THEN
		set @department_id=(select relatedId from user where user.id=in_user_id);
        if(@department_id>0) then 
			SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',@department_id);
        END IF;
	END IF;
	if(input_role_id=9) THEN
		set @team_member_id=(select relatedId from user where user.id=in_user_id);
		SET @sql = CONCAT(@sql,' AND complaint_view.team_member_id =',@team_member_id);
	END IF;
END IF;



IF ( daterangearray != '') THEN
	SET @sql = CONCAT(@sql,' AND complaint_date NOT BETWEEN ',daterangearray );
END IF;
SET @sql = CONCAT(@sql,' order by  complaint_view.complain_id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$

-- 2024-07-25

DROP PROCEDURE IF EXISTS `getStudentList`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getStudentList`()
SELECT
    `complainants`.`id` AS `id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`file` AS `file`,
    `complainants`.`ext` AS `ext`,
    `complainants`.`gender` AS `gender`,
    `complainants`.`divyaang` AS `divyaang`,
    `complainants`.`created_at` AS `created_at`,
    `complainants`.`is_pending_dues` AS `is_pending_dues`,
    `resident_academic_details`.`reference_number` AS `reference_number`,
    `resident_academic_details`.`registration_no` AS `registration_no`,
    `resident_academic_details`.`course_year` AS `course_year`,
    `resident_academic_details`.`branch` AS `branch_id`,
    `resident_academic_details`.`cgpa` AS `cgpa`,
    `resident_academic_details`.`course` AS `course_id`,
    `academic_courses`.`course_name` AS `course_name`,
    `academic_course_branches`.`branch_name` AS `branch_name`,
    `resident_personal_details`.`personal_data` AS `personal_data`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.dob'
        )
    ) AS `dob`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].address'
        )
    ) AS `parent_address`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].name'
        )
    ) AS `parent_name`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].phone_number'
        )
    ) AS `parent_phone_number`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].email'
        )
    ) AS `parent_email`
FROM
    (
        (
            (
                (
                    (
                        `complainants`
                        left join `resident_academic_details` on(
                            `complainants`.`id` = `resident_academic_details`.`resident_id`
                        )
                    )
                    left join `academic_courses` on(
                        `academic_courses`.`id` = `resident_academic_details`.`course`
                    )
                )
                left join `academic_course_branches` on(
                    `academic_course_branches`.`branch_id` = `resident_academic_details`.`branch`
                )
            )
            left join `resident_residences` on(
                `complainants`.`id` = `resident_residences`.`resident_id`
            )
        )
        left join `resident_personal_details` on(
            `complainants`.`id` = `resident_personal_details`.`resident_id`
        )
    )
WHERE
    `resident_residences`.`resident_id` is null
    AND `complainants`.`is_activated` = 'A'
ORDER BY
    `complainants`.`id` DESC$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `getActiveResident`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getActiveResident`(IN `in_building_ids` VARCHAR(255))
BEGIN
SET @sql = "SELECT
    `complainants`.`id` AS `id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`file` AS `file`,
    `complainants`.`ext` AS `ext`,
    `complainants`.`gender` AS `gender`,
    `complainants`.`divyaang` AS `divyaang`,
    `complainants`.`created_at` AS `created_at`,
    `complainants`.`is_pending_dues` AS `is_pending_dues`,
    `resident_academic_details`.`reference_number` AS `reference_number`,
    `resident_academic_details`.`registration_no` AS `registration_no`,
    `resident_academic_details`.`course_year` AS `course_year`,
    `resident_academic_details`.`branch` AS `branch_id`,
    `resident_academic_details`.`cgpa` AS `cgpa`,
    `resident_academic_details`.`course` AS `course_id`,
    `academic_courses`.`course_name` AS `course_name`,
    `academic_course_branches`.`branch_name` AS `branch_name`,
    `resident_personal_details`.`personal_data` AS `personal_data`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.dob'
        )
    ) AS `dob`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].address'
        )
    ) AS `parent_address`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].name'
        )
    ) AS `parent_name`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].phone_number'
        )
    ) AS `parent_phone_number`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].email'
        )
    ) AS `parent_email`,
    json_extract(
        `resident_personal_details`.`personal_data`,
        '$.parents'
    ) AS `parents`,
    json_extract(
        `resident_personal_details`.`personal_data`,
        '$.blood_group'
    ) AS `blood_group`,
    json_extract(
        `resident_personal_details`.`personal_data`,
        '$.local_guardians'
    ) AS `local_guardians`,
    `complainants`.`created_by` AS `created_by`,
    `resident_residences`.`resident_status` AS `resident_status`,
    `resident_residences`.`living_start_at` AS `living_start_at`,
    `resident_residences`.`booked_up_to` AS `living_end_at`,
    `resident_residences`.`tentative_living_up_to` AS `tentative_living_up_to`,
    `resident_residences`.`residence_id` AS `residence_id`,
    `complainants`.`approved_at` AS `approved_at`,
    `complainants`.`approved_by` AS `approved_by`,
    `residence_status`.`residence_status_text` AS `residence_status_text`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `building_residence`.`unit_type` AS `unit_type`,
    `building_residence`.`floor` AS `floor`,
    `building_unit_types`.`Description` AS `unit_name`,
    `building`.`name` AS `building_name`,
    `building_residence`.`building_id` AS `building_id`,
    ifnull(`pp`.`complete_in_per`, 0) AS `profile_complete`
FROM
    (
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            `complainants`
                                            join `resident_residences` on(
                                                `complainants`.`id` = `resident_residences`.`resident_id`
                                            )
                                        )
                                        left join `resident_academic_details` on(
                                            `complainants`.`id` = `resident_academic_details`.`resident_id`
                                        )
                                    )
                                    left join `academic_courses` on(
                                        `academic_courses`.`id` = `resident_academic_details`.`course`
                                    )
                                )
                                left join `academic_course_branches` on(
                                    `academic_course_branches`.`branch_id` = `resident_academic_details`.`branch`
                                )
                            )
                            left join `building_residence` on(
                                `resident_residences`.`residence_id` = `building_residence`.`id`
                            )
                        )
                        left join `building_unit_types` on(
                            `building_residence`.`unit_type` = `building_unit_types`.`unit_type_id`
                        )
                    )
                    left join `building` on(
                        `building`.`id` = `building_residence`.`building_id`
                    )
                )
                left join `residence_status` on(
                    `residence_status`.`residence_status_id` = `resident_residences`.`resident_status`
                )
            )
            left join `resident_personal_details` on(
                `complainants`.`id` = `resident_personal_details`.`resident_id`
            )
        )
        left join (
            select
                `complainants`.`id` AS `resident_id`,
                round(
                    (
                        octet_length(
                            trim(
                                both ','
                                from
                                    concat(
                                        coalesce(`kyc`.`doc_filled`, ''),
                                        ',',
                                        coalesce(
                                            `resident_personal_details`.`resident_profile_setting_id`,
                                            ''
                                        )
                                    )
                            )
                        ) - octet_length(
                            replace(
                                trim(
                                    both ','
                                    from
                                        concat(
                                            coalesce(`kyc`.`doc_filled`, ''),
                                            ',',
                                            coalesce(
                                                `resident_personal_details`.`resident_profile_setting_id`,
                                                ''
                                            )
                                        )
                                ),
                                ',',
                                ''
                            )
                        ) + 1
                    ) / (
                        select
                            count(`resident_profile_settings`.`id`)
                        from
                            `resident_profile_settings`
                        where
                            `resident_profile_settings`.`isActive` = 1
                    ) * 100,
                    0
                ) AS `complete_in_per`
            from
                (
                    (
                        `complainants`
                        left join (
                            select
                                group_concat(
                                    distinct `resident_kyc`.`resident_profile_setting_id` separator ','
                                ) AS `doc_filled`,
                                `resident_kyc`.`resident_id` AS `resident_id`
                            from
                                `resident_kyc`
                            group by
                                `resident_kyc`.`resident_id`
                        ) `kyc` on(`kyc`.`resident_id` = `complainants`.`id`)
                    )
                    left join `resident_personal_details` on(
                        `resident_personal_details`.`resident_id` = `complainants`.`id`
                    )
                )
            where
                `resident_personal_details`.`resident_profile_setting_id` is not null
                or `kyc`.`doc_filled` is not null
        ) `pp` on(`complainants`.`id` = `pp`.`resident_id`)
    )
 WHERE
    `resident_residences`.`isActive` = 1
    AND `complainants`.`is_activated` = 'A' ";

IF (in_building_ids IS NOT NULL AND in_building_ids != '') THEN
    SET @sql = CONCAT(@sql, ' AND `building_residence`.`building_id` IN (', in_building_ids, ')');
END IF;

SET @sql = CONCAT(@sql,' ORDER BY `complainants`.`id` DESC');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 2024-07-26
DROP PROCEDURE IF EXISTS `GetUserByID`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetUserByID`(IN `userid` INT)
BEGIN
SELECT u.id,u.userID,u.email,u.name,u.phoneNumber,u.isAdminUser,u.userRole,u.relatedId,u.language,u.isActive,
IF(Alias IS NULL ,r.role_name,r.Alias) as role_name
FROM `user` u LEFT JOIN `role` r 
ON ( u.userRole =r.role_id) 
WHERE u.id=userid;

-- 2024-07-27
ALTER TABLE `information` ADD `image_container_key` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

DROP PROCEDURE IF EXISTS `GetResidentGuestPass`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetResidentGuestPass`(IN `in_resident_id` INT, IN `in_view` ENUM('ALL','UPCOMING'))
    NO SQL
BEGIN
DECLARE temp_residence_id int;

set @residence_id=(CASE WHEN (select residence_id from resident_residences where resident_residences.resident_id=in_resident_id and resident_residences.isActive=1) IS NULL THEN 0 ELSE (select residence_id from resident_residences where resident_residences.resident_id=in_resident_id and resident_residences.isActive=1) END);
SET @sql = CONCAT("select gate_pass.id,ifnull(expected_dt,'') as expected_dt ,\r\nifnull(expected_tm,'') as expected_tm ,\r\nifnull(entry_dt,'') as entry_dt,ifnull(entry_tm,'') as entry_tm,ifnull(exit_dt,'') as exit_dt,ifnull(exit_tm,'') as exit_tm,(case when JSON_CONTAINS_PATH(gate_pass.residence_actions,'one' ,'$.",@residence_id,"') then json_unquote(json_extract(json_unquote(json_extract(gate_pass.residence_actions,'$.",@residence_id,"')),'$.action')) else gate_pass.status end) as status,gate_pass.purpose,gate_pass.extra_pax,(CASE WHEN complainants.complainant_name is not null THEN complainants.complainant_name ELSE gate_pass.to_meet  END) AS to_meet,gate_pass.vehicle,gate_pass.address,gate_pass.isMasked,gate_pass.body_temprature,visitors.name,visitors.contact_no,visitors.id_no,visitors.gender,visitors.file,visitors.file_ext,complainants.complainant_name as resident_name,building_residence.apartment_name,building.name as building_name from gate_pass\r\nleft join complainants on complainants.id=gate_pass.resident_id\r\nleft join building_residence on building_residence.id=gate_pass.residence_id\r\nleft join building on building.id=building_residence.building_id\r\nLEFT JOIN visitors on visitors.id=gate_pass.visitor_id where 1=1 ");

IF ( in_view = 'UPCOMING' ) THEN
  SET @query = " and gate_pass.expected_dt>=CURRENT_DATE";
   SET @sql = CONCAT(@sql,@query);
END IF;
IF ( in_resident_id > 0 ) THEN
		
        SET @sql = CONCAT(@sql,' AND JSON_CONTAINS(gate_pass.residences ,',@residence_id,')');
END IF;
SET @sql = CONCAT(@sql,' order by gate_pass.id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;


-- 2024-07-29
ALTER TABLE `gate_pass` CHANGE `status` `status` ENUM('NEW','APPROVAL_PENDING','APPROVED','CANCELLED','EXIT','PARCEL','WRONG_ENTRY','REJECTED') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'NEW';

DROP PROCEDURE IF EXISTS `UpdateGatePass`
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateGatePass`(IN `pass_id` INT, IN `user_id` INT, IN `in_event` ENUM('expect','entry_resident','exit','cancel','reject','entry_security','parcel','wrong_entry'), IN `event_dt` DATE, IN `event_time` TIME)
BEGIN

IF ( in_event = 'expect' ) THEN
Update `gate_pass` SET expected_dt = event_dt, expected_tm = event_time, permit_user=user_id,updated_at=NOW() WHERE id = pass_id;

ELSEIF ( in_event = 'entry_security' ) THEN
Update `gate_pass` SET entry_dt = event_dt, entry_tm = event_time, permit_user=user_id, entry_approved_by='SECURITY',status='APPROVED', updated_at=NOW() WHERE id = pass_id;
ELSEIF ( in_event = 'entry_resident' ) THEN
Update `gate_pass` SET entry_dt = event_dt, entry_tm = event_time, entry_approved_by='RESIDENT',status='APPROVED', updated_at=NOW() WHERE id = pass_id;
ELSEIF ( in_event = 'exit' ) THEN
Update `gate_pass` SET exit_dt = event_dt, exit_tm = event_time, updated_at=NOW(),status='EXIT' WHERE id = pass_id;
ELSEIF ( in_event = 'cancel' ) THEN
Update `gate_pass` SET status = 'CANCELLED',isActive=0, updated_at=NOW() WHERE id = pass_id;
ELSEIF ( in_event = 'reject' ) THEN
Update `gate_pass` SET status = 'REJECTED',isActive=0, updated_at=NOW() WHERE id = pass_id;
ELSEIF ( in_event = 'wrong_entry' ) THEN
Update `gate_pass` SET status = 'WRONG_ENTRY',isActive=0, updated_at=NOW() WHERE id = pass_id;
ELSEIF ( in_event = 'parcel' ) THEN
Update `gate_pass` SET status = 'PARCEL', updated_at=NOW() WHERE id = pass_id;
END IF;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `getActiveResident`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getActiveResident`(IN `in_building_ids` VARCHAR(255))
BEGIN
SET @sql = "SELECT
    `complainants`.`id` AS `id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`file` AS `file`,
    `complainants`.`ext` AS `ext`,
    `complainants`.`gender` AS `gender`,
    `complainants`.`divyaang` AS `divyaang`,
    `complainants`.`created_at` AS `created_at`,
    `complainants`.`is_pending_dues` AS `is_pending_dues`,
    `resident_academic_details`.`reference_number` AS `reference_number`,
    `resident_academic_details`.`registration_no` AS `registration_no`,
    `resident_academic_details`.`course_year` AS `course_year`,
    `resident_academic_details`.`branch` AS `branch_id`,
    `resident_academic_details`.`cgpa` AS `cgpa`,
    `resident_academic_details`.`course` AS `course_id`,
    `academic_courses`.`course_name` AS `course_name`,
    `academic_course_branches`.`branch_name` AS `branch_name`,
    `resident_personal_details`.`personal_data` AS `personal_data`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.dob'
        )
    ) AS `dob`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].address'
        )
    ) AS `parent_address`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].name'
        )
    ) AS `parent_name`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].phone_number'
        )
    ) AS `parent_phone_number`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].email'
        )
    ) AS `parent_email`,
    json_extract(
        `resident_personal_details`.`personal_data`,
        '$.parents'
    ) AS `parents`,
    json_extract(
        `resident_personal_details`.`personal_data`,
        '$.blood_group'
    ) AS `blood_group`,
    json_extract(
        `resident_personal_details`.`personal_data`,
        '$.local_guardians'
    ) AS `local_guardians`,
    `complainants`.`created_by` AS `created_by`,
    `resident_residences`.`resident_status` AS `resident_status`,
    `resident_residences`.`living_start_at` AS `living_start_at`,
    `resident_residences`.`booked_up_to` AS `booked_up_to`,
    `resident_residences`.`tentative_living_up_to` AS `tentative_living_up_to`,
    `resident_residences`.`residence_id` AS `residence_id`,
    `complainants`.`approved_at` AS `approved_at`,
    `complainants`.`approved_by` AS `approved_by`,
    `residence_status`.`residence_status_text` AS `residence_status_text`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `building_residence`.`unit_type` AS `unit_type`,
    `building_residence`.`floor` AS `floor`,
    `building_unit_types`.`Description` AS `unit_name`,
    `building`.`name` AS `building_name`,
    `building_residence`.`building_id` AS `building_id`,
    ifnull(`pp`.`complete_in_per`, 0) AS `profile_complete`
FROM
    (
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            `complainants`
                                            join `resident_residences` on(
                                                `complainants`.`id` = `resident_residences`.`resident_id`
                                            )
                                        )
                                        left join `resident_academic_details` on(
                                            `complainants`.`id` = `resident_academic_details`.`resident_id`
                                        )
                                    )
                                    left join `academic_courses` on(
                                        `academic_courses`.`id` = `resident_academic_details`.`course`
                                    )
                                )
                                left join `academic_course_branches` on(
                                    `academic_course_branches`.`branch_id` = `resident_academic_details`.`branch`
                                )
                            )
                            left join `building_residence` on(
                                `resident_residences`.`residence_id` = `building_residence`.`id`
                            )
                        )
                        left join `building_unit_types` on(
                            `building_residence`.`unit_type` = `building_unit_types`.`unit_type_id`
                        )
                    )
                    left join `building` on(
                        `building`.`id` = `building_residence`.`building_id`
                    )
                )
                left join `residence_status` on(
                    `residence_status`.`residence_status_id` = `resident_residences`.`resident_status`
                )
            )
            left join `resident_personal_details` on(
                `complainants`.`id` = `resident_personal_details`.`resident_id`
            )
        )
        left join (
            select
                `complainants`.`id` AS `resident_id`,
                round(
                    (
                        octet_length(
                            trim(
                                both ','
                                from
                                    concat(
                                        coalesce(`kyc`.`doc_filled`, ''),
                                        ',',
                                        coalesce(
                                            `resident_personal_details`.`resident_profile_setting_id`,
                                            ''
                                        )
                                    )
                            )
                        ) - octet_length(
                            replace(
                                trim(
                                    both ','
                                    from
                                        concat(
                                            coalesce(`kyc`.`doc_filled`, ''),
                                            ',',
                                            coalesce(
                                                `resident_personal_details`.`resident_profile_setting_id`,
                                                ''
                                            )
                                        )
                                ),
                                ',',
                                ''
                            )
                        ) + 1
                    ) / (
                        select
                            count(`resident_profile_settings`.`id`)
                        from
                            `resident_profile_settings`
                        where
                            `resident_profile_settings`.`isActive` = 1
                    ) * 100,
                    0
                ) AS `complete_in_per`
            from
                (
                    (
                        `complainants`
                        left join (
                            select
                                group_concat(
                                    distinct `resident_kyc`.`resident_profile_setting_id` separator ','
                                ) AS `doc_filled`,
                                `resident_kyc`.`resident_id` AS `resident_id`
                            from
                                `resident_kyc`
                            group by
                                `resident_kyc`.`resident_id`
                        ) `kyc` on(`kyc`.`resident_id` = `complainants`.`id`)
                    )
                    left join `resident_personal_details` on(
                        `resident_personal_details`.`resident_id` = `complainants`.`id`
                    )
                )
            where
                `resident_personal_details`.`resident_profile_setting_id` is not null
                or `kyc`.`doc_filled` is not null
        ) `pp` on(`complainants`.`id` = `pp`.`resident_id`)
    )
 WHERE
    `resident_residences`.`isActive` = 1
    AND `complainants`.`is_activated` = 'A' ";

IF (in_building_ids IS NOT NULL AND in_building_ids != '') THEN
    SET @sql = CONCAT(@sql, ' AND `building_residence`.`building_id` IN (', in_building_ids, ')');
END IF;

SET @sql = CONCAT(@sql,' ORDER BY `complainants`.`id` DESC');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;



CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `leave_request_view` AS
SELECT 
leave_requests.id, 
leave_requests.resident_id, 
leave_requests.residence_id, 
leave_requests.from_date, 
leave_requests.to_date, 
leave_requests.leave_time, 
leave_requests.planned_reporting_dt, 
leave_requests.reporting_dt, 
leave_requests.reporting_time, 
leave_requests.reporting_ack_by, 
leave_requests.template_id, 
leave_requests.description, 
leave_requests.flag, 
leave_requests.file_name, 
leave_requests.created_by, 
leave_requests.approved_at, 
leave_requests.approved_by, 
leave_requests.cancelled_at, 
leave_requests.cancelled_by, 
leave_requests.cancel_reason, 
leave_requests.created_at, 
leave_requests.updated_at, 
leave_requests.isActive, 
leave_requests.ex_approval_status, 
leave_requests.approval_code, 
leave_requests.ex_action_at, 
leave_requests.approver_comment, 
leave_requests.early_approve_by, 
leave_requests.early_approve_at, 
leave_requests.extension_date, 
leave_requests.extension_approve_by, 
leave_requests.extension_approve_at, 
leave_requests.eduhead_requested_by, 
leave_requests.eduhead_requested_at, 
leave_requests.eduhead_status, 
leave_requests.eduhead_action_at, 
leave_requests.building_exit_ack_by, 
leave_requests.building_exit_date_time, 
leave_requests.maingate_exit_ack_by, 
leave_requests.maingate_exit_at,
complainant_view.complainant_name AS resident_name,
complainant_view.contact_no,
complainant_view.gender,
complainant_view.file,
complainant_view.ext AS ext,
complainant_view.apartment_name,
complainant_view.building_id,
complainant_view.building_name,
leave_templates.description AS template_description,
resident_only_academic_view.reference_number AS roll_number,
resident_only_academic_view.course_year,
resident_only_academic_view.branch,
resident_only_academic_view.course,
resident_only_academic_view.course_name,
resident_only_academic_view.branch_name
FROM `leave_requests` 
LEFT JOIN complainant_view ON complainant_view.id=leave_requests.resident_id 
LEFT JOIN leave_templates ON leave_templates.id = leave_requests.template_id 
LEFT JOIN resident_only_academic_view ON leave_requests.resident_id=resident_only_academic_view.resident_id;


ALTER TABLE `leave_settings` CHANGE `branch_id` `branch_id` INT(11) NULL DEFAULT NULL;

ALTER TABLE building_users DROP INDEX building_id;
ALTER TABLE building_users ADD UNIQUE KEY building_id (`building_id`, `user_id`);

-- 2024-08-05
ALTER TABLE `leave_settings` ADD `is_warden_view_required` TINYINT(1) NOT NULL DEFAULT '1' AFTER `gender`;

DROP VIEW IF EXISTS `resident_leave_approval_settings_view`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `resident_leave_approval_settings_view` AS
SELECT
    `rad`.`resident_id` AS `resident_id`,
    `rad`.`course` AS `course`,
    `rad`.`branch` AS `branch`,
    `rad`.`course_year` AS `course_year`,
    `rad`.`registration_no` AS `registration_no`,
    `ls`.`feature` AS `feature`,
    `ls`.`is_warden_view_required` AS `is_warden_view_required`,
    `ls`.`is_warden_approval_required` AS `is_warden_approval_required`,
    `ls`.`is_parent_approval_required` AS `is_parent_approval_required`,
    `ls`.`is_notification_to_parent` AS `is_notification_to_parent`,
    `ls`.`is_edu_head_approval_required` AS `is_edu_head_approval_required`,
    `ls`.`approver_ids` AS `approver_ids`
FROM
    (
        `resident_academic_details` `rad`
        left join `leave_settings` `ls` on(
            (
                `rad`.`branch` = `ls`.`branch_id`
                or `rad`.`branch` is null
            )
            and `rad`.`course` = `ls`.`course_id`
            and `rad`.`course_year` = `ls`.`course_year`
        )
    )
WHERE
    `ls`.`isActive` = 1;



DROP PROCEDURE IF EXISTS `getNoticeBoard`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getNoticeBoard`(IN `in_type` ENUM('all','resident','team','community'), IN `in_reference_id` BIGINT, IN `from_dt` DATE, IN `to_dt` DATE) NO SQL BEGIN
if(in_type='all') then
SELECT noticeboard.*,COUNT(notice_board_file.id) as files_count,user.name as publisher,IF(Alias IS NULL ,role.role_name,role.Alias) as role_name,building.name as building_name,building.id as building_id,building_residence.apartment_name,building_residence.id as residence_id from noticeboard


left join building_residence on (noticeboard.recipient='residence' and noticeboard.relatedid=building_residence.id)
LEFT join building on ((noticeboard.recipient='building' and noticeboard.relatedid=building.id) or (noticeboard.recipient='residence' and building_residence.building_id=building.id))
inner join user on user.id=noticeboard.published_by
LEFT join role on role.role_id=user.userRole
LEFT join notice_board_file on notice_board_file.notice_id=noticeboard.id
where noticeboard.publish_at is null OR date(noticeboard.created_at) BETWEEN from_dt and to_dt  
GROUP by noticeboard.id
order by noticeboard.id desc;
elseif(in_type='resident') then
	set @resident_id=(SELECT user.relatedId from user where user.id=in_reference_id);
	
    select complainant_view.residence_id,complainant_view.gender,complainant_view.resident_status,complainant_view.building_id into @residence_id,@gender,@resident_status,@building_id from complainant_view 	WHERE complainant_view.id=@resident_id;
	

	SELECT noticeboard.id,noticeboard.subject,noticeboard.body,noticeboard.created_at,noticeboard.updated_at,noticeboard.event_at,noticeboard.recipient,noticeboard.publish_at, ifnull(JSON_EXISTS(noticeboard.read_by, CONCAT('$.',in_reference_id)),0) as isRead,user.name as publisher,IF(Alias IS NULL ,role.role_name,role.Alias) as role_name,COUNT(notice_board_file.id) as files_count,noticeboard.publish_at from noticeboard
	inner join user on user.id=noticeboard.published_by
    LEFT join role on role.role_id=user.userRole
    LEFT join notice_board_file on notice_board_file.notice_id=noticeboard.id
	WHERE noticeboard.isActive = 1   
    group by noticeboard.id
    order by noticeboard.id desc;
elseif(in_type='community') then
	
	SELECT noticeboard.id,noticeboard.subject,noticeboard.body,noticeboard.created_at,noticeboard.updated_at,noticeboard.event_at,noticeboard.recipient,noticeboard.publish_at, ifnull(JSON_EXISTS(noticeboard.read_by, CONCAT('$.',in_reference_id)),0) as isRead,user.name as publisher,IF(Alias IS NULL ,role.role_name,role.Alias) as role_name,COUNT(notice_board_file.id) as files_count,noticeboard.publish_at from noticeboard
	inner join user on user.id=noticeboard.published_by
    LEFT join role on role.role_id=user.userRole
    LEFT join notice_board_file on notice_board_file.notice_id=noticeboard.id
	WHERE noticeboard.stage='published' and recipient='society'  
    and date(noticeboard.created_at) BETWEEN from_dt and to_dt 
    group by noticeboard.id
    order by noticeboard.id desc;
end if;
END$$
DELIMITER ;

DROP VIEW IF EXISTS `complaint_view`;

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `complaint_view` AS
SELECT
    `complaints`.`complain_id` AS `complain_id`,
    `complaints`.`complainant_id` AS `complainant_id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`divyaang` AS `divyaang`,
    CASE
        WHEN `user`.`language` = 'EN' THEN `complain_subjects`.`subject_eng`
        ELSE `complain_subjects`.`subject_eng`
    END AS `complaint_subject`,
    `complaints`.`subject_id` AS `subject_id`,
    `complain_headers`.`header_id` AS `header_id`,
    `complain_headers`.`header` AS `header`,
    `complain_status`.`complain_status_name` AS `complaint_status`,
    `complain_status`.`complain_status_id` AS `complain_status_id`,
    `complaints`.`complaint_date` AS `complaint_date`,
    `complaints`.`complaint_time` AS `complaint_time`,
    `complaints`.`description` AS `description`,
    `complaints`.`isAnonymous` AS `isAnonymous`,
    `complain_source`.`complain_source_text` AS `complain_source_text`,
    `complaints`.`complain_source_id` AS `complain_source_id`,
    `ca`.`assignDate` AS `assignDate`,
    `complaints`.`sla_id` AS `sla_id`,
    `complaints`.`priority` AS `priority`,
    `complain_priority`.`priority_desc` AS `priority_desc`,
    `complaints`.`latitude` AS `latitude`,
    `complaints`.`longitute` AS `longitute`,
    `complaints`.`scope` AS `scope`,
    `complain_scopes`.`scope_text` AS `scope_text`,
    `complaints`.`scope_reference` AS `scope_reference`,
    `user`.`userRole` AS `userRole`,
    `user`.`name` AS `created_by_name`,
    `role`.`role_name` AS `role_name`,
    `user`.`language` AS `language`,
    `complaints`.`complaint` AS `complaint`,
    `complainants_feedback`.`feedback_rating` AS `complainant_feedback`,
    `department`.`department_name` AS `department_name`,
    `complaints`.`department_id` AS `department_id`,
    `department_team`.`member_name` AS `member_name`,
    `complaints`.`team_member_id` AS `team_member_id`,
    `department_team`.`member_contactno` AS `member_contactno`,
    `department_team`.`member_email` AS `member_email`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `complaints`.`residence_id` AS `residence_id`,
    `building_residence`.`floor` AS `floor`,
    `building`.`name` AS `building_name`,
    `building_residence`.`building_id` AS `building_id`,
    GROUP_CONCAT(`complaint_slot_history`.`slot_history_id`) AS `slot_history_id`,
    GROUP_CONCAT(`complaint_slot_history`.`slot_date`) AS `slot_date`,
    GROUP_CONCAT(`work_slots`.`slot_start`) AS `slot_start`,
    GROUP_CONCAT(`work_slots`.`slot_end`) AS `slot_end`
FROM
    (
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            (
                                                (
                                                    (
                                                        (
                                                            (
                                                                (
                                                                    (
                                                                        `complaints`
                                                                        left join `complain_subjects` on(
                                                                            `complaints`.`subject_id` = `complain_subjects`.`subject_id`
                                                                        )
                                                                    )
                                                                    left join `complain_headers` on(
                                                                        `complain_headers`.`header_id` = `complain_subjects`.`header_id`
                                                                    )
                                                                )
                                                                left join `complain_scopes` on(
                                                                    `complaints`.`scope` = `complain_scopes`.`scope_id`
                                                                )
                                                            )
                                                            left join `department_team` on(
                                                                `complaints`.`team_member_id` = `department_team`.`member_id`
                                                            )
                                                        )
                                                        left join `user` on(`complaints`.`created_by` = `user`.`id`)
                                                    )
                                                    left join `role` on(`role`.`role_id` = `user`.`userRole`)
                                                )
                                                left join `complain_priority` on(
                                                    `complaints`.`priority` = `complain_priority`.`priority_id`
                                                )
                                            )
                                            left join `complainants_feedback` on(
                                                `complainants_feedback`.`complain_no` = `complaints`.`complain_id`
                                            )
                                        )
                                        join `department` on(
                                            `complaints`.`department_id` = `department`.`department_id`
                                        )
                                    )
                                    left join `complainants` on(
                                        `complaints`.`complainant_id` = `complainants`.`id`
                                    )
                                )
                                join `complain_source` on(
                                    `complaints`.`complain_source_id` = `complain_source`.`complain_source_id`
                                )
                            )
                            join `complain_status` on(
                                `complaints`.`complaint_status` = `complain_status`.`complain_status_id`
                            )
                        )
                        left join (
                            select
                                max(`complain_activities`.`date`) AS `assignDate`,
                                `complain_activities`.`complain_no` AS `complain_no`
                            from
                                `complain_activities`
                            where
                                `complain_activities`.`activity_type` = 4
                            group by
                                `complain_activities`.`complain_no`
                        ) `ca` on(`complaints`.`complain_id` = `ca`.`complain_no`)
                    )
                    left join `building_residence` on(
                        `building_residence`.`id` = `complaints`.`residence_id`
                    )
                )
                left join `building` on(
                    `building_residence`.`building_id` = `building`.`id`
                )
            )
            left join `complaint_slot_history` on(
                `complaint_slot_history`.`complaint_id` = `complaints`.`complain_id`
            )
        )
        left join `work_slots` on(
            `work_slots`.`id` = `complaint_slot_history`.`slot_id`
        )
    )
GROUP BY
    `complaints`.`complain_id`
ORDER BY
    `complaints`.`complain_id` DESC;

-- 2024-08-08

DROP PROCEDURE IF EXISTS `getActiveResident`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getActiveResident`(IN `in_complainant_name` VARCHAR(255), IN `in_contact_no` VARCHAR(255), IN `in_email` VARCHAR(255), IN `in_gender` VARCHAR(10), IN `in_reference_number` VARCHAR(255), IN `in_registration_no` VARCHAR(255), IN `in_course_year` VARCHAR(255), IN `in_cgpa` VARCHAR(10), IN `in_course_name` VARCHAR(255), IN `in_branch_name` VARCHAR(255), IN `in_building_ids` VARCHAR(255))
BEGIN
SET @sql = "SELECT
    `complainants`.`id` AS `id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`file` AS `file`,
    `complainants`.`ext` AS `ext`,
    `complainants`.`gender` AS `gender`,
    `complainants`.`divyaang` AS `divyaang`,
    `complainants`.`created_at` AS `created_at`,
    `complainants`.`is_pending_dues` AS `is_pending_dues`,
    `resident_academic_details`.`reference_number` AS `reference_number`,
    `resident_academic_details`.`registration_no` AS `registration_no`,
    `resident_academic_details`.`course_year` AS `course_year`,
    `resident_academic_details`.`branch` AS `branch_id`,
    `resident_academic_details`.`cgpa` AS `cgpa`,
    `resident_academic_details`.`course` AS `course_id`,
    `academic_courses`.`course_name` AS `course_name`,
    `academic_course_branches`.`branch_name` AS `branch_name`,
    `resident_personal_details`.`personal_data` AS `personal_data`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.dob'
        )
    ) AS `dob`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].address'
        )
    ) AS `parent_address`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].name'
        )
    ) AS `parent_name`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].phone_number'
        )
    ) AS `parent_phone_number`,
    json_unquote(
        json_extract(
            `resident_personal_details`.`personal_data`,
            '$.parents[0].email'
        )
    ) AS `parent_email`,
    json_extract(
        `resident_personal_details`.`personal_data`,
        '$.parents'
    ) AS `parents`,
    json_extract(
        `resident_personal_details`.`personal_data`,
        '$.blood_group'
    ) AS `blood_group`,
    json_extract(
        `resident_personal_details`.`personal_data`,
        '$.local_guardians'
    ) AS `local_guardians`,
    `complainants`.`created_by` AS `created_by`,
    `resident_residences`.`resident_status` AS `resident_status`,
    `resident_residences`.`living_start_at` AS `living_start_at`,
    `resident_residences`.`booked_up_to` AS `booked_up_to`,
    `resident_residences`.`tentative_living_up_to` AS `tentative_living_up_to`,
    `resident_residences`.`residence_id` AS `residence_id`,
    `complainants`.`approved_at` AS `approved_at`,
    `complainants`.`approved_by` AS `approved_by`,
    `residence_status`.`residence_status_text` AS `residence_status_text`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `building_residence`.`unit_type` AS `unit_type`,
    `building_residence`.`floor` AS `floor`,
    `building_unit_types`.`Description` AS `unit_name`,
    `building`.`name` AS `building_name`,
    `building_residence`.`building_id` AS `building_id`,
    ifnull(`pp`.`complete_in_per`, 0) AS `profile_complete`
FROM
        `complainants`
    JOIN `resident_residences` ON `complainants`.`id` = `resident_residences`.`resident_id`
    LEFT JOIN `resident_academic_details` ON `complainants`.`id` = `resident_academic_details`.`resident_id`
    LEFT JOIN `academic_courses` ON `academic_courses`.`id` = `resident_academic_details`.`course`
    LEFT JOIN `academic_course_branches` ON `academic_course_branches`.`branch_id` = `resident_academic_details`.`branch`
    LEFT JOIN `building_residence` ON `resident_residences`.`residence_id` = `building_residence`.`id`
    LEFT JOIN `building_unit_types` ON `building_residence`.`unit_type` = `building_unit_types`.`unit_type_id`
    LEFT JOIN `building` ON `building`.`id` = `building_residence`.`building_id`
    LEFT JOIN `residence_status` ON `residence_status`.`residence_status_id` = `resident_residences`.`resident_status`
    LEFT JOIN `resident_personal_details` ON `complainants`.`id` = `resident_personal_details`.`resident_id`
    LEFT JOIN (
        SELECT
            `complainants`.`id` AS `resident_id`,
            round(
                (
                    octet_length(
                        trim(
                            both ','
                            from
                                concat(
                                    coalesce(`kyc`.`doc_filled`, ''),
                                    ',',
                                    coalesce(
                                        `resident_personal_details`.`resident_profile_setting_id`,
                                        ''
                                    )
                                )
                        )
                    ) - octet_length(
                        replace(
                            trim(
                                both ','
                                from
                                    concat(
                                        coalesce(`kyc`.`doc_filled`, ''),
                                        ',',
                                        coalesce(
                                            `resident_personal_details`.`resident_profile_setting_id`,
                                            ''
                                        )
                                    )
                            ),
                            ',',
                            ''
                        )
                    ) + 1
                ) / (
                    SELECT
                        count(`resident_profile_settings`.`id`)
                    FROM
                        `resident_profile_settings`
                    WHERE
                        `resident_profile_settings`.`isActive` = 1
                ) * 100,
                0
            ) AS `complete_in_per`
        FROM
            `complainants`
        LEFT JOIN (
            SELECT
                group_concat(
                    distinct `resident_kyc`.`resident_profile_setting_id` separator ','
                ) AS `doc_filled`,
                `resident_kyc`.`resident_id` AS `resident_id`
            FROM
                `resident_kyc`
            GROUP BY
                `resident_kyc`.`resident_id`
        ) `kyc` ON `kyc`.`resident_id` = `complainants`.`id`
        LEFT JOIN `resident_personal_details` ON `resident_personal_details`.`resident_id` = `complainants`.`id`
        WHERE
            `resident_personal_details`.`resident_profile_setting_id` IS NOT NULL
            OR `kyc`.`doc_filled` IS NOT NULL
    ) `pp` ON `complainants`.`id` = `pp`.`resident_id`
    WHERE
        `resident_residences`.`isActive` = 1
        AND `complainants`.`is_activated` = 'A'";

    
    IF in_complainant_name IS NOT NULL AND in_complainant_name != '' THEN
        SET @sql = CONCAT(@sql, ' AND `complainants`.`complainant_name` LIKE ''%', in_complainant_name, '%''');
    END IF;
    IF in_contact_no IS NOT NULL AND in_contact_no != '' THEN
        SET @sql = CONCAT(@sql, ' AND `complainants`.`contact_no` LIKE ''%', in_contact_no, '%''');
    END IF;
    IF in_email IS NOT NULL AND in_email != '' THEN
        SET @sql = CONCAT(@sql, ' AND `complainants`.`email` LIKE ''%', in_email, '%''');
    END IF;
    IF in_gender IS NOT NULL AND in_gender != '' THEN
        SET @sql = CONCAT(@sql, ' AND `complainants`.`gender` = ''', in_gender, '''');
    END IF;
    IF in_reference_number IS NOT NULL AND in_reference_number != '' THEN
        SET @sql = CONCAT(@sql, ' AND `resident_academic_details`.`reference_number` LIKE ''%', in_reference_number, '%''');
    END IF;
    IF in_registration_no IS NOT NULL AND in_registration_no != '' THEN
        SET @sql = CONCAT(@sql, ' AND `resident_academic_details`.`registration_no` LIKE ''%', in_registration_no, '%''');
    END IF;
    IF in_course_year IS NOT NULL AND in_course_year != '' THEN
        SET @sql = CONCAT(@sql, ' AND `resident_academic_details`.`course_year` LIKE ''%', in_course_year, '%''');
    END IF;
    IF in_cgpa IS NOT NULL AND in_cgpa != '' THEN
        SET @sql = CONCAT(@sql, ' AND `resident_academic_details`.`cgpa` = ''', in_cgpa, '''');
    END IF;
    IF in_course_name IS NOT NULL AND in_course_name != '' THEN
        SET @sql = CONCAT(@sql, ' AND `academic_courses`.`course_name` LIKE ''%', in_course_name, '%''');
    END IF;
    IF in_branch_name IS NOT NULL AND in_branch_name != '' THEN
        SET @sql = CONCAT(@sql, ' AND `academic_course_branches`.`branch_name` LIKE ''%', in_branch_name, '%''');
    END IF;
    IF in_building_ids IS NOT NULL AND in_building_ids != '' THEN
        SET @sql = CONCAT(@sql, ' AND `building_residence`.`building_id` IN (', in_building_ids, ')');
    END IF;

    SET @sql = CONCAT(@sql, ' ORDER BY `complainants`.`created_at` DESC');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `getStudentList`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getStudentList`()
SELECT 
    complainants.id AS id, 
    complainants.complainant_name AS complainant_name, 
    complainants.contact_no AS contact_no, 
    complainants.email AS email, 
    complainants.file AS file, 
    complainants.ext AS ext, 
    complainants.gender AS gender, 
    complainants.divyaang AS divyaang, 
    complainants.created_at AS created_at, 
    complainants.is_pending_dues AS is_pending_dues, 
    resident_academic_details.reference_number AS reference_number, 
    resident_academic_details.registration_no AS registration_no, 
    resident_academic_details.course_year AS course_year, 
    resident_academic_details.branch AS branch_id, 
    resident_academic_details.cgpa AS cgpa, 
    resident_academic_details.course AS course_id, 
    academic_courses.course_name AS course_name, 
    academic_course_branches.branch_name AS branch_name, 
    resident_personal_details.personal_data AS personal_data, 
    json_unquote(json_extract(resident_personal_details.personal_data, '$.dob')) AS dob, 
    json_unquote(json_extract(resident_personal_details.personal_data, '$.parents[0].address')) AS parent_address, 
    json_unquote(json_extract(resident_personal_details.personal_data, '$.parents[0].name')) AS parent_name, 
    json_unquote(json_extract(resident_personal_details.personal_data, '$.parents[0].phone_number')) AS parent_phone_number, 
    json_unquote(json_extract(resident_personal_details.personal_data, '$.parents[0].email')) AS parent_email 
FROM 
    complainants 
LEFT JOIN 
    resident_academic_details ON complainants.id = resident_academic_details.resident_id 
LEFT JOIN 
    academic_courses ON academic_courses.id = resident_academic_details.course 
LEFT JOIN 
    academic_course_branches ON academic_course_branches.branch_id = resident_academic_details.branch 
LEFT JOIN 
    (SELECT resident_id FROM resident_residences WHERE isActive = 0) AS inactive_residences 
    ON complainants.id = inactive_residences.resident_id 
    
LEFT JOIN resident_residences
    ON complainants.id = resident_residences.resident_id 

LEFT JOIN 
    resident_personal_details ON complainants.id = resident_personal_details.resident_id 
WHERE 
    resident_residences.resident_id IS NULL 
    AND complainants.is_activated = 'A' 
ORDER BY 
    complainants.id DESC$$
DELIMITER ;

-- 2024-08-09

ALTER VIEW `resident_leave_approval_settings_view` AS
SELECT
    `rad`.`resident_id` AS `resident_id`,
    `rad`.`course` AS `course`,
    `rad`.`branch` AS `branch`,
    `rad`.`course_year` AS `course_year`,
    `rad`.`reference_number` AS `reference_number`,
    `rad`.`gender` AS `gender`,
    `rad`.`registration_no` AS `registration_no`,
    `ls`.`feature` AS `feature`,
    `ls`.`is_warden_view_required` AS `is_warden_view_required`,
    `ls`.`is_warden_approval_required` AS `is_warden_approval_required`,
    `ls`.`is_parent_approval_required` AS `is_parent_approval_required`,
    `ls`.`is_notification_to_parent` AS `is_notification_to_parent`,
    `ls`.`is_edu_head_approval_required` AS `is_edu_head_approval_required`,
    `ls`.`approver_ids` AS `approver_ids`
FROM
    (
        `resident_academic_view` `rad`
        left join `leave_settings` `ls` on(
            (
                `rad`.`branch` = `ls`.`branch_id`
                or `rad`.`branch` is null
            )
            and `rad`.`course` = `ls`.`course_id`
            and `rad`.`course_year` = `ls`.`course_year`
            and `rad`.`gender` = `ls`.`gender`
        )
    )
WHERE
    `ls`.`isActive` = 1;

-- 2024-08-13
ALTER TABLE `leave_requests` ADD `is_missing_academic` TINYINT NULL DEFAULT '0' AFTER `eduhead_status`;

DROP VIEW IF EXISTS `leave_request_view`;

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `leave_request_view` AS
SELECT 
leave_requests.id, 
leave_requests.resident_id, 
leave_requests.residence_id, 
leave_requests.from_date, 
leave_requests.to_date, 
leave_requests.leave_time, 
leave_requests.planned_reporting_dt, 
leave_requests.reporting_dt, 
leave_requests.reporting_time, 
leave_requests.reporting_ack_by, 
leave_requests.template_id, 
leave_requests.description, 
leave_requests.flag, 
leave_requests.file_name, 
leave_requests.created_by, 
leave_requests.approved_at, 
leave_requests.approved_by, 
leave_requests.cancelled_at, 
leave_requests.cancelled_by, 
leave_requests.cancel_reason, 
leave_requests.created_at, 
leave_requests.updated_at, 
leave_requests.isActive, 
leave_requests.ex_approval_status, 
leave_requests.approval_code, 
leave_requests.ex_action_at, 
leave_requests.approver_comment, 
leave_requests.early_approve_by, 
leave_requests.early_approve_at, 
leave_requests.extension_date, 
leave_requests.extension_approve_by, 
leave_requests.is_missing_academic,
leave_requests.eduhead_requested_by, 
leave_requests.eduhead_requested_at, 
leave_requests.eduhead_status, 
leave_requests.eduhead_action_at, 
leave_requests.building_exit_ack_by, 
building_exit_ack.name AS building_exit_ack_by_name,
leave_requests.building_exit_date_time, 
leave_requests.maingate_exit_ack_by, 
maingate_exit_ack.name AS maingate_exit_ack_by_name,
leave_requests.maingate_exit_at,
complainant_view.complainant_name AS resident_name,
complainant_view.contact_no,
complainant_view.gender,
complainant_view.file,
complainant_view.ext AS ext,
complainant_view.apartment_name,
complainant_view.building_id,
complainant_view.building_name,
leave_templates.description AS template_description,
resident_only_academic_view.reference_number AS roll_number,
resident_only_academic_view.course_year,
resident_only_academic_view.branch,
resident_only_academic_view.course,
resident_only_academic_view.course_name,
resident_only_academic_view.branch_name
FROM `leave_requests` 
LEFT JOIN complainant_view ON complainant_view.id=leave_requests.resident_id 
LEFT JOIN leave_templates ON leave_templates.id = leave_requests.template_id 
LEFT JOIN user AS building_exit_ack ON building_exit_ack.id=leave_requests.building_exit_ack_by
LEFT JOIN user AS maingate_exit_ack ON maingate_exit_ack.id=leave_requests.maingate_exit_ack_by
LEFT JOIN resident_only_academic_view ON leave_requests.resident_id=resident_only_academic_view.resident_id;


DROP VIEW IF EXISTS `complaint_view`;

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `complaint_view` AS
SELECT
    `complaints`.`complain_id` AS `complain_id`,
    `complaints`.`complainant_id` AS `complainant_id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`divyaang` AS `divyaang`,
    CASE
        WHEN `user`.`language` = 'EN' THEN `complain_subjects`.`subject_eng`
        ELSE `complain_subjects`.`subject_eng`
    END AS `complaint_subject`,
    `complaints`.`subject_id` AS `subject_id`,
    `complain_headers`.`header_id` AS `header_id`,
    `complain_headers`.`header` AS `header`,
    `complain_status`.`complain_status_name` AS `complaint_status`,
    `complain_status`.`complain_status_id` AS `complain_status_id`,
    `complaints`.`complaint_date` AS `complaint_date`,
    `complaints`.`complaint_time` AS `complaint_time`,
    `complaints`.`description` AS `description`,
    `complaints`.`isAnonymous` AS `isAnonymous`,
    `complain_source`.`complain_source_text` AS `complain_source_text`,
    `complaints`.`complain_source_id` AS `complain_source_id`,
    `ca`.`assignDate` AS `assignDate`,
    `complaints`.`sla_id` AS `sla_id`,
    `complaints`.`priority` AS `priority`,
    `complain_priority`.`priority_desc` AS `priority_desc`,
    `complaints`.`latitude` AS `latitude`,
    `complaints`.`longitute` AS `longitute`,
    `complaints`.`scope` AS `scope`,
    `complain_scopes`.`scope_text` AS `scope_text`,
    `complaints`.`scope_reference` AS `scope_reference`,
    `user`.`userRole` AS `userRole`,
    `user`.`name` AS `created_by_name`,
    `role`.`role_name` AS `role_name`,
    `user`.`language` AS `language`,
    `complaints`.`complaint` AS `complaint`,
    `complainants_feedback`.`feedback_rating` AS `complainant_feedback`,
    `department`.`department_name` AS `department_name`,
    `complaints`.`department_id` AS `department_id`,
    `department_team`.`member_name` AS `member_name`,
    `complaints`.`team_member_id` AS `team_member_id`,
    `department_team`.`member_contactno` AS `member_contactno`,
    `department_team`.`member_email` AS `member_email`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `complaints`.`residence_id` AS `residence_id`,
    `building_residence`.`floor` AS `floor`,
    `building`.`name` AS `building_name`,
    `building_residence`.`building_id` AS `building_id`,
    `slot_info`.`slot_history_id` AS `multi_slot_history_id`,
    `slot_info`.`slot_id` AS `multi_slot_id`,
    `slot_info`.`slot_date` AS `multi_slot_date`,
    `slot_info`.`slot_start` AS `multi_slot_start`,
    `slot_info`.`slot_end` AS `multi_slot_end`,
    `complaint_slot_history`.`slot_date` AS `slot_date`,
    `work_slots`.`slot_start` AS `slot_start`,
    `work_slots`.`slot_end` AS `slot_end`
FROM
    `complaints`
left join `complain_subjects` on `complaints`.`subject_id` = `complain_subjects`.`subject_id`
left join `complain_headers` on `complain_headers`.`header_id` = `complain_subjects`.`header_id`
left join `complain_scopes` on `complaints`.`scope` = `complain_scopes`.`scope_id`
left join `department_team` on `complaints`.`team_member_id` = `department_team`.`member_id`
left join `user` on `complaints`.`created_by` = `user`.`id`
left join `role` on `role`.`role_id` = `user`.`userRole`
left join `complain_priority` on `complaints`.`priority` = `complain_priority`.`priority_id`
left join `complainants_feedback` on `complainants_feedback`.`complain_no` = `complaints`.`complain_id`
join `department` on `complaints`.`department_id` = `department`.`department_id`
left join `complainants` on `complaints`.`complainant_id` = `complainants`.`id`
join `complain_source` on `complaints`.`complain_source_id` = `complain_source`.`complain_source_id`
join `complain_status` on `complaints`.`complaint_status` = `complain_status`.`complain_status_id`
left join (
    select
        max(`complain_activities`.`date`) AS `assignDate`,
        `complain_activities`.`complain_no` AS `complain_no`
    from
        `complain_activities`
    where
        `complain_activities`.`activity_type` = 4
    group by
        `complain_activities`.`complain_no`
) `ca` on(`complaints`.`complain_id` = `ca`.`complain_no`)

left join `building_residence` on `building_residence`.`id` = `complaints`.`residence_id`
left join `building` on `building_residence`.`building_id` = `building`.`id`
LEFT JOIN (
	SELECT
		`complaint_slot_history`.`complaint_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_history_id`) AS `slot_history_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_id`) AS `slot_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_date`) AS `slot_date`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_start`, '')) AS `slot_start`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_end`, '')) AS `slot_end`
	FROM
		`complaint_slot_history`
	LEFT JOIN `work_slots` ON `work_slots`.`id` = `complaint_slot_history`.`slot_id`
	where `complaint_slot_history`.`isActive` = 1 
	GROUP BY
		`complaint_slot_history`.`complaint_id`
) `slot_info` ON `complaints`.`complain_id` = `slot_info`.`complaint_id`
left join `complaint_slot_history` on `complaint_slot_history`.`slot_history_id` = `complaints`.`slot_history_id`
left join `work_slots` on `work_slots`.`id` = `complaint_slot_history`.`slot_id`

ORDER BY
    `complaints`.`complain_id` DESC;


ALTER TABLE `complaint_slot_history` ADD `isActive` TINYINT NULL DEFAULT '1';

DROP PROCEDURE IF EXISTS `buildingAccessHistory`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `buildingAccessHistory`(IN `in_building_id` INT)
    NO SQL
SELECT building_users.*,access_user.name as access_user_name,IF(role.Alias IS NULL ,role.role_name,role.Alias) as role_name,creator.name as creator_name,denier.name as denier_name FROM `building_users`
inner join user as access_user on access_user.id=building_users.user_id
INNER JOIN role on role.role_id=access_user.userRole
INNER join user as creator on creator.id=building_users.created_by
left join user as denier on denier.id=building_users.updated_by
where building_id=in_building_id
order by building_users.building_user_id DESC$$
DELIMITER ;

-- 2024-08-21
DROP PROCEDURE IF EXISTS getResidentPreferredOutletforMeals;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentPreferredOutletforMeals`(IN `in_optin_dt` DATE)
SELECT resident_selected_outlets.resident_id,resident_selected_outlets.kitchen_outlet_id,resident_selected_outlets.meal_for,resident_selected_outlets.optin_in_date,resident_selected_outlets.created_at,kitchen_subscriptions.id as kitchen_subscription_id,kitchen_subscriptions.kitchen_id,kitchen_subscriptions.default_outlet_id,kitchen_subscriptions.from_date,kitchen_subscriptions.to_date,meal_packages.package_name,meal_packages.meal_for as package_timings,complainants.complainant_name,complainants.contact_no,complainants.email FROM `resident_selected_outlets`
INNER join kitchen_subscriptions on kitchen_subscriptions.resident_id=resident_selected_outlets.resident_id and in_optin_dt BETWEEN kitchen_subscriptions.from_date and kitchen_subscriptions.to_date and kitchen_subscriptions.isActive=1 and kitchen_subscriptions.payment_status='completed'
INNER JOIN meal_packages on meal_packages.id=kitchen_subscriptions.package_id
INNER join complainants on complainants.id=resident_selected_outlets.resident_id
WHERE optin_in_date=in_optin_dt and resident_selected_outlets.isActive=1 and resident_selected_outlets.cancelled_at is null and FIND_IN_SET(resident_selected_outlets.meal_for, meal_packages.meal_for)  
ORDER BY `resident_selected_outlets`.`resident_id` ASC$$
DELIMITER ;

DROP PROCEDURE `GetIndividualsSuggestions`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetIndividualsSuggestions`(IN `complainantId` INT, IN `user_id` INT)
BEGIN

SELECT suggestion.suggestion_id,suggestion_text,suggestion_date,suggestion.suggestion_time,suggestion.quality_remark,sq.quality_remark_text,count(suggestion_file.id) as file_count,suggestion.management_comment
FROM suggestion

JOIN suggestion_quality_codes sq ON (suggestion.quality_remark = sq.quality_remark) 
left join suggestion_file on suggestion_file.suggestion_id=suggestion.suggestion_id

WHERE (case when complainantId>0 then suggestion.user_id = user_id or suggestion.suggestiont_id=complainantId else suggestion.user_id = user_id END) 
GROUP by suggestion.suggestion_id
order by suggestion.suggestion_id DESC
;


END$$

-- 2024-08-28
DROP PROCEDURE IF EXISTS `GetComplainClarification`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainClarification`(IN `complain_no` INT, IN `isPublic` BOOLEAN)
BEGIN
SET @sql = "SELECT cc.id,cc.clarification,cc.date,cc.time,cc.clarification_file,cc.file_ext,isPublic,(case when cc.user_id=0 then complainants.complainant_name else u.name end) as name,IF(role.Alias IS NULL ,role.role_name,role.Alias) as role_name  FROM `complain_clarification` cc 
inner join complaints on complaints.complain_id=cc.complain_id
inner join complainants on complainants.id=complaints.complainant_id
LEFT JOIN `user` u ON (cc.user_id = u.id)
LEFT JOIN `role` ON (u.userRole = role.role_id) WHERE  ";
 SET @sql = CONCAT(@sql,' cc.complain_id=',complain_no);
IF ( isPublic > 0 ) THEN
        SET @sql = CONCAT(@sql,' AND cc.isPublic =',isPublic);
END IF;
SET @sql = CONCAT(@sql,' order by cc.id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- from Bishnu sir
DROP PROCEDURE IF EXISTS `GetComplainsByRole`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainsByRole`(IN `in_user_id` INT, IN `input_role_id` INT(3), IN `in_status` INT, IN `daterangearray` VARCHAR(500), IN `in_status_group` LONGTEXT, IN `in_dept_id` INT(11))
BEGIN
SET @sql = "SELECT complaint_view.* from complaint_view WHERE 1 =1 ";

IF ( in_status is not null) THEN
	SET @sql = CONCAT(@sql,' AND complaint_view.complain_status_id =',in_status);
END IF;

IF (in_status_group IS NOT NULL AND in_status_group != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.complain_status_id IN (', in_status_group, ')');
END IF;

if(in_dept_id>0) then 
	SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',in_dept_id);
END IF;

if (input_role_id>5) then 
	SET @sql = CONCAT(@sql,' AND building_id in (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1)');
	if(input_role_id=8) THEN
		set @department_id=(select relatedId from user where user.id=in_user_id);
        if(@department_id>0) then 
			SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',@department_id);
        END IF;
	END IF;
	if(input_role_id=9) THEN
		set @team_member_id=(select relatedId from user where user.id=in_user_id);
		SET @sql = CONCAT(@sql,' AND complaint_view.team_member_id =',@team_member_id);
	END IF;
END IF;

IF ( daterangearray != '') THEN
	SET @sql = CONCAT(@sql,' AND complaint_date NOT BETWEEN ',daterangearray );
END IF;
SET @sql = CONCAT(@sql,' order by  complaint_view.complain_id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 2024-08-30
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'NOTICEBOARD', 'community_notice_allowed_roles', 'Allowed Roles For Community Notice Add & Edit', 'string', '0,1,2,3,4,5,6,7', '1', '2024-08-30 17:01:38'),
(NULL, 'NOTICEBOARD', 'building_notice_allowed_roles', 'Allowed Roles For Building Notice Add & Edit', 'string', '0,1,2,3,4,5,6,7', '1', '2024-08-30 17:01:38'),
(NULL, 'NOTICEBOARD', 'academic_notice_allowed_roles', 'Allowed Roles For Academic Notice Add & Edit', 'string', '0,1,2,3,4,5,6,7', '1', '2024-08-30 17:01:38');

-- 2024-08-31

DROP VIEW IF EXISTS `complaint_view`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `complaint_view` AS
SELECT
    `complaints`.`complain_id` AS `complain_id`,
    `complaints`.`complainant_id` AS `complainant_id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`divyaang` AS `divyaang`,
    CASE
        WHEN `user`.`language` = 'EN' THEN `complain_subjects`.`subject_eng`
        ELSE `complain_subjects`.`subject_eng`
    END AS `complaint_subject`,
    `complaints`.`subject_id` AS `subject_id`,
    `complain_headers`.`header_id` AS `header_id`,
    `complain_headers`.`header` AS `header`,
    `complain_status`.`complain_status_name` AS `complaint_status`,
    `complain_status`.`complain_status_id` AS `complain_status_id`,
    `complaints`.`complaint_date` AS `complaint_date`,
    `complaints`.`complaint_time` AS `complaint_time`,
    `complaints`.`description` AS `description`,
    `complaints`.`remark` AS `remark`,
    `complaints`.`isAnonymous` AS `isAnonymous`,
    `complain_source`.`complain_source_text` AS `complain_source_text`,
    `complaints`.`complain_source_id` AS `complain_source_id`,
    `ca`.`assignDate` AS `assignDate`,
    `complaints`.`sla_id` AS `sla_id`,
    `complaints`.`priority` AS `priority`,
    `complain_priority`.`priority_desc` AS `priority_desc`,
    `complaints`.`latitude` AS `latitude`,
    `complaints`.`longitute` AS `longitute`,
    `complaints`.`scope` AS `scope`,
    `complain_scopes`.`scope_text` AS `scope_text`,
    `complaints`.`scope_reference` AS `scope_reference`,
    `user`.`userRole` AS `userRole`,
    `user`.`name` AS `created_by_name`,
    `role`.`role_name` AS `role_name`,
    `user`.`language` AS `language`,
    `complaints`.`complaint` AS `complaint`,
    `complainants_feedback`.`feedback_rating` AS `complainant_feedback`,
    `department`.`department_name` AS `department_name`,
    `complaints`.`department_id` AS `department_id`,
    `department_team`.`member_name` AS `member_name`,
    `complaints`.`team_member_id` AS `team_member_id`,
    `department_team`.`member_contactno` AS `member_contactno`,
    `department_team`.`member_email` AS `member_email`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `complaints`.`residence_id` AS `residence_id`,
    `building_residence`.`floor` AS `floor`,
    `building`.`name` AS `building_name`,
    `building_residence`.`building_id` AS `building_id`,
    `slot_info`.`slot_history_id` AS `multi_slot_history_id`,
    `slot_info`.`slot_id` AS `multi_slot_id`,
    `slot_info`.`slot_date` AS `multi_slot_date`,
    `slot_info`.`slot_start` AS `multi_slot_start`,
    `slot_info`.`slot_end` AS `multi_slot_end`,
    `complaint_slot_history`.`slot_date` AS `slot_date`,
    `work_slots`.`slot_start` AS `slot_start`,
    `work_slots`.`slot_end` AS `slot_end`
FROM
    `complaints`
left join `complain_subjects` on `complaints`.`subject_id` = `complain_subjects`.`subject_id`
left join `complain_headers` on `complain_headers`.`header_id` = `complain_subjects`.`header_id`
left join `complain_scopes` on `complaints`.`scope` = `complain_scopes`.`scope_id`
left join `department_team` on `complaints`.`team_member_id` = `department_team`.`member_id`
left join `user` on `complaints`.`created_by` = `user`.`id`
left join `role` on `role`.`role_id` = `user`.`userRole`
left join `complain_priority` on `complaints`.`priority` = `complain_priority`.`priority_id`
left join `complainants_feedback` on `complainants_feedback`.`complain_no` = `complaints`.`complain_id`
join `department` on `complaints`.`department_id` = `department`.`department_id`
left join `complainants` on `complaints`.`complainant_id` = `complainants`.`id`
join `complain_source` on `complaints`.`complain_source_id` = `complain_source`.`complain_source_id`
join `complain_status` on `complaints`.`complaint_status` = `complain_status`.`complain_status_id`
left join (
    select
        max(`complain_activities`.`date`) AS `assignDate`,
        `complain_activities`.`complain_no` AS `complain_no`
    from
        `complain_activities`
    where
        `complain_activities`.`activity_type` = 4
    group by
        `complain_activities`.`complain_no`
) `ca` on(`complaints`.`complain_id` = `ca`.`complain_no`)

left join `building_residence` on `building_residence`.`id` = `complaints`.`residence_id`
left join `building` on `building_residence`.`building_id` = `building`.`id`
LEFT JOIN (
	SELECT
		`complaint_slot_history`.`complaint_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_history_id`) AS `slot_history_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_id`) AS `slot_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_date`) AS `slot_date`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_start`, '')) AS `slot_start`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_end`, '')) AS `slot_end`
	FROM
		`complaint_slot_history`
	LEFT JOIN `work_slots` ON `work_slots`.`id` = `complaint_slot_history`.`slot_id`
	where `complaint_slot_history`.`isActive` = 1 
	GROUP BY
		`complaint_slot_history`.`complaint_id`
) `slot_info` ON `complaints`.`complain_id` = `slot_info`.`complaint_id`
left join `complaint_slot_history` on `complaint_slot_history`.`slot_history_id` = `complaints`.`slot_history_id`
left join `work_slots` on `work_slots`.`id` = `complaint_slot_history`.`slot_id`

ORDER BY
    `complaints`.`complain_id` DESC;


-- from Bishnu sir
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentPreferredOutletforMeals`(IN `in_optin_dt` DATE)
SELECT 
    resident_selected_outlets.resident_id,
    resident_selected_outlets.kitchen_outlet_id,
    resident_selected_outlets.meal_for,
    resident_selected_outlets.optin_in_date,
    resident_selected_outlets.created_at,
    kitchen_subscriptions.id AS kitchen_subscription_id,
    kitchen_subscriptions.kitchen_id,
    kitchen_subscriptions.default_outlet_id,
    kitchen_subscriptions.from_date,
    kitchen_subscriptions.to_date,
    meal_packages.package_name,
    meal_packages.meal_for AS package_timings,
    complainant_view.complainant_name,
    complainant_view.contact_no,
    complainant_view.email,
    complainant_view.apartment_name,
    complainant_view.floor,
    complainant_view.building_name,
    complainant_view.unit_name
     
FROM 
    resident_selected_outlets
INNER JOIN 
    kitchen_subscriptions 
    ON kitchen_subscriptions.resident_id = resident_selected_outlets.resident_id 
    AND resident_selected_outlets.optin_in_date BETWEEN kitchen_subscriptions.from_date AND kitchen_subscriptions.to_date 
    AND kitchen_subscriptions.isActive = 1 
    AND kitchen_subscriptions.payment_status = 'completed'
INNER JOIN 
    meal_packages 
    ON meal_packages.id = kitchen_subscriptions.package_id
INNER JOIN 
    complainant_view 
    ON complainant_view.id = resident_selected_outlets.resident_id
WHERE 
    resident_selected_outlets.isActive = 1 
    AND resident_selected_outlets.cancelled_at IS NULL 
    AND FIND_IN_SET(resident_selected_outlets.meal_for, meal_packages.meal_for)
ORDER BY 
    resident_selected_outlets.resident_id ASC$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getKitchenSubscriptionReport`(
    IN `in_outlet_id` INT(11), 
    IN `in_kitchen_id` INT(11), 
    IN `in_filter_date` VARCHAR(50)
)
BEGIN
    SET @sql = 'SELECT 
                    `complainants`.`complainant_name`, 
                    `complainants`.`contact_no`, 
                    `complainants`.`email`,
                    `kitchen_subscriptions`.*, 
                    `kitchens`.`name` AS `kitchen_name`, 
                    `kitchens`.`id` AS `kitchen_id`,
                    `meal_packages`.`package_name`, 
                    `meal_packages`.`amount` AS `package_amount`, 
                    `meal_packages`.`meal_for`,
                    `meal_packages`.`package_meal_foods`,
                    `building`.`name` AS `building_name`,
                    `building_residence`.`apartment_name`,
                    rav.reference_number,registration_no,course_year,course_name,branch_name,batch_name,
                    `building_unit_types`.`Description` AS `unit_type_name`,
                    `kitchen_outlets`.`outlet_name` AS `last_consumption_outlet_name`,
                    `latest_package_consumptions`.`last_consumption_datetime`,`latest_package_consumptions`.`last_consumption_meal_for`
                FROM 
                    `kitchen_subscriptions` 
                INNER JOIN 
                    `meal_packages` ON `meal_packages`.`id` = `kitchen_subscriptions`.`package_id`
                INNER JOIN 
                    `complainants` ON `complainants`.`id` = `kitchen_subscriptions`.`resident_id`
                INNER JOIN 
                    `kitchens` ON `kitchens`.`id` = `kitchen_subscriptions`.`kitchen_id`
                INNER JOIN 
                    `resident_residences` ON `resident_residences`.`resident_id` = `kitchen_subscriptions`.`resident_id`
                INNER JOIN 
                    `building_residence` ON `building_residence`.`id` = `resident_residences`.`residence_id`
                INNER JOIN 
                    `building` ON `building`.`id` = `building_residence`.`building_id`
                LEFT JOIN
                    resident_academic_view AS rav ON rav.resident_id=`kitchen_subscriptions`.`resident_id`
                INNER JOIN 
                    `building_unit_types` ON `building_unit_types`.`unit_type_id` = `building_residence`.`unit_type`
                LEFT JOIN (
                    SELECT `subscription_id`, `created_at` AS `last_consumption_datetime`, `outlet_id`,`meal_for` AS `last_consumption_meal_for` FROM `package_consumptions` ORDER BY `created_at` DESC LIMIT 1
                ) AS `latest_package_consumptions` 
                ON `latest_package_consumptions`.`subscription_id` = `kitchen_subscriptions`.`id`
                LEFT JOIN 
                    `kitchen_outlets` ON `kitchen_outlets`.`id` = `latest_package_consumptions`.`outlet_id`
                WHERE 
                    `complainants`.`is_activated` = "A" 
                    AND `resident_residences`.`isActive` = 1 
                    AND `kitchen_subscriptions`.`isActive` = 1 
                    AND `kitchen_subscriptions`.`payment_status` = "COMPLETED"';

    IF in_outlet_id > 0 THEN
        SET @sql = CONCAT(@sql, " AND `kitchen_subscriptions`.`default_outlet_id` = ", in_outlet_id);
    END IF;

    IF in_kitchen_id > 0 THEN
        SET @sql = CONCAT(@sql, " AND `kitchen_subscriptions`.`kitchen_id` = ", in_kitchen_id);
    END IF;

    IF in_filter_date IS NOT NULL THEN
        SET @sql = CONCAT(@sql, " AND '", in_filter_date, "' BETWEEN `kitchen_subscriptions`.`from_date` AND `kitchen_subscriptions`.`to_date`");
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Uncomment the following line to debug SQL query
    -- SELECT @sql;
END$$
DELIMITER ;


INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'NOTICEBOARD', 'academic_notice_allowed_roles', 'Allowed Roles For Academic Notice Add & Edit', 'string', '0,1,2,3,4,5,6,7', '1', '2024-08-30 17:01:38');

DROP PROCEDURE IF EXISTS `GetComplainActivities`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainActivities`(IN `in_complain_no` BIGINT)
    NO SQL
BEGIN

DROP TEMPORARY TABLE IF EXISTS tmp_team_change_activities;
 CREATE TEMPORARY TABLE complain_all_activities(
     	`activity_id` INT NOT NULL,
     	`date` date NOT NULL,
     	`time` time NOT NULL,
     	`user_id` INT NOT NULL,
     	`activity_type` INT NOT NULL,
     	`activity_name` TEXT CHARACTER SET utf8 NOT NULL
 ) ;


INSERT INTO complain_all_activities ( 
      activity_id,
      date, 
      time, 
      user_id, 
      activity_type,activity_name ) 
SELECT 0, date, time, action_user, 4,CONCAT('Team member changed from ',prev_team.member_name,' to ',new_team.member_name)  FROM complain_change_history 
inner join department_team prev_team on complain_change_history.from_value=prev_team.member_id  
left join department_team new_team on complain_change_history.to_value=new_team.member_id
where complain_no=in_complain_no and assign_type='TEAM';


INSERT INTO complain_all_activities ( 
      activity_id,
      date, 
      time, 
      user_id, 
      activity_type,activity_name ) 
SELECT 0, date, time, action_user, 24,CONCAT('Subject changed from ',prev_subject.subject_eng,' to ',new_subjects.subject_eng)  FROM complain_change_history 
inner join complain_subjects prev_subject on complain_change_history.from_value=prev_subject.subject_id  
inner join complain_subjects new_subjects on complain_change_history.to_value=new_subjects.subject_id
where complain_no=in_complain_no and assign_type='SUB';


INSERT INTO complain_all_activities ( 
      activity_id,
      date, 
      time, 
      user_id, 
      activity_type,activity_name ) 
SELECT 0, date, time, action_user, 3,CONCAT('Department changed from ',prev_department.department_name,' to ',new_department.department_name)  FROM complain_change_history 
inner join department prev_department on complain_change_history.from_value=prev_department.department_id  
inner join department new_department on complain_change_history.to_value=new_department.department_id
where complain_no=in_complain_no and assign_type='DEPT';



INSERT INTO complain_all_activities ( 
      activity_id,
      date, 
      time, 
      user_id, 
      activity_type,activity_name ) 
SELECT complain_activities.id,`date`,`time`,`user_id`,
	 			`activity_type`,activity_name
	 			 FROM complain_activities	
                 inner join complain_activity_type on  complain_activity_type.id=complain_activities.activity_type
			  where complain_no=in_complain_no;
select activity_id,date,time,user_id,activity_type,activity_name,name as action_user,IF(role.Alias IS NULL ,role.role_name,role.Alias) as role_name from complain_all_activities at
left join user on  user.id=at.user_id
left join role on (user.userRole = role.role_id)
  order by date desc,time desc;
END$$
DELIMITER ;


-- 2024-09-02
DROP PROCEDURE IF EXISTS `GetIndividualsSuggestions`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetIndividualsSuggestions`(IN `complainantId` INT, IN `user_id` INT)
BEGIN

SELECT suggestion.suggestion_id,suggestion_text,suggestion_date,suggestion.suggestion_time,suggestion.quality_remark,sq.quality_remark_text,count(suggestion_file.id) as file_count,suggestion.management_comment
FROM suggestion

JOIN suggestion_quality_codes sq ON (suggestion.quality_remark = sq.quality_remark) 
left join suggestion_file on suggestion_file.suggestion_id=suggestion.suggestion_id

WHERE (case when complainantId>0 then suggestion.suggestion_asked_by = user_id or suggestion.suggestion_id=complainantId else suggestion.suggestion_asked_by = user_id END) 
GROUP by suggestion.suggestion_id
order by suggestion.suggestion_id DESC
;


END$$
DELIMITER ;


-- 2024-09-02
UPDATE `complain_activity_type` SET `activity_name` = 'Resolved' WHERE `complain_activity_type`.`id` = 8;


DROP PROCEDURE IF EXISTS `DepartmentTeamGet`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `DepartmentTeamGet`(IN `deptid` INT)
BEGIN
IF ( deptid>0 ) THEN
	SELECT dt.member_name,dt.member_contactno,dt.member_email,dt.working_area,dt.member_id,dt.department_id,d.department_name, building_users.building_id as assigned_buildings FROM 
	`department_team` dt JOIN `department` d USING ( department_id ) 
	left join user on user.relatedId = dt.member_id
    left join (SELECT user_id,GROUP_CONCAT(building_id) as building_id FROM `building_users` WHERE isActive=1 GROUP by user_id) building_users on building_users.user_id=user.id
	WHERE dt.department_id= deptid;
ELSE
	SELECT dt.member_name,dt.member_contactno,dt.member_email,dt.working_area,dt.member_id,dt.department_id,d.department_name, building_users.building_id as assigned_buildings FROM 
	`department_team` dt JOIN `department` d USING ( department_id )
	left join user on user.relatedId = dt.member_id
    left join (SELECT user_id,GROUP_CONCAT(building_id) as building_id FROM `building_users` WHERE isActive=1 GROUP by user_id) building_users on building_users.user_id=user.id;
END IF;
END$$
DELIMITER ;

-- Ayush
ALTER TABLE `building` ADD `is_automated_electric_switch` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0: false,1:true' AFTER `address`;

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'COMPLAIN', 'is_helper_allowed_self_acknowledge_complaint', 'Is Helper Allowed Self Acknowledge Complaint', 'boolean', '1', '1', '2024-09-02 17:01:38');

-- 2024-09-04

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'COMPLAIN', 'is_helper_allowed_self_assign_complain', 'Is Helper Allowed Self Assign Complain', 'boolean', '0', '1', '2024-09-03 17:01:38');

ALTER TABLE `complaints` ADD `complain_floor` VARCHAR(3) NULL DEFAULT NULL AFTER `residence_id`, ADD `complain_building_id` INT(11) NULL DEFAULT NULL AFTER `complain_floor`;

DROP PROCEDURE IF EXISTS `QuickComplain`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `QuickComplain` (IN `subjectId` INT, IN `description` TEXT, IN `complaint` TEXT, IN `deptid` INT, IN `complaindate` DATE, IN `complaintime` TIME, IN `complainantid` BIGINT(20), IN `teammemberid` INT, IN `isAnonymous` TINYINT(1), IN `sourceid` INT(11), IN `in_priority` INT(1), IN `in_residence_id` INT(11), IN `in_created_by` INT(11), IN `in_floor` VARCHAR(3), IN `in_building_id` INT(11), OUT `cid` BIGINT(20))   BEGIN

INSERT INTO `complaints`(subject_id,complaint,complaint_date,complaint_time,complaint_status,complainant_id,department_id,description,team_member_id,isAnonymous,complain_source_id,priority,residence_id,created_by,complain_floor,complain_building_id)
VALUES (subjectId,complaint,complaindate,complaintime,0,complainantid,deptid,description,teammemberid,isAnonymous,sourceid,in_priority,in_residence_id,in_created_by,in_floor,in_building_id);
SET cid = LAST_INSERT_ID(); 
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `GetComplainsByRole`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainsByRole`(IN `in_user_id` INT, IN `input_role_id` INT(3), IN `in_status` INT, IN `daterangearray` VARCHAR(500), IN `in_status_group` LONGTEXT, IN `in_dept_id` INT(11))
BEGIN

DECLARE is_self_assign INT DEFAULT 0;

SELECT COALESCE(MAX(rule_value), '0') INTO is_self_assign FROM settings WHERE rule_name = 'is_helper_allowed_self_assign_complain';

SET @sql = "SELECT complaint_view.* from complaint_view WHERE 1 =1 ";

IF ( in_status is not null) THEN
	SET @sql = CONCAT(@sql,' AND complaint_view.complain_status_id =',in_status);
END IF;

IF (in_status_group IS NOT NULL AND in_status_group != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.complain_status_id IN (', in_status_group, ')');
END IF;

if(in_dept_id>0) then 
	SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',in_dept_id);
END IF;

if (input_role_id>5) then 
	SET @sql = CONCAT(@sql, ' AND (building_id IN (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1)');
    SET @sql = CONCAT(@sql, ' OR complain_building_id IN (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1))');

    if(input_role_id=8) THEN
		set @department_id=(select relatedId from user where user.id=in_user_id AND user.userRole = 8 );
        if(@department_id>0) then 
			SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',@department_id);
        END IF;
	END IF;
	if(input_role_id=9) THEN
		set @team_member_id=(select relatedId from user where user.id=in_user_id AND user.userRole = 9 );
        IF is_self_assign != 1 THEN
            SET @sql = CONCAT(@sql, ' AND complaint_view.team_member_id = ', @team_member_id);
        ELSE
            SET @sql = CONCAT(@sql, ' AND (complaint_view.complain_status_id = 0 OR (complaint_view.complain_status_id != 0 AND complaint_view.team_member_id = ', @team_member_id, '))');
        END IF;
	END IF;
END IF;

IF ( daterangearray != '') THEN
	SET @sql = CONCAT(@sql,' AND complaint_date NOT BETWEEN ',daterangearray );
END IF;
SET @sql = CONCAT(@sql,' order by  complaint_view.complain_id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DROP VIEW IF EXISTS `complaint_view`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `complaint_view` AS
SELECT
    `complaints`.`complain_id` AS `complain_id`,
    `complaints`.`complainant_id` AS `complainant_id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`divyaang` AS `divyaang`,
    CASE
        WHEN `user`.`language` = 'EN' THEN `complain_subjects`.`subject_eng`
        ELSE `complain_subjects`.`subject_eng`
    END AS `complaint_subject`,
    `complaints`.`subject_id` AS `subject_id`,
    `complain_headers`.`header_id` AS `header_id`,
    `complain_headers`.`header` AS `header`,
    `complain_status`.`complain_status_name` AS `complaint_status`,
    `complain_status`.`complain_status_id` AS `complain_status_id`,
    `complaints`.`complaint_date` AS `complaint_date`,
    `complaints`.`complaint_time` AS `complaint_time`,
    `complaints`.`description` AS `description`,
    `complaints`.`remark` AS `remark`,
    `complaints`.`isAnonymous` AS `isAnonymous`,
    `complain_source`.`complain_source_text` AS `complain_source_text`,
    `complaints`.`complain_source_id` AS `complain_source_id`,
    `ca`.`assignDate` AS `assignDate`,
    `complaints`.`sla_id` AS `sla_id`,
    `complaints`.`priority` AS `priority`,
    `complain_priority`.`priority_desc` AS `priority_desc`,
    `complaints`.`latitude` AS `latitude`,
    `complaints`.`longitute` AS `longitute`,
    `complaints`.`scope` AS `scope`,
    `complain_scopes`.`scope_text` AS `scope_text`,
    `complaints`.`scope_reference` AS `scope_reference`,
    `user`.`userRole` AS `userRole`,
    `user`.`name` AS `created_by_name`,
    `role`.`role_name` AS `role_name`,
    `user`.`language` AS `language`,
    `complaints`.`complaint` AS `complaint`,
    `complainants_feedback`.`feedback_rating` AS `complainant_feedback`,
    `department`.`department_name` AS `department_name`,
    `complaints`.`department_id` AS `department_id`,
    `department_team`.`member_name` AS `member_name`,
    `complaints`.`team_member_id` AS `team_member_id`,
    `department_team`.`member_contactno` AS `member_contactno`,
    `department_team`.`member_email` AS `member_email`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `complaints`.`residence_id` AS `residence_id`,
    `complaints`.`complain_floor` AS `complain_floor`,
    `complaints`.`complain_building_id` AS `complain_building_id`,
    `building_residence`.`floor` AS `floor`,
    `building`.`name` AS `building_name`,
    `building_residence`.`building_id` AS `building_id`,
    `slot_info`.`slot_history_id` AS `multi_slot_history_id`,
    `slot_info`.`slot_id` AS `multi_slot_id`,
    `slot_info`.`slot_date` AS `multi_slot_date`,
    `slot_info`.`slot_start` AS `multi_slot_start`,
    `slot_info`.`slot_end` AS `multi_slot_end`,
    `complaint_slot_history`.`slot_date` AS `slot_date`,
    `work_slots`.`slot_start` AS `slot_start`,
    `work_slots`.`slot_end` AS `slot_end`
FROM
    `complaints`
left join `complain_subjects` on `complaints`.`subject_id` = `complain_subjects`.`subject_id`
left join `complain_headers` on `complain_headers`.`header_id` = `complain_subjects`.`header_id`
left join `complain_scopes` on `complaints`.`scope` = `complain_scopes`.`scope_id`
left join `department_team` on `complaints`.`team_member_id` = `department_team`.`member_id`
left join `user` on `complaints`.`created_by` = `user`.`id`
left join `role` on `role`.`role_id` = `user`.`userRole`
left join `complain_priority` on `complaints`.`priority` = `complain_priority`.`priority_id`
left join `complainants_feedback` on `complainants_feedback`.`complain_no` = `complaints`.`complain_id`
join `department` on `complaints`.`department_id` = `department`.`department_id`
left join `complainants` on `complaints`.`complainant_id` = `complainants`.`id`
join `complain_source` on `complaints`.`complain_source_id` = `complain_source`.`complain_source_id`
join `complain_status` on `complaints`.`complaint_status` = `complain_status`.`complain_status_id`
left join (
    select
        max(`complain_activities`.`date`) AS `assignDate`,
        `complain_activities`.`complain_no` AS `complain_no`
    from
        `complain_activities`
    where
        `complain_activities`.`activity_type` = 4
    group by
        `complain_activities`.`complain_no`
) `ca` on(`complaints`.`complain_id` = `ca`.`complain_no`)

left join `building_residence` on `building_residence`.`id` = `complaints`.`residence_id`
left join `building` on `building_residence`.`building_id` = `building`.`id`
LEFT JOIN (
	SELECT
		`complaint_slot_history`.`complaint_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_history_id`) AS `slot_history_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_id`) AS `slot_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_date`) AS `slot_date`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_start`, '')) AS `slot_start`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_end`, '')) AS `slot_end`
	FROM
		`complaint_slot_history`
	LEFT JOIN `work_slots` ON `work_slots`.`id` = `complaint_slot_history`.`slot_id`
	where `complaint_slot_history`.`isActive` = 1 
	GROUP BY
		`complaint_slot_history`.`complaint_id`
) `slot_info` ON `complaints`.`complain_id` = `slot_info`.`complaint_id`
left join `complaint_slot_history` on `complaint_slot_history`.`slot_history_id` = `complaints`.`slot_history_id`
left join `work_slots` on `work_slots`.`id` = `complaint_slot_history`.`slot_id`
ORDER BY
    `complaints`.`complain_id` DESC;

-- 2024-09-05
DROP PROCEDURE IF EXISTS `GetComplainClarification`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainClarification`(IN `complain_no` INT, IN `isPublic` BOOLEAN, IN `in_user_id` INT, IN `in_user_role` INT)
BEGIN
SET @sql = "SELECT cc.id,cc.clarification,cc.date,cc.time,cc.clarification_file,cc.file_ext,isPublic,(case when cc.user_id=0 then complainants.complainant_name else u.name end) as name,IF(role.Alias IS NULL ,role.role_name,role.Alias) as role_name  FROM `complain_clarification` cc ";
IF in_user_role IS NOT NULL AND in_user_role < 6 THEN
    SET @sql = CONCAT(@sql,' left join complaints on complaints.complain_id=cc.complain_id left join complainants on complainants.id=complaints.complainant_id');
ELSE
    SET @sql = CONCAT(@sql," inner join complaints on complaints.complain_id=cc.complain_id inner join complainants on complainants.id=complaints.complainant_id");
END IF;

SET @sql = CONCAT(@sql," LEFT JOIN `user` u ON (cc.user_id = u.id) LEFT JOIN `role` ON (u.userRole = role.role_id) WHERE  ");

SET @sql = CONCAT(@sql,' cc.complain_id=',complain_no);
IF ( isPublic > 0 ) THEN
        SET @sql = CONCAT(@sql,' AND cc.isPublic =',isPublic);
END IF;
SET @sql = CONCAT(@sql,' order by cc.id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;



DROP PROCEDURE IF EXISTS `GetComplainsByRole`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainsByRole`(IN `in_user_id` INT, IN `input_role_id` INT(3), IN `in_status` INT, IN `daterangearray` VARCHAR(500), IN `in_status_group` LONGTEXT, IN `in_dept_id` INT(11))
BEGIN

DECLARE is_self_assign INT DEFAULT 0;

SELECT COALESCE(MAX(rule_value), '0') INTO is_self_assign FROM settings WHERE rule_name = 'is_helper_allowed_self_assign_complain';

SET @sql = "SELECT complaint_view.* from complaint_view WHERE 1 =1 ";

IF ( in_status is not null) THEN
	SET @sql = CONCAT(@sql,' AND complaint_view.complain_status_id =',in_status);
END IF;

IF (in_status_group IS NOT NULL AND in_status_group != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.complain_status_id IN (', in_status_group, ')');
END IF;

if(in_dept_id>0) then 
	SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',in_dept_id);
END IF;

if (input_role_id>5) then 
	SET @sql = CONCAT(@sql, ' AND (complaint_view.scope = 5 OR ( (building_id IN (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1)');
    SET @sql = CONCAT(@sql, ' OR complain_building_id IN (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1))');

    if(input_role_id=8) THEN
		-- set @department_id=(select relatedId from user where user.id=in_user_id AND user.userRole = 8 );
		set @department_ids=(select IFNULL(dt.department_id,'') as department_id from user left join department_team dt on dt.member_id = user.relatedId where user.id=in_user_id AND user.userRole = 8);
        if @department_ids != '' then 
			-- SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',@department_id);
			SET @sql = CONCAT(@sql, ' AND FIND_IN_SET(complaint_view.department_id,',@department_ids,')');
        END IF;
	END IF;
	if(input_role_id=9) THEN
		set @team_member_id=(select relatedId from user where user.id=in_user_id AND user.userRole = 9 );
		set @department_ids=(select IFNULL(dt.department_id,'') as department_id from department_team dt where dt.member_id = @team_member_id);
        IF is_self_assign != 1 THEN
            SET @sql = CONCAT(@sql, ' AND complaint_view.team_member_id = ', @team_member_id);
        ELSE
			SET @sql = CONCAT(@sql, ' AND FIND_IN_SET(complaint_view.department_id,',@department_ids,')');
            SET @sql = CONCAT(@sql, ' AND (complaint_view.complain_status_id = 0 OR (complaint_view.complain_status_id != 0 AND complaint_view.team_member_id = ', @team_member_id, '))');
        END IF;
	END IF;
	SET @sql = CONCAT(@sql, ' ))');
END IF;

IF ( daterangearray != '') THEN
	SET @sql = CONCAT(@sql,' AND complaint_date NOT BETWEEN ',daterangearray );
END IF;
SET @sql = CONCAT(@sql,' order by  complaint_view.complain_id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DROP VIEW IF EXISTS `complaint_view`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `complaint_view` AS
SELECT
    `complaints`.`complain_id` AS `complain_id`,
    `complaints`.`complainant_id` AS `complainant_id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`divyaang` AS `divyaang`,
    CASE
        WHEN `user`.`language` = 'EN' THEN `complain_subjects`.`subject_eng`
        ELSE `complain_subjects`.`subject_eng`
    END AS `complaint_subject`,
    `complaints`.`subject_id` AS `subject_id`,
    `complain_headers`.`header_id` AS `header_id`,
    `complain_headers`.`header` AS `header`,
    `complain_status`.`complain_status_name` AS `complaint_status`,
    `complain_status`.`complain_status_id` AS `complain_status_id`,
    `complaints`.`complaint_date` AS `complaint_date`,
    `complaints`.`complaint_time` AS `complaint_time`,
    `complaints`.`description` AS `description`,
    `complaints`.`remark` AS `remark`,
    `complaints`.`isAnonymous` AS `isAnonymous`,
    `complain_source`.`complain_source_text` AS `complain_source_text`,
    `complaints`.`complain_source_id` AS `complain_source_id`,
    `ca`.`assignDate` AS `assignDate`,
    `complaints`.`sla_id` AS `sla_id`,
    `complaints`.`priority` AS `priority`,
    `complain_priority`.`priority_desc` AS `priority_desc`,
    `complaints`.`latitude` AS `latitude`,
    `complaints`.`longitute` AS `longitute`,
    `complaints`.`scope` AS `scope`,
    `complain_scopes`.`scope_text` AS `scope_text`,
    `complaints`.`scope_reference` AS `scope_reference`,
    `user`.`userRole` AS `userRole`,
    `user`.`name` AS `created_by_name`,
    IF(`role`.`Alias` IS NULL , `role`.`role_name`, `role`.`Alias` ) as role_name,
    `user`.`language` AS `language`,
    `complaints`.`complaint` AS `complaint`,
    `complainants_feedback`.`feedback_rating` AS `complainant_feedback`,
    `department`.`department_name` AS `department_name`,
    `complaints`.`department_id` AS `department_id`,
    `department_team`.`member_name` AS `member_name`,
    `complaints`.`team_member_id` AS `team_member_id`,
    `department_team`.`member_contactno` AS `member_contactno`,
    `department_team`.`member_email` AS `member_email`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `complaints`.`residence_id` AS `residence_id`,
    `complaints`.`complain_floor` AS `complain_floor`,
    `complaints`.`complain_building_id` AS `complain_building_id`,
    `building_residence`.`floor` AS `floor`,
    `building`.`name` AS `building_name`,
    `building_residence`.`building_id` AS `building_id`,
    `slot_info`.`slot_history_id` AS `multi_slot_history_id`,
    `slot_info`.`slot_id` AS `multi_slot_id`,
    `slot_info`.`slot_date` AS `multi_slot_date`,
    `slot_info`.`slot_start` AS `multi_slot_start`,
    `slot_info`.`slot_end` AS `multi_slot_end`,
    `complaint_slot_history`.`slot_date` AS `slot_date`,
    `work_slots`.`slot_start` AS `slot_start`,
    `work_slots`.`slot_end` AS `slot_end`
FROM
    `complaints`
left join `complain_subjects` on `complaints`.`subject_id` = `complain_subjects`.`subject_id`
left join `complain_headers` on `complain_headers`.`header_id` = `complain_subjects`.`header_id`
left join `complain_scopes` on `complaints`.`scope` = `complain_scopes`.`scope_id`
left join `department_team` on `complaints`.`team_member_id` = `department_team`.`member_id`
left join `user` on `complaints`.`created_by` = `user`.`id`
left join `role` on `role`.`role_id` = `user`.`userRole`
left join `complain_priority` on `complaints`.`priority` = `complain_priority`.`priority_id`
left join `complainants_feedback` on `complainants_feedback`.`complain_no` = `complaints`.`complain_id`
join `department` on `complaints`.`department_id` = `department`.`department_id`
left join `complainants` on `complaints`.`complainant_id` = `complainants`.`id`
join `complain_source` on `complaints`.`complain_source_id` = `complain_source`.`complain_source_id`
join `complain_status` on `complaints`.`complaint_status` = `complain_status`.`complain_status_id`
left join (
    select
        max(`complain_activities`.`date`) AS `assignDate`,
        `complain_activities`.`complain_no` AS `complain_no`
    from
        `complain_activities`
    where
        `complain_activities`.`activity_type` = 4
    group by
        `complain_activities`.`complain_no`
) `ca` on(`complaints`.`complain_id` = `ca`.`complain_no`)
left join `building_residence` on `building_residence`.`id` = `complaints`.`residence_id`
left join `building` on (`building_residence`.`building_id` = `building`.`id` OR `complaints`.`complain_building_id` = `building`.`id` )
LEFT JOIN (
	SELECT
		`complaint_slot_history`.`complaint_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_history_id`) AS `slot_history_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_id`) AS `slot_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_date`) AS `slot_date`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_start`, '')) AS `slot_start`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_end`, '')) AS `slot_end`
	FROM
		`complaint_slot_history`
	LEFT JOIN `work_slots` ON `work_slots`.`id` = `complaint_slot_history`.`slot_id`
	where `complaint_slot_history`.`isActive` = 1 
	GROUP BY
		`complaint_slot_history`.`complaint_id`
) `slot_info` ON `complaints`.`complain_id` = `slot_info`.`complaint_id`
left join `complaint_slot_history` on `complaint_slot_history`.`slot_history_id` = `complaints`.`slot_history_id`
left join `work_slots` on `work_slots`.`id` = `complaint_slot_history`.`slot_id`
ORDER BY
    `complaints`.`complain_id` DESC;

-- from bishnu sir 
ALTER TABLE `complain_subjects` ADD `building_ids` VARCHAR(200) NULL DEFAULT NULL AFTER `scope`;

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES (NULL, 'PROFILE', 'is_profile_pic_editable_by_resident_from_gallery ', 'Is Student allowed to upload his pic from gallery ', 'boolean', '1', '1', '2024-06-11 17:56:19');
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES (NULL, 'COMPLAIN', 'is_resident_allowed_to_raise_community_complain', 'Is resident allowed to raise community complain Complain', 'boolean', '0', '1', '2024-09-04 19:32:24');
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES (NULL, 'COMPLAIN', 'is_resident_allowed_to_raise_building_complain', 'Is resident allowed to raise building complain Complain', 'boolean', '0', '1', '2024-09-04 19:32:24');



CREATE view  resident_kyc_details_view as
SELECT c.id,c.complainant_name,c.contact_no,res_kyc.name as doc_name,res_kyc.id as doc_id,res_kyc.file_name,res_kyc.created_at as uploaded_at,user.name as uploaded_by,is_activated as resident_status
FROM complainants as c
LEFT JOIN 
(SELECT resident_id,uploaded_by,resident_kyc.created_at,file_name,ext,resident_profile_settings.name,resident_profile_settings.id FROM `resident_kyc` 
INNER JOIN resident_profile_settings on resident_kyc.resident_profile_setting_id=resident_profile_settings.id)as res_kyc
on c.id=res_kyc.resident_id
LEFT JOIN `user` on res_kyc.uploaded_by=`user`.`id`
WHERE c.is_activated in ('A','S');



DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetKycDocumentCounts`()
BEGIN
    DECLARE resident_count INT;

    -- Initialize the resident_count
    SET resident_count = (SELECT COUNT(*) FROM complainants WHERE is_activated = 1);

    -- Perform the main query
    SELECT rps.id AS document_id, 
           rps.name AS document_type, 
           COUNT(rkdv.id) AS document_submitted_count,
           (resident_count - COUNT(rkdv.id)) AS document_pending_count
    FROM resident_profile_settings rps
    LEFT JOIN resident_kyc_details_view rkdv 
    ON rps.id = rkdv.doc_id
    WHERE rps.isActive = 1
    AND rps.type = 'doc'
    GROUP BY rps.id, rps.name;
END$$
DELIMITER ;


DROP PROCEDURE IF EXISTS `DepartmentTeamGet`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `DepartmentTeamGet`(IN `deptid` INT, IN `in_user_role` INT)
BEGIN
SET @sql = "SELECT dt.member_name,dt.member_contactno,dt.member_email,dt.working_area,dt.member_id,dt.department_id,dt.department_name, bu.building_ids as assigned_buildings, bu.building_names,user.userRole FROM 
	(
    SELECT
        department_team.member_id, department_team.department_id, department_team.member_name, department_team.member_contactno, department_team.member_email, department_team.working_area,
        GROUP_CONCAT(d.department_name SEPARATOR ', ') AS department_name
    FROM
        department_team LEFT JOIN department d ON FIND_IN_SET(d.department_id, department_team.department_id) > 0
    GROUP BY
        department_team.member_id, department_team.department_id, department_team.member_name, department_team.member_contactno, department_team.member_email, department_team.working_area
    ) dt 
	left join user on user.relatedId = dt.member_id
    left join (SELECT user_id,GROUP_CONCAT(building_id) as building_ids, GROUP_CONCAT(IFNULL(building.name,'')) as building_names FROM `building_users` left join building on building.id = building_users.building_id WHERE isActive=1 GROUP by building_users.user_id)  `bu` ON `bu`.`user_id` = `user`.`id` WHERE 1=1 ";

IF in_user_role IS NOT NULL AND in_user_role > 0 THEN
    SET @sql = CONCAT(@sql, ' AND user.userRole = ', in_user_role);
END IF;

IF deptid IS NOT NULL AND deptid > 0 THEN
    SET @sql = CONCAT(@sql, ' AND FIND_IN_SET(', deptid, ', dt.department_id)');
END IF;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 2024-09-09

DROP PROCEDURE IF EXISTS `getNoticeBoard`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getNoticeBoard`(
    IN `in_type` ENUM('all','community','building','academic','residence','resident'),
    IN `in_reference_id` BIGINT,
    IN `from_dt` DATE,
    IN `to_dt` DATE,
    IN `in_old_notice_days` INT, 
    IN `in_building_ids` VARCHAR(50))
    NO SQL
BEGIN
DECLARE residenceIdsString VARCHAR(1000);
    -- Case: Fetch all notices
    IF (in_type = 'all') THEN
        SELECT noticeboard.*, 
               COUNT(notice_board_file.id) AS files_count, 
               user.name AS publisher, 
               IF(Alias IS NULL ,role.role_name,role.Alias) as role_name, 
               building.name AS building_name, 
               building.id AS building_id, 
               building_residence.apartment_name, 
               building_residence.id AS residence_id 
        FROM noticeboard
        LEFT JOIN building_residence 
               ON (noticeboard.recipient = 'residence' AND noticeboard.relatedid = building_residence.id)
        LEFT JOIN building 
               ON ((noticeboard.recipient = 'building' AND noticeboard.relatedid = building.id) OR 
                   (noticeboard.recipient = 'residence' AND building_residence.building_id = building.id))
        INNER JOIN user 
               ON user.id = noticeboard.published_by
        LEFT JOIN role 
               ON role.role_id = user.userRole
        LEFT JOIN notice_board_file 
               ON notice_board_file.notice_id = noticeboard.id
        WHERE noticeboard.publish_at IS NULL OR 
              (DATE(noticeboard.created_at) BETWEEN from_dt AND to_dt)
        GROUP BY noticeboard.id
        ORDER BY noticeboard.id DESC;

    -- Case: Fetch notices specific to a building
    ELSEIF (in_type = 'building') THEN
        SELECT noticeboard.*, 
               COUNT(notice_board_file.id) AS files_count, 
               user.name AS publisher, 
               IF(Alias IS NULL ,role.role_name,role.Alias) as role_name, 
               building.name AS building_name, 
               building.id AS building_id, 
               building_residence.apartment_name, 
               building_residence.id AS residence_id 
        FROM noticeboard
        LEFT JOIN building 
               ON (noticeboard.recipient = 'building' AND noticeboard.relatedid = building.id)
        LEFT JOIN building_residence 
               ON building_residence.building_id = building.id
        INNER JOIN user 
               ON user.id = noticeboard.published_by
        LEFT JOIN role 
               ON role.role_id = user.userRole
        LEFT JOIN notice_board_file 
               ON notice_board_file.notice_id = noticeboard.id
        WHERE noticeboard.publish_at IS NULL OR 
              (DATE(noticeboard.created_at) BETWEEN from_dt AND to_dt)
        GROUP BY noticeboard.id
        ORDER BY noticeboard.id DESC;
    -- Case: Fetch notices specific to a resident
    ELSEIF (in_type = 'resident') THEN
        SET @resident_id = (SELECT user.relatedId FROM user WHERE user.id = in_reference_id);
        SELECT complainant_view.residence_id, complainant_view.gender, complainant_view.resident_status, complainant_view.building_id 
        INTO @residence_id, @gender, @resident_status, @building_id 
        FROM complainant_view 
        WHERE complainant_view.id = @resident_id;

        SELECT resident_academic_details.course, resident_academic_details.branch, resident_academic_details.course_year 
        INTO @course, @branch, @course_year 
        FROM resident_academic_details 
        WHERE resident_academic_details.resident_id = @resident_id;

        SELECT noticeboard.id, noticeboard.subject, noticeboard.body, noticeboard.created_at, noticeboard.gender, noticeboard.updated_at, noticeboard.valid_till_date, 
               noticeboard.publish_at, noticeboard.event_at, noticeboard.recipient, noticeboard.relatedid, 
               noticeboard.academic_filter, noticeboard.academic_course_year, 
               IFNULL(JSON_EXISTS(noticeboard.read_by, CONCAT('$.', in_reference_id)), 0) AS isRead, 
               user.name AS publisher, IF(Alias IS NULL ,role.role_name,role.Alias) as role_name, COUNT(notice_board_file.id) AS files_count 
        FROM noticeboard
        INNER JOIN user ON user.id = noticeboard.published_by
        LEFT JOIN role ON role.role_id = user.userRole
        LEFT JOIN notice_board_file ON notice_board_file.notice_id = noticeboard.id
        LEFT JOIN resident_academic_details ON resident_academic_details.resident_id = @resident_id
        WHERE noticeboard.stage = 'published' 
        AND DATEDIFF(CURRENT_TIMESTAMP, noticeboard.publish_at) < in_old_notice_days 
        AND (noticeboard.recipient = 'community' OR 
             (noticeboard.recipient = 'tenant' AND @resident_status IN (3, 5, 6)) OR 
             (noticeboard.recipient = 'building' AND @building_id IS NOT NULL AND FIND_IN_SET(@building_id, noticeboard.relatedid)) OR 
             (noticeboard.recipient = 'residence' AND @residence_id IS NOT NULL AND FIND_IN_SET(@residence_id, noticeboard.relatedid)) OR 
             (noticeboard.recipient = 'academic' AND academic_filter = 'course' AND @course IS NOT NULL AND FIND_IN_SET(@course, noticeboard.relatedid) 
              AND noticeboard.academic_course_year IS NULL) OR 
             (noticeboard.recipient = 'academic' AND academic_filter = 'branch' AND @branch IS NOT NULL AND FIND_IN_SET(@branch, noticeboard.relatedid) 
              AND noticeboard.academic_course_year IS NULL) OR 
             (noticeboard.recipient = 'academic' AND academic_filter = 'course' AND @course IS NOT NULL AND FIND_IN_SET(@course, noticeboard.relatedid) 
              AND noticeboard.academic_course_year IS NOT NULL AND @course_year IS NOT NULL AND FIND_IN_SET(@course_year, noticeboard.academic_course_year)) OR 
             (noticeboard.recipient = 'academic' AND academic_filter = 'branch' AND @branch IS NOT NULL AND FIND_IN_SET(@branch, noticeboard.relatedid) 
              AND noticeboard.academic_course_year IS NOT NULL AND @course_year IS NOT NULL AND FIND_IN_SET(@course_year, noticeboard.academic_course_year))
        ) AND (noticeboard.gender='b' OR noticeboard.gender=@gender) AND (noticeboard.valid_till_date IS NULL OR noticeboard.valid_till_date>=CURDATE())
        GROUP BY noticeboard.id
        ORDER BY noticeboard.id DESC;

    -- Case: Fetch notices for the community
    ELSEIF (in_type = 'community') THEN
        SELECT noticeboard.id, noticeboard.subject, noticeboard.body, noticeboard.created_at, noticeboard.updated_at, 
               noticeboard.event_at, noticeboard.recipient, 
               IFNULL(JSON_EXISTS(noticeboard.read_by, CONCAT('$.', in_reference_id)), 0) AS isRead, 
               user.name AS publisher, IF(Alias IS NULL ,role.role_name,role.Alias) as role_name, COUNT(notice_board_file.id) AS files_count 
        FROM noticeboard
        INNER JOIN user ON user.id = noticeboard.published_by
        LEFT JOIN role ON role.role_id = user.userRole
        LEFT JOIN notice_board_file ON notice_board_file.notice_id = noticeboard.id
        WHERE noticeboard.stage = 'published' 
        AND noticeboard.recipient = 'community'  
        AND DATE(noticeboard.created_at) BETWEEN from_dt AND to_dt 
        GROUP BY noticeboard.id
        ORDER BY noticeboard.id DESC;
    END IF;

END$$
DELIMITER ;



CREATE DEFINER=`root`@`localhost` PROCEDURE `registerUserMobileApp` (IN `app_key` VARCHAR(400), IN `in_device_id` VARCHAR(100), IN `uid` BIGINT, IN `in_device` ENUM('A','I'))  NO SQL BEGIN


set @userRole=(SELECT user.userRole from user where user.id=uid);
if(@userRole=11) then
	set @isExisting=(SELECT COUNT(id) from user_app_registration where user_app_registration.user_id=uid and user_app_registration.device_id=in_device_id);
    if(@isExisting=0) then 
    	insert into user_app_registration (app_reg_key,device_id,device,user_id,registraion_date,isActive,registration_time) VALUES(app_key,in_device_id,in_device,uid,CURRENT_DATE,1,CURRENT_TIME);
    else 
    	UPDATE user_app_registration set user_app_registration.isActive=1 ,user_app_registration.app_reg_key=app_key,user_app_registration.registraion_date=CURRENT_DATE,user_app_registration.registration_time=CURRENT_TIME where user_app_registration.user_id=uid and user_app_registration.device_id=in_device_id order by user_app_registration.id DESC LIMIT 1;
    end if;
ELSE
	set @existing_count=(select COUNT(user_app_registration.id) from user_app_registration WHERE user_app_registration.app_reg_key=app_key and user_app_registration.device_id=in_device_id);
    if (@existing_count=0) then
    INSERT INTO `user_app_registration` (`id`, `app_reg_key`, `device_id`,`device`, `user_id`, `registraion_date`, `isActive`, `registration_time`) VALUES (NULL, app_key, in_device_id, 'A',uid, CURRENT_DATE, '1', CURRENT_TIME);
    else
        update user_app_registration set user_app_registration.user_id = uid, user_app_registration.isActive=1 where user_app_registration.app_reg_key=app_key and user_app_registration.device_id=in_device_id order by user_app_registration.id DESC LIMIT 1; 
        -- update user_app_registration set user_app_registration.isActive=1 where user_app_registration.app_reg_key=app_key and user_app_registration.device_id=in_device_id order by user_app_registration.id DESC LIMIT 1;
    end if;
end if;


END$$
DELIMITER ;

DROP VIEW IF EXISTS `resident_kyc_details_view`;

CREATE view  resident_kyc_details_view as
SELECT c.id,c.complainant_name,c.contact_no,res_kyc.name as doc_name,res_kyc.id as doc_id,res_kyc.file_name,res_kyc.created_at as uploaded_at,user.name as uploaded_by,is_activated as resident_status,
building_residence.building_id AS building_id
FROM complainants as c
LEFT JOIN 
(SELECT resident_id,uploaded_by,resident_kyc.created_at,file_name,ext,resident_profile_settings.name,resident_profile_settings.id FROM `resident_kyc` 
INNER JOIN resident_profile_settings on resident_kyc.resident_profile_setting_id=resident_profile_settings.id)as res_kyc
on c.id=res_kyc.resident_id
LEFT JOIN `user` on res_kyc.uploaded_by=`user`.`id`
LEFT JOIN resident_residences ON resident_residences.resident_id = c.id AND resident_residences.isActive = 1
LEFT JOIN building_residence ON resident_residences.residence_id = building_residence.id
LEFT JOIN building ON building.id = building_residence.building_id
WHERE c.is_activated in ('A','S');

DROP PROCEDURE IF EXISTS `GetKycDocumentCounts`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetKycDocumentCounts`(IN `in_building_id` INT)
BEGIN
    DECLARE resident_count INT;

    -- Initialize the resident_count
    IF in_building_id IS NOT NULL AND in_building_id > 0 THEN
        SET resident_count = (SELECT COUNT(*) FROM complainant_view WHERE is_activated = 1 AND building_id = in_building_id);
    ELSE
        SET resident_count = (SELECT COUNT(*) FROM complainant_view WHERE is_activated = 1);
    END IF;

    -- Perform the main query
    SET @sql = CONCAT("SELECT rps.id AS document_id, 
           rps.name AS document_type, 
           COUNT(rkdv.id) AS document_submitted_count,
           (", resident_count, " - COUNT(rkdv.id)) AS document_pending_count
    FROM resident_profile_settings rps
    LEFT JOIN resident_kyc_details_view rkdv 
    ON rps.id = rkdv.doc_id");

    IF in_building_id IS NOT NULL AND in_building_id > 0 THEN
        SET @sql = CONCAT(@sql, ' AND rkdv.building_id = ', in_building_id);
    END IF;

    SET @sql = CONCAT(@sql, " WHERE rps.isActive = 1 AND rps.type = 'doc' GROUP BY rps.id, rps.name;");

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;


DROP PROCEDURE IF EXISTS `getActiveTokenUsers`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getActiveTokenUsers`()
SELECT name,email,phoneNumber,userRole,IF(role.Alias IS NULL ,role.role_name,role.Alias) as role_name FROM `user` INNER JOIN role ON role.role_id = user.userRole WHERE id IN(SELECT user_id FROM `oauth_access_tokens` WHERE revoked=0 AND expires_at>CURRENT_TIMESTAMP)$$
DELIMITER ;




DROP PROCEDURE IF EXISTS `getAllComplaintStats`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllComplaintStats`(IN `in_user_id` INT, IN `input_role_id` INT(3))
    NO SQL
BEGIN 

DECLARE is_self_assign INT DEFAULT 0;
DECLARE department_ids VARCHAR(500) DEFAULT '';
DECLARE team_member_id INT DEFAULT 0;

SET @sql = "SELECT COALESCE(count(complain_id), 0) as total_complaints,
	COALESCE(SUM(CASE WHEN complaint_status=0  THEN 1 ELSE 0 END), 0) AS 'pending_complaints',
	COALESCE(SUM(CASE WHEN complaint_status=1 THEN 1 ELSE 0 END), 0) AS 'complete_complaints' ,
	COALESCE(SUM(CASE WHEN complaint_status=2 THEN 1 ELSE 0 END), 0) AS 'reopen_complaints' ,
	COALESCE(SUM(CASE WHEN complaint_status=3 THEN 1 ELSE 0 END), 0) AS 'closed_complaints',
    COALESCE(SUM(CASE WHEN complaint_status=5 THEN 1 ELSE 0 END), 0) AS 'rejected_complaints',
    COALESCE(SUM(CASE WHEN complaint_status=6 THEN 1 ELSE 0 END), 0) AS 'work_assigned_complaints',
    COALESCE(SUM(CASE WHEN complaint_status=7 THEN 1 ELSE 0 END), 0) AS 'work_accepted_complaints',
	COALESCE(SUM(CASE WHEN complaint_status=4 THEN 1 ELSE 0 END), 0) AS 'long_term_complaints'	FROM `complaints` ";

IF input_role_id > 5 then 
    SET @sql = CONCAT(@sql, " left join building_residence on building_residence.id=complaints.residence_id where 
    (complaints.scope = 5 OR ( (building_residence.building_id in (SELECT building_users.building_id FROM building_users where building_users.user_id=", in_user_id, " and building_users.isActive=1))
    OR (complain_building_id IN (SELECT building_users.building_id FROM building_users where building_users.user_id=", in_user_id, " and building_users.isActive=1) ) ) )
    ");

    IF input_role_id=8 THEN
    -- service head
        SET @department_ids=(select IFNULL(dt.department_id,'') as department_id from user left join department_team dt on dt.member_id = user.relatedId where user.id=in_user_id AND user.userRole = 8);
        SET @sql = CONCAT(@sql, " AND FIND_IN_SET(complaints.department_id,'", @department_ids, "')");
    ELSEIF input_role_id=9 THEN
    -- helper
        SET @is_self_assign=(SELECT COALESCE(MAX(rule_value), '0') FROM settings WHERE rule_name = 'is_helper_allowed_self_assign_complain');
        SET @team_member_id=(select relatedId from user where user.id=in_user_id AND user.userRole = 9 );   
        SET @department_ids=(select IFNULL(dt.department_id,'') as department_id from department_team dt where dt.member_id = @team_member_id);
        IF is_self_assign != 1 THEN
            SET @sql = CONCAT(@sql, ' AND complaints.team_member_id = ', @team_member_id);
        ELSE
            SET @sql = CONCAT(@sql, " AND FIND_IN_SET(complaints.department_id,'", @department_ids, "')");
            SET @sql = CONCAT(@sql, ' AND (complaints.complain_status_id = 0 OR (complaints.complain_status_id != 0 AND complaints.team_member_id = ', @team_member_id, '))');
        END IF;
    END IF;
END IF;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;




DROP PROCEDURE IF EXISTS `GetComplainsByRole`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainsByRole`(IN `in_user_id` INT, IN `input_role_id` INT(3), IN `in_status` INT, IN `daterangearray` VARCHAR(500), IN `in_status_group` LONGTEXT, IN `in_dept_id` INT(11))
BEGIN

DECLARE is_self_assign INT DEFAULT 0;
DECLARE department_ids VARCHAR(500) DEFAULT '';
DECLARE team_member_id INT DEFAULT 0;

SELECT COALESCE(MAX(rule_value), '0') INTO is_self_assign FROM settings WHERE rule_name = 'is_helper_allowed_self_assign_complain';

SET @sql = "SELECT complaint_view.* from complaint_view WHERE 1 =1 ";

IF ( in_status is not null) THEN
	SET @sql = CONCAT(@sql,' AND complaint_view.complain_status_id =',in_status);
END IF;

IF (in_status_group IS NOT NULL AND in_status_group != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.complain_status_id IN (', in_status_group, ')');
END IF;

if(in_dept_id>0) then 
	SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',in_dept_id);
END IF;

if (input_role_id>5) then 
	SET @sql = CONCAT(@sql, ' AND (complaint_view.scope = 5 OR ( (building_id IN (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1)');
    SET @sql = CONCAT(@sql, ' OR complain_building_id IN (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1))');

    if(input_role_id=8) THEN
		-- set @department_id=(select relatedId from user where user.id=in_user_id AND user.userRole = 8 );
		set @department_ids=(select IFNULL(dt.department_id,'') as department_id from user left join department_team dt on dt.member_id = user.relatedId where user.id=in_user_id AND user.userRole = 8);
        if @department_ids != '' then 
			-- SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',@department_id);
			SET @sql = CONCAT(@sql, " AND FIND_IN_SET(complaint_view.department_id,'", @department_ids, "') ");
        END IF;
	END IF;
	if(input_role_id=9) THEN
		set @team_member_id=(select relatedId from user where user.id=in_user_id AND user.userRole = 9 );
		set @department_ids=(select IFNULL(dt.department_id,'') as department_id from department_team dt where dt.member_id = @team_member_id);
        IF is_self_assign != 1 THEN
            SET @sql = CONCAT(@sql, ' AND complaint_view.team_member_id = ', @team_member_id);
        ELSE
			SET @sql = CONCAT(@sql, " AND FIND_IN_SET(complaint_view.department_id,'", @department_ids, "') ");
            SET @sql = CONCAT(@sql, ' AND (complaint_view.complain_status_id = 0 OR (complaint_view.complain_status_id != 0 AND complaint_view.team_member_id = ', @team_member_id, '))');
        END IF;
	END IF;
	SET @sql = CONCAT(@sql, ' ))');
END IF;

IF ( daterangearray != '') THEN
	SET @sql = CONCAT(@sql,' AND complaint_date NOT BETWEEN ',daterangearray );
END IF;
SET @sql = CONCAT(@sql,' order by  complaint_view.complain_id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;


DROP PROCEDURE IF EXISTS `getDeptHeadMobileAppRegistration`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getDeptHeadMobileAppRegistration` (IN `in_dept_id` INT(5))  NO SQL SELECT app_reg_key FROM user_app_registration WHERE user_id IN ( SELECT u.id FROM user u INNER JOIN department_team dt ON u.relatedId = dt.member_id WHERE FIND_IN_SET(in_dept_id, dt.department_id) > 0 AND u.userRole = 8) AND isActive = 1$$
DELIMITER ;


ALTER VIEW `complaint_view` AS
SELECT
    `complaints`.`complain_id` AS `complain_id`,
    `complaints`.`complainant_id` AS `complainant_id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`divyaang` AS `divyaang`,
    `complaints`.`complain_type`, 
    `complaints`.`complain_facility_id`, 
    `common_facilities`.`facility_name`, 
    `common_facilities`.`building_ids` AS `facility_building_ids`,
    CASE
        WHEN `user`.`language` = 'EN' THEN `complain_subjects`.`subject_eng`
        ELSE `complain_subjects`.`subject_eng`
    END AS `complaint_subject`,
    `complaints`.`subject_id` AS `subject_id`,
    `complain_headers`.`header_id` AS `header_id`,
    `complain_headers`.`header` AS `header`,
    `complain_status`.`complain_status_name` AS `complaint_status`,
    `complain_status`.`complain_status_id` AS `complain_status_id`,
    `complaints`.`complaint_date` AS `complaint_date`,
    `complaints`.`complaint_time` AS `complaint_time`,
    `complaints`.`description` AS `description`,
    `complaints`.`remark` AS `remark`,
    `complaints`.`isAnonymous` AS `isAnonymous`,
    `complain_source`.`complain_source_text` AS `complain_source_text`,
    `complaints`.`complain_source_id` AS `complain_source_id`,
    `ca`.`assignDate` AS `assignDate`,
    `complaints`.`sla_id` AS `sla_id`,
    `complaints`.`priority` AS `priority`,
    `complain_priority`.`priority_desc` AS `priority_desc`,
    `complaints`.`latitude` AS `latitude`,
    `complaints`.`longitute` AS `longitute`,
    `complaints`.`scope` AS `scope`,
    `complain_scopes`.`scope_text` AS `scope_text`,
    `complaints`.`scope_reference` AS `scope_reference`,
    `user`.`userRole` AS `userRole`,
    `user`.`name` AS `created_by_name`,
    IF(`role`.`Alias` IS NULL , `role`.`role_name`, `role`.`Alias` ) as role_name,
    `user`.`language` AS `language`,
    `complaints`.`complaint` AS `complaint`,
    `complainants_feedback`.`feedback_rating` AS `complainant_feedback`,
    `department`.`department_name` AS `department_name`,
    `complaints`.`department_id` AS `department_id`,
    `department_team`.`member_name` AS `member_name`,
    `complaints`.`team_member_id` AS `team_member_id`,
    `department_team`.`member_contactno` AS `member_contactno`,
    `department_team`.`member_email` AS `member_email`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `complaints`.`residence_id` AS `residence_id`,
    `complaints`.`complain_floor` AS `complain_floor`,
    `complaints`.`complain_building_id` AS `complain_building_id`,
    `building_residence`.`floor` AS `floor`,
    `building`.`name` AS `building_name`,
    `building_residence`.`building_id` AS `building_id`,
    `slot_info`.`slot_history_id` AS `multi_slot_history_id`,
    `slot_info`.`slot_id` AS `multi_slot_id`,
    `slot_info`.`slot_date` AS `multi_slot_date`,
    `slot_info`.`slot_start` AS `multi_slot_start`,
    `slot_info`.`slot_end` AS `multi_slot_end`,
    `complaint_slot_history`.`slot_date` AS `slot_date`,
    `work_slots`.`slot_start` AS `slot_start`,
    `work_slots`.`slot_end` AS `slot_end`
FROM
    `complaints`
left join `complain_subjects` on `complaints`.`subject_id` = `complain_subjects`.`subject_id`
left join `complain_headers` on `complain_headers`.`header_id` = `complain_subjects`.`header_id`
left join `complain_scopes` on `complaints`.`scope` = `complain_scopes`.`scope_id`
left join `department_team` on `complaints`.`team_member_id` = `department_team`.`member_id`
left join `user` on `complaints`.`created_by` = `user`.`id`
left join `role` on `role`.`role_id` = `user`.`userRole`
left join `complain_priority` on `complaints`.`priority` = `complain_priority`.`priority_id`
left join `complainants_feedback` on `complainants_feedback`.`complain_no` = `complaints`.`complain_id`
join `department` on `complaints`.`department_id` = `department`.`department_id`
left join `complainants` on `complaints`.`complainant_id` = `complainants`.`id`
join `complain_source` on `complaints`.`complain_source_id` = `complain_source`.`complain_source_id`
join `complain_status` on `complaints`.`complaint_status` = `complain_status`.`complain_status_id`
left join (
    select
        max(`complain_activities`.`date`) AS `assignDate`,
        `complain_activities`.`complain_no` AS `complain_no`
    from
        `complain_activities`
    where
        `complain_activities`.`activity_type` = 4
    group by
        `complain_activities`.`complain_no`
) `ca` on(`complaints`.`complain_id` = `ca`.`complain_no`)
left join `building_residence` on `building_residence`.`id` = `complaints`.`residence_id`
left join `building` on (`building_residence`.`building_id` = `building`.`id` OR `complaints`.`complain_building_id` = `building`.`id` )
LEFT JOIN (
	SELECT
		`complaint_slot_history`.`complaint_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_history_id`) AS `slot_history_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_id`) AS `slot_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_date`) AS `slot_date`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_start`, '')) AS `slot_start`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_end`, '')) AS `slot_end`
	FROM
		`complaint_slot_history`
	LEFT JOIN `work_slots` ON `work_slots`.`id` = `complaint_slot_history`.`slot_id`
	where `complaint_slot_history`.`isActive` = 1 
	GROUP BY
		`complaint_slot_history`.`complaint_id`
) `slot_info` ON `complaints`.`complain_id` = `slot_info`.`complaint_id`
left join `complaint_slot_history` on `complaint_slot_history`.`slot_history_id` = `complaints`.`slot_history_id`
left join `work_slots` on `work_slots`.`id` = `complaint_slot_history`.`slot_id`
left join `common_facilities` on `complaints`.`complain_facility_id` = `common_facilities`.`id`
ORDER BY
    `complaints`.`complain_id` DESC;


DROP PROCEDURE IF EXISTS `GetComplainsByRoleDeptAndHeader`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainsByRoleDeptAndHeader`(IN `in_user_id` INT, IN `input_role_id` INT(3), IN `in_status` INT, IN `daterangearray` VARCHAR(500), IN `in_status_group` LONGTEXT, IN `in_dept_id` INT(11), IN `in_header_id` INT(11))
BEGIN

SET @sql = "SELECT complaint_view.* from complaint_view WHERE 1 =1 ";

IF (in_status_group IS NOT NULL AND in_status_group != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.complain_status_id IN (', in_status_group, ')');
END IF;

if(in_dept_id>0) then 
	SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',in_dept_id);
END IF;

if(in_header_id>0) then 
	SET @sql = CONCAT(@sql,' AND complaint_view.header_id =',in_header_id);
END IF;

SET @sql = CONCAT(@sql,' order by  complaint_view.complain_id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;





DROP PROCEDURE IF EXISTS `GetResidentKycWithFilters`;

DELIMITER $$

CREATE PROCEDURE `GetResidentKycWithFilters` (
    IN `in_building_id` INT,
    IN `in_document_id` INT,
    IN `in_filter_type` ENUM('all', 'submitted', 'not_submitted')
)
BEGIN
    SET @sql = CONCAT("
        SELECT 
            cv.id AS resident_id,
            cv.complainant_name AS name,
            cv.contact_no,
            cv.apartment_name,
            cv.building_id,
            cv.building_name,
            rps.id AS document_id,
            rps.name AS document_type,
            rk.id AS kyc_id,
            rk.file_name,
            rk.ext,
            rk.created_at AS uploaded_at,
            u.name AS uploaded_by
        FROM complainant_view cv
        CROSS JOIN resident_profile_settings rps
        LEFT JOIN resident_kyc rk ON cv.id = rk.resident_id AND rps.id = rk.resident_profile_setting_id AND rk.isActive = 1
        LEFT JOIN user u ON u.id = rk.uploaded_by
        WHERE cv.is_activated = 1 AND rps.isActive = 1 AND rps.type = 'doc'");

    IF in_building_id IS NOT NULL AND in_building_id > 0 THEN
        SET @sql = CONCAT(@sql, " AND cv.building_id = ", in_building_id);
    END IF;

    IF in_document_id IS NOT NULL AND in_document_id > 0 THEN
        SET @sql = CONCAT(@sql, " AND rps.id = ", in_document_id);
    END IF;

    CASE in_filter_type
        WHEN 'submitted' THEN
            SET @sql = CONCAT(@sql, " AND rk.id IS NOT NULL");
        WHEN 'not_submitted' THEN
            SET @sql = CONCAT(@sql, " AND rk.id IS NULL");
        ELSE
            -- 'all' case, no additional filter needed
            SET @sql = CONCAT(@sql, "");
    END CASE;

    SET @sql = CONCAT(@sql, " ORDER BY cv.complainant_name, rps.name");

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

-- 2024-09-23


ALTER VIEW `leave_request_view` AS 
SELECT 
leave_requests.id, 
leave_requests.resident_id, 
leave_requests.residence_id, 
leave_requests.from_date, 
leave_requests.to_date, 
leave_requests.leave_time, 
leave_requests.planned_reporting_dt, 
leave_requests.reporting_dt, 
leave_requests.reporting_time, 
leave_requests.reporting_ack_by, 
leave_requests.template_id, 
leave_requests.description, 
leave_requests.flag, 
leave_requests.file_name, 
leave_requests.created_by, 
leave_requests.approved_at, 
leave_requests.approved_by, 
leave_requests.cancelled_at, 
leave_requests.cancelled_by, 
leave_requests.cancel_reason, 
leave_requests.created_at, 
leave_requests.updated_at, 
leave_requests.isActive, 
leave_requests.ex_approval_status, 
leave_requests.approval_code, 
leave_requests.ex_action_at, 
leave_requests.approver_comment, 
leave_requests.early_approve_by, 
leave_requests.early_approve_at, 
leave_requests.is_missing_academic,
leave_requests.eduhead_requested_by, 
leave_requests.eduhead_requested_at, 
leave_requests.eduhead_status, 
leave_requests.eduhead_action_at, 
leave_requests.building_exit_ack_by, 
json_extract(resident_personal_details.personal_data, "$.parents") as parents,
json_extract(resident_personal_details.personal_data, "$.local_guardians") as local_guardians,
building_exit_ack.name AS building_exit_ack_by_name,
leave_requests.building_exit_date_time, 
leave_requests.maingate_exit_ack_by, 
maingate_exit_ack.name AS maingate_exit_ack_by_name,
leave_requests.maingate_exit_at,
complainant_view.complainant_name AS resident_name,
complainant_view.contact_no,
complainant_view.gender,
complainant_view.file,
complainant_view.ext AS ext,
complainant_view.apartment_name,
complainant_view.building_id,
complainant_view.building_name,
leave_templates.description AS template_description,
resident_only_academic_view.reference_number AS roll_number,
resident_only_academic_view.course_year,
resident_only_academic_view.branch,
resident_only_academic_view.course,
resident_only_academic_view.course_name,
resident_only_academic_view.branch_name
FROM `leave_requests` 
LEFT JOIN complainant_view ON complainant_view.id=leave_requests.resident_id 
LEFT JOIN leave_templates ON leave_templates.id = leave_requests.template_id 
LEFT JOIN user AS building_exit_ack ON building_exit_ack.id=leave_requests.building_exit_ack_by
LEFT JOIN user AS maingate_exit_ack ON maingate_exit_ack.id=leave_requests.maingate_exit_ack_by
LEFT JOIN resident_personal_details ON resident_personal_details.resident_id=leave_requests.resident_id
LEFT JOIN resident_only_academic_view ON leave_requests.resident_id=resident_only_academic_view.resident_id;


INSERT INTO `push_notification_types` (`push_notification_id`, `notification_desc`, `isPopUp`, `scope`, `created_at`, `updated_at`) VALUES 
('88', 'Leave Request Scan', '1', 'RESIDENT', '2023-02-13 09:13:50', '2023-02-13 09:13:50');


ALTER TABLE `department_team` CHANGE `department_id` `department_id` VARCHAR(100) NULL;

-- 2024-09-25
-- production update
DROP PROCEDURE IF EXISTS GetResidentsWithAcademic;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetResidentsWithAcademic`(IN `cid` INT, IN `is_activated` CHAR(2))
BEGIN
    DECLARE sql_query TEXT;

    SET sql_query = 'SELECT 
        complainants.id AS id,
        complainants.complainant_name AS complainant_name,
        complainants.contact_no AS contact_no,
        complainants.phone_prefix AS phone_prefix,
        complainants.email AS email,
        complainants.adhaar_no AS adhaar_no,
        complainants.ration_number AS ration_number,
        complainants.gender AS gender,
        complainants.language AS language,
        complainants.divyaang AS divyaang,
        resident_residences.residence_id AS residence_id,
        resident_residences.is_checked_in AS is_checked_in,
        complainants.is_activated AS is_activated,
        complainants.created_at AS created_at,
        creator.name AS created_by_name,
        complainants.updated_at AS updated_at,
        complainants.file AS file,
        complainants.ext AS ext,
        complainants.isContactDetailsPublic AS isContactDetailsPublic,
        complainants.created_by AS created_by,
        resident_residences.resident_status AS resident_status,
        resident_residences.living_start_at AS living_start_at,
        complainants.isTest AS isTest,
        complainants.isFake AS isFake,
        complainants.approved_at AS approved_at,
        complainants.approved_by AS approved_by,
        approver.name AS approved_by_name,
        complainants.disabled_at AS disabled_at,
        complainants.disabled_by AS disabled_by,
        remover.name AS disabled_by_name,
        residence_status.residence_status_text AS residence_status_text,
        building_residence.apartment_name AS apartment_name,
        building_residence.unit_type AS unit_type,
        building_residence.floor AS floor,
        building_unit_types.Description AS unit_name,
        building.name AS building_name,
        building.cordinates AS building_cordinates,
        building.checkin_radius AS building_checkin_radius,
        building_residence.building_id AS building_id,
        IFNULL(pp.complete_in_per, 0) AS profile_complete,
        JSON_EXTRACT(resident_personal_details.personal_data, "$.parents") AS parents,
        JSON_EXTRACT(resident_personal_details.personal_data, "$.blood_group") AS blood_group,
        JSON_EXTRACT(resident_personal_details.personal_data, "$.dob") AS dob,
        JSON_EXTRACT(resident_personal_details.personal_data, "$.local_guardians") AS local_guardians,
        resident_academic_details.registration_no AS registration_no,
        resident_academic_details.reference_number AS reference_number,
        academic_courses.course_name AS course_name,
        academic_course_branches.branch_name AS branch_name,
        resident_academic_details.course AS course_id,
        resident_academic_details.branch AS branch_id,
        resident_academic_details.cgpa AS cgpa,
        resident_academic_details.course_year AS course_year,
        discount_vouchers.discount AS discount,
        discount_vouchers.discount_for AS discount_for,
        discount_vouchers.type AS discount_type,
        pending_bill.pending_bill_amount AS pending_bill_amount,
        resident_community_deposit_balance.balance AS deposit_balance,
        resident_wallet_balance.community_balance
    FROM
        complainants
    LEFT JOIN resident_residences ON (resident_residences.resident_id = complainants.id AND resident_residences.isActive = 1)
    LEFT JOIN building_residence ON resident_residences.residence_id = building_residence.id
    LEFT JOIN building_unit_types ON building_residence.unit_type = building_unit_types.unit_type_id
    LEFT JOIN building ON building.id = building_residence.building_id
    LEFT JOIN residence_status ON residence_status.residence_status_id = resident_residences.resident_status
    LEFT JOIN user creator ON complainants.created_by = creator.id
    LEFT JOIN user approver ON complainants.approved_by = approver.id
    LEFT JOIN user remover ON complainants.disabled_by = remover.id
    LEFT JOIN resident_personal_details ON resident_personal_details.resident_id = complainants.id
    LEFT JOIN (
        SELECT 
            complainants.id AS resident_id,
            ROUND(
                (
                    LENGTH(TRIM("," FROM CONCAT(COALESCE(kyc.doc_filled, ""), ",", COALESCE(resident_personal_details.resident_profile_setting_id, "")))) -  
                    LENGTH(REPLACE(TRIM("," FROM CONCAT(COALESCE(kyc.doc_filled, ""), ",", COALESCE(resident_personal_details.resident_profile_setting_id, ""))), ",", "")) + 1
                ) / (SELECT COUNT(id)
 AS total_required FROM resident_profile_settings WHERE isActive = 1) * 100
            ) AS complete_in_per
        FROM complainants
        LEFT JOIN (
            SELECT 
                GROUP_CONCAT(DISTINCT resident_profile_setting_id) AS doc_filled,
                resident_id 
            FROM resident_kyc 
            GROUP BY resident_id
        ) AS kyc
        ON kyc.resident_id = complainants.id
        LEFT JOIN resident_personal_details 
        ON resident_personal_details.resident_id = complainants.id   
        WHERE resident_personal_details.resident_profile_setting_id IS NOT NULL 
        OR kyc.doc_filled IS NOT NULL
    ) AS pp
    ON complainants.id = pp.resident_id
    LEFT JOIN resident_academic_details ON resident_academic_details.resident_id = complainants.id
    LEFT JOIN academic_courses ON academic_courses.id = resident_academic_details.course
    LEFT JOIN academic_course_branches ON academic_course_branches.branch_id = resident_academic_details.branch
    LEFT JOIN resident_rent_discount AS rent_discount ON rent_discount.resident_id = complainants.id AND rent_discount.isLatest = 1 AND rent_discount.cancelled_by IS NULL
    LEFT JOIN discount_vouchers ON rent_discount.voucher_id = discount_vouchers.id
    LEFT JOIN (
        SELECT 
            resident_id, 
            ROUND(SUM(net_amount - paid_amount)) AS pending_bill_amount
        FROM community_occupancy_bills
        WHERE cancelled_at IS NULL AND approved_at IS NOT NULL
        GROUP BY resident_id
    ) AS pending_bill ON complainants.id = pending_bill.resident_id
    LEFT JOIN resident_community_deposit_balance ON resident_community_deposit_balance.resident_id = complainants.id
    LEFT JOIN resident_wallet_balance ON resident_wallet_balance.resident_id = complainants.id
    WHERE complainants.isTest = 0';


    IF (cid > 0) THEN
        SET sql_query = CONCAT(sql_query, ' AND complainants.id = ', cid);
    ELSE
        IF (is_activated IS NOT NULL AND is_activated IN ('A', 'I', 'S', 'AC', 'FC')) THEN
            SET sql_query = CONCAT(sql_query, ' AND complainants.is_activated = "', is_activated, '"');
        END IF;
    END IF;

    SET sql_query = CONCAT(sql_query, ' ORDER BY complainants.id DESC');

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;


-- 2024-10-02

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'GATE', 'informed_parents_on_gate_exit', 'Informed Parents On Gate Exit', 'string', 'SMS', '1', '2024-08-30 17:01:38'),
(NULL, 'GATE', 'informed_parents_on_gate_entry', 'Informed Parents On Gate Entry', 'string', 'SMS', '1', '2024-08-30 17:01:38');

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'GATE', 'informed_parents_on_gate_exit_for_dayout', 'Informed Parents On Gate Exit For Dayout', 'string', 'SMS', '1', '2024-08-30 17:01:38'),
(NULL, 'GATE', 'informed_parents_on_gate_entry_for_dayout', 'Informed Parents On Gate Entry For Dayout', 'string', 'SMS', '1', '2024-08-30 17:01:38');



INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'GATE', 'leave_notification_body_on_gate_exit', 'Leave Notification Body On Gate Exit', 'string', '', '1', '2024-08-30 17:01:38'),
(NULL, 'GATE', 'leave_notification_body_on_gate_entry', 'Leave Notification Body On Gate Entry', 'string', '', '1', '2024-08-30 17:01:38');

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'GATE', 'leave_notification_title_on_gate_exit', 'Leave Notification Title On Gate Exit', 'string', '', '1', '2024-08-30 17:01:38'),
(NULL, 'GATE', 'leave_notification_title_on_gate_entry', 'Leave Notification Title On Gate Entry', 'string', '', '1', '2024-08-30 17:01:38');

-- 2024-10-03

CREATE TABLE leave_urgent_passes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_date DATETIME,
    to_date DATETIME,
    resident_id INT,
    approved_by INT DEFAULT NULL,
    approved_at DATETIME DEFAULT NULL,
    rejected_by INT DEFAULT NULL,
    rejected_at DATETIME DEFAULT NULL,
    reason VARCHAR(255) DEFAULT NULL,
    created_by INT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);


INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'LEAVE', 'is_leave_urgent_pass_required', 'Is Leave Urgent Pass Required', 'boolean', '0', '1', '2024-10-03 17:01:38');


INSERT INTO `notification_workflow` (`id`, `feature`, `stage`, `guardian`, `roles`, `updated_at`, `updated_by`) VALUES 
(NULL, 'LEAVE', 'EXTENSION', '1', '7,6,5', '2023-10-03 17:01:38', NULL);

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'LEAVE', 'external_approval_leave_extension', 'External approval mode For Parents Leave Extension', 'string', 'SMS,EMAIL,WHATSAPP', '1', '2024-10-03 17:01:38');

-- 2024-09-30
CREATE TABLE complaint_report_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email_template TEXT NULL,
    department_ids TEXT NULL,
    header_ids TEXT NULL,
    recipient_emails TEXT NOT NULL,
    bcc_emails TEXT NULL,
    cc_emails TEXT NULL,
    phone_numbers TEXT NULL,
    is_active TINYINT DEFAULT 1
);

-- 2024-10-01
ALTER TABLE `msg_transaction_type` ADD `whatsapp` TINYINT NOT NULL DEFAULT '0' AFTER `email`;
ALTER TABLE `complaint_report_notifications` ADD `status_ids` VARCHAR(30) NOT NULL AFTER `email_template`;

-- add new column
ALTER TABLE `complaints` ADD `complain_infra_type` INT NULL DEFAULT NULL AFTER `complaint_status`;


-- update insert complain for new column
DROP PROCEDURE IF EXISTS QuickComplain;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `QuickComplain`(IN `subjectId` INT, IN `description` TEXT, IN `complaint` TEXT, IN `deptid` INT, IN `complaindate` DATE, IN `complaintime` TIME, IN `complainantid` BIGINT(20), IN `teammemberid` INT, IN `isAnonymous` TINYINT(1), IN `sourceid` INT(11), IN `in_priority` INT(1), IN `in_residence_id` INT(11), IN `in_created_by` INT(11), IN `in_floor` VARCHAR(3), IN `in_building_id` INT(11), IN `in_complain_type` VARCHAR(20), IN `in_complain_facility_id` INT(11),IN in_complain_infra_type INT(11), OUT `cid` BIGINT(20))
BEGIN

INSERT INTO `complaints`(subject_id,complaint,complaint_date,complaint_time,complaint_status,complainant_id,department_id,description,team_member_id,isAnonymous,complain_source_id,priority,residence_id,created_by,complain_floor,complain_building_id,complain_type,complain_facility_id,complain_infra_type)
VALUES (subjectId,complaint,complaindate,complaintime,0,complainantid,deptid,description,teammemberid,isAnonymous,sourceid,in_priority,in_residence_id,in_created_by,in_floor,in_building_id,in_complain_type,in_complain_facility_id,in_complain_infra_type);
SET cid = LAST_INSERT_ID(); 
END$$
DELIMITER ;


-- LATE_ENTRY

-- parent approval
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
 (NULL, 'LATE_ENTRY', 'is_parents_approval_required_for_late_entry', 'Is Parent Approval Required For Late Entry', 'boolean', '0', '1', '2024-10-04 15:45:42');

-- only parent approval
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
 (NULL, 'LATE_ENTRY', 'only_parent_approval_required_for_late_entry', 'Only Parent Approval Required For Late Entry', 'boolean', '0', '1', '2024-10-04 15:45:42');

-- external approval columns
ALTER TABLE `resident_gate_request` ADD `ex_approval_status` enum('INITIATED','APPROVED','DENIED') DEFAULT NULL AFTER `cancelled_at`;
ALTER TABLE `resident_gate_request` ADD `approval_code` VARCHAR(50) DEFAULT NULL AFTER `cancelled_at`;
ALTER TABLE `resident_gate_request` ADD `ex_action_at` DATETIME DEFAULT NULL AFTER `cancelled_at`;

-- new notification flow for late entry
INSERT INTO `notification_workflow` (`id`, `feature`, `stage`, `guardian`, `roles`, `updated_at`, `updated_by`) VALUES 
(NULL, 'LATE_ENTRY', 'NEW', '1', '6,7', '2023-10-03 17:01:38', NULL);

INSERT INTO `notification_workflow` (`id`, `feature`, `stage`, `guardian`, `roles`, `updated_at`, `updated_by`) VALUES 
(NULL, 'LATE_ENTRY', 'PARENT_ACTION', '1', '6,7', '2023-10-03 17:01:38', NULL);

-- add new column in view

ALTER VIEW `complaint_view` AS
SELECT
    `complaints`.`complain_id` AS `complain_id`,
    `complaints`.`complainant_id` AS `complainant_id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`divyaang` AS `divyaang`,
    `complaints`.`complain_type`, 
    `complaints`.`complain_infra_type`, 
    `infra_type`.`infra_type_name`,
    `complaints`.`complain_facility_id`, 
    `common_facilities`.`facility_name`, 
    CASE
        WHEN `user`.`language` = 'EN' THEN `complain_subjects`.`subject_eng`
        ELSE `complain_subjects`.`subject_eng`
    END AS `complaint_subject`,
    `complaints`.`subject_id` AS `subject_id`,
    `complain_headers`.`header_id` AS `header_id`,
    `complain_headers`.`header` AS `header`,
    `complain_status`.`complain_status_name` AS `complaint_status`,
    `complain_status`.`complain_status_id` AS `complain_status_id`,
    `complaints`.`complaint_date` AS `complaint_date`,
    `complaints`.`complaint_time` AS `complaint_time`,
    `complaints`.`description` AS `description`,
    `complaints`.`remark` AS `remark`,
    `complaints`.`isAnonymous` AS `isAnonymous`,
    `complain_source`.`complain_source_text` AS `complain_source_text`,
    `complaints`.`complain_source_id` AS `complain_source_id`,
    `ca`.`assignDate` AS `assignDate`,
    `complaints`.`sla_id` AS `sla_id`,
    `complaints`.`priority` AS `priority`,
    `complain_priority`.`priority_desc` AS `priority_desc`,
    `complaints`.`latitude` AS `latitude`,
    `complaints`.`longitute` AS `longitute`,
    `complaints`.`scope` AS `scope`,
    `complain_scopes`.`scope_text` AS `scope_text`,
    `complaints`.`scope_reference` AS `scope_reference`,
    `user`.`userRole` AS `userRole`,
    `user`.`name` AS `created_by_name`,
    IF(`role`.`Alias` IS NULL , `role`.`role_name`, `role`.`Alias` ) as role_name,
    `user`.`language` AS `language`,
    `complaints`.`complaint` AS `complaint`,
    `complainants_feedback`.`feedback_rating` AS `complainant_feedback`,
    `department`.`department_name` AS `department_name`,
    `complaints`.`department_id` AS `department_id`,
    `department_team`.`member_name` AS `member_name`,
    `complaints`.`team_member_id` AS `team_member_id`,
    `department_team`.`member_contactno` AS `member_contactno`,
    `department_team`.`member_email` AS `member_email`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `complaints`.`residence_id` AS `residence_id`,
    `complaints`.`complain_floor` AS `complain_floor`,
    `complaints`.`complain_building_id` AS `complain_building_id`,
    `building_residence`.`floor` AS `floor`,
    `building`.`name` AS `building_name`,
    `building_residence`.`building_id` AS `building_id`,
    `slot_info`.`slot_history_id` AS `multi_slot_history_id`,
    `slot_info`.`slot_id` AS `multi_slot_id`,
    `slot_info`.`slot_date` AS `multi_slot_date`,
    `slot_info`.`slot_start` AS `multi_slot_start`,
    `slot_info`.`slot_end` AS `multi_slot_end`,
    `complaint_slot_history`.`slot_date` AS `slot_date`,
    `work_slots`.`slot_start` AS `slot_start`,
    `work_slots`.`slot_end` AS `slot_end`
FROM
    `complaints`
left join `complain_subjects` on `complaints`.`subject_id` = `complain_subjects`.`subject_id`
left join `complain_headers` on `complain_headers`.`header_id` = `complain_subjects`.`header_id`
left join `complain_scopes` on `complaints`.`scope` = `complain_scopes`.`scope_id`
left join `department_team` on `complaints`.`team_member_id` = `department_team`.`member_id`
left join `user` on `complaints`.`created_by` = `user`.`id`
left join `role` on `role`.`role_id` = `user`.`userRole`
left join `complain_priority` on `complaints`.`priority` = `complain_priority`.`priority_id`
left join `complainants_feedback` on `complainants_feedback`.`complain_no` = `complaints`.`complain_id`
join `department` on `complaints`.`department_id` = `department`.`department_id`
left join `complainants` on `complaints`.`complainant_id` = `complainants`.`id`
join `complain_source` on `complaints`.`complain_source_id` = `complain_source`.`complain_source_id`
join `complain_status` on `complaints`.`complaint_status` = `complain_status`.`complain_status_id`
left join (
    select
        max(`complain_activities`.`date`) AS `assignDate`,
        `complain_activities`.`complain_no` AS `complain_no`
    from
        `complain_activities`
    where
        `complain_activities`.`activity_type` = 4
    group by
        `complain_activities`.`complain_no`
) `ca` on(`complaints`.`complain_id` = `ca`.`complain_no`)
left join `building_residence` on `building_residence`.`id` = `complaints`.`residence_id`
left join `building` on (`building_residence`.`building_id` = `building`.`id` OR `complaints`.`complain_building_id` = `building`.`id` )
LEFT JOIN (
	SELECT
		`complaint_slot_history`.`complaint_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_history_id`) AS `slot_history_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_id`) AS `slot_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_date`) AS `slot_date`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_start`, '')) AS `slot_start`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_end`, '')) AS `slot_end`
	FROM
		`complaint_slot_history`
	LEFT JOIN `work_slots` ON `work_slots`.`id` = `complaint_slot_history`.`slot_id`
	where `complaint_slot_history`.`isActive` = 1 
	GROUP BY
		`complaint_slot_history`.`complaint_id`
) `slot_info` ON `complaints`.`complain_id` = `slot_info`.`complaint_id`
left join `complaint_slot_history` on `complaint_slot_history`.`slot_history_id` = `complaints`.`slot_history_id`
left join `work_slots` on `work_slots`.`id` = `complaint_slot_history`.`slot_id`
left join `common_facilities` on `complaints`.`complain_facility_id` = `common_facilities`.`id`
left join `infra_type` on `complaints`.`complain_infra_type` = `infra_type`.`id`
ORDER BY
    `complaints`.`complain_id` DESC;


-- 2024-10-05
-- complain 

-- default value '1 = Residence'
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'COMPLAIN', 'allowed_infra_types_for_resident_complain', 'Allowed Infra Types For Resident Complain', 'string', '1', '1', '2024-10-05 13:01:38');


-- 2024-10-09

-- change from Krishna
DROP PROCEDURE IF EXISTS `GetComplainsByRoleDeptAndHeader`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainsByRoleDeptAndHeader`(IN `in_user_id` INT, IN `input_role_id` INT(3), IN `in_status_ids` LONGTEXT, IN `in_department_ids` LONGTEXT, IN `in_header_ids` LONGTEXT, IN `building_id` VARCHAR(500), IN `startDate` VARCHAR(100), IN `endDate` VARCHAR(100))
BEGIN

SET @sql = "SELECT complaint_view.* from complaint_view WHERE 1 =1 ";

IF (in_status_ids != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.complain_status_id IN (', in_status_ids, ')');
END IF;

IF (in_department_ids IS NOT NULL AND in_department_ids != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.department_id IN (', in_department_ids, ')');
END IF;

IF (in_header_ids IS NOT NULL AND in_header_ids != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.header_id IN (', in_header_ids, ')');
END IF;

IF (startDate IS NOT NULL AND endDate IS NOT NULL AND endDate != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.complaint_date >= ''', startDate, ''' AND complaint_view.complaint_date <= ''', endDate, '''');
END IF;

IF (building_id IS NOT NULL AND building_id != '') THEN
	 SET @sql = CONCAT(@sql, ' AND complaint_view.building_id IN (', building_id, ')');
END IF;

SET @sql = CONCAT(@sql,' order by  complaint_view.complain_id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- change from Krishna
DROP PROCEDURE IF EXISTS `GetComplainsByRole`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainsByRole`(IN `in_user_id` INT, IN `input_role_id` INT(3), IN `in_status` INT, IN `daterangearray` VARCHAR(500), IN `in_status_group` LONGTEXT, IN `in_dept_id` INT(11), IN `building_id` VARCHAR(500))
BEGIN

DECLARE is_self_assign INT DEFAULT 0;
DECLARE department_ids VARCHAR(500) DEFAULT '';
DECLARE team_member_id INT DEFAULT 0;

SELECT COALESCE(MAX(rule_value), '0') INTO is_self_assign FROM settings WHERE rule_name = 'is_helper_allowed_self_assign_complain';

SET @sql = "SELECT complaint_view.* from complaint_view WHERE 1 =1 ";

IF ( in_status is not null) THEN
	SET @sql = CONCAT(@sql,' AND complaint_view.complain_status_id =',in_status);
END IF;

IF (in_status_group IS NOT NULL AND in_status_group != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.complain_status_id IN (', in_status_group, ')');
END IF;

if(in_dept_id>0) then 
	SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',in_dept_id);
END IF;

if (input_role_id>5) then 
	SET @sql = CONCAT(@sql, ' AND (complaint_view.scope = 5 OR ( (building_id IN (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1)');
    SET @sql = CONCAT(@sql, ' OR complain_building_id IN (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1))');

    if(input_role_id=8) THEN
		-- set @department_id=(select relatedId from user where user.id=in_user_id AND user.userRole = 8 );
		set @department_ids=(select IFNULL(dt.department_id,'') as department_id from user left join department_team dt on dt.member_id = user.relatedId where user.id=in_user_id AND user.userRole = 8);
        if @department_ids != '' then 
			-- SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',@department_id);
			SET @sql = CONCAT(@sql, " AND FIND_IN_SET(complaint_view.department_id,'", @department_ids, "') ");
        END IF;
	END IF;
	if(input_role_id=9) THEN
		set @team_member_id=(select relatedId from user where user.id=in_user_id AND user.userRole = 9 );
		set @department_ids=(select IFNULL(dt.department_id,'') as department_id from department_team dt where dt.member_id = @team_member_id);
        IF is_self_assign != 1 THEN
            SET @sql = CONCAT(@sql, ' AND complaint_view.team_member_id = ', @team_member_id);
        ELSE
			SET @sql = CONCAT(@sql, " AND FIND_IN_SET(complaint_view.department_id,'", @department_ids, "') ");
            SET @sql = CONCAT(@sql, ' AND (complaint_view.complain_status_id = 0 OR (complaint_view.complain_status_id != 0 AND complaint_view.team_member_id = ', @team_member_id, '))');
        END IF;
	END IF;
	SET @sql = CONCAT(@sql, ' ))');
END IF;

IF ( daterangearray != '') THEN
	SET @sql = CONCAT(@sql,' AND complaint_view.complaint_date NOT BETWEEN ',daterangearray );
END IF;

IF (building_id IS NOT NULL AND building_id != '') THEN
	 SET @sql = CONCAT(@sql, ' AND complaint_view.building_id IN (', building_id, ')');
END IF;

SET @sql = CONCAT(@sql,' order by  complaint_view.complain_id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;



-- leave request & uegent pass
ALTER TABLE `leave_urgent_passes` ADD `approver_comment` VARCHAR(255) NOT NULL AFTER `approved_at`;
ALTER TABLE `leave_urgent_passes` ADD `reject_reason` VARCHAR(255) NOT NULL AFTER `rejected_at`;

-- leave urgent pass minimum time limit
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
VALUES (NULL, 'LEAVE', 'time_limit_to_apply_urgent_pass', 'Time limit to apply urgent pass in hr', 'int', '0', '1', '2024-10-07 15:58:28');

-- urgent pass notification flow
INSERT INTO `notification_workflow` (`id`, `feature`, `stage`, `guardian`, `roles`, `updated_at`, `updated_by`) VALUES 
(NULL, 'LEAVE', 'URGENT_PASS_NEW', '1', '', '2023-10-15 11:27:38', NULL);



 INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
 (NULL, 'LEAVE', 'is_parents_approval_required_leave_extension', 'Is Parents Approval Required Leave Extension', 'boolean', '0', '1', '2024-10-04 15:45:42');

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
 (NULL, 'LEAVE', 'only_parent_approval_required_for_leave', 'Only Parent Approval Required For Leave', 'boolean', '0', '1', '2024-10-04 15:45:42');

-- add new
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
VALUES (NULL, 'LABEL', 'leave_request_label', 'Leave Request', 'string', 'Leave Request', '1', '2024-10-15 11:38:28');

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
VALUES (NULL, 'LABEL', 'urgent_pass_label', 'Urgent Pass', 'string', 'Urgent Pass', '1', '2024-10-15 11:38:28');

-- add biometric exit & entry column
ALTER TABLE `leave_requests` ADD `biometric_exit_at` DATETIME NULL DEFAULT NULL;
ALTER TABLE `leave_requests` ADD `biometric_entry_at` DATETIME NULL DEFAULT NULL;

ALTER VIEW `leave_request_view` AS 
SELECT 
leave_requests.id, 
leave_requests.resident_id, 
leave_requests.residence_id, 
leave_requests.from_date, 
leave_requests.to_date, 
leave_requests.leave_time, 
leave_requests.planned_reporting_dt, 
leave_requests.reporting_dt, 
leave_requests.reporting_time, 
leave_requests.reporting_ack_by, 
leave_requests.template_id, 
leave_requests.description, 
leave_requests.flag, 
leave_requests.file_name, 
leave_requests.created_by, 
leave_requests.approved_at, 
leave_requests.approved_by, 
leave_requests.cancelled_at, 
leave_requests.cancelled_by, 
leave_requests.cancel_reason, 
leave_requests.created_at, 
leave_requests.updated_at, 
leave_requests.isActive, 
leave_requests.ex_approval_status, 
leave_requests.approval_code, 
leave_requests.ex_action_at, 
leave_requests.approver_comment, 
leave_requests.early_approve_by, 
leave_requests.early_approve_at, 
leave_requests.is_missing_academic,
leave_requests.eduhead_requested_by, 
leave_requests.eduhead_requested_at, 
leave_requests.eduhead_status, 
leave_requests.eduhead_action_at, 
leave_requests.building_exit_ack_by, 
json_extract(resident_personal_details.personal_data, "$.parents") as parents,
json_extract(resident_personal_details.personal_data, "$.local_guardians") as local_guardians,
building_exit_ack.name AS building_exit_ack_by_name,
leave_requests.building_exit_date_time, 
leave_requests.maingate_exit_ack_by, 
maingate_exit_ack.name AS maingate_exit_ack_by_name,
leave_requests.maingate_exit_at,
leave_requests.biometric_exit_at, 
leave_requests.biometric_entry_at,
complainant_view.complainant_name AS resident_name,
complainant_view.contact_no,
complainant_view.gender,
complainant_view.file,
complainant_view.ext AS ext,
complainant_view.apartment_name,
complainant_view.building_id,
complainant_view.building_name,
leave_templates.description AS template_description,
resident_only_academic_view.reference_number AS roll_number,
resident_only_academic_view.course_year,
resident_only_academic_view.branch,
resident_only_academic_view.course,
resident_only_academic_view.course_name,
resident_only_academic_view.branch_name
FROM `leave_requests` 
LEFT JOIN complainant_view ON complainant_view.id=leave_requests.resident_id 
LEFT JOIN leave_templates ON leave_templates.id = leave_requests.template_id 
LEFT JOIN user AS building_exit_ack ON building_exit_ack.id=leave_requests.building_exit_ack_by
LEFT JOIN user AS maingate_exit_ack ON maingate_exit_ack.id=leave_requests.maingate_exit_ack_by
LEFT JOIN resident_personal_details ON resident_personal_details.resident_id=leave_requests.resident_id
LEFT JOIN resident_only_academic_view ON leave_requests.resident_id=resident_only_academic_view.resident_id;




2024-10-28
ALTER TABLE `leave_urgent_passes` CHANGE `reason` `reason` TEXT NULL DEFAULT NULL;

-- Dayout
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
VALUES (NULL, 'DAYOUT', 'time_limit_to_apply_for_dayout', 'Time limit to apply dayout in hr', 'int', '0', '1', '2024-10-07 15:58:28');

ALTER TABLE `day_out_requests` CHANGE `approver_comment` `approver_comment` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;

-- 2024-10-30
-- form bishnu sir
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetAvailableResidencesForSession`(
    IN building_id VARCHAR(255),
    IN residence_id VARCHAR(255),
    IN abs_id INT
)
BEGIN
    DECLARE bookingStartDate DATETIME;
    DECLARE baseQuery TEXT;
    DECLARE whereClause TEXT DEFAULT '';

    -- Initialize the base query with a JOIN to building_unit_types for unit_name and building for building_name
    SET baseQuery = "
        SELECT br.id AS residence_id,
               br.apartment_name,
               br.unit_type,
               but.Description AS unit_name,
               b.name AS building_name,
               br.building_id,
               br.floor,
               br.block
        FROM building_residence br
        LEFT JOIN resident_final_stay rfs ON br.id = rfs.residence_id
        LEFT JOIN building_unit_types but ON br.unit_type = but.unit_type_id
        LEFT JOIN building b ON br.building_id = b.id
        WHERE br.is_active = 1 AND br.is_reserved = 0";

    -- Conditionally add the bookingStartDate filter if abs_id is provided
    IF abs_id IS NOT NULL THEN
        SELECT booking_start_date INTO bookingStartDate
        FROM academic_booking_sessions
        WHERE id = abs_id;

        SET whereClause = CONCAT(whereClause, " AND (rfs.final_end_date IS NULL OR rfs.final_end_date < '", bookingStartDate, "')");
    END IF;

    -- Conditionally add building_id filter using FIND_IN_SET if provided
    IF building_id IS NOT NULL AND building_id != '' THEN
        SET whereClause = CONCAT(whereClause, " AND FIND_IN_SET(br.building_id, '", building_id, "') > 0");
    END IF;

    -- Conditionally add residence_id filter using FIND_IN_SET if provided
    IF residence_id IS NOT NULL AND residence_id != '' THEN
        SET whereClause = CONCAT(whereClause, " AND FIND_IN_SET(br.id, '", residence_id, "') > 0");
    END IF;

    -- Final query construction
    SET @finalQuery = CONCAT(baseQuery, whereClause);
    PREPARE stmt FROM @finalQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

2024-11-06
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'LEAVE', 'is_edu_head_approval_required', 'Is Edu Head Approval Required', 'boolean', '0', '1', '2024-11-06 13:01:38');


2024-11-12
-- from server
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetBuildingSummaryDetails`(IN `building_ids` VARCHAR(255), IN `get_status` VARCHAR(10))
BEGIN
    DECLARE baseQuery TEXT;
    DECLARE whereClause TEXT DEFAULT '';

    -- Initialize the base query with joins and conditional aggregation
    SET baseQuery = "
        SELECT 
            b.id AS building_id,
            b.name,
            b.cordinates,
            b.address,
            b.is_automated_electric_switch,
            b.blocks,
            b.floor_count,
            b.floor_starts_from,
            b.checkin_radius,
            b.unit_types,
            cau.approvers AS building_approvers,
            cau.supreme_approval AS building_supreme_approvers,
            COUNT(DISTINCT br.id) AS totalResidences,
            COUNT(DISTINCT CASE WHEN rr.isActive = 1 THEN rr.residence_id END) AS totalOccupied,
            SUM(CASE WHEN but.capacity IS NOT NULL THEN but.capacity ELSE 0 END) AS totalCapacity,
            COUNT(DISTINCT CASE WHEN brr.id IS NOT NULL THEN br.id END) AS totalReservedResidences,
            b.is_active
        FROM building b
        LEFT JOIN checkout_approval_users cau ON cau.building_id = b.id
        LEFT JOIN building_residence br ON br.building_id = b.id AND br.is_active = 1
        LEFT JOIN resident_residences rr ON rr.residence_id = br.id AND rr.isActive = 1
        LEFT JOIN building_unit_types but ON br.unit_type = but.unit_type_id
        LEFT JOIN building_residence_reservations brr ON brr.residence_id = br.id AND brr.isActive = 1
        WHERE 1 = 1";

    -- Conditionally add the building_id filter using FIND_IN_SET if provided
    IF building_ids IS NOT NULL AND building_ids != '' THEN
        SET whereClause = CONCAT(whereClause, " AND FIND_IN_SET(b.id, '", building_ids, "') > 0");
    END IF;

    -- Conditionally add the status filter if 'get' is provided
    IF get_status = 'active' THEN
        SET whereClause = CONCAT(whereClause, " AND b.is_active = 1");
    ELSEIF get_status = 'deactive' THEN
        SET whereClause = CONCAT(whereClause, " AND b.is_active = 0");
    END IF;

    -- Add GROUP BY clause
    SET whereClause = CONCAT(whereClause, " GROUP BY b.id");

    -- Final query construction
    SET @finalQuery = CONCAT(baseQuery, whereClause);
	#select @finalQuery;
    -- Prepare and execute the statement
    PREPARE stmt FROM @finalQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

2024-11-13
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
VALUES (NULL, 'APP_VERSION', 'under_maintenance_message', 'Under Maintenance Message', 'string', '', '1', '2024-11-12 20:50:28');

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
VALUES (NULL, 'LEAVE', 'whatsapp_community_name', 'Whatsapp Community Name', 'string', '', '1', '2024-11-13 11:40:28');

SELECT 
leave_requests.id, 
leave_requests.resident_id, 
leave_requests.residence_id, 
leave_requests.from_date, 
leave_requests.to_date, 
leave_requests.leave_time, 
leave_requests.planned_reporting_dt, 
leave_requests.reporting_dt, 
leave_requests.reporting_time, 
leave_requests.reporting_ack_by, 
leave_requests.template_id, 
leave_requests.description, 
leave_requests.flag, 
leave_requests.file_name, 
leave_requests.created_by, 
leave_requests.approved_at, 
leave_requests.approved_by, 
leave_requests.cancelled_at, 
leave_requests.cancelled_by, 
leave_requests.cancel_reason, 
leave_requests.created_at, 
leave_requests.updated_at, 
leave_requests.isActive, 
leave_requests.ex_approval_status, 
leave_requests.approval_code, 
leave_requests.ex_action_at, 
leave_requests.approver_comment, 
leave_requests.early_approve_by, 
leave_requests.early_approve_at, 
leave_requests.is_missing_academic,
leave_requests.eduhead_requested_by, 
leave_requests.eduhead_requested_at, 
leave_requests.eduhead_status, 
leave_requests.eduhead_action_at, 
leave_requests.building_exit_ack_by, 
json_extract(resident_personal_details.personal_data, "$.parents") as parents,
json_extract(resident_personal_details.personal_data, "$.local_guardians") as local_guardians,
building_exit_ack.name AS building_exit_ack_by_name,
leave_requests.building_exit_date_time, 
leave_requests.maingate_exit_ack_by, 
maingate_exit_ack.name AS maingate_exit_ack_by_name,
leave_requests.maingate_exit_at,
leave_requests.biometric_exit_at, 
leave_requests.biometric_entry_at,
complainant_view.complainant_name AS resident_name,
complainant_view.contact_no,
complainant_view.gender,
complainant_view.file,
complainant_view.ext AS ext,
complainant_view.apartment_name,
complainant_view.building_id,
complainant_view.building_name,
leave_templates.description AS template_description,
resident_only_academic_view.reference_number AS roll_number,
resident_only_academic_view.course_year,
resident_only_academic_view.branch,
resident_only_academic_view.course,
resident_only_academic_view.course_name,
resident_only_academic_view.branch_name,
resident_only_academic_view.head_user_id,
head_user.name AS head_user_name,
ifnull(gate_info.device_count, 0) AS device_count
FROM `leave_requests` 
LEFT JOIN complainant_view ON complainant_view.id=leave_requests.resident_id 
LEFT JOIN leave_templates ON leave_templates.id = leave_requests.template_id 
LEFT JOIN user AS building_exit_ack ON building_exit_ack.id=leave_requests.building_exit_ack_by
LEFT JOIN user AS maingate_exit_ack ON maingate_exit_ack.id=leave_requests.maingate_exit_ack_by
LEFT JOIN user AS head_user ON head_user.id=resident_only_academic_view.head_user_id
LEFT JOIN resident_personal_details ON resident_personal_details.resident_id=leave_requests.resident_id
LEFT JOIN resident_only_academic_view ON leave_requests.resident_id=resident_only_academic_view.resident_id;
LEFT JOIN (
    select
        gates.building_id AS building_id,
        count(attendance_devices.id) AS device_count
    from gates
    LEFT JOIN attendance_devices on(attendance_devices.gate_id = gates.id)
    where attendance_devices.is_active = 1 and gates.gate_type = 'HostelGate'
    group by attendance_devices.gate_id, gates.building_id
) gate_info on gate_info.building_id = complainant_view.building_id;


DROP PROCEDURE IF EXISTS GetLeaveRequestCounts;
DELIMITER $$
CREATE PROCEDURE `GetLeaveRequestCounts`(
    IN userId INT,
    IN userRole INT,
    IN isLeaveSetting INT,
    IN isQrShow INT,
    IN isDeviceInstalled INT,
    IN isMainGateAvailable INT,
    IN userBuildings VARCHAR(100),
    IN startDt VARCHAR(30),
    IN endDt VARCHAR(30)
)
BEGIN
    -- Declare local variables for counts
    DECLARE expiredCount INT DEFAULT 0;
    DECLARE pendingApprovalCount INT DEFAULT 0;
    DECLARE approvedCount INT DEFAULT 0;
    DECLARE onLeaveCount INT DEFAULT 0;
    DECLARE leavingSoonCount INT DEFAULT 0;
    DECLARE completedCount INT DEFAULT 0;
    DECLARE cancelledCount INT DEFAULT 0;
    DECLARE notReportedCount INT DEFAULT 0;
    DECLARE notOnLeaveCount INT DEFAULT 0;

    -- Apply building filter if role requires it
    SET @buildingFilter := '';
    IF userRole > 5 AND userRole < 11 THEN
        IF isLeaveSetting = 1 THEN
            SET @buildingFilter := CONCAT(' AND (is_warden_view_required = 1 AND FIND_IN_SET(', userId, ', approver_ids) > 0) ');
        ELSE
            SET @buildingFilter := IF(userBuildings IS NOT NULL, 
                CONCAT(' AND FIND_IN_SET(building_id, "', userBuildings, '")'), 
                ''
            );
        END IF;
    END IF;

    IF userRole = 12 THEN
        IF isLeaveSetting = 1 THEN
            SET @buildingFilter := CONCAT(' AND (is_edu_head_approval_required = 1 AND FIND_IN_SET(', userId, ', approver_ids) > 0) ');
        ELSE
            SET @buildingFilter := CONCAT(' AND head_user_id = ', userId);
        END IF;
    END IF;

    -- Date range filter
    SET @dateFilter := IF(startDt IS NOT NULL AND endDt IS NOT NULL,
        CONCAT(' AND (from_date BETWEEN "', startDt, '" AND "', endDt, '" OR to_date BETWEEN "', startDt, '" AND "', endDt, '")'),
        ''
    );

    -- Counts for each status
    
    -- Expired
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @expiredCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NULL
          AND current_date > to_date
          AND isActive = 1
          ', @buildingFilter, @dateFilter
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Pending Approval
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @pendingApprovalCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NULL
          AND current_date <= to_date
          AND isActive = 1
          ', @buildingFilter, @dateFilter
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Approved
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @approvedCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NOT NULL
          AND isActive = 1
          ', @buildingFilter, @dateFilter
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- On Leave
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @onLeaveCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NOT NULL
          AND reporting_dt IS NULL
          AND isActive = 1
          AND CURRENT_DATE BETWEEN from_date AND to_date
          ', @buildingFilter
    );

    IF isQrShow = 1 THEN
        SET @sql := CONCAT(@sql, ' AND building_exit_date_time IS NOT NULL ' );
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sql := CONCAT(@sql, ' AND biometric_exit_at IS NOT NULL ' );
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sql := CONCAT(@sql, ' AND ((device_count < 2) OR (device_count > 1 AND biometric_exit_at IS NOT NULL)) ' );
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Leaving Soon
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @leavingSoonCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NOT NULL
          AND isActive = 1
          ', @buildingFilter
    );

    IF isQrShow = 1 THEN
        SET @sql := CONCAT(@sql, ' AND building_exit_date_time IS NULL
         AND current_date <= leave_request_view.to_date ' );
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sql := CONCAT(@sql, ' AND biometric_exit_at IS NULL
         AND current_date <= leave_request_view.to_date ' );
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sql := CONCAT(@sql, ' AND ((device_count > 1 AND current_date <= leave_request_view.to_date AND biometric_exit_at IS NULL)
         OR (device_count < 2 AND current_date < leave_request_view.from_date)) 
          AND current_date <= leave_request_view.to_date ' );
    ELSE
        SET @sql := CONCAT(@sql, ' AND current_date < leave_request_view.from_date ' );
    END IF;

    SET @sql := CONCAT(@sql, ' AND reporting_dt IS NULL ' );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Completed
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @completedCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NOT NULL
          AND isActive = 1
          ', @buildingFilter
    );

    IF isQrShow = 1 THEN
        SET @sql := CONCAT(@sql, ' AND leave_request_view.reporting_dt IS NOT NULL ');
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sql := CONCAT(@sql, ' AND biometric_entry_at IS NOT NULL ');
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sql := CONCAT(@sql, ' AND ((device_count > 1 AND biometric_entry_at IS NOT NULL) OR (device_count < 2 AND leave_request_view.planned_reporting_dt<=current_date)) ' );
    ELSE
        SET @sql := CONCAT(@sql, ' AND (leave_request_view.reporting_dt IS NOT NULL OR leave_request_view.planned_reporting_dt <= current_date) ');
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Cancelled
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @cancelledCount
        FROM leave_request_view
        WHERE cancelled_at IS NOT NULL
          ', @buildingFilter, @dateFilter
    );
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Not Reported
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @notReportedCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NOT NULL
          AND isActive = 1
          ', @buildingFilter
    );

    IF isQrShow = 1 THEN
        SET @sql := CONCAT(@sql, ' AND leave_request_view.reporting_dt IS NULL AND (leave_request_view.planned_reporting_dt <= current_date) ');
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sql := CONCAT(@sql, ' AND biometric_entry_at IS NULL AND (leave_request_view.planned_reporting_dt <= current_date) ');
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sql := CONCAT(@sql, ' AND (device_count > 1 AND biometric_entry_at IS NULL) AND (leave_request_view.planned_reporting_dt <= current_date) ');
    ELSE
        SET @sql := CONCAT(@sql, ' AND (leave_request_view.planned_reporting_dt <= current_date AND leave_request_view.reporting_dt IS NULL) ');
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Residents Not on Leave 
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @notOnLeaveCount
        FROM complainant_view
        LEFT JOIN resident_academic_view ON resident_academic_view.resident_id = complainant_view.id
        WHERE complainant_view.id NOT IN (
            SELECT leave_requests.resident_id
            FROM leave_requests
            WHERE leave_requests.approved_at IS NOT NULL
              AND leave_requests.cancelled_at IS NULL
              AND CURDATE() BETWEEN leave_requests.from_date AND leave_requests.to_date
        )
        AND complainant_view.resident_status = 6
        AND complainant_view.is_activated = "A"
    ');
    IF userRole > 5 AND userRole < 11 AND userBuildings IS NOT NULL THEN
        SET @sql := CONCAT(@sql, ' AND FIND_IN_SET(complainant_view.building_id, "', userBuildings, '")' );
    END IF;

    IF userRole = 12 THEN
        SET @sql := CONCAT(@sql, ' AND resident_academic_view.head_user_id = ', userId);
    END IF;


    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Return results as a single row
    SELECT 
        @expiredCount AS expired,
        @pendingApprovalCount AS pending_approval,
        @approvedCount AS approved,
        @onLeaveCount AS on_leave,
        @leavingSoonCount AS leaving_soon,
        @completedCount AS completed,
        @cancelledCount AS cancelled,
        @notReportedCount AS not_reported,
        @notOnLeaveCount AS not_on_leave;
END$$
DELIMITER ;



DELIMITER $$
CREATE PROCEDURE `GetComplainsByRole`(IN `in_user_id` INT, IN `input_role_id` INT(3), IN `in_status` INT, IN `daterangearray` VARCHAR(500), IN `in_status_group` LONGTEXT, IN `in_dept_id` INT(11))
BEGIN

DECLARE is_self_assign INT DEFAULT 0;
DECLARE department_ids VARCHAR(500) DEFAULT '';
DECLARE team_member_id INT DEFAULT 0;

SELECT COALESCE(MAX(rule_value), '0') INTO is_self_assign FROM settings WHERE rule_name = 'is_helper_allowed_self_assign_complain';

SET @sql = "SELECT complaint_view.* from complaint_view WHERE 1 =1 ";

IF ( in_status is not null) THEN
	SET @sql = CONCAT(@sql,' AND complaint_view.complain_status_id =',in_status);
END IF;

IF (in_status_group IS NOT NULL AND in_status_group != '') THEN
    SET @sql = CONCAT(@sql, ' AND complaint_view.complain_status_id IN (', in_status_group, ')');
END IF;

if(in_dept_id>0) then 
	SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',in_dept_id);
END IF;

if (input_role_id>5) then 
	SET @sql = CONCAT(@sql, ' AND (complaint_view.scope = 5 OR ( (building_id IN (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1)');
    SET @sql = CONCAT(@sql, ' OR complain_building_id IN (SELECT building_users.building_id FROM building_users where building_users.user_id=',in_user_id,' and building_users.isActive=1))');

    if(input_role_id=8) THEN
		-- set @department_id=(select relatedId from user where user.id=in_user_id AND user.userRole = 8 );
		set @department_ids=(select IFNULL(dt.department_id,'') as department_id from user left join department_team dt on dt.member_id = user.relatedId where user.id=in_user_id AND user.userRole = 8);
        if @department_ids != '' then 
			-- SET @sql = CONCAT(@sql,' AND complaint_view.department_id =',@department_id);
			SET @sql = CONCAT(@sql, " AND FIND_IN_SET(complaint_view.department_id,'", @department_ids, "') ");
        END IF;
	END IF;
	if(input_role_id=9) THEN
		set @team_member_id=(select relatedId from user where user.id=in_user_id AND user.userRole = 9 );
		set @department_ids=(select IFNULL(dt.department_id,'') as department_id from department_team dt where dt.member_id = @team_member_id);
        IF is_self_assign != 1 THEN
            SET @sql = CONCAT(@sql, ' AND complaint_view.team_member_id = ', @team_member_id);
        ELSE
			SET @sql = CONCAT(@sql, " AND FIND_IN_SET(complaint_view.department_id,'", @department_ids, "') ");
            SET @sql = CONCAT(@sql, ' AND (complaint_view.complain_status_id = 0 OR (complaint_view.complain_status_id != 0 AND complaint_view.team_member_id = ', @team_member_id, '))');
        END IF;
	END IF;
	SET @sql = CONCAT(@sql, ' ))');
END IF;

IF ( daterangearray != '') THEN
	SET @sql = CONCAT(@sql,' AND complaint_view.complaint_date BETWEEN ',daterangearray );
END IF;

SET @sql = CONCAT(@sql,' order by  complaint_view.complain_id desc');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;


-- 2024-11-18

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'DAYOUT', 'dayout_apply_open_time', 'Admin specifies dayout apply open time', 'string', '06:00', '1', '2024-11-18 12:45:38'),
(NULL, 'DAYOUT', 'dayout_apply_close_time', 'Admin specifies dayout apply close time', 'string', '21:00', '1', '2024-11-18 12:45:38');


ALTER TABLE `user` ADD `is_fake` TINYINT NOT NULL DEFAULT '0' AFTER `phone_prefix`;



CREATE TABLE feature_flags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    feature_name VARCHAR(50),
    is_resident_allow TINYINT,
    is_admin_allow TINYINT,
    is_feature_ready TINYINT,
    user_app_position INT,
    admin_app_position INT
);

ALTER TABLE feature_flags ADD CONSTRAINT feature_name UNIQUE(feature_name);

INSERT INTO feature_flags ( feature_name, is_resident_allow, is_admin_allow, is_feature_ready, user_app_position, admin_app_position ) VALUES
( 'isSocial', 0, 0, 0, 0, 0),
( 'isOffers', 0, 0, 0, 0, 0),
( 'isHomeKitchen', 0, 0, 0, 0, 0),
( 'isForum', 0, 0, 0, 0, 0),
( 'isHyperLocal', 0, 0, 0, 0, 0),
( 'isMaintenance', 0, 0, 0, 0, 0),
( 'isHostelRent', 0, 0, 0, 0, 0),
( 'isHostelRentPayOnline', 0, 0, 0, 0, 0),
( 'isComplaints', 0, 0, 0, 0, 0),
( 'isGatePass', 0, 0, 0, 0, 0),
( 'isResidentGatePass', 0, 0, 0, 0, 0),
( 'isAdvertising', 0, 0, 0, 0, 0),
( 'isParcel', 0, 0, 0, 0, 0),
( 'isExitPass', 0, 0, 0, 0, 0),
( 'isAmenitiesBooking', 0, 0, 0, 0, 0),
( 'isDocsNForms', 0, 0, 0, 0, 0),
( 'isNoticeBoard', 0, 0, 0, 0, 0),
( 'isEmergencyCalling', 0, 0, 0, 0, 0),
( 'isGuardPatrolling', 0, 0, 0, 0, 0),
( 'isFaceEntryRegular', 0, 0, 0, 0, 0),
( 'isFaceEntryResident', 0, 0, 0, 0, 0),
( 'isSuggestion', 0, 0, 0, 0, 0),
( 'isFeedback', 0, 0, 0, 0, 0),
( 'isPolling', 0, 0, 0, 0, 0),
( 'isRegularVisitor', 0, 0, 0, 0, 0),
( 'isVehicleManagement', 0, 0, 0, 0, 0),
( 'isInventory', 0, 0, 0, 0, 0),
( 'isKitchenMenu', 0, 0, 0, 0, 0),
( 'isOtherCharges', 0, 0, 0, 0, 0),
( 'isSoundBar', 0, 0, 0, 0, 0),
( 'IsStudentLeave', 0, 0, 0, 0, 0),
( 'isStudentDayPass', 0, 0, 0, 0, 0),
( 'isStudentNightPass', 0, 0, 0, 0, 0),
( 'isInstantDashboard', 0, 0, 0, 0, 0),
( 'isDeviceInstalled', 0, 0, 0, 0, 0),
( 'isMainGateAvailable', 0, 0, 0, 0, 0),
( 'isPowerControlDeviceInstalled', 0, 0, 0, 0, 0),
( 'isHostelAllotment', 0, 0, 0, 0, 0),
( 'indisciplinaryAction', 0, 0, 0, 0, 0),
( 'IsGuestBooking', 0, 0, 0, 0, 0);
--  2024-11-20
-- add is_activated
ALTER VIEW leave_request_view AS SELECT 
leave_requests.id, 
leave_requests.resident_id, 
leave_requests.residence_id, 
leave_requests.from_date, 
leave_requests.to_date, 
leave_requests.leave_time, 
leave_requests.planned_reporting_dt, 
leave_requests.reporting_dt, 
leave_requests.reporting_time, 
leave_requests.reporting_ack_by, 
leave_requests.template_id, 
leave_requests.description, 
leave_requests.flag, 
leave_requests.file_name, 
leave_requests.created_by, 
leave_requests.approved_at, 
leave_requests.approved_by, 
leave_requests.cancelled_at, 
leave_requests.cancelled_by, 
leave_requests.cancel_reason, 
leave_requests.created_at, 
leave_requests.updated_at, 
leave_requests.isActive, 
leave_requests.ex_approval_status, 
leave_requests.approval_code, 
leave_requests.ex_action_at, 
leave_requests.approver_comment, 
leave_requests.early_approve_by, 
leave_requests.early_approve_at, 
leave_requests.is_missing_academic,
leave_requests.eduhead_requested_by, 
leave_requests.eduhead_requested_at, 
leave_requests.eduhead_status, 
leave_requests.eduhead_action_at, 
leave_requests.building_exit_ack_by, 
json_extract(resident_personal_details.personal_data, "$.parents") as parents,
json_extract(resident_personal_details.personal_data, "$.local_guardians") as local_guardians,
building_exit_ack.name AS building_exit_ack_by_name,
leave_requests.building_exit_date_time, 
leave_requests.maingate_exit_ack_by, 
maingate_exit_ack.name AS maingate_exit_ack_by_name,
leave_requests.maingate_exit_at,
leave_requests.biometric_exit_at, 
leave_requests.biometric_entry_at,
complainant_view.complainant_name AS resident_name,
complainant_view.contact_no,
complainant_view.gender,
complainant_view.file,
complainant_view.ext AS ext,
complainant_view.apartment_name,
complainant_view.building_id,
complainant_view.building_name,
complainant_view.is_activated,
leave_templates.description AS template_description,
resident_only_academic_view.reference_number AS roll_number,
resident_only_academic_view.course_year,
resident_only_academic_view.branch,
resident_only_academic_view.course,
resident_only_academic_view.course_name,
resident_only_academic_view.branch_name,
resident_only_academic_view.head_user_id,
head_user.name AS head_user_name,
ifnull(gate_info.device_count, 0) AS device_count
FROM `leave_requests` 
LEFT JOIN complainant_view ON complainant_view.id=leave_requests.resident_id 
LEFT JOIN leave_templates ON leave_templates.id = leave_requests.template_id 
LEFT JOIN user AS building_exit_ack ON building_exit_ack.id=leave_requests.building_exit_ack_by
LEFT JOIN user AS maingate_exit_ack ON maingate_exit_ack.id=leave_requests.maingate_exit_ack_by
LEFT JOIN resident_personal_details ON resident_personal_details.resident_id=leave_requests.resident_id
LEFT JOIN resident_only_academic_view ON leave_requests.resident_id=resident_only_academic_view.resident_id
LEFT JOIN user AS head_user ON head_user.id=resident_only_academic_view.head_user_id
LEFT JOIN (
    select
        gates.building_id AS building_id,
        count(attendance_devices.id) AS device_count
    from gates
    LEFT JOIN attendance_devices on(attendance_devices.gate_id = gates.id)
    where attendance_devices.is_active = 1 and gates.gate_type = 'HostelGate'
    group by attendance_devices.gate_id, gates.building_id
) gate_info on gate_info.building_id = complainant_view.building_id;

-- 2024-11-21
DROP PROCEDURE IF EXISTS GetLeaveRequestCounts;
DELIMITER $$
CREATE PROCEDURE `GetLeaveRequestCounts`(
    IN userId INT,
    IN userRole INT,
    IN isLeaveSetting INT,
    IN isQrShow INT,
    IN isDeviceInstalled INT,
    IN isMainGateAvailable INT,
    IN userBuildings VARCHAR(100),
    IN startDt VARCHAR(30),
    IN endDt VARCHAR(30)
)
BEGIN
    -- Declare local variables for counts
    DECLARE expiredCount INT DEFAULT 0;
    DECLARE pendingApprovalCount INT DEFAULT 0;
    DECLARE approvedCount INT DEFAULT 0;
    DECLARE onLeaveCount INT DEFAULT 0;
    DECLARE leavingSoonCount INT DEFAULT 0;
    DECLARE completedCount INT DEFAULT 0;
    DECLARE cancelledCount INT DEFAULT 0;
    DECLARE notReportedCount INT DEFAULT 0;
    DECLARE notOnLeaveCount INT DEFAULT 0;

    -- Apply building filter if role requires it
    SET @buildingFilter := '';
    IF userRole > 5 AND userRole < 11 THEN
        IF isLeaveSetting = 1 THEN
            SET @buildingFilter := CONCAT(' AND (is_warden_view_required = 1 AND FIND_IN_SET(', userId, ', approver_ids) > 0) ');
        ELSE
            SET @buildingFilter := IF(userBuildings IS NOT NULL, 
                CONCAT(' AND FIND_IN_SET(building_id, "', userBuildings, '")'), 
                ''
            );
        END IF;
    END IF;

    IF userRole = 12 THEN
        IF isLeaveSetting = 1 THEN
            SET @buildingFilter := CONCAT(' AND (is_edu_head_approval_required = 1 AND FIND_IN_SET(', userId, ', approver_ids) > 0) ');
        ELSE
            SET @buildingFilter := CONCAT(' AND head_user_id = ', userId);
        END IF;
    END IF;

    -- Date range filter
    SET @dateFilter := IF(startDt IS NOT NULL AND endDt IS NOT NULL,
        CONCAT(' AND (from_date BETWEEN "', startDt, '" AND "', endDt, '" OR to_date BETWEEN "', startDt, '" AND "', endDt, '")'),
        ''
    );

    -- Counts for each status
    
    -- Expired
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @expiredCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NULL
          AND current_date > to_date
          AND isActive = 1
          ', @buildingFilter, @dateFilter
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Pending Approval
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @pendingApprovalCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NULL
          AND current_date <= to_date
          AND isActive = 1
          ', @buildingFilter, @dateFilter
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Approved
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @approvedCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NOT NULL
          AND isActive = 1
          ', @buildingFilter, @dateFilter
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- On Leave
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @onLeaveCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NOT NULL
          AND reporting_dt IS NULL
          AND isActive = 1
          AND CURRENT_DATE BETWEEN from_date AND to_date
          ', @buildingFilter
    );

    IF isQrShow = 1 THEN
        SET @sql := CONCAT(@sql, ' AND building_exit_date_time IS NOT NULL ' );
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sql := CONCAT(@sql, ' AND biometric_exit_at IS NOT NULL ' );
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sql := CONCAT(@sql, ' AND ((device_count < 2) OR (device_count > 1 AND biometric_exit_at IS NOT NULL)) ' );
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Leaving Soon
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @leavingSoonCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NOT NULL
          AND isActive = 1
          ', @buildingFilter
    );

    IF isQrShow = 1 THEN
        SET @sql := CONCAT(@sql, ' AND building_exit_date_time IS NULL
         AND current_date <= leave_request_view.to_date ' );
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sql := CONCAT(@sql, ' AND biometric_exit_at IS NULL
         AND current_date <= leave_request_view.to_date ' );
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sql := CONCAT(@sql, ' AND ((device_count > 1 AND current_date <= leave_request_view.to_date AND biometric_exit_at IS NULL)
         OR (device_count < 2 AND current_date < leave_request_view.from_date)) 
          AND current_date <= leave_request_view.to_date ' );
    ELSE
        SET @sql := CONCAT(@sql, ' AND current_date < leave_request_view.from_date ' );
    END IF;

    SET @sql := CONCAT(@sql, ' AND reporting_dt IS NULL ' );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Completed
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @completedCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NOT NULL
          AND isActive = 1
          ', @buildingFilter
    );

    IF isQrShow = 1 THEN
        SET @sql := CONCAT(@sql, ' AND leave_request_view.reporting_dt IS NOT NULL ');
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sql := CONCAT(@sql, ' AND biometric_entry_at IS NOT NULL ');
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sql := CONCAT(@sql, ' AND ((device_count > 1 AND biometric_entry_at IS NOT NULL) OR (device_count < 2 AND leave_request_view.planned_reporting_dt<=current_date)) ' );
    ELSE
        SET @sql := CONCAT(@sql, ' AND (leave_request_view.reporting_dt IS NOT NULL OR leave_request_view.planned_reporting_dt <= current_date) ');
    END IF;

    SET @sql := CONCAT(@sql, ' AND (DATEDIFF(current_date,leave_request_view.created_at)<60) ');
    

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Cancelled
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @cancelledCount
        FROM leave_request_view
        WHERE cancelled_at IS NOT NULL
          ', @buildingFilter, @dateFilter
    );
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Not Reported
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @notReportedCount
        FROM leave_request_view
        WHERE cancelled_at IS NULL
          AND approved_at IS NOT NULL
          AND isActive = 1
          ', @buildingFilter
    );

    IF isQrShow = 1 THEN
        SET @sql := CONCAT(@sql, ' AND leave_request_view.reporting_dt IS NULL AND (leave_request_view.planned_reporting_dt < current_date) ');
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sql := CONCAT(@sql, ' AND biometric_entry_at IS NULL AND (leave_request_view.planned_reporting_dt < current_date) ');
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sql := CONCAT(@sql, ' AND (device_count > 1 AND biometric_entry_at IS NULL) AND (leave_request_view.planned_reporting_dt < current_date) ');
    ELSE
        SET @sql := CONCAT(@sql, ' AND (leave_request_view.planned_reporting_dt < current_date AND leave_request_view.reporting_dt IS NULL) ');
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Residents Not on Leave 
    SET @sql := CONCAT('
        SELECT COUNT(*) INTO @notOnLeaveCount
        FROM complainant_view
        LEFT JOIN resident_academic_view ON resident_academic_view.resident_id = complainant_view.id
        WHERE complainant_view.id NOT IN (
            SELECT leave_requests.resident_id
            FROM leave_requests
            WHERE leave_requests.approved_at IS NOT NULL
              AND leave_requests.cancelled_at IS NULL
              AND CURDATE() BETWEEN leave_requests.from_date AND leave_requests.to_date
        )
        AND complainant_view.resident_status = 6
        AND complainant_view.is_activated = "A"
    ');
    IF userRole > 5 AND userRole < 11 AND userBuildings IS NOT NULL THEN
        SET @sql := CONCAT(@sql, ' AND FIND_IN_SET(complainant_view.building_id, "', userBuildings, '")' );
    END IF;

    IF userRole = 12 THEN
        SET @sql := CONCAT(@sql, ' AND resident_academic_view.head_user_id = ', userId);
    END IF;


    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Return results as a single row
    SELECT 
        @expiredCount AS expired,
        @pendingApprovalCount AS pending_approval,
        @approvedCount AS approved,
        @onLeaveCount AS on_leave,
        @leavingSoonCount AS leaving_soon,
        @completedCount AS completed,
        @cancelledCount AS cancelled,
        @notReportedCount AS not_reported,
        @notOnLeaveCount AS not_on_leave;
END$$
DELIMITER ;



-- add setting to bypass the bio-metric checking on leave
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
(NULL, 'LEAVE', 'is_bio_metric_check_skip_for_leave', 'Bio-metric Check Skip For Leave', 'boolean', '0', '1', '2024-11-21 16:29:38');

-- 2024-11-23
DROP PROCEDURE IF EXISTS GetLeaveAnalytics;
DELIMITER $$
CREATE PROCEDURE `GetLeaveAnalytics`(
    IN userId INT,
    IN userRole INT,
    IN isLeaveSetting INT,
    IN isQrShow INT,
    IN isDeviceInstalled INT,
    IN isMainGateAvailable INT,
    IN userBuildings VARCHAR(100),
    IN startDt VARCHAR(30),
    IN endDt VARCHAR(30)
)
NO SQL BEGIN 

    SET @sqlOnGoing := "";
    IF isQrShow = 1 THEN
        SET @sqlOnGoing := CONCAT(@sqlOnGoing, " AND building_exit_date_time IS NOT NULL " );
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sqlOnGoing := CONCAT(@sqlOnGoing, " AND biometric_exit_at IS NOT NULL " );
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sqlOnGoing := CONCAT(@sqlOnGoing, " AND ((device_count < 2) OR (device_count > 1 AND biometric_exit_at IS NOT NULL)) " );
    END IF;

    SET @sqlLeavingSoon := "";
    IF isQrShow = 1 THEN
        SET @sqlLeavingSoon := CONCAT(@sqlLeavingSoon, " AND building_exit_date_time IS NULL
         AND current_date <= leave_request_view.to_date " );
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sqlLeavingSoon := CONCAT(@sqlLeavingSoon, " AND biometric_exit_at IS NULL
         AND current_date <= leave_request_view.to_date " );
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sqlLeavingSoon := CONCAT(@sqlLeavingSoon, " AND ((device_count > 1 AND current_date <= leave_request_view.to_date AND biometric_exit_at IS NULL)
         OR (device_count < 2 AND current_date < leave_request_view.from_date)) 
          AND current_date <= leave_request_view.to_date " );
    ELSE
        SET @sqlLeavingSoon := CONCAT(@sqlLeavingSoon, " AND current_date < leave_request_view.from_date " );
    END IF;

    SET @sqlCompleted := "";
    IF isQrShow = 1 THEN
        SET @sqlCompleted := CONCAT(@sqlCompleted, ' AND leave_request_view.reporting_dt IS NOT NULL ');
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sqlCompleted := CONCAT(@sqlCompleted, ' AND biometric_entry_at IS NOT NULL ');
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sqlCompleted := CONCAT(@sqlCompleted, ' AND ((device_count > 1 AND biometric_entry_at IS NOT NULL) OR (device_count < 2 AND leave_request_view.planned_reporting_dt<=current_date)) ' );
    ELSE
        SET @sqlCompleted := CONCAT(@sqlCompleted, ' AND (leave_request_view.reporting_dt IS NOT NULL OR leave_request_view.planned_reporting_dt <= current_date) ');
    END IF;

    SET @sqlNotReported := "";
    IF isQrShow = 1 THEN
        SET @sqlNotReported := CONCAT(@sqlNotReported, ' AND leave_request_view.reporting_dt IS NULL AND (leave_request_view.planned_reporting_dt <= current_date) ');
    ELSEIF isDeviceInstalled = 1 AND isMainGateAvailable = 1 THEN
        SET @sqlNotReported := CONCAT(@sqlNotReported, ' AND biometric_entry_at IS NULL AND (leave_request_view.planned_reporting_dt <= current_date) ');
    ELSEIF isDeviceInstalled = 1 THEN
        SET @sqlNotReported := CONCAT(@sqlNotReported, ' AND (device_count > 1 AND biometric_entry_at IS NULL) AND (leave_request_view.planned_reporting_dt <= current_date) ');
    ELSE
        SET @sqlNotReported := CONCAT(@sqlNotReported, ' AND (leave_request_view.planned_reporting_dt <= current_date AND leave_request_view.reporting_dt IS NULL) ');
    END IF;    


    -- Apply building filter if role requires it
    SET @buildingFilter := '';
    IF userRole > 5 AND userRole < 11 THEN
        IF isLeaveSetting = 1 THEN
            SET @buildingFilter := CONCAT(' AND (is_warden_view_required = 1 AND FIND_IN_SET(', userId, ', approver_ids) > 0) ');
        ELSE
            SET @buildingFilter := IF(userBuildings IS NOT NULL, 
                CONCAT(' AND FIND_IN_SET(building_id, "', userBuildings, '")'), 
                ''
            );
        END IF;
    END IF;

    IF userRole = 12 THEN
        IF isLeaveSetting = 1 THEN
            SET @buildingFilter := CONCAT(' AND (is_edu_head_approval_required = 1 AND FIND_IN_SET(', userId, ', approver_ids) > 0) ');
        ELSE
            SET @buildingFilter := CONCAT(' AND head_user_id = ', userId);
        END IF;
    END IF;

    SET @createdDateFilter := IF(startDt IS NOT NULL AND endDt IS NOT NULL , CONCAT(" (created_at BETWEEN '", startDt, "' AND '", endDt, "' OR to_date BETWEEN '", startDt, "' AND '", endDt, "') "), "1");
    SET @leaveDateFilter := IF(startDt IS NOT NULL AND endDt IS NOT NULL , CONCAT(" (from_date BETWEEN '", startDt, "' AND '", endDt, "' OR to_date BETWEEN '", startDt, "' AND '", endDt, "') "), "1");
    -- Date range filter
    SET @dateFilter := IF(startDt IS NOT NULL AND endDt IS NOT NULL,
        CONCAT(" AND ( ", @createdDateFilter , " OR ", @leaveDateFilter , " ) "),
        ""
    );


    SET @sqlMain := CONCAT( 
    -- Expired
    " SUM(CASE WHEN ", @createdDateFilter, " AND cancelled_at IS NULL AND approved_at IS NULL AND current_date > to_date THEN 1  
        ELSE 0 END) AS  expired_counts,",
    -- Pending Approval
    " SUM(CASE WHEN ", @createdDateFilter, " AND cancelled_at IS NULL AND approved_at IS NULL AND current_date <= to_date THEN 1  
        ELSE 0 END) AS  pending_counts,",
    -- Approved
    " SUM(CASE WHEN ", @createdDateFilter, " AND cancelled_at IS NULL AND approved_at IS NOT NULL THEN 1
        ELSE 0 END) AS  approved_counts,",
    -- On Leave [ @sqlOnGoing ]
    " SUM(CASE WHEN ", @leaveDateFilter, " AND cancelled_at IS NULL AND approved_at IS NOT NULL AND reporting_dt IS NULL AND (CURRENT_DATE BETWEEN from_date AND to_date) ", @sqlOnGoing ," THEN 1
        ELSE 0 END) AS  ongoing_counts,",
    -- Leaving Soon [ @sqlLeavingSoon ]
    " SUM(CASE WHEN ", @createdDateFilter, " AND cancelled_at IS NULL AND approved_at IS NOT NULL AND reporting_dt IS NULL ", @sqlLeavingSoon ," THEN 1
        ELSE 0 END) AS  leaving_soon_counts,",
    -- Completed [ @sqlCompleted ]
    " SUM(CASE WHEN ", @createdDateFilter, " AND cancelled_at IS NULL AND approved_at IS NOT NULL ", @sqlCompleted ," THEN 1
        ELSE 0 END) AS  completed_counts,",
    -- Cancelled
    " SUM(CASE WHEN ", @createdDateFilter, " AND cancelled_at IS NOT NULL THEN 1
        ELSE 0 END) AS  cancelled_counts,",
    -- Not Reported [ @sqlNotReported ]
    " SUM(CASE WHEN ", @createdDateFilter, " AND cancelled_at IS NULL AND approved_at IS NOT NULL AND reporting_dt IS NULL ", @sqlNotReported ," THEN 1
        ELSE 0 END) AS  leaving_soon_counts",
    " FROM  leave_request_view WHERE isActive = 1 ");

    SET @sqlBuildingWise := CONCAT("SELECT building_id AS id, building_name AS name, COUNT(building_id) AS total_count, ",@sqlMain, " GROUP BY building_id ");
    PREPARE stmtBuildingWise FROM @sqlBuildingWise;
    EXECUTE stmtBuildingWise;
    DEALLOCATE PREPARE stmtBuildingWise;

    SET @sqlDayWise := CONCAT("SELECT from_date AS id, from_date AS name, COUNT(from_date) AS total_count, ",@sqlMain, " GROUP BY from_date ");
    PREPARE stmtDayWise FROM @sqlDayWise;
    EXECUTE stmtDayWise;
    DEALLOCATE PREPARE stmtDayWise;

    SET @sqlTemplateWise := CONCAT("SELECT template_id AS id, template_description AS name, COUNT(template_id) AS total_count, ",@sqlMain, " GROUP BY template_id ");
    PREPARE stmtTemplateWise FROM @sqlTemplateWise;
    EXECUTE stmtTemplateWise;
    DEALLOCATE PREPARE stmtTemplateWise;

END
DELIMITER ;
