///$tab Main
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

$(include=C:\QlikviewFiles\Properties\DataPath.txt)

LET RELOAD_VOLUME = 12;
LET RELOAD_VOLUME_FACT = 13;

//LET MP_CUTOFF_TIME = 9;



///$tab Mappings
ekl_workday_map:
Mapping LOAD
     date,
     dateno
FROM
[$(qvdPath_excel)ekl_workday_map_weekly.xls]
(biff, embedded labels, table is Sheet1$);

mp_fkl_mh:
Mapping Load * Inline [
	id, MH_name
	1, Blr_MH
	3, Del_MH
	5, Mum_MH
];

mp_pickup_hub:
Mapping LOAD id as mp_hub_id,
     name as mp_hub_name
FROM
[$(qvdPath_proc)proc_procurement_facilities.qvd]
(qvd);

hubid_hubname_mapping:
mapping LOAD id as hubId,
     upper(name) as hubname
FROM
 [$(qvdPath_ekl_facility)ekl_facilities_facilities.qvd]
(qvd);

hubid_fsd_HubType_mapping:
mapping LOAD  id as hubId,
     type as fsd_HubType
FROM
 [$(qvdPath_ekl_facility)ekl_facilities_facilities.qvd]
(qvd);

day_map:
Mapping LOAD
     date,
     dateno
FROM
[$(qvdPath_excel)workday_map.xls]
(biff, embedded labels, table is Sheet1$);


hub_city_map:
Mapping LOAD
     Destination as hub,
     [Destination City] as hub_city
FROM
[$(qvdPath_excel)Final Map v3.xlsx]
(ooxml, embedded labels, table is Route);

hub_zone_map:
Mapping LOAD Destination as hub,
     [Destination Zone] as hub_zone
FROM
[$(qvdPath_excel)Final Map v3.xlsx]
(ooxml, embedded labels, table is Route);


class_mapping:
Mapping LOAD City,
     Class
FROM
[$(qvdPath_excel)EKL_Class_Mapping.xlsx]
(ooxml, embedded labels, table is Sheet1);

hubid_hubname_mapping:
mapping LOAD [Destination Hub Id] as hubId,
     Destination as hubname
FROM
[$(qvdPath_excel)Final Map v3.xlsx]
(ooxml, embedded labels, table is Route);


connection_mapping:
Mapping LOAD
	Key_2,
	[Conn. Type]
FROM
[$(qvdPath_excel)Final Map v3.xlsx]
(ooxml, embedded labels, table is Route);

fsd_status_mapping:
Mapping LOAD statusId,
	statusDescription
	FROM
[$(qvdPath_fkl)fkl_status.qvd]
(qvd);

//hub_holiday_mapping:
//Mapping LOAD hub_city,
//     holiday
//FROM
//[$(qvdPath_excel)EKL_HUB_Holiday.xlsx]
//(ooxml, embedded labels, table is Sheet1);











///$tab Fact Reload
shipmentFact:
Load distinct upper(vendor_tracking_id) as tracking_id,
	 merchant_reference_id,
     cost_of_breach,
     weight as shipmentWeight,
     shipment_value,
     delivery_type,
     carrier,
     ekl_shipment_type_ekl_db,
     If(isnull(fkl_delivery_datetime)	, end_state_datetime,fkl_delivery_datetime) as fkl_delivery_datetime,
     if(cost_of_breach<100,'Normal',if(cost_of_breach<1000,'NDD Pilot',if(cost_of_breach<15000,'NDD',if(cost_of_breach<20000,'SDD Pilot',if(cost_of_breach>=20000,'SDD','Normal'))))) as order_priority_flag

FROM
[$(qvdPath_fkl)fkl_shipment_fact.qvd]
(qvd)
where floor(created_at) >= floor(today()-$(RELOAD_VOLUME_FACT));

left join(shipmentFact)
LOAD distinct shipmentId as tracking_id,
    // NZL,
//     fsd_numberOfAttempts,
//     fsd_first_received_datetime,
//     fsd_first_DH_received_datetime,
//     fsd_first_received_hubId,
      fsd_first_DH_hubId,
     fsd_first_DH_received_datetime,
     shipment_lastBagId as fsd_shipment_last_bagid,
     ApplyMap('hubid_fsd_HubType_mapping',fsd_last_received_hubId,null()) as fsd_last_hub_type,
     fsd_last_expected_datetime,
     fsd_last_received_datetime as fsd_last_receive_datetime,
     fsd_last_received_hubId as fsd_last_hub_id,
     floor(fsd_delivery_datetime) as ekl_delivery_date,
     fsd_delivery_datetime as ekl_delivery_datetime,
     fsd_current_status,
     shipment_lastConsignmentId as fsd_shipment_lastConsignmentId,
     fsd_first_OFD_datetime,
     fsd_last_OFD_datetime
    // fsd_first_OFD_datetime-fsd_first_DH_received_datetime as recToOFDTIme
     //fsd_shipment_flag
FROM
[$(qvdPath_fsd)fsd_shipment_fact.qvd](qvd);
//where Timestamp(fsd_first_OFD_datetime) >= Timestamp(today()-$(RELOAD_VOLUME_FACT));

left join(shipmentFact)
LOAD distinct consignmentId as fsd_shipment_lastConsignmentId,
     consignment_receive_datetime as fsd_consignment_receive_datetime

FROM
[$(qvdPath_fsd)fsd_consignment_facts.qvd]
(qvd);

left join(shipmentFact)
LOAD statusId as fsd_current_status,
     statusTypeId as fsd_current_status_typeId,
     statusCode as fsd_current_status_code,
     statusDescription as fsd_current_status_desc
FROM
[$(qvdPath_fkl)fkl_status.qvd]
(qvd);

left join(shipmentFact)
LOAD upper(shipmentId) as tracking_id,
     flagType as fsd_shipment_flag
FROM
[$(qvdPath_fsd)fsd_shipmentFlag.qvd]
(qvd);

left join(shipmentFact)
LOAD distinct [Destination Hub Id] as fsd_last_hub_id,
     //Destination as fsd_destinationHub,
     upper([Destination City]) as fsd_last_City
FROM
[$(qvdPath_excel)Final Map v3.xlsx]
(ooxml, embedded labels, table is Route);

left join(shipmentFact)
LOAD distinct  City as fsd_last_City,
    // State,
     Holiday_Date as holiday_dh_date,
     Holidays as holidays
FROM
[$(qvdPath_excel)EKL_HUB_Holiday_Weekly.xlsx]
(ooxml, embedded labels, table is Sheet1);

//exit script;


left join(shipmentFact)
LOAD distinct bagId as fsd_shipment_last_bagid,
	 bagDestHubId as fsd_shipment_last_bag_receive_hubid,
	 ApplyMap('hubid_fsd_HubType_mapping',bagDestHubId,null()) as fsd_shipment_last_bag_receive_type,
     bagFinalReachedDateTime as fsd_shipment_last_bag_receive_datetime
FROM
[$(qvdPath_fsd)fsd_bag_facts.qvd]
(qvd)
where bagAssignedHubId=bagDestHubId;

shipmentFact1:
NoConcatenate
Load *,
	 If(fsd_shipment_last_bag_receive_type='DELIVERY_HUB' and fsd_last_hub_type='DELIVERY_HUB'and fsd_last_expected_datetime<=fsd_shipment_last_bag_receive_datetime,
	 	fsd_shipment_last_bag_receive_datetime,if(fsd_last_hub_type='DELIVERY_HUB',fsd_last_receive_datetime)) as fsd_last_dh_receive_datetime,
	 if(fsd_last_hub_type='DELIVERY_HUB',fsd_last_hub_id) as fsd_last_dh_id
	// if(fsd_last_hub_type='DELIVERY_HUB',ApplyMap('hub_holiday_mapping',fsd_last_City)) as holiday_dh_date

Resident shipmentFact;

//exit script;

drop table shipmentFact;

shipmentFact2:
NoConcatenate
Load *,
	//if(floor(holiday_dh_date)>=floor(fsd_last_dh_receive_datetime) and floor(holiday_dh_date)<=floor(ekl_delivery_date),1,0) as holiday_dh_flag,
	if(floor(holiday_dh_date)>=floor(fsd_last_dh_receive_datetime) and floor(holiday_dh_date)<=floor(ekl_delivery_date),holidays,0) as holiday_dh_flag,
	DayName(fsd_last_dh_receive_datetime) as fsd_last_dh_receive_date,
	if(match(fsd_last_hub_id,54,145,134,86,462,148,55,95,177,199,628,204,188,173,158,490,481,482,110,1337,1338,178,641,636,258,234,201,235,1363,613,1362,1367,118,109,174,163,113,146,192,114,250,495,503,65,637,100,583,193,233,195,241,584,1361,78,106,414,168,175, 196, 77,	248, 94,	287, 640, 726, 1385, 212, 720, 151),
	  if(fsd_last_dh_receive_datetime-floor(fsd_last_dh_receive_datetime)>'8:00:00',fsd_last_dh_receive_datetime+1,fsd_last_dh_receive_datetime),
	//  if(match(fsd_last_hub_id,54,86,462,148,55,95,177,199,628,204,188,173,158,490,481,482,110,1337,1338,178,641,636,258,234,201,235,1363,613,1362,1367,118,109,174,163,113,146,192,114,250,495,503,65,637,100,583,193,233,195,241,584,1361,78,106,414,168,175, 196, 77,	248, 94,	287, 640, 726, 1385, 212, 720, 151),
//	if(fsd_last_dh_receive_datetime-floor(fsd_last_dh_receive_datetime)>'8:00:00',fsd_last_dh_receive_datetime+1,fsd_last_dh_receive_datetime),if(fsd_last_dh_receive_datetime-floor(fsd_last_dh_receive_datetime)>'12:00:00',fsd_last_dh_receive_datetime+1,fsd_last_dh_receive_datetime))) as fsd_last_dh_receive_adjusted_datetime,
	 if(fsd_last_dh_receive_datetime-floor(fsd_last_dh_receive_datetime)>'12:00:00',fsd_last_dh_receive_datetime+1,fsd_last_dh_receive_datetime)) as fsd_last_dh_receive_adjusted_datetime,
	Applymap('ekl_workday_map',Dayname(If(ekl_delivery_datetime-floor(ekl_delivery_datetime)<0.125,ekl_delivery_date-1,ekl_delivery_date))) as ekl_delivery_indays_adjusted
Resident shipmentFact1;

//exit Script;
drop table shipmentFact1;

shipmentFact:
NoConcatenate
Load *,
	if(fsd_last_hub_type='DELIVERY_HUB',
		if(IsNull(ekl_delivery_date),if(DayName(fsd_last_dh_receive_adjusted_datetime)=Today() or (DayName(fsd_last_dh_receive_adjusted_datetime)=today()-1 and today()=weekstart(today())),'Pending After 3',
			'Pending_'&(today()-Floor(fsd_last_dh_receive_adjusted_datetime))&'_days'),
			if(WeekDay(fsd_last_dh_receive_adjusted_datetime)=6,ekl_delivery_indays_adjusted-ApplyMap('ekl_workday_map',DayName(fsd_last_dh_receive_adjusted_datetime))-holiday_dh_flag-1,
				ekl_delivery_indays_adjusted-ApplyMap('ekl_workday_map',DayName(fsd_last_dh_receive_adjusted_datetime))-holiday_dh_flag)),'Still at MH') as Dh_delivery_indays,
	dayname(fsd_last_dh_receive_adjusted_datetime) as fsd_last_dh_receive_adjusted_date

resident shipmentFact2;

drop table shipmentFact2;

//exit script;
//left join(shipmentFact)
//Load upper(vendor_tracking_id) as tracking_id,
//	 merchant_reference_id,
//     cost_of_breach,
//     weight as shipmentWeight,
//     shipment_value,
//     delivery_type,
//     carrier,
//     ekl_shipment_type_ekl_db,
//     If(isnull(fkl_delivery_datetime)	, end_state_datetime,fkl_delivery_datetime) as fkl_delivery_datetime
//
//FROM
//[$(qvdPath_fkl)fkl_shipment_fact.qvd]
//(qvd)
//where floor(created_at) >= floor(today()-$(RELOAD_VOLUME_FACT));

left join(shipmentFact)
Load
distinct unit_tracking_id as tracking_id,
//Item_amount,
order_item_id,
product_id,
unit_quantity,
final_adjusted_amount as order_item_value,
unit_status
//unit_tracking_id&order_item_id as keyShip
//sku
FROM
[$(qvdPath_common)Fact_table.qvd]
(qvd)
where floor(dispatch_date) >=  floor(today()-$(RELOAD_VOLUME));

left join(shipmentFact)
Load
distinct product_id ,
title_product_info,
sub_category,
super_category,
vertical,
category,
anlt_cluster
FROM
[$(qvdPath_common)Product_InfoMP.qvd]
(qvd);


//left join(fkl_shipments)
flo_shipments:
LOAD Distinct display_id as merchant_reference_id,
     used_packing_box_id as warehouse_packing_box_id
FROM
[$(qvdPath_flo_warehouse_b2c)flo_warehouse_b2c_shipments.qvd]
(qvd);
Concatenate(flo_shipments)
LOAD Distinct display_id as merchant_reference_id,
     used_packing_box_id as warehouse_packing_box_id
FROM
[$(qvdPath_flo_warehouse_b2b)flo_warehouse_b2b_shipments.qvd](qvd);

left join(shipmentFact)
Load merchant_reference_id,
	 warehouse_packing_box_id
resident flo_shipments	;

drop table flo_shipments;

warehouse_packing_box:
LOAD Distinct id as warehouse_packing_box_id,
     length as warehouse_pb_length,
     breadth as warehouse_pb_breadth,
     height as warehouse_pb_height,
     (length*breadth*height)/366 as shipment_volumetricWeight
FROM
[$(qvdPath_flo_warehouse_b2c)flo_warehouse_b2c_packing_boxes.qvd]
(qvd);

Concatenate(warehouse_packing_box)
LOAD Distinct id as warehouse_packing_box_id,
     length as warehouse_pb_length,
     breadth as warehouse_pb_breadth,
     height as warehouse_pb_height,
     (length*breadth*height)/366 as shipment_volumetricWeight
FROM
[$(qvdPath_flo_warehouse_b2b)flo_warehouse_b2b_packing_boxes.qvd]
(qvd);

left join(shipmentFact)
Load warehouse_packing_box_id,
     shipment_volumetricWeight
resident warehouse_packing_box;

//left join(fkl_shipments)
//LOAD Distinct id as warehouse_packing_box_id,
//      (length*breadth*height)/366 as shipment_volumetricWeight
//FROM
//[$(qvdPath_flo_warehouse_b2c)flo_warehouse_b2c_packing_boxes.qvd]
//(qvd);

//exit script;

drop table warehouse_packing_box;

left join(shipmentFact)
Load Distinct tracking_id,
      if(shipmentWeight>shipment_volumetricWeight,shipmentWeight,shipment_volumetricWeight) as shipmentChargeableWeightRaw
Resident shipmentFact;

left join(shipmentFact)
Load Distinct tracking_id,
      if(shipmentChargeableWeightRaw>20,0.5,shipmentChargeableWeightRaw) as shipmentChargeableWeight
Resident shipmentFact;

left join(shipmentFact)
Load Distinct tracking_id,
      if(isnull(shipmentChargeableWeight),0.5,shipmentChargeableWeight) as shipmentChargeableWeightFinal
Resident shipmentFact;

//exit script;
///$tab Agent/Hub
//ODBC CONNECT TO facilities;
DayDetails:
Load date as Date,
    // date as DateRec,
     //day_date as fsd_last_dh_receive_adjusted_day,
    week_date as Week,
    month_date as Month,
    year_date as Year,
    if (DayName(date)=DayName(Today(1)-1),1,0) as varForYesterday,
    if (DayName(date)=DayName(Today(1)-4),1,0) as posFraudDateSelection,
    if (DayName(date)=DayName(Today(1)-1),1,0) as mp_varForYesterday,
    if (DayName(date)<DayName(Today(1)-2) and DayName(date)>DayName(41852),1,0) as dateSelectionPOS
FROM [$(qvdPath_common)date_calendar.qvd](qvd)
where Timestamp(date) >= Timestamp(today()-$(RELOAD_VOLUME));

Agents:
LOAD
	//`facility_id` as fsd_HubId,
	 id as runAgentId,
     upper(first_name)  as runAgentName,
     if(Right(first_name,2)='PL',upper(left(first_name,len(first_name)-3)),upper(first_name)) as runAgentNameFinal,
     employee_id as FHRId,
     active as isFEActive,
     created_at as  FEJoiningDate
     //Right(display_name,len(display_name)-Index(display_name,'_',-1)) as FHRId
     //TextBetween( first_name&':','_',':',3) as FHRId,
     //Lookup('isVan','AgentId',id,'VanNotBike') as validVan,
     //Lookup('istlhl','fhrid',TextBetween( first_name&':','_',':',3),'Tl_HI') as validMan
     FROM
[$(qvdPath_ekl_facility)ekl_facilities_agents.qvd]
(qvd)
where active=1;


//Agents:
//NoConcatenate
//Load *,
//     Right(runAgentNameFinal,len(runAgentNameFinal)-Index(runAgentNameFinal,'_',-1)) as FHRId
//    //if(Right(runAgentName,2)='PL',left(runAgentName,len(runAgentName)-2),runAgentName) as runAgentNameFinal`
//Resident Agents1;

//drop table Agents1;
left join(Agents)
LOAD
     facility_id as fsd_HubId,
     agent_id as runAgentId  ,
     active as isFAMAcitve
FROM
[$(qvdPath_ekl_facility)ekl_facility_agent_mappings.qvd]
(qvd)
where active=1;

//ODBC CONNECT TO fklogistics;
Run_Agents:
NoConcatenate
//left keep(Runsheet)
Load runAgentId,
	FHRId,
	 runAgentName,
	 runAgentNameFinal,
	 isFEActive,
	 isFAMAcitve,
	 if(isnull(fsd_HubId),'Hub not mapped',fsd_HubId) as fsd_HubId,
	 FEJoiningDate
Resident Agents;
//Exit Script;
left join(Run_Agents)
LOAD
     facility_id as fsd_HubId,
     value as fsd_loc_code
FROM
[$(qvdPath_ekl_facility)ekl_facilities_facility_attributes.qvd](qvd)
where upper(name)='LOCATION_CODE';

//exit script;

Run_Agents_Date1:
NoConcatenate
Load distinct FHRId,
     1 as join
Resident  Run_Agents ;

left join(Run_Agents_Date1)
Load Date,
     //day_date as fsd_last_dh_receive_adjusted_day,
    //week_date as Week,
    //month_date as Month,
    //year_date as Year,
    //if (DayName(date)=DayName(Today(1)-1),1,0) as varForYesterday,
    //if (DayName(date)=DayName(Today(1)-1),1,0) as mp_varForYesterday,
    1 as join
Resident DayDetails;
//FROM [$(qvdPath_common)date_calendar.qvd](qvd)
//where Timestamp(date) >= Timestamp(today()-$(RELOAD_VOLUME));


left keep(Run_Agents)
left keep(DayDetails)
Run_Agents_Date:
//NoConcatenate
Load *, FHRId&Date as key
resident Run_Agents_Date1;

drop table Run_Agents_Date1;

Hub:
left Keep(Run_Agents)
LOAD id as fsd_HubId,
     //upper(type) as fsd_HubType,
     upper(name) as fsd_HubName
FROM
[$(qvdPath_ekl_facility)ekl_facilities_facilities.qvd](qvd);
//where UPPER(type)='DELIVERY_HUB' or UPPER(type)='PICKUP_HUB';

left join(Hub)
LOAD
     facility_id as fsd_HubId,
     value as fsd_loc_code
FROM
[$(qvdPath_ekl_facility)ekl_facilities_facility_attributes.qvd](qvd)
where upper(name)='LOCATION_CODE';

left join(Hub)
LOAD distinct upper([Destination Hub Id]) as fsd_HubId,
     //Destination as fsd_destinationHub,
     upper([Destination City]) as fsd_City,
     upper([Ops Zone]) as fsd_Zone,
     upper([Destination City/State]) as fsd_State,
     upper([Hub Type]) as fsd_HubType

FROM
[$(qvdPath_excel)Final Map v3.xlsx]
(ooxml, embedded labels, table is Route);

run_Hub:
NoConcatenate
Load fsd_HubId,
	 fsd_HubType,
     fsd_loc_code,
     fsd_HubName,
    // if(isnull(fsd_destinationHub),'Hub not mapped',fsd_destinationHub) as fsd_destinationHub,
     if(isnull(fsd_City),'City not mapped',fsd_City) as fsd_City,
     if(isnull(fsd_Zone),'Zone not mapped',fsd_Zone) as fsd_Zone,
     if(isnull(fsd_State),'Zone not mapped',fsd_State) as fsd_State
Resident Hub;
Drop Table Hub;

//exit script;

//left join(run_Hub)
//LOAD
//     [Hub Id] as fsd_HubId,
//     Count as devicesAssigned,
//     '1' as inSmartProject1
//FROM
//[$(qvdPath_excel)Hub_Device_Count.xlsx]
//(ooxml, embedded labels, table is Sheet1);

//run_Hub2:
//NoConcatenate
//Load distinct fsd_HubId,
//			  fsd_City,
//               devicesAssigned
//resident  run_Hub;
//group by fsd_City;

//left join(run_Hub)
//Load fsd_City,
//	sum( devicesAssigned) as devicesAssignedCity
//resident  run_Hub2
//group by fsd_City ;

//left join(run_Hub)
//LOAD [Hub Id] as fsd_HubId,
//     //[Hub Name],
//     [MP Vans] as mp_vans
//FROM
//[$(qvdPath_excel)MP_Vans_Hub.xlsx]
//(ooxml, embedded labels, table is Sheet1);

left keep(run_Hub)
POSHubMap:
LOAD
     hubId as fsd_HubId,
    posId as posDevicesAssignedId
FROM
[$(qvdPath_fsd)fsd_POS_Hub_Mapping.qvd]
(qvd);

left join(POSHubMap)
LOAD posId as posDevicesAssignedId,
     serial as posSerialAssignedId,
     merchantId as posMerchantAssignedId,
     provider as posVendorNameAssignedId,
     status as posVendorStatusAssigned
FROM
[$(qvdPath_fsd)fsd_posDevice.qvd](qvd);

left keep(POSHubMap)
Load dateOfTransaction as dateOfTransactionMIS,
     if (DayName(dateOfTransaction)=DayName(Today(1)-4),1,0) as posFraudDateSelectionMIS,
     terminalId as posSerialAssignedId,
     sum(amount) as amountPOSMIS
FROM [$(qvdPath_fsd)fsd_posBankTransaction.qvd](qvd)
group by  dateOfTransaction,terminalId    ;



//drop table run_Hub2;

left join(run_Hub)
Load * inline
[fsd_HubId, inVolumetricProject
209,1
4,1
56,1
90,1
226,1
11,1
57,1
202,1
220,1
194,1
3,1
29,1
239,1
32,1
152,1
243,1
89,1
38,1
];

left join(run_Hub)
Load * inline
[fsd_City, inVanReport
KANCHIPURAM,1
BENGALURU,1
PONDICHERRY,1
KOLKATA,1
JAIPUR,1
KHARAGPUR,1
TRICHY,1
HYDERABAD,1
AHMEDABAD,1
INDORE,1
PATNA,1
BHOPAL,1
COCHIN,1
DELHI NCR,1
GOA,1
CHENNAI,1
COIMBATORE,1
MUMBAI,1
NAVI MUMBAI,1
PUNE,1
GUWAHATI,1
VISHAKHAPATANAM,1
NAGPUR,1
VELLORE,1
KHARAGPUR,1
BHUBANESWAR,1
NEW DELHI,1
GURGAON,1
FARIDABAD,1
MANGALORE,1
MYSORE,1
MANIPAL,1
CHANDIGAR,1H
SURAT,1
BARODA,1
LUCKNOW,1
HUBLI,1
MADURAI,1
ERODE,1
SALEM,1
THANJAVUR,1
GUNTUR,1
NELLORE,1
VIJAYAWADA,1
SILIGURI,1
TIRUPATI,1
RAIPUR,1

];


left join(run_Hub)
Load * inline
[fsd_City, inTopCity
BENGALURU,1
HYDERABAD,1
CHENNAI,1
MUMBAI,1
NAVI MUMBAI,1
NEW DELHI,1
GURGAON,1
FARIDABAD,1
NOIDA,1
GHAZIABAD,1
PUNE,1
KOLKATA,1
];

left join(run_Hub)
Load * inline
[fsd_City, inTopCityPOS
BENGALURU,1
HYDERABAD,1
CHENNAI,1
MUMBAI,1
NAVI MUMBAI,1
NEW DELHI,1
GURGAON,1
FARIDABAD,1
NOIDA,1
GHAZIABAD,1
PUNE,1
KOLKATA,1

];

left join(run_Hub)
Load * inline
[fsd_City, inSmartProject
BENGALURU,1
HYDERABAD,1
CHENNAI,1
MUMBAI,1
NAVI MUMBAI,1
NEW DELHI,1
GURGAON,1
FARIDABAD,1
NOIDA,1
GHAZIABAD,1
PUNE,1
KOLKATA,1
ALLEPPEY,1
COIMBATORE,1
BELGAUM,1
BELLARY,1
DAVANEGERE,1
COCHIN,1
GULBARGA,1
HOSUR,1
HUBLI,1
JAIPUR,1
KOLLAM,1
KOTTAYAM,1
LUDHIANA,1
MANAGALORE,1
MANIPAL,1
MYSORE,1
PALAKKAD,1
SALEM,1
SURAT,1
NAGPUR,1
];

Run_Hub_Final:
NoConcatenate
Load *
resident run_Hub;
//where fsd_HubType='DELIVERY_HUB';
//
////exit script;
//left join(Run_Hub_Final)
//Load fsd_loc_code,
//     fsd_HubId as fsd_pickupHubId
//resident run_Hub
//where fsd_HubType='PICKUP_HUB';
//exit script;

//left join(run_Hub)
//Load count(DISTINCT(fsd_HubId)) as shareFacilityNo,
//     fsd_loc_code
//resident run_Hub
//Group by  fsd_loc_code;
//
//left join(run_Hub)
//Load fsd_loc_code,
//     fsd_HubId as fsd_pickupHubId
//resident run_Hub
//where fsd_HubType='PICKUP_HUB';
//
//Run_Hub_Final:
//NoConcatenate
//Load *
//Resident run_Hub
//where shareFacilityNo<>2 or UPPER(fsd_HubType)='DELIVERY_HUB';

left keep(Run_Hub_Final)
Run_Hub_Final_LOC:
Load distinct fsd_loc_code,
     fsd_HubId as fsd_deliveryHubId
resident Run_Hub_Final
where fsd_HubType='DELIVERY_HUB';

left join(Run_Hub_Final_LOC)
Load distinct fsd_loc_code,
     fsd_HubId as fsd_pickupHubId
resident Run_Hub_Final
where fsd_HubType='PICKUP_HUB';



drop table run_Hub;

drop table Agents;

//exit script;


//load * inline
//[Control
//1
//2
//3];


///$tab RunSheet
//ODBC CONNECT TO fklogistics;
posDeviceMap:
LOAD posId,
     merchantId as posVendorMid,
     provider as posVendorName ,
     status as posVendorStatus
FROM
[$(qvdPath_fsd)fsd_posDevice.qvd]
(qvd);

smartDeviceMap:
 LOAD id as deviceIdSD,
      imei as imeiSD,
      isActive as isActiveSD,
      isBlocked as isBlockedSD,
      hubId as fsd_HubId,
      imei2 as imei2SD,
      deviceTag as deviceTagSD ,
	  byod as byodSD
  FROM
 [$(qvdPath_fsd)fsd_Devices.qvd](qvd);

left join(smartDeviceMap)
LOAD
     deviceId as deviceIdSD,
     agentId as agentIdSD,
     isActive as isActiveSDMap,
     dayname(updateDateTime) as devUpdateDayName
  FROM
  [$(qvdPath_fsd)fsd_deviceAgentMapping.qvd](qvd);

//
//smartDeviceMap:
//NoConcatenate
//Load * , agentIdSD&devUpdateDayName as key
//resident smartDeviceMap0;




Runsheet1:
//left keep (posDeviceMap)
LOAD distinct
	 runsheetId,
	if(upper(left(vehicleType,5))='ADHOC','ADHOC_VAN', upper(vehicleType)) as vehicleType,
	 createDateTime as runsheetCreateDateTime,
	 startTime as runsheetStartDateTime,
	 updateDateTime as runsheetUpdateDateTime,
	 endTime as runsheetEndDateTime,
     DayName(createDateTime) as runsheetCreateDayName,
     hour(createDateTime) as runsheetCreateHour,
     hour(endTime) as runsheetEndHour,
     hour(startTime) as runsheetStartHour,
     hour(updateDateTime) as runsheetUpdateHour,
     startKm as runsheetStartKM,
     endKm as runsheetEndKM,
   // if (DayName(createDateTime)=DayName(Today(1)-1),1,0) as varForYesterday,
    posId,
    posSerial as posTerminalIDRS

    // startKm,
     //endKm;
FROM
[$(qvdPath_fsd)fsd_runsheet.qvd]
(qvd) where Timestamp(createDateTime) >= Timestamp(today()-$(RELOAD_VOLUME));





//SQL SELECT *
//FROM fklogistics.runsheet where DATE(createDateTime)>date_sub(curdate(),interval 90 day);

//left join(Runsheet1)
//Load date as runsheetCreateDayName,
//	day_date as runsheetCreate_day,
//    week_date as runsheetCreate_week,
//    month_date as runsheetCreate_month,
//    year_date as runsheetCreate_year
//    FROM [$(qvdPath_common)date_calendar.qvd]
//(qvd);


drsDeliveryAttempt:
left keep(Runsheet1)
//left keep(fkl_shipments)
LOAD distinct
	 runsheetId,
     upper(shipmentId) as tracking_id,
     deliveryAttemptDateTime,
     wasDelivered,
     deliveryType as run_deliveryType,
     mobileUpdateDateTime as drsMobileUpdateDateTime,
     hour(mobileUpdateDateTime) as drsMobileUpdateDateTimeHour,
     hour(deliveryAttemptDateTime) as deliveryAttemptHour,
     mobileUpdateUserid as drsMobileUpdateuserid,
     posTransactionId as authCodeRS,
     posTerminalId as posTerminalIdFE,
     dayname(deliveryAttemptDateTime)&posTransactionId&posTerminalId as keyPOS,
     transactionMode as posIntegrationFlag,
     receiverRelation,
     receivedLocation
FROM
[$(qvdPath_fsd)fsd_drsdeliveryattempt.qvd](qvd)
where Timestamp(deliveryAttemptDateTime) >= Timestamp(today()-$(RELOAD_VOLUME));

left join(drsDeliveryAttempt)
LOAD
     upper(shipmentId) as tracking_id,
     preferredDate,
    if (DayName(preferredDate)=DayName(Today(1)-1),1,0) as preferredDateYesterday
    FROM
[$(qvdPath_fsd)fsd_shipmentChangeDeliveryDateMetaData.qvd](qvd)    ;

left join(drsDeliveryAttempt)
LOAD
     upper(shipmentId) as tracking_id,
     //createDateTime,
     //updateDateTime,
     updater
FROM
[$(qvdPath_fsd)fsd_shipmentCSActionFlagMapping.qvd]
(qvd) where isActive=1 and updater='self_serve' and flagId=9;

left join(drsDeliveryAttempt)
LOAD// reconciliationId,
     upper(shipmentId) as tracking_id,
     wasPendingInSystem as wasPendingInSystemDRS,
     wasPendingInHub as wasPendingInHubDRS,
     updateDateTime as updateDateTimePendingDRS,
     dayname(updateDateTime) as updateDatePendingDRS
FROM
[$(qvdPath_fsd)fsd_pendingReconciliation.qvd]
(qvd);

left join(drsDeliveryAttempt)
Load distinct tracking_id,
     cost_of_breach,
     ekl_shipment_type_ekl_db as fsd_shipment_type,
     shipmentChargeableWeightFinal,
     fsd_current_status_desc,
     fsd_current_status_code,
     shipment_value,
     delivery_type,
     fsd_current_status,
     sub_category,
     category,
     product_id ,
     super_category,
	 vertical,
	 anlt_cluster,
	 order_priority_flag
resident shipmentFact;
//FROM
//[$(qvdPath_fkl)fkl_shipment_fact.qvd]
//(qvd)
//where floor(created_at) >= floor(today()-$(RELOAD_VOLUME));



left keep(drsDeliveryAttempt)
MIS:
LOAD Distinct
	 id as bankTransactionId,
     dateOfTransaction,
     merchantId as merchantIdPOS,
     terminalId as posTerminalIdBank,
     authCode as authCodeBank ,
     dayname(dateOfTransaction)&authCode&terminalId as keyPOS,
     //amount,
     status as statusPOSRecon ,
     dayname(updatedAt) as posreconDate,
     if (DayName(updatedAt)=DayName(Today(1)-1),1,0) as posreconDateYesterday,
     amountReconciled,
     //createdAt,
     //updatedAt,
     //uploadedBy,
     bank as bankPOS
FROM
[$(qvdPath_fsd)fsd_posBankTransaction.qvd](qvd);

left join(MIS)
LOAD distinct
	  //id,
     bankTransactionId,
     runsheetId as runsheetIdMIS,
     shipmentId as tracking_idMIS,
     shipmentAmount,
     amount
FROM
[$(qvdPath_fsd)fsd_posReconciliation.qvd](qvd);

left join(drsDeliveryAttempt)
Load keyPOS,
     statusPOSRecon as statusPOSReconERP
resident MIS;

left join (Runsheet1)
LOAD distinct
	 runsheetId,
     min(time(deliveryAttemptDateTime)) as runsheetfirstDeliveredTime,
     max(time(deliveryAttemptDateTime)) as runsheetlastDeliveredTime,
     //max(time(deliveryAttemptDateTime))- runsheetStartDateTime  as runsheetStartTolLastDeliveredTime,
     //runsheetEndDateTime - runsheetStartDateTime as runsheetStartToEndDateTime,
     //max(time(deliveryAttemptDateTime)) - min(time(deliveryAttemptDateTime)) as runsheetFirstToLastDeliveredTime,
     if(count(if(drsMobileUpdateDateTime<>null() and upper(left(drsMobileUpdateuserid,6))='MOBILE',drsMobileUpdateDateTime))>0,'Device','Manual') as runsheetType,

     if(sum(if(run_deliveryType='POS' or (run_deliveryType='COD' and upper(posIntegrationFlag)='PINELAB'),1))>0,'POS','NonPOS') as runsheetPOSType,
     //count( distinct if (wasDelivered=1 and runsheetCreateDayName=DayName(deliveryAttemptDateTime),tracking_id)) as shipmentsDelivered,
     //count( distinct if (runsheetCreateDayName=DayName(deliveryAttemptDateTime),tracking_id)) as shipmentsAttempted
     count( distinct if (wasDelivered=1 ,tracking_id)) as shipmentsDelivered,
     count( distinct if (wasDelivered=1 and upper(delivery_type)<>'SMALL',tracking_id)) as shipmentsDeliveredVol,
     count( distinct (tracking_id)) as shipmentsAttempted
     resident drsDeliveryAttempt
     Group by runsheetId  ;

left join(Runsheet1)
LOAD distinct runsheetId,
     agentId as runAgentId
FROM
[$(qvdPath_fsd)fsd_runsheettoagent.qvd]
(qvd);

left join(Runsheet1)
LOAD distinct runAgentId,
     FHRId
Resident Run_Agents;

left join(Runsheet1)
 LOAD //id as deviceIdSD,
	    imeiSD as imeiSDRS,
//      isActive as isActiveSD,
//      isBlocked as isBlockedSD,
//      hubId as fsd_HubId,
//      imei2 as imei2SD,
  //    deviceTag as deviceTagSD ,
	    byodSD as byodSDRS,
	    agentIdSD as runAgentId,
	    devUpdateDayName
resident smartDeviceMap where isActiveSD=1 and isActiveSDMap=1;

left keep(Run_Agents_Date)
Runsheet:
//NoConcatenate
Load distinct
runsheetId,
imeiSDRS,
byodSDRS,
vehicleType,
runsheetCreateDateTime,
runsheetStartDateTime,
runsheetUpdateDateTime,
runsheetEndHour,
runsheetCreateHour,
runsheetStartHour,
runsheetUpdateHour,
runsheetEndDateTime,
runsheetCreateDayName,
//varForYesterday,
posId,
posTerminalIDRS,
runsheetfirstDeliveredTime,
runsheetlastDeliveredTime,
runsheetType,
runsheetPOSType,
shipmentsDelivered,
shipmentsDeliveredVol,
shipmentsAttempted   ,
FHRId&runsheetCreateDayName as key,
FHRId&runsheetCreateDayName as keyRS,
runAgentId&runsheetCreateDayName as key10,
runsheetlastDeliveredTime - runsheetStartDateTime  as runsheetStartTolLastDeliveredTime,
runsheetEndDateTime - runsheetStartDateTime as runsheetStartToEndDateTime,
runsheetlastDeliveredTime - runsheetfirstDeliveredTime as runsheetFirstToLastDeliveredTime,
devUpdateDayName as devUpdateDayNameRS,
runsheetStartKM,
runsheetEndKM
Resident Runsheet1 ;
//where devUpdateDayName<=runsheetCreateDayName or IsNull(devUpdateDayName)

left join(Runsheet)
LOAD distinct runsheetId,
     expectedAmount,
     posAmount,
     lostAmount,
     collectedAmount,
     unreconciledAmount,
     //remarks,
     status as statuscashRecon,
     updateTimeStamp as updateTimeStampCashRecon,
     dayname(updateTimeStamp) as cashReconDayName
FROM
[$(qvdPath_fsd)fsd_runsheetCashierInfo.qvd]
(qvd);

DROP Table Runsheet1;



//exit script;

DevAuthCodeInfo:
Load *
FROM
[$(qvdPath_fsd)fsd_deviceAuthCodeInfo.qvd](qvd);
//where isActive=1;

DevAuthCodeInfo1:
NoConcatenate
Load distinct agentId as runAgentId,
     isActive,
     authCode,
     dayname(expiryDate) as deviceExpDate,
     dayname(createDateTime) as devCreateDate,
     dayname(updateDateTime) as devUpdateDate,
     agentId&dayname(createDateTime)as key10
resident DevAuthCodeInfo;

DevAuthCodeInfo2:
NoConcatenate
Load distinct key10,
     if(count(authCode)>=1,1,0) as isDeviceActive
resident DevAuthCodeInfo1
group by key10 ;

left join(Runsheet)
Load distinct key10,
	 isDeviceActive
	 //deviceAuthCode
Resident DevAuthCodeInfo2;

left join(Runsheet)
Load distinct key10,
	 LastValue(authCode) as authCode
	 //deviceAuthCode
Resident DevAuthCodeInfo1 group by key10 order by authCode desc ;

//exit script;
left join(Runsheet)
LOAD distinct
	 runsheetId,
     name,
     replace(upper(value),' ','') as vehicleNo
     //createDateTime,
     //updateDateTime
FROM
[$(qvdPath_fsd)fsd_runsheet_Attribute.qvd](qvd);

drop table DevAuthCodeInfo;
drop table DevAuthCodeInfo1;
drop table DevAuthCodeInfo2;

//left keep (Runsheet)
//LOAD posId,
//     merchantId as posVendorMid,
//     provider as posVendorName
//FROM
//[$(qvdPath_fsd)fsd_posDevice.qvd]
//(qvd);
//exit script;
///$tab Reverse Pickup

PickupsheetFact1:
LOAD distinct pickupSheetId,
    dayname(updateDateTime) as pickupSheetUpDateDayName ,
     isActive
//     sentForPickup,
//     startKm,
//     endKm,
//     startTime,
//     endTime,
//    updateDateTime as pickupSheetUpDateDateTime
//    hour(updateDateTime) as pickupSheetUpDateHour
//     requestId
FROM
[$(qvdPath_fkl)fkl_pickup_sheet.qvd](qvd)
where Timestamp(updateDateTime) >= Timestamp(today()-$(RELOAD_VOLUME));

left join(PickupsheetFact1)
LOAD distinct mappingId,
     pickupSheetId,
     agentId as runAgentPickupId
  //   agentId&pickupSheetUpDateDayName as key
//     updateDateTime
FROM
[$(qvdPath_fkl)fkl_pickup_sheet_agent_mapping.qvd]
(qvd);

left join(PickupsheetFact1)
LOAD
     pickupSheetId,
    //name,
     if(upper(left(type,5))='ADHOC','ADHOC_VAN', upper(type)) as rvp_vehicle_type,
     replace(upper(value),' ','') as  rvp_vehicle_no

FROM
[$(qvdPath_fkl)fkl_pickupSheetAttributes.qvd]
(qvd);

left join(PickupsheetFact1)
LOAD distinct runAgentId as runAgentPickupId,
     FHRId as FHRIdPickup
Resident Run_Agents;

left keep (PickupsheetFact1)
PickupDetails:
LOAD distinct pickupAttemptId,
     shipmentId as rev_shipmentid,
    // requestId,
     pickupSheetId,
  customerId as pickupcustomerId,
    attemptTime as pickupAttemptTime,
 //    attemptCode,
 //    actionTaken,
  //   idProof,
  //   note,
     updateTimeStamp as pickupupdateTimeStamp
FROM
[$(qvdPath_fkl)fkl_pickup_attempt.qvd]
(qvd);


left join(PickupDetails)
LOAD distinct pickupId as rev_pickupid,
	shipmentId as rev_shipmentid,
//	orderId as order_id,
//	reverseId as rev_order_id,
	ApplyMap('fsd_status_mapping',statusId, null()) as rev_current_status ,
//	if(statusId=44,'PickupComplete','PickupNotComplete') as rev_pickup_status,
    if(statusId=44,1,0) as wasPicked,
//	if(statusId<>29 and statusId<>22 and statusId<>66 and statusId<>49 and statusId<>68 and statusId<>44 and statusId<>61 and statusId<>3
//		,'Pickup_pending' ,'Pickup_not_pending') as rev_pending_flag,
////'Cancelled','Lost','PICKUP_Cancelled','PICKUP_NotPicked_Attempted_CustomerRefused','PICKUP_Picked_Complete','Received','PICKUP_Picked_Partial'
//	itemCount as rev_qnty,
//	scheduleDateTime as rev_sheduled_datetime,
    DayName(scheduleDateTime) as rev_sheduled_date,
//	ApplyMap('day_map',Dayname(scheduleDateTime), null()) as rev_shedule_date_indays,
//	destinationWarehouse as rev_destination_fc,
//	pickupHubId as rev_pickup_hubid,
//	ApplyMap('hubid_hubname_mapping',pickupHubId,null()) as rev_hub_name,
//	ApplyMap('hub_zone_map',ApplyMap('hubid_hubname_mapping',pickupHubId,null()),null()) as rev_hub_zone,
//	ApplyMap('hub_city_map',ApplyMap('hubid_hubname_mapping',pickupHubId,null()),null()) as rev_hub_city,
//	ApplyMap('day_map',Dayname(Today()-1), null()) as today_indays,
//	(ApplyMap('day_map',Dayname(Today()-1), null())-ApplyMap('day_map',Dayname(scheduleDateTime), null())) as rev_pending_indays,
   reverseType as rev_type,
   mobileUpdateUserid,
   mobileUpdateDateTime,
   updateDateTime as revPickupUpdateDateTime,
   dayname(updateDateTime) as revPickupUpdateDateDayName
FROM
[$(qvdPath_fkl)fkl_pickup_event.qvd]
(qvd);
//where Timestamp(deliveryAttemptDateTime) >= Timestamp(today()-60);

left keep(Run_Agents_Date)
PickupsheetFact:
//NoConcatenate
Load *,
//if (pickupSheetUpDateDayName=shipmentPickupUpdateDayName and rev_pickup_status='PickupComplete',1,0) as wasPicked,
//if (rev_pickup_status='PickupComplete',1,0) as wasPicked,
FHRIdPickup&pickupSheetUpDateDayName as key,
FHRIdPickup&pickupSheetUpDateDayName as keyPS
resident PickupsheetFact1;

PickupDetails1:
NoConcatenate
Load *
resident PickupDetails;

left join(PickupDetails1)
Load distinct pickupSheetId,
   	 pickupSheetUpDateDayName
Resident PickupsheetFact;


left join(PickupsheetFact)
Load distinct pickupSheetId,
     //count(distinct(if(wasPicked=1 and DayName(revPickupUpdateDateTime)=pickupSheetUpDateDayName,rev_shipmentid))) as sumRVPPicked,
     //count(distinct(if(DayName(revPickupUpdateDateTime)=pickupSheetUpDateDayName,rev_shipmentid))) as sumRVPPickedAttempt
     count(distinct(if(wasPicked=1 and upper(rev_type)='PICKUP_ONLY' and revPickupUpdateDateDayName=pickupSheetUpDateDayName,rev_shipmentid))) as sumRVPPicked,
     count(distinct(if(upper(rev_type)='PICKUP_ONLY' and revPickupUpdateDateDayName=pickupSheetUpDateDayName,rev_shipmentid))) as sumRVPPickedAttempt
resident  PickupDetails1
group by  pickupSheetId;

drop table PickupDetails1;

drop table PickupsheetFact1;


//exit script;
///$tab MP
left keep(Run_Agents_Date)
MPSheetFact1:
LOAD distinct
     pickup_sheet_id as mp_pickup_sheet_id,
     pickup_request_id as mp_pickup_request_id,
     //in_time,
    // out_time,
     //created_by,
     //updated_by,
     dayname(created_at) as mp_sheet_createday
     //if (DayName(created_at)=DayName(Today(1)-1),1,0) as mp_varForYesterday
     //updated_at
     //weight,
     //remarks,
     //notes
FROM
[$(qvdPath_proc)proc_pr_pickup_attempts.qvd]
(qvd)
where Timestamp(updated_at) >= Timestamp(today()-$(RELOAD_VOLUME));

left keep(MPSheetFact1)
MPdetails:
LOAD distinct id as mp_pickup_request_id,
     tracking_id as mp_tracking_id ,
    // merchant_id,
     //supplier_location_id,
     procurement_facility_id as mp_procurement_facility_id,
   //  fkl_facility_id,
     //route_id,
     status as mp_status,
     if(status='received_at_tc' or status='received_at_origin' or status='dispatched_to_tc' or status='pickup_complete',1,0) as wasMPPicked,
  // if( status='received_at_origin' or status='pickup_complete',1,0) as wasMPPicked,
     requested_date as mp_requested_date,
     pickup_date as mp_pickup_date
     //procurement_type,
     //priority,
    // remarks,
     //merchant_reference_id,
     //supplier_reference_id,
     //created_by,
     //updated_by,
    // created_at,
     //updated_at
     //is_active_in_ps
FROM
[$(qvdPath_proc)proc_pickup_requests.qvd]
(qvd)
where Timestamp(updated_at) >= Timestamp(today()-$(RELOAD_VOLUME));

left join(MPdetails)
Load distinct mp_pickup_sheet_id,
     mp_pickup_request_id
resident MPSheetFact1;



left join(MPSheetFact1)
LOAD distinct id as mp_pickup_sheet_id,
     //status,
     vehicle_id as mp_vehicle_id,
     //field_executive_id as runAgentId,
      field_executive_id,
     //start_distance,
     //end_distance,
     //created_by,
     //updated_by,
     //created_at,
     updated_at as mp_sheet_updateDateTime
     //notes,
//     procurement_facility_id
FROM
[$(qvdPath_proc)proc_pickup_sheets.qvd](qvd)
where status <>'cancelled';

left join(MPSheetFact1)
LOAD distinct runAgentId as field_executive_id,
     FHRId as FHRIdMP
Resident Run_Agents;

left join(MPSheetFact1)
LOAD distinct id as mp_vehicle_id,
     if(upper(left(type,5))='ADHOC','ADHOC_VAN', upper(type)) as mp_vehicle_type
FROM
[$(qvdPath_ekl_facility)ekl_facilities_vehicles.qvd]
(qvd);


//left join(MP)
//LOAD //id as mp_field_executive_id,
//     id as runAgentId,
//     //id&mp_sheet_createday as key,
//     //employee_id as mp_employee_id,
//     //first_name as mp_agent_name,
//     display_name as mp_agent_name,
//     Right(display_name,len(display_name)-Index(display_name,'_',-1)) as mp_FHRId
//FROM
//[$(qvdPath_ekl_facility)ekl_facilities_agents.qvd]
//(qvd);

MPSheetFact:
NoConcatenate
Load  distinct  mp_pickup_sheet_id,
    // mp_pickup_request_id,
      mp_sheet_createday,
      field_executive_id,
      mp_sheet_updateDateTime,
      FHRIdMP,
      mp_vehicle_id,
     // mp_vehicle_type ,
      FHRIdMP&mp_sheet_createday as key,
      FHRIdMP&mp_sheet_createday as keyMP
Resident MPSheetFact1;

left join(MPSheetFact)
LOAD //id,
     pickup_sheet_id as mp_pickup_sheet_id ,
     //value as mp_vehicle_type
     if(upper(left(value,5))='ADHOC','ADHOC_VAN', upper(value)) as mp_vehicle_type
  FROM
[$(qvdPath_proc)proc_pickupSheetAttributes.qvd](qvd)
where name ='vehicle_type';

left join(MPSheetFact)
LOAD //id,
     pickup_sheet_id as mp_pickup_sheet_id ,
     replace(upper(value),' ','') as mp_vehicle_no
  FROM
[$(qvdPath_proc)proc_pickupSheetAttributes.qvd](qvd)
where name ='vehicle_number';


//left join(MPSheetFact2)
//Load
//	 mp_pickup_request_id,
//     mp_tracking_id,
//     wasMPPicked
//resident MPdetails;
//group by  mp_pickup_request_id;

left join(MPSheetFact)
//NoConcatenate
Load Distinct
	 mp_pickup_sheet_id,
    // mp_pickup_request_id,
     count(DISTINCT(if(wasMPPicked=1 ,mp_tracking_id))) as sumMPPicked,
     count(DISTINCT(mp_tracking_id)) as sumMPPickedAttempt
resident MPdetails
group by mp_pickup_sheet_id;


drop table MPSheetFact1;
//drop table MPSheetFact2;

//exit script;

MPDetails1:
NoConcatenate
Load  Distinct
      mp_tracking_id ,
      mp_procurement_facility_id,
   	  mp_status,
      wasMPPicked,
      mp_requested_date,
      mp_pickup_date,
      mp_pickup_sheet_id
resident  MPdetails;

drop table MPdetails;
//left keep(MPSheetFact)
//MP_Hub:
//LOAD distinct id as mp_procurement_facility_id,
//     display_name as mp_procurement_hub
//FROM
//[$(qvdPath_ekl_facility)ekl_facilities_facilities.qvd]
//(qvd)
//where type='PICKUP_HUB';
//
//left join(MP_Hub)
//LOAD
//	 Destination as  mp_procurement_hub,
//     [Destination City] as mp_procurement_City,
//     [Ops Zone] as mp_procurement_Zone
//FROM
//[$(qvdPath_excel)Final Map v3.xlsx]
//(ooxml, embedded labels, table is Route);
//
//left join(MP_Hub)
//LOAD //[Hub Id] as fsd_HubId,
//     //[Hub Name],
//     [MP Vans] as Vans_MP_Fixed,
//     Proc_Facility_Id as mp_procurement_facility_id
//FROM
//[$(qvdPath_excel)MP_Vans_Hub.xlsx]
//(ooxml, embedded labels, table is Sheet1);

store MPSheetFact into [$(qvdPath_fsd)fsd_MPSheet_Fact.qvd](qvd);

//exit script;

Run_Agents1:
NoConcatenate
Load Distinct
     FHRId,
	 //runAgentName,
	 isFEActive,
	 isFAMAcitve,
	 runAgentNameFinal,
	 FEJoiningDate,
     fsd_loc_code as fsd_loc_code_ra,
     fsd_HubId
resident  Run_Agents;

drop table Run_Agents;



//left join(Run_Agents_Date)
////NoConcatenate
//Load distinct keyRS as key,
//	 sum(shipmentsDelivered) as shipmentsDeliveredAgents ,
//     sum(shipmentsAttempted) as shipmentsAttemptedAgents
//resident Runsheet
//group by keyRS ;
//
//left join(Run_Agents_Date)
//Load distinct keyPS as key,
//	 sum(sumRVPPicked) as RVPPickedAgents,
//	 sum(sumRVPPickedAttempt) as RVPPickedAttemptAgents
//resident PickupsheetFact
//group by keyPS ;
//
//left join(Run_Agents_Date)
//Load distinct keyMP as key,
//	 sum(sumMPPicked) as MPPickedAgents,
//     sum(sumMPPickedAttempt) as MPPickedAttemptAgents
//resident   MPSheetFact
//group by keyMP ;


///$tab Van
Van_Hub:
left keep(Run_Hub_Final)
LOAD
     facility_id as fsd_HubId,
     vehicle_id
//     active,
//     updated_at,
//     created_at
FROM
[$(qvdPath_ekl_facility)ekl_facilities_facility_vehicles.qvd](qvd)
where active=1;

left join(Van_Hub)
LOAD id as vehicle_id,
     number as vehicleNoHub,
     type as vehicleTypeHub
  //   active as vehicleActiveHub
//     updated_at ,
//     created_at,
//     display_name
FROM
[$(qvdPath_ekl_facility)ekl_facilities_vehicles.qvd](qvd)
where active =1 and type='van' ;



Van_Agent_Date1:
Load key,
     vehicleNo as vehicleNoCalc,
     if(upper(vehicleType)='VAN','VAN','ADHOC_VAN') as vehicleTypeCalc,
     'DELIVERY' as vehicleModeCalc,
     vehicleNo&runsheetCreateDayName as keyVehicle
resident Runsheet
where upper(vehicleType)='VAN' or upper(vehicleType)='ADHOC_VAN';

Concatenate(Van_Agent_Date1)
Load key,
     mp_vehicle_no as vehicleNoCalc,
     if(upper(mp_vehicle_type)='VAN','VAN','ADHOC_VAN') as vehicleTypeCalc,
     'MP' as vehicleModeCalc,
     mp_vehicle_no&mp_sheet_createday as keyVehicle
resident MPSheetFact
where upper(mp_vehicle_type)='VAN' or upper(mp_vehicle_type)='ADHOC VAN';

Concatenate(Van_Agent_Date1)
Load key,
     rvp_vehicle_no as vehicleNoCalc,
     if(upper(rvp_vehicle_type)='VAN','VAN','ADHOC_VAN') as vehicleTypeCalc,
     'RVP' as vehicleModeCalc,
      rvp_vehicle_no&pickupSheetUpDateDayName as keyVehicle
resident PickupsheetFact
where upper(rvp_vehicle_type)='VAN' or upper(rvp_vehicle_type)='ADHOC_VAN';

Van_Agent_Date:
left keep(Run_Agents_Date)
Load distinct *
Resident Van_Agent_Date1
where not isnull(vehicleNoCalc);

Run_Agents_Date3:
NoConcatenate
Load *
Resident Run_Agents_Date;

//exit script;

left join(Run_Agents_Date3)
Load distinct key,
	 //vehicleType,
     sum(shipmentsDelivered) as shipmentsDeliveredTotalBike,
     sum(shipmentsAttempted) as shipmentsAttemptedTotalBike
Resident Runsheet
where upper(vehicleType)='BIKE'
group by key;

left join(Run_Agents_Date3)
Load distinct key,
	 //vehicleType,
     sum(shipmentsDelivered) as shipmentsDeliveredTotalVan,
     sum(shipmentsAttempted) as shipmentsAttemptedTotalVan
Resident Runsheet
where upper(right(vehicleType,3))='VAN'
group by key;

left join(Run_Agents_Date3)
Load distinct key,
     sum(sumMPPicked)/2 as MPPickedTotalBike,
     sum(sumMPPickedAttempt)/4 as MPAttemptedTotalBike
     //mp_vehicle_type
Resident MPSheetFact
where upper(mp_vehicle_type)='BIKE'
group by key;

left join(Run_Agents_Date3)
Load distinct key,
     sum(sumMPPicked)/4 as MPPickedTotalVan,
     sum(sumMPPickedAttempt)/4 as MPAttemptedTotalVan
     //mp_vehicle_type
Resident MPSheetFact
where upper(right(mp_vehicle_type,3))='VAN'
group by key;


left join(Run_Agents_Date3)
Load distinct key,
     sum(sumRVPPicked) as RVPPickedTotalBike,
     sum(sumRVPPickedAttempt) as RVPAttemptedTotalBike
    // rvp_vehicle_type
Resident PickupsheetFact
where upper(rvp_vehicle_type)='BIKE'
group by key;

left join(Run_Agents_Date3)
Load distinct key,
     sum(sumRVPPicked) as RVPPickedTotalVan,
     sum(sumRVPPickedAttempt) as RVPAttemptedTotalVan
    // rvp_vehicle_type
Resident PickupsheetFact
where upper(right(rvp_vehicle_type,3))='VAN'
group by key;

Run_Agents_Date4:
Load distinct key,
     if(shipmentsDeliveredTotalBike>0,shipmentsDeliveredTotalBike,0) as shipmentsDeliveredTotalBike,
     if(shipmentsDeliveredTotalVan>0,shipmentsDeliveredTotalVan,0) as shipmentsDeliveredTotalVan,
     if(MPPickedTotalBike>0,MPPickedTotalBike,0) as MPPickedTotalBike,
     if(MPPickedTotalVan>0,MPPickedTotalVan,0) as MPPickedTotalVan,
     if(RVPPickedTotalBike>0,RVPPickedTotalBike,0) as RVPPickedTotalBike,
     if(RVPPickedTotalVan>0,RVPPickedTotalVan,0) as RVPPickedTotalVan,

     if(shipmentsAttemptedTotalBike>0,shipmentsAttemptedTotalBike,0) as shipmentsAttemptedTotalBike,
     if(shipmentsAttemptedTotalVan>0,shipmentsAttemptedTotalVan,0) as shipmentsAttemptedTotalVan,
     if(MPAttemptedTotalBike>0,MPAttemptedTotalBike,0) as MPAttemptedTotalBike,
     if(MPAttemptedTotalVan>0,MPAttemptedTotalVan,0) as MPAttemptedTotalVan,
     if(RVPAttemptedTotalBike>0,RVPAttemptedTotalBike,0) as RVPAttemptedTotalBike,
     if(RVPAttemptedTotalVan>0,RVPAttemptedTotalVan,0) as RVPAttemptedTotalVan
Resident Run_Agents_Date3;

left join(Run_Agents_Date)
Load distinct key,
     shipmentsDeliveredTotalBike,
     shipmentsDeliveredTotalVan,
     MPPickedTotalBike,
     MPPickedTotalVan,
     RVPPickedTotalBike,
     RVPPickedTotalVan,
     ((shipmentsDeliveredTotalBike + RVPPickedTotalBike + MPPickedTotalBike)
     	/(shipmentsDeliveredTotalBike + RVPPickedTotalBike + MPPickedTotalBike + RVPPickedTotalVan + MPPickedTotalVan + shipmentsDeliveredTotalVan)) as bikeDay,
     (RVPPickedTotalVan + MPPickedTotalVan + shipmentsDeliveredTotalVan)
     	/(shipmentsDeliveredTotalBike + RVPPickedTotalBike + MPPickedTotalBike + RVPPickedTotalVan + MPPickedTotalVan + shipmentsDeliveredTotalVan) as vanDay,

    shipmentsDeliveredTotalBike + RVPPickedTotalBike + MPPickedTotalBike + shipmentsDeliveredTotalVan + RVPPickedTotalVan + MPPickedTotalVan as dailyOverAllProd,

    shipmentsDeliveredTotalBike + RVPPickedTotalBike + shipmentsDeliveredTotalVan + RVPPickedTotalVan  as dailyOverAllDelProd,

    shipmentsAttemptedTotalBike + shipmentsAttemptedTotalVan + RVPAttemptedTotalBike + RVPAttemptedTotalVan as dailyOverAllAttemptedDelProd

resident  Run_Agents_Date4;

//left join(Run_Agents1)
//Load distinct left(key,len(key)-10) as FHRId,
//     if (sum(dailyOverAllProd)>0,1,0) as isFEWorkingActive
//resident Run_Agents_Date
//where Dayname(right(key,10))>=Today()-5
//group by left(key,len(key)-10);


left join(Run_Agents1)
Load distinct  FHRId,
     if (sum(dailyOverAllProd)>0,1,0) as isFEWorkingActive
resident Run_Agents_Date
where Date>=Today()-5
group by FHRId;



drop table Run_Agents_Date3;

drop table Run_Agents_Date4;

drop table Van_Agent_Date1;

//exit script;
///$tab FKL
//exit script;
shipmentReceiveDetails:
left keep(Run_Hub_Final)
//left keep(DayDetails)
LOAD distinct tracking_id as tracking_idRec,
      fsd_last_dh_id as   fsd_HubId,
    fsd_last_dh_receive_date as DHReceiveDate,
    fsd_last_dh_receive_datetime as DHReceiveDateTime ,
    hour(fsd_last_dh_receive_datetime) as DHReceiveHour,
    if (DayName(fsd_last_dh_receive_datetime)=DayName(Today(1)-1),1,0) as DateRecYesterday,
    fsd_last_dh_receive_adjusted_date as DHReceiveAdjDate,
   ekl_delivery_indays_adjusted,
    Dh_delivery_indays,
    fsd_last_dh_receive_adjusted_date,
    ekl_delivery_date,
	if(today()=WeekStart(today()) or today()=WeekStart(today())+1,
	 if(fsd_last_dh_receive_adjusted_date>=WeekStart(today(),0,-2)-5 and fsd_last_dh_receive_adjusted_date<=WeekStart(today(),0,-2)+1,'Todays Dash','Countout'),
	 if(fsd_last_dh_receive_adjusted_date>=WeekStart(today()) and fsd_last_dh_receive_adjusted_date<=today(),'Todays Dash','Countout')) as DH_Dash_Date_Range,
      carrier,
     If((fsd_current_status='Lost' or fsd_current_status='Untraceable'),'Lost_Damaged',
	 	if(fsd_shipment_flag='RTO' and (fsd_last_hub_type<>'DELIVERY_HUB'),'Incoming_RTO',
	 		ekl_shipment_type_ekl_db)) as ekl_shipment_type,
	 cost_of_breach as cost_of_breach_receive,
	 holiday_dh_date as holiday_dh_date_receive,
	 holiday_dh_flag as holiday_dh_flag_receive,
	 fsd_consignment_receive_datetime,
	 fsd_shipment_last_bag_receive_datetime,
//	 recToOFDTIme	,
	 fsd_consignment_receive_datetime-fsd_shipment_last_bag_receive_datetime as consignmentRecToBagReceiveTime,
	 fsd_last_OFD_datetime-fsd_last_dh_receive_datetime as recToOFDTime,
	 fsd_last_dh_receive_datetime-fsd_shipment_last_bag_receive_datetime as bagRecToDHReceiveTime
//     shipment_idealBagCloseTime_60
Resident shipmentFact;

//FROM
//[C:\QlikviewFiles\Data\QV-erp\Data\fsd\fsd_shipment_fact.qvd](qvd)
//where Timestamp(fsd_first_DH_received_datetime) >= Timestamp(today()-$(RELOAD_VOLUME));


drop table shipmentFact;

///$tab Cost
//trial1:
//Load distinct vendor_tracking_id as tracking_id,
//     merchant_reference_id as merchant_reference_idShip,
//	 weight,
//     payment_type,
//     merchant_code,
//     shipment_type as shipment_typeShip,
//     dayname(fkl_delivery_datetime) as fkl_delivery_dayname,
//     dayname(RTO|RVP_received_datetime) as RTORVP_receive_dayname,
//     carrier as carrierShip
//     //trim(upper(text(MonthName(fkl_delivery_datetime)))) as fkl_delivery_month
//FROM
//[$(qvdPath_fkl)fkl_shipment_fact.qvd]
//(qvd)
//where Timestamp(created_at) >= Timestamp(today()-90)
//and carrier='FSD';
//
//left join(trial1)
//Load
//distinct unit_tracking_id as tracking_id,
////Item_amount,
//order_item_id,
//product_id,
//unit_quantity,
//final_adjusted_amount as order_item_value,
//unit_status
////unit_tracking_id&order_item_id as keyShip
////sku
//FROM
//[$(qvdPath_common)Fact_table.qvd]
//(qvd)
//where Timestamp(dispatch_date) >=  Timestamp(today()-90);
//
//trial2:
//NoConcatenate
//Load tracking_id, runsheetId
//resident drsDeliveryAttempt;
//
//left join(trial2)
//Load runsheetId,
// vehicleType as vehicleTypeShip,
// vehicleNo as vehicleNoShip
// //MonthName(runsheetCreateDateTime) as monthRunsheetCreate
//Resident Runsheet;
//
////left keep(fkl_shipments)
//shipment_details1:
//Load distinct tracking_id,
//     merchant_reference_idShip,
//     order_item_id,
//     product_id,
//     weight as shipmentWeight,
//     unit_status,
//     unit_quantity,
//     fkl_delivery_dayname,
//     RTORVP_receive_dayname,
//     if((shipment_typeShip='RTO' or shipment_typeShip='RVP'),RTORVP_receive_dayname,fkl_delivery_dayname) as fkl_action_dayname,
//    // fkl_delivery_month,
//     payment_type as shipmentPaymentType,
//     order_item_value,
//     merchant_code as merchant_codeShip,
//     shipment_typeShip,
//     carrierShip
//    // keyShip
//resident trial1;
////group by tracking_id, order_item_id,product_id;
//
//left join(shipment_details1)
//Load date as fkl_action_dayname,
//	 trim(upper(month_date)) as fkl_action_month
//   FROM [$(qvdPath_common)date_calendar.qvd]
//(qvd);
//
//left join(shipment_details1)
//Load tracking_id as tracking_id,vehicleTypeShip,vehicleNoShip
//resident trial2;
//
//drop table trial1;
//drop table trial2;
//
//
////LOAD
////     distinct order_item_id&tracking_id as keyShip,
////     quantity_rvp_done,
////     MonthName(updated_at) as monthRVP
////FROM
////[\QlikviewFiles\Data\QV-erp\Data\flo_oms_b2c\flo_oms_b2c_return_items.qvd]
////(qvd);
//left join(shipment_details1)
//Load
//distinct tracking_id,
//	    order_item_id  as order_item_id_rvp,
//     quantity_rvp_done
////     MonthName(updated_at) as monthRVP
//FROM
//[$(qvdPath_flo_oms_b2c)flo_oms_b2c_return_items.qvd]
//(qvd);
//
//left join(shipment_details1)
//Load
//distinct order_item_id as order_item_id_rvp,
//         product_id as  product_id_rvp
////     MonthName(updated_at) as monthRVP
//from
//[$(qvdPath_common)Fact_table.qvd](qvd)
//where Timestamp(dispatch_date) >=  Timestamp(today()-90);
//
//shipment_details:
//NoConcatenate
//Load * ,
//     if(order_item_id<>null(),order_item_id,order_item_id_rvp) as order_item_id_final,
//     if(product_id<>null(),product_id,product_id_rvp) as product_id_final,
//     if(order_item_id<>null(),tracking_id&order_item_id,tracking_id&order_item_id_rvp) as keyShip
//resident shipment_details1;
//
//drop table shipment_details1;
//
//left join(shipment_details)
//LOAD
//     distinct keyShip,
//      if(shipment_typeShip='RVP',quantity_rvp_done,unit_quantity) as unit_quantity_shipments
//resident shipment_details;
//
//left join(shipment_details)
////NoConcatenate
//Load  distinct tracking_id,
//     sum(unit_quantity_shipments) as shipments_quantity_total
//resident shipment_details
//group by tracking_id;
//
//left join(shipment_details)
//Load
//distinct product_id as product_id_final ,
//title_product_info,
//sub_category,
//category
//FROM
//[$(qvdPath_common)Product_InfoMP.qvd]
//(qvd);
//
////left join(shipment_details)
////Load distinct tracking_id,
////	 merchant_reference_id as merchant_reference_id_sd
////resident fkl_shipments;
//
//left join(shipment_details)
//LOAD Distinct id as merchant_reference_idShip,
//     used_packing_box_id as warehouse_packing_box_id
//FROM
//[$(qvdPath_flo_warehouse_b2c)flo_warehouse_b2c_shipments.qvd]
//(qvd);
//
//left join(shipment_details)
//LOAD Distinct id as warehouse_packing_box_id,
//     name as warehouse_pb_name
//FROM
//[$(qvdPath_flo_warehouse_b2c)flo_warehouse_b2c_packing_boxes.qvd]
//(qvd);
//
//
//left join(shipment_details)
//LOAD Distinct id as warehouse_packing_box_id,
//     length as warehouse_pb_length,
//     breadth as warehouse_pb_breadth,
//     height as warehouse_pb_height,
//     (length*breadth*height)/366 as shipment_volumetricWeight
//FROM
//[$(qvdPath_flo_warehouse_b2c)flo_warehouse_b2c_packing_boxes.qvd]
//(qvd);
//
//
//left join(shipment_details)
//LOAD Distinct FSN_shipping_info_fsn as product_id_final,
//     FSN_shipping_info_fsnAvgWeight,
//     FSN_shipping_info_fsnAvgPackedVolume,
//     FSN_shipping_info_fsnAvgPackedVolume/366 as FSN_volWeight
//FROM
//[$(qvdPath_fsd)fsd_temp_FSN_shipping_info_Nir.qvd]
//(qvd);
//
//left join(shipment_details)
//Load Distinct product_id_final,
//      if(FSN_volWeight>FSN_shipping_info_fsnAvgWeight,FSN_volWeight,FSN_shipping_info_fsnAvgWeight) as FSNchargeableWeight
//Resident shipment_details;
//
//left join(shipment_details)
//Load Distinct keyShip,
//      FSNchargeableWeight*unit_quantity_shipments as FSNchargeableWeightOrder
//Resident shipment_details;
//
////left join(shipment_details)
////Load Distinct product_id,
////      if(isnull(chargeableWeight),0.5,chargeableWeight) as FSNchargeableWeightFinal
////Resident shipment_details;
//
//left join(shipment_details)
//Load Distinct tracking_id,
//      if(shipmentWeight>shipment_volumetricWeight,shipmentWeight,shipment_volumetricWeight) as shipmentChargeableWeightRaw
//Resident shipment_details;
//
//left join(shipment_details)
//Load Distinct tracking_id,
//      if(shipmentChargeableWeightRaw>20,0.5,shipmentChargeableWeightRaw) as shipmentChargeableWeight
//Resident shipment_details;
//
//left join(shipment_details)
//Load Distinct tracking_id,
//      if(isnull(shipmentChargeableWeight),0.5,shipmentChargeableWeight) as shipmentChargeableWeightFinal
//Resident shipment_details;
//
//left join(shipment_details)
//Load distinct keyShip,
//     unit_quantity_shipments/shipments_quantity_total as shipment_ratio
//resident shipment_details;
//
//left join(shipment_details)
////NoConcatenate
//Load distinct tracking_id,
//     sum(FSNchargeableWeightOrder) as FSNchargeableWeightTotal
//resident shipment_details
//group by tracking_id;
//
//left join(shipment_details)
//Load distinct keyShip,
//     (FSNchargeableWeightOrder/FSNchargeableWeightTotal)*shipmentChargeableWeightFinal as volumetric_ratio
//resident shipment_details;
//
//left join(shipment_details)
//Load distinct mp_tracking_id as tracking_id,
//     wasMPPicked as wasMPPickedShipmentDetails,
//     mp_vehicle_type as mp_vehicle_typeShipmentDetails,
//     mp_sheet_createday as mp_sheet_createdayShipmentDetails
//Resident MP;
//
//left join(shipment_details)
//Load  fkl_action_dayname,
//     sum(if(vehicleTypeShip='bike' or shipment_typeShip='RVP' ,volumetric_ratio)) as volumetric_ratio_bike_total
//resident shipment_details
//group by fkl_action_dayname;
////
//left join(shipment_details)
//Load  fkl_action_dayname,
//     sum(if(vehicleTypeShip='van' or vehicleTypeShip='other',shipment_ratio)) as shipment_ratio_van_total
//resident shipment_details
//group by fkl_action_dayname;
////
//left join(shipment_details)
//Load  fkl_action_dayname,
//     sum(shipment_ratio) as shipment_ratio_total
//resident shipment_details
//group by fkl_action_dayname;
////
//left join(shipment_details)
//Load  fkl_action_dayname,
//     sum(if(shipmentPaymentType='COD',shipment_ratio)) as shipment_ratio_total_COD
//resident shipment_details
//group by fkl_action_dayname;
////
//left join(shipment_details)
//Load  fkl_action_dayname,
//     sum(if(shipmentPaymentType='COD',order_item_value)) as order_item_value_total
//resident shipment_details
//group by fkl_action_dayname;
////
//left join(shipment_details)
//Load  fkl_action_dayname,
//     sum(if(wasMPPickedShipmentDetails=1,shipment_ratio)) as shipment_ratio_total_MP
//resident shipment_details
//group by fkl_action_dayname;
//
//left join(shipment_details)
//Load  fkl_action_dayname,
//     sum(if(wasMPPickedShipmentDetails=1 and mp_vehicle_typeShipmentDetails='Bike',shipment_ratio)) as shipment_ratio_total_MP_Bike
//resident shipment_details
//group by fkl_action_dayname;
//
//left join(shipment_details)
//Load  fkl_action_dayname,
//     sum(if(wasMPPickedShipmentDetails=1 and mp_vehicle_typeShipmentDetails='Van',shipment_ratio)) as shipment_ratio_total_MP_Van
//resident shipment_details
//group by fkl_action_dayname;
//
//left join(shipment_details)
//LOAD trim(upper(Month)) as fkl_action_month ,
//     [FE Salary Marketplace] as FE_Salary_Marketplace,
//     [Van Conveyance Marketplace] as Van_Conveyance_Marketplace ,
//     [FE Salary Van] as FE_Salary_Van,
//     [FE Salary Bike] as FE_Salary_Bike,
//     [Van Conveyance] as Van_Conveyance,
//     [Biker Conveyance] as Biker_Conveyance,
//     [Central Team] as Central_Team,
//     [Hubs rental and Misc] as HRM,
//     [MH + TC Rent and Security] as MHTCRent_Security,
//     [LMD - HI + TL + Security] as LMDHITL_Security,
//     Incentives,
//     Consumables,
//     Communication,
//     [MH + TC Manpower] as MHTC_Manpower ,
//     [Collection Fixed] as Collection_Fixed,
//     [Collection Variable] as Collection_Variable
//FROM
//[$(qvdPath_excel)Cost_Daily.xlsx]
//(ooxml, embedded labels, table is Sheet2);
////
//left join(shipment_details)
//Load distinct keyShip,
//     if(wasMPPickedShipmentDetails=1 and mp_vehicle_typeShipmentDetails='Bike',(shipment_ratio/shipment_ratio_total_MP)*FE_Salary_Marketplace,0) as Contri_FE_Salary_Marketplace,
//     if(wasMPPickedShipmentDetails=1 and mp_vehicle_typeShipmentDetails='Van',(shipment_ratio/shipment_ratio_total_MP)*Van_Conveyance_Marketplace,0) as Contri_Van_Conveyance_Marketplace,
//     if((vehicleTypeShip='van' or vehicleTypeShip='other'), (shipment_ratio/shipment_ratio_van_total)*FE_Salary_Van,0) as Contri_FE_Salary_Van,
//
//     if(vehicleTypeShip='bike' or shipment_typeShip='RVP',(volumetric_ratio/volumetric_ratio_bike_total)*FE_Salary_Bike,0) as Contri_FE_Salary_Bike,
//
//     if((vehicleTypeShip='van' or vehicleTypeShip='other'),shipment_ratio/shipment_ratio_total)*Van_Conveyance as Contri_Van_Conveyance,
//
//    if(vehicleTypeShip='bike' or shipment_typeShip='RVP', (shipment_ratio/shipment_ratio_total))*Biker_Conveyance as Contri_Biker_Conveyance,
//
//     (shipment_ratio/shipment_ratio_total)*Central_Team as Contri_Central_Team,
//     (shipment_ratio/shipment_ratio_total)*HRM as Contri_HRM,
//     (shipment_ratio/shipment_ratio_total)*MHTCRent_Security as Contri_MHTCRent_Security,
//     (shipment_ratio/shipment_ratio_total)*LMDHITL_Security as Contri_LMDHITL_Security,
//     (shipment_ratio/shipment_ratio_total)*Incentives as Contri_Incentives,
//     (shipment_ratio/shipment_ratio_total)*Consumables as Contri_Consumables,
//     (shipment_ratio/shipment_ratio_total)*Communication as Contri_Communication,
//     (shipment_ratio/shipment_ratio_total)*MHTC_Manpower as Contri_MHTC_Manpower,
//     if(shipmentPaymentType='COD',(shipment_ratio/shipment_ratio_total_COD)*Collection_Fixed,0) as Contri_Collection_Fixed,
//     if(shipmentPaymentType='COD',(order_item_value/order_item_value_total)*Collection_Variable,0) as Contri_Collection_Variable
//resident shipment_details;
////
//shipment_detailsForQVD:
////left join(shipment_details)
//Load           merchant_reference_idShip,
//			   product_id_final,
//               Contri_FE_Salary_Marketplace,
//               Contri_Van_Conveyance_Marketplace,
//               Contri_FE_Salary_Van,
//               Contri_FE_Salary_Bike,
//               Contri_Van_Conveyance,
//               Contri_Biker_Conveyance,
//               Contri_Central_Team,
//               Contri_HRM,
//               Contri_MHTCRent_Security,
//               Contri_LMDHITL_Security,
//               Contri_Incentives,
//               Contri_Consumables,
//               Contri_Communication,
//               Contri_MHTC_Manpower,
//               Contri_Collection_Fixed,
//               Contri_Collection_Variable,
//
//               Contri_FE_Salary_Marketplace+
//               Contri_Van_Conveyance_Marketplace+
//               Contri_FE_Salary_Van+
//               Contri_FE_Salary_Bike+
//               Contri_Van_Conveyance+
//               Contri_Biker_Conveyance+
//               Contri_Central_Team+
//               Contri_HRM+
//               Contri_MHTCRent_Security+
//               Contri_LMDHITL_Security+
//               Contri_Incentives+
//               Contri_Consumables+
//               Contri_Communication+
//               Contri_MHTC_Manpower+
//               Contri_Collection_Fixed+
//               Contri_Collection_Variable as Cost,
//
//
//               shipment_typeShip,
//               if(merchant_codeShip='WSR','WSR','NonWSR') as isWSR
//               //'Y' as isWSR
//Resident shipment_details;
//
//store shipment_detailsForQVD into [$(qvdPath_fsd)fsd_LMD_Cost.qvd](qvd);
//////
//drop table  shipment_detailsForQVD;
//
///$tab Pending Reco
left keep(Run_Hub_Final)
PendingReco:
LOAD //reconciliationId,
     shipmentId as tracking_IDReco,
     hubId as fsd_HubId,
     wasPendingInSystem,
     wasPendingInHub,
     dayname(updateDateTime) as updateDatePendingReco,
     if(hour(updateDateTime)>=0 and hour(updateDateTime)<=2,dayname(updateDateTime)-1,dayname(updateDateTime)) as updateDatePendingRecoAdj,
     updateDateTime as updateDateTimePendingReco,
     if (DayName(updateDateTime)=DayName(Today(1)-1),1,0) as updateDatePendingRecoAdjYesterday

FROM
[$(qvdPath_fsd)fsd_pendingReconciliation.qvd]
(qvd);
///$tab mail_config
/* Mail Config Tab
 * This will contains tables containing your mail requirement
 * Name of the tab could be anything
 * Make sure you follow the table convention, table names/columns names etc.
 */

/* Variables if you want to use any */
let today = Today(1);
let yesterday=date(Today(1)-1);
let posDate=date(Today(1)-4);
set note = 'Total Month Sales: '&$(totalMonthSales);
let excelAttachVanHub = 'Van Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachVanCity = 'Van Report City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachVanRaw = 'Van Report Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachMPVan = 'MP Van Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachMPVanRaw = 'MP Van Report Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachSmartHub = 'Smart Devices Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachIntDevicesHub = 'Integrated Devices Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachIntDevicesHubDetail = 'Integrated Devices Report Hub Detail '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachIntDevicesCityDetail = 'Integrated Devices Report City Detail '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachIntDevicesFEDetail = 'Integrated Devices Report FE Detail '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';


let excelAttachSmartRaw = 'Smart Devices Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachVol = 'Volumetric Raw Data -'&day(Today(-1))&'-'&monthname(today(-1))&'.xls';
let excelAttachPOSHub = 'POS Devices Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachPOSFrauHub = 'POS Devices Report Hub '&date(Today(1)-4, 'DD-MMM-YY')&'.xls';
let excelAttachPOSRaw = 'POS Devices Report Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachPOSPLRaw = 'Pinelab Devices Report Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachPOSTrackingRaw = 'POS Devices Report Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachPOSReconHub = 'POS Devices Recon Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachPOSPLHub = 'Pinelabs Devices Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachPOSReconCity = 'POS Devices Recon Report City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachPOSPLCity = 'Pinelabs Devices Report City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachPOSReconRaw = 'POS Devices Recon Report Raw '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachMShipmentRaw = 'Shipment Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachRecOFDCity = 'Receive OFD Report City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachRecOFDHub = 'Receive OFD Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';

let excelAttachRecHourly = 'Receive Hourly'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachOFDHourly = 'OFD Hourly'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachDelHourly = 'Delivered Hourly'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachProdHourly = 'Producitivity Hourly '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachDeviceHourly = 'Device Hourly'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';


let excelAttachEKLDHCity = 'EKL DH Report City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachFEProdHub = 'FE Prod Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachFEProdCity ='FE Prod City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';


let excelAttachVanCityDelivery = 'Van Report City Delivery'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachVanHubDelivery = 'Van Report Hub Delivery'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachVanRawDelivery = 'Van Report Raw Delivery'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';

let excelAttachVanCityMP =  'Van Report City MP'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachVanHubMP =  'Van Report Hub MP'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachVanRawMP =  'Van Report Raw MP'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';

let excelAttachPendingRecoRaw = 'Pending Reco Raw'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachCashRecoRaw = 'Cash Reco Raw'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';

let excelAttachFEProdCityDelivery = 'FE Prod City Delivery '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachFEProdHubDelivery = 'FE Prod Hub Delivery '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachFEProdCityMP = 'FE Prod City MP '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachFEProdHubMP = 'FE Prod Hub MP '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
let excelAttachFEProdRaw = 'FE Prod Raw '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';

//"mail_1","config_1"

//"mail_2","config_2"
//"mail_3","config_3"
//"mail_7","config_7"
//"mail_8","config_8"
/* This Table stores no. of mails you want to send and mail_id, config_id corresponding to it.  */
MailEntity:
LOAD * Inline
[ mailId, mailConfigId
"mail_4","config_4"
"mail_5","config_5"
"mail_6","config_6"
"mail_9","config_9"
"mail_10","config_10"
"mail_11","config_11"
"mail_12","config_12"
"mail_13","config_13"
"mail_14","config_14"
];


/*
*For each Mail_id/config_id, table stores mail related informantion
*mailConfigId = foreign key from MailEntiy
*/
//"config_4", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_4", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_4", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_4", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_4", "mailSubject", Shipment Raw Data: $(yesterday)
//"config_4", "textBody",'Hi, <br>PFA the shipment Raw Data $(yesterday)</br>'


//"config_1", "toList","nirjhar.bhattacharya@flipkart.com"
//"config_1", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_1", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_1", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_1", "mailSubject", Linehaul/DH Hour Wise Report: $(yesterday)
//"config_1", "textBody",'Hi, <br>PFA the Linehaul/DH Hour Wise Report for $(yesterday)</br>'
//
//"config_2", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_2", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_2", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_2", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_2", "mailSubject", Smart Devices Report : $(yesterday)
//"config_2", "textBody",'Hi, <br>PFA the smart devices report for $(yesterday)</br>'
//
//"config_3", "toList", "nirjhar.bhattacharya@flipkart.com>"
//"config_3", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_3", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_3", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_3", "mailSubject", POS Devices Report : $(yesterday)
//"config_3", "textBody",'Hi, <br>PFA the POS devices report for $(yesterday)</br>'


//"config_7", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_7", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_7", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_7", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_7", "mailSubject",FE Productivity/Attendance Report: $(yesterday)
//"config_7", "textBody",'Hi, <br>PFA the Productivity/Attendance Report $(yesterday)</br>'
//
//"config_8", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_8", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_8", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_8", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_8", "mailSubject",POS ERP-MIS Mismatch Report: $(posDate)
//"config_8", "textBody",'Hi, <br>PFA the POS ERP-MIS Mismatch Report $(posDate)</br>'

MailConfigEntity:
LOAD * inline
[mailConfigId,    configKey,      configValue



"config_5", "toList", "kunwar.ak@flipkart.com"
"config_5", "ccList", "nirjhar.bhattacharya@flipkart.com"
"config_5", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
"config_5", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
"config_5", "mailSubject",Receive/OFD Timeline Report: $(yesterday)
"config_5", "textBody",'Hi, <br>PFA the Receive/OFD Timeline Report $(yesterday)</br>'

"config_6", "toList", "kunwar.ak@flipkart.com"
"config_6", "ccList", "nirjhar.bhattacharya@flipkart.com"
"config_6", "fromEmail", "kunwar.ak@flipkart.com"
"config_6", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
"config_6", "mailSubject",EKL DH Report: $(yesterday)
"config_6", "textBody",'Hi, <br>PFA the EKL DH Report $(yesterday)</br>'


"config_9", "toList", "kunwar.ak@flipkart.com"
"config_9", "ccList", "nirjhar.bhattacharya@flipkart.com"
"config_9", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
"config_9", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
"config_9", "mailSubject",Devices Integrated Report $(yesterday)
"config_9", "textBody",'Hi, <br>PFA the Devices Integrated Report for $(yesterday)</br>'

"config_10", "toList","kunwar.ak@flipkart.com"
"config_10", "ccList", "nirjhar.bhattacharya@flipkart.com"
"config_10", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
"config_10", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
"config_10", "mailSubject", Van Report : $(yesterday)
"config_10", "textBody",'Hi, <br>PFA the van report for $(yesterday)</br>'

"config_11", "toList", "kunwar.ak@flipkart.com"
"config_11", "ccList", "nirjhar.bhattacharya@flipkart.com"
"config_11", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
"config_11", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
"config_11", "mailSubject",FE Productivity/Attendance Report: $(yesterday)
"config_11", "textBody",'Hi, <br>PFA the Productivity/Attendance Report $(yesterday)</br>'

"config_12", "toList", "kunwar.ak@flipkart.com"
"config_12", "ccList", "nirjhar.bhattacharya@flipkart.com"
"config_12", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
"config_12", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
"config_12", "mailSubject",NDR Compliance Report: $(yesterday)
"config_12", "textBody",'Hi, <br>PFA the NDR Compliance Report $(yesterday)</br>'


"config_13", "toList", "kunwar.ak@flipkart.com"
"config_13", "ccList", "nirjhar.bhattacharya@flipkart.com"
"config_13", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
"config_13", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
"config_13", "mailSubject",Goods and Cash Reconciliation Report : $(yesterday)
"config_13", "textBody",'Hi, <br>PFA the Pending Reconciliation Report $(yesterday)</br>'


"config_14", "toList", "kunwar.ak@flipkart.com"
"config_14", "ccList", "nirjhar.bhattacharya@flipkart.com"
"config_14", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
"config_14", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
"config_14", "mailSubject",Devices Integrated Report $(yesterday)
"config_14", "textBody",'Hi, <br>PFA the Devices Integrated Report for $(yesterday)</br>'

];


//"config_5", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_5", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_5", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_5", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_5", "mailSubject",Receive/OFD Timeline Report: $(yesterday)
//"config_5", "textBody",'Hi, <br>PFA the Receive/OFD Timeline Report $(yesterday)</br>'
//

/*
* For each Mail, different attachments it has
* Each file represents one attachments
* fileType 		: attachment/mailbody
* fileAttachType: excel/image/html (excel only if filetype = attachment)
* fileName 	    : Temp file name for each fileId
* fileId		: Unique key for each row
* mailConfigId  : foreign key from MailEntity
*/
//AttachmentEntity:
//LOAD * INLINE [
//    mailConfigId, fileId, fileName, fileType, fileAttachType
//    config_1, file1, $(excelAttachVan), attachment, excel
//    config_1, file2, table1.html, mailbody, html
//    config_1, file3, table2.html, mailbody, html
//
//    config_2, file4, $(excelAttachSmart), attachment, excel
//    config_2, file5, table3.html, mailbody, html
//
//    config_3, file6, $(excelAttachVol), attachment, excel
//    config_3, file7, table4.html, mailbody, html
//
//];
//config_4, file16,  $(excelAttachMShipmentRaw), attachment, excel

AttachmentEntity:
LOAD * INLINE [
    mailConfigId, fileId, fileName, fileType, fileAttachType

    config_1, file1, table1.html, mailbody, image
    config_1, file2, table2.html, mailbody, image
    config_1, file3, table3.html, mailbody, image
    config_1, file4, table3.html, mailbody, image
    config_1, file5, $(excelAttachVanCity), attachment, excel
    config_1, file6, $(excelAttachVanHub), attachment, excel

    config_2, file7, table1.html, mailbody, html
    config_2, file8,  $(excelAttachSmartHub), attachment, excel
    config_2, file9,  $(excelAttachSmartRaw), attachment, excel

    config_3, file10,  table1.html, mailbody, html
    config_3, file30,  table2.html, mailbody, html
    config_3, file11,  $(excelAttachPOSHub), attachment, excel
    config_3, file12,  $(excelAttachPOSRaw), attachment, excel
    config_3, file13,  $(excelAttachPOSReconCity), attachment, excel
    config_3, file14,  $(excelAttachPOSReconHub), attachment, excel
    config_3, file15,  $(excelAttachPOSReconRaw), attachment, excel
    config_3, file31,  $(excelAttachPOSPLHub), attachment, excel, mailbody, html
    config_3, file36,  $(excelAttachPOSPLRaw), attachment, excel, mailbody, html


 	config_5, file17, table1.html, mailbody, html
    config_5, file18, table2.html, mailbody, html
    config_5, file19, $(excelAttachRecOFDCity), attachment, excel
    config_5, file20, $(excelAttachRecOFDHub), attachment, excel
    config_5, file73, $(excelAttachRecHourly), attachment, excel
    config_5, file74, $(excelAttachOFDHourly), attachment, excel
    config_5, file75, $(excelAttachDelHourly), attachment, excel
    config_5, file76, $(excelAttachProdHourly), attachment, excel
    config_5, file77, $(excelAttachDeviceHourly), attachment, excel

    config_6, file21, table1.html, mailbody, html
    config_6, file22, table2.html, mailbody, html
    config_6, file23, $(excelAttachEKLDHCity), attachment, excel
    config_6, file24, table4.html, mailbody, html

    config_7, file25, table1.html, mailbody, html
    config_7, file26, table2.html, mailbody, html
    config_7, file27, table3.html, mailbody, html
    config_7, file28,  $(excelAttachFEProdCity), attachment, excel
    config_7, file29,  $(excelAttachFEProdHub), attachment, excel

    config_8, file32,  table1.html, mailbody, html
    config_8, file33,  $(excelAttachPOSTrackingRaw), attachment, excel

    config_9, file34,  table1.html, mailbody, html
    config_9, file35,  $(excelAttachIntDevicesHub), attachment, excel

    config_10, file37, table1.html, mailbody, html
    config_10, file38, table2.html, mailbody, html
    config_10, file39, table3.html, mailbody, html
    config_10, file40, table4.html, mailbody, html
    config_10, file41, $(excelAttachVanCityDelivery), attachment, excel
    config_10, file42, $(excelAttachVanHubDelivery), attachment, excel
    config_10, file43, $(excelAttachVanRawDelivery), attachment, excel
    config_10, file44, $(excelAttachVanCityMP), attachment, excel
    config_10, file45, $(excelAttachVanHubMP), attachment, excel
    config_10, file46, $(excelAttachVanRawMP), attachment, excel

    config_11, file47, table1.html, mailbody, html
    config_11, file48, table2.html, mailbody, html
    config_11, file49, table3.html, mailbody, html
    config_11, file50, table4.html, mailbody, html
    config_11, file51,  $(excelAttachFEProdCityDelivery), attachment, excel
    config_11, file52,  $(excelAttachFEProdHubDelivery), attachment, excel
    config_11, file53,  $(excelAttachFEProdCityMP), attachment, excel
    config_11, file54,  $(excelAttachFEProdHubMP), attachment, excel
    config_11, file72,  $(excelAttachFEProdRaw), attachment, excel

    config_12, file55, table1.html, mailbody, html
    config_12, file56, table2.html, mailbody, html

    config_13, file57, table1.html, mailbody, html
    config_13, file58, table2.html, mailbody, html
    config_13, file71,  $(excelAttachPendingRecoRaw), attachment, excel
    config_13, file78, table3.html, mailbody, html
    config_13, file79, table4.html, mailbody, html
    config_13, file80,  $(excelAttachPendingRecoRaw), attachment, excel


    config_14, file69, table1.html, mailbody, html
    config_14, file70, table2.html, mailbody, html
    config_14, file59, table3.html, mailbody, html
    config_14, file60, table4.html, mailbody, html
    config_14, file61, table5.html, mailbody, html
    config_14, file62, table6.html, mailbody, html


    config_14, file63,  $(excelAttachIntDevicesCityDetail), attachment, excel
    config_14, file64,  $(excelAttachIntDevicesHubDetail), attachment, excel
    config_14, file65,  $(excelAttachIntDevicesFEDetail), attachment, excel
    config_14, file66,  $(excelAttachPOSHub), attachment, excel
    config_14, file67,  $(excelAttachPOSPLHub), attachment, excel
    config_14, file68,  $(excelAttachSmartHub), attachment, excel
 ];




// config_5, file16, table1.html, mailbody, html
//    config_5, file17, table2.html, mailbody, html
//    config_5, file18, $(excelAttachRecOFDCity), attachment, excel
//    config_5, file19, $(excelAttachRecOFDHub), attachment, excel
/*
config_3, file15,  table1.html, mailbody, html
    config_3, file16,  $(excelAttachPOSReconHub), attachment, excel
* For each File, stores different Objects it can have
* Each Object represents one object from front end
* if File corresponds to 'excel' filetype, it requires objTabName, objToSelect ,objComments,objCellPos,objType info
* objTabName  = Name of tab in Excel sheet
* objToSelect = Front end obj to copy/export
* objComments = Any comments associated with the table copied to excel, put "" (empty) if no comments
* objCellPos  = Cell no in excel from where you want to copy into excel sheet. Eg: A1,A4(if any comments)
* objType 	  = Type of object, Data/image/html. Eg: Tables: Data, Charts/graphs: image
* objId 	  = Unique id for each row
* fileId	  = foreign key from AttachmentEntity
*/
//ObjectEntity:
//Load * Inline
//[ fileId,objId,objTabName, objToSelect ,objComments,objCellPos,objType
//
//  "file1","obj1","$(excelAttachVan)", "CH37", "","A2","Data"
//  "file2","obj2","Van Report Zone", "CH35", "","A2","Data"
//  "file3","obj3","Van Report City", "CH36", "","A2","Data"
//
//
// "file4","obj4","$(excelAttachSmart)", "CH21", "","A2","Data"
// "file5","obj5","Smart Devices Report Hub", "CH23", "","A2","Data"
//
//
// "file6","obj6","$(excelAttachSmart)", "CH21", "","A2","Data"
// "file7","obj7","Volumetric Report Hub", "CH22", "","A2","Data"
//
//];

//"file16","obj16","Shipment Raw Data", "TB44", "","A2","Data"

ObjectEntity:
Load * Inline
[ fileId,objId,objTabName, objToSelect ,objComments,objCellPos,objType


  "file1","obj1","Received Hourly", "CH340", "","","image"
  "file2","obj2","OFD Hourly", "CH341", "","","image"
  "file3","obj3","Delivered Hourly", "CH339", "","","image"
  "file4","obj4","Devices Delivered Hourly", "CH338", "","","image"
  "file5","obj5","Van Report City All", "CH147", "","A2","Data"
  "file6","obj6","Van Report Hub All", "CH191", "","A2","Data"

  "file7","obj7","Smart Report City", "CH211", "","","html"
  "file8","obj8","Smart Report Hub ", "CH212", "","A2","Data"
  "file9","obj9","Smart Report Raw Data", "CH113", "","A2","Data"

  "file10","obj10","POS Devices Report City", "CH162", "","","html"
  "file11","obj11","POS Devices Report Hub ", "CH160", "","A2","Data"
  "file12","obj12","POS Devices Report Raw Data", "CH159", "","A2","Data"

  "file13","obj13","POS Recon Report City", "CH163","","A2","Data"
  "file14","obj14","POS Recon Report Hub", "CH164", "","A2","Data"
  "file15","obj15","POS Recon Raw Data", "TB34", "","A2","Data"
  "file30","obj30","Pinelabs Devices Report City", "CH210", "","","html"
  "file31","obj31","Pinelabs Devices Report Hub", "CH209", "","A2","Data"
  "file36","obj36","Pinelabs Devices Report Raw", "CH238", "","A2","Data"



  "file17","obj17","Receive OFD Zone", "CH175", "","","html"
  "file18","obj18","Receive OFD State", "CH176", "","","html"
  "file19","obj19","Receive OFD City", "CH177", "","A2","Data"
  "file20","obj20","Receive OFD Hub", "CH178", "","A2","Data"

  "file73","obj73","Receive Hourly", "CH306", "","A2","Data"
  "file74","obj74","OFD Hourly", "CH307", "","A2","Data"
  "file75","obj75","Delivered Hourly", "CH311", "","A2","Data"
  "file76","obj76","Productivity Hourly", "CH304", "","A2","Data"
  "file77","obj77","Devices Hourly", "CH309", "","A2","Data"


  "file21","obj21","EKL DH Zone", "CH188", "","","html"
  "file22","obj22","EKL DH State", "CH189", "","","html"
  "file23","obj23","EKL DH City", "CH187", "","A2","Data"
  "file24","obj24","EKL DH Hub", "CH186", "","","html"

  "file25","obj25","FE Prod Zone", "CH205", "","","html"
  "file26","obj26","FE Prod City", "CH204", "","","html"
  "file27","obj27","FE Prod Hub", "CH200", "","","html"
  "file28","obj28","FE Prod City", "CH204", "","A2","Data"
  "file29","obj29","FE Prod Hub", "CH200", "","A2","Data"

  "file32","obj32","POS ERP-MIS Mismatch Hub", "CH213", "","","html"
  "file33","obj33","POS ERP-MIS Mismatch Hub Raw", "TB46", "","A2","Data"

  "file34","obj34","Integrated Devices Report City ", "CH253", "","","html"
  "file35","obj35","Integrated Devices Report Hub", "CH226", "","A2","Data"

  "file37","obj37","Van Report Zone Deliveries", "CH248", "","","html"
  "file38","obj38","Van Report Top City Deliveries", "CH230", "","","html"
  "file39","obj39","Van Report Zone MP", "CH231", "","","html"
  "file40","obj40","Van Report Top City MP", "CH234", "","","html"
  "file41","obj41","Van Report City Deliveries", "CH230", "","A2","Data"
  "file42","obj42","Van Report Hub Deliveries", "CH249", "","A2","Data"
  "file43","obj43","Van Report Raw Deliveries", "CH225", "","A2","Data"
  "file44","obj44","Van Report City MP", "CH234", "","A2","Data"
  "file45","obj45","Van Report Hub MP", "CH235", "","A2","Data"
  "file46","obj46","Van Report Raw MP", "CH245", "","A2","Data"

  "file47","obj47","FE Prod Zone Deliveries", "CH237", "","","html"
  "file48","obj48","FE Prod Top City Deliveries", "CH240", "","","html"
  "file49","obj49","FE Prod Zone MP", "CH250", "","","html"
  "file50","obj50","FE Prod Top City MP", "CH242","","","html"
  "file51","obj51","FE Prod City Deliveries", "CH240", "","A2","Data"
  "file52","obj52","FE Prod Hub Deliveries", "CH241", "","A2","Data"
  "file53","obj53","FE Prod City MP ", "CH226", "","A2","Data"
  "file54","obj54","FE Prod Hub MP", "CH243", "","A2","Data"
  "file72","obj72","FE Prod Raw Data", "CH323", "","A2","Data"

  "file55","obj55","NDR City", "CH260", "","A2","Data"
  "file56","obj56","NRD Hub", "CH259", "","A2","Data"

  "file57","obj57","Pending Reconciliation Report City", "CH322", "","","html"
  "file58","obj58","Pending Reconciliation Report Hub", "CH321", "","","html"
  "file71","obj71","Pending Report Raw Data", "TB60", "","A2","Data"
  "file78","obj78","Cash Reconciliation Report City", "CH300", "","","html"
  "file79","obj79","Cash Reconciliation Report Hub", "CH313", "","","html"
  "file80","obj80","Cash Reconciliation Report Hub", "TB56", "","","html"

  "file59","obj59","Integrated Device Report City(Summary)", "CH279", "","","html"
  "file60","obj60","POS Report City(Summary)", "CH277", "","","html"
  "file61","obj61","Pinelab Report City(Summary)", "CH278", "","","html"
  "file62","obj62","Smart Devices Report city(Summary)", "CH283", "","","html"

  "file69","obj69","Integrated Device Report Zone", "CH324", "","A2","Data"
  "file70","obj70","Integrated Device Report Region", "CH325", "","A2","Data"
  "file63","obj63","Integrated Device Report City", "CH289", "","A2","Data"
  "file64","obj64","Integrated Device Report Hub", "CH290", "","A2","Data"
  "file65","obj65","Integrated Device Report FE", "CH291", "","A2","Data"
  "file66","obj66","POS Hub", "CH292", "","A2","Data"
  "file67","obj67","Pinelabs Hub", "CH293", "","A2","Data"
  "file68","obj68","Smart Devices Hub", "CH295", "","A2","Data"
  ];

// "file16","obj16","Receive/OFD Zone", "CH167", "","","html"
//  "file17","obj17","Receive/OFD State", "CH174", "","","html"
//  "file18","obj18","Receive/OFD City", "CH168", "","A2","Data"
//  "file19","obj19","Receive/OFD Hub", "CH158", "","A2","Data"


/*
 "file15","obj15","POS Devices Reconciliation Report City", "CH119", "","","html"
  "file16","obj16","POS Devices Reconciliation Report Hub", "CH116", "","A2","Data"
* For each Object to copy, stores different selections it requires before mailing
* Each Object will represents one selectionBoxId from front end
* This SelectionBox will be created and stored on front-end using snapshot button
* You can have same selection for different objects
* objId : primary key and foreign key
*/
//SelectionEnity:
//Load * Inline
//[ objId, selectionBoxId
// "obj1","selectionBox1"
// "obj2","selectionBox2"
// "obj3","selectionBox3"
// "obj4","selectionBox4"
// "obj5","selectionBox5"
// "obj6","selectionBox6"
// "obj7","selectionBox7"
// ];
// "obj16","selectionBox15"
SelectionEnity:
Load * Inline
[ objId, selectionBoxId
 "obj1","selectionBox1"
 "obj2","selectionBox2"
 "obj3","selectionBox3"
 "obj4","selectionBox4"
 "obj5","selectionBox5"
 "obj6","selectionBox6"
 "obj7","selectionBox7"
 "obj8","selectionBox8"

  "obj9","selectionBox9"
  "obj10","selectionBox10"
  "obj11","selectionBox11"

  "obj12","selectionBox12"
  "obj13","selectionBox13"

  "obj14","selectionBox14"
   "obj15","selectionBox20"



  "obj17","selectionBox16"
  "obj18","selectionBox17"
  "obj19","selectionBox18"
  "obj20","selectionBox19"

  "obj73","selectionBox73"
  "obj74","selectionBox74"
  "obj75","selectionBox75"
  "obj76","selectionBox76"
  "obj77","selectionBox77"

  "obj21","selectionBox21"
  "obj22","selectionBox22"
  "obj23","selectionBox23"
  "obj24","selectionBox24"

  "obj25","selectionBox25"
  "obj26","selectionBox26"
  "obj27","selectionBox27"

  "obj28","selectionBox28"
  "obj29","selectionBox29"
 "obj30","selectionBox30"
 "obj31","selectionBox31"

  "obj32","selectionBox32"
  "obj33","selectionBox33"

  "obj34","selectionBox34"
  "obj35","selectionBox35"

  "obj36","selectionBox36"
  "obj37","selectionBox37"
  "obj38","selectionBox38"
  "obj39","selectionBox39"
  "obj40","selectionBox40"
  "obj41","selectionBox41"
  "obj42","selectionBox42"
  "obj43","selectionBox43"
  "obj44","selectionBox44"
  "obj45","selectionBox45"
  "obj46","selectionBox46"
  "obj47","selectionBox47"
  "obj48","selectionBox48"
  "obj49","selectionBox49"
  "obj50","selectionBox50"
  "obj51","selectionBox51"
  "obj52","selectionBox52"
  "obj53","selectionBox53"
  "obj54","selectionBox54"
  "obj72","selectionBox72"

  "obj55","selectionBox55"
  "obj56","selectionBox56"

  "obj57","selectionBox57"
  "obj58","selectionBox58"
  "obj71","selectionBox71"
  "obj78","selectionBox78"
  "obj79","selectionBox79"
  "obj80","selectionBox80"

  "obj69","selectionBox69"
  "obj70","selectionBox70"
   "obj59","selectionBox59"
  "obj60","selectionBox60"
   "obj61","selectionBox61"
  "obj62","selectionBox62"
   "obj63","selectionBox63"
  "obj64","selectionBox64"
   "obj65","selectionBox65"
  "obj66","selectionBox66"
   "obj67","selectionBox67"
  "obj68","selectionBox68"

  ];

//  "obj16","selectionBox16"
//  "obj17","selectionBox17"
//  "obj18","selectionBox17"
//  "obj19","selectionBox17"
///$tab mail_config_1
///* Mail Config Tab
// * This will contains tables containing your mail requirement
// * Name of the tab could be anything
// * Make sure you follow the table convention, table names/columns names etc.
// */
//
///* Variables if you want to use any */
//let today = Today(1);
//let yesterday=date(Today(1)-1);
//let posDate=date(Today(1)-4);
//set note = 'Total Month Sales: '&$(totalMonthSales);
//let excelAttachVanHub = 'Van Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachVanCity = 'Van Report City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachVanRaw = 'Van Report Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachMPVan = 'MP Van Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachMPVanRaw = 'MP Van Report Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachSmartHub = 'Smart Devices Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachIntDevicesHub = 'Integrated Devices Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachIntDevicesHubDetail = 'Integrated Devices Report Hub Detail '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachIntDevicesCityDetail = 'Integrated Devices Report City Detail '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachIntDevicesFEDetail = 'Integrated Devices Report FE Detail '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//
//
//let excelAttachSmartRaw = 'Smart Devices Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachVol = 'Volumetric Raw Data -'&day(Today(-1))&'-'&monthname(today(-1))&'.xls';
//let excelAttachPOSHub = 'POS Devices Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachPOSFrauHub = 'POS Devices Report Hub '&date(Today(1)-4, 'DD-MMM-YY')&'.xls';
//let excelAttachPOSRaw = 'POS Devices Report Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachPOSPLRaw = 'Pinelab Devices Report Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachPOSTrackingRaw = 'POS Devices Report Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachPOSReconHub = 'POS Devices Recon Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachPOSPLHub = 'Pinelabs Devices Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachPOSReconCity = 'POS Devices Recon Report City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachPOSPLCity = 'Pinelabs Devices Report City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachPOSReconRaw = 'POS Devices Recon Report Raw '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachMShipmentRaw = 'Shipment Raw Data '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachRecOFDCity = 'Receive OFD Report City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachRecOFDHub = 'Receive OFD Report Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//
//let excelAttachRecHourly = 'Receive Hourly'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachOFDHourly = 'OFD Hourly'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachDelHourly = 'Delivered Hourly'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachProdHourly = 'Producitivity Hourly '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachDeviceHourly = 'Device Hourly'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//
//
//let excelAttachEKLDHCity = 'EKL DH Report City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachFEProdHub = 'FE Prod Hub '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachFEProdCity ='FE Prod City '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//
//
//let excelAttachVanCityDelivery = 'Van Report City Delivery'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachVanHubDelivery = 'Van Report Hub Delivery'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachVanRawDelivery = 'Van Report Raw Delivery'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//
//let excelAttachVanCityMP =  'Van Report City MP'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachVanHubMP =  'Van Report Hub MP'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachVanRawMP =  'Van Report Raw MP'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//
//let excelAttachPendingRecoRaw = 'Pending Reco Raw'&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//
//let excelAttachFEProdCityDelivery = 'FE Prod City Delivery '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachFEProdHubDelivery = 'FE Prod Hub Delivery '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachFEProdCityMP = 'FE Prod City MP '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachFEProdHubMP = 'FE Prod Hub MP '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//let excelAttachFEProdRaw = 'FE Prod Raw '&date(Today(1)-1, 'DD-MMM-YY')&'.xls';
//
///* This Table stores no. of mails you want to send and mail_id, config_id corresponding to it.  */
//MailEntity:
//LOAD * Inline
//[ mailId, mailConfigId
//"mail_1","config_1"
//"mail_2","config_2"
//"mail_3","config_3"
//"mail_4","config_4"
//"mail_5","config_5"
//"mail_6","config_6"
//"mail_7","config_7"
//"mail_8","config_8"
//"mail_9","config_9"
//"mail_10","config_10"
//"mail_11","config_11"
//"mail_12","config_12"
//"mail_13","config_13"
//"mail_14","config_14"
//];
//
//
///*
//*For each Mail_id/config_id, table stores mail related informantion
//*mailConfigId = foreign key from MailEntiy
//*/
////"config_4", "toList", "nirjhar.bhattacharya@flipkart.com"
////"config_4", "ccList", "nirjhar.bhattacharya@flipkart.com"
////"config_4", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
////"config_4", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
////"config_4", "mailSubject", Shipment Raw Data: $(yesterday)
////"config_4", "textBody",'Hi, <br>PFA the shipment Raw Data $(yesterday)</br>'
//
//
//MailConfigEntity:
//LOAD * inline
//[mailConfigId,    configKey,      configValue
//"config_1", "toList","nirjhar.bhattacharya@flipkart.com"
//"config_1", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_1", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_1", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_1", "mailSubject", Linehaul/DH Hour Wise Report: $(yesterday)
//"config_1", "textBody",'Hi, <br>PFA the Linehaul/DH Hour Wise Report for $(yesterday)</br>'
//
//"config_2", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_2", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_2", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_2", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_2", "mailSubject", Smart Devices Report : $(yesterday)
//"config_2", "textBody",'Hi, <br>PFA the smart devices report for $(yesterday)</br>'
//
//"config_3", "toList", "nirjhar.bhattacharya@flipkart.com>"
//"config_3", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_3", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_3", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_3", "mailSubject", POS Devices Report : $(yesterday)
//"config_3", "textBody",'Hi, <br>PFA the POS devices report for $(yesterday)</br>'
//
//
//"config_5", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_5", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_5", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_5", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_5", "mailSubject",Receive/OFD Timeline Report: $(yesterday)
//"config_5", "textBody",'Hi, <br>PFA the Receive/OFD Timeline Report $(yesterday)</br>'
//
//"config_6", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_6", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_6", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_6", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_6", "mailSubject",EKL DH Report: $(yesterday)
//"config_6", "textBody",'Hi, <br>PFA the EKL DH Report $(yesterday)</br>'
//
//"config_7", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_7", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_7", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_7", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_7", "mailSubject",FE Productivity/Attendance Report: $(yesterday)
//"config_7", "textBody",'Hi, <br>PFA the Productivity/Attendance Report $(yesterday)</br>'
//
//"config_8", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_8", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_8", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_8", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_8", "mailSubject",POS ERP-MIS Mismatch Report: $(posDate)
//"config_8", "textBody",'Hi, <br>PFA the POS ERP-MIS Mismatch Report $(posDate)</br>'
//
//"config_9", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_9", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_9", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_9", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_9", "mailSubject",Devices Integrated Report $(yesterday)
//"config_9", "textBody",'Hi, <br>PFA the Devices Integrated Report for $(yesterday)</br>'
//
//"config_10", "toList","nirjhar.bhattacharya@flipkart.com"
//"config_10", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_10", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_10", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_10", "mailSubject", Van Report : $(yesterday)
//"config_10", "textBody",'Hi, <br>PFA the van report for $(yesterday)</br>'
//
//"config_11", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_11", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_11", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_11", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_11", "mailSubject",FE Productivity/Attendance Report: $(yesterday)
//"config_11", "textBody",'Hi, <br>PFA the Productivity/Attendance Report $(yesterday)</br>'
//
//"config_12", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_12", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_12", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_12", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_12", "mailSubject",NDR Compliance Report: $(yesterday)
//"config_12", "textBody",'Hi, <br>PFA the NDR Compliance Report $(yesterday)</br>'
//
//
//"config_13", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_13", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_13", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_13", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_13", "mailSubject",Pending Reconciliation Report : $(yesterday)
//"config_13", "textBody",'Hi, <br>PFA the Pending Reconciliation Report $(yesterday)</br>'
//
//
//"config_14", "toList", "nirjhar.bhattacharya@flipkart.com"
//"config_14", "ccList", "nirjhar.bhattacharya@flipkart.com"
//"config_14", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_14", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
//"config_14", "mailSubject",Devices Integrated Report $(yesterday)
//"config_14", "textBody",'Hi, <br>PFA the Devices Integrated Report for $(yesterday)</br>'
//
//];
//
//
////"config_5", "toList", "nirjhar.bhattacharya@flipkart.com"
////"config_5", "ccList", "nirjhar.bhattacharya@flipkart.com"
////"config_5", "fromEmail", "nirjhar.bhattacharya@flipkart.com"
////"config_5", "ReplyToEmail", "nirjhar.bhattacharya@flipkart.com"
////"config_5", "mailSubject",Receive/OFD Timeline Report: $(yesterday)
////"config_5", "textBody",'Hi, <br>PFA the Receive/OFD Timeline Report $(yesterday)</br>'
////
//
///*
//* For each Mail, different attachments it has
//* Each file represents one attachments
//* fileType 		: attachment/mailbody
//* fileAttachType: excel/image/html (excel only if filetype = attachment)
//* fileName 	    : Temp file name for each fileId
//* fileId		: Unique key for each row
//* mailConfigId  : foreign key from MailEntity
//*/
////AttachmentEntity:
////LOAD * INLINE [
////    mailConfigId, fileId, fileName, fileType, fileAttachType
////    config_1, file1, $(excelAttachVan), attachment, excel
////    config_1, file2, table1.html, mailbody, html
////    config_1, file3, table2.html, mailbody, html
////
////    config_2, file4, $(excelAttachSmart), attachment, excel
////    config_2, file5, table3.html, mailbody, html
////
////    config_3, file6, $(excelAttachVol), attachment, excel
////    config_3, file7, table4.html, mailbody, html
////
////];
////config_4, file16,  $(excelAttachMShipmentRaw), attachment, excel
//
//AttachmentEntity:
//LOAD * INLINE [
//    mailConfigId, fileId, fileName, fileType, fileAttachType
//
//    config_1, file1, table1.html, mailbody, image
//    config_1, file2, table2.html, mailbody, image
//    config_1, file3, table3.html, mailbody, image
//    config_1, file4, table3.html, mailbody, image
//    config_1, file5, $(excelAttachVanCity), attachment, excel
//    config_1, file6, $(excelAttachVanHub), attachment, excel
//
//    config_2, file7, table1.html, mailbody, html
//    config_2, file8,  $(excelAttachSmartHub), attachment, excel
//    config_2, file9,  $(excelAttachSmartRaw), attachment, excel
//
//    config_3, file10,  table1.html, mailbody, html
//    config_3, file30,  table2.html, mailbody, html
//    config_3, file11,  $(excelAttachPOSHub), attachment, excel
//    config_3, file12,  $(excelAttachPOSRaw), attachment, excel
//    config_3, file13,  $(excelAttachPOSReconCity), attachment, excel
//    config_3, file14,  $(excelAttachPOSReconHub), attachment, excel
//    config_3, file15,  $(excelAttachPOSReconRaw), attachment, excel
//    config_3, file31,  $(excelAttachPOSPLHub), attachment, excel, mailbody, html
//    config_3, file36,  $(excelAttachPOSPLRaw), attachment, excel, mailbody, html
//
//
// 	config_5, file17, table1.html, mailbody, html
//    config_5, file18, table2.html, mailbody, html
//    config_5, file19, $(excelAttachRecOFDCity), attachment, excel
//    config_5, file20, $(excelAttachRecOFDHub), attachment, excel
//    config_5, file73, $(excelAttachRecHourly), attachment, excel
//    config_5, file74, $(excelAttachOFDHourly), attachment, excel
//    config_5, file75, $(excelAttachDelHourly), attachment, excel
//    config_5, file76, $(excelAttachProdHourly), attachment, excel
//    config_5, file77, $(excelAttachDeviceHourly), attachment, excel
//
//    config_6, file21, table1.html, mailbody, html
//    config_6, file22, table2.html, mailbody, html
//    config_6, file23, $(excelAttachEKLDHCity), attachment, excel
//    config_6, file24, table4.html, mailbody, html
//
//    config_7, file25, table1.html, mailbody, html
//    config_7, file26, table2.html, mailbody, html
//    config_7, file27, table3.html, mailbody, html
//    config_7, file28,  $(excelAttachFEProdCity), attachment, excel
//    config_7, file29,  $(excelAttachFEProdHub), attachment, excel
//
//    config_8, file32,  table1.html, mailbody, html
//    config_8, file33,  $(excelAttachPOSTrackingRaw), attachment, excel
//
//    config_9, file34,  table1.html, mailbody, html
//    config_9, file35,  $(excelAttachIntDevicesHub), attachment, excel
//
//    config_10, file37, table1.html, mailbody, html
//    config_10, file38, table2.html, mailbody, html
//    config_10, file39, table3.html, mailbody, html
//    config_10, file40, table4.html, mailbody, html
//    config_10, file41, $(excelAttachVanCityDelivery), attachment, excel
//    config_10, file42, $(excelAttachVanHubDelivery), attachment, excel
//    config_10, file43, $(excelAttachVanRawDelivery), attachment, excel
//    config_10, file44, $(excelAttachVanCityMP), attachment, excel
//    config_10, file45, $(excelAttachVanHubMP), attachment, excel
//    config_10, file46, $(excelAttachVanRawMP), attachment, excel
//
//    config_11, file47, table1.html, mailbody, html
//    config_11, file48, table2.html, mailbody, html
//    config_11, file49, table3.html, mailbody, html
//    config_11, file50, table4.html, mailbody, html
//    config_11, file51,  $(excelAttachFEProdCityDelivery), attachment, excel
//    config_11, file52,  $(excelAttachFEProdHubDelivery), attachment, excel
//    config_11, file53,  $(excelAttachFEProdCityMP), attachment, excel
//    config_11, file54,  $(excelAttachFEProdHubMP), attachment, excel
//    config_11, file72,  $(excelAttachFEProdRaw), attachment, excel
//
//    config_12, file55, table1.html, mailbody, html
//    config_12, file56, table2.html, mailbody, html
//
//    config_13, file57, table1.html, mailbody, html
//    config_13, file58, table2.html, mailbody, html
//    config_13, file71,  $(excelAttachPendingRecoRaw), attachment, excel
//
//    config_14, file69, table1.html, mailbody, html
//    config_14, file70, table2.html, mailbody, html
//    config_14, file59, table3.html, mailbody, html
//    config_14, file60, table4.html, mailbody, html
//    config_14, file61, table5.html, mailbody, html
//    config_14, file62, table6.html, mailbody, html
//
//
//    config_14, file63,  $(excelAttachIntDevicesCityDetail), attachment, excel
//    config_14, file64,  $(excelAttachIntDevicesHubDetail), attachment, excel
//    config_14, file65,  $(excelAttachIntDevicesFEDetail), attachment, excel
//    config_14, file66,  $(excelAttachPOSHub), attachment, excel
//    config_14, file67,  $(excelAttachPOSPLHub), attachment, excel
//    config_14, file68,  $(excelAttachSmartHub), attachment, excel
// ];
//
//
//
//
//// config_5, file16, table1.html, mailbody, html
////    config_5, file17, table2.html, mailbody, html
////    config_5, file18, $(excelAttachRecOFDCity), attachment, excel
////    config_5, file19, $(excelAttachRecOFDHub), attachment, excel
///*
//config_3, file15,  table1.html, mailbody, html
//    config_3, file16,  $(excelAttachPOSReconHub), attachment, excel
//* For each File, stores different Objects it can have
//* Each Object represents one object from front end
//* if File corresponds to 'excel' filetype, it requires objTabName, objToSelect ,objComments,objCellPos,objType info
//* objTabName  = Name of tab in Excel sheet
//* objToSelect = Front end obj to copy/export
//* objComments = Any comments associated with the table copied to excel, put "" (empty) if no comments
//* objCellPos  = Cell no in excel from where you want to copy into excel sheet. Eg: A1,A4(if any comments)
//* objType 	  = Type of object, Data/image/html. Eg: Tables: Data, Charts/graphs: image
//* objId 	  = Unique id for each row
//* fileId	  = foreign key from AttachmentEntity
//*/
////ObjectEntity:
////Load * Inline
////[ fileId,objId,objTabName, objToSelect ,objComments,objCellPos,objType
////
////  "file1","obj1","$(excelAttachVan)", "CH37", "","A2","Data"
////  "file2","obj2","Van Report Zone", "CH35", "","A2","Data"
////  "file3","obj3","Van Report City", "CH36", "","A2","Data"
////
////
//// "file4","obj4","$(excelAttachSmart)", "CH21", "","A2","Data"
//// "file5","obj5","Smart Devices Report Hub", "CH23", "","A2","Data"
////
////
//// "file6","obj6","$(excelAttachSmart)", "CH21", "","A2","Data"
//// "file7","obj7","Volumetric Report Hub", "CH22", "","A2","Data"
////
////];
//
////"file16","obj16","Shipment Raw Data", "TB44", "","A2","Data"
//
//ObjectEntity:
//Load * Inline
//[ fileId,objId,objTabName, objToSelect ,objComments,objCellPos,objType
//
//
//  "file1","obj1","Received Hourly", "CH340", "","","image"
//  "file2","obj2","OFD Hourly", "CH341", "","","image"
//  "file3","obj3","Delivered Hourly", "CH339", "","","image"
//  "file4","obj4","Devices Delivered Hourly", "CH338", "","","image"
//  "file5","obj5","Van Report City All", "CH147", "","A2","Data"
//  "file6","obj6","Van Report Hub All", "CH191", "","A2","Data"
//
//  "file7","obj7","Smart Report City", "CH211", "","","html"
//  "file8","obj8","Smart Report Hub ", "CH212", "","A2","Data"
//  "file9","obj9","Smart Report Raw Data", "CH113", "","A2","Data"
//
//  "file10","obj10","POS Devices Report City", "CH162", "","","html"
//  "file11","obj11","POS Devices Report Hub ", "CH160", "","A2","Data"
//  "file12","obj12","POS Devices Report Raw Data", "CH159", "","A2","Data"
//
//  "file13","obj13","POS Recon Report City", "CH163","","A2","Data"
//  "file14","obj14","POS Recon Report Hub", "CH164", "","A2","Data"
//  "file15","obj15","POS Recon Raw Data", "TB34", "","A2","Data"
//  "file30","obj30","Pinelabs Devices Report City", "CH210", "","","html"
//  "file31","obj31","Pinelabs Devices Report Hub", "CH209", "","A2","Data"
//  "file36","obj36","Pinelabs Devices Report Raw", "CH238", "","A2","Data"
//
//
//
//  "file17","obj17","Receive OFD Zone", "CH175", "","","html"
//  "file18","obj18","Receive OFD State", "CH176", "","","html"
//  "file19","obj19","Receive OFD City", "CH177", "","A2","Data"
//  "file20","obj20","Receive OFD Hub", "CH178", "","A2","Data"
//
//  "file73","obj73","Receive Hourly", "CH306", "","A2","Data"
//  "file74","obj74","OFD Hourly", "CH307", "","A2","Data"
//  "file75","obj75","Delivered Hourly", "CH311", "","A2","Data"
//  "file76","obj76","Productivity Hourly", "CH304", "","A2","Data"
//  "file77","obj77","Devices Hourly", "CH309", "","A2","Data"
//
//
//  "file21","obj21","EKL DH Zone", "CH188", "","","html"
//  "file22","obj22","EKL DH State", "CH189", "","","html"
//  "file23","obj23","EKL DH City", "CH187", "","A2","Data"
//  "file24","obj24","EKL DH Hub", "CH186", "","","html"
//
//  "file25","obj25","FE Prod Zone", "CH205", "","","html"
//  "file26","obj26","FE Prod City", "CH204", "","","html"
//  "file27","obj27","FE Prod Hub", "CH200", "","","html"
//  "file28","obj28","FE Prod City", "CH204", "","A2","Data"
//  "file29","obj29","FE Prod Hub", "CH200", "","A2","Data"
//
//  "file32","obj32","POS ERP-MIS Mismatch Hub", "CH213", "","","html"
//  "file33","obj33","POS ERP-MIS Mismatch Hub Raw", "TB46", "","A2","Data"
//
//  "file34","obj34","Integrated Devices Report City ", "CH253", "","","html"
//  "file35","obj35","Integrated Devices Report Hub", "CH226", "","A2","Data"
//
//  "file37","obj37","Van Report Zone Deliveries", "CH248", "","","html"
//  "file38","obj38","Van Report Top City Deliveries", "CH230", "","","html"
//  "file39","obj39","Van Report Zone MP", "CH231", "","","html"
//  "file40","obj40","Van Report Top City MP", "CH234", "","","html"
//  "file41","obj41","Van Report City Deliveries", "CH230", "","A2","Data"
//  "file42","obj42","Van Report Hub Deliveries", "CH249", "","A2","Data"
//  "file43","obj43","Van Report Raw Deliveries", "CH225", "","A2","Data"
//  "file44","obj44","Van Report City MP", "CH234", "","A2","Data"
//  "file45","obj45","Van Report Hub MP", "CH235", "","A2","Data"
//  "file46","obj46","Van Report Raw MP", "CH245", "","A2","Data"
//
//  "file47","obj47","FE Prod Zone Deliveries", "CH237", "","","html"
//  "file48","obj48","FE Prod Top City Deliveries", "CH240", "","","html"
//  "file49","obj49","FE Prod Zone MP", "CH250", "","","html"
//  "file50","obj50","FE Prod Top City MP", "CH242","","","html"
//  "file51","obj51","FE Prod City Deliveries", "CH240", "","A2","Data"
//  "file52","obj52","FE Prod Hub Deliveries", "CH241", "","A2","Data"
//  "file53","obj53","FE Prod City MP ", "CH226", "","A2","Data"
//  "file54","obj54","FE Prod Hub MP", "CH243", "","A2","Data"
//  "file72","obj72","FE Prod Raw Data", "CH323", "","A2","Data"
//
//  "file55","obj55","NDR City", "CH260", "","A2","Data"
//  "file56","obj56","NRD Hub", "CH259", "","A2","Data"
//
//  "file57","obj57","Pending Reconciliation Report City", "CH322", "","","html"
//  "file58","obj58","Pending Reconciliation Report Hub", "CH321", "","","html"
//  "file72","obj78","Cash Reconciliation Report Hub", "CH300", "","","html"
//  "file73","obj79","Cash Reconciliation Report Hub", "CH313", "","","html"
//  "file71","obj71","Pending Report Raw Data", "TB60", "","A2","Data"
//  "file74","obj80","Cash Recon Report Raw Data", "TB62", "","A2","Data"
//
//  "file59","obj59","Integrated Device Report City(Summary)", "CH279", "","","html"
//  "file60","obj60","POS Report City(Summary)", "CH277", "","","html"
//  "file61","obj61","Pinelab Report City(Summary)", "CH278", "","","html"
//  "file62","obj62","Smart Devices Report city(Summary)", "CH283", "","","html"
//
//  "file69","obj69","Integrated Device Report Zone", "CH324", "","A2","Data"
//  "file70","obj70","Integrated Device Report Region", "CH325", "","A2","Data"
//  "file63","obj63","Integrated Device Report City", "CH289", "","A2","Data"
//  "file64","obj64","Integrated Device Report Hub", "CH290", "","A2","Data"
//  "file65","obj65","Integrated Device Report FE", "CH291", "","A2","Data"
//  "file66","obj66","POS Hub", "CH292", "","A2","Data"
//  "file67","obj67","Pinelabs Hub", "CH293", "","A2","Data"
//  "file68","obj68","Smart Devices Hub", "CH295", "","A2","Data"
//  ];
//
//// "file16","obj16","Receive/OFD Zone", "CH167", "","","html"
////  "file17","obj17","Receive/OFD State", "CH174", "","","html"
////  "file18","obj18","Receive/OFD City", "CH168", "","A2","Data"
////  "file19","obj19","Receive/OFD Hub", "CH158", "","A2","Data"
//
//
///*
// "file15","obj15","POS Devices Reconciliation Report City", "CH119", "","","html"
//  "file16","obj16","POS Devices Reconciliation Report Hub", "CH116", "","A2","Data"
//* For each Object to copy, stores different selections it requires before mailing
//* Each Object will represents one selectionBoxId from front end
//* This SelectionBox will be created and stored on front-end using snapshot button
//* You can have same selection for different objects
//* objId : primary key and foreign key
//*/
////SelectionEnity:
////Load * Inline
////[ objId, selectionBoxId
//// "obj1","selectionBox1"
//// "obj2","selectionBox2"
//// "obj3","selectionBox3"
//// "obj4","selectionBox4"
//// "obj5","selectionBox5"
//// "obj6","selectionBox6"
//// "obj7","selectionBox7"
//// ];
//// "obj16","selectionBox15"
//SelectionEnity:
//Load * Inline
//[ objId, selectionBoxId
// "obj1","selectionBox1"
// "obj2","selectionBox2"
// "obj3","selectionBox3"
// "obj4","selectionBox4"
// "obj5","selectionBox5"
// "obj6","selectionBox6"
// "obj7","selectionBox7"
// "obj8","selectionBox8"
//
//  "obj9","selectionBox9"
//  "obj10","selectionBox10"
//  "obj11","selectionBox11"
//
//  "obj12","selectionBox12"
//  "obj13","selectionBox13"
//
//  "obj14","selectionBox14"
//   "obj15","selectionBox20"
//
//
//
//  "obj17","selectionBox16"
//  "obj18","selectionBox17"
//  "obj19","selectionBox18"
//  "obj20","selectionBox19"
//
//  "obj73","selectionBox73"
//  "obj74","selectionBox74"
//  "obj75","selectionBox75"
//  "obj76","selectionBox76"
//  "obj77","selectionBox77"
//
//  "obj21","selectionBox21"
//  "obj22","selectionBox22"
//  "obj23","selectionBox23"
//  "obj24","selectionBox24"
//
//  "obj25","selectionBox25"
//  "obj26","selectionBox26"
//  "obj27","selectionBox27"
//
//  "obj28","selectionBox28"
//  "obj29","selectionBox29"
// "obj30","selectionBox30"
// "obj31","selectionBox31"
//
//  "obj32","selectionBox32"
//  "obj33","selectionBox33"
//
//  "obj34","selectionBox34"
//  "obj35","selectionBox35"
//
//  "obj36","selectionBox36"
//  "obj37","selectionBox37"
//  "obj38","selectionBox38"
//  "obj39","selectionBox39"
//  "obj40","selectionBox40"
//  "obj41","selectionBox41"
//  "obj42","selectionBox42"
//  "obj43","selectionBox43"
//  "obj44","selectionBox44"
//  "obj45","selectionBox45"
//  "obj46","selectionBox46"
//  "obj47","selectionBox47"
//  "obj48","selectionBox48"
//  "obj49","selectionBox49"
//  "obj50","selectionBox50"
//  "obj51","selectionBox51"
//  "obj52","selectionBox52"
//  "obj53","selectionBox53"
//  "obj54","selectionBox54"
//  "obj72","selectionBox72"
//
//  "obj55","selectionBox55"
//  "obj56","selectionBox56"
//
//  "obj57","selectionBox57"
//  "obj58","selectionBox58"
//  "obj71","selectionBox71"
//  "obj78","selectionBox78"
//  "obj79","selectionBox79"
//  "obj80","selectionBox80"
//
//  "obj69","selectionBox69"
//  "obj70","selectionBox70"
//   "obj59","selectionBox59"
//  "obj60","selectionBox60"
//   "obj61","selectionBox61"
//  "obj62","selectionBox62"
//   "obj63","selectionBox63"
//  "obj64","selectionBox64"
//   "obj65","selectionBox65"
//  "obj66","selectionBox66"
//   "obj67","selectionBox67"
//  "obj68","selectionBox68"
//
//  ];
//
////  "obj16","selectionBox16"
////  "obj17","selectionBox17"
////  "obj18","selectionBox17"
////  "obj19","selectionBox17"