** ----------------------------
** generated by Mandragore Planete
** Time Date : 12:00 07/31/2022
** ----------------------------
#include "#SYSPATHMS4BI+\zInclude\includeChart.h"
#include "#SYSPATHMS4BI+\zInclude\IncludeDisplay.h"
#include "#SYSPATHMS4BI+\zInclude\IncludeSysSqlMs4.h"
text :lDirCur ,lDirAppDB ,database_name :="03-ExFamilyBudget.db";
integer : x;
BEGIN ** <----Local_PC ------>
GetCurrentDir(lDirCur); str_Format(lDirAppDB,"%s%s", lDirCur,database_name);
** ///////////////////NEW TABLE Family//////////////////////////////////////
DBConnect{Ms4DB}(lDirAppDB);
Ms4_SQL{MS4DB}( "CREATE TABLE IF NOT EXISTS Family ( YEAR int, PROJECTED real, MONTH int, CATEGORY text, ACTUAL real )") return (x);
if(x != MS4_SQL_OK)
then
DBClose{Ms4DB}(lDirAppDB);
DBDelete{Ms4DB}(lDirAppDB);
exit ;
end_if ;
Call:Ms4Script("Ms4Wait2");
END ; ** <----Local_PC ------>
** *----Next---> : Local_SERVER -------------------------< **
DBBackup{Ms4db}(lDirAppDB) to ("#WWWMS4BISERVERBACKUP+\03-ExFamilyBudget.db") return (x);
** if(x =1)then display "Backup Ok" ; else "Backup ko" ;end_if ;
Begin_TransactionWeb : "01-DashBoardMonth" ,
Cycle : NoCycle ;
backgroundColor: color(192,192,192) ;
** started !!!
BEGIN
MS4_SQL{ms4db}(" DROP VIEW IF EXISTS tmp_gentable_ms40001 ");
MS4_SQL{ms4db}(" CREATE TEMPORARY VIEW IF NOT EXISTS tmp_gentable_ms40001 AS "+
" select " +
" distinct month , year " +
" from family " +
" where month=02 and year =2022 " +
" ");
position : 31,80,135,257 :
Ms4_Query: Custom {ms4db}
Option :
BEGIN
pagesize:0,
titlesql:0,
END
begin_sw :
end_sw
( "select " +
" distinct month , year " +
" from family " +
" where month=02 and year =2022 " +
" ") return x;
END ;
BEGIN
position : 37,400,auto,auto :
;
END ;
position : 52,603,auto,881 :
>;
position:159, 618, 77, 841 : ;
BEGIN
position : 217,124,auto,585 :
Ms4_Query: Custom {ms4db}
Option :
BEGIN
pagesize:30,
titlesql:0,
inlineEdit:table : "Family" , field : "ACTUAL,PROJECTED",
END
title : ""
#include "#CURRENTDIR+\Transaction03-ExExcelBudgetReportStyle3.css.ms4"
( "select Family.rowid, CATEGORY as toto,
PROJECTED ,
ACTUAL,
printf('%.02f $', PROJECTED-ACTUAL ) as VARIANCE
from Family
where
month=2 and year=2022 and category not like('%Income%')") return x;
END ;
position : 287,760,auto,681 :
>;
BEGIN
MS4_SQL{ms4db}(" DROP VIEW IF EXISTS tmp_gentable_ms40005 ");
MS4_SQL{ms4db}(" CREATE TEMPORARY VIEW IF NOT EXISTS tmp_gentable_ms40005 AS "+
" select category, PROJECTED " +
" from family " +
" where " +
" month=2 and year=2022 and category not like('%Income%') " +
" ");
position : 311,854,275,353 :
setting : chart : legend : 0 ,
FormMorris : DrawPie ,height : "275", width : "353" title :"" , TYPE : $Donut , FORMAT: "" use View ("tmp_gentable_ms40005") ;
END ;
BEGIN
MS4_SQL{ms4db}(" DROP VIEW IF EXISTS tmp_gentable_ms40006 ");
MS4_SQL{ms4db}(" CREATE TEMPORARY VIEW IF NOT EXISTS tmp_gentable_ms40006 AS "+
" select category, PROJECTED " +
" from family " +
" where " +
" month=2 and year=2022 and category not like('%Income%') " +
" ");
position : 625,848,220,476 :
FormMorris : DrawBar ,height : "220", width : "476" title :"" , TYPE : $Column, FORMAT: "" use View ("tmp_gentable_ms40006") ;
END ;
position : 982,763,auto,679 :
>;
BEGIN
MS4_SQL{ms4db}(" DROP VIEW IF EXISTS tmp_gentable_ms40008 ");
MS4_SQL{ms4db}(" CREATE TEMPORARY VIEW IF NOT EXISTS tmp_gentable_ms40008 AS "+
" select category, ACTUAL " +
" from family " +
" where " +
" month=2 and year=2022 and category not like('%Income%') " +
" ");
position : 1007,796,460,536 :
setting : chart : Redraw : 1,counter :99 ,
FormPlus : DrawBar ,height : "460", width : "536" title :"" ,
TYPE : $BAR
FORMAT: "" use View ("tmp_gentable_ms40008") ;
END ;
BEGIN
MS4_SQL{ms4db}(" DROP VIEW IF EXISTS tmp_gentable_ms40009 ");
MS4_SQL{ms4db}(" CREATE TEMPORARY VIEW IF NOT EXISTS tmp_gentable_ms40009 AS "+
" select category, ACTUAL " +
" from family " +
" where " +
" month=2 and year=2022 and category not like('%Income%') " +
" ");
position : 1070,946,311,474 :
setting : chart : legend : 0 ,
FormPlus : DrawPie ,height : "311", width : "474" title :"" , ANGLE :150 , TYPE : $Donut , FORMAT: "" use View ("tmp_gentable_ms40009") ;
END ;
BEGIN
MS4_SQL{ms4db}(" DROP VIEW IF EXISTS tmp_gentable_ms40010 ");
MS4_SQL{ms4db}(" CREATE TEMPORARY VIEW IF NOT EXISTS tmp_gentable_ms40010 AS "+
" select " +
" printf('%+.02f ', " +
" sum(PROJECTED) ) '∑ PROJECTED', " +
" printf('%+.02f ', " +
" sum(ACTUAL) ) '∑ ACTUAL', " +
" printf('%+.02f $ ', " +
" ( sum(PROJECTED)- sum(ACTUAL) ) ) VARIANCE " +
" from family " +
" where " +
" month=2 and year=2022 " +
" and category not like('%Income%') " +
" ");
position : 1333,158,83,511 :
Ms4_Query: Custom {ms4db}
Option :
BEGIN
pagesize:0,
titlesql:0,
END
#include "#CURRENTDIR+\Transaction03-ExExcelBudgetReportStyle10.css.ms4"
( "select " +
" printf('%+.02f ', " +
" sum(PROJECTED) ) '∑ PROJECTED', " +
" printf('%+.02f ', " +
" sum(ACTUAL) ) '∑ ACTUAL', " +
" printf('%+.02f $ ', " +
" ( sum(PROJECTED)- sum(ACTUAL) ) ) VARIANCE " +
" from family " +
" where " +
" month=2 and year=2022 " +
" and category not like('%Income%') " +
" ") return x;
END ;
position : 1589,87,auto,739 :
>;
BEGIN
position : 1627,130,auto,595 :
Ms4_Query: Custom {ms4db}
Option :
BEGIN
pagesize:10,
titlesql:0,
inlineEdit:table : "Family" , field : "PROJECTED,ACTUAL",
END
title : ""
#include "#CURRENTDIR+\Transaction03-ExExcelBudgetReportStyle12.css.ms4"
( "select Family.rowid, CATEGORY as CatIncome ,
PROJECTED ,
ACTUAL,
printf('%.02f $', PROJECTED-ACTUAL ) as BALANCE
from Family
where
month=2 and year=2022 and category like('%Income%')") return x;
END ;
BEGIN
MS4_SQL{ms4db}(" DROP VIEW IF EXISTS tmp_gentable_ms40013 ");
MS4_SQL{ms4db}(" CREATE TEMPORARY VIEW IF NOT EXISTS tmp_gentable_ms40013 AS "+
" select 'INCOME' as INCOME, " +
" sum(PROJECTED ) as 'PROJECTED' , " +
" sum(ACTUAL) as 'ACTUAL' , " +
" sum(ACTUAL)-sum(PROJECTED) as 'Variance' " +
" from family " +
" where " +
" month=2 " +
" and year=2022 " +
" and category like('%Income%') " +
" ");
position : 1739,893,231,366 :
, >; /* end panel */
END ;
BEGIN
MS4_SQL{ms4db}(" DROP VIEW IF EXISTS tmp_gentable_ms40014 ");
MS4_SQL{ms4db}(" CREATE TEMPORARY VIEW IF NOT EXISTS tmp_gentable_ms40014 AS "+
" select " +
" printf('%+.02f ', sum(PROJECTED) ) '∑ PROJECTED', " +
" printf('%+.02f ', sum(ACTUAL) ) '∑ ACTUAL', " +
" printf(' %+.02f $ ', ( sum(ACTUAL)- sum(PROJECTED) ) ) Variance " +
" from family " +
" where " +
" month=2 and year=2022 and category like('%Income%') " +
" ");
position : 1849,150,75,458 :
Ms4_Query: Custom {ms4db}
Option :
BEGIN
pagesize:0,
titlesql:0,
END
#include "#CURRENTDIR+\Transaction03-ExExcelBudgetReportStyle14.css.ms4"
( "select " +
" printf('%+.02f ', sum(PROJECTED) ) '∑ PROJECTED', " +
" printf('%+.02f ', sum(ACTUAL) ) '∑ ACTUAL', " +
" printf(' %+.02f $ ', ( sum(ACTUAL)- sum(PROJECTED) ) ) Variance " +
" from family " +
" where " +
" month=2 and year=2022 and category like('%Income%') " +
" ") return x;
END ;
position : 2093,154,auto,1185 :
>;
BEGIN
MS4_SQL{ms4db}(" DROP VIEW IF EXISTS tmp_gentable_ms40016 ");
MS4_SQL{ms4db}(" CREATE TEMPORARY VIEW IF NOT EXISTS tmp_gentable_ms40016 AS "+
" select category as CASH , PROJECTED,ACTUAL , " +
" PROJECTED-ACTUAL as Variance " +
" from family " +
" where " +
" month=9999 " +
" union all " +
" select 'Total Incomes', " +
" printf('%+.02f ',sum(PROJECTED ) ) as PROJECTED1 , " +
" printf('%+.02f ',sum(ACTUAL ) ) as ACTUAL1 , " +
" printf('%+.02f $ ',sum(ACTUAL)-sum(PROJECTED ) ) as Variance1 " +
" from family " +
" where " +
" category like('%Income%') " +
" and month=2 and year=2022 " +
" union all " +
" select 'Total Expenses', " +
" printf('-%.02f ',sum(PROJECTED ) ) as PROJECTED2 , " +
" printf('-%.02f ',sum(ACTUAL ) ) as ACTUAL2 , " +
" printf('%+.02f $ ',sum(PROJECTED )-sum(ACTUAL)) as Variance2 " +
" from family " +
" where " +
" category not like('%Income%') " +
" and month=2 and year=2022 " +
" ");
position : 2147,258,152,520 :
Ms4_Query: Custom {ms4db}
Option :
BEGIN
pagesize:0,
titlesql:0,
END
begin_sw :
end_sw
( "select category as CASH , PROJECTED,ACTUAL , " +
" PROJECTED-ACTUAL as Variance " +
" from family " +
" where " +
" month=9999 " +
" union all " +
" select 'Total Incomes', " +
" printf('%+.02f ',sum(PROJECTED ) ) as PROJECTED1 , " +
" printf('%+.02f ',sum(ACTUAL ) ) as ACTUAL1 , " +
" printf('%+.02f $ ',sum(ACTUAL)-sum(PROJECTED ) ) as Variance1 " +
" from family " +
" where " +
" category like('%Income%') " +
" and month=2 and year=2022 " +
" union all " +
" select 'Total Expenses', " +
" printf('-%.02f ',sum(PROJECTED ) ) as PROJECTED2 , " +
" printf('-%.02f ',sum(ACTUAL ) ) as ACTUAL2 , " +
" printf('%+.02f $ ',sum(PROJECTED )-sum(ACTUAL)) as Variance2 " +
" from family " +
" where " +
" category not like('%Income%') " +
" and month=2 and year=2022 " +
" ") return x;
END ;
BEGIN
MS4_SQL{ms4db}(" DROP VIEW IF EXISTS tmp_gentable_ms40017 ");
MS4_SQL{ms4db}(" CREATE TEMPORARY VIEW IF NOT EXISTS tmp_gentable_ms40017 AS "+
" select ' PROJECTED' as CASH, " +
" SUM( " +
" CASE WHEN category like ('%Income%') " +
" THEN PROJECTED * 1 " +
" ELSE PROJECTED * -1 " +
" END ) PROJECTED " +
" from family " +
" where " +
" month=2 and year=2022 " +
" union all " +
" select ' ACTUAL' as CASH, " +
" SUM( " +
" CASE WHEN category like ('%Income%') " +
" THEN ACTUAL* 1 " +
" ELSE ACTUAL * -1 " +
" END ) ACTUAL " +
" from family " +
" where " +
" month=2 and year=2022 " +
" union all " +
" select ' VARIANCE' as CASH, " +
" SUM( " +
" CASE WHEN category like ('%Income%') " +
" THEN ACTUAL-PROJECTED " +
" ELSE (ACTUAL* -1) - (PROJECTED * -1 ) " +
" END) Variance " +
" from family " +
" where " +
" month=2 and year=2022 " +
" ");
position : 2253,1010,214,248 :
setting : chart : legend : 0 ,
FormChartJS : DrawPie ,height : "214", width : "248" title :"" , TYPE : $Donut , FORMAT: "" use View ("tmp_gentable_ms40017") ;
END ;
BEGIN
MS4_SQL{ms4db}(" DROP VIEW IF EXISTS tmp_gentable_ms40018 ");
MS4_SQL{ms4db}(" CREATE TEMPORARY VIEW IF NOT EXISTS tmp_gentable_ms40018 AS "+
" " +
" " +
" select " +
" printf('%+.02f $ ', " +
" SUM( " +
" CASE WHEN category like ('%Income%') " +
" THEN PROJECTED * 1 " +
" ELSE PROJECTED * -1 " +
" END) ) '∑ PROJECTED' , " +
" printf('%+.02f $ ', " +
" SUM( " +
" CASE WHEN category not like ('%Income%') " +
" THEN ACTUAL* -1 " +
" ELSE ACTUAL *1 " +
" END) " +
" ) '∑ ACTUAL' , " +
" " +
" printf('%+.02f $ ', " +
" SUM( " +
" CASE WHEN category like ('%Income%') " +
" THEN ACTUAL-PROJECTED " +
" ELSE (ACTUAL* -1) - (PROJECTED * -1 ) " +
" END) ) '∑ Variance' " +
" from family " +
" where " +
" month=2 and year=2022 " +
" ");
position : 2367,494,89,473 :
Ms4_Query: Custom {ms4db}
Option :
BEGIN
pagesize:0,
titlesql:0,
END
begin_sw :
end_sw
( " " +
" " +
" select " +
" printf('%+.02f $ ', " +
" SUM( " +
" CASE WHEN category like ('%Income%') " +
" THEN PROJECTED * 1 " +
" ELSE PROJECTED * -1 " +
" END) ) '∑ PROJECTED' , " +
" printf('%+.02f $ ', " +
" SUM( " +
" CASE WHEN category not like ('%Income%') " +
" THEN ACTUAL* -1 " +
" ELSE ACTUAL *1 " +
" END) " +
" ) '∑ ACTUAL' , " +
" " +
" printf('%+.02f $ ', " +
" SUM( " +
" CASE WHEN category like ('%Income%') " +
" THEN ACTUAL-PROJECTED " +
" ELSE (ACTUAL* -1) - (PROJECTED * -1 ) " +
" END) ) '∑ Variance' " +
" from family " +
" where " +
" month=2 and year=2022 " +
" ") return x;
END ;
position : 2538,29,54,424 :
;
position : 2614,41,auto,1663 :
>;
BEGIN
position : 2657,81,auto,auto :
;
END ;
BEGIN
position : 2660,607,auto,auto :
;
END ;
BEGIN
position : 2660,1130,auto,auto :
;
END ;
BEGIN
;
END ;
position : 3130, 140, auto, 254 :
begin_div : Frame "commentsmall" /* style */
Interested in our project? %%contact@mgplanete.com%%
\n /b The Mg Planete team /b
end_div ;
position : 3315, 140, auto, 266 :
BEGIN_MARKER
**the official sites :** \n
$$www.mgplanete.com$$ \n
$$getms4bi.com$$ \n
$$demos.ms4bi.com$$ \n
$$help.ms4bi.com$$ \n
$$www.ms4script.com$$
END_MARKER ;
BEGIN
position : 3322,453,auto,auto :
FormPlus : DrawQrCODE type : $URL ("https://getms4bi.com") ;
END ;
End_TransactionWeb ;