-- general remote login  KNRaE7zk46

alias rm='rm -v' ;
alias mv='mv -i' ;
alias ll='ls -l' ;

old: $$sms2TECHNO!
$$sms3TECHNO&

acc & itojuku setsuzoku (SSH)
	ユーザー   : development
	パスワード : 2RV9w4PprM87LDan

ACC DB access
	http://210.129.190.144/phpmyadmin
	root     89pQmsD6Y3gA4fME
	DB server ssh connection:	admin	NFZ3R74rCa5Dzws6  	logs@ /var/lib/mysql/slow.log
			search with /Query_time: 1[0..9]\. for queries taking between 10 to 20 seconds
	acc user on backup server:	acc_tool		2fP4pex4TEWNznar

	ACC WEB asahiculture.jp: accuser Vb4xYBHs

itojuku DB access
	http://210.239.38.150/phpmyadmin   https://itoujuku-db.e-school.jp/phpmyadmin
	root   H8wnWGDxZEm9567d
	https://tensaku-db.e-school.jp/phpmyadmin
	root   H6895DEdXpkAS3zs

Yamaha AWS remote desktop
	Username: metehan.ozcan@lassy.aws.infosys.yamaha.com.local
	Password: rq(l9RWixRRz

http://172.22.39.176/phpMyAdmin
	lassy
	rR7Fz9xV58weYC3J

ssh 
	210.129.190.154	su development/2RV9w4PprM87LDan  // key file same as 236 
ftp 
	210.129.190.154	development/2RV9w4PprM87LDan -> nG8e7CDL5H6xb9fX  ->  74nKFXaHV6rkg85B 	// key file same as 236

-- technopian new servers login
ssh 	
	211.133.135.208	su development/2RV9w4PprM87LDan  // same as itojuku including key files
	211.133.135.210	su development/2RV9w4PprM87LDan  // same as itojuku including key filesv
ftp 
	
	211.133.135.208	development/2RV9w4PprM87LDan	// same as itojuku including key files
	211.133.135.210	su development/2RV9w4PprM87LDan  // same as itojuku including key filesv

phpmyadmin
	http://210.129.190.234/phpmyadmin/	  	https://e-school.jp/phpmyadmin/
			db3tcpmaster/94pwVh5CGD
	http://210.129.190.236/phpmyadmin/		https://cheery.e-school.jp/phpmyadmin/
			root/efDA4ENJ7an3b9C6
			
sawa ftp		sawa_ftp		wUJmYSEtn628Z3x5
willway ftp	willway_ftp	k78WU9fbhQr2NJF4

-- willway server
ユーザー   : development
パスワード : nG8e7CDL5H6xb9fX	->	2RV9w4PprM87LDan
https://willway.e-school.jp/phpmyadmin
	root	XghAtb523sD9BRW6

-- TKG server
ユーザー   : development
パスワード : 2RV9w4PprM87LDan
		DB:		revolution_sms		Mx3umaDLbSEXtwEB
				root 				G26gZ98kbSKeM3fL


caplan ssh  XX 	caplanschool
ampi ssh	XX		caplantuho
DB				technopian/technopian
  https://aschool-jaca.caplan.jp/phpmyadmin
	
-- checking log files

●ご確認ください
・以下コマンドを sudo 経由で実行することで、root権限での実行が可能です
        /bin/cat
        /usr/bin/grep
        /usr/bin/less
        /usr/bin/view
        /bin/ls
        /bin/zcat          #追加いたしました
        /usr/bin/zgrep  #追加いたしました

    ex) [development] $ sudo zcat /var/log/httpd/www.youbuy.jp/access_log.4.gz | less
・Tabキーでの補完が効きませんので sudo ls との併用でフルパスを確認/指定しながらご利用ください

ls /var/log/maillog  ->   email log
sudo less /var/log/httpd/itojuku_t.e-school.jp/error_log  access_log
sudo zcat /var/log/httpd/itojuku_t.e-school.jp/error_log.1.gz|less  for old log files
sudo less /var/log/httpd/error_log   log file for general apache, e.g. batch i.e. 

-- ACC WEB server log check
egrep -v yoyaku_deletion_batch_cls php_errors.log-20150830 | egrep -v SERVER_NAME | less


mv htdocs htdocsOLD; mv htdocsNEW htdocs; mv htdocs/SCHOOL_CACHE_* .; mv htdocsOLD/SCHOOL_CACHE_* htdocs/; mv htdocsOLD/include/help htdocs/include/ ;

mv htdocs/test htdocs/testOLD; mv test htdocs/ ; mv htdocs/testOLD/SCHOOL_CACHE_* htdocs/test/ ;


8.8 Run repaire DB and ACL
	if there is DB change run the url below
		index.php?module=Administration&action=MG_TblRepair_AN&manage_type=echo_all
	and	
		index.php?module=Administration&action=MG_TblRepair_AN&manage_type=repair_all
		
	if there is new module run the url below	
		index.php?module=ACL&action=install_actions

-- to check DB tables
mysqlcheck -A -m --auto-repair --user=root --password='!tact.home1'
mysqlcheck misuzu -m --auto-repair --user=root --password='!tact.home1'

-- kaplan cache move
tar -cvzf cachekaplan.tar.gz --after-date='5 days ago' .
scp cachekaplan.tar.gz root@203.183.110.155:/home/development/ ;
- kaplan db move
scp kaplandb.tar.gz root@203.183.110.155:/home/development/ ;

mysqldump --add-drop-table -u root -pkalici1. niki-selection > niki-selection.sql

OLD server setsuzoku jouhou

admin 					A92nXb5fDR

mysqlcheck kaplansms -m --auto-repair --user=db1tcpmaster --password='6T5Nf3YsmP'  
mysqlcheck niki-selection -m --auto-repair --user=db2tcpmaster --password='42Txhe8ACK'  
mysqlcheck hiyoshi -m --auto-repair --user=db3tcpmaster --password='94pwVh5CGD'  

203.183.110.153  User:  development    FTP(su) Password:          KNRaE7zk46
203.183.110.155  User:  development    FTP(su) Password:          GET4sg3bX9
203.183.110.157  User:  development    FTP(su) Password:          hAb39BKCx4
SSH scp password   53feRKhF8W


CREATE TABLE sms_transactiontype (
  id varchar(36) NOT NULL default '',
  name varchar(100) NOT NULL default '',
  deleted tinyint(1) NOT NULL default '0',
  date_entered datetime NOT NULL default '0000-00-00 00:00:00',
  date_modified datetime NOT NULL default '0000-00-00 00:00:00',
  modified_user_id varchar(36) default NULL,
  created_by varchar(36) default NULL,
  keiri_category varchar(50) default NULL,
  apply_new_student tinyint(1) NOT NULL default '0',
  keiri_code int(8) default '0',
  description text,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `sms_transactiontype`
-- 

INSERT INTO sms_transactiontype (id, name, deleted, date_entered, date_modified, modified_user_id, created_by, keiri_category, apply_new_student, keiri_code, description) VALUES ('1', 'tuition', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 1, 401, 'Tuition fee for students'),
('2', 'tuition additional', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 401, 'Additional tuition fee for students'),
('3', 'transportation', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 665, 'Transporation fee for teachers'),
('14', 'salary', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 652, 'Salary for teachers'),
('4', 'parttime salary', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 653, 'Part-time salary for teachers'),
('5', 'textbook', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 1, 402, 'Textbook fee for students'),
('6', 'entrance', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 1, 402, 'Entrance fee for students'),
('7', 'rent', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 222, 'Rent fee deposited from teachers'),
('11', 'transfer', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 405, 'Money transfer from students'),
('12', 'transfer_out', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 665, 'Money transfer for teachers'),
('21', 'USKaplan commission', 0, '2006-02-15 00:00:00', '2006-02-15 00:00:00', NULL, NULL, 'general', 0, 403, 'Commission fee paid by US Kaplan');
        

ALTER TABLE `sms_fee` ADD `applied_contact` TINYINT( 1 ) DEFAULT '0' NOT NULL ;

UPDATE sms_fee, sms_transaction SET sms_fee.applied_contact=1 
WHERE sms_fee.deleted =0 AND sms_transaction.deleted =0
AND sms_fee.id = sms_transaction.fee_id ;



/*  20006.04.11  */

ALTER TABLE `event_contacts` CHANGE `event_contact_status` `event_contact_status` VARCHAR( 2 ) DEFAULT '0' NOT NULL ;


/*  20006.04.16 */

CREATE TABLE resource (
  id varchar(36) NOT NULL default '',
  school_id varchar(36) default NULL,
  date_entered datetime NOT NULL default '0000-00-00 00:00:00',
  date_modified datetime NOT NULL default '0000-00-00 00:00:00',
  modified_user_id varchar(36) NOT NULL default '',
  created_by varchar(36) default NULL,
  name varchar(150) default NULL,
  description text,
  category varchar(50) default NULL,
  location varchar(150) default NULL,
  deleted tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO resource (id, school_id, date_entered, date_modified, modified_user_id, created_by, name, description, category, location, deleted) VALUES 
('sssssss-1111', NULL, '2006-01-20 10:07:48', '2006-04-16 04:34:08', '1', '1', 'Aoyama', 'Aoyama Test Center', 'school', NULL, 0),
('483794c4-adee-a607-f685-4441c943a1e2', NULL, '2006-04-16 04:34:22', '2006-04-16 04:34:22', '1', '1', 'Ginza', 'Ginza English center', 'school', NULL, 0),
('58362bd0-5d1e-c3f9-efd1-4441c972601c', NULL, '2006-04-16 04:34:55', '2006-04-16 04:34:55', '1', '1', 'Ometosando', 'Ometosando English Center', 'school', NULL, 0);
        


CREATE TABLE users (
  id varchar(36) NOT NULL default '',
  user_name varchar(20) default NULL,
  user_password varchar(30) default NULL,
  user_hash varchar(32) default NULL,
  is_admin varchar(3) default '0',
  receive_notifications char(1) default '1',
  description text,
  date_entered datetime NOT NULL default '0000-00-00 00:00:00',
  date_modified datetime NOT NULL default '0000-00-00 00:00:00',
  modified_user_id varchar(36) default NULL,
  created_by varchar(36) default NULL,
  `status` varchar(25) default NULL,
  user_preferences text,
  deleted tinyint(1) NOT NULL default '0',
  portal_only tinyint(1) default '0',
  is_group tinyint(1) default '0',
  contact_id varchar(36) default NULL,
  smsgroup varchar(15) default NULL,
  PRIMARY KEY  (id),
  KEY user_name (user_name),
  KEY user_password (user_password)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO users (id, user_name, user_password, user_hash, is_admin, receive_notifications, description, date_entered, date_modified, modified_user_id, created_by, status, user_preferences, deleted, portal_only, is_group, contact_id, smsgroup) VALUES ('1', 'admin', 'adpexzg3FUZAk', '21232f297a57a5a743894a0e4a801fc3', 'on', '0', 'test user\r\ntest user2', '2006-01-13 01:59:35', '2006-04-13 11:05:58', '1', NULL, 'Active', '[encoded data, 4752 characters]', 0, 0, 0, NULL, 'teacher'),
('c4565fc9-30f9-719c-cb31-4437354e807d', 'demo', 'devFxxVFZsuos', 'fe01ce2a7fbac8fafaed7c982a04e229', 'off', '0', NULL, '2006-04-08 03:59:30', '2006-04-13 18:34:12', 'c4565fc9-30f9-719c-cb31-4437354e807d', '1', 'Active', 'YToyMDp7czo4OiJncmlkbGluZSI7czozOiJvZmYiO3M6MTI6Im1haWxtZXJnZV9vbiI7czozOiJvZmYiO3M6MTI6ImRpc3BsYXlfdGFicyI7YTo4OntpOjA7czo4OiJDYWxlbmRhciI7aToxO3M6ODoiQ29udGFjdHMiO2k6MjtzOjc6IlRlYWNoZXIiO2k6MztzOjc6IlN0dWRlbnQiO2k6NDtzOjY6IkVtYWlscyI7aTo1O3M6ODoiUmVzb3VyY2UiO2k6NjtzOjU6IktlaXJpIjtpOjc7czo2OiJDb3Vyc2UiO31zOjk6ImhpZGVfdGFicyI7YTowOnt9czoxMToicmVtb3ZlX3RhYnMiO2E6MDp7fXM6MjoidXQiO3M6MToiMSI7czoxMzoibWFpbF9mcm9tbmFtZSI7czoxMDoiS2FwbGFuIFNNUyI7czoxNjoibWFpbF9mcm9tYWRkcmVzcyI7czoyNDoidGVjaG5vc21zQHRlY2hub3BpYW4uY29tIjtzOjE3OiJtYWlsX3NtdHBhdXRoX3JlcSI7czowOiIiO3M6MTY6Im1haWxfcG9wYXV0aF9yZXEiO3M6MDoiIjtzOjMwOiJDb3Vyc2VfU1RVREVOVF9DVVNUT01fT1JERVJfQlkiO3M6Mzk6ImNvbnRhY3RzLmxhc3RfbmFtZSwgY29udGFjdHMuZmlyc3RfbmFtZSI7czoyNjoiQ291cnNlX0ZFRV9DVVNUT01fT1JERVJfQlkiO3M6NDY6InNtc19mZWUuc2VsbF9kYXRlIERFU0MgY29udGFjdHMubGFzdF9uYW1lIEFTQyAiO3M6MzQ6IkNvdXJzZV9UUkFOU0FDVElPTl9DVVNUT01fT1JERVJfQlkiO3M6NTE6InNtc190cmFuc2FjdGlvbi50X2RhdGUgREVTQyBjb250YWN0cy5sYXN0X25hbWUgQVNDICI7czoyNzoiU3R1ZGVudF9GRUVfQ1VTVE9NX09SREVSX0JZIjtzOjQ2OiJzbXNfZmVlLnNlbGxfZGF0ZSBERVNDIGNvbnRhY3RzLmxhc3RfbmFtZSBBU0MgIjtzOjI1OiJDb250YWN0c19DT05UQUNUX09SREVSX0JZIjtzOjM5OiJjb250YWN0cy5sYXN0X25hbWUsIGNvbnRhY3RzLmZpcnN0X25hbWUiO3M6MjA6IlN0YWZmX1NUQUZGX09SREVSX0JZIjtzOjM5OiJjb250YWN0cy5sYXN0X25hbWUsIGNvbnRhY3RzLmZpcnN0X25hbWUiO3M6MzA6IkNvdXJzZV9URUFDSEVSX0NVU1RPTV9PUkRFUl9CWSI7czozOToiY29udGFjdHMubGFzdF9uYW1lLCBjb250YWN0cy5maXJzdF9uYW1lIjtzOjM0OiJDb3Vyc2VfU01TRE9DVU1FTlRfQ1VTVE9NX09SREVSX0JZIjtOO3M6Mzc6IkNvdXJzZV9FVkVOVF9JTlNUQU5DRV9DVVNUT01fT1JERVJfQlkiO3M6MzI6InNtc19ldmVudF9pbnN0YW5jZS5mcm9tX2RhdGV0aW1lIjtzOjk6Imxhc3RUaGVtZSI7czo1OiJTdWdhciI7fQ==', 0, 0, 0, 'cead1c63-2afd-b555-6d60-443734c16b08', 'staff'),
('ee89c23e-17ae-f9cf-5dac-4411403dc1af', 'matsu', 'ma7/tbcmCo2H6', '7691de3fa16bd8aec6ad50139553f1fe', 'off', '0', NULL, '2006-03-10 09:03:36', '2006-03-18 05:30:39', '1', '1', 'Active', '', 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', 'student'),
('21c95d80-8265-5149-5538-44154f3734f0', 'saito', 'saBjohpr/rBbU', '20b46b82b477e76b30a00b0cc1257167', 'off', '0', NULL, '2006-03-13 10:54:05', '2006-03-18 05:31:27', '21c95d80-8265-5149-5538-44154f3734f0', '1', 'Active', '', 0, 0, 0, 'de6a6b20-3fb4-48d0-91bb-43d0a158a24e', 'student'),
('d03eabfe-24ed-4ca2-cd7f-441fc6d21ffe', 'TechnoSMS', NULL, NULL, '0', '1', NULL, '2006-03-21 09:26:51', '2006-03-21 09:26:51', '1', '1', NULL, 'YToxOntzOjg6InRpbWV6b25lIjtzOjEyOiJBc2lhL1lha3V0c2siO30=', 0, 0, 1, NULL, NULL);
        

CREATE TABLE users_schools (
  id varchar(36) NOT NULL default '',
  user_id varchar(36) default NULL,
  school_id varchar(36) default NULL,
  date_modified datetime default NULL,
  deleted tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY idx_user_sch_user (user_id),
  KEY idx_user_sch_school (school_id),
  KEY idx_users_schools (user_id,school_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO users_schools (id, user_id, school_id, date_modified, deleted) VALUES 
('c4565fc0-30f9-719c-cb31-37354e807d', '1', 'sssssss-1111', '2006-04-16 06:10:19', 0),
('c4565fc0-30f9-719c-cb31-44354e807d', '1', '483794c4-adee-a607-f685-4441c943a1e2', '2006-04-16 06:10:19', 0),
('c4565fc0-30f9-719c-cb31-44374e807d', '1', '58362bd0-5d1e-c3f9-efd1-4441c972601c', '2006-04-16 06:10:19', 0),
('c4565fc0-30f9-719c-cb31-443735807d', 'c4565fc9-30f9-719c-cb31-4437354e807d', 'sssssss-1111', '2006-04-16 06:10:19', 0),
('c4565fc0-30f9-719c-cb31-4437354e7d', 'c4565fc9-30f9-719c-cb31-4437354e807d', '483794c4-adee-a607-f685-4441c943a1e2', '2006-04-16 06:10:19', 0),
('c4565fc0-30f9-719c-cb31-4437354e80', 'ee89c23e-17ae-f9cf-5dac-4411403dc1af', 'sssssss-1111', '2006-04-16 06:10:19', 0),
('c4565fc0-30f9-719c-cb31-437354e807', '21c95d80-8265-5149-5538-44154f3734f0', '483794c4-adee-a607-f685-4441c943a1e2', '2006-04-16 06:10:19', 0);
        
ALTER TABLE `contacts` ADD `school_id` VARCHAR( 36 ) DEFAULT 'sssssss-1111' NOT NULL ;


/*  20006.04.17 */

ALTER TABLE `course_category` ADD `school_id` VARCHAR( 36 ) DEFAULT 'sssssss-1111' NOT NULL ;
ALTER TABLE `smsevent` ADD `school_id` VARCHAR( 36 ) DEFAULT 'sssssss-1111' NOT NULL ;


/*  20006.04.17 */

ALTER TABLE `sms_fee` ADD `discount_percentage` FLOAT DEFAULT '0' NOT NULL ;
ALTER TABLE `sms_fee` ADD `discount_price` FLOAT DEFAULT '0' NOT NULL ;

ALTER TABLE `sms_fee` DROP `discount_price` ;
ALTER TABLE `sms_fee` ADD `discount_price` FLOAT;


/*  20006.04.27 */

UPDATE sms_fee SET discount_price = NULL WHERE discount_price = 0;


/* 2006.04.30 */

/* CONFIG FILE AT DEMO SERVER */
-- http://www.phpmyadmin.net
-- 
-- 
-- Database: `schoolmng`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `config`
-- 

CREATE TABLE config (
  category varchar(32) default NULL,
  name varchar(32) default NULL,
  `value` text
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `config`
-- 

INSERT INTO config (category, name, value) VALUES ('notify', 'fromaddress', 'technosms@technopian.com'),
('notify', 'fromname', 'TechnoSMS'),
('notify', 'send_by_default', '0'),
('notify', 'on', '0'),
('mail', 'smtpserver', 'mail.anzenhosting.jp'),
('mail', 'smtpport', '25'),
('mail', 'sendtype', 'SMTP'),
('mail', 'smtpuser', 'technosms@technopian.com'),
('mail', 'smtppass', 'technosms1.'),
('mail', 'smtpauth_req', '1'),
('info', 'sugar_version', '4.0.0'),
('MySettings', 'tab', 'YToyNjp7aTowO3M6NDoiSG9tZSI7aToxO3M6NzoiaUZyYW1lcyI7aToyO3M6ODoiQ2FsZW5kYXIiO2k6MztzOjEwOiJBY3Rpdml0aWVzIjtpOjQ7czo4OiJDb250YWN0cyI7aTo1O3M6ODoiQWNjb3VudHMiO2k6NjtzOjc6IlRlYWNoZXIiO2k6NztzOjc6IlN0dWRlbnQiO2k6ODtzOjk6IkRvY3VtZW50cyI7aTo5O3M6NjoiRW1haWxzIjtpOjEwO3M6ODoiUmVzb3VyY2UiO2k6MTE7czoxMToiVHJhbnNhY3Rpb24iO2k6MTI7czo1OiJLZWlyaSI7aToxMztzOjk6IlNNU0dyb3VwcyI7aToxNDtzOjE0OiJDb3Vyc2VDYXRlZ29yeSI7aToxNTtzOjY6IkNvdXJzZSI7aToxNjtzOjg6IlNNU0V2ZW50IjtpOjE3O3M6MTM6IkV2ZW50SW5zdGFuY2UiO2k6MTg7czozOiJGZWUiO2k6MTk7czo1OiJMZWFkcyI7aToyMDtzOjEzOiJPcHBvcnR1bml0aWVzIjtpOjIxO3M6NToiQ2FzZXMiO2k6MjI7czo0OiJCdWdzIjtpOjIzO3M6NzoiUHJvamVjdCI7aToyNDtzOjU6IkZlZWRzIjtpOjI1O3M6OToiRGFzaGJvYXJkIjt9'),
('portal', 'on', '0'),
('Update', 'CheckUpdates', 'automatic'),
('license', 'last_validation_fail', '2006-03-19 14:17:36'),
('license', 'last_validation', 'success'),
('license', 'vk_end_date', '2006-01-13 02:01:21'),
('license', 'validation_key', 'YToxOntzOjg6InZlcmlmaWVkIjtiOjA7fQ=='),
('license', 'latest_versions', ''),
('Update', 'last_check_date', '1146400104'),
('proxy', 'host', ''),
('proxy', 'port', '80'),
('proxy', 'username', ''),
('proxy', 'password', ''),
('system', 'mailmerge_on', '0'),
('proxy', 'auth', '0'),
('proxy', 'on', '0'),
('system', 'skypeout_on', '0'),
('license', 'msg_admin', 'The latest version of SugarCRM is 4.0.1. Please visit www.sugarcrm.com to acquire the latest version.'),
('license', 'msg_all', ''),
('MySettings', 'disable_useredit', 'yes');
-- http://www.phpmyadmin.net
-- 
-- 
-- Database: `schoolmng`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `users`
-- 

CREATE TABLE users (
  id varchar(36) NOT NULL default '',
  user_name varchar(20) default NULL,
  user_password varchar(30) default NULL,
  user_hash varchar(32) default NULL,
  is_admin varchar(3) default '0',
  receive_notifications char(1) default '1',
  description text,
  date_entered datetime NOT NULL default '0000-00-00 00:00:00',
  date_modified datetime NOT NULL default '0000-00-00 00:00:00',
  modified_user_id varchar(36) default NULL,
  created_by varchar(36) default NULL,
  `status` varchar(25) default NULL,
  user_preferences text,
  deleted tinyint(1) NOT NULL default '0',
  portal_only tinyint(1) default '0',
  is_group tinyint(1) default '0',
  contact_id varchar(36) default NULL,
  smsgroup varchar(15) default NULL,
  PRIMARY KEY  (id),
  KEY user_name (user_name),
  KEY user_password (user_password)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `users`
-- 

INSERT INTO users (id, user_name, user_password, user_hash, is_admin, receive_notifications, description, date_entered, date_modified, modified_user_id, created_by, status, user_preferences, deleted, portal_only, is_group, contact_id, smsgroup) VALUES ('1', 'admin', 'adpexzg3FUZAk', '21232f297a57a5a743894a0e4a801fc3', 'on', '0', 'test user\r\ntest user2', '2006-01-13 01:59:35', '2006-04-19 03:01:29', '1', NULL, 'Active', '[encoded data, 7512 characters]', 0, 0, 0, NULL, 'teacher'),
('c4565fc9-30f9-719c-cb31-4437354e807d', 'demo', 'devFxxVFZsuos', 'fe01ce2a7fbac8fafaed7c982a04e229', 'off', '0', NULL, '2006-04-08 03:59:30', '2006-04-28 02:50:27', 'c4565fc9-30f9-719c-cb31-4437354e807d', '1', 'Active', '[encoded data, 8524 characters]', 0, 0, 0, 'ac063392-5adb-dd3f-532e-44332be2337b', 'staff'),
('ee89c23e-17ae-f9cf-5dac-4411403dc1af', 'matsu', 'ma7/tbcmCo2H6', '7691de3fa16bd8aec6ad50139553f1fe', 'off', '0', NULL, '2006-03-10 09:03:36', '2006-04-18 15:40:17', 'ee89c23e-17ae-f9cf-5dac-4411403dc1af', '1', 'Active', 'YTo0OntzOjg6InRpbWV6b25lIjtzOjEwOiJBc2lhL1Rva3lvIjtzOjI6InV0IjtpOjE7czo5OiJsYXN0VGhlbWUiO3M6NToiU3VnYXIiO3M6MjU6IkNvbnRhY3RzX0NPTlRBQ1RfT1JERVJfQlkiO3M6Mzk6ImNvbnRhY3RzLmxhc3RfbmFtZSwgY29udGFjdHMuZmlyc3RfbmFtZSI7fQ==', 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', 'student'),
('21c95d80-8265-5149-5538-44154f3734f0', 'saito', 'saBjohpr/rBbU', '20b46b82b477e76b30a00b0cc1257167', 'off', '0', NULL, '2006-03-13 10:54:05', '2006-04-18 15:04:48', '21c95d80-8265-5149-5538-44154f3734f0', '1', 'Active', 'YTo2OntzOjg6InRpbWV6b25lIjtzOjEwOiJBc2lhL1Rva3lvIjtzOjI6InV0IjtpOjE7czoyMDoiS2VpcmlfS0VJUklfT1JERVJfQlkiO3M6Mjc6InNtc190cmFuc2FjdGlvbi50X2RhdGUgZGVzYyI7czoyNDoiVGVhY2hlcl9URUFDSEVSX09SREVSX0JZIjtzOjM5OiJjb250YWN0cy5sYXN0X25hbWUsIGNvbnRhY3RzLmZpcnN0X25hbWUiO3M6MjU6IkNvbnRhY3RzX0NPTlRBQ1RfT1JERVJfQlkiO3M6Mzk6ImNvbnRhY3RzLmxhc3RfbmFtZSwgY29udGFjdHMuZmlyc3RfbmFtZSI7czo5OiJsYXN0VGhlbWUiO3M6NToiU3VnYXIiO30=', 0, 0, 0, 'de6a6b20-3fb4-48d0-91bb-43d0a158a24e', 'student'),
('d03eabfe-24ed-4ca2-cd7f-441fc6d21ffe', 'TechnoSMS', NULL, NULL, '0', '1', NULL, '2006-03-21 09:26:51', '2006-03-21 09:26:51', '1', '1', NULL, 'YToxOntzOjg6InRpbWV6b25lIjtzOjEyOiJBc2lhL1lha3V0c2siO30=', 0, 0, 1, NULL, NULL);


/*  20006.05.20 */


-- Table structure for table `sms_groupsaleno`

CREATE TABLE sms_groupsaleno (
  salesno varchar(10) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO sms_groupsaleno (salesno) VALUES ('10000');

ALTER TABLE `sms_fee` ADD `salesno` VARCHAR( 10 ) ;

ALTER TABLE `sms_transaction` ADD `salesno` VARCHAR( 10 ) ;


CREATE TABLE `sms_salesgroup` (
  `id` varchar(36) NOT NULL default '',
  `name` varchar(100) NOT NULL default '',
  `event_id` varchar(36) default NULL,
  `contact_id` varchar(36) default NULL,
  `type_id` varchar(36) NOT NULL default '',
  `occurences` int(3) NOT NULL default '1',
  `occurences_hour` double NOT NULL default '1',
  `deleted` tinyint(1) NOT NULL default '0',
  `date_entered` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `sell_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) default NULL,
  `created_by` varchar(36) default NULL,
  `basic_fee` int(11) default '0',
  `unit_type` varchar(36) NOT NULL default '',
  `unit_no` int(8) default '0',
  `period` int(8) default '0',
  `period_no` int(8) default '0',
  `due_to` varchar(2) NOT NULL default '0',
  `description` text,
  `applied_contact` tinyint(1) NOT NULL default '0',
  `discount_percentage` float NOT NULL default '0',
  `discount_price` float default NULL,
  `salesno` varchar(10) default NULL,
  PRIMARY KEY  (`id`),
  KEY `event_id` (`event_id`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO sms_salesgroup (id, name, event_id, contact_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) VALUES 
('48946021-951e-5ee1-20ca', '受講料', '6689d8a1-0d54-737c-236c-442b5dea67ea', '18ff4d2b-6530-0b36-8931-4422345c0fc5', '1', 1, 1, 0, '2006-03-31 09:00:08', '2006-05-22 09:58:43', '2006-05-22 01:00:00', '1', '1', 60000, '0', 0, 1, 0, '3', NULL, 0, 0, NULL, '10001'),
('81d5529a-c4ca-61ca-6771', '受講料', '6689d8a1-0d54-737c-236c-442b5dea67ea', '69394b16-519e-d25f-aae0-440197f5b5e9', '1', 1, 1, 0, '2006-03-31 09:00:08', '2006-05-23 01:43:29', '2006-05-23 01:00:00', '1', '1', 60000, '0', 0, 1, 0, '3', NULL, 0, 0, NULL, '10002'),
('db7e1b66-40ec-0728-315a', '受講料', 'b065ffe7-f2fa-6d1b-a669-4423a658cd2a', 'de6a6b20-3fb4-48d0-91bb-43d0a158a24e', '1', 1, 1, 0, '2006-04-24 12:00:50', '2006-05-23 01:44:14', '2006-05-22 01:00:00', '1', '1', 78000, '0', 0, 1, 0, '3', NULL, 0, 0, NULL, '10003');

INSERT INTO sms_salesgroup (id, name, event_id, contact_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) VALUES 
('e81124fa-b824-dbb6-95ad', '受講料', 'b065ffe7-f2fa-6d1b-a669-4423a658cd2a', NULL, '1', 1, 1, 0, '2006-04-24 12:00:50', '2006-05-23 01:44:14', '2006-05-22 01:00:00', '1', '1', 78000, '0', 0, 1, 0, '3', NULL, 0, 0, NULL, '10000');

ALTER TABLE `sms_salesgroup` CHANGE `type_id` `type_id` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci ;

UPDATE `sms_transaction` SET `payment_type` = 'cash' ;


/*  2006.05.27 */

ALTER TABLE `sms_transaction` CHANGE `fee_id` `fee_id` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci 


/*  2006.05.29 */

-- 
-- Table structure for table `sms_transaction`
-- 

CREATE TABLE sms_transaction (
  id varchar(36) NOT NULL default '',
  fee_id varchar(36) NOT NULL default '',
  type_id varchar(36) NOT NULL default '',
  document_id varchar(36) default NULL,
  deleted tinyint(1) NOT NULL default '0',
  date_entered datetime NOT NULL default '0000-00-00 00:00:00',
  date_modified datetime NOT NULL default '0000-00-00 00:00:00',
  modified_user_id varchar(36) default NULL,
  created_by varchar(36) default NULL,
  sell_date datetime NOT NULL default '0000-00-00 00:00:00',
  due_date datetime NOT NULL default '0000-00-00 00:00:00',
  t_date datetime NOT NULL default '0000-00-00 00:00:00',
  in_amount int(11) NOT NULL default '0',
  out_amount int(11) NOT NULL default '0',
  actual_in_amount int(11) NOT NULL default '0',
  actual_out_amount int(11) NOT NULL default '0',
  commission int(11) NOT NULL default '0',
  balance int(11) default '0',
  actual tinyint(1) NOT NULL default '0',
  contact_id varchar(36) default NULL,
  event_id varchar(36) default NULL,
  payment_type varchar(16) NOT NULL default '',
  bank_account_id varchar(36) default NULL,
  description text,
  salesno varchar(10) default NULL,
  PRIMARY KEY  (id),
  KEY contact_id (contact_id),
  KEY event_id (event_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO sms_transaction (id, fee_id, type_id, document_id, deleted, date_entered, date_modified, modified_user_id, created_by, sell_date, due_date, t_date, in_amount, out_amount, actual_in_amount, actual_out_amount, commission, balance, actual, contact_id, event_id, payment_type, bank_account_id, description, salesno) VALUES ('adc51a3f-95ed-4f29-6bc3-44331f9c3ea7', '209a06cb-d39b-aede-efa9-44331f5d3abc', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:49:05', NULL, NULL, '2006-04-03 01:00:00', '2006-04-15 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', NULL, 'cash', NULL, '標準テキスト101', NULL),
('abfbc445-e370-b175-7e13-44331fa3ff5b', '1dedd12d-b4f9-eaaf-e53d-44331f6d72f6', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:49:05', NULL, NULL, '2006-04-03 01:00:00', '2006-04-15 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, '4c61b564-3f6b-c7d0-439a-4423a73496cb', NULL, 'cash', NULL, '標準テキスト101', NULL),
('ab1c8032-1808-c204-00d3-44331fdac308', '1fc15aad-53be-0e7d-fcd1-44331f86bdc7', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:49:05', NULL, NULL, '2006-04-03 01:00:00', '2006-04-15 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, '4c61b564-3f6b-c7d0-439a-4423a73496cb', NULL, 'cash', NULL, '標準テキスト101', NULL),
('cd120909-81cb-b74d-3457-4433189552d5', 'b4e22621-bf78-9d3f-fbbd-4428998ea312', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', '1', NULL, '2006-03-28 01:00:00', '2006-04-10 01:00:00', '2006-03-30 01:00:00', 4000, 0, 4000, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', NULL, 'cash', NULL, 'テキスト（入門1）', NULL),
('31f3a7e4-ca26-6637-cdbc-444cc633861b', '85a334cf-e6bb-5e75-61b7-444cc61ea5eb', '6', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:39:49', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-02-25 01:00:00', '2006-02-28 01:00:00', '2006-02-26 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '4c61b564-3f6b-c7d0-439a-4423a73496cb', 'b065ffe7-f2fa-6d1b-a669-4423a658cd2a', 'cash', NULL, '入学金', NULL),
('b50a2234-092f-88d3-56c2-444c76942af5', 'c7214ca9-fba2-6740-ad6a-4433112ad887', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:46:54', NULL, NULL, '2006-04-04 01:00:00', '2006-04-07 00:00:00', '0000-00-00 00:00:00', 60000, 0, 0, 0, 0, 0, 0, '69394b16-519e-d25f-aae0-440197f5b5e9', '6689d8a1-0d54-737c-236c-442b5dea67ea', 'cash', NULL, '受講料', NULL),
('b3c82f80-3628-5f12-f41b-444c7646e10a', 'c6559109-c170-1153-5b5c-443311c00e18', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:46:49', NULL, NULL, '2006-04-04 01:00:00', '2006-04-07 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, '69394b16-519e-d25f-aae0-440197f5b5e9', '6689d8a1-0d54-737c-236c-442b5dea67ea', 'cash', NULL, 'テキスト代', NULL),
('ceac7fc3-6f40-47d7-dbbe-4430dee17f04', 'aeb64b1f-fa77-5474-7604-4430db89ec67', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-05 00:55:43', NULL, NULL, '2006-03-20 01:00:00', '2006-04-30 00:00:00', '0000-00-00 00:00:00', 23500, 0, 0, 0, 0, 0, 0, '1689dc03-2bbc-b11d-39a8-4423a7cd14a1', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '受講料', NULL),
('a584fe78-56a1-14c1-08c9-4430d9ac7b5b', '964b6200-96ba-cd56-acb7-4430d724691e', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:20:04', NULL, NULL, '2006-03-30 01:00:00', '2006-03-08 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, '831dc1f3-2e74-c170-b571-4423d4acd340', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, 'テキスト代', NULL),
('ce183e68-1984-d38b-99e9-4430de72f0fc', 'abbdff26-bfcd-e7ff-cab1-4430db443bdf', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-05 00:55:35', NULL, NULL, '2006-03-20 01:00:00', '2006-04-08 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, '1689dc03-2bbc-b11d-39a8-4423a7cd14a1', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, 'テキスト代', NULL),
('a7bc5651-b783-18ca-0d1c-4430d9158c81', '4b380cc9-7c8d-bd62-c973-4430d7cbb197', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:18:44', NULL, NULL, '2006-03-28 01:00:00', '2006-03-31 00:00:00', '0000-00-00 00:00:00', 23500, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '受講料', NULL),
('a9a793ad-8bb4-3e3e-a11c-4430d90566fc', '4bf082bd-a490-8037-5a2b-4430d738151a', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:19:11', NULL, NULL, '2006-03-28 01:00:00', '2006-03-31 00:00:00', '0000-00-00 00:00:00', 23500, 0, 0, 0, 0, 0, 0, 'de6a6b20-3fb4-48d0-91bb-43d0a158a24e', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '受講料', NULL),
('a9527a5c-493a-8e04-5e0b-4430d9e01aeb', '49c7890b-e0f2-243a-a2b7-4430d7fccd8e', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:15:47', NULL, NULL, '2006-03-28 01:00:00', '2006-03-08 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, 'de6a6b20-3fb4-48d0-91bb-43d0a158a24e', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, 'テキスト代', NULL),
('ab3905cb-abc8-d171-baba-4430d9727c32', '4a77b758-f844-3b01-de85-4430d7f705cb', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:19:43', NULL, NULL, '2006-03-28 01:00:00', '2006-03-08 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, '9166dec0-c033-d207-0a5b-43d0a1033deb', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, 'テキスト代', NULL),
('ab8e47c4-da60-129f-4dc3-4430d9165842', '4c9f62b9-a87b-0079-a146-4430d7f0baeb', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:19:36', NULL, NULL, '2006-03-28 01:00:00', '2006-03-31 00:00:00', '0000-00-00 00:00:00', 23500, 0, 0, 0, 0, 0, 0, '9166dec0-c033-d207-0a5b-43d0a1033deb', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '受講料', NULL),
('a5db3896-cfa5-bd34-95a0-4430d9115647', '97ca874b-7889-4ef7-58db-4430d75a418f', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:19:51', NULL, NULL, '2006-03-30 01:00:00', '2006-03-31 00:00:00', '0000-00-00 00:00:00', 23500, 0, 0, 0, 0, 0, 0, '831dc1f3-2e74-c170-b571-4423d4acd340', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '受講料', NULL),
('a3f1b145-a57c-a339-f364-4430d9631bc8', '988a4718-0bb2-fa8f-c283-4430d7b268d3', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:20:53', NULL, NULL, '2006-03-30 01:00:00', '2006-03-31 00:00:00', '0000-00-00 00:00:00', 23500, 0, 0, 0, 0, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '受講料', NULL),
('cdfe5b6e-b104-1150-20c2-4428a7e35ad8', 'b31f8446-8c42-0a9b-a6d7-442899c2f82e', '6', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', '1', NULL, '2006-03-05 01:00:00', '2006-03-10 01:00:00', '2006-03-06 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', NULL, 'cash', NULL, '入学金', NULL),
('a3031c5b-bbf7-7b00-19a2-4430d92b133b', '81a30477-4e18-b6c6-258c-4430d6c55a40', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:20:45', NULL, NULL, '2006-04-02 01:00:00', '2006-03-31 00:00:00', '0000-00-00 00:00:00', 23500, 0, 0, 0, 0, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '受講料', NULL),
('a38e72db-e716-6442-c597-4430d958a8f6', '97080751-3b20-aa05-2a1f-4430d7633036', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:20:38', NULL, NULL, '2006-03-30 01:00:00', '2006-03-08 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, 'テキスト代', NULL),
('a271e378-85b1-e2b3-249f-4430d9a2796e', '74ee02c1-9e36-1272-49ee-4430d6ecb317', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:20:31', NULL, NULL, '2006-04-02 01:00:00', '2006-03-08 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, 'テキスト代', NULL),
('df30953c-2212-738f-cb7d-444cc7946f9d', 'e8b0f3b6-6652-1d1b-6c62-444cc7292016', '5', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:47:11', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-02 01:00:00', '2006-03-04 01:00:00', '2006-03-03 01:00:00', 7000, 0, 7000, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '英会話テキスト2', NULL),
('8763f9b6-e8ca-8bc4-ccca-4445a7321aa2', '16a372e2-8713-adfa-b175-4445a7da1c72', '6', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-05 01:00:00', '2006-04-10 01:00:00', '2006-04-10 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', NULL, 'cash', NULL, '入学金', NULL),
('ddfe47e5-864f-565c-f53b-444cc73a69a1', 'e3f99e76-9dad-ec7c-fae3-444cc70522dd', '6', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:47:45', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-02 01:00:00', '2006-03-04 01:00:00', '2006-03-02 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '入学金', NULL),
('dc85844c-aff3-94b8-999c-444cc7ad0119', 'ddf9660b-6693-c81c-67d8-444cc7637636', '1', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:47:31', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-02 01:00:00', '2006-03-04 01:00:00', '2006-03-03 01:00:00', 68000, 0, 68000, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '受講料', NULL),
('cff337f4-0e4d-49f3-ecb2-4430de33c7ff', 'af6b1f55-48bd-82e2-690d-4430dba1094c', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-05 00:55:22', NULL, NULL, '2006-03-20 01:00:00', '2006-04-30 00:00:00', '0000-00-00 00:00:00', 23500, 0, 0, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '受講料', NULL),
('6caa4e78-2e28-595d-dc79-4430d2760b1b', '9866f7cb-c3da-4f06-7241-4430d2680173', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 07:55:08', NULL, NULL, '2006-04-02 01:00:00', '2006-04-30 00:00:00', '0000-00-00 00:00:00', 23500, 0, 0, 0, 0, 0, 0, '1689dc03-2bbc-b11d-39a8-4423a7cd14a1', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '受講料', NULL),
('d0870a68-5f16-e667-8ef6-4430de48eb5a', 'ac75eb70-93ba-10e1-772a-4430db83fbc2', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-05 00:55:06', NULL, NULL, '2006-03-20 01:00:00', '2006-04-08 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, 'テキスト代', NULL),
('4c9edffb-55b2-a960-911d-442cef40e3d3', '2d4d9f08-b012-774e-610e-442cefe3dfe5', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-05 00:37:23', NULL, NULL, '2006-03-31 01:00:00', '2006-02-23 00:00:00', '0000-00-00 00:00:00', 60000, 0, 0, 0, 0, 0, 0, '1689dc03-2bbc-b11d-39a8-4423a7cd14a1', '6689d8a1-0d54-737c-236c-442b5dea67ea', 'cash', NULL, '受講料', NULL),
('a7684d27-8cb5-526e-5dfe-4430d98aefd1', '4902a3f4-13f4-f2cb-cbe7-4430d7469907', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-03 08:18:52', NULL, NULL, '2006-03-28 01:00:00', '2006-03-08 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, 'テキスト代', NULL),
('efe03012-3746-919b-0445-443c733ec38a', '8b86cef3-93a9-da3e-55c2-443c73cb9b21', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-19 02:36:28', NULL, NULL, '2006-04-12 01:00:00', '2006-08-31 00:00:00', '0000-00-00 00:00:00', 100000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', 'e5b41b60-c556-3af4-5b9f-43fec3ff84f7', 'cash', NULL, 'Tuition Fee for English Advanced - 5 ', NULL),
('ae925abd-a2eb-a104-dc8e-44331fbb1f54', '1eda5542-2a5a-6daa-f2e0-44331f8457bf', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:49:05', NULL, NULL, '2006-04-03 01:00:00', '2006-04-15 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', NULL, 'cash', NULL, '標準テキスト101', NULL),
('862a9eda-bda7-8336-f17d-4445a76ac6f2', '2818c4ca-6289-7a58-3c01-4445a7b9ea16', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', NULL, NULL, '2006-04-05 01:00:00', '2006-04-10 00:00:00', '0000-00-00 00:00:00', 50000, 0, 0, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', NULL, 'cash', NULL, '受講料', NULL),
('a3ac431d-c0d8-401f-bf2c-4445a7b38b6a', 'a7880702-a887-c672-6113-4445a7021b9a', '6', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', NULL, NULL, '2006-04-05 01:00:00', '2006-04-10 00:00:00', '0000-00-00 00:00:00', 18900, 0, 0, 0, 0, 0, 0, '1689dc03-2bbc-b11d-39a8-4423a7cd14a1', NULL, 'cash', NULL, '入学金', NULL),
('a254b4c4-a528-8eac-fc42-4445a75c2d0c', '1f2185e0-d0fe-7b8a-37d6-4445a7ded4ca', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', NULL, NULL, '2006-04-05 01:00:00', '2006-04-10 00:00:00', '0000-00-00 00:00:00', 50000, 0, 0, 0, 0, 0, 0, '1689dc03-2bbc-b11d-39a8-4423a7cd14a1', NULL, 'cash', NULL, '受講料', NULL),
('9e5dd073-9d02-dac9-ec4c-443321547836', '3e0b2306-09b8-ed17-8f63-443321d94fa3', '6', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', NULL, NULL, '2006-04-02 01:00:00', '2006-04-10 00:00:00', '0000-00-00 00:00:00', 18900, 0, 0, 0, 0, 0, 0, '4c61b564-3f6b-c7d0-439a-4423a73496cb', NULL, 'cash', NULL, '入学金', NULL),
('9f309315-1323-1d18-0ccc-4433218873a0', '41a77570-3130-abe9-a67c-4433215cfc41', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', NULL, NULL, '2006-04-02 01:00:00', '2006-04-10 00:00:00', '0000-00-00 00:00:00', 50000, 0, 0, 0, 0, 0, 0, '4c61b564-3f6b-c7d0-439a-4423a73496cb', NULL, 'cash', NULL, '受講料', NULL),
('a002d537-4e50-2698-15e7-44332160e9c0', '44373567-b1ac-04ea-079f-44332180ffc3', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', NULL, NULL, '2006-04-02 01:00:00', '2006-04-10 00:00:00', '0000-00-00 00:00:00', 4000, 0, 0, 0, 0, 0, 0, '4c61b564-3f6b-c7d0-439a-4423a73496cb', NULL, 'cash', NULL, 'テキスト（入門1）', NULL),
('a21c2225-4e39-e9f8-3cae-443321f2b63e', '4024f664-936b-525b-98b5-44332135f8dc', '6', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', NULL, NULL, '2006-04-02 01:00:00', '2006-04-10 00:00:00', '0000-00-00 00:00:00', 18900, 0, 0, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', NULL, 'cash', NULL, '入学金', NULL),
('a30af639-5447-c28f-bed5-44332164bfb7', '425e0fcf-73ce-6f24-0289-443321145ed7', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', NULL, NULL, '2006-04-02 01:00:00', '2006-04-10 00:00:00', '0000-00-00 00:00:00', 50000, 0, 0, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', NULL, 'cash', NULL, '受講料', NULL),
('a3e18427-64ae-d918-607a-4433217808b4', '45145b2b-7dd2-c702-8bbb-4433212b67a8', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', NULL, NULL, '2006-04-02 01:00:00', '2006-04-10 00:00:00', '0000-00-00 00:00:00', 4000, 0, 0, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', NULL, 'cash', NULL, 'テキスト（入門1）', NULL),
('46ae7b81-8100-5b64-480c-44346d0220c6', 'd3392a24-7174-7cf6-8eff-443464e762ab', '6', NULL, 1, '0000-00-00 00:00:00', '2006-04-19 02:37:21', NULL, NULL, '2006-04-06 01:00:00', '2006-04-30 00:00:00', '0000-00-00 00:00:00', 18900, 0, 0, 0, 0, 0, 0, '618706bc-f73f-f30a-6dbe-4401b4486ff3', 'e5b41b60-c556-3af4-5b9f-43fec3ff84f7', 'cash', NULL, 'Entrance Fee for English Advanced', NULL),
('45ca9f4c-bf7d-c940-b926-44346db7ccca', 'cfe593ae-37b8-d48a-2fdf-4434645c198d', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:43:36', NULL, NULL, '2006-03-06 01:00:00', '2006-03-31 00:00:00', '0000-00-00 00:00:00', 500000, 0, 0, 0, 0, 0, 0, '618706bc-f73f-f30a-6dbe-4401b4486ff3', 'e5b41b60-c556-3af4-5b9f-43fec3ff84f7', 'cash', NULL, 'Tuition Fee for English Advanced', NULL),
('49d5df27-55a3-4851-bab6-4434f62e7b16', '', '5', NULL, 1, '2006-04-06 11:07:17', '2006-04-19 02:57:07', '1', '1', '2006-03-28 01:00:00', '2006-04-10 01:00:00', '2006-03-28 01:00:00', 10000, 0, 10000, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', NULL, 'cash', NULL, 'テキストadditional', NULL),
('1e96680f-9239-1b6d-165e-443500e8e7f5', '', '5', NULL, 1, '2006-04-06 11:50:02', '2006-04-24 09:48:05', 'c4565fc9-30f9-719c-cb31-4437354e807d', '1', '2006-03-28 01:00:00', '2006-04-10 01:00:00', '2006-04-13 01:00:00', 20000, 0, 20000, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', NULL, 'cash', NULL, 'テキストadditional II', NULL),
('614142fb-4cae-119c-5c84-443507576492', '', '5', NULL, 1, '2006-04-06 12:19:58', '2006-04-24 09:46:43', 'c4565fc9-30f9-719c-cb31-4437354e807d', '1', '2006-03-15 01:00:00', '2006-03-25 01:00:00', '0000-00-00 00:00:00', 100000, 0, 0, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', NULL, 'cash', NULL, NULL, NULL),
('c157caa2-ce8d-c1e5-e3c4-44353c1705d0', '', '14', NULL, 1, '2006-04-06 16:08:16', '2006-04-24 09:47:06', '1', '1', '2006-03-15 01:00:00', '2006-03-31 01:00:00', '2006-03-20 01:00:00', 0, 100000, 0, 100000, 0, 0, 0, 'cab4c931-0933-3fc7-3cb3-43d06da5a855', NULL, 'cash', NULL, NULL, NULL),
('e96609c1-dd5f-c816-5d72-44353fe108db', '', '7', NULL, 1, '2006-04-06 16:18:52', '2006-04-24 09:47:14', '1', '1', '2006-03-20 01:00:00', '2006-03-31 01:00:00', '2006-03-25 01:00:00', 200000, 0, 200000, 0, 0, 0, 0, 'cab4c931-0933-3fc7-3cb3-43d06da5a855', NULL, 'cash', NULL, NULL, NULL),
('72700a25-41b1-da37-be6e-44360698fe0f', '38', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:43:09', NULL, NULL, '2006-01-15 01:00:00', '2006-01-31 00:00:00', '0000-00-00 00:00:00', 133333, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '5f558325-c0ab-0ae3-9add-43d499209320', 'cash', NULL, 'Tuition Fee for English Beginner', NULL),
('72be3443-ae27-296b-c417-4436068b4fe2', '38', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:43:15', NULL, NULL, '2006-01-15 01:00:00', '2006-03-31 00:00:00', '0000-00-00 00:00:00', 133333, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '5f558325-c0ab-0ae3-9add-43d499209320', 'cash', NULL, 'Tuition Fee for English Beginner', NULL),
('730102dc-8920-f38a-dfc8-443606bc4aa7', '38', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:43:21', '1', NULL, '2006-01-15 01:00:00', '2006-05-31 01:00:00', '0000-00-00 00:00:00', 133334, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '5f558325-c0ab-0ae3-9add-43d499209320', 'cash', NULL, 'Tuition Fee for English Beginner', NULL),
('ef579326-e311-5e01-9d1a-443c7397bfa3', '8b86cef3-93a9-da3e-55c2-443c73cb9b21', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-19 02:44:06', '1', NULL, '2006-04-12 01:00:00', '2006-04-21 01:00:00', '0000-00-00 00:00:00', 100000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', 'e5b41b60-c556-3af4-5b9f-43fec3ff84f7', 'cash', NULL, 'Tuition Fee for English Advanced - 3 ', NULL),
('ef9be8b9-b8f9-84bf-91f5-443c735167fd', '8b86cef3-93a9-da3e-55c2-443c73cb9b21', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-19 02:36:36', NULL, NULL, '2006-04-12 01:00:00', '2006-07-31 00:00:00', '0000-00-00 00:00:00', 100000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', 'e5b41b60-c556-3af4-5b9f-43fec3ff84f7', 'cash', NULL, 'Tuition Fee for English Advanced - 4 ', NULL),
('ef12ddf2-1c75-bced-40ef-443c732b88e4', '8b86cef3-93a9-da3e-55c2-443c73cb9b21', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-19 02:37:05', NULL, NULL, '2006-04-12 01:00:00', '2006-05-31 00:00:00', '0000-00-00 00:00:00', 100000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', 'e5b41b60-c556-3af4-5b9f-43fec3ff84f7', 'cash', NULL, 'Tuition Fee for English Advanced - 2 ', NULL),
('eec54078-02f2-ab5f-07a7-443c73a106f9', '8b86cef3-93a9-da3e-55c2-443c73cb9b21', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-19 02:37:13', NULL, NULL, '2006-04-12 01:00:00', '2006-04-30 00:00:00', '0000-00-00 00:00:00', 100000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', 'e5b41b60-c556-3af4-5b9f-43fec3ff84f7', 'cash', NULL, 'Tuition Fee for English Advanced - 1 ', NULL),
('2c777f3c-1d11-9079-b69d-4439675de61d', '48', '6', NULL, 1, '0000-00-00 00:00:00', '2006-04-19 02:37:44', '1', NULL, '2006-02-27 00:00:00', '2006-02-28 01:00:00', '2006-04-11 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '927e8302-0fd0-98e9-e210-4401b1eec89e', 'e5b41b60-c556-3af4-5b9f-43fec3ff84f7', 'cash', NULL, 'Entrance Fee for English Advanced', NULL),
('ede2691f-42a2-fdf2-e8bd-443c73c21924', '93758431-104d-74ae-a112-443c733cb373', '6', NULL, 1, '0000-00-00 00:00:00', '2006-04-19 02:36:42', NULL, NULL, '2006-04-12 01:00:00', '2006-04-30 00:00:00', '0000-00-00 00:00:00', 18900, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', 'e5b41b60-c556-3af4-5b9f-43fec3ff84f7', 'cash', NULL, 'Entrance Fee for English Advanced', NULL),
('cc62f1d9-01eb-4996-cea5-44396bfb24dc', '47', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:43:30', NULL, NULL, '2006-02-27 00:00:00', '2006-02-28 00:00:00', '0000-00-00 00:00:00', 500000, 0, 0, 0, 0, 0, 0, '927e8302-0fd0-98e9-e210-4401b1eec89e', 'e5b41b60-c556-3af4-5b9f-43fec3ff84f7', 'cash', NULL, 'Tuition Fee for English Advanced', NULL),
('3344eb07-1455-2fa5-94c4-444cc6ea57df', '8f0c79f2-b06b-5038-2dc7-444cc6be3434', '1', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:39:30', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-02-25 01:00:00', '2006-02-28 01:00:00', '2006-02-26 01:00:00', 78000, 0, 78000, 0, 0, 0, 0, '4c61b564-3f6b-c7d0-439a-4423a73496cb', 'b065ffe7-f2fa-6d1b-a669-4423a658cd2a', 'cash', NULL, '受講料', NULL),
('80f8db89-0143-6916-edae-4445abeabd55', '', '5', NULL, 1, '2006-04-19 03:17:21', '2006-04-24 09:47:35', 'c4565fc9-30f9-719c-cb31-4437354e807d', 'c4565fc9-30f9-719c-cb31-4437354e807d', '2006-04-06 01:00:00', '2006-04-07 01:00:00', '2006-04-06 01:00:00', 0, 0, 7000, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', NULL, 'cash', NULL, '追加テキスト', NULL),
('74485e91-2b13-dfc2-e412-4445a7642107', '3ab26703-59a2-8865-e2a3-4445a7b30ec1', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', '1', NULL, '2006-04-05 01:00:00', '2006-04-10 01:00:00', '2006-04-12 01:00:00', 4000, 0, 4000, 0, 0, 0, 0, '51b00a49-98d7-b645-7e87-4423a743edfa', NULL, 'cash', NULL, 'テキスト（入門1）', NULL),
('a0c3472b-66d8-1bf0-eb39-4445a77b0a59', '30d3a5ff-cdc6-f8c2-53e1-4445a7eacee0', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', '1', NULL, '2006-04-05 01:00:00', '2006-04-10 01:00:00', '2006-04-07 01:00:00', 4000, 0, 4000, 0, 0, 0, 0, '1689dc03-2bbc-b11d-39a8-4423a7cd14a1', NULL, 'cash', NULL, 'テキスト（入門1）', NULL),
('a6198a0e-eb46-f0eb-a104-4445a716ab65', '35b221ce-2233-c0ab-ffa8-4445a701de2f', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', '1', NULL, '2006-04-05 01:00:00', '2006-04-10 01:00:00', '2006-04-06 01:00:00', 4000, 0, 4000, 0, 0, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', NULL, 'cash', NULL, 'テキスト（入門1）', NULL),
('a747d877-d108-4a46-3473-4445a70d709e', '23a033c3-fd60-3df5-8415-4445a7b716f5', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', '1', NULL, '2006-04-05 01:00:00', '2006-04-10 01:00:00', '2006-04-07 01:00:00', 50000, 0, 50000, 0, 0, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', NULL, 'cash', NULL, '受講料', NULL),
('a878092c-fbc0-829d-ce23-4445a7ae65df', '10ad5e9a-09a2-9e32-7d18-4445a7fcf6de', '6', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:50:50', '1', NULL, '2006-04-05 01:00:00', '2006-04-10 01:00:00', '2006-04-06 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', NULL, 'cash', NULL, '入学金', NULL),
('7455b102-74bf-70ac-f26d-444c8c109f0a', 'b8f9fe86-02a3-ca27-46ca-444c37dbbb1f', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-24 09:47:01', NULL, NULL, '2006-04-24 01:00:00', '2006-04-30 00:00:00', '0000-00-00 00:00:00', 23500, 0, 0, 0, 0, 0, 0, '5a8ac999-ff73-7881-3603-444c310a021e', '80271a4d-5911-19ac-0a2c-442b3cae4296', 'cash', NULL, '受講料', NULL),
('916344f3-eb91-5ad9-9337-444cc949d1a9', '4d4f0780-6377-87e0-c86f-444cc9bfdcea', '1', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:49:59', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-01 01:00:00', '2006-03-04 01:00:00', '2006-03-03 01:00:00', 60000, 0, 60000, 0, 0, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', '6689d8a1-0d54-737c-236c-442b5dea67ea', 'cash', NULL, '受講料', NULL),
('34b80d40-3798-9ed3-db78-444cc6bef37d', '8a8e8e47-ceef-91f1-b4c7-444cc6202cb9', '5', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:39:08', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-02-25 01:00:00', '2006-02-28 01:00:00', '2006-02-27 01:00:00', 4000, 0, 4000, 0, 0, 0, 0, '4c61b564-3f6b-c7d0-439a-4423a73496cb', 'b065ffe7-f2fa-6d1b-a669-4423a658cd2a', 'cash', NULL, '英会話テキスト1', NULL),
('929ebade-21c9-7739-a07a-444cc970092b', '5348b046-3899-cc4b-ef3b-444cc9b131b7', '5', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:50:24', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-01 01:00:00', '2006-03-04 01:00:00', '2006-03-03 01:00:00', 3400, 0, 3400, 0, 0, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', '6689d8a1-0d54-737c-236c-442b5dea67ea', 'cash', NULL, 'step english 1', NULL),
('93cdd16d-e826-5ad1-4c5f-444cc95c30ee', '59f49a6b-a4e1-2df3-035f-444cc94dfa18', '6', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:50:55', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-01 01:00:00', '2006-03-04 01:00:00', '2006-03-03 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', '6689d8a1-0d54-737c-236c-442b5dea67ea', 'cash', NULL, '入学金', NULL),
('6b98d543-ec3d-3c87-e0a3-444cc96465fd', '7b984478-721d-eeb6-8f61-444cc9b1d4fc', '6', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:53:17', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-01 01:00:00', '2006-03-04 01:00:00', '2006-03-02 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', '19cd1c54-fe75-11d1-2c85-444cb18c3119', 'cash', NULL, '入学金', NULL),
('6cc4732b-e44c-5d51-ebb0-444cc9d8eb62', '8148f2f5-cc6b-d1b9-db8f-444cc9ac5654', '1', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:53:52', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-01 01:00:00', '2006-03-04 01:00:00', '2006-03-03 01:00:00', 13200, 0, 13200, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', '19cd1c54-fe75-11d1-2c85-444cb18c3119', 'cash', NULL, '受講料', NULL),
('6def9b39-9176-2dd2-563b-444cc929906b', '86a5d81c-4a7f-7539-b39a-444cc9703606', '5', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 12:54:20', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-01 01:00:00', '2006-03-04 01:00:00', '2006-03-03 01:00:00', 3200, 0, 3200, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', '19cd1c54-fe75-11d1-2c85-444cb18c3119', 'cash', NULL, 'SATテキスト', NULL),
('9a2f438d-a092-5039-3fa7-444ccbf3c23f', '4ed292ed-c054-9b85-400b-444ccb68248d', '1', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:00:16', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-02-21 01:00:00', '2006-02-24 01:00:00', '2006-02-28 01:00:00', 78000, 0, 78000, 0, 0, 0, 0, '69394b16-519e-d25f-aae0-440197f5b5e9', '182abb7a-0060-cd16-c9c8-444cb3e36b3b', 'cash', NULL, '受講料', NULL),
('9b67b205-05da-d18a-b548-444ccbe45f3a', '54f99572-4014-a55f-6110-444ccb14c251', '6', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:00:39', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-02-21 01:00:00', '2006-02-24 01:00:00', '2006-02-28 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '69394b16-519e-d25f-aae0-440197f5b5e9', '182abb7a-0060-cd16-c9c8-444cb3e36b3b', 'cash', NULL, '入学金', NULL),
('9ca056d6-6cdb-b183-9d18-444ccbf39690', '59ac1432-c128-cb44-a0e2-444ccb91b870', '5', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:01:06', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-02-21 01:00:00', '2006-02-24 01:00:00', '2006-02-28 01:00:00', 6700, 0, 6700, 0, 0, 0, 0, '69394b16-519e-d25f-aae0-440197f5b5e9', '182abb7a-0060-cd16-c9c8-444cb3e36b3b', 'cash', NULL, 'Medicalテキスト', NULL),
('a2d790e6-8b6f-13bc-f322-444ccd5ee2ab', 'd5509b66-d7cc-512a-8246-444ccd8d4f6a', '1', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:08:46', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-01 01:00:00', '2006-03-04 01:00:00', '2005-03-03 01:00:00', 56700, 0, 56700, 0, 0, 0, 0, '5a8ac999-ff73-7881-3603-444c310a021e', '360aa549-5a14-c2d6-51af-444cb68a27dd', 'cash', NULL, '受講料', NULL),
('a4009eb5-b937-d8b2-69c5-444ccde1a29a', 'db29ab50-c370-bbdf-0b1a-444ccd6ba683', '6', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:08:19', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-01 01:00:00', '2006-03-04 01:00:00', '2006-03-03 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '5a8ac999-ff73-7881-3603-444c310a021e', '360aa549-5a14-c2d6-51af-444cb68a27dd', 'cash', NULL, '入学金', NULL),
('a5220ada-b366-96ab-a105-444ccdb6b3c5', 'df950d72-baf6-fcd7-acb1-444ccd72827e', '1', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:09:11', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-03-01 01:00:00', '2006-03-03 01:00:00', '2006-03-02 01:00:00', 4300, 0, 4300, 0, 0, 0, 0, '5a8ac999-ff73-7881-3603-444c310a021e', '360aa549-5a14-c2d6-51af-444cb68a27dd', 'cash', NULL, 'TSEテキスト', NULL),
('d13af621-bd1f-29da-e778-444ccf9b13cb', '4433b7c5-8598-3d96-ed8d-444ccf439b86', '1', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:36:43', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-10 01:00:00', '2006-04-13 01:00:00', '2006-04-11 01:00:00', 132000, 0, 132000, 0, 0, 0, 0, '6403500c-f44f-3baa-461c-4401b1fc48be', '13af83e3-409d-2b7e-8e57-444cce2c89ca', 'cash', NULL, '受講料', '10003'),
('d26ff5cf-5426-3c99-d1a2-444ccfb7bcff', '49851945-4576-2a5a-836f-444ccf37febd', '5', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:18:12', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-10 01:00:00', '2006-04-13 01:00:00', '2006-04-13 01:00:00', 5000, 0, 5000, 0, 0, 0, 0, '6403500c-f44f-3baa-461c-4401b1fc48be', '13af83e3-409d-2b7e-8e57-444cce2c89ca', 'cash', NULL, 'LASTテキスト', '10003'),
('d025d8cd-59c1-5ea5-6cec-444ec63f54e5', '4f8d2a3a-20c5-c970-b166-444ccfb50800', '6', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '2006-04-10 01:00:00', '2006-04-13 00:00:00', '0000-00-00 00:00:00', 18900, 0, 0, 0, 0, 0, 0, '6403500c-f44f-3baa-461c-4401b1fc48be', '13af83e3-409d-2b7e-8e57-444cce2c89ca', 'cash', NULL, '入学金', '10003'),
('3bf93470-1b87-b2b5-6625-444ccf5a9895', 'a3ef6466-1a9c-8451-b099-444ccfe91179', '1', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:35:53', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-04 01:00:00', '2006-04-07 01:00:00', '2006-04-06 01:00:00', 132000, 0, 132000, 0, 0, 0, 0, '1689dc03-2bbc-b11d-39a8-4423a7cd14a1', '13af83e3-409d-2b7e-8e57-444cce2c89ca', 'cash', NULL, '受講料', NULL),
('3d2db4c2-bc90-da07-09d7-444ccf3859c1', 'a898b21d-77b9-cdae-6e71-444ccfd42cad', '5', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:36:14', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-04 01:00:00', '2006-04-07 01:00:00', '2006-04-05 01:00:00', 5000, 0, 5000, 0, 0, 0, 0, '1689dc03-2bbc-b11d-39a8-4423a7cd14a1', '13af83e3-409d-2b7e-8e57-444cce2c89ca', 'cash', NULL, 'LASTテキスト', NULL),
('3e5dc96e-69a7-2a45-053b-444ccfcffc3d', 'ae6c1ecc-3d75-49af-7fa5-444ccfee16e9', '6', NULL, 0, '0000-00-00 00:00:00', '2006-04-25 02:21:58', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-04 01:00:00', '2006-04-07 01:00:00', '2006-04-12 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '1689dc03-2bbc-b11d-39a8-4423a7cd14a1', '13af83e3-409d-2b7e-8e57-444cce2c89ca', 'cash', NULL, '入学金　　　\r\nこちらは入学金の処理です。', NULL),
('3d8af5ac-ddd5-fc9a-e3e5-444cd3eb418b', '1b631032-d01e-b9e7-7975-444cd34a4436', '1', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:35:06', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-04 01:00:00', '2006-04-07 01:00:00', '2006-04-12 01:00:00', 228000, 0, 228000, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', 'f2aa5053-d0ad-3e45-a683-444cd087f1a6', 'cash', NULL, '受講料', NULL),
('3ed06fdb-2c00-ca00-59fa-444cd357f94a', '251c5720-36ed-4f3e-f02f-444cd37a882b', '5', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '2006-04-04 01:00:00', '2006-04-07 00:00:00', '0000-00-00 00:00:00', 6000, 0, 0, 0, 0, 0, 0, 'b5aea340-ec05-2a8f-a5d0-4423b63a5946', 'f2aa5053-d0ad-3e45-a683-444cd087f1a6', 'cash', NULL, 'テキストGRE', NULL),
('6050ceab-5b1f-6f62-b2a6-444cd38d0a57', '1692c7d7-d842-30c2-ce05-444cd39f81b1', '6', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:33:42', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-04 01:00:00', '2006-04-07 01:00:00', '2006-04-06 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '377b02b0-918d-0ae3-7aa6-43d0a2cfdf6a', 'f2aa5053-d0ad-3e45-a683-444cd087f1a6', 'cash', NULL, '入学金', NULL),
('618192ba-f03a-3295-ed0d-444cd3278603', '201125f0-e605-36cc-5699-444cd3c22ada', '1', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:34:02', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-04 01:00:00', '2006-04-07 01:00:00', '2006-04-06 01:00:00', 228000, 0, 228000, 0, 0, 0, 0, '377b02b0-918d-0ae3-7aa6-43d0a2cfdf6a', 'f2aa5053-d0ad-3e45-a683-444cd087f1a6', 'cash', NULL, '受講料', NULL),
('62b8028e-c4c6-a51b-85e1-444cd3d9432b', '29b79f64-66ce-1324-bcb7-444cd31a291e', '5', NULL, 0, '0000-00-00 00:00:00', '2006-04-24 13:34:25', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-04 01:00:00', '2006-04-07 01:00:00', '2006-04-06 01:00:00', 6000, 0, 6000, 0, 0, 0, 0, '377b02b0-918d-0ae3-7aa6-43d0a2cfdf6a', 'f2aa5053-d0ad-3e45-a683-444cd087f1a6', 'cash', NULL, 'テキストGRE', NULL),
('941372c9-82b6-1ddc-759a-444db69e7431', 'df50899f-4acf-02c5-5880-444d834b4115', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-25 05:45:19', NULL, NULL, '2006-04-25 01:00:00', '2006-09-25 00:00:00', '0000-00-00 00:00:00', 10000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '626edd0e-ee75-1d87-cf71-444d8202b0e9', 'cash', NULL, '受講料 - 6 ', NULL),
('9396a9f0-c4df-2b53-fdbe-444db63e3566', 'df50899f-4acf-02c5-5880-444d834b4115', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-25 05:45:13', NULL, NULL, '2006-04-25 01:00:00', '2006-08-25 00:00:00', '0000-00-00 00:00:00', 10000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '626edd0e-ee75-1d87-cf71-444d8202b0e9', 'cash', NULL, '受講料 - 5 ', NULL),
('9316abce-8233-aa7d-13b2-444db6201931', 'df50899f-4acf-02c5-5880-444d834b4115', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-25 05:45:06', NULL, NULL, '2006-04-25 01:00:00', '2006-07-25 00:00:00', '0000-00-00 00:00:00', 10000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '626edd0e-ee75-1d87-cf71-444d8202b0e9', 'cash', NULL, '受講料 - 4 ', NULL),
('929781cd-3cbf-c89c-c43d-444db6db52d5', 'df50899f-4acf-02c5-5880-444d834b4115', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-25 05:45:00', NULL, NULL, '2006-04-25 01:00:00', '2006-06-25 00:00:00', '0000-00-00 00:00:00', 10000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '626edd0e-ee75-1d87-cf71-444d8202b0e9', 'cash', NULL, '受講料 - 3 ', NULL),
('92105304-a7c4-a964-8cf9-444db67ca6ee', 'df50899f-4acf-02c5-5880-444d834b4115', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-25 05:44:51', NULL, NULL, '2006-04-25 01:00:00', '2006-05-25 00:00:00', '0000-00-00 00:00:00', 10000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '626edd0e-ee75-1d87-cf71-444d8202b0e9', 'cash', NULL, '受講料 - 2 ', NULL),
('918378f2-ea02-fd58-1143-444db6bab485', 'df50899f-4acf-02c5-5880-444d834b4115', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-25 05:44:44', NULL, NULL, '2006-04-25 01:00:00', '2006-04-25 00:00:00', '0000-00-00 00:00:00', 10000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '626edd0e-ee75-1d87-cf71-444d8202b0e9', 'cash', NULL, '受講料 - 1 ', NULL),
('b514d883-6e4c-ccdb-2578-444dbabcec50', '5708bd4a-7820-6c5d-d078-444dba64b497', '5', NULL, 0, '0000-00-00 00:00:00', '2006-04-25 06:02:59', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-24 01:00:00', '2006-04-27 01:00:00', '2006-04-26 01:00:00', 4000, 0, 4000, 0, 0, 0, 0, '1689dc03-2bbc-b11d-39a8-4423a7cd14a1', '13af83e3-409d-2b7e-8e57-444cce2c89ca', 'cash', NULL, 'テキスト2', NULL),
('bb9b1256-de9d-d603-b621-444ec8a67979', '6b2cbdc7-3ac7-a170-b2ab-444ec8b2732d', '1', NULL, 1, '0000-00-00 00:00:00', '2006-04-26 01:12:02', NULL, NULL, '2006-04-06 01:00:00', '2006-04-09 00:00:00', '0000-00-00 00:00:00', 92400, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '13af83e3-409d-2b7e-8e57-444cce2c89ca', 'cash', NULL, '受講料', NULL),
('ba612b39-5107-0b5a-0e09-444ec8ffc5e6', '70c5544e-16e3-37b8-8c48-444ec8be7210', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-26 01:11:56', NULL, NULL, '2006-04-06 01:00:00', '2006-04-09 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '13af83e3-409d-2b7e-8e57-444cce2c89ca', 'cash', NULL, 'LASTテキスト', NULL),
('b91d62da-4917-b927-7498-444ec82c84df', '78042a7c-842f-7e31-97be-444ec8837a18', '6', NULL, 1, '0000-00-00 00:00:00', '2006-04-26 01:11:44', NULL, NULL, '2006-04-06 01:00:00', '2006-04-09 00:00:00', '0000-00-00 00:00:00', 18900, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '13af83e3-409d-2b7e-8e57-444cce2c89ca', 'cash', NULL, '入学金', NULL),
('b7e8b32b-c139-d708-5510-444ec8bad25f', '7d3b3243-48ae-8ead-c41d-444ec8e71573', '5', NULL, 1, '0000-00-00 00:00:00', '2006-04-26 01:11:50', NULL, NULL, '2006-04-06 01:00:00', '2006-04-09 00:00:00', '0000-00-00 00:00:00', 4000, 0, 0, 0, 0, 0, 0, '6fd3d063-6a1f-76fc-de20-43d0a1c475fc', '13af83e3-409d-2b7e-8e57-444cce2c89ca', 'cash', NULL, 'テキスト2', NULL),
('5851e00d-3f32-e533-dffe-44504429a9e9', '833da613-f3eb-7b57-5d5e-445043d06b14', '6', NULL, 0, '0000-00-00 00:00:00', '2006-05-19 00:37:58', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-25 01:00:00', '2006-04-28 01:00:00', '2006-04-27 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '18ff4d2b-6530-0b36-8931-4422345c0fc5', 'ac510728-085b-1c12-ec04-444ef479a6e5', 'cash', NULL, '入学金', NULL),
('56883e97-bf58-a3da-8e69-4450440cf42d', '92dabf20-8f04-2c11-6caa-4450439c1b2c', '1', NULL, 0, '0000-00-00 00:00:00', '2006-05-19 00:37:40', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-25 01:00:00', '2006-04-28 01:00:00', '2006-04-26 01:00:00', 45000, 0, 45000, 0, 0, 0, 0, '18ff4d2b-6530-0b36-8931-4422345c0fc5', 'ac510728-085b-1c12-ec04-444ef479a6e5', 'cash', NULL, '受講料', NULL),
('8767009b-5ed4-0ab3-2f46-444eff8dca5a', '14f15190-e8c6-dce2-0574-444efcaaf239', '6', NULL, 1, '0000-00-00 00:00:00', '2006-04-27 04:06:21', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-25 01:00:00', '2006-04-28 01:00:00', '2006-04-26 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '18ff4d2b-6530-0b36-8931-4422345c0fc5', 'ac510728-085b-1c12-ec04-444ef479a6e5', 'cash', NULL, '入学金', NULL),
('53563c00-5771-a05a-2725-44504486c7ae', '77a9f50e-5cd2-8272-fe5f-445043647a98', '6', NULL, 0, '0000-00-00 00:00:00', '2006-05-19 00:38:35', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-25 01:00:00', '2006-04-28 01:00:00', '2006-04-29 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '927e8302-0fd0-98e9-e210-4401b1eec89e', 'ac510728-085b-1c12-ec04-444ef479a6e5', 'cash', NULL, '入学金', NULL),
('51933e3e-5f14-b4d3-8ab9-4450441757c2', '8b1f1a37-ceb4-6134-e3d9-4450430d8011', '1', NULL, 0, '0000-00-00 00:00:00', '2006-05-19 00:38:12', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-25 01:00:00', '2006-04-28 01:00:00', '2006-05-27 01:00:00', 50000, 0, 50000, 0, 0, 0, 0, '927e8302-0fd0-98e9-e210-4401b1eec89e', 'ac510728-085b-1c12-ec04-444ef479a6e5', 'cash', NULL, '受講料', NULL),
('75a96773-5e1f-622f-2060-4451c7fff451', '162b5568-4823-aa02-9546-4451c6644b2a', '1', NULL, 0, '0000-00-00 00:00:00', '2006-05-19 00:39:02', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-27 01:00:00', '2006-05-25 01:00:00', '2006-05-19 01:00:00', 100000, 0, 100000, 0, 0, 0, 0, 'de6a6b20-3fb4-48d0-91bb-43d0a158a24e', 'd4551d5f-dd2e-8c23-2de8-4451c10081f9', 'cash', NULL, 'Tiution fee for SC', NULL),
('785b31a0-7ed1-8392-d09c-4451c736fae2', '2a0fd71b-f7db-b10c-19db-4451c684be2e', '5', NULL, 0, '0000-00-00 00:00:00', '2006-05-19 00:39:23', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-27 01:00:00', '2006-04-30 01:00:00', '2006-04-28 01:00:00', 15000, 0, 15000, 0, 0, 0, 0, 'de6a6b20-3fb4-48d0-91bb-43d0a158a24e', 'd4551d5f-dd2e-8c23-2de8-4451c10081f9', 'cash', NULL, 'Textbook fee for SC', NULL),
('7b6c598e-91cd-11bf-e7dc-4451c7a7b7fa', '1fc2a021-b958-cd58-44c8-4451c6f41f8f', '1', NULL, 0, '0000-00-00 00:00:00', '2006-05-19 00:39:52', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-27 01:00:00', '2006-05-25 01:00:00', '2006-05-18 01:00:00', 90000, 0, 90000, 0, 0, 0, 0, '9166dec0-c033-d207-0a5b-43d0a1033deb', 'd4551d5f-dd2e-8c23-2de8-4451c10081f9', 'cash', NULL, 'Tiution fee for SC', NULL),
('7d36d571-0320-7254-10ac-4451c76ca48e', '32e4493d-3511-a92f-1bf6-4451c686accd', '5', NULL, 0, '0000-00-00 00:00:00', '2006-04-28 07:47:17', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-04-27 01:00:00', '2006-04-30 01:00:00', '2006-04-29 01:00:00', 15000, 0, 15000, 0, 0, 0, 0, '9166dec0-c033-d207-0a5b-43d0a1033deb', 'd4551d5f-dd2e-8c23-2de8-4451c10081f9', 'cash', NULL, 'Textbook fee for SC', NULL),
('9e34d3d8-6e88-996e-e25a-4462c0e008ae', '795380f4-a117-f7b0-94d8-4462c00ea442', '5', NULL, 1, '0000-00-00 00:00:00', '2006-05-11 04:54:53', NULL, NULL, '2006-05-11 01:00:00', '2006-06-11 00:00:00', '0000-00-00 00:00:00', 4000, 0, 0, 0, 0, 0, 0, '6403500c-f44f-3baa-461c-4401b1fc48be', '499b4e23-6d0a-b841-8b30-4462bc3eed5a', 'cash', NULL, 'テキスト代', NULL),
('dad7e8de-af8b-8d66-0b12-4462c3779f42', 'cc8210bf-e7a9-0c2b-9f0a-4462c300c9ce', '5', NULL, 1, '0000-00-00 00:00:00', '2006-05-11 08:04:44', NULL, NULL, '2006-05-11 01:00:00', '2006-06-11 00:00:00', '0000-00-00 00:00:00', 4000, 0, 0, 0, 0, 0, 0, 'a9bd20a1-9499-55ff-15cb-4462c3a1032d', '499b4e23-6d0a-b841-8b30-4462bc3eed5a', 'cash', NULL, 'テキスト代', NULL),
('de9a0d88-4c97-c546-f747-4462c301b1f9', 'd58d25ad-eb1c-07fa-7aed-4462c32ac21a', '6', NULL, 1, '0000-00-00 00:00:00', '2006-05-11 08:04:53', NULL, NULL, '2006-05-11 01:00:00', '2006-05-14 00:00:00', '0000-00-00 00:00:00', 18900, 0, 0, 0, 0, 0, 0, 'a9bd20a1-9499-55ff-15cb-4462c3a1032d', '499b4e23-6d0a-b841-8b30-4462bc3eed5a', 'cash', NULL, '入学金', NULL),
('40ebf77f-c910-95c3-8dba-446304109713', 'd79a40cc-4eb1-cf76-a0f0-4462f6eac07c', '6', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '2006-05-11 01:00:00', '2006-05-14 00:00:00', '0000-00-00 00:00:00', 18900, 0, 0, 0, 0, 0, 0, '6ccb8406-2673-d8db-7829-4462f00aec29', '499b4e23-6d0a-b841-8b30-4462bc3eed5a', 'cash', NULL, '入学金', NULL),
('3f3978b8-c1ec-98e7-4bb3-4463040a64f4', 'c935b22f-84dc-3a2d-0c2e-4462f657ad7d', '1', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '2006-05-11 01:00:00', '2006-05-26 00:00:00', '0000-00-00 00:00:00', 80000, 0, 0, 0, 0, 0, 0, '6ccb8406-2673-d8db-7829-4462f00aec29', '499b4e23-6d0a-b841-8b30-4462bc3eed5a', 'cash', NULL, '受講料', NULL),
('3d91824a-cb03-caf6-c3e7-446304969e1e', 'cf3b8886-0afd-eb71-787b-4462f6288f36', '5', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '2006-05-11 01:00:00', '2006-05-26 00:00:00', '0000-00-00 00:00:00', 3000, 0, 0, 0, 0, 0, 0, '6ccb8406-2673-d8db-7829-4462f00aec29', '499b4e23-6d0a-b841-8b30-4462bc3eed5a', 'cash', NULL, 'テキスト代', NULL),
('41ae8777-6c05-db0e-c646-446304356511', '99627eee-ba61-b5c6-d53e-446304121532', '2', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 8000, 0, 0, 0, 0, 0, 0, '6ccb8406-2673-d8db-7829-4462f00aec29', '499b4e23-6d0a-b841-8b30-4462bc3eed5a', 'cash', NULL, '受講料・延長', NULL),
('c666e484-2585-5dd5-2691-44631edc1d08', 'dace76da-8e66-65fd-004d-44631efcd67d', '6', NULL, 0, '0000-00-00 00:00:00', '2006-05-11 11:25:08', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-05-11 01:00:00', '2006-05-14 01:00:00', '2006-05-12 01:00:00', 18900, 0, 18900, 0, 0, 0, 0, '740cb6c5-2449-45de-367b-44631c4d9b52', 'c55ee7b1-cbd1-d66f-d359-44631cc8c1bf', 'cash', NULL, '入学金', NULL),
('c31e6f97-1ebb-077d-a4a5-44631ee3e961', 'd2e0fdbe-2b11-dc3d-ab83-44631e886acd', '5', NULL, 0, '0000-00-00 00:00:00', '2006-05-11 11:25:20', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-05-11 01:00:00', '2006-05-14 01:00:00', '2006-05-12 01:00:00', 3000, 0, 3000, 0, 0, 0, 0, '740cb6c5-2449-45de-367b-44631c4d9b52', 'c55ee7b1-cbd1-d66f-d359-44631cc8c1bf', 'cash', NULL, 'テキスト代', NULL),
('c4c13e70-e355-1f7e-a461-44631eb817ce', 'cd0a046e-5317-fe68-ab92-44631e324142', '1', NULL, 0, '0000-00-00 00:00:00', '2006-05-11 11:25:32', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-05-11 01:00:00', '2006-05-14 01:00:00', '2006-05-12 01:00:00', 12000, 0, 12000, 0, 0, 0, 0, '740cb6c5-2449-45de-367b-44631c4d9b52', 'c55ee7b1-cbd1-d66f-d359-44631cc8c1bf', 'cash', NULL, '受講料', NULL),
('67f80b65-224f-9461-7f9b-44632271f8e4', '95b7a87b-6d9a-8633-e8f9-44631f3b600f', '2', NULL, 0, '0000-00-00 00:00:00', '2006-05-12 00:55:26', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-05-16 01:00:00', '2006-05-26 01:00:00', '2006-05-25 01:00:00', 9000, 0, 9000, 0, 0, 0, 0, '740cb6c5-2449-45de-367b-44631c4d9b52', 'c55ee7b1-cbd1-d66f-d359-44631cc8c1bf', 'cash', NULL, '受講料・延長', NULL),
('82b30d50-a488-48a5-0e46-44632290c32d', 'b98067a0-7baa-cb52-2292-446322515515', '2', NULL, 0, '0000-00-00 00:00:00', '2006-05-12 00:56:01', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-06-01 01:00:00', '2006-06-26 01:00:00', '2006-06-23 01:00:00', 10000, 0, 10000, 0, 0, 0, 0, '740cb6c5-2449-45de-367b-44631c4d9b52', 'c55ee7b1-cbd1-d66f-d359-44631cc8c1bf', 'cash', NULL, '受講料・延長', NULL),
('93c00d54-d712-944b-1923-4463e51131fa', '9124f179-34fc-64d5-b569-4463de2a4e6f', '2', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '2006-09-01 01:00:00', '2006-09-26 00:00:00', '0000-00-00 00:00:00', 10000, 0, 0, 0, 0, 0, 0, '740cb6c5-2449-45de-367b-44631c4d9b52', 'c55ee7b1-cbd1-d66f-d359-44631cc8c1bf', 'cash', NULL, '受講料・延長', NULL),
('f30ef06f-0064-5344-c342-4463e5f675bb', '24cabe61-3468-fd1f-bfb2-4463dde9ff55', '2', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '2006-07-01 01:00:00', '2006-07-26 00:00:00', '0000-00-00 00:00:00', 10000, 0, 0, 0, 0, 0, 0, '740cb6c5-2449-45de-367b-44631c4d9b52', 'c55ee7b1-cbd1-d66f-d359-44631cc8c1bf', 'cash', NULL, '受講料・延長', NULL),
('1168b8fd-0b6f-b6b0-0536-4463e58bbbe8', '56de349d-fb43-3e20-5002-4463e50c0b6a', '2', NULL, 1, '0000-00-00 00:00:00', '2006-05-17 08:56:54', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '2006-10-01 01:00:00', '2006-10-26 01:00:00', '2006-10-24 01:00:00', 10000, 0, 10000, 0, 0, 0, 0, '740cb6c5-2449-45de-367b-44631c4d9b52', 'c55ee7b1-cbd1-d66f-d359-44631cc8c1bf', 'cash', NULL, '受講料・延長', NULL),
('694ed0bc-14e8-fb9f-accb-446410bb09ff', '', '5', NULL, 0, '2006-05-12 04:35:34', '2006-05-19 00:54:28', 'c4565fc9-30f9-719c-cb31-4437354e807d', 'c4565fc9-30f9-719c-cb31-4437354e807d', '2006-05-12 01:00:00', '2006-05-12 01:00:00', '2006-05-12 01:00:00', 5000, 0, 5000, 0, 0, 0, 0, '740cb6c5-2449-45de-367b-44631c4d9b52', NULL, 'cash', NULL, 'テキスト', NULL),
('4d6c1d02-34dd-7fdf-166f-446410ebec59', '', '5', NULL, 0, '2006-05-12 04:36:57', '2006-05-12 04:59:10', 'c4565fc9-30f9-719c-cb31-4437354e807d', 'c4565fc9-30f9-719c-cb31-4437354e807d', '2006-05-12 01:00:00', '2006-05-12 01:00:00', '2006-05-12 01:00:00', 5000, 0, 5000, 0, 0, 0, 0, '4c61b564-3f6b-c7d0-439a-4423a73496cb', NULL, 'cash', NULL, '追加テキスト１', NULL),
('9a3b168f-36d6-ecbd-09d6-446ae9fef9f5', '3b9bc82b-bebf-d362-f2dc-446ae96ba6a1', '6', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '2006-05-05 01:00:00', '2006-05-08 00:00:00', '0000-00-00 00:00:00', 18900, 0, 0, 0, 0, 0, 0, '7ba1ac21-3caf-1814-7d68-44584dc4644c', 'ac309326-b206-d6a6-efe4-446ad2e60a6d', 'cash', NULL, '入学金', NULL),
('9b06af77-53f5-b41c-9568-446ae91a4190', '540c5eb1-6663-5402-ac31-446ae918bb89', '1', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 50000, 0, 0, 0, 0, 0, 0, '7ba1ac21-3caf-1814-7d68-44584dc4644c', 'ac309326-b206-d6a6-efe4-446ad2e60a6d', 'cash', NULL, '受講料', NULL),
('266dadbe-699e-fd9c-6c88-446aff42deb6', '5283db72-f6d3-edd2-3f51-446affc585d7', '1', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '2006-05-17 01:00:00', '2006-05-20 00:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, '6ccb8406-2673-d8db-7829-4462f00aec29', '91d6e870-a898-e11f-59ca-446afd0a9cf5', 'cash', NULL, '受講料', NULL),
('24b017cc-6423-570e-0483-446affe8fe86', '5a54caa9-9417-4aec-ab56-446affcc5724', '5', NULL, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, NULL, '2006-05-17 01:00:00', '2006-05-20 00:00:00', '0000-00-00 00:00:00', 6000, 0, 0, 0, 0, 0, 0, '6ccb8406-2673-d8db-7829-4462f00aec29', '91d6e870-a898-e11f-59ca-446afd0a9cf5', 'cash', NULL, 'テキスト代', NULL),
('ad7b0c90-b51c-b535-efc9-447968086539', 'b25002a2-b166-6ee8-566c-44754a96d5e4', '1', NULL, 0, '0000-00-00 00:00:00', '2006-05-28 13:06:25', '1', NULL, '2006-05-25 01:00:00', '2006-05-28 01:00:00', '2006-05-29 01:00:00', 132000, 0, 60000, 0, 500, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', '13af83e3-409d-2b7e-8e57-444cce2c89ca', '', NULL, NULL, '10004'),
('ac16812a-5eed-3d95-48ac-447968f8fcce', '901806e3-e217-4ff8-8947-44754a139cb7', '5', NULL, 0, '0000-00-00 00:00:00', '2006-05-28 13:06:25', '1', NULL, '2006-05-25 01:00:00', '1999-12-31 15:00:00', '0000-00-00 00:00:00', 5000, 0, 0, 0, 0, 0, 0, 'a50f529a-d82c-cac2-387e-4430d60c9a52', '13af83e3-409d-2b7e-8e57-444cce2c89ca', '', NULL, 'LAST', '10004');
-- 
-- Dumed data for table `sms_transaction`
-- 


/*  2006.06.4 */

ALTER TABLE `event_contacts` ADD `start_date` DATE DEFAULT '0000:00:00' NOT NULL ,
ADD `end_date` DATE DEFAULT '0000:00:00' NOT NULL ;


/*  2006.06.15 */


INSERT INTO sms_transactiontype (id, name, deleted, date_entered, date_modified, modified_user_id, created_by, keiri_category, apply_new_student, keiri_code, description) VALUES 
('8', 'refund', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 401, 'Refund'),
('9', 'refund_c', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 301, 'Refund commission');
        
/*  2006.07.03 */

UPDATE sms_transaction SET payment_type = 'transfer' WHERE payment_type = '' ;


/*  2006.07.09 */

CREATE TABLE person_contacts (
  id varchar(36) NOT NULL default '',
  contact_id varchar(36) default NULL,
  person_id varchar(36) default NULL,
  date_modified datetime default NULL,
  deleted tinyint(1) NOT NULL default '0',
  person_contact_status varchar(2) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY contact_id (contact_id),
  KEY person_id (person_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO person_contacts (id, contact_id, person_id, date_modified, deleted, person_contact_status) VALUES 
('40a58c49-7fbe-e113-1cf0', '69394b16-519e-d25f-aae0-440197f5b5e9', '432f82bb-7920-944b-562d-44b002139720', '2006-07-09 11:56:48', 0, '41'),
('40a58c49-7fbe-1cf0', '4c61b564-3f6b-c7d0-439a-4423a73496cb', '432f82bb-7920-944b-562d-44b002139720', '2006-07-09 11:56:48', 0, '41'),
('40a58c49-7fbe-e113', '69394b16-519e-d25f-aae0-440197f5b5e9', '4c61b564-3f6b-c7d0-439a-4423a73496cb', '2006-07-10 07:41:35', 1, '42'),
('22016bef-c90b-f037-a7e9-44b1eea30028', '69394b16-519e-d25f-aae0-440197f5b5e9', '7ae40a6c-c881-44e6-a8ee-44b14a552758', '2006-07-10 06:13:44', 1, '41'),
('3575e0aa-eca1-f2f7-b376-44b1f07bcbc7', '69394b16-519e-d25f-aae0-440197f5b5e9', '7ae40a6c-c881-44e6-a8ee-44b14a552758', '2006-07-10 06:14:03', 0, '41'),
('baa472ee-4252-d65d-dcb2-44b1fd70eaf1', '69394b16-519e-d25f-aae0-440197f5b5e9', '4c61b564-3f6b-c7d0-439a-4423a73496cb', '2006-07-10 07:41:35', 1, '42'),
('ed3e4959-c7d0-fb06-3571-44b204d646d4', '69394b16-519e-d25f-aae0-440197f5b5e9', '4c61b564-3f6b-c7d0-439a-4423a73496cb', '2006-07-10 07:41:35', 1, '42'),
('6e530e5c-c42e-78a3-68f1-44b20864cf4c', '69394b16-519e-d25f-aae0-440197f5b5e9', '4c61b564-3f6b-c7d0-439a-4423a73496cb', '2006-07-10 07:57:40', 0, '42');
        

/*  2006.07.11 */

ALTER TABLE `contacts` ADD `idnumber` VARCHAR( 32 ) ;
ALTER TABLE `contacts` ADD `gender` VARCHAR( 8 ) ;
ALTER TABLE `contacts` ADD `work_school` VARCHAR( 64 ) ;
ALTER TABLE `contacts` ADD `last_school_name` VARCHAR( 64 ) ;
ALTER TABLE `contacts` ADD `last_school_dept` VARCHAR( 64 ) ;
ALTER TABLE `contacts` ADD `study_aim` VARCHAR( 64 ) ;

ALTER TABLE `contacts` ADD `custom_var1` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var2` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var3` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var4` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var5` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var6` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var7` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var8` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var9` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var10` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var11` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var12` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var13` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var14` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var15` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var16` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var17` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var18` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var19` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var20` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var21` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var22` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var23` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var24` VARCHAR( 256 ) ;
ALTER TABLE `contacts` ADD `custom_var25` VARCHAR( 256 ) ;


UPDATE contacts, contactsstudent_cstm 
SET contacts.gender = contactsstudent_cstm.sex_c
WHERE contacts.id = contactsstudent_cstm.id_c ;

UPDATE contacts, contactsstudent_cstm 
SET contacts.idnumber = contactsstudent_cstm.idnumber_c
WHERE contacts.id = contactsstudent_cstm.id_c ;


UPDATE contacts, contactsstudent_cstm 
SET contacts.work_school = contactsstudent_cstm.work_school_c
WHERE contacts.id = contactsstudent_cstm.id_c ;


UPDATE contacts, contactsstudent_cstm 
SET contacts.last_school_name = contactsstudent_cstm.last_school_name_c
WHERE contacts.id = contactsstudent_cstm.id_c ;


UPDATE contacts, contactsstudent_cstm 
SET contacts.last_school_dept = contactsstudent_cstm.last_school_dept_c
WHERE contacts.id = contactsstudent_cstm.id_c ;


UPDATE contacts, contactsstudent_cstm 
SET contacts.study_aim = contactsstudent_cstm.study_aim_c
WHERE contacts.id = contactsstudent_cstm.id_c ;



UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var1 = contactsstudent_cstm.discount_rate_c
WHERE contacts.id = contactsstudent_cstm.id_c ;
	
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var2 = contactsstudent_cstm.graduation_date_c
WHERE contacts.id = contactsstudent_cstm.id_c ;	

UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var3 = contactsstudent_cstm.commission_company_c
WHERE contacts.id = contactsstudent_cstm.id_c ;	
	
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var4 = contactsstudent_cstm.toefl_score_c
WHERE contacts.id = contactsstudent_cstm.id_c ;		
	
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var5 = contactsstudent_cstm.kaptest_username_c
WHERE contacts.id = contactsstudent_cstm.id_c ;	
	
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var6 = contactsstudent_cstm.kaptest_pswd_c
WHERE contacts.id = contactsstudent_cstm.id_c ;	
	
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var7 = contactsstudent_cstm.other_score_c
WHERE contacts.id = contactsstudent_cstm.id_c ;	
	
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var8 = contactsstudent_cstm.toeic_score_c
WHERE contacts.id = contactsstudent_cstm.id_c ;	
	
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var11 = contactsstudent_cstm.last_school_c 
WHERE contacts.id = contactsstudent_cstm.id_c ;	
	
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var12 = contactsstudent_cstm.contact_medium_c
WHERE contacts.id = contactsstudent_cstm.id_c ;	

UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var13 = contactsstudent_cstm.interest_kaplan_c
WHERE contacts.id = contactsstudent_cstm.id_c ;	
	
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var14 = contactsstudent_cstm.requested_material_c
WHERE contacts.id = contactsstudent_cstm.id_c ;	
	
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var15 = contactsstudent_cstm.kaptest_material_c
WHERE contacts.id = contactsstudent_cstm.id_c ;	
	
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var16 = contactsstudent_cstm.kaptest_register_c
WHERE contacts.id = contactsstudent_cstm.id_c ;	
		
UPDATE contacts, contactsstudent_cstm 
SET contacts.custom_var17 = contactsstudent_cstm.policy_given_c
WHERE contacts.id = contactsstudent_cstm.id_c ;


/* TECHEAR */

UPDATE contacts, contactsteacher_cstm 
SET contacts.custom_var9 = contactsteacher_cstm.transportfee_c
WHERE contacts.id = contactsteacher_cstm.id_c ;

UPDATE contacts, contactsteacher_cstm 
SET contacts.custom_var10 = contactsteacher_cstm.incometax_rate_c
WHERE contacts.id = contactsteacher_cstm.id_c ;

UPDATE contacts, contactsteacher_cstm 
SET contacts.custom_var18 = contactsteacher_cstm.trasport_fee_type_c
WHERE contacts.id = contactsteacher_cstm.id_c ;


CREATE TABLE contacts_custom_vars (
  name varchar(128) NOT NULL default '',
  module varchar(64) NOT NULL default 'Contacts',
  `display_order` int(3) NOT NULL default '50',
  `display_var` tinyint(1) NOT NULL default '1'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `contacts_custom_vars`
-- 

INSERT INTO contacts_custom_vars (name, module, display_order, display_var) VALUES 
('custom_var1', 'Student', 1, 1),
('custom_var2', 'Student', 2, 1),
('custom_var3', 'Student', 3, 1),
('custom_var4', 'Student', 4, 1),
('custom_var5', 'Student', 5, 1),
('custom_var6', 'Student', 6, 1),
('custom_var7', 'Student', 7, 1),
('custom_var8', 'Student', 8, 1),
('custom_var9', 'Teacher', 9, 1),
('custom_var10', 'Teacher', 10, 1),
('custom_var11', 'Student', 11, 1),
('custom_var12', 'Student', 12, 1),
('custom_var13', 'Student', 13, 1),
('custom_var14', 'Student', 14, 1),
('custom_var15', 'Student', 15, 1),
('custom_var16', 'Student', 16, 1),
('custom_var17', 'Student', 17, 1),
('custom_var18', 'Teacher', 18, 1),
('custom_var19', 'Contacts', 19, 0),
 

/*  2006.07.18 */

ALTER TABLE `contacts` ADD `trasport_fee_type` VARCHAR( 16 ) AFTER `study_aim` ,
ADD `transportfee` INT( 11 ) AFTER `trasport_fee_type` ,
ADD `incometax_rate` FLOAT DEFAULT '0.1' NOT NULL AFTER `transportfee` ;

/* GET THE OLD contactteacher_cstm table and re-copy the values into the contacts table */

UPDATE contacts, contactsteacher_cstm 
SET contacts.transportfee = contactsteacher_cstm.transportfee_c
WHERE contacts.id = contactsteacher_cstm.id_c ;

UPDATE contacts, contactsteacher_cstm 
SET contacts.incometax_rate = contactsteacher_cstm.incometax_rate_c
WHERE contacts.id = contactsteacher_cstm.id_c ;

UPDATE contacts, contactsteacher_cstm 
SET contacts.trasport_fee_type = contactsteacher_cstm.trasport_fee_type_c
WHERE contacts.id = contactsteacher_cstm.id_c ;


/*  2006.07.23 */

ALTER TABLE `smsevent_instances` ADD `attendance_status` TINYINT( 4 ) AFTER `status` ;
ALTER TABLE `smsevent_instances` ADD `from_instance_id` VARCHAR( 36 ) AFTER `status` ,
			ADD `to_instance_id` VARCHAR( 36 ) AFTER `from_instance_id` ;

ALTER TABLE `resource` ADD `capacity` TINYINT( 4 ) ;


/*  2006.07.26 */

ALTER TABLE `contacts` ADD `nationality` VARCHAR( 16 ) DEFAULT 'JP' NOT NULL AFTER `idnumber` ;


ALTER TABLE `smsevent` ADD `custom_var31` VARCHAR( 256 ) ;
ALTER TABLE `smsevent` ADD `custom_var32` VARCHAR( 256 ) ;
ALTER TABLE `smsevent` ADD `custom_var33` VARCHAR( 256 ) ;
ALTER TABLE `smsevent` ADD `custom_var34` VARCHAR( 256 ) ;
ALTER TABLE `smsevent` ADD `custom_var35` VARCHAR( 256 ) ;
ALTER TABLE `smsevent` ADD `custom_var36` VARCHAR( 256 ) ;
ALTER TABLE `smsevent` ADD `custom_var37` VARCHAR( 256 ) ;
ALTER TABLE `smsevent` ADD `custom_var38` VARCHAR( 256 ) ;
ALTER TABLE `smsevent` ADD `custom_var39` VARCHAR( 256 ) ;
ALTER TABLE `smsevent` ADD `custom_var40` VARCHAR( 256 ) ;

UPDATE smsevent, smseventcourse_cstm 
SET smsevent.custom_var31 = smseventcourse_cstm.required_level_c
WHERE smsevent.id = smseventcourse_cstm.id_c ;	
UPDATE smsevent, smseventcourse_cstm 
SET smsevent.custom_var36 = smseventcourse_cstm.entrance_allowed_c
WHERE smsevent.id = smseventcourse_cstm.id_c ;		


/*  2006.07.31 */

ALTER TABLE `smsevent_instances` CHANGE `attendance_status` `attendance_status` TINYINT( 1 ) DEFAULT NULL;
ALTER TABLE `smsevent_instances` CHANGE `status` `status` TINYINT( 1 ) DEFAULT '0' NOT NULL;


/*  2006.08.02 */

ALTER TABLE `sms_transaction` DROP `balance` ;

CREATE TABLE sms_transactiontype (
  id varchar(36) NOT NULL default '',
  name varchar(100) NOT NULL default '',
  deleted tinyint(1) NOT NULL default '0',
  date_entered datetime NOT NULL default '0000-00-00 00:00:00',
  date_modified datetime NOT NULL default '0000-00-00 00:00:00',
  modified_user_id varchar(36) default NULL,
  created_by varchar(36) default NULL,
  keiri_category varchar(50) default NULL,
  apply_new_student tinyint(1) NOT NULL default '0',
  keiri_code int(8) default '0',
  description text,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO sms_transactiontype (id, name, deleted, date_entered, date_modified, modified_user_id, created_by, keiri_category, apply_new_student, keiri_code, description) VALUES 
('1', 'tuition', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 1, 401, 'Tuition fee for students'),
('2', 'tuition additional', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 401, 'Additional tuition fee for students'),
('5', 'textbook', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 1, 402, 'Textbook fee for students'),
('6', 'entrance', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 1, 402, 'Entrance fee for students'),
('11', 'transfer', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 405, 'Money transfer from students'),
('8', 'refund', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 401, 'Refund'),
('9', 'refund_c', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 301, 'Refund commission'),
('14', 'salary', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 652, 'Salary for teachers'),
('4', 'parttime salary', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 653, 'Part-time salary for teachers'),
('3', 'transportation', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 665, 'Transporation fee for teachers'),
('7', 'rent', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 222, 'Rent fee deposited from teachers'),
('12', 'transfer_out', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 665, 'Money transfer for teachers'),
('13', 'adjustment', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 665, 'Salary djusment value for teachers'),
('21', 'USKaplan commission', 0, '2006-02-15 00:00:00', '2006-02-15 00:00:00', NULL, NULL, 'general', 0, 403, 'Commission fee paid by US Kaplan');


/*  2006.08.03 */

UPDATE contacts SET contact_status='3' WHERE smsgroup='teacher';


/*  2006.08.13 */

ALTER TABLE `sms_fee` ADD `start_date` DATE, ADD `end_date` DATE;


/* 2006.08.20   SQL COMMANDS TO CLEAN A DATABASE FOR THE CUSTOMER: */

/* CONTACT RELATED */
TRUNCATE TABLE `contacts`; TRUNCATE TABLE `person_contacts`;
TRUNCATE TABLE `accounts`; TRUNCATE TABLE `accountsaccount_cstm`; TRUNCATE TABLE `accounts_contacts`;

/* USER RELATED */
TRUNCATE TABLE `contacts_users`; TRUNCATE TABLE `roles`; TRUNCATE TABLE `acl_roles_users`;
TRUNCATE TABLE `roles_modules`; TRUNCATE TABLE `roles_users`; TRUNCATE TABLE `users`; TRUNCATE TABLE `users_schools`;
INSERT INTO `contacts` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `assigned_user_id`, `created_by`, `salutation`, `first_name`, `last_name`, `first_name_furigana`, `last_name_furigana`, `lead_source`, `title`, `department`, `reports_to_id`, `birthdate`, `do_not_call`, `phone_home`, `phone_mobile`, `phone_work`, `phone_other`, `phone_fax`, `email1`, `email2`, `assistant`, `assistant_phone`, `email_opt_out`, `primary_address_street`, `primary_address_city`, `primary_address_state`, `primary_address_postalcode`, `primary_address_country`, `alt_address_street`, `alt_address_city`, `alt_address_state`, `alt_address_postalcode`, `alt_address_country`, `description`, `portal_name`, `portal_active`, `portal_app`, `invalid_email`, `smsgroup`, `contact_status`, `picture_filename`, `picture_file_mime_type`, `school_id`, `vacation_total`, `vacation_used`, `is_available_strict`, `send_document_home`, `send_document_work`, `company_name`, `idnumber`, `nationality`, `gender`, `work_school`, `last_school_name`, `last_school_dept`, `study_aim`, `transport_fee_type`, `transportfee`, `incometax_rate`, `custom_var1`, `custom_var2`, `custom_var3`, `custom_var4`, `custom_var5`, `custom_var6`, `custom_var7`, `custom_var8`, `custom_var9`, `custom_var10`, `custom_var11`, `custom_var12`, `custom_var13`, `custom_var14`, `custom_var15`, `custom_var16`, `custom_var17`, `custom_var18`, `custom_var19`, `custom_var20`) VALUES ('c4565fc9-30f9-719c-cb31-4437354e807d', 0, '2006-04-08 03:58:15', '2006-08-20 11:54:56', 'c4565fc9-30f9-719c-cb31-4437354e807d', NULL, '1', NULL, 'スタッフ', 'demoユーザ', NULL, NULL, NULL, '校長', '校長室', NULL, NULL, 'off', NULL, '090-123-123', NULL, NULL, NULL, 'demo@technopian.com', NULL, NULL, NULL, 'off', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, 'staff', '22', NULL, NULL, 'sssssss-1111', NULL, NULL, 0, 'on', 'on', NULL, NULL, 'JP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0.1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);


DELETE * FROM `resource` WHERE `id` NOT LIKE 'sssssss-1111';
DELETE * FROM `resource` WHERE `category` NOT LIKE 'school';

TRUNCATE TABLE `course_categories_contacts`; TRUNCATE TABLE `course_category`; TRUNCATE TABLE `category_tree`;
TRUNCATE TABLE `smsevent`; TRUNCATE TABLE `event_contacts`; TRUNCATE TABLE `event_resource`;

UPDATE `sms_groupsaleno` SET `salesno` = '10050' WHERE CONVERT( `salesno` USING utf8 ) = '10049' LIMIT 1 ;

TRUNCATE TABLE `email_templates`; TRUNCATE TABLE `product`; TRUNCATE TABLE `productcategory`;


TRUNCATE TABLE `accounts_audit`; TRUNCATE TABLE `accounts_bugs`; TRUNCATE TABLE `accounts_cases`;
TRUNCATE TABLE `accounts_opportunities`; TRUNCATE TABLE `bank_account`; TRUNCATE TABLE `bugs`;
TRUNCATE TABLE `bugs_audit`; TRUNCATE TABLE `calls`; TRUNCATE TABLE `calls_contacts`;
TRUNCATE TABLE `calls_users`; TRUNCATE TABLE `campaign_log`; TRUNCATE TABLE `campaigns`;
TRUNCATE TABLE `campaigns_audit`; TRUNCATE TABLE `cases`; TRUNCATE TABLE `cases_audit`;
TRUNCATE TABLE `cases_bugs`; TRUNCATE TABLE `contacts_audit`; TRUNCATE TABLE `contacts_bugs`;
TRUNCATE TABLE `contacts_cases`; TRUNCATE TABLE `currencies`; TRUNCATE TABLE `custom_fields`;
TRUNCATE TABLE `dashboards`; TRUNCATE TABLE `document_revisions`; TRUNCATE TABLE `documents`;
TRUNCATE TABLE `email_marketing`; TRUNCATE TABLE `email_marketing_prospect_lists`; TRUNCATE TABLE `emailman`;
TRUNCATE TABLE `emailman_sent`; TRUNCATE TABLE `emails`; TRUNCATE TABLE `emails_accounts`;
TRUNCATE TABLE `emails_cases`; TRUNCATE TABLE `emails_contacts`; TRUNCATE TABLE `emails_opportunities`;
TRUNCATE TABLE `emails_users`; TRUNCATE TABLE `feeds`; TRUNCATE TABLE `files`;
TRUNCATE TABLE `homework`; TRUNCATE TABLE `iframes`; TRUNCATE TABLE `import_maps`;
TRUNCATE TABLE `inbound_email_autoreply`; TRUNCATE TABLE `leads`; TRUNCATE TABLE `leads_audit`;
TRUNCATE TABLE `leads_cstm`; TRUNCATE TABLE `meetings`; TRUNCATE TABLE `meetings_contacts`;
TRUNCATE TABLE `meetings_users`; TRUNCATE TABLE `notes`; TRUNCATE TABLE `opportunities`;
TRUNCATE TABLE `opportunities_audit`; TRUNCATE TABLE `opportunities_contacts`; TRUNCATE TABLE `project`;
TRUNCATE TABLE `project_relation`; TRUNCATE TABLE `project_task`; TRUNCATE TABLE `project_task_audit`;
TRUNCATE TABLE `prospect_list_campaigns`; TRUNCATE TABLE `prospect_lists`; TRUNCATE TABLE `prospect_lists_prospects`;
TRUNCATE TABLE `prospects`; TRUNCATE TABLE `prospects_cstm`; TRUNCATE TABLE `releases`;
TRUNCATE TABLE `schedulers`; TRUNCATE TABLE `schedulers_times`; TRUNCATE TABLE `session`;
TRUNCATE TABLE `sms_document`; TRUNCATE TABLE `sms_event_instance`; TRUNCATE TABLE `sms_fee`;
TRUNCATE TABLE `sms_recurrence`; TRUNCATE TABLE `sms_salesgroup`; TRUNCATE TABLE `sms_syllabus`;
TRUNCATE TABLE `sms_transaction`; TRUNCATE TABLE `smsevent_instances`; TRUNCATE TABLE `smsgroups`;
TRUNCATE TABLE `smsgroups_module_action`; TRUNCATE TABLE `tasks`; TRUNCATE TABLE `tracker`;
TRUNCATE TABLE `users_feeds`; TRUNCATE TABLE `users_last_import`;


/*  2006.08.23 */
ALTER TABLE `sms_fee` ADD `product_id` VARCHAR( 36 ) ;
ALTER TABLE `sms_fee` ADD `product_count` INT( 8 ) DEFAULT '0' NOT NULL ;
ALTER TABLE `product` CHANGE `discount_price` `discount_price` DECIMAL( 20, 2 ) DEFAULT '0' NOT NULL ;
ALTER TABLE `product` CHANGE `list_price` `list_price` DECIMAL( 20, 2 ) DEFAULT '0' NOT NULL ;


/*  2006.08.24 */
ALTER TABLE `product` CHANGE `quantity` `quantity` INT( 5 ) DEFAULT '0' NOT NULL ;

CREATE TABLE memo (
  id varchar(36) NOT NULL default '',
  date_entered datetime NOT NULL default '0000-00-00 00:00:00',
  date_modified datetime NOT NULL default '0000-00-00 00:00:00',
  modified_user_id varchar(36) default NULL,
  created_by varchar(36) default NULL,
  name varchar(255) default NULL,
  filename varchar(255) default NULL,
  file_mime_type varchar(100) default NULL,
  parent_type varchar(25) default NULL,
  parent_id varchar(36) default NULL,
  contact_id varchar(36) default NULL,
  portal_flag tinyint(1) NOT NULL default '0',
  description text,
  description2 text,
  deleted tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY idx_note_name (name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


/*  2006.08.30 */
ALTER TABLE `contacts` ADD `commission_company_id` VARCHAR( 36 ) AFTER `reports_to_id` ;


/*  2006.09.04 */
ALTER TABLE `contacts` ADD `lead_source_description` VARCHAR( 64 ) AFTER `lead_source` ;
ALTER TABLE `contacts` ADD `leave_date` DATE DEFAULT '0000-00-00' NOT NULL AFTER `incometax_rate` ,
ADD `enroll_date` DATE DEFAULT '0000-00-00' NOT NULL AFTER `leave_date` ,
ADD `grade` VARCHAR( 10 ) NOT NULL AFTER `enroll_date` ;

/*  2006.09.22 */
/* delete the event_contacts relations for which contacts are already deleted or NON existent */
SELECT event_contacts . * , contacts.last_name, contacts.deleted AS con_del
FROM event_contacts
LEFT JOIN contacts ON event_contacts.contact_id = contacts.id
AND contacts.deleted =1
WHERE event_contacts.deleted =0

DELETE event_contacts.* FROM event_contacts
LEFT JOIN contacts ON event_contacts.contact_id = contacts.id
AND contacts.deleted =1
WHERE event_contacts.deleted =0

DELETE smsevent.* FROM smsevent
WHERE category LIKE 'vacation'


/*  2006.09.26 */
/* delete the start-end dates of the fees which are not set as gessha type (actually they were set wrongly)  */
UPDATE sms_fee SET start_date = NULL, end_date=NULL WHERE `unit_type` NOT LIKE '1';


/*  2006.10.15  */
/* addition of fields to the DB  */
/* CUSTOM FIELDS
	Files updated:  
		cache/dynamic_fields/School/fields.php 
		custom/modules/School/language/*.lang.php  => last two fields are not shown on the display, so may be ignored 
	DB:  */
INSERT INTO `fields_meta_data` 
	(`id`, `name`, `label`, `custom_module`, `data_type`, `max_size`, `required_option`, `default_value`, `date_modified`, `deleted`, `audited`, `ext1`, `ext2`, `ext3`) 
	VALUES 
		('Schoollast_student_id_c', 'last_student_id_c', 'last_student_id_c', 'School', 'varchar', 10, 'optional', NULL, '2006-10-13 10:58:23', 0, 0, 'moduleList', '0', '0'),
	('Schoollast_invoice_id_c', 'last_invoice_id_c', 'last_invoice_id_c', 'School', 'varchar', 10, 'optional', NULL, '2006-10-13 11:19:54', 0, 0, 'moduleList', '0', '0');

ALTER TABLE `resourceschool_cstm` ADD `last_student_id_c` VARCHAR( 10 ) ;
ALTER TABLE `resourceschool_cstm` ADD `last_invoice_id_c` VARCHAR( 10 ) ;
/*		then for each branch last student and invoice ID's should be set to proper values 

	Contacts additional fields: 
		Student:
*/
ALTER TABLE `contacts` ADD `grade_entrance_date` DATE AFTER `grade` ,
ADD `application_date` DATE AFTER `grade_entrance_date` ;
/*		Teacher:
*/
ALTER TABLE `contacts` ADD `major` VARCHAR(32) AFTER `is_available_strict`  ;

ALTER TABLE `contacts` CHANGE `leave_date` `leave_date` DATE;
ALTER TABLE `contacts` CHANGE `enroll_date` `enroll_date` DATE;

/* update the school branch numbers and idnumber & invoice number for each branch manually  */


/*  2006.11.01  */

/*	adding the SMSHelp module to the system  
	1. run the following action&module pair on the top page 
		index.php?module=ACL&action=install_actions 
	2. update tabs in the system
		make SMSReports and SMSHelp visible to the users  => Admin->Edit Tabs
*/

/*  2006.11.21  */
ALTER TABLE `resource` ADD `shortname` VARCHAR( 12 ) AFTER `name` ;
ALTER TABLE `smsevent` ADD `shortname` VARCHAR( 12 ) AFTER `name` ;

UPDATE contacts SET enroll_date = NULL WHERE enroll_date = '0000-00-00';

/*  2006.11.23  */
ALTER TABLE `sms_transactiontype` ADD `show_in_edit_fee` TINYINT( 1 ) DEFAULT '1' NOT NULL AFTER `apply_new_student` ;
UPDATE `sms_transactiontype` SET `show_in_edit_fee` = '0' WHERE CONVERT( `id` USING utf8 ) = '9' ;
UPDATE `sms_transactiontype` SET `show_in_edit_fee` = '0' WHERE CONVERT( `id` USING utf8 ) = '8' ;
UPDATE `sms_transactiontype` SET `show_in_edit_fee` = '0' WHERE CONVERT( `id` USING utf8 ) = '13' ;

DELETE FROM `sms_transactiontype` WHERE CONVERT( `id` USING utf8 ) = '7' ;  /* delete transactiontype for rent  */

/*   RUN THE FOLLOWING in order to give edit right to the non-admin users for scheduling
	index.php?module=ACL&action=install_actions  
*/


/*  2006.11.26  */
UPDATE sms_fee SET UNIT_NO = 60 WHERE type_id = '4';


/*  2006.12.17  */
UPDATE contacts SET grade_entrance_date = NULL WHERE grade_entrance_date = '0000-00-00';

/*  2006.12.19  */
ALTER TABLE `sms_fee` CHANGE `due_to` `due_to` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' ;

/* 2006.12.27 */
/* QUERIES WITH smsevent_instance and sms_event_instances  */
/*		HENKIN */
SELECT smsevent_instances.*, count(smsevent_instances.event_id) as number_of_occurrences, 
								CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
								contacts.id as contact_id, smsevent.id as event_id, 
								smsevent.name as course_name, contacts.smsgroup, sms_fee.id as fee_id, 
								IFNULL(sms_fee.discount_price, sms_fee.basic_fee) as sell_amount,
								sms_fee.name as description, smsevent_instances.from_datetime as t_date,
								event_contacts.start_date, event_contacts.end_date
							FROM smsevent_instances 
							LEFT JOIN smsevent ON smsevent_instances.event_id = smsevent.id AND smsevent.deleted=0
							LEFT JOIN event_contacts ON event_contacts.event_id = smsevent.id AND event_contacts.deleted=0
							JOIN sms_fee ON sms_fee.event_id = smsevent_instances.event_id AND sms_fee.deleted=0 
							LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id
							CROSS JOIN contacts ON sms_fee.contact_id = contacts.id AND contacts.deleted=0 AND event_contacts.contact_id=contacts.id 
						 WHERE  1=1  AND smsevent.school_id = 'sssssss-1111'  AND ( smsevent_instances.from_datetime <= CONCAT(sms_fee.end_date,' 23:59:59') 
									AND smsevent_instances.from_datetime >= CONCAT(sms_fee.start_date,' 00:00:00') 
								 ) AND smsevent_instances.status !='512' AND smsevent_instances.from_datetime <= '2006-10-15 23:59:59'  AND sms_transactiontype.keiri_code=401 AND sms_fee.applied_contact=1  AND contacts.smsgroup = 'student' AND smsevent.id = smsevent_instances.event_id AND contacts.id = smsevent_instances.target_id  GROUP BY smsevent_instances.event_id, contacts.id, sms_fee.id  ORDER BY sms_fee.sell_date desc ;
								 
/* 		TEACHER FEES  */
/*			teacher part-time fees */
SELECT smsevent_instances.*, 
								( UNIX_TIMESTAMP(smsevent_instances.to_datetime) - UNIX_TIMESTAMP(smsevent_instances.from_datetime) )  / 3600 as number_of_occurrences, 
								CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
								smsevent_instances.instance_id as instance_id, 
								contacts.id as contact_id, smsevent.id as course_id, 
								smsevent.name as course_name, contacts.smsgroup, sms_fee.id as fee_id, 
								IFNULL(sms_fee.discount_price, sms_fee.basic_fee) * 60 / sms_fee.unit_no as sell_amount,
								IFNULL(sms_fee.discount_price, sms_fee.basic_fee) as past_sales,
								CASE sms_fee.unit_no 
									WHEN 60 THEN ''
									ELSE CONCAT('/', sms_fee.unit_no, 'min')
								END as teacher_hourly_salary,
								sms_fee.name as description, 
								SUBSTRING(date_add(smsevent_instances.from_datetime, INTERVAL 540 MINUTE), 1, 10) as sell_date, 
								'0000-00-00 00:00:00' as t_date
								FROM smsevent_instances 
								JOIN contacts ON smsevent_instances.target_id = contacts.id AND contacts.deleted=0
								JOIN smsevent ON smsevent_instances.event_id = smsevent.id
								JOIN sms_fee ON smsevent.id = sms_fee.event_id AND sms_fee.contact_id = contacts.id AND sms_fee.deleted=0 
								JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id 
								JOIN event_contacts ON event_contacts.event_id = smsevent.id AND event_contacts.contact_id=contacts.id AND event_contacts.deleted=0  WHERE  1=1  AND smsevent.school_id = 'sssssss-1111'  AND smsevent_instances.attendance_status IS NOT NULL AND (smsevent_instances.status & 512) = 0  AND smsevent_instances.from_datetime < '2006-11-01 00:00:00 ' AND smsevent_instances.from_datetime >= '2006-10-01 00:00:00' 	AND sms_transactiontype.id = '4' AND event_contacts.event_contact_status='4' 	AND smsevent_instances.from_datetime <= CONCAT(sms_fee.end_date,' 23:59:59') 
												AND smsevent_instances.from_datetime >= CONCAT(sms_fee.start_date,' 00:00:00')     ORDER BY contacts.last_name desc;
/*			teacher transporations fees */
SELECT
								sms_transaction.*, contacts.id as contact_id, 
								CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
								contacts.smsgroup, sms_transaction.t_date, 
								sms_transaction.fee_id as fee_id
								FROM sms_transaction, contacts, sms_transactiontype
								WHERE sms_transaction.contact_id = contacts.id  AND
								sms_transaction.event_id IS NULL AND 
								sms_transactiontype.id = sms_transaction.type_id AND
								sms_transactiontype.keiri_category = 'teacher' 
								 AND 1=1  AND contacts.school_id = 'sssssss-1111'  AND sms_transaction.deleted=0 AND contacts.deleted=0  AND sms_transaction.sell_date < '2006-11-01 00:00:00 ' AND sms_transaction.sell_date >= '2006-10-01 00:00:00' ;
								 
/*			teacher other fees */
SELECT 		count(DISTINCT SUBSTRING(date_add(smsevent_instances.from_datetime, INTERVAL 540 MINUTE), 1, 10)) as number_of_occurrences, 
								CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
								contacts.id as contact_id, contacts.smsgroup, 
								'' as course_name, '' as in_amount, '' as actual_in_amount, '' as actual_out_amount, 
								IF(contacts.transport_fee_type='daily', contacts.transportfee, 0) as sell_amount,
								SUBSTRING(date_add(smsevent_instances.from_datetime, INTERVAL 540 MINUTE), 1, 7) as sell_date, 
								'0000-00-00 00:00:00' as t_date
								FROM smsevent_instances 
								JOIN contacts ON smsevent_instances.target_id = contacts.id AND contacts.deleted=0
								JOIN smsevent ON smsevent_instances.event_id = smsevent.id
								JOIN sms_fee ON smsevent.id = sms_fee.event_id AND sms_fee.contact_id = contacts.id AND sms_fee.deleted=0 
								JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id 
								JOIN event_contacts ON event_contacts.event_id = smsevent.id AND event_contacts.contact_id=contacts.id AND event_contacts.deleted=0  WHERE  1=1  AND smsevent.school_id = 'sssssss-1111'  AND smsevent_instances.attendance_status IS NOT NULL AND (smsevent_instances.status & 512) = 0  AND smsevent_instances.from_datetime < '2006-11-01 00:00:00 ' AND smsevent_instances.from_datetime >= '2006-10-01 00:00:00' 	AND sms_transactiontype.id = '4' AND event_contacts.event_contact_status='4'  GROUP BY contacts.id  ORDER BY contacts.last_name desc ;
								
/* 		SALES */
/*			course related tuition */
SELECT 	count(smsevent_instances.event_id) as number_of_occurrences, 
								CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
								contacts.id as contact_id, smsevent.id as event_id, 
								smsevent.name as course_name, contacts.smsgroup, sms_fee.id as fee_id, 
								IFNULL(sms_fee.discount_price, sms_fee.basic_fee) as sell_amount,
								sms_fee.name as description, smsevent_instances.from_datetime as t_date,
								event_contacts.start_date, event_contacts.end_date,
								sms_fee.sell_date
							FROM smsevent_instances 
							LEFT JOIN smsevent ON smsevent_instances.event_id = smsevent.id AND smsevent.deleted=0 
							LEFT JOIN event_contacts ON event_contacts.event_id = smsevent.id AND event_contacts.deleted=0
							CROSS JOIN contacts ON contacts.deleted=0 AND event_contacts.contact_id=contacts.id 
							CROSS JOIN sms_fee ON sms_fee.contact_id = contacts.id AND sms_fee.event_id = smsevent_instances.event_id AND sms_fee.deleted=0 
							LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id WHERE  1=1  AND smsevent.school_id = 'sssssss-1111' 	AND smsevent_instances.from_datetime <= CONCAT(event_contacts.end_date,' 23:59:59') 
							AND smsevent_instances.from_datetime >= CONCAT(event_contacts.start_date,' 00:00:00')  AND (smsevent_instances.instance_contact_status & 128) = 0   AND smsevent_instances.from_datetime > '2006-09-30 15:00:00'  AND sms_transactiontype.keiri_code=401 AND sms_fee.applied_contact=1  AND contacts.smsgroup = 'student' AND smsevent.id = smsevent_instances.event_id AND contacts.id = smsevent_instances.target_id  	AND (sms_fee.type_id NOT IN ('1', '2') OR (
								smsevent_instances.from_datetime <= CONCAT(sms_fee.end_date,' 23:59:59') 
								AND smsevent_instances.from_datetime >= CONCAT(sms_fee.start_date,' 00:00:00') ) )  GROUP BY smsevent_instances.event_id, contacts.id, sms_fee.id  ORDER BY sms_fee.sell_date desc ;

/*			course related other */
SELECT
								contacts.id as contact_id, smsevent.id as course_id, 
								CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
								smsevent.name as course_name, contacts.smsgroup, 
								sms_fee.id as fee_id, sms_fee.name as description, IFNULL(sms_fee.discount_price, sms_fee.basic_fee) as sell_amount,
								event_contacts.start_date, event_contacts.end_date, smsevent_instances.from_datetime as t_date,
								sms_fee.sell_date
							FROM smsevent_instances 
							LEFT JOIN smsevent ON smsevent_instances.event_id = smsevent.id AND smsevent.deleted=0
							LEFT JOIN event_contacts ON event_contacts.event_id = smsevent.id AND event_contacts.deleted=0
							JOIN sms_fee ON sms_fee.event_id = smsevent_instances.event_id AND sms_fee.deleted=0 
							LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id 
							CROSS JOIN contacts ON event_contacts.contact_id=contacts.id AND sms_fee.contact_id = contacts.id AND contacts.deleted=0  AND sms_transactiontype.keiri_code!=401 AND sms_fee.applied_contact=1  AND contacts.smsgroup = 'student' AND smsevent.id = smsevent_instances.event_id AND contacts.id = smsevent_instances.target_id  AND smsevent.school_id = 'sssssss-1111'  AND event_contacts.start_date < '2006-09-30 15:00:00 ' AND event_contacts.start_date >= '2006-08-31 15:00:00' ;

/*			non-course related (person related only) */
SELECT
								sms_transaction.payment_type, sms_fee.name as description, sms_transaction.t_date, sms_transaction.id, 
								contacts.id as contact_id, CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
								contacts.smsgroup, sms_transaction.sell_date as start_date, sms_transaction.sell_date as end_date, 
								sms_transaction.fee_id as fee_id, sms_transaction.in_amount as sell_amount,
								sms_fee.sell_date
								FROM sms_transaction, contacts, sms_fee 
								WHERE sms_transaction.contact_id = contacts.id
									AND sms_transaction.event_id IS NULL  AND sms_transaction.deleted=0
									AND sms_transaction.fee_id = sms_fee.id AND sms_fee.deleted=0								
								 AND contacts.smsgroup = 'student'  AND contacts.school_id = 'sssssss-1111'  AND sms_transaction.deleted=0 AND contacts.deleted=0  AND sms_transaction.sell_date < '2006-09-30 15:00:00 ' AND sms_transaction.sell_date >= '2006-08-31 15:00:00' ;

/* 		ATTENDANCE */

/*			direct input attendance */
SELECT 	smsevent.id as event_id, 
								case 
									when resource.name IS NULL then
										smsevent.name 
									else 
										concat(smsevent.name,' (', resource.name,')') 	
								end  as course_name,
								smsevent.parent_id as category_id, course_category.name as category_name,
								CONCAT(SUBSTRING(date_add(sms_event_instance.from_datetime, INTERVAL 540 MINUTE), 12, 5), 
									'～', SUBSTRING(date_add(sms_event_instance.to_datetime, INTERVAL 540 MINUTE), 12, 5) , '  ') as class_period,
								CONCAT(person.last_name, ' ', IFNULL(person.first_name, '') ) as person_name, person.id as person_id,
								smsevent_instances.attendance_status, smsevent_instances.category as person_category,
								sms_event_instance.id as instance_id, sms_event_instance.status as instance_status,
								smsevent_instances.instance_contact_status
							FROM smsevent
							JOIN sms_event_instance ON smsevent.id = sms_event_instance.parent_id 
							LEFT JOIN course_category ON smsevent.parent_id = course_category.id AND course_category.deleted=0  
							LEFT JOIN category_tree ON course_category.id = category_tree.self_id 
							LEFT JOIN smsevent_instances ON sms_event_instance.id = smsevent_instances.instance_id 
								AND (smsevent_instances.category='student' OR smsevent_instances.category='teacher')
							LEFT JOIN contacts person ON smsevent_instances.target_id = person.id 
							LEFT JOIN smsevent_instances det on sms_event_instance.id=det.instance_id and det.category='subject'
							LEFT JOIN resource on det.target_id=resource.id   	WHERE smsevent.category='course' AND smsevent.deleted=0 
								AND 	date_add(sms_event_instance.from_datetime, INTERVAL 540 MINUTE) 
											>= '2006-12-27 00:00:00'
										AND date_add(sms_event_instance.from_datetime, INTERVAL 540 MINUTE) 
											<= '2006-12-27 23:59:59' AND (sms_event_instance.status & 1088 ) = 0 AND (smsevent_instances.status & 1232) = 0  AND smsevent.parent_id = course_category.id  AND smsevent.school_id = 'sssssss-1111'  AND category_tree.node_id IN ( '0', '1332', '1352', '1353', '1355', '1354', '1365', '1366', '1367', '1368', '1369', '1371', '1372', '1394', '1396', '1400', '1401', '1402', '1399', '1407', '1356', '1413', '1435', '1425', '1442', '1446', '1456', '1450', '1468', '1466', '1539', '1540', '1491', '1492', '1462', '1472', '1470', '1471', '1481', '1494', '1563', '1548', '1550', '1549', '1551', '1504', '1505', '1506', '1509', '1514', '1517', '1510', '1518', '1570' )  ORDER BY course_category.name DESC, smsevent.name DESC, sms_event_instance.from_datetime ;
/*			old attendance create class instances */
SELECT sms_event_instance.id as instance_id, 
						date_add(sms_event_instance.from_datetime, INTERVAL 540 MINUTE) as from_time, 
						date_add(sms_event_instance.to_datetime, INTERVAL 540 MINUTE) as to_time
					FROM sms_event_instance
					INNER JOIN smsevent ON smsevent.id = sms_event_instance.parent_id 
					WHERE smsevent.id ='8daf8da3-a9af-bda8-7da2-458713ff968a' 
						AND sms_event_instance.deleted=0 AND smsevent.deleted=0 
						AND date_add(sms_event_instance.from_datetime, INTERVAL 540 MINUTE)  >= '2006-10-01'
						AND date_add(sms_event_instance.to_datetime, INTERVAL 540 MINUTE)  <= '2006-12-31' AND (sms_event_instance.status & 1088 ) = 0 ORDER BY from_time ;

/*			old attendance create person attendance values */
SELECT contacts.id as attendance_id, CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
								CONCAT(contacts.last_name_furigana, ' ', IFNULL(contacts.first_name_furigana, '') ) as contact_name_furigana, 
								contacts.smsgroup as smsgroup, smsevent_instances.instance_id,
								smsevent_instances.from_datetime as from_time, smsevent_instances.to_datetime as to_time,
								smsevent_instances.attendance_status, smsevent_instances.status, smsevent_instances.instance_contact_status
								FROM contacts
								INNER JOIN smsevent_instances ON contacts.id=smsevent_instances.target_id 
								INNER JOIN smsevent ON smsevent.id = smsevent_instances.event_id 
								WHERE smsevent.id ='8daf8da3-a9af-bda8-7da2-458713ff968a' AND smsevent_instances.status IS NOT NULL 
									AND date_add(smsevent_instances.from_datetime, INTERVAL 540 MINUTE)  >= '2006-10-01'
									AND date_add(smsevent_instances.to_datetime, INTERVAL 540 MINUTE)  <= '2006-12-31'
									AND contacts.deleted=0 AND smsevent.deleted=0 AND (smsevent_instances.status & 1232) = 0 ;
									

/*			old attendance create class instances */


INSERT INTO `sms_transactiontype` 	(	`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) 
VALUES ('20', 'tuition_account', 0, '2007-02-08 00:00:00', '2007-02-08 00:00:00', NULL, NULL, 'account', 1, 1, 701, 'Tuition fee for accounts');

INSERT INTO `sms_transactiontype` (	`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) 
VALUES ('21', 'tuition_add_account', 0, '2007-02-08 00:00:00', '2007-02-08 00:00:00', NULL, NULL, 'account', 0, 1, 701, 'Additional tuition fee for accounts');

INSERT INTO `sms_transactiontype` (	`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) 
VALUES ('22', 'text_account', 0, '2007-02-08 00:00:00', '2007-02-08 00:00:00', NULL, NULL, 'account', 1, 1, 702, 'Textbook fee for accounts');

INSERT INTO `sms_transactiontype` (	`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) 
VALUES ('23', 'entrance_account', 0, '2007-02-08 00:00:00', '2007-02-08 00:00:00', NULL, NULL, 'account', 1, 1, 702, 'Entrance fee for accounts');

ALTER TABLE `sms_fee` ADD `account_id` VARCHAR( 36 ) AFTER `contact_id` ;

// query for plusculture to check with ID number
SELECT id, last_name, first_name, smsgroup, contact_status, idnumber, email1, school_id, contacts. * 
FROM `contacts` WHERE `deleted` =0 AND `idnumber` IS NOT NULL AND smsgroup = 'student' ;


INSERT INTO `sms_transactiontype` 
	(`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES 
	('31', 'percentage salary', 0, '2007-03-29 00:00:00', '2007-03-29 00:00:00', NULL, NULL, 'teacher', 0, 1, 654, 'Percentage-based salary for teachers');
        
UPDATE contacts SET incometax_rate = 0 WHERE `smsgroup` LIKE 'teacher' ;  // set the teacher tax rates to zero for cultureplus

INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES ('35', 'tuition taiken', 0, '2007-02-08 00:00:00', '2007-02-08 00:00:00', NULL, NULL, 'student', 1, 1, 401, 'Taiken fee for students');
        

INSERT INTO `inbound_email` VALUES ('d1f90bbb-d603-cb2f-da44-441fc60da065', 0, '2006-03-21 09:26:51', '2007-05-02 08:26:30', '1', '1', 'TechnoSMS', 'Active', 'mail.technopian.com', 'technosms@technopian.com', 'yeVdrSuObvqOah4nFSEwMA==', 110, '::::::pop3::::notls::', 'INBOX', 0, NULL, NULL, 'YTo1OntzOjk6ImZyb21fbmFtZSI7czowOiIiO3M6OToiZnJvbV9hZGRyIjtzOjA6IiI7czoxMDoib25seV9zaW5jZSI7YjoxO3M6MTM6ImZpbHRlcl9kb21haW4iO3M6MDoiIjtzOjE1OiJvbmx5X3NpbmNlX2xhc3QiO3M6NDY6IldlZCwgMiBNYXkgMjAwNyAxNzoyNjozMCArMDkwMCAok4yLniAolVePgI6eKSkiO30=', '9f94ada3-2558-ec7d-848f-45e5ace43091');
        



ALTER TABLE `contacts` CHANGE `id_card_field1` `id_card_field1` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL ;
ALTER TABLE `contacts` CHANGE `id_card_field4` `id_card_field4` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES 
('17', 'student other', 0, '2007-04-28 00:00:00', '2007-04-28 00:00:00', NULL, NULL, 'student', 1, 1, 402, 'Other student fees');


ALTER TABLE `sms_fee` ADD `jikyu_rate` VARCHAR( 20 ) ;

ALTER TABLE `sms_salesgroup` ADD `hakkoubi` DATE NOT NULL ;
ALTER TABLE `sms_salesgroup` ADD `seikyusaki` TEXT;

ALTER TABLE `contacts` ADD `renmei_label` TEXT AFTER `id_card_field4` ;

ALTER TABLE `emails` CHANGE `description_html` `description_html` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;


INSERT INTO `sms_transactiontype` (`id`, `name`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `keiri_category`, `apply_new_student`, `show_in_edit_fee`, `keiri_code`, `description`) VALUES 
	('27', 'tuition student kaisu', 0, '2007-09-15 00:00:00', '2007-09-15 00:00:00', NULL, NULL, 'student', 0, 1, 407, 'Tuition fees with even distribution');

ALTER TABLE `sms_fee` ADD `school_id` VARCHAR ( 36 ) AFTER `id` ; 

UPDATE sms_fee, contacts SET sms_fee.school_id = contacts.school_id WHERE sms_fee.contact_id = contacts.id ;
UPDATE sms_fee, smsevent SET sms_fee.school_id = smsevent.school_id WHERE sms_fee.event_id = smsevent.id ;
UPDATE sms_fee, contacts, users SET sms_fee.school_id = contacts.school_id 
WHERE sms_fee.created_by = users.id AND users.contact_id = contacts.id AND sms_fee.contact_id IS NULL AND sms_fee.event_id IS NULL ;

SELECT sms_fee.id, sms_fee.school_id, smsevent.school_id AS course_school_id, smsevent.deleted, contacts.school_id AS contact_school_id, contacts.deleted
FROM sms_fee
LEFT JOIN smsevent on sms_fee.event_id = smsevent.id AND smsevent.deleted=0
LEFT JOIN contacts on sms_fee.contact_id = contacts.id AND contacts.deleted=0 ;
SELECT sms_fee.id, sms_fee.school_id, sms_fee.contacts_id, sms_fee.event_id, contacts.school_id AS contact_school_id, contacts.deleted
FROM sms_fee, contacts, users 
WHERE sms_fee.created_by = users.id AND users.contact_id = contacts.id AND sms_fee.contact_id IS NULL AND sms_fee.event_id IS NULL ;

-- 2007-10-14  --- metehan TO GET PRODUCTS AND CATEGORIES FROM DB
SELECT product.name, product.id, product.list_price, product.cost_price, product.description, product.internal_product_id, productcategory.name FROM product, productcategory  WHERE product.productcategory_id = productcategory.id ;

UPDATE sms_fee SET sell_date = '2007-08-31 20:00' WHERE `sell_date` = '2007-08-31 15:00' ;  // temp
UPDATE sms_fee SET sell_date = '2007-09-01 01:00' WHERE `sell_date` = '2007-08-31 20:00' ;  // temp
UPDATE sms_fee SET sell_date = '2007-09-01 01:00' WHERE `sell_date` = '2007-08-31 15:00' ;
UPDATE sms_transaction SET sell_date = '2007-09-01 01:00' WHERE `sell_date` = '2007-08-31 15:00' ;


-- 2007-11-11  --- metehan  anaylze a quiry with explain 
EXPLAIN QUERY 


-- 2007-11-19  --- inbound_email table contents 
INSERT INTO `inbound_email` VALUES ('d1f90bbb-d603-cb2f-da44-441fc60da065', 0, '2006-03-21 09:26:51', '2007-06-05 22:45:25', '1', '1', 'TechnoSMS', 'Active', 'mail.technopian.com', 'technosms@technopian.com', 'yeVdrSuObvqOah4nFSEwMA==', 110, '::::::pop3::::notls::', 'INBOX', 0, 'bounce', NULL, 'YTo0OntzOjk6ImZyb21fbmFtZSI7czoxMToiVGVjaG5vTG9jYWwiO3M6OToiZnJvbV9hZGRyIjtzOjI2OiJ0ZWNobm9sb2NhbEB0ZWNobm9waWFuLmNvbSI7czoxMDoib25seV9zaW5jZSI7YjoxO3M6MTM6ImZpbHRlcl9kb21haW4iO3M6MDoiIjt9', '9f94ada3-2558-ec7d-848f-45e5ace43091');
INSERT INTO `inbound_email` VALUES ('e840b54a-87e9-3e82-e9f9-45511d6da28a', 1, '2006-11-07 23:56:32', '2006-11-24 08:31:16', '1', '1', 'Yahya Aydin', 'Active', 'mail.anzenhosting.jp', 'yahya.aydin@technopian.com', 'sifre1.', 110, 'notls::novalidate-cert', '', 0, 'bounce', NULL, 'YTozOntzOjk6ImZyb21fbmFtZSI7czoyMzoiWWFoeWEgQXlkaW4gW1RlY2hub1NNU10iO3M6OToiZnJvbV9hZGRyIjtzOjI2OiJ5YWh5YS5heWRpbkB0ZWNobm9waWFuLmNvbSI7czoxMDoib25seV9zaW5jZSI7YjoxO30=', 'e75907ac-f94c-1f1b-9f9d-45511d257460');
INSERT INTO `inbound_email` VALUES ('eaf0686c-9410-ea1a-2a4e-45e5af8fc1ff', 1, '2007-02-28 16:36:05', '2007-02-28 16:40:48', '1', '1', 'TechnoSMS', 'Active', 'mail.technopian.com', 'technosms@technopian.com', 'yeVdrSuObvqOah4nFSEwMA==', 110, '::::::pop3::::notls::', 'INBOX', 0, 'bounce', NULL, 'YTo0OntzOjk6ImZyb21fbmFtZSI7czo5OiJUZWNobm9TTVMiO3M6OToiZnJvbV9hZGRyIjtzOjI0OiJ0ZWNobm9zbXNAdGVjaG5vcGlhbi5jb20iO3M6MTA6Im9ubHlfc2luY2UiO2I6MDtzOjEzOiJmaWx0ZXJfZG9tYWluIjtzOjA6IiI7fQ==', 'e75907ac-f94c-1f1b-9f9d-45511d257460');
        
SELECT * FROM `sms_fee` WHERE `type_id` LIKE '15' OR `type_id` LIKE '16' ;
SELECT * FROM `sms_transaction` WHERE `type_id` LIKE '15' OR `type_id` LIKE '16' ;

UPDATE `sms_fee` SET type_id='27' WHERE `type_id` LIKE '15' ;
UPDATE `sms_transaction` SET type_id='27' WHERE `type_id` LIKE '15' ;

-- ABC cooking
UPDATE `sms_fee` SET `jikyu_rate`='1'  WHERE `type_id` LIKE '15' ;
UPDATE `sms_fee` SET `type_id`='27'  WHERE `type_id` LIKE '15' ;
UPDATE `sms_transaction` SET type_id='27' WHERE `type_id` LIKE '15' ;

SELECT * FROM `sms_fee` WHERE `type_id` LIKE '15' OR id = 'cda04ab6-9515-096e-6aa4-47426de2f4f3';


-- 2007-11-21  --- ges attendance correction
	J-PL(60) TOEIC 		753f2d33-c6ae-c095-6f9b-45e130ff12d9
	
SELECT `id`, `from_datetime`,`to_datetime`,`name`,`status` FROM `ni` 
WHERE `parent_id`='753f2d33-c6ae-c095-6f9b-45e130ff12d9' AND (status & 2048)

-- CONFIRMATION 
SELECT ni.id, ni.from_datetime, ni.to_datetime, ni.name, ni.status, ni0829.id, ni0829.status FROM ni, ni0829
WHERE ni.id = ni0829.id AND (ni0829.status & 2048) 
	AND ni.parent_id='753f2d33-c6ae-c095-6f9b-45e130ff12d9' AND ni0829.parent_id='753f2d33-c6ae-c095-6f9b-45e130ff12d9' ;

UPDATE  ni, ni0829 SET ni.status = (ni.status | 2048) 
WHERE ni.id = ni0829.id AND (ni0829.status & 2048) 
	AND ni.parent_id='753f2d33-c6ae-c095-6f9b-45e130ff12d9' AND ni0829.parent_id='753f2d33-c6ae-c095-6f9b-45e130ff12d9' ;
	

UPDATE  sms_event_instance , ni0829 SET sms_event_instance.status = (sms_event_instance.status | 2048) 
WHERE sms_event_instance.id = ni0829.id AND (ni0829.status & 2048) 
	AND sms_event_instance.parent_id='753f2d33-c6ae-c095-6f9b-45e130ff12d9' AND ni0829.parent_id='753f2d33-c6ae-c095-6f9b-45e130ff12d9'
	

UPDATE sms_event_instance , ni0829 SET sms_event_instance.status = (sms_event_instance.status | 2048) 
WHERE sms_event_instance.id = ni0829.id AND (ni0829.status & 2048) 
	AND sms_event_instance.to_datetime < '2007-08-30 11:00:00' AND ni0829.to_datetime < '2007-08-30 11:00:00'
	AND sms_event_instance.parent_id='753f2d33-c6ae-c095-6f9b-45e130ff12d9' AND ni0829.parent_id='753f2d33-c6ae-c095-6f9b-45e130ff12d9'

-- HONBAN

UPDATE sms_event_instance , ni0425 SET sms_event_instance.status = (sms_event_instance.status | 2048) 
WHERE sms_event_instance.id = ni0425.id AND (ni0425.status & 2048) AND sms_event_instance.to_datetime < '2007-04-26 11:00:00' AND ni0425.to_datetime < '2007-04-26 11:00:00' ;

UPDATE sms_event_instance , ni0523 SET sms_event_instance.status = (sms_event_instance.status | 2048) 
WHERE sms_event_instance.id = ni0523.id AND (ni0523.status & 2048) AND sms_event_instance.to_datetime < '2007-05-24 11:00:00' AND ni0523.to_datetime < '2007-05-24 11:00:00' ;

UPDATE sms_event_instance , ni0628 SET sms_event_instance.status = (sms_event_instance.status | 2048) 
WHERE sms_event_instance.id = ni0628.id AND (ni0628.status & 2048) AND sms_event_instance.to_datetime < '2007-06-29 11:00:00' AND ni0628.to_datetime < '2007-06-29 11:00:00' ;

UPDATE sms_event_instance , ni0725 SET sms_event_instance.status = (sms_event_instance.status | 2048) 
WHERE sms_event_instance.id = ni0725.id AND (ni0725.status & 2048) AND sms_event_instance.to_datetime < '2007-07-26 11:00:00' AND ni0725.to_datetime < '2007-07-26 11:00:00' ;

UPDATE sms_event_instance , ni0829 SET sms_event_instance.status = (sms_event_instance.status | 2048) 
WHERE sms_event_instance.id = ni0829.id AND (ni0829.status & 2048) AND sms_event_instance.to_datetime < '2007-08-30 11:00:00' AND ni0829.to_datetime < '2007-08-30 11:00:00' ;

UPDATE sms_event_instance , ni0926 SET sms_event_instance.status = (sms_event_instance.status | 2048) 
WHERE sms_event_instance.id = ni0926.id AND (ni0926.status & 2048) AND sms_event_instance.to_datetime < '2007-09-27 11:00:00' AND ni0926.to_datetime < '2007-09-27 11:00:00' ;

-- ATTENDANCE

SELECT `id`, `from_datetime`,`to_datetime`,`name`,`status` FROM `nis` 
WHERE `event_id`='753f2d33-c6ae-c095-6f9b-45e130ff12d9' AND  attendance_status IS NOT NULL

UPDATE nis , nis0725 SET nis.attendance_status = nis.instance_contact_status
WHERE nis.id = nis0725.id AND (nis0725.attendance_status IS NOT NULL) AND nis.to_datetime < '2007-07-26 11:00:00' AND nis0725.to_datetime < '2007-07-26 11:00:00' AND nis.event_id='753f2d33-c6ae-c095-6f9b-45e130ff12d9' 

UPDATE smsevent_instances , nis0725 SET smsevent_instances.attendance_status = smsevent_instances.instance_contact_status
WHERE smsevent_instances.id = nis0725.id AND (nis0725.attendance_status IS NOT NULL) AND smsevent_instances.to_datetime < '2007-07-26 11:00:00' AND nis0725.to_datetime < '2007-07-26 11:00:00' AND smsevent_instances.event_id='753f2d33-c6ae-c095-6f9b-45e130ff12d9' 

-- HONBAN


UPDATE smsevent_instances , nis0425 SET smsevent_instances.attendance_status = smsevent_instances.instance_contact_status
WHERE smsevent_instances.id = nis0425.id AND (nis0425.attendance_status IS NOT NULL) AND smsevent_instances.to_datetime < '2007-04-26 11:00:00' AND nis0425.to_datetime < '2007-04-26 11:00:00' ;

UPDATE smsevent_instances , nis0523 SET smsevent_instances.attendance_status = smsevent_instances.instance_contact_status
WHERE smsevent_instances.id = nis0523.id AND (nis0523.attendance_status IS NOT NULL) AND smsevent_instances.to_datetime < '2007-05-24 11:00:00' AND nis0523.to_datetime < '2007-05-24 11:00:00' ;

UPDATE smsevent_instances , nis0628 SET smsevent_instances.attendance_status = smsevent_instances.instance_contact_status
WHERE smsevent_instances.id = nis0628.id AND (nis0628.attendance_status IS NOT NULL) AND smsevent_instances.to_datetime < '2007-06-29 11:00:00' AND nis0628.to_datetime < '2007-06-29 11:00:00' ;

UPDATE smsevent_instances , nis0725 SET smsevent_instances.attendance_status = smsevent_instances.instance_contact_status
WHERE smsevent_instances.id = nis0725.id AND (nis0725.attendance_status IS NOT NULL) AND smsevent_instances.to_datetime < '2007-07-26 11:00:00' AND nis0725.to_datetime < '2007-07-26 11:00:00' ;

UPDATE smsevent_instances , nis0829 SET smsevent_instances.attendance_status = smsevent_instances.instance_contact_status
WHERE smsevent_instances.id = nis0829.id AND (nis0829.attendance_status IS NOT NULL) AND smsevent_instances.to_datetime < '2007-08-30 11:00:00' AND nis0829.to_datetime < '2007-08-30 11:00:00' ;

UPDATE smsevent_instances , nis0926 SET smsevent_instances.attendance_status = smsevent_instances.instance_contact_status
WHERE smsevent_instances.id = nis0926.id AND (nis0926.attendance_status IS NOT NULL) AND smsevent_instances.to_datetime < '2007-09-27 11:00:00' AND nis0926.to_datetime < '2007-09-27 11:00:00' ;





---  KAPLAN MIKEIKA WORK

-- COURSE
SELECT 	count(smsevent_instances.event_id) as number_of_occurrences, 
								CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
								contacts.id as contact_id, smsevent.id as event_id, 
								accounts.name as account_name, accounts.id as account_id,
								smsevent.name as course_name, contacts.smsgroup, sms_fee.id as fee_id, 
								IFNULL(sms_fee.discount_price, sms_fee.basic_fee) as sell_amount,
								sms_fee.name as description, smsevent_instances.from_datetime as t_date,
								event_contacts.start_date, event_contacts.end_date,
								sms_fee.sell_date
							FROM smsevent_instances 
							LEFT JOIN smsevent ON smsevent_instances.event_id = smsevent.id AND smsevent.deleted=0 
							LEFT JOIN event_contacts ON event_contacts.event_id = smsevent.id AND event_contacts.deleted=0
							CROSS JOIN contacts ON contacts.deleted=0 AND event_contacts.contact_id=contacts.id 
							CROSS JOIN sms_fee ON sms_fee.contact_id = contacts.id AND sms_fee.event_id = smsevent_instances.event_id AND sms_fee.deleted=0 
							LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id
							
							LEFT JOIN accounts_contacts ON contacts.id = accounts_contacts.contact_id
							LEFT JOIN accounts ON accounts_contacts.account_id = accounts.id WHERE  1=1  AND sms_fee.school_id = '483794c4-adee-a607-f685-4441c943a1e2' 	AND smsevent_instances.from_datetime <= event_contacts.end_date
							AND smsevent_instances.from_datetime >= event_contacts.start_date  AND (smsevent_instances.instance_contact_status & 128) = 0   AND smsevent_instances.from_datetime < '2007-10-31 15:00:00 ' AND smsevent_instances.from_datetime >= '2007-09-30 15:00:00'  AND sms_transactiontype.keiri_code=401 AND sms_fee.applied_contact=1  AND contacts.smsgroup = 'student' AND smsevent.id = smsevent_instances.event_id AND contacts.id = smsevent_instances.target_id  	AND (sms_fee.type_id NOT IN ('1', '2') OR (
								smsevent_instances.from_datetime <= CONCAT(sms_fee.end_date,' 23:59:59') 
								AND smsevent_instances.from_datetime >= CONCAT(sms_fee.start_date,' 00:00:00') ) )  GROUP BY smsevent_instances.event_id, contacts.id, sms_fee.id  ORDER BY sms_fee.sell_date desc 
								
 AND smsevent_instances.from_datetime > '2007-10-31 15:00:00' 
 
 
 -- STUDENT
 SELECT 	count(smsevent_instances.event_id) as number_of_occurrences, 
									CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
									contacts.id as contact_id, 'student_based' as event_id,
									accounts.name as account_name, accounts.id as account_id, 
									'' as course_name, contacts.smsgroup, sms_fee.id as fee_id, 
									IFNULL(sms_fee.discount_price, sms_fee.basic_fee) as sell_amount,
									sms_fee.name as description, smsevent_instances.from_datetime as t_date,
									sms_fee.start_date, sms_fee.end_date,
									sms_fee.sell_date
								FROM smsevent_instances 
								LEFT JOIN smsevent ON smsevent_instances.event_id = smsevent.id AND smsevent.deleted=0 
								LEFT JOIN event_contacts ON event_contacts.event_id = smsevent.id AND event_contacts.deleted=0
								CROSS JOIN contacts ON contacts.deleted=0 AND event_contacts.contact_id=contacts.id 
								CROSS JOIN sms_fee ON sms_fee.contact_id = contacts.id AND sms_fee.event_id IS NULL AND sms_fee.deleted=0 
								LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id 
								
								LEFT JOIN accounts_contacts ON contacts.id = accounts_contacts.contact_id
								LEFT JOIN accounts ON accounts_contacts.account_id = accounts.id WHERE  1=1  AND sms_fee.school_id = '483794c4-adee-a607-f685-4441c943a1e2' 	AND smsevent_instances.from_datetime <= event_contacts.end_date
								AND smsevent_instances.from_datetime >= event_contacts.start_date  AND (smsevent_instances.instance_contact_status & 128) = 0   AND smsevent_instances.from_datetime < '2007-10-31 15:00:00 ' AND smsevent_instances.from_datetime >= '2007-09-30 15:00:00'  AND sms_transactiontype.keiri_code=403 AND sms_fee.applied_contact=1  AND contacts.smsgroup = 'student' AND smsevent.id = smsevent_instances.event_id AND contacts.id = smsevent_instances.target_id  	AND (
									smsevent_instances.from_datetime <= CONCAT(sms_fee.end_date,' 23:59:59') 
									AND smsevent_instances.from_datetime >= CONCAT(sms_fee.start_date,' 00:00:00') )  GROUP BY contacts.id, sms_fee.id  ORDER BY sms_fee.sell_date desc 
									
 AND smsevent_instances.from_datetime > '2007-10-31 15:00:00' 
 
 -- KAISU
 SELECT 	CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
									contacts.id as contact_id, 'kaisu_based' as event_id,
									'' as course_name, contacts.smsgroup, sms_fee.id as fee_id, 
									IFNULL(sms_fee.discount_price, sms_fee.basic_fee) as sell_amount,
									sms_fee.name as description, sms_fee.start_date as t_date,
									sms_fee.start_date, sms_fee.jikyu_rate as end_date,
									sms_fee.sell_date									
								FROM sms_fee 
								LEFT JOIN contacts ON contacts.deleted=0 AND sms_fee.contact_id=contacts.id 
								LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id  WHERE   1=1  AND sms_fee.school_id = '483794c4-adee-a607-f685-4441c943a1e2' and sms_fee.deleted=0  AND sms_transactiontype.keiri_code=407 AND sms_fee.applied_contact=1  AND contacts.smsgroup = 'student'   AND ( SUBSTRING(sms_fee.start_date, 1, 7) <= '2007-10'
										AND date_add(sms_fee.start_date, INTERVAL sms_fee.jikyu_rate MONTH) 
											>= '2007-10-31' )  GROUP BY contacts.id, sms_fee.id  ORDER BY sms_fee.sell_date desc 
											
 AND smsevent_instances.from_datetime > '2007-10-31 15:00:00' 
 
 
 -- NON-TUITION FOR SCHEDULED COURSES
 SELECT
								contacts.id as contact_id, smsevent.id as course_id, 
								CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
								accounts.name as account_name, accounts.id as account_id, 
								smsevent.name as course_name, contacts.smsgroup, 
								sms_fee.id as fee_id, sms_fee.name as description, IFNULL(sms_fee.discount_price, sms_fee.basic_fee) as sell_amount,
								event_contacts.start_date, event_contacts.end_date, smsevent_instances.from_datetime as t_date,
								sms_fee.sell_date
							FROM smsevent_instances 
							
							LEFT JOIN smsevent ON smsevent_instances.event_id = smsevent.id AND smsevent.deleted=0
							LEFT JOIN event_contacts ON event_contacts.event_id = smsevent.id AND event_contacts.deleted=0
							JOIN sms_fee ON sms_fee.event_id = smsevent_instances.event_id AND sms_fee.deleted=0 
							
							
							
							
							LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id 
							CROSS JOIN contacts ON event_contacts.contact_id=contacts.id AND sms_fee.contact_id = contacts.id AND contacts.deleted=0 
							
							LEFT JOIN accounts_contacts ON accounts_contacts.contact_id = contacts.id
							LEFT JOIN accounts ON accounts_contacts.account_id = accounts.id  WHERE sms_transactiontype.keiri_code!=401 AND sms_transactiontype.keiri_code!=403 AND sms_transactiontype.keiri_code!=407 AND sms_fee.applied_contact=1  AND contacts.smsgroup = 'student' AND smsevent.id = smsevent_instances.event_id AND contacts.id = smsevent_instances.target_id  AND sms_fee.school_id = '483794c4-adee-a607-f685-4441c943a1e2'  AND event_contacts.start_date < '2007-10-31 15:00:00 ' AND event_contacts.start_date >= '2007-09-30 15:00:00' 
							
							
-- NOT COURSE RELATED
SELECT
								sms_transaction.payment_type, sms_fee.name as description, sms_transaction.t_date, sms_transaction.id, 
								contacts.id as contact_id, CONCAT(contacts.last_name, ' ', IFNULL(contacts.first_name, '') ) as contact_name,
								accounts.name as account_name, accounts.id as account_id, 
								contacts.smsgroup, sms_transaction.sell_date as start_date, sms_transaction.sell_date as end_date, 
								sms_transaction.fee_id as fee_id, sms_transaction.in_amount as sell_amount,
								sms_fee.sell_date
								FROM sms_transaction, sms_fee, contacts
								
								
								LEFT JOIN accounts_contacts ON contacts.id = accounts_contacts.contact_id
								LEFT JOIN accounts ON accounts_contacts.account_id = accounts.id
							
								WHERE sms_transaction.contact_id = contacts.id
									AND sms_transaction.event_id IS NULL AND (sms_transaction.type_id NOT IN ('15', '16', '27', '28')) AND sms_transaction.deleted=0
									AND sms_transaction.fee_id = sms_fee.id AND sms_fee.deleted=0  AND contacts.smsgroup = 'student'  AND sms_fee.school_id = '483794c4-adee-a607-f685-4441c943a1e2'  AND sms_transaction.deleted=0 AND contacts.deleted=0  AND sms_transaction.sell_date < '2007-10-31 15:00:00 ' AND sms_transaction.sell_date >= '2007-09-30 15:00:00' 
									
									
									
-- WORK MIKEIKA URIAGE FUTURE ? 
-- Kaplan/test eiko December Uriage extra queries

SELECT SUM(IFNULL(sms_fee.discount_price, sms_fee.basic_fee)) as total_fee 
					FROM sms_fee
					LEFT JOIN contacts ON sms_fee.contact_id = contacts.id AND contacts.deleted=0 AND contacts.smsgroup='student'
					LEFT JOIN event_contacts ON event_contacts.contact_id = contacts.id AND event_contacts.deleted=0 
					LEFT JOIN smsevent ON smsevent.id = event_contacts.event_id AND sms_fee.event_id = smsevent.id AND smsevent.deleted=0 
					LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id AND sms_transactiontype.keiri_code=401
					WHERE sms_fee.deleted=0 AND sms_fee.applied_contact=1 
						AND sms_fee.school_id = 'baaafce6-ff68-4ba5-dd7c-4519098601a2' 
						AND sms_fee.sell_date < '2007-10-31 15:00:00'
						AND event_contacts.start_date > '2007-10-31 15:00:00' AND sms_fee.start_date > '2007-10-31 15:00:00' ;
						
				
						
 	SELECT SUM(IFNULL(sms_fee.discount_price, sms_fee.basic_fee)) as total_fee 
						FROM sms_fee
						LEFT JOIN contacts ON sms_fee.contact_id = contacts.id AND contacts.deleted=0 AND contacts.smsgroup='student'
						LEFT JOIN event_contacts ON event_contacts.contact_id = contacts.id AND event_contacts.deleted=0 
						LEFT JOIN smsevent ON smsevent.id = event_contacts.event_id AND sms_fee.event_id = smsevent.id AND smsevent.deleted=0 
						LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id AND sms_transactiontype.keiri_code=403 
						WHERE sms_fee.deleted=0 AND sms_fee.applied_contact=1 AND sms_fee.event_id IS NULL
							AND sms_fee.school_id = 'baaafce6-ff68-4ba5-dd7c-4519098601a2' 
							AND sms_fee.sell_date < '2007-10-31 15:00:00'
							AND event_contacts.start_date > '2007-10-31 15:00:00' AND sms_fee.start_date > '2007-10-31 15:00:00' ;
					
							
 	SELECT SUM(IFNULL(sms_fee.discount_price, sms_fee.basic_fee)) as total_fee 
						FROM sms_fee
						LEFT JOIN contacts ON sms_fee.contact_id = contacts.id AND contacts.deleted=0 AND contacts.smsgroup='student'
						LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id AND sms_transactiontype.keiri_code=407
						WHERE sms_fee.deleted=0 AND sms_fee.applied_contact=1 
							AND sms_fee.school_id = 'baaafce6-ff68-4ba5-dd7c-4519098601a2' 
							AND sms_fee.sell_date < '2007-10-31 15:00:00'
							AND ( SUBSTRING(sms_fee.start_date, 1, 7) > '2007-10' ) ;
							
							
 	SELECT SUM(IFNULL(sms_fee.discount_price, sms_fee.basic_fee)) as total_fee 
						FROM sms_fee
						LEFT JOIN contacts ON sms_fee.contact_id = contacts.id AND contacts.deleted=0 AND contacts.smsgroup='student'
						LEFT JOIN event_contacts ON event_contacts.contact_id = contacts.id AND event_contacts.deleted=0 
						LEFT JOIN smsevent ON smsevent.id = event_contacts.event_id AND sms_fee.event_id = smsevent.id AND smsevent.deleted=0 
						LEFT JOIN sms_transactiontype ON sms_transactiontype.id = sms_fee.type_id 
							AND sms_transactiontype.keiri_code!=401 AND sms_transactiontype.keiri_code!=403 AND sms_transactiontype.keiri_code!=407
						WHERE sms_fee.deleted=0 AND sms_fee.applied_contact=1 
							AND sms_fee.school_id = 'baaafce6-ff68-4ba5-dd7c-4519098601a2' 
							AND sms_fee.sell_date < '2007-10-31 15:00:00'
							AND event_contacts.start_date > '2007-10-31 15:00:00' AND sms_fee.start_date > '2007-10-31 15:00:00' ;
					

-- WORK AGE REPORT
	
 SELECT CEILING((YEAR(CURDATE()) - YEAR(contacts.birthdate))/5) AS age,   DATE_FORMAT(date_add(contacts. date_entered , INTERVAL 540 MINUTE),  '%Y ') as year_ , DATE_FORMAT(date_add(contacts. date_entered , INTERVAL 540 MINUTE),  '%m ') as month_ , count(*) as count_  from contacts   	LEFT JOIN event_contacts on contacts.id = event_contacts.contact_id  AND event_contacts.deleted = 0
						LEFT JOIN smsevent on event_contacts.event_id = smsevent.id AND smsevent.category='course' AND smsevent.deleted=0
						LEFT JOIN category_tree as course on smsevent.id = course.self_id
						LEFT JOIN category_tree as category on course.parent_node_id = category.node_id
						LEFT JOIN course_category on category.self_id = course_category.id	 
				where  contacts.smsgroup='student'  and contacts.deleted = 0   and 
				DATE_FORMAT(date_add(contacts. date_entered , INTERVAL 540 MINUTE), '%Y-%m') >='2007-01'   and 
				DATE_FORMAT(date_add(contacts. date_entered , INTERVAL 540 MINUTE), '%Y-%m') <='2007-01'  and 
				contacts.school_id='sssssss-1111'  and 
				contacts.school_id in  ('sssssss-1111','483794c4-adee-a607-f685-4441c943a1e2','58362bd0-5d1e-c3f9-efd1-4441c972601c','baaafce6-ff68-4ba5-dd7c-4519098601a2')  group by age  , year_, month_  order by contacts. date_entered  asc, count_ desc ;
				
SELECT id , last_name , first_name , birthdate 
FROM `contacts` 
WHERE deleted = 0 
AND school_id = 'sssssss-1111' 
AND DATE_FORMAT(date_add(contacts. date_entered , INTERVAL 540 MINUTE), '%Y-%m') >='2007-01'   
AND DATE_FORMAT(date_add(contacts. date_entered , INTERVAL 540 MINUTE), '%Y-%m') <='2007-01'  
AND contacts.smsgroup = 'student' 
AND birthdate IS NOT NULL 
ORDER BY `birthdate` ASC 

 SELECT last_name , first_name , birthdate 
 from contacts   	LEFT JOIN event_contacts on contacts.id = event_contacts.contact_id  AND event_contacts.deleted = 0
						LEFT JOIN smsevent on event_contacts.event_id = smsevent.id AND smsevent.category='course' AND smsevent.deleted=0
						LEFT JOIN category_tree as course on smsevent.id = course.self_id
						LEFT JOIN category_tree as category on course.parent_node_id = category.node_id
						LEFT JOIN course_category on category.self_id = course_category.id	 
				where  contacts.smsgroup='student'  and contacts.deleted = 0   and 
				DATE_FORMAT(date_add(contacts. date_entered , INTERVAL 540 MINUTE), '%Y-%m') >='2007-01'   and 
				DATE_FORMAT(date_add(contacts. date_entered , INTERVAL 540 MINUTE), '%Y-%m') <='2007-01'  and 
				contacts.school_id='sssssss-1111'  and 
				contacts.school_id in  ('sssssss-1111','483794c4-adee-a607-f685-4441c943a1e2','58362bd0-5d1e-c3f9-efd1-4441c972601c','baaafce6-ff68-4ba5-dd7c-4519098601a2')  group by age  , year_, month_  order by contacts. date_entered  asc, count_ desc ;
				
CREATE TABLE `sms_transactiontype` (
  `id` varchar(36) NOT NULL default '',
  `name` varchar(100) NOT NULL default '',
  `deleted` tinyint(1) NOT NULL default '0',
  `date_entered` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) default NULL,
  `created_by` varchar(36) default NULL,
  `keiri_category` varchar(50) default NULL,
  `apply_new_student` tinyint(1) NOT NULL default '0',
  `show_in_edit_fee` tinyint(1) NOT NULL default '1',
  `keiri_code` int(8) default '0',
  `description` text,
  PRIMARY KEY  (`id`),
  KEY `keiri_category` (`keiri_category`,`keiri_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `sms_transactiontype` VALUES 
('27', 'tuition student kaisu', 0, '2007-09-15 00:00:00', '2007-09-15 00:00:00', NULL, NULL, 'student', 0, 1, 407, 'Tuition fees with even distribution'),
('28', 'tuition gessha kaisu', 0, '2007-11-03 12:00:00', '2007-11-03 12:00:00', NULL, NULL, 'student', 0, 1, 407, 'Gessha fees with even distribution'),
('29', 'tuition kaisu course', 0, '2008-06-29 12:00:00', '2008-06-29 12:00:00', NULL, NULL, 'student', 1, 1, 407, 'Course tuition fees with even distribution'),
('5', 'textbook', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 1, 1, 402, 'Textbook fee for students'),
('6', 'entrance', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 1, 1, 402, 'Entrance fee for students'),
('17', 'student other', 0, '2007-04-28 00:00:00', '2007-04-28 00:00:00', NULL, NULL, 'student', 1, 1, 402, 'Other student fees'),
('4', 'parttime salary', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 1, 653, 'Part-time salary for teachers'),
('14', 'salary', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 1, 652, 'Salary for teachers'),
('3', 'transportation', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 1, 665, 'Transporation fee for teachers'),
('13', 'adjustment', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 0, 665, 'Salary djusment value for teachers'),
('12', 'transfer_out', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'teacher', 0, 1, 665, 'Money transfer for teachers');
        

SELECT *  FROM `users` WHERE `status` LIKE 'Active' AND `deleted` =0 ;

-- Table structure for table `inbound_email` technosms demo site
CREATE TABLE `inbound_email` (
  `id` varchar(36) NOT NULL default '',
  `deleted` tinyint(1) NOT NULL default '0',
  `date_entered` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) default NULL,
  `created_by` varchar(36) default NULL,
  `name` varchar(255) default NULL,
  `status` varchar(25) NOT NULL default 'Active',
  `server_url` varchar(100) NOT NULL default '',
  `email_user` varchar(100) NOT NULL default '',
  `email_password` varchar(100) NOT NULL default '',
  `port` int(5) NOT NULL default '0',
  `service` varchar(30) NOT NULL default '',
  `mailbox` varchar(50) NOT NULL default '',
  `delete_seen` tinyint(1) default '0',
  `mailbox_type` varchar(10) default NULL,
  `template_id` varchar(36) default NULL,
  `stored_options` varchar(255) default NULL,
  `group_id` varchar(36) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `inbound_email` (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, `name`, `status`, `server_url`, `email_user`, `email_password`, `port`, `service`, `mailbox`, `delete_seen`, `mailbox_type`, `template_id`, `stored_options`, `group_id`) VALUES 
('d1f90bbb-d603-cb2f-da44-441fc60da065', 0, '2006-03-21 09:26:51', '2007-05-23 04:14:26', '1', '1', 'TechnoSMS', 'Active', 'mail.anzenhosting.jp', 'technosms@technopian.com', 'yeVdrSuObvqOah4nFSEwMA==', 110, '::::::pop3::::notls::', 'INBOX', 0, NULL, NULL, 'YTo0OntzOjk6ImZyb21fbmFtZSI7czowOiIiO3M6OToiZnJvbV9hZGRyIjtzOjA6IiI7czoxMDoib25seV9zaW5jZSI7YjowO3M6MTM6ImZpbHRlcl9kb21haW4iO3M6MDoiIjt9', '3c801f9b-cf28-fe53-cd5d-4653bfe5220a'),
('e840b54a-87e9-3e82-e9f9-45511d6da28a', 1, '2006-11-07 23:56:32', '2006-11-24 08:31:16', '1', '1', 'Yahya Aydin', 'Active', 'mail.anzenhosting.jp', 'yahya.aydin@technopian.com', 'sifre1.', 110, 'notls::novalidate-cert', '', 0, 'bounce', NULL, 'YTozOntzOjk6ImZyb21fbmFtZSI7czoyMzoiWWFoeWEgQXlkaW4gW1RlY2hub1NNU10iO3M6OToiZnJvbV9hZGRyIjtzOjI2OiJ5YWh5YS5heWRpbkB0ZWNobm9waWFuLmNvbSI7czoxMDoib25seV9zaW5jZSI7YjoxO30=', 'e75907ac-f94c-1f1b-9f9d-45511d257460');

