def import_delegates():
db.engine.execute("""
CREATE TABLE IF NOT EXISTS etl_delegate (
practice_code VARCHAR(255),
instance INT,
name VARCHAR(255),
role VARCHAR(255),
gcp_trained BIT,
gv_trained BIT,
on_delegation_log_yn BIT,
gv_start_del_log DATE,
gv_end_del_log DATE,
rsn_not_on_del_log VARCHAR(500),
gv_phone_a VARCHAR(100),
gv_phone_b VARCHAR(100),
contact_email_add VARCHAR(100),
primary_contact_yn BIT
);
""")
db.engine.execute("""
CREATE INDEX idx__etl_delegates__practice_code ON etl_delegate(practice_code);
""")
imports = []
with etl_import_database() as r_db:
for r in r_db.execute(delegate_table.select()):
imports.append(Delegate(
practice_code=r['practice_code'],
instance=r['instance'],
name=r['name'],
role=r['role'],
gcp_trained=r['gcp_trained'],
gv_trained=r['gv_trained'],
on_delegation_log_yn=r['on_delegation_log_yn'],
gv_start_del_log=parse_date(r['gv_start_del_log']),
gv_end_del_log=parse_date(r['gv_end_del_log']),
gv_phone_a=r['gv_phone_a'],
gv_phone_b=r['gv_phone_b'],
contact_email_add=r['contact_email_add'],
primary_contact_yn=r['primary_contact_yn'],
))
db.session.add_all(imports)
db.session.flush()
db.session.commit()
def import_user():
current_app.logger.info('Importing user details')
existing_users = {u.email: u for u in User.query.options(joinedload('practices')).all()}
practices = {p.code: p for p in Practice.query.all()}
users = []
with etl_practice_database() as p_db:
for email, details in groupby(p_db.execute(user_table.select().order_by(user_table.c.email)), key=lambda x: x['email'].lower()):
user = existing_users.get(email, None)
if user is None:
user = User(email=email)
ds = list(details)
u = ds[0]
user.project_id = u['project_id']
user.current_portal_user_yn = bool(u['current_portal_user_yn'])
user.gv_end_del_log = parse_date(u['gv_end_del_log'])
user.last_update_timestamp = u['last_update_timestamp']
user.is_imported = True
user.active = bool(u['current_portal_user_yn'])
user.practices = set([practices[d['practice_code']] for d in ds])
users.append(user)
db.session.add_all(users)
db.session.flush()
for u in User.query.filter(User.id.notin_([u.id for u in users]), User.is_imported.is_(True)).all():
db.session.delete(u)
db.session.commit()
def agree_66_end_date_2(self):
if self.agree_66_end_date_2_str:
return parse_date(self.agree_66_end_date_2_str).date()
def agree_66_signed_date_1(self):
if self.agree_66_signed_date_1_str:
return parse_date(self.agree_66_signed_date_1_str).date()
def isa_1_caldicott_guard_end(self):
if self.isa_1_caldicott_guard_end_str:
return parse_date(self.isa_1_caldicott_guard_end_str).date()
def isa_1_signed_date(self):
if self.isa_1_signed_date_str:
return parse_date(self.isa_1_signed_date_str).date()
def collab_ag_signed_date(self):
if self.collab_ag_signed_date_str:
return parse_date(self.collab_ag_signed_date_str).date()
def import_practices():
db.engine.execute("""
CREATE TABLE IF NOT EXISTS etl_practice_detail (
project_id INT,
ccg INT,
federation INT,
code VARCHAR(255),
name VARCHAR(255),
street_address VARCHAR(255),
town VARCHAR(255),
city VARCHAR(255),
county VARCHAR(255),
postcode VARCHAR(255),
partners VARCHAR(255),
collab_ag_comp_yn BIT,
collab_ag_signed_date_str VARCHAR(100),
isa_comp_yn BIT,
isa_1_signed_date_str VARCHAR(255),
isa_1_caldicott_guard_end_str VARCHAR(255),
agree_66_comp_yn BIT,
agree_66_signed_date_1_str VARCHAR(255),
agree_66_end_date_2_str VARCHAR(255),
genvasc_initiated BIT,
status_id INT NULL
);
""")
db.engine.execute("""
CREATE INDEX idx__etl_practice_detail__practice_code ON etl_practice_detail(code);
""")
db.engine.execute("""
CREATE INDEX idx__etl_practice_detail__ccg ON etl_practice_detail(ccg);
""")
db.engine.execute("""
CREATE INDEX idx__etl_practice_detail__federation ON etl_practice_detail(federation);
""")
imports = []
with etl_import_database() as r_db:
for r in r_db.execute(practice_table.select()):
imports.append(Practice(
project_id=r['project_id'],
code=r['code'],
name=r['name'],
ccg=r['ccg'],
street_address=r['street_address'],
town=r['town'],
city=r['city'],
county=r['county'],
postcode=r['postcode'],
federation=r['federation'],
partners=r['partners'],
collab_ag_comp_yn=r['collab_ag_comp_yn'],
collab_ag_signed_date_str=parse_date(r['collab_ag_signed_date_str']),
isa_comp_yn=r['isa_comp_yn'],
isa_1_signed_date_str=parse_date(r['isa_1_signed_date_str']),
isa_1_caldicott_guard_end_str=parse_date(r['isa_1_caldicott_guard_end_str']),
agree_66_comp_yn=r['agree_66_comp_yn'],
agree_66_signed_date_1_str=parse_date(r['agree_66_signed_date_1_str']),
agree_66_end_date_2_str=parse_date(r['agree_66_end_date_2_str']),
genvasc_initiated=r['genvasc_initiated'] in ('1', 1),
status_id=r['status_id'],
))
db.session.add_all(imports)
db.session.flush()
db.session.commit()