array( 'type' => 'int(11)', 'Null' => false, 'Key' => 'PRI', 'Extra' => 'auto_increment' ), 'system_id' => array('type' => 'int(11)', 'Null' => false), 'photo_type' => array('type' => 'varchar(50)', 'Null' => false, 'Default' => 'other'), 'image_path' => array('type' => 'varchar(255)', 'Null' => false), 'original_filename' => array('type' => 'varchar(255)', 'Null' => false), 'width' => array('type' => 'int(11)', 'Null' => true), 'height' => array('type' => 'int(11)', 'Null' => true), 'file_size' => array('type' => 'int(11)', 'Null' => true), 'date_uploaded' => array('type' => 'int(11)', 'Null' => false), // JSON array of thumbnail objects: [{"width":80,"height":60,"url":"path/to/thumb_80x60.jpg"},{"width":150,"height":150,"url":"path/to/thumb_150.jpg"}] 'thumbnails' => array('type' => 'JSON', 'Null' => true) ); $schema['system_meta'] = array( // Form meta data 'id' => array( 'type' => 'int(11)', 'Null' => false, 'Key' => 'PRI', 'Extra' => 'auto_increment', 'editable' => false, 'name' => 'ID', 'group' => 'Overview' ), 'userid' => array('type' => 'int(11)', 'editable' => false), 'published' => array('type' => 'tinyint(1)', 'editable' => false, 'optional' => false, 'name' => 'Published', 'group' => 'Overview'), 'last_updated' => array('type' => 'int(11)', 'editable' => false, 'name' => 'Last updated', 'group' => 'Overview'), // New method of linking an emoncms instance myheatpump app 'app_id' => array('type' => 'int(11)', 'editable' => true), 'readkey' => array('type' => 'varchar(64)', 'editable' => true), /* ------------------------------ Overview ----------------------------- */ 'location' => array( 'type' => 'varchar(64)', 'filter_regex' => '/[^a-zA-Z0-9\s\.\,\-]/u', 'editable' => true, 'optional' => false, 'name' => 'Location (Town, County)', 'helper' => 'Roughly where the heat pump is installed, to nearest city or county', 'group' => 'Overview' ), 'latitude' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'Latitude', 'group' => 'Overview', 'helper' => 'Rough location, nearest town or county', ), 'longitude' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'Longitude', 'group' => 'Overview', 'helper' => 'Rough location, nearest town or county', ), 'installer_name' => array( 'type' => 'varchar(64)', 'filter_regex' => '/[^a-zA-Z0-9\s\.\,\-\+\(\)\&]/u', 'editable' => true, 'optional' => true, 'name' => 'Installer', 'group' => 'Overview', 'helper' => 'Optional. If you are not the installer we recommend asking the installer if they are happy with their name being displayed. Self install is also an option..', 'basic' => true ), 'installer_url' => array( 'type' => 'varchar(255)', 'filter_regex' => 'url', 'editable' => true, 'optional' => true, 'name' => 'Installer URL', 'group' => 'Overview', 'basic' => true ), 'installer_logo' => array( 'type' => 'varchar(64)', 'editable' => false, 'optional' => true, 'name' => 'Installer Logo', 'heading' => '', 'group' => 'Overview' ), 'installation_Cost' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'name' => 'Installation Cost (before subtracting grant)', 'group' => 'Overview' ), 'heatgeek' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Heat Geek Mastery', 'group' => 'Training', //'basic' => true ), 'ultimaterenewables' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Ultimate Renewables Pro', 'group' => 'Training', //'basic' => true ), 'heatingacademy' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Heating Academy Hydronics', 'group' => 'Training', //'basic' => true ), 'guild' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Guild of Heating Engineers', 'group' => 'Training', //'basic' => true ), 'betateach' => array( 'type' => 'varchar(256)', 'filter_regex' => 'url', 'editable' => true, 'optional' => true, 'name' => 'BetaTalk', 'group' => 'Learn more', //'basic' => true ), 'youtube' => array( 'type' => 'varchar(256)', 'filter_regex' => 'url', 'editable' => true, 'optional' => true, 'name' => 'YouTube', 'group' => 'Learn more', //'basic' => true ), 'url' => array( 'type' => 'varchar(128)', 'filter_regex' => 'url', 'editable' => true, 'optional' => false, 'name' => 'MyHeatpump App URL', 'helper' => '', // 'group' => 'Overview', 'show' => false, 'show_to_admin' => true ), 'share' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => false, 'name' => 'Share', 'group' => 'Overview', 'show' => false ), /* ------------------------------ Heat pump system ----------------------------- */ 'hp_type' => array( 'type' => 'varchar(64)', 'editable' => true, 'optional' => false, 'name' => 'Heat pump type', 'group' => 'Heat pump', 'options'=>array('Air Source','Ground Source','Water Source','Air-to-Air','Other'), 'show' => false ), 'hp_hybrid' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Hybrid heat pump system', 'helper' => 'A hybrid system is one that includes a non-electric backup heating system such as a gas boiler', 'group' => 'Heat pump', 'show' => false ), 'hp_manufacturer' => array( 'type' => 'varchar(64)', 'filter_regex' => '/[^a-zA-Z0-9\s\.\,\-\+\(\)\&]/u', 'editable' => true, 'optional' => false, 'name' => 'Heat pump manufacturer', 'group' => 'Heat pump', 'show' => false ), 'hp_model' => array( 'type' => 'varchar(64)', 'filter_regex' => '/[^a-zA-Z0-9\s\.\,\-\+\(\)\&]/u', 'editable' => true, 'optional' => false, 'name' => 'Heat pump make & model', 'group' => 'Heat pump', 'show' => false ), 'hp_output' => array( 'type' => 'float', 'editable' => true, 'optional' => false, 'name' => 'Heat pump output', 'helper' => 'This is the badge output not necessarily the maximum output as given on the datasheet', 'group' => 'Heat pump', 'unit' => 'kW', 'show' => false ), 'hp_max_output' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'name' => 'Heat pump max output', 'helper' => 'Maximum output as given on the datasheet for expected design flow temperature', 'group' => 'Heat pump', 'unit' => 'kW', 'show' => false ), 'hp_max_output_test' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'show' => false, 'name' => 'Heat pump max output test', 'helper' => 'Maximum output recorded over 2-3 defrost cycles using HeatpumpMonitor data', 'group' => 'Heat pump', 'unit' => 'kW' ), 'refrigerant' => array( 'type' => 'varchar(64)', 'editable' => true, 'optional' => true, 'name' => 'Refrigerant', 'group' => 'Heat pump', 'options'=>array('R290','R32','CO2','R410A','R210A','R134A','R407C','R454B','R454C','R452B'), 'show' => false ), 'uses_backup_heater' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Heat pump has backup heater installed and in use', 'helper'=> 'This is an inline electric element that can top up the heat pump output mostly for space heating', 'group' => 'Heat pump', 'basic' => true, 'show' => false ), /* ------------------------------ Space heating ----------------------------- */ 'flow_temp' => array( 'type' => 'float', 'editable' => true, 'optional' => false, 'name' => 'Design flow temperature', 'group' => 'Space heating', 'helper' => "Design flow temperature (e.g 45°C at -3°C)", 'unit' => '°C' ), 'design_temp' => array( 'type' => 'float', 'editable' => true, 'optional' => false, 'name' => 'Outside design temperature', 'group' => 'Space heating', 'helper' => "E.g -3°C", 'unit' => '°C' ), 'zone_number' => array( 'type' => 'int(11)', 'editable' => true, 'optional' => true, 'name' => 'Number of zones', 'group' => 'Space heating', 'default' => 1 ), 'new_radiators' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'New radiators', 'group' => 'Space heating' ), 'old_radiators' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Old radiators', 'group' => 'Space heating' ), 'fan_coil_radiators' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Fan coil radiators', 'group' => 'Space heating' ), 'UFH' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Underfloor heating', 'group' => 'Space heating' ), 'hydraulic_separation' => array( 'type' => 'varchar(64)', 'editable' => true, 'optional' => false, 'name' => 'Hydraulic seperation', 'group' => 'Space heating', 'options'=> array('None','Low loss header','Buffer','Plate heat exchanger',"Don't know") ), 'volumiser' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Volumiser', 'group' => 'Space heating', 'helper' => "A volumiser is a 2-pipe tank that adds more water volume to the system to reduce cycling" ), /* --------------------------- Hot water cylinders -------------------------- */ 'dhw_method' => array( 'type' => 'varchar(64)', 'editable' => true, 'optional' => false, 'name' => 'Hot water storage', 'group' => 'Hot water', 'options'=>array( 'None', 'Cylinder with coil', 'Cylinder with plate heat exchanger', 'Thermal store (heat exchanger on output)', 'Phase change store', 'Other' ) ), 'dhw_make_model' => array( 'type' => 'varchar(64)', 'filter_regex' => '/[^a-zA-Z0-9\s\.\,\-\+\(\)\&]/u', 'editable' => true, 'optional' => true, 'name' => 'Cylinder make & model', 'group' => 'Hot water', ), 'cylinder_volume' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'name' => 'Cylinder volume', 'helper' => 'If applicable', 'group' => 'Hot water', 'unit' => 'litres' ), 'dhw_target_temperature' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'name' => 'DHW target temperature', 'helper' => 'Cylinder temperature the heat pump heats to, if applicable', 'group' => 'Hot water', 'unit' => '°C' ), 'legionella_frequency' => array( 'type' => 'varchar(32)', 'editable' => true, 'optional' => true, 'name' => 'Legionella frequency', 'group' => 'Hot water', 'options' => array('Daily', 'Weekly', 'Fornightly', 'Monthly', 'Other', 'Flexible', 'Disabled', 'No cylinder') ), 'legionella_immersion' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Legionella cycle uses immersion heater', 'group' => 'Hot water', 'basic' => true ), /* --------------------------------- Other --------------------------------- */ 'freeze' => array( 'type' => 'varchar(64)', 'editable' => true, 'optional' => true, 'name' => 'Freeze protection', 'group' => 'Misc', 'options' => array('Glycol/water mixture', 'Anti-freeze valves', 'Central heat pump water circulation','Not applicable','None') ), /* --------------------------------- Property --------------------------------- */ 'heat_loss' => array( 'type' => 'float', 'editable' => true, 'optional' => false, 'name' => 'Heat loss at design outside temperature (DOT)', 'group' => 'Property', 'helper' => 'E.g as given in detailed installer assessment', 'unit' => 'kW @ -3°C DOT' ), 'property' => array( 'type' => 'varchar(64)', 'editable' => true, 'optional' => false, 'name' => 'Property type', 'group' => 'Property', 'options' => array('Detached', 'Semi-detached', 'End-terrace', 'Mid-terrace', 'Flat / appartment', 'Bungalow', 'Office building') ), 'floor_area' => array( 'type' => 'float', 'editable' => true, 'optional' => false, 'name' => 'Floor area', 'group' => 'Property', 'unit' => 'm²' ), 'age' => array( 'type' => 'varchar(64)', 'editable' => true, 'optional' => false, 'name' => 'Age', 'group' => 'Property', 'options' => array('2012 or newer', '1983 to 2011', '1940 to 1982', '1900 to 1939', 'Pre-1900') ), 'insulation' => array( 'type' => 'varchar(64)', 'editable' => true, 'optional' => false, 'name' => 'Insulation level', 'group' => 'Property', 'options' => array('Passivhaus', 'Fully insulated walls, floors and loft', 'Some insulation in walls and loft', 'Cavity wall, plus some loft insulation', 'Non-insulated cavity wall', 'Solid walls') ), 'passivhaus_certification' => array( 'type' => 'varchar(10)', 'editable' => true, 'optional' => true, 'name' => 'Passivhaus Certification', 'group' => 'Property', 'options' => array('Yes', 'No', 'Pending'), 'show' => false ), /* 'EPC_spaceheat_demand' => array( 'type' => 'int(11)', 'editable' => true, 'optional' => true, 'name' => 'EPC space heating demand', 'group' => 'Property', 'helper' => 'As given on the EPC', 'unit' => 'kWh/year' ), 'EPC_waterheat_demand' => array( 'type' => 'int(11)', 'editable' => true, 'optional' => true, 'name' => 'EPC water heating demand', 'group' => 'Property', 'helper' => 'As given on the EPC', 'unit' => 'kWh/year' ),*/ /* 'kwh_m2' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'kWh/m²', 'group' => 'Property', 'helper' => 'Annual space and water heating demand per m2', 'unit' => 'kWh/m²/yr' ),*/ /* ----------------------------- Electricity tariff ----------------------------- */ /* 'electricity_tariff' => array( 'type' => 'varchar(64)', 'editable' => true, 'optional' => true, 'name' => 'Electricity tariff', 'group' => 'Tariff & Generation' ), 'electricity_tariff_type' => array( 'type' => 'varchar(64)', 'editable' => true, 'optional' => true, 'name' => 'Tariff type', 'group' => 'Tariff & Generation' , 'options' => array('Single fixed rate', 'On peak & Off peak', 'Variable half hourly') ), 'electricity_tariff_unit_rate_all' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'name' => 'Average unit rate paid for all electricity', 'helper' => 'Including e.g off peak EV charging', 'group' => 'Tariff & Generation', 'unit' => 'p/kWh' ), 'electricity_tariff_unit_rate_hp' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'name' => 'Average unit rate for heat pump', 'helper' => 'If known, based on time of use monitoring', 'group' => 'Tariff & Generation', 'unit' => 'p/kWh' ),*/ /* --------------------------------- Monitoring --------------------------------- */ 'mid_metering' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'MID Metering', 'heading' => 'MID', 'helper' => 'Automatically selected if electric meter is class 1 and heat meter at least class 2', 'group' => 'Metering', 'disabled' => true, 'basic' => true ), 'electric_meter' => array( 'type' => 'varchar(128)', 'editable' => true, 'optional' => false, 'name' => 'Electric meter', 'group' => 'Metering', 'options' => array( 'SDM120 Modbus/MBUS Single Phase (class 1)', 'SDM220 Modbus/MBUS Single Phase (class 1)', 'SDM630 Modbus/MBUS Three Phase (class 1)', 'OpenEnergyMonitor EmonPi v1, EmonTx3 or earlier', 'OpenEnergyMonitor EmonPi v2, EmonTx4 or newer', 'Other Modbus/MBUS meter (class 1)', 'Other pulse output meter (class 1)', 'Heat pump integration', 'Other electricity meter' ) ), 'heat_meter' => array( 'type' => 'varchar(128)', 'editable' => true, 'optional' => false, 'name' => 'Heat meter', 'group' => 'Metering', 'options' => array( 'Axioma Qalcosonic heat meter (class 2)', 'Sontex heat meter (class 2)', 'Kamstrup heat meter (class 2)', 'Sharky heat meter (class 2)', 'SensoStar heat meter (class 2)', 'Itron heat meter (class 2)', 'Danfoss Sono heat meter (class 2)', 'Ista Ultego heat meter (class 2)', 'Sika or Grundfos VFS flow meter', 'Heat pump integration', 'Other heat meter', 'No heat meter' ) ), 'metering_inc_boost' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Includes backup heater', 'group' => 'Metering', 'basic' => true ), 'metering_inc_immersion' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Includes DHW immersion heater', 'group' => 'Metering', 'basic' => true ), 'metering_inc_central_heating_pumps' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Includes primary circulation pump', 'group' => 'Metering', 'basic' => true ), 'metering_inc_secondary_heating_pumps' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Includes secondary circulation pump/s', 'group' => 'Metering', 'basic' => true ), 'metering_inc_brine_pumps' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Includes ground source brine pumps', 'group' => 'Metering', 'basic' => true ), 'metering_inc_controls' => array( 'type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Includes indoor controller or other controls', 'group' => 'Metering', 'basic' => true ), 'metering_boundary_code' => array( 'type' => 'int(11)', 'editable' => true, 'optional' => true, 'name' => 'Metering boundary code', 'heading' => 'Hx', 'helper' => 'SEPEMO metering boundary code', 'group' => 'Metering', 'disabled' => true, 'basic' => true ), 'notes' => array( 'type' => 'text', 'filter_regex' => '/[^a-zA-Z0-9\s\.\,\-\+\(\)\&\£\%\:\/]/u', 'editable' => true, 'optional' => true, 'name' => 'Any other notes or comments', 'group' => 'Notes', 'basic' => true ), 'data_flag' => array('type' => 'tinyint(1)', 'editable' => true, 'optional' => true, 'name' => 'Data issue flag', 'group' => 'Metering'), 'data_flag_note' => array( 'type' => 'varchar(128)', 'filter_regex' => '/[^a-zA-Z0-9\s\.\,\-\+\(\)\&]/u', 'editable' => true, 'optional' => true, 'name' => 'Data issue flag note', 'group' => 'Metering' ), /* ----------------------------- Measurements ----------------------------- */ /* // Disabled for now, not enough data to make this useful 'measured_emitter_spec' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'show' => true, 'name' => 'Measured emitter spec', 'group' => 'Measurements', 'helper' => 'Measured emitter output at DT50', 'unit' => 'kW @ DT50' ), 'measured_system_volume' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'show' => true, 'name' => 'Measured system volume', 'group' => 'Measurements', 'helper' => 'Measured system volume from Heatpump dashboard', 'unit' => 'L' ), */ // Measured heat demand and design flow temperature: worked out nightly into the // measurement table by scripts/measurements/system_measurements.php (the hand-saved // measured_base_DT, measured_design_DT, measured_heat_loss(_range) and // measured_design_flowT(_range) fields were retired in November 2026, with // measured_max_flow_temp_coldest_day, which was entered by hand on views/coldest.php). /* ---------------------------- Design estimates ---------------------------- */ // Computed by DesignEstimates (design_estimates_model.php) on save and after // the nightly stats update. Not editable; 0 where the method doesn't apply. 'mcs031_space_spf' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'MCS 031 space heating SPF', 'group' => 'Design estimates', 'helper' => 'MCS 031 seasonal performance factor at the declared design flow temperature' ), 'mcs031_spf' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'MCS 031 SPF (last 365 days)', 'group' => 'Design estimates', 'helper' => 'MCS 031 combined space heating and hot water SPF, applied to the measured heat over the last 365 days' ), 'mcs031_spf_ratio' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'Measured SPF ÷ MCS 031 SPF (last 365 days)', 'group' => 'Design estimates', 'helper' => 'Measured SPF over the last 365 days divided by the MCS 031 estimate for the same heat' ), 'scop_space' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'Manufacturer SCOP at design flow temperature', 'group' => 'Design estimates', 'helper' => 'SCOP declared to MCS for this heat pump model at the declared design flow temperature' ), 'scop_spf' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'Manufacturer SCOP estimate (last 365 days)', 'group' => 'Design estimates', 'helper' => 'Manufacturer SCOP at the design flow temperature for space heating and at the hot water target + 5 K, applied to the measured heat over the last 365 days' ), 'scop_spf_ratio' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'Measured SPF ÷ manufacturer SCOP estimate (last 365 days)', 'group' => 'Design estimates', 'helper' => 'Measured SPF over the last 365 days divided by the manufacturer SCOP estimate for the same heat' ), 'measured_mean_flow_temp_coldest_day' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'show' => false, 'name' => 'Weighted mean flow temperature on coldest day', 'group' => 'Measurements', 'helper' => '', 'unit' => '°C', 'dp' => 2 ), 'measured_outside_temp_coldest_day' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'show' => false, 'name' => 'Weighted mean outside temperature on coldest day', 'group' => 'Measurements', 'helper' => '', 'unit' => '°C', 'dp' => 2 ), 'measured_room_temp_coldest_day' => array( 'type' => 'float', 'editable' => true, 'optional' => true, 'show' => false, 'name' => 'When running room temperature on coldest day', 'group' => 'Measurements', 'helper' => '', 'unit' => '°C', 'dp' => 2 ), // Start of the coldest day the measurements above come from, set nightly by scripts/measurements/system_measurements.php 'measured_coldest_day_timestamp' => array( 'type' => 'int(11)', 'editable' => false, 'optional' => true, 'show' => false, 'name' => 'Coldest day', 'group' => 'Measurements', 'helper' => 'Start of the coldest day in the last year, as a unix timestamp' ), // Projected annual SPF for systems with less than a year of data, set daily by // scripts/projected_spf.php from the fleet-calibrated model in scripts/projected_spf_model.json 'projected_spf' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'Projected annual SPF', 'group' => 'Projected performance', 'helper' => 'Annual combined SPF projected from the data so far, for systems with less than a year of data. Empty until the projection is within about ±10%' ), 'projected_spf_low' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'Projected annual SPF, low', 'group' => 'Projected performance', 'helper' => 'Lower end of the projection range: 9 in 10 systems in testing ended above it' ), 'projected_spf_high' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'Projected annual SPF, high', 'group' => 'Projected performance', 'helper' => 'Upper end of the projection range: 9 in 10 systems in testing ended below it' ), 'projected_spf_within10' => array( 'type' => 'float', 'editable' => false, 'optional' => true, 'name' => 'Projection track record', 'group' => 'Projected performance', 'helper' => 'Share of systems in testing whose annual SPF ended within ±10% of a projection made at the same point in the year from the same number of days' ), 'projected_spf_days' => array( 'type' => 'int(11)', 'editable' => false, 'optional' => true, 'name' => 'Projection based on (days)', 'group' => 'Projected performance', 'helper' => 'Days of data the projection uses. Set with an empty projection while waiting for heating season data' ) ); $schema['system_stats_daily'] = array( // System ID 'id' => array('type' => 'int(11)'), // Start of day timestamp 'timestamp' => array('type' => 'int(11)'), // Full period stats 'combined_elec_kwh' => array('type' => 'float', 'name'=>'Electricity consumption', 'group'=>'Stats: Combined', 'dp'=>0, 'unit'=>'kWh'), 'combined_heat_kwh' => array('type' => 'float', 'name'=>'Heat output', 'group'=>'Stats: Combined', 'dp'=>0, 'unit'=>'kWh'), 'combined_cop' => array('type' => 'float', 'name'=>'COP', 'heading'=>"COP", 'group'=>'Stats: Combined', 'dp'=>1, 'unit'=>''), 'combined_data_length' => array('type' => 'float', 'name'=>'Length', 'group'=>'Stats: Combined', 'dp'=>0, 'unit'=>''), 'combined_elec_mean' => array('type' => 'float', 'name'=>'Elec mean', 'group'=>'Stats: Combined', 'dp'=>0, 'unit'=>'W'), 'combined_heat_mean' => array('type' => 'float', 'name'=>'Heat mean', 'group'=>'Stats: Combined', 'dp'=>0, 'unit'=>'W'), 'combined_flowT_mean' => array('type' => 'float', 'name'=>'FlowT mean', 'group'=>'Stats: Combined', 'dp'=>1, 'unit'=>'°C'), 'combined_returnT_mean' => array('type' => 'float', 'name'=>'ReturnT mean', 'group'=>'Stats: Combined', 'dp'=>1, 'unit'=>'°C'), 'combined_outsideT_mean' => array('type' => 'float', 'name'=>'OutsideT mean', 'group'=>'Stats: Combined', 'dp'=>1, 'unit'=>'°C'), 'combined_roomT_mean' => array('type' => 'float', 'name'=>'RoomT mean', 'group'=>'Stats: Combined', 'dp'=>1, 'unit'=>'°C'), 'combined_prc_carnot' => array('type' => 'float', 'name'=>'% Carnot', 'group'=>'Stats: Combined', 'dp'=>1, 'unit'=>'%'), 'combined_cooling_kwh' => array('type' => 'float', 'name'=>'Cooling energy', 'group'=>'Stats: Combined', 'dp'=>0, 'unit'=>'kWh'), 'combined_starts' => array('type' => 'float', 'name'=>'Starts', 'group'=>'Stats: Combined', 'dp'=>0, 'unit'=>''), 'combined_starts_per_hour' => array('type' => 'float', 'name'=>'Starts per hour', 'group'=>'Stats: Combined', 'dp'=>2, 'unit'=>''), // When Running 'running_elec_kwh' => array('type' => 'float', 'name'=>'Electricity consumption', 'group'=>'Stats: When Running', 'dp'=>0, 'unit'=>'kWh'), 'running_heat_kwh' => array('type' => 'float', 'name'=>'Heat output', 'group'=>'Stats: When Running', 'dp'=>0, 'unit'=>'kWh'), 'running_cop' => array('type' => 'float', 'name'=>'COP', 'heading'=>"Running
COP", 'group'=>'Stats: When Running', 'dp'=>1, 'unit'=>''), 'running_data_length' => array('type' => 'float', 'name'=>'Data length', 'group'=>'Stats: When Running', 'dp'=>0, 'unit'=>''), 'running_elec_mean' => array('type' => 'float', 'name'=>'Elec mean', 'group'=>'Stats: When Running', 'dp'=>0, 'unit'=>'W'), 'running_heat_mean' => array('type' => 'float', 'name'=>'Heat mean', 'group'=>'Stats: When Running', 'dp'=>0, 'unit'=>'W'), 'running_flowT_mean' => array('type' => 'float', 'name'=>'FlowT mean', 'group'=>'Stats: When Running', 'dp'=>1, 'unit'=>'°C'), 'running_returnT_mean' => array('type' => 'float', 'name'=>'ReturnT mean', 'group'=>'Stats: When Running', 'dp'=>1, 'unit'=>'°C'), 'running_outsideT_mean' => array('type' => 'float', 'name'=>'OutsideT mean', 'group'=>'Stats: When Running', 'dp'=>1, 'unit'=>'°C'), 'running_roomT_mean' => array('type' => 'float', 'name'=>'RoomT mean', 'group'=>'Stats: When Running', 'dp'=>1, 'unit'=>'°C'), 'running_prc_carnot' => array('type' => 'float', 'name'=>'% Carnot', 'group'=>'Stats: When Running', 'dp'=>1, 'unit'=>'%'), // Space heating 'space_elec_kwh' => array('type' => 'float', 'name'=>'Electricity consumption', 'group'=>'Stats: Space heating', 'dp'=>0, 'unit'=>'kWh'), 'space_heat_kwh' => array('type' => 'float', 'name'=>'Heat output', 'group'=>'Stats: Space heating', 'dp'=>0, 'unit'=>'kWh'), 'space_cop' => array('type' => 'float', 'name'=>'COP', 'heading'=>"Space
COP", 'group'=>'Stats: Space heating', 'dp'=>1, 'unit'=>''), 'space_data_length' => array('type' => 'float', 'name'=>'Data length', 'group'=>'Stats: Space heating', 'dp'=>0, 'unit'=>''), 'space_elec_mean' => array('type' => 'float', 'name'=>'Elec mean', 'group'=>'Stats: Space heating', 'dp'=>0, 'unit'=>'W'), 'space_heat_mean' => array('type' => 'float', 'name'=>'Heat mean', 'group'=>'Stats: Space heating', 'dp'=>0, 'unit'=>'W'), 'space_flowT_mean' => array('type' => 'float', 'name'=>'FlowT mean', 'group'=>'Stats: Space heating', 'dp'=>1, 'unit'=>'°C'), 'space_returnT_mean' => array('type' => 'float', 'name'=>'ReturnT mean', 'group'=>'Stats: Space heating', 'dp'=>1, 'unit'=>'°C'), 'space_outsideT_mean' => array('type' => 'float', 'name'=>'OutsideT mean', 'group'=>'Stats: Space heating', 'dp'=>1, 'unit'=>'°C'), 'space_roomT_mean' => array('type' => 'float', 'name'=>'RoomT mean', 'group'=>'Stats: Space heating', 'dp'=>1, 'unit'=>'°C'), 'space_prc_carnot' => array('type' => 'float', 'name'=>'% Carnot', 'group'=>'Stats: Space heating', 'dp'=>1, 'unit'=>'%'), // Water heating 'water_elec_kwh' => array('type' => 'float', 'name'=>'Electricity consumption', 'group'=>'Stats: Water heating', 'dp'=>0, 'unit'=>'kWh'), 'water_heat_kwh' => array('type' => 'float', 'name'=>'Heat output', 'group'=>'Stats: Water heating', 'dp'=>0, 'unit'=>'kWh'), 'water_cop' => array('type' => 'float', 'name'=>'COP', 'heading'=>"DHW", 'group'=>'Stats: Water heating', 'dp'=>1, 'unit'=>''), 'water_data_length' => array('type' => 'float', 'name'=>'Data length', 'group'=>'Stats: Water heating', 'dp'=>0, 'unit'=>''), 'water_elec_mean' => array('type' => 'float', 'name'=>'Elec mean', 'group'=>'Stats: Water heating', 'dp'=>0, 'unit'=>'W'), 'water_heat_mean' => array('type' => 'float', 'name'=>'Heat mean', 'group'=>'Stats: Water heating', 'dp'=>0, 'unit'=>'W'), 'water_flowT_mean' => array('type' => 'float', 'name'=>'FlowT mean', 'group'=>'Stats: Water heating', 'dp'=>1, 'unit'=>'°C'), 'water_returnT_mean' => array('type' => 'float', 'name'=>'ReturnT mean', 'group'=>'Stats: Water heating', 'dp'=>1, 'unit'=>'°C'), 'water_outsideT_mean' => array('type' => 'float', 'name'=>'OutsideT mean', 'group'=>'Stats: Water heating', 'dp'=>1, 'unit'=>'°C'), 'water_roomT_mean' => array('type' => 'float', 'name'=>'RoomT mean', 'group'=>'Stats: Water heating', 'dp'=>1, 'unit'=>'°C'), 'water_prc_carnot' => array('type' => 'float', 'name'=>'% Carnot', 'group'=>'Stats: Water heating', 'dp'=>1, 'unit'=>'%'), // Cooling 'cooling_elec_kwh' => array('type' => 'float', 'name'=>'Electricity consumption', 'group'=>'Stats: Cooling', 'dp'=>0, 'unit'=>'kWh'), 'cooling_heat_kwh' => array('type' => 'float', 'name'=>'Heat output', 'group'=>'Stats: Cooling', 'dp'=>0, 'unit'=>'kWh'), 'cooling_cop' => array('type' => 'float', 'name'=>'COP', 'heading'=>"Cooling
COP", 'group'=>'Stats: Cooling', 'dp'=>1, 'unit'=>''), 'cooling_data_length' => array('type' => 'float', 'name'=>'Data length', 'group'=>'Stats: Cooling', 'dp'=>0, 'unit'=>''), 'cooling_elec_mean' => array('type' => 'float', 'name'=>'Elec mean', 'group'=>'Stats: Cooling', 'dp'=>0, 'unit'=>'W'), 'cooling_heat_mean' => array('type' => 'float', 'name'=>'Heat mean', 'group'=>'Stats: Cooling', 'dp'=>0, 'unit'=>'W'), 'cooling_flowT_mean' => array('type' => 'float', 'name'=>'FlowT mean', 'group'=>'Stats: Cooling', 'dp'=>1, 'unit'=>'°C'), 'cooling_returnT_mean' => array('type' => 'float', 'name'=>'ReturnT mean', 'group'=>'Stats: Cooling', 'dp'=>1, 'unit'=>'°C'), 'cooling_outsideT_mean' => array('type' => 'float', 'name'=>'OutsideT mean', 'group'=>'Stats: Cooling', 'dp'=>1, 'unit'=>'°C'), 'cooling_roomT_mean' => array('type' => 'float', 'name'=>'RoomT mean', 'group'=>'Stats: Cooling', 'dp'=>1, 'unit'=>'°C'), 'cooling_prc_carnot' => array('type' => 'float', 'name'=>'% Carnot', 'group'=>'Stats: Cooling', 'dp'=>1, 'unit'=>'%'), // Weighted averages 'weighted_flowT' => array('type' => 'float', 'name'=>'Weighted flowT', 'group'=>'Weighted averages', 'dp'=>1, 'unit'=>'°C'), 'weighted_outsideT' => array('type' => 'float', 'name'=>'Weighted outsideT', 'group'=>'Weighted averages', 'dp'=>1, 'unit'=>'°C'), 'weighted_flowT_minus_outsideT' => array('type' => 'float', 'name'=>'Weighted flowT - outsideT', 'group'=>'Weighted averages', 'dp'=>1, 'unit'=>'°C'), 'weighted_flowT_minus_returnT' => array('type' => 'float', 'name'=>'Weighted flowT - returnT', 'group'=>'Weighted averages', 'dp'=>1, 'unit'=>'°C'), 'weighted_elec' => array('type' => 'float', 'name'=>'Weighted elec', 'group'=>'Weighted averages', 'dp'=>0, 'unit'=>'W'), 'weighted_heat' => array('type' => 'float', 'name'=>'Weighted heat', 'group'=>'Weighted averages', 'dp'=>0, 'unit'=>'W'), 'weighted_prc_carnot' => array('type' => 'float', 'name'=>'Weighted % Carnot', 'group'=>'Weighted averages', 'dp'=>1, 'unit'=>'%'), 'weighted_kwh_elec' => array('type' => 'float', 'name'=>'Total elec kWh', 'group'=>'Weighted averages', 'dp'=>0, 'unit'=>'kWh'), 'weighted_kwh_heat' => array('type' => 'float', 'name'=>'Total heat kWh', 'group'=>'Weighted averages', 'dp'=>0, 'unit'=>'kWh'), 'weighted_kwh_heat_running' => array('type' => 'float', 'name'=>'Total heat running kWh', 'group'=>'Weighted averages', 'dp'=>0, 'unit'=>'kWh'), 'weighted_kwh_elec_running' => array('type' => 'float', 'name'=>'Total elec running kWh', 'group'=>'Weighted averages', 'dp'=>0, 'unit'=>'kWh'), 'weighted_kwh_carnot_elec' => array('type' => 'float', 'name'=>'Total carnot elec kWh', 'group'=>'Weighted averages', 'dp'=>0, 'unit'=>'kWh'), 'weighted_time_on' => array('type' => 'float', 'name'=>'Total time on', 'group'=>'Weighted averages', 'dp'=>0, 'unit'=>'s'), 'weighted_time_total' => array('type' => 'float', 'name'=>'Total time', 'group'=>'Weighted averages', 'dp'=>0, 'unit'=>'s'), 'weighted_cycle_count' => array('type' => 'float', 'name'=>'Total cycle count', 'group'=>'Weighted averages', 'dp'=>0, 'unit'=>''), // from energy feeds 'from_energy_feeds_elec_kwh' => array('type' => 'float', 'name'=>'Electricity consumption', 'group'=>'From energy feeds', 'dp'=>0, 'unit'=>'kWh'), 'from_energy_feeds_heat_kwh' => array('type' => 'float', 'name'=>'Heat output', 'group'=>'From energy feeds', 'dp'=>0, 'unit'=>'kWh'), 'from_energy_feeds_cop' => array('type' => 'float', 'name'=>'COP', 'group'=>'From energy feeds', 'dp'=>1, 'unit'=>''), // Quality 'quality_elec' => array('type' => 'float', 'name'=>'Quality elec', 'group'=>'Quality', 'dp'=>1, 'unit'=>'%'), 'quality_heat' => array('type' => 'float', 'name'=>'Quality heat', 'group'=>'Quality', 'dp'=>1, 'unit'=>'%'), 'quality_flowT' => array('type' => 'float', 'name'=>'Quality flowT', 'group'=>'Quality', 'dp'=>1, 'unit'=>'%'), 'quality_returnT' => array('type' => 'float', 'name'=>'Quality returnT', 'group'=>'Quality', 'dp'=>1, 'unit'=>'%'), 'quality_outsideT' => array('type' => 'float', 'name'=>'Quality outsideT', 'group'=>'Quality', 'dp'=>1, 'unit'=>'%'), 'quality_roomT' => array('type' => 'float', 'name'=>'Quality roomT', 'group'=>'Quality', 'dp'=>1, 'unit'=>'%'), // Errors 'error_air' => array('type' => 'int(11)', 'name'=>'Air error', 'group'=>'Errors', 'dp'=>0, 'unit'=>'s'), 'error_air_kwh' => array('type' => 'float', 'name'=>'Air error elec kWh', 'group'=>'Errors', 'dp'=>1, 'unit'=>'kWh'), // Auxilary consumption 'immersion_kwh' => array('type' => 'float', 'name'=>'Immersion', 'group'=>'Auxilary consumption', 'dp'=>0, 'unit'=>'kWh'), 'boiler_kwh' => array('type' => 'float', 'name'=>'Boiler', 'group'=>'Auxilary consumption', 'dp'=>0, 'unit'=>'kWh'), // Unit rates 'unit_rate_agile' => array('type' => 'float', 'name'=>'Unit rate agile', 'group'=>'Unit rates', 'dp'=>1, 'unit'=>'p/kWh'), 'unit_rate_cosy' => array('type' => 'float', 'name'=>'Unit rate cosy', 'group'=>'Unit rates', 'dp'=>1, 'unit'=>'p/kWh'), 'unit_rate_go' => array('type' => 'float', 'name'=>'Unit rate GO', 'group'=>'Unit rates', 'dp'=>1, 'unit'=>'p/kWh'), // 'unit_rate_eon_next_pumped_v2' => array('type' => 'float', 'name'=>'Unit rate EON Next Pumped V2', 'group'=>'Unit rates', 'dp'=>1, 'unit'=>'p/kWh'), ); // Copy the same structure for aggregated stats $schema['system_stats_monthly_v2'] = $schema['system_stats_daily']; $schema['system_stats_last7_v2'] = $schema['system_stats_daily']; $schema['system_stats_last30_v2'] = $schema['system_stats_daily']; $schema['system_stats_last90_v2'] = $schema['system_stats_daily']; $schema['system_stats_last365_v2'] = $schema['system_stats_daily']; $schema['system_stats_all_v2'] = $schema['system_stats_daily']; // Used for specific date range 05 September 2024 to 04 September 2025 $schema['system_stats_custom'] = $schema['system_stats_daily']; // Remove the timestamp field from the aggregated stats unset($schema['system_stats_last7_v2']['timestamp']); unset($schema['system_stats_last30_v2']['timestamp']); unset($schema['system_stats_last90_v2']['timestamp']); unset($schema['system_stats_last365_v2']['timestamp']); unset($schema['system_stats_all_v2']['timestamp']); unset($schema['system_stats_custom']['timestamp']); // Schema to hold a list of system_meta changes // timestamp, systemid, userid, field, old_value, new_value $schema['system_meta_changes'] = array( 'timestamp' => array('type' => 'int(11)'), 'systemid' => array('type' => 'int(11)'), 'userid' => array('type' => 'int(11)'), 'field' => array('type' => 'varchar(64)'), 'old_value' => array('type' => 'text'), 'new_value' => array('type' => 'text') ); // Per-user configuration for the system list view (My Systems) // config is a JSON blob: {"template":"topofthescops","columns":["..."],"headings":{"column_key":"Custom title"}} $schema['system_list_user_config'] = array( 'userid' => array('type' => 'int(11)', 'Null' => false, 'Key' => 'PRI'), 'config' => array('type' => 'text'), 'last_updated' => array('type' => 'int(11)') );