$setupAlreadyRun = true;
} else {
// set up tables
if (!isset($silent)) {
$silent = true;
}
// set up tables
setupTable('customers', "create table if not exists `customers` ( `CustomerID` VARCHAR(5) not null , primary key (`CustomerID`), `CompanyName` VARCHAR(40) , `ContactName` VARCHAR(30) , `ContactTitle` VARCHAR(30) , `Address` VARCHAR(60) , `City` VARCHAR(15) , `Region` VARCHAR(15) , `PostalCode` VARCHAR(10) , `Country` VARCHAR(15) , `Phone` VARCHAR(24) , `Fax` VARCHAR(24) ) CHARSET latin1", $silent);
setupTable('employees', "create table if not exists `employees` ( `EmployeeID` INT not null auto_increment , primary key (`EmployeeID`), `TitleOfCourtesy` VARCHAR(50) , `Photo` VARCHAR(40) , `LastName` VARCHAR(50) , `FirstName` VARCHAR(10) , `Title` VARCHAR(30) , `BirthDate` DATE , `HireDate` DATE , `Address` VARCHAR(50) , `City` VARCHAR(15) , `Region` VARCHAR(15) , `PostalCode` VARCHAR(10) , `Country` VARCHAR(15) , `HomePhone` VARCHAR(24) , `Extension` VARCHAR(4) , `Notes` TEXT , `ReportsTo` INT ) CHARSET latin1", $silent);
setupIndexes('employees', array('ReportsTo'));
setupTable('orders', "create table if not exists `orders` ( `OrderID` INT not null auto_increment , primary key (`OrderID`), `CustomerID` VARCHAR(5) , `EmployeeID` INT , `OrderDate` DATE , `RequiredDate` DATE , `ShippedDate` DATE , `ShipVia` INT(11) , `Freight` FLOAT(10,2) default '0' , `ShipName` VARCHAR(5) , `ShipAddress` VARCHAR(5) , `ShipCity` VARCHAR(5) , `ShipRegion` VARCHAR(5) , `ShipPostalCode` VARCHAR(5) , `ShipCountry` VARCHAR(5) ) CHARSET latin1", $silent);
setupIndexes('orders', array('CustomerID', 'EmployeeID', 'ShipVia'));
setupTable('order_details', "create table if not exists `order_details` ( `odID` INT unsigned not null auto_increment , primary key (`odID`), `OrderID` INT default '0' , `ProductID` INT default '0' , `Category` INT , `UnitPrice` FLOAT(10,2) default '0' , `Quantity` SMALLINT default '1' , `Discount` FLOAT(10,2) default '0' ) CHARSET latin1", $silent);
setupIndexes('order_details', array('OrderID', 'ProductID'));
setupTable('products', "create table if not exists `products` ( `ProductID` INT not null auto_increment , primary key (`ProductID`), `ProductName` VARCHAR(50) , `SupplierID` INT(11) , `CategoryID` INT , `QuantityPerUnit` VARCHAR(50) , `UnitPrice` FLOAT(10,2) default '0' , `UnitsInStock` SMALLINT default '0' , `UnitsOnOrder` SMALLINT(6) default '0' , `ReorderLevel` SMALLINT default '0' , `Discontinued` TINYINT default '0' ) CHARSET latin1", $silent);
setupIndexes('products', array('SupplierID', 'CategoryID'));
setupTable('categories', "create table if not exists `categories` ( `CategoryID` INT not null auto_increment , primary key (`CategoryID`), `Picture` VARCHAR(40) , `CategoryName` VARCHAR(50) , unique(`CategoryName`), `Description` TEXT ) CHARSET latin1", $silent);
setupTable('suppliers', "create table if not exists `suppliers` ( `SupplierID` INT(11) not null auto_increment , primary key (`SupplierID`), `CompanyName` VARCHAR(50) , `ContactName` VARCHAR(30) , `ContactTitle` VARCHAR(30) , `Address` VARCHAR(50) , `City` VARCHAR(15) , `Region` VARCHAR(15) , `PostalCode` VARCHAR(10) , `Country` VARCHAR(50) , `Phone` VARCHAR(24) , `Fax` VARCHAR(24) , `HomePage` TEXT ) CHARSET latin1", $silent);
setupTable('shippers', "create table if not exists `shippers` ( `ShipperID` INT(11) not null auto_increment , primary key (`ShipperID`), `CompanyName` VARCHAR(40) not null , `Phone` VARCHAR(24) ) CHARSET latin1", $silent);
// save MD5
if ($fp = @fopen(dirname(__FILE__) . '/setup.md5', 'w')) {
fwrite($fp, $thisMD5);
fclose($fp);
}
}
function setupIndexes($tableName, $arrFields)
{
if (!is_array($arrFields)) {
return false;
}
foreach ($arrFields as $fieldName) {
<?php
// check this file's MD5 to make sure it wasn't called before
$prevMD5 = @implode('', @file(dirname(__FILE__) . '/setup.md5'));
$thisMD5 = md5(@implode('', @file("./updateDB.php")));
if ($thisMD5 == $prevMD5) {
$setupAlreadyRun = true;
} else {
// set up tables
if (!isset($silent)) {
$silent = true;
}
// set up tables
setupTable('trans_mrs2016', "create table if not exists `trans_mrs2016` ( `transaction_id` SMALLINT(5) not null auto_increment , primary key (`transaction_id`), `firstname` VARCHAR(50) , `lastname` VARCHAR(50) , `email` VARCHAR(50) , `phone` VARCHAR(20) , `quantity` SMALLINT(4) , `amount` DOUBLE(6,2) , `mailinglist` TINYINT(1) default '1' , `remarks` TEXT , `transactiondate` DATE , `seller` VARCHAR(40) , `editingdate` DATE , `editor` VARCHAR(40) ) CHARSET latin1", $silent);
setupTable('duck_mrs2016', "create table if not exists `duck_mrs2016` ( `duck_id` SMALLINT(5) not null auto_increment , primary key (`duck_id`), `transaction_id` SMALLINT(5) , `creationdate` VARCHAR(40) ) CHARSET latin1", $silent);
setupIndexes('duck_mrs2016', array('transaction_id'));
// save MD5
if ($fp = @fopen(dirname(__FILE__) . '/setup.md5', 'w')) {
fwrite($fp, $thisMD5);
fclose($fp);
}
}
function setupIndexes($tableName, $arrFields)
{
if (!is_array($arrFields)) {
return false;
}
foreach ($arrFields as $fieldName) {
if (!($res = @db_query("SHOW COLUMNS FROM `{$tableName}` like '{$fieldName}'"))) {
continue;
}
$silent = true;
}
// set up tables
setupTable('applications_leases', "create table if not exists `applications_leases` ( `id` INT unsigned not null auto_increment , primary key (`id`), `tenants` INT unsigned , `status` VARCHAR(40) not null default 'Application' , `property` INT unsigned , `unit` INT unsigned , `type` VARCHAR(40) not null default 'Fixed' , `total_number_of_occupants` VARCHAR(15) , `start_date` DATE , `end_date` DATE , `recurring_charges_frequency` VARCHAR(40) not null default 'Monthly' , `next_due_date` DATE , `rent` DECIMAL(8,2) , `security_deposit` DECIMAL(15,2) , `security_deposit_date` DATE , `emergency_contact` VARCHAR(100) , `co_signer_details` VARCHAR(100) , `notes` TEXT , `agreement` VARCHAR(40) ) CHARSET utf8", $silent);
setupIndexes('applications_leases', array('tenants', 'property', 'unit'));
setupTable('residence_and_rental_history', "create table if not exists `residence_and_rental_history` ( `id` INT unsigned not null auto_increment , primary key (`id`), `tenant` INT unsigned , `address` VARCHAR(40) , `landlord_or_manager_name` VARCHAR(15) , `landlord_or_manager_phone` VARCHAR(15) , `monthly_rent` DECIMAL(6,2) , `duration_of_residency_from` DATE , `to` DATE , `reason_for_leaving` VARCHAR(40) , `notes` TEXT ) CHARSET utf8", $silent);
setupIndexes('residence_and_rental_history', array('tenant'));
setupTable('employment_and_income_history', "create table if not exists `employment_and_income_history` ( `id` INT unsigned not null auto_increment , primary key (`id`), `tenant` INT unsigned , `employer_name` VARCHAR(15) , `city` VARCHAR(20) , `employer_phone` VARCHAR(15) , `employed_from` DATE , `employed_till` DATE , `occupation` VARCHAR(40) , `notes` TEXT ) CHARSET utf8", $silent);
setupIndexes('employment_and_income_history', array('tenant'));
setupTable('references', "create table if not exists `references` ( `id` INT unsigned not null auto_increment , primary key (`id`), `tenant` INT unsigned , `reference_name` VARCHAR(15) , `phone` VARCHAR(15) ) CHARSET utf8", $silent);
setupIndexes('references', array('tenant'));
setupTable('applicants_and_tenants', "create table if not exists `applicants_and_tenants` ( `id` INT unsigned not null auto_increment , primary key (`id`), `last_name` VARCHAR(15) , `first_name` VARCHAR(15) , `email` VARCHAR(80) , `phone` VARCHAR(15) , `birth_date` DATE , `driver_license_number` VARCHAR(15) , `driver_license_state` VARCHAR(15) , `requested_lease_term` VARCHAR(15) , `monthly_gross_pay` DECIMAL(8,2) , `additional_income` DECIMAL(8,2) , `assets` DECIMAL(8,2) , `status` VARCHAR(40) not null default 'Applicant' , `notes` TEXT ) CHARSET utf8", $silent);
setupTable('properties', "create table if not exists `properties` ( `id` INT unsigned not null auto_increment , primary key (`id`), `property_name` TEXT not null , `type` VARCHAR(40) not null , `number_of_units` DECIMAL(15,0) , `photo` VARCHAR(40) , `owner` INT unsigned , `operating_account` VARCHAR(40) , `property_reserve` DECIMAL(15,0) , `lease_term` VARCHAR(15) , `country` VARCHAR(40) , `street` VARCHAR(40) , `City` VARCHAR(40) , `State` VARCHAR(40) , `ZIP` DECIMAL(15,0) ) CHARSET utf8", $silent);
setupIndexes('properties', array('owner'));
setupTable('units', "create table if not exists `units` ( `id` INT unsigned not null auto_increment , primary key (`id`), `property` INT unsigned , `unit_number` VARCHAR(40) , `photo` VARCHAR(40) , `status` VARCHAR(40) not null , `size` VARCHAR(40) , `country` INT unsigned , `street` INT unsigned , `city` INT unsigned , `state` INT unsigned , `postal_code` INT unsigned , `rooms` VARCHAR(40) , `bathroom` DECIMAL(15,0) , `features` TEXT , `market_rent` DECIMAL(15,0) , `rental_amount` DECIMAL(6,2) , `deposit_amount` DECIMAL(6,2) , `description` TEXT ) CHARSET utf8", $silent);
setupIndexes('units', array('property'));
setupTable('rental_owners', "create table if not exists `rental_owners` ( `id` INT unsigned not null auto_increment , primary key (`id`), `first_name` VARCHAR(40) , `last_name` VARCHAR(40) , `company_name` VARCHAR(40) , `date_of_birth` DATE , `primary_email` VARCHAR(40) , `alternate_email` VARCHAR(40) , `phone` VARCHAR(40) , `country` VARCHAR(40) , `street` VARCHAR(40) , `city` VARCHAR(40) , `state` VARCHAR(40) , `zip` DECIMAL(15,0) , `comments` TEXT ) CHARSET utf8", $silent);
// save MD5
if ($fp = @fopen(dirname(__FILE__) . '/setup.md5', 'w')) {
fwrite($fp, $thisMD5);
fclose($fp);
}
}
function setupIndexes($tableName, $arrFields)
{
if (!is_array($arrFields)) {
return false;
}
foreach ($arrFields as $fieldName) {
if (!($res = @db_query("SHOW COLUMNS FROM `{$tableName}` like '{$fieldName}'"))) {
continue;
$thisMD5 = md5(@implode('', @file("./updateDB.php")));
if ($thisMD5 == $prevMD5) {
$setupAlreadyRun = true;
} else {
// set up tables
if (!isset($silent)) {
$silent = true;
}
// set up tables
setupTable('diseases', "create table if not exists `diseases` ( `id` INT unsigned not null auto_increment , primary key (`id`), `short_name` VARCHAR(40) not null , `latin_name` VARCHAR(40) , `description` TEXT , `other_details` TEXT , `comments` TEXT ) CHARSET latin1", $silent);
setupTable('patients', "create table if not exists `patients` ( `id` INT unsigned not null auto_increment , primary key (`id`), `last_name` VARCHAR(40) not null , `first_name` VARCHAR(40) not null , `gender` VARCHAR(10) not null default 'Unknown' , `birth_date` DATE , `age` INT , `address` TEXT , `city` VARCHAR(40) , `state` VARCHAR(15) , `zip` CHAR(8) , `home_phone` VARCHAR(40) , `work_phone` VARCHAR(40) , `mobile` VARCHAR(40) , `other_details` TEXT , `comments` TEXT , `filed` DATETIME , `last_modified` VARCHAR(40) ) CHARSET latin1", $silent);
setupTable('symptoms', "create table if not exists `symptoms` ( `id` INT unsigned not null auto_increment , primary key (`id`), `name` VARCHAR(80) not null , `description` TEXT , `comments` TEXT ) CHARSET latin1", $silent);
setupTable('disease_symptoms', "create table if not exists `disease_symptoms` ( `id` INT unsigned not null auto_increment , primary key (`id`), `disease` INT unsigned not null , `symptom` INT unsigned not null , `expected_probability` VARCHAR(40) , `minimum` VARCHAR(40) , `maximum` VARCHAR(40) , `reading_other_value` VARCHAR(40) , `comments` TEXT ) CHARSET latin1", $silent);
setupIndexes('disease_symptoms', array('disease', 'symptom'));
setupTable('patient_symptoms', "create table if not exists `patient_symptoms` ( `id` INT unsigned not null auto_increment , primary key (`id`), `patient` INT unsigned not null , `symptom` INT unsigned not null , `observation_date` DATE , `observation_time` TIME , `symptom_value` VARCHAR(40) ) CHARSET latin1", $silent);
setupIndexes('patient_symptoms', array('patient', 'symptom'));
// save MD5
if ($fp = @fopen(dirname(__FILE__) . '/setup.md5', 'w')) {
fwrite($fp, $thisMD5);
fclose($fp);
}
}
function setupIndexes($tableName, $arrFields)
{
if (!is_array($arrFields)) {
return false;
}
foreach ($arrFields as $fieldName) {
if (!($res = @mysql_query("SHOW COLUMNS FROM `{$tableName}` like '{$fieldName}'"))) {
continue;
}
setupTable('clients', "create table if not exists `clients` ( `client_id` TINYINT not null auto_increment , primary key (`client_id`), `name` VARCHAR(60) not null , unique(`name`), `created` DATE , `created_by` VARCHAR(40) ) CHARSET utf8", $silent);
setupTable('companies', "create table if not exists `companies` ( `company_id` TINYINT not null auto_increment , primary key (`company_id`), `name` VARCHAR(60) , `client` TINYINT , `website` VARCHAR(40) , `description` TEXT , `founded` TINYINT , `industry` VARCHAR(40) , `company_number` INT , `country_hq` VARCHAR(40) default 'United Kingdom' , `country_operations` BLOB , `num_employees` INT , `company_type` VARCHAR(40) , `sic_code` INT , `created` DATE , `created_by` VARCHAR(40) ) CHARSET utf8", $silent);
setupIndexes('companies', array('client', 'sic_code'));
setupTable('sic', "create table if not exists `sic` ( `sic_id` INT not null auto_increment , primary key (`sic_id`), `code` INT , `activity` VARCHAR(255) ) CHARSET utf8", $silent);
setupTable('reports', "create table if not exists `reports` ( `report_id` TINYINT not null auto_increment , primary key (`report_id`), `start_date` DATE not null , `end_date` DATE , `company` TINYINT , `created` DATE , `created_by` VARCHAR(40) , `average_score` INT ) CHARSET utf8", $silent);
setupIndexes('reports', array('company'));
setupTable('entries', "create table if not exists `entries` ( `entry_id` INT not null auto_increment , primary key (`entry_id`), `created` DATE , `created_by` VARCHAR(40) , `report` TINYINT not null , `outcome_area` INT , `outcome` INT , `indicator` INT , `score` TINYINT , `beneficiary_group` TINYINT , `beneficiary_group_relevance` VARCHAR(40) , `comment` MEDIUMTEXT , `reference` VARCHAR(255) , `reliability` VARCHAR(40) , `intentionality` VARCHAR(40) , `equivalence` VARCHAR(40) ) CHARSET utf8", $silent);
setupIndexes('entries', array('report', 'outcome', 'indicator', 'beneficiary_group'));
setupTable('outcome_areas', "create table if not exists `outcome_areas` ( `outcome_area_id` TINYINT not null auto_increment , primary key (`outcome_area_id`), `name` VARCHAR(40) , `description` MEDIUMTEXT ) CHARSET utf8", $silent);
setupTable('outcomes', "create table if not exists `outcomes` ( `outcome_id` INT not null auto_increment , primary key (`outcome_id`), `outcome_area` TINYINT , `description` MEDIUMTEXT , `strata` VARCHAR(40) ) CHARSET utf8", $silent);
setupIndexes('outcomes', array('outcome_area'));
setupTable('beneficiary_groups', "create table if not exists `beneficiary_groups` ( `beneficiary_group_id` TINYINT not null auto_increment , primary key (`beneficiary_group_id`), `name` VARCHAR(100) , `description` MEDIUMTEXT ) CHARSET utf8", $silent);
setupTable('indicators', "create table if not exists `indicators` ( `indicator_id` INT not null auto_increment , primary key (`indicator_id`), `outcome` INT , `description` LONGTEXT ) CHARSET utf8", $silent, array(" ALTER TABLE `indicators` CHANGE `strata` `strata` VARCHAR(40) ", "ALTER TABLE `indicators` DROP `strata`"));
setupIndexes('indicators', array('outcome'));
setupTable('tax_entry', "create table if not exists `tax_entry` ( `tax_entry_id` TINYINT not null auto_increment , primary key (`tax_entry_id`), `created` DATE , `created_by` VARCHAR(40) , `report` TINYINT not null , `turnover` VARCHAR(40) , `payroll_tax` VARCHAR(40) , `ni_contribution` VARCHAR(40) , `business_rates` VARCHAR(40) , `corporation_tax` VARCHAR(40) , `other_tax` VARCHAR(40) , `sum` VARCHAR(40) , `ratio` VARCHAR(40) ) CHARSET utf8", $silent);
setupIndexes('tax_entry', array('report'));
// save MD5
if ($fp = @fopen(dirname(__FILE__) . '/setup.md5', 'w')) {
fwrite($fp, $thisMD5);
fclose($fp);
}
}
function setupIndexes($tableName, $arrFields)
{
if (!is_array($arrFields)) {
return false;
}
foreach ($arrFields as $fieldName) {
if (!($res = @mysql_query("SHOW COLUMNS FROM `{$tableName}` like '{$fieldName}'"))) {
continue;
}