Untitled
unknown
plain_text
2 months ago
99 kB
4
Indexable
/*******************************************************************************
* CONFIDENTIAL, PROPRIETARY & TRADE SECRET PROPERTY OF WASTE MANAGEMENT NOT FOR USE OUTSIDE OF WASTE MANAGEMENT
* Copyright (C) 2019 Waste Management, Inc.
******************************************************************************/
package com.wml.ts.dao;
import static com.datastax.oss.driver.api.querybuilder.QueryBuilder.bindMarker;
import static com.datastax.oss.driver.api.querybuilder.QueryBuilder.deleteFrom;
import static com.datastax.oss.driver.api.querybuilder.QueryBuilder.insertInto;
import static com.datastax.oss.driver.api.querybuilder.QueryBuilder.selectFrom;
import static com.datastax.oss.driver.api.querybuilder.QueryBuilder.update;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.SparkSession;
import com.datastax.oss.driver.api.core.cql.Row;
import com.datastax.oss.driver.api.querybuilder.delete.Delete;
import com.datastax.oss.driver.api.querybuilder.insert.RegularInsert;
import com.datastax.oss.driver.api.querybuilder.select.Select;
import com.datastax.oss.driver.api.querybuilder.update.Update;
import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.wml.gis.model.CalcTarget;
import com.wml.ts.dao.model.CalcErrorLog;
import com.wml.ts.dao.model.CalcException;
import com.wml.ts.dao.model.RouteDetailResponse;
import com.wml.ts.model.JobStatusHistory;
import com.wml.ts.model.TicketEventDetail;
import com.wml.ts.util.TableConstants;
public interface CalculationDao {
String GET_CALC_STATUS_BY_CONFIRMATION_DATE_RANGE = "select * from " + TableConstants.TS_SCHED_CALC_STATUS + " where confirm_dt='2025-02-24' and rt_dt='2025-02-24' and site='S04800' and lob='O' and emp_id='020719'";
//String GET_CALC_STATUS_BY_CONFIRMATION_DATE_RANGE = "select * from " + TableConstants.TS_SCHED_CALC_STATUS + " where confirm_dt='2025-02-04' and rt_dt='2025-02-04' and site='S00752' and lob='C' and emp_id='132098'";
//String GET_CALC_STATUS_BY_CONFIRMATION_DATE_RANGE = "select * from " + TableConstants.TS_SCHED_CALC_STATUS + " where rt_dt='2020-05-20'";
String GET_CALC_STATUS_BY_CONFIRMATION_DATE = "select * from " + TableConstants.TS_SCHED_CALC_STATUS + " where confirm_dt = '%s'";
/*String GET_DT_CALC_STATUS_BY_REQUEST_DATE_RANGE = "select * from " + TableConstants.M100_WML +
+ TableConstants.RTO_SCHED_CALC_STATUS_DT + " where request_dt in ('%s', '%s')";
*/
String GET_DT_CALC_STATUS_BY_REQUEST_DATE_RANGE = "select * from "+ TableConstants.RTO_SCHED_CALC_STATUS_DT + " where request_dt in ('%s', '%s')";
//String GET_DT_CALC_STATUS_BY_REQUEST_DATE_RANGE = "select * from "+ TableConstants.RTO_SCHED_CALC_STATUS_DT + " where request_dt='2019-11-26' and rt_dt='2019-11-25' and lob='C'";
String GET_DP_CALC_STATUS_BY_REQUEST_DATE_RANGE = "select * from "+ TableConstants.RTO_SCHED_CALC_STATUS_DP + " where request_dt in ('%s', '%s')";
//String GET_DP_CALC_STATUS_BY_REQUEST_DATE_RANGE = "select * from "+ TableConstants.RTO_SCHED_CALC_STATUS_DP + " where request_dt='2019-11-26' and rt_dt='2019-11-25' and lob='O'";
Select GET_RO_CO_RESULT = selectFrom(TableConstants.WM_CSD_DISP_DRVR_KS , TableConstants.RO_CO_RESULT)
.columns("rt_dt","site","lob","emp_id","ro_key","etype","rec_id","co_key","customerid","customeruniqueid","fac_id","disp_ts","arr_ts","dept_ts","tkt_ts","start_ts","end_ts","gps_ts","lat","lon","seq","stopticketstamp","restartticketstamp","rt_id","event_id","load_type","returntripstamp","fac_cd","cont_yardcd","loadtypdesc","v_id","fac_nm" ,"source","gps_ts_start","lat_start","lon_start","fac_pricing_cd","fac_pricing_id","fac_pricing_nm","tandemticketobjid")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("ro_key").isEqualTo(bindMarker());
// 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
// RegularStatement GET_RO_CO_RESULT = select("rt_dt","site","lob","emp_id","ro_key","etype","rec_id","co_key","customerid","customeruniqueid","fac_id","disp_ts","arr_ts","dept_ts","tkt_ts","start_ts","end_ts","gps_ts","lat","lon","seq","stopticketstamp","restartticketstamp","rt_id","event_id","load_type","returntripstamp","fac_cd","cont_yardcd","loadtypdesc","v_id","fac_nm" ,"source","gps_ts_start","lat_start","lon_start","fac_pricing_cd","fac_pricing_id","fac_pricing_nm","tandemticketobjid")
// .from(TableConstants.M100_WML, TableConstants.RO_CO_RESULT)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(in("ro_key", bindMarker()));
Select GET_RO_CO_EVENT = selectFrom(TableConstants.WM_CSD_DISP_DRVR_KS , TableConstants.RO_CO_EVENT)
.columns("rt_dt","site","lob","emp_id","ro_key","etype","event_id","rec_id","co_key","co_svc_uni_id","customerid","customeruniqueid","gps_ts","seq","rt_id","v_id","start_ts","end_ts","lat","lon","load_type","loadtypdesc")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("ro_key").isEqualTo(bindMarker());
// 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
// RegularStatement GET_RO_CO_EVENT = select("rt_dt","site","lob","emp_id","ro_key","etype","event_id","rec_id","co_key","co_svc_uni_id","customerid","customeruniqueid","gps_ts","seq","rt_id","v_id","start_ts","end_ts","lat","lon","load_type","loadtypdesc")
// .from(TableConstants.M100_WML, TableConstants.RO_CO_EVENT)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("ro_key", bindMarker()));
Select GET_RO_CUSTOMER_ORDER_DT = selectFrom(TableConstants.M100_WML , TableConstants.RO_CUSTOMER_ORDER_DT)
.columns("rt_dt","site","ro_key","co_key","auditmode","autoservebothside","cancel_cutoff","city","comments","cust_id","cust_nm","cust_uniq_id","descr","drvr_key","drvr_ln","emp_nm","grp_name","house","lat","lob","lon","ord_no","ord_seq","orginal_rt","oth_routes","ph","prod_area","resi_auto_status","resn_cd","ro_co_status","rt_id","short_desc","site_key","site_pkey","source","state","street","sum_qty","sum_yrds","trans_id","v_id","virtualcircle","zip","ticketno","service_info","cancelled_flg","cutoff_flg","rt_seq","waste_typ","map_grid","load_typ_oid","uom","mp_cd","lf_oid","cy_oid","cy_nm","cy_cd","dscd","nm","txt")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("ro_key").isEqualTo(bindMarker());
// 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
// RegularStatement GET_RO_CUSTOMER_ORDER_DT = select("rt_dt","site","ro_key","co_key","auditmode","autoservebothside","cancel_cutoff","city","comments","cust_id","cust_nm","cust_uniq_id","descr","drvr_key","drvr_ln","emp_nm","grp_name","house","lat","lob","lon","ord_no","ord_seq","orginal_rt","oth_routes","ph","prod_area","resi_auto_status","resn_cd","ro_co_status","rt_id","short_desc","site_key","site_pkey","source","state","street","sum_qty","sum_yrds","trans_id","v_id","virtualcircle","zip","ticketno","service_info","cancelled_flg","cutoff_flg","rt_seq","waste_typ","map_grid","load_typ_oid","uom","mp_cd","lf_oid","cy_oid","cy_nm","cy_cd","dscd","nm","txt")
// .from(TableConstants.M100_WML, TableConstants.RO_CUSTOMER_ORDER_DT)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("ro_key", bindMarker()));
Select GET_ROUTE_MAP_DETAILS = selectFrom(TableConstants.M100_WML , TableConstants.ROUTE_MAP)
.columns("rt_dt","site","lob","rt_id","ro_key","ro_status","emp_id","confirm_status","v_id","containerdelivery","audit_ts")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("rt_id").isEqualTo(bindMarker());
// 0 1 2 3 4 5 6 7 8 9 10
// RegularStatement GET_ROUTE_MAP_DETAILS = select("rt_dt"," site"," lob"," rt_id"," ro_key"," ro_status"," emp_id"," confirm_status"," v_id"," containerdelivery","audit_ts")
// .from(TableConstants.M100_WML, TableConstants.ROUTE_MAP)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("rt_id", bindMarker()));
Select GET_RO_DEPO_PARAMS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.RO_DEPO_PARAMS)
.columns("x_coord","y_coord","site","roads_uid","address_1","city","state","zip","operation_area_nm","pre_trip_tm","post_trip_tm")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("dow").isEqualTo(bindMarker());
// 0 1 2 3 4 5 6 7 8 9 10
// RegularStatement GET_RO_DEPO_PARAMS = select("x_coord"," y_coord"," site"," roads_uid"," address_1"," city"," state"," zip"," operation_area_nm"," pre_trip_tm"," post_trip_tm")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.RO_DEPO_PARAMS)
// .where(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("dow", bindMarker()));
// 0 1 2 3 4 5 6 7 8 9
/*RegularStatement GET_ERL_ROUTE_PARAMETERS = select("site","lob","rt_id","sub_lob","a_post_rt","a_pre_rt","a_rt_st","p_post_rt","p_pre_rt","p_route_st")
.from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_ROUTE_PARAMETERS)
.where(eq("site", bindMarker()))
.and(eq("rt_id", bindMarker()))
.and(eq("lob", bindMarker()));*/
Select GET_ERL_ROUTE_PARAMETERS_V_ONE = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_ROUTE_PARAMETERS_V_ONE)
.columns("site","lob","rt_id","rt_dt","sub_lob","a_post_rt","a_pre_rt","a_rt_st","p_post_rt","p_pre_rt","p_route_st")
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("rt_id").isEqualTo(bindMarker());
// 0 1 2 3 4 5 6 7 8 9 10
// RegularStatement GET_ERL_ROUTE_PARAMETERS_V_ONE = select("site","lob","rt_id","rt_dt","sub_lob","a_post_rt","a_pre_rt","a_rt_st","p_post_rt","p_pre_rt","p_route_st")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_ROUTE_PARAMETERS_V_ONE)
// .where(eq("lob", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("rt_dt", bindMarker()))
// .and(eq("rt_id", bindMarker()));
Select GET_ERL_DEPOT_PARAMETERS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_DEPOT_PARAMETERS)
.columns("x_coord","y_coord","site","roads_uid","address_1","city","state","zip","operation_area_nm")
.whereColumn("site").isEqualTo(bindMarker());
// 0 1 2 3 4 5 6 7 8
// RegularStatement GET_ERL_DEPOT_PARAMETERS = select("x_coord","y_coord","site","roads_uid","address_1","city","state","zip","operation_area_nm")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_DEPOT_PARAMETERS)
// .where(eq("site", bindMarker()));
Select GET_ERL_ROUTE_MASTER_FOR_3_PARAMS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_ROUTE_MASTER)
.columns("rt_typ")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("rt_no").isEqualTo(bindMarker())
.whereColumn("day_of_wk").isEqualTo(bindMarker());
// RegularStatement GET_ERL_ROUTE_MASTER_FOR_3_PARAMS = select("rt_typ")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_ROUTE_MASTER)
// .where(eq("site", bindMarker()))
// .and(eq("rt_no", bindMarker()))
// .and(eq("day_of_wk", bindMarker()));
Select GET_ERL_ROUTE_MASTER_FOR_2_PARAMS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_ROUTE_MASTER)
.columns("rt_typ")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("rt_no").isEqualTo(bindMarker());
// RegularStatement GET_ERL_ROUTE_MASTER_FOR_2_PARAMS = select("rt_typ")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_ROUTE_MASTER)
// .where(eq("site", bindMarker()))
// .and(eq("rt_no", bindMarker()));
Select GET_ERL_FACILITY_PARAMETERS_4_PARAMETERS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_FACILITY_PARAMETERS)
.columns("site","lob","dow","fac_cd","fac_nm","avg_srvc_tm","roads_uid","st_side","x","y","address_1","city","state","zip","fac_id","lat","lon")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("fac_id").isEqualTo(bindMarker())
.whereColumn("dow").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker());
// RegularStatement GET_ERL_FACILITY_PARAMETERS_4_PARAMETERS = select("site","lob","dow","fac_cd","fac_nm","avg_srvc_tm","roads_uid","st_side","x","y","address_1","city","state","zip","fac_id","lat","lon")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_FACILITY_PARAMETERS)
// .where(eq("site", bindMarker()))
// .and(eq("fac_id", bindMarker()))
// .and(eq("dow", bindMarker()))
// .and(eq("lob", bindMarker()));
Select GET_RO_FACILITY_PARAMETERS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.RO_FACILITY_PARAMETERS)
.columns("site","dow","fac_nm","srvc_tm","roads_uid","side_of_strt","x","y","address","city","state","zip","fac_id","lat","lon","geo_addr","geo_city","geo_cntry","geo_lat","geo_lon","geo_rds_uid","geo_side_of_st","geo_state","geo_status","geo_x","geo_y","geo_zip","fac_mat_typ_cd")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("fac_mat_typ_cd").isEqualTo(bindMarker())
.whereColumn("dow").isEqualTo(bindMarker());
// RegularStatement GET_RO_FACILITY_PARAMETERS = select("site","dow","fac_nm","srvc_tm","roads_uid","side_of_strt","x","y","address","city","state","zip","fac_id","lat","lon","geo_addr","geo_city","geo_cntry","geo_lat","geo_lon","geo_rds_uid","geo_side_of_st","geo_state","geo_status","geo_x","geo_y","geo_zip","fac_mat_typ_cd")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.RO_FACILITY_PARAMETERS)
// .where(eq("site", bindMarker()))
// .and(eq("fac_mat_typ_cd", bindMarker()))
// .and(eq("dow", bindMarker()));
Select GET_RO_FACILITY_PARAMETERS_BY_SITE_DOW = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.RO_FACILITY_PARAMETERS)
.columns("site","dow","fac_mat_typ_cd","fac_nm","srvc_tm","roads_uid","side_of_strt","x","y","address","city","state","zip","fac_id","lat","lon","geo_addr","geo_city","geo_cntry","geo_lat","geo_lon","geo_rds_uid","geo_side_of_st","geo_state","geo_status","geo_x","geo_y","geo_zip","fac_mat_typ_cd")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("dow").isEqualTo(bindMarker());
// RegularStatement GET_RO_FACILITY_PARAMETERS_BY_SITE_DOW = select("site","dow","fac_mat_typ_cd","fac_nm","srvc_tm","roads_uid","side_of_strt","x","y","address","city","state","zip","fac_id","lat","lon","geo_addr","geo_city","geo_cntry","geo_lat","geo_lon","geo_rds_uid","geo_side_of_st","geo_state","geo_status","geo_x","geo_y","geo_zip","fac_mat_typ_cd")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.RO_FACILITY_PARAMETERS)
// .where(eq("site", bindMarker()))
// .and(eq("dow", bindMarker()));
Select GET_DISPOSAL_CYCLE_TM = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.DISPOSAL_CYCLE_TM)
.columns("srvc_tm")
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("fac_cd").isEqualTo(bindMarker())
.whereColumn("cntnr_typ_desc").isEqualTo(bindMarker())
.whereColumn("cntnr_size_cd").isEqualTo(bindMarker());
Select GET_DISPOSAL_CYCLE_TM_OCS = selectFrom(TableConstants.WM_CSD_DR_STAGGING , TableConstants.DISPOSAL_CYCLE_TM_OCS)
.columns("srvc_tm")
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("fac_cd").isEqualTo(bindMarker())
.whereColumn("cntnr_typ_desc").isEqualTo(bindMarker())
.whereColumn("cntnr_size_cd").isEqualTo(bindMarker());
// RegularStatement GET_DISPOSAL_CYCLE_TM = select("srvc_tm")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.DISPOSAL_CYCLE_TM)
// .where(eq("lob", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("rt_dt", bindMarker()))
// .and(eq("fac_cd", bindMarker()))
// .and(eq("cntnr_typ_desc", bindMarker()))
// .and(eq("cntnr_size_cd", bindMarker()));
Select GET_RO_EMP_VEHICLE_PARAMETERS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.RO_EMP_VEHICLE_PARAMETERS)
.columns("site","dow","emp_id","route_no","vhcl_label","vehicle_id","driver_cd","el_address","el_city","el_roads_uid","el_state","el_x","el_y","el_zone","first_name","last_name","sl_address","sl_city","sl_roads_uid","sl_state","sl_x","sl_y","sl_zone","end_loc_typ_desc","start_loc_typ_desc")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("dow").isEqualTo(bindMarker())
.whereColumn("route_no").isEqualTo(bindMarker());
// 0 1 2 3 4 5 6 7 8 9 10 11 12 14 15 16 17 18 19 20 21 22 23 24 25
// RegularStatement GET_RO_EMP_VEHICLE_PARAMETERS = select("site","dow","emp_id","route_no","vhcl_label","vehicle_id","driver_cd","el_address","el_city","el_roads_uid","el_state","el_x","el_y","el_zone","first_name","last_name","sl_address","sl_city","sl_roads_uid","sl_state","sl_x","sl_y","sl_zone","end_loc_typ_desc","start_loc_typ_desc")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.RO_EMP_VEHICLE_PARAMETERS)
// .where(eq("site", bindMarker()))
// .and(eq("dow", bindMarker()))
// .and(eq("route_no", bindMarker()));
Select GET_TRAFFIC_COAFFICIENT = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_TRAFFIC_COEFFICIENT_RO)
.columns("site","lob","speed_cat","coefficient_val")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker());
// 0 1 2 3
// RegularStatement GET_TRAFFIC_COAFFICIENT = select("site","lob","speed_cat","coefficient_val")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_TRAFFIC_COEFFICIENT_RO)
// .where(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()));
Select GET_ERL_SERVICE_CODE_PARAMETERS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_SERVICE_CODE_PARAMETERS)
.columns("ocs_srvc_cd","erl_srvc_cd","equi_hauls")
.whereColumn("ocs_srvc_cd").isEqualTo(bindMarker());
// RegularStatement GET_ERL_SERVICE_CODE_PARAMETERS = select("ocs_srvc_cd","erl_srvc_cd","equi_hauls")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_SERVICE_CODE_PARAMETERS)
// .where(eq("ocs_srvc_cd", bindMarker()));
Select GET_RO_CUSTOMER_PARAMETERS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.RO_CUSTOMER_PARAMETERS)
.columns("library_nm","site","mas_unique_id","srvc_tm","x_coord","y_coord","address","roads_uid","city","state","zip")
.whereColumn("library_nm").isEqualTo(bindMarker())
.whereColumn("mas_unique_id").isEqualTo(bindMarker());
// RegularStatement GET_RO_CUSTOMER_PARAMETERS = select("library_nm","site","mas_unique_id","srvc_tm","x_coord","y_coord","address","roads_uid","city","state","zip")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.RO_CUSTOMER_PARAMETERS)
// .where(eq("library_nm", bindMarker()))
// .and(eq("mas_unique_id", bindMarker()));
Select GET_RO_CUSTOMER_PARAMETERS_4_PARAMETERS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.RO_CUSTOMER_PARAMETERS)
.columns("library_nm","site","mas_unique_id","srvc_tm","x_coord","y_coord","address","roads_uid","city","state","zip")
.whereColumn("library_nm").isEqualTo(bindMarker())
.whereColumn("mas_unique_id").isEqualTo(bindMarker())
.whereColumn("cntnr_typ").isEqualTo(bindMarker())
.whereColumn("srvc_cd").isEqualTo(bindMarker());
// RegularStatement GET_RO_CUSTOMER_PARAMETERS_4_PARAMETERS = select("library_nm","site","mas_unique_id","srvc_tm","x_coord","y_coord","address","roads_uid","city","state","zip")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.RO_CUSTOMER_PARAMETERS)
// .where(eq("library_nm", bindMarker()))
// .and(eq("mas_unique_id", bindMarker()))
// .and(eq("cntnr_typ", bindMarker()))
// .and(eq("srvc_cd", bindMarker()));
Select GE_ERL_SERVICE_TIME_BY_CNTNR_LOAD_TYPE = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_SERVICE_TIME_BY_CNTNR_LOAD_TYPE)
.columns("srvc_tm")
.whereColumn("cntnr_typ").isEqualTo(bindMarker())
.whereColumn("srvc_cd").isEqualTo(bindMarker());
// RegularStatement GE_ERL_SERVICE_TIME_BY_CNTNR_LOAD_TYPE = select("srvc_tm")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_SERVICE_TIME_BY_CNTNR_LOAD_TYPE)
// .where(eq("cntnr_typ", bindMarker()))
// .and(eq("srvc_cd", bindMarker()));
Select GET_RO_CUSTOMER_ORDER = selectFrom(TableConstants.WM_CSD_DISP_DRVR_KS , TableConstants.RO_CUSTOMER_ORDER)
.columns("rt_dt","site","ro_key","co_key","auditmode","autoservebothside","cancel_cutoff","city","comments","cust_id","cust_nm","cust_uniq_id","descr","drvr_key","drvr_ln","emp_nm","grp_name","house","lat","lob","lon","ord_no","ord_seq","orginal_rt","oth_routes","ph","prod_area","resi_auto_status","resn_cd","ro_co_status","rt_id","short_desc","site_key","site_pkey","source","state","street","sum_qty","sum_yrds","trans_id","v_id","virtualcircle","zip","ticketno","service_info","cancelled_flg","cutoff_flg","rt_seq","waste_typ","map_grid","load_typ_oid","uom","mp_cd","lf_oid","cy_oid","cy_nm","cy_cd","dscd","nm","txt","library_cd","company_cd")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("ro_key").isEqualTo(bindMarker());
// 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
// RegularStatement GET_RO_CUSTOMER_ORDER = select("rt_dt","site","ro_key","co_key","auditmode","autoservebothside","cancel_cutoff","city","comments","cust_id","cust_nm","cust_uniq_id","descr","drvr_key","drvr_ln","emp_nm","grp_name","house","lat","lob","lon","ord_no","ord_seq","orginal_rt","oth_routes","ph","prod_area","resi_auto_status","resn_cd","ro_co_status","rt_id","short_desc","site_key","site_pkey","source","state","street","sum_qty","sum_yrds","trans_id","v_id","virtualcircle","zip","ticketno","service_info","cancelled_flg","cutoff_flg","rt_seq","waste_typ","map_grid","load_typ_oid","uom","mp_cd","lf_oid","cy_oid","cy_nm","cy_cd","dscd","nm","txt")
// .from(TableConstants.M100_WML, TableConstants.RO_CUSTOMER_ORDER)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("ro_key", bindMarker()));
Select GET_STG_OCS_SITE_MASTER = selectFrom(TableConstants.WM_CSD_DR_STAGGING , TableConstants.SITE_MASTER)
.columns("meal1duration","meal1hour","meal1paid","meal2duration","meal2hour","meal2paid","break1duration","break1hour","break1paid","break2duration","break2hour","break2paid","break3duration","break3hour","break3paid","timezone","company_code")
.whereColumn("site").isEqualTo(bindMarker());
// RegularStatement GET_SITE_MASTER = select("meal1duration","meal1hour","meal1paid","meal2duration","meal2hour","meal2paid","break1duration","break1hour","break1paid","break2duration","break2hour","break2paid","break3duration","break3hour","break3paid","timezone","company_code")
// .from(TableConstants.M100_WML, TableConstants.SITE_MASTER)
// .where(eq("site_cd", bindMarker()));
Select GET_KRONOS_MASTER = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.KRONOS_MASTER)
.columns("rt_dt","emp_id","st_tm","end_tm","name")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
// RegularStatement GET_KRONOS_MASTER = select("rt_dt","emp_id","st_tm","end_tm","name")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.KRONOS_MASTER)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("emp_id", bindMarker()));
Select GET_ERL_TRAFFIC_COEFFICIENT_CAT_4_PARAMS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_TRAFFIC_COEFFICIENT_CAT)
.columns("coefficient_cat")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("rt_id").isEqualTo(bindMarker())
.whereColumn("day_of_wk").isEqualTo(bindMarker())
.whereColumn("rt_typ").isEqualTo(bindMarker());
// RegularStatement GET_ERL_TRAFFIC_COEFFICIENT_CAT_4_PARAMS = select("coefficient_cat")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_TRAFFIC_COEFFICIENT_CAT)
// .where(eq("site", bindMarker()))
// .and(eq("rt_id", bindMarker()))
// .and(eq("day_of_wk", bindMarker()))
// .and(eq("rt_typ", bindMarker()));
Select GET_ERL_TRAFFIC_COEFFICIENT_CAT_2_PARAMS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_TRAFFIC_COEFFICIENT_CAT)
.columns("coefficient_cat")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("rt_id").isEqualTo(bindMarker());
// RegularStatement GET_ERL_TRAFFIC_COEFFICIENT_CAT_2_PARAMS = select("coefficient_cat")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_TRAFFIC_COEFFICIENT_CAT)
// .where(eq("site", bindMarker()))
// .and(eq("rt_id", bindMarker()));
Select GET_ERL_TRAFFIC_COEFFICIENT_VAL = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_TRAFFIC_COEFFICIENT_VAL)
.columns("site","traffic_condition_cd","speed_cat","coefficent_val","traffic_condition_desc")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("traffic_condition_cd").isEqualTo(bindMarker());
// RegularStatement GET_ERL_TRAFFIC_COEFFICIENT_VAL = QueryBuilder.select("site","traffic_condition_cd","speed_cat","coefficent_val","traffic_condition_desc")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_TRAFFIC_COEFFICIENT_VAL)
// .where(eq("site", bindMarker()))
// .and(eq("traffic_condition_cd", bindMarker()));
Select GET_LOADTYPE_MASTER = selectFrom(TableConstants.M100_WML , TableConstants.LOADTYPE_MASTER)
.columns("shortdesc")
.whereColumn("objectid").isEqualTo(bindMarker());
// RegularStatement GET_LOADTYPE_MASTER = select("shortdesc")
// .from(TableConstants.M100_WML, TableConstants.LOADTYPE_MASTER)
// .where(eq("objectid", bindMarker()));
Update UPDATE_TS_SCHED_CALC_STATUS = update(TableConstants.WM_CSD_DISP_DRVR_KS, TableConstants.TS_SCHED_CALC_STATUS)
.setColumn("calc_status", bindMarker())
.setColumn("publish_status", bindMarker())
.setColumn("calc_ts", bindMarker())
.whereColumn("confirm_dt").isEqualTo(bindMarker())
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
// RegularStatement UPDATE_TS_SCHED_CALC_STATUS = update(TableConstants.M100_WML, TableConstants.TS_SCHED_CALC_STATUS)
// .with(set("calc_status", bindMarker()))
// .and(set("publish_status", bindMarker()))
// .and(set("calc_ts", bindMarker()))
// .where(eq("confirm_dt", bindMarker()))
// .and(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("emp_id", bindMarker()));
Delete DELETE_ERL_ROUTE_DETAIL_RESPONSE = deleteFrom(TableConstants.M100_WML , TableConstants.ERL_ROUTE_DETAIL_RESPONSE)
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
// RegularStatement DELETE_ERL_ROUTE_DETAIL_RESPONSE = delete()
// .from(TableConstants.M100_WML, TableConstants.ERL_ROUTE_DETAIL_RESPONSE)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("emp_id", bindMarker()));
RegularInsert INSERT_ERL_ROUTE_DETAIL_RESPONSE = insertInto(TableConstants.M100_WML, TableConstants.ERL_ROUTE_DETAIL_RESPONSE)
.value("rt_dt", bindMarker("rt_dt"))
.value("site", bindMarker("site"))
.value("lob", bindMarker("lob"))
.value("emp_id", bindMarker("emp_id"))
.value("rt_id", bindMarker("rt_id"))
.value("sq_no", bindMarker("sq_no"))
.value("a_addr", bindMarker("a_addr"))
.value("a_arr_ts", bindMarker("a_arr_ts"))
.value("a_cus_nm", bindMarker("a_cus_nm"))
.value("a_dep_ts", bindMarker("a_dep_ts"))
.value("a_lat", bindMarker("a_lat"))
.value("a_long", bindMarker("a_long"))
.value("a_srvc_tm", bindMarker("a_srvc_tm"))
.value("a_trvl_km", bindMarker("a_trvl_km"))
.value("a_trvl_mi", bindMarker("a_trvl_mi"))
.value("a_trvl_tm", bindMarker("a_trvl_tm"))
.value("arr_ts", bindMarker("arr_ts"))
.value("dep_ts", bindMarker("dep_ts"))
.value("loc_cd", bindMarker("loc_cd"))
.value("loc_id", bindMarker("loc_id"))
.value("loc_nm", bindMarker("loc_nm"))
.value("loc_typ", bindMarker("loc_typ"))
.value("qty", bindMarker("qty"))
.value("ro_key", bindMarker("ro_key"))
.value("srvc_tm", bindMarker("srvc_tm"))
.value("trvl_km", bindMarker("trvl_km"))
.value("trvl_mi", bindMarker("trvl_mi"))
.value("trvl_tm", bindMarker("trvl_tm"))
.value("vhcl_id", bindMarker("vhcl_id"))
.value("x", bindMarker("x"))
.value("y", bindMarker("y"))
.value("yrds", bindMarker("yrds"))
.value("g_src", bindMarker("g_src"))
.value("srvc_src", bindMarker("srvc_src"))
.value("qck_pn_flg", bindMarker("qck_pn_flg"))
.value("dis_dif", bindMarker("dis_dif"))
.value("dis_dif_c", bindMarker("dis_dif_c"))
.value("co_key", bindMarker("co_key"))
.value("paid", bindMarker("paid"))
.value("cntnr_typ_cd", bindMarker("cntnr_typ_cd"))
.value("load_type", bindMarker("load_type"))
.value("eq_haul", bindMarker("eq_haul"))
.value("tkt_num", bindMarker("tkt_num"))
.value("idx", bindMarker("idx"))
.value("rec_id", bindMarker("rec_id"))
.value("p_seq", bindMarker("p_seq"))
.value("cust_trp_flg", bindMarker("cust_trp_flg"))
.value("stp_typ", bindMarker("stp_typ"))
.value("ntt_flag", bindMarker("ntt_flag"))
.value("cyr_lat", bindMarker("cyr_lat"))
.value("cyr_lon", bindMarker("cyr_lon"))
// .value("cyl_lat", bindMarker("cyl_lat"))
// .value("cyl_lon", bindMarker("cyl_lon"))
.value("cyr_x", bindMarker("cyr_x"))
.value("cyr_y", bindMarker("cyr_y"))
.value("cyt_tm", bindMarker("cyt_tm"))
.value("cyt_dist", bindMarker("cyt_dist"))
.value("unq_id", bindMarker("unq_id"))
.value("ott_tm", bindMarker("ott_tm"))
.value("ott_dist", bindMarker("ott_dist"));
RegularInsert INSERT_RTO_ROUTE_DETAIL_RESPONSE_DT = insertInto(TableConstants.M100_WML, TableConstants.RTO_ROUTE_DETAIL_RESPONSE_DT)
.value("rt_dt", bindMarker("rt_dt"))
.value("site", bindMarker("site"))
.value("lob", bindMarker("lob"))
.value("emp_id", bindMarker("emp_id"))
.value("rt_id", bindMarker("rt_id"))
.value("ro_key", bindMarker("ro_key"))
.value("sq_no", bindMarker("sq_no"))
.value("a_addr", bindMarker("a_addr"))
.value("a_arr_ts", bindMarker("a_arr_ts"))
.value("a_cus_nm", bindMarker("a_cus_nm"))
.value("a_dep_ts", bindMarker("a_dep_ts"))
.value("a_lat", bindMarker("a_lat"))
.value("a_long", bindMarker("a_long"))
.value("a_srvc_tm", bindMarker("a_srvc_tm"))
.value("a_trvl_km", bindMarker("a_trvl_km"))
.value("a_trvl_mi", bindMarker("a_trvl_mi"))
.value("a_trvl_tm", bindMarker("a_trvl_tm"))
.value("arr_ts", bindMarker("arr_ts"))
.value("cntnr_typ_cd", bindMarker("cntnr_typ_cd"))
.value("co_key", bindMarker("co_key"))
.value("dep_ts", bindMarker("dep_ts"))
.value("dis_dif", bindMarker("dis_dif"))
.value("dis_dif_c", bindMarker("dis_dif_c"))
.value("eq_haul", bindMarker("eq_haul"))
.value("g_src", bindMarker("g_src"))
.value("idx", bindMarker("idx"))
.value("load_type", bindMarker("load_type"))
.value("loc_cd", bindMarker("loc_cd"))
.value("loc_id", bindMarker("loc_id"))
.value("loc_nm", bindMarker("loc_nm"))
.value("loc_typ", bindMarker("loc_typ"))
.value("paid", bindMarker("paid"))
.value("qck_pn_flg", bindMarker("qck_pn_flg"))
.value("qty", bindMarker("qty"))
.value("srvc_src", bindMarker("srvc_src"))
.value("srvc_tm", bindMarker("srvc_tm"))
.value("trvl_km", bindMarker("trvl_km"))
.value("trvl_mi", bindMarker("trvl_mi"))
.value("trvl_tm", bindMarker("trvl_tm"))
.value("vhcl_id", bindMarker("vhcl_id"))
.value("x", bindMarker("x"))
.value("y", bindMarker("y"))
.value("yrds", bindMarker("yrds"))
.value("unq_id", bindMarker("unq_id"))
.value("tkt_num", bindMarker("tkt_num"));
RegularInsert INSERT_RTO_ROUTE_DETAIL_RESPONSE_DP = insertInto(TableConstants.M100_WML, TableConstants.RTO_ROUTE_DETAIL_RESPONSE_DP)
.value("rt_dt", bindMarker("rt_dt"))
.value("site", bindMarker("site"))
.value("lob", bindMarker("lob"))
.value("emp_id", bindMarker("emp_id"))
.value("rt_id", bindMarker("rt_id"))
.value("ro_key", bindMarker("ro_key"))
.value("sq_no", bindMarker("sq_no"))
.value("a_addr", bindMarker("a_addr"))
.value("a_arr_ts", bindMarker("a_arr_ts"))
.value("a_cus_nm", bindMarker("a_cus_nm"))
.value("a_dep_ts", bindMarker("a_dep_ts"))
.value("a_lat", bindMarker("a_lat"))
.value("a_long", bindMarker("a_long"))
.value("a_srvc_tm", bindMarker("a_srvc_tm"))
.value("a_trvl_km", bindMarker("a_trvl_km"))
.value("a_trvl_mi", bindMarker("a_trvl_mi"))
.value("a_trvl_tm", bindMarker("a_trvl_tm"))
.value("arr_ts", bindMarker("arr_ts"))
.value("cntnr_typ_cd", bindMarker("cntnr_typ_cd"))
.value("co_key", bindMarker("co_key"))
.value("dep_ts", bindMarker("dep_ts"))
.value("dis_dif", bindMarker("dis_dif"))
.value("dis_dif_c", bindMarker("dis_dif_c"))
.value("eq_haul", bindMarker("eq_haul"))
.value("g_src", bindMarker("g_src"))
.value("idx", bindMarker("idx"))
.value("load_type", bindMarker("load_type"))
.value("loc_cd", bindMarker("loc_cd"))
.value("loc_id", bindMarker("loc_id"))
.value("loc_nm", bindMarker("loc_nm"))
.value("loc_typ", bindMarker("loc_typ"))
.value("paid", bindMarker("paid"))
.value("qck_pn_flg", bindMarker("qck_pn_flg"))
.value("qty", bindMarker("qty"))
.value("srvc_src", bindMarker("srvc_src"))
.value("srvc_tm", bindMarker("srvc_tm"))
.value("trvl_km", bindMarker("trvl_km"))
.value("trvl_mi", bindMarker("trvl_mi"))
.value("trvl_tm", bindMarker("trvl_tm"))
.value("vhcl_id", bindMarker("vhcl_id"))
.value("x", bindMarker("x"))
.value("y", bindMarker("y"))
.value("yrds", bindMarker("yrds"))
.value("unq_id", bindMarker("unq_id"))
.value("tkt_num", bindMarker("tkt_num"));
RegularInsert INSERT_ROUTE_ORDER_TARGET = insertInto(TableConstants.M100_WML, TableConstants.ROUTE_ORDER_TARGET)
.value("site", bindMarker("site"))
.value("rt_dt", bindMarker("rt_dt"))
.value("lob", bindMarker("lob"))
.value("ro_key", bindMarker("ro_key"))
.value("rt_id", bindMarker("rt_id"))
.value("rs_req", bindMarker("rs_req"))
.value("rs_res", bindMarker("rs_res"))
.value("trgt_mtr", bindMarker("trgt_mtr"))
.value("ttl_cust", bindMarker("ttl_cust"))
.value("v_id", bindMarker("v_id"))
.value("emp_id", bindMarker("emp_id"))
.value("drvr_nm", bindMarker("drvr_nm"))
.value("lfts", bindMarker("lfts"))
.value("sgmt_mis_mch", bindMarker("sgmt_mis_mch"))
.value("a_cust_svc_tm", bindMarker("a_cust_svc_tm"))
.value("a_dis_svc_tm", bindMarker("a_dis_svc_tm"))
.value("a_dwn_tm", bindMarker("a_dwn_tm"))
.value("a_ttl_tm", bindMarker("a_ttl_tm"))
.value("a_trvl_tm", bindMarker("a_trvl_tm"))
.value("a_idl", bindMarker("a_idl"))
.value("a_lnch", bindMarker("a_lnch"))
.value("a_up_meal", bindMarker("a_up_meal"))
.value("a_post_rt", bindMarker("a_post_rt"))
.value("a_pre_rt", bindMarker("a_pre_rt"))
.value("a_yrds", bindMarker("a_yrds"))
.value("a_eff", bindMarker("a_eff"))
.value("a_dis_trp", bindMarker("a_dis_trp"))
.value("ap_cust_svc_tm", bindMarker("ap_cust_svc_tm"))
.value("ap_dis_svc_tm", bindMarker("ap_dis_svc_tm"))
.value("ap_p_meal", bindMarker("ap_p_meal"))
.value("ap_p_mis", bindMarker("ap_p_mis"))
.value("ap_post_rt", bindMarker("ap_post_rt"))
.value("ap_pre_rt", bindMarker("ap_pre_rt"))
.value("ap_ttl_tm", bindMarker("ap_ttl_tm"))
.value("ap_trvl_mi", bindMarker("ap_trvl_mi"))
.value("ap_trvl_tm", bindMarker("ap_trvl_tm"))
.value("ap_up_meal", bindMarker("ap_up_meal"))
.value("ap_up_mis", bindMarker("ap_up_mis"))
.value("ap_yrds", bindMarker("ap_yrds"))
.value("ap_eff", bindMarker("ap_eff"))
.value("ap_dis_trp", bindMarker("ap_dis_trp"))
.value("g_src_ocs_p", bindMarker("g_src_ocs_p"))
.value("stop", bindMarker("stop"))
.value("srvc_src_lob_p", bindMarker("srvc_src_lob_p"))
.value("srvc_src_rt_p", bindMarker("srvc_src_rt_p"))
.value("srvc_src_df_p", bindMarker("srvc_src_df_p"))
.value("srvc_src_cust_p", bindMarker("srvc_src_cust_p"))
.value("srvc_src_st_lob_rt_p", bindMarker("srvc_src_st_lob_rt_p"))
.value("qck_pn_p", bindMarker("qck_pn_p"))
.value("zr_xy", bindMarker("zr_xy"))
.value("clk_src", bindMarker("clk_src"))
.value("pre_rt_src", bindMarker("pre_rt_src"))
.value("total_hauls", bindMarker("total_hauls"))
.value("eq_hauls", bindMarker("eq_hauls"))
.value("mile_per_eqhauls", bindMarker("mile_per_eqhauls"))
.value("hauls_detail", bindMarker("hauls_detail"))
.value("EqHauls_Per_Hour", bindMarker("EqHauls_Per_Hour"))
.value("a_cy_srvc_tm", bindMarker("a_cy_srvc_tm"))
.value("ap_cy_srvc_tm", bindMarker("ap_cy_srvc_tm"))
.value("a_ntt", bindMarker("a_ntt"))
.value("ott_tm", bindMarker("ott_tm"))
.value("ott_dist", bindMarker("ott_dist"));
RegularInsert INSERT_TICKET_EVENT_DETAIL = insertInto(TableConstants.M100_WML, TableConstants.TICKET_EVENT_DETAIL)
.value("rt_dt", bindMarker("rt_dt"))
.value("site", bindMarker("site"))
.value("lob", bindMarker("lob"))
.value("emp_id", bindMarker("emp_id"))
.value("ro_key", bindMarker("ro_key"))
.value("co_key", bindMarker("co_key"))
.value("event_id", bindMarker("event_id"))
.value("event_desc", bindMarker("event_desc"))
.value("loc_type", bindMarker("loc_type"))
.value("loc_nm", bindMarker("loc_nm"))
.value("tkt_no", bindMarker("tkt_no"))
.value("event_ts", bindMarker("event_ts"))
.value("rec_id", bindMarker("rec_id"))
.value("rt_id", bindMarker("rt_id"))
.value("load_typ", bindMarker("load_typ"))
.value("cy_eq_cd", bindMarker("cy_eq_cd"))
.value("seq", bindMarker("seq"))
.value("tandemticketobjid", bindMarker("tandemticketobjid"));
RegularInsert INSERT_RTO_SUMMARY_RESPONSE_DT = insertInto(TableConstants.M100_WML, TableConstants.RTO_SUMMARY_RESPONSE_DT)
.value("rt_dt", bindMarker("rt_dt"))
.value("site", bindMarker("site"))
.value("lob", bindMarker("lob"))
.value("rt_id", bindMarker("rt_id"))
.value("ro_key", bindMarker("ro_key"))
.value("rs_req", bindMarker("rs_req"))
.value("rs_res", bindMarker("rs_res"))
.value("trgt_mtr", bindMarker("trgt_mtr"))
.value("ttl_cust", bindMarker("ttl_cust"))
.value("v_id", bindMarker("v_id"))
.value("emp_id", bindMarker("emp_id"))
.value("drvr_nm", bindMarker("drvr_nm"))
.value("lfts", bindMarker("lfts"))
.value("sgmt_mis_mch", bindMarker("sgmt_mis_mch"))
.value("ap_cust_svc_tm", bindMarker("ap_cust_svc_tm"))
.value("ap_dis_svc_tm", bindMarker("ap_dis_svc_tm"))
.value("ap_p_meal", bindMarker("ap_p_meal"))
.value("ap_p_mis", bindMarker("ap_p_mis"))
.value("ap_post_rt", bindMarker("ap_post_rt"))
.value("ap_pre_rt", bindMarker("ap_pre_rt"))
.value("ap_ttl_tm", bindMarker("ap_ttl_tm"))
.value("ap_trvl_mi", bindMarker("ap_trvl_mi"))
.value("ap_trvl_tm", bindMarker("ap_trvl_tm"))
.value("ap_up_meal", bindMarker("ap_up_meal"))
.value("ap_up_mis", bindMarker("ap_up_mis"))
.value("ap_yrds", bindMarker("ap_yrds"))
.value("ap_eff", bindMarker("ap_eff"))
.value("ap_dis_trp", bindMarker("ap_dis_trp"))
.value("g_src_ocs_p", bindMarker("g_src_ocs_p"))
.value("stop", bindMarker("stop"))
.value("srvc_src_lob_p", bindMarker("srvc_src_lob_p"))
.value("srvc_src_rt_p", bindMarker("srvc_src_rt_p"))
.value("srvc_src_df_p", bindMarker("srvc_src_df_p"))
.value("srvc_src_cust_p", bindMarker("srvc_src_cust_p"))
.value("srvc_src_st_lob_rt_p", bindMarker("srvc_src_st_lob_rt_p"))
.value("qck_pn_p", bindMarker("qck_pn_p"))
.value("zr_xy", bindMarker("zr_xy"))
.value("clk_src", bindMarker("clk_src"))
.value("pre_rt_src", bindMarker("pre_rt_src"))
.value("total_hauls", bindMarker("total_hauls"))
.value("eq_hauls", bindMarker("eq_hauls"))
.value("mile_per_eqhauls", bindMarker("mile_per_eqhauls"))
.value("hauls_detail", bindMarker("hauls_detail"))
.value("EqHauls_Per_Hour", bindMarker("EqHauls_Per_Hour"))
.value("trans_id", bindMarker("trans_id"));
RegularInsert INSERT_RTO_SUMMARY_RESPONSE_LOG_DT = insertInto(TableConstants.DIAGNOSTIC_TOOL, TableConstants.RTO_SUMMARY_RESPONSE_LOG_DT)
.value("rt_dt", bindMarker("rt_dt"))
.value("site", bindMarker("site"))
.value("lob", bindMarker("lob"))
.value("rt_id", bindMarker("rt_id"))
.value("ro_key", bindMarker("ro_key"))
.value("log", bindMarker("log"));
RegularInsert INSERT_RTO_SUMMARY_RESPONSE_DP = insertInto(TableConstants.M100_WML, TableConstants.RTO_SUMMARY_RESPONSE_DP)
.value("rt_dt", bindMarker("rt_dt"))
.value("site", bindMarker("site"))
.value("lob", bindMarker("lob"))
.value("rt_id", bindMarker("rt_id"))
.value("ro_key", bindMarker("ro_key"))
.value("rs_req", bindMarker("rs_req"))
.value("rs_res", bindMarker("rs_res"))
.value("trgt_mtr", bindMarker("trgt_mtr"))
.value("ttl_cust", bindMarker("ttl_cust"))
.value("v_id", bindMarker("v_id"))
.value("emp_id", bindMarker("emp_id"))
.value("drvr_nm", bindMarker("drvr_nm"))
.value("lfts", bindMarker("lfts"))
.value("sgmt_mis_mch", bindMarker("sgmt_mis_mch"))
.value("a_cust_svc_tm", bindMarker("a_cust_svc_tm"))
.value("a_dis_svc_tm", bindMarker("a_dis_svc_tm"))
.value("a_dwn_tm", bindMarker("a_dwn_tm"))
.value("a_ttl_tm", bindMarker("a_ttl_tm"))
.value("a_trvl_mi", bindMarker("a_trvl_mi"))
.value("a_trvl_tm", bindMarker("a_trvl_tm"))
.value("a_idl", bindMarker("a_idl"))
.value("a_lnch", bindMarker("a_lnch"))
.value("a_up_meal", bindMarker("a_up_meal"))
.value("a_post_rt", bindMarker("a_post_rt"))
.value("a_pre_rt", bindMarker("a_pre_rt"))
.value("a_yrds", bindMarker("a_yrds"))
.value("a_eff", bindMarker("a_eff"))
.value("a_dis_trp", bindMarker("a_dis_trp"))
.value("ap_cust_svc_tm", bindMarker("ap_cust_svc_tm"))
.value("ap_dis_svc_tm", bindMarker("ap_dis_svc_tm"))
.value("ap_p_meal", bindMarker("ap_p_meal"))
.value("ap_p_mis", bindMarker("ap_p_mis"))
.value("ap_post_rt", bindMarker("ap_post_rt"))
.value("ap_pre_rt", bindMarker("ap_pre_rt"))
.value("ap_ttl_tm", bindMarker("ap_ttl_tm"))
.value("ap_trvl_mi", bindMarker("ap_trvl_mi"))
.value("ap_trvl_tm", bindMarker("ap_trvl_tm"))
.value("ap_up_meal", bindMarker("ap_up_meal"))
.value("ap_up_mis", bindMarker("ap_up_mis"))
.value("ap_yrds", bindMarker("ap_yrds"))
.value("ap_eff", bindMarker("ap_eff"))
.value("ap_dis_trp", bindMarker("ap_dis_trp"))
.value("g_src_ocs_p", bindMarker("g_src_ocs_p"))
.value("stop", bindMarker("stop"))
.value("srvc_src_lob_p", bindMarker("srvc_src_lob_p"))
.value("srvc_src_rt_p", bindMarker("srvc_src_rt_p"))
.value("srvc_src_df_p", bindMarker("srvc_src_df_p"))
.value("srvc_src_cust_p", bindMarker("srvc_src_cust_p"))
.value("srvc_src_st_lob_rt_p", bindMarker("srvc_src_st_lob_rt_p"))
.value("qck_pn_p", bindMarker("qck_pn_p"))
.value("zr_xy", bindMarker("zr_xy"))
.value("clk_src", bindMarker("clk_src"))
.value("pre_rt_src", bindMarker("pre_rt_src"))
.value("total_hauls", bindMarker("total_hauls"))
.value("eq_hauls", bindMarker("eq_hauls"))
.value("mile_per_eqhauls", bindMarker("mile_per_eqhauls"))
.value("hauls_detail", bindMarker("hauls_detail"))
.value("EqHauls_Per_Hour", bindMarker("EqHauls_Per_Hour"))
.value("trans_id", bindMarker("trans_id"));
Update UPDATE_RTO_SCHED_CALC_STATUS_DT = update(TableConstants.M100_WML, TableConstants.RTO_SCHED_CALC_STATUS_DT)
.setColumn("calc_status", bindMarker())
.setColumn("publish_status", bindMarker())
.setColumn("additional_info", bindMarker())
.setColumn("calc_ts", bindMarker())
.whereColumn("request_dt").isEqualTo(bindMarker())
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("rt_id").isEqualTo(bindMarker())
.whereColumn("trans_id").isEqualTo(bindMarker());
// Update UPDATE_RTO_SCHED_CALC_STATUS_DT = update(TableConstants.M100_WML, TableConstants.RTO_SCHED_CALC_STATUS_DT)
// .with(set("calc_status", bindMarker()))
// .and(set("publish_status", bindMarker()))
// .and(set("additional_info", bindMarker()))
// .and(set("calc_ts", bindMarker()))
// .where(eq("request_dt", bindMarker()))
// .and(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("rt_id", bindMarker()))
// .and(eq("trans_id", bindMarker()));
Update UPDATE_RTO_SCHED_CALC_STATUS_DP = update(TableConstants.M100_WML, TableConstants.RTO_SCHED_CALC_STATUS_DP)
.setColumn("calc_status", bindMarker())
.setColumn("publish_status", bindMarker())
.setColumn("calc_ts", bindMarker())
.whereColumn("request_dt").isEqualTo(bindMarker())
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker())
.whereColumn("trans_id").isEqualTo(bindMarker());
// RegularStatement UPDATE_RTO_SCHED_CALC_STATUS_DP = update(TableConstants.M100_WML, TableConstants.RTO_SCHED_CALC_STATUS_DP)
// .with(set("calc_status", bindMarker()))
// .and(set("publish_status", bindMarker()))
// .and(set("calc_ts", bindMarker()))
// .where(eq("request_dt", bindMarker()))
// .and(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("emp_id", bindMarker()))
// .and(eq("trans_id", bindMarker()));
Select GEL_RO_EMP_MAP = selectFrom(TableConstants.WM_CSD_DISP_DRVR_KS , TableConstants.RO_EMP_MAP)
.columns("rt_dt","site","lob","emp_id","ro_key","ro_status","confirm_status","v_id","rt_id","containerdelivery","audit_ts")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker());
// RegularStatement GEL_RO_EMP_MAP = select("rt_dt","site","lob","emp_id","ro_key","ro_status","confirm_status","v_id","rt_id","containerdelivery","audit_ts")
// .from(TableConstants.M100_WML, TableConstants.RO_EMP_MAP)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()));
Select GET_RO_CALC_EXCEPTION_3_PARAMS = selectFrom(TableConstants.DIAGNOSTIC_TOOL , TableConstants.RO_CALC_EXCEPTION)
.columns("rt_dt","site","lob","ro_key","emp_id","reason_code","reason_descr","status")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker());
Select GET_RO_CALC_EXCEPTION_4_PARAMS = selectFrom(TableConstants.DIAGNOSTIC_TOOL , TableConstants.RO_CALC_EXCEPTION)
.columns("rt_dt","site","lob","ro_key","emp_id","reason_code","reason_descr","status")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("ro_key").isEqualTo(bindMarker());
Select GET_RS_PERF = selectFrom(TableConstants.DIAGNOSTIC_TOOL , TableConstants.RS_PERF)
.columns("e_ts","pub_st")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
// RegularStatement GET_RS_PERF = select("e_ts","pub_st")
// .from(TableConstants.DIAGNOSTIC_TOOL, TableConstants.RS_PERF)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("emp_id", bindMarker()));
Select GET_RO_SITE_LIBRARY = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.RO_SITE_LIBRARY)
.columns("library_nm")
.whereColumn("site").isEqualTo(bindMarker());
// RegularStatement GET_RO_SITE_LIBRARY = select("library_nm")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.RO_SITE_LIBRARY)
// .where(eq("site", bindMarker()));
// Select GET_RO_CNTNR_PARAMETERS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.RO_CNTNR_PARAMETERS)
// .columns("src_svc_typ_cd","src_cntnr_typ_cd","cntnr_size_id","cntnr_size_cd","cntnr_typ_cd","cntnr_typ_desc","requested_volume","cntnr_typ_id","act_cntnr_typ_id","act_cntnr_typ_cd","act_cntnr_typ_desc","src_cntnr_typ_desc")
// .whereColumn("src_svc_typ_cd").isEqualTo(bindMarker());
Select GET_RO_CNTNR_PARAMETERS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.RO_CNTNR_PARAMETERS)
.columns("src_cntnr_typ_cd","cntnr_typ_id","requested_volume","self_contained_flg","src_cntnr_typ_desc","trailer_flg")
.whereColumn("src_cntnr_typ_cd").isEqualTo(bindMarker());
// RegularStatement GET_RO_CNTNR_PARAMETERS = select("src_svc_typ_cd","src_cntnr_typ_cd","cntnr_size_id","cntnr_size_cd","cntnr_typ_cd","cntnr_typ_desc","requested_volume","cntnr_typ_id","act_cntnr_typ_id","act_cntnr_typ_cd","act_cntnr_typ_desc")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.RO_CNTNR_PARAMETERS)
// .where(eq("src_svc_typ_cd", bindMarker()));
Select GET_TRUCK_WT_RESTRT_PARAMS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.TRUCK_WT_RESTRT_PARAMS)
.columns("site","rt_param_id","rt_param_cd","rt_param_val")
.whereColumn("site").isEqualTo(bindMarker());
// RegularStatement GET_TRUCK_WT_RESTRT_PARAMS = select("site","rt_param_id","rt_param_cd","rt_param_val")
// .from(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.TRUCK_WT_RESTRT_PARAMS)
// .where(eq("site", bindMarker()));
Delete DELETE_RO_CALC_EXCEPTION = deleteFrom(TableConstants.DIAGNOSTIC_TOOL, TableConstants.RO_CALC_EXCEPTION)
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("ro_key").isEqualTo(bindMarker());
// RegularStatement DELETE_RO_CALC_EXCEPTION = delete()
// .all()
// .from(TableConstants.DIAGNOSTIC_TOOL, TableConstants.RO_CALC_EXCEPTION)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(in("ro_key", bindMarker()));
Delete DELETE_RS_PERF = deleteFrom(TableConstants.DIAGNOSTIC_TOOL, TableConstants.RS_PERF)
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
// RegularStatement DELETE_RS_PERF = delete()
// .all()
// .from(TableConstants.DIAGNOSTIC_TOOL, TableConstants.RS_PERF)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("emp_id", bindMarker()));
Delete DELETE_TICKET_EVENT_DETAIL = deleteFrom(TableConstants.M100_WML, TableConstants.TICKET_EVENT_DETAIL)
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker())
.whereColumn("ro_key").isEqualTo(bindMarker());
// RegularStatement DELETE_TICKET_EVENT_DETAIL = delete()
// .all()
// .from(TableConstants.M100_WML, TableConstants.TICKET_EVENT_DETAIL)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("emp_id", bindMarker()))
// .and(eq("ro_key", bindMarker()));
Select GET_RO_EMP_MAP = selectFrom(TableConstants.WM_CSD_DISP_DRVR_KS, TableConstants.RO_EMP_MAP)
.columns("rt_dt","site","lob","emp_id","ro_key","ro_status","confirm_status","v_id","rt_id","containerdelivery","audit_ts")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
// RegularStatement GET_RO_EMP_MAP = select("rt_dt","site","lob","emp_id","ro_key","ro_status","confirm_status","v_id","rt_id","containerdelivery","audit_ts")
// .from(TableConstants.M100_WML, TableConstants.RO_EMP_MAP)
// .where(eq("rt_dt", bindMarker()))
// .and(eq("site", bindMarker()))
// .and(eq("lob", bindMarker()))
// .and(eq("emp_id", bindMarker()));
RegularInsert INSERT_CALC_ERROR_LOG = insertInto(TableConstants.M100_WML_LOG, TableConstants.CALC_ERROR_LOG)
.value("site", bindMarker())
.value("rt_dt", bindMarker())
.value("emp_id", bindMarker())
.value("ro_key", bindMarker())
.value("run_date", bindMarker())
.value("error_time", bindMarker())
.value("error", bindMarker())
.value("warning", bindMarker())
.value("stacktrace", bindMarker())
.value("lob", bindMarker());
RegularInsert INSERT_RO_CALC_EXCEPTION = insertInto(TableConstants.DIAGNOSTIC_TOOL, TableConstants.RO_CALC_EXCEPTION)
.value("rt_dt", bindMarker())
.value("site", bindMarker())
.value("lob", bindMarker())
.value("ro_key", bindMarker())
.value("emp_id", bindMarker())
.value("reason_code", bindMarker())
.value("reason_descr", bindMarker())
.value("rt_id", bindMarker())
.value("status", bindMarker())
.value("detail", bindMarker());
Select GET_ERL_CUSTOMER_PARAMETERS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_CUSTOMER_PARAMETERS)
.columns("site","lob_cd","route_typ","mas_cus_unique_id","avg_srvc_tm","x_coord","y_coord","address_1","roads_uid","city_nm","state_cd","zip_cd","f_cont_srvc_tm","o_cont_srvc_tm")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob_cd").isEqualTo(bindMarker())
.whereColumn("mas_cus_unique_id").isEqualTo(bindMarker());
Select GET_ERL_SERVICE_TIME_BY_SITE = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER ,TableConstants.ERL_SERVICE_TIME_BY_SITE)
.columns("site","lob","route_type","f_cont_srvc_tm","o_cont_srvc_tm")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker());
Delete DELETE_RTO_ROUTE_DETAIL_RESPONSE_DT_4_PARAMS = deleteFrom(TableConstants.M100_WML , TableConstants.RTO_ROUTE_DETAIL_RESPONSE_DT)
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("rt_id").isEqualTo(bindMarker());
Delete DELETE_RTO_ROUTE_DETAIL_RESPONSE_DT_5_PARAMS = deleteFrom(TableConstants.M100_WML , TableConstants.RTO_ROUTE_DETAIL_RESPONSE_DT)
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("rt_id").isEqualTo(bindMarker())
.whereColumn("ro_key").isEqualTo(bindMarker());
Delete DELETE_ROUTE_SUMMARY_DT = deleteFrom( TableConstants.M100_WML , TableConstants.RTO_SUMMARY_RESPONSE_DT)
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("rt_id").isEqualTo(bindMarker())
.whereColumn("ro_key").isEqualTo(bindMarker());
RegularInsert INSERT_ROUTE_SUMMARY = insertInto(TableConstants.DIAGNOSTIC_TOOL, TableConstants.ROUTE_SUMMARY)
.value("site", bindMarker("site"))
.value("rt_dt", bindMarker("rt_dt"))
.value("lob", bindMarker("lob"))
.value("ro_key", bindMarker("ro_key"))
.value("rt_id", bindMarker("rt_id"))
.value("emp_id", bindMarker("emp_id"))
.value("drvr_nm", bindMarker("drvr_nm"))
.value("v_id", bindMarker("v_id"))
.value("a_cust_svc_tm", bindMarker("a_cust_svc_tm"))
.value("a_dis_svc_tm", bindMarker("a_dis_svc_tm"))
.value("a_dwn_tm", bindMarker("a_dwn_tm"))
.value("a_idl", bindMarker("a_idl"))
.value("a_lnch", bindMarker("a_lnch"))
.value("a_ntt", bindMarker("a_ntt"))
.value("a_post_rt", bindMarker("a_post_rt"))
.value("a_pre_rt", bindMarker("a_pre_rt"))
.value("a_up_meal", bindMarker("a_up_meal"))
.value("a_trvl_tm", bindMarker("a_trvl_tm"))
.value("a_meal_count", bindMarker("a_meal_count"))
.value("a_cy_count", bindMarker("a_cy_count"))
.value("a_cy_srvc_tm", bindMarker("a_cy_srvc_tm"))
.value("ap_cust_svc_tm", bindMarker("ap_cust_svc_tm"))
.value("ap_cy_srvc_tm", bindMarker("ap_cy_srvc_tm"))
.value("ap_dis_svc_tm", bindMarker("ap_dis_svc_tm"))
.value("ap_p_meal", bindMarker("ap_p_meal"))
.value("ap_p_mis", bindMarker("ap_p_mis"))
.value("ap_post_rt", bindMarker("ap_post_rt"))
.value("ap_pre_rt", bindMarker("ap_pre_rt"))
.value("ap_trvl_mi", bindMarker("ap_trvl_mi"))
.value("ap_trvl_tm", bindMarker("ap_trvl_tm"))
.value("ap_up_meal", bindMarker("ap_up_meal"))
.value("ap_up_mis", bindMarker("ap_up_mis"))
.value("ap_misc_count", bindMarker("ap_misc_count"))
.value("ap_meal_count", bindMarker("ap_meal_count"))
.value("ap_cy_count", bindMarker("ap_cy_count"));
Select SELECT_ERL_RT_PARAMS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_RT_PARAMS)
.columns("site","lob","emp_id","rt_id","dow","plan_pre_rt_tm","plan_post_rt_tm")
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("rt_id").isEqualTo(bindMarker());
Select GET_ERROR_CODE_MASTER = selectFrom(TableConstants.M100_WML , TableConstants.ERROR_CODE_MASTER)
.columns("reason_code","reason_desc","error_type","detail")
.whereColumn("reason_code").isEqualTo(bindMarker());
Select GET_RO_CO_EVENT_SELECTIVE = selectFrom(TableConstants.WM_CSD_DISP_DRVR_KS , TableConstants.RO_CO_EVENT)
.columns("rt_dt","site","lob","emp_id","ro_key","etype","event_id","rec_id","co_key","co_svc_uni_id","customerid","customeruniqueid","gps_ts","seq","rt_id","v_id","start_ts","end_ts","lat","lon","load_type","loadtypdesc")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("ro_key").isEqualTo(bindMarker())
.whereColumn("etype").isEqualTo(bindMarker())
.whereColumn("event_id").in(bindMarker());
Select GET_ERL_FACILITY_PARAMETERS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_FACILITY_PARAMETERS)
.columns("site","lob","fac_cd","fac_nm","roads_uid","st_side","x","y","address_1","city","state","zip","fac_id","lat","lon","monday_avg_srvc_tm","tuesday_avg_srvc_tm","wednesday_avg_srvc_tm","thursday_avg_srvc_tm","friday_avg_srvc_tm","saturday_avg_srvc_tm","sunday_avg_srvc_tm" )
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("fac_id").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker());
Select GET_JOB_PROPERTY_SELECTIVE = selectFrom(TableConstants.M100_WML, TableConstants.JOB_PROPERTIES)
.columns("value")
.whereColumn("key").isEqualTo(bindMarker());
Select GET_ROUTE_DISPOSAL_SEQ_HISTORY = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ROUTE_DISPOSAL_SEQ_HISTORY)
.columns("site","dow","rt_id","disp_seq","act_fac_id","fac_id","adj_lat","adj_lon","fac","lat","lon","ranking","roads_uid","side_of_street","x_coord","y_coord")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("dow").isEqualTo(bindMarker())
.whereColumn("rt_id").isEqualTo(bindMarker());
Select GET_DISPOSAL_HISTORY_BY_SITE = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.DISPOSAL_HISTORY_BY_SITE)
.columns("site","dow","act_fac_id","fac_id","adj_lat","adj_lon","fac","lat","lon","ranking","roads_uid","side_of_street","x_coord","y_coord")
.whereColumn("site").isEqualTo(bindMarker());
Select GET_ERL_ACTUAL_ROUTE = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.ERL_ACTUAL_ROUTE)
.columns("lob","site","dow","rt_id","avg_actual_efficiency","avg_disposal_trip_cnt","avg_lift_cnt","avg_route_distance","avg_seq_compl_pct","avg_stop_cnt","avg_volume","avg_volume_per_intraday_trip","avg_volume_per_trip","avg_weight","avg_weight_per_intraday_trip","avg_weight_per_trip","data_source_id","rt_typ","source_id","total_disposal_trip_cnt","total_intraday_volume","total_intraday_weight","total_volume","total_weight")
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("dow").isEqualTo(bindMarker())
.whereColumn("rt_id").isEqualTo(bindMarker());
Select GET_ERL_ROUTE_TYPE_CONSTRAINT = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.ERL_ROUTE_TYPE_CONSTRAINT)
.columns("lob","site","dow","route_typ","avg_rt_cap","compaction_ratio","max_rt_cap_pct_factor","max_rt_hrs","max_stops","max_veh_cap_pct_factor","max_yards","min_veh_cap_pct_factor","rt_hrs_pct_factor","start_tm","stop_tm","tm_window_buffer","trav_tm_pct_factor")
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("dow").isEqualTo(bindMarker())
.whereColumn("route_typ").isEqualTo(bindMarker());
Select GET_LOAD_TYPE_LEGS = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER ,TableConstants.LOAD_TYPE_LEGS)
.columns("ld_typ","lg1","lg2","lg3","lg4","lg5")
.whereColumn("ld_typ").isEqualTo(bindMarker());
RegularInsert INSERT_JOB_STATUS_HISTORY = insertInto(TableConstants.DIAGNOSTIC_TOOL, TableConstants.JOB_STATUS_HISTORY)
.value("curr_dt", bindMarker("curr_dt"))
.value("job_name", bindMarker("job_name"))
.value("job_submit_ts", bindMarker("job_submit_ts"))
.value("job_completion_ts", bindMarker("job_completion_ts"))
.value("job_duration", bindMarker("job_duration"))
.value("job_type", bindMarker("job_type"))
.value("input_records", bindMarker("input_records"))
.value("processed_records", bindMarker("processed_records"))
.value("calc_type", bindMarker("calc_type"))
.value("status", bindMarker("status"));
Delete DELETE_RTO_ROUTE_DETAIL_RESPONSE_DP = deleteFrom(TableConstants.M100_WML , TableConstants.RTO_ROUTE_DETAIL_RESPONSE_DP)
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
Delete DELETE_ROUTE_SUMMARY_DP = deleteFrom(TableConstants.M100_WML , TableConstants.RTO_SUMMARY_RESPONSE_DP)
// .all()
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
Select GET_LANDFILL_CUSTOMER_USAGE = selectFrom(TableConstants.RTO_ANALYTICS ,TableConstants.LANDFILL_CUSTOMER_USAGE)
.columns("lob","site","customeruniqueid","fac_cd","fac_id","waste_typ")
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("customeruniqueid").isEqualTo(bindMarker())
.whereColumn("waste_typ").isEqualTo(bindMarker());
Select GET_LANDFILL_SITE_USAGE = selectFrom(TableConstants.RTO_ANALYTICS, TableConstants.LANDFILL_SITE_USAGE)
.columns("lob","site","waste_typ","fac_cd","fac_id")
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("waste_typ").isEqualTo(bindMarker());
Select LOOKUP_ON_PRPRTY_TRVL_AND_DIST_FOR_CUST = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.RO_CUSTOMER_ON_PROP_TRAVEL_TM_DIST)
.columns("site","mas_unique_id","on_prop_travel_tm","on_prop_travel_dist")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("mas_unique_id").isEqualTo(bindMarker());
Select LOOKUP_PRE_POST_RTIME_FROM_RCROUTEPLANNINGVIEW = selectFrom(TableConstants.WM_CSD_DR_STAGGING, TableConstants.RC_ROUTE_PLANNING_VIEW)
.columns("plan_date","site","driver_id","rc_uuid","audit_ts","audit_user","delete_flag","driver_lob_id","driver_start","launch_group_name","post","pre","primary_driver","remote_end_addr","remote_end_flag","remote_end_lat","remote_end_lat_dir","remote_end_loc_nm","remote_end_long","remote_end_long_dir","remote_end_od_loc_id","remote_end_roads_uid","remote_end_sos","remote_end_x","remote_end_y","remote_start_addr","remote_start_flag","remote_start_lat","remote_start_lat_dir","remote_start_loc_nm","remote_start_long","remote_start_long_dir","remote_start_od_loc_id","remote_start_roads_uid","remote_start_sos","remote_start_x","remote_start_y","ro_key","route_id","route_lob_id","route_starting_rail_status","scheduled_hours","scheduled_start","vehicle_id")
.whereColumn("plan_date").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("driver_id").isEqualTo(bindMarker());
Select GET_OCS_LANDFILL_PARAM = selectFrom(TableConstants.WM_CSD_DR_STAGGING, TableConstants.OCS_LANDFILL_MASTER)
.columns("site", "fac_cd", "name", "is_active", "ro_enabled", "comm_enabled", "objectid", "od_loc_id", "x", "y", "houseno", "street", "city", "region", "zipcode", "country", "roads_uid","lat","lon","ocs_lat","ocs_lon")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("fac_cd").isEqualTo(bindMarker());
Select LOOKUP_DOMAIN_CONFIG = selectFrom(TableConstants.WM_CSD_KS, TableConstants.GET_DOMAIN_CONFIG)
.columns("value")
.whereColumn("key").isEqualTo(bindMarker());
Select LOOKUP_COMPASS_VEHICLE_MASTER = selectFrom(TableConstants.WM_CSD_DR_STAGGING, TableConstants.COMPASS_VEHICLE_MASTER)
.columns("site", "vehicle_no", "audit_ts", "audit_user", "axle", "body_yardage", "category", "change_dt", "class2", "company", "description", "fuel_type", "gross_weight", "height_inches", "in_serv_dt", "legal_weight", "ltd_usage", "maint_loc", "make", "manufacturer", "model", "overall_length_inches", "parking_loc", "rt_boom_deck_hght_inches", "sale_dt", "serial_no", "spec_no", "status", "tandem_capable", "tare_weight", "unit_id", "unit_type", "vehicle_type", "vehicle_width", "year")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("vehicle_no").isEqualTo(bindMarker());
// Select LOOKUP_SITE_ACTIVATION = selectFrom(TableConstants.WM_CSD_DISP_DRVR_KS, TableConstants.CSD_SITE_ACTIVATION)
// .columns("site","lob","plan","audit_ts","audit_user","boddp_calc_flg","boddp_publish_flg","calc_act_dt","dynamicplan_calc_flg","dynamicplan_publish_flg","enable_calc_flg","enable_cc_flg","enable_container_info","enable_customer_geocodes","enable_disp_geocodes_hrs_op","enable_drh","enable_disp_geocodes_hrs_op","enable_ingest_flg","enable_publish_flg","enable_pva_flg","enable_rc_flg","enable_waste_acceptance","ingest_act_dt","publish_act_dt")
// .whereColumn("site").isEqualTo(bindMarker())
// .whereColumn("lob").isEqualTo(bindMarker());
Select LOOKUP_SITE_ACTIVATION = selectFrom(TableConstants.WM_CSD_DISP_DRVR_KS, TableConstants.CSD_SITE_ACTIVATION)
.columns("site","lob","plan","audit_ts","audit_user","boddp_calc_flg","boddp_publish_flg","calc_act_dt","enable_calc_flg","enable_drh","enable_publish_flg","enable_pva_flg","enable_waste_acceptance")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker());
Select LOOKUP_OCS_CONTAINER_YARD_DETAILS = selectFrom(TableConstants.WM_CSD_DR_STAGGING, TableConstants.STG_OCS_CONTAINERYARD)
.columns("lob","site","cy_code","address","audit_create_dt","audit_create_uid","audit_ts","audit_updated_dt","audit_user","city","comp_owned","country","cy_name","cy_obj_id","fri_avg_srv_tm","fri_close_hr","fri_open_hr","geocode_status_cd","geocode_updated_dtm","house_no","inactive","ismanual","lat","lon","modifiedby","mon_avg_srv_tm","mon_close_hr","mon_open_hr","name","od_loc_id","pkey","roads_uid","sat_avg_srv_tm","sat_close_hr","sat_open_hr","sessionid","st_side","stamp","state","state_cd","street","sub_lob","sun_avg_srv_tm","sun_close_hr","sun_open_hr","thu_avg_srv_tm","thu_close_hr","thu_open_hr","tue_avg_srv_tm","tue_close_hr","tue_open_hr","wed_avg_srv_tm","wed_close_hr","wed_open_hr","x","y","zip")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("cy_code").isEqualTo(bindMarker());
Select STG_MAS_DR_CUSTOMER = selectFrom(TableConstants.WM_CSD_DR_STAGGING, TableConstants.STG_MAS_DR_CUSTOMER)
.columns("site","mas_cus_uniq_id","address_1","lat_lon","latitude","library","lob","longitude","roads_uid","cmpy_cd","x_coord","y_coord")
//.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("library").isEqualTo(bindMarker())
.whereColumn("cmpy_cd").isEqualTo(bindMarker())
.whereColumn("mas_cus_uniq_id").isEqualTo(bindMarker());
Select STG_MAS_DR_CUSTOMER_SPECIFIC = selectFrom(TableConstants.WM_CSD_DR_STAGGING, TableConstants.STG_MAS_DR_CUSTOMER)
.columns("site","mas_cus_uniq_id","address_1","lat_lon","latitude","library","lob","longitude","roads_uid","cmpy_cd","x_coord","y_coord")
//.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("library").isEqualTo(bindMarker())
.whereColumn("cmpy_cd").isEqualTo(bindMarker())
.whereColumn("mas_cus_uniq_id").isEqualTo(bindMarker());
Select LOOKUP_TS_SHED_CAL_STATUS = selectFrom(TableConstants.WM_CSD_DISP_DRVR_KS, TableConstants.TS_SCHED_CALC_STATUS)
.columns("confirm_dt","rt_dt","site","lob","emp_id","apv2_calc_status","apv2_publish_detail","apv2_publish_status","audit_ts","audit_user","calc_status","calc_ts","confirm_ts","publish_status","publish_ts","route_confirmed","source","tendem_flg","trans_id")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
Select GEL_RO_EMP_MAP_CONFIRM_DATE = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER , TableConstants.RO_CUSTOMER_ON_PROP_TRAVEL_TM_DIST)
.columns("confirm_dt")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
Select GEL_VEHICLE_FROM_RO_EMP_MAP = selectFrom(TableConstants.M100_WML , TableConstants.RO_EMP_MAP)
.columns("v_id")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
Select LOOKUP_SITE_LIBRARY = selectFrom(TableConstants.ERL_ROUTE_OPTIMIZER, TableConstants.RO_SITE_LIBRARY)
.columns("site","library_nm","company_cd")
.whereColumn("site").isEqualTo(bindMarker());
Select GET_KRONOS_CLOCKIN = selectFrom(TableConstants.WM_CSD_DISP_DRVR_KS, TableConstants.KRONOS_CLOCKIN)
.columns("rt_dt","emp_id","actual_start","actual_end","sched_start","sched_end")
.whereColumn("rt_dt").isEqualTo(bindMarker())
.whereColumn("emp_id").isEqualTo(bindMarker());
Select GET_CROSS_SITES = selectFrom(TableConstants.WM_CSD_DR_STAGGING, TableConstants.RC_GROUP_CROSS_SITE)
.columns("lob","site","audit_ts","audit_user","cross_site_list","group_nm","mkt_area_group","primary_site")
.whereColumn("site").isEqualTo(bindMarker())
.whereColumn("lob").isEqualTo(bindMarker());
Select GET_RO_DOMAIN_CONFIG = selectFrom(TableConstants.WM_CSD_RO_KS, TableConstants.RO_DOMAIN_CONFIG)
.columns("key","value");
/**
* Perform look up in the ro_co_result table to fetch all the events for the criteria passed as input.
*
* @param roKeys : Comma separated list of route order keys
* @param useConsistencyStatement the use consistency statement
* @return List of Row matching input criteria
*/
List<Row> lookupRoCoResult(List<String> roKeys, boolean useConsistencyStatement);
/**
* Perform look up in the ro_co_event table to fetch all the events for the criteria passed as input.
*
* @param roKey the ro key
* @param useConsistencyStatement the use consistency statement
* @return List of Row matching input criteria
*/
List<Row> lookupRoCoEvent(
String roKey, boolean useConsistencyStatement);
/**
* Retrieve customer order list for the input site/date/roKey.
*
* @param roKey Route Order key
* @return List of customer orders matching input criteria
*/
List<Row> lookupRoCustomerOrder(
String roKey);
/**
* Perform look up in the erl_route_params table to fetch EMAP pre/post time.
* Data in Cassandra table is populated in nightly batch job from eRL database.
*
* @param routeId Route ID value
* @return Row object contain data matching input criteria.It can also return null.
*/
Row lookupRouteParams(
String routeId);
/**
* Perform look up in the erl_depot_parameters table to fetch Depot geocode and address information.
* Data in Cassandra table is populated in nightly batch job from eRL database.
* @param site
* @return Row object contain data matching input criteria.It can also return null.
*/
Row lookupDepotParams(String site);
/**
* Perform look up in the site master table to fetch route type information.It performs
* lookup based on
* a) site/routeId/dayofWeek if data not found then it performs lookup on
* b) site/routeId
* Data in Cassandra table is populated in nightly batch job from eRL database.
* @param routeId Route ID
* @param dayOfWeek : Day of week for which the Route type information in required
* @return Row matching input criteria
*/
Row lookupRouteType(
String routeId, String dayOfWeek);
/**
* Perform look up in the erl Disposal facility table to get geocode and service time
* information for landfills.THe source of eRL disposal information is OPUS historic
* information(Populated in cassandra using nightly job).
*
* @param facilityCd the facility cd
* @param dayOfWeek : Day of week for which the Route type information in required
* @return Row matching input criteria
*/
Row lookupDisposalFacilityParams(
String facilityCd, String dayOfWeek);
/**
* Perform look up in the erl Disposal facility table to get geocode and service time
* information for landfills.THe source of eRL disposal information is OPUS historic
* information(Populated in cassandra using nightly job).
* @param facilityId Facility Code
* @return Row matching input criteria
*/
Row lookupDisposalFacilityParams( String facilityId);
/**
* Perform look up in the erl Disposal facility table to get geocode and service time
* information for landfills. THe source of eRL disposal information is OPUS historic
* information(Populated in cassandra using nightly job).
*
* @param facilityCd the facility cd
* @param dayOfWeek : Day of week for which the Route type information in required
* @return Row matching input criteria
*/
Row lookupRoDisposalFacilityParams(
String facilityCd, String dayOfWeek);
/**
* Retrieve allowances information from site master.
* @return Allowance row for the site passed as input
*/
Row lookupAllowance();
/**
* Perform look up in the kronos_master table to fetch schedule clock in information
* based on the empID and date passed as input
* Data in Cassandra table is populated in nightly batch job from eRL database.
* @return Row matching input criteria
*/
Row lookupKronosData();
/**
* Perform look up in the erl_traffic_coefficient_cat & erl_traffic_coefficient_val table to route level
* calibration factors.Look up is performed in following order
* a) Get coefficient category based on site/rt_id/dayOfWeek/route Type & then site/rt_id
* b) Get coefficient value based on the coefficient retrieved in step(a)
* Data in Cassandra table in quarterly calibration update process.
*
* @param routeId : Route ID
* @param dayOfWeek the day of week
* @param routeType : Route Type
* @return Return list of row with calibration factors
*/
List<Row> lookupRouteCalibrationFactor(String routeId, String dayOfWeek, String routeType);
/**
* Save route order target.
*
* @param calcTargetRequest the calc target request
* @param request the request
* @param response the response
*/
void saveRouteOrderTarget(CalcTarget calcTargetRequest, String request,String response);
/**
* Save route detail response.
*
* @param routeResponses the route responses
*/
void saveRouteDetailResponse(List<RouteDetailResponse> routeResponses);
/**
* Save route detail response daily target.
*
* @param routeResponses the route responses
*/
void saveRouteDetailResponseDailyTarget(List<RouteDetailResponse> routeResponses);
/**
* Save route detail response daily plan.
*
* @param routeResponses the route responses
*/
void saveRouteDetailResponseDailyPlan(List<RouteDetailResponse> routeResponses);
/**
* Lookup route calculation records by confirmation date.
*
* @param spark the spark
* @param dateStr the date str
* @return the dataset
* @throws Exception
*/
Dataset<org.apache.spark.sql.Row> lookupRouteCalculationRecordsByConfirmationDate(SparkSession spark, String dateStr) throws Exception;
/**
* Lookup route calculation records by confirmation date range.
*
* @param spark the spark
* @param dateStr the date str
* @return the dataset
* @throws Exception
*/
Dataset<org.apache.spark.sql.Row> lookupRouteCalculationRecordsByConfirmationDateRange(SparkSession spark, String dateStr) throws Exception;
/**
* Lookup daily target calculation records by request date range.
*
* @param spark the spark
* @param dateStr the date str
* @return the dataset
* @throws Exception
*/
Dataset<org.apache.spark.sql.Row> lookupDailyTargetCalculationRecordsByRequestDateRange(SparkSession spark, String dateStr) throws Exception;
/**
* Lookup daily plan calculation records by request date range.
*
* @param spark the spark
* @param dateStr the date str
* @return the dataset
* @throws Exception
*/
Dataset<org.apache.spark.sql.Row> lookupDailyPlanCalculationRecordsByRequestDateRange(SparkSession spark, String dateStr) throws Exception;
/**
* Return Dataset of ro_emp_map by route date.
*
* @param spark the spark
* @param dateStr the date str
* @return the dataset
* @throws Exception
*/
Dataset<org.apache.spark.sql.Row> lookupRoEmpMapByRouteDate(SparkSession spark, String dateStr) throws Exception;
/**
* Return Dataset of ro_emp_map.
*
* @return the list
*/
List<Row> lookupRoEmpMap();
/**
* Return Dataset of ro_emp_map by emp id.
*
* @return the list
*/
List<Row> lookupRoEmpMapWithEmpId();
/**
* Checks in ro_customer_order table if data is available for input site/date/lob/roKey.
*
* @param roKey Route Order key
* @return True,If customer order is available for matching input criteria else false
*/
@Deprecated
boolean isRoCustomerOrderAvailable(String roKey);
/**
* Save calc error log.
*
* @param calcErrorLog the calc error log
*/
void saveCalcErrorLog(CalcErrorLog calcErrorLog);
/**
* Save calc exception.
*
* @param calcException the calc exception
*/
void saveCalcException(CalcException calcException);
/**
* Update calc status.
*
* @param confirmationDate the confirmation date
* @param status the status
* @param publishFlg
*/
void updateCalcStatus(String confirmationDate,String status, String publishFlg);
/**
* Deletes the Route details response from cassandra for the site/rtDt/empId passed as input.Route
* details are always delete before recalculating the route.This steps is necessay to make sure
* that is any landfill/lunch/downtime is dded or removed as a part of OPUS post route update.
*
*/
void deleteRouteDetailResponse();
/**
* Perform look up in the erl_customer_parameter table to fetch geocode and service time
* information for customer based site/lob/customer unique id/route type.
* Data in Cassandra table is populated in nightly batch job from eRL database.
*
* @param customerUniqueId the customer unique id
* @param routeType Route type
* @return Row matching input criteria
*/
@Deprecated
public Row lookupCustomerParameterForSiteLobCustomerUniqueIdRouteType(String customerUniqueId, String routeType);
/**
* Perform look up in the erl_customer_parameter table to fetch geocode and service time
* information for customer based site/lob/Customer unique id.
* Data in Cassandra table is populated in nightly batch job from eRL database.
*
* @param customerUniqueId the customer unique id
* @return Row matching input criteria
* @see lookupCustomerParameterForSiteLobCompanyCodeCompanyNoRouteType
* @see lookupCustomerParameterForLobRouteType
*/
@Deprecated
public Row lookupCustomerParameterForSiteLobCustomerUniqueId( String customerUniqueId);
/**
* Perform look up in the erl_customer_parameter table to fetch geocode and service time
* information for customer based site/lob/Route Type
* Data in Cassandra table is populated in nightly batch job from eRL database.
*
* @param routeType Route type
* @return Row matching input criteria
* @see lookupCustomerParameterForSiteLobCompanyCodeCompanyNoRouteType
* @see lookupCustomerParameterForSiteLobCompanyCodeCompanyNo
*/
@Deprecated
public Row lookupCustomerParameterForLobCustomerUniqueIdRouteType(String customerUniqueId, String routeType);
/**
* Perform look up in the erl_customer_parameter table to fetch geocode and service time
* information for customer based lob/Customer unique id.
* Data in Cassandra table is populated in nightly batch job from eRL database.
*
* @param customerUniqueId the customer unique id
* @return Row matching input criteria
* @see lookupCustomerParameterForSiteLobCompanyCodeCompanyNoRouteType
* @see lookupCustomerParameterForLobRouteType
*/
@Deprecated
public Row lookupCustomerParameterForSiteLobRouteType(String routeType);
/**
* Lookup customer parameter for site lob.
*
* @return the com.datastax.driver.core. row
*/
@Deprecated
public Row lookupCustomerParameterForSiteLob();
/**
* Lookup calc exception.
*
* @return the list
*/
List<Row> lookupCalcException();
/**
* Lookup calc exception.
*
* @param roKey the ro key
* @return the com.datastax.driver.core. row
*/
Row lookupCalcException(String roKey);
/**
* lookup all site for given lob.
*
* @param spark the spark
* @param lob the lob
* @return the dataset
* @throws Exception
*/
Dataset<org.apache.spark.sql.Row> lookupSiteActivation(SparkSession spark, String lob) throws Exception;
/**
* lookup all site for given lob.
*
* @param spark the spark
* @return the dataset
* @throws Exception
*/
Dataset<org.apache.spark.sql.Row> lookupSiteActivation(SparkSession spark) throws Exception;
/**
* return library name for site.
*
* @return the library name
*/
String getLibraryName();
/**
* lookup container parameter for srcCntnrTypCd .
*
* @param srcCntnrTypCd the src cntnr typ cd
* @return the com.datastax.driver.core. row
*/
Row lookupContainerParameter(String srcCntnrTypCd);
/**
* Lookup ro customer parameter.
*
* @param library the library
* @param customerUniqueId the customer unique id
* @param cntnrTypId the cntnr typ id
* @param srvcCd the srvc cd
* @return the com.datastax.driver.core. row
*/
Row lookupRoCustomerParameter(String library, String customerUniqueId, String cntnrTypId,
String srvcCd);
/**
* Return truck restriction parameters like max weight allowed, max height allowed for given site .
*
* @return the list
*/
List<Row> lookupTruckRestrictionParameter();
/**
* Lookup load type params.
*
* @param ocsLoadType the ocs load type
* @return the com.datastax.driver.core. row
*/
Row lookupLoadTypeParams(String ocsLoadType);
/**
* Lookup ro customer parameter for container load type.
*
* @param cntnrTypId the cntnr typ id
* @param loadType the load type
* @return the com.datastax.driver.core. row
*/
Row lookupRoCustomerParameterForContainerLoadType(String cntnrTypId, String loadType);
/**
* check route is calculated or not at the time of exception report publish. Originally route is in Pending status in calc_exception table
*
* @return positive value if route is calculated successfully and there is an entry in the rs_perf table for that employee
*/
boolean isRouteComplete();
/**
* Returns employee Name from employee_master table.
*
* @return Row object containing selected element for empID
*/
@Deprecated
Row getEmployeeName();
/**
* return OCS service code (load type short description).
*
* @param objectId the object id
* @return the ocs srvc code
*/
String getOcsSrvcCode( String objectId);
/**
* Delete calc exception.
*
* @param roKeys the ro keys
*/
void deleteCalcException(List<String> roKeys);
/**
* Delete routing service performance.
*/
void deleteRoutingServicePerformance();
/**
* Returns customer geocode based on input parameter .
*
* @param customerUniqueId the customer unique id
* @return the com.datastax.driver.core. row
*/
@Deprecated
Row lookupCustomerGeocode(String customerUniqueId);
/**
* Deletes the TicketEventDetail from cassandra for the site/rtDt/empId passed as input. TicketEventDetail
* are always delete before recalculating the route.
*
* @param roKey the ro key
*/
void deleteTicketEventDetail(String roKey);
/**
* Save ticket event detail.
*
* @param ticketEventDetails the ticket event details
*/
void saveTicketEventDetail(List<TicketEventDetail> ticketEventDetails);
/**
* Returns calibration value for Roll off.
*
* @return the list
*/
List<Row> lookuproCalibration();
/**
* Lookup emp vehicle route params.
*
* @param dow the dow
* @param vId the v id
* @return the com.datastax.driver.core. row
*/
Row lookupEmpVehicleRouteParams(String dow, String vId);
/**
* Gets the disposal cycle time.
*
* @param facilityCd the facility cd
* @param containerType the container type
* @param containerSize the container size
* @param site
* @return the disposal cycle time
*/
Row getDisposalCycleTime(String facilityCd,
String containerType, String containerSize, String fetchDispCycletime, String site);
/**
* Perform look up in the erl_depot_parameters table to fetch Depot geocode and address information.
* Data in Cassandra table is populated in nightly batch job from eRL database.
*
* @param dow the dow
* @return Row object contain data matching input criteria.It can also return null.
*/
Row lookuproDepotParams(String dow);
/**
* Lookup ro customer parameter.
*
* @param library the library
* @param customerUniqueId the customer unique id
* @return the com.datastax.driver.core. row
*/
Row lookupRoCustomerParameter(String library, String customerUniqueId);
/**
* Gets the ro co event.
*
* @param ro_key the ro key
* @param useConsistencyStatement the use consistency statement
* @return the ro co event
*/
public List<Row> getRoCoEvent(String ro_key, boolean useConsistencyStatement);
/**
* Ro pre post time.
*
* @param rt_id the rt id
* @return the list
*/
public List<Row> roPrePostTime(String rt_id);
/**
* Save route order target meal misc count.
*
* @param calcTargetRequest the calc target request
* @param actMeal the act meal
* @param adMeal the ad meal
* @param misc the misc
* @param actCyCount the act cy count
* @param adCyCount the ad cy count
*/
void saveRouteOrderTargetMealMiscCount(CalcTarget calcTargetRequest, String actMeal,String adMeal,
String misc, String actCyCount, String adCyCount);
/**
* Lookup error code.
*
* @param reasonCode the reason code
* @return the com.datastax.driver.core. row
*/
Row lookupErrorCode(String reasonCode);
/**
* Lookup route map.
*
* @param routeId the route id
* @return the list
*/
List<Row> lookupRouteMap(String routeId);
/**
* Lookup ro customer order daily target.
*
* @param roKey the ro key
* @return the list
*/
List<Row> lookupRoCustomerOrderDailyTarget(String roKey);
/**
* Save route summary daily target.
*
* @param calcTargetRequest the calc target request
* @param request the request
* @param response the response
* @param outputJson the output json
*/
void saveRouteSummaryDailyTarget(CalcTarget calcTargetRequest, String request, String response, String outputJson);
/**
* Save route summary daily plan.
*
* @param calcTargetRequest the calc target request
* @param request the request
* @param response the response
* @param outputJson the output json
*/
void saveRouteSummaryDailyPlan(CalcTarget calcTargetRequest, String request, String response, String outputJson);
/**
* Update calc status daily target.
*
* @param requestDate the request date
* @param routeId the route id
* @param transactionId the transaction id
* @param status the status
* @param additionalInformation the additional information
*/
void updateCalcStatusDailyTarget(
String requestDate, String routeId, String transactionId, String status, String additionalInformation);
/**
* Update calc status daily plan.
*
* @param requestDate the request date
* @param transactionId the transaction id
* @param status the status
*/
void updateCalcStatusDailyPlan(String requestDate, String transactionId, String status);
/**
* @param cassandraConnector
* @param calcErrorLogService
* @param site
* @param lob
* @param customerUniqueIds
* @return
*/
List<Row> lookupErlCustomerParameter( List<String> customerUniqueIds);
/**
* Lookup erl service time.
*
* @return the list
*/
List<Row> lookupErlServiceTime();
/**
* Delete route detail response daily target.
*
* @param routeId the route id
*/
void deleteRouteDetailResponseDailyTarget(
String routeId);
/**
* Delete route summary daily target.
*
* @param routeId the route id
* @param roKeys the ro keys
*/
void deleteRouteSummaryDailyTarget(String routeId, List<String> roKeys);
/**
* Lookup facility params.
*
* @param dayOfWeek the day of week
* @param routeId the route id
* @return the list
*/
List<Row> lookupFacilityParams( String dayOfWeek, String routeId);
/**
* Lookup facility params by site.
*
* @return the com.datastax.driver.core. row
*/
Row lookupFacilityParamsBySite();
/**
* Lookup erl actual route.
*
* @param dayOfWeek the day of week
* @param routeId the route id
* @return the com.datastax.driver.core. row
*/
Row lookupErlActualRoute( String dayOfWeek, String routeId);
/**
* Lookup erl route type constraint.
*
* @param dayOfWeek the day of week
* @param routeType the route type
* @return the com.datastax.driver.core. row
*/
Row lookupErlRouteTypeConstraint( String dayOfWeek, String routeType);
/**
* Lookup legs for load type.
*
* @param loadType the load type
* @return the com.datastax.driver.core. row
*/
Row lookupLegsForLoadType(
String loadType);
/**
* Delete route detail response daily target.
*
* @param routeId the route id
* @param roKey the ro key
*/
void deleteRouteDetailResponseDailyTarget(
String routeId,
String roKey);
/**
* Save job status history.
*
* @param history the history
*/
void saveJobStatusHistory(JobStatusHistory history);
/**
* Gets the container yard code using manhattan distance.
*
* @param dayOfWeek the day of week
* @return the container yard code using manhattan distance
*/
List<Row> getContainerYardCodeUsingManhattanDistance(String dayOfWeek);
/**
* Save route summary daily target log.
*
* @param calcTargetRequest the calc target request
* @param request the request
* @param response the response
* @param outputJson the output json
*/
void saveRouteSummaryDailyTargetLog(CalcTarget calcTargetRequest, String request, String response,
String outputJson);
/**
* @param ro_key
*/
void deleteRouteDetailResponseDailyPlan();
/**
* @param ro_key
*/
void deleteRouteSummaryDailyPlan();
/**
* @param customerUniqId
* @param wastType
* @return
*/
Row lookupLandfillCustomerUsage(String customerUniqId, String wastType);
/**
* @param wastType
* @return
*/
Row lookupLandfillSiteUsage(String wastType);
Row lookupOnPrprtyTrvlAndDistForCust(String masUniqueId);
//com.datastax.driver.core.Row roPrePostTime(String string, String string2);
public List<Row> lookupRcRoutePlanningView(String routeDate, String empId);
Row lookupOcsLandfillMaster(String site, String facilityCd);
Row getDomainConfig(String key);
/**
* Perform look up in the kronos_clockin table to fetch schedule clock in information
* based on the empID and date passed as input
* Data in Cassandra table is populated in nightly batch job from eRL database.
* @return Row matching input criteria
*/
Row lookupSiteActivation();
Row lookupOcsContainerYardDetails(String site, String lob, String containerObjCd);
Row lookupRoCustomerService(String lib, List<String> cmpCodes, String CustUniqueId);
Row lookupTsSHedCalStatus(String confirmDate);
String lookupConfirmDate();
public List<Row> getLibraryCmpnyCdName();
Row getKronosClockin();
Row lookupRoCustomerService(String library, String cmpCode, String massUniqueId);
Row getCrossSites(String site);
Map<String, String> lookupRoDomainConfig() throws JsonParseException, JsonMappingException, IOException;
Row lookupCompassVehicleMaster(String key, String vehicle);
String lookupRoEmpMap(String routeDate, String site, String lob, String employeeId);
}
Editor is loading...
Leave a Comment