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;