Imports Microsoft.VisualBasic
Imports System.Web.Configuration
Imports System.Data.SqlClient
Imports System.Data
Imports System.Globalization
'modified the updatemanager function to include region and overload function without region - lloyd 20210422
'modified the insertmanager function to include region and overload function without region -lloyd 20210422
Public Class sqlDB
   'Inherits System.Web.UI.Page
   Public Shared conn As New SqlConnection(WebConfigurationManager.ConnectionStrings("csStockControl").ToString)
   Public Shared ci As CultureInfo
   Public Shared UNAME As String = WebConfigurationManager.AppSettings("UNAME")

   Sub sqlDB()
      ' ci = CultureInfo.CreateSpecificCulture(WebConfigurationManager.AppSettings("culture"))
      UNAME = WebConfigurationManager.AppSettings("UNAME")

   End Sub

   Public Shared Function Nz(ByVal v As Object, ByVal d As Object) As Object
      ' pass in the value to check as v and the default as d
      ' d will be returned if v is null or empty
      If (v Is Nothing) Then
         ' v is null, return d
         Return d
      ElseIf v.Equals(DBNull.Value) Then
         Return d
      Else
         ' v is not null
         ' check to see if v is a string
         If v.GetType() Is "String" Or v.GetType.Name = "String" Or v.GetType.FullName = "System.String" Then
            ' v is a string
            ' check to see if v is empty
            If Trim(v) = String.Empty Or Trim(v) = "" Then
               ' v is empty string, return d
               Return d
            Else
               ' v is not empty, return v
               Return v
            End If
         Else
            ' v is not a string, return v
            Return v
         End If
      End If
   End Function

   Public Shared Function CleanString(ByVal strUnClean As Object) As String
      Dim strClean As String
      'strClean = strUnClean.Replace(Chr(13), "'r")
      'strClean = strUnClean.Replace("'", "''")
      strUnClean = Nz(strUnClean, "")
      strClean = strUnClean.ToString.Replace(Chr(34), "'")
      strClean = strClean.ToString.Replace(Chr(39), "''")
      Return strClean.ToString
   End Function

   Public Shared Function doQueryDS(ByVal strSQL As String) As DataSet
      'v1.4.1 - 20120914 - Restructured some of the db class functions to help prevent Open DataReader Error

      'Dim adp As New SqlDataAdapter(strSQL, conn)
      'Dim ds As New DataSet
      'adp.Fill(ds)

      'Return ds
      Dim ds As New DataSet

      Try
         If conn.State = ConnectionState.Open Then conn.Close() 'lame
      Catch ex As Exception
      End Try

      Try
         conn.Open()

         Dim adp As New SqlDataAdapter(strSQL, conn)
         adp.Fill(ds)

         conn.Close()

      Catch ex As Exception
         Throw New Exception(ex.Message, ex.InnerException)
      Finally
         conn.Close()
      End Try

      Return ds

   End Function

   Public Shared Function GetRow(ByVal strSQL As String) As DataRow
      'v1.4.1 - 20120914 - Restructured some of the db class functions

      Try
         Return doQueryDS(strSQL).Tables(0).Rows(0)
      Catch ex As Exception
         Return Nothing
      End Try

   End Function

   Public Shared Function CountRows(ByVal strSQL As String) As Integer
      'v1.4.1 - 20120914 - Restructured some of the db class functions

      Try
         Return doQueryDS(strSQL).Tables(0).Rows.Count
      Catch ex As Exception
         Return -1
      End Try

   End Function

   Public Shared Function LastID(ByVal id As String, ByVal strTable As String) As Integer
      'v1.4.1 - 20120914 - Restructured some of the db class functions

      Return GetRow("SELECT MAX(" & id & ") FROM " & strTable).Item(0)

   End Function


   Public Shared Function getDW(ByVal strTable As String, ByVal Keys() As String, ByVal Values() As String, Optional ByVal strWhere As String = "") As DataSet
      Dim s As String = "1=1 "

      If strWhere.Length > 0 Then
         s = strWhere
      Else
         For i As Integer = 0 To Keys.Count - 1
            s &= "AND " & Keys(i) & " = '" & Values(i) & "' "
         Next
      End If

      Return doQueryDS("SELECT * FROM " & strTable & " WHERE " & s)
   End Function

   Public Shared Function Update_OuterDwd(ByVal dt As DataTable) As String
      Dim strComma As String = ""
      Dim strUpdateFields, value As String
      'Dim del As String = ""
      strUpdateFields = ""

      For i As Integer = 0 To dt.Columns.Count - 1
         'If dt.Columns(i).DataType.FullName = "System.String" Then del = "'" Else del = ""
         value = ""
         Select Case dt.Rows(0).Item(i).GetType.Name
            Case "String", "Char"
               value = "'" & CleanString(dt.Rows(0).Item(i).ToString.Trim()) & "'"
            Case "Boolean", "Bit"
               value = CInt(dt.Rows(0).Item(i))
            Case "Double", "Int32", "Float", "Single"
               value = dt.Rows(0).Item(i)
            Case Else
               value = "'" & dt.Rows(0).Item(i) & "'"
         End Select

         strUpdateFields &= strComma & " " & dt.Columns(i).ColumnName & " = " & value
         strComma = ","
      Next

      Return strUpdateFields
   End Function

   Public Shared Function dbDate() As String
      Dim s As String
      s = Date.Now.ToString("yyyyMMddHHmm")
      's = Date.Today.ToString(ci)
      Return s

   End Function

   Public Shared Function dbDate(ByVal strDate As String) As String        'inputs dd-MM-yyyy where - is $seperator

      If strDate = "" Then
         Return dbDate()
      Else
         Dim arrDate As String()
         arrDate = strDate.Split("-")
         strDate = arrDate(2) & arrDate(1) & arrDate(0).Substring(0, 2)
         Return strDate
      End If

   End Function

   Public Shared Function dbDate(ByVal strDate As String, ByVal separator As String) As String

      If strDate = "" Then
         Return dbDate()
      Else
         Dim arrDate As String()
         arrDate = strDate.Split(separator)
         strDate = arrDate(2) & arrDate(1) & arrDate(0)
         Return strDate
      End If

   End Function

   Public Shared Function SqlDate() As String
      Return Date.Now.ToString("dd/MM/yyyy HH:mm")
   End Function

   Public Shared Function SqlDate(ByVal strDate As String, ByVal blnBR As Boolean) As String
      Dim strBR As String = " "
      If blnBR = True Then strBR = "<BR>"
      If strDate = "" Then
         Return appDate()
      Else
         Dim d As New Date(strDate.Substring(0, 4), strDate.Substring(4, 2), strDate.Substring(6, 2), Nz(strDate.Substring(8, 2), 1), Nz(strDate.Substring(10, 2), 1), 1)
         Return d.ToString("dd/MM/yy") & strBR & d.ToString("HH:mm")
      End If

   End Function

   Public Shared Function SqlDate(ByVal strDate As String, ByVal format As String) As String        'input yyyyMMdd[hhmm]

      If strDate = "" Or IsNothing(strDate) Or IsDBNull(strDate) Then
         Return appDate()
      Else
         Dim d As New Date(strDate.Substring(0, 4), strDate.Substring(4, 2), strDate.Substring(6, 2), Nz(strDate.Substring(8, 2), 1), Nz(strDate.Substring(10, 2), 1), 1)
         Return d.ToString(format)
      End If

   End Function

   Public Shared Function appDate() As String

      Dim s As String
      s = Date.Now.ToString("d MMM yyyy HH:mm")
      's = Date.Today.ToString(ci)
      Return s

   End Function

   Public Shared Function appDate(ByVal strDate As String) As String           'input: yyyyMMddhhmm

      If strDate = "" Then
         Return appDate()
      Else
         Dim d As New Date(strDate.Substring(0, 4), strDate.Substring(4, 2), strDate.Substring(6, 2), Nz(strDate.Substring(8, 2), 1), Nz(strDate.Substring(10, 2), 1), 1)
         strDate = d.ToString("d MMM yyyy HH:mm")
         Return strDate
      End If

   End Function

   Public Shared Function appDate(ByVal strDate As String, ByVal separator As String) As String

      If strDate = "" Then
         Return appDate()
      Else
         Dim arrDate As String()
         arrDate = strDate.Split(separator)
         Dim d As New Date(arrDate(2), arrDate(1), arrDate(0))
         Return d.ToString("d MMM yyyy HH:mm")
      End If

   End Function

   Public Shared Function Alert(ByVal strMessage As String) As String
      Return "<script language='javascript'>alert(""" & strMessage & """);</script>"
   End Function

   Public Shared Function getGridStartDate(ByVal strPeriod As String) As String
      Dim d As New Date(strPeriod.Substring(0, 4), strPeriod.Substring(4, 2), 1)
      Dim newDate As Date

      If d.Month > 3 Then
         newDate = New Date(d.Year, d.Month - 3, 1)
      Else
         newDate = New Date(d.Year - 1, d.Month + 9, 1)
      End If
      Return newDate.ToString("yyyyMM")
   End Function

   Public Shared Function getGridEndDate(ByVal strPeriod As String) As String
      Dim d As New Date(strPeriod.Substring(0, 4), strPeriod.Substring(4, 2), 1)
      Dim newDate As Date

      If d.Month < 2 Then
         newDate = New Date(d.Year, d.Month + 11, 1)
      Else
         newDate = New Date(d.Year + 1, d.Month - 1, 1)
      End If
      Return newDate.ToString("yyyyMM")

   End Function

   Public Shared Function findRow(ByVal ds As DataSet, ByVal intCol As Integer, ByVal value As Object) As DataRow
      Dim dr As DataRow
      For Each dr In ds.Tables(0).Rows
         If dr(intCol).Equals(value) Then
            Return dr
         End If
      Next
      Return Nothing
   End Function

   Public Shared Sub WriteLog(ByVal Process As String, ByVal strAction As String, ByVal Message As String, Optional ByVal refStamp As String = "", Optional ByVal blnEmail As Integer = 1)
      Dim s As String = ""
      System.Threading.Thread.Sleep(13)
      Message = CleanString(Message)
      s = Date.Now.Ticks
      If refStamp = "" Then refStamp = s
      doQueryDS("INSERT INTO dlyImportLog(dtStamp, strFile, strAction, strLog, refStamp, blnEmail)" & _
                "VALUES('" & s & "', '" & Process & "', '" & strAction & "', '" & Message & "', '" & refStamp & "', " & blnEmail & ")")
   End Sub

   'function naming c: sql_get_   overloaded: 1:(int), 2:(str), 3(str, {!} int)
   '                   sql_insert_
   '                   sql_update_
   '                   sql_delete_
   '
   '//////////////////////////////////////////////////////////////////
   '// SUPERBOWL SQL FUNCTIONS
   '//////////////////////////////////////////////////////////////////

   '//// CUSTOMER
   'v.1.2.4
   Public Function sql_get_Customer(ByVal strCustomerNo As String) As String
      'Note: This query uses SELECT mstCustomer.* which will include strTags if the column exists.
      'If strTags column doesn't exist yet, the query will fail. The code-behind handles this gracefully.
      Return "SELECT mstCustomer.* , mstRep_1.refSegmentID, mstSnellsupergroup.strSnellsupergroupName, mstRep_1.strRepName + ' [' + mstCustomer.refRepID + ']' AS Rep, mstRep_2.strRepName + ' [' + mstCustomer.refRepBBID + ']' AS BrandBuilderRep, mstRep_3.strRepName + ' [' + mstCustomer.refRepDlxID + ']' AS DeluxRep, mstRep_1.strRepName + ' [' + mstCustomer.refRepLuxID + ']' AS LuxuryRep, mstBranch.strBranch + ' [' + CAST(mstBranch.BranchID AS varchar) + ']' AS Branch, mstKeyAccount.strKeyAccount + ' - ' + mstKeyAccount_Group.strGroupName + ' [' + CAST(mstKeyAccount.KeyAccountID AS varchar) + ']-[' + CAST(mstKeyAccount_Group.KeyAccount_GroupID AS varchar) + ']' AS KeyAccount, mstChannelSegment.strSegmentPrimary + ' > ' + mstChannelSegment.strSegment + ' > ' + mstChannelSegment.strChannelPrimary + ' > ' + mstChannelSegment.strChannel + ' [' + CAST(mstCustomer.refChannelSegmentID AS varchar) + ']' AS ChannelSegment, mstType1.strType1 + ' [' + CAST(mstType1.Type1ID AS varchar) + ']' AS Type1, mstType2.strType2 + ' [' + CAST(mstType2.Type2ID AS varchar) + ']' AS Type2 " & _
            "FROM mstKeyAccount RIGHT OUTER JOIN mstKeyAccount_Group RIGHT OUTER JOIN mstChannelSegment RIGHT OUTER JOIN mstType2 RIGHT OUTER JOIN mstRep AS mstRep_1 RIGHT JOIN mstCustomer ON mstRep_1.RepID = mstCustomer.refRepID LEFT OUTER JOIN mstType1 ON mstCustomer.refType1ID = mstType1.Type1ID ON mstType2.Type2ID = mstCustomer.refType2ID ON mstChannelSegment.ChannelSegmentID = mstCustomer.refChannelSegmentID ON mstKeyAccount_Group.KeyAccount_GroupID = mstCustomer.refKeyAccount_GroupID ON mstKeyAccount.KeyAccountID = mstKeyAccount_Group.refKeyAccountID LEFT OUTER JOIN mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID LEFT OUTER JOIN mstRep AS mstRep_4 ON mstCustomer.refRepLuxID = mstRep_4.RepID LEFT OUTER JOIN mstRep AS mstRep_3 ON mstCustomer.refRepDlxID = mstRep_3.RepID LEFT OUTER JOIN mstRep AS mstRep_2 ON mstCustomer.refRepBBID = mstRep_2.RepID LEFT OUTER JOIN mstSnellsupergroup ON mstCustomer.strSnellsupergroup = mstSnellsupergroup.strSnellsupergroup " & _
            "WHERE strCustomerNo = '" & CleanString(strCustomerNo) & "'"
      'FROM mstCustomer LEFT OUTER JOIN mstSnellsupergroup ON mstCustomer.strSnellsupergroup = mstSnellsupergroup.strSnellsupergroup LEFT OUTER JOIN mstRep ON mstCustomer.refRepID = mstRep.RepID LEFT OUTER JOIN mstSegment ON mstRep.refSegmentID = mstSegment.SegmentID 
   End Function ', mstSegment.strSegment

   Public Function sql_get_Customer_LIKE(ByVal strCustomerName As String) As String
      Return "Select * From mstCustomer WHERE strCustomerName LIKE '%" & CleanString(strCustomerName) & "%'"
   End Function

   Public Function sql_get_Customer(ByVal strCustomerNo As String, ByVal strCustomerName As String) As String
      Return "Select * From mstCustomer WHERE strCustomerNo <> '" & CleanString(strCustomerNo) & "' AND strCustomerName = '" & CleanString(strCustomerName) & "'"
   End Function

   'v1.4.3 - 20131106 - Added strPriceGroupCode
   Public Function sql_get_Customer_dw(ByVal strCustomerNo As String) As String                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          ', mstType1.strType1, mstType2.strType2,
      'Return "SELECT mstCustomer.strCustomerNo, mstCustomer.strCustomerName, mstCustomer.strSnellChannel, mstCustomer.strSnellArea, mstCustomer.strSnellcwsireg, mstCustomer.strSnellrepno, mstCustomer.strSnellsupergroup, mstSnellsupergroup.strSnellsupergroupName, mstCustomer.strSnellgrading, mstCustomer.refRepID, mstCustomer.refRepBBID, mstCustomer.refRepDlxID, mstCustomer.refRepLuxID, mstRep.strRepName, mstRep_1.strRepName AS strRepNameBB, mstRep_2.strRepName AS strRepNameDlx, mstRep_3.strRepName AS strRepNameLux, mstChannel.strChannel, mstSegment.strSegment, mstKeyAccount_Group.strGroupName, mstKeyAccount.strKeyAccount, mstCustomer.blnPromotionalAccounts, mstCustomer.blnActive, mstBranch.strCode, mstBranch.strBranch, mstBranch.strSalesOffice, mstBranch.strRegion, mstBranch.strSAPCode, mstBranch.strRegionCode, mstBranch.strRegionDesc, mstBranch.strRegionAccpacCode, mstBranch.strRegionDesc2, mstType1.strType1, mstType2.strType2, mstChannelSegment.strSegmentPrimary, mstChannelSegment.strSegment AS strSegmentSecondary, mstChannelSegment.strChannelPrimary, mstChannelSegment.strChannel AS strChannelSecondary " & _
      '  "FROM mstKeyAccount RIGHT OUTER JOIN mstKeyAccount_Group RIGHT OUTER JOIN mstSegment RIGHT OUTER JOIN mstRep RIGHT OUTER JOIN mstType1 RIGHT OUTER JOIN mstType2 RIGHT OUTER JOIN mstChannelSegment RIGHT OUTER JOIN mstCustomer ON mstChannelSegment.ChannelSegmentID = mstCustomer.refChannelSegmentID ON mstType2.Type2ID = mstCustomer.refType2ID ON mstType1.Type1ID = mstCustomer.refType1ID LEFT OUTER JOIN mstSnellsupergroup ON mstCustomer.strSnellsupergroup = mstSnellsupergroup.strSnellsupergroup ON mstRep.RepID = mstCustomer.refRepID LEFT OUTER JOIN mstChannel ON mstCustomer.refChannelSegmentID = mstChannel.ChannelID ON mstSegment.SegmentID = mstChannel.refSegmentID ON mstKeyAccount.KeyAccountID = mstKeyAccount_Group.refKeyAccountID LEFT OUTER JOIN mstRep AS mstRep_2 ON mstCustomer.refRepDlxID = mstRep_2.RepID LEFT OUTER JOIN mstRep AS mstRep_3 ON mstCustomer.refRepLuxID = mstRep_3.RepID " & _
      '  "WHERE mstCustomer.strCustomerNo = '" & CleanString(strCustomerNo) & "' "
      'NOTE: SELECT must sync with INSERT INTO SELECT from _customer_dw()
      Return "SELECT mstCustomer.strCustomerNo, mstCustomer.strCustomerName, mstCustomer.strSnellChannel, mstCustomer.strSnellArea,  " & _
        "mstCustomer.strSnellcwsireg, mstCustomer.strSnellrepno, mstCustomer.strSnellsupergroup, mstSnellsupergroup.strSnellsupergroupName, " & _
        "mstCustomer.strSnellgrading, mstCustomer.refRepID, mstCustomer.refRepBBID, mstCustomer.refRepDlxID, mstCustomer.refRepLuxID, " & _
        "mstRep.strRepName, mstRep_1.strRepName AS strRepNameBB, mstRep_2.strRepName AS strRepNameDlx,  " & _
        "mstRep_3.strRepName AS strRepNameLux, mstChannel.strChannel, mstSegment.strSegment, mstKeyAccount_Group.strGroupName,  " & _
        "mstKeyAccount.strKeyAccount,mstKeyAccount.strPriceGroupCode, mstCustomer.blnPromotionalAccounts, mstCustomer.blnActive, mstBranch.strCode, mstBranch.strBranch, " & _
        "mstBranch.strSalesOffice, mstBranch.strRegion, mstBranch.strSAPCode, mstBranch.strRegionCode, mstBranch.strRegionDesc, " & _
        "mstBranch.strRegionAccpacCode, mstBranch.strRegionDesc2, mstType1.strType1, mstType2.strType2, mstChannelSegment.strSegmentPrimary, " & _
        "mstChannelSegment.strSegment AS strSegmentSecondary, mstChannelSegment.strChannelPrimary AS strChannelPrimary, mstChannelSegment.strChannel AS strChannelSecondary, mstCustomer.strCountry, mstCustomer.strTags " & _
        "FROM mstCustomer LEFT OUTER JOIN " & _
        "mstRep ON mstCustomer.refRepID = mstRep.RepID LEFT OUTER JOIN " & _
        "mstRep AS mstRep_1 ON mstCustomer.refRepBBID = mstRep_1.RepID LEFT OUTER JOIN " & _
        "mstRep AS mstRep_2 ON mstCustomer.refRepDlxID = mstRep_2.RepID LEFT OUTER JOIN " & _
        "mstRep AS mstRep_3 ON mstCustomer.refRepLuxID = mstRep_3.RepID LEFT OUTER JOIN " & _
        "mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID LEFT OUTER JOIN " & _
        "mstKeyAccount_Group ON mstCustomer.refKeyAccount_GroupID = mstKeyAccount_Group.KeyAccount_GroupID LEFT OUTER JOIN " & _
        "mstKeyAccount ON mstKeyAccount_Group.refKeyAccountID = mstKeyAccount.KeyAccountID LEFT OUTER JOIN " & _
        "mstChannelSegment ON mstCustomer.refChannelSegmentID = mstChannelSegment.ChannelSegmentID LEFT OUTER JOIN " & _
        "mstType1 ON mstCustomer.refType1ID = mstType1.Type1ID LEFT OUTER JOIN " & _
        "mstType2 ON mstCustomer.refType2ID = mstType2.Type2ID LEFT OUTER JOIN " & _
        "mstChannel ON mstCustomer.refChannelID = mstChannel.ChannelID LEFT OUTER JOIN " & _
        "mstSegment ON mstChannel.refSegmentID = mstSegment.SegmentID LEFT OUTER JOIN " & _
        "mstSnellsupergroup ON mstCustomer.strSnellsupergroup = mstSnellsupergroup.strSnellsupergroup " & _
        "WHERE mstCustomer.strCustomerNo = '" & CleanString(strCustomerNo) & "' "


      'Return "SELECT mstCustomer.strCustomerNo, mstCustomer.strCustomerName, mstCustomer.strSnellChannel, mstCustomer.strSnellArea, mstCustomer.strSnellcwsireg, mstCustomer.strSnellrepno, mstCustomer.strSnellsupergroup, mstSnellsupergroup.strSnellsupergroupName, mstCustomer.strSnellgrading, mstCustomer.refRepID, mstCustomer.refRepBBID, mstCustomer.refRepDlxID, mstCustomer.refRepLuxID, mstRep.strRepName, mstRep_1.strRepName AS strRepNameBB, mstRep_2.strRepName AS strRepNameDlx, mstRep_3.strRepName AS strRepNameLux, mstChannel.strChannel, mstSegment.strSegment, mstKeyAccount_Group.strGroupName, mstKeyAccount.strKeyAccount, mstCustomer.blnPromotionalAccounts, mstCustomer.blnActive, mstBranch.strCode, mstBranch.strBranch, mstBranch.strSalesOffice, mstBranch.strRegion, mstBranch.strSAPCode, mstBranch.strRegionCode, mstBranch.strRegionDesc, mstBranch.strRegionAccpacCode, mstBranch.strRegionDesc2, mstType1.strType1, mstType2.strType2 " & _
      '"FROM mstRep AS mstRep_2 RIGHT OUTER JOIN mstRep AS mstRep_1 RIGHT OUTER JOIN mstBranch RIGHT OUTER JOIN mstKeyAccount_Group RIGHT OUTER JOIN mstChannel RIGHT OUTER JOIN mstRep RIGHT OUTER JOIN mstSnellsupergroup RIGHT OUTER JOIN mstCustomer ON mstSnellsupergroup.strSnellsupergroup = mstCustomer.strSnellsupergroup ON mstRep.RepID = mstCustomer.refRepID ON mstChannel.ChannelID = mstCustomer.refChannelSegmentID LEFT OUTER JOIN mstSegment ON mstChannel.refSegmentID = mstSegment.SegmentID ON mstKeyAccount_Group.KeyAccount_GroupID = mstCustomer.refKeyAccount_GroupID LEFT OUTER JOIN mstKeyAccount ON mstKeyAccount_Group.refKeyAccountID = mstKeyAccount.KeyAccountID ON mstBranch.BranchID = mstCustomer.refBranchID ON mstRep_1.RepID = mstCustomer.refRepBBID ON mstRep_2.RepID = mstCustomer.refRepDlxID LEFT OUTER JOIN mstRep AS mstRep_3 ON mstCustomer.refRepLuxID = mstRep_3.RepID  " & _
      '"WHERE mstCustomer.strCustomerNo = '" & CleanString(strCustomerNo) & "' "
   End Function

    Public Function sql_insert_Customer(ByVal strCustomerNo As String, ByVal strCustomerName As String, ByVal strSnellChannel As String, ByVal strSnellArea As String, ByVal strSnellcwsireg As String, ByVal strSnellrepno As String, ByVal strSnellsupergroup As String, ByVal strSnellgrading As String, ByVal refRepID As String, ByVal refRepBBID As String, ByVal refRepDlxID As String, ByVal refRepLuxID As String, ByVal refBranchID As Integer, ByVal refKeyAccount_GroupID As Integer, ByVal refChannelSegmentID As Integer, ByVal blnActive As Integer, ByVal blnPromotionalAccounts As Integer, ByVal refType1 As Integer, ByVal refType2 As Integer, ByVal strCountry As String, ByVal strUserName As String, ByVal strAddress As String, ByVal strEmail As String, ByVal strTel As String, ByVal strTags As String) As String
        Return "INSERT INTO mstCustomer ( strCustomerNo, strCustomerName, strSnellChannel, strSnellArea, strSnellcwsireg, strSnellrepno, strSnellsupergroup, strSnellgrading, refRepID, refRepBBID, refRepDlxID, refRepLuxID, refBranchID, refKeyAccount_GroupID, refChannelSegmentID, blnActive, blnPromotionalAccounts, refType1ID, refType2ID, strLastUser, dtStamp, strCountry, strAddress, strEmail, strTelephone, strTags ) " &
      " VALUES ('" & CleanString(strCustomerNo) & "','" & CleanString(strCustomerName) & "','" & CleanString(strSnellChannel) & "','" & CleanString(strSnellArea) & "','" & CleanString(strSnellcwsireg) & "','" & CleanString(strSnellrepno) & "','" & CleanString(strSnellsupergroup) & "','" & CleanString(strSnellgrading) & "','" & CleanString(refRepID) & "','" & CleanString(refRepBBID) & "','" & CleanString(refRepDlxID) & "', '" & CleanString(refRepLuxID) & "', " & refBranchID & "," & refKeyAccount_GroupID & "," & refChannelSegmentID & "," & blnActive & "," & blnPromotionalAccounts & ", " & refType1 & ", " & refType2 & ",'" & strUserName & "','" & dbDate() & "', '" & CleanString(strCountry) & "', '" & CleanString(strAddress) & "' , '" & CleanString(strEmail) & "', '" & CleanString(strTel) & "', '" & CleanString(strTags) & "')"
    End Function

    Public Function sql_update_Customer(ByVal strCustomerNo As String, ByVal strCustomerName As String, ByVal strSnellChannel As String, ByVal strSnellArea As String, ByVal strSnellcwsireg As String, ByVal strSnellrepno As String, ByVal strSnellsupergroup As String, ByVal strSnellgrading As String, ByVal refRepID As String, ByVal refRepBBID As String, ByVal refRepDlxID As String, ByVal refRepLuxID As String, ByVal refBranchID As Integer, ByVal refKeyAccount_GroupID As Integer, ByVal refChannelSegmentID As Integer, ByVal blnActive As Integer, ByVal blnPromotionalAccounts As Integer, ByVal refType1 As Integer, ByVal refType2 As Integer, ByVal strCountry As String, ByVal strUserName As String, ByVal strAddress As String, ByVal strEmail As String, ByVal strTel As String, ByVal strTags As String) As String
        Return "UPDATE mstCustomer SET strCustomerName = '" & CleanString(strCustomerName) & "', strSnellChannel = '" & CleanString(strSnellChannel) & "', strSnellArea = '" & CleanString(strSnellArea) & "', strSnellcwsireg = '" & CleanString(strSnellcwsireg) & "', strSnellrepno = '" & CleanString(strSnellrepno) & "', strSnellsupergroup = '" & CleanString(strSnellsupergroup) &
      "', strSnellgrading = '" & CleanString(strSnellgrading) & "', refRepID = '" & refRepID & "', refRepBBID = '" & refRepBBID & "', refRepDlxID = '" & refRepDlxID & "', refRepLuxID = '" & refRepLuxID & "', refBranchID = " & refBranchID & ", refKeyAccount_GroupID = " & refKeyAccount_GroupID & ", refChannelSegmentID = " & refChannelSegmentID & ", blnActive = " & blnActive &
      ", blnPromotionalAccounts = " & blnPromotionalAccounts & ", refType1ID = " & refType1 & ", refType2ID = " & refType2 & ", strCountry = '" & CleanString(strCountry) & "', strLastUser = '" & strUserName & "', strEmail = '" & CleanString(strEmail) & "', strAddress = '" & CleanString(strAddress) & "', strTelephone = '" & CleanString(strTel) & "', strTags = '" & CleanString(strTags) & "', dtStamp = '" & dbDate() & "' WHERE strCustomerNo='" & CleanString(strCustomerNo) & "' "
    End Function

    'v1.4.3 - 20131106 - Added strPriceGroupCode
    Public Function sql_insert_Customer_dw(ByVal strCustomerNo As String) As String
      Return "INSERT INTO dwdCustomer ( strCustomerNo, strCustomerName, strSnellChannel, strSnellArea, strSnellcwsireg, strSnellrepno, strSnellsupergroup, strSnellsupergroupName, strSnellgrading, refRepID, refRepBBID, refRepDlxID, refRepLuxID, strRepName, strRepNameBB, strRepNameDlx, strRepNameLux, strChannel, strSegment, strGroupName, strKeyAccount, strPriceGroupCode, blnPromotionalAccounts, blnActive, strCode, strBranch, strSalesOffice, strRegion, strSAPCode, strRegionCode, strRegionDesc, strRegionAccpacCode, strRegionDesc2, strType1, strType2, strSegmentPrimary, strSegmentSecondary, strChannelPrimary, strChannelSecondary, strCountry, strTags ) " & _
 sql_get_Customer_dw(strCustomerNo)
   End Function

   'v1.4.3 - 20131106 - Added strPriceGroupCode
   Private Shared Function GetBaseUpdateQuery() As String
      Return "UPDATE dwdCustomer " & _
      "SET strCode = b.strCode, strBranch = b.strBranch, strSalesOffice = b.strSalesOffice, strRegion = b.strRegion, strSAPCode = b.strSAPCode, " & _
      "strRegionCode = b.strRegionCode, strRegionDesc = b.strRegionDesc, strRegionAccpacCode = b.strRegionAccpacCode,  " & _
      "strRegionDesc2 = b.strRegionDesc2, strRegionORder = b.strRegionOrder, strCustomerName = c.strCustomerName, strSnellChannel = c.strSnellChannel, strSnellArea = c.strSnellArea,  " & _
      "strSnellcwsireg = c.strSnellcwsireg, strSnellrepno = c.strSnellrepno, strSnellsupergroup = c.strSnellsupergroup,  " & _
      "strSnellsupergroupName = mstSnellsupergroup.strSnellsupergroup, strSnellgrading = c.strSnellgrading, refRepID = c.refRepID,  " & _
      "refRepBBID = c.refRepBBID, refRepDlxID = c.refRepDlxID, refRepLuxID = c.refRepLuxID, strRepName = mstRep.strRepName,  " & _
      "strRepNameBB = mstRep_2.strRepName, strRepNameDlx = mstRep_3.strRepName, strRepNameLux = mstRep_4.strRepName,  " & _
      "strChannel = mstChannel.strChannel, strSegment = mstSegment.strSegment, strType1 = mstType1.strType1, strType2 = mstType2.strType2,  " & _
      "strGroupName = dwdCustomer.strGroupName, strKeyAccount = mstKeyAccount.strKeyAccount,strPriceGroupCode = mstKeyAccount.strPriceGroupCode, blnPromotionalAccounts = c.blnPromotionalAccounts,  " & _
      "blnActive = c.blnActive, strChannelPrimary = mstChannelSegment.strChannelPrimary, strChannelSecondary = mstChannelSegment.strChannel,  " & _
      "strSegmentPrimary = mstChannelSegment.strSegmentPrimary, strSegmentSecondary = mstChannelSegment.strSegment, strCountry = mstCustomer.strCountry, strTags = c.strTags " & _
      "FROM mstSnellsupergroup RIGHT OUTER JOIN " & _
      "mstRep RIGHT OUTER JOIN " & _
      "mstRep AS mstRep_4 RIGHT OUTER JOIN " & _
      "mstKeyAccount_Group LEFT OUTER JOIN " & _
      "mstKeyAccount ON mstKeyAccount_Group.refKeyAccountID = mstKeyAccount.KeyAccountID RIGHT OUTER JOIN " & _
      "mstChannelSegment RIGHT OUTER JOIN " & _
      "dwdCustomer INNER JOIN " & _
      "mstCustomer AS c ON dwdCustomer.strCustomerNo = c.strCustomerNo ON  " & _
      "mstChannelSegment.ChannelSegmentID = c.refChannelSegmentID LEFT OUTER JOIN " & _
      "mstChannel LEFT OUTER JOIN " & _
      "mstSegment ON mstChannel.refSegmentID = mstSegment.SegmentID ON c.refChannelSegmentID = mstChannel.ChannelID ON  " & _
      "mstKeyAccount_Group.KeyAccount_GroupID = c.refKeyAccount_GroupID ON mstRep_4.RepID = c.refRepLuxID LEFT OUTER JOIN " & _
      "mstRep AS mstRep_3 ON c.refRepDlxID = mstRep_3.RepID LEFT OUTER JOIN " & _
      "mstRep AS mstRep_2 ON c.refRepBBID = mstRep_2.RepID ON mstRep.RepID = c.refRepID LEFT OUTER JOIN " & _
      "mstType2 ON c.refType2ID = mstType2.Type2ID LEFT OUTER JOIN " & _
      "mstType1 ON c.refType1ID = mstType1.Type1ID ON mstSnellsupergroup.strSnellsupergroup = c.strSnellsupergroup LEFT OUTER JOIN " & _
      "mstBranch AS b ON c.refBranchID = b.BranchID"
   End Function

   Public Shared Function sql_update_Customer_dw() As String
      Return GetBaseUpdateQuery()
   End Function

   Public Function sql_update_Customer_dw(ByVal strCustomerNo As String) As String
      Return GetBaseUpdateQuery() & " WHERE dwdCustomer.strCustomerNo = '" & strCustomerNo & "' "
   End Function

   Public Function sql_update_Customer_dw(ByVal strCustomerNo As String, ByVal fields As String) As String
      Return "UPDATE dwdCustomer SET " & fields & " WHERE strCustomerNo = '" & strCustomerNo & "' "
   End Function
   'from SC
   Public Shared Function sql_update_Customer_dw_IN(ByVal inCustomerNo As String) As String
      Return sql_update_Customer_dw() & " WHERE dwdCustomer.strCustomerNo IN (" & inCustomerNo & ")"
   End Function

   '\\\\	Product

   Public Function sql_get_Product(ByVal strProdCode As String) As String
      Return "SELECT mstUser.UserID, mstUser.strUserName, mstBrand.strBrand, mstBrand.intLeadMonths, mstProduct.* " & _
      " FROM mstUser RIGHT OUTER JOIN mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID ON mstUser.UserID = mstBrand.refBrandManagerID " & _
      " WHERE Left(strProdCode,5)='" & strProdCode & "'  ORDER BY mstProduct.strProdCode"
   End Function
   'v.1.2.4
   Public Function sql_get_Product_6(ByVal strProdCode As String) As String
      Return "SELECT mstBrand.strBrand + ' ['+ Cast(BrandID as varchar) +']' as Brand, mstProduct.* " & _
      " FROM mstUser RIGHT OUTER JOIN mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID ON mstUser.UserID = mstBrand.refBrandManagerID " & _
      " WHERE strProdCode = '" & CleanString(strProdCode) & "'  ORDER BY mstProduct.strProdCode"
   End Function

   Public Function sql_get_Product_5(ByVal strProdCode As String, ByVal strLongProdDesc As String) As String
      Return "SELECT mstUser.UserID, mstUser.strUserName, mstBrand.strBrand, mstBrand.intLeadMonths, mstProduct.* " & _
      " FROM mstUser RIGHT  JOIN mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID ON mstUser.UserID = mstBrand.refBrandManagerID " & _
      " WHERE strProdCode NOT LIKE '%" & CleanString(strProdCode) & "%'  AND strLongProdDesc = '" & CleanString(strLongProdDesc) & "' ORDER BY mstProduct.strProdCode"
   End Function

   Public Function sql_get_Product_dw(ByVal strProdCode As String) As String
      Return "SELECT mstProduct.strProdCode, mstProduct.strProductDesc, mstProduct.intPack, mstProduct.intSize, mstProduct.dblPrice, mstProduct.strLongProdDesc, mstProduct.strType, mstProduct.strType2, mstProduct.intShortCode, mstProduct.strPriceSeg, mstProduct.strCategory, mstProduct.blnActive, mstProduct.dblFlat, mstProduct.cwsicat, mstProduct.strCategory2, mstProduct.strPrincipalMaterialRefNum, mstProduct.strPrincipalProdDesc, mstPrincipal.strPrincipal, mstPrincipal.strFYStart AS strPrincipalFYStart, mstPrincipal.strFYEnd AS strPrincipalFYEnd, mstCompany.strCompany, mstProduct.refBrandID, mstBrand.strBrand, mstBrand.blnActive AS blnBrandActive, mstProduct.strBottleBarcode, mstProduct.strCaseBarcode, mstProduct.strGiftboxBarcode, mstProduct.intDisplayOrder, mstProduct.strBrandVarietal, mstGroupingDaily.strGroupingDaily, mstGroupingPrimary.strGroupingPrimary, mstGroupingSecondary.strGroupingSecondary, mstProduct.blnQF, mstProduct.blnDataTransfer, mstProduct.blnCompProduct, mstProduct.strTags " & _
         "FROM mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID INNER JOIN mstPrincipal ON mstBrand.refPrincipalID = mstPrincipal.PrincipalID INNER JOIN mstCompany ON mstBrand.refCompanyID = mstCompany.CompanyID INNER JOIN mstGroupingSecondary ON mstBrand.refGroupingSecondaryID = mstGroupingSecondary.GroupingSecondaryID INNER JOIN mstGroupingPrimary ON mstBrand.refGroupingPrimaryID = mstGroupingPrimary.GroupingPrimaryID INNER JOIN mstGroupingDaily ON mstBrand.refGroupingDailyID = mstGroupingDaily.GroupingDailyID WHERE mstProduct.strProdCode = '" & CleanString(strProdCode) & "' "
      'Return "SELECT mstProduct.strProdCode, mstProduct.strProductDesc, mstProduct.intPack, mstProduct.intSize, mstProduct.dblPrice, mstProduct.strLongProdDesc, mstProduct.strType, mstProduct.strType2, mstProduct.intShortCode, mstProduct.strPriceSeg, mstProduct.strCategory, mstProduct.blnActive, mstProduct.dblFlat, mstProduct.cwsicat, mstProduct.strCategory2, mstProduct.strPrincipalMaterialRefNum, mstProduct.strPrincipalProdDesc, mstPrincipal.strPrincipal, mstCompany.strCompany, mstProduct.refBrandID, mstBrand.strBrand, mstBrand.blnActive AS blnBrandActive, mstProduct.strBottleBarcode, mstProduct.strCaseBarcode, mstProduct.strGiftboxBarcode, mstProduct.intDisplayOrder, mstProduct.strBrandVarietal FROM mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID INNER JOIN mstPrincipal ON mstBrand.refPrincipalID = mstPrincipal.PrincipalID INNER JOIN mstCompany ON mstBrand.refCompanyID = mstCompany.CompanyID WHERE mstProduct.strProdCode = '" & CleanString(strProdCode) & "' "
   End Function
   '                                                                                                                                                                                                                                       ByVal strCategory,,'" & CleanString(strCategory) & "' ByVal strCategory2, ,'" & CleanString(strCategory2) & "'
   Public Function sql_insert_Product(ByVal strProdCode, ByVal refBrandID, ByVal strProductDesc, ByVal intPack, ByVal intSize, ByVal dblPrice, ByVal strLongProdDesc, ByVal strType, ByVal strType2, ByVal intShortCode, ByVal strPriceSeg, ByVal blnActive, ByVal dblFlat, ByVal cwsicat, ByVal strPrincipalMaterialRefNum, ByVal strPrincipalProdDesc, ByVal strBottleBarcode, ByVal strCaseBarcode, ByVal strGiftboxBarcode, ByVal intDisplayOrder, ByVal strBrandVarietal, ByVal strCategory, ByVal strCategory2, ByVal blnQF, ByVal blnDataTransfer, ByVal blnCompProduct, ByVal strUserName, ByVal strTags) As String
      Return "INSERT INTO mstProduct ( strProdCode, refBrandID, strProductDesc, intPack, intSize, dblPrice, strLongProdDesc, strType, strType2, intShortCode, strPriceSeg, blnActive, dblFlat, cwsicat, strCategory, strCategory2, strPrincipalMaterialRefNum, strPrincipalProdDesc, strBottleBarcode, strCaseBarcode, strGiftboxBarcode, intDisplayOrder, strBrandVarietal, strLastUser, dtStamp, blnQF, blnDataTransfer, blnCompProduct, strTags )" & _
       "VALUES ('" & CleanString(strProdCode) & "'," & refBrandID & ",'" & CleanString(strProductDesc) & "'," & intPack & "," & intSize & "," & dblPrice & ",'" & CleanString(strLongProdDesc) & "','" & CleanString(strType) & "','" & CleanString(strType2) & "','" & CleanString(intShortCode) & "','" & CleanString(strPriceSeg) & "'," & blnActive & "," & dblFlat & ",'" & CleanString(cwsicat) & "','" & CleanString(strCategory) & "','" & CleanString(strCategory2) & "','" & CleanString(strPrincipalMaterialRefNum) & "','" & CleanString(strPrincipalProdDesc) & "','" & CleanString(strBottleBarcode) & "','" & CleanString(strCaseBarcode) & "','" & CleanString(strGiftboxBarcode) & "'," & intDisplayOrder & ",'" & CleanString(strBrandVarietal) & "', '" & strUserName & "','" & dbDate() & "'," & blnQF & "," & CInt(blnDataTransfer) & "," & CInt(blnCompProduct) & ",'" & CleanString(strTags) & "')"
   End Function
    '                                                                                                                                                                                                                                     , ByVal strCategory, strCategory = '" & CleanString(strCategory) & "', ByVal strCategory2, strCategory2 = '" & CleanString(strCategory2) & "'
    Public Function sql_update_Product(ByVal strProdCode, ByVal refBrandID, ByVal strProductDesc, ByVal intPack, ByVal intSize, ByVal dblPrice, ByVal strLongProdDesc, ByVal strType, ByVal strType2, ByVal intShortCode, ByVal strPriceSeg, ByVal blnActive, ByVal dblFlat, ByVal cwsicat, ByVal strPrincipalMaterialRefNum, ByVal strPrincipalProdDesc, ByVal strBottleBarcode, ByVal strCaseBarcode, ByVal strGiftboxBarcode, ByVal intDisplayOrder, ByVal strBrandVarietal, ByVal strCategory, ByVal strCategory2, ByVal blnQF, ByVal blnDataTransfer, ByVal blnCompProduct, ByVal strUserName, ByVal strTags) As String
      Return "UPDATE mstProduct SET refBrandID = " & refBrandID & ", strProductDesc = '" & CleanString(strProductDesc) & "', intPack = " & intPack & _
      ", intSize = " & intSize & ", dblPrice = " & dblPrice & ", strLongProdDesc = '" & CleanString(strLongProdDesc) & "', strType = '" & CleanString(strType) & _
      "', strType2 = '" & CleanString(strType2) & "', intShortCode = '" & CleanString(intShortCode) & "', strPriceSeg = '" & CleanString(strPriceSeg) & _
      "', blnActive = " & blnActive & ", dblFlat = " & dblFlat & ", cwsicat = '" & CleanString(cwsicat) & _
      "',  strPrincipalMaterialRefNum = '" & CleanString(strPrincipalMaterialRefNum) & "', strCategory = '" & CleanString(strCategory) & "', strCategory2 = '" & CleanString(strCategory2) & _
      "', strPrincipalProdDesc = '" & CleanString(strPrincipalProdDesc) & "', strBottleBarcode = '" & CleanString(strBottleBarcode) & "', strCaseBarcode = '" & CleanString(strCaseBarcode) & _
      "', strGiftboxBarcode = '" & CleanString(strGiftboxBarcode) & "',   intDisplayOrder = " & intDisplayOrder & ", strBrandVarietal = '" & CleanString(strBrandVarietal) & _
      "', strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "', blnQF = " & blnQF & ", blnDataTransfer = " & CInt(blnDataTransfer) & ", blnCompProduct = " & CInt(blnCompProduct) & ", strTags = '" & CleanString(strTags) & "' WHERE strProdCode='" & CleanString(strProdCode) & "' "

   End Function
    Public Function sql_get_ProductDataDimension(ByVal strProdCode As String) As String
        Return "SELECT mstBrand.strBrand + ' ['+ Cast(BrandID as varchar) +']' as Brand, mstProductDataDimension.* " &
      " FROM mstUser RIGHT OUTER JOIN mstProductDataDimension INNER JOIN mstBrand ON mstProductDataDimension.refBrandID = mstBrand.BrandID ON mstUser.UserID = mstBrand.refBrandManagerID " &
      " WHERE strProdCode = '" & CleanString(strProdCode) & "'  ORDER BY mstProductDataDimension.strProdCode"
    End Function
    Public Function sql_update_ProductDataDimension(ByVal strProdCode, ByVal refBrandID, ByVal strDescription, ByVal strAccpacCode, ByVal strVendorCode, ByVal intABVPerc, ByVal intImportCertNo, ByVal intPalletBase, ByVal intPalletLayer, ByVal intPalletTotal, ByVal intBottleWeight, ByVal intVAPGiftUnitWeight, ByVal intCaseWeight, ByVal intPalletWeight, ByVal intBottleHeight, ByVal intVAPGiftUnitHeight, ByVal intCaseHeight, ByVal intPalletHeight, ByVal intBottleWidth, ByVal intVAPGiftUnitWidth, ByVal intCaseWidth, ByVal intPalletWidth, ByVal intBottleLength, ByVal intVAPGiftUnitLength, ByVal intCaseLength, ByVal intPalletLength, ByVal strBottleBarcode, ByVal strVAPGiftUnitBarcode, ByVal strCaseBarcode, ByVal strPalletBarcode, ByVal strUserName, ByVal intBottleNetWeight, ByVal intVAPGiftUnitNetWeight, ByVal intCaseNetWeight, ByVal intPalletNetWeight) As String
        Return "UPDATE mstProductDataDimension SET refBrandID = " & refBrandID & ", strDescription = '" & CleanString(strDescription) & "', strAccpacCode = '" & CleanString(strAccpacCode) & "', strVendorCode = '" & CleanString(strVendorCode) &
      "',intABVPerc = " & intABVPerc & ", intImportCertNo = " & intImportCertNo & ", intPalletBase = " & intPalletBase & ", intPalletLayer = " & intPalletLayer & ", intPalletTotal = " & intPalletTotal &
      ", intBottleWeight = " & intBottleWeight & ", intBottleHeight = " & intBottleHeight & ", intBottleWidth = " & intBottleWidth & ", intBottleLength = " & intBottleLength &
      ", strBottleBarcode = '" & strBottleBarcode & "', intVAPGiftUnitWeight = " & intVAPGiftUnitWeight & ", intVAPGiftUnitHeight = " & intVAPGiftUnitHeight &
      ", intVAPGiftUnitWidth = " & intVAPGiftUnitWidth & ", intVAPGiftUnitLength = " & intVAPGiftUnitLength & ", strVAPGiftUnitBarcode = '" & strVAPGiftUnitBarcode &
      "', intCaseWeight = " & intCaseWeight & ", intCaseHeight = " & intCaseHeight & ", intCaseWidth = " & intCaseWidth &
      ", intCaseLength = " & intCaseLength & ", strCaseBarcode = '" & strCaseBarcode & "', intPalletWeight = " & intPalletWeight &
      ", intPalletHeight = " & intPalletHeight & ", intPalletWidth = " & intPalletWidth & ", intPalletLength = " & intPalletLength & ", strPalletBarcode = '" & strPalletBarcode &
      "', strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "', intBottleNetWeight = " & intBottleNetWeight & ", intVAPGiftUnitNetWeight = " & intVAPGiftUnitNetWeight &
      ", intCaseNetWeight = " & intCaseNetWeight & ", intPalletNetWeight = " & intPalletNetWeight & " WHERE strProdCode='" & CleanString(strProdCode) & "' "

    End Function

    Public Function sql_insert_ProductDataDimension(ByVal strProdCode, ByVal refBrandID, ByVal strDescription, ByVal strAccpacCode, ByVal strVendorCode, ByVal intABVPerc, ByVal intImportCertNo, ByVal intPalletBase, ByVal intPalletLayer, ByVal intPalletTotal, ByVal intBottleWeight, ByVal intVAPGiftUnitWeight, ByVal intCaseWeight, ByVal intPalletWeight, ByVal intBottleHeight, ByVal intVAPGiftUnitHeight, ByVal intCaseHeight, ByVal intPalletHeight, ByVal intBottleWidth, ByVal intVAPGiftUnitWidth, ByVal intCaseWidth, ByVal intPalletWidth, ByVal intBottleLength, ByVal intVAPGiftUnitLength, ByVal intCaseLength, ByVal intPalletLength, ByVal strBottleBarcode, ByVal strVAPGiftUnitBarcode, ByVal strCaseBarcode, ByVal strPalletBarcode, ByVal strUserName, ByVal intBottleNetWeight, ByVal intVAPGiftUnitNetWeight, ByVal intCaseNetWeight, ByVal intPalletNetWeight) As String
        Return "INSERT INTO mstProductDataDimension ( strProdCode, refBrandID, strDescription, strAccpacCode, strVendorCode,intABVPerc, intImportCertNo ,intPalletBase ,intPalletLayer ,intPalletTotal ,intBottleWeight ,intVAPGiftUnitWeight ,intCaseWeight ,intPalletWeight ,intBottleHeight ,intVAPGiftUnitHeight ,intCaseHeight ,intPalletHeight ,intBottleWidth ,intVAPGiftUnitWidth ,intCaseWidth ,intPalletWidth ,intBottleLength , intVAPGiftUnitLength , intCaseLength , intPalletLength , strBottleBarcode , strVAPGiftUnitBarcode , strCaseBarcode , strPalletBarcode , intBottleNetWeight, intVAPGiftUnitNetWeight, intCaseNetWeight, intPalletNetWeight,  strLastUser, dtStamp )" &
       "VALUES ('" & CleanString(strProdCode) & "'," & refBrandID & ",'" & CleanString(strDescription) & "','" & CleanString(strAccpacCode) & "','" & CleanString(strVendorCode) & "'," & intABVPerc & "," & intImportCertNo & "," & intPalletBase & "," & intPalletLayer & "," & intPalletTotal & "," & intBottleWeight & "," & intVAPGiftUnitWeight & "," & intCaseWeight & "," & intPalletWeight & "," & intBottleHeight & "," & intVAPGiftUnitHeight & "," & intCaseHeight & "," & intPalletHeight & "," & intBottleWidth & "," & intVAPGiftUnitWidth & "," & intCaseWidth & "," & intPalletWidth & "," & intBottleLength & ", " & intVAPGiftUnitLength & ", " & intCaseLength & ", " & intPalletLength & ", '" & strBottleBarcode & "', '" & strVAPGiftUnitBarcode & "', '" & strCaseBarcode & "', '" & strPalletBarcode & "', '" & intBottleNetWeight & "', '" & intVAPGiftUnitNetWeight & "', '" & intCaseNetWeight & "', '" & intPalletNetWeight & "', '" & strUserName & "','" & dbDate() & "')"
    End Function
    Public Function sql_get_ProductDataDimension_dw(ByVal strProdCode As String) As String
      Return "SELECT mstProductDataDimension.strProdCode, mstProductDataDimension.refBrandID, mstProductDataDimension.strDescription, mstProductDataDimension.strAccpacCode, mstProductDataDimension.strVendorCode, mstProductDataDimension.intABVPerc, mstProductDataDimension.intImportCertNo , mstProductDataDimension.intPalletBase , mstProductDataDimension.intPalletLayer , mstProductDataDimension.intPalletTotal ,mstProductDataDimension.intBottleWeight , mstProductDataDimension.intVAPGiftUnitWeight , mstProductDataDimension.intCaseWeight , mstProductDataDimension.intPalletWeight , mstProductDataDimension.intBottleHeight , mstProductDataDimension.intVAPGiftUnitHeight , mstProductDataDimension.intCaseHeight , mstProductDataDimension.intPalletHeight , mstProductDataDimension.intBottleWidth , mstProductDataDimension.intVAPGiftUnitWidth , mstProductDataDimension.intCaseWidth , mstProductDataDimension.intPalletWidth , mstProductDataDimension.intBottleLength , mstProductDataDimension.intVAPGiftUnitLength , mstProductDataDimension.intCaseLength , mstProductDataDimension.intPalletLength , mstProductDataDimension.strBottleBarcode , mstProductDataDimension.strVAPGiftUnitBarcode , mstProductDataDimension.strCaseBarcode , mstProductDataDimension.strPalletBarcode " &
         "FROM mstProductDataDimension INNER JOIN mstBrand ON mstProductDataDimension.refBrandID = mstBrand.BrandID WHERE mstProductDataDimension.strProdCode = '" & CleanString(strProdCode) & "' "
   End Function
   Public Function sql_update_ProductDataDimension_dw(ByVal strProdCode As String, ByVal fields As String) As String
      Return "UPDATE dwdProductDataDimension SET " & fields & " WHERE strProdCode = '" & strProdCode & "' "
   End Function
   Public Function sql_insert_ProductDataDimension_dw(ByVal strProdCode As String) As String
      Return "INSERT INTO dwdProductDataDimension (strProdCode, refBrandID, strDescription, strAccpacCode, strVendorCode,intABVPerc, intImportCertNo ,intPalletBase ,intPalletLayer ,intPalletTotal ,intBottleWeight ,intVAPGiftUnitWeight ,intCaseWeight ,intPalletWeight ,intBottleHeight ,intVAPGiftUnitHeight ,intCaseHeight ,intPalletHeight ,intBottleWidth ,intVAPGiftUnitWidth ,intCaseWidth ,intPalletWidth ,intBottleLength , intVAPGiftUnitLength , intCaseLength , intPalletLength , strBottleBarcode , strVAPGiftUnitBarcode , strCaseBarcode , strPalletBarcode) " &
   sql_get_ProductDataDimension_dw(strProdCode)
   End Function

    Public Function sql_insert_Product_dw(ByVal strProdCode As String) As String
      Return "INSERT INTO dwdProduct (strProdCode, strProductDesc, intPack, intSize, dblPrice, strLongProdDesc, strType, strType2, intShortCode, strPriceSeg, strCategory, blnActive, dblFlat, cwsicat,  strCategory2, strPrincipalMaterialRefNum, strPrincipalProdDesc, strPrincipal, strPrincipalFYStart, strPrincipalFYEnd, strCompany, refBrandID, strBrand, blnBrandActive, strBottleBarcode, strCaseBarcode, strGiftboxBarcode, intDisplayOrder, strBrandVarietal, strGroupingDaily, strGroupingPrimary, strGroupingSecondary, blnQF, blnDataTransfer, blnCompProduct, strTags) " & _
   sql_get_Product_dw(strProdCode)
   End Function

   Public Function sql_update_Product_dw(ByVal strProdCode As String, ByVal fields As String) As String
      Return "UPDATE dwdProduct SET " & fields & " WHERE strProdCode = '" & strProdCode & "' "
   End Function

   '////   PRODUCT COMPETITOR SET

   'Get all active comp set records for a primary product, ordered by position
   Public Function sql_get_ProductCompetitorSet(ByVal strPrimaryProdCode As String) As String
      Return "SELECT mstProductCompetitorSet.*, " & _
             "mstProduct_Comp.strProductDesc AS strCompProductDesc, mstProduct_Comp.strLongProdDesc AS strCompLongProdDesc, " & _
             "mstProduct_Comp.strProdCode AS strCompProdCode, mstBrand_Comp.strBrand AS strCompBrand " & _
             "FROM mstProductCompetitorSet " & _
             "INNER JOIN mstProduct AS mstProduct_Comp ON mstProductCompetitorSet.strCompProdCode = mstProduct_Comp.strProdCode " & _
             "LEFT JOIN mstBrand AS mstBrand_Comp ON mstProduct_Comp.refBrandID = mstBrand_Comp.BrandID " & _
             "WHERE mstProductCompetitorSet.strPrimaryProdCode = '" & CleanString(strPrimaryProdCode) & "' " & _
             "AND mstProductCompetitorSet.blnActive = 1 " & _
             "ORDER BY mstProductCompetitorSet.intPosition"
   End Function

   'Get comp set name for a primary product (gets from first active record)
   Public Function sql_get_ProductCompetitorSetName(ByVal strPrimaryProdCode As String) As String
      Return "SELECT TOP 1 strCompSetName " & _
             "FROM mstProductCompetitorSet " & _
             "WHERE strPrimaryProdCode = '" & CleanString(strPrimaryProdCode) & "' " & _
             "AND blnActive = 1 " & _
             "ORDER BY intPosition"
   End Function

   'Get comp set records with optional inactive filter
   Public Function sql_get_ProductCompetitorSet(ByVal strPrimaryProdCode As String, ByVal blnIncludeInactive As Boolean) As String
      Dim strWhere As String = "WHERE mstProductCompetitorSet.strPrimaryProdCode = '" & CleanString(strPrimaryProdCode) & "' "
      If Not blnIncludeInactive Then
         strWhere &= "AND mstProductCompetitorSet.blnActive = 1 "
      End If
      Return "SELECT mstProductCompetitorSet.*, " & _
             "mstProduct_Comp.strProductDesc AS strCompProductDesc, mstProduct_Comp.strLongProdDesc AS strCompLongProdDesc, " & _
             "mstProduct_Comp.strProdCode AS strCompProdCode, mstBrand_Comp.strBrand AS strCompBrand " & _
             "FROM mstProductCompetitorSet " & _
             "INNER JOIN mstProduct AS mstProduct_Comp ON mstProductCompetitorSet.strCompProdCode = mstProduct_Comp.strProdCode " & _
             "LEFT JOIN mstBrand AS mstBrand_Comp ON mstProduct_Comp.refBrandID = mstBrand_Comp.BrandID " & _
             strWhere & _
             "ORDER BY mstProductCompetitorSet.intPosition"
   End Function

   'Get single comp set record by ID
   Public Function sql_get_ProductCompetitorSet_ByID(ByVal ProductCompetitorSetID As Integer) As String
      Return "SELECT mstProductCompetitorSet.*, " & _
             "mstProduct_Comp.strProductDesc AS strCompProductDesc, mstProduct_Comp.strLongProdDesc AS strCompLongProdDesc, " & _
             "mstProduct_Comp.strProdCode AS strCompProdCode, mstBrand_Comp.strBrand AS strCompBrand, " & _
             "mstProduct_Primary.strProductDesc AS strPrimaryProductDesc, mstProduct_Primary.strLongProdDesc AS strPrimaryLongProdDesc " & _
             "FROM mstProductCompetitorSet " & _
             "INNER JOIN mstProduct AS mstProduct_Comp ON mstProductCompetitorSet.strCompProdCode = mstProduct_Comp.strProdCode " & _
             "INNER JOIN mstProduct AS mstProduct_Primary ON mstProductCompetitorSet.strPrimaryProdCode = mstProduct_Primary.strProdCode " & _
             "LEFT JOIN mstBrand AS mstBrand_Comp ON mstProduct_Comp.refBrandID = mstBrand_Comp.BrandID " & _
             "WHERE mstProductCompetitorSet.ProductCompetitorSetID = " & ProductCompetitorSetID
   End Function

   'Insert new comp set record
   Public Function sql_insert_ProductCompetitorSet(ByVal strPrimaryProdCode As String, ByVal strCompProdCode As String, ByVal intPosition As Integer, ByVal strCompSetName As String, ByVal strUserName As String) As String
      Return "INSERT INTO mstProductCompetitorSet (strPrimaryProdCode, strCompProdCode, intPosition, strCompSetName, blnActive, strLastUser, dtStamp) " & _
             "VALUES ('" & CleanString(strPrimaryProdCode) & "', '" & CleanString(strCompProdCode) & "', " & intPosition & ", '" & CleanString(strCompSetName) & "', 1, '" & CleanString(strUserName) & "', '" & dbDate() & "')"
   End Function

   'Update existing comp set record
   Public Function sql_update_ProductCompetitorSet(ByVal ProductCompetitorSetID As Integer, ByVal strCompProdCode As String, ByVal intPosition As Integer, ByVal strCompSetName As String, ByVal blnActive As Boolean, ByVal strUserName As String) As String
      Return "UPDATE mstProductCompetitorSet SET " & _
             "strCompProdCode = '" & CleanString(strCompProdCode) & "', " & _
             "intPosition = " & intPosition & ", " & _
             "strCompSetName = '" & CleanString(strCompSetName) & "', " & _
             "blnActive = " & CInt(blnActive) & ", " & _
             "strLastUser = '" & CleanString(strUserName) & "', " & _
             "dtStamp = '" & dbDate() & "' " & _
             "WHERE ProductCompetitorSetID = " & ProductCompetitorSetID
   End Function

   'Update comp set name for all records of a primary product
   Public Function sql_update_ProductCompetitorSetName(ByVal strPrimaryProdCode As String, ByVal strCompSetName As String, ByVal strUserName As String) As String
      Return "UPDATE mstProductCompetitorSet SET " & _
             "strCompSetName = '" & CleanString(strCompSetName) & "', " & _
             "strLastUser = '" & CleanString(strUserName) & "', " & _
             "dtStamp = '" & dbDate() & "' " & _
             "WHERE strPrimaryProdCode = '" & CleanString(strPrimaryProdCode) & "'"
   End Function

   'Update active status for all records of a primary product
   Public Function sql_update_ProductCompetitorSetActive(ByVal strPrimaryProdCode As String, ByVal blnActive As Boolean, ByVal strUserName As String) As String
      Return "UPDATE mstProductCompetitorSet SET " & _
             "blnActive = " & CInt(blnActive) & ", " & _
             "strLastUser = '" & CleanString(strUserName) & "', " & _
             "dtStamp = '" & dbDate() & "' " & _
             "WHERE strPrimaryProdCode = '" & CleanString(strPrimaryProdCode) & "'"
   End Function

   'Soft delete comp set record (set blnActive = 0)
   Public Function sql_delete_ProductCompetitorSet(ByVal ProductCompetitorSetID As Integer, ByVal strUserName As String) As String
      Return "UPDATE mstProductCompetitorSet SET " & _
             "blnActive = 0, " & _
             "strLastUser = '" & CleanString(strUserName) & "', " & _
             "dtStamp = '" & dbDate() & "' " & _
             "WHERE ProductCompetitorSetID = " & ProductCompetitorSetID
   End Function

   'Count active comp set records for a primary product (for max 10 validation)
   Public Function sql_count_ProductCompetitorSet_Active(ByVal strPrimaryProdCode As String) As String
      Return "SELECT COUNT(*) AS intCount " & _
             "FROM mstProductCompetitorSet " & _
             "WHERE strPrimaryProdCode = '" & CleanString(strPrimaryProdCode) & "' " & _
             "AND blnActive = 1"
   End Function

   'Check if a specific comp product already exists for a primary product (for duplicate validation)
   Public Function sql_check_ProductCompetitorSet_Exists(ByVal strPrimaryProdCode As String, ByVal strCompProdCode As String, Optional ByVal ProductCompetitorSetID As Integer = -1) As String
      Dim strWhere As String = "WHERE strPrimaryProdCode = '" & CleanString(strPrimaryProdCode) & "' " & _
                               "AND strCompProdCode = '" & CleanString(strCompProdCode) & "' "
      If ProductCompetitorSetID > 0 Then
         strWhere &= "AND ProductCompetitorSetID <> " & ProductCompetitorSetID & " "
      End If
      Return "SELECT COUNT(*) AS intCount FROM mstProductCompetitorSet " & strWhere
   End Function

   'Soft delete all comp set records for a primary product
   Public Function sql_delete_ProductCompetitorSet_ByPrimaryProduct(ByVal strPrimaryProdCode As String, ByVal strUserName As String) As String
      Return "UPDATE mstProductCompetitorSet SET " & _
             "blnActive = 0, " & _
             "strLastUser = '" & CleanString(strUserName) & "', " & _
             "dtStamp = '" & dbDate() & "' " & _
             "WHERE strPrimaryProdCode = '" & CleanString(strPrimaryProdCode) & "'"
   End Function

   '////   BRAND

   Public Function sql_get_Brand() As String
      Return "SELECT mstBrand.*, mstPrincipal.strPrincipal, mstCompany.strCompany, mstGroupingDaily.strGroupingDaily, mstGroupingPrimary.strGroupingPrimary, mstGroupingSecondary.strGroupingSecondary, mstUser.strUserName +'['+ CAST(mstUser.UserID AS varchar) +']' AS BrandManager2, mstUser_1.strUserName +' ['+ CAST(mstUser_1.UserID AS varchar) +']' AS BrandManager1 " & _
             "FROM mstBrand LEFT JOIN mstGroupingPrimary ON mstBrand.refGroupingPrimaryID = mstGroupingPrimary.GroupingPrimaryID LEFT JOIN mstGroupingSecondary ON mstBrand.refGroupingSecondaryID = mstGroupingSecondary.GroupingSecondaryID LEFT JOIN mstGroupingDaily ON mstBrand.refGroupingDailyID = mstGroupingDaily.GroupingDailyID LEFT JOIN mstUser ON mstBrand.refBrandManager2ID = mstUser.UserID LEFT JOIN mstUser AS mstUser_1 ON mstBrand.refBrandManagerID = mstUser_1.UserID LEFT JOIN mstCompany ON mstBrand.refCompanyID = mstCompany.CompanyID LEFT JOIN mstPrincipal ON mstBrand.refPrincipalID = mstPrincipal.PrincipalID "
   End Function

   Public Function sql_get_Brand(ByVal strBrand As String) As String
      Return sql_get_Brand() & " WHERE strBrand = '" & strBrand & "'"
   End Function

   Public Function sql_get_Brand(ByVal BrandID As Integer) As String
      Return sql_get_Brand() & " WHERE BrandID = " & BrandID
   End Function

   Public Function sql_get_Brand(ByVal strBrand As String, ByVal BrandID As Integer) As String
      Return sql_get_Brand() & " WHERE BrandID <> " & BrandID & " AND strBrand = '" & strBrand & "'"
   End Function

   Public Function sql_insert_Brand(ByVal refPrincipalID As Integer, ByVal refCompanyID As Integer, ByVal refBrandManagerID As Integer, ByVal refBrandManager2ID As Integer, ByVal strAccpacCode As String, ByVal strBrand As String, ByVal strGroupCode As String, ByVal strGroupAccounts As String, ByVal brand2 As String, ByVal brandrebate As String, ByVal brandToolkit As String, ByVal intLeadMonths As Integer, ByVal dblVariance As Double, ByVal blnActive As Integer, ByVal refGroupingDaily As Integer, ByVal refGroupingPrimary As Integer, ByVal refGroupingSecondary As Integer) As String
      Return "INSERT INTO mstBrand (refPrincipalID, refCompanyID, refBrandManagerID, refBrandManager2ID, refGroupingDailyID, refGroupingPrimaryID, refGroupingSecondaryID, strAccpacCode, strBrand, strGroupCode, strGroupAccounts, brand2, brandrebate, brandToolkit, intLeadMonths, intMonthsCover, dblVariance, blnActive, strLastUser, dtStamp)" & _
       " VALUES (" & refPrincipalID & "," & refCompanyID & "," & refBrandManagerID & "," & refBrandManager2ID & ", " & refGroupingDaily & ", " & refGroupingPrimary & ", " & refGroupingSecondary & ",'" & CleanString(strAccpacCode) & "','" & CleanString(strBrand) & "','" & CleanString(strGroupCode) & "','" & CleanString(strGroupAccounts) & "','" & CleanString(brand2) & "','" & CleanString(brandrebate) & "','" & CleanString(brandToolkit) & "'," & intLeadMonths & ",0 ," & dblVariance & ", " & blnActive & ", '" & UNAME & "','" & dbDate() & "')"
   End Function

   Public Function sql_update_Brand(ByVal BrandID As Integer, ByVal refPrincipalID As Integer, ByVal refCompanyID As Integer, ByVal refBrandManagerID As Integer, ByVal refBrandManager2ID As Integer, ByVal strAccpacCode As String, ByVal strBrand As String, ByVal strGroupCode As String, ByVal strGroupAccounts As String, ByVal brand2 As String, ByVal brandrebate As String, ByVal brandToolkit As String, ByVal intLeadMonths As Integer, ByVal dblVariance As Double, ByVal blnActive As Integer, ByVal refGroupingDaily As Integer, ByVal refGroupingPrimary As Integer, ByVal refGroupingSecondary As Integer, ByVal strUserName As String) As String
      Return "UPDATE mstBrand SET refPrincipalID = " & refPrincipalID & ", refCompanyID = " & refCompanyID & ", refBrandManagerID = " & _
       refBrandManagerID & ", refBrandManager2ID = " & refBrandManager2ID & ",  refGroupingDailyID = " & refGroupingDaily & ", refGroupingPrimaryID = " & refGroupingPrimary & ", refGroupingSecondaryID = " & refGroupingSecondary & _
       ", strAccpacCode = '" & CleanString(strAccpacCode) & _
       "', strBrand = '" & CleanString(strBrand) & "', strGroupCode = '" & CleanString(strGroupCode) & "', strGroupAccounts = '" & CleanString(strGroupAccounts) & _
       "', 	brand2 = '" & CleanString(brand2) & "', brandrebate = '" & CleanString(brandrebate) & "', brandToolkit = '" & CleanString(brandToolkit) & _
       "', intLeadMonths = " & intLeadMonths & ", dblVariance = " & dblVariance & ", blnActive = " & blnActive & ", strLastUser = '" & _
       strUserName & "', dtStamp = '" & dbDate() & "' WHERE BrandID = " & BrandID

   End Function

   Public Shared Function sql_update_Brand_dw(ByVal BrandID As Integer, ByVal strPrincipal As String, ByVal strCompany As String, ByVal strBrand As String, ByVal blnActive As Integer, ByVal strGroupingDaily As String, ByVal strGroupingPrimary As String, ByVal strGroupingSecondary As String) As String
      Return "UPDATE dwdProduct SET strBrand = '" & strBrand & "', blnBrandActive = " & blnActive & ", strGroupingDaily = '" & strGroupingDaily & "', strGroupingPrimary = '" & strGroupingPrimary & "', strGroupingSecondary = '" & strGroupingSecondary & "', strPrincipal = mstPrincipal.strPrincipal, " & _
              " strPrincipalFYStart = mstPrincipal.strFYStart, strPrincipalFYEnd = mstPrincipal.strFYEnd, strCompany = mstCompany.strCompany " & _
              "FROM mstPrincipal INNER JOIN mstBrand ON mstPrincipal.PrincipalID = mstBrand.refPrincipalID INNER JOIN mstCompany ON mstBrand.refCompanyID = mstCompany.CompanyID INNER JOIN dwdProduct ON mstBrand.BrandID = dwdProduct.refBrandID " & _
              "WHERE refBrandID = " & BrandID & " "
      Return "UPDATE dwdProduct SET strBrand = '" & CleanString(strBrand) & "', blnBrandActive = " & blnActive & ", strGroupingDaily = '" & strGroupingDaily & "', strGroupingPrimary = '" & strGroupingPrimary & "', strGroupingSecondary = '" & strGroupingSecondary & "'  WHERE refBrandID = " & BrandID & " "
   End Function

   '////   Segment

   Public Function sql_get_Segment(ByVal strSegment As String) As String
      Return "Select * From mstSegment WHERE strSegment = '" & strSegment & "'"
   End Function

   Public Function sql_get_Segment(ByVal SegmentID As Integer) As String
      Return "Select * From mstSegment WHERE SegmentID = " & SegmentID
   End Function

   Public Function sql_get_Segment(ByVal strSegment As String, ByVal SegmentID As Integer) As String
      Return "Select * From mstSegment WHERE SegmentID <> " & SegmentID & " AND strSegment = '" & strSegment & "'"
   End Function

   Public Function sql_insert_Segment(ByVal strSegment As String, ByVal strUserName As String) As String
      Return "INSERT INTO mstSegment (strSegment, strLastUser, dtStamp)" & _
       " VALUES ('" & CleanString(strSegment) & "', '" & strUserName & "','" & dbDate() & "')"
   End Function

   Public Function sql_update_Segment(ByVal SegmentID As Integer, ByVal strSegment As String, ByVal strUserName As String) As String
      Return "UPDATE mstSegment SET strSegment = '" & CleanString(strSegment) & "', strLastUser = '" & strUserName & _
      "', dtStamp = '" & dbDate() & "' WHERE SegmentID = " & SegmentID

   End Function

   Public Function sql_update_Segment_dw(ByVal dwSegment As String, ByVal strSegment As String) As String
      Return "UPDATE dwdCustomer SET strSegment = '" & CleanString(strSegment) & "'  WHERE strSegment = '" & dwSegment & "' "
   End Function

   '////   Channel
   Public Function sql_get_ChannelSegment() As String
      Return "Select * FROM mstChannelSegment"
   End Function

   Public Function sql_get_ChannelSegment(ByVal ChannelSegmentID As Integer) As String
      Return sql_get_ChannelSegment() & " WHERE ChannelSegmentID = " & ChannelSegmentID
   End Function

   Public Function sql_insert_ChannelSegment(ByVal txtChannelPrimary As String, ByVal txtChannelSecondary As String, ByVal txtSegmentPrimary As String, ByVal txtSegmentSecondary As String, ByVal strUserName As String) As String
      Return "INSERT INTO mstChannelSegment (strSegmentPrimary, strSegment, strChannelPrimary, strChannel, strLastUser, dtStamp)" & _
       " VALUES  ('" & CleanString(txtSegmentPrimary) & "', '" & CleanString(txtSegmentSecondary) & "', '" & CleanString(txtChannelPrimary) & "', '" & CleanString(txtChannelSecondary) & "', '" & strUserName & "', '" & dbDate() & "')"
   End Function

   Public Function sql_update_ChannelSegment(ByVal ChannelSegmentID As Integer, ByVal txtChannelPrimary As String, ByVal txtChannelSecondary As String, ByVal txtSegmentPrimary As String, ByVal txtSegmentSecondary As String, ByVal strUserName As String) As String
      Return "UPDATE mstChannelSegment SET strSegmentPrimary = '" & CleanString(txtSegmentPrimary) & "', strSegment = '" & CleanString(txtSegmentSecondary) & "', strChannelPrimary = '" & CleanString(txtChannelPrimary) & "', strChannel = '" & CleanString(txtChannelSecondary) & "', strLastUser = '" & strUserName & _
      "', dtStamp = '" & dbDate() & "' WHERE ChannelSegmentID = " & ChannelSegmentID

   End Function

   Public Function sql_update_ChannelSegment_dw(ByVal dwCP As String, ByVal dwCS As String, ByVal dwSP As String, ByVal dwSS As String) As String
      Return "UPDATE    dwdCustomer " & _
      "SET strChannelPrimary = mstChannelSegment.strChannelPrimary, strChannelSecondary = mstChannelSegment.strChannel, strSegmentPrimary = mstChannelSegment.strSegmentPrimary, strSegmentSecondary = mstChannelSegment.strSegment " & _
      "FROM dwdCustomer INNER JOIN mstCustomer ON dwdCustomer.strCustomerNo = mstCustomer.strCustomerNo INNER JOIN mstChannelSegment ON mstCustomer.refChannelSegmentID = mstChannelSegment.ChannelSegmentID " & _
      "WHERE (dwdCustomer.strChannelPrimary = '" & dwCP & "') AND (dwdCustomer.strChannelSecondary = '" & dwCS & "') AND (dwdCustomer.strSegmentPrimary = '" & dwSP & "') AND (dwdCustomer.strSegmentSecondary = '" & dwSS & "')"
   End Function

   '////   KeyAccount_Group

   Public Function sql_get_KeyAccount_Group(ByVal strGroupName As String) As String
      Return "Select mstKeyAccount_Group.*, mstKeyAccount.strKeyAccount From mstKeyAccount_Group LEFT JOIN mstKeyAccount ON KeyAccountID = refKeyAccountID WHERE strGroupName = '" & strGroupName & "'"
   End Function

   Public Function sql_get_KeyAccount_Group(ByVal KeyAccount_GroupID As Integer) As String
      Return "Select mstKeyAccount_Group.*, mstKeyAccount.strKeyAccount From mstKeyAccount_Group LEFT JOIN mstKeyAccount ON KeyAccountID = refKeyAccountID WHERE KeyAccount_GroupID = " & KeyAccount_GroupID
   End Function

   Public Function sql_get_KeyAccount_Group(ByVal strGroupName As String, ByVal KeyAccount_GroupID As Integer, ByVal refKeyAccountID As Integer) As String
      Return "Select mstKeyAccount_Group.*, mstKeyAccount.strKeyAccount From mstKeyAccount_Group LEFT JOIN mstKeyAccount ON KeyAccountID = refKeyAccountID WHERE KeyAccount_GroupID <> " & KeyAccount_GroupID & " AND refKeyAccountID <> " & refKeyAccountID & " AND strGroupName = '" & CleanString(strGroupName) & "'"
   End Function

   Public Function sql_insert_KeyAccount_Group(ByVal strGroupName As String, ByVal refKeyAccountID As Integer, ByVal strUserName As String) As String
      Return "INSERT INTO mstKeyAccount_Group (strGroupName, refKeyAccountID, strLastUser, dtStamp)" & _
       " VALUES ('" & CleanString(strGroupName) & "', " & refKeyAccountID & ", '" & strUserName & "','" & dbDate() & "')"
   End Function

   Public Function sql_update_KeyAccount_Group(ByVal KeyAccount_GroupID As Integer, ByVal strGroupName As String, ByVal refKeyAccountID As Integer, ByVal strUserName As String) As String
      Return "UPDATE mstKeyAccount_Group SET strGroupName = '" & CleanString(strGroupName) & "', refKeyAccountID = " & refKeyAccountID & ", strLastUser = '" & strUserName & _
      "', dtStamp = '" & dbDate() & "' WHERE KeyAccount_GroupID = " & KeyAccount_GroupID

   End Function

   Public Function sql_update_KeyAccount_Group_dw(ByVal dwKeyAccount_Group As String, ByVal strGroupName As String, ByVal dwKeyAccount As String, ByVal strKeyAccount As String) As String
      Return "UPDATE dwdCustomer SET strGroupName = '" & CleanString(strGroupName) & "', strKeyAccount = '" & CleanString(strKeyAccount) & "'  WHERE strGroupName = '" & dwKeyAccount_Group & "' AND strKeyAccount = '" & dwKeyAccount & "' "
   End Function
   '////   KeyAccount
   'Added strSnellKeyAcc x3 2009/01/16
   'v1.4.3 - 20131106 - Added txtPriceGroupCode to sql update/insert strings -christiaan 
   Public Function sql_get_KeyAccount(ByVal strKeyAccount As String) As String
      Return "Select * From mstKeyAccount WHERE strKeyAccount = '" & strKeyAccount & "'"
   End Function

   Public Function sql_get_KeyAccount(ByVal KeyAccountID As Integer) As String
      Return "Select * From mstKeyAccount WHERE KeyAccountID = " & KeyAccountID
   End Function

   Public Function sql_get_KeyAccount(ByVal strKeyAccount As String, ByVal KeyAccountID As Integer) As String
      Return "Select * From mstKeyAccount WHERE KeyAccountID <> " & KeyAccountID & " AND strKeyAccount = '" & strKeyAccount & "'"
   End Function

   'v1.4.3 - 20131106 - Added strPriceGroupCode
   Public Function sql_insert_KeyAccount(ByVal strKeyAccount As String, ByVal strAccpacName As String, ByVal strAccpacCode As String, ByVal strPriceGroupCode As String, ByVal blnOncon As Integer, ByVal blnToolkit As Integer, ByVal blnRebate As Integer, ByVal blnPayLessInvoice As Integer, ByVal blnActive As Integer, ByVal strUserName As String) As String
      Return "INSERT INTO mstKeyAccount (strKeyAccount, strAccpacName, strAccpacCode, strPriceGroupCode, blnOncon, blnToolkit, blnRebate, blnPayLessInvoice, blnActive, strLastUser, dtStamp) " & _
     "VALUES ('" & CleanString(strKeyAccount) & "', '" & CleanString(strAccpacName) & "', '" & CleanString(strAccpacCode) & "','" & CleanString(strPriceGroupCode) & "', " & blnOncon & ", " & blnToolkit & ", " & blnRebate & ", " & blnPayLessInvoice & ", " & blnActive & ", '" & strUserName & "','" & dbDate() & "')	"
   End Function

   'v1.4.3 - 20131106 - Added strPriceGroupCode
   Public Function sql_update_KeyAccount(ByVal KeyAccountID As Integer, ByVal strKeyAccount As String, ByVal strAccpacName As String, ByVal strAccpacCode As String, ByVal strPriceGroupCode As String, ByVal blnOncon As Integer, ByVal blnToolkit As Integer, ByVal blnRebate As Integer, ByVal blnPayLessInvoice As Integer, ByVal blnActive As Integer, ByVal strUserName As String) As String
      Return "UPDATE mstKeyAccount SET strKeyAccount = '" & CleanString(strKeyAccount) & "', strAccpacName = '" & CleanString(strAccpacName) & _
      "', strAccpacCode = '" & CleanString(strAccpacCode) & "', strPriceGroupCode = '" & CleanString(strPriceGroupCode) & "',  blnOncon = " & blnOncon & ", blnToolkit = " & blnToolkit & ", blnRebate = " & blnRebate & _
      ", blnPayLessInvoice = " & blnPayLessInvoice & ", blnActive = " & blnActive & ", strLastUser = '" & strUserName & _
      "', dtStamp = '" & dbDate() & "' WHERE KeyAccountID = " & KeyAccountID
   End Function

   Public Function sql_update_KeyAccount_dw(ByVal dwKeyAccount As String, ByVal strKeyAccount As String, ByVal PriceGroupCode As String) As String
      Return "UPDATE dwdCustomer SET strKeyAccount = '" & CleanString(strKeyAccount) & "',strPriceGroupCode = '" & CleanString(PriceGroupCode) & "'  WHERE strKeyAccount = '" & dwKeyAccount & "' "
   End Function

   '////   Rep
   'v.1.2.4
   Public Function sql_get_Rep(ByVal RepID As String) As String
      Return "SELECT mstRep.*, mstSegment.strSegment + ' [' + CAST(mstRep.refSegmentID AS varchar) + ']' AS Channel, mstBranch.strBranch + ' [' + CAST(mstRep.refBranchID AS varchar) + ']' AS Branch, mstRepManager.strRepManager + ' [' + CAST(mstRep.refRegionalManagerID AS varchar) + ']' AS RegionalManager, mstRepManager_2.strRepManager + ' [' + CAST(mstRep.refSalesManagerID AS varchar) + ']' AS SalesManager " & _
            "FROM mstSegment RIGHT OUTER JOIN mstRepManager RIGHT OUTER JOIN mstRep LEFT OUTER JOIN mstRepManager AS mstRepManager_2 ON mstRep.refSalesManagerID = mstRepManager_2.RepManagerID ON mstRepManager.RepManagerID = mstRep.refRegionalManagerID LEFT OUTER JOIN mstBranch ON mstRep.refBranchID = mstBranch.BranchID ON mstSegment.SegmentID = mstRep.refSegmentID " & _
            "WHERE RepID = '" & RepID & "'"
      'Return "Select mstRep.*, mstSegment.strSegment FROM mstRep LEFT JOIN mstSegment ON SegmentID = refSegmentID WHERE RepID = '" & RepID & "' "
   End Function

   Public Function sql_get_Rep(ByVal strRep As String, ByVal RepID As String) As String
      Return "Select mstRep.*, mstSegment.strSegment From mstRep LEFT JOIN mstSegment ON SegmentID = refSegmentID WHERE RepID <> '" & RepID & "' AND strRepName = '" & strRep & "' "
   End Function

   Public Function sql_insert_Rep(ByVal RepID As String, ByVal strRepName As String, ByVal strMarket As String, ByVal refBranchID As Integer, ByVal refSegmentID As Integer, ByVal strRepEmail As String, ByVal refRegionalManagerID As String, ByVal refSalesManagerID As String, ByVal blnRep As Integer, ByVal blnGenerateQF As Integer, ByVal strUserName As String) As String
      Return "INSERT INTO mstRep (RepID, strRepName, strMarket, refBranchID, refSegmentID, refRegionalManagerID, blnRep, blnGenerateQF, strRepEmail, refSalesManagerID, strLastUser, dtStamp)" & _
       " VALUES ('" & CleanString(RepID) & "', '" & CleanString(strRepName) & "', '" & CleanString(strMarket) & "', " & refBranchID & ", " & refSegmentID & ", " & refRegionalManagerID & ", " & blnRep & ", " & blnGenerateQF & ", '" & CleanString(strRepEmail) & "', " & refSalesManagerID & ", '" & strUserName & "','" & dbDate() & "')"
   End Function

   Public Function sql_update_Rep(ByVal RepID As String, ByVal strRepName As String, ByVal strMarket As String, ByVal refBranchID As Integer, ByVal refSegmentID As Integer, ByVal strRepEmail As String, ByVal refRegionalManagerID As String, ByVal refSalesManagerID As String, ByVal blnRep As Integer, ByVal blnGenerateQF As Integer, ByVal strUserName As String) As String
      Return "UPDATE mstRep SET strRepName = '" & CleanString(strRepName) & "', strMarket = '" & CleanString(strMarket) & "', refBranchID = " & refBranchID & _
      ", refSegmentID = " & refSegmentID & ", refRegionalManagerID = " & refRegionalManagerID & ", blnRep = " & blnRep & ", blnGenerateQF = " & blnGenerateQF & _
      ", strRepEmail = '" & CleanString(strRepEmail) & "', refSalesManagerID = " & refSalesManagerID & _
      ", strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "' WHERE RepID = '" & RepID & "' "

   End Function

   Public Function sql_update_Rep1_dw(ByVal dwRepID As String, ByVal strRepName As String) As String
      Return "UPDATE dwdCustomer SET strRepName = '" & CleanString(strRepName) & "' WHERE refRepID= '" & dwRepID & "' "
   End Function
   Public Function sql_update_Rep2_dw(ByVal dwRepID As String, ByVal strRepName As String) As String
      Return "UPDATE dwdCustomer SET strRepNameBB = '" & CleanString(strRepName) & "' WHERE refRepBBID = '" & dwRepID & "' "
   End Function
   Public Function sql_update_Rep3_dw(ByVal dwRepID As String, ByVal strRepName As String) As String
      Return "UPDATE dwdCustomer SET strRepNameDlx = '" & CleanString(strRepName) & "' WHERE refRepDlxID = '" & dwRepID & "' "
   End Function
   Public Function sql_update_Rep4_dw(ByVal dwRepID As String, ByVal strRepName As String) As String
      Return "UPDATE dwdCustomer SET strRepNameLux = '" & CleanString(strRepName) & "' WHERE refRepLuxID = '" & dwRepID & "' "
   End Function


   '////   Snellsupergroup

   Public Function sql_get_Snellsupergroup(ByVal SnellsupergroupID As String) As String
      Return "Select * FROM mstSnellsupergroup WHERE strSnellsupergroup = '" & SnellsupergroupID & "' "
   End Function

   Public Function sql_get_Snellsupergroup(ByVal strSnellsupergroup As String, ByVal SnellsupergroupID As String) As String
      Return "Select * FROM mstSnellsupergroup WHERE strSnellsupergroup <> '" & SnellsupergroupID & "' AND strSnellsupergroupName = '" & strSnellsupergroup & "' "
   End Function

   Public Function sql_insert_Snellsupergroup(ByVal SnellsupergroupID As String, ByVal strSnellsupergroupName As String, ByVal strUserName As String) As String
      Return "INSERT INTO mstSnellsupergroup (strSnellsupergroup, strSnellsupergroupName, strLastUser, dtStamp)" & _
       " VALUES ('" & CleanString(SnellsupergroupID) & "', '" & CleanString(strSnellsupergroupName) & "', '" & strUserName & "','" & dbDate() & "')"
   End Function

   Public Function sql_update_Snellsupergroup(ByVal SnellsupergroupID As String, ByVal strSnellsupergroupName As String, ByVal strUserName As String) As String
      Return "UPDATE mstSnellsupergroup SET strSnellsupergroupName = '" & CleanString(strSnellsupergroupName) & "', strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "' WHERE strSnellsupergroup = '" & SnellsupergroupID & "' "
   End Function

   Public Function sql_update_Snellsupergroup_dw(ByVal dwSnellsupergroupID As String, ByVal strSnellsupergroupName As String) As String
      Return "UPDATE dwdCustomer SET strSnellsupergroupName = '" & CleanString(strSnellsupergroupName) & "' WHERE strSnellsupergroup= '" & dwSnellsupergroupID & "' "
   End Function


   '////   Exports

   Public Shared Function sql_get_Export() As String
      Return "SELECT mstExports.*, mstCustomer.strCustomerNo, mstCustomer.strCustomerName, mstCustomer.refBranchID, mstProduct.refBrandID, mstProduct.dblPrice, mstProduct.strProdCode " & _
        "FROM mstExports LEFT OUTER JOIN mstCustomer ON mstExports.mstCustomer = mstCustomer.strCustomerNo LEFT OUTER JOIN mstProduct ON mstExports.mstProdCode = mstProduct.strProdCode "
   End Function

   Public Shared Function sql_get_Export(ByVal ExportID As Double) As String
      Return sql_get_Export() & " WHERE ExportID = " & ExportID & " "
   End Function

   Public Function sql_insert_Export(ByVal mstPeriod As String, ByVal mstCustomer As String, ByVal mstProdCode As String, ByVal dblUnits As Double, ByVal dblGSV As Double, ByVal dblNSV As Double, ByVal dblNettEx As Double, ByVal strCountry As String, ByVal strBatch As String, ByVal strUserName As String) As String
      Return "INSERT INTO mstExports (mstPeriod, mstCustomer, mstProdCode, dblUnits, dblGSV, dblNSV, dblNettEx, strCountry, blnProcessed, refBatch, strLastUser, dtStamp) " & _
        "VALUES ('" & mstPeriod & "', '" & CleanString(mstCustomer) & "', '" & CleanString(mstProdCode) & "', '" & dblUnits & "', '" & dblGSV & "', '" & dblNSV & "', '" & dblNettEx & "', '" & CleanString(strCountry) & "', '" & 0 & "', '" & Date.Today.Ticks.ToString.Substring(0, 12) & strBatch & "', '" & strUserName & "','" & dbDate() & "')"
   End Function

   Public Function sql_update_Export(ByVal ExportID As Double, ByVal mstPeriod As String, ByVal mstCustomer As String, ByVal mstProdCode As String, ByVal dblUnits As Double, ByVal dblGSV As Double, ByVal dblNSV As Double, ByVal dblNettEx As Double, ByVal strCountry As String, ByVal strUserName As String) As String
      Return "UPDATE mstExports SET mstPeriod = '" & mstPeriod & "', mstCustomer = '" & CleanString(mstCustomer) & "', mstProdCode = '" & CleanString(mstProdCode) & "', dblUnits = " & dblUnits & ", dblGSV = " & dblGSV & ", dblNSV = " & dblNSV & ", dblNettEx = " & dblNettEx & ", strCountry = '" & CleanString(strCountry) & "', strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "' " & _
          "WHERE ExportID = " & ExportID & " "
   End Function

   Public Shared Function sql_process_Export(ByVal ExportID As Double, ByVal refBatch As String, ByVal strUserName As String) As String
      Return "UPDATE mstExports SET blnProcessed = 1, refBatch = '" & refBatch & "', strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "'" & _
          "WHERE ExportID = " & ExportID & " "
   End Function

   Public Shared Function sql_process_Export_undo(ByVal refBatch As String, ByVal strUserName As String) As String
      Return "UPDATE mstExports SET blnProcessed = 0, refBatch = '" & Date.Today.Ticks.ToString.Substring(0, 12) & "959595" & "', strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "'" & _
          "WHERE refBatch = '" & refBatch & "' "
   End Function

   'generating doc numbers:
   'format: "Y" & fill("0") + ExportID (max length = 8)

   Public Shared Function sql_get_Exports(ByVal refBatch As String) As String
      Return "SELECT mstExports.mstPeriod, mstExports.mstCustomer, mstExports.mstProdCode, 'Y' + RIGHT('000000' + CAST(mstExports.ExportID AS VARCHAR), 7) AS refDocumentNo, 'I' AS strDocumentType, mstExports.dblNSV, mstExports.dblGSV, mstExports.dblUnits, mstExports.dblUnits AS lngUnitsTotal, 0 AS lngUnitsFreeStock, 0 AS lng9lFreeStock, ROUND(mstExports.dblUnits * mstProduct.intPack * mstProduct.intSize / 9000, 4) AS dbl9l, ROUND(mstExports.dblUnits * mstProduct.intPack * mstProduct.intSize / 9000, 4) AS dbl9lTotal, mstExports.dblNettEx, left(mstExports.dtStamp, 8) as strDate " & _
        "FROM mstProduct INNER JOIN mstExports ON mstProduct.strProdCode = mstExports.mstProdCode " & _
        "WHERE mstExports.refBatch = '" & refBatch & "'"
   End Function

   Public Shared Function sql_insert_Sales_Exports(ByVal refBatch As String) As String
      Return "INSERT INTO dwfSales (refPeriod, refCustomerNo, refProdCode, refDocumentNo, strDocumentType, dblNSV, dblGSV, lngUnits, lngUnitsTotal, lngUnitsFreeStock, dbl9lFreeStock, dbl9l, dbl9lTotal, dblNettEx, strDate) " & _
        sql_get_Exports(refBatch)
   End Function

   Public Shared Function sql_delete_Sales_Exports(ByVal refBatch As String) As String
      Return "DELETE FROM dwfSales WHERE refDocumentNo IN " & _
        "(SELECT 'Y' + RIGHT('000000' + CAST(mstExports.ExportID AS VARCHAR), 7) AS refDocumentNo FROM mstProduct INNER JOIN mstExports ON mstProduct.strProdCode = mstExports.mstProdCode " & _
        "WHERE mstExports.refBatch = '" & refBatch & "') "
   End Function

   '//// PRINCIPAL

   Public Shared Function sql_get_Principal() As String
      Return "SELECT mstPrincipal.* FROM mstPrincipal "
   End Function

   Public Shared Function sql_get_Principal(ByVal PrincipalID As Double) As String
      Return sql_get_Principal() & " WHERE PrincipalID = " & PrincipalID & " "
   End Function

   Public Shared Function sql_get_Principal(ByVal strPrincipal As String, ByVal PrincipalID As Double) As String
      Return sql_get_Principal() & " WHERE strPrincipal = '" & CleanString(strPrincipal) & "' AND PrincipalID <> " & PrincipalID & " "
   End Function

   Public Function sql_insert_Principal(ByVal strPrincipal As String, ByVal strFYStart As String, ByVal strFYEnd As String, ByVal strUserName As String) As String

      Return "INSERT INTO mstPrincipal (strPrincipal, strFYStart, strFYEnd, strLastUser, dtStamp) " & _
        "VALUES ('" & CleanString(strPrincipal) & "', '" & CleanString(strFYStart) & "', '" & CleanString(strFYEnd) & "', '" & strUserName & "','" & dbDate() & "')"
   End Function

   Public Function sql_update_Principal(ByVal PrincipalID As Double, ByVal strPrincipal As String, ByVal strFYStart As String, ByVal strFYEnd As String, ByVal strUserName As String) As String
      Return "UPDATE mstPrincipal SET  strPrincipal ='" & CleanString(strPrincipal) & "', strFYStart ='" & CleanString(strFYStart) & "', strFYEnd ='" & CleanString(strFYEnd) & "', strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "' " & _
          "WHERE PrincipalID = " & PrincipalID & " "
   End Function

   Public Shared Function sql_update_Principal_dw(ByVal dwPrincipal As String, ByVal strPrincipal As String, ByVal strFYStart As String, ByVal strFYEnd As String) As String
      Return "UPDATE dwdProduct SET strPrincipal ='" & CleanString(strPrincipal) & "', strPrincipalFYStart ='" & CleanString(strFYStart) & "', strPrincipalFYEnd ='" & CleanString(strFYEnd) & "' WHERE strPrincipal = '" & dwPrincipal & "'"
   End Function

   '//// COMPANY

   Public Shared Function sql_get_Company() As String
      Return "SELECT mstCompany.* FROM mstCompany "
   End Function

   Public Shared Function sql_get_Company(ByVal CompanyID As Double) As String
      Return sql_get_Company() & " WHERE CompanyID = " & CompanyID & " "
   End Function

   Public Shared Function sql_get_Company(ByVal strCompany As String, ByVal CompanyID As Double) As String
      Return sql_get_Company() & " WHERE strCompany = '" & CleanString(strCompany) & "' AND CompanyID <> " & CompanyID & " "
   End Function

   Public Function sql_insert_Company(ByVal strCompany As String, ByVal strFYStart As String, ByVal strFYEnd As String, ByVal strUserName As String) As String

      Return "INSERT INTO mstCompany (strCompany, strFYStart, strFYEnd, strLastUser, dtStamp) " & _
        "VALUES ('" & CleanString(strCompany) & "', '" & CleanString(strFYStart) & "', '" & CleanString(strFYEnd) & "', '" & strUserName & "','" & dbDate() & "')"
   End Function

   Public Function sql_update_Company(ByVal CompanyID As Double, ByVal strCompany As String, ByVal strFYStart As String, ByVal strFYEnd As String, ByVal strUserName As String) As String
      Return "UPDATE mstCompany SET  strCompany ='" & CleanString(strCompany) & "', strFYStart ='" & CleanString(strFYStart) & "', strFYEnd ='" & CleanString(strFYEnd) & "', strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "' " & _
          "WHERE CompanyID = " & CompanyID & " "
   End Function

   Public Shared Function sql_update_Company_dw(ByVal dwCompany As String, ByVal strCompany As String) As String
      Return "UPDATE dwdProduct SET strCompany ='" & CleanString(strCompany) & "' WHERE strCompany = '" & dwCompany & "'"
   End Function

   '////   BRANCH

   Public Shared Function sql_get_Branch() As String
      Return "SELECT mstBranch.* FROM mstBranch "
   End Function

   Public Shared Function sql_get_Branch(ByVal BranchID As Double) As String
      Return sql_get_Branch() & " WHERE BranchID = " & BranchID & " "
   End Function

   Public Shared Function sql_get_Branch(ByVal strCode As String, ByVal strSAPCode As String, ByVal BranchID As Double) As String
      Return sql_get_Branch() & " WHERE strCode = '" & CleanString(strCode) & "' AND strSAPCode = '" & CleanString(strSAPCode) & "' AND BranchID <> " & BranchID & " "
   End Function

   Public Function sql_insert_Branch(ByVal strCode As String, ByVal strBranch As String, ByVal strSalesOffice As String, ByVal strRegion As String, ByVal strSAPCode As String, ByVal strRegionCode As String, ByVal strRegionDesc As String, ByVal strRegionAccpacCode As String, ByVal strRegionDesc2 As String, ByVal strUserName As String) As String
      Return "INSERT INTO mstBranch (strCode, strBranch, strSalesOffice, strRegion, strSAPCode, strRegionCode, strRegionDesc, strRegionAccpacCode, strRegionDesc2, strLastUser, dtStamp)" & _
       " VALUES ('" & CleanString(strCode) & "', '" & CleanString(strBranch) & "', '" & CleanString(strSalesOffice) & "', '" & CleanString(strRegion) & "', '" & CleanString(strSAPCode) & "', '" & CleanString(strRegionCode) & "', '" & CleanString(strRegionDesc) & "', '" & CleanString(strRegionAccpacCode) & "', '" & CleanString(strRegionDesc2) & "', '" & strUserName & "','" & dbDate() & "')"
   End Function

   Public Function sql_update_Branch(ByVal BranchID As Double, ByVal strCode As String, ByVal strBranch As String, ByVal strSalesOffice As String, ByVal strRegion As String, ByVal strSAPCode As String, ByVal strRegionCode As String, ByVal strRegionDesc As String, ByVal strRegionAccpacCode As String, ByVal strRegionDesc2 As String, ByVal strUserName As String) As String
      Return "UPDATE mstBranch SET strCode = '" & CleanString(strCode) & "', strBranch = '" & CleanString(strBranch) & "', strSalesOffice = '" & CleanString(strSalesOffice) & "', strRegion = '" & CleanString(strRegion) & "', strSAPCode = '" & CleanString(strSAPCode) & "', strRegionCode = '" & CleanString(strRegionCode) & "', strRegionDesc = '" & CleanString(strRegionDesc) & "', strRegionAccpacCode = '" & CleanString(strRegionAccpacCode) & "', strRegionDesc2 = '" & CleanString(strRegionDesc2) & "', strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "' WHERE BranchID = " & BranchID & " "
   End Function

   Public Function sql_update_Branch_dw(ByVal dwBranch As String) As String
      Return "UPDATE dwdCustomer SET strCode = b.strCode, strBranch = b.strBranch, strSalesOffice = b.strSalesOffice, strRegion = b.strRegion, strSAPCode = b.strSAPCode, strRegionCode = b.strRegionCode, strRegionDesc = b.strRegionDesc, strRegionAccpacCode = b.strRegionAccpacCode, strRegionDesc2 = b.strRegionDesc2 " & _
        "FROM dwdCustomer INNER JOIN mstBranch AS b ON dwdCustomer.strBranch = b.strBranch WHERE dwdCustomer.strBranch = '" & dwBranch & "'"
   End Function

   '//// NON RGBC CUSTOMERS

   Public Function sql_get_Not_RGBC_Customer(ByVal strCustomerNo As String) As String
      Return "Select mstNot_RGBC_Customer.* , mstRep.refSegmentID, mstSegment.strSegment FROM mstNot_RGBC_Customer LEFT OUTER JOIN mstRep ON mstNot_RGBC_Customer.refRepID = mstRep.RepID LEFT OUTER JOIN mstSegment ON mstRep.refSegmentID = mstSegment.SegmentID WHERE strCustomerNo = '" & CleanString(strCustomerNo) & "'"
   End Function

   Public Function sql_get_Not_RGBC_Customer_LIKE(ByVal strCustomerName As String) As String
      Return "Select * From mstNot_RGBC_Customer WHERE strCustomerName LIKE '%" & CleanString(strCustomerName) & "%'"
   End Function

   Public Function sql_get_Not_RGBC_Customer(ByVal strCustomerNo As String, ByVal strCustomerName As String) As String
      Return "Select * From mstNot_RGBC_Customer WHERE strCustomerNo <> '" & CleanString(strCustomerNo) & "' AND strCustomerName = '" & CleanString(strCustomerName) & "'"
   End Function

    Public Function sql_insert_Not_RGBC_Customer(
        ByVal strCustomerNo As String, ByVal strCustomerName As String, ByVal strSnellArea As String,
        ByVal refRepID As String, ByVal refBranchID As Integer, ByVal refChannelSegmentID As Integer,
        ByVal blnActive As Integer, ByVal refType1ID As Integer, ByVal refType2ID As Integer,
        ByVal strUserName As String, ByVal txtAddress As String, ByVal txtEmail As String, ByVal txtTel As String,
        ByVal refKeyAccount_GroupID As Integer
    ) As String
        ' NOTE: dtStamp must be wrapped in quotes to form valid SQL, consistent with sql_update_Not_RGBC_Customer
        Return "INSERT INTO mstNot_RGBC_Customer (strCustomerNo, strCustomerName, strSnellArea, refRepID, refBranchID, refChannelSegmentID, blnActive, refType1ID, refType2ID, strLastUser, refKeyAccount_GroupID, dtStamp, txtAddress, strEmail, strTelephone) " &
               "VALUES('" & CleanString(strCustomerNo) & "','" & CleanString(strCustomerName) & "', '" & CleanString(strSnellArea) & "', '" & CleanString(refRepID) & "', " & refBranchID & ", " & refChannelSegmentID & ", " & blnActive & ", " & refType1ID & ", " & refType2ID & ", '" & strUserName & "'," & refKeyAccount_GroupID & ", '" & dbDate() & "', '" & CleanString(txtAddress) & "', '" & CleanString(txtEmail) & "', '" & CleanString(txtTel) & "')"
    End Function

    Public Function sql_update_Not_RGBC_Customer(
        ByVal strCustomerNo As String, ByVal strCustomerName As String, ByVal strSnellArea As String,
        ByVal refRepID As String, ByVal refBranchID As Integer, ByVal refChannelSegmentID As Integer,
        ByVal blnActive As Integer, ByVal refType1ID As Integer, ByVal refType2ID As Integer,
        ByVal strUserName As String, ByVal txtAddress As String, ByVal txtEmail As String, ByVal txtTel As String,
        ByVal refKeyAccount_GroupID As Integer
    ) As String
        Return "UPDATE mstNot_RGBC_Customer SET strCustomerName = '" & CleanString(strCustomerName) & "', strSnellArea = '" & CleanString(strSnellArea) & "', refRepID = '" & CleanString(refRepID) & "', refBranchID = " & refBranchID & ", refChannelSegmentID = " & refChannelSegmentID & ", blnActive = " & blnActive & ", refType1ID = " & refType1ID & ", refType2ID = " & refType2ID & ", strLastUser = '" & strUserName & "', refKeyAccount_GroupID = " & refKeyAccount_GroupID & ", txtAddress = '" & CleanString(txtAddress) & "',strEmail = '" & CleanString(txtEmail) & "',strTelephone = '" & CleanString(txtTel) & "', dtStamp = '" & dbDate() & "' WHERE strCustomerNo='" & CleanString(strCustomerNo) & "' "
    End Function

    '\\\\   USER

    Public Function sql_get_Users(ByVal strDomainLogin As String, ByVal strDomain As String) As String
      Return "SELECT mstUser.*, mstUserGroup.SystemStockControl, mstUserGroupSuperbowl.strUserGroup AS strGroupSB, mstUserGroupSuperbowl.SystemSuperbowl, mstUserGroupSuperbowl.intLevel, mstUserGroup.strUserGroup AS strGroupSC " & _
        " FROM mstUserGroupSuperbowl RIGHT OUTER JOIN mstUser ON mstUserGroupSuperbowl.UserGroupID = mstUser.refUserGroupSuperbowlID LEFT OUTER JOIN mstUserGroup ON mstUser.refUserGroupID = mstUserGroup.UserGroupID WHERE mstUser.strDomainLogin = '" & CleanString(strDomainLogin) & "' AND System" & strDomain & " = '" & strDomain & "'"
   End Function

   Public Function sql_get_Users(ByVal UserID As Integer) As String
      Return "SELECT mstUser.*, mstUserGroup.UserGroupID as UGSCID, mstUserGroup.SystemStockControl, mstUserGroupSuperbowl.UserGroupID as UGSBID, mstUserGroupSuperbowl.strUserGroup AS strGroupSB, mstUserGroupSuperbowl.SystemSuperbowl, mstUserGroupSuperbowl.intLevel, mstUserGroup.strUserGroup AS strGroupSC " & _
      " FROM mstUserGroupSuperbowl RIGHT OUTER JOIN mstUser ON mstUserGroupSuperbowl.UserGroupID = mstUser.refUserGroupSuperbowlID LEFT OUTER JOIN mstUserGroup ON mstUser.refUserGroupID = mstUserGroup.UserGroupID  WHERE UserID = " & UserID
   End Function

   Public Function sql_get_Users(ByVal strDomainLogin As String, ByVal UserID As Integer) As String
      Return "Select * From mstUser WHERE UserID <> " & UserID & " AND strDomainLogin = '" & CleanString(strDomainLogin) & "'"
   End Function

    '20150323 - Removed Manager ID from insert, a subsequent call to the update sets the manager ID
    Public Function sql_insert_Users(ByVal strUserName As String, ByVal strDomainLogin As String, ByVal strEmail As String, ByVal refUGSCID As Integer, ByVal refUGSBID As Integer, refUGFMID As Integer, refUGPMID As Integer, refUGSCDID As Integer, refUGFSID As Integer, refUGPOID As Integer, refUGAMID As Integer, refUGEMID As Integer, blnManager As Integer, blnManagerExecutive As Integer, refSDSUGID As Integer) As String
        Return "INSERT INTO mstUser (strUserName, strDomainLogin, strEmail, refUserGroupID, refUserGroupSuperbowlID, refUserGroupForecastID, refUserGroupPromotionID, refUserGroupScoreCardID, refUserGroupFreeStockID, refUserGroupPORID, refUserGroupSDSID, refManagerAlternativeID, refManagerExecutiveID, blnManager, blnManagerExecutive, strLastUser, dtStamp) VALUES ('" & CleanString(strUserName) & "', '" & CleanString(strDomainLogin) & "', '" & CleanString(strEmail) & "', " & refUGSCID & ", " & refUGSBID & ", " & refUGFMID & ", " & refUGPMID & ", " & refUGSCDID & "," & refUGFSID & "," & refUGPOID & "," & refSDSUGID & "," & refUGAMID & "," & refUGEMID & "," & blnManager & "," & blnManagerExecutive & ", '" & strUserName & "', '" & dbDate() & "')"
    End Function

    Public Function sql_update_Users(ByVal UserID As Integer, ByVal strUserName As String, ByVal strDomainLogin As String, ByVal strEmail As String, ByVal refUGSCID As Integer, ByVal refUGSBID As Integer, refUGFMID As Integer, refUGPMID As Integer, refUGSCDID As Integer, refUGFSID As Integer, refUGPOID As Integer, refUGMID As Integer, refUGAMID As Integer, refUGEMID As Integer, blnManager As Integer, blnManagerExecutive As Integer, refSDSUGID As Integer) As String
        Return "UPDATE mstUser SET strUserName = '" & CleanString(strUserName) & "', strDomainLogin = '" & CleanString(strDomainLogin) & "', strEmail = '" & CleanString(strEmail) & "', refUserGroupID = " & refUGSCID & ", refUserGroupSuperbowlID = " & refUGSBID & ", refUserGroupForecastID = " & refUGFMID & ", refUserGroupPromotionID = " & refUGPMID & ", refUserGroupScoreCardID = " & refUGSCDID & ", refUserGroupFreeStockID =" & refUGFSID & ", refUserGroupPORID =" & refUGPOID & ", refManagerID = " & refUGMID & ", refManagerAlternativeID = " & refUGAMID & ", refManagerExecutiveID = " & refUGEMID & ", dtStamp = '" & dbDate() & "', blnManager = '" & blnManager & "', blnManagerExecutive = '" & blnManagerExecutive & "', strLastUser = '" & strUserName & "', refUserGroupSDSID = '" & refSDSUGID & "' WHERE UserID = " & UserID   ' , LastActivityDate = " & sqlDate() & "
    End Function

    '20150323 - Only Updates the refManagerID
    Public Function sql_update_Users_refManagerID(ByVal UserID As Integer, ByVal refUGMID As Integer)
      Return "UPDATE mstUser refManagerID = " & refUGMID & " WHERE UserID = " & UserID
   End Function

   '////   SnellArea

   Public Shared Function sql_get_SnellArea() As String
      Return "SELECT SnellAreaCode, strSnellAreaName, strRegion, strBranch, strLastUser, dtStamp FROM mstSnellArea "
   End Function

   Public Shared Function sql_get_SnellArea(ByVal SnellAreaCode As String) As String
      Return sql_get_SnellArea() & " WHERE SnellAreaCode = '" & CleanString(SnellAreaCode) & "' "
   End Function

   Public Shared Function sql_get_SnellArea(ByVal strSnellAreaName As String, ByVal SnellAreaCode As String) As String
      Return sql_get_SnellArea() & " WHERE strSnellAreaName = '" & CleanString(strSnellAreaName) & "' AND SnellAreaCode <> '" & CleanString(SnellAreaCode) & "' "
   End Function

   Public Function sql_insert_SnellArea(ByVal strSnellAreaCode As String, ByVal strSnellAreaName As String, ByVal strRegion As String, ByVal strBranch As String, ByVal strUserName As String) As String
      Return "INSERT INTO mstSnellArea  (SnellAreaCode, strSnellAreaName, strRegion, strBranch, strLastUser, dtStamp)" & _
       " VALUES ('" & CleanString(strSnellAreaCode) & "', '" & CleanString(strSnellAreaName) & "','" & CleanString(strRegion) & "','" & CleanString(strBranch) & "', '" & strUserName & "','" & dbDate() & "')"
   End Function

   Public Function sql_update_SnellArea(ByVal SnellAreaCode As String, ByVal strSnellAreaName As String, ByVal strRegion As String, ByVal strBranch As String, ByVal strUserName As String) As String
      Return "UPDATE mstSnellArea SET SnellAreaCode = '" & CleanString(SnellAreaCode) & "', strSnellAreaName = '" & CleanString(strSnellAreaName) & "', strRegion = '" & CleanString(strRegion) & "', strBranch = '" & CleanString(strBranch) & "', strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "' WHERE SnellAreaCode = '" & SnellAreaCode & "'"
   End Function

   Public Function sql_update_SnellArea_dw(ByVal dwSnellAreaCode As String, ByVal strSnellArea As String) As String
      Return "UPDATE dwdCustomer SET strSnellArea = '" & CleanString(strSnellArea) & "' WHERE SnellArea = '" & CleanString(dwSnellAreaCode) & "' "
   End Function

   '////   RepManager

   Public Shared Function sql_get_RepManager() As String
      Return "SELECT mstRepManager.* FROM mstRepManager "
   End Function

   Public Shared Function sql_get_RepManager(ByVal RepManagerID As Double) As String
      Return sql_get_RepManager() & " WHERE RepManagerID = " & RepManagerID & " "
   End Function

   Public Shared Function sql_get_RepManager(ByVal strRepManager As String, ByVal RepManagerID As Double) As String
      Return sql_get_RepManager() & " WHERE strRepManager = '" & CleanString(strRepManager) & "' AND RepManagerID <> " & RepManagerID & " "
   End Function

    Public Function sql_insert_RepManager(ByVal strRepManager As String, ByVal strEmail As String, ByVal strUserName As String, ByVal strRegion As String) As String
        Return "INSERT INTO mstRepManager (strRepManager, strEmail, strRegion, strLastUser, dtStamp)" &
       " VALUES ('" & CleanString(strRepManager) & "', '" & CleanString(strEmail) & "','" & CleanString(strRegion) & "', '" & strUserName & "','" & dbDate() & "')"
    End Function

    Public Function sql_update_RepManager(ByVal RepManagerID As Double, ByVal strRepManager As String, ByVal strEmail As String, ByVal strUserName As String, ByVal strRegion As String) As String
        Return "UPDATE mstRepManager SET strRepManager = '" & CleanString(strRepManager) & "', strEmail = '" & CleanString(strEmail) & "',strRegion = '" & CleanString(strRegion) & "', strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "' WHERE RepManagerID = " & RepManagerID & " "
    End Function
    Public Function sql_insert_RepManager(ByVal strRepManager As String, ByVal strEmail As String, ByVal strUserName As String) As String
        Return "INSERT INTO mstRepManager (strRepManager, strEmail, strLastUser, dtStamp)" &
       " VALUES ('" & CleanString(strRepManager) & "', '" & CleanString(strEmail) & "', '" & strUserName & "','" & dbDate() & "')"
    End Function

    Public Function sql_update_RepManager(ByVal RepManagerID As Double, ByVal strRepManager As String, ByVal strEmail As String, ByVal strUserName As String) As String
        Dim nullString = "NULL"
        Return "UPDATE mstRepManager SET strRepManager = '" & CleanString(strRepManager) & "', strEmail = '" & CleanString(strEmail) & "',strLastUser = '" & strUserName & "',strRegion = " & nullString & ", dtStamp = '" & dbDate() & "' WHERE RepManagerID = " & RepManagerID & " "
    End Function

    '////   ZZ

    Public Shared Function sql_get_ZZ() As String
      Return "SELECT mstZZ.* FROM mstZZ "
   End Function

   Public Shared Function sql_get_ZZ(ByVal ZZID As Double) As String
      Return sql_get_ZZ() & " WHERE ZZID = " & ZZID & " "
   End Function

   Public Shared Function sql_get_ZZ(ByVal strZZ As String, ByVal ZZID As Double) As String
      Return sql_get_ZZ() & " WHERE strZZ = '" & CleanString(strZZ) & "' AND ZZID <> " & ZZID & " "
   End Function

   Public Function sql_insert_ZZ(ByVal strZZ As String, ByVal blnZZ As Integer, ByVal strUserName As String) As String
      Return "INSERT INTO mstZZ (strZZ, blnZZ, strLastUser, dtStamp)" & _
       " VALUES ('" & CleanString(strZZ) & "', " & blnZZ & ", '" & strUserName & "','" & dbDate() & "')"
   End Function

   Public Function sql_update_ZZ(ByVal ZZID As Double, ByVal strZZ As String, ByVal blnZZ As Integer, ByVal strUserName As String) As String
      Return "UPDATE mstZZ SET strZZ = '" & CleanString(strZZ) & "', blnZZ = " & blnZZ & ", strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "' WHERE ZZID = " & ZZID & " "

   End Function

   Public Function sql_update_ZZ_dw(ByVal dwZZID As String, ByVal strZZ As String) As String
      Return "UPDATE dwdCustomer SET strZZ = '" & CleanString(strZZ) & "' WHERE refZZID= '" & dwZZID & "' "
   End Function

   'Public Function sql_insert_Users_OLD_WITH_PASSWORD(ByVal strUserName As String, ByVal strDomainLogin As String, ByVal strPassword As String, ByVal intType As Integer) As String
   '	Return "INSERT INTO mstUser (strUserName, strDomainLogin, strPassword, refUserGroupID, strLastUser, dtStamp) VALUES ('" & CleanString(strUserName) & "', '" & CleanString(strDomainLogin) & "', '" & CleanString(strPassword) & "', " & intType & ", '" & strUserName & "', '" & dbDate() & "')"
   'End Function
   'Public Function sql_update_Users_OLD_WITH_PASSWORD(ByVal UserID As Integer, ByVal strUserName As String, ByVal strDomainLogin As String, ByVal strPassword As String, ByVal intType As Integer) As String
   '	Return "UPDATE mstUser SET strUserName = '" & CleanString(strUserName) & "', strDomainLogin = '" & CleanString(strDomainLogin) & "', strPassword = '" & CleanString(strPassword) & "', refUserGroupID = " & intType & ", dtStamp = '" & dbDate() & "', strLastUser = '" & strUserName & "' WHERE UserID = " & UserID		' , LastActivityDate = " & sqlDate() & "
   'End Function

   '//////////////////////////////////////////////////////////////////
   '// STOCKCONTROL SQL FUNCTIONS
   '//////////////////////////////////////////////////////////////////


   '\\\\	USER GROUP

   Public Function sql_get_UserType(ByVal strUser As String) As String
      Return "Select * From mstUser WHERE strUserName = '" & CleanString(strUser) & "'"
   End Function

   '\\\\	Period

   Public Function sql_get_Period(ByVal strProdCode As String, ByVal strStartDate As String, ByVal strEndDate As String) As String
      Return "SELECT mstPeriod.strPeriod, mstPeriod.intCalendarYear, mstPeriod.intCalendarMonth, (SELECT Count(mstProdCode) FROM stkProductPeriod WHERE mstProdCode = '" & strProdCode & "' AND mstPeriod = [mstPeriod].[strPeriod]) AS intPP " & _
       "FROM mstPeriod WHERE mstPeriod.strPeriod>='" & strStartDate & "' And mstPeriod.strPeriod<='" & strEndDate & "'"
   End Function

   '\\\\	Product Period

   Public Function sql_get_PreviousPP(ByVal strProdCode As String, ByVal strStartDate As String, ByVal strEndDate As String) As String
      Return "SELECT TOP 15 mstPeriod.strPeriod, Sum(dwfSales.lngUnitsTotal * dwdProduct.dblFlat) AS dblActual " & _
      "FROM mstPeriod INNER JOIN dwfSales ON mstPeriod.strPeriod = dwfSales.refPeriod INNER JOIN dwdProduct ON dwfSales.strProdCode = dwdProduct.strProdCode " & _
      "WHERE Left(dwdProduct.strProdCode, 5) = '" & strProdCode & "' " & _
      "GROUP BY mstPeriod.strPeriod, mstPeriod.intCalendarMonth " & _
      "HAVING mstPeriod.strPeriod>='" & strStartDate & "' And mstPeriod.strPeriod<='" & strEndDate & "' "
   End Function

   Public Function sql_get_PreviousPP_9L(ByVal refBrandID As Integer, ByVal strStartDate As String, ByVal strEndDate As String) As String
      Return "SELECT TOP 15 mstPeriod.strPeriod, Sum(dwfSales.dbl9lTotal) AS dblActual " & _
      "FROM (mstPeriod INNER JOIN dwfSales ON mstPeriod.strPeriod = dwfSales.refPeriod) INNER JOIN mstProduct ON dwfSales.strProdCode = mstProduct.strProdCode " & _
      "WHERE mstProduct.refBrandID=" & refBrandID & _
      " GROUP BY mstPeriod.strPeriod, mstPeriod.intCalendarMonth " & _
      "HAVING mstPeriod.strPeriod>='" & strStartDate & "' And mstPeriod.strPeriod<='" & strEndDate & "' "
   End Function

   Public Function sql_get_PP(ByVal strProdCode As String, ByVal strStartDate As String, ByVal strEndDate As String) As String
      Return "SELECT stkProductPeriod.mstPeriod, stkProductPeriod.dblOpening, stkProductPeriod.dblQF, dblBMA, stkProductPeriod.dblActual, stkProductPeriod.dblOrders, stkProductPeriod.dblReceipts, stkProductPeriod.dblExports, stkProductPeriod.dblDamages, stkProductPeriod.dblRepack, stkProductPeriod.dblClosing, stkProductPeriod.dblMonths, stkProductPeriod.strLastUser, stkProductPeriod.dtStamp, stkProductPeriod.strLastUserSM, stkProductPeriod.dtStampSM, stkProductPeriod.mstProdCode " & _
       " FROM stkProductPeriod INNER JOIN mstPeriod ON stkProductPeriod.mstPeriod = mstPeriod.strPeriod " & _
       " WHERE (mstPeriod.strPeriod Between '" & strStartDate & "' And '" & strEndDate & "') AND (stkProductPeriod.mstProdCode='" & strProdCode & "')  ORDER BY mstPeriod.strPeriod"
   End Function

   Public Function sql_insert_PP(ByVal strProdCode As String, ByVal strPeriod As String, ByVal dblOpening As Double, ByVal dblQF As Double, ByVal dblBMA As Double, ByVal dblActual As Double, ByVal dblOrders As Double, ByVal dblReceipts As Double, ByVal dblExports As Double, ByVal dblDamages As Double, ByVal dblRepack As Double, ByVal dblClosing As Double, ByVal dblMonths As Double, ByVal dblOpeningImports As Double)
      Return "INSERT INTO stkProductPeriod ( mstProdCode, mstPeriod, dblOpening, dblQF, dblBMA, dblActual, dblOrders, dblReceipts, dblExports, dblDamages, dblRepack, dblClosing, dblMonths, strLastUser, dtStamp, strLastUserSM, dtStampSM, dblOpeningImports)" & _
      " VALUES ('" & strProdCode & "', '" & strPeriod & "', " & dblOpening & ", " & dblQF & ",  " & dblBMA & ", " & dblActual & ", " & dblOrders & ", " & dblReceipts & ", " & dblExports & ", " & dblDamages & ", " & dblRepack & ", " & dblClosing & ", " & dblMonths & ", 'new', '" & dbDate() & "', 'new', '" & dbDate() & "', " & dblOpeningImports & ")"
   End Function

   Public Function sql_Update_PP(ByVal strProdCode As String, ByVal strPeriod As String, ByVal strColumn As String, ByVal value As Double)
      Return "UPDATE stkProductPeriod SET " & strColumn & " = " & value & _
      " WHERE (mstPeriod = '" & strPeriod & "') AND (mstProdCode='" & strProdCode & "')"
   End Function

   Public Function sql_Update_PP(ByVal strProdCode As String, ByVal strPeriod As String, ByVal dblOpening As Double, ByVal dblBMA As Double, ByVal dblOrders As Double, ByVal dblClosing As Double, ByVal dblMonths As Double, ByVal strUserName As String, Optional ByVal strEdit As String = "")
      If strEdit.Length = 0 Then
         If WebConfigurationManager.AppSettings("UGROUP").ToLower.Contains("brand") Then
            strEdit = ", dblBMA = " & dblBMA & ", strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "'"
         End If
         If WebConfigurationManager.AppSettings("UGROUP").ToLower.Contains("shipping") Or WebConfigurationManager.AppSettings("UGROUP").ToLower.Contains("order") Then
            strEdit = ", dblOrders = " & dblOrders & ", strLastUserSM = '" & strUserName & "', dtStampSM = '" & dbDate() & "'"
         End If
         If WebConfigurationManager.AppSettings("UGROUP").ToLower.Contains("admin") Then
            strEdit = ", dblBMA = " & dblBMA & ", strLastUser = '" & strUserName & "', dtStamp = '" & dbDate() & "', dblOrders = " & dblOrders & ", strLastUserSM = '" & strUserName & "', dtStampSM = '" & dbDate() & "'"
         End If
      End If

      Return "UPDATE stkProductPeriod SET dblOpening = " & dblOpening & ",  dblClosing = " & dblClosing & ", dblMonths = " & dblMonths & " " & strEdit & _
      " WHERE (mstPeriod = '" & strPeriod & "') AND (mstProdCode='" & strProdCode & "')"
   End Function

   Public Function sql_Update_PP_Import(ByVal strProdCode As String, ByVal strPeriod As String, ByVal strColumn As String, ByVal value As Double)
      Return "UPDATE stkProductPeriod SET " & strColumn & " = (" & strColumn & " + " & value & ") " & _
      " WHERE (mstPeriod = '" & strPeriod & "') AND (mstProdCode='" & strProdCode & "')"
   End Function

   Public Function sql_delete_PP(ByVal strProdCode As String, ByVal strStartDate As String, ByVal strEndDate As String) As String
      Return "DELETE FROM stkProductPeriod WHERE (mstPeriod Between '" & strStartDate & "' And '" & strEndDate & "') AND (mstProdCode='" & strProdCode & "')"
   End Function

   Public Function sql_get_9L(ByVal intBrandID As Integer, ByVal strStartDate As String, ByVal strEndDate As String) As String
      Return "SELECT stk9L.mstPeriod, Sum(stk9L.dblOpening) AS dblOpening, Sum(stk9L.dblQF) AS dblQF, Sum(stk9L.dblBMA) AS dblBMA, Sum(stk9L.dblActual) AS dblActual, Sum(stk9L.dblOrders) AS dblOrders, Sum(stk9L.dblReceipts) AS dblReceipts, Sum(stk9L.dblExports) AS dblExports, Sum(stk9L.dblDamages) AS dblDamages, Sum(stk9L.dblRepack) AS dblRepack, Sum(stk9L.dblClosing) AS dblClosing, AVG(stk9L.dblMonths) AS dblMonths, ' ' as strLastUser, ' ' as dtStamp, ' ' as strLastUserSM, ' ' as dtStampSM, stk9L.mstBrandID " & _
       " FROM stk9L GROUP BY stk9L.mstPeriod, stk9L.mstBrandID " & _
       " HAVING (stk9L.mstBrandID = " & intBrandID & ") AND (stk9L.mstPeriod Between '" & strStartDate & "' AND '" & strEndDate & "') ORDER BY stk9L.mstPeriod "
   End Function

   Public Function sql_insert_9L(ByVal intBrandID As Integer, ByVal strStartDate As String, ByVal strEndDate As String) As String
      Return "INSERT INTO stk9L (mstBrandID, mstPeriod, dblOpening, dblQF, dblBMA, dblActual, dblOrders, dblReceipts, dblExports, dblDamages, dblRepack, dblClosing, dblMonths) " & _
      " SELECT mstProduct.refBrandID, stkProductPeriod.mstPeriod, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblOpening) / 9000.0 AS dblOpening, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblQF) / 9000.0 AS dblQF, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblBMA) / 9000.0 AS dblBMA, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblActual) / 9000.0 AS dblActual, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblOrders) / 9000.0 AS dblOrders,SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblReceipts) / 9000.0 AS dblReceipts, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblExports) / 9000.0 AS dblDamages, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblDamages) / 9000.0 AS dblDamages, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblRepack) / 9000.0 AS dblRepack, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblClosing) / 9000.0 AS dblClosing, AVG(stkProductPeriod.dblMonths) AS dblMonths " & _
      " FROM (mstPeriod INNER JOIN stkProductPeriod ON mstPeriod.strPeriod = stkProductPeriod.mstPeriod) INNER JOIN mstProduct ON stkProductPeriod.mstProdCode = left(mstProduct.strProdCode,5) " & _
      " WHERE strProdCode LIKE '%F%' " & _
      " GROUP BY stkProductPeriod.mstPeriod, mstProduct.refBrandID, mstPeriod.strPeriod, stkProductPeriod.mstProdCode, mstProduct.intPack, mstProduct.intSize " & _
      " HAVING (mstProduct.refBrandID = " & intBrandID & ") AND (mstPeriod Between '" & strStartDate & "' AND '" & strEndDate & "') ORDER BY mstPeriod.strPeriod"
   End Function

   Public Function sql_get_principle_9L(ByVal intPrincipleID As Integer, ByVal strStartDate As String, ByVal strEndDate As String) As String
      Return "SELECT Sum(stk9L.dblOpening) AS dblOpening, Sum(stk9L.dblQF) AS dblQF, Sum(stk9L.dblBMA) AS dblBMA, Sum(stk9L.dblActual) AS dblActual, Sum(stk9L.dblOrders) AS dblOrders, Sum(stk9L.dblReceipts) AS dblReceipts, SUM(stk9L.dblExports) AS dblExports, Sum(stk9L.dblDamages) AS dblDamages, Sum(stk9L.dblRepack) AS dblRepack, Sum(stk9L.dblClosing) AS dblClosing, Sum(stk9L.dblMonths) AS dblMonths, ' ' as strLastUser, ' ' as dtStamp, ' ' as strLastUserSM, ' ' as dtStampSM, stk9L.mstBrandID " & _
       " FROM stk9L WHERE (stk9L.mstPeriod Between '" & strStartDate & "' AND '" & strEndDate & "') " & _
       " GROUP BY stk9L.mstBrandID HAVING (stk9L.mstBrandID = " & intPrincipleID & ") "
   End Function

   Public Function sql_insert_principle_9L(ByVal intBrandID As Integer, ByVal strStartDate As String, ByVal strEndDate As String) As String
      Return "INSERT INTO stk9L (mstBrandID, mstPeriod, dblOpening, dblQF, dblBMA, dblActual, dblOrders, dblReceipts, dblExports, dblDamages, dblRepack, dblClosing, dblMonths) " & _
      " SELECT mstBrand.BrandID, stkProductPeriod.mstPeriod, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblOpening) / 9000.0 AS dblOpening, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblQF) / 9000.0 AS dblQF, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblBMA) / 9000.0 AS dblBMA, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblActual) / 9000.0 AS dblActual, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblOrders) / 9000.0 AS dblOrders, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblReceipts) / 9000.0 AS dblReceipts, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblExports) / 9000.0 AS dblExports, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblDamages) / 9000.0 AS dblDamages,  SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblRepack) / 9000.0 AS dblRepack, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblClosing) / 9000.0 AS dblClosing, AVG(stkProductPeriod.dblMonths) AS dblMonths " & _
      " FROM mstBrand INNER JOIN  mstProduct ON mstBrand.BrandID = mstProduct.refBrandID INNER JOIN stkProductPeriod ON LEFT(mstProduct.strProdCode, 5) = stkProductPeriod.mstProdCode " & _
      " WHERE (mstProduct.strProdCode LIKE '%F%')" & _
      " GROUP BY stkProductPeriod.mstPeriod, mstBrand.strBrand, mstProduct.intSize, mstProduct.intPack, mstBrand.refPrincipalID " & _
      " HAVING (mstBrand.BrandID = " & intBrandID & ") AND (mstPeriod Between '" & strStartDate & "' AND '" & strEndDate & "') ORDER BY mstPeriod"
   End Function

   '\\\\ SKU REPORT

   Public Function sql_get_brand_all_bma(ByVal refBrandID As Integer, ByVal strStartDate As String, ByVal strEndDate As String) As String
      Return "SELECT mstProduct.strLongProdDesc, mstProduct.intPack, stkProductPeriod.mstPeriod, stkProductPeriod.dblBMA, stkProductPeriod.strLastUser, stkProductPeriod.dtStamp, mstProduct.refBrandID, stkProductPeriod.mstProdCode" & _
       " FROM stkProductPeriod INNER JOIN mstProduct ON stkProductPeriod.mstProdCode = LEFT(mstProduct.strProdCode, 5) " & _
       " WHERE (stkProductPeriod.mstPeriod BETWEEN '" & strStartDate & "' AND '" & strEndDate & "') AND (mstProduct.refBrandID = " & refBrandID & ") AND (mstProduct.strProdCode LIKE '%F%') " & _
       "ORDER BY mstProduct.strLongProdDesc, stkProductPeriod.mstPeriod"
   End Function

   Public Function sql_get_product_bma(ByVal strProdCode As String, ByVal strEndDate As String) As String
      Return "SELECT TOP 1 stkConfirm.dbl1, stkConfirm.dbl2, stkConfirm.dbl3, stkConfirm.dbl4, stkConfirm.dbl5, stkConfirm.dbl6, stkConfirm.dbl7, stkConfirm.dbl8, stkConfirm.dbl9, stkConfirm.dbl10, stkConfirm.dbl11, stkConfirm.dbl12, stkConfirm.strLastUser, stkConfirm.dtStamp, stkConfirm.mstPeriod, stkConfirm.mstProdCode " & _
       "FROM stkConfirm INNER JOIN mstProduct ON stkConfirm.mstProdCode = LEFT(mstProduct.strProdCode, 5) " & _
       "WHERE (mstProduct.strProdCode LIKE '%F%') " & _
       "GROUP BY stkConfirm.dbl1, stkConfirm.dbl2, stkConfirm.dbl3, stkConfirm.dbl4, stkConfirm.dbl5, stkConfirm.dbl6, stkConfirm.dbl8, stkConfirm.dbl7, stkConfirm.dbl9, stkConfirm.dbl10, stkConfirm.dbl11, stkConfirm.dbl12, stkConfirm.strLastUser, stkConfirm.dtStamp, stkConfirm.mstProdCode, stkConfirm.mstPeriod " & _
       "HAVING (stkConfirm.mstPeriod <= '" & strEndDate & "') AND (stkConfirm.mstProdCode = '" & strProdCode & "') " & _
       "ORDER BY stkConfirm.mstPeriod DESC"
   End Function

   Public Function sql_get_brand_product(Optional ByVal refBrandID As Integer = 0, Optional ByVal refPrincipalID As Integer = 0)
      Dim strWhere As String = ""
      If refBrandID > 0 Then strWhere &= " AND (mstBrand.BrandID = " & refBrandID & ")"
      If refPrincipalID > 0 Then strWhere &= " AND (mstBrand.refPrincipalID = " & refPrincipalID & ")"

      Return "SELECT mstBrand.strBrand, mstPrincipal.strPrincipal, mstBrandManager.strBrandManager, mstBrandManager.strEmail, mstProduct.strLongProdDesc, mstProduct.intPack, 0 AS dblMC1, 0 AS dblMC2, 0 AS dblMC3, 0 AS dblMC4, 0 AS dblMC5, 0 AS dblMC6, 0 AS dblMC7, 0 AS dblMC8, 0 AS dblMC9, 0 AS dblMC10, 0 AS dblMC11, 0 AS dblMC12 " & _
       "FROM mstProduct INNER JOIN ((mstBrand INNER JOIN mstPrincipal ON mstBrand.refPrincipalID = mstPrincipal.PrincipalID) INNER JOIN mstBrandManager ON mstBrand.refBrandManagerID = mstBrandManager.BrandManagerID) ON mstProduct.refBrandID = mstBrand.BrandID " & _
       "WHERE (mstProduct.strProdCode Like '%F%') AND (mstProduct.blnActive=1) AND (mstBrand.blnActive=1) " & strWhere

   End Function

   '\\\\ Confirm

   Public Function sql_insert_confirmBM(ByVal strProdCode As String, ByVal strPeriod As String, ByVal dbl1 As Double, ByVal dbl2 As Double, ByVal dbl3 As Double, ByVal dbl4 As Double, ByVal dbl5 As Double, ByVal dbl6 As Double, ByVal dbl7 As Double, ByVal dbl8 As Double, ByVal dbl9 As Double, ByVal dbl10 As Double, ByVal dbl11 As Double, ByVal dbl12 As Double, ByVal strUserName As String) As String
      Return "INSERT INTO stkConfirm ( mstProdCode, mstPeriod, dbl1, dbl2, dbl3, dbl4, dbl5, dbl6, dbl7, dbl8, dbl9, dbl10, dbl11, dbl12, strLastUser, dtStamp )" & _
      " VALUES('" & strProdCode & "', '" & strPeriod & "', " & dbl1 & ", " & dbl2 & ", " & dbl3 & ", " & dbl4 & ", " & dbl5 & ", " & dbl6 & ", " & dbl7 & ", " & dbl8 & ", " & dbl9 & ", " & dbl10 & ", " & dbl11 & ", " & dbl12 & ",  '" & strUserName & "', '" & dbDate() & "')"
   End Function

   Public Function sql_insert_confirmSM(ByVal strProdCode As String, ByVal strPeriod As String, ByVal dbl1 As Double, ByVal dbl2 As Double, ByVal dbl3 As Double, ByVal dbl4 As Double, ByVal dbl5 As Double, ByVal dbl6 As Double, ByVal dbl7 As Double, ByVal dbl8 As Double, ByVal dbl9 As Double, ByVal dbl10 As Double, ByVal dbl11 As Double, ByVal dbl12 As Double, ByVal strUserName As String) As String
      Return "INSERT INTO stkConfirmSM( mstProdCode, mstPeriod, dbl1, dbl2, dbl3, dbl4, dbl5, dbl6, dbl7, dbl8, dbl9, dbl10, dbl11, dbl12, strLastUser, dtStamp )" & _
       " VALUES('" & strProdCode & "', '" & strPeriod & "', " & dbl1 & ", " & dbl2 & ", " & dbl3 & ", " & dbl4 & ", " & dbl5 & ", " & dbl6 & ", " & dbl7 & ", " & dbl8 & ", " & dbl9 & ", " & dbl10 & ", " & dbl11 & ", " & dbl12 & ",  '" & strUserName & "', '" & dbDate() & "')"
   End Function

   '\\\\ Import Log

   Public Function sql_get_ImportLog() As String
      Return "SELECT * From stkImportLog ORDER BY mstPeriod DESC"
   End Function

   Public Function sql_Insert_ImportLog(ByVal pPeriod As String, ByVal strUserName As String) As String
      Return "INSERT INTO stkImportLog (mstPeriod, blnOpening, blnForecast, blnReceipts, blnDamages, strLastUser, dtStamp) " & _
      "VALUES ('" & pPeriod & "', 0, 0, 0, 0, '" & strUserName & "', '" & dbDate() & "') "
   End Function

   Public Function sql_Update_ImportLog(ByVal pPeriod As String, ByVal pCol As String, ByVal strUserName As String) As String
      Return "UPDATE stkImportLog SET " & pCol & " = 1, strLastUser='" & strUserName & "', dtStamp='" & dbDate() & "' WHERE mstPeriod = '" & pPeriod & "' "
   End Function


   '\\\\ C14 IMPORT FROM SB_DAILY APP
   Public Shared Function sql_insert_stgCognos14(ByVal doctype As String, ByVal docno As Double, ByVal cusno As Double, ByVal cusname As String, ByVal rep As Double, ByVal period As String, ByVal movement As String, ByVal prodcode As String, ByVal proddesc As String, ByVal packs As Double, ByVal packsize As Double, ByVal units As Double, ByVal ninelce As Double, ByVal ordernum As String, ByVal nett As Double, ByVal measure As String, ByVal gsv As Double, ByVal dblNettEx As Double)
      Return "INSERT INTO stgCognos14 (doctype, docno, cusno, cusname, rep, period, movement, prodcode, proddesc, packs, packsize, units, ninelce, ordernum, nett, measure, gsv, dblNettEx)" & _
        "VALUES ('" & CleanString(doctype) & "', '" & docno & "', '" & cusno & "', '" & CleanString(cusname) & "', '" & rep & "', '" & _
        CleanString(period) & "', '" & CleanString(movement) & "', '" & CleanString(prodcode) & "', '" & CleanString(proddesc) & "', " & packs & ", " & packsize & ", " & _
        units & ", " & ninelce & ", '" & CleanString(ordernum) & "', " & nett & ", '" & CleanString(measure) & "', " & gsv & ", " & dblNettEx & ")"
   End Function

End Class