public static Refund RefundFound(EbayTransaction originalPurchase, int refundTransactionIndex)
{
Console.WriteLine("Found a refund for item " + originalPurchase.itemName);
if (debug)
{
Console.WriteLine("Original name was: " + payPalReports[PPRindexbyRename[originalPurchase.itemName]].Name);
}
string usdValue = "";
if (originalPurchase.originalCurrency == "USD")
{
usdValue = payPalReports[refundTransactionIndex].Gross;
}
else
{
usdValue = GetUsdValue(refundTransactionIndex).ToString();
}
return(new Refund(usdValue, payPalReports[refundTransactionIndex].Date + payPalReports[refundTransactionIndex].Time));
}
static void Main(string[] args)
{
Console.WriteLine("hello, this program will take a report from paypal in csv format,");
Console.WriteLine("and then take all ebay transactions and insert them into a spreadsheet");
Console.WriteLine("with costs translated into the currency of your paypal account, press enter");
string launchOption = Console.ReadLine();
if (launchOption == "debug")
{
debug = true;
}
Console.WriteLine("Please type in the name of the excel spreadsheet you wish to create");
string spreadsheetName = Console.ReadLine();
FileInfo excelFile = new FileInfo(Directory.GetCurrentDirectory() + Path.DirectorySeparatorChar + spreadsheetName + ".xlsx");
ExcelPackage excelPackage = new ExcelPackage(excelFile);
string worksheetName = "eBay";
if (excelPackage.Workbook.Worksheets[worksheetName] == null)
{
excelPackage.Workbook.Worksheets.Add(worksheetName);
}
else
{
Console.WriteLine("The file already exists and has a worksheet with the default name \"eBay\" in it.");
Console.WriteLine("Specify a name of the new worksheet");
worksheetName = Console.ReadLine();
excelPackage.Workbook.Worksheets.Add(worksheetName);
}
try {
excelPackage.Save();
}
catch {
RedCW("The file is already open");
Console.ReadLine();
return;
}
DirectoryInfo currentDirectory = new DirectoryInfo(Directory.GetCurrentDirectory());
FileInfo[] filesInCurrentDirectory = currentDirectory.GetFiles();
List <List <eBayReport> > ebayReports = new List <List <eBayReport> >();
foreach (FileInfo file in filesInCurrentDirectory)
{
if (file.Extension == ".json")
{
ebayReports.Add(JsonConvert.DeserializeObject <List <eBayReport> >(File.ReadAllText(file.FullName)));
}
}
Console.WriteLine("Now specify the full name (with extension) of the CSV file");
string csvFile = Console.ReadLine();
string[] linesOfCSV;
try {
linesOfCSV = File.ReadAllLines(csvFile);
}
catch {
RedCW("The file is already open");
Console.ReadLine();
return;
}
ExcelWorksheet sheet = excelPackage.Workbook.Worksheets[worksheetName];
string[] initialLine = linesOfCSV[0].Split(',');
FieldInfo[] fieldsInPaypalReport = typeof(RoughPayPalReport).GetFields();
List <int> wantedPositions = new List <int>();
for (int i = 0; i < initialLine.Length; i++)
{
foreach (FieldInfo field in fieldsInPaypalReport)
{
if (initialLine[i].Replace(" ", "").Replace("\"", "") == field.Name)
{
wantedPositions.Add(i);
break;
}
}
}
FieldInfo[] fieldsInEBayReport = typeof(EbayTransaction).GetFields();
FieldInfo[] fieldsInRefund = typeof(Refund).GetFields();
for (int i = 0; i < fieldsInEBayReport.Length + fieldsInRefund.Length; i++)
{
if (i < fieldsInEBayReport.Length)
{
sheet.SetValue(1, i + 1, fieldsInEBayReport[i].Name);
}
else
{
sheet.SetValue(1, i + 1, fieldsInRefund[i - fieldsInEBayReport.Length].Name);
}
}
for (int i = 1; i < linesOfCSV.Length; i++)
{
List <string> line = linesOfCSV[i].Split(new string[] { "\",\"" }, StringSplitOptions.None).ToList <string>();
line[0] = line[0].Remove(0, 1);
line[line.Count - 1] = line[line.Count - 1].Remove(line[line.Count - 1].Length - 1, 1);
//List<string> adjustedLine = new List<string>();
//foreach (string value in line) {
// if (value != "," && value != "") {
// adjustedLine.Add(value);
// }
//}
RoughPayPalReport report = new RoughPayPalReport();
for (int indexOfSignificant = 0; indexOfSignificant < wantedPositions.Count; indexOfSignificant++)
{
fieldsInPaypalReport[indexOfSignificant].SetValue(report, line[wantedPositions[indexOfSignificant]].Replace("\"", ""));
}
payPalReports.Add(report);
}
Console.WriteLine("All parsing is complete, now you can rename the items or skip individual ones with enter");
for (int i = 0; i < payPalReports.Count; i++)
{
switch (payPalReports[i].Type)
{
case "eBay Auction Payment": {
EbayTransaction purchase = new EbayTransaction();
purchase.date = DateTime.ParseExact(payPalReports[i].Date, "dd. MM. yyyy", System.Globalization.CultureInfo.InvariantCulture);
purchase.time = DateTime.ParseExact(payPalReports[i].Time, "HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
purchase.originalCurrency = payPalReports[i].Currency;
if (purchase.originalCurrency != "USD")
{
purchase.usdValue = GetUsdValue(i);
}
else
{
purchase.usdValue = float.Parse(payPalReports[i].Gross.Replace(",", ""));
}
purchase.usdShippingCost = float.Parse(payPalReports[i].ShippingandHandlingAmount.Replace(",", ""));
purchase.sellerName = payPalReports[i].Name;
Console.WriteLine("Item: " + payPalReports[i].ItemTitle);
Console.WriteLine("Leave empty to keep name or type new one");
string s = Console.ReadLine();
if (s.Trim() == "")
{
purchase.itemName = payPalReports[i].ItemTitle;
}
else
{
purchase.itemName = s.Trim();
}
PPRindexbyRename.Add(purchase.itemName, i);
purchases.Add(purchase);
break;
}
case "Payment Refund": {
RoughPayPalReport currentReport = payPalReports[i];
List <int> matchingPurchaseIndexesSeller = new List <int>();
for (int j = 0; j < purchases.Count; j++)
{
if (purchases[j].sellerName == currentReport.Name)
{
matchingPurchaseIndexesSeller.Add(j);
}
}
if (matchingPurchaseIndexesSeller.Count == 1)
{
purchases[matchingPurchaseIndexesSeller[0]].refund = RefundFound(purchases[matchingPurchaseIndexesSeller[0]], i);
}
else if (matchingPurchaseIndexesSeller.Count == 0)
{
Console.WriteLine("Found a refund with seller inconsistencies");
Console.WriteLine("Item name: " + currentReport.ItemTitle);
SellerInconsistencyHandling(i);
}
else
{
Console.WriteLine("Found more sales from one seller");
Console.WriteLine("Item name: " + currentReport.ItemTitle);
SellerInconsistencyHandling(i);
}
break;
}
}
}
for (int i = 0; i < purchases.Count; i++)
{
int line = 2 + i;
for (int j = 0; j < fieldsInEBayReport.Length + fieldsInRefund.Length; j++)
{
if (j < fieldsInEBayReport.Length)
{
sheet.SetValue(line, j + 1, fieldsInEBayReport[j].GetValue(purchases[i]));
}
else if (purchases[i].refund != null)
{
sheet.SetValue(line, j + 1, fieldsInRefund[j - fieldsInEBayReport.Length].GetValue(purchases[i].refund));
}
}
//sheet.SetValue(line, 1, purchases[i].date.ToShortDateString());
//sheet.SetValue(line, 2, purchases[i].date.ToShortTimeString());
//sheet.SetValue(line, 3, purchases[i].itemName);
//sheet.SetValue(line, 4, purchases[i].originalCurrency);
//sheet.SetValue(line, 5, purchases[i].sellerName);
//sheet.SetValue(line, 6, purchases[i].usdShippingCost);
//sheet.SetValue(line, 7, purchases[i].usdValue);
//if (purchases[i].refund == null) {
// sheet.SetValue(line, 8, "NoRefunds");
//}
//else {
// sheet.SetValue(line, 9, purchases[i].refund.usdValue);
// sheet.SetValue(line, 10, purchases[i].refund.date);
//}
}
excelPackage.Save();
}
