TITLE;กราฟเปรียบเทียบ งบกำไรขาดทุนทั้งปี ต่อไตรมาส
SQL;drop table if exists _pfyr&
SQL;
SQL;create table _pfyr
SQL;select datedoc,year(datedoc) as xyear,sum(credit-debit) as rev,0000000000.00 as exp,0000000000.00 as cost,0000000000.00 as pf,1 as mflag,1 as mline
SQL;from gltr inner join glmst on gltr.glid = glmst.glid
SQL;where left(gltype,1) = 'R'
SQL;group by 2,1
SQL;order by 2,1&
SQL;
SQL;insert into _pfyr
SQL;select datedoc,year(datedoc) as xyear,0000000000.00 as rev,sum(debit-credit) as exp,0000000000.00 as cost,0000000000.00 as pf,-1 as mflag,2 as mline
SQL;from gltr inner join glmst on gltr.glid = glmst.glid
SQL;where left(gltype,1) = 'E'
SQL;group by 2,1
SQL;order by 2,1&
SQL;
SQL;insert into  _pfyr
SQL;select datedoc,year(datedoc) as xyear,0000000000.00 as rev,0000000000.00 as exp,sum(debit-credit) as cost,0000000000.00 as pf,-1 as mflag,3 as mline
SQL;from gltr inner join glmst on gltr.glid = glmst.glid
SQL;where left(gltype,1) = 'C'
SQL;group by 2,1
SQL;order by 2,1&
SQL;
SQL;update _pfyr set pf = rev-exp-cost&
SQL;
SQL;delete from _pfyr where xyear <> :m11&
SQL;
SQL;drop table if exists _saleyear1&
SQL;
SQL;create table _saleyear1
SQL;select QUARTER(datedoc) as mth,space(20) as fullmth,
SQL;sum(Rev) as rev,
SQL;sum(Cost) as cost,
SQL;sum(exp) as exp,
SQL;sum(pf) as pf
SQL;from _pfyr
SQL;group by 1,2&
SQL;
SQL;update _saleyear1 set fullmth = 'Quarter-1' where Mth = 1&
SQL;update _saleyear1 set fullmth = 'Quarter-2' where Mth = 2&
SQL;update _saleyear1 set fullmth = 'Quarter-3' where Mth = 3&
SQL;update _saleyear1 set fullmth = 'Quarter-4' where Mth = 4&
SQL;
SQL;select * from _saleyear1 order by Mth
SQL;
CONDITION;เลือกข้อมูลปี~YEAR~Chr~1~SELECT YEAR(CURDATE()) FROM CONFIG~
SERIES;1~FULLMTH~REV~รายได้
SERIES;2~FULLMTH~COST~ต้นทุน
SERIES;3~FULLMTH~EXP~ค่าใช้จ่าย
SERIES;4~FULLMTH~PF~กำไร/ขาดทุน
