' PROGRAM MENGHITUNG LEMBUR ' MANAF TEA Private Sub cmd_dept_Click() Dim Conn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim judul(0) As String, sSQL As String Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBSIUDIN;Data Source=CSIS_DEV1" Conn.Open judul(0) = "Department" sSQL = "SELECT dept,empname FROM dbsiudin.dbo.t_empmaster" rs.CursorLocation = adUseClient rs.Open sSQL, Conn, adOpenStatic txt_dept.Text = gridBrowse(rs, txt_dept, judul, sSQL) End Sub Private Sub cmd_nik_Click() Dim Conn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim judul(1) As String, sSQL As String Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBSIUDIN;Data Source=CSIS_DEV1" Conn.Open judul(0) = "NIK" judul(1) = "NAMA" sSQL = "SELECT compnumber,empname FROM dbsiudin.dbo.t_empmaster" rs.CursorLocation = adUseClient rs.Open sSQL, Conn, adOpenStatic txt_nik.Text = gridBrowse(rs, txt_nik, judul, sSQL) End Sub Private Sub cmdclose_Click() Unload Me End Sub Private Sub cmdprint_Click() If optnik.Value Then crp_lembur.SQLQuery = "SELECT TSPSOVT.I_EMP, TSPSOVT.D_EMP_OVT, TSPSOVT.I_EMP_REF, TSPSOVT.C_PGM, t_empmaster.EmpName, t_empmaster.Dept,TspsTimeAcc.q_wt_sprlafter , TspsTimeAcc.d_emp_in, TspsTimeAcc.d_emp_out, TspsTimeAcc.q_wt_totalovt From dbsiudin.dbo.TSPSOVT TSPSOVT, dbsiudin.dbo.t_empmaster t_empmaster, dbsiudin.dbo.TspsTimeAcc TspsTimeAcc Where TSPSOVT.I_EMP = t_empmaster.CompNumber AND TSPSOVT.I_EMP = TspsTimeAcc.i_emp AND TSPSOVT.D_EMP_OVT = TspsTimeAcc.d_wt_calc and TSPSOVT.i_emp= '" & txt_nik.Text & "' and TSPSOVT.D_EMP_OVT = '" & Format(DTawal.Value, "dd/mm/yyyy") & "' and TSPSOVT.D_EMP_OVT = '" & Format(DTahir.Value, "dd/mm/yyyy") & " ' Order By TSPSOVT.i_emp Asc" ElseIf optdepartment.Value Then crp_lembur.SQLQuery = "SELECT TSPSOVT.I_EMP, TSPSOVT.D_EMP_OVT, TSPSOVT.I_EMP_REF, TSPSOVT.C_PGM, t_empmaster.EmpName, t_empmaster.Dept,TspsTimeAcc.q_wt_sprlafter , TspsTimeAcc.d_emp_in, TspsTimeAcc.d_emp_out, TspsTimeAcc.q_wt_totalovt From dbsiudin.dbo.TSPSOVT TSPSOVT, dbsiudin.dbo.t_empmaster t_empmaster, dbsiudin.dbo.TspsTimeAcc TspsTimeAcc Where TSPSOVT.I_EMP = t_empmaster.CompNumber AND TSPSOVT.I_EMP =
TspsTimeAcc.i_emp AND TSPSOVT.D_EMP_OVT = TspsTimeAcc.d_wt_calc and t_empmaster.Dept= '" & txt_dept.Text & "' and TSPSOVT.D_EMP_OVT > '" & Format(DTawal.Value, "mm/dd/yyyy") & "' and TSPSOVT.D_EMP_OVT < '" & Format(DTahir.Value, "mm/dd/yyyy") & "' Order By t_empmaster.dept Asc" ElseIf opt_prog.Value Then crp_lembur.SQLQuery = "SELECT TSPSOVT.I_EMP, TSPSOVT.D_EMP_OVT, TSPSOVT.I_EMP_REF, TSPSOVT.C_PGM, t_empmaster.EmpName, t_empmaster.Dept,TspsTimeAcc.q_wt_sprlafter , TspsTimeAcc.d_emp_in, TspsTimeAcc.d_emp_out, TspsTimeAcc.q_wt_totalovt From dbsiudin.dbo.TSPSOVT TSPSOVT, dbsiudin.dbo.t_empmaster t_empmaster, dbsiudin.dbo.TspsTimeAcc TspsTimeAcc Where TSPSOVT.I_EMP = t_empmaster.CompNumber AND TSPSOVT.I_EMP = TspsTimeAcc.i_emp AND TSPSOVT.D_EMP_OVT = TspsTimeAcc.d_wt_calc and TSPSOVT.C_PGM= '" & txt_prog.Text & "' and TSPSOVT.D_EMP_OVT > '" & Format(DTawal.Value, "mm/dd/yyyy") & "' and TSPSOVT.D_EMP_OVT < '" & Format(DTahir.Value, "mm/dd/yyyy") & "' Order By TSPSOVT.c_pgm Asc" End If crp_lembur.ReportFileName = App.Path & "\lembur2.rpt" crp_lembur.Connect = "dsn= csis_dev1;uid= ;pwd= " crp_lembur.RetrieveDataFiles crp_lembur.Action = 1 End Sub Private Sub cmdprog_Click() Dim Conn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim judul(0) As String, sSQL As String Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBSIUDIN;Data Source=CSIS_DEV1" Conn.Open judul(0) = "Program" sSQL = "SELECT c_pgm FROM dbsiudin.dbo.tspsovt" rs.CursorLocation = adUseClient rs.Open sSQL, Conn, adOpenStatic txt_prog.Text = gridBrowse(rs, txt_prog, judul, sSQL) End Sub Private Sub Form_Load() ' Dim i As Long ' For i = 1900 To 2100 ' cmbyear.AddItem i ' Next ' DTawal.Value = Format(Date, "mm/dd/yyyy") ' DTahir.Value = Format(Date, "mm/dd/yyyy") End Sub Private Sub opt_prog_Click() txt_nik.Enabled = False cmd_nik.Enabled = False txt_dept.Enabled = False cmd_dept.Enabled = False txt_prog.Enabled = True cmdprog.Enabled = True End Sub Private Sub optdepartment_Click()
txt_nik.Enabled = False cmd_nik.Enabled = False txt_dept.Enabled = True cmd_dept.Enabled = True txt_prog.Enabled = False cmdprog.Enabled = False End Sub Private Sub optnik_Click() txt_nik.Enabled = True cmd_nik.Enabled = True txt_dept.Enabled = False cmd_dept.Enabled = False txt_prog.Enabled = False cmdprog.Enabled = False End Sub Private Sub txt_dept_GotFocus() Fokus txt_dept End Sub Private Sub txt_dept_Validate(Cancel As Boolean) If txt_dept.Text = "" Then cancle = True MsgBox "Department (Dept) don't empty!", , "Empty..." Else Dim Rst_empmaster As New ADODB.Recordset Connect_CSIS Rst_empmaster.CursorType = adOpenStatic With Rst_empmaster .Open "SELECT * FROM dbsiudin.dbo.t_empmaster WHERE dept ='" & Trim(txt_dept.Text) & "'", kcsis If .EOF Then MsgBox " Data tidak ketemu" End If End With End If End Sub Private Sub txt_nik_GotFocus() Fokus txt_nik End Sub Private Sub txt_nik_Validate(Cancel As Boolean) If txt_nik.Text = "" Then cancle = True MsgBox "Emp (NIK) don't empty!", , "Empty..." Else Dim Rst_empmaster As New ADODB.Recordset Connect_CSIS Rst_empmaster.CursorType = adOpenStatic With Rst_empmaster .Open "SELECT * FROM dbsiudin.dbo.t_empmaster WHERE compnumber ='" & Trim(txt_nik.Text) & "'", kcsis If .EOF Then MsgBox " Data tidak ketemu" End If
End With End If End Sub Private Sub txt_prog_Change() Fokus txt_prog End Sub Private Sub txt_prog_Validate(Cancel As Boolean) If txt_prog.Text = "" Then cancle = True MsgBox "Program (c_pgm) don't empty!", , "Empty..." Else Dim Rstspsovt As New ADODB.Recordset Connect_CSIS Rstspsovt.CursorType = adOpenStatic With Rstspsovt .Open "SELECT * FROM dbsiudin.dbo.tspsovt WHERE c_pgm ='" & Trim(txt_prog.Text) & "'", kcsis If .EOF Then MsgBox " Data tidak ketemu" End If End With End If End Sub Public kcsis As ADODB.Connection Public Function Connect_CSIS() As Boolean Connect_CSIS = False Set kcsis = New ADODB.Connection kcsis.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBSIUDIN;Data Source=CSIS_DEV1" kcsis.Open Connect_CSIS = True Screen.MousePointer = vbDefault Exit Function ErrorExecute: Screen.MousePointer = vbDefault MsgBox "Error =" & Err.Description & Err.Number End Function Sub Fokus(ByRef objtext As Object) objtext.SetFocus objtext.SelStart = 0 objtext.SelLength = Len(objtext.Text) End Sub