BEGIN
	#Routine body goes here...
	SELECT var_name,
				 var_id,
				(SELECT GROUP_CONCAT(topd.var_progress) FROM tm_op_template_detail topd WHERE top.var_id = topd.var_id) 'item',
				(SELECT GROUP_CONCAT(CONCAT('<span style="background-color: #398bf7;color: white;padding: 5px;border-radius: 5px;">',topd.var_progress,'</span>')) 
				FROM tm_op_template_detail topd WHERE top.var_id = topd.var_id)'progress' 
	FROM tm_op_template top WHERE tin_inactive = 1;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_PASSENGER_LIST`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_PASSENGER_LIST`()
BEGIN
	SELECT 
		var_id 'var_id', 
		var_name 'var_name', 
		var_gender 'var_gender', 
		var_uom_id 'var_uom_id', 
		dat_dob 'dat_dob', 
		dat_pp_issued 'dat_pp_issued', 
		dat_pp_expiry 'dat_pp_expiry', 
		var_pp_number 'var_pp_number', 
		var_nationality 'var_nationality' 
	FROM 
		tm_passenger 
	WHERE 
		tin_inactive = 1;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_PRICING_LIST`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_PRICING_LIST`(pricing_id varchar(100),var_limit int(6), var_page int(6))
BEGIN
	#Routine body goes here...
	DECLARE var_off INT;
	SET var_off = ((var_page-1)*var_limit);
	IF pricing_id = '' THEN
		SELECT 
					tp.var_id 'pricingId',
					tp.var_freegift 'gift',
					tp.var_promotion_disc 'promotion',
					tp.var_agent_comm 'comission',
					tp.tour_id 'tourId',
					CONCAT('[',tp.tour_id,'] ', (SELECT var_itemcode FROM ame_mt_ite WHERE var_id = (SELECT item FROM tm_tour tt WHERE tt.tour_id = tp.tour_id))) 'tour',
					(SELECT DATE_FORMAT(start_date,'%d/%M/%Y') FROM tm_tour tt WHERE tp.tour_id = tt.tour_id)'start_date',
					(SELECT DATE_FORMAT(end_date,'%d/%M/%Y') FROM tm_tour tt WHERE tp.tour_id = tt.tour_id)'end_date',
					(SELECT GROUP_CONCAT(DISTINCT (SELECT country_name FROM tm_country WHERE tm_country.country_id = tm_tour_trip.country_id) SEPARATOR ', ') FROM tm_tour_trip WHERE tour_id = tp.tour_id) 'country',
					(SELECT var_name FROM ame_mt_emp WHERE var_id = (SELECT tour_leader FROM tm_tour tt WHERE tp.tour_id = tt.tour_id)) 'tour_leader'
		FROM tm_pricing tp WHERE tp.tin_inactive = 1 LIMIT var_off, var_limit; 
	ELSE
			SELECT 
						tpd.var_uom_id 'uom',	
						tpd.dou_profit 'profit'
			FROM tm_pricing_detail tpd 
			WHERE tpd.var_id = pricing_id;
			SELECT var_uom_id 'uom',SUM(dou_price_usd)'cost'FROM tm_quote_detail_uom TQDU WHERE TQDU.var_quote_id IN (SELECT quote_id FROM tm_quote WHERE tm_quote.tour_id = (SELECT tm_pricing.tour_id FROM tm_pricing WHERE tm_pricing.var_id = pricing_id))GROUP BY var_uom_id;
	END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_PRICING_LOAD`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_PRICING_LOAD`(tour_id varchar(100))
BEGIN
	#Routine body goes here...
	if tour_id = '' THEN
		#Load Tour	
			SELECT
				tt.tour_id 'tour_id',
				(SELECT var_itemcode FROM ame_mt_ite WHERE var_id = item) 'var_itemcode'
			FROM 
				tm_tour tt
			LEFT JOIN tm_pricing USING(tour_id)
		  WHERE tour_inactive = 1 AND tm_pricing.tour_id IS NULL OR tm_pricing.tin_inactive = 2;
	ELSE 
		#Load Tour
		SELECT 
			tt.tour_id,
			(SELECT tm_tour_category.category_name FROM tm_tour_category WHERE tm_tour_category.id = tt.category_id) 'category',
			(SELECT tm_tour_type.type_name FROM tm_tour_type WHERE tm_tour_type.type_id = tt.type_id) 'type',
			(SELECT ami.var_itemcode FROM ame_mt_ite ami WHERE tt.item = ami.var_id)'code',
			DATE_FORMAT(tt.start_date, '%d/%m/%Y') 'startdate',
			DATE_FORMAT(tt.end_date, '%d/%m/%Y') 'enddate',
			(SELECT var_name FROM ame_mt_emp WHERE ame_mt_emp.var_id = tt.tour_leader) 'emp',
			(SELECT tm_tour_season.season_name FROM tm_tour_season WHERE tm_tour_season.season_id = tt.season_id) 'season',
			tt.item 'itemId'
		FROM
			tm_tour tt
		WHERE tt.tour_id = tour_id;
		#Load Country & Destination
		SELECT country_id, country_name FROM tm_country WHERE country_id IN (SELECT ttt.country_id FROM tm_tour_trip ttt WHERE ttt.tour_id = tour_id);
		SELECT destination_id, destination_name, country_id FROM tm_destination WHERE destination_id IN (SELECT ttt.destination FROM tm_tour_trip ttt WHERE ttt.tour_id = tour_id);
		SELECT var_uomid 'uom', (SELECT var_name FROM ame_mt_uom WHERE var_id = var_uomid) 'uomname', COALESCE(int_tm_amount, 0)'qty' FROM ame_mt_ite_uom WHERE var_id = (SELECT item FROM tm_tour tt WHERE tt.tour_id = tour_id);
		SELECT var_uom_id 'uom',SUM(dou_price_usd)'uomPrice'FROM tm_quote_detail_uom TQDU WHERE TQDU.var_quote_id IN (SELECT quote_id FROM tm_quote WHERE tm_quote.tour_id = tour_id)GROUP BY var_uom_id; 
	END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_PRICING_LOAD_UPDATE`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_PRICING_LOAD_UPDATE`(pricingId varchar(110))
BEGIN
	#Routine body goes here...
	#Load Tour on Update
	SELECT
		tt.tour_id 'tour_id',
		(SELECT var_itemcode FROM ame_mt_ite WHERE var_id = item) 'var_itemcode'	
	FROM 
		tm_tour tt
	LEFT JOIN tm_booking USING(tour_id)
	WHERE tour_inactive = 1 AND tm_booking.tour_id IS NULL OR tm_booking.booking_inactive = 2 OR tt.tour_id =(SELECT tour_id FROM tm_pricing WHERE tm_pricing.var_id=pricingId);
  CALL TM_PRO_PRICING_LOAD((SELECT tour_id FROM tm_pricing WHERE tm_pricing.var_id=pricingId));
	SELECT tpd.var_uom_id 'uom',tpd.dou_profit 'profit' FROM tm_pricing_detail tpd WHERE tpd.var_id = pricingId;
	SELECT var_freegift 'gift',
				 var_promotion_disc'promotion',
				 var_agent_comm'comission',
				 (SELECT var_id FROM tm_pricing_history WHERE tm_pricing_history.var_pricing_id = pricingId LIMIT 1)'pricingHistoryId'
	FROM tm_pricing WHERE tm_pricing.var_id = pricingId;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_QUOTATION_CREATE_LOAD`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_QUOTATION_CREATE_LOAD`(IN par_id VARCHAR(255))
BEGIN

	#Tour
	SELECT 
		tt.tour_id 'tour_id',
		(SELECT var_itemcode FROM ame_mt_ite WHERE var_id = item) 'itemcode',
		(SELECT category_name FROM tm_tour_category WHERE id = tt.category_id) 'category',
		(SELECT season_name FROM tm_tour_season WHERE season_id = tt.season_id) 'season',
		(SELECT type_name FROM tm_tour_type WHERE type_id = tt.type_id) 'type',
		DATE_FORMAT(tt.start_date, '%d/%m/%Y') 'startdate',
		DATE_FORMAT(tt.end_date, '%d/%m/%Y') 'enddate',
		(SELECT GROUP_CONCAT(var_uomid) FROM ame_mt_ite_uom WHERE var_id = tt.item) 'uom'
	FROM 
		tm_tour tt
	LEFT JOIN tm_quote USING(tour_id)
	WHERE IF(par_id  = '',tm_quote.tour_id IS NULL AND tour_inactive = 1 OR tm_quote.tin_inactive = 2,tm_quote.tour_id IS NULL AND tour_inactive = 1 OR tm_quote.tin_inactive = 2 OR tt.tour_id = par_id);

	#Referrence Quotation
	SELECT 
		quote_id 'quote_id', 
		quote_remark 'quote_remark' 
	FROM tm_quote
	WHERE tin_inactive = 1;

	#Vendor
	SELECT 
		var_id 'var_id',
		var_name 'var_name',
		var_name_con 'var_name_con',
		var_cur 'var_cur',
		(SELECT COALESCE(dou_rate, 0) FROM ame_mt_cur_rate WHERE var_id = var_cur) 'dou_rate'
	FROM 
		ame_mt_ven
	WHERE tin_inactive = 0
	ORDER BY var_name;

	#Tips
	SELECT
		tour_id 'tour_id',
		(SELECT country_name FROM tm_country WHERE tm_country.country_id = tm_tour_trip.country_id),
		CONCAT( '[',
			IF( tm_tour_trip.destination = 0, NULL,
				(SELECT GROUP_CONCAT('"', (SELECT destination_name FROM tm_destination WHERE tm_destination.destination_id = tm_tour_trip.destination), '"'))
			)
		,']')
	FROM
		tm_tour_trip
	GROUP BY tm_tour_trip.tour_id, tm_tour_trip.country_id;

	#Customer
	SELECT
		var_id 'var_id',
		var_name 'var_name',
		var_tel1 'var_tel1',
		var_tel2 'var_tel2'
	FROM
		ame_mt_cus
	WHERE tin_inactive = 0
	ORDER BY var_name;

	#Item
	SELECT 
		var_id 'var_id', 
		var_name 'var_name'
	FROM 
		ame_mt_ite 
	WHERE tin_inactive = 0;

	#UOM
	SELECT
		var_id 'var_id',
		var_name 'var_name'
	FROM
		ame_mt_uom;

	#Template
	CALL TM_PRO_QUOTATION_TEMPLATE_LOAD();
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_QUOTATION_DETAIL_LIST`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_QUOTATION_DETAIL_LIST`(IN par_var_id VARCHAR(255))
BEGIN
	#Detail
	SELECT
		quote_id,
		lineno,
		item_id,
		(SELECT var_name FROM ame_mt_ite WHERE var_id = item_id) 'var_item_name',
		description,
		(SELECT var_name FROM ame_mt_ven WHERE var_id = var_vendor) 'var_vendor_name',
		var_vendor,
		(SELECT CONCAT(var_cur, ' (', (SELECT var_symbol FROM ame_mt_cur WHERE ame_mt_cur.var_id = var_cur) , ')') FROM ame_mt_ven WHERE var_id = var_vendor) 'var_currency',
		(SELECT dou_rate FROM ame_mt_cur_rate WHERE var_id = (SELECT var_cur FROM ame_mt_ven WHERE var_id = var_vendor )) 'rate',
		(
			SELECT
				GROUP_CONCAT((SELECT var_name FROM ame_mt_uom WHERE var_id = var_uom_id), ',', dou_price_fc, ',', dou_price_usd SEPARATOR '|')
			FROM
				tm_quote_detail_uom
			WHERE
				var_quote_id = quote_id AND var_item_id = item_id
			GROUP BY var_item_id
		) 'uom_price'
	FROM
		tm_quote_detail
	WHERE quote_id = par_var_id
	GROUP BY item_id
	ORDER BY lineno;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_QUOTATION_LIST`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_QUOTATION_LIST`()
BEGIN
	SELECT
		tq.quote_id,
		DATE_FORMAT(create_date, '%d/%m/%Y') 'create_date',
		quote_remark,
		quote_reference,
		(SELECT var_name FROM ame_mt_emp WHERE var_id = (SELECT var_empid FROM ame_user WHERE ame_user.var_id = create_by)) 'create_by',
		GROUP_CONCAT(CONCAT(tbl.var_uom_id, '=', tbl.dou_price_usd)) 'quote_uom',
		(SELECT CONCAT('[', var_id, '] ', var_name) FROM ame_mt_cus WHERE var_id = tq.var_cust_id) 'customer',
		(SELECT var_tel1 FROM ame_mt_cus WHERE var_id = tq.var_cust_id) 'tel',
		(SELECT var_itemcode FROM ame_mt_ite WHERE var_id = (SELECT item FROM tm_tour WHERE tour_id = tq.tour_id)) 'tour_code'
	FROM tm_quote tq
	INNER JOIN (
		SELECT
			tqdu.var_quote_id 'quote_id',
			tqdu.var_uom_id 'var_uom_id',
			SUM(tqdu.dou_price_usd) 'dou_price_usd'
		FROM 
			tm_quote_detail_uom tqdu
		GROUP BY tqdu.var_quote_id, tqdu.var_uom_id
	) tbl USING(quote_id)
	WHERE tin_inactive = 1
	GROUP BY tq.quote_id;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_QUOTATION_TEMPLATE`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_QUOTATION_TEMPLATE`(IN par_var_status VARCHAR(100))
BEGIN
	IF par_var_status = 'item' OR par_var_status = 'all' THEN
		#Item
		SELECT
			var_id,
			var_name
		FROM
			ame_mt_ite
		WHERE
			tin_inactive = 0
		ORDER BY
			var_name;
	END IF;
	
	IF par_var_status = 'quotation' OR par_var_status = 'all' THEN
	#Quotation
	CALL TM_PRO_QUOTATION_TEMPLATE_LOAD();
	END IF;
	
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_QUOTATION_TEMPLATE_LOAD`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_QUOTATION_TEMPLATE_LOAD`()
BEGIN
	SELECT
		var_id,
		var_remark,
		CONCAT
		(
			'<div style="display: inline-flex;">',
			(
			SELECT 
				GROUP_CONCAT(
					(
						SELECT 
							CONCAT('<span style="background-color: #398bf7;color: white;padding: 5px;border-radius: 5px;"><strong>[',var_id,']</strong> ',var_name,'</span>')
						FROM
							ame_mt_ite
						WHERE
							var_id = var_item
					)
				)
			)
			,'</div>'
		) 'var_item',
		GROUP_CONCAT(var_item) 'item'
	FROM
		tm_quotation_template
	WHERE
		tin_inactive = 1
	GROUP BY
		var_id;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_QUOTATION_UPDATE_LOAD`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_QUOTATION_UPDATE_LOAD`(IN par_var_id VARCHAR(255))
BEGIN
	
	#Master
	SELECT 
		quote_remark,
		quote_reference,
		tour_id,
		var_cust_id
	FROM 
		tm_quote
	WHERE quote_id = par_var_id;
	
	#Detail
	SELECT
		lineno,
		item_id,
		description,
		var_vendor
	FROM
		tm_quote_detail
	WHERE quote_id = par_var_id;
	#Uom
	SELECT
		var_item_id,
		var_uom_id,
		dou_price_fc,
		dou_price_usd,
		var_remark
	FROM 
		tm_quote_detail_uom
	WHERE var_quote_id = par_var_id;

	CALL TM_PRO_QUOTATION_CREATE_LOAD((SELECT tour_id FROM tm_quote WHERE quote_id = par_var_id));
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_ROOMING_CHECK_PASSENGER`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_ROOMING_CHECK_PASSENGER`(IN par_var_pname VARCHAR(100))
BEGIN
	SELECT var_id FROM tm_passenger WHERE tin_inactive = 1 AND LOWER(var_name) = LOWER(par_var_pname);
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_ROOMING_CREATE_LOAD`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_ROOMING_CREATE_LOAD`()
BEGIN

	#List Infor Tour
	SELECT
		tour_id 'tour_id',
		(SELECT var_itemcode FROM ame_mt_ite WHERE var_id = tt.item) 'var_itemcode',
		(SELECT category_name FROM tm_tour_category WHERE id = tt.category_id) 'category_name',
		(SELECT type_name FROM tm_tour_type WHERE tm_tour_type.type_id = tt.type_id) 'type_name',
		DATE_FORMAT(visa_deadline, '%d/%m/%Y') 'visa_deadline',
		tour_leader 'tour_leader'
	FROM
		tm_tour tt
	WHERE
		tour_inactive = 1;

	#List Infor Tour Leader
	SELECT
		var_id 'var_id', 
		var_name 'var_name',
		var_hrm_sex 'var_hrm_sex',
		DATE_FORMAT(dat_hrm_dob, '%d/%m/%Y') 'dat_hrm_dob', 
		var_hrm_passportid 'var_hrm_passportid',
		var_hrm_nationality 'var_hrm_nationality', 
		var_tel1 'var_tel1' 
	FROM 
		ame_mt_emp;

	#List UOM and Limit Amount UOM
	SELECT 
		(SELECT tour_id FROM tm_tour WHERE item = var_id) 'tour_id', 
		var_id 'var_id',
		var_uomid 'var_uomid',
		(SELECT var_name FROM ame_mt_uom WHERE var_id = var_uomid) 'var_uom',
		int_tm_amount 'int_tm_amount'
	FROM 
		ame_mt_ite_uom 
	WHERE 
		int_tm_amount <> NULL || int_tm_amount > 0;

	#List Client
	SELECT 
		var_id 'var_id', 
		var_name 'var_name'
	FROM 
		ame_mt_cus	
	WHERE 
		tin_inactive = 0;

	#List Room Type
	SELECT 
		type_id 'type_id', 
		room_type 'room_type' 
	FROM 
		tm_room_type 
	WHERE 
		room_inactive = 1;

	#List Nationality
	SELECT 
		int_id 'int_id',
		var_name 'var_name'
	FROM 
		tm_nationality 
	WHERE 
		tin_inactive = 1;
	
	#List Passenger
	CALL TM_PRO_PASSENGER_LIST();
	
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_SETTING_LOAD`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_SETTING_LOAD`(var_chk tinyint(1))
BEGIN
	IF var_chk = TRUE THEN
		#ItemType
		SELECT var_name 'TypeName' FROM ame_sys_type WHERE var_id = 'IC_TYPE' ORDER BY int_sortor;
		#Uom
		SELECT var_id 'UomID', var_name 'Des' FROM ame_mt_uom;
		#Item
		SELECT var_id 'ItemID', var_name 'ItemName' FROM ame_mt_ite ORDER BY var_name;
		#PriceCode
		SELECT var_id 'PriceCode', var_name 'Description' FROM ame_mt_pri_cod ORDER BY var_name;
	END IF;
	#Setting
	SELECT id, module, object FROM tm_setting ORDER BY id;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_TOUR_LIST`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_TOUR_LIST`(par_limit int(6), par_page int(6))
BEGIN
#Init
	DECLARE var_off INT;
	SET var_off = ((par_page-1)*par_limit);

#List All Records
	SELECT
		tt.tour_id 'tour_id',
		ami.var_itemcode 'itemcode',
		(SELECT category_name FROM tm_tour_category WHERE tm_tour_category.id = tt.category_id) 'category',
		(SELECT type_name FROM tm_tour_type WHERE tm_tour_type.type_id = tt.type_id) 'type',
		(SELECT GROUP_CONCAT(DISTINCT (SELECT country_name FROM tm_country WHERE tm_country.country_id = tm_tour_trip.country_id) SEPARATOR ', ') FROM tm_tour_trip WHERE tour_id = tt.tour_id) 'country',
		DATE_FORMAT(tt.start_date, '%d/%m/%Y') 'startdate',
		DATE_FORMAT(tt.end_date, '%d/%m/%Y') 'enddate',
		(SELECT season_name FROM tm_tour_season WHERE tm_tour_season.season_id = tt.season_id) 'season',
		(SELECT GROUP_CONCAT(CONCAT('[',var_uomid, ': ', int_tm_amount, ']') SEPARATOR ', ') FROM ame_mt_ite_uom WHERE var_id = tt.item) 'uoms',
		(SELECT status_name FROM tm_tour_status WHERE tm_tour_status.status_id = tt.status_id) 'status'
	FROM
		tm_tour tt
	INNER JOIN ame_mt_ite ami ON tt.item = ami.var_id
	WHERE tt.tour_inactive = 1
	ORDER BY ami.var_itemcode
	LIMIT var_off, par_limit;

#Get Total Records
	SELECT
		COUNT(tour_id) 'totalRecords'
	FROM
		tm_tour
	WHERE tour_inactive = 1
	LIMIT var_off, par_limit;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_TOUR_LOAD`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_TOUR_LOAD`()
BEGIN
	#Number Prefix
	SELECT var_modid, CONCAT(var_id,crm_generate_zero(int_length, int_lastid)) 'var_id', var_name FROM tm_numberprefix_custom WHERE int_IsUsed = 1 ORDER BY var_name;
	#Season
	SELECT season_id, season_name FROM tm_tour_season WHERE season_inactive = 1 ORDER BY season_name;
	#Category
	SELECT id, category_name FROM tm_tour_category WHERE category_inactive = 1 ORDER BY category_name;
	#Type
	SELECT type_id, type_name FROM tm_tour_type WHERE type_inactive = 1 ORDER BY type_name;
	#Employee
	SELECT var_id, var_name FROM ame_mt_emp ORDER BY var_name;
	#GET Destination by Country
	CALL TM_COUNTRY_LIST_DEST();
	#Status Tour
	SELECT status_id, status_name FROM tm_tour_status WHERE status_inactive = 1;
	#Uom
	SELECT var_id, var_name FROM ame_mt_uom ORDER BY var_name;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_TOUR_UPDATE_LOAD`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_TOUR_UPDATE_LOAD`(IN par_id VARCHAR(255))
BEGIN
#Load Tour
	SELECT
		tour_id 'tour_id',
		(SELECT var_itemcode FROM ame_mt_ite WHERE var_id = item) 'var_itemcode',
		(SELECT var_name FROM ame_mt_ite WHERE var_id = item) 'var_name',
		(SELECT var_desc FROM ame_mt_ite WHERE var_id = item) 'var_desc',
		category_id 'category_id',
		type_id 'type_id',
		season_id 'season_id',
		item 'item',
		DATE_FORMAT(start_date, '%d/%m/%Y') 'start_date',
		DATE_FORMAT(end_date, '%d/%m/%Y') 'end_date',
		status_id 'status_id',
		tour_leader 'tour_leader',
		tour_instruction 'tour_instruction',
		DATE_FORMAT(visa_deadline, '%d/%m/%Y') 'visa_deadline',
		tour_year 'tour_year',
		tour_month 'tour_month'
	FROM
		tm_tour
	WHERE tour_id = par_id;
#Load Trip
	SELECT
		country_id 'country_id',
		IF(ttt.destination <> 0, CONCAT('[',(SELECT GROUP_CONCAT(ttt.destination)),']'), NULL) 'destination'
	FROM
		tm_tour_trip ttt
	WHERE tour_id = par_id
	GROUP BY country_id;
#Load Uom
	SELECT 
		var_uomid 'var_uomid', 
		int_tm_amount 'int_tm_amount' 
	FROM
		ame_mt_ite_uom
	WHERE var_id = (SELECT item FROM tm_tour WHERE tour_id = par_id)
	ORDER BY int_line;
	
#Load Startup
	CALL TM_PRO_TOUR_LOAD();
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `TM_PRO_USER_LOGIN`;
DELIMITER |
CREATE PROCEDURE `TM_PRO_USER_LOGIN`(IN var_uid VARCHAR(255))
BEGIN
	SELECT
		COALESCE(u.var_id, '') 'userId',
		COALESCE(u.var_name, '') 'username',
		COALESCE(u.var_crm_role, '') 'type',
		COALESCE(emp.var_name, '') 'empName',
		COALESCE(emp.var_email, '') 'empEmail',
		COALESCE(u.var_pwd, '') 'Password',
		COUNT(u.var_id) 'Has',
		COUNT(a.var_id) 'Permission'
	FROM
		ame_user u
	LEFT JOIN ame_user_app a ON u.var_id = a.var_id
	INNER JOIN ame_mt_emp emp ON u.var_empid = emp.var_id
	WHERE 
		u.var_id = var_uid
	LIMIT 1;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `tm_status`;
DELIMITER |
CREATE PROCEDURE `tm_status`(type varchar(19))
BEGIN
	#Routine body goes here...
	IF type = ' ' THEN
		SELECT * FROM tm_tour_status ;
	ELSE
		SELECT * FROM tm_tour;
	END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `updateLeadStatusToConverted`;
DELIMITER |
CREATE PROCEDURE `updateLeadStatusToConverted`(IN `leadId` varchar(11),IN `custId` varchar(255),IN `opId` varchar(11))
    DETERMINISTIC
BEGIN
IF opId = "" THEN
UPDATE crm_lead SET LA_StatusID = 4, LA_ConvertedDate = NOW(), LA_CustID = custId WHERE LA_ID = leadId;
ELSE
UPDATE crm_lead SET LA_StatusID = 4, LA_ConvertedDate = NOW(), LA_CustID = custId, LA_OPID = opId WHERE LA_ID = leadId;
END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `updatePostStatus`;
DELIMITER |
CREATE PROCEDURE `updatePostStatus`(IN `saleId` varchar(255), IN `postStatus` varchar(255))
    DETERMINISTIC
BEGIN
	UPDATE tblsaleorder SET PostStatus = postStatus WHERE SalID = saleId;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_ADD_PUR_CREDIT_SEEN_HIS`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_ADD_PUR_CREDIT_SEEN_HIS`(IN `userName` varchar(255),IN `purId` int)
    DETERMINISTIC
BEGIN

	SELECT vb_userId INTO @user_id from vb_user where vb_userName = userName;

	SELECT COUNT(pu.vpsid) INTO @rowCount FROM vb_purchaseseen pu WHERE pu.vpsid = purId AND pu.userId = @user_id;
	
	IF @rowCount = 0 THEN
		INSERT INTO vb_purchaseseen (vpsid, userId, date) VALUES(purId,@user_id,NOW());
	END IF;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_ADM_DAS`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_ADM_DAS`(IN `userId` int(11))
    DETERMINISTIC
BEGIN
		SELECT vb_roleType INTO @roleType FROM vb_user_role WHERE vb_roleId = (SELECT vb_admin_role FROM vb_user WHERE vb_userId = userId);
		SELECT vb_empId INTO @empId FROM vb_user WHERE vb_userId = userId;

		SELECT COUNT(var_id) 'customer' INTO @customer FROM ame_mt_cus WHERE IF(@roleType = 1, TRUE, var_id IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId));
    SELECT COUNT(vpStatus) 'purchase' INTO @purchase FROM vb_purchasecredit WHERE vb_purchasecredit.vpStatus = 'Deposit' AND IF(@roleType = 1, TRUE, custId IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId));
    SELECT COUNT(vit_id) 'order' INTO @orderCount FROM vb_item WHERE vb_item.vit_status = 2 AND IF(@roleType = 1, TRUE, vit_custID IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId));
		SELECT COUNT(vir_id) INTO @refundCount FROM vb_refunditem WHERE IF(@roleType = 1, TRUE, vir_custId IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId)) AND vir_status = 'Pending';
		SELECT @customer 'custCounts', @purchase 'purCounts', @orderCount 'orderCounts',@refundCount 'refundCount';
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CHECK_BAL_ORD`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CHECK_BAL_ORD`(cust varchar(200), orderid INT(11))
    DETERMINISTIC
BEGIN
	
	SET @balance = VB_FUN_CUST_BAL(cust);
  SELECT vit_totalINUsd INTO @netto FROM vb_item WHERE vit_id = orderid;
	
  IF @balance >= @netto THEN
		SELECT 'SUCCESS';
	ELSE 
		SELECT 'INVALID BALANCE';
	END IF;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CHECK_MOB_CODE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CHECK_MOB_CODE`(IN cust varchar(255), IN codes INT(11))
    DETERMINISTIC
BEGIN
		DECLARE cod INT(11) DEFAULT 0;
		SELECT COALESCE(vb_code,0) INTO cod FROM vb_user WHERE CustID = cust AND vb_code = codes;
		IF cod <> 0 THEN
			UPDATE vb_user SET vb_isMobileVerified = 1 WHERE CustID = cust;
			SELECT cod;
		ELSE
				SELECT cod;
		END IF;
		
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CHECK_USER_NAME`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CHECK_USER_NAME`(IN `uname` varchar(255))
    DETERMINISTIC
BEGIN
	
	DECLARE countUser INT;
  SELECT COUNT(vb_userName) INTO countUser FROM vb_user WHERE vb_userName = uname;
	SELECT countUser;
	
 

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CLEAR_DATA`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CLEAR_DATA`()
BEGIN
	
	TRUNCATE TABLE vb_customershipaddress;
	TRUNCATE TABLE vb_customer_conditions;
	TRUNCATE TABLE vb_customer_goods_receiver;
	TRUNCATE TABLE vb_cust_addon_info;
	TRUNCATE TABLE vb_item;
	TRUNCATE TABLE vb_itemdetail;
	TRUNCATE TABLE vb_itemcart;
	TRUNCATE TABLE vb_itemproperty;
	TRUNCATE TABLE vb_exchangerate;
	TRUNCATE TABLE vb_imageslide;
	TRUNCATE TABLE vb_notification_detail;
	TRUNCATE TABLE vb_purchasecredit;
	TRUNCATE TABLE vb_purchaseseen;
	TRUNCATE TABLE vb_refunditem;
	TRUNCATE TABLE vb_transaction;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREATE_CUS`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREATE_CUS`(cid varchar(200),cname varchar(200),cother varchar(200),ccon varchar(200),ctel1 varchar(200),ctel2 varchar(200),cemail varchar(200),caddr varchar(255),cweb varchar(200),cimg varchar(200),cgr varchar(200),cpri  varchar(200),cur varchar(200))
    DETERMINISTIC
BEGIN
	SELECT var_accid INTO @acid FROM ame_mt_acc_set WHERE var_type = "AR" AND var_groupid = "DEFAULT";
	INSERT INTO ame_mt_cus (var_id,
												var_name,
												var_name_other,
												var_name_con,
												var_tel1,
												var_tel2,
												var_email,
												var_addr,
												var_web,
												var_img,
												var_groupid,
												var_pricecode,
												var_cur,
												var_accid)
		VALUES (cid,cname,cother,ccon,ctel1,ctel2,cemail,caddr,cweb,cimg,cgr,cpri,cur,@acid);
		
	
		INSERT INTO vb_customer_conditions(vb_custId, vb_isNormalGoods, vb_isBrandName, vb_isConAvailable,vb_isConNotAvailable, vb_isCusGetProduct, vb_isCusNeedShip)
		VALUES (cid,0,0,0,0,1,0) ;

	CALL VB_PRO_MSG(1,'customer');

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREATE_CUST_BALAN`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREATE_CUST_BALAN`(cust varchar(200),custi varchar(200),dr double,cb varchar(200), trans varchar(200))
    DETERMINISTIC
BEGIN

	DECLARE balance DOUBLE DEFAULT 0;
  DECLARE des VARCHAR(255);
  DECLARE us VARCHAR(200);
  DECLARE numberpre VARCHAR(200);
  
  SET numberpre = AME_FUN_PREFIX('Card-Trans',1);

  SELECT vb_user.vb_mobile INTO @mobile FROM vb_user WHERE vb_user.CustID = custi;
  INSERT INTO tblcardtransactionhistory (TransID,CardID,Date,TransType,Amount) VALUES (numberpre,@mobile,NOW(),'Refill',dr);
	
	SET balance = VB_FUN_CUST_BAL(custi)+dr;
 
  UPDATE tblcard SET tblcard.CardBalance = balance WHERE tblcard.CustID = custi;
 
  SELECT vb_user.vb_userName INTO us FROM vb_user WHERE vb_user.CustID = custi;
  SET des = CONCAT('Respect customer: ',us,', your recharge has been credited, the amount credited: ',dr);
 
  IF trans <> '' THEN
		UPDATE vb_purchasecredit SET vb_purchasecredit.vpStatus = 'Has Been Accounted For' WHERE vb_purchasecredit.custId = custi AND vb_purchasecredit.vpTransactionNo = trans;
	END IF;
  CALL VB_PRO_NOTI_DET("NT-00002",custi,des);
  CALL VB_PRO_MSG(1,'customer balance');

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREATE_EMP`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREATE_EMP`(varid varchar(200),varname varchar(200),varnameother varchar(200),varnamecom varchar(200),vartel1 varchar(200),vartel2 varchar(200),varfax varchar(200),varweb varchar(200),varemail varchar(200),varadd varchar(200),vardesc varchar(200),vargroup varchar(200),varpost varchar(200),varpmttype varchar(200),varcard varchar(200),varcur varchar(200),varimg varchar(200),varinactive INT(1), varers INT(1))
BEGIN
	
	DECLARE veid VARCHAR(200);

	SELECT COUNT(var_id) INTO @vid FROM ame_mt_emp WHERE var_id = varid;
	IF @vid <> 0 THEN
			CALL VB_PRO_MSG(4,"employee id");
	ELSE

		IF varid = "" THEN
			SET veid = AME_FUN_PREFIX("MT-EMP",1);
		ELSE 
			SET veid = varid;
		END IF;

		INSERT INTO ame_mt_emp 
		(var_id,var_name,var_name_other,var_name_con,var_tel1,var_tel2,var_fax,var_web,var_email,var_addr,var_desc,var_groupid,var_posid,var_term,var_pmttype,var_cardid,var_cur,var_img,tin_inactive,tin_rep)
		VALUES 
		(veid,varname,varnameother,varnamecom,vartel1,vartel2,varfax,varweb,varemail,varadd,vardesc,vargroup,varpost,"",varpmttype,varcard,varcur,varimg,varinactive,varers);
	 CALL VB_PRO_MSG(1,"employee");
	END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREATE_ITEM`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREATE_ITEM`(IN cusid varchar(255),IN shipTo varchar(255),IN empID varchar(255),IN exDollar varchar(255), IN exKhmer varchar(255), IN exChina varchar(255),IN deposit varchar(255), IN balance varchar(255), IN chinaWarehouse varchar(255), IN camWarehouse varchar(255), IN noOfDay varchar(255),IN totalProductFeeINUsd varchar(255),IN totalProductFeeINYan varchar(255),IN serviceChargeUsd varchar(255),IN totalLCFeeINUsd varchar(255),IN totalINUsd DOUBLE,IN `statusitem` varchar(100),IN `type` varchar(255), IN `cart` varchar(255))
    DETERMINISTIC
BEGIN
		DECLARE NumberPre INT(11) DEFAULT 0;
    DECLARE itemcode VARCHAR(200) DEFAULT "";
		DECLARE emp VARCHAR(200) DEFAULT "";

		SELECT var_empid INTO emp FROM ame_mt_cus_emp WHERE var_cusid = cusid;

		IF cusid <> ''  THEN
      SET itemcode = VB_FUN_GENERATE_CODE(cusid);
			
			INSERT INTO vb_item (vit_custID,vit_code, vit_shipTo,vit_empID, vit_exDollar,vit_exKhmer,
													vit_exChina,vit_deposit,vit_balance,vit_chinaWarehouse,vit_camWarehouse, vit_noOfDay,
													vit_totalProductFeeINUsd,vit_totalProductFeeINYan,vit_serviceChargeUsd,
													vit_totalLCFeeINUsd,vit_totalINUsd,vit_date,vit_status,vit_type
													)
									VALUES	(cusid,itemcode,shipTo,emp,exDollar,exKhmer,
													exChina,deposit,balance,chinaWarehouse,camWarehouse,noOfDay,
													totalProductFeeINUsd,totalProductFeeINYan,serviceChargeUsd,totalLCFeeINUsd,totalINUsd
													,NOW(),`statusitem`,`type`);

			SET NumberPre = LAST_INSERT_ID();
      CALL VB_PRO_CREAT_SHIP_CUS(NumberPre,itemcode,cusid);

			IF LOWER(`type`) = "web link" THEN
					SET @SQL2 = CONCAT("DELETE FROM vb_itemcart WHERE vb_itemcart.vit_id IN (",cart,")  AND vb_itemcart.custId = '",cusid,"'; ");
					PREPARE St2 FROM @SQL2;
					EXECUTE St2;
					DEALLOCATE PREPARE St2;
			END IF;
			
			CALL VB_PRO_MSG(1,'item');
			SELECT NumberPre;
		ELSE
			CALL VB_PRO_MSG(2,'item');
		END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREATE_ITEM_CART`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREATE_ITEM_CART`(IN cusid varchar(255) , IN itemDetail text)
    DETERMINISTIC
BEGIN
		DECLARE NumberPre INT(11) DEFAULT 0;
    
		IF cusid <> '' AND itemDetail != '' THEN
			SET @SQL1 = CONCAT("INSERT INTO vb_itemcart (custId, vit_name, vit_link, vit_photo, vit_size,
														vit_color, vit_qty, vit_unitPriceYan, vit_totalProductFeeYan, vit_lcFeeYan,
														vit_chinaInvoiceCode, vit_truckingNoInChina, vit_remark, vit_unitPriceDollar, vit_amountDollar,
														vit_lcFeeDollar, vit_vendor, vit_status, vit_type, vit_document, vit_date) VALUES ", itemDetail , " ;");
			PREPARE St FROM @SQL1;
			EXECUTE St;
			DEALLOCATE PREPARE St;
			SELECT LAST_INSERT_ID();
			CALL VB_PRO_MSG(1,'item cart');
		ELSE
			CALL VB_PRO_MSG(2,'item cart');
		END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREATE_ITEM_DE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREATE_ITEM_DE`(itemDetail TEXT,item INT(11))
    DETERMINISTIC
BEGIN
			DECLARE lastid INT(11) DEFAULT 0;
			SET @SQL1 = CONCAT("INSERT INTO vb_itemdetail(vit_id, vit_name, vit_link, vit_photo, vit_size,
														vit_color, vit_qty, vit_unitPriceYan, vit_totalProductFeeYan, vit_lcFeeYan,
														vit_chinaInvoiceCode, vit_truckingNoInChina, vit_remark, vit_unitPriceDollar, vit_amountDollar,
														vit_lcFeeDollar, vit_vendor, vit_status, vit_type, vit_document, vit_date,vit_someqty) VALUES ", itemDetail , " ;");
			PREPARE St FROM @SQL1;
			EXECUTE St;
			DEALLOCATE PREPARE St;
			SET lastid = LAST_INSERT_ID();
			UPDATE vb_itemproperty SET vb_itemproperty.Vit_Id = lastid WHERE vb_itemproperty.Vit_Id = item;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREATE_ITEM_OP`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREATE_ITEM_OP`(itemid varchar(200), fieldName INT(11), valuedata varchar(200))
    DETERMINISTIC
BEGIN
			
		-- DECLARE lastId INT(11) DEFAULT 0;

		/*INSERT INTO ame_sys_of (int_line,var_datatype,var_value,var_desc,tin_ven,tin_cus,tin_emp,tin_item,tin_req)
		VALUES (1,'TEXT',fieldName,'From VBuykh',1,1,1,1,1);
		
		SET lastId = LAST_INSERT_ID();*/
		
		INSERT INTO ame_mt_of_value (int_id, var_modid,var_num, var_value) VALUES (fieldName,'MT-ITE',itemid,valuedata);

		

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREATE_ITEM_TO_AME`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREATE_ITEM_TO_AME`(IN cust varchar(255), IN orderId INT(11))
    DETERMINISTIC
BEGIN
		DECLARE pdate VARCHAR(200);

		SET @balance = VB_FUN_CUST_BAL(cust);
		SELECT vb_item.vit_totalINUsd,COALESCE(vb_item.vit_paymentDate,""),vb_item.vit_code,vb_item.vit_status INTO @netorder,pdate,@vvcode,@stat FROM vb_item WHERE vit_id = orderId;
		
		IF @balance >= @netorder THEN
				IF pdate = "" THEN
					IF @stat = 3 THEN
						CALL VB_PRO_GENERATE_ORDER(cust,orderId);
					ELSE
						CALL VB_PRO_MSG(7,'item status not invalid');
					END IF;
					
				ELSE
						SELECT "failed",CONCAT("The order with of code : ",@vvcode," has already paid.");
				END IF;
		ELSE 
			CALL VB_PRO_MSG(7,'balance');
		END IF;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREATE_PRI_COD`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREATE_PRI_COD`(IN `var_priceId` varchar(100),IN `var_priceName` varchar(100),IN `tin_isDefault` tinyint,IN `dou_serviceCharge` double)
    DETERMINISTIC
BEGIN

	SELECT SQL_CALC_FOUND_ROWS var_id INTO @var_id FROM ame_mt_pri_cod WHERE  var_id = var_priceId;
	
	IF FOUND_ROWS() > 0 THEN 
		SET @msg = "Sorry, the price code already exists!<br/> Please try a new one!";
		SET @id = "exist";
	ELSE 
		INSERT INTO ame_mt_pri_cod (var_id,var_name,tin_vb_isDefault,dou_vb_serviceCharge) VALUES(var_priceId,var_priceName,tin_isDefault,dou_serviceCharge);
		IF tin_isDefault = 1 THEN
			Update ame_mt_pri_cod SET tin_vb_isDefault = 0 WHERE var_id <> var_priceId;
		END IF;
		SET @msg = CONCAT("The price code : ",var_priceId," created successfully!");
		SET @id = "success";
	END IF;
	SELECT @msg 'msg', @id 'key';
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREATE_SALE_ORDER_AME`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREATE_SALE_ORDER_AME`(IN srefer varchar(200),IN sdate varchar(200), IN ddate varchar(200),IN cust varchar(200),IN emp varchar(200),IN total double, IN nettotal double, IN detailItem TEXT,IN orderId INT(11))
    DETERMINISTIC
BEGIN
	DECLARE NumberPre VARCHAR(200) DEFAULT "";
	SET NumberPre = AME_FUN_PREFIX("AR-SO",1);
	SELECT ame_mt_cus.var_pricecode INTO @prico FROM ame_mt_cus WHERE ame_mt_cus.var_id = cust;

	INSERT INTO ame_ar_so (var_id, var_type, var_ref, dat_date,dat_due, var_cusid, var_pricecode, var_status, var_pmtstatus, var_empid, dou_sub, dou_net, dou_rate,dou_funsub,dou_funnet,var_cur,var_claid,var_term,var_po,var_so,var_shipid,var_shipaddr,var_create,var_modify,dou_taxdol1,var_desc)
	VALUES 	(NumberPre, 'General Item Sale', srefer, sdate, ddate, cust, @prico,'Open','Unpaid',emp, total, nettotal, VB_FUN_GET_EX_RATE(),total, nettotal,"USD",'','','','','','','ADMIN','',0,'');

	SET @SQL2 = CONCAT("INSERT INTO ame_ar_so_det (var_id, int_line, var_itemid, var_uomid, var_locid, dou_qty, dou_price, dou_sub ,dou_net,dou_funprice, dou_funsub, dou_funnet, int_poline, var_claid, var_desc) VALUES ",detailItem," ;");
	SET @SQL2 = REPLACE(@SQL2, 'tempid', NumberPre);
	PREPARE St2 FROM @SQL2;
	EXECUTE St2;
	DEALLOCATE PREPARE St2;


		SELECT vb_mobile INTO @mob FROM vb_user WHERE CustID = cust;
		SELECT vb_item.vit_totalINUsd INTO @totalUSD FROM	vb_item WHERE vb_item.vit_id = orderId LIMIT 1;
		SET @balance = VB_FUN_CUST_BAL(cust)-@totalUSD;

		-- POS 
		INSERT INTO tblcardtransactionhistory (TransID,CardID,Date,TransType,Amount,SalID) VALUES (AME_FUN_PREFIX('Card-Trans',1),@mob,NOW(),'Payment',@totalUSD,srefer);
		UPDATE tblcard SET tblcard.CardBalance = @balance WHERE tblcard.CustID = cust;

    UPDATE vb_item SET vb_item.vit_salID = NumberPre WHERE vb_item.vit_id = orderId;
    CALL VB_PRO_GENERATE_RECEIPT(srefer,cust,@totalUSD,orderId);
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREATE_USER_FOR_CUS`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREATE_USER_FOR_CUS`(IN `custId` varchar(11),IN `passwd` varchar(255),IN `empId` varchar(11))
BEGIN
	INSERT 
		INTO vb_user(vb_userName, vb_passwd, CustID, vb_empId, vb_role, vb_isEnable, vb_createDate) 
		VALUES (custId,passwd,custId,empId,'USER',1,NOW());
	INSERT 
		INTO tblcard (CardID,CustID,CardTypeID,CardBalance,CardDiscount,StartDate,ExpireDate,IsCreditReset,IsLimited,IsValid,IsDismiss,IssuedBy,PinNo,IssuedDate) 
		VALUES(custId,custId,(SELECT CustCard FROM vb_defaultcustomer LIMIT 1),0,0,NOW(),NOW(),0,0,1,0,empId,'',NOW());
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREAT_NOTI_DET`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREAT_NOTI_DET`(IN notification varchar(200),IN cust varchar(200), IN id_notification varchar(255), IN param4 varchar(255), IN param5 varchar(200))
    DETERMINISTIC
BEGIN

	DECLARE TempID VARCHAR(255) DEFAULT('');
	SET TempID = VB_FUN_MEG_NOTI(id_notification,param4,param5);
	INSERT INTO vb_notification_detail (cnid,cncustid,cndescription,cndate,cnseen) VALUES (notification ,cust ,TempID,NOW(),0);
	CALL VB_PRO_MEG(1,'notification');
	
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CREAT_SHIP_CUS`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CREAT_SHIP_CUS`(item int(11),itemcode varchar(200),cust varchar(200))
    DETERMINISTIC
BEGIN
		
		DECLARE cns INT(1);
		DECLARE cgp INT(1);
    DECLARE pre VARCHAR(200);

		SET pre = VB_FUN_GET_PREFIX('ShipLocation');

		SELECT LocationID INTO @loca FROM vb_location LIMIT 1;
		INSERT INTO vb_customershipaddress (TransID,ItemId,ItemCode,CustId,Location,Date) VALUES (pre,item,itemcode,cust,@loca,NOW());

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CUST_CREAT_ITE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CUST_CREAT_ITE`(IN orderid INT(11), IN custid varchar(200))
    DETERMINISTIC
BEGIN
	DECLARE NumberPre VARCHAR(200) DEFAULT "";
	DECLARE done INT DEFAULT FALSE;
	DECLARE getitemiddetail VARCHAR(255);
	DECLARE getitemname VARCHAR(255);
	DECLARE getitemlink VARCHAR(255);
	DECLARE getitemsize VARCHAR(255);
	DECLARE getitemcolor VARCHAR(255);
	DECLARE getitemqty VARCHAR(255);
	DECLARE getitemunitprice VARCHAR(255);
	DECLARE getitemtotalfeeyan VARCHAR(255);
	DECLARE getitemlcfeeyan VARCHAR(255);
	DECLARE codeitem varchar(255) DEFAULT "";



	DECLARE cur CURSOR FOR SELECT vit_iddetail, vit_name ,vit_link, vit_size, vit_color, vit_qty, vit_unitPriceDollar, vit_totalProductFeeYan, vit_lcFeeYan FROM vb_itemdetail WHERE vit_id = orderid;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;



	OPEN cur;
	read_loop1: LOOP
    FETCH cur INTO getitemiddetail,getitemname,getitemlink,getitemsize,getitemcolor,getitemqty,getitemunitprice,getitemtotalfeeyan,getitemlcfeeyan;
    IF done THEN
      LEAVE read_loop1;
    END IF;
		IF !ISNULL(getitemiddetail) THEN
				-- ,'",codeitem,"','",getitemname,"','",getitemsize,"','",getitemcolor,"','",getitemqty,"','",getitemunitprice,"','",getitemtotalfeeyan,"','",getitemlcfeeyan,"','',''
					SELECT vb_item.vit_code INTO codeitem FROM vb_item  WHERE vb_item.vit_id = orderid;
					SET NumberPre = vb_itemid(custid);
					SET @SQL1 = CONCAT("INSERT INTO ame_mt_ite (var_id, var_type, var_name, tin_vat, dou_tax, dou_order_pt, dou_order_qty, var_groupid, var_uomid,var_desc, var_img, tin_no_sal)
															VALUES ('tempid','Inventory Part','",getitemlink,"',0,0,0,0,'",custid,"','EA','','',1)");
					SET @SQL1 = REPLACE(@SQL1, 'tempid', NumberPre);
					PREPARE St FROM @SQL1;
					EXECUTE St;
					DEALLOCATE PREPARE St;
					
				

					SET @SQL2 = CONCAT("INSERT INTO ame_mt_ite_uom (var_id, var_uomid, int_line, dou_conv, tin_pur, tin_sal, tin_ship) VALUES ('tempid','EA', 0, 1, 1,1,1);");
					SET @SQL2 = REPLACE(@SQL2, 'tempid', NumberPre);
					PREPARE St2 FROM @SQL2;
					EXECUTE St2;
					DEALLOCATE PREPARE St2;

		END IF;
  END LOOP;
	CLOSE cur;




END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CUST_CREAT_ITE_GRO`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CUST_CREAT_ITE_GRO`(IN cid varchar(255),IN cname varchar(255))
    DETERMINISTIC
BEGIN
	INSERT INTO  ame_mt_ite_gro (var_id, var_name, var_desc,tin_inactive) VALUES (cid,cname,'FROM Vbuy',0);
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CUST_DEL_SHIP_CON`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CUST_DEL_SHIP_CON`(cust varchar(200))
    DETERMINISTIC
BEGIN

	SET @SQL1 = CONCAT("DELETE FROM vb_customer_conditions WHERE vb_custId = '",cust,"' ;");
	PREPARE St FROM @SQL1;
	EXECUTE St;
	DEALLOCATE PREPARE St;

	SET @SQL2 = CONCAT("DELETE FROM ame_mt_ship_addr WHERE var_num = '",cust,"' ;");
	PREPARE St1 FROM @SQL2;
	EXECUTE St1;
	DEALLOCATE PREPARE St1;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_CUS_CANCEL_ORD`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_CUS_CANCEL_ORD`(id varchar(200), cust varchar(200), po varchar(200))
    DETERMINISTIC
BEGIN

	SELECT vit_status INTO @stat FROM vb_item WHERE vit_id = id AND vit_custID = cust;

	IF @stat = 1 OR @stat = 2 OR @stat = 3 THEN
			UPDATE vb_item SET vit_status = 13, vit_ordertype = 4 WHERE vit_id = id AND vit_custID = cust;
			 CALL VB_PRO_MSG(8,CONCAT(" PO/Number ",po));
	ELSE 
		 CALL VB_PRO_MSG(2,CONCAT(" PO/Number ",po));
	END IF;
	

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_DASH`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_DASH`(IN cust varchar(200))
    DETERMINISTIC
BEGIN
	DECLARE n INT(11) DEFAULT 0;
  DECLARE c INT(11) DEFAULT 0;

	SELECT  COUNT(vb_notification_detail.cncustid) INTO n 
	FROM vb_notification_detail  
	WHERE vb_notification_detail.cncustid = cust  
				AND vb_notification_detail.CNSeen = 0; 

	SELECT COUNT(vb_itemcart.custId) 
		INTO c FROM vb_itemcart  
		WHERE custId = cust;
	-- SELECT COUNT(tblcard.CardBalance) INTO b FROM tblcard  WHERE tblcard.CustID = cust; 
	CALL VB_PRO_UPDATE_EXP_DATE_ITEM(cust);
	SELECT n,c,VB_FUN_CUST_BAL(cust);
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_DELETE_EMP`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_DELETE_EMP`(varid varchar(200))
BEGIN

DELETE FROM ame_mt_emp WHERE ame_mt_emp.var_id = varid;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_EXC_RAT_LIST`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_EXC_RAT_LIST`(IN `pageNum` int(11),IN `numRow` int(11))
    DETERMINISTIC
BEGIN
DECLARE page INT DEFAULT 0;

SELECT COUNT(ex.Date) INTO @rowCount from vb_exchangerate ex;

SET @totalPage = CEIL(@rowCount / numRow);

SET page = (pageNum - 1) * numRow;
SELECT 
	DATE_FORMAT(vb_ex.Date,'%d-%b-%Y') 'date',
	vb_ex.Dollar 'dollar', 
	vb_ex.Khmer 'khmer', 
	vb_ex.China 'chinese', 
	vb_ex.active 'active', 
	(SELECT CONCAT('[',var_id,'] ',var_name) FROM ame_mt_emp WHERE var_id = (SELECT vb_empId FROM vb_user WHERE vb_userId = vb_ex.cby)) 'createBy' 
FROM vb_exchangerate vb_ex ORDER BY DATE(vb_ex.Date) DESC, vb_ex.active DESC LIMIT numRow OFFSET page;
SELECT @totalPage 'totalPage',@rowCount 'totalRows';
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_FIND_ITE_DET_BY_DET_ID`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_FIND_ITE_DET_BY_DET_ID`(IN `detailId` int(11))
    DETERMINISTIC
BEGIN
	-- SELECT ImageGetWeb INTO @pathImg from tblconfiguration LIMIT 1;
	SELECT COALESCE(var_name,"") INTO @pathImg  FROM ame_sys_config WHERE var_id = "IMG_GE_WEB";
	SELECT
		vid.vit_iddetail 'vitDetailId',
		vid.vit_link 'vitLink',
		vid.vit_photo 'vitPhoto',
		vid.vit_size 'vitSize',
		vid.vit_color 'vitColor',
		vid.vit_qty 'vitQty',
		vid.vit_unitPriceYan 'vitUnitPriceYuan',
		vid.vit_unitPriceDollar 'vitUnitPriceUSD',
		vid.vit_totalProductFeeYan 'vitTotalProFeeYuan',
		vid.vit_lcFeeYan 'vitLCFeeYuan',
		vid.vit_lcFeeDollar 'vitLCFeeDollar',
		vid.vit_chinaInvoiceCode 'vitChinaInvCode',
		vid.vit_truckingNoInChina 'vitTrackNoInChina',
		vid.vit_remark 'remark',
		vid.vit_amountDollar 'vitAmtUSD',
		IF(vid.vit_document = '','',CONCAT(@pathImg,'Customer/',(SELECT vb_item.vit_custID FROM vb_item WHERE vb_item.vit_id = vid.vit_id),'/document/',vid.vit_document)) 'vitDocument',
		vid.vit_vendor 'vitVender',
		vid.vit_someqty 'vitSomeQty'
	FROM vb_itemdetail vid WHERE vid.vit_iddetail = detailId;

	SELECT ProTitle 'title',ProName 'name',ProKey 'key',COALESCE(ProImage,'') 'image' FROM vb_itemproperty WHERE Vit_Id = detailId;
	
	SELECT COALESCE(vb_defaultcustomer.VenLC,""),COALESCE(vb_defaultcustomer.VenService,"") INTO @vdl,@vds FROM vb_defaultcustomer LIMIT 1;

	SELECT var_id 'venderId', var_name 'venderName' from ame_mt_ven WHERE var_id <> @vdl AND var_id <> @vds;
	
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_FIND_ITE_ORD_DET_BY_ID`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_FIND_ITE_ORD_DET_BY_ID`(IN `vitId` int(11),IN `userId` int(11))
    DETERMINISTIC
BEGIN
	DECLARE checked BOOL DEFAULT FALSE;

	SELECT vb_roleType INTO @roleType FROM vb_user_role WHERE vb_roleId = (SELECT vb_admin_role FROM vb_user WHERE vb_userId = userId);

	SELECT China, Dollar, Khmer INTO @CNY, @USD, @RIEL FROM vb_exchangerate WHERE active = 1;
	
	SELECT
		vi.vit_id 'vitId',
		tblc.var_id 'custId',
		tblc.var_name 'custName',
		IF(COALESCE(vi.vit_priceCode,'') = '',pc.var_id, vi.vit_priceCode) 'vitPriceCode',
		IF(COALESCE(vi.vit_priceCode,'') = '',COALESCE(pc.var_name,''),(SELECT COALESCE(pr.var_name,'') FROM ame_mt_pri_cod pr WHERE pr.var_id = vi.vit_priceCode)) 'vitPriceDes',
		pc.dou_vb_serviceCharge 'pcServiceCharge',
		vi.vit_code 'vitCode',
		VB_FUN_CUST_BAL(vi.vit_custID) 'vitCustBalance',
		vi.vit_totalINUsd 'vitTotalAmtUSD',
		vi.vit_totalLCFeeINUsd 'vitTotalLCFeeUSD',
		vi.vit_totalProductFeeINUsd 'vitTotalProFeeUSD',
		vi.vit_totalProductFeeINYan 'vitTotalProFeeCNY',
		vi.vit_serviceChargeUsd 'vitServiceChargeUSD',
		COALESCE(vi.vit_camWarehouse,'') 'vitCamWH',
		IF(vi.vit_camWarehouse IS NULL,'',(SELECT loc.var_name FROM ame_mt_loc loc WHERE loc.var_id = vi.vit_camWarehouse)) 'vitCamWHName',
		COALESCE(vi.vit_chinaWarehouse,'') 'vitChinaWH',
		IF(vi.vit_chinaWarehouse is NULL,'',(SELECT loc.var_name FROM ame_mt_loc loc WHERE loc.var_id = vi.vit_camWarehouse)) 'vitChinaWHName',
		vi.vit_noOfDay 'noOfDays',
		COALESCE(vi.vit_empID,'') 'empId',
		(SELECT var_name FROM ame_mt_emp WHERE var_id = vi.vit_empID LIMIT 1) 'empName',
		vi.vit_exChina 'vitExChina',
		vi.vit_exKhmer 'vitExKhmer',
		vi.vit_exDollar 'vitExDollar',
		@CNY 'exChina',
		@USD 'exDollar',
		@RIEL 'exKhmer',
		vi.vit_status 'vitStatus',
		vi.vit_type 'vitType',
		vi.vit_pmtExpireHours 'vitExpireHours',
		(SELECT SName from vb_status WHERE SID = vi.vit_status) 'vitStatusName',
		vi.vit_dateAvailable 'vitDateAvialable',
    COALESCE((SELECT ItemAvailable FROM vb_defaultcustomer LIMIT 1),"") 'vitDateAvilableNum',
		COALESCE((SELECT var_status FROM ame_ar_py WHERE ame_ar_py.var_id = vi.vit_recID),'') 'postStatus',
		COALESCE(vi.vit_salID,'') 'vitSalId',COALESCE(vi.vit_recID,'') 'vitRecId',
		@roleType 'roleType',
		vi.vit_ordertype 'vitOrderType'
	from vb_item vi 
		INNER JOIN (SELECT var_id, var_name, var_pricecode FROM ame_mt_cus) AS tblc ON tblc.var_id = vi.vit_custID 
		INNER JOIN ame_mt_pri_cod pc ON tblc.var_pricecode = pc.var_id
	WHERE vi.vit_id = vitId;



	SELECT
		vid.vit_iddetail 'vitDetailId',
		vid.vit_link 'vitLink',
		vid.vit_photo 'vitPhoto',
		vid.vit_size 'vitSize',
		vid.vit_color 'vitColor',
		vid.vit_qty 'vitQty',
		vid.vit_unitPriceYan 'vitUnitPriceYuan',
		vid.vit_unitPriceDollar 'vitUnitPriceUSD',
		vid.vit_totalProductFeeYan 'vitTotalProFeeYuan',
		vid.vit_lcFeeYan 'vitLCFeeYuan',
		vid.vit_lcFeeDollar 'vitLCFeeDollar',
		vid.vit_chinaInvoiceCode 'vitChinaInvCode',
		vid.vit_truckingNoInChina 'vitTrackNoInChina',
		vid.vit_remark 'remark',
		vid.vit_amountDollar 'vitAmtUSD',
		vid.vit_document 'vitDocument',
		COALESCE(vid.vit_vendor,'') 'vitVender',
    vid.vit_type 'vitType',
		COALESCE(vid.vit_someqty,0) 'vitSomeQty',
		COALESCE(vid.vit_chinaInvoiceCode,'') 'vitChinaInvCode',
		COALESCE(vid.vit_truckingNoInChina,'') 'vitTrackNoInChina',
		checked 'checked',
		COALESCE(vid.vit_itemId,'') 'vitItemId',
		vid.vit_refund 'vitRefund'
	FROM vb_itemdetail vid WHERE vid.vit_id = vitid ORDER BY vid.vit_iddetail ASC;

	SELECT SID 'statusId', SName 'statusName' from vb_status ORDER BY SID ASC;
	SELECT TID 'typeId', TName 'typeName' FROM vb_itemtype;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_FIND_SO_DET_BY_ID`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_FIND_SO_DET_BY_ID`(IN `soId` varchar(100),IN `itemId` varchar(100))
    DETERMINISTIC
BEGIN
	
	SELECT 
		det.var_id 'saleId',
		det.dou_qty 'saleQty',
		COALESCE(det.var_vb_meatype,'') 'measureType',
		COALESCE(ite.var_itemcode,'') 'itemCode',
		COALESCE(det.dou_vb_meavalue,0) 'measureValue',
		COALESCE(det.dou_vb_meaprice,0) 'measurePrice',
		COALESCE(det.dou_vb_availqty,0) 'availableQty',
		ite.var_id 'itemId'
	FROM 
		ame_ar_so_det det 
	INNER JOIN (SELECT var_id, var_name,var_itemcode,tin_inactive,tin_no_sal FROM ame_mt_ite) ite 
		ON det.var_itemid = ite.var_id
	WHERE det.var_id = soId AND ite.var_id = itemId AND ite.tin_inactive = 0 AND ite.tin_no_sal = 0;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GENERATE_ORDER`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GENERATE_ORDER`(IN cust varchar(255), IN orderId INT(11))
    DETERMINISTIC
BEGIN
		DECLARE NumberPre VARCHAR(200) DEFAULT "";
   	DECLARE doneItem INT DEFAULT FALSE;
		DECLARE item VARCHAR(255) DEFAULT "";
		DECLARE dateTran VARCHAR(255) DEFAULT "";
		DECLARE empSelect VARCHAR(300) DEFAULT "";
		DECLARE itemNetTotal DOUBLE DEFAULT 0;
		DECLARE detailItem Text DEFAULT '';
		DECLARE sumUnitLC DOUBLE DEFAULT 0;
		DECLARE locatdefa VARCHAR(255) DEFAULT "";

		DECLARE link Text DEFAULT "";
		DECLARE size VARCHAR(255) DEFAULT "";
		DECLARE color VARCHAR(255) DEFAULT "";
		DECLARE qty INT(11) DEFAULT 0;	
		DECLARE lc DOUBLE DEFAULT 0;
		DECLARE amt DOUBLE DEFAULT 0;
		DECLARE pho VARCHAR(255) DEFAULT "";
		DECLARE unit DOUBLE DEFAULT 0;
		DECLARE remark VARCHAR(255) DEFAULT "";
		DECLARE nameItem VARCHAR(255) DEFAULT "";
		DECLARE num INT(11) DEFAULT 0;	
		DECLARE ven VARCHAR(255) DEFAULT "";
    DECLARE dorsa DOUBLE DEFAULT 0;
		DECLARE idDetail INT(11) DEFAULT 0;

		DECLARE lcSql TEXT DEFAULT "";
		DECLARE serSql TEXT DEFAULT "";
		DECLARE listItem TEXT DEFAULT "";

		DECLARE cur2 CURSOR FOR SELECT vit_link,COALESCE(vit_size,""),COALESCE(vit_color,""),vit_qty,vit_unitPriceDollar,vit_lcFeeDollar,vit_amountDollar,COALESCE(vit_photo,''),COALESCE(vit_remark,""),COALESCE(vit_name,""),COALESCE(vit_vendor,""),
		(SELECT vb_item.vit_code FROM vb_item WHERE vb_item.vit_id = orderId LIMIT 1),
		(SELECT vb_item.vit_empID FROM vb_item WHERE vb_item.vit_id = orderId LIMIT 1),
		(SELECT vb_item.vit_totalINUsd FROM	vb_item WHERE vb_item.vit_id = orderId LIMIT 1),
    (SELECT vb_item.vit_camWarehouse FROM vb_item WHERE vb_item.vit_id = orderId LIMIT 1),
		 vit_iddetail
		 FROM vb_itemdetail WHERE vit_id = orderId AND vit_id  = (SELECT vb_item.vit_id FROM vb_item WHERE vb_item.vit_custID = cust AND vb_item.vit_id = orderId)  ORDER BY vit_id DESC;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneItem = TRUE;
	
		IF cust <> '' THEN

				OPEN cur2;
					read_loop1: LOOP
					FETCH cur2 INTO link,size,color,qty,unit,lc,amt,pho,remark,nameItem,ven,item,empSelect,itemNetTotal,locatdefa,idDetail;
					IF doneItem THEN
						LEAVE read_loop1;
					END IF;
					IF !ISNULL(link) THEN
						SET NumberPre = VB_FUN_GEN_PREFIX(cust);

						
						SET @SQL1 = CONCAT("INSERT INTO ame_mt_ite (var_id, var_type, var_name, tin_vat, dou_order_pt, dou_order_qty, var_groupid, var_uomid,var_desc, var_img, tin_no_sal, var_venid, int_vb_orderId)
															VALUES ('",NumberPre,"','Inventory Part','",link,"',0,0,0,'",cust,"','EA','','',0,'",ven,"',",orderId,")");
						PREPARE St FROM @SQL1;
						EXECUTE St;
						DEALLOCATE PREPARE St;
						SET @SQL2 = CONCAT("INSERT INTO ame_mt_ite_uom (var_id, var_uomid, int_line, dou_conv, tin_pur, tin_sal) VALUES ('",NumberPre,"','EA', 0, 1, 1,1);");
						PREPARE St2 FROM @SQL2;
						EXECUTE St2;
						DEALLOCATE PREPARE St2;
						
						CALL VB_PRO_CREATE_ITEM_OP(NumberPre,1,orderId);
						CALL VB_PRO_CREATE_ITEM_OP(NumberPre,2,item);
						CALL VB_PRO_CREATE_ITEM_OP(NumberPre,3,qty);
						CALL VB_PRO_CREATE_ITEM_OP(NumberPre,4,lc);
						CALL VB_PRO_CREATE_ITEM_OP(NumberPre,5,idDetail);
						
						INSERT INTO ame_mt_ite_pri (var_id, var_pricecode, var_uomid, var_cur, int_line, dou_price) VALUES (NumberPre,VB_FUN_GET_CUS_CODE(cust),'EA',"USD",1,unit);
						
						SET @SQL3 = CONCAT("UPDATE vb_itemdetail SET vit_itemId = '",NumberPre,"' WHERE vit_iddetail = ",idDetail,";");
						PREPARE St3 FROM @SQL3;
						EXECUTE St3;
						DEALLOCATE PREPARE St3;
						
					
						SET dorsa = qty * unit;
						SET num = num +1;
						SET detailItem = CONCAT(detailItem,"('tempid',",num,",'",NumberPre,"','Ea','",locatdefa,"','",qty,"',",unit,",",dorsa,",",dorsa,",",unit,",",dorsa,",",dorsa,",",num,",'',''),");
						SET listItem = CONCAT(listItem,"'",NumberPre,"',");
					END IF;
				END LOOP;
				CLOSE cur2;
		
			SELECT COALESCE(VenLC,""),COALESCE(VenService,"") INTO @venlc,@venser FROM vb_defaultcustomer LIMIT 1;
			
			 -- Open Item LC
			SET @lcNum = VB_FUN_GEN_PREFIX(cust);
			SET num = num +1;
			SELECT COALESCE(vit_totalLCFeeINUsd,"") 'Lc',COALESCE(vit_camWarehouse,"") "cam" INTO @tolc,@camw FROM vb_item WHERE vit_id = orderId;
			SET @SQL6 = CONCAT("INSERT INTO ame_mt_ite (var_id, var_type, var_name, tin_vat, dou_order_pt, dou_order_qty, var_groupid, var_uomid,var_desc, var_img, tin_no_sal, var_venid, int_vb_orderId) VALUES ('",@lcNum,"','Inventory Part','",'Total LC USD',"',0,0,0,'",cust,"','EA','','',1,'",@venlc,"',",orderId,")");
			PREPARE St6 FROM @SQL6;
			EXECUTE St6;
			DEALLOCATE PREPARE St6;
			SET @SQL5 = CONCAT("INSERT INTO ame_mt_ite_uom (var_id, var_uomid, int_line, dou_conv, tin_pur, tin_sal) VALUES ('",@lcNum,"','EA', 0, 1, 1,1);"); 
			PREPARE St5 FROM @SQL5;
			EXECUTE St5;
			DEALLOCATE PREPARE St5;
			INSERT INTO ame_mt_ite_pri (var_id, var_pricecode, var_uomid, var_cur, int_line, dou_price) VALUES (@lcNum,VB_FUN_GET_CUS_CODE(cust),'EA',"USD",1,@tolc);
			SET detailItem = CONCAT(detailItem,"('tempid',",num,",'",@lcNum,"','Ea','",@camw,"',1,",@tolc,",",@tolc,",",@tolc,",",@tolc,",",@tolc,",",@tolc,",",num,",'',''),");
			SET listItem = CONCAT(listItem,"'",@lcNum,"',");
		  -- Close Item LC


			-- OPEN Item SERV
			SET @serNum = VB_FUN_GEN_PREFIX(cust);
      SELECT COALESCE(vit_serviceChargeUsd,"") "service",COALESCE(vit_camWarehouse,"") "cam" INTO @toServ,@cam FROM vb_item WHERE vit_id = orderId;
			SET @SQL8 = CONCAT("INSERT INTO ame_mt_ite (var_id, var_type, var_name, tin_vat, dou_order_pt, dou_order_qty, var_groupid, var_uomid,var_desc, var_img, tin_no_sal, var_venid, int_vb_orderId)	VALUES ('",@serNum,"','Inventory Part','",'Service charge Total USD',"',0,0,0,'",cust,"','EA','','',1,'",@venser,"',",orderId,")");
			PREPARE St8 FROM @SQL8;
			EXECUTE St8;
			DEALLOCATE PREPARE St8;
			SET num = num +1;
			SET @SQL7 = CONCAT("INSERT INTO ame_mt_ite_uom (var_id, var_uomid, int_line, dou_conv, tin_pur, tin_sal) VALUES ('",@serNum,"','EA', 0, 1, 1,1);"); 
			PREPARE St7 FROM @SQL7;
			EXECUTE St7;
			DEALLOCATE PREPARE St7;
			INSERT INTO ame_mt_ite_pri (var_id, var_pricecode, var_uomid, var_cur, int_line, dou_price) VALUES (@serNum,VB_FUN_GET_CUS_CODE(cust),'EA',"USD",1,@toServ);
			SET detailItem = CONCAT(detailItem,"('tempid',",num,",'",@serNum,"','Ea','",@cam,"',1,",@toServ,",",@toServ,",",@toServ,",",@toServ,",",@toServ,",",@toServ,",",num,",'',''),");
			SET listItem = CONCAT(listItem,"'",@serNum,"',");

			-- CLOSE Item SERV

			SET detailItem = SUBSTR(detailItem FROM 1 FOR LENGTH(detailItem)-1);
			SET listItem = SUBSTR(listItem FROM 1 FOR LENGTH(listItem)-1);
	
			SET dateTran = NOW();
			CALL VB_PRO_CREATE_SALE_ORDER_AME(item,dateTran,dateTran,cust,empSelect,itemNetTotal,itemNetTotal,detailItem,orderId);
			CALL VB_PRO_PUR_ORDER_AME(cust,orderId,listItem);
			UPDATE vb_item SET vit_status = 5, vit_paymentDate = NOW() WHERE vit_id = orderId AND vit_custID = cust;
			CALL VB_PRO_MEG(1,'submit order');


		ELSE
			CALL VB_PRO_MEG(2,'submit order');
		END IF;
		
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GENERATE_RECEIPT`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GENERATE_RECEIPT`(vbCode varchar(200),cust varchar(200), NetAmt double, vid INT(11))
    DETERMINISTIC
BEGIN
		DECLARE prefix VARCHAR(200);
		DECLARE module VARCHAR(200);
		DECLARE accCash VARCHAR(200);
		DECLARE accID VARCHAR(200);
		DECLARE empstr VARCHAR(200);

		SET empstr = "";
    SELECT COALESCE(vb_defaultcustomer.RecModule,''),COALESCE(vb_defaultcustomer.AccountCash,''),COALESCE(vb_defaultcustomer.AccountID,'') INTO module,accCash,accID FROM vb_defaultcustomer LIMIT 1;
    SET prefix = AME_FUN_PREFIX_CUSTOM(module,"AR-PY",1);

	
	 INSERT INTO ame_ar_py (var_id, var_type, var_accid, var_cusid ,var_status,var_pmtstatus,var_ref,var_prefix, var_create,
												dat_date, dat_create, dou_amt, dou_net,var_cur,dou_rate,dou_funamt,dou_funnet,dou_funar,var_claid,var_desc,var_pmttype,var_pmtref) 
		VALUES (prefix, 'Prepayment', accCash, cust, "Open", "Unpaid", vbCode, "DEFAULT", "ADMIN", NOW(), NOW(), NetAmt, NetAmt, "USD", VB_FUN_GET_EX_RATE(), NetAmt, NetAmt, NetAmt,empstr,empstr,empstr,empstr);


		INSERT INTO ame_ar_py_det (var_id,int_line,var_docid,dou_amt,dou_funamt,var_claid,var_desc) VALUES (prefix,1, accID, NetAmt, NetAmt,empstr,empstr);
	  UPDATE vb_item SET vb_item.vit_recID = prefix WHERE vb_item.vit_id = vid;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GENERATE_SHIP`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GENERATE_SHIP`(varid VARCHAR(200),vartype VARCHAR(200),verref VARCHAR(200),datdate VARCHAR(200),varcusId VARCHAR(200), varpricecode VARCHAR(200),varstatus VARCHAR(200),varempid VARCHAR(200),dousub DOUBLE,doufundub DOUBLE,dounet DOUBLE,doufunnet DOUBLE,doutaxdol1 DOUBLE,datdue VARCHAR(200),varclaid VARCHAR(200),varshipid VARCHAR(200),varshipaddress VARCHAR(200),verterm VARCHAR(200),varprefix VARCHAR(200),varpmtstatus VARCHAR(200),vbCode varchar(200),cust varchar(200),saleId varchar(200))
    DETERMINISTIC
BEGIN
		DECLARE prefix VARCHAR(200);
		DECLARE prefix1 VARCHAR(200);
		DECLARE module VARCHAR(200);
		DECLARE accCash VARCHAR(200);
		DECLARE accID VARCHAR(200);
		DECLARE empstr VARCHAR(200);
		DECLARE cusbalan DOUBLE;
		DECLARE kgCou DOUBLE;
		DECLARE kgPri DOUBLE;
		DECLARE kgCou1 INT(11);
		DECLARE cbmCou1 INT(11);
		DECLARE cbmCou DOUBLE;
		DECLARE cbmPri DOUBLE;

		DECLARE sumKg DOUBLE;
		DECLARE sumCbm DOUBLE;

		DECLARE totalAmt DOUBLE;


		SELECT SUM(dou_vb_meavalue),dou_vb_meaprice INTO kgCou,kgPri FROM ame_ar_so_det WHERE var_id = saleId AND LOWER(var_vb_meatype) = "kg" GROUP BY var_vb_meatype;
		SELECT SUM(dou_vb_meavalue),dou_vb_meaprice INTO cbmCou,cbmPri FROM ame_ar_so_det WHERE var_id = saleId AND LOWER(var_vb_meatype) = "cbm" GROUP BY var_vb_meatype;
		 
		SET kgCou = COALESCE(kgCou,0);
		SET cbmCou = COALESCE(cbmCou,0);
		SET kgCou = VB_FUN_ROUND_UP(kgCou);
		SET sumKg = COALESCE(kgCou,0) * COALESCE(kgPri,0);
		SET sumCbm = COALESCE(cbmCou,0) * COALESCE(cbmPri,0);
		SET totalAmt = sumKg + sumCbm;
		SET totalAmt = COALESCE(totalAmt,0);



		SET @priBalanc = VB_FUN_CUST_BAL(cust);

		IF @priBalanc < totalAmt THEN
			CALL VB_PRO_MEG(7," balance customer ");
		ELSE 
				SET cusbalan = @priBalanc - totalAmt;
				SET @SQL2 = CONCAT("UPDATE tblcard SET tblcard.CardBalance = ",cusbalan," WHERE tblcard.CustID = '",cust,"' ;");
				PREPARE St2 FROM @SQL2;
				EXECUTE St2;
				DEALLOCATE PREPARE St2;
				
				SELECT vb_user.vb_mobile INTO @mobile FROM vb_user WHERE vb_user.CustID = cust;
				INSERT INTO tblcardtransactionhistory (TransID,CardID,Date,TransType,Amount,SalID) VALUES (AME_FUN_PREFIX("Card-Trans",1),@mobile,NOW(),'Payment',FORMAT(totalAmt,2),vbCode);

				INSERT INTO vb_notification_detail (CNID,CNCustID,CNDescription,CNDate,CNSeen) VALUES ("NT-00002",cust,CONCAT("You was pay for shipment OF Code :",vbCode,". amount :",FORMAT(totalAmt,2),""),NOW(),0);
							

				SET empstr = "";
				SELECT COALESCE(vb_defaultcustomer.RecModule,''),COALESCE(vb_defaultcustomer.AccountCash,''),COALESCE(vb_defaultcustomer.AccountID,'') INTO module,accCash,accID FROM vb_defaultcustomer LIMIT 1;
				SET prefix = AME_FUN_PREFIX_CUSTOM(module,"AR-PY",1);
			
				INSERT INTO ame_ar_py (var_id, var_type, var_accid, var_cusid ,var_status,var_pmtstatus,var_ref,var_prefix, var_create,
															dat_date, dat_create, dou_amt, dou_net,var_cur,dou_rate,dou_funamt,dou_funnet,dou_funar,var_claid,var_desc,var_pmttype,var_pmtref) 
											VALUES (prefix, 'Prepayment', accCash, cust, "Open", "Unpaid", vbCode, "DEFAULT", "ADMIN", 
															NOW(), NOW(), totalAmt, totalAmt, "USD", 1, totalAmt, totalAmt, totalAmt,empstr,empstr,empstr,empstr);
				INSERT INTO ame_ar_py_det (var_id,int_line,var_docid,dou_amt,dou_funamt,var_claid,var_desc) VALUES (prefix,1, accID, totalAmt, totalAmt,empstr,empstr);


				SET prefix1 = AME_FUN_PREFIX_CUSTOM(varid,"AR-IN",1);

				Insert into ame_ar_in (var_id, var_type, var_ref, dat_date, var_cusid, var_pricecode, var_status, var_empid, dou_sub, dou_funsub, dou_net, dou_funnet, dou_taxdol1,
														dat_due, var_claid, var_shipid, var_shipaddr, var_term, var_prefix, var_pmtstatus,dat_create,var_cur,var_desc)
											VALUES(prefix1, vartype, verref, datdate, cust, varpricecode, varstatus, varempid, totalAmt, totalAmt, totalAmt, totalAmt, doutaxdol1,
														datdue, varclaid, varshipid, varshipaddress, verterm, varprefix, varpmtstatus,NOW(),'USD',prefix);
							
				SELECT ame_ar_so_det.var_locid INTO @locationIdToShip FROM ame_ar_so_det WHERE ame_ar_so_det.var_id = saleId LIMIT 1;

				IF kgCou <> 0 THEN
							SET @serNum = VB_FUN_GEN_PREFIX(cust);
							SET @SQL8 = CONCAT("INSERT INTO ame_mt_ite (var_id, var_type, var_name, tin_vat, dou_order_pt, dou_order_qty, var_groupid, var_uomid,var_desc, var_img, tin_no_sal, var_venid)	VALUES ('",@serNum,"','Non-Inventory Part','",'Total Shipment KG',"',0,0,0,'",cust,"','EA','','',1,'')");
							PREPARE St8 FROM @SQL8;
							EXECUTE St8;
							DEALLOCATE PREPARE St8;
							SET @SQL7 = CONCAT("INSERT INTO ame_mt_ite_uom (var_id, var_uomid, int_line, dou_conv, tin_pur, tin_sal) VALUES ('",@serNum,"','EA', 0, 1, 1,1);"); 
							PREPARE St7 FROM @SQL7;
							EXECUTE St7;
							DEALLOCATE PREPARE St7;
							INSERT INTO ame_mt_ite_pri (var_id, var_pricecode, var_uomid, var_cur, int_line, dou_price) VALUES (@serNum,VB_FUN_GET_CUS_CODE(cust),'EA',"USD",1,sumKg);
							
							
							INSERT INTO ame_ar_in_det (var_id, int_line, var_itemid, var_uomid, var_locid, dou_qty, dou_price, dou_sub, dou_net, dou_funsub, dou_funnet ,dou_orderqty,int_poline) 
																VALUES (prefix1, 1, @serNum, "EA", @locationIdToShip, 1, sumKg, sumKg, sumKg, sumKg,sumKg,1,1);


				END IF;

				IF cbmCou <> 0 THEN
							SET @serNum1 = VB_FUN_GEN_PREFIX(cust);
							SET @SQL9 = CONCAT("INSERT INTO ame_mt_ite (var_id, var_type, var_name, tin_vat, dou_order_pt, dou_order_qty, var_groupid, var_uomid,var_desc, var_img, tin_no_sal, var_venid)	VALUES ('",@serNum1,"','Non-Inventory Part','",'Total Shipment CBM',"',0,0,0,'",cust,"','EA','','',1,'')");
							PREPARE St9 FROM @SQL9;
							EXECUTE St9;
							DEALLOCATE PREPARE St9;

							SET @SQL10 = CONCAT("INSERT INTO ame_mt_ite_uom (var_id, var_uomid, int_line, dou_conv, tin_pur, tin_sal) VALUES ('",@serNum1,"','EA', 0, 1, 1,1);"); 
							PREPARE SQL10 FROM @SQL10;
							EXECUTE SQL10;
							DEALLOCATE PREPARE SQL10;
							INSERT INTO ame_mt_ite_pri (var_id, var_pricecode, var_uomid, var_cur, int_line, dou_price) VALUES (@serNum1,VB_FUN_GET_CUS_CODE(cust),'EA',"USD",1,sumCbm);
							
							INSERT INTO ame_ar_in_det (var_id, int_line, var_itemid, var_uomid, var_locid, dou_qty, dou_price, dou_sub, dou_net, dou_funsub, dou_funnet ,dou_orderqty,int_poline) 
																VALUES (prefix1, 1, @serNum, "EA", @locationIdToShip, 1, sumKg, sumKg, sumKg, sumKg,sumKg,1,1);
				END IF;
	

		END IF;


END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_ADM_USER`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_ADM_USER`(IN `pageNum` int,IN `numRow` int,IN `search` varchar(255))
    DETERMINISTIC
BEGIN

	DECLARE page INT DEFAULT 0;

	SET page = (pageNum - 1) * numRow;

	SELECT COUNT(vb_userId) INTO @rowCounts FROM vb_user WHERE vb_role = 'ADMIN' AND IF(search = '',TRUE, vb_userName LIKE CONCAT('%',search,'%'));

	SELECT
		vb_userId 'userId',
		vb_userName 'userName',
		vb_admin_role 'roleId',
		vb_roleName 'roleName',
		(SELECT vb_typeName FROM vb_user_role_type WHERE vb_roleType = vb_typeId) 'roleType',
		vb_empId 'empId',
		(SELECT var_name FROM ame_mt_emp WHERE var_id = vb_empId) 'empName',
		vb_isEnable 'isEnable'
		from vb_user
			INNER JOIN vb_user_role ON vb_roleId = vb_admin_role
		WHERE vb_role = 'ADMIN' 
		AND IF(search = '',TRUE, vb_userName LIKE CONCAT('%',search,'%'))
		AND vb_isShow = 1
		LIMIT numRow OFFSET page;

	SELECT @rowCounts 'rowCounts';
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_ADM_USER_BY_ID`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_ADM_USER_BY_ID`(IN `userId` int)
    DETERMINISTIC
BEGIN

	SELECT 
		vb_userId 'userId',
		vb_userName 'userName',
		vb_admin_role 'roleId',
		(SELECT vb_roleName FROM vb_user_role WHERE vb_roleId = vb_admin_role) 'roleName',
		COALESCE(vb_empId,'') 'empId',
		COALESCE((SELECT var_name FROM ame_mt_emp WHERE var_id = vb_empId),'') 'empName'
		FROM 
	vb_user 
	WHERE vb_userId = userId;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_CLASS`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_CLASS`(IN `pageNum` int,IN `numRow` int,IN `search` varchar(255))
    DETERMINISTIC
BEGIN
	
	DECLARE page INT DEFAULT 0;
	SET page = (pageNum - 1) * numRow;
	SELECT COUNT(var_id) INTO @rowCounts FROM ame_mt_cla WHERE tin_inactive = 0 AND IF(search = '', TRUE, var_id LIKE CONCAT('%',search,'%') OR var_name LIKE CONCAT('%',search,'%'));
	SELECT @rowCounts 'rowCounts'; 

	SELECT 
		var_id 'classId',
		var_name 'className' 
	FROM ame_mt_cla 
	WHERE 
		tin_inactive = 0 
		AND IF(search = '', TRUE, var_id LIKE CONCAT('%',search,'%') OR var_name LIKE CONCAT('%',search,'%')) LIMIT numRow OFFSET page;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_CUST_BY_ID`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_CUST_BY_ID`(IN cust varchar(255))
    DETERMINISTIC
BEGIN
	SELECT 
		COALESCE(ame_mt_cus.var_id,'') 'CustID',
		COALESCE(ame_mt_cus.var_name,'') 'CustName',
		COALESCE(ame_mt_cus.var_name_con,'') 'CustContactName',
		COALESCE(ame_mt_cus.var_tel1,'') 'CustTel1',
		COALESCE(ame_mt_cus.var_tel2,'') 'CustTel2',
		COALESCE(ame_mt_cus.var_email,'') 'CustEmail',
		COALESCE(vb_cust_addon_info.dat_dob,"") 'DOB',
		COALESCE(ame_mt_cus.var_addr,'') 'CustAddress',
		COALESCE(ame_mt_cus.var_web,'') 'CustWebsite',
		COALESCE(vb_cust_addon_info.var_fb,'') 'Facebook',
		COALESCE(vb_cust_addon_info.var_line,'') 'Line',
		COALESCE(vb_cust_addon_info.var_whatapp,'') 'WhatApp',
		COALESCE(vb_cust_addon_info.var_wechat,'') 'CustWeChat',
		COALESCE(vb_cust_addon_info.var_aba_acc,"") 'CustBankAcc',
		COALESCE(vb_cust_addon_info.var_iden_num,'') 'CustIdentity',
		COALESCE(ame_mt_cus.var_img,'') 'LGT_Logo',
		COALESCE(vb_cust_addon_info.var_iden_img,'') 'CustIdentityImg',
		COALESCE(ame_mt_cus.var_name_other,'') 'CustNameKhmer' 
	FROM ame_mt_cus
		LEFT JOIN vb_cust_addon_info ON var_id = var_custId
	WHERE var_id = cust; 
  SELECT VB_FUN_CUST_BAL(cust);

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_EMP_AS_DEF`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_EMP_AS_DEF`(IN `pageNum` int(11),IN `numRow` int(11),IN `var_search` varchar(11))
BEGIN

	DECLARE page INT DEFAULT 0;
	SELECT COUNT(var_id) INTO @rowCount from ame_mt_emp WHERE IF(var_search = '', true, (var_id LIKE CONCAT('%',var_search,'%')) OR (var_name LIKE CONCAT('%',var_search,'%'))) AND tin_rep = 1;

	SET page = (pageNum - 1) * numRow;
	SELECT 
		var_id 'empId', 
		var_name 'empName' 
		FROM ame_mt_emp 
	WHERE tin_rep = 1 AND	
		IF(var_search = '', true, (var_id LIKE CONCAT('%',var_search,'%')) OR (var_name LIKE CONCAT('%',var_search,'%'))) 
	LIMIT numRow OFFSET page;
	SELECT @rowCount 'rowCount';
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_EMP_BY_ID`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_EMP_BY_ID`(varid varchar(200))
BEGIN
	DECLARE imgPat VARCHAR(200) DEFAULT "";
	SELECT COALESCE(var_name,"") INTO imgPat FROM ame_sys_config WHERE var_id = "IMG_GE_WEB";
  SET imgPat = CONCAT(imgPat,"Employees/");
	SELECT
		emp.var_id 'empId',
		emp.var_name 'empName',
		emp.var_name_other 'empOtherName',
	  emp.var_name_con 'empConName',
		emp.var_tel1 'tel1',
		emp.var_tel2 'tel2',
		emp.var_fax 'fax',
		emp.var_web 'web',
		emp.var_email 'email',
		emp.var_addr 'address',
		emp.var_desc 'desc',
		emp.var_groupid 'groupId',
		(SELECT ame_mt_emp_gro.var_name FROM ame_mt_emp_gro WHERE ame_mt_emp_gro.var_id = emp.var_groupid) 'groupName',
		emp.var_posid 'posId',
		(SELECT ame_mt_pos.var_name FROM ame_mt_pos WHERE ame_mt_pos.var_id = emp.var_posid) 'posName',
		emp.var_term 'term',
		emp.var_cardid 'card',
		emp.var_cur 'cur',
		COALESCE(emp.tin_inactive,0) 'inactive',
		emp.var_img 'img',
		COALESCE(emp.tin_rep,0) 'esr',
		imgPat 'path'
	FROM ame_mt_emp emp 
		WHERE emp.var_id = varid;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_EMP_LIST`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_EMP_LIST`(IN `pageNum` int(11),IN `numRow` int(11),IN `var_search` varchar(255))
    DETERMINISTIC
BEGIN
	DECLARE page INT DEFAULT 0;
	DECLARE rr VARCHAR(200) DEFAULT "";

	SELECT COUNT(var_id) INTO @rowCount from ame_mt_emp WHERE IF(var_search = 'all', true, (var_id LIKE CONCAT('%',var_search,'%')) OR (var_name LIKE CONCAT('%',var_search,'%')));
	SET @totalPage = CEIL(@rowCount / numRow);
	SET page = (pageNum - 1) * numRow;
	
	SELECT COALESCE(var_name,"") INTO rr FROM ame_sys_config WHERE var_id = "IMG_GE_WEB";
  SET rr = CONCAT(rr,"Employees/");
	
	SELECT
		emp.var_id 'empId',
		emp.var_name 'empName',
		emp.var_name_other 'empOtherName',
	  emp.var_name_con 'empConName',
		emp.var_tel1 'tel1',
		emp.var_tel2 'tel2',
		emp.var_fax 'fax',
		emp.var_web 'web',
		emp.var_email 'email',
		emp.var_addr 'address',
		emp.var_desc 'desc',
		emp.var_groupid 'groupId',
		(SELECT ame_mt_emp_gro.var_name FROM ame_mt_emp_gro WHERE ame_mt_emp_gro.var_id = emp.var_groupid) 'groupName',
		emp.var_posid 'posId',
		(SELECT ame_mt_pos.var_name FROM ame_mt_pos WHERE ame_mt_pos.var_id = emp.var_posid) 'posName',
		emp.var_term 'term',
		emp.var_cardid 'card',
		emp.var_cur 'cur',
		COALESCE(emp.tin_inactive,0) 'inactive',
		emp.var_img 'img',
		rr
	FROM ame_mt_emp emp 
		WHERE 
			IF(var_search = 'all', true, (emp.var_id LIKE CONCAT('%',var_search,'%')) OR (emp.var_name LIKE CONCAT('%',var_search,'%'))) 
	LIMIT numRow 
	OFFSET page;
	
	SELECT var_id 'posId',var_name 'posName' FROM ame_mt_pos;
	SELECT var_id 'groupId',var_name 'groupName' FROM ame_mt_emp_gro;
	SELECT var_id 'curId', var_name 'curName', var_symbol 'sym' FROM ame_mt_cur WHERE ame_mt_cur.tin_inactive = 0;
	SELECT @totalPage 'totalPages', @rowCount 'rowCount';
	
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_EMP_NOT_RELATED_TO_USER`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_EMP_NOT_RELATED_TO_USER`(IN `pageNum` int(11),IN `numRow` int(11),IN `search` varchar(255),IN `userId` int)
    DETERMINISTIC
BEGIN

	DECLARE page INT DEFAULT 0;

	SET page = (pageNum - 1) * numRow;
	SELECT
		var_id 'empId',
		var_name 'empName'
		FROM ame_mt_emp
	WHERE 
		var_id NOT IN (SELECT COALESCE(vb_empId,'') FROM vb_user WHERE IF(userId = 0, TRUE, vb_userId <> userId)) AND IF(search = '',TRUE,var_id LIKE CONCAT('%',search,'%') OR var_name LIKE CONCAT('%',search,'%')) LIMIT numRow OFFSET page;

	SELECT COUNT(var_id) 'rowCounts' FROM ame_mt_emp WHERE var_id NOT IN (SELECT COALESCE(vb_empId,'') FROM vb_user WHERE IF(userId = 0, TRUE, vb_userId <> userId)) AND IF(search = '',TRUE,var_id LIKE CONCAT('%',search,'%') OR var_name LIKE CONCAT('%',search,'%'));

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_INV_NUM_PREFIX`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_INV_NUM_PREFIX`()
    DETERMINISTIC
BEGIN
	
	DECLARE Len INTEGER DEFAULT 0;
	DECLARE LastID INTEGER DEFAULT 0;
	DECLARE NumberPre VARCHAR(255) DEFAULT "";
	DECLARE NumOfZero VARCHAR(255) DEFAULT "";
	DECLARE preId VARCHAR(255) DEFAULT "";
	DECLARE modid VARCHAR(255) DEFAULT "";
	DECLARE result VARCHAR(255) DEFAULT "";
	DECLARE LenID INTEGER DEFAULT 0;
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE prefix_cursor CURSOR FOR SELECT DISTINCT var_modid, var_id FROM ame_sys_prefix_custom WHERE var_type = "AR-IN";
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
	OPEN prefix_cursor;
	get_prefix:LOOP
		FETCH prefix_cursor INTO modid,preId;
		SELECT nc.int_length,nc.int_lastid,nc.var_id INTO Len,LastID,NumberPre from ame_sys_prefix_custom nc WHERE  nc.var_id = preId;
		IF finished = 1 THEN 
			LEAVE get_prefix;
		END IF;
		SET result = CONCAT(result,',','{"id":"',modid,'","invPrefix":"',CONCAT(NumberPre,LPAD('X',Len,"0")),'"}');
		SET NumOfZero = "";
	END LOOP get_prefix;
	CLOSE prefix_cursor;
	SET result = CONCAT('[',SUBSTR(result,2),']');
	SELECT result 'invPrefix'; 
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_ITEM_BY_CUST`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_ITEM_BY_CUST`(IN cust varchar(255),IN wher varchar(255),IN val varchar(255),IN pageNum INT(10), IN numRow INT(10))
    DETERMINISTIC
BEGIN

	DECLARE page INT DEFAULT 0;
  
  SELECT COUNT(ex.vit_id) INTO @rowCount from vb_item ex  WHERE vit_custID = cust 
	AND IF(LOWER(val) = '1', ex.vit_status IN (2,3,12), ex.vit_status = val) AND IF(wher = '',TRUE,ex.vit_code = wher);

  SET @totalPage = CEIL(@rowCount / numRow);
  SET page = (pageNum - 1) * numRow;

 SELECT COALESCE(vit_id,"") 'id', COALESCE(vit_custID,"") 'customer',COALESCE(vit_shipTo,"") 'shipto',COALESCE(vit_empID,"") "employee", COALESCE(vit_exDollar,0) 'dollar',
				COALESCE(vit_exKhmer,0) 'khmer', COALESCE(vit_exChina,0) 'china', COALESCE(vit_deposit,0) 'deposit', COALESCE(vit_balance,0) "balance", COALESCE(vit_chinaWarehouse,"") "chinawarehouse",
				COALESCE(vit_camWarehouse,"") "camWarehouse", COALESCE(vit_noOfDay,"") "noofday",
				COALESCE(vit_totalProductFeeINUsd,0) "totalProductFeeINUsd",COALESCE(vit_totalProductFeeINYan,0) 'totalProductFeeINYan', COALESCE(vit_serviceChargeUsd,0) 'serviceChargeUsd',
				COALESCE(vit_totalLCFeeINUsd,0) "totalLCFeeINUsd", COALESCE(vit_totalINUsd,0) "totalINUsd",
				COALESCE(vit_status,0) 'status',(SELECT vb_status.SName FROM vb_status WHERE vb_status.SID = vb_item.vit_status) 'StatusName',COALESCE(vit_type,"") 'type',COALESCE(vit_code,"") 'code',vit_date, 
				COALESCE(vit_paymentDate,"") 'paymentDate',COALESCE(vit_dateAvailable,"") 'availdate',COALESCE(vit_expirationDate,"") 'exDate',COALESCE(vit_ordertype,"") 'orderTypeid',
				COALESCE((SELECT COALESCE(TName,"") FROM vb_itemtype WHERE  vb_itemtype.TID = vb_item.vit_ordertype),"") 'orderTypeName'
	FROM vb_item WHERE vit_custID = cust 
 	AND IF(LOWER(val) = '1',vit_status IN (2,3,12), vit_status = val) AND IF(wher = '',TRUE,vit_code = wher)
	ORDER BY vit_id DESC
  LIMIT numRow OFFSET page;

	SELECT @rowCount 'total',@totalPage;

		CALL VB_PRO_UPDATE_EXP_DATE_ITEM(cust);
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_ITEM_CART_BY_CUST`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_ITEM_CART_BY_CUST`(IN cust varchar(255),IN pageNum INT(10), IN numRow INT(10))
    DETERMINISTIC
BEGIN
	DECLARE page INT DEFAULT 0;
 
	IF cust <> '' THEN
		SELECT COUNT(ex.vit_id) INTO @rowCount from vb_itemcart ex  WHERE ex.custId = cust ;
		SET @totalPage = CEIL(@rowCount / numRow);
		SET page = (pageNum - 1) * numRow;

		SELECT COALESCE(vit_name,"") "title", COALESCE(vit_link,"") "link", COALESCE(vit_photo,"") "photo", COALESCE(vit_size,"") "size", COALESCE(vit_color,"") "color",
					 COALESCE(vit_qty,"") "qty", COALESCE(vit_unitPriceYan,0) "unitpriceyan", COALESCE(vit_totalProductFeeYan,0) "totalProductFeeYan", COALESCE(vit_lcFeeYan,0) "lcfeeyan",
					 COALESCE(vit_chinaInvoiceCode,"") "chinaInvoiceCode", COALESCE(vit_truckingNoInChina,"") "truckingNoInChina", COALESCE(vit_remark,"") "remark", 
					 COALESCE(vit_unitPriceDollar,0) "unitpricedollar", COALESCE(vit_amountDollar,0) "amountDollar", COALESCE(vit_lcFeeDollar,0) "lcfeedollar",COALESCE(vit_vendor,"") "vendor",
					 COALESCE(vit_status,"") "status", COALESCE(vit_type,"") "type",COALESCE(vit_id,"") "vit_id",custId,vit_id
		FROM vb_itemcart WHERE custId = cust ORDER BY vit_id DESC  LIMIT numRow OFFSET page;
		
		SELECT COALESCE(@rowCount,0) 'total',@totalPage 'totalPage';
		CALL VB_PRO_MSG(5,'item cart');
	ELSE
		CALL VB_PRO_MSG(2,'item cart');
	END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_ITEM_CUST`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_ITEM_CUST`(IN cust VARCHAR(255), IN vid INT(11),IN pageNum INT(10), IN numRow INT(10))
    DETERMINISTIC
BEGIN

   DECLARE page INT DEFAULT 0;
		SELECT COALESCE(vb_defaultcustomer.ItemAvailable,0) INTO @itAvl FROM vb_defaultcustomer LIMIT 1;
		SELECT COALESCE(vit_id,"") 'id', COALESCE(vit_custID,"") 'customer',COALESCE(vit_shipTo,"") 'shipto',COALESCE(vit_empID,"") "employee", COALESCE(vit_exDollar,0) 'dollar',
				   COALESCE(vit_exKhmer,0) 'khmer', COALESCE(vit_exChina,0) 'china', COALESCE(vit_deposit,0) 'deposit', VB_FUN_CUST_BAL(cust) "balance", COALESCE(vit_chinaWarehouse,"") "chinawarehouse",
				   COALESCE(vit_camWarehouse,"") "camWarehouse", COALESCE(vit_noOfDay,"") "noofday",
				   COALESCE(vit_totalProductFeeINUsd,0) "totalProductFeeINUsd",COALESCE(vit_totalProductFeeINYan,0) 'totalProductFeeINYan', COALESCE(vit_serviceChargeUsd,0) 'serviceChargeUsd',
				   COALESCE(vit_totalLCFeeINUsd,0) "totalLCFeeINUsd", COALESCE(vit_totalINUsd,0) "totalINUsd",
				   COALESCE(vit_status,"") 'status',(SELECT vb_status.SName FROM vb_status WHERE vb_status.SID = vb_item.vit_status) 'StatusName',COALESCE(vit_type,"") 'type',COALESCE(vit_code,"") 'code',vit_date,
					 COALESCE(vit_paymentDate,"") 'paymentDate',COALESCE(vit_dateAvailable,"") 'availdate',COALESCE(vit_expirationDate,"") 'exDate',
           (SELECT vb_customershipaddress.Active FROM vb_customershipaddress WHERE vb_customershipaddress.ItemId = vid) 'checkship',
					 COALESCE(vit_goodsExpired,"") 'goodEx',@itAvl
					FROM vb_item WHERE vit_custID = cust  AND vit_id = vid;


		SELECT COUNT(ex.vit_id) INTO @rowCount from vb_itemdetail ex  WHERE ex.vit_id = vid;
		SET @totalPage = CEIL(@rowCount / numRow);
		SET page = (pageNum - 1) * numRow;

		SELECT COALESCE(vit_name,"") "title", COALESCE(vit_link,"") "link", COALESCE(vit_photo,"") "photo", COALESCE(vit_size,"") "size", COALESCE(vit_color,"") "color",
					 COALESCE(vit_qty,"") "qty", COALESCE(vit_unitPriceYan,0) "unitpriceyan", COALESCE(vit_totalProductFeeYan,0) "totalProductFeeYan", COALESCE(vit_lcFeeYan,0) "lcfeeyan",
					 COALESCE(vit_chinaInvoiceCode,"") "chinaInvoiceCode", COALESCE(vit_truckingNoInChina,"") "truckingNoInChina", COALESCE(vit_remark,"") "remark", 
					 COALESCE(vit_unitPriceDollar,0) "unitpricedollar", COALESCE(vit_amountDollar,0) "amountDollar", COALESCE(vit_lcFeeDollar,0) "lcfeedollar",COALESCE(vit_vendor,"") "vendor",
					 COALESCE(vit_status,"") "status", COALESCE(vit_type,"") "type",vit_iddetail,vit_someqty FROM vb_itemdetail WHERE vit_id = vid AND vb_itemdetail.vit_refund <> 2 LIMIT numRow OFFSET page;

		SELECT COALESCE(vb_customershipaddress.TransID,""),COALESCE(vb_customershipaddress.CustId,""),COALESCE(vb_customershipaddress.ShipTo,""),COALESCE(vb_customershipaddress.Location,""),COALESCE(vb_customershipaddress.Description,''),COALESCE(vb_customershipaddress.Type,''),COALESCE((SELECT var_name FROM ame_mt_ship_addr WHERE var_id = vb_customershipaddress.ShipTo),"") FROM vb_customershipaddress WHERE vb_customershipaddress.ItemId = vid;
		SELECT COALESCE(@rowCount,0) 'total',@totalPage;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_ITEM_CUST_DET`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_ITEM_CUST_DET`(IN vid INT(255))
    DETERMINISTIC
BEGIN

	SELECT vit_id,vit_link,vit_photo,vit_name,COALESCE(vit_someqty,0) 'some' FROM vb_itemdetail
	WHERE vit_id = vid AND vit_refund <> 2;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_ITEM_PENDING_AUDIT`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_ITEM_PENDING_AUDIT`(IN `var_status` int(11), IN `pageNum` int(11),IN `numRow` int(11),IN `var_search` varchar(255),IN `userId` int(11))
    DETERMINISTIC
BEGIN
	
	DECLARE page INT DEFAULT 0;

	SELECT vb_empId INTO @empId FROM vb_user WHERE vb_userId = userId;

	SELECT vb_roleType INTO @roleType FROM vb_user_role WHERE vb_roleId = (SELECT vb_admin_role FROM vb_user WHERE vb_userId = userId);

	SELECT 
		COUNT(vb_item.vit_id) INTO @rowCount from vb_item 
		where 
			IF(var_status = 1,vb_item.vit_status IN (2,3,12),vb_item.vit_status = var_status)
			AND IF(var_search = '',true,(vb_item.vit_code LIKE CONCAT('%',var_search,'%')) 
					OR (DATE_FORMAT(vb_item.vit_date,'%d-%b-%Y') = var_search) 
					OR (vb_item.vit_custID LIKE CONCAT('%',var_search,'%')) 
					OR (vb_item.vit_custID IN (SELECT var_id from ame_mt_cus WHERE var_id = vb_item.vit_custID AND var_name LIKE CONCAT('%',var_search,'%'))))
			AND IF(@roleType = 1, TRUE, vb_item.vit_custID IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId));

	SET @totalPage = CEIL(@rowCount / numRow);
	SET page = (pageNum - 1) * numRow;
	SELECT
		vi.vit_id 'vitId',
		vi.vit_custID 'custId',
		(SELECT COUNT(vb_itemdetail.vit_id) FROM vb_itemdetail WHERE vb_itemdetail.vit_id = vi.vit_id) 'countItem',
		(SELECT COUNT(vb_itemdetail.vit_id) FROM vb_itemdetail WHERE vb_itemdetail.vit_id = vi.vit_id AND vb_itemdetail.vit_truckingNoInChina = '') 'truckingNoNotYetSet',
		(SELECT COUNT(vb_itemdetail.vit_id) FROM vb_itemdetail WHERE vb_itemdetail.vit_id = vi.vit_id AND vb_itemdetail.vit_truckingNoInChina <> '') 'truckingNoSet',
		(SELECT var_name from ame_mt_cus WHERE var_id = vi.vit_custID) 'custName',
		vi.vit_code 'vitCode',
		VB_FUN_CUST_BAL(vi.vit_custID) 'vitCustBalance',
		vi.vit_empID 'empId',
		(SELECT ame_mt_emp.var_name FROM ame_mt_emp WHERE ame_mt_emp.var_id = vi.vit_empID) 'empName',
		(SELECT var_name from ame_mt_emp WHERE var_id = vi.vit_empID) 'empName',
		vi.vit_totalINUsd 'vitTotalInUSD',
		vi.vit_totalLCFeeINUsd 'vitTotalLCFeeInUSD',
		vi.vit_totalProductFeeINUsd 'vitTotalProFeeInUSD',
		vi.vit_serviceChargeUsd 'vitServiceChargeUSD',
		vi.vit_status 'vitStatus',
		(SELECT SName from vb_status WHERE SID = vi.vit_status) 'vitStatusName',
		vi.vit_type 'vitType',
		DATE_FORMAT(vi.vit_date,'%d-%b-%Y') 'vitDate',
		DATE_FORMAT(vi.vit_expirationDate,'%d-%b-%Y %r') 'vitExpireDate'
	from vb_item vi 
	WHERE 
		IF(var_status = 1,vi.vit_status IN (2,3),vi.vit_status = var_status)
 		AND IF(var_search = '', true, 
						(vi.vit_code LIKE CONCAT('%',var_search,'%'))
						OR (DATE_FORMAT(vi.vit_date,'%d-%b-%Y') = var_search)
						OR (vi.vit_custID LIKE CONCAT('%',var_search,'%'))
						OR (vi.vit_custID IN (SELECT var_id from ame_mt_cus WHERE var_id = vi.vit_custID AND var_name LIKE CONCAT('%',var_search,'%'))))
		AND IF(@roleType = 1, TRUE, vi.vit_custID IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId))
	ORDER BY DATE(vi.vit_date) DESC, vi.vit_id DESC LIMIT numRow OFFSET page;

	SELECT @totalPage 'totalPage', @rowCount 'rowCount';
	SELECT 
		SID 'statusId', 
		SName 'statusName', 
		(SELECT COUNT(vb_item.vit_id) 
				FROM vb_item 
				WHERE IF(SID = 1,vb_item.vit_status IN (2,3), vb_item.vit_status = SID) 
				AND IF(@roleType = 1, TRUE, vb_item.vit_custID IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId))) 'countRow' 
	FROM vb_status;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_MEASURE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_MEASURE`()
    DETERMINISTIC
BEGIN

	SELECT
		var_id 'measureId',
		var_name 'measureName',
		dou_price 'measurePrice',
		dou_amt 'amt',
		var_cur 'currency'
	FROM vb_measure;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_QTY_SALE_INV_SALE_ORDER`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_QTY_SALE_INV_SALE_ORDER`(IN `saleRef` varchar(255))
    DETERMINISTIC
BEGIN

		SELECT 
			COALESCE(SUM(det.dou_qty),0) INTO @saleQty 
		FROM ame_ar_so_det det 
		WHERE det.var_id IN (SELECT var_id FROM ame_ar_so WHERE var_ref = saleRef);

		SELECT 
			COALESCE(SUM(det.dou_qty),0) INTO @invSaleQty 
		FROM ame_ar_in_det det 
		WHERE det.var_id IN (SELECT var_id FROM ame_ar_in WHERE var_ref = saleRef);

		SELECT @saleQty 'saleQty', @invSaleQty 'invSaleQty';

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_ROLE_ID`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_ROLE_ID`(IN `roleId` int)
    DETERMINISTIC
BEGIN
	
	SELECT
		vb_roleId 'roleId',
		vb_roleName 'roleName',
		vb_roleType 'typeId',
		(SELECT vb_typeName FROM vb_user_role_type WHERE vb_typeId = vb_roleType) 'typeName'
	FROM vb_user_role WHERE vb_roleId = roleId;

	SELECT 
		n.ModuleID 'moduleId',
		n.Remarks 'moduleName',
		rdt.vb_dtId 'dtId',
		rdt.vb_roleId 'roleId',
		COALESCE(rdt.vb_isAccessible,0) 'isAccessible'
		FROM 
			vb_numberprefix n 
		LEFT JOIN (SELECT vb_dtId, vb_moduleId, vb_roleId, vb_isAccessible FROM vb_user_role_dt WHERE vb_roleId = roleId) AS rdt 
			ON n.ModuleID = rdt.vb_moduleId
		WHERE n.Hide = 0; 

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_ROLE_TYPE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_ROLE_TYPE`()
    DETERMINISTIC
BEGIN
	SELECT 
		vb_typeId 'typeId', 
		vb_typeName 'typeName' 
	from vb_user_role_type;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_ROW_AFFECTED`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_ROW_AFFECTED`(IN `itemCode` TEXT)
    DETERMINISTIC
BEGIN
   	SET @SQL2 = CONCAT("
			SELECT
				(SELECT var_ref FROM ame_ar_so so WHERE so.var_id = det.var_id) 'ofCode',
				(SELECT ite.var_itemcode FROM ame_mt_ite ite WHERE ite.var_id = det.var_itemid) 'itemcode',
				det.var_vb_meatype 'measure',
				det.dou_vb_meavalue 'qty'
				FROM 
					ame_ar_so_det det 
			WHERE det.var_itemid IN (SELECT ite.var_id FROM ame_mt_ite ite WHERE ite.var_itemcode IN (",itemCode,"));");
		PREPARE St2 FROM @SQL2;
		EXECUTE St2;
		DEALLOCATE PREPARE St2;


	

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_SALE_INV`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_SALE_INV`(IN `pageNum` int,IN `numRow` int,IN `search` varchar(255))
    DETERMINISTIC
BEGIN
	DECLARE page INT DEFAULT 0;
	
	SET page = (pageNum - 1) * numRow;
	
	SELECT
		inv.var_id 'saleId',
		DATE_FORMAT(inv.dat_date,'%d-%b-%Y') 'saleDate',
		inv.var_ref 'saleReference',
		inv.dou_net 'netTotalAmt',
		inv.dou_sub 'totalAmt',
		inv.var_type 'saleType',
		inv.var_cusid 'custId',
		inv.var_term 'custTerm',
		inv.var_cur 'custCurrency',
		(SELECT c.var_name FROM ame_mt_cus c WHERE c.var_id = inv.var_cusid) 'custName',
		inv.var_empid 'empId',
		(SELECT e.var_name FROM ame_mt_emp e WHERE e.var_id = inv.var_empid) 'empName',
		inv.var_pricecode 'priceCode',
		SUM(invd.dou_qty)'saleQty'
		FROM ame_ar_in inv
			INNER JOIN (SELECT var_id, var_itemid, dou_qty FROM ame_ar_in_det) invd ON  invd.var_id = inv.var_id
			INNER JOIN (SELECT var_id, tin_no_sal  FROM ame_mt_ite) ite ON invd.var_itemid = ite.var_id
		WHERE 
			IF(search = '', TRUE, 
					inv.var_id LIKE CONCAT('%',search,'%') 
					OR inv.var_ref LIKE CONCAT('%',search,'%')
					OR DATE_FORMAT(inv.dat_date,'%d-%b-%Y') = search
					OR inv.var_empid LIKE CONCAT('%',search,'%')
					OR inv.var_empid IN (SELECT em.var_id FROM ame_mt_emp em WHERE em.var_name LIKE CONCAT('%',search,'%'))
					OR inv.var_cusid LIKE CONCAT('%',search,'%')
					OR inv.var_cusid IN (SELECT cu.var_id FROM ame_mt_cus cu WHERE cu.var_name LIKE CONCAT('%',search,'%'))
					OR inv.var_term LIKE CONCAT('%',search,'%'))
			AND ite.tin_no_sal = 0
			AND COALESCE(inv.var_ref,'') <> ''
		GROUP BY inv.var_id
		ORDER BY inv.dat_date DESC, inv.var_id DESC LIMIT numRow OFFSET page;

		SELECT 
			COUNT(DISTINCT inv.var_id) AS 'rowCounts' 
		FROM ame_ar_in inv
			INNER JOIN(SELECT var_id, var_itemid  FROM ame_ar_in_det) invd ON  invd.var_id = inv.var_id
			INNER JOIN (SELECT var_id, tin_no_sal  FROM ame_mt_ite) ite ON invd.var_itemid = ite.var_id
		WHERE 
			IF(search = '', TRUE, 
					inv.var_id LIKE CONCAT('%',search,'%') 
					OR inv.var_ref LIKE CONCAT('%',search,'%')
					OR DATE_FORMAT(inv.dat_date,'%d-%b-%Y') = search
					OR inv.var_empid LIKE CONCAT('%',search,'%')
					OR inv.var_empid IN (SELECT em.var_id FROM ame_mt_emp em WHERE em.var_name LIKE CONCAT('%',search,'%'))
					OR inv.var_cusid LIKE CONCAT('%',search,'%')
					OR inv.var_cusid IN (SELECT cu.var_id FROM ame_mt_cus cu WHERE cu.var_name LIKE CONCAT('%',search,'%'))
					OR inv.var_term LIKE CONCAT('%',search,'%'))
			AND ite.tin_no_sal = 0
			AND COALESCE(inv.var_ref,'') <> '';
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_SALE_ORDER_BY_ID`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_SALE_ORDER_BY_ID`(IN `orderId` varchar(255),IN `pickup_code` varchar(10),IN `userId` int(11))
    DETERMINISTIC
BEGIN

	SELECT vb_roleType INTO @roleType FROM vb_user_role WHERE vb_roleId = (SELECT vb_admin_role FROM vb_user WHERE vb_userId = userId);
	
	SELECT SQL_CALC_FOUND_ROWS  var_id,var_cusid INTO @saleId,@cusId FROM ame_ar_so WHERE var_ref = orderId AND int_vb_code = pickup_code;

	SET @priCheck = VB_FUN_FNUM(VB_FUN_CAL_SAL(@saleId));
	SET @priBalanc = VB_FUN_FNUM(VB_FUN_CUST_BAL(@cusId));
	
	IF FOUND_ROWS() > 0 THEN

			IF @priBalanc < @priCheck THEN
				SELECT CONCAT("The customer balance is not enough. Balance: ",@priBalanc,", Pay for shipment: ",@priCheck,". ") 'msg', CONCAT('','INVALID','') 'key';
			ELSE
				
			SELECT 
				so.var_id 'saleId',
				so.var_ref 'saleReference',
				so.var_type 'saleType',
				tbc.var_id 'custId',
				tbc.var_name 'custName',
				tp.var_id 'priceCode',
				tp.var_name 'priceDes',
				so.var_empid 'empId',
				(SELECT e.var_id FROM ame_mt_emp e WHERE e.var_id = so.var_empid) 'empName',
				COALESCE(so.var_shipid,'') 'shipId',
				COALESCE(so.var_shipaddr,'') 'shipName',
				(SELECT SUM((det.dou_qty - det.dou_orderqty) * det.dou_price) FROM ame_ar_so_det det WHERE det.var_id = so.var_id AND det.dou_orderqty < det.dou_qty ) 'netTotalAmt',
				(SELECT SUM((det.dou_qty - det.dou_orderqty) * det.dou_price) FROM ame_ar_so_det det WHERE det.var_id = so.var_id AND det.dou_orderqty < det.dou_qty) 'totalAmt',
				@roleType 'roleType'
				FROM ame_ar_so so 
				INNER JOIN (SELECT tc.var_id, tc.var_name, tc.var_pricecode FROM ame_mt_cus tc) as tbc 
					ON tbc.var_id = so.var_cusid
				LEFT JOIN ame_mt_pri_cod tp ON tbc.var_pricecode = tp.var_id
				WHERE so.var_id = @saleId;

			SELECT 
				sd.var_id 'saleId', 
				sd.int_line 'lineNo',
				sd.var_itemid 'itemId',
				(SELECT var_name FROM ame_mt_ite WHERE var_id = sd.var_itemid) 'itemName',
				COALESCE(sd.var_locid,'') 'locatId',
				COALESCE((SELECT l.var_name FROM ame_mt_loc l WHERE l.var_id = sd.var_locid),'') 'locatName',
				COALESCE(sd.var_claid,'') 'classId',
				COALESCE((SELECT c.var_name FROM ame_mt_cla c WHERE c.var_id = sd.var_claid),'') 'className',
				sd.var_uomid 'uomId',
				(SELECT u.var_name FROM ame_mt_uom u WHERE u.var_id = sd.var_uomid) 'uomDes',
				VB_FUN_CHECK_QTY_SAL(sd.var_itemid,sd.var_id) 'qty',
				sd.dou_price 'unitPrice',
				((sd.dou_qty - sd.dou_orderqty) * sd.dou_price) 'totalAmt',
				sd.dou_disdol 'disDol',
				sd.dou_disper 'disPer',
				sd.dou_taxdol 'vTaxDol',
				sd.dou_taxper 'vTaxPer',
				((sd.dou_qty - sd.dou_orderqty) * sd.dou_price) 'netTotalAmt',
				COALESCE(sd.dou_orderqty,0) 'receiveQty',
				sd.int_poline 'keyLine',
				sd.var_vb_meatype 'measure',
				sd.dou_vb_meavalue 'qtyMeasure',
				sd.dou_vb_meaprice 'priceOfMeasure',
				sd.dou_vb_availqty 'availableQty'
				FROM ame_ar_so_det sd 
				WHERE sd.var_id = @saleId 
							AND sd.dou_orderqty < sd.dou_qty 
				ORDER BY sd.int_line ASC;

			SELECT CONCAT("OK") 'msg', CONCAT('','FOUND','') 'key';
		END IF;

	ELSE 
		SELECT CONCAT("The OF CODE: ",'"',orderId,'"', " with pickup code doesn`t contain any info!") 'msg', CONCAT('','NOT_FOUND','') 'key';
	END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_SALE_ORDER_DET_BY_SALEID`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_SALE_ORDER_DET_BY_SALEID`(IN `saleId` varchar(255))
    DETERMINISTIC
BEGIN
	
	SELECT
		int_poline 'keyLine',
		dou_qty 'saleQty'
	FROM ame_ar_so_det WHERE var_id = saleId;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_SHIP_OF_CUST`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_SHIP_OF_CUST`(IN `custId` varchar(100),IN `pageNum` int,IN `numRow` int,IN `search` varchar(100))
    DETERMINISTIC
BEGIN
	
	DECLARE page INT DEFAULT 0;
	SET page = (pageNum - 1) * numRow;

	SELECT COUNT(var_id) INTO @rowCounts FROM ame_mt_ship_addr WHERE IF(custId='',TRUE,var_num = custId) AND IF(search = '', TRUE, var_id LIKE CONCAT('%',search,'%') OR var_name LIKE CONCAT('%',search,'%'));
	SELECT @rowCounts 'rowCounts';

	SELECT 
		var_id 'shipId', 
		var_name 'shipName' 
	FROM ame_mt_ship_addr 
	WHERE 
		IF(custId='',TRUE,var_num = custId) 
		AND IF(search = '', TRUE, var_id LIKE CONCAT('%',search,'%') OR var_name LIKE CONCAT('%',search,'%')) LIMIT numRow OFFSET page;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_SLIDE_MENU_BY_USER`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_SLIDE_MENU_BY_USER`(IN `userId` int)
    DETERMINISTIC
BEGIN
	(SELECT 
		n.ModuleID 'moduleId',
		n.Remarks 'moduleName',
		urd.vb_isAccessible 'isAccessible'
		FROM vb_numberprefix n
		INNER JOIN vb_user_role_dt urd ON n.ModuleID = urd.vb_moduleId
		INNER JOIN vb_user_role ur ON ur.vb_roleId = urd.vb_roleId
		INNER JOIN (SELECT u.vb_admin_role, u.vb_role FROM vb_user u WHERE u.vb_userId = userId) as vu ON vu.vb_admin_role = ur.vb_roleId
	WHERE n.Hide = 0 AND vu.vb_role = 'ADMIN')
	UNION 
	(SELECT 
			ModuleID 'mdouleId',
			Remarks 'moduleName', 
			false 'isAccessible' 
		FROM 
			vb_numberprefix 
		WHERE Hide = 0 
			AND ModuleID NOT IN (
				SELECT urd.vb_moduleId FROM vb_user_role_dt urd 
					INNER JOIN vb_user_role ur ON ur.vb_roleId = urd.vb_roleId
					INNER JOIN (SELECT u.vb_admin_role, u.vb_role FROM vb_user u WHERE u.vb_userId = userId) as vu ON vu.vb_admin_role = ur.vb_roleId));
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_SO_AVAILABLE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_SO_AVAILABLE`(IN `pageNum` int,IN `numRow` int,IN `search` varchar(255),IN `userId` int(11))
BEGIN
	DECLARE page INT DEFAULT 0;

	SET page = (pageNum - 1) * numRow;
	SELECT vb_roleType INTO @roleType FROM vb_user_role WHERE vb_roleId = (SELECT vb_admin_role FROM vb_user WHERE vb_userId = userId);
	SELECT vb_empId INTO @empId FROM vb_user WHERE vb_userId = userId;

	SELECT 
		so.var_id 'saleId',
		so.var_ref 'saleRef',
		so.var_type 'saleType',
		tbc.var_id 'custId',
		tbc.var_name 'custName',
		tp.var_id 'priceCode',
		tp.var_name 'priceDes',
		so.var_empid 'empId',
		(SELECT e.var_id FROM ame_mt_emp e WHERE e.var_id = so.var_empid) 'empName',
		(SELECT SUM(sd.dou_qty) FROM ame_ar_so_det sd INNER JOIN (SELECT var_id, var_itemcode,tin_no_sal FROM ame_mt_ite) AS ite ON ite.var_id = sd.var_itemid WHERE sd.var_id = so.var_id AND ite.tin_no_sal = 0) 'qty',
		COALESCE((SELECT CONCAT('[',GROUP_CONCAT(CONCAT_WS(',',CONCAT('{"code":"',COALESCE(ite.var_itemcode,''),'","itemId":"',ite.var_id,'","orderQty":',COALESCE(sd.dou_qty,0),',"availableQty":',COALESCE(sd.dou_vb_availqty,0),'}')) SEPARATOR ','),']') FROM ame_ar_so_det sd INNER JOIN (SELECT var_id, var_itemcode,tin_no_sal FROM ame_mt_ite) AS ite ON ite.var_id = sd.var_itemid WHERE sd.var_id = so.var_id AND ite.tin_no_sal = 0),'') 'item',
		@roleType 'roleType',
		vbi.vit_status 'vitStatus'
		FROM ame_ar_so so 
		INNER JOIN (SELECT tc.var_id, tc.var_name, tc.var_pricecode FROM ame_mt_cus tc) as tbc 
			ON tbc.var_id = so.var_cusid
		INNER JOIN (SELECT vit_code, vit_status FROM vb_item) as vbi ON vbi.vit_code = so.var_ref
		LEFT JOIN ame_mt_pri_cod tp ON tbc.var_pricecode = tp.var_id
		WHERE IF(search = '',TRUE,
			so.var_id LIKE CONCAT('%',search,'%')
			OR so.var_ref LIKE CONCAT('%',search,'%'))
		AND so.var_status = 'Open'
		AND IF(@roleType = 1, TRUE, so.var_cusid IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId))
		AND vbi.vit_status IN (8,9) ORDER BY so.var_id ASC LIMIT numRow OFFSET page;

		SELECT
			COUNT(var_id) INTO @rowCounts
		FROM ame_ar_so 
		INNER JOIN (SELECT vit_code, vit_status FROM vb_item) as vbi ON vbi.vit_code = var_ref
		WHERE IF(search = '',TRUE,
			var_id LIKE CONCAT('%',search,'%')
			OR var_ref LIKE CONCAT('%',search,'%'))
		AND var_status = 'Open'
		AND IF(@roleType = 1, TRUE, var_cusid IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId))
		AND vbi.vit_status IN (8,9);

		SELECT @rowCounts 'rowCounts', (SELECT CONCAT('[',GROUP_CONCAT(CONCAT_WS(',',CONCAT('{"statusId":"',SID,'","statusName":"',SName,'"}')) SEPARATOR ','),']') FROM vb_status WHERE SID IN (9,10) ORDER BY SID ASC) 'status';
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_SO_DET`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_SO_DET`(IN `pageNum` int,IN `numRow` int,IN `search` varchar(255),IN `int_type` int,userId INT(10))
    DETERMINISTIC
BEGIN
	DECLARE page INT DEFAULT 0;

	SELECT vb_roleType INTO @roleType FROM vb_user_role WHERE vb_roleId = (SELECT vb_admin_role FROM vb_user WHERE vb_userId = userId);
	SELECT vb_empId INTO @empId FROM vb_user WHERE vb_userId = userId;
	
	SET page = (pageNum - 1) * numRow;
	SELECT 
		sd.var_id 'sdId',
		sd.dou_qty 'sdQty',
		sd.dou_vb_meavalue 'measureValue',
		sd.var_vb_meatype 'measureType',
		sd.dou_vb_meaprice 'measurePrice',
		sd.dou_vb_availqty 'availableQty',
		so.var_ref 'ref',
		DATE_FORMAT(so.dat_date,'%d-%b-%Y') 'date',
		ite.var_id 'itemId',
		ite.var_name 'itemName',
		ite.var_itemcode 'itemCode',
		so.var_cusid 'custId',
		(SELECT var_name FROM ame_mt_cus WHERE var_id = so.var_cusid) 'custName'
	FROM 
		ame_ar_so_det sd
	INNER JOIN (SELECT var_id, var_name, var_itemcode,tin_inactive,tin_no_sal FROM ame_mt_ite) AS ite ON ite.var_id = var_itemid
	INNER JOIN (SELECT var_id, var_ref,dat_date,var_cusid,var_status FROM ame_ar_so) AS so ON so.var_id = sd.var_id
	WHERE 
		IF(search = '',TRUE,
			sd.var_id LIKE CONCAT('%',search,'%')
			OR so.var_ref LIKE CONCAT('%',search,'%')
			OR sd.var_itemid LIKE CONCAT('%',search,'%')
			OR ite.var_name LIKE CONCAT('%',search,'%'))
		AND IF(int_type = 0,COALESCE(ite.var_itemcode,'') = '',COALESCE(ite.var_itemcode,'') <> '')
		AND COALESCE(so.var_ref,'') <> '' 
		AND ite.tin_inactive = 0 
		AND ite.tin_no_sal = 0 
		AND so.var_status = 'Open'
		AND IF(@roleType = 1, TRUE, so.var_cusid IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId))
	ORDER BY so.dat_date ASC LIMIT numRow OFFSET page;

	SELECT 
		COUNT(sd.var_id) 'rowCounts'
	FROM 
		ame_ar_so_det sd
	INNER JOIN (SELECT var_id, var_name, var_itemcode,tin_inactive,tin_no_sal FROM ame_mt_ite) AS ite ON ite.var_id = var_itemid
	INNER JOIN (SELECT var_id, var_ref,dat_date,var_status,var_cusid FROM ame_ar_so) AS so ON so.var_id = sd.var_id
	WHERE 
		IF(search = '',TRUE,
			sd.var_id LIKE CONCAT('%',search,'%')
			OR so.var_ref LIKE CONCAT('%',search,'%')
			OR sd.var_itemid LIKE CONCAT('%',search,'%')
			OR ite.var_name LIKE CONCAT('%',search,'%'))
		AND IF(int_type = 0,COALESCE(ite.var_itemcode,'') = '',COALESCE(ite.var_itemcode,'') <> '')
		AND COALESCE(so.var_ref,'') <> ''  AND ite.tin_inactive = 0 AND ite.tin_no_sal = 0 AND so.var_status = 'Open'
		AND IF(@roleType = 1, TRUE, so.var_cusid IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId));

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_TERM_INV`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_TERM_INV`()
    DETERMINISTIC
BEGIN

	SELECT 
		var_id 'termId', 
		var_type 'termType', 
		dou_disc 'termDiscPer', 
		int_daydue 'termDue',
		int_daydisc 'termDayDisc',
		int_dayminpay 'termMinDayToPay'
	FROM ame_mt_pmt_term 
	WHERE tin_inactive = 0;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_TRAN_BY_CUST`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_TRAN_BY_CUST`(IN cust varchar(255), IN pageNum INT(10), IN numRow INT(10))
    DETERMINISTIC
BEGIN
	
	DECLARE page INT DEFAULT 0;
  
  SELECT COUNT(ex.CardID) INTO @rowCount from tblcardtransactionhistory ex  WHERE ex.CardID = (SELECT vb_user.vb_mobile FROM vb_user WHERE vb_user.CustID = cust) ;
  SET @totalPage = CEIL(@rowCount / numRow);
  SET page = (pageNum - 1) * numRow;
	
 SELECT TransID,CardID,Date,TransType,Amount,COALESCE(SalID,"") 'sale' FROM tblcardtransactionhistory WHERE CardID = (SELECT vb_user.vb_mobile FROM vb_user WHERE vb_user.CustID = cust)
  ORDER BY TransID DESC  LIMIT numRow OFFSET page;

	SELECT @rowCount 'total',@totalPage;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_UNPAID_ITE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_UNPAID_ITE`(IN `pageNum` int,IN `numRow` int,IN `var_search` varchar(100),IN `userId` int,IN `isPaid` tinyint(1))
BEGIN
	
	DECLARE page INT DEFAULT 0;

	SET page = (pageNum - 1) * numRow;
		
	SELECT vb_empId INTO @empId FROM vb_user WHERE vb_userId = userId;
	SELECT vb_roleType INTO @roleType FROM vb_user_role WHERE vb_roleId = (SELECT vb_admin_role FROM vb_user WHERE vb_userId = userId);

	SELECT 
		COUNT(vid.vit_iddetail) INTO @rowCount 
	FROM vb_itemdetail vid
			INNER JOIN (SELECT vit_id, vit_custID, vit_code, vit_date,vit_status FROM vb_item) AS vi 
			ON vi.vit_id = vid.vit_id 
		where 
			IF(var_search = '',TRUE,
					(vi.vit_code LIKE CONCAT('%',var_search,'%')) 
					OR (DATE_FORMAT(vi.vit_date,'%d-%b-%Y') = var_search) 
					OR (vi.vit_custID LIKE CONCAT('%',var_search,'%')) 
					OR (vi.vit_custID IN (SELECT var_id from ame_mt_cus WHERE var_id = vi.vit_custID AND var_name LIKE CONCAT('%',var_search,'%')))
					OR vid.vit_itemId LIKE CONCAT('%',var_search,'%'))
			AND IF(@roleType = 1, TRUE, vi.vit_custID IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId))
			AND IF(isPaid = 0,(vid.tin_isPaid = isPaid AND vi.vit_status IN (5,6)),(vid.tin_isPaid = isPaid  AND vi.vit_status IN (5,6)));
	
	SELECT
		vid.vit_iddetail 'vitDetailId',
		vid.vit_link 'vitLink',
		vid.vit_photo 'vitPhoto',
		vid.vit_qty 'vitQty',
		vid.vit_amountDollar 'vitAmtUSD',
		vid.vit_totalProductFeeYan 'vitAmtCNY',
		vid.vit_lcFeeDollar 'vitLCFeeUSD',
		vid.vit_lcFeeYan 'vitLCFeeCNY',
		vi.vit_exChina 'vitExChina',
		COALESCE(vid.vit_vendor,'') 'vitVender',
    vid.vit_type 'vitType',
		COALESCE(vid.vit_someqty,0) 'vitSomeQty',
		COALESCE(vid.vit_itemId,'') 'vitItemId',
		vid.tin_isPaid 'vitIsPaid',
		DATE_FORMAT(vi.vit_date,'%d-%b-%Y') 'date',
		vi.vit_code 'ofCode',
		vi.vit_custID 'custId',
		(SELECT var_name FROM ame_mt_cus WHERE var_id = vi.vit_custID) 'custName',
		COALESCE((SELECT CONCAT('[',GROUP_CONCAT(CONCAT_WS(',', CONCAT('{"title":"',ProTitle,'","name":"',ProName,'","key":"',ProKey,'","image":"',COALESCE(ProImage,''),'"}')) SEPARATOR ','),']') FROM vb_itemproperty  WHERE Vit_id = vid.vit_iddetail),'') 'prop'
	FROM vb_itemdetail vid 
		INNER JOIN (SELECT vit_id, vit_custID, vit_code, vit_date, vit_status,vit_exChina FROM vb_item) AS vi 
			ON vi.vit_id = vid.vit_id
	WHERE 
		IF(var_search = '',TRUE,
					(vi.vit_code LIKE CONCAT('%',var_search,'%')) 
					OR (DATE_FORMAT(vi.vit_date,'%d-%b-%Y') = var_search) 
					OR (vi.vit_custID LIKE CONCAT('%',var_search,'%')) 
					OR (vi.vit_custID IN (SELECT var_id from ame_mt_cus WHERE var_id = vi.vit_custID AND var_name LIKE CONCAT('%',var_search,'%')))
					OR vid.vit_itemId LIKE CONCAT('%',var_search,'%'))
		AND IF(@roleType = 1, TRUE, vi.vit_custID IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId))
		AND IF(isPaid = 0,(vid.tin_isPaid = isPaid AND vi.vit_status IN (5,6)),(vid.tin_isPaid = isPaid  AND vi.vit_status IN (5,6)))
	  ORDER BY (CASE WHEN isPaid = 0 THEN vid.vit_iddetail END) ASC,
							(CASE WHEN isPaid = 1 THEN vid.vit_iddetail END) DESC
		LIMIT numRow OFFSET page;

	SELECT 
		COUNT(vid.vit_iddetail) INTO @paidCounts
	FROM vb_itemdetail vid 
		INNER JOIN (SELECT vit_id, vit_status FROM vb_item) AS vi ON vid.vit_id = vi.vit_id WHERE vid.tin_isPaid = 1  AND vi.vit_status IN (5,6);

SELECT 
		COUNT(vid.vit_iddetail) INTO @pendingCounts
	FROM vb_itemdetail vid 
		INNER JOIN (SELECT vit_id, vit_status FROM vb_item) AS vi ON vid.vit_id = vi.vit_id WHERE vid.tin_isPaid = 0 AND vi.vit_status IN (5,6);

	SELECT @rowCount 'rowCounts', @pendingCounts 'pendingCounts', @paidCounts 'paidCounts';
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_GET_USER_ROLES`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_GET_USER_ROLES`(IN `pageNum` int,IN `numRow` int,IN `search` varchar(255))
    DETERMINISTIC
BEGIN
	
	DECLARE page INT DEFAULT 0;

	SET page = (pageNum - 1) * numRow;
	
	SELECT COUNT(vb_roleId) 'rowCounts' FROM vb_user_role ur WHERE vb_roleName LIKE CONCAT('%',search,'%');

	SELECT
		ur.vb_roleId 'roleId',
		ur.vb_roleName 'roleName',
		(SELECT vb_typeName FROM vb_user_role_type WHERE vb_typeId = ur.vb_roleType) 'typeName'
	FROM 
		vb_user_role ur WHERE ur.vb_roleName LIKE CONCAT('%',search,'%') LIMIT numRow OFFSET page;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_IMG_WEB_PATH`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_IMG_WEB_PATH`()
    DETERMINISTIC
BEGIN
	
	SELECT var_name INTO @pathUpload FROM ame_sys_config WHERE var_id = 'IMG_UP_WEB';
	SELECT var_name INTO @pathDownload FROM ame_sys_config WHERE var_id = 'IMG_GE_WEB';
	SELECT @pathUpload 'ImagePathWeb', @pathDownload 'ImageGetWeb';
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_INSERT_TABLE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_INSERT_TABLE`(IN tablename varchar(255), IN col varchar(500), IN val text)
    DETERMINISTIC
BEGIN

	SET @SQL1 = CONCAT("INSERT INTO ",tablename," ",col," VALUES ", val ," ;");
	#SET @SQL1 = REPLACE(@SQL1, 'tempid', NumberPre);
	PREPARE St FROM @SQL1;
	EXECUTE St;
	DEALLOCATE PREPARE St;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_ITEM_RE_CREATE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_ITEM_RE_CREATE`(IN cusid varchar(255),IN shipTo varchar(255),IN empID varchar(255),IN exDollar varchar(255), IN exKhmer varchar(255), IN exChina varchar(255),IN deposit varchar(255), IN balance varchar(255), IN chinaWarehouse varchar(255), IN camWarehouse varchar(255), IN noOfDay varchar(255),IN totalProductFeeINUsd varchar(255),IN totalProductFeeINYan varchar(255),IN serviceChargeUsd varchar(255),IN totalLCFeeINUsd varchar(255),IN totalINUsd DOUBLE,IN `statusitem` varchar(100),IN `type` varchar(255), IN itemDetail text,IN `cart` varchar(255))
    DETERMINISTIC
BEGIN
		DECLARE NumberPre INT(11) DEFAULT "";
    
		IF cusid <> '' AND itemDetail != '' THEN

			INSERT INTO vb_item (vit_custID,vit_code, vit_shipTo,vit_empID, vit_exDollar,vit_exKhmer,
													vit_exChina,vit_deposit,vit_balance,vit_chinaWarehouse,vit_camWarehouse, vit_noOfDay,
													vit_totalProductFeeINUsd,vit_totalProductFeeINYan,vit_serviceChargeUsd,
													vit_totalLCFeeINUsd,vit_totalINUsd,vit_date,vit_status,vit_type
													)
									VALUES	(cusid,vb_generateCode(cusid),shipTo,empID,exDollar,exKhmer,
													exChina,deposit,balance,chinaWarehouse,camWarehouse,noOfDay,
													totalProductFeeINUsd,totalProductFeeINYan,serviceChargeUsd,totalLCFeeINUsd,totalINUsd
													,NOW(),`statusitem`,`type`);

			SET NumberPre = LAST_INSERT_ID();

		
			SET @SQL1 = CONCAT("INSERT INTO vb_itemdetail(vit_id, vit_name, vit_link, vit_photo, vit_size,
														vit_color, vit_qty, vit_unitPriceYan, vit_totalProductFeeYan, vit_lcFeeYan,
														vit_chinaInvoiceCode, vit_truckingNoInChina, vit_remark, vit_unitPriceDollar, vit_amountDollar,
														vit_lcFeeDollar, vit_vendor, vit_status, vit_type, vit_document, vit_date) VALUES ", itemDetail , " ;");
			SET @SQL1 = REPLACE(@SQL1, 'tempid', NumberPre);
			PREPARE St FROM @SQL1;
			EXECUTE St;
			DEALLOCATE PREPARE St;


			IF LOWER(`type`) = "web link" THEN
					SET @SQL2 = CONCAT("DELETE FROM vb_itemcart WHERE vb_itemcart.vit_id IN (",cart,")  AND vb_itemcart.custId = '",cusid,"'; ");
					PREPARE St2 FROM @SQL2;
					EXECUTE St2;
					DEALLOCATE PREPARE St2;
			END IF;
	
			CALL vb_message(1,'item');
		ELSE
			CALL vb_message(2,'item');
		END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_LIST_CUST`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_LIST_CUST`(pageNum INT(10), numRow INT(10),fil varchar(200), sea varchar(200),IN `userId` int(11))
    DETERMINISTIC
BEGIN	
	
	DECLARE page INT(11) DEFAULT 0;

	SELECT vb_roleType INTO @roleType FROM vb_user_role WHERE vb_roleId = (SELECT vb_admin_role FROM vb_user WHERE vb_userId = userId);
	SELECT vb_empId INTO @empId FROM vb_user WHERE vb_userId = userId;

	SELECT COUNT(ex.var_id) INTO @rowCount FROM ame_mt_cus ex
	INNER JOIN ame_mt_pri_cod pc ON pc.var_id = ex.var_pricecode
	WHERE IF(fil <> '' AND sea <> '',IF(fil='id',ex.var_id LIKE CONCAT('%',sea,'%'),
				IF(fil='name',ex.var_name LIKE CONCAT('%',sea,'%'),
        IF(fil='tel',ex.var_tel1 LIKE CONCAT('%',sea,'%'),
        IF(fil='email',ex.var_email LIKE CONCAT('%',sea,'%'),true)))), true)
				AND IF( fil = '' AND sea <> '',
						ex.var_id LIKE CONCAT('%',sea,'%') 
						OR ex.var_name LIKE CONCAT('%',sea,'%')
						OR ex.var_tel1 LIKE CONCAT('%',sea,'%')
						OR ex.var_email LIKE CONCAT('%',sea,'%'), TRUE)
				AND IF(@roleType = 1, TRUE, ex.var_id IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId))
				AND ex.tin_inactive = 0;
	SET @totalPage = CEIL(@rowCount / numRow);
	SET page = (pageNum - 1) * numRow;

  SELECT 
		ex.var_id 'custId',
		ex.var_name 'custName',
		COALESCE(ex.var_name_other,"") 'custNameOther',
		COALESCE(ex.var_name_con,"") 'custContactName',
		COALESCE(ex.var_tel1) 'custTel1',
		COALESCE(ex.var_tel2,"") 'custTel2',
		ex.var_email 'custEmail',
		pc.var_id 'priceCode',
		pc.var_name 'priceDes',
		(SELECT ame_mt_cus_emp.var_empid FROM ame_mt_cus_emp WHERE ex.var_id = ame_mt_cus_emp.var_cusid) 'empId',
		(SELECT ame_mt_emp.var_name FROM ame_mt_emp WHERE ame_mt_emp.var_id = empId) 'empName',
		pc.dou_vb_serviceCharge 'serviceCharge',
		IF(@roleType = 1, 1, 0) 'editable',
		IF((SELECT vb_userId FROM vb_user WHERE CustID = ex.var_id) IS NULL, 0,1) 'hasUser'
	FROM ame_mt_cus ex
	INNER JOIN ame_mt_pri_cod pc ON pc.var_id = ex.var_pricecode
	WHERE IF(fil <> '' AND sea <> '',IF(fil='id',ex.var_id LIKE CONCAT('%',sea,'%'),
				IF(fil='name',ex.var_name LIKE CONCAT('%',sea,'%'),
        IF(fil='tel',ex.var_tel1 LIKE CONCAT('%',sea,'%'),
        IF(fil='email',ex.var_email LIKE CONCAT('%',sea,'%'),true)))), true)
				AND IF( fil = '' AND sea <> '',
						ex.var_id LIKE CONCAT('%',sea,'%') 
						OR ex.var_name LIKE CONCAT('%',sea,'%')
						OR ex.var_tel1 LIKE CONCAT('%',sea,'%')
						OR ex.var_email LIKE CONCAT('%',sea,'%'), TRUE)
				AND IF(@roleType = 1, TRUE, ex.var_id IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId))
				AND ex.tin_inactive = 0
  LIMIT numRow OFFSET page;
  SELECT COALESCE(@rowCount,0) 'rowCounts',@totalPage 'totalPages',@roleType 'roleType';

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_LIST_CUST_BALAN`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_LIST_CUST_BALAN`(fil varchar(200), sea varchar(200),pageNum INT(10), numRow INT(10))
    DETERMINISTIC
BEGIN	
	DECLARE page INT(11) DEFAULT 0;

	SELECT 
		COUNT(c.var_id) INTO @rowCount 
	FROM tblcard ex 
	RIGHT JOIN (SELECT var_id,var_name,var_tel1,var_email FROM ame_mt_cus) c ON ex.CustID = c.var_id
	WHERE IF(fil <> '' AND sea <> '',IF(fil='id',c.var_id LIKE CONCAT('%',sea,'%'),
				IF(fil='name',c.var_name LIKE CONCAT('%',sea,'%'),
        IF(fil='tel',c.var_tel1 LIKE CONCAT('%',sea,'%'),
        IF(fil='email',c.var_email LIKE CONCAT('%',sea,'%'),true)))), true)
				AND IF(fil = '' AND sea <> '',
						ex.CustID LIKE CONCAT('%',sea,'%')
						OR c.var_name LIKE CONCAT('%',sea,'%')
						OR c.var_tel1 LIKE CONCAT('%',sea,'%')
						OR c.var_email LIKE CONCAT('%',sea,'%')
						OR CardID LIKE CONCAT('%',sea,'%')
				,TRUE);
	SET @totalPage = CEIL(@rowCount / numRow);
	SET page = (pageNum - 1) * numRow;

  SELECT 
		CardID,
		c.var_id,
		c.var_name,
		c.var_tel1,
		c.var_email,
		CardBalance,
		CardTypeID 
	FROM tblcard ex 
	RIGHT JOIN (SELECT var_id,var_name,var_tel1,var_email FROM ame_mt_cus) c ON ex.CustID = c.var_id
  WHERE
			IF(fil <> '' AND sea <> '',IF(fil='id',c.var_id LIKE CONCAT('%',sea,'%'),
				IF(fil='name',c.var_name LIKE CONCAT('%',sea,'%'),
        IF(fil='tel',c.var_tel1 LIKE CONCAT('%',sea,'%'),
        IF(fil='email',c.var_email LIKE CONCAT('%',sea,'%'),true)))), true)
				AND IF(fil = '' AND sea <> '',
						ex.CustID LIKE CONCAT('%',sea,'%')
						OR c.var_name LIKE CONCAT('%',sea,'%')
						OR c.var_tel1 LIKE CONCAT('%',sea,'%')
						OR c.var_email LIKE CONCAT('%',sea,'%')
						OR CardID LIKE CONCAT('%',sea,'%')
				,TRUE) 
  LIMIT numRow OFFSET page;
  SELECT COALESCE(@rowCount,0) 'total',@totalPage;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_LIST_CUST_PUR_AME`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_LIST_CUST_PUR_AME`()
    DETERMINISTIC
BEGIN
 SELECT vpTransactionNo,custId FROM vb_purchasecredit WHERE custId = cust AND LOWER(vpStatus) = "deposit";
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_LIST_DEFAULT_DATA`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_LIST_DEFAULT_DATA`()
    DETERMINISTIC
BEGIN
	SELECT COALESCE(var_id,"") 'custGroupId',COALESCE(var_name,"") 'custGroupName' FROM ame_mt_cus_gro;
	SELECT COALESCE(CardTypeID,"") 'CardTypeId',COALESCE(CardTypeName,"") 'CardTypeName' FROM tblres_cardtype;
  SELECT var_id 'accId',var_name 'accName' FROM ame_mt_acc;
  SELECT var_id 'module',var_modid 'numberPrefix' FROM ame_sys_prefix_custom WHERE var_type = "AR-PY";
	SELECT var_id 'venid',var_name 'venname' FROM ame_mt_ven;
  SELECT COALESCE(CustGroup,"") 'CustGroup',COALESCE(CustCard,"") 'CardType',COALESCE(AccountCash,'') 'AccountCash', COALESCE(AccountID,'') 'AccountId',COALESCE(RecModule,'') 'Module',COALESCE(VenLC,"") 'Venlc',COALESCE(VenService,"") 'Venservice',COALESCE(ItemAvailable,'') "ExpriedItem"  FROM vb_defaultcustomer LIMIT 1;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_LIST_ITEM_REFUND`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_LIST_ITEM_REFUND`(IN search varchar(255),IN valu varchar(255),IN pageNum INT(10), IN numRow INT(10))
    DETERMINISTIC
BEGIN

		DECLARE page INT DEFAULT 0;
  
		SELECT COUNT(ex.vir_id) INTO @rowCount from vb_refunditem ex  WHERE IF(search = '', true, search = valu);
		SET @totalPage = CEIL(@rowCount / numRow);
		SET page = (pageNum - 1) * numRow;

		SELECT vb_refunditem.vir_id,
				vb_refunditem.vir_custId,
				vb_refunditem.vir_empId,
				vb_refunditem.vir_itemId,
				vb_refunditem.vir_salId,
				vb_refunditem.vir_orderId,
				vb_refunditem.vir_recId,
				vb_refunditem.vir_amount,
				vb_refunditem.vir_reason,
				vb_refunditem.vir_date,
				COALESCE(vb_refunditem.vir_link,""),
				COALESCE(vb_refunditem.vir_image,""),
				COALESCE(vb_refunditem.vir_status,""),
				COALESCE(vb_refunditem.vir_approved_by,"")
		FROM vb_refunditem WHERE IF(search = '', true, search = valu)  ORDER BY vir_date DESC
    LIMIT numRow OFFSET page;

		SELECT @rowCount 'total',@totalPage;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_LIST_NOTI_BY_CUST`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_LIST_NOTI_BY_CUST`(IN cust varchar(255),IN type varchar(255),IN pageNum INT(10), IN numRow INT(10))
    DETERMINISTIC
BEGIN

		DECLARE page INT DEFAULT 0;
  
		SELECT COUNT(ex.cndid) INTO @rowCount from vb_notification_detail ex  WHERE ex.cnid = type AND ex.cncustid = cust;
		SET @totalPage = CEIL(@rowCount / numRow);
		SET page = (pageNum - 1) * numRow;

		SELECT cndid, cnid,cncustid, cndescription,cndate,COALESCE(CNSeen,0) FROM vb_notification_detail WHERE cnid = type AND cncustid = cust ORDER BY cndate DESC
    LIMIT numRow OFFSET page;

		SELECT @rowCount 'total',@totalPage;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_LIST_NOTI_DET`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_LIST_NOTI_DET`(IN cid varchar(255), IN nid varchar(255))
    DETERMINISTIC
BEGIN

	SELECT CNDID,CNID,CustID,CNDDescription,CNDate FROM vb_notification_detail WHERE CustID = cid AND CNID = nid;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_LIST_PRICE_CODE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_LIST_PRICE_CODE`(IN `pageNum` int(11),IN `numRow` int(11),IN  `search` varchar(255))
    DETERMINISTIC
BEGIN
	
	DECLARE page INT DEFAULT 0;

	SET page = (pageNum - 1) * numRow;

	SELECT COUNT(var_id) INTO @rowCounts FROM ame_mt_pri_cod WHERE IF(search = '', true, var_id LIKE CONCAT('%',search,'%') OR var_name LIKE CONCAT('%',search,'%') OR dou_vb_serviceCharge = CAST(search AS DECIMAL(10, 2))) AND tin_inactive = 0;

	SELECT 
		pc.var_id 'code', 
		COALESCE(pc.var_name,'') 'des', 
		pc.tin_vb_isDefault 'isDefault', 
		pc.dou_vb_serviceCharge 'serviceCharge' 
	FROM ame_mt_pri_cod pc WHERE 
		IF(search = '', true, 
			pc.var_id LIKE CONCAT('%',search,'%') 
			OR pc.var_name LIKE CONCAT('%',search,'%') 
			OR (pc.dou_vb_serviceCharge = CAST(search AS DECIMAL(10, 2)) AND CAST(search AS DECIMAL(10, 2)) > 0)) 
		AND tin_inactive = 0
	LIMIT numRow OFFSET page ;
	
	SELECT @rowCounts 'rowCounts';
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_LIST_PUR_CRD`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_LIST_PUR_CRD`(IN cid varchar(255),IN pageNum INT(10), IN numRow INT(10))
    DETERMINISTIC
BEGIN

	DECLARE page INT(11) DEFAULT 0;

	SELECT COUNT(ex.custId) INTO @rowCount from vb_purchasecredit ex  WHERE IF(cid = 'all', TRUE, ex.custId = cid);
	SET @totalPage = CEIL(@rowCount / numRow);
	SET page = (pageNum - 1) * numRow;

  SELECT vpid,custId,vpAccountId,vpAmount,vpDate,VB_FUN_HTTP(custId,"/document/",vpDocument),vpRemark,vpStatus,vpType,vpCDate,vpTransactionNo,VB_FUN_CUST_BAL(custId) FROM vb_purchasecredit WHERE IF(cid = 'all', TRUE, custId = cid) ORDER BY vpid DESC LIMIT numRow OFFSET page;
	SELECT COALESCE(@rowCount,0) 'total',@totalPage;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_LIST_PUR_CRD_ADMIN`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_LIST_PUR_CRD_ADMIN`(IN `pageNum` int(11),IN `numRow` int(11),IN `search` varchar(255),IN stat varchar(200))
    DETERMINISTIC
BEGIN
	DECLARE page INT(11) DEFAULT 0;

	SELECT COUNT(ex.vpid) INTO @rowCount from vb_purchasecredit ex  WHERE ex.vpStatus = stat AND
					IF(search = '',true,(ex.vpTransactionNo LIKE CONCAT('%',search,'%')) 
					OR (DATE_FORMAT(ex.vpDate,'%d-%b-%Y') = search)
					OR (DATE_FORMAT(ex.vpCDate,'%d-%b-%Y') = search)
					OR (ex.custId LIKE CONCAT('%',search,'%')) 
					OR (ex.custId IN (SELECT var_id from ame_mt_cus WHERE var_id = ex.custId AND var_name LIKE CONCAT('%',search,'%')))) ;
	-- SET @totalPage = CEIL(@rowCount / numRow);
	SET page = (pageNum - 1) * numRow;
	
  SELECT 
		pd.vpid,
		pd.custId,
		(SELECT var_name FROM ame_mt_cus WHERE var_id = pd.custId) 'custName',
		VB_FUN_CUST_BAL(pd.custId) 'custBalance',
		tblAccType.vaid 'accId',
		tblAccType.accName 'accName',
		tblAccType.accNumber 'accNumber',
		pd.vpAmount,
		DATE_FORMAT(pd.vpDate,'%d-%b-%Y %h:%m %p') 'vpDate',
		VB_FUN_HTTP(pd.custId,"/document/",pd.vpDocument) 'vpDocument',
		pd.vpRemark,
		pd.vpStatus,
		pd.vpType,
		DATE_FORMAT(pd.vpCDate,'%d-%b-%Y %h:%m %p') 'vpCDate',
		pd.vpTransactionNo,
		(SELECT accName FROM vb_account_type WHERE vaid = vpAccountId) 'accountName'
		FROM 
			vb_purchasecredit pd
		INNER JOIN (SELECT vaid, accName, accNumber FROM vb_account_type) tblAccType 
		ON tblAccType.vaid = pd.vpAccountId
		WHERE pd.vpStatus = stat AND IF(search = '',true,(pd.vpTransactionNo LIKE CONCAT('%',search,'%')) 
					OR (DATE_FORMAT(pd.vpDate,'%d-%b-%Y') = search)
					OR (DATE_FORMAT(pd.vpCDate,'%d-%b-%Y') = search)
					OR (pd.custId LIKE CONCAT('%',search,'%')) 
					OR (pd.custId IN (SELECT var_id from ame_mt_cus WHERE var_id = pd.custId AND var_name LIKE CONCAT('%',search,'%'))))
		ORDER BY pd.vpDate DESC LIMIT numRow OFFSET page;

		SELECT @rowCount 'rowCount';

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_LIST_REFUND_ITEM`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_LIST_REFUND_ITEM`(stat varchar(200), search varchar(200), pageNum INT(10), numRow INT(10),userId INT(10))
    DETERMINISTIC
BEGIN

	DECLARE page INT DEFAULT 0;

	SELECT vb_roleType INTO @roleType FROM vb_user_role WHERE vb_roleId = (SELECT vb_admin_role FROM vb_user WHERE vb_userId = userId);
	SELECT vb_empId INTO @empId FROM vb_user WHERE vb_userId = userId;
  
  SELECT 
		COUNT(ex.vir_id) INTO @rowCount 
	from vb_refunditem ex  
	WHERE ex.vir_status = stat 
		AND IF(search = '',TRUE, ex.vir_custId LIKE CONCAT("%",search,"%") OR ex.vir_itemId LIKE CONCAT("%",search,"%") OR ex.vir_ofcode LIKE CONCAT("%",search,"%") OR ex.vir_salId LIKE CONCAT("%",search,"%"))
		AND IF(@roleType = 1, TRUE, ex.vir_custId IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId));

  SET @totalPage = CEIL(@rowCount / numRow);
  SET page = (pageNum - 1) * numRow;
	
	SELECT vir_id,vir_orderId,vir_custId,vir_itemId,vir_salId,vir_recId,vir_amount,vir_reason,vir_refauth,vir_empId,vir_image,vir_link,vir_date,vir_status,vir_approved_by,COALESCE(vir_ofcode,''),
	(SELECT ame_mt_emp.var_name FROM ame_mt_emp WHERE ame_mt_emp.var_id = vb_refunditem.vir_empId),(SELECT ame_mt_cus.var_name FROM ame_mt_cus WHERE ame_mt_cus.var_id = vb_refunditem.vir_custId),
	vir_lc,vir_service,vir_total
	FROM vb_refunditem  WHERE vir_status = stat 
	AND IF(search = '',TRUE, vir_custId LIKE CONCAT("%",search,"%") OR vir_itemId LIKE CONCAT("%",search,"%") OR vir_ofcode LIKE CONCAT("%",search,"%") OR vir_salId LIKE CONCAT("%",search,"%"))
	AND IF(@roleType = 1, TRUE, vir_custId IN (SELECT var_cusid FROM ame_mt_cus_emp WHERE var_empid = @empId))
	ORDER BY vir_id DESC
  LIMIT numRow OFFSET page;

	SELECT @rowCount 'total',@totalPage;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_MEG`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_MEG`(IN scode varchar(255), IN processid varchar(255))
    DETERMINISTIC
BEGIN
	SELECT title,CONCAT(msg1,' ',processid, ' ', msg2) AS 'description' FROM vb_message WHERE `mid` = scode;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_MODULE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_MODULE`()
    DETERMINISTIC
BEGIN
	
	SELECT
		ModuleID 'moduleId',
		Remarks 'moduleName',
		false 'isAccessible'
	FROM vb_numberprefix WHERE Hide = 0 ORDER BY sort ASC;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_MSG`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_MSG`(IN scode varchar(255), IN processid varchar(255))
    DETERMINISTIC
BEGIN
	SELECT title,CONCAT(msg1,' ',processid, ' ', msg2) AS 'description' FROM vb_message WHERE `mid` = scode;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_NOTI_DET`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_NOTI_DET`(IN nid varchar(255),IN cid varchar(255), IN cdesc varchar(255))
    DETERMINISTIC
BEGIN
	
		INSERT INTO vb_notification_detail (CNID,CNCustID,CNDescription,CNDate,CNSeen) VALUE (nid,cid,cdesc,NOW(),0);

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_PAID_ITE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_PAID_ITE`(IN `itemId` int(11),IN `ofCode` varchar(255))
BEGIN

	UPDATE vb_itemdetail SET tin_isPaid = 1 WHERE vit_iddetail = itemId;

	SELECT 
		COALESCE(SUM(vid.vit_qty),0),
		COALESCE(SUM(CASE WHEN vid.tin_isPaid = 1 THEN vid.vit_qty END),0)
		INTO @allQty, @paidQty
	FROM vb_itemdetail vid 
		INNER JOIN (SELECT vit_id, vit_code FROM vb_item) AS vi 
			ON vi.vit_id = vid.vit_id 
	WHERE vi.vit_code = ofCode;
	#SELECT @allQty, @paidQty;
	
	IF @allQty = @paidQty THEN 
		UPDATE vb_item SET vit_status = 6 WHERE vit_code = ofCode;
	END IF;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_PICKUP_NOTI`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_PICKUP_NOTI`(IN `custId` varchar(255),IN `orderCode` varchar(255),IN `genCode` varchar(255))
    DETERMINISTIC
BEGIN
	
	UPDATE ame_ar_so SET int_vb_code = genCode  WHERE var_cusid = custId AND var_ref = orderCode;  
	SELECT CONCAT(msg1,' ',(SELECT c.var_name from ame_mt_cus c WHERE c.var_id = custId),' ',msg2,' ',orderCode,' ',msg3,' ',genCode,' ',msg4) INTO @msg FROM vb_notification_message WHERE id = 4;
	INSERT INTO vb_notification_detail (CNID, CNCustID,CNDescription, CNDate, CNSeen) VALUES('NT-00003',custId,@msg,NOW(),0);
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_PUR_ITEM_AME`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_PUR_ITEM_AME`(van varchar(200),cus varchar(200),orderId INT(11))
    DETERMINISTIC
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE num1 INT(11) DEFAULT 0;
	DECLARE NumberPre VARCHAR(200) DEFAULT "";
	DECLARE empstr VARCHAR(200) DEFAULT "";
  DECLARE numberze INT(11) DEFAULT 0;
	DECLARE varid VARCHAR(300);
	DECLARE varname Text;
	DECLARE varuom VARCHAR(300);
	DECLARE venid VARCHAR(200);
	DECLARE vargroup VARCHAR(200);
	DECLARE varprice DOUBLE;
	DECLARE varsub DOUBLE;
	DECLARE varqty DOUBLE;
	DECLARE cur2 CURSOR FOR SELECT var_id,var_name,var_groupid,var_uomid,var_venid,(SELECT ame_mt_ite_pri.dou_price FROM ame_mt_ite_pri WHERE ame_mt_ite_pri.var_id = ame_mt_ite.var_id AND ame_mt_ite_pri.var_pricecode = VB_FUN_GET_CUS_CODE(cus)) FROM ame_mt_ite WHERE var_venid = van  AND tin_no_sal = 0 AND int_vb_orderId = orderId ORDER BY var_venid;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	
	OPEN cur2;
	read_loop1: LOOP
    FETCH cur2 INTO  varid,varname,vargroup,varuom,venid,varprice;
    IF done THEN
      LEAVE read_loop1;
    END IF;
		IF !ISNULL(varid) THEN
		
				SET empstr = "";
				SET numberze = 0;
				SET varqty= 0;
				SET NumberPre = AME_FUN_PREFIX("AP-PO",1);
				SELECT vit_camWarehouse,vit_code INTO @cambodia,@ofcode FROM vb_item WHERE vit_id = orderId;

				INSERT INTO ame_ap_po (var_id, var_type,var_ref, var_venid, var_status, var_pmtstatus, var_cur, var_create, dat_date,dat_due,dat_create,dou_sub,dou_net, dou_rate, dou_funsub, dou_funnet,var_claid,var_term,var_desc,var_shipid,var_shipaddr,dou_taxdol1)
				SELECT NumberPre,"Purchase Inventory",@ofcode, venid,"Open","Unpaid","USD","ADMIN",NOW(),NOW(),NOW(),VB_FUN_FNUM(SUM(varprice)),VB_FUN_FNUM(SUM(varprice)),VB_FUN_GET_EX_RATE(),VB_FUN_FNUM((varprice)),VB_FUN_FNUM(SUM(varprice)),empstr,empstr,empstr,empstr,empstr,numberze
				FROM ame_mt_ite WHERE var_groupid = cus AND var_venid = venid GROUP BY var_venid ;
				
				SELECT ame_mt_of_value.var_value INTO varqty FROM ame_mt_of_value WHERE ame_mt_of_value.var_num = varid AND ame_mt_of_value.int_id = 3;
				SET varqty = COALESCE(varqty,0);

				SET varsub = varqty * varprice;
				SET varsub = VB_FUN_FNUM(varsub);
				
				SET num1=num1+1;
				INSERT INTO ame_ap_po_det (var_id, var_type, int_line, var_itemid, var_locid, var_uomid, dou_qty, 
				dou_price, dou_sub, dou_net, dou_funprice, dou_funsub, dou_funnet, int_poline,var_claid,var_desc)
				VALUES (NumberPre,"Purchase Inventory",num1, varid,@cambodia,varuom,varqty,
				varprice,varsub,varsub,varprice,varsub,varsub,num1,empstr,empstr);
		
		END IF;
  END LOOP;
	CLOSE cur2;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_PUR_ORDER_AME`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_PUR_ORDER_AME`(IN cust varchar(255), IN orderId INT(11), listItem TEXT)
    DETERMINISTIC
BEGIN
	DECLARE done INT DEFAULT FALSE;
		
  DECLARE masters TEXT DEFAULT '';
  DECLARE details TEXT DEFAULT '';
	DECLARE NumberPre VARCHAR(200) DEFAULT "";
	DECLARE num INT;
	
	
	DECLARE varid VARCHAR(200);


	DECLARE cur2 CURSOR FOR SELECT var_id FROM ame_mt_ven ORDER BY var_id;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	
	OPEN cur2;
	read_loop1: LOOP
    FETCH cur2 INTO varid;
    IF done THEN
      LEAVE read_loop1;
    END IF;
		IF !ISNULL(varid) THEN

				CALL VB_PRO_PUR_ITEM_AME(varid,cust,orderId);
		
		END IF;
  END LOOP;
	CLOSE cur2;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_REJ_PUR`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_REJ_PUR`(IN vid INT(11))
    DETERMINISTIC
BEGIN
	Update vb_purchasecredit SET vpStatus = 'Rejected' WHERE vpid = vid;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_RPT_SALE_INV`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_RPT_SALE_INV`(IN `custId` varchar(255),IN `OFCode` varchar(255),IN `invId` varchar(255))
    DETERMINISTIC
BEGIN
	
	SELECT
		c.var_id 'custId',
		c.var_name 'custName',
		so.var_ref 'OFCode',
		so.var_empid 'empId',
		(SELECT var_name FROM ame_mt_emp e WHERE e.var_id = so.var_empid) 'empName',
		DATE_FORMAT(so.dat_date,'%d-%b-%Y') 'saleDate',
		sod.int_line 'lineNo',
		sod.var_uomid 'UOM',
		sod.dou_qty 'qty',
		sod.var_itemid 'itemId',
		ite.var_itemcode 'itemCode',
		sod.var_vb_meatype 'measure',
		sod.dou_vb_meavalue 'qtyOfMeasure',
		sod.dou_vb_meaprice 'pricePer'
	FROM
		ame_ar_in so 
		INNER JOIN 
			(SELECT cu.var_id, cu.var_name FROM ame_mt_cus cu) AS c ON c.var_id = so.var_cusid
		INNER JOIN 
			ame_ar_in_det sod ON so.var_id = sod.var_id
		INNER JOIN 
			(SELECT var_id, var_name, var_itemcode,tin_no_sal FROM ame_mt_ite) ite ON ite.var_id = sod.var_itemid
		WHERE so.var_ref = OFCode AND so.var_cusid = custId AND so.var_id = invId AND ite.tin_no_sal = 0;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_SEARCH_OF_CODE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_SEARCH_OF_CODE`(IN `pageNum` int,IN `numRow` int,IN `search` varchar(255))
    DETERMINISTIC
BEGIN

	DECLARE page INT DEFAULT 0;

	SET page = (pageNum - 1) * numRow;

	SELECT 
		ame_ar_so.var_ref 'ofCode' 
	FROM ame_ar_so 
		WHERE IF(search = '',TRUE, ame_ar_so.var_ref LIKE CONCAT('%',search,'%')) AND ame_ar_so.var_status = 'Open' AND ame_ar_so.var_ref <> '' ORDER BY dat_date DESC LIMIT numRow OFFSET page;

	SELECT COUNT(ame_ar_so.var_ref) 'rowCounts' FROM ame_ar_so WHERE IF(search = '',TRUE, ame_ar_so.var_ref LIKE CONCAT('%',search,'%')) AND ame_ar_so.var_status = 'Open' AND var_ref <> '';

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_UPDATE_EMP`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_UPDATE_EMP`(varid varchar(200),varname varchar(200),varnameother varchar(200),varnamecom varchar(200),vartel1 varchar(200),vartel2 varchar(200),varfax varchar(200),varweb varchar(200),varemail varchar(200),varadd varchar(200),vardesc varchar(200),vargroup varchar(200),varpost varchar(200),varpmttype varchar(200),varcard varchar(200),varcur varchar(200),varimg varchar(200),varinactive INT(1), varers INT(1))
BEGIN

	SELECT COUNT(var_id) INTO @vid FROM ame_mt_emp WHERE var_id = varid AND var_id <> varid;
	IF @vid <> 0 THEN
			CALL VB_PRO_MSG(4,"employee id");
	ELSE
		UPDATE ame_mt_emp 
		SET var_name = varname,
			  var_name_other = varnameother,
				var_name_con = varnamecom,
				var_tel1 = vartel1,
				var_tel2 = vartel2,
				var_fax = varfax,
				var_web = varweb,
				var_email = varemail,
				var_addr = varadd,
				var_desc = vardesc,
				var_groupid = vargroup,
				var_posid = varpost,
				var_term = '',
				var_pmttype = '',
				var_cardid = varcard,
				var_cur = varcur,
				tin_inactive = varinactive,
				tin_rep = varers
		WHERE var_id = varid;
		CALL VB_PRO_MSG(6,"employee ");
	END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_UPDATE_EXP_DATE_ITEM`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_UPDATE_EXP_DATE_ITEM`(IN cust varchar(200))
    DETERMINISTIC
BEGIN

	DECLARE done INT DEFAULT FALSE;
	DECLARE getitemiddetail VARCHAR(255);
	DECLARE getitem VARCHAR(255);
	DECLARE codev VARCHAR(255);
	DECLARE namecode VARCHAR(255);
	DECLARE noti VARCHAR(255);
 
	DECLARE m2 VARCHAR(255);
  DECLARE m1 VARCHAR(255);
	
	DECLARE cur CURSOR FOR SELECT vit_expirationDate,vit_id,vit_code FROM vb_item WHERE vb_item.vit_custID = cust AND vb_item.vit_status = 3;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


	OPEN cur;
	read_loop1: LOOP
    FETCH cur INTO getitemiddetail,getitem,codev;
    IF done THEN
      LEAVE read_loop1;
    END IF;
		IF !ISNULL(getitemiddetail) THEN
			SELECT msg1, msg2 INTO m1,m2 FROM vb_notification_message WHERE id = 5;
		  SET namecode = CONCAT(m1,codev,m2);
			SET noti = "NT-00001";
			IF DATE_FORMAT(getitemiddetail, "%Y-%m-%d %H:%i:%s") < DATE_FORMAT(NOW(),"%Y-%m-%d %H:%i:%s") THEN 
				UPDATE vb_item SET vit_status = 13,vit_type = "Item Expiration Date",vit_ordertype = 2 WHERE vit_custID = cust AND vit_status = 3 AND vit_id = getitem;
        INSERT INTO vb_notification_detail (CNID,CNCustID,CNDescription,CNDate,CNSeen) VALUES (noti,cust,namecode,NOW(),0);
			END IF;
		
		END IF;
  END LOOP;
	CLOSE cur;


	
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_UPDATE_EX_CUST_AME`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_UPDATE_EX_CUST_AME`(sal varchar(200), cust varchar(200), refer varchar(200))
    DETERMINISTIC
BEGIN
		DECLARE csr INT(11);
    SELECT vit_id INTO csr FROM vb_item WHERE vit_custID = cust AND vit_code = refer;
    UPDATE vb_item SET vit_status = 11 WHERE vit_custID = cust AND vit_id = csr;
		UPDATE vb_expensecustomer SET Amount = 0 AND Active = 0 WHERE CustID = cust AND SalID = sal AND DReference = refer;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_UPDATE_ITE_MEASURE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_UPDATE_ITE_MEASURE`(IN `itemCode` varchar(100),IN `measure` varchar(100),IN `qty` double)
    DETERMINISTIC
BEGIN
	
	UPDATE 
		ame_ar_so_det
	SET var_vb_meatype = measure,
		dou_vb_meavalue = qty,
		dou_vb_meaprice = (SELECT m.dou_price FROM vb_measure m WHERE m.var_id = measure)
	WHERE var_itemid = (SELECT ite.var_id FROM ame_mt_ite ite WHERE ite.var_itemcode = itemCode);

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_UPDATE_PICKUP_STS`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_UPDATE_PICKUP_STS`(IN `statusId` int,IN `ofCode` varchar(255),IN `custId` varchar(100),IN `saleId` varchar(100))
BEGIN
	
	SET @pickCode = LPAD(FLOOR(RAND() * 999999.99), 6, '0');
	UPDATE vb_item SET vit_status = statusId WHERE vit_code = ofCode;
	CALL VB_PRO_PICKUP_NOTI(custId,ofCode,@pickCode);

	SET @balance = VB_FUN_CAL_SAL(saleId);
	
	IF @balance > 0 THEN
		SELECT CONCAT(msg1,' ',(SELECT c.var_name from ame_mt_cus c WHERE c.var_id = custId),' ',msg2,' ',@balance,' ',msg3,ofCode,msg4) INTO @msg FROM vb_notification_message WHERE id = 6;
		INSERT INTO vb_notification_detail (CNID, CNCustID,CNDescription, CNDate, CNSeen) VALUES('NT-00004',custId,@msg,NOW(),0);
	END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_UPDATE_PRI_COD`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_UPDATE_PRI_COD`(IN `priceCode` varchar(11),IN `priceName` varchar(255),IN `serviceCharge` double,IN `isDefault` tinyint)
BEGIN

	SELECT SQL_CALC_FOUND_ROWS var_id INTO @var_id FROM ame_mt_pri_cod WHERE tin_vb_isDefault = 1 AND tin_inactive = 0;

	IF FOUND_ROWS() > 0 AND isDefault = 1 THEN
		Update ame_mt_pri_cod SET var_name = priceName, dou_vb_serviceCharge = serviceCharge, tin_vb_isDefault = isDefault WHERE var_id = priceCode;
		Update ame_mt_pri_cod SET tin_vb_isDefault = 0 WHERE var_id <> priceCode;
	ELSE
		Update ame_mt_pri_cod SET var_name = priceName, dou_vb_serviceCharge = serviceCharge WHERE var_id = priceCode;
	END IF;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_UPDATE_PUR_CUST_AME`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_UPDATE_PUR_CUST_AME`(tran varchar(200), cust varchar(200),amt double)
    DETERMINISTIC
BEGIN
	DECLARE codes VARCHAR(255);

	SET codes = CONCAT("Respect customer:",cust,", your recharge has been credited, the amount credited:",amt,". ");
	UPDATE vb_purchasecredit SET vb_purchasecredit.vpStatus = "Has been accounted for" WHERE vb_purchasecredit.vpTransactionNo = tran AND vb_purchasecredit.custId = cust;
  INSERT INTO vb_notification_detail (CNID,CNCustID,CNDescription,CNDate,CNSeen) VALUES ("NT-00002",cust,codes,NOW(),0);
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_UPDATE_REFUND_ITEM`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_UPDATE_REFUND_ITEM`(type varchar(200), item varchar(200), tranid varchar(200), appby varchar(200))
    DETERMINISTIC
BEGIN
		DECLARE subt DOUBLE;
		DECLARE cusbalan DOUBLE;
		DECLARE prefix VARCHAR(200);
		DECLARE mobile VARCHAR(200);
		DECLARE actionBy VARCHAR(200);
		
		DECLARE feeusd DOUBLE;
		DECLARE feeyan DOUBLE;
		DECLARE serusd DOUBLE;
		DECLARE feelc DOUBLE;
		DECLARE prefixRefund VARCHAR(200);

		SELECT vir_amount,vir_recId,vir_salId,vir_custId,vir_ofcode,vir_link,vir_lc,vir_service,vir_total INTO @amou,@rece,@sal,@custid,@ofcode,@link,@lc,@service,@total FROM vb_refunditem WHERE vir_itemId = item AND vir_id = tranid;
		SELECT var_status INTO @st FROM ame_ar_py WHERE var_id = @rece;
		SELECT COALESCE(vb_empId,"") INTO actionBy FROM vb_user WHERE vb_userName = appby;
		
		IF type = "Approved" THEN 
					IF @st = "Posted" THEN
						

							-- Default get VB
							UPDATE vb_refunditem SET vir_status = "Approved", vir_approved_by = actionBy WHERE vir_itemId = item AND vir_id = tranid;
							UPDATE vb_itemdetail SET vit_refund = 2 WHERE vit_itemId = item;

						
							-- Sale order AM<E
							SELECT dou_sub INTO @subtotal FROM ame_ar_so WHERE var_id = @sal;
							SET subt = @subtotal - @total;
							UPDATE ame_ar_so SET dou_sub = subt, dou_net = subt,dou_funsub = subt,dou_funnet = subt WHERE ame_ar_so.var_id = @sal;
							DELETE FROM ame_ar_so_det WHERE ame_ar_so_det.var_id = @sal AND ame_ar_so_det.var_itemid = item;
							
							-- Card POS
							SELECT vb_user.vb_mobile INTO mobile FROM vb_user WHERE vb_user.CustID = @custid;
							SET cusbalan = VB_FUN_CUST_BAL(@custid) + @total;

							SET @SQL2 = CONCAT("UPDATE tblcard SET tblcard.CardBalance = ",cusbalan," WHERE tblcard.CustID = '",@custid,"' ;");
							PREPARE St2 FROM @SQL2;
							EXECUTE St2;
							DEALLOCATE PREPARE St2;

							
							SET prefix = AME_FUN_PREFIX("Card-Trans",1);
							INSERT INTO tblcardtransactionhistory (TransID,CardID,Date,TransType,Amount,SalID) VALUES (prefix,mobile,NOW(),'Refund',@total,@ofcode);
							

							-- Prepayment AME
							-- UPDATE ame_ar_py SET dou_amt = subt, dou_net = subt, dou_funamt = subt, dou_funnet = subt WHERE var_id = @rece;
							-- UPDATE ame_ar_py_det SET dou_amt = subt, dou_funamt = subt WHERE var_id = @rece;
							
							SELECT vb_itemdetail.vit_id INTO @vid FROM vb_itemdetail WHERE vb_itemdetail.vit_itemId = item;
							SELECT COUNT(vb_itemdetail.vit_refund) INTO @crefun FROM vb_itemdetail WHERE vb_itemdetail.vit_refund = 2 AND vb_itemdetail.vit_id = @vid;
							SELECT COUNT(vb_itemdetail.vit_refund) INTO @citem FROM vb_itemdetail WHERE vb_itemdetail.vit_id = @vid;

							IF @crefun = @citem THEN
									UPDATE vb_item SET vb_item.vit_status = 13, vb_item.vit_orderType = 7 WHERE vb_item.vit_id = @vid;
							END IF;
					
							SELECT vb_exchangerate.China INTO @chi FROM vb_exchangerate WHERE vb_exchangerate.active = 1;
							SELECT vb_item.vit_totalProductFeeINUsd,vb_item.vit_totalProductFeeINYan,vb_item.vit_serviceChargeUsd,vb_item.vit_totalLCFeeINUsd INTO @t1,@t2,@t3,@t4 FROM vb_item WHERE vb_item.vit_id = @vid;
							
						
							SET serusd = @t3 - @service;
							SET feelc =  @t4 - @lc;
							SET feeusd = @t1 - @amou;
							SET feeyan = feeusd % @chi;

								UPDATE vb_item SET vb_item.vit_totalINUsd = VB_FUN_FNUM(subt),
																 vb_item.vit_totalProductFeeINUsd = VB_FUN_FNUM(feeusd),
																 vb_item.vit_totalProductFeeINYan = VB_FUN_FNUM(feeyan),
																 vb_item.vit_serviceChargeUsd = VB_FUN_FNUM(serusd),
																 vb_item.vit_totalLCFeeINUsd = VB_FUN_FNUM(feelc)
							WHERE vb_item.vit_id = @vid;

							UPDATE vb_itemdetail SET vb_itemdetail.vit_status = "Cancel", vb_itemdetail.vit_unitPriceYan = 0 , vb_itemdetail.vit_unitPriceDollar = 0, vb_itemdetail.vit_amountDollar = 0, vb_itemdetail.vit_lcFeeDollar = 0, vb_itemdetail.vit_lcFeeYan = 0,vb_itemdetail.vit_totalProductFeeYan = 0 WHERE vb_itemdetail.vit_itemId = item;
							INSERT INTO vb_notification_detail (CNID,CNCustID,CNDescription,CNDate,CNSeen) VALUES ("NT-00002",@custid,CONCAT("Your order OF: ",@ofcode," with link: ",@link," was refund to you. amount :",VB_FUN_FNUM(@total),""),NOW(),0);
							
							 
							-- Refund AME_FUN_ACC
							SELECT ame_ar_py.var_accid INTO @reReAccId FROM ame_ar_py WHERE ame_ar_py.var_id = @rece;
							SELECT ame_ar_py_det.var_docid INTO @reAccId FROM ame_ar_py_det WHERE ame_ar_py_det.var_id = @rece;

							SET prefixRefund = AME_FUN_PREFIX("AR-RF",1);
							INSERT INTO ame_refund (var_id,int_line,var_doctype,var_docid,var_perid,var_accid,var_preaccid,var_claid,var_status,var_ref,
							var_desc,var_pmttype,var_pmtref,var_create,dat_date,dat_create,dou_amt,var_cur,dou_rate,dou_funamt,dou_funar)
							VALUES (prefixRefund,1,"AR-PY",@rece,@custid,@reAccId,@reReAccId,'','Open',@ofcode,
							"","","","ADMIN",NOW(),NOW(),@total,"USD",1,@total,@total);
						
							SELECT COALESCE(vb_defaultcustomer.VenLC,""),COALESCE(vb_defaultcustomer.VenService,"") INTO @vdl,@vds FROM vb_defaultcustomer LIMIT 1;
							
							SET @venItemID = VB_FUN_GET_ITEM_ONLY_ID(@ofcode);

							UPDATE ame_mt_ite_pri SET ame_mt_ite_pri.dou_price = feelc WHERE ame_mt_ite_pri.var_id = (SELECT vb_itemdetail.vit_itemId FROM vb_itemdetail WHERE vb_itemdetail.vit_vendor = @vdl AND vb_itemdetail.vit_id = @venItemID);
							UPDATE ame_mt_ite_pri SET ame_mt_ite_pri.dou_price = serusd WHERE ame_mt_ite_pri.var_id = (SELECT  vb_itemdetail.vit_itemId FROM vb_itemdetail WHERE vb_itemdetail.vit_vendor = @vds AND vb_itemdetail.vit_id = @venItemID);

							CALL VB_PRO_MEG(1,'approved refund ');

						

					ELSE
							CALL VB_PRO_MEG(9,CONCAT("receipt ID: ",@rece,""));
					END IF;

		ELSE
				UPDATE vb_refunditem SET vir_status = "Rejected" WHERE vir_itemId = item AND vir_id = tranid;
				UPDATE vb_itemdetail SET vit_refund = 3 WHERE vit_itemId = item;
				CALL VB_PRO_MEG(1,'rejected refund ');
		END IF;
		
	
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_UPDATE_SALE_ORDER_STATUS`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_UPDATE_SALE_ORDER_STATUS`(IN `saleId` varchar(255),IN `keyLine` int,IN `reQty` double)
    DETERMINISTIC
BEGIN
	
	SELECT dou_orderqty INTO @tmpQty FROM ame_ar_so_det WHERE int_poline = keyLine AND var_id = saleId;

	SET reQty = reQty + @tmpQty;
	
	UPDATE ame_ar_so_det SET dou_orderqty = reQty WHERE int_poline = keyLine AND var_id = saleId;

	SELECT SUM(dou_orderqty), SUM(dou_qty) INTO @reQty, @qty FROM ame_ar_so_det WHERE var_id = saleId; #AND var_itemid IN (SELECT ite.var_id FROM ame_mt_ite ite WHERE ite.tin_no_sal = 0);
SELECT @reQty, @qty ;
	IF @reQty = @qty THEN
		UPDATE ame_ar_so SET var_status = "Closed" WHERE var_id = saleId;
		UPDATE vb_item SET vit_status = 11 WHERE vit_code = (SELECT var_ref FROM ame_ar_so WHERE var_id = saleId);
	END IF;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_PRO_UPDATE_TABLE`;
DELIMITER |
CREATE PROCEDURE `VB_PRO_UPDATE_TABLE`(IN tablename varchar(255), IN val text, IN wheCol varchar(200), IN wheVal varchar(200))
    DETERMINISTIC
BEGIN
  SET @SQL1 = CONCAT("UPDATE ",tablename," ",val," WHERE ",wheCol," = ",wheVal," ;");
	#SET @SQL1 = REPLACE(@SQL1, 'tempid', NumberPre);
	PREPARE St FROM @SQL1;
	EXECUTE St;
	DEALLOCATE PREPARE St;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `VB_TEST`;
DELIMITER |
CREATE PROCEDURE `VB_TEST`(cusid varchar(200))
BEGIN
	  SET @itemcode = VB_FUN_GENERATE_CODE(cusid);
		SELECT @itemcode;
END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `viewOpportunityById`;
DELIMITER |
CREATE PROCEDURE `viewOpportunityById`(IN `opConId` int)
    DETERMINISTIC
BEGIN
	
	SELECT 
			con.CO_ID conID,
			CONCAT(CO_Salutation,' ',CO_FirstName,' ',CO_LastName) conName,
			con.CO_Phone conPhone,
			con.CO_Mobile conMobile,
			con.CO_Email conEmail,
			con.CO_Title conTitle,
			con.CO_Department conDepartment,
			opcon.OPCON_Role role,
			opcon.OPCON_Type Type,
			opcon.OPCON_ID id
		FROM 
			crm_contact con 
			INNER JOIN 
				crm_opportunity_contact opcon
					ON con.CO_ID = opcon.OPCON_CONID
		WHERE 
			opcon.OPCON_ID = opConId;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `viewOpportunityQuotationById`;
DELIMITER |
CREATE PROCEDURE `viewOpportunityQuotationById`(IN `opQuoteId` int)
    DETERMINISTIC
BEGIN
	SELECT
		quote.SalID quoteId,
		quote.TotalAmt totalAmmount,
		quote.SalDate quoteDate,
		quote.StartDate startDate,
		quote.ExpireDate expireDate,
		emp.EmpID empId,
		emp.EmpName empName,
		L_ID opQuoteId
		FROM
			tblsbquote quote
		INNER JOIN
			crm_opportunity_quote opquote
				ON quote.SalID = opquote.QuotID
		LEFT OUTER JOIN 
			tblemployee emp
				ON quote.EmpID = emp.EmpID
		WHERE opquote.L_ID = opQuoteId;

END |
DELIMITER ;

DROP PROCEDURE IF EXISTS `viewOpportunitySaleOrderById`;
DELIMITER |
CREATE PROCEDURE `viewOpportunitySaleOrderById`(IN `opSaleOrderId` int)
    DETERMINISTIC
BEGIN
	SELECT 
		 sale.SalID saleOrderId,
		 sale.TotalAmt totalAmount,
		 sale.SalDate saleOrderDate,
		 sale.DueDate saleDueDate,
		 emp.EmpID empId,
		 emp.EmpName empName,
		 L_ID opSaleOrderId
	FROM		
		tblsaleorder sale
	INNER JOIN
		crm_opportunity_saleorder opsale
			ON sale.SalID = opsale.S_O_ID
	LEFT OUTER JOIN 
		tblemployee emp 
			ON sale.EmpID = emp.EmpID
	WHERE opsale.L_ID = opSaleOrderId;

END |
DELIMITER ;

-- 
-- Dumping triggers
-- 

DROP TRIGGER /*!50030 IF EXISTS */ `AddCreateDate`;
DELIMITER |
CREATE TRIGGER `AddCreateDate` BEFORE INSERT ON `hrm_timesheet_fingerprint` FOR EACH ROW BEGIN
DECLARE tz VARCHAR(20) DEFAULT('');
SELECT Setting_Value INTO tz FROM hrm_setting WHERE Setting_Type = 'TimeZone' AND Setting_Name = 'TimeZone';
SET New.CreateDate = convert_tz(now(), @@session.time_zone, tz);
END |
DELIMITER ;

DROP TRIGGER /*!50030 IF EXISTS */ `AddAttendance`;
DELIMITER |
CREATE TRIGGER `AddAttendance` AFTER INSERT ON `hrm_timesheet_fingerprint` FOR EACH ROW BEGIN
     DECLARE ShiftType VARCHAR(255);
     DECLARE ShiftID VARCHAR(255);
     DECLARE SEmpID VARCHAR(255);
	
     SELECT var_hrm_shift_type,var_hrm_shift_id,var_id INTO ShiftType,ShiftID,SEmpID FROM ame_mt_emp WHERE var_hrm_fpid= NEW.FP_ID;
     IF LOWER(ShiftType) = "normal" THEN
          CALL spHRMAddAttendanceNormal(SEmpID,ShiftID, NEW.FP_ID, New.FP_Date, NEW.FP_Punch1, NEW.FP_Punch2, NEW.FP_Punch3, NEW.FP_Punch4, NEW.FP_Punch5, NEW.FP_Punch6, NEW.FP_Punch7, NEW.FP_Punch8, NEW.FP_Punch9, NEW.FP_Punch10);
    ELSEIF LOWER(ShiftType) = "roster" THEN
          SELECT Roster_ShiftID INTO ShiftID FROM hrm_timesheet_roster WHERE Roster_Date = New.FP_Date AND Roster_EmpID = SEmpID ;
          CALL spHRMAddAttendanceRoster(SEmpID,ShiftID, NEW.FP_ID, New.FP_Date, NEW.FP_Punch1, NEW.FP_Punch2, NEW.FP_Punch3, NEW.FP_Punch4, NEW.FP_Punch5, NEW.FP_Punch6, NEW.FP_Punch7, NEW.FP_Punch8, NEW.FP_Punch9, NEW.FP_Punch10);
     END IF;
	
END |
DELIMITER ;

DROP TRIGGER /*!50030 IF EXISTS */ `UpdateAttendance`;
DELIMITER |
CREATE TRIGGER `UpdateAttendance` AFTER UPDATE ON `hrm_timesheet_fingerprint` FOR EACH ROW BEGIN
     DECLARE ShiftType VARCHAR(255);
     DECLARE ShiftID VARCHAR(255);
     DECLARE SEmpID VARCHAR(255);
	
     SELECT var_hrm_shift_type,var_hrm_shift_id,var_id INTO ShiftType,ShiftID,SEmpID FROM ame_mt_emp WHERE var_hrm_fpid= NEW.FP_ID;
     IF LOWER(ShiftType) = "normal" THEN
          CALL spHRMAddAttendanceNormal(SEmpID,ShiftID, NEW.FP_ID, New.FP_Date, NEW.FP_Punch1, NEW.FP_Punch2, NEW.FP_Punch3, NEW.FP_Punch4, NEW.FP_Punch5, NEW.FP_Punch6, NEW.FP_Punch7, NEW.FP_Punch8, NEW.FP_Punch9, NEW.FP_Punch10);
    ELSEIF LOWER(ShiftType) = "roster" THEN
          SELECT Roster_ShiftID INTO ShiftID FROM hrm_timesheet_roster WHERE Roster_Date = New.FP_Date AND Roster_EmpID = SEmpID ;
          CALL spHRMAddAttendanceRoster(SEmpID,ShiftID, NEW.FP_ID, New.FP_Date, NEW.FP_Punch1, NEW.FP_Punch2, NEW.FP_Punch3, NEW.FP_Punch4, NEW.FP_Punch5, NEW.FP_Punch6, NEW.FP_Punch7, NEW.FP_Punch8, NEW.FP_Punch9, NEW.FP_Punch10);
     END IF;
	
END |
DELIMITER ;

DROP TRIGGER /*!50030 IF EXISTS */ `Add`;
DELIMITER |
CREATE TRIGGER `Add` AFTER INSERT ON `hrm_timesheet_roster` FOR EACH ROW BEGIN
     DECLARE SFPID VARCHAR(255);

     SELECT var_hrm_fpid INTO SFPID FROM ame_mt_emp WHERE var_id=NEW.Roster_EmpID;

    CALL spHRMAddAttendanceRoster(NEW.Roster_EmpID, NEW.Roster_ShiftID, SFPID, NEW.Roster_Date, '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00');
	
END |
DELIMITER ;

DROP TRIGGER /*!50030 IF EXISTS */ `Update`;
DELIMITER |
CREATE TRIGGER `Update` AFTER UPDATE ON `hrm_timesheet_roster` FOR EACH ROW BEGIN
     DECLARE SFPID VARCHAR(255);

     SELECT Emp_FPID INTO SFPID FROM tblemployee WHERE EmpID=NEW.Roster_EmpID;

    DELETE FROM hrm_timesheet_attendance WHERE Att_EmpID=NEW.Roster_EmpID AND Att_FPID=SFPID AND Att_Date=NEW.Roster_Date;

    CALL spHRMAddAttendanceRoster(NEW.Roster_EmpID, NEW.Roster_ShiftID, SFPID, NEW.Roster_Date, '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00');
	
END |
DELIMITER ;

DROP TRIGGER /*!50030 IF EXISTS */ `Delete`;
DELIMITER |
CREATE TRIGGER `Delete` BEFORE DELETE ON `hrm_timesheet_roster` FOR EACH ROW BEGIN
     DECLARE SFPID VARCHAR(255);

     SELECT Emp_FPID INTO SFPID FROM tblemployee WHERE EmpID=OLD.Roster_EmpID;

    DELETE FROM hrm_timesheet_attendance WHERE Att_EmpID=OLD.Roster_EmpID AND Att_FPID=SFPID AND Att_Date=OLD.Roster_Date;
	
END |
DELIMITER ;




-- Total time: 0:0:0:9:0 (d:h:m:s:ms)
