///  20006.06.07
INSERT INTO `fields_meta_data` VALUES ('Schooltel_c', 'tel_c', 'tel_c', 'School', 'varchar', 25, 'optional', NULL, '2006-06-06 17:48:58', 0, 0, 'moduleList', '0', '0');
INSERT INTO `fields_meta_data` VALUES ('Schooladdress_c', 'address_c', 'address_c', 'School', 'varchar', 128, 'optional', NULL, '2006-06-06 17:49:34', 0, 0, 'moduleList', '0', '0');
INSERT INTO `fields_meta_data` VALUES ('Schoolrules_c', 'rules_c', 'rules_c', 'School', 'text', 255, 'optional', NULL, '2006-06-07 09:10:56', 0, 0, 'moduleList', '0', '0');
INSERT INTO `fields_meta_data` VALUES ('Schoolfax_c', 'fax_c', 'fax_c', 'School', 'varchar', 25, 'optional', NULL, '2006-06-07 09:13:44', 0, 0, 'moduleList', '0', '0');
INSERT INTO `fields_meta_data` VALUES ('Schoolid_label_c', 'id_label_c', 'id_label_c', 'School', 'varchar', 100, 'optional', NULL, '2006-06-07 09:22:05', 0, 0, 'moduleList', '0', '0');



-- Added  new fields to the contacts table 
ALTER TABLE `contacts` ADD `send_document_home` VARCHAR( 3 ) DEFAULT 'on' NOT NULL ;
ALTER TABLE `contacts` ADD `send_document_work` VARCHAR( 3 ) DEFAULT 'on' NOT NULL ;
ALTER TABLE `contacts` ADD `company_name` VARCHAR( 100 ) ;


-- We have to copy yubin table from nikicrm35 database to schoolmng database
-- this operation only can be made on command line of mysql 

CREATE TABLE `schoolmng`.`yubin` (
`postcode` int( 7 ) unsigned default NULL ,
`kenkana` varchar( 64 ) default NULL ,
`citykana` varchar( 128 ) default NULL ,
`addresskana` varchar( 255 ) default NULL ,
`ken` varchar( 32 ) default NULL ,
`city` varchar( 64 ) default NULL ,
`address` varchar( 255 ) default NULL ,
KEY `postcode` ( `postcode` ) ,
KEY `ken` ( `ken` ) ,
KEY `city` ( `city` ) 
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

INSERT INTO `schoolmng`.`yubin` 
SELECT * 
FROM `nikicrm35`.`yubin` WHERE postcode > 100000 AND postcode < 110000;

// for localhost
INSERT INTO schoolmng.yubin 
SELECT * 
FROM nikicrm35.yubin 
WHERE nikicrm35.yubin.postcode > 100000 AND nikicrm35.yubin.postcode < 110000;




--- 2006.07.15
--- add quoq_number to sms_transaction table
--- to process monthly quoq import 
ALTER TABLE `sms_transaction` ADD `quoq_number` VARCHAR( 30 ) NOT NULL ;



-- 2006-12-10 
-- Additional fields to be used in Id Card Printing
ALTER TABLE `contacts` ADD `id_card_field1` VARCHAR( 50 ) AFTER `application_date` ,
ADD `id_card_field2` VARCHAR( 50 ) AFTER `id_card_field1` ,
ADD `id_card_field3` VARCHAR( 50 ) AFTER `id_card_field2` ;


ALTER TABLE `contacts` ADD `id_card_field4` VARCHAR( 50 ) AFTER `id_card_field3` ;




-- 2007-01-04 
-- 
-- Table structure for table `postcard`
-- 

CREATE TABLE `postcard` (
  `id` varchar(36) NOT NULL default '',
  `date_entered` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_by` varchar(36) default NULL,
  `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) NOT NULL default '0',
  `deleted` tinyint(1) NOT NULL default '0',
  `title` varchar(255) NOT NULL default '',
  `description` text,
  `picture_filename` varchar(255) default NULL,
  `address_placement` varchar(30) default NULL,
  `print_horizontally` varchar(3) default 'off',
  `school_id` varchar(36) NOT NULL default 'sssssss-1111',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
        

-- 2007-01-09
--

ALTER TABLE `postcard` ADD `print_address_horizontally` VARCHAR( 3 ) DEFAULT 'off' NOT NULL AFTER `print_horizontally` ;
ALTER TABLE `postcard` ADD `sender_address` VARCHAR( 3 ) DEFAULT 'off' NOT NULL AFTER `print_address_horizontally` ;
ALTER TABLE `postcard` ADD `yubin_hagaki` VARCHAR( 3 ) DEFAULT 'off' NOT NULL AFTER `sender_address` ;
ALTER TABLE `postcard` ADD `school_postcode` VARCHAR( 10 ) ;
ALTER TABLE `postcard` ADD `school_address` TEXT;

-- 2007-01-16
ALTER TABLE `accounts` ADD `custom_var41` TEXT,
ADD `custom_var42` TEXT,
ADD `custom_var43` TEXT,
ADD `custom_var44` TEXT,
ADD `custom_var45` TEXT,
ADD `custom_var46` TEXT,
ADD `custom_var47` TEXT,
ADD `custom_var48` TEXT,
ADD `custom_var49` TEXT,
ADD `custom_var50` TEXT,
ADD `custom_var51` TEXT,
ADD `custom_var52` TEXT,
ADD `custom_var53` TEXT,
ADD `custom_var54` TEXT,
ADD `custom_var55` TEXT,
ADD `custom_var56` TEXT,
ADD `custom_var57` TEXT,
ADD `custom_var58` TEXT,
ADD `custom_var59` TEXT,
ADD `custom_var60` TEXT;



-- 2007-01-17 -- merdan
ALTER TABLE `accounts` ADD `billing_address_kana` VARCHAR( 250 ) AFTER `billing_address_country` ;

-- 2007-01-17 -- merdan (update for school custom field)
-- NOTE: please do not apply this if you can add last_teacher_id from admin page
ALTER TABLE `resourceschool_cstm` ADD `last_teacher_id_c` VARCHAR( 10 );
INSERT INTO `fields_meta_data` values
('Schoollast_teacher_id_c', 'last_teacher_id_c', 'last_teacher_id_c', 'School', 'varchar', 10, 'optional', NULL, '2007-01-17 02:20:31', 0, 0, 'moduleList', '0', '0');
        

-- 2007-02-01 --merdan
ALTER TABLE `resourceschool_cstm` ADD `rules_teacher_c` TEXT;
ALTER TABLE `resourceschool_cstm` ADD `rules_staff_c` TEXT ;
ALTER TABLE `resourceschool_cstm` ADD `bank_account_c` TEXT ;
ALTER TABLE `resourceschool_cstm` ADD `tantosha_c` VARCHAR(50);

INSERT INTO `fields_meta_data` VALUES ('Schoolrules_teacher_c', 'rules_teacher_c', 'rules_teacher_c', 'School', 'text', 255, 'optional', NULL, '2007-02-01 01:04:56', 0, 0, 'moduleList', '0', '0');
INSERT INTO `fields_meta_data` VALUES ('Schoolrules_staff_c', 'rules_staff_c', 'rules_staff_c', 'School', 'text', 255, 'optional', NULL, '2007-02-01 01:05:14', 0, 0, 'moduleList', '0', '0');
INSERT INTO `fields_meta_data` VALUES ('Schoolbank_account_c', 'bank_account_c', 'bank_account_c', 'School', 'text', 255, 'optional', NULL, '2007-02-01 01:05:37', 0, 0, 'moduleList', '0', '0');
INSERT INTO `fields_meta_data` VALUES ('Schooltantosha_c', 'tantosha_c', 'tantosha_c', 'School', 'varchar', 50, 'optional', NULL, '2007-02-01 07:33:33', 0, 0, 'moduleList', '0', '0');
             

            
-- 2007-02-01   --merdan
ALTER TABLE `contacts` CHANGE `contact_status` `contact_status` VARCHAR( 3 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0';
ALTER TABLE `event_contacts` CHANGE `event_contact_status` `event_contact_status` VARCHAR( 3 ) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' ;

-- 2007-02-07
UPDATE `resource` SET `school_id` = '01' WHERE  `school_id`= '001' ;
UPDATE `resource` SET `school_id` = '02' WHERE  `school_id`= '002' ;
UPDATE `resource` SET `school_id` = '03' WHERE  `school_id`= '003' ;
UPDATE `resource` SET `school_id` = '04' WHERE  `school_id`= '004' ;
UPDATE `resource` SET `school_id` = '05' WHERE  `school_id`= '005' ;


-- 2007-02-08 --merdan
update contacts set idnumber = CONCAT(SUBSTRING(idnumber,2,2), SUBSTRING(idnumber, 6)) where LENGTH(idnumber)=10;  


-- 2007-02-16 --merdan
ALTER TABLE `sms_transaction` ADD `account_id` VARCHAR( 36 ) AFTER `contact_id` ;
ALTER TABLE `sms_salesgroup` ADD `account_id` VARCHAR( 36 ) AFTER `contact_id` ;


-- 2007-02-19 -- merdan
ALTER TABLE `sms_salesgroup` ADD `bank_account` TEXT;


-- 2007-05-02 --merdan
ALTER TABLE `sms_salesgroup` ADD `hakkoubi` DATE NOT NULL ;
ALTER TABLE `sms_salesgroup` ADD `seikyusaki` TEXT;

ALTER TABLE `sms_fee` ADD `jikyu_rate` VARCHAR( 20 ) ;


-- 2008-04-03 --merdan
-- 
-- Table structure for table `bankactions_history`
-- 

CREATE TABLE `bankactions_history` (
  `id` varchar(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `input_type` varchar(10) NOT NULL,
  `total_count` varchar(10) default '0',
  `total_in_amount` varchar(20) default '0',
  PRIMARY KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


-- 
-- Table structure for table `bankinfo_history`
-- 

CREATE TABLE `bankinfo_history` (
  `id` varchar(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `bankactions_id` varchar(36) NOT NULL,
  `input_type` varchar(10) default NULL,
  `bank_code` varchar(4) default NULL,
  `bank_name` varchar(15) default NULL,
  `branch_code` varchar(3) default NULL,
  `branch_name` varchar(15) default NULL,
  `account` varchar(7) default NULL,
  `name` varchar(30) default NULL,
  `in_amount` varchar(10) default NULL,
  `new_code` varchar(1) default NULL,
  `salesno` varchar(10) default NULL,
  `result_code` varchar(1) default NULL,
  `error_code` varchar(20) default '0',
  PRIMARY KEY `id` (`id`),
  KEY `bankactions_id` (`bankactions_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 
-- Table structure for table `keiriposactions_history`
-- 

CREATE TABLE `keiriposactions_history` (
  `id` varchar(36) NOT NULL,
  `school_id` varchar(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `total_count` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 
-- Table structure for table `keiripos_history`
-- 

CREATE TABLE `keiripos_history` (
  `id` varchar(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `keiriposactions_id` varchar(36) NOT NULL,
  `sell_date` date default NULL,
  `shop_id` varchar(4) default NULL,
  `sell_hour` varchar(2) default NULL,
  `sell_minute` varchar(2) default NULL,
  `tantou_code` varchar(20) default NULL,
  `contact_idnumber` varchar(10) default NULL,
  `pos_type1` varchar(30) default NULL,
  `pos_type2` varchar(30) default NULL,
  `bumon` varchar(3) default NULL,
  `sub_genre` varchar(10) default NULL,
  `product_code` varchar(12) default NULL,
  `product_name` varchar(20) default '0',
  `jan_code` varchar(13) default NULL,
  `basic_fee` varchar(10) default NULL,
  `product_count` varchar(4) default NULL,
  `basic_fee_total` varchar(10) default NULL,
  `product_stock` varchar(5) default NULL,
  `idou_average` varchar(8) default NULL,
  `product_original_price` varchar(10) default NULL,
  `tax_class` varchar(1) default NULL,
  `contact_age` varchar(4) default NULL,
  `contact_gender` varchar(3) default NULL,
  `process_status` varchar(30) default NULL,
  PRIMARY KEY  (`id`),
  KEY `bankactions_id` (`keiriposactions_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;