--  initial settings
UPDATE `childvillage`.`sms_transactiontype` SET `deleted` = '1' WHERE `sms_transactiontype`.`id` = '28' LIMIT 1 ;
UPDATE `childvillage`.`sms_transactiontype` SET `deleted` = '1' WHERE `sms_transactiontype`.`id` = '17' LIMIT 1 ;
UPDATE `childvillage`.`sms_transactiontype` SET `deleted` = '1' WHERE `sms_transactiontype`.`id` = '8' LIMIT 1 ;
UPDATE `childvillage`.`sms_transactiontype` SET `deleted` = '1' WHERE `sms_transactiontype`.`id` = '9' LIMIT 1 ;

SELECT last_school_name, last_school_dept from contacts WHERE 1;
UPDATE contacts SET last_school_name = last_school_dept WHERE 1;
UPDATE contacts SET last_school_dept=NULL WHERE 1 ;

SELECT contacts.id, contacts.application_date, contacts3.id, contacts.custom_var12, contacts3.custom_var12
FROM contacts, contacts3 WHERE contacts.id=contacts3.id

UPDATE contacts, contacts3 SET contacts.application_date=contacts3.application_date, contacts.custom_var12=contacts3.custom_var12
WHERE contacts.id=contacts3.id

SELECT MAX(assistant) FROM contacts WHERE smsgroup='student' AND deleted=0
SELECT DISTINCT assistant FROM contacts WHERE smsgroup='student' AND deleted=0

-- fee subcategory settings
ALTER TABLE `sms_fee` ADD `extra_kubun1` VARCHAR( 256 )  NULL ;

-- HP fee assignment related

-- SELECT * FROM `sms_salesgroup` WHERE `salesno` IN ('610ad5aa-1', '5bff01b9-0', '54191d04-a', 'a45efbc6-b');

INSERT INTO `sms_salesgroup` (`id`, `name`, `event_id`, `contact_id`, `account_id`, `type_id`, `occurences`, `occurences_hour`, `deleted`, `date_entered`, `date_modified`, `sell_date`, `modified_user_id`, `created_by`, `basic_fee`, `unit_type`, `unit_no`, `period`, `period_no`, `due_to`, `description`, `applied_contact`, `discount_percentage`, `discount_price`, `salesno`, `bank_account`, `hakkoubi`, `receipt_hakkoubi`, `seikyusaki`, `tantosha`, `item`, `school_code`) VALUES
('54191d04-af92-c19d-8eb3-4de302dbfa72', 'U-8 新規エントリー用料金', NULL, NULL, NULL, NULL, 1, 1, 0, '2011-05-30 02:34:04', '2011-05-30 08:40:39', '1999-11-29 16:00:00', '1', '1', 91700, '0', 0, 0, 0, '31', '削除しないでください。削除すると、エントリー時に料金が請求できません。テクノピアン株式会社 ', 0, 0, NULL, '54191d04-a', NULL, '0000-00-00', NULL, NULL, NULL, NULL, NULL),
('5bff01b9-0463-f825-1b49-4de302571889', 'U-10 新規エントリー用料金', NULL, NULL, NULL, NULL, 1, 1, 0, '2011-05-30 02:35:19', '2011-05-30 08:40:48', '1999-11-29 16:00:00', '1', '1', 91700, '0', 0, 0, 0, '31', '削除しないでください。削除すると、エントリー時に料金が請求できません。テクノピアン株式会社', 0, 0, NULL, '5bff01b9-0', NULL, '0000-00-00', NULL, NULL, NULL, NULL, NULL),
('610ad5aa-1040-ef1f-1790-4de302367a13', 'U-12 新規エントリー用料金', NULL, NULL, NULL, NULL, 1, 1, 0, '2011-05-30 02:36:15', '2011-05-30 08:40:57', '1999-11-29 16:00:00', '1', '1', 91700, '0', 0, 0, 0, '31', '削除しないでください。削除すると、エントリー時に料金が請求できません。テクノピアン株式会社', 0, 0, NULL, '610ad5aa-1', NULL, '0000-00-00', NULL, NULL, NULL, NULL, NULL),
('a45efbc6-beac-ef38-d73d-4dd4f2a85649', 'U-6 新規エントリー用料金', NULL, NULL, NULL, NULL, 1, 1, 0, '2011-05-19 10:35:54', '2011-05-30 08:41:07', '1999-11-29 16:00:00', '1', '1', 60700, '0', 0, 0, 0, '31', '削除しないでください。削除すると、エントリー時に料金が請求できません。テクノピアン株式会社', 0, 0, NULL, 'a45efbc6-b', NULL, '0000-00-00', NULL, NULL, NULL, NULL, NULL);

-- SELECT * FROM `sms_fee` WHERE `salesno` IN ('610ad5aa-1', '5bff01b9-0', '54191d04-a', 'a45efbc6-b') AND contact_id IS NULL AND deleted=0;

INSERT INTO `sms_fee` (`id`, `school_id`, `name`, `event_id`, `contact_id`, `account_id`, `type_id`, `deleted`, `date_entered`, `date_modified`, `sell_date`, `modified_user_id`, `created_by`, `basic_fee`, `unit_type`, `unit_no`, `period`, `period_no`, `due_to`, `description`, `applied_contact`, `discount_percentage`, `discount_price`, `salesno`, `start_date`, `end_date`, `product_id`, `product_count`, `ticket_count`, `ticket_valid`, `jikyu_rate`, `event_credit`, `fee_subcategory`, `source_gessha_id`, `next_gessha_applied`, `extra_kubun1`) VALUES
('3825b737-12d1-fa20-1388-4de302160c97', 'sssssss-1111', '月謝(4,800円×4ヶ月分)', NULL, NULL, NULL, '27', 0, '2011-05-27 17:04:00', '2011-05-31 05:27:13', '0000-00-00 00:00:00', '1', '1', 19200, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, 'a45efbc6-b', '2011-09-01', '2011-05-31', NULL, 0, 0, NULL, '4', NULL, 'U-6', NULL, '0', '月謝'),
('3a0fc2ad-da9d-f126-39fb-4de302632c6d', 'sssssss-1111', 'スクールキット', NULL, NULL, NULL, '27', 0, '2011-05-27 17:04:00', '2011-05-30 02:33:40', '0000-00-00 00:00:00', '1', '1', 8500, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, 'a45efbc6-b', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-6', NULL, '0', NULL),
('3be16e37-1c81-e8bb-6840-4de3027f96d4', 'sssssss-1111', '入会金', NULL, NULL, NULL, '27', 0, '2011-05-30 11:06:00', '2011-05-30 02:33:40', '0000-00-00 00:00:00', '1', '1', 30000, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, 'a45efbc6-b', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-6', NULL, '0', '入会金'),
('3dbae8eb-8337-87ed-2aa7-4de30280c740', 'sssssss-1111', '年会費', NULL, NULL, NULL, '27', 0, '2011-05-30 11:07:00', '2011-05-30 02:33:40', '0000-00-00 00:00:00', '1', '1', 3000, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, 'a45efbc6-b', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-6', NULL, '0', '年会費'),
('edbaf550-68ef-8ad1-3aec-4de302666afb', 'sssssss-1111', '月謝(6,800円×4ヶ月分)', NULL, NULL, NULL, '27', 0, '2011-05-27 17:04:00', '2011-05-31 05:27:18', '0000-00-00 00:00:00', '1', '1', 27200, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '54191d04-a', '2011-09-01', '2011-05-31', NULL, 0, 0, NULL, '4', NULL, 'U-8', NULL, '0', '月謝'),
('efab3fff-8aeb-f354-95d8-4de30209fbf6', 'sssssss-1111', 'スクールキット', NULL, NULL, NULL, '27', 0, '2011-05-27 17:04:00', '2011-05-30 02:37:53', '0000-00-00 00:00:00', '1', '1', 31500, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '54191d04-a', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-8', NULL, '0', NULL),
('f177ab71-ee74-33b2-9208-4de302012140', 'sssssss-1111', '入会金', NULL, NULL, NULL, '27', 0, '2011-05-30 11:06:00', '2011-05-30 02:34:57', '0000-00-00 00:00:00', '1', '1', 30000, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '54191d04-a', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-8', NULL, '0', '入会金'),
('f37081c3-24af-0831-9c64-4de302f58e1d', 'sssssss-1111', '年会費', NULL, NULL, NULL, '27', 0, '2011-05-30 11:07:00', '2011-05-30 02:35:04', '0000-00-00 00:00:00', '1', '1', 3000, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '54191d04-a', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-8', NULL, '0', '年会費'),
('2428617e-6136-db1c-1d95-4de302ed6a14', 'sssssss-1111', '月謝(6,800円×4ヶ月分)', NULL, NULL, NULL, '27', 0, '2011-05-27 17:04:00', '2011-05-31 05:27:25', '0000-00-00 00:00:00', '1', '1', 27200, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '5bff01b9-0', '2011-09-01', '2011-05-31', NULL, 0, 0, NULL, '4', NULL, 'U-10', NULL, '0', '月謝'),
('26077c43-75f0-e029-a697-4de302f8f0a9', 'sssssss-1111', 'スクールキット', NULL, NULL, NULL, '27', 0, '2011-05-27 17:04:00', '2011-05-30 02:38:04', '0000-00-00 00:00:00', '1', '1', 31500, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '5bff01b9-0', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-10', NULL, '0', NULL),
('2806445e-6993-0688-2654-4de30287b662', 'sssssss-1111', '入会金', NULL, NULL, NULL, '27', 0, '2011-05-30 11:06:00', '2011-05-30 02:35:54', '0000-00-00 00:00:00', '1', '1', 30000, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '5bff01b9-0', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-10', NULL, '0', '入会金'),
('29f9724a-6631-7265-76eb-4de30266e9d8', 'sssssss-1111', '年会費', NULL, NULL, NULL, '27', 0, '2011-05-30 11:07:00', '2011-05-30 02:35:59', '0000-00-00 00:00:00', '1', '1', 3000, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '5bff01b9-0', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-10', NULL, '0', '年会費'),
('bdd7ce61-d6cb-5a39-0f3c-4de302b6902e', 'sssssss-1111', '月謝(6,800円×4ヶ月分)', NULL, NULL, NULL, '27', 0, '2011-05-27 17:04:00', '2011-05-31 05:27:30', '0000-00-00 00:00:00', '1', '1', 27200, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '610ad5aa-1', '2011-09-01', '2011-05-31', NULL, 0, 0, NULL, '4', NULL, 'U-12', NULL, '0', '月謝'),
('bf0efa7c-9d89-f79d-f133-4de302cb13a5', 'sssssss-1111', 'スクールキット', NULL, NULL, NULL, '27', 0, '2011-05-27 17:04:00', '2011-05-30 02:37:24', '0000-00-00 00:00:00', '1', '1', 31500, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '610ad5aa-1', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-12', NULL, '0', NULL),
('c04b3395-65b2-9b19-ccf3-4de3021721cd', 'sssssss-1111', '入会金', NULL, NULL, NULL, '27', 0, '2011-05-30 11:06:00', '2011-05-30 02:36:51', '0000-00-00 00:00:00', '1', '1', 30000, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '610ad5aa-1', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-12', NULL, '0', '入会金'),
('c183c964-c99d-e398-f08b-4de302ec891e', 'sssssss-1111', '年会費', NULL, NULL, NULL, '27', 0, '2011-05-30 11:07:00', '2011-05-30 02:36:57', '0000-00-00 00:00:00', '1', '1', 3000, '0', 1, 1, 1, '31', NULL, 0, 0, NULL, '610ad5aa-1', '2011-05-01', '2011-05-31', NULL, 0, 0, NULL, '1', NULL, 'U-12', NULL, '0', '年会費');

-- combini sakusei id
ALTER TABLE `sms_transaction` ADD `combini_sakusei_id` INT NOT NULL DEFAULT '0';
ALTER TABLE `sms_transaction` CHANGE `combini_sakusei_id` `combini_sakusei_id` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0';
ALTER TABLE `sms_transaction` ADD INDEX ( `combini_sakusei_id` );

-- insert a new row into config_lock table to store the last combini unique id
INSERT INTO config_lock ( `category` , `name` , `value`) VALUES ( 'unique_id', 'combini_sakusei_id', '0' );

-- get detailed contact information for prospect list 2011-07-01
SELECT contacts.id, CONCAT_WS(' ',contacts.last_name, contacts.first_name) AS contact_name, smsgroup,
	email1, send_document_home, send_document_work, email_opt_out, invalid_email, 
	CONCAT_WS(' ',primary_address_state, primary_address_city, primary_address_street) AS home_address ,
	primary_address_postalcode, alt_address_postalcode, 
	CONCAT_WS(' ',alt_address_state, alt_address_city, alt_address_street) AS work_address,
	contacts.*
FROM prospect_lists_prospects 
INNER JOIN contacts ON prospect_lists_prospects.related_id = contacts.id 
WHERE prospect_lists_prospects.deleted=0 AND contacts.deleted=0 
	AND prospect_lists_prospects.prospect_list_id='52e6f2d6-9d46-2606-1d9b-4e0962dfdfad'

-- get detailed contact information for prospect list 2011-07-13
SELECT contacts.id, CONCAT_WS(' ',contacts.last_name, contacts.first_name) AS contact_name, smsgroup,
	email1, send_document_home, send_document_work, email_opt_out, invalid_email, 
	CONCAT_WS(' ',primary_address_state, primary_address_city, primary_address_street) AS home_address ,
	primary_address_postalcode, alt_address_postalcode, 
	CONCAT_WS(' ',alt_address_state, alt_address_city, alt_address_street) AS work_address,
	contacts.*
FROM prospect_lists_prospects 
INNER JOIN contacts ON prospect_lists_prospects.related_id = contacts.id 
WHERE prospect_lists_prospects.deleted=0 AND contacts.deleted=0 
	AND prospect_lists_prospects.prospect_list_id='52e6f2d6-9d46-2606-1d9b-4e0962dfdfad'


SELECT contacts.id, CONCAT_WS(' ',contacts.last_name, contacts.first_name) AS contact_name, smsgroup,
	email1, send_document_home, send_document_work, email_opt_out, invalid_email, 
	CONCAT_WS(' ',primary_address_state, primary_address_city, primary_address_street) AS home_address ,
	primary_address_postalcode, alt_address_postalcode, 
	CONCAT_WS(' ',alt_address_state, alt_address_city, alt_address_street) AS work_address,
	contacts.*
FROM prospect_lists_prospects 
INNER JOIN contacts ON prospect_lists_prospects.related_id = contacts.id 
WHERE prospect_lists_prospects.deleted=0 AND contacts.deleted=0 
	AND prospect_lists_prospects.prospect_list_id='79104f07-f21d-1a4e-d73e-4e2939fed1bb'
	
-- delete some fees, transaction and salesno

SELECT * FROM sms_salesgroup WHERE salesno IN (
 '0100000176', '0100000177', '0100000178', '0100000179', '0100000180', '0100000181', '0100000182', '0100000183', '0100000184', '0100000185', 
 '0100000186', '0100000187', '0100000188', '0100000189', '0100000190', '0100000191', '0100000192', '0100000193', '0100000194', '0100000195',
 '0100000196', '0100000197', '0100000198', '0100000199', '0100000200', '0100000201', '0100000202', '0100000203', '0100000204', '0100000205',
 '0100000206', '0100000207', '0100000208', '0100000209', '0100000212', '0100000213', '0100000215', '0100000216', '0100000217', '0100000218',
 '0100000220', '0100000221', '0100000222', '0100000223', '0100000224', '0100000225', '0100000226', '0100000227', '0100000228', '0100000230',
 '0100000231', '0100000232', '0100000233', '0100000234', '0100000236', '0100000237', '0100000238', '0100000239', '0100000240', '0100000241',
 '0100000242', '0100000243', '0100000244', '0100000245', '0100000246', '0100000247', '0100000248', '0100000249', '0100000250', '0100000252',
 '0100000253', '0100000254', '0100000255', '0100000256', '0100000259', '0100000260', '0100000261', '0100000262', '0100000263', '0100000264',
 '0100000265', '0100000266', '0100000267', '0100000268', '0100000269', '0100000270', '0100000271', '0100000272', '0100000273', '0100000274',
 '0100000275', '0100000276', '0100000277', '0100000278', '0100000279', '0100000280', '0100000281', '0100000282', '0100000283', '0100000284',
 '0100000285', '0100000286', '0100000287', '0100000288', '0100000289', '0100000290', '0100000291', '0100000292', '0100000293', '0100000295',
 '0100000296', '0100000298', '0100000299', '0100000300', '0100000301', '0100000303', '0100000304', '0100000305', '0100000306', '0100000307',
 '0100000308', '0100000309', '0100000310', '0100000311', '0100000313', '0100000314', '0100000315', '0100000316', '0100000317', '0100000318',
 '0100000319', '0100000320', '0100000321', '0100000322', '0100000323', '0100000324', '0100000325', '0100000326', '0100000327', '0100000328',
 '0100000329', '0100000330', '0100000331', '0100000332', '0100000333', '0100000335', '0100000336', '0100000337', '0100000338', '0100000339') ;
 
SELECT * FROM sms_fee WHERE salesno IN (
 '0100000176', '0100000177', '0100000178', '0100000179', '0100000180', '0100000181', '0100000182', '0100000183', '0100000184', '0100000185', 
 '0100000186', '0100000187', '0100000188', '0100000189', '0100000190', '0100000191', '0100000192', '0100000193', '0100000194', '0100000195',
 '0100000196', '0100000197', '0100000198', '0100000199', '0100000200', '0100000201', '0100000202', '0100000203', '0100000204', '0100000205',
 '0100000206', '0100000207', '0100000208', '0100000209', '0100000212', '0100000213', '0100000215', '0100000216', '0100000217', '0100000218',
 '0100000220', '0100000221', '0100000222', '0100000223', '0100000224', '0100000225', '0100000226', '0100000227', '0100000228', '0100000230',
 '0100000231', '0100000232', '0100000233', '0100000234', '0100000236', '0100000237', '0100000238', '0100000239', '0100000240', '0100000241',
 '0100000242', '0100000243', '0100000244', '0100000245', '0100000246', '0100000247', '0100000248', '0100000249', '0100000250', '0100000252',
 '0100000253', '0100000254', '0100000255', '0100000256', '0100000259', '0100000260', '0100000261', '0100000262', '0100000263', '0100000264',
 '0100000265', '0100000266', '0100000267', '0100000268', '0100000269', '0100000270', '0100000271', '0100000272', '0100000273', '0100000274',
 '0100000275', '0100000276', '0100000277', '0100000278', '0100000279', '0100000280', '0100000281', '0100000282', '0100000283', '0100000284',
 '0100000285', '0100000286', '0100000287', '0100000288', '0100000289', '0100000290', '0100000291', '0100000292', '0100000293', '0100000295',
 '0100000296', '0100000298', '0100000299', '0100000300', '0100000301', '0100000303', '0100000304', '0100000305', '0100000306', '0100000307',
 '0100000308', '0100000309', '0100000310', '0100000311', '0100000313', '0100000314', '0100000315', '0100000316', '0100000317', '0100000318',
 '0100000319', '0100000320', '0100000321', '0100000322', '0100000323', '0100000324', '0100000325', '0100000326', '0100000327', '0100000328',
 '0100000329', '0100000330', '0100000331', '0100000332', '0100000333', '0100000335', '0100000336', '0100000337', '0100000338', '0100000339') ;
 
SELECT * FROM sms_transaction WHERE salesno IN (
 '0100000176', '0100000177', '0100000178', '0100000179', '0100000180', '0100000181', '0100000182', '0100000183', '0100000184', '0100000185', 
 '0100000186', '0100000187', '0100000188', '0100000189', '0100000190', '0100000191', '0100000192', '0100000193', '0100000194', '0100000195',
 '0100000196', '0100000197', '0100000198', '0100000199', '0100000200', '0100000201', '0100000202', '0100000203', '0100000204', '0100000205',
 '0100000206', '0100000207', '0100000208', '0100000209', '0100000212', '0100000213', '0100000215', '0100000216', '0100000217', '0100000218',
 '0100000220', '0100000221', '0100000222', '0100000223', '0100000224', '0100000225', '0100000226', '0100000227', '0100000228', '0100000230',
 '0100000231', '0100000232', '0100000233', '0100000234', '0100000236', '0100000237', '0100000238', '0100000239', '0100000240', '0100000241',
 '0100000242', '0100000243', '0100000244', '0100000245', '0100000246', '0100000247', '0100000248', '0100000249', '0100000250', '0100000252',
 '0100000253', '0100000254', '0100000255', '0100000256', '0100000259', '0100000260', '0100000261', '0100000262', '0100000263', '0100000264',
 '0100000265', '0100000266', '0100000267', '0100000268', '0100000269', '0100000270', '0100000271', '0100000272', '0100000273', '0100000274',
 '0100000275', '0100000276', '0100000277', '0100000278', '0100000279', '0100000280', '0100000281', '0100000282', '0100000283', '0100000284',
 '0100000285', '0100000286', '0100000287', '0100000288', '0100000289', '0100000290', '0100000291', '0100000292', '0100000293', '0100000295',
 '0100000296', '0100000298', '0100000299', '0100000300', '0100000301', '0100000303', '0100000304', '0100000305', '0100000306', '0100000307',
 '0100000308', '0100000309', '0100000310', '0100000311', '0100000313', '0100000314', '0100000315', '0100000316', '0100000317', '0100000318',
 '0100000319', '0100000320', '0100000321', '0100000322', '0100000323', '0100000324', '0100000325', '0100000326', '0100000327', '0100000328',
 '0100000329', '0100000330', '0100000331', '0100000332', '0100000333', '0100000335', '0100000336', '0100000337', '0100000338', '0100000339') ;


DELETE FROM sms_salesgroup WHERE salesno IN (
 '0100000176', '0100000177', '0100000178', '0100000179', '0100000180', '0100000181', '0100000182', '0100000183', '0100000184', '0100000185', 
 '0100000186', '0100000187', '0100000188', '0100000189', '0100000190', '0100000191', '0100000192', '0100000193', '0100000194', '0100000195',
 '0100000196', '0100000197', '0100000198', '0100000199', '0100000200', '0100000201', '0100000202', '0100000203', '0100000204', '0100000205',
 '0100000206', '0100000207', '0100000208', '0100000209', '0100000212', '0100000213', '0100000215', '0100000216', '0100000217', '0100000218',
 '0100000220', '0100000221', '0100000222', '0100000223', '0100000224', '0100000225', '0100000226', '0100000227', '0100000228', '0100000230',
 '0100000231', '0100000232', '0100000233', '0100000234', '0100000236', '0100000237', '0100000238', '0100000239', '0100000240', '0100000241',
 '0100000242', '0100000243', '0100000244', '0100000245', '0100000246', '0100000247', '0100000248', '0100000249', '0100000250', '0100000252',
 '0100000253', '0100000254', '0100000255', '0100000256', '0100000259', '0100000260', '0100000261', '0100000262', '0100000263', '0100000264',
 '0100000265', '0100000266', '0100000267', '0100000268', '0100000269', '0100000270', '0100000271', '0100000272', '0100000273', '0100000274',
 '0100000275', '0100000276', '0100000277', '0100000278', '0100000279', '0100000280', '0100000281', '0100000282', '0100000283', '0100000284',
 '0100000285', '0100000286', '0100000287', '0100000288', '0100000289', '0100000290', '0100000291', '0100000292', '0100000293', '0100000295',
 '0100000296', '0100000298', '0100000299', '0100000300', '0100000301', '0100000303', '0100000304', '0100000305', '0100000306', '0100000307',
 '0100000308', '0100000309', '0100000310', '0100000311', '0100000313', '0100000314', '0100000315', '0100000316', '0100000317', '0100000318',
 '0100000319', '0100000320', '0100000321', '0100000322', '0100000323', '0100000324', '0100000325', '0100000326', '0100000327', '0100000328',
 '0100000329', '0100000330', '0100000331', '0100000332', '0100000333', '0100000335', '0100000336', '0100000337', '0100000338', '0100000339') ;
DELETE FROM sms_fee WHERE salesno IN (
 '0100000176', '0100000177', '0100000178', '0100000179', '0100000180', '0100000181', '0100000182', '0100000183', '0100000184', '0100000185', 
 '0100000186', '0100000187', '0100000188', '0100000189', '0100000190', '0100000191', '0100000192', '0100000193', '0100000194', '0100000195',
 '0100000196', '0100000197', '0100000198', '0100000199', '0100000200', '0100000201', '0100000202', '0100000203', '0100000204', '0100000205',
 '0100000206', '0100000207', '0100000208', '0100000209', '0100000212', '0100000213', '0100000215', '0100000216', '0100000217', '0100000218',
 '0100000220', '0100000221', '0100000222', '0100000223', '0100000224', '0100000225', '0100000226', '0100000227', '0100000228', '0100000230',
 '0100000231', '0100000232', '0100000233', '0100000234', '0100000236', '0100000237', '0100000238', '0100000239', '0100000240', '0100000241',
 '0100000242', '0100000243', '0100000244', '0100000245', '0100000246', '0100000247', '0100000248', '0100000249', '0100000250', '0100000252',
 '0100000253', '0100000254', '0100000255', '0100000256', '0100000259', '0100000260', '0100000261', '0100000262', '0100000263', '0100000264',
 '0100000265', '0100000266', '0100000267', '0100000268', '0100000269', '0100000270', '0100000271', '0100000272', '0100000273', '0100000274',
 '0100000275', '0100000276', '0100000277', '0100000278', '0100000279', '0100000280', '0100000281', '0100000282', '0100000283', '0100000284',
 '0100000285', '0100000286', '0100000287', '0100000288', '0100000289', '0100000290', '0100000291', '0100000292', '0100000293', '0100000295',
 '0100000296', '0100000298', '0100000299', '0100000300', '0100000301', '0100000303', '0100000304', '0100000305', '0100000306', '0100000307',
 '0100000308', '0100000309', '0100000310', '0100000311', '0100000313', '0100000314', '0100000315', '0100000316', '0100000317', '0100000318',
 '0100000319', '0100000320', '0100000321', '0100000322', '0100000323', '0100000324', '0100000325', '0100000326', '0100000327', '0100000328',
 '0100000329', '0100000330', '0100000331', '0100000332', '0100000333', '0100000335', '0100000336', '0100000337', '0100000338', '0100000339') ;
DELETE FROM sms_transaction WHERE salesno IN (
 '0100000176', '0100000177', '0100000178', '0100000179', '0100000180', '0100000181', '0100000182', '0100000183', '0100000184', '0100000185', 
 '0100000186', '0100000187', '0100000188', '0100000189', '0100000190', '0100000191', '0100000192', '0100000193', '0100000194', '0100000195',
 '0100000196', '0100000197', '0100000198', '0100000199', '0100000200', '0100000201', '0100000202', '0100000203', '0100000204', '0100000205',
 '0100000206', '0100000207', '0100000208', '0100000209', '0100000212', '0100000213', '0100000215', '0100000216', '0100000217', '0100000218',
 '0100000220', '0100000221', '0100000222', '0100000223', '0100000224', '0100000225', '0100000226', '0100000227', '0100000228', '0100000230',
 '0100000231', '0100000232', '0100000233', '0100000234', '0100000236', '0100000237', '0100000238', '0100000239', '0100000240', '0100000241',
 '0100000242', '0100000243', '0100000244', '0100000245', '0100000246', '0100000247', '0100000248', '0100000249', '0100000250', '0100000252',
 '0100000253', '0100000254', '0100000255', '0100000256', '0100000259', '0100000260', '0100000261', '0100000262', '0100000263', '0100000264',
 '0100000265', '0100000266', '0100000267', '0100000268', '0100000269', '0100000270', '0100000271', '0100000272', '0100000273', '0100000274',
 '0100000275', '0100000276', '0100000277', '0100000278', '0100000279', '0100000280', '0100000281', '0100000282', '0100000283', '0100000284',
 '0100000285', '0100000286', '0100000287', '0100000288', '0100000289', '0100000290', '0100000291', '0100000292', '0100000293', '0100000295',
 '0100000296', '0100000298', '0100000299', '0100000300', '0100000301', '0100000303', '0100000304', '0100000305', '0100000306', '0100000307',
 '0100000308', '0100000309', '0100000310', '0100000311', '0100000313', '0100000314', '0100000315', '0100000316', '0100000317', '0100000318',
 '0100000319', '0100000320', '0100000321', '0100000322', '0100000323', '0100000324', '0100000325', '0100000326', '0100000327', '0100000328',
 '0100000329', '0100000330', '0100000331', '0100000332', '0100000333', '0100000335', '0100000336', '0100000337', '0100000338', '0100000339') ;
 
 -- updating due dates of some transactions
SELECT sell_date, deleted, in_amount, due_date FROM `sms_transaction` WHERE `sell_date` = '2011-09-23 01:00:00'
UPDATE sms_transaction SET due_date='2011-10-31 01:00:00' WHERE `sell_date` = '2011-09-23 01:00:00' ; 


SELECT sell_date, deleted, in_amount, due_date, payment_type FROM `sms_transaction` WHERE `sell_date` = '2011-09-28 01:00:00' AND payment_type='combini_machi'
UPDATE sms_transaction SET due_date='2011-10-31 01:00:00' WHERE `sell_date` = '2011-09-28 01:00:00' AND payment_type='combini_machi'

-- shusseki status problem
SELECT * FROM `smsevent_instances` WHERE `category` LIKE 'teacher' AND event_type='course'
AND `from_datetime` > '2011-09-05 00:54:48' AND `to_datetime` < '2011-09-06 06:54:53'
AND target_id='856b0d15-838b-6750-bf06-4dbe14111b6a' ;

SELECT from_datetime, status, attendance_status, instance_contact_status
FROM `smsevent_instances` WHERE `target_id` LIKE '856b0d15-838b-6750-bf06-4dbe14111b6a' AND instance_contact_status =3;

UPDATE smsevent_instances SET instance_contact_status=4 WHERE `target_id` LIKE '856b0d15-838b-6750-bf06-4dbe14111b6a' AND instance_contact_status =3;

-- selecting appropriate parts from the address field
SELECT idnumber, grade, last_name, first_name, 
	primary_address_city, 
	CASE 
		WHEN LOCATE('区', primary_address_city) > 0 THEN
			SUBSTRING(primary_address_city, 1, LOCATE('区', primary_address_city) )
		WHEN LOCATE('市', primary_address_city) > 0 THEN
			SUBSTRING(primary_address_city, 1, LOCATE('市', primary_address_city) )
		WHEN LOCATE('町', primary_address_city) > 0 THEN
			SUBSTRING(primary_address_city, 1, LOCATE('町', primary_address_city) )
		WHEN LOCATE('村', primary_address_city) > 0 THEN
			SUBSTRING(primary_address_city, 1, LOCATE('村', primary_address_city) )
	ELSE 
		primary_address_city
	END AS city
FROM contacts
WHERE deleted=0 AND smsgroup='student'

-- 2012-12-16 send_document_work update
SELECT id, idnumber, last_name, first_name, deleted, send_document_home, send_document_work FROM `contacts` WHERE smsgroup='student' ;

UPDATE contacts SET send_document_work='off' WHERE smsgroup='student' ;

-- kibo jikantai updates

SELECT * FROM `sms_transaction` WHERE `due_date` = '2012-02-29 01:00:00'
UPDATE sms_transaction SET due_date='2012-01-31 01:00:00' WHERE `due_date` = '2012-02-29 01:00:00'

UPDATE contacts SET custom_var16='月曜日 [15:20-16:20] U-6' WHERE custom_var16='月曜日 [15:30-16:30] U-6';
UPDATE contacts SET custom_var16='金曜日 [17:45-18:45] U-10〜U-12' WHERE custom_var16='金曜日 [18:00-19:00] U-10〜U-12';
UPDATE contacts SET custom_var16='月曜日 [15:20-16:20]' WHERE custom_var16='月曜日 [15:30-16:30]';
UPDATE contacts SET custom_var16='金曜日 [17:45-18:45]' WHERE custom_var16='金曜日 [18:00-19:00]';
UPDATE contacts SET custom_var16='金曜日 [16:30-17:30]' WHERE custom_var16='金曜日 [16:45-17:45]';

UPDATE contacts SET custom_var17='月曜日 [15:20-16:20] U-6' WHERE custom_var17='月曜日 [15:30-16:30] U-6';
UPDATE contacts SET custom_var17='金曜日 [17:45-18:45] U-10〜U-12' WHERE custom_var17='金曜日 [18:00-19:00] U-10〜U-12';
UPDATE contacts SET custom_var17='月曜日 [15:20-16:20]' WHERE custom_var17='月曜日 [15:30-16:30]';
UPDATE contacts SET custom_var17='金曜日 [17:45-18:45]' WHERE custom_var17='金曜日 [18:00-19:00]';
UPDATE contacts SET custom_var17='金曜日 [16:30-17:30]' WHERE custom_var17='金曜日 [16:45-17:45]';

UPDATE contacts SET custom_var18='月曜日 [15:20-16:20] U-6' WHERE custom_var18='月曜日 [15:30-16:30] U-6';
UPDATE contacts SET custom_var18='金曜日 [17:45-18:45] U-10〜U-12' WHERE custom_var18='金曜日 [18:00-19:00] U-10〜U-12';
UPDATE contacts SET custom_var18='月曜日 [15:20-16:20]' WHERE custom_var18='月曜日 [15:30-16:30]';
UPDATE contacts SET custom_var18='金曜日 [17:45-18:45]' WHERE custom_var18='金曜日 [18:00-19:00]';
UPDATE contacts SET custom_var18='金曜日 [16:30-17:30]' WHERE custom_var18='金曜日 [16:45-17:45]';

-- Oita kousha addition
INSERT INTO `resource` (`id`, `school_id`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `shortname`, `description`, `category`, `location`, `deleted`, `capacity`, `resource_attributes`, `common_code`, `activation_key`) VALUES
('c3940c88-49c7-f844-7efb-4f59a43c6372', '02', '2012-03-09 06:33:16', '2012-03-09 06:33:16', '1', '1', '大分校', NULL, NULL, 'school', NULL, 0, NULL, NULL, NULL, '');
INSERT INTO `resourceschool_cstm` (`id_c`, `tel_c`, `address_c`, `rules_c`, `fax_c`, `id_label_c`, `last_student_id_c`, `last_invoice_id_c`, `last_teacher_id_c`, `rules_teacher_c`, `rules_staff_c`, `bank_account_c`, `bank_account_receipt_c`, `tantosha_c`, `shimebi_c`) VALUES
('c3940c88-49c7-f844-7efb-4f59a43c6372', '', '', '', '', '', '', '', '90000', '', '', '', '', '', '0000-00-00');

-- HP pulldown
"11":"
	<OPTION value=\'\' SELECTED>希望なし<\/OPTION>
	<OPTION value=\'月曜日 [17:00-18:00] U-8〜U-12\'>月曜日 [17:00-18:00] U-8〜U-12<\/OPTION>
	<OPTION value=\'木曜日 [19:45-20:45] U-10〜U-12\'>木曜日 [19:45-20:45] U-10〜U-12<\/OPTION>
",
"10":"
	<OPTION value=\'\' SELECTED>希望なし<\/OPTION>
	<OPTION value=\'月曜日 [17:00-18:00] U-8〜U-12\'>月曜日 [17:00-18:00] U-8〜U-12<\/OPTION>
	<OPTION value=\'木曜日 [19:45-20:45] U-10〜U-12\'>木曜日 [19:45-20:45] U-10〜U-12<\/OPTION>
",
"9":"
	<OPTION value=\'\' SELECTED>希望なし<\/OPTION>
	<OPTION value=\'月曜日 [17:00-18:00] U-8〜U-12\'>月曜日 [17:00-18:00] U-8〜U-12<\/OPTION>
	<OPTION value=\'木曜日 [19:45-20:45] U-10〜U-12\'>木曜日 [19:45-20:45] U-10〜U-12<\/OPTION>
",
"8":"
	<OPTION value=\'\' SELECTED>希望なし<\/OPTION>
	<OPTION value=\'月曜日 [17:00-18:00] U-8〜U-12\'>月曜日 [17:00-18:00] U-8〜U-12<\/OPTION>
	<OPTION value=\'木曜日 [18:30-19:30] U-8〜U-10\'>木曜日 [18:30-19:30] U-8〜U-10<\/OPTION>
",
"7":"
	<OPTION value=\'\' SELECTED>希望なし<\/OPTION>
	<OPTION value=\'月曜日 [17:00-18:00] U-8〜U-12\'>月曜日 [17:00-18:00] U-8〜U-12<\/OPTION>
	<OPTION value=\'木曜日 [18:30-19:30] U-8〜U-10\'>木曜日 [18:30-19:30] U-8〜U-10<\/OPTION>
	<OPTION value=\'土曜日 [16:30-17:30] U-8\'>土曜日 [16:30-17:30] U-8<\/OPTION>
",
"6":"
	<OPTION value=\'\' SELECTED>希望なし<\/OPTION>
	<OPTION value=\'月曜日 [17:00-18:00] U-8〜U-12\'>月曜日 [17:00-18:00] U-8〜U-12<\/OPTION>
	<OPTION value=\'木曜日 [18:30-19:30] U-8〜U-10\'>木曜日 [18:30-19:30] U-8〜U-10<\/OPTION>
	<OPTION value=\'土曜日 [16:30-17:30] U-8\'>土曜日 [16:30-17:30] U-8<\/OPTION>
", 
"5":"
	<OPTION value=\'\' SELECTED>希望なし<\/OPTION>
	<OPTION value=\'月曜日 [16:00-16:45] U-6\'>月曜日 [16:00-16:45] U-6<\/OPTION>
	<OPTION value=\'土曜日 [15:00-15:45] U-6\'>土曜日 [15:00-15:45] U-6<\/OPTION>
", 
"4":"
	<OPTION value=\'\' SELECTED>希望なし<\/OPTION>
	<OPTION value=\'月曜日 [16:00-16:45] U-6\'>月曜日 [16:00-16:45] U-6<\/OPTION>
	<OPTION value=\'土曜日 [15:00-15:45] U-6\'>土曜日 [15:00-15:45] U-6<\/OPTION>
"


-- gakunen update
SELECT id, last_name, first_name, idnumber, contact_status, grade, grade_entered, grade_entrance_date, date_entered, date_modified, modified_user_id, portal_app
FROM `contacts` WHERE deleted =0 AND grade_entered IS NOT NULL AND smsgroup = 'student' AND date_entered > '2012-01-09 00:00:00' ORDER BY `contacts`.`date_entered` ASC

UPDATE contacts SET grade=grade-1, grade_entered=grade_entered-1 WHERE deleted =0 AND grade_entered IS NOT NULL AND smsgroup = 'student' AND date_entered > '2012-01-09 00:00:00' ;

-- 
UPDATE contacts SET custom_var16='月曜日 [15:20-16:20] U-6' WHERE custom_var16='月曜日 [15:20-16:20]' ;
UPDATE contacts SET custom_var16='月曜日 [19:00-20:00] U-8〜U-10' WHERE custom_var16='月曜日 [19:00-20:00]' ; 
UPDATE contacts SET custom_var16='月曜日 [20:15-21:15] U-10〜U-12' WHERE custom_var16='月曜日 [20:15-21:15]' ; 
UPDATE contacts SET custom_var16='火曜日 [19:00-20:00] U-8〜U-10' WHERE custom_var16='火曜日 [19:00-20:00]' ; 
UPDATE contacts SET custom_var16='火曜日 [20:15-21:15] U-10〜U-12' WHERE custom_var16='火曜日 [20:15-21:15]' ; 
UPDATE contacts SET custom_var16='金曜日 [16:30-17:30] U-6〜U-8' WHERE custom_var16='金曜日 [16:30-17:30]' ; 
UPDATE contacts SET custom_var16='金曜日 [17:45-18:45] U-10〜U-12' WHERE custom_var16='金曜日 [17:45-18:45]' ; 

UPDATE contacts SET custom_var17='月曜日 [15:20-16:20] U-6' WHERE custom_var17='月曜日 [15:20-16:20]' ;
UPDATE contacts SET custom_var17='月曜日 [19:00-20:00] U-8〜U-10' WHERE custom_var17='月曜日 [19:00-20:00]' ; 
UPDATE contacts SET custom_var17='月曜日 [20:15-21:15] U-10〜U-12' WHERE custom_var17='月曜日 [20:15-21:15]' ; 
UPDATE contacts SET custom_var17='火曜日 [19:00-20:00] U-8〜U-10' WHERE custom_var17='火曜日 [19:00-20:00]' ; 
UPDATE contacts SET custom_var17='火曜日 [20:15-21:15] U-10〜U-12' WHERE custom_var17='火曜日 [20:15-21:15]' ; 
UPDATE contacts SET custom_var17='金曜日 [16:30-17:30] U-6〜U-8' WHERE custom_var17='金曜日 [16:30-17:30]' ; 
UPDATE contacts SET custom_var17='金曜日 [17:45-18:45] U-10〜U-12' WHERE custom_var17='金曜日 [17:45-18:45]' ; 

UPDATE contacts SET custom_var18='月曜日 [15:20-16:20] U-6' WHERE custom_var18='月曜日 [15:20-16:20]' ;
UPDATE contacts SET custom_var18='月曜日 [19:00-20:00] U-8〜U-10' WHERE custom_var18='月曜日 [19:00-20:00]' ; 
UPDATE contacts SET custom_var18='月曜日 [20:15-21:15] U-10〜U-12' WHERE custom_var18='月曜日 [20:15-21:15]' ; 
UPDATE contacts SET custom_var18='火曜日 [19:00-20:00] U-8〜U-10' WHERE custom_var18='火曜日 [19:00-20:00]' ; 
UPDATE contacts SET custom_var18='火曜日 [20:15-21:15] U-10〜U-12' WHERE custom_var18='火曜日 [20:15-21:15]' ; 
UPDATE contacts SET custom_var18='金曜日 [16:30-17:30] U-6〜U-8' WHERE custom_var18='金曜日 [16:30-17:30]' ; 
UPDATE contacts SET custom_var18='金曜日 [17:45-18:45] U-10〜U-12' WHERE custom_var18='金曜日 [17:45-18:45]' ; 

-- 2012-03-30 delete fees of Oita branch
SELECT * FROM sms_salesgroup WHERE salesno LIKE '020000%' AND contact_id IS NOT NULL
SELECT * FROM sms_fee WHERE school_id='c3940c88-49c7-f844-7efb-4f59a43c6372' AND contact_id IS NOT NULL AND deleted=0
SELECT * FROM sms_fee WHERE salesno LIKE '020000%' AND contact_id IS NOT NULL AND deleted=0
SELECT * FROM sms_transaction WHERE salesno LIKE '020000%' AND contact_id IS NOT NULL

DELETE FROM sms_salesgroup WHERE salesno LIKE '020000%' AND contact_id IS NOT NULL ;
DELETE FROM sms_fee WHERE salesno LIKE '020000%' AND contact_id IS NOT NULL ;
DELETE FROM sms_transaction WHERE salesno LIKE '020000%' AND contact_id IS NOT NULL ;

-- 2012-06-22 combini check

SELECT last_name, first_name, idnumber, salesno, SUM(in_amount) as total, contact_id, DATE_ADD(combini_history.date_entered, INTERVAL 540 MINUTE) AS date_entered, DATE_ADD(t_date, INTERVAL 540 MINUTE) AS trans_date, contact_id
FROM combini_history
LEFT JOIN contacts ON contact_id=contacts.id
WHERE combini_history.date_entered > '2012-04-04 15:00:00' AND combini_history.date_entered <'2012-05-07 15:00:00' 
	AND contacts.school_id='c3940c88-49c7-f844-7efb-4f59a43c6372'
GROUP BY contact_id
ORDER BY contact_id

AND error_code!='no_error' 
2012/04/05　～2012/05/07

-- 2013-05-13
SELECT * FROM `sms_salesgroup` WHERE `contact_id` IS NULL AND id != salesno

SELECT sms_salesgroup.name as package_name, sms_fee.name as fee_name,  sms_fee.basic_fee, sms_fee.school_id, sms_fee.id, sms_fee.salesno, sms_salesgroup.salesno, sms_salesgroup.id FROM sms_fee, sms_salesgroup 
WHERE sms_fee.salesno=sms_salesgroup.salesno AND sms_salesgroup.contact_id IS NULL AND sms_salesgroup.id!=sms_salesgroup.salesno
ORDER BY  sms_fee.school_id, sms_salesgroup.salesno

UPDATE sms_fee, sms_salesgroup  SET sms_fee.salesno=sms_salesgroup.id 
WHERE sms_fee.salesno=sms_salesgroup.salesno AND sms_salesgroup.contact_id IS NULL AND sms_salesgroup.id!=sms_salesgroup.salesno ; 

UPDATE sms_salesgroup SET sms_salesgroup.salesno=sms_salesgroup.id WHERE `contact_id` IS NULL AND id != salesno ;

-- 2013-05-17
SELECT * FROM `sms_salesgroup` WHERE `contact_id` IS NOT NULL
DELETE FROM `sms_salesgroup` WHERE `contact_id` IS NOT NULL
SELECT * FROM `sms_salesgroup` WHERE `deleted` =1
DELETE FROM `sms_salesgroup` WHERE `deleted` =1

SELECT * FROM `sms_fee` WHERE `contact_id` IS NOT NULL
DELETE FROM `sms_fee` WHERE `contact_id` IS NOT NULL

SELECT sms_salesgroup.name as package_name, sms_fee.name as fee_name,  sms_fee.basic_fee, sms_fee.school_id, sms_fee.id, sms_fee.salesno 
FROM sms_salesgroup, sms_fee WHERE sms_fee.salesno=sms_salesgroup.salesno AND sms_fee.contact_id IS NULL AND sms_fee.salesno IS NOT NULL and sms_fee.date_entered > '2013-05-13 06:00:00'

SELECT sms_salesgroup.name as package_name, sms_fee.name as fee_name,  sms_fee.basic_fee, sms_fee.school_id, sms_fee.date_entered as fee_date, sms_salesgroup.date_entered as package_date, sms_fee.id, sms_fee.salesno 
FROM sms_salesgroup, sms_fee WHERE sms_fee.salesno=sms_salesgroup.salesno AND sms_fee.contact_id IS NULL AND sms_fee.salesno IS NOT NULL and sms_fee.date_entered > '2013-05-13 06:00:00'



SELECT * FROM `sms_fee` WHERE `contact_id` IS NOT NULL
DELETE FROM `sms_fee` WHERE `contact_id` IS NOT NULL


SELECT sms_fee.name as fee_name, sms_fee.basic_fee, sms_fee.id, sms_fee.salesno, sms_fee_b.salesno FROM sms_fee, sms_fee_b WHERE sms_fee.id = sms_fee_b.id AND sms_fee.contact_id IS NULL AND sms_fee_b.salesno!=sms_fee.salesno

UPDATE sms_fee, sms_fee_b SET sms_fee.salesno=sms_fee_b.salesno WHERE sms_fee.id = sms_fee_b.id AND sms_fee.contact_id IS NULL AND sms_fee_b.salesno!=sms_fee.salesno