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 Spread.Common.Features.Spread.Common.Features.Sample
Imports FarPoint.Win.Spread
Imports FarPoint.Win.Spread.CellType
Imports FarPoint.Win
Imports GrapeCity.Spreadsheet

Public Partial Class CellValidators
	Inherits BaseSampleControl
	Private _lNamesSheetDesc As Label, _lSchedulesSheetDesc As Label
	Public Sub New()
		InitializeComponent()
		Name = "Cell Validators"
		SetDescription()
		'Common Style Setup
		fpSpread.ActiveSheetIndex = 0
		_sheetNames.ColumnHeader.Visible = False
		_sheetNames.RowHeader.Visible = False
		_sheetSched.ColumnHeader.Visible = False
		_sheetSched.RowHeader.Visible = False
		_sheetSched.AddCustomName("SchedNames", "Names!$C$2:$C$100", 0, 0, False, "")
		_sheetSched.AddCustomName("SchedDate", "SampleValidators!$F$4", 0, 0, False, "")

		'Sheet Introduction
		_sheetIntro.Rows(1).Height = InlineAssignHelper(_sheetIntro.Rows(10).Height, 50)
		_sheetIntro.Cells(1, 0).Font = InlineAssignHelper(_sheetIntro.Cells(10, 0).Font, New System.Drawing.Font("Segoi UI", 10, System.Drawing.FontStyle.Bold))
		_sheetIntro.Cells(1, 0).HorizontalAlignment = InlineAssignHelper(_sheetIntro.Cells(10, 0).HorizontalAlignment, CellHorizontalAlignment.Center)
		_sheetIntro.Cells(1, 0).VerticalAlignment = InlineAssignHelper(_sheetIntro.Cells(10, 0).VerticalAlignment, CellVerticalAlignment.Center)

		'RangeValidator
		_sheetIntro.AddSpanCell(1, 0, 1, 10)
		_sheetIntro.Cells(1, 0).Text = "The range below has a RangeValidator applied to accept values between 1 and 5"
		Dim lineNotify As New LineNotify()
        Dim rangeValidator As New RangeValidator() With {
             .MinValue = 1,
             .MaxValue = 5
        }
        ApplyCellValidator(_sheetIntro, 2, 1, 8, 5, rangeValidator, _
			lineNotify)
		_sheetIntro.Cells(2, 1, 9, 5).BackColor = System.Drawing.Color.FromArgb(100, System.Drawing.Color.SandyBrown)
		


		'IncludeListValidator
		_sheetIntro.AddSpanCell(10, 0, 1, 10)
		_sheetIntro.Cells(10, 0).Text = "The range below has an IncludeListValidator applied to accept values 'hi' or 'hello' only"
        Dim tipNotify As New TipNotify() With {
             .ToolTipIcon = ToolTipIcon.[Error],
             .ToolTipTitle = "Wrong Value!",
             .ToolTipText = "This cell accepts only 'hi' or 'hello'"
        }
        Dim listValidator As New IncludeListValidator() With {
             .Candidates = New String() {"hi", "hello"}
        }
        ApplyCellValidator(_sheetIntro, 11, 1, 8, 5, listValidator, _
			tipNotify, lineNotify)
		_sheetIntro.Cells(11, 1, 18, 5).BackColor = System.Drawing.Color.FromArgb(100, System.Drawing.Color.LightBlue)

		'Sheet Names
		SetSheetStyle(_sheetNames)
		_sheetNames.SetClip(0, 0, 1, 3, "Employee" & vbTab & "Name & Date" & vbTab & "Show Name")
		_sheetNames.SetClip(1, 0, 11, 1, "Patricia Gabel " & vbLf & "Pail Molden " & vbLf & "Lyle Kier " & vbLf & "Chandra Ordidge" & vbLf & "Pennie Murdie " & vbLf & "Kelci Kopelman " & vbLf & "Teirtza Infantino " & vbLf & "Jamil Vasilkov " & vbLf & "Tine Thickens " & vbLf & "Dorisa Tompion " & vbLf & "Belle McGlaud")
		_sheetNames.Cells(1, 1, 99, 1).Formula = "IF(A2<>"""",A2&SchedDate,"""")"
		_sheetNames.Cells(1, 2, 99, 2).Formula = "IF(COUNTIF(SampleValidators!C:C,B2)>0,"""",Names!A2)"
		_sheetNames.FrozenRowCount = 1

		'Sheet Schedules
		SetSheetStyle(_sheetSched)
		_sheetSched.SetClip(0, 0, 1, 3, "Work Date" & vbTab & "Employee" & vbTab & "Name & Date")
		_sheetSched.Cells(1, 2, 99, 2).Formula = "IF(B2<>"""",B2&A2,"""")"
		_sheetSched.Cells("E4").Text = "Scheduling Date-->"
		_sheetSched.Cells("F4").Text = DateTime.Now.ToShortDateString()
		_sheetSched.Cells("E4:F4").BackColor = System.Drawing.Color.LightYellow

		For i As Integer = 1 To 10
			_sheetSched.Cells(i, 0).Text = DateTime.Now.AddDays(i - 1).ToShortDateString()
		Next
		For i As Integer = 1 To 99
			If _sheetSched.Cells(i, 0).Text = _sheetSched.Cells("F4").Text Then
				_sheetSched.Validations.Clear(i, 1, i, 1)
                Dim validation As IValidation = _sheetSched.Cells(i, 1).Validation.Add(DataValidationType.List, DataValidationErrorStyle.Warning, DataValidationOperator.Equal, "=SchedNames", Nothing)
                validation.IgnoreBlank = True
			End If
		Next
		_sheetSched.FrozenRowCount = 1
		AddHandler _sheetSched.CellChanged, AddressOf _sheetSched_CellChanged

		AddValidators()
		AddHandler fpSpread.ActiveSheetChanged, AddressOf FpSpread_ActiveSheetChanged
	End Sub

	Private Sub FpSpread_ActiveSheetChanged(sender As Object, e As EventArgs)
		If fpSpread.ActiveSheet.SheetName = "Names" Then
			_lNamesSheetDesc.Visible = True
			_lSchedulesSheetDesc.Visible = False
			_pnlButtons.Visible = False
		ElseIf fpSpread.ActiveSheet.SheetName = "SampleValidators" Then
			_lNamesSheetDesc.Visible = False
			_lSchedulesSheetDesc.Visible = True
			_pnlButtons.Visible = False
		Else
			_lNamesSheetDesc.Visible = False
			_lSchedulesSheetDesc.Visible = False
			_pnlButtons.Visible = True
		End If
	End Sub

	Private Sub SetDescription()
        _lNamesSheetDesc = New Label() With {
             .Dock = DockStyle.Fill,
             .Visible = False
        }
        _lNamesSheetDesc.Text = "TextLength Validator is set for cells in first column allowing 2 to 20 char" & "acters in name. If cell text(name) is shorter or longer, notify will be displayed." & vbCr & vbLf & vbCr & vbLf & "Guidelines:" & vbCr & vbLf & "When you want to schedule for 1 day, do as follow:" & vbCr & vbLf & "1. Enter the date you want to schedule for in F4 cell(colored yellow) of SampleValidators sheet." & vbCr & vbLf & "2. The available employees for that day will appear in the dropdown list of 2nd column of SampleValidators sheet.On selecting an employee, his/her name will be removed from the list." & vbCr & vbLf & "3. You can add additional employees to the Names sheet and additional work days in SampleValidators sheet." & vbCr & vbLf

        _lSchedulesSheetDesc = New Label() With {
             .Dock = DockStyle.Fill,
             .Visible = False
        }
        _lSchedulesSheetDesc.Text = "Scheduling Date Cell : " & vbLf & "This cell has been named (SchedDate) so it is easier to use this cell in formulas on the worksheet." & vbLf & "Enter date, you wish to schedule for, in this cell." & vbCr & vbLf & vbCr & vbLf & "1. First Column uses RequiredType Validator to allow only dates" & vbCr & vbLf & "2. Cell F4 uses a RangeValidator to allow scheduling dates to be in the range of next 100 days." & vbCr & vbLf

		Me.pnlControls.Controls.Add(_lNamesSheetDesc)
		Me.pnlControls.Controls.Add(_lSchedulesSheetDesc)
	End Sub

	Private Sub SetSheetStyle(sheet As SheetView)
		sheet.RowCount = 100
		sheet.ColumnCount = 6
		sheet.Columns(0, 5).Width = 120

		sheet.Rows(0).Height = 30
		sheet.Cells(0, 0, 0, 2).BackColor = System.Drawing.Color.Purple
		sheet.Cells(0, 0, 0, 2).ForeColor = System.Drawing.Color.White
		sheet.Cells(0, 0, 0, 2).Font = New System.Drawing.Font("Segoi UI", 10, System.Drawing.FontStyle.Bold)
		sheet.Cells(0, 0, 99, 2).Border = New LineBorder(System.Drawing.Color.Black, 1, True, True, True, True)
	End Sub
	Private Sub _sheetSched_CellChanged(sender As Object, e As SheetViewEventArgs)
		If (e.Column = 0) OrElse ((e.Column = 5) AndAlso (e.Row = 3)) Then
			For i As Integer = 1 To 99
				If _sheetSched.Cells(i, 0).Text = _sheetSched.Cells(3, 5).Text Then
					_sheetSched.Validations.Clear(i, 1, i, 1)

                    Dim validation As IValidation = _sheetSched.Cells(i, 1).Validation.Add(DataValidationType.List, DataValidationErrorStyle.Warning, DataValidationOperator.Equal, "=SchedNames", Nothing)
                    validation.IgnoreBlank = True
				End If
			Next
		End If
	End Sub
	Private Sub AddValidators()
		Dim lineNotify As New LineNotify()

        'Length validator for Names
        Dim nameLengthValidator As New TextLengthValidator() With {
             .MinimumLength = 2,
             .MaximumLength = 20
        }
        Dim msgLengthNotify As New MessageBoxNotify() With {
             .Message = "Please make sure that Name should have 2-20 characters",
             .Caption = "Invalid Length",
             .Icon = MessageBoxIcon.[Stop],
             .DoActionReason = ValidateReasons.CancelEdit Or ValidateReasons.EndEdit
        }
        ApplyCellValidator(_sheetNames, 1, 0, 99, 1, nameLengthValidator, _
			lineNotify, msgLengthNotify)

        'RequiredType validator for DateTimes
        Dim requiredTypValidator As New RequiredTypeValidator() With {
             .RequiredType = GetType(DateTime)
        }
        Dim msgRequiredTypNotify As New MessageBoxNotify() With {
             .Message = "Please enter Date only",
             .Caption = "Invalid Type",
             .Icon = MessageBoxIcon.[Stop],
             .DoActionReason = ValidateReasons.CancelEdit Or ValidateReasons.EndEdit
        }
        ApplyCellValidator(_sheetSched, 1, 0, 99, 1, requiredTypValidator, _
			lineNotify, msgRequiredTypNotify)

        'RangeValidator for scheduling date
        Dim currentDate As New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0)
        Dim rangeDateValidator As New RangeValidator() With {
             .MinValue = currentDate,
             .MaxValue = currentDate.AddDays(100).AddSeconds(-1),
             .RequiredType = GetType(DateTime)
        }
        Dim msgDateRangeNotify As New MessageBoxNotify() With {
             .Message = "Please enter a Date in range from {rangeDateValidator.MinValue} to {DateTime.Now.AddDays(99).ToShortDateString()}",
             .Caption = "Invalid Date for scheduling",
             .Icon = MessageBoxIcon.[Stop],
             .DoActionReason = ValidateReasons.CancelEdit Or ValidateReasons.EndEdit
        }

        ApplyCellValidator(_sheetSched, 3, 5, 1, 1, rangeDateValidator, _
			lineNotify, msgDateRangeNotify)
	End Sub

	Private Sub _bAddEditValidator_Click(sender As Object, e As EventArgs)
		fpSpread.ShowCellValidatorsForm(Me.ParentForm, True)
	End Sub

	Private Sub _bClearValidators_Click(sender As Object, e As EventArgs)
        Dim cellRange As FarPoint.Win.Spread.Model.CellRange = fpSpread.Sheets(0).GetSelection(0)
        If cellRange IsNot Nothing Then
			fpSpread.Sheets(0).ClearValidators(cellRange.Row, cellRange.Column, cellRange.RowCount, cellRange.ColumnCount)
		End If
	End Sub

	Private Sub ApplyCellValidator(sheet As SheetView, row As Integer, col As Integer, rowCount As Integer, colCount As Integer, validator As BaseDataValidator, _
		ParamArray actions As ValidateActionBase())
        For Each action As Object In actions
            validator.Actions.Add(action)
        Next
        sheet.AddValidators(New FarPoint.Win.Spread.Model.CellRange(row, col, rowCount, colCount), validator)
	End Sub
	Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, value As T) As T
		target = value
		Return value
	End Function
End Class

'
'
'

Partial Class CellValidators
	''' <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.fpSpread = New FarPoint.Win.Spread.FpSpread()
		Me._sheetIntro = New FarPoint.Win.Spread.SheetView()
		Me._sheetSched = New FarPoint.Win.Spread.SheetView()
		Me._sheetNames = New FarPoint.Win.Spread.SheetView()
		Me._bAddEditValidator = New System.Windows.Forms.Button()
		Me._bClearValidators = New System.Windows.Forms.Button()
		Me._pnlButtons = New System.Windows.Forms.Panel()
		Me.pnlMain.SuspendLayout()
		Me.pnlControls.SuspendLayout()
		DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me._sheetIntro, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me._sheetSched, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me._sheetNames, System.ComponentModel.ISupportInitialize).BeginInit()
		Me._pnlButtons.SuspendLayout()
		Me.SuspendLayout()
		' 
		' pnlMain
		' 
		Me.pnlMain.Controls.Add(Me.fpSpread)
		Me.pnlMain.Size = New System.Drawing.Size(603, 599)
		' 
		' pnlControls
		' 
		Me.pnlControls.Controls.Add(Me._pnlButtons)
		Me.pnlControls.Size = New System.Drawing.Size(199, 599)
		' 
		' fpSpread
		' 
		Me.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill
		Me.fpSpread.Location = New System.Drawing.Point(0, 0)
		Me.fpSpread.Name = "fpSpread"
		Me.fpSpread.Sheets.AddRange(New FarPoint.Win.Spread.SheetView() {Me._sheetIntro, Me._sheetSched, Me._sheetNames})
		Me.fpSpread.Size = New System.Drawing.Size(603, 599)
		Me.fpSpread.TabIndex = 0
		Me.fpSpread.ActiveSheetIndex = 1
		' 
		' _sheetIntro
		' 
		Me._sheetIntro.Reset()
		Me._sheetIntro.SheetName = "Introduction"
		' Formulas and custom names must be loaded with R1C1 reference style
		Me._sheetIntro.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
		Me._sheetIntro.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
		' 
		' _sheetSched
		' 
		Me._sheetSched.Reset()
		Me._sheetSched.SheetName = "SampleValidators"
		' Formulas and custom names must be loaded with R1C1 reference style
		Me._sheetSched.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
		Me._sheetSched.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
		' 
		' _sheetNames
		' 
		Me._sheetNames.Reset()
		Me._sheetNames.SheetName = "Names"
		' Formulas and custom names must be loaded with R1C1 reference style
		Me._sheetNames.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
		Me._sheetNames.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
		' 
		' _bAddEditValidator
		' 
		Me._bAddEditValidator.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
		Me._bAddEditValidator.Location = New System.Drawing.Point(15, 20)
		Me._bAddEditValidator.Name = "_bAddEditValidator"
		Me._bAddEditValidator.Size = New System.Drawing.Size(130, 23)
		Me._bAddEditValidator.TabIndex = 0
		Me._bAddEditValidator.Text = "Add/Edit Validators"
		Me._bAddEditValidator.UseVisualStyleBackColor = True
		AddHandler Me._bAddEditValidator.Click, New System.EventHandler(AddressOf Me._bAddEditValidator_Click)
		' 
		' _bClearValidators
		' 
		Me._bClearValidators.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
		Me._bClearValidators.Location = New System.Drawing.Point(15, 73)
		Me._bClearValidators.Name = "_bClearValidators"
		Me._bClearValidators.Size = New System.Drawing.Size(130, 23)
		Me._bClearValidators.TabIndex = 1
		Me._bClearValidators.Text = "Clear Validators"
		Me._bClearValidators.UseVisualStyleBackColor = True
		AddHandler Me._bClearValidators.Click, New System.EventHandler(AddressOf Me._bClearValidators_Click)
		' 
		' _pnlButtons
		' 
		Me._pnlButtons.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
		Me._pnlButtons.Controls.Add(Me._bAddEditValidator)
		Me._pnlButtons.Controls.Add(Me._bClearValidators)
		Me._pnlButtons.Location = New System.Drawing.Point(5, 14)
		Me._pnlButtons.Name = "_pnlButtons"
		Me._pnlButtons.Size = New System.Drawing.Size(159, 127)
		Me._pnlButtons.TabIndex = 2
		' 
		' CellValidation
		' 
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.Name = "CellValidation"
		Me.Size = New System.Drawing.Size(803, 599)
		Me.pnlMain.ResumeLayout(False)
		Me.pnlControls.ResumeLayout(False)
		DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
		DirectCast(Me._sheetIntro, System.ComponentModel.ISupportInitialize).EndInit()
		DirectCast(Me._sheetSched, System.ComponentModel.ISupportInitialize).EndInit()
		DirectCast(Me._sheetNames, System.ComponentModel.ISupportInitialize).EndInit()
		Me._pnlButtons.ResumeLayout(False)
		Me.ResumeLayout(False)

	End Sub

	#End Region

	Private fpSpread As FarPoint.Win.Spread.FpSpread
	Private _sheetNames As FarPoint.Win.Spread.SheetView
	Private _sheetSched As FarPoint.Win.Spread.SheetView
	Private _sheetIntro As FarPoint.Win.Spread.SheetView
	Private _bClearValidators As System.Windows.Forms.Button
	Private _bAddEditValidator As System.Windows.Forms.Button
	Private _pnlButtons As System.Windows.Forms.Panel
End Class
