VB Linq - Group invoices by store, and include all the invoice items
-
I searched around the internet, but wasn't able to find much, and I lack the knowledge of using the correct terms to search with. I'm trying to group a bunch of invoices together by store code, and I want to bring the invoice items over as well. The invoice items are just a
List(Of AtdMiniInvoiceItems), which matches the.FITEMSin the group, so I really don't need to use theWithto itemize what I want. I'm looking for the correct way to do this without writing a ton of code, and a point in the right direction. I can see that perhaps using With and doing this again would be the correct solution, but was looking to just combine it all as is.'Group the Store Invoices by Store to Average out the Freight/Shipping Margins
Dim gFreightInvoicesGrouped As List(Of AtdFreightInvoiceReport) = gFreightInvoicesAll _
.OrderBy(Function(ob) ob.FSTORECODE) _
.Where(Function(a) a.FSTORECODE = String.Empty Or a.FSTORECODE.ToLower() = "unknown") _
.GroupBy(Function(inv) inv.FSTORECODE).Select(Function(cl) New AtdFreightInvoiceReport() With {
.FINVNO = cl.First().FINVNO,
.FSTORECODE = cl.First().FSTORECODE,
.FLOCATION = cl.First().FLOCATION,
.FAMOUNT = cl.Sum(Function(x) x.FAMOUNT),
.FSHIPCOST = cl.Sum(Function(x) x.FSHIPCOST),
.FSHIPPRICE = cl.Sum(Function(x) x.FSHIPPRICE),
.FMARGINPERCENT = cl.Sum(Function(x) x.FMARGINPERCENT),
.FREDFLAG = False,
.FITEMS = cl.GroupBy(Function(shipItems) shipItems.FITEMS).ToList()
}).ToList()If it ain't broke don't fix it Discover my world at jkirkerx.com
-
I searched around the internet, but wasn't able to find much, and I lack the knowledge of using the correct terms to search with. I'm trying to group a bunch of invoices together by store code, and I want to bring the invoice items over as well. The invoice items are just a
List(Of AtdMiniInvoiceItems), which matches the.FITEMSin the group, so I really don't need to use theWithto itemize what I want. I'm looking for the correct way to do this without writing a ton of code, and a point in the right direction. I can see that perhaps using With and doing this again would be the correct solution, but was looking to just combine it all as is.'Group the Store Invoices by Store to Average out the Freight/Shipping Margins
Dim gFreightInvoicesGrouped As List(Of AtdFreightInvoiceReport) = gFreightInvoicesAll _
.OrderBy(Function(ob) ob.FSTORECODE) _
.Where(Function(a) a.FSTORECODE = String.Empty Or a.FSTORECODE.ToLower() = "unknown") _
.GroupBy(Function(inv) inv.FSTORECODE).Select(Function(cl) New AtdFreightInvoiceReport() With {
.FINVNO = cl.First().FINVNO,
.FSTORECODE = cl.First().FSTORECODE,
.FLOCATION = cl.First().FLOCATION,
.FAMOUNT = cl.Sum(Function(x) x.FAMOUNT),
.FSHIPCOST = cl.Sum(Function(x) x.FSHIPCOST),
.FSHIPPRICE = cl.Sum(Function(x) x.FSHIPPRICE),
.FMARGINPERCENT = cl.Sum(Function(x) x.FMARGINPERCENT),
.FREDFLAG = False,
.FITEMS = cl.GroupBy(Function(shipItems) shipItems.FITEMS).ToList()
}).ToList()If it ain't broke don't fix it Discover my world at jkirkerx.com
Interesting, I didn't know we can run functions inside a Linq GroupBy. Hmm ... This opens up some new possibilities for me. I'll attack the invoice items again, report almost done now
'Group the Store Invoices by Store to Average out the Freight/Shipping Margins
Dim gFreightInvoicesGrouped As List(Of AtdFreightInvoiceReport) = gFreightInvoicesAll _
.OrderBy(Function(ob) ob.FSTORECODE) _
.Where(Function(a) a.FSTORECODE <> String.Empty Or a.FSTORECODE.ToLower() <> "unknown") _
.GroupBy(Function(inv) inv.FSTORECODE).Select(Function(cl) New AtdFreightInvoiceReport() With {
.FINVNO = cl.First().FINVNO,
.FSTORECODE = cl.First().FSTORECODE,
.FLOCATION = cl.First().FLOCATION,
.FAMOUNT = cl.Sum(Function(x) x.FAMOUNT),
.FSHIPCOST = cl.Sum(Function(x) x.FSHIPCOST),
.FSHIPPRICE = cl.Sum(Function(x) x.FSHIPPRICE),
.FMARGINPERCENT = AmCommon.CalculateShippingPercentageFormattedToString(cl.Sum(Function(x) x.FSHIPCOST), cl.Sum(Function(x) x.FAMOUNT)),
.FREDFLAG = False
}).ToList()If it ain't broke don't fix it Discover my world at jkirkerx.com