SELECT idnumber, CONCAT(last_name, '　',  first_name) as name, CONCAT(last_name_furigana, '　',  first_name_furigana) as name_furigana, phone_home, phone_mobile, primary_address_city, date_entered, id
FROM `contacts` 
WHERE `deleted` =0 AND `date_entered` != '2014-01-03 00:00:00' AND `smsgroup` = 'student'

SELECT idnumber, last_name, first_name, last_name_furigana, first_name_furigana, phone_home, phone_mobile, primary_address_city, date_entered, id
FROM `contacts` 
WHERE `deleted` =0 AND `smsgroup` = 'student' AND SUBSTRING(idnumber, 1, 4)='2004'


UPDATE contacts SET idnumber='0231476' WHERE id='a2a03b4e-4bf3-1fdd-ee27-52d72eb242dc' ; 
UPDATE contacts SET idnumber='0231475' WHERE id='bb77d2ca-a6da-1db1-4388-52d7306d10c3' ; 
UPDATE contacts SET idnumber='0231474' WHERE id='b651300f-f3f2-e39e-aaf7-52d7363c7591' ; 
UPDATE contacts SET idnumber='0231472' WHERE id='ea052220-6b01-1edd-d6c6-52d73814735e' ; 
UPDATE contacts SET idnumber='0231466' WHERE id='6ae2893a-08a2-4696-947b-52d741fa4d78' ; 
UPDATE contacts SET idnumber='0231455' WHERE id='565c62de-c52c-1666-68dd-52d7462ff362' ; 
UPDATE contacts SET idnumber='0231365' WHERE id='8ba7aacb-e872-36b5-8cc7-52d74891998a' ; 
UPDATE contacts SET idnumber='0231370' WHERE id='72e89e90-cdbb-64e7-14f1-52d74aabea21' ; 
UPDATE contacts SET idnumber='0231372' WHERE id='a888dd12-73d8-a99e-205b-52d74cde7136' ; 
UPDATE contacts SET idnumber='0231464' WHERE id='937a57e4-dee3-9e53-1be5-52d7558a6d78' ; 
UPDATE contacts SET idnumber='0231467' WHERE id='12c8f79a-339c-1ee8-c50f-52d766aee63c' ; 
UPDATE contacts SET idnumber='0231450' WHERE id='c8514942-bb5a-9640-9120-52d769af149f' ; 
UPDATE contacts SET idnumber='0231461' WHERE id='16ee920b-0bc9-01b2-450d-52d775263c64' ; 
UPDATE contacts SET idnumber='0231367' WHERE id='d54a4bec-adcd-9c1f-a6e7-52d785810625' ; 
UPDATE contacts SET idnumber='0231449' WHERE id='87bcf91a-cecc-896e-ac5b-52d78725f59c' ; 
UPDATE contacts SET idnumber='0231362' WHERE id='cf03b0c4-f89f-878b-1060-52d78e387a35' ; 
UPDATE contacts SET idnumber='0231368' WHERE id='e6f48129-1424-ed72-a203-52d79deb38a4' ; 
UPDATE contacts SET idnumber='0231458' WHERE id='8c9b6022-135e-fed3-5a3b-52d7a489283b' ; 
UPDATE contacts SET idnumber='0231364' WHERE id='795807db-64fd-d7a3-ad3f-52d7a7636163' ; 
UPDATE contacts SET idnumber='0231456' WHERE id='92439e0e-7a5e-c564-aa59-52d7b4fc6b7d' ; 



-- 2013-09-30 metehan gakkomaster updates


DELETE FROM `gakkomaster` WHERE `category` LIKE '小学校'
DELETE FROM `gakkomaster` WHERE `category` LIKE '中学校'

SELECT distinct category FROM `gakkomaster` WHERE 1
小学校
中学校
高等学校
大学
短期大学
高等専門学校

SELECT * FROM gakkomaster WHERE 1 
GROUP BY name, city
HAVING count( name ) >1
ORDER BY name

INSERT INTO gm2 
SELECT * FROM gakkomaster WHERE 1 
GROUP BY name, city
HAVING count( name ) >1


SELECT gakkomaster.name, gakkomaster.category, gakkomaster.city, gakkomaster.duplicate, gm2.name, gm2.category, gm2.city, gm2.duplicate
FROM gakkomaster, gm2
WHERE gakkomaster.name=gm2.name AND gakkomaster.category=gm2.category AND gakkomaster.city=gm2.city 
ORDER BY gakkomaster.name, gakkomaster.category, gakkomaster.city

UPDATE gakkomaster, gm2 SET gakkomaster.duplicate=1 
WHERE gakkomaster.name=gm2.name AND gakkomaster.category=gm2.category AND gakkomaster.city=gm2.city 


SELECT gakkomaster.name, gakkomaster.category, gakkomaster.city, gm2.name, gm2.category, gm2.city, gm2.duplicate
FROM gakkomaster, gm2
WHERE gakkomaster.id=gm2.id

UPDATE gakkomaster, gm2 SET gakkomaster.duplicate=2
WHERE gakkomaster.id=gm2.id


--2013-11-26 DB updates for version 5.6
-- to check columns which has to be not NULL but default value is set to NULL -> logical contradiction, but some values are guarantied to be not NULL like IDs
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE
FROM `COLUMNS`
WHERE TABLE_SCHEMA = 'itojuku_test' AND IS_NULLABLE='NO' 
AND COLUMN_NAME NOT IN ('id', 'date_entered', 'date_modified', 'modified_user_id', 'created_by')
AND COLUMN_DEFAULT IS NULL

-- adding from subpanel problem: instance_contact_status is NULL in the program, so it becomes empty string, i.e '' in the created sql string, however in version 5.6 integers cannot be assigned to empty string value (in 5.1 it is OK)
INSERT INTO 
		smsevent_instances
		(id, event_id, event_type, instance_id, target_id, category, from_datetime, to_datetime, from_instance_id, to_instance_id,  instance_contact_status, status, description, date_modified) 
		VALUES 
			('ccbaf4b8-725d-cb9d-032f-5294a1867218', '38a5db82-dad2-fbd8-b8ef-524cf49dedc2', 'course', 'b13ae97e-fa59-f156-e443-527b1ea8331f', '66e660fe-73d8-260b-9947-5242362c40b1', 'classroom', '2013-11-28 17:00:00', '2013-11-28 18:00:00', '', '',  '', 1, '', '2013-11-26 22:25:42' ),
			('d17e2535-198d-8b75-fbf8-5294a11c1d68', '38a5db82-dad2-fbd8-b8ef-524cf49dedc2', 'course', 'b14ff685-00c6-1cdb-1a51-527b1e3a278c', '66e660fe-73d8-260b-9947-5242362c40b1', 'classroom', '2013-12-05 17:00:00', '2013-12-05 18:00:00', '', '',  '', 1, '', '2013-11-26 22:25:42' )
		ON DUPLICATE KEY UPDATE instance_contact_status=VALUES(instance_contact_status),
						date_modified='2013-11-26 22:25:42',
						instance_id=VALUES(instance_id),
						description=CASE WHEN VALUES(description) != '' THEN
							VALUES(description)
						ELSE
							description
						END, status = CASE WHEN  ( (status & 2) >0 or (status & 8) >0)  THEN 
								((status & -11 & -1021137) | VALUES(status))
							ELSE 
								((status & -1021137) | VALUES(status))
							END


-- 2013-11-29 data iko kakunin
SELECT id, idnumber, last_name, grade, grade_entered, grade_entrance_date FROM `contacts` WHERE 1 ;
SELECT id, idnumber, phone_mobile, phone_mobile_cleaned, phone_home, phone_home_cleaned, phone_work, phone_work_cleaned, urgency_phone, urgency_phone_cleaned, kiseisaki_phone, kiseisaki_phone_cleaned FROM contacts WHERE 1

UPDATE master_kanri SET school_id=NULL, master_category1=NULL, master_category2=NULL, field1=NULL, field2=NULL, field3=NULL, field4=NULL, field5=NULL, field6=NULL WHERE master_group='seikyou_dairiten_group'

UPDATE master_kanri SET school_id=NULL, master_category2=NULL, field4=NULL, field5=NULL, field6=NULL WHERE 1

UPDATE master_kanri SET master_category1=NULL, field2=NULL, field3=NULL WHERE master_group=''

UPDATE master_kanri SET field2=NULL, field3=NULL WHERE master_group='seikyou_dairiten'
UPDATE master_kanri SET master_category1=NULL, field2=NULL, field3=NULL WHERE master_group='koushi'
UPDATE master_kanri SET master_category1=NULL, field2=NULL, field3=NULL WHERE master_group='jukoukeitai'
UPDATE master_kanri SET master_category1=NULL WHERE master_group='buppin_category'

SELECT * FROM `koza_code_master` WHERE `koza_code_type` LIKE '1' 
AND id NOT IN ('1a000000001', '1a000000021', '1a000000037', '1a000000077', '1a000000081', '1a000000101', '1a000000115', '1a000000129', '1a000000157', '1a000000181', '1a000000199', '1a000000217', '1a000000219', '1a000000232', '1a000000242', '1a000000250', 
'1a000000254', '1a000000261', '1a000000269', '1a000000277', '1a000000280', '1a000000288', '1a000000292', '1a000000300', '1a000000307', '1a000000311', '1a000000318', '1a000000326', '1a000000327', '1a000000328', '1a000000329', '1a000000330', 
'1a000000331', '1a000000335', '1a000000343', '1a000000346', '1a000000349', '1a000000357', '1a000000365', '1a000000373', '1a000000389', '1a000000390', '1a000000391', '1a000000392', '1a000000393', '1a000000394', '1a000000395', '1a000000396', 
'1a000000397', '1a000000406', '1a000000415', '1a000000416', '1a000000425', '1a000000434', '1a000000443', '1a000000452', '1a000000461', '1a000000470', '1a000000479', '1a000000488', '1a000000542', '1a000000543', '1a000000567', '1a000000569', 
'1a000000570', '1a000000723', '1a000000724', '1a000000726', '1a000000727', '1a000000735', '1a000000744', '1a000000752', '1a000000775', '1a000000789', '1a000000817', '1a000000828', '1a000000849', '1a000000850', '1a000000851', '1a000000852', 
'1a000000853', '1a000000854', '1a000000855', '1a000000860', '1a000000861', '1a000000866', '1a000000871', '1a000000879', '1a000000880', '1a000000881', '1a000000893', '1a000000905', '1a000000920', '1a000000976', '1a000000977', '1a000000978', 
'1a000000979', '1a000000980', '1a000000981', '1a000000982', '1a000000983', '1a000000984', '1a000000987', '1a000000988', '1a000000989', '1a000000990', '1a000000995', '1a000001000', '1a000001001', '1a000001005', '1a000001017', '1a000001022', 
'1a000001023', '1a000001030', '1a000001033', '1a000001035', '1a000001036', '1a000001038', '1a000001040', '1a000001048', '1a000001049', '1a000001051', '1a000001052', '1a000001064', '1a000001065', '1a000001066', '1a000001067', '1a000001068', 
'1a000001069', '1a000001070', '1a000001071', '1a000001072', '1a000001073', '1a000001074', '1a000001075', '1a000001076')


INSERT INTO `contacts` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `assigned_user_id`, `created_by`, `salutation`, `first_name`, `last_name`, `first_name_furigana`, `last_name_furigana`, `lead_source`, `lead_source_description`, `title`, `department`, `reports_to_id`, `commission_company_id`, `birthdate`, `do_not_call`, `phone_home`, `phone_mobile`, `phone_work`, `phone_other`, `phone_fax`, `email1`, `email2`, `assistant`, `assistant_phone`, `email_opt_out`, `primary_address_street`, `primary_address_city`, `primary_address_state`, `primary_address_postalcode`, `primary_address_country`, `primary_address_kana`, `alt_address_street`, `alt_address_city`, `alt_address_state`, `alt_address_postalcode`, `alt_address_country`, `alt_address_kana`, `description`, `portal_name`, `portal_active`, `portal_app`, `invalid_email`, `smsgroup`, `contact_status`, `picture_filename`, `picture_file_mime_type`, `school_id`, `vacation_total`, `vacation_used`, `is_available_strict`, `major`, `send_document_home`, `send_document_work`, `company_name`, `idnumber`, `nationality`, `gender`, `work_school`, `last_school_name`, `last_school_dept`, `study_aim`, `transport_fee_type`, `transportfee`, `incometax_rate`, `leave_date`, `enroll_date`, `grade`, `grade_entered`, `grade_entrance_date`, `application_date`, `extra_date`, `id_card_field1`, `id_card_field2`, `id_card_field3`, `id_card_field4`, `renmei_label`, `card_id`, `nyutai_mail_notice`, `account_id`, `phone_mobile_cleaned`, `phone_home_cleaned`, `phone_work_cleaned`, `phone_other_cleaned`, `bank_payment_type`, `bank_code`, `bank_branch_code`, `bank_name`, `bank_branch_name`, `bank_account_type`, `bank_account_no`, `bank_account_name`, `taiokyohi`, `jukouseicard_uid`, `jukouseicard_enterd`, `snsdate_out`, `rank_shihoushiken`, `rank_koumuin`, `rank_shihoushoshi`, `rank_gyouseishoshi`, `houka_graduate_school`, `kishu_mishu`, `graduate_school`, `vocational`, `university`, `gakubu`, `gakka`, `hougakukei_circle`, `circle_title`, `highschool`, `work_school_etc`, `name_work`, `address_work`, `email_work`, `obog_members`, `jikanshitei`, `kyokudome`, `urgency_name`, `urgency_phone`, `urgency_phone_cleaned`, `kiseisaki_address_postalcode`, `kiseisaki_address_state`, `kiseisaki_address_city`, `kiseisaki_address_street`, `kiseisaki_address_kana`, `kiseisaki_phone`, `kiseisaki_phone_cleaned`, `kiseisaki_date_entered`, `kiseisaki_date_end`, `kyokudome_address_postalcode`, `kyokudome_address_state`, `kyokudome_address_city`, `kyokudome_address_street`, `kyokudome_address_kana`, `deletedate`) VALUES
('9999999', 0, '2013-11-29 23:51:57', '2013-11-29 23:51:57', '1', NULL, '1', NULL, 'データ調整', '受講生', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, 'student', '14', NULL, NULL, '1e0efc2d-f25f-f6cb-3fb8-50ecbf489027', NULL, NULL, 0, NULL, 1, 0, NULL, '9999999', ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0.1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

-- 2013-12-12
SELECT max(idnumber) FROM `contacts` WHERE smsgroup='student' AND SUBSTRING(idnumber, 1, 1)='2'   -- 2003684
SELECT max(idnumber) FROM `contacts` WHERE smsgroup='student' AND SUBSTRING(idnumber, 1, 1)='3'   -- 3900816
SELECT max(idnumber) FROM `contacts` WHERE smsgroup='student' AND SUBSTRING(idnumber, 1, 1)='4'   -- 4000440
SELECT max(idnumber) FROM `contacts` WHERE smsgroup='student' AND SUBSTRING(idnumber, 1, 1)='5'   -- 5801466
SELECT max(idnumber) FROM `contacts` WHERE smsgroup='student' AND SUBSTRING(idnumber, 1, 1)='6'   -- 6100862
SELECT max(idnumber) FROM `contacts` WHERE smsgroup='student' AND SUBSTRING(idnumber, 1, 1)='7'   -- 7800646

SELECT shiryo_fb_event.* FROM shiryo_fb_event_contacts LEFT JOIN shiryo_fb_event ON shiryo_fb_event_contacts.shiryo_fb_event_id=shiryo_fb_event.id WHERE shiryo_fb_event_contacts.deleted=0 AND shiryo_fb_event.shiryo_fb_event_category='fb'
SELECT shiryo_fb_event.* FROM shiryo_fb_event_contacts LEFT JOIN shiryo_fb_event ON shiryo_fb_event_contacts.shiryo_fb_event_id=shiryo_fb_event.id WHERE shiryo_fb_event_contacts.deleted=0 AND shiryo_fb_event.shiryo_fb_event_category='shiryo'
SELECT shiryo_fb_event.* FROM shiryo_fb_event_contacts LEFT JOIN shiryo_fb_event ON shiryo_fb_event_contacts.shiryo_fb_event_id=shiryo_fb_event.id WHERE shiryo_fb_event_contacts.deleted=0 AND shiryo_fb_event.shiryo_fb_event_category='event'

SELECT shiryo_fb_event.*  FROM shiryo_fb_event_contacts LEFT JOIN shiryo_fb_event ON shiryo_fb_event_contacts.shiryo_fb_event_id=shiryo_fb_event.id LEFT JOIN contacts ON shiryo_fb_event_contacts.contact_id=contacts.id WHERE shiryo_fb_event_contacts.deleted=0 AND contacts.id IS NULL AND shiryo_fb_event.id IS NOT NULL


0,5,10,15,20,25,30,35,40,45,50,55 * * * * cd /home/sites/itojuku.e-school.jp/htdocs; /usr/bin/php /home/sites/itojuku.e-school.jp/htdocs/emailmandelivery.php crontab_server_name=itojuku.e-school.jp crontab_php_self=itojuku.e-school.jp;
0,5,10,15,20,25,30,35,40,45,50,55 * * * * cd /home/sites/itojuku.e-school.jp/htdocs/test; /usr/bin/php /home/sites/itojuku.e-school.jp/htdocs/test/emailmandelivery.php crontab_server_name=itojuku.e-school.jp crontab_php_self=itojuku.e-school.jp/test;
1,6,11,16,21,26,31,36,41,46,51,56 * * * * cd /home/sites/itojuku.e-school.jp/htdocs/test2; /usr/bin/php /home/sites/itojuku.e-school.jp/htdocs/test2/emailmandelivery.php crontab_server_name=itojuku.e-school.jp crontab_php_self=itojuku.e-school.jp/test2;

missing uriage 
				
"UA20130036160","MM0228669","9000000001","104       ","201       ","300       ","3","3",,8,,10000,2013/11/20 0:00:00,,"0","EM90820",2013/11/20 20:52:38,2013/11/25 16:22:18,216305956.00

SELECT donem, yil, ki, kaikonen  FROM koza LEFT JOIN kishusei ON donem=ki 

SELECT id, ki, donem, kaikonen, yil FROM koza
INNER JOIN knew ON koza.id=knew.kod AND yil != '#N/A'

SELECT id, ki, donem, kaikonen, yil
FROM koza, knew WHERE koza.id = knew.kod AND yil != '#N/A'

UPDATE  koza, knew SET kaikonen=yil WHERE koza.id = knew.kod AND yil != '#N/A'

SELECT id, ki, donem, kaikonen, yil, jukoukeitai_code, SUBSTRING(jukoukeitai_code, 1, 2), SUBSTRING(jukoukeitai_code, 3, 20), CONCAT('37', SUBSTRING(jukoukeitai_code, 3, 20)) FROM koza, knew WHERE koza.id = knew.kod AND yil = '#N/A' AND donem='３７期' AND SUBSTRING(jukoukeitai_code, 1, 2)='04'

UPDATE koza, knew SET kaikonen='2013', ki='37', jukoukeitai_code=CONCAT('37', SUBSTRING(jukoukeitai_code, 3, 20))  WHERE koza.id = knew.kod AND yil = '#N/A' AND donem='３７期' AND SUBSTRING(jukoukeitai_code, 1, 2)='04' ; 
UPDATE koza, knew SET kaikonen='2013', ki='37', jukoukeitai_code=CONCAT('37', SUBSTRING(jukoukeitai_code, 3, 20))  WHERE koza.id = knew.kod AND yil = '#N/A' AND donem='３７期' AND SUBSTRING(jukoukeitai_code, 1, 2)='24' ;


SELECT id, ki, donem, kaikonen, yil, jukoukeitai_code, SUBSTRING(jukoukeitai_code, 1, 2), SUBSTRING(jukoukeitai_code, 3, 20), CONCAT('39', SUBSTRING(jukoukeitai_code, 3, 20)) FROM koza, knew WHERE koza.id = knew.kod AND yil = '#N/A' AND donem='３９期' AND SUBSTRING(jukoukeitai_code, 1, 2)='00'
SELECT id, ki, donem, kaikonen, yil, jukoukeitai_code, SUBSTRING(jukoukeitai_code, 1, 2), SUBSTRING(jukoukeitai_code, 3, 20), CONCAT('39', SUBSTRING(jukoukeitai_code, 3, 20)) FROM koza, knew WHERE koza.id = knew.kod AND yil = '#N/A' AND donem='３９期' AND SUBSTRING(jukoukeitai_code, 1, 2)='13'
SELECT id, ki, donem, kaikonen, yil, jukoukeitai_code, SUBSTRING(jukoukeitai_code, 1, 2), SUBSTRING(jukoukeitai_code, 3, 20), CONCAT('39', SUBSTRING(jukoukeitai_code, 3, 20)) FROM koza, knew WHERE koza.id = knew.kod AND yil = '#N/A' AND donem='３９期' AND SUBSTRING(jukoukeitai_code, 1, 2)='39'

UPDATE koza, knew SET kaikonen='2014', ki='39', jukoukeitai_code=CONCAT('39', SUBSTRING(jukoukeitai_code, 3, 20)) WHERE koza.id = knew.kod AND yil = '#N/A' AND donem='３９期' AND SUBSTRING(jukoukeitai_code, 1, 2)='00' ;
UPDATE koza, knew SET kaikonen='2014', ki='39', jukoukeitai_code=CONCAT('39', SUBSTRING(jukoukeitai_code, 3, 20)) WHERE koza.id = knew.kod AND yil = '#N/A' AND donem='３９期' AND SUBSTRING(jukoukeitai_code, 1, 2)='13' ; 
UPDATE koza, knew SET kaikonen='2014', ki='39', jukoukeitai_code=CONCAT('39', SUBSTRING(jukoukeitai_code, 3, 20)) WHERE koza.id = knew.kod AND yil = '#N/A' AND donem='３９期' AND SUBSTRING(jukoukeitai_code, 1, 2)='39' ;


select id, t_table.buppin_code from
    (SELECT buppin_name, buppin_code, id
        FROM `buppin`
        group by 
        buppin_name, buppin_code
    ) t_table    
    group by t_table.buppin_name
    having count(t_table.buppin_name) = 1 AND t_table.buppin_code = ''

CREATE TABLE IF NOT EXISTS `buppin_lookup` (
  `id` char(36) NOT NULL,
  `buppin_code` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `buppin_code` (`buppin_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


SELECT * FROM buppin, buppin_lookup WHERE buppin.id=buppin_lookup.id

SELECT * FROM `buppin` WHERE `buppin_code`=''

UPDATE buppin SET deleted=1 WHERE `buppin_code`=''

SELECT sms_transaction.id, sell_date FROM sms_transaction, buppin WHERE sms_transaction.buppin_id=buppin.id AND buppin.deleted=1

SELECT sms_transaction.id, sell_date, t_date FROM sms_transaction, buppin WHERE sms_transaction.buppin_id=buppin.id AND buppin.deleted=1  AND sms_transaction.deleted=0 ORDER BY sell_date asc

-- cleaning koza, curriculum, class for DB itojuku_training
TRUNCATE `curriculum`;
TRUNCATE `event_contacts`;
TRUNCATE `event_koza`;
TRUNCATE `koza`;
TRUNCATE `koza_contacts`;
TRUNCATE `smsevent`;
TRUNCATE `smsevent_instances`;
TRUNCATE `sms_event_instance`;
TRUNCATE `sms_event_instance_sub`;
TRUNCATE `sms_recurrence`;

-- itojuku net updates
UPDATE sms_event_instance SET date_modified=from_datetime WHERE 1;
UPDATE event_contacts SET date_modified=start_date WHERE start_date IS NOT NULL;
UPDATE koza_contacts SET date_modified=koza_juko_toroku WHERE 1

-- gakkomaster update
INSERT INTO `gakkomaster` (`id`, `name`, `category`, `subcategory`, `state`, `city`, `global_id`, `description`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`) VALUES
('T99999999', '移行データ(該当なし)', '大学', '私立', '東京都', '渋谷区', '', '', 0, '2013-01-03 00:00:00', '2013-01-03 00:00:00', '1', '1')

-- user addition
SELECT * FROM users WHERE id IN ('9510b510-a7d5-1296-ddd5-52b90f73881d', '882bfe28-9e80-42ad-7549-52b90de9252e', '4ffbf594-1885-a139-f78a-52b90b5faaf0', 
'6c22f9a7-d20a-6047-769c-52b90a021ad0', '1f0c5d56-8d67-0424-3c56-52b90805d556', '89f82f1c-6836-273f-d53a-52b8f4b602eb', 'e520fd12-57ae-c83a-a7c3-52b401a800b0')

SELECT * FROM users_schools WHERE user_id IN ('9510b510-a7d5-1296-ddd5-52b90f73881d', '882bfe28-9e80-42ad-7549-52b90de9252e', '4ffbf594-1885-a139-f78a-52b90b5faaf0', 
'6c22f9a7-d20a-6047-769c-52b90a021ad0', '1f0c5d56-8d67-0424-3c56-52b90805d556', '89f82f1c-6836-273f-d53a-52b8f4b602eb', 'e520fd12-57ae-c83a-a7c3-52b401a800b0')

SELECT * FROM acl_roles_users WHERE user_id IN ('9510b510-a7d5-1296-ddd5-52b90f73881d', '882bfe28-9e80-42ad-7549-52b90de9252e', '4ffbf594-1885-a139-f78a-52b90b5faaf0', 
'6c22f9a7-d20a-6047-769c-52b90a021ad0', '1f0c5d56-8d67-0424-3c56-52b90805d556', '89f82f1c-6836-273f-d53a-52b8f4b602eb', 'e520fd12-57ae-c83a-a7c3-52b401a800b0')

SELECT * FROM contacts WHERE id IN ('ad7d78bd-c9bc-b6ac-e1f0-52b8f7e60ca1', '54675bf8-7b63-36b0-353a-52b90a5c7d81', '10000db4-5c54-a3a7-40a9-52b90a759b21', 
	'ceb4d4de-d739-6edd-8210-52b90c14546f', 'ba89608e-7ffb-1a15-f93e-52b8f18ab202', '86fb7d55-aa02-6ad7-dc63-52b90e5cf099', 'd7f21c6c-e69c-0268-eebe-52b4012f73b5')
	
SELECT * FROM users_schools WHERE user_id IN (
'd72548a5-af2c-3a94-08e1-52cf6117e88f', 
'e53c5bc2-d0b9-06f3-3950-52cf5e81f1e8', 
'883931ad-be00-e7a2-3c0b-52cf5eb8254e', 
'1c9eede4-840a-17cd-c248-52cf5e38f96a', 
'b7263c2c-ca9b-74d7-46d4-52cf5de2f640', 
'25459ab4-446d-b9a7-1d01-52cf5d2dbe1f', 
'35dba3c0-d930-7926-e129-52cf5ba99a62', 
'17296c37-ba89-2966-21f5-52cf5bcbf773', 
'2e70b5be-759c-eead-f07d-52cf5afa80c9', 
'954f3980-0670-34fb-5d61-52cf59488681', 
'19be98aa-b4c7-0f1c-c11f-52cf59679d9a', 
'991fe012-e400-0564-d808-52cf5894a62c', 
'3f18804b-b477-683e-fc54-52cf5845ee83', 
'89e16115-6e15-1782-a24b-52cf574d0a17', 
'92ed7c3a-6843-f143-c935-52cf56c3c7af', 
'3cddb070-2500-6294-66fd-52cf558906cf', 
'5b948004-5efd-0fb8-a84d-52cf543c2df9', 
'9d4df3d1-e7bf-0f9b-ce67-52cf549961db', 
'c94b4391-c510-b0dc-454b-52cf538f7613', 
'833710f9-8f4e-c22c-8b6f-52cf51c35f41', 
'a665e773-2a58-af7c-4dce-52cf4f388e3a', 
'737b8bd6-d7d9-f897-24c9-52cf4afa115b', 
'aa479afb-e35c-b7bf-dc2a-52cf494bd354', 
'4c024b32-75a3-f9fe-f050-52cf48a798e6', 
'bd76feee-fda1-6cd2-dfd2-52cf480a93fd', 
'44316b8d-0178-d077-c690-52cf47587b20', 
'78c1663b-754c-f82a-d8b1-52cf425d4305'
)

SELECT * FROM contacts WHERE id IN (
'162008c2-ab9d-d485-318b-52cf5643d0c2', 
'48840550-b404-02ef-0f59-52cf5e158d6b', 
'6c13b21f-f44c-80a6-ef7d-52cf5ee2812e', 
'75f5946c-b3fd-346f-200d-52cf5ea6dbba', 
'f15601f5-13be-3742-fa9f-52cf5d468cce', 
'11142eb6-5976-3504-3fb7-52cf5c177904', 
'ab300239-29de-2c07-ecb1-52cf5bf09a5e', 
'2bdbd963-bb5f-caeb-eaff-52cf5aefb202', 
'd4ae4944-c65e-6855-9d64-52cf593c16da', 
'dcf34667-2ec9-9bbf-a849-52cf59ec2979', 
'85691da8-5442-72dc-83e6-52cf589d0b78', 
'8c22d25c-2556-eee2-746e-52cf58e9ccc3', 
'221cb72f-d85d-88d5-8dea-52cf57d31061', 
'85f009d1-1ce5-f9f0-d92d-52cf567721d5', 
'e91a0670-d45e-6131-811c-52cf56d843b5', 
'4a19d400-6936-1e0f-f3e6-52cf53228295', 
'12b5a4dc-a982-ec50-bd17-52cf54dfd4ad', 
'a22d06ca-0ba9-6811-b07b-52cf54c3ac5c', 
'6e4d36ce-28e1-b8c2-2078-52cf52d4a089', 
'41020df2-6444-c258-5273-52cf51ccb4d3', 
'c4dbdb1a-9e08-d9a0-4b27-52cf4e26efa7', 
'b6470f45-26b2-d6df-1247-52cf4a4a05da', 
'5ae6f325-e69a-c142-ef6e-52cf49d92694', 
'5c61ba8f-66ba-0d49-fb24-52cf48911aa9', 
'e2b1f8c9-1d13-981a-8ed2-52cf480a3613', 
'9641c167-a07d-6969-5532-52cf478ba9e0', 
'6cfadf1e-4c95-f3a5-a0de-52cf42fa77d2'
)



		
SELECT buppin.id, buppin_01.id FROM buppin, buppin_01 WHERE buppin.id=buppin_01.id
UPDATE buppin, buppin_01 SET buppin.deleted=2 WHERE buppin.id=buppin_01.id

SELECT buppin.id, buppin_deleted.id, buppin_deleted.changeid FROM buppin_deleted LEFT JOIN buppin ON buppin.rejiyou_barcode=buppin_deleted.rejiyou_barcode AND buppin.deleted=0
UPDATE buppin_deleted LEFT JOIN buppin ON buppin.rejiyou_barcode=buppin_deleted.rejiyou_barcode AND buppin.deleted=0 SET buppin_deleted.changeid=buppin.id

SELECT sms_fee.id, buppin_id, changeid FROM sms_fee INNER JOIN buppin_deleted ON sms_fee.buppin_id=buppin_deleted.id WHERE sms_fee.deleted=0
SELECT shukka_kobetsu_buppin.id, buppin_id, changeid FROM shukka_kobetsu_buppin INNER JOIN buppin_deleted ON shukka_kobetsu_buppin.buppin_id=buppin_deleted.id WHERE shukka_kobetsu_buppin.deleted=0

SELECT distinct CONCAT(NO_MASTER, NO_HASSO) FROM `m_hassogroup` WHERE FG_DELETE=0
SELECT distinct CONCAT(NO_MASTER, NO_HASSO) FROM `m_hassogroup` WHERE FG_DELETE=0 AND TD_SAKUSEI>='2010-11-01'

SELECT * FROM `m_kijunhasso` WHERE FG_DELETE=0 AND TD_SAKUSEI>='2010-11-01'

SELECT * FROM `m_kijunhasso` WHERE FG_DELETE=1 AND TD_SAKUSEI>='2010-11-01'
SELECT CONCAT('h-', NO_MASTER, '-', NO_KAMOKU, '-', NO_CLASS, '-', SU_KAI) FROM `m_kijunhasso` WHERE FG_DELETE=1 AND TD_SAKUSEI>='2010-11-01'

SELECT itojuku_import.hassobi.deleted,  itojuku_test.hassobi.deleted FROM itojuku_test.hassobi, itojuku_import.hassobi WHERE itojuku_test.hassobi.id=itojuku_import.hassobi.id AND itojuku_test.hassobi.deleted=1

UPDATE itojuku_test.hassobi, itojuku_import.hassobi SET itojuku_import.hassobi.deleted=itojuku_test.hassobi.deleted WHERE itojuku_test.hassobi.id=itojuku_import.hassobi.id AND itojuku_test.hassobi.deleted=1


CREATE TABLE IF NOT EXISTS `buppin_update` (
  `id` varbinary(36) NOT NULL,
  `buppin_name` varchar(100) DEFAULT NULL,
  `buppin_category` varchar(100) DEFAULT NULL,
  `seishikimeisho` varchar(100) DEFAULT NULL,
  `hanbai_tanka` double DEFAULT NULL,
  `teika` double DEFAULT NULL,
  `shainwari_tanka` double DEFAULT NULL,
  `version_up_tanka` double DEFAULT NULL,
  `if_code_buppin` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `if_code_buppin` (`if_code_buppin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


SELECT buppin.buppin_name, buppin_update.buppin_name FROM buppin, buppin_update WHERE buppin.id=buppin_update.id

UPDATE buppin, buppin_update SET buppin.buppin_code=buppin_update.buppin_code WHERE buppin.id=buppin_update.id; 
UPDATE buppin, buppin_update SET buppin.buppin_category=buppin_update.buppin_category WHERE buppin.id=buppin_update.id; 


UPDATE buppin, buppin_update SET buppin.seishikimeisho=buppin_update.seishikimeisho WHERE buppin.id=buppin_update.id; 
UPDATE buppin, buppin_update SET buppin.hanbai_tanka=buppin_update.hanbai_tanka WHERE buppin.id=buppin_update.id; 
UPDATE buppin, buppin_update SET buppin.teika=buppin_update.teika WHERE buppin.id=buppin_update.id; 
UPDATE buppin, buppin_update SET buppin.shainwari_tanka=buppin_update.shainwari_tanka WHERE buppin.id=buppin_update.id; 
UPDATE buppin, buppin_update SET buppin.version_up_tanka=buppin_update.version_up_tanka WHERE buppin.id=buppin_update.id; 
UPDATE buppin, buppin_update SET buppin.if_code_buppin=buppin_update.if_code_buppin WHERE buppin.id=buppin_update.id; 

SELECT NO_CLASS, SUM(FG_ICLASS) FROM tmp_class WHERE 1 GROUP BY NO_CLASS 
SELECT no_class, count(no_class) FROM `tmp_class` WHERE 1 GROUP BY no_class HAVING count(*) > 1


CREATE TABLE IF NOT EXISTS `smsevent_nethaishin` (
  `id` char(36) NOT NULL,
  `net_haishin_umu` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT smsevent.net_haishin_umu, smsevent_nethaishin.net_haishin_umu FROM smsevent, smsevent_nethaishin WHERE smsevent.id=smsevent_nethaishin.id AND smsevent.net_haishin_umu != smsevent_nethaishin.net_haishin_umu
UPDATE smsevent, smsevent_nethaishin SET smsevent.net_haishin_umu=smsevent_nethaishin.net_haishin_umu WHERE smsevent.id=smsevent_nethaishin.id AND smsevent.net_haishin_umu != smsevent_nethaishin.net_haishin_umu


UPDATE buppin SET deleted=1 WHERE id IN (
'9784863541290', 
'9784140883754', 
'9784569813318', 
'9784005006120', 
'9784272211050', 
'9784047101814', 
'9784758437295', 
'9784763410481', 
'9784763410368', 
'9784324096536', 
'9784322123968', 
'9784877984588', 
'9784779008047', 
'9784061498778', 
'9784772611329', 
'9784335355677', 
'9784335304804', 
'9784335304811', 
'9784335304859', 
'9784335304842', 
'9784335304866', 
'9784335304828', 
'9784335304873', 
'9784335304880', 
'9784335304835', 
'9784335305412', 
'9784335305429', 
'9784335305436', 
'9784335305450', 
'9784335305443', 
'9784335303531', 
'9784335303548', 
'9784335303555', 
'9784335303562', 
'9784335303579', 
'9784335303586', 
'9784335303593', 
'9784335312762', 
'9784335355615', 
'9784335314261', 
'9784335314223', 
'9784335314254', 
'9784335310140', 
'9784335310126', 
'9784335355684', 
'9784335312793', 
'9784335312823', 
'9784335312830', 
'9784335312816', 
'9784335312847', 
'9784335312663', 
'9784335312670', 
'9784335314018', 
'9784335312632', 
'9784335312656', 
'9784335312687', 
'9784335312649', 
'9784335312700', 
'9784335301858', 
'9784335301889', 
'9784335301865', 
'9784335301896', 
'9784335301872', 
'9784335304576', 
'9784335304545', 
'9784335355752', 
'9784335301674', 
'9784335304071', 
'9784335304088', 
'9784335304095', 
'9784335304101', 
'9784335312694', 
'9784335304057', 
'9784335304064', 
'9784335355110', 
'9784335355189', 
'9784385159577', 
'9784385321417', 
'9784385156989', 
'9784385159751', 
'9784385159669', 
'9784763198556', 
'9784763131966', 
'9784763132635', 
'9784763184955', 
'9784763184948', 
'9784788969742', 
'9784788969759', 
'9784788914148', 
'9784788914124', 
'9784788914162', 
'9784788914643', 
'9784087203998', 
'9784426120016', 
'9784426111694', 
'9784785717261', 
'9784785720421', 
'9784785721299', 
'9784785721275', 
'9784785721336', 
'9784785721305', 
'9784785721282', 
'9784785719050', 
'9784785718534', 
'9784785720056', 
'9784883841455', 
'9784883841424', 
'9784406053518', 
'9784792319625', 
'9784479792703', 
'9784796669375', 
'9784480067555', 
'9784806135715', 
'9784806135753', 
'9784806124528', 
'9784806146797', 
'9784806133445', 
'9784806136330', 
'9784130323680', 
'9784130323512', 
'9784130323321', 
'9784130323338', 
'9784130323109', 
'9784901510332', 
'9784860310882', 
'9784816353789', 
'9784816351440', 
'9784532691899', 
'9784822264376', 
'9784532407735', 
'9784532407704', 
'9784532407797', 
'9784532407711', 
'9784532407728', 
'9784532408039', 
'9784532408046', 
'9784532405861', 
'9784532407483', 
'9784532407476', 
'9784532407438', 
'9784532405069', 
'9784532407421', 
'9784532405052', 
'9784532406875', 
'9784532407742', 
'9784532407759', 
'9784532407766', 
'9784532407605', 
'9784534047205', 
'9784534043504', 
'9784820717676', 
'9784535519718', 
'9784535517912', 
'9784535518308', 
'9784535517394', 
'9784535517363', 
'9784535517349', 
'9784535517370', 
'9784535517332', 
'9784535517387', 
'9784535517356', 
'9784535519954', 
'9784535518117', 
'9784535514935', 
'9784535517899', 
'9784535519015', 
'9784535516670', 
'9784535516144', 
'9784535408296', 
'9784535518063', 
'9784535517882', 
'9784535408241', 
'9784535408265', 
'9784535408289', 
'9784535516755', 
'9784797225976', 
'9784891861704', 
'9784924533714', 
'9784582851922', 
'9784587233235', 
'9784587233242', 
'9784587999896', 
'9784587233259', 
'9784587233105', 
'9784587233150', 
'9784587999919', 
'9784587233266', 
'9784587233112', 
'9784587233167', 
'9784587999926', 
'9784587233273', 
'9784587233129', 
'9784587233174', 
'9784587222901', 
'9784587222765', 
'9784587222666', 
'9784587222963', 
'9784587222956', 
'9784587222857', 
'9784587222710', 
'9784587222741', 
'9784587532000', 
'9784587532055', 
'9784587534288', 
'9784587534295', 
'9784587613365', 
'9784587422950', 
'9784587422851', 
'9784587422547', 
'9784587422905', 
'9784587422806', 
'9784587421205', 
'9784587233556', 
'9784587233525', 
'9784587233334', 
'9784587233389', 
'9784587233457', 
'9784587233471', 
'9784587233433', 
'4910051471031', 
'4910051471130', 
'4910051471239', 
'4910051470140', 
'9784587234416', 
'9784587220457', 
'9784587220501', 
'9784587220556', 
'9784587220600', 
'9784587220655', 
'9784587220709', 
'9784587220754', 
'9784587232054', 
'9784905432517', 
'9784905432524', 
'9784905432531', 
'9784905432548', 
'9784905432555', 
'9784344980723', 
'9784344016231', 
'9784896288599', 
'9784896288605', 
'9784896283549', 
'9784634590670', 
'9784641115842', 
'9784641115859', 
'9784641115866', 
'9784641115873', 
'9784641009141', 
'9784641179110', 
'9784641179189', 
'9784641114937', 
'9784641115057', 
'9784641115033', 
'9784641042759', 
'9784641114890', 
'9784641114906', 
'9784641130975', 
'9784641091580', 
'9784641115170', 
'9784641115187', 
'9784641136090', 
'9784641115118', 
'9784641115125', 
'9784641042858', 
'9784641042827', 
'9784641136113', 
'9784641042797', 
'9784641136441', 
'9784641114739', 
'9784641136274', 
'9784641136557', 
'9784641136243', 
'9784641114944', 
'9784641125544', 
'9784641134911', 
'9784641134225', 
'9784641134232', 
'9784641115163', 
'9784641042773', 
'9784641144088', 
'9784641003347', 
'9784641004146', 
'9784641115019', 
'9784641135185', 
'9784641134515', 
'9784641114951', 
'9784641114968', 
'9784641131446', 
'9784641114975', 
'9784903722290')

-- 2014-02-13  dairitenuketsukebi henko

SELECT uketsukebi, TD_DAIRITENUKETSUKE, NO_URIAGE FROM t_kozamoshikomi, t_kozauriage1
WHERE t_kozamoshikomi.YY_NENDO=t_kozauriage1.YY_NENDO AND t_kozamoshikomi.KB_KISETSU=t_kozauriage1.KB_KISETSU AND t_kozamoshikomi.NO_COURSE=t_kozauriage1.NO_COURSE 
	AND t_kozamoshikomi.NO_KOZASEQ=t_kozauriage1.NO_SEQ AND t_kozamoshikomi.NO_KAIJUKU=t_kozauriage1.NO_KAIJUKU AND t_kozamoshikomi.NO_YOBI=t_kozauriage1.NO_YOBI 
	AND t_kozamoshikomi.NO_KAIIN=t_kozauriage1.NO_KAIIN AND TD_DAIRITENUKETSUKE!='0000-00-00 00:00:00'
	
UPDATE t_kozamoshikomi, t_kozauriage1 SET  uketsukebi=TD_DAIRITENUKETSUKE 
WHERE t_kozamoshikomi.YY_NENDO=t_kozauriage1.YY_NENDO AND t_kozamoshikomi.KB_KISETSU=t_kozauriage1.KB_KISETSU AND t_kozamoshikomi.NO_COURSE=t_kozauriage1.NO_COURSE 
	AND t_kozamoshikomi.NO_KOZASEQ=t_kozauriage1.NO_SEQ AND t_kozamoshikomi.NO_KAIJUKU=t_kozauriage1.NO_KAIJUKU AND t_kozamoshikomi.NO_YOBI=t_kozauriage1.NO_YOBI 
	AND t_kozamoshikomi.NO_KAIIN=t_kozauriage1.NO_KAIIN AND TD_DAIRITENUKETSUKE!='0000-00-00 00:00:00'
	
SELECT dairiten_uketsukebi, uketsukebi, deleted FROM sms_transaction, t_kozauriage1
WHERE sms_transaction.salesno=t_kozauriage1.NO_URIAGE AND deleted=0
AND dairiten_uketsukebi=uketsukebi

SELECT dairiten_uketsukebi, uketsukebi, deleted, YY_NENDO
FROM t_kozauriage1 LEFT JOIN sms_transaction ON sms_transaction.salesno=t_kozauriage1.NO_URIAGE
WHERE dairiten_uketsukebi IS NULL 

UPDATE sms_transaction, t_kozauriage1 SET dairiten_uketsukebi=uketsukebi
WHERE sms_transaction.salesno=t_kozauriage1.NO_URIAGE 

 -- total 282,083 updated & all have date_modified='2014-01-03 00:00:00', dairiten_uketsukebi IS NULL=154,672  (dairiten_uketsukebi=uketsukebi=114,416)
 
 
-- 2015-06-10 classes with more than one instances with the same instance order
 
SELECT parent_id, from_datetime FROM sms_event_instance, sms_event_instance_sub WHERE id=instance_id 
	AND parent_id='3846382d-21bb-3de6-74e9-54377134b534' 
GROUP BY ins_order 
HAVING count(ins_order) > 1


SELECT parent_id, instance_id, event_type, status, from_datetime FROM sms_event_instance, sms_event_instance_sub WHERE id=instance_id 
GROUP BY parent_id, ins_order 
HAVING count(ins_order) > 1


SELECT distinct parent_id FROM sms_event_instance, sms_event_instance_sub WHERE id=instance_id 
GROUP BY parent_id, ins_order 
HAVING count(ins_order) > 1