Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Data
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Windows.Forms

Imports FarPoint.Win.Spread
Imports Spread.Common.DataStore.DataSourceEntities
Imports FarPoint.Win
Imports FarPoint.Win.Spread.Model
Imports Spread.Common.Features.Spread.Common.Features.Sample

Partial Public Class ConditionalRules
    Inherits BaseSampleControl
    Public Sub New()
        InitializeComponent()
        _cbOperator.DataSource = [Enum].GetNames(GetType(ComparisonOperator))

        'Set Data
        Dim rnd = New Random()
        fpSpread1_Sheet1.SetClipValue(0, 0, 1, 5, "Student ID" & vbTab & "Student Name" & vbTab & "English" & vbTab & "Mathematics" & vbTab & "Science")
        Dim students = New StudentDashboardDataSource().GetAllStudents()
        For Each student As Object In students
            Dim id = student.ID
            fpSpread1_Sheet1.Cells(id, 0).Value = id
            fpSpread1_Sheet1.Cells(id, 1).Value = student.Name
            fpSpread1_Sheet1.Cells(id, 2).Value = If(rnd.NextDouble() > 0.3, rnd.[Next](40, 99), rnd.[Next](10, 40))
            fpSpread1_Sheet1.Cells(id, 3).Value = If(rnd.NextDouble() > 0.3, rnd.[Next](40, 99), rnd.[Next](10, 40))
            fpSpread1_Sheet1.Cells(id, 4).Value = If(rnd.NextDouble() > 0.3, rnd.[Next](40, 99), rnd.[Next](10, 40))
        Next

        fpSpread1_Sheet1.Rows.[Default].Height = 21
        fpSpread1_Sheet1.Columns.[Default].Width = 120
        fpSpread1_Sheet1.Cells(0, 0, students.Count, 4).Border = New LineBorder(Color.Gray)
        fpSpread1_Sheet1.Cells(0, 0, 0, 4).BackColor = Color.Black
        fpSpread1_Sheet1.Cells(0, 0, 0, 4).ForeColor = Color.White
        fpSpread1_Sheet1.Cells(0, 0, 0, 4).Font = New Font("Segoe UI", 10, FontStyle.Bold)

        'Set Conditional Rules
        Dim lowMarksRule = New BetweenValuesConditionalFormattingRule(False, 1, False, 33, False)
        lowMarksRule.BackColor = Color.FromArgb(100, Color.LightPink)
        lowMarksRule.ForeColor = Color.Red

        Dim avgMarksRule = New BetweenValuesConditionalFormattingRule(False, 34, False, 75, False)
        avgMarksRule.BackColor = Color.FromArgb(50, Color.LightGreen)
        avgMarksRule.ForeColor = Color.DarkGreen

        Dim highMarksRule = New BetweenValuesConditionalFormattingRule(False, 76, False, 100, False)
        highMarksRule.BackColor = Color.FromArgb(100, Color.LightBlue)
        highMarksRule.ForeColor = Color.DarkBlue

        fpSpread1_Sheet1.SetConditionalFormatting(1, 2, students.Count, 3, lowMarksRule, avgMarksRule,
            highMarksRule)
    End Sub

    Private Sub _bAddRule_Click(sender As Object, e As EventArgs)
        If _cbOperator.SelectedIndex >= 0 Then
            Dim compareOprtr = CType([Enum].Parse(GetType(ComparisonOperator), _cbOperator.SelectedItem.ToString()), ComparisonOperator)
            Dim range = fpSpread1_Sheet1.GetSelection(0)
            If range IsNot Nothing Then
                FarPoint.Win.Spread.ConditionalFormat.ShowConditionalFormatDialog(Me.ParentForm, fpSpread1, compareOprtr, range)
            Else
                Dim activeCell = fpSpread1_Sheet1.ActiveCell
                FarPoint.Win.Spread.ConditionalFormat.ShowConditionalFormatDialog(Me.ParentForm, fpSpread1, compareOprtr, New CellRange(activeCell.Row.Index, activeCell.Column.Index, 1, 1))
            End If
        End If
    End Sub

    Private Sub _bClearRule_Click(sender As Object, e As EventArgs)
        Dim range = fpSpread1_Sheet1.GetSelection(0)
        If range IsNot Nothing Then
            fpSpread1_Sheet1.ClearConditionalFormating(range.Row, range.Column, range.RowCount, range.ColumnCount)
        End If
    End Sub

    Private Sub _bViewRules_Click(sender As Object, e As EventArgs)
        ConditionalFormattingUtils.ShowConditionalFormattingManagerForm(Me.ParentForm, fpSpread1)
    End Sub
End Class

'
'
'

Partial Class ConditionalRules
	''' <summary> 
	''' Required designer variable.
	''' </summary>
	Private components As System.ComponentModel.IContainer = Nothing

    ''' <summary> 
    ''' Clean up any resources being used.
    ''' </summary>
    ''' <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
    Protected Shadows Sub Dispose(disposing As Boolean)
        If disposing AndAlso (components IsNot Nothing) Then
            components.Dispose()
        End If
        MyBase.Dispose(disposing)
    End Sub

#Region "Component Designer generated code"

    ''' <summary> 
    ''' Required method for Designer support - do not modify 
    ''' the contents of this method with the code editor.
    ''' </summary>
    Private Sub InitializeComponent()
		Me.fpSpread1 = New FarPoint.Win.Spread.FpSpread()
		Me.fpSpread1_Sheet1 = New FarPoint.Win.Spread.SheetView()
		Me.splitContainer1 = New System.Windows.Forms.SplitContainer()
		Me._lbOperator = New System.Windows.Forms.Label()
		Me._cbOperator = New System.Windows.Forms.ComboBox()
		Me._bAddRule = New System.Windows.Forms.Button()
		Me._bClearRule = New System.Windows.Forms.Button()
		Me._bViewRules = New System.Windows.Forms.Button()
		Me.pnlMain.SuspendLayout()
		Me.pnlControls.SuspendLayout()
		DirectCast(Me.fpSpread1, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me.fpSpread1_Sheet1, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me.splitContainer1, System.ComponentModel.ISupportInitialize).BeginInit()
		Me.splitContainer1.Panel1.SuspendLayout()
		Me.splitContainer1.Panel2.SuspendLayout()
		Me.splitContainer1.SuspendLayout()
		Me.SuspendLayout()
		' 
		' pnlMain
		' 
		Me.pnlMain.Controls.Add(Me.fpSpread1)
		' 
		' pnlControls
		' 
		Me.pnlControls.Controls.Add(Me._bClearRule)
		Me.pnlControls.Controls.Add(Me._bAddRule)
		Me.pnlControls.Controls.Add(Me._bViewRules)
		Me.pnlControls.Controls.Add(Me.splitContainer1)
		' 
		' fpSpread1
		' 
		Me.fpSpread1.AccessibleDescription = ""
		Me.fpSpread1.Dock = System.Windows.Forms.DockStyle.Fill
		Me.fpSpread1.Location = New System.Drawing.Point(0, 0)
		Me.fpSpread1.Name = "fpSpread1"
		Me.fpSpread1.Sheets.AddRange(New FarPoint.Win.Spread.SheetView() {Me.fpSpread1_Sheet1})
		Me.fpSpread1.Size = New System.Drawing.Size(477, 487)
		Me.fpSpread1.TabIndex = 0
		' 
		' fpSpread1_Sheet1
		' 
		Me.fpSpread1_Sheet1.Reset()
		Me.fpSpread1_Sheet1.SheetName = "Sheet1"
		' Formulas and custom names must be loaded with R1C1 reference style
		Me.fpSpread1_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
		Me.fpSpread1_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
		' 
		' splitContainer1
		' 
		Me.splitContainer1.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
		Me.splitContainer1.Location = New System.Drawing.Point(21, 15)
		Me.splitContainer1.Name = "splitContainer1"
		' 
		' splitContainer1.Panel1
		' 
		Me.splitContainer1.Panel1.Controls.Add(Me._lbOperator)
		' 
		' splitContainer1.Panel2
		' 
		Me.splitContainer1.Panel2.Controls.Add(Me._cbOperator)
		Me.splitContainer1.Size = New System.Drawing.Size(122, 36)
		Me.splitContainer1.SplitterDistance = 40
		Me.splitContainer1.TabIndex = 0
		' 
		' _lbOperator
		' 
		Me._lbOperator.AutoSize = True
		Me._lbOperator.Dock = System.Windows.Forms.DockStyle.Fill
		Me._lbOperator.Location = New System.Drawing.Point(0, 0)
		Me._lbOperator.Name = "_lbOperator"
		Me._lbOperator.Size = New System.Drawing.Size(54, 13)
		Me._lbOperator.TabIndex = 0
		Me._lbOperator.Text = "Operator :"
		' 
		' _cbOperator
		' 
		Me._cbOperator.Dock = System.Windows.Forms.DockStyle.Fill
		Me._cbOperator.FormattingEnabled = True
		Me._cbOperator.Location = New System.Drawing.Point(0, 0)
		Me._cbOperator.Name = "_cbOperator"
		Me._cbOperator.Size = New System.Drawing.Size(78, 21)
		Me._cbOperator.TabIndex = 0
		' 
		' _bAddRule
		' 
		Me._bAddRule.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
		Me._bAddRule.Location = New System.Drawing.Point(21, 57)
		Me._bAddRule.Name = "_bAddRule"
		Me._bAddRule.Size = New System.Drawing.Size(122, 23)
		Me._bAddRule.TabIndex = 1
		Me._bAddRule.Text = "Add Conditional Rule"
		Me._bAddRule.UseVisualStyleBackColor = True
		AddHandler Me._bAddRule.Click, New System.EventHandler(AddressOf Me._bAddRule_Click)
		' 
		' _bClearRule
		' 
		Me._bClearRule.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
		Me._bClearRule.Location = New System.Drawing.Point(21, 97)
		Me._bClearRule.Name = "_bClearRule"
		Me._bClearRule.Size = New System.Drawing.Size(122, 23)
		Me._bClearRule.TabIndex = 2
		Me._bClearRule.Text = "Clear Conditional Rule"
		Me._bClearRule.UseVisualStyleBackColor = True
		AddHandler Me._bClearRule.Click, New System.EventHandler(AddressOf Me._bClearRule_Click)
		' 
		' _bViewRules
		' 
		Me._bViewRules.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
		Me._bViewRules.Location = New System.Drawing.Point(21, 137)
		Me._bViewRules.Name = "_bClearRule"
		Me._bViewRules.Size = New System.Drawing.Size(122, 23)
		Me._bViewRules.TabIndex = 3
		Me._bViewRules.Text = "Edit Rules for Selection"
		Me._bViewRules.UseVisualStyleBackColor = True
		AddHandler Me._bViewRules.Click, New System.EventHandler(AddressOf Me._bViewRules_Click)
		' 
		' ConditionalRules
		' 
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.Name = "Conditional Rules"
		Me.pnlMain.ResumeLayout(False)
		Me.pnlControls.ResumeLayout(False)
		DirectCast(Me.fpSpread1, System.ComponentModel.ISupportInitialize).EndInit()
		DirectCast(Me.fpSpread1_Sheet1, System.ComponentModel.ISupportInitialize).EndInit()
		Me.splitContainer1.Panel1.ResumeLayout(False)
		Me.splitContainer1.Panel1.PerformLayout()
		Me.splitContainer1.Panel2.ResumeLayout(False)
		DirectCast(Me.splitContainer1, System.ComponentModel.ISupportInitialize).EndInit()
		Me.splitContainer1.ResumeLayout(False)
		Me.ResumeLayout(False)

	End Sub

	#End Region

	Private fpSpread1 As FarPoint.Win.Spread.FpSpread
	Private fpSpread1_Sheet1 As FarPoint.Win.Spread.SheetView
	Private _bClearRule As System.Windows.Forms.Button
	Private _bAddRule As System.Windows.Forms.Button
	Private _bViewRules As System.Windows.Forms.Button
	Private splitContainer1 As System.Windows.Forms.SplitContainer
	Private _lbOperator As System.Windows.Forms.Label
	Private _cbOperator As System.Windows.Forms.ComboBox
End Class
