[Summary view] [Print] [Text view]

   1  /*
   2  If you are upgrading version pre-1.2.0 BETA, CVS snapshot 2003-02-27 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 DATE!!!
   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_Envelope` smallint(5) UNSIGNED AFTER `per_fam_ID`;
  25  
  26  # Dispense with the silly multiple ID columns in record2property table
  27  ALTER TABLE record2property_r2p
  28      ADD `r2p_record_ID` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL AFTER `r2p_pro_ID` ;
  29  UPDATE record2property_r2p SET r2p_record_ID = r2p_per_ID + r2p_fam_ID + r2p_grp_ID;
  30  ALTER TABLE record2property_r2p DROP r2p_per_ID, DROP r2p_fam_ID, DROP r2p_grp_ID;
  31  
  32  # Add some new queries
  33  
  34  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);
  35  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);
  36  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);
  37  
  38  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 ;
  39  
  40  UPDATE `query_qry` SET `qry_Description` = 'Selects person records with the family role and gender specified.' WHERE `qry_ID` = '7' LIMIT 1 ;
  41  
  42  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 ;
  43  
  44  INSERT INTO queryparameteroptions_qpo VALUES (5, 15, 'Name', 'CONCAT(per_FirstName,per_MiddleName,per_LastName)');
  45  INSERT INTO queryparameteroptions_qpo VALUES (6, 15, 'Zip Code', 'per_Zip');
  46  INSERT INTO queryparameteroptions_qpo VALUES (7, 15, 'State', 'per_State');
  47  INSERT INTO queryparameteroptions_qpo VALUES (8, 15, 'City', 'per_City');
  48  INSERT INTO queryparameteroptions_qpo VALUES (9, 15, 'Home Phone', 'per_HomePhone');
  49  
  50  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);
  51  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);
  52  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);
  53  INSERT INTO queryparameters_qrp VALUES (15, 15, 1, '', 'Field', 'Select field to search for.', 'searchwhat', '1', 1, 0, '', 0, 0, 0, 0);
  54  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);
  55  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);
  56  
  57  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 ;
  58  
  59  ALTER TABLE group_grp
  60        ADD grp_Type tinyint(4) NOT NULL default '0',
  61      ADD `grp_RoleListID` MEDIUMINT UNSIGNED NOT NULL AFTER `grp_Type`,
  62      DROP grp_per_ID;
  63  
  64  ALTER TABLE groupprop_master
  65      CHANGE `propType_ID` `type_ID` smallint(5) unsigned NOT NULL default '0',
  66      ADD prop_PersonDisplay enum('false','true') NOT NULL default 'false';
  67  
  68  CREATE TABLE donations_don (
  69    don_ID mediumint(9) unsigned NOT NULL auto_increment,
  70    don_DonorID mediumint(9) unsigned default NULL,
  71    don_PaymentType tinyint(3) default NULL,
  72    don_CheckNumber mediumint(9) unsigned NOT NULL default '0',
  73    don_Date date NOT NULL default '0000-00-00',
  74    don_Envelope smallint(5) unsigned default NULL,
  75    PRIMARY KEY (don_ID),
  76    KEY don_DonorID (don_DonorID),
  77    KEY don_Date (don_Date)
  78  ) TYPE=MyISAM;
  79  
  80  CREATE TABLE donationamounts_dna (
  81    dna_don_ID mediumint(9) unsigned NOT NULL,
  82    dna_Amount decimal(10,2) default NULL,
  83    dna_fun_ID tinyint(3) unsigned default NULL,
  84    KEY dna_don_ID (dna_don_ID)
  85  ) TYPE=MyISAM;
  86  
  87  CREATE TABLE donationfund_fun (
  88    fun_ID tinyint(3) NOT NULL auto_increment,
  89    fun_Active enum('true','false') NOT NULL default 'true',
  90    fun_Name varchar(30) default NULL,
  91    fun_Description varchar(100) default NULL,
  92    PRIMARY KEY  (fun_ID),
  93    UNIQUE KEY fun_ID (fun_ID)
  94  ) TYPE=MyISAM;
  95  
  96  # Sample data for table `donationfund_fun`
  97  INSERT INTO donationfund_fun VALUES (1, 'true', 'General Donation', 'Default fund: General operating expenses.');
  98  INSERT INTO donationfund_fun VALUES (2, 'true', 'Missions', 'Support for missions.');
  99  INSERT INTO donationfund_fun VALUES (3, 'true', 'Building', 'New building fund.');
 100  
 101  CREATE TABLE person_custom_master (
 102    custom_Order smallint(6) NOT NULL default '0',
 103    custom_Field varchar(5) NOT NULL default '',
 104    custom_Name varchar(40) NOT NULL default '',
 105    custom_Special mediumint(8) unsigned default NULL,
 106    custom_Side enum('left','right') NOT NULL default 'left',
 107    type_ID tinyint(4) NOT NULL default '0'
 108  ) TYPE=MyISAM;
 109  
 110  CREATE TABLE person_custom (
 111    per_ID mediumint(9) NOT NULL default '0',
 112    PRIMARY KEY  (per_ID)
 113  ) TYPE=MyISAM;
 114  
 115  CREATE TABLE list_lst (
 116    lst_ID mediumint(8) unsigned NOT NULL default '0',
 117    lst_OptionID mediumint(8) unsigned NOT NULL default '0',
 118    lst_OptionSequence tinyint(3) unsigned NOT NULL default '0',
 119    lst_OptionName varchar(50) NOT NULL default ''
 120  ) TYPE=MyISAM;
 121  
 122  # Sample data for group types
 123  INSERT INTO list_lst VALUES (3, 1, 1, 'Ministry');
 124  INSERT INTO list_lst VALUES (3, 2, 2, 'Team');
 125  INSERT INTO list_lst VALUES (3, 3, 3, 'Bible Study');
 126  INSERT INTO list_lst VALUES (3, 4, 4, 'Sunday School Class');
 127  
 128  # Insert the custom-field / group-property types
 129  INSERT INTO list_lst VALUES (4, 1, 1, 'True / False');
 130  INSERT INTO list_lst VALUES (4, 2, 2, 'Date');
 131  INSERT INTO list_lst VALUES (4, 3, 3, 'Text Field (50 char)');
 132  INSERT INTO list_lst VALUES (4, 4, 4, 'Text Field (100 char)');
 133  INSERT INTO list_lst VALUES (4, 5, 5, 'Text Field (Long)');
 134  INSERT INTO list_lst VALUES (4, 6, 6, 'Year');
 135  INSERT INTO list_lst VALUES (4, 7, 7, 'Season');
 136  INSERT INTO list_lst VALUES (4, 8, 8, 'Number');
 137  INSERT INTO list_lst VALUES (4, 9, 9, 'Person from Group');
 138  INSERT INTO list_lst VALUES (4, 10, 10, 'Money');
 139  INSERT INTO list_lst VALUES (4, 11, 11, 'Phone Number');
 140  INSERT INTO list_lst VALUES (4, 12, 12, 'Custom Drop-Down List');
 141