CREATE DATABASE  IF NOT EXISTS `webform` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `webform`;
-- MySQL dump 10.13  Distrib 5.5.16, for Win32 (x86)
--
-- Host: localhost    Database: webform
-- ------------------------------------------------------
-- Server version	5.5.16

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `mst_users`
--

DROP TABLE IF EXISTS `mst_users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mst_users` (
  `id` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `operator_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `role` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `role_text` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_password_change` datetime DEFAULT NULL,
  `initial_password` bit(1) DEFAULT NULL,
  `lock_flag` bit(1) DEFAULT NULL,
  `failed_count` int(11) DEFAULT NULL,
  `last_login_date` datetime DEFAULT NULL,
  `last_login_date2` datetime DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `disabled` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `mst_users`
--

LOCK TABLES `mst_users` WRITE;
/*!40000 ALTER TABLE `mst_users` DISABLE KEYS */;
INSERT INTO `mst_users` VALUES ('999999','8d5cd6f0b6fa1f5f8cecb34400f82d2ba0c41720','admin','1,2,3,4','Read,Write,CSV Download,User Manager','2012-02-22 17:19:49','\0','\0',0,'2012-02-23 10:22:31','2012-02-23 08:55:03','2012-02-19 11:00:00','\0');
/*!40000 ALTER TABLE `mst_users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `download_logs`
--

DROP TABLE IF EXISTS `download_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `download_logs` (
  `operator_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `download_filename` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `download_count` int(11) DEFAULT NULL,
  `download_date` datetime DEFAULT NULL,
  `ip_address` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `session_id` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `download_log_operator_id` (`operator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `download_logs`
--

LOCK TABLES `download_logs` WRITE;
/*!40000 ALTER TABLE `download_logs` DISABLE KEYS */;
/*!40000 ALTER TABLE `download_logs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_datas`
--

DROP TABLE IF EXISTS `request_datas`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_datas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sei_kanji` varchar(50) DEFAULT NULL,
  `mei_kanji` varchar(50) DEFAULT NULL,
  `sei_kana` varchar(50) DEFAULT NULL,
  `mei_kana` varchar(50) DEFAULT NULL,
  `birth_date` varchar(8) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `gender_text` varchar(100) DEFAULT NULL,
  `zip1` varchar(10) DEFAULT NULL,
  `zip2` varchar(10) DEFAULT NULL,
  `address1` varchar(60) DEFAULT NULL,
  `address2` varchar(60) DEFAULT NULL,
  `address3` varchar(80) DEFAULT NULL,
  `address4` varchar(80) DEFAULT NULL,
  `tel1` varchar(10) DEFAULT NULL,
  `tel2` varchar(10) DEFAULT NULL,
  `tel3` varchar(10) DEFAULT NULL,
  `m_tel1` varchar(10) DEFAULT NULL,
  `m_tel2` varchar(10) DEFAULT NULL,
  `m_tel3` varchar(10) DEFAULT NULL,
  `e_mail` varchar(100) DEFAULT NULL,
  `occupation` int(11) DEFAULT NULL,
  `occupation_text` varchar(50) DEFAULT NULL,
  `request_date` datetime DEFAULT NULL,
  `ip_address` varchar(100) DEFAULT NULL,
  `session_id` varchar(100) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `status_text` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `request_data_e_mail` (`e_mail`),
  KEY `request_data_sei_kanji` (`sei_kanji`),
  KEY `request_data_mei_kanji` (`mei_kanji`),
  KEY `request_data_sei_kana` (`sei_kana`),
  KEY `request_data_mei_kana` (`mei_kana`),
  KEY `request_data_date` (`request_date`),
  KEY `request_data_zip1` (`zip1`),
  KEY `request_data_zip2` (`zip2`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_datas`
--

LOCK TABLES `request_datas` WRITE;
/*!40000 ALTER TABLE `request_datas` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_datas` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `login_logs`
--

DROP TABLE IF EXISTS `login_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `login_logs` (
  `operator_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `login_successful` bit(1) DEFAULT NULL,
  `login_date` datetime DEFAULT NULL,
  `ip_address` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `session_id` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `login_log_operator_id` (`operator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `login_logs`
--

LOCK TABLES `login_logs` WRITE;
/*!40000 ALTER TABLE `login_logs` DISABLE KEYS */;
/*!40000 ALTER TABLE `login_logs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `status_histories`
--

DROP TABLE IF EXISTS `status_histories`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `status_histories` (
  `id` int(11) DEFAULT NULL,
  `old_status` int(11) DEFAULT NULL,
  `old_status_text` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `new_status` int(11) DEFAULT NULL,
  `new_status_text` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `comment` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `operator_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `status_history_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `status_histories`
--

LOCK TABLES `status_histories` WRITE;
/*!40000 ALTER TABLE `status_histories` DISABLE KEYS */;
/*!40000 ALTER TABLE `status_histories` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping routines for database 'webform'
--
/*!50003 DROP PROCEDURE IF EXISTS `execute_login` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `execute_login`(
_id varchar(50),
_successful bit,
_failed_count int,
_lock_flag bit,
_ip_address varchar(256),
_session_id varchar(256)
)
BEGIN
    DECLARE  EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN  
        ROLLBACK; 
    END;

START TRANSACTION;
IF _successful = 1 Then
    UPDATE mst_users SET 
		failed_count = 0,
           last_login_date2 = last_login_date,
           last_login_date = now()
		WHERE  id = _id;
    
    INSERT INTO login_logs (
            operator_id,
            login_successful,
            login_date,
            ip_address,
            session_id) 
            VALUES (
            _id,
            1,
            now(),
            _ip_address,
            _session_id
            );
ELSE 
  UPDATE mst_users SET 
		failed_count = _failed_count,
            lock_flag = _lock_flag
 		WHERE  id = _id;
        
           INSERT INTO login_logs (
            operator_id,
            login_successful,
            login_date,
            ip_address,
            session_id) 
            VALUES (
            _id,
            0,
            now(),
            _ip_address,
            _session_id
            );
END IF;
 COMMIT;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_download_log` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `insert_download_log`(
_operator_id varchar(50),
_download_filename varchar(256),
_download_count  int,
_ip_address varchar(256),
_session_id varchar(256)
)
BEGIN
	INSERT INTO download_logs(
		operator_id,
		download_filename,
		download_count,
           download_date,
           ip_address,
		session_id
        ) Values (
            _operator_id,
		_download_filename,
		_download_count,
           now(),
           _ip_address,
		_session_id
	);
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_mst_user` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `insert_mst_user`(
id varchar(50),
encrypted_password varchar(256),
operator_name  varchar(50),
role varchar(100),
role_text varchar(256)
)
BEGIN
	INSERT INTO mst_users(
		id,
		password,
		operator_name,
           role,
           role_text,
		last_password_change,
		initial_password,
		lock_flag,
		failed_count,
		date_created,
		disabled
	) Values (
            id,
		encrypted_password,
		operator_name,
           role,
           role_text,
		now(),
		1,
		0,
		0,
		now(),
		0
	);
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_request` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `insert_request`(
sei_kanji varchar(50),
mei_kanji varchar(50),
sei_kana varchar(50),
mei_kana varchar(50),
birth_date varchar(8),
gender int,
gender_text varchar(100),
zip1 varchar(10),
zip2 varchar(10),
address1 varchar(60),
address2 varchar(60),
address3 varchar(80),
address4 varchar(80),
tel1 varchar(10),
tel2 varchar(10),
tel3 varchar(10),
m_tel1 varchar(10),
m_tel2 varchar(10),
m_tel3 varchar(10),
e_mail varchar(100),
occupation int,
occupation_text varchar(100),
ip_address varchar(100),
session_id varchar(100)
)
BEGIN
	INSERT INTO request_datas (
		sei_kanji,
           mei_kanji,
		sei_kana,
		mei_kana,
		birth_date,
		gender,
		gender_text,
		zip1,
		zip2,
		address1,
		address2,
		address3,
		address4,
		tel1,
		tel2,
		tel3,
		m_tel1,
		m_tel2,
		m_tel3,
		e_mail,
		occupation,
		occupation_text,
		request_date,
		ip_address,
		session_id,
		status,
		status_text
	) Values (
		sei_kanji,
           mei_kanji,
		sei_kana,
		mei_kana,
		birth_date,
		gender,
		gender_text,
		zip1,
		zip2,
		address1,
		address2,
		address3,
		address4,
		tel1,
		tel2,
		tel3,
		m_tel1,
		m_tel2,
		m_tel3,
		e_mail,
		occupation,
		occupation_text,
		now(),
		ip_address,
		session_id,
		'0',
		'正常'
	);
    
    
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_status_history` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `insert_status_history`(
_id int,
_new_status int,
_new_status_text varchar(100),
_old_status int,
_old_status_text varchar(100),
_comment varchar(1024),
_operator_id varchar(50)
)
BEGIN 
    DECLARE  EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN  
        ROLLBACK; 
    END;
    
    START TRANSACTION;
    
    UPDATE request_datas SET 
		status = _new_status,
           status_text= _new_status_text
		WHERE id = _id; 
        
	INSERT INTO status_histories (
		id,
		old_status,
		old_status_text,
		new_status,
		new_status_text,
		comment,
		create_date,
		operator_id
	) Values (
		_id,
		_old_status,
		_old_status_text,
           _new_status,
		_new_status_text,
		_comment,
		now(), 
		_operator_id
	);
    
     COMMIT;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `update_mst_user` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `update_mst_user`(
_id varchar(50),
_operator_name  varchar(50),
_role varchar(100),
_role_text varchar(256),
_lock_flag bit,
_disabled bit
)
BEGIN
    UPDATE mst_users SET 
		operator_name = _operator_name,
		 role = _role,
            role_text = _role_text,
           failed_count = 0,
		lock_flag = _lock_flag,
		disabled = _disabled
		WHERE  id = _id;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `update_password` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `update_password`(
    _id varchar(50),
    _initial_password bit,
    _encrypted_password varchar(256)
)
BEGIN
	UPDATE mst_users SET 
		password = _encrypted_password,
		initial_password = _initial_password,
		last_password_change = now() 
		WHERE id = _id;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-02-23 10:23:58
