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

Public Class sqlDB_Daily
   '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.ToString.Trim
            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
      Dim strClean As String
      strUnClean = Nz(strUnClean, "")
      'strClean = strUnClean.Replace(Chr(13), "'r")
      'strClean = strUnClean.Replace("'", "''")
      strClean = Nz(strUnClean.Replace(Chr(34), "'"), "")
      strClean = strClean.Replace(Chr(39), "''")
      Return strClean
   End Function

   Public Shared Function doQueryDS(ByVal strSQL As String) As DataSet
      Dim adp As New SqlDataAdapter(strSQL.Replace("dbo_", ""), 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 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_
   '

   Public Function sql_insert_stgZMB(ByVal prodcode As String, ByVal proddesc As String, ByVal branch As String, ByVal f4 As String, ByVal f5 As String, ByVal mvt As String, ByVal f7 As String, ByVal f8 As String, ByVal f9 As String, ByVal period As String, ByVal flat As Double, ByVal bot As String, ByVal f13 As String, ByVal f14 As String, ByVal f15 As String, ByVal f16 As String) As String
      Return "INSERT INTO stgZMB(prodcode, proddesc, branch, f4, f5, mvt, f7, f8, f9, period, flat, bot, f13, f14, f15, f16)" & _
      "VALUES ('" & CleanString(prodcode) & "', '" & CleanString(proddesc) & "', '" & CleanString(branch) & "', '" & CleanString(f4) & "', '" & CleanString(f5) & "', '" & CleanString(mvt) & "', '" & CleanString(f7) & "', '" & CleanString(f8) & "', '" & CleanString(f9) & "', '" & CleanString(period) & "', " & flat & ", '" & CleanString(bot) & "', '" & CleanString(f13) & "', '" & CleanString(f14) & "', '" & CleanString(f15) & "', '" & CleanString(f16) & "')"
   End Function

   'v1.28 - 20141031 - Added FSR/Pinkslip Number to c14 import - added strPinkslipNumber
    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, ByVal strPSN As String)
      Return "INSERT INTO stgCognos14 (doctype, docno, cusno, cusname, rep, period, movement, prodcode, proddesc, packs, packsize, units, ninelce, ordernum, nett, measure, gsv, dblNettEx,strPSN)" & _
        "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 & ", '" & strPSN & "')"
    End Function

   Public Shared Function sql_update_Customer_daily(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, Optional ByVal blnActive As Integer = -1) As String
      Dim strSet As String = ""
      If blnActive <> -1 Then
         'v1.26 - 20130903 - Changed Customer/cognog09 import mstCustomer.blnActive = !$blnActive
         If blnActive = 0 Then
            blnActive = 1
         Else
            blnActive = 0
         End If

         strSet = ", blnActive = " & blnActive
      End If
         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) & "', refBranchID = " & refBranchID & strSet & _
         " WHERE strCustomerNo='" & CleanString(strCustomerNo) & "' "
   End Function


   '////   Exports from superbowl!!

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

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

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

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

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

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

   'generating doc numbers:
   'format: "Y" & fill("0") + ExportID (max length = 8)
   Public Shared Function sql_insert_Sales_Exports(ByVal refBatch As String) As String
      Return "INSERT INTO dwfSales (refPeriod, refCustomerNo, refProdCode, refDocumentNo, strDocumentType, dblNSV, dblGSV, lngUnits, lngUnitsTotal, lngUnitsFreeStock, dbl9lFreeStock, dbl9l, dbl9lTotal, dblNettEx) " & _
        "SELECT mstExports.mstPeriod, mstExports.mstCustomer, mstExports.mstProdCode, 'Y' + RIGHT('000000' + CAST(mstExports.ExportID AS VARCHAR), 7) AS refDocumentNo, 'I' AS strDocumentType, mstExports.dblNSV, mstExports.dblGSV, mstExports.dblUnits, mstExports.dblUnits AS lngUnitsTotal, 0 AS lngUnitsFreeStock, 0 AS lng9lFreeStock, ROUND(mstExports.dblUnits * mstProduct.intPack * mstProduct.intSize / 9000, 4) AS dbl9l, ROUND(mstExports.dblUnits * mstProduct.intPack * mstProduct.intSize / 9000, 4) AS dbl9lTotal, mstExports.dblNettEx " & _
        "FROM mstProduct INNER JOIN mstExports ON mstProduct.strProdCode = mstExports.mstProdCode " & _
        "WHERE mstExports.refBatch = '" & refBatch & "'"
   End Function

   '//// Exports not superbowl

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

   Public Function sql_insert_Salba(ByVal intYear As Integer, ByVal strMonth As String, ByVal intMonth As Integer, ByVal strCountry As String, ByVal strRegion As String, ByVal strArea As String, ByVal intAreaCode As Integer, ByVal strCategory As String, ByVal intCatCode As Integer, ByVal dblSales As Double) As String
      Return "INSERT INTO dwfSalba (intYear, strMonth, intMonth, strCountry, strRegion, strArea, intAreaCode, strCategory, intCatCode, dblSales) " & _
        "VALUES (" & intYear & ", '" & CleanString(strMonth) & "', " & (intMonth) & ", '" & CleanString(strCountry) & "', '" & CleanString(strRegion) & "', '" & CleanString(strArea) & "', " & intAreaCode & ", '" & CleanString(strCategory) & "', " & (intCatCode) & ", " & dblSales & ")"
   End Function

   '//// Stock
   'v1.11
   'v1.21 - 20091019
   'v1.23 - 20091209
   Public Function sql_insert_Stock(ByVal strPeriod As String, ByVal strProdCode As String, ByVal strDate As String, ByVal dbDBN As Double, ByVal dblNCL As Double, ByVal dblJHB As Double, ByVal dblPOL As Double, ByVal dbCPT As Double, ByVal dblGRG As Double, ByVal dbBFN As Double, ByVal dbPE As Double, ByVal dblELN As Double, ByVal dblJBond As Double, ByVal dblJCons As Double, ByVal dblCBond As Double, ByVal dblCCons As Double, ByVal dblExport2 As Double, ByVal dbDP As Double) As String
      Return "INSERT INTO dwfStock (strPeriod, strProdCode, strDate, dbDBN, dblNCL, dblJHB, dblPOL, dbCPT, dblGRG, dbBFN, dbPE, dblELN, dblJBond, dblJCons, dblCBond, dblCCons, dblExport2, dbDP) " & _
        "VALUES ('" & strPeriod & "','" & strProdCode & "','" & strDate & "', " & dbDBN & ", " & dblNCL & ", " & dblJHB & ", " & dblPOL & ", " & dbCPT & ", " & dblGRG & ", " & dbBFN & ", " & dbPE & ", " & dblELN & ", " & dblJBond & ", " & dblJCons & ", " & dblCBond & ", " & dblCCons & ", " & dblExport2 & ", " & dbDP & ")"
   End Function

   '//// Rep Calls 20090911
   'v1.18
   Public Function sql_insert_RepCall(ByVal strDate As String, ByVal RepID As String, ByVal strRepName As String, ByVal strRegionDesc2 As String, ByVal intAppointmentScheduled As Integer, ByVal intAppointmentOpen As Integer, ByVal intAppointmentCompleted As Integer, ByVal intAppointmentCompletedNoAccount As Integer, ByVal intAppointmentCancelled As Integer, ByVal intOutOfOfficeScheduled As Integer, ByVal memOutOfOfficeDetails As String, ByVal intSpecialActivityScheduled As Integer, ByVal memSpecialActivityDetails As String, ByVal intSurveyOn As Integer, ByVal intSurveyOff As Integer, ByVal intOrders As Integer) As String
      Return "INSERT INTO dwfRepCalls (strDate, RepID, strRepName, strRegionDesc2, intAppointmentScheduled, intAppointmentOpen, intAppointmentCompleted, intAppointmentCompletedNoAccount, intAppointmentCancelled, intOutOfOfficeScheduled, memOutOfOfficeDetails, intSpecialActivityScheduled, memSpecialActivityDetails, intSurveyOn, intSurveyOff, intOrders) " & _
        "VALUES ('" & CleanString(strDate) & "' , '" & CleanString(RepID) & "', '" & CleanString(strRepName) & "', '" & CleanString(strRegionDesc2) & "', " & intAppointmentScheduled & ", " & intAppointmentOpen & ", " & intAppointmentCompleted & ", " & intAppointmentCompletedNoAccount & ", " & intAppointmentCancelled & ", " & intOutOfOfficeScheduled & ", '" & CleanString(memOutOfOfficeDetails) & "', " & intSpecialActivityScheduled & ", '" & CleanString(memSpecialActivityDetails) & "', " & intSurveyOn & ", " & intSurveyOff & ", " & intOrders & ")"
   End Function

   '//// OffconSurvey 20090911
   'v1.18
   Public Function sql_insert_OffconSurvey(ByVal strDate As String, ByVal RepID As String, ByVal strCustomerNo As String, ByVal strProdCode As String, ByVal strRepName As String, ByVal strCustomerName As String, ByVal strProductDesc As String, ByVal blnDistribution As String, ByVal dblRSP As String, ByVal blnDisplayBin As String, ByVal blnDisplayGondola As String, ByVal blnDisplayTill As String, ByVal intFacings As String, ByVal blnShelvingAccessible As String, ByVal blnShelvingPlinth As String, Optional ByVal blnShelvingNone As String = "0", Optional ByVal strDateSurveyed As String = "00000000", Optional ByVal blnLocation_AbsolutBlue As String = "0", Optional ByVal blnLocation_AbsolutFlavours As String = "0", Optional ByVal blnLocation_BlackGrouse As String = "0", Optional ByVal blnLocation_Bombay As String = "0", Optional ByVal blnLocation_FamousGrouse As String = "0", Optional ByVal blnLocation_JSDB As String = "0", Optional ByVal blnLocation_SocoLiquerCategory As String = "0", Optional ByVal blnLocation_Soco As String = "0", Optional ByVal blnLocation_SocoWhiskeyCategory As String = "0", Optional ByVal blnLocation_TullamoreDew As String = "0", Optional ByVal strPeriod As String = "0") As String
      'SD1 version:
      Return "INSERT INTO dwfOffconSurvey (strDate, RepID, strCustomerNo, strProdCode, strRepName, strCustomerName, strProductDesc, blnDistribution, dblRSP, blnDisplayBin, blnDisplayGondola, blnDisplayTill, intFacings, blnShelvingAccessible, blnShelvingPlinth, strDateSurveyed, blnShelvingNone, blnLocation_AbsolutBlue_Next_To_White_Spirits, blnLocation_AbsolutFlavours_Next_To_AbsolutBlue, blnLocation_BlackGrouse_Next_To_TFG, blnLocation_Bombay_Next_To_White_Spirits, blnLocation_FamousGrouse_Next_To_Bells, blnLocation_JSDB_Next_To_White_JackDaniels, blnLocation_SocoLiquerCategory, blnLocation_Soco_Next_To_SpicedGold, blnLocation_SocoWhiskeyCategory, blnLocation_TullamoreDew_Next_To_Jameson, strPeriod) " & _
              "VALUES ('" & CleanString(strDate) & "', '" & CleanString(RepID) & "', '" & CleanString(strCustomerNo) & "', '" & CleanString(strProdCode) & "', '" & CleanString(strRepName) & "', '" & CleanString(strCustomerName) & "', '" & CleanString(strProductDesc) & "', " & blnDistribution & ", " & dblRSP & ", " & blnDisplayBin & ", " & blnDisplayGondola & ", " & blnDisplayTill & ", " & intFacings & ", " & blnShelvingAccessible & ", " & blnShelvingPlinth & ", '" & CleanString(strDateSurveyed) & "', " & blnShelvingNone & ", " & blnLocation_AbsolutBlue & ", " & blnLocation_AbsolutFlavours & ", " & blnLocation_BlackGrouse & ", " & blnLocation_Bombay & ", " & blnLocation_FamousGrouse & ", " & blnLocation_JSDB & ", " & blnLocation_SocoLiquerCategory & ", " & blnLocation_Soco & ", " & blnLocation_SocoWhiskeyCategory & ", " & blnLocation_TullamoreDew & ", '" & strPeriod & "')"

      'return statement changed by dwayne peterson 15/08/013, above fields were not valid in dwfOffconSurvey
      'SD2 version:
      'Return "INSERT INTO dwfOffconSurvey (strCustomerNo, strProdCode ,strDateSurveyed ,strCustomerName ,strProductDesc ,blnDistribution ,dblRSP ,blnDisplayBin ,blnDisplayGondola ,blnDisplayTill ,intFacings ,blnShelvingAccessible ,blnShelvingPlinth ,blnShelvingNone ,blnLocation_AbsolutBlue_Next_To_White_Spirits ,blnLocation_AbsolutFlavours_Next_To_AbsolutBlue ,blnLocation_BlackGrouse_Next_To_TFG ,blnLocation_Bombay_Next_To_White_Spirits  ,blnLocation_FamousGrouse_Next_To_Bells ,blnLocation_JSDB_Next_To_White_JackDaniels ,blnLocation_SocoLiquerCategory ,blnLocation_Soco_Next_To_SpicedGold ,blnLocation_SocoWhiskeyCategory,blnLocation_TullamoreDew_Next_To_Jameson, strPeriod) " & _
      '"VALUES ('" & CleanString(strCustomerNo) & "', '" & CleanString(strProdCode) & "', '" & CleanString(strDateSurveyed) & "', '" & CleanString(strCustomerName) & "', '" & CleanString(strProductDesc) & "', " & blnDistribution & ", " & dblRSP & ", " & blnDisplayBin & ", " & blnDisplayGondola & ", " & blnDisplayTill & ", " & intFacings & ", " & blnShelvingAccessible & ", " & blnShelvingPlinth & ", " & blnShelvingNone & ", " & blnLocation_AbsolutBlue & ", " & blnLocation_AbsolutFlavours & ", " & blnLocation_BlackGrouse & ", " & blnLocation_Bombay & ", " & blnLocation_FamousGrouse & ", " & blnLocation_JSDB & ", " & blnLocation_SocoLiquerCategory & ", " & blnLocation_Soco & ", " & blnLocation_SocoWhiskeyCategory & ", " & blnLocation_TullamoreDew & ", " & strPeriod & ")"

   End Function

   '//// OnconSurvey 20090911
   'v1.18
   Public Function sql_insert_OnconSurvey(ByVal strDate As String, ByVal RepID As String, ByVal strCustomerNo As String, ByVal strBrand As String, ByVal strDateSurveyed As String, ByVal strRepName As String, ByVal strCustomerName As String, ByVal blnDistribution As String, ByVal dblRSP As String, ByVal blnDisplayBar As String, ByVal blnDisplayOptic As String, ByVal blnDisplaySpeedRail As String, ByVal blnVisibleAccessories As String, ByVal blnVisibleClothing As String, ByVal blnVisibleMenu As String, ByVal blnVisibleSignage As String) As String
      Return "INSERT INTO dwfOnconSurvey (strDate, RepID, strCustomerNo, strBrand, strDateSurveyed, strRepName, strCustomerName, blnDistribution, dblRSP, blnDisplayBar, blnDisplayOptic, blnDisplaySpeedRail, blnVisibleAccessories, blnVisibleClothing, blnVisibleMenu, blnVisibleSignage) " & _
        "VALUES ('" & CleanString(strDate) & "', '" & CleanString(RepID) & "', '" & CleanString(strCustomerNo) & "', '" & CleanString(strBrand) & "', '" & CleanString(strDateSurveyed) & "', '" & CleanString(strRepName) & "', '" & CleanString(strCustomerName) & "', " & blnDistribution & ", " & dblRSP & ", " & blnDisplayBar & ", " & blnDisplayOptic & ", " & blnDisplaySpeedRail & ", " & blnVisibleAccessories & ", " & blnVisibleClothing & ", " & blnVisibleMenu & ", " & blnVisibleSignage & ")"
   End Function

   '//// OffconPromo 20090929
   'v1.19
   Public Function sql_insert_OffconPromo(ByVal strDate As String, ByVal strRepName As String, ByVal RepID As String, ByVal strCustomerName As String, ByVal strCustomerNo As String, ByVal strRegionDesc2 As String, ByVal strBrand As String, ByVal intUnits As Integer, ByVal intPromoters As Integer, ByVal strType As String, ByVal strPromoKit As String, ByVal intPromoKit As Integer, ByVal dblCost As Double, ByVal dblRSP As Double, ByVal dblCompetitor1RSP As Double, ByVal dblCompetitor2RSP As Double, ByVal strDateStart As String, ByVal strDateEnd As String) As String
      Return "INSERT INTO dwfOffconPromo(strDate, strRepName, RepID, strCustomerName, strCustomerNo, strRegionDesc2, strBrand, intUnits, intPromoters, strType, strPromoKit, intPromoKit, dblCost, dblRSP, dblCompetitor1RSP, dblCompetitor2RSP, strDateStart, strDateEnd) " & _
            " VALUES ('" & CleanString(strDate) & "', '" & CleanString(strRepName) & "', '" & CleanString(RepID) & "', '" & CleanString(strCustomerName) & "', '" & CleanString(strCustomerNo) & "', '" & CleanString(strRegionDesc2) & "', '" & CleanString(strBrand) & "', " & intUnits & ", " & intPromoters & ", '" & CleanString(strType) & "', '" & CleanString(strPromoKit) & "', " & intPromoKit & ", " & dblCost & ", " & dblRSP & ", " & dblCompetitor1RSP & ", " & dblCompetitor2RSP & ", '" & CleanString(strDateStart) & "', '" & CleanString(strDateEnd) & "')"
   End Function

   '//// OnconPromo 20090929
   'v1.19
   Public Function sql_insert_OnconPromo(ByVal strDate As String, ByVal strRepName As String, ByVal RepID As String, ByVal strCustomerName As String, ByVal strCustomerNo As String, ByVal strRegionDesc2 As String, ByVal strBrand As String, ByVal intTots As String, ByVal strObjective As String, ByVal strType As String, ByVal strPromoKit As String, ByVal intPromoKit As String, ByVal dblCost As String, ByVal dblRSP As String, ByVal dblCompetitor1RSP As String, ByVal dblCompetitor2RSP As String, ByVal memKeyLearning As String, ByVal intDataCards As String, ByVal strParticipationRating As String, ByVal strResponseDrinks As String, ByVal strResponsePrizes As String, ByVal strResponseEntertainment As String, ByVal strResponseBranding As String, ByVal strDisplayBar As String, ByVal strDisplayGeneral As String, ByVal strDisplayTriggerBanner As String, ByVal strPOS As String, ByVal strDatePromo As String) As String
      Return "INSERT INTO dwfOnconPromo (strDate, strRepName, RepID, strCustomerName, strCustomerNo, strRegionDesc2, strBrand, intTots, strObjective, strType, strPromoKit, intPromoKit, dblCost, dblRSP, dblCompetitor1RSP, dblCompetitor2RSP, memKeyLearning, intDataCards, strParticipationRating, strResponseDrinks, strResponsePrizes, strResponseEntertainment, strResponseBranding, strDisplayBar, strDisplayGeneral, strDisplayTriggerBanner, strPOS, strDatePromo) " & _
             "VALUES ('" & CleanString(strDate) & "', '" & CleanString(strRepName) & "', '" & CleanString(RepID) & "', '" & CleanString(strCustomerName) & "', '" & CleanString(strCustomerNo) & "', '" & CleanString(strRegionDesc2) & "', '" & CleanString(strBrand) & "', " & intTots & ", '" & CleanString(strObjective) & "', '" & CleanString(strType) & "', '" & CleanString(strPromoKit) & "', " & intPromoKit & ", " & dblCost & ", " & dblRSP & ", " & dblCompetitor1RSP & ", " & dblCompetitor2RSP & ", '" & CleanString(memKeyLearning) & "', " & intDataCards & ", '" & CleanString(strParticipationRating) & "', '" & CleanString(strResponseDrinks) & "', '" & CleanString(strResponsePrizes) & "', '" & CleanString(strResponseEntertainment) & "', '" & CleanString(strResponseBranding) & "', '" & CleanString(strDisplayBar) & "', '" & CleanString(strDisplayGeneral) & "', '" & CleanString(strDisplayTriggerBanner) & "', '" & CleanString(strPOS) & "', '" & CleanString(strDatePromo) & "')"
   End Function

    'v1.22 - 20091023 
    'v1.31 - 20181107 - update AdSpend process: remove ProductSubCategory, add ai_imagefile (vc255 null) - pj
    Public Function sql_insert_AdSpend(ByVal dr As DataRow) As String

		Return "INSERT INTO dwfAdSpend(AdGroup, AdSize, Brand, BrandName, Caption, Chain, Channel, CorporateGroup, IssueDate, PortionofAdspend, PrintMediaType, ProductCategory, ProductExtra, ProductExtraCompact, PublicationName, RegionofPublication, RetailSalesPrice, StoreName, TypeofPublication, UnitMeasurecompact, ai_imagefile)" &
			"VALUES ('" & CleanString(dr!AdGroup) & "', '" & CleanString(dr!AdSize) & "', '" & CleanString(dr!Brand) & "', '" & CleanString(dr!BrandName) & "', '" & CleanString(dr!Caption) & "', '" & CleanString(dr!Chain) & "', '" & CleanString(dr!Channel) & "', '" & CleanString(dr!CorporateGroup) & "', '" & dr!IssueDate & "', " & dr!PortionofAdspend & ", '" & CleanString(dr!PrintMediaType) & "', '" & CleanString(dr!ProductCategory) & "', '" & CleanString(dr!ProductExtra) & "', '" & CleanString(dr!ProductExtraCompact) & "', '" & CleanString(dr!PublicationName) & "', '" & CleanString(dr!RegionofPublication) & "', " & dr!RetailSalesPrice & ", '" & CleanString(dr!StoreName) & "', '" & CleanString(dr!TypeofPublication) & "', '" & CleanString(dr!UnitMeasurecompact) & "', '" & CleanString(dr!ai_imagefile) & "')"

		'Return "INSERT INTO dwfAdSpend(AdGroup, AdSize, Brand, BrandName, Caption, Chain, Channel, CorporateGroup, IssueDate, PortionofAdspend, PrintMediaType, ProductCategory, ProductExtra, ProductSubCategory, ProductExtraCompact, PublicationName, RegionofPublication, RetailSalesPrice, StoreName, TypeofPublication, UnitMeasurecompact)" & _
		'"VALUES ('" & CleanString(dr!AdGroup) & "', '" & CleanString(dr!AdSize) & "', '" & CleanString(dr!Brand) & "', '" & CleanString(dr!BrandName) & "', '" & CleanString(dr!Caption) & "', '" & CleanString(dr!Chain) & "', '" & CleanString(dr!Channel) & "', '" & CleanString(dr!CorporateGroup) & "', '" & dr!IssueDate & "', " & dr!PortionofAdspend & ", '" & CleanString(dr!PrintMediaType) & "', '" & CleanString(dr!ProductCategory) & "', '" & CleanString(dr!ProductExtra) & "', '" & CleanString(dr!ProductSubCategory) & "', '" & CleanString(dr!ProductExtraCompact) & "', '" & CleanString(dr!PublicationName) & "', '" & CleanString(dr!RegionofPublication) & "', " & dr!RetailSalesPrice & ", '" & CleanString(dr!StoreName) & "', '" & CleanString(dr!TypeofPublication) & "', '" & CleanString(dr!UnitMeasurecompact) & "')"
	End Function
End Class
