-- 2011-02-10

SELECT last_name, grade, grade_entrance_date, grade_entered, SUBSTRING(grade_entrance_date, 6, 2) as month, SUBSTRING(grade_entrance_date, 1, 4) as year, 
	(2011-SUBSTRING(grade_entrance_date, 1, 4)) as diff
FROM CONTACTS WHERE smsgroup='student' AND grade IS NOT NULL AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken')
	AND SUBSTRING(grade_entrance_date, 6, 2) < 4 ;
SELECT last_name, grade, grade_entrance_date, grade_entered, SUBSTRING(grade_entrance_date, 6, 2) as month, SUBSTRING(grade_entrance_date, 1, 4) as year, 
	(2010-SUBSTRING(grade_entrance_date, 1, 4)) as diff
FROM CONTACTS WHERE smsgroup='student' AND grade IS NOT NULL AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') 
	AND SUBSTRING(grade_entrance_date, 6, 2) >= 4 ;

-- first set the grade_entered
ALTER TABLE `contacts` ADD `grade_entered` VARCHAR( 10 ) AFTER `grade` ;

UPDATE contacts SET grade_entered=grade WHERE smsgroup='student' ; 

-- calculate new current grade from original grade entered, month of grade change is April -> example 1
UPDATE contacts SET grade=grade_entered + (2011-SUBSTRING(grade_entrance_date, 1, 4)) WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND SUBSTRING(grade_entrance_date, 6, 2) < 4 ;
UPDATE contacts SET grade=grade_entered + (2010-SUBSTRING(grade_entrance_date, 1, 4)) WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND SUBSTRING(grade_entrance_date, 6, 2) >= 4 ;

-- calculate new current grade from original grade entered, month of grade change is March -> example 2
UPDATE contacts SET grade=grade_entered + (2011-SUBSTRING(grade_entrance_date, 1, 4)) WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND SUBSTRING(grade_entrance_date, 6, 2) < 3 ;
UPDATE contacts SET grade=grade_entered + (2010-SUBSTRING(grade_entrance_date, 1, 4)) WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND SUBSTRING(grade_entrance_date, 6, 2) >= 3 ;

-- some grades are assigned greater than 18, assign them back to 18 -> grade_ippan_general
SELECT last_name, grade, grade_entrance_date, grade_entered
FROM contacts WHERE smsgroup='student' AND grade IS NOT NULL AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND grade > 18;

UPDATE contacts SET grade=18 WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND grade > 18 ;

UPDATE contacts SET grade=15 WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND grade > 15 ;


SELECT DISTINCT grade
FROM `contacts`
WHERE date_modified > '2008-01-01'


-- LATER DO IN MARCH (misuzu), IN SEPTEMBER (horiozon) OR APRIL (other remaining schools) 

-- for batch processing or gakunen updates on the terms where 18 is grade_ippan_general  - for change month 3
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 grade_entrance_date < '2013-03-01' AND date_modified < '2013-03-01 00:00:00' ;
	
SELECT id, last_name, date_modified, grade, grade_entrance_date, grade_entered FROM contacts WHERE grade_entrance_date < '2013-03-01' AND date_modified < '2013-03-01 00:00:00' ;

SELECT id, last_name, date_modified, grade, grade_entrance_date, grade_entered FROM contacts WHERE grade=18 ;
UPDATE contacts SET grade='general1' WHERE grade=18 ;
	
-- for batch processing or gakunen updates on the terms where 15 is grade_ippan_general  - for change month 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', '15') AND grade_entrance_date < '2013-04-01'
	
SELECT last_name, grade, grade_entrance_date, grade_entered FROM contacts WHERE grade_entrance_date >= '2011-04-01';

-- little steps grade update
SELECT DISTINCT grade FROM contacts WHERE grade NOT IN ('00', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15') ;

SELECT last_name, grade, grade_entrance_date, grade_entered FROM contacts WHERE grade IN ('00', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15') ;

SELECT last_name, grade, grade_entrance_date, grade_entered FROM contacts 
WHERE grade IN ('00', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15') AND grade=grade_entered AND grade_entrance_date <'2010-04-01'

UPDATE contacts SET grade=grade_entered + (2011-SUBSTRING(grade_entrance_date, 1, 4)) 
WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND SUBSTRING(grade_entrance_date, 6, 2) < 4 
	AND grade IN ('00', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15') AND grade=grade_entered AND grade_entrance_date <'2010-04-01' ;

UPDATE contacts SET grade=grade_entered + (2010-SUBSTRING(grade_entrance_date, 1, 4)) 
WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND SUBSTRING(grade_entrance_date, 6, 2) >= 4 
	AND grade IN ('00', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15') AND grade=grade_entered AND grade_entrance_date <'2010-04-01' ;


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', '15') AND grade_entrance_date < '2011-04-01'
	AND grade IN ('00', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15') ;
	
SELECT last_name, grade, grade_entrance_date, grade_entered FROM contacts WHERE grade > 15

UPDATE contacts SET grade=15 WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND grade > 15 ; 

-- akamon not this time (grade part NOT UPDATE YET)
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');
UPDATE contacts SET grade='18' WHERE smsgroup='student' AND grade='19' ;

SELECT last_name, grade, grade_entrance_date, grade_entered FROM contacts WHERE smsgroup='student' AND grade IS NOT NULL 

SELECT last_name, grade, grade_entrance_date, grade_entered FROM contacts WHERE smsgroup='student' AND grade IS NOT NULL
	AND grade IN ('general', 'general1', 'general2', 'general3', 'eiken', '18') 

	
-- 2013-02-28 gakunen updates
-- misuzu
-- first copy contacts table into contacts20130331
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');
UPDATE contacts SET grade='general1' WHERE grade=18 ;


-- 2013-03-31 gakunen updates
-- first copy contacts table into contacts20130331

-- alc & asunaro & belle & child-village & higashinihon & juku & leaf & noborders & silia
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');
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND grade='18' ;

-- FICS
UPDATE contacts SET grade=grade+1 WHERE smsgroup='student' AND grade IS NOT NULL AND grade!='' AND school_id='sssssss-1111'  AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken');
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND school_id='sssssss-1111' AND grade='18' ;

-- gojuku & little-steps
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');
UPDATE contacts SET grade='15' WHERE smsgroup='student' AND grade='16' ;
	
-- Hegl 
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');
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND grade='19' ;

-- sho-shin 
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');
UPDATE contacts SET grade='18' WHERE smsgroup='student' AND grade='19' ;


-- 2015-03-31 gakunen updates
-- akamon, basic schools, alc-alphawing, juku are not needed (last 2 have batch processes)
-- first copy contacts table into contacts20150331

SELECT id, last_name, date_modified, grade, grade_entrance_date, grade_entered, school_id FROM contacts WHERE deleted=0 AND smsgroup='student' AND grade IS NOT NULL AND grade!='' 
SELECT id, last_name, date_modified, grade, grade_entrance_date, grade_entered FROM contacts WHERE deleted=0 AND smsgroup='student' AND grade IS NOT NULL AND grade='18' 
SELECT DISTINCT grade FROM contacts WHERE deleted=0 AND smsgroup='student' AND grade IS NOT NULL AND grade!='' ORDER BY grade ASC

SELECT DISTINCT school_id FROM contacts WHERE deleted=0 AND smsgroup='student' AND grade IS NOT NULL AND grade!=''   -- hegl

-- itojuku
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');
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' ;

-- higashinihon & alc & noborders & leaf & asunaro & silia=aquaplannet
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');
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND grade='18' ;

-- FICS
UPDATE contacts SET grade=grade+1 WHERE smsgroup='student' AND grade IS NOT NULL AND grade!='' AND school_id='sssssss-1111'  AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken');
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND school_id='sssssss-1111' AND grade='18' ;

-- Hegl   ->  branch joken teian zumi but not accepted yet -> update all branches
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');
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND grade='19' ;

UPDATE contacts SET grade=grade_entered WHERE smsgroup='student' AND grade IS NOT NULL AND grade!='' ;  -- alc-alphawing

-- gojuku 
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');
UPDATE contacts SET grade='15' WHERE smsgroup='student' AND grade='16' ;
	
-- sho-shin & jpa
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');
UPDATE contacts SET grade='18' WHERE smsgroup='student' AND grade='19' ;


-- 2016-03-31 gakunen updates
-- akamon, basic schools, alc-alphawing, juku are not needed (last 2 have batch processes)
-- first copy contacts table into contacts20160331

SELECT id, last_name, date_modified, grade, grade_entrance_date, grade_entered, school_id FROM contacts WHERE deleted=0 AND smsgroup='student' AND grade IS NOT NULL AND grade!='' 
SELECT id, last_name, date_modified, grade, grade_entrance_date, grade_entered FROM contacts WHERE deleted=0 AND smsgroup='student' AND grade IS NOT NULL AND grade='18' 
SELECT DISTINCT grade FROM contacts WHERE deleted=0 AND smsgroup='student' AND grade IS NOT NULL AND grade!='' ORDER BY grade ASC

SELECT DISTINCT school_id FROM contacts WHERE deleted=0 AND smsgroup='student' AND grade IS NOT NULL AND grade!=''   -- hegl

-- itojuku
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');
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' ;

-- higashinihon & alc & noborders & asunaro & silia=aquaplannet
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');
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND grade='18' ;

-- Hegl   ->  branch joken teian zumi but not accepted yet -> update all branches
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');
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND grade='19' ;

-- gojuku 
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 < '2016-04-01 00:00:00' ;
UPDATE contacts SET grade='15' WHERE smsgroup='student' AND grade='16' ;
	
-- sho-shin & jpa & flaps
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');
UPDATE contacts SET grade='18' WHERE smsgroup='student' AND grade='19' ;

--  zoophoenics 
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');
UPDATE contacts SET grade='12' WHERE smsgroup='student' AND grade='13' ;

-- creo -> have to confirm!!!


-- 2017-03-31 gakunen updates
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') WHERE 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') WHERE 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') WHERE 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') WHERE grade_entrance_date <'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') WHERE 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') WHERE 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 < '2016-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') WHERE date_modified <'2017-04-01';
UPDATE contacts SET grade='18' WHERE smsgroup='student' AND grade='19' ;