Imports Microsoft.VisualBasic
Imports System.Configuration
Imports System.Web.Configuration
Imports System.Data.SqlClient
Imports System.Data
Imports System.Globalization
'added direct sales 20210308 - lloyd
Public Class sqlDB
  'Inherits System.Web.UI.Page
  '#If DEBUG Then
  '  Public Shared conn As New Odbc.OdbcConnection(WebConfigurationManager.ConnectionStrings("odbcStockControl").ToString)
  '#Else
  '  Public Shared conn As New SqlConnection(WebConfigurationManager.ConnectionStrings("csStockControl").ToString)
  '#End If
  ' Connection string from Web.config when populated (e.g. dev); otherwise from env via SecureConfig (e.g. server without config write permission).
  Public Shared conn As New SqlConnection(GetConnectionString())

  Private Shared Function GetConnectionString() As String
    Dim css As ConnectionStringSettings = WebConfigurationManager.ConnectionStrings("csStockControl")
    If css IsNot Nothing AndAlso Not String.IsNullOrEmpty(css.ConnectionString) Then
      Return css.ConnectionString
    End If
    Return Global.Stock_Control.Stock_Control.SecureConfig.GetConnectionString("csStockControl")
  End Function

  Public Shared ci As CultureInfo
  Public Shared UNAME As String = WebConfigurationManager.AppSettings("UNAME")

  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("'", "''")
    strUnClean = nz(strUnClean, "")
    strClean = strUnClean.Replace(Chr(34), "'")
    strClean = strClean.Replace(Chr(39), "''")
    Return strClean.Trim
  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 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 = "" 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 getGridStartDate(ByVal strPeriod As String, ByVal intDiff As Integer) 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_
  '
  '//////////////////////////////////////////////////////////////////
  '// STOCK_CONTROL USER SQL FUNCTIONS
  '//////////////////////////////////////////////////////////////////

  '////   BRAND

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

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

  Public 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 Function sql_insert_Brand(ByVal strBrand As String, ByVal intMonths As Integer, ByVal dblVariance As Double, ByVal intBrandManager As Integer, ByVal intBrandManager2 As Integer, ByVal blnActive As Integer) As String
    Return "INSERT INTO mstBrand (strBrand, intLeadMonths, dblVariance, refBrandManagerID, refBrandManager2ID, strLastUser, dtStamp, blnActive) VALUES ('" & CleanString(strBrand) & "', " & intMonths & ", " & dblVariance & ", " & intBrandManager & ", " & intBrandManager2 & ", '" & WebConfigurationManager.AppSettings("UNAME") & "','" & dbDate() & "'," & blnActive & " )"
  End Function

  Public Function sql_update_Brand(ByVal BrandID As Integer, ByVal strBrand As String, ByVal intLeadMonths As Integer, ByVal dblVariance As Double, ByVal intBrandManager As Integer, ByVal intBrandManager2 As Integer, ByVal blnActive As Integer) As String
    Return "UPDATE mstBrand SET strBrand = '" & CleanString(strBrand) & "', intLeadMonths = " & intLeadMonths & ", dblVariance = " & dblVariance & ", refBrandManagerID = " & intBrandManager & ", refBrandManager2ID = " & intBrandManager2 & ", blnActive = " & blnActive & ", strLastUser = '" & WebConfigurationManager.AppSettings("UNAME") & "', dtStamp = '" & dbDate() & "' WHERE BrandID = " & BrandID   ' , LastActivityDate = " & sqlDate() & "
  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.strUserGroup" & _
    " FROM mstUserGroup INNER JOIN mstUser ON mstUserGroup.UserGroupID = mstUser.refUserGroupID 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

  Public Function sql_insert_Users(ByVal strUserName As String, ByVal strDomainLogin As String, ByVal intType As Integer) As String
    Return "INSERT INTO mstUser (strUserName, strDomainLogin, refUserGroupID, strLastUser, dtStamp) VALUES ('" & CleanString(strUserName) & "', '" & CleanString(strDomainLogin) & "', " & intType & ", '" & WebConfigurationManager.AppSettings("UNAME") & "', '" & dbDate() & "')"
  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 & ", '" & WebConfigurationManager.AppSettings("UNAME") & "', '" & dbDate() & "')"
  End Function

  Public Function sql_update_Users(ByVal UserID As Integer, ByVal strUserName As String, ByVal strDomainLogin As String, ByVal intType As Integer) As String
    Return "UPDATE mstUser SET strUserName = '" & CleanString(strUserName) & "', strDomainLogin = '" & CleanString(strDomainLogin) & "', refUserGroupID = " & intType & ", dtStamp = '" & dbDate() & "', strLastUser = '" & WebConfigurationManager.AppSettings("UNAME") & "' WHERE UserID = " & UserID    ' , LastActivityDate = " & sqlDate() & "
  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 = '" & WebConfigurationManager.AppSettings("UNAME") & "' WHERE UserID = " & UserID    ' , LastActivityDate = " & sqlDate() & "
  End Function

  '\\\\	USER GROUP

  Public Function sql_get_UserType(ByVal strUser As String) As String
    Return "Select * From mstUser WHERE strUserName = '" & CleanString(strUser) & "'"
  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

  '\\\\	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 * mstProduct.dblFlat) AS dblActual " & _
   "FROM mstPeriod INNER JOIN dwfSales ON mstPeriod.strPeriod = dwfSales.refPeriod INNER JOIN mstProduct ON dwfSales.refProdCode = mstProduct.strProdCode " & _
   "WHERE Left(mstProduct.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, Optional ByVal strVarietal As String = "", Optional ByVal strCategory 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.refProdCode = mstProduct.strProdCode " & _
    "WHERE mstProduct.refBrandID=" & refBrandID & " AND mstProduct.strBrandVarietal LIKE ('%" & strVarietal & "%') AND mstProduct.strCategory2 LIKE ('%" & strCategory & "%') " & _
    "GROUP BY mstPeriod.strPeriod, mstPeriod.intCalendarMonth " & _
    "HAVING mstPeriod.strPeriod>='" & strStartDate & "' And mstPeriod.strPeriod<='" & strEndDate & "' "
  End Function

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

    Public Function sql_get_DirectSalesAll(ByVal refBrandID As Integer, ByVal strStartDate As String, ByVal strEndDate As String, Optional ByVal strVarietal As String = "", Optional ByVal strCategory 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.refProdCode = mstProduct.strProdCode " &
        "WHERE mstProduct.refBrandID=" & refBrandID & " AND mstProduct.strBrandVarietal LIKE ('%" & strVarietal & "%') AND mstProduct.strCategory2 LIKE ('%" & strCategory & "%') AND dwfSales.refDocumentNo LIKE 'Y%' " &
        "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, stkProductPeriod.dblOpeningImports " & _
        '" 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"

        ' [DS20240619] - changed query to bring back 0 for any values that are NULL in the database
        '              - NULL values were causing issues when trying to truncate decimal with in Save() function on ProductPeriod.aspx
        Return "SELECT " &
               "    stkProductPeriod.mstPeriod, 
	                stkProductPeriod.dblOpening, 
	                stkProductPeriod.dblQF, 
	                ISNULL(dblBMA, 0) AS 'dblBMA', 
	                ISNULL(stkProductPeriod.dblActual, 0) AS 'dblActual', 
	                ISNULL(stkProductPeriod.dblOrders, 0) AS 'dblOrders', 
	                ISNULL(stkProductPeriod.dblReceipts, 0) AS 'dblReceipts', 
	                ISNULL(stkProductPeriod.dblExports, 0) AS 'dblExports', 
	                ISNULL(stkProductPeriod.dblDamages, 0) AS 'dblDamages', 
	                ISNULL(stkProductPeriod.dblRepack, 0) AS 'dblRepack', 
	                stkProductPeriod.dblClosing, 
	                stkProductPeriod.dblMonths, 
	                stkProductPeriod.strLastUser, 
	                stkProductPeriod.dtStamp, 
	                stkProductPeriod.strLastUserSM, 
	                stkProductPeriod.dtStampSM, 
	                stkProductPeriod.mstProdCode, 
	                stkProductPeriod.dblOpeningImports" &
               " 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 dblBMA As Double, ByVal dblOrders As Double, Optional ByVal strEdit As String = "")
    If strEdit.Length = 0 Then
      If WebConfigurationManager.AppSettings("UGROUP").ToLower.Contains("brand") Then
        strEdit = "dblBMA = " & dblBMA & ", strLastUser = '" & WebConfigurationManager.AppSettings("UNAME") & "', dtStamp = '" & dbDate() & "'"
      End If
      If WebConfigurationManager.AppSettings("UGROUP").ToLower.Contains("shipping") Or WebConfigurationManager.AppSettings("UGROUP").ToLower.Contains("order") Then
        strEdit = "dblOrders = " & dblOrders & ", strLastUserSM = '" & WebConfigurationManager.AppSettings("UNAME") & "', dtStampSM = '" & dbDate() & "'"
      End If
      If WebConfigurationManager.AppSettings("UGROUP").ToLower.Contains("admin") Then
        strEdit = "dblBMA = " & dblBMA & ", strLastUser = '" & WebConfigurationManager.AppSettings("UNAME") & "', dtStamp = '" & dbDate() & "', dblOrders = " & dblOrders & ", strLastUserSM = '" & WebConfigurationManager.AppSettings("UNAME") & "', dtStampSM = '" & dbDate() & "'"
      End If
    End If

    Return "UPDATE stkProductPeriod SET " & 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, Optional ByVal strVarietal As String = "", Optional ByVal strCategory 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, SUM(dblOpeningImports) as dblOpeningImports " & _
   " FROM stk9L WHERE strCategory2 LIKE('%" & strCategory & "%') AND strBrandVarietal LIKE('%" & strVarietal & "%') 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
      'v1.2.9  - 20130114 - changed All Products (9L) view to use OpeningImports values if not 0
      Return "INSERT INTO stk9L (mstBrandID, mstPeriod, dblOpening, dblQF, dblBMA, dblActual, dblOrders, dblReceipts, dblExports, dblDamages, dblRepack, dblClosing, dblMonths, strCategory2, strBrandVarietal, dblOpeningImports) " & _
    " SELECT mstProduct.refBrandID, stkProductPeriod.mstPeriod, SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblOpening) / 9000 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, mstProduct.strCategory2, mstProduct.strBrandVarietal, CASE WHEN SUM(stkProductPeriod.dblOpeningImports) <> 0 THEN SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblOpeningImports) / 9000 ELSE SUM(mstProduct.intPack * mstProduct.intSize * stkProductPeriod.dblOpening) / 9000 END AS dblOpeningImports " & _
    " FROM (mstPeriod INNER JOIN stkProductPeriod ON mstPeriod.strPeriod = stkProductPeriod.mstPeriod) INNER JOIN mstProduct ON stkProductPeriod.mstProdCode = left(mstProduct.strProdCode,5) " & _
    " WHERE strProdCode LIKE '%F%' AND mstProduct.blnActive = 1 AND (mstProduct.blnCompProduct = 0 OR mstProduct.blnCompProduct IS NULL) " & _
    " GROUP BY stkProductPeriod.mstPeriod, mstProduct.refBrandID, mstPeriod.strPeriod, stkProductPeriod.mstProdCode, mstProduct.intPack, mstProduct.intSize, mstProduct.strCategory2, mstProduct.strBrandVarietal " & _
    " HAVING (mstProduct.refBrandID = " & intBrandID & ") AND (mstPeriod Between '" & strStartDate & "' AND '" & strEndDate & "') ORDER BY mstPeriod.strPeriod"



      '  Return "INSERT INTO stk9L (mstBrandID, mstPeriod, dblOpening, dblQF, dblBMA, dblActual, dblOrders, dblReceipts, dblExports, dblDamages, dblRepack, dblClosing, dblMonths, strCategory2, strBrandVarietal) " & _
      '" 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, mstProduct.strCategory2, mstProduct.strBrandVarietal " & _
      '" 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, mstProduct.strCategory2, mstProduct.strBrandVarietal " & _
      '" 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 (mstProduct.blnCompProduct = 0 OR mstProduct.blnCompProduct IS NULL) 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) 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 & ",  '" & WebConfigurationManager.AppSettings("UNAME") & "', '" & 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) 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 & ",  '" & WebConfigurationManager.AppSettings("UNAME") & "', '" & 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) As String
    Return "INSERT INTO stkImportLog (mstPeriod, strLastUser, dtStamp) " & _
    "VALUES ('" & pPeriod & "', '" & WebConfigurationManager.AppSettings("UNAME") & "', '" & dbDate() & "') "
  End Function

  Public Function sql_Update_ImportLog(ByVal pPeriod As String, ByVal pCol As String) As String
    Return "UPDATE stkImportLog SET " & pCol & " = 1, strLastUser='" & WebConfigurationManager.AppSettings("UNAME") & "', 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


  '//// COPY FROM Superbowl

  Public Shared Function sql_update_Customer_dw() 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, 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, blnPromotionalAccounts = c.blnPromotionalAccounts, " & _
    "blnActive = c.blnActive " & _
    "FROM mstBranch AS b 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 AS c ON dwdCustomer.strCustomerNo = c.strCustomerNo ON mstChannel.ChannelID = c.refChannelID LEFT OUTER JOIN mstKeyAccount_Group LEFT OUTER JOIN mstKeyAccount ON mstKeyAccount_Group.refKeyAccountID = mstKeyAccount.KeyAccountID ON c.refKeyAccount_GroupID = mstKeyAccount_Group.KeyAccount_GroupID ON mstRep_4.RepID = c.refRepLuxID ON mstRep_3.RepID = c.refRepDlxID LEFT OUTER JOIN mstRep AS mstRep_2 ON c.refRepBBID = mstRep_2.RepID LEFT OUTER JOIN mstRep ON c.refRepID = mstRep.RepID ON mstType2.Type2ID = c.refType2ID LEFT OUTER JOIN mstType1 ON c.refType1ID = mstType1.Type1ID LEFT OUTER JOIN mstSnellsupergroup ON c.strSnellsupergroup = mstSnellsupergroup.strSnellsupergroup ON b.BranchID = c.refBranchID "
  End Function


  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
End Class