private void AddFileFieldControl(string field)
{
Label lblField = new Label {Width = 120, Text = field};
ComboBox cbo = new ComboBox {Width = 140};
StringCollection propertyList = new Mapper().GetPaymentProperties();
//add payment properties to second list
foreach (string str in propertyList)
{
cbo.Items.Add(str);
if (field.ToLower() == str.ToLower())
{
cbo.SelectedItem = str;
}
}
flowLayoutPanel1.Controls.Add(lblField);
flowLayoutPanel1.Controls.Add(cbo);
flowLayoutPanel1.SetFlowBreak(cbo, true);
}
private DataTable ReadPaymentsFromFile(Mapper mapper)
{
string fileName = Path.GetFileName(txtExcelFilename.Text);
DataTable dt = new DataTable();
// check for Excel office version -- old versions
if (fileName.ToLower().EndsWith(".xls"))
{
FileStream stream = new FileStream(txtExcelFilename.Text, FileMode.Open);
ExcelDataReader excelReader
= new ExcelDataReader(stream);
DataSet ds = excelReader.GetDataSet();
dt = ds.Tables[0];
//this stores the mapped column names to the column names in Excel
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
if (mapper.ContainsKey(ds.Tables[0].Rows[0][i].ToString()))
{
dt.Columns[i].ColumnName = (string)ds.Tables[0].Rows[0][i];
}
}
stream.Close();
}
else
{
// Office 2007 file parser
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + txtExcelFilename.Text + ";" +
"Extended Properties=\"Excel 12.0;HDR=YES;\"";
// if you don't want to show the header row (first row)
// use 'HDR=NO' in the string
string oleSql = "SELECT * FROM [{0}]";
DataSet ds = new DataSet();
using (OleDbConnection oleCnn = new OleDbConnection(connectionString))
{
oleCnn.Open();
DataTable schema = oleCnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheet1Name = schema.Rows[0]["TABLE_NAME"].ToString();
oleSql = String.Format(oleSql, sheet1Name);
using (OleDbDataAdapter oleDA = new OleDbDataAdapter(oleSql, oleCnn))
{
oleDA.Fill(ds);
}
}
dt = ds.Tables[0];
}
return dt;
}
private void LogMappingResults(Mapper mapper)
{
//displays the mapping results
StringBuilder sb = new StringBuilder();
sb.AppendLine("Mapping Results:");
foreach (DictionaryEntry de in mapper)
{
sb.AppendFormat("Excel Column: {0}, Payment Property: {1};", de.Key, de.Value);
sb.AppendLine();
}
_log.WriteLog(sb.ToString());
_log.WriteLog("");
}
private Mapper CreateMapperObject()
{
Mapper mapper = new Mapper();
string labelValue = string.Empty;
string comboValue = string.Empty;
foreach (Control ctl in flowLayoutPanel1.Controls)
{
Type ctlType = ctl.GetType();
if (ctlType == typeof(Label))
{
labelValue = ctl.Text;
}
else if (ctlType == typeof(ComboBox))
{
comboValue = ctl.Text;
}
if (labelValue != string.Empty && comboValue != string.Empty && comboValue != string.Empty)
{
mapper.Add(labelValue, comboValue);
labelValue = string.Empty;
comboValue = string.Empty;
}
}
return mapper;
}
/// <summary>
/// Adds a list of payments to the BulkPayment object
/// </summary>
/// <param name="dt"></param>
/// <param name="mapper"></param>
/// <param name="deleteFirstRow"></param>
private void AddPayments(DataTable dt, Mapper mapper, bool deleteFirstRow)
{
//add each payment to bulkPayment object
if (deleteFirstRow)
{
//Deletes the first row with the column names
dt.Rows[0].Delete();
}
//stores the data in the first sheet to the bulkpayment.Payments collection
foreach (DataRow row in dt.Rows)
{
IPayment payment = AddPayment();
try
{
foreach (DataColumn column in dt.Columns)
{
if (mapper.ContainsKey(column.ColumnName))
{payment.SetProperty(mapper[column.ColumnName], row[column].ToString());}
}
}
catch
{
//MessageBox.Show(ex.Message);
RemovePayment(payment);
//add to Dropped Payments
_droppedPayments.Add(payment);
_sumDroppedPayments += payment.Amount;
_countDroppedPayments += 1;
}
}
}
/// <summary>
/// Adds Payments to Payments list; Verifies payments; Identifies Claims; and Identifies Transaction Types
/// </summary>
/// <param name="table"></param>
/// <param name="mapper"></param>
/// <param name="deleteFirstRow"></param>
public void ProcessPayments(DataTable table, Mapper mapper, bool deleteFirstRow)
{
AddPayments(table, mapper, deleteFirstRow);
VerifyPayments();
IdentifyClaims();
IdentifyTransactionTypes();
}
