Imports Superbowl.Duodecimal
Imports System.Web.Configuration

Partial Public Class IntegrityTest
  Inherits System.Web.UI.Page
  Private ds As DataSet
  Private dr As DataRow
  'Private today As String
  Private date1 As String
  Private date2 As String

  Private Shared blnCMst, blnCDW, blnPMst, blnPDW As Boolean

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    'ds = sqlDB.doQueryDS("SELECT mstBrand.BrandID, mstBrand.strBrand, mstBrand.blnActive, mstPrincipal.strPrincipal, mstPrincipal.strFYStart, mstPrincipal.strFYEnd, mstCompany.strCompany, mstGroupingPrimary.strGroupingPrimary, mstGroupingDaily.strGroupingDaily, mstGroupingSecondary.strGroupingSecondary " & _
    '      "FROM mstBrand LEFT OUTER JOIN mstGroupingPrimary ON mstBrand.refGroupingPrimaryID = mstGroupingPrimary.GroupingPrimaryID LEFT OUTER JOIN mstPrincipal ON mstBrand.refPrincipalID = mstPrincipal.PrincipalID LEFT OUTER JOIN mstCompany ON mstBrand.refCompanyID = mstCompany.CompanyID LEFT OUTER JOIN mstGroupingSecondary ON mstBrand.refGroupingSecondaryID = mstGroupingSecondary.GroupingSecondaryID LEFT OUTER JOIN mstGroupingDaily ON mstBrand.refGroupingDailyID = mstGroupingDaily.GroupingDailyID")

    'lblMessage.Text = " # of Updates:" & ds.Tables(0).Rows.Count
    Dim duoDate As New Duodecimal()
    Dim s, strDate, strDate1, strDate2 As String

    strDate = Date.Now.ToString("yyyyMMdd")
    'duoDate.Add(-1)
    strDate1 = duoDate.DuoDate.ToString("yyyyMMdd")
    gvSummary.Columns(3).HeaderText = duoDate.DuoDate.ToString("MMM yyyy")
    duoDate.Add(-1)
    strDate2 = duoDate.DuoDate.ToString("yyyyMMdd")
    gvSummary.Columns(4).HeaderText = duoDate.DuoDate.ToString("MMM yyyy")

    s = sdsSummary.SelectCommand.ToString

    's = s.Replace("#today", "'" & strDate & "'")
    s = s.Replace("#date1", "'" & strDate1 & "'")
    s = s.Replace("#date2", "'" & strDate2 & "'")

    sdsSummary.SelectCommand = ""
    sdsSummary.SelectCommand = s

  End Sub

  'Protected Sub btnBrand_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnBrand.Click
  '  'UpdateBrands()
  '  lblMessage.Text &= ". Records updated: " & UpdateBrands()
  'End Sub

  Public Shared Function UpdateBrands()
    Dim ds As DataSet
    Dim dr As DataRow
    Dim dblCount As Double
    dblCount = 0
    ds = sqlDB.doQueryDS("SELECT mstBrand.BrandID, mstBrand.strBrand, mstBrand.blnActive, mstPrincipal.strPrincipal, mstPrincipal.strFYStart, mstPrincipal.strFYEnd, mstCompany.strCompany, mstGroupingPrimary.strGroupingPrimary, mstGroupingDaily.strGroupingDaily, mstGroupingSecondary.strGroupingSecondary " & _
          "FROM mstBrand LEFT OUTER JOIN mstGroupingPrimary ON mstBrand.refGroupingPrimaryID = mstGroupingPrimary.GroupingPrimaryID LEFT OUTER JOIN mstPrincipal ON mstBrand.refPrincipalID = mstPrincipal.PrincipalID LEFT OUTER JOIN mstCompany ON mstBrand.refCompanyID = mstCompany.CompanyID LEFT OUTER JOIN mstGroupingSecondary ON mstBrand.refGroupingSecondaryID = mstGroupingSecondary.GroupingSecondaryID LEFT OUTER JOIN mstGroupingDaily ON mstBrand.refGroupingDailyID = mstGroupingDaily.GroupingDailyID")

    For Each dr In ds.Tables(0).Rows
      sqlDB.doQueryDS("UPDATE dwdProduct SET strBrand = '" & dr!strBrand & "', blnBrandActive = " & CInt(dr!blnActive) & ", strGroupingDaily = '" & dr!strGroupingDaily & "', strGroupingPrimary = '" & dr!strGroupingPrimary & "', strGroupingSecondary = '" & dr!strGroupingSecondary & "', dwdProduct.strPrincipal = '" & dr!strPrincipal & "', dwdProduct.strPrincipalFYStart = '" & dr!strFYStart & "', dwdProduct.strPrincipalFYEnd = '" & dr!strFYEnd & "', dwdProduct.strCompany = '" & dr!strCompany & "' WHERE refBrandID = " & dr!BrandID & " ")
      dblCount += 1

    Next

    Return dblCount
  End Function

  Protected Sub btnCMst_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim chk As CheckBox

    blnCMst = Not blnCMst

    For Each row As GridViewRow In gvCustomerMaster.Rows
      Try
        If row.Cells(row.Cells.Count - 1).Controls.Count > 2 Then
          chk = row.Cells(row.Cells.Count - 1).Controls(1)
          chk.Checked = blnCMst
        End If
      Catch
      End Try
    Next
  End Sub

  Protected Sub btnCDW_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim chk As CheckBox

    blnCDW = Not blnCDW

    For Each row As GridViewRow In gvCustomerDW.Rows
      Try
        If row.Cells(row.Cells.Count - 1).Controls.Count > 2 Then
          chk = row.Cells(row.Cells.Count - 1).Controls(1)
          chk.Checked = blnCDW
        End If
      Catch
      End Try
    Next
  End Sub

  Protected Sub btnPMst_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim chk As CheckBox

    blnPMst = Not blnPMst

    For Each row As GridViewRow In gvPMst.Rows
      Try
        If row.Cells(row.Cells.Count - 1).Controls.Count > 2 Then
          chk = row.Cells(row.Cells.Count - 1).Controls(1)
          chk.Checked = blnPMst
        End If
      Catch
      End Try
    Next
  End Sub

  Protected Sub btnPDW_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim chk As CheckBox

    blnPDW = Not blnPDW

    For Each row As GridViewRow In gvPDW.Rows
      Try
        If row.Cells(row.Cells.Count - 1).Controls.Count > 2 Then
          chk = row.Cells(row.Cells.Count - 1).Controls(1)
          chk.Checked = blnPDW
        End If
      Catch
      End Try
    Next
  End Sub

  Protected Sub btnCustomerMst_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnCustomerMst.Click
    Dim strID, comma As String
    Dim chk As CheckBox

    lblMessage.Text = ""
    For Each row As GridViewRow In gvCustomerMaster.Rows
      Try
        chk = row.Cells(row.Cells.Count - 1).Controls(1)
        If chk.Checked = True Then
          strID &= comma & "'" & sqlDB.CleanString(row.Cells(0).Text) & "'"
          comma = ","
        End If
      Catch
      End Try
    Next

    Try
      sqlDB.doQueryDS("INSERT INTO dwdCustomer (strCustomerNo, strCustomerName, strSnellChannel, strSnellArea, strSnellcwsireg, strSnellrepno, strSnellsupergroup, strSnellsupergroupName, strSnellgrading, refRepID, refRepBBID, refRepDlxID, refRepLuxID, strRepName, strRepNameBB, strRepNameDlx, strRepNameLux, strChannel, strSegment, strType1, strType2, strGroupName, strKeyAccount, blnPromotionalAccounts, blnActive, strCode, strBranch, strSalesOffice, strRegion, strSAPCode, strRegionCode, strRegionDesc, strRegionAccpacCode, strRegionDesc2) " & _
        "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_2.strRepName AS Expr1, mstRep_3.strRepName AS Expr2, mstRep_4.strRepName AS Expr3, mstChannel.strChannel,  " & _
        "mstSegment.strSegment, mstType1.strType1, mstType2.strType2, 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 " & _
        "FROM mstSnellsupergroup RIGHT OUTER JOIN " & _
        "mstRep RIGHT OUTER JOIN " & _
        "mstRep AS mstRep_4 RIGHT OUTER JOIN " & _
        "mstKeyAccount_Group LEFT OUTER JOIN " & _
        "mstKeyAccount ON mstKeyAccount_Group.refKeyAccountID = mstKeyAccount.KeyAccountID RIGHT OUTER JOIN " & _
        "mstCustomer LEFT OUTER JOIN " & _
        "mstChannel LEFT OUTER JOIN " & _
        "mstSegment ON mstChannel.refSegmentID = mstSegment.SegmentID ON mstCustomer.refChannelID = mstChannel.ChannelID ON  " & _
        "mstKeyAccount_Group.KeyAccount_GroupID = mstCustomer.refKeyAccount_GroupID ON  " & _
        "mstRep_4.RepID = mstCustomer.refRepLuxID LEFT OUTER JOIN " & _
        "mstRep AS mstRep_3 ON mstCustomer.refRepDlxID = mstRep_3.RepID LEFT OUTER JOIN " & _
        "mstRep AS mstRep_2 ON mstCustomer.refRepBBID = mstRep_2.RepID ON mstRep.RepID = mstCustomer.refRepID LEFT OUTER JOIN " & _
        "mstType2 ON mstCustomer.refType2ID = mstType2.Type2ID LEFT OUTER JOIN " & _
        "mstType1 ON mstCustomer.refType1ID = mstType1.Type1ID ON  " & _
        "mstSnellsupergroup.strSnellsupergroup = mstCustomer.strSnellsupergroup LEFT OUTER JOIN " & _
        "mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID " & _
        "WHERE mstCustomer.strCustomerNo IN (" & strID & ")")

      sdsCustomerMaster.FilterExpression = "1=1"
      sdsSummary.FilterExpression = "1=1"
    Catch ex As Exception
      sqlDB.WriteLog("Data Warehouse Integrity", "Error inserting into [dwdCustomer]", sqlDB.CleanString(ex.Message))
      lblMessage.Text = ex.Message
    End Try

  End Sub

  Protected Sub btnCustomerDW_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnCustomerDW.Click
    Dim strID, comma As String
    Dim chk As CheckBox

    lblMessage.Text = ""
    For Each row As GridViewRow In gvCustomerDW.Rows
      Try
        chk = row.Cells(row.Cells.Count - 1).Controls(1)
        If chk.Checked = True Then
          strID &= comma & "'" & sqlDB.CleanString(row.Cells(0).Text) & "'"
          comma = ","
        End If
      Catch
      End Try
    Next
      'v1.4.5 - 20160913 - Fixed logged In user & savings (i.e use session instead) - maanie
    Try
         sqlDB.doQueryDS("INSERT INTO mstCustomer(strCustomerNo, strCustomerName, strSnellChannel, strSnellArea, strSnellcwsireg, strSnellrepno, strSnellsupergroup, strSnellgrading, refRepID, refRepBBID, refRepDlxID, refRepLuxID, refBranchID, refKeyAccount_GroupID, refChannelID, refType1ID, refType2ID, blnActive, blnPromotionalAccounts, strLastUser, dtStamp) " & _
           "SELECT dwdCustomer_1.strCustomerNo, dwdCustomer_1.strCustomerName, dwdCustomer_1.strSnellChannel, dwdCustomer_1.strSnellArea,  " & _
           "dwdCustomer_1.strSnellcwsireg, dwdCustomer_1.strSnellrepno, dwdCustomer_1.strSnellsupergroup, dwdCustomer_1.strSnellgrading,  " & _
           "dwdCustomer_1.refRepID, dwdCustomer_1.refRepBBID, dwdCustomer_1.refRepDlxID, dwdCustomer_1.refRepLuxID, mstBranch.BranchID,  " & _
           "mstKeyAccount_Group.KeyAccount_GroupID, mstChannel.ChannelID, mstType1.Type1ID, mstType2.Type2ID, dwdCustomer_1.blnActive,  " & _
           "dwdCustomer_1.blnPromotionalAccounts, 'DW Integrity: " & Session.Item("User") & "' AS LastUser, '" & sqlDB.dbDate() & "' AS dtStamp " & _
           "FROM mstKeyAccount_Group RIGHT OUTER JOIN mstChannel RIGHT OUTER JOIN mstType2 RIGHT OUTER JOIN dwdCustomer AS dwdCustomer_1 ON mstType2.strType2 = dwdCustomer_1.strType2 LEFT OUTER JOIN " & _
           "mstType1 ON dwdCustomer_1.strType1 = mstType1.strType1 ON mstChannel.strChannel = dwdCustomer_1.strChannel ON mstKeyAccount_Group.strGroupName = dwdCustomer_1.strGroupName LEFT OUTER JOIN " & _
           "mstBranch ON dwdCustomer_1.strCode = mstBranch.strCode AND dwdCustomer_1.strBranch = mstBranch.strBranch LEFT OUTER JOIN mstSnellsupergroup ON dwdCustomer_1.strSnellsupergroup = mstSnellsupergroup.strSnellsupergroup " & _
           "WHERE dwdCustomer_1.strCustomerNo IN (" & strID & ")")
         sdsCustomerDW.FilterExpression = "1=1"
         sdsSummary.FilterExpression = "1=1"
      Catch ex As Exception
         sqlDB.WriteLog("Data Warehouse Integrity", "Error inserting into [mstCustomer]", sqlDB.CleanString(ex.Message))
         lblMessage.Text = ex.Message
      End Try
   End Sub

   Protected Sub btnPMstAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnPMstAdd.Click
      Dim strID, comma As String
      Dim chk As CheckBox

      lblMessage.Text = ""
      For Each row As GridViewRow In gvPMst.Rows
         Try
            chk = row.Cells(row.Cells.Count - 1).Controls(1)
            If chk.Checked = True Then
               strID &= comma & "'" & sqlDB.CleanString(row.Cells(0).Text) & "'"
               comma = ","
            End If
         Catch
         End Try
      Next

      Try
         sqlDB.doQueryDS("INSERT INTO dwdProduct(strProdCode, strProductDesc, intPack, intSize, dblPrice, strLongProdDesc, strType, strType2, intShortCode, strPriceSeg, strCategory, strCategory2, blnActive, dblFlat, cwsicat, strPrincipalMaterialRefNum, strPrincipalProdDesc, strPrincipal, strPrincipalFYStart, strPrincipalFYEnd, strCompany, refBrandID, strBrand, blnBrandActive, strGroupingDaily, strGroupingPrimary, strGroupingSecondary, strBottleBarcode, strCaseBarcode, strGiftboxBarcode, intDisplayOrder, strBrandVarietal) " & _
     "SELECT     mstProduct.strProdCode, mstProduct.strProductDesc, mstProduct.intPack, mstProduct.intSize, mstProduct.dblPrice, mstProduct.strLongProdDesc,  " & _
     "                      mstProduct.strType, mstProduct.strType2, mstProduct.intShortCode, mstProduct.strPriceSeg, mstProduct.strCategory, mstProduct.strCategory2,  " & _
     "                      mstProduct.blnActive, mstProduct.dblFlat, mstProduct.cwsicat, mstProduct.strPrincipalMaterialRefNum, mstProduct.strPrincipalProdDesc,  " & _
     "                      mstPrincipal.strPrincipal, mstPrincipal.strFYStart, mstPrincipal.strFYEnd, mstCompany.strCompany, mstBrand.BrandID, mstBrand.strBrand,  " & _
     "                      mstBrand.blnActive AS Expr1, isnull(mstGroupingDaily.strGroupingDaily,''), isnull(mstGroupingPrimary.strGroupingPrimary, ''),  " & _
     "                      isnull(mstGroupingSecondary.strGroupingSecondary, ''), mstProduct.strBottleBarcode, mstProduct.strCaseBarcode, mstProduct.strGiftboxBarcode,  " & _
     "                      mstProduct.intDisplayOrder, mstProduct.strBrandVarietal " & _
     "FROM         mstGroupingSecondary RIGHT OUTER JOIN " & _
     "                      mstBrand ON mstGroupingSecondary.GroupingSecondaryID = mstBrand.refGroupingSecondaryID LEFT OUTER JOIN " & _
     "                      mstGroupingPrimary ON mstBrand.refGroupingPrimaryID = mstGroupingPrimary.GroupingPrimaryID LEFT OUTER JOIN " & _
     "                      mstGroupingDaily ON mstBrand.refGroupingDailyID = mstGroupingDaily.GroupingDailyID LEFT OUTER JOIN " & _
     "                      mstCompany ON mstBrand.refCompanyID = mstCompany.CompanyID LEFT OUTER JOIN " & _
     "                      mstPrincipal ON mstBrand.refPrincipalID = mstPrincipal.PrincipalID RIGHT OUTER JOIN " & _
     "                      mstProduct ON mstBrand.BrandID = mstProduct.refBrandID " & _
     "WHERE mstProduct.strProdCode IN (" & strID & ")")
         sdsProductMst.FilterExpression = "1=1"
         sdsSummary.FilterExpression = "1=1"

      Catch ex As Exception
         sqlDB.WriteLog("Data Warehouse Integrity", "Error inserting into [dwdProduct]", sqlDB.CleanString(ex.Message))
         lblMessage.Text = ex.Message
      End Try
   End Sub

   Protected Sub btnPDWAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnPDWAdd.Click
      Dim strID, comma As String
      Dim chk As CheckBox

      lblMessage.Text = ""
      For Each row As GridViewRow In gvPDW.Rows
         Try
            chk = row.Cells(row.Cells.Count - 1).Controls(1)
            If chk.Checked = True Then
               strID &= comma & "'" & sqlDB.CleanString(row.Cells(0).Text) & "'"
               comma = ","
            End If
         Catch
         End Try
      Next

      Try
         sqlDB.doQueryDS("INSERT INTO mstProduct (strProdCode, refBrandID, strProductDesc, intPack, intSize, dblPrice, strLongProdDesc, strType, strType2, intShortCode, strPriceSeg, strCategory, strCategory2, blnActive, dblFlat, cwsicat, strPrincipalMaterialRefNum, strPrincipalProdDesc, strBottleBarcode, strCaseBarcode, strGiftboxBarcode, intDisplayOrder, strBrandVarietal, strLastUser, dtStamp) " & _
     "SELECT     strProdCode, refBrandID, strProductDesc, intPack, intSize, dblPrice, strLongProdDesc, strType, strType2, intShortCode, strPriceSeg, strCategory,  " & _
     "                      strCategory2, blnActive, dblFlat, cwsicat, strPrincipalMaterialRefNum, strPrincipalProdDesc, strBottleBarcode, strCaseBarcode, strGiftboxBarcode,  " & _
     "                      intDisplayOrder, strBrandVarietal, 'DW Integrity: " & Session.Item("User") & "' AS LastUser, '" & sqlDB.dbDate() & "' AS dtStamp " & _
     "FROM         dwdProduct WHERE dwdProduct.strProdCode IN (" & strID & ")")
         sdsProductDW.FilterExpression = "1=1"
         sdsSummary.FilterExpression = "1=1"
      Catch ex As Exception
         sqlDB.WriteLog("Data Warehouse Integrity", "Error inserting into [mstProduct]", sqlDB.CleanString(ex.Message))
         lblMessage.Text = ex.Message

      End Try
   End Sub
End Class