'*********************************************************************************************************
'VALIDATION FUNCTIONS (CASE FUNCTIONS,FUNCTIONS TO CREATE ERROR SHEET AND GENERATE UPLOAD)
'*********************************************************************************************************

Public Sub createErrorSheet()
	'Start Here
	thisComponent.CurrentController.Frame.ContainerWindow.Enable= false

    Dim sectionName As String
	Dim sectionDesc As String
    Dim fieldName As String
    Dim previousSectionName As String
    Dim row As Long
    Dim column As Long
    Dim errorFound As Boolean
    errorFound = False
    ' Get the Error Sheet and clear its content
    Dim errorSheet as object
    errorSheet = ThisComponent.Sheets.getByName("Errors")
	errorSheet.Unprotect (Pwd)
    errorSheet.clearContents( _
		com.sun.star.sheet.CellFlags.VALUE _
		+com.sun.star.sheet.CellFlags.STRING _
		+com.sun.star.sheet.CellFlags.DATETIME _
		+com.sun.star.sheet.CellFlags.FORMULA)
    Dim srNo As Integer
    Dim startIndex As Integer
    startIndex = 1
    Dim errInd As Integer
    Dim errDesc As String
    EMPTYCELLTYPE = com.sun.star.table.CellContentType.EMPTY
    Dim validationSheet as object
    validationSheet = ThisComponent.Sheets.getByName("Validations")
    validationSheet.UnProtect (Pwd)
    'With validationSheet
    'With Worksheets.Item(4)
    Dim rangeName as String, mandatFlag as String, validityCheck as Boolean

	'Added By Janhavi on 23 Jan 2012
   	errInd = 1

   	call MacroRunning()
	oProgressBarModel.setPropertyValue( "ProgressValue", 10 )
    labelModel.Label="Please wait while data is being read and validated..."
    For row = 1 To 65535
    	If(validationSheet.getCellByPosition(0,row).getType() = EMPTYCELLTYPE) Then
    		Exit For
    	End If
        sectionName = validationSheet.getCellByPosition(0,row).String
		sectionDesc = validationSheet.getCellByPosition(14,row).String
        If Trim(previousSectionName) = "" Then
            previousSectionName = sectionName
        End If


        If row = 1 Or UCase(sectionName) <> UCase(previousSectionName) Then
         'Added By Janhavi on 23 Jan 2012
	     'Define Header of Error Sheet

        	previousSectionName = sectionName

        	If (row-1) =0 then
			'errorSheet.getCellByPosition(0, errInd - 2).CharWeight = com.sun.star.awt.FontWeight.BOLD
			 errorSheet.getCellByPosition(0,0).String = "Sr. No."
		     errorSheet.getCellByPosition(1,0).String = "Section Name"
		     errorSheet.getCellByPosition(2,0).String = "Field"
		     errorSheet.getCellByPosition(3,0).String = "Error Description"
		     errorSheet.getCellByPosition(4,0).String = "Reference Cell"
			End If
    	'End Janhavi on 23 Jan 2012
        End If
        'mandatFlag = validationSheet.getCellByPosition(3, row).String
        rangeName = validationSheet.getCellByPosition(2, row).String
        Dim startRowNum as Long , endRowNum as Long, startColumnNum as Long , endcolumnNum as Long
        oRange = ThisComponent.NamedRanges.getByName(rangeName)
        startRowNum = oRange.getReferredCells().getRangeAddress().StartRow
        endRowNum = oRange.getReferredCells().getRangeAddress().EndRow
        startColumnNum = oRange.getReferredCells().getRangeAddress().StartColumn
        endcolumnNum = oRange.getReferredCells().getRangeAddress().EndColumn
        'Added by Janhavi on 17.02.2012
        Dim singleCellRng as String

        If (startRowNum = endRowNum and startColumnNum = endColumnNum) then
        	REM this is a single cell validate against the rule in same rows
        	fieldName = validationSheet.getCellByPosition(1, row).String
        	For column = 3 To 256 Step 2
		    	validationFunctionCell = validationSheet.getCellByPosition(column, row)
		    	' if the cell is empty break current for loop and goto next row
		    	If(validationFunctionCell.getType() = EMPTYCELLTYPE) Then
    				Exit For
    			Else
    				validationFunctionInt = validationFunctionCell.String
    				errDesc = validationSheet.getCellByPosition(column+1, row).String
    				validityCheck = True
    				cell = oRange.getReferredCells().getCellByPosition(0,0)
    				cellValue = cell.String
    				Select Case validationFunctionInt
				    	Case "0" 'isMandatory
				        	validityCheck = isMandatory(cellValue)
				        Case "1" 'TestAlphanumeric
				        	validityCheck = TestAlphanumeric(cellValue)
				        Case "2" 'TestPIN
								validityCheck = TestPIN(cellValue)
						Case "3" 'TestNumber
				        	validityCheck = TestNumber(cellValue)
				        Case "4" 'validateCurrencyFormat
				        	validityCheck = validateCurrencyFormat(cellValue)
	                    Case "5" 'TestDate
			 				validityCheck = ValidateDate(cellValue)
				        Case "6" 'TestDateBtwnRtnPeriod
							If(cellValue="") then
								validityCheck =True
							else
								validityCheck = TestDateBtwnRtnPeriod(cellValue)
							End IF

						Case "7" 'TestAlphabetOnly
							validityCheck = TestAlphabet(cellValue)
						Case "8" 'TestAlphabetWithSpaceOnly
							validityCheck = TestAlphabetWithSpace(cellValue)
				        case "9"
				        	validityCheck = TestAlphabetNumSpl(cellValue)
				       	Case "10"
				       		If(cellValue="") then
								validityCheck =True
							else
								validityCheck = checkFutureDate(cellValue)
							End If
						Case "11"
							validityCheck = checkDuplicateTaxPyrPIN(cellValue)
				        Case "12" 'check amount is greater than Zero or not
							        		if (validateCurrencyFormat(cellValue)) then
							        			if CDbl(cellValue)>0 then
							        				validityCheck=True
							        			else
							        				validityCheck=false
							        			End If
							        		Else
					        					validityCheck=false
								        	End If
					    Case "15"
							validityCheck = CompareSumValue()
					    'CompareSumValue
				        Case "20" 'RelevantDateCheck
							If(cellValue="") then
								validityCheck =True
							else
								validityCheck = RelevantDateCheck(cellValue)
							End IF
						Case "37" 'lessCompareTotals
							validityCheck = lessCompareTotals(rangeName,cell.value)
						Case "40" 'checkDateOfDeposit
							validityCheck = checkDateOfDeposit(cellValue)
						Case "41" 'checkDateOfDepositSelfAssess
							validityCheck = checkDateOfDepositSelfAssess(cellValue)
						Case "42" 'prnEntriesBothSch
							validityCheck = prnEntriesBothSch()
						Case "43" 'prnEntriesSelfAssess
							validityCheck = prnEntriesSelfAssess()
						Case "44" 'advPaymentGreaterThanLiability
							validityCheck = advPaymentGreaterThanLiability()
						Case "45" 'selfPaymentGreaterThanLiability
							validityCheck = selfPaymentGreaterThanLiability()
						Case "61" 'TestAlphabetNumNoSpl
							validityCheck = TestAlphabetNumNoSpl(cellValue)
						Case "62" 'TestAlphabetNumSpaceDot
							validityCheck = TestAlphabetNumSpaceDot(cellValue)
						Case "64" 'TestPRN
							validityCheck = TestPRN(cellValue)
				        Case Else
				            ina = ina + 1
				    End Select
				    If validityCheck = False Then
						srNo = srNo + 1

					'Updated by janhavi on 23 Jan 2012
					'errInd=	printErrorStack(errInd, startRowNum, fieldName, errDesc, startIndex, rangeName)
					'errInd = printErrorStack(errInd,sectionName, fieldName,errDesc,startRowNum,srNo, rangeName)

			   			  'Added by Janhavi on 17.02.2012
			   			singleCellRng= ColumnNumberToString(INT(cell.getRangeAddress().startcolumn))& (cell.getRangeAddress().startrow +1)
						errInd = printErrorStack(errInd,sectionName, fieldName, errDesc,singleCellRng ,  srNo, rangeName,sectionDesc)
						'Changes by Maulika end ColumnNumberToString(INT(columnName))&(startRowNum + multriEntryRowNum + 1)

			        	'errInd = errInd + 1
			        	errorFound = True
				    End If
    			End If
    		Next
    	Else

    		Rem This is Multi-Entry field validate according to the range specified
    		startMEVR = validationSheet.getCellByPosition(3, row).value - 1
    		endMEVR = validationSheet.getCellByPosition(4, row).value - 1
    		For multriEntryRowNum = 0 to endRowNum - startRowNum
    			REM Check whether any column contains data
    			REM for current multi-entry row using multriEntryRowNum, startColNum and endColNum
    			REM If any column contains data then run the validationrules according to the range

    			doValidation = False
    			For multiEntryColNum = startColumnNum to endColumnNum
    				curCell = oRange.getReferredCells().getCellByPosition(multiEntryColNum,multriEntryRowNum)
					'Added by Janhavi for skiping cells with formulas on 7th Dec 2011
    				If(curCell.getType() <> EMPTYCELLTYPE And InStr(curCell.FormulaLocal, "=")=0) Then
'    				If(curCell.getType() <> EMPTYCELLTYPE ) Then
    					doValidation = True
    					Exit For
					End If
    			Next 'multiEntryColNum
    			If (doValidation) Then
	    			Rem Do the validation for the row start from startmultyentryvalidationrow to endmultyentryvalidationrow
	    			For eachCellValidationRow = startMEVR to endMEVR
	    				fieldName = validationSheet.getCellByPosition(1, eachCellValidationRow).String
	    				columnName = validationSheet.getCellByPosition(2, eachCellValidationRow).String
		    			oCells=oRange.getReferredCells().getCellByPosition(columnName,multriEntryRowNum)
			    		CellProtStruc = oCells.CellProtection
			    		If CellProtStruc.isLocked = False then

	    				For column = 3 To 256 Step 2
					    	validationFunctionCell = validationSheet.getCellByPosition(column, eachCellValidationRow)
					    	' if the cell is empty break current for loop and goto next row
					    	If(validationFunctionCell.getType() = EMPTYCELLTYPE) Then
			    				Exit For
			    			Else
			    				validationFunctionInt = validationFunctionCell.String
			    				errDesc = validationSheet.getCellByPosition(column+1, eachCellValidationRow).String
			    				validityCheck = True
			    				' TODO change tge multriEntryColNum with the value related to C column value of Validations
			    				cellValue = oRange.getReferredCells().getCellByPosition(columnName,multriEntryRowNum).String
			    				'Arjun - Added For Relevant Invoice Date and Number
			    				nextCellValueStr= oRange.getReferredCells().getCellByPosition(1,multriEntryRowNum).String
			    				multiCell = oRange.getReferredCells().getCellByPosition(columnName,multriEntryRowNum).CellAddress

			    				Select Case validationFunctionInt
							    	Case "0" 'isMandatory
							        	validityCheck = isMandatory(cellValue)
							        Case "1" 'TestAlphanumeric
							        	validityCheck = TestAlphanumeric(cellValue)
							        Case "2" 'TestPIN
							        	validityCheck = TestPIN(cellValue)

							        Case "3" 'TestNumber
							        	validityCheck = TestNumber(cellValue)
							        Case "4" 'TestCurrencyFormat
							        	validityCheck = validateCurrencyFormat(cellValue)
							        Case "5" 'TestDate
										validityCheck = ValidateDate(cellValue)
							        Case "6" 'TestDateBtwnRtnPeriod
											If(cellValue="") then
												validityCheck =True
											else
												validityCheck = TestDateBtwnRtnPeriod(cellValue)
											End IF
									Case "7" 'TestAlphabetOnly
										validityCheck = TestAlphabet(cellValue)
									Case "8" 'TestAlphabetWithSpaceOnly
										validityCheck = TestAlphabetWithSpace(cellValue)
									Case "9"
							        	validityCheck = TestAlphabetNumSpl(cellValue)
									Case "10"
										validityCheck = checkFutureDate(cellValue)
									Case "11"
										validityCheck = checkDuplicateTaxPyrPIN(cellValue)
						            Case "12" 'check amount is greater than Zero or not
							        		if (validateCurrencyFormat(cellValue)) then
							        			if CDbl(cellValue)>0 then
							        				validityCheck=True
							        			else
							        				validityCheck=false
							        			End If
							        		Else
					        					validityCheck=false
								        	End If
								    Case "15"
										validityCheck = CompareSumValue()
									Case "20" 'RelevantDateCheck
										If(cellValue="") then
											validityCheck =True
										else
											validityCheck = RelevantDateCheck(cellValue)
										End IF
									Case "37" 'lessCompareTotals
										validityCheck = lessCompareTotals(rangeName,cell.value)
									Case "40" 'checkDateOfDeposit
										validityCheck = checkDateOfDeposit(cellValue)
									Case "41" 'checkDateOfDepositSelfAssess
										validityCheck = checkDateOfDepositSelfAssess(cellValue)
									Case "42" 'prnEntriesBothSch
										validityCheck = prnEntriesBothSch()
									Case "43" 'prnEntriesSelfAssess
										validityCheck = prnEntriesSelfAssess()
									Case "44" 'advPaymentGreaterThanLiability
										validityCheck = advPaymentGreaterThanLiability()
									Case "45" 'selfPaymentGreaterThanLiability
										validityCheck = selfPaymentGreaterThanLiability()
									Case "61" 'TestAlphabetNumNoSpl
										validityCheck = TestAlphabetNumNoSpl(cellValue)
									Case "62" 'TestAlphabetNumSpaceDot
										validityCheck = TestAlphabetNumSpaceDot(cellValue)
									Case "63" 'checkRelevantInvNoDate
										validityCheck = checkRelevantInvNoDate(rangeName)
									Case "64" 'TestPRN
										validityCheck = TestPRN(cellValue)
							        Case Else
							            ina = ina + 1
							    End Select


							    'Check For Condtional Mandatory
							    If validityCheck = False Then
									srNo = srNo + 1
								'Updated by janhavi on 23 Jan 2012
								'	errInd = printErrorStack(errInd, startRowNum + multriEntryRowNum , fieldName, errDesc, startIndex, rangeName)
						        ''	errInd = printErrorStack(errInd,sectionName, fieldName,errDesc,startRowNum + multriEntryRowNum,srNo, rangeName)
							        'Changes by Maulika start
									errInd = printErrorStack(errInd,sectionName, fieldName, errDesc, ColumnNumberToString(INT(columnName))&(startRowNum + multriEntryRowNum + 1) ,  srNo, rangeName,sectionDesc)
									'changes by Maulika end
						        	'errInd = errInd + 1
						        	errorFound = True
							    End If
			    			End If
		    			Next 'column
		  			  End If

	    			Next 'eachCellValidation
	    		End If
	    	Next 'multriEntryRowNum
    	End If
  	Next

   oProgressBarModel.setPropertyValue( "ProgressValue", 100 )

   errorSheet.protect (Pwd)
   validationSheet.Protect (Pwd)
  	If errorFound Then
    	MsgBox "Error Found in the sheet"
      	oSheet = ThisComponent.Sheets.getByName("Errors")
		ThisComponent.CurrentController.setActiveSheet(oSheet)

	Else
        Call RunSheetUploadDlg
		If (allow=true) then
	        Call fillUploadSheet
	        'MsgBox "Sheets are ready to be uploaded."
	      '  msg1 = MsgBox(msg, vbQuestion + vbYesNo, "Generate Upload File")
	      '  If msg1 = vbYes Then
	       ' Generate_upload
	       generate_ods_file
       End If
      '  End If
    End If
  'Call createErrorSheetForList(errInd, srNo)
  thisComponent.CurrentController.Frame.ContainerWindow.Enable= True
End Sub

Public Function CheckOtherValidations(rangeName As String, row as Long, Optional colName As String) As Boolean
	CheckOtherValidations = True
End Function
'PREVIOUS METHOD
'Public Function printErrorStack(errline_index As Integer, col_no As Double, field As String, error As String, start_index As Integer, rangeName As String) As Integer
'   Dim Doc As Object
'	Dim SheetError As Object
'	Dim CellError As Object
'   Doc = ThisComponent
'	SheetError = Doc.Sheets.getByName("Errors")
'	CellError = SheetError.getCellByPosition(0,errline_index)
'	CellError.String = errline_index - start_index
'	CellError = SheetError.getCellByPosition(1,errline_index)
'	CellError.String = col_no + 1
'	CellError = SheetError.getCellByPosition(2,errline_index)
'	CellError.String = field
'	CellError = SheetError.getCellByPosition(3,errline_index)
'	CellError.String = error
	'CellError = SheetError.getCellByPosition(4,errline_index)
	'CellError.String = rangeName
'    printErrorStack = errline_index + 1
    'ERR_COUNT = ERR_COUNT + 1
'End Function

'UPDATED METHOD TO PRINT ERROR STACK IN ERROR SHEET. Updated By Janhavi 23 Jan 2012
'Public Function printErrorStack(errline_index As Integer,sectionName as String, field As String, error As String, col_no As Double, srNo As Integer, rangeName As String) As Integer
'    Dim Doc As Object
'	Dim SheetError As Object
'	Dim CellError As Object
'   Doc = ThisComponent
'	SheetError = Doc.Sheets.getByName("Errors")
'	CellError = SheetError.getCellByPosition(0,errline_index)
'	CellError.String = srNo
'	CellError = SheetError.getCellByPosition(1,errline_index)
'	CellError.String = sectionName
'	CellError = SheetError.getCellByPosition(2,errline_index)
'	CellError.String = field
'	CellError = SheetError.getCellByPosition(3,errline_index)
'	CellError.String = error
'	CellError = SheetError.getCellByPosition(4,errline_index)
'	CellError.String = col_no + 1

	'CellError = SheetError.getCellByPosition(4,errline_index)
	'CellError.String = rangeName
'    printErrorStack = errline_index + 1
    'ERR_COUNT = ERR_COUNT + 1
'End Function

REM changed by  maulika for Hyperlinks
Public Function printErrorStack(errline_index As Integer,sectionName as String, field As String, error As String, col_no As String, srNo As Integer, rangeName As String, optional sectionDesc As String) As Integer
    Dim Doc As Object
	Dim SheetError As Object
	Dim CellError As Object
    Doc = ThisComponent
	SheetError = Doc.Sheets.getByName("Errors")
	CellError = SheetError.getCellByPosition(0,errline_index)
	CellError.String = srNo
	CellError = SheetError.getCellByPosition(1,errline_index)
	CellError.String = sectionDesc 'Section Description added by Sandeep Thaker
'	CellError.CharColor = RGB(0,0,0)
	CellError.CharUnderline = 0
	CellError = SheetError.getCellByPosition(2,errline_index)
	CellError.String = field
'	CellError.CharColor = RGB(0,0,255)
	CellError.CharUnderline = 1
	CellError.FormulaLocal="=HYPERLINK(CONCATENATE(""#"";"""+ sectionName +""";""!"";"""+col_no+""");"""+field+""")"


	CellError = SheetError.getCellByPosition(3,errline_index)
	CellError.String = error
	CellError = SheetError.getCellByPosition(4,errline_index)
	CellError.String = col_no
	'CellError = SheetError.getCellByPosition(4,errline_index)
	'CellError.String = rangeName
    printErrorStack = errline_index + 1
    'ERR_COUNT = ERR_COUNT + 1
End Function
REM end of changes by maulika

'**************************************************************
'	VALIDATION CASES FUNCTIONS
'**************************************************************
'Case 0 isMandatory
'Case 1 TestAlphanumeric
'Case 2 TestPIN
'Case 3 TestNumber
'Case 4 validateCurrencyFormat
'Case 5 TestDate
'Case 6 TestDateBtwnRtnPeriod
'Case 7 TestAlphabet
'Case 8 TestAlphabetWithSpace
'Case 9 TestAlphabetNumSpl
'Case10
'Case 11 checkDuplicateTaxPyrPIN
Public Function isMandatory(lstr_check As String) As Boolean
	isMandatory = True
	If Trim(lstr_check) = "" Then
		isMandatory = False
	End If
	'sheetName = value.Worksheet.Name
End Function

Public Function TestAlphanumeric(lstr_check As String) As Boolean
	'allowed characters A to Z, a to z, 0 To 9 And Blank
	Dim i As Integer
	Dim ia As Integer
	Dim ina As Integer
	Dim stlen As Integer
	stlen = Len(lstr_check)
	ia = 0
	TestAlphanumeric = True
	For i = 1 To stlen
	    Select Case (Mid(lstr_check, i, 1))
	        Case "A" To "Z"          'A to Z
	            ia = ia + 1
	        Case "a" To "z"          'a to z
	            ia = ia + 1
	       ' Case " "                 ' Blank
	       '     ia = ia + 1
	        Case "0" To "9"          '0 to 9
	        ia = ia + 1
	      ' Case ","                 ',
	      '  ia = ia + 1
	      '  Case "."                 '.
	      '  ia = ia + 1
	      '  Case "/"
	      '  ia = ia + 1
	     '   Case "-"
	     '   ia = ia + 1
	        Case Else
	            TestAlphanumeric = False
				Exit Function
	    End Select
	Next i
End Function

Public Function TestPIN(lstr_check As String) As Boolean
	'allowed characters A to Z, a to z And Blank
	Dim i As Integer
	Dim ia As Integer
	Dim ina As Integer
	Dim stlen As Integer
	Dim ascChr As Integer
	stlen = Len(lstr_check)
	ia = 0
	TestPIN = True
	If(stlen <> 11) Then
		'ina = 1
		TestPIN = False
		If (stlen =0 ) Then
			TestPIN = True
		End If
		Exit Function

	Else
		For i = 1 To 11
			curChar = (Mid(lstr_check, i, 1))
			If i = 1  And (curChar <> "A" and curChar <> "a" and curChar <> "P" and curChar <> "p") Then
				'ina = 1
				TestPIN = False
				Exit Function
			ElseIf i > 1 and i < 11 Then
		    	Select Case curChar
		        	Case "0" To "9"          'A to Z
		            	ia = ia + 1
			        Case Else
			            'ina = 1
			            TestPIN = False
			            Exit Function
			    End Select
			ElseIf i = 11 Then
				Select Case curChar
		        	Case "A" To "Z"          'A to Z
		            	ia = ia + 1
		            Case "a" To "z"          'A to Z
		            	ia = ia + 1
		        	Case Else
				    	TestPIN = False
			            Exit Function
				End Select
			End If
		Next i
	End If
End Function


Public Function TestNumber(lstr_check As String) As Boolean
	'allowed characters 0 to 9 and .
	Dim i As Integer
	Dim ia As Integer
	'Dim ina As Integer
	Dim stlen As Integer

	stlen = Len(lstr_check)
	ia = 0
	'ina = 0
	TestNumber = True
	For i = 1 To stlen
	    Select Case (Mid(lstr_check, i, 1))
	        Case "0" To "9"          '0 to 9
	        	ia = ia + 1
	        Case Else
	            'ina = ina + 1
	            TestNumber = False
	            Exit Function
	    End Select
	Next i
End Function

'validateCurrencyFormat
Public Function validateCurrencyFormat(lstr_check As String) As Boolean
	'allowed characters 0 to 9 and .
	Dim i As Integer
	Dim ia As Integer
	Dim ina As Integer
	Dim stlen As Integer
	Dim dotCount As Integer
	stlen = Len(lstr_check)
	ia = 0
	ina = 0
	validateCurrencyFormat = True
	For i = 1 To stlen
		Select Case (Mid(lstr_check, i, 1))
	    	Case "-"
	        	ia = ia + 1
	       	Case ","
	        	ia = ia + 1
	       	Case "."
	        	ia = ia + 1
	        	dotCount = dotCount + 1
	        Case "0" To "9"          '0 to 9
	        	ia = ia + 1
	        Case Else
	            ina = ina + 1
	            validateCurrencyFormat = False
	            Exit Function
	    End Select
	Next i
	If dotCount > 1 Then
		validateCurrencyFormat = False
	End If
End Function
Public Function TestDate(strDate As String) As Boolean
    Dim strMonth As String
    Dim strDay As String
    Dim strYear As String
    Dim validDay As Boolean
    Dim validMonth As Boolean
    Dim validYear As Boolean
    validDay = False
    validMonth = False
    validYear = False
    Dim validFormat As Boolean
    Dim i As Integer
    Dim Length As Integer
    Dim temp As String
    Dim index1 As Integer
    Dim index2 As Integer
    Dim flag As Integer

    TestDate = False

    If IsDate(strDate) = True Then
       Length = Len(strDate)
       flag = 0

       If (Length > 10) Then
            validFormat = False
       Else

            validFormat = True
            For i = 1 To Length
                If (Mid(strDate, i, 1) = "/") Then
                    flag = flag + 1

                    If (flag = 1) Then
                        index1 = i
                    End If
                    If (flag = 2) Then
                        index2 = i
                    End If

                End If
            Next i
            If (index1 > 0 And index2 > 0) Then
            strDay = Mid(strDate, 1, index1 - 1)
            If (Len(strDay) > 2) Then
                validDay = False
            Else
                If IsNumeric(strDay) = True Then
                    If Val(strDay) > 32 Then
                        validDay = False
                    Else
                        validDay = True
                    End If
                Else
                    validDay = True
                End If
            End If


            strMonth = Mid(strDate, index1 + 1, (index2 - index1) - 1)
            If (Len(strMonth) > 2) Then
                validDay = False
            Else
                If IsNumeric(strMonth) = True Then
                    If Val(strMonth) > 12 Then
                        validMonth = False
                    Else
                        validMonth = True
                    End If
                Else
                    validMonth = True
                End If
            End If

            strYear = Mid(strDate, index2 + 1, Len(strDate))
            If (Len(strYear) > 4) Then
                validYear = False
            Else

                If IsNumeric(strYear) = True Then
                    validYear = True
                Else
                    validYear = False
                End If
            End If
       End If
       End If
        If (validFormat = False Or validDay = False Or validMonth = False Or validYear = False) Then
            If Length=0 then
	           TestDate = True
       		Else
               TestDate = False
            End If
        Else

               TestDate = True
        End If
    End If

End Function

Function ValidateDate(EnteredDate As String) As Boolean
	ValidateDate = True
	Dim i as Integer
	Dim enDate as Integer
	Dim enMonth as Integer
	Dim enYear as Integer
	if Len(Trim(EnteredDate))=0 then
			ValidateDate = True
			Exit Function
	End If
	if Len(Trim(EnteredDate)) <> 10 and Len(Trim(EnteredDate)) <> 0 then
		ValidateDate = False
		EXIT FUNCTION
	else
		for i= 1 to Len(EnteredDate)
			curChar = (Mid(EnteredDate, i, 1))
			if i = 3 or i = 6 then
				Select Case (curChar)
					Case "/"
					Case Else
						ValidateDate = False
							EXIT FUNCTION
				end select
			else
				Select Case (curChar)
					Case "0" To "9"
					Case Else
						ValidateDate = False
							EXIT FUNCTION
				end select
			end if
		Next i
	end if
	enDate = Int((Mid(EnteredDate, 1, 2)))
	enMonth = Int((Mid(EnteredDate, 4, 2)))
	enYear = Int((Mid(EnteredDate, 7,4)))
	'Added By Arjun to avoid 00/00/0000
	if enDate=0 or enMonth=0 or enYear=0 then
		ValidateDate = False
		EXIT FUNCTION
	End if
	yearMod = INT(enYear / 4)
	if (yearMod * 4)  <> enYear then

		if enMonth > 12 then
			ValidateDate = False
					EXIT FUNCTION
		else
			if enMonth <> 2 then
				if enMonth = 1 or enMonth = 3 or enMonth = 5 or enMonth= 7 or enMonth = 8 or enmonth = 10 or enmonth = 12 then
					if enDate > 31 then
						ValidateDate = False
								EXIT FUNCTION
					end if
				else
					if enDate > 30 then
						ValidateDate = False
								EXIT FUNCTION
					end if
				end if
			else
				if enDate > 28 then
					ValidateDate = False
							EXIT FUNCTION
				end if
			end if
		end if
	else
		if enMonth > 12 then
			ValidateDate = False
		else
			if enMonth <> 2 then
				if enMonth = 1 or enMonth = 3 or enMonth = 5 or enMonth= 7 or enMonth = 8 or enmonth = 10 or enmonth = 12 then
					if enDate > 31 then
						ValidateDate = False
								EXIT FUNCTION
					end if
				else
					if enDate > 30 then
						ValidateDate = False
								EXIT FUNCTION
					end if
				end if
			else
				if enDate > 29 then
					ValidateDate = False
							EXIT FUNCTION
				end if
			end if
		end if
	end if

end Function


Public Function TestDateBtwnRtnPeriod1(ByVal value As String) As Boolean
Dim mm As Integer
Dim yr As Integer
Dim startDate As String
Dim endDate As String
Dim sheet As Object
Dim startMon as Integer
Dim endMon as Integer
Dim currMon as Integer

Dim startDt as Object
Dim endDt as Object
Dim currDt as Object


startDate =    ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdFrom").string
endDate =     ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdTo").string
startDt= Split( startDate,"/")
endDt= Split( endDate,"/")
currDt= Split( value,"/")

If ValidateDate(value) = True And ValidateDate(startDate) = True And ValidateDate(endDate) = True Then
    If currDt(2)=startDt(2) then
	    If currDt(1)>=startDt(1) and currDt(1)<=endDt(1) then
	          TestDateBtwnRtnPeriod1 = True
	    Else
	          TestDateBtwnRtnPeriod1 = False
	    End If
    Else
        TestDateBtwnRtnPeriod1 = False
    End If
Else
        TestDateBtwnRtnPeriod1 = False
End If
End Function

Public Function TestDateBtwnRtnPeriod(ByVal value As String) As Boolean
Dim mm As Integer
Dim yr As Integer
Dim startDate As String
Dim endDate As String
Dim sheet As Object
Dim startMon as Integer
Dim endMon as Integer
Dim currMon as Integer

Dim startDt as Object
Dim endDt as Object
Dim currDt as Object


unprotectsheet("A_Basic_Info")
'->Arjun
if(ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdTo").string<>"")then
	startDate =    ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdFrom").string
	endDate =     ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdTo").string

	startDt= Split( startDate,"/")
	endDt= Split( endDate,"/")
	currDt= Split( value,"/")

	If ValidateDate(value) = True And ValidateDate(startDate) = True And ValidateDate(endDate) = True Then
	    If lessThnEqualToReturnYr(value,INT(endDt(2)),INT(endDt(1)),INT(endDt(0))) and greaterThnEqualToReturnYr(value,INT(startDt(2)),INT(startDt(1)),INT(startDt(0))) then
		    TestDateBtwnRtnPeriod = True
	    Else
	        TestDateBtwnRtnPeriod = False
	    End If
	Else
	        TestDateBtwnRtnPeriod = False
	End If
Else
	TestDateBtwnRtnPeriod = True
End If
protectsheet("A_Basic_Info")
End Function

'Arjun
Public Function lessThnEqualToReturnYr(ByVal value As String,ByVal year as Integer,ByVal month as Integer,ByVal day as Integer) As Boolean
Dim mm As Integer
Dim yr As Integer
Dim dd as Integer
Dim strDate As Object
Dim currDate as Object
Dim returnDate As Date

returnDate = DateSerial(year, month, day)

If(Len(Trim(value))=0) then
	lessThnEqualToReturnYr=True
	Exit function
End If
if ValidateDate(value) then
 strDate= Split(value, "/")
 dd= INT(strDate(0))
 mm= INT(strDate(1))
 yr= INT(strDate(2))

currDate= Split( Format(returnDate, "dd/mm/yyyy"),"/")
'If yr = Format(Now(), "yyyy") Then
If yr= INT(currDate(2)) then
	Dim mon as Integer
	Dim dayD as Integer
	mon= INT(currDate(1))
	dayD = INT(currDate(0))
    If mm < mon Then
      lessThnEqualToReturnYr = True
    ElseIf mm=mon then
    	If dd<=dayD then
    		lessThnEqualToReturnYr = True
    	Else
	      lessThnEqualToReturnYr = False
	    End If
	Else
	 lessThnEqualToReturnYr = False
	End If

ElseIf yr < currDate(2) And mm <= 12 Then
        lessThnEqualToReturnYr = True
	Else
    	lessThnEqualToReturnYr = False
End If
End If
End Function


'Arjun
Public Function greaterThnEqualToReturnYr(ByVal value As String,ByVal year as Integer,ByVal month as Integer,ByVal day as Integer) As Boolean
Dim mm As Integer
Dim yr As Integer
Dim dd as Integer
Dim strDate As Object
Dim currDate as Object
Dim returnDate As Date

returnDate = DateSerial(year, month, day)

If(Len(Trim(value))=0) then
	greaterThnEqualToReturnYr=True
	Exit function
End If
if ValidateDate(value) then
 strDate= Split(value, "/")
 dd= INT(strDate(0))
 mm= INT(strDate(1))
 yr= INT(strDate(2))

currDate= Split( Format(returnDate, "dd/mm/yyyy"),"/")
'If yr = Format(Now(), "yyyy") Then
If yr= INT(currDate(2)) then
	Dim mon as Integer
	Dim dayD as Integer
	mon= INT(currDate(1))
	dayD = INT(currDate(0))
    If mm < mon Then
      greaterThnEqualToReturnYr = False
    ElseIf mm=mon then
    	If dd<dayD then
    		greaterThnEqualToReturnYr = False
    	Else
	      greaterThnEqualToReturnYr = True
	    End If
	Else
	 greaterThnEqualToReturnYr = True
	End If

ElseIf yr < currDate(2) And mm <= 12 Then
        greaterThnEqualToReturnYr = False
	Else
    	greaterThnEqualToReturnYr = True
End If
End If
End Function

Public Function TestAlphabet(lstr_check As String) As Boolean
'allowed characters A to Z, a to z And Blank
Dim i As Integer
Dim ia As Integer
Dim ina As Integer
Dim stlen As Integer

stlen = Len(lstr_check)
ia = 0
ina = 0
For i = 1 To stlen
    Select Case (Mid(lstr_check, i, 1))
        Case "A" To "Z"          'A to Z
            ia = ia + 1
        Case "a" To "z"          'a to z
            ia = ia + 1
        Case Else
            ina = ina + 1
    End Select
Next i
If ina = 0 Then
    TestAlphabet = True
Else
    TestAlphabet = False
End If

End Function


Public Function TestAlphabetWithSpace(lstr_check As String) As Boolean
'allowed characters A to Z, a to z And Blank
Dim i As Integer
Dim ia As Integer
Dim ina As Integer
Dim stlen As Integer

stlen = Len(lstr_check)
ia = 0
ina = 0
For i = 1 To stlen
    Select Case (Mid(lstr_check, i, 1))
        Case "A" To "Z"          'A to Z
            ia = ia + 1
        Case "a" To "z"          'a to z
            ia = ia + 1
        Case " "                 ' Blank
            ia = ia + 1
        Case Else
            ina = ina + 1
    End Select
Next i
If ina = 0 Then
    TestAlphabetWithSpace = True
Else
    TestAlphabetWithSpace = False
End If

End Function


Public Function TestAlphabetNumSpl(lstr_check As String) As Boolean
'allowed characters A to Z, a to z And Blank
Dim i As Integer
Dim ia As Integer
Dim ina As Integer
Dim stlen As Integer

stlen = Len(lstr_check)
ia = 0
ina = 0
For i = 1 To stlen
    Select Case (Mid(lstr_check, i, 1))
        Case "A" To "Z"          'A to Z
            ia = ia + 1
        Case "a" To "z"          'a to z
            ia = ia + 1
        Case "0" To "9"          '0 to 9
            ia = ia + 1
       Case " ", ",", ".", "-", "/","'",":","&", "(", ")", "`", "$", "%", "}", "$", "}", "{" , "!", "|" ,"#", ";", "\" ', "[", "]"   ' Blank
            ia = ia + 1
        Case Else
            ina = ina + 1
    End Select
Next i
If ina = 0 Then
    TestAlphabetNumSpl = True
Else
    TestAlphabetNumSpl = False
End If

End Function


Public Function checkFutureDate(ByVal value As String) As Boolean
Dim mm As Integer
Dim yr As Integer
Dim dd as Integer
Dim strDate As Object
Dim currDate as Object
'mm = Format(CDate(Trim(value)), "MM")
'yr = Format(CDate(Trim(value)), "yyyy")
'dd = Format(CDate(Trim(value)), "dd")
If(Len(Trim(value))=0) then
	checkFutureDate=True
	Exit function
End If
if ValidateDate(value) then
 strDate= Split(value, "/")
 dd= INT(strDate(0))
 mm= INT(strDate(1))
 yr= INT(strDate(2))

currDate= Split( Format(Now(), "dd/mm/yyyy"),"/")
'If yr = Format(Now(), "yyyy") Then
If yr= INT(currDate(2)) then
	Dim mon as Integer
	Dim dayD as Integer
	mon= INT(currDate(1))
	dayD = INT(currDate(0))
    If mm < mon Then
      checkFutureDate = True
    ElseIf mm=mon then
    	If dd<=dayD then
    		checkFutureDate = True
    	Else
	      checkFutureDate = False
	    End If
	Else
	 checkFutureDate = False
	End If

ElseIf yr < currDate(2) And mm <= 12 Then
        checkFutureDate = True
	Else
    	checkFutureDate = False
End If
End If
End Function

'Arjun
Public Function specificYearBeforeDate(ByVal value As String,ByVal yearGap as Integer,ByVal dd as String) As String
Dim currDt as Object
Dim newDt as String
Dim intYear
'Dim a
'a = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12")

If value<>"" Then
		currDt= Split(value,"/")
		'Day
		newDt=dd & "/"
		'Month
		if currDt(1)="01" then
			newDt=newDt & "02"
			ElseIf currDt(1)="02" then
			newDt=newDt & "03"
			ElseIf currDt(1)="03" then
			newDt=newDt & "04"
			ElseIf currDt(1)="04" then
			newDt=newDt & "05"
			ElseIf currDt(1)="05" then
			newDt=newDt & "06"
			ElseIf currDt(1)="06" then
			newDt=newDt & "07"
			ElseIf currDt(1)="07" then
			newDt=newDt & "08"
			ElseIf currDt(1)="08" then
			newDt=newDt & "09"
			ElseIf currDt(1)="09" then
			newDt=newDt & "10"
			ElseIf currDt(1)="10" then
			newDt=newDt & "11"
			ElseIf currDt(1)="11" then
			newDt=newDt & "12"
			ElseIf currDt(1)="12" then
			newDt=newDt & "01"
		End If
		'Year
		intYear=INT(currDt(2))
		intYear=intYear-yearGap
		newDt=newDt & "/" & Cstr(intYear)
		'msgbox newDt
		specificYearBeforeDate=newDt
	End If

End Function

Public function checkDuplicateTaxPyrPIN(ByVal value As String) As Boolean

Dim PINNo As String
Dim sheet as Object
sheet=ThisComponent.sheets.getByName("A_Basic_Info")
PINNo = sheet.getCellrangeByName("SecA.TaxPayerPIN").String
If (TestPIN(lstr_check) And TestPIN(PINNo)) Then
    If lstr_check = PINNo And lstr_check <> "" And PINNo <> "" Then
        checkDuplicateTaxPyrPIN = False
    Else
        checkDuplicateTaxPyrPIN = True
    End If
Else
    checkDuplicateTaxPyrPIN = False
End If

End Function

'EXTRA FUNCTIONS
Public Function isTodaysDate(ByVal value As String) As Boolean

Dim sheetName As String
value = Format(CDate(Trim(value)), "dd/MM/yyyy")
If Trim(value) <> "" And Trim(value) <> Format(Now(), "dd/MM/yyyy") Then
    isTodaysDate = False
Else
    isTodaysDate = True
End If
End Function

sub generate_ods_file()

	ThisComponent.Store
	REM source document
	Dim sURL$, sLinkSheetName$
	   sURL = thisComponent.getURL()
	   'sLinkSheetName = thisComponent.Sheets.getByIndex(thisComponent.Sheets.count-1).getName()

   	  	'********************************************************
	  	'Code added by janhavi on 28.02.2012 for Amendment
	  	if ThisComponent.getSheets().hasByName("Amendment") Then
			sLinkSheetName = thisComponent.Sheets.getByIndex(thisComponent.Sheets.count-2).getName()
		Else
			sLinkSheetName = thisComponent.Sheets.getByIndex(thisComponent.Sheets.count-1).getName()
		End If
	  	'********************************************************


	   'Msgbox sURL
	REM target document
	Dim doc, sheets, sName$, pos%
	   doc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_default",0, Array())
	   sheets = doc.getSheets()
	  ' sName = getUniqueName(sheets, "Copied")
	   sName = getUniqueName(sheets, "Sheet1")
	   pos = 0

	REM new sheet
	Dim sh
	   sheets.insertNewByName(sName, pos)
	   sh = sheets.getByName(sName)

	REM link the new sheet
	   sh.link(sURL, sLinkSheetName, "calc8", "", com.sun.star.sheet.SheetLinkMode.VALUE)
	REM break link
	   sh.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)

	REM
	   sheets.removeByName("Sheet2")
	   sheets.removeByName("Sheet3")
	REM
	Dim cFile,cURL,oColumn,oCell,FilePicker,FPtype(0),FileN
	   oCell = Nothing
	   GlobalScope.BasicLibraries.LoadLibrary("Tools")
	   FileN=GetFileNameWithoutExtension(sURL,"/")
	   'FileN=GetFileNameWithoutExtension(sURL)
	   'msgbox "With Path : "+FileN


	'=======================
	' Added By Sandeep Thaker
	'==========================
		Dim currDate As String
	   Dim currTime As String
	   Dim TaxPayerPIN As String

	   currDate=Day(Now) & "-" & Month(Now) & "-" & Year(Now)
	   currTime=Hour(Now) & "-" & Minute(Now) & "-" & Second(Now)

	   TaxPayerPIN = 	   ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.TaxPayerPIN").string

	   FileN  = currDate + "_" +currTime+ "_" + TaxPayerPIN  + "_VAT.ods" 'Modified by Sandeep Thaker

	'=======================
	' End of Added By Sandeep Thaker
	'==========================

	   'cFile = "F:\MyCalc_upload" ' Windows
	   'cURL = ConvertToURL( cFile + "_upload.ods" )
	   cURL = FileN



	   'cFile = "F:\MyCalc_upload" ' Windows
	   'cURL = ConvertToURL( cFile + "_upload.ods" )
	   cURL = FileN
	   sh = sheets.getByName(sName)

	   For i=1 to 25
	    If i=10 Then
	    	i = i+1
	    End IF
	   	oColumn = sh.getColumns.getByIndex( i )
	   	oColumn.setPropertyValue("IsVisible", true)

	  Next
	   sh.CharColor = RGB(0,0,0)
	  oColumns = sh.getColumns()
	   ' Get a specific column.
   	  oColumn = oColumns.getByName( "A" )
	   ' Change width of column.
       oColumn.Width = 0 ' 0 cm

	   'oCell = sh.getCellRangeByName("B1")
	   'oCell.CharColor = RGB(0,0,0)

	   'oCell.setString("This sheet have hidden data please do not try to alter this file.")
 	   oCell = sh.getCellRangeByName("A3")
	   oCell.setString("VAT_RET")
   	   sh.Protect(Pwd)
	   oCell = sh.getCellRangeByName("C1")
	   'doc.storeAsURL( cURL, Array() )
	   'doc.Protect(Pwd)
	   oDocFrame = doc.getCurrentController().getFrame()
	   oDispatchHelper = createUnoService( "com.sun.star.frame.DispatchHelper" )
	  ' oDispatchHelper.executeDispatch( oDocFrame, ".uno:SaveAs", "", 0, Array() )
	   doc.Protect(Pwd)

  cURL =  ConvertFromUrl(fSaveFile(FileN,doc)) ' Modified By Sandeep Thaker

	   doc.close(True)
	   if cURL <> "" then
		   a5 = split(ConvertFromUrl(cURL),"\")
		   Dim destFileName as String
		   destFileName = a5(Ubound(a5))
		   dim makeNewFolderPath as String
		   makeNewFolderPath = ""
		   for i = Lbound(a5) to UBound(a5) - 1
		   	if i = Lbound(a5) then
		   		makeNewFolderPath =  a5(i)
		   	else
		   		makeNewFolderPath = makeNewFolderPath + "\" + a5(i)
		   	end if
		   next i
		   tod = NOW()
			tod = (Mid(tod, 1, 10))
			tod = join(split(tod, "/"), "-")
		   makeNewFolderPath = makeNewFolderPath + "\"
		   a4 = split(a5(Ubound(a5)),".")
		   zipPath = makeNewFolderPath + a4(0) + ".zip"
		   PutZipContent(ConvertToUrl(zipPath),destFileName,cURL, True)
		   Dim s As String
		   s = ConvertFromUrl(cURL)
		   Kill s
		   MsgBox "The zip file containing the sheet to be uploaded is saved at :"+ ConvertFromUrl(zipPath) '"File Saved at: "+ ConvertFromUrl(cURL) + " and
	   end if

end sub

Function getUniqueName(oContainer,sName$)
Dim i%,sNew$
   sNew = sName
   Do while oContainer.hasByName(sNew)
   	  oContainer.removeByName(sNew)
      'i = i +1
      'sNew = sName &"_"& i
   loop
   getUniqueName = sNew
End Function


  'ENDED BY JANHAVI ON 23.11.2011

Public Sub fillUploadSheet()
	Dim sheetCount As Integer, i As Integer, rowCount As Integer
	Dim finalSheet As String, propValuePair As String

	Dim PROP_SEP As String
	PROP_SEP = "@P_@"
	Dim CLASS_SEP As String
	CLASS_SEP = "#C_@"
	Dim VALUE_SEP As String
	VALUE_SEP = "%V_@"
	Dim MAIN_PROP_START As String
	MAIN_PROP_START = "#"
	Dim LIST_PROP_START As String
	LIST_PROP_START = "@L_@"

	Dim LIST_PROP_SEP As String
    LIST_PROP_SEP = "@PL@"
    Dim LIST_SEP As String
    LIST_SEP = "@L_@"
    Dim LIST_VALUE_SEP As String
    LIST_VALUE_SEP = "%VL@"



	Dim tempString As String, cellName As String
	Dim row As Long, column As Long, LastColumn As Long, LastRow As Long
	Dim currentWorkSheet As Object
	Dim cellRange As Object
	Dim nameCell As Object
	Dim str1 As String, str2 As String, str3 As String, str4 As String
	sheetCount = ThisComponent.Sheets.Count

	'Arjun
	Dim cellCounter as Long
	Dim listNameAppendFlag as Boolean
	listNameAppendFlag = False
	cellCounter=0

	If (sheetCount > 0) Then
		'*************************************************
		'Code added for amendment by Janhavi on 28.02.2012

   		if ThisComponent.getSheets().hasByName("Amendment") Then
			sheet1 = ThisComponent.Sheets.getByIndex(sheetCount - 2)
		Else
			sheet1 = ThisComponent.Sheets.getByIndex(sheetCount - 1)
		End If

		'*************************************************

	'	sheet1 = ThisComponent.Sheets.getByIndex(sheetCount - 1)
		ThisComponent.CurrentController.setActiveSheet(sheet1)
    	sheet1.Unprotect(Pwd)
    	finalSheet = sheet1.Name
     	sheet1.getCellByPosition(0,0).String = ""
     	For i = 0 To 255
			If sheet1.getCellByPosition(i,1).String<>"" then
			sheet1.getCellByPosition(i,1).String=""
			End If
		Next
		allRangeNames = ThisComponent.NamedRanges.getElementNames()
		Dim firstRowValue as String, secondRowValue As String
		firstRowValue = ""
	    secondRowValue = ""
		Dim validationSheet as object
    	validationSheet = ThisComponent.Sheets.getByName("Validations")
		For i = 1 To 65535
	    	If(validationSheet.getCellByPosition(0,i).getType() = EMPTYCELLTYPE) Then
	    		Exit For
	    	End If
	    	dim ccell as object
	    	ccell = validationSheet.getCellByPosition(3,i)
	    	If(validationSheet.getCellByPosition(3,i).String = "") Then
		    	Goto Continue
	    	End If
			rangeName  = validationSheet.getCellByPosition(2, i).String
	        Dim startRowNum as Long , endRowNum as Long, startColumnNum as Long , endcolumnNum as Long
	        oRange = ThisComponent.NamedRanges.getByName(rangeName)
	        startRowNum = oRange.getReferredCells().getRangeAddress().StartRow
	        endRowNum = oRange.getReferredCells().getRangeAddress().EndRow
	        startColumnNum = oRange.getReferredCells().getRangeAddress().StartColumn
	        endcolumnNum = oRange.getReferredCells().getRangeAddress().EndColumn

	        If (startRowNum = endRowNum and startColumnNum = endColumnNum) then
	        	cell = oRange.getReferredCells().getCellByPosition(0,0)
	        	cellValue = cell.value
    			if(cellValue = 0) then
    				cellValue = cell.String
    			End If
    			If Trim(firstRowValue) = "" Then
                    firstRowValue = firstRowValue & rangeName & VALUE_SEP & cellValue
                Else
                    firstRowValue = firstRowValue & PROP_SEP & rangeName & VALUE_SEP & cellValue
                End If
       		Else
       			If Trim(secondRowValue) = "" Then
		            secondRowValue = rangeName
		        Else
		            secondRowValue = secondRowValue & LIST_SEP & rangeName
		        End If

       			For multriEntryRowNum = 0 to endRowNum - startRowNum
	    			REM Check whether any column contains data
	    			REM for current multi-entry row using multriEntryRowNum, startColNum and endColNum
	    			REM If any column contains data then run the validationrules according to the range

	    			'Arjun
	    			If listNameAppendFlag = True Then
                        If Trim(secondRowValue) = "" Then
                            secondRowValue = rangeName
	                    Else
                            secondRowValue = secondRowValue & LIST_SEP & rangeName
                        End If
                        listNameAppendFlag = False
                    End If

	    			doValidation = False
	    			For multiEntryColNum = startColumnNum to endColumnNum
	    				Dim referredCells as object
	    				referredCells = oRange.getReferredCells()
	    				curCell = oRange.getReferredCells().getCellByPosition(multiEntryColNum,multriEntryRowNum)
	    				If(curCell.getType() <> EMPTYCELLTYPE) Then
	    				Dim formulaLng as Long
	    				formulaLng = 3
		    				If(curCell.getType() = formulaLng and curCell.String = "" and curCell.value = 0) Then

		    				else
		    					doValidation = True
		    					Exit For
							End If
	    				End If
	    			Next 'multiEntryColNum

	    			If (doValidation) Then
		    			Rem Do the validation for the row start from startmultyentryvalidationrow to endmultyentryvalidationrow
		    			For multiEntryColNum = startColumnNum to endColumnNum
			    			curCell = oRange.getReferredCells().getCellByPosition(multiEntryColNum,multriEntryRowNum)
			    			cellValue = curCell.value
			    			if(cellValue = 0) then
			    				cellValue = curCell.String
			    			End If
			    			If multiEntryColNum = startColumnNum Then
						    	secondRowValue = secondRowValue & LIST_PROP_SEP
						    End If
							If multiEntryColNum = endColumnNum Then
								secondRowValue = secondRowValue & cellValue

								sheet1.getCellByPosition(cellCounter,1).String=secondRowValue

									If Len(secondRowValue) > 12000 And Len(secondRowValue) < 16000 Then
										cellCounter=cellCounter+1
										secondRowValue=""
										listNameAppendFlag = True
									End If

							Else
								secondRowValue = secondRowValue & cellValue & LIST_VALUE_SEP
							End If
			    		Next 'multiEntryColNum
		    		End If
				Next multriEntryRowNum
			End If
			Continue:
		Next i
	End If
	sheet1.unprotect(Pwd)
	sheet1.getCellByPosition(0,0).String = firstRowValue
	'sheet1.getCellByPosition(0,1).String = secondRowValue
	'For generating hash codes.
	oDocScriptProvider = ThisComponent.getScriptProvider()
	oScriptHash = oDocScriptProvider.getScript("vnd.sun.star.script:Encrypt.SHA.js?language=JavaScript&location=document")

	sheet1.getCellByPosition(0,3).String =  oScriptHash.invoke(Array(sheet1.getCellByPosition(0,0).String), Array(), Array())

	For i = 0 To 255
		If sheet1.getCellByPosition(i,1).String<>"" then
			sheet1.getCellByPosition(i,4).String=oScriptHash.invoke(Array(sheet1.getCellByPosition(i,1).String), Array(), Array())
		End If
	Next
	sheet1.protect(Pwd)
End Sub


Function calc_SUBSTITUTE(vRange) As String

                     ' The first argument to callFunction() is the Calc Function Name
                     ' The 2nd argument is the parameters of that function as an array
   FuncAcc = createunoservice("com.sun.star.sheet.FunctionAccess")
	aArgs = array(vRange,",","")
   calc_SUBSTITUTE = FuncAcc.callFunction("SUBSTITUTE", aArgs)

End Function

'Function fSaveFile() as String
'   'Set the Dialog Arguments to a Template for FILESAVE
 '  sFilePickerArgs = Array(com.sun.star.ui.dialogs.TemplateDescription.FILESAVE_AUTOEXTENSION )

   'register the Service for Filepicker
  ' oFilePicker = CreateUnoService( "com.sun.star.ui.dialogs.FilePicker" )

   'Pass some arguments to it
 '  With oFilePicker
 '     .Initialize( sFilePickerArgs() )
 '     .setDisplayDirectory( "C:/" )
 '     .appendFilter("CSV Files (.csv)", "*.csv" )
 '     .setTitle( "Save As ..." )
 '  End With

   'If the savepath is selected return the complete path and display it in an messagebox
 '  If oFilePicker.execute() Then
 '     sFiles = oFilePicker.getFiles()
  '    fSaveFile = sFiles(0)
 '  End If

   ' Close the Dialog
 '  oFilePicker.Dispose()'
'End Function


'=========================================
'Default Name in Dialog box for saving file
'Added by Sandeep Thaker
'==========================================
Function fSaveFile(fileName as String,doc as Object) as String
   'Set the Dialog Arguments to a Template for FILESAVE

   sFilePickerArgs = Array(com.sun.star.ui.dialogs.TemplateDescription.FILESAVE_AUTOEXTENSION )

   'register the Service for Filepicker
   oFilePicker = CreateUnoService( "com.sun.star.ui.dialogs.FilePicker" )

   'Pass some arguments to it
   With oFilePicker
      .Initialize( sFilePickerArgs() )

       .appendFilter("ODS Files (.ods)", "*.ods" )
      .setTitle( "Save As ..." )
   .setDefaultName(fileName)
   End With

   'If the savepath is selected return the complete path and display it in an messagebox
   If oFilePicker.execute() Then
      sFiles = oFilePicker.getFiles()
      fSaveFile = sFiles(0)
      doc.storeToURL( fSaveFile, Array() )
   End If

   ' Close the Dialog
   oFilePicker.Dispose()

End Function
'==============================================
'End of Added by Sandeep Thaker
'==============================================


'*************NOT USED/EXRTA FUNCTIONS*********************************************
Public Sub Generate_upload()
	On Error GoTo Errorhandle

	Dim sURL$, sLinkSheetName$
	sURL = thisComponent.getURL()
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	FileN=GetFileNameWithoutExtension(sURL)
	FileN = fSaveFile() '"file:///D:/KRA Work/Excel/VAT Returns_up.csv"
	'	cURL = FileN

	'	doc.storeAsURL( cURL, Array() )
	'	MsgBox "File Saved at: "+ ConvertFromUrl(cURL)
	Dim oSFA As Object, oOutStream as Object, oOutText as Object
   	Dim sFilePath as String
   	oSFA = createUNOService ("com.sun.star.ucb.SimpleFileAccess")
    sFilePath = FileN '"C:\basefile.txt"
    If oSFA.exists(sFilePath) Then
      oSFA.kill(sFilePath) 'if file exists, delete it
    End If
   	sheetCount = ThisComponent.Sheets.Count

	If (sheetCount > 0) Then
		sheet1 = ThisComponent.Sheets.getByIndex(sheetCount - 1)
		firstRowValue = sheet1.getCellByPosition(0,0).String
		secondRowValue = sheet1.getCellByPosition(0,1).String
	End If

   oOutStream = oSFA.openFileWrite(sFilePath)
   oOutText = createUNOService ("com.sun.star.io.TextOutputStream")
   oOutText.setOutputStream(oOutStream)

   oOutText.WriteString(firstRowValue)
   oOutText.WriteString(chr(13))
   oOutText.WriteString(secondRowValue)
   oOutText.WriteString(chr(13))
   oOutText.WriteString(DigestStrToHexStr(firstRowValue + secondRowValue))
   oOutText.closeOutput()
   MsgBox ("No Error Found,Upload file is saved : """ + ConvertFromUrl(sFilePath) + """.")

Exit Sub

Errorhandle:
    MsgBox ("Modifications Are Not Saved,Upload File Not Generated") '& Err.Description)
    Exit Sub
    Resume
End Sub

Public Function validateExpoFormat(lstr_check As String) As Boolean
	'allowed characters 0 to 9 and .
	Dim i As Integer
	Dim ia As Integer
	Dim ina As Integer
	Dim stlen As Integer
	Dim dotCount As Integer
	Dim expCount as Integer
	Dim plusCount as Integer

	stlen = Len(lstr_check)
	ia = 0
	ina = 0
	validateExpoFormat = True
	For i = 1 To stlen
		Select Case (Mid(lstr_check, i, 1))
	    	Case "-"
	        	ia = ia + 1
	       	Case ","
	        	ia = ia + 1
	       	Case "."
	        	ia = ia + 1
	        	dotCount = dotCount + 1
	        Case "E"
	        	ia = ia + 1
	        	expCount = expCount + 1
	        Case "+"
	        	ia = ia + 1
	        	plusCount = plusCount + 1
	        Case "0" To "9"          '0 to 9
	        	ia = ia + 1
	        Case Else
	            ina = ina + 1
	            validateExpoFormat = False
	            Exit Function
	    End Select
	Next i
	If dotCount > 1 or expCount>1 or plusCount>1 Then
		validateExpoFormat = False
	End If
End Function

Public Function CompareSumValue() As Boolean
unprotectsheet("O_VAT_Due")
unprotectsheet("M_Sales_N_Purchases")

Dim value as Double
Dim value1 as Double
Dim sum1 as Double
Dim sum2 as Double

value =    ThisComponent.Sheets.getByName("O_VAT_Due").getCellrangeByName("TaxDue.TotalVatPurCharged").value
value1 =    ThisComponent.Sheets.getByName("O_VAT_Due").getCellrangeByName("TaxDue.AmountVatClaimableListTO").value
sum1 =     ThisComponent.Sheets.getByName("O_VAT_Due").getCellrangeByName("SecD.InputVatExemptSup").value
sum2 =     ThisComponent.Sheets.getByName("O_VAT_Due").getCellrangeByName("SecD.InputVatTaxbleExemptSup").value
If (Cdbl(format(value,"#,##0.00"))+Cdbl(format(value1,"#,##0.00")))=0 and (Cdbl(format(sum1,"#,##0.00"))+Cdbl(format(sum2,"#,##0.00")))=0 then
	CompareSumValue=True
Else
	If (Cdbl(format(value,"#,##0.00"))+Cdbl(format(value1,"#,##0.00")))>=0 Then
		If (Cdbl(format(value,"#,##0.00"))+Cdbl(format(value1,"#,##0.00")))<=(Cdbl(format(sum1,"#,##0.00"))+Cdbl(format(sum2,"#,##0.00"))) Then
		    CompareSumValue=False
		Else
		    CompareSumValue=True
		End If
	Else
		CompareSumValue=True
	End If
End If
protectsheet("M_Sales_N_Purchases")
protectsheet("O_VAT_Due")
End Function


Sub resetForAmendment()
	Call resetOnUsageToggle("Sch5.InputTaxPurchDtlsGRList","F_General_Rated_Purchases_Dtls")
	Call resetOnUsageToggle("Sch6.InputTaxPurchDtlsORList","G_Other_Rated_Purchases_Dtls")
	Call resetOnUsageToggle("Sch7.InputTaxPurchDtlsExemptList","H_Zero_Rated_Purchases_Dtls")
	Call resetOnUsageToggle("Sch8.InputTaxPurchDtlsExemptList","I_Exempted_Purchases_Dtls")
	Call resetOnUsageToggleExemption("Sch3.ZeroRateSalesDtlsList","D_Zero_Rated_Sales_Dtls")
	'Call resetPRNNumber("Sch10.VATPaidDtlsList","K_VAT_Payment_Credits")
	'Call resetPRNNumber("Sch10.VATSelfAssPaidDtlsList","K_VAT_Payment_Credits")
	'Call resetPRNNumberSpecial("Sch10.ClaimableServiceImportedList","J_VAT_Imported_Services_Dtls")
	Call resetTaxableValues("Sch1.GeneralRateSalesDtlsList","B_General_Rated_Sales_Dtls")
	Call resetTaxableValues("Sch2.OtherRateSalesDtlsList","C_Other_Rated_Sales_Dtls")
	Call resetTaxableValuesNew("Sch5.InputTaxPurchDtlsGRList","F_General_Rated_Purchases_Dtls")
	Call resetTaxableValuesNew("Sch6.InputTaxPurchDtlsORList","G_Other_Rated_Purchases_Dtls")
	Call resetTaxSingleCellCheck()
	'Disable 21 and 22 on Amendment
	unprotectsheet("O_VAT_Due")
	if ThisComponent.getSheets().hasByName("Amendment") Then

		sheet=ThisComponent.sheets.getByName("O_VAT_Due")
		oCells = ThisComponent.Sheets.getByName("O_VAT_Due").getCellRangeByName("SecD.CrdtBroughtFrwd")
		CellProtStruc = oCells.CellProtection

	    If CellProtStruc.isLocked = False then
		Special_Protect_Range("O_VAT_Due","SecD.CrdtBroughtFrwd")
		End If

		oCells = ThisComponent.Sheets.getByName("O_VAT_Due").getCellRangeByName("SecD.AddRefundClaimPaid")
		CellProtStruc = oCells.CellProtection

		If CellProtStruc.isLocked = False then
		'Special_Protect_Range("O_VAT_Due","SecD.AddRefundClaimPaid")
		End If
	Else
		sheet=ThisComponent.sheets.getByName("O_VAT_Due")
		oCells = ThisComponent.Sheets.getByName("O_VAT_Due").getCellRangeByName("SecD.CrdtBroughtFrwd")
		CellProtStruc = oCells.CellProtection

	    If CellProtStruc.isLocked = True then
		Special_Unprotect_Range("O_VAT_Due","SecD.CrdtBroughtFrwd")
		End If

		oCells = ThisComponent.Sheets.getByName("O_VAT_Due").getCellRangeByName("SecD.AddRefundClaimPaid")
		CellProtStruc = oCells.CellProtection

		If CellProtStruc.isLocked = True then
		'Special_Unprotect_Range("O_VAT_Due","SecD.AddRefundClaimPaid")
		End If
	End If
	protectsheet("O_VAT_Due")
End Sub

sub resetPRNNumber(rangeName as String,sheetName as String)

	    oRange = ThisComponent.NamedRanges.getByName(rangeName)
	    startRowNum = oRange.getReferredCells().getRangeAddress().StartRow
	    endRowNum = oRange.getReferredCells().getRangeAddress().EndRow
		sheet=ThisComponent.sheets.getByName(sheetName)
		oColRange = sheet.getCellrangeByName("A"&(startRowNum+1)&":A"&(endRowNum+1))

		Dim prnNo as String
		Dim prnNoObject as Object
	'***
		for i = startRowNum to endRowNum
			row=i
			prnNoObject=sheet.getCellrangeByName("A"&(row+1)&":A"&(row+1))
			prnNo= sheet.getCellrangeByName("A"&(row+1)&":A"&(row+1)).string
			sheet.getCellrangeByName("A"&(row+1)&":A"&(row+1)).string=Cstr(Format(prnNo,0))
		Next

end Sub

sub resetPRNNumberSpecial(rangeName as String,sheetName as String)

	    oRange = ThisComponent.NamedRanges.getByName(rangeName)
	    startRowNum = oRange.getReferredCells().getRangeAddress().StartRow
	    endRowNum = oRange.getReferredCells().getRangeAddress().EndRow
		sheet=ThisComponent.sheets.getByName(sheetName)
		oColRange = sheet.getCellrangeByName("F"&(startRowNum+1)&":F"&(endRowNum+1))

		Dim prnNo as String
		Dim prnNoObject as Object
	'***
		for i = startRowNum to endRowNum
			row=i
			prnNoObject=sheet.getCellrangeByName("F"&(row+1)&":F"&(row+1))
			prnNo= sheet.getCellrangeByName("F"&(row+1)&":F"&(row+1)).string
			sheet.getCellrangeByName("F"&(row+1)&":F"&(row+1)).string=Cstr(Format(prnNo,0))
		Next

end Sub

sub resetTaxableValues(rangeName as String,sheetName as String)

		Dim startRowNum as Long , endRowNum as Long, startColumnNum as Long , endcolumnNum as Long
	    oRange = ThisComponent.NamedRanges.getByName(rangeName)
	    startRowNum = oRange.getReferredCells().getRangeAddress().StartRow
	    endRowNum = oRange.getReferredCells().getRangeAddress().EndRow
		sheet=oRange.getReferredCells().getRangeAddress().sheet

		Unprotectsheet(sheetName)
		Dim usage as String
		Dim msgStr As String
		Dim currRange as Object
		Dim row As Long
		Dim tempValue as Double
		Dim tempValueString as String
		sheet=ThisComponent.sheets.getByName(sheetName)

		for i = startRowNum to endRowNum
				row=i
				taxableValue=sheet.getCellrangeByName("H"&(row+1)&":H"&(row+1)).value
'************
		If taxableValue<0 then
			tempValueString=sheet.getCellrangeByName("I"&(row+1)&":I"&(row+1)).string
			Unprotect_Range(sheetName,"I" & (row+1) & ":I" & (row+1))
			sheet.getCellrangeByName("I"&(row+1)&":I"&(row+1)).value=tempValueString
			tempValueString=sheet.getCellrangeByName("J"&(row+1)&":J"&(row+1)).string
            Unprotect_Range(sheetName,"J" & (row+1) & ":J" & (row+1))
            sheet.getCellrangeByName("J"&(row+1)&":J"&(row+1)).string=tempValueString
		Else
			sheet.getCellrangeByName("I"&(row+1)&":I"&(row+1)).string=""
            Protect_Range(sheetName,"I" & (row+1) & ":I" & (row+1))
            sheet.getCellrangeByName("J"&(row+1)&":J"&(row+1)).string=""
            Protect_Range(sheetName,"J" & (row+1) & ":J" & (row+1))
		End If
'************
	   Next
	protectsheet(sheetName)
end Sub

sub resetTaxableValuesNew(rangeName as String,sheetName as String)

		Dim startRowNum as Long , endRowNum as Long, startColumnNum as Long , endcolumnNum as Long
	    oRange = ThisComponent.NamedRanges.getByName(rangeName)
	    startRowNum = oRange.getReferredCells().getRangeAddress().StartRow
	    endRowNum = oRange.getReferredCells().getRangeAddress().EndRow
		sheet=oRange.getReferredCells().getRangeAddress().sheet

		Unprotectsheet(sheetName)
		Dim usage as String
		Dim msgStr As String
		Dim currRange as Object
		Dim row As Long
		Dim tempValue as Double
		Dim tempValueString as String

		sheet=ThisComponent.sheets.getByName(sheetName)

		for i = startRowNum to endRowNum
				row=i
				taxableValue=sheet.getCellrangeByName("H"&(row+1)&":H"&(row+1)).value
'************
		If taxableValue<0 then
			tempValueString=sheet.getCellrangeByName("J"&(row+1)&":J"&(row+1)).string
			Unprotect_Range(sheetName,"J" & (row+1) & ":J" & (row+1))
			sheet.getCellrangeByName("J"&(row+1)&":J"&(row+1)).value=tempValueString
			tempValueString=sheet.getCellrangeByName("K"&(row+1)&":K"&(row+1)).string
            Unprotect_Range(sheetName,"K" & (row+1) & ":K" & (row+1))
            sheet.getCellrangeByName("K"&(row+1)&":K"&(row+1)).string=tempValueString
		Else
			sheet.getCellrangeByName("J"&(row+1)&":J"&(row+1)).string=""
            Protect_Range(sheetName,"J" & (row+1) & ":J" & (row+1))
            sheet.getCellrangeByName("K"&(row+1)&":K"&(row+1)).string=""
            Protect_Range(sheetName,"K" & (row+1) & ":K" & (row+1))
		End If
'************
	   Next
	protectsheet(sheetName)
end Sub

sub resetOnUsageToggle(rangeName as String,sheetName as String)

		Dim startRowNum as Long , endRowNum as Long, startColumnNum as Long , endcolumnNum as Long
	    oRange = ThisComponent.NamedRanges.getByName(rangeName)
	    startRowNum = oRange.getReferredCells().getRangeAddress().StartRow
	    endRowNum = oRange.getReferredCells().getRangeAddress().EndRow
		sheet=oRange.getReferredCells().getRangeAddress().sheet

		Unprotectsheet(sheetName)
		Dim usage as String
		Dim msgStr As String
		Dim currRange as Object
		Dim row As Long
		Dim tempValue as String

		sheet=ThisComponent.sheets.getByName(sheetName)

		for i = startRowNum to endRowNum
				row=i
				selectedPurchaserCode=sheet.getCellrangeByName("A"&(row+1)&":A"&(row+1)).string
'************
				If selectedPurchaserCode <> "" Then

				 	'disable Exemption Certificate Number in case "Exempt"
	                If selectedPurchaserCode = "Import" Then
	                	sheet.getCellrangeByName("L"&(row+1)&":L"&(row+1)).string="Import"
	                	sheet.getCellrangeByName("B"&(row+1)&":B"&(row+1)).string=""
	                    Protect_Range(sheetName,"B" & (row+1) & ":B" & (row+1))
	                    tempValue=sheet.getCellrangeByName("G"&(row+1)&":G"&(row+1)).string
	                    Unprotect_Range(sheetName,"G" & (row+1) & ":G" & (row+1))
	                    sheet.getCellrangeByName("G"&(row+1)&":G"&(row+1)).string=tempValue
	                ElseIf selectedPurchaserCode = "Local" Then
	                	sheet.getCellrangeByName("L"&(row+1)&":L"&(row+1)).string="Local"
	                	tempValue=sheet.getCellrangeByName("B"&(row+1)&":B"&(row+1)).string
	                    Unprotect_Range(sheetName,"B" & (row+1) & ":B" & (row+1))
	                    sheet.getCellrangeByName("B"&(row+1)&":B"&(row+1)).string=tempValue
	                    sheet.getCellrangeByName("G"&(row+1)&":G"&(row+1)).string=""
	                    Protect_Range(sheetName,"G" & (row+1) & ":G" & (row+1))
		     		End If
	     		Else
                	sheet.getCellrangeByName("L"&(row+1)&":L"&(row+1)).string=""
                	tempValue=sheet.getCellrangeByName("B"&(row+1)&":B"&(row+1)).string
                	Unprotect_Range(sheetName,"B" & (row+1) & ":B" & (row+1))
                	sheet.getCellrangeByName("B"&(row+1)&":B"&(row+1)).string=tempValue
                    sheet.getCellrangeByName("G"&(row+1)&":G"&(row+1)).string=""
                    Protect_Range(sheetName,"G" & (row+1) & ":G" & (row+1))
         		End If
'************
	   Next
	protectsheet(sheetName)
end Sub

Sub resetTaxSingleCellCheck()
	unprotectsheet("M_Sales_N_Purchases")
	unprotectsheet("O_VAT_Due")
	Dim sheet as Object
	Dim msgStr As String
	Dim salesExemptValue as Double
	Dim tempValue as Double
	Dim taxableSalesGen as Double
	Dim taxableSalesOther as Double
	Dim taxableSales as Double

	sheet=ThisComponent.sheets.getByName("M_Sales_N_Purchases")
	salesExemptValue= sheet.getCellrangeByName("Sales.ExemptSalesDtlsTO").value
	taxableSalesGen=sheet.getCellrangeByName("Sales.GeneralRateSalesDtlsTO").value
	taxableSalesOther=sheet.getCellrangeByName("Sales.OtherRateSalesDtlsTO").value
	taxableSales=sheet.getCellrangeByName("Sales.ZeroRateSalesSecASecBTO").value

   If salesExemptValue>0 and (taxableSalesGen>0 or taxableSalesOther>0 or taxableSales>0) then
   		sheet=ThisComponent.sheets.getByName("O_VAT_Due")
   		'enable
   		tempValue=sheet.getCellrangeByName("SecD.InputVatExemptSup").value
   		Unprotect_Range("O_VAT_Due","SecD.InputVatExemptSup")
   		sheet.getCellrangeByName("SecD.InputVatExemptSup").value=tempValue
   		tempValue=sheet.getCellrangeByName("SecD.InputVatTaxbleExemptSup").value
   		Unprotect_Range("O_VAT_Due","SecD.InputVatTaxbleExemptSup")
   		sheet.getCellrangeByName("SecD.InputVatTaxbleExemptSup").value=tempValue
   Else
   		sheet=ThisComponent.sheets.getByName("O_VAT_Due")
   		'disable
   		sheet.getCellrangeByName("SecD.InputVatExemptSup").string=""
        Protect_Range("O_VAT_Due","SecD.InputVatExemptSup")
        sheet.getCellrangeByName("SecD.InputVatTaxbleExemptSup").string=""
        Protect_Range("O_VAT_Due","SecD.InputVatTaxbleExemptSup")
   End If


	protectsheet("O_VAT_Due")
	protectsheet("M_Sales_N_Purchases")
	Call EnableDisableBasedOnBAndC()
End Sub


Public Function RelevantDateCheck(ByVal value As String) As Boolean

Dim startDate As String
Dim endDate As String
Dim sheet As Object

	Dim date1 as Object
	Dim date2 as Object
	Dim dd1 as Integer
	Dim mm1 as Integer
	Dim yy1 as Integer
	Dim dd2 as Integer
	Dim mm2 as Integer
	Dim yy2 as Integer


startDate =    ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdFrom").string
endDate =     ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdTo").string

	date1= Split(value, "/")
	dd1= INT(date1(0))
	mm1= INT(date1(1))
	yy1= INT(date1(2))

	date2= Split(endDate, "/")
	dd2= INT(date2(0))
	mm2= INT(date2(1))
	yy2= INT(date2(2))

	If checkGrtrThnDate(Cstr(Format(DateSerial(yy2-1, mm2, dd2),"dd/mm/yyyy")),dateVal)=False Then
       RelevantDateCheck = False
    ElseIf checkFutureDate(value)=False or checkGrtrThnDate(value,endDate)=False then
       RelevantDateCheck = False
    Else
       RelevantDateCheck = True
    End If

End Function


Public Function lessCompareTotals(totalRangeName As String,cellValue As Double) As Boolean
'unprotect all sheets
for i=0 to ThisComponent.Sheets.Count - 1
	ActiveSheet = ThisComponent.sheets.getByIndex(i)
	ActiveSheet.unprotect(Pwd)
next i

Dim sheetCompareName as String
Dim CompareRangeName as String

'get rangeNames
if(totalRangeName="SecD.AddRefundClaimPaid")then
	sheetCompareName="O_VAT_Due"
	CompareRangeName="SecD.CrdtBroughtFrwd"
elseif(totalRangeName="")then
	sheetCompareName=""
	CompareRangeName=""
elseif(totalRangeName="")then
	sheetCompareName=""
	CompareRangeName=""
End If

Dim value1 as Double
Dim value2 as Double
value1 =    cellValue
value2 =    ThisComponent.Sheets.getByName(sheetCompareName).getCellrangeByName(CompareRangeName).value

If Cdbl(format(value1,"#,##0.00"))>Cdbl(format(value2,"#,##0.00")) then
	lessCompareTotals=False
Else
	lessCompareTotals=True
End If

'protect all sheets
for i=0 to ThisComponent.Sheets.Count - 1
	ActiveSheet = ThisComponent.sheets.getByIndex(i)
	ActiveSheet.protect (Pwd)
next i

errorSheet = ThisComponent.Sheets.getByName("Errors")
	errorSheet.Unprotect (Pwd)

validationSheet = ThisComponent.Sheets.getByName("Validations")
    validationSheet.UnProtect (Pwd)

End Function

Public Function checkGrtrThnDate(ByVal dateFrom As String,ByVal dateTo as String) As Boolean
Dim mm As Integer
Dim yr As Integer
Dim dd as Integer
Dim strDate As Object
Dim currDate as Object
'mm = Format(CDate(Trim(value)), "MM")
'yr = Format(CDate(Trim(value)), "yyyy")
'dd = Format(CDate(Trim(value)), "dd")
If(Len(Trim(dateFrom))=0) then
	checkGrtrThnDate=True
	Exit function
End If
If(Len(Trim(dateTo))=0) then
	checkGrtrThnDate=True
	Exit function
End If
if ValidateDate(dateFrom) then
 strDate= Split(dateFrom, "/")
 dd= INT(strDate(0))
 mm= INT(strDate(1))
 yr= INT(strDate(2))

currDate= Split(dateTo,"/")
'If yr = Format(Now(), "yyyy") Then
If yr= INT(currDate(2)) then
	Dim mon as Integer
	Dim dayD as Integer
	mon= INT(currDate(1))
	dayD = INT(currDate(0))
    If mm < mon Then
      checkGrtrThnDate = True
    ElseIf mm=mon then
    	If dd<=dayD then
    		checkGrtrThnDate = True
    	Else
	      checkGrtrThnDate = False
	    End If
	Else
	 checkGrtrThnDate = False
	End If

ElseIf yr < currDate(2) And mm <= 12 Then
        checkGrtrThnDate = True
	Else
    	checkGrtrThnDate = False
End If
End If
End Function



Public Function checkGrtrThnDateForInvoiceDate(ByVal rtnPrdTo As String,ByVal invoiceDate as String) As Boolean
Dim mmRtnTo As Integer
Dim yrRtnTo As Integer
Dim ddRtnTo as Integer
Dim strDate As Object
Dim currDate as Object

Dim mmInvDt As Integer
Dim yrInvDt As Integer
Dim ddInvDt As Integer

Dim tempMonth As Integer

If(Len(Trim(rtnPrdTo))=0) then
	checkGrtrThnDateForInvoiceDate=True
	Exit function
End If
If(Len(Trim(invoiceDate))=0) then
	checkGrtrThnDateForInvoiceDate=True
	Exit function
End If
if ValidateDate(rtnPrdTo) then
 strDate= Split(rtnPrdTo, "/")

 ddRtnTo= INT(strDate(0))
 mmRtnTo= INT(strDate(1))
 yrRtnTo= INT(strDate(2))

if ValidateDate(invoiceDate) then
currDate= Split(invoiceDate,"/")

 ddInvDt= INT(currDate(0))
 mmInvDt= INT(currDate(1))
 yrInvDt= INT(currDate(2))

'year of invoice date and rtnPrdTo is same then ...
		If yrRtnTo= yrInvDt then

			if mmInvDt=mmRtnTo then
				checkGrtrThnDateForInvoiceDate = True
		    elseif mmInvDt<mmRtnTo and (mmRtnTo-mmInvDt)<=6 Then
		      checkGrtrThnDateForInvoiceDate = True
		    Else
			      checkGrtrThnDateForInvoiceDate = False
		    End If
'if Year of invoice Date and rtnPrdTo is different

		elseIf yrInvDt<yrRtnTo then
			tempMonth=mmRtnTo+6
			if mmInvDt>mmRtnTo and tempMonth<=mmInvDt then
			checkGrtrThnDateForInvoiceDate = True
		    Else
			      checkGrtrThnDateForInvoiceDate = False
			END if

		else
		checkGrtrThnDateForInvoiceDate = True
		End If
End If
End If
End Function


Public Function specificDateBeforeYears(ByVal value As String,ByVal yearGap as Integer) As String
Dim currDt as Object
Dim newDt as String
Dim intYear
'Dim a
'a = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12")

If value<>"" Then
		currDt= Split(value,"/")
		'Day & Month
		newDt=currDt(0)& "/" & currDt(1)
		'Year
		intYear=INT(currDt(2))
		intYear=intYear-yearGap
		newDt=newDt & "/" & Cstr(intYear)
		'msgbox newDt
		specificDateBeforeYears=newDt
	End If

End Function

Public Function checkDateOfDeposit(ByVal value As String) As Boolean
Dim startDate as String
Dim endDate as String

checkDateOfDeposit = True

unprotectsheet("A_Basic_Info")
'->Arjun
if(ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdFrom").string<>"" and ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdTo").string<>"")then
	startDate =    ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdFrom").string
	endDate =    ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdTo").string
	If value<>"" then
	          If  ValidateDate(value) then
		           If  checkGrtrThnDate(value,endDate)=False then
		   		   			checkDateOfDeposit = False
		  	       End If
			  Else
			  		checkDateOfDeposit = False
			  End If
	         End If
Else
	checkDateOfDeposit = True
End If
protectsheet("A_Basic_Info")
End Function

Public Function TestAlphabetNumNoSpl(lstr_check As String) As Boolean
'allowed characters A to Z, a to z And Blank
Dim i As Integer
Dim ia As Integer
Dim ina As Integer
Dim stlen As Integer

stlen = Len(lstr_check)
ia = 0
ina = 0
For i = 1 To stlen
    Select Case (Mid(lstr_check, i, 1))
        Case "A" To "Z"          'A to Z
            ia = ia + 1
        Case "a" To "z"          'a to z
            ia = ia + 1
        Case "0" To "9"          '0 to 9
            ia = ia + 1
        Case Else
            ina = ina + 1
    End Select
Next i
If ina = 0 Then
    TestAlphabetNumNoSpl = True
Else
    TestAlphabetNumNoSpl = False
End If

End Function

sub resetOnUsageToggleExemption(rangeName as String,sheetName as String)

		Dim startRowNum as Long , endRowNum as Long, startColumnNum as Long , endcolumnNum as Long
	    oRange = ThisComponent.NamedRanges.getByName(rangeName)
	    startRowNum = oRange.getReferredCells().getRangeAddress().StartRow
	    endRowNum = oRange.getReferredCells().getRangeAddress().EndRow
		sheet=oRange.getReferredCells().getRangeAddress().sheet

		Unprotectsheet(sheetName)
		Dim usage as String
		Dim msgStr As String
		Dim currRange as Object
		Dim row As Long
		Dim tempValue as String

		sheet=ThisComponent.sheets.getByName(sheetName)

		for i = startRowNum to endRowNum
				row=i
				selectedPurchaserCode=sheet.getCellrangeByName("A"&(row+1)&":A"&(row+1)).string
'************
				If selectedPurchaserCode <> "" Then

				 	'disable Exemption Certificate Number in case "Exempt"
	                If selectedPurchaserCode = "Local" Then
	                	sheet.getCellrangeByName("K"&(row+1)&":K"&(row+1)).string="Import"
	                	sheet.getCellrangeByName("I"&(row+1)&":I"&(row+1)).string=""
	                    Protect_Range(sheetName,"I" & (row+1) & ":I" & (row+1))
	                ElseIf selectedPurchaserCode = "Exemption" Then
	                	sheet.getCellrangeByName("K"&(row+1)&":K"&(row+1)).string="Exempt"
	                	tempValue=sheet.getCellrangeByName("I"&(row+1)&":I"&(row+1)).string
	                    Unprotect_Range(sheetName,"I" & (row+1) & ":I" & (row+1))
	                    sheet.getCellrangeByName("I"&(row+1)&":I"&(row+1)).string=tempValue
		     		End If
	     		Else
                	sheet.getCellrangeByName("K"&(row+1)&":K"&(row+1)).string=""
                	sheet.getCellrangeByName("I"&(row+1)&":I"&(row+1)).string=""
                    Protect_Range(sheetName,"I" & (row+1) & ":I" & (row+1))
         		End If
'************
	   Next
	protectsheet(sheetName)
end Sub

Public Function TestAlphabetNumSpaceDot(lstr_check As String) As Boolean
'allowed characters A to Z, a to z And Blank
Dim i As Integer
Dim ia As Integer
Dim ina As Integer
Dim stlen As Integer

stlen = Len(lstr_check)
ia = 0
ina = 0
For i = 1 To stlen
    Select Case (Mid(lstr_check, i, 1))
        Case "A" To "Z"          'A to Z
            ia = ia + 1
        Case "a" To "z"          'a to z
            ia = ia + 1
        Case "0" To "9"          '0 to 9
            ia = ia + 1
        Case " ", "." ', ".", "-", "/",":" '";", "-", "#", "+", "*", "/", "=", "@", "$", "&", "%" , "(", ")" ,">", "<"   ' Blank
            ia = ia + 1
        Case Else
            ina = ina + 1
    End Select
Next i
If ina = 0 Then
    TestAlphabetNumSpaceDot = True
Else
    TestAlphabetNumSpaceDot = False
End If

End Function

Function PutZipContent( sZipURL As String, sContentName As String, sInputURL As String, Optional bCompress As Boolean )
  Dim oZipPkg As Object, oSFA As Object
  Dim oContentStream As Object, oZipFolder As Object
  oZipPkg = CreateUnoService("com.sun.star.packages.Package")
  oZipPkg.initialize(array(sZipURL))
  oZipFolder = oZipPkg.getByHierarchicalName("/")
  oContentStream = oZipPkg.createInstanceWithArguments(array(false))
  oSFA = CreateUnoService("com.sun.star.ucb.SimpleFileAccess")
  If oSFA.exists(sInputURL) Then
    oContentStream.setInputStream(oSFA.openFileRead(sInputURL))

    If IsMissing(bCompress) Then bCompress = True
    oContentStream.setPropertyValue("Compressed", bCompress)
    If NOT oZipFolder.hasByName(sContentName) Then
      oZipFolder.insertByName(sContentName,oContentStream)
    Else
      oZipFolder.replaceByName(sContentName,oContentStream)
    End If
    oZipPkg.commitChanges()
  End If
End Function

Sub checkRelevantInvNoDate(rangeName as String)as Boolean

	checkRelevantInvNoDate=True

	Dim startRowNum as Long , endRowNum as Long, startColumnNum as Long , endcolumnNum as Long
	Dim startRowNumInternal as Long , endRowNumInternal as Long, startColumnNumInternal as Long , endcolumnNumInternal as Long
	Dim sheetName as String
	Dim revInvNumCol as String
	Dim revInvDateCol as String
	Dim revInvNum as String
	Dim revInvDate as String
	Dim invNum as String
	Dim invDate as String
	Dim mainPinCol as String
	Dim mainPin as String
	Dim internalPin as String
	Dim taxableValue as Double
	Dim taxableValueCol as String
	Dim exists as Boolean

	exists=False

	If rangeName="Sch1.GeneralRateSalesDtlsList" Then
		sheetName="B_General_Rated_Sales_Dtls"
		revInvNumCol="I"
		revInvDateCol="J"
		mainPinCol="A"
		taxableValueCol="G"
	ElseIf rangeName="Sch2.OtherRateSalesDtlsList" Then
		sheetName="C_Other_Rated_Sales_Dtls"
		revInvNumCol="I"
		revInvDateCol="J"
		mainPinCol="A"
		taxableValueCol="G"
	ElseIf rangeName="Sch5.InputTaxPurchDtlsGRList" Then
		sheetName="F_General_Rated_Purchases_Dtls"
		revInvNumCol="J"
		revInvDateCol="K"
		mainPinCol="B"
		taxableValueCol="H"
	ElseIf rangeName="Sch6.InputTaxPurchDtlsORList" Then
		sheetName="G_Other_Rated_Purchases_Dtls"
		revInvNumCol="J"
		revInvDateCol="K"
		mainPinCol="B"
		taxableValueCol="H"
	Else
		sheetName=""
		revInvNumCol=""
		revInvDateCol=""
		mainPinCol=""
		taxableValueCol=""
	End If

	If sheetName<>"" and revInvNumCol<>"" and revInvDateCol<>"" and mainPinCol<>"" and taxableValueCol<>"" then

	    oRange = ThisComponent.NamedRanges.getByName(rangeName)
	    startRowNum = oRange.getReferredCells().getRangeAddress().StartRow
	    endRowNum = oRange.getReferredCells().getRangeAddress().EndRow
	    startRowNumInternal = oRange.getReferredCells().getRangeAddress().StartRow
	    endRowNumInternal = oRange.getReferredCells().getRangeAddress().EndRow
		sheet=ThisComponent.sheets.getByName(sheetName)

		for i=startRowNum to endRowNum

			 revInvNum=sheet.getCellrangeByName(revInvNumCol &(i+1)&":"& revInvNumCol &(i+1)).string
			 revInvDate=sheet.getCellrangeByName(revInvDateCol &(i+1)&":"& revInvDateCol &(i+1)).string
			 mainPin=sheet.getCellrangeByName(mainPinCol &(i+1)&":"& mainPinCol &(i+1)).string

			 exists=False

			 If revInvNum<>"" and revInvDate<>"" then
					 If (ValidateDate(revInvDate) = True) Then
						 If TestDateBtwnRtnPeriod(revInvDate)=True then

							 for j=startRowNumInternal to endRowNumInternal

								 invNum=sheet.getCellrangeByName("E"&(j+1)&":E"&(j+1)).string
								 invDate=sheet.getCellrangeByName("D"&(j+1)&":D"&(j+1)).string
								 internalPin=sheet.getCellrangeByName(mainPinCol &(j+1)&":"& mainPinCol &(j+1)).string
								 taxableValue=sheet.getCellrangeByName(taxableValueCol &(j+1)&":"& taxableValueCol  &(j+1)).value

							         if revInvNum=invNum and revInvDate=invDate and mainPin=internalPin and taxableValue>=0 then
							         	exists=True
									 End If
							 Next j

							 If exists=False then
							 	checkRelevantInvNoDate=False
							 	Exit For
							 End If
						  End If
					End If
			End If
		 Next i
	End If
End Sub

Public Function checkDateOfDepositSelfAssess(ByVal value As String) As Boolean
Dim startDate as String

checkDateOfDepositSelfAssess = True

unprotectsheet("A_Basic_Info")
'->Arjun
if(ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdFrom").string<>"" and ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdTo").string<>"")then
	startDate =    ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdFrom").string
	endDate = ThisComponent.Sheets.getByName("A_Basic_Info").getCellrangeByName("SecA.RtnPdTo").string
	If value<>"" then
	          If  ValidateDate(value) then
		           If  checkFutureDate(value) = False Then
		   	   		    	checkDateOfDepositSelfAssess = False
		   		   ElseIf  checkGrtrThnNotEqlDate(endDate,value)=False then
		   		   			checkDateOfDepositSelfAssess = False
		  	       End If
			  Else
			  		checkDateOfDepositSelfAssess = False
			  End If
	         End If
Else
	checkDateOfDepositSelfAssess = True
End If
protectsheet("A_Basic_Info")
End Function

Sub prnEntriesBothSch() as Boolean
	Dim taxDue as Double
	Dim prnAdvPmt as Double
	Dim prnTotal as Double

	prnEntriesBothSch=True

	taxDue=ThisComponent.Sheets.getByName("O_VAT_Due").getCellrangeByName("SecD.TotalVatPyble").value
	prnAdvPmt=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATPaidDtlsTO").value
	prnTotal=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATAdvcSelfAssPaidDtlsTO").value
	prnSelfAssess=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATSelfAssPaidDtlsTO").value

	If (Cdbl(format(taxDue,"#,##0.00")))<=0 and (Cdbl(format(prnTotal,"#,##0.00")))>0 then
		prnEntriesBothSch=False
	End If
End Sub

Sub prnEntriesSelfAssess() as Boolean
	Dim taxDue as Double
	Dim prnAdvPmt as Double
	Dim prnTotal as Double
	Dim prnSelfAssess as Double

	prnEntriesSelfAssess=True

	taxDue=ThisComponent.Sheets.getByName("O_VAT_Due").getCellrangeByName("SecD.TotalVatPyble").value
	prnAdvPmt=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATPaidDtlsTO").value
	prnTotal=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATAdvcSelfAssPaidDtlsTO").value
	prnSelfAssess=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATSelfAssPaidDtlsTO").value

	If (Cdbl(format(prnSelfAssess,"#,##0.00")))>0 and (Cdbl(format(prnAdvPmt,"#,##0.00")))>0 then
		If (Cdbl(format(taxDue,"#,##0.00")))<=(Cdbl(format(prnSelfAssess,"#,##0.00"))) and (Cdbl(format(prnAdvPmt,"#,##0.00")))>0 then
			prnEntriesSelfAssess=False
		ElseIf (Cdbl(format(taxDue,"#,##0.00")))<(Cdbl(format(prnSelfAssess,"#,##0.00")))+(Cdbl(format(prnAdvPmt,"#,##0.00"))) and (Cdbl(format(prnAdvPmt,"#,##0.00")))>0 then
			prnEntriesSelfAssess=False
		End If
	End If
End Sub

Sub advPaymentGreaterThanLiability() as Boolean
	Dim taxDue as Double
	Dim prnAdvPmt as Double
	Dim prnTotal as Double
	Dim prnSelfAssess as Double

	advPaymentGreaterThanLiability=True

	taxDue=ThisComponent.Sheets.getByName("O_VAT_Due").getCellrangeByName("SecD.TotalVatPyble").value
	prnAdvPmt=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATPaidDtlsTO").value
	prnTotal=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATAdvcSelfAssPaidDtlsTO").value
	prnSelfAssess=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATSelfAssPaidDtlsTO").value

	If (Cdbl(format(taxDue,"#,##0.00")))>0 then
		If (Cdbl(format(prnSelfAssess,"#,##0.00")))<=0 then
			If (Cdbl(format(taxDue,"#,##0.00")))<(Cdbl(format(prnAdvPmt,"#,##0.00"))) then
				advPaymentGreaterThanLiability=False
			End If
		End If
	End If
End Sub

Sub selfPaymentGreaterThanLiability() as Boolean
	Dim taxDue as Double
	Dim prnAdvPmt as Double
	Dim prnTotal as Double
	Dim prnSelfAssess as Double

	selfPaymentGreaterThanLiability=True

	taxDue=ThisComponent.Sheets.getByName("O_VAT_Due").getCellrangeByName("SecD.TotalVatPyble").value
	prnAdvPmt=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATPaidDtlsTO").value
	prnTotal=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATAdvcSelfAssPaidDtlsTO").value
	prnSelfAssess=ThisComponent.Sheets.getByName("K_VAT_Payment_Credits").getCellrangeByName("Sch10.VATSelfAssPaidDtlsTO").value

	If (Cdbl(format(taxDue,"#,##0.00")))>0 then
		If (Cdbl(format(prnAdvPmt,"#,##0.00")))<=0 then
			If (Cdbl(format(taxDue,"#,##0.00")))<(Cdbl(format(prnSelfAssess,"#,##0.00"))) then
				selfPaymentGreaterThanLiability=False
			End If
		End If
	End If
End Sub

Public Function checkGrtrThnNotEqlDate(ByVal dateFrom As String,ByVal dateTo as String) As Boolean
Dim mm As Integer
Dim yr As Integer
Dim dd as Integer
Dim strDate As Object
Dim currDate as Object
'mm = Format(CDate(Trim(value)), "MM")
'yr = Format(CDate(Trim(value)), "yyyy")
'dd = Format(CDate(Trim(value)), "dd")
If(Len(Trim(dateFrom))=0) then
	checkGrtrThnNotEqlDate=True
	Exit function
End If
If(Len(Trim(dateTo))=0) then
	checkGrtrThnNotEqlDate=True
	Exit function
End If
if ValidateDate(dateFrom) then
 strDate= Split(dateFrom, "/")
 dd= INT(strDate(0))
 mm= INT(strDate(1))
 yr= INT(strDate(2))

currDate= Split(dateTo,"/")
'If yr = Format(Now(), "yyyy") Then
If yr= INT(currDate(2)) then
	Dim mon as Integer
	Dim dayD as Integer
	mon= INT(currDate(1))
	dayD = INT(currDate(0))
    If mm < mon Then
      checkGrtrThnNotEqlDate = True
    ElseIf mm=mon then
    	If dd<dayD then
    		checkGrtrThnNotEqlDate = True
    	Else
	      checkGrtrThnNotEqlDate = False
	    End If
	Else
	 checkGrtrThnNotEqlDate = False
	End If

ElseIf yr < currDate(2) And mm <= 12 Then
        checkGrtrThnNotEqlDate = True
	Else
    	checkGrtrThnNotEqlDate = False
End If
End If
End Function

Public Function TestPRN(lstr_check As String) As Boolean
'allowed characters A to Z, a to z And Blank
Dim i As Integer
Dim ia As Integer
Dim ina As Integer
Dim stlen As Integer
Dim count As Integer

stlen = Len(lstr_check)
ia = 0
ina = 0
count = 0
For i = 1 To stlen
    Select Case (Mid(lstr_check, i, 1))
        Case "0" To "9"          '0 to 9
            ia = ia + 1
        Case "-"                 ' Hypen
        	count=count+1
            ia = ia + 1
        Case Else
            ina = ina + 1
    End Select
Next i
If ina = 0 Then
    TestPRN = True
Else
    TestPRN = False
End If

'Single Hypen
'If count>1 Then
'	TestPRN = False
'End If
'For checking whether all characters in PRN are 0.
ia=0
For i = 1 To stlen
    Select Case (Mid(lstr_check, i, 1))
        Case "0"         '0
            ia = ia + 1
        Case Else
            ina = ina + 1
    End Select
Next i
If stlen <> 0 then
	If ia = stlen then
		TestPRN = False
	End If
End If
End Function