private static void SaveFiles(Int32 jobId, List <FileAttachment> files)
{
foreach (FileAttachment item in files)
{
if (item.name != "DELETE")
{
using (Redi.Utility.SqlText insert = new Redi.Utility.SqlText(
"Insert Into ALL_JobFile (JobFileFileName, JobFileLength, JobFileContentType, JobFileCategoryCode, JobId, CreatedOn, CreatedByName, DisplayName, FileSystem, FolderName, VersionNo ) " +
"Values (@JobFileFileName, @JobFileLength, @JobFileContentType, @JobFileCategoryCode, @JobId, @CreatedOn, @CreatedByName, @DisplayName, @FileSystem, @FolderName, @VersionNo) ", crmConnectionStringName))
{
string wkContentType = Redi.Utility.MapFileContentType.Do(item.name);
if (string.IsNullOrEmpty(wkContentType))
{
wkContentType = ".";
}
insert.AddParameter("@JobFileFileName", item.target_name);
insert.AddParameter("@JobFileLength", item.size);
insert.AddParameter("@JobFileContentType", wkContentType);
insert.AddParameter("@JobFileCategoryCode", "Invoice");
insert.AddParameter("@JobId", jobId);
insert.AddParameter("@CreatedOn", DateTime.Now);
insert.AddParameter("@CreatedByName", createdBy);
insert.AddParameter("@DisplayName", item.name);
insert.AddParameter("@FileSystem", "LOCAL"); // Local to be moved to Amazon
insert.AddParameter("@FolderName", "ClientUploadedFiles");
insert.AddParameter("@VersionNo", 1);
insert.ExecuteNonQuery();
}
}
}
// Kick off the process that moves files to S3.
RequestBackgroundProcess("ABLELocalFileToS3", "");
}
/// <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);
}
// Move all LOCAL files to S3
public static void Do(string requestData)
{
using (Redi.Utility.SqlText select = new Redi.Utility.SqlText(
"Select JobFileFileName, Id, FolderName, VersionNo, DisplayName " +
"From ALL_JobFile " +
"Where FileSystem='LOCAL' AND Deleted = 0 "))
{
DbDataReader myReader = select.ExecuteReader();
while (myReader.Read())
{
string fileName = myReader.GetString(0);
Int32 fileId = myReader.GetInt32(1);
string folderName = "";
if (!myReader.IsDBNull(2))
{
folderName = myReader.GetString(2);
}
Int32 version = myReader.GetInt32(3);
string displayName = "";
if (!myReader.IsDBNull(4))
{
displayName = myReader.GetString(4);
}
else
{
displayName = fileName;
}
string wkCurrentPath = HostingEnvironment.MapPath("\\" + folderName.Trim('/') + "\\" + fileName);
if (MoveFile(fileName, folderName, version, wkCurrentPath))
{
using (Redi.Utility.SqlText update = new Redi.Utility.SqlText(
"Update ALL_JobFile " +
"Set FileSystem = 'S3', " +
" DisplayName = @DisplayName " +
"Where Id = @Id "))
{
update.AddParameter("@DisplayName", displayName);
update.AddParameter("@Id", fileId);
update.ExecuteNonQuery();
}
}
}
}
}
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());
}
}
/// <summary>
/// Get the next available number from the NextNumber table and prefix with the prefix defined on the same table and the year
/// prefix-year-number
/// </summary>
/// <param name="NextNumberType"></param>
/// <returns></returns>
public static string GetNextYearID(string NextNumberType, Int16 NumberLength)
{
string wkIDPrefix = "";
int wkCurrentYear = 0;
Int64 wkNextNumber = GetNextNumber(NextNumberType);
using (SqlText SelectID = new SqlText(
"Select IDPrefix, CurrentYear " +
"from [ALL_NextNumber] " +
"Where NextNumberType = @NextNumberType; ", CrmHelper.crmConnectionStringName))
{
SelectID.AddParameter("@NextNumberType", NextNumberType);
DbDataReader myReader = SelectID.ExecuteReader();
if (myReader.Read())
{
wkIDPrefix = myReader.GetString(0);
if (!myReader.IsDBNull(1))
{
wkCurrentYear = myReader.GetInt32(1);
}
}
}
if (wkCurrentYear != DateTime.Now.Year)
{
wkCurrentYear = DateTime.Now.Year;
using (SqlText UpdateNext = new SqlText(
"Update [ALL_NextNumber] " +
"Set NextNumber = 1 " +
" ,CurrentYear = @CurrentYear " +
"Where NextNumberType = @NextNumberType; ", CrmHelper.crmConnectionStringName))
{
UpdateNext.AddParameter("@NextNumberType", NextNumberType);
UpdateNext.AddParameter("@CurrentYear", wkCurrentYear);
UpdateNext.ExecuteNonQuery();
}
wkNextNumber = 1;
}
string wkNum = "";
if (wkNextNumber.ToString().Length > NumberLength)
{
NumberLength = (Int16)wkNextNumber.ToString().Length;
}
switch (NumberLength)
{
case 0: wkNum = wkNextNumber.ToString();
break;
case 7: wkNum = wkNextNumber.ToString("0000000");
break;
case 6: wkNum = wkNextNumber.ToString("000000");
break;
case 5: wkNum = wkNextNumber.ToString("00000");
break;
case 4: wkNum = wkNextNumber.ToString("0000");
break;
case 3: wkNum = wkNextNumber.ToString("000");
break;
default: wkNum = wkNextNumber.ToString();
break;
}
return(wkIDPrefix + wkCurrentYear.ToString().Substring(2, 2) + "-" + wkNum);
}
