Belajar Pemograman Yuuk (VB 6.0, SQL Server, PostgreSQL, PHP)

Form Reporting data, using crystall report

Option Explicit
Dim Cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim indeks As Integer

Private Sub Form_Load()
'Deklarasikan variabel koneksi
Set Cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset

With Cnn
.ConnectionString = "provider=SQLOLEDB;" & _
"Data Source=10.11.12.56,1433/Kenari;initial Catalog = Pulsa;" & _
"Trusted Connection=yes;User ID=pulsa; Password=Pulsa123"
.Open
End With
rs.CursorLocation = adUseClient
rs1.Open "tbl_Branch", Cnn

cab.Clear
Dim Msql As String
Msql = "SELECT Branch_ID FROM tbl_Branch"
While Not rs1.EOF: cab.AddItem rs1!Branch_ID: rs1.MoveNext: Wend
If cab.ListCount <> 0 Then cab.ListIndex = 0

Me.Top = (Main.ScaleHeight - Me.ScaleHeight) / 2
Me.Left = (Main.ScaleWidth - Me.ScaleWidth) / 2

End Sub

Private Sub selesai_Click()
Unload Me
End Sub

Sub Lihat_Report(laporan As String, saring As String)
With Report1
.LogOnServer "p2ssql.dll", "10.11.12.56", "Pulsa", "pulsa", "Pulsa123"
.ReportFileName = App.Path & "\Report\" & laporan & ".rpt"
.WindowState = crptMaximized
.SelectionFormula = saring
.ReportTitle = "Dari tanggal " & dt_dari.Value & " sampai dengan tanggal " & dt_sampai.Value
.Action = 1
.Reset
End With
End Sub

Private Sub cetak_Click()
Select Case indeks
Case 0
'Top 10 Produksi Mitra - Nasional
Lihat_Report "top10-mitra", "SELECT TOP 10 tbl_Production.Branch_ID, tbl_Branch.Branch_Name, tbl_Production.Agent_ID, tbl_Agent.Agent_Name, " & _
" tbl_Production.Product_ID, tbl_Product.Prdct_Desc, COUNT(tbl_Production.CardNo) AS Kartu " & _
" FROM tbl_Production INNER JOIN tbl_Agent ON tbl_Production.Agent_ID = tbl_Agent.Agent_ID INNER JOIN " & _
" tbl_Branch ON tbl_Production.Branch_ID = tbl_Branch.Branch_ID INNER JOIN " & _
" tbl_Product ON tbl_Production.Product_ID = tbl_Product.Product_ID " & _
" Where tbl_Production.Transc_Date IN DATE (" & Format(dt_dari.Value, "YYYY,MM,DD") & ")" & _
" TO DATE (" & Format(dt_sampai.Value, "YYYY,MM,DD") & ")" & _
" GROUP BY tbl_Production.Branch_ID, tbl_Branch.Branch_Name, tbl_Production.Agent_ID, tbl_Agent.Agent_Name, " & _
" tbl_Production.Product_ID , tbl_Product.Prdct_Desc " & _
" ORDER BY Kartu DESC"
Case 1
'Top 10 Produksi Cabang
Lihat_Report "gesa-add", "{gesa_add.tanggal} IN DATE (" & Format(dt_dari.Value, "YYYY,MM,DD") & ")" & _
"TO DATE (" & Format(dt_sampai.Value, "YYYY,MM,DD") & ")"
Case 2
'Top N Produksi Mitra - Nasional
Lihat_Report "preminet-konvensional", "{gesa_netpremi_konv.tgl} IN DATE (" & Format(dt_dari.Value, "YYYY,MM,DD") & ")" & _
"TO DATE (" & Format(dt_sampai.Value, "YYYY,MM,DD") & ")"
Case 3
'Top N Produksi Cabang
Lihat_Report "preminet-syariah", "{gesa_netpremi_syariah.tgl} IN DATE (" & Format(dt_dari.Value, "YYYY,MM,DD") & ")" & _
"TO DATE (" & Format(dt_sampai.Value, "YYYY,MM,DD") & ")"
Case 4
'Top N Produksi Mitra Per Cabang

Case 5
'Top N Produksi Mitra Per Produk

End Select
End Sub

Private Sub Form_Activate()
dt_dari.Value = Format(Now, 1 & "/mmmm/yyyy")
dt_sampai.Value = DateAdd("m", DateDiff("m", 0, Now), 1)
End Sub

Private Sub lap_Click(Index As Integer)
indeks = Index
Select Case Index
Case 0
'Top 10 Produksi Mitra - Nasional
cab.Enabled = False: Jum.Enabled = False
dt_dari.Enabled = True: dt_sampai.Enabled = True
Case 1
'Top 10 Produksi Cabang
cab.Enabled = False: Jum.Enabled = False
dt_dari.Enabled = True: dt_sampai.Enabled = True
Case 2
'Top N Produksi Mitra - Nasional
cab.Enabled = False: Jum.Enabled = True
dt_dari.Enabled = True: dt_sampai.Enabled = True
Case 3
'Top N Produksi Cabang
cab.Enabled = False: Jum.Enabled = True
dt_dari.Enabled = True: dt_sampai.Enabled = True
Case 4
'Top N Produksi Mitra Per Cabang
cab.Enabled = True: Jum.Enabled = True
dt_dari.Enabled = True: dt_sampai.Enabled = True
Case 5
'Top N Produksi Mitra Per Produk
cab.Enabled = False: Jum.Enabled = True
dt_dari.Enabled = True: dt_sampai.Enabled = True
End Select
End Sub