CREATE DEFINER=`goldendish_hms`@`%` FUNCTION `get_asset_value_at_date`(asset_id int, target_date date) RETURNS decimal(15,2) DETERMINISTIC BEGIN DECLARE purchase_date DATE; DECLARE initial_value DECIMAL(15,2); DECLARE depreciation_rate DECIMAL(5,2); DECLARE months_diff INT; DECLARE depreciated_value DECIMAL(15,2); -- Retrieve the purchase date, initial value, and depreciation rate for the asset SELECT ab.purchase_date, ab.value, ac.depreciation_rate INTO purchase_date, initial_value, depreciation_rate FROM asset_batches ab JOIN assets a ON ab.asset_id = a.id JOIN asset_categories ac ON a.category_id = ac.id WHERE a.id = asset_id LIMIT 1; -- Calculate the number of months between the purchase date and the end of the target month SET months_diff = TIMESTAMPDIFF(MONTH, purchase_date, LAST_DAY(target_date)); -- Calculate the depreciated value SET depreciated_value = initial_value * POW(1 - depreciation_rate / 100, months_diff); RETURN depreciated_value; END; CREATE DEFINER=`goldendish_hms`@`%` PROCEDURE `applyDepreciation`(IN assetBatchId int, IN startMonth int, IN startYear int, IN endMonth int, IN endYear int, IN userId int, IN storeId int, OUT finalValue decimal(10, 2)) BEGIN DECLARE previousValue DECIMAL(10, 2); DECLARE currentValue DECIMAL(10, 2); DECLARE monthlyRate DECIMAL(10, 4); DECLARE assetId INT; DECLARE countRows INT; DECLARE categoryId INT; DECLARE depreciationRate DECIMAL(5, 2); DECLARE batchQuantity INT; DECLARE tempMonth INT; DECLARE tempYear INT; DECLARE finished INT DEFAULT 0; DECLARE purchaseDate DATE; DECLARE depreciationDate DATE; -- Check if the asset batch exists and retrieve purchase date SELECT COUNT(*), purchase_date INTO countRows, purchaseDate FROM asset_batches WHERE id = assetBatchId; IF countRows > 0 THEN -- Retrieve the asset batch details SELECT asset_id, value, quantity INTO assetId, previousValue, batchQuantity FROM asset_batches WHERE id = assetBatchId; -- Retrieve the asset details SELECT category_id INTO categoryId FROM assets WHERE id = assetId; -- Retrieve the category details SELECT depreciation_rate INTO depreciationRate FROM asset_categories WHERE id = categoryId; -- Convert annual depreciation rate to monthly rate SET monthlyRate = depreciationRate / 100 / 12; -- Initialize loop variables to start from the month following the purchase date SET tempMonth = MONTH(purchaseDate); SET tempYear = YEAR(purchaseDate); -- Adjust tempMonth and tempYear to start from the next month IF tempMonth = 12 THEN SET tempMonth = 1; SET tempYear = tempYear + 1; ELSE SET tempMonth = tempMonth + 1; END IF; -- Loop through each month from the purchase date to the specified end month/year WHILE NOT finished DO -- Calculate the depreciation SET currentValue = previousValue * (1 - monthlyRate); IF currentValue < 0 THEN SET currentValue = 0; END IF; -- Insert depreciation records only within the user-specified date range IF (tempYear > startYear OR (tempYear = startYear AND tempMonth >= startMonth)) AND (tempYear < endYear OR (tempYear = endYear AND tempMonth <= endMonth)) THEN -- Create the depreciation record SET depreciationDate = LAST_DAY(CONCAT(tempYear,'-', LPAD(tempMonth, 2, '0'), '-01')); -- Check if the depreciation record already exists IF NOT EXISTS (SELECT 1 FROM asset_depreciations WHERE batch_id = assetBatchId AND depreciation_month = tempYear AND depreciation_year = tempYear) THEN INSERT INTO asset_depreciations (batch_id, depreciation_month, depreciation_year, previous_value, quantity, previous_total_value, current_value, current_total_value, applied_rate,depreciation_date,status, store_id, created_by) VALUES (assetBatchId, tempMonth, tempYear, previousValue, batchQuantity, previousValue * batchQuantity, currentValue, currentValue * batchQuantity, depreciationRate,depreciationDate,'PENDING', storeId, userId); -- Update batch with the final current value UPDATE asset_batches SET current_value = currentValue WHERE id = assetBatchId; end if; END IF; -- Update the previous value for the next iteration SET previousValue = currentValue; -- Move to the next month IF tempMonth = 12 THEN SET tempMonth = 1; SET tempYear = tempYear + 1; ELSE SET tempMonth = tempMonth + 1; END IF; -- Check if we have reached the specified end month and year IF tempYear > endYear OR (tempYear = endYear AND tempMonth > endMonth) THEN SET finished = 1; END IF; END WHILE; -- Set the final value to the OUT parameter SET finalValue = previousValue; END IF; END; CREATE DEFINER=`goldendish_hms`@`%` PROCEDURE `sp_cancel_sale`(IN p_sale_id int, OUT p_message varchar(255)) BEGIN DECLARE v_sales_product_id INT; DECLARE v_sales_product_business_type varchar(35); DECLARE v_sales_order_id INT; DECLARE v_sales_quantity INT; DECLARE v_sales_store_id INT; DECLARE v_sales_stock_session_id INT; DECLARE v_count INT; DECLARE sale_not_found BOOLEAN DEFAULT TRUE; DECLARE cur CURSOR FOR SELECT product_id, quantity, store_id, stock_session_id FROM sale_details WHERE sale_id = p_sale_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET sale_not_found = FALSE; select order_id into v_sales_order_id from sales where id = p_sale_id; -- Check if there is an open sale session SELECT COUNT(*) INTO v_count FROM sales WHERE id = p_sale_id; IF v_count > 0 THEN -- Begin transaction START TRANSACTION; -- Loop through sale details and update stock status OPEN cur; read_loop: LOOP FETCH cur INTO v_sales_product_id, v_sales_quantity, v_sales_store_id, v_sales_stock_session_id; -- Exit the loop when there are no more rows IF NOT sale_not_found THEN LEAVE read_loop; END IF; select pc.business_type into v_sales_product_business_type from product_categories pc join products p on pc.id = p.category_id where p.id = v_sales_product_id; if v_sales_product_business_type = 'Products' then -- Update stock statuses UPDATE stock_statuses SET quantity = quantity + v_sales_quantity WHERE product_id = v_sales_product_id AND store_id = v_sales_store_id; -- Insert into stock movements INSERT INTO stock_movements(product_id, from_store_id, to_store_id, quantity, activity, description, movement_date, created_by, stock_session_id) VALUES (v_sales_product_id, 1, v_sales_store_id, v_sales_quantity, 'CANCELLED SALE', 'Sale Cancellation', CURRENT_DATE(), 1, v_sales_stock_session_id); end if; END LOOP; CLOSE cur; -- Update the cancelled status of the sale delete from sales WHERE id = p_sale_id; delete from orders where id = v_sales_order_id; -- Commit transaction COMMIT; SET p_message = 'Sale cancelled successfully'; ELSE -- No sale found or already cancelled SET p_message = 'Sale not found or already cancelled'; END IF; END; CREATE DEFINER=`goldendish_hms`@`%` PROCEDURE `sp_extend_check_in`(IN p_booking_id int, IN p_store_id int, IN p_from_date varchar(255), IN p_to_date varchar(255), IN p_user_id int, IN p_description varchar(255), IN p_space_category varchar(25), IN p_stock_session_id int, OUT v_message varchar(255)) BEGIN DECLARE v_from_date DATE; DECLARE v_to_date DATE; DECLARE v_occupied_from_date DATE; DECLARE v_occupied_to_date DATE; DECLARE v_occupied_name VARCHAR(100); DECLARE v_occupied_phone VARCHAR(25); DECLARE v_product_id INT; DECLARE v_lease_space_id INT; DECLARE v_order_id INT; DECLARE v_sales_amount DECIMAL(15, 2); DECLARE v_additional_amount DECIMAL(15, 2); DECLARE v_sales_id INT; DECLARE v_count INT DEFAULT 0; DECLARE v_session_id INT; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET v_message = 'An unexpected error occurred. Transaction rolled back.'; END; START TRANSACTION; -- Begin the transaction -- Labeled block for flow control LEASE_CHECK: BEGIN -- Convert input strings to DATE SELECT p_from_date, p_to_date INTO v_from_date, v_to_date from dual limit 1; -- Validate booking ID SELECT COUNT(*) INTO v_count FROM lease_bookings WHERE id = p_booking_id; IF v_count = 0 THEN SET v_message = 'No record match for this booking: fail'; ROLLBACK; LEAVE LEASE_CHECK; END IF; -- Check associated orders SELECT COUNT(*) INTO v_count FROM orders WHERE lease_id = p_booking_id; IF v_count = 0 THEN SET v_message = 'No sales order found: fail'; ROLLBACK; LEAVE LEASE_CHECK; END IF; SELECT id INTO v_order_id FROM orders WHERE lease_id = p_booking_id limit 1; -- Check for sales SELECT COUNT(*) INTO v_count FROM sales WHERE order_id = v_order_id; IF v_count = 0 THEN SET v_message = 'No sale found: fail'; ROLLBACK; LEAVE LEASE_CHECK; END IF; SELECT id, total_amount INTO v_sales_id, v_sales_amount FROM sales WHERE order_id = v_order_id limit 1; -- Check lease conflicts SELECT lease_space_id, rate INTO v_lease_space_id, v_additional_amount FROM lease_bookings WHERE id = p_booking_id limit 1; SELECT COUNT(*) INTO v_count FROM leases WHERE leases.lease_space_id = v_lease_space_id AND leases.status IN ('ACTIVE', 'PENDING CANCELLATION') AND (leases.start_date < v_to_date AND leases.end_date > v_from_date); IF v_count > 0 THEN SELECT leases.start_date, leases.end_date, customers.name, customers.phone INTO v_occupied_from_date, v_occupied_to_date, v_occupied_name, v_occupied_phone FROM leases JOIN customers ON customers.id = leases.customer_id WHERE leases.lease_space_id = v_lease_space_id AND leases.status IN ('ACTIVE', 'PENDING CANCELLATION') AND (leases.start_date < v_to_date AND leases.end_date > v_from_date) LIMIT 1; SET v_message = CONCAT( 'Collision| The room has been occupied by ', v_occupied_name, ' ', v_occupied_phone, ' from ', v_occupied_from_date, ' to ', v_occupied_to_date, ': fail' ); ROLLBACK; LEAVE LEASE_CHECK; END IF; -- Handle stock session SELECT COUNT(*) INTO v_count FROM stock_sessions WHERE store_id = p_store_id AND status = 'UNVERIFIED'; IF v_count > 0 THEN SELECT id INTO v_session_id FROM stock_sessions WHERE store_id = p_store_id AND status = 'UNVERIFIED' limit 1; END IF; -- Insert new lease INSERT INTO leases (lease_space_id, customer_id, store_id, booking_id, start_date, end_date, rate, quantity, total_amount_due, notes, created_by, updated_by, created_at, updated_at, status) SELECT lease_space_id, customer_id, store_id, id, v_from_date, v_to_date, rate, 1, rate, p_description, p_user_id, p_user_id, NOW(), NOW(), 'ACTIVE' FROM lease_bookings WHERE id = p_booking_id; -- Determine product ID based on space category SET v_product_id = IF(p_space_category = 'Lease', 4, 3); -- Update order and sales amounts UPDATE orders SET total_amount = v_sales_amount + v_additional_amount WHERE id = v_order_id; UPDATE sales SET total_amount = v_sales_amount + v_additional_amount WHERE id = v_sales_id; -- Insert into order_details INSERT INTO order_details (order_id, product_id, quantity, unit_price, subtotal, created_by, description, discount_rate, created_at, updated_at) VALUES (v_order_id, v_product_id, 1, v_additional_amount, v_additional_amount, p_user_id, p_description, 0, NOW(), NOW()); -- Insert into sale_details INSERT INTO sale_details (sale_id, product_id, store_id, description, quantity, unit_price, subtotal, created_by, updated_by, created_at, updated_at, stock_session_id) VALUES (v_sales_id, v_product_id, p_store_id, p_description, 1, v_additional_amount, v_additional_amount, p_user_id, p_user_id, NOW(), NOW(), v_session_id); -- Update Booking End Date update lease_bookings set end_date = v_to_date, quantity = quantity + 1 where id = p_booking_id; -- Update Lease daily status update lease_space_daily_statuses set is_current = 0 where lease_space_id = v_lease_space_id and status = 'CHECKED IN'; insert into lease_space_daily_statuses (lease_space_id, status_date, status, is_current, created_by, updated_by, created_at, updated_at, rank_id, reference_id, stock_session_id) select v_lease_space_id, v_from_date, 'CHECKED IN', 1, p_user_id, p_user_id, now(), now(), 5, p_booking_id, p_stock_session_id from dual; -- Insert new status SET v_message = 'Success: Lease extended.'; END LEASE_CHECK; COMMIT; -- Commit the transaction if no errors occurred END; CREATE DEFINER=`goldendish_hms`@`%` PROCEDURE `sp_stock_verification`(IN p_store_id int, OUT p_message varchar(255)) begin declare v_session_id int; declare v_count int; declare v_stock_date date; # Check if there is open session select count(*) into v_count from stock_sessions where store_id = p_store_id and status <> 'VERIFIED'; if v_count < 1 then INSERT INTO stock_sessions (store_id, stock_date, reference_no, status, opened_by, created_by, created_at) VALUES (p_store_id, CURDATE(), CONCAT(p_store_id, DATE_FORMAT(CURDATE(), '%d%m%Y')), 'UNVERIFIED', 1, 1, now()); select id, stock_date into v_session_id,v_stock_date from stock_sessions where store_id = p_store_id and status <> 'VERIFIED'; insert into stock_verifications (session_id, product_id, opening_quantity, created_by, created_at) select v_session_id, p.id, ss.quantity, 1, now() from products p join product_categories pc on p.category_id = pc.id left join stock_statuses ss on ss.product_id = p.id and ss.store_id = p_store_id where pc.business_type = 'Products'; commit; end if; set p_message = 'Stock verification already in progress'; select id, stock_date into v_session_id,v_stock_date from stock_sessions where store_id = p_store_id and status <> 'VERIFIED'; # insert new products that are in stock statuses but not in stock verification insert into stock_verifications (session_id, product_id, created_by, created_at) select v_session_id, ss.product_id, 1, now() from stock_statuses ss join products p on p.id = ss.product_id join product_categories pc on pc.id = p.category_id where ss.store_id = p_store_id and pc.business_type = 'Products' and ss.product_id not in (select product_id from stock_verifications sv where sv.session_id = v_session_id); #remove previous stock verification details update stock_verifications SET received_quantity = 0, closing_quantity = 0, sold_quantity = 0, transffered_quantity = 0, spoiled_quantity = 0, updated_at = now() where session_id = v_session_id; # update closing quantity update stock_verifications sv set sv.closing_quantity = (select sum(ss.quantity) from stock_statuses ss where ss.product_id = sv.product_id and ss.store_id = p_store_id) where session_id = v_session_id; # update received quantity update stock_verifications sv set sv.received_quantity = (select sum(ss.quantity) from stock_movements ss where ss.product_id = sv.product_id and activity = 'Stock Movement' and ss.to_stock_session_id = sv.session_id and ss.to_store_id = p_store_id) where session_id = v_session_id; # update transffered quantity update stock_verifications sv set sv.transffered_quantity = (select sum(ss.quantity) from stock_movements ss where ss.product_id = sv.product_id and activity = 'Stock Movement' and ss.stock_session_id = sv.session_id and ss.from_store_id = p_store_id) where session_id = v_session_id; # update sold quantity update stock_verifications sv set sv.sold_quantity = (select ifnull(sum(ss.quantity),0) from sale_details ss where ss.product_id = sv.product_id and ss.stock_session_id = sv.session_id and ss.store_id = p_store_id) where session_id = v_session_id; # update purchase prices update stock_verifications sv set sv.purchase_price = (select ifnull(sum(p.current_purchase_price),0) from products p where p.id = sv.product_id) where session_id = v_session_id; # update sale with stock session id update sales s set s.stock_session_id = v_session_id where s.store_id = p_store_id and s.id in (select sd.sale_id from sale_details sd where sd.sale_id = s.id and sd.stock_session_id = v_session_id); #updated spoiled items update stock_verifications sv set sv.spoiled_quantity = (select ifnull(sum(ss.quantity),0) from spoil_products ss where ss.product_id = sv.product_id and ss.stock_session_id = sv.session_id and ss.store_id = p_store_id) where session_id = v_session_id; set p_message = 'Stock verification completed successfully'; end; CREATE DEFINER=`goldendish_hms`@`%` PROCEDURE `sp_update_lease_status`(IN p_lease_space_id int, IN p_status varchar(50), IN p_user_id int, IN p_from_date date, IN p_to_date date, IN p_rank int, IN p_reference_id int, IN p_stock_session_id int, OUT v_message varchar(255)) BEGIN DECLARE v_current_date DATE; DECLARE v_status_exists INT; -- Initialize the current date SET v_current_date = p_from_date; -- Loop through each date from from_date to to_date WHILE v_current_date < p_to_date DO -- Check if a record for this date and lease_space_id already exists SELECT COUNT(*) INTO v_status_exists FROM lease_space_daily_statuses WHERE lease_space_id = p_lease_space_id AND status = p_status AND status_date = v_current_date; IF v_status_exists > 0 THEN -- If a status already exists, mark the previous as is_current = 0 UPDATE lease_space_daily_statuses SET is_current = '0' WHERE lease_space_id = p_lease_space_id AND status = p_status AND status_date = v_current_date; END IF; -- Insert a new record for the current date INSERT INTO lease_space_daily_statuses (lease_space_id, status_date, status, created_by, updated_by, is_current, reference_id, rank_id, stock_session_id) VALUES (p_lease_space_id, v_current_date, p_status, p_user_id, p_user_id, '1', p_reference_id, p_rank, p_stock_session_id); -- Update the message to indicate progress SET v_message = CONCAT('Processed date: ', v_current_date); -- Move to the next date SET v_current_date = DATE_ADD(v_current_date, INTERVAL 1 DAY); END WHILE; -- Final message indicating completion SET v_message = CONCAT('Completed processing for lease_space_id ', p_lease_space_id); END;