Export

[lowwintersun]

Enable export of data in to PEC CRM database.

[AppSynergy]

• Need connection information for the PEC CRM database.
• What data to be inserted/updated into what table?

[lowwintersun]

Data to be exported:

• all fields that data is entered in to
• list of recommendations + any notes assigned to these.

Am waiting on details of the CRM system.

[AppSynergy]

all fields that data is entered in to

That's a lot of things!

e.g.

{
  "assessment": {
    "id": 4,
    "assessor_name": "Adam Marshall",
    "assessment_date": "2018-01-01",
    "homeowner_name": "James Testington",
    "homeowner_email": "james@example.com",
    "homeowner_phone": "07444 333 222",
    "homeowner_address": "53 Ballamaddrell\nPort Erin\nIM9 777",
    "home_type": null,
    "created_at": "2017-10-11 10:28:52",
    "updated_at": "2017-10-30 10:21:12",
    "comfort_rate_comfort": 2,
    "comfort_rate_health": 3,
    "comfort_rate_environment": 2,
    "comfort_rate_saving_money": 4,
    "comfort_rate_temperature_summer": 1,
    "comfort_rate_temperature_winter": 1,
    "comfort_rate_humidity_summer": 1,
    "comfort_rate_humidity_winter": 3,
    "comfort_rate_airflow_summer": 2,
    "comfort_rate_airflow_winter": 3,
    "comfort_rate_natural_light": 1,
    "comfort_rate_artificial_light": 2,
    "comfort_rate_noise_levels": 2,
    "comfort_general": "It's generally comfortable.",
    "comfort_favourite_room": "Lounge.",
    "comfort_least_loved_room": "Attic.",
    "comfort_other_comments": "No other comment.",
    "reading_temperature_living_room": 44,
    "reading_humidity_living_room": 33,
    "reading_surface_temperature_living_room": 22,
    "reading_temperature_bedroom": 11,
    "reading_humidity_bedroom": 6,
    "reading_surface_temperature_bedroom": 7,
    "reading_air_quality": 8,
    "health_surface_temperature_notes": "No cold spots.",
    "health_condensation": "some",
    "health_damp": "yep",
    "health_mold": "near the fans",
    "health_ventilation": "he's in the ducts!",
    "health_laundry": "sure",
    "health_air_quality": "rich",
    "submitted": 1,
    "homeowner_uniqueid": "JT53B001",
    "misc_comments": "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Donec odio. Quisque volutpat mattis eros. Nullam malesuada erat ut turpis. Suspendisse urna nibh, viverra non, semper suscipit, posuere a, pede.\n\nDonec nec justo eget felis facilisis fermentum. Aliquam porttitor mauris sit amet orci. Aenean dignissim pellentesque felis.\n\nMorbi in sem quis dui placerat ornare. Pellentesque odio nisi, euismod in, pharetra a, ultricies in, diam. Sed arcu. Cras consequat.\n\nPraesent dapibus, neque id cursus faucibus, tortor neque egestas augue, eu vulputate magna eros eu erat. Aliquam erat volutpat. Nam dui mi, tincidunt quis, accumsan porttitor, facilisis luctus, metus.",
    "checklist_health": "Donec nec justo eget felis facilisis fermentum.",
    "checklist_warm_discount": "Donec nec justo eget felis facilisis fermentum.",
    "checklist_priority": "Donec nec justo eget felis facilisis fermentum.",
    "checklist_fuel_debt": "Donec nec justo eget felis facilisis fermentum.",
    "checklist_supplier_issues": "Donec nec justo eget felis facilisis fermentum.",
    "checklist_water_debt": "Donec nec justo eget felis facilisis fermentum.",
    "checklist_switching": "Donec nec justo eget felis facilisis fermentum.",
    "checklist_income_max": "Donec nec justo eget felis facilisis fermentum.",
    "checklist_fire_safety": "Donec nec justo eget felis facilisis fermentum.",
    "checklist_further_visit": 0,
    "checklist_further_assistance": "Donec nec justo eget felis facilisis fermentum. Donec nec justo eget felis facilisis fermentum."
  },
  "improvements": [
    {
      "improvement_id": 1,
      "value": "have",
      "comment": "here's an actual comment."
    },
    {
      "improvement_id": 2,
      "value": "need",
      "comment": "here's an actual comment."
    },
    {
      "improvement_id": 3,
      "value": "n/a",
      "comment": "here's an actual comment."
    },
    // etc!
  ]
}

[lowwintersun]

Cool, this is the kind of thing I had in mind. I'm waiting on Marianne to cut down a fair few of the temperature and humidity readings as well.

If worse comes to the worst I think we just export this file and they can work out how to import it (TBH I suspect this is what they'll want).

One thing that's missing from here is the measures that have been recommended - I think this will be important to flag up impact.

[AppSynergy]

Every field is there... do you mean these? I only included three, but there's 40-something of them:

{
      "improvement_id": 1,
      "value": "need",
      "comment": "Something about which thermal curtains you need."
},

[lowwintersun]

Duh, sorry, Monday morning! Thanks Adam.

I'm going to send this export to PEC, see what they make of it.

[AppSynergy]

I'd assume they'd need a similar db setup to the one the app runs internally to make the best of the data...

• e.g. two tables (assessments & improvements, foreign keyed)
• or they could pack the whole data object down into one string (as above) if they're limited by their options.

[lowwintersun]

PEC's CRM staff member has left so this is on hold - need to decide how to leave this ie do we leave as above, park time etc.

[AppSynergy]

I guess we just leave this here for now.

The initial spec assumed this would be as simple as connecting to a MySQL db and making a few INSERT calls.

If this is stuck because the reality is vastly more complicated, we may need a re-think.

[lowwintersun]

OK, it may be beyond the agreed specification - let's see what they come back with. I pushed this a lot so there's not much else we can do for now.