-- 2010-03-19
INSERT INTO `email_templates_category` (`id`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `description`, `deleted`, `template_category_status`) VALUES 
('894fc01b-a32f-7977-a0d1-4ba1ee8ee41c', '2010-03-18 09:12:45', '2010-03-18 09:12:45', '1', '1', 'システム自動配信メール', 'このカテゴリーに入っているのは、システムから自動的に配信されるメールのテンプレートです。\r\n内容を変更することは可能ですが、絶対に削除しないでください。削除すると、メールが配信されなくなります。', 0, 'active');
INSERT INTO `email_templates` (`id`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `published`, `name`, `description`, `subject`, `body`, `body_html`, `deleted`, `from_addr`, `category_id`) VALUES 
('e18ab2e1-7b7d-436b-928a-4ba1ee527774', '2010-03-18 09:11:26', '2010-03-18 09:13:09', '1', '1', 'off', '欠席通知メール', '削除しないでください。削除するとメールが配信されません。', '出席のご確認', '$contact_name様、保護者様\r\n\r\n平素より当学苑をご利用いただき、誠にありがとうございます。\r\n本日、$contact_name様がまだ受講予定になっておりますが、\r\nまだ来校されておりません。\r\n\r\nご確認の程お願い申し上げます。\r\n\r\n\r\nみすず学苑', '$contact_name様、保護者様<br />\r\n<br />\r\n平素より当学苑をご利用いただき、誠にありがとうございます。<br />\r\n本日、$contact_name様がまだ受講予定になっておりますが、<br />\r\nまだ来校されておりません。<br />\r\n<br />\r\nご確認の程お願い申し上げます。<br />\r\n<br />\r\n<br />\r\nみすず学苑', 0, NULL, '894fc01b-a32f-7977-a0d1-4ba1ee8ee41c');

-- 2010-04-08
UPDATE contacts SET  `grade_entrance_date` = '2010-04-01' WHERE `date_modified` = '2010-03-30 10:00:33';

-- 2010-04-12
UPDATE sms_fee SET start_date = DATE_ADD(start_date, INTERVAL 1 DAY) WHERE start_date IS NOT NULL;


-- 2010-04-28
UPDATE sms_fee SET name = REPLACE(name, '(', '（');
UPDATE sms_fee SET name = REPLACE(name, ')', '）');
UPDATE sms_transaction SET description = REPLACE(description,  '(', '（');
UPDATE sms_transaction SET description = REPLACE(description, ')', '）');

UPDATE sms_fee SET name = REPLACE(name, '（3月分）', '（03月分）') WHERE 1;
UPDATE sms_transaction SET description = REPLACE(description,'（3月分）', '（03月分）') WHERE 1;

UPDATE sms_fee SET name = REPLACE(name, '（4月分）', '（04月分）') WHERE 1;
UPDATE sms_transaction SET description = REPLACE(description,'（4月分）', '（04月分）') WHERE 1;

UPDATE sms_fee SET name = REPLACE(name, '（5月分）', '（05月分）') WHERE 1;
UPDATE sms_transaction SET description = REPLACE(description,'（5月分）', '（05月分）') WHERE 1;

UPDATE sms_fee SET name = REPLACE(name, '（6月分）', '（06月分）') WHERE 1;
UPDATE sms_transaction SET description = REPLACE(description,'（6月分）', '（06月分）') WHERE 1;

UPDATE sms_fee SET name = REPLACE(name, '（7月分）', '（07月分）') WHERE 1;
UPDATE sms_transaction SET description = REPLACE(description,'（7月分）', '（07月分）') WHERE 1;

UPDATE sms_fee SET name = REPLACE(name, '（8月分）', '（08月分）') WHERE 1;
UPDATE sms_transaction SET description = REPLACE(description,'（8月分）', '（08月分）') WHERE 1;

UPDATE sms_fee SET name = REPLACE(name, '（9月分）', '（09月分）') WHERE 1;
UPDATE sms_transaction SET description = REPLACE(description,'（9月分）', '（09月分）') WHERE 1;


-- 2010-05-06
SELECT * FROM contacts WHERE deleted = 0 AND idnumber IN (
'0700004',
'1063013',
'1063009',
'1062989',
'1062988',
'1063023',
'1062983',
'0700003',
'1062992',
'1063010',
'1063005',
'1063039',
'1063002',
'1063040',
'1062993',
'1062914',
'1063025',
'1062985',
'1062987',
'1062991',
'1062994',
'1062980',
'1062986',
'1063011',
'1063003',
'1063037'
);

-- 2010-05-10
UPDATE `contacts_import`, contacts SET contacts.last_name_furigana = contacts_import.last_name, contacts.first_name_furigana = contacts_import.first_name 
WHERE contacts_import.idnumber = contacts.idnumber;

-- 2010-05-11
UPDATE smsevent_instances SET event_type = 'course' WHERE category = 'teacher' AND event_type = 'NULL';

-- 2010-05-18
UPDATE sms_fee SET start_date = '2010-11-02', end_date = '2010-11-30' 
WHERE start_date = '2010-04-02' AND end_date='2010-04-30' AND fee_subcategory = 'tuition_g_witout_transportaion';

UPDATE sms_fee SET start_date = '2010-11-02', end_date = '2010-11-30' 
WHERE start_date = '2010-04-02' AND end_date='2010-04-30' AND fee_subcategory = 'tuition_g_discount';

-- 2010-05-20
UPDATE sms_fee SET name = '卒業生　年間模試代（前期分）' WHERE name = '卒業生　年間模試代（03分月）';
UPDATE sms_fee SET name = '卒業生　年間模試代（後期分）' WHERE name = '卒業生　年間模試代（09月分）';
UPDATE sms_fee SET name = '卒業生　諸経費（前期分）' WHERE name = '卒業生　諸経費（03月分）';
UPDATE sms_fee SET name = '卒業生　諸経費（後期分）' WHERE name = '卒業生　諸経費（09月分）';

UPDATE sms_fee SET name = '卒業生　交通費補助（前期分）' WHERE name = '卒業生　交通費補助' AND MONTH(start_date) = '7';
UPDATE sms_fee SET name = '卒業生　交通費補助（後期分）' WHERE name = '卒業生　交通費補助' AND MONTH(start_date) = '11';

-- 2010-05-26
UPDATE sms_fee SET name = '卒業生　年間模試代(前期分)' WHERE name = '卒業生　年間模試代（03月分）';

-- 2010-05-31
UPDATE contacts, teacher SET last_name_furigana = teacher.last_name,first_name_furigana = teacher.first_name
WHERE teacher.idnumber = contacts.idnumber AND contacts.smsgroup = 'teacher';

-- 2010-07-08
UPDATE sms_fee SET start_date = DATE_ADD(start_date, INTERVAL -1 DAY)  WHERE `start_date` LIKE '%02';


-- misuzu email transfer

SELECT contacts.idnumber, contacts.email1, contacts.email2, contacts.custom_var1, date_modified, contacts.idnumber, c2.email1, c2.email2 
FROM contacts, contact_newemails c2 
WHERE contacts.idnumber = c2.id AND contacts.deleted=0
	AND contacts.date_modified < '2010-08-03' 

SELECT * FROM `contacts` WHERE `smsgroup` LIKE 'student' AND deleted=0;


UPDATE `contacts` SET custom_var11='notchanged' WHERE smsgroup = 'student';

UPDATE contacts, contact_newemails c2 SET contacts.email1=c2.email1, contacts.email2=c2.email2,  custom_var11 = 'updated'
WHERE contacts.idnumber = c2.id AND contacts.date_modified < '2010-08-03'  AND contacts.deleted=0 AND smsgroup = 'student' ;

UPDATE contacts, contact_newemails c2 SET  custom_var11 = 'conflict'
WHERE contacts.idnumber = c2.id AND contacts.date_modified >= '2010-08-03'  AND contacts.deleted=0 AND smsgroup = 'student' ;

SELECT contacts.idnumber, contacts.email1, contacts.email2, c2.email1 as import_email1, c2.email2 as import_email2, date_modified
FROM contacts, contact_newemails c2 WHERE contacts.idnumber = c2.id AND contacts.deleted=0 AND  custom_var11 = 'conflict' ;

-- email address problematic students
SELECT idnumber, last_name, first_name, resource.name as school_name, email1, email2 FROM `contacts`
JOIN resource ON contacts.school_id = resource.id 
WHERE contacts.deleted =0 AND 
(`email1` LIKE '%..%' OR `email2` LIKE '%..%' OR `email1` LIKE '-%' OR `email2` LIKE '-%' OR `email1` LIKE '.%' OR `email2` LIKE '.%' 
  OR `email1` LIKE '%@%/%' OR `email2` LIKE '%@%/%' 
)

-- fee package sell_date update from 02 to 01
-- SELECT start_date, CONCAT(SUBSTRING(start_date, 1, 9), '1') as newd, SUBSTRING(start_date, 1, 9) s1, SUBSTRING(start_date, 10, 1) s2 FROM `sms_fee` WHERE `contact_id` IS NULL AND deleted=0 ;

UPDATE sms_fee SET start_date = CONCAT(SUBSTRING(start_date, 1, 9), '1')  WHERE `contact_id` IS NULL AND deleted=0 ;

-- update imported teacher schedule status
SELECT  instance_contact_status, contact_status FROM `smsevent_instances`
JOIN contacts ON contacts.id=smsevent_instances.target_id AND contacts.smsgroup='teacher'
WHERE category='teacher' and instance_contact_status!=contact_status ;

SELECT  instance_contact_status, contact_status FROM smsevent_instances, contacts
WHERE contacts.id=smsevent_instances.target_id AND contacts.smsgroup='teacher' AND category='teacher' and instance_contact_status!=contact_status ;

-- UPDATE smsevent_instances, contacts SET instance_contact_status=contact_status
-- WHERE contacts.id=smsevent_instances.target_id AND contacts.smsgroup='teacher' AND category='teacher' and instance_contact_status!=contact_status ;

-- UPDATE event_type in smsevent_instances
-- SELECT  smsevent.category, instance_contact_status, event_type FROM smsevent_instances, smsevent
-- WHERE smsevent.id=smsevent_instances.event_id AND smsevent_instances.category='teacher' and smsevent_instances.event_type = 'NULL';
	
-- UPDATE smsevent_instances, smsevent SET smsevent_instances.event_type=smsevent.category
-- WHERE smsevent.id=smsevent_instances.event_id AND smsevent_instances.category='teacher' and smsevent_instances.event_type = 'NULL';

-- delete fees of the school tachikawa 2010.11.09

DELETE FROM sms_salesgroup WHERE deleted=1 ; DELETE FROM sms_fee WHERE deleted=1 ; DELETE FROM sms_transaction WHERE deleted=1 ;

SELECT * FROM sms_transaction LEFT JOIN sms_fee ON sms_fee.id=sms_transaction.fee_id WHERE school_id='157d198b-2416-0e1e-5dc3-4b343e7e1461' 
SELECT * FROM sms_salesgroup LEFT JOIN sms_fee ON sms_fee.salesno=sms_salesgroup.salesno WHERE school_id='157d198b-2416-0e1e-5dc3-4b343e7e1461' 
SELECT * FROM sms_fee WHERE school_id='157d198b-2416-0e1e-5dc3-4b343e7e1461' 

UPDATE sms_transaction, sms_fee SET sms_transaction.deleted=1 
WHERE sms_fee.id=sms_transaction.fee_id AND school_id='157d198b-2416-0e1e-5dc3-4b343e7e1461' ;

UPDATE sms_salesgroup, sms_fee SET sms_salesgroup.deleted=1
WHERE sms_fee.salesno=sms_salesgroup.salesno AND school_id='157d198b-2416-0e1e-5dc3-4b343e7e1461' ;

UPDATE sms_fee SET deleted=1 WHERE school_id='157d198b-2416-0e1e-5dc3-4b343e7e1461' ;

-- 2010-11-11 fee import work on the temporary sms_import file  430 rows (exluding one row test campus data)
-- fill in school_id
SELECT school_name, sms_import.school_id, name, resource.id FROM sms_import, resource
WHERE SUBSTRING(school_name,1,2) = SUBSTRING(name,1,2) AND resource.category='school' ;

UPDATE sms_import, resource SET sms_import.school_id=resource.id 
WHERE SUBSTRING(school_name,1,2) = SUBSTRING(name,1,2) AND resource.category='school'

-- fill in contact_id
SELECT contacts.id, contacts.idnumber, CONCAT(last_name, ' ', first_name) as student_name, sms_import.idnumber, sms_import.contact_name
FROM sms_import, contacts WHERE sms_import.idnumber= contacts.idnumber AND contacts.deleted=0 ;  -- 411 matches

-- updated 15 (?) missing leading zeros, wrong ID number (1061303->1000023, 1063072->223072, 1063649->0800065) and one non-existing record (1063078)

UPDATE sms_import, contacts SET sms_import.contact_id=contacts.id WHERE sms_import.idnumber= contacts.idnumber AND contacts.deleted=0

-- 9900000033 salesno for the templates
-- DELETE FROM sms_transaction WHERE salesno!='9900000033' ;
-- DELETE FROM sms_salesgroup WHERE salesno!='9900000033' ;
-- DELETE FROM sms_fee WHERE salesno!='9900000033' ;

--  schedule problem 2010.12.15

SELECT DISTINCT event_id, smsevent.name, smsevent.school_id, resource.name AS school_name
FROM event_contacts, smsevent, resource
WHERE smsevent.id = event_contacts.event_id AND smsevent.school_id = resource.id
	AND start_date > '2010-11-01 00:00:00'
	AND SUBSTRING( start_date, 1, 10 ) = SUBSTRING( end_date, 1, 10 )
	AND smsevent.deleted =0 
ORDER BY resource.name

SELECT FROM 

SELECT DISTINCT event_id, smsevent.name, smsevent.school_id, resource.name AS school_name
FROM event_contacts, smsevent, resource
WHERE smsevent.id = event_contacts.event_id AND smsevent.school_id = resource.id
	AND start_date > '2010-11-01 00:00:00'
	AND SUBSTRING( start_date, 1, 10 ) = SUBSTRING( end_date, 1, 10 )
	AND smsevent.deleted =0 
ORDER BY resource.name


SELECT st.event_id, smsevent.name, smsevent.school_id, st.category, st.from_datetime, st.to_datetime, ss.event_id, ss.category, ss.from_datetime, ss.to_datetime
FROM smsevent_instances st
LEFT JOIN smsevent_instances ss ON st.instance_id = ss.instance_id AND ss.category='student'
LEFT JOIN smsevent ON st.event_id=smsevent.id AND st.event_id=smsevent.id 
WHERE st.from_datetime > '2010-11-01 00:00:00' AND st.category='teacher' 
AND st.to_datetime < '2010-12-12 00:00:00'


SELECT si.parent_id, smsevent.name, smsevent.school_id, si.from_datetime, ss.to_datetime, ss.event_id
FROM sms_event_instance si
LEFT JOIN smsevent_instances ss ON si.id = ss.instance_id 
LEFT JOIN smsevent ON si.parent_id=smsevent.id
WHERE si.from_datetime > '2010-11-01 00:00:00' AND si.to_datetime < '2010-12-13 00:00:00'

--  2011.02.17  Chibaeki branch transaction problem
SELECT sms_fee.basic_fee, sms_transaction.in_amount, sms_fee.id
FROM sms_fee, sms_transaction
WHERE school_id='1e815a81-badd-ce69-5e94-4b343f88a265' AND sms_transaction.deleted=0 AND sms_fee.deleted=0
	AND sms_transaction.fee_id=sms_fee.id AND sms_fee.contact_id IS NOT NULL
	AND sms_fee.sell_date>='2011-02-01' AND sms_fee.sell_date<='2011-02-28'
	AND sms_fee.basic_fee!=sms_transaction.in_amount
	AND sms_fee.id NOT IN ('b9ecd647-dde8-92ec-d807-4d4fa4a6637c', '4e9fea6c-90e5-bd5c-b55f-4d4fa4db1d13')
	AND sms_fee.basic_fee*2=sms_transaction.in_amount ;
	
UPDATE sms_fee, sms_transaction
SET sms_transaction.in_amount=sms_fee.basic_fee
WHERE school_id='1e815a81-badd-ce69-5e94-4b343f88a265' AND sms_transaction.deleted=0 AND sms_fee.deleted=0
	AND sms_transaction.fee_id=sms_fee.id AND sms_fee.contact_id IS NOT NULL
	AND sms_fee.sell_date>='2011-02-01' AND sms_fee.sell_date<='2011-02-28'
	AND sms_fee.basic_fee!=sms_transaction.in_amount
	AND sms_fee.id NOT IN ('b9ecd647-dde8-92ec-d807-4d4fa4a6637c', '4e9fea6c-90e5-bd5c-b55f-4d4fa4db1d13')
	AND sms_fee.basic_fee*2=sms_transaction.in_amount ;

-- 2011-03-02
ALTER TABLE `contacts` ADD `toiawase_email_date` DATE NOT NULL DEFAULT '2011-03-01' ; 

-- 2011-03-09
SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, custom_var15, contact_status FROM `contacts` WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')

AND date_entered!=date_modified 
AND grade IS NULL AND grade_entered IS NULL


SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, custom_var15, contact_status FROM `contacts` WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade IS NOT NULL AND grade_entered IS NULL


SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, custom_var15, contact_status FROM `contacts` WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade IS NOT NULL AND grade_entered IS NULL

-1: 76 persons
SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, custom_var15, contact_status FROM `contacts` WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade IS NOT NULL AND grade_entered IS NOT NULL
AND date_entered=date_modified  

-2: 273 persons
SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, custom_var15, contact_status FROM `contacts` WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade IS NOT NULL AND grade_entered IS NULL
AND date_entered=date_modified

-3
UPDATE contacts SET grade_entered=grade WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade IS NOT NULL AND grade_entered IS NULL
AND date_entered=date_modified

-4 
UPDATE contacts SET grade=grade+1 WHERE smsgroup='student' AND grade IS NOT NULL AND grade!='' 
AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken', '18') 
AND date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade IS NOT NULL AND grade_entered IS NOT NULL
AND date_entered=date_modified

-5
UPDATE contacts SET grade_entered=grade WHERE smsgroup='student' AND grade IS NOT NULL AND grade!='' 
AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') 
AND date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade IS NOT NULL AND grade_entered IS NOT NULL
AND date_entered=date_modified

-6
UPDATE contacts SET grade='general1' WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' 
AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade=18 AND grade_entered IS NOT NULL
AND date_entered=date_modified

-7 
SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, custom_var15, contact_status FROM `contacts` WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND date_entered!=date_modified  
AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') 

-8 
UPDATE contacts SET grade=grade+1, grade_entered=grade+1
 WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND date_entered!=date_modified  
AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') 
-9
UPDATE contacts SET grade_entered=grade
 WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND date_entered!=date_modified  
AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') 
-10
UPDATE contacts SET grade='general1' 
WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND date_entered!=date_modified  
AND grade=18 AND grade_entered IS NOT NULL
-11
UPDATE contacts SET grade_entered=grade
 WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND date_entered!=date_modified  AND grade IS NOT NULL AND grade_entered IS NOT NULL

-13
SELECT id, email1, last_name, first_name, school_id, date_entered, date_modified, grade, grade_entered, grade_entrance_date, custom_var15, contact_status FROM `contacts` WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND date_entered!=date_modified  AND grade IS NULL

-14  -> manual update for early March 1st applications
SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, custom_var15, contact_status FROM `contacts` WHERE date_entered<='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL 
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade IS NOT NULL AND grade_entrance_date ='2011-03-01'

-15
SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, custom_var15, contact_status FROM `contacts` WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade='general1'
AND date_entered!=date_modified

UPDATE contacts SET grade_entered=grade
WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL AND grade_entrance_date IS NOT NULL
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade='general1' 
AND date_entered!=date_modified

-16
SELECT grade, grade_entered, emailsemail_cstm.to_addrs_ids, emailsemail_cstm.description FROM emails, emailsemail_cstm, contacts
WHERE emails.id=emailsemail_cstm.id_c AND emails.date_entered>='2011-03-01 00:00:00' 
AND emails.name='【みすず学苑】へのお問い合わせありがとうこざいます'
AND emailsemail_cstm.description LIKE '%平成23年4月からの学年
大検%'
AND contacts.id=SUBSTRING(emailsemail_cstm.to_addrs_ids, 1, 36)

SELECT grade, grade_entered, emailsemail_cstm.to_addrs_ids, emailsemail_cstm.description FROM emails, emailsemail_cstm, contacts
WHERE emails.id=emailsemail_cstm.id_c AND emails.date_entered>='2011-03-01 00:00:00' 
AND emails.name='【みすず学苑】へのお問い合わせありがとうこざいます'
AND emailsemail_cstm.description LIKE '%平成23年4月からの学年
その他%'
AND contacts.id=SUBSTRING(emailsemail_cstm.to_addrs_ids, 1, 36)

- 17
SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, SUBSTRING(date_entered, 1, 10) , custom_var15, contact_status FROM `contacts` WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL 
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade_entrance_date IS NULL 

SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, SUBSTRING(date_entered, 1, 10) , custom_var15, contact_status FROM `contacts` WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL 
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade_entrance_date IS NULL AND grade_entered IS NULL AND grade IS NOT NULL
AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') 

UPDATE `contacts` SET grade=grade+1 WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL 
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade_entrance_date IS NULL AND grade_entered IS NULL AND grade IS NOT NULL
AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') 


UPDATE `contacts` SET grade='general1' WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL 
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade_entrance_date IS NULL AND grade_entered IS NULL AND grade IS NOT NULL
AND grade=18

UPDATE `contacts` SET grade_entered=grade, grade_entrance_date=SUBSTRING(date_entered, 1, 10) 
WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL 
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade_entrance_date IS NULL AND grade_entered IS NULL AND grade IS NOT NULL

-18
UPDATE `contacts` SET grade_entered=grade, grade_entrance_date=SUBSTRING(date_entered, 1, 10) 
WHERE date_entered<='2011-03-09 01:52:06' AND date_entered>='2011-03-00 00:00:00' AND custom_var15 IS NOT NULL 
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade_entrance_date IS NULL 

-19
SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, SUBSTRING(date_entered, 1, 10) , custom_var15, contact_status 
FROM `contacts` WHERE custom_var15 IS NOT NULL 
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade_entered IS NOT NULL AND grade IS NOT NULL AND grade_entrance_date IS NULL 

-20
UPDATE `contacts` SET grade_entrance_date=SUBSTRING(date_entered, 1, 10) 
WHERE custom_var15 IS NOT NULL 
AND custom_var15 IN ('web_shiryo', 'web_taiken', 'web_contact', 'mobile_toiawase', 'shudo_toiawase')
AND grade_entered IS NOT NULL AND grade IS NOT NULL AND grade_entrance_date IS NULL 

-- 20110324 status change according to grade
SELECT id, last_name, first_name, school_id, idnumber, grade, grade_entered, grade_entrance_date, contact_status FROM contacts
WHERE deleted =0 AND grade_entered =17 AND grade_entrance_date < '2010-12-31' AND contact_status =0 
UPDATE contacts SET contact_status=7, leave_date='2011-03-31'
WHERE deleted =0 AND grade_entered =17 AND grade_entrance_date < '2010-12-31' AND contact_status =0

SELECT id, last_name, first_name, school_id, idnumber, grade, grade_entered, grade_entrance_date, contact_status FROM contacts
WHERE deleted =0 AND grade_entered ='general1' AND grade_entrance_date < '2011-01-31' AND contact_status =0 
AND idnumber NOT IN ('0700044', '0600126', '0100116')
UPDATE contacts SET contact_status=7, leave_date='2011-03-31'
WHERE deleted =0 AND grade_entered ='general1' AND grade_entrance_date < '2011-01-31' AND contact_status =0 
AND idnumber NOT IN ('0700044', '0600126', '0100116')

-- 20110324 revocating accidentally deleted fees and transactions

SELECT sms_salesgroup.salesno, contacts.last_name, first_name, idnumber FROM sms_salesgroup, contacts 
WHERE contacts.deleted=0 AND contacts.id=sms_salesgroup.contact_id 
	AND salesno IN ('0500000130', '0600000155', '0800000102', '0500000212', '0100000241', '0200000012', '0200001028', '0700000154', 
		'0200000036', '0200000274', '0200000175')
	
0500000130 	清水 	彩華 	0500123
0600000155 	荻野 	真平 	1062514
0800000102 	山崎 	花織 	1063504
0500000212 	田邉 	寛人 	0500134
0100000241 	青木 	えみ乃 	0100143
0200000012 	園田 	友美 	0200122			-> （これは一部の入出金データのみ消えています）
0200001028 	樋口 	柾生 	0200117			-> （これは一部の入出金データのみ消えています）
0700000154 	大河戸 	悠香 	0700050		-> （これは一部の入出金データのみ消えています）
0200000036 	河野 	紗英 	0200141
0200000274 	高地 	皓治 	1061616
0200000175 	佐藤 	莉花 	0200026

SELECT sms_salesgroup.salesno, contacts.last_name, first_name, idnumber FROM sms_salesgroup, contacts 
WHERE contacts.deleted=0 AND contacts.id=sms_salesgroup.contact_id 
	AND salesno IN ('0500000130', '0600000155', '0800000102', '0500000212', '0100000241', '0200000036', '0200000274', '0200000175')
	
SELECT * FROM sms_salesgroup 
WHERE salesno IN ('0500000130', '0600000155', '0800000102', '0500000212', '0100000241', '0200000036', '0200000274', '0200000175')
ORDER BY salesno
SELECT * FROM sms_fee 
WHERE salesno IN ('0500000130', '0600000155', '0800000102', '0500000212', '0100000241', '0200000036', '0200000274', '0200000175')
ORDER BY salesno, name
SELECT * FROM sms_transaction 
WHERE salesno IN ('0500000130', '0600000155', '0800000102', '0500000212', '0100000241', '0200000036', '0200000274', '0200000175')
ORDER BY salesno, description

-- toiawase on 2001-04-01 before form change
SELECT contacts.grade, contacts.grade_entered, contacts.grade_entrance_date, contacts . *
FROM `contacts` WHERE `date_entered` >= '2011-03-31 15:00:00' AND `smsgroup` LIKE 'student' AND portal_app IS NOT NULL


SELECT sms_transaction.id, fee_id, sms_transaction.salesno, payment_type, actual_in_amount, sms_fee.name, sms_fee.start_date, 
	sms_transaction.t_date, sms_transaction.contact_id 
FROM sms_transaction, sms_fee 
WHERE sms_transaction.fee_id=sms_fee.id AND actual_in_amount !=0 AND sms_transaction.payment_type IS NULL 
	AND sms_transaction.deleted =0 AND sms_fee.deleted=0
ORDER BY sms_transaction.contact_id, sms_transaction.salesno


SELECT DISTINCT sms_transaction.salesno FROM sms_transaction, sms_salesgroup WHERE sms_transaction.salesno IS NOT NULL AND fee_id IS NULL AND sms_transaction.salesno=sms_salesgroup.salesno AND sms_salesgroup.deleted=1 AND actual_in_amount!=0


SELECT * FROM sms_salesgroup WHERE salesno IN ('0500000337', '0500000316') ORDER BY salesno ;
SELECT * FROM sms_fee WHERE salesno IN ('0500000337', '0500000316') ORDER BY salesno, name ;
SELECT * FROM sms_transaction WHERE salesno IN ('0500000337', '0500000316') ORDER BY salesno, description ;

-- fee subcategory spell miss
UPDATE sms_fee SET fee_subcategory="extra_charge_j_without_entrance " WHERE `fee_subcategory` LIKE 'extra_charge_ｊ_without_entrance ' ;
UPDATE sms_fee SET fee_subcategory="return_j_ " WHERE `fee_subcategory` LIKE 'return_ｊ_ ' ;
UPDATE sms_fee SET fee_subcategory="leave_j_commission " WHERE `fee_subcategory` LIKE 'leave_ｊ_commission ' ;
UPDATE sms_fee SET fee_subcategory="subject_j_delete " WHERE `fee_subcategory` LIKE 'subject_ｊ_delete ' ;


-- deleted fees & transactions again
SELECT * FROM sms_salesgroup WHERE salesno IN ('0500000337', '0500000316', '0200000295', '0500000343', '0600000141') 
ORDER BY salesno ;
SELECT * FROM sms_fee WHERE salesno IN ('0500000337', '0500000316', '0200000295', '0500000343', '0600000141') 
ORDER BY salesno, name ;
SELECT * FROM sms_transaction WHERE salesno IN ('0500000337', '0500000316', '0200000295', '0500000343', '0600000141') 
ORDER BY salesno, description ;

-- 
SELECT last_name, first_name, idnumber, salesno, contact_id
SUBSTRING(t_date, 1, 10) as t_date, SUM(actual_in_amount) as total FROM sms_transaction, contacts, resource
WHERE payment_type='combini' AND t_date > '2011-02-27' AND t_date <'2011-03-31' 
AND sms_transaction.contact_id=contacts.id AND resource.id=contacts.school_id 
GROUP BY t_date, salesno


SELECT last_name, first_name, idnumber, contact_id, salesno, SUM(actual_in_amount) as total, SUBSTRING(t_date, 1, 10) as t_date
FROM sms_transaction, contacts
WHERE payment_type='combini' AND t_date > '2011-02-28' AND t_date <'2011-03-31' 
	AND sms_transaction.contact_id=contacts.id AND sms_transaction.deleted=0
GROUP BY salesno
ORDER BY salesno

SELECT last_name, first_name, idnumber, salesno, SUM(in_amount) as total, contact_id
FROM combini_history
LEFT JOIN contacts ON contact_id=contacts.id 
WHERE combini_history.date_entered > '2011-03-01' AND combini_history.date_entered <'2011-04-01' 
GROUP BY salesno
ORDER BY salesno


SELECT last_name, first_name, idnumber, salesno, SUM(in_amount) as total, contact_id
FROM combini_history
LEFT JOIN contacts ON contact_id=contacts.id 
WHERE combini_history.date_entered > '2011-03-01' AND combini_history.date_entered <'2011-04-01' AND error_code='no_error'
GROUP BY salesno
ORDER BY salesno


SELECT last_name, first_name, idnumber, salesno, SUM(in_amount) as total, contact_id
FROM combini_history
LEFT JOIN contacts ON contact_id=contacts.id 
WHERE combini_history.date_entered > '2011-03-01' AND combini_history.date_entered <'2011-04-01' AND error_code!='no_error'
GROUP BY salesno
ORDER BY salesno

-- 2011-04-12 DB updates
SELECT * FROM `sms_transaction` WHERE `deleted` =0 AND `salesno` LIKE '0500000458'

UPDATE sms_transaction SET t_date='0000-00-00 00:00:00' WHERE `deleted` =0 AND `salesno` LIKE '0500000458'

-- 2011-04-14 transaction updates for kamoku_torikeshi
SELECT * FROM sms_transaction WHERE payment_type='kamoku_torikeshi' AND deleted=0 AND salesno = '0800000037' ; 

-- test site
SELECT sms_fee.id, sms_transaction.fee_id, sms_transaction.id, sms_fee.name, in_amount, basic_fee
FROM sms_transaction
LEFT JOIN sms_fee ON sms_fee.salesno=sms_transaction.salesno AND basic_fee < 0
WHERE payment_type='kamoku_torikeshi' AND sms_transaction.deleted=0 AND sms_fee.deleted=0 AND in_amount < 0 
	AND sms_fee.name=sms_transaction.description
	AND sms_fee.salesno = '0800000037' AND sms_transaction.salesno = '0800000037' 
	

SELECT sms_fee.id, sms_transaction.fee_id, sms_transaction.id, sms_fee.name, in_amount, basic_fee
FROM sms_transaction, sms_fee
WHERE payment_type='kamoku_torikeshi' AND sms_transaction.deleted=0 AND sms_fee.deleted=0 AND in_amount < 0 
	AND sms_fee.name=sms_transaction.description
	AND sms_fee.salesno=sms_transaction.salesno AND basic_fee < 0
	AND sms_fee.salesno = '0800000037' AND sms_transaction.salesno = '0800000037' 
	
UPDATE sms_transaction, sms_fee
SET sms_transaction.fee_id = sms_fee.id
WHERE payment_type='kamoku_torikeshi' AND sms_transaction.deleted=0 AND sms_fee.deleted=0 AND in_amount < 0 
	AND sms_fee.name=sms_transaction.description
	AND sms_fee.salesno=sms_transaction.salesno AND basic_fee < 0
	AND sms_fee.salesno = '0800000037' AND sms_transaction.salesno = '0800000037' 
	

-- honban
SELECT sms_fee.id, sms_transaction.fee_id, sms_transaction.id, sms_fee.name, in_amount, basic_fee
FROM sms_transaction, sms_fee
WHERE payment_type='kamoku_torikeshi' AND sms_transaction.deleted=0 AND sms_fee.deleted=0 AND in_amount < 0 
	AND sms_fee.name=sms_transaction.description
	AND sms_fee.salesno=sms_transaction.salesno AND basic_fee < 0
	AND sms_fee.salesno = '0200000383' AND sms_transaction.salesno = '0200000383' 
	
UPDATE sms_transaction, sms_fee
SET sms_transaction.fee_id = sms_fee.id
WHERE payment_type='kamoku_torikeshi' AND sms_transaction.deleted=0 AND sms_fee.deleted=0 AND in_amount < 0 
	AND sms_fee.name=sms_transaction.description
	AND sms_fee.salesno=sms_transaction.salesno AND basic_fee < 0
	AND sms_fee.salesno = '0200000383' AND sms_transaction.salesno = '0200000383' 
	

-- 2011-05-06 tsuki no shime

SELECT last_name, first_name, idnumber, contact_id, salesno, SUM(actual_in_amount) as total, SUBSTRING(t_date, 1, 10) as t_date
FROM sms_transaction, contacts
WHERE payment_type='combini' AND t_date > '2011-03-31' AND t_date <'2011-04-30' 
	AND sms_transaction.contact_id=contacts.id AND sms_transaction.deleted=0
GROUP BY salesno
ORDER BY salesno



SELECT last_name, first_name, idnumber, contact_id, salesno, actual_in_amount, SUBSTRING(t_date, 1, 10) as t_date
FROM sms_transaction, contacts
WHERE payment_type='combini' AND t_date >= '2011-03-31' AND t_date <'2011-04-30' 
	AND sms_transaction.contact_id=contacts.id AND sms_transaction.deleted=0
	

-- updating some transactions
SELECT * FROM `sms_transaction`
WHERE `contact_id` LIKE '39ae4a2a-4da9-2eb7-99bb-4c4f882f90f9' AND `salesno` LIKE '0100000234' AND deleted=0
AND t_date!='0000-00-00 00:00:00' AND in_amount!=0 AND actual_in_amount=0

SELECT * FROM `sms_transaction`
WHERE `contact_id` LIKE '39ae4a2a-4da9-2eb7-99bb-4c4f882f90f9' AND `salesno` LIKE '0100000234' AND deleted=0
AND t_date!='0000-00-00 00:00:00' AND actual_in_amount=0 AND due_date>'2011-05-28 01:00:00'

UPDATE sms_transaction SET t_date='0000-00-00 00:00:00', payment_type=NULL
WHERE `contact_id` LIKE '39ae4a2a-4da9-2eb7-99bb-4c4f882f90f9' AND `salesno` LIKE '0100000234' AND deleted=0
AND t_date!='0000-00-00 00:00:00' AND actual_in_amount=0 AND due_date>'2011-05-28 01:00:00'

SELECT * FROM `sms_transaction`
WHERE `contact_id` LIKE '39ae4a2a-4da9-2eb7-99bb-4c4f882f90f9' AND `salesno` LIKE '0100000234' AND deleted=0
AND t_date='0000-00-00 00:00:00' AND actual_in_amount=0 AND due_date>'2011-05-28 01:00:00' AND payment_type IS NOT NULL

-- 2011-05-27
SELECT * FROM `sms_transaction` WHERE salesno LIKE '0700000614' ;
UPDATE sms_transaction SET t_date='0000-00-00 00:00:00' WHERE `salesno` LIKE '0700000614' ;

-- deleted transaction salesno:
SELECT * FROM `sms_fee` WHERE `deleted` =1 AND `salesno` LIKE '0100000376' 
ORDER BY `sms_fee`.`name` ASC , discount_price ASC

SELECT * FROM `sms_transaction` WHERE `deleted` =1 AND `salesno` LIKE '0100000376' 
ORDER BY `sms_transaction`.`description` ASC , in_amount ASC


T1 45f2e85f-8742-6429-3b81-4dde2b5d3237
T2 20e1d293-6ac8-e5ca-1a3a-4da03c30bbbe

F1 295d4ee8-b6a9-5669-ca37-4dde2b6ab43f
F2 4f62a0af-8ae8-2111-eb4a-4da03b6473a2


-- max salesno setting fix for Yokohoma branch (later for all branches)
SELECT MAX(salesno) FROM sms_salesgroup WHERE salesno LIKE '03%' AND deleted=0 ;

--  2011-05 combini check

SELECT last_name, first_name, idnumber, salesno, SUM(in_amount) as total, contact_id
FROM combini_history
LEFT JOIN contacts ON contact_id=contacts.id 
WHERE combini_history.date_entered > '2011-04-30' AND combini_history.date_entered <'2011-05-30' AND error_code!='no_error'
GROUP BY salesno
ORDER BY salesno


SELECT last_name, first_name, idnumber, contact_id, salesno, SUM(actual_in_amount) as total, SUBSTRING(t_date, 1, 10) as t_date
FROM sms_transaction, contacts
WHERE payment_type='combini' AND t_date >= '2011-04-30' AND t_date <'2011-05-31' 
	AND sms_transaction.contact_id=contacts.id AND sms_transaction.deleted=0
GROUP BY salesno
ORDER BY salesno

-- 2011-06-29 toiawase status change
SELECT id, last_name, first_name, idnumber, application_date, custom_var17 FROM contacts 
WHERE deleted=0 AND contact_status=5 AND application_date <'2011-06-25' ;

UPDATE contacts SET custom_var17='done' 
WHERE deleted=0 AND contact_status=5 AND application_date <'2011-06-25'


-- 2011-09-08 fee_subcategory fix
SELECT * FROM `sms_fee` WHERE `fee_subcategory` LIKE 'return_summeｒ_class' ;
UPDATE `sms_fee` SET fee_subcategory='return_summer_class' WHERE `fee_subcategory` LIKE 'return_summeｒ_class' ;

-- 2012-01-25  toiawase email kubun update
SELECT id, custom_var17 FROM `contacts` WHERE custom_var17 IS NOT NULL AND custom_var17 !='done' AND smsgroup='student' 
AND school_id NOT IN ('863649b2-6596-dd64-39ee-4b3452b0aeb5', '1e815a81-badd-ce69-5e94-4b343f88a265', 'e68e95eb-26e6-af79-5ec9-4b343ec9f4ce')

UPDATE contacts SET custom_var17='done' WHERE custom_var17 IS NOT NULL AND custom_var17 !='done' AND smsgroup='student' 
AND school_id NOT IN ('863649b2-6596-dd64-39ee-4b3452b0aeb5', '1e815a81-badd-ce69-5e94-4b343f88a265', 'e68e95eb-26e6-af79-5ec9-4b343ec9f4ce')

-- 2012-01-26 fee update
SELECT * FROM `sms_fee` WHERE start_date= '2013-01-01' AND deleted =0 AND contact_id IS NOT NULL
SELECT * FROM `sms_fee` WHERE start_date= '2013-02-01' AND deleted =0 AND contact_id IS NOT NULL

UPDATE sms_fee SET start_date= '2012-01-01' WHERE start_date= '2013-01-01' AND deleted =0 AND contact_id IS NOT NULL;
UPDATE sms_fee SET start_date= '2012-02-01' WHERE start_date= '2013-02-01' AND deleted =0 AND contact_id IS NOT NULL;

-- 2012-03-01
SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, contact_status FROM `contacts` 
WHERE date_entered<='2012-02-29 15:00:00'  AND grade IS NOT NULL AND grade!='' 
	AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken');

SELECT id, email1, date_entered, date_modified, grade, grade_entered, grade_entrance_date, contact_status FROM `contacts` 
WHERE date_entered<='2012-02-29 15:00:00'  AND grade = '18'
	
UPDATE contacts SET grade=grade+1 WHERE smsgroup='student' AND grade IS NOT NULL AND grade!=''
	AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND date_entered<='2012-02-29 15:00:00'
	
UPDATE contacts SET grade='general1' WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') 
	AND grade=18 AND date_entered<='2012-02-29 15:00:00'
	
-- mypage related DB updates
ALTER TABLE `exam` DROP `exam_takable_by` ;
ALTER TABLE `exam` ADD `kamoku_name` VARCHAR( 50 ) NULL DEFAULT NULL AFTER `resource_id` ;
ALTER TABLE `exam` ADD `nendo` VARCHAR( 16 ) NULL DEFAULT NULL AFTER `max_point` ;


CREATE TABLE IF NOT EXISTS `exam_stats` (
  `contact_id` varchar(36) NOT NULL DEFAULT '',
  `subject_id` varchar(36) NOT NULL DEFAULT '',
  `rawscore` varchar(5) DEFAULT NULL,
  `rank` varchar(5) DEFAULT NULL,
  `period` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`contact_id`,`subject_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `kadai_master` (
  `id` varchar(36) NOT NULL,
  `kamoku` varchar(32) DEFAULT NULL,
  `kubun` varchar(64) DEFAULT NULL,
  `subkubun` varchar(64) DEFAULT NULL,
  `name` varchar(128) NOT NULL,
  `orderno` smallint(1) NOT NULL DEFAULT '0',
  `publisher` varchar(64) DEFAULT NULL,
  `valid` varchar(4) NOT NULL DEFAULT '有',
  `description` varchar(128) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


-- DROP TABLE IF EXISTS `kadai`;
CREATE TABLE IF NOT EXISTS `kadai` (
  `id` varchar(36) NOT NULL,
  `contact_id` varchar(36) NOT NULL default '',
  `kamoku` varchar(32) default NULL,
  `kubun` varchar(64) default NULL,
  `kadai_name` varchar(128) default NULL,
  `kadai_id` varchar(36) default NULL,
  `nendo` varchar(16) default NULL,
  `start_date` date default NULL,
  `target_date` date default NULL,
  `end_date` date default NULL,
  `start_percent` tinyint(1) NOT NULL default '0',
  `end_percent` tinyint(1) NOT NULL default '0',
  `completed_percent` tinyint(1) NOT NULL default '0',
  `date_updated` date default NULL,
  `deleted` tinyint(1) NOT NULL default '0',
  `modified_user_id` varchar(36) default '1'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `kadai` (`id`, `contact_id`, `kamoku`, `kubun`, `kadai_name`, `kadai_id`, `nendo`, `start_date`, `target_date`, `end_date`, `start_percent`, `end_percent`, `completed_percent`, `date_updated`, `deleted`, `modified_user_id`) VALUES
('kadai_kojin_0001', 'e409babc-d0d5-ba9a-af56-4bff397faa95', '国語', '現代文編', '基礎入試現代文―分析と攻略（ダイアグラム）', 'kadai_01003', '2012', '2012-03-10', '2012-03-15', NULL, 0, 100, 0, '2012-03-10', 0, '1');

-- 20120417  fee_subcategory change

UPDATE sms_fee SET fee_subcategory="tuition_21_subject3" WHERE `fee_subcategory` LIKE 'tuition_21_3subject' ;
UPDATE sms_fee SET fee_subcategory="tuition_21_subject_and_japanese_or_kobun3" WHERE `fee_subcategory` LIKE 'tuition_21_3subject_and_japanese_or_kobun' ;

-- 20120903 honban upload DB changes
-- 2012-02-06 salesno DB field update
ALTER TABLE `sms_fee` CHANGE `salesno` `salesno` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;
ALTER TABLE `sms_transaction` CHANGE `salesno` `salesno` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;
ALTER TABLE `sms_salesgroup` CHANGE `salesno` `salesno` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;

CREATE TABLE IF NOT EXISTS `sms_entrance` (
  `id` varchar(36) NOT NULL,
  `resource_id` varchar(36) default NULL,
  `card_id` varchar(32) default NULL,
  `contact_id` varchar(36) default NULL,
  `device_id` varchar(255) character set utf8 default NULL,
  `card_type` varchar(20) default NULL,
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `card_input_date` datetime default NULL,
  `deleted` tinyint(4) NOT NULL,
  `modified_user_id` varchar(36) NOT NULL,
  `created_by` varchar(36) NOT NULL,
  `status` tinyint(4) NOT NULL default '1',
  KEY `resource_id` (`resource_id`),
  KEY `contact_id` (`contact_id`),
  KEY `card_input_date` (`card_input_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `trans_combini_hist` (
  `transaction_id` varchar(36) NOT NULL,
  `contact_id` varchar(36) NOT NULL,
  `old_combini_sakusei_id` varchar(16) NOT NULL,
  `new_combini_sakusei_id` varchar(16) NOT NULL,
  `date_modified` datetime NOT NULL,
  PRIMARY KEY (`transaction_id`,`contact_id`,`old_combini_sakusei_id`,`new_combini_sakusei_id`),
  KEY `old_combini_sakusei_id` (`old_combini_sakusei_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE `event_contacts` ADD INDEX ( `start_date` ) ;

ALTER TABLE `sms_entrance` ADD `status` TINYINT NOT NULL DEFAULT '1' ;

ALTER TABLE `contacts` CHANGE `card_id` `card_id` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;

ALTER TABLE `sms_transaction` ADD `combini_sakusei_id` INT NOT NULL DEFAULT '0';
ALTER TABLE `sms_transaction` CHANGE `combini_sakusei_id` `combini_sakusei_id` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0';
ALTER TABLE `sms_transaction` ADD INDEX ( `combini_sakusei_id` );

ALTER TABLE `exam` DROP `exam_takable_by` ;
ALTER TABLE `exam` ADD `kamoku_name` VARCHAR( 50 ) NULL DEFAULT NULL AFTER `resource_id` ;
ALTER TABLE `exam` ADD `nendo` VARCHAR( 16 ) NULL DEFAULT NULL AFTER `max_point` ;

ALTER TABLE `batch_manager` CHANGE `category` `category` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

ALTER TABLE `batch_manager` ADD `school_id` VARCHAR( 36 ) NULL DEFAULT NULL ;
--2012-08-30

-- 20120903 seiseki kanren

UPDATE exam SET exam_subtype1='前期' WHERE exam_type IN ('コマンドテスト', 'コマンドテスト追試験', 'レビューテスト', 'レビューテスト追試験')

SELECT exam.name, exam.kamoku_name, exam_subtype2, SUBSTR(exam.exam_date, 1, 16) as exam_datetime, exam_grades.score, exam.exam_type, exam_id FROM exam
INNER JOIN exam_grades ON exam.id = exam_grades.exam_id AND contact_id='9d95fe9c-e7ab-7bf5-da7a-4d7869fbb71a' 
WHERE exam.deleted=0 AND exam_type IN ('コマンドテスト', 'コマンドテスト追試験') 	AND exam.nendo = '2012'   
ORDER BY exam.exam_subtype2, exam.exam_date 


-- 2012-09-04  exam data export

SELECT resource.name as school_name, exam.school_id, kamoku_name, course_category.name AS category_name, smsevent.name AS course_name, exam_date, exam.name AS exam_name,
	exam_subtype1 AS exam_ki, exam_subtype2 AS kaisu, exam.id AS exam_id, smsevent.shortname, custom_var36 AS nendo, custom_var38 AS course_kamoku, custom_var39 AS course_level, custom_var40 AS shurui
FROM exam 
LEFT JOIN smsevent ON exam.event_id=smsevent.id AND smsevent.deleted=0
LEFT JOIN course_category ON course_category.id=smsevent.parent_id AND course_category.deleted=0
LEFT JOIN resource ON resource.category='school' AND exam.school_id=resource.id AND smsevent.school_id=resource.id AND course_category.school_id=resource.id 
WHERE exam.deleted=0 AND exam.event_id IS NOT NULL 
ORDER BY resource.school_id, exam.kamoku_name, course_category.name, smsevent.name, exam.exam_date, exam.name


CREATE TABLE IF NOT EXISTS `a_exam` (
  `school_name` varchar(256) DEFAULT NULL,
  `school_id` varchar(36) DEFAULT NULL,
  `kamoku_name` varchar(256) DEFAULT NULL,
  `category_name` varchar(256) DEFAULT NULL,
  `course_name` varchar(256) DEFAULT NULL,
  `exam_date` varchar(36) DEFAULT NULL,
  `exam_name` varchar(256) DEFAULT NULL,
  `exam_subtype1` varchar(36) DEFAULT NULL,
  `exam_subtype2` varchar(36) DEFAULT NULL,
  `exam_id` varchar(36) DEFAULT NULL,
  `shortname` varchar(36) DEFAULT NULL,
  `nendo` varchar(36) DEFAULT NULL,
  `course_kamoku` varchar(256) DEFAULT NULL,
  `course_level` varchar(256) DEFAULT NULL,
  `shurui` varchar(256) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT exam_id, a_exam.exam_subtype2, exam.exam_subtype2 FROM exam, a_exam WHERE exam_id=exam.id ;

UPDATE exam, a_exam SET exam.exam_subtype2=a_exam.exam_subtype2 WHERE exam_id=exam.id ;

SELECT * FROM `exam` WHERE exam_type IN ('コマンドテスト', 'コマンドテスト追試験', 'レビューテスト', 'レビューテスト追試験') AND deleted=0 AND exam_subtype2 IS NULL 

UPDATE exam SET exam_subtype1='前期' WHERE exam_subtype1 IS NULL

-- 2012-09-30

INSERT INTO `kadai_master` (`id`, `kamoku`, `kubun`, `subkubun`, `name`, `orderno`, `publisher`, `valid`, `description`) VALUES
('nkadai_03028', '英語', '精読編', '', 'Construction Starter ', 961, 'みすず学苑', '有', ''),
('nkadai_03029', '英語', '精読編', '', 'ＰＣ English', 962, 'みすず学苑', '有', ''),
('nkadai_03030', '英語', '精読編', '', '英文解釈のトレ-ニング 必修編　', 963, 'みすず学苑', '有', ''),
('nkadai_03031', '英語', '精読編', '', 'ＯＬ English', 964, 'みすず学苑', '有', ''),
('nkadai_03032', '英語', '精読編', '', '入門英文解釈の技術 ７０', 965, 'みすず学苑', '有', '');

-- 2012-10-01 toiawase field update
SELECT id, last_name, first_name, idnumber, application_date, custom_var17 FROM contacts WHERE deleted=0 AND smsgroup='student' AND application_date <'2012-09-01' ;

UPDATE contacts SET custom_var17='done' WHERE deleted=0 AND smsgroup='student' AND application_date <'2012-09-01'

-- 2012-10-12 kadai addition

INSERT INTO `kadai_master` (`id`, `kamoku`, `kubun`, `subkubun`, `name`, `orderno`, `publisher`, `valid`, `description`) VALUES
('nkadai_01024', '国語', '現代文編', '', '大学入試問題選『現代文　中堅私立大学レベル（マ-ク式＋記述式）', 7, 'みすず学苑', '有', '');

-- 2012-11-06 exam table hizuke updatr

UPDATE `exam` SET `instance_id`=DATE_ADD(`instance_id`, INTERVAL 540 MINUTE) WHERE  instance_id LIKE '2%'
    
-- 2012-12-20
UPDATE `bank_info`.`bank_info` SET `katakana` = 'ﾐﾂｲｽﾐﾄﾓｼﾝﾀｸ ',
`name` = '三井住友信託' WHERE CONVERT( `bank_info`.`bank_code` USING utf8 ) = '0294' AND CONVERT( `bank_info`.`branch_code` USING utf8 ) = '000' AND CONVERT( `bank_info`.`katakana` USING utf8 ) = 'ｽﾐﾄﾓｼﾝﾀｸ ' AND CONVERT( `bank_info`.`name` USING utf8 ) = '住友信託' AND CONVERT( `bank_info`.`type` USING utf8 ) = '1' AND CONVERT( `bank_info`.`branch_type` USING utf8 ) = '1' LIMIT 1 ;
INSERT INTO `bank_info`.`bank_info` (`bank_code`, `branch_code`, `katakana`, `name`, `type`, `branch_type`) VALUES ('0294', '629', 'ﾊﾁｵｳｼﾞ', '八王子', '2', '1');


--2012-12-26   nyutai resource insert

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`, `activation_key`, `area_id`) VALUES
('nyutai_install_id_yo_1', '7b00e40c-8b57-406a-ac7f-4b343f2451d7', '2012-12-26 17:00:00', '2012-12-26 17:00:00', '1', '1', 'entrance system', '横浜校', '', 'entrance', NULL, 0, 1, '', NULL, '', NULL),
('nyutai_install_id_ue_1', 'db265adc-c5a8-f8d0-a7a6-4b343ee6e1bf', '2012-12-26 17:00:00', '2012-12-26 17:00:00', '1', '1', 'entrance system', '上野の森校舎', '', 'entrance', NULL, 0, 1, '', NULL, '', NULL),
('nyutai_install_id_to_1', '4a493c88-1df0-aad0-3c96-4b343f2f6c7a', '2012-12-26 17:00:00', '2012-12-26 17:00:00', '1', '1', 'entrance system', '所沢校', '', 'entrance', NULL, 0, 1, '', NULL, '', NULL),
('nyutai_install_id_ta_1', '157d198b-2416-0e1e-5dc3-4b343e7e1461', '2012-12-26 17:00:00', '2012-12-26 17:00:00', '1', '1', 'entrance system', '立川駅・北口校', '', 'entrance', NULL, 0, 1, '', NULL, '', NULL),
('nyutai_install_id_om_1', 'd358388c-1af7-9977-4268-4b343e3e65e3', '2012-12-26 17:00:00', '2012-12-26 17:00:00', '1', '1', 'entrance system', '大宮校', '', 'entrance', NULL, 0, 1, '', NULL, '', NULL),
('nyutai_install_id_ni_1', '863649b2-6596-dd64-39ee-4b3452b0aeb5', '2012-12-26 17:00:00', '2012-12-26 17:00:00', '1', '1', 'entrance system', '西荻本校', '', 'entrance', NULL, 0, 1, '', NULL, '', NULL),
('nyutai_install_id_mi_1', 'e68e95eb-26e6-af79-5ec9-4b343ec9f4ce', '2012-12-26 17:00:00', '2012-12-26 17:00:00', '1', '1', 'entrance system', '南浦和校', '', 'entrance', NULL, 0, 1, '', NULL, '', NULL),
('nyutai_install_id_ch_1', '1e815a81-badd-ce69-5e94-4b343f88a265', '2012-12-26 17:00:00', '2012-12-26 17:00:00', '1', '1', 'entrance system', '千葉駅校', '', 'entrance', NULL, 0, 1, '', NULL, '', NULL);

-- 2013-03-05 contact status update according to grade
SELECT id, last_name, contact_status, date_modified, grade, grade_entrance_date, grade_entered FROM contacts WHERE grade='general1' AND contact_status='0' ;
UPDATE contacts SET contact_status='7' WHERE grade='general1' AND contact_status='0' ;


-- 2013-06-20 course tree teian related
-- nishiogikubo  863649b2-6596-dd64-39ee-4b3452b0aeb5
SELECT * FROM `course_category` WHERE `deleted` =0 AND `date_entered` LIKE '2013%' AND `school_id` LIKE '863649b2-6596-dd64-39ee-4b3452b0aeb5'
SELECT * FROM `smsevent` WHERE `deleted` =0 AND `date_entered` LIKE '2013%' AND `school_id` LIKE '863649b2-6596-dd64-39ee-4b3452b0aeb5'

SELECT name, custom_var36, custom_var38, date_entered FROM `smsevent` WHERE `deleted` =0 AND `date_entered` > '2013-03-01 00:00:00' AND `date_entered` < '2014-03-01 00:00:00' AND `school_id` LIKE '863649b2-6596-dd64-39ee-4b3452b0aeb5'
SELECT * FROM `course_category` WHERE `deleted` =0 AND `date_entered` > '2013-03-01 00:00:00' AND `date_entered` < '2014-03-01 00:00:00' AND `school_id` LIKE '863649b2-6596-dd64-39ee-4b3452b0aeb5'

-- chibaekimae  1e815a81-badd-ce69-5e94-4b343f88a265
SELECT * FROM `course_category` WHERE `deleted` =0 AND `date_entered` LIKE '2013%' AND `school_id` LIKE '1e815a81-badd-ce69-5e94-4b343f88a265'
SELECT * FROM `smsevent` WHERE `deleted` =0 AND `date_entered` LIKE '2013%' AND `school_id` LIKE '1e815a81-badd-ce69-5e94-4b343f88a265'

SELECT name, custom_var36, custom_var38, date_entered FROM `smsevent` WHERE `deleted` =0 AND `date_entered` > '2013-03-01 00:00:00' AND `date_entered` < '2014-03-01 00:00:00' AND `school_id` LIKE '1e815a81-badd-ce69-5e94-4b343f88a265'
SELECT * FROM `course_category` WHERE `deleted` =0 AND `date_entered` > '2013-03-01 00:00:00' AND `date_entered` < '2014-03-01 00:00:00' AND `school_id` LIKE '1e815a81-badd-ce69-5e94-4b343f88a265'