I need some C# help

Hey all,
Not at all new to Epicor but definitely fairly new to E10 and C#. I’m learning as I go but will be taking some courses soon. I have a need to calculate LaborHed hours but output into single days (in a csv) within a date range. If the user has a single clock in and out within a day, I’ve got this down and can calculate OT hours and such easily. However, if they clock out and in for lunch, lets say, I get multiple LaborHed records for that employee for that day. Not what they, my employer, wants. I reached out in another developer forum for help and someone got me close, very close. I was wondering if someone here can help me nail the rest down.I will paste my code below. The problem with the code below is that I cannot figure out how to include EmployeeNum in my select along with the ClockInDate which is the Key and the calculated work hours as you will see below. Any help would be oh so appreciated.

    foreach (var ttUD06_iterator in (from ttUD06_Row in ttUD06
                                 where ttUD06_Row.Company == Session.CompanyID
                                 select ttUD06_Row))
    {
    var ttUD06Row = ttUD06_iterator;
    
    Outfile = @"\\localhost\EpicorData\PayrollHours\PayrollHours_TEST_Merc.csv";
    /* Write column headers */
    OString = "Key,#Last,#First,#ClockInDate,E_Reg_Hours,E_Ovt_Hours,E_DT_Hours,E_TP_Hours,E_Tv O_Hours,E_Tv D_Hours,E_For_Dollars"; //Mercantile Format
    using (System.IO.StreamWriter file = new System.IO.StreamWriter(@Outfile, false))
      {
         file.WriteLine(OString);
      }
      
      var empWorkTimes = Db.LaborHed.Where(x => x.ClockInDate >= (DateTime?)ttUD06Row.Date01 && x.ClockInDate <= (DateTime?)ttUD06Row.Date02 && x.EmployeeNum == "2436")
        .OrderBy(x => x.EmployeeNum)
        .GroupBy(x => x.ClockInDate)
        .Select(x => new { ClockInDate = x.Key, EmployeeNum = x.Select(employee => employee.EmployeeNum), WorkHours = x.Sum(l => (l.ClockOutTime - l.ClockInTime) - (l.LunchInTime - l.LunchOutTime)) });
        
          if (empWorkTimes != null)
          {
           foreach (var WorkDetails in empWorkTimes)
             {
                strEmpNum = Convert.ToString(WorkDetails.EmployeeNum);
                
                    this.PublishInfoMessage(System.Convert.ToString(WorkDetails.EmployeeNum),Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual,"","");
                  EmpBasic = (from EmpBasic_Row in Db.EmpBasic
                          where EmpBasic_Row.EmpID == strEmpNum && EmpBasic_Row.ShopEmployee_c == true
                          select EmpBasic_Row).FirstOrDefault();
                  
                  if (EmpBasic != null)
                  {
                    this.PublishInfoMessage(System.Convert.ToString(WorkDetails.EmployeeNum),Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual,"","");
                  }
            }
          }
          
    ttUD06Row.CheckBox01 = false;
    }

I would approach it a little differently than hand coding it all. You could easily write a BAQ to generate the data you want grouped and all. Then use the updatable features (enabling BPMs on a BAQ) to turn the data already the way you want into a simple CSV.

4 Likes

I agree with @jgiese.wci
In Epicor you should only use custom code as a last resort since there are so many very good tools to use first. My primary reason for this is to keep your environment as upgrade friendly as possible. Custom Code in Epicor can quickly become technical debt.
Depending on what you need to accomplish, you would probably first create a BAQ and use Calculated fields with Sums on the hours and then another calc field to get to days.
As for getting the data out, you can ‘publish’ the BAQ to a Dashboard and add a ‘tracker’ pane so the user can filter the data.
Once the data is in a Grid in the dashboard they can just Right-Click in the grid and select ‘Copy To Excel’ to get it to Excel.
You can also use the BAQ Export Process to schedule the BAQ to run and send the results to a CSV.
You can setup dynamic criteria with expressions in the BAQ criteria to only export what you need.
If you have DMT you can use DMT and PowerShell to script the execution of the BAQ and store it where you need.
You can also create a ‘BAQ Report’ where the Output is a Text file.
If you haven’t dug into it yet, go through the ICE Tools Guide

2 Likes

Could you expand on this? Does it require a BAQ Report or a Dashboard to initiate the BAQ?

No you can just put some code in the UBAQ Post Processing to export the BAQ Results as a CSV. Or you can schedule a BAQ Export.

With a small app that calls BAQs via rest you can even fire stuff off from task scheduler pretty easy

1 Like

I agree entirely with you all as it makes perfect sense to do as you mention. I did figure out the code and will finish this since it is extremely time sensitive and needs to be done ASAP.
I will absolutely begin creating a BAQ to do so going forward. Hopefully, I can figure it out. Certainly struggling with E10 and the advanced BAQ with subqueries right now.

Thanks,
Chris

This is what I actually need, as the output format I require is more “CSV-like” than actual CSV. The structure of each row can vary. Here’s a sample:

image

I currently get it into the format I need in a BAQ report, but don’t know how to have the report save as a text file. Currently, we just preview the report, select all the text, copy, and then paste into a new text file.

Now this right here I need to learn more about…

@ckrusen have you tried the BAQ Export first, it’s way easier? Usually the struggle with BAQ Export is finding the file, it’s not where the application help says it is (EpicorData), its in some odd Epicor3Data directory.
If you check the ‘Report/Task Log’ tab in the System Monitor after it runs it will give the file location.

Using BAQ Export rings a bell … I currently can’t do that as the output can have variable length rows. I currently use SSRS to do some post processing to the BAQ results. A couple of calcs, some grouping, ordering, and actual output formatting.

I need output like

"H","0397","200211",134032.72,10
"I","60047","39767924","200210","R",12525.00,"COL02","SCUS","2190954","C","303677","200210","200210"
"D","J","910","91",1,8325.00
"D","J","917","91",1,4200.00

I don’t think a CSV formatted BAQ Export could do that.

Even if I made it such that my BAQ output a single column of pre-formatted text, a value of
image

Because the BAQ Export converts that to:
"""H"",""0397"",""200211"",134032.72,10"

If I have individual fields (which I reall can’t have as sometimes the value in specific column is a number and other times is text)

image

The BAQ Export outputs as:

"H","0397","200211","134032.72","10"

Yes that would be tricky for a BAQ to do since Epicor limits some SQL functionality.
Just some thoughts off the top of my head as alternatives to SSRS.
(Disclaimer: I can’t quite work out the data structure from your examples)

  • DMT + PowerShell: Many times I have used DMT to export a BAQ (not sure if it outputs the results differently than BAQ Export Process) and then I used PowerShell’s CSV extensions to further manipulate the CSV. Effectively running queries against the CSV and creating a new CSV from those results. Not sure it could do exactly what you need, as I don’t fully understand the manipulations you need.
  • BAQ Sub-queries + Stuff(): I don’t recall if BAQ will allow Stuff() but many times you can use Stuff() to get multiple row results into one row: https://stackoverflow.com/questions/14141903/how-to-group-many-rows-into-one-row-by-sql-server

But if you’ve done this with SSRS, that is quite good as you didn’t have to resort to Epicor custom code.

Just brainstorming.

1 Like

Keep persisting you will get there. It’s a constant learning curve for us all.