internal void CalculateColumnIndex(string[] dataHeaders)
{
if (string.IsNullOrEmpty(ColumnName))
{
ColumnIndex = -1;
}
if (mapType == ColumnMapType.ExcelHeader)
{
string temp = ColumnName.Trim().ToUpper();
if (temp.Length == 2)
{
//(temp[0] - 65 + 1) * 26 + (temp[1] - 65);
ColumnIndex = (temp[0] - 64) * 26 + (temp[1] - 65);
}
else
{
ColumnIndex = temp[0] - 65;
}
}
else
{
ColumnIndex = Array.IndexOf <string>(dataHeaders, ColumnName.Trim());
}
}
private void DefaultValueToAllNulls()
{
string strMsg = "";
if (Type.Trim().ToUpper() != "SCENARIO")
{
strMsg = "All rows for treatment " + Type + " component " + ColumnName + "\r\n" +
"will be assigned the value " + this.txtCubicFootDollarValue.Text.Trim() + " if the current value is null.\r\n" +
"Do you wish to update the table rows with the default value?(Y/N)";
}
else
{
strMsg = "All rows for component " + ColumnName + "\r\n" +
"will be assigned the value " + this.txtCubicFootDollarValue.Text.Trim() + " if the current value is null.\r\n" +
"Do you wish to update the table rows with the default value?(Y/N)";
}
DialogResult result = MessageBox.Show(strMsg, "FIA Biosum", System.Windows.Forms.MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
frmMain.g_oFrmMain.ActivateStandByAnimation(
this.ParentForm.WindowState,
this.ParentForm.Left,
this.ParentForm.Height,
this.ParentForm.Width,
this.ParentForm.Top);
this.ReferenceAdo.m_strSQL = "UPDATE additional_harvest_costs_work_table " +
"SET " + ColumnName.Trim() + "=" + this.txtCubicFootDollarValue.Text.Replace("$", "");
if (Type.Trim().ToUpper() != "SCENARIO")
{
this.ReferenceAdo.m_strSQL = ReferenceAdo.m_strSQL + " WHERE rx='" + Type.Trim() + "' AND " + ColumnName.Trim() + " IS NULL";
}
else
{
this.ReferenceAdo.m_strSQL = ReferenceAdo.m_strSQL + " WHERE " + ColumnName.Trim() + " IS NULL";
}
frmMain.g_sbpInfo.Text = "Updating Harvest Cost Component $/A/C Values...Stand By";
this.ReferenceAdo.SqlNonQuery(ReferenceOleDbConnection, ReferenceAdo.m_strSQL);
frmMain.g_sbpInfo.Text = "Ready";
if (this.ReferenceAdo.m_intError == 0)
{
this.ReferenceAdditionalHarvestCostColumnsUserControl.UpdateNullCounts();
this.ReferenceAdditionalHarvestCostColumnsUserControl.ReferenceProcessorScenarioForm.m_bSave = true;
frmMain.g_oFrmMain.DeactivateStandByAnimation();
MessageBox.Show("Done");
}
else
{
frmMain.g_oFrmMain.DeactivateStandByAnimation();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="FileEntry"></param>
/// <param name="FileEntry"></param>
/// <returns></returns>
public static CSVFileEntry ReadCSVFileColumnNames(CSVFileEntry FileEntry)
{
// 2. Read the header line of the CSV File.
StreamReader CSVFileReader = new StreamReader(FileEntry.HeaderPath);
string HeaderRow = CSVFileReader.ReadLine();
// 3. Extract the comma-separated columns names of the CSV File from its header line.
// Strip empty spaces around column names.
List <string> ColumnNames = new List <string>(HeaderRow.Split(','));
FileEntry.ColumnNames = new List <string>();
foreach (string ColumnName in ColumnNames)
{
FileEntry.ColumnNames.Add(ColumnName.Trim());
}
CSVFileReader.Close();
return(FileEntry);
}
public override string ToString()
{
return($"{ColumnName.Trim()} {SortType.ToString()}");
}
string ITablePart.GetSql(bool newTable)
{
if (OldColumn != null &&
OldColumn.ColumnName != null &&
ObjectHelper.AreEqual(this, OldColumn))
{
return(null);
}
if (String.IsNullOrEmpty(ColumnName))
{
return(null);
}
StringBuilder props = new StringBuilder();
props.AppendFormat(" {0}", DataType);
if (!String.IsNullOrEmpty(CharacterSet))
{
props.AppendFormat(" CHARACTER SET '{0}'", CharacterSet);
}
if (!String.IsNullOrEmpty(Collation))
{
props.AppendFormat(" COLLATE '{0}'", Collation);
}
if (!String.IsNullOrEmpty(Comment))
{
props.AppendFormat(" COMMENT '{0}'", Comment);
}
if (IsZerofill)
{
props.Append(" ZEROFILL");
}
if (IsUnsigned)
{
props.Append(" UNSIGNED");
}
if (!AllowNull)
{
props.Append(" NOT NULL");
}
if (AutoIncrement)
{
props.Append(" AUTO_INCREMENT");
}
if (!String.IsNullOrEmpty(DefaultValue))
{
if (DataType.IndexOf("char") >= 0)
{
props.AppendFormat(" DEFAULT '{0}'", DefaultValue);
}
else
{
props.AppendFormat(" DEFAULT {0}", DefaultValue);
}
}
ColumnName = ColumnName.Trim();
if (newTable)
{
return(String.Format("`{0}`{1}", ColumnName, props.ToString()));
}
if (isNew)
{
return(String.Format("ADD `{0}`{1}", ColumnName, props.ToString()));
}
return(String.Format("CHANGE `{0}` `{1}` {2}",
OldColumn.ColumnName, ColumnName, props.ToString()));
}
public static void TranslateRecordStatusDescription(List <string> ColumnNameList, ref DataTable IncomingDataTable)
{
//flag to see if all column required is in
//assuming it is ok
bool ColumnNameFound = true;
foreach (string ColumnName in ColumnNameList)
{
if (!IncomingDataTable.Columns.Contains(ColumnName.Trim() + "InFull"))
{
//add in the display name column if not found
IncomingDataTable.Columns.Add(ColumnName.Trim() + "InFull");
}
if (!IncomingDataTable.Columns.Contains(ColumnName))
{
ColumnNameFound = false;
}
}
if (ColumnNameFound)
{
//loop through all the record
foreach (DataRow MyRow in IncomingDataTable.Rows)
{
foreach (string ColumnName in ColumnNameList)
{
//loop through all the column
if (MyRow[ColumnName] != DBNull.Value)
{
string RecordStatus = Convert.ToString(MyRow[ColumnName]).Trim().ToUpper();
if (RecordStatus.Equals("A"))
{
MyRow[ColumnName + "InFull"] = "Active";
}
else if (RecordStatus.Equals("R"))
{
MyRow[ColumnName + "InFull"] = "Renting";
}
else if (RecordStatus.Equals("I"))
{
MyRow[ColumnName + "InFull"] = "InActive";
}
else if (RecordStatus.Equals("E"))
{
MyRow[ColumnName + "InFull"] = "Expired";
}
else if (RecordStatus.Equals("S"))
{
MyRow[ColumnName + "InFull"] = "Successful";
}
else if (RecordStatus.Equals("M"))
{
MyRow[ColumnName + "InFull"] = "Maintenance";
}
else if (RecordStatus.Equals("N"))
{
MyRow[ColumnName + "InFull"] = "Not Returned";
}
else if (RecordStatus.Equals("O"))
{
MyRow[ColumnName + "InFull"] = "Out Of Stocked";
}
else
{
MyRow[ColumnName + "InFull"] = "Unknown";
}
}
else
{
MyRow[ColumnName + "InFull"] = "Unknown";
}
}
}
}
}
public List <CCBTransaction> ParseCSV(string fileLocation, bool useImportFormat)
{
List <CCBTransaction> TransactionSet = new List <CCBTransaction>();
Dictionary <string, int> ColumnMapping = new Dictionary <string, int>();
if (!File.Exists(fileLocation))
{
throw new IOException(string.Format("CSV file does not exist: {0}", fileLocation));
}
TransactionSet.Clear();
string FullFile = File.ReadAllText(fileLocation);
bool HaveHeaders = false;
string AccountID = string.Empty;
int LineIndex = 0;
bool UseLFonly = FullFile.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries).Length
< FullFile.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries).Length;
foreach (string RawLine in FullFile.Split(
UseLFonly ? new string[] { "\n" } : new string[] { "\r\n" },
StringSplitOptions.RemoveEmptyEntries))
{
LineIndex++;
if (RawLine.Length == 0)
{
continue;
}
if (!HaveHeaders)
{
string[] ColumnNames = ParseLine(RawLine);
int Offset = 0;
foreach (string ColumnName in ColumnNames)
{
ColumnMapping.Add(ColumnName.Trim(), Offset++);
}
HaveHeaders = true;
}
else
{
try
{
string[] ColumnValues = ParseLine(RawLine);
if (useImportFormat)
{
TransactionSet.Add(
new CCBTransaction(
IDcounter++,
DateTime.Parse(ColumnValues[ColumnMapping["Date"]]),
DoubleOrNothing(ColumnValues[ColumnMapping["Amount"]]),
ColumnValues[ColumnMapping["Description"]],
ColumnValues[ColumnMapping["Category"]],
ColumnValues[ColumnMapping["Account"]],
ColumnValues[ColumnMapping["Jive"]] == "yes" ? true : false,
ColumnValues[ColumnMapping["Check"]],
ColumnValues[ColumnMapping["Payee"]],
ColumnValues[ColumnMapping["Memo"]],
string.Empty
));
}
else
{
TransactionSet.Add(
new CCBTransaction(
IDcounter++,
DateTime.Parse(ColumnValues[ColumnMapping["Date(MM/DD/YYYY)"]]),
DoubleOrNothing(ColumnValues[ColumnMapping["Amount"]]),
ColumnValues[ColumnMapping["Description"]],
ColumnValues[ColumnMapping["Category"]],
ColumnValues[ColumnMapping["Account"]],
ColumnValues[ColumnMapping["Jived"]] == "yes" ? true : false,
ColumnValues[ColumnMapping["Check Number"]],
ColumnValues[ColumnMapping["Payee"]],
ColumnValues[ColumnMapping["Memo"]],
ColumnValues[ColumnMapping["User"]]
));
}
}
catch (Exception e1)
{
Console.WriteLine("Error parsing line #{0}:\r\n{1}\r\n\r\n{2}", LineIndex, RawLine,
DetailedException.WithEnteriseContent(ref e1));
throw;
}
}
}
return(TransactionSet);
}
