[Summary view] [Print] [Text view]

   1  /*
   2  If you are upgrading version pre-1.2.0 BETA, CVS snapshot 2003-01-02 (or) 2002-11-15 to this release of InfoCentral,
   3  you should run this SQL file. This must be done for the upgraded files to run correctly.
   4  
   5  THIS SCRIPT IS *ONLY* FOR PEOPLE WHO USED THE OLD pre-1.2.0 BETA WITH THE ABOVE SNAPSHOT DATES!!!
   6  
   7  */
   8  
   9  DROP TABLE IF EXISTS agegroup_agr;
  10  DROP TABLE IF EXISTS groupprop_types;
  11  
  12  ALTER TABLE `user_usr`
  13      ADD `usr_Finance` TINYINT( 3 ) UNSIGNED DEFAULT '0' NOT NULL AFTER `usr_ManageGroups`,
  14      ADD `usr_Communication` TINYINT( 3 ) UNSIGNED DEFAULT '0' NOT NULL AFTER `usr_ManageGroups`,
  15      ADD `usr_SearchLimit` TINYINT( 3 ) DEFAULT '10',
  16      ADD `usr_Style` VARCHAR( 50 ) DEFAULT 'Style.css';
  17  
  18  
  19  ALTER TABLE person2group2role_p2g2r
  20      DROP PRIMARY KEY,
  21      ADD PRIMARY KEY (p2g2r_per_ID,p2g2r_grp_ID);
  22  
  23  ALTER TABLE person_per
  24      ADD per_Title varchar(50) default NULL,
  25      ADD per_Suffix varchar(50) default NULL,
  26      ADD `per_Envelope` smallint(5) UNSIGNED AFTER `per_fam_ID`;
  27  
  28  # Dispense with the silly multiple ID columns in record2property table
  29  ALTER TABLE record2property_r2p
  30      ADD `r2p_record_ID` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL AFTER `r2p_pro_ID` ;
  31  UPDATE record2property_r2p SET r2p_record_ID = r2p_per_ID + r2p_fam_ID + r2p_grp_ID;
  32  ALTER TABLE record2property_r2p DROP r2p_per_ID, DROP r2p_fam_ID, DROP r2p_grp_ID;
  33  
  34  # Add some new queries
  35  
  36  INSERT INTO query_qry VALUES (10, 'SELECT CONCAT(\'<a href=PersonView.php?PersonID=\',per_ID,\' target=view>\', per_FirstName,\' \', per_MiddleName,\' \', per_LastName,\'</a>\') AS Name, CONCAT(\'<a href=DonationView.php?PersonID=\',per_ID,\' target=view>\', \'$\',sum(round(dna_amount,2)),\'</a>\') as Amount\r\nFROM donations_don, person_per\r\nLEFT JOIN donationamounts_dna ON don_ID = dna_don_ID\r\nWHERE don_DonorID = per_ID AND don_date >= \'~startdate~\'\r\nAND don_date <= \'~enddate~\'\r\nGROUP BY don_DonorID\r\nORDER BY per_LastName ASC', 'Total Donations by Member', 'Sum of donations by member for a specific period of time between two dates.', 1);
  37  INSERT INTO query_qry VALUES (11, 'SELECT fun_name as Fund, CONCAT(\'$\',sum(round(dna_amount,2))) as Total\r\nFROM donations_don\r\nLEFT JOIN donationamounts_dna ON donations_don.don_ID = donationamounts_dna.dna_don_ID LEFT JOIN donationfund_fun ON donationamounts_dna.dna_fun_ID = donationfund_fun.fun_ID\r\nWHERE don_date >= \'~startdate~\'\r\nAND don_date <= \'~enddate~\'\r\nGROUP BY fun_id\r\nORDER BY fun_name', 'Total Donations by Fund', 'Sum of donations by FUND for a specific period of time between two dates.', 1);
  38  INSERT INTO query_qry VALUES (15, 'SELECT per_ID as AddToCart, CONCAT(\'<a href=PersonView.php?PersonID=\',per_ID,\'>\',per_FirstName,\' \',per_MiddleName,\' \',per_LastName,\'</a>\') AS Name, \r\nper_City as City, per_State as State,\r\nper_Zip as ZIP, per_HomePhone as HomePhone\r\nFROM person_per \r\nWHERE ~searchwhat~ LIKE \'%~searchstring~%\'', 'Advanced Search', 'Search by any part of Name, City, State, Zip, or Home Phone.', 1);
  39  
  40  UPDATE `query_qry` SET `qry_SQL` = 'SELECT per_ID as AddToCart, CONCAT(\'<a href=PersonView.php?PersonID=\',per_ID,\'>\',per_FirstName,\' \',per_LastName,\'</a>\') AS Name, CONCAT(per_BirthMonth,\'/\',per_BirthDay,\'/\',per_BirthYear) AS \'Birth Date\', YEAR(CURRENT_DATE) - per_BirthYear AS \'Age\' FROM person_per WHERE DATE_ADD(CONCAT(per_BirthYear,\'-\',per_BirthMonth,\'-\',per_BirthDay),INTERVAL ~min~ YEAR) <= CURDATE() AND DATE_ADD(CONCAT(per_BirthYear,\'-\',per_BirthMonth,\'-\',per_BirthDay),INTERVAL ~max~ YEAR) >= CURDATE()' WHERE `qry_ID` = '4' LIMIT 1 ;
  41  
  42  UPDATE `query_qry` SET `qry_Description` = 'Selects person records with the family role and gender specified.' WHERE `qry_ID` = '7' LIMIT 1 ;
  43  
  44  UPDATE `query_qry` SET `qry_SQL` = 'SELECT per_ID as AddToCart, CONCAT(per_FirstName,\' \',per_LastName) AS Name, CONCAT(r2p_Value,\'&nbsp;\') AS Value FROM person_per,record2property_r2p WHERE per_ID = r2p_record_ID AND r2p_pro_ID = ~PropertyID~ ORDER BY per_LastName' WHERE `qry_ID` = '9' LIMIT 1 ;
  45  
  46  INSERT INTO queryparameteroptions_qpo VALUES (5, 15, 'Name', 'CONCAT(per_FirstName,per_MiddleName,per_LastName)');
  47  INSERT INTO queryparameteroptions_qpo VALUES (6, 15, 'Zip Code', 'per_Zip');
  48  INSERT INTO queryparameteroptions_qpo VALUES (7, 15, 'State', 'per_State');
  49  INSERT INTO queryparameteroptions_qpo VALUES (8, 15, 'City', 'per_City');
  50  INSERT INTO queryparameteroptions_qpo VALUES (9, 15, 'Home Phone', 'per_HomePhone');
  51  
  52  INSERT INTO queryparameters_qrp VALUES (9, 10, 2, 'SELECT distinct don_date as Value, don_date as Display FROM donations_don ORDER BY don_date ASC', 'Beginning Date', 'Please select the beginning date to calculate total contributions for each member (i.e. YYYY-MM-DD). NOTE: You can only choose dates that conatain donations.', 'startdate', '1', 1, 0, '0', 0, 0, 0, 0);
  53  INSERT INTO queryparameters_qrp VALUES (10, 10, 2, 'SELECT distinct don_date as Value, don_date as Display FROM donations_don\r\nORDER BY don_date DESC', 'Ending Date', 'Please enter the last date to calculate total contributions for each member (i.e. YYYY-MM-DD).', 'enddate', '1', 1, 0, '', 0, 0, 0, 0);
  54  INSERT INTO queryparameters_qrp VALUES (14, 15, 0, '', 'Search', 'Enter any part of the following: Name, City, State, Zip, or Home Phone.', 'searchstring', '', 1, 0, '', 0, 0, 0, 0);
  55  INSERT INTO queryparameters_qrp VALUES (15, 15, 1, '', 'Field', 'Select field to search for.', 'searchwhat', '1', 1, 0, '', 0, 0, 0, 0);
  56  INSERT INTO queryparameters_qrp VALUES (16, 11, 2, 'SELECT distinct don_date as Value, don_date as Display FROM donations_don ORDER BY don_date ASC', 'Beginning Date', 'Please select the beginning date to calculate total contributions for each member (i.e. YYYY-MM-DD). NOTE: You can only choose dates that conatain donations.', 'startdate', '1', 1, 0, '0', 0, 0, 0, 0);
  57  INSERT INTO queryparameters_qrp VALUES (17, 11, 2, 'SELECT distinct don_date as Value, don_date as Display FROM donations_don\r\nORDER BY don_date DESC', 'Ending Date', 'Please enter the last date to calculate total contributions for each member (i.e. YYYY-MM-DD).', 'enddate', '1', 1, 0, '', 0, 0, 0, 0);
  58  
  59  UPDATE `queryparameters_qrp` SET `qrp_OptionSQL` = 'SELECT lst_OptionID as Value, lst_OptionName as Display FROM list_lst WHERE lst_ID=2 ORDER BY lst_OptionSequence' WHERE `qrp_ID` = '5' LIMIT 1 ;
  60  
  61  ALTER TABLE group_grp
  62        ADD grp_Type tinyint(4) NOT NULL default '0',
  63      ADD `grp_RoleListID` MEDIUMINT UNSIGNED NOT NULL AFTER `grp_Type`,
  64      DROP grp_per_ID;
  65  
  66  ALTER TABLE groupprop_master
  67      CHANGE `propType_ID` `type_ID` smallint(5) unsigned NOT NULL default '0',
  68      ADD prop_PersonDisplay enum('false','true') NOT NULL default 'false';
  69  
  70  CREATE TABLE donations_don (
  71    don_ID mediumint(9) unsigned NOT NULL auto_increment,
  72    don_DonorID mediumint(9) unsigned default NULL,
  73    don_PaymentType tinyint(3) default NULL,
  74    don_CheckNumber mediumint(9) unsigned NOT NULL default '0',
  75    don_Date date NOT NULL default '0000-00-00',
  76    don_Envelope smallint(5) unsigned default NULL,
  77    PRIMARY KEY (don_ID),
  78    KEY don_DonorID (don_DonorID),
  79    KEY don_Date (don_Date)
  80  ) TYPE=MyISAM;
  81  
  82  CREATE TABLE donationamounts_dna (
  83    dna_don_ID mediumint(9) unsigned NOT NULL,
  84    dna_Amount decimal(10,2) default NULL,
  85    dna_fun_ID tinyint(3) unsigned default NULL,
  86    KEY dna_don_ID (dna_don_ID)
  87  ) TYPE=MyISAM;
  88  
  89  CREATE TABLE donationfund_fun (
  90    fun_ID tinyint(3) NOT NULL auto_increment,
  91    fun_Active enum('true','false') NOT NULL default 'true',
  92    fun_Name varchar(30) default NULL,
  93    fun_Description varchar(100) default NULL,
  94    PRIMARY KEY  (fun_ID),
  95    UNIQUE KEY fun_ID (fun_ID)
  96  ) TYPE=MyISAM;
  97  
  98  # Sample data for table `donationfund_fun`
  99  INSERT INTO donationfund_fun VALUES (1, 'true', 'General Donation', 'Default fund: General operating expenses.');
 100  INSERT INTO donationfund_fun VALUES (2, 'true', 'Missions', 'Support for missions.');
 101  INSERT INTO donationfund_fun VALUES (3, 'true', 'Building', 'New building fund.');
 102  
 103  CREATE TABLE person_custom_master (
 104    custom_Order smallint(6) NOT NULL default '0',
 105    custom_Field varchar(5) NOT NULL default '',
 106    custom_Name varchar(40) NOT NULL default '',
 107    custom_Special mediumint(8) unsigned default NULL,
 108    custom_Side enum('left','right') NOT NULL default 'left',
 109    type_ID tinyint(4) NOT NULL default '0'
 110  ) TYPE=MyISAM;
 111  
 112  CREATE TABLE person_custom (
 113    per_ID mediumint(9) NOT NULL default '0',
 114    PRIMARY KEY  (per_ID)
 115  ) TYPE=MyISAM;
 116  
 117  CREATE TABLE list_lst (
 118    lst_ID mediumint(8) unsigned NOT NULL default '0',
 119    lst_OptionID mediumint(8) unsigned NOT NULL default '0',
 120    lst_OptionSequence tinyint(3) unsigned NOT NULL default '0',
 121    lst_OptionName varchar(50) NOT NULL default ''
 122  ) TYPE=MyISAM;
 123  
 124  # Sample data for group types
 125  INSERT INTO list_lst VALUES (3, 1, 1, 'Ministry');
 126  INSERT INTO list_lst VALUES (3, 2, 2, 'Team');
 127  INSERT INTO list_lst VALUES (3, 3, 3, 'Bible Study');
 128  INSERT INTO list_lst VALUES (3, 4, 4, 'Sunday School Class');
 129  
 130  # Insert the custom-field / group-property types
 131  INSERT INTO list_lst VALUES (4, 1, 1, 'True / False');
 132  INSERT INTO list_lst VALUES (4, 2, 2, 'Date');
 133  INSERT INTO list_lst VALUES (4, 3, 3, 'Text Field (50 char)');
 134  INSERT INTO list_lst VALUES (4, 4, 4, 'Text Field (100 char)');
 135  INSERT INTO list_lst VALUES (4, 5, 5, 'Text Field (Long)');
 136  INSERT INTO list_lst VALUES (4, 6, 6, 'Year');
 137  INSERT INTO list_lst VALUES (4, 7, 7, 'Season');
 138  INSERT INTO list_lst VALUES (4, 8, 8, 'Number');
 139  INSERT INTO list_lst VALUES (4, 9, 9, 'Person from Group');
 140  INSERT INTO list_lst VALUES (4, 10, 10, 'Money');
 141  INSERT INTO list_lst VALUES (4, 11, 11, 'Phone Number');
 142  INSERT INTO list_lst VALUES (4, 12, 12, 'Custom Drop-Down List');
 143