/*
MySQL Data Transfer
Source Host: 192.168.5.222
Source Database: CMSF
Target Host: 192.168.5.222
Target Database: CMSF
Date: 2009/07/20 23:06:19
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Function structure for CMSF_FUNC_EXTRACT_ADDRESS
-- ----------------------------
DROP FUNCTION IF EXISTS `CMSF_FUNC_EXTRACT_ADDRESS`;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `CMSF_FUNC_EXTRACT_ADDRESS`(p_address text, p_zip7 varchar(20) , p_flg varchar(5)) RETURNS text CHARSET utf8
BEGIN
/**
 * 住所を県、市区、地域、その他に分割する関数
 *
 * @package     CMSF
 * @category    CMSF_FUNC_EXTRACT_ADDRESS
 * @param       text              p_address        住所
 * @param       text              p_zip7           郵便番号
 * @param       text              p_flg            取得指示フラグ
 * @return      text                               分割結果
 * @example
 * @license     LGPL version 3 - http://www.gnu.org/licenses/lgpl.html
 * @copyright   2009 Yuusuke takagi <nya.takasan@gmail.com>
 * @link        http://sourceforge.jp/projects/cmsf/
 * @author      Yuusuke takagi <nya.takasan@gmail.com>
 * @version     $Revision: 0.0.1 2009/07/14 初期リリース Yuusuke takagi <nya.takasan@gmail.com>$
 * @version     $Revision: 0.0.2 2009/07/16 郵便番号がセットされていなかった場合のチェックを強化 Yuusuke takagi <nya.takasan@gmail.com>$
 * @version     $Revision: 0.0.3 2009/07/20 郵便番号がセットされている場合に、途中まで住所をセットする Yuusuke takagi <nya.takasan@gmail.com>$
 * @since
 * @see         CMSF_FUNC_ADDRESS2POSTALCODE
 * @require     MYSQL 5.0.0 or higther
 */
 /*------------------------------------------------------
    変数定義
 -------------------------------------------------------*/
 declare w_result                       text            default '';             /*  変換文字列 */

 /* ログ関連 */
 declare w_log_result                   varchar(255)    default '';             /* Log 結果 */
 declare w_log_query                    varchar(2000)   default '';             /*  Query Log */

 /* ユーザー定義変数 */
 declare w_address                      text            default NULL;           /*  文字列 */
 declare w_zip7                         varchar(20)     default NULL;           /*  文字列 */
 declare w_flg                          varchar(5)      default NULL;           /*  文字列 */

 declare w_pref_name                    varchar(20)      default '';           /*  文字列 */
 declare w_city_name                    varchar(20)      default '';           /*  文字列 */
 declare w_area_name                    varchar(20)      default '';           /*  文字列 */
 declare w_street_name                  varchar(20)      default '';           /*  文字列 */
 declare w_aza_name                     varchar(20)      default '';           /*  文字列 */

 declare w_num                          int(18)          default 0;           /*  文字列 */

 /*------------------------------------------------------
    ユーザ例外定義
  -------------------------------------------------------*/

 /*------------------------------------------------------
    定数定義
 -------------------------------------------------------*/
 declare s_empty_string                 varchar(255)    default '';             /*  空文字 */
 declare s_true                         varchar(255)    default 1;              /*  TRUE  FLG*/
 declare s_false                        varchar(255)    default 0;              /*  FALSE FLG */

 /* ログ関連 */
 declare s_log_flg                      int(1)          default 0;              /*  エラー処理フラグ ON:1 OFF:0 */

/*
 ログ出力サンプル SQLログ

 SET w_log_query = CONCAT(
 "    SQL"
 );

 SET w_log_result = CMSF_FUNC_PUT_LOG( s_log_created_by , s_log_prj_name , s_log_apps_name , s_log_mod_name , s_log_prg_var , s_log_type_text
                                     , '処理名称' , w_log_query , 'ログ1' , 'ログ2' , 'ログ3' , 'ログ4' , 'ログ5' );

 ログ出力サンプル テキストログ

 SET w_log_result = CMSF_FUNC_PUT_LOG( s_log_created_by , s_log_prj_name , s_log_apps_name , s_log_mod_name , s_log_prg_var , s_log_type_text
                                     , '処理名称' , ''  , 'ログ1' , 'ログ2' , 'ログ3' , 'ログ4' , 'ログ5' );
*/
 declare s_log_prj_name                 varchar(255)    default 'CMSF';                                 /*  プロジェクト名 */
 declare s_log_apps_name                varchar(255)    default 'CMSF_FUNC_ADDRESS_IDENTIFICATION';     /*  アプリケーション名 */
 declare s_log_mod_name                 varchar(255)    default 'CMSF_FUNC_EXTRACT_ADDRESS';            /*  モジュール名 */
 declare s_log_prg_var                  varchar(255)    default '0.0.1';                                /*  モジュールバージョン */

 declare s_log_created_by               varchar(255)    default 'SYSTEM';               /*  ログ作成者 */

 declare s_log_type_insert              varchar(255)    default 'INSERT';               /*  ログタイプ:INSERT  */
 declare s_log_type_select              varchar(255)    default 'SELECT';               /*  ログタイプ:SELECT  */
 declare s_log_type_update              varchar(255)    default 'UPDATE';               /*  ログタイプ:UPDATE */
 declare s_log_type_delete              varchar(255)    default 'DELETE';               /*  ログタイプ:DELETE */

 declare s_log_type_text                varchar(255)    default 'TEXT';                 /*  ログタイプ:TEXT */

 declare s_log_type_error               varchar(255)    default 'ERROR';                /*  ログタイプ:ERROR */

 declare s_log_type_tmp                 varchar(255)    default 'TEMPORARY';            /*  ログタイプ:TEMPORARY */

 /* ユーザー定義定数 */


 /*------------------------------------------------------
    カーソル定義
 -------------------------------------------------------*/
  /*--------------------------------------------
   カーソル説明
  ---------------------------------------------*/

 /*----------------------------------------------------------
   処理定義
 -----------------------------------------------------------*/
 /* 1.0 初期処理*/
 SET w_address          = REPLACE(REPLACE(REPLACE(REPLACE(p_address,'－','-'),'ー','-'),'―','-'),'‐','-');
 SET w_zip7             = REPLACE(p_zip7,'-','');
 SET w_flg              = p_flg;

 /*  ログ出力*/
 IF( s_log_flg = 1 ) THEN
        SET w_log_result =  CMSF_FUNC_PUT_LOG( s_log_created_by , s_log_prj_name , s_log_apps_name , s_log_mod_name , s_log_prg_var , s_log_type_text
                                             , CONCAT( '1.0 初期処理 パラメータ:' , p_address , ',' , p_zip7 , ',' , p_flg ) , s_empty_string , s_empty_string , s_empty_string , s_empty_string , s_empty_string　, s_empty_string );
 END IF;

 /* 2.1.1.1 郵便番号がセットされていなかった場合、郵便番号を取得 */
 IF( w_zip7 IS NULL OR w_zip7 = '' OR CHAR_LENGTH( w_zip7 ) < 7 ) THEN

   SET w_zip7 = REPLACE( CMSF_FUNC_ADDRESS2POSTALCODE( w_address , TRUE ) , '-' , '' );

   /*  ログ出力*/
   IF( s_log_flg = 1 ) THEN
        SET w_log_result =  CMSF_FUNC_PUT_LOG( s_log_created_by , s_log_prj_name , s_log_apps_name , s_log_mod_name , s_log_prg_var , s_log_type_text
                                             , '2.1.1.1 郵便番号がセットされていなかった場合、郵便番号を取得' , s_empty_string , w_zip7 , s_empty_string , s_empty_string , s_empty_string , s_empty_string );
   END IF;

 END IF;

 /* 2.1.1.2 郵便番号、住所から分割した住所を取得 */
 SELECT STRAIGHT_JOIN
        ms_addresses.pref_name,
        ms_addresses.city_name,
        ms_addresses.area_name,
        ms_addresses.street_name,
        ms_addresses.aza_name,
        MAX(CHAR_LENGTH(ms_addresses.aza_name))
   INTO w_pref_name,
        w_city_name,
        w_area_name,
        w_street_name,
        w_aza_name,
        w_num
   FROM ms_addresses_pref
   INNER JOIN ms_addresses
           ON ms_addresses.pred_cd = ms_addresses_pref.pred_cd
          AND(w_address like CONCAT(COALESCE(ms_addresses.pref_name,''),
                                    COALESCE(ms_addresses.city_name,''),
                                    COALESCE(ms_addresses.area_name,''),
                                    COALESCE(ms_addresses.street_name,''),
                                    REPLACE(COALESCE(ms_addresses.aza_name,''),'丁目',''),
                                    '%'))
          OR (w_address like CONCAT(COALESCE(ms_addresses.city_name,''),
                                    COALESCE(ms_addresses.area_name,''),
                                    COALESCE(ms_addresses.street_name,''),
                                    REPLACE(COALESCE(ms_addresses.aza_name,''),'丁目',''),
                                    '%'))
  WHERE zip = w_zip7
    AND w_address like CONCAT( ms_addresses_pref.name , '%');

 /*  ログ出力*/
 IF( s_log_flg = 1 ) THEN

 SET w_log_query = CONCAT(
   " SELECT STRAIGHT_JOIN"
  ,"        ms_addresses.pref_name,"
  ,"        ms_addresses.city_name,"
  ,"        ms_addresses.area_name,"
  ,"        ms_addresses.street_name,"
  ,"        ms_addresses.aza_name,"
  ,"        MAX(CHAR_LENGTH(ms_addresses.aza_name))"
  ,"   INTO w_pref_name,"
  ,"        w_city_name,"
  ,"        w_area_name,"
  ,"        w_street_name,"
  ,"        w_aza_name,"
  ,"        w_num"
  ,"   FROM ms_addresses_pref"
  ,"   INNER JOIN ms_addresses"
  ,"           ON ms_addresses.pred_cd = ms_addresses_pref.pred_cd"
  ,"          AND(w_address like CONCAT(COALESCE(ms_addresses.pref_name,''),"
  ,"                                    COALESCE(ms_addresses.city_name,''),"
  ,"                                    COALESCE(ms_addresses.area_name,''),"
  ,"                                    COALESCE(ms_addresses.street_name,''),"
  ,"                                    REPLACE(COALESCE(ms_addresses.aza_name,''),'丁目',''),"
  ,"                                    '%'))"
  ,"          OR (w_address like CONCAT(COALESCE(ms_addresses.city_name,''),"
  ,"                                    COALESCE(ms_addresses.area_name,''),"
  ,"                                    COALESCE(ms_addresses.street_name,''),"
  ,"                                    REPLACE(COALESCE(ms_addresses.aza_name,''),'丁目',''),"
  ,"                                    '%'))"
  ,"  WHERE zip = " , w_zip7
  ,"    AND w_address like CONCAT( ms_addresses_pref.name , '%');"
 );

        SET w_log_result =  CMSF_FUNC_PUT_LOG( s_log_created_by , s_log_prj_name , s_log_apps_name , s_log_mod_name , s_log_prg_var , s_log_type_text
                                             , '2.1.1.2 郵便番号、住所から分割した住所を取得' , w_log_query , w_pref_name , w_city_name , w_area_name , w_street_name , w_aza_name );
 END IF;

 /* 2.1.1.3 郵便番号がセットされている場合に、途中まで住所をセットする */
 IF( w_pref_name IS NULL OR w_pref_name = '' ) THEN

    SELECT
        ms_addresses.pref_name,
        ms_addresses.city_name,
        ms_addresses.area_name,
        ms_addresses.street_name,
        ms_addresses.aza_name,
        NULL
   INTO w_pref_name,
        w_city_name,
        w_area_name,
        w_street_name,
        w_aza_name,
        w_num
   FROM ms_addresses
  WHERE zip = w_zip7;

 /*  ログ出力*/
 IF( s_log_flg = 1 ) THEN

 SET w_log_query = CONCAT(
   "    SELECT"
  ,"        ms_addresses.pref_name,"
  ,"        ms_addresses.city_name,"
  ,"        ms_addresses.area_name,"
  ,"        ms_addresses.street_name,"
  ,"        ms_addresses.aza_name,"
  ,"        NULL"
  ,"   INTO w_pref_name,"
  ,"        w_city_name,"
  ,"        w_area_name,"
  ,"        w_street_name,"
  ,"        w_aza_name,"
  ,"        w_num"
  ,"   FROM ms_addresses"
  ,"  WHERE zip = " , w_zip7 , ";"
 );

        SET w_log_result =  CMSF_FUNC_PUT_LOG( s_log_created_by , s_log_prj_name , s_log_apps_name , s_log_mod_name , s_log_prg_var , s_log_type_text
                                             , '2.1.1.3 郵便番号がセットされている場合に、途中まで住所をセットする' , w_log_query , w_pref_name , w_city_name , w_area_name , w_street_name , w_aza_name );
 END IF;

 END IF;

 /* 2.1.2 パラメータとして渡されているデータをセット */
 IF( w_flg = 'PREF' ) THEN

   SET w_result = w_pref_name;

 ELSEIF( w_flg = 'CITY' ) THEN

   SET w_result = w_city_name;

 ELSEIF( w_flg = 'AREA' ) THEN

   SET w_result = CONCAT( COALESCE( w_area_name , s_empty_string ) , COALESCE( w_street_name , s_empty_string ) , COALESCE( w_aza_name , s_empty_string ) );

 ELSEIF( w_flg = 'OTHER' ) THEN

   /* 都道府県名削除 */
   IF( LEFT( w_address , CHAR_LENGTH( w_pref_name ) ) = w_pref_name ) THEN

     SET w_result = SUBSTRING( w_address , CHAR_LENGTH( w_pref_name ) + 1 , CHAR_LENGTH( w_address ) - CHAR_LENGTH( w_pref_name ) );

   ELSEIF( w_result = '' ) THEN

     SET w_result = w_address;

   END IF;

   /* 区市町村名削除 */
   IF( LEFT( w_result , CHAR_LENGTH( w_city_name ) ) = w_city_name ) THEN

     SET w_result = SUBSTRING( w_result , CHAR_LENGTH( w_city_name ) + 1 , CHAR_LENGTH( w_result ) - CHAR_LENGTH( w_city_name ) +1 );

     /* 字通り名削除 */
     IF( LEFT( w_result , CHAR_LENGTH( CONCAT( COALESCE( w_area_name , s_empty_string ) , COALESCE( w_street_name , s_empty_string ) , COALESCE( w_aza_name , s_empty_string ) ) ) ) = CONCAT( COALESCE( w_area_name , s_empty_string ) , COALESCE( w_street_name , s_empty_string ) , COALESCE( w_aza_name , s_empty_string ) ) ) THEN

       SET w_result = SUBSTRING( w_result , CHAR_LENGTH( CONCAT( COALESCE( w_area_name , s_empty_string ) , COALESCE( w_street_name , s_empty_string ) , COALESCE( w_aza_name , s_empty_string ) ) ) + 1 , CHAR_LENGTH( w_result ) - CHAR_LENGTH( CONCAT( COALESCE( w_area_name , s_empty_string ) , COALESCE( w_street_name , s_empty_string ) , COALESCE( w_aza_name , s_empty_string ) ) ) + 1 );

       IF( LEFT( w_result , 1 ) = '-' ) THEN

         SET w_result = SUBSTRING( w_result , 2 , CHAR_LENGTH( w_result ) - 1 );

       END IF;

     ELSEIF( LEFT( w_result , CHAR_LENGTH( CONCAT( COALESCE( w_area_name , s_empty_string ) , COALESCE( w_street_name , s_empty_string ) , REPLACE(COALESCE( w_aza_name , ''),'丁目','') ) ) ) = CONCAT( COALESCE( w_area_name , '') , COALESCE( w_street_name , '' ) , REPLACE(COALESCE( w_aza_name , ''),'丁目','') ) ) THEN

       SET w_result = SUBSTRING( w_result , CHAR_LENGTH( CONCAT( COALESCE( w_area_name , s_empty_string ) , COALESCE( w_street_name , s_empty_string ) , REPLACE(COALESCE( w_aza_name , ''),'丁目', s_empty_string ) ) ) + 1 , CHAR_LENGTH( w_result ) - CHAR_LENGTH( CONCAT( COALESCE( w_area_name , s_empty_string ) , COALESCE( w_street_name , s_empty_string ) , REPLACE(COALESCE( w_aza_name , s_empty_string ),'丁目', s_empty_string ) ) ) + 1 );

       IF( LEFT( w_result , 1 ) = '-' ) THEN

         SET w_result = SUBSTRING( w_result , 2 , CHAR_LENGTH( w_result ) - 1 );

       END IF;

     END IF;

   END IF;

 END IF;

 /*  ログ出力*/
 IF( s_log_flg = 1 ) THEN
        SET w_log_result =  CMSF_FUNC_PUT_LOG( s_log_created_by , s_log_prj_name , s_log_apps_name , s_log_mod_name , s_log_prg_var , s_log_type_text
                                             , '2.1.2 パラメータとして渡されているデータをセット' , s_empty_string , s_empty_string , s_empty_string , s_empty_string , s_empty_string , s_empty_string );
 END IF;

 /*  ログ出力処理  */
 IF( s_log_flg = 1 ) THEN
        SET w_log_result =  CMSF_FUNC_PUT_LOG( s_log_created_by , s_log_prj_name , s_log_apps_name , s_log_mod_name , s_log_prg_var , s_log_type_text
                                             , CONCAT( '9.9.9 処理完了 :' , w_result  ) , s_empty_string , w_flg , w_result , s_empty_string , s_empty_string , s_empty_string );
 END IF;

 return w_result;

END;;
DELIMITER ;

-- ----------------------------
-- Records 
-- ----------------------------
