-- 2010-10-27
-- update package fees which are gessha
SELECT name, id, deleted, period_no, due_to FROM `sms_fee` WHERE `type_id` LIKE '28' AND deleted=0 AND contact_id IS NULL ;
UPDATE sms_fee SET period_no=1, due_to=27 WHERE `type_id` LIKE '28' AND deleted=0 AND contact_id IS NULL ;
UPDATE sms_fee SET period_no=0, due_to=5 WHERE `type_id` LIKE '27' AND deleted=0 AND contact_id IS NULL ;
UPDATE sms_fee SET period_no=0, due_to=5 WHERE `type_id` LIKE '17' AND deleted=0 AND contact_id IS NULL ;

-- 2010-11-10
-- update contacts for send_document_work

SELECT id, send_document_work, date_entered, date_modified, idnumber, last_name, first_name FROM `contacts` 
WHERE deleted =0 AND smsgroup='student'  AND send_document_work='on'
 
UPDATE contacts SET send_document_work='off' WHERE deleted =0 AND smsgroup='student' AND send_document_work='on'

-- 2010-11-23

CREATE TABLE IF NOT EXISTS `exam_student` (
  `contact_id` varchar(36) NOT NULL DEFAULT '',
  `exam_id` varchar(36) NOT NULL,
  `memo` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`contact_id`,`exam_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 2010-11-29
SELECT person_contacts.*, contacts.last_name, contacts.smsgroup
FROM person_contacts, contacts
WHERE person_contacts.deleted =0 AND contacts.deleted=0 AND contacts.smsgroup='parent'
	AND person_contacts.person_id = contacts.id AND person_contacts.person_contact_status='42' ;
	
SELECT person_contacts.*, s1.last_name, s1.smsgroup,  p1.last_name, p1.smsgroup
FROM person_contacts, contacts s1, contacts p1
WHERE person_contacts.deleted =0 AND s1.deleted=0 AND p1.deleted=0 
	AND person_contacts.person_contact_status='42'
	AND p1.smsgroup='parent' AND person_contacts.person_id = p1.id 
	AND s1.smsgroup='student' AND person_contacts.contact_id = s1.id ;
	
UPDATE person_contacts, contacts
SET person_contacts.person_contact_status='41'
WHERE person_contacts.deleted =0 AND contacts.deleted=0 AND contacts.smsgroup='parent'
	AND person_contacts.person_id = contacts.id AND person_contacts.person_contact_status='42'
	
-- 2011-01-03  update
ALTER TABLE `batch_manager` ADD `query` TEXT NOT NULL ,
ADD `filename` VARCHAR( 255 ) NOT NULL DEFAULT '';
ALTER TABLE `batch_manager` ADD `freeparam1` VARCHAR( 255 ) NOT NULL ,
ADD `freeparam2` VARCHAR( 255 ) NOT NULL ,
ADD `freeparam3` VARCHAR( 255 ) NOT NULL ;
ALTER TABLE `batch_items` ADD `item_info3` VARCHAR( 255 ) NOT NULL DEFAULT '' AFTER `item_info2` ,
ADD `item_info4` VARCHAR( 255 ) NOT NULL DEFAULT '' AFTER `item_info3`;
ALTER TABLE `batch_items` CHANGE `item_info1` `item_info1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
CHANGE `item_info2` `item_info2` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';


-- 2011-02-10 gakko table updates
-- OPERATIONS ON GLOBAL gakkoglobal DB
-- gakko to update 
CREATE TABLE IF NOT EXISTS `sendai` (
  `id` varchar(10) NOT NULL,
  `name` varchar(128) NOT NULL,
  `category` varchar(32) NOT NULL,
  `subcategory` varchar(32) default NULL,
  `state` varchar(32) NOT NULL,
  `city` varchar(64) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO `sendai` (`id`, `name`, `category`, `subcategory`, `state`, `city`) VALUES
('K17005591', '石巻北高等学校', '高等学校', '公立', '宮城県', '石巻市'),
('K17006307', '聖和学園三神峯高校', '高等学校', '私立', '宮城県', '仙台市'),
('K17006318', '聖和学園薬師堂高校', '高等学校', '私立', '宮城県', '仙台市'),
('K17007180', '岩ヶ崎高等学校（鶯沢校舎）', '高等学校', '公立', '宮城県', '栗原市'),
('K17022044', '尚絅学院高等学校', '高等学校', '私立', '宮城県', '仙台市'),
('K17026277', '水産高等学校（石巻）', '高等学校', '公立', '宮城県', '石巻市'),
('K17029041', '石巻市立女子商業高等学校', '高等学校', '公立', '宮城県', '石巻市'),
('K17034186', '宮城第一高等学校', '高等学校', '公立', '宮城県', '仙台市'),
('K17034212', '仙台三桜高等学校', '高等学校', '公立', '宮城県', '仙台市'),
('K17034278', '仙台二華高等学校', '高等学校', '公立', '宮城県', '仙台市'),
('K17042420', '宮城農業高等学校', '高等学校', '公立', '宮城県', '名取市');
INSERT INTO `sendai` (`id`, `name`, `category`, `subcategory`, `state`, `city`) VALUES 
('K17008826', '宮城学院高等学校', '高等学校', '私立', '宮城県', '仙台市');

SELECT gm.id, gm.name, sendai.id, sendai.name FROM `sendai` INNER JOIN gakkomaster gm ON gm.id=sendai.id; 
UPDATE gakkomaster, sendai SET gakkomaster.name=sendai.name WHERE gakkomaster.id=sendai.id ;

-- on global gakkomaster table additional schools
INSERT INTO `gakkomaster` (`id`, `name`, `category`, `subcategory`, `state`, `city`) VALUES
('A00000001', '明成高等学校', '高等学校', '私立', '宮城県', '仙台市'),
('A00000002', '石巻北高等学校飯野川校', '高等学校', '公立', '宮城県', '石巻市'),
('A00000003', '加美農業高等学校', '高等学校', '公立', '宮城県', '色麻町'),
('A00000004', '仙台市立仙台大志高等学校', '高等学校', '公立', '宮城県', '仙台市'),
('A00000005', '仙台青陵教育学校', '高等学校', '公立', '宮城県', '仙台市'),
('A00000006', '田尻さくら高等学校', '高等学校', '公立', '宮城県', '大崎市'),
('A00000007', '東北学院高等学校', '高等学校', '私立', '宮城県', '仙台市'),
('A00000008', '宮城工業高等学校', '高等学校', '公立', '宮城県', '仙台市'),
('A00000009', '石巻市立女子高等学校', '高等学校', '公立', '宮城県', '石巻市');

-- OPERATIONS ON LOCAL asunaro DB
-- first again create the above sendai table

SELECT gm.id, gm.name, sendai.id, sendai.name FROM `sendai` INNER JOIN gakkomaster gm ON gm.global_id=sendai.id;

UPDATE gakkomaster, sendai SET gakkomaster.name=sendai.name WHERE gakkomaster.global_id=sendai.id ;



-- updates for upgrade on 20110331

ALTER TABLE `contacts` ADD `grade_entered` VARCHAR( 10 ) AFTER `grade` ;
UPDATE contacts SET grade_entered=grade WHERE smsgroup='student' ; 
UPDATE contacts SET grade=grade+1 WHERE smsgroup='student' AND grade IS NOT NULL AND grade!=''
	AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken', '18');
UPDATE contacts SET grade='general' WHERE smsgroup='student' AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken') AND grade > 17 ;

-- 2011-01-13
ALTER TABLE `emailman` ADD `send_options` INT NOT NULL DEFAULT '0',
ADD `status` INT NOT NULL DEFAULT '0';

-- 2011-01-20
CREATE TABLE IF NOT EXISTS `config_export_fields` (
  `module` varchar(32) NOT NULL,
  `suffix` varchar(32) NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `field` varchar(64) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `date_updated` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 2011-02-02
ALTER TABLE `bankexport_salesnos` CHANGE `salesno` `salesno` VARCHAR( 20 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ;
ALTER TABLE `combini_history` CHANGE `salesno` `salesno` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `combini_history` ADD `student_id` VARCHAR( 20 ) NULL DEFAULT NULL;

-- 2011-02-17
ALTER TABLE `combini_history` DROP `branch_id`;

-- 2011-03-11
ALTER TABLE `product` ADD `subcategory` VARCHAR( 64 ) NULL AFTER `productcategory_id` ;
CREATE TABLE IF NOT EXISTS `productinout` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) DEFAULT NULL,
  `created_by` varchar(36) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `product_id` varchar(36) DEFAULT NULL,
  `category` varchar(16) DEFAULT NULL,
  `price` decimal(20,2) NOT NULL DEFAULT '0.00',
  `date_buy_sell` date DEFAULT NULL,
  `quantity` int(5) NOT NULL DEFAULT '0',
  `school_id` varchar(36) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- gessha sakusei mondai  2011-07-12

SELECT * FROM `sms_transaction` WHERE `type_id` LIKE '28' AND `deleted` =0
AND sell_date='2011-07-09 15:00:00'

SELECT DISTINCT date_entered, COUNT(date_entered) FROM `sms_transaction` WHERE `type_id` LIKE '28' AND `deleted` =0
AND sell_date='2011-07-09 15:00:00'
GROUP BY date_entered

SELECT next_gessha_applied, source_gessha_id, sms_fee.* FROM `sms_fee` WHERE `type_id` LIKE '28' AND `deleted` =0
AND sell_date='2011-06-09 15:00:00'

SELECT DISTINCT date_entered, COUNT(date_entered) FROM `sms_fee` WHERE `type_id` LIKE '28' AND `deleted` =0
AND sell_date='2011-07-09 15:00:00' GROUP BY date_entered


SELECT next_gessha_applied, source_gessha_id, sms_fee.* FROM `sms_fee` WHERE `type_id` LIKE '28' AND `deleted` =0
AND sell_date='2011-06-09 15:00:00' AND next_gessha_applied=1


SELECT fee6.next_gessha_applied, fee7.source_gessha_id, fee7.* FROM sms_fee as fee7
INNER JOIN sms_fee as fee6 ON fee7.source_gessha_id=fee6.id AND fee6.deleted=0 
WHERE fee7.type_id='28' AND fee7.deleted =0 AND fee7.sell_date='2011-07-09 15:00:00' 


SELECT sms_fee.id, sms_fee.deleted, sms_transaction.fee_id, sms_transaction.id, sms_transaction.deleted FROM `sms_fee` 
LEFT JOIN sms_transaction ON sms_transaction.fee_id=sms_fee.id AND sms_transaction.deleted=0
WHERE sms_fee.type_id='28' AND sms_fee.deleted =0 AND sms_fee.sell_date='2011-07-09 15:00:00' 

SELECT sms_fee.id, sms_fee.deleted, sms_transaction.fee_id, sms_transaction.id, sms_transaction.deleted 
FROM sms_fee, sms_transaction 
WHERE sms_fee.type_id='28' AND sms_fee.deleted =0 AND sms_fee.sell_date='2011-07-09 15:00:00' 
AND sms_transaction.fee_id=sms_fee.id AND sms_transaction.deleted=0


UPDATE sms_fee, sms_transaction SET  sms_transaction.deleted=1
WHERE sms_fee.type_id='28' AND sms_fee.deleted =0 AND sms_fee.sell_date='2011-07-09 15:00:00' 
AND sms_transaction.fee_id=sms_fee.id AND sms_transaction.deleted=0

UPDATE sms_fee SET  sms_fee.deleted=1
WHERE sms_fee.type_id='28' AND sms_fee.deleted =0 AND sms_fee.sell_date='2011-07-09 15:00:00' 

-- ryoking template update 2011-07-12

SELECT * FROM sms_fee
WHERE type_id='28' AND deleted=0 AND contact_id IS NULL AND (period_no!='1' OR due_to!='27')

UPDATE sms_fee SET period_no='1', due_to='27'
WHERE type_id='28' AND deleted=0 AND contact_id IS NULL AND (period_no!='1' OR due_to!='27')


-- 2014-04-01 gakunen updates

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

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