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