//MicroService 4
public IActionResult ReadMQ2OPLDNCreateSPNPushTOMQ3()
{
try
{
//Read from MQ
OPLD opldObject = CommonUtility <OPLD> .PullFromActiveMQ(2);
ServicePoint servicePointObject = new ServicePoint();
//CreateServicepoint
//Check if opld tracking number matches with dials matching number
SakilaContext context = HttpContext.RequestServices.GetService(typeof(SakilaContext)) as SakilaContext;
DIALS dialsObject = context.GetMatchingDialsID(opldObject.TrackingNumber);
if (dialsObject != null)
{
servicePointObject = ServicePointUtility.CreateServicePoint(opldObject, dialsObject.ConsigneeName, dialsObject.ClarifiedSignature, true);
}
else
{
servicePointObject = ServicePointUtility.CreateServicePoint(opldObject, "", "", false);
}
//Push OPLD in to Active MQ2
CommonUtility <ServicePoint> .PushToActiveMQ(servicePointObject, 3);
}
catch
{
return(new JsonResult(new { Result = System.Net.HttpStatusCode.InternalServerError }));
}
return(Ok());
}
public static ServicePoint CreateServicePoint(OPLD opldDetails, string consigneeName, string clarifiedSignature, bool isMatch)
{
//Create Service point
ServicePoint servicePoint = new ServicePoint();
servicePoint.AddressLine1 = opldDetails.AddressLine1;
servicePoint.AddressLine2 = opldDetails.AddressLine2;
servicePoint.AddressLine3 = opldDetails.AddressLine3;
servicePoint.AddressType = opldDetails.AddressType;
servicePoint.AttentionName = opldDetails.AttentionName;
servicePoint.CityName = opldDetails.CityName;
servicePoint.CountryCode = opldDetails.CountryCode;
servicePoint.CreatedDate = System.DateTime.Now.ToString();
servicePoint.PhoneNumber = opldDetails.PhoneNumber;
servicePoint.ShiperNumber = opldDetails.ShiperNumber;
servicePoint.StateCode = opldDetails.StateCode;
servicePoint.ZipCode = opldDetails.ZipCode;
servicePoint.ConsigneeName = clarifiedSignature;
servicePoint.SignatureClarify = clarifiedSignature;
servicePoint.ServicePointStatus = (isMatch ? "Match found between OPld and DIALS" : "No Match found between OPld and DIALS");
return(servicePoint);
}
public static ServicePoint CreateServicePoint(OPLD opldDetails, string consigneeName, string clarifiedSignature, bool isMatch)
{
//Create Service point
ServicePoint servicePoint = new ServicePoint();
servicePoint.AddressLine1 = opldDetails.SHIP_ADDR_LINE_1_1;
servicePoint.AddressLine2 = opldDetails.SHIP_ADDR_LINE_1_2;
servicePoint.AddressLine3 = opldDetails.SHIP_ADDR_LINE_1_3;
servicePoint.AddressType = opldDetails.SHIP_ADDR_TYPE_1;
servicePoint.AttentionName = opldDetails.SHIP_ATTENTION_NAME_1;
servicePoint.CityName = opldDetails.SHIP_CITY_NAME_1;
servicePoint.CountryCode = opldDetails.SHIP_COUNTRY_CODE_1;
servicePoint.CreatedDate = System.DateTime.Now.ToString();
servicePoint.PhoneNumber = opldDetails.SHIP_PH_NR_1;
servicePoint.ShiperNumber = opldDetails.PAGE_SHIPPER_NUMBER;
servicePoint.StateCode = opldDetails.SHIP_STATE_CODE_1;
servicePoint.ZipCode = opldDetails.SHIP_ZIP_CODE_1;
servicePoint.ConsigneeName = string.IsNullOrEmpty(consigneeName) ? opldDetails.SHIP_ATTENTION_NAME_1 : consigneeName;
servicePoint.SignatureClarify = clarifiedSignature;
servicePoint.ServicePointStatus = (isMatch ? "Match found between OPld and DIALS" : "No Match found between OPld and DIALS");
return(servicePoint);
}
public IActionResult ProcessOPLDNPushTOMQ1(OPLD opldObject)
{
try
{
//log.Info(DateTime.Now.ToString() + " AMS-POC: OPLD file processing started.");
//Process OPLD data
//var opldProcString = Request.Headers["opldProcString"];
//OPLD opldObject = Newtonsoft.Json.JsonConvert.DeserializeObject<OPLD>(opldProcString);
//log.Info(DateTime.Now.ToString() + " AMS-POC: OPLD file processing completed.");
//Push OPLD in to Active MQ1
if (!string.IsNullOrEmpty(opldObject.TrackingNumber))
{
CommonUtility <OPLD> .PushToActiveMQ(opldObject, 1);
log.Info(DateTime.Now.ToString() + " AMS-POC: OPLD message pushed to MQ1.");
}
else
{
log.Warn(DateTime.Now.ToString() + " AMS-POC: Tracking number not found in OPLD message.");
}
}
catch (Exception ex)
{
log.Error(DateTime.Now.ToString() + " AMS-POC: " + Convert.ToString(ex.Message));
return(new JsonResult(new { Result = System.Net.HttpStatusCode.InternalServerError }));
}
return(Ok(new { Result = "Success" }));
}
//MicroService 2
public IActionResult ProcessMQ1OPLDMessageNWriteToDBNMQ2()
{
try
{
//Read from MQ
OPLD opldObject = CommonUtility <OPLD> .PullFromActiveMQ(1);
log.Info(DateTime.Now.ToString() + " AMS-POC: OPLD message read from MQ1.");
//Store in to DB
SakilaContext context = HttpContext.RequestServices.GetService(typeof(SakilaContext)) as SakilaContext;
context.AddNewOPLD(opldObject);
log.Info(DateTime.Now.ToString() + " AMS-POC: OPLD message inserted in to database.");
//Push OPLD in to Active MQ2
CommonUtility <OPLD> .PushToActiveMQ(opldObject, 2);
log.Info(DateTime.Now.ToString() + " AMS-POC: OPLD message pushed to MQ2.");
}
catch (Exception ex)
{
log.Error(DateTime.Now.ToString() + " AMS-POC: " + Convert.ToString(ex.Message));
return(new JsonResult(new { Result = System.Net.HttpStatusCode.InternalServerError }));
}
return(Ok());
}
private static void Consumer_Listener(IMessage message)
{
// Read from MQ
OPLD opldObject = Newtonsoft.Json.JsonConvert.DeserializeObject <OPLD>((message as ITextMessage).Text);
CreateServicePointController createServicePointController = new CreateServicePointController();
createServicePointController.MicroServiceServicePointGeneration(opldObject);
}
public IActionResult MicroServiceServicePointGeneration(OPLD opldObject)
{
try
{
log.Info(DateTime.Now.ToString() + " AMS-POC-MicroServiceServicePointGeneration: Read OPLD message and Start processing.");
//CreateServicepoint
//SakilaContext context = new SakilaContext("server=127.0.01;port=3306;database=ams;user=root;password=techM@Ups1");
SakilaContext context = new SakilaContext("server=techm.cooavdyjxzoz.us-east-1.rds.amazonaws.com;port=3306;database=ams;user=root;password=Password123");
log.Info(DateTime.Now.ToString() + " AMS-POC-MicroServiceServicePointGeneration: Getting OPLD tracking number matching DIALS record.");
//Check if opld tracking number matches with dials matching number
DIALS dialsObject = context.GetMatchingDialsID(opldObject.SHIP_SP_NR);
ServicePoint servicePointObject = new ServicePoint();
if (!string.IsNullOrEmpty(dialsObject.TrackingNumber))
{
//Match found
//Compare OPLD Add1 with SP Add1 and Update timestamp and increase Stop ID
ServicePoint servicePoint = context.CompareOPLDAdd1WithSPAddr1(opldObject.SHIP_ADDR_LINE_1_1);
if (servicePoint != null && (servicePoint.ServicePointID != 0))
{
context.UpdateServicePoint(servicePoint.ServicePointID);
}
else
{
servicePointObject = ServicePointUtility.CreateServicePoint(opldObject, dialsObject.ConsigneeName, dialsObject.ClarifiedSignature, true);
log.Info(DateTime.Now.ToString() + " AMS-POC-MicroServiceServicePointGeneration: Writing ServicePoint in to DB.");
//Write Servicepoint to DB
context.AddNewServicePoint(servicePointObject);
}
}
else
{
//Create Service Point with Stop ID = 1
servicePointObject = ServicePointUtility.CreateServicePoint(opldObject, opldObject.SHIP_ATTENTION_NAME_1, "", false);
log.Info(DateTime.Now.ToString() + " AMS-POC-MicroServiceServicePointGeneration: Writing ServicePoint in to DB.");
//Write Servicepoint to DB
context.AddNewServicePoint(servicePointObject);
}
//Update OPLD Processing Status
context.UpdateOPLDProcessingStatus(opldObject.SHIP_SP_NR);
}
catch (Exception ex)
{
log.Error(DateTime.Now.ToString() + " AMS-POC-MicroServiceServicePointGeneration: " + Convert.ToString(ex.ToString()));
return(new JsonResult(new { Result = System.Net.HttpStatusCode.InternalServerError }));
}
return(Ok(new { Result = "Success" }));
}
private static void ConsumerQ1_Listener(IMessage message)
{
//Read from MQ
OPLD opldObject = Newtonsoft.Json.JsonConvert.DeserializeObject <OPLD>((message as ITextMessage).Text);
string myString = Newtonsoft.Json.JsonConvert.SerializeObject(opldObject);
HttpClient client = new HttpClient();
var content = new StringContent(Newtonsoft.Json.JsonConvert.SerializeObject(opldObject));
System.Threading.Tasks.Task <HttpResponseMessage> response = client.PostAsync("http://localhost:59477/api/ProcessOPLD/ProcessMQ1OPLDMessageNWriteToDBNMQ2?strOPLD=" + myString, content);
}
private static void ConsumerQ2_Listener(IMessage message)
{
//Read from MQ
OPLD opldObject = Newtonsoft.Json.JsonConvert.DeserializeObject <OPLD>((message as ITextMessage).Text);
string myString = Newtonsoft.Json.JsonConvert.SerializeObject(opldObject);
HttpClient client = new HttpClient();
var content = new StringContent(Newtonsoft.Json.JsonConvert.SerializeObject(opldObject));
System.Threading.Tasks.Task <HttpResponseMessage> response = client.PostAsync("http://localhost:59477/api/ServicePoint/ReadMQ2OPLDNCreateSPNPushTOMQ3?strOPLD=" + myString, content);
}
public bool AddNewOPLD(OPLD newOPLD)
{
int rowsAffected = 0;
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO oplddetails VALUES('" + (GetLastOPLDID() + 1) + "','" + newOPLD.TrackingNumber + "','" + newOPLD.ShiperNumber + "','" + newOPLD.ShiperCountry +
"','" + newOPLD.VersionNumber + "','" + newOPLD.AttentionName + "','" + newOPLD.AddressType + "','" + newOPLD.AddressLine1 + "','" + newOPLD.AddressLine2 +
"','" + newOPLD.AddressLine3 + "','" + newOPLD.CityName + "','" + newOPLD.StateCode + "','" + newOPLD.ZipCode +
"','" + newOPLD.CountryCode + "','" + newOPLD.PhoneNumber + "','" + DateTime.Now.ToString() + "')", conn);
rowsAffected = cmd.ExecuteNonQuery();
}
return(rowsAffected > 0 ? true : false);
}
//MicroService 4
public IActionResult ReadMQ2OPLDNCreateSPNPushTOMQ3()
{
try
{
//Read from MQ
//OPLD opldObject = CommonUtility<OPLD>.PullFromActiveMQ(2);
log.Info(DateTime.Now.ToString() + " AMS-POC: Service point genration process started.");
ServicePoint servicePointObject = new ServicePoint();
var opldProcString = Request.Headers["opldProcString"];
OPLD opldObject = Newtonsoft.Json.JsonConvert.DeserializeObject <OPLD>(opldProcString);
//CreateServicepoint
//Check if opld tracking number matches with dials matching number
SakilaContext context = HttpContext.RequestServices.GetService(typeof(SakilaContext)) as SakilaContext;
DIALS dialsObject = context.GetMatchingDialsID(opldObject.TrackingNumber);
if (dialsObject != null)
{
servicePointObject = ServicePointUtility.CreateServicePoint(opldObject, dialsObject.ConsigneeName, dialsObject.ClarifiedSignature, true);
}
else
{
servicePointObject = ServicePointUtility.CreateServicePoint(opldObject, "", "", false);
}
//Push OPLD in to Active MQ2
CommonUtility <ServicePoint> .PushToActiveMQ(servicePointObject, 3);
log.Info(DateTime.Now.ToString() + " AMS-POC: Service point Created and Pushed to MQ3.");
}
catch (Exception ex)
{
log.Error(DateTime.Now.ToString() + " AMS-POC: " + Convert.ToString(ex.Message));
return(new JsonResult(new { Result = System.Net.HttpStatusCode.InternalServerError }));
}
return(Ok());
}
//public static void PushToActiveMQ(T opldObject, int queueNumber)
//{
// Uri connecturi = new Uri("activemq:tcp://localhost:61616");
// // NOTE: ensure the nmsprovider-activemq.config file exists in the executable folder.
// IConnectionFactory factory = new NMSConnectionFactory(connecturi);
// using (IConnection connection = factory.CreateConnection())
// using (ISession session = connection.CreateSession())
// {
// IDestination destination = SessionUtil.GetDestination(session, GETQueueName(queueNumber));
// // Create the producer
// using (IMessageProducer producer = session.CreateProducer(destination))
// {
// // Start the connection so that messages will be processed.
// connection.Start();
// producer.DeliveryMode = MsgDeliveryMode.Persistent;
// var messageToQueue = JsonConvert.SerializeObject(opldObject);
// //var ss = "Test Message " + DateTime.Now;
// ITextMessage request = session.CreateTextMessage(messageToQueue);
// request.NMSCorrelationID = "abc";
// //request.Properties["NMSXGroupID"] = "cheese";
// //request.Properties["myHeader"] = "Cheddar";
// producer.Send(request);
// }
// }
//}
public static OPLD PullFromActiveMQ()
{
try
{
Uri connecturi = new Uri("activemq:tcp://54.173.238.145:61616");
// NOTE: ensure the nmsprovider-activemq.config file exists in the executable folder.
IConnectionFactory factory = new NMSConnectionFactory(connecturi);
using (IConnection connection = factory.CreateConnection())
using (ISession session = connection.CreateSession())
{
IDestination destination = SessionUtil.GetDestination(session, "queue://OPLDProcess");
// Create a consumer and producer
using (IMessageConsumer consumer = session.CreateConsumer(destination))
using (IMessageProducer producer = session.CreateProducer(destination))
{
// Start the connection so that messages will be processed.
connection.Start();
producer.DeliveryMode = MsgDeliveryMode.Persistent;
// Consume a message
ITextMessage message = consumer.Receive() as ITextMessage;
if (message == null)
{
return(default(OPLD));
}
else
{
OPLD opld = Newtonsoft.Json.JsonConvert.DeserializeObject <OPLD>(message.Text);
return(opld);
}
}
}
}
catch {
return(default(OPLD));
}
}
public static OPLD ProcessOPLD(string opldString)
{
OPLD opldData = new OPLD();
opldData.TrackingNumber = opldString.Length > 3533 ? opldString.Substring(3533, 35).Trim() : "";
opldData.VersionNumber = opldString.Length > 2 ? opldString.Substring(2, 4).Trim() : "";
opldData.ShiperNumber = opldString.Length > 55 ? opldString.Substring(55, 10).Trim() : "";
opldData.ShiperCountry = opldString.Length > 65 ? opldString.Substring(65, 2).Trim() : "";
opldData.AttentionName = opldString.Length > 229 ? opldString.Substring(229, 35).Trim() : "";
opldData.AddressType = opldString.Length > 182 ? opldString.Substring(182, 2).Trim() : "";
opldData.AddressLine1 = opldString.Length > 264 ? opldString.Substring(264, 35).Trim() : "";
opldData.AddressLine2 = opldString.Length > 299 ? opldString.Substring(299, 35).Trim() : "";
opldData.AddressLine3 = opldString.Length > 334 ? opldString.Substring(334, 35).Trim() : "";
opldData.CityName = opldString.Length > 369 ? opldString.Substring(369, 30).Trim() : "";
opldData.StateCode = opldString.Length > 399 ? opldString.Substring(399, 5).Trim() : "";
opldData.ZipCode = opldString.Length > 404 ? opldString.Substring(404, 9).Trim() : "";
opldData.CountryCode = opldString.Length > 413 ? opldString.Substring(413, 2).Trim() : "";
opldData.PhoneNumber = opldString.Length > 415 ? opldString.Substring(415, 15).Trim() : "";
return(opldData);
}
//MicroService 2
public IActionResult ProcessMQ1OPLDMessageNWriteToDBNMQ2()
{
try
{
//Read from MQ
OPLD opldObject = CommonUtility <OPLD> .PullFromActiveMQ(1);
//Store in to DB
SakilaContext context = HttpContext.RequestServices.GetService(typeof(SakilaContext)) as SakilaContext;
context.AddNewOPLD(opldObject);
//Push OPLD in to Active MQ2
CommonUtility <OPLD> .PushToActiveMQ(opldObject, 2);
}
catch
{
return(new JsonResult(new { Result = System.Net.HttpStatusCode.InternalServerError }));
}
return(Ok());
}
public IActionResult MicroServiceServicePointGeneration(OPLD opldObject)
{
try
{
log.Info(DateTime.Now.ToString() + " AMS-POC-MicroServiceServicePointGeneration: Read OPLD message and Start processing.");
//CreateServicepoint
SakilaContext context = new SakilaContext("server=127.0.01;port=3306;database=ams;user=root;password=techM@Ups1");//HttpContext.RequestServices.GetService(typeof(SakilaContext)) as SakilaContext;
log.Info(DateTime.Now.ToString() + " AMS-POC-MicroServiceServicePointGeneration: Getting OPLD tracking number matching DIALS record.");
//Check if opld tracking number matches with dials matching number
DIALS dialsObject = context.GetMatchingDialsID(opldObject.TrackingNumber);
ServicePoint servicePointObject = new ServicePoint();
if (!string.IsNullOrEmpty(dialsObject.TrackingNumber))
{
servicePointObject = ServicePointUtility.CreateServicePoint(opldObject, dialsObject.ConsigneeName, dialsObject.ClarifiedSignature, true);
}
else
{
servicePointObject = ServicePointUtility.CreateServicePoint(opldObject, "", "", false);
}
log.Info(DateTime.Now.ToString() + " AMS-POC-MicroServiceServicePointGeneration: Writing ServicePoint in to DB.");
//Write Servicepoint to DB
context.AddNewServicePoint(servicePointObject);
//Update OPLD Processing Status
context.UpdateOPLDProcessingStatus(opldObject.TrackingNumber);
}
catch (Exception ex)
{
log.Error(DateTime.Now.ToString() + " AMS-POC-MicroServiceServicePointGeneration: " + Convert.ToString(ex.Message));
return(new JsonResult(new { Result = System.Net.HttpStatusCode.InternalServerError }));
}
return(Ok(new { Result = "Success" }));
}
public IActionResult MicroServiceProcessOPLDFile(OPLD opldObject)
{
try
{
//Push OPLD in DB
SakilaContext context = HttpContext.RequestServices.GetService(typeof(SakilaContext)) as SakilaContext;
context.AddNewOPLD(opldObject);
log.Info(DateTime.Now.ToString() + " AMS-POC-MicroServiceProcessOPLDNDIALSFiles: OPLD Data inserted in DB.");
//Push OPLD in to Active MQ2
CommonUtility <OPLD> .PushToActiveMQ(opldObject, 1);
log.Info(DateTime.Now.ToString() + " AMS-MicroServiceProcessOPLDNDIALSFiles: OPLD message pushed to MQ.");
}
catch (Exception ex)
{
log.Error(DateTime.Now.ToString() + " AMS-MicroServiceProcessOPLDNDIALSFiles: " + Convert.ToString(ex.Message));
return(new JsonResult(new { Result = System.Net.HttpStatusCode.InternalServerError }));
}
return(Ok(new { Result = "Success" }));
}
public IActionResult MicroServiceProcessOPLDFile(OPLD opldObject)
{
try
{
//Push OPLD in DB
//SakilaContext context = new SakilaContext("server=127.0.01;port=3306;database=ams;user=root;password=techM@Ups1");
SakilaContext context = new SakilaContext("server=techm.cooavdyjxzoz.us-east-1.rds.amazonaws.com;port=3306;database=ams;user=root;password=Password123");
context.AddNewOPLD(opldObject);
log.Info(DateTime.Now.ToString() + " AMS-POC-MicroServiceProcessOPLDNDIALSFiles: OPLD Data inserted in DB.");
//Push OPLD in to Active MQ2
CommonUtility <OPLD> .PushToActiveMQ(opldObject, 1);
log.Info(DateTime.Now.ToString() + " AMS-MicroServiceProcessOPLDNDIALSFiles: OPLD message pushed to MQ.");
}
catch (Exception ex)
{
log.Error(DateTime.Now.ToString() + " AMS-MicroServiceProcessOPLDNDIALSFiles: " + Convert.ToString(ex.Message));
return(new JsonResult(new { Result = System.Net.HttpStatusCode.InternalServerError }));
}
return(Ok(new { Result = "Success" }));
}
public Tuple <bool, int> AddNewOPLD(OPLD newOPLD)
{
int rowsAffected = 0;
int lastOPLD = (GetLastOPLDID() + 1);
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO oplddetails VALUES('" + lastOPLD + "','" + newOPLD.PAGE_SMT_REC_FLAG + "','" + newOPLD.VERSION_NUMBER + "','" + newOPLD.PAGE_INF_SRC_TYP_CD +
"','" + newOPLD.PAGE_SMT_REC_FLAG + "','" + newOPLD.VERSION_NUMBER + "','" + newOPLD.PAGE_INF_SRC_TYP_CD + "','" + newOPLD.PAGE_MAILBOX + "','" + newOPLD.PAGE_PICKUP_DT
+ "','" + newOPLD.PAGE_PICKUP_CC_DT + "','" + newOPLD.PAGE_PICKUP_YY_DT + "','" + newOPLD.PAGE_PICKUP_MM_DT + "','" + newOPLD.PAGE_PICKUP_DD_DT + "','" + newOPLD.PAGE_SEQ_NUM
+ "','" + newOPLD.PAGE_SHIPPER_NUMBER + "','" + newOPLD.PAGE_SHIPPER_COUNTRY + "','" + newOPLD.PAGE_PICKUP_BOOK + "','" + newOPLD.PAGE_PICKUP_PAGE + "','" + newOPLD.PAGE_PICKUP_BOOK_TYPE
+ "','" + newOPLD.PAGE_VOID_IND + "','" + newOPLD.PAGE_VSN_NR + "','" + newOPLD.SMT_SEQ_NR + "','" + newOPLD.SHIP_PKGING_TYP + "','" + newOPLD.SHIP_DOC_IND + "','" + newOPLD.SHIP_CONTAINER_TYPE
+ "','" + newOPLD.SHIP_BILL_TERMS + "','" + newOPLD.SHIP_PAY_MEDIA_TYPE + "','" + newOPLD.SHIP_SHR_3PB_FLG + "','" + newOPLD.SHIP_CSGN_3PB_FLG + "','" + newOPLD.SHIP_REF_TYP_CD_1
+ "','" + newOPLD.SHIP_REF_NR_1 + "','" + newOPLD.SHIP_REF_TYP_CD_2 + "','" + newOPLD.SHIP_REF_NR_2 + "','" + newOPLD.SHIP_CWT_IND + "','" + newOPLD.SHIP_ADDR_TYPE_1
+ "','" + newOPLD.SHIP_AC_NR_1 + "','" + newOPLD.SHIP_COMP_NA_1 + "','" + newOPLD.SHIP_ATTENTION_NAME_1 + "','" + newOPLD.SHIP_ADDR_LINE_1_1 + "','" + newOPLD.SHIP_ADDR_LINE_2_1
+ "','" + newOPLD.SHIP_ADDR_LINE_3_1 + "','" + newOPLD.SHIP_CITY_NAME_1 + "','" + newOPLD.SHIP_STATE_CODE_1 + "','" + newOPLD.SHIP_ZIP_CODE_1 + "','" + newOPLD.SHIP_COUNTRY_CODE_1
+ "','" + newOPLD.SHIP_PH_NR_1 + "','" + newOPLD.SHIP_ADDR_TYPE_2 + "','" + newOPLD.SHIP_AC_NR_2 + "','" + newOPLD.SHIP_COMP_NA_2 + "','" + newOPLD.SHIP_ATTENTION_NAME_2
+ "','" + newOPLD.SHIP_ADDR_LINE_1_2 + "','" + newOPLD.SHIP_ADDR_LINE_2_2 + "','" + newOPLD.SHIP_ADDR_LINE_3_2 + "','" + newOPLD.SHIP_CITY_NAME_2 + "','" + newOPLD.SHIP_STATE_CODE_2
+ "','" + newOPLD.SHIP_ZIP_CODE_2 + "','" + newOPLD.SHIP_COUNTRY_CODE_2 + "','" + newOPLD.SHIP_PH_NR_2 + "','" + newOPLD.SHIP_ADDR_TYPE_3 + "','" + newOPLD.SHIP_AC_NR_3
+ "','" + newOPLD.SHIP_COMP_NA_3 + "','" + newOPLD.SHIP_ATTENTION_NAME_3 + "','" + newOPLD.SHIP_ADDR_LINE_1_3 + "','" + newOPLD.SHIP_ADDR_LINE_2_3 + "','" + newOPLD.SHIP_ADDR_LINE_3_3
+ "','" + newOPLD.SHIP_CITY_NAME_3 + "','" + newOPLD.SHIP_STATE_CODE_3 + "','" + newOPLD.SHIP_ZIP_CODE_3 + "','" + newOPLD.SHIP_COUNTRY_CODE_3 + "','" + newOPLD.SHIP_PH_NR_3
+ "','" + newOPLD.SHIP_ADDR_TYPE_4 + "','" + newOPLD.SHIP_AC_NR_4 + "','" + newOPLD.SHIP_COMP_NA_4 + "','" + newOPLD.SHIP_ATTENTION_NAME_4 + "','" + newOPLD.SHIP_ADDR_LINE_1_4
+ "','" + newOPLD.SHIP_ADDR_LINE_2_4 + "','" + newOPLD.SHIP_ADDR_LINE_3_4 + "','" + newOPLD.SHIP_CITY_NAME_4 + "','" + newOPLD.SHIP_STATE_CODE_4 + "','" + newOPLD.SHIP_ZIP_CODE_4
+ "','" + newOPLD.SHIP_COUNTRY_CODE_4 + "','" + newOPLD.SHIP_PH_NR_4 + "','" + newOPLD.SHIP_ADDR_TYPE_5 + "','" + newOPLD.SHIP_AC_NR_5 + "','" + newOPLD.SHIP_COMP_NA_5
+ "','" + newOPLD.SHIP_ATTENTION_NAME_5 + "','" + newOPLD.SHIP_ADDR_LINE_1_5 + "','" + newOPLD.SHIP_ADDR_LINE_2_5 + "','" + newOPLD.SHIP_ADDR_LINE_3_5 + "','" + newOPLD.SHIP_CITY_NAME_5
+ "','" + newOPLD.SHIP_STATE_CODE_5 + "','" + newOPLD.SHIP_ZIP_CODE_5 + "','" + newOPLD.SHIP_COUNTRY_CODE_5 + "','" + newOPLD.SHIP_PH_NR_5 + "','" + newOPLD.SHIP_ADDR_TYPE_6
+ "','" + newOPLD.SHIP_AC_NR_6 + "','" + newOPLD.SHIP_COMP_NA_6 + "','" + newOPLD.SHIP_ATTENTION_NAME_6 + "','" + newOPLD.SHIP_ADDR_LINE_1_6 + "','" + newOPLD.SHIP_ADDR_LINE_2_6
+ "','" + newOPLD.SHIP_ADDR_LINE_3_6 + "','" + newOPLD.SHIP_CITY_NAME_6 + "','" + newOPLD.SHIP_STATE_CODE_6 + "','" + newOPLD.SHIP_ZIP_CODE_6 + "','" + newOPLD.SHIP_COUNTRY_CODE_6
+ "','" + newOPLD.SHIP_PH_NR_6 + "','" + newOPLD.SHIP_ADDR_TYPE_7 + "','" + newOPLD.SHIP_AC_NR_7 + "','" + newOPLD.SHIP_COMP_NA_7 + "','" + newOPLD.SHIP_ATTENTION_NAME_7
+ "','" + newOPLD.SHIP_ADDR_LINE_1_7 + "','" + newOPLD.SHIP_ADDR_LINE_2_7 + "','" + newOPLD.SHIP_ADDR_LINE_3_7 + "','" + newOPLD.SHIP_CITY_NAME_7 + "','" + newOPLD.SHIP_STATE_CODE_7
+ "','" + newOPLD.SHIP_ZIP_CODE_7 + "','" + newOPLD.SHIP_COUNTRY_CODE_7 + "','" + newOPLD.SHIP_PH_NR_7 + "','" + newOPLD.SHIP_ADDR_TYPE_8 + "','" + newOPLD.SHIP_AC_NR_8
+ "','" + newOPLD.SHIP_COMP_NA_8 + "','" + newOPLD.SHIP_ATTENTION_NAME_8 + "','" + newOPLD.SHIP_ADDR_LINE_1_8 + "','" + newOPLD.SHIP_ADDR_LINE_2_8 + "','" + newOPLD.SHIP_ADDR_LINE_3_8
+ "','" + newOPLD.SHIP_CITY_NAME_8 + "','" + newOPLD.SHIP_STATE_CODE_8 + "','" + newOPLD.SHIP_ZIP_CODE_8 + "','" + newOPLD.SHIP_COUNTRY_CODE_8 + "','" + newOPLD.SHIP_PH_NR_8
+ "','" + newOPLD.SHIP_PKG_QTY + "','" + newOPLD.SHIP_OCA_IND + "','" + newOPLD.SHIP_COMM_RES_IND + "','" + newOPLD.SHIP_SVC_LVL + "','" + newOPLD.SHIP_SVC_LVL_IND + "','" + newOPLD.SHIP_WAYBILL_BRK_NR
+ "','" + newOPLD.SHIP_WAYBILL_PRINT_IND + "','" + newOPLD.SHIP_ACC_TYPE_1 + "','" + newOPLD.SHIP_ACC_VALUE_1 + "','" + newOPLD.SHIP_ACC_VALUE_N_1 + "','" + newOPLD.SHIP_ACC_CCY_CD_1 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_1
+ "','" + newOPLD.SHIP_ACC_TYPE_2 + "','" + newOPLD.SHIP_ACC_VALUE_2 + "','" + newOPLD.SHIP_ACC_VALUE_N_2 + "','" + newOPLD.SHIP_ACC_CCY_CD_2 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_2
+ "','" + newOPLD.SHIP_ACC_TYPE_3 + "','" + newOPLD.SHIP_ACC_VALUE_3 + "','" + newOPLD.SHIP_ACC_VALUE_N_3 + "','" + newOPLD.SHIP_ACC_CCY_CD_3 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_3
+ "','" + newOPLD.SHIP_ACC_TYPE_4 + "','" + newOPLD.SHIP_ACC_VALUE_4 + "','" + newOPLD.SHIP_ACC_VALUE_N_4 + "','" + newOPLD.SHIP_ACC_CCY_CD_4 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_4
+ "','" + newOPLD.SHIP_ACC_TYPE_5 + "','" + newOPLD.SHIP_ACC_VALUE_5 + "','" + newOPLD.SHIP_ACC_VALUE_N_5 + "','" + newOPLD.SHIP_ACC_CCY_CD_5 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_5
+ "','" + newOPLD.SHIP_ACC_TYPE_6 + "','" + newOPLD.SHIP_ACC_VALUE_6 + "','" + newOPLD.SHIP_ACC_VALUE_N_6 + "','" + newOPLD.SHIP_ACC_CCY_CD_6 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_6
+ "','" + newOPLD.SHIP_ACC_TYPE_7 + "','" + newOPLD.SHIP_ACC_VALUE_7 + "','" + newOPLD.SHIP_ACC_VALUE_N_7 + "','" + newOPLD.SHIP_ACC_CCY_CD_7 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_7 + "','" + newOPLD.SHIP_ACC_TYPE_8
+ "','" + newOPLD.SHIP_ACC_VALUE_8 + "','" + newOPLD.SHIP_ACC_VALUE_N_8 + "','" + newOPLD.SHIP_ACC_CCY_CD_8 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_8 + "','" + newOPLD.SHIP_ACC_TYPE_9
+ "','" + newOPLD.SHIP_ACC_VALUE_9 + "','" + newOPLD.SHIP_ACC_VALUE_N_9 + "','" + newOPLD.SHIP_ACC_CCY_CD_9 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_9 + "','" + newOPLD.SHIP_ACC_TYPE_10
+ "','" + newOPLD.SHIP_ACC_VALUE_10 + "','" + newOPLD.SHIP_ACC_VALUE_N_10 + "','" + newOPLD.SHIP_ACC_CCY_CD_10 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_10 + "','" + newOPLD.SHIP_ACC_TYPE_11
+ "','" + newOPLD.SHIP_ACC_VALUE_11 + "','" + newOPLD.SHIP_ACC_VALUE_N_11 + "','" + newOPLD.SHIP_ACC_CCY_CD_11 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_11 + "','" + newOPLD.SHIP_ACC_TYPE_12
+ "','" + newOPLD.SHIP_ACC_VALUE_12 + "','" + newOPLD.SHIP_ACC_VALUE_N_12 + "','" + newOPLD.SHIP_ACC_CCY_CD_12 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_12 + "','" + newOPLD.SHIP_ACC_TYPE_13
+ "','" + newOPLD.SHIP_ACC_VALUE_13 + "','" + newOPLD.SHIP_ACC_VALUE_N_13 + "','" + newOPLD.SHIP_ACC_CCY_CD_13 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_13 + "','" + newOPLD.SHIP_ACC_TYPE_14 + "','" + newOPLD.SHIP_ACC_VALUE_14
+ "','" + newOPLD.SHIP_ACC_VALUE_N_14 + "','" + newOPLD.SHIP_ACC_CCY_CD_14 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_14 + "','" + newOPLD.SHIP_ACC_TYPE_15 + "','" + newOPLD.SHIP_ACC_VALUE_15
+ "','" + newOPLD.SHIP_ACC_VALUE_N_15 + "','" + newOPLD.SHIP_ACC_CCY_CD_15 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_15 + "','" + newOPLD.SHIP_ACC_TYPE_16 + "','" + newOPLD.SHIP_ACC_VALUE_16
+ "','" + newOPLD.SHIP_ACC_VALUE_N_16 + "','" + newOPLD.SHIP_ACC_CCY_CD_16 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_16 + "','" + newOPLD.SHIP_ACC_TYPE_17 + "','" + newOPLD.SHIP_ACC_VALUE_17
+ "','" + newOPLD.SHIP_ACC_VALUE_N_17 + "','" + newOPLD.SHIP_ACC_CCY_CD_17 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_17 + "','" + newOPLD.SHIP_ACC_TYPE_18 + "','" + newOPLD.SHIP_ACC_VALUE_18
+ "','" + newOPLD.SHIP_ACC_VALUE_N_18 + "','" + newOPLD.SHIP_ACC_CCY_CD_18 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_18 + "','" + newOPLD.SHIP_ACC_TYPE_19 + "','" + newOPLD.SHIP_ACC_VALUE_19 + "','" + newOPLD.SHIP_ACC_VALUE_N_19
+ "','" + newOPLD.SHIP_ACC_CCY_CD_19 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_19 + "','" + newOPLD.SHIP_ACC_TYPE_20 + "','" + newOPLD.SHIP_ACC_VALUE_20 + "','" + newOPLD.SHIP_ACC_VALUE_N_20 + "','" + newOPLD.SHIP_ACC_CCY_CD_20
+ "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_20 + "','" + newOPLD.SHIP_ACC_TYPE_21 + "','" + newOPLD.SHIP_ACC_VALUE_21 + "','" + newOPLD.SHIP_ACC_VALUE_N_21 + "','" + newOPLD.SHIP_ACC_CCY_CD_21
+ "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_21 + "','" + newOPLD.SHIP_ACC_TYPE_22 + "','" + newOPLD.SHIP_ACC_VALUE_22 + "','" + newOPLD.SHIP_ACC_VALUE_N_22 + "','" + newOPLD.SHIP_ACC_CCY_CD_22
+ "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_22 + "','" + newOPLD.SHIP_ACC_TYPE_23 + "','" + newOPLD.SHIP_ACC_VALUE_23 + "','" + newOPLD.SHIP_ACC_VALUE_N_23 + "','" + newOPLD.SHIP_ACC_CCY_CD_23
+ "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_23 + "','" + newOPLD.SHIP_ACC_TYPE_24 + "','" + newOPLD.SHIP_ACC_VALUE_24 + "','" + newOPLD.SHIP_ACC_VALUE_N_24 + "','" + newOPLD.SHIP_ACC_CCY_CD_24
+ "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_24 + "','" + newOPLD.SHIP_ACC_TYPE_25 + "','" + newOPLD.SHIP_ACC_VALUE_25 + "','" + newOPLD.SHIP_ACC_VALUE_N_25 + "','" + newOPLD.SHIP_ACC_CCY_CD_25
+ "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_25 + "','" + newOPLD.SHIP_ACC_TYPE_26 + "','" + newOPLD.SHIP_ACC_VALUE_26 + "','" + newOPLD.SHIP_ACC_VALUE_N_26 + "','" + newOPLD.SHIP_ACC_CCY_CD_26
+ "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_26 + "','" + newOPLD.SHIP_ACC_TYPE_27 + "','" + newOPLD.SHIP_ACC_VALUE_27 + "','" + newOPLD.SHIP_ACC_VALUE_N_27 + "','" + newOPLD.SHIP_ACC_CCY_CD_27 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_27
+ "','" + newOPLD.SHIP_ACC_TYPE_28 + "','" + newOPLD.SHIP_ACC_VALUE_28 + "','" + newOPLD.SHIP_ACC_VALUE_N_28 + "','" + newOPLD.SHIP_ACC_CCY_CD_28 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_28
+ "','" + newOPLD.SHIP_ACC_TYPE_29 + "','" + newOPLD.SHIP_ACC_VALUE_29 + "','" + newOPLD.SHIP_ACC_VALUE_N_29 + "','" + newOPLD.SHIP_ACC_CCY_CD_29 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_29
+ "','" + newOPLD.SHIP_ACC_TYPE_30 + "','" + newOPLD.SHIP_ACC_VALUE_30 + "','" + newOPLD.SHIP_ACC_VALUE_N_30 + "','" + newOPLD.SHIP_ACC_CCY_CD_30 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_30
+ "','" + newOPLD.SHIP_ACC_TYPE_31 + "','" + newOPLD.SHIP_ACC_VALUE_31 + "','" + newOPLD.SHIP_ACC_VALUE_N_31 + "','" + newOPLD.SHIP_ACC_CCY_CD_31 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_31
+ "','" + newOPLD.SHIP_ACC_TYPE_32 + "','" + newOPLD.SHIP_ACC_VALUE_32 + "','" + newOPLD.SHIP_ACC_VALUE_N_32 + "','" + newOPLD.SHIP_ACC_CCY_CD_32 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_32
+ "','" + newOPLD.SHIP_ACC_TYPE_33 + "','" + newOPLD.SHIP_ACC_VALUE_33 + "','" + newOPLD.SHIP_ACC_VALUE_N_33 + "','" + newOPLD.SHIP_ACC_CCY_CD_33 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_33
+ "','" + newOPLD.SHIP_ACC_TYPE_34 + "','" + newOPLD.SHIP_ACC_VALUE_34 + "','" + newOPLD.SHIP_ACC_VALUE_N_34 + "','" + newOPLD.SHIP_ACC_CCY_CD_34 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_34 + "','" + newOPLD.SHIP_ACC_TYPE_35
+ "','" + newOPLD.SHIP_ACC_VALUE_35 + "','" + newOPLD.SHIP_ACC_VALUE_N_35 + "','" + newOPLD.SHIP_ACC_CCY_CD_35 + "','" + newOPLD.RA_SHIP_COD_CSH_ONLY_IND_35 + "','" + newOPLD.SHIP_UOM_WGT
+ "','" + newOPLD.SHIP_DIM_WEIGHT + "','" + newOPLD.SHIP_DIM_WEIGHT_NUM + "','" + newOPLD.SHIP_ACTUAL_WEIGHT + "','" + newOPLD.SHIP_ACTUAL_WEIGHT_NUM + "','" + newOPLD.SHIP_BILLED_WEIGHT
+ "','" + newOPLD.SHIP_BILLED_WEIGHT_NUM + "','" + newOPLD.SHIP_SENDER_NAME + "','" + newOPLD.SHIP_CRED_CARD_NR + "','" + newOPLD.SHIP_CRED_CARD_EXP_DT + "','" + newOPLD.SHIP_INTERNET_USER_ID
+ "','" + newOPLD.SHIP_CRED_CARD_AUTH_NR + "','" + newOPLD.SHIP_SHIPPER + "','" + newOPLD.SHIP_CONSIGNEE + "','" + newOPLD.SHIP_CNSG_SHIP_NR + "','" + newOPLD.SHIP_3PB
+ "','" + newOPLD.SHIP_3PB_SHIP_NR + "','" + newOPLD.SHIP_FREIGHT + "','" + newOPLD.SHIP_FREIGHT_SHIP_NR + "','" + newOPLD.SHIP_RLA_NR + "','" + newOPLD.SHIP_RLA_SHIP_NR
+ "','" + newOPLD.SHIP_OVS_NR + "','" + newOPLD.SHIP_IVS_NR + "','" + newOPLD.SHIP_IVS_SHIP_NR + "','" + newOPLD.SHIP_HARM_CDE + "','" + newOPLD.SHIP_MANF_CTRY_ORIG_CD + "','" + newOPLD.SHIP_CUSTM_VAL_S
+ "','" + newOPLD.SHIP_CUSTM_VAL_N + "','" + newOPLD.SHIP_GCCN_IND + "','" + newOPLD.SHIP_INV_TOTAL_AMT_N + "','" + newOPLD.SHIP_EXP_AC_NR + "','" + newOPLD.SHIP_EXP_COMP_NA + "','" + newOPLD.SHIP_EXP_ATTN_NAME
+ "','" + newOPLD.SHIP_EXP_ADDR_LINE_1 + "','" + newOPLD.SHIP_EXP_ADDR_LINE_2 + "','" + newOPLD.SHIP_EXP_ADDR_LINE_3 + "','" + newOPLD.SHIP_EXP_CITY + "','" + newOPLD.SHIP_EXP_STATE_CD
+ "','" + newOPLD.SHIP_EXP_POSTAL_CD + "','" + newOPLD.SHIP_EXP_CNTRY_CD + "','" + newOPLD.SHIP_IMP_CITY + "','" + newOPLD.SHIP_IMP_STATE_CD + "','" + newOPLD.SHIP_IMP_POSTAL_CD + "','" + newOPLD.SHIP_IMP_CNTRY_CD
+ "','" + newOPLD.SHIP_CLRNCE_PORT + "','" + newOPLD.SHIP_SP_NR + "','" + newOPLD.SHIP_NR_PKG + "','" + newOPLD.SHIP_SVC_TYP_CD + "','" + newOPLD.SHIP_CSGN_ADDR_VAL_RES + "','" + newOPLD.SHIP_CRNCY_CDE
+ "','" + newOPLD.SHIP_CSGN_VCE_PHN_NRS + "','" + newOPLD.SHIP_CSGN_FAX_PHN_NRS + "','" + newOPLD.SHIP_DOMEST_INTL_FLAG + "','" + newOPLD.SHIP_SOURCE_FLAG + "','" + newOPLD.SHIP_IMP_ACC_NR
+ "','" + newOPLD.SHIP_IMP_FLG + "','" + newOPLD.SHIP_INV_CURR_CDE + "','" + newOPLD.SHIP_ACY_INS_CC + "','" + newOPLD.SHIP_ACY_INS_YY + "','" + newOPLD.SHIP_ACY_INS_MM
+ "','" + newOPLD.SHIP_ACY_INS_DD + "','" + newOPLD.SHIP_ACY_INS_HH + "','" + newOPLD.SHIP_ACY_INS_MIN + "','" + newOPLD.SHIP_QVIO_DATA_AREA + "','" + newOPLD.SHIP_SHR_OPT_IND
+ "','" + newOPLD.SHIP_USI + "','" + newOPLD.SHIP_TDPROD_TYP_CDE + "','" + newOPLD.AMS_TOKEN + "','" + newOPLD.FILLER + "','" + newOPLD.SHIP_REQ_ADDR_CLASS + "','" + newOPLD.SHIP_REQ_ADDR_ID
+ "','" + newOPLD.SHIP_IA_GCCNNumber + "','" + newOPLD.SHIP_CA01_PKUP_PHN_NR + "','" + newOPLD.CRA_PDP_Delivery_Token + "','" + newOPLD.CRA_PDP_Delivery_NGDW_Start + "','" + newOPLD.CRA_PDP_Delivery_NGDW_End
+ "','" + newOPLD.CRA_PDP_Delivery_NGDW_Confidence + "','" + newOPLD.CRA_PDP_Delivery_Date_Required + "','" + newOPLD.CRA_PDP_PickUp_Token + "','" + newOPLD.CRA_PDP_PickUp_NGDW_Start
+ "','" + newOPLD.CRA_PDP_PickUp_NGDW_End + "','" + newOPLD.CRA_PDP_PickUp_NGDW_Confidence + "','" + newOPLD.CRA_PDP_PickUp_Date_Required + "','" + newOPLD.PDP_Replay + "','" + newOPLD.PDP_Flags
+ "','" + newOPLD.SHIP_PSI_NR_PCS + "','" + newOPLD.SHIP_DSPLY_NAME + "','" + DateTime.Now.ToString() + "','" + newOPLD.ProcessingStatus + "')", conn);
rowsAffected = cmd.ExecuteNonQuery();
}
return(rowsAffected > 0 ? Tuple.Create <bool, int>(true, lastOPLD) : Tuple.Create <bool, int>(false, (GetLastOPLDID() + 1)));
}
