proc_label:BEGIN
declare dailycapping int default 16;
declare evratio int default 10;
declare muserid varchar(45);
declare mleftuser varchar(45);
declare mrightuser varchar(45);
declare mleftuserpin varchar(45);
declare mrightuserpin varchar(45);
declare mleftpinprice double;
declare mrightpinprice double;
declare mmatchingdate datetime;
declare totalamount double;
declare aday_bal double;
declare acurrent_bal double;
declare atotal_bal double;
declare acurrent_matching_count int;
declare atotal_matching_count int;
declare acurrent_paid_matching int;
declare atotal_paid_matching int;
declare aaday_bal double;
declare aacurrent_bal double;
declare aatotal_bal double;
declare aacurrent_matching_count int;
declare aatotal_matching_count int;
declare aacurrent_paid_matching int;
declare aatotal_paid_matching int;
declare n int default 0;
declare i int default 0;
declare matchingid int default 0;
declare edes text;

#START TRANSACTION READ WRITE;

/*all not updated date wise user*/
SET SQL_SAFE_UPDATES = 0;
SELECT count(*) FROM tbl_matching_details where matchingdate !='1970-01-01 00:00:00' and isupdated='0' into n;
 #insert into `infinity2_iml4`.`tbl_proc_logs` (`userid`,`procname`,`status`) values ('400','amount','start');



while i < n do
/*amount update here*/
	SELECT id,userid,leftuser,rightuser,leftuserpin,rightuserpin,leftpinprice,rightpinprice,matchingdate
		into matchingid,muserid,mleftuser,mrightuser,mleftuserpin,mrightuserpin,mleftpinprice,mrightpinprice,mmatchingdate
        FROM tbl_matching_details where matchingdate !='1970-01-01 00:00:00' and isupdated='0'
        order by id asc limit 1;
	# insert into logs
 insert into tbl_proc_logs (`userid`,`procname`,`status`) values (matchingid,'amount','start');
	update tbl_matching_details set isupdated='1' where id=matchingid;
	set totalamount=mleftpinprice+mrightpinprice;


	select day_bal,current_bal,total_bal,current_matching_count,total_matching_count,current_paid_matching,total_paid_matching
		into aday_bal,acurrent_bal,atotal_bal,acurrent_matching_count,atotal_matching_count,acurrent_paid_matching,atotal_paid_matching
        from tbl_ew where userid=muserid;
	if acurrent_matching_count >= dailycapping then
		/*matching count increase */
        update tbl_ew set current_matching_count=acurrent_matching_count+1,total_matching_count=atotal_matching_count+1 where userid=muserid;
        /* insert into flush table*/
		insert tbl_matching_flush (`matchingid`,`userid`,`leftuser`,`rightuser`,`leftuserpin`,`rightuserpin`,`leftpinprice`,`rightpinprice`,`matchingdate`)
			value (matchingid,muserid,mleftuser,mrightuser,mleftuserpin,mrightuserpin,mleftpinprice,mrightpinprice,mmatchingdate);
    else
			/*check matching condition 10:1*/

        if mod((atotal_paid_matching+1),evratio) = 0 then
			select day_bal,current_bal,total_bal,current_matching_count,total_matching_count,current_paid_matching,total_paid_matching
				into aaday_bal,aacurrent_bal,aatotal_bal,aacurrent_matching_count,aatotal_matching_count,aacurrent_paid_matching,aatotal_paid_matching
				from tbl_ev where userid=muserid;

			update tbl_ev set
                day_bal=aaday_bal+totalamount,current_bal=aacurrent_bal+totalamount,total_bal=aatotal_bal+totalamount,
                current_matching_count=aacurrent_matching_count+1,total_matching_count=aatotal_matching_count+1,
                current_paid_matching=aacurrent_paid_matching+1,total_paid_matching=aatotal_paid_matching+1
                where userid=muserid;

			update tbl_ew set current_matching_count=acurrent_matching_count+1,total_matching_count=atotal_matching_count+1,
            current_paid_matching=acurrent_paid_matching+1,total_paid_matching=atotal_paid_matching+1
                where userid=muserid;
			/*insert detals data into wallet history for EV*/
			set edes =CONCAT('Credited by: ','Left pin ',mleftuserpin,' with price ',mleftpinprice,' and ','right pin ',mrightuserpin,' with price ',mrightpinprice);
            insert tbl_wallet_history (`userid`,`prev_bal`,`amount`,`new_bal`,`des`,`account_type`)
			value (muserid,aacurrent_bal,totalamount,(aacurrent_bal+totalamount),edes,'ev');
             /*insert detals data into Ev history*/
			insert tbl_ev_history (`matchingid`,`userid`,`leftuser`,`rightuser`,`leftuserpin`,`rightuserpin`,`leftpinprice`,`rightpinprice`,`matchingdate`)
				value (matchingid,muserid,mleftuser,mrightuser,mleftuserpin,mrightuserpin,mleftpinprice,mrightpinprice,mmatchingdate);
        else
			/*update amount and count for ecah user*/
			select day_bal,current_bal,total_bal,current_matching_count,total_matching_count,current_paid_matching,total_paid_matching
				into aday_bal,acurrent_bal,atotal_bal,acurrent_matching_count,atotal_matching_count,acurrent_paid_matching,atotal_paid_matching
				from tbl_ew where userid=muserid;

			update tbl_ew set
                day_bal=aday_bal+totalamount,current_bal=acurrent_bal+totalamount,total_bal=atotal_bal+totalamount,
                current_matching_count=acurrent_matching_count+1,total_matching_count=atotal_matching_count+1,
                current_paid_matching=acurrent_paid_matching+1,total_paid_matching=atotal_paid_matching+1
                where userid=muserid;
			/*insert data into wallet history for EW*/
            set edes =CONCAT('Credited by: ','Left pin ',mleftuserpin,' with price ',mleftpinprice,' and ','right pin ',mrightuserpin,' with price ',mrightpinprice);
            insert tbl_wallet_history (`userid`,`prev_bal`,`amount`,`new_bal`,`des`,`account_type`)
			value (muserid,acurrent_bal,totalamount,(acurrent_bal+totalamount),edes,'ew');

            /*insert detals data into EW history*/
			insert tbl_ew_history (`matchingid`,`userid`,`leftuser`,`rightuser`,`leftuserpin`,`rightuserpin`,`leftpinprice`,`rightpinprice`,`matchingdate`)
				value (matchingid,muserid,mleftuser,mrightuser,mleftuserpin,mrightuserpin,mleftpinprice,mrightpinprice,mmatchingdate);
        end if;
    end if;

    # insert into logs
    insert into tbl_proc_logs (userid,procname,`status`) values (muserid,'amount','end');

	set i =i+1;
end while;
/*update current matching count and current paid matching from tbl_ew,tbl_ev and day_bal*/
update tbl_ew set day_bal=0,current_matching_count=0,current_paid_matching=0 where current_matching_count != 0;
update tbl_ev set day_bal=0,current_matching_count=0,current_paid_matching=0 where current_matching_count != 0;
# call promo engine here
call promoengine(/*@mgs*/ );
/*SET mgs='updated all data';
select @mgs;*/
COMMIT;
END