SELECT contacts.school_id, resource.id, resource.school_id FROM contacts, resource WHERE contacts.school_id=resource.school_id
UPDATE contacts, resource SET contacts.school_id=resource.id WHERE contacts.school_id=resource.school_id


SELECT smsevent.school_id, resource.id, resource.school_id FROM smsevent, resource WHERE smsevent.school_id=resource.school_id
UPDATE smsevent, resource SET smsevent.school_id=resource.id WHERE smsevent.school_id=resource.school_id


UPDATE smsevent SET date_entered='2013-05-20 10:00:00', date_modified='2013-05-20 10:00:00', modified_user_id='1', created_by='1' WHERE 1 ;


SELECT * FROM `contacts` WHERE `smsgroup` LIKE 'student'
DELETE FROM `contacts` WHERE `smsgroup` LIKE 'student'

-- 2013-06-12 data ikou check
SELECT contacts.last_name, contacts.first_name, contacts.idnumber, contacts.school_id AS student_school_id, 
	smsevent.name as course_name, smsevent.start_date as course_start_date, smsevent.end_date as course_start_date, smsevent.school_id AS course_school_id, 
	event_contacts.*
	FROM event_contacts
LEFT JOIN contacts ON event_contacts.contact_id=contacts.id AND contacts.deleted=0
LEFT JOIN smsevent ON event_contacts.event_id=smsevent.id AND smsevent.deleted=0

SELECT *, CONCAT('0',contact_id) AS new_contact_id  FROM `event_contacts` WHERE CHAR_LENGTH( contact_id ) =6

UPDATE event_contacts SET contact_id=CONCAT('0',contact_id)WHERE CHAR_LENGTH( contact_id ) =6



SELECT contacts.last_name, contacts.first_name, contacts.idnumber, contacts.school_id AS student_school_id, 
	smsevent.name as course_name, smsevent.start_date as course_start_date, smsevent.end_date as course_start_date, smsevent.school_id AS course_school_id, 
	event_contacts.*
	FROM event_contacts
LEFT JOIN contacts ON event_contacts.contact_id=contacts.id AND contacts.deleted=0
LEFT JOIN smsevent ON event_contacts.event_id=smsevent.id AND smsevent.deleted=0

SELECT distinct event_id 
	FROM event_contacts
LEFT JOIN contacts ON event_contacts.contact_id=contacts.id AND contacts.deleted=0
LEFT JOIN smsevent ON event_contacts.event_id=smsevent.id AND smsevent.deleted=0
WHERE smsevent.name IS NULL

SELECT DISTINCT course_category_option FROM `smsevent`

SELECT course_category_option FROM smsevent WHERE course_category_option=1

UPDATE smsevent SET course_category_option='01' WHERE course_category_option=1 ;
UPDATE smsevent SET course_category_option='02' WHERE course_category_option=2 ;
UPDATE smsevent SET course_category_option='03' WHERE course_category_option=3 ;


CREATE TABLE IF NOT EXISTS `smsevent2` (
  `id` varchar(36) NOT NULL default '',
  `school_id` varchar(36) NOT NULL default 'sssssss-1111',
  `name` varchar(50) NOT NULL default '',
  `nendo` varchar(255) default NULL,
  `zyugyosyubetsu` varchar(255) default NULL,
  `zyugyokeitai` varchar(255) default NULL,
  `grade_dom` varchar(255) default NULL,
  `category` varchar(15) default 'event',
  `start_date` date default NULL,
  `end_date` date default NULL,
  KEY `school_id` (`school_id`),
  KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT `id`,name, `category`,`nendo`,`grade_dom`,`zyugyosyubetsu`,`course_category_option`,`zyugyokeitai`,`start_date`,`end_date`
FROM `smsevent`
WHERE `nendo` LIKE '2012' AND  start_date = '2013-04-01'

UPDATE  `smsevent` SET start_date = '2012-04-01' WHERE `nendo` LIKE '2012' AND  start_date = '2013-04-01'

UPDATE smsevent2 SET school_id='01' WHERE school_id='1';
UPDATE smsevent2 SET school_id='02' WHERE school_id='2';
UPDATE smsevent2 SET school_id='03' WHERE school_id='3';
UPDATE smsevent2 SET school_id='04' WHERE school_id='4';
UPDATE smsevent2 SET school_id='05' WHERE school_id='5';
UPDATE smsevent2 SET school_id='06' WHERE school_id='6';
UPDATE smsevent2 SET school_id='07' WHERE school_id='7';
UPDATE smsevent2 SET school_id='08' WHERE school_id='8';

SELECT smsevent2.school_id, resource.school_id FROM smsevent2, resource WHERE resource.school_id=smsevent2.school_id

UPDATE smsevent2, resource SET smsevent2.school_id=resource.id WHERE resource.school_id=smsevent2.school_id


UPDATE smsevent2 SET grade_dom='sho' WHERE grade_dom='小学生' ; 
UPDATE smsevent2 SET grade_dom='6' WHERE grade_dom='小1' ; 
UPDATE smsevent2 SET grade_dom='7' WHERE grade_dom='小2' ; 
UPDATE smsevent2 SET grade_dom='8' WHERE grade_dom='小3' ; 
UPDATE smsevent2 SET grade_dom='9' WHERE grade_dom='小4' ; 
UPDATE smsevent2 SET grade_dom='10' WHERE grade_dom='小5' ; 
UPDATE smsevent2 SET grade_dom='11' WHERE grade_dom='小6' ; 
UPDATE smsevent2 SET grade_dom='12' WHERE grade_dom='中1' ; 
UPDATE smsevent2 SET grade_dom='13' WHERE grade_dom='中2' ; 
UPDATE smsevent2 SET grade_dom='14' WHERE grade_dom='中3' ; 
UPDATE smsevent2 SET grade_dom='15' WHERE grade_dom='高1' ; 
UPDATE smsevent2 SET grade_dom='16' WHERE grade_dom='高2' ; 
UPDATE smsevent2 SET grade_dom='17' WHERE grade_dom='高3' ; 

SELECT category, SUBSTRING(category, 1, 2) FROM smsevent2 WHERE 1
UPDATE smsevent2 SET category=SUBSTRING(category, 1, 2) WHERE 1

SELECT smsevent2.name, smsevent2.nendo, smsevent2.category, smsevent2.nendo, smsevent2.zyugyosyubetsu, smsevent2.grade_dom, smsevent.id, 
	smsevent.grade_dom, smsevent.category, smsevent.nendo, smsevent.zyugyosyubetsu
FROM smsevent, smsevent2
WHERE smsevent.name=smsevent2.name AND smsevent.nendo=smsevent2.nendo AND smsevent.zyugyosyubetsu=smsevent2.zyugyosyubetsu AND smsevent.course_category_option=smsevent2.category 
	AND smsevent.school_id=smsevent2.school_id AND smsevent.zyugyosyubetsu='通常授業' AND smsevent.category='course'


SELECT smsevent2.name, smsevent2.nendo, smsevent2.category, smsevent2.nendo, smsevent2.zyugyosyubetsu, smsevent2.grade_dom, smsevent.id, 
	smsevent.grade_dom, smsevent.category, smsevent.nendo, smsevent.zyugyosyubetsu
FROM smsevent, smsevent2
WHERE smsevent.name=smsevent2.name AND smsevent.nendo=smsevent2.nendo AND smsevent.zyugyosyubetsu=smsevent2.zyugyosyubetsu AND smsevent.course_category_option=smsevent2.category 
	AND smsevent.school_id=smsevent2.school_id AND smsevent.zyugyosyubetsu='通常授業' AND smsevent.category='course' AND smsevent.grade_dom IS NULL

UPDATE smsevent, smsevent2 SET smsevent.grade_dom = smsevent2.grade_dom 
WHERE smsevent.name=smsevent2.name AND smsevent.nendo=smsevent2.nendo AND smsevent.zyugyosyubetsu=smsevent2.zyugyosyubetsu AND smsevent.course_category_option=smsevent2.category 
	AND smsevent.school_id=smsevent2.school_id AND smsevent.zyugyosyubetsu='通常授業' AND smsevent.category='course' AND smsevent.grade_dom IS NULL
	
	
	
	
SELECT id, last_name, contacts.bank_code, bank_branch_code, bank_name, bank_branch_name
FROM contacts
WHERE smsgroup='student' AND bank_name IS NOT NULL  AND bank_name!='' 
	
	
SELECT id, last_name, contacts.bank_code, bank_branch_code, bank_name, bank_branch_name, 
	bank_info.bank_code, bank_info.branch_code, bank_info.name AS bank_info_name
FROM contacts, bank_info
WHERE smsgroup='student' AND bank_name IS NOT NULL  AND bank_name!='' AND contacts.bank_code=bank_info.bank_code AND bank_info.branch_code='000'

UPDATE contacts, bank_info
SET contacts.bank_name=bank_info.name
WHERE smsgroup='student' AND bank_name IS NOT NULL  AND bank_name!='' AND contacts.bank_code=bank_info.bank_code AND bank_info.branch_code='000'



SELECT id, last_name, contacts.bank_code, bank_branch_code, bank_name, bank_branch_name, 
	bank_info.bank_code, bank_info.branch_code, bank_info.name AS bank_info_name
FROM contacts, bank_info
WHERE smsgroup='student' AND bank_name IS NOT NULL  AND bank_name!='' AND contacts.bank_code=bank_info.bank_code AND bank_info.branch_code=contacts.bank_branch_code


UPDATE contacts, bank_info
SET contacts.bank_branch_name=bank_info.name
WHERE smsgroup='student' AND bank_name IS NOT NULL  AND bank_name!='' AND contacts.bank_code=bank_info.bank_code AND bank_info.branch_code=contacts.bank_branch_code


SELECT * FROM contacts WHERE bank_branch_name IN ('ﾔﾂﾔﾏﾀﾞ', 'ﾌﾈﾋｷ', 'ﾆﾎﾝﾏﾂﾐﾅﾐ', 'ｵｵﾐｶｴｷﾏｴ')


SELECT * FROM bank_info WHERE 
 (bank_code=4069 AND branch_code=027) OR
 (bank_code=0131 AND branch_code=123) OR
 (bank_code=0126 AND branch_code=208) OR
 (bank_code=0514 AND branch_code=111)

INSERT INTO `bank_info` (`bank_code`, `branch_code`, `katakana`, `name`, `type`, `branch_type`, `deleted`, `date_modified`, `modified_user_id`) VALUES
('0126', '208', 'ﾌﾈﾋｷ', '船引支店', '2', '1', 0, '2013-03-20 10:10:00', '1'),
('0131', '123', 'ｵｵﾐｶｴｷﾏｴ', '大みか駅前支店', '2', '1', 0, '2013-03-20 10:10:00', '1'),
('0514', '111', 'ﾔﾂﾔﾏﾀﾞ', '八山田支店', '2', '1', 0, '2013-03-20 10:10:00', '1'),
('4069', '027', 'ﾆﾎﾝﾏﾂﾐﾅﾐ', '二本松南支店', '2', '1', 0, '2013-03-20 10:10:00', '1');




-- 2013-06-12  4,3,13
--UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE `id` = '5' LIMIT 1 ;
--UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE `id` = '28' LIMIT 1 ;
UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE id not in (27,29);

-- 2013-06-12 
UPDATE `resource` SET `area_id` = '郡山本部' WHERE `area_id` = '郡山分本部' AND category='school'; 


-- 2013-06-13
SELECT * FROM `smsevent` WHERE `category` = '' ;
UPDATE smsevent SET category='course' WHERE `category` = '' ;

--2013-06-17 data ikou 
update contacts 
set phone_home_cleaned=replace(phone_home, '-', ''), phone_mobile_cleaned=replace(phone_mobile, '-', ''), 
	phone_work_cleaned=replace(phone_work, '-', ''), phone_other_cleaned=replace(phone_other, '-', ''), phone_fax_cleaned=replace(phone_fax, '-', ''); 

--2013-06-17 data ikou check
SELECT id, contacts.kenritu_ittuki_shiboukou as global_id FROM `contacts` WHERE not exists (select gakkomaster.id from gakkomaster where gakkomaster.id=contacts.kenritu_ittuki_shiboukou) and contacts.kenritu_ittuki_shiboukou is not null and  contacts.kenritu_ittuki_shiboukou <> ''
union distinct SELECT id, contacts.kenritu_shiboukou as global_id FROM `contacts` WHERE not exists (select gakkomaster.id from gakkomaster where gakkomaster.id=contacts.kenritu_shiboukou) and contacts.kenritu_shiboukou is not null and contacts.kenritu_shiboukou <> ''
union distinct SELECT id, contacts.shiritu_shiboukou as global_id FROM `contacts` WHERE not exists (select gakkomaster.id from gakkomaster where gakkomaster.id=contacts.shiritu_shiboukou) and contacts.shiritu_shiboukou is not null and contacts.shiritu_shiboukou <> ''
union distinct SELECT id, contacts.siboudaigaku_1 as global_id FROM `contacts` WHERE not exists (select gakkomaster.id from gakkomaster where gakkomaster.id=contacts.siboudaigaku_1) and contacts.siboudaigaku_1 is not null and contacts.siboudaigaku_1 <> ''
union distinct SELECT id, contacts.siboudaigaku_2 as global_id FROM `contacts` WHERE not exists (select gakkomaster.id from gakkomaster where gakkomaster.id=contacts.siboudaigaku_2) and contacts.siboudaigaku_2 is not null and contacts.siboudaigaku_2 <> ''
union distinct SELECT id, contacts.siboudaigaku_3 as global_id FROM `contacts` WHERE not exists (select gakkomaster.id from gakkomaster where gakkomaster.id=contacts.siboudaigaku_3) and contacts.siboudaigaku_3 is not null and contacts.siboudaigaku_3 <> ''
union distinct SELECT id, contacts.siboudaigaku_4 as global_id FROM `contacts` WHERE not exists (select gakkomaster.id from gakkomaster where gakkomaster.id=contacts.siboudaigaku_4) and contacts.siboudaigaku_4 is not null and contacts.siboudaigaku_4 <> ''
union distinct SELECT id, contacts.siboudaigaku_5 as global_id FROM `contacts` WHERE not exists (select gakkomaster.id from gakkomaster where gakkomaster.id=contacts.siboudaigaku_5) and contacts.siboudaigaku_5 is not null and contacts.siboudaigaku_5 <> ''
ORDER BY global_id;

--テストデータのみだった 2013/06/17 14:42
--INSERT INTO gakkomaster SELECT id, name, category, subcategory, state, city, id, null, '0', now(), now(), '1', '1' FROM gakkoglobal.gakkomaster WHERE id='**上記ID**';

--2013-06-25 コンビニ印刷の見形に伴う料金名の変更
update `sms_fee` set `name` = REPLACE(`name`,'入塾者','新規') where `name` like '%入塾者%';
update `sms_fee` set `name` = REPLACE(`name`,'入塾','新規') where `name` like '%入塾%';
update `sms_fee` set `name` = REPLACE(`name`,'通塾者','継続') where `name` like '%通塾者%';
update `sms_fee` set `name` = REPLACE(`name`,'通塾','継続') where `name` like '%通塾%';


-- 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 ("3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17") 
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 ="17"

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 ("3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17")  AND grade!=grade_entered

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


-- 2015-01-07

SELECT contacts.id, contacts.idnumber, last_name, first_name, resource.area_id, resource.name, sms_fee.salesno, sms_transaction.actual_in_amount, t_date, sms_fee.name
FROM sms_transaction, sms_fee, contacts, resource
WHERE sms_fee.id=sms_transaction.fee_id AND sms_fee.contact_id=contacts.id AND contacts.school_id=resource.id
	AND sms_fee.deleted=0 AND sms_transaction.deleted=0 AND contacts.deleted=0 
	AND sms_transaction.t_date='2014-12-29 00:00:00' AND sms_fee.unit_type='0' AND sms_transaction.payment_type='hikiotoshi'
ORDER BY contacts.idnumber, sms_fee.salesno

-- COPY sms_transaction TO sms_transaction_20150108

UPDATE sms_transaction, sms_fee
SET t_date=NULL, actual_in_amount=0, payment_type=NULL
WHERE sms_fee.id=sms_transaction.fee_id 
	AND sms_fee.deleted=0 AND sms_transaction.deleted=0 
	AND sms_transaction.t_date='2014-12-29 00:00:00' AND sms_fee.unit_type='0' AND sms_transaction.payment_type='hikiotoshi'
	
-- for testing
SELECT contacts.id, contacts.idnumber, last_name, first_name, resource.area_id, resource.name, sms_fee.salesno, sms_transaction_20150107.actual_in_amount, t_date, sms_fee.name
FROM sms_transaction_20150107, sms_fee, contacts, resource
WHERE sms_fee.id=sms_transaction_20150107.fee_id AND sms_fee.contact_id=contacts.id AND contacts.school_id=resource.id
	AND sms_fee.deleted=0 AND sms_transaction_20150107.deleted=0 AND contacts.deleted=0 
	AND sms_transaction_20150107.t_date='2014-12-29 00:00:00' AND sms_fee.unit_type='0' AND sms_transaction_20150107.payment_type='hikiotoshi'
ORDER BY contacts.idnumber, sms_fee.salesno

UPDATE sms_transaction_20150107, sms_fee
SET t_date=NULL, actual_in_amount=0, payment_type=NULL
WHERE sms_fee.id=sms_transaction_20150107.fee_id 
	AND sms_fee.deleted=0 AND sms_transaction_20150107.deleted=0 
	AND sms_transaction_20150107.t_date='2014-12-29 00:00:00' AND sms_fee.unit_type='0' AND sms_transaction_20150107.payment_type='hikiotoshi'