Comment 1 for bug 539648

design notes for sharing database between northshoreport and mvhub and normalizing area_data,area_type

############
use cases:
############

    We have MVHub (Merrimack Valley), NorthShorePort (north shore) and TBA (springfield?) Each site covers a particula
r geographic region.
---
case set 0:
---
Agency or program has a service area that is both MV and NS, they shouldn't have to maintain two sets of records.

  ---Each agency or program should get (1) email reminder for each record
  ---Each agency or program should be reminded that their info goes on (2) sites
  --each agency or program

Examples:

    Dear <wide geographic agency>
      It is time to update your record click here:

   http://mvhub.com/rm=ql?id=34884848848484848

  ...by the way, your records will also be updated on http://northshoreport.org

  What if some agencies have some programs in geographic area of MVHub but not NorthShorePort and other programs in No
rthShorePort but not MVHub ?

    Do the programs with north shore only areas goto northshoreport.org?
    The ones with MVHub only goto mvhub
    All updates to a 3rd website ?
    does the update/data entry work like display to users, only show programs that match terratory?

eric: default hid non relviant agencies but have show button or like

---
case set 1:
---

    If an agency has no programs with a service area that overlaps with that greographic area, don't show that agency
on that site.

    If a program service area doesn't overlap with a websites coverage area, don't show program

---
case set 2:
---
  display services by town
    Generate a PDF of services available in Peabody

Report of services not in a town by town

Dummied up examples:

(report) SERVICE LEVELS PER TOWN:

AIDS EDUCATION
    Danvers (0)
    Peabody (3)
....
DRAMA CLASSES
    Lawrence (0)
    Beverly (0)
    Lowell (1)
    ...
    Tweaksbury (99)

FUEL ASSISTANCE
    Chelmsford (0)
    Lowell (1)
    Lynn (3)

Example:
SERVICES NOT FOUND IN TOWN
number of statewide & "everywhere" orgs in ()

    Chelmsford
        Citizen Prep (2)
        Asylum (1)

      Gang Prevention/Intervention (1)
.....
    Lynn
        Citizen Prep (2)
        Arts Youth (1)
        Adult Music lessons (2)

############
code/schema mods

  email reminders mention
  new reports
      list of towns with sub list of sevices not in that town
      pdf with services in a town
      list of services with sub list of towns and (# services) of that type per town
  new code to display agency / program only if in site's service area

############
TABLES NOW
############
TABLE program:
 area_type
      TOWN,COUNTY,EVERYWHERE,STATE
  area_data

  DELIM<city>DELIM<city>DELIM<city>

'Middlesex County', "Essex County"

TABLE town:
  town_state:
    'Lowell MA',"
  display:
    boolean
  region:
    'greater lowell', 'greater lawrence', null

#########
TABLES LATER
#########

program:
  ??? area_everywhere
  ???? area_state

  -area_type
  -area_data

program_town:
    program_id
    town_id

program_everywhere
  program_id

program_state
  program_id
  state_id

program_county
  program_id

county_town
  county_id
  town_id

county
  name
  id

town:
  name: (Lowell, Boston, Cambride)
  state: (MA, NH,RI)
  id: <int>

 site_town

 site
    name (mvhub, northshoreport)
    id

# or three tables site_state, site_county, site_town
site_area
  site_id
   area_type (state,county,town)
   area_id (state_id, county_id,town_id)

#################
misc existing notes
#################

program.pl:
main();
  handle_main_program_form();
      add_areas_served_to_record()

validate_more()

add_areas_served_to_record()
add_areas_served_to_form
get_selectable_towns_from_db

area_type: EVERYWHERE, STATE, COUNTY
COUNTIES" Middlesex, Essex

$AREAS_SERVED_CBOX_PREFIX

cboxarea_TOWN

guide.pm
  show_program