# Database Schema CREATE TABLE `ticket_contacts` ( `tc_id` INT AUTO_INCREMENT NOT NULL, `tc_ticket` INT NULL DEFAULT NULL , `tc_contact` INT NULL DEFAULT NULL , PRIMARY KEY (`tc_id`) ) ENGINE = MyISAM; CREATE TABLE `login` ( `login_id` INT AUTO_INCREMENT NOT NULL COMMENT 'ID' , `login_user` VARCHAR(16) NULL DEFAULT NULL COMMENT 'Login Name' , `login_timestamp` DATETIME NULL DEFAULT NULL COMMENT 'Login Timestamp' , `login_url` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Login URL' , PRIMARY KEY (`login_id`) ) ENGINE = InnoDB; CREATE TABLE `ra_controls` ( `con_id` INT AUTO_INCREMENT NOT NULL, `con_risk` INT NULL DEFAULT NULL , `con_text` VARCHAR(1000) NULL DEFAULT '\'None'' , PRIMARY KEY (`con_id`) ) ENGINE = InnoDB; CREATE TABLE `users_social` ( `id` INT AUTO_INCREMENT NOT NULL, `username` VARCHAR(100) NULL DEFAULT NULL , `email` VARCHAR(100) NULL DEFAULT NULL , `source` VARCHAR(100) NULL DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; CREATE TABLE `ra_training` ( `train_id` INT AUTO_INCREMENT NOT NULL, `train_risk` INT NULL DEFAULT NULL , `train_text` VARCHAR(1000) NULL DEFAULT NULL , PRIMARY KEY (`train_id`) ) ENGINE = InnoDB; CREATE TABLE `client_users` ( `id` INT AUTO_INCREMENT NOT NULL, `username` VARCHAR(50) NOT NULL, `password` VARCHAR(100) NULL DEFAULT NULL , `email` VARCHAR(100) NULL DEFAULT NULL , `activ_status` TINYINT NULL DEFAULT 0 , `activ_key` VARCHAR(1000) NULL DEFAULT NULL , `fullname` VARCHAR(128) NULL DEFAULT NULL , `client` VARCHAR(128) NULL DEFAULT NULL , `client_id` INT NULL DEFAULT NULL , `contact_id` INT NULL DEFAULT NULL , PRIMARY KEY (`id`), CONSTRAINT `username` UNIQUE (`username`) ) ENGINE = InnoDB; CREATE TABLE `line_items` ( `line_id` INT AUTO_INCREMENT NOT NULL, `line_quote` INT NULL DEFAULT NULL , `line_proposal` INT NULL DEFAULT NULL , `line_type` VARCHAR(16) NULL DEFAULT NULL , `line_description` TEXT NULL DEFAULT NULL , `line_description_extra` TEXT NULL DEFAULT NULL , `line_status` VARCHAR(16) NULL DEFAULT NULL , `line_quantity` DECIMAL(10,2) NULL DEFAULT NULL , `line_unit_price` DECIMAL(10,2) NULL DEFAULT NULL , `line_total` DECIMAL(10,2) NULL DEFAULT NULL , `line_notes` TEXT NULL DEFAULT NULL , `line_logged_user` VARCHAR(32) NULL DEFAULT NULL , `line_created` DATETIME NULL DEFAULT NULL , `line_modified_by` VARCHAR(32) NULL DEFAULT NULL , `line_modified` DATETIME NULL DEFAULT NULL , `line_closed_by` VARCHAR(32) NULL DEFAULT NULL , `line_closed` DATETIME NULL DEFAULT NULL , `line_order` INT NULL DEFAULT NULL , `line_order_new` INT NULL DEFAULT NULL , `line_prov` VARCHAR(3) NULL DEFAULT NULL , `line_prov_pre` TEXT NULL DEFAULT NULL , `line_prov_post` TEXT NULL DEFAULT NULL , `line_prov_value` DECIMAL(10,2) NULL DEFAULT 0.00 , `line_orig_total` DECIMAL(10,2) NULL DEFAULT NULL , `line_orig_prov_value` DECIMAL(10,2) NULL DEFAULT NULL , `line_summary` TEXT NULL DEFAULT NULL , PRIMARY KEY (`line_id`) ) ENGINE = InnoDB; CREATE TABLE `vehicles_mileage` ( `vm_id` INT AUTO_INCREMENT NOT NULL, `vm_vehicle` INT NULL DEFAULT NULL , `vm_date` DATE NULL DEFAULT NULL , `vm_mileage` INT NULL DEFAULT NULL , PRIMARY KEY (`vm_id`) ) ENGINE = InnoDB; CREATE TABLE `ticket_air` ( `ticket_air_id` INT AUTO_INCREMENT NOT NULL, `ticket_air_ticket` INT NULL DEFAULT NULL , `ticket_air_ref` VARCHAR(16) NULL DEFAULT NULL , PRIMARY KEY (`ticket_air_id`) ) ENGINE = InnoDB; CREATE TABLE `users` ( `id` INT AUTO_INCREMENT NOT NULL, `username` VARCHAR(50) NOT NULL, `password` VARCHAR(100) NULL DEFAULT NULL , `email` VARCHAR(100) NULL DEFAULT NULL , `activ_status` TINYINT NULL DEFAULT 0 , `activ_key` VARCHAR(1000) NULL DEFAULT NULL , `fullname` VARCHAR(128) NULL DEFAULT NULL , PRIMARY KEY (`id`), CONSTRAINT `username` UNIQUE (`username`) ) ENGINE = InnoDB; CREATE TABLE `ticket_gas` ( `ticket_gas_id` INT AUTO_INCREMENT NOT NULL, `ticket_gas_ticket` INT NULL DEFAULT NULL , `ticket_gas_cert` VARCHAR(32) NULL DEFAULT NULL , `ticket_gas_check` VARCHAR(32) NULL DEFAULT NULL , `ticket_gas_main` VARCHAR(32) NULL DEFAULT NULL , PRIMARY KEY (`ticket_gas_id`) ) ENGINE = InnoDB; CREATE TABLE `proposals` ( `prop_id` INT AUTO_INCREMENT NOT NULL, `prop_ref` VARCHAR(20) NULL DEFAULT NULL , `prop_revision` INT NULL DEFAULT 1 , `prop_quote` INT NULL DEFAULT NULL , `prop_title` VARCHAR(500) NULL DEFAULT NULL , `prop_description` VARCHAR(5000) NULL DEFAULT NULL , `prop_status` VARCHAR(16) NULL DEFAULT NULL , `prop_outcome` VARCHAR(32) NULL DEFAULT NULL , `prop_logged_user` VARCHAR(32) NULL DEFAULT NULL , `prop_created` DATETIME NULL DEFAULT NULL , `prop_modified_by` VARCHAR(32) NULL DEFAULT NULL , `prop_modified` DATETIME NULL DEFAULT NULL , `prop_closed_by` VARCHAR(32) NULL DEFAULT NULL , `prop_closed` DATETIME NULL DEFAULT NULL , `prop_deleted_by` VARCHAR(32) NULL DEFAULT NULL , `prop_deleted` DATETIME NULL DEFAULT NULL , `prop_deleted_flag` INT NULL DEFAULT NULL , `prop_approved` DATETIME NULL DEFAULT NULL , `prop_rejected` DATETIME NULL DEFAULT NULL , `prop_value` DECIMAL(10,2) NULL DEFAULT NULL , PRIMARY KEY (`prop_id`) ) ENGINE = InnoDB; CREATE TABLE `red_drain_ref` ( `ref_id` INT AUTO_INCREMENT NOT NULL, `reference_type` VARCHAR(3) NULL DEFAULT '\'DRA'' , `next_number` INT NULL DEFAULT NULL , PRIMARY KEY (`ref_id`) ) ENGINE = InnoDB; CREATE TABLE `method_templates` ( `mt_id` INT AUTO_INCREMENT NOT NULL, `mt_fault` INT NULL DEFAULT NULL , `mt_hazards` BLOB NULL DEFAULT NULL , `mt_persons` BLOB NULL DEFAULT NULL , `mt_sequence` BLOB NULL DEFAULT NULL , `mt_supervision` BLOB NULL DEFAULT NULL , `mt_controls` BLOB NULL DEFAULT NULL , `mt_plant` BLOB NULL DEFAULT NULL , `mt_welfare` BLOB NULL DEFAULT NULL , `mt_emergency` BLOB NULL DEFAULT NULL , `mt_resource` VARCHAR(64) NULL DEFAULT NULL , `mt_description` BLOB NULL DEFAULT NULL , `mt_helmet` VARCHAR(3) NULL DEFAULT NULL , `mt_footwear` VARCHAR(3) NULL DEFAULT NULL , `mt_coverall` VARCHAR(3) NULL DEFAULT NULL , `mt_gloves` VARCHAR(3) NULL DEFAULT NULL , `mt_hiviz` VARCHAR(3) NULL DEFAULT NULL , `mt_resp` VARCHAR(3) NULL DEFAULT NULL , `mt_mask` VARCHAR(3) NULL DEFAULT NULL , `mt_glasses` VARCHAR(3) NULL DEFAULT NULL , `mt_ear` VARCHAR(3) NULL DEFAULT NULL , `mt_harness` VARCHAR(3) NULL DEFAULT NULL , PRIMARY KEY (`mt_id`) ) ENGINE = InnoDB; CREATE TABLE `ticket_method` ( `method_id` INT AUTO_INCREMENT NOT NULL, `method_ticket` INT NULL DEFAULT NULL , `method_hazards` BLOB NULL DEFAULT NULL , `method_persons` BLOB NULL DEFAULT NULL , `method_sequence` BLOB NULL DEFAULT NULL , `method_supervision` BLOB NULL DEFAULT NULL , `method_controls` BLOB NULL DEFAULT NULL , `method_plant` BLOB NULL DEFAULT NULL , `method_welfare` BLOB NULL DEFAULT NULL , `method_emergency` BLOB NULL DEFAULT NULL , `method_resource` VARCHAR(64) NULL DEFAULT NULL , `method_date` DATETIME NULL DEFAULT NULL , `method_description` BLOB NULL DEFAULT NULL , `method_works_date` DATE NULL DEFAULT NULL , `method_works_end_date` DATE NULL DEFAULT NULL , `method_works_duration` VARCHAR(100) NULL DEFAULT NULL , `method_helmet` VARCHAR(3) NULL DEFAULT NULL , `method_footwear` VARCHAR(3) NULL DEFAULT NULL , `method_coverall` VARCHAR(3) NULL DEFAULT NULL , `method_gloves` VARCHAR(3) NULL DEFAULT NULL , `method_hiviz` VARCHAR(3) NULL DEFAULT NULL , `method_resp` VARCHAR(3) NULL DEFAULT NULL , `method_mask` VARCHAR(3) NULL DEFAULT NULL , `method_glasses` VARCHAR(3) NULL DEFAULT NULL , `method_ear` VARCHAR(3) NULL DEFAULT NULL , `method_harness` VARCHAR(3) NULL DEFAULT NULL , `method_category` VARCHAR(100) NULL DEFAULT NULL , PRIMARY KEY (`method_id`) ) ENGINE = InnoDB; CREATE TABLE `red_pat_record` ( `pat_id` INT AUTO_INCREMENT NOT NULL, `pat_ref` VARCHAR(16) NULL DEFAULT NULL , `pat_address` VARCHAR(512) NULL DEFAULT NULL , `pat_date` DATETIME NULL DEFAULT NULL , `pat_status` VARCHAR(16) NULL DEFAULT NULL , `pat_client` VARCHAR(128) NULL DEFAULT NULL , `pat_hdt` VARCHAR(12) NULL DEFAULT NULL , `pat_engineer` VARCHAR(64) NULL DEFAULT NULL , `pat_notes` BLOB NULL DEFAULT NULL , PRIMARY KEY (`pat_id`) ) ENGINE = InnoDB; CREATE TABLE `red_drain_observations` ( `obs_id` INT AUTO_INCREMENT NOT NULL, `obs_section` INT NULL DEFAULT NULL , `obs_code` INT NULL DEFAULT NULL , `obs_description` VARCHAR(128) NULL DEFAULT NULL , `obs_position` DECIMAL(10,2) NULL DEFAULT NULL , PRIMARY KEY (`obs_id`) ) ENGINE = MyISAM; CREATE TABLE `red_gas_appliances_hiu` ( `hiu_id` INT AUTO_INCREMENT NOT NULL, `hiu_ref` INT NULL DEFAULT NULL , `hiu_location` VARCHAR(64) NULL DEFAULT NULL , `hiu_model` VARCHAR(64) NULL DEFAULT NULL , `hiu_serial` VARCHAR(64) NULL DEFAULT NULL , `hiu_heat_exchange` VARCHAR(4) NULL DEFAULT NULL , `hiu_temp_settings` VARCHAR(4) NULL DEFAULT NULL , `hiu_pressure_settings` VARCHAR(4) NULL DEFAULT NULL , `hiu_filters` VARCHAR(4) NULL DEFAULT NULL , `hiu_heating_flow` VARCHAR(4) NULL DEFAULT NULL , `hiu_water_flow` VARCHAR(4) NULL DEFAULT NULL , `hiu_controls` VARCHAR(4) NULL DEFAULT NULL , `hiu_safety` VARCHAR(4) NULL DEFAULT NULL , `hiu_impulse_line` VARCHAR(4) NULL DEFAULT NULL , `hiu_electrical` VARCHAR(4) NULL DEFAULT NULL , `hiu_leaks` VARCHAR(4) NULL DEFAULT NULL , `hiu_casing` VARCHAR(4) NULL DEFAULT NULL , `hiu_working` VARCHAR(4) NULL DEFAULT NULL , `hiu_defects` TEXT NULL DEFAULT NULL , `hiu_notes` TEXT NULL DEFAULT NULL , PRIMARY KEY (`hiu_id`) ) ENGINE = InnoDB; CREATE TABLE `red_gas_appliances_serv` ( `check_id` INT AUTO_INCREMENT NOT NULL, `check_service_ref` INT NULL DEFAULT NULL , `check_location` VARCHAR(128) NULL DEFAULT NULL , `check_model_type` VARCHAR(128) NULL DEFAULT NULL , `check_make` VARCHAR(128) NULL DEFAULT NULL , `check_model` VARCHAR(128) NULL DEFAULT NULL , `check_type` VARCHAR(16) NULL DEFAULT '\'Service'' , `check_tight` VARCHAR(4) NULL DEFAULT '\'Pass'' , `check_burner` VARCHAR(3) NULL DEFAULT '\'Yes'' , `check_burner_action` BLOB NULL DEFAULT NULL , `check_exchange` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_exchange_action` BLOB NULL DEFAULT NULL , `check_ignition` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_ignition_action` BLOB NULL DEFAULT NULL , `check_electric` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_electric_action` BLOB NULL DEFAULT NULL , `check_controls` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_controls_action` BLOB NULL DEFAULT NULL , `check_leaks` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_leaks_action` BLOB NULL DEFAULT NULL , `check_connections` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_connections_action` BLOB NULL DEFAULT NULL , `check_seals` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_seals_action` BLOB NULL DEFAULT NULL , `check_pipework` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_pipework_action` BLOB NULL DEFAULT NULL , `check_fans` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_fans_action` BLOB NULL DEFAULT NULL , `check_fireplace` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_fireplace_action` BLOB NULL DEFAULT NULL , `check_plate` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_plate_action` BLOB NULL DEFAULT NULL , `check_picture` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_picture_action` BLOB NULL DEFAULT NULL , `check_loc` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_loc_action` BLOB NULL DEFAULT NULL , `check_stable` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_stable_action` BLOB NULL DEFAULT NULL , `check_plenum` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_plenum_action` BLOB NULL DEFAULT NULL , `check_vent` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_vent_action` BLOB NULL DEFAULT NULL , `check_flue` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_flue_action` BLOB NULL DEFAULT NULL , `check_flow` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_flow_action` BLOB NULL DEFAULT NULL , `check_spill` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_spill_action` BLOB NULL DEFAULT NULL , `check_device` VARCHAR(4) NULL DEFAULT '\'Yes'' , `check_device_action` BLOB NULL DEFAULT NULL , `check_pressure` VARCHAR(16) NULL DEFAULT NULL , `check_meter_sn` VARCHAR(64) NULL DEFAULT NULL , `check_co_percentage` VARCHAR(16) NULL DEFAULT NULL , `check_co_ppm` VARCHAR(16) NULL DEFAULT NULL , `check_co_ratio` VARCHAR(16) NULL DEFAULT NULL , `check_safe` VARCHAR(3) NULL DEFAULT '\'Yes'' , `check_label` VARCHAR(3) NULL DEFAULT '\'Yes'' , `check_standard` VARCHAR(3) NULL DEFAULT '\'Yes'' , `check_work` BLOB NULL DEFAULT NULL , `check_meter_reading` VARCHAR(16) NULL DEFAULT NULL , PRIMARY KEY (`check_id`) ) ENGINE = InnoDB; CREATE TABLE `ticket_outcome_codes` ( `outcome_id` INT AUTO_INCREMENT NOT NULL, `outcome_code` VARCHAR(32) NULL DEFAULT NULL , PRIMARY KEY (`outcome_id`) ) ENGINE = InnoDB; CREATE TABLE `suppliers` ( `supplier_id` INT AUTO_INCREMENT NOT NULL COMMENT 'ID' , `supplier_name` VARCHAR(256) NULL DEFAULT NULL COMMENT 'Supplier Name' , PRIMARY KEY (`supplier_id`) ) ENGINE = InnoDB; CREATE TABLE `sites` ( `site_id` INT AUTO_INCREMENT NOT NULL, `site_hdt` VARCHAR(16) NULL DEFAULT NULL , `site_address` VARCHAR(512) NULL DEFAULT NULL , PRIMARY KEY (`site_id`) ) ENGINE = InnoDB; CREATE TABLE `red_air_ref` ( `id` INT AUTO_INCREMENT NOT NULL, `reference_type` VARCHAR(10) NULL DEFAULT NULL , `reference_code` VARCHAR(3) NULL DEFAULT NULL , `next_number` INT NULL DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; CREATE TABLE `quote_templates` ( `qt_id` INT AUTO_INCREMENT NOT NULL, `qt_title` VARCHAR(256) NULL DEFAULT NULL , `qt_description` TEXT NULL DEFAULT NULL , `qt_price` DOUBLE NULL DEFAULT 0.00 , `qt_category` INT NULL DEFAULT NULL , PRIMARY KEY (`qt_id`) ) ENGINE = InnoDB; CREATE TABLE `red_me_services` ( `me_service_id` INT AUTO_INCREMENT NOT NULL, `me_service_site` INT NULL DEFAULT NULL , `me_service_portfolio` INT NULL DEFAULT NULL , `me_service_lights_freq` VARCHAR(16) NULL DEFAULT NULL , `me_service_elights_freq` VARCHAR(16) NULL DEFAULT NULL , `me_service_flush_freq` VARCHAR(16) NULL DEFAULT NULL , `me_service_temp_freq` VARCHAR(16) NULL DEFAULT NULL , `me_service_fire_freq` VARCHAR(16) NULL DEFAULT NULL , `me_service_smoke_freq` VARCHAR(16) NULL DEFAULT NULL , `me_service_air_con_freq` VARCHAR(16) NULL DEFAULT NULL , `me_service_boiler_freq` VARCHAR(16) NULL DEFAULT NULL , `me_service_wmeter_freq` VARCHAR(16) NULL DEFAULT NULL , `me_service_emeter_freq` VARCHAR(16) NULL DEFAULT NULL , `me_service_gmeter_freq` VARCHAR(16) NULL DEFAULT NULL , `me_service_hpmeter_freq` VARCHAR(16) NULL DEFAULT NULL , PRIMARY KEY (`me_service_id`) ) ENGINE = InnoDB; CREATE TABLE `red_fire_doors` ( `fire_id` INT AUTO_INCREMENT NOT NULL, `fire_ref` VARCHAR(16) NULL DEFAULT NULL , `fire_address` VARCHAR(512) NULL DEFAULT NULL , `fire_date` DATE NULL DEFAULT NULL , `fire_client` VARCHAR(128) NULL DEFAULT NULL , `fire_hdt` INT NULL DEFAULT NULL , `fire_engineer` VARCHAR(64) NULL DEFAULT NULL , PRIMARY KEY (`fire_id`) ) ENGINE = InnoDB; CREATE TABLE `ra_assessment` ( `ass_id` INT NOT NULL, `ass_ref` VARCHAR(8) NULL DEFAULT NULL , `ass_title` VARCHAR(256) NULL DEFAULT NULL , `ass_persons` VARCHAR(256) NULL DEFAULT NULL , `ass_helmet` CHAR(1) NULL DEFAULT NULL , `ass_resp` CHAR(1) NULL DEFAULT NULL , `ass_foot` CHAR(1) NULL DEFAULT NULL , `ass_mask` CHAR(1) NULL DEFAULT NULL , `ass_cover` CHAR(1) NULL DEFAULT NULL , `ass_glass` CHAR(1) NULL DEFAULT NULL , `ass_glove` CHAR(1) NULL DEFAULT NULL , `ass_ear` CHAR(1) NULL DEFAULT NULL , `ass_hiviz` CHAR(1) NULL DEFAULT NULL , `ass_harn` CHAR(1) NULL DEFAULT NULL , `ass_prob` INT NULL DEFAULT NULL , `ass_prob_t` VARCHAR(8) NULL DEFAULT NULL , `ass_sev` INT NULL DEFAULT NULL , `ass_sev_t` VARCHAR(8) NULL DEFAULT NULL , `ass_assess` INT NULL DEFAULT NULL , `ass_assess_t` VARCHAR(8) NULL DEFAULT NULL , `ass_rev_date` DATETIME NULL DEFAULT NULL , PRIMARY KEY (`ass_id`) ) ENGINE = InnoDB; CREATE TABLE `red_me_history` ( `me_history_id` INT AUTO_INCREMENT NOT NULL, `me_history_site` INT NULL DEFAULT NULL , `me_history_service` VARCHAR(64) NULL DEFAULT NULL , `me_history_date` DATETIME NULL DEFAULT NULL , PRIMARY KEY (`me_history_id`) ) ENGINE = InnoDB; CREATE TABLE `proposals2` ( `prop_id` INT AUTO_INCREMENT NOT NULL, `prop_ref` VARCHAR(20) NULL DEFAULT NULL , `prop_revision` INT NULL DEFAULT 1 , `prop_quote` INT NULL DEFAULT NULL , `prop_title` VARCHAR(500) NULL DEFAULT NULL , `prop_description` VARCHAR(5000) NULL DEFAULT NULL , `prop_status` VARCHAR(16) NULL DEFAULT NULL , `prop_outcome` VARCHAR(32) NULL DEFAULT NULL , `prop_logged_user` VARCHAR(32) NULL DEFAULT NULL , `prop_created` DATETIME NULL DEFAULT NULL , `prop_modified_by` VARCHAR(32) NULL DEFAULT NULL , `prop_modified` DATETIME NULL DEFAULT NULL , `prop_closed_by` VARCHAR(32) NULL DEFAULT NULL , `prop_closed` DATETIME NULL DEFAULT NULL , `prop_deleted_by` VARCHAR(32) NULL DEFAULT NULL , `prop_deleted` DATETIME NULL DEFAULT NULL , `prop_deleted_flag` INT NULL DEFAULT NULL , `prop_approved` DATETIME NULL DEFAULT NULL , `prop_rejected` DATETIME NULL DEFAULT NULL , PRIMARY KEY (`prop_id`) ) ENGINE = InnoDB; CREATE TABLE `red_gas_records` ( `cert_id` INT AUTO_INCREMENT NOT NULL, `cert_ref` VARCHAR(16) NULL DEFAULT NULL , `cert_address` VARCHAR(512) NULL DEFAULT NULL , `cert_date` DATETIME NULL DEFAULT NULL , `cert_status` VARCHAR(16) NULL DEFAULT NULL , `cert_client` VARCHAR(128) NULL DEFAULT NULL , `cert_hdt` VARCHAR(12) NULL DEFAULT NULL , `cert_engineer` VARCHAR(64) NULL DEFAULT NULL , `cert_notes` BLOB NULL DEFAULT NULL , `cert_type` VARCHAR(32) NULL DEFAULT NULL , `cert_install` VARCHAR(8) NULL DEFAULT '\'Pass'' , `cert_supply` VARCHAR(8) NULL DEFAULT '\'Pass'' , `cert_bonding` VARCHAR(8) NULL DEFAULT '\'Yes'' , `cert_tightness` VARCHAR(8) NULL DEFAULT '\'Pass'' , `cert_ecv` VARCHAR(8) NULL DEFAULT '\'Yes'' , `cert_main_defect` BLOB NULL DEFAULT NULL , `cert_client_address` VARCHAR(512) NULL DEFAULT NULL , `cert_alarm_remedial` VARCHAR(1024) NULL DEFAULT NULL , `cert_alarm_work` VARCHAR(1024) NULL DEFAULT NULL , CONSTRAINT `unique_cert_id` UNIQUE (`cert_id`) ) ENGINE = InnoDB; CREATE TABLE `red_time_expenses` ( `ex_id` INT AUTO_INCREMENT NOT NULL, `ex_date` DATETIME NULL DEFAULT NULL , `ex_value` DECIMAL(10,2) NULL DEFAULT NULL , `ex_resource` VARCHAR(16) NULL DEFAULT NULL , `ex_notes` BLOB NULL DEFAULT NULL , PRIMARY KEY (`ex_id`) ) ENGINE = InnoDB; CREATE TABLE `orders` ( `order_id` INT AUTO_INCREMENT NOT NULL COMMENT 'Order ID' , `order_text` VARCHAR(1024) NULL DEFAULT NULL COMMENT 'Order Text' , `order_date` DATETIME NULL DEFAULT NULL COMMENT 'Order Date' , `order_note` TEXT NULL DEFAULT NULL COMMENT 'Order Note' , `order_ticket` INT NULL DEFAULT NULL COMMENT 'Order Ticket' , `order_resource` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Ordered By' , `order_delivery` VARCHAR(512) NULL DEFAULT NULL COMMENT 'Delivery Address' , `order_status` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Order Status' , `order_delivery_date` DATETIME NULL DEFAULT NULL COMMENT 'Date Delivered' , `order_reference` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Order Reference' , `order_value` DOUBLE NULL DEFAULT NULL COMMENT 'Order Value' , `order_supplier` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Order Supplier' , PRIMARY KEY (`order_id`) ) ENGINE = MyISAM; CREATE TABLE `red_fire_doors_assets` ( `asset_id` INT AUTO_INCREMENT NOT NULL, `asset_ref` VARCHAR(16) NULL DEFAULT NULL , `asset_location` VARCHAR(512) NULL DEFAULT NULL , `asset_close_frame` VARCHAR(4) NULL DEFAULT NULL , `asset_close_frame_comment` TEXT NULL DEFAULT NULL , `asset_close_open` VARCHAR(4) NULL DEFAULT NULL , `asset_close_open_comment` TEXT NULL DEFAULT NULL , `asset_close_held` VARCHAR(4) NULL DEFAULT NULL , `asset_close_held_comment` TEXT NULL DEFAULT NULL , `asset_close_binding` VARCHAR(4) NULL DEFAULT NULL , `asset_close_binding_comment` TEXT NULL DEFAULT NULL , `asset_frame_head` VARCHAR(4) NULL DEFAULT NULL , `asset_frame_head_comment` TEXT NULL DEFAULT NULL , `asset_frame_edge` VARCHAR(4) NULL DEFAULT NULL , `asset_frame_edge_comment` TEXT NULL DEFAULT NULL , `asset_frame_hanging` VARCHAR(4) NULL DEFAULT NULL , `asset_frame_hanging_comment` TEXT NULL DEFAULT NULL , `asset_frame_threshold` VARCHAR(4) NULL DEFAULT NULL , `asset_frame_threshold_comment` TEXT NULL DEFAULT NULL , `asset_seal_head` VARCHAR(4) NULL DEFAULT NULL , `asset_seal_head_comment` TEXT NULL DEFAULT NULL , `asset_seal_hanging_edge` VARCHAR(4) NULL DEFAULT NULL , `asset_seal_hanging_edge_comment` TEXT NULL DEFAULT NULL , `asset_seal_closing_edge` VARCHAR(4) NULL DEFAULT NULL , `asset_seal_closing_edge_comment` TEXT NULL DEFAULT NULL , `asset_leaf_faces` VARCHAR(4) NULL DEFAULT NULL , `asset_leaf_faces_comment` TEXT NULL DEFAULT NULL , `asset_leaf_edges` VARCHAR(4) NULL DEFAULT NULL , `asset_leaf_edges_comment` TEXT NULL DEFAULT NULL , `asset_frame_damage` VARCHAR(4) NULL DEFAULT NULL , `asset_frame_damage_comment` TEXT NULL DEFAULT NULL , `asset_frame_fixed` VARCHAR(4) NULL DEFAULT NULL , `asset_frame_fixed_comment` TEXT NULL DEFAULT NULL , `asset_frame_gaps` VARCHAR(4) NULL DEFAULT NULL , `asset_frame_gaps_comment` TEXT NULL DEFAULT NULL , `asset_closer` VARCHAR(4) NULL DEFAULT NULL , `asset_closer_comment` TEXT NULL DEFAULT NULL , `asset_hinge_no` VARCHAR(4) NULL DEFAULT NULL , `asset_hinge_no_comment` TEXT NULL DEFAULT NULL , `asset_hinge_screws` VARCHAR(4) NULL DEFAULT NULL , `asset_hinge_screws_comment` TEXT NULL DEFAULT NULL , `asset_lock` VARCHAR(4) NULL DEFAULT NULL , `asset_lock_comment` TEXT NULL DEFAULT NULL , `asset_glass` VARCHAR(4) NULL DEFAULT NULL , `asset_glass_comment` TEXT NULL DEFAULT NULL , `asset_glass_retention` VARCHAR(4) NULL DEFAULT NULL , `asset_glass_retention_comment` TEXT NULL DEFAULT NULL , `asset_letterbox` VARCHAR(4) NULL DEFAULT NULL , `asset_letterbox_comment` TEXT NULL DEFAULT NULL , `asset_ticket_id` INT NULL DEFAULT NULL , `asset_no_access` VARCHAR(3) NULL DEFAULT NULL , PRIMARY KEY (`asset_id`) ) ENGINE = InnoDB; CREATE TABLE `vehicle_recs` ( `vehicle_rec_id` INT AUTO_INCREMENT NOT NULL, `vehicle_rec_type` VARCHAR(16) NULL DEFAULT NULL , `vehicle_rec_vehicle` INT NULL DEFAULT NULL , `vehicle_rec_date` DATETIME NULL DEFAULT NULL , `vehicle_rec_act_date` DATETIME NULL DEFAULT NULL , `vehicle_rec_mileage` INT NULL DEFAULT NULL , `vehicle_rec_status` VARCHAR(32) NULL DEFAULT NULL , `vehicle_rec_notes` VARCHAR(5000) NULL DEFAULT NULL , `vehicle_rec_created` DATETIME NULL DEFAULT NULL , `vehicle_rec_modified` DATETIME NULL DEFAULT NULL , `vehicle_rec_closed` DATETIME NULL DEFAULT NULL , PRIMARY KEY (`vehicle_rec_id`) ) ENGINE = InnoDB; CREATE TABLE `as_user_roles` ( `role_id` INT AUTO_INCREMENT NOT NULL, `role` VARCHAR(20) NOT NULL, PRIMARY KEY (`role_id`) ) ENGINE = InnoDB; CREATE TABLE `red_gas_appliances_mvhr` ( `mvhr_id` INT AUTO_INCREMENT NOT NULL, `mvhr_ref` INT NULL DEFAULT NULL , `mvhr_location` VARCHAR(64) NULL DEFAULT NULL , `mvhr_model` VARCHAR(64) NULL DEFAULT NULL , `mvhr_serial` VARCHAR(64) NULL DEFAULT NULL , `mvhr_heat_exchange` VARCHAR(4) NULL DEFAULT NULL , `mvhr_filters` VARCHAR(4) NULL DEFAULT NULL , `mvhr_controls` VARCHAR(4) NULL DEFAULT NULL , `mvhr_working` VARCHAR(4) NULL DEFAULT NULL , `mvhr_defects` TEXT NULL DEFAULT NULL , `mvhr_notes` TEXT NULL DEFAULT NULL , `mvhr_motors` VARCHAR(4) NULL DEFAULT NULL , `mvhr_diffusers` VARCHAR(4) NULL DEFAULT NULL , `mvhr_testing` VARCHAR(4) NULL DEFAULT NULL , PRIMARY KEY (`mvhr_id`) ) ENGINE = InnoDB; CREATE TABLE `invoices` ( `invoice_id` INT AUTO_INCREMENT NOT NULL COMMENT 'ID' , `invoice_ticket` INT NULL DEFAULT NULL COMMENT 'Ticket ID' , `invoice_ref` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Reference' , `invoice_v_ref` VARCHAR(128) NULL DEFAULT NULL COMMENT 'V Reference' , `invoice_value` DECIMAL(10,2) NULL DEFAULT NULL COMMENT 'Value' , `invoice_date` DATETIME NULL DEFAULT NULL COMMENT 'Invoice Date' , `invoice_note` BLOB NULL DEFAULT NULL COMMENT 'Note' , `invoice_created` DATETIME NULL DEFAULT NULL COMMENT 'Created Date' , `invoice_created_by` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Created By' , `invoice_modified` DATETIME NULL DEFAULT NULL COMMENT 'Modified Date' , `invoice_modified_by` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Modified By' , `invoice_doc` VARCHAR(16) NULL DEFAULT NULL COMMENT 'Opera DOC Ref' , `invoice_export` INT NULL DEFAULT 0 COMMENT 'Exported?' , `invoice_co` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Care Of' , `invoice_fao` VARCHAR(128) NULL DEFAULT NULL COMMENT 'For Attention Of' , `invoice_client_ref` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Client Reference' , PRIMARY KEY (`invoice_id`) ) ENGINE = InnoDB; CREATE TABLE `client_documents` ( `client_doc_id` INT AUTO_INCREMENT NOT NULL, `client_doc_client` INT NULL DEFAULT NULL , `client_doc_type` VARCHAR(32) NULL DEFAULT NULL , `client_doc_ticket` INT NULL DEFAULT NULL , `client_doc_date` DATETIME NULL DEFAULT NULL , `client_doc_title` VARCHAR(256) NULL DEFAULT NULL , `client_doc_file` VARCHAR(256) NULL DEFAULT NULL , PRIMARY KEY (`client_doc_id`) ) ENGINE = InnoDB; CREATE TABLE `red_gas_services` ( `serv_id` INT AUTO_INCREMENT NOT NULL, `serv_test` INT NULL DEFAULT NULL , `serv_loc` VARCHAR(256) NULL DEFAULT NULL , `serv_type` VARCHAR(128) NULL DEFAULT NULL , `serv_make` VARCHAR(128) NULL DEFAULT NULL , `serv_model` VARCHAR(128) NULL DEFAULT NULL , `serv_service_type` VARCHAR(32) NULL DEFAULT NULL , `serv_tightness` VARCHAR(8) NULL DEFAULT NULL , `serv_burner` INT NULL DEFAULT NULL , `serv_burner_action` BLOB NULL DEFAULT NULL , `serv_exchanger` INT NULL DEFAULT NULL , `serv_exchanger_action` BLOB NULL DEFAULT NULL , `serv_ignition` INT NULL DEFAULT NULL , `serv_ignition_action` BLOB NULL DEFAULT NULL , `serv_electrics` INT NULL DEFAULT NULL , `serv_electrics_action` BLOB NULL DEFAULT NULL , `serv_controls` VARCHAR(8) NULL DEFAULT NULL , `serv_controls_action` BLOB NULL DEFAULT NULL , `serv_leaks` VARCHAR(8) NULL DEFAULT NULL , `serv_leaks_action` BLOB NULL DEFAULT NULL , `serv_gas` VARCHAR(8) NULL DEFAULT NULL , `serv_gas_action` BLOB NULL DEFAULT NULL , `serv_seals` VARCHAR(8) NULL DEFAULT NULL , `serv_seals_action` BLOB NULL DEFAULT NULL , `serv_pipe` VARCHAR(8) NULL DEFAULT NULL , `serv_pipe_action` BLOB NULL DEFAULT NULL , `serv_fans` VARCHAR(8) NULL DEFAULT NULL , `serv_fans_action` BLOB NULL DEFAULT NULL , `serv_fireplace` VARCHAR(8) NULL DEFAULT NULL , `serv_fireplace_action` BLOB NULL DEFAULT NULL , `serv_plate` VARCHAR(8) NULL DEFAULT NULL , `serv_plate_action` BLOB NULL DEFAULT NULL , `serv_flame` VARCHAR(8) NULL DEFAULT NULL , `serv_flame_action` BLOB NULL DEFAULT NULL , `serv_location` VARCHAR(8) NULL DEFAULT NULL , `serv_location_action` BLOB NULL DEFAULT NULL , `serv_stability` VARCHAR(8) NULL DEFAULT NULL , `serv_stability_action` BLOB NULL DEFAULT NULL , `serv_return` VARCHAR(8) NULL DEFAULT NULL , `serv_return_action` BLOB NULL DEFAULT NULL , `serv_ventilation` VARCHAR(8) NULL DEFAULT NULL , `serv_ventilation_action` BLOB NULL DEFAULT NULL , `serv_flue_ventilation` VARCHAR(8) NULL DEFAULT NULL , `serv_flue_ventilation_action` BLOB NULL DEFAULT NULL , `serv_flue_flow` VARCHAR(8) NULL DEFAULT NULL , `serv_flue_flow_action` BLOB NULL DEFAULT NULL , `serv_spillage` VARCHAR(8) NULL DEFAULT NULL , `serv_spillage_action` BLOB NULL DEFAULT NULL , `serv_safety` VARCHAR(8) NULL DEFAULT NULL , `serv_safety_action` BLOB NULL DEFAULT NULL , `serv_op_pressure` VARCHAR(8) NULL DEFAULT NULL , `serv_serial` VARCHAR(64) NULL DEFAULT NULL , `serv_reading` VARCHAR(16) NULL DEFAULT NULL , `serv_co2_perc` VARCHAR(8) NULL DEFAULT NULL , `serv_co2_ppm` VARCHAR(8) NULL DEFAULT NULL , `serv_co2_ratio` VARCHAR(8) NULL DEFAULT NULL , `serv_safe` VARCHAR(8) NULL DEFAULT NULL , `serv_label` VARCHAR(8) NULL DEFAULT NULL , `serv_standards` VARCHAR(8) NULL DEFAULT NULL , `serv_work` BLOB NULL DEFAULT NULL , PRIMARY KEY (`serv_id`) ) ENGINE = InnoDB; CREATE TABLE `red_time_sites` ( `site_id` INT AUTO_INCREMENT NOT NULL, `site_hdt` VARCHAR(16) NULL DEFAULT NULL , `site_address` VARCHAR(512) NULL DEFAULT NULL , PRIMARY KEY (`site_id`) ) ENGINE = InnoDB; CREATE TABLE `tickets_recurring` ( `ticket_r_id` INT AUTO_INCREMENT NOT NULL, `ticket_r_ref` VARCHAR(10) NULL DEFAULT NULL , `ticket_r_title` VARCHAR(250) NULL DEFAULT NULL , `ticket_r_description` VARCHAR(1000) NULL DEFAULT NULL , `ticket_r_client` INT NULL DEFAULT NULL , `ticket_r_contact` INT NULL DEFAULT NULL , `ticket_r_address_1` VARCHAR(200) NULL DEFAULT NULL , `ticket_r_address_2` VARCHAR(200) NULL DEFAULT NULL , `ticket_r_town` VARCHAR(50) NULL DEFAULT NULL , `ticket_r_county` VARCHAR(32) NULL DEFAULT NULL , `ticket_r_postcode` VARCHAR(10) NULL DEFAULT NULL , `ticket_r_frequency` INT NULL DEFAULT NULL , `ticket_r_duration` VARCHAR(16) NULL DEFAULT NULL , `ticket_r_last` DATETIME NULL DEFAULT NULL , `ticket_r_next` DATETIME NULL DEFAULT NULL , `ticket_r_fault_code` INT NULL DEFAULT NULL , `ticket_r_status_code` VARCHAR(32) NULL DEFAULT NULL , `ticket_r_created` DATETIME NULL DEFAULT NULL , `ticket_r_logged_user` VARCHAR(32) NULL DEFAULT NULL , `ticket_r_modified` DATETIME NULL DEFAULT NULL , `ticket_r_modified_by` VARCHAR(32) NULL DEFAULT NULL , `ticket_r_closed` DATETIME NULL DEFAULT NULL , `ticket_r_closed_by` VARCHAR(32) NULL DEFAULT NULL , `ticket_r_deleted` DATETIME NULL DEFAULT NULL , `ticket_r_deleted_by` VARCHAR(32) NULL DEFAULT NULL , `ticket_r_deleted_flag` INT NULL DEFAULT 0 , `ticket_r_client_site` INT NULL DEFAULT NULL , PRIMARY KEY (`ticket_r_id`) ) ENGINE = InnoDB; CREATE TABLE `red_gas_ref` ( `id` INT AUTO_INCREMENT NOT NULL, `reference_type` VARCHAR(10) NULL DEFAULT NULL , `reference_code` VARCHAR(3) NULL DEFAULT NULL , `next_number` INT NULL DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; CREATE TABLE `schedule` ( `schedule_id` INT AUTO_INCREMENT NOT NULL, `schedule_resource` INT NULL DEFAULT NULL , `schedule_note` VARCHAR(1000) NULL DEFAULT NULL , `schedule_start` DATETIME NULL DEFAULT NULL , `schedule_end` DATETIME NULL DEFAULT NULL , PRIMARY KEY (`schedule_id`) ) ENGINE = InnoDB; CREATE TABLE `ra_hazard` ( `hbc_id` INT AUTO_INCREMENT NOT NULL, `hbc_risk` INT NULL DEFAULT NULL , `hbc_text` VARCHAR(1000) NULL DEFAULT NULL , `hbc_high` CHAR(1) NULL DEFAULT NULL , `hbc_med` CHAR(1) NULL DEFAULT NULL , `hbc_low` CHAR(1) NULL DEFAULT NULL , `hbc_high_a` CHAR(1) NULL DEFAULT NULL , `hbc_med_a` CHAR(1) NULL DEFAULT NULL , `hbc_low_a` CHAR(1) NULL DEFAULT NULL , PRIMARY KEY (`hbc_id`) ) ENGINE = InnoDB; CREATE TABLE `quote_activities` ( `qa_id` INT AUTO_INCREMENT NOT NULL, `qa_quote_id` INT NULL DEFAULT NULL , `qa_resource_id` INT NULL DEFAULT NULL , `qa_notes` BLOB NULL DEFAULT NULL , `qa_response` BLOB NULL DEFAULT NULL , `qa_deadline` DATETIME NULL DEFAULT NULL , `qa_status` VARCHAR(32) NULL DEFAULT NULL , `qa_priority` VARCHAR(32) NULL DEFAULT NULL , `qa_closed` DATETIME NULL DEFAULT NULL , `qa_created` DATETIME NULL DEFAULT NULL , `qa_modified` DATETIME NULL DEFAULT NULL , `qa_created_by` VARCHAR(32) NULL DEFAULT NULL , `qa_modified_by` VARCHAR(32) NULL DEFAULT NULL , `qa_email` VARCHAR(128) NULL DEFAULT NULL , PRIMARY KEY (`qa_id`) ) ENGINE = InnoDB; CREATE TABLE `report_images` ( `ri_id` INT AUTO_INCREMENT NOT NULL, `ri_ticket` INT NULL DEFAULT NULL , `ri_url` VARCHAR(1000) NULL DEFAULT NULL , PRIMARY KEY (`ri_id`) ) ENGINE = InnoDB; CREATE TABLE `activities_block` ( `ab_id` INT AUTO_INCREMENT NOT NULL, `ab_resource_id` INT NULL DEFAULT NULL , `ab_notes` VARCHAR(2000) NULL DEFAULT NULL , `ab_start` DATETIME NULL DEFAULT NULL , `ab_end` DATETIME NULL DEFAULT NULL , PRIMARY KEY (`ab_id`) ) ENGINE = InnoDB; CREATE TABLE `as_social_logins` ( `id` INT AUTO_INCREMENT NOT NULL, `user_id` INT NOT NULL, `provider` VARCHAR(50) NULL DEFAULT '\'email'' , `provider_id` VARCHAR(250) NULL DEFAULT NULL , `created_at` DATETIME NULL DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; CREATE TABLE `quotes` ( `quote_id` INT AUTO_INCREMENT NOT NULL, `quote_ref` VARCHAR(20) NULL DEFAULT NULL , `quote_revision` INT NULL DEFAULT 1 , `quote_client` INT NULL DEFAULT NULL , `quote_contact` INT NULL DEFAULT NULL , `quote_title` VARCHAR(1000) NULL DEFAULT NULL , `quote_description` VARCHAR(5000) NULL DEFAULT NULL , `quote_status` VARCHAR(32) NULL DEFAULT NULL , `quote_outcome` VARCHAR(32) NULL DEFAULT NULL , `quote_assigned` INT NULL DEFAULT NULL , `quote_due` DATETIME NULL DEFAULT NULL , `quote_chase` DATETIME NULL DEFAULT NULL , `quote_address_1` VARCHAR(128) NULL DEFAULT NULL , `quote_address_2` VARCHAR(128) NULL DEFAULT NULL , `quote_town` VARCHAR(50) NULL DEFAULT NULL , `quote_county` VARCHAR(50) NULL DEFAULT NULL , `quote_postcode` VARCHAR(10) NULL DEFAULT NULL , `quote_logged_user` VARCHAR(32) NULL DEFAULT NULL , `quote_created` DATETIME NULL DEFAULT NULL , `quote_modified_by` VARCHAR(32) NULL DEFAULT NULL , `quote_modified` DATETIME NULL DEFAULT NULL , `quote_closed_by` VARCHAR(32) NULL DEFAULT NULL , `quote_closed` DATETIME NULL DEFAULT NULL , `quote_deleted_by` VARCHAR(32) NULL DEFAULT NULL , `quote_deleted` DATETIME NULL DEFAULT NULL , `quote_deleted_flag` INT NULL DEFAULT NULL , `quote_preamble` VARCHAR(5000) NULL DEFAULT '\'We thank you for your recent enquiry and have pleasure in submitting our quotation as detailed below.'' , `quote_postamble` VARCHAR(5000) NULL DEFAULT '\'We trust that the above is satisfactory and we look forward to your further instructions.'' , `quote_client_site` INT NULL DEFAULT NULL , `quote_value` DECIMAL(10,2) NULL DEFAULT NULL , `quote_multi_sites` INT NULL DEFAULT NULL , `quote_category` INT NULL DEFAULT NULL , `quote_won_revision` INT NULL DEFAULT NULL , `quote_source_ticket` INT NULL DEFAULT NULL , PRIMARY KEY (`quote_id`) ) ENGINE = InnoDB; CREATE TABLE `clients` ( `client_id` INT AUTO_INCREMENT NOT NULL COMMENT 'ID' , `client_ref` VARCHAR(10) NOT NULL COMMENT 'Reference' , `client_name` VARCHAR(200) NOT NULL COMMENT 'Name' , `client_alias` VARCHAR(200) NULL DEFAULT NULL COMMENT 'Alias' , `client_phone` VARCHAR(20) NULL DEFAULT NULL COMMENT 'Phone' , `client_email` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Email' , `client_website` VARCHAR(256) NULL DEFAULT NULL COMMENT 'Wesbite URL' , `client_office_address_1` VARCHAR(200) NULL DEFAULT NULL COMMENT 'Office Address 1' , `client_office_address_2` VARCHAR(200) NULL DEFAULT NULL COMMENT 'Office Address 2' , `client_office_po_box` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Office PO Box' , `client_office_town` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Office Town' , `client_office_county` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Office County' , `client_office_postcode` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Office Postcode' , `client_invoice_address_1` VARCHAR(200) NULL DEFAULT NULL COMMENT 'Invoice Address 1' , `client_invoice_address_2` VARCHAR(200) NULL DEFAULT NULL COMMENT 'Invoice Address 2' , `client_invoice_po_box` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Invoice PO Box' , `client_invoice_town` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Invoice Town' , `client_invoice_county` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Invoice County' , `client_invoice_postcode` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Invoice Postcode' , `client_created` DATETIME NULL DEFAULT NULL COMMENT 'Created Date' , `client_modified` DATETIME NULL DEFAULT NULL COMMENT 'Modified Date' , `client_deleted` DATETIME NULL DEFAULT NULL COMMENT 'Deleted Date' , `client_deleted_flag` INT NULL DEFAULT 0 COMMENT 'Deletion Flag' , `client_created_by` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Created By' , `client_modified_by` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Modified By' , `client_deleted_by` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Deleted By' , `client_opera_ref` VARCHAR(16) NULL DEFAULT NULL COMMENT 'Opera Reference' , `client_active` INT NULL DEFAULT 1 , PRIMARY KEY (`client_id`) ) ENGINE = InnoDB; CREATE TABLE `ra_measures` ( `measure_id` INT AUTO_INCREMENT NOT NULL, `measure_risk` INT NULL DEFAULT NULL , `measure_text` VARCHAR(1000) NULL DEFAULT NULL , PRIMARY KEY (`measure_id`) ) ENGINE = InnoDB; CREATE TABLE `red_drain_codes` ( `codes_id` INT AUTO_INCREMENT NOT NULL, `code_ref` VARCHAR(16) NULL DEFAULT NULL , `code_description` VARCHAR(128) NULL DEFAULT NULL , `code_short_description` VARCHAR(128) NULL DEFAULT NULL , PRIMARY KEY (`codes_id`) ) ENGINE = MyISAM; CREATE TABLE `ra_monitor` ( `mon_id` INT AUTO_INCREMENT NOT NULL, `mon_risk` INT NULL DEFAULT NULL , `mon_text` VARCHAR(1000) NULL DEFAULT NULL , PRIMARY KEY (`mon_id`) ) ENGINE = InnoDB; CREATE TABLE `ticket_notes` ( `ticket_notes_id` INT AUTO_INCREMENT NOT NULL, `ticket_notes_ticket` INT NULL DEFAULT NULL , `ticket_notes_r_ticket` INT NULL DEFAULT NULL , `ticket_notes_date` DATETIME NULL DEFAULT NULL , `ticket_notes_user` VARCHAR(32) NULL DEFAULT NULL , `ticket_notes_text` VARCHAR(5000) NULL DEFAULT NULL , `ticket_notes_type` VARCHAR(1) NULL DEFAULT NULL , PRIMARY KEY (`ticket_notes_id`) ) ENGINE = InnoDB; CREATE TABLE `ticket_pat` ( `ticket_pat_id` INT AUTO_INCREMENT NOT NULL, `ticket_pat_ticket` INT NULL DEFAULT NULL , `ticket_pat_ref` VARCHAR(16) NULL DEFAULT NULL , `ticket_pat_address` VARCHAR(256) NULL DEFAULT NULL , PRIMARY KEY (`ticket_pat_id`) ) ENGINE = InnoDB; CREATE TABLE `red_pat_ref` ( `id` INT AUTO_INCREMENT NOT NULL, `reference_type` VARCHAR(10) NULL DEFAULT NULL , `reference_code` VARCHAR(3) NULL DEFAULT NULL , `next_number` INT NULL DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; CREATE TABLE `ticket_full_notes` ( `ticket_full_notes_id` INT AUTO_INCREMENT NOT NULL, `ticket_full_notes_ticket` INT NULL DEFAULT NULL , `ticket_full_notes_text` VARCHAR(5000) NULL DEFAULT NULL , PRIMARY KEY (`ticket_full_notes_id`) ) ENGINE = InnoDB; CREATE TABLE `documents` ( `doc_id` INT AUTO_INCREMENT NOT NULL, `doc_ref` VARCHAR(8) NULL DEFAULT NULL , `doc_title` VARCHAR(256) NULL DEFAULT NULL , `doc_url` VARCHAR(256) NULL DEFAULT '\'docs/risk/'' , `doc_type` VARCHAR(16) NULL DEFAULT '\'Risk'' , `doc_rev_date` DATETIME NULL DEFAULT NULL , `doc_pdf` VARCHAR(256) NULL DEFAULT NULL , `doc_roo` INT NULL DEFAULT NULL , `doc_rub` INT NULL DEFAULT NULL , `doc_sca` INT NULL DEFAULT NULL , `doc_til` INT NULL DEFAULT NULL , `doc_pre` INT NULL DEFAULT NULL , `doc_aah` INT NULL DEFAULT NULL , `doc_app` INT NULL DEFAULT NULL , `doc_bri` INT NULL DEFAULT NULL , `doc_car` INT NULL DEFAULT NULL , `doc_dec` INT NULL DEFAULT NULL , `doc_dra` INT NULL DEFAULT NULL , `doc_ele` INT NULL DEFAULT NULL , `doc_eme` INT NULL DEFAULT NULL , `doc_fir` INT NULL DEFAULT NULL , `doc_flo` INT NULL DEFAULT NULL , `doc_plu` INT NULL DEFAULT NULL , `doc_gas` INT NULL DEFAULT NULL , `doc_gpt` INT NULL DEFAULT NULL , `doc_gen` INT NULL DEFAULT NULL , `doc_gla` INT NULL DEFAULT NULL , `doc_gro` INT NULL DEFAULT NULL , `doc_lea` INT NULL DEFAULT NULL , `doc_loc` INT NULL DEFAULT NULL , `doc_pat` INT NULL DEFAULT NULL , `doc_gah` VARCHAR(1) NULL DEFAULT NULL , `doc_ahu` VARCHAR(1) NULL DEFAULT NULL , `doc_air` INT NULL DEFAULT NULL , `doc_quo` INT NULL DEFAULT NULL , `doc_itc` INT NULL DEFAULT NULL , PRIMARY KEY (`doc_id`) ) ENGINE = InnoDB; CREATE TABLE `ra_risks` ( `risk_id` INT AUTO_INCREMENT NOT NULL, `risk_risk` INT NULL DEFAULT NULL , `risk_text` VARCHAR(128) NULL DEFAULT NULL , PRIMARY KEY (`risk_id`) ) ENGINE = InnoDB; CREATE TABLE `work_log` ( `wl_id` INT AUTO_INCREMENT NOT NULL, `wl_activity` VARCHAR(10) NOT NULL, `wl_date` DATETIME NULL DEFAULT NULL , `wl_description` TEXT NULL DEFAULT NULL , `wl_complete` VARCHAR(3) NULL DEFAULT NULL , `wl_cost` DOUBLE NULL DEFAULT NULL , `wl_resource` INT NULL DEFAULT NULL , `wl_confirm` INT NULL DEFAULT 1 , PRIMARY KEY (`wl_id`) ) ENGINE = MyISAM; CREATE TABLE `red_gas_alarms` ( `alarm_id` INT AUTO_INCREMENT NOT NULL, `alarm_test` INT NULL DEFAULT NULL , `alarm_asset` INT NULL DEFAULT NULL , `alarm_type` VARCHAR(63) NULL DEFAULT NULL , `alarm_location` VARCHAR(64) NULL DEFAULT NULL , `alarm_make` VARCHAR(64) NULL DEFAULT NULL , `alarm_model` VARCHAR(64) NULL DEFAULT NULL , `alarm_install` DATETIME NULL DEFAULT NULL , `alarm_replace` DATETIME NULL DEFAULT NULL , `alarm_visual` VARCHAR(64) NULL DEFAULT NULL , `alarm_smoke` VARCHAR(8) NULL DEFAULT NULL , `alarm_co` VARCHAR(8) NULL DEFAULT NULL , `alarm_replaced` VARCHAR(3) NULL DEFAULT NULL , `alarm_defect` VARCHAR(500) NULL DEFAULT NULL , `alarm_warning` VARCHAR(3) NULL DEFAULT NULL , PRIMARY KEY (`alarm_id`) ) ENGINE = MyISAM; CREATE TABLE `red_drain_sections` ( `section_id` INT AUTO_INCREMENT NOT NULL, `section_survey` INT NULL DEFAULT NULL , `section_from` VARCHAR(64) NULL DEFAULT NULL , `section_to` VARCHAR(64) NULL DEFAULT NULL , `section_direction` VARCHAR(16) NULL DEFAULT NULL , `section_length` DECIMAL(10,2) NULL DEFAULT 0.00 , `section_depth` DECIMAL(10,2) NULL DEFAULT NULL , `section_material` VARCHAR(32) NULL DEFAULT NULL , `section_diameter` INT NULL DEFAULT NULL , `section_duty` VARCHAR(32) NULL DEFAULT NULL , `section_shape` VARCHAR(32) NULL DEFAULT NULL , `section_conclusion` VARCHAR(1000) NULL DEFAULT '\'No defects detected'' , PRIMARY KEY (`section_id`) ) ENGINE = MyISAM; CREATE TABLE `on_hold_codes` ( `ticket_on_hold_id` INT AUTO_INCREMENT NOT NULL, `ticket_on_hold_code` VARCHAR(6) NOT NULL, `ticket_on_hold_description` VARCHAR(32) NOT NULL, PRIMARY KEY (`ticket_on_hold_id`) ) ENGINE = InnoDB; CREATE TABLE `as_users` ( `user_id` INT AUTO_INCREMENT NOT NULL, `email` VARCHAR(40) NOT NULL, `username` VARCHAR(250) NOT NULL, `password` VARCHAR(250) NOT NULL, `confirmation_key` VARCHAR(40) NOT NULL, `confirmed` ENUM('Y','N') NOT NULL DEFAULT '\'N'' , `password_reset_key` VARCHAR(250) NOT NULL DEFAULT '\''' , `password_reset_confirmed` ENUM('Y','N') NOT NULL DEFAULT '\'N'' , `password_reset_timestamp` DATETIME NULL DEFAULT NULL , `register_date` DATE NOT NULL, `user_role` INT NOT NULL DEFAULT 1 , `last_login` DATETIME NULL DEFAULT NULL , `banned` ENUM('Y','N') NOT NULL DEFAULT '\'N'' , PRIMARY KEY (`user_id`), CONSTRAINT `username` UNIQUE (`username`) ) ENGINE = InnoDB; CREATE TABLE `engineers` ( `engineer_id` INT AUTO_INCREMENT NOT NULL COMMENT 'Engineer ID' , `engineer_name` VARCHAR(64) NULL DEFAULT NULL COMMENT 'Engineer Name' , `engineer_mobile` VARCHAR(20) NULL DEFAULT NULL COMMENT 'Mobile Phone' , `engineer_phone` VARCHAR(20) NULL DEFAULT NULL COMMENT 'Home Phone' , `engineer_address_1` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Street Address 1' , `engineer_address_2` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Street Address 2' , `engineer_town` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Town' , `engineer_postcode` VARCHAR(12) NULL DEFAULT NULL COMMENT 'Postcode' , `engineer_contact_1` VARCHAR(64) NULL DEFAULT NULL COMMENT 'Emergency Contact 1' , `engineer_phone_1` VARCHAR(20) NULL DEFAULT NULL COMMENT 'Emergency Contact 1 Phone' , `engineer_mobile_1` VARCHAR(20) NULL DEFAULT NULL COMMENT 'Emergency Contact 1 Mobile' , `engineer_rel_1` VARCHAR(20) NULL DEFAULT NULL COMMENT 'Relationship' , `engineer_con1_address_1` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Emergency Contact 1 Address' , `engineer_con1_address_2` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Emergency Contact 1 Address' , `engineer_con1_town` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Emergency Contact 1 Address' , `engineer_con1_postcode` VARCHAR(12) NULL DEFAULT NULL COMMENT 'Emergency Contact 1 Address' , `engineer_contact_2` VARCHAR(64) NULL DEFAULT NULL COMMENT 'Emergency Contact 2' , `engineer_phone_2` VARCHAR(20) NULL DEFAULT NULL COMMENT 'Emergency Contact 2 Phone' , `engineer_mobile_2` VARCHAR(20) NULL DEFAULT NULL COMMENT 'Emergency Contact 2 Mobile' , `engineer_rel_2` VARCHAR(20) NULL DEFAULT NULL COMMENT 'Relationship' , `engineer_con2_address_1` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Emergency Contact 2 Address' , `engineer_con2_address_2` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Emergency Contact 2 Address' , `engineer_con2_town` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Emergency Contact 2 Address' , `engineer_con2_postcode` VARCHAR(12) NULL DEFAULT NULL COMMENT 'Emergency Contact 2 Address' , `engineer_asbestos` INT NULL DEFAULT NULL COMMENT 'Asbestos' , `engineer_asbestos_expire` DATETIME NULL DEFAULT NULL COMMENT 'Expiry Date' , `engineer_height` INT NULL DEFAULT NULL COMMENT 'Working At Height' , `engineer_height_expire` DATETIME NULL DEFAULT NULL COMMENT 'Expiry Date' , `engineer_tower` INT NULL DEFAULT NULL COMMENT 'Tower' , `engineer_tower_expire` DATETIME NULL DEFAULT NULL COMMENT 'Expiry Date' , `engineer_harness` INT NULL DEFAULT NULL COMMENT 'Harness' , `engineer_harness_expire` DATETIME NULL DEFAULT NULL COMMENT 'Expiry Date' , `engineer_pat` INT NULL DEFAULT NULL COMMENT 'PAT Testing' , `engineer_pat_expire` DATETIME NULL DEFAULT NULL COMMENT 'Expiry Date' , `engineer_electrical` INT NULL DEFAULT NULL COMMENT 'Electrical' , `engineer_electrical_expire` DATETIME NULL DEFAULT NULL COMMENT 'Expiry Date' , `engineer_first_aid` INT NULL DEFAULT NULL COMMENT 'First Aid' , `engineer_first_aid_expire` DATETIME NULL DEFAULT NULL COMMENT 'Expiry Date' , PRIMARY KEY (`engineer_id`) ) ENGINE = InnoDB; CREATE TABLE `timesheets` ( `timesheet_id` INT AUTO_INCREMENT NOT NULL COMMENT 'Timesheet ID' , `timesheet_activity` INT NULL DEFAULT NULL COMMENT 'Activity ID' , `timesheet_ticket` INT NULL DEFAULT NULL , `timesheet_date` DATETIME NULL DEFAULT NULL COMMENT 'Date' , `timesheet_time` TIME NULL DEFAULT NULL COMMENT 'Hours' , `timesheet_notes` VARCHAR(5000) NULL DEFAULT NULL COMMENT 'Notes' , `timesheet_reduced_hour` VARCHAR(3) NULL DEFAULT NULL COMMENT 'Reduced Hour' , `timesheet_created` DATETIME NULL DEFAULT NULL COMMENT 'Created Date' , `timesheet_created_by` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Created By' , `timesheet_modified` DATETIME NULL DEFAULT NULL COMMENT 'Modified Date' , `timesheet_modified_by` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Modified By' , `timesheet_mech_works` VARCHAR(3) NULL DEFAULT NULL COMMENT 'Mechanical Works' , `timesheet_quoted` VARCHAR(4) NULL DEFAULT NULL COMMENT 'Quoted Works' , `timesheet_quoted_value` DECIMAL(10,2) NULL DEFAULT NULL COMMENT 'Quoted Value' , `timesheet_contractor` VARCHAR(4) NULL DEFAULT NULL COMMENT 'Contractor Work' , `timesheet_contractor_cost` DECIMAL(10,2) NULL DEFAULT NULL COMMENT 'Contractor Cost' , `timesheet_contractor_value` DECIMAL(10,2) NULL DEFAULT NULL COMMENT 'Contractor Value' , `timesheet_reduced_rate` VARCHAR(4) NULL DEFAULT NULL COMMENT 'Reduced Rate' , `timesheet_reduced_value` DECIMAL(10,2) NULL DEFAULT NULL COMMENT 'Reduced Rate Value' , `timesheet_fixed` VARCHAR(4) NULL DEFAULT NULL COMMENT 'Fixed Price' , `timesheet_fixed_value` DECIMAL(10,2) NULL DEFAULT NULL COMMENT 'Fixed Price Value' , `timesheet_half_hour` VARCHAR(3) NULL DEFAULT NULL COMMENT 'Final Half Hour Rate' , `timesheet_completed` VARCHAR(3) NULL DEFAULT NULL , `timesheet_evening` TIME NULL DEFAULT '\'00:00:00'' , `timesheet_materials` VARCHAR(3) NULL DEFAULT NULL , `timesheet_materials_used` VARCHAR(5000) NULL DEFAULT NULL , `timesheet_confirmed` INT NULL DEFAULT NULL , `timesheet_ad_hoc_site` VARCHAR(250) NULL DEFAULT NULL , PRIMARY KEY (`timesheet_id`) ) ENGINE = InnoDB; CREATE TABLE `reference_numbers` ( `id` INT AUTO_INCREMENT NOT NULL, `reference_type` VARCHAR(10) NOT NULL, `reference_code` VARCHAR(3) NOT NULL, `next_number` INT NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB; CREATE TABLE `red_itc_records` ( `itc_id` INT AUTO_INCREMENT NOT NULL, `itc_ref` VARCHAR(16) NULL DEFAULT NULL , `itc_address` VARCHAR(512) NULL DEFAULT NULL , `itc_date` DATE NULL DEFAULT NULL , `itc_client` VARCHAR(128) NULL DEFAULT NULL , `itc_hdt` INT NULL DEFAULT NULL , `itc_engineer` VARCHAR(64) NULL DEFAULT NULL , `itc_items` VARCHAR(4) NULL DEFAULT NULL , `itc_items_notes` TEXT NULL DEFAULT NULL , `itc_paint` VARCHAR(4) NULL DEFAULT NULL , `itc_paint_notes` TEXT NULL DEFAULT NULL , `itc_blinds` VARCHAR(4) NULL DEFAULT NULL , `itc_blinds_notes` TEXT NULL DEFAULT NULL , `itc_floor` VARCHAR(4) NULL DEFAULT NULL , `itc_floor_notes` TEXT NULL DEFAULT NULL , `itc_lights` VARCHAR(4) NULL DEFAULT NULL , `itc_lights_notes` TEXT NULL DEFAULT NULL , `itc_door` VARCHAR(4) NULL DEFAULT NULL , `itc_door_notes` TEXT NULL DEFAULT NULL , `itc_locks` VARCHAR(4) NULL DEFAULT NULL , `itc_locks_notes` TEXT NULL DEFAULT NULL , `itc_glazing` VARCHAR(4) NULL DEFAULT NULL , `itc_glazing_notes` TEXT NULL DEFAULT NULL , `itc_glazing2` VARCHAR(4) NULL DEFAULT NULL , `itc_glazing2_notes` TEXT NULL DEFAULT NULL , `itc_iron` VARCHAR(4) NULL DEFAULT NULL , `itc_iron_notes` TEXT NULL DEFAULT NULL , `itc_appliances` VARCHAR(4) NULL DEFAULT NULL , `itc_appliances_notes` TEXT NULL DEFAULT NULL , `itc_kitchen` VARCHAR(4) NULL DEFAULT NULL , `itc_kitchen_notes` TEXT NULL DEFAULT NULL , `itc_cupboards` VARCHAR(4) NULL DEFAULT NULL , `itc_cupboards_notes` TEXT NULL DEFAULT NULL , `itc_tiling` VARCHAR(4) NULL DEFAULT NULL , `itc_tiling_notes` TEXT NULL DEFAULT NULL , `itc_taps` VARCHAR(4) NULL DEFAULT NULL , `itc_taps_notes` TEXT NULL DEFAULT NULL , `itc_shower` VARCHAR(4) NULL DEFAULT NULL , `itc_shower_notes` TEXT NULL DEFAULT NULL , `itc_drain` VARCHAR(4) NULL DEFAULT NULL , `itc_drain_notes` TEXT NULL DEFAULT NULL , `itc_extract` VARCHAR(4) NULL DEFAULT NULL , `itc_extract_notes` TEXT NULL DEFAULT NULL , `itc_sealant` VARCHAR(4) NULL DEFAULT NULL , `itc_sealant_notes` TEXT NULL DEFAULT NULL , `itc_alarm` VARCHAR(4) NULL DEFAULT NULL , `itc_alarm_notes` TEXT NULL DEFAULT NULL , `itc_clean` VARCHAR(4) NULL DEFAULT NULL , `itc_clean_notes` TEXT NULL DEFAULT NULL , `itc_door2` VARCHAR(4) NULL DEFAULT NULL , `itc_door2_notes` TEXT NULL DEFAULT NULL , `itc_boiler` VARCHAR(4) NULL DEFAULT NULL , `itc_boiler_notes` TEXT NULL DEFAULT NULL , `itc_radiators` VARCHAR(4) NULL DEFAULT NULL , `itc_radiators_notes` TEXT NULL DEFAULT NULL , `itc_extras` TEXT NULL DEFAULT NULL , `itc_bedrooms` INT NULL DEFAULT NULL , `itc_bathrooms` INT NULL DEFAULT NULL , `itc_walls` VARCHAR(4) NULL DEFAULT NULL , `itc_walls_notes` TEXT NULL DEFAULT NULL , `itc_external` VARCHAR(4) NULL DEFAULT NULL , `itc_external_notes` TEXT NULL DEFAULT NULL , `itc_intercom` VARCHAR(4) NULL DEFAULT NULL , `itc_intercom_notes` TEXT NULL DEFAULT NULL , `itc_sills` VARCHAR(4) NULL DEFAULT NULL , `itc_sills_notes` TEXT NULL DEFAULT NULL , `itc_drawers` VARCHAR(4) NULL DEFAULT NULL , `itc_drawers_notes` TEXT NULL DEFAULT NULL , `itc_tiles` VARCHAR(4) NULL DEFAULT NULL , `itc_tiles_notes` TEXT NULL DEFAULT NULL , `itc_toilet` VARCHAR(4) NULL DEFAULT NULL , `itc_toilet_notes` TEXT NULL DEFAULT NULL , `itc_bath_lock` VARCHAR(4) NULL DEFAULT NULL , `itc_bath_lock_notes` TEXT NULL DEFAULT NULL , `itc_towel` VARCHAR(4) NULL DEFAULT NULL , `itc_towel_notes` TEXT NULL DEFAULT NULL , `itc_bath_furniture` VARCHAR(4) NULL DEFAULT NULL , `itc_bath_furniture_notes` TEXT NULL DEFAULT NULL , `itc_bath_damage` VARCHAR(4) NULL DEFAULT NULL , `itc_bath_damage_notes` TEXT NULL DEFAULT NULL , `itc_bed_damage` VARCHAR(4) NULL DEFAULT NULL , `itc_bed_damage_notes` TEXT NULL DEFAULT NULL , `itc_therm` VARCHAR(4) NULL DEFAULT NULL , `itc_therm_notes` TEXT NULL DEFAULT NULL , `itc_underfloor` VARCHAR(4) NULL DEFAULT NULL , `itc_underfloor_notes` TEXT NULL DEFAULT NULL , `itc_water` VARCHAR(4) NULL DEFAULT NULL , `itc_water_notes` TEXT NULL DEFAULT NULL , `itc_pressure` VARCHAR(4) NULL DEFAULT NULL , `itc_pressure_notes` TEXT NULL DEFAULT NULL , `itc_leaks` VARCHAR(4) NULL DEFAULT NULL , `itc_leaks_notes` TEXT NULL DEFAULT NULL , `itc_sockets` VARCHAR(4) NULL DEFAULT NULL , `itc_sockets_notes` TEXT NULL DEFAULT NULL , `itc_co` VARCHAR(4) NULL DEFAULT NULL , `itc_co_notes` TEXT NULL DEFAULT NULL , `itc_oven` VARCHAR(4) NULL DEFAULT NULL , `itc_oven_notes` TEXT NULL DEFAULT NULL , `itc_washer` VARCHAR(4) NULL DEFAULT NULL , `itc_washer_notes` TEXT NULL DEFAULT NULL , `itc_microwave` VARCHAR(4) NULL DEFAULT NULL , `itc_microwave_notes` TEXT NULL DEFAULT NULL , `itc_fridge` VARCHAR(4) NULL DEFAULT NULL , `itc_fridge_notes` TEXT NULL DEFAULT NULL , `itc_freezer` VARCHAR(4) NULL DEFAULT NULL , `itc_freezer_notes` TEXT NULL DEFAULT NULL , `itc_hood` VARCHAR(4) NULL DEFAULT NULL , `itc_hood_notes` TEXT NULL DEFAULT NULL , `itc_sink` VARCHAR(4) NULL DEFAULT NULL , `itc_sink_notes` TEXT NULL DEFAULT NULL , `itc_alarm_1` VARCHAR(8) NULL DEFAULT NULL , `itc_alarm_date_1` DATE NULL DEFAULT NULL , `itc_alarm_status_1` VARCHAR(20) NULL DEFAULT NULL , `itc_alarm_2` VARCHAR(8) NULL DEFAULT NULL , `itc_alarm_date_2` DATE NULL DEFAULT NULL , `itc_alarm_status_2` VARCHAR(20) NULL DEFAULT NULL , `itc_alarm_3` VARCHAR(8) NULL DEFAULT NULL , `itc_alarm_date_3` DATE NULL DEFAULT NULL , `itc_alarm_status_3` VARCHAR(20) NULL DEFAULT NULL , `itc_alarm_4` VARCHAR(8) NULL DEFAULT NULL , `itc_alarm_date_4` DATE NULL DEFAULT NULL , `itc_alarm_status_4` VARCHAR(20) NULL DEFAULT NULL , `itc_alarm_5` VARCHAR(8) NULL DEFAULT NULL , `itc_alarm_date_5` DATE NULL DEFAULT NULL , `itc_alarm_status_5` VARCHAR(20) NULL DEFAULT NULL , `itc_alarm_6` VARCHAR(8) NULL DEFAULT NULL , `itc_alarm_date_6` DATE NULL DEFAULT NULL , `itc_alarm_status_6` VARCHAR(20) NULL DEFAULT NULL , `itc_alarm_7` VARCHAR(8) NULL DEFAULT NULL , `itc_alarm_date_7` DATE NULL DEFAULT NULL , `itc_alarm_status_7` VARCHAR(20) NULL DEFAULT NULL , `itc_alarm_8` VARCHAR(8) NULL DEFAULT NULL , `itc_alarm_date_8` DATE NULL DEFAULT NULL , `itc_alarm_status_8` VARCHAR(20) NULL DEFAULT NULL , `itc_mains_alarm` VARCHAR(8) NULL DEFAULT NULL , `itc_gas_serial` VARCHAR(64) NULL DEFAULT NULL , `itc_gas_reading` VARCHAR(32) NULL DEFAULT NULL , `itc_water_serial` VARCHAR(64) NULL DEFAULT NULL , `itc_water_reading` VARCHAR(32) NULL DEFAULT NULL , `itc_electricity_serial` VARCHAR(64) NULL DEFAULT NULL , `itc_electricity_reading` VARCHAR(32) NULL DEFAULT NULL , `itc_fire_door` VARCHAR(4) NULL DEFAULT NULL , `itc_fire_door_notes` TEXT NULL DEFAULT NULL , `itc_restrictors` VARCHAR(4) NULL DEFAULT NULL , `itc_restrictors_notes` TEXT NULL DEFAULT NULL , PRIMARY KEY (`itc_id`) ) ENGINE = InnoDB; CREATE TABLE `activities` ( `activity_id` INT AUTO_INCREMENT NOT NULL, `activity_ref` VARCHAR(16) NULL DEFAULT NULL , `activity_ticket_id` INT NULL DEFAULT NULL , `activity_resource_id` INT NULL DEFAULT NULL , `activity_notes` VARCHAR(2000) NULL DEFAULT NULL , `activity_start` DATETIME NULL DEFAULT NULL , `activity_end` DATETIME NULL DEFAULT NULL , `activity_planned` VARCHAR(8) NULL DEFAULT NULL , `activity_status` VARCHAR(32) NULL DEFAULT NULL , `activity_priority` VARCHAR(32) NULL DEFAULT NULL , `activity_closed` DATETIME NULL DEFAULT NULL , `activity_created` DATETIME NULL DEFAULT NULL , `activity_modified` DATETIME NULL DEFAULT NULL , `activity_created_by` VARCHAR(32) NULL DEFAULT NULL , `activity_modified_by` VARCHAR(32) NULL DEFAULT NULL , `activity_email` VARCHAR(128) NULL DEFAULT NULL , `activity_timesheet` VARCHAR(3) NULL DEFAULT NULL , `activity_expense` INT NULL DEFAULT NULL , `activity_active_days` INT NULL DEFAULT NULL , `activity_active_flag` VARCHAR(10) NULL DEFAULT '\'Active'' , `activity_external_email` VARCHAR(128) NULL DEFAULT NULL , `activity_confirmed` INT NULL DEFAULT NULL , PRIMARY KEY (`activity_id`) ) ENGINE = InnoDB; CREATE TABLE `ticket_fault_codes` ( `ticket_fault_id` INT AUTO_INCREMENT NOT NULL, `ticket_fault_code` VARCHAR(6) NOT NULL, `ticket_fault_description` TEXT NOT NULL, PRIMARY KEY (`ticket_fault_id`) ) ENGINE = InnoDB; CREATE TABLE `red_me_notes` ( `note_id` INT AUTO_INCREMENT NOT NULL, `note_portfolio` INT NULL DEFAULT NULL , `note_text` TEXT NULL DEFAULT NULL , `note_date` DATETIME NULL DEFAULT NULL , `note_user` VARCHAR(32) NULL DEFAULT NULL , PRIMARY KEY (`note_id`) ) ENGINE = InnoDB; CREATE TABLE `red_gas_appliances_main` ( `mapp_id` INT AUTO_INCREMENT NOT NULL, `mapp_main_ref` INT NULL DEFAULT NULL , `mapp_name` VARCHAR(64) NULL DEFAULT NULL , `mapp_location` VARCHAR(64) NULL DEFAULT NULL , `mapp_pipework` VARCHAR(4) NULL DEFAULT NULL , `mapp_waste` VARCHAR(3) NULL DEFAULT NULL , `mapp_drain` VARCHAR(64) NULL DEFAULT NULL , `mapp_shower` VARCHAR(64) NULL DEFAULT NULL , PRIMARY KEY (`mapp_id`) ) ENGINE = InnoDB; CREATE TABLE `ticket_drain` ( `ticket_drain_id` INT AUTO_INCREMENT NOT NULL, `ticket_drain_ticket` INT NULL DEFAULT NULL , `ticket_drain_ref` VARCHAR(32) NULL DEFAULT NULL , PRIMARY KEY (`ticket_drain_id`) ) ENGINE = InnoDB; CREATE TABLE `ra_health` ( `health_id` INT AUTO_INCREMENT NOT NULL, `health_risk` INT NULL DEFAULT NULL , `health_text` VARCHAR(1000) NULL DEFAULT NULL , PRIMARY KEY (`health_id`) ) ENGINE = InnoDB; CREATE TABLE `red_me_portfolios` ( `port_id` INT AUTO_INCREMENT NOT NULL, `port_ref` VARCHAR(16) NULL DEFAULT NULL , `port_name` VARCHAR(128) NULL DEFAULT NULL , `port_client` INT NULL DEFAULT NULL , `port_contact` INT NULL DEFAULT NULL , `port_status` VARCHAR(16) NULL DEFAULT NULL , `port_date` DATETIME NULL DEFAULT NULL , `port_light_freq` INT NULL DEFAULT 0 , `port_flush_freq` INT NULL DEFAULT 0 , `port_temp_freq` INT NULL DEFAULT 0 , `port_fire_freq` INT NULL DEFAULT 0 , `port_air_freq` INT NULL DEFAULT 0 , `port_boiler_freq` INT NULL DEFAULT 0 , `port_smoke_freq` INT NULL DEFAULT 0 , `port_sump_freq` INT NULL DEFAULT 0 , `port_chlorine_freq` INT NULL DEFAULT 0 , `port_lights_freq` INT NULL DEFAULT 0 , PRIMARY KEY (`port_id`) ) ENGINE = InnoDB; CREATE TABLE `contacts` ( `contact_id` INT AUTO_INCREMENT NOT NULL, `contact_ref` VARCHAR(10) NULL DEFAULT NULL , `contact_title` VARCHAR(10) NULL DEFAULT NULL , `contact_first_name` VARCHAR(200) NULL DEFAULT NULL , `contact_last_name` VARCHAR(200) NULL DEFAULT NULL , `contact_alias` VARCHAR(200) NULL DEFAULT NULL , `contact_phone` VARCHAR(20) NULL DEFAULT NULL , `contact_email` VARCHAR(128) NULL DEFAULT NULL , `contact_company_address` INT NULL DEFAULT NULL , `contact_address_1` VARCHAR(100) NULL DEFAULT NULL , `contact_address_2` VARCHAR(100) NULL DEFAULT NULL , `contact_po_box` VARCHAR(10) NULL DEFAULT NULL , `contact_town` VARCHAR(50) NULL DEFAULT NULL , `contact_county` VARCHAR(32) NULL DEFAULT NULL , `contact_postcode` VARCHAR(10) NULL DEFAULT NULL , `contact_notes` VARCHAR(1000) NULL DEFAULT NULL , `contact_client_id` INT NULL DEFAULT NULL , `contact_created` DATETIME NULL DEFAULT NULL , `contact_modified` DATETIME NULL DEFAULT NULL , `contact_deleted` DATETIME NULL DEFAULT NULL , `contact_deleted_flag` INT NULL DEFAULT 0 , `contact_created_by` VARCHAR(32) NULL DEFAULT NULL , `contact_modified_by` VARCHAR(32) NULL DEFAULT NULL , `contact_deleted_by` VARCHAR(32) NULL DEFAULT NULL , `contact_active` INT NULL DEFAULT 1 , PRIMARY KEY (`contact_id`) ) ENGINE = InnoDB; CREATE TABLE `red_fire_doors_images` ( `image_id` INT AUTO_INCREMENT NOT NULL, `image_asset_id` INT NOT NULL, `image_ticket_id` INT NOT NULL, `image_filename` VARCHAR(255) NOT NULL, `image_path` VARCHAR(500) NOT NULL, `image_uploaded_at` DATETIME NOT NULL, PRIMARY KEY (`image_id`) ) ENGINE = InnoDB; CREATE TABLE `employees` ( `employee_id` INT AUTO_INCREMENT NOT NULL, `employee_no` VARCHAR(16) NULL DEFAULT NULL , `employee_name` VARCHAR(200) NOT NULL, `employee_dob` DATE NULL DEFAULT NULL , `employee_phone` VARCHAR(20) NULL DEFAULT NULL , `employee_email` VARCHAR(128) NULL DEFAULT NULL , `employee_address` VARCHAR(256) NULL DEFAULT NULL , `employee_vehicle` VARCHAR(16) NULL DEFAULT NULL , `employee_nok_name` VARCHAR(128) NULL DEFAULT NULL , `employee_nok_phone` VARCHAR(20) NULL DEFAULT NULL , `employee_nok_email` VARCHAR(128) NULL DEFAULT NULL , `employee_nok_relation` VARCHAR(128) NULL DEFAULT NULL , `employee_resource_id` INT NULL DEFAULT NULL , `employee_start` DATE NULL DEFAULT NULL , `employee_end` DATE NULL DEFAULT NULL , `employee_nok_address` VARCHAR(256) NULL DEFAULT NULL , PRIMARY KEY (`employee_id`) ) ENGINE = InnoDB; CREATE TABLE `ticket_risk` ( `ticket_doc_id` INT AUTO_INCREMENT NOT NULL, `ticket_doc_ticket` INT NULL DEFAULT NULL , `ticket_doc_doc` INT NULL DEFAULT NULL , PRIMARY KEY (`ticket_doc_id`) ) ENGINE = InnoDB; CREATE TABLE `accreditations` ( `acc_id` INT AUTO_INCREMENT NOT NULL, `acc_name` VARCHAR(256) NULL DEFAULT NULL , `acc_last` DATE NULL DEFAULT NULL , `acc_next` DATE NULL DEFAULT NULL , `acc_duration` INT NULL DEFAULT NULL , `acc_notes` BLOB NULL DEFAULT NULL , `acc_status` VARCHAR(16) NULL DEFAULT '\'Active'' , PRIMARY KEY (`acc_id`) ) ENGINE = InnoDB; CREATE TABLE `red_gas_appliances_cert` ( `app_id` INT AUTO_INCREMENT NOT NULL, `app_test` INT NULL DEFAULT NULL , `app_assett` INT NULL DEFAULT NULL , `app_location` VARCHAR(128) NULL DEFAULT NULL , `app_type` VARCHAR(128) NULL DEFAULT NULL , `app_make` VARCHAR(64) NULL DEFAULT NULL , `app_model` VARCHAR(128) NULL DEFAULT NULL , `app_landlord` VARCHAR(4) NULL DEFAULT NULL , `app_inspected` VARCHAR(4) NULL DEFAULT NULL , `app_flue` VARCHAR(2) NULL DEFAULT NULL , `app_input` VARCHAR(16) NULL DEFAULT NULL , `app_safety` VARCHAR(8) NULL DEFAULT NULL , `app_ventilation` VARCHAR(8) NULL DEFAULT NULL , `app_flue_visual` VARCHAR(8) NULL DEFAULT NULL , `app_flue_check` VARCHAR(8) NULL DEFAULT NULL , `app_analyser` VARCHAR(16) NULL DEFAULT NULL , `app_serviced` VARCHAR(4) NULL DEFAULT NULL , `app_safe` VARCHAR(4) NULL DEFAULT NULL , `app_giusp` VARCHAR(64) NULL DEFAULT NULL , `app_defect` VARCHAR(1024) NULL DEFAULT NULL , `app_co_fit` VARCHAR(4) NULL DEFAULT NULL , `app_co_test` VARCHAR(4) NULL DEFAULT NULL , `app_serial` VARCHAR(100) NULL DEFAULT NULL , `app_defect_1` TEXT NULL DEFAULT NULL , `app_defect_2` TEXT NULL DEFAULT NULL , `app_defect_3` TEXT NULL DEFAULT NULL , `app_defect_4` TEXT NULL DEFAULT NULL , `app_remedial_1` TEXT NULL DEFAULT NULL , `app_remedial_2` TEXT NULL DEFAULT NULL , `app_remedial_3` TEXT NULL DEFAULT NULL , `app_remedial_4` TEXT NULL DEFAULT NULL , `app_giusp_1` VARCHAR(100) NULL DEFAULT NULL , `app_giusp_2` VARCHAR(100) NULL DEFAULT NULL , `app_giusp_3` VARCHAR(100) NULL DEFAULT NULL , `app_giusp_4` VARCHAR(100) NULL DEFAULT NULL , `app_advisory_1` VARCHAR(100) NULL DEFAULT NULL , `app_advisory_2` VARCHAR(100) NULL DEFAULT NULL , `app_advisory_3` VARCHAR(100) NULL DEFAULT NULL , `app_advisory_4` VARCHAR(100) NULL DEFAULT NULL , PRIMARY KEY (`app_id`) ) ENGINE = InnoDB; CREATE TABLE `ticket_gas_v2` ( `ticket_gas_id` INT AUTO_INCREMENT NOT NULL, `ticket_gas_ticket` INT NULL DEFAULT NULL , `ticket_gas_type` VARCHAR(16) NULL DEFAULT NULL , `ticket_gas_ref` VARCHAR(16) NULL DEFAULT NULL , PRIMARY KEY (`ticket_gas_id`) ) ENGINE = InnoDB; CREATE TABLE `expenses` ( `expense_id` INT AUTO_INCREMENT NOT NULL COMMENT 'ID' , `expense_ref` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Ref' , `expense_ticket` INT NULL DEFAULT NULL COMMENT 'Ticket ID' , `expense_date` DATETIME NULL DEFAULT NULL COMMENT 'Date' , `expense_type` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Type' , `expense_value` DECIMAL(10,2) NULL DEFAULT NULL COMMENT 'Value' , `expense_note` VARCHAR(2000) NULL DEFAULT NULL COMMENT 'Notes' , `expense_url` VARCHAR(250) NULL DEFAULT NULL COMMENT 'URL' , `expense_assigned` INT NULL DEFAULT NULL COMMENT 'Assigned To' , `expense_created` DATETIME NULL DEFAULT NULL COMMENT 'Date Created' , `expense_created_by` INT NULL DEFAULT NULL COMMENT 'Created By' , `expense_modified` DATETIME NULL DEFAULT NULL COMMENT 'Date Last Modified' , `expense_modified_by` INT NULL DEFAULT NULL COMMENT 'Last Modified By' , `expense_deleted` DATETIME NULL DEFAULT NULL COMMENT 'Date Deleted' , `expense_deleted_by` INT NULL DEFAULT NULL COMMENT 'Deleted By' , `expense_deleted_flag` INT NULL DEFAULT NULL COMMENT 'Deleted Flag' , `expense_supplier` INT NULL DEFAULT NULL COMMENT 'Supplier ID' , `expense_activity` INT NULL DEFAULT NULL , `expense_engineer` VARCHAR(32) NULL DEFAULT NULL , `expense_confirmed` INT NULL DEFAULT NULL , `expense_category` VARCHAR(16) NULL DEFAULT NULL , PRIMARY KEY (`expense_id`) ) ENGINE = InnoDB; CREATE TABLE `red_time_timesheets` ( `ts_id` INT AUTO_INCREMENT NOT NULL, `ts_hdt` VARCHAR(10) NULL DEFAULT NULL , `ts_site` VARCHAR(512) NULL DEFAULT NULL , `ts_date` DATETIME NULL DEFAULT NULL , `ts_complete` VARCHAR(3) NULL DEFAULT NULL , `ts_description` BLOB NULL DEFAULT NULL , `ts_hours` TIME NULL DEFAULT NULL , `ts_evening` TIME NULL DEFAULT NULL , `ts_expense` DECIMAL(10,2) NULL DEFAULT NULL , `ts_status` VARCHAR(16) NULL DEFAULT NULL , `ts_resource` VARCHAR(16) NULL DEFAULT NULL , `ts_materials` VARCHAR(3) NULL DEFAULT NULL , `ts_materials_used` TEXT NULL DEFAULT NULL , `ts_red_desk` VARCHAR(3) NULL DEFAULT NULL , PRIMARY KEY (`ts_id`) ) ENGINE = InnoDB; CREATE TABLE `red_air_appliances` ( `app_id` INT AUTO_INCREMENT NOT NULL, `app_test` INT NULL DEFAULT NULL , `app_loc` VARCHAR(32) NULL DEFAULT NULL , `app_make` VARCHAR(64) NULL DEFAULT NULL , `app_model` VARCHAR(64) NULL DEFAULT NULL , `app_serial` VARCHAR(64) NULL DEFAULT NULL , `app_type` VARCHAR(32) NULL DEFAULT NULL , `app_year` VARCHAR(16) NULL DEFAULT NULL , `app_gas` VARCHAR(16) NULL DEFAULT NULL , `app_kg` DOUBLE NULL DEFAULT NULL , `app_visual` VARCHAR(16) NULL DEFAULT NULL , `app_security` VARCHAR(16) NULL DEFAULT NULL , `app_filter_clean` VARCHAR(4) NULL DEFAULT NULL , `app_filter_change` VARCHAR(4) NULL DEFAULT NULL , `app_filter_type` VARCHAR(16) NULL DEFAULT NULL , `app_filter_no` INT NULL DEFAULT NULL , `app_drip_check` VARCHAR(4) NULL DEFAULT NULL , `app_drip_clean` VARCHAR(4) NULL DEFAULT NULL , `app_pump` VARCHAR(4) NULL DEFAULT NULL , `app_drain` VARCHAR(4) NULL DEFAULT NULL , `app_coil` VARCHAR(4) NULL DEFAULT NULL , `app_chemical` VARCHAR(4) NULL DEFAULT NULL , `app_codes` VARCHAR(4) NULL DEFAULT NULL , `app_remote` VARCHAR(4) NULL DEFAULT NULL , `app_unit` VARCHAR(4) NULL DEFAULT NULL , `app_leak` VARCHAR(4) NULL DEFAULT NULL , `app_on_cool` INT NULL DEFAULT NULL , `app_off_cool` INT NULL DEFAULT NULL , `app_on_heat` INT NULL DEFAULT NULL , `app_off_heat` INT NULL DEFAULT NULL , `app_amps` VARCHAR(4) NULL DEFAULT NULL , `app_isolator` VARCHAR(4) NULL DEFAULT NULL , `app_filter_switch` VARCHAR(4) NULL DEFAULT NULL , `app_works` VARCHAR(4) NULL DEFAULT NULL , `app_notes` BLOB NULL DEFAULT NULL , PRIMARY KEY (`app_id`) ) ENGINE = InnoDB; CREATE TABLE `quotes2` ( `quote_id` INT AUTO_INCREMENT NOT NULL, `quote_ref` VARCHAR(20) NULL DEFAULT NULL , `quote_client` INT NULL DEFAULT NULL , `quote_contact` INT NULL DEFAULT NULL , `quote_title` VARCHAR(1000) NULL DEFAULT NULL , `quote_description` VARCHAR(5000) NULL DEFAULT NULL , `quote_status` VARCHAR(32) NULL DEFAULT NULL , `quote_outcome` VARCHAR(32) NULL DEFAULT NULL , `quote_assigned` INT NULL DEFAULT NULL , `quote_due` DATETIME NULL DEFAULT NULL , `quote_chase` DATETIME NULL DEFAULT NULL , `quote_address_1` VARCHAR(128) NULL DEFAULT NULL , `quote_address_2` VARCHAR(128) NULL DEFAULT NULL , `quote_town` VARCHAR(50) NULL DEFAULT NULL , `quote_county` VARCHAR(50) NULL DEFAULT NULL , `quote_postcode` VARCHAR(10) NULL DEFAULT NULL , `quote_logged_user` VARCHAR(32) NULL DEFAULT NULL , `quote_created` DATETIME NULL DEFAULT NULL , `quote_modified_by` VARCHAR(32) NULL DEFAULT NULL , `quote_modified` DATETIME NULL DEFAULT NULL , `quote_closed_by` VARCHAR(32) NULL DEFAULT NULL , `quote_closed` DATETIME NULL DEFAULT NULL , `quote_deleted_by` VARCHAR(32) NULL DEFAULT NULL , `quote_deleted` DATETIME NULL DEFAULT NULL , `quote_deleted_flag` INT NULL DEFAULT NULL , `quote_preamble` VARCHAR(5000) NULL DEFAULT '\'We thank you for your recent enquiry and have pleasure in submitting our quotation as detailed below.'' , `quote_postamble` VARCHAR(5000) NULL DEFAULT '\'We trust that the above is satisfactory and we look forward to your further instructions.'' , `quote_client_site` INT NULL DEFAULT NULL , `quote_value` DECIMAL(10,2) NULL DEFAULT NULL , `quote_multi_sites` INT NULL DEFAULT NULL , `quote_category` INT NULL DEFAULT NULL , PRIMARY KEY (`quote_id`) ) ENGINE = InnoDB; CREATE TABLE `red_gas_engineers` ( `eng_id` INT AUTO_INCREMENT NOT NULL, `eng_name` VARCHAR(32) NULL DEFAULT NULL , `eng_gas_safe` VARCHAR(32) NULL DEFAULT NULL , `eng_sig` VARCHAR(64) NULL DEFAULT NULL , PRIMARY KEY (`eng_id`) ) ENGINE = InnoDB; CREATE TABLE `as_login_attempts` ( `id_login_attempts` INT AUTO_INCREMENT NOT NULL, `ip_addr` VARCHAR(20) NOT NULL, `attempt_number` INT NOT NULL DEFAULT 1 , `date` DATE NOT NULL, PRIMARY KEY (`id_login_attempts`) ) ENGINE = InnoDB; CREATE TABLE `as_comments` ( `comment_id` INT AUTO_INCREMENT NOT NULL, `posted_by` INT NOT NULL, `posted_by_name` VARCHAR(30) NOT NULL, `comment` TEXT NOT NULL, `post_time` DATETIME NOT NULL, PRIMARY KEY (`comment_id`) ) ENGINE = InnoDB; CREATE TABLE `red_air_test_record` ( `test_id` INT AUTO_INCREMENT NOT NULL, `test_ref` VARCHAR(16) NULL DEFAULT NULL , `test_address` VARCHAR(512) NULL DEFAULT NULL , `test_date` DATETIME NULL DEFAULT NULL , `test_status` VARCHAR(16) NULL DEFAULT NULL , `test_client` VARCHAR(128) NULL DEFAULT NULL , `test_hdt` VARCHAR(12) NULL DEFAULT NULL , `test_engineer` VARCHAR(64) NULL DEFAULT NULL , `test_notes` BLOB NULL DEFAULT NULL , PRIMARY KEY (`test_id`) ) ENGINE = InnoDB; CREATE TABLE `ticket_sites` ( `ts_id` INT AUTO_INCREMENT NOT NULL, `ts_ticket` INT NULL DEFAULT NULL , `ts_quote` INT NULL DEFAULT NULL , `ts_site` INT NULL DEFAULT NULL , `ts_resolution` TEXT NULL DEFAULT NULL , `ts_recs` TEXT NULL DEFAULT NULL , `ts_updates` TEXT NULL DEFAULT NULL , PRIMARY KEY (`ts_id`) ) ENGINE = MyISAM; CREATE TABLE `fixed_costs` ( `fc_id` INT AUTO_INCREMENT NOT NULL, `fc_type` VARCHAR(50) NOT NULL, `fc_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00 ) ENGINE = MyISAM; CREATE TABLE `absence` ( `absence_id` INT AUTO_INCREMENT NOT NULL COMMENT 'ID' , `absence_resource` INT NULL DEFAULT NULL COMMENT 'Resource' , `absence_note` VARCHAR(1000) NULL DEFAULT NULL COMMENT 'Notes' , `absence_start` DATETIME NULL DEFAULT NULL COMMENT 'Start Date' , `absence_end` DATETIME NULL DEFAULT NULL COMMENT 'End Date' , `absence_type` VARCHAR(16) NULL DEFAULT NULL COMMENT 'Type' , `absence_paid` INT NULL DEFAULT NULL COMMENT 'Paid Leave' , `absence_half_day` INT NULL DEFAULT NULL COMMENT 'Half Day' , `absence_status` INT NULL DEFAULT NULL , `absence_days` FLOAT NULL DEFAULT NULL , PRIMARY KEY (`absence_id`) ) ENGINE = InnoDB; CREATE TABLE `employees_training` ( `et_id` INT AUTO_INCREMENT NOT NULL, `et_employee_id` INT NULL DEFAULT NULL , `et_asbestos` VARCHAR(16) NULL DEFAULT NULL , `et_emergency` VARCHAR(16) NULL DEFAULT NULL , `et_manual` VARCHAR(16) NULL DEFAULT NULL , `et_puwer` VARCHAR(16) NULL DEFAULT NULL , `et_heights` VARCHAR(16) NULL DEFAULT NULL , `et_harness` VARCHAR(16) NULL DEFAULT NULL , `et_tower` VARCHAR(16) NULL DEFAULT NULL , `et_spaces` VARCHAR(16) NULL DEFAULT NULL , `et_pat` VARCHAR(16) NULL DEFAULT NULL , `et_edge` VARCHAR(16) NULL DEFAULT NULL , `et_ladders` VARCHAR(16) NULL DEFAULT NULL , `et_first_aid` VARCHAR(16) NULL DEFAULT NULL , `et_gas_domestic` VARCHAR(16) NULL DEFAULT NULL , `et_gas_commercial` VARCHAR(16) NULL DEFAULT NULL , `et_pumps` VARCHAR(16) NULL DEFAULT NULL , `et_fgas` VARCHAR(16) NULL DEFAULT NULL , `et_niceic` VARCHAR(16) NULL DEFAULT NULL , `et_ipaf` VARCHAR(16) NULL DEFAULT NULL , PRIMARY KEY (`et_id`) ) ENGINE = InnoDB; CREATE TABLE `client_sites` ( `cs_id` INT AUTO_INCREMENT NOT NULL, `cs_client` INT NULL DEFAULT NULL , `cs_address_1` VARCHAR(512) NULL DEFAULT NULL , `cs_address_2` VARCHAR(512) NULL DEFAULT NULL , `cs_town` VARCHAR(64) NULL DEFAULT NULL , `cs_postcode` VARCHAR(10) NULL DEFAULT NULL , `cs_status` INT NULL DEFAULT 1 , PRIMARY KEY (`cs_id`) ) ENGINE = InnoDB; CREATE TABLE `as_user_details` ( `id_user_details` INT AUTO_INCREMENT NOT NULL, `user_id` INT NOT NULL, `first_name` VARCHAR(35) NOT NULL DEFAULT '\''' , `last_name` VARCHAR(35) NOT NULL DEFAULT '\''' , `phone` VARCHAR(30) NOT NULL DEFAULT '\''' , `address` VARCHAR(255) NOT NULL DEFAULT '\''' , PRIMARY KEY (`id_user_details`) ) ENGINE = InnoDB; CREATE TABLE `quote_notes` ( `quote_notes_id` INT AUTO_INCREMENT NOT NULL, `quote_notes_quote` INT NULL DEFAULT NULL , `quote_notes_date` DATETIME NULL DEFAULT NULL , `quote_notes_user` VARCHAR(32) NULL DEFAULT NULL , `quote_notes_text` VARCHAR(5000) NULL DEFAULT NULL , `quote_notes_type` VARCHAR(1) NULL DEFAULT NULL , PRIMARY KEY (`quote_notes_id`) ) ENGINE = InnoDB; CREATE TABLE `red_pat_appliances` ( `app_id` INT AUTO_INCREMENT NOT NULL, `app_test` INT NULL DEFAULT NULL , `app_assett` INT NULL DEFAULT NULL , `app_make` VARCHAR(64) NULL DEFAULT NULL , `app_description` VARCHAR(128) NULL DEFAULT NULL , `app_location` VARCHAR(128) NULL DEFAULT NULL , `app_retest` VARCHAR(32) NULL DEFAULT NULL , `app_load` VARCHAR(32) NULL DEFAULT NULL , `app_volt` VARCHAR(16) NULL DEFAULT NULL , `app_class` VARCHAR(16) NULL DEFAULT NULL , `app_fuse` VARCHAR(16) NULL DEFAULT NULL , `app_visual` VARCHAR(16) NULL DEFAULT NULL , `app_bond` VARCHAR(16) NULL DEFAULT NULL , `app_ins` VARCHAR(16) NULL DEFAULT NULL , `app_mload` VARCHAR(16) NULL DEFAULT NULL , `app_lcurrent` VARCHAR(16) NULL DEFAULT NULL , `app_polarity` VARCHAR(16) NULL DEFAULT NULL , `app_rcd` VARCHAR(16) NULL DEFAULT NULL , `pat_retest` DATETIME NULL DEFAULT NULL , `pat_notes` BLOB NULL DEFAULT NULL , `app_result` VARCHAR(8) NULL DEFAULT NULL , PRIMARY KEY (`app_id`) ) ENGINE = InnoDB; CREATE TABLE `red_me_sites` ( `me_site_id` INT AUTO_INCREMENT NOT NULL, `me_site_portfolio` INT NULL DEFAULT NULL , `me_site_site_id` INT NULL DEFAULT NULL , `me_site_emergency_lighting` INT NULL DEFAULT NULL , `me_site_flushing` INT NULL DEFAULT NULL , `me_site_temperature` INT NULL DEFAULT NULL , `me_site_fire_alarm` INT NULL DEFAULT NULL , `me_site_air_con` INT NULL DEFAULT NULL , `me_site_boiler` INT NULL DEFAULT NULL , `me_site_smoke_alarm` INT NULL DEFAULT NULL , `me_site_sump` INT NULL DEFAULT NULL , `me_site_chlorine` INT NULL DEFAULT NULL , `me_site_lights` INT NULL DEFAULT NULL , `me_site_wmeter` INT NULL DEFAULT NULL , `me_site_emeter` INT NULL DEFAULT NULL , `me_site_gmeter` INT NULL DEFAULT NULL , `me_site_hpmeter` INT NULL DEFAULT NULL , PRIMARY KEY (`me_site_id`) ) ENGINE = InnoDB; CREATE TABLE `red_me_freq` ( `freq_id` INT AUTO_INCREMENT NOT NULL, `freq_value` VARCHAR(16) NULL DEFAULT NULL , `freq_order` INT NULL DEFAULT NULL , PRIMARY KEY (`freq_id`) ) ENGINE = InnoDB; CREATE TABLE `vehicles` ( `vehicle_id` INT AUTO_INCREMENT NOT NULL COMMENT 'ID' , `vehicle_reg` VARCHAR(12) NULL DEFAULT NULL COMMENT 'Registration' , `vehicle_make` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Make' , `vehicle_model` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Model' , `vehicle_driver` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Driver' , `vehicle_status` VARCHAR(16) NULL DEFAULT NULL COMMENT 'Status' , `vehicle_notes` VARCHAR(5000) NULL DEFAULT NULL COMMENT 'Notes' , `vehicle_created` DATETIME NULL DEFAULT NULL COMMENT 'Created' , `vehicle_modified` DATETIME NULL DEFAULT NULL COMMENT 'Modified' , `vehicle_mot` DATETIME NULL DEFAULT NULL COMMENT 'MOT' , `vehicle_service` DATETIME NULL DEFAULT NULL COMMENT 'Service' , `vehicle_year` INT NULL DEFAULT NULL COMMENT 'Year' , `vehicle_last_service` DATETIME NULL DEFAULT NULL , `vehicle_mileage` INT NULL DEFAULT NULL , `vehicle_mileage_update` DATE NULL DEFAULT NULL , PRIMARY KEY (`vehicle_id`) ) ENGINE = InnoDB; CREATE TABLE `categories` ( `category_id` INT AUTO_INCREMENT NOT NULL, `category_code` VARCHAR(6) NOT NULL, `category_name` VARCHAR(32) NOT NULL, `category_doc_name` VARCHAR(16) NOT NULL, PRIMARY KEY (`category_id`) ) ENGINE = InnoDB; CREATE TABLE `image_descriptions` ( `id_id` INT AUTO_INCREMENT NOT NULL, `id_image` VARCHAR(256) NULL DEFAULT NULL , `id_text` VARCHAR(1000) NULL DEFAULT NULL , PRIMARY KEY (`id_id`) ) ENGINE = InnoDB; CREATE TABLE `resources` ( `resource_id` INT AUTO_INCREMENT NOT NULL COMMENT 'ID' , `resource_name` VARCHAR(64) NULL DEFAULT NULL COMMENT 'Full Name' , `resource_first_name` VARCHAR(32) NULL DEFAULT NULL COMMENT 'First Name' , `resource_user` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Username' , `resource_time_user` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Timesheet User' , `resource_email` VARCHAR(64) NULL DEFAULT NULL COMMENT 'Email' , `resource_group` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Group' , `resource_colour` VARCHAR(7) NULL DEFAULT NULL COMMENT 'Resource Colour' , `resource_code` VARCHAR(8) NULL DEFAULT NULL COMMENT 'Short Code' , `user_id` INT NULL DEFAULT NULL COMMENT 'User ID' , `resource_status` INT NULL DEFAULT 1 COMMENT 'Status' , `external` INT NULL DEFAULT NULL COMMENT 'External Resource' , `resource_quote` INT NULL DEFAULT NULL COMMENT 'Quote User' , `resource_absence` INT NULL DEFAULT NULL COMMENT 'Absence User' , `resource_calendar` INT NULL DEFAULT NULL , `resource_calendar_all` INT NULL DEFAULT NULL , `resource_timesheet` INT NULL DEFAULT NULL , `resource_phone` VARCHAR(20) NULL DEFAULT NULL , `resource_position` VARCHAR(100) NULL DEFAULT NULL , `resource_employee_id` VARCHAR(10) NULL DEFAULT NULL , `resource_engineer_report` INT NULL DEFAULT NULL , PRIMARY KEY (`resource_id`) ) ENGINE = InnoDB; CREATE TABLE `client_report` ( `cr_id` INT AUTO_INCREMENT NOT NULL, `cr_client_id` INT NULL DEFAULT NULL , `cr_client_name` VARCHAR(250) NULL DEFAULT NULL , `cr_sector` VARCHAR(128) NULL DEFAULT NULL , `cr_estimates_won` INT NULL DEFAULT NULL , `cr_estimates_lost` INT NULL DEFAULT NULL , `cr_estimate_conversion` DOUBLE NULL DEFAULT NULL , `cr_ranking_overall` INT NULL DEFAULT NULL , `cr_ranking_current` INT NULL DEFAULT NULL , `cr_ranking_previous` INT NULL DEFAULT NULL , `cr_ranking_sector` INT NULL DEFAULT NULL , `cr_turnover_overall` DOUBLE NULL DEFAULT NULL , `cr_turnover_current` DOUBLE NULL DEFAULT NULL , `cr_turnover_previous` DOUBLE NULL DEFAULT NULL , `cr_install_volume` INT NULL DEFAULT NULL , `cr_install_value` DOUBLE NULL DEFAULT NULL , `cr_repair_volume` INT NULL DEFAULT NULL , `cr_repair_value` DOUBLE NULL DEFAULT NULL , `cr_service_volume` INT NULL DEFAULT NULL , `cr_service_value` DOUBLE NULL DEFAULT NULL , `cr_installed_assets` INT NULL DEFAULT NULL , `cr_active_mc` INT NULL DEFAULT NULL , `cr_active_mc_previous` INT NULL DEFAULT NULL , `cr_active_mc_older` INT NULL DEFAULT NULL , `cr_install_quoted_volume_older` INT NULL DEFAULT NULL , `cr_install_quoted_volume_current` INT NULL DEFAULT NULL , `cr_install_quoted_volume_previous` INT NULL DEFAULT NULL , `cr_install_quoted_value_current` DOUBLE NULL DEFAULT NULL , `cr_install_quoted_value_previous` DOUBLE NULL DEFAULT NULL , `cr_install_quoted_value_older` DOUBLE NULL DEFAULT NULL , `cr_install_assets_installed_current` INT NULL DEFAULT NULL , `cr_install_assets_installed_previous` INT NULL DEFAULT NULL , `cr_install_assets_installed_older` INT NULL DEFAULT NULL , `cr_install_under_contract_current` INT NULL DEFAULT NULL , `cr_install_under_contract_previous` INT NULL DEFAULT NULL , `cr_install_under_contract_older` INT NULL DEFAULT NULL , `cr_install_under_contract_other_current` INT NULL DEFAULT NULL , `cr_install_under_contract_other_previous` INT NULL DEFAULT NULL , `cr_install_under_contract_other_older` INT NULL DEFAULT NULL , `cr_install_no_contract_current` INT NULL DEFAULT NULL , `cr_install_no_contract_previous` INT NULL DEFAULT NULL , `cr_install_no_contract_older` INT NULL DEFAULT NULL , `cr_install_remedial_volume_current` INT NULL DEFAULT NULL , `cr_install_remedial_volume_previous` INT NULL DEFAULT NULL , `cr_install_remedial_volume_older` INT NULL DEFAULT NULL , `cr_install_remedial_value_current` DOUBLE NULL DEFAULT NULL , `cr_install_remedial_value_previous` DOUBLE NULL DEFAULT NULL , `cr_install_remedial_value_older` DOUBLE NULL DEFAULT NULL , `cr_install_won_value_current` DOUBLE NULL DEFAULT NULL , `cr_install_won_value_previous` DOUBLE NULL DEFAULT NULL , `cr_install_won_value_older` DOUBLE NULL DEFAULT NULL , `cr_install_won_volume_current` INT NULL DEFAULT NULL , `cr_install_won_volume_previous` INT NULL DEFAULT NULL , `cr_install_won_volume_older` INT NULL DEFAULT NULL , `cr_repair_quoted_volume_current` INT NULL DEFAULT NULL , `cr_repair_quoted_volume_previous` INT NULL DEFAULT NULL , `cr_repair_quoted_volume_older` INT NULL DEFAULT NULL , `cr_repair_quoted_value_current` DOUBLE NULL DEFAULT NULL , `cr_repair_quoted_value_previous` DOUBLE NULL DEFAULT NULL , `cr_repair_quoted_value_older` DOUBLE NULL DEFAULT NULL , `cr_repair_won_volume_current` INT NULL DEFAULT NULL , `cr_repair_won_volume_previous` INT NULL DEFAULT NULL , `cr_repair_won_volume_older` INT NULL DEFAULT NULL , `cr_repair_won_value_current` DOUBLE NULL DEFAULT NULL , `cr_repair_won_value_previous` DOUBLE NULL DEFAULT NULL , `cr_repair_won_value_older` DOUBLE NULL DEFAULT NULL , `cr_service_visits_current` INT NULL DEFAULT NULL , `cr_service_visits_previous` INT NULL DEFAULT NULL , `cr_service_visits_older` INT NULL DEFAULT NULL , PRIMARY KEY (`cr_id`) ) ENGINE = InnoDB; CREATE TABLE `red_time_availability` ( `av_id` INT AUTO_INCREMENT NOT NULL, `av_date` DATETIME NULL DEFAULT NULL , `av_hours` TIME NULL DEFAULT NULL , `av_resource` VARCHAR(16) NULL DEFAULT NULL , `av_notes` BLOB NULL DEFAULT NULL , PRIMARY KEY (`av_id`) ) ENGINE = InnoDB; CREATE TABLE `tickets` ( `ticket_id` INT AUTO_INCREMENT NOT NULL, `ticket_reference` VARCHAR(10) NULL DEFAULT NULL , `ticket_old_ref` VARCHAR(10) NULL DEFAULT NULL , `ticket_follow_up_id` INT NULL DEFAULT NULL , `ticket_r_ticket_id` INT NULL DEFAULT NULL , `ticket_client` INT NULL DEFAULT NULL , `ticket_contact` INT NULL DEFAULT NULL , `ticket_address_1` VARCHAR(200) NULL DEFAULT NULL , `ticket_address_2` VARCHAR(200) NULL DEFAULT NULL , `ticket_town` VARCHAR(50) NULL DEFAULT NULL , `ticket_county` VARCHAR(32) NULL DEFAULT NULL , `ticket_postcode` VARCHAR(10) NULL DEFAULT NULL , `tmp_address` VARCHAR(300) NULL DEFAULT NULL , `ticket_outcome_code` VARCHAR(32) NULL DEFAULT NULL , `ticket_on_hold_code` INT NULL DEFAULT NULL , `ticket_status_code` VARCHAR(32) NULL DEFAULT NULL , `ticket_title` VARCHAR(1000) NULL DEFAULT NULL , `ticket_description` VARCHAR(5000) NULL DEFAULT NULL , `ticket_resolution` VARCHAR(2000) NULL DEFAULT NULL , `ticket_created` DATETIME NULL DEFAULT NULL , `ticket_modified` DATETIME NULL DEFAULT NULL , `ticket_closed` DATETIME NULL DEFAULT NULL , `ticket_deleted` DATETIME NULL DEFAULT NULL , `ticket_deleted_flag` INT NULL DEFAULT 0 , `ticket_priority` VARCHAR(16) NULL DEFAULT NULL , `ticket_logged_user` VARCHAR(32) NULL DEFAULT NULL , `ticket_fault_code` INT NULL DEFAULT NULL , `ticket_client_ref` VARCHAR(128) NULL DEFAULT NULL , `ticket_callout` VARCHAR(8) NULL DEFAULT NULL , `ticket_modified_by` VARCHAR(32) NULL DEFAULT NULL , `ticket_deleted_by` VARCHAR(32) NULL DEFAULT NULL , `ticket_closed_by` VARCHAR(32) NULL DEFAULT NULL , `ticket_pro_forma` INT NULL DEFAULT 0 , `ticket_invoice_total` DECIMAL(10,2) NULL DEFAULT NULL , `ticket_full_notes_text` BLOB NULL DEFAULT NULL , `ticket_full_notes_text_source` BLOB NULL DEFAULT NULL , `ticket_activity_email` VARCHAR(128) NULL DEFAULT NULL , `ticket_quote` INT NULL DEFAULT NULL , `ticket_quote_revision` INT NULL DEFAULT NULL , `ticket_quoted_value` DECIMAL(10,2) NULL DEFAULT NULL , `ticket_audit` INT NULL DEFAULT 0 , `ticket_audit_date` DATETIME NULL DEFAULT NULL , `ticket_audit_comment` BLOB NULL DEFAULT NULL , `ticket_resources` VARCHAR(256) NULL DEFAULT NULL , `ticket_new_hourly_rate` VARCHAR(1) NULL DEFAULT NULL , `ticket_report` INT NULL DEFAULT 0 , `ticket_report_date` DATETIME NULL DEFAULT NULL , `ticket_report_comment` BLOB NULL DEFAULT NULL , `ticket_report_update` BLOB NULL DEFAULT NULL , `ticket_client_site` INT NULL DEFAULT NULL , `ticket_access_type` VARCHAR(32) NULL DEFAULT NULL , `ticket_multi_sites` INT NULL DEFAULT NULL , `ticket_max_value` DECIMAL(10,2) NULL DEFAULT 750.00 , `ticket_value` DECIMAL(10,2) NULL DEFAULT NULL , `ticket_fixed_cost` INT NULL DEFAULT NULL , `ticket_fixed_cost_value` DOUBLE NULL DEFAULT NULL , `ticket_purchase_order` VARCHAR(100) NULL DEFAULT NULL , `ticket_nic_sent` DATE NULL DEFAULT NULL , `ticket_flag` INT NULL DEFAULT 0 , `ticket_price_confirmed` VARCHAR(4) NULL DEFAULT NULL , `ticket_confirmed_price` DOUBLE NULL DEFAULT NULL , `ticket_pre_invoice_status` INT NULL DEFAULT NULL , `ticket_po_requested` DATETIME NULL DEFAULT NULL , PRIMARY KEY (`ticket_id`) ) ENGINE = InnoDB; CREATE TABLE `red_drain_surveys` ( `survey_id` INT AUTO_INCREMENT NOT NULL, `survey_ref` VARCHAR(16) NULL DEFAULT NULL , `survey_date` DATETIME NULL DEFAULT NULL , `survey_purpose` VARCHAR(32) NULL DEFAULT NULL , `survey_surveyor` VARCHAR(32) NULL DEFAULT NULL , `survey_weather` VARCHAR(32) NULL DEFAULT NULL , `survey_client` VARCHAR(128) NULL DEFAULT NULL , `survey_address` VARCHAR(512) NULL DEFAULT NULL , `survey_hdt` VARCHAR(16) NULL DEFAULT NULL , `survey_summary` VARCHAR(3000) NULL DEFAULT NULL , `survey_status` VARCHAR(32) NULL DEFAULT '\'Open'' , PRIMARY KEY (`survey_id`) ) ENGINE = MyISAM;