private void Store_one_service_info(ref string requestId, ref string ServiceCode, ref string Type)
{
string sql = string.Concat("insert into LTL_RATE_REQUESTS_SERVICE (RequestId,ServiceCode,Type) values ",
"('", requestId, "','", ServiceCode, "','", Type, "')");
HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringAesAPI, ref sql, "StoreLTLRequestsSql Genera");
}
public static void InsertAdditionalService(int intSegmentID, int intServiceID)
{
string strSQL =
"insert into tbl_ACCDetail(SEGMENTID,LKUPID_ACC,ACCType)"
+ " values("
+ intSegmentID + "," + intServiceID + DB.GetCommaSingleQuoteValue("SEG")
+ ")";
HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringAesData, ref strSQL,
"dispatch InsertAdditionalService");
}
public static void InsertInsuranceCost(ref DispatchData dispatch_data, double shipmentValue, int intRQID)
{
string strSQL;
int intServiceID = 291;
double insuranceCost = shipmentValue * AppCodeConstants.InsuranceSellRate;
if (insuranceCost < dispatch_data.minInsuranceCost)
{
insuranceCost = dispatch_data.minInsuranceCost;
}
SetInsuranceMinimum(ref dispatch_data, ref insuranceCost, ref shipmentValue);
strSQL = "insert into tbl_ACCDetail(SEGMENTID,LKUPID_ACC,ACCType,ChargeAmt,RQID)"
+ " values("
+ dispatch_data.intSegmentID + "," + intServiceID + DB.GetCommaSingleQuoteValue("SEG") + "," +
insuranceCost + "," + intRQID
+ ")";
HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringAesData, ref strSQL,
"dispatch InsertInsuranceCost");
}
public HttpResponseMessage Post()
{
try
{
//--
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
//--
#region Authentication
var re = Request;
var headers = re.Headers;
string username = "", password = "";
if (headers.Contains("username"))
{
username = headers.GetValues("username").First();
DB.LogGenera(iam, "username", username);
}
else
{
DB.LogGenera(iam, "username", "none found");
}
if (headers.Contains("password"))
{
password = headers.GetValues("password").First();
DB.LogGenera(iam, "password", password);
}
else
{
DB.LogGenera(iam, "password", "none found");
}
#endregion
string data = this.Request.Content.ReadAsStringAsync().Result;
DB.LogGenera("Get_LTL_ratesController " + AppCodeConstants.mode, "Public API data", data);
string url_string = "", request_format = "", error_response = "", response_string = "",
content_type = "application/json";
LTLQuoteRequest ltl_quote_request = new LTLQuoteRequest();
QuoteData quoteData = new QuoteData();
#region Parse/Deserialize request, make sure JSON is valid
// Parse/Deserialize request, make sure JSON is valid
Validator vld = new Validator();
vld.Validate_request_json(ref data, ref url_string, ref error_response, ref request_format,
ref ltl_quote_request);
#endregion
// Xml_helper xml_helper = new Xml_helper();
Json_helper json_helper = new Json_helper();
Models.Public.LTL.Helper public_helper = new Models.Public.LTL.Helper();
//DB.LogGenera(iam, "Public API url_string", url_string);
//DB.LogGenera(iam, "Public API error_response", error_response);
//DB.LogGenera(iam, "Public API url_string", url_string);
string sql = "";
if (error_response == "")
{
// Do nothing
}
else
{
//return error_response;
response_string = error_response;
var response_message = public_helper.Get_response_message(ref response_string, ref content_type);
sql = string.Concat("INSERT INTO Genera_Rating(QuoteID,Request_Data,Response_Data,TotalCube) VALUES(",
"0,'", data, "','", response_string, "',", ltl_quote_request.totalCube,
")");
HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringAesAPI, ref sql, "Get_LTL_ratesController");
return(response_message);
}
// If got to here, request is valid JSON or XML
// Convert JSON or XML to URL Encoded string and get GCM rates
url_string = string.Concat("username="******"&password="******"&mode=ws&subdomain=www&showDLSRates=True",
"&isHHG=False&isUSED=False",
url_string);
//DB.LogGenera(iam, "Public API url_string", url_string);
Models.LTL.Helper helper = new Models.LTL.Helper();
helper.Set_parameters(ref ltl_quote_request, ref quoteData);
quoteData.username = username;
quoteData.showDLSRates = true;
quoteData.subdomain = "www";
#region Check for > 4 line items
if (quoteData.m_lPiece.Length > 4)
{
DB.LogGenera(iam, "m_lPiece.Length > 4", "true");
response_string = json_helper.Build_error_response("35",
"Too many freight item nodes, maximum number of item nodes is 4");
return(public_helper.Get_response_message(ref response_string, ref content_type));
}
else
{
DB.LogGenera(iam, "m_lPiece.Length > 4", "false");
}
#endregion
Validator validator = new Validator(ref quoteData);
RateServiceNotification validation_result = validator.Validate_rating_request();
#region If validation was not successful return error result
if (validation_result.Code == "0")
{
// Do nothing
DB.LogGenera(iam, "validation_result.Code", validation_result.Code);
}
else
{
DB.LogGenera(iam, "validation_result.Code", validation_result.Code);
response_string = json_helper.Build_error_response(validation_result.Code, validation_result.Message);
var response_message = public_helper.Get_response_message(ref response_string, ref content_type);
sql = string.Concat("INSERT INTO Genera_Rating(QuoteID,Request_Data,Response_Data,TotalCube) VALUES(",
"0,'", data, "','", response_string, "',", ltl_quote_request.totalCube,
")");
HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringAesAPI, ref sql, "Get_LTL_ratesController");
return(response_message);
}
#endregion
quoteData.destCity = quoteData.destCity.Replace(" Twp", " Township");
quoteData.origCity = quoteData.origCity.Replace(" Twp", " Township");
LTL_Carriers carriers = new LTL_Carriers(quoteData);
SharedLTL.CarriersResult result = carriers.GetRates();
#region If any rates were found return rates, if not give error result
if (result.totalQuotes == null || result.totalQuotes.Length == 0)
{
#region If did not find any rates return error result
// If did not find any rates return error result
response_string = json_helper.Build_error_response("2",
string.Concat("Could not find any rates. Please try again."));
var response_message = public_helper.Get_response_message(ref response_string, ref content_type);
sql = string.Concat("INSERT INTO Genera_Rating(QuoteID,Request_Data,Response_Data,TotalCube) VALUES(",
"0,'", data, "','", response_string, "',", ltl_quote_request.totalCube,
")");
HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringAesAPI, ref sql, "Get_LTL_ratesController");
return(response_message);
#endregion
}
else
{
string[] additionalServices = new string[0];
string requestId;
public_helper.StoreLTLRequestsSql(
ref quoteData, ref result.totalQuotes, result.totalQuotes[0].NewLogId, data, response_string,
out requestId);
response_string = json_helper.Build_response(ref result.totalQuotes);
var response_message = public_helper.Get_response_message(ref response_string, ref content_type);
#region INSERT INTO Genera_Rating
sql = string.Concat("INSERT INTO Genera_Rating(QuoteID,Request_Data,Response_Data,TotalCube) VALUES(",
result.totalQuotes[0].NewLogId, ",'", data, "','", response_string, "',", ltl_quote_request.totalCube,
")");
HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringAesAPI, ref sql, "Get_LTL_ratesController");
#endregion
#region INSERT INTO ResponseTimes
// INSERT INTO ResponseTimes
int UPS_freight_milliseconds = 0, SMTL_milliseconds = 0, BestOvernite_milliseconds = 0, NewPenn_milliseconds = 0,
RL_milliseconds = 0, RRD_Truckload_milliseconds = 0;
foreach (GCMRateQuote quote in result.totalQuotes)
{
//DB.LogGenera("quote.DisplayName", "quote.DisplayName", quote.DisplayName);
if (quote.DisplayName == "UPS - Genera")
{
UPS_freight_milliseconds = quote.Elapsed_milliseconds;
}
else if (quote.DisplayName == "SMTL - Genera")
{
SMTL_milliseconds = quote.Elapsed_milliseconds;
}
else if (quote.DisplayName == "Best Overnite - Genera")
{
BestOvernite_milliseconds = quote.Elapsed_milliseconds;
}
else if (quote.DisplayName == "New Penn - Genera")
{
NewPenn_milliseconds = quote.Elapsed_milliseconds;
}
else if (quote.DisplayName == "R&L Carrier - Genera")
{
RL_milliseconds = quote.Elapsed_milliseconds;
}
else if (quote.DisplayName == "RRD Truckload")
{
RRD_Truckload_milliseconds = quote.Elapsed_milliseconds;
}
}
stopwatch.Stop();
int elapsedMilliseconds = (int)stopwatch.ElapsedMilliseconds;
//int seconds = elapsedMilliseconds / 1000;
//int gcm_time = 0;
sql = string.Concat("INSERT INTO Rate_response_times(",
"QuoteID,RequestID,GCM_API,DLS,UPS_freight,SMTL,BestOvernite,NewPenn,RL,P44,Truckload) VALUES(",
result.totalQuotes[0].NewLogId, ",'", requestId, "',", elapsedMilliseconds, ",", result.elapsed_milliseconds_DLS_Genera,
",", UPS_freight_milliseconds, ",", SMTL_milliseconds, ",", BestOvernite_milliseconds, ",", NewPenn_milliseconds,
",", RL_milliseconds, ",", result.elapsed_milliseconds_P44, ",", RRD_Truckload_milliseconds,
")");
HelperFuncs.ExecuteNonQuery(AppCodeConstants.conn_string_Genera, ref sql, "Get_LTL_ratesController");
#endregion
return(response_message);
}
#endregion
}
catch (Exception e)
{
#region Unknown error
DB.LogGenera(iam, "get_ltl_rates_test", e.ToString());
Json_helper json_helper = new Json_helper();
string response_string = "";
response_string = json_helper.Build_error_response("2",
string.Concat("Could not find any rates. Please try again."));
Models.Public.LTL.Helper public_helper = new Models.Public.LTL.Helper();
string content_type = "application/json";
return(public_helper.Get_response_message(ref response_string, ref content_type));
#endregion
}
}
public HttpResponseMessage Post()
{
string data = "", DLS_PrimaryReferencePNW = "", response_string = "";
try
{
#region Authentication
var re = Request;
var headers = re.Headers;
string username = "", password = "";
if (headers.Contains("username"))
{
username = headers.GetValues("username").First();
DB.LogGenera(iam, "username", username);
}
else
{
DB.LogGenera(iam, "username", "none found");
}
if (headers.Contains("password"))
{
password = headers.GetValues("password").First();
DB.LogGenera(iam, "password", password);
}
else
{
DB.LogGenera(iam, "password", "none found");
}
#endregion
data = this.Request.Content.ReadAsStringAsync().Result;
DB.LogGenera(iam, "Public API booking data", data);
string request_format = "", error_response = "",
content_type = "application/json";
LTLBookRequest ltl_book_request = new LTLBookRequest();
QuoteData quoteData = new QuoteData();
//
Validator vld = new Validator();
DB.LogGenera(iam, "before", "Validate_booking_request_xml_json");
// Validate_booking_request_json
vld.Validate_booking_request_json(ref data, ref error_response, ref request_format,
ref ltl_book_request);
DB.LogGenera(iam, "after", "Validate_booking_request_xml_json");
Models.Public.LTL.Helper public_helper = new Models.Public.LTL.Helper();
#region If got an error response from Validator - return error message
DB.LogGenera(iam, "Public API error_response", error_response);
if (error_response == "")
{
// Do nothing
}
else
{
response_string = error_response;
return(public_helper.Get_response_message(ref response_string, ref content_type));
}
#endregion
#region Not used, MakeBookingViaSOAP
//return new HttpResponseMessage()
//{
// Content = new StringContent(response_string, Encoding.UTF8, "application/json")
//};
//Models.Public.LTL.Helper public_helper = new Models.Public.LTL.Helper();
// If got to here, request is valid JSON or XML
// Make booking via SOAP
//LTLBooking booking = new LTLBooking();
//response_string = booking.MakeBookingViaSOAP(ref ltl_book_request, ref username, ref password);
#endregion
// Get Calculated freight_class, carrier Mode
var repo = new gcmAPI.Models.Public.LTL.Repository();
AES_API_info api_info;
repo.Get_booking_info_by_booking_key(ltl_book_request.bookingKey, out api_info);
// Get_carriers_by_QuoteID
List <AES_API_info> quote_carriers;
// Get_carriers_by_QuoteID
repo.Get_carriers_by_QuoteID(api_info.QuoteId, out quote_carriers);
//
// Build email text
StringBuilder sb = new StringBuilder();
public_helper.getEmailText(ref sb, ref quote_carriers);
DB.LogGenera("create ltl booking", "sb carriers count", quote_carriers.Count.ToString());
DB.LogGenera("create ltl booking", "sb carriers", sb.ToString());
// Get_items_by_QuoteID
repo.Get_items_by_QuoteID(api_info.QuoteId, ref api_info);
// Get_total_cube_by_QuoteID
repo.Get_total_cube_by_QuoteID(api_info.QuoteId, ref api_info);
#region Calculate density
if (api_info.total_cube > 0 && api_info.total_weight > 0)
{
api_info.total_density = api_info.total_weight / api_info.total_cube;
DB.LogGenera(iam, "api_info.total_density", api_info.total_density.ToString());
}
else
{
DB.LogGenera(iam, "could not set density", "total cube or total weight 0");
}
#endregion
string sql = "";
#region Check for Genera carrier, if true return before getting PNW
DB.LogGenera(iam, "before DLS Shipment Import", "before DLS Shipment Import");
DB.LogGenera(iam, "api_info.CarrierDisplayName", api_info.CarrierDisplayName);
if (api_info.CarrierDisplayName.Contains("Genera"))
{
response_string = "Genera carrier, could not get PNW";
// Return error
return(public_helper.Get_response_message(ref response_string, ref content_type));
}
else
{
// Do nothing
}
#endregion
//
// Book_request_vs_BookingKey
vld.Book_request_vs_BookingKey(ref error_response, ref ltl_book_request, ref api_info);
//
#region DLS Shipment Import
HelperFuncs.dlsShipInfo dlsInfo = new HelperFuncs.dlsShipInfo();
HelperFuncs.DispatchInfo dispatchInfo = new HelperFuncs.DispatchInfo();
HelperFuncs.AccessorialsObj AccessorialsObj = new HelperFuncs.AccessorialsObj();
// Get_accessorials_by_RequestID
repo.Get_accessorials_by_RequestID(ref AccessorialsObj, ref api_info);
DLS_ShipmentImport shipment_import =
new DLS_ShipmentImport(ref dlsInfo, ref dispatchInfo, ref api_info, ref AccessorialsObj,
ref username, "0");
shipment_import.Set_DLS_ShipmentImport_objects(ref ltl_book_request, ref api_info);
//DB.LogGenera("dispatchInfo.username", dispatchInfo.username);
DB.LogGenera(iam, "before ShipmentImportDLS, mode:", AppCodeConstants.mode);
if (AppCodeConstants.mode == AppCodeConstants.prod)
{
DB.LogGenera(iam, "ShipmentImportDLS, mode:", "is prod");
// Prod ShipmentImportDLS
shipment_import.ShipmentImportDLS(ref DLS_PrimaryReferencePNW, ref quote_carriers, ref sb);
}
else if (AppCodeConstants.mode == AppCodeConstants.demo)
{
DB.LogGenera(iam, "ShipmentImportDLS, mode:", "is demo");
// Demo ShipmentImportDLS
shipment_import.test_email(ref DLS_PrimaryReferencePNW, ref quote_carriers, ref sb);
Random rnd = new Random();
int rand = rnd.Next();
DLS_PrimaryReferencePNW = string.Concat("PNW", rand);
}
else
{
DB.LogGenera(iam, "ShipmentImportDLS, mode:", "is different");
// A different mode
}
//
DB.LogGenera(iam, "after ShipmentImportDLS", "after ShipmentImportDLS");
#endregion
if (DLS_PrimaryReferencePNW == "")
{
throw new Exception("Did not get PNW");
}
#region INSERT INTO Segments.CarrierQuoteNum
// Only for Live GCM bookings
string[] tokens = new string[4];
tokens[0] = "shipmentID";
tokens[1] = ":";
tokens[2] = "\"";
tokens[3] = "\"";
int shipmentID = 0;
// Scrape the shipment id from the request string
string shipment_id = HelperFuncs.scrapeFromPage(tokens, data);
DB.LogGenera(iam, "shipment_id", shipment_id);
if (int.TryParse(shipment_id, out shipmentID) && shipmentID > 0)
{
// This happens only when Genera user booked in Live GCM
// Update CarrierQuoteNum
HelperFuncs.updateCarrierQuoteNum(ref shipmentID, ref DLS_PrimaryReferencePNW);
}
else
{
// Regular booking, from the API
// Do nothing
}
//dynamic dyn = JsonConvert.DeserializeObject(doc);
#endregion
//response_string = DLS_PrimaryReferencePNW;
DB.LogGenera(iam, "get_ltl_book_test", DLS_PrimaryReferencePNW);
//
repo.UpdateStatsQuoteWasBooked(api_info.QuoteId, DLS_PrimaryReferencePNW,
ltl_book_request.destinationCompany.Replace("'", "''"));
//
Json_helper json_helper = new Json_helper();
response_string = json_helper.Build_booking_response("0", "success", ref DLS_PrimaryReferencePNW, ref api_info);
#region INSERT INTO Genera_Booking
//
string PO = "";
if (!string.IsNullOrEmpty(ltl_book_request.poNumber))
{
PO = ltl_book_request.poNumber;
}
//
data = data.Replace("'", "''");
sql = string.Concat("INSERT INTO Genera_Booking_2(Booking_request,Booking_request_escaped,Booking_response,PNW,PO) VALUES(",
"'", data, "','",
data.Replace("\"", "\\\""), "','",
response_string.Replace("'", "''"), "','",
DLS_PrimaryReferencePNW, "','",
PO.Replace("'", "''"),
"')");
HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringAesAPI, ref sql, "Get_LTL_ratesController");
#endregion
return(public_helper.Get_response_message(ref response_string, ref content_type));
}
catch (Exception e)
{
#region Unknown error
DB.LogGenera(iam, "get_ltl_book_test", e.ToString());
Json_helper json_helper = new Json_helper();
response_string = json_helper.Build_booking_error_response("2",
string.Concat("Could not create booking. Please try again."));
Models.Public.LTL.Helper public_helper = new Models.Public.LTL.Helper();
string content_type = "application/json";
string sql = string.Concat("INSERT INTO Genera_Booking_2(Booking_request,Booking_request_escaped,Booking_Response,PNW) VALUES(",
"'", data, "','",
data.Replace("\"", "\\\""), "','",
response_string, "','",
DLS_PrimaryReferencePNW, "')");
HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringAesAPI, ref sql, "Get_LTL_ratesController");
return(public_helper.Get_response_message(ref response_string, ref content_type));
#endregion
}
}
public HttpResponseMessage Post()
{
try
{
#region Authentication
var re = Request;
var headers = re.Headers;
string username = "", password = "";
if (headers.Contains("username"))
{
username = headers.GetValues("username").First();
DB.LogGenera(iam, "username", username);
}
else
{
DB.LogGenera(iam, "username", "none found");
}
if (headers.Contains("password"))
{
password = headers.GetValues("password").First();
DB.LogGenera(iam, "password", password);
}
else
{
DB.LogGenera(iam, "password", "none found");
}
#endregion
string data = this.Request.Content.ReadAsStringAsync().Result;
DB.LogGenera(iam, "Public API cancel_ltl_book_test data", data);
LTLCancelBookRequest ltl_cancel_book_request = new LTLCancelBookRequest();
ltl_cancel_book_request = new JavaScriptSerializer().Deserialize <LTLCancelBookRequest>(data);
Models.Public.LTL.Helper public_helper = new Models.Public.LTL.Helper();
string content_type = "application/json";
string response_string = "success";
if (ltl_cancel_book_request == null || ltl_cancel_book_request.PNW == null)
{
response_string = "To cancel booking please give a valid PNW number, you gave: null";
return(public_helper.Get_response_message(ref response_string, ref content_type));
}
if (ltl_cancel_book_request.PNW.Contains("PNW"))
{
// Do nothing
}
else
{
response_string = "To cancel booking please give a valid PNW number, you gave: " + ltl_cancel_book_request.PNW;
return(public_helper.Get_response_message(ref response_string, ref content_type));
}
string po = "", our_first_carrier = "", genera_first_carrier = "",
our_first_cost_str = "", genera_first_cost_str = "";
decimal our_first_cost = 0m, genera_first_cost = 0m;
public_helper.Get_cancelled_pnw_info(ltl_cancel_book_request.PNW, ref po,
ref our_first_carrier, ref genera_first_carrier, ref our_first_cost, ref genera_first_cost);
//
if (genera_first_cost > 0m)
{
genera_first_cost_str = genera_first_cost.ToString();
}
//
//StringBuilder email_body = new StringBuilder();
//email_body.Append(string.Concat("Please cancel Pickup for ", ltl_cancel_book_request.PNW));
#region Send email to CS
// Send email to CS
EmailInfo info = new EmailInfo();
//info.body = string.Concat("Please cancel Pickup for ", ltl_cancel_book_request.PNW);
info.body = string.Concat("Please cancel Pickup for ", ltl_cancel_book_request.PNW, "<br><br>",
"<span style='font-weight: bold;'>BOL/PO#</span>: ", po, "<br><br>",
"<span style='font-weight: bold;'>Our carrier</span>: ", our_first_carrier, "<br><br>",
"<span style='font-weight: bold;'>Rate</span>: ", our_first_cost, "<br><br>",
"<span style='font-weight: bold;'>Genera carrier</span>: ", genera_first_carrier, "<br><br>",
"<span style='font-weight: bold;'>Rate</span>: ", genera_first_cost_str, "<br><br>"
);
info.fromAddress = AppCodeConstants.Alex_email;
info.to = string.Concat("cs" + AppCodeConstants.email_domain, " ", AppCodeConstants.BobsEmail, " ", AppCodeConstants.AnnesEmail);
info.fromName = "Genera";
//info.bcc = AppCodeConstants.Alex_email;
info.subject = "Cancellation # " + ltl_cancel_book_request.PNW;
Mail mail = new Mail(ref info);
mail.SendEmail();
#endregion
#region Update DB tables
string sql = string.Concat("INSERT INTO Genera_Cancel_Booking(PNW) VALUES(",
"'", ltl_cancel_book_request.PNW, "')");
HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringAesAPI, ref sql, "Cancel_LTL_bookingController");
DB.LogGenera(iam, "cancel_ltl_book_test", response_string);
//
//sql = string.Concat("UPDATE SQL_STATS_GCM SET PNW='Cancelled' ",
// "WHERE PNW='", ltl_cancel_book_request.PNW, "'");
//HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringRater2009, ref sql, "Cancel_LTL_bookingController");
sql = string.Concat("UPDATE SQL_STATS_GCM SET PNW_Cancelled='True' ",
"WHERE PNW='", ltl_cancel_book_request.PNW, "'");
HelperFuncs.ExecuteNonQuery(AppCodeConstants.connStringRater2009, ref sql, "Cancel_LTL_bookingController");
//
//response_string = "test done";
#endregion
return(public_helper.Get_response_message(ref response_string, ref content_type));
}
catch (Exception e)
{
#region Unknown error
DB.LogGenera(iam, "cancel_ltl_book_test", e.ToString());
//Xml_helper xml_helper = new Xml_helper();
Json_helper json_helper = new Json_helper();
// string request_format = "XML";
//return xml_helper.Build_error_response("2",
// string.Concat("Could not find any rates. Please try again."), ref request_format);
string response_string = json_helper.Build_error_response("2",
string.Concat("Could not cancel booking. Please try again."));
Models.Public.LTL.Helper public_helper = new Models.Public.LTL.Helper();
string content_type = "application/json";
return(public_helper.Get_response_message(ref response_string, ref content_type));
//return new HttpResponseMessage()
//{
// Content = new StringContent(response_string, Encoding.UTF8, "application/json")
//};
#endregion
}
}