TRUNCATE `accounts`;
TRUNCATE `accountsaccount_cstm`;
TRUNCATE `accounts_audit`;
TRUNCATE `accounts_bugs`;
TRUNCATE `accounts_cases`;
TRUNCATE `accounts_contacts`;
TRUNCATE `accounts_opportunities`;
TRUNCATE `bankactions_history`;
TRUNCATE `bankinfo_history`;
TRUNCATE `bank_account`;
TRUNCATE `bugs`;
TRUNCATE `bugs_audit`;
TRUNCATE `calls`;
TRUNCATE `calls_contacts`;
TRUNCATE `calls_users`;
TRUNCATE `campaigns`;
TRUNCATE `campaigns_audit`;
TRUNCATE `campaign_log`;
TRUNCATE `cases`;
TRUNCATE `cases_audit`;
TRUNCATE `cases_bugs`;
TRUNCATE `contacts_audit`;
TRUNCATE `contacts_bugs`;
TRUNCATE `contacts_cases`;
TRUNCATE `contacts_users`;
TRUNCATE `course_categories_contacts`;
TRUNCATE `currencies`;
TRUNCATE `dashboards`;
TRUNCATE `documents`;
TRUNCATE `document_revisions`;
TRUNCATE `emailman`;
TRUNCATE `emailman_sent`;
TRUNCATE `emails_cases`;
TRUNCATE `emails_opportunities`;
TRUNCATE `emails_users`;
TRUNCATE `email_marketing`;
TRUNCATE `email_marketing_prospect_lists`;
TRUNCATE `feeds`;
TRUNCATE `files`;
TRUNCATE `forums`;
TRUNCATE `forumtopics`;
TRUNCATE `homework`;
TRUNCATE `iframes`;
TRUNCATE `import_maps`;
TRUNCATE `inbound_email_autoreply`;
TRUNCATE `leads`;
TRUNCATE `leads_audit`;
TRUNCATE `leads_cstm`;
TRUNCATE `meetings`;
TRUNCATE `meetings_contacts`;
TRUNCATE `meetings_users`;
TRUNCATE `memo`;
TRUNCATE `notes`;
TRUNCATE `opportunities`;
TRUNCATE `opportunities_audit`;
TRUNCATE `opportunities_contacts`;
TRUNCATE `posts`;
TRUNCATE `product`;
TRUNCATE `productcategory`;
TRUNCATE `project`;
TRUNCATE `project_relation`;
TRUNCATE `project_task`;
TRUNCATE `project_task_audit`;
TRUNCATE `prospects`;
TRUNCATE `prospects_cstm`;
TRUNCATE `prospect_lists`;
TRUNCATE `prospect_lists_prospects`;
TRUNCATE `prospect_list_campaigns`;
TRUNCATE `releases`;
TRUNCATE `schedulers_times`;
TRUNCATE `session`;
TRUNCATE `smsevent_instances_backup`;
TRUNCATE `smsgroups`;
TRUNCATE `smsgroups_module_action`;
TRUNCATE `sms_document`;
TRUNCATE `sms_event_instance_backup`;
TRUNCATE `sms_fee`;
TRUNCATE `sms_recurrence`;
TRUNCATE `sms_recurrence_templates`;
TRUNCATE `sms_salesgroup`;
TRUNCATE `sms_syllabus`;
TRUNCATE `sms_transaction`;
TRUNCATE `tasks`;
TRUNCATE `threads`;
TRUNCATE `tracker`;
TRUNCATE `upgrade_history`;
TRUNCATE `users_feeds`;
TRUNCATE `users_last_import`;




DELETE FROM `contacts` WHERE `school_id` NOT IN ( 'sssssss-1111', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' );
DELETE FROM `course_category` WHERE `school_id` NOT IN ( 'sssssss-1111', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' );
DELETE FROM `smsevent` WHERE `school_id` NOT IN ( 'sssssss-1111', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' );

DELETE FROM resource WHERE id  NOT IN ( 'sssssss-1111', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' );
DELETE FROM resourceschool_cstm  WHERE id_c NOT IN ( 'sssssss-1111', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' );
DELETE FROM users WHERE id NOT IN ('c4565fc9-30f9-719c-cb31-4437354e807d', '1', 'ef256ecf-d1c4-c18a-6ade-46cb935f01ae', '12ea7738-ba68-9c80-cae5-49ac8d2df419', 'e75907ac-f94c-1f1b-9f9d-45511d257460', '8161e46d-f6ee-1ff0-16e0-4d7dba246628' );
DELETE FROM users_schools WHERE user_id  NOT IN ('c4565fc9-30f9-719c-cb31-4437354e807d', '1', 'ef256ecf-d1c4-c18a-6ade-46cb935f01ae', '12ea7738-ba68-9c80-cae5-49ac8d2df419', 'e75907ac-f94c-1f1b-9f9d-45511d257460', '8161e46d-f6ee-1ff0-16e0-4d7dba246628' );
DELETE FROM acl_roles_users WHERE user_id  NOT IN ('c4565fc9-30f9-719c-cb31-4437354e807d', '1', 'ef256ecf-d1c4-c18a-6ade-46cb935f01ae', '12ea7738-ba68-9c80-cae5-49ac8d2df419', 'e75907ac-f94c-1f1b-9f9d-45511d257460', '8161e46d-f6ee-1ff0-16e0-4d7dba246628' );
 

-- updating idnumber for branches; note that teacher (staff) id may change according to the length of idnumber (below is for 5 digit)
-- UPDATE resourceschool_cstm SET last_student_id_c='0', last_invoice_id_c='0', last_teacher_id_c='90000' WHERE 1;

** TRUNCATE `category_tree`;
** TRUNCATE `contacts_sms_memo`;
** TRUNCATE `emails`;
** TRUNCATE `emails_contacts`;
** TRUNCATE `email_templates`;
** TRUNCATE `event_contacts`;
** TRUNCATE `event_resource`;
** TRUNCATE `person_contacts`;
** TRUNCATE `smsevent_instances`;
** TRUNCATE `sms_event_instance`;
** TRUNCATE `sms_memo`;

'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4'

-- 2011-07-30 honban data iko preperation

DELETE  FROM `contacts` WHERE id NOT IN ('be59a6ae-2ceb-667f-5ba0-45b03573f1ac', '675da30e-42ab-e026-72d3-4d905c10060c', 'c4565fc9-30f9-719c-cb31-4437354e807d', '914d3e44-978a-5730-68ae-49ac8d15cb87', '18836fb2-ffd7-3122-0f5f-4d7db9945a53');

DELETE FROM users_schools WHERE user_id NOT IN ('1', 'cbf88f91-3d6e-bb40-68e0-4d905c198e75', 'c4565fc9-30f9-719c-cb31-4437354e807d', '12ea7738-ba68-9c80-cae5-49ac8d2df419', 'e75907ac-f94c-1f1b-9f9d-45511d257460', '8161e46d-f6ee-1ff0-16e0-4d7dba246628');
DELETE FROM acl_roles_users WHERE user_id NOT IN ('1', 'cbf88f91-3d6e-bb40-68e0-4d905c198e75', 'c4565fc9-30f9-719c-cb31-4437354e807d', '12ea7738-ba68-9c80-cae5-49ac8d2df419', 'e75907ac-f94c-1f1b-9f9d-45511d257460', '8161e46d-f6ee-1ff0-16e0-4d7dba246628');
DELETE FROM acl_roles_users WHERE user_id NOT IN ('1', 'cbf88f91-3d6e-bb40-68e0-4d905c198e75', 'c4565fc9-30f9-719c-cb31-4437354e807d', '12ea7738-ba68-9c80-cae5-49ac8d2df419', 'e75907ac-f94c-1f1b-9f9d-45511d257460', '8161e46d-f6ee-1ff0-16e0-4d7dba246628');


SELECT id, idnumber FROM `contacts` WHERE smsgroup='student' AND idnumber IS NOT NULL AND idnumber!='';
UPDATE contacts SET idnumber= CONCAT('010' , idnumber) WHERE smsgroup='student' AND idnumber IS NOT NULL AND idnumber!='';

UPDATE `fis`.`contacts` SET `primary_address_city` = 'Momochihama Sawara-ku ' WHERE `contacts`.`id` = 'student_103' LIMIT 1 ;
UPDATE `fis`.`contacts` SET `phone_mobile` = '080-2741-3291' WHERE `contacts`.`id` = 'student_119' LIMIT 1 ;
UPDATE `fis`.`contacts` SET `primary_address_city` = 'Momochihama' WHERE `contacts`.`id` = 'Barkley' LIMIT 1 ;

UPDATE `fis`.`contacts` SET `email1` = 'marikolamlam@hotmail.com', `description` = 'marikolamlam@hotmail.com (mother)' WHERE `contacts`.`id` = 'student_094' LIMIT 1 ;
UPDATE `fis`.`contacts` SET `email1` = 'kumiaes@earthlink.net', `email2` = 'bobservetter@hotmail.com',
`description` = 'kumiaes@earthlink.net (mother) bobservetter@hotmail.com (father)' WHERE `contacts`.`id` = 'student_100' LIMIT 1 ;
UPDATE `fis`.`contacts` SET `email1` = 'barkley@seinan-gu.ac.jp', `email2` = 'carolyndesu@hotmail.com',
`description` = 'barkley@seinan-gu.ac.jp (father) carolyndesu@hotmail.com (mother)' WHERE `contacts`.`id` = 'student_103' LIMIT 1 ;
UPDATE `fis`.`contacts` SET `email1` = '', `description` = 'tominaga-08zj:jp.nomura.com' WHERE `contacts`.`id` = 'student_013' LIMIT 1 ;


-- on test site to clean up course and schedule data
TRUNCATE `category_tree`;
TRUNCATE `course_categories_contacts`;
TRUNCATE `course_category`;
TRUNCATE `event_contacts`;
TRUNCATE `event_resource`;
TRUNCATE `homework`;
TRUNCATE `meetings`;
TRUNCATE `meetings_contacts`;
TRUNCATE `meetings_users`;
TRUNCATE `smsevent`;
TRUNCATE `smsevent_instances`;
TRUNCATE `sms_event_instance_backup`;
TRUNCATE `smsevent_instances`;
TRUNCATE `smsevent_instances_backup`;

-- 2011-08-02 import shusei
SELECT `phone_fax` FROM `contacts` WHERE smsgroup='student';
UPDATE contacts SET phone_fax=NULL WHERE smsgroup='student';

UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE CONVERT( `sms_transactiontype`.`id` USING utf8 ) = '27' LIMIT 1 ;
UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE CONVERT( `sms_transactiontype`.`id` USING utf8 ) = '28' LIMIT 1 ;
UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE CONVERT( `sms_transactiontype`.`id` USING utf8 ) = '5' LIMIT 1 ;
UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE CONVERT( `sms_transactiontype`.`id` USING utf8 ) = '6' LIMIT 1 ;
UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE CONVERT( `sms_transactiontype`.`id` USING utf8 ) = '17' LIMIT 1 ;
UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE CONVERT( `sms_transactiontype`.`id` USING utf8 ) = '8' LIMIT 1 ;
UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE CONVERT( `sms_transactiontype`.`id` USING utf8 ) = '9' LIMIT 1 ;

SELECT DISTINCT (nationality) FROM `contacts` WHERE LENGTH( nationality ) >15 ;

ALTER TABLE `contacts` CHANGE `nationality` `nationality` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;
UPDATE contacts SET nationality='American/Moldavian' WHERE nationality='American/Moldavi' ;
UPDATE contacts SET nationality='Japanese/American' WHERE nationality='Japanese/America' ;
UPDATE contacts SET nationality='Japanese/Australian' WHERE nationality='Japanese/Austral' ;
UPDATE contacts SET nationality='Japanese/Canadian' WHERE nationality='Japanese/Canadia' ;
UPDATE contacts SET nationality='Australian/British' WHERE nationality='Australian/Briti' ;

SELECT last_name, idnumber FROM contacts WHERE school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' AND deleted=0 AND smsgroup='student'

SELECT c1.idnumber, c1.custom_var4, c2.idnumber, c2.custom_var4 FROM contacts c1, contacts_update c2 WHERE c1.id=c2.id

UPDATE contacts, contacts_update SET contacts.idnumber=contacts_update.idnumber, contacts.custom_var4=contacts_update.custom_var4 WHERE contacts.id=contacts_update.id

SELECT last_name, idnumber FROM contacts WHERE school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' AND deleted=0 AND smsgroup='student'

SELECT id, last_name, first_name, send_document_home, send_document_work,custom_var11, custom_var12
FROM `contacts` WHERE `smsgroup` LIKE 'parent' AND `gender` LIKE 'female' AND id!='Gauntlett'

UPDATE contacts SET send_document_home='off', send_document_work='on' WHERE `smsgroup` LIKE 'parent' AND `gender` LIKE 'female' AND id!='Gauntlett'

UPDATE contacts SET send_document_home='on' WHERE `smsgroup` LIKE 'parent' AND `gender` LIKE 'male' AND custom_var11='Yes';
UPDATE contacts SET send_document_home='off' WHERE `smsgroup` LIKE 'parent' AND `gender` LIKE 'male' AND custom_var11='No';

UPDATE contacts SET send_document_work='on' WHERE `smsgroup` LIKE 'parent' AND `gender` LIKE 'male' AND custom_var12='Yes';
UPDATE contacts SET send_document_work='off' WHERE `smsgroup` LIKE 'parent' AND `gender` LIKE 'male' AND custom_var12='No';

UPDATE contacts SET send_document_home='on', send_document_work='off' WHERE `smsgroup` LIKE 'parent' ;
UPDATE contacts SET custom_var11='Yes', custom_var12='No' WHERE `smsgroup` LIKE 'parent' 
	AND id IN ('Yoo', 'Nosaka', 'Ryu', 'Tanaka', 'Sakamoto', 'Manning', 'Shinohara', 'Inoue')

UPDATE `fis`.`contacts` SET `id` = 'Kajiwara' WHERE `contacts`.`id` = 'Kajiwara\r\n' LIMIT 1 ;
UPDATE `fis`.`contacts` SET `id` = 'Han' WHERE `contacts`.`id` = 'Han\r\n' LIMIT 1 ;
UPDATE `fis`.`contacts` SET `id` = 'Kunitomo' WHERE `contacts`.`id` = 'Kunitomo\r\n' LIMIT 1 ;

SELECT id, last_name, first_name, idnumber, primary_address_street
FROM `contacts` WHERE school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' AND deleted=0 
	AND primary_address_street IS NOT NULL AND smsgroup='student' 
ORDER BY `contacts`.`id` ASC

UPDATE contacts, contacts_update2 SET contacts.primary_address_street=contacts_update2.primary_address_street WHERE contacts.id=contacts_update2.id; 

UPDATE `smsevent` SET custom_var36='2011-2012' WHERE `subtype` =4 AND deleted=0;

UPDATE contacts SET custom_var10=email1 WHERE smsgroup='student' AND school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' ;
UPDATE contacts SET email1=email2 WHERE smsgroup='student' AND school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' ;
UPDATE contacts SET email2=custom_var10 WHERE smsgroup='student' AND school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' ;
UPDATE contacts SET custom_var10=NULL WHERE smsgroup='student' AND school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4' ;


SELECT last_name, first_name, idnumber, primary_address_city, primary_address_city_new FROM contacts, contacts_a WHERE idnumber=idnumber_new ;
UPDATE contacts, contacts_a SET primary_address_city=primary_address_city_new WHERE idnumber=idnumber_new ;

SELECT last_name, first_name, id, primary_address_city, primary_address_city_new FROM contacts, contacts_p WHERE id=id_new ;
UPDATE contacts, contacts_p SET primary_address_city=primary_address_city_new WHERE id=id_new ;

SELECT primary_address_city_new, LENGTH(primary_address_city_new) FROM `contacts_a` WHERE LENGTH(primary_address_city_new)=32;

-- seiseki related
		'Poll' =>	'SeisekiTemplate',
		'PollQuestion' =>	'SeisekiItem',
		'SeisekiTemplate' =>	'Grading Template',
		'SeisekiItem' =>	'Grading Item',
		'SeisekiTemplate' =>	'成績テンプレート',
		'SeisekiItem' =>	'成績項目',
		
-- course-teacher list for seiseki mappings
-- for primary and kindergarden
SELECT smsevent.id as course_id, course_category.name as category_name, smsevent.name as course_name, 
	smsevent.custom_var38 as subject, smsevent.custom_var39 as ib, smsevent.custom_var40 as ib_type, 
	CONCAT_WS(' ', contacts.last_name, contacts.first_name) AS teacher_name
FROM smsevent INNER JOIN course_category ON course_category.id=smsevent.parent_id AND course_category.deleted=0
INNER JOIN event_contacts ON event_contacts.event_id=smsevent.id AND event_contacts.deleted=0 
INNER JOIN contacts ON event_contacts.contact_id=contacts.id AND contacts.deleted=0 AND contacts.smsgroup='teacher' 
WHERE smsevent.deleted=0 AND smsevent.category='course' AND smsevent.school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4'
ORDER BY category_name, course_name

-- get grade template associated for each course
SELECT smsevent.name as course_name, 	CONCAT_WS(' ', contacts.last_name, contacts.first_name) AS teacher_name,
	seiseki_template.name as seiseki_template, course_category.name as category_name, smsevent.id as course_id
FROM smsevent INNER JOIN course_category ON course_category.id=smsevent.parent_id AND course_category.deleted=0
INNER JOIN event_contacts ON event_contacts.event_id=smsevent.id AND event_contacts.deleted=0 
INNER JOIN contacts ON event_contacts.contact_id=contacts.id AND contacts.deleted=0 AND contacts.smsgroup='teacher' 
INNER JOIN course_seisekitemplates ON course_seisekitemplates.course_id=smsevent.id AND course_seisekitemplates.deleted=0
INNER JOIN seiseki_template ON course_seisekitemplates.template_id=seiseki_template.id AND seiseki_template.deleted=0
WHERE smsevent.deleted=0 AND smsevent.category='course' AND smsevent.school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4'
ORDER BY category_name, course_name, course_seisekitemplates.template_order ASC

SELECT seiseki_template.name as seiseki_template, seiseki_item.name as seiseki_item, item_type, st_category as template_category
FROM seiseki_item
INNER JOIN seiseki_template ON seiseki_template.id=seiseki_item.template_id
WHERE seiseki_item.deleted=0
ORDER BY template_id, item_order ASC;

-- schedule import tests
DELETE FROM `smsevent` WHERE id='d0b568ec';
DELETE FROM `sms_recurrence` WHERE id='d0b568ec';
DELETE FROM `sms_event_instance` WHERE parent_id='d0b568ec';
DELETE FROM `smsevent_instances` WHERE event_id='d0b568ec';
DELETE FROM `event_contacts` WHERE event_id='d0b568ec';

SELECT smsevent.name, smsevent.id, smsevent.category
FROM event_contacts, smsevent WHERE smsevent.id=event_contacts.event_id AND smsevent.deleted=0 
	AND smsevent.category='course' AND event_contacts.deleted=0 
	
DELETE event_contacts.* FROM event_contacts, smsevent WHERE smsevent.id=event_contacts.event_id AND smsevent.category='course' ;

DELETE event_resource.* FROM event_resource, smsevent WHERE smsevent.id=event_resource.event_id AND smsevent.category='course' ;

DELETE sms_recurrence.* FROM sms_recurrence, smsevent WHERE smsevent.id=sms_recurrence.id AND smsevent.category='course' ;

DELETE sms_event_instance.* FROM sms_event_instance, smsevent WHERE smsevent.id=sms_event_instance.parent_id AND smsevent.category='course' ;

DELETE smsevent_instances.* FROM smsevent_instances, smsevent WHERE smsevent.id=smsevent_instances.event_id AND smsevent.category='course' ;

DELETE smsevent.* FROM smsevent WHERE smsevent.category='course' ;
TRUNCATE `category_tree`;
TRUNCATE `course_categories_contacts`;
TRUNCATE `course_category`;

UPDATE contacts SET custom_var4=custom_var6 WHERE smsgroup='student'
UPDATE contacts SET custom_var6=NULL WHERE smsgroup='student'

-- FICS data import
DELETE FROM `contacts` WHERE `id` NOT IN ('be59a6ae-2ceb-667f-5ba0-45b03573f1ac');
DELETE FROM users_schools WHERE user_id  NOT IN ('c4565fc9-30f9-719c-cb31-4437354e807d', '1', 'ef256ecf-d1c4-c18a-6ade-46cb935f01ae', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b');
-- DELETE FROM users_schools WHERE school_id  NOT IN ('sssssss-1111',);
DELETE FROM acl_roles_users WHERE user_id  NOT IN ('c4565fc9-30f9-719c-cb31-4437354e807d', '1', 'ef256ecf-d1c4-c18a-6ade-46cb935f01ae', 'a437cc11-564c-e5c4-3ff0-4c88c6961e3b');
DELETE FROM resource WHERE id NOT IN ('sssssss-1111'); 

-- FICS data import 

-- admin user only
-- 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`, `custom_var1`, `custom_var2`, `custom_var3`, `custom_var4`, `custom_var5`, `custom_var6`, `custom_var7`, `custom_var8`, `custom_var9`, `custom_var10`, `custom_var11`, `custom_var12`, `custom_var13`, `custom_var14`, `custom_var15`, `custom_var16`, `custom_var17`, `custom_var18`, `custom_var19`, `custom_var20`) VALUES
('be59a6ae-2ceb-667f-5ba0-45b03573f1ac', 1, '2007-01-19 03:05:26', '2007-01-19 03:05:33', '1', '1', '1', NULL, NULL, 'Admin', NULL, 'Dummy', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, 'contact', '0', NULL, NULL, 'sssssss-1111', NULL, NULL, 0, NULL, 'off', 'off', NULL, NULL, 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0.1, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', '0');

SELECT phone_fax, phone_home FROM contacts WHERE phone_fax='TRUE' ;
UPDATE contacts SET phone_fax=phone_home WHERE phone_fax='TRUE';

-- adult class student (2)

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`, `custom_var1`, `custom_var2`, `custom_var3`, `custom_var4`, `custom_var5`, `custom_var6`, `custom_var7`, `custom_var8`, `custom_var9`, `custom_var10`, `custom_var11`, `custom_var12`, `custom_var13`, `custom_var14`, `custom_var15`, `custom_var16`, `custom_var17`, `custom_var18`, `custom_var19`, `custom_var20`) VALUES
('fics_student_609', 0, '2011-10-31 06:00:00', '2005-01-01 00:00:00', '1', NULL, '1', NULL, 'Akiko', 'Akiyoshi', '', 'ｱｷﾖｼ ｱｷｺ', NULL, NULL, NULL, NULL, NULL, NULL, '0000-00-00', 'off', '', NULL, NULL, NULL, '', NULL, NULL, NULL, '', 'off', '', '', '', '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Mao　Yuki　Akiyoshi', NULL, 0, NULL, 0, 'student', '0', NULL, NULL, 'sssssss-1111', NULL, NULL, 0, NULL, 'on', 'off', NULL, '35', NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, 0.1, NULL, '2011-06-07', '', '', '0000-00-00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', '', 'TRUE', '', '', '', '', 'FALSE', NULL, NULL, NULL, '', 'Adult', NULL, NULL, NULL, '', NULL, NULL, '0', '0'),
('fics_student_610', 0, '2011-10-31 06:00:00', '2005-01-01 00:00:00', '1', NULL, '1', NULL, 'Fumiko', 'Kitashiro', '', 'ｷﾀｼﾛ ﾌﾐｺ', NULL, NULL, NULL, NULL, NULL, NULL, '0000-00-00', 'off', '', NULL, NULL, NULL, '', NULL, NULL, NULL, '', 'off', '', '', '', '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Risako Takizawa', NULL, 0, NULL, 0, 'student', '0', NULL, NULL, 'sssssss-1111', NULL, NULL, 0, NULL, 'on', 'off', NULL, '112', NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, 0.1, NULL, '2011-05-31', '', '', '0000-00-00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', '', 'TRUE', '', '', '', '', 'FALSE', NULL, NULL, NULL, '', 'Adult', NULL, NULL, NULL, '', NULL, NULL, '0', '0');

-- course categories
354349de-0128-ed0c-3383-4eaf0df85119 	402 	Kids
e8fbc67f-7253-18e1-228b-4eaf0d7b8d89 	403 	Kindy
11d24fbd-cc8b-b98c-fa35-4eaf0dc71969 	404 	Returnees
499a099e-327e-518f-ab36-4eaf0dc8c911 	405 	Yound Adult
12117d2d-f8b6-0fff-110b-4eaf0db059d9 	406 	Adult

-- updating description field
-- UPDATE contacts SET NOTES5=CONCAT("\nセゾンID: ", saison_id) WHERE saison_id IS NOT NULL AND saison_id !='' ;

SELECT description, CONCAT(description, "\n11: ", custom_var1, "\nNew tuition: ", custom_var2, " Instalments: ", custom_var7, "\nPrice per class: ", custom_var3, " Amount: ", custom_var4, " Paid on: ", custom_var5, " Paid: ", custom_var6) FROM contacts WHERE id='fics_student_001'

UPDATE contacts SET description=CONCAT(description, "\n11: ", custom_var1, "\nNew tuition: ", custom_var2, " Instalments: ", custom_var7, "\nPrice per class: ", custom_var3, " Amount: ", custom_var4, " Paid on: ", custom_var5, " Paid: ", custom_var6) WHERE id!='fics_student_001' AND id!='be59a6ae-2ceb-667f-5ba0-45b03573f1ac';

UPDATE contacts SET custom_var8=custom_var3, custom_var9=custom_var4 WHERE id !='1';

UPDATE contacts SET custom_var1=NULL, custom_var2=NULL, custom_var3=NULL, custom_var4=NULL, custom_var5=NULL, custom_var6=NULL, custom_var7=NULL
WHERE id!='1'

SELECT custom_var7, custom_var13, custom_var7 FROM contacts WHERE id!='1' AND custom_var7!='TRUE' AND custom_var7!='FALSE'

SELECT contact_i3.custom_var13, contacts_ib2.custom_var13 FROM contact_i3, contacts_ib2 WHERE contact_i3.id=contacts_ib2.id

UPDATE contact_i3, contacts_ib2 SET contact_i3.custom_var13=contacts_ib2.custom_var13 WHERE contact_i3.id=contacts_ib2.id

SELECT event_contacts.event_id, smsevent.name, smsevent.id FROM event_contacts, smsevent WHERE smsevent.name=event_contacts.event_id

UPDATE event_contacts, smsevent SET event_contacts.event_id=smsevent.id  WHERE smsevent.name=event_contacts.event_id;

SELECT id, DISTINCT event_id, contact_id FROM event_contacts_teacher WHERE 1


-- new seiseki templates and correspondin seiseki items for high school

INSERT INTO `seiseki_template` (`id`, `name`, `st_category`, `issue_timing`, `overall_grade_type`, `target_type`, `template_order`, `output_template_name`, `description`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `school_id`, `deleted`) VALUES
('high_subject02', 'High School Grading 1-7', 'high_school_report_card', 'quarter_semester', NULL, 'homeroom', 1, NULL, NULL, '2011-10-08 00:00:00', '2011-10-30 15:00:38', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('high_subject03', 'High School Grading P-NC', 'high_school_report_card', 'quarter_semester', NULL, 'homeroom', 1, NULL, NULL, '2011-10-08 00:00:00', '2011-10-30 15:00:38', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('high_subject04', 'High School Grading Effort', 'high_school_report_card', 'quarter_semester', NULL, 'homeroom', 1, NULL, NULL, '2011-10-08 00:00:00', '2011-10-30 15:00:38', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0);

INSERT INTO `seiseki_item` (`id`, `template_id`, `name`, `item_type`, `item_order`, `item_weight`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`) VALUES
('high_grading03', 'high_subject02', 'Grade', '1-7', 1, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('high_grading04', 'high_subject02', 'Comment', 'expectation', 2, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('high_grading05', 'high_subject03', 'Grade', 'P-NC', 1, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('high_grading06', 'high_subject03', 'Comment', 'expectation', 2, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('high_grading07', 'high_subject04', 'Grade', 'expectation', 2, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1');

UPDATE `course_classes` SET `class_id` = 'prekinder' WHERE `course_classes`.`id` = 'course_class_008' LIMIT 1 ;

INSERT INTO `sms_class` 
(`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `school_id`, `school_year`, `grade`, `subtype`, `description`) VALUES
('grade10', 0, '2011-11-21 00:00:00', '2011-11-21 00:00:00', '1', '1', 'Grade 10', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2011-2012', '15', 'high', NULL);
INSERT INTO `course_classes` (`id`, `event_id`, `class_id`, `date_modified`, `deleted`) VALUES
('course_class_101', '41e3d105-04b2-2b2e-717b-4e77eb079778', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_102', 'd2315805-89eb-bc37-0eb4-4e68970fc1ea', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_103', '9920087d-0d91-eef6-f151-4e6897514e63', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_104', '5d4e8044-c5b5-1987-050a-4e6897a53ab2', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_105', '4afd1255-7816-765e-048b-4e6897a7b691', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_106', 'b6696aab-09c5-6640-7169-4e689759ab0e', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_107', '90241527-222f-7d47-05b2-4e68974d1d4f', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_108', 'f1a4966a-8de8-ad32-4450-4e68979efa4b', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_109', '24d3ad49-0255-465c-7c33-4e68977016b3', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_110', '6389dcab-84ba-881b-693b-4e6897c0e7db', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_111', '87fcadf3-eab9-8338-a1bd-4e6897ae7bff', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_112', 'bd80c871-7356-5f54-9d62-4e6897661108', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_113', '5795048f-71c0-902c-c145-4e6897a4feb8', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_114', 'd10566d2-48d2-a267-afd2-4e6897ad5f9a', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_115', '333fb486-eabf-2fac-13af-4e68975a7168', 'grade10', '2011-11-01 00:00:00', 0)

-- FOR HONBAN
INSERT INTO `sms_class` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `school_id`, `school_year`, `grade`, `subtype`, `description`) VALUES
('grade6', 0, '2011-11-21 00:00:00', '2011-11-21 00:00:00', '1', '1', 'Grade 06', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2011-2012', '11', 'middle', NULL),
('grade7', 0, '2011-11-21 00:00:00', '2011-11-21 00:00:00', '1', '1', 'Grade 07', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2011-2012', '12', 'middle', NULL),
('grade8', 0, '2011-11-21 00:00:00', '2011-11-21 00:00:00', '1', '1', 'Grade 08', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2011-2012', '13', 'middle', NULL),
('grade9', 0, '2011-11-21 00:00:00', '2011-11-21 00:00:00', '1', '1', 'Grade 09', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2011-2012', '14', 'high', NULL),
('grade10', 0, '2011-11-21 00:00:00', '2011-11-21 00:00:00', '1', '1', 'Grade 10', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2011-2012', '15', 'high', NULL),
('grade11', 0, '2011-11-21 00:00:00', '2011-11-21 00:00:00', '1', '1', 'Grade 11', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2011-2012', '16', 'high', NULL),
('grade12', 0, '2011-11-21 00:00:00', '2011-11-21 00:00:00', '1', '1', 'Grade 12', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2011-2012', '17', 'high', NULL);

INSERT INTO `course_classes` (`id`, `event_id`, `class_id`, `date_modified`, `deleted`) VALUES
('course_class_101', 'bb89cd6c-c1e0-280c-0ced-4e77eb017016', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_102', '6617fa3e-a020-3bd3-698f-4e77ebdb6a20', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_103', '7e0fe113-49fa-9dba-1712-4e77eb3d8db8', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_104', '89447f10-77d6-1154-3d07-4e77eb6f3ced', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_105', '41e3d105-04b2-2b2e-717b-4e77eb079778', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_106', '1a83bf50-39e0-99eb-d48d-4e77eb66bb2b', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_107', '963a83c6-5dc8-aa78-353d-4e77ebddf676', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_108', 'dbc679ca-2e9a-3c1c-937e-4e6897888c20', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_109', '345f378c-eef0-c993-d65f-4e6897d9d195', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_110', '840560a6-51ab-9991-2e1e-4e6897f6a7f2', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_111', '4d70d299-d6e5-101d-31c6-4e6897e65187', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_112', '4e8232dd-5d80-2c4b-1505-4e6897f1dab5', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_113', 'a1478bab-12fa-1e43-9445-4e6897c5a9ba', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_114', '6ff03467-0b0e-08ea-d852-4e6897ee561b', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_115', '273a2716-2bbf-7fe4-a19a-4e6897e3e57e', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_116', '8cb2c820-2335-4d78-ac7a-4e68970dac08', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_117', 'e5849cda-9ce5-b7a4-842e-4e68979f0a39', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_118', '9dd63d5a-67df-37ac-5cb2-4e6897be91f9', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_119', '3897318e-7b9f-f945-7391-4e6897c4ca74', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_120', 'eb778c3c-09ca-3e95-80ae-4e6897854cd0', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_121', '54de7715-74f9-dbb5-24df-4e6897f2b75d', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_122', '6cb98dc1-2fb2-da77-e480-4e68973e1e68', 'grade6', '2011-11-01 00:00:00', 0),
('course_class_123', '65fc0f7b-f1eb-1c3a-f351-4e68977bb6e6', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_124', 'a7ed47a4-7eb3-f8b6-4994-4e68974318f4', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_125', 'ba835b2e-b06a-9af8-ec45-4e6897aca979', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_126', '6e85a27c-3d70-2858-e7b5-4e6897e9742a', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_127', '3d9b99e8-8647-20ce-a326-4e689754915c', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_128', '988ef66e-31f0-d641-48e6-4e68975e7a9a', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_129', '7eae09b9-ab93-643c-c6c6-4e6897dc5917', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_130', '9dd63d5a-67df-37ac-5cb2-4e6897be91f9', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_131', '3897318e-7b9f-f945-7391-4e6897c4ca74', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_132', 'eb778c3c-09ca-3e95-80ae-4e6897854cd0', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_133', '54de7715-74f9-dbb5-24df-4e6897f2b75d', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_134', '6cb98dc1-2fb2-da77-e480-4e68973e1e68', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_135', 'cde93ca8-23bd-ab0a-a0cf-4e6897351598', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_136', 'cde93ca8-23bd-ab0a-a0cf-4e6897351598', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_137', '1d159c18-0a1e-936e-59e4-4e68978741e7', 'grade7', '2011-11-01 00:00:00', 0),
('course_class_138', '37a67bf8-ad4c-2b29-1125-4e6897e7b8c1', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_139', '88c40330-12a3-6606-63ab-4e68972697e9', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_140', '534c2f03-c453-f75d-e828-4e6897d6cdaa', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_141', '6c4fa9ef-75e0-fa06-d881-4e6897233d86', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_142', 'c4b6ff74-79d7-4044-cbdc-4e68971ac71a', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_143', 'e83024d0-6096-199e-bc95-4e6897f11ccd', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_144', 'd9011679-ce16-7ba1-8189-4e6897092baf', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_145', 'a0dbd605-3448-dbe7-9252-4e6897acc389', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_146', '1172b524-1d62-305a-8678-4e68975a164a', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_147', 'afa37b4a-41d0-9f20-ad54-4e6897731d67', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_148', '59298343-8d4a-fbdc-95ba-4e6897483262', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_149', '851e65fb-57c5-7db3-baa6-4e6897988619', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_150', 'c0e7b66a-94c7-07a5-89df-4e68971e44dd', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_151', 'cde93ca8-23bd-ab0a-a0cf-4e6897351598', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_152', 'cde93ca8-23bd-ab0a-a0cf-4e6897351598', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_153', '1d159c18-0a1e-936e-59e4-4e68978741e7', 'grade8', '2011-11-01 00:00:00', 0),
('course_class_154', '3b26d25c-19f1-eac7-69bb-4e689783254d', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_155', '69b23bda-0e44-a838-f124-4e68979ec0e7', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_156', 'ae9fe9d9-27b7-38b6-12c8-4e6897acdb45', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_157', 'b588f902-5b2a-c155-f4e0-4e6897800be3', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_158', 'e91c0dca-2ff8-ce4e-068e-4e689788c946', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_159', '1542299e-4ffc-94c2-f2a0-4e6897445f66', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_160', 'd597903a-eb02-06dd-b5c2-4e6897a847b2', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_161', '627f7d08-fd7b-b655-f180-4e689790ab0b', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_162', '8028d208-e481-fbe5-3c40-4e68973835d1', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_163', '30c8c5f6-0daf-f0e8-ccf1-4e689731e567', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_164', '87fcadf3-eab9-8338-a1bd-4e6897ae7bff', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_165', 'bd80c871-7356-5f54-9d62-4e6897661108', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_166', '5795048f-71c0-902c-c145-4e6897a4feb8', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_167', 'd10566d2-48d2-a267-afd2-4e6897ad5f9a', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_168', '333fb486-eabf-2fac-13af-4e68975a7168', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_169', '333fb486-eabf-2fac-13af-4e68975a7168', 'grade9', '2011-11-01 00:00:00', 0),
('course_class_170', 'd2315805-89eb-bc37-0eb4-4e68970fc1ea', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_171', '9920087d-0d91-eef6-f151-4e6897514e63', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_172', '5d4e8044-c5b5-1987-050a-4e6897a53ab2', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_173', '4afd1255-7816-765e-048b-4e6897a7b691', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_174', 'b6696aab-09c5-6640-7169-4e689759ab0e', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_175', '90241527-222f-7d47-05b2-4e68974d1d4f', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_176', 'f1a4966a-8de8-ad32-4450-4e68979efa4b', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_177', '24d3ad49-0255-465c-7c33-4e68977016b3', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_178', '6389dcab-84ba-881b-693b-4e6897c0e7db', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_179', '87fcadf3-eab9-8338-a1bd-4e6897ae7bff', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_180', 'bd80c871-7356-5f54-9d62-4e6897661108', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_181', '5795048f-71c0-902c-c145-4e6897a4feb8', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_182', 'd10566d2-48d2-a267-afd2-4e6897ad5f9a', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_183', '333fb486-eabf-2fac-13af-4e68975a7168', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_184', '333fb486-eabf-2fac-13af-4e68975a7168', 'grade10', '2011-11-01 00:00:00', 0),
('course_class_185', '877d9335-a607-01ea-acca-4e92bd5b6d42', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_186', '51c562a8-fab9-4431-fbac-4e92bbbe73c4', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_187', 'badb6fae-202d-6b09-6020-4e689755c8fd', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_188', 'a40fe1ca-169e-268d-1c38-4e6897df6c0b', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_189', '775a92c7-2c96-2d99-d0c0-4e6897ffc4f8', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_190', '2d3ed298-7331-d189-90ee-4e6897d64e19', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_191', 'ec961375-1f69-ea68-4be1-4e689701ac4f', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_192', '8eb0c3b6-8a18-f74c-0d5a-4e6897a921d1', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_193', '572843ca-2ee3-ec9c-d7a4-4e689710a112', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_194', '6de7a330-df70-8a1f-791a-4e6897bd360f', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_195', '37d6eeb4-16c2-c6aa-7f44-4e68975796fe', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_196', 'dce82902-e8f5-1a63-91ba-4e68976169a9', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_197', '15ecfacd-b51c-8c73-5982-4e6897496d66', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_198', 'bdea66d4-bd51-ad00-788c-4e68970ac651', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_199', '51669d04-6895-7caf-3069-4e68977ea4a6', 'grade11', '2011-11-01 00:00:00', 0),
('course_class_200', '1a019e29-26c3-cb49-44d6-4e92c288cac9', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_201', '3670dcb2-bba0-dfc4-0533-4e92b9d71c90', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_202', '3670dcb2-bba0-dfc4-0533-4e92b9d71c90', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_203', '82c31064-ccbf-02fc-cf04-4e92b9900822', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_204', '82c31064-ccbf-02fc-cf04-4e92b9900822', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_205', '6cbc95c9-a7d0-7fb6-78e1-4e6897bafb4c', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_206', '1dd038ae-eb30-a4a3-804b-4e6897130865', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_207', 'b568a5fa-6023-ef31-68d4-4e6897ce0e14', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_208', '940d86c9-098c-b069-c424-4e68971d48db', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_209', 'cf9273d2-4e6d-e740-eb07-4e689763d36f', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_210', 'e6dd0b66-400c-059e-85f0-4e689720ea95', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_211', '40c5093e-fbb5-d2b2-a64f-4e68970d16f4', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_212', '1e648589-9e5d-12d4-8571-4e689723ae15', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_213', 'b02003aa-30f4-e926-da83-4e689752d19e', 'grade12', '2011-11-01 00:00:00', 0),
('course_class_214', 'dc740d12-41ba-231b-bf32-4e6897a3012a', 'grade12', '2011-11-01 00:00:00', 0);

UPDATE contacts SET contact_status='101' WHERE smsgroup='parent' ;

SELECT contact_id, name, basic_fee, event_credit FROM `sms_fee` WHERE fee_subcategory='tuition' AND contact_id IS NOT NULL AND deleted=0 GROUP BY contact_id HAVING COUNT(contact_id) >1 ;

SELECT contacts.last_name, contacts.first_name, contacts.idnumber, contacts.custom_var14, f1.id, f1. contact_id, f1.name, f1.basic_fee, f1.event_credit, f2.name, f2.basic_fee, f2.event_credit, f2.id 
FROM sms_fee f1
INNER JOIN sms_fee f2 ON f1.contact_id=f2.contact_id AND f2.fee_subcategory='tuition' AND f2.deleted=0 AND f1.id!=f2.id AND f1.event_credit!=f2.event_credit
INNER JOIN contacts ON f1.contact_id=contacts.id AND contacts.deleted=0
WHERE f1.fee_subcategory='tuition' AND f1.contact_id IS NOT NULL AND f1.deleted=0

-- eiken fix
SELECT last_name, first_name, id, idnumber, school_id, contact_status, custom_var11 FROM contacts WHERE deleted=0 AND custom_var11 IS NOT NULL AND smsgroup='student'
AND custom_var11 NOT IN ('5', '4', '3', 'Pre-2', '2', 'Pre-1', '1')  => there were no record matching this

-- count queries for courses

-- FICS
SELECT name, school_id, count(contact_id) FROM `event_contacts` 
JOIN smsevent ON smsevent.id=event_contacts.event_id AND smsevent.deleted=0 
WHERE event_contacts.deleted=0 AND school_id='sssssss-1111' GROUP BY event_id 

-- FIS
SELECT name, school_id, count(contact_id) FROM `event_contacts` 
JOIN smsevent ON smsevent.id=event_contacts.event_id AND smsevent.deleted=0 
WHERE event_contacts.deleted=0 AND school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4'GROUP BY event_id 

-- FIS PreK 3 class addition

INSERT INTO `sms_class` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `school_id`, `school_year`, `grade`, `subtype`, `description`) VALUES
('prek_3', 0, '2012-03-30 00:00:00', '2012-03-30 07:29:23', '1', '1', 'Pre-kindergarten K3', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2011-2012', '4', 'kinder', NULL);

INSERT INTO `course_classes` (`id`, `event_id`, `class_id`, `date_modified`, `deleted`) VALUES
('814e6cd1-ca94-3fdb-9a73-4f7561b070c1', '95fef2a2-7f49-b799-9e47-4f150a53ac33', 'prek_3', '2012-03-30 07:33:04', 0);

INSERT INTO `seiseki_template` (`id`, `name`, `st_category`, `issue_timing`, `overall_grade_type`, `target_type`, `template_order`, `output_template_name`, `description`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `school_id`, `deleted`) VALUES
('prekinder3_rc_readiness', 'School Readiness Skills', 'pre-kinder_report_card', 'quarterly', NULL, 'homeroom', 1, '', '', '2012-03-30 00:00:00', '2012-03-30 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('prekinder3_rc_writing', 'Writing', 'pre-kinder_report_card', 'quarterly', NULL, 'homeroom', 2, '', '', '2012-03-30 00:00:00', '2012-03-30 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('prekinder3_rc_math', 'Math', 'pre-kinder_report_card', 'quarterly', NULL, 'homeroom', 3, '', '', '2012-03-30 00:00:00', '2012-03-30 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('prekinder3_rc_reading', 'Reading', 'pre-kinder_report_card', 'quarterly', NULL, 'homeroom', 4, '', '', '2012-03-30 00:00:00', '2012-03-30 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('prekinder3_rc_comment', 'Teacher Comments', 'pre-kinder_report_card_comment', 'quarterly', NULL, 'homeroom', 5, NULL, NULL, '2012-03-30 00:00:00', '2012-03-30 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0);

INSERT INTO `course_seisekitemplates` (`id`, `course_id`, `template_id`, `date_modified`, `deleted`, `template_order`) VALUES
('course_seiseki_f3001', '95fef2a2-7f49-b799-9e47-4f150a53ac33', 'prekinder3_rc_readiness', '2012-03-30 00:00:00', 0, 1),
('course_seiseki_f3002', '95fef2a2-7f49-b799-9e47-4f150a53ac33', 'prekinder3_rc_writing', '2012-03-30 00:00:00', 0, 2),
('course_seiseki_f3003', '95fef2a2-7f49-b799-9e47-4f150a53ac33', 'prekinder3_rc_math', '2012-03-30 00:00:00', 0, 3),
('course_seiseki_f3004', '95fef2a2-7f49-b799-9e47-4f150a53ac33', 'prekinder3_rc_reading', '2012-03-30 00:00:00', 0, 4),
('course_seiseki_f3005', '95fef2a2-7f49-b799-9e47-4f150a53ac33', 'prekinder_rc_comment', '2012-03-30 00:00:00', 0, 5);


INSERT INTO `seiseki_item` (`id`, `template_id`, `name`, `item_type`, `item_order`, `item_weight`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`) VALUES
('prekinder3_txt01', 'prekinder3_rc_comment', 'Teacher''s Comments', 'text', 1, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),

('prekinder3_math01', 'prekinder3_rc_math', 'Names basic shapes', '1-4', 1, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_math02', 'prekinder3_rc_math', 'Names colors', '1-4', 2, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_math03', 'prekinder3_rc_math', 'Keeps 1:1 correspondence when counting', '1-4', 3, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_math04', 'prekinder3_rc_math', 'Can identify and extend patterns', '1-4', 4, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_math05', 'prekinder3_rc_math', 'Can sort objects', '1-4', 5, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_math06', 'prekinder3_rc_math', 'Beginning to understands concept of more, less, and equal', '1-4', 6, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_math07', 'prekinder3_rc_math', 'Recognizes numbers to 5', '1-4', 7, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_math08', 'prekinder3_rc_math', 'Understands that numbers represent a quantity of objects', '1-4', 8, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),

('prekinder3_rs01', 'prekinder3_rc_readiness', 'Separates from caregiver', '1-4', 1, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rs02', 'prekinder3_rc_readiness', 'Shares, takes turns, gets along with others', '1-4', 2, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rs03', 'prekinder3_rc_readiness', 'Follows class and playground rules', '1-4', 3, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rs04', 'prekinder3_rc_readiness', 'Responsible for and takes care of school supplies', '1-4', 4, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rs05', 'prekinder3_rc_readiness', 'Tries his/her best', '1-4', 5, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rs06', 'prekinder3_rc_readiness', 'Stays focused during activities', '1-4', 6, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rs07', 'prekinder3_rc_readiness', 'Listens without interrupting', '1-4', 7, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rs08', 'prekinder3_rc_readiness', 'Follows directions', '1-4', 8, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),

('prekinder3_rdg01', 'prekinder3_rc_reading', 'Shows interest in books/listens to stories read aloud', '1-4', 1, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rdg02', 'prekinder3_rc_reading', 'Knows the difference between real and pretend', '1-4', 2, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rdg05', 'prekinder3_rc_reading', 'Knows some letter names', '1-4', 3, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rdg06', 'prekinder3_rc_reading', 'Knows some letter sounds', '1-4', 4, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rdg07', 'prekinder3_rc_reading', 'Can express basic needs in English', '1-4', 5, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rdg08', 'prekinder3_rc_reading', 'Sings simple familiar songs', '1-4', 6, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),

('prekinder3_rw01', 'prekinder3_rc_writing', 'Can hold and use pencils, scissors and crayons correctly', '1-4', 1, 1, 0, '2011-10-08 10:00:00', '2011-10-25 01:34:05', '77906c27-c1b8-51de-ea3b-4e5db7a0e9f5', '1'),
('prekinder3_rw02', 'prekinder3_rc_writing', 'Recognizes first name', '1-4', 2, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1'),
('prekinder3_rw03', 'prekinder3_rc_writing', 'Can write first name', '1-4', 3, 1, 0, '2011-10-08 10:00:00', '2011-10-08 10:00:00', '1', '1');

-- 2012-06-07 gakunen updates
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_entrance_date='2011-06-25'

UPDATE contacts SET grade_entrance_date='2011-07-01' 
WHERE smsgroup='student' AND grade IS NOT NULL AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND grade_entrance_date='2011-06-25'

-- 2012-06-07 attendance revival for grade1, grade2 and grade3 home room courses
grade1
	'e22ba0b0-9c32-1b55-0a28-4e77eb5d06cb' 
grade2
	'c6fe063d-0e2e-4087-3a1d-4e77eb9866f2'
grade3
	'55e1b59e-8719-2cbe-83de-4e77ebc6bc75'
	
DELETE FROM si WHERE event_id NOT IN (	'e22ba0b0-9c32-1b55-0a28-4e77eb5d06cb', 'c6fe063d-0e2e-4087-3a1d-4e77eb9866f2', '55e1b59e-8719-2cbe-83de-4e77ebc6bc75')

SELECT SUBSTRING(from_datetime, 12, 5), from_datetime FROM si 
WHERE SUBSTRING(from_datetime, 12, 2) < '20'    -- afternoon session

WHERE SUBSTRING(from_datetime, 12, 2) > '20'    -- morning session


SELECT SUBSTRING(so.from_datetime, 1, 10), SUBSTRING(si.from_datetime, 1, 10), so.from_datetime, so.attendance_status, si.attendance_status
	FROM smsevent_instances20120607 so, si
WHERE SUBSTRING(so.from_datetime, 1, 10)=SUBSTRING(si.from_datetime, 1, 10) AND so.event_id=si.event_id
	AND so.target_id=si.target_id AND so.category=si.category 
	AND SUBSTRING(so.from_datetime, 12, 2) < '20' AND SUBSTRING(si.from_datetime, 12, 2) < '20' 
	AND so.category='teacher'
	AND si.attendance_status IS NOT NULL AND so.attendance_status IS NULL
	AND so.event_id='e22ba0b0-9c32-1b55-0a28-4e77eb5d06cb'
	AND so.from_datetime > '2012-06-01'
	
UPDATE smsevent_instances so, si SET so.attendance_status=si.attendance_status
WHERE SUBSTRING(so.from_datetime, 1, 10)=SUBSTRING(si.from_datetime, 1, 10) AND so.event_id=si.event_id
	AND so.target_id=si.target_id AND so.category=si.category 
	AND SUBSTRING(so.from_datetime, 12, 2) > '20' AND SUBSTRING(si.from_datetime, 12, 2) > '20' 
	AND so.category='teacher'
	AND si.attendance_status IS NOT NULL AND so.attendance_status IS NULL
	


SELECT SUBSTRING(so.from_datetime, 1, 10), SUBSTRING(si.from_datetime, 1, 10), so.from_datetime, so.attendance_memo, si.attendance_memo
	FROM smsevent_instances so, si
WHERE SUBSTRING(so.from_datetime, 1, 10)=SUBSTRING(si.from_datetime, 1, 10) AND so.event_id=si.event_id
	AND so.target_id=si.target_id AND so.category=si.category 
	AND SUBSTRING(so.from_datetime, 12, 2) > '20' AND SUBSTRING(si.from_datetime, 12, 2) > '20' 
	AND so.category='student'
	AND si.attendance_memo!='' AND so.attendance_memo=''
	AND so.event_id='e22ba0b0-9c32-1b55-0a28-4e77eb5d06cb'

UPDATE smsevent_instances so, si SET so.attendance_memo=si.attendance_memo
WHERE SUBSTRING(so.from_datetime, 1, 10)=SUBSTRING(si.from_datetime, 1, 10) AND so.event_id=si.event_id
	AND so.target_id=si.target_id AND so.category=si.category 
	AND SUBSTRING(so.from_datetime, 12, 2) > '20' AND SUBSTRING(si.from_datetime, 12, 2) > '20' 
	AND so.category='student'
	AND si.attendance_memo!='' AND so.attendance_memo=''
	
-- 2012-08-13

SELECT name, CONCAT( name, " (2011)" )  FROM `sms_class` ;

UPDATE sms_class SET name=CONCAT( name, " (2011)" )  WHERE 1; 

INSERT INTO `sms_class` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `school_id`, `school_year`, `grade`, `subtype`, `description`) VALUES
('2012_prek_3', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Pre-kindergarten K3 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '4', 'kinder', NULL),
('2012_prek_4', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Pre-kindergarten K4 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '4', 'kinder', NULL),
('2012_kindergarten', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Kindergarten (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '5', 'kinder', NULL),
('2012_grade1', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 01 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '6', 'primary', NULL),
('2012_grade2', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 02 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '7', 'primary', NULL),
('2012_grade3', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 03 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '8', 'primary', NULL),
('2012_grade4', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 04 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '9', 'primary', NULL),
('2012_grade5', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 05 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '10', 'primary', NULL),
('2012_grade6', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 06 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '11', 'middle', NULL),
('2012_grade7', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 07 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '12', 'middle', NULL),
('2012_grade8', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 08 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '13', 'middle', NULL),
('2012_grade9', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 09 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '14', 'high', NULL),
('2012_grade10', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 10 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '15', 'high', NULL),
('2012_grade11', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 11 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '16', 'high', NULL),
('2012_grade12', 0, '2012-08-13 00:00:00', '2012-08-13 00:00:00', '1', '1', 'Grade 12 (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '17', 'high', NULL);


INSERT INTO `sms_class` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `school_id`, `school_year`, `grade`, `subtype`, `description`) VALUES
('2012_grade_10n', 0, '2012-09-04 00:00:00', '2012-09-04 00:00:00', '1', '1', 'Grade 10 [n] (2012)', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', '2012-2013', '15', 'high', NULL);


-- 2012-09-30 course_seisekitemplates table update for 2012-2013 academic year

SELECT smsevent.name as course_name, sms_class.name as class_name, course_classes.id
FROM smsevent, course_classes, sms_class
WHERE course_classes.event_id=smsevent.id AND course_classes.class_id=sms_class.id 
	AND course_classes.deleted=1 AND smsevent.name LIKE "%Homeroom%"

UPDATE `fis_honban0928`.`course_classes` SET `deleted` = '0' WHERE `course_classes`.`id` = 'course_class_101' LIMIT 1 ;
UPDATE `fis_honban0928`.`course_classes` SET `deleted` = '0' WHERE `course_classes`.`id` = 'course_class_102' LIMIT 1 ;
UPDATE `fis_honban0928`.`course_classes` SET `deleted` = '0' WHERE `course_classes`.`id` = 'course_class_103' LIMIT 1 ;
UPDATE `fis_honban0928`.`course_classes` SET `deleted` = '0' WHERE `course_classes`.`id` = 'course_class_104' LIMIT 1 ;
UPDATE `fis_honban0928`.`course_classes` SET `deleted` = '0' WHERE `course_classes`.`id` = 'course_class_105' LIMIT 1 ;
UPDATE `fis_honban0928`.`course_classes` SET `deleted` = '0' WHERE `course_classes`.`id` = 'course_class_106' LIMIT 1 ;
UPDATE `fis_honban0928`.`course_classes` SET `deleted` = '0' WHERE `course_classes`.`id` = 'course_class_107' LIMIT 1 ;

	

SELECT sms_class.name as class_name, sms_class.grade, smsevent.name as course_name, custom_var38 as course_subject, 
	course_category.name as course_category, 
	seiseki_template.name as seiseki_name, seiseki_template.st_category, 
	course_seisekitemplates.id, smsevent.id as course_id, seiseki_template.id as template_id, course_seisekitemplates.date_modified, 
	course_seisekitemplates.deleted, course_seisekitemplates.template_order
FROM smsevent, seiseki_template, course_seisekitemplates, course_classes, sms_class, course_category
WHERE course_seisekitemplates.course_id=smsevent.id AND course_seisekitemplates.template_id=seiseki_template.id
	AND course_classes.event_id=smsevent.id AND course_classes.class_id=sms_class.id
	AND smsevent.parent_id=course_category.id 
	AND custom_var36="2011-2012" AND smsevent.school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4'
	AND sms_class.school_year="2011-2012" 
	AND course_seisekitemplates.deleted=0 AND course_classes.deleted=0
ORDER BY CAST(sms_class.grade AS UNSIGNED), sms_class.name, seiseki_template.st_category ASC, course_seisekitemplates.template_order ASC


SELECT sms_class.name as class_name, sms_class.grade, smsevent.name as course_name, custom_var38 as course_subject, 
	course_category.name as course_category, 
	seiseki_template.name as seiseki_name, seiseki_template.st_category, 
	course_seisekitemplates.id, smsevent.id as course_id, seiseki_template.id as template_id, course_seisekitemplates.date_modified, 
	course_seisekitemplates.deleted, course_seisekitemplates.template_order
FROM smsevent, seiseki_template, course_seisekitemplates, course_classes, sms_class, course_category
WHERE course_seisekitemplates.course_id=smsevent.id AND course_seisekitemplates.template_id=seiseki_template.id
	AND smsevent.parent_id=course_category.id 
	AND course_classes.event_id=smsevent.id AND course_classes.class_id=sms_class.id
	AND custom_var36="2012-2013" AND smsevent.school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4'
	AND sms_class.school_year="2012-2013" 
	AND course_seisekitemplates.deleted=0 AND course_classes.deleted=0
ORDER BY CAST(sms_class.grade AS UNSIGNED), sms_class.name, seiseki_template.st_category ASC, course_seisekitemplates.template_order ASC



SELECT sms_class.name as class_name, course_category.name as course_category, 
	smsevent.name as course_name, custom_var38 as course_subject, smsevent.id as course_id
FROM smsevent, course_classes, sms_class, course_category
WHERE smsevent.parent_id=course_category.id AND course_classes.event_id=smsevent.id AND course_classes.class_id=sms_class.id 
	AND custom_var36="2012-2013" AND smsevent.deleted=0 AND course_classes.deleted=0
	AND smsevent.school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4'
ORDER BY CAST(sms_class.grade AS UNSIGNED), sms_class.name, smsevent.name



SELECT course_category.name as course_category, smsevent.name as course_name, custom_var38 as course_subject, smsevent.id as course_id
FROM smsevent, course_category
WHERE smsevent.parent_id=course_category.id AND custom_var36="2012-2013" AND smsevent.deleted=0 
	AND smsevent.school_id='bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4'
ORDER BY course_category.name ASC, smsevent.name

-- 2012-10-17 grading updates for elementary

INSERT INTO `seiseki_template` (`id`, `name`, `st_category`, `issue_timing`, `overall_grade_type`, `target_type`, `template_order`, `output_template_name`, `description`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `school_id`, `deleted`) VALUES
('ngrd01_lang_art_read', 'Reading', 'elementary_report_card1', 'quarterly', NULL, 'homeroom', 1, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('ngrd02_lang_art_read', 'Reading', 'elementary_report_card2', 'quarterly', NULL, 'homeroom', 1, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('ngrd03_lang_art_read', 'Reading', 'elementary_report_card3', 'quarterly', NULL, 'homeroom', 1, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('ngrd04_lang_art_read', 'Reading', 'elementary_report_card4', 'quarterly', NULL, 'homeroom', 1, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('ngrd05_lang_art_read', 'Reading', 'elementary_report_card5', 'quarterly', NULL, 'homeroom', 1, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0);

INSERT INTO `seiseki_template` (`id`, `name`, `st_category`, `issue_timing`, `overall_grade_type`, `target_type`, `template_order`, `output_template_name`, `description`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `school_id`, `deleted`) VALUES
('ngrd01_lang_art_write', ' Writing', 'elementary_report_card1', 'quarterly', NULL, 'homeroom', 2, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('ngrd02_lang_art_write', ' Writing', 'elementary_report_card2', 'quarterly', NULL, 'homeroom', 2, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('ngrd03_lang_art_write', ' Writing', 'elementary_report_card3', 'quarterly', NULL, 'homeroom', 2, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('ngrd04_lang_art_write', ' Writing', 'elementary_report_card4', 'quarterly', NULL, 'homeroom', 2, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('ngrd05_lang_art_write', ' Writing', 'elementary_report_card5', 'quarterly', NULL, 'homeroom', 2, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0);

INSERT INTO `seiseki_item` (`id`, `template_id`, `name`, `item_type`, `item_order`, `item_weight`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`) VALUES
('ngrd01_lang_art_read_01', 'ngrd01_lang_art_read', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_02', 'ngrd01_lang_art_read', 'Knows letter names', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_03', 'ngrd01_lang_art_read', 'Knows consonant sounds', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_04', 'ngrd01_lang_art_read', 'Uses basic punctuation when reading orally', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_05', 'ngrd01_lang_art_read', 'Is able to use phonic skills to sound out new words', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_06', 'ngrd01_lang_art_read', 'Reads independently for 10 to 15 minutes', '1-4', 6, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_07', 'ngrd01_lang_art_read', 'Follows written directions', '1-4', 7, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_08', 'ngrd01_lang_art_read', 'Retells beginning, middle and ending of story', '1-4', 8, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_09', 'ngrd01_lang_art_read', 'Reads aloud with fluency', '1-4', 9, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_10', 'ngrd01_lang_art_read', 'Knows Japanese vowel sounds', '1-4', 10, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_11', 'ngrd01_lang_art_read', 'Knows short vowel sounds', '1-4', 11, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_12', 'ngrd01_lang_art_read', 'knows long vowel sounds', '1-4', 12, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_read_13', 'ngrd01_lang_art_read', 'Able to read own writing', '1-4', 13, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_read_01', 'ngrd02_lang_art_read', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_read_02', 'ngrd02_lang_art_read', 'Understands and applies vocabulary appropriately', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_read_03', 'ngrd02_lang_art_read', 'Uses phonics rules/other skills to read new words', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_read_04', 'ngrd02_lang_art_read', 'Uses predicting and questioning strategies ', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_read_05', 'ngrd02_lang_art_read', 'Identifies details and sequence of events', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_read_06', 'ngrd02_lang_art_read', 'Summarizes and draws conclusions from the text ', '1-4', 6, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_read_07', 'ngrd02_lang_art_read', 'Gathers simple information for reports other areas of study', '1-4', 7, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_read_08', 'ngrd02_lang_art_read', 'Reads aloud accurately and with clarity', '1-4', 8, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_read_09', 'ngrd02_lang_art_read', 'Reads silently for at least 15 minutes', '1-4', 9, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_read_01', 'ngrd03_lang_art_read', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_read_02', 'ngrd03_lang_art_read', 'Makes simple predictions', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_read_03', 'ngrd03_lang_art_read', 'Uses phonetic and structural analysis ', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_read_04', 'ngrd03_lang_art_read', 'Uses word reference materials', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_read_05', 'ngrd03_lang_art_read', 'Understands level-appropriate reading vocabulary', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_read_06', 'ngrd03_lang_art_read', 'Follows written directions', '1-4', 6, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_read_07', 'ngrd03_lang_art_read', 'Understands the main problem in stories', '1-4', 7, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_read_08', 'ngrd03_lang_art_read', 'Makes connections between stories and own life', '1-4', 8, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_read_09', 'ngrd03_lang_art_read', 'Summarizes and paraphrases information', '1-4', 9, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd04_lang_art_read_01', 'ngrd04_lang_art_read', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd04_lang_art_read_02', 'ngrd04_lang_art_read', 'Makes/confirms predictions according to context of a story', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:44:55', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_read_03', 'ngrd04_lang_art_read', 'Understands the basic concept of plot', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:45:19', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_read_04', 'ngrd04_lang_art_read', 'Understands level-appropriate reading vocabulary', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:45:27', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_read_05', 'ngrd04_lang_art_read', 'Reads with animation and expression', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:45:36', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_read_06', 'ngrd04_lang_art_read', 'Decodes unfamiliar words by looking at the context of the story', '1-4', 6, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:45:50', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_read_07', 'ngrd04_lang_art_read', 'Understands elements of character development', '1-4', 7, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:46:00', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_read_08', 'ngrd04_lang_art_read', 'Summarizes and paraphrases information in texts', '1-4', 8, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:46:10', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_read_09', 'ngrd04_lang_art_read', 'Uses phonetic/structural analysis to decode unknown words', '1-4', 9, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:46:21', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_read_10', 'ngrd04_lang_art_read', 'Understands the author', '1-4', 10, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd04_lang_art_read_11', 'ngrd04_lang_art_read', 'Knows themes that recur across literary works', '1-4', 11, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:46:29', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_read_12', 'ngrd04_lang_art_read', 'Understands the ways in which language is used ', '1-4', 12, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:46:37', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_read_13', 'ngrd04_lang_art_read', 'Recognizes the characteristics of a variety of genres', '1-4', 13, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:46:45', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_read_14', 'ngrd04_lang_art_read', 'Understands grade level texts', '1-4', 14, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:46:53', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd05_lang_art_read_01', 'ngrd05_lang_art_read', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_read_02', 'ngrd05_lang_art_read', 'Understands level-appropriate reading vocabulary', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_read_03', 'ngrd05_lang_art_read', 'Reads with  expression and confidence', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_read_04', 'ngrd05_lang_art_read', 'Understands the basic concept of plot ', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_read_05', 'ngrd05_lang_art_read', 'Understands key literary terms', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_read_06', 'ngrd05_lang_art_read', 'Summarizes and paraphrases information in texts', '1-4', 6, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_read_07', 'ngrd05_lang_art_read', 'Knows characteristics of a variety of literary forms/genres ', '1-4', 7, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_read_08', 'ngrd05_lang_art_read', 'Uses word reference material to decode unfamiliar words', '1-4', 8, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1');

INSERT INTO `seiseki_item` (`id`, `template_id`, `name`, `item_type`, `item_order`, `item_weight`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`) VALUES
('ngrd01_lang_art_write_01', 'ngrd01_lang_art_write', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_02', 'ngrd01_lang_art_write', 'Appropriate capitalization', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_03', 'ngrd01_lang_art_write', 'Uses basic punctuation correctly', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_04', 'ngrd01_lang_art_write', 'Forms letters legibly', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_05', 'ngrd01_lang_art_write', 'Can write a basic sentence', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_06', 'ngrd01_lang_art_write', 'Can write a simple paragraph', '1-4', 6, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_07', 'ngrd01_lang_art_write', 'Can write a list', '1-4', 7, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_08', 'ngrd01_lang_art_write', 'Can write basic directions', '1-4', 8, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_09', 'ngrd01_lang_art_write', 'Writing begins to tell a story or make a point', '1-4', 9, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_10', 'ngrd01_lang_art_write', 'Writing using beginning, middle and ending', '1-4', 10, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_11', 'ngrd01_lang_art_write', 'Able to proofread and correct own writing', '1-4', 11, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_12', 'ngrd01_lang_art_write', 'Can fill in blanks in a model sentence', '1-4', 12, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_lang_art_write_13', 'ngrd01_lang_art_write', 'Spelling', '1-4', 12, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_01', 'ngrd02_lang_art_write', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_02', 'ngrd02_lang_art_write', 'Spells words correctly on tests', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_03', 'ngrd02_lang_art_write', 'Spells words correctly in daily writing', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_04', 'ngrd02_lang_art_write', 'Uses proper capitalization and punctuation', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_05', 'ngrd02_lang_art_write', 'Writes simple sentences with nouns and verbs.', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_06', 'ngrd02_lang_art_write', 'Applies grammar rules to daily writing', '1-4', 6, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_07', 'ngrd02_lang_art_write', 'Writes paragraphs with main idea and makes simple sentences', '1-4', 7, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_08', 'ngrd02_lang_art_write', 'Expresses ideas in an organized way', '1-4', 8, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_09', 'ngrd02_lang_art_write', 'Expresses ideas using own voice and creativity', '1-4', 9, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_10', 'ngrd02_lang_art_write', 'Uses age appropriate vocabulary and expressions', '1-4', 10, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_11', 'ngrd02_lang_art_write', 'Able to use the writing process', '1-4', 11, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_lang_art_write_12', 'ngrd02_lang_art_write', 'Spelling', '1-4', 11, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_write_01', 'ngrd03_lang_art_write', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_write_02', 'ngrd03_lang_art_write', 'Uses prewriting strategies', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_write_03', 'ngrd03_lang_art_write', 'Uses descriptive language writing', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_write_04', 'ngrd03_lang_art_write', 'Develops ideas to make stories interesting', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_write_05', 'ngrd03_lang_art_write', 'Writes stories with a clear sequence', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2011-11-02 02:54:33', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd03_lang_art_write_06', 'ngrd03_lang_art_write', 'Writes using complete sentences', '1-4', 6, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_write_07', 'ngrd03_lang_art_write', 'Uses a variety of sentence structures', '1-4', 7, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_write_08', 'ngrd03_lang_art_write', 'Uses conventional spelling', '1-4', 8, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_write_09', 'ngrd03_lang_art_write', 'Summarizes main ideas providing details', '1-4', 9, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_write_10', 'ngrd03_lang_art_write', 'Uses conventions of capitalization and punctuation', '1-4', 10, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_write_11', 'ngrd03_lang_art_write', 'Shares own writing with classmates', '1-4', 11, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd03_lang_art_write_12', 'ngrd03_lang_art_write', 'Writes legibly', '1-4', 12, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd04_lang_art_write_01', 'ngrd04_lang_art_write', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd04_lang_art_write_02', 'ngrd04_lang_art_write', 'Writes legibly in print and cursive', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:47:27', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_write_03', 'ngrd04_lang_art_write', 'Writes stories with a clear sequence', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:47:42', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_write_04', 'ngrd04_lang_art_write', 'Uses a variety of sentence structures in writing', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:47:34', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_write_05', 'ngrd04_lang_art_write', 'Writes using plot', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:47:49', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_write_06', 'ngrd04_lang_art_write', 'Uses descriptive language to clarify and enhance writing', '1-4', 6, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:48:01', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_write_07', 'ngrd04_lang_art_write', 'Used a wide variety of verbs and verb tenses', '1-4', 7, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:48:10', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_write_08', 'ngrd04_lang_art_write', 'Uses resources to build new vocabulary in writing', '1-4', 8, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:48:18', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_write_09', 'ngrd04_lang_art_write', 'Demonstrates grade level spelling accuracy', '1-4', 9, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:48:26', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_write_10', 'ngrd04_lang_art_write', 'Properly uses conventions in final writing samples', '1-4', 10, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:48:35', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_write_11', 'ngrd04_lang_art_write', 'Proofreads and edits written work', '1-4', 11, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:48:44', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_write_12', 'ngrd04_lang_art_write', 'Uses paragraph form', '1-4', 12, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:48:51', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd04_lang_art_write_13', 'ngrd04_lang_art_write', 'Effort is shown on writing assignments  ', '1-4', 13, 1, 0, '2012-10-17 10:00:00', '2011-11-08 00:48:59', '64a60626-3732-684b-3326-4e5df9ae90b2', '1'),
('ngrd05_lang_art_write_01', 'ngrd05_lang_art_write', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_02', 'ngrd05_lang_art_write', 'Writes legibly in print', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_03', 'ngrd05_lang_art_write', 'Writes legibly in cursive', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_04', 'ngrd05_lang_art_write', 'Writes stories with a clear sequence', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_05', 'ngrd05_lang_art_write', 'Creates multi-paragraph compositions', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_06', 'ngrd05_lang_art_write', 'Uses a variety of sentence structures in writing', '1-4', 6, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_07', 'ngrd05_lang_art_write', 'Writes using plot, conflict, resolution', '1-4', 7, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_08', 'ngrd05_lang_art_write', 'Writes with clear character development', '1-4', 8, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_09', 'ngrd05_lang_art_write', 'Uses descriptive language that clarifies and enhances ideas', '1-4', 9, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_10', 'ngrd05_lang_art_write', 'Uses resources to build new vocabulary in writing', '1-4', 10, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_11', 'ngrd05_lang_art_write', 'Properly uses conventions in final writing samples', '1-4', 11, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_12', 'ngrd05_lang_art_write', 'Demonstrates grade level spelling accuracy', '1-4', 12, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_13', 'ngrd05_lang_art_write', 'Effort is shown on spelling exams', '1-4', 13, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_lang_art_write_14', 'ngrd05_lang_art_write', 'Effort is shown on writing assignments', '1-4', 14, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1');


INSERT INTO `seiseki_template` (`id`, `name`, `st_category`, `issue_timing`, `overall_grade_type`, `target_type`, `template_order`, `output_template_name`, `description`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `school_id`, `deleted`) VALUES
('ngrd01_scn_hlth', 'Science', 'elementary_report_card1', 'quarterly', NULL, 'homeroom', 5, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('ngrd02_scn_hlth', 'Science', 'elementary_report_card2', 'quarterly', NULL, 'homeroom', 4, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('ngrd04_scn_hlth', 'Science', 'elementary_report_card4', 'quarterly', NULL, 'homeroom', 5, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('ngrd05_scn_hlth', 'Science', 'elementary_special_report_card', 'quarterly', NULL, 'homeroom', 23, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0);


INSERT INTO `seiseki_item` (`id`, `template_id`, `name`, `item_type`, `item_order`, `item_weight`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`) VALUES
('ngrd01_scn_hlth_01', 'ngrd01_scn_hlth', 'Demonstrates effort', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_scn_hlth_02', 'ngrd01_scn_hlth', 'Shares prior and learned knowledge', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_scn_hlth_03', 'ngrd01_scn_hlth', 'Actively becomes involved in science related activities', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_scn_hlth_04', 'ngrd01_scn_hlth', 'Can group like animals, plants, foods, etc.', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd01_scn_hlth_05', 'ngrd01_scn_hlth', 'Understands and applies new information', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_scn_hlth_01', 'ngrd02_scn_hlth', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_scn_hlth_02', 'ngrd02_scn_hlth', 'Actively participates in science activities', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_scn_hlth_03', 'ngrd02_scn_hlth', 'Demonstrates effort', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_scn_hlth_04', 'ngrd02_scn_hlth', 'Quality of work', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_scn_hlth_05', 'ngrd02_scn_hlth', 'Asks meaningful questions', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd02_scn_hlth_06', 'ngrd02_scn_hlth', 'Understands scientific concepts', '1-4', 6, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd04_scn_hlth_01', 'ngrd04_scn_hlth', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd04_scn_hlth_02', 'ngrd04_scn_hlth', 'Science Experiments', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd04_scn_hlth_03', 'ngrd04_scn_hlth', 'Demonstrates effort', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd04_scn_hlth_04', 'ngrd04_scn_hlth', 'Quality of work', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd04_scn_hlth_05', 'ngrd04_scn_hlth', 'Understands and applies scientific vocabulary', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_scn_hlth_01', 'ngrd05_scn_hlth', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_scn_hlth_02', 'ngrd05_scn_hlth', 'Laboratory Work/ Science Experiments', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_scn_hlth_03', 'ngrd05_scn_hlth', 'Demonstrates effort', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_scn_hlth_04', 'ngrd05_scn_hlth', 'Quality of work', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('ngrd05_scn_hlth_05', 'ngrd05_scn_hlth', 'Understands and applies scientific vocabulary', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1');


INSERT INTO `seiseki_template` (`id`, `name`, `st_category`, `issue_timing`, `overall_grade_type`, `target_type`, `template_order`, `output_template_name`, `description`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `school_id`, `deleted`) VALUES
('nelementary_jp01', 'Japanese', 'elementary_special_report_card', 'quarterly', NULL, 'homeroom', 22, '', '', '2012-10-17 00:00:00', '2012-10-17 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0);

INSERT INTO `seiseki_item` (`id`, `template_id`, `name`, `item_type`, `item_order`, `item_weight`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`) VALUES
('nelm_jp01', 'nelementary_jp01', 'Overall performance', '1-4', 1, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('nelm_jp02', 'nelementary_jp01', 'Reading', '1-4', 2, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('nelm_jp03', 'nelementary_jp01', 'Writing', '1-4', 3, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('nelm_jp04', 'nelementary_jp01', 'Oral activities', '1-4', 4, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1'),
('nelm_jp05', 'nelementary_jp01', 'Effort and attitude', '1-4', 5, 1, 0, '2012-10-17 10:00:00', '2012-10-17 10:00:00', '1', '1');

UPDATE `seiseki_template` SET `st_category` = 'elementary_report_card4', `template_order` = '5' WHERE CONVERT( `seiseki_template`.`id` USING utf8 ) = 'ngrd05_scn_hlth' LIMIT 1 ;
UPDATE `course_seisekitemplates` SET `course_id` = '56da12ed-38f3-ccb8-0296-5021488bba6f' WHERE CONVERT( `course_seisekitemplates`.`id` USING utf8 ) = 'course_seiseki_n089' LIMIT 1 ;
UPDATE `course_seisekitemplates` SET `deleted` = '1' WHERE CONVERT( `course_seisekitemplates`.`id` USING utf8 ) = 'course_seiseki_n090' LIMIT 1 ;

-- 2012-11-05 seiseki templates

INSERT INTO `seiseki_template` (`id`, `name`, `st_category`, `issue_timing`, `overall_grade_type`, `target_type`, `template_order`, `output_template_name`, `description`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `school_id`, `deleted`) VALUES
('kinder_art', 'Art', 'kinder_special_report_card', 'quarterly', NULL, 'homeroom', 4, '', '', '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('kinder_jp', 'Japanese', 'kinder_special_report_card', 'quarterly', NULL, 'homeroom', 7, '', '', '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('kinder_music', 'Music', 'kinder_special_report_card', 'quarterly', NULL, 'homeroom', 6, '', '', '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('kinder_pe', 'Physical Education', 'kinder_special_report_card', 'quarterly', NULL, 'homeroom', 5, '', '', '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('n_kinder_la_writing', 'Writing', 'kinder_report_card', 'quarterly', NULL, 'homeroom', 2, '', '', '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('n_prekinder_rc_writing', 'Writing', 'pre-kinder_report_card', 'quarterly', NULL, 'homeroom', 2, '', '', '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('prekinder_rc_art', 'Art', 'pre-kinder_special_report_card', 'quarterly', NULL, 'homeroom', 5, '', '', '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('prekinder_rc_music', 'Music', 'pre-kinder_special_report_card', 'quarterly', NULL, 'homeroom', 7, '', '', '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('prekinder_rc_pe', 'Physical Education', 'pre-kinder_special_report_card', 'quarterly', NULL, 'homeroom', 6, '', '', '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0),
('prekinder_rc_jp', 'Japanese', 'pre-kinder_special_report_card', 'quarterly', NULL, 'homeroom', 8, '', '', '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1', 'bb00ea0a-0e9d-0c9c-74e2-49ac8dc4cca4', 0);

INSERT INTO `seiseki_item` (`id`, `template_id`, `name`, `item_type`, `item_order`, `item_weight`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`) VALUES
('n_prekinder_rw01', 'n_prekinder_rc_writing', 'Can hold and use pencils, scissors and crayons correctly', '1-4', 1, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prekinder_rw02', 'n_prekinder_rc_writing', 'Can write first name', '1-4', 2, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prekinder_rw03', 'n_prekinder_rc_writing', 'Can draw a picture and tell a story to match the picture', '1-4', 3, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prekinder_rw04', 'n_prekinder_rc_writing', 'Spelling', '1-4', 4, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prekinder3_rw01', 'n_prekinder3_rc_writing', 'Can hold and use pencils, scissors and crayons correctly', '1-4', 1, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prekinder3_rw02', 'n_prekinder3_rc_writing', 'Recognizes first name', '1-4', 2, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prekinder3_rw03', 'n_prekinder3_rc_writing', 'Can write first name', '1-4', 3, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prekinder3_rw04', 'n_prekinder3_rc_writing', 'Spelling', '1-4', 4, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prek_art01', 'prekinder_rc_art', 'Overall performance', '1-4', 1, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prek_art02', 'prekinder_rc_art', 'Understands and applies the elements and principals of art.', '1-4', 2, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prek_art03', 'prekinder_rc_art', 'Communicates and expresses oneself through art.', '1-4', 3, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prek_art04', 'prekinder_rc_art', 'Stays focused and works to his/her best ability.', '1-4', 4, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prek_art05', 'prekinder_rc_art', 'Manages time well to complete projects on time.', '1-4', 5, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prek_pe01', 'prekinder_rc_pe', 'Overall performance', '1-4', 1, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prek_pe02', 'prekinder_rc_pe', 'Demonstrates age appropriate fundamental skills.', '1-4', 2, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prek_pe03', 'prekinder_rc_pe', 'Applies learned skills to game situations.', '1-4', 3, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prek_pe04', 'prekinder_rc_pe', 'Puts forth effort and participates in all activities.', '1-4', 4, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n_prek_pe05', 'prekinder_rc_pe', 'Cooperates and displays positive sportsmanship.', '1-4', 5, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_prek_music01', 'prekinder_rc_music', 'Overall performance', '1-4', 1, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_prek_music02', 'prekinder_rc_music', 'Sings with appropriate technique and expression.', '1-4', 2, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_prek_music03', 'prekinder_rc_music', 'Plays instrument with accuracy, appropriate technique.', '1-4', 3, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_prek_music04', 'prekinder_rc_music', 'Stays focused and works to his/her best ability.', '1-4', 4, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_prek_music05', 'prekinder_rc_music', 'Cooperates with others and has a positive attitude in class.', '1-4', 5, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_prek_jp01', 'prekinder_rc_jp', 'Participation', '1-4', 1, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_prek_jp02', 'prekinder_rc_jp', 'Effort', '1-4', 2, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_prek_jp03', 'prekinder_rc_jp', 'Oral communication', '1-4', 3, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_prek_jp04', 'prekinder_rc_jp', 'Knowledge of Hiragana characters', '1-4', 4, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_rw01', 'n_kinder_la_writing', 'Able to read own writing', '1-4', 1, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_rw02', 'n_kinder_la_writing', 'Writes using invented spelling', '1-4', 2, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_rw03', 'n_kinder_la_writing', 'Uses spaces between words', '1-4', 3, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_rw04', 'n_kinder_la_writing', 'Uses upper and lower case letters appropriately', '1-4', 4, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_rw05', 'n_kinder_la_writing', 'Forms letters correctly', '1-4', 5, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_rw06', 'n_kinder_la_writing', 'Understands how to use proper punctuation.', '1-4', 6, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_rw07', 'n_kinder_la_writing', 'Writes high frequency words correctly', '1-4', 7, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_rw08', 'n_kinder_la_writing', 'Able to write 2-3 sentences about a topic', '1-4', 8, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_rw09', 'n_kinder_la_writing', 'Spelling', '1-4', 9, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_art01', 'kinder_art', 'Overall performance', '1-4', 1, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_art02', 'kinder_art', 'Understands and applies the elements and principals of art.', '1-4', 2, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_art03', 'kinder_art', 'Communicates and expresses oneself through art.', '1-4', 3, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_art04', 'kinder_art', 'Stays focused and works to his/her best ability.', '1-4', 4, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_art05', 'kinder_art', 'Manages time well to complete projects on time.', '1-4', 5, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_pe01', 'kinder_pe', 'Overall performance', '1-4', 1, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_pe02', 'kinder_pe', 'Demonstrates age appropriate fundamental skills.', '1-4', 2, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_pe03', 'kinder_pe', 'Applies learned skills to game situations.', '1-4', 3, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_pe04', 'kinder_pe', 'Puts forth effort and participates in all activities.', '1-4', 4, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_pe05', 'kinder_pe', 'Cooperates and displays positive sportsmanship.', '1-4', 5, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_music01', 'kinder_music', 'Overall performance', '1-4', 1, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_music02', 'kinder_music', 'Sings with appropriate technique and expression.', '1-4', 2, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_music03', 'kinder_music', 'Plays instrument with accuracy, appropriate technique.', '1-4', 3, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_music04', 'kinder_music', 'Stays focused and works to his/her best ability.', '1-4', 4, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_music05', 'kinder_music', 'Cooperates with others and has a positive attitude in class.', '1-4', 5, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_jp01', 'kinder_jp', 'Participation', '1-4', 1, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_jp02', 'kinder_jp', 'Effort', '1-4', 2, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_jp03', 'kinder_jp', 'Oral communication', '1-4', 3, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1'),
('n2_kinder_jp04', 'kinder_jp', 'Knowledge of Hiragana characters', '1-4', 4, 1, 0, '2012-11-05 00:00:00', '2012-11-05 00:00:00', '1', '1');

INSERT INTO `course_seisekitemplates` (`id`, `course_id`, `template_id`, `date_modified`, `deleted`, `template_order`) VALUES
('n2_course_seiseki01', '1f3311b7-93eb-a821-d836-5027d0fc21ec', 'prekinder3_rc_readiness', '2012-11-05 00:00:00', 0, 1),
('n2_course_seiseki02', '1f3311b7-93eb-a821-d836-5027d0fc21ec', 'n_prekinder_rc_writing', '2012-11-05 00:00:00', 0, 2),
('n2_course_seiseki03', '1f3311b7-93eb-a821-d836-5027d0fc21ec', 'prekinder3_rc_math', '2012-11-05 00:00:00', 0, 3),
('n2_course_seiseki04', '1f3311b7-93eb-a821-d836-5027d0fc21ec', 'prekinder3_rc_reading', '2012-11-05 00:00:00', 0, 4),
('n2_course_seiseki05', '1f3311b7-93eb-a821-d836-5027d0fc21ec', 'prekinder_rc_comment', '2012-11-05 00:00:00', 0, 5),
('n2_course_seiseki06', '46c28aa2-06fd-b01c-0b87-50213fed4d6c', 'prekinder_rc_art', '2012-11-05 00:00:00', 0, 6),
('n2_course_seiseki07', '7047bc6f-4b34-22e6-1270-50213f6f95d8', 'prekinder_rc_pe', '2012-11-05 00:00:00', 0, 7),
('n2_course_seiseki08', '13638637-9efc-e230-477f-50213f90993c', 'prekinder_rc_music', '2012-11-05 00:00:00', 0, 8),
('n2_course_seiseki09', 'abc493d3-5800-4738-1976-502140283915', 'prekinder_rc_readiness', '2012-11-05 00:00:00', 0, 1),
('n2_course_seiseki10', 'abc493d3-5800-4738-1976-502140283915', 'n_prekinder_rc_writing', '2012-11-05 00:00:00', 0, 2),
('n2_course_seiseki11', 'abc493d3-5800-4738-1976-502140283915', 'prekinder_rc_math', '2012-11-05 00:00:00', 0, 3),
('n2_course_seiseki12', 'abc493d3-5800-4738-1976-502140283915', 'prekinder_rc_reading', '2012-11-05 00:00:00', 0, 4),
('n2_course_seiseki13', 'abc493d3-5800-4738-1976-502140283915', 'prekinder_rc_comment', '2012-11-05 00:00:00', 0, 5),
('n2_course_seiseki14', '4610b583-aaea-d970-120f-502140692c09', 'prekinder_rc_art', '2012-11-05 00:00:00', 0, 6),
('n2_course_seiseki15', 'c95373b5-0291-fd3f-785c-5021409f67cf', 'prekinder_rc_pe', '2012-11-05 00:00:00', 0, 7),
('n2_course_seiseki16', 'da81d8e9-32a4-e750-1b7e-502140316d33', 'prekinder_rc_music', '2012-11-05 00:00:00', 0, 8),
('n2_course_seiseki17', '796c1a7e-444d-4983-7d6c-5021401fa748', 'prekinder_rc_jp', '2012-11-05 00:00:00', 0, 9),
('n2_course_seiseki18', '77f7bea9-f389-da78-e1c5-5021409ba024', 'prekinder_rc_jp', '2012-11-05 00:00:00', 0, 9),
('n2_course_seiseki19', '6c9ca3cc-58ca-6d93-224e-5021407bde38', 'kinder_la_reading', '2012-11-05 00:00:00', 0, 1),
('n2_course_seiseki20', '6c9ca3cc-58ca-6d93-224e-5021407bde38', 'n_kinder_la_writing', '2012-11-05 00:00:00', 0, 2),
('n2_course_seiseki21', '6c9ca3cc-58ca-6d93-224e-5021407bde38', 'kinder_math', '2012-11-05 00:00:00', 0, 3),
('n2_course_seiseki22', '6c9ca3cc-58ca-6d93-224e-5021407bde38', 'kinder_comment', '2012-11-05 00:00:00', 0, 4),
('n2_course_seiseki23', '4a8e6d2b-9b2a-df1a-a9ab-502141221cc0', 'kinder_art', '2012-11-05 00:00:00', 0, 5),
('n2_course_seiseki24', '67eeb43c-f150-812c-3fad-502141282658', 'kinder_pe', '2012-11-05 00:00:00', 0, 6),
('n2_course_seiseki25', 'a6832720-eeed-048c-683d-5021419a5f32', 'kinder_music', '2012-11-05 00:00:00', 0, 7),
('n2_course_seiseki26', '6c6593b1-17f6-2654-6365-5021419b1666', 'kinder_jp', '2012-11-05 00:00:00', 0, 8),
('n2_course_seiseki27', 'b2c5f992-3a53-debc-66fb-502141f7bced', 'kinder_jp', '2012-11-05 00:00:00', 0, 8),

('n2_course_seiseki28', '796c1a7e-444d-4983-7d6c-5021401fa748', 'prekinder_rc_jp', '2012-11-05 00:00:00', 0, 9),
('n2_course_seiseki29', '77f7bea9-f389-da78-e1c5-5021409ba024', 'prekinder_rc_jp', '2012-11-05 00:00:00', 0, 9),

-- 2012-11-07 seiseki updates

UPDATE `course_seisekitemplates` SET `template_order` = '5' WHERE `course_seisekitemplates`.`id` = 'f2e86c7f-6378-63a9-500c-50974e2d7a35' LIMIT 1 ;
UPDATE `course_seisekitemplates` SET `template_order` = '6' WHERE `course_seisekitemplates`.`id` = 'c1650262-ea59-78e2-24c4-50974ede6c2e' LIMIT 1 ;
UPDATE `course_seisekitemplates` SET `template_order` = '4' WHERE `course_seisekitemplates`.`id` = 'n2_course_seiseki01' LIMIT 1 ;
UPDATE `course_seisekitemplates` SET `template_order` = '1' WHERE `course_seisekitemplates`.`id` = 'n2_course_seiseki04' LIMIT 1 ;
UPDATE `course_seisekitemplates` SET `template_order` = '4' WHERE `course_seisekitemplates`.`id` = 'f2e86c7f-6378-63a9-500c-50974e2d7a35' LIMIT 1 ;
UPDATE `course_seisekitemplates` SET `template_order` = '5' WHERE `course_seisekitemplates`.`id` = 'c1650262-ea59-78e2-24c4-50974ede6c2e' LIMIT 1 ;
UPDATE `course_seisekitemplates` SET `template_order` = '6' WHERE `course_seisekitemplates`.`id` = 'n2_course_seiseki22' LIMIT 1 ;
UPDATE `course_seisekitemplates` SET `template_order` = '4' WHERE `course_seisekitemplates`.`id` = 'n2_course_seiseki09' LIMIT 1 ;
UPDATE `course_seisekitemplates` SET `template_order` = '1' WHERE `course_seisekitemplates`.`id` = 'n2_course_seiseki12' LIMIT 1 ;