2
\$\begingroup\$

This is my sample XML file:

<?xml version="1.0" encoding="utf-8"?>
<receipts>
  <receipt id="F66BF89E-1070-4E02-9B98-B934A66D0E16" year="2014" month="9" day="17" place="Place#1">
    <purchase name="Coca-Cola" quantity="1" price="3.99" category="Drinks" />
    <purchase name="Watermelon" quantity="1" price="1.98" />
    <purchase name="Water" quantity="1" price="1.49" category="Drinks" />
  </receipt>
  <receipt id="13ECD7F9-F639-4E65-9C80-9E1A8C2E305B" year="2014" month="9" day="17" place="Place#1">
    <purchase name="Sweets" quantity="1" price="3.98" category="Sweets" />
    <purchase name="Water" quantity="1" price="1.99" category="Drinks" />
    <purchase name="Flour" quantity="1" price="1.99" />
    <purchase name="Bread" quantity="1" price="1.19" />
    <purchase name="Milk" quantity="1" price="1.98" category="Drinks"/>
    <purchase name="Potatoes" quantity="0.345" price="1.49" />
  </receipt>
</receipts>

And this is my approach to parsing, filtering, categorizing and displaying it:

// load XML
XElement xmlReceipts = XElement.Load(@".\receipts.xml");

// create objects from XML
List<Receipt> receipts = new List<Receipt>();

foreach (var xmlReceipt in xmlReceipts.Elements("receipt"))
{
    Guid id = new Guid(xmlReceipt.Attribute("id").Value);
    string place = xmlReceipt.Attribute("place").Value;
    int year = Int32.Parse(xmlReceipt.Attribute("year").Value);
    int month = Int32.Parse(xmlReceipt.Attribute("month").Value);
    int day = Int32.Parse(xmlReceipt.Attribute("day").Value);

    List<ProductLine> products = new List<ProductLine>();

    foreach (var xmlProduct in xmlReceipt.Elements("purchase"))
    {
      string name = xmlProduct.Attribute("name").Value;
      string quantity = xmlProduct.Attribute("quantity").Value;
      string price = xmlProduct.Attribute("price").Value;
      string category = xmlProduct.Attribute("category") == null ? "(no category)" : xmlProduct.Attribute("category").Value;
      products.Add(new ProductLine(name, quantity, price, category));
    }
    receipts.Add(new Receipt(id, place, year, month, day, products));
}

// filter to this month's receipts
var receiptsFiltered = from receipt in receipts
                 where receipt.Date.Year == DateTime.Now.Year &&
                 receipt.Date.Month == DateTime.Now.Month
                 select receipt.Products as List<ProductLine>;

// flatten lists of receipts and products
var productsFiltered = from products in receiptsFiltered
                 from product in products
                 select new { Name = product.Name, Price = product.Price, Quantity = product.Quantity, Category = product.Category };

// group products by category
var productsByCategory = from product in productsFiltered
                 group product by product.Category;


// calculate totals per category
Dictionary<string, double> categoriesTotals = new Dictionary<string, double>();

foreach (var category in productsByCategory)
{
    double totalSum = 0;

    foreach (var product in category)
    {
      totalSum += product.Quantity * (double)product.Price;
    }

    categoriesTotals.Add(category.Key, totalSum);
}

// show data on chart
chart1.Series[0].Points.DataBindXY(categoriesTotals.Keys, categoriesTotals.Values);

Is there a way to simplify it? I tried to use XML to LINQ or linking XML directly to Chart but I couldn't manage grouping before flattening the lists.

Sources:

\$\endgroup\$
3
\$\begingroup\$

For the LINQ query at the end SelectMany is your friend.

You should be able to achieve your desired results in a single query:

var dateOfInterest = DateTime.Now; // just put in whatever date interests you

var categoryTotals = receipts
         // filter receipts by the desired month
        .Where(r => IsWithinSameMonthAndYear(r.Date, dateOfInterest))
        // select all products from all filtered receipts as a flat list
        .SelectMany(r => r.Products)
        // group the products by their category
        .GroupBy(p => p.Category)
        // turn into a dictionary keyed of the category key and the value is the weighted sum of the product quantities
        .ToDictionary(g => g.key.Key, g => g.Sum(p => p.Quantity * p.Price));

Little helper method:

// returns true if first is within the same month and year as second
private bool IsWithinSameMonthAndYear(DateTime first, DateTime second)
{
    return first.Year == second.Year && first.Month == second.Month;
}
\$\endgroup\$
  • \$\begingroup\$ Wow, LINQ is so powerful... if you're proficient with it :) Thank you. But there is still one thing: is it possible to move this part: .Where(r => r.Date.Year == today.Year && r.Date.Month == today.Month) to a variable? In case if I wanted to enable showing earlier months from the UI. It Would involve creating a Func and pass it to Where(), I guess? Correct me if I'm wrong. \$\endgroup\$ – Mariusz Ignatowicz Sep 18 '14 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.