﻿
ALTER TABLE cmprocessing MODIFY CRITERIA_XML VARCHAR(4000) ;
ALTER TABLE cmprocessing MODIFY CRITERIA_XML_1 VARCHAR(4000) ;
ALTER TABLE cmprocessing MODIFY CRITERIA_XML_2 VARCHAR(4000) ;

DROP function IF EXISTS `months_between`;

DELIMITER $$
CREATE FUNCTION `months_between` ( startdate datetime, enddate datetime)
RETURNS varchar(20)
BEGIN
declare ls_month varchar(20);
set ls_month = cast( 
(
TIMESTAMPDIFF(MONTH, startdate, enddate) +
  DATEDIFF(
    enddate,
    startdate + INTERVAL
      TIMESTAMPDIFF(MONTH, startdate, enddate)
    MONTH
  ) /
  DATEDIFF(
    startdate + INTERVAL
      TIMESTAMPDIFF(MONTH, startdate, enddate) + 1
    MONTH,
    startdate + INTERVAL
      TIMESTAMPDIFF(MONTH, startdate, enddate)
    MONTH
  )
  ) as CHAR );
  
RETURN ls_month;
END$$

DELIMITER ;


DROP function IF EXISTS `ftcm_calagemth`;

DELIMITER $$

CREATE FUNCTION `ftcm_calagemth` ( adtm_from datetime, adtm_to datetime)
RETURNS varchar(10)
BEGIN

DECLARE ldc_age	decimal(15,10);
DECLARE ls_year varchar(10);
DECLARE ls_mth	varchar(10);
DECLARE ls_age 	varchar(10);

	if adtm_from is null then
		return '0.00';
	end if;
	if adtm_to is null then
		return '0.00';
	end if;
    
	select months_between( adtm_to , adtm_from )
	into ldc_age
	from dual;
    
	set ls_year	= CAST( (floor( ldc_age - mod( ldc_age , 12 )  )/12 ) as CHAR);
	set ls_mth	= CAST( (floor( mod( ldc_age , 12 ))) as CHAR);
	set ls_age	= concat(concat(lpad(ls_year,2,'0') , '.' ), lpad(ls_mth,2 ,'0' ) );
    
	return trim(ls_age);
    
END

/*
create or replace FuncTion ft_calagemth( adtm_from date , adtm_to date ) return varchar
is
ldc_age	number(15,10);
ls_year varchar(5);
ls_mth	varchar(5);
ls_age 	varchar(10);
begin
	if adtm_from is null then
		return '0.00';
	end if;
	if adtm_to is null then
		return '0.00';
	end if;
	select months_between( adtm_to , adtm_from )
	into ldc_age
	from dual;
	ls_year	:= to_char(trunc( ldc_age - mod( ldc_age , 12 ) , 0 )/12);
	ls_mth	:= to_char(trunc( mod( ldc_age , 12 ), 0 ));
	ls_age	:= ls_year || '.' || lpad(ls_mth,2 ,'0' );
	return trim(ls_age);
end ;
 */$$

 
DELIMITER ;

DROP function IF EXISTS `ft_getmemname`;

DELIMITER $$

CREATE FUNCTION `ft_getmemname` (as_coopid char , as_memno char)
RETURNS varchar(150)
BEGIN
 declare ls_memname varchar(150);
 
 if as_coopid is null or as_memno is null then
  return '';
 end if;
 select concat(concat(concat(ifnull( mp.prename_desc , '' ) , ifnull( mm.memb_name , '' ) ), '   ') , ifnull( mm.memb_surname , '' ) ) as memname
 into ls_memname
  from mbmembmaster mm left join  mbucfprename mp on 
 mm.prename_code = mp.prename_code
 where mm.coop_id = as_coopid
 and mm.member_no = as_memno;
 return rtirm(ltrim(ls_memname));
END


/*
create or replace FUNCTION           "FT_GETMEMNAME" ( as_coopid char , as_memno char ) return varchar
is
  ls_memname varchar(150);
begin
 if as_coopid is null or as_memno is null then
  return '';
 end if;
 select nvl( mp.prename_desc , '' ) || nvl( mm.memb_name , '' ) || '   ' || nvl( mm.memb_surname , '' ) as memname
 into ls_memname
 from mbmembmaster mm, mbucfprename mp
 where mm.prename_code = mp.prename_code(+)
 and mm.coop_id = as_coopid
 and mm.member_no = as_memno;
 return trim(ls_memname);
end ;
 
*/$$

DELIMITER ;


DROP function IF EXISTS `ft_memgrp`;

DELIMITER $$

CREATE FUNCTION `ft_memgrp` ( as_coopid char , as_memgrp char )
RETURNS varchar(150)
BEGIN
  declare ls_memgrpdesc varchar(150);
  
  if as_coopid is null or as_memgrp is null then
		return '';
  end if;
	
  select ifnull( membgroup_desc , '' )
	into ls_memgrpdesc
  from mbucfmembgroup
  where coop_id = as_coopid
	and membgroup_code = as_memgrp;
	
  return ltrim(rtrim(ls_memgrpdesc));
    
RETURN 1;
END

/*
create or replace FUNCTION           "FT_MEMGRP" ( as_coopid char , as_memgrp char ) return varchar
is
  ls_memgrpdesc varchar(150);
begin
	if as_coopid is null or as_memgrp is null then
		return '';
	end if;
	select nvl( membgroup_desc , '' )
	into ls_memgrpdesc
	from mbucfmembgroup
	where coop_id = as_coopid
	and membgroup_code = as_memgrp;
	return trim(ls_memgrpdesc);
end;
 
 
 
*/$$

DELIMITER ;

DROP function IF EXISTS `trim`;

DELIMITER $$

CREATE FUNCTION `trim` (str char )
RETURNS varchar(255)
BEGIN
RETURN rtrim(ltrim(str));
END$$

DELIMITER ;


DROP function IF EXISTS `nvl`;

DELIMITER $$

CREATE FUNCTION `nvl` (str char ,val char)
RETURNS varchar(255)
BEGIN
RETURN ifnull(str,val);
END$$

DELIMITER ;

DROP function IF EXISTS `ftreadbaht_`;

DELIMITER $$
CREATE FUNCTION `ftreadbaht_` (ai_amount decimal(20,2))
RETURNS varchar(1000)
BEGIN
   declare ls_ans1          varchar(200);
   declare ls_money1        varchar(200);
   declare ls_money2        varchar(200);
   declare ls_money3        varchar(200);
   declare ls_pre           varchar(100);
   declare ls_moneystr      varchar(100);
   declare ls_curr_name     varchar(100);
   declare ls_curr_sub_name varchar(100);
   declare li_amount        decimal;
   declare li_strlength     integer;
   declare li_counter1      integer;
   declare li_stang         decimal;
   declare ls_decimal       varchar(200);

	  /*set li_amount := truncate( ai_amount , 2 );*/
      set li_amount := ai_amount ;

	  if (ls_curr_name is null) or (ls_curr_name = '') then
		 set ls_curr_name := 'บาท';
	  end if;

	  if (ls_curr_sub_name is null) or (ls_curr_sub_name = '') then
		  set ls_curr_sub_name := 'สตางค์';
	  end if;
		  set li_strlength := length(ifnull(li_amount,0));
	  if substr(trim(cast(li_amount as char)),li_strlength-1,1) = '.' then
		 set ls_money1 := concat(trim(cast(li_amount as char)),'0');
	  else
		 set ls_money1 := trim(cast(li_amount as char));
	  end if;
	  set li_counter1 := 1;
	  set ls_ans1 := '';
	  set li_stang := 0;

	  WHILE length(ifnull(ls_money1,0)) > 0 do
		set li_strlength := length(ifnull(ls_money1,0));
		set ls_money2 := substr(ls_money1,li_strlength,1);
		if li_strlength <> 1 Then
		   set ls_money3 := substr(ls_money1,li_strlength - 1,1);
		end If;
		if ls_money2 = '0' then
			  set ls_moneystr := '';
		else if ls_money2 = '1' then
			   set ls_moneystr := 'หนึ่ง';
			   if li_counter1 = 1 then
				  If ls_money3 <> '0' And li_strlength > 1 Then
/*			  If li_strlength > 1 Then */
		   set ls_moneystr := 'เอ็ด';
			 End If;
		  else if li_counter1 = 2 then
				  set ls_moneystr := '';
					end if;
			   end if;
			 else if ls_money2 = '2' then
				If li_counter1 = 2 Then
				   set ls_moneystr := 'ยี่';
					 Else
				   set ls_moneystr := 'สอง';
					 End If;
				  else if ls_money2 = '3' then
						  set ls_moneystr := 'สาม';
					   else if ls_money2 = '4' then
						  set ls_moneystr := 'สี่';
							else if ls_money2 = '5' then
							   set ls_moneystr := 'ห้า';
								 else if ls_money2 = '6' then
									set ls_moneystr := 'หก';
									  else if ls_money2 = '7' then
										 set ls_moneystr := 'เจ็ด';
										   else if ls_money2 = '8' then
											  set ls_moneystr := 'แปด';
												else if ls_money2 = '9' then
												   set ls_moneystr := 'เก้า';
													 else
												   set ls_moneystr := '';
													 end if;
												end if;
										   end if;
									  end if;
								 end if;
							end if;
					   end if;
				  end if;
			 end if;
		end if;
		set ls_pre := '';
		if li_counter1 = 1 then
		   set ls_pre := '';
		else if li_counter1 = 2 then
		   If ls_money2 <> '0' Then
			  set ls_pre := 'สิบ';
				End If;
		else if li_counter1 = 3 then
				If ls_money2 <> '0' And ls_money2 <> '.' Then
				   set ls_pre := 'ร้อย';
		   End If;
			 else if li_counter1 = 4 then
				If ls_money2 <> '0' Then
			  set ls_pre := 'พัน';
				End If;
			 else if li_counter1 = 5 then
				If ls_money2 <> '0' Then
				   set ls_pre := 'หมื่น';
				End If;
			 else if li_counter1 = 6 then
				If ls_money2 <> '0' Then
				   set ls_pre := 'แสน';
					  End If;
				  end if;
							end if;
					   end if;
				  end if;
			 end if;
		end if;
	   set ls_money1 := substr(ls_money1,1,li_strlength - 1);
	   set ls_ans1 := concat(concat(ls_moneystr,ls_pre),ls_ans1);
	   If li_counter1 = 6 Then
		  set li_counter1 := 1;
		  If li_strlength >1 Then
		set ls_ans1 :=concat( 'ล้าน',ls_ans1);
		  End If;
	   Else
		  If ls_money2 = '.' Then
			set li_stang := 1;
			 set li_counter1 := 1;
			 set ls_decimal := ls_ans1;
			 If ls_ans1 <> '' Then
		   If li_strlength > 2 Then
			  set ls_ans1 := concat(concat(ltrim(ls_curr_name),ls_ans1),ltrim(ls_curr_sub_name));
				Else
			  If li_strlength = 2 Then
			   If ls_money3 <> '0' Then
			   set ls_ans1 := concat(concat(ltrim(ls_curr_name),ls_ans1),ltrim(ls_curr_sub_name));
				 Else
				  set ls_ans1 := concat(ls_ans1,ltrim(ls_curr_sub_name));
				 End If;
			  Else
			set ls_ans1 := concat(ls_ans1,ltrim(ls_curr_sub_name));
			  End If;
		   End If;
	   Else
		set ls_ans1 := concat(concat(ltrim(ls_curr_name),ls_decimal),ltrim(ls_curr_sub_name));
		   /*ls_ans1 := ltrim(ls_curr_name)||ls_decimal||ltrim(ls_curr_sub_name);*/
			End If;
		  Else
			 set li_counter1 := li_counter1 +1;
		  End If;
		End If;
	  end while;
	  if li_stang = 0 then
		   RETURN concat(concat(ls_ans1,ltrim(ls_curr_name)),'ถ้วน');
	  else
		   if substr( ls_ans1 , 0 , 3 ) = 'บาท' then
			   set ls_ans1 := replace( ls_ans1 , 'บาท' , '' );
		   end if;
		   RETURN (ls_ans1);
	  end if;
  
/*RETURN 'บาท'; */
END
/*
create or replace Function ft_readtbaht(ai_amount  IN NUMBER ) RETURN VARCHAR2 IS
  

BEGIN

  li_amount := trunc( ai_amount , 2 );

  if (ls_curr_name is null) or (ls_curr_name = '') then
      ls_curr_name := 'บาท';
  end if;

  if (ls_curr_sub_name is null) or (ls_curr_sub_name = '') then
      ls_curr_sub_name := 'สตางค์';
  end if;
  li_strlength := NVL(Length(to_char(li_amount)), 0);
  if substr(to_char(li_amount),li_strlength-1,1) = '.' then
     ls_money1 := to_char(li_amount)||'0';
  else
     ls_money1 := to_char(li_amount);
  end if;
  li_counter1 := 1;
  ls_ans1 := '';
  li_stang := 0;

  loop
    exit when NVL(length(ls_money1), 0) = 0;
    li_strlength := NVL(Length(ls_money1), 0);
    ls_money2 := substr(ls_money1,li_strlength,1);
    if li_strlength <> 1 Then
       ls_money3 := substr(ls_money1,li_strlength - 1,1);
    end If;
    if ls_money2 = '0' then
          ls_moneystr := '';
    else if ls_money2 = '1' then
           ls_moneystr := 'หนึ่ง';
           if li_counter1 = 1 then
              If ls_money3 <> '0' And li_strlength > 1 Then
	      --If li_strlength > 1 Then
       ls_moneystr := 'เอ็ด';
         End If;
      else if li_counter1 = 2 then
              ls_moneystr := '';
                end if;
           end if;
         else if ls_money2 = '2' then
            If li_counter1 = 2 Then
               ls_moneystr := 'ยี่';
                 Else
               ls_moneystr := 'สอง';
                 End If;
              else if ls_money2 = '3' then
                      ls_moneystr := 'สาม';
                   else if ls_money2 = '4' then
                      ls_moneystr := 'สี่';
                        else if ls_money2 = '5' then
                           ls_moneystr := 'ห้า';
                             else if ls_money2 = '6' then
                                ls_moneystr := 'หก';
                                  else if ls_money2 = '7' then
                                     ls_moneystr := 'เจ็ด';
                                       else if ls_money2 = '8' then
                                          ls_moneystr := 'แปด';
                                            else if ls_money2 = '9' then
                                               ls_moneystr := 'เก้า';
                                                 else
                                               ls_moneystr := '';
                                                 end if;
                                            end if;
                                       end if;
                                  end if;
                             end if;
                        end if;
                   end if;
              end if;
         end if;
    end if;
    ls_pre := '';
    if li_counter1 = 1 then
       ls_pre := '';
    else if li_counter1 = 2 then
       If ls_money2 <> '0' Then
          ls_pre := 'สิบ';
            End If;
    else if li_counter1 = 3 then
            If ls_money2 <> '0' And ls_money2 <> '.' Then
               ls_pre := 'ร้อย';
       End If;
         else if li_counter1 = 4 then
            If ls_money2 <> '0' Then
          ls_pre := 'พัน';
            End If;
         else if li_counter1 = 5 then
            If ls_money2 <> '0' Then
               ls_pre := 'หมื่น';
            End If;
         else if li_counter1 = 6 then
            If ls_money2 <> '0' Then
               ls_pre := 'แสน';
                  End If;
              end if;
                        end if;
                   end if;
              end if;
         end if;
    end if;
   ls_money1 := substr(ls_money1,1,li_strlength - 1);
   ls_ans1 := ls_moneystr||ls_pre||ls_ans1;
   If li_counter1 = 6 Then
      li_counter1 := 1;
      If li_strlength >1 Then
    ls_ans1 := 'ล้าน'||ls_ans1;
      End If;
   Else
      If ls_money2 = '.' Then
        li_stang := 1;
         li_counter1 := 1;
         ls_decimal := ls_ans1;
         If ls_ans1 <> '' Then
       If li_strlength > 2 Then
          ls_ans1 := ltrim(ls_curr_name)||ls_ans1||ltrim(ls_curr_sub_name);
            Else
          If li_strlength = 2 Then
           If ls_money3 <> '0' Then
           ls_ans1 := ltrim(ls_curr_name)||ls_ans1||ltrim(ls_curr_sub_name);
             Else
              ls_ans1 := ls_ans1||ltrim(ls_curr_sub_name);
             End If;
          Else
        ls_ans1 := ls_ans1||ltrim(ls_curr_sub_name);
          End If;
       End If;
   Else
	ls_ans1 := ltrim(ls_curr_name)||ls_decimal||ltrim(ls_curr_sub_name);
       --ls_ans1 := ltrim(ls_curr_name)||ls_decimal||ltrim(ls_curr_sub_name);
        End If;
      Else
         li_counter1 := li_counter1 +1;
      End If;
    End If;
  end Loop;
  if li_stang = 0 then
       RETURN(ls_ans1||ltrim(ls_curr_name)||'ถ้วน');
  else
       if substr( ls_ans1 , 0 , 3 ) = 'บาท' then
           ls_ans1 := replace( ls_ans1 , 'บาท' , '' );
       end if;
       RETURN(ls_ans1);
  end if;
  End ;
 
*/
$$

DELIMITER ;


DROP function IF EXISTS `ftreadsatang`;

DELIMITER $$
CREATE FUNCTION `ftreadsatang` (ai_amount decimal(20,2))
RETURNS varchar(500)
BEGIN
 declare ls_val          varchar(200);
 declare is_stang        decimal(10,2);
 set is_stang :=(round(ai_amount,2)-truncate(ai_amount,0))*100;
 /*return cast(is_stang as char);*/
 set ls_val := replace(replace(ftreadbaht_(is_stang),'บาท',''),'ถ้วน','');
 if(length(ls_val)>0) then
    set ls_val := concat(ls_val,'สตางค์');
 end if;	
RETURN ls_val;
END$$

DELIMITER ;

DROP function IF EXISTS `ftreadbaht`;

DELIMITER $$
CREATE FUNCTION `ftreadbaht` (ai_amount decimal(20,2))
RETURNS varchar(500)
BEGIN
  declare ls_baht varchar(500);
  declare ls_satang varchar(500);
  declare ls_val varchar(500);
  set ls_baht:=ftreadbaht_(ai_amount);
  set ls_satang:=ftreadsatang(ai_amount);
  set ls_val:=ls_baht;
  if (length(ls_satang)>0) then 
   set ls_val:=replace(concat(ls_baht,ls_satang),'ถ้วน','');
  end if;
RETURN ls_val;
END$$

DELIMITER ;


DROP function IF EXISTS `ft_cnvtdate`;

DELIMITER $$

CREATE FUNCTION `ft_cnvtdate` ( adtm_date date , ai_option integer )
RETURNS varchar(255)
BEGIN

RETURN '';
END

/*

create or replace FUNCTION           "FTCNVTDATE" ( adtm_date date , ai_option number ) return VarChar2
is
ls_preday VarChar2(10);
ls_day    VarChar2(5);
ls_premth VarChar2(10);
ls_mth    VarChar2(30);
ls_preyr  VarChar2(5);
ls_yr     VarChar2(4);
ls_date		VarChar2(70);
begin
	if adtm_date is null then
		return null;
	end if;

  ls_preday   := '';
  ls_preyr    := '';

  ls_day      := to_char( adtm_date , 'dd' );
  ls_mth      := to_char( adtm_date , 'mm' );
  ls_yr       := to_char( to_number( to_char( adtm_date , 'yyyy' ) ) + 543 );

  
  //เป็น 1 ก็ให้แปลงแค่ปี 18/05/2013 --> 18/05/2556
  //เป็น 2 ก็ให้แปลงเดือนด้วย 18/05/2013 --> 18 พ.ค. 2556
  //เป็น 3 ก็ให้แปลงเดือนด้วย 18/05/2013 --> 18 พฤษภาคม 2556
  //เป็น 4 ก็ให้แปลงแบบใส่ตำแหน่ง 18/05/2013 --> วันที่ 18 เดือนพฤษภาคม พ.ศ. 2556
  //เป็น 5 ก็ให้แปลงแบบไทยเต็มยศเลย 18/05/2013 --> ๑๘ พฤษภาคม พ.ศ. ๒๕๕๖
  //เป็น 10 แสดงเดือนแบบย่อ พ.ค.
  //เป็น 11 แสดงเดือนแบบเต็ม พฤษภาคม
  

  If ai_option = 2 or ai_option = 10 Then
    Case ls_mth
      When '01' Then
        ls_mth  := 'ม.ค.' ;
      When '02' Then
        ls_mth  := 'ก.พ.' ;
      When '03' Then
        ls_mth  := 'มี.ค.' ;
      When '04' Then
        ls_mth  := 'เม.ย.' ;
      When '05' Then
        ls_mth  := 'พ.ค.' ;
      When '06' Then
        ls_mth  := 'มิ.ย.' ;
      When '07' Then
        ls_mth  := 'ก.ค.' ;
      When '08' Then
        ls_mth  := 'ส.ค.' ;
      When '09' Then
        ls_mth  := 'ก.ย.' ;
      When '10' Then
        ls_mth  := 'ต.ค.' ;
      When '11' Then
        ls_mth  := 'พ.ย.' ;
      When '12' Then
        ls_mth  := 'ธ.ค.' ;
    Else
      ls_mth := '' ;
    End Case;
  End If;

  If ai_option = 3 or ai_option = 4 or ai_option = 5 or ai_option = 11 Then
    Case ls_mth
      When '01' Then
        ls_mth  := 'มกราคม' ;
      When '02' Then
        ls_mth  := 'กุมภาพันธ์' ;
      When '03' Then
        ls_mth  := 'มีนาคม' ;
      When '04' Then
        ls_mth  := 'เมษายน' ;
      When '05' Then
        ls_mth  := 'พฤษภาคม' ;
      When '06' Then
        ls_mth  := 'มิถุนายน' ;
      When '07' Then
        ls_mth  := 'กรกฏาคม' ;
      When '08' Then
        ls_mth  := 'สิงหาคม' ;
      When '09' Then
        ls_mth  := 'กันยายน' ;
      When '10' Then
        ls_mth  := 'ตุลาคม' ;
      When '11' Then
        ls_mth  := 'พฤศจิกายน' ;
      When '12' Then
        ls_mth  := 'ธันวาคม' ;
    Else
      ls_mth := '' ;
    End Case;
  End If;

  Case ai_option
    When 1 Then
      ls_day  := trim( ls_day ) || '/' ;
      ls_mth  := trim( ls_mth ) || '/' ;
    When 2 Then
      ls_day  := trim( to_char( to_number( ls_day ) ) ) || ' ' ;
      ls_mth  := trim( ls_mth ) || ' ' ;
    When 3 Then
      ls_day  := trim( to_char( to_number( ls_day ) ) ) || ' ' ;
      ls_mth  := trim( ls_mth ) || ' ' ;
    When 4 Then
      ls_preday := 'วันที่ ' ;
      ls_day    := trim( to_char( to_number( ls_day ) ) ) || ' ' ;
      ls_premth := 'เดือน' ;
      ls_mth    := trim( ls_mth ) || ' ' ;
      ls_preyr  := 'พ.ศ. ' ;
      ls_yr     := trim( ls_yr ) ;
    When 10 Then
      ls_day    := '' ;
      ls_yr     := '' ;
    When 11 Then
      ls_day    := '' ;
      ls_yr     := '' ;
  Else
    ls_day  := trim( ls_day ) || '/' ;
    ls_mth  := trim( ls_mth ) || '/' ;
  End Case;

	ls_date := ls_preday || ls_day || ls_premth || ls_mth || ls_preyr || ls_yr ;
	return trim(ls_date);
end ;
 
*/

$$

DELIMITER ;


DROP function IF EXISTS `ft_retrydate`;

DELIMITER $$

CREATE FUNCTION `ft_retrydate` (as_coopid char , adtm_birth date)
RETURNS date
BEGIN

RETURN curedate();
END
/*

create or replace FUNCTION           "FT_RETRYDATE" ( as_coopid char , adtm_birth date ) return date
is
  ldtm_retry date;
begin
	if as_coopid is null or adtm_birth is null then
		return null;
	end if;

  select last_day( to_date( '01/' || trim( to_char( retry_month , '00' ) ) || '/' || to_char( retry_date , 'yyyy' ) , 'dd/mm/yyyy' ) ) as retry_date
  into ldtm_retry
  from(
        select ( retry_month - 1 ) as retry_month ,
        ( case when to_char( adtm_birth , 'mm' ) < nvl( retry_month , 0 ) then add_months( adtm_birth , 12 * nvl( retry_age , 0 ) ) else add_months( adtm_birth , 12 * ( nvl( retry_age , 0 ) + 1 ) ) end ) as retry_date
        from cmcoopconstant
        where coop_no = as_coopid
      ) ;

	return ldtm_retry;
end ;

 */$$

DELIMITER ;



DROP function IF EXISTS `ft_checkdigit`;

DELIMITER $$

CREATE FUNCTION `ft_checkdigit`( as_data varchar(20)) 
RETURNS DECIMAL(10,0)
BEGIN

  declare  as_length int ;
  declare  as_datas char(1);
  declare  as_tmp int;
  declare  as_tmp_ varchar(20);
  declare  as_checkdigit int;
  declare  as_start int;
  declare  as_end int;
  
  set as_checkdigit = 0;
  set as_length =length(as_data);
  set as_start =1;
  set as_end =as_length;
  set as_tmp = 0;
  /*set as_start = 1 ;*/
  
  WHILE as_start<=as_end DO 
  
  set as_datas =  SUBSTR(as_data,as_start,1);
  set as_tmp = ( CAST(as_datas AS DECIMAL(1,0) )*(as_length-as_start+2) ) + as_tmp;
  set as_start =as_start +1;
  
  end  WHILE;
  
  set as_tmp = ( as_length -1 ) - ( as_tmp % ( as_length -1 ) ) ;
  
  set as_tmp_ = ltrim(rtrim(CAST(as_tmp as char(20))));
  case when length(as_tmp_) > 1 then
   set as_tmp = CAST(SUBSTR(as_tmp_,length(as_tmp_)-1,1) as  DECIMAL(1,0) ) ;
  else 
   set as_tmp_ = ltrim(rtrim(CAST(as_tmp as char(20))));
  end case ;
  
RETURN as_tmp;
/*return cast(as_datas as DECIMAL(2,0) );*/
END$$

DELIMITER ;


DROP function IF EXISTS `ft_fillcheckdigit`;

DELIMITER $$

CREATE FUNCTION `ft_fillcheckdigit`( as_data varchar(20)) 
RETURNS varchar(20)
BEGIN


RETURN concat(as_data,ft_checkdigit(as_data));
/*return cast(as_datas as DECIMAL(2,0) );*/
END$$

DELIMITER ;


DROP function IF EXISTS `ft_checkageyear`;

DELIMITER $$

CREATE FUNCTION `ft_checkageyear`( as_date datetime) 
RETURNS integer 
BEGIN

 declare is_year integer;
 declare is_month integer;
 declare is_day integer;

SELECT 
       TIMESTAMPDIFF(year, dt.dt, NOW()) AS y,
       TIMESTAMPDIFF(month, dt.dt, NOW())%12  AS m,
       TIMESTAMPDIFF ( day, 
        DATE_ADD( adddate(curdate(), day( dt.dt) - day(curdate())), interval 
        -(day( dt.dt)>day(curdate())) month), 
        curdate()) as days
		into 
		is_year,is_month,is_day
FROM (select as_date as dt ) as dt;
return is_year;
END$$

DELIMITER ;


DROP function IF EXISTS `ft_checkagemonth`;

DELIMITER $$

CREATE FUNCTION `ft_checkagemonth`( as_date datetime) 
RETURNS integer 
BEGIN

 declare is_year integer;
 declare is_month integer;
 declare is_day integer;

SELECT 
       TIMESTAMPDIFF(year, dt.dt, NOW()) AS y,
       TIMESTAMPDIFF(month, dt.dt, NOW())%12  AS m,
       TIMESTAMPDIFF ( day, 
        DATE_ADD( adddate(curdate(), day( dt.dt) - day(curdate())), interval 
        -(day( dt.dt)>day(curdate())) month), 
        curdate()) as days
		into 
		is_year,is_month,is_day
FROM (select as_date as dt ) as dt;
return is_month;
END$$

DELIMITER ;

DROP function IF EXISTS `ft_checkageday`;

DELIMITER $$

CREATE FUNCTION `ft_checkageday`( as_date datetime) 
RETURNS integer 
BEGIN

 declare is_year integer;
 declare is_month integer;
 declare is_day integer;

SELECT 
       TIMESTAMPDIFF(year, dt.dt, NOW()) AS y,
       TIMESTAMPDIFF(month, dt.dt, NOW())%12  AS m,
       TIMESTAMPDIFF ( day, 
        DATE_ADD( adddate(curdate(), day( dt.dt) - day(curdate())), interval 
        -(day( dt.dt)>day(curdate())) month), 
        curdate()) as days
		into 
		is_year,is_month,is_day
FROM (select as_date as dt ) as dt;
return is_day;
END$$

DELIMITER ;


DROP function IF EXISTS `ft_checkage`;

DELIMITER $$

CREATE FUNCTION `ft_checkage`( as_date datetime) 
RETURNS varchar(50) 
BEGIN

 declare is_year integer;
 declare is_month integer;
 declare is_day integer;

SELECT 
        ft_checkageyear(as_date) ,
        ft_checkagemonth(as_date) ,
        ft_checkageday(as_date) 
      	into  is_year,is_month,is_day
FROM dual;
return concat(concat(concat(concat(concat(is_year,'y,'),is_month),'m,'),is_day),'d');
END$$

DELIMITER ;
