REPORTFORM;BR4_2.frf
TITLE;รายรับประจำวัน แยกตามชนิดชำระ
SQL;drop table if exists _head&
SQL;create table _head
SQL;select 'S1' as head, space(30) as subhead from config& 
SQL;update _head set subhead = 'เงินสด'&
SQL;insert into  _head
SQL;select 'S2' as head, 'เช็ค' as subhead from config& 
SQL;insert into  _head
SQL;select 'S3' as head, 'ส่วนลด' as subhead from config& 
SQL;insert into  _head
SQL;select 'S4' as head, 'ผังบัญชี/อื่นๆ' as subhead from config& 
SQL;insert into  _head
SQL;select 'S5' as head, 'ภาษี' as subhead from config& 
SQL;insert into  _head
SQL;select 'S6' as head, 'เงินโอน' as subhead from config& 
SQL;insert into  _head
SQL;select 'S7' as head, 'บัตรเครดิต' as subhead from config& 
SQL;
SQL;drop table if exists _payar&
SQL;create table _payar
SQL;select bktr.DOCNO, bktr.DATEDOC,bktr.ARACCID,armst.XCOMP,
SQL;sum(if(left(paytype,2)='01',bktr.AMT_IN,0)) as s01,
SQL;sum(if(left(paytype,2)='02',bktr.AMT_IN,0)) as s02,
SQL;sum(if(left(paytype,2)='03',bktr.AMT_IN,0)) as s03,
SQL;sum(if(left(paytype,2)='04',bktr.AMT_IN,0)) as s04,
SQL;sum(if(left(paytype,2)='05',bktr.AMT_IN,0)) as s05,
SQL;sum(if(left(paytype,2)='06',bktr.AMT_IN,0)) as s06,
SQL;sum(if(left(paytype,2)='07',bktr.AMT_IN,0)) as s07,
SQL;sum(bktr.AMT_IN) as xtotal,space(50) as paydesc
SQL;from bktr inner join armst on bktr.ARACCID = armst.ACCID
SQL;where bktr.TRTYPE = 'AR'
SQL;and datedoc >= :m11 and datedoc <= :m12
SQL;group by 1,2,3,4&
SQL;
SQL;alter table _payar
SQL;  add index _payar (docno)&
SQL;
SQL;update _payar inner join arpayhd on _payar.docno = arpayhd.docno
SQL;set _payar.paydesc = arpayhd.paytypedesc&
SQL;
SQL;
SQL;select * from _payar
CONDITION;ตั้งแต่วันที่~BKTR.DATEDOC~Date~2~~
EXCELSQL;drop table if exists _head&
EXCELSQL;create table _head
EXCELSQL;select '01' as head, space(30) as subhead from config& 
EXCELSQL;update _head set subhead = 'เงินสด'&
EXCELSQL;insert into  _head
EXCELSQL;select '02' as head, 'เช็ค' as subhead from config& 
EXCELSQL;insert into  _head
EXCELSQL;select '03' as head, 'ส่วนลด' as subhead from config& 
EXCELSQL;insert into  _head
EXCELSQL;select '04' as head, 'ผังบัญชี/อื่นๆ' as subhead from config& 
EXCELSQL;insert into  _head
EXCELSQL;select '05' as head, 'ภาษี' as subhead from config& 
EXCELSQL;insert into  _head
EXCELSQL;select '06' as head, 'เงินโอน' as subhead from config& 
EXCELSQL;insert into  _head
EXCELSQL;select '07' as head, 'บัตรเครดิต' as subhead from config& 
EXCELSQL;
EXCELSQL;drop table if exists _payar&
EXCELSQL;create table _payar
EXCELSQL;select bktr.DOCNO, bktr.DATEDOC,bktr.ARACCID,armst.XCOMP,
EXCELSQL;sum(if(left(paytype,2)='01',bktr.AMT_IN,0)) as s01,
EXCELSQL;sum(if(left(paytype,2)='02',bktr.AMT_IN,0)) as s02,
EXCELSQL;sum(if(left(paytype,2)='03',bktr.AMT_IN,0)) as s03,
EXCELSQL;sum(if(left(paytype,2)='04',bktr.AMT_IN,0)) as s04,
EXCELSQL;sum(if(left(paytype,2)='05',bktr.AMT_IN,0)) as s05,
EXCELSQL;sum(if(left(paytype,2)='06',bktr.AMT_IN,0)) as s06,
EXCELSQL;sum(if(left(paytype,2)='07',bktr.AMT_IN,0)) as s07,
EXCELSQL;sum(bktr.AMT_IN) as xtotal,space(50) as paydesc
EXCELSQL;from bktr inner join armst on bktr.ARACCID = armst.ACCID
EXCELSQL;where bktr.TRTYPE = 'AR'
EXCELSQL;and datedoc >= :m11 and datedoc <= :m12
EXCELSQL;group by 1,2,3,4&
EXCELSQL;
EXCELSQL;alter table _payar
EXCELSQL;  add index _payar (docno)&
EXCELSQL;
EXCELSQL;update _payar inner join arpayhd on _payar.docno = arpayhd.docno
EXCELSQL;set _payar.paydesc = arpayhd.paytypedesc&
EXCELSQL;
EXCELSQL;
EXCELSQL;select * from _payar
USECROSSTAB;Y
CROSSTABFRF;Y
CROSSTABHEAD;_head
CROSSTABFIELD;subhead
