|
|
 Edit
|
Copy
|
Delete
DELETE FROM proc WHERE `proc`.`db` = 'sgm_crm' AND `proc`.`name` = 'mark_item_daily_movement_dirty' AND `proc`.`type` = 'PROCEDURE'
|
sgm_crm |
mark_item_daily_movement_dirty |
PROCEDURE |
mark_item_daily_movement_dirty |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN p_branch_id INT UNSIGNED,
IN p_finance_year_id INT UNSIGNED,
IN p_item_id BIGINT UNSIGNED,
IN p_godown_id INT UNSIGNED,
IN p_item_cat_id INT UNSIGNED,
IN p_earliest_date DATE,
IN p_reason VARCHAR(50)
|
|
BEGIN
/*
Exact branch + exact FY is used by normal transaction/opening-stock events.
These rows must still be marked even when the changed/deleted row was the
final activity for the item, otherwise stale movement rows could survive.
*/
IF p_branch_id > 0 AND p_finance_year_id > 0 THEN
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT
p_branch_id,
p_finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
(
SELECT fy.start_date
FROM financial_years fy
WHERE fy.id = p_finance_year_id
AND fy.start_date >= '1900-01-01'
LIMIT 1
),
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE 1 = 1
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
ELSE
/*
Configuration/wildcard events are expanded only to stock scopes that
actually exist in item_transactions or items_opening_stock.
Do not join financial_years.branch_id here: the live CRM has valid
Branch 1 and Branch 2 stock activity against the shared FY id 13.
*/
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT DISTINCT
activity_scope.branch_id,
activity_scope.finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
fy.start_date,
activity_scope.first_activity_date,
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT
t.branch_id,
t.finance_year_id,
MIN(
CASE
WHEN t.transaction_date >= '1900-01-01'
THEN t.transaction_date
ELSE NULL
END
) AS first_activity_date
FROM item_transactions t
LEFT JOIN items i ON i.id = t.item_id
WHERE t.branch_id > 0
AND t.finance_year_id > 0
AND t.delete_status = 0
AND (p_branch_id = 0 OR t.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR t.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR t.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR t.godown_id = p_godown_id OR t.godown_id = 0)
GROUP BY t.branch_id, t.finance_year_id
UNION
SELECT
os.branch_id,
os.finance_year_id,
NULL AS first_activity_date
FROM items_opening_stock os
LEFT JOIN items i ON i.id = os.item_id
WHERE os.branch_id > 0
AND os.finance_year_id > 0
AND (p_branch_id = 0 OR os.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR os.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR os.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR os.godown_id = p_godown_id OR os.godown_id = 0)
GROUP BY os.branch_id, os.finance_year_id
) AS activity_scope
LEFT JOIN financial_years fy
ON fy.id = activity_scope.finance_year_id
CROSS JOIN (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE activity_scope.branch_id > 0
AND activity_scope.finance_year_id > 0
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
END IF;
END
|
root@localhost |
2026-09-22 18:30:40 |
2026-09-22 18:30:40 |
NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... |
|
utf8mb4 |
utf8mb4_general_ci |
utf8mb4_unicode_ci |
BEGIN
/*
Exact branch + exact FY is used by normal transaction/opening-stock events.
These rows must still be marked even when the changed/deleted row was the
final activity for the item, otherwise stale movement rows could survive.
*/
IF p_branch_id > 0 AND p_finance_year_id > 0 THEN
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT
p_branch_id,
p_finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
(
SELECT fy.start_date
FROM financial_years fy
WHERE fy.id = p_finance_year_id
AND fy.start_date >= '1900-01-01'
LIMIT 1
),
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE 1 = 1
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
ELSE
/*
Configuration/wildcard events are expanded only to stock scopes that
actually exist in item_transactions or items_opening_stock.
Do not join financial_years.branch_id here: the live CRM has valid
Branch 1 and Branch 2 stock activity against the shared FY id 13.
*/
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT DISTINCT
activity_scope.branch_id,
activity_scope.finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
fy.start_date,
activity_scope.first_activity_date,
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT
t.branch_id,
t.finance_year_id,
MIN(
CASE
WHEN t.transaction_date >= '1900-01-01'
THEN t.transaction_date
ELSE NULL
END
) AS first_activity_date
FROM item_transactions t
LEFT JOIN items i ON i.id = t.item_id
WHERE t.branch_id > 0
AND t.finance_year_id > 0
AND t.delete_status = 0
AND (p_branch_id = 0 OR t.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR t.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR t.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR t.godown_id = p_godown_id OR t.godown_id = 0)
GROUP BY t.branch_id, t.finance_year_id
UNION
SELECT
os.branch_id,
os.finance_year_id,
NULL AS first_activity_date
FROM items_opening_stock os
LEFT JOIN items i ON i.id = os.item_id
WHERE os.branch_id > 0
AND os.finance_year_id > 0
AND (p_branch_id = 0 OR os.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR os.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR os.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR os.godown_id = p_godown_id OR os.godown_id = 0)
GROUP BY os.branch_id, os.finance_year_id
) AS activity_scope
LEFT JOIN financial_years fy
ON fy.id = activity_scope.finance_year_id
CROSS JOIN (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE activity_scope.branch_id > 0
AND activity_scope.finance_year_id > 0
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
END IF;
END
|
NONE |
|
|
Edit
|
Copy
|
Delete
DELETE FROM proc WHERE `proc`.`db` = 'surena_crm' AND `proc`.`name` = 'mark_item_daily_movement_dirty' AND `proc`.`type` = 'PROCEDURE'
|
surena_crm |
mark_item_daily_movement_dirty |
PROCEDURE |
mark_item_daily_movement_dirty |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN p_branch_id INT UNSIGNED,
IN p_finance_year_id INT UNSIGNED,
IN p_item_id BIGINT UNSIGNED,
IN p_godown_id INT UNSIGNED,
IN p_item_cat_id INT UNSIGNED,
IN p_earliest_date DATE,
IN p_reason VARCHAR(50)
|
|
BEGIN
/*
Exact branch + exact FY is used by normal transaction/opening-stock events.
These rows must still be marked even when the changed/deleted row was the
final activity for the item, otherwise stale movement rows could survive.
*/
IF p_branch_id > 0 AND p_finance_year_id > 0 THEN
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT
p_branch_id,
p_finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
(
SELECT fy.start_date
FROM financial_years fy
WHERE fy.id = p_finance_year_id
AND fy.start_date >= '1900-01-01'
LIMIT 1
),
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE 1 = 1
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
ELSE
/*
Configuration/wildcard events are expanded only to stock scopes that
actually exist in item_transactions or items_opening_stock.
Do not join financial_years.branch_id here: the live CRM has valid
Branch 1 and Branch 2 stock activity against the shared FY id 13.
*/
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT DISTINCT
activity_scope.branch_id,
activity_scope.finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
fy.start_date,
activity_scope.first_activity_date,
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT
t.branch_id,
t.finance_year_id,
MIN(
CASE
WHEN t.transaction_date >= '1900-01-01'
THEN t.transaction_date
ELSE NULL
END
) AS first_activity_date
FROM item_transactions t
LEFT JOIN items i ON i.id = t.item_id
WHERE t.branch_id > 0
AND t.finance_year_id > 0
AND t.delete_status = 0
AND (p_branch_id = 0 OR t.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR t.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR t.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR t.godown_id = p_godown_id OR t.godown_id = 0)
GROUP BY t.branch_id, t.finance_year_id
UNION
SELECT
os.branch_id,
os.finance_year_id,
NULL AS first_activity_date
FROM items_opening_stock os
LEFT JOIN items i ON i.id = os.item_id
WHERE os.branch_id > 0
AND os.finance_year_id > 0
AND (p_branch_id = 0 OR os.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR os.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR os.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR os.godown_id = p_godown_id OR os.godown_id = 0)
GROUP BY os.branch_id, os.finance_year_id
) AS activity_scope
LEFT JOIN financial_years fy
ON fy.id = activity_scope.finance_year_id
CROSS JOIN (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE activity_scope.branch_id > 0
AND activity_scope.finance_year_id > 0
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
END IF;
END
|
root@localhost |
2026-09-22 18:30:01 |
2026-09-22 18:30:01 |
NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... |
|
utf8mb4 |
utf8mb4_general_ci |
utf8mb4_unicode_ci |
BEGIN
/*
Exact branch + exact FY is used by normal transaction/opening-stock events.
These rows must still be marked even when the changed/deleted row was the
final activity for the item, otherwise stale movement rows could survive.
*/
IF p_branch_id > 0 AND p_finance_year_id > 0 THEN
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT
p_branch_id,
p_finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
(
SELECT fy.start_date
FROM financial_years fy
WHERE fy.id = p_finance_year_id
AND fy.start_date >= '1900-01-01'
LIMIT 1
),
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE 1 = 1
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
ELSE
/*
Configuration/wildcard events are expanded only to stock scopes that
actually exist in item_transactions or items_opening_stock.
Do not join financial_years.branch_id here: the live CRM has valid
Branch 1 and Branch 2 stock activity against the shared FY id 13.
*/
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT DISTINCT
activity_scope.branch_id,
activity_scope.finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
fy.start_date,
activity_scope.first_activity_date,
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT
t.branch_id,
t.finance_year_id,
MIN(
CASE
WHEN t.transaction_date >= '1900-01-01'
THEN t.transaction_date
ELSE NULL
END
) AS first_activity_date
FROM item_transactions t
LEFT JOIN items i ON i.id = t.item_id
WHERE t.branch_id > 0
AND t.finance_year_id > 0
AND t.delete_status = 0
AND (p_branch_id = 0 OR t.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR t.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR t.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR t.godown_id = p_godown_id OR t.godown_id = 0)
GROUP BY t.branch_id, t.finance_year_id
UNION
SELECT
os.branch_id,
os.finance_year_id,
NULL AS first_activity_date
FROM items_opening_stock os
LEFT JOIN items i ON i.id = os.item_id
WHERE os.branch_id > 0
AND os.finance_year_id > 0
AND (p_branch_id = 0 OR os.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR os.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR os.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR os.godown_id = p_godown_id OR os.godown_id = 0)
GROUP BY os.branch_id, os.finance_year_id
) AS activity_scope
LEFT JOIN financial_years fy
ON fy.id = activity_scope.finance_year_id
CROSS JOIN (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE activity_scope.branch_id > 0
AND activity_scope.finance_year_id > 0
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
END IF;
END
|
NONE |
|
|
Edit
|
Copy
|
Delete
DELETE FROM proc WHERE `proc`.`db` = 'rajkamal_crm' AND `proc`.`name` = 'mark_item_daily_movement_dirty' AND `proc`.`type` = 'PROCEDURE'
|
rajkamal_crm |
mark_item_daily_movement_dirty |
PROCEDURE |
mark_item_daily_movement_dirty |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN p_branch_id INT UNSIGNED,
IN p_finance_year_id INT UNSIGNED,
IN p_item_id BIGINT UNSIGNED,
IN p_godown_id INT UNSIGNED,
IN p_item_cat_id INT UNSIGNED,
IN p_earliest_date DATE,
IN p_reason VARCHAR(50)
|
|
BEGIN
/*
Exact branch + exact FY is used by normal transaction/opening-stock events.
These rows must still be marked even when the changed/deleted row was the
final activity for the item, otherwise stale movement rows could survive.
*/
IF p_branch_id > 0 AND p_finance_year_id > 0 THEN
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT
p_branch_id,
p_finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
(
SELECT fy.start_date
FROM financial_years fy
WHERE fy.id = p_finance_year_id
AND fy.start_date >= '1900-01-01'
LIMIT 1
),
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE 1 = 1
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
ELSE
/*
Configuration/wildcard events are expanded only to stock scopes that
actually exist in item_transactions or items_opening_stock.
Do not join financial_years.branch_id here: the live CRM has valid
Branch 1 and Branch 2 stock activity against the shared FY id 13.
*/
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT DISTINCT
activity_scope.branch_id,
activity_scope.finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
fy.start_date,
activity_scope.first_activity_date,
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT
t.branch_id,
t.finance_year_id,
MIN(
CASE
WHEN t.transaction_date >= '1900-01-01'
THEN t.transaction_date
ELSE NULL
END
) AS first_activity_date
FROM item_transactions t
LEFT JOIN items i ON i.id = t.item_id
WHERE t.branch_id > 0
AND t.finance_year_id > 0
AND t.delete_status = 0
AND (p_branch_id = 0 OR t.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR t.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR t.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR t.godown_id = p_godown_id OR t.godown_id = 0)
GROUP BY t.branch_id, t.finance_year_id
UNION
SELECT
os.branch_id,
os.finance_year_id,
NULL AS first_activity_date
FROM items_opening_stock os
LEFT JOIN items i ON i.id = os.item_id
WHERE os.branch_id > 0
AND os.finance_year_id > 0
AND (p_branch_id = 0 OR os.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR os.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR os.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR os.godown_id = p_godown_id OR os.godown_id = 0)
GROUP BY os.branch_id, os.finance_year_id
) AS activity_scope
LEFT JOIN financial_years fy
ON fy.id = activity_scope.finance_year_id
CROSS JOIN (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE activity_scope.branch_id > 0
AND activity_scope.finance_year_id > 0
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
END IF;
END
|
root@localhost |
2026-09-22 18:30:24 |
2026-09-22 18:30:24 |
NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... |
|
utf8mb4 |
utf8mb4_general_ci |
utf8mb4_unicode_ci |
BEGIN
/*
Exact branch + exact FY is used by normal transaction/opening-stock events.
These rows must still be marked even when the changed/deleted row was the
final activity for the item, otherwise stale movement rows could survive.
*/
IF p_branch_id > 0 AND p_finance_year_id > 0 THEN
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT
p_branch_id,
p_finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
(
SELECT fy.start_date
FROM financial_years fy
WHERE fy.id = p_finance_year_id
AND fy.start_date >= '1900-01-01'
LIMIT 1
),
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE 1 = 1
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
ELSE
/*
Configuration/wildcard events are expanded only to stock scopes that
actually exist in item_transactions or items_opening_stock.
Do not join financial_years.branch_id here: the live CRM has valid
Branch 1 and Branch 2 stock activity against the shared FY id 13.
*/
INSERT INTO item_daily_movement_dirty (
branch_id, finance_year_id, item_id, godown_id, item_cat_id,
valuation_method_used, earliest_affected_date, reason, marked_at
)
SELECT DISTINCT
activity_scope.branch_id,
activity_scope.finance_year_id,
CASE WHEN p_item_id > 0 THEN p_item_id ELSE 0 END,
p_godown_id,
CASE WHEN p_item_id = 0 AND p_item_cat_id > 0 THEN p_item_cat_id ELSE 0 END,
valuation_scope.valuation_method_used,
COALESCE(
CASE
WHEN p_earliest_date IS NOT NULL
AND p_earliest_date >= '1900-01-01'
THEN p_earliest_date
ELSE NULL
END,
fy.start_date,
activity_scope.first_activity_date,
CURRENT_DATE
) AS earliest_affected_date,
p_reason,
NOW()
FROM (
SELECT
t.branch_id,
t.finance_year_id,
MIN(
CASE
WHEN t.transaction_date >= '1900-01-01'
THEN t.transaction_date
ELSE NULL
END
) AS first_activity_date
FROM item_transactions t
LEFT JOIN items i ON i.id = t.item_id
WHERE t.branch_id > 0
AND t.finance_year_id > 0
AND t.delete_status = 0
AND (p_branch_id = 0 OR t.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR t.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR t.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR t.godown_id = p_godown_id OR t.godown_id = 0)
GROUP BY t.branch_id, t.finance_year_id
UNION
SELECT
os.branch_id,
os.finance_year_id,
NULL AS first_activity_date
FROM items_opening_stock os
LEFT JOIN items i ON i.id = os.item_id
WHERE os.branch_id > 0
AND os.finance_year_id > 0
AND (p_branch_id = 0 OR os.branch_id = p_branch_id)
AND (p_finance_year_id = 0 OR os.finance_year_id = p_finance_year_id)
AND (p_item_id = 0 OR os.item_id = p_item_id)
AND (p_item_cat_id = 0 OR i.item_cat_id = p_item_cat_id)
AND (p_godown_id = 0 OR os.godown_id = p_godown_id OR os.godown_id = 0)
GROUP BY os.branch_id, os.finance_year_id
) AS activity_scope
LEFT JOIN financial_years fy
ON fy.id = activity_scope.finance_year_id
CROSS JOIN (
SELECT 'DEFAULT' AS valuation_method_used
UNION ALL SELECT 'FIFO'
UNION ALL SELECT 'LIFO'
UNION ALL SELECT 'WAC'
UNION ALL SELECT 'SIMPLE_AVG'
UNION ALL SELECT 'STANDARD'
UNION ALL SELECT 'SPECIFIC'
UNION ALL SELECT 'RETAIL'
UNION ALL SELECT 'LOWER_NRV'
) AS valuation_scope
WHERE activity_scope.branch_id > 0
AND activity_scope.finance_year_id > 0
ON DUPLICATE KEY UPDATE
earliest_affected_date = LEAST(earliest_affected_date, VALUES(earliest_affected_date)),
reason = VALUES(reason),
marked_at = VALUES(marked_at);
END IF;
END
|
NONE |
|
|
Edit
|
Copy
|
Delete
DELETE FROM proc WHERE `proc`.`db` = 'surena_crm' AND `proc`.`name` = 'camcom_add' AND `proc`.`type` = 'PROCEDURE'
|
surena_crm |
camcom_add |
PROCEDURE |
camcom_add |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN p_location_id INT, IN p_dvr_name VARCHAR(200), IN p_model VARCHAR(100),
IN p_serial VARCHAR(100), IN p_dvr_ip VARCHAR(200), IN p_dvr_port VARCHAR(10),
IN p_dvr_user VARCHAR(100), IN p_dvr_password VARCHAR(50),
IN p_analog_channels INT, IN p_ip_channels INT, IN p_motion_enabled INT,
IN p_http_port INT, IN p_rtsp_port INT, IN p_start_time TIME,
IN p_end_time TIME, IN p_custom_rtsp VARCHAR(500), IN p_created_by INT
|
|
BEGIN
DECLARE v_rtsp_url VARCHAR(500);
IF UPPER(p_model) = 'DAHUA' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/cam/realmonitor?channel=<CameraNo>&subtype=0');
ELSEIF UPPER(p_model) = 'HIKVISION' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/Streaming/Channels/<CameraNo>01');
ELSE
SET v_rtsp_url = p_custom_rtsp;
END IF;
INSERT INTO camcom (
location_id, dvr_name, model, serial, dvr_ip, dvr_port,
dvr_usr, dvr_pwd, dvr_agchannels, dvr_ipchannels,
motion_enabled, httpport, rtspport, stime, etime, rtsp_URL, created_by_user_id
) VALUES (
p_location_id, p_dvr_name, p_model, p_serial, p_dvr_ip, p_dvr_port,
p_dvr_user, p_dvr_password, p_analog_channels, p_ip_channels,
p_motion_enabled, p_http_port, p_rtsp_port, p_start_time, p_end_time,
v_rtsp_url, p_created_by
);
END
|
root@localhost |
2026-09-23 20:32:58 |
2026-09-23 20:32:58 |
NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... |
|
utf8mb4 |
utf8mb4_general_ci |
utf8mb4_unicode_ci |
BEGIN
DECLARE v_rtsp_url VARCHAR(500);
IF UPPER(p_model) = 'DAHUA' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/cam/realmonitor?channel=<CameraNo>&subtype=0');
ELSEIF UPPER(p_model) = 'HIKVISION' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/Streaming/Channels/<CameraNo>01');
ELSE
SET v_rtsp_url = p_custom_rtsp;
END IF;
INSERT INTO camcom (
location_id, dvr_name, model, serial, dvr_ip, dvr_port,
dvr_usr, dvr_pwd, dvr_agchannels, dvr_ipchannels,
motion_enabled, httpport, rtspport, stime, etime, rtsp_URL, created_by_user_id
) VALUES (
p_location_id, p_dvr_name, p_model, p_serial, p_dvr_ip, p_dvr_port,
p_dvr_user, p_dvr_password, p_analog_channels, p_ip_channels,
p_motion_enabled, p_http_port, p_rtsp_port, p_start_time, p_end_time,
v_rtsp_url, p_created_by
);
END
|
NONE |
|
|
Edit
|
Copy
|
Delete
DELETE FROM proc WHERE `proc`.`db` = 'rajkamal_crm' AND `proc`.`name` = 'camcom_add' AND `proc`.`type` = 'PROCEDURE'
|
rajkamal_crm |
camcom_add |
PROCEDURE |
camcom_add |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN p_location_id INT, IN p_dvr_name VARCHAR(200), IN p_model VARCHAR(100),
IN p_serial VARCHAR(100), IN p_dvr_ip VARCHAR(200), IN p_dvr_port VARCHAR(10),
IN p_dvr_user VARCHAR(100), IN p_dvr_password VARCHAR(50),
IN p_analog_channels INT, IN p_ip_channels INT, IN p_motion_enabled INT,
IN p_http_port INT, IN p_rtsp_port INT, IN p_start_time TIME,
IN p_end_time TIME, IN p_custom_rtsp VARCHAR(500), IN p_created_by INT
|
|
BEGIN
DECLARE v_rtsp_url VARCHAR(500);
IF UPPER(p_model) = 'DAHUA' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/cam/realmonitor?channel=<CameraNo>&subtype=0');
ELSEIF UPPER(p_model) = 'HIKVISION' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/Streaming/Channels/<CameraNo>01');
ELSE
SET v_rtsp_url = p_custom_rtsp;
END IF;
INSERT INTO camcom (
location_id, dvr_name, model, serial, dvr_ip, dvr_port,
dvr_usr, dvr_pwd, dvr_agchannels, dvr_ipchannels,
motion_enabled, httpport, rtspport, stime, etime, rtsp_URL, created_by_user_id
) VALUES (
p_location_id, p_dvr_name, p_model, p_serial, p_dvr_ip, p_dvr_port,
p_dvr_user, p_dvr_password, p_analog_channels, p_ip_channels,
p_motion_enabled, p_http_port, p_rtsp_port, p_start_time, p_end_time,
v_rtsp_url, p_created_by
);
END
|
root@localhost |
2026-09-23 20:32:58 |
2026-09-23 20:32:58 |
NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... |
|
utf8mb4 |
utf8mb4_general_ci |
utf8mb4_unicode_ci |
BEGIN
DECLARE v_rtsp_url VARCHAR(500);
IF UPPER(p_model) = 'DAHUA' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/cam/realmonitor?channel=<CameraNo>&subtype=0');
ELSEIF UPPER(p_model) = 'HIKVISION' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/Streaming/Channels/<CameraNo>01');
ELSE
SET v_rtsp_url = p_custom_rtsp;
END IF;
INSERT INTO camcom (
location_id, dvr_name, model, serial, dvr_ip, dvr_port,
dvr_usr, dvr_pwd, dvr_agchannels, dvr_ipchannels,
motion_enabled, httpport, rtspport, stime, etime, rtsp_URL, created_by_user_id
) VALUES (
p_location_id, p_dvr_name, p_model, p_serial, p_dvr_ip, p_dvr_port,
p_dvr_user, p_dvr_password, p_analog_channels, p_ip_channels,
p_motion_enabled, p_http_port, p_rtsp_port, p_start_time, p_end_time,
v_rtsp_url, p_created_by
);
END
|
NONE |
|
|
Edit
|
Copy
|
Delete
DELETE FROM proc WHERE `proc`.`db` = 'sgm_crm' AND `proc`.`name` = 'camcom_add' AND `proc`.`type` = 'PROCEDURE'
|
sgm_crm |
camcom_add |
PROCEDURE |
camcom_add |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN p_location_id INT, IN p_dvr_name VARCHAR(200), IN p_model VARCHAR(100),
IN p_serial VARCHAR(100), IN p_dvr_ip VARCHAR(200), IN p_dvr_port VARCHAR(10),
IN p_dvr_user VARCHAR(100), IN p_dvr_password VARCHAR(50),
IN p_analog_channels INT, IN p_ip_channels INT, IN p_motion_enabled INT,
IN p_http_port INT, IN p_rtsp_port INT, IN p_start_time TIME,
IN p_end_time TIME, IN p_custom_rtsp VARCHAR(500), IN p_created_by INT
|
|
BEGIN
DECLARE v_rtsp_url VARCHAR(500);
IF UPPER(p_model) = 'DAHUA' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/cam/realmonitor?channel=<CameraNo>&subtype=0');
ELSEIF UPPER(p_model) = 'HIKVISION' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/Streaming/Channels/<CameraNo>01');
ELSE
SET v_rtsp_url = p_custom_rtsp;
END IF;
INSERT INTO camcom (
location_id, dvr_name, model, serial, dvr_ip, dvr_port,
dvr_usr, dvr_pwd, dvr_agchannels, dvr_ipchannels,
motion_enabled, httpport, rtspport, stime, etime, rtsp_URL, created_by_user_id
) VALUES (
p_location_id, p_dvr_name, p_model, p_serial, p_dvr_ip, p_dvr_port,
p_dvr_user, p_dvr_password, p_analog_channels, p_ip_channels,
p_motion_enabled, p_http_port, p_rtsp_port, p_start_time, p_end_time,
v_rtsp_url, p_created_by
);
END
|
root@localhost |
2026-09-23 20:32:58 |
2026-09-23 20:32:58 |
NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... |
|
utf8mb4 |
utf8mb4_general_ci |
utf8mb4_unicode_ci |
BEGIN
DECLARE v_rtsp_url VARCHAR(500);
IF UPPER(p_model) = 'DAHUA' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/cam/realmonitor?channel=<CameraNo>&subtype=0');
ELSEIF UPPER(p_model) = 'HIKVISION' THEN
SET v_rtsp_url = CONCAT('rtsp://', p_dvr_user, ':', p_dvr_password, '@', p_dvr_ip, ':', p_rtsp_port, '/Streaming/Channels/<CameraNo>01');
ELSE
SET v_rtsp_url = p_custom_rtsp;
END IF;
INSERT INTO camcom (
location_id, dvr_name, model, serial, dvr_ip, dvr_port,
dvr_usr, dvr_pwd, dvr_agchannels, dvr_ipchannels,
motion_enabled, httpport, rtspport, stime, etime, rtsp_URL, created_by_user_id
) VALUES (
p_location_id, p_dvr_name, p_model, p_serial, p_dvr_ip, p_dvr_port,
p_dvr_user, p_dvr_password, p_analog_channels, p_ip_channels,
p_motion_enabled, p_http_port, p_rtsp_port, p_start_time, p_end_time,
v_rtsp_url, p_created_by
);
END
|
NONE |
