-- 01-22	nyukinnippo kanren azukarikin

SELECT sms_fee.basic_fee, sms_fee.fee_subcategory, sms_fee.sell_date, sms_transaction.t_date, sms_fee.school_id, sms_fee.*, sms_fee_sub1.*, sms_transaction.*
FROM sms_fee, sms_fee_sub1, sms_transaction
WHERE sms_fee.id=sms_fee_sub1.id_sub1 AND sms_fee.id=sms_transaction.fee_id
	AND sms_fee.deleted=0 AND sms_transaction.deleted=0 AND sms_fee_sub1.fee_shokubun=1
	AND sms_fee.school_id='sssssss-1111'
	
	
-- 01-26 event_contacts correction
SELECT event_contacts  {$relation_table} ec SET 
			date_modified='$date_modified'
			,start_date = ( SELECT min( from_datetime ) 
				FROM smsevent_instances
				WHERE target_id = '{$target_id}' AND event_id='{$event_id}'  {$cancelled_str})
			, end_date = ( SELECT max( to_datetime ) 
				FROM smsevent_instances
				WHERE target_id = '{$target_id}' AND event_id='{$event_id}'  {$cancelled_str})
			{$credit_set_if_needed}
			{$status_set_if_needed}
			WHERE {$target_field_name} = '{$target_id}'
			AND event_id = '{$event_id}' AND deleted=0 
			

select id, t_table.buppin_code from
    (SELECT buppin_name, buppin_code, id
        FROM `buppin`
        group by 
        buppin_name, buppin_code
    ) t_table    
    group by t_table.buppin_name
    having count(t_table.buppin_name) = 1 AND t_table.buppin_code = ''
    
    
-- VarsCls::AllCancelledOrRescheduledOrigin()  ->  what is the decimal value?  => 1021136

SELECT contact_id, event_id, start_date, min_start_date, end_date FROM event_contacts, (
	SELECT event_id AS min_event_id, target_id AS min_target_id, min(from_datetime) as min_start_date
	FROM smsevent_instances 	WHERE (status & 1021136) = 0 AND category!='classroom'
	GROUP BY target_id, event_id )
	min_instances
WHERE event_id=min_event_id AND contact_id=min_target_id AND deleted=0 AND start_date!=min_start_date

-- to be confirmed
UPDATE  event_contacts, (
	SELECT event_id AS min_event_id, target_id AS min_target_id, min(from_datetime) as min_start_date
	FROM smsevent_instances 	WHERE (status & 1021136) = 0 AND category!='classroom'
	GROUP BY target_id, event_id )
	min_instances
SET start_date=min_start_date
WHERE event_id=min_event_id AND contact_id=min_target_id AND deleted=0 AND start_date!=min_start_date

SELECT contact_id, event_id, start_date, min_start_date, end_date FROM event_contacts, (
	SELECT event_id AS min_event_id, target_id AS min_target_id, min(from_datetime) as min_start_date
	FROM smsevent_instances 	WHERE (status & 1021136) = 0 AND category='classroom'
	GROUP BY target_id, event_id )
	min_instances
WHERE event_id=min_event_id AND contact_id=min_target_id AND deleted=0 AND start_date!=min_start_date


SELECT contact_id, event_id, start_date, end_date, max_end_date FROM event_contacts, (
	SELECT event_id AS min_event_id, target_id AS min_target_id, max(to_datetime) as max_end_date
	FROM smsevent_instances 	WHERE (status & 1021136) = 0 AND category!='classroom'
	GROUP BY target_id, event_id )
	min_instances
WHERE event_id=min_event_id AND contact_id=min_target_id AND deleted=0 AND end_date!=max_end_date


SELECT contact_id, event_id, start_date, end_date, max_end_date FROM event_contacts, (
	SELECT event_id AS min_event_id, target_id AS min_target_id, max(to_datetime) as max_end_date
	FROM smsevent_instances 	WHERE (status & 1021136) = 0 AND category='classroom'
	GROUP BY target_id, event_id )
	min_instances
WHERE event_id=min_event_id AND contact_id=min_target_id AND deleted=0 AND end_date!=max_end_date


-- 01-26 sms_fee total_paid inconsistency
SELECT sms_fee.id, sms_fee.contact_id, sms_fee.event_id, sms_fee.fee_subcategory, sms_fee.basic_fee, in_amount, total_paid, actual_in_amount, payment_type, t_date
FROM sms_fee, sms_transaction
WHERE sms_fee.id=sms_transaction.fee_id AND sms_fee.deleted=0 AND sms_transaction.deleted=0 AND actual_in_amount!=0 AND actual_in_amount!=total_paid
-- doube nyukin for henkin & 0 total_paid for some nyukaikin


-- 01/-27 zeinuki_kakaku
SELECT id, basic_fee, zeinuki_kakaku, fee_subcategory FROM sms_fee, sms_fee_sub1 WHERE id=id_sub1 AND deleted=0 AND basic_fee>0 AND zeinuki_kakaku IS NULL 
SELECT id, basic_fee, zeinuki_kakaku, fee_subcategory FROM sms_fee, sms_fee_sub1 WHERE id=id_sub1 AND deleted=0 AND basic_fee<0 AND zeinuki_kakaku IS NULL 

-- 01/28 combini_machi
SELECT sms_fee.fee_payment_type, sms_transaction.payment_type FROM sms_fee, sms_transaction 
WHERE sms_fee.id=sms_transaction.fee_id AND sms_fee.deleted=0 AND sms_transaction.deleted=0 AND sms_fee.fee_payment_type='combini_machi'

-- 01/31 students w. no schedule
SELECT contact_id, event_id, start_date, end_date FROM event_contacts, smsevent_sub1 WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contact_status=2 AND event_contacts.deleted=0 
AND smsevent_sub1.nenki IN ('201501', '201504') AND start_date IS NULL ORDER BY event_id


SELECT contact_id, idnumber, event_id, common_code, smsevent.name, start_date, end_date FROM event_contacts, smsevent, smsevent_sub1, contacts 
WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.event_id=smsevent.id AND smsevent_sub1.id_sub1=smsevent.id AND event_contacts.contact_id=contacts.id 
	AND event_contact_status=2 AND event_contacts.deleted=0 AND smsevent_sub1.nenki IN ('201501', '201504') AND start_date IS NULL ORDER BY event_id

-- 01/31 sharei check
select 	instance_id, target_id, category, status
FROM smsevent_instances WHERE instance_id IN ('619c0174-5fd4-0d20-5a58-547086ffe2f2') AND smsevent_instances.category='student' 
AND (status & 1021136) = 0 AND  smsevent_instances.event_type='course' 
- problem is this student: 1516455   although event_contact_status is 13=全取消 schedule is not deleted
SELECT * FROM `smsevent_instances` WHERE `event_id` LIKE '9370d928-93d6-cd0d-9a82-5470863c12fe' AND `target_id` LIKE '1516455'  -- deleted all these records
INSERT INTO `smsevent_instances` (`id`, `date_modified`, `event_id`, `event_type`, `instance_id`, `target_id`, `category`, `from_datetime`, `to_datetime`, `status`, `from_instance_id`, `to_instance_id`, `attendance_status`, `instance_contact_status`, `name`, `description`, `early_late_time2`, `early_late_time3`, `attendance_memo`) VALUES
('69857f33-7e8f-3a4f-7a2e-548686543b69', '2014-12-09 14:18:20', '9370d928-93d6-cd0d-9a82-5470863c12fe', 'course', '619c0174-5fd4-0d20-5a58-547086ffe2f2', '1516455', 'student', '2015-01-08 15:30:00', '2015-01-08 17:00:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL),
('698b87ba-e9e1-4441-ba11-5486868ae725', '2014-12-09 14:18:20', '9370d928-93d6-cd0d-9a82-5470863c12fe', 'course', '62670e4f-8dd5-3df6-d26c-54708604320a', '1516455', 'student', '2015-02-12 15:30:00', '2015-02-12 17:00:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL),
('6990e5d8-9009-d14a-8670-5486862388b9', '2014-12-09 14:18:20', '9370d928-93d6-cd0d-9a82-5470863c12fe', 'course', '63150595-7e55-07a8-9d9d-547086cb4af4', '1516455', 'student', '2015-03-12 15:30:00', '2015-03-12 17:00:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL);
SELECT * FROM `event_contacts` WHERE `contact_id` LIKE '1516455' AND `event_id` LIKE '9370d928-93d6-cd0d-9a82-5470863c12fe'  -- start/end dates are set to NULL 
-- check all courses for this kind of strange data
SELECT contact_id, event_id, event_contact_status, start_date, end_date FROM event_contacts, smsevent_sub1 WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contact_status=13 
AND event_contacts.deleted=0 AND start_date IS NOT NULL AND smsevent_sub1.nenki IN ('201501', '201504') 

-- schedule addition

INSERT INTO `smsevent_instances` (`id`, `date_modified`, `event_id`, `event_type`, `instance_id`, `target_id`, `category`, `from_datetime`, `to_datetime`, `status`, `from_instance_id`, `to_instance_id`, `attendance_status`, `instance_contact_status`, `name`, `description`, `early_late_time2`, `early_late_time3`, `attendance_memo`) VALUES
('x1d0b3a1-0543-8e3c-b49b-54ab945db436', '2015-01-06 16:54:00', 'e8358919-91fa-3e25-1c6b-5461c5db78a4', 'course', 'd35ca721-14d0-f15c-b7da-549785ce10b4', '1400594', 'student', '2015-01-01 09:00:00', '2015-01-01 10:00:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL),
('y1d0b3a1-0543-8e3c-b49b-54ab945db436', '2015-01-06 16:54:00', 'e8358919-91fa-3e25-1c6b-5461c5db78a4', 'course', 'd35ca721-14d0-f15c-b7da-549785ce10b4', '375567', 'student', '2015-01-01 09:00:00', '2015-01-01 10:00:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL),
('w1d0b3a1-0543-8e3c-b49b-54ab945db436', '2015-01-06 16:54:00', 'e8358919-91fa-3e25-1c6b-5461c5db78a4', 'course', 'd35ca721-14d0-f15c-b7da-549785ce10b4', '1455743', 'student', '2015-01-01 09:00:00', '2015-01-01 10:00:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL),
('z1d0b3a1-0543-8e3c-b49b-54ab945db436', '2015-01-06 16:54:00', 'e8358919-91fa-3e25-1c6b-5461c5db78a4', 'course', 'd35ca721-14d0-f15c-b7da-549785ce10b4', '1453005', 'student', '2015-01-01 09:00:00', '2015-01-01 10:00:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL);



SELECT * FROM `sharei_hibetsu` WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `tsuikakbn` =0 AND `furikomibi` = '2015-03-15'
SELECT * FROM `sharei_koushi` WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `tsuikakbn` =0 AND `furikomibi` = '2015-03-15'
SELECT * FROM `sharei_kouza` WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `tsuikakbn` =0 AND `furikomibi` = '2015-03-15'

UPDATE sharei_hibetsu SET `furikomibi` = '2015-02-15' WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `tsuikakbn` =0 AND `furikomibi` = '2015-03-15';
UPDATE sharei_koushi SET `furikomibi` = '2015-02-15' WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `tsuikakbn` =0 AND `furikomibi` = '2015-03-15';
UPDATE sharei_kouza SET `furikomibi` = '2015-02-15' WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `tsuikakbn` =0 AND `furikomibi` = '2015-03-15';

SELECT * FROM `sharei_hibetsu` WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `tsuikakbn` =1 AND `furikomibi` = '2015-03-15'

UPDATE sharei_hibetsu SET `furikomibi` = '2015-02-15' WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `tsuikakbn` =1 AND `furikomibi` = '2015-03-15';
UPDATE sharei_koushi SET `furikomibi` = '2015-02-15' WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `tsuikakbn` =1 AND `furikomibi` = '2015-03-15';
UPDATE sharei_kouza SET `furikomibi` = '2015-02-15' WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `tsuikakbn` =1 AND `furikomibi` = '2015-03-15';


INSERT INTO maeukekin_t2 SELECT DISTINCT t1.* FROM maeukekin_test t1 

SELECT count( * ) , course_id, student_id, keijobi, fee_id, fee_subcategory, kaisu, kingaku FROM `maeukekin_t2`
GROUP BY student_id, course_id, keijobi, fee_id, fee_subcategory, kaisu HAVING COUNT( * ) >1

-- multiple classes in one day for one course
SELECT parent_id, from_datetime, SUBSTRING(from_datetime, 1, 10)  FROM sms_event_instance WHERE from_datetime LIKE '2015-01-%' AND (status & 1021136) = 0 GROUP BY parent_id, SUBSTRING(from_datetime, 1, 10) HAVING COUNT(*) > 1
	'parent_id','SUBSTRING(from_datetime, 1, 10)'
SELECT * FROM maeukekin_test WHERE course_id='20954cee-c2a2-a562-ad06-544f6ad1b118' AND keijobi='2015-01-13' ;  -- 7 records some maeukekin values have wrong tanka -> deleted them (probably 1/30 data) 
SELECT * FROM maeukekin_test WHERE course_id='333a876e-8cbb-96ae-db95-5435daa55d0c' AND keijobi='2015-01-04';  -- no data in maeukekin
SELECT * FROM maeukekin_test WHERE course_id='333a876e-8cbb-96ae-db95-5435daa55d0c' AND keijobi='2015-01-05';  -- no data in maeukekin
SELECT * FROM maeukekin_test WHERE course_id='3a430465-eff4-6187-6bdd-544a31cdd24e' AND keijobi='2015-01-17';  -- 11 records
SELECT * FROM maeukekin_test WHERE course_id='614af0f9-7def-8e5c-3c6c-5459f18cbac2' AND keijobi='2015-01-11'; -- no data in maeukekin
SELECT * FROM maeukekin_test WHERE course_id='7b8db336-f96a-d340-62d7-5461e01e6b5e' AND keijobi='2015-01-08';  -- 12 records
SELECT * FROM maeukekin_test WHERE course_id='821b6789-df22-55c9-f723-543f96975d49' AND keijobi='2015-01-10';  -- 3 records
SELECT * FROM maeukekin_test WHERE course_id='821b6789-df22-55c9-f723-543f96975d49' AND keijobi='2015-01-17';  -- 3 records
SELECT * FROM maeukekin_test WHERE course_id='821b6789-df22-55c9-f723-543f96975d49' AND keijobi='2015-01-24';  -- 3 records
SELECT * FROM maeukekin_test WHERE course_id='b2db8b16-c6db-0718-7a1e-544a2c7400eb' AND keijobi='2015-01-10'; -- 15 records
SELECT * FROM maeukekin_test WHERE course_id='c285b639-9724-4a87-cc51-5456ec163c2e' AND keijobi='2015-01-29'; -- 12 
	
-- copy records into maeukekin_t1

SELECT maeukekin_t2.*, smsevent_instances.id, from_datetime FROM maeukekin_t2, smsevent_instances 
WHERE smsevent_instances.target_id=maeukekin_t2.student_id AND smsevent_instances.event_id=maeukekin_t2.course_id AND SUBSTRING(from_datetime, 1, 10)=keijobi AND from_datetime LIKE '2015-01-%' AND (status & 1021136) = 0

UPDATE maeukekin_t2, smsevent_instances SET maeukekin_t2.deleted=0 
WHERE smsevent_instances.target_id=maeukekin_t2.student_id AND smsevent_instances.event_id=maeukekin_t2.course_id AND SUBSTRING(from_datetime, 1, 10)=keijobi AND from_datetime LIKE '2015-01-%' AND (status & 1021136) = 0

SELECT maeukekin_t2.*, sms_event_instance.id, from_datetime FROM maeukekin_t2, sms_event_instance 
WHERE maeukekin_t2.student_id='' AND sms_event_instance.parent_id=maeukekin_t2.course_id AND SUBSTRING(from_datetime, 1, 10)=keijobi AND from_datetime LIKE '2015-01-%' AND (status & 1021136) = 0

UPDATE maeukekin_t2 SET deleted=0 WHERE student_id='';

-- copy maeukekin_t2 to maeukekin_t1 and delete records wih deleted=1
-- 

SELECT * FROM `maeukekin` WHERE `keijobi` LIKE '2015-01-%'
DELETE FROM `maeukekin` WHERE `keijobi` LIKE '2015-01-%'

INSERT INTO maeukekin_201501_2 SELECT * FROM `maeukekin` WHERE `keijobi` LIKE '2015-01-%'

INSERT INTO maeukekin SELECT * FROM `maeukekin_201501_2` WHERE `keijobi` LIKE '2015-01-%'

-- 02-04  combini torikomi error
SELECT contact_id, idnumber, event_id, common_code, sell_date, in_amount, fee_name, salesno, combini_sakusei_id
FROM yoyaku_transactions_deleted, contacts, smsevent
WHERE yoyaku_transactions_deleted.contact_id=contacts.id AND yoyaku_transactions_deleted.event_id=smsevent.id AND combini_sakusei_id LIKE '1002000000000177'

-- 02/07

INSERT INTO maeukekin_t3_noadd SELECT * FROM `maeukekin` WHERE `keijobi` LIKE '2015-01-%'
-- add tshushin data import for 2015-01 then
INSERT INTO maeukekin_t3 SELECT * FROM `maeukekin` WHERE `keijobi` LIKE '2015-01-%'

-- 2/8 add date_modified to maeukekin table
ALTER TABLE `maeukekin` ADD `date_modified` DATETIME NULL ; 
-- UPDATE maeukekin SET date_modified='2014-12-31 01:00:00' WHERE keijobi < '2015-01-01' ;
UPDATE maeukekin SET date_modified='2015-02-01 01:00:00' WHERE keijobi LIKE '2015-01%' ;
UPDATE maeukekin SET date_modified='2015-02-08 01:00:00' WHERE keijobi > '2015-01-31' ;

- 2/8 tsushin kingaku check by kousha
SELECT sum( kingaku ) , name FROM maeukekin_tsushin, resource WHERE maeukekin_tsushin.school_id = resource.id GROUP BY maeukekin_tsushin.school_id

DELETE FROM `maeukekin` WHERE `keijobi` LIKE '2015-01-%'

INSERT INTO maeukekin SELECT * FROM `maeukekin_t3` WHERE `keijobi` LIKE '2015-01-%'

-- 2/9 maeukekin
INSERT INTO maeukekin_t4_noadd SELECT * FROM `maeukekin` WHERE `keijobi` LIKE '2015-01-%'
UPDATE maeukekin_t4 SET date_modified='2015-02-01 01:00:00' WHERE keijobi LIKE '2015-01%' ;
DELETE FROM `maeukekin` WHERE `keijobi` LIKE '2015-01-%'
INSERT INTO maeukekin SELECT * FROM `maeukekin_t4` WHERE `keijobi` LIKE '2015-01-%'


SELECT * FROM `maeukekin_t4` WHERE `nyukinbi` LIKE '2015-02-%' AND `keijobi` LIKE '2015-01-%'
DELETE FROM `maeukekin_t4` WHERE `nyukinbi` LIKE '2015-02-%' AND `keijobi` LIKE '2015-01-%'


-- 2/10 yoyaku_fee not exist but yayaku_event_contacts exists  -> only Yonemura San

SELECT * FROM yoyaku_event_contacts 
LEFT JOIN yoyaku_fees ON yoyaku_event_contacts.contact_id=yoyaku_fees.contact_id AND yoyaku_event_contacts.event_id=yoyaku_fees.event_id AND yoyaku_fees.deleted=0 
WHERE yoyaku_event_contacts.deleted=0 AND event_contact_status!=3 AND yoyaku_fees.id IS NULL

-- 2/10 although zentorikeshi still schedule remains

SELECT contact_id, event_id, start_date, end_date FROM event_contacts, smsevent_sub1 
WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contact_status=13
	AND event_contacts.deleted=0 AND smsevent_sub1.nenki = '201501' AND start_date IS NOT NULL
ORDER BY event_id


-- 2/10 maeukekin check courses below
Notice: Undefined index: 5214fdff-3e58-d48c-9297-5461f40da06c in /koza/SCHOOLCUSTOM/acc/modules/Student/include/maeukekin_batch_acc.php on line 444
Notice: Undefined index: 53d48b31-755c-3308-ce11-544b621196d8 in /koza/SCHOOLCUSTOM/acc/modules/Student/include/maeukekin_batch_acc.php on line 444
Notice: Undefined index: 86fbe69f-debe-243e-ea7b-5444a0ad4d6d in /koza/SCHOOLCUSTOM/acc/modules/Student/include/maeukekin_batch_acc.php on line 444
Notice: Undefined index: cd66a750-c22a-ee30-ee8f-545de41321a6 in /koza/SCHOOLCUSTOM/acc/modules/Student/include/maeukekin_batch_acc.php on line 444
Notice: Undefined index: c9c5519c-641f-5b3e-6fa0-5451ead8e587 in /koza/SCHOOLCUSTOM/acc/modules/Student/include/maeukekin_batch_acc.php on line 444
Notice: Undefined index: c524084e-c71e-66f1-2030-5460655049d5 in /koza/SCHOOLCUSTOM/acc/modules/Student/include/maeukekin_batch_acc.php on line 444

-- 2/10 
INSERT INTO maeukekin_1iko SELECT * FROM `maeukekin` WHERE `keijobi` > '2015-00'
INSERT INTO maeukekin SELECT * FROM `maeukekin_tsushin` WHERE 1

INSERT INTO maeukekin_1iko_0210_0201 SELECT * FROM `maeukekin` WHERE `keijobi` > '2015-00'

-- 2/12
INSERT INTO maeukekin_1iko_0212_0201 SELECT * FROM `maeukekin` WHERE `keijobi` > '2015-00'
INSERT INTO maeukekin SELECT * FROM `maeukekin_tsushin` WHERE 1

-- 2/15
INSERT INTO maeukekin_1iko_0214_0201 SELECT * FROM `maeukekin` WHERE `keijobi` > '2015-00'

-- tanka problem
SELECT sms_fee.id, basic_fee, sms_fee_sub1.tanka, sms_fee.school_id, sms_fee.fee_subcategory, resource.name, FLOOR(sms_fee_sub1.tanka), ROUND(basic_fee/FLOOR(sms_fee_sub1.tanka)), (FLOOR(sms_fee_sub1.tanka) * ROUND(basic_fee/FLOOR(sms_fee_sub1.tanka))) as tanka_total
FROM sms_fee, sms_fee_sub1, resource
WHERE sms_fee.id=sms_fee_sub1.id_sub1 AND sms_fee.deleted=0 AND resource.id=sms_fee.school_id 
	AND (FLOOR(sms_fee_sub1.tanka) * ROUND(basic_fee/FLOOR(sms_fee_sub1.tanka))) > basic_fee
	AND basic_fee > 0
	
id 	basic_fee 	tanka 	school_id 	fee_subcategory 	name 	FLOOR(sms_fee_sub1.tanka) 	ROUND(basic_fee/FLOOR(sms_fee_sub1.tanka)) 	tanka_total
500f7171-8c00-79b4-afe5-54ba113d66a6 	648 	130 	e86510a2-302b-7c34-250d-5001d1fc1b41 	教材費 	名古屋 	130 	5 	650
63a608a2-3241-b9d6-5f9e-54b5fcf71cb9 	3855 	643 	e86510a2-302b-7c34-250d-5001d1fc1b41 	受講料 	名古屋 	643 	6 	3858
72ee719a-1222-55f1-208b-54ae4b37b538 	3240 	295 	e86510a2-302b-7c34-250d-5001d1fc1b41 	教材費 	名古屋 	295 	11 	3245
ba5a538f-2d7e-fdcb-178f-54af463835c0 	185 	16.8182 	e86510a2-302b-7c34-250d-5001d1fc1b41 	教材費 	名古屋 	16 	12 	

-- 2/15 done on koza site
UPDATE `sms_fee_sub1` SET `tanka` = '129' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = '500f7171-8c00-79b4-afe5-54ba113d66a6' LIMIT 1 ;
UPDATE `sms_fee_sub1` SET `tanka` = '642' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = '63a608a2-3241-b9d6-5f9e-54b5fcf71cb9' LIMIT 1 ;
UPDATE `sms_fee_sub1` SET `tanka` = '294' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = '72ee719a-1222-55f1-208b-54ae4b37b538' LIMIT 1 ;
UPDATE `sms_fee_sub1` SET `tanka` = '15' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = 'ba5a538f-2d7e-fdcb-178f-54af463835c0' LIMIT 1 ;

SELECT sms_fee.id, basic_fee, tanka, FLOOR(tanka), ROUND(basic_fee/FLOOR(tanka)), (FLOOR(tanka) * ROUND(basic_fee/FLOOR(tanka))) as tanka_total
FROM sms_fee, sms_fee_sub1
WHERE sms_fee.id=sms_fee_sub1.id_sub1 AND deleted=0
	AND (FLOOR(tanka) * ROUND(basic_fee/FLOOR(tanka))) < basic_fee
	AND basic_fee < 0
	
-- nenki problem
	
SELECT SUM(kingaku), resource.name, maeukekin.school_id FROM maeukekin, resource WHERE nenki='201501' AND maeukekin.school_id=resource.id AND resource.category='school' GROUP BY maeukekin.school_id 

SELECT SUM(kingaku), resource.name, maeukekin.school_id FROM maeukekin, resource WHERE nenki>'201501' AND maeukekin.school_id=resource.id AND resource.category='school' GROUP BY maeukekin.school_id   -> 38,260 shinjuku
SELECT sum(kingaku), student_id FROM maeukekin, resource WHERE nenki > '201507' AND maeukekin.school_id = resource.id AND resource.category = 'school' GROUP BY student_id
SELECT SUM(kingaku), course_id, resource.name, maeukekin.school_id FROM maeukekin, resource WHERE nenki>'201501' AND maeukekin.school_id=resource.id AND resource.category='school' GROUP BY maeukekin.course_id
	-> 38,260 shinjuku  &	18,792 shonan
UPDATE `smsevent_sub1` SET `nenki` = '201501' WHERE CONVERT( `smsevent_sub1`.`id_sub1` USING utf8 ) = 'a06b9706-006d-b88c-358a-5450a7d2a181' LIMIT 1 ;
UPDATE `smsevent_sub1` SET `nenki` = '201501' WHERE CONVERT( `smsevent_sub1`.`id_sub1` USING utf8 ) = 'd91f5c8f-9923-21fe-aecd-543e1c327c8e' LIMIT 1 ;
UPDATE `maeukekin` SET nenki='201501' WHERE `course_id` LIKE 'a06b9706-006d-b88c-358a-5450a7d2a181'
UPDATE `maeukekin` SET nenki='201501' WHERE `course_id` LIKE 'd91f5c8f-9923-21fe-aecd-543e1c327c8e'

SELECT SUM(kingaku), resource.name, maeukekin.school_id FROM maeukekin, resource WHERE nenki='201410' AND `keijobi` > '2015-00' AND maeukekin.school_id=resource.id AND resource.category='school' GROUP BY maeukekin.school_id   


SELECT SUM(kingaku), resource.name, maeukekin.school_id FROM maeukekin, resource WHERE nenki='201501' AND maeukekin.school_id=resource.id AND maeukekin.student_id IS NULL AND resource.category='school' GROUP BY maeukekin.school_id

SELECT DISTINCT course_id, school_id FROM `maeukekin` WHERE `keijobi` LIKE '2015-01-%' AND nenki = '201507' 		d91f5c8f-9923-21fe-aecd-543e1c327c8e
SELECT sum(kingaku) FROM `maeukekin` WHERE `keijobi` LIKE '2015-01-%' AND nenki = '201507' 	

-- shujitsu schedule problem
SELECT * FROM `smsevent_instances` WHERE `event_id` LIKE '1b288a6e-56b9-62b4-e3e7-54488bf6f639' AND to_datetime='2015-01-22 00:00:00'
-- UPDATE `smsevent_instances` SET to_datetime='2015-01-22 00:00:00' WHERE `event_id` LIKE '1b288a6e-56b9-62b4-e3e7-54488bf6f639' AND to_datetime='2015-01-21 23:55:00';
-- UPDATE `smsevent_instances` SET from_datetime='2015-01-21 00:00:00' WHERE `event_id` LIKE '1b288a6e-56b9-62b4-e3e7-54488bf6f639' AND from_datetime='2015-01-21 00:05:00';
 	

SELECT * FROM `sms_event_instance` WHERE `parent_id` LIKE '1b288a6e-56b9-62b4-e3e7-54488bf6f639' AND to_datetime='2015-01-22 00:00:00'
-- UPDATE `sms_event_instance` SET to_datetime='2015-01-22 00:00:00' WHERE `parent_id` LIKE '1b288a6e-56b9-62b4-e3e7-54488bf6f639' AND to_datetime='2015-01-21 23:55:00';

SELECT tanka, sms_fee.* FROM sms_fee, sms_fee_sub1 WHERE sms_fee.id=sms_fee_sub1.id_sub1 AND event_id LIKE '9d795d50-7905-091a-f05c-546358a43140'

-- 02/16

INSERT INTO maeukekin_1iko_0215_0201 SELECT * FROM `maeukekin` WHERE `keijobi` > '2015-00'
INSERT INTO maeukekin SELECT * FROM `maeukekin_tsushin` WHERE 1

UPDATE `acc_20150206`.`sms_fee_sub1` SET `tanka` = '16' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = 'ba5a538f-2d7e-fdcb-178f-54af463835c0' LIMIT 1 ;

INSERT INTO maeukekin_1iko_0216_0201 SELECT * FROM `maeukekin` WHERE `keijobi` > '2015-00'


-- 2/25

DELETE FROM maeukekin WHERE keijobi > '2015-00'

INSERT INTO maeukekin SELECT * FROM maeukekin_1iko_0219_0201 WHERE keijobi > '2015-00'

INSERT INTO maeukekin SELECT * FROM `maeukekin_tsushin` WHERE 1

UPDATE `maeukekin` SET kingaku=294 WHERE `fee_id` LIKE '72ee719a-1222-55f1-208b-54ae4b37b538';
UPDATE `maeukekin` SET `kingaku` = '300' WHERE `student_id`= '1510288' AND `course_id` = '944dd053-7b96-c345-813c-546849889653' AND `keijobi` = '2015-01-15' AND `fee_id` = '72ee719a-1222-55f1-208b-54ae4b37b538' LIMIT 1 ;

-- 2/26

SELECT contacts.idnumber, yoyaku_fees_deleted.contact_id, yoyaku_fees_deleted.event_id, smsevent.name, smsevent.common_code, yoyaku_fees_deleted.basic_fee
FROM yoyaku_fees_deleted, contacts, smsevent
WHERE yoyaku_fees_deleted.contact_id=contacts.id AND yoyaku_fees_deleted.event_id=smsevent.id AND yoyaku_fees_deleted.contact_id='1490884'


-- 3/3

DELETE FROM maeukekin WHERE date_modified='2015-02-01 01:00:00'; 
INSERT INTO maeukekin SELECT * FROM maeukekin_tsushin WHERE 1; 

-- 3/4

SELECT * FROM `sharei_koushi` WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `furikomibi` LIKE '2015-03%'
UPDATE sharei_koushi SET furikomibi='2015-02-15' WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `furikomibi` LIKE '2015-03%'

SELECT * FROM `sharei_hibetsu` WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `furikomibi` LIKE '2015-03%'
UPDATE sharei_hibetsu SET furikomibi='2015-02-15' WHERE `t_bank_payment_type` =2 AND `date_modified` = '2015-01-31 23:59:59' AND `furikomibi` LIKE '2015-03%'

-- 3/10

SELECT event_contact_status, contact_status, start_date FROM event_contacts, contacts WHERE contact_id=contacts.id AND contacts.smsgroup='teacher' AND contact_status='3' AND event_contact_status=0 AND event_contacts.deleted=0 AND contacts.deleted=0
UPDATE event_contacts, contacts SET event_contact_status=3 WHERE contact_id=contacts.id AND contacts.smsgroup='teacher' AND contact_status='3' AND event_contact_status=0 AND event_contacts.deleted=0 AND contacts.deleted=0

SELECT event_contact_status, contact_status, start_date FROM event_contacts, contacts WHERE contact_id=contacts.id AND contacts.smsgroup='teacher' AND contact_status='4' AND event_contact_status=0 AND event_contacts.deleted=0 AND contacts.deleted=0
UPDATE event_contacts, contacts SET event_contact_status=4 WHERE contact_id=contacts.id AND contacts.smsgroup='teacher' AND contact_status='4' AND event_contact_status=0 AND event_contacts.deleted=0 AND contacts.deleted=0


INSERT INTO `smsevent_instances` (`id`, `date_modified`, `event_id`, `event_type`, `instance_id`, `target_id`, `category`, `from_datetime`, `to_datetime`, `status`, `from_instance_id`, `to_instance_id`, `attendance_status`, `instance_contact_status`, `name`, `description`, `early_late_time2`, `early_late_time3`, `attendance_memo`) VALUES
('59ad6ca1-xxxx-b564-b328-54d07b56a140', '2015-02-03 16:39:31', 'a2a9ee47-1838-28d7-1074-5462057b57ea', 'course', '59814dc0-b309-b136-7cfc-54d07bc5bdaf', '1349483', 'student', '2015-02-22 13:00:00', '2015-02-22 16:30:00', 1, '', '', NULL, 2, NULL, '', 0, 0, NULL);

UPDATE `event_contacts` SET `start_date` = '2015-02-22 13:00:00', `end_date` = '2015-02-22 13:00:00' WHERE CONVERT( `event_contacts`.`id` USING utf8 ) = '97f51308-fb10-a404-1e90-549d00bd16ce' LIMIT 1 ;


INSERT INTO `maeukekin` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('1349483', 'a2a9ee47-1838-28d7-1074-5462057b57ea', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '01', 201501, '108200200', '2015-02-22', '2015-02-22', 'j', 6480, '9a1e06ff-6ceb-5e74-e5ea-549d00af0da9', 1, '2015-03-10 12:59:02');;


-- maeukekin shusei
SELECT DISTINCT `date_modified` FROM `maeukekin` WHERE `keijobi` LIKE '2015-01%' AND date_modified > '2015-02-20'
SELECT * FROM `maeukekin` WHERE `keijobi` LIKE '2015-01%' AND date_modified > '2015-02-20'
DELETE FROM maeukekin WHERE `keijobi` LIKE '2015-01%' AND date_modified > '2015-02-20' ;

SELECT DISTINCT `date_modified` FROM `maeukekin` WHERE `keijobi` LIKE '2015-02%' AND date_modified > '2015-03-03'
SELECT * FROM `maeukekin` WHERE `keijobi` LIKE '2015-02%' AND date_modified >= '2015-03-03'
DELETE FROM maeukekin WHERE `keijobi` LIKE '2015-02%' AND date_modified >= '2015-03-03' ;

INSERT INTO maeukekin SELECT * FROM maeukekin_import WHERE 1; 

-- 3/13
-- maeukekin shuusei
UPDATE `sms_fee` SET `end_date` = '2015-03-28' WHERE CONVERT( `sms_fee`.`id` USING utf8 ) = '78248955-a2f2-0e0c-876e-54bdbacb5bde' LIMIT 1 ;
UPDATE `sms_fee` SET `end_date` = '2015-03-27' WHERE CONVERT( `sms_fee`.`id` USING utf8 ) = 'a7024d5d-5ce6-f4ff-c287-54825930ecc7' LIMIT 1 ;
UPDATE `sms_fee` SET `end_date` = '2015-04-04' WHERE CONVERT( `sms_fee`.`id` USING utf8 ) = 'b26d9fb9-6f53-54a4-fd7b-5489277623bd' LIMIT 1 ;


INSERT INTO maeukekin_1iko_0313_0301 SELECT * FROM `maeukekin` WHERE `keijobi` > '2015-00'

INSERT INTO `maeukekin` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('319494', 'd9eb06a3-e96b-bc85-0ab2-5487dfd92e90', 'sssssss-1111', '01', 201501, '111200101', '2015-01-20', '2015-03-28', 't', -500, '790e280a-ccbf-9ff5-f21c-54bdba2ad5ce', -1, '2015-03-13 09:23:11');

SELECT * FROM `maeukekin` WHERE `keijobi` LIKE '2015-02%' AND date_modified = '2015-03-03 00:00:00'

DELETE FROM `maeukekin` WHERE `keijobi` LIKE '2015-02%' AND date_modified != '2015-03-03 00:00:00'

INSERT INTO maeukekin SELECT * FROM `maeukekin_1iko_0313_0301` WHERE `keijobi` LIKE '2015-02%'


-- 3/20

INSERT INTO maeukekin SELECT * FROM maeukekin_chousei


SELECT * FROM maeukekin WHERE course_id='d98203ed-9a6f-70d0-8831-544b625508b5' AND nyukinbi < '2015-03-01' AND keijobi >= '2015-03-01' ORDER BY keijobi, student_id
INSERT INTO `maeukekin_chousei_3gatsu` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('1484620', 'd98203ed-9a6f-70d0-8831-544b625508b5', '49caa19d-9872-3095-6bd4-5001d0aa692d', '03', 201501, '362007200', '2014-12-07', '2015-03-07', 'j', 3240, '2e7d0fb7-6463-07c6-2181-5483d0d831b3', 1, '2015-03-20 00:54:27'),
('1490986', 'd98203ed-9a6f-70d0-8831-544b625508b5', '49caa19d-9872-3095-6bd4-5001d0aa692d', '03', 201501, '362007200', '2014-12-07', '2015-03-07', 'j', 3240, 'e45e3964-7cb9-9cb5-2fbc-5483d05ecda6', 1, '2015-03-20 00:54:27'),
('1499802', 'd98203ed-9a6f-70d0-8831-544b625508b5', '49caa19d-9872-3095-6bd4-5001d0aa692d', '03', 201501, '362007200', '2015-01-05', '2015-03-07', 'j', 3240, 'b7bedf37-bf8a-7b02-83fb-54aa363f5132', 1, '2015-03-20 00:54:27'),
('827763', 'd98203ed-9a6f-70d0-8831-544b625508b5', '49caa19d-9872-3095-6bd4-5001d0aa692d', '03', 201501, '362007200', '2014-11-23', '2015-03-07', 'j', 3240, 'a0e904ef-b51d-be65-0e51-54714114918d', 1, '2015-03-20 00:54:27');
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='d98203ed-9a6f-70d0-8831-544b625508b5'

SELECT * FROM maeukekin WHERE course_id='b6bd930a-1167-539c-2cad-544e04215409' AND nyukinbi < '2015-03-01' AND keijobi >= '2015-03-01' ORDER BY keijobi, student_id
UPDATE sms_fee SET `end_date` = '2015-03-30' WHERE CONVERT( `sms_fee`.`id` USING utf8 ) = 'b5925d02-35ce-7b2d-b110-54af443ef38a' LIMIT 1 ;
INSERT INTO `maeukekin_chousei_3gatsu` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('9494', 'b6bd930a-1167-539c-2cad-544e04215409', 'a32c39d6-45da-a750-6e4d-5001d0bd7a8d', '01', 201501, '123000600', '2015-01-13', '2015-03-30', 'j', 2700, 'b5925d02-35ce-7b2d-b110-54af443ef38a', 1, '2015-03-20 01:02:29');

SELECT * FROM maeukekin WHERE course_id='2f6d2f2d-a03c-ddb9-ad27-544f6804e0e4' AND nyukinbi < '2015-03-01' AND keijobi >= '2015-03-01' ORDER BY keijobi, student_id
INSERT INTO `maeukekin_chousei_3gatsu` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('1128413', '2f6d2f2d-a03c-ddb9-ad27-544f6804e0e4', 'a312c57a-0e45-6f65-0402-5001d1e84f1d', '05', 201501, '548000300', '2014-12-10', '2015-03-28', 'j', 2538, '56242e6d-0c3d-c047-6835-5487b6641411', 1, '2015-03-20 01:06:20'),
('1182457', '2f6d2f2d-a03c-ddb9-ad27-544f6804e0e4', 'a312c57a-0e45-6f65-0402-5001d1e84f1d', '05', 201501, '548000300', '2014-12-15', '2015-03-28', 'j', 2538, 'd5f121b3-911f-3483-8a85-548ea33df8b8', 1, '2015-03-20 01:06:20'),
('1333659', '2f6d2f2d-a03c-ddb9-ad27-544f6804e0e4', 'a312c57a-0e45-6f65-0402-5001d1e84f1d', '05', 201501, '548000300', '2014-12-15', '2015-03-28', 'j', 2538, 'ac65ea1f-50e3-35e2-b1c0-548ea00c0547', 1, '2015-03-20 01:06:20'),
('1431793', '2f6d2f2d-a03c-ddb9-ad27-544f6804e0e4', 'a312c57a-0e45-6f65-0402-5001d1e84f1d', '05', 201501, '548000300', '2014-12-15', '2015-03-28', 'j', 2538, 'd87ed61d-6a14-040e-70d1-548ea2731293', 1, '2015-03-20 01:06:20');
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='2f6d2f2d-a03c-ddb9-ad27-544f6804e0e4'

SELECT * FROM maeukekin WHERE course_id='794976da-72ba-5e89-8a41-544f6cf0e010' AND nyukinbi < '2015-03-01' AND keijobi >= '2015-03-01' ORDER BY keijobi, student_id
INSERT INTO `maeukekin_chousei_3gatsu` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('1181251', '794976da-72ba-5e89-8a41-544f6cf0e010', 'a312c57a-0e45-6f65-0402-5001d1e84f1d', '05', 201501, '548000400', '2014-12-19', '2015-03-28', 'j', 2538, '17f67e10-a093-65bf-f9c7-5493eab55f8b', 1, '2015-03-20 01:06:20'),
('1188552', '794976da-72ba-5e89-8a41-544f6cf0e010', 'a312c57a-0e45-6f65-0402-5001d1e84f1d', '05', 201501, '548000400', '2014-12-15', '2015-03-28', 'j', 2538, '8bf6d7b7-4ae6-24c4-c16a-548ea45642ad', 1, '2015-03-20 01:06:20'),
('1337513', '794976da-72ba-5e89-8a41-544f6cf0e010', 'a312c57a-0e45-6f65-0402-5001d1e84f1d', '05', 201501, '548000400', '2014-12-15', '2015-03-28', 'j', 2538, 'd778224b-9bdb-f8fd-f338-548ea3771b3b', 1, '2015-03-20 01:06:20');
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='794976da-72ba-5e89-8a41-544f6cf0e010'

SELECT * FROM maeukekin WHERE course_id='bd436350-1df4-d8a9-d43b-5462087a08fc' AND nyukinbi < '2015-03-01' AND keijobi >= '2015-03-01' ORDER BY student_id, keijobi
UPDATE `sms_fee` SET `start_date` = '2015-03-28', `end_date` = '2015-03-28' WHERE CONVERT( `sms_fee`.`id` USING utf8 ) = '95759205-42d9-eaa6-c4a8-548ff43e4a37' LIMIT 1 ;
UPDATE `sms_fee` SET `start_date` = '2015-03-28', `end_date` = '2015-03-28' WHERE CONVERT( `sms_fee`.`id` USING utf8 ) = '5ccff154-b70a-3a84-04ca-548a5e9445d6' LIMIT 1 ;
UPDATE `sms_fee` SET `start_date` = '2015-03-28', `end_date` = '2015-03-28' WHERE CONVERT( `sms_fee`.`id` USING utf8 ) = '1c460e67-465b-57f3-5c64-548c0f079af3' LIMIT 1 ;
UPDATE `sms_fee` SET `start_date` = '2015-03-28', `end_date` = '2015-03-28' WHERE CONVERT( `sms_fee`.`id` USING utf8 ) = '87a038ec-182a-a222-a27f-5475ac9be5d1' LIMIT 1 ;
UPDATE `sms_fee` SET `start_date` = '2015-03-28', `end_date` = '2015-03-28' WHERE CONVERT( `sms_fee`.`id` USING utf8 ) = 'c945b1b7-110a-e91e-0a27-5483eb08ebca' LIMIT 1 ;
SELECT * FROM sms_fee WHERE id IN ('95759205-42d9-eaa6-c4a8-548ff43e4a37', '5ccff154-b70a-3a84-04ca-548a5e9445d6', '1c460e67-465b-57f3-5c64-548c0f079af3', '87a038ec-182a-a222-a27f-5475ac9be5d1', 'c945b1b7-110a-e91e-0a27-5483eb08ebca')
INSERT INTO `maeukekin_chousei_3gatsu` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('1113932', 'bd436350-1df4-d8a9-d43b-5462087a08fc', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '01', 201501, '103200000', '2014-12-26', '2015-03-28', 'j', 2916, '95759205-42d9-eaa6-c4a8-548ff43e4a37', 1, '2015-03-20 01:06:20'), 
('1114535', 'bd436350-1df4-d8a9-d43b-5462087a08fc', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '01', 201501, '103200000', '2014-12-26', '2015-03-28', 'j', 2916, '5ccff154-b70a-3a84-04ca-548a5e9445d6', 1, '2015-03-20 01:06:20'), 
('1124610', 'bd436350-1df4-d8a9-d43b-5462087a08fc', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '01', 201501, '103200000', '2014-12-26', '2015-03-28', 'j', 2916, '1c460e67-465b-57f3-5c64-548c0f079af3', 1, '2015-03-20 01:06:20'), 
('1466549', 'bd436350-1df4-d8a9-d43b-5462087a08fc', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '01', 201501, '103200000', '2014-12-26', '2015-03-28', 'j', 2916, '87a038ec-182a-a222-a27f-5475ac9be5d1', 1, '2015-03-20 01:06:20'), 
('1496192', 'bd436350-1df4-d8a9-d43b-5462087a08fc', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '01', 201501, '103200000', '2014-12-26', '2015-03-28', 'j', 2916, 'c945b1b7-110a-e91e-0a27-5483eb08ebca', 1, '2015-03-20 01:06:20');
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='bd436350-1df4-d8a9-d43b-5462087a08fc'

SELECT * FROM maeukekin WHERE course_id='177f2663-bb96-42f6-cd15-54716ab263cf' AND nyukinbi < '2015-03-01' AND keijobi >= '2015-03-01' ORDER BY student_id, keijobi
INSERT INTO `maeukekin_chousei_3gatsu` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('1120202', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2014-12-19', '2015-03-20', 'j', 2160, '46960ec3-aa93-c385-eeae-54938cc1d093', 1, '2015-03-20 01:02:29'),
('1120933', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2014-12-11', '2015-03-20', 'j', 2160, 'bb179f42-1207-89e7-a6b0-54896b3dbc18', 1, '2015-03-20 01:02:29'),
('1163983', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2015-01-12', '2015-03-20', 'j', 2160, '365d10f0-3b7a-516e-83cd-54b355b79966', 1, '2015-03-20 01:02:29'),
('1203359', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2015-01-08', '2015-03-20', 'j', 756, '7ac74a7c-d895-2a17-fb0d-54ae17609bca', 1, '2015-03-20 01:02:29'),
('1382454', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2014-12-19', '2015-03-20', 'j', 2160, 'a7612898-6fc7-8094-c229-5493a8a0a974', 1, '2015-03-20 01:02:29'),
('1427893', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2015-01-16', '2015-03-20', 'j', 2160, '6f06be6e-65b9-b674-383a-54b893d8248f', 1, '2015-03-20 01:02:29'),
('1450963', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2014-12-19', '2015-03-20', 'j', 2160, '3bdda045-d128-1a41-0c3e-5493a654dc63', 1, '2015-03-20 01:02:29'),
('1469567', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2014-12-19', '2015-03-20', 'j', 2160, '94cc310b-acad-405a-73c4-5493c68dedba', 1, '2015-03-20 01:02:29'),
('1478279', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2014-12-19', '2015-03-20', 'j', 2160, '1632fdd3-fa65-3dda-e40f-5493c5594e0b', 1, '2015-03-20 01:02:29'),
('1479015', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2015-01-16', '2015-03-20', 'j', 2160, '8c64cc9d-b942-0759-24ce-54b88a5b8dde', 1, '2015-03-20 01:02:29'),
('1479040', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2014-12-19', '2015-03-20', 'j', 2160, '4c3afcb5-eda6-c861-7178-5493c587e051', 1, '2015-03-20 01:02:29'),
('1489910', '177f2663-bb96-42f6-cd15-54716ab263cf', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '547001900', '2015-01-16', '2015-03-20', 'j', 2160, 'e4bf7b8a-1cec-17eb-b850-54b8908d3d4a', 1, '2015-03-20 01:02:29');
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='177f2663-bb96-42f6-cd15-54716ab263cf'

SELECT * FROM maeukekin WHERE course_id='20f60841-817c-a944-06c8-54701a159ed0' AND nyukinbi < '2015-03-01' AND keijobi >= '2015-03-01' ORDER BY keijobi, student_id
INSERT INTO `maeukekin_chousei_3gatsu` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('1114709', '20f60841-817c-a944-06c8-54701a159ed0', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '538001900', '2014-12-24', '2015-03-11', 'j', 2160, '1bfcb85f-7f30-98dd-76d3-549a19d4cb74', 1, '2015-03-20 00:58:25'),
('1120258', '20f60841-817c-a944-06c8-54701a159ed0', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '538001900', '2015-01-15', '2015-03-11', 'j', 2160, '19ce67f1-0ea5-01c5-3691-54b70a53f148', 1, '2015-03-20 00:58:25'),
('1471974', '20f60841-817c-a944-06c8-54701a159ed0', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '538001900', '2014-12-10', '2015-03-11', 'j', 2160, 'bddfe16e-c67f-4d71-0518-5487e4607278', 1, '2015-03-20 00:58:25'),
('1493057', '20f60841-817c-a944-06c8-54701a159ed0', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '538001900', '2014-12-24', '2015-03-11', 'j', 2160, 'd7ada3d6-90a3-c9c3-8365-549a383c2ace', 1, '2015-03-20 00:58:25'),
('1496951', '20f60841-817c-a944-06c8-54701a159ed0', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '538001900', '2014-12-24', '2015-03-11', 'j', 2160, '4d31e2c2-bed7-b7e7-905b-549a11bc072e', 1, '2015-03-20 00:58:25');
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='20f60841-817c-a944-06c8-54701a159ed0'

SELECT * FROM maeukekin WHERE course_id='863b4999-3e1d-262f-2807-54701480acde' AND nyukinbi < '2015-03-01' AND keijobi >= '2015-03-01' ORDER BY keijobi, student_id
INSERT INTO `maeukekin_chousei_3gatsu` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('1120548', '863b4999-3e1d-262f-2807-54701480acde', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '538001500', '2014-12-25', '2015-03-26', 'j', 1944, 'cc037b3a-1d76-8834-75e3-549b8ca6f0d7', 1, '2015-03-20 01:06:20'),
('1123568', '863b4999-3e1d-262f-2807-54701480acde', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '538001500', '2014-12-07', '2015-03-26', 'j', 1944, '9a6297a6-9ed3-23f7-48b2-5483d15a5304', 1, '2015-03-20 01:06:20'),
('1124031', '863b4999-3e1d-262f-2807-54701480acde', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '538001500', '2014-12-25', '2015-03-26', 'j', 1944, 'c86607c0-e5ad-3398-7136-549b8e7ca4c2', 1, '2015-03-20 01:06:20'),
('1125192', '863b4999-3e1d-262f-2807-54701480acde', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '538001500', '2014-12-25', '2015-03-26', 'j', 1944, '9bf5e5e9-2891-2108-872d-549b94257409', 1, '2015-03-20 01:06:20'),
('1511066', '863b4999-3e1d-262f-2807-54701480acde', 'e86510a2-302b-7c34-250d-5001d1fc1b41', '05', 201501, '538001500', '2014-12-11', '2015-03-26', 'j', 1944, 'a3d53823-fb54-a687-e29c-548949c0bcf5', 1, '2015-03-20 01:06:20');
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='863b4999-3e1d-262f-2807-54701480acde'

SELECT sum(kingaku) FROM maeukekin WHERE course_id='d4c6b7bb-95bb-b0b7-14d6-54420fc68fae'

-- kyoto
SELECT * FROM maeukekin WHERE course_id='1b288a6e-56b9-62b4-e3e7-54488bf6f639' AND nyukinbi < '2015-03-01' AND keijobi >= '2015-03-01' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='1b288a6e-56b9-62b4-e3e7-54488bf6f639'

SELECT * FROM maeukekin WHERE course_id='22c3b5ad-5c4f-a703-6089-54485e2e944c' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-30' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='22c3b5ad-5c4f-a703-6089-54485e2e944c'

SELECT * FROM maeukekin WHERE course_id='22fbc2ea-44da-af08-1c40-5448880027a0' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-25' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='22fbc2ea-44da-af08-1c40-5448880027a0'

SELECT * FROM maeukekin WHERE course_id='472f40d9-dc83-1785-2957-544861c9f76f' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-25' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='472f40d9-dc83-1785-2957-544861c9f76f'

SELECT * FROM maeukekin WHERE course_id='56bd0c82-dd38-df24-a85a-5448780910dc' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-03' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='56bd0c82-dd38-df24-a85a-5448780910dc'

SELECT * FROM maeukekin WHERE course_id='7e0c895f-c1cd-d276-d805-5449a543bd57' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-26' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='7e0c895f-c1cd-d276-d805-5449a543bd57'

SELECT * FROM maeukekin WHERE course_id='902c4fc7-2c1c-88b0-2d11-5448862a38a8' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-17' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='902c4fc7-2c1c-88b0-2d11-5448862a38a8'

SELECT * FROM maeukekin WHERE course_id='b439e5aa-80e0-a3b2-2f31-5449a4062346' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-19' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='b439e5aa-80e0-a3b2-2f31-5449a4062346'

SELECT * FROM maeukekin WHERE course_id='c9b6159c-bef7-0459-f706-54486283764d' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-20' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='c9b6159c-bef7-0459-f706-54486283764d'

SELECT * FROM maeukekin WHERE course_id='d47160c7-77cc-3b00-7326-544889727a99' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-11' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='d47160c7-77cc-3b00-7326-544889727a99'

SELECT * FROM maeukekin WHERE course_id='d6d7c488-5130-13cb-7c84-5449a588fbd9' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-12' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='d6d7c488-5130-13cb-7c84-5449a588fbd9'

SELECT * FROM maeukekin WHERE course_id='c8748ef3-d56a-6678-6e38-5459bf1b9d1e' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-28' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='c8748ef3-d56a-6678-6e38-5459bf1b9d1e'

SELECT * FROM maeukekin WHERE course_id='1393787a-0fd6-e593-8d35-547ee5ac0bcb' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-26' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='1393787a-0fd6-e593-8d35-547ee5ac0bcb'

SELECT * FROM maeukekin WHERE course_id='2457a1ca-2829-1a63-b5be-547ebd9de636' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-25' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='2457a1ca-2829-1a63-b5be-547ebd9de636'

SELECT * FROM maeukekin WHERE course_id='63a8e5d7-5854-8199-9afe-547edd66bc36' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-23' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='63a8e5d7-5854-8199-9afe-547edd66bc36'

SELECT * FROM maeukekin WHERE course_id='67591d18-37de-3fec-8dd9-5482a901fde1' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-18' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='67591d18-37de-3fec-8dd9-5482a901fde1'

SELECT * FROM maeukekin WHERE course_id='72c11ac8-6ca2-e35d-0034-547ec6b993a7' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-23' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='72c11ac8-6ca2-e35d-0034-547ec6b993a7'

SELECT * FROM maeukekin WHERE course_id='a066a802-a8b4-418e-489c-5482b0acf2fd' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-14' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='a066a802-a8b4-418e-489c-5482b0acf2fd'

SELECT * FROM maeukekin WHERE course_id='a6cf60be-990e-9b53-8e04-5482b2d2e398' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-18' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='a6cf60be-990e-9b53-8e04-5482b2d2e398'

SELECT * FROM maeukekin WHERE course_id='ad4f07ef-b773-79f2-e5a0-547ee55a92ce' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-22' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='ad4f07ef-b773-79f2-e5a0-547ee55a92ce'

SELECT * FROM maeukekin WHERE course_id='b1a76138-5fff-6995-7660-547ec70f61d4' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-23' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='b1a76138-5fff-6995-7660-547ec70f61d4'

SELECT * FROM maeukekin WHERE course_id='b6271b0f-4aee-f7ed-4c44-547ebfe97a87' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-23' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='b6271b0f-4aee-f7ed-4c44-547ebfe97a87'

SELECT * FROM maeukekin WHERE course_id='bac4c2e7-ce8d-c40f-340a-547ee4b734a1' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-25' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='bac4c2e7-ce8d-c40f-340a-547ee4b734a1'

SELECT * FROM maeukekin WHERE course_id='c0840867-3abe-3c76-1c34-547beb8caa00' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-23' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='c0840867-3abe-3c76-1c34-547beb8caa00'

SELECT * FROM maeukekin WHERE course_id='d86bb2fe-6352-155e-b57e-547ee18eb3aa' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-23' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='d86bb2fe-6352-155e-b57e-547ee18eb3aa'

SELECT * FROM maeukekin WHERE course_id='db446360-c1b8-2fc5-59c4-547ed162c334' AND nyukinbi < '2015-03-01' AND keijobi = '2015-03-23' ORDER BY keijobi, student_id
SELECT sum(kingaku) FROM maeukekin_chousei_3gatsu WHERE course_id='db446360-c1b8-2fc5-59c4-547ed162c334'


INSERT INTO maeukekin SELECT * FROM maeukekin_chousei_1_2gatsu ;

SELECT * FROM `maeukekin` WHERE `school_id` LIKE 'sssssss-1111' AND `keijobi` LIKE '2015-02%' AND `date_modified` = '2015-03-03 00:00:00'

SELECT sum(kingaku) FROM `maeukekin` WHERE `school_id` LIKE 'sssssss-1111' AND `keijobi` LIKE '2015-02%' AND `date_modified` = '2015-03-03 00:00:00'

SELECT sum(kingaku) FROM `maeukekin` WHERE `school_id` LIKE 'sssssss-1111' AND `keijobi` LIKE '2015-01%' AND `date_modified` = '2015-02-01 01:00:00'

SELECT school_id, sum(kingaku) FROM `maeukekin` WHERE `keijobi` LIKE '2015-01%' AND `date_modified` = '2015-02-01 01:00:00' GROUP BY school_id

SELECT school_id, sum(kingaku) FROM `maeukekin` WHERE `keijobi` LIKE '2015-02%' AND `date_modified` = '2015-03-03 00:00:00' GROUP BY school_id

-- 3/31

INSERT INTO `maeukekin` (`student_id`, `course_id`, `school_id`, `bumon_code`, `nenki`, `common_code`, `nyukinbi`, `keijobi`, `fee_subcategory`, `kingaku`, `fee_id`, `kaisu`, `date_modified`) VALUES
('319494', 'd9eb06a3-e96b-bc85-0ab2-5487dfd92e90', 'sssssss-1111', '01', 201501, '111200101', '2015-01-20', '2015-03-28', 't', -500, 'chousei_790e280a-ccbf-9ff5-f21c-54bdba2ad5ce', -1, '2015-03-13 09:19:00');

UPDATE maeukekin_import_3 SET fee_id=CONCAT("chousei_", fee_id) WHERE 1;
UPDATE maeukekin_import_4 SET fee_id=CONCAT("chousei_", fee_id) WHERE 1;
UPDATE maeukekin_import_5 SET fee_id=CONCAT("chousei_", fee_id) WHERE 1;
UPDATE maeukekin_import_6 SET fee_id=CONCAT("chousei_", fee_id) WHERE 1;

UPDATE maeukekin_import_3 SET date_modified="2015-03-31 22:03:00";
UPDATE maeukekin_import_4 SET date_modified="2015-03-31 22:04:00";
UPDATE maeukekin_import_5 SET date_modified="2015-03-31 22:05:00";
UPDATE maeukekin_import_6 SET date_modified="2015-03-31 22:06:00";

INSERT INTO maeukekin SELECT * FROM maeukekin_import_3 ;
INSERT INTO maeukekin SELECT * FROM maeukekin_import_4 ;
INSERT INTO maeukekin SELECT * FROM maeukekin_import_5 ;
INSERT INTO maeukekin SELECT * FROM maeukekin_import_6 ;


SELECT sum(kingaku), school_id FROM maeukekin_import_3 WHERE 1 GROUP BY school_id

-- 2015/3		2,051,061 		sssssss-1111
-- 2015/4		  500,845 		sssssss-1111
-- 2015/5		  177,116		sssssss-1111
-- 2015/6		         0		sssssss-1111

-- 4/1
UPDATE `acc`.`sms_fee_sub1` SET `tanka` = '1276' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = 'c8272b2c-169d-f6ed-dbc3-5518b6aef35c' LIMIT 1 ;  -- from 1404
UPDATE `acc`.`sms_fee_sub1` SET `tanka` = '1276' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = '81f3a391-4025-ec2d-c9cb-5510dd47f276' LIMIT 1 ;
UPDATE `acc`.`sms_fee_sub1` SET `tanka` = '1276' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = '335f30f9-e5f4-c8fb-b751-54f913c149ba' LIMIT 1 ;
UPDATE `acc`.`sms_fee_sub1` SET `tanka` = '1374' WHERE CONVERT( `sms_fee_sub1`.`id_sub1` USING utf8 ) = '882ad3fd-587f-61fa-7a2f-54f93bdc5657' LIMIT 1 ;

SELECT ec_shinki_keizoku, shinki_keizoku04 FROM event_contacts, event_contacts201501_04 WHERE event_contacts.id=event_contacts201501_04.id04 AND shinki_keizoku01=1

UPDATE event_contacts, event_contacts201501_04 SET ec_shinki_keizoku=2 WHERE event_contacts.id=event_contacts201501_04.id04 AND shinki_keizoku01=1

INSERT INTO maeukekin_1iko_0401 SELECT * FROM `maeukekin` WHERE `keijobi` > '2015-00'

-- 4/6 updating maeuekin for maeukekin_20150401 table
DELETE FROM maeukekin WHERE keijobi>='2015-03-01'
INSERT INTO maeukekin SELECT * FROM `maeukekin_1iko_0401` WHERE `keijobi` >= '2015-03-01'

SELECT salesno, date_entered, sum(basic_fee) as mysum FROM `sms_fee` WHERE date_entered < '2015-03-03 13:14:41' AND date_entered > '2015-03-03 10:15:29' GROUP BY salesno HAVING mysum=9072 

SELECT * FROM sms_fee WHERE salesno IN ('0100030375', '0100028205', '0100028222', '0100028244', '0100028062', '0100028206') ORDER BY salesno, date_entered

SELECT contacts.idnumber, contacts.last_name, contacts.first_name, sms_fee.event_id, smsevent.name, smsevent.common_code, resource.name, sms_fee.basic_fee, sms_fee.date_entered, basic_fee, salesno
FROM sms_fee
INNER JOIN resource ON sms_fee.school_id=resource.id
INNER JOIN contacts ON sms_fee.contact_id=contacts.id  AND contacts.deleted=0
LEFT JOIN smsevent ON sms_fee.event_id=smsevent.id AND smsevent.deleted=0
WHERE sms_fee.deleted=0 AND sms_fee.salesno IN ('0100030375', '0100028205', '0100028222', '0100028244', '0100028062', '0100028206') 
ORDER BY salesno, date_entered

SELECT salesno, date_entered, sum(basic_fee) as mysum FROM `sms_fee` WHERE date_entered < '2015-03-31 11:34:47' AND date_entered > '2015-03-31 11:25:56' GROUP BY salesno HAVING mysum=49248