Imports System.Data
Imports Microsoft.VisualBasic
Public Class ClassStatistics

    Public Shared Function sCallStoreProcedure() As Boolean
        Dim dbcon As New Data.OleDb.OleDbConnection
        dbcon.ConnectionString = ClassConst.ConnectStr
        Dim oledbcmd As New OleDb.OleDbCommand("sta_package.cre_sta", dbcon)
        oledbcmd.CommandType = CommandType.StoredProcedure
        dbcon.Open()
        oledbcmd.ExecuteNonQuery()
        Return False
    End Function

    Public Shared Function sGetTotalMon(ByVal intClientId As Integer, ByVal strDate As String, ByRef intNum As Integer) As Boolean
        Dim dbcon As New Data.OleDb.OleDbConnection
        Dim oledbcmd As New OleDb.OleDbCommand("sta_package.statisticRate", dbcon)
        dbcon.ConnectionString = ClassConst.ConnectStr
        oledbcmd.CommandType = CommandType.StoredProcedure
        dbcon.Open()
        oledbcmd.Parameters.Add(New OleDb.OleDbParameter("v_client_id", OleDb.OleDbType.BigInt, 5))
        oledbcmd.Parameters("v_client_id").Direction = ParameterDirection.Input
        oledbcmd.Parameters("v_client_id").Value = intClientId
        oledbcmd.Parameters.Add(New OleDb.OleDbParameter("v_month", OleDb.OleDbType.VarChar, 10))
        oledbcmd.Parameters("v_month").Direction = ParameterDirection.Input
        oledbcmd.Parameters("v_month").Value = strDate
        oledbcmd.Parameters.Add(New OleDb.OleDbParameter("result", OleDb.OleDbType.BigInt, 10))
        oledbcmd.Parameters("result").Direction = ParameterDirection.Output
        oledbcmd.ExecuteNonQuery()
        If oledbcmd.Parameters("result").Value.Equals(System.DBNull.Value) Then
            intNum = 0
        Else
            intNum = oledbcmd.Parameters("result").Value
        End If
        Return False
    End Function

    Public Shared Function fGet_SQL_Sta_Date() As String
        Dim strSQL As Text.StringBuilder = New Text.StringBuilder("")
        strSQL.AppendLine("SELECT")
        strSQL.AppendLine("to_char(Statistics.YearMonth,'YYYY-MM') AS strDate,")
        strSQL.AppendLine("Statistics.LastMonth AS intLastSur,")
        strSQL.AppendLine("Statistics.ThisMonth AS intThisPro,")
        strSQL.AppendLine("Statistics.OrderNumber AS intOrdNum,")
        strSQL.AppendLine("Product.ProductName AS strProName")
        strSQL.AppendLine("FROM")
        strSQL.AppendLine("Statistics,")
        strSQL.AppendLine("Product")
        strSQL.AppendLine("WHERE")
        strSQL.AppendLine("Statistics.ProductId=Product.ProductId")
        Return strSQL.ToString
    End Function

    Public Shared Function fGet_SQL_Cli_Data() As String
        Dim strSQL As Text.StringBuilder = New Text.StringBuilder("")
        strSQL.AppendLine("SELECT")
        strSQL.AppendLine("ClientName AS strCliName,")
        strSQL.AppendLine("ClientId AS intCliId")
        strSQL.AppendLine("FROM")
        strSQL.AppendLine("Client")
        Return strSQL.ToString
    End Function
    Public Shared Function f_Get_SQL_Date()
        Dim strSQL As Text.StringBuilder = New Text.StringBuilder("")
        strSQL.AppendLine("SELECT")
        strSQL.AppendLine("DISTINCT")
        strSQL.AppendLine("to_char(OrderDate,'YYYY-MM') AS strDate")
        strSQL.AppendLine("FROM")
        strSQL.AppendLine("OrderList")
        strSQL.AppendLine("WHERE")
        strSQL.AppendLine("orderdate<sysdate")
        Return strSQL.ToString
    End Function

End Class