** ****************************************************************************************** *
** MONDAY SOFTWARE  
** *******************************************************************************************
#include  "#SYSPATHMANDRAGORE+\zInclude\IncludeDisplay.h"  
#include  "#SYSPATHMANDRAGORE+\zInclude\includeChart.h"
text : LocalServerDB :="#WWWMANDRAGORE+\MANDRAGORE-DemoUnicodeCube.db";  

integer :msgsql, TRUE :=1;
 
  	
<div  $IsVisible_lg :  /* visible only large (screen) */
	DrawWindow : "Win2" , actions : $WinCustom +  $WinMinimize + $WinMaximize + $WinClose  , position :150, 350,1050,960  TITLE : "  Window Web: MS4 script Rapid Developt" 
		 							 url :  UrlMs4Script  /* key word ms4script */
  >;
Begin_Block_Rwd  :  full_page
<notification : $Bts_Notif_warning_Dismissable ,  " ###b Step 3  :  Build DB View ###b ">;
 printsw "\n ###b+c Build MS4DB View (only Windows)   : 4 views :total/sum/avg : population/province/city \n /r READ MS4SCRIPT CODE /r ###b+c ";
 End_Block_Rwd ; 
    Display "------------------------------------------------------------------------- " ;
	Display "    Create DB Lang Views :  6 views                                                 " ;
	Display "     Total population/province/city + Match Longitude" ;
	Display "------------------------------------------------------------------------- " ;

BEGIN ** **********************INIT ** Drop View or create view IF NOT EXISTS*********
    DBconnect{Ms4DB}(LocalServerDB);
	     ViewExist{ms4db}("ms4TotalPopulByProvince") Return  ( msgsql) ; /* 2 return codes : TRUE or FALSE */
	 		if ( msgsql = TRUE) then Ms4_sql{ms4db} ( " Drop  View  [ms4TotalPopulByProvince] ");end_if ;
	  
	  
	  ViewExist{ms4db}("ms4TotalPopulByProvinceMapCode") Return  ( msgsql) ;
   		if ( msgsql = TRUE) then Ms4_sql{ms4db} ( " Drop  View  [ms4TotalPopulByProvinceMapCode] "); end_if ;

     ViewExist{ms4db}("ms4TotalcityByProvinceMapCode") Return  ( msgsql) ;
   		if ( msgsql = TRUE) then Ms4_sql{ms4db} ( " Drop  View  [ms4TotalcityByProvinceMapCode] "); end_if ;

		 ViewExist{ms4db}("ms4TotalcityByProvince") Return  ( msgsql) ;
   		if ( msgsql = TRUE) then Ms4_sql{ms4db} ( " Drop  View  [ms4TotalcityByProvince] "); end_if ;
		
	     ViewExist{ms4db}("ms4TotalcityProvincePopulation") Return  ( msgsql) ;
   		if ( msgsql = TRUE) then Ms4_sql{ms4db} ( " Drop  View  [ms4TotalcityProvincePopulation] "); end_if ;
			ViewExist{ms4db}("ms4AVGPopulation") Return  ( msgsql) ;
   		if ( msgsql = TRUE) then Ms4_sql{ms4db} ( " Drop  View  [ms4AVGPopulation] "); end_if ;
		ViewExist{ms4db}("ms4ProvinceMapCode") Return  ( msgsql) ;
   		if ( msgsql = TRUE) then Ms4_sql{ms4db} ( " Drop  View  [ms4ProvinceMapCode] "); end_if ;
 	ViewExist{ms4db}("ms4CityLatitudeLongitude") Return  ( msgsql) ;
   		if ( msgsql = TRUE) then Ms4_sql{ms4db} ( " Drop  View  [ms4CityLatitudeLongitude] "); end_if ;
 
	 display "    Create View  1:  [ms4TotalPopulByProvince]   " ;
			Ms4_SQL{Ms4DB}("CREATE VIEW  [ms4TotalPopulByProvince] AS  " +
               "SELECT  province,  sum(population/1000)  AS TOTALThousandpopulationbyProvince    " +
                  "   FROM  [ms4LangConsolidation]  group by province ");
	
	display "    Create View  2:  [ms4TotalPopulByProvinceMapCode]   " ;		  
				Ms4_SQL{Ms4DB}("CREATE VIEW  IF NOT EXISTS [ms4TotalPopulByProvinceMapCode] AS  " +
               "SELECT  province,  sum(population/1000)  AS TOTALThousandpopulationbyProvince , mapcode  " +
                  "   FROM  [ms4LangConsolidation]  group by province ");
  
	 display "    Create View  3:   [ms4TotalcityByProvince]  " ;
	 	  	Ms4_SQL{Ms4DB}("CREATE VIEW IF NOT EXISTS [ms4TotalcityByProvince] AS  " +
               "SELECT   province, count(city)  AS TOTALcitybyProvince " +
                  "   FROM  [ms4LangConsolidation]  group by province ");
				  
	display "    Create View  4:  [ms4TotalcityProvincePopulation]  ";			  
			   Ms4_SQL{Ms4DB}("CREATE VIEW  IF NOT EXISTS [ms4TotalcityProvincePopulation] AS  " +
               "SELECT    ('Totalavg') as TotalAVG ,count(DISTINCT city)  AS TOTALcity ,count(DISTINCT province) AS TOTALprovince,sum(population) AS TotalPopulation " +
		 	               "   FROM  [ms4LangConsolidation]    ");
						   
	display "    Create View  5:  [ms4AVGPopulation]  ";			  
			   Ms4_SQL{Ms4DB}("CREATE VIEW IF NOT EXISTS [ms4AVGPopulation] AS  " +
      		    " SELECT ('Totalavg') as TotalAVG ,AVG(population) as AVGPopulation, MAX(population) as MAXPopulation, MIN(population) AS MINPopulation" +
                  "   FROM  [ms4LangConsolidation]    ");
	
	display "    Create View  6:  [ms4ProvinceMapCode]  ";						  
	 	Ms4_SQL{Ms4DB}("CREATE VIEW IF NOT EXISTS [ms4ProvinceMapCode] AS  " +
               "SELECT   province, mapcode " +
                  "   FROM  [ms4LangConsolidation]  group by province ");
  
    display "    Create View  7:  [ms4CityLatitudeLongitude]  ";						  
	 	Ms4_SQL{Ms4DB}("CREATE VIEW IF NOT EXISTS [ms4CityLatitudeLongitude] AS  " +
               "SELECT    city,    latitude, longitude  " +
                  "   FROM  [ms4LangConsolidation]  A " +        ** instr :  substring...
				  "   join  [ms4ChinaLatitudeLongitude] B  where instr(B.PlaceNameCHINA, A.city)  group by A.city  ");
  
    DBBackup{Ms4db}(LocalServerDB) to( "#WWWMANDRAGOREBACKUP+\MANDRAGORE-DemoUnicodeCube.db") return (msgsql);
  if(msgsql= 1)
  then
  display " BACKUP :  ok  #WWWMANDRAGOREBACKUP+  local WEB backup";
  else
  display " BACKUP :  ko #WWWMANDRAGOREBACKUP+  local WEB  backup"; 
  end_if; 

				   
  DBconnect {Ms4DB} (LocalServerDB) ;				  
    position  : 400,10,auto,650 :		  
  FormPlus : DrawGrid, title :"MS4 DB : Create View 1 :  Total population/province : Drag & Drop Column "    use   View  ("[ms4TotalPopulByProvince]")  ;
 
  position  :  1350,10,auto,500 :	
	FormPlus : DrawGrid, title :"MS4 DB : Create View  2:  nb city/province : Drag & Drop Column "    use   View  ("[ms4TotalcitybyProvince]")  ;

	position  : 950,700,250,650 :	
 FormPlus : DrawGrid, title :"MS4 DB : Create View  3 :  nb city ,province,population  "    use   View  ("[ms4TotalcityProvincePopulation]")  ;
		position  : 1450,700,250,650 :	
 FormPlus : DrawGrid, title :"MS4 DB : Create View  4 :  average  population  "    use   View  ("[ms4AVGPopulation]")  ;

		position  : 1850,700,auto,350 :	
 FormPlus : DrawGrid, title :"MS4 DB : Create View  5 :  province-mapcode   "    use   View  ("[ms4ProvinceMapCode]")  ;
		position  : 1850,1100,auto,350 :	
 FormPlus : DrawGrid, title :"MS4 DB : Create View  6 :  city  Latitude/Longitude   "    use   View  ("[ms4CityLatitudeLongitude]")  ;

 SetColorText(Hwdisplay:green,black) ;
	  Display "   to continue,  Click here                  ";
      enter ;
	 
	 
 
 END;