Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
Imports System.Globalization

Public Class sqlDB_Superbowl
  'Inherits System.Web.UI.Page
#If DEBUG Then
  Public Shared conn As New SqlConnection(My.Settings.SQLConnectionDebug)
#Else
  Public Shared conn As New SqlConnection(My.Settings.SQLConnectionRelease)
#End If
  Public Shared UNAME As String = My.Settings.UNAME

  Sub sqlDB()
    ' ci = CultureInfo.CreateSpecificCulture(WebConfigurationManager.AppSettings("culture"))
    'UNAME = 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 String) As String
    Dim strClean As String
    'strClean = strUnClean.Replace(Chr(13), "'r")
    'strClean = strUnClean.Replace("'", "''")
    strClean = Nz(strUnClean.Replace(Chr(34), "'"), "")
    strClean = strClean.Replace(Chr(39), "''").Trim
    Return strClean
  End Function

  Public Shared Function doQueryDS(ByVal strSQL As String) As DataSet
    Dim adp As New SqlDataAdapter(strSQL, conn)
    Dim ds As New DataSet
    adp.Fill(ds)

    Return ds
  End Function

  Public Shared Function GetRow(ByVal strSQL As String) As DataRow
    Dim adp As New SqlDataAdapter(strSQL, conn)
    Dim ds As New DataSet
    Try
      adp.Fill(ds)
    Catch ex As Exception
    End Try
    Return ds.Tables(0).Rows(0)
  End Function

  Public Shared Function CountRows(ByVal strSQL As String) As Integer
    Dim adp As New SqlDataAdapter(strSQL, conn)
    Dim ds As New DataSet
    adp.Fill(ds)

    Return ds.Tables(0).Rows.Count
  End Function

  Public Shared Function LastID(ByVal id As String, ByVal strTable As String) As Integer
    Dim adp As New SqlDataAdapter("SELECT MAX(" & id & ") FROM " & strTable, conn)
    Dim ds As New DataSet
    Try
      adp.Fill(ds)
    Catch ex As Exception
    End Try
    Return ds.Tables(0).Rows(0).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 chrComma As Char = ""
    Dim s As String
    'Dim del As String = ""
    s = ""
    For i As Integer = 0 To dt.Columns.Count - 1
      'If dt.Columns(i).DataType.FullName = "System.String" Then del = "'" Else del = ""
      s &= chrComma & " " & dt.Columns(i).ColumnName & " = '" & dt.Rows(0).Item(i) & "'"
      chrComma = ","
    Next

    Return s
  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

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

  '//// CUSTOMER

  Public Shared Function sql_get_Customer(ByVal strCustomerNo As String) As String
    Return "Select mstCustomer.* , mstRep.refSegmentID, mstSegment.strSegment, mstSnellsupergroup.strSnellsupergroupName 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 WHERE strCustomerNo = '" & CleanString(strCustomerNo) & "'"
  End Function

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

  Public Shared 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

  Public Shared 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, mstBranch.strRegionOrder " & _
    "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.refChannelID 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 Shared 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 refChannelID As Integer, ByVal blnActive As Integer, ByVal blnPromotionalAccounts As Integer) As String
      'v1.26 - 20130903 - Changed Customer/cognog09 import mstCustomer.blnActive = !$blnActive
      If blnActive = 0 Then
         blnActive = 1
      Else
         blnActive = 0
      End If

      Return "INSERT INTO mstCustomer ( strCustomerNo, strCustomerName, strSnellChannel, strSnellArea, strSnellcwsireg, strSnellrepno, strSnellsupergroup, strSnellgrading, refRepID, refRepBBID, refRepDlxID, refRepLuxID, refBranchID, refKeyAccount_GroupID, refChannelID, blnActive, blnPromotionalAccounts, strLastUser, dtStamp ) " & _
      " 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 & "," & refChannelID & "," & blnActive & "," & blnPromotionalAccounts & ", '" & UNAME & "','" & dbDate() & "')"
   End Function

  Public Shared 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 refChannelID As Integer, ByVal blnActive As Integer, ByVal blnPromotionalAccounts As Integer) 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 & ", refChannelID = " & refChannelID & ", blnActive = " & blnActive & _
    ", blnPromotionalAccounts = " & blnPromotionalAccounts & ", strLastUser = '" & UNAME & "', dtStamp = '" & dbDate() & "' WHERE strCustomerNo='" & CleanString(strCustomerNo) & "' "
  End Function

  Public Shared 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, blnPromotionalAccounts, blnActive, strCode, strBranch, strSalesOffice, strRegion, strSAPCode, strRegionCode, strRegionDesc, strRegionAccpacCode, strRegionDesc2, strRegionOrder ) " & _
   sql_get_Customer_dw(strCustomerNo)
  End Function

  Public Shared Function sql_update_Customer_dw() As String
      Return "UPDATE dwdCustomer " & _
      "SET strCode = mstBranch.strCode, strBranch = mstBranch.strBranch, strSalesOffice = mstBranch.strSalesOffice, strRegion = mstBranch.strRegion, strSAPCode = mstBranch.strSAPCode, " & _
      "strRegionCode = mstBranch.strRegionCode, strRegionDesc = mstBranch.strRegionDesc, strRegionAccpacCode = mstBranch.strRegionAccpacCode, " & _
      "strRegionDesc2 = mstBranch.strRegionDesc2, strRegionOrder = mstBranch.strRegionOrder, strCustomerName = mstCustomer.strCustomerName, strSnellChannel = mstCustomer.strSnellChannel, strSnellArea = mstCustomer.strSnellArea, " & _
      "strSnellcwsireg = mstCustomer.strSnellcwsireg, strSnellrepno = mstCustomer.strSnellrepno, strSnellsupergroup = mstCustomer.strSnellsupergroup, " & _
      "strSnellsupergroupName = mstSnellsupergroup.strSnellsupergroup, strSnellgrading = mstCustomer.strSnellgrading, refRepID = mstCustomer.refRepID, " & _
      "refRepBBID = mstCustomer.refRepBBID, refRepDlxID = mstCustomer.refRepDlxID, refRepLuxID = mstCustomer.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, blnPromotionalAccounts = mstCustomer.blnPromotionalAccounts, " & _
      "blnActive = mstCustomer.blnActive " & _
      "FROM mstBranch RIGHT OUTER JOIN mstType2 RIGHT OUTER JOIN mstRep AS mstRep_3 RIGHT OUTER JOIN mstRep AS mstRep_4 RIGHT OUTER JOIN mstChannel LEFT OUTER JOIN mstSegment ON mstChannel.refSegmentID = mstSegment.SegmentID RIGHT OUTER JOIN dwdCustomer INNER JOIN mstCustomer ON dwdCustomer.strCustomerNo = mstCustomer.strCustomerNo ON mstChannel.ChannelID = mstCustomer.refChannelID LEFT OUTER JOIN mstKeyAccount_Group LEFT OUTER JOIN mstKeyAccount ON mstKeyAccount_Group.refKeyAccountID = mstKeyAccount.KeyAccountID ON mstCustomer.refKeyAccount_GroupID = mstKeyAccount_Group.KeyAccount_GroupID ON mstRep_4.RepID = mstCustomer.refRepLuxID ON mstRep_3.RepID = mstCustomer.refRepDlxID LEFT OUTER JOIN mstRep AS mstRep_2 ON mstCustomer.refRepBBID = mstRep_2.RepID LEFT OUTER JOIN mstRep ON mstCustomer.refRepID = mstRep.RepID ON mstType2.Type2ID = mstCustomer.refType2ID LEFT OUTER JOIN mstType1 ON mstCustomer.refType1ID = mstType1.Type1ID LEFT OUTER JOIN mstSnellsupergroup ON mstCustomer.strSnellsupergroup = mstSnellsupergroup.strSnellsupergroup ON mstBranch.BranchID = mstCustomer.refBranchID "
  End Function

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

  Public Shared 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

  '\\\\	Product

  Public Shared 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

  Public Shared Function sql_get_Product_6(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 strProdCode = '" & CleanString(strProdCode) & "'  ORDER BY mstProduct.strProdCode"
  End Function

  Public Shared 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 Shared 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, mstCompany.strCompany, 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

  Public Shared 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 strCategory, ByVal blnActive, ByVal dblFlat, ByVal cwsicat, ByVal strCategory2, ByVal strPrincipalMaterialRefNum, ByVal strPrincipalProdDesc, ByVal strBottleBarcode, ByVal strCaseBarcode, ByVal strGiftboxBarcode, ByVal intDisplayOrder, ByVal strBrandVarietal) As String
    Return "INSERT INTO mstProduct ( strProdCode, refBrandID, strProductDesc, intPack, intSize, dblPrice, strLongProdDesc, strType, strType2, intShortCode, strPriceSeg, strCategory, blnActive, dblFlat, cwsicat,  strCategory2, strPrincipalMaterialRefNum, strPrincipalProdDesc, strBottleBarcode, strCaseBarcode, strGiftboxBarcode, intDisplayOrder, strBrandVarietal, strLastUser, dtStamp )" & _
     "VALUES ('" & CleanString(strProdCode) & "'," & refBrandID & ",'" & CleanString(strProductDesc) & "'," & intPack & "," & intSize & "," & dblPrice & ",'" & CleanString(strLongProdDesc) & "','" & CleanString(strType) & "','" & CleanString(strType2) & "'," & intShortCode & ",'" & CleanString(strPriceSeg) & "','" & CleanString(strCategory) & "',1," & dblFlat & ",'" & CleanString(cwsicat) & "','" & CleanString(strCategory2) & "','" & CleanString(strPrincipalMaterialRefNum) & "','" & CleanString(strPrincipalProdDesc) & "','" & CleanString(strBottleBarcode) & "','" & CleanString(strCaseBarcode) & "','" & CleanString(strGiftboxBarcode) & "'," & intDisplayOrder & ",'" & CleanString(strBrandVarietal) & "', '" & UNAME & "','" & dbDate() & "')"
  End Function

  Public Shared 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 strCategory, ByVal blnActive, ByVal dblFlat, ByVal cwsicat, ByVal strCategory2, ByVal strPrincipalMaterialRefNum, ByVal strPrincipalProdDesc, ByVal strBottleBarcode, ByVal strCaseBarcode, ByVal strGiftboxBarcode, ByVal intDisplayOrder, ByVal strBrandVarietal) 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) & _
    "', strCategory = '" & CleanString(strCategory) & "', blnActive = " & blnActive & ", dblFlat = " & dblFlat & ", cwsicat = '" & CleanString(cwsicat) & _
    "',  strCategory2 = '" & CleanString(strCategory2) & "',  strPrincipalMaterialRefNum = '" & CleanString(strPrincipalMaterialRefNum) & _
    "', strPrincipalProdDesc = '" & CleanString(strPrincipalProdDesc) & "', strBottleBarcode = '" & CleanString(strBottleBarcode) & "', strCaseBarcode = '" & CleanString(strCaseBarcode) & _
    "', strGiftboxBarcode = '" & CleanString(strGiftboxBarcode) & "',   intDisplayOrder = " & intDisplayOrder & ", strBrandVarietal = '" & CleanString(strBrandVarietal) & _
    "', strLastUser = '" & UNAME & "', dtStamp = '" & dbDate() & "' WHERE strProdCode='" & CleanString(strProdCode) & "' "

  End Function

  Public Shared 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, strCompany, strBrand, blnBrandActive, strBottleBarcode, strCaseBarcode, strGiftboxBarcode, intDisplayOrder, strBrandVarietal) " & _
     sql_get_Product_dw(strProdCode)
  End Function

  Public Shared 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

  '////   BRAND

  Public Shared Function sql_get_Brand(ByVal strBrand As String) As String
    Return "Select * From mstBrand WHERE strBrand = '" & strBrand & "'"
  End Function

  Public Shared Function sql_get_Brand(ByVal BrandID As Integer) As String
    Return "Select * From mstBrand WHERE BrandID = " & BrandID
  End Function

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

  Public Shared 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 Integer, ByVal blnActive As Integer) As String
    Return "INSERT INTO mstBrand (refPrincipalID, refCompanyID, refBrandManagerID, refBrandManager2ID, strAccpacCode, strBrand, strGroupCode, strGroupAccounts, brand2, brandrebate, brandToolkit, intLeadMonths, intMonthsCover, dblVariance, blnActive, strLastUser, dtStamp)" & _
     " VALUES (" & refPrincipalID & "," & refCompanyID & "," & refBrandManagerID & "," & refBrandManager2ID & ",'" & CleanString(strAccpacCode) & "','" & CleanString(strBrand) & "','" & CleanString(strGroupCode) & "','" & CleanString(strGroupAccounts) & "','" & CleanString(brand2) & "','" & CleanString(brandrebate) & "','" & CleanString(brandToolkit) & "'," & intLeadMonths & ",0 ," & dblVariance & ", 1, '" & UNAME & "','" & dbDate() & "')"
  End Function

  Public Shared 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 Integer, ByVal blnActive As Integer) As String
    Return "UPDATE mstBrand SET refPrincipalID = " & refPrincipalID & ", refCompanyID = " & refCompanyID & ", refBrandManagerID = " & _
     refBrandManagerID & ", refBrandManager2ID = " & refBrandManager2ID & ", 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 = '" & _
     UNAME & "', dtStamp = '" & dbDate() & "' WHERE BrandID = " & BrandID

  End Function

  Public Shared Function sql_update_Brand_dw(ByVal dwBrand As String, ByVal strBrand As String, ByVal blnActive As Integer) As String
    Return "UPDATE dwdProduct SET strBrand = '" & CleanString(strBrand) & "', blnBrandActive = " & blnActive & "  WHERE strBrand = '" & dwBrand & "' "
  End Function

  '////   Segment

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

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

  Public Shared 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 Shared Function sql_insert_Segment(ByVal strSegment As String) As String
    Return "INSERT INTO mstSegment (strSegment, strLastUser, dtStamp)" & _
     " VALUES ('" & CleanString(strSegment) & "', '" & UNAME & "','" & dbDate() & "')"
  End Function

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

  End Function

  Public Shared 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 Shared Function sql_get_Channel(ByVal strChannel As String) As String
    Return "Select mstChannel.*, mstSegment.strSegment From mstChannel LEFT JOIN mstSegment ON SegmentID = refSegmentID WHERE strChannel = '" & strChannel & "'"
  End Function

  Public Shared Function sql_get_Channel(ByVal ChannelID As Integer) As String
    Return "Select mstChannel.*, mstSegment.strSegment From mstChannel LEFT JOIN mstSegment ON SegmentID = refSegmentID WHERE ChannelID = " & ChannelID
  End Function

  Public Shared Function sql_get_Channel(ByVal strChannel As String, ByVal ChannelID As Integer, ByVal refSegmentID As Integer) As String
    Return "Select mstChannel.*, mstSegment.strSegment From mstChannel LEFT JOIN mstSegment ON SegmentID = refSegmentID WHERE ChannelID <> " & ChannelID & " AND refSegmentID <> " & refSegmentID & " AND strChannel = '" & strChannel & "'"
  End Function

  Public Shared Function sql_insert_Channel(ByVal strChannel As String, ByVal refSegmentID As Integer) As String
    Return "INSERT INTO mstChannel (strChannel, refSegmentID, strLastUser, dtStamp)" & _
     " VALUES ('" & CleanString(strChannel) & "', " & refSegmentID & ", '" & UNAME & "','" & dbDate() & "')"
  End Function

  Public Shared Function sql_update_Channel(ByVal ChannelID As Integer, ByVal strChannel As String, ByVal refSegmentID As Integer) As String
    Return "UPDATE mstChannel SET strChannel = '" & CleanString(strChannel) & "', refSegmentID = " & refSegmentID & ", strLastUser = '" & UNAME & _
    "', dtStamp = '" & dbDate() & "' WHERE ChannelID = " & ChannelID

  End Function

  Public Shared Function sql_update_Channel_dw(ByVal dwChannel As String, ByVal strChannel As String, ByVal dwSegment As String, ByVal strSegment As String) As String
    Return "UPDATE dwdCustomer SET strChannel = '" & CleanString(strChannel) & "', strSegment = '" & CleanString(strSegment) & "'  WHERE strChannel = '" & dwChannel & "' AND strSegment = '" & dwSegment & "' "
  End Function

  '////   KeyAccount_Group

  Public Shared 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 Shared 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 Shared 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 = '" & strGroupName & "'"
  End Function

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

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

  End Function

  Public Shared 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

  Public Shared Function sql_get_KeyAccount(ByVal strKeyAccount As String) As String
    Return "Select * From mstKeyAccount WHERE strKeyAccount = '" & strKeyAccount & "'"
  End Function

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

  Public Shared 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

  Public Shared Function sql_insert_KeyAccount(ByVal strKeyAccount As String, ByVal strAccpacName As String, ByVal strAccpacCode As String, ByVal blnOncon As Integer, ByVal blnToolkit As Integer, ByVal blnRebate As Integer, ByVal blnPayLessInvoice As Integer, ByVal blnActive As Integer) As String
    Return "INSERT INTO mstKeyAccount (strKeyAccount, strAccpacName, strAccpacCode, blnOncon, blnToolkit, blnRebate, blnPayLessInvoice, blnActive, strLastUser, dtStamp) " & _
     "VALUES ('" & CleanString(strKeyAccount) & "', '" & CleanString(strAccpacName) & "', '" & CleanString(strAccpacCode) & "', " & blnOncon & ", " & blnToolkit & ", " & blnRebate & ", '" & blnPayLessInvoice & "', " & blnActive & ", '" & UNAME & "','" & dbDate() & "')	"
  End Function

  Public Shared Function sql_update_KeyAccount(ByVal KeyAccountID As Integer, ByVal strKeyAccount As String, ByVal strAccpacName As String, ByVal strAccpacCode As String, ByVal blnOncon As Integer, ByVal blnToolkit As Integer, ByVal blnRebate As Integer, ByVal blnPayLessInvoice As Integer, ByVal blnActive As Integer) As String
    Return "UPDATE mstKeyAccount SET strKeyAccount = '" & CleanString(strKeyAccount) & "', strAccpacName = '" & CleanString(strAccpacName) & _
    "', strAccpacCode = '" & CleanString(strAccpacCode) & "',  blnOncon = " & blnOncon & ", blnToolkit = " & blnToolkit & ", blnRebate = " & blnRebate & _
    ", blnPayLessInvoice = '" & blnPayLessInvoice & "', blnActive = " & blnActive & ", strLastUser = '" & UNAME & _
    "', dtStamp = '" & dbDate() & "' WHERE KeyAccountID = " & KeyAccountID

  End Function

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

  '////   Rep

  Public Shared Function sql_get_Rep(ByVal RepID As String) As String
    Return "Select mstRep.*, mstSegment.strSegment FROM mstRep LEFT JOIN mstSegment ON SegmentID = refSegmentID WHERE RepID = '" & RepID & "' "
  End Function

  Public Shared 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 Shared 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 strRegionalManager As String, ByVal strSalesManager As String, ByVal strSalesManagerEmail As String, ByVal blnRep As Integer, ByVal blnGenerateQF As Integer) As String
    Return "INSERT INTO mstRep (RepID, strRepName, strMarket, refBranchID, refSegmentID, strRegionalManager, blnRep, blnGenerateQF, strRepEmail, strSalesManager, strSalesManagerEmail, strLastUser, dtStamp)" & _
     " VALUES ('" & CleanString(RepID) & "', '" & CleanString(strRepName) & "', '" & CleanString(strMarket) & "', " & refBranchID & ", " & refSegmentID & ", '" & CleanString(strRegionalManager) & "', " & blnRep & ", " & blnGenerateQF & ", '" & CleanString(strRepEmail) & "', '" & CleanString(strSalesManager) & "', '" & CleanString(strSalesManagerEmail) & "', '" & UNAME & "','" & dbDate() & "')"
  End Function

  Public Shared 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 strRegionalManager As String, ByVal strSalesManager As String, ByVal strSalesManagerEmail As String, ByVal blnRep As Integer, ByVal blnGenerateQF As Integer) As String
    Return "UPDATE mstRep SET strRepName = '" & CleanString(strRepName) & "', strMarket = '" & CleanString(strMarket) & "', refBranchID = " & refBranchID & _
    ", refSegmentID = " & refSegmentID & ", strRegionalManager = '" & CleanString(strRegionalManager) & "', blnRep = " & blnRep & ", blnGenerateQF = " & blnGenerateQF & _
    ", strRepEmail = '" & CleanString(strRepEmail) & "', strSalesManager = '" & CleanString(strSalesManager) & "', strSalesManagerEmail = '" & CleanString(strSalesManagerEmail) & _
    "', strLastUser = '" & UNAME & "', dtStamp = '" & dbDate() & "' WHERE RepID = '" & RepID & "' "

  End Function

  Public Shared 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 Shared 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 Shared 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 Shared 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

  '\\\\   USER

  Public Shared 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 Shared 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 Shared 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

  Public Shared Function sql_insert_Users(ByVal strUserName As String, ByVal strDomainLogin As String, ByVal strEmail As String, ByVal refUGSCID As Integer, ByVal refUGSBID As Integer) As String
    Return "INSERT INTO mstUser (strUserName, strDomainLogin, setEmail, refUserGroupID, refUserGroupSuperbowlID, strLastUser, dtStamp) VALUES ('" & CleanString(strUserName) & "', '" & CleanString(strDomainLogin) & "', '" & CleanString(strEmail) & "', " & refUGSCID & ", " & refUGSBID & ", '" & UNAME & "', '" & dbDate() & "')"
  End Function

  Public Shared 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) As String
    Return "UPDATE mstUser SET strUserName = '" & CleanString(strUserName) & "', strDomainLogin = '" & CleanString(strDomainLogin) & "', strEmail = '" & CleanString(strEmail) & "', refUserGroupID = " & refUGSCID & ", refUserGroupSuperbowlID = " & refUGSBID & ", dtStamp = '" & dbDate() & "', strLastUser = '" & UNAME & "' WHERE UserID = " & UserID   ' , LastActivityDate = " & sqlDate() & "
  End Function

  'Public Shared 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 & ", '" & UNAME & "', '" & dbDate() & "')"
  'End Function
  'Public Shared 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 = '" & UNAME & "' WHERE UserID = " & UserID		' , LastActivityDate = " & sqlDate() & "
  'End Function

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


  '\\\\	USER GROUP

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

  '\\\\	Period

  Public Shared 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 Shared 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 Shared 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 Shared 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 Shared 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)
    Return "INSERT INTO stkProductPeriod ( mstProdCode, mstPeriod, dblOpening, dblQF, dblBMA, dblActual, dblOrders, dblReceipts, dblExports, dblDamages, dblRepack, dblClosing, dblMonths, strLastUser, dtStamp, strLastUserSM, dtStampSM)" & _
    " VALUES ('" & strProdCode & "', '" & strPeriod & "', " & dblOpening & ", " & dblQF & ",  " & dblBMA & ", " & dblActual & ", " & dblOrders & ", " & dblReceipts & ", " & dblExports & ", " & dblDamages & ", " & dblRepack & ", " & dblClosing & ", " & dblMonths & ", 'new', '" & dbDate() & "', 'new', '" & dbDate() & "')"
  End Function

  Public Shared 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 Shared 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 Shared 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 Shared 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 Shared 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 Shared 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 Shared 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 Shared 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 Shared 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 Shared 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 Shared 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) 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 & ",  '" & UNAME & "', '" & dbDate() & "')"
  End Function

  Public Shared 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) 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 & ",  '" & UNAME & "', '" & dbDate() & "')"
  End Function

  '\\\\ Import Log

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

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

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

End Class
