CREATE OR REPLACE FORCE VIEW "VW_MESSAGE_COUNT_LIST"
AS
SELECT urp.user_id AS user_id,
urp.patient_id AS patient_id,
COUNT(m.message_id) AS message_count,
MAX(m.updated_date) AS message_updated_date
FROM user_role_patient_lnk urp
LEFT JOIN messages m
ON m.patient_id = urp.patient_id
AND m.isusergenerated =1
AND TO_CHAR(m.created_date, 'MM-DD-YYYY') > TO_CHAR(SYSDATE - INTERVAL '7' DAY, 'MM-DD-YYYY')
LEFT JOIN module_lkp ml
ON ml.module_name='Group Messages'
LEFT JOIN network_role_module_permission nrml
ON nrml.network_role_id=urp.network_role_id
AND nrml.permission_id >9
AND ml.module_id =nrml.module_id
GROUP BY urp.user_id,
urp.patient_id;
CREATE OR REPLACE FORCE VIEW "VW_MESSAGE_ALERT_COUNT_LIST"
AS
SELECT urp.user_id AS user_id,
urp.patient_id AS patient_id,
COUNT(m.message_id) AS message_count,
MAX(m.updated_date) AS alert_updated_date
FROM user_role_patient_lnk urp
LEFT JOIN module_lkp ml
ON ml.module_name='Group Messages'
LEFT JOIN network_role_module_permission nrml
ON nrml.network_role_id=urp.network_role_id
AND nrml.permission_id >9
AND ml.module_id =nrml.module_id
LEFT JOIN messages m
ON m.patient_id = urp.patient_id
AND m.is_alert =1
AND m.isusergenerated =1
AND TO_CHAR(m.created_date, 'MM-DD-YYYY') > TO_CHAR(SYSDATE - INTERVAL '7' DAY, 'MM-DD-YYYY')
GROUP BY urp.user_id,
urp.patient_id;
CREATE OR REPLACE FORCE VIEW "VW_MESSAGE_ALERT_COUNT"
AS
SELECT v.user_id,
p.patient_id,
lower(p.name) AS name,
lower(p.last_name) AS last_name,
v.message_count,
v1.message_count AS alert_count,
v.message_updated_date,
v1.alert_updated_date
FROM vw_message_count_list v,
vw_message_alert_count_list v1,
patient_profile p,
user_role_patient_lnk urpl
WHERE p.patient_id =v.patient_id
AND v1.patient_id =p.patient_id
AND urpl.patient_id = p.patient_id
AND urpl.is_active = 1
AND urpl.user_id = v.user_id
AND v1.user_id =v.user_id
AND p.is_deleted =0
AND p.is_active =1;
alter table patient_profile add GENDER VARCHAR2(25 CHAR);
CREATE OR REPLACE FORCE VIEW "VW_PATIENT_DETAILS"
AS
SELECT p.patient_id,
p.name,
p.last_name,
p.middle_initial,
p.dob,
p.blood_group_id,
bl.bg_type,
p.is_active,
p.is_deleted,
p.birth_country,
p.spoken_language,
p.ethnic,
p.maritial_status,
p.height_feet,
p.weight,
p.nick_name,
p.height_inches,
p.gender
FROM patient_profile p
LEFT JOIN blood_group_lkp bl
ON p.blood_group_id = bl.blood_group_id;
alter table site_settings modify "VALUE" VARCHAR2(1500 CHAR);
insert into site_settings (variable_name, value) values ('html_footer','');
insert into site_settings (variable_name, value) values ('html_header','');
insert into site_settings (variable_name, value) values ('invitation_reject','Reject');
insert into site_settings (variable_name, value) values ('sitename','StrengthForCaring.com');
insert into site_settings (variable_name, value) values ('siteadmin','Strength for Caring Admin');
insert into site_settings (variable_name, value) values ('reject_invitation_url','/inviteresponse/reject/');
insert into site_settings (variable_name, value) values ('register_invitation_url','/signup/family/');
insert into site_settings (variable_name, value) values ('accept_invitation_url','?destination=inviteresponse/accept/');
insert into site_settings (variable_name, value) values ('forgot_password','/reset/password/');
insert into site_settings (variable_name, value) values ('adminmail','support@strengthforcaring.com');
insert into site_settings (variable_name, value) values ('default_image_path','/usr/local/src/bal.gif');
insert into site_settings (variable_name, value) values ('pageLength','0');
insert into site_settings (variable_name, value) values ('site_title','Strength for Caring');
insert into site_settings (variable_name, value) values ('register_tuscan','Register for Strength for Caring');
insert into site_settings (variable_name, value) values ('invitation_accept','Accept');
update site_settings set value = '' where variable_name = 'html_footer';
update site_settings set value = '' where variable_name = 'html_header';
commit;