useful-qry/function-in-db/procedures/clo_pos_closing.sql

-- truncate table dbr_pos_closing;

drop procedure if exists clo_pos_closing;

DELIMITER //
create procedure clo_pos_closing( _curr_date varchar(8), _branch int, _user int)

BEGIN

Declare _sales_day decimal(20,4) default "0.0000";
Declare _ccard_day decimal(20,4) default "0.0000";
Declare _cash_day decimal(20,4) default "0.0000";
Declare _coupon_day decimal(20,4) default "0.0000";
Declare _reward_day decimal(20,4) default "0.0000";
Declare _discount_day decimal(20,4) default "0.0000";
Declare _sorder_cnt int;
Declare _sorder_new int;
Declare _revisit_cnt int;

Declare _sales_month decimal(20,4) default "0.0000";
Declare _ccard_month decimal(20,4) default "0.0000";
Declare _cash_month decimal(20,4) default "0.0000";
Declare _coupon_month decimal(20,4) default "0.0000";
Declare _reward_month decimal(20,4) default "0.0000";
Declare _discount_month decimal(20,4) default "0.0000";

Declare _not_found boolean;
Declare _sales_day_cur cursor for 
	select 
	  sum(sorder_sum), sum(ccard_amt), sum(cash_amt), sum(coupon_amt), sum(reward_amt),
	  sum(discount_amt)
	from
		dbr_sorder as mx
		inner join 
			( select
		    dbr_sorder.id, dbr_company.company_name, birth_date, count(sorder_no) as visit_cnt
	    from
		    dbr_company
		    inner join dbr_sorder on dbr_company.id = dbr_sorder.buyer_id
	    group by company_name) as com
		on com.id = mx.id
	  
	  inner join dbr_sorder_bd on mx.id = dbr_sorder_bd.sorder_id
	where sorder_date between _curr_date and _curr_date
				and mx.branch_id = _branch;

Declare _sales_month_cur cursor for
	select 
	  sum(sorder_sum), sum(ccard_amt), sum(cash_amt), sum(coupon_amt), sum(reward_amt),
	  sum(discount_amt)
	from
		dbr_sorder as mx
		inner join 
			( select
		    dbr_sorder.id, dbr_company.company_name, birth_date, count(sorder_no) as visit_cnt
	    from
		    dbr_company
		    inner join dbr_sorder on dbr_company.id = dbr_sorder.buyer_id
	    group by company_name) as com
		on com.id = mx.id
	  
	  inner join dbr_sorder_bd on mx.id = dbr_sorder_bd.sorder_id
	where sorder_date between _first_date(_curr_date) and _curr_date
				and mx.branch_id = _branch;
				
Declare _visit_day_cur cursor for
	select
	  sum(if(is_first_order = '1', 1, 0)), sum(if(is_first_order <> '1', 1, 0))
	from dbr_sorder as mx
	where sorder_date between _curr_date and _curr_date
				and mx.branch_id = _branch;

Declare continue handler for 
        not found set _not_found = true;

open _sales_day_cur; 
	loop_sales_day: loop
		fetch _sales_day_cur into _sales_day, _ccard_day, _cash_day, _coupon_day, _reward_day,
			_discount_day;
		if _not_found then leave loop_sales_day; end if;
	end loop loop_sales_day;
close _sales_day_cur;

open _sales_month_cur; 
	loop_month_day: loop
		fetch _sales_month_cur into _sales_month, _ccard_month, _cash_month, _coupon_month, _reward_month,
			_discount_month;
		if _not_found then leave loop_month_day; end if;
	end loop loop_month_day;
close _sales_month_cur;

open _visit_day_cur; 
	loop_visit_day: loop
		fetch _visit_day_cur into _sorder_new, _revisit_cnt;
		if _not_found then leave loop_visit_day; end if;
	end loop loop_visit_day;
close _visit_day_cur;

delete from dbr_pos_closing
	where branch_id = branch_id and closing_date = _curr_date and  user_id = _user;       		
insert into dbr_pos_closing
	( branch_id, closing_date, user_id, sales_day, ccard_day, 
	cash_day, reward_day, discount_day, sorder_new, revisit_cnt,
	sales_month, ccard_month, cash_month, reward_month, discount_month
	)
	values ( _branch, _curr_date, _user, _sales_day, _ccard_day, 
	_cash_day, _reward_day, _discount_day, _sorder_new, _revisit_cnt,
	_sales_month, _ccard_month, _cash_month, _reward_month, _discount_month
	 );

END; //
DELIMITER ;