REPORTFORM;GLCARD_ARAP.frf
TITLE;รายงานบัญชีแยกประเภท แสดงลูกค้า/ผู้จำหน่าย
SQL;drop table if exists _glcard2&
SQL;
SQL;create table _glcard2
SQL;select gltr.GLID, glmst.GLDESC,gltr.DATEDOC, gltr.DOCNO, gltrhd.XREMLINE, glmst.GLTYPE, gltr.DEBIT, gltr.CREDIT,
SQL;0000000000.00 as nbal , 1 as line, if(instr('AEC',left(glmst.GLTYPE,1)) > 0 , "D","C") as nature, SPACE(25) AS JOBNO, space(50) as xcomp, space(20) as accid,"N" as nomove
SQL;from gltr inner join gltrhd on gltr.DOCNO = gltrhd.DOCNO
SQL;inner join glmst on gltr.GLID = glmst.GLID
SQL;where gltr.glid >= :m21 and gltr.glid <= :m22
SQL;and gltr.datedoc >= :m11 and gltr.datedoc <= :m12&
SQL;
SQL;alter table _glcard2 
SQL;   add index docno (docno),
SQL;   add index glid (glid),
SQL;   add index accid (accid)&
SQL;
SQL;drop table if exists _glcard&
SQL;
SQL;create table _glcard
SQL;select gltr.GLID, glmst.GLDESC,curdate() as datedoc, 'ยอดยกมา' as docno, space(10) as xremline, glmst.GLTYPE, 0 as debit, 0 as credit,
SQL;sum(if(instr('AEC',left(glmst.GLTYPE,1)) > 0,gltr.DEBIT - gltr.CREDIT,gltr.CREDIT - gltr.DEBIT)) as nbal , 0 as line, if(instr('AEC',left(glmst.GLTYPE,1)) > 0 , "D","C") as nature,
SQL;SPACE(25) AS JOBNO, space(50) as xcomp, space(20) as accid,"N" as nomove
SQL;from gltr inner join gltrhd on gltr.DOCNO = gltrhd.DOCNO
SQL;inner join glmst on gltr.GLID = glmst.GLID
SQL;where gltr.glid >= :m21 and gltr.glid <= :m22
SQL;and gltr.datedoc < :m11 
SQL;group by gltr.GLID, glmst.GLDESC&
SQL;
SQL;update _glcard set datedoc = '0000-00-00'&
SQL;
SQL;insert into _glcard2
SQL;select glid,gldesc,datedoc,docno,xremline,gltype,sum(debit) as debit,sum(credit) as credit,sum(nbal) as nbal, sum(line) as line,nature ,jobno,xcomp,accid,nomove
SQL;from _glcard
SQL;group by glid,gldesc,datedoc,docno,xremline,gltype,nature&
SQL;
SQL;
SQL;update _glcard2 inner join arcard on _glcard2.docno = arcard.docno
SQL;inner join armst on arcard.accid = armst.accid
SQL;set _glcard2.accid = armst.accid, _glcard2.xcomp = armst.xcomp&
SQL;
SQL;update _glcard2 inner join apcard on _glcard2.docno = apcard.docno
SQL;inner join apmst on apcard.accid = apmst.accid
SQL;set _glcard2.accid = apmst.accid, _glcard2.xcomp = apmst.xcomp&
SQL;
SQL;drop table if exists _nomove&
SQL;create table _nomove
SQL;select glid from _glcard2 group by glid having count(*) = 1&
SQL;
SQL;alter table _nomove
SQL;   add index _nomove (glid)&
SQL;
SQL;update _glcard2 inner join _nomove on _glcard2.glid = _nomove.glid
SQL;set _glcard2.nomove = "Y"&
SQL;
SQL;select * from _glcard2 order by 
SQL;glid,line, datedoc
CONDITION;ตั้งแต่วันที่~DATEDOC~Date~2~~
CONDITION;ตั้งแต่รหัสผังบัญชี~GLID~Chr~2~SELECT GLID FROM GLMST~GL
CONDITION;เงื่อนไข~~TextFile~~GLMOVE.TXT~
EXCELSQL;drop table if exists _glcard2&
EXCELSQL;
EXCELSQL;create table _glcard2
EXCELSQL;select gltr.GLID, glmst.GLDESC,gltr.DATEDOC, gltr.DOCNO, gltrhd.XREMLINE, glmst.GLTYPE, gltr.DEBIT, gltr.CREDIT,
EXCELSQL;0000000000.00 as nbal , 1 as line, if(instr('AEC',left(glmst.GLTYPE,1)) > 0 , "D","C") as nature, SPACE(25) AS JOBNO, space(50) as xcomp, space(20) as accid,"N" as nomove
EXCELSQL;from gltr inner join gltrhd on gltr.DOCNO = gltrhd.DOCNO
EXCELSQL;inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;where gltr.glid >= :m21 and gltr.glid <= :m22
EXCELSQL;and gltr.datedoc >= :m11 and gltr.datedoc <= :m12&
EXCELSQL;
EXCELSQL;alter table _glcard2 
EXCELSQL;   add index docno (docno),
EXCELSQL;   add index glid (glid),
EXCELSQL;   add index accid (accid)&
EXCELSQL;
EXCELSQL;drop table if exists _glcard&
EXCELSQL;
EXCELSQL;create table _glcard
EXCELSQL;select gltr.GLID, glmst.GLDESC,curdate() as datedoc, 'ยอดยกมา' as docno, space(10) as xremline, glmst.GLTYPE, 0 as debit, 0 as credit,
EXCELSQL;sum(if(instr('AEC',left(glmst.GLTYPE,1)) > 0,gltr.DEBIT - gltr.CREDIT,gltr.CREDIT - gltr.DEBIT)) as nbal , 0 as line, if(instr('AEC',left(glmst.GLTYPE,1)) > 0 , "D","C") as nature,
EXCELSQL;SPACE(25) AS JOBNO, space(50) as xcomp, space(20) as accid,"N" as nomove
EXCELSQL;from gltr inner join gltrhd on gltr.DOCNO = gltrhd.DOCNO
EXCELSQL;inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;where gltr.glid >= :m21 and gltr.glid <= :m22
EXCELSQL;and gltr.datedoc < :m11 
EXCELSQL;group by gltr.GLID, glmst.GLDESC&
EXCELSQL;
EXCELSQL;update _glcard set datedoc = '0000-00-00'&
EXCELSQL;
EXCELSQL;insert into _glcard2
EXCELSQL;select glid,gldesc,datedoc,docno,xremline,gltype,sum(debit) as debit,sum(credit) as credit,sum(nbal) as nbal, sum(line) as line,nature ,jobno,xcomp,accid,nomove
EXCELSQL;from _glcard
EXCELSQL;group by glid,gldesc,datedoc,docno,xremline,gltype,nature&
EXCELSQL;
EXCELSQL;
EXCELSQL;update _glcard2 inner join arcard on _glcard2.docno = arcard.docno
EXCELSQL;inner join armst on arcard.accid = armst.accid
EXCELSQL;set _glcard2.accid = armst.accid, _glcard2.xcomp = armst.xcomp&
EXCELSQL;
EXCELSQL;update _glcard2 inner join apcard on _glcard2.docno = apcard.docno
EXCELSQL;inner join apmst on apcard.accid = apmst.accid
EXCELSQL;set _glcard2.accid = apmst.accid, _glcard2.xcomp = apmst.xcomp&
EXCELSQL;
EXCELSQL;drop table if exists _nomove&
EXCELSQL;create table _nomove
EXCELSQL;select glid from _glcard2 group by glid having count(*) = 1&
EXCELSQL;
EXCELSQL;alter table _nomove
EXCELSQL;   add index _nomove (glid)&
EXCELSQL;
EXCELSQL;update _glcard2 inner join _nomove on _glcard2.glid = _nomove.glid
EXCELSQL;set _glcard2.nomove = "Y"&
EXCELSQL;
EXCELSQL;select * from _glcard2 order by 
EXCELSQL;glid,line, datedoc
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
