', NULL, 0, 1, 1, '{ts escape="sql"}Default domain email address and from name.{/ts}', 0, 0, 1, NULL, @domainID, NULL ),
-- grant types
(@option_group_id_grantTyp, '{ts escape="sql"}Emergency{/ts}' , 1, 'Emergency' , NULL, 0, 1, 1, NULL, 0, 0, 1, NULL, @domainID, NULL),
(@option_group_id_grantTyp, '{ts escape="sql"}Family Support{/ts}' , 2, 'Family Support' , NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL, @domainID, NULL),
(@option_group_id_grantTyp, '{ts escape="sql"}General Protection{/ts}' , 3, 'General Protection', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL, @domainID, NULL),
(@option_group_id_grantTyp, '{ts escape="sql"}Impunity{/ts}' , 4, 'Impunity' , NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL, @domainID, NULL),
-- grant program status
(@option_group_id_grantProgramSt, '{ts escape="sql"}Accepting Applications{/ts}' , 1, 'Accepting Applications' , NULL, 0, 1, 1, NULL, 0, 0, 1, NULL, @domainID, NULL),
(@option_group_id_grantProgramSt, '{ts escape="sql"}Trial Allocation{/ts}' , 2, 'Trial Allocation' , NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL, @domainID, NULL),
(@option_group_id_grantProgramSt, '{ts escape="sql"}Allocation Finalized{/ts}' , 3, 'Allocation Finalized', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL, @domainID, NULL),
-- grant program alocation algorithm
(@option_group_id_allocationAlgo, '{ts escape="sql"}Over Threshold, Percentage of Request Funded{/ts}' , 1, 'Over Threshold, Percentage of Request Funded' , NULL, 0, 1, 1, NULL, 0, 0, 1, NULL, @domainID, NULL),
(@option_group_id_allocationAlgo, '{ts escape="sql"}Best to Worst, Fully Funded{/ts}' , 2, 'Best to Worst, Fully Funded' , NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL, @domainID, NULL),
-- Mail Approval Status Preferences
(@option_group_id_mail_approval_status, '{ts escape="sql"}Approved{/ts}' , 1, 'Approved', NULL, 0, 1, 1, NULL, 0, 1, 1, @mailCompId, @domainID, NULL),
(@option_group_id_mail_approval_status, '{ts escape="sql"}Rejected{/ts}' , 2, 'Rejected', NULL, 0, 0, 2, NULL, 0, 1, 1, @mailCompId, @domainID, NULL),
(@option_group_id_mail_approval_status, '{ts escape="sql"}None{/ts}' , 3, 'None', NULL, 0, 0, 3, NULL, 0, 1, 1, @mailCompId, @domainID, NULL),
-- custom group objects
(@option_group_id_cgeo, '{ts escape="sql"}Survey{/ts}', 'Survey', 'civicrm_survey', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL, NULL, NULL),
(@option_group_id_cgeo, '{ts escape="sql"}Cases{/ts}', 'Case', 'civicrm_case', NULL, 0, NULL, 2, 'CRM_Case_PseudoConstant::caseType;', 0, 0, 1, NULL, NULL, NULL);
-- CRM-6138
{include file='languages.tpl'}
-- /*******************************************************
-- *
-- * Encounter Medium Option Values (for case activities)
-- *
-- *******************************************************/
INSERT INTO `civicrm_option_group` (name, title, description, is_reserved, is_active)
VALUES ('encounter_medium', 'Encounter Medium', 'Encounter medium for case activities (e.g. In Person, By Phone, etc.)', 1, 1);
SELECT @option_group_id_medium := max(id) from civicrm_option_group where name = 'encounter_medium';
INSERT INTO
`civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
VALUES
(@option_group_id_medium, '{ts escape="sql"}In Person{/ts}', 1, 'in_person', NULL, 0, 0, 1, NULL, 0, 1, 1),
(@option_group_id_medium, '{ts escape="sql"}Phone{/ts}', 2, 'phone', NULL, 0, 1, 2, NULL, 0, 1, 1),
(@option_group_id_medium, '{ts escape="sql"}Email{/ts}', 3, 'email', NULL, 0, 0, 3, NULL, 0, 1, 1),
(@option_group_id_medium, '{ts escape="sql"}Fax{/ts}', 4, 'fax', NULL, 0, 0, 4, NULL, 0, 1, 1),
(@option_group_id_medium, '{ts escape="sql"}Letter Mail{/ts}', 5, 'letter_mail', NULL, 0, 0, 5, NULL, 0, 1, 1);
-- sample membership status entries
INSERT INTO
civicrm_membership_status(name, label, start_event, start_event_adjust_unit, start_event_adjust_interval, end_event, end_event_adjust_unit, end_event_adjust_interval, is_current_member, is_admin, weight, is_default, is_active, is_reserved)
VALUES
('New', '{ts escape="sql"}New{/ts}', 'join_date', null, null,'join_date','month',3, 1, 0, 1, 0, 1, 0),
('Current', '{ts escape="sql"}Current{/ts}', 'start_date', null, null,'end_date', null, null, 1, 0, 2, 1, 1, 0),
('Grace', '{ts escape="sql"}Grace{/ts}', 'end_date', null, null,'end_date','month', 1, 1, 0, 3, 0, 1, 0),
('Expired', '{ts escape="sql"}Expired{/ts}', 'end_date', 'month', 1, null, null, null, 0, 0, 4, 0, 1, 0),
('Pending', '{ts escape="sql"}Pending{/ts}', 'join_date', null, null,'join_date',null,null, 0, 0, 5, 0, 1, 1),
('Cancelled', '{ts escape="sql"}Cancelled{/ts}', 'join_date', null, null,'join_date',null,null, 0, 0, 6, 0, 1, 0),
('Deceased', '{ts escape="sql"}Deceased{/ts}', null, null, null, null, null, null, 0, 1, 7, 0, 1, 1);
INSERT INTO `civicrm_preferences_date`
(name, start, end, date_format, time_format, description)
VALUES
( 'activityDate' , 20, 10, '', '', 'Date for activities including contributions: receive, receipt, cancel. membership: join, start, renew. case: start, end.' ),
( 'activityDateTime', 20, 10, '', 1, 'Date and time for activity: scheduled. participant: registered.' ),
( 'birth' , 100, 0, '', '', 'Birth and deceased dates. Only year, month and day fields are supported.' ),
( 'creditCard' , 0, 10, 'M Y', '', 'Month and year only for credit card expiration.' ),
( 'custom' , 20, 20, '', '', 'Uses date range passed in by form field. Can pass in a posix date part parameter. Start and end offsets defined here are ignored.'),
( 'mailing' , 0, 1, '', '', 'Date and time. Used for scheduling mailings.' ),
( 'searchDate' , 20, 20, '', '', 'Used in search forms.' );
-- various processor options
--
-- Table structure for table `civicrm_payment_processor_type`
--
INSERT INTO `civicrm_payment_processor_type`
(name, title, description, is_active, is_default, user_name_label, password_label, signature_label, subject_label, class_name, url_site_default, url_api_default, url_recur_default, url_button_default, url_site_test_default, url_api_test_default, url_recur_test_default, url_button_test_default, billing_mode, is_recur )
VALUES
('PayPal_Standard', '{ts escape="sql"}PayPal - Website Payments Standard{/ts}', NULL,1,0,'{ts escape="sql"}Merchant Account Email{/ts}',NULL,NULL,NULL,'Payment_PayPalImpl','https://www.paypal.com/',NULL,'https://www.paypal.com/',NULL,'https://www.sandbox.paypal.com/',NULL,'https://www.sandbox.paypal.com/',NULL,4,1),
('PayPal', '{ts escape="sql"}PayPal - Website Payments Pro{/ts}', NULL,1,0,'{ts escape="sql"}User Name{/ts}','{ts escape="sql"}Password{/ts}','{ts escape="sql"}Signature{/ts}',NULL,'Payment_PayPalImpl','https://www.paypal.com/','https://api-3t.paypal.com/','https://www.paypal.com/','https://www.paypal.com/en_US/i/btn/btn_xpressCheckout.gif','https://www.sandbox.paypal.com/','https://api-3t.sandbox.paypal.com/','https://www.sandbox.paypal.com/','https://www.paypal.com/en_US/i/btn/btn_xpressCheckout.gif',3, 1 ),
('PayPal_Express', '{ts escape="sql"}PayPal - Express{/ts}', NULL,1,0,'{ts escape="sql"}User Name{/ts}','{ts escape="sql"}Password{/ts}','{ts escape="sql"}Signature{/ts}',NULL,'Payment_PayPalImpl','https://www.paypal.com/','https://api-3t.paypal.com/',NULL,'https://www.paypal.com/en_US/i/btn/btn_xpressCheckout.gif','https://www.sandbox.paypal.com/','https://api-3t.sandbox.paypal.com/',NULL,'https://www.paypal.com/en_US/i/btn/btn_xpressCheckout.gif',2,NULL),
('Google_Checkout', '{ts escape="sql"}Google Checkout{/ts}', NULL,1,0,'{ts escape="sql"}Merchant ID{/ts}','{ts escape="sql"}Key{/ts}',NULL,NULL,'Payment_Google','https://checkout.google.com/',NULL,'https://checkout.google.com/','https://checkout.google.com/buttons/checkout.gif?merchant_id=YOURMERCHANTIDHERE&w=160&h=43&style=white&variant=text&loc=en_US','https://sandbox.google.com/checkout/',NULL,'https://sandbox.google.com/checkout/','https://sandbox.google.com/checkout/buttons/checkout.gif?merchant_id=YOURMERCHANTIDHERE&w=160&h=43&style=white&variant=text&loc=en_US',4,1),
('Moneris', '{ts escape="sql"}Moneris{/ts}', NULL,1,0,'{ts escape="sql"}User Name{/ts}','{ts escape="sql"}Password{/ts}','{ts escape="sql"}Store ID{/ts}',NULL,'Payment_Moneris','https://www3.moneris.com/',NULL,NULL,NULL,'https://esqa.moneris.com/',NULL,NULL,NULL,1,1),
('AuthNet', '{ts escape="sql"}Authorize.Net{/ts}', NULL,1,0,'{ts escape="sql"}API Login{/ts}','{ts escape="sql"}Payment Key{/ts}','{ts escape="sql"}MD5 Hash{/ts}',NULL,'Payment_AuthorizeNet','https://secure.authorize.net/gateway/transact.dll',NULL,'https://api.authorize.net/xml/v1/request.api',NULL,'https://test.authorize.net/gateway/transact.dll',NULL,'https://apitest.authorize.net/xml/v1/request.api',NULL,1,1),
('PayJunction', '{ts escape="sql"}PayJunction{/ts}', NULL,1,0,'User Name','Password',NULL,NULL,'Payment_PayJunction','https://payjunction.com/quick_link',NULL,NULL,NULL,'https://www.payjunctionlabs.com/quick_link',NULL,NULL,NULL,1,1),
('eWAY', '{ts escape="sql"}eWAY (Single Currency){/ts}', NULL,1,0,'Customer ID',NULL,NULL,NULL,'Payment_eWAY','https://www.eway.com.au/gateway_cvn/xmlpayment.asp',NULL,NULL,NULL,'https://www.eway.com.au/gateway_cvn/xmltest/testpage.asp',NULL,NULL,NULL,1,0),
('Payment_Express', '{ts escape="sql"}DPS Payment Express{/ts}', NULL,1,0,'User ID','Key','Mac Key - pxaccess only',NULL,'Payment_PaymentExpress','https://www.paymentexpress.com/pleaseenteraurl',NULL,NULL,NULL,'https://www.paymentexpress.com/pleaseenteratesturl',NULL,NULL,NULL,4,0),
('Dummy', '{ts escape="sql"}Dummy Payment Processor{/ts}',NULL,1,1,'{ts escape="sql"}User Name{/ts}',NULL,NULL,NULL,'Payment_Dummy',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1),
('Elavon', '{ts escape="sql"}Elavon Payment Processor{/ts}','{ts escape="sql"}Elavon / Nova Virtual Merchant{/ts}',1,0,'{ts escape="sql"}SSL Merchant ID {/ts}','{ts escape="sql"}SSL User ID{/ts}','{ts escape="sql"}SSL PIN{/ts}',NULL,'Payment_Elavon','https://www.myvirtualmerchant.com/VirtualMerchant/processxml.do',NULL,NULL,NULL,'https://www.myvirtualmerchant.com/VirtualMerchant/processxml.do',NULL,NULL,NULL,1,0),
('Realex', '{ts escape="sql"}Realex Payment{/ts}', NULL,1,0,'Merchant ID', 'Password', NULL, 'Account', 'Payment_Realex', 'https://epage.payandshop.com/epage.cgi', NULL, NULL, NULL, 'https://epage.payandshop.com/epage-remote.cgi', NULL, NULL, NULL, 1, 0 ),
('PayflowPro', '{ts escape="sql"}PayflowPro{/ts}', NULL,1,0,'Vendor ID', 'Password', 'Partner (merchant)', 'User', 'Payment_PayflowPro', 'https://Payflowpro.paypal.com', NULL, NULL, NULL, 'https://pilot-Payflowpro.paypal.com', NULL, NULL, NULL, 1, 0 ),
('FirstData', '{ts escape="sql"}FirstData (aka linkpoint){/ts}', '{ts escape="sql"}FirstData (aka linkpoint){/ts}', 1, 0, 'Store name', 'certificate path', NULL, NULL, 'Payment_FirstData', 'https://secure.linkpt.net', NULL, NULL, NULL, 'https://staging.linkpt.net', NULL, NULL, NULL, 1, NULL);
-- the fuzzy default dedupe rules
-- IndividualSupervised uses hard-coded optimized query (CRM_Dedupe_BAO_QueryBuilder_IndividualSupervised)
INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, used, name, title, is_reserved)
VALUES ('Individual', 20, 'Supervised', 'IndividualSupervised', '{ts escape="sql"}Name and Email (reserved){/ts}', 1);
SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
VALUES (@drgid, 'civicrm_contact', 'first_name', 5),
(@drgid, 'civicrm_contact', 'last_name', 7),
(@drgid, 'civicrm_email' , 'email', 10);
INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, used, name, title, is_reserved)
VALUES ('Organization', 10, 'Supervised', 'OrganizationSupervised', '{ts escape="sql"}Name and Email{/ts}', 0);
SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
VALUES (@drgid, 'civicrm_contact', 'organization_name', 10),
(@drgid, 'civicrm_email' , 'email', 10);
INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, used, name, title, is_reserved)
VALUES ('Household', 10, 'Supervised', 'HouseholdSupervised', '{ts escape="sql"}Name and Email{/ts}', 0);
SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
VALUES (@drgid, 'civicrm_contact', 'household_name', 10),
(@drgid, 'civicrm_email' , 'email', 10);
-- the strict dedupe rules
-- IndividualUnsupervised uses hard-coded optimized query (CRM_Dedupe_BAO_QueryBuilder_IndividualUnsupervised)
INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, used, name, title, is_reserved)
VALUES ('Individual', 10, 'Unsupervised', 'IndividualUnsupervised', '{ts escape="sql"}Email (reserved){/ts}', 1);
SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
VALUES (@drgid, 'civicrm_email', 'email', 10);
INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, used, name, title, is_reserved)
VALUES ('Organization', 10, 'Unsupervised', 'OrganizationUnsupervised', '{ts escape="sql"}Name and Email{/ts}', 0);
SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
VALUES (@drgid, 'civicrm_contact', 'organization_name', 10),
(@drgid, 'civicrm_email' , 'email', 10);
INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, used, name, title, is_reserved)
VALUES ('Household', 10, 'Unsupervised', 'HouseholdUnsupervised', '{ts escape="sql"}Name and Email{/ts}', 0);
SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
VALUES (@drgid, 'civicrm_contact', 'household_name', 10),
(@drgid, 'civicrm_email' , 'email', 10);
-- IndividualGeneral uses hard-coded optimized query (CRM_Dedupe_BAO_QueryBuilder_IndividualGeneral)
INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, used, name, title, is_reserved)
VALUES ('Individual', 15, 'General', 'IndividualGeneral', '{ts escape="sql"}Name and Address (reserved){/ts}', 1);
SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
VALUES (@drgid, 'civicrm_contact', 'first_name', '5'),
(@drgid, 'civicrm_contact', 'last_name', '5'),
(@drgid, 'civicrm_address', 'street_address', '5'),
(@drgid, 'civicrm_contact', 'middle_name', '1'),
(@drgid, 'civicrm_contact', 'suffix_id', '1');
-- Sample counties (state-province and country lists defined in a separate tpl files)
INSERT INTO civicrm_county (name, state_province_id) VALUES ('Alameda', 1004);
INSERT INTO civicrm_county (name, state_province_id) VALUES ('Contra Costa', 1004);
INSERT INTO civicrm_county (name, state_province_id) VALUES ('Marin', 1004);
INSERT INTO civicrm_county (name, state_province_id) VALUES ('San Francisco', 1004);
INSERT INTO civicrm_county (name, state_province_id) VALUES ('San Mateo', 1004);
INSERT INTO civicrm_county (name, state_province_id) VALUES ('Santa Clara', 1004);
-- Bounce classification patterns
INSERT INTO civicrm_mailing_bounce_type
(name, description, hold_threshold)
VALUES ('AOL', '{ts escape="sql"}AOL Terms of Service complaint{/ts}', 1);
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'AOL';
INSERT INTO civicrm_mailing_bounce_pattern
(bounce_type_id, pattern)
VALUES
(@bounceTypeID, 'Client TOS Notification');
INSERT INTO civicrm_mailing_bounce_type
(name, description, hold_threshold)
VALUES ('Away', '{ts escape="sql"}Recipient is on vacation{/ts}', 30);
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Away';
INSERT INTO civicrm_mailing_bounce_pattern
(bounce_type_id, pattern)
VALUES
(@bounceTypeID, '(be|am)? (out of|away from) (the|my)? (office|computer|town)'),
(@bounceTypeID, 'i am on vacation');
INSERT INTO civicrm_mailing_bounce_type
(name, description, hold_threshold)
VALUES ('Dns', '{ts escape="sql"}Unable to resolve recipient domain{/ts}', 3);
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Dns';
INSERT INTO civicrm_mailing_bounce_pattern
(bounce_type_id, pattern)
VALUES
(@bounceTypeID, 'name(server entry| lookup failure)'),
(@bounceTypeID, 'no (mail server|matches to nameserver query|dns entries)'),
(@bounceTypeID, 'reverse dns entry'),
(@bounceTypeID, 'Host or domain name not found');
INSERT INTO civicrm_mailing_bounce_type
(name, description, hold_threshold)
VALUES ('Host', '{ts escape="sql"}Unable to deliver to destintation mail server{/ts}', 3);
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Host';
INSERT INTO civicrm_mailing_bounce_pattern
(bounce_type_id, pattern)
VALUES
(@bounceTypeID, '(unknown|not local) host'),
(@bounceTypeID, 'all hosts have been failing'),
(@bounceTypeID, 'allowed rcpthosts'),
(@bounceTypeID, 'connection (refused|timed out)'),
(@bounceTypeID, 'not connected'),
(@bounceTypeID, 'couldn\'t find any host named'),
(@bounceTypeID, 'error involving remote host'),
(@bounceTypeID, 'host unknown'),
(@bounceTypeID, 'invalid host name'),
(@bounceTypeID, 'isn\'t in my control/locals file'),
(@bounceTypeID, 'local configuration error'),
(@bounceTypeID, 'not a gateway'),
(@bounceTypeID, 'server is down or unreachable'),
(@bounceTypeID, 'too many connections'),
(@bounceTypeID, 'unable to connect'),
(@bounceTypeID, 'lost connection'),
(@bounceTypeID, 'conversation with [^ ]* timed out while');
INSERT INTO civicrm_mailing_bounce_type
(name, description, hold_threshold)
VALUES ('Inactive', '{ts escape="sql"}User account is no longer active{/ts}', 1);
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Inactive';
INSERT INTO civicrm_mailing_bounce_pattern
(bounce_type_id, pattern)
VALUES
(@bounceTypeID, '(my )?e-?mail( address)? has changed'),
(@bounceTypeID, 'account (inactive|expired|deactivated)'),
(@bounceTypeID, 'account is locked'),
(@bounceTypeID, 'changed \w+( e-?mail)? address'),
(@bounceTypeID, 'deactivated mailbox'),
(@bounceTypeID, 'disabled or discontinued'),
(@bounceTypeID, 'inactive user'),
(@bounceTypeID, 'is inactive on this domain'),
(@bounceTypeID, 'mail receiving disabled'),
(@bounceTypeID, 'mail( ?)address is administrative?ly disabled'),
(@bounceTypeID, 'mailbox (temporarily disabled|currently suspended)'),
(@bounceTypeID, 'no longer (accepting mail|on server|in use|with|employed|on staff|works for|using this account)'),
(@bounceTypeID, 'not accepting mail'),
(@bounceTypeID, 'please use my new e-?mail address'),
(@bounceTypeID, 'this address no longer accepts mail'),
(@bounceTypeID, 'user account suspended');
INSERT INTO civicrm_mailing_bounce_type
(name, description, hold_threshold)
VALUES ('Invalid', '{ts escape="sql"}Email address is not valid{/ts}', 1);
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Invalid';
INSERT INTO civicrm_mailing_bounce_pattern
(bounce_type_id, pattern)
VALUES
(@bounceTypeID, '(user|recipient( name)?) is not recognized'),
(@bounceTypeID, '554 delivery error'),
(@bounceTypeID, 'address does not exist'),
(@bounceTypeID, 'address(es)? could not be found'),
(@bounceTypeID, 'addressee unknown'),
(@bounceTypeID, 'bad destination'),
(@bounceTypeID, 'badly formatted address'),
(@bounceTypeID, 'can\'t open mailbox for'),
(@bounceTypeID, 'cannot deliver'),
(@bounceTypeID, 'delivery to the following recipient(s)? failed'),
(@bounceTypeID, 'destination addresses were unknown'),
(@bounceTypeID, 'did not reach the following recipient'),
(@bounceTypeID, 'does not exist'),
(@bounceTypeID, 'does not like recipient'),
(@bounceTypeID, 'does not specify a valid notes mail file'),
(@bounceTypeID, 'illegal alias'),
(@bounceTypeID, 'invalid (mailbox|(e-?mail )?address|recipient|final delivery)'),
(@bounceTypeID, 'invalid( or unknown)?( virtual)? user'),
(@bounceTypeID, 'mail delivery to this user is not allowed'),
(@bounceTypeID, 'mailbox (not found|unavailable|name not allowed)'),
(@bounceTypeID, 'message could not be forwarded'),
(@bounceTypeID, 'missing or malformed local(-| )part'),
(@bounceTypeID, 'no e-?mail address registered'),
(@bounceTypeID, 'no such (mail drop|mailbox( \w+)?|(e-?mail )?address|recipient|(local )?user)( here)?'),
(@bounceTypeID, 'no mailbox here by that name'),
(@bounceTypeID, 'not (listed in|found in directory|known at this site|our customer)'),
(@bounceTypeID, 'not a valid( (user|mailbox))?'),
(@bounceTypeID, 'not present in directory entry'),
(@bounceTypeID, 'recipient (does not exist|(is )?unknown)'),
(@bounceTypeID, 'this user doesn\'t have a yahoo.com address'),
(@bounceTypeID, 'unavailable to take delivery of the message'),
(@bounceTypeID, 'unavailable mailbox'),
(@bounceTypeID, 'unknown (local( |-)part|recipient)'),
(@bounceTypeID, 'unknown( or illegal)? user( account)?'),
(@bounceTypeID, 'unrecognized recipient'),
(@bounceTypeID, 'unregistered address'),
(@bounceTypeID, 'user (unknown|does not exist)'),
(@bounceTypeID, 'user doesn\'t have an? \w+ account'),
(@bounceTypeID, 'user(\'s e-?mail name is)? not found'),
(@bounceTypeID, '^Validation failed for:');
INSERT INTO civicrm_mailing_bounce_type
(name, description, hold_threshold)
VALUES ('Loop', '{ts escape="sql"}Mail routing error{/ts}', 3);
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Loop';
INSERT INTO civicrm_mailing_bounce_pattern
(bounce_type_id, pattern)
VALUES
(@bounceTypeID, '(mail|routing) loop'),
(@bounceTypeID, 'excessive recursion'),
(@bounceTypeID, 'loop detected'),
(@bounceTypeID, 'maximum hop count exceeded'),
(@bounceTypeID, 'message was forwarded more than the maximum allowed times'),
(@bounceTypeID, 'too many hops');
INSERT INTO civicrm_mailing_bounce_type
(name, description, hold_threshold)
VALUES ('Quota', '{ts escape="sql"}User inbox is full{/ts}', 3);
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Quota';
INSERT INTO civicrm_mailing_bounce_pattern
(bounce_type_id, pattern)
VALUES
(@bounceTypeID, '(disk|over the allowed|exceed(ed|s)?|storage) quota'),
(@bounceTypeID, '522_mailbox_full'),
(@bounceTypeID, 'exceeds allowed message count'),
(@bounceTypeID, 'file too large'),
(@bounceTypeID, 'full mailbox'),
(@bounceTypeID, 'mailbox ((for user \w+ )?is )?full'),
(@bounceTypeID, 'mailbox has exceeded the limit'),
(@bounceTypeID, 'mailbox( exceeds allowed)? size'),
(@bounceTypeID, 'no space left for this user'),
(@bounceTypeID, 'over\\s?quota'),
(@bounceTypeID, 'quota (for the mailbox )?has been exceeded'),
(@bounceTypeID, 'quota (usage|violation|exceeded)'),
(@bounceTypeID, 'recipient storage full'),
(@bounceTypeID, 'not able to receive more mail');
INSERT INTO civicrm_mailing_bounce_type
(name, description, hold_threshold)
VALUES ('Relay', '{ts escape="sql"}Unable to reach destination mail server{/ts}', 3);
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Relay';
INSERT INTO civicrm_mailing_bounce_pattern
(bounce_type_id, pattern)
VALUES
(@bounceTypeID, 'cannot find your hostname'),
(@bounceTypeID, 'ip name lookup'),
(@bounceTypeID, 'not configured to relay mail'),
(@bounceTypeID, 'relay (not permitted|access denied)'),
(@bounceTypeID, 'relayed mail to .+? not allowed'),
(@bounceTypeID, 'sender ip must resolve'),
(@bounceTypeID, 'unable to relay'),
(@bounceTypeID, 'No route to host'),
(@bounceTypeID, 'Network is unreachable');
INSERT INTO civicrm_mailing_bounce_type
(name, description, hold_threshold)
VALUES ('Spam', '{ts escape="sql"}Message caught by a content filter{/ts}', 1);
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Spam';
INSERT INTO civicrm_mailing_bounce_pattern
(bounce_type_id, pattern)
VALUES
(@bounceTypeID, '(bulk( e-?mail)|content|attachment blocking|virus|mail system) filters?'),
(@bounceTypeID, '(hostile|questionable|unacceptable) content'),
(@bounceTypeID, 'address .+? has not been verified'),
(@bounceTypeID, 'anti-?spam (polic\w+|software)'),
(@bounceTypeID, 'anti-?virus gateway has detected'),
(@bounceTypeID, 'blacklisted'),
(@bounceTypeID, 'blocked message'),
(@bounceTypeID, 'content control'),
(@bounceTypeID, 'delivery not authorized'),
(@bounceTypeID, 'does not conform to our e-?mail policy'),
(@bounceTypeID, 'excessive spam content'),
(@bounceTypeID, 'message looks suspicious'),
(@bounceTypeID, 'open relay'),
(@bounceTypeID, 'sender was rejected'),
(@bounceTypeID, 'spam(check| reduction software| filters?)'),
(@bounceTypeID, 'blocked by a user configured filter'),
(@bounceTypeID, 'detected as spam');
INSERT INTO civicrm_mailing_bounce_type
(name, description, hold_threshold)
VALUES ('Syntax', '{ts escape="sql"}Error in SMTP transaction{/ts}', 3);
SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Syntax';
INSERT INTO civicrm_mailing_bounce_pattern
(bounce_type_id, pattern)
VALUES
(@bounceTypeID, 'nonstandard smtp line terminator'),
(@bounceTypeID, 'syntax error in from address'),
(@bounceTypeID, 'unknown smtp code');
-- add sample and reserved profiles
INSERT INTO civicrm_uf_group
(id, name, group_type, title, is_cms_user, is_reserved, help_post) VALUES
(1, 'name_and_address', 'Individual,Contact', '{ts escape="sql"}Name and Address{/ts}', 0, 0, NULL),
(2, 'supporter_profile', 'Individual,Contact', '{ts escape="sql"}Supporter Profile{/ts}', 2, 0, '{ts escape="sql"}The information you provide will NOT be shared with any third party organisations.{/ts}
{ts escape="sql"}Thank you for getting involved in our campaign!{/ts}
'),
(3, 'participant_status', 'Participant', '{ts escape="sql"}Participant Status{/ts}', 0, 1, NULL),
(4, 'new_individual', 'Individual,Contact', '{ts escape="sql"}New Individual{/ts}' , 0, 1, NULL),
(5, 'new_organization', 'Organization,Contact','{ts escape="sql"}New Organization{/ts}' , 0, 1, NULL),
(6, 'new_household', 'Household,Contact', '{ts escape="sql"}New Household{/ts}' , 0, 1, NULL),
(7, 'summary_overlay', 'Contact', '{ts escape="sql"}Summary Overlay{/ts}' , 0, 1, NULL),
(8, 'shared_address', 'Contact', '{ts escape="sql"}Shared Address{/ts}' , 0, 1, NULL),
(9, 'on_behalf_organization', 'Contact,Organization','{ts escape="sql"}On Behalf Of Organization{/ts}', 0, 1, NULL),
(10, 'contribution_batch_entry', 'Contribution', '{ts escape="sql"}Contribution Bulk Entry{/ts}' , 0, 1, NULL),
(11, 'membership_batch_entry', 'Membership', '{ts escape="sql"}Membership Bulk Entry{/ts}' , 0, 1, NULL),
(12, 'event_registration', 'Individual, Contact', '{ts escape="sql"}Your Registration Info{/ts}', 0, 0, NULL);
INSERT INTO civicrm_uf_join
(is_active,module,entity_table,entity_id,weight,uf_group_id)
VALUES
(1, 'User Registration',NULL, NULL,1,1),
(1, 'User Account', NULL, NULL, 1, 1),
(1, 'Profile', NULL, NULL, 1, 1),
(1, 'Profile', NULL, NULL, 2, 2),
(1, 'Profile', NULL, NULL, 3, 4),
(1, 'Profile', NULL, NULL, 4, 5),
(1, 'Profile', NULL, NULL, 5, 6),
(1, 'Profile', NULL, NULL, 6, 7),
(1, 'Profile', NULL, NULL, 7, 8),
(1, 'Profile', NULL, NULL, 8, 9),
(1, 'Profile', NULL, NULL, 9, 10),
(1, 'Profile', NULL, NULL, 9, 11),
(1, 'Profile', NULL, NULL, 9, 12);
INSERT INTO civicrm_uf_field
( uf_group_id, field_name, is_required, is_reserved, weight, visibility, in_selector, is_searchable, location_type_id, label, field_type, help_post, phone_type_id ) VALUES
( 1, 'first_name', 1, 0, 1, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}First Name{/ts}', 'Individual', NULL, NULL),
( 1, 'last_name', 1, 0, 2, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Last Name{/ts}', 'Individual', NULL, NULL),
( 1, 'street_address', 0, 0, 3, 'User and User Admin Only', 0, 0, 1, '{ts escape="sql"}Street Address (Home){/ts}', 'Contact', NULL, NULL),
( 1, 'city', 0, 0, 4, 'User and User Admin Only', 0, 0, 1, '{ts escape="sql"}City (Home){/ts}', 'Contact', NULL, NULL),
( 1, 'postal_code', 0, 0, 5, 'User and User Admin Only', 0, 0, 1, '{ts escape="sql"}Postal Code (Home){/ts}', 'Contact', NULL, NULL),
( 1, 'country', 0, 0, 6, 'User and User Admin Only', 0, 0, 1, '{ts escape="sql"}Country (Home){/ts}', 'Contact', NULL, NULL),
( 1, 'state_province', 0, 0, 7, 'User and User Admin Only', 0, 0, 1, '{ts escape="sql"}State (Home){/ts}', 'Contact', NULL, NULL),
( 2, 'first_name', 1, 0, 1, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}First Name{/ts}', 'Individual', NULL, NULL),
( 2, 'last_name', 1, 0, 2, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Last Name{/ts}', 'Individual', NULL, NULL),
( 2, 'email', 1, 0, 3, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Email Address{/ts}', 'Contact', NULL, NULL),
( 3, 'participant_status', 1, 1, 1, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Participant Status{/ts}', 'Participant', NULL, NULL),
( 4, 'first_name', 1, 0, 1, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}First Name{/ts}', 'Individual', NULL, NULL),
( 4, 'last_name', 1, 0, 2, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Last Name{/ts}', 'Individual', NULL, NULL),
( 4, 'email', 0, 0, 3, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Email Address{/ts}', 'Contact', NULL, NULL),
( 5, 'organization_name', 1, 0, 2, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Organization Name{/ts}', 'Organization',NULL, NULL),
( 5, 'email', 0, 0, 3, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Email Address{/ts}', 'Contact', NULL, NULL),
( 6, 'household_name', 1, 0, 2, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Household Name{/ts}', 'Household', NULL, NULL),
( 6, 'email', 0, 0, 3, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Email Address{/ts}', 'Contact', NULL, NULL),
( 7, 'phone' ,1 ,0 ,1 ,'User and User Admin Only' ,0 ,0 ,1 ,'{ts escape="sql"}Home Phone{/ts}' ,'Contact' ,NULL, 1 ),
( 7, 'phone' ,1 ,0 ,2 ,'User and User Admin Only' ,0 ,0 ,1 ,'{ts escape="sql"}Home Mobile{/ts}' ,'Contact' ,NULL, 2 ),
( 7, 'street_address', 1, 0, 3, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Primary Address{/ts}', 'Contact', NULL, NULL),
( 7, 'city', 1, 0, 4, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}City{/ts}', 'Contact', NULL, NULL),
( 7, 'state_province', 1, 0, 5, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}State{/ts}', 'Contact', NULL, NULL),
( 7, 'postal_code', 1, 0, 6, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Postal Code{/ts}', 'Contact', NULL, NULL),
( 7, 'email', 1, 0, 7, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Primary Email{/ts}', 'Contact', NULL, NULL),
( 7, 'group', 1, 0, 8, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Groups{/ts}', 'Contact', NULL, NULL),
( 7, 'tag', 1, 0, 9, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Tags{/ts}', 'Contact', NULL, NULL),
( 7, 'gender' ,1 ,0 ,10 ,'User and User Admin Only' ,0 ,0 ,NULL, '{ts escape="sql"}Gender{/ts}' ,'Individual' ,NULL, NULL),
( 7, 'birth_date' ,1 ,0 ,11 ,'User and User Admin Only' ,0 ,0 ,NULL, '{ts escape="sql"}Date of Birth{/ts}' ,'Individual' ,NULL, NULL),
( 8, 'street_address', 1, 1, 1, 'User and User Admin Only', 0, 0, 1, '{ts escape="sql"}Street Address (Home){/ts}', 'Contact', NULL, NULL),
( 8, 'city', 1, 1, 2, 'User and User Admin Only', 0, 0, 1, '{ts escape="sql"}City (Home){/ts}', 'Contact', NULL, NULL),
( 8, 'postal_code', 0, 0, 3, 'User and User Admin Only', 0, 0, 1, '{ts escape="sql"}Postal Code (Home){/ts}', 'Contact', NULL, NULL),
( 8, 'country', 0, 0, 4, 'User and User Admin Only', 0, 0, 1, '{ts escape="sql"}Country (Home){/ts}', 'Contact', NULL, NULL),
( 8, 'state_province', 0, 0, 5, 'User and User Admin Only', 0, 0, 1, '{ts escape="sql"}State (Home){/ts}', 'Contact', NULL, NULL),
( 9, 'organization_name', 1, 0, 1, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Organization Name{/ts}', 'Organization',NULL, NULL),
( 9, 'phone', 1, 0, 2, 'User and User Admin Only', 0, 0, 3, '{ts escape="sql"}Phone (Main) {/ts}', 'Contact', NULL, 1),
( 9, 'email', 1, 0, 3, 'User and User Admin Only', 0, 0, 3, '{ts escape="sql"}Email (Main) {/ts}', 'Contact', NULL, NULL),
( 9, 'street_address', 1, 0, 4, 'User and User Admin Only', 0, 0, 3, '{ts escape="sql"}Street Address{/ts}', 'Contact', NULL, NULL),
( 9, 'city', 1, 0, 5, 'User and User Admin Only', 0, 0, 3, '{ts escape="sql"}City{/ts}', 'Contact', NULL, NULL),
( 9, 'postal_code', 1, 0, 6, 'User and User Admin Only', 0, 0, 3, '{ts escape="sql"}Postal Code{/ts}', 'Contact', NULL, NULL),
( 9, 'country', 1, 0, 7, 'User and User Admin Only', 0, 0, 3, '{ts escape="sql"}Country{/ts}', 'Contact', NULL, NULL),
( 9, 'state_province', 1, 0, 8, 'User and User Admin Only', 0, 0, 3, '{ts escape="sql"}State / Province{/ts}', 'Contact', NULL, NULL),
( 10, 'financial_type', 1, 1, 1, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Type{/ts}', 'Contribution', NULL, NULL ),
( 10, 'total_amount', 1, 1, 2, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Amount{/ts}', 'Contribution', NULL, NULL ),
( 10, 'contribution_status_id', 1, 1, 3, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Status{/ts}', 'Contribution', NULL, NULL ),
( 10, 'receive_date', 1, 1, 4, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Received{/ts}', 'Contribution', NULL, NULL ),
( 10, 'contribution_source', 0, 0, 5, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Source{/ts}', 'Contribution', NULL, NULL ),
( 10, 'payment_instrument', 0, 0, 6, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Paid By{/ts}', 'Contribution', NULL, NULL ),
( 10, 'check_number', 0, 0, 7, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Check Number{/ts}', 'Contribution', NULL, NULL ),
( 10, 'send_receipt', 0, 0, 8, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Send Receipt{/ts}', 'Contribution', NULL, NULL ),
( 10, 'invoice_id', 0, 0, 9, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Invoice ID{/ts}', 'Contribution', NULL, NULL ),
( 11, 'membership_type', 1, 1, 1, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Type{/ts}', 'Membership', NULL, NULL ),
( 11, 'join_date', 1, 1, 2, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Member Since{/ts}', 'Membership', NULL, NULL ),
( 11, 'membership_start_date', 0, 1, 3, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Start Date{/ts}', 'Membership', NULL, NULL ),
( 11, 'membership_end_date', 0, 1, 4, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}End Date{/ts}', 'Membership', NULL, NULL ),
( 11, 'membership_source', 0, 0, 5, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Source{/ts}', 'Membership', NULL, NULL ),
( 11, 'send_receipt', 0, 0, 6, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Send Receipt{/ts}', 'Membership', NULL, NULL ),
( 11, 'financial_type', 1, 1, 7, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Financial Type{/ts}', 'Membership', NULL, NULL ),
( 11, 'total_amount', 1, 1, 8, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Amount{/ts}', 'Membership', NULL, NULL ),
( 11, 'receive_date', 1, 1, 9, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Received{/ts}', 'Membership', NULL, NULL ),
( 11, 'payment_instrument', 0, 0, 10, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Paid By{/ts}', 'Membership', NULL, NULL ),
( 11, 'check_number', 0, 0, 11, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Check Number{/ts}', 'Membership', NULL, NULL ),
( 11, 'contribution_status_id', 1, 1, 12, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Payment Status{/ts}', 'Membership', NULL, NULL ),
( 12, 'email', 1, 0, 1, 'User and User Admin Only', 0, 0, NULL, '{ts escape="sql"}Email Address{/ts}', 'Contact', NULL, NULL);
INSERT INTO civicrm_participant_status_type
(id, name, label, class, is_reserved, is_active, is_counted, weight, visibility_id) VALUES
(1, 'Registered', '{ts escape="sql"}Registered{/ts}', 'Positive', 1, 1, 1, 1, 1 ),
(2, 'Attended', '{ts escape="sql"}Attended{/ts}', 'Positive', 0, 1, 1, 2, 2 ),
(3, 'No-show', '{ts escape="sql"}No-show{/ts}', 'Negative', 0, 1, 0, 3, 2 ),
(4, 'Cancelled', '{ts escape="sql"}Cancelled{/ts}', 'Negative', 1, 1, 0, 4, 2 ),
(5, 'Pending from pay later', '{ts escape="sql"}Pending from pay later{/ts}', 'Pending', 1, 1, 1, 5, 2 ),
(6, 'Pending from incomplete transaction', '{ts escape="sql"}Pending from incomplete transaction{/ts}', 'Pending', 1, 1, 0, 6, 2 ),
(7, 'On waitlist', '{ts escape="sql"}On waitlist{/ts}', 'Waiting', 1, 0, 0, 7, 2 ),
(8, 'Awaiting approval', '{ts escape="sql"}Awaiting approval{/ts}', 'Waiting', 1, 0, 1, 8, 2 ),
(9, 'Pending from waitlist', '{ts escape="sql"}Pending from waitlist{/ts}', 'Pending', 1, 0, 1, 9, 2 ),
(10, 'Pending from approval', '{ts escape="sql"}Pending from approval{/ts}', 'Pending', 1, 0, 1, 10, 2 ),
(11, 'Rejected', '{ts escape="sql"}Rejected{/ts}', 'Negative', 1, 0, 0, 11, 2 ),
(12, 'Expired', '{ts escape="sql"}Expired{/ts}', 'Negative', 1, 1, 0, 12, 2 ),
(13, 'Pending in cart', '{ts escape="sql"}Pending in cart{/ts}', 'Pending', 1, 1, 0, 13, 2 );
-- CRM-8150
INSERT INTO civicrm_action_mapping
(entity, entity_value, entity_value_label, entity_status, entity_status_label, entity_date_start, entity_date_end, entity_recipient)
VALUES
( 'civicrm_activity', 'activity_type', 'Activity Type', 'activity_status', 'Activity Status', 'activity_date_time', NULL, 'activity_contacts'),
( 'civicrm_participant', 'event_type', 'Event Type', 'civicrm_participant_status_type', 'Participant Status', 'event_start_date', 'event_end_date', 'event_contacts'),
( 'civicrm_participant', 'civicrm_event', 'Event Name', 'civicrm_participant_status_type', 'Participant Status', 'event_start_date', 'event_end_date', 'event_contacts'),
( 'civicrm_membership', 'civicrm_membership_type', 'Membership Type', 'auto_renew_options', 'Auto Renew Options', 'membership_join_date', 'membership_end_date', NULL),
( 'civicrm_participant', 'event_template', 'Event Template', 'civicrm_participant_status_type', 'Participant Status', 'event_start_date', 'event_end_date', 'event_contacts');
INSERT INTO `civicrm_contact_type`
(`id`, `name`, `label`,`image_URL`, `parent_id`, `is_active`,`is_reserved`)
VALUES
( 1, 'Individual' , '{ts escape="sql"}Individual{/ts}' , NULL, NULL, 1, 1),
( 2, 'Household' , '{ts escape="sql"}Household{/ts}' , NULL, NULL, 1, 1),
( 3, 'Organization', '{ts escape="sql"}Organization{/ts}', NULL, NULL, 1, 1);
{include file='civicrm_msg_template.tpl'}
-- CRM-8358
INSERT INTO `civicrm_job`
( domain_id, run_frequency, last_run, name, description, api_entity, api_action, parameters, is_active )
VALUES
( @domainID, 'Always' , NULL, '{ts escape="sql" skip="true"}Send Scheduled Mailings{/ts}', '{ts escape="sql" skip="true"}Sends out scheduled CiviMail mailings{/ts}', 'job', 'process_mailing', NULL, 0),
( @domainID, 'Hourly' , NULL, '{ts escape="sql" skip="true"}Fetch Bounces{/ts}', '{ts escape="sql" skip="true"}Fetches bounces from mailings and writes them to mailing statistics{/ts}', 'job', 'fetch_bounces', NULL, 0),
( @domainID, 'Hourly' , NULL, '{ts escape="sql" skip="true"}Process Inbound Emails{/ts}', '{ts escape="sql" skip="true"}Inserts activity for a contact or a case by retrieving inbound emails from a mail directory{/ts}', 'job', 'fetch_activities', NULL, 0),
( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Process Pledges{/ts}', '{ts escape="sql" skip="true"}Updates pledge records and sends out reminders{/ts}', 'job', 'process_pledge','{ts escape="sql" skip="true"}send_reminders=[1 or 0] optional- 1 to send payment reminders{/ts}', 0),
( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Geocode and Parse Addresses{/ts}', '{ts escape="sql" skip="true"}Retrieves geocodes (lat and long) and / or parses street addresses (populates street number, street name, etc.){/ts}', 'job', 'geocode', '{ts escape="sql" skip="true"}geocoding=[1 or 0] required
parse=[1 or 0] required
start=[contact ID] optional-begin with this contact ID
end=[contact ID] optional-process contacts with IDs less than this
throttle=[1 or 0] optional-1 adds five second sleep{/ts}', 0),
( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Update Greetings and Addressees{/ts}','{ts escape="sql" skip="true"}Goes through contact records and updates email and postal greetings, or addressee value{/ts}', 'job', 'update_greeting','{ts escape="sql" skip="true"}ct=[Individual or Household or Organization] required
gt=[email_greeting or postal_greeting or addressee] required
force=[0 or 1] optional-0 update contacts with null value, 1 update all{/ts}', 0),
( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Mail Reports{/ts}', '{ts escape="sql" skip="true"}Generates and sends out reports via email{/ts}', 'job', 'mail_report','{ts escape="sql" skip="true"}instanceId=[ID of report instance] required
format=[csv or print] optional-output CSV or print-friendly HTML, else PDF{/ts}', 0),
( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Send Scheduled Reminders{/ts}', '{ts escape="sql" skip="true"}Sends out scheduled reminders via email{/ts}', 'job', 'send_reminder', NULL, 0),
( @domainID, 'Always' , NULL, '{ts escape="sql" skip="true"}Update Participant Statuses{/ts}', '{ts escape="sql" skip="true"}Updates pending event participant statuses based on time{/ts}', 'job', 'process_participant', NULL, 0),
( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Update Membership Statuses{/ts}', '{ts escape="sql" skip="true"}Updates membership statuses. WARNING: Membership renewal reminders have been migrated to the Schedule Reminders functionality, which supports multiple renewal reminders.{/ts}', 'job', 'process_membership', NULL, 0),
( @domainID, 'Always' , NULL, '{ts escape="sql" skip="true"}Process Survey Respondents{/ts}', '{ts escape="sql" skip="true"}Releases reserved survey respondents when they have been reserved for longer than the Release Frequency days specified for that survey.{/ts}', 'job', 'process_respondent',NULL, 0),
( @domainID, 'Hourly' , NULL, '{ts escape="sql" skip="true"}Clean-up Temporary Data and Files{/ts}','{ts escape="sql" skip="true"}Removes temporary data and files, and clears old data from cache tables. Recommend running this job every hour to help prevent database and file system bloat.{/ts}', 'job', 'cleanup', NULL, 0),
( @domainID, 'Always' , NULL, '{ts escape="sql" skip="true"}Send Scheduled SMS{/ts}', '{ts escape="sql" skip="true"}Sends out scheduled SMS{/ts}', 'job', 'process_sms', NULL, 0),
( @domainID, 'Always' , NULL, '{ts escape="sql" skip="true"}Rebuild Smart Group Cache{/ts}', '{ts escape="sql" skip="true"}Rebuilds the smart group cache.{/ts}', 'job', 'group_rebuild', '{ts escape="sql" skip="true"}limit=Number optional-Limit the number of smart groups rebuild{/ts}', 0),
( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Disable expired relationships{/ts}','{ts escape="sql" skip="true"}Disables relationships that have expired (ie. those relationships whose end date is in the past).{/ts}', 'job', 'disable_expired_relationships', NULL, 0),
( @domainID, 'Daily' , NULL, '{ts escape="sql" skip="true"}Validate Email Address from Mailings.{/ts}', '{ts escape="sql" skip="true"}Updates the reset_date on an email address to indicate that there was a valid delivery to this email address.{/ts}', 'mailing', 'update_email_resetdate', '{ts escape="sql" skip="true"}minDays, maxDays=Consider mailings that have completed between minDays and maxDays{/ts}', 0);
SELECT @option_value_rel_id := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Income Account is';
SELECT @option_value_rel_id_exp := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Expense Account is';
SELECT @option_value_rel_id_ar := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Accounts Receivable Account is';
SELECT @option_value_rel_id_as := value FROM civicrm_option_value WHERE option_group_id = @option_group_id_arel AND name = 'Asset Account is';
SELECT @financial_type_id_dtn := max(id) FROM civicrm_financial_type WHERE name = '{ts escape="sql"}Donation{/ts}';
SELECT @financial_type_id_md := max(id) FROM civicrm_financial_type WHERE name = '{ts escape="sql"}Member Dues{/ts}';
SELECT @financial_type_id_cc := max(id) FROM civicrm_financial_type WHERE name = '{ts escape="sql"}Campaign Contribution{/ts}';
SELECT @financial_type_id_ef := max(id) FROM civicrm_financial_type WHERE name = '{ts escape="sql"}Event Fee{/ts}';
SELECT @financial_account_id_dtn := max(id) FROM civicrm_financial_account WHERE name = '{ts escape="sql"}Donation{/ts}';
SELECT @financial_account_id_md := max(id) FROM civicrm_financial_account WHERE name = '{ts escape="sql"}Member Dues{/ts}';
SELECT @financial_account_id_cc := max(id) FROM civicrm_financial_account WHERE name = '{ts escape="sql"}Campaign Contribution{/ts}';
SELECT @financial_account_id_ef := max(id) FROM civicrm_financial_account WHERE name = '{ts escape="sql"}Event Fee{/ts}';
SELECT @financial_account_id_bf := max(id) FROM civicrm_financial_account WHERE name = '{ts escape="sql"}Banking Fees{/ts}';
SELECT @financial_account_id_ap := max(id) FROM civicrm_financial_account WHERE name = '{ts escape="sql"}Accounts Receivable{/ts}';
SELECT @financial_account_id_ar := max(id) FROM civicrm_financial_account WHERE name = '{ts escape="sql"}Deposit Bank Account{/ts}';
SELECT @financial_account_id_pp := max(id) FROM civicrm_financial_account WHERE name = '{ts escape="sql"}Payment Processor Account{/ts}';
INSERT INTO `civicrm_entity_financial_account`
( entity_table, entity_id, account_relationship, financial_account_id )
VALUES
( 'civicrm_financial_type', @financial_type_id_dtn, @option_value_rel_id, @financial_account_id_dtn ),
( 'civicrm_financial_type', @financial_type_id_dtn, @option_value_rel_id_exp, @financial_account_id_bf ),
( 'civicrm_financial_type', @financial_type_id_dtn, @option_value_rel_id_ar, @financial_account_id_ap ),
( 'civicrm_financial_type', @financial_type_id_md, @option_value_rel_id, @financial_account_id_md ),
( 'civicrm_financial_type', @financial_type_id_md, @option_value_rel_id_exp, @financial_account_id_bf ),
( 'civicrm_financial_type', @financial_type_id_md, @option_value_rel_id_ar, @financial_account_id_ap ),
( 'civicrm_financial_type', @financial_type_id_cc, @option_value_rel_id, @financial_account_id_cc ),
( 'civicrm_financial_type', @financial_type_id_cc, @option_value_rel_id_exp, @financial_account_id_bf ),
( 'civicrm_financial_type', @financial_type_id_cc, @option_value_rel_id_ar, @financial_account_id_ap ),
( 'civicrm_financial_type', @financial_type_id_ef, @option_value_rel_id_exp, @financial_account_id_bf ),
( 'civicrm_financial_type', @financial_type_id_ef, @option_value_rel_id_ar, @financial_account_id_ap ),
( 'civicrm_financial_type', @financial_type_id_ef, @option_value_rel_id, @financial_account_id_ef );
-- CRM-11516
INSERT INTO civicrm_entity_financial_account (entity_table, entity_id, account_relationship, financial_account_id)
SELECT 'civicrm_option_value', cov.id, @option_value_rel_id_as, @financial_account_id_ar FROM `civicrm_option_group` cog
LEFT JOIN civicrm_option_value cov ON cog.id = cov.option_group_id
WHERE cog.name = 'payment_instrument' AND cov.name NOT IN ('Credit Card', 'Debit Card');
SELECT @option_value_cc_id := max(id) FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_pi AND `name` = 'Credit Card';
SELECT @option_value_dc_id := max(id) FROM `civicrm_option_value` WHERE `option_group_id` = @option_group_id_pi AND `name` = 'Debit Card';
INSERT INTO `civicrm_entity_financial_account`
( entity_table, entity_id, account_relationship, financial_account_id )
VALUES
( 'civicrm_option_value', @option_value_cc_id, @option_value_rel_id_as, @financial_account_id_pp),
( 'civicrm_option_value', @option_value_dc_id, @option_value_rel_id_as, @financial_account_id_pp);
-- CRM-9714
SELECT @financial_type_id := max(id) FROM `civicrm_financial_type` WHERE `name` = 'Member Dues';
INSERT INTO `civicrm_price_set` ( `name`, `title`, `is_active`, `extends`, `is_quick_config`, `financial_type_id`, `is_reserved` )
VALUES ( 'default_contribution_amount', 'Contribution Amount', '1', '2', '1', NULL,1),
( 'default_membership_type_amount', 'Membership Amount', '1', '3', '1', @financial_type_id,1);
SELECT @setID := max(id) FROM civicrm_price_set WHERE name = 'default_contribution_amount' AND extends = 2 AND is_quick_config = 1 ;
INSERT INTO `civicrm_price_field` (`price_set_id`, `name`, `label`, `html_type`,`weight`, `is_display_amounts`, `options_per_line`, `is_active`, `is_required`,`visibility_id` )
VALUES ( @setID, 'contribution_amount', 'Contribution Amount', 'Text', '1', '1', '1', '1', '1', '1' );
SELECT @fieldID := max(id) FROM civicrm_price_field WHERE name = 'contribution_amount' AND price_set_id = @setID;
INSERT INTO `civicrm_price_field_value` ( `price_field_id`, `name`, `label`, `amount`, `weight`, `is_default`, `is_active`, `financial_type_id`)
VALUES ( @fieldID, 'contribution_amount', 'Contribution Amount', '1', '1', '0', '1', 1);