Problem with duplicated rows

Question

I have this data

And I need to transfer it in this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {

            DataTable dt = new DataTable();
            dt.Columns.Add("EmpID");
            dt.Columns.Add("fName");
            dt.Columns.Add("sName");
            dt.Columns.Add("WorkDay", typeof(DateTime));
            dt.Columns.Add("HoursWorked", typeof(decimal));
            // mix them up a bit
            dt.Rows.Add("Emp1", "First1", "Second1", "2014-02-06", 3.2);
            dt.Rows.Add("Emp2", "First2", "Second2", "2014-02-06", 3.4);
            dt.Rows.Add("Emp1", "First1", "Second1", "2014-02-07", 3.2);
            dt.Rows.Add("Emp2", "First2", "Second2", "2014-02-04", 3.2);
            dt.Rows.Add("Emp2", "First2", "Second2", "2014-02-03", 3.2);
            dt.Rows.Add("Emp1", "First1", "Second1", "2014-01-06", 3.2);

DataTable dw = new DataTable();
dw.Columns.Add("EmpID");

            dw.Columns.Add("Mon", typeof(decimal));
            dw.Columns.Add("Tue", typeof(decimal));
            dw.Columns.Add("Wed", typeof(decimal));
            dw.Columns.Add("Thu", typeof(decimal));
            dw.Columns.Add("Fri", typeof(decimal));
            dw.Columns.Add("Sat", typeof(decimal));
            dw.Columns.Add("Sun", typeof(decimal));

            DateTime inputDate = Convert.ToDateTime("2014-02-06");
            DateTime firstDayOfTheWeek = inputDate.AddDays(0 - (inputDate).DayOfWeek.GetHashCode());

}
}
}

Vulpes · Answer

OK I've altered the previous version of the program to deal with the shifts aspect. The changes are highlighted. The array is just being populated for each output record - nothing is being stored or printed at this stage apart from some test code: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; namespace Test { class Program { static void Main(string[] args) { Console.Write(Enter filter date : ); DateTime fDate = DateTime.Parse(Console.ReadLine()); int dow = ((int)fDate.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc DateTime wComm= fDate.AddDays(-dow); DataTable tdInput = createData(); DataTable tdOutput = outputData(); string id = ; string fName = ; string lName = ; DateTime pDate = DateTime.MinValue; DateTime tempWComm = DateTime.MinValue; string temp = null; string tempFName = null; string tempLName = null; decimal[] total = new decimal[7]; int[] shifts = new int[7]; int shift = 0; object[] oa = null; DataRow row = null; foreach (DataRow dr in tdInput.Rows) { temp = dr[0].ToString(); tempFName = dr[1].ToString(); tempLName = dr[2].ToString(); shift = (int)dr[3]; pDate = (DateTime)dr[4]; dow = ((int)pDate.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc tempWComm = pDate.AddDays(-dow); if (id == ) { id = temp; fName = tempFName; lName = tempLName; } else if (id != temp) { row = tdOutput.NewRow(); oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } row.ItemArray = oa; tdOutput.Rows.Add(row); // test code for shifts Console.Write({0}  id); for(int i = 0; i Console.WriteLine(); // end test code id = temp; fName = tempFName; lName = tempLName; Array.Clear(total 0 7); Array.Clear(shifts 0 7); } if(wComm == tempWComm) { total[dow] += (decimal)dr[5]; if(shifts[dow] == 0) { shifts[dow] = shift; } else { shifts[dow] = 9; } } } // add the final row to the combined table row = tdOutput.NewRow(); oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } row.ItemArray = oa; tdOutput.Rows.Add(row); // check it worked Console.WriteLine(Results for Week commencing {0}
 wComm.ToString(yyyy-MM-dd)); string heading = String.Format(ID First Last Mon Tue Wed Thu Fri Sat Sun); string uline = String.Format(---- ------ ----- --- --- --- --- --- --- ---); Console.WriteLine(heading); Console.WriteLine(uline); foreach (DataRow dr in tdOutput.Rows) { foreach (object o in dr.ItemArray) { if(o is Decimal) { Console.Write({0:F1}  o); } else { Console.Write({0}  o); } } Console.WriteLine(); } Console.ReadKey(); } private static DataTable createData() { DataTable rowData = new DataTable(); rowData.Columns.Add(EmpID typeof(string)); rowData.Columns.Add(FirstName typeof(string)); rowData.Columns.Add(LastName typeof(string)); rowData.Columns.Add(Shift typeof(int)); rowData.Columns.Add(PayDate typeof(DateTime)); rowData.Columns.Add(WorkHours typeof(decimal)); rowData.Rows.Add(Emp1 First1 Last1 1 new DateTime(2014 2 3) 2.4); rowData.Rows.Add(Emp2 First2 Last2 2 new DateTime(2014 2 4) 5.4); rowData.Rows.Add(Emp1 First1 Last1 1 new DateTime(2014 2 5) 3.7); rowData.Rows.Add(Emp2 First2 Last2 3 new DateTime(2014 2 4) 2.4); rowData.Rows.Add(Emp2 First2 Last2 1 new DateTime(2014 2 6) 7.4); rowData.Rows.Add(Emp1 First1 Last1 2 new DateTime(2014 2 7) 2.1); rowData.Rows.Add(Emp3 First3 Last3 1 new DateTime(2014 2 3) 2.4); rowData.Rows.Add(Emp3 First3 Last3 2 new DateTime(2014 2 4) 5.4); rowData.Rows.Add(Emp4 First4 Last4 1 new DateTime(2014 2 5) 3.7); rowData.Rows.Add(Emp5 First5 Last5 3 new DateTime(2014 2 4) 2.4); rowData.Rows.Add(Emp4 First4 Last4 1 new DateTime(2014 2 6) 7.4); rowData.Rows.Add(Emp5 First5 Last5 2 new DateTime(2014 2 7) 2.1); rowData.Rows.Add(Emp1 First1 Last1 2 new DateTime(2014 210) 4.0); rowData.Rows.Add(Emp6 First6 Last6 3 new DateTime(2014 217) 2.5); //** new DataView dv = rowData.DefaultView; dv.Sort = EmpIDPayDateShift; DataTable rowData1 = dv.ToTable(); return rowData1; } private static DataTable outputData() { DataTable dt = new DataTable(); dt.Columns.Add(EmpID); dt.Columns.Add(FirstName); dt.Columns.Add(LastName); dt.Columns.Add(Mon typeof(decimal)); dt.Columns.Add(Tue typeof(decimal)); dt.Columns.Add(Wed typeof(decimal)); dt.Columns.Add(Thu typeof(decimal)); dt.Columns.Add(Fri typeof(decimal)); dt.Columns.Add(Sat typeof(decimal)); dt.Columns.Add(Sun typeof(decimal)); return dt; } } }

Vulpes · Answer

OK I've altered the code so that there are now individual records in the output table for each EmpId and 'Week Commencing' combination. Note that I've also made some changes to the 2 data methods and marked the changes with //** comments: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; namespace Test { class Program { static void Main(string[] args) { DataTable tdInput = createData(); DataTable tdOutput = outputData(); string id = ; string fName = ; string lName = ; DateTime wComm = DateTime.MinValue; DateTime pDate = DateTime.MinValue; string temp = null; string tempFName = null; string tempLName = null; DateTime tempWComm = DateTime.MinValue; int dow = 0; decimal[] total = new decimal[7]; object[] oa = null; DataRow row = null; foreach (DataRow dr in tdInput.Rows) { temp = dr[0].ToString(); tempFName = dr[1].ToString(); tempLName = dr[2].ToString(); pDate = DateTime.ParseExact(dr[4].ToString() yyyy-MM-dd null); dow = ((int)pDate.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc tempWComm = pDate.AddDays(-dow); if (id == ) { id = temp; fName = tempFName; lName = tempLName; wComm = tempWComm; } else if (id != temp || wComm != tempWComm) { row = tdOutput.NewRow(); oa = new object[11]; oa[0] = id; oa[1] = fName; oa[2] = lName; oa[3] = wComm.ToString(yyyy-MM-dd); for (int i = 4; i { oa[i] = total[i - 4]; } row.ItemArray = oa; tdOutput.Rows.Add(row); id = temp; fName = tempFName; lName = tempLName; wComm = tempWComm; Array.Clear(total 0 7); } total[dow] += (decimal)dr[5]; } // add the final row to the combined table row = tdOutput.NewRow(); oa = new object[11]; oa[0] = id; oa[1] = fName; oa[2] = lName; oa[3] = wComm.ToString(yyyy-MM-dd); for (int i = 4; i { oa[i] = total[i - 4]; } row.ItemArray = oa; tdOutput.Rows.Add(row); // check it worked string heading = String.Format(ID First Last Week Com'g Mon Tue Wed Thu Fri Sat Sun); string uline = String.Format(---- ------ ----- ---------- --- --- --- --- --- --- ---); Console.WriteLine(heading); Console.WriteLine(uline); foreach (DataRow dr in tdOutput.Rows) { foreach (object o in dr.ItemArray) { if(o is Decimal) { Console.Write({0:F1}  o); } else { Console.Write({0}  o); } } Console.WriteLine(); } Console.ReadKey(); } private static DataTable createData() { DataTable rowData = new DataTable(); rowData.Columns.Add(EmpID typeof(string)); rowData.Columns.Add(FirstName typeof(string)); rowData.Columns.Add(LastName typeof(string)); rowData.Columns.Add(Shift typeof(int)); rowData.Columns.Add(PayDate); //** this needs to be string type rowData.Columns.Add(WorkHours typeof(decimal)); rowData.Rows.Add(Emp1 First1 Last1 1 2014-02-03 2.4); rowData.Rows.Add(Emp2 First2 Last2 2 2014-02-04 5.4); rowData.Rows.Add(Emp1 First1 Last1 1 2014-02-05 3.7); rowData.Rows.Add(Emp2 First2 Last2 3 2014-02-04 2.4); rowData.Rows.Add(Emp2 First2 Last2 1 2014-02-06 7.4); rowData.Rows.Add(Emp1 First1 Last1 2 2014-02-07 2.1); rowData.Rows.Add(Emp3 First3 Last3 1 2014-02-03 2.4); rowData.Rows.Add(Emp3 First3 Last3 2 2014-02-04 5.4); rowData.Rows.Add(Emp4 First4 Last4 1 2014-02-05 3.7); rowData.Rows.Add(Emp5 First5 Last5 3 2014-02-04 2.4); //** Last5 rowData.Rows.Add(Emp4 First4 Last4 1 2014-02-06 7.4); rowData.Rows.Add(Emp5 First5 Last5 2 2014-02-07 2.1); rowData.Rows.Add(Emp1 First1 Last1 2 2014-02-10 4.0); //** Added DataView dv = rowData.DefaultView; dv.Sort = EmpIDPayDate; //** sort by empId then by PayDate DataTable rowData1 = dv.ToTable(); return rowData1; } private static DataTable outputData() { DataTable dt = new DataTable(); dt.Columns.Add(EmpID); dt.Columns.Add(FirstName); dt.Columns.Add(LastName); dt.Columns.Add(WeekCommencing); // ** changed name dt.Columns.Add(Mon typeof(decimal)); //** now decimal Mon to Sun dt.Columns.Add(Tue typeof(decimal)); dt.Columns.Add(Wed typeof(decimal)); dt.Columns.Add(Thu typeof(decimal)); dt.Columns.Add(Fri typeof(decimal)); dt.Columns.Add(Sat typeof(decimal)); dt.Columns.Add(Sun typeof(decimal)); return dt; } } } The output is: ID First Last Week Com'g Mon Tue Wed Thu Fri Sat Sun ---- ------ ----- ---------- --- --- --- --- --- --- --- Emp1 First1 Last1 2014-02-03 2.4 0.0 3.7 0.0 2.1 0.0 0.0 Emp1 First1 Last1 2014-02-10 4.0 0.0 0.0 0.0 0.0 0.0 0.0 Emp2 First2 Last2 2014-02-03 0.0 7.8 0.0 7.4 0.0 0.0 0.0 Emp3 First3 Last3 2014-02-03 2.4 5.4 0.0 0.0 0.0 0.0 0.0 Emp4 First4 Last4 2014-02-03 0.0 0.0 3.7 7.4 0.0 0.0 0.0 Emp5 First5 Last5 2014-02-03 0.0 2.4 0.0 0.0 2.1 0.0 0.0

Vulpes · Answer

This code which appears in a couple of lines (using either fDate or pDate): dow = ((int)fDate.DayOfWeek + 6) % 7; will need to be changed to: dow = (int)fDate.DayOfWeek; That's because the DayOfWeek enum starts from Sunday ( = 0) and so we don't need to adjust it now.

Lio Liov · Answer

Thank you for the help. How do I have to modify the code to display the week from Sunday to Saturday?

Vulpes · Answer

Sorry I don't have any personal experience of using that. However I found this link: http://help.infragistics.com/Help/Doc/WinForms/2012.1/CLR2.0/HTML/WinGrid_Add_Rows_to_WinGrid_Programmatically.html which says you can do it using the AddRow method (it seems to be actually called AddNew) of the UltraGridBand class but performance is poor if you're adding several rows at a time. I think you'll therefore have to go back to the DataTable approach if you're going to use this particular control.

Lio Liov · Answer

Thank you  instead of dataGrid in my case I have to add the data row to an ultraWinGrid. Do you have an Idea how to do that?

Vulpes · Answer

Yes there is a way that you can eliminate it and simplify the code at the same time: int shift = 0; object[] oa = null; Dictionary colors = new Dictionary (); colors.Add(9 Color.Red); colors.Add(1 Color.Blue); colors.Add(2 Color.Yellow); colors.Add(3 Color.Gray); DataGridViewRow dgvr = null; foreach (DataRow dr in tdInput.Rows) { temp = dr[0].ToString(); tempFName = dr[1].ToString(); tempLName = dr[2].ToString(); shift = (int)dr[3]; pDate = (DateTime)dr[4]; dow = ((int)pDate.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc tempWComm = pDate.AddDays(-dow); if (id == ) { id = temp; fName = tempFName; lName = tempLName; } else if (id != temp) { oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } // this line works because Add returns the index of the new row dgvr = dataGridView1.Rows[dataGridView1.Rows.Add(oa)]; for (int a = 0; a { dgvr.Cells[a + 3].Style.Backcolor = colors[shifts[a]]; } id = temp; fName = tempFName; lName = tempLName; Array.Clear(total 0 7); Array.Clear(shifts 0 7); } if(wComm == tempWComm) { total[dow] += (decimal)dr[5]; if(shifts[dow] == 0) { shifts[dow] = shift; } else { shifts[dow] = 9; } } } // add the final row to the combined table oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } dgvr = dataGridView1.Rows[dataGridView1.Rows.Add(oa)]; for (int a = 0; a { dgvr.Cells[a + 3].Style.Backcolor = colors[shifts[a]]; } }

Lio Liov · Answer

Thank you very much . If I had a teacher like you I wouldnt ask here for help I achieve the coloring by the folowing code Do you think that there is a way to eleminate the iter variable using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace Test1234 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender EventArgs e) { DateTime fDate = new DateTime(20140204); int dow = ((int)fDate.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc DateTime wComm = fDate.AddDays(-dow); DataTable tdInput = createData(); string id = ; string fName = ; string lName = ; DateTime pDate = DateTime.MinValue; DateTime tempWComm = DateTime.MinValue; string temp = null; string tempFName = null; string tempLName = null; decimal[] total = new decimal[7]; int[] shifts = new int[7]; int shift = 0; object[] oa = null; int iter = 0; foreach (DataRow dr in tdInput.Rows) { temp = dr[0].ToString(); tempFName = dr[1].ToString(); tempLName = dr[2].ToString(); shift = (int)dr[3]; pDate = (DateTime)dr[4]; dow = ((int)pDate.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc tempWComm = pDate.AddDays(-dow); if (id == ) { id = temp; fName = tempFName; lName = tempLName; } else if (id != temp) { oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } dataGridView1.Rows.Add(oa); for (int a = 0; a { if ((int)shifts[a] == 9) { dataGridView1.Rows[iter].Cells[a+3].Style.BackColor = Color.Red; } else if ((int)shifts[a] == 1) { dataGridView1.Rows[iter].Cells[a + 3].Style.BackColor = Color.Blue; } if ((int)shifts[a] == 2) { dataGridView1.Rows[iter].Cells[a + 3].Style.BackColor = Color.Yellow; } if ((int)shifts[a] == 3) { dataGridView1.Rows[iter].Cells[a + 3].Style.BackColor = Color.Gray; } } iter++; id = temp; fName = tempFName; lName = tempLName; Array.Clear(total 0 7); Array.Clear(shifts 0 7); } if(wComm == tempWComm) { total[dow] += (decimal)dr[5]; if(shifts[dow] == 0) { shifts[dow] = shift; } else { shifts[dow] = 9; } } } // add the final row to the combined table oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } dataGridView1.Rows.Add(oa); for (int a = 0; a { if ((int)shifts[a] == 9) { dataGridView1.Rows[iter].Cells[a + 3].Style.BackColor = Color.Red; } else if ((int)shifts[a] == 1) { dataGridView1.Rows[iter].Cells[a + 3].Style.BackColor = Color.Blue; } if ((int)shifts[a] == 2) { dataGridView1.Rows[iter].Cells[a + 3].Style.BackColor = Color.Yellow; } if ((int)shifts[a] == 3) { dataGridView1.Rows[iter].Cells[a + 3].Style.BackColor = Color.Gray; } } } private static DataTable createData() { DataTable rowData = new DataTable(); rowData.Columns.Add(EmpID typeof(string)); rowData.Columns.Add(FirstName typeof(string)); rowData.Columns.Add(LastName typeof(string)); rowData.Columns.Add(Shift typeof(int)); rowData.Columns.Add(PayDate typeof(DateTime)); rowData.Columns.Add(WorkHours typeof(decimal)); rowData.Rows.Add(Emp1 First1 Last1 1 new DateTime(2014 2 3) 2.4); rowData.Rows.Add(Emp2 First2 Last2 2 new DateTime(2014 2 4) 5.4); rowData.Rows.Add(Emp1 First1 Last1 1 new DateTime(2014 2 5) 3.7); rowData.Rows.Add(Emp2 First2 Last2 3 new DateTime(2014 2 4) 2.4); rowData.Rows.Add(Emp2 First2 Last2 1 new DateTime(2014 2 6) 7.4); rowData.Rows.Add(Emp1 First1 Last1 2 new DateTime(2014 2 7) 2.1); rowData.Rows.Add(Emp3 First3 Last3 1 new DateTime(2014 2 3) 2.4); rowData.Rows.Add(Emp3 First3 Last3 2 new DateTime(2014 2 4) 5.4); rowData.Rows.Add(Emp4 First4 Last4 1 new DateTime(2014 2 5) 3.7); rowData.Rows.Add(Emp5 First5 Last5 3 new DateTime(2014 2 4) 2.4); rowData.Rows.Add(Emp4 First4 Last4 1 new DateTime(2014 2 6) 7.4); rowData.Rows.Add(Emp5 First5 Last5 2 new DateTime(2014 2 7) 2.1); rowData.Rows.Add(Emp1 First1 Last1 2 new DateTime(2014 210) 4.0); rowData.Rows.Add(Emp6 First6 Last6 3 new DateTime(2014 25) 2.5); //** new DataView dv = rowData.DefaultView; dv.Sort = EmpIDPayDateShift; DataTable rowData1 = dv.ToTable(); return rowData1; } } }

Vulpes · Answer

Well if you're going to add the rows directly to the DataGridView (dataGridView1 say) and have already created the appropriate columns then this part of the code: row = tdOutput.NewRow(); oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } row.ItemArray = oa; tdOutput.Rows.Add(row); would translate to: oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } dataGridView1.Rows.Add(oa); As regards relative speed clearly the data is going to hit the grid sooner than it did before but the display might be a bit jumpy as the grid redraws after each row is added. If it is then you could set the grid's Visible property to false initially and then set it back to true after it's been fully populated.

Lio Liov · Answer

Do you think that it is going to perform faster. Do you have the time to show me how to do that.I try a few times and I have wrong data in the grid or duplicated rows.Thank you

Vulpes · Answer

Yes you could certainly do that. The only drawback is that you'd no longer have a DataTable if you wanted to manipulate or do something else with it later.

Lio Liov · Answer

Another thought do you think that it is possible instead of adding a row to the tdOtput after all calculations to add a row to the dataGrid  do the logic for the background and shifts and display it .

Vulpes · Answer

In theory it should perform faster if you're not having to hide rows but unless there's a large number of rows you're unlikely to notice any difference. So if you've coded it already and performance is acceptable stick with it.

Lio Liov · Answer

The way I handled it is: I bound the data table with rows for employee and shifts directly to the grid then I iterate through the grid hide every second row and change the background depending on the shift. Do you think that the suggested by you new solution is going to perform faster.

Vulpes · Answer

If it's not too late I've just had another thought. Immediately after creating it you could clone the output table: DataTable tdOutput = outputData(); DataTable tdShifts = tdOutput.Clone(); You could now add the 'shifts' rows to tdShifts rather than tdOutput and bind the grid directly to the latter. If you've put all this code in a method which returns a DataTable then return a DataTable array instead: return new DataTable[]{tdOutput tdShifts};

Vulpes · Answer

If you can live with that then I think I'd go with it. One idea that was going through my mind was to create two DataViews for the even and odd numbered rows. You could then bind the grid to the first of these DataViews. However you'd need to add another column to the datatable to store the row number so you could create a row filter expression and all things considered it's probably not worth the hassle.

Lio Liov · Answer

Yes I have to iterate through the data table and add one row to the grid at the time. But I have to change the background on some of the grid ceils. And I don't see any other solution ( up to my knowledge). Do you have any other suggestions. Thank you very much for the help.

Vulpes · Answer

I hadn't thought of including a second row in the output datatable for the shifts but it's a good solution as it keeps everything together. The only problem I can see is that you're not going to be able to bind the datatable directly to the grid because of the 'additional rows'.

Lio Liov · Answer

Thank you very much I realy admire your skills. I need to access the data in end of the method including the shift array and put it in a grid with different background per grid cell depending of the shift. The way I decided to handle the problem was I added a second row in the data table with each iteration and I saved the shift array in the second row. Is it a good approach? Thanks

Lio Liov · Answer

Thank you again. I think int array should be fine. 1 for first2-second3-third and 9 for multipleshifts

Vulpes · Answer

Well each output record can of course represent a full week of hours worked. So it's possible that a given employee could have hours worked (and multiple shifts) on any one or more days of that week. I think you'll therefore need to introduce a 'shifts' array with 7 elements one for each day. The question then is how to represent multiple shifts for any of those days. You could use a string as I mentioned earlier you could use an int (123 would represent shifts 1 2 and 3) or you could use a List . Which of these would be most convenient for what you want to do?

Lio Liov · Answer

I just want to be able to catch the shift (if it is single shift) or just set a multiple variable if are more than one after every output row Thank you again

Vulpes · Answer

As the code stands you'll only have one record for a given employee no matter how many shifts there are. However are you wanting to write the shifts to the output datatable in some form? If you're going to have stuff like '1 3' then you'l either need to use an int array or a string.

Lio Liov · Answer

Thank you very much for the detailed explanation I don't want to have different records for the same day and shift. example Monday  Shift 13 total hours 12 Tuesday shift 1 total hours 8 I don't want to display the shift I just want to be able to access the shift as I iterate the rows And thank you again

Vulpes · Answer

Well the 'total' array stores the totals for Monday (index 0) through to Sunday (index 7). So for each record we need to decide on which day of the week PayDate falls and then convert this to the appropriate index. This is done with the following code: pDate = (DateTime)dr[4]; dow = ((int)pDate.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc tempWComm = pDate.AddDays(-dow); pDate.DayOfWeek returns a value of the DayOfWeek enum. However DayOfWeek.Sunday (rather than Monday) is equivalent to an integer value of 0 so we need to adjust it to give the correct index and then assign this to the variable 'dow'. We can then obtain the first day of the pay week and assign it to the variable 'tempWComm' by subtracting 'dow' days from PayDate. At the beginning of the Main() method we've already computed the first day of the week for the filter date and assigned it to the variable 'wComm'. As we're only interested in calculating the totals for records which fall in the same week as the filter date we need to qualify the code accordingly: if(wComm == tempWComm) total[dow] += (decimal)dr[5]; Each time we move to a new EmpId we save the totals within a new record amd add it to the output datatable. We then have to clear the totals in readiness for the next record. You could adjust the code to obtain the value of the 'Shift' column as follows: object[] oa = null; DataRow row = null; int shift = 0; foreach (DataRow dr in tdInput.Rows) { temp = dr[0].ToString(); tempFName = dr[1].ToString(); tempLName = dr[2].ToString(); shift = (int)dr[3]; pDate = (DateTime)dr[4]; dow = ((int)pDate.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc tempWComm = pDate.AddDays(-dow); // etc You can now use it to adjust the calculations for each record as you want.

Lio Liov · Answer

Thank you very much. I realy appresiate everithing you have done. Everithing works I have one question. Where is going to be the best place to set a flag returning the shift value. I need to use the shift later in my logic.

Vulpes · Answer

OK let's try again: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; namespace Test { class Program { static void Main(string[] args) { Console.Write(Enter filter date : ); DateTime fDate = DateTime.Parse(Console.ReadLine()); int dow = ((int)fDate.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc DateTime wComm= fDate.AddDays(-dow); DataTable tdInput = createData(); DataTable tdOutput = outputData(); string id = ; string fName = ; string lName = ; DateTime pDate = DateTime.MinValue; DateTime tempWComm = DateTime.MinValue; string temp = null; string tempFName = null; string tempLName = null; decimal[] total = new decimal[7]; object[] oa = null; DataRow row = null; foreach (DataRow dr in tdInput.Rows) { temp = dr[0].ToString(); tempFName = dr[1].ToString(); tempLName = dr[2].ToString(); pDate = (DateTime)dr[4]; dow = ((int)pDate.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc tempWComm = pDate.AddDays(-dow); if (id == ) { id = temp; fName = tempFName; lName = tempLName; } else if (id != temp) { row = tdOutput.NewRow(); oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } row.ItemArray = oa; tdOutput.Rows.Add(row); id = temp; fName = tempFName; lName = tempLName; Array.Clear(total 0 7); } if(wComm == tempWComm) total[dow] += (decimal)dr[5]; } // add the final row to the combined table row = tdOutput.NewRow(); oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } row.ItemArray = oa; tdOutput.Rows.Add(row); // check it worked Console.WriteLine(Results for Week commencing {0}
 wComm.ToString(yyyy-MM-dd)); string heading = String.Format(ID First Last Mon Tue Wed Thu Fri Sat Sun); string uline = String.Format(---- ------ ----- --- --- --- --- --- --- ---); Console.WriteLine(heading); Console.WriteLine(uline); foreach (DataRow dr in tdOutput.Rows) { foreach (object o in dr.ItemArray) { if(o is Decimal) { Console.Write({0:F1}  o); } else { Console.Write({0}  o); } } Console.WriteLine(); } Console.ReadKey(); } private static DataTable createData() { DataTable rowData = new DataTable(); rowData.Columns.Add(EmpID typeof(string)); rowData.Columns.Add(FirstName typeof(string)); rowData.Columns.Add(LastName typeof(string)); rowData.Columns.Add(Shift typeof(int)); rowData.Columns.Add(PayDate typeof(DateTime)); rowData.Columns.Add(WorkHours typeof(decimal)); rowData.Rows.Add(Emp1 First1 Last1 1 new DateTime(2014 2 3) 2.4); rowData.Rows.Add(Emp2 First2 Last2 2 new DateTime(2014 2 4) 5.4); rowData.Rows.Add(Emp1 First1 Last1 1 new DateTime(2014 2 5) 3.7); rowData.Rows.Add(Emp2 First2 Last2 3 new DateTime(2014 2 4) 2.4); rowData.Rows.Add(Emp2 First2 Last2 1 new DateTime(2014 2 6) 7.4); rowData.Rows.Add(Emp1 First1 Last1 2 new DateTime(2014 2 7) 2.1); rowData.Rows.Add(Emp3 First3 Last3 1 new DateTime(2014 2 3) 2.4); rowData.Rows.Add(Emp3 First3 Last3 2 new DateTime(2014 2 4) 5.4); rowData.Rows.Add(Emp4 First4 Last4 1 new DateTime(2014 2 5) 3.7); rowData.Rows.Add(Emp5 First5 Last5 3 new DateTime(2014 2 4) 2.4); rowData.Rows.Add(Emp4 First4 Last4 1 new DateTime(2014 2 6) 7.4); rowData.Rows.Add(Emp5 First5 Last5 2 new DateTime(2014 2 7) 2.1); rowData.Rows.Add(Emp1 First1 Last1 2 new DateTime(2014 210) 4.0); rowData.Rows.Add(Emp6 First6 Last6 3 new DateTime(2014 217) 2.5); //** new DataView dv = rowData.DefaultView; dv.Sort = EmpIDPayDate; DataTable rowData1 = dv.ToTable(); return rowData1; } private static DataTable outputData() { DataTable dt = new DataTable(); dt.Columns.Add(EmpID); dt.Columns.Add(FirstName); dt.Columns.Add(LastName); dt.Columns.Add(Mon typeof(decimal)); dt.Columns.Add(Tue typeof(decimal)); dt.Columns.Add(Wed typeof(decimal)); dt.Columns.Add(Thu typeof(decimal)); dt.Columns.Add(Fri typeof(decimal)); dt.Columns.Add(Sat typeof(decimal)); dt.Columns.Add(Sun typeof(decimal)); return dt; } } } The output this time is: Results for Week commencing 2014-02-03 ID First Last Mon Tue Wed Thu Fri Sat Sun ---- ------ ----- --- --- --- --- --- --- --- Emp1 First1 Last1 2.4 0.0 3.7 0.0 2.1 0.0 0.0 Emp2 First2 Last2 0.0 7.8 0.0 7.4 0.0 0.0 0.0 Emp3 First3 Last3 2.4 5.4 0.0 0.0 0.0 0.0 0.0 Emp4 First4 Last4 0.0 0.0 3.7 7.4 0.0 0.0 0.0 Emp5 First5 Last5 0.0 2.4 0.0 0.0 2.1 0.0 0.0 Emp6 First6 Last6 0.0 0.0 0.0 0.0 0.0 0.0 0.0

Lio Liov · Answer

Thank you very much for your help.And I am sorry if I don't explain clearly what I am trying to achieve.I don't need the commencing column. I Will pass a day of the week and all the employee must be displayed with sumarized hours if they have more than one record per day(even if they don't have a record for this week they will be displayed). If I input todays date I will display all the people with summarized hours per day for who ever work per this week the rest are going to diaplay zerro. I tryed to use your code but I dont realy get how are you summarizing the hours per day. Also my date is in dateTime format

Lio Liov · Answer

Thank you for the help. I should have one record for employee with days populated for certain week.I am going to pass the day of the week. I need the shift column in the input table because I am going to use it in my logic how to display the records.

Vulpes · Answer

The reason for the format error is that you've included a 'Shift' column in the input datatable which means that the index of the PayDate column is now 4 rather than 3. However there are a lot of other errors besides. Whilst I can correct these I just need to be clear on your second point which may mean that some more drastic changes are needed. As the PayDates for a given EmpId may be different are you saying that the output datatable should now contain a separate record for each distinct EmpId and PayDate combination?

Lio Liov · Answer

Thank you very much for your time and help. When I run the code I am geting Format exception was unhandle on the date formating. Also how can I add the workHours if the empId and the dates are the same not taking in consideration the shift. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; namespace Test { class Program { static void Main(string[] args) { DataTable tdInput = createData(); DataTable tdOutput = outputData(); string id = ; string fName = ; string lName = ; string temp = null; string tempFName = null; string tempLName = null; int dow = 0; decimal[] total = new decimal[7]; object[] oa = null; DataRow row = null; foreach (DataRow dr in tdInput.Rows) { temp = dr[0].ToString(); tempFName = dr[1].ToString(); tempLName = dr[2].ToString(); DateTime workDay = DateTime.ParseExact(dr[3].ToString() yyyy-MM-dd null); dow = ((int)workDay.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc if (id == ) { id = temp; fName = tempFName; lName = tempLName; } else if (id != temp) { row = tdInput.NewRow(); oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } row.ItemArray = oa; tdInput.Rows.Add(row); id = temp; fName = tempFName; lName = tempLName; Array.Clear(total 0 7); } total[dow] += (decimal)dr[4]; } // add the final row to the combined table row = tdInput.NewRow(); oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } row.ItemArray = oa; tdInput.Rows.Add(row); // check it worked foreach (DataRow dr in tdInput.Rows) { foreach (object o in dr.ItemArray) { Console.Write({03}  o); } Console.WriteLine(); } Console.ReadKey(); } private static DataTable createData() { DataTable rowData = new DataTable(); rowData.Columns.Add(EmpID typeof(string)); rowData.Columns.Add(FirstName typeof(string)); rowData.Columns.Add(LastName typeof(string)); rowData.Columns.Add(Shift typeof(int)); rowData.Columns.Add(PayDate typeof(DateTime)); rowData.Columns.Add(WorkHours typeof(decimal)); rowData.Rows.Add(Emp1 First1 Last1 1 2014-02-03 2.4); rowData.Rows.Add(Emp2 First2 Last2 2 2014-02-04 5.4); rowData.Rows.Add(Emp1 First1 Last1 1 2014-02-05 3.7); rowData.Rows.Add(Emp2 First2 Last2 3 2014-02-04 2.4); rowData.Rows.Add(Emp2 First2 Last2 1 2014-02-06 7.4); rowData.Rows.Add(Emp1 First1 Last1 2 2014-02-07 2.1); rowData.Rows.Add(Emp3 First3 Last3 1 2014-02-03 2.4); rowData.Rows.Add(Emp3 First3 Last3 2 2014-02-04 5.4); rowData.Rows.Add(Emp4 First4 Last4 1 2014-02-05 3.7); rowData.Rows.Add(Emp5 First5 Las5 3 2014-02-04 2.4); rowData.Rows.Add(Emp4 First4 Last4 1 2014-02-06 7.4); rowData.Rows.Add(Emp5 First5 Last5 2 2014-02-07 2.1); DataView dv = rowData.DefaultView; dv.Sort = EmpID; DataTable rowData1 = dv.ToTable(); return rowData1; } private static DataTable outputData() { DataTable dt = new DataTable(); dt.Columns.Add(EmpID); dt.Columns.Add(FirstName); dt.Columns.Add(LastName); dt.Columns.Add(PayDate); dt.Columns.Add(Mon typeof(int)); dt.Columns.Add(Tue typeof(int)); dt.Columns.Add(Wed typeof(int)); dt.Columns.Add(Thu typeof(int)); dt.Columns.Add(Fri typeof(int)); dt.Columns.Add(Sat typeof(int)); dt.Columns.Add(Sun typeof(int)); return dt; } } }

Vulpes · Answer

These are getting harder ;) Try this: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; namespace Test { class Program { static void Main(string[] args) { DataTable dt = new DataTable(); dt.Columns.Add(EmpID); dt.Columns.Add(fName); dt.Columns.Add(sName); dt.Columns.Add(WorkDay); // ** keep this as string type dt.Columns.Add(HoursWorked typeof(decimal)); // mix them up a bit dt.Rows.Add(Emp1 First1 Second1 2014-02-06 3.2); dt.Rows.Add(Emp2 First2 Second2 2014-02-06 3.4); dt.Rows.Add(Emp1 First1 Second1 2014-02-07 3.2); dt.Rows.Add(Emp2 First2 Second2 2014-02-04 3.2); dt.Rows.Add(Emp2 First2 Second2 2014-02-03 3.2); dt.Rows.Add(Emp1 First1 Second1 2014-01-06 3.2); DataTable dw = new DataTable(); dw.Columns.Add(EmpID); dw.Columns.Add(fName); // **added this dw.Columns.Add(sName); // ** added this dw.Columns.Add(Mon typeof(decimal)); dw.Columns.Add(Tue typeof(decimal)); dw.Columns.Add(Wed typeof(decimal)); dw.Columns.Add(Thu typeof(decimal)); dw.Columns.Add(Fri typeof(decimal)); dw.Columns.Add(Sat typeof(decimal)); dw.Columns.Add(Sun typeof(decimal)); // sort original table DataView dv = dt.DefaultView; dv.Sort = EmpID; // sort by EmpID DataTable du = dv.ToTable(); string id = ; string fName = ; string lName = ; string temp = null; string tempFName = null; string tempLName = null; int dow = 0; decimal[] total = new decimal[7]; object[] oa = null; DataRow row = null; foreach (DataRow dr in du.Rows) { temp = dr[0].ToString(); tempFName = dr[1].ToString(); tempLName = dr[2].ToString(); DateTime workDay = DateTime.ParseExact(dr[3].ToString() yyyy-MM-dd null); dow = ((int)workDay.DayOfWeek + 6) % 7; // Monday = 0 Tuesday = 1 etc if (id == ) { id = temp; fName = tempFName; lName = tempLName; } else if (id != temp) { row = dw.NewRow(); oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } row.ItemArray = oa; dw.Rows.Add(row); id = temp; fName = tempFName; lName = tempLName; Array.Clear(total 0 7); } total[dow] += (decimal)dr[4]; } // add the final row to the combined table row = dw.NewRow(); oa = new object[10]; oa[0] = id; oa[1] = fName; oa[2] = lName; for (int i = 3; i { oa[i] = total[i - 3]; } row.ItemArray = oa; dw.Rows.Add(row); // check it worked foreach (DataRow dr in dw.Rows) { foreach (object o in dr.ItemArray) { Console.Write({03}  o); } Console.WriteLine(); } Console.ReadKey(); } } } The output is: Emp1 First1 Second1 3.2 0 0 3.2 3.2 0 0 Emp2 First2 Second2 3.2 3.2 0 3.4 0 0 0