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

  	<div  $IsVisible_lg :  /* visible only large (screen) */  
	  DrawWindow : "Win0302" , actions : $WinCustom +  $WinMinimize + $WinMaximize + $WinClose  , position :  150,350,1750,1050  TITLE : "  Window Web: MS4 script Rapid Developt" 
		 							 url :  UrlMs4Script  /* key word ms4script */
    >;
	
Begin_Block_Rwd  :  full_page
 		<notification : $Bts_Notif_success_Dismissable ,  " ###b Step 2  :  Build MS4 DB - Consolidation of 2 Excel files   ###b "> ;
	 	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;