| |  编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'edit_batch_master\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
edit_batch_master |
PROCEDURE |
edit_batch_master |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `bat_id` INT
|
|
BEGIN
SELECT bm.*,
bm.parent_task as p_task,
ts.task_description as ts_desc,
eq.name as st_name,
bm.batch_id as b_id,
bm.stf_id as batch_staff_id,
bm.region_id as batch_region_id,
bm.branch_id as batch_branch_id,
bm.course_category_id as batch_cc_id,
bm.course_id as batch_co_id
FROM `batch_master` as bm
LEFT JOIN task_sheet as ts
ON ts.task_id=bm.parent_task
LEFT JOIN staff as st
ON st.stf_id=ts.stf_id
LEFT JOIN enquiry as eq
ON eq.user_id=st.user_id
WHERE bm.batch_id=bat_id and bm.delflag=0;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT bm.*,
bm.parent_task as p_task,
ts.task_description as ts_desc,
eq.name as st_name,
bm.batch_id as b_id,
bm.stf_id as batch_staff_id,
bm.region_id as batch_region_id,
bm.branch_id as batch_branch_id,
bm.course_category_id as batch_cc_id,
bm.course_id as batch_co_id
FROM `batch_master` as bm
LEFT JOIN task_sheet as ts
ON ts.task_id=bm.parent_task
LEFT JOIN staff as st
ON st.stf_id=ts.stf_id
LEFT JOIN enquiry as eq
ON eq.user_id=st.user_id
WHERE bm.batch_id=bat_id and bm.delflag=0;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'check_kyc_exists\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
check_kyc_exists |
PROCEDURE |
check_kyc_exists |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `enq_id` INT
|
|
BEGIN
SELECT * FROM enquiry e
INNER JOIN add_enquiry_list ae
ON ae.enquiry_mobile=e.mobile_number
WHERE ae.enquiry_id = enq_id;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * FROM enquiry e
INNER JOIN add_enquiry_list ae
ON ae.enquiry_mobile=e.mobile_number
WHERE ae.enquiry_id = enq_id;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'CheckColumnForDelete\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
CheckColumnForDelete |
PROCEDURE |
CheckColumnForDelete |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `tableName` VARCHAR(255), IN `columnName` VARCHAR(255), IN `idToCheck` VARCHAR(255)
|
|
BEGIN
DECLARE lowerName VARCHAR(255);
SET lowerName = TRIM(idToCheck);
SET @sql = CONCAT("SELECT IFNULL(COUNT(*),0) as field_exist FROM ", tableName, " WHERE TRIM(", columnName, ") = '" ,lowerName,"' AND delflag=0;");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
DECLARE lowerName VARCHAR(255);
SET lowerName = TRIM(idToCheck);
SET @sql = CONCAT("SELECT IFNULL(COUNT(*),0) as field_exist FROM ", tableName, " WHERE TRIM(", columnName, ") = '" ,lowerName,"' AND delflag=0;");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'CheckColumnName\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
CheckColumnName |
PROCEDURE |
CheckColumnName |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `tableName` VARCHAR(255), IN `columnName` VARCHAR(255), IN `nameToCheck` VARCHAR(255)
|
|
BEGIN
DECLARE lowerName VARCHAR(255);
SET lowerName = TRIM(LOWER(nameToCheck));
SET @sql = CONCAT("SELECT IFNULL(COUNT(*),0) as field_exist FROM ", tableName, " WHERE TRIM(LOWER(", columnName, ")) = '" ,lowerName,"' AND delflag=0;");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
DECLARE lowerName VARCHAR(255);
SET lowerName = TRIM(LOWER(nameToCheck));
SET @sql = CONCAT("SELECT IFNULL(COUNT(*),0) as field_exist FROM ", tableName, " WHERE TRIM(LOWER(", columnName, ")) = '" ,lowerName,"' AND delflag=0;");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'CheckColumnNameForUpdate\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
CheckColumnNameForUpdate |
PROCEDURE |
CheckColumnNameForUpdate |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `tableName` VARCHAR(255), IN `columnName` VARCHAR(255), IN `nameToCheck` VARCHAR(255), IN `idname` VARCHAR(255), IN `id` VARCHAR(255)
|
|
BEGIN
DECLARE lowerName VARCHAR(255);
SET lowerName = TRIM(LOWER(nameToCheck));
if(id='') then
SET @sql = CONCAT("SELECT IFNULL(COUNT(*),0) as field_exist FROM ", tableName, " WHERE TRIM(LOWER(", columnName, ")) = '" ,lowerName,"' AND delflag=0;");
ELSE
SET @sql = CONCAT("SELECT IFNULL(COUNT(*),0) as field_exist FROM ", tableName, " WHERE TRIM(LOWER(", columnName, ")) = '" ,lowerName,"' AND delflag=0 and ",idname,"!='",id,"'");
end if;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
DECLARE lowerName VARCHAR(255);
SET lowerName = TRIM(LOWER(nameToCheck));
if(id='') then
SET @sql = CONCAT("SELECT IFNULL(COUNT(*),0) as field_exist FROM ", tableName, " WHERE TRIM(LOWER(", columnName, ")) = '" ,lowerName,"' AND delflag=0;");
ELSE
SET @sql = CONCAT("SELECT IFNULL(COUNT(*),0) as field_exist FROM ", tableName, " WHERE TRIM(LOWER(", columnName, ")) = '" ,lowerName,"' AND delflag=0 and ",idname,"!='",id,"'");
end if;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'check_and_load_application_data\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
check_and_load_application_data |
PROCEDURE |
check_and_load_application_data |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `course_id_filter` VARCHAR(50), IN `academic_year_filter` VARCHAR(50), IN `aadhaar_no_filter` VARCHAR(50)
|
|
BEGIN
SELECT * FROM course_application
WHERE
(course_application.apply_for_course_id = course_id_filter)
AND
(course_application.academic_year = academic_year_filter)
AND
(course_application.aadhaar_no = aadhaar_no_filter)
AND
(course_application.delflag = 0)
ORDER BY IFNULL(course_application.update_date,course_application.create_date)
LIMIT 1
;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * FROM course_application
WHERE
(course_application.apply_for_course_id = course_id_filter)
AND
(course_application.academic_year = academic_year_filter)
AND
(course_application.aadhaar_no = aadhaar_no_filter)
AND
(course_application.delflag = 0)
ORDER BY IFNULL(course_application.update_date,course_application.create_date)
LIMIT 1
;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'batch_master\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
batch_master |
PROCEDURE |
batch_master |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
|
|
BEGIN
SELECT *,DATE_FORMAT(bm.start_date, "%d/%m/%Y") AS s_date,DATE_FORMAT(bm.end_date, "%d/%m/%Y") AS e_date FROM `batch_master`as bm
INNER JOIN region_master as rm
ON bm.region_id=rm.region_id
INNER JOIN branch_master as brm
ON bm.branch_id=brm.branch_id
INNER JOIN course_category as cc
ON bm.course_category_id=cc.course_category_id
INNER JOIN course as c
ON bm.course_id=c.course_id
WHERE bm.`delflag`=0 ORDER BY bm.batch_id DESC;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT *,DATE_FORMAT(bm.start_date, "%d/%m/%Y") AS s_date,DATE_FORMAT(bm.end_date, "%d/%m/%Y") AS e_date FROM `batch_master`as bm
INNER JOIN region_master as rm
ON bm.region_id=rm.region_id
INNER JOIN branch_master as brm
ON bm.branch_id=brm.branch_id
INNER JOIN course_category as cc
ON bm.course_category_id=cc.course_category_id
INNER JOIN course as c
ON bm.course_id=c.course_id
WHERE bm.`delflag`=0 ORDER BY bm.batch_id DESC;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'batch_master_with_pagination\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
batch_master_with_pagination |
PROCEDURE |
batch_master_with_pagination |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `p_no` INT
|
|
BEGIN
SELECT *,eq.name as st_name,
(SELECT COUNT(ad.std_id) FROM admission as ad WHERE ad.batch_id=bm.batch_id and ad.delflag=0) as std_count,
DATE_FORMAT(bm.start_date, "%d/%m/%Y") AS s_date,
DATE_FORMAT(bm.end_date, "%d/%m/%Y") AS e_date,
CASE
WHEN bm.batch_status=1 THEN "INACTIVE"
ELSE "ACTIVE"
END AS bt_status,
CASE
WHEN (bm.qrcode_status=0 AND bm.qrcode_status !="") THEN "ACTIVE"
ELSE "INACTIVE"
END AS qr_status
FROM `batch_master`as bm
INNER JOIN region_master as rm
ON bm.region_id=rm.region_id
INNER JOIN branch_master as brm
ON bm.branch_id=brm.branch_id
INNER JOIN course_category as cc
ON bm.course_category_id=cc.course_category_id
INNER JOIN course as c
ON bm.course_id=c.course_id
INNER JOIN staff as st
ON st.stf_id=bm.stf_id
INNER JOIN enquiry as eq
ON eq.user_id=st.user_id
WHERE bm.`delflag`=0 ORDER BY bm.batch_id DESC LIMIT 10 OFFSET P_no;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT *,eq.name as st_name,
(SELECT COUNT(ad.std_id) FROM admission as ad WHERE ad.batch_id=bm.batch_id and ad.delflag=0) as std_count,
DATE_FORMAT(bm.start_date, "%d/%m/%Y") AS s_date,
DATE_FORMAT(bm.end_date, "%d/%m/%Y") AS e_date,
CASE
WHEN bm.batch_status=1 THEN "INACTIVE"
ELSE "ACTIVE"
END AS bt_status,
CASE
WHEN (bm.qrcode_status=0 AND bm.qrcode_status !="") THEN "ACTIVE"
ELSE "INACTIVE"
END AS qr_status
FROM `batch_master`as bm
INNER JOIN region_master as rm
ON bm.region_id=rm.region_id
INNER JOIN branch_master as brm
ON bm.branch_id=brm.branch_id
INNER JOIN course_category as cc
ON bm.course_category_id=cc.course_category_id
INNER JOIN course as c
ON bm.course_id=c.course_id
INNER JOIN staff as st
ON st.stf_id=bm.stf_id
INNER JOIN enquiry as eq
ON eq.user_id=st.user_id
WHERE bm.`delflag`=0 ORDER BY bm.batch_id DESC LIMIT 10 OFFSET P_no;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'edit_staff_name\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
edit_staff_name |
PROCEDURE |
edit_staff_name |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `stf_id` INT
|
|
BEGIN
SELECT * FROM `staff` as st
INNER JOIN enquiry as eq
ON eq.user_id=st.user_id
LEFT JOIN user_type as ut
ON ut.user_type=eq.user_type
WHERE st.stf_id=stf_id and eq.delflag=0 and st.delflag=0;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * FROM `staff` as st
INNER JOIN enquiry as eq
ON eq.user_id=st.user_id
LEFT JOIN user_type as ut
ON ut.user_type=eq.user_type
WHERE st.stf_id=stf_id and eq.delflag=0 and st.delflag=0;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'edit_user_master_details_using_user_table_id\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
edit_user_master_details_using_user_table_id |
PROCEDURE |
edit_user_master_details_using_user_table_id |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `user_table_id_filter` INT
|
|
BEGIN
SELECT * FROM user_master as um
WHERE
(um.user_table_id = user_table_id_filter)
AND
(um.delflag = 0);
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * FROM user_master as um
WHERE
(um.user_table_id = user_table_id_filter)
AND
(um.delflag = 0);
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'fill_details_using_mobile_number\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
fill_details_using_mobile_number |
PROCEDURE |
fill_details_using_mobile_number |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `phone_no` VARCHAR(20)
|
|
BEGIN
SELECT * FROM enquiry as enq
LEFT JOIN student as stu
on enq.user_id = stu.user_id
WHERE (enq.mobile_number = phone_no OR enq.alter_mobile_number = phone_no);
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * FROM enquiry as enq
LEFT JOIN student as stu
on enq.user_id = stu.user_id
WHERE (enq.mobile_number = phone_no OR enq.alter_mobile_number = phone_no);
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'filter_get_detail\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
filter_get_detail |
PROCEDURE |
filter_get_detail |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `service` VARCHAR(50), IN `cc_id` VARCHAR(10), IN `c_id` VARCHAR(10), IN `dt1` VARCHAR(20), IN `dt2` VARCHAR(20), IN `pno` INT, IN `status` VARCHAR(10)
|
|
BEGIN
IF UPPER(service)="ALL" THEN
IF UPPER(status) = "ALL" THEN
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
ELSE
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(est.status) = UPPER(status)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
END IF;
ELSEIF UPPER(service)="TRAINING" THEN
IF UPPER(cc_id) = "ALL" THEN
IF UPPER(status) = "ALL" THEN
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
ELSE
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
AND UPPER(est.status) = UPPER(status)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
END IF;
ELSE
IF UPPER(c_id) = "ALL" THEN
IF UPPER(status) = "ALL" THEN
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
AND ae.course_category_id = cc_id
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
ELSE
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
AND ae.course_category_id = cc_id
AND UPPER(est.status) = UPPER(status)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
END IF;
ELSE
IF UPPER(status) = "ALL" THEN
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
AND ae.course_category_id = cc_id
AND ae.course_id = c_id
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
ELSE
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
AND ae.course_category_id = cc_id
AND ae.course_id = c_id
AND UPPER(est.status) = UPPER(status)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
END IF;
END IF;
END IF;
ELSE
IF UPPER(status) = "ALL" THEN
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER(service)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
ELSE
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER(service)
AND UPPER(est.status) = UPPER(status)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
END IF;
END IF;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
IF UPPER(service)="ALL" THEN
IF UPPER(status) = "ALL" THEN
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
ELSE
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(est.status) = UPPER(status)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
END IF;
ELSEIF UPPER(service)="TRAINING" THEN
IF UPPER(cc_id) = "ALL" THEN
IF UPPER(status) = "ALL" THEN
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
ELSE
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
AND UPPER(est.status) = UPPER(status)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
END IF;
ELSE
IF UPPER(c_id) = "ALL" THEN
IF UPPER(status) = "ALL" THEN
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
AND ae.course_category_id = cc_id
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
ELSE
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
AND ae.course_category_id = cc_id
AND UPPER(est.status) = UPPER(status)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
END IF;
ELSE
IF UPPER(status) = "ALL" THEN
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
AND ae.course_category_id = cc_id
AND ae.course_id = c_id
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
ELSE
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER("TRAINING")
AND ae.course_category_id = cc_id
AND ae.course_id = c_id
AND UPPER(est.status) = UPPER(status)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
END IF;
END IF;
END IF;
ELSE
IF UPPER(status) = "ALL" THEN
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER(service)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
ELSE
SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT max(`next_call_date`) FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ), "%d/%m/%Y") AS follow_date,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count,
CASE ae.upload_type
WHEN 'REGISTER' THEN "app.ayyanargroup.com (REGISTER)"
WHEN 'DEMO' THEN "app.ayyanargroup.com (DEMO)"
ELSE ae.enquiry_received_by
END as received,
CASE ae.upload_type
WHEN 'REGISTER' THEN "TRAINING "
WHEN 'DEMO' THEN "TRAINING "
ELSE ae.enquiry_service
END as service
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id
WHERE ae.delflag=0 AND est.delflag=0 AND est.enquiry_id!=""
AND ae.enquiry_date BETWEEN dt1 AND CONCAT(dt2, ' 23:59:59')
AND UPPER(ae.enquiry_service) = UPPER(service)
AND UPPER(est.status) = UPPER(status)
ORDER BY ae.enquiry_date DESC LIMIT 10 OFFSET pno;
END IF;
END IF;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'filter_get_detail2\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
filter_get_detail2 |
PROCEDURE |
filter_get_detail2 |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `service` VARCHAR(50), IN `cc_id` VARCHAR(10), IN `c_id` VARCHAR(10), IN `reg_id` VARCHAR(10), IN `br_id` VARCHAR(10), IN `dt1` VARCHAR(20), IN `dt2` VARCHAR(20), IN `p_no` INT, IN `status` VARCHAR(10)
|
|
BEGIN
-- DECLARE @queryString VARCHAR(2000) DEFAULT "";
SET @queryString = CONCAT (
'SELECT *,
ae.enquiry_id as enq_id,
DATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date,
DATE_FORMAT((SELECT `next_call_date` FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ORDER BY `next_call_date` DESC LIMIT 1), "%d/%m/%Y") AS follow_date,
DATE_FORMAT((SELECT `next_call_date` FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ORDER BY `next_call_date` DESC LIMIT 1), "%Y%m%d") AS follow_date_sorter,
(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,
(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response,
(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count
FROM `add_enquiry_list` as ae
LEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id '
);
-- IF UPPER(service)="TRAINING" THEN
SET @queryString = CONCAT(
@queryString,
'LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id
LEFT JOIN course as co on ae.course_id = co.course_id '
);
-- END IF;
IF (UPPER(reg_id)!="ALL" AND reg_id != "") OR (UPPER(br_id)!="ALL" AND br_id != "") THEN
SET @queryString = CONCAT(
@queryString,
'LEFT JOIN region_master rg ON rg.region_id = ae.region_id
LEFT JOIN branch_master br ON br.branch_id = ae.branch_id '
);
END IF;
SET @queryString = CONCAT(
@queryString,
'WHERE ae.delflag=0
AND ae.enquiry_date BETWEEN "',dt1,'" AND CONCAT("',dt2,'", " 23:59:59") '
);
IF UPPER(service)!="ALL" AND service != "" THEN
SET @queryString = CONCAT(
@queryString,
' AND UPPER(ae.enquiry_service) = UPPER("',service,'") '
);
END IF;
IF UPPER(reg_id)!="ALL" AND reg_id != "" THEN
SET @queryString = CONCAT(
@queryString,
' AND UPPER(ae.region_id) = UPPER("',reg_id,'") '
);
END IF;
IF UPPER(br_id)!="ALL" AND br_id != "" THEN
SET @queryString = CONCAT(
@queryString,
' AND UPPER(ae.branch_id) = UPPER("',br_id,'") '
);
END IF;
IF UPPER(cc_id) != "ALL" AND cc_id != "" THEN
SET @queryString = CONCAT(
@queryString,
'AND ae.course_category_id = "',cc_id,'" '
);
END IF;
IF UPPER(c_id) != "ALL" AND c_id != "" THEN
SET @queryString = CONCAT(
@queryString,
'AND ae.course_id = "',c_id,'" '
);
END IF;
SET @queryString = CONCAT(
@queryString,
'GROUP BY enq_id '
);
IF UPPER(status) != "ALL" AND status != "" THEN
SET @queryString = CONCAT(
@queryString,
' HAVING UPPER(enq_stat) = UPPER("',status,'") '
);
END IF;
SET @queryString = CONCAT(
@queryString,
' ORDER BY follow_date_sorter,ae.enquiry_date ASC LIMIT 10 OFFSET ',p_no,';'
);
PREPARE stmt FROM @queryString;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- SELECT @queryString;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
-- DECLARE @queryString VARCHAR(2000) DEFAULT "";
SET @queryString = CONCAT (
'SELECT *, \r\nae.enquiry_id as enq_id, \r\nDATE_FORMAT(ae.enquiry_date, "%d/%m/%Y") AS enq_date, \r\nDATE_FORMAT((SELECT `next_call_date` FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ORDER BY `next_call_date` DESC LIMIT 1), "%d/%m/%Y") AS follow_date, \r\nDATE_FORMAT((SELECT `next_call_date` FROM `add_enquiry_status` WHERE `enquiry_id`=ae.enquiry_id ORDER BY `next_call_date` DESC LIMIT 1), "%Y%m%d") AS follow_date_sorter,\r\n(SELECT es.status FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_stat,\r\n(SELECT es.response FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id ORDER BY es.enquiry_status_id DESC LIMIT 1) AS enq_response, \r\n(SELECT COUNT(es.enquiry_id) FROM `add_enquiry_status` as es WHERE es.`enquiry_id`=ae.enquiry_id) AS call_count \r\nFROM `add_enquiry_list` as ae\r\nLEFT JOIN add_enquiry_status as est ON ae.enquiry_id = est.enquiry_id '
);
-- IF UPPER(service)="TRAINING" THEN
SET @queryString = CONCAT(
@queryString,
'LEFT JOIN course_category cc on ae.course_category_id=cc.course_category_id\r\n\t\tLEFT JOIN course as co on ae.course_id = co.course_id '
);
-- END IF;
IF (UPPER(reg_id)!="ALL" AND reg_id != "") OR (UPPER(br_id)!="ALL" AND br_id != "") THEN
SET @queryString = CONCAT(
@queryString,
'LEFT JOIN region_master rg ON rg.region_id = ae.region_id\r\n\t\tLEFT JOIN branch_master br ON br.branch_id = ae.branch_id '
);
END IF;
SET @queryString = CONCAT(
@queryString,
'WHERE ae.delflag=0 \r\n\t\tAND ae.enquiry_date BETWEEN "',dt1,'" AND CONCAT("',dt2,'", " 23:59:59") '
);
IF UPPER(service)!="ALL" AND service != "" THEN
SET @queryString = CONCAT(
@queryString,
' AND UPPER(ae.enquiry_service) = UPPER("',service,'") '
);
END IF;
IF UPPER(reg_id)!="ALL" AND reg_id != "" THEN
SET @queryString = CONCAT(
@queryString,
' AND UPPER(ae.region_id) = UPPER("',reg_id,'") '
);
END IF;
IF UPPER(br_id)!="ALL" AND br_id != "" THEN
SET @queryString = CONCAT(
@queryString,
' AND UPPER(ae.branch_id) = UPPER("',br_id,'") '
);
END IF;
IF UPPER(cc_id) != "ALL" AND cc_id != "" THEN
SET @queryString = CONCAT(
@queryString,
'AND ae.course_category_id = "',cc_id,'" '
);
END IF;
IF UPPER(c_id) != "ALL" AND c_id != "" THEN
SET @queryString = CONCAT(
@queryString,
'AND ae.course_id = "',c_id,'" '
);
END IF;
SET @queryString = CONCAT(
@queryString,
'GROUP BY enq_id '
);
IF UPPER(status) != "ALL" AND status != "" THEN
SET @queryString = CONCAT(
@queryString,
' HAVING UPPER(enq_stat) = UPPER("',status,'") '
);
END IF;
SET @queryString = CONCAT(
@queryString,
' ORDER BY follow_date_sorter,ae.enquiry_date ASC LIMIT 10 OFFSET ',p_no,';'
);
PREPARE stmt FROM @queryString;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- SELECT @queryString;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'filter_panel_batch\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
filter_panel_batch |
PROCEDURE |
filter_panel_batch |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `r_id` VARCHAR(50), IN `b_id` VARCHAR(50), IN `cc_id` VARCHAR(50), IN `c_id` VARCHAR(50), IN `p_no` INT, IN `st_id` VARCHAR(20)
|
|
BEGIN
SELECT *,eq.name as st_name,
(SELECT COUNT(ad.std_id) FROM admission as ad WHERE ad.batch_id=bm.batch_id and ad.delflag=0) as std_count,
(SELECT CONCAT(sum(bm.batch_status=0)," ",sum(bm.batch_status=1),' ',(SELECT sum((SELECT COUNT(ad.std_id) FROM admission as ad WHERE ad.batch_id=bm.batch_id and bm.batch_status=0 and ad.delflag=0))),' ',(SELECT sum((SELECT COUNT(ad.std_id) FROM admission as ad WHERE ad.batch_id=bm.batch_id and bm.batch_status=1 and ad.delflag=0)))) FROM `batch_master` as bm
INNER JOIN branch_master as bi
on bm.branch_id=bi.branch_id
INNER JOIN region_master as ri
on bm.region_id=ri.region_id
INNER JOIN course_category as cc
on bm.course_category_id=cc.course_category_id
INNER JOIN course as co
on bm.course_id=co.course_id
INNER JOIN staff as st
ON st.stf_id=bm.stf_id
INNER JOIN enquiry as eq
ON eq.user_id=st.user_id
WHERE (bm.region_id=r_id OR r_id="ALL")
AND (bm.branch_id=b_id OR b_id-"ALL")
AND (bm.stf_id=st_id OR st_id="ALL")
AND (bm.course_category_id=cc_id OR cc_id="ALL")
AND (bm.course_id=c_id OR c_id="ALL")
AND bm.delflag=0
) as act_and_in_act_batch_count,
DATE_FORMAT(bm.`start_date`,'%d-%m-%Y') as s_date,
DATE_FORMAT(bm.`end_date`,'%d-%m-%Y') as e_date,
CASE
WHEN bm.batch_status=1 THEN "INACTIVE"
ELSE "ACTIVE"
END AS bt_status,
CASE
WHEN (bm.qrcode_status=0 AND bm.qrcode_status !="") THEN "ACTIVE"
ELSE "INACTIVE"
END AS qr_status
FROM `batch_master` as bm
INNER JOIN branch_master as bi
on bm.branch_id=bi.branch_id
INNER JOIN region_master as ri
on bm.region_id=ri.region_id
INNER JOIN course_category as cc
on bm.course_category_id=cc.course_category_id
INNER JOIN course as co
on bm.course_id=co.course_id
INNER JOIN staff as st
ON st.stf_id=bm.stf_id
INNER JOIN enquiry as eq
ON eq.user_id=st.user_id
WHERE (bm.region_id=r_id OR r_id="ALL")
AND (bm.branch_id=b_id OR b_id-"ALL")
AND (bm.stf_id=st_id OR st_id="ALL")
AND (bm.course_category_id=cc_id OR cc_id="ALL")
AND (bm.course_id=c_id OR c_id="ALL")
AND bm.delflag=0 LIMIT 10 OFFSET p_no;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT *,eq.name as st_name,
(SELECT COUNT(ad.std_id) FROM admission as ad WHERE ad.batch_id=bm.batch_id and ad.delflag=0) as std_count,
(SELECT CONCAT(sum(bm.batch_status=0)," ",sum(bm.batch_status=1),' ',(SELECT sum((SELECT COUNT(ad.std_id) FROM admission as ad WHERE ad.batch_id=bm.batch_id and bm.batch_status=0 and ad.delflag=0))),' ',(SELECT sum((SELECT COUNT(ad.std_id) FROM admission as ad WHERE ad.batch_id=bm.batch_id and bm.batch_status=1 and ad.delflag=0)))) FROM `batch_master` as bm
INNER JOIN branch_master as bi
on bm.branch_id=bi.branch_id
INNER JOIN region_master as ri
on bm.region_id=ri.region_id
INNER JOIN course_category as cc
on bm.course_category_id=cc.course_category_id
INNER JOIN course as co
on bm.course_id=co.course_id
INNER JOIN staff as st
ON st.stf_id=bm.stf_id
INNER JOIN enquiry as eq
ON eq.user_id=st.user_id
WHERE (bm.region_id=r_id OR r_id="ALL")
AND (bm.branch_id=b_id OR b_id-"ALL")
AND (bm.stf_id=st_id OR st_id="ALL")
AND (bm.course_category_id=cc_id OR cc_id="ALL")
AND (bm.course_id=c_id OR c_id="ALL")
AND bm.delflag=0
) as act_and_in_act_batch_count,
DATE_FORMAT(bm.`start_date`,'%d-%m-%Y') as s_date,
DATE_FORMAT(bm.`end_date`,'%d-%m-%Y') as e_date,
CASE
WHEN bm.batch_status=1 THEN "INACTIVE"
ELSE "ACTIVE"
END AS bt_status,
CASE
WHEN (bm.qrcode_status=0 AND bm.qrcode_status !="") THEN "ACTIVE"
ELSE "INACTIVE"
END AS qr_status
FROM `batch_master` as bm
INNER JOIN branch_master as bi
on bm.branch_id=bi.branch_id
INNER JOIN region_master as ri
on bm.region_id=ri.region_id
INNER JOIN course_category as cc
on bm.course_category_id=cc.course_category_id
INNER JOIN course as co
on bm.course_id=co.course_id
INNER JOIN staff as st
ON st.stf_id=bm.stf_id
INNER JOIN enquiry as eq
ON eq.user_id=st.user_id
WHERE (bm.region_id=r_id OR r_id="ALL")
AND (bm.branch_id=b_id OR b_id-"ALL")
AND (bm.stf_id=st_id OR st_id="ALL")
AND (bm.course_category_id=cc_id OR cc_id="ALL")
AND (bm.course_id=c_id OR c_id="ALL")
AND bm.delflag=0 LIMIT 10 OFFSET p_no;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'get_all_branch_list_for_region_id\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
get_all_branch_list_for_region_id |
PROCEDURE |
get_all_branch_list_for_region_id |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `reg_id` INT
|
|
BEGIN
SELECT * FROM `branch_master` where region_id = reg_id;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * FROM `branch_master` where region_id = reg_id;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'get_all_branch_list_using_p_no\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
get_all_branch_list_using_p_no |
PROCEDURE |
get_all_branch_list_using_p_no |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `p_no` INT(100)
|
|
BEGIN
SELECT * FROM `branch_master` as bm inner join region_master as rm ON rm.region_id=bm.region_id WHERE
bm.delflag=0
LIMIT 10 OFFSET p_no;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * FROM `branch_master` as bm inner join region_master as rm ON rm.region_id=bm.region_id WHERE
bm.delflag=0
LIMIT 10 OFFSET p_no;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'get_all_contact_details_course_batch\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
get_all_contact_details_course_batch |
PROCEDURE |
get_all_contact_details_course_batch |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `cou_bat` INT
|
|
BEGIN
SELECT * FROM `admission` as ad
INNER JOIN student as st
ON st.std_id=ad.std_id
INNER JOIN enquiry as eq
ON eq.user_id=st.user_id
where ad.`batch_id`=cou_bat and ad.delflag=0
GROUP BY eq.mobile_number
ORDER BY ad.create_date;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * FROM `admission` as ad
INNER JOIN student as st
ON st.std_id=ad.std_id
INNER JOIN enquiry as eq
ON eq.user_id=st.user_id
where ad.`batch_id`=cou_bat and ad.delflag=0
GROUP BY eq.mobile_number
ORDER BY ad.create_date;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'GetParentGroupId\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
GetParentGroupId |
PROCEDURE |
GetParentGroupId |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `parentid` INT
|
|
BEGIN
SELECT parent_acc_grp_id FROM `acc_group_map` WHERE acc_grp_id=parentid;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT parent_acc_grp_id FROM `acc_group_map` WHERE acc_grp_id=parentid;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'get_allowed_attendance_location_of_user\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
get_allowed_attendance_location_of_user |
PROCEDURE |
get_allowed_attendance_location_of_user |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `user_id_filter` VARCHAR(50)
|
|
BEGIN
SELECT * FROM user_attendance_location_assignment as ual
LEFT JOIN attendance_location_master as alm
ON alm.att_loc_id = ual.att_loc_id
WHERE
ual.user_id = user_id_filter
AND
ual.delflag = 0
;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * FROM user_attendance_location_assignment as ual
LEFT JOIN attendance_location_master as alm
ON alm.att_loc_id = ual.att_loc_id
WHERE
ual.user_id = user_id_filter
AND
ual.delflag = 0
;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'get_all_branch_list\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
get_all_branch_list |
PROCEDURE |
get_all_branch_list |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
|
|
BEGIN
SELECT * FROM `branch_master` as bm inner join region_master as rm ON rm.region_id=bm.region_id WHERE
bm.delflag=0;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * FROM `branch_master` as bm inner join region_master as rm ON rm.region_id=bm.region_id WHERE
bm.delflag=0;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'get_all_contact_details_enquiry\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
get_all_contact_details_enquiry |
PROCEDURE |
get_all_contact_details_enquiry |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `cc_id` VARCHAR(20), IN `c_id` VARCHAR(20), IN `reg_id` INT, IN `bran_id` INT, IN `service` VARCHAR(50)
|
|
BEGIN
IF cc_id = "ALL" AND c_id = "ALL" THEN
SELECT
enquiry_name as name,
enquiry_mobile as mobile_number,
enquiry_email as email,
'' as address,
alt_enquiry_mobile as alter_mobile_number
FROM `add_enquiry_list` WHERE `enquiry_mobile` NOT IN (SELECT mobile_number FROM enquiry WHERE user_type=2 OR user_type=1 OR user_type=3 ) and
region_id= reg_id
and branch_id= bran_id
and enquiry_service=service
and delflag=0 group by enquiry_mobile ORDER BY enquiry_name;
END IF;
IF c_id = "ALL" THEN
SELECT
enquiry_name as name,
enquiry_mobile as mobile_number,
enquiry_email as email,
'' as address,
alt_enquiry_mobile as alter_mobile_number
FROM `add_enquiry_list` WHERE `enquiry_mobile` NOT IN (SELECT mobile_number FROM enquiry WHERE user_type=2 OR user_type=1 OR user_type=3 ) and course_category_id= cc_id
and region_id= reg_id
and branch_id= bran_id
and enquiry_service=service
and delflag=0 group by enquiry_mobile ORDER BY enquiry_name;
END IF;
IF cc_id != "ALL" AND c_id != "ALL" THEN
SELECT
enquiry_name as name,
enquiry_mobile as mobile_number,
enquiry_email as email,
'' as address,
alt_enquiry_mobile as alter_mobile_number
FROM `add_enquiry_list` WHERE `enquiry_mobile` NOT IN (SELECT mobile_number FROM enquiry WHERE user_type=2 OR user_type=1 OR user_type=3 ) and course_category_id= cc_id
and course_id= c_id
and region_id= reg_id
and branch_id= bran_id
and enquiry_service=service
and delflag=0 group by enquiry_mobile ORDER BY enquiry_name;
END IF;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
IF cc_id = "ALL" AND c_id = "ALL" THEN
SELECT
enquiry_name as name,
enquiry_mobile as mobile_number,
enquiry_email as email,
'' as address,
alt_enquiry_mobile as alter_mobile_number
FROM `add_enquiry_list` WHERE `enquiry_mobile` NOT IN (SELECT mobile_number FROM enquiry WHERE user_type=2 OR user_type=1 OR user_type=3 ) and
region_id= reg_id
and branch_id= bran_id
and enquiry_service=service
and delflag=0 group by enquiry_mobile ORDER BY enquiry_name;
END IF;
IF c_id = "ALL" THEN
SELECT
enquiry_name as name,
enquiry_mobile as mobile_number,
enquiry_email as email,
'' as address,
alt_enquiry_mobile as alter_mobile_number
FROM `add_enquiry_list` WHERE `enquiry_mobile` NOT IN (SELECT mobile_number FROM enquiry WHERE user_type=2 OR user_type=1 OR user_type=3 ) and course_category_id= cc_id
and region_id= reg_id
and branch_id= bran_id
and enquiry_service=service
and delflag=0 group by enquiry_mobile ORDER BY enquiry_name;
END IF;
IF cc_id != "ALL" AND c_id != "ALL" THEN
SELECT
enquiry_name as name,
enquiry_mobile as mobile_number,
enquiry_email as email,
'' as address,
alt_enquiry_mobile as alter_mobile_number
FROM `add_enquiry_list` WHERE `enquiry_mobile` NOT IN (SELECT mobile_number FROM enquiry WHERE user_type=2 OR user_type=1 OR user_type=3 ) and course_category_id= cc_id
and course_id= c_id
and region_id= reg_id
and branch_id= bran_id
and enquiry_service=service
and delflag=0 group by enquiry_mobile ORDER BY enquiry_name;
END IF;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'get_all_contact_details_kyc\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
get_all_contact_details_kyc |
PROCEDURE |
get_all_contact_details_kyc |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
|
|
BEGIN
SELECT * FROM `enquiry` WHERE mobile_number NOT IN (SELECT enquiry_mobile from add_enquiry_list WHERE delflag=0) and user_type=0 GROUP BY mobile_number ORDER BY name;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * FROM `enquiry` WHERE mobile_number NOT IN (SELECT enquiry_mobile from add_enquiry_list WHERE delflag=0) and user_type=0 GROUP BY mobile_number ORDER BY name;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'get_all_dept_list_for_staff\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
get_all_dept_list_for_staff |
PROCEDURE |
get_all_dept_list_for_staff |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
|
|
BEGIN
SELECT * from dept_master as sm
WHERE
(sm.delflag=0 )
AND
((sm.display_for ='1') OR (sm.display_for = '0'))
AND
(sm.dept_status = 1)
;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * from dept_master as sm
WHERE
(sm.delflag=0 )
AND
((sm.display_for ='1') OR (sm.display_for = '0'))
AND
(sm.dept_status = 1)
;
END
|
| | 编辑 | 复制 | 删除 DELETE FROM `proc` WHERE `proc`.`db` = \'ayyanargroup_com_erp\' AND `proc`.`name` = \'get_all_dept_list_using_p_no\' AND `proc`.`type` = \'PROCEDURE\' | ayyanargroup_com_erp |
get_all_dept_list_using_p_no |
PROCEDURE |
get_all_dept_list_using_p_no |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `p_no` INT
|
|
BEGIN
SELECT * from dept_master as sm
WHERE
(sm.delflag=0 )
AND
((sm.display_for = '0') OR (sm.display_for = '2'))
AND
(sm.dept_status = 1)
;
END
|
root@localhost |
2024-09-26 16:31:13 |
2024-09-26 16:31:13 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
utf8mb4 |
utf8mb4_unicode_ci |
utf8mb4_unicode_520_ci |
BEGIN
SELECT * from dept_master as sm
WHERE
(sm.delflag=0 )
AND
((sm.display_for = '0') OR (sm.display_for = '2'))
AND
(sm.dept_status = 1)
;
END
|