/// <summary>
/// Get the next available number from the NextNumber table and prefix with the prefix defined on the same table.
/// </summary>
/// <param name="NextNumberType"></param>
/// <returns></returns>
public static string GetNextID(string NextNumberType, Int16 NumberLength)
{
string wkIDPrefix = "";
using (SqlText SelectID = new SqlText(
"Select IDPrefix " +
"from [ALL_NextNumber] " +
"Where NextNumberType = @NextNumberType; ", CrmHelper.crmConnectionStringName))
{
SelectID.AddParameter("@NextNumberType", NextNumberType);
wkIDPrefix = SelectID.ExecuteScalar().ToString();
}
return(GetNextID(wkIDPrefix, NextNumberType, NumberLength));
}
/// <summary>
/// Get the next available number from the NextNumber table.
/// </summary>
/// <param name="NextNumberType"></param>
/// <returns></returns>
public static Int64 GetNextNumber(string NextNumberType)
{
Int64 NextNumber = -1;
using (SqlText UpdateNext = new SqlText(
"Update [ALL_NextNumber] " +
"Set NextNumber = NextNumber + 1 " +
"Where NextNumberType = @NextNumberType; " +
"Select NextNumber from [ALL_NextNumber] Where NextNumberType = @NextNumberType;", CrmHelper.crmConnectionStringName))
{
UpdateNext.AddParameter("@NextNumberType", NextNumberType);
NextNumber = Convert.ToInt64(UpdateNext.ExecuteScalar());
}
return(NextNumber);
}
public static Boolean SaveEnquiry(Order order, string comment, List <FileAttachment> files, Store store)
{
Int32 jobId;
string wkRef = NextNumber.GetNextID("QuoteRef", 7);
DateTime created = GetCurrentStandardTime();
Boolean filesAttached = false;
Int32? companyId = null;
Int32? clientId = null;
string wkShortDesc = "";
if (order.Items != null && order.Items.Count > 0)
{
int wkCount = 0;
foreach (OrderItem it in order.Items)
{
wkCount++;
if (!string.IsNullOrEmpty(wkShortDesc))
{
wkShortDesc = wkShortDesc + ", ";
}
wkShortDesc = wkShortDesc + it.Product.Name;
if (wkCount > 2)
{
if (order.Items.Count > wkCount)
{
wkShortDesc = wkShortDesc + ", ...";
}
break;
}
}
}
else
{
wkShortDesc = "Website Enquiry";
}
GetCompanyAndClient(order, out companyId, out clientId);
if (files != null && files != null && files.Count > 0)
{
filesAttached = true;
}
// Insert the Job record.
using (Redi.Utility.SqlText insert = new Redi.Utility.SqlText(
"Insert Into ALL_Job (ReferenceNo, ClientId, CompanyId, CreatedOn, CreatedByName, CreatedFromWebsite, NewEnquiry, ShortDescription, QuoteAvailable) " +
"Values (@ReferenceNo, @ClientId, @CompanyId, @CreatedOn, @CreatedByName, @CreatedFromWebsite, @NewEnquiry, @ShortDescription, 0) " +
"SELECT SCOPE_IDENTITY(); ", crmConnectionStringName))
{
insert.AddParameter("@ReferenceNo", wkRef);
insert.AddParameter("@ClientId", clientId);
if (companyId != null)
{
insert.AddParameter("@CompanyId", companyId);
}
else
{
insert.AddParameter("@CompanyId", System.Data.SqlTypes.SqlInt32.Null);
}
insert.AddParameter("@CreatedOn", created);
insert.AddParameter("@CreatedByName", createdBy);
insert.AddParameter("@CreatedFromWebsite", true);
insert.AddParameter("@NewEnquiry", true);
insert.AddParameter("@ShortDescription", wkShortDesc);
jobId = Convert.ToInt32(insert.ExecuteScalar());
}
// Insert the Enquiry record.
using (Redi.Utility.SqlText insert = new Redi.Utility.SqlText(
"Insert Into ALL_Enquiry (JobId, Comment, CreatedOn, CreatedByName, ShoppingCartOrderId, ShoppingCartUserId, FilesAttached ) " +
"Values (@JobId, @Comment, @CreatedOn, @CreatedByName, @ShoppingCartOrderId, @ShoppingCartUserId, @FilesAttached); ", crmConnectionStringName))
{
insert.AddParameter("@JobId", jobId);
insert.AddParameter("@Comment", comment);
insert.AddParameter("@CreatedOn", created);
insert.AddParameter("@CreatedByName", createdBy);
if (order.OrderId != null)
{
insert.AddParameter("@ShoppingCartOrderId", order.OrderId);
}
else
{
insert.AddParameter("@ShoppingCartOrderId", System.Data.SqlTypes.SqlInt32.Null);
}
if (order.UserId != null)
{
insert.AddParameter("@ShoppingCartUserId", order.UserId);
}
else
{
insert.AddParameter("@ShoppingCartUserId", System.Data.SqlTypes.SqlInt32.Null);
}
insert.AddParameter("@FilesAttached", filesAttached);
insert.ExecuteScalar();
}
if (order.Items != null && order.Items.Count > 0)
{
SaveEnquiryItems(order, jobId, store);
}
if (files != null && files != null && files.Count > 0)
{
SaveFiles(jobId, files);
}
try
{
SubmitJobStatusChangeRequest(jobId, "WebsiteEnquiry", null, null, null, null, null);
}
catch (Exception ex)
{
// Ignore any errors trying to do the background process trigger for confirmation emails.
}
return(true);
}
private static void GetCompanyAndClient(Order order, out Int32?companyId, out Int32?clientId)
{
companyId = null;
clientId = null;
// See if the client exists
using (SqlText select = new SqlText(
"Select ClientId, FullName, FirstName, LastName, EmailAddress, [ALL_Company].CompanyId, [ALL_Company].CompanyName " +
"From [ALL_Client] " +
"Left Outer Join [ALL_Company] ON [ALL_Company].CompanyId = [ALL_Client].CompanyId " +
"Where EmailAddress = @EmailAddress AND [ALL_Client].Deleted=0 ", crmConnectionStringName))
{
select.AddParameter("@EmailAddress", order.BillToEmail.Trim());
DbDataReader myReader = select.ExecuteReader();
while (myReader.Read())
{
Int32 wkClientId = myReader.GetInt32(0);
string wkName = myReader.GetString(1);
//string wkFirstName = myReader.GetString(2);
//string wkLastname = myReader.GetString(3);
string wkEmail = myReader.GetString(4);
Int32? wkCompanyId = null;
if (!myReader.IsDBNull(5))
{
wkCompanyId = myReader.GetInt32(5);
}
string wkCompanyName = "";
if (!myReader.IsDBNull(6))
{
wkCompanyName = myReader.GetString(6);
}
clientId = wkClientId;
companyId = wkCompanyId;
}
}
if (clientId != null)
{
// Found a record so exit.
return;
}
if (!string.IsNullOrEmpty(order.BillToCompany) && companyId == null && clientId == null)
{
// See if the company exists
using (SqlText select = new SqlText(
"Select CompanyId, CompanyName " +
"From [ALL_Company] " +
"Where CompanyName = @CompanyName AND Deleted=0 AND DepartmentName is null ", crmConnectionStringName))
{
select.AddParameter("@CompanyName", order.BillToCompany.Trim());
DbDataReader myReader = select.ExecuteReader();
while (myReader.Read())
{
companyId = myReader.GetInt32(0);
}
}
if (companyId == null)
{
// Not found so create a new company record.
using (Redi.Utility.SqlText insert = new Redi.Utility.SqlText(
"Insert Into ALL_Company (CompanyName, CreatedOn, CreatedByName) " +
"Values (@CompanyName, @CreatedOn, @CreatedByName) " +
"SELECT SCOPE_IDENTITY(); ", crmConnectionStringName))
{
insert.AddParameter("@CompanyName", order.BillToCompany.Trim());
insert.AddParameter("@CreatedOn", GetCurrentStandardTime());
insert.AddParameter("@CreatedByName", createdBy);
companyId = Convert.ToInt32(insert.ExecuteScalar());
}
}
}
string wkMobile = "";
string wkPhone = "";
string wkFax = "";
string wkFullName = "";
if (!string.IsNullOrEmpty(order.BillToFirstName))
{
wkFullName = order.BillToFirstName.Trim() + ' ';
}
if (!string.IsNullOrEmpty(order.BillToLastName))
{
wkFullName = wkFullName + order.BillToLastName.Trim();
}
if (!string.IsNullOrEmpty(order.BillToPhone))
{
if (order.BillToPhone.Length > 2 && order.BillToPhone.Substring(0, 2) == "04")
{
wkMobile = order.BillToPhone;
}
else
{
wkPhone = order.BillToPhone;
}
}
if (!string.IsNullOrEmpty(order.BillToFax))
{
wkFax = order.BillToFax;
}
// Not an existing client so create a new record.
using (Redi.Utility.SqlText insert = new Redi.Utility.SqlText(
"Insert Into ALL_Client (FullName, FirstName, LastName, Phone, Fax, MobilePhone, CreatedOn, CreatedByName, EmailAddress, CompanyId ) " +
"Values (@FullName, @FirstName, @LastName, @Phone, @Fax, @MobilePhone, @CreatedOn, @CreatedByName, @EmailAddress, @CompanyId) " +
"SELECT SCOPE_IDENTITY(); ", crmConnectionStringName))
{
insert.AddParameter("@FullName", wkFullName);
insert.AddParameter("@FirstName", order.BillToFirstName);
insert.AddParameter("@LastName", order.BillToLastName);
insert.AddParameter("@Phone", wkPhone);
insert.AddParameter("@Fax", wkFax);
insert.AddParameter("@MobilePhone", wkMobile);
if (companyId != null)
{
insert.AddParameter("@CompanyId", companyId);
}
else
{
insert.AddParameter("@CompanyId", System.Data.SqlTypes.SqlInt32.Null);
}
insert.AddParameter("@CreatedOn", GetCurrentStandardTime());
insert.AddParameter("@CreatedByName", createdBy);
insert.AddParameter("@EmailAddress", order.BillToEmail);
clientId = Convert.ToInt32(insert.ExecuteScalar());
}
}