using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Collections; using framework_business; using framework_library; using System.Configuration; using System.Globalization; namespace dataImporter { public partial class frmSurfaceDataImport : Form { public frmSurfaceDataImport() { InitializeComponent(); backgroundWorker1.WorkerReportsProgress = true; } /// /// 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++; backgroundWorker1.ReportProgress(2, "Processing row " + counter + "..."); backgroundWorker1.ReportProgress(0, counter + " of " + sourcedata.Rows.Count); //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) { backgroundWorker1.ReportProgress(2, "SQL Error: " + insertBuilder.ToString()); 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++; backgroundWorker1.ReportProgress(2, "Processing row " + counter + "..."); backgroundWorker1.ReportProgress(0, counter + " of " + sourcedata.Rows.Count); //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; } } } // backgroundWorker1.ReportProgress(1, "Field data count is " + fieldData.Count + "."); if (fieldData.Count > 0) { xData.SaveTypedCollection("recId", typeof(oSurfaceFieldData), fieldData); fieldData.Clear(); //backgroundWorker1.ReportProgress(1, "Field data saved."); } } // backgroundWorker1.ReportProgress(2, "Row " + counter + " has been imported."); } } private void lnkSelectFile_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) { try { lnkSelectFile.Enabled = false; if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK) { lblSelectedFile.Text = openFileDialog1.FileName; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Surface data import", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { lnkSelectFile.Enabled = true; } } private void btnImport_Click(object sender, EventArgs e) { try { btnImport.Enabled = false; txtProgress.Text = ""; lblProgressRow.Text = "0"; backgroundWorker1.RunWorkerAsync(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Surface data import", MessageBoxButtons.OK, MessageBoxIcon.Error); } } private void frmSurfaceDataImport_Load(object sender, EventArgs e) { try { string Database = ConfigurationSettings.AppSettings["conn"]; if (Database.IndexOf("Initial Catalog") >= 0) Database = Database.Substring(Database.IndexOf("Initial Catalog")); if (Database.IndexOf(";") > 0) Database = Database.Substring(0, Database.IndexOf(";")); Database = Database.Replace("Initial Catalog=", ""); txtInstance.Text = Database; } catch (Exception ex) { MessageBox.Show(ex.Message, "Surface data import", MessageBoxButtons.OK, MessageBoxIcon.Error); } } private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { string surfaceName = txtSurface.Text; foreach (oSurface surf in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurface), "name", surfaceName)) { backgroundWorker1.ReportProgress(1, "Starting data import into surface " + surf.name + "."); DataTable importData = utils.getDataTableFromExcel(lblSelectedFile.Text); backgroundWorker1.ReportProgress(2, "Rows to import: " + importData.Rows.Count); if (importData.Rows.Count > 0)//we have records { if (surf.isPublished) { ImportDataToPublishedSurface(importData, surf.recId, surf.name); } else { ImportDataToSurface(importData, surf.recId); //backgroundWorker1.ReportProgress(2, "Recreating Query Table."); ////create query table //List paramList = new List(); //oDynamicParam param1 = new oDynamicParam(); //param1.paramDisplayName = "SurfaceID"; //param1.paramObject = surf.recId; //paramList.Add(param1); //oDynamicParam param2 = new oDynamicParam(); //param2.paramDisplayName = "UserLink"; //param2.paramObject = 0; //paramList.Add(param2); //oDynamicParam param3 = new oDynamicParam(); //param3.paramDisplayName = "UserId"; //param3.paramObject = 216; //paramList.Add(param3); //xData.ExecuteProc("sp_CreateSurfaceQueryTable", paramList); } } } } private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e) { if (e.ProgressPercentage == 0) { lblProgressRow.Text = e.UserState.ToString(); lblProgressRow.Refresh(); } else { txtProgress.AppendText(e.UserState.ToString()); txtProgress.AppendText(Environment.NewLine); if (e.ProgressPercentage == 2) txtProgress.AppendText(Environment.NewLine); txtProgress.Refresh(); } } private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) { btnImport.Enabled = true; if (e.Error != null) { txtProgress.AppendText(e.Error.Message); txtProgress.AppendText(Environment.NewLine); txtProgress.AppendText(Environment.NewLine); txtProgress.Refresh(); MessageBox.Show(e.Error.Message, "Surface data import", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { txtProgress.AppendText("Import Complete."); txtProgress.AppendText(Environment.NewLine); txtProgress.AppendText(Environment.NewLine); txtProgress.Refresh(); MessageBox.Show("Data import complete. If this is not a published surface, please run the script to recreate the QT table manually after all the data for this surface has been imported.", "Surface data import", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } }