DELIMITER $$
CREATE PROCEDURE `CarryOverReport`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8, IN `users_id` VARCHAR(500) CHARSET utf8)
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'SELECT salesman_unloads.id,salesman_unloads.trip_id,salesman_unloads.transaction_date ,users.firstname, users.lastname,salesman_infos.user_id,salesman_infos.salesman_code,routes.route_code,routes.route_name,items.id as item_id,items.item_code,items.item_name,salesman_unload_details.unload_qty,salesman_unloads.code, item_uoms.name as item_uom_name, item_uoms.code as item_uoms_code,item_uoms.id as item_uoms_id  from salesman_unloads LEFT JOIN salesman_unload_details on salesman_unload_details.salesman_unload_id = salesman_unloads.id LEFT JOIN items ON items.id = salesman_unload_details.item_id LEFT JOIN item_uoms ON item_uoms.id = salesman_unload_details.item_uom LEFT JOIN users on users.id = salesman_unloads.salesman_id LEFT JOIN salesman_infos on salesman_infos.user_id = salesman_unloads.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id LEFT JOIN routes on routes.id = salesman_unloads.route_id where users.deleted_at is null and salesman_unloads.organisation_id = org_ids ';

SET SQLText = REPLACE (SQLText,'org_ids',org_id);

IF users_id != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(users.id, "useridss")'; 
SET otherreplace = REPLACE (otherreplace,'useridss',users_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate IS NOT NULL AND edate IS NOT NULL) THEN
SET otherreplace = ' AND salesman_unloads.transaction_date  between "sdatess" and "edatess" ';
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(salesman_unloads.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' GROUP BY salesman_unloads.trip_id';
SET SQLText = CONCAT(SQLText,otherreplace);


SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `CallStrikePlanned`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'select COUNT(customer_visits.id) as visitshop from customer_visits left join customer_infos on customer_infos.user_id = customer_visits.customer_id  where customer_visits.is_sequnece = "1" AND (customer_visits.shop_status = "open" ';

IF org_id > 0 THEN
	SET otherreplace = ' AND customer_visits.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate IS NOT NULL AND edate IS NOT NULL) THEN
SET otherreplace = ' AND customer_visits.created_at between "sdatess" and "edatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sales_id != '' THEN
SET otherreplace = ' AND customer_infos.user_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = '))';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `CarryOverReportDetails`(IN `unload_id` INT(11) UNSIGNED)
    NO SQL
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'SELECT items.id as item_id,items.item_code,items.item_name,salesman_unload_details.unload_qty, item_uoms.name as item_uom_name, item_uoms.code as item_uoms_code,item_uoms.id as item_uoms_id from salesman_unload_details LEFT JOIN items ON items.id = salesman_unload_details.item_id LEFT JOIN item_uoms ON item_uoms.id = salesman_unload_details.item_uom';

IF unload_id > 0 THEN
SET otherreplace = ' WHERE salesman_unload_details.salesman_unload_id = unload_idss ';
SET otherreplace = REPLACE (otherreplace,'unload_idss',unload_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `DailyFieldActivityReport`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8, IN `start_limit` INT(11) UNSIGNED, IN `end_limit` INT(11) UNSIGNED)
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'select salesman_infos.user_id,salesman_infos.id, salesman_infos.created_at, salesman_infos.user_id, salesman_infos.salesman_code,users.firstname,users.lastname FROM salesman_infos 
left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id
LEFT JOIN users on users.id = salesman_infos.user_id where salesman_infos.deleted_at is null  ';


IF org_id > 0 THEN
SET otherreplace = ' AND salesman_infos.organisation_id = orgidss';
SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(salesman_infos.user_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' GROUP BY salesman_infos.user_id ORDER BY salesman_infos.created_at';
SET SQLText = CONCAT(SQLText,otherreplace);

IF start_limit > 0 AND end_limit <= 0 THEN
SET otherreplace = ' LIMIT end_limits , start_limits';
SET otherreplace = REPLACE (otherreplace,'start_limits',start_limit);
SET otherreplace = REPLACE (otherreplace,'end_limits',end_limit);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;




END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `GetTotalReturnCreditNote`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText ='select credit_notes.id as credit_notes_id from credit_notes left join salesman_infos on salesman_infos.user_id = credit_notes.salesman_id  WHERE credit_notes.credit_note_date between "sdatess" and "edatess" ';


IF sdate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'sdatess',sdate);
END IF;

IF edate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'edatess',edate);
END IF;

IF org_id > 0 THEN
	SET otherreplace = ' AND credit_notes.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sales_id != '' THEN
SET otherreplace = ' AND salesman_infos.user_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `GetTotalReturnCreditNoteDetails`(IN `credit_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText ='select SUM(credit_note_details.item_qty) as Total_return_qty from credit_note_details left join items on items.id = credit_note_details.item_id ';

IF credit_id != '' THEN
SET otherreplace = ' WHERE credit_note_details.credit_note_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,credit_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `CallStrikeVisited`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'select COUNT(customer_visits.id) as visitshop from customer_visits left join customer_infos on customer_infos.user_id = customer_visits.customer_id  where (customer_visits.is_sequnece = "1" OR customer_visits.is_sequnece = "0") AND (customer_visits.shop_status = "open" ';

IF org_id > 0 THEN
	SET otherreplace = ' AND customer_visits.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate IS NOT NULL AND edate IS NOT NULL) THEN
SET otherreplace = ' AND customer_visits.created_at between "sdatess" and "edatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sales_id != '' THEN
SET otherreplace = ' AND customer_infos.user_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = '))';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `CustomerSalesPerMonthReport`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `cha_id` VARCHAR(500) CHARSET utf8, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8, IN `cust_id` INT(11) UNSIGNED)
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'select customer_infos.id, customer_infos.user_id as customer_user_id, customer_infos.customer_code, users.firstname, users.lastname, users.email, users.mobile, customer_types.customer_type_name as customer_type, customer_groups.group_name as customer_group, invoices.invoice_date, salesman_infos.salesman_supervisor, salesman_infos.user_id, customer_infos.region_id, customer_routes.route_id, customer_lobs.lob_id FROM customer_infos left join users on customer_infos.user_id = users.id left join customer_types on customer_infos.customer_type_id = customer_types.id left join customer_groups on customer_infos.customer_group_id = customer_groups.id left join invoices on customer_infos.user_id = invoices.customer_id left join salesman_infos on invoices.salesman_id = salesman_infos.user_id left join customer_lobs on customer_infos.id = customer_lobs.customer_info_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id left join customer_routes on customer_infos.id = customer_routes.customer_id WHERE customer_infos.organisation_id = org_ids and date(invoices.invoice_date) between "sdatess" and "edatess"  ';

IF sdate IS NOT NULL THEN
	SET SQLText = REPLACE (SQLText,'sdatess',sdate);
END IF;

IF edate IS NOT NULL THEN
	SET SQLText = REPLACE (SQLText,'edatess',edate);
END IF;

IF org_id > 0 THEN
	SET SQLText = REPLACE (SQLText,'org_ids',org_id);
END IF;

IF cha_id != '' THEN
	SET otherreplace = ' AND FIND_IN_SET(customer_infos.channel_id, "chaidss")'; 
	SET otherreplace = REPLACE (otherreplace,'chaidss',cha_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;


IF cust_id > 0 THEN
	SET otherreplace = ' AND customer_infos.id = cust_ids';
	SET otherreplace = REPLACE (otherreplace,'cust_ids',cust_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF all_ids != '' THEN
SET otherreplace = '  AND FIND_IN_SET(invoices.salesman_id, "allthis_ids")';
SET otherreplace = REPLACE (otherreplace,'allthis_ids',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(customer_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(customer_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(customer_routes.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' group by customer_infos.id, users.firstname, users.lastname, users.email, users.mobile, customer_type, customer_group ';
SET SQLText = CONCAT(SQLText,otherreplace); 

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `GetTotalSalesInvoicesPerDate`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText ='select invoices.id as invoices_id FROM invoices left join salesman_infos on salesman_infos.user_id = invoices.salesman_id  ';

IF sdate IS NOT NULL THEN
SET otherreplace = ' WHERE invoices.invoice_date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF org_id > 0 THEN
	SET otherreplace = ' AND invoices.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sales_id != '' THEN
SET otherreplace = ' AND salesman_infos.user_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `MonthlyAgeingReport`(IN `sdate` DATE, IN `edate` DATE, IN `cust_id` INT(11) UNSIGNED, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
BEGIN

DECLARE SQLText varchar(8000);

DECLARE otherreplace varchar(500);


SET SQLText ='select customer_infos.customer_code, concat(users.firstname," ",users.lastname) AS customer_fullname, customer_infos.id, customer_infos.user_id, invoices.invoice_date, invoices.pending_credit as invoice_pending_credit, credit_notes.pending_credit as credit_notes_pending_credit, customer_infos.route_id as customer_infos_route_id, customer_lobs.route_id as customer_lobs_route_id, customer_lobs.lob_id, YEAR(invoices.invoice_date) as year, MONTH(invoices.invoice_date) as month, MONTHNAME(invoices.invoice_date) as monthname, sum(invoices.pending_credit) as invoice_total, sum(credit_notes.pending_credit) as credit_notes_total from customer_infos left join users on customer_infos.user_id = users.id left join customer_routes on customer_infos.id = customer_routes.customer_id left join invoices on customer_infos.user_id = invoices.customer_id left join salesman_infos on salesman_infos.user_id = invoices.salesman_id 
left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id
left join customer_lobs on customer_infos.id = customer_lobs.customer_info_id left join credit_notes on users.id = credit_notes.customer_id WHERE invoices.invoice_date is not null AND invoices.pending_credit is not null AND credit_notes.pending_credit is not null AND invoices.invoice_date between "sdatess" and "edatess" ';

IF sdate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'sdatess',sdate);
END IF;

IF edate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'edatess',edate);
END IF;

IF cust_id > 0 THEN
SET otherreplace = ' AND customer_infos.user_id = cust_ids';
SET otherreplace = REPLACE (otherreplace,'cust_ids',cust_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(invoices.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

  
SET otherreplace = ' group by customer_infos.id, month, year order by customer_infos.id asc';

SET SQLText = CONCAT(SQLText,otherreplace); 

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `MonthlyAgeingReportOneCustomer`(IN `sdate` DATE, IN `edate` DATE, IN `cust_id` INT(11) UNSIGNED, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
BEGIN

DECLARE SQLText varchar(8000);

DECLARE otherreplace varchar(500);


SET SQLText ='select invoices.pending_credit as invoice_pending_credit from customer_infos left join users on customer_infos.user_id = users.id left join customer_routes on customer_infos.id = customer_routes.customer_id left join invoices on customer_infos.user_id = invoices.customer_id left join salesman_infos on salesman_infos.user_id = invoices.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id  WHERE invoices.invoice_date is not null AND invoices.pending_credit is not null AND invoices.invoice_date between "sdatess" and "edatess" ';

IF sdate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'sdatess',sdate);
END IF;

IF edate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'edatess',edate);
END IF;

IF cust_id > 0 THEN
SET otherreplace = ' AND customer_infos.user_id = cust_ids';
SET otherreplace = REPLACE (otherreplace,'cust_ids',cust_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(invoices.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `SalesQuantityCreditNoteReport`(IN `sdate` DATE, IN `edate` DATE, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `cat_id` VARCHAR(500) CHARSET utf8, IN `cust_id` VARCHAR(500) CHARSET utf8, IN `org_id` INT(11) UNSIGNED, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
BEGIN

DECLARE ids varchar(500);

DECLARE cates_id varchar(500);

DECLARE other varchar(500);

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);


SET cates_id = REPLACE(cat_id,' ','');

SET other = REPLACE(all_ids,' ','');

SET SQLText ='select credit_notes.credit_note_number,credit_notes.id as credit_notes_id, credit_notes.salesman_id, credit_notes.customer_id, credit_note_details.id as credit_note_details_id, credit_note_details.item_id, items.item_code, items.item_name,item_uoms.name as uoms_name, SUM(credit_note_details.item_qty) as Total_return_qty from credit_notes left join credit_note_details on credit_note_details.credit_note_id = credit_notes.id left join items on items.id = credit_note_details.item_id left join item_uoms on item_uoms.id = items.lower_unit_uom_id left join salesman_infos on salesman_infos.user_id = credit_notes.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id WHERE items.organisation_id = org_idss AND credit_notes.credit_note_date between "sdatess" and "edatess" ';

IF org_id > 0 THEN
SET SQLText = REPLACE (SQLText,'org_idss',org_id);
END IF;

IF sdate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'sdatess',sdate);
END IF;

IF edate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'edatess',edate);
END IF;

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(credit_notes.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',other);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;



IF cat_id != '' THEN
SET otherreplace = ' AND FIND_IN_SET(items.item_major_category_id, "cates_idss")'; 
SET otherreplace = REPLACE (otherreplace,'cates_idss',cates_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF cust_id != '' THEN
SET otherreplace = ' AND FIND_IN_SET(credit_notes.customer_id, "cust_ids")';
SET otherreplace = REPLACE (otherreplace,'cust_ids',cust_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' group by credit_note_details_id';

SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `SalesQuantityInvoiceReport`(IN `sdate` DATE, IN `edate` DATE, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `cat_id` VARCHAR(500) CHARSET utf8, IN `cust_id` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
BEGIN

DECLARE ids varchar(500);

DECLARE cates_id varchar(500);

DECLARE other varchar(500);

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET cates_id = REPLACE(cat_id,' ','');

SET other = REPLACE(all_ids,' ','');

SET SQLText ='select invoices.invoice_number,invoices.id as invoices_id,invoices.salesman_id, invoices.customer_id, invoice_details.id as invoice_details_id, invoice_details.item_id, items.item_code, items.item_name,item_uoms.name as uoms_name, SUM(invoice_details.item_qty) as Total_sales_qty FROM invoices left join invoice_details on invoice_details.invoice_id = invoices.id left join items on items.id = invoice_details.item_id left join item_uoms on item_uoms.id = items.lower_unit_uom_id 
left join salesman_infos on salesman_infos.user_id = invoices.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id and items.lower_unit_uom_id != 0 WHERE invoices.invoice_date between "sdatess" and "edatess" ';

IF sdate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'sdatess',sdate);
END IF;

IF edate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'edatess',edate);
END IF;


IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(invoices.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',other);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;


IF cat_id != '' THEN
SET otherreplace = ' AND FIND_IN_SET(items.item_major_category_id, "cates_idss")'; 
SET otherreplace = REPLACE (otherreplace,'cates_idss',cates_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF cust_id != '' THEN
SET otherreplace = ' AND FIND_IN_SET(invoices.customer_id, "cust_ids")';
SET otherreplace = REPLACE (otherreplace,'cust_ids',cust_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' group by invoices_id';

SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `TimeSheetReport`(IN `sdate` DATE, IN `edate` DATE, IN `salesman_name` VARCHAR(500) CHARSET utf8, IN `salesman_code` VARCHAR(500) CHARSET utf8, IN `org_id` INT(11) UNSIGNED, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = ' select trips.id, trips.route_id, trips.salesman_id, trips.trip_start, trips.trip_start_date, trips.trip_start_time, trips.trip_end, trips.trip_end_date, trips.trip_end_time, users.firstname,users.lastname,salesman_infos.user_id,salesman_infos.salesman_code from trips LEFT JOIN users on users.id = trips.salesman_id left join salesman_infos on salesman_infos.user_id = trips.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id where exists (select * from customer_visits where trips.id = customer_visits.trip_id and customer_visits.shop_status = "open" and customer_visits.reason is null and customer_visits.deleted_at is null and customer_visits.organisation_id = 1) and exists (select * from customer_visits where trips.id = customer_visits.trip_id and exists (select * from customer_activities where customer_visits.id = customer_activities.customer_visit_id and customer_activities.id is not null and customer_activities.deleted_at is null) and customer_visits.deleted_at is null and customer_visits.organisation_id = 1) and trips.deleted_at is null ';

IF (sdate IS NOT NULL AND edate IS NOT NULL) THEN
SET otherreplace = ' AND trips.trip_start  between "sdatess" and "edatess" ';
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF org_id > 0 THEN 
SET otherreplace = ' and trips.organisation_id = org_id';
SET otherreplace = REPLACE (otherreplace,'org_id',org_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(trips.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_name != '' THEN

SET SQLText = CONCAT(SQLText,salesman_name);

END IF;

IF salesman_code != '' THEN

SET SQLText = CONCAT(SQLText,salesman_code);
 
END IF;

SET otherreplace = ' Group by trips.id order by trips.id desc';

SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `TimeSheetReportCustomerActivity`(IN `cust_visit_id` INT(11) UNSIGNED)
    NO SQL
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'select * from customer_activities where customer_visit_id = visit_idss';

SET SQLText = REPLACE (SQLText,'visit_idss',cust_visit_id);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `TimeSheetReportCustomerVisit`(IN `trip_id` INT(11) UNSIGNED, IN `org_id` INT(11) UNSIGNED)
    NO SQL
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'select customer_visits.*,customer_infos.customer_code,users.firstname,users.lastname from customer_visits LEFT JOIN users on users.id = customer_visits.customer_id LEFT JOIN customer_infos on customer_infos.user_id =  users.id where shop_status = "open" and reason is null';

IF trip_id > 0 THEN
SET otherreplace = ' and customer_visits.trip_id = trip_idss';
SET otherreplace = REPLACE (otherreplace,'trip_idss',trip_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' and exists (select * from customer_activities where customer_visits.id = customer_activities.customer_visit_id and id is not null and customer_activities.deleted_at is null) and customer_visits.deleted_at is null';
SET SQLText = CONCAT(SQLText,otherreplace);

IF org_id > 0 THEN
SET otherreplace = ' and customer_visits.organisation_id = ord_idss';
SET otherreplace = REPLACE (otherreplace,'ord_idss',org_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' order by customer_visits.id desc';
SET SQLText = CONCAT(SQLText,otherreplace);


SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `PerodicewisecollectionReport`(IN `sdate` DATE, IN `edate` DATE, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'select users.firstname,users.lastname,customer_infos.customer_code,collections.collection_number as recipt_number,collections.payemnt_type,invoices.invoice_number,collection_details.amount,collection_details.pending_amount,collections.created_at as date from collections left join salesman_infos on salesman_infos.user_id = collections.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id left join users on users.id = collections.customer_id left join customer_infos on customer_infos.user_id = users.id left join collection_details on collection_details.collection_id = collections.id left join invoices on invoices.id = collection_details.invoice_id WHERE customer_infos.customer_code IS NOT NULL';

IF sdate IS NOT NULL AND edate IS NOT NULL THEN
	SET otherreplace = ' AND collections.created_at between "sdatess" and "edatess" ';
	SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
	SET otherreplace = REPLACE (otherreplace,'edatess',edate);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF all_ids != '' THEN
SET otherreplace = ' AND FIND_IN_SET(collections.salesman_id, "saelsidss")'; 
SET otherreplace = REPLACE (otherreplace,'saelsidss',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = '  Group BY date';
SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `coverage_sp_channel_three`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `cust_id` LONGTEXT CHARSET utf8mb4)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'select customer_visits.added_on as date, count(customer_visits.id) as value from channels inner join  customer_infos ON customer_infos.channel_id = channels.id inner join customer_lobs ON customer_lobs.channel_id = channels.id inner join  customer_visits ON customer_visits.customer_id = customer_infos.user_id where customer_infos.is_lob = 1 AND customer_visits.shop_status = "open" AND customer_visits.reason is null';

IF org_id > 0 THEN
	SET otherreplace = ' AND channels.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate = edate) THEN
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSEIF (sdate IS NOT NULL AND edate IS NOT NULL) THEN

SET otherreplace = ' AND customer_visits.date between "sdatess" and "edatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSE 
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

END IF;

IF cust_id != '' THEN
SET otherreplace = ' AND customer_infos.user_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,cust_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' group by customer_visits.date order by customer_visits.added_on asc';
SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `coverage_sp_coverage_listing_region`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `cust_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'select DISTINCT DATE(customer_visits.added_on) as date,customerInfo.firstname AS customer,customer_infos.customer_code AS customerCode,customer_categories.customer_category_name AS category,salesman.firstname AS merchandiser,salesman_infos.salesman_supervisor AS supervisor,channels.name AS channel,regions.region_name AS region,customer_visits.total_task AS total_tasks_planned,SUM(customer_visits.completed_task) AS no_of_tasks_completed,COUNT(DISTINCT customer_visits.id) as total_visits from regions inner join customer_infos on customer_infos.region_id = regions.id inner join customer_routes on customer_routes.customer_id = customer_infos.id inner join salesman_infos on salesman_infos.route_id = customer_routes.route_id inner join users as salesman on salesman.id = salesman_infos.user_id inner join customer_visits on customer_infos.user_id = customer_visits.customer_id inner join users as customerInfo on customerInfo.id = customer_infos.user_id inner join customer_categories on customer_categories.id = customer_infos.customer_category_id inner join channels on channels.id = customer_infos.channel_id where customer_visits.shop_status = "open" and customer_visits.completed_task != 0 and customer_visits.reason is null';

IF org_id > 0 THEN
	SET otherreplace = ' AND regions.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF cust_id != '' THEN
SET otherreplace = ' AND customer_visits.customer_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,cust_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' group by customer_visits.added_on, region'; 
SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `coverage_sp_listing_channel`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `cust_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'select DISTINCT DATE(customer_visits.added_on) as date,customerInfo.firstname AS customer,customer_infos.customer_code AS customerCode,customer_categories.customer_category_name AS category,salesman.firstname AS merchandiser,salesman_infos.salesman_supervisor AS supervisor,channels.name AS channel,regions.region_name AS region,customer_visits.total_task AS total_tasks_planned,SUM(customer_visits.completed_task) AS no_of_tasks_completed,COUNT(DISTINCT customer_visits.id) as total_visits from channels inner join customer_infos on customer_infos.channel_id = channels.id inner join customer_routes on customer_routes.customer_id = customer_infos.id inner join salesman_infos on salesman_infos.route_id = customer_routes.route_id inner join users as salesman on salesman.id = salesman_infos.user_id inner join customer_visits on customer_infos.user_id = customer_visits.customer_id inner join users as customerInfo on customerInfo.id = customer_infos.user_id inner join customer_categories on customer_categories.id = customer_infos.customer_category_id inner join regions on regions.id = customer_infos.region_id where customer_visits.shop_status = "open" and customer_visits.completed_task != 0 and customer_visits.reason is null';

IF org_id > 0 THEN
	SET otherreplace = ' AND channels.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate = edate) THEN
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSEIF (sdate IS NOT NULL AND edate IS NOT NULL) THEN

SET otherreplace = ' AND customer_visits.date between "sdatess" and "edatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSE 
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

END IF;

IF cust_id != '' THEN
SET otherreplace = ' AND customer_visits.customer_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,cust_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;
                    
                    
SET otherreplace = ' group by customer_visits.added_on, channel'; 
SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `coverage_sp_listing_data_last`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'select DISTINCT DATE(customer_visits.added_on) as date,customerInfo.firstname AS customer,customer_infos.customer_code AS customerCode,customer_categories.customer_category_name AS category,salesman.firstname AS merchandiser,salesmanSupervisor.firstname AS supervisor,channels.name AS channel,regions.region_name AS region,customer_visits.total_task AS total_tasks_planned,SUM(customer_visits.completed_task) AS no_of_tasks_completed,COUNT(DISTINCT customer_visits.id) as total_visits from salesman_infos inner join users as salesman on salesman.id = salesman_infos.user_id inner join users as salesmanSupervisor on salesmanSupervisor.id = salesman_infos.salesman_supervisor left join customer_routes on customer_routes.route_id = salesman_infos.route_id inner join customer_infos on customer_infos.id = customer_routes.customer_id inner join customer_visits on customer_infos.user_id = customer_visits.customer_id inner join users as customerInfo on customerInfo.id = customer_infos.user_id inner join customer_lobs on customer_lobs.customer_info_id = customer_infos.id inner join customer_categories on customer_categories.id = customer_lobs.customer_category_id inner join channels on channels.id = customer_lobs.channel_id inner join regions on regions.id = customer_lobs.region_id where customer_visits.shop_status = "open" and customer_visits.reason is null';

IF org_id > 0 THEN
	SET otherreplace = ' AND salesman_infos.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate = edate) THEN
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSEIF (sdate IS NOT NULL AND edate IS NOT NULL) THEN

SET otherreplace = ' AND customer_visits.date between "sdatess" and "edatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSE 
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

END IF;

IF sales_id != '' THEN
SET otherreplace = ' AND customer_visits.salesman_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;


SET otherreplace = ' group by customer_visits.customer_id, customer_visits.date';
SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `coverage_sp_listing_data_nsm`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `nsm_id` LONGTEXT CHARSET utf8, IN `salesman_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'select DISTINCT DATE(customer_visits.added_on) as date,\r\n                customerInfo.firstname AS customer,\r\n                customer_infos.customer_code AS customerCode,\r\n                customer_categories.customer_category_name AS category,\r\n                salesman.firstname AS merchandiser,\r\n                salesmanSupervisor.firstname AS supervisor,\r\n                channels.name AS channel,\r\n                regions.region_name AS region,\r\n                customer_visits.total_task AS total_tasks_planned,\r\n                SUM(customer_visits.completed_task) AS no_of_tasks_completed,\r\n                COUNT(DISTINCT customer_visits.id) as total_visits from salesman_infos inner join organisation_role_user_attacheds as attached on attached.role_user_id = salesman_infos.salesman_supervisor inner join users as salesman on salesman.id = salesman_infos.user_id inner join users as salesmanSupervisor on salesmanSupervisor.id = salesman_infos.salesman_supervisor left join customer_routes on customer_routes.route_id = salesman_infos.route_id inner join customer_infos on customer_infos.id = customer_routes.customer_id inner join customer_visits on customer_infos.user_id = customer_visits.customer_id inner join users as customerInfo on customerInfo.id = customer_infos.user_id inner join customer_categories on customer_categories.id = customer_infos.customer_category_id inner join channels on channels.id = customer_infos.channel_id inner join regions on regions.id = customer_infos.region_id where customer_visits.shop_status = "open" ';
                
IF org_id > 0 THEN
SET otherreplace = ' AND salesman_infos.organisation_id = org_idss';
SET otherreplace = REPLACE (otherreplace,'org_idss',org_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF nsm_id != '' THEN
SET otherreplace = ' AND attached.role_parent_user_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,nsm_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_id != '' THEN
SET otherreplace = ' AND customer_visits.salesman_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,salesman_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate = edate) THEN
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSEIF (sdate IS NOT NULL AND edate IS NOT NULL) THEN

SET otherreplace = ' AND customer_visits.date between "sdatess" and "edatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSE 
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

END IF;


SET otherreplace = ' group by customer_visits.customer_id, customer_visits.date, merchandiser'; 
SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `coverage_sp_listing_data_one`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'select DISTINCT DATE(customer_visits.date) as date, customerInfo.firstname AS customer, customer_infos.customer_code AS customerCode,customer_categories.customer_category_name AS category,salesman.firstname AS merchandiser,salesman_infos.salesman_supervisor AS supervisor,channels.name AS channel,regions.region_name AS region,customer_visits.total_task AS total_tasks_planned,SUM(customer_visits.completed_task) AS no_of_tasks_completed,COUNT(DISTINCT customer_visits.id) as total_visits from salesman_infos inner join users as salesman on salesman.id = salesman_infos.user_id inner join customer_routes on customer_routes.route_id = salesman_infos.route_id \r\ninner join customer_infos on customer_infos.id = customer_routes.customer_id inner join customer_visits on salesman_infos.user_id = customer_visits.salesman_id inner join users as customerInfo on customerInfo.id = customer_infos.user_id inner join customer_lobs on customer_lobs.customer_info_id = customer_infos.id \r\ninner join customer_categories on customer_categories.id = customer_lobs.customer_category_id inner join channels on channels.id = customer_lobs.channel_id inner join regions on regions.id = customer_lobs.region_id where customer_visits.shop_status = "open" and customer_visits.reason is null ';

IF org_id > 0 THEN
	SET otherreplace = ' AND salesman_infos.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate = edate) THEN
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSEIF (sdate IS NOT NULL AND edate IS NOT NULL) THEN

SET otherreplace = ' AND customer_visits.date between "sdatess" and "edatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSE 
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

END IF;

IF sales_id != '' THEN
SET otherreplace = ' AND customer_visits.customer_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;


SET otherreplace = ' group by customer_visits.customer_id, customer_visits.date, merchandiser';
SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `coverage_sp_one`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sup_id` LONGTEXT CHARSET utf8, IN `cust_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

DECLARE cust_idss LONGTEXT;

SET cust_idss = cust_id;

SET SQLText = 'select customer_visits.date as date, count(DISTINCT customer_visits.customer_id) as value from salesman_infos inner join customer_visits on salesman_infos.user_id = customer_visits.salesman_id where customer_visits.shop_status = "open" and customer_visits.reason is null ';

IF org_id > 0 THEN
	SET otherreplace = ' AND salesman_infos.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sup_id != '' THEN
SET otherreplace = ' AND salesman_infos.salesman_supervisor IN ( sup_idss)'; 
SET otherreplace = REPLACE (otherreplace,'sup_idss',sup_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF cust_id != '' THEN
SET otherreplace = ' AND customer_visits.customer_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,cust_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate = edate) THEN
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSEIF (sdate IS NOT NULL AND edate IS NOT NULL) THEN

SET otherreplace = ' AND customer_visits.date between "sdatess" and "edatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSE 
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

END IF;


SET otherreplace = ' group by customer_visits.date order by customer_visits.added_on asc'; 
SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `coverage_sp_two`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);


SET SQLText = 'select customer_visits.date as date, count(DISTINCT customer_visits.customer_id) as value from salesman_infos inner join customer_visits on salesman_infos.user_id = customer_visits.salesman_id where customer_visits.shop_status = "open" and customer_visits.reason is null ';

IF org_id > 0 THEN
	SET otherreplace = ' AND salesman_infos.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;


IF sales_id != '' THEN
SET otherreplace = ' AND customer_visits.salesman_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate = edate) THEN
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSEIF (sdate IS NOT NULL AND edate IS NOT NULL) THEN

SET otherreplace = ' AND customer_visits.date between "sdatess" and "edatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSE 
SET otherreplace = ' AND customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

END IF;


SET otherreplace = ' group by customer_visits.date order by customer_visits.added_on asc'; 
SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `dashboardTotalCollection`(IN `sdate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'select SUM(invoice_amount) as invoiceAmt from collections left join salesman_infos on salesman_infos.user_id = collections.salesman_id';

IF org_id > 0 THEN
	SET otherreplace = ' WHERE collections.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sdate IS NOT NULL THEN
SET otherreplace = ' AND date(collections.created_at) = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sales_id != '' THEN
SET otherreplace = ' AND salesman_infos.user_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `dashboardTotalSales`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'select invoices.id as invoices_id,invoices.salesman_id, invoices.customer_id, SUM(invoices.grand_total) as Total_sales FROM invoices left join salesman_infos on salesman_infos.user_id = invoices.salesman_id LEFT JOIN users on users.id = invoices.salesman_id ';

IF org_id > 0 THEN
	SET otherreplace = ' WHERE invoices.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sdate IS NOT NULL THEN
SET otherreplace = ' AND invoices.invoice_date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sales_id != '' THEN
SET otherreplace = ' AND salesman_infos.user_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `dashboardTotalSalesUnit`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'select invoices.id as invoices_id FROM invoices left join salesman_infos on salesman_infos.user_id = invoices.salesman_id LEFT JOIN users on users.id = invoices.salesman_id ';

IF org_id > 0 THEN
	SET otherreplace = ' WHERE invoices.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sdate IS NOT NULL THEN
SET otherreplace = ' AND invoices.invoice_date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sales_id != '' THEN
SET otherreplace = ' AND salesman_infos.user_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `dashboardTotalSalesUnitDetails`(IN `invoice_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'select SUM(lower_unit_qty) as lowerUnitQty FROM invoice_details';

IF invoice_id != '' THEN
SET otherreplace = ' WHERE invoice_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,invoice_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;


SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `execution_sp_listing`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `cust_id` LONGTEXT CHARSET utf8mb4)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'select DISTINCT DATE(customer_visits.added_on) as date,customerInfo.firstname AS customer,customer_categories.customer_category_name AS category,salesman.firstname AS merchandiser,salesman_infos.salesman_supervisor AS supervisor,customer_infos.customer_code AS customerCode,channels.name AS channel,regions.region_name AS region,customer_visits.start_time AS startTime,customer_visits.end_time AS endTime,customer_visits.total_task as totalTask,customer_visits.completed_task as completedTask,customer_visits.visit_total_time as timeSpent,customer_visits.latitude AS latitude,customer_visits.longitude AS longitude from channels inner join customer_infos on customer_infos.channel_id = channels.id inner join users as customerInfo on customerInfo.id = customer_infos.user_id inner join customer_routes on customer_routes.route_id = customer_infos.route_id inner join salesman_infos on salesman_infos.route_id = customer_routes.route_id inner join users as salesman on salesman.id = salesman_infos.user_id inner join customer_visits on customer_visits.customer_id = customer_infos.user_id inner join regions on regions.id = customer_infos.region_id inner join customer_categories on customer_categories.id = customer_infos.customer_category_id where customer_visits.shop_status = "open" and customer_visits.reason is null AND customer_visits.completed_task != 0 and customer_infos.status = 1';

IF org_id > 0 THEN
	SET otherreplace = ' AND customer_visits.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate = edate) THEN
SET otherreplace = ' AND customer_visits.added_on = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSEIF (sdate IS NOT NULL AND edate IS NOT NULL AND edate != '0000-00-00' AND sdate != '0000-00-00') THEN

SET otherreplace = ' AND customer_visits.added_on between "sdatess" and "edatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSE 
SET otherreplace = ' AND customer_visits.added_on = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

END IF;

IF cust_id != '' THEN
SET otherreplace = ' AND customer_visits.customer_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,cust_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `execution_sp_listing_region`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `cust_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'select DISTINCT DATE(customer_visits.added_on) as date,customerInfo.firstname AS customer,customer_categories.customer_category_name AS category,salesman.firstname AS merchandiser,customer_infos.customer_code AS customerCode,salesman_infos.salesman_supervisor AS supervisor,channels.name AS channel,regions.region_name AS region,customer_visits.start_time AS startTime,customer_visits.end_time AS endTime,customer_visits.total_task as totalTask,customer_visits.completed_task as completedTask,customer_visits.visit_total_time as timeSpent,customer_visits.latitude AS latitude,customer_visits.longitude AS longitude from regions inner join customer_infos on customer_infos.region_id = regions.id inner join users as customerInfo on customerInfo.id = customer_infos.user_id inner join customer_routes on customer_routes.route_id = customer_infos.route_id inner join salesman_infos on salesman_infos.route_id = customer_routes.route_id inner join users as salesman on salesman.id = salesman_infos.user_id inner join customer_visits on customer_visits.customer_id = customer_infos.user_id inner join channels on channels.id = customer_infos.channel_id inner join customer_categories on customer_categories.id = customer_infos.customer_category_id where customer_visits.shop_status = "open" and customer_visits.completed_task != 0 and customer_infos.status = 1 and customer_visits.reason is null and customer_visits.deleted_at is null';

IF org_id > 0 THEN
	SET otherreplace = ' AND customer_visits.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF (sdate = edate) THEN
SET otherreplace = ' AND customer_visits.added_on = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSEIF (sdate IS NOT NULL AND edate IS NOT NULL AND edate != '0000-00-00' AND sdate != '0000-00-00') THEN

SET otherreplace = ' AND customer_visits.added_on between "sdatess" and "edatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSE 
SET otherreplace = ' AND customer_visits.added_on = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

END IF;

IF cust_id != '' THEN
SET otherreplace = ' AND customer_visits.customer_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,cust_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `getCreditNoteReport`(IN `sdate` DATE, IN `edate` DATE, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `cat_id` VARCHAR(500) CHARSET utf8, IN `cust_id` VARCHAR(500) CHARSET utf8, IN `org_id` INT(11) UNSIGNED, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
BEGIN

DECLARE ids varchar(500);

DECLARE cates_id varchar(500);

DECLARE other varchar(500);

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);


SET cates_id = REPLACE(cat_id,' ','');

SET other = REPLACE(all_ids,' ','');

SET SQLText ='select credit_notes.id as credit_notes_id, credit_notes.salesman_id, credit_notes.customer_id, credit_note_details.id as credit_note_details_id, credit_note_details.item_id, items.item_code, items.item_name, SUM(credit_note_details.item_gross) as Total_creditnote from credit_notes left join credit_note_details on credit_note_details.credit_note_id = credit_notes.id left join items on items.id = credit_note_details.item_id left join salesman_infos on salesman_infos.user_id = credit_notes.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id WHERE credit_notes.credit_note_date between "sdatess" and "edatess" ';

IF org_id > 0 THEN
SET otherreplace = ' AND items.organisation_id = org_idss '; 
SET otherreplace = REPLACE (otherreplace,'org_idss',org_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF  sdate IS NOT NULL AND edate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'sdatess',sdate);
SET SQLText = REPLACE (SQLText,'edatess',edate);

END IF;

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(credit_notes.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',other);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF cat_id != '' THEN
SET otherreplace = ' AND FIND_IN_SET(items.item_major_category_id, ''cates_idss'')'; 
SET otherreplace = REPLACE (otherreplace,'cates_idss',cates_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF cust_id != '' THEN
SET otherreplace = '  AND FIND_IN_SET(credit_notes.customer_id, "cust_ids")';
SET otherreplace = REPLACE (otherreplace,'cust_ids',cust_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' group by credit_note_details.item_id';

SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `getCustomerBalanceSheet`(IN `sdate` DATE, IN `edate` DATE, IN `customer_id` VARCHAR(500) CHARSET utf8, IN `org_id` INT(11) UNSIGNED, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);


SET SQLText ='(select DATE_FORMAT(debit_note_date,"%d/%m/%Y") as c_date,(CASE WHEN is_debit_note=1  THEN "Debit Note" WHEN is_debit_note=0  THEN (select t2.item_name from debit_note_listingfee_shelfrent_rebatediscount_details t2 where t2.debit_note_id = debit_notes.id) END ) as transaction ,debit_note_number as detail, "0.00" as amount, grand_total as payment, 4 as status, debit_notes.created_at,(CASE WHEN debit_notes.lob_id=1 THEN "SPICES DIVISION" WHEN debit_notes.lob_id=2 THEN "RTC" WHEN debit_notes.lob_id=3 THEN "RICE DIVISION" END) as debitnote_lob_names,grand_total as balance from debit_notes left join salesman_infos on salesman_infos.user_id = debit_notes.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id where debit_notes.debit_note_date between "sdate" and "edate" and debit_notes.deleted_at is null and debit_notes.organisation_id = org_id ';

IF customer_id > 0 THEN
SET otherreplace = ' AND debit_notes.customer_id = customer_idsss ';
SET otherreplace = REPLACE (otherreplace,'customer_idsss',customer_id);
SET SQLText = CONCAT(SQLText,otherreplace); 
END IF;

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(debit_notes.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' order by debit_note_date asc)';
SET SQLText = CONCAT(SQLText,otherreplace); 	
		
SET SQLText = CONCAT(SQLText, ' union (select DATE_FORMAT(invoice_date,"%d/%m/%Y") as c_date,"Invoice" as transaction,CONCAT(invoice_number," - due on ",DATE_FORMAT(invoice_due_date,"%d/%m/%y")) as detail,grand_total as amount,"0.00" as payment,1 as status, invoices.created_at,(CASE WHEN invoices.lob_id=1 THEN "SPICES DIVISION" WHEN invoices.lob_id=2 THEN "RTC" WHEN invoices.lob_id=3 THEN "RICE DIVISION" END) as debitnote_lob_names,grand_total as balance from invoices left join salesman_infos on salesman_infos.user_id = invoices.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id where invoice_date between "sdate" and "edate" and invoices.deleted_at is null and invoices.organisation_id = org_id ');

IF customer_id > 0 THEN
SET otherreplace = ' AND invoices.customer_id = customer_idsss';
SET otherreplace = REPLACE (otherreplace,'customer_idsss',customer_id);
SET SQLText = CONCAT(SQLText,otherreplace); 
END IF;


IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(invoices.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' order by invoices.created_at asc)';
SET SQLText = CONCAT(SQLText,otherreplace); 

SET SQLText = CONCAT(SQLText, ' union (select DATE_FORMAT(collection_details.created_at,"%d/%m/%Y") as\r\nc_date,"Collection" as transaction, CONCAT(" For payment of ",collections.collection_number),\r\n"0.00" as amount,amount as payment,2 as\r\nstatus,collection_details.created_at,(CASE WHEN collection_details.lob_id=1 THEN "SPICES DIVISION" WHEN collection_details.lob_id=2 THEN "RTC" WHEN collection_details.lob_id=3 THEN "RICE DIVISION" END ) as debitnote_lob_names, amount as balance from collection_details left join collections on collections.id = collection_details.collection_id \r\nleft join salesman_infos on salesman_infos.user_id = collections.salesman_id \r\nleft join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id \r\nwhere collection_details.created_at between "sdate" and "edate" and collection_details.deleted_at is null');


IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(collections.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF customer_id > 0 THEN
SET otherreplace = ' AND collection_details.customer_id = customer_idsss';
SET otherreplace = REPLACE (otherreplace,'customer_idsss',customer_id);
SET SQLText = CONCAT(SQLText,otherreplace); 
END IF;

SET otherreplace = ' order by collection_details.created_at asc)';
SET SQLText = CONCAT(SQLText,otherreplace); 


SET SQLText =CONCAT(SQLText, ' union (select DATE_FORMAT(credit_note_date,"%d/%m/%Y") as c_date,"Credit Note" as transaction,credit_note_number as detail, "0.00" as amount, grand_total as payment,3 as status, credit_notes.created_at,(CASE WHEN credit_notes.lob_id=1 THEN "SPICES DIVISION" WHEN credit_notes.lob_id=2 THEN "RTC" WHEN credit_notes.lob_id=3 THEN "RICE DIVISION" END) as debitnote_lob_names, grand_total as balance from credit_notes left join salesman_infos on salesman_infos.user_id = credit_notes.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id where credit_notes.credit_note_date between "sdate" and "edate" and credit_notes.deleted_at is null and credit_notes.organisation_id = org_id ');

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(credit_notes.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF customer_id > 0 THEN
SET otherreplace = ' AND credit_notes.customer_id = customer_idsss';
SET otherreplace = REPLACE (otherreplace,'customer_idsss',customer_id);
SET SQLText = CONCAT(SQLText,otherreplace); 
END IF;

SET otherreplace = ' order by credit_notes.created_at asc) order by created_at asc';
SET SQLText = CONCAT(SQLText,otherreplace); 

IF sdate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'sdate',sdate);
END IF;

IF edate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'edate',edate);
END IF;

IF org_id > 0 THEN
SET SQLText = REPLACE (SQLText,'org_id',org_id);
END IF;

IF org_id > 0 THEN
SET SQLText = REPLACE (SQLText,'org_id',org_id);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`admin`@`localhost` PROCEDURE `getCustomerDropDownList`(IN `_type` INT(20), IN `_search_value` VARCHAR(250))
BEGIN
    SELECT
        u.id, CONCAT(u.firstname, u.lastname) AS name,
        c.customer_code,c.payment_term_id, c.is_lob,c.user_id
    FROM
        users AS u
    INNER JOIN customer_infos AS c
    ON
        c.user_id = u.id
    WHERE
        u.usertype = _type
        AND 
       ( 
           u.firstname LIKE CONCAT('%', _search_value , '%') ||
           u.lastname LIKE CONCAT('%', _search_value , '%') ||
           c.customer_code LIKE CONCAT('%', _search_value , '%')
       
       );

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`admin`@`localhost` PROCEDURE `getItemDropDownList`(IN `_search_value` VARCHAR(250))
BEGIN
    SELECT
    i.id,i.uuid,i.item_name,i.item_code,i.lower_unit_item_upc,i.stock_keeping_unit,i.lower_unit_item_price 
    FROM
    `items` as i
    WHERE
       ( 
           i.item_name LIKE CONCAT('%', _search_value , '%') ||
           i.item_code LIKE CONCAT('%', _search_value , '%') 
       );

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `getRouteInvoice`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'SELECT id as route_id, route_name from routes WHERE deleted_at IS NULL';

IF org_id > 0 THEN
	SET otherreplace = ' AND organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `getRouteVisit`(IN `sdate` DATE, IN `edate` DATE, IN `div_id` VARCHAR(500) CHARSET utf8, IN `org_id` INT(11) UNSIGNED, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
    NO SQL
BEGIN

DECLARE divid varchar(500);

DECLARE SQLText varchar(8000);

DECLARE otherreplace varchar(500);

SET divid = REPLACE(div_id,' ','');


SET SQLText ='SELECT SUM(CASE WHEN customer_visits.journey_plan_id > 0 THEN 1 ELSE 0 END) as total_journey,SUM(CASE WHEN customer_visits.is_sequnece = "1" THEN 1 ELSE 0 END) as planed_journey,SUM(CASE WHEN customer_visits.is_sequnece = "0" THEN 1 ELSE 0 END) as un_planed_journey,customer_visits.id, customer_visits.customer_id, customer_visits.route_id, customer_visits.journey_plan_id,customer_visits.salesman_id, customer_visits.latitude,customer_visits.longitude,customer_visits.start_time,customer_visits.end_time,customer_visits.is_sequnece,customer_visits.date,customer_visits.created_at, salesman_infos.route_id as salesman_route,salesman_infos.salesman_code,CONCAT(users.firstname," ",users.lastname) as salesman_name,customer_visits.salesman_id FROM customer_visits LEFT JOIN salesman_infos ON salesman_infos.user_id = customer_visits.salesman_id LEFT JOIN salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id LEFT JOIN users on users.id = salesman_infos.user_id WHERE ';


IF (sdate = edate) THEN
SET otherreplace = ' customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSEIF (sdate IS NOT NULL AND edate IS NOT NULL) THEN

SET otherreplace = ' customer_visits.date between "sdatess" and ''edatess'' '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSE 

SET otherreplace = ' customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

END IF;


IF div_id != '' THEN
SET otherreplace = ' AND FIND_IN_SET(customer_visits.salesman_id, "dividss")'; 
SET otherreplace = REPLACE (otherreplace,'dividss',divid);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF; 

IF org_id > 0 THEN
SET otherreplace = ' AND customer_visits.organisation_id = org_idsss';
SET otherreplace = REPLACE (otherreplace,'org_idsss',org_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' group by customer_visits.salesman_id, customer_visits.date,customer_visits.customer_id order by customer_visits.id desc,customer_visits.id DESC';

SET SQLText = CONCAT(SQLText,otherreplace); 

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `getRouteWiseInvoiceIds`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `route_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText ='select invoices.id as invoices_id FROM invoices left join salesman_infos on salesman_infos.user_id = invoices.salesman_id  WHERE invoices.invoice_date between "sdatess" and "edatess"  ';

IF sdate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'sdatess',sdate);
END IF;

IF edate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'edatess',edate);
END IF;

IF org_id > 0 THEN
	SET otherreplace = ' AND invoices.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_id > 0 THEN
	SET otherreplace = ' AND invoices.route_id = routeidss '; 
	SET otherreplace = REPLACE (otherreplace,'routeidss',route_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sales_id != '' THEN
SET otherreplace = ' AND salesman_infos.user_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `getSalesAnalysisReport`(IN `sdate` DATE, IN `edate` DATE, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `cat_id` VARCHAR(500) CHARSET utf8, IN `cust_id` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
    NO SQL
BEGIN

DECLARE ids varchar(500);

DECLARE cates_id varchar(500);

DECLARE other varchar(500);

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET cates_id = REPLACE(cat_id,' ','');

SET other = REPLACE(all_ids,' ','');

SET SQLText ='select invoices.id as invoices_id,invoices.salesman_id, invoices.customer_id, invoice_details.id as invoice_details_id, invoice_details.item_id, items.item_code, items.item_name,  SUM(invoice_details.item_grand_total) as Total_invoice_sales, SUM(invoice_details.item_net) as Total_invoice_net FROM invoices left join invoice_details on invoice_details.invoice_id = invoices.id left join items on items.id = invoice_details.item_id left join salesman_infos on salesman_infos.user_id = invoices.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id WHERE invoices.invoice_date between "sdatess" and "edatess" ';

IF sdate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'sdatess',sdate);
END IF;

IF edate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'edatess',edate);
END IF;

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(invoices.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',other);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF cat_id != '' THEN
SET otherreplace = ' AND FIND_IN_SET(items.item_major_category_id, "cates_idss")'; 
SET otherreplace = REPLACE (otherreplace,'cates_idss',cates_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF cust_id != '' THEN
SET otherreplace = '  AND FIND_IN_SET(invoices.customer_id, "cust_ids")';
SET otherreplace = REPLACE (otherreplace,'cust_ids',cust_id);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' group by invoice_details.item_id';

SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `getTEST`(IN `sdate` DATE, IN `edate` DATE, IN `route_id` VARCHAR(500) CHARSET utf8, IN `all_ids` VARCHAR(500) CHARSET utf8)
BEGIN

DECLARE ids varchar(500);

DECLARE cates_id varchar(500);

DECLARE other varchar(500);

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET ids = REPLACE(route_id,' ','');

SET SQLText ='select invoices.id as invoices_id,invoices.salesman_id, invoices.customer_id, invoice_details.id as invoice_details_id, invoice_details.item_id, items.item_code, items.item_name,  SUM(invoice_details.item_grand_total) as Total_invoice_sales, SUM(invoice_details.item_net) as Total_invoice_net FROM invoices left join invoice_details on invoice_details.invoice_id = invoices.id left join items on items.id = invoice_details.item_id WHERE invoices.invoice_date between "sdatess" and "edatess" ';

IF sdate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'sdatess',sdate);
END IF;

IF edate IS NOT NULL THEN
SET SQLText = REPLACE (SQLText,'edatess',edate);
END IF;

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(invoices.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',other);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_id != '' THEN
SET otherreplace = ' AND FIND_IN_SET(invoices.route_id, "routeidss")'; 
SET otherreplace = REPLACE (otherreplace,'routeidss',ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;


SET otherreplace = ' group by invoice_details.item_id';

SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `getUniqueSalesmanIdFrom_salesman_unloads_tbl`()
SELECT DISTINCT(salesman_id) FROM salesman_unloads$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `loadsheetreports`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
    NO SQL
BEGIN

 DECLARE otherreplace varchar(500);

 DECLARE SQLText varchar(8000);
 
 SET SQLText = 'select load_requests.id, load_requests.route_id, load_requests.salesman_id, load_requests.load_number, load_requests.load_type, load_requests.load_date, load_requests.status, routes.route_code, routes.route_name, users.firstname, users.lastname, salesman_infos.salesman_code, salesman_infos.user_id, load_request_details.load_request_id,load_request_details.item_id ,load_request_details.item_uom_id ,load_request_details.qty,load_request_details.requested_qty,items.item_name, items.item_code ,item_uoms.name as item_uom_name, item_uoms.id as item_uoms_id, item_uoms.code as item_uoms_code FROM load_requests LEFT JOIN routes ON routes.id = load_requests.route_id LEFT JOIN users ON users.id = load_requests.salesman_id left join salesman_infos on salesman_infos.user_id = load_requests.salesman_id left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id LEFT JOIN load_request_details ON load_request_details.load_request_id = load_requests.id LEFT JOIN items ON items.id = load_request_details.item_id LEFT JOIN item_uoms ON item_uoms.id = load_request_details.item_uom_id WHERE load_requests.load_date between "sdatess" and "edatess" AND load_requests.deleted_at is null and load_requests.organisation_id = org_idss';
 
 
 SET SQLText = REPLACE (SQLText,'sdatess',sdate);
 SET SQLText = REPLACE (SQLText,'edatess',edate);
 SET SQLText = REPLACE (SQLText,'org_idss',org_id);
 
IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(load_requests.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;	

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;
 
SET otherreplace = ' Group By load_requests.id';
SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

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

DELIMITER $$
CREATE PROCEDURE `GetTotalSalesInvoices`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `sales_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText ='select invoices.id as invoices_id FROM invoices left join salesman_infos on salesman_infos.user_id = invoices.salesman_id  ';

IF (sdate IS NOT NULL AND edate IS NOT NULL) THEN
SET otherreplace = ' WHERE invoices.invoice_date between "sdatess" and "edatess"'; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF org_id > 0 THEN
	SET otherreplace = ' AND invoices.organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF sales_id != '' THEN
SET otherreplace = ' AND salesman_infos.user_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,sales_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `loadsheetreportsDetails`(IN `load_id` INT(11) UNSIGNED)
    NO SQL
BEGIN

 DECLARE otherreplace varchar(500);

 DECLARE SQLText varchar(8000);
 
 SET SQLText = 'select load_request_details.load_request_id,load_request_details.item_id ,load_request_details.item_uom_id ,load_request_details.qty,load_request_details.requested_qty,items.item_name, items.item_code ,item_uoms.name as item_uom_name, item_uoms.id as item_uoms_id, item_uoms.code as item_uoms_code FROM load_request_details LEFT JOIN items ON items.id = load_request_details.item_id LEFT JOIN item_uoms ON item_uoms.id = load_request_details.item_uom_id where load_request_details.load_request_id = loadrequest_idss';
 
 
 SET SQLText = REPLACE (SQLText,'loadrequest_idss',load_id);
 
SET @SQLText = SQLText;

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

DELIMITER $$
CREATE PROCEDURE `orderReport`(IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT(11) UNSIGNED, IN `all_ids` VARCHAR(500) CHARSET utf8, IN `div_this` VARCHAR(500) CHARSET utf8, IN `region_this` VARCHAR(500) CHARSET utf8, IN `route_this` VARCHAR(500) CHARSET utf8, IN `supervisor_this` VARCHAR(500) CHARSET utf8, IN `salesman_this` VARCHAR(500) CHARSET utf8)
BEGIN

 DECLARE otherreplace varchar(500);

 DECLARE SQLText varchar(8000);
 
 SET SQLText = 'select orders.id, orders.customer_id, orders.depot_id, orders.order_type_id, orders.salesman_id, orders.order_number, orders.order_date, orders.due_date, orders.delivery_date, orders.payment_term_id, orders.total_qty, orders.total_gross, orders.total_discount_amount, orders.total_net, orders.total_vat, orders.total_excise, orders.grand_total, orders.current_stage, orders.lob_id, concat(custUser.firstname," ",custUser.lastname) AS customer_fullname, concat(salesUser.firstname," ",salesUser.lastname) AS salesman_fullname,   customer_infos.user_id,customer_infos.customer_code, salesman_infos.salesman_code, order_types.name as orderTypeName, order_types.description,payment_terms.name as paymentTermsName, payment_terms.number_of_days,lobs.name as lobsname FROM orders 
 LEFT JOIN users as custUser ON custUser.id = orders.customer_id
 LEFT JOIN customer_infos ON customer_infos.user_id = custUser.id  
 LEFT JOIN users as salesUser ON salesUser.id = orders.salesman_id 
 left join salesman_infos on salesman_infos.user_id = orders.salesman_id 
 left join salesman_lobs on salesman_lobs.salesman_info_id = salesman_infos.id 
 LEFT JOIN order_types ON  order_types.id = orders.order_type_id 
 LEFT JOIN payment_terms ON  payment_terms.id = orders.payment_term_id 
 LEFT JOIN depots ON  depots.id = orders.depot_id 
 LEFT JOIN lobs ON  lobs.id = orders.lob_id 
 where orders.order_date between "sdatess" and "edatess" AND orders.organisation_id = org_idss AND orders.deleted_at is null';
 
 SET SQLText = REPLACE (SQLText,'sdatess',sdate);
 SET SQLText = REPLACE (SQLText,'edatess',edate);
 SET SQLText = REPLACE (SQLText,'org_idss',org_id);
 
IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(orders.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF div_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_lobs.lob_id, "divthis_ids")';
SET otherreplace = REPLACE (otherreplace,'divthis_ids',div_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF region_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.region_id, "regionthis_ids")';
SET otherreplace = REPLACE (otherreplace,'regionthis_ids',region_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF route_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.route_id, "routethis_ids")';
SET otherreplace = REPLACE (otherreplace,'routethis_ids',route_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF supervisor_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.salesman_supervisor, "supervisorthis_ids")';
SET otherreplace = REPLACE (otherreplace,'supervisorthis_ids',supervisor_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

IF salesman_this != '' THEN
SET otherreplace = '  AND FIND_IN_SET(salesman_infos.user_id, "salesmanthis_ids")';
SET otherreplace = REPLACE (otherreplace,'salesmanthis_ids',salesman_this);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' Group by orders.id';

SET SQLText = CONCAT(SQLText,otherreplace);
 
SET @SQLText = SQLText;

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

DELIMITER $$
CREATE PROCEDURE `orderReportDetails`(IN `order_id` INT(11) UNSIGNED)
    NO SQL
BEGIN

 DECLARE otherreplace varchar(500);

 DECLARE SQLText varchar(8000);
 
 SET SQLText = 'select order_details.order_id, order_details.item_id, order_details.item_uom_id, order_details.discount_id, order_details.is_free, order_details.is_item_poi, order_details.promotion_id, order_details.item_qty, order_details.item_price, order_details.item_gross , order_details.item_discount_amount , order_details.item_net , order_details.item_vat , order_details.item_excise , order_details.item_grand_total , order_details.delivered_qty , order_details.open_qty , order_details.order_status, items.item_name, items.item_code,item_uoms.name as itemUomName,item_uoms.code as itemUomCode, price_disco_promo_plans.name as priceDiscoPromoName, discount.name as discountName FROM order_details  LEFT JOIN items ON items.id = order_details.item_id LEFT JOIN item_uoms ON item_uoms.id = order_details.item_uom_id LEFT JOIN price_disco_promo_plans ON price_disco_promo_plans.id = order_details.promotion_id LEFT JOIN price_disco_promo_plans as discount ON price_disco_promo_plans.id = order_details.discount_id ';
 
 IF order_id > 0 THEN
 SET otherreplace = ' where order_details.order_id = order_idss';
 SET otherreplace = REPLACE (otherreplace,'order_idss',order_id);
 SET SQLText = CONCAT(SQLText,otherreplace);
 END IF;
 
 SET @SQLText = SQLText;

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

DELIMITER $$
CREATE PROCEDURE `routeComplianceSP`(IN `sdate` DATE, IN `edate` DATE, IN `all_ids` VARCHAR(500) CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace varchar(500);

DECLARE SQLText varchar(8000);

SET SQLText = 'SELECT salesman_infos.salesman_supervisor,salesman_infos.salesman_code,customer.firstname as cust_firstname,customer.lastname as cust_lastname,salesman.firstname as sales_firstname,salesman.lastname as sales_lastname, salesman.email as sales_email, customer_visits.id, customer_visits.customer_id,customer_visits.journey_plan_id, customer_visits.salesman_id, customer_visits.latitude, customer_visits.longitude, customer_visits.start_time, customer_visits.end_time, customer_visits.is_sequnece, customer_visits.date, customer_visits.created_at,SUM(CASE WHEN customer_visits.journey_plan_id > 0 THEN 1 ELSE 0 END) as total_journey,SUM(CASE WHEN is_sequnece = "1" THEN 1 ELSE 0 END) as planed_journey,SUM(CASE WHEN is_sequnece = "0" THEN 1 ELSE 0 END) as un_planed_journey from customer_visits LEFT JOIN users as customer on customer.id = customer_visits.customer_id LEFT JOIN users as salesman on salesman.id = customer_visits.salesman_id LEFT JOIN salesman_infos on salesman_infos.user_id = salesman.id';

IF (sdate = edate) THEN
SET otherreplace = ' WHERE customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSEIF (sdate IS NOT NULL AND edate IS NOT NULL) THEN

SET otherreplace = ' WHERE customer_visits.date between "sdatess" and ''edatess'' '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET otherreplace = REPLACE (otherreplace,'edatess',edate);
SET SQLText = CONCAT(SQLText,otherreplace);

ELSE 

SET otherreplace = ' WHERE customer_visits.date = "sdatess" '; 
SET otherreplace = REPLACE (otherreplace,'sdatess',sdate);
SET SQLText = CONCAT(SQLText,otherreplace);

END IF;

IF all_ids != '' THEN 
SET otherreplace = ' AND FIND_IN_SET(customer_visits.salesman_id, "otherid")'; 
SET otherreplace = REPLACE (otherreplace,'otherid',all_ids);
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET otherreplace = ' GROUP BY customer_visits.salesman_id, customer_visits.date, customer_visits.customer_id';
SET SQLText = CONCAT(SQLText,otherreplace);

SET @SQLText = SQLText;

 PREPARE stmt FROM @SQLText;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 


END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `salesmanIdGet`(IN `org_id` INT(11) UNSIGNED)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText = 'select user_id from salesman_infos';

IF org_id > 0 THEN
	SET otherreplace = ' WHERE organisation_id = orgidss '; 
	SET otherreplace = REPLACE (otherreplace,'orgidss',org_id);
	SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_add_routegroup`(IN `routeid` INT(11), IN `groupid` INT(11))
BEGIN
	DECLARE cnt INT;
		
	SELECT COUNT(*) INTO @cnt FROM route_group WHERE routeid =routeid AND groupid =groupid ; 	
	
	IF @cnt = 0 THEN
	INSERT INTO route_group(routeid,groupid)
	VALUES(routeid,groupid);
	END IF;
		
	
	
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_get_pending_invoice`(IN `routeid` VARCHAR(255), IN `org_id` INT)
BEGIN
      
    
SELECT 'Credit' itype,a.id AS invid,a.credit_note_number AS invoicenumber,a.pdc_amount AS pdcAmount,b.customer_code,CONCAT(u.firstname,u.lastname) AS customername,a.customer_id,a.credit_note_date AS invdate,a.credit_note_date AS invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype FROM `credit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE b.organisation_id =org_id and b.customer_group_id IN (SELECT DISTINCT groupid FROM route_group WHERE routeid IN (routeid)) OR b.id IN (SELECT DISTINCT customer_id FROM customer_routes WHERE route_id IN (routeid))  
UNION
SELECT 'Invoice' itype,a.id AS invid,a.invoice_number AS invoicenumber,a.pdc_amount AS pdcAmount,b.customer_code,CONCAT(u.firstname,u.lastname) AS customername,a.customer_id,a.invoice_date AS invdate,a.invoice_due_date AS invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype FROM `invoices` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0 INNER JOIN users u ON b.user_id =u.id WHERE b.organisation_id =org_id and b.customer_group_id IN (SELECT DISTINCT groupid FROM route_group WHERE routeid IN (routeid) ) OR b.id IN (SELECT DISTINCT customer_id FROM customer_routes WHERE route_id IN (routeid)) 
UNION
SELECT 'Debit' itype,a.id AS invid,a.`debit_note_number` AS invoicenumber,a.pdc_amount AS pdcAmount,b.customer_code,CONCAT(u.firstname,u.lastname) AS customername,a.customer_id,a.debit_note_date AS invdate,a.debit_note_date AS invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,a.`debit_note_type` AS debittype FROM `debit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0 INNER JOIN users u ON b.user_id =u.id WHERE b.organisation_id =org_id and b.customer_group_id  IN (SELECT DISTINCT groupid FROM route_group WHERE routeid IN (routeid) ) OR b.id IN (SELECT DISTINCT customer_id FROM customer_routes WHERE route_id IN (routeid));	
    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_get_pending_invoice_customer`(IN `customerid` INT, IN `lobid` INT, IN `sdate` DATE, IN `edate` DATE)
BEGIN
  
 SELECT 'Credit' itype,a.id as invid,a.credit_note_number AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.credit_note_date as invdate,a.credit_note_date as invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype FROM `credit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id  AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id  WHERE a.customer_id = customerid AND a.lob_id = lobid AND a.created_at BETWEEN  sdate  AND edate  
UNION
SELECT 'Invoice' itype,a.id as invid,a.invoice_number AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.invoice_date AS invdate,a.invoice_due_date AS invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype FROM `invoices` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE a.customer_id = customerid AND a.lob_id = lobid AND a.created_at BETWEEN  sdate  AND edate  
UNION
SELECT 'Debit' itype,a.id as invid,a.`debit_note_number` AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.debit_note_date as invdate,a.debit_note_date as invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,a.`debit_note_type` AS debittype FROM `debit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE a.customer_id = customerid AND a.lob_id = lobid AND a.created_at BETWEEN   sdate  AND edate   ;  
  
    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_get_pending_invoice_customer_id`(IN `customerid` INT)
BEGIN
  
SELECT 'Credit' itype,a.id as invid,a.credit_note_number AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.credit_note_date as invdate,a.credit_note_date as invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount, a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype  FROM `credit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id  AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE a.customer_id = customerid  
UNION
SELECT 'Invoice' itype,a.id as invid,a.invoice_number AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.invoice_date AS invdate,a.invoice_due_date AS invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount, a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype  FROM `invoices` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE a.customer_id = customerid  
UNION

SELECT 'Debit' itype,a.id as invid,a.`debit_note_number` AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.debit_note_date as invdate,a.debit_note_date as invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount, a.`total_net` AS netamount,a.`total_vat` AS vatamount,a.`debit_note_type` AS debittype FROM `debit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE a.customer_id = customerid ;
  
    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_get_pending_invoice_customer_lobid`(IN `customerid` INT, IN `lobid` INT)
BEGIN
  
     
SELECT 'Credit' itype,a.id as invid,a.credit_note_number AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.credit_note_date as invdate,a.credit_note_date as invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype FROM `credit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id  AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE a.customer_id = customerid AND a.lob_id = lobid 
UNION
SELECT 'Invoice' itype,a.id as invid,a.invoice_number AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.invoice_date AS invdate,a.invoice_due_date AS invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype FROM `invoices` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE a.customer_id = customerid AND a.lob_id = lobid 
UNION
SELECT 'Debit' itype,a.id as invid,a.`debit_note_number` AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.debit_note_date as invdate,a.debit_note_date as invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,a.`debit_note_type` AS debittype FROM `debit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE a.customer_id = customerid AND a.lob_id = lobid;
  
    END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_get_pending_invoice_group`(IN `groupid` INT, IN `org_id` INT)
BEGIN
    
SELECT 'Credit' itype,a.id AS invid,a.credit_note_number AS invoicenumber,b.customer_code,CONCAT(u.firstname,u.lastname) AS customername,a.customer_id,a.credit_note_date AS invdate,a.credit_note_date AS invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype FROM `credit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE b.organisation_id =org_id AND b.customer_group_id IN (groupid)
UNION
SELECT 'Invoice' itype,a.id AS invid,a.invoice_number AS invoicenumber,b.customer_code,CONCAT(u.firstname,u.lastname) AS customername,a.customer_id,a.invoice_date AS invdate,a.invoice_due_date AS invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype FROM `invoices` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0 INNER JOIN users u ON b.user_id =u.id WHERE b.organisation_id =org_id AND b.customer_group_id IN (groupid)
UNION
SELECT 'Debit' itype,a.id AS invid,a.`debit_note_number` AS invoicenumber,b.customer_code,CONCAT(u.firstname,u.lastname) AS customername,a.customer_id,a.debit_note_date AS invdate,a.debit_note_date AS invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,a.`debit_note_type` AS debittype FROM `debit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0 INNER JOIN users u ON b.user_id =u.id WHERE b.organisation_id =org_id AND b.customer_group_id  IN (groupid);	
	
    END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `GetTotalSalesInvoicesDetails`(IN `invoice_id` LONGTEXT CHARSET utf8)
    NO SQL
BEGIN

DECLARE otherreplace LONGTEXT;

DECLARE SQLText LONGTEXT;

SET SQLText ='select SUM(invoice_details.item_qty) as Total_sales_qty FROM invoice_details left join items on items.id = invoice_details.item_id left join item_uoms on item_uoms.id = items.lower_unit_uom_id ';

IF invoice_id != '' THEN
SET otherreplace = ' WHERE invoice_details.invoice_id IN (';
SET SQLText = CONCAT(SQLText,otherreplace);
SET SQLText = CONCAT(SQLText,invoice_id);
SET otherreplace = ')';
SET SQLText = CONCAT(SQLText,otherreplace);
END IF;

SET @SQLText = SQLText;

PREPARE stmt FROM @SQLText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_get_pending_invoice_group_date`(IN `groupid` INT, IN `sdate` DATE, IN `edate` DATE, IN `org_id` INT)
BEGIN
    
        
    
SELECT 'Credit' itype,a.id as invid,a.credit_note_number AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.credit_note_date as invdate,a.credit_note_date as invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype FROM `credit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id  AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE b.organisation_id =org_id AND b.customer_group_id in (groupid) and a.created_at BETWEEN  sdate  AND edate   
UNION
SELECT 'Invoice' itype,a.id as invid,a.invoice_number AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.invoice_date AS invdate,a.invoice_due_date AS invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,'' AS debittype FROM `invoices` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE b.organisation_id =org_id AND b.customer_group_id in (groupid) and a.created_at BETWEEN  sdate  AND edate  
UNION
SELECT 'Debit' itype,a.id as invid,a.`debit_note_number` AS invoicenumber,b.customer_code,Concat(u.firstname,u.lastname) as customername,a.customer_id,a.debit_note_date as invdate,a.debit_note_date as invduedate,a.grand_total,a.lob_id,b.customer_group_id,a.`pending_credit` AS pending_amount,a.`total_net` AS netamount,a.`total_vat` AS vatamount,a.`debit_note_type` AS debittype FROM `debit_notes` a INNER JOIN customer_infos b ON a.customer_id = b.user_id AND a.`pending_credit`>0  INNER JOIN users u ON b.user_id =u.id WHERE b.organisation_id =org_id AND b.customer_group_id  in (groupid) and a.created_at BETWEEN  sdate  AND edate;
	
	
    END$$
DELIMITER ;