-- 2012-07-14 import

SELECT idnumber, last_name, first_name FROM contacts WHERE smsgroup = 'student' AND contact_status != '0'

UPDATE contacts SET idnumber=NULL WHERE smsgroup = 'student' AND contact_status != '0' ;

UPDATE `noborders`.`resourceschool_cstm` SET `last_student_id_c` = '1000' WHERE `resourceschool_cstm`.`id_c` = 'sssssss-1111' LIMIT 1 ;
UPDATE `noborders`.`resourceschool_cstm` SET `last_student_id_c` = '1000' WHERE `resourceschool_cstm`.`id_c` = '9581c53d-6b97-18f1-def3-4fc2fff07f29' LIMIT 1 ;
UPDATE `noborders`.`resourceschool_cstm` SET `last_student_id_c` = '1000' WHERE `resourceschool_cstm`.`id_c` = '2251222b-6147-8e3d-80b5-4fc2ff22f4b9' LIMIT 1 ;
UPDATE `noborders`.`resourceschool_cstm` SET `last_student_id_c` = '1000' WHERE `resourceschool_cstm`.`id_c` = 'aed8fd74-08e6-9617-1f38-4ff51e735840' LIMIT 1 ;


-- 2013-03-15

SELECT * FROM `sms_fee` WHERE sms_fee.deleted =0 AND sms_fee.start_date = '2013-04-01' AND sms_fee.sell_date = '2013-03-16 10:00:00' AND sms_fee.fee_subcategory = '指導関連費'

SELECT sms_fee.id, sms_salesgroup.id, sms_salesgroup.deleted, sms_fee.salesno FROM sms_fee, sms_salesgroup WHERE sms_fee.salesno=sms_salesgroup.salesno AND sms_fee.deleted =0 AND sms_fee.start_date = '2013-04-01' AND sms_fee.fee_subcategory = '指導関連費'

UPDATE sms_fee, sms_salesgroup SET sms_salesgroup.deleted=1 WHERE sms_fee.salesno=sms_salesgroup.salesno AND sms_fee.deleted =0 AND sms_fee.start_date = '2013-04-01' AND sms_fee.fee_subcategory = '指導関連費'


SELECT sms_fee.id, sms_transaction.id, sms_transaction.deleted FROM sms_fee, sms_transaction WHERE sms_fee.id=sms_transaction.fee_id AND sms_fee.deleted =0 AND sms_fee.start_date = '2013-04-01' AND sms_fee.fee_subcategory = '指導関連費'

UPDATE sms_fee, sms_transaction SET sms_transaction.deleted=1 WHERE sms_fee.id=sms_transaction.fee_id AND sms_fee.deleted =0 AND sms_fee.start_date = '2013-04-01' AND sms_fee.fee_subcategory = '指導関連費'


UPDATE sms_fee SET sms_fee.deleted=1 WHERE sms_fee.deleted =0 AND sms_fee.start_date = '2013-04-01' AND sms_fee.fee_subcategory = '指導関連費'


-- 2013-04-23

SELECT custom_var17, custom_var5, id FROM `contacts` WHERE custom_var17 IS NULL AND custom_var5 IS NOT NULL

UPDATE contacts SET custom_var17='1' WHERE custom_var17 IS NULL AND custom_var5 IS NOT NULL



-- 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") AND grade_entered IS NOT NULL 

UPDATE contacts SET grade="general" WHERE smsgroup='student' AND grade ="17" AND grade_entered IS NOT NULL 
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")  AND grade_entered IS NOT NULL 


SELECT id, last_name, first_name, idnumber, 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")  AND grade_entrance_date IS NULL 


-- SELECT contacts.grade_entrance_date, contacts_20140331.grade_entrance_date FROM contacts, contacts_20140331 WHERE contacts.id=contacts_20140331.id AND contacts.smsgroup='student' AND contacts.grade  IN ("00", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17")  
-- UPDATE contacts, contacts_20140331  SET contacts.grade_entrance_date=contacts_20140331.grade_entrance_date WHERE contacts.id=contacts_20140331.id AND contacts.smsgroup='student' AND contacts.grade  IN ("00", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17")  


UPDATE contacts SET grade_entrance_date=NULL WHERE smsgroup='student' AND grade  IN ("00", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17")  AND grade_entered IS NULL 
UPDATE contacts SET grade_entered=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")  AND grade_entrance_date IS NULL 
UPDATE contacts SET grade_entrance_date='2012-07-10' WHERE smsgroup='student' AND grade  IN ("00", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17")  AND grade_entrance_date IS NULL 

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

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