Coding VB.net Dan Query SQL server (Catatan pribadi) part 3

Contoh report on screen

Imports CrystalDecisions.CrystalReports.Engine
Imports System.Runtime.Serialization
Imports DevExpress.XtraBars.Docking
Imports System
Imports System.Collections
Imports System.Collections.Specialized
Imports CrystalDecisions.Shared
Imports System.Xml
Imports System.IO
Imports GHRMSReports2017.Connection.Services.Database
Imports System.Threading
Imports DevExpress.XtraPrinting
Imports DevExpress.XtraGrid.Views.Base
Imports System.Globalization
Imports Excel = Microsoft.Office.Interop.Excel
Imports DevExpress.XtraGrid.Views.Grid

Public Class frmReportSIP
    Private allowedSource, AllowedWarehouse, AllowedEmployeeType, AllowedDepartment, AllowedPosition, AllowedEmployee As String
    Dim rpHidden = DevExpress.XtraBars.BarItemVisibility.Never
    Private parentribbonx As DevExpress.XtraBars.Ribbon.RibbonControl
    Private ModulCode As String = "HM1"
    Dim modetampilan As Integer
    Public Enum LayoutState
        OnScreen
        Report
        Load
        reportLembur
        reportAbsensi
        LaporanGaji
        payroll
    End Enum
    Private Sub SetSecurityButtonAccess()
        Try
            btnPreview.Enabled = False
            btnExport.Enabled = False

            Dim dt As New DataTable
            dt = Sp_MDGETUserModuleFunctionTableAdapter.GetData(My.Settings.Username, ModulCode, My.Settings.WHCode)
            Dim dv As DataView = dt.DefaultView
            dv.RowFilter = String.Format("ModulCode = '{0}'", ModulCode)
            If dv.Count > 0 Then
                For Each ctl As DevExpress.XtraBars.BarItem In RibbonControl.Items
                    For i As Integer = 0 To dv.Count - 1
                        If ctl.Tag = dv.Item(i).Row("idx") Then
                            ctl.Enabled = True
                        End If
                    Next
                Next
            End If
        Catch ex As Exception
            AlertControl1.Show(Me, "Error! Check on SetSecurityButtonAccess()", ex.Message)
        End Try
    End Sub
    Private Sub DisplayButtons(ByVal mode As LayoutState)
        Select Case mode
            Case LayoutState.OnScreen
                btnBack.Visibility = rpHidden
                btnPreview.Enabled = True
                btnPrint.Enabled = False
                btnExcel.Enabled = True
                btnPDF.Enabled = False
            Case LayoutState.Report
                btnBack.Visibility = rpHidden
                btnPreview.Enabled = True
                btnPrint.Enabled = True
                btnExcel.Enabled = False
                btnPDF.Enabled = True
            Case LayoutState.Load
                btnBack.Visibility = rpHidden
                btnPreview.Enabled = True
                btnPrint.Enabled = False
                btnExcel.Enabled = True
                btnPDF.Enabled = False
            Case LayoutState.reportLembur
                btnBack.Visibility = rpHidden
                btnPreview.Enabled = True
                btnPrint.Enabled = False
                btnExcel.Enabled = True
                btnPDF.Enabled = False
            Case LayoutState.reportAbsensi
                btnBack.Visibility = rpHidden
                btnPreview.Enabled = True
                btnPrint.Enabled = False
                btnExcel.Enabled = True
                btnPDF.Enabled = False
            Case LayoutState.LaporanGaji
                btnBack.Visibility = rpHidden
                btnPreview.Enabled = True
                btnPrint.Enabled = False
                btnExcel.Enabled = True
                btnPDF.Enabled = False
            Case LayoutState.payroll
                btnBack.Visibility = rpHidden
                btnPreview.Enabled = True
                btnPrint.Enabled = False
                btnExcel.Enabled = True
                btnPDF.Enabled = False

        End Select
        _layoutState = mode
    End Sub
    Private Sub SwitchRibbon(ByVal mode As LayoutState)
        Select Case mode
            Case LayoutState.OnScreen
                DisplayButtons(mode)
            Case LayoutState.Report
                DisplayButtons(mode)
            Case LayoutState.Load
                DisplayButtons(mode)
            Case LayoutState.reportLembur
                DisplayButtons(mode)
            Case LayoutState.reportAbsensi
                DisplayButtons(mode)
            Case LayoutState.LaporanGaji
                DisplayButtons(mode)
            Case LayoutState.payroll
                DisplayButtons(mode)
        End Select
        _layoutState = mode
    End Sub
    Private Sub HideAllLayouts()
        lcRptCRAbsensiSIP.Visible = False
        lcRptCRLapGajiSIP.Visible = False
        lcRptCRLemburSIP.Visible = False
        lcRptCRPayrollSIP.Visible = False

    End Sub
    Public Sub SwitchLayout(ByVal sender As Object, ByVal e As System.EventArgs, ByVal mode As LayoutState)
        Select Case mode
            Case LayoutState.OnScreen
                CRViewer.Visible = False
                CRViewer.Dock = DockStyle.None
                HideAllLayouts()
            Case LayoutState.Report
                CRViewer.Dock = DockStyle.Fill
                CRViewer.Visible = True
                HideAllLayouts()
            Case LayoutState.Load
                CRViewer.Dock = DockStyle.Fill
                CRViewer.Visible = True
                HideAllLayouts()
            Case LayoutState.reportLembur
                CRViewer.Dock = DockStyle.Fill
                CRViewer.Visible = True
                HideAllLayouts()
                lcRptCRLemburSIP.Visible = True
                lcRptCRLemburSIP.Dock = DockStyle.Fill
            Case LayoutState.reportAbsensi
                CRViewer.Dock = DockStyle.Fill
                CRViewer.Visible = True
                HideAllLayouts()
                lcRptCRAbsensiSIP.Visible = True
                lcRptCRAbsensiSIP.Dock = DockStyle.Fill
            Case LayoutState.LaporanGaji
                CRViewer.Dock = DockStyle.Fill
                CRViewer.Visible = True
                HideAllLayouts()
                lcRptCRLapGajiSIP.Visible = True
                lcRptCRLapGajiSIP.Dock = DockStyle.Fill
            Case LayoutState.payroll
                CRViewer.Dock = DockStyle.Fill
                CRViewer.Visible = True
                HideAllLayouts()
                lcRptCRPayrollSIP.Visible = True
                lcRptCRPayrollSIP.Dock = DockStyle.Fill
        End Select
        DxErrorProvider1.ClearErrors()

        SwitchRibbon(mode)
        Try
            parentribbonx.UnMergeRibbon()
            parentribbonx.MergeRibbon(RibbonControl)
        Catch ex As Exception
        End Try
    End Sub
    Private Enum GenGetMode
        WithoutAll = 0
        WithAll = 1
    End Enum

    Private _layoutState As LayoutState
    Private Sub btnExit_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs)
        Me.Close()
    End Sub
    Private Function getAllowedSource()
        Dim res As String = ""
        Me.Sp_GenGetAllowedSourceWarehouseTableAdapter1.Fill(Me.DSGHRMSReports.sp_GenGetAllowedSourceWarehouse, My.Settings.Username, My.Settings.ModuleID)

        If DSGHRMSReports.sp_GenGetAllowedSourceWarehouse.Rows.Count > 0 Then
            For Each dr As DataRow In DSGHRMSReports.sp_GenGetAllowedSourceWarehouse
                res = res & dr.Item(0) & ","
            Next
            res = Microsoft.VisualBasic.Strings.Left(res, Len(res) - 1)
        End If
        Return res
    End Function

    Private Function getAllowedWarehouse()
        Dim res As String = ""
        Me.Sp_GenGetAllowedSourceWarehouseTableAdapter1.Fill(Me.DSGHRMSReports.sp_GenGetAllowedSourceWarehouse, My.Settings.Username, My.Settings.ModuleID)

        If DSGHRMSReports.sp_GenGetAllowedSourceWarehouse.Rows.Count > 0 Then
            For Each dr As DataRow In DSGHRMSReports.sp_GenGetAllowedSourceWarehouse
                res = res & dr.Item(1) & ","
            Next
            res = Microsoft.VisualBasic.Strings.Left(res, Len(res) - 1)
        End If
        Return res
    End Function

    Private Function getAllowedEmployeeType()
        Dim res As String = ""
        Me.Sp_GenGetAllowedEmployeeTypeTableAdapter1.Fill(Me.DSGHRMSReports.sp_GenGetAllowedEmployeeType, My.Settings.Username)

        If DSGHRMSReports.sp_GenGetAllowedEmployeeType.Rows.Count > 0 Then
            For Each dr As DataRow In DSGHRMSReports.sp_GenGetAllowedEmployeeType
                res = res & dr.Item(0) & ","
            Next
            res = Microsoft.VisualBasic.Strings.Left(res, Len(res) - 1)
        End If
        Return res
    End Function

    Private Function getAllowedDepartment()
        Dim res As String = ""
        Me.Sp_GenGetAllowedDepartmentTableAdapter1.Fill(Me.DSGHRMSReports.sp_GenGetAllowedDepartment, My.Settings.Username)

        If DSGHRMSReports.sp_GenGetAllowedDepartment.Rows.Count > 0 Then
            For Each dr As DataRow In DSGHRMSReports.sp_GenGetAllowedDepartment
                res = res & dr.Item(0) & ","
            Next
            res = Microsoft.VisualBasic.Strings.Left(res, Len(res) - 1)
        End If
        Return res
    End Function

    Private Function getAllowedPosition()
        Dim res As String = ""
        Me.Sp_GenGetAllowedPositionTableAdapter1.Fill(Me.DSGHRMSReports.sp_GenGetAllowedPosition, My.Settings.Username)

        If DSGHRMSReports.sp_GenGetAllowedPosition.Rows.Count > 0 Then
            For Each dr As DataRow In DSGHRMSReports.sp_GenGetAllowedPosition
                res = res & dr.Item(0) & ","
            Next
            res = Microsoft.VisualBasic.Strings.Left(res, Len(res) - 1)
        End If
        Return res
    End Function
    Private Sub frmReportSIP_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try

            'allowedSource = getAllowedSource()
            SwitchLayout(sender, e, LayoutState.Load)

            Dim dtTitle As New DataTable
            dtTitle = SpGenGetFormTitleTableAdapter.GetData(My.Settings.GIIConnectionString, ModulCode.ToString)
            Me.Text = dtTitle.Rows(0).Item(0)

            'GetDefaultSetting()
            'nbiPresence.Visible = presence
            'nbiPresenceMonthly.Visible = presence
            'nbiLeave.Visible = Leave

            SetSecurityButtonAccess()
            '--BEGIN-- ADD BY ALEXS 23/02/2018
            allowedSource = getAllowedSource()
            AllowedWarehouse = getAllowedWarehouse()
            AllowedEmployeeType = getAllowedEmployeeType()
            AllowedDepartment = getAllowedDepartment()
            AllowedPosition = getAllowedPosition()
            AllowedEmployee = QueriesTableAdapter1.sp_GenGetAllowedEmployee(My.Settings.Username)
            '--END-- ADD BY ALEXS 23/02/2018
            'Calculate = 0
            Me.Sp_RptCRAbsensiSIPTableAdapter.Fill(DSGHRMSReports.sp_RptCRAbsensiSIP)
            Me.Sp_RptCRLapGajiSIPTableAdapter.Fill(DSGHRMSReports.sp_RptCRLapGajiSIP)
            Me.Sp_RptCRLemburSIPTableAdapter.Fill(DSGHRMSReports.sp_RptCRLemburSIP)
            Me.Sp_RptCRPayrollSIPTableAdapter.Fill(DSGHRMSReports.sp_RptCRPayrollSIP)
            dpFilter.Visibility = DockVisibility.Hidden
            'lueWarehouse.ItemIndex = 0
            'Me.Sp_GenGetGroupNewTableAdapter.Fill(Me.DSGHRMSReports.sp_GenGetGroupNew, GenGetMode.WithAll, lueSource.EditValue, lueWarehouse.EditValue, lueDepartment.EditValue, "%", lueGroup.EditValue, allowedSource)
            'dpFilter.Visibility = DockVisibility.Hidden
            'paramLoad = True
            'deStartDate.EditValue = Today
            'deEndDate.EditValue = Today
            'lueSource.EditValue = My.Settings.Source
            'lueDepartment.ItemIndex = 0
            'lciStartDate.Visibility = True
            'lciEndDate.Visibility = True
            'lueGroup.ItemIndex = 0
            'lueYear.ItemIndex = 0
            'GCopyConsole()
            'HideAllLayouts()
        Catch ex As Exception
            AlertControl1.Show(Me, "Error! Check on frmRptAttendance_Load()", ex.Message)
        End Try

        panelContainer1.ActiveChildIndex = 0
    End Sub

    Private Sub btnExit_ItemClick_1(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles btnExit.ItemClick
        Me.Close()
    End Sub

    Private Sub btnPreview_ItemClick_1(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles btnPreview.ItemClick
        Try
            SwitchLayout(sender, e, LayoutState.Report)
            If modetampilan = 1 Then
                CRViewer.Refresh()
                Dim cryRpt As New ReportDocument
                cryRpt.Load(Application.StartupPath & "\Report\GHRMS\sp_RptCRLemburSIP.rpt") 'Lokasi file report
                'cryRpt.SetParameterValue("@awal", txtAwal.EditValue) 'Parameter
                'cryRpt.SetParameterValue("@akhir", txtakhir.EditValue)
                CRViewer.ReportSource = cryRpt
                CRViewer.Refresh()
            ElseIf modetampilan = 2 Then
                CRViewer.Refresh()
                Dim cryRpt As New ReportDocument
                cryRpt.Load(Application.StartupPath & "\Report\GHRMS\sp_RptCRAbsensiSIP.rpt") 'Lokasi file report
                'cryRpt.SetParameterValue("@awal", txtAwal.EditValue) 'Parameter
                'cryRpt.SetParameterValue("@akhir", txtakhir.EditValue)
                CRViewer.ReportSource = cryRpt
                CRViewer.Refresh()
            ElseIf modetampilan = 3 Then
                CRViewer.Refresh()
                Dim cryRpt As New ReportDocument
                cryRpt.Load(Application.StartupPath & "\Report\GHRMS\sp_RptCRLapGajiSIP.rpt") 'Lokasi file report
                'cryRpt.SetParameterValue("@awal", txtAwal.EditValue) 'Parameter
                'cryRpt.SetParameterValue("@akhir", txtakhir.EditValue)
                CRViewer.ReportSource = cryRpt
                CRViewer.Refresh()
            ElseIf modetampilan = 4 Then
                CRViewer.Refresh()
                Dim cryRpt As New ReportDocument
                cryRpt.Load(Application.StartupPath & "\Report\GHRMS\sp_RptCRPayrollSIP.rpt") 'Lokasi file report
                'cryRpt.SetParameterValue("@awal", txtAwal.EditValue) 'Parameter
                'cryRpt.SetParameterValue("@akhir", txtakhir.EditValue)
                CRViewer.ReportSource = cryRpt
                CRViewer.Refresh()
            End If
        Catch ex As Exception
            AlertControl1.Show(Me, "Error! Check on btnPreview_ItemClick_1()", ex.Message)
        End Try
     
    End Sub

    Private Sub btnPrint_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles btnPrint.ItemClick
        CRViewer.PrintReport()
    End Sub

    Private Sub nbiReportLembur_LinkClicked(ByVal sender As System.Object, ByVal e As DevExpress.XtraNavBar.NavBarLinkEventArgs) Handles nbiReportLembur.LinkClicked
        Try
            SwitchLayout(sender, e, LayoutState.reportLembur)
            dpFilter.Visibility = DockVisibility.Visible
            modetampilan = 1
        Catch ex As Exception

        End Try
    End Sub

    Private Sub nbiReportAbsensi_LinkClicked(ByVal sender As System.Object, ByVal e As DevExpress.XtraNavBar.NavBarLinkEventArgs) Handles nbiReportAbsensi.LinkClicked
        Try
            SwitchLayout(sender, e, LayoutState.reportAbsensi)
            dpFilter.Visibility = DockVisibility.Visible
            modetampilan = 2
        Catch ex As Exception

        End Try
    End Sub

    Private Sub nbiLaporanGaji_LinkClicked(ByVal sender As System.Object, ByVal e As DevExpress.XtraNavBar.NavBarLinkEventArgs) Handles nbiLaporanGaji.LinkClicked
        Try
            SwitchLayout(sender, e, LayoutState.LaporanGaji)
            dpFilter.Visibility = DockVisibility.Visible
            modetampilan = 3
        Catch ex As Exception

        End Try
    End Sub

    Private Sub nbiPayroll_LinkClicked(ByVal sender As System.Object, ByVal e As DevExpress.XtraNavBar.NavBarLinkEventArgs) Handles nbiPayroll.LinkClicked
        Try
            SwitchLayout(sender, e, LayoutState.payroll)
            dpFilter.Visibility = DockVisibility.Visible
            modetampilan = 4
        Catch ex As Exception

        End Try
    End Sub
End Class

part 1 klik di sini
part 2 klik di sini

Coding VB.net Dan Query SQL server (Catatan pribadi) part 2

'untuk mendapatkan data di kolom user berdasarkan value IP pada box machinelookupedit 

Dim temp As DataTable
 Dim port As String
temp = Sp_GetMachineNameTableAdapter.GetData(MachineNameComboBoxEdit.EditValue, getAllowedSource)
port = temp.Select(String.Format("IP = '{0}'", MachineNameLookUpEdit.EditValue))(0)("User")

'tambahan setting kolom gridview riki 04 01 2019

            GridView1.BestFitColumns()
            GridView1.Columns("No").OptionsColumn.AllowFocus = False
            GridView1.Columns("No").OptionsColumn.ReadOnly = True
            GridView1.Columns("No").AppearanceHeader.Font = New System.Drawing.Font("Tahoma",              8.25!, System.Drawing.FontStyle.Bold)
            GridView1.Columns("No").AppearanceHeader.TextOptions.HAlignment =                                          DevExpress.Utils.HorzAlignment.Center

           GridView1.Columns("Production Date").DisplayFormat.FormatType =                                        DevExpress.Utils.FormatType.DateTime
            GridView1.Columns("Production Date").DisplayFormat.FormatString = "dd-MMM-yyyy"
GridView1.Columns("Qty Pack").DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric
            GridView1.Columns("Qty Pack").DisplayFormat.FormatString = "n2"
            GridView1.Columns("Qty Pack").ColumnEdit = reponu

Qery di gunakan untuk membuat nomer row otomatis

ROW_NUMBER() OVER ( ORDER BY me.id) AS [No.]

 ORDER BY timestamp desc : di gunakan untuk mencari data dgn urutan terbesar menuju ke kecil

 ORDER BY timestamp asc : di gunakan untuk mencari data dengan urutan terkecil ke besar

Part 1 klik di sini
part 3 klik di sini