** ****************************************************************************************** *
** MANDRAGORE PLANETE
** *******************************************************************************************
#include "#SYSPATHMS4BI+\zInclude\IncludeDisplay.h"
#include "#SYSPATHMS4BI+\zInclude\includeChart.h"
text : LCurrentDir , table1, Table1SheetJapanese,Table1Sheetchinese, LpathDataTempExcel;
text : LFile1Excel:="#DRIVERSYS+\MANDRAGOREPLANETE\MS4BI\01-GETTING-STARTED-UNICODE\japaneseExample.xls" ;
text : LFile2Excel:="#DRIVERSYS+\MANDRAGOREPLANETE\MS4BI\01-GETTING-STARTED-UNICODE\list_of_cities_in_china-44j.xls" ;
text : LFile3Excel:="#DRIVERSYS+\MANDRAGOREPLANETE\MS4BI\01-GETTING-STARTED-UNICODE\list_of_cities_in_china_LatitudeLongitude.xlsx" ;
text : LocalServerDB :="#WWWMS4BISERVER+\Ms4BI-DemoUnicodeCube.db";
integer : x;
integer : yes :=1,msgsql;
Getcurrentdir(LCurrentDir);
;
Begin_Block_Rwd : full_page
;
printsw "\n ###b+c Build MS4DB (only Windows) : EXCEL CONSOLIDATION (example) : \n /r READ MS4 SCRIPT CODE /r ###b+c ";
End_Block_Rwd ;
Display "------------------------------------------------------------------------- " ;
Display " Create DB Lang Consolidation : " ;
Display "------------------------------------------------------------------------- " ;
Display "------------------------------------------------------------------------- " ;
Display " Consolidation Excel to MS4BI DB : EXCEL unicode TABLES " ;
Display "------------------------------------------------------------------------- " ;
BEGIN ** **********************INIT ***********
DBexist(LocalServerDB) return (X) ;
if (x not_void ) then
Display " DB exist Ok : DELETE !! " ,LocalServerDB Column 40;
ELSE
Display "DB not found : ",LocalServerDB;
End_if ;
DBDelete{MS4DB}(LocalServerDB);
Tableexist(Lfile1Excel) return (X) ;
if (x not_void ) then
Display " Table Ok : continue " ,Lfile1Excel Column 40;
ELSE
Display "Table not found : pb database !! ",Lfile1Excel;
Exit;
End_if ;
Tableexist(Lfile2Excel) return (X) ;
if (x not_void) then
Display " Table Ok " ,Lfile2Excel Column 40;
ELSE
Display "Table not found : ",Lfile2Excel; Exit;
Exit;
End_if ;
GetDirSysPathMS4BI(LpathDataTempExcel);
** important : Ms4_SQL{Ado:"xls"}(" SELECT * into " + " %s " : into %s ****** this temporary path not excedeed 128 caracters !!
Concatene LpathDataTempExcel,"\ztmp\l.XLS" into Table1; ** tempory table = CONSOLIDATION orders+ employees
Concatene LpathDataTempExcel,"\ztmp\l.lang1" into Table1SheetJapanese;
Concatene LpathDataTempExcel,"\ztmp\l.lang2" into Table1Sheetchinese;
Tableexist(Table1) return (X) ;
/* 2 return codes */
** x = 0 : Ok , x = 1 Not ok */
if (x not_void) then
Tabledelete (Table1) ;
Display " TableDelete Ok " ,Table1 Column 60;
ELSE
Display "Table Delete not found : ",Table1;
End_if ;
END ;
BEGIN ** CONSOLIDATION **************tables : japanese and china ->into Excel table temp *********
SetUniCode(1);
** sheet 1
DBconnect{Ado:"xls"}( LFile1Excel);
display "insert Table Japanese : CONSOLIDATION 1 ";
Ms4_SQL{Ado:"xls"}(" SELECT * into " + " %s " + " from [中国最大の都市のリスト$]", Table1SheetJapanese) return x ;
display " 中国最大の都市のリスト sql return :",x;
** sheet 2
DBconnect{Ado:"xls"}(Lfile2Excel);
display "insert Table Chinese : CONSOLIDATION 2 ";
Ms4_SQL{Ado:"xls"}(" SELECT * into %s " + " from [Sheet1$]",Table1Sheetchinese) return x;
display " Chinese sql return :",x;
END ;
BEGIN ** Excute query table temp TABLE1 for REPORT
display "IMPORTANT Create MS4 CUBE Lang UNICODE for Input DATA : CHART : PIE, BAR, DASHBOARD, REPORTING ..",LocalServerDB ;
** *******************************************************************
** INSERT a FILE xls, txt ..another in DB MS4
** Create Cube : ms4LangConsolidation Example
** *******************************************************************
DBconnect{Ado:"xls"}(Table1);
IMPORT : New Ms4Db
BEGIN
DBconnect {Ms4DB} (LocalServerDB) ,
Create:Table("[ms4LangConsolidation]")
END
(" SELECT a.SNO , 都市名 as CityName, 英語 as CityEnglish, POPULATION ,"+
" 備考 as proforma, CITY , 城市 As CityChinese, "+
" PROVINCE,mapcode, PREFECTURE , TYPELevel , FOUNDED ,b.sno as snoduplicate" +
" from [lang1$] as a ,[lang2$] as b " +
" where a.英語 = b.city order by b.city " );
** **********************************
** INSERT Longitude/Latitude
** **********************************
DBconnect{Ado:"xlsx"}(LFile3Excel);
IMPORT : New Ms4Db
BEGIN
DBconnect {Ms4DB} (LocalServerDB) ,
Create:Table("[ms4ChinaLatitudeLongitude]")
END
(" SELECT * from [LatiLongi$] ");
DBBackup{Ms4db}(LocalServerDB) to( "#WWWMS4BISERVERBACKUP+\Ms4BI-DemoUnicodeCube.db") return (msgsql);
if(msgsql= 1)
then
display " BACKUP : ok #WWWMS4BISERVERBACKUP+ local WEB backup";
else
display " BACKUP : ko #WWWMS4BISERVERBACKUP+ local WEB backup";
end_if;
DBconnect {Ms4DB} (LocalServerDB);
FormPlus : DrawGrid, title :"Result : Excel Consolidation -> MS4 DB : ms4LangConsolidation China/Japan : Relation -> 英語 = b.city "
use View ("[ms4LangConsolidation]") ;
FormPlus : DrawGrid, title :"Result : Excel Consolidation -> MS4 DB : ms4ChinaLatitudeLongitude "
use View ("[ms4ChinaLatitudeLongitude]") ;
Display " Create DB Lang Consolidation and Latitude/Longitude : End ",LocalServerDB ;
SetColorText(Hwdisplay:green,black) ;
Display " to continue, Click here ";
enter;
END;