001 |    
002 | ** ****************************************************************************************** *
003 | ** MANDRAGORE PLANETE  
004 | ** *******************************************************************************************
005 | #include  "#SYSPATHMS4BI+\zInclude\IncludeDisplay.h"
006 | #include  "#SYSPATHMS4BI+\zInclude\includeChart.h"
007 | text : LCurrentDir ,  table1, Table1SheetJapanese,Table1Sheetchinese, LpathDataTempExcel;
008 |  
009 | text : LFile1Excel:="#DRIVERSYS+\MANDRAGOREPLANETE\MS4BI\01-GETTING-STARTED-UNICODE\japaneseExample.xls"    ;
010 | text : LFile2Excel:="#DRIVERSYS+\MANDRAGOREPLANETE\MS4BI\01-GETTING-STARTED-UNICODE\list_of_cities_in_china-44j.xls"    ;
011 | text : LFile3Excel:="#DRIVERSYS+\MANDRAGOREPLANETE\MS4BI\01-GETTING-STARTED-UNICODE\list_of_cities_in_china_LatitudeLongitude.xlsx"    ;
012 | text : LocalServerDB :="#WWWMS4BISERVER+\Ms4BI-DemoUnicodeCube.db";  
013 | integer : x;
014 | integer : yes :=1,msgsql;
015 | Getcurrentdir(LCurrentDir);
016 |  
017 | 
018 |       <div  $IsVisible_lg :  /* visible only large (screen) */  
019 |       DrawWindow : "Win0302" , actions : $WinCustom +  $WinMinimize + $WinMaximize + $WinClose  , position :  150,350,1750,1050  TITLE : "  Window Web: MS4 script Rapid Developt" 
020 |                                       url :  UrlMs4Script  /* key word ms4script */
021 |     >;
022 |     
023 | Begin_Block_Rwd  :  full_page
024 |          <notification : $Bts_Notif_success_Dismissable ,  " ###b Step 2  :  Build MS4 DB - Consolidation of 2 Excel files   ###b "> ;
025 |          printsw "\n ###b+c Build MS4DB (only Windows) : EXCEL CONSOLIDATION (example) :  \n /r READ MS4 SCRIPT CODE /r ###b+c ";
026 | End_Block_Rwd ; 
027 |  
028 |  
029 |     Display "------------------------------------------------------------------------- " ;
030 |     Display "    Create DB Lang Consolidation :                                                   " ;
031 |     Display "------------------------------------------------------------------------- " ;
032 | 
033 |  
034 |     Display "------------------------------------------------------------------------- " ;
035 |     Display "     Consolidation Excel to MS4BI DB   :  EXCEL unicode TABLES " ;
036 |     Display "------------------------------------------------------------------------- " ;
037 | 
038 | BEGIN ** **********************INIT ***********
039 |     DBexist(LocalServerDB) return (X) ;
040 |     if (x not_void ) then
041 |      
042 |         Display " DB exist   Ok : DELETE !!   " ,LocalServerDB Column 40;
043 |     ELSE
044 |           Display "DB   not found   :  ",LocalServerDB;  
045 |      
046 |     End_if ;
047 |     DBDelete{MS4DB}(LocalServerDB);
048 |       Tableexist(Lfile1Excel) return (X) ;
049 |     if (x not_void ) then
050 |      
051 |         Display " Table  Ok : continue  " ,Lfile1Excel Column 40;
052 |     ELSE
053 |           Display "Table   not found : pb database !! ",Lfile1Excel;  
054 |     Exit;
055 |     End_if ;
056 |  
057 |     Tableexist(Lfile2Excel) return (X) ;
058 |     if (x not_void) then
059 |      
060 |         Display " Table  Ok " ,Lfile2Excel Column 40;
061 |     ELSE
062 |          Display "Table   not found : ",Lfile2Excel; Exit;
063 |          Exit;
064 |     End_if ;
065 |  GetDirSysPathMS4BI(LpathDataTempExcel);
066 | ** important :  Ms4_SQL{Ado:"xls"}(" SELECT  * into " + " %s " : into %s ****** this temporary path not excedeed 128 caracters  !!
067 |     Concatene  LpathDataTempExcel,"\ztmp\l.XLS"     into Table1; ** tempory table = CONSOLIDATION orders+ employees
068 |     Concatene  LpathDataTempExcel,"\ztmp\l.lang1"          into Table1SheetJapanese;
069 |     Concatene  LpathDataTempExcel,"\ztmp\l.lang2"          into Table1Sheetchinese;
070 |      
071 |     Tableexist(Table1) return (X) ;
072 |    
073 |     /* 2 return codes */
074 |     ** x = 0 : Ok , x = 1 Not ok */
075 |     if (x not_void) then
076 |         Tabledelete (Table1) ;
077 |         Display " TableDelete Ok " ,Table1 Column 60;
078 |     ELSE
079 |         Display "Table Delete not found : ",Table1;
080 |     End_if ;
081 | END ;
082 | 
083 | BEGIN ** CONSOLIDATION **************tables : japanese and china  ->into  Excel table temp ********* 
084 |  SetUniCode(1);
085 | ** sheet 1 
086 |      DBconnect{Ado:"xls"}( LFile1Excel);
087 |      display "insert Table Japanese : CONSOLIDATION 1  ";
088 |       Ms4_SQL{Ado:"xls"}(" SELECT  * into " + " %s " + "  from [中国最大の都市のリスト$]", Table1SheetJapanese) return x ;
089 |       display " 中国最大の都市のリスト sql return :",x; 
090 |  
091 | ** sheet 2 
092 |      DBconnect{Ado:"xls"}(Lfile2Excel);
093 |     display "insert Table Chinese : CONSOLIDATION 2   ";
094 |     Ms4_SQL{Ado:"xls"}(" SELECT  * into  %s " + " from [Sheet1$]",Table1Sheetchinese) return x;
095 |      display " Chinese sql return :",x;  
096 |  
097 |     
098 | END ;
099 |   BEGIN ** Excute query  table temp TABLE1 for REPORT
100 |      
101 |      display "IMPORTANT Create   MS4 CUBE Lang UNICODE for   Input DATA :  CHART : PIE, BAR, DASHBOARD, REPORTING ..",LocalServerDB ;
102 |  ** *******************************************************************    
103 |  ** INSERT a FILE xls, txt ..another in DB MS4
104 |  ** Create Cube : ms4LangConsolidation Example
105 |  ** *******************************************************************    
106 |             DBconnect{Ado:"xls"}(Table1);
107 |               IMPORT : New Ms4Db   
108 |               BEGIN  
109 |                        DBconnect {Ms4DB} (LocalServerDB)  , 
110 |                       Create:Table("[ms4LangConsolidation]") 
111 |               END  
112 |                 (" SELECT   a.SNO , 都市名 as CityName, 英語 as CityEnglish, POPULATION ,"+
113 |                  " 備考 as proforma, CITY  , 城市 As CityChinese, "+
114 |                  " PROVINCE,mapcode, PREFECTURE  , TYPELevel , FOUNDED ,b.sno as snoduplicate" +
115 |                  " from  [lang1$] as a  ,[lang2$] as b   " +
116 |                         " where  a.英語 = b.city order by   b.city  "  );
117 |     ** **********************************                    
118 |     ** INSERT Longitude/Latitude    
119 |     ** **********************************
120 |          DBconnect{Ado:"xlsx"}(LFile3Excel);
121 |               IMPORT : New Ms4Db   
122 |               BEGIN  
123 |                        DBconnect {Ms4DB} (LocalServerDB)  , 
124 |                       Create:Table("[ms4ChinaLatitudeLongitude]") 
125 |               END  
126 |                 (" SELECT   * from [LatiLongi$] ");
127 |                 
128 |    DBBackup{Ms4db}(LocalServerDB) to( "#WWWMS4BISERVERBACKUP+\Ms4BI-DemoUnicodeCube.db") return (msgsql);
129 |   if(msgsql= 1)
130 |   then
131 |   display " BACKUP :  ok  #WWWMS4BISERVERBACKUP+  local WEB backup";
132 |   else
133 |   display " BACKUP :  ko #WWWMS4BISERVERBACKUP+  local WEB  backup"; 
134 |   end_if; 
135 |                   
136 |  
137 |  DBconnect {Ms4DB} (LocalServerDB); 
138 |   FormPlus : DrawGrid, title :"Result : Excel Consolidation -> MS4 DB : ms4LangConsolidation China/Japan : Relation -> 英語 = b.city  " 
139 |   use   View  ("[ms4LangConsolidation]")  ;
140 |  FormPlus : DrawGrid, title :"Result : Excel Consolidation -> MS4 DB : ms4ChinaLatitudeLongitude    " 
141 |   use   View  ("[ms4ChinaLatitudeLongitude]")  ;
142 | 
143 |      Display "    Create DB Lang Consolidation and Latitude/Longitude  : End                  ",LocalServerDB ;
144 |      SetColorText(Hwdisplay:green,black) ;
145 |      Display "   to continue,  Click here                  ";
146 |       enter;
147 |       
148 |  END;
149 |      
150 |