--=============================================================================================
--=============================================================================================
-- ************Purpose		: Golu Finder
-- ************Definition	: This procedure for finding the invalid character in devanagari unicode data
-- ************Developed by	: Technical Team, Election Commission of India, New Delhi.

--==============================================================================================
--==============================================================================================
CREATE OR REPLACE PROCEDURE Uni_InvalidChar AS
BEGIN
 DECLARE
 Cursor p_cursor IS
  SELECT st_code,elector_name_unicode_hi,frequency
  FROM UP_DICT
  where TRIM(elector_name_unicode_hi) IS NOT NULL;
 state VARCHAR2(3);
 uniname VARCHAR2(200);
 ch_3char VARCHAR2(200);
 Nlen number(30);
 chk VARCHAR2(10);
 numId number(5);
 SCODE varchar2(10);
 FirstChar varchar2(10);
 SecondChar varchar2(10);
 thirdChar varchar2(10);
 FirstFlag char(1);
 SecondFlag char(1);
 thirdFlag char(1);
 numpOS NUMBER(10);
  st varchar2(5);
BEGIN
numId:=1;
 For UniRec in p_cursor
  LOOP
 uniname:=UniRec.elector_name_unicode_hi;
 SCODE:=UniRec.st_code;
  Nlen :=length(uniname);
  --dbms_output.Put_line(uniname);
  chk:=0;
    
-- ****************For starting character checking ************************************
    chk:=substr(uniname,1,1);
   If (ascii(chk) NOT BETWEEN 14722181 AND 14722200) AND (ascii(chk) NOT BETWEEN 14722202 AND 14722205) 
     AND (ascii(chk) NOT BETWEEN 14722207 AND 14722233) AND (ascii(chk) NOT BETWEEN 14722456 AND 14722565) THEN
    ch_3char:=substr(uniname,1,4);
     insert into UNICODE_CHECK(ID,ELECTORS_NAME,THREECHAR,STATUS,st_code,INVCHAR)  values(NumID,uniname,ch_3char,'F',SCODE,chk);
     numId:=numId+1;
   End If;

-- ******************** End for Start character ****************************************

-- ****************For Last character checking ************************************
   chk:=substr(uniname,Nlen,1);

   If (ascii(chk) NOT BETWEEN 14722177 AND 14722179) AND (ascii(chk) NOT BETWEEN 14722181 AND 14722233) 
     AND (ascii(chk) NOT BETWEEN 14722234 AND 14722239) AND (ascii(chk) NOT BETWEEN 14722432 AND 14722445)
     AND (ascii(chk) NOT BETWEEN 14722456 AND 14722465) THEN 
     ch_3char:=substr(uniname,Nlen-3,4);
     insert into UNICODE_CHECK(ID,ELECTORS_NAME,THREECHAR,STATUS,st_code,INVCHAR) values(NumID,uniname,ch_3char,'L',SCODE,chk);
     numId:=numId+1;
   End If;
-- ******************** End for last character **********************************************

-- ****************For Middle including last character checking ************************************

  		FOR CNT IN 2..Nlen-1 LOOP
   			FirstChar:=substr(trim(uniname),cnt,1);
                	SecondChar:=substr(trim(uniname),cnt+1,1);
    			--thirdChar:=substr(trim(uniname),cnt+2,1);
                        st:='A';

dbms_output.Put_line(uniname);
dbms_output.Put_line(nlen);
dbms_output.Put_line(ascii(FirstChar));
dbms_output.Put_line(ascii(SecondChar));

--************************ First char identification ************************
                       <<first>>
                        CASE 
                            When  ascii(FirstChar)= 14722176 THEN
                             FirstFlag:='M';
			    When  ascii(FirstChar)  between  14722177 and 14722178  then
                             FirstFlag:='S';
                            When  ascii(FirstChar)  between  14722179 and 14722180  then
                             FirstFlag:='M';
			    When ascii(FirstChar)  between  14722234 and 14722447  then
			     FirstFlag:='M';
			    When ascii(FirstChar)  between  14722449 and 14722455  then
			     FirstFlag:='M';
			    When ascii(FirstChar) between  14722466 and 14722469  then
			     FirstFlag:='M';
			    When ascii(FirstChar)  between  14722470 and 14722479  then
			     FirstFlag:='N';
			    When ascii(FirstChar)  between  14722181 and 14722186  then
			     FirstFlag:='A';
			    When ascii(FirstChar)  between  14722187 and 14722188  then
			     FirstFlag:='C';
			    When ascii(FirstChar)  between  14722189 and 14722196  then
			     FirstFlag:='A';
			   When ascii(FirstChar)  between  14722197 and 14722200  then
			     FirstFlag:='C';
			   When ascii(FirstChar) = 14722201  then
			     FirstFlag:='A';
			   When ascii(FirstChar)  between  14722202 and 14722206  then
			     FirstFlag:='C';
			   When ascii(FirstChar) = 14722206  then
			     FirstFlag:='A';
			   When ascii(FirstChar)  between  14722207 and 14722233  then
			     FirstFlag:='C';
			    When ascii(FirstChar)  between  14722456 and 14722465  then
			     FirstFlag:='C';
			    When ascii(FirstChar) = 14722448  then
			     FirstFlag:='C';
                            else
			    FirstFlag:='S';
                        END CASE first; 

--************************ second char identification ************************
			<<second>>
                           case 
                            When  ascii(SecondChar)= 14722176 THEN
                             SecondFlag:='M';
			    When  ascii(SecondChar)  between  14722177 and 14722178  then
                             SecondFlag:='S';
                            When  ascii(SecondChar)  between  14722179 and 14722180  then
                             SecondFlag:='M';
			    When ascii(SecondChar)  between  14722234 and 14722447  then
			     SecondFlag:='M';
			    When ascii(SecondChar)  between  14722449 and 14722455  then
			     SecondFlag:='M';
			    When ascii(SecondChar) between  14722466 and 14722469  then
			     SecondFlag:='M';
			    When ascii(SecondChar)  between  14722470 and 14722479  then
			     SecondFlag:='N';
			    When ascii(SecondChar)  between  14722181 and 14722186  then
			     SecondFlag:='A';
			    When ascii(SecondChar)  between  14722187 and 14722188  then
			     SecondFlag:='C';
			    When ascii(SecondChar)  between  14722189 and 14722196  then
			     SecondFlag:='A';
			   When ascii(SecondChar)  between  14722197 and 14722200  then
			     SecondFlag:='C';
			   When ascii(SecondChar) = 14722201  then
			     SecondFlag:='A';
			   When ascii(SecondChar)  between  14722202 and 14722205  then
			     SecondFlag:='C';
			   When ascii(SecondChar) = 14722206  then
			     SecondFlag:='A';
			   When ascii(SecondChar)  between  14722207 and 14722233  then
			     SecondFlag:='C';
			    When ascii(SecondChar)  between  14722456 and 14722465  then
			     SecondFlag:='C';
			    When ascii(SecondChar) = 14722448  then
			     SecondFlag:='C';
			     else
			     SecondFlag:='S';
                       END CASE second; 

--************************ third char identification ************************
                          <<third>>
                  --      CASE 
                  --          When  thirdChar  between  14722176 and 14722180  then
                  --           thirdFlag:='M';
		  --	    When thirdChar  between  14722234 and 14722447  then
		  --	     thirdFlag:='M';
		  --	    When thirdChar  between  14722449 and 14722455  then
		  --	     thirdFlag:='M';
		  --	    When thirdChar between  14722466 and 14722469  then
		  --	     thirdFlag:='M';
		  --	    When thirdChar  between  14722470 and 14722479  then
		  --	     thirdFlag:='N';
		  --	    When thirdChar between  14722181 and 14722186  then
		  --	     thirdFlag:='A';
		  --	    When thirdChar between  14722187 and 14722188  then
		  --	     thirdFlag:='C';
		  --	    When thirdChar between  14722189 and 14722196  then
		  --	     thirdFlag:='A';
		  --	   When thirdChar between  14722197 and 14722200  then
		  --	     thirdFlag:='C';
		  --	   When thirdChar = 14722201  then
		  --	     thirdFlag:='A';
		  --	   When thirdChar between  14722202 and 14722205  then
		  --	     thirdFlag:='C';
		  --	   When thirdChar = 14722206  then
		  --	     thirdFlag:='A';
		  --	   When thirdChar between  14722207 and 14722233  then
		  --	     thirdFlag:='C';
		  --	    When thirdChar between  14722456 and 14722465  then
		  --	     thirdFlag:='C';
		  --	    When thirdChar = 14722448  then
		  --	     thirdFlag:='C';
                  --     END CASE third;
                            IF FirstFLAG ='M' AND SecondFlag ='M' THEN
				
				ch_3char:=substr((uniname),cnt,3);
				chk:= FirstChar || SecondChar;
				insert into UNICODE_CHECK(ID,ELECTORS_NAME,
					THREECHAR,STATUS,st_code,INVCHAR) 
					values(NumID,uniname,ch_3char,st,SCODE,chk);
                                 NumID:=NumID+1;
                            END IF; 
                  
                           IF FirstFlag ='A' AND SecondFlag ='M' THEN
				ch_3char:=substr((uniname),cnt,3);
				chk:= FirstChar || SecondChar;
				insert into UNICODE_CHECK(ID,ELECTORS_NAME,
					THREECHAR,STATUS,st_code,INVCHAR) 
					values(NumID,uniname,ch_3char,st,SCODE,chk);
                                 NumID:=NumID+1;
 

                           END IF;