-- 2017-03-31 gakunen updates

SELECT last_name, date_entered, date_modified, idnumber, grade, grade_entrance_date, grade_entered, id FROM contacts WHERE smsgroup='student' AND SUBSTRING(date_modified, 1, 10)='2017-03-31' ORDER BY  `contacts`.`date_modified` DESC 

itojuku server
-- first copy contacts table into contacts20170331

1.  新学年更新コード key: grade_entrance_date
https://itojuku.e-school.jp/index.php
※特別ルールあり  「学年資料」シート参照
\\ASO\marketing\Customers\617_伊藤塾\伊藤塾\08_詳細設計\B_受講生管理\B-001【詳細】（完）_受講生登録：窓口02_受講生登録.xls

UPDATE contacts SET grade=grade+1 WHERE smsgroup='student' AND grade IS NOT NULL AND grade!='' AND grade NOT IN ('22', 'general', 'general1', 'general2', 'general3', 'eiken', 'kousotsu', 'houka1', 'houka2', 'houka3', 'houka4', 'graduate', 'm1', 'm2', 'd1', 'd2', 'd3', 'g5', 'g6', 'g7', 'g8') AND grade_entrance_date <'2017-04-01' ;
UPDATE contacts SET grade='houka4' WHERE smsgroup='student' AND grade='houka3' ;
UPDATE contacts SET grade='houka3' WHERE smsgroup='student' AND grade='houka2' ;
UPDATE contacts SET grade='houka2' WHERE smsgroup='student' AND grade='houka1' ;

236 server
-- first copy contacts table into contacts20170331

2.  
https://zoo-phonics.e-school.jp		-> max grade =12

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_modified <'2017-04-01' ;
UPDATE contacts SET grade='12' WHERE smsgroup='student' AND grade='13' ;

3.  新学年更新コード key: grade_entrance_date
https://flaps.e-school.jp/			-> max 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 grade_entrance_date <'2017-04-01' ;
UPDATE contacts SET grade='18' WHERE smsgroup='student' AND grade='19' ;

4.  
https://jpa.e-school.jp			-> max grade= 14

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_modified <'2017-04-01' ;
UPDATE contacts SET grade='14' WHERE smsgroup='student' AND grade='15' ;


234 server
-- first copy contacts table into contacts20170331


-- higashinihon & alc & noborders & asunaro & silia=aquaplannet

5.
https://higashinihon.e-school.jp		-> max grade= general (general comes after 17)

6.
https://alc-gp.e-school.jp			-> max grade= general (general comes after 17)

5.
https://aquaplannet.e-school.jp		-> max grade= general (general comes after 17)

7.
https://noborders.e-school.jp/		-> max grade= general (general comes after 17)

9.
https://asunaro.e-school.jp/		-> max grade= general (general comes after 17)

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_modified <'2017-04-01';
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND grade='18' ;

8.
https://hegl.e-school.jp/			-> max grade= general (general comes after 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_modified <'2017-04-01';
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND grade='19' ;

10.
https://gojuku.e-school.jp/		-> max grade= 15

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_modified < '2017-04-01 00:00:00' ;
UPDATE contacts SET grade='15' WHERE smsgroup='student' AND grade='16' ;

11.
https://kenjusha.e-school.jp/		-> max 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_modified <'2017-04-01';
UPDATE contacts SET grade='18' WHERE smsgroup='student' AND grade='19' ;

12.
https://juku.e-school.jp/		-> max grade= general (general comes after 17)

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 grade_entrance_date <'2017-04-01';
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND grade='18' ;



-- 2016-03-01 grade updates (early morning)
-- Misuzu
-- copy contacts to contacts_20160229
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_modified < '2016-03-01';
UPDATE contacts SET grade='general1' WHERE grade=18  AND date_modified < '2016-03-01';