I am trying to create a function that creates one (1) PDF-invoice for each distinct value of a property in a List I am looping through (using PDFSharp).

I grab the value of the string, and want to use it as header of the invoices (it's the name of the business getting invoiced).

To illustrate: In the following table, I would like to get the value of LicenseHolder1 (once), LicenseHolder2 (once) and LicenseHolder3 (once).

This is the method I am using:

using (SqlConnection con = new(ConnectionString.connectionString))
using (SqlCommand cmd = new("spCreateInvoice", con) {CommandType = CommandType.StoredProcedure})
using (SqlDataAdapter da = new(cmd))
// this is the name of my SQL table
using (DataTable dt = new("tblTripsPerMonth")) 
using (DataSet ds = new()) {

    con.Open();
    da.Fill(dt);
    ds.Tables.Add(dt);

    /* adding the SQL table rows to my ObservableCollection (PaidTrips), 
    which is bound to properties in my holder class (PaidTrip): */

    foreach (DataRow dr in ds.Tables[0].Rows {

       PaidTrips.Add(new PaidTrip {

             LicenseHolderID = dr[0].ToString(),
             Value1 = (decimal)dr[1],
             Value2 = (decimal)dr[2],
             Value3 = dr[3].ToString(),
        });

        // Now I am instantiating a list, so that I can group
        List<PaidTrip> paidTrip = PaidTrips
            .GroupBy(p => new {p.LicenseHolderID})
            .Select(g => g.First())
            .ToList();
            
        // this is copied code from another question, and this is where I fall of
        foreach (PaidTrip PaidTrips in paidTrip) {

            foreach (var LicenseHolderID in PaidTrips.GetType().GetProperties()) {

                string n = LicenseHolderID.GetValue(PaidTrips).ToString();

If I after this grab string n and pass it into, let's say, a MessageBox.Show, it displays LicenseHolder1, as it should. But, if I try to pass it into the PDF, like this... (continued from last code-block)

                System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
                PdfDocument pdf = new();
                PdfPage page = pdf.AddPage();
                XGraphics gfx = XGraphics.FromPdfPage(page);

                // drawing in the string value
                gfx.DrawString(n, new XFont("Arial", 40, XFontStyle.Bold), myColor, new XPoint(40, 250));

                // saving the PDF
                pdf.Save("C:\File\Path\TestPDF.pdf");
            }
        }
    }
}

...the PDF is created, but the string I drew is not filled with LicenseHolder1, but rather, with (seemingly) random values of the other columns (like 66, UF-2107 or 77). How come?

(Oh, and I know that this code only creates one (1) PDF-file, I need several (one for each distinct value in the LicenseHolderID column -- but that's an issue for another day)

CodePudding user response：

Maybe you can do smt like this:

List<PaidTrip> paidTrip = PaidTrips
        .GroupBy(p => new {p.LicenseHolderID})
        .Select(g => g.First()).Distinct().ToList();

CodePudding user response：

I don't understand what you are trying to accomplish in these nested foreach loops

foreach (PaidTrip PaidTrips in paidTrip) {

    foreach (var LicenseHolderID in PaidTrips.GetType().GetProperties()) {
        //...
    }
}

paidTrip should be an IEnumerable<PaidTrip> that represents the first PaidTrip by each company, so maybe renaming the variable to firstPaidTrips would help clarify things in your code, then your outer foreach loop it could be

foreach (var paidTrip in firstPaidTrips)

Then it will be more clear that each variable in that foreach loop is a singular paidTrip

To get the LicenseHolderID for that paid trip, you just need to access that property directly.

foreach (var paidTrip in firstPaidTrips)
{
    var n = paidTrip.LicenseHolderID;
    //rest of the code is the same without that inner foreach loop.
}

That inner foreach loop looks like you were trying to use reflection to get all the properties on an unknown type object, but from your example code it looks like you know what the object type is, and you should be able to just grab the property from the object PaidTrip

CodePudding user response：

Is there an id column in your datatable as shown above? would your column zero not be taking an id there?

foreach (DataRow dr in ds.Tables[0].Rows {

   PaidTrips.Add(new PaidTrip {

         LicenseHolderID = dr[0].ToString(),
         Value1 = (decimal)dr[1],
         Value2 = (decimal)dr[2],
         Value3 = dr[3].ToString(),
    });

If your grouping by LicenseHolderID your selecting the first element of the grouped result. Is that intended?

List<PaidTrip> paidTrip = PaidTrips
        .GroupBy(p => new {p.LicenseHolderID})
        .Select(g => g.First())
        .ToList();

This would essentialy return one new Object Containing multiple Paid Trip objects containing the same LicenseHolderID.

Usually if you want to use the string name of the group you can reference the key value.

paidTrip.Key.ToString();
    

might be better to use,

    var paidTrip = PaidTrips
        .GroupBy(p => new {p.LicenseHolderID})
        .ToList();

    foreach (var tripFound in paidTrip)
    {
        string tripId = tripFound.Key.ToString();
        //Generate your PDF File.
    }

This should give you one file per group. Apologies if i've picked you up wrong.

CodePudding user response：

I think you need to group by the LicenseHolderID and then Sum / Max the rest of the fields. From there, you can get rid of the loop foreach (var LicenseHolderID in PaidTrips.GetType().GetProperties()) as you will have built a collection of the objects you need to iterate.

Where you create the collection, you might want to group the data there, and it looks like you're taking a row at a time:

var paidTrips = ds.Tables[0].Rows.Cast<DataRow>().GroupBy(t => t["LicenseHolderID"])
                    .Select(g => new PaidTrip  {
                        LicenseHolderID = g.Key,
                        Value1 = g.Sum(x => x.["Value1"] ),
                        Value2 = g.Sum(x => x.["Value2"] ),
                        Value3 = g.Max(x => x.["Value3"] ) 
                    })
                    .ToList();

This should get you the list above, grouped by LicenseHolderID, for example, LicenseHolder2 becomes:

LicenseHolder2 84 49 UF-2107

Given your grouping by LicenseHolderID. You can then iterate the loop, getting 3 PDFs in this case.

    foreach (PaidTrip pd in paidTrips ) {
    
        string n = pd.LicenseHolderID;
       //...pdf code...
       gfx.DrawString(n, new XFont("Arial", 40, XFontStyle.Bold),
                      myColor, new XPoint(40, 250));
      // saving the PDF
      pdf.Save("C:\File\Path\TestPDF.pdf");
                }

Please note, I'm handwriting this, so it may not compile - but you should get the idea.