** ---------------------------- ** 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 :
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 ;