using framework_business;
using framework_library;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace scheduler
{
class SurfaceDataImport
{
string _dbName = "";
oSetup _setup = new oSetup();
public SurfaceDataImport(string dbName, oSetup setup)
{
_dbName = dbName;
_setup = setup;
}
public string Process(string startupPath)
{
StringBuilder returnString = new StringBuilder();
try
{
//loop through all folders in instance folder (a folder for each surface)
foreach (string surfaceDirectoryPath in Directory.GetDirectories(startupPath))
{
returnString.AppendLine("Processing folder: " + surfaceDirectoryPath);
//get surface
DirectoryInfo dir = new DirectoryInfo(surfaceDirectoryPath);
string surfaceName = dir.Name;
DataTable dtImportSurface = SQLSelectDatatable("select * from pal_Surface where name = '" + surfaceName.Replace("'","''") + "'");
ArrayList importSurfaceList = utils.ConvertDataTableToList(dtImportSurface, typeof(oSurface));
foreach (oSurface importSurface in importSurfaceList)
{
//loop through all files in surface folder
foreach (string importFile in Directory.GetFiles(surfaceDirectoryPath))
{
returnString.AppendLine("Processing file: " + importFile);
//get data from excel file
DataTable importData = utils.getDataTableFromExcel(importFile);
FileInfo file = new FileInfo(importFile);
string inProgressFolder = surfaceDirectoryPath;
if (!inProgressFolder.EndsWith("\\"))
inProgressFolder += "\\";
inProgressFolder += "InProgress\\";
utils.validateFolder(inProgressFolder);
file.MoveTo(inProgressFolder + file.Name);
try
{
if (importData != null && importData.Rows.Count > 0)//we have records
{
returnString.AppendLine("Row count: " + importData.Rows.Count);
if (_setup.code == "CAPI-1" && importSurface.name == "SupplierPricelistParts")
{
//get import history item ID from filename
int historyItemId = 0;
int start = file.Name.LastIndexOf("_") + 1;
int end = file.Name.LastIndexOf(".");
if (start > 0 && end > 0 && start < end)
int.TryParse(file.Name.Substring(start, end - start), out historyItemId);
if (historyItemId <= 0)
throw new Exception("Supplier Pricelist Import History Item ID could not be determined from import file name.");
string error = ImportDataToPublishedSurfaceCAPI1SupplierPricelistParts(importData, importSurface.recId, importSurface.name, historyItemId);
if (error != "")
returnString.AppendLine(error);
}
else if (importSurface.isPublished)
{
//ImportDataToPublishedSurface(importData, importSurface.recId, importSurface.name);
}
else
{
//ImportDataToSurface(importData, importSurface.recId);
}
}
string completedFolder = surfaceDirectoryPath;
if (!completedFolder.EndsWith("\\"))
completedFolder += "\\";
completedFolder += "Completed\\";
utils.validateFolder(completedFolder);
file.MoveTo(completedFolder += file.Name);
returnString.AppendLine("Import successful");
}
catch (Exception ex)
{
string errorFolder = surfaceDirectoryPath;
if (!errorFolder.EndsWith("\\"))
errorFolder += "\\";
errorFolder += "Error\\";
utils.validateFolder(errorFolder);
file.MoveTo(errorFolder += file.Name);
oEmail email = new oEmail();
email.fromAddress = _setup.communicationEmail;
email.toAddress = ConfigurationManager.AppSettings["support"];
if (email.toAddress.EndsWith(";"))
email.toAddress = email.toAddress.TrimEnd(';');
email.Body = importFile + Environment.NewLine + Environment.NewLine + ex.Message + Environment.NewLine + Environment.NewLine + ex.StackTrace;
email.Subject = "Scheduler Exception: Surface Data Import";
try
{
communication.SendAnEmail(email);
}
catch { }
returnString.AppendLine("ERROR: " + ex.Message);
}
}
}
returnString.AppendLine("Folder processing complete.");
}
}
catch (Exception ex)
{
returnString.AppendLine("EXCEPTION: " + ex.Message);
}
return returnString.ToString();
}
//Redo these methods without using xData - need to connect to database manually
#region To Do
/*
///
/// Import Data to Published Surface
///
///
///
private void ImportDataToPublishedSurface(DataTable sourcedata, int surfaceId, string surfaceName, int _parentItemIdXX = 0)
{
ArrayList fields = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceField), "surfaceId", surfaceId.ToString(), "sequence");
ArrayList fieldData = new ArrayList();
int counter = 0;
foreach (DataRow drow in sourcedata.Rows)
{
counter++;
//create new surface item
oSurfaceItem item = new oSurfaceItem();
item.surfaceId = surfaceId;
item.isActive = true;
//CVH 2016-09-15 isDeleted
item.isDeleted = false;
item.dateCreated = DateTime.Now;
item.createdBy = 0;
item.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), item);
//backgroundWorker1.ReportProgress(1, "Surface Item " + item.recId + " created.");
if (item.recId > 0)
{
//build insert query for cols
StringBuilder insertBuilder = new StringBuilder();
insertBuilder.AppendLine("INSERT INTO publ_" + surfaceName + " ");
insertBuilder.AppendLine("([itemID]");
insertBuilder.AppendLine(",[isActive]");
insertBuilder.AppendLine(",[surfaceId]");
foreach (DataColumn column in sourcedata.Columns)
{
foreach (oSurfaceField field in fields)
{
if (!field.surfaceFieldName.StartsWith("_"))
{
string fieldNameToMatch = field.surfaceFieldName;
if (!column.ColumnName.Contains("-:-"))
fieldNameToMatch = field.surfaceFieldDisplay;
//if (field.isActive && field.surfaceFieldName != "ParentSurfaceItemId")
if (field.isActive)
{
if (utils.stripCharacters(column.ColumnName.Replace("-:-", "_")).Replace(" ", "") == utils.stripCharacters(fieldNameToMatch).Replace(" ", ""))
{
pNums.FieldType typ = (pNums.FieldType)Enum.ToObject(typeof(pNums.FieldType), field.surfaceFieldTypeId);
switch (typ)
{
case pNums.FieldType.Text://text
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Checkbox://checkbox
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Date:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Time:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Decimal:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Number:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Picklist:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.RadioButtonList:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.CheckboxList:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.MultiPicklist:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Address:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Caption://caption
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.FormulaField:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
}
break;
}
}
}
}
}
insertBuilder.AppendLine(") VALUES (");
insertBuilder.AppendLine(item.recId.ToString());
insertBuilder.AppendLine(",1");
insertBuilder.AppendLine("," + item.surfaceId);
//if (_parentItemId > 0)
// insertBuilder.AppendLine("," + _parentItemId);
foreach (DataColumn column in sourcedata.Columns)
{
foreach (oSurfaceField field in fields)
{
if (!field.surfaceFieldName.StartsWith("_"))
{
string fieldNameToMatch = field.surfaceFieldName;
if (!column.ColumnName.Contains("-:-"))
fieldNameToMatch = field.surfaceFieldDisplay;
//if (field.isActive && field.surfaceFieldName != "ParentSurfaceItemId")
if (field.isActive)
{
if (utils.stripCharacters(column.ColumnName.Replace("-:-", "_")).Replace(" ", "") == utils.stripCharacters(fieldNameToMatch).Replace(" ", ""))
{
//check the field type
pNums.FieldType typ = (pNums.FieldType)Enum.ToObject(typeof(pNums.FieldType), field.surfaceFieldTypeId);
switch (typ)
{
case pNums.FieldType.Text://text
insertBuilder.AppendLine(",'" + utils.formatSqlString(drow[column.ColumnName].ToString()) + "'");
break;
case pNums.FieldType.Checkbox://checkbox
if (drow[column.ColumnName].ToString().ToLower() == "true")
{
insertBuilder.AppendLine(",'1'");
}
else if (drow[column.ColumnName].ToString().ToLower() == "1")
{ insertBuilder.AppendLine(",'1'"); }
else if (drow[column.ColumnName].ToString().ToLower() == "yes")
{ insertBuilder.AppendLine(",'1'"); }
else
{ insertBuilder.AppendLine(",'0'"); }
break;
case pNums.FieldType.Date:
if (drow[column.ColumnName].ToString() != String.Empty)
{
if (field.surfaceDateTypeId == (int)pNums.SurfaceDateType.Year)
{
int yr = 0;
int.TryParse(drow[column.ColumnName].ToString(), out yr);
if (yr > 0)
{
DateTime myDateVal = new DateTime(yr, 1, 1);
insertBuilder.AppendLine(",'" + utils.fixDate(myDateVal) + "'");
}
else
{
insertBuilder.AppendLine(",'" + utils.fixDate(utils.formatStringToDate(drow[column.ColumnName].ToString())) + "'");
}
}
else
{
insertBuilder.AppendLine(",'" + utils.fixDate(utils.formatStringToDate(drow[column.ColumnName].ToString())) + "'");
}
}
else
insertBuilder.AppendLine(",''");
break;
case pNums.FieldType.Time:
if (drow[column.ColumnName].ToString() != String.Empty)
{
insertBuilder.AppendLine(",'" + utils.fixDate(utils.formatStringToTime(drow[column.ColumnName].ToString())));
}
break;
case pNums.FieldType.Decimal:
//CVH 2018-11-20 Force decimal separator, otherwise when running on server it bombs out with a , separator
NumberFormatInfo nfi = new NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
decimal decimalValue = 0;
decimal.TryParse(drow[column.ColumnName].ToString().Replace(" ", "").Replace("R", "").Replace("%", ""), out decimalValue);
insertBuilder.AppendLine("," + decimalValue.ToString(nfi));
break;
case pNums.FieldType.Number:
int intValue = 0;
int.TryParse(drow[column.ColumnName].ToString(), out intValue);
insertBuilder.AppendLine("," + intValue);
break;
case pNums.FieldType.Picklist:
if (field.isCustomSource)
{
string picklistValue = drow[column.ColumnName].ToString();
string fieldSelect = String.Empty;
String[] flds = field.relationalFields.Split(char.Parse(","));
foreach (string f in flds)
{
if (fieldSelect == String.Empty)
fieldSelect = f;
else
fieldSelect += " + ' ' + " + f;
}
if (field.relationalObject != String.Empty)
{
bool userService = field.relationalObject == "pal_User" ? true : false;
if (userService && picklistValue.Contains(","))//handle surname, name
{
string surname = picklistValue.Substring(0, picklistValue.IndexOf(","));
string name = picklistValue.Replace(" ", "").Substring(picklistValue.IndexOf(",") + 1);
picklistValue = name + " " + surname;
}
string query = "Select " + field.relationalValues + " AS Value FROM " + field.relationalObject + " WHERE (" + fieldSelect + ") = '" + picklistValue + "' ";
if (field.relationalObject == "pal_User")
query += "AND customerCode = '" + handler.ReturnSetup().code + "'";
DataTable pickData = xData.GetCustomTypedTable(query, userService);
if (pickData.Rows.Count > 0)
{
insertBuilder.AppendLine(",'" + pickData.Rows[0][0].ToString().Replace("'", "''") + "'");
}
else
{
insertBuilder.AppendLine(",''");
}
}
}
else
{
//CVH lookupDisplay~reason
string picklistValue = drow[column.ColumnName].ToString();
if (picklistValue != null && picklistValue.Trim() != String.Empty)
{
string lookupDisplay = "";
string reason = "";
if (picklistValue.IndexOf("~") >= 0)
{
lookupDisplay = picklistValue.Substring(0, picklistValue.IndexOf("~"));
reason = picklistValue.Substring(picklistValue.IndexOf("~") + 1);
}
else
{
lookupDisplay = picklistValue;
}
if (lookupDisplay != "")
{
ArrayList pickListValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + lookupDisplay);
if (pickListValues.Count > 0)
{
foreach (oSurfaceLookup look in pickListValues)
{
insertBuilder.AppendLine("," + look.recId);
}
}
else
{
oSurfaceLookup lookAdd = new oSurfaceLookup();
lookAdd.categoryId = field.lookupCategory;
lookAdd.display = lookupDisplay;
lookAdd.isActive = true;
lookAdd.sequence = 1;
lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
if (lookAdd.recId > 0)
{
insertBuilder.AppendLine("," + lookAdd.recId);
}
}
}
}
}
break;
case pNums.FieldType.RadioButtonList:
if (field.isCustomSource)
{
string picklistValue = drow[column.ColumnName].ToString();
string fieldSelect = String.Empty;
String[] flds = field.relationalFields.Split(char.Parse(","));
foreach (string f in flds)
{
if (fieldSelect == String.Empty)
fieldSelect = f;
else
fieldSelect += " + ' ' + " + f;
}
if (field.relationalObject != String.Empty)
{
bool userService = field.relationalObject == "pal_User" ? true : false;
string query = "Select " + field.relationalValues + " AS Value FROM " + field.relationalObject + " WHERE (" + fieldSelect + ") = '" + picklistValue + "' ";
if (field.relationalObject == "pal_User")
query += "AND customerCode = " + handler.ReturnSetup().code;
DataTable pickData = xData.GetCustomTypedTable(query, userService);
if (pickData.Rows.Count > 0)
{
insertBuilder.AppendLine(",'" + pickData.Rows[0][0].ToString().Replace("'", "''") + "'");
}
else
{
insertBuilder.AppendLine(",''");
}
}
}
else
{
//CVH lookupDisplay~reason
string radioValue = drow[column.ColumnName].ToString();
if (radioValue != null && radioValue.Trim() != String.Empty)
{
string lookupDisplay = "";
string reason = "";
if (radioValue.IndexOf("~") >= 0)
{
lookupDisplay = radioValue.Substring(0, radioValue.IndexOf("~"));
reason = radioValue.Substring(radioValue.IndexOf("~") + 1);
}
else
{
lookupDisplay = radioValue;
}
if (lookupDisplay != "")
{
ArrayList radioListValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + lookupDisplay);
if (radioListValues.Count > 0)
{
foreach (oSurfaceLookup look in radioListValues)
{
insertBuilder.AppendLine("," + look.recId);
}
}
else
{
oSurfaceLookup lookAdd = new oSurfaceLookup();
lookAdd.categoryId = field.lookupCategory;
lookAdd.display = lookupDisplay;
lookAdd.isActive = true;
lookAdd.sequence = 1;
lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
if (lookAdd.recId > 0)
{
insertBuilder.AppendLine("," + lookAdd.recId);
}
}
}
}
}
break;
case pNums.FieldType.CheckboxList:
if (field.isCustomSource)
{
string picklistValue = drow[column.ColumnName].ToString();
string fieldSelect = String.Empty;
String[] flds = field.relationalFields.Split(char.Parse(","));
foreach (string f in flds)
{
if (fieldSelect == String.Empty)
fieldSelect = f;
else
fieldSelect += " + ' ' + " + f;
}
if (field.relationalObject != String.Empty)
{
bool userService = field.relationalObject == "pal_User" ? true : false;
string query = "Select " + field.relationalValues + " AS Value FROM " + field.relationalObject + " WHERE (" + fieldSelect + ") = '" + picklistValue + "' ";
if (field.relationalObject == "pal_User")
query += "AND customerCode = " + handler.ReturnSetup().code;
DataTable pickData = xData.GetCustomTypedTable(query, userService);
if (pickData.Rows.Count > 0)
{
insertBuilder.AppendLine(",'" + pickData.Rows[0][0].ToString().Replace("'", "''") + "'");
}
else
{
insertBuilder.AppendLine(",''");
}
}
}
else
{
//CVH lookupDisplay~reason|lookupDisplay~reason Save as lookupId~R~Reason~|~
string checkValue = drow[column.ColumnName].ToString();
string vals = "";
if (checkValue != null && checkValue.Trim() != String.Empty)
{
foreach (string value in checkValue.Split('|'))
{
string lookupDisplay = "";
string reason = "";
if (value.IndexOf("~") >= 0)
{
lookupDisplay = value.Substring(0, value.IndexOf("~"));
reason = value.Substring(value.IndexOf("~") + 1);
}
else
{
lookupDisplay = value;
}
if (lookupDisplay != "")
{
ArrayList checkListValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + lookupDisplay);
if (checkListValues.Count > 0)
{
foreach (oSurfaceLookup look in checkListValues)
{
vals += look.recId.ToString() + "~R~" + reason + "~|~";
}
}
else
{
oSurfaceLookup lookAdd = new oSurfaceLookup();
lookAdd.categoryId = field.lookupCategory;
lookAdd.display = lookupDisplay;
lookAdd.isActive = true;
lookAdd.sequence = 1;
lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
if (lookAdd.recId > 0)
{
vals += lookAdd.recId.ToString() + "~R~" + reason + "~|~";
}
}
}
}
}
insertBuilder.AppendLine(",'" + vals.Replace("'", "''") + "'");
}
break;
case pNums.FieldType.MultiPicklist:
string multiValue = drow[column.ColumnName].ToString();
string valsMulti = "";
if (multiValue != null && multiValue.Trim() != String.Empty)
{
foreach (string value in multiValue.Split('|'))
{
if (value != "")
{
ArrayList multiPicklistValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + value);
if (multiPicklistValues.Count > 0)
{
foreach (oSurfaceLookup look in multiPicklistValues)
{
if (valsMulti == "")
valsMulti = look.recId.ToString();
else valsMulti += "," + look.recId.ToString();
}
}
else
{
oSurfaceLookup lookAdd = new oSurfaceLookup();
lookAdd.categoryId = field.lookupCategory;
lookAdd.display = value;
lookAdd.isActive = true;
lookAdd.sequence = 1;
lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
if (lookAdd.recId > 0)
{
if (valsMulti == "")
valsMulti = lookAdd.recId.ToString();
else valsMulti += "," + lookAdd.recId.ToString();
}
}
}
}
}
insertBuilder.AppendLine(",'" + valsMulti.Replace("'", "''") + "'");
break;
case pNums.FieldType.Address:
//CVH Street,Suburb,City,Province,South Africa,Postal Code. Saved as ~1~Street~|~~2~Suburb~|~ etc.
if (drow[column.ColumnName].ToString() != String.Empty)
{
string address = "";
int line = 1;
foreach (string addressLine in drow[column.ColumnName].ToString().Split(','))
{
address += "~" + line.ToString() + "~" + addressLine.Trim() + "~|~";
line++;
}
insertBuilder.AppendLine(",'" + address.Replace("'", "''") + "'");
}
break;
case pNums.FieldType.Caption://caption
insertBuilder.AppendLine(",'" + drow[column.ColumnName].ToString() + "'");
break;
case pNums.FieldType.FormulaField:
decimal valueDecimal = 0;
if (decimal.TryParse(drow[column.ColumnName].ToString(), out valueDecimal))
{
insertBuilder.AppendLine("," + valueDecimal);
}
else //assume textbox
{
insertBuilder.AppendLine(",'" + drow[column.ColumnName].ToString().Replace("'", "''") + "'");
}
break;
}
break;
}
}
}
}
}
insertBuilder.AppendLine(")");
try
{
//insert record
xData.RunCustomQuery(insertBuilder.ToString());
}
catch (Exception ex)
{
throw ex;
}
}
// backgroundWorker1.ReportProgress(2, "Row " + counter + " has been imported.");
}
}
///
/// Import Data to Surface
///
///
///
private void ImportDataToSurface(DataTable sourcedata, int surfaceId, int _parentItemId = 0)
{
ArrayList fields = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceField), "surfaceId", surfaceId.ToString(), "sequence");
ArrayList fieldData = new ArrayList();
int counter = 0;
foreach (DataRow drow in sourcedata.Rows)
{
counter++;
//create new surface item
oSurfaceItem item = new oSurfaceItem();
item.surfaceId = surfaceId;
item.isActive = true;
//CVH 2016-09-15 isDeleted
item.isDeleted = false;
item.dateCreated = DateTime.Now;
item.createdBy = 0;
item.recId = xData.SaveTyped("recId", typeof(oSurfaceItem), item);
//backgroundWorker1.ReportProgress(1, "Surface Item " + item.recId + " created.");
if (item.recId > 0)
{
foreach (DataColumn column in sourcedata.Columns)
{
//backgroundWorker1.ReportProgress(1, "Creating SurfaceFieldData for field " + column.ColumnName + ".");
foreach (oSurfaceField field in fields)
{
if (utils.stripCharacters(column.ColumnName.Replace("-:-", "_")).Replace(" ", "") == utils.stripCharacters(field.surfaceFieldName).Replace(" ", ""))
{
oSurfaceFieldData dataField = new oSurfaceFieldData();
dataField.surfaceFieldID = field.recId;
dataField.surfaceId = surfaceId;
dataField.surfaceItemId = item.recId;
//check the field type
pNums.FieldType typ = (pNums.FieldType)Enum.ToObject(typeof(pNums.FieldType), field.surfaceFieldTypeId);
switch (typ)
{
case pNums.FieldType.Text://text
dataField.surfaceFieldValueChar = drow[column.ColumnName].ToString();
break;
case pNums.FieldType.Checkbox://checkbox
if (drow[column.ColumnName].ToString().ToLower() == "true")
{
dataField.surfaceFieldValueBool = true;
}
else if (drow[column.ColumnName].ToString().ToLower() == "1")
{ dataField.surfaceFieldValueBool = true; }
else if (drow[column.ColumnName].ToString().ToLower() == "yes")
{ dataField.surfaceFieldValueBool = true; }
else
{ dataField.surfaceFieldValueBool = false; }
break;
case pNums.FieldType.Date:
if (drow[column.ColumnName].ToString() != String.Empty)
{
if (field.surfaceDateTypeId == (int)pNums.SurfaceDateType.Year)
{
int yr = 0;
int.TryParse(drow[column.ColumnName].ToString(), out yr);
if (yr > 0)
{
DateTime myDateVal = new DateTime(yr, 1, 1);
dataField.surfaceFieldValueDate = myDateVal;
}
else
{
dataField.surfaceFieldValueDate = utils.formatStringToDate(drow[column.ColumnName].ToString());
}
}
else
{
dataField.surfaceFieldValueDate = utils.formatStringToDate(drow[column.ColumnName].ToString());
}
}
break;
case pNums.FieldType.Time:
if (drow[column.ColumnName].ToString() != String.Empty)
{
dataField.surfaceFieldValueTime = utils.formatStringToTime(drow[column.ColumnName].ToString());
}
break;
case pNums.FieldType.Decimal:
decimal decimalValue = 0;
decimal.TryParse(drow[column.ColumnName].ToString().Replace(" ", "").Replace("R", "").Replace("%", ""), out decimalValue);
dataField.surfaceFieldValueDecimal = decimalValue;
break;
case pNums.FieldType.Number:
if (_parentItemId > 0 && field.surfaceFieldName == "ParentSurfaceItemId")
{
dataField.surfaceFieldValueNum = _parentItemId;
}
else
{
int intValue = 0;
int.TryParse(drow[column.ColumnName].ToString(), out intValue);
dataField.surfaceFieldValueNum = intValue;
}
break;
case pNums.FieldType.Picklist:
//CVH lookupDisplay~reason
string picklistValue = drow[column.ColumnName].ToString();
if (picklistValue != null && picklistValue.Trim() != String.Empty)
{
if (field.relationalObject == "oSurface")
{
dataField.surfaceFieldValueChar = drow[column.ColumnName].ToString();
}
else
{
string lookupDisplay = "";
string reason = "";
if (picklistValue.IndexOf("~") >= 0)
{
lookupDisplay = picklistValue.Substring(0, picklistValue.IndexOf("~"));
reason = picklistValue.Substring(picklistValue.IndexOf("~") + 1);
}
else
{
lookupDisplay = picklistValue;
}
if (lookupDisplay != "")
{
ArrayList pickListValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + lookupDisplay);
if (pickListValues.Count > 0)
{
foreach (oSurfaceLookup look in pickListValues)
{
dataField.surfaceFieldLookupID = look.recId;
dataField.surfaceFieldValueChar = reason;
}
}
else
{
oSurfaceLookup lookAdd = new oSurfaceLookup();
lookAdd.categoryId = field.lookupCategory;
lookAdd.display = lookupDisplay;
lookAdd.isActive = true;
lookAdd.sequence = 1;
lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
if (lookAdd.recId > 0)
{
dataField.surfaceFieldLookupID = lookAdd.recId;
dataField.surfaceFieldValueChar = reason;
}
}
}
}
}
break;
case pNums.FieldType.RadioButtonList:
//CVH lookupDisplay~reason
string radioValue = drow[column.ColumnName].ToString();
if (radioValue != null && radioValue.Trim() != String.Empty)
{
string lookupDisplay = "";
string reason = "";
if (radioValue.IndexOf("~") >= 0)
{
lookupDisplay = radioValue.Substring(0, radioValue.IndexOf("~"));
reason = radioValue.Substring(radioValue.IndexOf("~") + 1);
}
else
{
lookupDisplay = radioValue;
}
if (lookupDisplay != "")
{
ArrayList radioListValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + lookupDisplay);
if (radioListValues.Count > 0)
{
foreach (oSurfaceLookup look in radioListValues)
{
dataField.surfaceFieldLookupID = look.recId;
dataField.surfaceFieldValueChar = reason;
}
}
else
{
oSurfaceLookup lookAdd = new oSurfaceLookup();
lookAdd.categoryId = field.lookupCategory;
lookAdd.display = lookupDisplay;
lookAdd.isActive = true;
lookAdd.sequence = 1;
lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
if (lookAdd.recId > 0)
{
dataField.surfaceFieldLookupID = lookAdd.recId;
dataField.surfaceFieldValueChar = reason;
}
}
}
}
break;
case pNums.FieldType.CheckboxList:
//CVH lookupDisplay~reason|lookupDisplay~reason Save as lookupId~R~Reason~|~
string checkValue = drow[column.ColumnName].ToString();
if (checkValue != null && checkValue.Trim() != String.Empty)
{
dataField.surfaceFieldValueChar = "";
foreach (string value in checkValue.Split('|'))
{
string lookupDisplay = "";
string reason = "";
if (value.IndexOf("~") >= 0)
{
lookupDisplay = value.Substring(0, value.IndexOf("~"));
reason = value.Substring(value.IndexOf("~") + 1);
}
else
{
lookupDisplay = value;
}
if (lookupDisplay != "")
{
ArrayList checkListValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + lookupDisplay);
if (checkListValues.Count > 0)
{
foreach (oSurfaceLookup look in checkListValues)
{
dataField.surfaceFieldValueChar += look.recId.ToString() + "~R~" + reason + "~|~";
}
}
else
{
oSurfaceLookup lookAdd = new oSurfaceLookup();
lookAdd.categoryId = field.lookupCategory;
lookAdd.display = lookupDisplay;
lookAdd.isActive = true;
lookAdd.sequence = 1;
lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
if (lookAdd.recId > 0)
{
dataField.surfaceFieldValueChar += lookAdd.recId.ToString() + "~R~" + reason + "~|~";
}
}
}
}
}
break;
case pNums.FieldType.MultiPicklist:
string multiValue = drow[column.ColumnName].ToString();
if (multiValue != null && multiValue.Trim() != String.Empty)
{
dataField.surfaceFieldValueChar = "";
foreach (string value in multiValue.Split('|'))
{
if (value != "")
{
ArrayList multiPicklistValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + value);
if (multiPicklistValues.Count > 0)
{
foreach (oSurfaceLookup look in multiPicklistValues)
{
if (dataField.surfaceFieldValueChar == "")
dataField.surfaceFieldValueChar = look.recId.ToString();
else dataField.surfaceFieldValueChar += "," + look.recId.ToString();
}
}
else
{
oSurfaceLookup lookAdd = new oSurfaceLookup();
lookAdd.categoryId = field.lookupCategory;
lookAdd.display = value;
lookAdd.isActive = true;
lookAdd.sequence = 1;
lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
if (lookAdd.recId > 0)
{
if (dataField.surfaceFieldValueChar == "")
dataField.surfaceFieldValueChar = lookAdd.recId.ToString();
else dataField.surfaceFieldValueChar += "," + lookAdd.recId.ToString();
}
}
}
}
}
break;
case pNums.FieldType.Address:
//CVH Street,Suburb,City,Province,South Africa,Postal Code. Saved as ~1~Street~|~~2~Suburb~|~ etc.
if (drow[column.ColumnName].ToString() != String.Empty)
{
string address = "";
int line = 1;
foreach (string addressLine in drow[column.ColumnName].ToString().Split(','))
{
address += "~" + line.ToString() + "~" + addressLine.Trim() + "~|~";
line++;
}
dataField.surfaceFieldValueChar = address;
}
break;
case pNums.FieldType.Caption://caption
dataField.surfaceFieldValueChar = drow[column.ColumnName].ToString();
break;
case pNums.FieldType.FormulaField:
decimal valueDecimal = 0;
if (decimal.TryParse(drow[column.ColumnName].ToString(), out valueDecimal))
{
dataField.surfaceFieldValueDecimal = valueDecimal;
}
else //assume textbox
{
dataField.surfaceFieldValueChar = drow[column.ColumnName].ToString();
}
break;
}
fieldData.Add(dataField);
break;
}
}
}
if (fieldData.Count > 0)
{
xData.SaveTypedCollection("recId", typeof(oSurfaceFieldData), fieldData);
fieldData.Clear();
}
}
}
}
*/
#endregion
///
/// Import Data to Published Surface
///
///
///
private string ImportDataToPublishedSurfaceCAPI1SupplierPricelistParts(DataTable sourcedata, int partSurfaceId, string partSurfaceName, int historyId)
{
string emailError = "";
oUser uploadUser = new oUser();
DataTable dtHistory = new DataTable();
DataTable dtSupplierPricelist = new DataTable();
try
{
string currentDateTime = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
//get pricelist parent surface item id
int supplierPricelistItemId = 0;
bool replacePriceList = false;
string importFilename = "";
dtHistory = SQLSelectDatatable("select * from qt_SupplierPricelistImportHistory where itemID = " + historyId);
if (dtHistory != null && dtHistory.Rows.Count > 0)
{
int.TryParse(dtHistory.Rows[0]["ParentSurfaceItemId"].ToString(), out supplierPricelistItemId);
importFilename = dtHistory.Rows[0]["SupplierPricelistImportHistory_History_FileName"].ToString();
string importType = dtHistory.Rows[0]["SupplierPricelistImportHistory_History_ImportType"].ToString();
if (importType == "Replace")
replacePriceList = true;
string userEmail = dtHistory.Rows[0]["SupplierPricelistImportHistory_History_UploadUser"].ToString();
if (userEmail != "")
{
string queryUser = "select * from pal_vUserShared where email = '" + userEmail + "' and customerCode = 'CAPI-1'";
DataTable dtUser = SQLSelectDatatable(queryUser);
if (dtUser == null || dtUser.Rows.Count <= 0)
{
queryUser = queryUser.Replace("'CAPI-1'", "'EVOL-1'");
dtUser = SQLSelectDatatable(queryUser);
}
if (dtUser != null && dtUser.Rows.Count > 0)
uploadUser = (oUser)utils.ConvertDataTableToList(dtUser, typeof(oUser))[0];
}
}
if (supplierPricelistItemId <= 0)
throw new Exception("Supplier Pricelist Item Id could not be determined from Import History Id");
//get supplier pricelist details
string querySupplierPricelist = "select * from qt_SupplierPricelists where itemID = " + supplierPricelistItemId;
dtSupplierPricelist = SQLSelectDatatable(querySupplierPricelist);
string currency = "";
string leadTime = "";
string statusImport = "IMPORTED" + historyId; //dummy status to be updated later when clearing previous list - if replacing not appending
string statusPricelist = "";
string supplierName = "";
if (dtSupplierPricelist != null && dtSupplierPricelist.Rows.Count > 0)
{
supplierName = dtSupplierPricelist.Rows[0]["SupplierPricelists_SupplierPricelistDetails_SupplierTradingName"].ToString();
leadTime = dtSupplierPricelist.Rows[0]["SupplierPricelists_SupplierPricelistDetails_SupplierPricelistDefaultLeadTime"].ToString();
statusPricelist = dtSupplierPricelist.Rows[0]["SupplierPricelists_SupplierPricelistDetails_SupplierPricelistStatus"].ToString();
//if we're appending to a pricelist, use status of parent
if (!replacePriceList)
statusImport = statusPricelist;
string querySupplier = "select SupplierFinancialandCreditDetails_SupplierFinancialDetails_SupplierCurrency from qt_Suppliers where SupplierDetails_SupplierDetails_SupplierTradingName = '" + supplierName.Replace("'", "''") + "'";
DataTable dtSupplier = SQLSelectDatatable(querySupplier);
if (dtSupplier != null && dtSupplier.Rows.Count > 0)
currency = dtSupplier.Rows[0]["SupplierFinancialandCreditDetails_SupplierFinancialDetails_SupplierCurrency"].ToString();
}
DataTable dtFields = SQLSelectDatatable("select * from pal_SurfaceField where surfaceId = " + partSurfaceId + " order by sequence");
ArrayList fields = utils.ConvertDataTableToList(dtFields, typeof(oSurfaceField));
ArrayList fieldData = new ArrayList();
int counter = 0;
foreach (DataRow drow in sourcedata.Rows)
{
counter++;
//create new surface item
oSurfaceItem item = new oSurfaceItem();
item.surfaceId = partSurfaceId;
item.isActive = true;
item.isDeleted = false;
item.dateCreated = DateTime.Now;
item.createdBy = 0;
item.recId = SQLInsertSurfaceItem(item);
if (item.recId > 0)
{
//build insert query for cols
StringBuilder insertBuilder = new StringBuilder();
insertBuilder.AppendLine("INSERT INTO publ_" + partSurfaceName + " ");
insertBuilder.AppendLine("([itemID]");
insertBuilder.AppendLine(",[isActive]");
insertBuilder.AppendLine(",[surfaceId]");
foreach (DataColumn column in sourcedata.Columns)
{
foreach (oSurfaceField field in fields)
{
if (!field.surfaceFieldName.StartsWith("_"))
{
if (field.isActive)
{
if (column.ColumnName == field.surfaceFieldDisplay || column.ColumnName == field.controlText)
{
pNums.FieldType typ = (pNums.FieldType)Enum.ToObject(typeof(pNums.FieldType), field.surfaceFieldTypeId);
switch (typ)
{
case pNums.FieldType.Text://text
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Checkbox://checkbox
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Date:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Time:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Decimal:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Number:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Picklist:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.RadioButtonList:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.CheckboxList:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.MultiPicklist:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Address:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.Caption://caption
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.FormulaField:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
case pNums.FieldType.BarcodeScanner:
insertBuilder.AppendLine(",[" + field.surfaceFieldName + "]");
break;
}
break;
}
}
}
}
}
//add "system" columns not in import file
insertBuilder.AppendLine(",[ParentSurfaceItemId]");
insertBuilder.AppendLine(",[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency]");
insertBuilder.AppendLine(",[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLastUpdatedBy]");
insertBuilder.AppendLine(",[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLastUpdatedOn]");
insertBuilder.AppendLine(",[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime]");
insertBuilder.AppendLine(",[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus]");
insertBuilder.AppendLine(",[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName]");
insertBuilder.AppendLine(") VALUES (");
insertBuilder.AppendLine(item.recId.ToString());
insertBuilder.AppendLine(",1");
insertBuilder.AppendLine("," + item.surfaceId);
//if (_parentItemId > 0)
// insertBuilder.AppendLine("," + _parentItemId);
foreach (DataColumn column in sourcedata.Columns)
{
foreach (oSurfaceField field in fields)
{
if (!field.surfaceFieldName.StartsWith("_"))
{
if (field.isActive)
{
if (column.ColumnName == field.surfaceFieldDisplay || column.ColumnName == field.controlText)
{
//check the field type
pNums.FieldType typ = (pNums.FieldType)Enum.ToObject(typeof(pNums.FieldType), field.surfaceFieldTypeId);
switch (typ)
{
case pNums.FieldType.Text://text
insertBuilder.AppendLine(",'" + utils.formatSqlString(drow[column.ColumnName].ToString()).Trim() + "'");
break;
case pNums.FieldType.Checkbox://checkbox
if (drow[column.ColumnName].ToString().ToLower() == "true")
{
insertBuilder.AppendLine(",'1'");
}
else if (drow[column.ColumnName].ToString().ToLower() == "1")
{ insertBuilder.AppendLine(",'1'"); }
else if (drow[column.ColumnName].ToString().ToLower() == "yes")
{ insertBuilder.AppendLine(",'1'"); }
else
{ insertBuilder.AppendLine(",'0'"); }
break;
case pNums.FieldType.Date:
if (drow[column.ColumnName].ToString() != String.Empty)
{
if (field.surfaceDateTypeId == (int)pNums.SurfaceDateType.Year)
{
int yr = 0;
int.TryParse(drow[column.ColumnName].ToString(), out yr);
if (yr > 0)
{
DateTime myDateVal = new DateTime(yr, 1, 1);
insertBuilder.AppendLine(",'" + utils.fixDate(myDateVal) + "'");
}
else
{
insertBuilder.AppendLine(",'" + utils.fixDate(utils.formatStringToDate(drow[column.ColumnName].ToString())) + "'");
}
}
else
{
insertBuilder.AppendLine(",'" + utils.fixDate(utils.formatStringToDate(drow[column.ColumnName].ToString())) + "'");
}
}
else
insertBuilder.AppendLine(",''");
break;
case pNums.FieldType.Time:
if (drow[column.ColumnName].ToString() != String.Empty)
{
insertBuilder.AppendLine(",'" + utils.fixDate(utils.formatStringToTime(drow[column.ColumnName].ToString())));
}
break;
case pNums.FieldType.Decimal:
//CVH 2018-11-20 Force decimal separator, otherwise when running on server it bombs out with a , separator
NumberFormatInfo nfi = new NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
decimal decimalValue = 0;
decimal.TryParse(drow[column.ColumnName].ToString().Replace(" ", "").Replace("R", "").Replace("%", ""), out decimalValue);
insertBuilder.AppendLine("," + decimalValue.ToString(nfi));
break;
case pNums.FieldType.Number:
int intValue = 0;
int.TryParse(drow[column.ColumnName].ToString(), out intValue);
insertBuilder.AppendLine("," + intValue);
break;
//CVH 2019-06-06 Need to redo xData query, but field type not used, so not needed now
#region not used
//case pNums.FieldType.Picklist:
// if (field.isCustomSource)
// {
// string picklistValue = drow[column.ColumnName].ToString();
// string fieldSelect = String.Empty;
// String[] flds = field.relationalFields.Split(char.Parse(","));
// foreach (string f in flds)
// {
// if (fieldSelect == String.Empty)
// fieldSelect = f;
// else
// fieldSelect += " + ' ' + " + f;
// }
// if (field.relationalObject != String.Empty)
// {
// bool userService = field.relationalObject == "pal_User" ? true : false;
// if (userService && picklistValue.Contains(","))//handle surname, name
// {
// string surname = picklistValue.Substring(0, picklistValue.IndexOf(","));
// string name = picklistValue.Replace(" ", "").Substring(picklistValue.IndexOf(",") + 1);
// picklistValue = name + " " + surname;
// }
// string query = "Select " + field.relationalValues + " AS Value FROM " + field.relationalObject + " WHERE (" + fieldSelect + ") = '" + picklistValue + "' ";
// if (field.relationalObject == "pal_User")
// query += "AND customerCode = '" + handler.ReturnSetup().code + "'";
// DataTable pickData = xData.GetCustomTypedTable(query, userService);
// if (pickData.Rows.Count > 0)
// {
// insertBuilder.AppendLine(",'" + pickData.Rows[0][0].ToString().Replace("'", "''") + "'");
// }
// else
// {
// insertBuilder.AppendLine(",''");
// }
// }
// }
// else
// {
// //CVH lookupDisplay~reason
// string picklistValue = drow[column.ColumnName].ToString();
// if (picklistValue != null && picklistValue.Trim() != String.Empty)
// {
// string lookupDisplay = "";
// string reason = "";
// if (picklistValue.IndexOf("~") >= 0)
// {
// lookupDisplay = picklistValue.Substring(0, picklistValue.IndexOf("~"));
// reason = picklistValue.Substring(picklistValue.IndexOf("~") + 1);
// }
// else
// {
// lookupDisplay = picklistValue;
// }
// if (lookupDisplay != "")
// {
// ArrayList pickListValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + lookupDisplay);
// if (pickListValues.Count > 0)
// {
// foreach (oSurfaceLookup look in pickListValues)
// {
// insertBuilder.AppendLine("," + look.recId);
// }
// }
// else
// {
// oSurfaceLookup lookAdd = new oSurfaceLookup();
// lookAdd.categoryId = field.lookupCategory;
// lookAdd.display = lookupDisplay;
// lookAdd.isActive = true;
// lookAdd.sequence = 1;
// lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
// if (lookAdd.recId > 0)
// {
// insertBuilder.AppendLine("," + lookAdd.recId);
// }
// }
// }
// }
// }
// break;
//case pNums.FieldType.RadioButtonList:
// if (field.isCustomSource)
// {
// string picklistValue = drow[column.ColumnName].ToString();
// string fieldSelect = String.Empty;
// String[] flds = field.relationalFields.Split(char.Parse(","));
// foreach (string f in flds)
// {
// if (fieldSelect == String.Empty)
// fieldSelect = f;
// else
// fieldSelect += " + ' ' + " + f;
// }
// if (field.relationalObject != String.Empty)
// {
// bool userService = field.relationalObject == "pal_User" ? true : false;
// string query = "Select " + field.relationalValues + " AS Value FROM " + field.relationalObject + " WHERE (" + fieldSelect + ") = '" + picklistValue + "' ";
// if (field.relationalObject == "pal_User")
// query += "AND customerCode = " + handler.ReturnSetup().code;
// DataTable pickData = xData.GetCustomTypedTable(query, userService);
// if (pickData.Rows.Count > 0)
// {
// insertBuilder.AppendLine(",'" + pickData.Rows[0][0].ToString().Replace("'", "''") + "'");
// }
// else
// {
// insertBuilder.AppendLine(",''");
// }
// }
// }
// else
// {
// //CVH lookupDisplay~reason
// string radioValue = drow[column.ColumnName].ToString();
// if (radioValue != null && radioValue.Trim() != String.Empty)
// {
// string lookupDisplay = "";
// string reason = "";
// if (radioValue.IndexOf("~") >= 0)
// {
// lookupDisplay = radioValue.Substring(0, radioValue.IndexOf("~"));
// reason = radioValue.Substring(radioValue.IndexOf("~") + 1);
// }
// else
// {
// lookupDisplay = radioValue;
// }
// if (lookupDisplay != "")
// {
// ArrayList radioListValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + lookupDisplay);
// if (radioListValues.Count > 0)
// {
// foreach (oSurfaceLookup look in radioListValues)
// {
// insertBuilder.AppendLine("," + look.recId);
// }
// }
// else
// {
// oSurfaceLookup lookAdd = new oSurfaceLookup();
// lookAdd.categoryId = field.lookupCategory;
// lookAdd.display = lookupDisplay;
// lookAdd.isActive = true;
// lookAdd.sequence = 1;
// lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
// if (lookAdd.recId > 0)
// {
// insertBuilder.AppendLine("," + lookAdd.recId);
// }
// }
// }
// }
// }
// break;
//case pNums.FieldType.CheckboxList:
// if (field.isCustomSource)
// {
// string picklistValue = drow[column.ColumnName].ToString();
// string fieldSelect = String.Empty;
// String[] flds = field.relationalFields.Split(char.Parse(","));
// foreach (string f in flds)
// {
// if (fieldSelect == String.Empty)
// fieldSelect = f;
// else
// fieldSelect += " + ' ' + " + f;
// }
// if (field.relationalObject != String.Empty)
// {
// bool userService = field.relationalObject == "pal_User" ? true : false;
// string query = "Select " + field.relationalValues + " AS Value FROM " + field.relationalObject + " WHERE (" + fieldSelect + ") = '" + picklistValue + "' ";
// if (field.relationalObject == "pal_User")
// query += "AND customerCode = " + handler.ReturnSetup().code;
// DataTable pickData = xData.GetCustomTypedTable(query, userService);
// if (pickData.Rows.Count > 0)
// {
// insertBuilder.AppendLine(",'" + pickData.Rows[0][0].ToString().Replace("'", "''") + "'");
// }
// else
// {
// insertBuilder.AppendLine(",''");
// }
// }
// }
// else
// {
// //CVH lookupDisplay~reason|lookupDisplay~reason Save as lookupId~R~Reason~|~
// string checkValue = drow[column.ColumnName].ToString();
// string vals = "";
// if (checkValue != null && checkValue.Trim() != String.Empty)
// {
// foreach (string value in checkValue.Split('|'))
// {
// string lookupDisplay = "";
// string reason = "";
// if (value.IndexOf("~") >= 0)
// {
// lookupDisplay = value.Substring(0, value.IndexOf("~"));
// reason = value.Substring(value.IndexOf("~") + 1);
// }
// else
// {
// lookupDisplay = value;
// }
// if (lookupDisplay != "")
// {
// ArrayList checkListValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + lookupDisplay);
// if (checkListValues.Count > 0)
// {
// foreach (oSurfaceLookup look in checkListValues)
// {
// vals += look.recId.ToString() + "~R~" + reason + "~|~";
// }
// }
// else
// {
// oSurfaceLookup lookAdd = new oSurfaceLookup();
// lookAdd.categoryId = field.lookupCategory;
// lookAdd.display = lookupDisplay;
// lookAdd.isActive = true;
// lookAdd.sequence = 1;
// lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
// if (lookAdd.recId > 0)
// {
// vals += lookAdd.recId.ToString() + "~R~" + reason + "~|~";
// }
// }
// }
// }
// }
// insertBuilder.AppendLine(",'" + vals.Replace("'", "''") + "'");
// }
// break;
//case pNums.FieldType.MultiPicklist:
// string multiValue = drow[column.ColumnName].ToString();
// string valsMulti = "";
// if (multiValue != null && multiValue.Trim() != String.Empty)
// {
// foreach (string value in multiValue.Split('|'))
// {
// if (value != "")
// {
// ArrayList multiPicklistValues = xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceLookup), "categoryId,display", field.lookupCategory + "," + value);
// if (multiPicklistValues.Count > 0)
// {
// foreach (oSurfaceLookup look in multiPicklistValues)
// {
// if (valsMulti == "")
// valsMulti = look.recId.ToString();
// else valsMulti += "," + look.recId.ToString();
// }
// }
// else
// {
// oSurfaceLookup lookAdd = new oSurfaceLookup();
// lookAdd.categoryId = field.lookupCategory;
// lookAdd.display = value;
// lookAdd.isActive = true;
// lookAdd.sequence = 1;
// lookAdd.recId = xData.SaveTyped("recId", typeof(oSurfaceLookup), lookAdd);
// if (lookAdd.recId > 0)
// {
// if (valsMulti == "")
// valsMulti = lookAdd.recId.ToString();
// else valsMulti += "," + lookAdd.recId.ToString();
// }
// }
// }
// }
// }
// insertBuilder.AppendLine(",'" + valsMulti.Replace("'", "''") + "'");
// break;
#endregion
case pNums.FieldType.Address:
//CVH Street,Suburb,City,Province,South Africa,Postal Code. Saved as ~1~Street~|~~2~Suburb~|~ etc.
if (drow[column.ColumnName].ToString() != String.Empty)
{
string address = "";
int line = 1;
foreach (string addressLine in drow[column.ColumnName].ToString().Split(','))
{
address += "~" + line.ToString() + "~" + addressLine.Trim() + "~|~";
line++;
}
insertBuilder.AppendLine(",'" + address.Replace("'", "''") + "'");
}
break;
case pNums.FieldType.Caption://caption
insertBuilder.AppendLine(",'" + drow[column.ColumnName].ToString().Replace("'", "''").Trim() + "'");
break;
case pNums.FieldType.FormulaField:
decimal valueDecimal = 0;
if (decimal.TryParse(drow[column.ColumnName].ToString(), out valueDecimal))
{
insertBuilder.AppendLine("," + valueDecimal);
}
else //assume textbox
{
insertBuilder.AppendLine(",'" + drow[column.ColumnName].ToString().Replace("'", "''") + "'");
}
break;
case pNums.FieldType.BarcodeScanner:
insertBuilder.AppendLine(",'" + utils.formatSqlString(drow[column.ColumnName].ToString()).Trim() + "'");
break;
}
break;
}
}
}
}
}
//add "system" columns not in import file
//ParentSurfaceItemId
insertBuilder.AppendLine("," + supplierPricelistItemId + " ");
//SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency
insertBuilder.AppendLine(",'" + currency.Replace("'", "''").Trim() + "'");
//SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLastUpdatedBy
insertBuilder.AppendLine(",'" + uploadUser.userDisplay.Replace("'", "''").Trim() + "'");
//SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLastUpdatedOn
insertBuilder.AppendLine(",'" + currentDateTime + "'");
//SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime
insertBuilder.AppendLine(",'" + leadTime.Replace("'", "''").Trim() + "'");
//SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus
insertBuilder.AppendLine(",'" + statusImport.Replace("'", "''").Trim() + "'");
//SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName
insertBuilder.AppendLine(",'" + supplierName.Replace("'", "''").Trim() + "'");
insertBuilder.AppendLine(")");
//insert record
SQLInsertNoParam(insertBuilder.ToString());
}
}
//if we're replacing a pricelist, delete the old data, and update the status of the imported items to the pricelist status
if (replacePriceList)
{
string queryUpdate = "delete from pal_SurfaceItem where surfaceId = 3066 and recId IN ( " +
"select p.itemID from publ_SupplierPricelistParts p " +
"where SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus <> '" + statusImport.Replace("'", "''") + "' " +
"and ParentSurfaceItemId = " + supplierPricelistItemId + " ); " +
"delete " +
"from publ_SupplierPricelistParts " +
"where SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus <> '" + statusImport.Replace("'", "''") + "' " +
"and ParentSurfaceItemId = " + supplierPricelistItemId + "; " +
"update p set p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus = '" + statusPricelist.Replace("'", "''") + "' " +
"from publ_SupplierPricelistParts p " +
"where SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus = '" + statusImport.Replace("'", "''") + "' " +
"and ParentSurfaceItemId = " + supplierPricelistItemId + "; ";
SQLExecuteWithTransaction(queryUpdate);
}
//update history status
DateTime dtCompleted = System.DateTime.Now;
SQLUpdatePricelistImportHistoryItem(dtCompleted, historyId, "Completed");
//email notification
if (uploadUser.recId > 0)
emailError = CAPI1SupplierPricelistEmail(uploadUser, dtSupplierPricelist, dtHistory, "Completed", dtCompleted);
}
catch (Exception ex)
{
//update history item status to error
DateTime dtCompleted = System.DateTime.Now;
SQLUpdatePricelistImportHistoryItem(dtCompleted, historyId, "Error");
//email notification
if (uploadUser.recId > 0)
CAPI1SupplierPricelistEmail(uploadUser, dtSupplierPricelist, dtHistory, "Error", dtCompleted);
throw ex;
}
return emailError;
}
private string ReplaceTempTagsFromTable(string content, DataTable dt)
{
try
{
if (dt == null || dt.Rows.Count <= 0)
return content;
//replace temp tags
foreach (DataColumn col in dt.Columns)
{
string value = dt.Rows[0][col].ToString();
content = content.Replace("{" + col.ColumnName + "}", value);
}
return content;
}
catch (Exception)
{
return content;
}
}
private string CAPI1SupplierPricelistEmail(oUser user, DataTable dtSupplierPricelist, DataTable dtHistory, string status, DateTime dtDate)
{
string emailError = "";
try
{
string queryTemplate = "select * from pal_Template where templateName like '%Pricelist Import%'";
DataTable dtTemplate = SQLSelectDatatable(queryTemplate);
if (dtTemplate == null || dtTemplate.Rows.Count <= 0)
throw new Exception("No Pricelist Import template found.");
oTemplate template = (oTemplate)utils.ConvertDataTableToList(dtTemplate, typeof(oTemplate))[0];
string body = template.templateContent;
body = body.Replace("{SupplierPricelistImportHistory_History_Status}", status);
body = body.Replace("{SupplierPricelistImportHistory_History_CompleteDateTimestamp}", dtDate.ToString("yyyy-MM-dd HH:mm"));
utils.MergeHTMData(ref body, utils.BuildFieldCodeList(user, true));
body = ReplaceTempTagsFromTable(body, dtSupplierPricelist);
body = ReplaceTempTagsFromTable(body, dtHistory);
string subject = template.templateName;
subject = subject.Replace("{SupplierPricelistImportHistory_History_Status}", status);
subject = ReplaceTempTagsFromTable(subject, dtSupplierPricelist);
subject = ReplaceTempTagsFromTable(subject, dtHistory);
oEmail email = new oEmail();
email.fromAddress = _setup.communicationEmail;
email.toAddress = user.email;
email.Body = body;
email.Subject = subject;
communication.SendAnEmail(email);
}
catch (Exception ex)
{
emailError = "EMAIL ERROR: " + ex.Message;
}
return emailError;
}
private DataTable SQLSelectDatatable(string query)
{
DataTable result = new DataTable();
string connString = handler.ReturnConfigAppSetting("conn");
connString = connString.Replace("{catalog}", _dbName);
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlDataAdapter adp = new SqlDataAdapter(query, con))
{
adp.Fill(result);
}
}
return result;
}
private int SQLInsertSurfaceItem(oSurfaceItem item)
{
string connString = handler.ReturnConfigAppSetting("conn");
connString = connString.Replace("{catalog}", _dbName);
int recId = 0;
string query = "INSERT INTO [dbo].[pal_SurfaceItem] " +
" ([surfaceId] " +
" ,[dateCreated] " +
" ,[isActive] " +
" ,[createdBy] " +
" ,[updatedBy] " +
" ,[dateUpdated] " +
" ,[isDeleted] " +
" ,[isWizardCompleted] " +
" ,[lastTabCompleted] " +
" ,[userLink] " +
" ,[surfaceLinkItems]) " +
" OUTPUT INSERTED.recId " +
"VALUES " +
" (@surfaceId " +
" ,@dateCreated " +
" ,@isActive " +
" ,@createdBy " +
" ,@updatedBy " +
" ,@dateUpdated " +
" ,@isDeleted " +
" ,@isWizardCompleted " +
" ,@lastTabCompleted " +
" ,@userLink " +
" ,@surfaceLinkItems )";
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
//setting to 24 hours
cmd.CommandTimeout = 86400;
cmd.Parameters.Add(new SqlParameter("@surfaceId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@dateCreated", SqlDbType.DateTime));
cmd.Parameters.Add(new SqlParameter("@isActive", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@createdBy", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@updatedBy", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@dateUpdated", SqlDbType.DateTime));
cmd.Parameters.Add(new SqlParameter("@isDeleted", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@isWizardCompleted", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@lastTabCompleted", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@userLink", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@surfaceLinkItems", SqlDbType.NVarChar));
cmd.Parameters["@surfaceId"].Value = item.surfaceId;
cmd.Parameters["@dateCreated"].Value = item.dateCreated.Year < 1900 ? Convert.ToDateTime("1900-01-01").ToUniversalTime() : item.dateCreated.ToUniversalTime();
cmd.Parameters["@isActive"].Value = item.isActive;
cmd.Parameters["@createdBy"].Value = item.createdBy;
cmd.Parameters["@updatedBy"].Value = item.updatedBy;
cmd.Parameters["@dateUpdated"].Value = item.dateUpdated.Year < 1900 ? Convert.ToDateTime("1900-01-01").ToUniversalTime() : item.dateUpdated.ToUniversalTime();
cmd.Parameters["@isDeleted"].Value = item.isDeleted;
cmd.Parameters["@isWizardCompleted"].Value = item.isWizardCompleted;
cmd.Parameters["@lastTabCompleted"].Value = item.lastTabCompleted;
cmd.Parameters["@userLink"].Value = item.userLink;
cmd.Parameters["@surfaceLinkItems"].Value = item.surfaceLinkItems;
//cmd.Parameters.Add("@surfaceId", SqlDbType.Int).Value = item.surfaceId;
//cmd.Parameters.Add("@dateCreated", SqlDbType.DateTime).Value = item.dateCreated.ToUniversalTime();
//cmd.Parameters.Add("@isActive", SqlDbType.Bit).Value = item.isActive;
//cmd.Parameters.Add("@createdBy", SqlDbType.Int).Value = item.createdBy;
//cmd.Parameters.Add("@updatedBy", SqlDbType.Int).Value = item.updatedBy;
//cmd.Parameters.Add("@dateUpdated", SqlDbType.DateTime).Value = item.dateUpdated.ToUniversalTime();
//cmd.Parameters.Add("@isDeleted", SqlDbType.Bit).Value = item.isDeleted;
//cmd.Parameters.Add("@isWizardCompleted", SqlDbType.Bit).Value = item.isWizardCompleted;
//cmd.Parameters.Add("@lastTabCompleted", SqlDbType.Int).Value = item.lastTabCompleted;
//cmd.Parameters.Add("@userLink", SqlDbType.Int).Value = item.userLink;
//cmd.Parameters.Add("@surfaceLinkItems", SqlDbType.NVarChar).Value = item.surfaceLinkItems;
con.Open();
recId = (int)cmd.ExecuteScalar();
if (con.State == System.Data.ConnectionState.Open)
con.Close();
}
}
return recId;
}
private void SQLUpdatePricelistImportHistoryItem(DateTime datetimestamp, int itemId, string status)
{
string connString = handler.ReturnConfigAppSetting("conn");
connString = connString.Replace("{catalog}", _dbName);
string query = "update d set d.surfaceFieldValueChar = @status " +
"from pal_Surface s " +
"inner join pal_SurfaceField f on f.surfaceId = s.recId " +
"inner join pal_SurfaceFieldData d on d.surfaceFieldID = f.recId " +
"where s.name = 'SupplierPricelistImportHistory' " +
"and d.surfaceItemId = @itemId " +
"and f.surfaceFieldName = 'SupplierPricelistImportHistory_History_Status'; " +
"update d set d.surfaceFieldValueChar = '" + datetimestamp.ToString("yyyy-MM-dd HH:mm") + "' " +
"from pal_Surface s " +
"inner join pal_SurfaceField f on f.surfaceId = s.recId " +
"inner join pal_SurfaceFieldData d on d.surfaceFieldID = f.recId " +
"where s.name = 'SupplierPricelistImportHistory' " +
"and d.surfaceItemId = @itemId " +
"and f.surfaceFieldName = 'SupplierPricelistImportHistory_History_CompleteDateTimestamp'; " +
"update h " +
"set h.SupplierPricelistImportHistory_History_Status = @status, h.SupplierPricelistImportHistory_History_CompleteDateTimestamp = '" + datetimestamp.ToString("yyyy-MM-dd HH:mm") + "' " +
"from qt_SupplierPricelistImportHistory h " +
"where h.itemID = @itemId ";
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
//setting to 24 hours
cmd.CommandTimeout = 86400;
cmd.Parameters.Add(new SqlParameter("@itemId", SqlDbType.Int));
cmd.Parameters["@itemId"].Value = itemId;
cmd.Parameters.Add(new SqlParameter("@status", SqlDbType.NVarChar));
cmd.Parameters["@status"].Value = status;
con.Open();
cmd.ExecuteNonQuery();
if (con.State == System.Data.ConnectionState.Open)
con.Close();
}
}
}
private void SQLInsertNoParam(string query)
{
string connString = handler.ReturnConfigAppSetting("conn");
connString = connString.Replace("{catalog}", _dbName);
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
//setting to 24 hours
cmd.CommandTimeout = 86400;
con.Open();
cmd.ExecuteNonQuery();
if (con.State == System.Data.ConnectionState.Open)
con.Close();
}
}
}
private void SQLExecuteWithTransaction(string query)
{
string connString = handler.ReturnConfigAppSetting("conn");
connString = connString.Replace("{catalog}", _dbName);
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
//setting to 24 hours
cmd.CommandTimeout = 86400;
con.Open();
using (SqlTransaction trn = con.BeginTransaction())
{
try
{
cmd.Connection = con;
cmd.Transaction = trn;
cmd.ExecuteNonQuery();
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
throw ex;
}
if (con.State == System.Data.ConnectionState.Open)
con.Close();
}
}
}
}
}
}