Imports Superbowl.sqlDB
Imports System.Web.Configuration

Public Class RepQF
	Private arrRepQF(,,) As Rep
	Private RepArray(), PeriodArray(), PeriodArrayNew() As String
	Private BrandArray() As Integer
	Private db As New sqlDB()
	Private ds As DataSet
	Private dr As DataRow
	Private Complete As Boolean
  Public StartDate, EndDate, Message As String

	Structure Rep
		Public dblPerc As Double
		Public dblSales As Double
		Public dblQF As Double
	End Structure

  Public Sub New()
    StartDate = Date.Today.ToString("yyyyMM")
    EndDate = Date.Today.ToString("yyyyMM")
    Message = ""
  End Sub

   'THIS FUNCTION IS NO LONGER USED
  Public Function Run(ByVal strStartDate As String, ByVal strEndDate As String) As Boolean
    StartDate = strStartDate
    EndDate = strEndDate
    Return Me.Run()
  End Function

	Public Function Run() As Boolean
		Message = ""
		Complete = False
		'pop arr
		'get %
		'delete existing qfs
		'insert new qfs
		If Me.Pop Then

			If Me.Calulate Then

				If Me.Delete Then

					If Me.Insert Then
						Complete = True
						Return True
					Else
						Return False
					End If

				Else
					Return False
				End If

			Else
				Return False
			End If

		Else
			Return False
		End If

	End Function

	Private Function Pop() As Boolean
		Dim b, p, r As Integer

		ds = db.doQueryDS("SELECT BrandID FROM mstBrand WHERE blnActive = 1")
		ReDim BrandArray(ds.Tables(0).Rows.Count - 1)
		For b = 0 To ds.Tables(0).Rows.Count - 1
			BrandArray(b) = CInt(ds.Tables(0).Rows(b)!BrandID)
		Next

		ds = db.doQueryDS("select '' as strPeriod Union SELECT strPeriod FROM mstPeriod WHERE strPeriod >= '" & Me.StartDate & "' AND strPeriod <= '" & Me.EndDate & "' ORDER BY strPeriod  ")
		ReDim PeriodArrayNew(ds.Tables(0).Rows.Count - 1)
		For p = 0 To ds.Tables(0).Rows.Count - 1
			PeriodArrayNew(p) = ds.Tables(0).Rows(p)!strPeriod
		Next

		ds = db.doQueryDS("select '' as strPeriod Union SELECT strPeriod FROM mstPeriod WHERE strPeriod >= '" & CInt(Me.StartDate) - 100 & "' AND strPeriod <= '" & CInt(Me.EndDate) - 100 & "' ORDER BY strPeriod  ")
		ReDim PeriodArray(ds.Tables(0).Rows.Count - 1)
		For p = 0 To ds.Tables(0).Rows.Count - 1
			PeriodArray(p) = ds.Tables(0).Rows(p)!strPeriod							'min 12 + (1 x strPeriod='0')
		Next

		ds = db.doQueryDS("SELECT RepID FROM mstRep WHERE blnGenerateQF = 1 ORDER BY RepID")
		ReDim RepArray(ds.Tables(0).Rows.Count - 1)
		For r = 0 To ds.Tables(0).Rows.Count - 1
			RepArray(r) = ds.Tables(0).Rows(r)!RepID
		Next

		ReDim arrRepQF(b, p, r)

		Try
			ds = db.doQueryDS(Me.get_Sale_Rep_Totals_by_Period(CInt(Me.StartDate) - 100, CInt(Me.EndDate) - 100))
			For Each dr In ds.Tables(0).Rows
				Try

					b = Array.IndexOf(BrandArray, CInt(dr!BrandID))
					p = 0 'Array.IndexOf(PeriodArray, dr!strPeriod)
					r = Array.IndexOf(RepArray, dr!RepID)

					arrRepQF(b, p, r).dblSales = Math.Round(db.Nz(dr!dblSales9L, 0.0), 4, MidpointRounding.AwayFromZero)
				Catch ex As Exception
					Message &= "||" & ex.Message

				End Try
			Next

			Return True
		Catch ex As Exception
			Message &= "||" & ex.Message
			Return False
		End Try

	End Function

	Private Function Calulate() As Boolean
		Dim t, p, b, r As Double
		Try
			ds = db.doQueryDS(Me.get_Sales_Totals_by_Period(CInt(Me.StartDate) - 100, CInt(Me.EndDate) - 100))
			For Each dr In ds.Tables(0).Rows
				t = db.Nz(dr!dblSales9L, 0.0)
				b = Array.IndexOf(BrandArray, dr!BrandID)
				p = 0 'Array.IndexOf(PeriodArray, dr!strPeriod)
				For r = 0 To RepArray.Length - 1 And t <> 0
					Try
						arrRepQF(b, p, r).dblPerc = Math.Round(arrRepQF(b, p, r).dblSales / t, 4, MidpointRounding.AwayFromZero)
					Catch ex As Exception																								 'round 4 dec places: .Perc = 0.0875 == 8.75%
						Message &= "||" & ex.Message
					End Try
				Next						 'i

			Next					 'dr

			ds = db.doQueryDS(Me.get_Brand_Totals_by_Period(Me.StartDate, Me.EndDate))			 'brand qf per period forward
			For Each dr In ds.Tables(0).Rows
				t = db.Nz(dr!dblQF, 0.0)
				b = Array.IndexOf(BrandArray, dr!BrandID)
				p = Array.IndexOf(PeriodArrayNew, dr!strPeriod)				'periodNew!
				For r = 0 To RepArray.Length - 1
					Try
						arrRepQF(b, p, r).dblQF = Math.Round(arrRepQF(b, 0, r).dblPerc * t, 4, MidpointRounding.AwayFromZero)
						arrRepQF(b, p, r).dblPerc = arrRepQF(b, 0, r).dblPerc
					Catch ex As Exception
						Message &= "||" & ex.Message
					End Try
				Next						 'i

			Next

			Return True
		Catch ex As Exception
			Message &= "||" & ex.Message
			Return False
		End Try

	End Function

	Private Function Delete() As Boolean
		Try
      db.doQueryDS("DELETE FROM dwfQF WHERE strPeriod >='" & Me.StartDate & "' And strPeriod <='" & Me.EndDate & "' ")

      Return True
    Catch ex As Exception
      Message &= "||" & ex.Message
      Return False
    End Try

  End Function

   Private Function Insert() As Boolean
      Dim b, ib, ip, ir As Integer
      Dim p, r As String
      Dim iRepQF As Rep

      Try

         For ip = 1 To PeriodArrayNew.Length - 1                  'skip the 1rst period=''
            p = PeriodArrayNew(ip)
            For Each b In BrandArray
               For Each r In RepArray
                  Try
                     ib = Array.IndexOf(BrandArray, b)
                     ir = Array.IndexOf(RepArray, r)
                     iRepQF = arrRepQF(ib, ip, ir)
                     If iRepQF.dblPerc > 0 Or iRepQF.dblQF > 0 Then
                        db.doQueryDS(Me.insert_RepQF(b, p, r, Math.Round(iRepQF.dblQF, 4, MidpointRounding.AwayFromZero), Math.Round(iRepQF.dblPerc, 4, MidpointRounding.AwayFromZero)))
                     End If

                  Catch ex As Exception
                     Message &= "||" & ex.Message
                  End Try
               Next
            Next
         Next

         db.doQueryDS("DELETE FROM dwfQF WHERE strPeriod = '' OR refBrandID = 0 OR refRepID = ''")

         Return True
      Catch ex As Exception
         Return False
      End Try

   End Function

  '	////	SQL Functions

  Public Function get_Brand_Totals_by_Period(ByVal strStartDate As String, ByVal strEndDate As String) As String
    Return "SELECT mstBrand.BrandID, mstBrand.strBrand, Sum(stkProductPeriod.dblQF) AS dblQF, stkProductPeriod.mstPeriod as strPeriod " & _
     "FROM (stkProductPeriod INNER JOIN mstProduct ON stkProductPeriod.mstProdCode + 'F' = mstProduct.strProdCode) INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID " & _
     "WHERE (mstBrand.blnActive = 1) " & _
     "GROUP BY mstBrand.BrandID, mstBrand.strBrand, stkProductPeriod.mstPeriod " & _
     "HAVING stkProductPeriod.mstPeriod >= '" & strStartDate & "' And stkProductPeriod.mstPeriod <= '" & strEndDate & "' " & _
     "ORDER BY strBrand, mstPeriod"
  End Function

  Public Function get_Principal()
    Return "SELECT DISTINCT mstPrincipal.PrincipalID, mstPrincipal.strPrincipal, mstPrincipal.strFYStart, mstPrincipal.strFYEnd " & _
        "FROM mstPrincipal INNER JOIN mstBrand ON mstPrincipal.PrincipalID = mstBrand.refPrincipalID " & _
        "WHERE mstBrand.blnActive = 1"
  End Function

  Public Function get_Sales_Totals_by_Period(ByVal strStartDate As String, ByVal strEndDate As String) As String
    Return "SELECT mstBrand.BrandID, mstBrand.strBrand, ROUND(SUM(dwfSales.dbl9l), 4) AS dblSales9L " & _
     "FROM ((mstCustomer INNER JOIN (dwfSales INNER JOIN (mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID) ON dwfSales.refProdCode = mstProduct.strProdCode) ON mstCustomer.strCustomerNo = dwfSales.refCustomerNo) INNER JOIN mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID) INNER JOIN mstRep ON mstCustomer.refRepID = mstRep.RepID " & _
     " WHERE (mstRep.blnGenerateQF = 1) AND (mstBrand.blnActive = 1) AND refPeriod >= '" & strStartDate & "' And refPeriod <= '" & strEndDate & "' " & _
     "GROUP BY mstBrand.BrandID, mstBrand.strBrand " & _
     "ORDER BY mstBrand.strBrand"
  End Function

  Public Function get_Sales_Totals_by_Period_Union(ByVal strStartDate As String, ByVal strEndDate As String, ByVal refPrincipalID As Integer, ByVal Union As String) As String

    Return Union & " SELECT mstBrand.BrandID, mstBrand.strBrand, ROUND(SUM(dwfSales.dbl9l), 4) AS dblSales9L " & _
     "FROM ((mstCustomer INNER JOIN (dwfSales INNER JOIN (mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID) ON dwfSales.refProdCode = mstProduct.strProdCode) ON mstCustomer.strCustomerNo = dwfSales.refCustomerNo) INNER JOIN mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID) INNER JOIN mstRep ON mstCustomer.refRepID = mstRep.RepID " & _
     "WHERE (mstRep.blnGenerateQF = 1) AND (mstBrand.blnActive = 1) AND refPeriod >= '" & strStartDate & "' And refPeriod <= '" & strEndDate & "' AND mstBrand.refPrincipalID = " & refPrincipalID & _
     "GROUP BY mstBrand.BrandID, mstBrand.strBrand "
    '"ORDER BY mstBrand.strBrand"
  End Function

  Public Function get_Sale_Rep_Totals_by_Period(ByVal strStartDate As String, ByVal strEndDate As String) As String
    Return "SELECT mstBrand.BrandID, mstBrand.strBrand, mstRep.RepID, mstRep.strRepName, ROUND(SUM(dwfSales.dbl9l), 4) AS dblSales9L " & _
     "FROM ((mstCustomer INNER JOIN (dwfSales INNER JOIN (mstProduct INNER JOIN mstBrand ON mstProduct.refBrandID = mstBrand.BrandID) ON dwfSales.refProdCode = mstProduct.strProdCode) ON mstCustomer.strCustomerNo = dwfSales.refCustomerNo) INNER JOIN mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID) INNER JOIN mstRep ON mstCustomer.refRepID = mstRep.RepID " & _
     "WHERE (mstRep.blnGenerateQF = 1) AND (mstBrand.blnActive = 1) AND refPeriod >= '" & strStartDate & "' And refPeriod <= '" & strEndDate & "' " & _
     "GROUP BY mstBrand.BrandID, mstBrand.strBrand, mstRep.RepID, mstRep.strRepName " & _
     "ORDER BY mstBrand.strBrand, mstRep.strRepName"
  End Function

   Function insert_RepQF(ByVal BrandID As Integer, ByVal strPeriod As String, ByVal RepID As String, ByVal dblQF9L As Double, ByVal dblPerc As Double) As String
      Return "INSERT INTO dwfQF (strPeriod, refBrandID, refRepID, dblBudget9l, dblPercentage, strLastUser, dtStamp) " & _
      "VALUES ('" & strPeriod & "', " & BrandID & ", '" & RepID & "', " & dblQF9L & ", " & dblPerc & ", '" & WebConfigurationManager.AppSettings("UNAME") & "','" & dbDate() & "')"
   End Function
End Class