** ****************************************************************************************** *
** (ms4 script) - MANDRAGORE WORKSPACE for WINDOWS & WEB-
** *******************************************************************************************
 
#include  "#SYSPATHMANDRAGORE+\zInclude\IncludeDisplay.h"
#include  "#SYSPATHMANDRAGORE+\zInclude\includeChart.h"
 
	

CHAR : Table1:255,Table1SheetOrders:60,Table1Sheetemployees:60 ;
INTEGER : Msgsql,X ,no:=0,yes:=1;
Char : LpathDataBaseExcel:255,LCurrentDir:255, LFile1Excel :255, LFile2Excel :255 ;


	Display "------------------------------------------------------------------------- " ;
	Display "     Example 1 .... REPORT SINGLE :  EXCEL   2 TABLES :Employees , Orders " ;
	Display "------------------------------------------------------------------------- " ;
BEGIN ** **********************INIT ***********
	GetDirSysPathMANDRAGORE(LpathDataBaseExcel);
**	display " LpathDataBaseExcel : "LpathDataBaseExcel; enter;
	Concatene  LpathDataBaseExcel,"\zConnectors-ADO\Excel\DataBase\tableOrders.xls"   into  Lfile1Excel;
	Concatene  LpathDataBaseExcel,"\zConnectors-ADO\Excel\DataBase\tableEmployees.xls" into  Lfile2Excel;
	Tableexist(Lfile1Excel) return (X) ;
	if (x not_void) then
	 
		Display " Table  Ok " ,Lfile1Excel Column 60;
	ELSE
		Display "Table   Not found : ",Lfile1Excel;  Exit;
	End_if ;
 
	Tableexist(Lfile2Excel) return (X) ;
	if (x not_void) then
	 
		Display " Table  Ok " ,Lfile2Excel Column 60;
	ELSE
		Display "Table   Not found : ",Lfile2Excel; enter; Exit;
	End_if ;
 
 
	Concatene  LpathDataBaseExcel,"\ztmp\MergeXLS1.XLS" into Table1; ** tempory table = CONSOLIDATION orders+ employees
	Concatene  LpathDataBaseExcel,"\ztmp\MergeXLS1.Orders"    into Table1SheetOrders;
	Concatene  LpathDataBaseExcel,"\ztmp\MergeXLS1.Employe" into Table1Sheetemployees; 

 
	Tableexist(Table1) return (X) ;

	/* 2 return codes */
	** x = 1 : Ok , x = 0 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 **EXCEL************table order + employee into table temp ********* 
  
** sheet 1 
 	DBconnect{Ado:"xls"}( LFile1Excel);
 	display "insert Table ORders : Consolidation  ";
  	Ms4_SQL{Ado:"xls"}(" SELECT  * into " + " %s " + "  from [tableOrders$]",Table1SheetOrders) return x ;
 	 display " ORDERS sql return :",x;
Sql_DestroyEnv();
if (x void) then
	 
		Display "1 Select Ok " ,Lfile1Excel Column 60;
	ELSE
		Display "1 Sql Problem : ",Lfile1Excel; 
		enter;  Exit;
End_if ;
** sheet 2   
	 
	DBconnect{Ado:"xls"}(Lfile2Excel);
	display "insert Table Employees : Consolidation  ";
	Ms4_SQL{Ado:"xls"}(" SELECT  * into  %s " + " from [tableEmployees$]",Table1SheetEmployees) return x;
 	display " Employees sql return :",x;
  Sql_DestroyEnv();
  if (x void) then
	 
		Display "2 Select Ok " ,Lfile2Excel Column 30;
	ELSE
		Display "2 Sql Problem !!!!!!: x ",x,Lfile2Excel; 
	enter; Exit;	 
End_if ;

END;
  BEGIN ** Excute query  table temp TABLE1 for REPORT
	SetColorText (Hwdisplay:yellow,Blue ) ;
	display "CreateVIEW for Report Input DATA...............IMPORTANT";
 
			DBconnect{Ado:"xls"}(Table1);
			 IMPORT : NEW  MS4DB 
		          BEGIN  
        		       DBconnect { Ms4DB} ($MS4DBTMP)  ,  
		    		Create:View("[ReportEmployAndOrders]") 
			  END (" SELECT  *  from  [Employe$],[Orders$]   where Employe$.EmployeeID = Orders$.EmployeeID "
				+ "order by Employe$.EmployeeID,Orders$.EmployeeID ");
   
	Display "Wait...";  
     
End;
 display "REPORT ";
** *****************************************
** DASHBORD : REPORT 1 and 2
** *****************************************
	DECLARE SCHEMA_REPORT :    Employees
	{ TEXT :  EmployeeID ,LastName ; } ;
 	DECLARE SCHEMA_REPORT :    Orders
	{ TEXT : OrderID ,EmployeeID, CustomerID , ShipName, ShipAddress,	
            ShipCity,ShipRegion,ShipPostalCode,ShipCountry ; 
     };
 <MS4> 
  
BEGIN_DASHBOARD 
 option :
  BEGIN
		type  : $NOTABSTRIP , 
		style : $METRO ,  
		title : "  DASHBOARD :  ANALYSIS EXCEL CONSOLIDATION xxxxxxxxxxx"  
   END
  Setting : ** by chart
	Chart :
	Backgroundcolor : $color_bkMANDRAGORE ;
	
	 Begin_Sw                /* Script Web  */
   <style>   
   	.k-grid { border-bottom: none; font-size: 9px;}  
   	.k-header { background-color:#9de219;color:blue;font-family:tahoma;font-size:18px; } 
   	.k-header.k-link { color:black; } 
   	.k-state-selected { background-color:#A7CDF0;background-image:none;color:red;} 
   	 .k-alt.k-state-selected  { background-color:green;background-image:none;color:black;} 
    	 tr { font-family:arial;font-size:11px;color:black;border-bottom:solid 14px #DBDEE1;height:21px;} 
    	.k-alt { background-color:#dff7ae; } 
    	.k-grid td { padding:0px 10px;margin:0px;border-bottom:solid 2px green;  } 
    	.k-grid-header { padding:0px 0px;margin:0px; } 
  </style>  
End_Sw  ;
     Begin_Report   ** REPORT 1
  
           REPORTINPUT :   DBconnect {Ms4DB}   ($MS4DBTMP) use View  ("[ReportEmployAndOrders]")   
           REPORTFORM:GROUP , TITLE :  " EXCEL Informations and Consolidation : REPORT ORDER ID BY EMPLOYEE" 
           GROUP ORDER  BY    :         Employees.EmployeeID   ,    Orders.OrderID  
           HEADER GROUP       : 
             Option  : 
               BEGIN 
                 style       : $METRO ,
                 height      : 480,
                 ExpandFirst : yes,  
                 pagesize:10 
               END  OPTION  
           Employees.EmployeeID  , TITLE "Employee"   ,
           Employees.LastName    , TITLE " LastName"  
				    
           DETAIL GROUP  : 
           Option  :  BEGIN   pagesize:6    END OPTION      
                    Orders.OrderID   ,        TITLE "n0 ORDER"     ,
                    Orders.ShipName ,         TITLE " ship Name "  ,
                    Orders.ShipAddress  ,     TITLE "Adress"    ,
                    Orders.ShipCity  ,        TITLE "city"     ,
                    Orders.ShipRegion  ,      TITLE "Region"     ,
                    Orders.ShipPostalCode  ,  TITLE "Postal code "  , 
                    Orders.Shipcountry  ,     TITLE "Ship country"   
   END_REPORT ; ** REPORT 1
** *****************************************
** REPORT 2   AGGGREGATE
** ***************************************** 
     Begin_Report  ** REPORT 2
           REPORTINPUT :   DBconnect{Ms4DB}   ($MS4DBTMP) use View  ("[ReportEmployAndOrders]")   
            REPORTFORM:AGGREGATE , TITLE :" EXCEL Informations and Consolidation : REPORT   EMPLOYEE orders BY SHIP COUNTRY " 
              AGGREGATE  BY       :    Employees.EmployeeID   ,   Orders.Shipcountry 
        	 			    
              DETAIL AGGREGATE    : Option  :  BEGIN pagesize:500  END OPTION      
                    Orders.OrderID   ,        TITLE "n0 ORDER"  , 
                    Orders.ShipName ,         TITLE " ship Name "  ,
                    Orders.ShipAddress  ,     TITLE "Adress"   ,
                    Orders.ShipCity  ,        TITLE "city"   ,
                    Orders.ShipRegion  ,      TITLE "Region"   ,
                    Orders.ShipPostalCode  ,  TITLE "Postal code "   ,
                    Orders.Shipcountry  ,     TITLE "Ship country"   
    END_REPORT ;
END_DASHBOARD ;  
</MS4> ;