I have made a code to do this in VB.net
it simply
1-gets a table for current existing employee
2- set the display grid (DGV) rows to equal existing employee number
3- set the DGV column number to equal search period plus two (name and position)
4- get a string matrix of Assignment name with their ID s as index
5- search for each employee ID during that period and puts the found data to the grid by subtracting assignment date from search start date to get days number and add two as column index (the two is the shift to make space for name and position column)
5- the row number is the employee order in the employee data table
it is slow because it searches the data base as the number of employee (huge number)
I am currently working on creating a one table search for a certain period grouped by employee id
and distribute them on the grid
still my problem even if this worked well
I need it printed
and crystal reports is faster and spare me from page setup thing
below is the code I made
It has some dependencies which in turn has some dependencies
but if anyone wants to get the idea it is here
if someone wants the dependencies i will be more than happy to give it for free
(help as you want to helped)
Private Sub UpdateMonthAssigmentGrid()
Dim IntMonth As Integer = ComboDisplayedMonth.SelectedIndex + 1 '7 'the month to be searched
Dim IntYear As Integer = NumericUpDownDisplayedYear.Value '2018 'the year to be searched
Dim IntMonthLengthDays As Integer = GetMonthLength(IntYear, IntMonth) 'the number of days in a month (31,30,29,28) to set the grid columns number
Dim StrStartDate As String = "'" + IntYear.ToString + "-" + IntMonth.ToString + "-01'" 'the beging of search Date
Dim StrEndDate As String = "'" + IntYear.ToString + "-" + IntMonth.ToString + "-" + IntMonthLengthDays.ToString + "'" 'the End of search Date
Dim IntDayIndex As Integer
Dim StrAssigmentTypeList() As String 'matrix of Assigment Names
Dim IntIDAssigment2Order() As Integer 'matrix of Assigment ID to appera order
'
Dim GridColNo As Integer = 2 'the number of column used for showing data(name and rank)
'
Dim IntEmpolyeeCounter As Integer 'Dummy Variable used to in looping for empolyees
Dim INtDaysCounter As Integer 'Dummy Variable used to in looping for Days
'
Dim TblEmpolyeeNames As Data.DataTable 'data table holding empolyees NAmes and ranks
Dim IntEmpolyeeNo As Integer 'the no of empolyee = TblEmpolyeeNames.Rows.Count will be used to set the number of displayed grid rows
Dim IntIdEmpolyee As Integer 'Dummy Variable used to store the currently processed
Dim TblMonthAssigment As Data.DataTable 'data table holding empolyees NAmes and ranks
'= ReadDataTable("SELECT TblEmpolyee.IDEmpolyee ,TblEmpolyee.EmpolyeeName ,TblRank.RankName from TblEmpolyee left join TblRank TblRank on TblRank.IDRank= TblEmpolyee.IDRank where InService='True' order by TblEmpolyee.IDRank DESC")
Dim StrAssigmentDate As String
Dim IntAssigmentType As Integer, IntAssigmentOrder As Integer
Dim ShowedDayDAte As Date
Dim StrMonthlyAssigmentSql As String
'
'ReadSql2DataGrid(DataGridView1, "SELECT [IDEmpolyee],[EmpolyeeName] from TblEmpolyee")
' DataGridView1.DataSource = Nothing
If RadioBOffReport.Checked = True Then
TblEmpolyeeNames = ReadDataTable("SELECT TblEmpolyee.IDEmpolyee ,TblEmpolyee.EmpolyeeName ,TblRank.RankName from TblEmpolyee left join TblRank TblRank on TblRank.IDRank= TblEmpolyee.IDRank where InService='True' and TblEmpolyee.IDRank > 8 order by TblEmpolyee.IDRank DESC")
Else
TblEmpolyeeNames = ReadDataTable("SELECT TblEmpolyee.IDEmpolyee ,TblEmpolyee.EmpolyeeName ,TblRank.RankName from TblEmpolyee left join TblRank TblRank on TblRank.IDRank= TblEmpolyee.IDRank where InService='True' and TblEmpolyee.IDRank <9 order by TblEmpolyee.IDRank DESC")
End If
'
'DGVMonthlyReport.DataSource = TblEmpolyeeNames
'Exit Sub
If RadioBShowSumAssigment.Checked = True Then
StrMonthlyAssigmentSql = "select TblDailyAssigment.IDDailyAssigment, TblDailyAssigment.AssigmentDate,TblAssigmentTypeGeneral.AssigmentTypeGeneralName ,TblAssigmentTypeGeneral.IDAssigmentTypeGeneral from TblDailyAssigment left join TblAssigmentType TblAssigmentType on TblAssigmentType.IDAssigmentType =TblDailyAssigment.IDAssigmentType left join TblAssigmentTypeGeneral TblAssigmentTypeGeneral on TblAssigmentTypeGeneral.IDAssigmentTypeGeneral=TblAssigmentType.IDAssigmentTypeGeneral"
ReadSql2CStrgArry(StrAssigmentTypeList, "TblAssigmentTypeGeneral", "AssigmentTypeGeneralName", "")
ReadSql2IDCorrMat(IntIDAssigment2Order, "TblAssigmentTypeGeneral", "IDAssigmentTypeGeneral", "")
Else
If RadioBShowGuards.Checked = True Then
'Show the monthly guards
StrMonthlyAssigmentSql = "select TblDailyAssigment.IDDailyAssigment, TblDailyAssigment.AssigmentDate,TblGuard.GuardName ,TblGuard.IDGuard from TblDailyAssigment left join TblAssigmentType TblAssigmentType on TblAssigmentType.IDAssigmentType =TblDailyAssigment.IDAssigmentType left join TblGuard TblGuard on TblGuard.IDGuard= TblDailyAssigment.IDGuard"
ReadSql2CStrgArry(StrAssigmentTypeList, "TblGuard", "GuardName", "")
ReadSql2IDCorrMat(IntIDAssigment2Order, "TblGuard", "IDGuard", "")
Else
'show the monthly detailed assigment
StrMonthlyAssigmentSql = "select TblDailyAssigment.IDDailyAssigment, TblDailyAssigment.AssigmentDate,TblAssigmentType.AssigmentTypeName ,TblAssigmentType.IDAssigmentType from TblDailyAssigment left join TblAssigmentType TblAssigmentType on TblAssigmentType.IDAssigmentType =TblDailyAssigment.IDAssigmentType"
ReadSql2CStrgArry(StrAssigmentTypeList, "TblAssigmentType", "AssigmentTypeName", "")
ReadSql2IDCorrMat(IntIDAssigment2Order, "TblAssigmentType", "IDAssigmentType", "")
End If
End If
'
'set the grid holding detailed empolyee assigment data
IntEmpolyeeNo = TblEmpolyeeNames.Rows.Count
DGVMonthlyReport.Rows.Clear()
DGVMonthlyReport.RowCount = IntEmpolyeeNo
DGVMonthlyReport.ColumnCount = GridColNo + IntMonthLengthDays 'the no of days plus the the number of column used for showing data(name and rank)
'Displaying the name of the day of the week at each day
For s = 1 To IntMonthLengthDays
ShowedDayDAte = CDate(IntYear.ToString + "-" + IntMonth.ToString + "-" + s.ToString)
DGVMonthlyReport.Columns.Item(s + GridColNo - 1).HeaderText = GetArabicDayName(ShowedDayDAte) + "_" + s.ToString
Next
'
'set the grid holding detailed empolyee assigment data
DGVMontlyREportSum.Rows.Clear()
DGVMontlyREportSum.RowCount = IntEmpolyeeNo
DGVMontlyREportSum.ColumnCount = StrAssigmentTypeList.GetUpperBound(0) + GridColNo
For s = 0 To StrAssigmentTypeList.GetUpperBound(0)
DGVMontlyREportSum.Columns.Item(s + 1).HeaderText = StrAssigmentTypeList(s)
Next
For IntEmpolyeeCounter = 0 To IntEmpolyeeNo - 1
IntIdEmpolyee = ReadDataTableItem(TblEmpolyeeNames, 0, IntEmpolyeeCounter)
'Debug.Print(IntIdEmpolyee)
TblMonthAssigment = ReadDataTable(StrMonthlyAssigmentSql + " where IDEmpolyee=" + IntIdEmpolyee.ToString + " and AssigmentDate between " + StrStartDate + " and " + StrEndDate)
If TblMonthAssigment.Rows.Count > 0 Then
For INtDaysCounter = 0 To TblMonthAssigment.Rows.Count - 1
StrAssigmentDate = ReadDataTableItem(TblMonthAssigment, 1, INtDaysCounter)
StrAssigmentDate = Mid(StrAssigmentDate, 9, 2)
IntDayIndex = Val(StrAssigmentDate)
'
DGVMonthlyReport.Item(IntDayIndex + GridColNo - 1, IntEmpolyeeCounter).Value = ReadDataTableItem(TblMonthAssigment, 2, INtDaysCounter)
'
IntAssigmentType = ReadDataTableItem(TblMonthAssigment, 3, INtDaysCounter)
IntAssigmentOrder = IntIDAssigment2Order(IntAssigmentType)
DGVMontlyREportSum.Item(IntAssigmentOrder + GridColNo - 1, IntEmpolyeeCounter).Value = DGVMontlyREportSum.Item(IntAssigmentOrder + GridColNo - 1, IntEmpolyeeCounter).Value + 1
Next
'
End If
DGVMonthlyReport.Item(1, IntEmpolyeeCounter).Value = ReadDataTableItem(TblEmpolyeeNames, 1, IntEmpolyeeCounter)
DGVMonthlyReport.Item(0, IntEmpolyeeCounter).Value = ReadDataTableItem(TblEmpolyeeNames, 2, IntEmpolyeeCounter)
' Exit Sub
'
DGVMontlyREportSum.Item(0, IntEmpolyeeCounter).Value = DGVMonthlyReport.Item(0, IntEmpolyeeCounter).Value
DGVMontlyREportSum.Item(1, IntEmpolyeeCounter).Value = DGVMonthlyReport.Item(1, IntEmpolyeeCounter).Value
Next
'Catch ex As Exception
' MsgBox(ex.Message)
'End Try
DGVMonthlyReport.Columns.Item(0).HeaderText = "Rank"
DGVMonthlyReport.Columns.Item(1).HeaderText = "NAme"
'
End Sub