REPORTFORM;WK1.frf
TITLE;กระดาษทำการ
SQL;drop table if exists _WorkSheet_0&
SQL;create table _WorkSheet_0
SQL;select space(5),"กำไร/ขาดทุน ภายในปี",
SQL;"XXX","O1-ทุน","" as glgroupdesc,"7" as xline,
SQL;sum(debit) as xdebit, sum(credit) as xredit, 0000000000.00 as xbal,
SQL;0000000000.00 as tdebit, 0000000000.00 as tcredit,
SQL;0000000000.00 as bdebit, 0000000000.00 as bcredit,
SQL;0000000000.00 as pdebit, 0000000000.00 as pcredit 
SQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
SQL;where instr('RCE',left(glmst.GLTYPE,1)) > 0
SQL;and datedoc <= :m11
SQL;&
SQL;
SQL;drop table if exists _WorkSheet_1&
SQL;
SQL;create table _WorkSheet_1
SQL;SELECT gltr.GLID, glmst.GLDESC, glmst.GLGROUP, glmst.GLTYPE, glgroup.glgroupdesc,
SQL;if(left(glmst.GLTYPE,1)='A','1',if(left(glmst.GLTYPE,1)='L','2',if(left(glmst.GLTYPE,1)='O','3',if(left(glmst.GLTYPE,1)='R','4',if(left(glmst.GLTYPE,1)='C','5','6'))))) as Xline,
SQL;sum(debit) as xdebit, sum(credit) as xcredit, 0000000000.00 as xbal,
SQL;0000000000.00 as tdebit, 0000000000.00 as tcredit,
SQL;0000000000.00 as bdebit, 0000000000.00 as bcredit,
SQL;0000000000.00 as pdebit, 0000000000.00 as pcredit
SQL;FROM gltr inner join glmst on gltr.GLID = glmst.GLID
SQL;left join glgroup on glmst.GLGROUP = glgroup.glgroup
SQL;WHERE gltr.datedoc <= :m11
SQL;group by gltr.GLID, glmst.GLDESC,glmst.GLGROUP, glmst.GLTYPE,glgroup.glgroupdesc&
SQL;
SQL;insert into _WorkSheet_1
SQL;select * from _WorkSheet_0&
SQL;
SQL;drop table if exists _WorkSheet_0&
SQL;
SQL;update _WorkSheet_1 set xbal = xdebit - xcredit where instr('AEC',left(gltype,1)) > 0&
SQL;update _WorkSheet_1 set xbal = xcredit - xdebit where instr('AEC',left(gltype,1)) = 0&
SQL;
SQL;update _WorkSheet_1 set tdebit = xbal where instr('AEC',left(gltype,1)) > 0 and xbal > 0&
SQL;update _WorkSheet_1 set tcredit = abs(xbal) where instr('AEC',left(gltype,1)) > 0 and xbal < 0&
SQL;update _WorkSheet_1 set tcredit = xbal where instr('AEC',left(gltype,1)) = 0 and xbal > 0&
SQL;update _WorkSheet_1 set tdebit = abs(xbal) where instr('AEC',left(gltype,1)) = 0 and xbal < 0&
SQL;
SQL;update _WorkSheet_1 set bdebit = tdebit,bcredit = tcredit where instr('ALO',left(gltype,1)) > 0 &
SQL;update _WorkSheet_1 set pdebit = tdebit,pcredit = tcredit where instr('ALO',left(gltype,1)) = 0 &
SQL;update _WorkSheet_1 set tdebit = 0, tcredit = 0 where glgroup = 'XXX'&
SQL;update _WorkSheet_1 set pdebit = bcredit where glgroup = 'XXX' and bcredit > 0&
SQL;update _WorkSheet_1 set pcredit = bdebit where glgroup = 'XXX' and bdebit > 0&
SQL;
SQL;select * from _WorkSheet_1 order by Xline,GLID
CONDITION;สิ้นสดุ ณ วันที่~DATEDOC~Date~1~~
EXCELSQL;drop table if exists _WorkSheet_0
EXCELSQL;create table _WorkSheet_0
EXCELSQL;select space(5),"กำไร/ขาดทุน ภายในปี",
EXCELSQL;"XXX","O1-ทุน","" as glgroupdesc,"7" as xline,
EXCELSQL;sum(debit) as xdebit, sum(credit) as xredit, 0000000000.00 as xbal,
EXCELSQL;0000000000.00 as tdebit, 0000000000.00 as tcredit,
EXCELSQL;0000000000.00 as bdebit, 0000000000.00 as bcredit,
EXCELSQL;0000000000.00 as pdebit, 0000000000.00 as pcredit 
EXCELSQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;where instr('RCE',left(glmst.GLTYPE,1)) > 0
EXCELSQL;and datedoc <= :m11
EXCELSQL;drop table if exists _WorkSheet_1
EXCELSQL;create table _WorkSheet_1
EXCELSQL;SELECT gltr.GLID, glmst.GLDESC, glmst.GLGROUP, glmst.GLTYPE, glgroup.glgroupdesc,
EXCELSQL;if(left(glmst.GLTYPE,1)='A','1',if(left(glmst.GLTYPE,1)='L','2',if(left(glmst.GLTYPE,1)='O','3',if(left(glmst.GLTYPE,1)='R','4',if(left(glmst.GLTYPE,1)='C','5','6'))))) as Xline,
EXCELSQL;sum(debit) as xdebit, sum(credit) as xcredit, 0000000000.00 as xbal,
EXCELSQL;0000000000.00 as tdebit, 0000000000.00 as tcredit,
EXCELSQL;0000000000.00 as bdebit, 0000000000.00 as bcredit,
EXCELSQL;0000000000.00 as pdebit, 0000000000.00 as pcredit
EXCELSQL;FROM gltr inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;left join glgroup on glmst.GLGROUP = glgroup.glgroup
EXCELSQL;WHERE gltr.datedoc <= :m11
EXCELSQL;group by gltr.GLID, glmst.GLDESC,glmst.GLGROUP, glmst.GLTYPE,glgroup.glgroupdesc
EXCELSQL;insert into _WorkSheet_1
EXCELSQL;select * from _WorkSheet_0
EXCELSQL;drop table if exists _WorkSheet_0
EXCELSQL;update _WorkSheet_1 set xbal = xdebit - xcredit where instr('AEC',left(gltype,1)) > 0
EXCELSQL;update _WorkSheet_1 set xbal = xcredit - xdebit where instr('AEC',left(gltype,1)) = 0
EXCELSQL;update _WorkSheet_1 set tdebit = xbal where instr('AEC',left(gltype,1)) > 0 and xbal > 0
EXCELSQL;update _WorkSheet_1 set tcredit = abs(xbal) where instr('AEC',left(gltype,1)) > 0 and xbal < 0
EXCELSQL;update _WorkSheet_1 set tcredit = xbal where instr('AEC',left(gltype,1)) = 0 and xbal > 0
EXCELSQL;update _WorkSheet_1 set tdebit = abs(xbal) where instr('AEC',left(gltype,1)) = 0 and xbal < 0
EXCELSQL;update _WorkSheet_1 set bdebit = tdebit,bcredit = tcredit where instr('ALO',left(gltype,1)) > 0 
EXCELSQL;update _WorkSheet_1 set pdebit = tdebit,pcredit = tcredit where instr('ALO',left(gltype,1)) = 0 
EXCELSQL;update _WorkSheet_1 set tdebit = 0, tcredit = 0 where glgroup = 'XXX'
EXCELSQL;update _WorkSheet_1 set pdebit = bcredit where glgroup = 'XXX' and bcredit > 0
EXCELSQL;update _WorkSheet_1 set pcredit = bdebit where glgroup = 'XXX' and bdebit > 0
EXCELSQL;select * from _WorkSheet_1 order by Xline,GLID
