/// <summary>
/// read the Csv File and fill the DataTable in the DataSet
/// </summary>
/// <param name="dataSet"></param>
/// <param name="dataTableName"></param>
/// <returns></returns>
public int Fill(DataSet dataSet, string dataTableName = "Table")
{
DataTable dt = new DataTable(dataTableName);
dataSet.Tables.Add(dt);
int fieldCount = reader.FieldCount;
string[] headers = reader.GetFieldHeaders();
foreach (string s in headers)
{
dt.Columns.Add(s);
}
int nbRows = 0;
while (reader.ReadNextRecord())
{
DataRow row = dt.NewRow();
for (int i = 0; i < fieldCount; i++)
{
row.SetField(headers[i], reader[i]);
}
dt.Rows.Add(row);
nbRows++;
}
return(nbRows);
}
// Declaration of ReadRecords Method
/// <summary>
/// Catches all type of exceptions generated.
/// </summary>
/// <param name="passHeader"></param>
/// <param name="in_delimeter"></param>
/// <param name="filePath"></param>
/// <returns></returns>
public object ReadRecords(string[] passHeader = null, char in_delimeter = ',', string filePath = null)
{
try
{
if (!filePath.Contains(".csv"))
{
throw new CensusAnalyserException(CensusAnalyserException.ExceptionType.INVALID_EXTENSION_OF_FILE, "Invalid Extension of file");
}
else if (!filePath.Contains(actualPath))
{
throw new CensusAnalyserException(CensusAnalyserException.ExceptionType.FILE_NOT_FOUND, "Invalid file");
}
// streams are used to read/write data from csv files
//CsvReader is open source C# library to read CSV data from strings/textFiles
CsvReader csvRecords = new CsvReader(new StreamReader(filePath), true);
int fieldCount = csvRecords.FieldCount;
string[] headers = csvRecords.GetFieldHeaders();
delimeter = csvRecords.Delimiter;
// string ArrayList
List <string[]> record = new List <string[]>();
while (csvRecords.ReadNextRecord())
{
string[] tempRecord = new string[fieldCount];
csvRecords.CopyCurrentRecordTo(tempRecord);
record.Add(tempRecord);
numberOfRecord++;
}
if (numberOfRecord == 0)
{
throw new CSVException(CSVException.ExceptionType.FILE_IS_EMPTY, "This file does not contains any data");
}
if (!in_delimeter.Equals(delimeter))
{
throw new CensusAnalyserException(CensusAnalyserException.ExceptionType.INCORRECT_DELIMETER, "Incorrect Delimeter");
}
else if (!IsHeaderSame(passHeader, headers))
{
throw new CensusAnalyserException(CensusAnalyserException.ExceptionType.INVALID_HEADER_ERROR, "Invalid Header");
}
return(numberOfRecord);
}
catch (CensusAnalyserException file_not_found)
{
return(file_not_found.Message);
}
catch (CSVException emptyFileException)
{
return(emptyFileException.Message);
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
//private void OnConfiguration(object sender, RoutedEventArgs e)
//{
//}
private void OnImportInventory(object sender, RoutedEventArgs e)
{
OpenFileDialog diag = new OpenFileDialog();
diag.Title = "Select File for Import";
diag.Filter = "Comma Delimited|*.csv|All Files|*.*";
diag.CheckFileExists = true;
diag.CheckPathExists = true;
diag.DefaultExt = "csv";
diag.Multiselect = false;
if (diag.ShowDialog() == true)
{
using (StreamReader sr = new StreamReader(diag.FileName))
{
LumenWorks.Framework.IO.Csv.CsvReader csv = new LumenWorks.Framework.IO.Csv.CsvReader(sr, true);
int fieldCount = csv.FieldCount;
string[] headers = csv.GetFieldHeaders();
while (csv.ReadNextRecord())
{
ActiveInventoryObject Inv = new ActiveInventoryObject();
int i = 0;
Inv.UPC = csv[0];
Inv.Distributor = csv[1];
Inv.SKU = csv[2];
Inv.Description = csv[3];
Inv.Category = csv[4];
if (int.TryParse(csv[5], out i))
{
Inv.Quantity = i;
}
Inv.WholeSalePrice = Configuration.CurrencyToDecimal(csv[6]);
Inv.MSRP = Configuration.CurrencyToDecimal(csv[7]);
Inv.AdditionalOverhead = Configuration.CurrencyToDecimal(csv[8]);
Inv.DescriptionShort = Inv.Description;
Inv.Manufacturer = csv[14];
Cache.Current.Inventory.Add(Inv);
}
}
MessageBox.Show("Process Complete.");
}
}
//private void OnConfiguration(object sender, RoutedEventArgs e)
//{
//}
private void OnImportInventory(object sender, RoutedEventArgs e)
{
OpenFileDialog diag = new OpenFileDialog();
diag.Title = "Select File for Import";
diag.Filter = "Comma Delimited|*.csv|All Files|*.*";
diag.CheckFileExists = true;
diag.CheckPathExists = true;
diag.DefaultExt = "csv";
diag.Multiselect = false;
if (diag.ShowDialog() == true)
{
using (StreamReader sr = new StreamReader(diag.FileName))
{
LumenWorks.Framework.IO.Csv.CsvReader csv = new LumenWorks.Framework.IO.Csv.CsvReader(sr, true);
int fieldCount = csv.FieldCount;
string[] headers = csv.GetFieldHeaders();
while (csv.ReadNextRecord())
{
ActiveInventoryObject Inv = new ActiveInventoryObject();
int i = 0;
Inv.UPC = csv[0];
Inv.Distributor = csv[1];
Inv.SKU = csv[2];
Inv.Description = csv[3];
Inv.Category = csv[4];
if (int.TryParse(csv[5], out i))
{
Inv.Quantity = i;
}
Inv.WholeSalePrice = Configuration.CurrencyToDecimal(csv[6]);
Inv.MSRP = Configuration.CurrencyToDecimal(csv[7]);
Inv.AdditionalOverhead = Configuration.CurrencyToDecimal(csv[8]);
Inv.DescriptionShort = Inv.Description;
Inv.Manufacturer = csv[14];
Cache.Current.Inventory.Add(Inv);
}
}
MessageBox.Show("Process Complete.");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request["testing"] == "true")
return;
//fdata.DataSource = csv;
//fdata.DataBind();
List<PayPalRecord> PayPalRecords = new List<PayPalRecord>();
decimal totalfees = Decimal.Parse("0.0");
decimal totalgross = Decimal.Parse("0.0");
decimal totalnet = Decimal.Parse("0.0");
string text = "";
foreach (string directory in Directory.GetDirectories(@"W:\Projects\Weavver\Accounting\Paypal"))
{
foreach (string file in Directory.GetFiles(directory))
{
Response.Write(file);
if (!file.EndsWith(".txt"))
continue;
LumenWorks.Framework.IO.Csv.CsvReader csv = new LumenWorks.Framework.IO.Csv.CsvReader(new StreamReader(file), true, '\t');
int fieldCount = csv.FieldCount;
string[] headers = csv.GetFieldHeaders();
decimal fees = Decimal.Parse("0.0");
decimal gross = Decimal.Parse("0.0");
decimal net = Decimal.Parse("0.0");
while (csv.ReadNextRecord())
{
string itemtitle = null;
try
{
itemtitle = csv["Item Title"];
}
catch (Exception ex)
{
ShowError(ex, "Import error");
continue;
}
if (itemtitle == "Snap Pro")
{
PayPalRecord item = new PayPalRecord();
item.Date = csv["Date"];
item.Time = csv["Time"];
item.TimeZone = csv["Time Zone"];
item.Name = csv["Name"];
item.Type = csv["Type"];
item.Status = csv["Status"];
item.Currency = csv["Currency"];
item.Gross = csv["Gross"];
item.Net = csv["Net"];
item.FromEmailAddress = csv["From Email Address"];
item.ToEmailAddress = csv["To Email Address"];
item.TransactionID = csv["Transaction ID"];
item.CounterpartyStatus = csv["Counterparty Status"];
item.ShippingAddress = csv["Shipping Address"];
item.AddressStatus = csv["Address Status"];
item.ItemTitle = csv["Item Title"];
item.ItemID = csv["Item ID"];
item.ShippingandHandlingAmount = Decimal.Parse(csv["Shipping and Handling Amount"]);
item.InsuranceAmount = csv["Insurance Amount"];
item.SalesTax = Decimal.Parse(csv["Sales Tax"]);
item.Option1Name = csv["Option 1 Name"];
item.Option1Value = csv["Option 1 Value"];
item.Option2Name = csv["Option 2 Name"];
item.Option2Value = csv["Option 2 Value"];
item.AuctionSite = csv["Auction Site"];
item.BuyerID = csv["Buyer ID"];
item.ReferenceTxnID = csv["Reference Txn ID"];
item.InvoiceNumber = csv["Invoice Number"];
item.CustomNumber = csv["Custom Number"];
item.ReceiptID = csv["Receipt ID"];
item.Balance = csv["Balance"];
item.ContactPhoneNumber = csv["Contact Phone Number"];
item.BalanceImpact = csv["Balance Impact"];
PayPalRecords.Add(item);
fees += Decimal.Parse(csv["Fee"]);
gross += Decimal.Parse(csv["Gross"]);
net += Decimal.Parse(csv["Net"]);
continue;
}
}
text += "<hr />";
totalfees += fees;
totalgross += gross;
totalnet += net;
}
}
fdata.DataSource = PayPalRecords;
//for (int i = 0; i < PayPalRecords.Count; i++)
//{
// PayPalRecords[i].Commit();
//}
//fdata.DataBind();
//FileData.Text = text;
//totalfees = -1 * totalfees;
//Fees.Text = "Fees: $" + totalfees.ToString();
//Totals.Text = "Gross: $" + totalgross.ToString();
//Net.Text = "Net: $" + totalnet.ToString();
//Net.Text += "<br>Total Records: " + PayPalRecords.Count;
//Console.ReadLine();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request["testing"] == "true")
{
return;
}
//fdata.DataSource = csv;
//fdata.DataBind();
List <PayPalRecord> PayPalRecords = new List <PayPalRecord>();
decimal totalfees = Decimal.Parse("0.0");
decimal totalgross = Decimal.Parse("0.0");
decimal totalnet = Decimal.Parse("0.0");
string text = "";
foreach (string directory in Directory.GetDirectories(@"W:\Projects\Weavver\Accounting\Paypal"))
{
foreach (string file in Directory.GetFiles(directory))
{
Response.Write(file);
if (!file.EndsWith(".txt"))
{
continue;
}
LumenWorks.Framework.IO.Csv.CsvReader csv = new LumenWorks.Framework.IO.Csv.CsvReader(new StreamReader(file), true, '\t');
int fieldCount = csv.FieldCount;
string[] headers = csv.GetFieldHeaders();
decimal fees = Decimal.Parse("0.0");
decimal gross = Decimal.Parse("0.0");
decimal net = Decimal.Parse("0.0");
while (csv.ReadNextRecord())
{
string itemtitle = null;
try
{
itemtitle = csv["Item Title"];
}
catch (Exception ex)
{
ShowError(ex, "Import error");
continue;
}
if (itemtitle == "Snap Pro")
{
PayPalRecord item = new PayPalRecord();
item.Date = csv["Date"];
item.Time = csv["Time"];
item.TimeZone = csv["Time Zone"];
item.Name = csv["Name"];
item.Type = csv["Type"];
item.Status = csv["Status"];
item.Currency = csv["Currency"];
item.Gross = csv["Gross"];
item.Net = csv["Net"];
item.FromEmailAddress = csv["From Email Address"];
item.ToEmailAddress = csv["To Email Address"];
item.TransactionID = csv["Transaction ID"];
item.CounterpartyStatus = csv["Counterparty Status"];
item.ShippingAddress = csv["Shipping Address"];
item.AddressStatus = csv["Address Status"];
item.ItemTitle = csv["Item Title"];
item.ItemID = csv["Item ID"];
item.ShippingandHandlingAmount = Decimal.Parse(csv["Shipping and Handling Amount"]);
item.InsuranceAmount = csv["Insurance Amount"];
item.SalesTax = Decimal.Parse(csv["Sales Tax"]);
item.Option1Name = csv["Option 1 Name"];
item.Option1Value = csv["Option 1 Value"];
item.Option2Name = csv["Option 2 Name"];
item.Option2Value = csv["Option 2 Value"];
item.AuctionSite = csv["Auction Site"];
item.BuyerID = csv["Buyer ID"];
item.ReferenceTxnID = csv["Reference Txn ID"];
item.InvoiceNumber = csv["Invoice Number"];
item.CustomNumber = csv["Custom Number"];
item.ReceiptID = csv["Receipt ID"];
item.Balance = csv["Balance"];
item.ContactPhoneNumber = csv["Contact Phone Number"];
item.BalanceImpact = csv["Balance Impact"];
PayPalRecords.Add(item);
fees += Decimal.Parse(csv["Fee"]);
gross += Decimal.Parse(csv["Gross"]);
net += Decimal.Parse(csv["Net"]);
continue;
}
}
text += "<hr />";
totalfees += fees;
totalgross += gross;
totalnet += net;
}
}
fdata.DataSource = PayPalRecords;
//for (int i = 0; i < PayPalRecords.Count; i++)
//{
// PayPalRecords[i].Commit();
//}
//fdata.DataBind();
//FileData.Text = text;
//totalfees = -1 * totalfees;
//Fees.Text = "Fees: $" + totalfees.ToString();
//Totals.Text = "Gross: $" + totalgross.ToString();
//Net.Text = "Net: $" + totalnet.ToString();
//Net.Text += "<br>Total Records: " + PayPalRecords.Count;
//Console.ReadLine();
}
