-- 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'