ALTER TABLE `sms_fee` ADD `extra_kubun1` VARCHAR( 256 ) NOT NULL ;
ALTER TABLE `sms_fee` ADD `extra_kubun2` VARCHAR( 256 ) NOT NULL ;
ALTER TABLE `sms_fee` ADD `extra_kubun3` VARCHAR( 256 ) NOT NULL ;
ALTER TABLE `sms_fee` CHANGE `extra_kubun1` `extra_kubun1` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
CHANGE `extra_kubun2` `extra_kubun2` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
CHANGE `extra_kubun3` `extra_kubun3` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;

ALTER TABLE `event_contacts` ADD `application_date` DATE NOT NULL , ADD `entrance_date` DATE NOT NULL ;
ALTER TABLE `event_contacts` ADD `course_kubun` VARCHAR( 255 ) NOT NULL , ADD `contents` VARCHAR( 255 ) NOT NULL ;

ALTER TABLE `sms_fee` CHANGE `salesno` `salesno` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `sms_transaction` CHANGE `salesno` `salesno` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL; 
ALTER TABLE `sms_salesgroup` CHANGE `salesno` `salesno` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL; 

ALTER TABLE `sms_fee` ADD `hinmei` VARCHAR( 64 ) NULL ;

ALTER TABLE `product` ADD `subcategory` VARCHAR( 64 ) NULL AFTER `productcategory_id` ;


CREATE TABLE IF NOT EXISTS `productinout` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) DEFAULT NULL,
  `created_by` varchar(36) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `product_id` varchar(36) DEFAULT NULL,
  `category` varchar(16) DEFAULT NULL,
  `price` decimal(20,2) NOT NULL DEFAULT '0.00',
  `date_buy_sell` date DEFAULT NULL,
  `quantity` int(5) NOT NULL DEFAULT '0',
  `school_id` varchar(36) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT id, cost_price, discount_price, list_price FROM `product` WHERE discount_price IS NOT NULL ;
UPDATE product SET list_price=discount_price WHERE discount_price IS NOT NULL ;

SELECT * FROM `smsevent` WHERE `category` LIKE 'course' AND `subtype` =2 ;
SELECT * FROM `smsevent` WHERE `category` LIKE 'course' AND `subtype` =3 ;

UPDATE smsevent SET subtype=4 WHERE `category` LIKE 'course' AND `subtype` =2 ;
UPDATE smsevent SET subtype=5 WHERE `category` LIKE 'course' AND `subtype` =3 ;

SELECT * FROM `sms_fee` WHERE `product_id` IS NOT NULL AND `product_count` =0 AND contact_id IS NULL ; 
UPDATE `sms_fee` SET product_count=1 WHERE `product_id` IS NOT NULL AND `product_count` =0 AND contact_id IS NULL ; 

UPDATE sms_fee SET deleted=1 WHERE id IN
('coursefee02723','coursefee02724','coursefee02726','coursefee02727','coursefee02728','coursefee02729',
'coursefee02730','coursefee02731','coursefee02732','coursefee02733','coursefee02734','coursefee02735',
'coursefee02736','coursefee02747','coursefee02748','coursefee02749','coursefee02750','coursefee02751',
'coursefee02752','coursefee02753','coursefee02754','coursefee02756','coursefee02757','coursefee02758',
'coursefee02759','coursefee02760','coursefee02761','coursefee02762','coursefee02763','coursefee02714',
'coursefee02715','coursefee02716','coursefee02717','coursefee02718','coursefee02719','coursefee02720',
'coursefee02721','coursefee02722')

SELECT sms_fee.id, sms_fee.name, sms_fee_update.name FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_name=1 ;
UPDATE sms_fee, sms_fee_update SET sms_fee.name=sms_fee_update.name WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_name=1 ;

SELECT sms_fee.id, sms_fee.event_id, sms_fee_update.event_id FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_event_id=1 ;
UPDATE sms_fee, sms_fee_update SET sms_fee.event_id=sms_fee_update.event_id WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_event_id=1 ;

SELECT sms_fee.id, sms_fee.basic_fee, sms_fee_update.basic_fee FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_basic_fee=1 ;
UPDATE sms_fee, sms_fee_update SET sms_fee.basic_fee=sms_fee_update.basic_fee WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_basic_fee=1 ;

SELECT sms_fee.id, sms_fee.extra_kubun2, sms_fee_update.extra_kubun2 FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_extra_kubun2=1 ;
UPDATE sms_fee, sms_fee_update SET sms_fee.extra_kubun2=sms_fee_update.extra_kubun2 WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_extra_kubun2=1 ;

SELECT sms_fee.id, sms_fee.extra_kubun3, sms_fee_update.extra_kubun3 FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_extra_kubun3=1 ;
UPDATE sms_fee, sms_fee_update SET sms_fee.extra_kubun3=sms_fee_update.extra_kubun3 WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_extra_kubun3=1 ;

SELECT sms_fee.id, sms_fee.name, sms_fee.extra_kubun3, sms_fee_update.extra_kubun3 FROM sms_fee, sms_fee_update WHERE sms_fee.id=sms_fee_update.id AND sms_fee_update.modified_extra_kubun3=1 ;


SELECT product.id, product.discount_price, product.list_price, product2.discount_price as discount_price2, product2.list_price list_price2
FROM product, product2 WHERE product.id=product2.id
AND product.id='9801'

UPDATE product, product2 SET product.discount_price = product2.list_price, product.list_price = product2.list_price
WHERE product.id=product2.id

SELECT smsevent3.id, smsevent3.new_id  FROM smsevent3, smsevent WHERE smsevent.id=smsevent3.new_id; 

UPDATE product SET subcategory=NULL WHERE `subcategory` LIKE '-' ;

INSERT INTO `resource` (`id`, `school_id`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `shortname`, `description`, `category`, `location`, `deleted`, `capacity`, `resource_attributes`, `common_code`) VALUES
('1001', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '601教室', '601', '立川本部校', 'classroom', NULL, 0, 20, 'YTowOnt9', NULL),
('1002', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '602教室', '602', '立川本部校', 'classroom', NULL, 0, 20, 'YTowOnt9', NULL),
('1003', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '603教室', '603', '立川本部校', 'classroom', NULL, 0, 20, 'YTowOnt9', NULL),
('1004', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '701教室', '701', '立川本部校', 'classroom', NULL, 0, 30, 'YTowOnt9', NULL),
('1005', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '702教室', '702', '立川本部校', 'classroom', NULL, 0, 30, 'YTowOnt9', NULL),
('1006', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '703教室', '703', '立川本部校', 'classroom', NULL, 0, 10, 'YTowOnt9', NULL),
('1007', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '第2校', '703', '立川第2校', 'classroom', NULL, 0, 120, 'YTowOnt9', NULL),
('1008', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '301教室', '301', '立川MEP校', 'classroom', NULL, 0, 40, 'YTowOnt9', NULL),
('1009', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '302教室', '302', '立川MEP校', 'classroom', NULL, 0, 40, 'YTowOnt9', NULL),
('1010', 'sssssss-1111', '2011-03-20 08:00:00', '2011-03-20 08:00:00', '1', '1', '303教室', '303', '立川MEP校', 'classroom', NULL, 0, 40, 'YTowOnt9', NULL);


DELETE FROM `contacts` WHERE `date_entered` = '2011-03-20 10:00'

SELECT event_contacts.event_id, smsevent3.id, smsevent3.new_id  FROM event_contacts, smsevent3 WHERE event_contacts.event_id=smsevent3.id; 

SELECT event_contacts.event_id, smsevent3.id, smsevent3.new_id  FROM event_contacts
LEFT JOIN smsevent3 ON event_contacts.event_id=smsevent3.id WHERE smsevent3.id IS NULL

SELECT distinct event_contacts.event_id FROM event_contacts
LEFT JOIN smsevent3 ON event_contacts.event_id = smsevent3.id
WHERE smsevent3.id IS NULL

SELECT event_contacts.event_id, smsevent3.id, smsevent3.new_id FROM event_contacts, smsevent3 WHERE event_contacts.event_id = smsevent3.id

UPDATE event_contacts, smsevent3 SET event_contacts.event_id=new_id  WHERE event_contacts.event_id = smsevent3.id
UPDATE event_contacts2, smsevent3 SET event_contacts2.event_id=new_id  WHERE event_contacts2.event_id = smsevent3.id

UPDATE productcategory SET NAME="PAD(初級ベーシック)" WHERE id='9900';
UPDATE productcategory SET NAME="PAD(初級アドバンス)" WHERE id='9910';
UPDATE productcategory SET NAME="PAD(中級講座)" WHERE id='9920';
UPDATE productcategory SET NAME="PAD(高速基本)" WHERE id='9930';

SELECT id, idnumber FROM contacts WHERE idnumber IN ('0810140058', '0810100000') ;
UPDATE contacts SET idnumber=id WHERE idnumber IN ('0810140058', '0810100000') ;

SELECT users.id,user_name, last_name, first_name, idnumber FROM users, contacts 
WHERE users.contact_id=contacts.id AND contacts.smsgroup='teacher' AND contacts.deleted=0 ORDER BY contacts.idnumber ASC

SELECT DISTINCT assigned_user_id FROM `contacts` WHERE smsgroup='student' AND deleted=0 
AND assigned_user_id NOT IN ('a437cc11-564c-e5c4-3ff0-4c88c6961e3b', 'c4565fc9-30f9-719c-cb31-4437354e807d')

	Edit 	Delete 	0810190001
	Edit 	Delete 	0810190005
	Edit 	Delete 	0810190006
	Edit 	Delete 	0810190007
	Edit 	Delete 	0810190008
	Edit 	Delete 	0810190009
	Edit 	Delete 	0810190010
	Edit 	Delete 	0810190011
	Edit 	Delete 	0810190013
	Edit 	Delete 	0810190014
	Edit 	Delete 	0810190015
	Edit 	Delete 	0810190016
	Edit 	Delete 	0810190017
	
SELECT DISTINCT reports_to_id FROM `contacts` WHERE smsgroup='student' AND deleted=0 
AND reports_to_id>'0'

	Edit 	Delete 	0810190002
	Edit 	Delete 	0810190005
	Edit 	Delete 	0810190009
	Edit 	Delete 	0810190013
	Edit 	Delete 	0810190014
	Edit 	Delete 	0810190026
	

SELECT 'XXXX', users.id,idnumber,'YYYY' FROM users, contacts 
WHERE users.contact_id=contacts.id AND contacts.smsgroup='teacher' AND contacts.deleted=0 ORDER BY contacts.idnumber ASC


UPDATE contacts SET assigned_user_id='82a02561-4084-ab73-4109-4d3791710603' WHERE assigned_user_id ='0810190000';
UPDATE contacts SET assigned_user_id='236b0a68-5463-3188-bc02-4d8aa56b3bb7' WHERE assigned_user_id ='0810190001';
UPDATE contacts SET assigned_user_id='4ef5f4c9-02be-337a-d2ea-4d8aa709d9e5' WHERE assigned_user_id ='0810190002';
UPDATE contacts SET assigned_user_id='445d7b5e-a48d-55a2-6c79-4d8aa7227ae7' WHERE assigned_user_id ='0810190003';
UPDATE contacts SET assigned_user_id='6414547c-173d-152b-64e6-4d8aa5775197' WHERE assigned_user_id ='0810190004';
UPDATE contacts SET assigned_user_id='3d8fde0b-0a0e-e8ed-0ac5-4d8aa6cee9bb' WHERE assigned_user_id ='0810190005';
UPDATE contacts SET assigned_user_id='25a50ba3-f8ed-d211-4da3-4d8aa71f97e2' WHERE assigned_user_id ='0810190006';
UPDATE contacts SET assigned_user_id='d7182dac-105d-0a20-2786-4d8aa6819efb' WHERE assigned_user_id ='0810190007';
UPDATE contacts SET assigned_user_id='e1913fd2-c03d-6aaa-6f04-4d8aa239b8f3' WHERE assigned_user_id ='0810190008';
UPDATE contacts SET assigned_user_id='b2ab8f92-0f9d-6d91-ca6e-4d8aa5708132' WHERE assigned_user_id ='0810190009';
UPDATE contacts SET assigned_user_id='cc3df45b-3f39-d600-53a3-4d8aa6e8effd' WHERE assigned_user_id ='0810190010';
UPDATE contacts SET assigned_user_id='fcff00aa-2ea3-2c41-d8f2-4d8aa64a49e3' WHERE assigned_user_id ='0810190011';
UPDATE contacts SET assigned_user_id='4aee34d7-0109-9ae1-59ba-4d8aa6524c4e' WHERE assigned_user_id ='0810190012';
UPDATE contacts SET assigned_user_id='33f69107-f01f-7bd0-9361-4d8aa7cfb487' WHERE assigned_user_id ='0810190013';
UPDATE contacts SET assigned_user_id='b7718260-01a6-d6e4-d5bb-4d8aa5e25cb3' WHERE assigned_user_id ='0810190014';
UPDATE contacts SET assigned_user_id='50ce182d-9ee9-0ff2-6c29-4d8aa601b2d3' WHERE assigned_user_id ='0810190015';
UPDATE contacts SET assigned_user_id='aef74633-c9a0-ae20-a5b7-4d8aa610792f' WHERE assigned_user_id ='0810190016';
UPDATE contacts SET assigned_user_id='57e3b68c-f07e-14bf-3f08-4d8aa5e3feb4' WHERE assigned_user_id ='0810190017';
UPDATE contacts SET assigned_user_id='5d3f9953-7efd-c13b-ebb4-4d8aa4edc778' WHERE assigned_user_id ='0810190018';
UPDATE contacts SET assigned_user_id='cb532f97-ec72-b94f-3259-4d8aa79b39a2' WHERE assigned_user_id ='0810190019';
UPDATE contacts SET assigned_user_id='65f635ee-88c1-b5f2-ffb5-4d8aa5322a0f' WHERE assigned_user_id ='0810190020';
UPDATE contacts SET assigned_user_id='e1cb1b2d-3007-d80b-6f0a-4d8aa58b0f6a' WHERE assigned_user_id ='0810190021';
UPDATE contacts SET assigned_user_id='4ea2870d-8c69-d965-6693-4d8aa626922e' WHERE assigned_user_id ='0810190022';
UPDATE contacts SET assigned_user_id='9c45bd7a-26e0-cf72-1201-4d8aa4aa2493' WHERE assigned_user_id ='0810190023';
UPDATE contacts SET assigned_user_id='efa59bf7-033d-94c9-c0cb-4d8aa188febf' WHERE assigned_user_id ='0810190024';
UPDATE contacts SET assigned_user_id='c708178d-d09d-a7df-e0aa-4d8aa612dd8f' WHERE assigned_user_id ='0810190025';
UPDATE contacts SET assigned_user_id='46385af7-5c22-41ef-7cac-4d8aa5bbd1f4' WHERE assigned_user_id ='0810190026';
UPDATE contacts SET assigned_user_id='2e0ed05f-31a6-71de-d0ed-4d8aa68f4d74' WHERE assigned_user_id ='0810190027';
UPDATE contacts SET assigned_user_id='cff0e98c-adfe-e72e-cdc8-4d3791dac350' WHERE assigned_user_id ='0810290000';
UPDATE contacts SET assigned_user_id='14bb1ece-4e37-30b8-befd-4d3791fb9d9e' WHERE assigned_user_id ='0810390000';


UPDATE contacts SET reports_to_id='4ef5f4c9-02be-337a-d2ea-4d8aa709d9e5' WHERE reports_to_id ='0810190002';
UPDATE contacts SET reports_to_id='3d8fde0b-0a0e-e8ed-0ac5-4d8aa6cee9bb' WHERE reports_to_id ='0810190005';
UPDATE contacts SET reports_to_id='b2ab8f92-0f9d-6d91-ca6e-4d8aa5708132' WHERE reports_to_id ='0810190009';
UPDATE contacts SET reports_to_id='33f69107-f01f-7bd0-9361-4d8aa7cfb487' WHERE reports_to_id ='0810190013';
UPDATE contacts SET reports_to_id='b7718260-01a6-d6e4-d5bb-4d8aa5e25cb3' WHERE reports_to_id ='0810190014';
UPDATE contacts SET reports_to_id='46385af7-5c22-41ef-7cac-4d8aa5bbd1f4' WHERE reports_to_id ='0810190026';
	

UPDATE sms_f SET extra_kubun1='236b0a68-5463-3188-bc02-4d8aa56b3bb7' WHERE extra_kubun1 ='0810190001';
UPDATE sms_f SET extra_kubun1='3d8fde0b-0a0e-e8ed-0ac5-4d8aa6cee9bb' WHERE extra_kubun1 ='0810190005';
UPDATE sms_f SET extra_kubun1='25a50ba3-f8ed-d211-4da3-4d8aa71f97e2' WHERE extra_kubun1 ='0810190006';
UPDATE sms_f SET extra_kubun1='d7182dac-105d-0a20-2786-4d8aa6819efb' WHERE extra_kubun1 ='0810190007';
UPDATE sms_f SET extra_kubun1='e1913fd2-c03d-6aaa-6f04-4d8aa239b8f3' WHERE extra_kubun1 ='0810190008';
UPDATE sms_f SET extra_kubun1='b2ab8f92-0f9d-6d91-ca6e-4d8aa5708132' WHERE extra_kubun1 ='0810190009';
UPDATE sms_f SET extra_kubun1='cc3df45b-3f39-d600-53a3-4d8aa6e8effd' WHERE extra_kubun1 ='0810190010';
UPDATE sms_f SET extra_kubun1='fcff00aa-2ea3-2c41-d8f2-4d8aa64a49e3' WHERE extra_kubun1 ='0810190011';
UPDATE sms_f SET extra_kubun1='4aee34d7-0109-9ae1-59ba-4d8aa6524c4e' WHERE extra_kubun1 ='0810190012';
UPDATE sms_f SET extra_kubun1='33f69107-f01f-7bd0-9361-4d8aa7cfb487' WHERE extra_kubun1 ='0810190013';
UPDATE sms_f SET extra_kubun1='b7718260-01a6-d6e4-d5bb-4d8aa5e25cb3' WHERE extra_kubun1 ='0810190014';
UPDATE sms_f SET extra_kubun1='50ce182d-9ee9-0ff2-6c29-4d8aa601b2d3' WHERE extra_kubun1 ='0810190015';
UPDATE sms_f SET extra_kubun1='aef74633-c9a0-ae20-a5b7-4d8aa610792f' WHERE extra_kubun1 ='0810190016';
UPDATE sms_f SET extra_kubun1='57e3b68c-f07e-14bf-3f08-4d8aa5e3feb4' WHERE extra_kubun1 ='0810190017';
	

SELECT sms_f.event_id, smsevent3.id, smsevent3.new_id FROM sms_f, smsevent3 WHERE sms_f.event_id = smsevent3.id
UPDATE sms_f, smsevent3 SET sms_f.event_id=new_id  WHERE sms_f.event_id = smsevent3.id

SELECT sms_t.event_id, smsevent3.id, smsevent3.new_id FROM sms_t, smsevent3 WHERE sms_t.event_id = smsevent3.id
UPDATE sms_t, smsevent3 SET sms_t.event_id=new_id  WHERE sms_t.event_id = smsevent3.id

UPDATE sms_s SET id=CONCAT('0', salesno-0810100000+081010000100) WHERE id='0810100014'

UPDATE sms_s SET id=CONCAT('0', salesno-0810100000+081010000100) WHERE 1;
UPDATE sms_s SET salesno=CONCAT('0', salesno-0810100000+081010000100) WHERE 1;
UPDATE sms_s SET name=salesno WHERE 1;
UPDATE sms_f SET salesno=CONCAT('0', salesno-0810100000+081010000100) WHERE 1;
UPDATE sms_t SET salesno=CONCAT('0', salesno-0810100000+081010000100) WHERE 1;


SELECT contacts.idnumber, custom_var7, custom_var8, custom_var9, custom_var10, bank_code, branch_code, katakana
FROM contacts, bank_info WHERE custom_var7=bank_code AND branch_code='000'  

UPDATE contacts, bank_info SET custom_var8=katakana
WHERE custom_var7=bank_code AND branch_code='000'  

SELECT contacts.idnumber, custom_var7, custom_var8, custom_var9, custom_var10, bank_code, branch_code, katakana
FROM contacts, bank_info WHERE custom_var7=bank_code AND branch_code=custom_var9  

UPDATE contacts, bank_info SET custom_var10=katakana
WHERE custom_var7=bank_code AND branch_code=custom_var9  


SELECT smsevent.custom_var36, smsevent.id as event_id, smsevent.custom_var37, 
	fee_subcategory, in_amount, actual_in_amount, actual_out_amount, payment_type  
FROM sms_transaction LEFT JOIN sms_fee ON sms_transaction.fee_id=sms_fee.id AND sms_fee.deleted=0 
LEFT JOIN smsevent ON sms_fee.event_id=smsevent.id AND smsevent.deleted=0 
WHERE sms_transaction.deleted=0 AND sms_fee.school_id='sssssss-1111' AND sms_fee.start_date>=2012-09-01 
	AND sms_transaction.t_date<'2012-09-01' 
GROUP BY payment_type, fee_subcategory

-- 201104-18 student status update

SELECT DISTINCT custom_var17 FROM contacts WHERE smsgroup='student' ;
	
UPDATE contacts SET custom_var17='退室・CD会員' WHERE custom_var17='CD会員' ;
UPDATE contacts SET custom_var17='受講・休室' WHERE custom_var17='休室' ;
UPDATE contacts SET custom_var17='一般・保護者' WHERE custom_var17='保護者' ;
UPDATE contacts SET custom_var17='一般・外部生' WHERE custom_var17='外部生' ;
UPDATE contacts SET custom_var17='受講・月1' WHERE custom_var17='月1' ;
UPDATE contacts SET custom_var17='受講・月2' WHERE custom_var17='月2' ;
UPDATE contacts SET custom_var17='退室・退室' WHERE custom_var17='退室' ;
UPDATE contacts SET custom_var17='受講・通常' WHERE custom_var17='通常' ;

UPDATE contacts SET custom_var17='一般・退室' WHERE custom_var17='退室・退室' AND contact_status='102'

	
-- 2011-04-16 yahya (add index)
ALTER TABLE `event_contacts` ADD INDEX ( `start_date` );
ALTER TABLE `event_contacts` ADD INDEX ( `end_date` );

-- 2011-04-22  gessha sakusei for April

SELECT DISTINCT leave_date FROM contacts 
UPDATE contacts SET leave_date=NULL WHERE leave_date='0000-00-00' ;
UPDATE contacts SET enroll_date=NULL WHERE enroll_date='0000-00-00' ;

SELECT event_contacts.*, contacts.*, sms_fee.* FROM event_contacts, contacts, sms_fee
WHERE sms_fee.contact_id=contacts.id AND sms_fee.event_id=event_contacts.event_id AND contacts.id=event_contacts.contact_id
AND contacts.idnumber IN ('0810120295', '0810106175') AND sms_fee.id IN ('fee_02551', 'fee_02054') 


SELECT contacts.*, sms_fee.* FROM contacts, sms_fee
WHERE sms_fee.contact_id=contacts.id 
AND contacts.idnumber IN ('0810120295', '0810106175') AND sms_fee.id IN ('fee_02551', 'fee_02054') 

-- gakunen update 2011-04-27
SELECT id, last_name, grade, grade_entrance_date, grade_entered, date_entered, date_modified
FROM contacts WHERE smsgroup = 'student' AND deleted =0 AND grade IS NOT NULL AND grade!=''
AND date_entered < '2011-04-01 00:00:00' AND grade!=grade_entered


SELECT id, last_name, grade, grade_entrance_date, grade_entered, date_entered, date_modified
FROM contacts WHERE smsgroup = 'student' AND deleted =0 AND grade IS NOT NULL AND grade!=''
AND date_entered < '2011-04-01 00:00:00' AND grade=grade_entered AND grade !='general'

UPDATE contacts SET grade=grade+1 WHERE smsgroup = 'student' AND deleted =0 AND grade IS NOT NULL AND grade!=''
AND date_entered < '2011-04-01 00:00:00' AND grade=grade_entered AND grade !='general'


SELECT id, last_name, grade, grade_entrance_date, grade_entered, date_entered, date_modified
FROM contacts WHERE smsgroup = 'student' AND deleted =0 AND grade IS NOT NULL AND grade!=''
AND date_entered < '2011-04-01 00:00:00' AND grade=grade_entered AND grade !='general'

UPDATE contacts SET grade='general' WHERE grade =19 ; 

-- 2011-04-28  shinki toiawase kanri excel xml related  fields
ALTER TABLE `event_contacts` ADD `setsumeikai_date1` DATE NULL;
ALTER TABLE `event_contacts` ADD `setsumeikai_tantosha1_1` VARCHAR( 36 ) NULL ,
ADD `setsumeikai_tantosha1_2` VARCHAR( 36 ) NULL;
ALTER TABLE `event_contacts` ADD `setsumeikai_comment1` VARCHAR( 255 ) NULL ;

ALTER TABLE `event_contacts` ADD `kengaku_date1` DATE NULL;
ALTER TABLE `event_contacts` ADD `kengaku_tantosha1_1` VARCHAR( 36 ) NULL ,
ADD `kengaku_tantosha1_2` VARCHAR( 36 ) NULL;
ALTER TABLE `event_contacts` ADD `kengaku_comment1` VARCHAR( 255 ) NULL ;


ALTER TABLE `event_contacts` ADD `kengaku_date2` DATE NULL;
ALTER TABLE `event_contacts` ADD `kengaku_tantosha2_1` VARCHAR( 36 ) NULL ,
ADD `kengaku_tantosha2_2` VARCHAR( 36 ) NULL;
ALTER TABLE `event_contacts` ADD `kengaku_comment2` VARCHAR( 255 ) NULL ;

ALTER TABLE `event_contacts` ADD `kengaku_date3` DATE NULL;
ALTER TABLE `event_contacts` ADD `kengaku_tantosha3_1` VARCHAR( 36 ) NULL ,
ADD `kengaku_tantosha3_2` VARCHAR( 36 ) NULL;
ALTER TABLE `event_contacts` ADD `kengaku_comment3` VARCHAR( 255 ) NULL ;

ALTER TABLE `event_contacts` CHANGE `setsumeikai_date1` `setsumeikai_date1` VARCHAR( 64 ) NULL DEFAULT NULL ,
CHANGE `kengaku_date1` `kengaku_date1` VARCHAR( 64 ) NULL DEFAULT NULL ,
CHANGE `kengaku_date2` `kengaku_date2` VARCHAR( 64 ) NULL DEFAULT NULL ,
CHANGE `kengaku_date3` `kengaku_date3` VARCHAR( 64 ) NULL DEFAULT NULL ;

-- SMSEventContactMemo related updates

CREATE TABLE IF NOT EXISTS `sms_ec_memo` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) DEFAULT NULL,
  `assigned_user_id` varchar(36) DEFAULT NULL,
  `assigned_user_id2` varchar(36) DEFAULT NULL,
  `created_by` varchar(36) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `category` varchar(16) DEFAULT NULL,
  `status` varchar(16) DEFAULT NULL,
  `memodate` date DEFAULT NULL,
  `memodate2` date DEFAULT NULL,
  `value` int(8) DEFAULT NULL,
  `contact_id` varchar(36) DEFAULT NULL,
  `smsgroup` varchar(25) DEFAULT NULL,
  `event_id` varchar(36) DEFAULT NULL,
  `event_type` varchar(25) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  KEY `event_id` (`event_id`),
  KEY `contact_id` (`contact_id`,`event_id`),
  KEY `memodate` (`memodate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `sms_ec_memo` CHANGE `memodate` `memodate2` DATE NULL DEFAULT NULL ;
ALTER TABLE `sms_ec_memo` ADD `memodate3` DATE NULL DEFAULT NULL AFTER `memodate2` ;

--- add a new field to event_contacts table
ALTER TABLE `event_contacts` ADD `leave_date` DATE NOT NULL AFTER `entrance_date`;

-- to fix textbook fees not related course uriage problem
UPDATE sms_transactiontype SET `keiri_code` = '407' WHERE `sms_transactiontype`.`id` = '5' LIMIT 1 ;
UPDATE sms_transactiontype SET `keiri_code` = '407' WHERE `sms_transactiontype`.`id` = '17' LIMIT 1 ;

--  objective table
CREATE TABLE IF NOT EXISTS `sms_objective` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) DEFAULT NULL,
  `group` varchar(32) DEFAULT NULL,
  `category` varchar(64) DEFAULT NULL,
  `subcategory` varchar(64) DEFAULT NULL,
  `objectivedate` date DEFAULT NULL,
  `mokuhyo` int(8) DEFAULT NULL,
  `jisseki` int(8) DEFAULT NULL,
  `target_id` varchar(36) DEFAULT NULL,
  `school_id` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `target_id` (`target_id`),
  KEY `objectivedate` (`objectivedate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `sms_objective` CHANGE `mokuhyo` `mokuhyo` DECIMAL( 20, 2 ) NULL DEFAULT NULL ;
ALTER TABLE `sms_objective` CHANGE `jisseki` `jisseki` DECIMAL( 20, 2 ) NULL DEFAULT NULL ;


-- DROP TABLE IF EXISTS `combini_history`    (if first time, drop the old table)
CREATE TABLE IF NOT EXISTS `combini_history` (
  `id` varchar(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `combiniactions_id` varchar(36) NOT NULL,
  `contact_id` varchar(36) default NULL,
  `data_type` varchar(2) default NULL,
  `t_date` datetime default NULL,
  `salesno` varchar(20) default NULL,
  `due_date` date default NULL,
  `in_amount` varchar(6) default NULL,
  `store_code` varchar(8) default NULL,
  `store_name` varchar(60) default NULL,
  `payment_date` date default NULL,
  `error_code` varchar(20) default NULL,
  `raw_import_data` text,
  `student_id` varchar(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `combiniactions_id` (`combiniactions_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- eigoban course and fees ido
javascript: get_category('614', '01本科', '1b2f032b-e1d8-81ee-46bd-4e689467a126');
javascript: get_course('7fc64b04-d9b5-8390-02f6-4e6894c0caf1');
javascript: get_course('77632c36-e2f1-ab64-021f-4e68940a04e1');
javascript: get_course('b1a1eef1-a6d9-c62a-7049-4e6894718002');
javascript: get_course('ec603b46-568e-f7ec-7842-4e6894e31df7');
IN ('7fc64b04-d9b5-8390-02f6-4e6894c0caf1', '77632c36-e2f1-ab64-021f-4e68940a04e1', 'b1a1eef1-a6d9-c62a-7049-4e6894718002', 'ec603b46-568e-f7ec-7842-4e6894e31df7')
	javascript: get_category('616', '01胎教', '499b1259-9532-ba30-3b90-4e6895a1f96d');
	javascript: get_course('3b55eee4-3367-7ad8-a796-4e689548f8ad');
	IN ('3b55eee4-3367-7ad8-a796-4e689548f8ad')
	javascript: get_category('617', '02親と子の共育大学', '64e42d36-8b51-6c96-5556-4e6895b57c63');
	javascript: get_course('5ad85867-16fa-7867-ea36-4e689636d5c5');
	IN('5ad85867-16fa-7867-ea36-4e689636d5c5')
javascript: get_category('621', '03入室説明会', '5bafe2ca-544f-b6d8-ef32-4e6896883f41');
javascript: get_course('3c6bc540-420e-0420-0545-4e68966d5436');
IN('3c6bc540-420e-0420-0545-4e68966d5436')

javascript: get_category('622', '01本科', 'c2d5a49e-d019-e8ec-a958-4e689715b6ea');
	javascript: get_category('625', '01胎教', '51aa535d-eaec-5c3d-dcf7-4e6897bba160');
	javascript: get_category('626', '02親と子の共育大学', '473e7cd5-795d-1c7e-b4af-4e6897ffd4bd');
javascript: get_category('624', '03入室説明会', 'e9a3481c-3928-db44-1105-4e6897bcad2e');


UPDATE smsevent SET school_id='ba1a2012-eb6e-ee2a-ae6f-4e65ad43749d' WHERE id IN ('7fc64b04-d9b5-8390-02f6-4e6894c0caf1', '77632c36-e2f1-ab64-021f-4e68940a04e1', 'b1a1eef1-a6d9-c62a-7049-4e6894718002', 'ec603b46-568e-f7ec-7842-4e6894e31df7', '3b55eee4-3367-7ad8-a796-4e689548f8ad', '5ad85867-16fa-7867-ea36-4e689636d5c5', '3c6bc540-420e-0420-0545-4e68966d5436')

UPDATE sms_fee SET school_id='ba1a2012-eb6e-ee2a-ae6f-4e65ad43749d' WHERE event_id IN ('7fc64b04-d9b5-8390-02f6-4e6894c0caf1', '77632c36-e2f1-ab64-021f-4e68940a04e1', 'b1a1eef1-a6d9-c62a-7049-4e6894718002', 'ec603b46-568e-f7ec-7842-4e6894e31df7', '3b55eee4-3367-7ad8-a796-4e689548f8ad', '5ad85867-16fa-7867-ea36-4e689636d5c5', '3c6bc540-420e-0420-0545-4e68966d5436')

-- 2011-10-18 ryokin tekiyo problem
SELECT * FROM `sms_fee` WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND contact_id IS NOT NULL AND deleted=0

SELECT * FROM `sms_fee` WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND contact_id IS NOT NULL 
AND deleted=0 AND contact_id!='create_student_schedule_as_well'
SELECT * FROM `sms_transaction` WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND deleted=0

UPDATE `sms_fee` SET deleted=1 WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND contact_id IS NOT NULL 
AND deleted=0 AND contact_id!='create_student_schedule_as_well'

UPDATE `sms_transaction` SET deleted=1 WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND deleted=0

UPDATE `sms_fee` SET deleted=1 WHERE `event_id` LIKE '114a8fad-8430-14c3-a589-4e97d78e33e1' AND contact_id IS NOT NULL 
AND deleted=0 AND contact_id='create_student_schedule_as_well' 

-- grade problem with age0
SELECT id, grade, grade_entered, grade_entrance_date FROM `contacts` WHERE grade_entered = '00'

-- 2012-04-23 fee hinmei update
UPDATE sms_fee SET hinmei=CONCAT( '維持費', basic_fee )
WHERE `name` LIKE '%プレミアム%維持%' AND `deleted` =0 AND hinmei != CONCAT( '維持費', basic_fee )

-- 2012-08-21
ALTER TABLE `contacts` ADD `shiryo_hassobi` DATE NULL DEFAULT NULL AFTER `birthdate` ,
ADD `kengakubi` DATE NULL DEFAULT NULL AFTER `shiryo_hassobi` ,
ADD `nyushitsu_tetsuzukibi` DATE NULL DEFAULT NULL AFTER `kengakubi`;


-- 2013-01-31 bank branch addition
INSERT INTO `bank_info` (`bank_code`, `branch_code`, `katakana`, `name`, `type`, `branch_type`) VALUES
('0001', '004', 'ﾏﾙﾉｳﾁﾁﾕｳｵｳ     ', '丸の内中央', '2', '1');

--2013-10-04 update finish 受講生ステータス変更バッチに伴うパッチ
----contactsのステータス変更
--update contacts set contact_status=7, custom_var17='退室・退室' 
--where leave_date is not null and smsgroup='student' and contact_status!=7 and custom_var17!='退室・退室'
----event_contactsのステータス変更
--update event_contacts set event_contact_status=7
--where leave_date is not null and leave_date!='0000-00-00' and event_contact_status!=7
--and   contact_id in (select id from contacts where smsgroup='student')



-- 2014-04-01 gakunen updates

SELECT id, last_name, date_modified, grade, grade_entrance_date, grade_entered, date_modified, date_entered, contact_status FROM contacts WHERE smsgroup='student' AND grade  IN ("00", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18") 

UPDATE contacts SET grade="general" WHERE smsgroup='student' AND grade ="18"
UPDATE contacts SET grade=grade+1 WHERE smsgroup='student' AND grade  IN ("00", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17") 

-- 2015-12-21 course keisuichiran settings

SELECT DISTINCT custom_var37 FROM `smsevent` WHERE custom_var36='本科' 
SELECT DISTINCT custom_var37 FROM `smsevent` WHERE custom_var36='英語' 
SELECT DISTINCT custom_var37 FROM `smsevent` WHERE custom_var36='パズル道場' 
SELECT DISTINCT custom_var37 FROM `smsevent` WHERE custom_var36='MEP' 
SELECT DISTINCT custom_var37 FROM `smsevent` WHERE custom_var36='PreMEP' 
SELECT DISTINCT custom_var37 FROM `smsevent` WHERE custom_var36='MEP進学' 


SELECT custom_var37, count(id) AS mycount FROM `smsevent` WHERE custom_var36='本科' AND deleted=0 GROUP BY custom_var37
SELECT custom_var37, count(id) AS mycount FROM `smsevent` WHERE custom_var36='英語' AND deleted=0 GROUP BY custom_var37
SELECT custom_var37, count(id) AS mycount FROM `smsevent` WHERE custom_var36='パズル道場' AND deleted=0 GROUP BY custom_var37
SELECT custom_var37, count(id) AS mycount FROM `smsevent` WHERE custom_var36='MEP' AND deleted=0 GROUP BY custom_var37
SELECT custom_var37, count(id) AS mycount FROM `smsevent` WHERE custom_var36='PreMEP' AND deleted=0 GROUP BY custom_var37
SELECT custom_var37, count(id) AS mycount FROM `smsevent` WHERE custom_var36='MEP進学' AND deleted=0 GROUP BY custom_var37

SELECT custom_var36, custom_var37, id FROM smsevent WHERE custom_var36 NOT IN ('', 'なし') AND  custom_var37 IN (
'1DAY PAD①', 
'1DAY PAD②',
'ミニPAD',
'高速実践',
'高速基本',
'中級・上級',
'初級Ad・Ba',
'壱萬会',
'壱萬会調整会',
'人間学',
'PADフォロー',
'木曜特講',
'胎教',
'共育大学①',
'共育大学②',
'共育大学③',
'東洋思想',
'他講座・教材',
'個性学')

SELECT custom_var36, custom_var37, id FROM smsevent WHERE custom_var36 IS NOT NULL AND custom_var36 NOT IN ('', 'なし')  AND custom_var37 IN (
'1DAY PAD①', 
'1DAY PAD②',
'ミニPAD',
'高速実践',
'高速基本',
'中級・上級',
'初級Ad・Ba',
'壱萬会',
'壱萬会調整会',
'人間学',
'PADフォロー',
'木曜特講',
'胎教',
'共育大学①',
'共育大学②',
'共育大学③',
'東洋思想',
'他講座・教材',
'個性学')

