Beispiel #1
0
        private bool InsertRecievedMailToInbox(string fromMail, string toMail, string subject, string messageBody, DateTime sentDate, string emailUid, bool isDeleted, int folderId, int folderOrder, string strippedText, out int emailId)
        {
            emailId = 0;
            bool success = false;
            try
            {
                string connectionString = null;
                connectionString = System.Configuration.ConfigurationSettings.AppSettings["DbConn"];
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("InsertMailToInbox", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@From_Email", SqlDbType.VarChar).Value = fromMail;
                        cmd.Parameters.Add("@To_Email", SqlDbType.VarChar).Value = toMail;
                        cmd.Parameters.Add("@Subject", SqlDbType.VarChar).Value = subject;
                        cmd.Parameters.Add("@Message_Body", SqlDbType.VarChar).Value = messageBody;
                        cmd.Parameters.Add("@Date_Sent", SqlDbType.DateTime).Value = sentDate;
                        cmd.Parameters.Add("@Email_Uid", SqlDbType.VarChar).Value = emailUid;
                        cmd.Parameters.Add("@IsDeleted", SqlDbType.Bit).Value = isDeleted;
                        cmd.Parameters.Add("@Folder_ID", SqlDbType.Int).Value = folderId;
                        cmd.Parameters.Add("@Folder_Order", SqlDbType.Int).Value = folderOrder;
                        cmd.Parameters.Add("@StrippedText", SqlDbType.NVarChar).Value = strippedText;
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        if (dt != null)
                        {
                            if (dt.Rows.Count > 0)
                            {
                                emailId = int.Parse(dt.Rows[0][0].ToString());
                            }
                        }
                        if (emailId != 0)
                        {
                            success = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                string strLog = ex.StackTrace.ToString() + "~~~~~~~~~~~~Error~~~~~~~~~~~:" + ex.Message.ToString();
                Email eml = new Email();
                eml.SendMail("Error processing before trying to insert to emailInbox email to EmailInbox Table.", strLog, "*****@*****.**", "", false);
                success = false;
            }

            return success;
        }
Beispiel #2
0
        private bool InsertOrderIntoDatabase(Mail mail, CompanyMail companyMail, Order order, List<OrderItem> orderItems, int emailId, string fileName, string htmlText, List<SalesContact> salesContacts, List<AdminContact> adminContacts,string propertyAddress)
        {
            Email eml = new Email();
            bool status = false;
            int mailId = 0, CompanyMailId = 0, orderId = 0;
            SqlConnection db = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["DbConn"]);

            try
            {
                db.Open();

                mailId = InsertMail(mail, db);

                order.MailId = mailId;

                InsertComapnyMail(companyMail, db, mailId);

                orderId = InsertOrder(order, db);

                List<int> xeroIds = InsertOrderItems(orderItems, db, orderId);

                bool eventCreated = false;

                foreach (var xeroId in xeroIds)
                {
                    if (xeroId != null)
                    {
                        if (!eventCreated)
                        {
                            var dsProductNotification = ProductSchedule(xeroId);
                            if (dsProductNotification.Tables[0].Rows.Count > 0)
                            {
                                string productGroupName = string.Empty;
                                string productGroupId = string.Empty;
                                try
                                {
                                    if (dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["ProductGroupId"] != null)
                                    {
                                        productGroupId = dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["ProductGroupId"].ToString();
                                    }
                                    if (dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["ProductGroupName"] != null)
                                    {
                                        productGroupName = dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["ProductGroupName"].ToString();
                                    }
                                }
                                catch
                                {
                                    productGroupId = string.Empty;
                                    productGroupName = string.Empty;
                                }

                                if (dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["Value"].ToString().Contains("@"))
                                {
                                    eml.SendMail(mail.Subject, htmlText, "*****@*****.**", "", false);
                                    eventCreated = true;

                                }
                                else
                                {
                                    GoogleService.IGoogleNotificationService client = new GoogleService.GoogleNotificationServiceClient();
                                    string orderItemDetails = string.Empty;
                                    if (!string.IsNullOrEmpty(productGroupId))
                                    {
                                        orderItemDetails = "Order Item (Please do not delete or update Order Item Id) :" + order.OrderId + ", " + productGroupId + "-" + productGroupName;
                                    }
                                    else
                                    {
                                        orderItemDetails = "Order Item (Please do not delete or update Order Item Id) :" + order.OrderId;
                                    }
                                    string salesContactDetails = salesContacts.FirstOrDefault().SalesContactName + " on " + salesContacts.FirstOrDefault().SalesContactNumber1 + " or " + salesContacts.FirstOrDefault().SalesContactNumber2;
                                    //foreach (SalesContact salesContact in salesContacts)
                                    //{
                                    //    if (string.IsNullOrEmpty(salesContactDetails))
                                    //    {
                                    //        salesContactDetails = salesContact.SalesContactName + " on " + salesContact.SalesContactNumber1 + " or " + salesContact.SalesContactNumber2;
                                    //    }
                                    //    else
                                    //    {
                                    //        salesContactDetails = salesContactDetails + Environment.NewLine + salesContact.SalesContactName + " on " + salesContact.SalesContactNumber1 + " or " + salesContact.SalesContactNumber2;
                                    //    }
                                    //}

                                    string orderItemDescription = string.Empty;
                                    foreach (var item in orderItems)
                                    {
                                        if (string.IsNullOrEmpty(orderItemDescription))
                                        {
                                            orderItemDescription = item.Name;
                                        }
                                        else
                                        {
                                            orderItemDescription = orderItemDescription + Environment.NewLine + Environment.NewLine + item.Name;
                                        }
                                    }

                                    string description = "SalesContact : " + salesContactDetails + " " + Environment.NewLine + Environment.NewLine
                                                       + "Required On :" + Convert.ToDateTime(order.RequiredDate).ToShortDateString() + Environment.NewLine + Environment.NewLine
                                                       + "Product Description :" + orderItemDescription + Environment.NewLine + Environment.NewLine
                                        //+ "Supplier Instructions :" + dtRecordtoProcess.Rows[i]["Comments"].ToString().Replace("Supplier Instructions", "") + Environment.NewLine
                                                       + orderItemDetails;
                                    DateTime startDate = Convert.ToDateTime(mail.SentDate).AddHours(10);
                                    int intervalMinutes = int.Parse(dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["Value"].ToString());
                                    client.CreateEvent(dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["Title"].ToString(), propertyAddress + ",Australia", salesContactDetails, startDate, startDate.AddMinutes(intervalMinutes), order.Description, order.Description, order.OrderId, order.RequiredDate.ToString(), "8", "*****@*****.**", description, string.Empty, false);
                                    eventCreated = true;

                                }
                            }
                            UpdateOrderEvent(orderId);//Order Row_Id
                        }
                    }
                }

                status = true;
            }
            catch (SqlException sqlError)
            {
                RollbackOrder(mailId, CompanyMailId, orderId);
                string strLog = sqlError.StackTrace.ToString() + "~~~~~~~~~~~~Error~~~~~~~~~~~:" + sqlError.Message.ToString();
                eml.SendMail("Error in parsing file.. FileName : " + fileName + ".htm", "EmailInbox table Row_Id = " + emailId + Environment.NewLine + "Error :- " + strLog, "*****@*****.**", "", false);
                status = false;
            }
            if (db.State == ConnectionState.Open)
                db.Close();
            return status;
        }
Beispiel #3
0
        //(string attachments)
        public int ProcessMessages(string sendar, string recipient, string subject, string htmlBody, string strippedHtml, string messageURL, string strippedText, string attachments, DateTime sentDate)
        {
            int returnCode = 0;
            try
            {
                string stringSentDate = sentDate.ToString("MM/dd/yyyy hh:mm:ss tt");

                string fileName = System.DateTime.Now.ToString("yyyyMMddHHmmssfffffff");// string.Format("{0}{1}{2}{3}{4}{5}{6}", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second, DateTime.Now.Millisecond);

                SaveRecievedMailToFileSystem(sendar, stringSentDate, subject, htmlBody, fileName);

                int emailId = 0;
                if (InsertRecievedMailToInbox(sendar, recipient, subject, htmlBody, sentDate, messageURL, false, 1, 1, strippedText, out emailId))
                {
                    returnCode = 200;

                    Dictionary<string, string> attributeValues;
                    List<OrderItem> orderItems;
                    bool isCancelled = false;
                    List<MultipleOrder> multipleOrdersList;
                    List<SalesContact> salesContacts;
                    List<AdminContact> adminContacts;
                    bool hasMultipleOrders = false;
                    ParseRecievedOrder(out attributeValues, out isCancelled, strippedText, out orderItems, out hasMultipleOrders, out multipleOrdersList, out salesContacts, out adminContacts);

                    if (!isCancelled)
                    {
                        if (hasMultipleOrders)
                        {
                            //process multiple orders
                            foreach (var ord in multipleOrdersList)
                            {
                                if (attributeValues.ContainsKey("OrderId"))
                                {
                                    attributeValues["OrderId"] = ord.OrderId;
                                }
                                else
                                {
                                    attributeValues.Add("OrderId", ord.OrderId);
                                }
                                attributeValues.Add("ProductDescription", ord.ProductDescription);
                                attributeValues.Add("RequiredDate", ord.RequiredDate);
                                ProcessOrder(attributeValues, stringSentDate, ord.OrderItems, subject, emailId, fileName, htmlBody, salesContacts, adminContacts);
                                attributeValues.Remove("OrderId");
                                attributeValues.Remove("ProductDescription");
                                attributeValues.Remove("RequiredDate");
                            }
                        }
                        else
                        {
                            ProcessOrder(attributeValues, stringSentDate, orderItems, subject, emailId, fileName, htmlBody, salesContacts, adminContacts);
                        }
                    }
                    else // process cancelled order
                    {
                        List<OrderItem> ordItems = new List<OrderItem>();
                        InsertCancelledOrders(attributeValues["ProductDescription"], attributeValues["PropertyAddress"], attributeValues["RequiredDate"], fileName);
                    }
                    MoveRecievedFile(fileName, false, isCancelled);
                }
                else
                {
                    //Email eml = new Email();
                    //eml.SendMail("Error inserting email to EmailInbox Table.", "FileName " + fileName + ".htm., Email Url = " + messageURL, "*****@*****.**", "", false);
                    MoveRecievedFile(fileName, true, false);
                    returnCode = 460;
                }
            }
            catch (Exception ex)
            {
                string strLog = ex.StackTrace.ToString() + "~~~~~~~~~~~~Error~~~~~~~~~~~:" + ex.Message.ToString();
                Email eml = new Email();
                eml.SendMail("Error processing before trying to insert to emailInbox email to EmailInbox Table.", " Email Url = " + messageURL + Environment.NewLine + strLog, "*****@*****.**", "", false);
            }
            return returnCode;
        }
Beispiel #4
0
        private int ProcessOrder(string subject, string htmlBody, string strippedText, int returnCode, DateTime stringSentDate,
            int emailId, string fileName)
        {
            returnCode = 200;

            Dictionary<string, string> attributeValues;
            List<OrderItem> orderItems;
            bool isCancelled = false;
            List<MultipleOrder> multipleOrdersList;
            List<SalesContact> salesContacts;
            List<AdminContact> adminContacts;
            bool hasMultipleOrders = false;

            ParseRecievedOrder(out attributeValues, out isCancelled, strippedText, out orderItems, out hasMultipleOrders,
                out multipleOrdersList, out salesContacts, out adminContacts);
            invoice = null;

            if (!isCancelled)
            {

                if (hasMultipleOrders)
                {
                    //process multiple orders

                    foreach (var ord in multipleOrdersList)
                    {
                        if (!exceptionOccured)
                        {
                            if (attributeValues.ContainsKey("OrderId"))
                            {
                                attributeValues["OrderId"] = ord.OrderId;
                            }
                            else
                            {
                                attributeValues.Add("OrderId", ord.OrderId);
                            }
                            attributeValues.Add("ProductDescription", ord.ProductDescription);
                            attributeValues.Add("RequiredDate", ord.RequiredDate);
                            ProcessOrder(attributeValues, stringSentDate, ord.OrderItems, subject, emailId, fileName,
                                htmlBody,
                                salesContacts, adminContacts);
                            attributeValues.Remove("OrderId");
                            attributeValues.Remove("ProductDescription");
                            attributeValues.Remove("RequiredDate");
                        }
                    }

                    if (!exceptionOccured)
                    {
                        if (createInvoice)
                        {
                            try
                            {
                                IOAuthSession session = new XeroApi.OAuth.XeroApiPrivateSession(
                                    "CT-DPI", // Photography",
                                    "N2KFABNCYXNHCGNU7GDVRL3AOUYMS2", //"ZFRY6DJBFEMVYJYBFKJFYDVDJZUXYV",
                                    new X509Certificate2(@"D:\CampaignTrack\Certificate\public_privatekey.pfx",
                                        "zerofootprint"));
                                Repository repository = new Repository(session);
                                var inv = repository.Create<XeroApi.Model.Invoice>(invoice);
                                if (invoice.ValidationStatus == ValidationStatus.ERROR)
                                {
                                    foreach (var message in invoice.ValidationErrors)
                                    {
                                    }
                                }
                                try
                                {
                                    if (inv.InvoiceNumber != null)
                                    {
                                        Guid invoiceId = inv.InvoiceID;
                                        string invoiceNo = inv.InvoiceNumber;
                                        // System.Threading.Thread.Sleep(5000);
                                        string jpegFileName =
                                            System.Configuration.ConfigurationSettings.AppSettings["MailPath"] +
                                            fileName +
                                            ".jpg";
                                        //string AnyAttachmentFilename = fileName;
                                        var SalesInvoice =
                                            repository.Invoices.FirstOrDefault(it => it.InvoiceID == invoiceId);
                                        if (File.Exists(jpegFileName))
                                        {
                                            var newAttachment = repository.Attachments.Create(SalesInvoice,
                                                new FileInfo(jpegFileName));
                                        }
                                        InsertPropertyInvoice(propertyId, invoiceNo);
                                    }

                                }
                                catch
                                {

                                }
                            }
                            catch (Exception sqlError)
                            {
                                Email eml = new Email();
                                string supportEmail =
                                    System.Configuration.ConfigurationSettings.AppSettings["supportEmail"];
                                string supportCCEmail =
                                    System.Configuration.ConfigurationSettings.AppSettings["supportCCEmail"];
                                string strLog = sqlError.StackTrace.ToString() + "~~~~~~~~~~~~Error~~~~~~~~~~~:" +
                                                sqlError.Message.ToString();
                                UpdateFailedOrder(emailId);
                                eml.SendMail("Error while creating invoice at Xero : " + fileName + ".htm",
                                    "EmailInbox table Row_Id = " + emailId + Environment.NewLine + "Sentdate : " +
                                    stringSentDate + Environment.NewLine + "Error :- " + strLog, supportEmail,
                                    supportCCEmail, false);

                            }
                        }
                    }
                }
                else
                {
                    ProcessOrder(attributeValues, stringSentDate, orderItems, subject, emailId, fileName, htmlBody,
                        salesContacts, adminContacts);
                    if (!exceptionOccured)
                    {
                        if (createInvoice)
                        {
                            try
                            {
                                IOAuthSession session = new XeroApi.OAuth.XeroApiPrivateSession(
                                    "CT-DPI",
                                    "N2KFABNCYXNHCGNU7GDVRL3AOUYMS2",
                                    new X509Certificate2(@"D:\CampaignTrack\Certificate\public_privatekey.pfx",
                                        "zerofootprint"));
                                Repository repository = new Repository(session);
                                var inv = repository.Create<XeroApi.Model.Invoice>(invoice);
                                if (invoice.ValidationStatus == ValidationStatus.ERROR)
                                {
                                    foreach (var message in invoice.ValidationErrors)
                                    {
                                    }
                                }
                                try
                                {
                                    if (inv.InvoiceNumber != null)
                                    {
                                        Guid invoiceId = inv.InvoiceID;
                                        string invoiceNo = inv.InvoiceNumber;
                                        // System.Threading.Thread.Sleep(5000);
                                        string jpegFileName =
                                            System.Configuration.ConfigurationSettings.AppSettings["MailPath"] +
                                            fileName +
                                            ".jpg";
                                        var SalesInvoice =
                                            repository.Invoices.FirstOrDefault(it => it.InvoiceID == invoiceId);
                                        if (File.Exists(jpegFileName))
                                        {
                                            var newAttachment = repository.Attachments.Create(SalesInvoice,
                                                new FileInfo(jpegFileName));
                                        }
                                        InsertPropertyInvoice(propertyId, invoiceNo);
                                    }
                                }
                                catch (Exception sqlError)
                                {

                                }

                            }
                            catch (Exception sqlError)
                            {
                                Email eml = new Email();
                                string supportEmail =
                                    System.Configuration.ConfigurationSettings.AppSettings["supportEmail"];
                                string supportCCEmail =
                                    System.Configuration.ConfigurationSettings.AppSettings["supportCCEmail"];
                                string strLog = sqlError.StackTrace.ToString() + "~~~~~~~~~~~~Error~~~~~~~~~~~:" +
                                                sqlError.Message.ToString();
                                UpdateFailedOrder(emailId);
                                eml.SendMail("Error while creating invoice at Xero : " + fileName + ".htm",
                                    "EmailInbox table Row_Id = " + emailId + Environment.NewLine + "Sentdate : " +
                                    stringSentDate + Environment.NewLine + "Error :- " + strLog, supportEmail,
                                    supportCCEmail, false);

                            }
                        }
                    }
                }
            }
            else // process cancelled order
            {
                List<OrderItem> ordItems = new List<OrderItem>();
                InsertCancelledOrders(attributeValues["ProductDescription"], attributeValues["PropertyAddress"],
                    attributeValues["RequiredDate"], fileName);
            }
            MoveRecievedFile(fileName, false, isCancelled);
            return returnCode;
        }
Beispiel #5
0
        //(string attachments)
        public int ProcessMessages(string sendar, string recipient, string subject, string htmlBody, string strippedHtml, string messageURL, string strippedText, string attachments, DateTime sentDate)
        {
            int returnCode = 0;
            try
            {
                sentDate = sentDate.ToLocalTime();
                string stringSentDate = sentDate.ToString("MM/dd/yyyy hh:mm:ss tt");

                string fileName = System.DateTime.Now.ToString("yyyyMMddHHmmssfffffff");// string.Format("{0}{1}{2}{3}{4}{5}{6}", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second, DateTime.Now.Millisecond);

                // check duplicate emailUid before process the order

                if (!CheckDuplicateMailToInbox(messageURL))
                {
                    int emailId = 0;
                    if (InsertRecievedMailToInbox(sendar, recipient, subject, htmlBody, sentDate, messageURL, false, 1, 1, strippedText, out emailId))
                    {
                        fileName = System.DateTime.Now.ToString("yyyyMMddHHmmssfffffff");
                        SaveRecievedMailToFileSystem(sendar, stringSentDate, subject, htmlBody, fileName);
                        returnCode = ProcessOrder(subject, htmlBody, strippedText, returnCode, sentDate, emailId, fileName);
                    }
                    else
                    {
                        //Email eml = new Email();
                        //eml.SendMail("Error inserting email to EmailInbox Table.", "FileName " + fileName + ".htm., Email Url = " + messageURL, "*****@*****.**", "", false);
                        MoveRecievedFile(fileName, true, false);
                        returnCode = 460;
                    }
                }
            }
            catch (Exception ex)
            {
                string strLog = ex.StackTrace.ToString() + "~~~~~~~~~~~~Error~~~~~~~~~~~:" + ex.Message.ToString();
                string supportEmail = System.Configuration.ConfigurationSettings.AppSettings["supportEmail"];
                string supportCCEmail = System.Configuration.ConfigurationSettings.AppSettings["supportCCEmail"];
                Email eml = new Email();
                eml.SendMail("Error processing before trying to insert to emailInbox email to EmailInbox Table.", " Email Url = " + messageURL + Environment.NewLine + strLog, supportEmail, supportCCEmail, false);
            }
            return returnCode;
        }
Beispiel #6
0
        private bool InsertOrderIntoDatabase(Mail mail, CompanyMail companyMail, Order order, List<OrderItem> orderItems, int emailId, string fileName, string htmlText, List<SalesContact> salesContacts, List<AdminContact> adminContacts, string propertyAddress, DateTime sentDate)
        {
            string year = "", month = "", day = "";
            Email eml = new Email();
            bool status = false;
            int mailId = 0, CompanyMailId = 0, orderId = 0;
            int companyId = 0, userId = 0, calendarId = 0;
            string XeroCompanyName = string.Empty;
            int adminId = 0;
            int salesId = 0;
            SqlConnection db = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["DbConn"]);

            try
            {
                db.Open();

                mailId = InsertMail(mail, db);

                order.MailId = mailId;

                InsertComapnyMail(companyMail, db, mailId, order.PropertyId, propertyAddress);

                // insert sales contact
                foreach (var salesContact in salesContacts)
                {
                    InsertContact(companyMail.ComanyName, salesContact.SalesContactName, salesContact.SalesContactNumber1, "Sales", out salesId);
                }

                // insert admin contact
                foreach (var adminContact in adminContacts)
                {

                    InsertContact(companyMail.ComanyName, adminContact.Name, adminContact.Email, "Admin", out adminId);
                }

                orderId = InsertOrder(order, db);
                Dictionary<int, XeroProductInfo> xeroCodeCostDictionary = new Dictionary<int, XeroProductInfo>();
                List<int> xeroIds = InsertOrderItems(orderItems, db, orderId, out xeroCodeCostDictionary);

                GetCompanyIdPropertyIdUserIdCalendarId(companyMail.ComanyName, order.PropertyId, "*****@*****.**", out companyId, out propertyId, out userId, out calendarId, out createInvoice, out XeroCompanyName);//[email protected]

                if (propertyId != 0 && !string.IsNullOrEmpty(propertyAddress.Trim()))
                {
                    ProprtyCoordinates(propertyId, propertyAddress.Trim());
                }

                string companyCode = GetCompanyCode(companyMail.ComanyName);
                if (string.IsNullOrEmpty(companyCode))
                {
                    RollbackOrder(mailId, CompanyMailId, orderId);
                    string supportEmail = System.Configuration.ConfigurationSettings.AppSettings["supportEmail"];
                    string supportCCEmail = System.Configuration.ConfigurationSettings.AppSettings["supportCCEmail"];
                    string strLog = "~~~~~~~~~~~~No Company Match Found : Company Name : " + companyMail.ComanyName + "~~~~~~~~~~~:";
                    UpdateFailedOrder(emailId);
                    MoveRecievedFile(fileName, true, false);
                    eml.SendMail("No Company Match Found FileName : " + fileName + ".htm", "EmailInbox table Row_Id = " + emailId + Environment.NewLine + "Sentdate : " + sentDate + Environment.NewLine + "Error :- " + strLog, supportEmail, supportCCEmail, false);
                    status = false;
                    exceptionOccured = true;
                    return false;
                }

                bool eventCreated = false;

                if (!isCompanyMail && xeroIds.Count == 0)
                {
                    RollbackOrder(mailId, CompanyMailId, orderId);
                    string supportEmail = System.Configuration.ConfigurationSettings.AppSettings["supportEmail"];
                    string supportCCEmail = System.Configuration.ConfigurationSettings.AppSettings["supportCCEmail"];
                    string strLog = "~~~~~~~~~~~~No Product Match Found : Company Name : " + companyMail.ComanyName + "~~~~~~~~~~~:";
                    UpdateFailedOrder(emailId);
                    MoveRecievedFile(fileName, true, false);
                    eml.SendMail("No Product Match Found FileName : " + fileName + ".htm", "EmailInbox table Row_Id = " + emailId + Environment.NewLine + "Sentdate : " + sentDate + Environment.NewLine + "Error :- " + strLog, supportEmail, supportCCEmail, false);
                    status = false;
                    exceptionOccured = true;
                    return false;
                }
                if (!isCompanyMail)
                {
                    foreach (var xeroId in xeroIds)
                    {
                        if (xeroId != null)
                        {
                            //if (!eventCreated)
                            //{
                            var dsProductNotification = ProductSchedule(xeroId);
                            if (dsProductNotification.Tables[0].Rows.Count > 0)
                            {
                                foreach (DataRow row in dsProductNotification.Tables[0].Rows)
                                {
                                    string productGroupName = string.Empty;
                                    string productGroupId = string.Empty;
                                    try
                                    {
                                        if (row["ProductGroupId"] != null)
                                        {
                                            productGroupId = row["ProductGroupId"].ToString();
                                        }
                                        if (row["ProductGroupName"] != null)
                                        {
                                            productGroupName = row["ProductGroupName"].ToString();
                                        }
                                    }
                                    catch
                                    {
                                        productGroupId = string.Empty;
                                        productGroupName = string.Empty;
                                    }

                                    if (row["Value"].ToString().Contains("@"))
                                    {
                                        string dpiEmail = System.Configuration.ConfigurationSettings.AppSettings["dpiEmail"];
                                        string dpiCCEmail = System.Configuration.ConfigurationSettings.AppSettings["dpiCCEmail"];
                                        // need to comment below code while you debug this form local machine Manoj
                                        eml.SendMail(mail.Subject, htmlText, dpiEmail, dpiCCEmail, false);
                                        eventCreated = true;

                                    }
                                    else
                                    {
                                        GoogleService.IGoogleNotificationService client = new GoogleService.GoogleNotificationServiceClient();
                                        string orderItemDetails = string.Empty;
                                        if (!string.IsNullOrEmpty(productGroupId))
                                        {
                                            if (!string.IsNullOrEmpty(productGroupName))
                                            {
                                                orderItemDetails =
                                                    "Order Item (Please do not delete or update Order Item Id) :" +
                                                    order.OrderId + ", " + productGroupId + "-" + productGroupName;
                                            }
                                            else
                                            {
                                                orderItemDetails =
                                                    "Order Item (Please do not delete or update Order Item Id) :" +
                                                    order.OrderId + ", " + productGroupId;
                                            }
                                        }
                                        else
                                        {
                                            orderItemDetails = "Order Item (Please do not delete or update Order Item Id) :" + order.OrderId;
                                        }
                                        string salesContactDetails = salesContacts.FirstOrDefault().SalesContactName + " on " + salesContacts.FirstOrDefault().SalesContactNumber1 + " or " + salesContacts.FirstOrDefault().SalesContactNumber2;

                                        string orderItemDescription = string.Empty;
                                        foreach (var item in orderItems)
                                        {
                                            if (string.IsNullOrEmpty(orderItemDescription))
                                            {
                                                orderItemDescription = item.Name;
                                            }
                                            else
                                            {
                                                orderItemDescription = orderItemDescription + Environment.NewLine + Environment.NewLine + item.Name;
                                            }
                                        }

                                        string description = "SalesContact : " + salesContactDetails + " " + Environment.NewLine + Environment.NewLine
                                                             + "Required On :" + Convert.ToDateTime(order.RequiredDate).ToShortDateString() + Environment.NewLine + Environment.NewLine
                                                             + "Product Description :" + orderItemDescription + Environment.NewLine + Environment.NewLine
                                            //+ "Supplier Instructions :" + dtRecordtoProcess.Rows[i]["Comments"].ToString().Replace("Supplier Instructions", "") + Environment.NewLine
                                                             + orderItemDetails;

                                        string title = companyCode + ":" + propertyAddress + ":" + row["Title"].ToString() ?? order.Description;
                                        string colorId = row["ColorId"].ToString();

                                        //GetStartDate(mail.SentDate, out year, out month, out day);
                                        var startDate = sentDate;// GetStartDate(mail.SentDate);

                                        //DateTime startDate = Convert.ToDateTime(mail.SentDate);

                                        if (startDate.TimeOfDay.Minutes < 15 && startDate.TimeOfDay.Minutes > 0)
                                        {
                                            startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day, startDate.Hour, 00, 00);
                                        }
                                        else if (startDate.TimeOfDay.Minutes < 30 && startDate.TimeOfDay.Minutes > 15)
                                        {
                                            startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day, startDate.Hour, 30, 00);
                                        }

                                        else if (startDate.TimeOfDay.Minutes < 45 && startDate.TimeOfDay.Minutes > 30)
                                        {
                                            startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day, startDate.Hour, 30, 00);
                                        }
                                        else if (startDate.TimeOfDay.Minutes < 45)
                                        {
                                            startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day, startDate.Hour + 1, 00, 00);
                                        }
                                        int intervalMinutes = int.Parse(row["Value"].ToString());

                                        // need to change [email protected] into [email protected] below code while you debug this form local machine
                                        var returnEventId = client.CreateEvent(title, propertyAddress + ",Australia", salesContactDetails, startDate, startDate.AddMinutes(intervalMinutes), order.Description, order.Description, order.OrderId, order.RequiredDate.ToString(), colorId, "*****@*****.**", description, string.Empty, false);
                                        // var returnEventId = client.CreateEvent(title, propertyAddress + ",Australia", salesContactDetails, startDate, startDate.AddMinutes(intervalMinutes), order.Description, order.Description, order.OrderId, order.RequiredDate.ToString(), colorId, "*****@*****.**", description, string.Empty, false);
                                        if (!string.IsNullOrEmpty(returnEventId))
                                        {
                                            eventCreated = true;
                                            int eventId = GetEventId(returnEventId.Split(',')[0]);
                                            int jobId = CreateJob(825, orderId, userId, propertyId, order.RequiredDate, mailId, companyId, "Not Cancelled", "", adminId);
                                            CreateEvent(825, jobId, userId, calendarId, eventId, int.Parse(productGroupId), "Not Cancelled", "");
                                        }
                                        else
                                        {
                                            throw new Exception("Google service has problem");
                                        }
                                    }
                                    UpdateOrderEvent(orderId);//Order Row_Id
                                }
                                //string productGroupName = string.Empty;
                                //string productGroupId = string.Empty;
                                //try
                                //{
                                //    if (dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["ProductGroupId"] != null)
                                //    {
                                //        productGroupId = dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["ProductGroupId"].ToString();
                                //    }
                                //    if (dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["ProductGroupName"] != null)
                                //    {
                                //        productGroupName = dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["ProductGroupName"].ToString();
                                //    }
                                //}
                                //catch
                                //{
                                //    productGroupId = string.Empty;
                                //    productGroupName = string.Empty;
                                //}

                                //if (dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["Value"].ToString().Contains("@"))
                                //{
                                //    string dpiEmail = System.Configuration.ConfigurationSettings.AppSettings["dpiEmail"];
                                //    string dpiCCEmail = System.Configuration.ConfigurationSettings.AppSettings["dpiCCEmail"];
                                //    //eml.SendMail(mail.Subject, htmlText, dpiEmail, dpiCCEmail, false);
                                //    eventCreated = true;

                                //}
                                //else
                                //{
                                //    GoogleService.IGoogleNotificationService client = new GoogleService.GoogleNotificationServiceClient();
                                //    string orderItemDetails = string.Empty;
                                //    if (!string.IsNullOrEmpty(productGroupId))
                                //    {
                                //        orderItemDetails = "Order Item (Please do not delete or update Order Item Id) :" + order.OrderId + ", " + productGroupId + "-" + productGroupName;
                                //    }
                                //    else
                                //    {
                                //        orderItemDetails = "Order Item (Please do not delete or update Order Item Id) :" + order.OrderId;
                                //    }
                                //    string salesContactDetails = salesContacts.FirstOrDefault().SalesContactName + " on " + salesContacts.FirstOrDefault().SalesContactNumber1 + " or " + salesContacts.FirstOrDefault().SalesContactNumber2;

                                //    string orderItemDescription = string.Empty;
                                //    foreach (var item in orderItems)
                                //    {
                                //        if (string.IsNullOrEmpty(orderItemDescription))
                                //        {
                                //            orderItemDescription = item.Name;
                                //        }
                                //        else
                                //        {
                                //            orderItemDescription = orderItemDescription + Environment.NewLine + Environment.NewLine + item.Name;
                                //        }
                                //    }

                                //    string description = "SalesContact : " + salesContactDetails + " " + Environment.NewLine + Environment.NewLine
                                //                       + "Required On :" + Convert.ToDateTime(order.RequiredDate).ToShortDateString() + Environment.NewLine + Environment.NewLine
                                //                       + "Product Description :" + orderItemDescription + Environment.NewLine + Environment.NewLine
                                //        //+ "Supplier Instructions :" + dtRecordtoProcess.Rows[i]["Comments"].ToString().Replace("Supplier Instructions", "") + Environment.NewLine
                                //                       + orderItemDetails;
                                //    string companyCode = GetCompanyCode(companyMail.ComanyName);

                                //    string title = companyCode + ":" + propertyAddress + ":" + dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["Title"].ToString() ?? order.Description;
                                //    string colorId = dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["ColorId"].ToString();

                                //    //GetStartDate(mail.SentDate, out year, out month, out day);
                                //    var startDate = sentDate;// GetStartDate(mail.SentDate);

                                //    //DateTime startDate = Convert.ToDateTime(mail.SentDate);

                                //    if (startDate.TimeOfDay.Minutes < 15 && startDate.TimeOfDay.Minutes > 0)
                                //    {
                                //        startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day, startDate.Hour, 00, 00);
                                //    }
                                //    else if (startDate.TimeOfDay.Minutes < 30 && startDate.TimeOfDay.Minutes > 15)
                                //    {
                                //        startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day, startDate.Hour, 30, 00);
                                //    }

                                //    else if (startDate.TimeOfDay.Minutes < 45 && startDate.TimeOfDay.Minutes > 30)
                                //    {
                                //        startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day, startDate.Hour, 30, 00);
                                //    }
                                //    else if (startDate.TimeOfDay.Minutes < 45)
                                //    {
                                //        startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day, startDate.Hour + 1, 00, 00);
                                //    }
                                //    int intervalMinutes = int.Parse(dsProductNotification.Tables[0].Rows[dsProductNotification.Tables[0].Rows.Count - 1]["Value"].ToString());
                                //    //var returnEventId = client.CreateEvent(title, propertyAddress + ",Australia", salesContactDetails, startDate, startDate.AddMinutes(intervalMinutes), order.Description, order.Description, order.OrderId, order.RequiredDate.ToString(), colorId, "*****@*****.**", description, string.Empty, false);
                                //    //if (!string.IsNullOrEmpty(returnEventId))
                                //    //{
                                //    //    eventCreated = true;
                                //    //    int eventId = GetEventId(returnEventId.Split(',')[0]);
                                //    //    int jobId = CreateJob(825, orderId, userId, propertyId, order.RequiredDate, mailId, companyId, "Not Cancelled", "");
                                //    //    CreateEvent(825, jobId, userId, calendarId, eventId, int.Parse(productGroupId), "Not Cancelled", "");
                                //    //}
                                //    //else
                                //    //{
                                //    //    throw new Exception("Google service has problem");
                                //    //}
                                //}
                                //UpdateOrderEvent(orderId);//Order Row_Id
                            }

                            //}
                        }
                    }
                }
                AddItemsToXeroInvoice(propertyAddress, xeroIds, XeroCompanyName, xeroCodeCostDictionary);
                status = true;
            }
            catch (Exception sqlError)
            {
                RollbackOrder(mailId, CompanyMailId, orderId);
                string supportEmail = System.Configuration.ConfigurationSettings.AppSettings["supportEmail"];
                string supportCCEmail = System.Configuration.ConfigurationSettings.AppSettings["supportCCEmail"];
                string strLog = sqlError.StackTrace.ToString() + "~~~~~~~~~~~~Error~~~~~~~~~~~:" + sqlError.Message.ToString();
                UpdateFailedOrder(emailId);
                MoveRecievedFile(fileName, true, false);
                eml.SendMail("Error in parsing file.. FileName : " + fileName + ".htm", "EmailInbox table Row_Id = " + emailId + Environment.NewLine + "Sentdate : " + sentDate + Environment.NewLine + "Error :- " + strLog, supportEmail, supportCCEmail, false);
                status = false;
                exceptionOccured = true;
            }
            if (db.State == ConnectionState.Open)
                db.Close();
            return status;
        }