UPDATE sms_fee SET fee_subcategory='受講料' WHERE fee_subcategory='授業料' ;
UPDATE sms_fee SET fee_subcategory='体験受講料' WHERE fee_subcategory='体験授業料' ;
UPDATE sms_fee SET fee_subcategory='施設使用料' WHERE fee_subcategory='施設利用料' ;
UPDATE sms_fee SET fee_subcategory='施設使用料(体験)' WHERE fee_subcategory='施設利用料(体験)' ;

UPDATE `excelmito_test`.`sms_transactiontype` SET `deleted` = '1' WHERE `sms_transactiontype`.`id` = '4' LIMIT 1 ;
UPDATE `excelmito_test`.`sms_transactiontype` SET `deleted` = '1' WHERE `sms_transactiontype`.`id` = '3' LIMIT 1 ;


DELETE FROM sms_fee WHERE contact_id='7111a0ab-a014-96ca-9df5-4e4b30aa41ef' ;
DELETE FROM sms_transaction WHERE contact_id='7111a0ab-a014-96ca-9df5-4e4b30aa41ef' ;

CREATE TABLE IF NOT EXISTS `choseikin` (
  `chosei_date` date NOT NULL,
  `teacher_id` varchar(36) NOT NULL,
  `choseikin1` int(11) NOT NULL DEFAULT '0',
  `choseikin2` int(11) NOT NULL DEFAULT '0',
  `choseikin3` int(11) NOT NULL DEFAULT '0',
  `choseikin4` int(11) NOT NULL DEFAULT '0',
  `kotsuhi_choseikin` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`chosei_date`,`teacher_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

// zenkaku hankaku kakko
update sms_fee  set fee_subcategory = REPLACE(fee_subcategory, '（', '(');
update sms_fee  set fee_subcategory = REPLACE(fee_subcategory, '）', ')');

--  import process 
UPDATE a_contacts SET name=CONCAT(last_name, ' ', first_name) WHERE 1;
SELECT last_name, first_name, CONCAT(last_name, ' ', first_name) FROM a_contacts  WHERE 1;

a_event_contacts 
	id  contact_name  event_name  contact_id  event_id  start_date  
	
a_contacts 
	name  last_name  first_name  idnumber  

a_smsevent
	common_code  name 

SELECT contact_name, idnumber, event_name, common_code
FROM a_event_contacts
LEFT JOIN a_contacts ON a_event_contacts.contact_name=a_contacts.name
LEFT JOIN a_smsevent ON a_event_contacts.event_name=a_smsevent.name

SELECT id, contact_name, idnumber, name, import_error 
FROM a_event_contacts
LEFT JOIN a_contacts ON a_event_contacts.contact_name = a_contacts.name
ORDER BY `a_contacts`.`idnumber` ASC , id ASC 

UPDATE a_event_contacts SET id = RTRIM(id) WHERE 1

SELECT id, event_name, name, common_code, import_error
FROM a_event_contacts
LEFT JOIN a_smsevent ON a_event_contacts.event_name=a_smsevent.name AND term1=1
WHERE import_error=0 AND event_id IS NULL AND name is NULL
ORDER BY a_smsevent.common_code ASC , event_name, id ASC 

SELECT id, event_name FROM a_event_contacts WHERE event_name='こどもの絵画造形教室'


UPDATE a_event_contacts SET import_error=1
WHERE event_name LIKE '%オカリーナ%'


UPDATE a_event_contacts SET event_name='ゲルダおばさんの初めてのドイツ語'
WHERE event_name LIKE '初めてのドイツ語'



SELECT id, event_name
FROM a_event_contacts
LEFT JOIN a_smsevent ON a_event_contacts.event_name=a_smsevent.name AND term1=1
WHERE import_error=0 AND event_id IS NULL AND name is NULL
ORDER BY a_smsevent.common_code ASC , event_name, id ASC 

a_smsevent
	common_code  name 

SELECT contact_name, idnumber, event_name, common_code
FROM a_event_contacts
LEFT JOIN a_contacts ON a_event_contacts.contact_name=a_contacts.name
LEFT JOIN a_smsevent ON a_event_contacts.event_name=a_smsevent.name

SELECT id, contact_name, idnumber, name, import_error 
FROM a_event_contacts
LEFT JOIN a_contacts ON a_event_contacts.contact_name = a_contacts.name
ORDER BY `a_contacts`.`idnumber` ASC , id ASC 


SELECT id, event_name, name, common_code
FROM a_event_contacts
LEFT JOIN a_smsevent ON a_event_contacts.event_name=a_smsevent.name AND term1=1 
WHERE import_error=0  
ORDER BY a_smsevent.common_code ASC , event_name, id ASC 

UPDATE a_event_contacts SET id = RTRIM(id) WHERE 1

SELECT id, SUBSTRING(c_id, 1, LOCATE('_', c_id) -1 ) FROM a_event_contacts WHERE LOCATE('_', c_id) > 0
UPDATE a_event_contacts SET c_id=SUBSTRING(c_id, 1, LOCATE('_', c_id) -1 )  WHERE LOCATE('_', c_id) > 0
UPDATE a_event_contacts SET contact_id=c_id WHERE 1


SELECT id, event_name, event_id, contact_id, name, common_code
FROM a_event_contacts
LEFT JOIN a_smsevent ON a_event_contacts.event_name=a_smsevent.name AND term1=1 
WHERE import_error=0  
ORDER BY contact_id ASC, id ASC


SELECT id, event_name, event_id, contact_id, name, common_code
FROM a_event_contacts
LEFT JOIN a_smsevent ON a_event_contacts.event_name=a_smsevent.name AND term1=1 
WHERE import_error=0