SELECT * 
FROM `contacts` 
WHERE idnumber
IN (
'NG00000188', 'NG00000189', 'NG00000190', 'NG00000191', 'NG00000192', 'NG00000193', 'NG00000194', 'NG00000195', 'NG00000196', 'NG00000197', 'NG00000198', 'NG00000199', 'NG00000200', 'NG00000201', 'NG00000202', 'NG00000203', 'NG00000204', 'NG00000205', 'NG00000206', 'NG00000207', 'NG00000208', 'NG00000209', 'NG00000210', 'NG00000211', 'NG00000212', 'NG00000213', 'NG00000214', 'NG00000215', 'NG00000216', 'NG00000217', 'NG00000218', 'NG00000219', 'NG00000220', 'NG00000221', 'NG00000222', 'NG00000223', 'NG00000224', 'NG00000225', 'NG00000226', 'NG00000227', 'NG00000228', 'NG00000229', 'NG00000230', 'NG00000231', 'NG00000232'
)
LIMIT 0 , 30;


update contacts set school_id = '58362bd0-5d1e-c3f9-efd1-4441c972601c' 
WHERE idnumber
IN (
'NG00000188', 'NG00000189', 'NG00000190', 'NG00000191', 'NG00000192', 'NG00000193', 'NG00000194', 'NG00000195', 'NG00000196', 'NG00000197', 'NG00000198', 'NG00000199', 'NG00000200', 'NG00000201', 'NG00000202', 'NG00000203', 'NG00000204', 'NG00000205', 'NG00000206', 'NG00000207', 'NG00000208', 'NG00000209', 'NG00000210', 'NG00000211', 'NG00000212', 'NG00000213', 'NG00000214', 'NG00000215', 'NG00000216', 'NG00000217', 'NG00000218', 'NG00000219', 'NG00000220', 'NG00000221', 'NG00000222', 'NG00000223', 'NG00000224', 'NG00000225', 'NG00000226', 'NG00000227', 'NG00000228', 'NG00000229', 'NG00000230', 'NG00000231', 'NG00000232'
);


SELECT primary_address_city, primary_address_street from contacts
 where primary_address_street LIKE "%郡%" ;
 
SELECT contacts.id,contacts_backup2.primary_address_street as part1, CONCAT_WS('',contacts.primary_address_state,contacts.primary_address_city,contacts.primary_address_street) as part2,
contacts.date_modified from contacts INNER JOIN contacts_backup2 on contacts.id = contacts_backup2.id
WHERE
contacts_backup2.primary_address_street !=  CONCAT_WS('',contacts.primary_address_state,contacts.primary_address_city,contacts.primary_address_street) AND
contacts.date_modified = '2007-07-19 19:27:56'
 

update contacts, contacts_backup2 set contacts.primary_address_street = contacts_backup2.primary_address_street,
contacts.primary_address_state='', contacts.primary_address_city=''
WHERE
contacts.id= contacts_backup2.id AND
contacts_backup2.primary_address_street !=  CONCAT_WS('',contacts.primary_address_state,contacts.primary_address_city,contacts.primary_address_street) AND
contacts.date_modified = '2007-07-19 19:27:56'

update contacts set custom_var2='FALSE' where date_modified='2007-08-07' and date_entered='2007-08-07';


-- 2007-10-18  --- metehan

SELECT * FROM `contacts` WHERE (custom_var19 & '256') ;
UPDATE `contacts` SET custom_var19 = (custom_var19 -256) WHERE (custom_var19 & '256') and  (custom_var19 & '512') ;
SELECT id, custom_var19, (custom_var19 -256) FROM `contacts` WHERE (custom_var19 & '256') ;
UPDATE `contacts` SET custom_var19 = 512 WHERE (custom_var19 & '256') ;

SELECT id, custom_var2 FROM `contacts` WHERE custom_var2 = '0' ;
UPDATE contacts SET custom_var2= 'FALSE' WHERE custom_var2 = '0' ;


-- 2007-10-31  --- metehan

SELECT c.last_name,c.first_name,r.name,c.idnumber,c.application_date, contact_status FROM contacts c, resource r 
WHERE c.smsgroup='student' and c.deleted=0 and c.school_id = r.id and (custom_var19 & 2) ;  // list are created for 2(F), 16(C), 128(J)

-- 2008-04-01  metehan  
DELETE FROM `sms_transactiontype` WHERE CONVERT(`sms_transactiontype`.`id` USING utf8) = '2' LIMIT 1;
DELETE FROM `sms_transactiontype` WHERE CONVERT(`sms_transactiontype`.`id` USING utf8) = '28' LIMIT 1;

-- 2008-04-16  metehan  
DROP TABLE IF EXISTS `sms_transactiontype`;
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;

-- 
-- 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`, `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'),
('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'),
('8', 'refund', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 0, 401, 'Refund'),
('9', 'refund_c', 0, '2006-01-15 00:00:00', '2006-01-15 00:00:00', NULL, NULL, 'student', 0, 0, 301, 'Refund commission'),
('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'),
('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'),
('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');


-- 2008-05-14
UPDATE sms_fee SET deleted=1 WHERE  contact_id LIKE 'f00caa0b-f1c7-f25f-5814-4791ae7a8956';
UPDATE sms_transaction SET deleted=1 WHERE  contact_id LIKE 'f00caa0b-f1c7-f25f-5814-4791ae7a8956';
UPDATE sms_salesgroup SET deleted=1 WHERE  contact_id LIKE 'f00caa0b-f1c7-f25f-5814-4791ae7a8956';

--2008-05-15
UPDATE sms_fee SET deleted=1 WHERE  contact_id LIKE 'c238f3b8-c2ce-b070-231b-47b928cc486d';
UPDATE sms_transaction SET deleted=1 WHERE  contact_id LIKE 'c238f3b8-c2ce-b070-231b-47b928cc486d';
UPDATE sms_salesgroup SET deleted=1 WHERE  contact_id LIKE 'c238f3b8-c2ce-b070-231b-47b928cc486d';


-- 2008-11-18
update sms_transaction SET payment_type='dccard' WHERE payment_type='jcb';

-- 2009-07-07
UPDATE sms_fee SET fee_subcategory = 'tuition' WHERE name LIKE "%?????";
UPDATE sms_fee SET fee_subcategory = 'entrance' WHERE name LIKE "%?��???";
UPDATE sms_fee SET fee_subcategory = 'textbook' WHERE name LIKE "%???�?";
UPDATE sms_fee SET fee_subcategory = 'flower' WHERE name LIKE "%?��?�?";
UPDATE sms_fee SET fee_subcategory = 'association' WHERE name LIKE "%???%";

UPDATE `sms_fee` SET type_id = '27' WHERE type_id='17';
UPDATE `sms_transaction` SET type_id = '27' WHERE type_id='17';

UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE CONVERT( `sms_transactiontype`.`id` USING utf8 ) = '17' LIMIT 1 ;
UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE CONVERT( `sms_transactiontype`.`id` USING utf8 ) = '5' LIMIT 1 ;


-- 2009-07-14
UPDATE sms_fee SET fee_subcategory = 'tuition' WHERE name LIKE "%224100F????��??��????????��??��?%";
UPDATE sms_fee SET fee_subcategory = 'tuition' WHERE name LIKE "%241100F????��??��??��?%";
UPDATE sms_fee SET fee_subcategory = 'tuition' WHERE name LIKE "%3411CCT???????��??��???";
UPDATE sms_fee SET fee_subcategory = 'tuition' WHERE name LIKE "%?��??��???????%";
UPDATE sms_fee SET fee_subcategory = 'tuition' WHERE name LIKE "%�??�?";

UPDATE sms_fee SET fee_subcategory = 'other' WHERE fee_subcategory IS NULL;


-- 2009-07-15
UPDATE `sms_fee` SET start_date = sell_date WHERE fee_subcategory!='tuition' 
AND id NOT IN (
'ed99043a-b951-9cdd-aafb-4a3b50a4f5f8',
'1abdedbe-ffa9-0d94-4ba4-4a35e75d91f2',
'dd297e29-3686-eabd-5979-4a35d55791f0',
'c61f58ad-069e-9583-e6c5-4a41aafc35bf'
);

-- 2009-07-15 queries checking for email shot problem
SELECT last_name, first_name, idnumber, emails.name, contacts.id, emails.date_entered
FROM contacts, prospect_lists_prospects, emails
WHERE prospect_lists_prospects.prospect_list_id = '7f4f0c50-9c11-32ec-9323-4c11b2ad97ac'
	AND contacts.id = prospect_lists_prospects.related_id 
	AND emails.parent_id = contacts.id AND emails.parent_id = prospect_lists_prospects.related_id
	AND emails.date_entered > '2010-06-11'
GROUP BY contacts.id ;

SELECT contacts.id, count(*)
FROM contacts, prospect_lists_prospects, emails
WHERE prospect_lists_prospects.prospect_list_id = '7f4f0c50-9c11-32ec-9323-4c11b2ad97ac'
	AND contacts.id = prospect_lists_prospects.related_id 
	AND emails.parent_id = contacts.id AND emails.parent_id = prospect_lists_prospects.related_id
	AND emails.date_entered > '2010-06-11'
GROUP BY contacts.id 
HAVING count(*) >1 ;

/*  yahya20100706     --  to split emails table into two tables (2nd one is emailsemail_cstm)  */
CREATE TABLE `emailsemail_cstm` SELECT * FROM `emails`;
ALTER TABLE `emailsemail_cstm` CHANGE `id` `id_c` VARCHAR( 36 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `emailsemail_cstm` DROP `date_entered` ,
DROP `date_modified` ,
DROP `assigned_user_id` ,
DROP `modified_user_id` ,
DROP `created_by` ,
DROP `name` ,
DROP `date_start` ,
DROP `time_start` ,
DROP `parent_type` ,
DROP `parent_id` ,
DROP `from_addr` ,
DROP `from_name` ,
DROP `type` ,
DROP `status` ,
DROP `message_id` ,
DROP `reply_to_name` ,
DROP `reply_to_addr` ,
DROP `intent` ,
DROP `mailbox_id` ,
DROP `deleted` ;
ALTER TABLE `emailsemail_cstm` ADD PRIMARY KEY ( `id_c` ) ;

ALTER TABLE `emails` DROP `description` ,
DROP `description_html` ,
DROP `to_addrs` ,
DROP `cc_addrs` ,
DROP `bcc_addrs` ,
DROP `to_addrs_ids` ,
DROP `to_addrs_names` ,
DROP `to_addrs_emails` ,
DROP `cc_addrs_ids` ,
DROP `cc_addrs_names` ,
DROP `cc_addrs_emails` ,
DROP `bcc_addrs_ids` ,
DROP `bcc_addrs_names` ,
DROP `bcc_addrs_emails` ;

ALTER TABLE `contacts` ADD INDEX `first_name_deleted` ( `first_name` , `deleted` ) ;

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
('Emailsid_c', 'id_c', 'id_c', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailsdescription', 'description', 'description', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailsdescription_html', 'description_html', 'description_html', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailsto_addrs', 'to_addrs', 'to_addrs', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailscc_addrs', 'cc_addrs', 'cc_addrs', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailsbcc_addrs', 'bcc_addrs', 'bcc_addrs', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailsto_addrs_ids', 'to_addrs_ids', 'to_addrs_ids', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailsto_addrs_names', 'to_addrs_names', 'to_addrs_names', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailsto_addrs_emails', 'to_addrs_emails', 'to_addrs_emails', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailscc_addrs_ids', 'cc_addrs_ids', 'cc_addrs_ids', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailscc_addrs_names', 'cc_addrs_names', 'cc_addrs_names', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailscc_addrs_emails', 'cc_addrs_emails', 'cc_addrs_emails', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emailsbcc_addrs_ids', 'bcc_addrs_ids', 'bcc_addrs_ids', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0'),
('Emails', 'bcc_addrs_emails', 'bcc_addrs_emails', 'Emails', 'text', 255, 'optional', NULL, '0000-00-00 00:00:00', 0, 0, 'moduleList', '0', '0');

--  special update for emailman table since Save is changes this updates must also be applied!!!
ALTER TABLE `emailman` ADD `email_type` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'email1';

ALTER TABLE `emails` ADD UNIQUE `id_deleted` ( `id` , `deleted` ) ;  
ALTER TABLE `emails` ADD INDEX ( `assigned_user_id` ); 

-- 2013-05-07 DB time updates
UPDATE `emails` SET `date_entered`=DATE_ADD(`date_entered`, INTERVAL 540 MINUTE), `date_modified`=DATE_ADD(`date_modified`, INTERVAL 540 MINUTE) WHERE 1 ; 

-- 2013-5-30 fee updates
SELECT sms_fee.name, sms_fee2.name, sms_fee.salesno, sms_fee2.salesno
FROM sms_fee, sms_fee2 WHERE sms_fee.deleted=0 AND sms_fee2.deleted=0 AND sms_fee.contact_id IS NULL AND sms_fee.contact_id IS NULL AND sms_fee.id=sms_fee2.id AND sms_fee.salesno!=sms_fee2.salesno

UPDATE sms_fee, sms_fee2 SET sms_fee2.salesno=sms_fee.salesno 
WHERE sms_fee.deleted=0 AND sms_fee2.deleted=0 AND sms_fee.contact_id IS NULL AND sms_fee.contact_id IS NULL AND sms_fee.id=sms_fee2.id AND sms_fee.salesno!=sms_fee2.salesno