/// <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);
     }
 }