1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
Private Sub bonus()
Dim min As Decimal
For i = 0 To DataGridView1.RowCount - 1
min = getmatrix(CType(DataGridView1.Rows(i).Cells("Workplace").Value, String), CType(DataGridView1.Rows(i).Cells("E2").Value, Decimal))
DataGridView1.Rows(i).Cells("level").Value = min * (DataGridView1.Rows(i).Cells(9).Value / 60)
Next
End Sub
Private Function getmatrix(ByVal workplace As String, ByVal value As Decimal)
conn = New SqlConnection()
conn = connexion()
Dim min As Decimal
Dim sql As String = ("select workplace,workplacegroup,cost_center,area,niveau,valeur,amount,month from matrice where workplace='" & workplace & "'")
Dim exec As SqlCommand = New SqlCommand(sql, conn)
Dim dreader As SqlDataReader = exec.ExecuteReader
While (dreader.Read())
If (dreader(0) = workplace) Then
If (value >= dreader(5)) Then
min = dreader(6)
Else
min = 0
End If
End If
End While
Return min
conn.Close()
End Function
Private Sub report_efficiency_bydate(ByVal from_convert As String, ByVal to_convert As String)
Try
conn = connexion()
sql = "select a.code 'Area',cc.code 'Cctr N°',w.workcell_group 'Workplace Group',oc.workcell 'Workplace',oc.matricule 'Matricule',SUM(oc.normal_time) 'Normal time',SUM(oc.overtime) 'Overtime',SUM(oc.attendance_time) 'Worktime',SUM(oc.downtime) 'Downtime',SUM(oc.productional_time) 'Productional time',SUM(oc.produced_minute) 'Produced minutes',ROUND((cast(NULLIF(sum(oc.produced_minute),0) as float)/sum(oc.attendance_time))*100,2)E1,ROUND((cast(sum(oc.produced_minute) as float)/NULLIF(sum(oc.productional_time),0))*100,2)E2 from operator_card oc INNER JOIN work_cell w ON oc.workcell = w.code JOIN cost_center cc ON w.cost_center = cc.code JOIN area a on cc.area= a.code inner join employe emp on emp.matricule=oc.matricule where oc.date BETWEEN '" & from_convert & "' AND '" & to_convert & "' group by oc.matricule,a.code,cc.code,w.workcell_group,oc.workcell having SUM(oc.produced_minute) > 0 order by a.code"
dts.Clear()
dta = New SqlDataAdapter(sql, conn)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(dta)
dta.Fill(dts, "report")
dtt = dts.Tables("report")
DataGridView1.DataSource = dtt
DataGridView1.Columns.Add("level", "Level")
End Sub
Private Sub run_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles run.Click
Dim date_from As Date = Convert.ToDateTime(from.Value.Date)
Dim from_convert As String = date_from.ToString("yyyy-MM-dd")
Dim date_to As Date = Convert.ToDateTime(to_date.Value.Date)
Dim to_convert As String = date_to.ToString("yyyy-MM-dd")
report_efficiency_bydate(from_convert, to_convert)
Dim thread As New Thread(AddressOf bonus)
thread.Start()
End Sub |
