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 MS4
DB
(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 : ",Table
1;
080 | End_if
;
081 | END
;
082 |
083 | BEGIN
** CONSOLIDATION **************table
s : 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 |