/// <summary>
/// This function will Encrypt the WebUsername and WebPassword with Hashkey password
/// </summary>
/// <param name="xmldoc">object - XCBL file which is uploaded to Service</param>
public void Meridian_EncrpytCredentials(ref XCBLService xmldoc)
{
XCBL_User objuser = xmldoc.XCBLUser;
xmldoc.XCBLUser = MeridianSystemLibrary.sysGetAuthenticationByUsernameAndPassword(xmldoc.XCBLUser);
if (xmldoc.XCBLUser != null)
{
xmldoc.XCBLUser.WebUsername = Encryption.Encrypt(xmldoc.XCBLUser.WebUsername, xmldoc.XCBLUser.Hashkey);
xmldoc.XCBLUser.WebPassword = Encryption.Encrypt(xmldoc.XCBLUser.WebPassword, xmldoc.XCBLUser.Hashkey);
}
else
{
xmldoc.XCBLUser = objuser;
}
return;
}
/// <summary>
///This function will retrieve the authentication information for a specified xCBL Web Service username and password found in MER000Authentication table if the user is enabled
/// </summary>
/// <param name="username">String - The username assigned to the xCBL web service credentials</param>
/// <param name="password">String - The password assigned to the xCBL web service credentials</param>
/// <returns>XCBL_User - XCBL_User class object that contains the authentication information for the record matching the username and password</returns>
public static XCBL_User sysGetAuthenticationByUsernameAndPassword(XCBL_User objXCBLUser)
{
// If either the username or password are empty then return null for the method
if (String.IsNullOrEmpty(objXCBLUser.WebUsername) || String.IsNullOrEmpty(objXCBLUser.WebPassword))
{
return(null);
}
// Try to retrieve the authentication record based on the specified username and password
try
{
System.Data.DataSet dsRecords = new System.Data.DataSet();
SqlCommand sqlQuery = new SqlCommand("SELECT [ID],[WebUsername],[WebPassword],[WebHashKey],[FtpUsername],[FtpPassword],[FtpServerUrl],[WebContactName],[WebContactCompany],[WebContactEmail],"
+ "[WebContactPhone1],[WebContactPhone2],[Enabled] FROM MER000Authentication WHERE [WebUsername] = @webUsername AND [WebPassword] = @WebPassword AND [Enabled] = 1", scDatabaseConnection);
sqlQuery.Parameters.Add("webUsername", SqlDbType.NVarChar).Value = objXCBLUser.WebUsername;
sqlQuery.Parameters.Add("webPassword", SqlDbType.NVarChar).Value = objXCBLUser.WebPassword;
// Fill the data adapter with the sql query results
using (System.Data.SqlClient.SqlDataAdapter sdaAdapter = new SqlDataAdapter(sqlQuery))
{
sdaAdapter.Fill(dsRecords);
}
// Parse the authentication record to a XCBL_User class object
XCBL_User user = new XCBL_User()
{
WebUsername = dsRecords.Tables[0].Rows[0].ItemArray[1].ToString(), WebPassword = dsRecords.Tables[0].Rows[0].ItemArray[2].ToString(), Hashkey = dsRecords.Tables[0].Rows[0].ItemArray[3].ToString(), FtpUsername = dsRecords.Tables[0].Rows[0].ItemArray[4].ToString(), FtpPassword = dsRecords.Tables[0].Rows[0].ItemArray[5].ToString(), FtpServerUrl = dsRecords.Tables[0].Rows[0].ItemArray[6].ToString(), WebContactName = dsRecords.Tables[0].Rows[0].ItemArray[7].ToString(), WebContactCompany = dsRecords.Tables[0].Rows[0].ItemArray[8].ToString(), WebContactEmail = dsRecords.Tables[0].Rows[0].ItemArray[9].ToString(), WebContactPhone1 = dsRecords.Tables[0].Rows[0].ItemArray[10].ToString(), WebContactPhone2 = dsRecords.Tables[0].Rows[0].ItemArray[11].ToString(), Enabled = Boolean.Parse(dsRecords.Tables[0].Rows[0].ItemArray[12].ToString())
};
return(user);
}
catch (Exception ex)
{
// If there was an error encountered in retrieving the authentication record then try to insert a record in MER010TransactionLog table to record the issue
try
{
sysInsertTransactionRecord(objXCBLUser.WebUsername, "", "sysGetAuthenticationByUsername", "0.0", "Error - Cannot retrieve record from MER000Authentication table", ex.InnerException.ToString(), "", "");
}
catch
{
}
return(null);
}
}
/// <summary>
/// This function will authenticate the User with Username and Password
/// </summary>
/// <param name="objXCBLUser">Object - holds the user related information</param>
/// <returns></returns>
private bool Meridian_AuthenticateUser(ref XCBL_User objXCBLUser)
{
try
{
int count = 0;
int index = OperationContext.Current.IncomingMessageHeaders.FindHeader("Username", "");
string username = "", password = "", hashkey = "";
if (index >= 0)
{
username = OperationContext.Current.IncomingMessageHeaders.GetHeader <string>("Username", "");
count++;
// XCBL_User username1 = Newtonsoft.Json.JsonConvert.DeserializeObject<XCBL_User>(value);
}
index = OperationContext.Current.IncomingMessageHeaders.FindHeader("Password", "");
if (index >= 0)
{
password = OperationContext.Current.IncomingMessageHeaders.GetHeader <string>("Password", "");
count++;
}
index = OperationContext.Current.IncomingMessageHeaders.FindHeader("Hashkey", "");
if (index >= 0)
{
hashkey = OperationContext.Current.IncomingMessageHeaders.GetHeader <string>("Hashkey", "");
count++;
}
if (count == 3)
{
objXCBLUser.WebUsername = Encryption.Decrypt(username, hashkey);
objXCBLUser.WebPassword = Encryption.Decrypt(password, hashkey);
objXCBLUser = MeridianSystemLibrary.sysGetAuthenticationByUsernameAndPassword(objXCBLUser);
return((objXCBLUser != null) ? true : false);
}
return(false);
}
catch
{
return(false);
}
}
/// <summary>
/// This function will return the Success / Failure of the Action performed which tranferring the CSV file.
/// </summary>
/// <param name="xmldoc">Object - XCBL file which is uploaded to Service</param>
/// <returns></returns>
public string Meridian_SendScheduleMessage(XCBLService xmldoc)
{
string status = MeridianGlobalConstants.MESSAGE_ACKNOWLEDGEMENT_SUCCESS;
string filePath = string.Empty;
string scheduleID = string.Empty;
xCblServiceUser = xmldoc.XCBLUser;
try
{
//// http headers Readers
//WebHeaderCollection headers = WebOperationContext.Current.IncomingRequest.Headers;
//UserInfo username = Newtonsoft.Json.JsonConvert.DeserializeObject<UserInfo>(headers["UserName"]);
//var password = headers["Password"];
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_SendScheduleMessage", "2.1", "SOAP Request Received", "Process Information", xmldoc.XCBL_FileName, scheduleID);
//Validate the User Credentials if the User is Authenticated or not
if (Meridian_AuthenticateUser(ref xCblServiceUser))
{
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_SendScheduleMessage", "2.2", "Meridian Authentication Success", "Process Information", xmldoc.XCBL_FileName, scheduleID);
//byte[] xdoc = xmldoc.XCBLDocument;
IncomingWebRequestContext context = WebOperationContext.Current.IncomingRequest;
//string XslFilename = System.Text.Encoding.Default.GetString(xdoc);
string XslFilename = xmldoc.XCBLDocument;
StringReader SR = new StringReader(XslFilename);
DataSet ds = new DataSet();
//Read the XCBL file passed from the Client Call
ds.ReadXml(SR);
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_SendScheduleMessage", "2.3", "Reading XCBL File - Success", "Process Information", xmldoc.XCBL_FileName, scheduleID);
StringBuilder csvoutput = new StringBuilder();
csvoutput.AppendLine(MeridianGlobalConstants.CSV_HEADER_NAMES);
scheduleID = ds.Tables[MeridianGlobalConstants.XCBL_SHIPPING_SCHEDULE_HEADER].Rows[0][MeridianGlobalConstants.XCBL_SCHEDULE_ID].ToString();
string Other_FirstStop = string.Empty, Other_Before7 = string.Empty, Other_Before9 = string.Empty, Other_Before12 = string.Empty, Other_SameDay = string.Empty, Other_OwnerOccupied = string.Empty;
string PurposeCoded = ds.Tables[MeridianGlobalConstants.XCBL_PURPOSE].Select(MeridianGlobalConstants.XCBL_SHIPPING_SCHEDULE_HEADER_ID + "=" + ds.Tables[MeridianGlobalConstants.XCBL_SHIPPING_SCHEDULE_HEADER].Rows[0][MeridianGlobalConstants.XCBL_SHIPPING_SCHEDULE_HEADER_ID].ToString()).CopyToDataTable().Rows[0][MeridianGlobalConstants.XCBL_PURPOSE_CODED].ToString();
string ScheduleTypeCoded = string.Empty;
string workOrderNumber = string.Empty;
string sSID = string.Empty;
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_SendScheduleMessage", "2.4", "Parsing SOAP Request", "Process Information", xmldoc.XCBL_FileName, scheduleID);
try
{
ScheduleTypeCoded = ds.Tables[MeridianGlobalConstants.XCBL_SHIPPING_SCHEDULE_HEADER].Select(MeridianGlobalConstants.XCBL_SHIPPING_SCHEDULE_HEADER_ID + "=" + ds.Tables[MeridianGlobalConstants.XCBL_SHIPPING_SCHEDULE_HEADER].Rows[0][MeridianGlobalConstants.XCBL_SHIPPING_SCHEDULE_HEADER_ID].ToString()).CopyToDataTable().Rows[0][MeridianGlobalConstants.XCBL_SCHEDULE_TYPE_CODED].ToString();
}
catch (Exception e)
{
//Handling the exception if ScheduleTypeCoded in xCBL file is blank.
ScheduleTypeCoded = "";
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_SendScheduleMessage", "1.0", "Warning - The Select Statement for the ScheduleTypeCoded is Empty", Convert.ToString(e.InnerException), xmldoc.XCBL_FileName, scheduleID);
}
string ContactNumber_1 = string.Empty, ContactNumber_2 = string.Empty;
DataTable dtTelphone = null;
// Reading all column values from xCBL file and writing into csv file.
for (int i = 0; i < ds.Tables[MeridianGlobalConstants.XCBL_PURCHASE_ORDER_REFERENCE].Rows.Count; i++)
{
dtTelphone = ds.Tables[MeridianGlobalConstants.XCBL_CONTACT_NUMBER].Select(MeridianGlobalConstants.XCBL_LIST_OF_CONTACT_NUMBER_ID + "=" + i).CopyToDataTable();
if (dtTelphone.Rows.Count > 0)
{
ContactNumber_1 = dtTelphone.Rows[0][MeridianGlobalConstants.XCBL_CONTACT_NUMBER_VALUE].ToString();
try
{
ContactNumber_2 = dtTelphone.Rows[1][MeridianGlobalConstants.XCBL_CONTACT_NUMBER_VALUE].ToString();
}
catch (Exception e)
{
//Handling the exception if ContactNumber_2 in xCBL file is blank.
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_SendScheduleMessage", "1.1", "Error - The Contact Number are incorrect", Convert.ToString(e.InnerException), xmldoc.XCBL_FileName, scheduleID);
}
}
dtTelphone = ds.Tables[MeridianGlobalConstants.XCBL_REFERENCE_CODED].Select(MeridianGlobalConstants.XCBL_OTHER_SCHEDULE_REFERENCES_ID + "=" + i).CopyToDataTable();
if (dtTelphone.Rows.Count > 0)
{
Other_FirstStop = dtTelphone.Rows[0][MeridianGlobalConstants.XCBL_REFERENCE_DESCRIPTION].ToString();
Other_Before7 = dtTelphone.Rows[1][MeridianGlobalConstants.XCBL_REFERENCE_DESCRIPTION].ToString();
Other_Before9 = dtTelphone.Rows[2][MeridianGlobalConstants.XCBL_REFERENCE_DESCRIPTION].ToString();
Other_Before12 = dtTelphone.Rows[3][MeridianGlobalConstants.XCBL_REFERENCE_DESCRIPTION].ToString();
Other_SameDay = dtTelphone.Rows[4][MeridianGlobalConstants.XCBL_REFERENCE_DESCRIPTION].ToString();
Other_OwnerOccupied = dtTelphone.Rows[5][MeridianGlobalConstants.XCBL_REFERENCE_DESCRIPTION].ToString();
workOrderNumber = dtTelphone.Rows[6][MeridianGlobalConstants.XCBL_REFERENCE_DESCRIPTION].ToString();
sSID = dtTelphone.Rows[7][MeridianGlobalConstants.XCBL_REFERENCE_DESCRIPTION].ToString();
}
// preparing string builder data which needs to be written to CSV file.
csvoutput.AppendLine(string.Format("{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}", ds.Tables[MeridianGlobalConstants.XCBL_SHIPPING_SCHEDULE_HEADER].Rows[0][MeridianGlobalConstants.XCBL_SCHEDULE_ID], ds.Tables[MeridianGlobalConstants.XCBL_SHIPPING_SCHEDULE_HEADER].Rows[0][MeridianGlobalConstants.XCBL_SCHEDULE_ISSUED_DATE]
, ds.Tables[MeridianGlobalConstants.XCBL_PURCHASE_ORDER_REFERENCE].Rows[i][MeridianGlobalConstants.XCBL_SELLER_ORDER_NUMBER], ds.Tables[MeridianGlobalConstants.XCBL_PURCHASE_ORDER_REFERENCE].Rows[i][MeridianGlobalConstants.XCBL_CHANGE_ORDER_SEQUENCE_NUMBER]
, Other_FirstStop, Other_Before7, Other_Before9, Other_Before12, Other_SameDay, Other_OwnerOccupied, "", "", "", "", PurposeCoded, ScheduleTypeCoded
, ds.Tables[MeridianGlobalConstants.XCBL_AGENCY].Rows[i][MeridianGlobalConstants.XCBL_AGENCY_CODED], ds.Tables[MeridianGlobalConstants.XCBL_NAME_ADDRESS].Rows[i][MeridianGlobalConstants.XCBL_NAME].ToString().Replace(",", ";"), ds.Tables[MeridianGlobalConstants.XCBL_NAME_ADDRESS].Rows[i][MeridianGlobalConstants.XCBL_STREET].ToString().Replace(',', ';'), ds.Tables[MeridianGlobalConstants.XCBL_NAME_ADDRESS].Rows[i][MeridianGlobalConstants.XCBL_STREET_SUPPLEMENT], ds.Tables[MeridianGlobalConstants.XCBL_NAME_ADDRESS].Rows[i][MeridianGlobalConstants.XCBL_POSTAL_CODE], ds.Tables[MeridianGlobalConstants.XCBL_NAME_ADDRESS].Rows[i][MeridianGlobalConstants.XCBL_CITY]
, ds.Tables[MeridianGlobalConstants.XCBL_REGION].Rows[i][MeridianGlobalConstants.XCBL_REGION_CODED], ds.Tables[MeridianGlobalConstants.XCBL_PRIMARY_CONTACT].Rows[i][MeridianGlobalConstants.XCBL_CONTACT_NAME].ToString().Replace(",", ";"), ContactNumber_1, ContactNumber_2, "", "", "", "", ds.Tables[MeridianGlobalConstants.XCBL_TRANSPORT_ROUTING].Rows[i][MeridianGlobalConstants.XCBL_SHIPPING_INSTRUCTIONS]
, ds.Tables[MeridianGlobalConstants.XCBL_GPS_COORDINATES].Rows[i][MeridianGlobalConstants.XCBL_GPS_SYSTEM], ds.Tables[MeridianGlobalConstants.XCBL_GPS_COORDINATES].Rows[i][MeridianGlobalConstants.XCBL_LATITUDE], ds.Tables[MeridianGlobalConstants.XCBL_GPS_COORDINATES].Rows[i][MeridianGlobalConstants.XCBL_LONGITUDE], ds.Tables[MeridianGlobalConstants.XCBL_LOCATION].Rows[i][MeridianGlobalConstants.XCBL_LOCATION_ID], ds.Tables[MeridianGlobalConstants.XCBL_END_TRANSPORT_LOCATION].Rows[i][MeridianGlobalConstants.XCBL_ESTIMATED_ARRIVAL_DATE]
, workOrderNumber
, sSID
));
}
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_SendScheduleMessage", "2.5", "SOAP Request Parsed Successfully", "Process Information", xmldoc.XCBL_FileName, scheduleID);
//Creating the CSV file on the Server Desktop - (Webservice hosted)
string pathDesktop = System.Configuration.ConfigurationManager.AppSettings["CsvPath"].ToString();
filePath = string.Format("{0}\\{1}{2}{3}", pathDesktop, MeridianGlobalConstants.XCBL_AWC_FILE_PREFIX, DateTime.Now.ToString(MeridianGlobalConstants.XCBL_FILE_DATETIME_FORMAT), MeridianGlobalConstants.XCBL_FILE_EXTENSION);
if (File.Exists(filePath))
{
//Delete file if already exists on server path.
File.Delete(filePath);
}
try
{
// Finally writing the xCBL Data to CSV file.
File.Create(filePath).Close();
File.AppendAllText(filePath, csvoutput.ToString());
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_SendScheduleMessage", "2.6", "Creating CSV File - Success", "Process Information", xmldoc.XCBL_FileName, scheduleID);
}
catch (Exception e)
{
//Handling the exception if CSV file is not valid.
status = MeridianGlobalConstants.MESSAGE_ACKNOWLEDGEMENT_FAILURE;
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_SendScheduleMessage", "1.2", "Error - The CSV file cannot be created or closed.", Convert.ToString(e.InnerException), xmldoc.XCBL_FileName, scheduleID);
return(GetMeridian_Status(status, scheduleID));
}
// This should be updated to use the XCBL_User xCblServiceUser variable that contains the FTP server url, FTP username, and FTP password
// Make sure the xCblServiceUser object is not null for the FTP credentials
try
{
Meridian_FTPUpload(MeridianGlobalConstants.FTP_SERVER_URL, xCblServiceUser.FtpUsername, xCblServiceUser.FtpPassword, filePath, xCblServiceUser, scheduleID);
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_SendScheduleMessage", "2.7", "Uploaded CSV File to FTP Site", "Process Information", xmldoc.XCBL_FileName, scheduleID);
}
catch (Exception e)
{
//Handling the exception if any FTP Error occurs while transferring the CSV file.
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_FTPUpload", "1.1", "Error - The FTP Error while transferring the CSV file.", Convert.ToString(e.InnerException), xmldoc.XCBL_FileName, scheduleID);
return(GetMeridian_Status(MeridianGlobalConstants.MESSAGE_ACKNOWLEDGEMENT_FAILURE, scheduleID));
}
status = MeridianGlobalConstants.MESSAGE_ACKNOWLEDGEMENT_SUCCESS;
return(GetMeridian_Status(status, scheduleID));
}
else
{
//Handling the exception if FTP Username/Password is invalid.
status = MeridianGlobalConstants.MESSAGE_ACKNOWLEDGEMENT_FAILURE;
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_AuthenticateUser", "1.1", "Error - The Incorrect Username / Password", "", xmldoc.XCBL_FileName, scheduleID);
return(GetMeridian_Status(status, scheduleID));
}
}
catch (Exception e)
{
//Handling the exception if the XML Document cannot be parsed.
status = MeridianGlobalConstants.MESSAGE_ACKNOWLEDGEMENT_FAILURE;
MeridianSystemLibrary.sysInsertTransactionRecord(xCblServiceUser.WebUsername, xCblServiceUser.FtpUsername, "Meridian_SendScheduleMessage", "1.1", "Error - The XML Document cannot be parsed", Convert.ToString(e.InnerException), xmldoc.XCBL_FileName, scheduleID);
return(GetMeridian_Status(status, scheduleID));
}
}
/// <summary>
/// This function will Upload CSV files to the FTP server
/// </summary>
/// <param name="ftpServer">string - Ftpserver URL</param>
/// <param name="userName">string - FTP username</param>
/// <param name="password">string - FTP Password</param>
/// <param name="filename">string - Xcbl Filename</param>
/// <param name="xCblServiceUser">Object - holds the user related information</param>
/// <param name="scheduleID">string - which is unique id of the XCBL file which is to be uploaded</param>
private static void Meridian_FTPUpload(string ftpServer, string userName, string password, string filename, XCBL_User xCblServiceUser, string scheduleID)
{
using (System.Net.WebClient client = new System.Net.WebClient())
{
client.Credentials = new System.Net.NetworkCredential(userName, password);
client.UploadFile(ftpServer + "/" + new FileInfo(filename).Name, "STOR", filename);
}
}
