#
Region " Subs and Functions "
Public FCharmonth As Integer
Public TCharmonth As Integer
Public CharMonth As Integer
Public Charyear As Integer
Public MonthDesc As Integer
Private Sub initDivision()
Dim fDataReader As SqlClient.SqlDataReader
Dim fParam(1) As SqlClient.SqlParameter
fParam(0) =
New SqlClient.SqlParameter("@username", SqlDbType.VarChar, 255, ParameterDirection.Input, False, 0, 0, "", DataRowVersion.Proposed, Session("User"))
fParam(1) =
New SqlClient.SqlParameter("@module", SqlDbType.VarChar, 255, ParameterDirection.Input, False, 0, 0, "", DataRowVersion.Proposed, UCase(Left(Request.QueryString("opt"), 2)))
initConn(1)
fDataReader = initCommand("mvxRPT_GetDivision", 1, 4, fParam)
ddldivision.DataSource = fDataReader
ddldivision.DataTextField = "Description"
ddldivision.DataValueField = "Division"
ddldivision.DataBind()
closeCommand(fDataReader)
closeConn()
End Sub
Private Sub initYearMonth()
Dim fDataReader As SqlClient.SqlDataReader
Dim ftype As Integer
ftype = 5
'Invoice Year
initConn(ftype)
fDataReader = initCommand("SELECT DISTINCT [Rec_Year] as Year FROM view_PurchaseReport_L4 ORDER BY [Rec_Year]", 1, 1)
ddlYear.DataSource = fDataReader
ddlYear.DataTextField = "Year"
ddlYear.DataValueField = "Year"
ddlYear.DataBind()
closeCommand(fDataReader)
closeConn()
ddlYear.SelectedIndex = ddlYear.Items.Count - 1
End Sub
Private Sub setGUI(ByVal pVisible As Boolean)
'Label
lblYear.Visible = pVisible
lblMonth.Visible = pVisible
lbldivision.Visible = pVisible
lblSorting.Visible = pVisible
'Object
ddlYear.Visible = pVisible
ddlMonth.Visible = pVisible
ddldivision.Visible = pVisible
ddlSorting.Visible = pVisible
cmdGenerate.Visible = pVisible
CrystalReportViewer.Visible =
Not pVisible
ctlExport.Visible =
Not pVisible
cmdExport.Visible =
Not pVisible
End Sub
Private Sub initReport()
Dim crConnectionInfo As ConnectionInfo
Dim crDatabase As Database
Dim crTables As Tables
Dim crTable As Table
Dim crTableLogonInfo As TableLogOnInfo
'Setup the connection information structure to be used to log onto the datasource for the report.
crConnectionInfo =
New ConnectionInfo()
With crConnectionInfo
.ServerName = ConfigurationSettings.AppSettings("BPW_LIVE_ODBC")
'Session("Environment") 'physical server name
.DatabaseName = ConfigurationSettings.AppSettings("BPW_LIVE_DB")
'"S65B0ABF." & Session("Environment") 'database name
.UserID = ConfigurationSettings.AppSettings("BPW_UID")
'username
.Password = ConfigurationSettings.AppSettings("BPW_PWD")
End With
If ddlSorting.SelectedItem.Value = 1 Then
crReportDocument =
New Top20Supplier_IndividualSubsidary()
Else
'If ddlSorting.SelectedItem.Value = 2 Then
crReportDocument =
New Top20Supplier_IndividualSubsidary_MTD()
'End If
End If
crDatabase = crReportDocument.Database
crTables = crDatabase.Tables
'Loop through all tables in the report and apply the connection information for each table.
For Each crTable In crTables
crTableLogonInfo = crTable.LogOnInfo
crTableLogonInfo.ConnectionInfo = crConnectionInfo
crTable.ApplyLogOnInfo(crTableLogonInfo)
Next
'crReportDocument.datadefinition.Recordselectionformula = "{FT_KJCF_IntLocalOverseaSales_Ver1.Company}=666 and {FT_KJCF_IntLocalOverseaSales_Ver1.Year} =2005 and {FT_KJCF_IntLocalOverseaSales_Ver1.MonthDesc} ='December'"
CrystalReportViewer.ReportSource = crReportDocument
CrystalReportViewer.Visible =
True
End Sub
Private Sub initParam()
'apply parameter to crystal report
Dim StrQry As String
Dim fParameterValues As New ParameterValues()
Dim fDiscreteValue As New ParameterDiscreteValue()
'Year
'fDiscreteValue.Value = Session("ZZPGRN.ICPUNO.YEAR")
fDiscreteValue.Value = Session("Year")
fParameterValues.Add(fDiscreteValue)
crReportDocument.DataDefinition.ParameterFields("Year").ApplyCurrentValues(fParameterValues)
'division
fDiscreteValue.Value = Session("division")
fParameterValues.Add(fDiscreteValue)
crReportDocument.DataDefinition.ParameterFields("division").ApplyCurrentValues(fParameterValues)
'From Month
'fDiscreteValue.Value = Session("ZZPGRN.ICPUNO.MONTH")
fDiscreteValue.Value = Session("Month")
fParameterValues.Add(fDiscreteValue)
crReportDocument.DataDefinition.ParameterFields("Month").ApplyCurrentValues(fParameterValues)
If ddlSorting.SelectedItem.Value = 1 Then
'Sorting Option
fDiscreteValue.Value = Session("Sorting")
fParameterValues.Add(fDiscreteValue)
crReportDocument.DataDefinition.ParameterFields("SortBy").ApplyCurrentValues(fParameterValues)
Else
fDiscreteValue.Value = Session("User")
fParameterValues.Add(fDiscreteValue)
crReportDocument.DataDefinition.ParameterFields("UserID").ApplyCurrentValues(fParameterValues)
End If
End Sub
Private Sub setParam()
'set parameter to session and retrieve it later
'Session("ZZPGRN.ICPUNO.YEAR") = ddlYear.SelectedItem.Value
Session("Year") = ddlYear.SelectedItem.Value
Session("division") = ddldivision.SelectedItem.Value
'Session("ZZPGRN.ICPUNO.MONTH") = ddlMonth.SelectedItem.Value
Session("Month") = ddlMonth.SelectedItem.Value
Session("Sorting") = ddlSorting.SelectedItem.Value
End Sub
Function MonthChar(ByVal MonthDesc)
Select Case MonthDesc
Case Is = "January"
CharMonth = 1
Case Is = "February"
CharMonth = 2
Case Is = "March"
CharMonth = 3
Case Is = "April"
CharMonth = 4
Case Is = "May"
CharMonth = 5
Case Is = "June"
CharMonth = 6
Case Is = "July"
CharMonth = 7
Case Is = "August"
CharMonth = 8
Case Is = "September"
CharMonth = 9
Case Is = "October"
CharMonth = 10
Case Is = "November"
CharMonth = 11
Case Is = "December"
CharMonth = 12
End Select
End Function
Private Sub exportFile()
'export output to excel, doc or pdf
Dim fFileName As String = mvx_rpt.report.exportFile(crReportDocument, CStr(Request.QueryString("opt")), ctlExport.ExportFormat, Session("User"))
Response.Redirect("./export/" & fFileName)
End Sub
#
End Region
End
Class
P/S: this is the vb.net coding
---------------------------------------------------------------------------------------------
I not sure how to solve the problem, please advise.
thanks in advance.
IP Logged