/*2024-07-05 krishna*/
ALTER TABLE `community_occupancy_bills`
CHANGE `break_up` `break_up` LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL;

ALTER TABLE `community_occupancy_bills`
ADD `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `monthly_other_charges_id`;

ALTER TABLE `community_occupancy_bills`
CHANGE `to_date` `to_date` DATE NULL;

/*2024-07-06 krishna*/
ALTER TABLE `resident_academic_details`
ADD `batch` INT NULL AFTER `course`;

CREATE TABLE `batches` (
    `id` int(11) NOT NULL,
    `name` varchar(50) DEFAULT NULL,
    `isActive` int(11) DEFAULT 1,
    `created_at` timestamp NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `batches` ADD PRIMARY KEY (`id`);

ALTER TABLE `batches`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT = 6;

COMMIT;

/* 07/09/2024 */
ALTER TABLE `maintenance_charge_rate`
CHANGE `effective_date` `effective_date` DATE NULL;

DROP PROCEDURE IF EXISTS `unitMaintenanceCharges`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `unitMaintenanceCharges`(IN `in_unit_id` SMALLINT)
    NO SQL
BEGIN
set @sql="SELECT building_unit_types.unit_type_id,current_charge.master_session_id,current_charge.building_id,Description,size,current_charge.amount,current_charge.amount_daily,current_charge.amount_session,current_charge.currency,current_charge.effective_date,current_charge.created_at,current_charge.created_by,new_charge.amount as new_amount,new_charge.amount_daily as new_amount_daily,new_charge.amount_session as new_amount_session,new_charge.effective_date as new_effective_date,new_charge.created_at as new_created_at FROM  (SELECT unit_type,maintenance_charge_rate.building_id,maintenance_charge_rate.master_session_id,amount,amount_daily,amount_session,currency,effective_date,maintenance_charge_rate.created_at,user.name as created_by from maintenance_charge_rate  inner join user on user.id=maintenance_charge_rate.created_by where charge_id in (SELECT max(charge_id) FROM `maintenance_charge_rate` where effective_date<=CURRENT_DATE GROUP by unit_type,building_id)) current_charge inner join building_unit_types  on current_charge.unit_type=building_unit_types.unit_type_id left join (SELECT unit_type,building_id,amount,amount_daily,amount_session,currency,effective_date,created_at from maintenance_charge_rate where charge_id in (SELECT min(charge_id) FROM `maintenance_charge_rate` where effective_date>CURRENT_DATE GROUP by unit_type,building_id)) new_charge on new_charge.unit_type=current_charge.unit_type and new_charge.building_id=current_charge.building_id where building_unit_types.isActive=1 ";
 
IF ( in_unit_id >0 ) THEN
	SET @sql = CONCAT(@sql,' and  building_unit_types.unit_type_id =',in_unit_id);
END IF;

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

DELIMITER;

-- 2024-07-10 update sp getResidentOccupancyBills for session name and course id

DROP PROCEDURE IF EXISTS `getResidentOccupancyBills`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentOccupancyBills`(IN `in_resident_id` INT, IN `in_bill_id` INT)
    NO SQL
BEGIN


IF(in_bill_id >0) then
	SELECT community_occupancy_bills.*,resident_academic_details.course,yearly_session.session_name,yearly_session.start_date as session_start_date,yearly_session.end_date as session_end_date,building_residence.apartment_name,building_residence.building_id,building.name as building,complainants.complainant_name as name,complainants.email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status from community_occupancy_bills

    INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
     
    left JOIN complainants on complainants.id=community_occupancy_bills.resident_id
   	left join user on user.id=community_occupancy_bills.approved_by
    left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
    left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
    left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
    left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.resident_id
    WHERE community_occupancy_bills.bill_id=in_bill_id ;
elseif(in_resident_id >0) then
SELECT community_occupancy_bills.*,resident_academic_details.course,yearly_session.session_name,building_residence.apartment_name,building_residence.building_id,building.name as building,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.resident_id
WHERE community_occupancy_bills.resident_id=in_resident_id and community_occupancy_bills.approved_at is not null ORDER by community_occupancy_bills.bill_id DESC;
else

SELECT community_occupancy_bills.*,resident_academic_details.course,yearly_session.session_name,building_residence.apartment_name,building_residence.building_id,building.name as building,building_residence.unit_type,building_unit_types.Description as unit_description,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status,approver.name as approved_by_name,reviewer.name as cancelled_by_name from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER join building_unit_types on building_unit_types.unit_type_id=building_residence.unit_type
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
 left join user as approver on approver.id=community_occupancy_bills.approved_by
 left join user as reviewer on reviewer.id=community_occupancy_bills.cancelled_by
 left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
 left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.resident_id
 ORDER by community_occupancy_bills.bill_id DESC;
end if;
END$$

DELIMITER;

--  2024-07-11 update sp getResidentOccupancyBills for course data (need course name for occupancyBillPDF)

DROP PROCEDURE IF EXISTS `getResidentOccupancyBills`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentOccupancyBills`(IN `in_resident_id` INT, IN `in_bill_id` INT)
    NO SQL
BEGIN


IF(in_bill_id >0) then
	SELECT community_occupancy_bills.*,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,yearly_session.start_date as session_start_date,yearly_session.end_date as session_end_date,building_residence.apartment_name,building_residence.building_id,building.name as building,complainants.complainant_name as name,complainants.email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status from community_occupancy_bills

    INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
     
    left JOIN complainants on complainants.id=community_occupancy_bills.resident_id
   	left join user on user.id=community_occupancy_bills.approved_by
    left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
    left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
    left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
    left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.resident_id
    left join academic_courses on academic_courses.id = resident_academic_details.course
    WHERE community_occupancy_bills.bill_id=in_bill_id ;
elseif(in_resident_id >0) then
SELECT community_occupancy_bills.*,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,building_residence.apartment_name,building_residence.building_id,building.name as building,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.resident_id
left join academic_courses on academic_courses.id = resident_academic_details.course
WHERE community_occupancy_bills.resident_id=in_resident_id and community_occupancy_bills.approved_at is not null ORDER by community_occupancy_bills.bill_id DESC;
else

SELECT community_occupancy_bills.*,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,building_residence.apartment_name,building_residence.building_id,building.name as building,building_residence.unit_type,building_unit_types.Description as unit_description,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status,approver.name as approved_by_name,reviewer.name as cancelled_by_name from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER join building_unit_types on building_unit_types.unit_type_id=building_residence.unit_type
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
 left join user as approver on approver.id=community_occupancy_bills.approved_by
 left join user as reviewer on reviewer.id=community_occupancy_bills.cancelled_by
 left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
 left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.resident_id
 left join academic_courses on academic_courses.id = resident_academic_details.course
 ORDER by community_occupancy_bills.bill_id DESC;
end if;
END$$

DELIMITER;

-- 12/07/2024 update sp getResidentOccupancyBills for branch name, course year and batch data (need course name for occupancyBillPDF)

DROP PROCEDURE IF EXISTS `getResidentOccupancyBills`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentOccupancyBills`(IN `in_resident_id` INT, IN `in_bill_id` INT)
    NO SQL
BEGIN


IF(in_bill_id >0) then
	SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,yearly_session.start_date as session_start_date,yearly_session.end_date as session_end_date,building_residence.apartment_name,building_residence.building_id,building.name as building,complainants.complainant_name as name,complainants.email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status from community_occupancy_bills

    INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
     
    left JOIN complainants on complainants.id=community_occupancy_bills.resident_id
   	left join user on user.id=community_occupancy_bills.approved_by
    left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
    left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
    left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
    left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
    WHERE community_occupancy_bills.bill_id=in_bill_id ;
elseif(in_resident_id >0) then
SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,building_residence.apartment_name,building_residence.building_id,building.name as building,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
WHERE community_occupancy_bills.resident_id=in_resident_id and community_occupancy_bills.approved_at is not null ORDER by community_occupancy_bills.bill_id DESC;
else

SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,building_residence.apartment_name,building_residence.building_id,building.name as building,building_residence.unit_type,building_unit_types.Description as unit_description,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status,approver.name as approved_by_name,reviewer.name as cancelled_by_name from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER join building_unit_types on building_unit_types.unit_type_id=building_residence.unit_type
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
 left join user as approver on approver.id=community_occupancy_bills.approved_by
 left join user as reviewer on reviewer.id=community_occupancy_bills.cancelled_by
 left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
 left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
 ORDER by community_occupancy_bills.bill_id DESC;
end if;
END$$

DELIMITER;

-- 17/07/2024 update sp getResidentOccupancyBills for branch name, reference_number AND setting reference_no_alias (need course name for occupancyBillPDF)

DROP PROCEDURE IF EXISTS `getResidentOccupancyBills`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentOccupancyBills`(IN `in_resident_id` INT, IN `in_bill_id` INT)
    NO SQL
BEGIN


IF(in_bill_id >0) then
    SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,resident_academic_details.reference_number,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,yearly_session.start_date as session_start_date,yearly_session.end_date as session_end_date,building_residence.apartment_name,building_residence.building_id,building.name as building,complainants.complainant_name as name,complainants.email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status from community_occupancy_bills

    INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
     
    left JOIN complainants on complainants.id=community_occupancy_bills.resident_id
    left join user on user.id=community_occupancy_bills.approved_by
    left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
    left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
    left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
    left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
    WHERE community_occupancy_bills.bill_id=in_bill_id ;
elseif(in_resident_id >0) then
SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,resident_academic_details.reference_number,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,building_residence.apartment_name,building_residence.building_id,building.name as building,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
WHERE community_occupancy_bills.resident_id=in_resident_id and community_occupancy_bills.approved_at is not null ORDER by community_occupancy_bills.bill_id DESC;
else

SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,resident_academic_details.reference_number,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,building_residence.apartment_name,building_residence.building_id,building.name as building,building_residence.unit_type,building_unit_types.Description as unit_description,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status,approver.name as approved_by_name,reviewer.name as cancelled_by_name from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER join building_unit_types on building_unit_types.unit_type_id=building_residence.unit_type
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
 left join user as approver on approver.id=community_occupancy_bills.approved_by
 left join user as reviewer on reviewer.id=community_occupancy_bills.cancelled_by
 left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
 left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
 ORDER by community_occupancy_bills.bill_id DESC;
end if;
END$$

DELIMITER;

-- 19/07/2024 update sp getResidentOccupancyBills for branch name, reference_number AND setting reference_no_alias (need course name for occupancyBillPDF)

DROP PROCEDURE IF EXISTS `getResidentOccupancyBills`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentOccupancyBills`(IN `in_resident_id` INT, IN `in_bill_id` INT)
    NO SQL
BEGIN


IF(in_bill_id >0) then
	SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,resident_academic_details.registration_no,resident_academic_details.reference_number,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,yearly_session.start_date as session_start_date,yearly_session.end_date as session_end_date,building_residence.apartment_name,building_residence.unit_type,building_residence.building_id,building.name as building,complainants.complainant_name as name,complainants.email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status from community_occupancy_bills

    INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
     
    left JOIN complainants on complainants.id=community_occupancy_bills.resident_id
   	left join user on user.id=community_occupancy_bills.approved_by
    left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
    left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
    left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
    left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
    WHERE community_occupancy_bills.bill_id=in_bill_id ;
elseif(in_resident_id >0) then
SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,resident_academic_details.registration_no,resident_academic_details.reference_number,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,building_residence.unit_type,building_residence.apartment_name,building_residence.building_id,building.name as building,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
WHERE community_occupancy_bills.resident_id=in_resident_id and community_occupancy_bills.approved_at is not null ORDER by community_occupancy_bills.bill_id DESC;
else

SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,resident_academic_details.registration_no,resident_academic_details.reference_number,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,building_residence.apartment_name,building_residence.building_id,building.name as building,building_residence.unit_type,building_unit_types.Description as unit_description,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status,approver.name as approved_by_name,reviewer.name as cancelled_by_name from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER join building_unit_types on building_unit_types.unit_type_id=building_residence.unit_type
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
 left join user as approver on approver.id=community_occupancy_bills.approved_by
 left join user as reviewer on reviewer.id=community_occupancy_bills.cancelled_by
 left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
 left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
 ORDER by community_occupancy_bills.bill_id DESC;
end if;
END$$

DELIMITER;

-- 20/07/2024 --adding building id in getVehicles sp for check building permision
DROP PROCEDURE IF EXISTS `getVehicles`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getVehicles`(IN `in_id` INT, IN `in_resident_id` INT)
    NO SQL
BEGIN
SET @sql = 'select resident_vehicles.*,building.id as building_id,vehicle_pass.pass_no,vehicle_pass.pass_status,complainants.complainant_name as resident_name,complainants.contact_no,building_residence.apartment_name,building.name as building_name,user.name as added_by_name,role.role_name from resident_vehicles\r\nINNER join complainants on complainants.id=resident_vehicles.resident_id \r\ninner join user on user.id=resident_vehicles.added_by \r\ninner join role on role.role_id=user.userRole left join resident_residences on resident_residences.resident_id =complainants.id and resident_residences.isActive=1 left join building_residence on building_residence.id=resident_residences.residence_id\r\nleft join building on building.id=building_residence.building_id\r\nleft join vehicle_pass on vehicle_pass.vehicle_id=resident_vehicles.id and vehicle_pass.pass_status=1';

IF ( in_id > 0) THEN
	SET @sql = CONCAT(@sql,' where resident_vehicles.id =',in_id);
END IF;

IF ( in_resident_id > 0) THEN
	SET @sql = CONCAT(@sql,' where resident_vehicles.resident_id =',in_resident_id);
END IF;
SET @sql = CONCAT(@sql,' order by resident_vehicles.id desc');

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

DELIMITER;

--adding building_id (complainant_view.building_id,) in sp getResidentKycOverView for check building permisionadding building_id (complainant_view.building_id,) in sp getResidentKycOverView for check building permision

DROP PROCEDURE IF EXISTS `getResidentKycOverView`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentKycOverView`(IN `building` VARCHAR(70))
BEGIN
SET @sql =  "SELECT\r\n    complainant_view.id AS resident_id,\r\n    complainant_view.building_id,\r\n    complainant_name AS name,\r\n    contact_no,\r\n    complainant_view.building_name,\r\n    complainant_view.apartment_name,\r\n    p_doc.uplaoded_doc_id,\r\n    uplaoded_doc_name,\r\n    p_doc.uplaoded_doc_file_name\r\nFROM\r\n    complainant_view\r\nLEFT JOIN (\r\n    SELECT\r\n        resident_id,\r\n        GROUP_CONCAT(resident_kyc.resident_profile_setting_id) AS uplaoded_doc_id,\r\n   \t\t GROUP_CONCAT(resident_profile_settings.name) AS uplaoded_doc_name,\r\n     \tGROUP_CONCAT(resident_kyc.file_name) AS uplaoded_doc_file_name\r\n    FROM\r\n        resident_kyc\r\n    INNER JOIN\r\n        resident_profile_settings ON resident_profile_settings.id = resident_kyc.resident_profile_setting_id\r\n    WHERE\r\n        resident_profile_settings.type = 'doc'\r\n    GROUP BY\r\n        resident_id\r\n) AS p_doc ON complainant_view.id = p_doc.resident_id";

 IF building IS NOT NULL AND building != "" AND building != 0 THEN
        SET @sql = CONCAT(@sql, " WHERE complainant_view.building_id IN (", building, ")");
END IF;

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

END$$

DELIMITER;

-- 25/07/2024
--adding (whereIn clause) and  building_ids perms with check in sp getResidentOtherCharges for building permistion

DROP PROCEDURE IF EXISTS `getResidentOtherCharges`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentOtherCharges`(IN `in_resident_id` INT, IN `building_ids` VARCHAR(200))
    NO SQL
BEGIN
set @community_type=(select information.community_type from information where id=1);
if(@community_type='hostel') then 
set @sqlquery = 'select community_other_charge_details.*,complainants.complainant_name as resident_name,complainants.contact_no,complainants.email,building_residence.apartment_name,building.name as building_name,community_charge_types.charge_text,community_other_charges.*,approver.name as approved_by_name,payer.name as paid_by_name from `community_other_charge_details`\r\nINNER join community_other_charges on community_other_charges.charge_id=community_other_charge_details.other_charge_id\r\nINNER join community_charge_types on community_charge_types.charge_type_id=community_other_charges.charge_type\r\nINNER join building_residence on building_residence.id=community_other_charge_details.residence_id\r\nINNER JOIN building on building.id=building_residence.building_id\r\ninner join complainants on complainants.id=community_other_charge_details.resident_id\r\nLEFT join user payer on payer.id=community_other_charge_details.paid_by\r\nleft join user approver on approver.id=community_other_charge_details.approved_by\r\nwhere community_other_charge_details.cancelled_at is null';

    	IF building_ids IS NOT NULL AND building_ids != "" AND building_ids != 0 THEN
            SET @sqlquery := CONCAT(@sqlquery, ' AND building.id IN (', building_ids, ')');
        END IF;

        SET @sqlquery := CONCAT(@sqlquery, ' AND community_other_charge_details.resident_id = ', in_resident_id);
        SET @sqlquery := CONCAT(@sqlquery, ' ORDER BY community_other_charge_details.bill_id DESC');

        PREPARE stmt FROM @sqlquery;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
else 
set @residence_id=(SELECT resident_residences.residence_id  from resident_residences where resident_residences.isActive=1 and  resident_residences.resident_id=in_resident_id);
select community_other_charge_details.*,community_charge_types.charge_text,community_other_charges.*,approver.name as approved_by_name,payer.name as paid_by_name from `community_other_charge_details`
INNER join community_other_charges on community_other_charges.charge_id=community_other_charge_details.other_charge_id
INNER join community_charge_types on community_charge_types.charge_type_id=community_other_charges.charge_type
LEFT join user payer on payer.id=community_other_charge_details.paid_by
left join user approver on approver.id=community_other_charge_details.approved_by
where community_other_charge_details.residence_id=@residence_id and community_other_charge_details.cancelled_at is null;

end if;

END$$

DELIMITER;

-- add in variable building.name as building_id, in sp getOtherCharges

DROP PROCEDURE IF EXISTS `getOtherCharges`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getOtherCharges`(IN `in_charge_ids` JSON)
    NO SQL
BEGIN
if in_charge_ids is NULL OR in_charge_ids='' THEN
	SELECT community_other_charges.*,community_charge_types.charge_text,creator.name as created_by_name,building.name as building_name,building.name as building_id,count(community_other_charge_details.bill_id) as impacted_units FROM `community_other_charges`
INNER join community_charge_types on community_charge_types.charge_type_id=community_other_charges.charge_type
INNER join user creator on creator.id=community_other_charges.created_by
left join building on building.id=community_other_charges.building_id
left join community_other_charge_details on community_other_charge_details.other_charge_id=community_other_charges.charge_id and community_other_charge_details.cancelled_by is null
GROUP by community_other_charge_details.other_charge_id;
ELSE
	SELECT community_other_charges.*,community_charge_types.charge_text,creator.name as created_by_name,building.name as building_name,building.name as building_id,count(community_other_charge_details.bill_id) as impacted_units FROM `community_other_charges`
INNER join community_charge_types on community_charge_types.charge_type_id=community_other_charges.charge_type
INNER join user creator on creator.id=community_other_charges.created_by
left join building on building.id=community_other_charges.building_id
left join community_other_charge_details on community_other_charge_details.other_charge_id=community_other_charges.charge_id and community_other_charge_details.cancelled_by is null
where FIND_IN_SET(community_other_charges.charge_id, in_charge_ids)
GROUP by community_other_charge_details.other_charge_id;
end if ;

END$$

DELIMITER;

-- 05-08-2024
ALTER TABLE `day_out_requests`
ADD `building_exit_ack_by` INT NULL AFTER `arrival_id`,
ADD `building_exit_date_time` DATETIME NULL AFTER `building_exit_ack_by`,
ADD `maingate_exit_ack_by` INT NULL AFTER `building_exit_date_time`,
ADD `maingate_exit_at` DATE NULL AFTER `maingate_exit_ack_by`;

-- 06-08-2024
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,json_extract(json_unquote(json_extract(gate_pass.residence_actions,'$.",@residence_id,"')),'$.action') as residence_status,gate_pass.status as security_status,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,')');
        SET @sql = CONCAT(@sql, ' AND gate_pass.resident_id = ', in_resident_id);
END IF;
SET @sql = CONCAT(@sql,' order by gate_pass.id desc');

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

DELIMITER;

-- adding building id
DROP PROCEDURE IF EXISTS `GetAllResidentGuestPass`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetAllResidentGuestPass` (IN `records` VARCHAR(10), IN `result_from` DATE, IN `result_to` DATE, IN `records_limit` INT)  NO SQL BEGIN
SET @sql = "select gate_pass.id,building_residence.building_id,gate_pass.visitor_id,ifnull(expected_dt,'') as expected_dt,ifnull(expected_tm,'') as expected_tm,\r\n\t\t\tifnull(entry_dt,'') as entry_dt, ifnull(entry_tm,'') as entry_tm,ifnull(exit_dt,'') as exit_dt,\r\n\t\t\tifnull(exit_tm,'') as exit_tm,residences,residence_actions,entry_approved_by,extra_pax,(CASE WHEN complainants.complainant_name is not null THEN complainants.complainant_name ELSE gate_pass.to_meet  END) AS to_meet,purpose,vehicle,gate_pass.address,gate_pass.status,visitors.name,visitors.contact_no,visitors.gender,visitors.id_no,identity_proof_type.description as id_description,visitors.identity_proof_type,visitors.file,visitors.file_ext,complainants.complainant_name,complainants.contact_no as complainant_contact,user.name as permit_user from gate_pass\r\n\t\t\tleft join complainants on complainants.id=gate_pass.resident_id\r\n\r\n            \r\n  left join building_residence on FIND_IN_SET(building_residence.id, REPLACE(REPLACE(gate_pass.residences, '[', ''), ']', '')) > 0           left join user on user.id=gate_pass.permit_user\r\nLEFT JOIN visitors on visitors.id=gate_pass.visitor_id\r\nleft join identity_proof_type on identity_proof_type.id=visitors.identity_proof_type\r\n\t\t\tWHERE 1=1";

IF (records ='ENTRY') THEN
	SET @sql = CONCAT(@sql," AND gate_pass.status in ('NEW','APPROVAL_PENDING','CANCELLED' ) and ABS(TIMESTAMPDIFF(HOUR,TIMESTAMP(expected_dt,expected_tm),now()))<8");

ELSEIF ( result_from !='0000-00-00' AND result_to !='0000-00-00') THEN
	SET @sql = CONCAT(@sql," AND gate_pass.expected_dt between '",result_from,"' and '",result_to,"'");
ELSEIF (result_from !='0000-00-00') THEN
	SET @sql = CONCAT(@sql," AND gate_pass.expected_dt >='",result_from,"'");
ELSEIF (result_to !='0000-00-00') THEN
	SET @sql = CONCAT(@sql," AND gate_pass.expected_dt <='",result_to,"'");
END IF;


SET @sql = CONCAT(@sql,' order by  gate_pass.expected_dt desc,gate_pass.expected_tm desc');
IF ( records_limit != '' ) THEN
	SET @sql = CONCAT(@sql,' limit ',records_limit);
END IF;

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

DELIMITER;

-- 10/08/2024

ALTER TABLE `gate_pass`
ADD `entry_approve_user` INT NULL AFTER `status`,
ADD `rejected_at` TIMESTAMP NULL AFTER `entry_approve_user`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetAllResidentGuestPass`(IN `records` VARCHAR(10), IN `result_from` DATE, IN `result_to` DATE, IN `records_limit` INT)
    NO SQL
BEGIN
SET @sql = "select gate_pass.id,building_residence.building_id,gate_pass.visitor_id,ifnull(expected_dt,'') as expected_dt,ifnull(expected_tm,'') as expected_tm,\r\n\t\t\tifnull(entry_dt,'') as entry_dt, ifnull(entry_tm,'') as entry_tm,ifnull(exit_dt,'') as exit_dt,\r\n\t\t\tifnull(exit_tm,'') as exit_tm,residences,residence_actions,entry_approved_by,extra_pax,(CASE WHEN complainants.complainant_name is not null THEN complainants.complainant_name ELSE gate_pass.to_meet  END) AS to_meet,purpose,vehicle,gate_pass.address,gate_pass.status,visitors.name,visitors.contact_no,visitors.gender,visitors.id_no,identity_proof_type.description as id_description,visitors.identity_proof_type,visitors.file,visitors.file_ext,complainants.complainant_name,complainants.contact_no as complainant_contact,user.name as permit_user,entry_approve_users.name as entry_approve_user_name,gate_pass.rejected_at from gate_pass\r\n\t\t\tleft join complainants on complainants.id=gate_pass.resident_id\r\n\r\n            \r\n  left join building_residence on FIND_IN_SET(building_residence.id, REPLACE(REPLACE(gate_pass.residences, '[', ''), ']', '')) > 0           left join user as entry_approve_users on entry_approve_users.id=gate_pass.entry_approve_user left join user on user.id=gate_pass.permit_user\r\nLEFT JOIN visitors on visitors.id=gate_pass.visitor_id\r\nleft join identity_proof_type on identity_proof_type.id=visitors.identity_proof_type\r\n\t\t\tWHERE 1=1";

IF (records ='ENTRY') THEN
	SET @sql = CONCAT(@sql," AND gate_pass.status in ('NEW','APPROVAL_PENDING','CANCELLED' ) and ABS(TIMESTAMPDIFF(HOUR,TIMESTAMP(expected_dt,expected_tm),now()))<8");

ELSEIF ( result_from !='0000-00-00' AND result_to !='0000-00-00') THEN
	SET @sql = CONCAT(@sql," AND gate_pass.expected_dt between '",result_from,"' and '",result_to,"'");
ELSEIF (result_from !='0000-00-00') THEN
	SET @sql = CONCAT(@sql," AND gate_pass.expected_dt >='",result_from,"'");
ELSEIF (result_to !='0000-00-00') THEN
	SET @sql = CONCAT(@sql," AND gate_pass.expected_dt <='",result_to,"'");
END IF;


SET @sql = CONCAT(@sql,' order by  gate_pass.expected_dt desc,gate_pass.expected_tm desc');
IF ( records_limit != '' ) THEN
	SET @sql = CONCAT(@sql,' limit ',records_limit);
END IF;

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

DELIMITER;

-- 12/08/2024
ALTER TABLE `gate_pass`
CHANGE `updated_at` `updated_at` DATETIME on update CURRENT_TIMESTAMP NULL DEFAULT NULL;

-- 13/08/2024

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetAllResidentGuestPass`(IN `records` VARCHAR(10), IN `result_from` DATE, IN `result_to` DATE, IN `records_limit` INT)
    NO SQL
BEGIN
SET @sql = "select gate_pass.id,building_residence.building_id,gate_pass.visitor_id,ifnull(expected_dt,'') as expected_dt,ifnull(expected_tm,'') as expected_tm,\r\n\t\t\tifnull(entry_dt,'') as entry_dt, ifnull(entry_tm,'') as entry_tm,ifnull(exit_dt,'') as exit_dt,\r\n\t\t\tifnull(exit_tm,'') as exit_tm,residences,residence_actions,entry_approved_by,extra_pax,(CASE WHEN complainants.complainant_name is not null THEN complainants.complainant_name ELSE gate_pass.to_meet  END) AS to_meet,purpose,vehicle,gate_pass.address,gate_pass.status,visitors.name,visitors.contact_no,visitors.gender,visitors.id_no,identity_proof_type.description as id_description,visitors.identity_proof_type,visitors.file,visitors.file_ext,complainants.complainant_name,complainants.contact_no as complainant_contact,user.name as permit_user,entry_approve_users.name as entry_approve_user_name,gate_pass.rejected_at,exitUsers.name as exitUser,gate_pass.updated_at from gate_pass\r\n\t\t\tleft join complainants on complainants.id=gate_pass.resident_id\r\n\r\n            \r\n  left join building_residence on FIND_IN_SET(building_residence.id, REPLACE(REPLACE(gate_pass.residences, '[', ''), ']', '')) > 0           left join user as entry_approve_users on entry_approve_users.id=gate_pass.entry_approve_user left join user on user.id=gate_pass.permit_user\r\nLEFT JOIN visitors on visitors.id=gate_pass.visitor_id\r\nleft join identity_proof_type on identity_proof_type.id=visitors.identity_proof_type LEFT JOIN user AS exitUsers ON exitUsers.id = gate_pass.exit_user\r\n\t\t\tWHERE 1=1";

IF (records ='ENTRY') THEN
    SET @sql = CONCAT(@sql," AND gate_pass.status in ('NEW','APPROVAL_PENDING','CANCELLED' ) and ABS(TIMESTAMPDIFF(HOUR,TIMESTAMP(expected_dt,expected_tm),now()))<8");

ELSEIF ( result_from !='0000-00-00' AND result_to !='0000-00-00') THEN
    SET @sql = CONCAT(@sql," AND gate_pass.expected_dt between '",result_from,"' and '",result_to,"'");
ELSEIF (result_from !='0000-00-00') THEN
    SET @sql = CONCAT(@sql," AND gate_pass.expected_dt >='",result_from,"'");
ELSEIF (result_to !='0000-00-00') THEN
    SET @sql = CONCAT(@sql," AND gate_pass.expected_dt <='",result_to,"'");
END IF;


SET @sql = CONCAT(@sql,' order by  gate_pass.expected_dt desc,gate_pass.expected_tm desc');
IF ( records_limit != '' ) THEN
	SET @sql = CONCAT(@sql,' limit ',records_limit);
END IF;

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

DELIMITER;

-- 16/08/2024

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,REPLACE(json_extract(json_unquote(json_extract(gate_pass.residence_actions, '$.", @residence_id, "')), '$.action'), '\"', '') AS residence_status,gate_pass.status as security_status,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,')');
        SET @sql = CONCAT(@sql, ' AND gate_pass.resident_id = ', in_resident_id);
END IF;
SET @sql = CONCAT(@sql,' order by gate_pass.id desc');

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

DELIMITER;

'-- 20/08/24

ALTER TABLE `complain_headers` ADD `isActive` TINYINT NOT NULL DEFAULT '1' AFTER `closing_time`;

-- 21/08/24 add isActive field in this sp








































DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getComplainSubjectsWithHeader`()
    NO SQL
BEGIN

SELECT * from complain_headers;

SELECT subject_id,subject_eng,subject_hindi,subject_mr,scope,isNotify,header_id,priority,isActive FROM `complain_subjects`;

END$$

DELIMITER;

-- 22/08/2024

DROP PROCEDURE IF EXISTS `InsertDepartment`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertDepartment`(IN `dname` VARCHAR(70), OUT `deptid` INT)
BEGIN
INSERT INTO `department`(department_name) VALUES (dname);
SET deptid = LAST_INSERT_ID();
END$$

DELIMITER;

-- add left join for dept_id

DROP PROCEDURE IF EXISTS `getComplainSubjectsWithHeader`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getComplainSubjectsWithHeader`()
    NO SQL
BEGIN

SELECT * from complain_headers;

SELECT complain_subjects.subject_id,complain_subjects.subject_eng,complain_subjects.subject_hindi,complain_subjects.subject_mr,complain_subjects.scope,complain_subjects.isNotify,complain_subjects.header_id,complain_subjects.priority,complain_subjects.isActive,department_subjects.dept_id FROM `complain_subjects`
left join department_subjects on department_subjects.subject_id=complain_subjects.subject_id;

END$$

DELIMITER;

-- 24/08/2024
DROP PROCEDURE IF EXISTS `GetSuggestion`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSuggestion`(IN `suggestion_id` BIGINT, IN `in_limit` INT)
BEGIN
SET @sql = "SELECT s.suggestion_id,br.apartment_name AS apartmentName, b.name AS buildingName, bu.Description AS unitType,(case when s.suggestiont_id>0 then 'resident' else 'team' end) as raised_by,(case when s.isUser=1 then user.name else c.complainant_name end) as name,(case when s.isUser=1 then user.phoneNumber else c.contact_no end) as contact_no,(case when s.isUser=1 then user.email else c.email end) as email,c.gender,s.suggestion_text,s.management_comment,suggestion_date,s.quality_remark,sq.quality_remark_text,count(suggestion_file.id) as file_count \r\nFROM suggestion s \r\nleft JOIN complainants c ON (c.id = s.suggestiont_id) \r\nleft JOIN suggestion_quality_codes sq ON (s.quality_remark = sq.quality_remark) \r\nleft join suggestion_file on suggestion_file.suggestion_id=s.suggestion_id \r\nleft join user on user.id=s.user_id LEFT JOIN \r\n            resident_residences rr ON rr.resident_id = c.id\r\n        LEFT JOIN \r\n            building_residence br ON rr.residence_id = br.id\r\n        LEFT JOIN \r\n            building_unit_types bu ON br.unit_type = bu.unit_type_id\r\n        LEFT JOIN \r\n            building b ON br.building_id = b.id";
IF ( suggestion_id >0 ) THEN
	SET @sql = CONCAT(@sql,' where s.suggestion_id =',suggestion_id);
END IF;

	SET @sql = CONCAT(@sql,' GROUP by s.suggestion_id order by s.suggestion_id DESC');
IF ( in_limit >0 ) THEN
	SET @sql = CONCAT(@sql,' limit ',in_limit);
END IF;


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

DELIMITER;

-- 26/08/2024 adding s.suggestion_time, for @parth in suggestion model
-- changing in sp GetSuggestion for give left join role with user table for user.userRole,role.role_name,

DROP PROCEDURE IF EXISTS `GetSuggestion`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSuggestion`(IN `suggestion_id` BIGINT, IN `in_limit` INT)
BEGIN
SET @sql = "SELECT s.suggestion_id,br.apartment_name AS apartmentName, b.name AS buildingName, bu.Description AS unitType,(case when s.suggestiont_id>0 then 'resident' else 'team' end) as raised_by,(case when s.isUser=1 then user.name else c.complainant_name end) as name,(case when s.isUser=1 then user.phoneNumber else c.contact_no end) as contact_no,(case when s.isUser=1 then user.email else c.email end) as email,c.gender,s.suggestion_text,s.management_comment,s.suggestion_date,s.suggestion_time,s.quality_remark,user.userRole,role.role_name,sq.quality_remark_text,count(suggestion_file.id) as file_count \r\nFROM suggestion s \r\nleft JOIN complainants c ON (c.id = s.suggestiont_id) \r\nleft JOIN suggestion_quality_codes sq ON (s.quality_remark = sq.quality_remark) \r\nleft join suggestion_file on suggestion_file.suggestion_id=s.suggestion_id \r\nleft join user on user.id=s.user_id LEFT JOIN role ON role.role_id = user.userRole LEFT JOIN \r\n            resident_residences rr ON rr.resident_id = c.id\r\n        LEFT JOIN \r\n            building_residence br ON rr.residence_id = br.id\r\n        LEFT JOIN \r\n            building_unit_types bu ON br.unit_type = bu.unit_type_id\r\n        LEFT JOIN \r\n            building b ON br.building_id = b.id";
IF ( suggestion_id >0 ) THEN
	SET @sql = CONCAT(@sql,' where s.suggestion_id =',suggestion_id);
END IF;

	SET @sql = CONCAT(@sql,' GROUP by s.suggestion_id order by s.suggestion_id DESC');
IF ( in_limit >0 ) THEN
	SET @sql = CONCAT(@sql,' limit ',in_limit);
END IF;


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

DELIMITER;

-- @vipin @raunak need form_date in vehicle pass for builk vehicle pass api
ALTER TABLE `vehicle_pass`
ADD `from_date` DATE NULL AFTER `pass_valid_upto`;

-- 28/08/2024 modify sp insertVehiclePass by consent bisnu for bulk vehicle pass
DROP PROCEDURE IF EXISTS `insertVehiclePass`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `insertVehiclePass`(
    IN `csv_data` TEXT, 
    IN `in_user` INT, 
    IN `in_valid` DATE, 
    IN `in_from_date` DATE,
    OUT `out_id` INT
)
BEGIN
    DECLARE start_index INT DEFAULT 1;
    DECLARE end_index INT;
    DECLARE v_id INT;
    DECLARE done INT DEFAULT FALSE;

    -- Initialize the output parameter
    SET out_id = 0;

    -- Loop to process each CSV entry
    REPEAT
        -- Find the end of the next entry
        SET end_index = LOCATE(',', csv_data, start_index);
        IF end_index = 0 THEN
            SET end_index = LENGTH(csv_data) + 1;
        END IF;

        -- Extract the current entry
        SET v_id = CAST(SUBSTRING(csv_data, start_index, end_index - start_index) AS UNSIGNED);
        
        -- Remove the processed entry from the CSV string
        SET csv_data = SUBSTRING(csv_data, end_index + 1);

        -- Determine the new pass number
        SET @count = (SELECT COUNT(id) FROM vehicle_pass) + 1;
        SET @name = (SELECT SUBSTRING_INDEX(cust_name, ' ', 1) FROM information LIMIT 1);
        SET @pass_no = CONCAT(@name, @count);

        -- Insert the data into vehicle_pass
        INSERT INTO vehicle_pass (pass_no, issued_by, pass_valid_upto, from_date, vehicle_id)
        VALUES (@pass_no, in_user, in_valid, in_from_date, v_id);

        -- Update the output parameter with the last inserted ID
        SET out_id = LAST_INSERT_ID();

        -- Continue if there are more entries
        IF LENGTH(csv_data) > 0 THEN
            SET done = FALSE;
        ELSE
            SET done = TRUE;
        END IF;

        SET start_index = 1;
    UNTIL done END REPEAT;

END$$

DELIMITER;

--by consent @bishnu sir, add new column in suggestion table suggestion_asked_by,suggestion_responded_by and user_id must be suggestion_asked_by

ALTER TABLE `suggestion`
ADD `suggestion_asked_by` INT NULL AFTER `user_id`,
ADD `suggestion_responded_by` INT NULL AFTER `suggestion_asked_by`;

-- 29/08/2024 change
-- left join user as responded_user on responded_user.id=s.suggestion_responded_by LEFT JOIN role as responded_user_role ON responded_user_role.role_id = responded_user.userRole

-- responded_user.name as respond_by_name, responded_user.userRole as respond_by_role_id, responded_user_role.role_name as respond_by_role_name,s.respond_by_date

-- adding where clouse and adding 2 new parms building_ids for building premistion and user_id
-- where br.is_active = 1 and rr.isActive = 1 and bu.isActive = 1 and c.is_activated = 'A'";

-- IF ( user_id !="" && user_id > 0  && building_ids !="" && building_ids != 0) THEN
-- 	SET @sql = CONCAT(@sql, ' and (s.user_id = ', user_id, ' OR br.building_id IN (', building_ids, '))');
-- END IF;

DROP PROCEDURE IF EXISTS `GetSuggestion`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSuggestion`(IN `suggestion_id` BIGINT, IN `in_limit` INT, IN `user_id` INT, IN `building_ids` VARCHAR(60))
BEGIN
    -- Define the base query
    SET @sql = "
        SELECT 
            s.suggestion_id,
            c.apartment_name AS apartmentName,
            c.building_name AS buildingName,
            c.unit_name AS unitType,
            CASE 
                WHEN s.role = 11 THEN 'resident'
                ELSE 'team'
            END AS raised_by,
            CASE 
                WHEN s.role != 11 THEN user.name
                ELSE c.complainant_name
            END AS name,
            CASE 
                WHEN s.role != 11 THEN user.phoneNumber
                ELSE c.contact_no
            END AS contact_no,
            CASE 
                WHEN s.role != 11 THEN user.email
                ELSE c.email
            END AS email,
            c.gender,
            s.suggestion_text,
            s.management_comment,
            s.suggestion_date,
            s.suggestion_time,
            s.quality_remark,
            user.userRole,
            role.role_name,
            responded_user.name AS respond_by_name,
            responded_user.userRole AS respond_by_role_id,
            s.respond_by_date,
            responded_user_role.role_name AS respond_by_role_name,
            sq.quality_remark_text,
            
            COUNT(suggestion_file.id) AS file_count
        FROM suggestion s
        JOIN user ON user.id = s.suggestion_asked_by
        JOIN role ON role.role_id = user.userRole
        LEFT JOIN suggestion_quality_codes sq ON s.quality_remark = sq.quality_remark
        LEFT JOIN suggestion_file ON suggestion_file.suggestion_id = s.suggestion_id
        LEFT JOIN user AS responded_user ON responded_user.id = s.suggestion_responded_by
        LEFT JOIN role AS responded_user_role ON responded_user_role.role_id = responded_user.userRole
        LEFT JOIN complainant_view c ON s.role = 11 AND c.id = user.relatedId
    ";

    -- Add condition for specific suggestion_id
    IF (suggestion_id > 0) THEN
        SET @sql = CONCAT(@sql, ' WHERE s.suggestion_id = ', suggestion_id);
    END IF;

    -- Add conditions for user_id and building_ids
    IF user_id IS NOT NULL AND user_id > 0 AND building_ids IS NOT NULL AND building_ids != '' AND building_ids != 0 THEN
        IF suggestion_id > 0 THEN
            SET @sql = CONCAT(@sql, ' AND (s.suggestion_asked_by = ', user_id, ' OR c.building_id IN (', building_ids, '))');
        ELSE
            SET @sql = CONCAT(@sql, ' WHERE (s.suggestion_asked_by = ', user_id, ' OR c.building_id IN (', building_ids, '))');
        END IF;
    END IF;

    -- Group by and order the results
    SET @sql = CONCAT(@sql, ' GROUP BY s.suggestion_id ORDER BY s.suggestion_id DESC');

    -- Add limit if specified
    IF (in_limit > 0) THEN
        SET @sql = CONCAT(@sql, ' LIMIT ', in_limit);
    END IF;

    -- Prepare, execute, and deallocate the SQL statement
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER;

-- consent by @bishnu sir
ALTER TABLE `suggestion` ADD `role` INT NULL AFTER `isUser`;

-- chaging accouding new column

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertSuggestion`(IN `suggestiontext` TEXT, IN `suggestiontid` INT(5), IN `in_is_user` BOOLEAN, IN `in_role` INT, OUT `sugid` INT)
BEGIN
INSERT INTO `suggestion`(suggestion_text,suggestion_date,suggestion_time,suggestion_asked_by,isUser,role) 
VALUES(suggestiontext,CURRENT_DATE,CURRENT_TIME,suggestiontid,in_is_user,in_role);
SET sugid = LAST_INSERT_ID();
END$$

DELIMITER;

--

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
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.suggestiont_id=complainantId else suggestion.suggestion_asked_by = user_id END) 
GROUP by suggestion.suggestion_id
order by suggestion.suggestion_id DESC
;


END$$

DELIMITER;

--
ALTER TABLE `department_team`
ADD `user_id` INT NULL AFTER `working_area`;

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

ALTER TABLE `department_team`
ADD `updated_at` TIMESTAMP on update CURRENT_TIMESTAMP NULL AFTER `department_id`;

ALTER TABLE `department_team`
ADD `updated_by` INT NULL AFTER `user_id`;

-- 30/08/2024
-- adding suggestion.management_comment for prasun

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.suggestion_asked_by = user_id or suggestion.suggestiont_id=complainantId else suggestion.suggestion_asked_by = user_id END) 
GROUP by suggestion.suggestion_id
order by suggestion.suggestion_id DESC
;


END$$

DELIMITER;

--31/08/24
DROP PROCEDURE IF EXISTS `InsertDepartmentTeam`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertDepartmentTeam`(IN `memberName` VARCHAR(50), IN `memberContact` BIGINT(20), IN `memberEmail` VARCHAR(100), IN `memberWorkingarea` VARCHAR(150), IN `designation` VARCHAR(50), IN `memberDeptid` VARCHAR(255), OUT `memid` INT)
BEGIN
INSERT INTO `department_team`(member_name,member_contactno,member_email,working_area,designation,department_id) VALUES(memberName,memberContact,memberEmail,memberWorkingarea,designation,memberDeptid);
SET memid = LAST_INSERT_ID();
END$$

DELIMITER ;

Error Code: 1406. Data too long for column 'memberDeptid' at row 1

-- 09/02/2024

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_asked_by=complainantId else suggestion.suggestion_asked_by = user_id END) 
GROUP by suggestion.suggestion_id
order by suggestion.suggestion_id DESC
;


END$$

DELIMITER;

--

DROP PROCEDURE IF EXISTS `GetSuggestion`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSuggestion`(IN `suggestion_id` BIGINT, IN `in_limit` INT, IN `user_id` INT, IN `building_ids` VARCHAR(60))
BEGIN
    -- Define the base query
    SET @sql = "
        SELECT 
            s.suggestion_id,
            c.apartment_name AS apartmentName,
            c.building_name AS buildingName,
            c.unit_name AS unitType,
            c.building_id,
            CASE 
                WHEN s.role = 11 THEN 'resident'
                ELSE 'team'
            END AS raised_by,
            CASE 
                WHEN s.role != 11 THEN user.name
                ELSE c.complainant_name
            END AS name,
            CASE 
                WHEN s.role != 11 THEN user.phoneNumber
                ELSE c.contact_no
            END AS contact_no,
            CASE 
                WHEN s.role != 11 THEN user.email
                ELSE c.email
            END AS email,
            CASE 
                WHEN s.role = 11 THEN c.file
                ELSE c.file
            END AS file,
            CASE 
                WHEN s.role = 11 THEN c.ext
                ELSE user.ext
            END AS ext,
            c.gender,
            c.floor,
            s.suggestion_text,
            s.management_comment,
            s.suggestion_date,
            s.suggestion_time,
            s.quality_remark,
            user.userRole,
            role.role_name,
            responded_user.name AS respond_by_name,
            responded_user.userRole AS respond_by_role_id,
            s.respond_by_date,
            responded_user_role.role_name AS respond_by_role_name,
            sq.quality_remark_text,
            bu.building_user_ids,
            s.suggestion_asked_by,
            suggestion_file.file_count,
            ra.course_year,
            ra.course_name,
            ra.branch_name
        FROM suggestion s
        JOIN user ON user.id = s.suggestion_asked_by
        JOIN role ON role.role_id = user.userRole
        LEFT JOIN (
            SELECT 
                user.id AS user_id, 
                GROUP_CONCAT(DISTINCT building_users.building_user_id ORDER BY building_users.building_user_id SEPARATOR ',') AS building_user_ids
            FROM user
            JOIN building_users ON user.id = building_users.user_id
            WHERE user.userRole != 11 
            GROUP BY user.id
        ) AS bu ON user.id = bu.user_id
        LEFT JOIN suggestion_quality_codes sq ON s.quality_remark = sq.quality_remark
        LEFT JOIN (
            SELECT 
                s.suggestion_id, 
                COUNT(suggestion_file.id) AS file_count 
            FROM suggestion s
            JOIN suggestion_file ON suggestion_file.suggestion_id = s.suggestion_id
            GROUP BY s.suggestion_id
        ) AS suggestion_file ON suggestion_file.suggestion_id = s.suggestion_id
        LEFT JOIN user AS responded_user ON responded_user.id = s.suggestion_responded_by
        LEFT JOIN role AS responded_user_role ON responded_user_role.role_id = responded_user.userRole
        LEFT JOIN complainant_view c ON s.role = 11 AND c.id = user.relatedId
        LEFT JOIN resident_only_academic_view ra ON ra.resident_id = c.id
    ";

    -- Add condition for specific suggestion_id
    IF (suggestion_id > 0) THEN
        SET @sql = CONCAT(@sql, ' WHERE s.suggestion_id = ', suggestion_id);
    END IF;

    -- Add conditions for user_id and building_ids
    IF user_id IS NOT NULL AND user_id > 0 AND building_ids IS NOT NULL AND building_ids != '' AND building_ids != 0 THEN
        IF suggestion_id > 0 THEN
            SET @sql = CONCAT(@sql, ' AND (s.suggestion_asked_by = ', user_id, ' OR c.building_id IN (', building_ids, '))');
        ELSE
            SET @sql = CONCAT(@sql, ' WHERE (s.suggestion_asked_by = ', user_id, ' OR c.building_id IN (', building_ids, '))');
        END IF;
    END IF;

    -- Group by and order the results
    SET @sql = CONCAT(@sql, ' ORDER BY s.suggestion_id DESC');

    -- Add limit if specified
    IF (in_limit > 0) THEN
        SET @sql = CONCAT(@sql, ' LIMIT ', in_limit);
    END IF;

    -- Prepare, execute, and deallocate the SQL statement
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER;

-- 03/09/2024

ALTER TABLE `suggestion`
ADD `implemented_by` INT NULL AFTER `suggestion_date`,
ADD `implemented_status` TINYINT NULL DEFAULT '0' AFTER `implemented_by`,
ADD `implemented_by_date` TIMESTAMP NULL DEFAULT NULL AFTER `implemented_status`;

DROP PROCEDURE IF EXISTS `GetSuggestion`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSuggestion`(IN `suggestion_id` BIGINT, IN `in_limit` INT, IN `user_id` INT, IN `building_ids` VARCHAR(60))
BEGIN
    -- Define the base query
    SET @sql = "
        SELECT 
            s.suggestion_id,
            c.apartment_name AS apartmentName,
            c.building_name AS buildingName,
            c.unit_name AS unitType,
            c.building_id,
            CASE 
                WHEN s.role = 11 THEN 'resident'
                ELSE 'team'
            END AS raised_by,
            CASE 
                WHEN s.role != 11 THEN user.name
                ELSE c.complainant_name
            END AS name,
            CASE 
                WHEN s.role != 11 THEN user.phoneNumber
                ELSE c.contact_no
            END AS contact_no,
            CASE 
                WHEN s.role != 11 THEN user.email
                ELSE c.email
            END AS email,
            CASE 
                WHEN s.role = 11 THEN c.file
                ELSE c.file
            END AS file,
            CASE 
                WHEN s.role = 11 THEN c.ext
                ELSE user.ext
            END AS ext,
            c.gender,
            c.floor,
            s.suggestion_text,
            s.management_comment,
            s.suggestion_date,
            s.suggestion_time,
            s.quality_remark,
            user.userRole,
            role.role_name,
            responded_user.name AS respond_by_name,
            responded_user.userRole AS respond_by_role_id,
            s.respond_by_date,
            responded_user_role.role_name AS respond_by_role_name,
            sq.quality_remark_text,
            bu.building_user_ids,
            s.suggestion_asked_by,
            suggestion_file.file_count,
            ra.course_year,
            ra.course_name,
            ra.branch_name,
            s.implemented_by,
            s.implemented_status,
            implemented_by_user.name as implemented_by_name,
            s.implemented_by_date
        FROM suggestion s
        JOIN user ON user.id = s.suggestion_asked_by
        JOIN role ON role.role_id = user.userRole
        LEFT JOIN user as implemented_by_user ON implemented_by_user.id = s.implemented_by
        LEFT JOIN (
            SELECT 
                user.id AS user_id, 
                GROUP_CONCAT(DISTINCT building_users.building_user_id ORDER BY building_users.building_user_id SEPARATOR ',') AS building_user_ids
            FROM user
            JOIN building_users ON user.id = building_users.user_id
            WHERE user.userRole != 11 
            GROUP BY user.id
        ) AS bu ON user.id = bu.user_id
        LEFT JOIN suggestion_quality_codes sq ON s.quality_remark = sq.quality_remark
        LEFT JOIN (
            SELECT 
                s.suggestion_id, 
                COUNT(suggestion_file.id) AS file_count 
            FROM suggestion s
            JOIN suggestion_file ON suggestion_file.suggestion_id = s.suggestion_id
            GROUP BY s.suggestion_id
        ) AS suggestion_file ON suggestion_file.suggestion_id = s.suggestion_id
        LEFT JOIN user AS responded_user ON responded_user.id = s.suggestion_responded_by
        LEFT JOIN role AS responded_user_role ON responded_user_role.role_id = responded_user.userRole
        LEFT JOIN complainant_view c ON s.role = 11 AND c.id = user.relatedId
        LEFT JOIN resident_only_academic_view ra ON ra.resident_id = c.id
    ";

    -- Add condition for specific suggestion_id
    IF (suggestion_id > 0) THEN
        SET @sql = CONCAT(@sql, ' WHERE s.suggestion_id = ', suggestion_id);
    END IF;

    -- Add conditions for user_id and building_ids
    IF user_id IS NOT NULL AND user_id > 0 AND building_ids IS NOT NULL AND building_ids != '' AND building_ids != 0 THEN
        IF suggestion_id > 0 THEN
            SET @sql = CONCAT(@sql, ' AND (s.suggestion_asked_by = ', user_id, ' OR c.building_id IN (', building_ids, '))');
        ELSE
            SET @sql = CONCAT(@sql, ' WHERE (s.suggestion_asked_by = ', user_id, ' OR c.building_id IN (', building_ids, '))');
        END IF;
    END IF;

    -- Group by and order the results
    SET @sql = CONCAT(@sql, ' ORDER BY s.suggestion_id DESC');

    -- Add limit if specified
    IF (in_limit > 0) THEN
        SET @sql = CONCAT(@sql, ' LIMIT ', in_limit);
    END IF;

    -- Prepare, execute, and deallocate the SQL statement
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER;

--

-- 09/05/24

DROP PROCEDURE IF EXISTS `getUserForDashboardWithDepartment`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getUserForDashboardWithDepartment`(IN `systemUserId` INT)
BEGIN
    SET @sql = "select Id,name,email,phoneNumber,userID,isAdminUser,userRole,\r\n            file,ext,allusers.isActive,(case when allusers.isActive=1 then 'Active' else 'Inactive' END)\r\n            as status,department,department_ids,IF(role.Alias IS NULL ,role.role_name,role.Alias) as role_name,building_users.building_id as assigned_buildings from\r\n            (SELECT id,name,email,phoneNumber,userID,isAdminUser,user.isActive,userRole,user.file,user.ext,'' as department,'' as department_ids FROM `user` \r\n            where userRole not in (8,9,11) union SELECT \r\n            u.id,\r\n            u.name,\r\n            u.email,\r\n            u.phoneNumber,\r\n            u.userID,\r\n            u.isAdminUser,\r\n            u.isActive,\r\n            u.userRole,\r\n            u.file,\r\n            u.ext,\r\n            department_team.department_name AS department,\r\n            department_team.department_id AS department_ids\r\n        FROM \r\n            `user` u\r\n            LEFT JOIN (\r\n                SELECT \r\n                    dt.member_id,\r\n                    dt.department_id,\r\n                    GROUP_CONCAT(d.department_name SEPARATOR ', ') AS department_name\r\n                    \r\n                FROM \r\n                    department_team dt\r\n                    LEFT JOIN department d \r\n                    ON FIND_IN_SET(d.department_id, dt.department_id) > 0\r\n                GROUP BY \r\n                    dt.member_id,dt.department_id\r\n            ) AS department_team \r\n            ON department_team.member_id = u.relatedId\r\n        WHERE u.userRole IN (8,9)) allusers left join role on allusers.userRole=role.role_id left join\r\n            (SELECT user_id,GROUP_CONCAT(building_id) as building_id FROM `building_users` WHERE isActive=1 GROUP by user_id) \r\n            building_users on building_users.user_id=allusers.id \r\n            where userRole>0 ";
   
   -- Add condition for specific suggestion_id
    IF (systemUserId IS NOT NULL AND systemUserId !="" and systemUserId > 0) THEN
        SET @sql = CONCAT(@sql, ' and allusers.id= ', systemUserId);
    END IF;
    
    SET @sql = CONCAT(@sql, ' order by userRole asc');
    
    -- Prepare, execute, and deallocate the SQL statement
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER;

-- 06/10/204

ALTER TABLE `resident_checked_in`
CHANGE `status` `status` ENUM(
    '0',
    '1',
    'Review',
    'Accept_As_Damage',
    'Replace',
    'Repair',
    'CheckedIn_With_Damage'
) NOT NULL DEFAULT '1' COMMENT 'cancelled = 0, 1=Active';

ALTER TABLE `resident_checked_in`
ADD `isActive` TINYINT NOT NULL DEFAULT '1' AFTER `status`;

-- 09/11/2024
ALTER TABLE `residence_inventories`
ADD `inventory_status` ENUM(
    'Good_Condition',
    'Checked_In_With_Damage',
    'Accept_As_Damage',
    'Review',
    'Repair',
    'Replace'
) NULL DEFAULT 'Good_Condition' AFTER `status`;

-- 13/11/2024
DROP PROCEDURE IF EXISTS getResidentOccupancyBills;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentOccupancyBills`(IN `in_resident_id` INT, IN `in_bill_id` INT)
    NO SQL
BEGIN


IF(in_bill_id >0) then
	SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,resident_academic_details.registration_no,resident_academic_details.reference_number,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,yearly_session.start_date as session_start_date,yearly_session.end_date as session_end_date,building_residence.apartment_name,building_residence.unit_type,building_residence.building_id,building.name as building,complainants.complainant_name as name,complainants.email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status,occupancy_payment_transaction.reference_number as transaction_reference_number,occupancy_payment_transaction.notes as remark from community_occupancy_bills

    INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
     
    left JOIN complainants on complainants.id=community_occupancy_bills.resident_id
   	left join user on user.id=community_occupancy_bills.approved_by
    left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
    left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
    left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
    left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
    WHERE community_occupancy_bills.bill_id=in_bill_id ;
elseif(in_resident_id >0) then
SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,resident_academic_details.registration_no,resident_academic_details.reference_number,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,building_residence.unit_type,building_residence.apartment_name,building_residence.building_id,building.name as building,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status,occupancy_payment_transaction.reference_number as transaction_reference_number,occupancy_payment_transaction.notes as remark from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
WHERE community_occupancy_bills.resident_id=in_resident_id and community_occupancy_bills.approved_at is not null ORDER by community_occupancy_bills.bill_id DESC;
else

SELECT community_occupancy_bills.*,batches.name batch_name,academic_course_branches.branch_name,resident_academic_details.course_year,resident_academic_details.registration_no,resident_academic_details.reference_number,academic_courses.id as course_id,academic_courses.course_name as course_name,yearly_session.session_name,building_residence.apartment_name,building_residence.building_id,building.name as building,building_residence.unit_type,building_unit_types.Description as unit_description,complainants.complainant_name as resident_name,complainants.email as resident_email,complainants.contact_no,occupancy_payment_transaction.t_id,occupancy_payment_transaction.payment_medium,payment_modes.mode_name,occupancy_payment_transaction.start_at as transaction_start_at,occupancy_payment_transaction.status,approver.name as approved_by_name,reviewer.name as cancelled_by_name,occupancy_payment_transaction.reference_number as transaction_reference_number,occupancy_payment_transaction.notes as remark from community_occupancy_bills
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
INNER join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER join building_unit_types on building_unit_types.unit_type_id=building_residence.unit_type
INNER JOIN building on building.id=building_residence.building_id
left join occupancy_payment_transaction on occupancy_payment_transaction.bill_id=community_occupancy_bills.bill_id and occupancy_payment_transaction.isLatest=1
left join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
 left join user as approver on approver.id=community_occupancy_bills.approved_by
 left join user as reviewer on reviewer.id=community_occupancy_bills.cancelled_by
 left join yearly_session on yearly_session.session_id = community_occupancy_bills.master_session_id
 left join resident_academic_details on resident_academic_details.resident_id = community_occupancy_bills.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 batches on batches.id = resident_academic_details.batch
 ORDER by community_occupancy_bills.bill_id DESC;
end if;
END$$

DELIMITER;

-- 18/09/2024
ALTER TABLE `inventories`
ADD `type` VARCHAR(70) NULL DEFAULT NULL AFTER `updated_by`,
ADD `building_id` TINYINT NULL DEFAULT NULL AFTER `type`;

-- adding building id for security building permistion

DROP PROCEDURE IF EXISTS getGateParcel;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getGateParcel`(IN `in_booking_id` BIGINT, IN `in_residence_id` SMALLINT, IN `in_user_id` SMALLINT, IN `in_view` ENUM('ALL','N','D','DP'), IN `in_limit` TINYINT, IN `in_created_at` DATE, IN `in_delivered_at` DATE, IN `in_disposed_at` DATE)
    NO SQL
BEGIN

SET @sql = "SELECT parcel_at_gate.*,visitors.name as visitor_name,visitors.contact_no as visitor_contact,CONCAT(visitors.file,'.',visitors.file_ext) as visitor_image,visitors.gender,building_residence.apartment_name,building.name as building,user.name,user.phoneNumber,creator.name as created_by_name,deliver.name as deliver_name,building_residence.building_id from parcel_at_gate \r\ninner join visitors on visitors.id=parcel_at_gate.visitor_id\r\nLEFT join building_residence on building_residence.id=parcel_at_gate.residence_id \r\nleft join building on building.id=building_residence.building_id\r\nleft join user on user.id=parcel_at_gate.user_id\r\nINNER join user as creator on creator.id=parcel_at_gate.created_by \r\nleft join user as deliver on deliver.id=parcel_at_gate.delivered_by \r\n where 1=1  ";
 IF ( in_booking_id > 0 ) THEN
       SET @sql = CONCAT(@sql,' AND parcel_at_gate.booking_id =',in_booking_id);
 
elseIF ( in_user_id > 0 ) THEN
       SET @sql = CONCAT(@sql,' AND parcel_at_gate.user_id =',in_user_id);
 elseIF ( in_residence_id > 0 ) THEN
       SET @sql = CONCAT(@sql,' AND parcel_at_gate.residence_id =',in_residence_id);
 END IF;
 IF ( in_view = 'N' || in_view = 'D' || in_view = 'DP' ) THEN
       SET @sql = CONCAT(@sql," AND parcel_at_gate.status ='",in_view,"'");
 END IF;
 IF ( in_created_at is not null ) THEN
       SET @sql = CONCAT(@sql," AND date(parcel_at_gate.created_at) ='",in_created_at,"'");
 END IF;
 IF ( in_delivered_at is not null ) THEN
       SET @sql = CONCAT(@sql," AND date(parcel_at_gate.delivered_at) ='",in_delivered_at,"'");
 END IF;
 IF ( in_disposed_at is not null ) THEN
       SET @sql = CONCAT(@sql," AND date(parcel_at_gate.disposed_at) ='",in_disposed_at,"'");
 END IF;
 SET @sql = CONCAT(@sql,' order by parcel_at_gate.booking_id desc');
IF ( in_limit > 0 ) THEN
       SET @sql = CONCAT(@sql,' limit 0,',in_limit);
 END IF;
  

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

DELIMITER;

-- 23/09/2024

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,REPLACE(json_extract(json_unquote(json_extract(gate_pass.residence_actions, '$.", @residence_id, "')), '$.action'), '\"', '') AS residence_status,gate_pass.status as security_status,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,gate_pass.updated_at 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,')');
        SET @sql = CONCAT(@sql, ' AND gate_pass.resident_id = ', in_resident_id);
END IF;
SET @sql = CONCAT(@sql,' order by gate_pass.id desc');

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

DELIMITER;

'-- DELIMITER //

CREATE PROCEDURE getInventoryAssignedNotAssignedFilterCount(
    IN category_id INT,
    IN status VARCHAR(100),
    IN inventory_id INT,
    IN building_ids VARCHAR(55),
    IN residence_id INT,
    IN resident_id INT
)
BEGIN
    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT 
        inventory_categories.name AS inventory,
        COUNT(inventory_categories.name) AS total,
        COUNT(CASE WHEN residence_inventories.residence_id IS NOT NULL THEN 1 END) AS assigned,
        COUNT(CASE WHEN inventories.status = 0 AND residence_inventories.residence_id IS NOT NULL THEN 1 END) AS damage_in_room,
        COUNT(CASE WHEN inventories.status = 0 AND residence_inventories.residence_id IS NULL THEN 1 END) AS damage_out_room,
        (COUNT(inventory_categories.name) 
            - (COUNT(CASE WHEN residence_inventories.residence_id IS NOT NULL THEN 1 END) 
            + COUNT(CASE WHEN inventories.status = 0 AND residence_inventories.residence_id IS NULL THEN 1 END))) AS surplus
    FROM 
        inventories
    JOIN 
        inventory_categories ON inventory_categories.id = inventories.category_id
    LEFT JOIN 
        residence_inventories ON residence_inventories.inventory_id = inventories.id 
        AND residence_inventories.status = 1
    LEFT JOIN 
        building_residence ON building_residence.id = residence_inventories.residence_id
    LEFT JOIN 
        building ON building.id = building_residence.building_id
    LEFT JOIN 
        building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
    LEFT JOIN 
        user AS creator ON creator.id = inventories.created_by
    LEFT JOIN 
        user AS remover ON remover.id = inventories.updated_by';


    IF status != "" AND status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' WHERE inventories.status = ', status);
    END IF;

    IF category_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.category_id = ', category_id);
    END IF;

    IF inventory_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.id = ', inventory_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND building_residence.building_id IN (', building_ids, ')');
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.residence_id = ', residence_id);
    END IF;

    IF resident_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.resident_id = ', resident_id);
    END IF;

    SET sql_query = CONCAT(sql_query, ' GROUP BY inventory_categories.name');
    SET sql_query = CONCAT(sql_query, ' ORDER BY inventories.id DESC');

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER;

-- Total , Damaged, Disposed Inventories count







































DELIMITER $$

CREATE PROCEDURE getInventoryWithDamagedDisposedFilterCount(
    IN category_id INT,
    IN status VARCHAR(100),
    IN inventory_id INT,
    IN building_ids VARCHAR(55),
    IN residence_id INT,
    IN resident_id INT
)
BEGIN
    -- Total , Damaged, Disposed Inventories count
    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT 
        COUNT(inventories.id) AS totalInventories,
        COUNT(CASE WHEN inventories.health_status = "DAMAGED" THEN 1 END ) AS damagedInventories,
        COUNT(CASE WHEN inventories.health_status = "DISPOSED" THEN 1 END) AS dispostedInventories
    FROM 
        inventories
    JOIN 
        inventory_categories ON inventory_categories.id = inventories.category_id
    LEFT JOIN 
        residence_inventories ON residence_inventories.inventory_id = inventories.id 
        AND residence_inventories.status = 1
    LEFT JOIN 
        building_residence ON building_residence.id = residence_inventories.residence_id
    LEFT JOIN 
        building ON building.id = building_residence.building_id
    LEFT JOIN 
        building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
    LEFT JOIN 
        user AS creator ON creator.id = inventories.created_by
    WHERE 1=1';


    IF status != "" AND status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.health_status = ', status);
    END IF;

    IF category_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.category_id = ', category_id);
    END IF;

    IF inventory_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.id = ', inventory_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND building_residence.building_id IN (', building_ids, ')');
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.residence_id = ', residence_id);
    END IF;

    IF resident_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.resident_id = ', resident_id);
    END IF;

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

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END $$

DELIMITER;

DROP PROCEDURE IF EXISTS `getInventoryWithFilter`;

DELIMITER / /

CREATE PROCEDURE getInventoryWithFilter(
    IN category_id INT,
    IN status VARCHAR(100),
    IN inventory_id INT,
    IN building_ids VARCHAR(55),
    IN residence_id INT,
    IN resident_id INT,
    IN searchBy VARCHAR(100)
)
BEGIN
    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT inventories.*, 
                            inventory_categories.name AS inventory_category_name,
                            residence_inventories.residence_id,
                            building_residence.apartment_name,
                            building.name AS building_name,
                            building_unit_types.description,
                            creator.name AS creator_name,
                            remover.name AS remover_name,
                            building_residence.floor,
                            residence_inventories.resident_id,
                            building_residence.building_id,
                            building_residence.unit_type AS unit_type_id,
                            building_unit_types.Description AS unit_type_name,
                            residence_inventories.status AS residence_inventories_status,
                            complainants.complainant_name
                     FROM inventories
                     JOIN inventory_categories ON inventory_categories.id = inventories.category_id
                     LEFT JOIN residence_inventories ON residence_inventories.inventory_id = inventories.id 
                          AND residence_inventories.status = 1
                     LEFT JOIN complainants ON complainants.id = residence_inventories.resident_id
                     LEFT JOIN building_residence ON building_residence.id = residence_inventories.residence_id
                     LEFT JOIN building ON building.id = building_residence.building_id
                     LEFT JOIN building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
                     LEFT JOIN user AS creator ON creator.id = inventories.created_by
                     LEFT JOIN user AS remover ON remover.id = inventories.updated_by WHERE 1=1';

    
    IF status != "" AND status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.status = ', status);
    END IF;

    IF category_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.category_id = ', category_id);
    END IF;

    IF inventory_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.id = ', inventory_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND building_residence.building_id IN (', building_ids, ')');
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.residence_id = ', residence_id);
    END IF;

    IF resident_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.resident_id = ', resident_id);
    END IF;

    -- Handle searchBy filtering
    IF searchBy IS NOT NULL AND searchBy != "" THEN
        SET sql_query = CONCAT(sql_query, ' AND (inventories.label LIKE "%', searchBy, '%" 
            OR building_residence.apartment_name LIKE "%', searchBy, '%" 
            OR inventory_categories.name LIKE "%', searchBy, '%"
            OR building_unit_types.Description LIKE "%', searchBy, '%"
            OR complainants.complainant_name LIKE "%', searchBy, '%")');
    END IF;

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

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END //

DELIMITER;

-- 25/09/204

ALTER TABLE `gate_pass`
ADD `cancel_by` INT NULL DEFAULT NULL AFTER `body_temprature`,
ADD `remark` VARCHAR(255) NULL DEFAULT NULL AFTER `cancel_by`;

-- gate_pass.remark,gate_pass.cancel_by,cancel_by_user.name as cancel_by_user_name
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,
            REPLACE(json_extract(json_unquote(json_extract(gate_pass.residence_actions, '$.", @residence_id, "')), '$.action'), '""', '') AS residence_status,
            gate_pass.status AS security_status,
            IFNULL(expected_dt, '') AS expected_dt,
            IFNULL(expected_tm, '') AS expected_tm,
            IFNULL(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,
            gate_pass.updated_at,
            gate_pass.remark,
            gate_pass.cancel_by,
            cancel_by_user.name as cancel_by_user_name

        FROM 
            gate_pass
        LEFT JOIN 
            complainants ON complainants.id = gate_pass.resident_id
        LEFT JOIN 
            building_residence ON building_residence.id = gate_pass.residence_id
        LEFT JOIN 
            building ON building.id = building_residence.building_id
        LEFT JOIN 
            visitors ON visitors.id = gate_pass.visitor_id 
        LEFT JOIN 
            user as cancel_by_user ON cancel_by_user.id = gate_pass.cancel_by
        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,')');
        SET @sql = CONCAT(@sql, ' AND gate_pass.resident_id = ', in_resident_id);
END IF;
SET @sql = CONCAT(@sql,' order by gate_pass.id desc');

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

DELIMITER;

-- 25/09/2024
CREATE TABLE `damage_inventory_files` (
    `id` int(11) NOT NULL,
    `inventory_id` int(11) NOT NULL,
    `file_name` varchar(200) DEFAULT NULL,
    `file_ext` varchar(11) DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `damage_inventory_files` ADD PRIMARY KEY (`id`);

ALTER TABLE `damage_inventory_files`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;

-- media files

CREATE TABLE `medias` (
    `id` int(11) NOT NULL,
    `user_id` int(11) DEFAULT NULL,
    `url` varchar(100) NOT NULL,
    `type` enum(
        'image',
        'video',
        'audio',
        'document'
    ) DEFAULT NULL,
    `ext` varchar(100) DEFAULT NULL,
    `alt_text` varchar(150) DEFAULT NULL,
    `mime_type` varchar(150) DEFAULT NULL,
    `isActive` enum('1', '0') NOT NULL DEFAULT '1',
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

--
ALTER TABLE `medias` ADD PRIMARY KEY (`id`);

--
ALTER TABLE `medias`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT = 5;

COMMIT;

-- inventory type table

CREATE TABLE `inventory_types` (
    `id` int(11) NOT NULL,
    `type_name` varchar(100) NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `inventory_types` ADD PRIMARY KEY (`id`);

ALTER TABLE `inventory_types`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT = 3;

COMMIT;

-- add new column in inventory table
ALTER TABLE `inventory_categories`
ADD `type_id` INT NULL DEFAULT NULL AFTER `id`;

-- add alter

ALTER TABLE `inventories`
ADD `ownership` ENUM('RENTED', 'OWNED') NULL DEFAULT 'OWNED' AFTER `label`,
ADD `health_status` ENUM(
    'DAMAGED',
    'DISPOSED',
    'NEW',
    'USED'
) NULL DEFAULT 'NEW' AFTER `ownership`,
ADD `residence_id` INT NULL AFTER `health_status`,
ADD `is_resident` INT NULL AFTER `residence_id`,
ADD `resident_id` INT NULL AFTER `is_resident`,
ADD `images` TEXT NULL AFTER `resident_id`;

--
ALTER TABLE `gate_pass`
ADD `cancel_by` INT NULL DEFAULT NULL AFTER `body_temprature`,
ADD `remark` VARCHAR(255) NULL DEFAULT NULL AFTER `cancel_by`;

ALTER TABLE `visitors`
ADD `identity_proof_file` VARCHAR(100) NULL DEFAULT NULL AFTER `file_ext`;

-- 30/09/2024
-- remaining to execute
ALTER TABLE inventories DROP COLUMN updated_by;

ALTER TABLE inventories DROP COLUMN type;

ALTER TABLE inventories DROP COLUMN building_id;

ALTER TABLE inventories DROP COLUMN status;
--

ALTER TABLE `inventories`
ADD `manufacturer_name` VARCHAR(150) NULL AFTER `images`,
ADD `manufacturer_website` VARCHAR(150) NULL AFTER `manufacturer_name`,
ADD `purchase_date` DATE NULL AFTER `manufacturer_website`,
ADD `warranty_expiry_date` DATE NULL AFTER `purchase_date`,
ADD `tech_desc` VARCHAR(255) NULL AFTER `warranty_expiry_date`,
ADD `sr_no` VARCHAR(150) NULL AFTER `tech_desc`,
ADD `email_address` VARCHAR(150) NULL AFTER `sr_no`,
ADD `phone_number` VARCHAR(50) NULL AFTER `email_address`,
ADD `address` VARCHAR(255) NULL AFTER `phone_number`,
ADD `modal` VARCHAR(150) NULL AFTER `address`;

CREATE TABLE `inventory_activities` (
    `id` int(11) NOT NULL,
    `inventory_id` int(11) NOT NULL,
    `resident_id` int(11) DEFAULT NULL,
    `residence_id` int(11) DEFAULT NULL,
    `activity_by` int(11) DEFAULT NULL,
    `activity` enum(
        'ALLOCATE',
        'REVOKED',
        'CREATED',
        'UPDATED',
        'DAMAGED',
        'DISPOSED'
    ) DEFAULT NULL,
    `activity_date` date DEFAULT NULL,
    `images` varchar(100) DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `inventory_activities` ADD PRIMARY KEY (`id`);

ALTER TABLE `inventory_activities`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT = 13;

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `inventory_activities`
--
ALTER TABLE `inventory_activities`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT = 13;

COMMIT;

-- 10/01/2024 working on electricity meter Manage and Manage modele

CREATE TABLE `meters` (
    `id` int(11) NOT NULL,
    `parent_id` int(11) DEFAULT 0,
    `created_by` int(11) DEFAULT NULL,
    `sr_no` varchar(150) DEFAULT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `meters` ADD PRIMARY KEY (`id`);

ALTER TABLE `meters` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;

--

CREATE TABLE `meter_allocations` (
    `id` int(11) NOT NULL,
    `meter_id` int(11) NOT NULL,
    `building_id` int(11) DEFAULT NULL,
    `residence_id` int(11) DEFAULT NULL,
    `created_by` int(11) DEFAULT NULL,
    `is_active` tinyint(4) DEFAULT 1,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `meter_allocations` ADD PRIMARY KEY (`id`);

ALTER TABLE `meter_allocations`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;

-- 10/03/2024

CREATE TABLE `meter_readings` (
    `id` int(11) NOT NULL,
    `meter_id` int(11) NOT NULL,
    `created_by` int(11) DEFAULT NULL,
    `reading_value` decimal(10, 2) NOT NULL,
    `reading_date` date DEFAULT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

--

ALTER TABLE `meter_readings` ADD PRIMARY KEY (`id`);

--

ALTER TABLE `meter_readings`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT = 7;

COMMIT;

--

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `meterWithReadingSummary`(
    IN meter_id INT,
    IN meter_parent_id INT,
    IN allocations_id INT,
    IN residence_id INT,
    IN building_ids VARCHAR(55),
    IN searchBy VARCHAR(100),
    IN allocations_status INT,
    IN meter_status INT
)
BEGIN

    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT 
        meters.id,
        meters.parent_id,
        meters.sr_no,
        meters.is_active as is_meter_active,
        latest_parent_reading.reading_value AS current_reading_value,
        latest_parent_reading.total_energy,
        meter_created_by.name AS meter_created_by_name,
        meter_created_by.userRole AS meter_created_by_userRole,
        meter_allocations.building_id,
        meter_allocations.residence_id,
        building_residence.apartment_name,
        building.name AS building_name,
        building_unit_types.description as unit_type,
        meters.created_at,
        meters.updated_at
    FROM 
        meters
    JOIN 
        user meter_created_by ON meter_created_by.id = meters.created_by 
    LEFT JOIN 
        meter_allocations ON meter_allocations.meter_id = meters.id AND meter_allocations.is_active = 1
    LEFT JOIN 
        building_residence ON building_residence.id = meter_allocations.building_id
    LEFT JOIN 
        building ON building.id = building_residence.building_id 
    LEFT JOIN 
        building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
    LEFT JOIN 
        (SELECT 
            mr.id,
            mr.meter_id,
            mr.reading_value,
            COALESCE(mr.reading_value - previous_reading.reading_value, 0) AS total_energy,
            mr.created_at
        FROM 
            meter_readings mr
        LEFT JOIN 
            meter_readings previous_reading ON mr.meter_id = previous_reading.meter_id 
            AND previous_reading.id = (
                SELECT MAX(id) 
                FROM meter_readings 
                WHERE meter_id = mr.meter_id AND id < mr.id
            )
        JOIN 
            (SELECT 
                meter_id, 
                MAX(id) AS max_id
            FROM 
                meter_readings
            GROUP BY 
                meter_id) AS latest 
        ON 
            mr.meter_id = latest.meter_id AND mr.id = latest.max_id 
        ) AS latest_parent_reading 
    ON 
        latest_parent_reading.meter_id = meters.id WHERE 1=1';

    
    IF allocations_status != "" AND allocations_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.is_active = ', allocations_status);
    END IF;

    IF meter_status != "" AND meter_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.is_active = ', meter_status);
    END IF;

    IF meter_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.id = ', meter_id);
    END IF;

    IF meter_parent_id IS NOT NULL AND meter_parent_id !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.parent_id = ', meter_parent_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.building_id IN (', building_ids, ')');
    END IF;

    IF allocations_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.id = ', allocations_id);
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.residence_id = ', residence_id);
    END IF;

     -- Handle searchBy filtering
    IF searchBy IS NOT NULL AND searchBy != "" THEN
        SET sql_query = CONCAT(sql_query, ' AND (meters.sr_no LIKE "%', searchBy, '%" 
            OR building_residence.apartment_name LIKE "%', searchBy, '%" 
            OR building.name LIKE "%', searchBy, '%"
            OR building_unit_types.Description LIKE "%', searchBy, '%")');
    END IF;

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

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER;

--

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getMasterMeterFilterCountWithEnergy`(
    IN meter_id INT,
    IN meter_parent_id INT,
    IN allocations_id INT,
    IN residence_id INT,
    IN building_ids VARCHAR(55),
    IN searchBy VARCHAR(100),
    IN allocations_status INT,
    IN meter_status INT
)
BEGIN

    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT 
        COUNT(DISTINCT CASE WHEN meters.parent_id = 0 THEN meters.id END) AS total_master_meters,
        COUNT(DISTINCT CASE WHEN meters.parent_id != 0 THEN meters.id END) AS total_sub_meters,
        SUM(CASE WHEN meters.parent_id = 0 THEN latest_parent_reading.reading_value ELSE 0 END) AS total_reading,
        SUM(CASE WHEN meters.parent_id = 0 THEN latest_parent_reading.total_energy ELSE 0 END) AS total_energy_kWh
    FROM 
        meters
    JOIN 
        user meter_created_by ON meter_created_by.id = meters.created_by 
    LEFT JOIN 
        meter_allocations ON meter_allocations.meter_id = meters.id AND meter_allocations.is_active = 1
    LEFT JOIN 
        building_residence ON building_residence.id = meter_allocations.building_id
    LEFT JOIN 
        building ON building.id = building_residence.building_id 
    LEFT JOIN 
        building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
    LEFT JOIN 
        (SELECT 
            mr.id,
            mr.meter_id,
            mr.reading_value,
            COALESCE(mr.reading_value - previous_reading.reading_value, 0) AS total_energy,
            mr.created_at
        FROM 
            meter_readings mr
        LEFT JOIN 
            meter_readings previous_reading ON mr.meter_id = previous_reading.meter_id 
            AND previous_reading.id = (
                SELECT MAX(id) 
                FROM meter_readings 
                WHERE meter_id = mr.meter_id AND id < mr.id
            )
        JOIN 
            (SELECT 
                meter_id, 
                MAX(id) AS max_id
            FROM 
                meter_readings
            GROUP BY 
                meter_id) AS latest 
        ON 
            mr.meter_id = latest.meter_id AND mr.id = latest.max_id 
        ) AS latest_parent_reading 
    ON 
        latest_parent_reading.meter_id = meters.id WHERE 1=1';

    
    IF allocations_status != "" AND allocations_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.is_active = ', allocations_status);
    END IF;

    IF meter_status != "" AND meter_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.is_active = ', meter_status);
    END IF;

    IF meter_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.id = ', meter_id);
    END IF;

    IF meter_parent_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.parent_id = ', meter_parent_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.building_id IN (', building_ids, ')');
    END IF;

    IF allocations_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.id = ', allocations_id);
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.residence_id = ', residence_id);
    END IF;

     -- Handle searchBy filtering
    IF searchBy IS NOT NULL AND searchBy != "" THEN
        SET sql_query = CONCAT(sql_query, ' AND (meters.sr_no LIKE "%', searchBy, '%" 
            OR building_residence.apartment_name LIKE "%', searchBy, '%" 
            OR building.name LIKE "%', searchBy, '%"
            OR building_unit_types.Description LIKE "%', searchBy, '%")');
    END IF;

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

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER;

--

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `masterMeterWithReadingSummary`(
    IN meter_id INT,
    IN allocations_id INT,
    IN residence_id INT,
    IN building_ids VARCHAR(55),
    IN searchBy VARCHAR(100),
    IN allocations_status INT,
    IN meter_status INT
)
BEGIN

    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT 
        meters.id,
        meters.parent_id,
        meters.sr_no,
        meters.is_active AS is_meter_active,
        latest_parent_reading.reading_value AS current_reading_value,
        latest_parent_reading.total_energy,
        COALESCE(sub_meter_counts.sub_meter_count, 0) AS sub_meter_count,
        meter_created_by.name AS meter_created_by_name,
        meter_created_by.userRole AS meter_created_by_userRole,
        meter_allocations.building_id,
        meter_allocations.residence_id,
        building_residence.apartment_name,
        building.name AS building_name,
        building_unit_types.description AS unit_type,
        meters.created_at,
        meters.updated_at
    FROM 
        meters
    JOIN 
        user meter_created_by ON meter_created_by.id = meters.created_by 
    LEFT JOIN 
        meter_allocations ON meter_allocations.meter_id = meters.id AND meter_allocations.is_active = 1
    LEFT JOIN 
        building_residence ON building_residence.id = meter_allocations.building_id
    LEFT JOIN 
        building ON building.id = building_residence.building_id 
    LEFT JOIN 
        building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
    LEFT JOIN 
        (SELECT 
            mr.id,
            mr.meter_id,
            mr.reading_value,
            COALESCE(mr.reading_value - previous_reading.reading_value, 0) AS total_energy,
            mr.created_at
        FROM 
            meter_readings mr
        LEFT JOIN 
            meter_readings previous_reading ON mr.meter_id = previous_reading.meter_id 
            AND previous_reading.id = (
                SELECT MAX(id) 
                FROM meter_readings 
                WHERE meter_id = mr.meter_id AND id < mr.id
            )
        JOIN 
            (SELECT 
                meter_id, 
                MAX(id) AS max_id
            FROM 
                meter_readings
            GROUP BY 
                meter_id) AS latest 
        ON 
            mr.meter_id = latest.meter_id AND mr.id = latest.max_id 
        ) AS latest_parent_reading 
    ON 
        latest_parent_reading.meter_id = meters.id
    LEFT JOIN 
        (SELECT 
            parent_id,
            COUNT(id) AS sub_meter_count
        FROM 
            meters
        GROUP BY 
            parent_id) AS sub_meter_counts 
    ON 
        sub_meter_counts.parent_id = meters.id
    WHERE meters.parent_id = 0';

    
    IF allocations_status != "" AND allocations_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.is_active = ', allocations_status);
    END IF;

    IF meter_status != "" AND meter_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.is_active = ', meter_status);
    END IF;

    IF meter_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.id = ', meter_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.building_id IN (', building_ids, ')');
    END IF;

    IF allocations_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.id = ', allocations_id);
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.residence_id = ', residence_id);
    END IF;

     -- Handle searchBy filtering
    IF searchBy IS NOT NULL AND searchBy != "" THEN
        SET sql_query = CONCAT(sql_query, ' AND (meters.sr_no LIKE "%', searchBy, '%" 
            OR building_residence.apartment_name LIKE "%', searchBy, '%" 
            OR building.name LIKE "%', searchBy, '%"
            OR building_unit_types.Description LIKE "%', searchBy, '%")');
    END IF;

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

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER;

-- 10/04/2024

ALTER TABLE `meters`
ADD `model` VARCHAR(150) NULL AFTER `sr_no`,
ADD `manufacturer` VARCHAR(150) NULL AFTER `model`,
ADD `capacity` DECIMAL(10, 2) NULL AFTER `manufacturer`,
ADD `start_at_reading` DECIMAL(10, 2) NULL DEFAULT NULL AFTER `capacity`;

---
ALTER TABLE `gates`
ADD `gate_opening_closing_time` TEXT NULL DEFAULT NULL AFTER `building_id`;

---
ALTER TABLE `meter_readings`
ADD `media_id` INT NULL AFTER `reading_date`;

-- 10/08/2024
ALTER TABLE `inventories`
ADD `label_setting` TEXT NULL DEFAULT NULL AFTER `label`;
--

DROP Procedure if EXISTS masterMeterWithReadingSummary;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `masterMeterWithReadingSummary`(
    IN meter_id INT,
    IN allocations_id INT,
    IN residence_id INT,
    IN building_ids VARCHAR(55),
    IN searchBy VARCHAR(100),
    IN allocations_status INT,
    IN meter_status INT
)
BEGIN

    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT 
        meters.id,
        meters.parent_id,
        meters.sr_no,
        meters.manufacturer,
        meters.model,
        meters.capacity,
        meters.start_at_reading,
        meters.is_active AS is_meter_active,
        latest_parent_reading.reading_value AS current_reading_value,
        latest_parent_reading.total_energy,
        COALESCE(sub_meter_counts.sub_meter_count, 0) AS sub_meter_count,
        meter_created_by.name AS meter_created_by_name,
        meter_created_by.userRole AS meter_created_by_userRole,
        meter_allocations.building_id,
        meter_allocations.residence_id,
        building_residence.apartment_name,
        building.name AS building_name,
        building_unit_types.description AS unit_type,
        meters.created_at,
        meters.updated_at
    FROM 
        meters
    JOIN 
        user meter_created_by ON meter_created_by.id = meters.created_by 
    LEFT JOIN 
        meter_allocations ON meter_allocations.meter_id = meters.id AND meter_allocations.is_active = 1
    LEFT JOIN 
        building_residence ON building_residence.id = meter_allocations.building_id
    LEFT JOIN 
        building ON building.id = building_residence.building_id 
    LEFT JOIN 
        building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
    LEFT JOIN 
        (SELECT 
            mr.id,
            mr.meter_id,
            mr.reading_value,
            COALESCE(mr.reading_value - previous_reading.reading_value, 0) AS total_energy,
            mr.created_at
        FROM 
            meter_readings mr
        LEFT JOIN 
            meter_readings previous_reading ON mr.meter_id = previous_reading.meter_id 
            AND previous_reading.id = (
                SELECT MAX(id) 
                FROM meter_readings 
                WHERE meter_id = mr.meter_id AND id < mr.id
            )
        JOIN 
            (SELECT 
                meter_id, 
                MAX(id) AS max_id
            FROM 
                meter_readings
            GROUP BY 
                meter_id) AS latest 
        ON 
            mr.meter_id = latest.meter_id AND mr.id = latest.max_id 
        ) AS latest_parent_reading 
    ON 
        latest_parent_reading.meter_id = meters.id
    LEFT JOIN 
        (SELECT 
            parent_id,
            COUNT(id) AS sub_meter_count
        FROM 
            meters
        GROUP BY 
            parent_id) AS sub_meter_counts 
    ON 
        sub_meter_counts.parent_id = meters.id
    WHERE meters.parent_id = 0';

    
    IF allocations_status != "" AND allocations_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.is_active = ', allocations_status);
    END IF;

    IF meter_status != "" AND meter_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.is_active = ', meter_status);
    END IF;

    IF meter_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.id = ', meter_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.building_id IN (', building_ids, ')');
    END IF;

    IF allocations_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.id = ', allocations_id);
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.residence_id = ', residence_id);
    END IF;

     -- Handle searchBy filtering
    IF searchBy IS NOT NULL AND searchBy != "" THEN
        SET sql_query = CONCAT(sql_query, ' AND (meters.sr_no LIKE "%', searchBy, '%" 
            OR building_residence.apartment_name LIKE "%', searchBy, '%" 
            OR building.name LIKE "%', searchBy, '%"
            OR building_unit_types.Description LIKE "%', searchBy, '%")');
    END IF;

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

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER;

ALTER TABLE `suggestion`
ADD `suggestion_asked_by` INT NULL AFTER `user_id`,
ADD `suggestion_responded_by` INT NULL AFTER `suggestion_asked_by`;

ALTER TABLE `suggestion`
ADD `respond_by_date` TIMESTAMP NULL AFTER `suggestion_responded_by`;

-- 10/09/2024

ALTER TABLE `complainants`
ADD `intern_box_token` TEXT NULL DEFAULT NULL AFTER `phone_prefix`;

--

CREATE TABLE `laundry_categories` (
    `id` int(11) NOT NULL,
    `name` varchar(200) NOT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_by` int(11) DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `laundry_categories` ADD PRIMARY KEY (`id`);

ALTER TABLE `laundry_categories`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;

-- 10/11/2024
CREATE TABLE `laundry_requests` (
    `id` int(11) NOT NULL,
    `resident_id` int(11) DEFAULT NULL,
    `residence_id` int(11) DEFAULT NULL,
    `building_id` int(11) DEFAULT NULL,
    `request_date` date DEFAULT NULL,
    `created_by` int(11) DEFAULT NULL,
    `status` enum(
        'PENDING',
        'APPROVED',
        'PICKUP',
        'DELIVERED',
        'WASHED',
        'CANCELLED'
    ) NOT NULL DEFAULT 'APPROVED',
    `cancelled_by` int(11) DEFAULT NULL,
    `laundry_type` enum('DryCleaning', 'Normal') NOT NULL DEFAULT 'Normal',
    `request_assigned_to` int(11) DEFAULT NULL,
    `pickup_by` int(11) DEFAULT NULL,
    `delivered_by` int(11) DEFAULT NULL,
    `approved_date` date DEFAULT NULL,
    `pickup_date` date DEFAULT NULL,
    `washed_date` date DEFAULT NULL,
    `delivered_date` date DEFAULT NULL,
    `cancelled_date` date DEFAULT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` timestamp NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `laundry_requests` ADD PRIMARY KEY (`id`);

ALTER TABLE `laundry_requests`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
DROP PROCEDURE if EXISTS masterMeterWithReadingSummary;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `masterMeterWithReadingSummary`(
    IN meter_id INT,
    IN allocations_id INT,
    IN residence_id INT,
    IN building_ids VARCHAR(55),
    IN searchBy VARCHAR(100),
    IN allocations_status INT,
    IN meter_status INT
)
BEGIN

    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT 
        meters.id,
        meters.parent_id,
        meters.sr_no,
        meters.manufacturer,
        meters.model,
        meters.capacity,
        meters.start_at_reading,
        meters.is_active AS is_meter_active,
        latest_parent_reading.reading_value AS current_reading_value,
        latest_parent_reading.total_energy,
        COALESCE(sub_meter_counts.sub_meter_count, 0) AS sub_meter_count,
        meter_created_by.name AS meter_created_by_name,
        meter_created_by.userRole AS meter_created_by_userRole,
        meterAllocations.building_id,
        meterAllocations.residence_id,
        meterAllocations.apartment_names,
        meterAllocations.building_name,
        meterAllocations.unit_type,
        meterAllocations.blocks,
        meterAllocations.floors,
        meters.created_at,
        meters.updated_at
    FROM 
        meters
    JOIN 
        user meter_created_by ON meter_created_by.id = meters.created_by 
    LEFT JOIN 
    (
        SELECT 
            meter_allocations.id,
            meter_allocations.meter_id,
            meter_allocations.building_id,
            meter_allocations.residence_id,
            GROUP_CONCAT(DISTINCT building_residence.apartment_name ORDER BY building_residence.apartment_name SEPARATOR ",") AS apartment_names,

            GROUP_CONCAT(DISTINCT building_residence.floor ORDER BY building_residence.floor SEPARATOR ",") AS floors,
            GROUP_CONCAT(DISTINCT building_residence.	block ORDER BY building_residence.	block SEPARATOR ",") AS blocks,

            GROUP_CONCAT(DISTINCT building_unit_types.description ORDER BY building_unit_types.description SEPARATOR ",") AS unit_type, 
            building.name AS building_name,
            meter_allocations.is_active AS allocations_is_active
        FROM 
            meter_allocations
        JOIN 
            building ON building.id = meter_allocations.building_id 
        LEFT JOIN 
            building_residence ON FIND_IN_SET(building_residence.id, meter_allocations.residence_id)
        LEFT JOIN 
            building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
        WHERE 
            meter_allocations.is_active = 1
        GROUP BY 
            meter_allocations.id, meter_allocations.meter_id, building.name, meter_allocations.residence_id, meter_allocations.building_id,meter_allocations.is_active 
    ) AS meterAllocations ON meterAllocations.meter_id = meters.id
    LEFT JOIN 
        (SELECT 
            mr.id,
            mr.meter_id,
            mr.reading_value,
            COALESCE(mr.reading_value - previous_reading.reading_value, 0) AS total_energy,
            mr.created_at
        FROM 
            meter_readings mr
        LEFT JOIN 
            meter_readings previous_reading ON mr.meter_id = previous_reading.meter_id 
            AND previous_reading.id = (
                SELECT MAX(id) 
                FROM meter_readings 
                WHERE meter_id = mr.meter_id AND id < mr.id
            )
        JOIN 
            (SELECT 
                meter_id, 
                MAX(id) AS max_id
            FROM 
                meter_readings
            GROUP BY 
                meter_id) AS latest 
        ON 
            mr.meter_id = latest.meter_id AND mr.id = latest.max_id 
        ) AS latest_parent_reading 
    ON 
        latest_parent_reading.meter_id = meters.id
    LEFT JOIN 
        (SELECT 
            parent_id,
            COUNT(id) AS sub_meter_count
        FROM 
            meters
        GROUP BY 
            parent_id) AS sub_meter_counts 
    ON 
        sub_meter_counts.parent_id = meters.id
    WHERE meters.parent_id = 0';

    
    IF allocations_status != "" AND allocations_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meterAllocations.is_active = ', allocations_status);
    END IF;

    IF meter_status != "" AND meter_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.is_active = ', meter_status);
    END IF;

    IF meter_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.id = ', meter_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND meterAllocations.building_id IN (', building_ids, ')');
    END IF;

    IF allocations_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meterAllocations.id = ', allocations_id);
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meterAllocations.residence_id = ', residence_id);
    END IF;

     -- Handle searchBy filtering
    IF searchBy IS NOT NULL AND searchBy != "" THEN
        SET sql_query = CONCAT(sql_query, ' AND (meters.sr_no LIKE "%', searchBy, '%" 
            OR meterAllocations.apartment_names LIKE "%', searchBy, '%" 
            OR meterAllocations.building_name LIKE "%', searchBy, '%"
            OR meterAllocations.unit_type LIKE "%', searchBy, '%")');
    END IF;

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

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER;

--

CREATE TABLE `laundry_requests` (
    `id` int(11) NOT NULL,
    `resident_id` int(11) DEFAULT NULL,
    `residence_id` int(11) DEFAULT NULL,
    `building_id` int(11) DEFAULT NULL,
    `request_date` date DEFAULT NULL,
    `created_by` int(11) DEFAULT NULL,
    `status` enum(
        'PENDING',
        'APPROVED',
        'PICKUP',
        'DELIVERED',
        'WASHED',
        'CANCELLED'
    ) NOT NULL DEFAULT 'APPROVED',
    `cancelled_by` int(11) DEFAULT NULL,
    `laundry_type` enum('DryCleaning', 'Normal') NOT NULL DEFAULT 'Normal',
    `request_assigned_to` int(11) DEFAULT NULL,
    `pickup_by` int(11) DEFAULT NULL,
    `delivered_by` int(11) DEFAULT NULL,
    `approved_date` date DEFAULT NULL,
    `pickup_date` date DEFAULT NULL,
    `washed_date` date DEFAULT NULL,
    `delivered_date` date DEFAULT NULL,
    `cancelled_date` date DEFAULT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` timestamp NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `laundry_requests` ADD PRIMARY KEY (`id`);

ALTER TABLE `laundry_requests`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
ALTER TABLE `complainants`
ADD `intern_box_id` INT NULL DEFAULT NULL AFTER `intern_box_token`;

ALTER TABLE `complainants`
CHANGE `intern_box_id` `intern_box_id` VARCHAR(255) NULL DEFAULT NULL;

---

ALTER TABLE `meter_allocations`
CHANGE `residence_id` `residence_id` VARCHAR(100) NULL DEFAULT NULL;

-- 14/10/2024

ALTER TABLE `laundry_requests`
ADD `cancel_reason` TEXT NULL AFTER `cancelled_by`;

-- 15/04/2024
DROP PROCEDURE if EXISTS meterWithReadingSummary;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `meterWithReadingSummary`(
    IN meter_id INT,
    IN meter_parent_id INT,
    IN allocations_id INT,
    IN residence_id INT,
    IN building_ids VARCHAR(55),
    IN searchBy VARCHAR(100),
    IN allocations_status INT,
    IN meter_status INT
)
BEGIN

    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT 
        meters.id,
        meters.parent_id,
        meters.sr_no,
        meters.manufacturer,
        meters.model,
        meters.capacity,
        meters.start_at_reading,
        meters.is_active as is_meter_active,
        latest_parent_reading.reading_value AS current_reading_value,
        latest_parent_reading.reading_date AS current_reading_date,
        latest_parent_reading.total_energy,
        meter_created_by.name AS meter_created_by_name,
        meter_created_by.userRole AS meter_created_by_userRole,
        meter_allocations.building_id,
        meter_allocations.residence_id,
        meter_allocations.apartment_names,
        meter_allocations.building_name,
        meter_allocations.unit_type,
        meter_allocations.blocks,
        meter_allocations.floors,
        meters.created_at,
        meters.updated_at
    FROM 
        meters
    JOIN 
        user meter_created_by ON meter_created_by.id = meters.created_by 
    LEFT JOIN 
    (
        SELECT 
            meter_allocations.id,
            meter_allocations.meter_id,
            meter_allocations.building_id,
            meter_allocations.residence_id,
            GROUP_CONCAT(DISTINCT building_residence.apartment_name ORDER BY building_residence.apartment_name SEPARATOR ",") AS apartment_names,

            GROUP_CONCAT(DISTINCT building_residence.floor ORDER BY building_residence.floor SEPARATOR ",") AS floors,
            GROUP_CONCAT(DISTINCT building_residence.	block ORDER BY building_residence.	block SEPARATOR ",") AS blocks,

            GROUP_CONCAT(DISTINCT building_unit_types.description ORDER BY building_unit_types.description SEPARATOR ",") AS unit_type, 
            building.name AS building_name,
            meter_allocations.is_active AS allocations_is_active
        FROM 
            meter_allocations
        JOIN 
            building ON building.id = meter_allocations.building_id 
        LEFT JOIN 
            building_residence ON FIND_IN_SET(building_residence.id, meter_allocations.residence_id)
        LEFT JOIN 
            building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
        WHERE 
            meter_allocations.is_active = 1
        GROUP BY 
            meter_allocations.id, meter_allocations.meter_id, building.name, meter_allocations.residence_id, meter_allocations.building_id,meter_allocations.is_active 
    ) AS meter_allocations ON meter_allocations.meter_id = meters.id
    LEFT JOIN 
        (SELECT 
            mr.id,
            mr.meter_id,
            mr.reading_value,
            mr.reading_date,
            COALESCE(mr.reading_value - previous_reading.reading_value, 0) AS total_energy,
            mr.created_at
        FROM 
            meter_readings mr
        LEFT JOIN 
            meter_readings previous_reading ON mr.meter_id = previous_reading.meter_id 
            AND previous_reading.id = (
                SELECT MAX(id) 
                FROM meter_readings 
                WHERE meter_id = mr.meter_id AND id < mr.id
            )
        JOIN 
            (SELECT 
                meter_id, 
                MAX(id) AS max_id
            FROM 
                meter_readings
            GROUP BY 
                meter_id) AS latest 
        ON 
            mr.meter_id = latest.meter_id AND mr.id = latest.max_id 
        ) AS latest_parent_reading 
    ON 
        latest_parent_reading.meter_id = meters.id WHERE 1=1';

    
    IF allocations_status != "" AND allocations_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.is_active = ', allocations_status);
    END IF;

    IF meter_status != "" AND meter_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.is_active = ', meter_status);
    END IF;

    IF meter_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.id = ', meter_id);
    END IF;

    IF meter_parent_id IS NOT NULL AND meter_parent_id !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.parent_id = ', meter_parent_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.building_id IN (', building_ids, ')');
    END IF;

    IF allocations_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.id = ', allocations_id);
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meter_allocations.residence_id = ', residence_id);
    END IF;

     -- Handle searchBy filtering
    IF searchBy IS NOT NULL AND searchBy != "" THEN
        SET sql_query = CONCAT(sql_query, ' AND (meters.sr_no LIKE "%', searchBy, '%" 
            OR building_residence.apartment_name LIKE "%', searchBy, '%" 
            OR building.name LIKE "%', searchBy, '%"
            OR building_unit_types.Description LIKE "%', searchBy, '%")');
    END IF;

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

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER;

--
CREATE TABLE `laundry_discrepancies` (
    `id` int(11) NOT NULL,
    `request_id` int(11) DEFAULT NULL,
    `laundry_category_id` int(11) DEFAULT NULL,
    `remark` text DEFAULT NULL,
    `status` enum(
        'Pending',
        'Resolved',
        'Lost_and_Damage'
    ) NOT NULL DEFAULT 'Pending',
    `remark_by_admin` int(11) DEFAULT NULL,
    `attachment` varchar(100) DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `laundry_discrepancies` ADD PRIMARY KEY (`id`);

ALTER TABLE `laundry_discrepancies`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--

CREATE TABLE `laundry_request_details` (
    `id` int(11) NOT NULL,
    `request_id` int(11) NOT NULL,
    `cloth_category_id` int(11) NOT NULL,
    `quantity` int(11) DEFAULT NULL,
    `attachment` varchar(100) DEFAULT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `laundry_request_details` ADD PRIMARY KEY (`id`);

ALTER TABLE `laundry_request_details`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT = 3;

--

-- 16/10/2024

CREATE TABLE `electricity_charges` (
    `id` int(11) NOT NULL,
    `building_id` int(11) DEFAULT NULL,
    `residence_id` int(11) DEFAULT NULL,
    `unit_type_id` int(11) DEFAULT NULL,
    `charge_per_unit_rate` decimal(10, 2) DEFAULT 0.00,
    `free_unit` decimal(10, 2) DEFAULT 0.00,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `electricity_charges` ADD PRIMARY KEY (`id`);

ALTER TABLE `electricity_charges`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT = 3;
--

-- 17/10/2024

ALTER TABLE `laundry_requests`
CHANGE `status` `status` ENUM(
    'PENDING',
    'APPROVED',
    'PICKUP',
    'DELIVERED',
    'WASHED',
    'CANCELLED'
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'PENDING';

ALTER TABLE `laundry_requests`
ADD `net_weight_of_items` DECIMAL(10, 3) NULL AFTER `delivered_date`;

ALTER TABLE `laundry_categories`
ADD `icon` VARCHAR(200) NULL DEFAULT NULL AFTER `name`,
ADD `media_id` INT NULL DEFAULT NULL AFTER `icon`;

-- 21/10/2024
INSERT INTO
    `settings` (
        `feature`,
        `rule_name`,
        `description`,
        `rule_type`,
        `rule_value`,
        `updated_by`,
        `updated_at`
    )
VALUES (
        'LAUNDRY',
        'laundry_price_per_kg',
        'Sets the cost per kilogram for laundry services.',
        'int',
        '100',
        '0',
        '2024-08-22 12:23:51'
    );

INSERT INTO
    `settings` (
        `feature`,
        `rule_name`,
        `description`,
        `rule_type`,
        `rule_value`,
        `updated_by`,
        `updated_at`
    )
VALUES (
        'LAUNDRY',
        'per_month_free_laundry_request',
        'Sets the per month free laundry request services.',
        'int',
        '1',
        '0',
        '2024-08-22 12:23:51'
    );

UPDATE `settings`
SET
    `feature` = 'LAUNDRY',
    `description` = 'Sets the monthly limit for free laundry requests, including weight and frequency.',
    `rule_type` = 'json',
    `rule_value` = '{"numberOfTimes": 2,"maximumWeightKg": 7}',
    `updated_by` = '0',
    `updated_at` = '2024-08-22 12:23:51'
WHERE
    `rule_name` = 'per_month_free_laundry_request'

CREATE TABLE `laundry_charges` (
    `id` int(11) NOT NULL,
    `laundry_category_id` int(11) NOT NULL,
    `laundry_vendor_id` int(11) DEFAULT NULL,
    `charge_per_item` decimal(10, 2) DEFAULT NULL,
    `charge_per_kg` decimal(10, 2) DEFAULT NULL,
    `is_active` tinyint(1) DEFAULT 1,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `laundry_charges` ADD PRIMARY KEY (`id`);

ALTER TABLE `laundry_charges`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

----

CREATE TABLE `laundry_vendors` (
    `id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    `address` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `phone_no` varchar(15) NOT NULL,
    `is_active` tinyint(1) DEFAULT 1,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `laundry_vendors`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `email` (`email`),
ADD UNIQUE KEY `phone_no` (`phone_no`);

ALTER TABLE `laundry_vendors`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `laundry_requests`
CHANGE `status` `status` ENUM(
    'PENDING',
    'ACCEPT',
    'APPROVED',
    'PICKUP',
    'DELIVERED',
    'WASHED',
    'CANCELLED'
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'PENDING';

-- 23/10/2024

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `masterMeterWithReadingSummary`(
    IN meter_id INT,
    IN allocations_id INT,
    IN residence_id INT,
    IN building_ids VARCHAR(55),
    IN searchBy VARCHAR(100),
    IN allocations_status INT,
    IN meter_status INT
)
BEGIN

    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT 
        meters.id,
        meters.parent_id,
        meters.sr_no,
        meters.manufacturer,
        meters.model,
        meters.capacity,
        meters.start_at_reading,
        meters.is_active AS is_meter_active,
        COALESCE(sub_meter_reading.sub_reading_value,0) AS current_reading_value,
        latest_parent_reading.reading_value AS current_meter_reading_value,
        latest_parent_reading.total_energy,
        COALESCE(sub_meter_counts.sub_meter_count, 0) AS sub_meter_count,
        meter_created_by.name AS meter_created_by_name,
        meter_created_by.userRole AS meter_created_by_userRole,
        meterAllocations.building_id,
        meterAllocations.residence_id,
        meterAllocations.apartment_names,
        meterAllocations.building_name,
        meterAllocations.unit_type,
        meterAllocations.blocks,
        meterAllocations.floors,
        meters.created_at,
        meters.updated_at
    FROM 
        meters
    JOIN 
        user meter_created_by ON meter_created_by.id = meters.created_by 
    LEFT JOIN 
    (
        SELECT 
            meter_allocations.id,
            meter_allocations.meter_id,
            meter_allocations.building_id,
            meter_allocations.residence_id,
            GROUP_CONCAT(DISTINCT building_residence.apartment_name ORDER BY building_residence.apartment_name SEPARATOR ",") AS apartment_names,

            GROUP_CONCAT(DISTINCT building_residence.floor ORDER BY building_residence.floor SEPARATOR ",") AS floors,
            GROUP_CONCAT(DISTINCT building_residence.	block ORDER BY building_residence.	block SEPARATOR ",") AS blocks,

            GROUP_CONCAT(DISTINCT building_unit_types.description ORDER BY building_unit_types.description SEPARATOR ",") AS unit_type, 
            building.name AS building_name,
            meter_allocations.is_active AS allocations_is_active
        FROM 
            meter_allocations
        JOIN 
            building ON building.id = meter_allocations.building_id 
        LEFT JOIN 
            building_residence ON FIND_IN_SET(building_residence.id, meter_allocations.residence_id)
        LEFT JOIN 
            building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
        WHERE 
            meter_allocations.is_active = 1
        GROUP BY 
            meter_allocations.id, meter_allocations.meter_id, building.name, meter_allocations.residence_id, meter_allocations.building_id,meter_allocations.is_active 
    ) AS meterAllocations ON meterAllocations.meter_id = meters.id
    LEFT JOIN
    (SELECT meters.id, SUM(latest_meter_reading.reading_value) as sub_reading_value FROM meters
        JOIN 
        (SELECT meters.id,meters.parent_id,latest_reading.reading_value FROM meters
        JOIN 
                (SELECT latest_reading.meter_id,latest_reading.reading_value from meters
                    JOIN (SELECT 
                            mr.meter_id, 
                            mr.reading_value
                        FROM 
                            meter_readings mr
                        JOIN 
                            (SELECT 
                                meter_id, 
                                MAX(id) AS max_id
                            FROM 
                                meter_readings
                            GROUP BY 
                                meter_id) AS latest 
                        ON 
                            mr.meter_id = latest.meter_id AND mr.id = latest.max_id
                        ) AS latest_reading 
                        on latest_reading.meter_id = meters.id 
            ) AS latest_reading
        on meters.id = latest_reading.meter_id)
        AS latest_meter_reading on latest_meter_reading.parent_id = meters.id
        GROUP BY meters.id) 
    AS sub_meter_reading ON  sub_meter_reading.id = meters.id
    LEFT JOIN 
        (SELECT 
            mr.id,
            mr.meter_id,
            mr.reading_value,
            COALESCE(mr.reading_value - previous_reading.reading_value, 0) AS total_energy,
            mr.created_at
        FROM 
            meter_readings mr
        LEFT JOIN 
            meter_readings previous_reading ON mr.meter_id = previous_reading.meter_id 
            AND previous_reading.id = (
                SELECT MAX(id) 
                FROM meter_readings 
                WHERE meter_id = mr.meter_id AND id < mr.id
            )
        JOIN 
            (SELECT 
                meter_id, 
                MAX(id) AS max_id
            FROM 
                meter_readings
            GROUP BY 
                meter_id) AS latest 
        ON 
            mr.meter_id = latest.meter_id AND mr.id = latest.max_id 
        ) AS latest_parent_reading 
    ON 
        latest_parent_reading.meter_id = meters.id
    LEFT JOIN 
        (SELECT 
            parent_id,
            COUNT(id) AS sub_meter_count
        FROM 
            meters
        GROUP BY 
            parent_id) AS sub_meter_counts 
    ON 
        sub_meter_counts.parent_id = meters.id
    WHERE meters.parent_id = 0';

    
    IF allocations_status != "" AND allocations_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meterAllocations.is_active = ', allocations_status);
    END IF;

    IF meter_status != "" AND meter_status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.is_active = ', meter_status);
    END IF;

    IF meter_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meters.id = ', meter_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND meterAllocations.building_id IN (', building_ids, ')');
    END IF;

    IF allocations_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meterAllocations.id = ', allocations_id);
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND meterAllocations.residence_id = ', residence_id);
    END IF;

     -- Handle searchBy filtering
    IF searchBy IS NOT NULL AND searchBy != "" THEN
        SET sql_query = CONCAT(sql_query, ' AND (meters.sr_no LIKE "%', searchBy, '%" 
            OR meterAllocations.apartment_names LIKE "%', searchBy, '%" 
            OR meterAllocations.building_name LIKE "%', searchBy, '%"
            OR meterAllocations.unit_type LIKE "%', searchBy, '%")');
    END IF;

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

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER;

--

INSERT INTO
    `suggestion_quality_codes` (
        `quality_remark`,
        `quality_remark_text`
    )
VALUES ('5', 'Poor'),
    ('6', 'Bad'),
    ('7', 'Excellent');

-- 28/10/204
CREATE TABLE `md_currencies` (
    `id` int(11) NOT NULL,
    `currency_name` varchar(150) NOT NULL,
    `currency_code` varchar(100) DEFAULT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` datetime NOT NULL DEFAULT current_timestamp(),
    `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `md_currencies` ADD PRIMARY KEY (`id`);

ALTER TABLE `md_currencies`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `md_tax_rates` (
    `id` int(11) NOT NULL,
    `tax_name` varchar(200) DEFAULT NULL,
    `tax_type` enum(
        'Percent',
        'Flat',
        'Feference'
    ) DEFAULT NULL,
    `value` varchar(150) DEFAULT NULL,
    `type` enum('Single', 'Group') NOT NULL DEFAULT 'Single',
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` datetime NOT NULL DEFAULT current_timestamp(),
    `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `md_tax_rates` ADD PRIMARY KEY (`id`);

ALTER TABLE `md_tax_rates`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

-- 29/10/2024
CREATE TABLE `md_payment_terms` (
    `id` int(11) NOT NULL,
    `name` varchar(150) NOT NULL,
    `type` enum('Day', 'Month') NOT NULL DEFAULT 'Day',
    `no_of_days` int(11) NOT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` datetime NOT NULL DEFAULT current_timestamp(),
    `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `md_payment_terms` ADD PRIMARY KEY (`id`);

ALTER TABLE `md_payment_terms`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

-- 29/10/2024
  `id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `type` enum('Day','Month') NOT NULL DEFAULT 'Day',
  `no_of_days` int(11) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `md_payment_terms` ADD PRIMARY KEY (`id`);

ALTER TABLE `md_payment_terms`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--

CREATE TABLE `md_billing_accounts` (
    `id` int(11) NOT NULL,
    `account_name` varchar(150) NOT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` datetime NOT NULL DEFAULT current_timestamp(),
    `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `md_billing_accounts` ADD PRIMARY KEY (`id`);

ALTER TABLE `md_billing_accounts`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--

CREATE TABLE `md_units` (
    `id` int(11) NOT NULL,
    `unit_short_name` varchar(150) DEFAULT NULL,
    `unit_long_name` varchar(200) DEFAULT NULL,
    `unit_data_type` varchar(150) DEFAULT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` datetime NOT NULL DEFAULT current_timestamp(),
    `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `md_units` ADD PRIMARY KEY (`id`);

ALTER TABLE `md_units` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

-- 05/11/2024

CREATE TABLE `billing_charge_unit_pricings` (
    `id` int(11) NOT NULL,
    `charge_id` int(11) DEFAULT NULL,
    `unit_id` int(11) DEFAULT NULL,
    `amount` decimal(10, 2) DEFAULT NULL,
    `currency` int(11) DEFAULT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` datetime NOT NULL DEFAULT current_timestamp(),
    `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `billing_charge_unit_pricings` ADD PRIMARY KEY (`id`);

ALTER TABLE `billing_charge_unit_pricings`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--

CREATE TABLE `billing_charges` (
    `id` int(11) NOT NULL,
    `charge_name` varchar(200) NOT NULL,
    `sac` varchar(200) DEFAULT NULL,
    `tax_preference` enum('TAXABLE', 'NON-TAXABLE') DEFAULT 'NON-TAXABLE',
    `account` int(11) DEFAULT NULL,
    `description` text DEFAULT NULL,
    `default_tax_rate` int(11) DEFAULT NULL,
    `payment_terms` int(11) DEFAULT NULL,
    `late_charge` int(11) DEFAULT NULL,
    `repeat_frequency` int(11) DEFAULT NULL,
    `repeat_frequency_unit` int(11) DEFAULT NULL,
    `invoice_required` tinyint(4) DEFAULT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 1,
    `created_at` datetime NOT NULL DEFAULT current_timestamp(),
    `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

ALTER TABLE `billing_charges` ADD PRIMARY KEY (`id`);

ALTER TABLE `billing_charges`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

-- 06/11/2024
DROP PROCEDURE IF EXISTS getInventoryAssignedNotAssignedFilterCount;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getInventoryAssignedNotAssignedFilterCount`(
    IN `category_id` INT,
    IN `status` VARCHAR(100),
    IN `inventory_id` INT,
    IN `building_ids` VARCHAR(55),
    IN `residence_id` INT,
    IN `resident_id` INT
)
BEGIN
    DECLARE sql_query TEXT;

    SET sql_query = 'SELECT
        inventory_categories.id AS inventory_categories_id, 
        inventory_categories.name AS inventory_categories_name,
        COUNT(inventory_categories.name) AS total,
        COUNT(CASE WHEN residence_inventories.residence_id IS NOT NULL THEN 1 END) AS assigned,
        COUNT(CASE WHEN inventories.health_status = "DAMAGED" AND residence_inventories.residence_id IS NOT NULL THEN 1 END) AS damage_in_room,
        COUNT(CASE WHEN inventories.health_status = "DAMAGED" AND residence_inventories.residence_id IS NULL THEN 1 END) AS damage_out_room,
        (COUNT(inventory_categories.name) 
            - (COUNT(CASE WHEN residence_inventories.residence_id IS NOT NULL THEN 1 END) 
            + COUNT(CASE WHEN inventories.health_status = 0 AND residence_inventories.residence_id IS NULL THEN 1 END))) AS surplus
    FROM 
        inventories
    JOIN 
        inventory_categories ON inventory_categories.id = inventories.category_id
    LEFT JOIN 
        residence_inventories ON residence_inventories.inventory_id = inventories.id 
    LEFT JOIN 
        building_residence ON building_residence.id = residence_inventories.residence_id
    LEFT JOIN 
        building ON building.id = building_residence.building_id
    LEFT JOIN 
        building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
    LEFT JOIN 
        user AS creator ON creator.id = inventories.created_by WHERE 1=1';

    IF status != "" AND status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.health_status = "', status, '"');
    END IF;

    IF category_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.category_id = ', category_id);
    END IF;

    IF inventory_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.id = ', inventory_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids != "" THEN
        SET sql_query = CONCAT(sql_query, ' AND building_residence.building_id IN (', building_ids, ')');
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.residence_id = ', residence_id);
    END IF;

    IF resident_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.resident_id = ', resident_id);
    END IF;

    SET sql_query = CONCAT(sql_query, ' GROUP BY inventory_categories.name');
    SET sql_query = CONCAT(sql_query, ' ORDER BY inventories.id DESC');

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

DELIMITER;

--

DROP PROCEDURE IF EXISTS getInventoryWithDamagedDisposedFilterCount;

DELIMITER $$

CREATE PROCEDURE getInventoryWithDamagedDisposedFilterCount(
    IN category_id INT,
    IN status VARCHAR(100),
    IN inventory_id INT,
    IN building_ids VARCHAR(55),
    IN residence_id INT,
    IN resident_id INT
)
BEGIN
    -- Total , Damaged, Disposed Inventories count
    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT 
        COUNT(inventories.id) AS totalInventories,
        COUNT(CASE WHEN inventories.health_status = "DAMAGED" THEN 1 END ) AS damagedInventories,
        COUNT(CASE WHEN inventories.health_status = "DISPOSED" THEN 1 END) AS dispostedInventories
    FROM 
        inventories
    JOIN 
        inventory_categories ON inventory_categories.id = inventories.category_id
    LEFT JOIN 
        residence_inventories ON residence_inventories.inventory_id = inventories.id 
        AND residence_inventories.status = 1
    LEFT JOIN 
        building_residence ON building_residence.id = residence_inventories.residence_id
    LEFT JOIN 
        building ON building.id = building_residence.building_id
    LEFT JOIN 
        building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
    LEFT JOIN 
        user AS creator ON creator.id = inventories.created_by
    WHERE 1=1';


    IF status != "" AND status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.health_status = ', status);
    END IF;

    IF category_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.category_id = ', category_id);
    END IF;

    IF inventory_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.id = ', inventory_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND building_residence.building_id IN (', building_ids, ')');
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.residence_id = ', residence_id);
    END IF;

    IF resident_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.resident_id = ', resident_id);
    END IF;

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

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END $$

DELIMITER;

--

DROP PROCEDURE IF EXISTS `getInventoryWithFilter`;

DELIMITER $$

CREATE PROCEDURE getInventoryWithFilter(
    IN category_id INT,
    IN status VARCHAR(100),
    IN inventory_id INT,
    IN building_ids VARCHAR(55),
    IN residence_id INT,
    IN resident_id INT,
    IN searchBy VARCHAR(100),
    IN filterBy VARCHAR(100)
)
BEGIN
    DECLARE sql_query TEXT;
    SET sql_query = 'SELECT inventories.*, 
                            inventory_categories.name AS inventory_category_name,
                            residence_inventories.residence_id,
                            building_residence.apartment_name,
                            building.name AS building_name,
                            building_unit_types.description,
                            creator.name AS creator_name,
                            building_residence.floor,
                            residence_inventories.resident_id,
                            building_residence.building_id,
                            building_residence.unit_type AS unit_type_id,
                            building_unit_types.Description AS unit_type_name,
                            residence_inventories.status AS residence_inventories_status,
                            complainants.complainant_name
                     FROM inventories
                     JOIN inventory_categories ON inventory_categories.id = inventories.category_id
                     LEFT JOIN residence_inventories ON residence_inventories.inventory_id = inventories.id 
                          AND residence_inventories.status = 1
                     LEFT JOIN complainants ON complainants.id = residence_inventories.resident_id
                     LEFT JOIN building_residence ON building_residence.id = residence_inventories.residence_id
                     LEFT JOIN building ON building.id = building_residence.building_id
                     LEFT JOIN building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
                     LEFT JOIN user AS creator ON creator.id = inventories.created_by WHERE 1=1';

    
    IF status != "" AND status IS NOT NULL THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.status = ', status);
    END IF;

    IF category_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.category_id = ', category_id);
    END IF;

    IF inventory_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.id = ', inventory_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET sql_query = CONCAT(sql_query, ' AND building_residence.building_id IN (', building_ids, ')');
    END IF;

    IF filterBy IS NOT NULL AND filterBy !="" AND filterBy ="assigned" THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.residence_id IS NOT NULL');
    END IF;

    IF filterBy IS NOT NULL AND filterBy !="" AND filterBy ="damage_in_room" THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.health_status = "DAMAGED" AND residence_inventories.residence_id IS NOT NULL');
    END IF;

    IF filterBy IS NOT NULL AND filterBy !="" AND filterBy ="damage_out_room" THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.health_status = "DAMAGED" AND residence_inventories.residence_id IS NULL');
    END IF;

    IF filterBy IS NOT NULL AND filterBy !="" AND filterBy ="surplus" THEN
        SET sql_query = CONCAT(sql_query, ' AND inventories.health_status <> "DAMAGED" AND residence_inventories.residence_id IS NULL');
    END IF;

    IF residence_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.residence_id = ', residence_id);
    END IF;

    IF resident_id > 0 THEN
        SET sql_query = CONCAT(sql_query, ' AND residence_inventories.resident_id = ', resident_id);
    END IF;

    -- Handle searchBy filtering
    IF searchBy IS NOT NULL AND searchBy != "" THEN
        SET sql_query = CONCAT(sql_query, ' AND (inventories.label LIKE "%', searchBy, '%" 
            OR building_residence.apartment_name LIKE "%', searchBy, '%" 
            OR inventory_categories.name LIKE "%', searchBy, '%"
            OR building_unit_types.Description LIKE "%', searchBy, '%"
            OR complainants.complainant_name LIKE "%', searchBy, '%")');
    END IF;

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

    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END $$

DELIMITER;

-- 08/11/2024

DROP VIEW IF EXISTS laundry_request_view;

CREATE VIEW laundry_request_view AS
SELECT
    laundry_requests.id AS laundry_request_id,
    laundry_requests.created_by,
    laundry_requests.delivered_by,
    laundry_requests.approved_by,
    laundry_requests.cancelled_by,
    laundry_requests.pickup_by,
    laundry_requests.resident_id,
    laundry_requests.residence_id,
    laundry_requests.request_date,
    laundry_requests.id,
    laundry_requests.status,
    laundry_requests.laundry_type,
    laundry_requests.cancel_reason,
    laundry_requests.request_assigned_to,
    laundry_requests.approved_date,
    laundry_requests.pickup_date,
    laundry_requests.washed_date,
    laundry_requests.delivered_date,
    laundry_requests.cancelled_date,
    laundry_requests.net_weight_of_items,
    laundry_requests.is_active,
    created_by_user.name AS created_by_user_name,
    delivered_by_user.name AS delivered_by_user_name,
    approved_by_user.name AS approved_by_user_name,
    cancelled_by_user.name AS cancelled_by_user_name,
    pickup_by_user.name AS pickup_by_user_name,
    active_resident_view.complainant_name,
    active_resident_view.contact_no,
    active_resident_view.apartment_name,
    active_resident_view.building_name,
    active_resident_view.course_year,
    active_resident_view.branch_name,
    active_resident_view.course_name,
    active_resident_view.email,
    active_resident_view.course_id,
    active_resident_view.branch_id,
    -- Laundry request details
    laundryRequestDetails.laundry_request_details_total_clothes_quantity,
    laundryRequestDetails.laundry_request_details_quantity,
    laundryRequestDetails.laundry_details_categories_name,
    laundryRequestDetails.laundry_request_details_attachment,
    laundryRequestDetails.laundry_details_medias_url,
    laundryRequestDetails.laundry_request_details_is_active,
    laundryRequestDetails.laundry_request_details_id,
    -- Laundry discrepancies
    laundryDiscrepancies.laundry_discrepancies_total_clothes_quantity,
    laundryDiscrepancies.laundry_discrepancies_remark_by_admin,
    laundryDiscrepancies.laundry_discrepancies_status,
    laundryDiscrepancies.laundry_discrepancies_remark,
    laundryDiscrepancies.laundry_discrepancies_categories_name,
    laundryDiscrepancies.laundry_discrepancies_attachment,
    laundryDiscrepancies.laundry_discrepancies_medias_url,
    laundryDiscrepancies.laundry_discrepancies_id

FROM laundry_requests

JOIN (
    SELECT
        laundry_request_details.request_id,
        GROUP_CONCAT(
            laundry_request_details.id
            ORDER BY laundry_request_details.id SEPARATOR ","
        ) AS laundry_request_details_id,
        SUM(
            laundry_request_details.quantity
        ) AS laundry_request_details_total_clothes_quantity,
        GROUP_CONCAT(
            laundry_request_details.quantity
            ORDER BY laundry_request_details.id SEPARATOR ","
        ) AS laundry_request_details_quantity,
        GROUP_CONCAT(
            DISTINCT laundry_categories.name
            ORDER BY laundry_request_details.id SEPARATOR ","
        ) AS laundry_details_categories_name,
        GROUP_CONCAT(
            DISTINCT laundry_request_details.attachment
            ORDER BY laundry_request_details.id SEPARATOR ","
        ) AS laundry_request_details_attachment,
        GROUP_CONCAT(
            DISTINCT medias.url
            ORDER BY laundry_request_details.id SEPARATOR ","
        ) AS laundry_details_medias_url,
        GROUP_CONCAT(
            DISTINCT laundry_request_details.is_active
            ORDER BY laundry_request_details.id SEPARATOR ","
        ) AS laundry_request_details_is_active
    FROM
        laundry_request_details
        JOIN laundry_categories ON laundry_categories.id = laundry_request_details.cloth_category_id
        LEFT JOIN medias ON medias.id = laundry_request_details.attachment
    GROUP BY
        laundry_request_details.request_id
) AS laundryRequestDetails ON laundryRequestDetails.request_id = laundry_requests.id

LEFT JOIN (
    SELECT
        laundry_discrepancies.request_id,
        GROUP_CONCAT(
            laundry_discrepancies.id
            ORDER BY laundry_discrepancies.id SEPARATOR ","
        ) AS laundry_discrepancies_id,
        SUM(laundry_discrepancies.id) AS laundry_discrepancies_total_clothes_quantity,
        GROUP_CONCAT(
            DISTINCT laundry_discrepancies.remark_by_admin
            ORDER BY laundry_discrepancies.id SEPARATOR ","
        ) AS laundry_discrepancies_remark_by_admin,
        GROUP_CONCAT(
            DISTINCT laundry_discrepancies.status
            ORDER BY laundry_discrepancies.id SEPARATOR ","
        ) AS laundry_discrepancies_status,
        GROUP_CONCAT(
            DISTINCT laundry_discrepancies.remark
            ORDER BY laundry_discrepancies.id SEPARATOR ","
        ) AS laundry_discrepancies_remark,
        GROUP_CONCAT(
            DISTINCT laundry_categories.name
            ORDER BY laundry_discrepancies.id SEPARATOR ","
        ) AS laundry_discrepancies_categories_name,
        GROUP_CONCAT(
            DISTINCT laundry_discrepancies.attachment
            ORDER BY laundry_discrepancies.id SEPARATOR ","
        ) AS laundry_discrepancies_attachment,
        GROUP_CONCAT(
            DISTINCT medias.url
            ORDER BY laundry_discrepancies.id SEPARATOR ","
        ) AS laundry_discrepancies_medias_url
    FROM
        laundry_discrepancies
        JOIN laundry_categories ON laundry_categories.id = laundry_discrepancies.laundry_category_id
        LEFT JOIN medias ON medias.id = laundry_discrepancies.attachment
    GROUP BY
        laundry_discrepancies.request_id
) AS laundryDiscrepancies ON laundryDiscrepancies.request_id = laundry_requests.id

JOIN user AS created_by_user ON created_by_user.id = laundry_requests.created_by
LEFT JOIN user AS delivered_by_user ON delivered_by_user.id = laundry_requests.delivered_by
LEFT JOIN user AS approved_by_user ON approved_by_user.id = laundry_requests.approved_by
LEFT JOIN user AS cancelled_by_user ON cancelled_by_user.id = laundry_requests.cancelled_by
LEFT JOIN user AS pickup_by_user ON pickup_by_user.id = laundry_requests.pickup_by
JOIN active_resident_view ON active_resident_view.id = laundry_requests.resident_id

GROUP BY
    laundry_requests.id,
    laundry_requests.created_by,
    laundry_requests.delivered_by,
    laundry_requests.approved_by,
    laundry_requests.cancelled_by,
    laundry_requests.pickup_by,
    laundry_requests.resident_id,
    laundry_requests.residence_id,
    laundry_requests.status,
    laundry_requests.laundry_type,
    laundry_requests.cancel_reason,
    laundry_requests.request_assigned_to,
    laundry_requests.approved_date,
    laundry_requests.pickup_date,
    laundry_requests.washed_date,
    laundry_requests.delivered_date,
    laundry_requests.cancelled_date,
    laundry_requests.request_date,
    laundry_requests.net_weight_of_items,
    laundry_requests.is_active,
    created_by_user.name,
    delivered_by_user.name,
    approved_by_user.name,
    cancelled_by_user.name,
    pickup_by_user.name,
    active_resident_view.complainant_name,
    active_resident_view.contact_no,
    active_resident_view.apartment_name,
    active_resident_view.building_name,
    active_resident_view.course_year,
    active_resident_view.branch_name,
    active_resident_view.course_name,
    active_resident_view.email,
    active_resident_view.course_id,
    active_resident_view.branch_id,
    laundryRequestDetails.laundry_request_details_total_clothes_quantity,
    laundryRequestDetails.laundry_request_details_quantity,
    laundryRequestDetails.laundry_details_categories_name,
    laundryRequestDetails.laundry_request_details_attachment,
    laundryRequestDetails.laundry_details_medias_url,
    laundryRequestDetails.laundry_request_details_is_active,
    laundryRequestDetails.laundry_request_details_id,
    laundryDiscrepancies.laundry_discrepancies_total_clothes_quantity,
    laundryDiscrepancies.laundry_discrepancies_remark_by_admin,
    laundryDiscrepancies.laundry_discrepancies_status,
    laundryDiscrepancies.laundry_discrepancies_remark,
    laundryDiscrepancies.laundry_discrepancies_categories_name,
    laundryDiscrepancies.laundry_discrepancies_attachment,
    laundryDiscrepancies.laundry_discrepancies_medias_url,
    laundryDiscrepancies.laundry_discrepancies_id
ORDER BY laundry_requests.id DESC;


-- 09/11/2024

DROP PROCEDURE IF EXISTS getInventoryAssignedNotAssignedFilterCount;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getInventoryAssignedNotAssignedFilterCount`(
    IN `category_id` INT,
    IN `status` VARCHAR(100),
    IN `inventory_id` INT,
    IN `building_ids` VARCHAR(55),
    IN `residence_id` INT,
    IN `resident_id` INT
)
BEGIN
    DECLARE @sql_query TEXT;

    SET @sql_query = 'SELECT
        inventory_categories.id AS inventory_categories_id, 
        inventory_categories.name AS inventory_categories_name,
        COUNT(inventory_categories.name) AS total,
        COUNT(CASE WHEN inventories.residence_id IS NOT NULL OR inventories.resident_id IS NOT NULL THEN 1 END) AS assigned,
        COUNT(CASE WHEN inventories.health_status = "DAMAGED" AND inventories.residence_id IS NOT NULL THEN 1 END) AS damage_in_room,
        COUNT(CASE WHEN inventories.health_status = "DAMAGED" AND inventories.residence_id IS NULL THEN 1 END) AS damage_out_room,
        COUNT(CASE WHEN inventories.health_status <> "DAMAGED" AND inventories.residence_id IS NULL THEN 1 END) AS surplus
    FROM 
        inventories
    JOIN 
        inventory_categories ON inventory_categories.id = inventories.category_id
    LEFT JOIN 
        building_residence ON building_residence.id = inventories.residence_id
    LEFT JOIN 
        building ON building.id = building_residence.building_id
    LEFT JOIN 
        building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
    LEFT JOIN 
        user AS creator ON creator.id = inventories.created_by
    WHERE 1=1';

    IF status != "" AND status IS NOT NULL AND status != "assigned" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.health_status = "', status, '"');
    END IF;

    IF status IS NOT NULL AND status != "" AND status = "assigned" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND (inventories.residence_id IS NOT NULL OR inventories.resident_id IS NOT NULL)');
    END IF;

    IF category_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.category_id = ', category_id);
    END IF;

    IF inventory_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.id = ', inventory_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids != "" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND building_residence.building_id IN (', building_ids, ')');
    END IF;

    IF residence_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.residence_id = ', residence_id);
    END IF;

    IF resident_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.resident_id = ', resident_id);
    END IF;

    SET @sql_query = CONCAT(@sql_query, ' GROUP BY inventory_categories.name');
    SET @sql_query = CONCAT(@sql_query, ' ORDER BY inventories.id DESC');

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

-- 09/11/2024

DROP procedure if EXISTS getInventoryWithFilter;
DELIMITER $$
CREATE PROCEDURE `getInventoryWithFilter`(
    IN category_id INT,
    IN status VARCHAR(100),
    IN inventory_id INT,
    IN building_ids VARCHAR(55),
    IN residence_id INT,
    IN resident_id INT,
    IN searchBy VARCHAR(100),
    IN filterBy VARCHAR(100)
)
BEGIN
    SET @sql_query = 'SELECT inventories.*, 
                            inventory_categories.name AS inventory_category_name,
                            building_residence.apartment_name,
                            building.name AS building_name,
                            building_unit_types.description,
                            creator.name AS creator_name,
                            building_residence.floor,
                            building_residence.unit_type AS unit_type_id,
                            building_unit_types.Description AS unit_type_name,
                            complainants.complainant_name
                     FROM inventories
                     JOIN inventory_categories ON inventory_categories.id = inventories.category_id
                     LEFT JOIN (SELECT inventories.id, GROUP_CONCAT(complainants.complainant_name SEPARATOR ",") As complainant_name FROM inventories JOIN complainants ON FIND_IN_SET(complainants.id, inventories.resident_id) > 0) AS complainants ON complainants.id = inventories.id
                     LEFT JOIN building_residence ON building_residence.id = inventories.residence_id
                     LEFT JOIN building ON building.id = inventories.building_id
                     LEFT JOIN building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
                     LEFT JOIN user AS creator ON creator.id = inventories.created_by WHERE 1=1';

    
    IF status != "" AND status IS NOT NULL THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.status = ', status);
    END IF;

    IF category_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.category_id = ', category_id);
    END IF;

    IF inventory_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.id = ', inventory_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND building_residence.building_id IN (', building_ids, ')');
    END IF;

    IF filterBy IS NOT NULL AND filterBy != "" AND filterBy = "assigned" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND (inventories.residence_id IS NOT NULL OR inventories.resident_id IS NOT NULL)');
    END IF;


    IF filterBy IS NOT NULL AND filterBy !="" AND filterBy ="damage_in_room" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.health_status = "DAMAGED" AND inventories.residence_id IS NOT NULL');
    END IF;

    IF filterBy IS NOT NULL AND filterBy !="" AND filterBy ="damage_out_room" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.health_status = "DAMAGED" AND inventories.residence_id IS NULL');
    END IF;

    IF filterBy IS NOT NULL AND filterBy !="" AND filterBy ="surplus" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.health_status <> "DAMAGED" AND inventories.residence_id IS NULL');
    END IF;

    IF residence_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.residence_id = ', residence_id);
    END IF;

    IF resident_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.resident_id = ', resident_id);
    END IF;

    -- Handle searchBy filtering
    IF searchBy IS NOT NULL AND searchBy != "" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND (inventories.label LIKE "%', searchBy, '%" 
            OR building_residence.apartment_name LIKE "%', searchBy, '%" 
            OR inventory_categories.name LIKE "%', searchBy, '%"
            OR building_unit_types.Description LIKE "%', searchBy, '%"
            OR complainants.complainant_name LIKE "%', searchBy, '%")');
    END IF;

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

    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

-- 12/11/2014

DELIMITER $$

CREATE PROCEDURE `getInventoryActivities`(
    IN `activity_by` INT, 
    IN `id` INT, 
    IN `activity` VARCHAR(255),
    IN `building_ids` VARCHAR(255),
    IN `residence_id` INT,
    IN `resident_id` INT
)
BEGIN
    -- Start building the query
    SET @sql_query = 'SELECT inventory_activities.*, 
                            inventory_categories.name AS inventory_category_name, 
                            building_residence.apartment_name, 
                            building.name AS building_name, 
                            building_unit_types.description, 
                            complainants.complainant_name, 
                            activity_by_user.name AS activity_by_user_name 
                     FROM inventory_activities
                     JOIN user AS activity_by_user 
                         ON activity_by_user.id = inventory_activities.activity_by
                     JOIN inventories 
                         ON inventory_activities.inventory_id = inventories.id
                     LEFT JOIN inventory_categories 
                         ON inventory_categories.id = inventories.category_id
                     LEFT JOIN complainants 
                         ON complainants.id = inventories.category_id
                     LEFT JOIN building_residence 
                         ON building_residence.id = inventory_activities.residence_id
                     LEFT JOIN building 
                         ON building.id = building_residence.building_id
                     LEFT JOIN building_unit_types 
                         ON building_unit_types.unit_type_id = building_residence.unit_type 
                     WHERE 1=1';

    -- Apply filter for `activity_by` if provided
    IF activity_by IS NOT NULL AND activity_by > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventory_activities.activity_by = ', activity_by);
    END IF;

    -- Apply filter for `id` if provided
    IF id IS NOT NULL AND id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventory_activities.id = ', id);
    END IF;

    -- Apply filter for `activity` if provided
    IF activity IS NOT NULL AND activity != '' THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventory_activities.activity = "', activity, '"');
    END IF;

    -- Apply filter for `residence_id` if provided
    IF residence_id IS NOT NULL AND residence_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventory_activities.residence_id = ', residence_id);
    END IF;

    -- Apply filter for `resident_id` if provided
    IF resident_id IS NOT NULL AND resident_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventory_activities.resident_id = ', resident_id);
    END IF;

    -- Apply filter for `building_ids` if provided
    IF building_ids IS NOT NULL AND building_ids != '' THEN
        SET @sql_query = CONCAT(@sql_query, ' AND building_residence.building_id IN (', building_ids, ')');
    END IF;

    -- Execute the dynamically built query
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;


-- 13/11/2024

ALTER TABLE `indiscipline_types` DROP `severity`;

ALTER TABLE `indiscipline_actions` DROP `severity`;

ALTER TABLE `indiscipline_types` ADD `flagId` INT NULL AFTER `type`;

ALTER TABLE `indiscipline_settings` DROP `severity_min`, DROP `severity_max`;

ALTER TABLE `indiscipline_settings` ADD `severity` INT NULL AFTER `flag_color`;

-- 

DROP PROCEDURE IF EXISTS getIndisciplineFlagCount;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getIndisciplineFlagCount`(
    IN residence_id INT,
    IN resident_id INT,
    IN unit_type VARCHAR(255),
    IN building_id INT,
    IN indisActionId INT,
    IN flagId INT
)
BEGIN
    -- Declare a variable to hold the SQL query dynamically
    SET @sql_query = 'SELECT 
                          indiscipline_settings.*, 
                          COALESCE(indisciplineTypes.flagCount, 0) AS flagCount
                      FROM 
                          indiscipline_settings
                      LEFT JOIN (
                          SELECT 
                              indiscipline_types.id AS indiscipline_typesId, 
                              indiscipline_types.flagId,
                              COUNT(indiscipline_types.id) AS flagCount
                          FROM 
                              indiscipline_types
                          JOIN 
                              indiscipline_actions 
                              ON indiscipline_types.id = indiscipline_actions.type_id
                          LEFT JOIN 
                              building_residence 
                              ON building_residence.id = indiscipline_actions.residence_id
                          WHERE 1';

    -- Dynamically build the WHERE clause based on the input parameters
    IF residence_id IS NOT NULL THEN
        SET @sql_query = CONCAT(@sql_query, ' AND indiscipline_actions.residence_id = ', residence_id);
    END IF;

    IF resident_id IS NOT NULL THEN
        SET @sql_query = CONCAT(@sql_query, ' AND indiscipline_actions.resident_id = ', resident_id);
    END IF;

    IF unit_type IS NOT NULL THEN
        SET @sql_query = CONCAT(@sql_query, ' AND building_residence.unit_type = "', unit_type, '"');
    END IF;

    IF building_id IS NOT NULL THEN
        SET @sql_query = CONCAT(@sql_query, ' AND building_residence.building_id = ', building_id);
    END IF;

    IF indisActionId IS NOT NULL THEN
        SET @sql_query = CONCAT(@sql_query, ' AND indiscipline_actions.id = ', indisActionId);
    END IF;

    -- Close the inner query and add the outer query's conditions
    SET @sql_query = CONCAT(@sql_query, '
                        GROUP BY 
                            indiscipline_types.id, indiscipline_types.flagId
                    ) AS indisciplineTypes 
                    ON indisciplineTypes.flagId = indiscipline_settings.id');

    -- Add the condition for the flagId parameter
    IF flagId IS NOT NULL THEN
        SET @sql_query = CONCAT(@sql_query, ' WHERE indiscipline_settings.id = ', flagId);
    END IF;

    -- Execute the dynamically generated query
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
END$$

DELIMITER ;

-- 14/11/2024
drop procedure if exists GetIndisciplineType;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetIndisciplineType`(IN `p_id` INT(11), IN `p_only_active` INT(1))
BEGIN
    SET @sql = 'SELECT indiscipline_settings.flag_color, indiscipline_settings.severity AS flag_severity, indiscipline_types.*
FROM indiscipline_types 
LEFT JOIN indiscipline_settings ON indiscipline_settings.id = indiscipline_types.flagId WHERE 1=1';

    IF p_id > 0 THEN
        SET @sql = CONCAT(@sql, " AND `indiscipline_types`.`id` = ", p_id);
	END IF;
    IF p_only_active = 1 THEN
    	SET @sql = CONCAT(@sql, " AND `indiscipline_types`.`is_active` = ",p_only_active);
    
    END IF;

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

END$$
DELIMITER ;

-- 

drop procedure if exists GetAllResidentGuestPass;
DELIMITER $$
CREATE PROCEDURE `GetAllResidentGuestPass`(IN `records` VARCHAR(10), IN `result_from` VARCHAR(20), IN `result_to` VARCHAR(20), IN `records_limit` INT)
    NO SQL
BEGIN
SET @sql = "select gate_pass.id,building_residence.building_id,gate_pass.visitor_id,ifnull(expected_dt,'') as expected_dt,ifnull(expected_tm,'') as expected_tm,\r\n\t\t\tifnull(entry_dt,'') as entry_dt, ifnull(entry_tm,'') as entry_tm,ifnull(exit_dt,'') as exit_dt,\r\n\t\t\tifnull(exit_tm,'') as exit_tm,residences,residence_actions,entry_approved_by,extra_pax,(CASE WHEN complainants.complainant_name is not null THEN complainants.complainant_name ELSE gate_pass.to_meet  END) AS to_meet,purpose,vehicle,gate_pass.address,gate_pass.status,visitors.name,visitors.contact_no,visitors.gender,visitors.id_no,identity_proof_type.description as id_description,visitors.identity_proof_type,visitors.file,visitors.file_ext,complainants.complainant_name,complainants.contact_no as complainant_contact,user.name as permit_user,entry_approve_users.name as entry_approve_user_name,gate_pass.rejected_at,exitUsers.name as exitUser,gate_pass.updated_at from gate_pass\r\n\t\t\tleft join complainants on complainants.id=gate_pass.resident_id\r\n\r\n            \r\n  left join building_residence on FIND_IN_SET(building_residence.id, REPLACE(REPLACE(gate_pass.residences, '[', ''), ']', '')) > 0           left join user as entry_approve_users on entry_approve_users.id=gate_pass.entry_approve_user left join user on user.id=gate_pass.permit_user\r\nLEFT JOIN visitors on visitors.id=gate_pass.visitor_id\r\nleft join identity_proof_type on identity_proof_type.id=visitors.identity_proof_type LEFT JOIN user AS exitUsers ON exitUsers.id = gate_pass.exit_user\r\n\t\t\tWHERE 1=1";

IF (records ='ENTRY') THEN
    SET @sql = CONCAT(@sql," AND gate_pass.status in ('NEW','APPROVAL_PENDING','CANCELLED' ) and ABS(TIMESTAMPDIFF(HOUR,TIMESTAMP(expected_dt,expected_tm),now()))<8");

ELSEIF ( result_from !='0000-00-00' AND result_to !='0000-00-00') THEN
    SET @sql = CONCAT(@sql," AND gate_pass.expected_dt between '",result_from,"' and '",result_to,"'");
ELSEIF (result_from !='0000-00-00') THEN
    SET @sql = CONCAT(@sql," AND gate_pass.expected_dt >='",result_from,"'");
ELSEIF (result_to !='0000-00-00') THEN
    SET @sql = CONCAT(@sql," AND gate_pass.expected_dt <='",result_to,"'");
END IF;


SET @sql = CONCAT(@sql,' order by  gate_pass.expected_dt desc,gate_pass.expected_tm desc');
IF ( records_limit != '' ) THEN
	SET @sql = CONCAT(@sql,' limit ',records_limit);
END IF;

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

-- 15/11/2024

DROP PROCEDURE IF EXISTS `GetIndisciplineAction`;
DELIMITER $$
CREATE PROCEDURE `GetIndisciplineAction`(IN `p_id` INT, IN `p_from_date` DATE, IN `p_to_date` DATE, IN `p_resident_id` INT)
BEGIN
    -- Define the base SQL query
    SET @sql = 'SELECT indiscipline_actions.*, creator.name AS creator_name, 
                       building.name AS building_name, 
                       building_residence.apartment_name, 
                      COALESCE(indisciplineTypes.flagCount,0) AS flag_count,
                      COALESCE(indisciplineTypes.total_fine_amount,0) AS total_fine_amount,
                       indiscipline_types.type, 
                       indiscipline_settings.flag_color,
                       indiscipline_settings.severity,
                       complainants.complainant_name AS resident_name, 
                       complainants.contact_no,
                       complainants.is_activated
                FROM indiscipline_actions 
                LEFT JOIN (SELECT 
                    indiscipline_types.id AS indiscipline_typesId,
                    indiscipline_types.flagId,
                    indiscipline_actions.resident_id,
                    indiscipline_actions.residence_id,
                    SUM(indiscipline_actions.fine_amount) AS total_fine_amount,
                    COUNT(indiscipline_types.id) AS flagCount
                FROM 
                    indiscipline_types
                JOIN 
                    indiscipline_actions 
                    ON indiscipline_types.id = indiscipline_actions.type_id
                GROUP BY 
                    indiscipline_types.id, indiscipline_types.flagId,
                    indiscipline_actions.resident_id,indiscipline_actions.residence_id
                    ) AS indisciplineTypes 
                    ON indisciplineTypes.indiscipline_typesId = indiscipline_actions.type_id 
                    AND indiscipline_actions.resident_id = indisciplineTypes.resident_id
                    AND indiscipline_actions.residence_id = indisciplineTypes.residence_id
                JOIN complainants ON complainants.id = indiscipline_actions.resident_id
                JOIN user AS creator ON creator.id = indiscipline_actions.created_by
                JOIN indiscipline_types ON indiscipline_types.id = indiscipline_actions.type_id 
                JOIN building_residence ON indiscipline_actions.residence_id = building_residence.id
                JOIN building ON building.id = building_residence.building_id
                JOIN indiscipline_settings ON indiscipline_types.flagId = indiscipline_settings.id WHERE 1=1';

    -- Add condition if p_id is greater than 0
    IF p_id > 0 THEN
        SET @sql = CONCAT(@sql, ' AND indiscipline_actions.id = ', p_id);
    END IF;
    
-- Add date range conditions if provided
IF p_from_date IS NOT NULL AND p_to_date IS NOT NULL THEN
	SET @sql = CONCAT(@sql, ' AND indiscipline_actions.action_date BETWEEN "', p_from_date, ' 00:00:00" AND "', p_to_date, ' 23:59:59"');
END IF;

IF p_resident_id > 0 THEN
     SET @sql = CONCAT(@sql, ' AND indiscipline_actions.resident_id = ', p_resident_id);
END IF;


    -- Add GROUP BY clause
    -- SET @sql = CONCAT(@sql, ' GROUP BY indiscipline_actions.resident_id, indiscipline_settings.flag_color');
    
    SET @sql = CONCAT(@sql, ' ORDER BY indiscipline_actions.id DESC');

    -- Prepare, execute, and deallocate the statement
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

-- 

drop procedure if exists GetMoreThanTwoResidentFlagCounts;
DELIMITER $$

CREATE PROCEDURE GetMoreThanTwoResidentFlagCounts(
    IN p_residence_id INT,
    IN p_resident_id INT,
    IN p_flagId INT,
    IN p_building_ids VARCHAR(255) -- New parameter: comma-separated list of building_ids
)
BEGIN
    -- Declare a variable to hold the query string
    SET @sql = 'SELECT 
                    indiscipline_settings.flag_color,
                    COALESCE(SUM(indisciplineTypes.flagCount), 0) AS flagCount
                FROM 
                    indiscipline_settings
                LEFT JOIN (
                    SELECT 
                        indiscipline_types.flagId,
                        indiscipline_actions.resident_id,
                        indiscipline_actions.residence_id,
                        COUNT(indiscipline_types.id) AS flagCount
                    FROM 
                        indiscipline_types
                    JOIN 
                        indiscipline_actions ON indiscipline_types.id = indiscipline_actions.type_id
                    LEFT JOIN 
                        building_residence ON building_residence.id = indiscipline_actions.residence_id
                    WHERE 1
                    GROUP BY 
                        indiscipline_types.id, 
                        indiscipline_types.flagId,
                        indiscipline_actions.resident_id,
                        indiscipline_actions.residence_id
                    HAVING COUNT(indiscipline_actions.residence_id) > 2
                ) AS indisciplineTypes 
                ON indisciplineTypes.flagId = indiscipline_settings.id
                LEFT JOIN building_residence ON building_residence.id = indisciplineTypes.residence_id
                GROUP BY indiscipline_settings.flag_color';

    -- Conditionally append filters to the query based on the provided parameters

    -- Filter by residence_id if provided
    IF p_residence_id IS NOT NULL THEN
        SET @sql = CONCAT(@sql, ' AND indisciplineTypes.residence_id = ', p_residence_id);
    END IF;

    -- Filter by resident_id if provided
    IF p_resident_id IS NOT NULL THEN
        SET @sql = CONCAT(@sql, ' AND indisciplineTypes.resident_id = ', p_resident_id);
    END IF;

    -- Filter by flagId if provided
    IF p_flagId IS NOT NULL THEN
        SET @sql = CONCAT(@sql, ' AND indisciplineTypes.flagId = ', p_flagId);
    END IF;

    -- Filter by building_ids if provided
    IF p_building_ids IS NOT NULL AND p_building_ids != '' THEN
        SET @sql = CONCAT(@sql, ' AND building_residence.building_id IN (', p_building_ids, ')');
    END IF;

    -- Execute the dynamic query
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END $$

DELIMITER ;

-- 
drop procedure if exists InsertIndisciplineSetting;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertIndisciplineSetting`(IN `p_flag_color` VARCHAR(50), IN `p_severity` INT, OUT `record_id` INT)
BEGIN
    INSERT INTO indiscipline_settings (flag_color, severity)
    VALUES (p_flag_color, p_severity);
    
    SET record_id = LAST_INSERT_ID();
END$$
DELIMITER ;

-- 

drop procedure if exists UpdateIndisciplineSetting;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateIndisciplineSetting`(IN `p_id` INT, IN `p_flag_color` VARCHAR(50), IN `p_severity` INT)
BEGIN
    UPDATE `indiscipline_settings`
    SET flag_color = p_flag_color,
        severity = p_severity
    WHERE id = p_id;
END$$
DELIMITER ;

-- 

drop procedure if exists UpdateIndisciplineType;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateIndisciplineType`(
    IN p_id INT,
    IN p_type VARCHAR(255),
    IN p_default_fine DECIMAL(10, 2),
    IN p_is_active TINYINT(1),
    IN flagId VARCHAR(10)
)
BEGIN
    -- Update the indiscipline_types table
    UPDATE indiscipline_types
    SET
        type = p_type,
        default_fine = p_default_fine,
        is_active = p_is_active,
        flagId = flagId,
        updated_at = NOW()
    WHERE id = p_id;
END$$
DELIMITER ;

-- 

drop procedure if exists InsertIndisciplineType;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertIndisciplineType`(IN `p_type` VARCHAR(255), IN `p_default_fine` DECIMAL(10,2),`flagId` VARCHAR(10), OUT `p_new_id` INT)
BEGIN
    -- Insert into indiscipline_types table
    INSERT INTO indiscipline_types (type, default_fine, is_active, flagId, created_at, updated_at)
    VALUES (p_type, p_default_fine, 1, flagId, NOW(), NOW());
    
    -- Get the last inserted ID
    SET p_new_id = LAST_INSERT_ID();
END$$
DELIMITER ;

-- 19/11/2024

DROP PROCEDURE IF EXISTS GetComplainsByRoleListData;
DELIMITER $$

CREATE PROCEDURE `GetComplainsByRoleListData`(
    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 `page_number` INT(3), 
    IN `page_size` INT(11)
)
BEGIN
    DECLARE is_self_assign INT DEFAULT 0;
    DECLARE department_ids VARCHAR(500) DEFAULT '';
    DECLARE team_member_id INT DEFAULT 0;
    DECLARE offset 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');
    
    -- Add pagination with LIMIT and OFFSET
    IF (page_number IS NOT NULL AND page_number !="" AND page_size IS NOT NULL AND page_size > 0) THEN
        -- Calculate offset for pagination
        SET offset = (page_number - 1) * page_size;

        SET @sql = CONCAT(@sql, ' LIMIT ', page_size, ' OFFSET ', offset);
    END IF;

    -- Execute the dynamic SQL for the complaints data
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


    -- total count with filter
    -- Now, for the total count query
    SET @sqlCount = "SELECT COUNT(*) AS total FROM complaint_view WHERE 1 = 1";

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

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

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

    if (input_role_id>5) then 
        SET @sqlCount = CONCAT(@sqlCount, ' 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 @sqlCount = CONCAT(@sqlCount, ' 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 @sqlCount = CONCAT(@sqlCount,' AND complaint_view.department_id =',@department_id);
                SET @sqlCount = CONCAT(@sqlCount, " 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 @sqlCount = CONCAT(@sqlCount, ' AND complaint_view.team_member_id = ', @team_member_id);
            ELSE
                SET @sqlCount = CONCAT(@sqlCount, " AND FIND_IN_SET(complaint_view.department_id,'", @department_ids, "') ");
                SET @sqlCount = CONCAT(@sqlCount, ' 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 @sqlCount = CONCAT(@sqlCount, ' ))');
    END IF;

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

    -- Execute the dynamic SQL for total count
    PREPARE stmt1 FROM @sqlCount;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

END$$

DELIMITER ;

-- 21/11/2024

DROP PROCEDURE IF EXISTS getInventoryAssignedNotAssignedFilterCount;
DELIMITER $$

CREATE PROCEDURE `getInventoryAssignedNotAssignedFilterCount`(
    IN `category_id` INT,
    IN `status` VARCHAR(100),
    IN `inventory_id` INT,
    IN `building_ids` VARCHAR(55),
    IN `residence_id` INT,
    IN `resident_id` INT
)
BEGIN

    -- Initialize the SQL query
    SET @sql_query = 'SELECT
        inventories.category_id AS inventory_categories_id, 
        inventory_categories.inventory_categories_name,
        sum(inventory_categories.total) AS total,
        sum(inventory_categories.assigned) AS assigned,
        sum(inventory_categories.damage_in_room) AS damage_in_room,
        sum(inventory_categories.damage_out_room) AS damage_out_room,
        sum(inventory_categories.surplus) AS surplus,
        sum(inventory_categories.not_assigned_inventory) AS not_assigned_inventory
    FROM 
        inventories
    JOIN (SELECT
        inventories.id AS inventories_id, 
        inventory_categories.name AS inventory_categories_name,
        COUNT(inventory_categories.name) AS total,
        COUNT(CASE WHEN inventories.residence_id IS NOT NULL OR inventories.resident_id IS NOT NULL THEN 1 END) AS assigned,
        COUNT(CASE WHEN inventories.health_status = "DAMAGED" AND inventories.residence_id IS NOT NULL THEN 1 END) AS damage_in_room,
        COUNT(CASE WHEN inventories.health_status = "DAMAGED" AND inventories.residence_id IS NULL THEN 1 END) AS damage_out_room,
        COUNT(CASE WHEN inventories.health_status <> "DAMAGED" AND inventories.residence_id IS NULL THEN 1 END) AS surplus,
        COUNT(CASE WHEN inventories.health_status <> "DISPOSED" AND inventories.residence_id IS NULL THEN 1 END) AS not_assigned_inventory
    FROM 
        inventories
	JOIN inventory_categories ON inventory_categories.id = inventories.category_id
    LEFT JOIN 
        building_residence ON building_residence.id = inventories.residence_id
    LEFT JOIN 
        building ON building.id = building_residence.building_id
    LEFT JOIN 
        building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
     GROUP BY inventory_categories.name, inventories.id,inventories.health_status,inventories.residence_id
    ) AS inventory_categories on inventory_categories.inventories_id = inventories.id
    WHERE 1=1';

    -- Append conditions to the query based on the provided parameters

    IF status != "" AND status IS NOT NULL AND status != "assigned" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.health_status = "', status, '"');
    END IF;

    IF status IS NOT NULL AND status != "" AND status = "assigned" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND (inventories.residence_id IS NOT NULL OR inventories.resident_id IS NOT NULL)');
    END IF;

    IF category_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.category_id = ', category_id);
    END IF;

    IF inventory_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.id = ', inventory_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids != "" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.building_id IN (', building_ids, ')');
    END IF;

    IF residence_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.residence_id = ', residence_id);
    END IF;

    IF resident_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.resident_id = ', resident_id);
    END IF;

    -- Finalize the query
    SET @sql_query = CONCAT(@sql_query, ' GROUP BY inventory_categories.inventory_categories_name, inventories.category_id');
    -- SET @sql_query = CONCAT(@sql_query, ' ORDER BY inventories.id DESC');

    -- Prepare and execute the query
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;

-- 
ALTER TABLE `department_team` CHANGE `department_id` `department_id` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

ALTER TABLE `inventory_activities` ADD `building_id` INT NULL AFTER `residence_id`;

-- 

DROP PROCEDURE IF EXISTS `UpdateDepartmentTeam`;
DELIMITER $$
CREATE PROCEDURE `UpdateDepartmentTeam`(IN `memberName` VARCHAR(50),
IN `memberContact` BIGINT, IN `memberEmail` VARCHAR(100), IN `memberWorkingarea` VARCHAR(150), IN `memberDeptid` VARCHAR(240), IN `memid` INT)
BEGIN
UPDATE `department_team` SET member_name = memberName,
member_contactno= memberContact,
member_email = memberEmail,
working_area = memberWorkingarea,
department_id = memberDeptid
WHERE  member_id = memid;
END$$
DELIMITER ;


-- 

DROP procedure if EXISTS getInventoryWithFilter;
DELIMITER $$
CREATE PROCEDURE `getInventoryWithFilter`(
    IN category_id INT,
    IN status VARCHAR(100),
    IN inventory_id INT,
    IN building_ids VARCHAR(55),
    IN residence_id INT,
    IN resident_id INT,
    IN searchBy VARCHAR(100),
    IN filterBy VARCHAR(100)
)
BEGIN
    SET @sql_query = 'SELECT inventories.*, 
                            inventory_categories.name AS inventory_category_name,
                            building_residence.apartment_name,
                            building.name AS building_name,
                            building_unit_types.description,
                            creator.name AS creator_name,
                            building_residence.floor,
                            building_residence.unit_type AS unit_type_id,
                            building_unit_types.Description AS unit_type_name,
                            complainants.complainant_name
                     FROM inventories
                     JOIN inventory_categories ON inventory_categories.id = inventories.category_id
                     LEFT JOIN (
                     SELECT inventories.id, GROUP_CONCAT(complainants.complainant_name SEPARATOR ",") As complainant_name FROM inventories 
                     JOIN complainants ON FIND_IN_SET(complainants.id, inventories.resident_id) > 0 group by inventories.id) AS complainants ON complainants.id = inventories.id
                     LEFT JOIN building_residence ON building_residence.id = inventories.residence_id
                     LEFT JOIN building ON building.id = inventories.building_id
                     LEFT JOIN building_unit_types ON building_unit_types.unit_type_id = building_residence.unit_type
                     LEFT JOIN user AS creator ON creator.id = inventories.created_by WHERE 1=1';

    
    IF status != "" AND status IS NOT NULL THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.status = ', status);
    END IF;

    IF category_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.category_id = ', category_id);
    END IF;

    IF inventory_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND FIND_IN_SET(complainants.id, inventories.resident_id) > 0inventories.id = ', inventory_id);
    END IF;

    -- Filter by building_ids if provided
    IF building_ids IS NOT NULL AND building_ids !="" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.building_id IN (', building_ids, ')');
    END IF;

    IF filterBy IS NOT NULL AND filterBy != "" AND filterBy = "assigned" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND (inventories.residence_id IS NOT NULL OR inventories.resident_id IS NOT NULL)');
    END IF;


    IF filterBy IS NOT NULL AND filterBy !="" AND filterBy ="damage_in_room" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.health_status = "DAMAGED" AND inventories.residence_id IS NOT NULL');
    END IF;

    IF filterBy IS NOT NULL AND filterBy !="" AND filterBy ="damage_out_room" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.health_status = "DAMAGED" AND inventories.residence_id IS NULL');
    END IF;

    IF filterBy IS NOT NULL AND filterBy !="" AND filterBy ="surplus" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.health_status <> "DAMAGED" AND inventories.residence_id IS NULL');
    END IF;

    -- not assigned inavailable 

    IF residence_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.residence_id = ', residence_id);
    END IF;

    IF resident_id > 0 THEN
        SET @sql_query = CONCAT(@sql_query, ' AND inventories.resident_id = ', resident_id);
    END IF;

    -- Handle searchBy filtering
    IF searchBy IS NOT NULL AND searchBy != "" THEN
        SET @sql_query = CONCAT(@sql_query, ' AND (inventories.label LIKE "%', searchBy, '%" 
            OR building_residence.apartment_name LIKE "%', searchBy, '%" 
            OR inventory_categories.name LIKE "%', searchBy, '%"
            OR building_unit_types.Description LIKE "%', searchBy, '%"
            OR complainants.complainant_name LIKE "%', searchBy, '%")');
    END IF;

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

    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;


-- not_assigned_building, not_assigned_residence, not_assigned_resident