<?php
$debug='0';
$dashboard_report='Order Management Analytics';
$dashboard_id='ordermanagementanalytics';
$button_text='Go';
$database_config='dbconfig/ordermanagementanalytics_db.php';
$d_options['use_plet_positions']= true; //this will place each parameter at its own x,y location, setting to false will use the grouping feature
$d_options['grid_height_offset']=-80;//this is the offset added to the actual height for accomodating the caption,pager and column header height
$d_options['top_button_offset']=0;//Push all the objects down by this pixel height.
$d_options['top_button_display']=true;//Display the Go button?



  $num_format = 'number_format';
  
  $percent = 'percentof';
function percentof($value,$percent)
{
  return ($value*$percent)/100;
}



  $get_position = 'getPosition';
/*
* Total height /total width of the canvas
offset = deduct this from the actual height or widht
per = percentage of the remaining height or width and based on this return the relative position
*/
function getPosition($total_height,$offset,$per)
{
  return $total_height - (($total_height-$offset)*$per)/100;
}






/************** fromorderdate_p36 *********************/
$plet['fromorderdate_p36']['type']='date' ;
$plet['fromorderdate_p36']['data_type']='DATA_TYPE' ;
$plet['fromorderdate_p36']['id']='fromorderdate_p36' ;
$plet['fromorderdate_p36']['name']='From Order Date' ;
$plet['fromorderdate_p36']['raw_type']='DATE' ;
$plet['fromorderdate_p36']['connection']='order_raw.xls' ;
$plet['fromorderdate_p36']['width']=150 ;
$plet['fromorderdate_p36']['height']=50 - 27 ;
$plet['fromorderdate_p36']['x_position']=108 ;
$plet['fromorderdate_p36']['y_position']=2 ;
$plet['fromorderdate_p36']['format']='dd-M-yy' ;
$plet['fromorderdate_p36']['font']='font-family:Arial;font-name:Arial;font-style:plain;font-size:12px' ;
$plet['fromorderdate_p36']['font_color']='#000000' ;
$plet['fromorderdate_p36']['background_color']='#f8faac' ;
$plet['fromorderdate_p36']['default_value']=date('m/1/Y') ;//NOTE: you can provide any default value as string e.g 1/1/2010
$plet['fromorderdate_p36']['enable_all']='INCLUDE_ALL_AS_CHAR' ;
$plet['fromorderdate_p36']['all_label']='ALL' ;
$plet['fromorderdate_p36']['all_value']='%' ;
$plet['fromorderdate_p36']['display_members']='1';  //value decides whether it is a multilist combo or single selection ;
$plet['fromorderdate_p36']['control_type']='LIST';  //In future, other values could be radio,slider,checkbox etc ;
$plet['fromorderdate_p36']['default_null_value']='%';  // ;
$plet['fromorderdate_p36']['static_values']='' ;
$plet['fromorderdate_p36']['static_display_values']= $plet['fromorderdate_p36']['static_values']  ;
$plet['fromorderdate_p36']['sql']= <<<EOD
 select 
 distinct "Category Name" 
 from ( select * from ( 
select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity" ,
"Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date",
year("Order Date")  as "Or_Year",
format("Order Date",'m')  as "Or_Mth_N",
format("Order Date",'mm')  as "Or_Mth_Disp",
format("Order Date",'mm-mmm')  as "Or_Mth_NN",
format("Order Date",'yyyy-mm')  as "Or_Yr_Mth",
format("Order Date",'yyyy-mm(mmm)')  as "Or_Yr_Mth_NN",
format("Order Date",'q')  as "Or_Qtr",
'Qtr - '&format("Order Date",'yy-q')  as "Or_Qtr_Disp",
format("Order Date",'ww')  as "Or_Week",
format("Order Date",'yyyy-ww')  as "Or_Yr_wk",
datepart('y',"Order Date")  as "Or_Yr_Dy",
format("Order Date",'dd')  as "Or_Day",
format("Order Date",'yyyy-q')  as "Or_Yr_Qtr",
month("Order Date") as "Or_Month" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity",
"Unit Price" * "Quantity" as "Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (Select 
       ord."Orderid" ,
       ord."Productid" ,
       ord."Supplierid" ,
       ord."Categoryid" ,
       ord."Unit Price" ,
       ord."Quantity" ,
       ord."Discount" ,
       ord."Employeeid" ,
       ord."Emp Lastname" ,
       ord."Emp Firstname" ,
       ord."Emp Title" ,
       ord."Order Date" ,
       ord."Required Date" ,
       ord."Shipped Date" ,
       ord."Ship Name" ,
       ord."Ship Address" ,
       ord."Ship Region" ,
       ord."Shippostalcode" ,
       ord."Shipcountry" ,
       ord."Supplier Name" ,
       ord."Product Name" ,
       ord."Quantity Per Unit" ,
       ord."List Price" ,
       ord."Category Name" ,
       ord."Description" ,
       ord."City" ,
       ord."Region" ,
       ord."Country" ,
       ord."Customerid" ,
       ord."Customer Name" ,
       ord."Cust Contactname" ,
       ord."City1" ,
       ord."Region1" ,
       ord."Country1"
From 
      [order_data$]    ord
 ) as  calx  ) as  dateColsx  ) as  x  )  as  x  
EOD;




/************** toorderdate_p38 *********************/
$plet['toorderdate_p38']['type']='date' ;
$plet['toorderdate_p38']['data_type']='DATA_TYPE' ;
$plet['toorderdate_p38']['id']='toorderdate_p38' ;
$plet['toorderdate_p38']['name']='To Order Date' ;
$plet['toorderdate_p38']['raw_type']='DATE' ;
$plet['toorderdate_p38']['connection']='order_raw.xls' ;
$plet['toorderdate_p38']['width']=149 ;
$plet['toorderdate_p38']['height']=50 - 27 ;
$plet['toorderdate_p38']['x_position']=261 ;
$plet['toorderdate_p38']['y_position']=1 ;
$plet['toorderdate_p38']['format']='dd-M-yy' ;
$plet['toorderdate_p38']['font']='font-family:Arial;font-name:Arial;font-style:plain;font-size:12px' ;
$plet['toorderdate_p38']['font_color']='#000000' ;
$plet['toorderdate_p38']['background_color']='#f8faac' ;
$plet['toorderdate_p38']['default_value']=date('m/t/Y') ;//NOTE: you can provide any default value as string e.g 12/31/2010
$plet['toorderdate_p38']['enable_all']='INCLUDE_ALL_AS_CHAR' ;
$plet['toorderdate_p38']['all_label']='ALL' ;
$plet['toorderdate_p38']['all_value']='%' ;
$plet['toorderdate_p38']['display_members']='1';  //value decides whether it is a multilist combo or single selection ;
$plet['toorderdate_p38']['control_type']='LIST';  //In future, other values could be radio,slider,checkbox etc ;
$plet['toorderdate_p38']['default_null_value']='%';  // ;
$plet['toorderdate_p38']['static_values']='' ;
$plet['toorderdate_p38']['static_display_values']= $plet['toorderdate_p38']['static_values']  ;
$plet['toorderdate_p38']['sql']= <<<EOD
 select 
 distinct "Category Name" 
 from ( select * from ( 
select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity" ,
"Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date",
year("Order Date")  as "Or_Year",
format("Order Date",'m')  as "Or_Mth_N",
format("Order Date",'mm')  as "Or_Mth_Disp",
format("Order Date",'mm-mmm')  as "Or_Mth_NN",
format("Order Date",'yyyy-mm')  as "Or_Yr_Mth",
format("Order Date",'yyyy-mm(mmm)')  as "Or_Yr_Mth_NN",
format("Order Date",'q')  as "Or_Qtr",
'Qtr - '&format("Order Date",'yy-q')  as "Or_Qtr_Disp",
format("Order Date",'ww')  as "Or_Week",
format("Order Date",'yyyy-ww')  as "Or_Yr_wk",
datepart('y',"Order Date")  as "Or_Yr_Dy",
format("Order Date",'dd')  as "Or_Day",
format("Order Date",'yyyy-q')  as "Or_Yr_Qtr",
month("Order Date") as "Or_Month" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity",
"Unit Price" * "Quantity" as "Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (Select 
       ord."Orderid" ,
       ord."Productid" ,
       ord."Supplierid" ,
       ord."Categoryid" ,
       ord."Unit Price" ,
       ord."Quantity" ,
       ord."Discount" ,
       ord."Employeeid" ,
       ord."Emp Lastname" ,
       ord."Emp Firstname" ,
       ord."Emp Title" ,
       ord."Order Date" ,
       ord."Required Date" ,
       ord."Shipped Date" ,
       ord."Ship Name" ,
       ord."Ship Address" ,
       ord."Ship Region" ,
       ord."Shippostalcode" ,
       ord."Shipcountry" ,
       ord."Supplier Name" ,
       ord."Product Name" ,
       ord."Quantity Per Unit" ,
       ord."List Price" ,
       ord."Category Name" ,
       ord."Description" ,
       ord."City" ,
       ord."Region" ,
       ord."Country" ,
       ord."Customerid" ,
       ord."Customer Name" ,
       ord."Cust Contactname" ,
       ord."City1" ,
       ord."Region1" ,
       ord."Country1"
From 
      [order_data$]    ord
 ) as  calx  ) as  dateColsx  ) as  x  )  as  x  
EOD;




/************** categoryname_p34 *********************/
$plet['categoryname_p34']['type']='dynamic_lov' ;
$plet['categoryname_p34']['data_type']='CHAR' ;
$plet['categoryname_p34']['id']='categoryname_p34' ;
$plet['categoryname_p34']['name']='Category Name' ;
$plet['categoryname_p34']['raw_type']='PLET' ;
$plet['categoryname_p34']['connection']='order_raw.xls' ;
$plet['categoryname_p34']['width']=199 ;
$plet['categoryname_p34']['height']=50 - 27 ;
$plet['categoryname_p34']['x_position']=414 ;
$plet['categoryname_p34']['y_position']=2 ;
$plet['categoryname_p34']['format']='dd-M-yy' ;
$plet['categoryname_p34']['font']='font-family:Arial;font-name:Arial;font-style:plain;font-size:12px' ;
$plet['categoryname_p34']['font_color']='#000000' ;
$plet['categoryname_p34']['background_color']='#f8faac' ;
$plet['categoryname_p34']['default_value']='' ;
$plet['categoryname_p34']['enable_all']='N' ;
$plet['categoryname_p34']['all_label']='ALL' ;
$plet['categoryname_p34']['all_value']='%' ;
$plet['categoryname_p34']['display_members']='1';  //value decides whether it is a multilist combo or single selection ;
$plet['categoryname_p34']['control_type']='LIST';  //In future, other values could be radio,slider,checkbox etc ;
$plet['categoryname_p34']['default_null_value']='%';  // ;
$plet['categoryname_p34']['static_values']='' ;
$plet['categoryname_p34']['static_display_values']= $plet['categoryname_p34']['static_values']  ;
$plet['categoryname_p34']['sql']= <<<EOD
 select 
 distinct "Category Name" 
 from ( select * from ( 
select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity" ,
"Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date",
year("Order Date")  as "Or_Year",
format("Order Date",'m')  as "Or_Mth_N",
format("Order Date",'mm')  as "Or_Mth_Disp",
format("Order Date",'mm-mmm')  as "Or_Mth_NN",
format("Order Date",'yyyy-mm')  as "Or_Yr_Mth",
format("Order Date",'yyyy-mm(mmm)')  as "Or_Yr_Mth_NN",
format("Order Date",'q')  as "Or_Qtr",
'Qtr - '&format("Order Date",'yy-q')  as "Or_Qtr_Disp",
format("Order Date",'ww')  as "Or_Week",
format("Order Date",'yyyy-ww')  as "Or_Yr_wk",
datepart('y',"Order Date")  as "Or_Yr_Dy",
format("Order Date",'dd')  as "Or_Day",
format("Order Date",'yyyy-q')  as "Or_Yr_Qtr",
month("Order Date") as "Or_Month" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity",
"Unit Price" * "Quantity" as "Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (Select 
       ord."Orderid" ,
       ord."Productid" ,
       ord."Supplierid" ,
       ord."Categoryid" ,
       ord."Unit Price" ,
       ord."Quantity" ,
       ord."Discount" ,
       ord."Employeeid" ,
       ord."Emp Lastname" ,
       ord."Emp Firstname" ,
       ord."Emp Title" ,
       ord."Order Date" ,
       ord."Required Date" ,
       ord."Shipped Date" ,
       ord."Ship Name" ,
       ord."Ship Address" ,
       ord."Ship Region" ,
       ord."Shippostalcode" ,
       ord."Shipcountry" ,
       ord."Supplier Name" ,
       ord."Product Name" ,
       ord."Quantity Per Unit" ,
       ord."List Price" ,
       ord."Category Name" ,
       ord."Description" ,
       ord."City" ,
       ord."Region" ,
       ord."Country" ,
       ord."Customerid" ,
       ord."Customer Name" ,
       ord."Cust Contactname" ,
       ord."City1" ,
       ord."Region1" ,
       ord."Country1"
From 
      [order_data$]    ord
 ) as  calx  ) as  dateColsx  ) as  x  )  as  x  
EOD;




/************** year_p32 *********************/
$plet['year_p32']['type']='dynamic_lov' ;
$plet['year_p32']['data_type']='CHAR' ;
$plet['year_p32']['id']='year_p32' ;
$plet['year_p32']['name']='Year' ;
$plet['year_p32']['raw_type']='PLET' ;
$plet['year_p32']['connection']='order_raw.xls' ;
$plet['year_p32']['width']=100 ;
$plet['year_p32']['height']=50 - 27 ;
$plet['year_p32']['x_position']=0 ;
$plet['year_p32']['y_position']=0 ;
$plet['year_p32']['format']='dd-M-yy' ;
$plet['year_p32']['font']='font-family:Arial;font-name:Arial;font-style:plain;font-size:12px' ;
$plet['year_p32']['font_color']='#000000' ;
$plet['year_p32']['background_color']='#f8faac' ;
$plet['year_p32']['default_value']='' ;
$plet['year_p32']['enable_all']='INCLUDE_ALL_AS_NUM' ;
$plet['year_p32']['all_label']='ALL' ;
$plet['year_p32']['all_value']='%' ;
$plet['year_p32']['display_members']='1';  //value decides whether it is a multilist combo or single selection ;
$plet['year_p32']['control_type']='LIST';  //In future, other values could be radio,slider,checkbox etc ;
$plet['year_p32']['default_null_value']='%';  // ;
$plet['year_p32']['static_values']='' ;
$plet['year_p32']['static_display_values']= $plet['year_p32']['static_values']  ;
$plet['year_p32']['sql']= <<<EOD
 select 
 distinct Or_Year 
 from ( select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity" ,
"Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date",
year("Order Date")  as "Or_Year",
format("Order Date",'m')  as "Or_Mth_N",
format("Order Date",'mm')  as "Or_Mth_Disp",
format("Order Date",'mm-mmm')  as "Or_Mth_NN",
format("Order Date",'yyyy-mm')  as "Or_Yr_Mth",
format("Order Date",'yyyy-mm(mmm)')  as "Or_Yr_Mth_NN",
format("Order Date",'q')  as "Or_Qtr",
'Qtr - '&format("Order Date",'yy-q')  as "Or_Qtr_Disp",
format("Order Date",'ww')  as "Or_Week",
format("Order Date",'yyyy-ww')  as "Or_Yr_wk",
datepart('y',"Order Date")  as "Or_Yr_Dy",
format("Order Date",'dd')  as "Or_Day",
format("Order Date",'yyyy-q')  as "Or_Yr_Qtr",
month("Order Date") as "Or_Month" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity",
"Unit Price" * "Quantity" as "Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (Select 
       ord."Orderid" ,
       ord."Productid" ,
       ord."Supplierid" ,
       ord."Categoryid" ,
       ord."Unit Price" ,
       ord."Quantity" ,
       ord."Discount" ,
       ord."Employeeid" ,
       ord."Emp Lastname" ,
       ord."Emp Firstname" ,
       ord."Emp Title" ,
       ord."Order Date" ,
       ord."Required Date" ,
       ord."Shipped Date" ,
       ord."Ship Name" ,
       ord."Ship Address" ,
       ord."Ship Region" ,
       ord."Shippostalcode" ,
       ord."Shipcountry" ,
       ord."Supplier Name" ,
       ord."Product Name" ,
       ord."Quantity Per Unit" ,
       ord."List Price" ,
       ord."Category Name" ,
       ord."Description" ,
       ord."City" ,
       ord."Region" ,
       ord."Country" ,
       ord."Customerid" ,
       ord."Customer Name" ,
       ord."Cust Contactname" ,
       ord."City1" ,
       ord."Region1" ,
       ord."Country1"
From 
      [order_data$]    ord
 ) as  calx  ) as  dateColsx  )  as  x  
EOD;


$d_options['top_button_x_location']=633;//X position where the Go button will appear
$d_options['top_button_y_location']=$d_options['top_button_offset'] +15;//Y Position of the Go button
$d_options['top_button_width']=40;//Width of the Go button
$d_options['top_button_height']=20;//Height of the Go Button
$d_options['use_go_checkobx_x_location']=633+$d_options['top_button_width']+5 ;//X- position of the Checkbox. The checkbox is used to disable AutoRefresh when you change the drop down value
$plet['list'] = array ('fromorderdate_p36' , 'toorderdate_p38' , 'categoryname_p34' , 'year_p32');


$plet_group['list'] =array('g1'); 
$plet_group['g1']['label']='Select Values'; 
$plet_group['g1']['members'] = array ('fromorderdate_p36' , 'toorderdate_p38' , 'categoryname_p34' , 'year_p32');


$plet_group['g1']['width']=700; 


/******************************Parameter End********************************/
$qlet['relative_position']=true;// 



/**************  *********************/


/************** productrevenuedistribution_g51 *********************/
$qlet['productrevenuedistribution_g51']['id']='productrevenuedistribution_g51' ;
$qlet['productrevenuedistribution_g51']['name']='Product Revenue Distribution' ;
$qlet['productrevenuedistribution_g51']['type']=  'chart_3d pie' ;
$qlet['productrevenuedistribution_g51']['connection']='order_raw.xls' ;
$qlet['productrevenuedistribution_g51']['original_type']='chart' ;
$qlet['productrevenuedistribution_g51']['width']=614 ;
$qlet['productrevenuedistribution_g51']['height']=449 ;
$qlet['productrevenuedistribution_g51']['x_position']=2 ;
$qlet['productrevenuedistribution_g51']['y_position']=377 ;
$qlet['productrevenuedistribution_g51']['max_rows']=100 ;
$qlet['productrevenuedistribution_g51']['number_of_columns']=2 ;
$qlet['productrevenuedistribution_g51']['column_names']= array('Product Name','SUM_Amount' ) ;
$qlet['productrevenuedistribution_g51']['column_display_names']= array('Product Name','SUM_Amount' ) ;
$qlet['productrevenuedistribution_g51']['column_alignment']= array('right','right' ) ;
$qlet['productrevenuedistribution_g51']['column_width']= array(307,307 ) ;
$qlet['productrevenuedistribution_g51']['display_tooltip']= true  ;
$qlet['productrevenuedistribution_g51']['tooltip_template']= <<<EOD
#percent#%, #label# = #value# 
EOD;
$qlet['productrevenuedistribution_g51']['background_color']= 'FFFFFF' ;
$qlet['productrevenuedistribution_g51']['total_script']= <<<EOD
  
EOD;
$qlet['productrevenuedistribution_g51']['sql']= <<<EOD
 Select   "Product Name", SUM( "Amount" ) as "SUM_Amount" 
from ( 
select * from ( 
select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity" ,
"Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date",
year("Order Date")  as "Or_Year",
format("Order Date",'m')  as "Or_Mth_N",
format("Order Date",'mm')  as "Or_Mth_Disp",
format("Order Date",'mm-mmm')  as "Or_Mth_NN",
format("Order Date",'yyyy-mm')  as "Or_Yr_Mth",
format("Order Date",'yyyy-mm(mmm)')  as "Or_Yr_Mth_NN",
format("Order Date",'q')  as "Or_Qtr",
'Qtr - '&format("Order Date",'yy-q')  as "Or_Qtr_Disp",
format("Order Date",'ww')  as "Or_Week",
format("Order Date",'yyyy-ww')  as "Or_Yr_wk",
datepart('y',"Order Date")  as "Or_Yr_Dy",
format("Order Date",'dd')  as "Or_Day",
format("Order Date",'yyyy-q')  as "Or_Yr_Qtr",
month("Order Date") as "Or_Month" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity",
"Unit Price" * "Quantity" as "Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (Select 
       ord."Orderid" ,
       ord."Productid" ,
       ord."Supplierid" ,
       ord."Categoryid" ,
       ord."Unit Price" ,
       ord."Quantity" ,
       ord."Discount" ,
       ord."Employeeid" ,
       ord."Emp Lastname" ,
       ord."Emp Firstname" ,
       ord."Emp Title" ,
       ord."Order Date" ,
       ord."Required Date" ,
       ord."Shipped Date" ,
       ord."Ship Name" ,
       ord."Ship Address" ,
       ord."Ship Region" ,
       ord."Shippostalcode" ,
       ord."Shipcountry" ,
       ord."Supplier Name" ,
       ord."Product Name" ,
       ord."Quantity Per Unit" ,
       ord."List Price" ,
       ord."Category Name" ,
       ord."Description" ,
       ord."City" ,
       ord."Region" ,
       ord."Country" ,
       ord."Customerid" ,
       ord."Customer Name" ,
       ord."Cust Contactname" ,
       ord."City1" ,
       ord."Region1" ,
       ord."Country1"
From 
      [order_data$]    ord
 ) as  calx  ) as  dateColsx  ) as  x 
 where "Or_Year" in (PVAL['year_p32'])  
 and  "Category Name" in ('PVAL['categoryname_p34']')  
 and  "Order Date" <=   now()
 and  "Order Date" between format('PVAL['fromorderdate_p36']','yyyy/mm/dd')  and format('PVAL['toorderdate_p38']','yyyy/mm/dd')

 ) as g_tabx
 group by "Product Name" 
EOD;
$qlet['productrevenuedistribution_g51']['chart_margin']= array(left =>'35',right=>'15',top=>'25',bottom=>'45');
$qlet['productrevenuedistribution_g51']['chart_size']=
                           array(
                                 width=>$qlet['productrevenuedistribution_g51']['width'] - $qlet['productrevenuedistribution_g51']['chart_margin']['left']- $qlet['productrevenuedistribution_g51']['chart_margin']['right']
                                ,height =>$qlet['productrevenuedistribution_g51']['height'] - $qlet['productrevenuedistribution_g51']['chart_margin']['top']- $qlet['productrevenuedistribution_g51']['chart_margin']['bottom']-20
                                 );
                                 
$qlet['productrevenuedistribution_g51']['chart_position']=
                           array(
                                 top_x=> $qlet['productrevenuedistribution_g51']['chart_margin']['left']
                                ,top_y => $qlet['productrevenuedistribution_g51']['chart_margin']['top']
                                 );
                                 
$qlet['productrevenuedistribution_g51']['legend']=
                           array(
                                 width=> $qlet['productrevenuedistribution_g51']['width'] - $qlet['productrevenuedistribution_g51']['chart_margin']['left']- $qlet['productrevenuedistribution_g51']['chart_margin']['right']
                                ,height => 20
                                ,top_x=> $qlet['productrevenuedistribution_g51']['chart_margin']['left']
                                ,top_y => $qlet['productrevenuedistribution_g51']['height'] - 20
                                ,label_max_length =>10
                                 );
                                 
$qlet['productrevenuedistribution_g51']['template']= <<<EOD

	<chart_label  size='10'  as_percentage='true'/>  
	
<chart_rect x='0' y='20' width='{$percent($qlet['productrevenuedistribution_g51']['width'],90)}' height='{$percent($qlet['productrevenuedistribution_g51']['height']-20,55)}' positive_color='000000' positive_alpha='0' negative_color='ff0000' negative_alpha='0' />		

 <legend layout='horizontal' 
           fill_color='F0EBE6'
           bullet='square'
           font='arial'
           bold='true'
           size='10'
		  width='{$percent($qlet['productrevenuedistribution_g51']['width'],80)}'
		  height='{$percent($qlet['productrevenuedistribution_g51']['height']-20,35)}'
		   y='{$get_position($qlet['productrevenuedistribution_g51']['height'],20,35)}'
           alpha='90'
           />			
		   
  <draw>
   <text   size='15' x='0' y='0' width='{$qlet['productrevenuedistribution_g51']['width']}' height='20' h_align='center' v_align='top'>{$qlet['productrevenuedistribution_g51']['name']}</text>
  </draw>
  
<chart_pref
        select='true'            
            />   
  
	<chart_type>#chart_type#</chart_type>

    	#chart_data#


EOD;




/**************  *********************/


/************** bysupplier_g57 *********************/
$qlet['bysupplier_g57']['id']='bysupplier_g57' ;
$qlet['bysupplier_g57']['name']='By Supplier' ;
$qlet['bysupplier_g57']['type']=  'chart_3d pie' ;
$qlet['bysupplier_g57']['connection']='order_raw.xls' ;
$qlet['bysupplier_g57']['original_type']='chart' ;
$qlet['bysupplier_g57']['width']=297 ;
$qlet['bysupplier_g57']['height']=205 ;
$qlet['bysupplier_g57']['x_position']=621 ;
$qlet['bysupplier_g57']['y_position']=375 ;
$qlet['bysupplier_g57']['max_rows']=100 ;
$qlet['bysupplier_g57']['number_of_columns']=2 ;
$qlet['bysupplier_g57']['column_names']= array('Supplier Name','SUM_Amount' ) ;
$qlet['bysupplier_g57']['column_display_names']= array('Supplier Name','SUM_Amount' ) ;
$qlet['bysupplier_g57']['column_alignment']= array('right','right' ) ;
$qlet['bysupplier_g57']['column_width']= array(148,148 ) ;
$qlet['bysupplier_g57']['display_tooltip']= true  ;
$qlet['bysupplier_g57']['tooltip_template']= <<<EOD
#percent#%, #label# = #value# 
EOD;
$qlet['bysupplier_g57']['background_color']= 'FFFFFF' ;
$qlet['bysupplier_g57']['total_script']= <<<EOD
  
EOD;
$qlet['bysupplier_g57']['sql']= <<<EOD
 Select   "Supplier Name", SUM( "Amount" ) as "SUM_Amount" 
from ( 
select * from ( 
select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity" ,
"Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date",
year("Order Date")  as "Or_Year",
format("Order Date",'m')  as "Or_Mth_N",
format("Order Date",'mm')  as "Or_Mth_Disp",
format("Order Date",'mm-mmm')  as "Or_Mth_NN",
format("Order Date",'yyyy-mm')  as "Or_Yr_Mth",
format("Order Date",'yyyy-mm(mmm)')  as "Or_Yr_Mth_NN",
format("Order Date",'q')  as "Or_Qtr",
'Qtr - '&format("Order Date",'yy-q')  as "Or_Qtr_Disp",
format("Order Date",'ww')  as "Or_Week",
format("Order Date",'yyyy-ww')  as "Or_Yr_wk",
datepart('y',"Order Date")  as "Or_Yr_Dy",
format("Order Date",'dd')  as "Or_Day",
format("Order Date",'yyyy-q')  as "Or_Yr_Qtr",
month("Order Date") as "Or_Month" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity",
"Unit Price" * "Quantity" as "Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (Select 
       ord."Orderid" ,
       ord."Productid" ,
       ord."Supplierid" ,
       ord."Categoryid" ,
       ord."Unit Price" ,
       ord."Quantity" ,
       ord."Discount" ,
       ord."Employeeid" ,
       ord."Emp Lastname" ,
       ord."Emp Firstname" ,
       ord."Emp Title" ,
       ord."Order Date" ,
       ord."Required Date" ,
       ord."Shipped Date" ,
       ord."Ship Name" ,
       ord."Ship Address" ,
       ord."Ship Region" ,
       ord."Shippostalcode" ,
       ord."Shipcountry" ,
       ord."Supplier Name" ,
       ord."Product Name" ,
       ord."Quantity Per Unit" ,
       ord."List Price" ,
       ord."Category Name" ,
       ord."Description" ,
       ord."City" ,
       ord."Region" ,
       ord."Country" ,
       ord."Customerid" ,
       ord."Customer Name" ,
       ord."Cust Contactname" ,
       ord."City1" ,
       ord."Region1" ,
       ord."Country1"
From 
      [order_data$]    ord
 ) as  calx  ) as  dateColsx  ) as  x 
 where "Or_Year" in (PVAL['year_p32'])  
 and  "Category Name" in ('PVAL['categoryname_p34']')  
 and  "Order Date" <=   now()
 and  "Order Date" between format('PVAL['fromorderdate_p36']','yyyy/mm/dd')  and format('PVAL['toorderdate_p38']','yyyy/mm/dd')

 ) as g_tabx
 group by "Supplier Name" 
EOD;
$qlet['bysupplier_g57']['chart_margin']= array(left =>'35',right=>'15',top=>'25',bottom=>'45');
$qlet['bysupplier_g57']['chart_size']=
                           array(
                                 width=>$qlet['bysupplier_g57']['width'] - $qlet['bysupplier_g57']['chart_margin']['left']- $qlet['bysupplier_g57']['chart_margin']['right']
                                ,height =>$qlet['bysupplier_g57']['height'] - $qlet['bysupplier_g57']['chart_margin']['top']- $qlet['bysupplier_g57']['chart_margin']['bottom']-20
                                 );
                                 
$qlet['bysupplier_g57']['chart_position']=
                           array(
                                 top_x=> $qlet['bysupplier_g57']['chart_margin']['left']
                                ,top_y => $qlet['bysupplier_g57']['chart_margin']['top']
                                 );
                                 
$qlet['bysupplier_g57']['legend']=
                           array(
                                 width=> $qlet['bysupplier_g57']['width'] - $qlet['bysupplier_g57']['chart_margin']['left']- $qlet['bysupplier_g57']['chart_margin']['right']
                                ,height => 20
                                ,top_x=> $qlet['bysupplier_g57']['chart_margin']['left']
                                ,top_y => $qlet['bysupplier_g57']['height'] - 20
                                ,label_max_length =>10
                                 );
                                 
$qlet['bysupplier_g57']['template']= <<<EOD

	<chart_label  size='10'  as_percentage='true'/>  
	
<chart_rect x='0' y='20' width='{$percent($qlet['bysupplier_g57']['width'],90)}' height='{$percent($qlet['bysupplier_g57']['height']-20,55)}' positive_color='000000' positive_alpha='0' negative_color='ff0000' negative_alpha='0' />		

 <legend layout='horizontal' 
           fill_color='F0EBE6'
           bullet='square'
           font='arial'
           bold='true'
           size='10'
		  width='{$percent($qlet['bysupplier_g57']['width'],80)}'
		  height='{$percent($qlet['bysupplier_g57']['height']-20,35)}'
		   y='{$get_position($qlet['bysupplier_g57']['height'],20,35)}'
           alpha='90'
           />			
		   
  <draw>
   <text   size='15' x='0' y='0' width='{$qlet['bysupplier_g57']['width']}' height='20' h_align='center' v_align='top'>{$qlet['bysupplier_g57']['name']}</text>
  </draw>
  
<chart_pref
        select='true'            
            />   
  
	<chart_type>#chart_type#</chart_type>

    	#chart_data#


EOD;




/**************  *********************/


/************** byemptitle_g58 *********************/
$qlet['byemptitle_g58']['id']='byemptitle_g58' ;
$qlet['byemptitle_g58']['name']='By Emp title' ;
$qlet['byemptitle_g58']['type']=  'chart_3d pie' ;
$qlet['byemptitle_g58']['connection']='order_raw.xls' ;
$qlet['byemptitle_g58']['original_type']='chart' ;
$qlet['byemptitle_g58']['width']=303 ;
$qlet['byemptitle_g58']['height']=205 ;
$qlet['byemptitle_g58']['x_position']=925 ;
$qlet['byemptitle_g58']['y_position']=376 ;
$qlet['byemptitle_g58']['max_rows']=100 ;
$qlet['byemptitle_g58']['number_of_columns']=2 ;
$qlet['byemptitle_g58']['column_names']= array('Emp Title','SUM_Amount' ) ;
$qlet['byemptitle_g58']['column_display_names']= array('Emp Title','SUM_Amount' ) ;
$qlet['byemptitle_g58']['column_alignment']= array('right','right' ) ;
$qlet['byemptitle_g58']['column_width']= array(151,151 ) ;
$qlet['byemptitle_g58']['display_tooltip']= true  ;
$qlet['byemptitle_g58']['tooltip_template']= <<<EOD
#percent#%, #label# = #value# 
EOD;
$qlet['byemptitle_g58']['background_color']= 'FFFFFF' ;
$qlet['byemptitle_g58']['total_script']= <<<EOD
  
EOD;
$qlet['byemptitle_g58']['sql']= <<<EOD
 Select   "Emp Title", SUM( "Amount" ) as "SUM_Amount" 
from ( 
select * from ( 
select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity" ,
"Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date",
year("Order Date")  as "Or_Year",
format("Order Date",'m')  as "Or_Mth_N",
format("Order Date",'mm')  as "Or_Mth_Disp",
format("Order Date",'mm-mmm')  as "Or_Mth_NN",
format("Order Date",'yyyy-mm')  as "Or_Yr_Mth",
format("Order Date",'yyyy-mm(mmm)')  as "Or_Yr_Mth_NN",
format("Order Date",'q')  as "Or_Qtr",
'Qtr - '&format("Order Date",'yy-q')  as "Or_Qtr_Disp",
format("Order Date",'ww')  as "Or_Week",
format("Order Date",'yyyy-ww')  as "Or_Yr_wk",
datepart('y',"Order Date")  as "Or_Yr_Dy",
format("Order Date",'dd')  as "Or_Day",
format("Order Date",'yyyy-q')  as "Or_Yr_Qtr",
month("Order Date") as "Or_Month" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity",
"Unit Price" * "Quantity" as "Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (Select 
       ord."Orderid" ,
       ord."Productid" ,
       ord."Supplierid" ,
       ord."Categoryid" ,
       ord."Unit Price" ,
       ord."Quantity" ,
       ord."Discount" ,
       ord."Employeeid" ,
       ord."Emp Lastname" ,
       ord."Emp Firstname" ,
       ord."Emp Title" ,
       ord."Order Date" ,
       ord."Required Date" ,
       ord."Shipped Date" ,
       ord."Ship Name" ,
       ord."Ship Address" ,
       ord."Ship Region" ,
       ord."Shippostalcode" ,
       ord."Shipcountry" ,
       ord."Supplier Name" ,
       ord."Product Name" ,
       ord."Quantity Per Unit" ,
       ord."List Price" ,
       ord."Category Name" ,
       ord."Description" ,
       ord."City" ,
       ord."Region" ,
       ord."Country" ,
       ord."Customerid" ,
       ord."Customer Name" ,
       ord."Cust Contactname" ,
       ord."City1" ,
       ord."Region1" ,
       ord."Country1"
From 
      [order_data$]    ord
 ) as  calx  ) as  dateColsx  ) as  x 
 where "Or_Year" in (PVAL['year_p32'])  
 and  "Category Name" in ('PVAL['categoryname_p34']')  
 and  "Order Date" <=   now()
 and  "Order Date" between format('PVAL['fromorderdate_p36']','yyyy/mm/dd')  and format('PVAL['toorderdate_p38']','yyyy/mm/dd')

 ) as g_tabx
 group by "Emp Title" 
EOD;
$qlet['byemptitle_g58']['chart_margin']= array(left =>'35',right=>'15',top=>'25',bottom=>'45');
$qlet['byemptitle_g58']['chart_size']=
                           array(
                                 width=>$qlet['byemptitle_g58']['width'] - $qlet['byemptitle_g58']['chart_margin']['left']- $qlet['byemptitle_g58']['chart_margin']['right']
                                ,height =>$qlet['byemptitle_g58']['height'] - $qlet['byemptitle_g58']['chart_margin']['top']- $qlet['byemptitle_g58']['chart_margin']['bottom']-20
                                 );
                                 
$qlet['byemptitle_g58']['chart_position']=
                           array(
                                 top_x=> $qlet['byemptitle_g58']['chart_margin']['left']
                                ,top_y => $qlet['byemptitle_g58']['chart_margin']['top']
                                 );
                                 
$qlet['byemptitle_g58']['legend']=
                           array(
                                 width=> $qlet['byemptitle_g58']['width'] - $qlet['byemptitle_g58']['chart_margin']['left']- $qlet['byemptitle_g58']['chart_margin']['right']
                                ,height => 20
                                ,top_x=> $qlet['byemptitle_g58']['chart_margin']['left']
                                ,top_y => $qlet['byemptitle_g58']['height'] - 20
                                ,label_max_length =>10
                                 );
                                 
$qlet['byemptitle_g58']['template']= <<<EOD

	<chart_label  size='10'  as_percentage='true'/>  
	
<chart_rect x='0' y='20' width='{$percent($qlet['byemptitle_g58']['width'],90)}' height='{$percent($qlet['byemptitle_g58']['height']-20,55)}' positive_color='000000' positive_alpha='0' negative_color='ff0000' negative_alpha='0' />		

 <legend layout='horizontal' 
           fill_color='F0EBE6'
           bullet='square'
           font='arial'
           bold='true'
           size='10'
		  width='{$percent($qlet['byemptitle_g58']['width'],80)}'
		  height='{$percent($qlet['byemptitle_g58']['height']-20,35)}'
		   y='{$get_position($qlet['byemptitle_g58']['height'],20,35)}'
           alpha='90'
           />			
		   
  <draw>
   <text   size='15' x='0' y='0' width='{$qlet['byemptitle_g58']['width']}' height='20' h_align='center' v_align='top'>{$qlet['byemptitle_g58']['name']}</text>
  </draw>
  
<chart_pref
        select='true'            
            />   
  
	<chart_type>#chart_type#</chart_type>

    	#chart_data#


EOD;




/**************  *********************/


/************** byquarter_g47 *********************/
$qlet['byquarter_g47']['id']='byquarter_g47' ;
$qlet['byquarter_g47']['name']='By Quarter' ;
$qlet['byquarter_g47']['type']=  'chart_column' ;
$qlet['byquarter_g47']['connection']='order_raw.xls' ;
$qlet['byquarter_g47']['original_type']='chart' ;
$qlet['byquarter_g47']['width']=250 ;
$qlet['byquarter_g47']['height']=276 ;
$qlet['byquarter_g47']['x_position']=269 ;
$qlet['byquarter_g47']['y_position']=78 ;
$qlet['byquarter_g47']['max_rows']=100 ;
$qlet['byquarter_g47']['number_of_columns']=2 ;
$qlet['byquarter_g47']['column_names']= array('Or_Qtr_Disp','SUM_Amount' ) ;
$qlet['byquarter_g47']['column_display_names']= array('Or_Qtr_Disp','SUM_Amount' ) ;
$qlet['byquarter_g47']['column_alignment']= array('right','right' ) ;
$qlet['byquarter_g47']['column_width']= array(125,125 ) ;
$qlet['byquarter_g47']['display_tooltip']= true  ;
$qlet['byquarter_g47']['tooltip_template']= "#label# = #value#"  ;
$qlet['byquarter_g47']['background_color']= 'FFFFFF' ;
$qlet['byquarter_g47']['total_script']= <<<EOD
  
EOD;
$qlet['byquarter_g47']['sql']= <<<EOD
 Select   "Or_Qtr_Disp", SUM( "Amount" ) as "SUM_Amount" 
from ( 
select * from ( 
select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity" ,
"Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date",
year("Order Date")  as "Or_Year",
format("Order Date",'m')  as "Or_Mth_N",
format("Order Date",'mm')  as "Or_Mth_Disp",
format("Order Date",'mm-mmm')  as "Or_Mth_NN",
format("Order Date",'yyyy-mm')  as "Or_Yr_Mth",
format("Order Date",'yyyy-mm(mmm)')  as "Or_Yr_Mth_NN",
format("Order Date",'q')  as "Or_Qtr",
'Qtr - '&format("Order Date",'yy-q')  as "Or_Qtr_Disp",
format("Order Date",'ww')  as "Or_Week",
format("Order Date",'yyyy-ww')  as "Or_Yr_wk",
datepart('y',"Order Date")  as "Or_Yr_Dy",
format("Order Date",'dd')  as "Or_Day",
format("Order Date",'yyyy-q')  as "Or_Yr_Qtr",
month("Order Date") as "Or_Month" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity",
"Unit Price" * "Quantity" as "Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (Select 
       ord."Orderid" ,
       ord."Productid" ,
       ord."Supplierid" ,
       ord."Categoryid" ,
       ord."Unit Price" ,
       ord."Quantity" ,
       ord."Discount" ,
       ord."Employeeid" ,
       ord."Emp Lastname" ,
       ord."Emp Firstname" ,
       ord."Emp Title" ,
       ord."Order Date" ,
       ord."Required Date" ,
       ord."Shipped Date" ,
       ord."Ship Name" ,
       ord."Ship Address" ,
       ord."Ship Region" ,
       ord."Shippostalcode" ,
       ord."Shipcountry" ,
       ord."Supplier Name" ,
       ord."Product Name" ,
       ord."Quantity Per Unit" ,
       ord."List Price" ,
       ord."Category Name" ,
       ord."Description" ,
       ord."City" ,
       ord."Region" ,
       ord."Country" ,
       ord."Customerid" ,
       ord."Customer Name" ,
       ord."Cust Contactname" ,
       ord."City1" ,
       ord."Region1" ,
       ord."Country1"
From 
      [order_data$]    ord
 ) as  calx  ) as  dateColsx  ) as  x 
 where "Or_Year" in (PVAL['year_p32'])  
 and  "Category Name" in ('PVAL['categoryname_p34']')  
 and  "Order Date" <=   now()
 and  "Order Date" between format('PVAL['fromorderdate_p36']','yyyy/mm/dd')  and format('PVAL['toorderdate_p38']','yyyy/mm/dd')

 ) as g_tabx
 group by "Or_Qtr_Disp" 
EOD;
$qlet['byquarter_g47']['chart_margin']= array(left =>'35',right=>'15',top=>'25',bottom=>'45');
$qlet['byquarter_g47']['chart_size']=
                           array(
                                 width=>$qlet['byquarter_g47']['width'] - $qlet['byquarter_g47']['chart_margin']['left']- $qlet['byquarter_g47']['chart_margin']['right']
                                ,height =>$qlet['byquarter_g47']['height'] - $qlet['byquarter_g47']['chart_margin']['top']- $qlet['byquarter_g47']['chart_margin']['bottom']-20
                                 );
                                 
$qlet['byquarter_g47']['chart_position']=
                           array(
                                 top_x=> $qlet['byquarter_g47']['chart_margin']['left']
                                ,top_y => $qlet['byquarter_g47']['chart_margin']['top']
                                 );
                                 
$qlet['byquarter_g47']['legend']=
                           array(
                                 width=> $qlet['byquarter_g47']['width'] - $qlet['byquarter_g47']['chart_margin']['left']- $qlet['byquarter_g47']['chart_margin']['right']
                                ,height => 20
                                ,top_x=> $qlet['byquarter_g47']['chart_margin']['left']
                                ,top_y => $qlet['byquarter_g47']['height'] - 20
                                ,label_max_length =>10
                                 );
                                 
$qlet['byquarter_g47']['template']= <<<EOD
	<chart_guide horizontal='true' vertical='true' thickness='1' color='000000' alpha='35' type='dashed' radius='3' fill_alpha='75' line_alpha='0' background_color='FF4400' text_h_alpha='90' text_v_alpha='90' prefix_h=' ' suffix_h=' ' suffix_v=' ' />
	<chart_label  color='000000' alpha='50' size='8' position='middle' as_percentage='true'/>  


	<chart_grid_h thickness='1' type='solid' />


	<series bar_gap='0' set_gap='20' transfer='false' />
	
	<axis_category alpha='75' orientation='diagonal_up' size='9'/>
	
	<axis_value  
         	prefix=''  
			suffix=''  
            decimals=''
            decimal_char=''  
            separator='' 
            show_min='true' 
            size='10' 
            alpha='75'
            orientation='diagonal_up' 
            />

 <legend layout='horizontal' 
           fill_color='F0EBE6'
           bullet='square'
           font='arial'
           bold='true'
           size='10'
 		   width='{$qlet['byquarter_g47']['legend']['width']}'
		   height='{$qlet['byquarter_g47']['legend']['height']}'
		   x='{$qlet['byquarter_g47']['legend']['top_x']}'		   
		   y='{$qlet['byquarter_g47']['legend']['top_y']}'
           alpha='90'
           />			
		   
<chart_rect x='{$qlet['byquarter_g47']['chart_position']['top_x']}' 
            y='{$qlet['byquarter_g47']['chart_position']['top_y']}' 
			width='{$qlet['byquarter_g47']['chart_size']['width']}' 
			height='{$qlet['byquarter_g47']['chart_size']['height']}' 
			positive_color='000000' positive_alpha='0' negative_color='ff0000' negative_alpha='0' />				   
  <draw>
   <text   size='15' x='0' y='0' width='{$qlet['byquarter_g47']['width']}' height='20' h_align='center' v_align='top'>{$qlet['byquarter_g47']['name']}</text>
  </draw> 		   
  
<chart_pref line_thickness='3'     
            point_shape='square'     
            point_size='7'
            fill_shape='false'
            connect='false'
            tip='none'
                        
            
            point_size='7'         
            point_shape='square'
            trend_thickness='3'
            trend_alpha='20'
            line_thickness='0'
            
            rotation_x='30'        
            rotation_y='30'
            drag='true'
    

            select='true'            
            
            zero_line='true'      
            />   
  
	<chart_type>#chart_type#</chart_type>

    	#chart_data#
		
		#link_data#
EOD;




/**************  *********************/


/************** bymonth_g49 *********************/
$qlet['bymonth_g49']['id']='bymonth_g49' ;
$qlet['bymonth_g49']['name']='By Month' ;
$qlet['bymonth_g49']['type']=  'chart_column' ;
$qlet['bymonth_g49']['connection']='order_raw.xls' ;
$qlet['bymonth_g49']['original_type']='chart' ;
$qlet['bymonth_g49']['width']=701 ;
$qlet['bymonth_g49']['height']=276 ;
$qlet['bymonth_g49']['x_position']=526 ;
$qlet['bymonth_g49']['y_position']=77 ;
$qlet['bymonth_g49']['max_rows']=100 ;
$qlet['bymonth_g49']['number_of_columns']=2 ;
$qlet['bymonth_g49']['column_names']= array('Or_Yr_Mth_NN','SUM_Amount' ) ;
$qlet['bymonth_g49']['column_display_names']= array('Or_Yr_Mth_NN','SUM_Amount' ) ;
$qlet['bymonth_g49']['column_alignment']= array('right','right' ) ;
$qlet['bymonth_g49']['column_width']= array(350,350 ) ;
$qlet['bymonth_g49']['display_tooltip']= true  ;
$qlet['bymonth_g49']['tooltip_template']= "#label# = #value#"  ;
$qlet['bymonth_g49']['background_color']= 'FFFFFF' ;
$qlet['bymonth_g49']['total_script']= <<<EOD
  
EOD;
$qlet['bymonth_g49']['sql']= <<<EOD
 Select   "Or_Yr_Mth_NN", SUM( "Amount" ) as "SUM_Amount" 
from ( 
select * from ( 
select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity" ,
"Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date",
year("Order Date")  as "Or_Year",
format("Order Date",'m')  as "Or_Mth_N",
format("Order Date",'mm')  as "Or_Mth_Disp",
format("Order Date",'mm-mmm')  as "Or_Mth_NN",
format("Order Date",'yyyy-mm')  as "Or_Yr_Mth",
format("Order Date",'yyyy-mm(mmm)')  as "Or_Yr_Mth_NN",
format("Order Date",'q')  as "Or_Qtr",
'Qtr - '&format("Order Date",'yy-q')  as "Or_Qtr_Disp",
format("Order Date",'ww')  as "Or_Week",
format("Order Date",'yyyy-ww')  as "Or_Yr_wk",
datepart('y',"Order Date")  as "Or_Yr_Dy",
format("Order Date",'dd')  as "Or_Day",
format("Order Date",'yyyy-q')  as "Or_Yr_Qtr",
month("Order Date") as "Or_Month" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity",
"Unit Price" * "Quantity" as "Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (Select 
       ord."Orderid" ,
       ord."Productid" ,
       ord."Supplierid" ,
       ord."Categoryid" ,
       ord."Unit Price" ,
       ord."Quantity" ,
       ord."Discount" ,
       ord."Employeeid" ,
       ord."Emp Lastname" ,
       ord."Emp Firstname" ,
       ord."Emp Title" ,
       ord."Order Date" ,
       ord."Required Date" ,
       ord."Shipped Date" ,
       ord."Ship Name" ,
       ord."Ship Address" ,
       ord."Ship Region" ,
       ord."Shippostalcode" ,
       ord."Shipcountry" ,
       ord."Supplier Name" ,
       ord."Product Name" ,
       ord."Quantity Per Unit" ,
       ord."List Price" ,
       ord."Category Name" ,
       ord."Description" ,
       ord."City" ,
       ord."Region" ,
       ord."Country" ,
       ord."Customerid" ,
       ord."Customer Name" ,
       ord."Cust Contactname" ,
       ord."City1" ,
       ord."Region1" ,
       ord."Country1"
From 
      [order_data$]    ord
 ) as  calx  ) as  dateColsx  ) as  x 
 where "Or_Year" in (PVAL['year_p32'])  
 and  "Category Name" in ('PVAL['categoryname_p34']')  
 and  "Order Date" <=   now()
 and  "Order Date" between format('PVAL['fromorderdate_p36']','yyyy/mm/dd')  and format('PVAL['toorderdate_p38']','yyyy/mm/dd')

 ) as g_tabx
 group by "Or_Yr_Mth_NN" 
EOD;
$qlet['bymonth_g49']['chart_margin']= array(left =>'35',right=>'15',top=>'25',bottom=>'45');
$qlet['bymonth_g49']['chart_size']=
                           array(
                                 width=>$qlet['bymonth_g49']['width'] - $qlet['bymonth_g49']['chart_margin']['left']- $qlet['bymonth_g49']['chart_margin']['right']
                                ,height =>$qlet['bymonth_g49']['height'] - $qlet['bymonth_g49']['chart_margin']['top']- $qlet['bymonth_g49']['chart_margin']['bottom']-20
                                 );
                                 
$qlet['bymonth_g49']['chart_position']=
                           array(
                                 top_x=> $qlet['bymonth_g49']['chart_margin']['left']
                                ,top_y => $qlet['bymonth_g49']['chart_margin']['top']
                                 );
                                 
$qlet['bymonth_g49']['legend']=
                           array(
                                 width=> $qlet['bymonth_g49']['width'] - $qlet['bymonth_g49']['chart_margin']['left']- $qlet['bymonth_g49']['chart_margin']['right']
                                ,height => 20
                                ,top_x=> $qlet['bymonth_g49']['chart_margin']['left']
                                ,top_y => $qlet['bymonth_g49']['height'] - 20
                                ,label_max_length =>10
                                 );
                                 
$qlet['bymonth_g49']['template']= <<<EOD
	<chart_guide horizontal='true' vertical='true' thickness='1' color='000000' alpha='35' type='dashed' radius='3' fill_alpha='75' line_alpha='0' background_color='FF4400' text_h_alpha='90' text_v_alpha='90' prefix_h=' ' suffix_h=' ' suffix_v=' ' />
	<chart_label  color='000000' alpha='50' size='8' position='middle' as_percentage='true'/>  


	<chart_grid_h thickness='1' type='solid' />


	<series bar_gap='0' set_gap='20' transfer='false' />
	
	<axis_category alpha='75' orientation='diagonal_up' size='9'/>
	
	<axis_value  
         	prefix=''  
			suffix=''  
            decimals=''
            decimal_char=''  
            separator='' 
            show_min='true' 
            size='10' 
            alpha='75'
            orientation='diagonal_up' 
            />

 <legend layout='horizontal' 
           fill_color='F0EBE6'
           bullet='square'
           font='arial'
           bold='true'
           size='10'
 		   width='{$qlet['bymonth_g49']['legend']['width']}'
		   height='{$qlet['bymonth_g49']['legend']['height']}'
		   x='{$qlet['bymonth_g49']['legend']['top_x']}'		   
		   y='{$qlet['bymonth_g49']['legend']['top_y']}'
           alpha='90'
           />			
		   
<chart_rect x='{$qlet['bymonth_g49']['chart_position']['top_x']}' 
            y='{$qlet['bymonth_g49']['chart_position']['top_y']}' 
			width='{$qlet['bymonth_g49']['chart_size']['width']}' 
			height='{$qlet['bymonth_g49']['chart_size']['height']}' 
			positive_color='000000' positive_alpha='0' negative_color='ff0000' negative_alpha='0' />				   
  <draw>
   <text   size='15' x='0' y='0' width='{$qlet['bymonth_g49']['width']}' height='20' h_align='center' v_align='top'>{$qlet['bymonth_g49']['name']}</text>
  </draw> 		   
  
<chart_pref line_thickness='3'     
            point_shape='square'     
            point_size='7'
            fill_shape='false'
            connect='false'
            tip='none'
                        
            
            point_size='7'         
            point_shape='square'
            trend_thickness='3'
            trend_alpha='20'
            line_thickness='0'
            
            rotation_x='30'        
            rotation_y='30'
            drag='true'
    

            select='true'            
            
            zero_line='true'      
            />   
  
	<chart_type>#chart_type#</chart_type>

    	#chart_data#
		
		#link_data#
EOD;




/**************  *********************/


/************** byyear_g45 *********************/
$qlet['byyear_g45']['id']='byyear_g45' ;
$qlet['byyear_g45']['name']='By year' ;
$qlet['byyear_g45']['type']=  'chart_column' ;
$qlet['byyear_g45']['connection']='order_raw.xls' ;
$qlet['byyear_g45']['original_type']='chart' ;
$qlet['byyear_g45']['width']=262 ;
$qlet['byyear_g45']['height']=276 ;
$qlet['byyear_g45']['x_position']=2 ;
$qlet['byyear_g45']['y_position']=77 ;
$qlet['byyear_g45']['max_rows']=100 ;
$qlet['byyear_g45']['number_of_columns']=2 ;
$qlet['byyear_g45']['column_names']= array('Or_Year','SUM_Amount' ) ;
$qlet['byyear_g45']['column_display_names']= array('Or_Year','SUM_Amount' ) ;
$qlet['byyear_g45']['column_alignment']= array('right','right' ) ;
$qlet['byyear_g45']['column_width']= array(131,131 ) ;
$qlet['byyear_g45']['display_tooltip']= true  ;
$qlet['byyear_g45']['tooltip_template']= "#label# = #value#"  ;
$qlet['byyear_g45']['background_color']= 'FFFFFF' ;
$qlet['byyear_g45']['total_script']= <<<EOD
  
EOD;
$qlet['byyear_g45']['sql']= <<<EOD
 Select   "Or_Year", SUM( "Amount" ) as "SUM_Amount" 
from ( 
select * from ( 
select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity" ,
"Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date",
year("Order Date")  as "Or_Year",
format("Order Date",'m')  as "Or_Mth_N",
format("Order Date",'mm')  as "Or_Mth_Disp",
format("Order Date",'mm-mmm')  as "Or_Mth_NN",
format("Order Date",'yyyy-mm')  as "Or_Yr_Mth",
format("Order Date",'yyyy-mm(mmm)')  as "Or_Yr_Mth_NN",
format("Order Date",'q')  as "Or_Qtr",
'Qtr - '&format("Order Date",'yy-q')  as "Or_Qtr_Disp",
format("Order Date",'ww')  as "Or_Week",
format("Order Date",'yyyy-ww')  as "Or_Yr_wk",
datepart('y',"Order Date")  as "Or_Yr_Dy",
format("Order Date",'dd')  as "Or_Day",
format("Order Date",'yyyy-q')  as "Or_Yr_Qtr",
month("Order Date") as "Or_Month" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity",
"Unit Price" * "Quantity" as "Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (Select 
       ord."Orderid" ,
       ord."Productid" ,
       ord."Supplierid" ,
       ord."Categoryid" ,
       ord."Unit Price" ,
       ord."Quantity" ,
       ord."Discount" ,
       ord."Employeeid" ,
       ord."Emp Lastname" ,
       ord."Emp Firstname" ,
       ord."Emp Title" ,
       ord."Order Date" ,
       ord."Required Date" ,
       ord."Shipped Date" ,
       ord."Ship Name" ,
       ord."Ship Address" ,
       ord."Ship Region" ,
       ord."Shippostalcode" ,
       ord."Shipcountry" ,
       ord."Supplier Name" ,
       ord."Product Name" ,
       ord."Quantity Per Unit" ,
       ord."List Price" ,
       ord."Category Name" ,
       ord."Description" ,
       ord."City" ,
       ord."Region" ,
       ord."Country" ,
       ord."Customerid" ,
       ord."Customer Name" ,
       ord."Cust Contactname" ,
       ord."City1" ,
       ord."Region1" ,
       ord."Country1"
From 
      [order_data$]    ord
 ) as  calx  ) as  dateColsx  ) as  x 
 where "Or_Year" in (PVAL['year_p32'])  
 and  "Category Name" in ('PVAL['categoryname_p34']')  
 and  "Order Date" <=   now()
 and  "Order Date" between format('PVAL['fromorderdate_p36']','yyyy/mm/dd')  and format('PVAL['toorderdate_p38']','yyyy/mm/dd')

 ) as g_tabx
 group by "Or_Year" 
EOD;
$qlet['byyear_g45']['chart_margin']= array(left =>'35',right=>'15',top=>'25',bottom=>'45');
$qlet['byyear_g45']['chart_size']=
                           array(
                                 width=>$qlet['byyear_g45']['width'] - $qlet['byyear_g45']['chart_margin']['left']- $qlet['byyear_g45']['chart_margin']['right']
                                ,height =>$qlet['byyear_g45']['height'] - $qlet['byyear_g45']['chart_margin']['top']- $qlet['byyear_g45']['chart_margin']['bottom']-20
                                 );
                                 
$qlet['byyear_g45']['chart_position']=
                           array(
                                 top_x=> $qlet['byyear_g45']['chart_margin']['left']
                                ,top_y => $qlet['byyear_g45']['chart_margin']['top']
                                 );
                                 
$qlet['byyear_g45']['legend']=
                           array(
                                 width=> $qlet['byyear_g45']['width'] - $qlet['byyear_g45']['chart_margin']['left']- $qlet['byyear_g45']['chart_margin']['right']
                                ,height => 20
                                ,top_x=> $qlet['byyear_g45']['chart_margin']['left']
                                ,top_y => $qlet['byyear_g45']['height'] - 20
                                ,label_max_length =>10
                                 );
                                 
$qlet['byyear_g45']['template']= <<<EOD
	<chart_guide horizontal='true' vertical='true' thickness='1' color='000000' alpha='35' type='dashed' radius='3' fill_alpha='75' line_alpha='0' background_color='FF4400' text_h_alpha='90' text_v_alpha='90' prefix_h=' ' suffix_h=' ' suffix_v=' ' />
	<chart_label  color='000000' alpha='50' size='8' position='middle' as_percentage='true'/>  


	<chart_grid_h thickness='1' type='solid' />


	<series bar_gap='0' set_gap='20' transfer='false' />
	
	<axis_category alpha='75' orientation='diagonal_up' size='9'/>
	
	<axis_value  
         	prefix=''  
			suffix=''  
            decimals=''
            decimal_char=''  
            separator='' 
            show_min='true' 
            size='10' 
            alpha='75'
            orientation='diagonal_up' 
            />

 <legend layout='horizontal' 
           fill_color='F0EBE6'
           bullet='square'
           font='arial'
           bold='true'
           size='10'
 		   width='{$qlet['byyear_g45']['legend']['width']}'
		   height='{$qlet['byyear_g45']['legend']['height']}'
		   x='{$qlet['byyear_g45']['legend']['top_x']}'		   
		   y='{$qlet['byyear_g45']['legend']['top_y']}'
           alpha='90'
           />			
		   
<chart_rect x='{$qlet['byyear_g45']['chart_position']['top_x']}' 
            y='{$qlet['byyear_g45']['chart_position']['top_y']}' 
			width='{$qlet['byyear_g45']['chart_size']['width']}' 
			height='{$qlet['byyear_g45']['chart_size']['height']}' 
			positive_color='000000' positive_alpha='0' negative_color='ff0000' negative_alpha='0' />				   
  <draw>
   <text   size='15' x='0' y='0' width='{$qlet['byyear_g45']['width']}' height='20' h_align='center' v_align='top'>{$qlet['byyear_g45']['name']}</text>
  </draw> 		   
  
<chart_pref line_thickness='3'     
            point_shape='square'     
            point_size='7'
            fill_shape='false'
            connect='false'
            tip='none'
                        
            
            point_size='7'         
            point_shape='square'
            trend_thickness='3'
            trend_alpha='20'
            line_thickness='0'
            
            rotation_x='30'        
            rotation_y='30'
            drag='true'
    

            select='true'            
            
            zero_line='true'      
            />   
  
	<chart_type>#chart_type#</chart_type>

    	#chart_data#
		
		#link_data#
EOD;




/**************  *********************/


/************** byproduct_g50 *********************/
$qlet['byproduct_g50']['id']='byproduct_g50' ;
$qlet['byproduct_g50']['name']='By Product' ;
$qlet['byproduct_g50']['type']=  'chart_column' ;
$qlet['byproduct_g50']['connection']='order_raw.xls' ;
$qlet['byproduct_g50']['original_type']='chart' ;
$qlet['byproduct_g50']['width']=600 ;
$qlet['byproduct_g50']['height']=248 ;
$qlet['byproduct_g50']['x_position']=626 ;
$qlet['byproduct_g50']['y_position']=579 ;
$qlet['byproduct_g50']['max_rows']=100 ;
$qlet['byproduct_g50']['number_of_columns']=2 ;
$qlet['byproduct_g50']['column_names']= array('Product Name','SUM_Amount' ) ;
$qlet['byproduct_g50']['column_display_names']= array('Product Name','SUM_Amount' ) ;
$qlet['byproduct_g50']['column_alignment']= array('right','right' ) ;
$qlet['byproduct_g50']['column_width']= array(300,300 ) ;
$qlet['byproduct_g50']['display_tooltip']= true  ;
$qlet['byproduct_g50']['tooltip_template']= "#label# = #value#"  ;
$qlet['byproduct_g50']['background_color']= 'FFFFFF' ;
$qlet['byproduct_g50']['total_script']= <<<EOD
  
EOD;
$qlet['byproduct_g50']['sql']= <<<EOD
 Select   "Product Name", SUM( "Amount" ) as "SUM_Amount" 
from ( 
select * from ( 
select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity" ,
"Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date",
year("Order Date")  as "Or_Year",
format("Order Date",'m')  as "Or_Mth_N",
format("Order Date",'mm')  as "Or_Mth_Disp",
format("Order Date",'mm-mmm')  as "Or_Mth_NN",
format("Order Date",'yyyy-mm')  as "Or_Yr_Mth",
format("Order Date",'yyyy-mm(mmm)')  as "Or_Yr_Mth_NN",
format("Order Date",'q')  as "Or_Qtr",
'Qtr - '&format("Order Date",'yy-q')  as "Or_Qtr_Disp",
format("Order Date",'ww')  as "Or_Week",
format("Order Date",'yyyy-ww')  as "Or_Yr_wk",
datepart('y',"Order Date")  as "Or_Yr_Dy",
format("Order Date",'dd')  as "Or_Day",
format("Order Date",'yyyy-q')  as "Or_Yr_Qtr",
month("Order Date") as "Or_Month" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (select 
"Orderid" ,
"Productid" ,
"Supplierid" ,
"Categoryid" ,
"Unit Price" ,
"Quantity",
"Unit Price" * "Quantity" as "Amount" ,
"Discount" ,
"Employeeid" ,
"Emp Lastname" ,
"Emp Firstname" ,
"Emp Title" ,
"Order Date" ,
"Required Date" ,
"Shipped Date" ,
"Ship Name" ,
"Ship Address" ,
"Ship Region" ,
"Shippostalcode" ,
"Shipcountry" ,
"Supplier Name" ,
"Product Name" ,
"Quantity Per Unit" ,
"List Price" ,
"Category Name" ,
"Description" ,
"City" ,
"Region" ,
"Country" ,
"Customerid" ,
"Customer Name" ,
"Cust Contactname" ,
"City1" ,
"Region1" ,
"Country1" 
From (Select 
       ord."Orderid" ,
       ord."Productid" ,
       ord."Supplierid" ,
       ord."Categoryid" ,
       ord."Unit Price" ,
       ord."Quantity" ,
       ord."Discount" ,
       ord."Employeeid" ,
       ord."Emp Lastname" ,
       ord."Emp Firstname" ,
       ord."Emp Title" ,
       ord."Order Date" ,
       ord."Required Date" ,
       ord."Shipped Date" ,
       ord."Ship Name" ,
       ord."Ship Address" ,
       ord."Ship Region" ,
       ord."Shippostalcode" ,
       ord."Shipcountry" ,
       ord."Supplier Name" ,
       ord."Product Name" ,
       ord."Quantity Per Unit" ,
       ord."List Price" ,
       ord."Category Name" ,
       ord."Description" ,
       ord."City" ,
       ord."Region" ,
       ord."Country" ,
       ord."Customerid" ,
       ord."Customer Name" ,
       ord."Cust Contactname" ,
       ord."City1" ,
       ord."Region1" ,
       ord."Country1"
From 
      [order_data$]    ord
 ) as  calx  ) as  dateColsx  ) as  x 
 where "Or_Year" in (PVAL['year_p32'])  
 and  "Category Name" in ('PVAL['categoryname_p34']')  
 and  "Order Date" <=   now()
 and  "Order Date" between format('PVAL['fromorderdate_p36']','yyyy/mm/dd')  and format('PVAL['toorderdate_p38']','yyyy/mm/dd')

 ) as g_tabx
 group by "Product Name"
order by 2 desc 
EOD;
$qlet['byproduct_g50']['chart_margin']= array(left =>'35',right=>'15',top=>'25',bottom=>'45');
$qlet['byproduct_g50']['chart_size']=
                           array(
                                 width=>$qlet['byproduct_g50']['width'] - $qlet['byproduct_g50']['chart_margin']['left']- $qlet['byproduct_g50']['chart_margin']['right']
                                ,height =>$qlet['byproduct_g50']['height'] - $qlet['byproduct_g50']['chart_margin']['top']- $qlet['byproduct_g50']['chart_margin']['bottom']-20
                                 );
                                 
$qlet['byproduct_g50']['chart_position']=
                           array(
                                 top_x=> $qlet['byproduct_g50']['chart_margin']['left']
                                ,top_y => $qlet['byproduct_g50']['chart_margin']['top']
                                 );
                                 
$qlet['byproduct_g50']['legend']=
                           array(
                                 width=> $qlet['byproduct_g50']['width'] - $qlet['byproduct_g50']['chart_margin']['left']- $qlet['byproduct_g50']['chart_margin']['right']
                                ,height => 20
                                ,top_x=> $qlet['byproduct_g50']['chart_margin']['left']
                                ,top_y => $qlet['byproduct_g50']['height'] - 20
                                ,label_max_length =>10
                                 );
                                 
$qlet['byproduct_g50']['template']= <<<EOD
	<chart_guide horizontal='true' vertical='true' thickness='1' color='000000' alpha='35' type='dashed' radius='3' fill_alpha='75' line_alpha='0' background_color='FF4400' text_h_alpha='90' text_v_alpha='90' prefix_h=' ' suffix_h=' ' suffix_v=' ' />
	<chart_label  color='000000' alpha='50' size='8' position='middle' as_percentage='true'/>  


	<chart_grid_h thickness='1' type='solid' />


	<series bar_gap='0' set_gap='20' transfer='false' />
	
	<axis_category alpha='75' orientation='diagonal_up' size='9'/>
	
	<axis_value  
         	prefix=''  
			suffix=''  
            decimals=''
            decimal_char=''  
            separator='' 
            show_min='true' 
            size='10' 
            alpha='75'
            orientation='diagonal_up' 
            />

 <legend layout='horizontal' 
           fill_color='F0EBE6'
           bullet='square'
           font='arial'
           bold='true'
           size='10'
 		   width='{$qlet['byproduct_g50']['legend']['width']}'
		   height='{$qlet['byproduct_g50']['legend']['height']}'
		   x='{$qlet['byproduct_g50']['legend']['top_x']}'		   
		   y='{$qlet['byproduct_g50']['legend']['top_y']}'
           alpha='90'
           />			
		   
<chart_rect x='{$qlet['byproduct_g50']['chart_position']['top_x']}' 
            y='{$qlet['byproduct_g50']['chart_position']['top_y']}' 
			width='{$qlet['byproduct_g50']['chart_size']['width']}' 
			height='{$qlet['byproduct_g50']['chart_size']['height']}' 
			positive_color='000000' positive_alpha='0' negative_color='ff0000' negative_alpha='0' />				   
  <draw>
   <text   size='15' x='0' y='0' width='{$qlet['byproduct_g50']['width']}' height='20' h_align='center' v_align='top'>{$qlet['byproduct_g50']['name']}</text>
  </draw> 		   
  
<chart_pref line_thickness='3'     
            point_shape='square'     
            point_size='7'
            fill_shape='false'
            connect='false'
            tip='none'
                        
            
            point_size='7'         
            point_shape='square'
            trend_thickness='3'
            trend_alpha='20'
            line_thickness='0'
            
            rotation_x='30'        
            rotation_y='30'
            drag='true'
    

            select='true'            
            
            zero_line='true'      
            />   
  
	<chart_type>#chart_type#</chart_type>

    	#chart_data#
		
		#link_data#
EOD;


$qlet['list'] = array ('productrevenuedistribution_g51' , 'bysupplier_g57' , 'byemptitle_g58' , 'byquarter_g47' , 'bymonth_g49' , 'byyear_g45' , 'byproduct_g50');





$d_options['lowest_y_location'] = 77



?>