REPORTFORM;GLCARD_J.frf
TITLE;รายงานบัญชีแยกประเภท แยก Job
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, gltr.jobno, jobmst.jdesc,"N" as nomove
SQL;from gltr inner join gltrhd on gltr.DOCNO = gltrhd.DOCNO
SQL;inner join glmst on gltr.GLID = glmst.GLID
SQL;inner join jobmst on gltr.jobno = jobmst.jobno
SQL;where gltr.glid >= :m21 and gltr.glid <= :m22
SQL;and gltr.datedoc >= :m11 and gltr.datedoc <= :m12
SQL;and gltr.jobno >= :m31 and gltr.jobno <= :m32
SQL;&
SQL;
SQL;alter table _glcard2
SQL;   add index _glcard2 (glid,jobno)&
SQL;
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;gltr.jobno, jobmst.jdesc,"N" as nomove
SQL;from gltr inner join gltrhd on gltr.DOCNO = gltrhd.DOCNO
SQL;inner join glmst on gltr.GLID = glmst.GLID
SQL;inner join jobmst on gltr.jobno = jobmst.jobno
SQL;where gltr.glid >= :m21 and gltr.glid <= :m22
SQL;and gltr.datedoc < :m11 
SQL;and gltr.jobno >= :m31 and gltr.jobno <= :m32
SQL;group by gltr.GLID, glmst.GLDESC,gltr.jobno, jobmst.jdesc&
SQL;
SQL;alter table _glcard
SQL;   add index _glcard (glid,jobno)&
SQL;
SQL;
SQL;insert into _glcard
SQL;select jobtail.GLID, glmst.GLDESC,curdate() as datedoc, 'ยอดยกมา', '', glmst.GLTYPE, 0 as debit, 0 as credit,
SQL;if(instr('AEC',left(glmst.GLTYPE,1)) > 0,jobtail.begdb - jobtail.begcr, jobtail.begcr - jobtail.begdb) as nbal , 0 as line, if(instr('AEC',left(glmst.GLTYPE,1)) > 0 , "D","C") as nature,
SQL;jobtail.jobno, jobmst.jdesc,"N" as nomove
SQL;from jobtail inner join glmst on jobtail.GLID = glmst.GLID
SQL;inner join jobmst on jobtail.jobno = jobmst.jobno
SQL;where jobtail.glid >= :m21 and jobtail.glid <= :m22
SQL;and jobtail.jobno >= :m31 and jobtail.jobno <= :m32&
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,jdesc,nomove
SQL;from _glcard
SQL;group by glid,gldesc,datedoc,docno,xremline,gltype,nature ,jobno,jdesc&
SQL;
SQL;drop table if exists _nomove&
SQL;create table _nomove
SQL;select glid,jobno from _glcard2 group by glid,jobno having count(*) = 1&
SQL;
SQL;alter table _nomove
SQL;   add index _nomove (glid,jobno)&
SQL;
SQL;update _glcard2 inner join _nomove on _glcard2.glid = _nomove.glid
SQL;and _glcard2.jobno = _nomove.jobno
SQL;set _glcard2.nomove = "Y"&
SQL;
SQL;select * from _glcard2 order by 
SQL;jobno,glid,line, datedoc
CONDITION;ตั้งแต่วันที่~DATEDOC~Date~2~~
CONDITION;ตั้งแต่รหัสผังบัญชี~GLID~Chr~2~SELECT GLID FROM GLMST~GL
CONDITION;ตั้งแต่รหัส Job~JOBNO~Chr~2~SELECT JOBNO FROM JOBMST~JOB
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, gltr.jobno, jobmst.jdesc,"N" as nomove
EXCELSQL;from gltr inner join gltrhd on gltr.DOCNO = gltrhd.DOCNO
EXCELSQL;inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;inner join jobmst on gltr.jobno = jobmst.jobno
EXCELSQL;where gltr.glid >= :m21 and gltr.glid <= :m22
EXCELSQL;and gltr.datedoc >= :m11 and gltr.datedoc <= :m12
EXCELSQL;and gltr.jobno >= :m31 and gltr.jobno <= :m32
EXCELSQL;&
EXCELSQL;
EXCELSQL;alter table _glcard2
EXCELSQL;   add index _glcard2 (glid,jobno)&
EXCELSQL;
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;gltr.jobno, jobmst.jdesc,"N" as nomove
EXCELSQL;from gltr inner join gltrhd on gltr.DOCNO = gltrhd.DOCNO
EXCELSQL;inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;inner join jobmst on gltr.jobno = jobmst.jobno
EXCELSQL;where gltr.glid >= :m21 and gltr.glid <= :m22
EXCELSQL;and gltr.datedoc < :m11 
EXCELSQL;and gltr.jobno >= :m31 and gltr.jobno <= :m32
EXCELSQL;group by gltr.GLID, glmst.GLDESC,gltr.jobno, jobmst.jdesc&
EXCELSQL;
EXCELSQL;alter table _glcard
EXCELSQL;   add index _glcard (glid,jobno)&
EXCELSQL;
EXCELSQL;
EXCELSQL;insert into _glcard
EXCELSQL;select jobtail.GLID, glmst.GLDESC,curdate() as datedoc, 'ยอดยกมา', '', glmst.GLTYPE, 0 as debit, 0 as credit,
EXCELSQL;if(instr('AEC',left(glmst.GLTYPE,1)) > 0,jobtail.begdb - jobtail.begcr, jobtail.begcr - jobtail.begdb) as nbal , 0 as line, if(instr('AEC',left(glmst.GLTYPE,1)) > 0 , "D","C") as nature,
EXCELSQL;jobtail.jobno, jobmst.jdesc,"N" as nomove
EXCELSQL;from jobtail inner join glmst on jobtail.GLID = glmst.GLID
EXCELSQL;inner join jobmst on jobtail.jobno = jobmst.jobno
EXCELSQL;where jobtail.glid >= :m21 and jobtail.glid <= :m22
EXCELSQL;and jobtail.jobno >= :m31 and jobtail.jobno <= :m32&
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,jdesc,nomove
EXCELSQL;from _glcard
EXCELSQL;group by glid,gldesc,datedoc,docno,xremline,gltype,nature ,jobno,jdesc&
EXCELSQL;
EXCELSQL;drop table if exists _nomove&
EXCELSQL;create table _nomove
EXCELSQL;select glid,jobno from _glcard2 group by glid,jobno having count(*) = 1&
EXCELSQL;
EXCELSQL;alter table _nomove
EXCELSQL;   add index _nomove (glid,jobno)&
EXCELSQL;
EXCELSQL;update _glcard2 inner join _nomove on _glcard2.glid = _nomove.glid
EXCELSQL;and _glcard2.jobno = _nomove.jobno
EXCELSQL;set _glcard2.nomove = "Y"&
EXCELSQL;
EXCELSQL;select * from _glcard2 order by 
EXCELSQL;jobno,glid,line, datedoc
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
