How to Track the Number of Authorised Sessions

[slh]

These scripts check the total number of sessions and compare it against the number of authorised sessions saved in some custom fields on the Patient record.

It’s still a work in progress, I’ll update as I go.

frmEditorApp.OnAfterSaveRecord();

using System.Collections.Generic;

using System.Data;

frmEditorBaseRec Editor = (Params[0] as frmEditorBaseRec);

cApp app = (Editor.Record as cApp);

//PSDevEx.cMessages.InformationBox(Convert.ToString(app.per_id));
//Convert.ToString(app.id)

DataTable tbl_pat = cGlobals.DB.GetCannedTable("select pc.name as category, ps.name as status, cl.name as clinic, cpf.returnsessions, cpf.rfat, cpf.sessionauthdate from per p left join patcat pc on pc.id = p.patcat_id left join patstatus ps on ps.id = p.patstatus_id left join clinic cl on cl.id = p.clinic_id left join custom_patient_fields cpf on cpf.per_id = p.id where p.id= "+ app.per_id);

string patient_category = Convert.ToString(tbl_pat.Rows[0][0]);
string patient_status = Convert.ToString(tbl_pat.Rows[0][1]);
string clinic_name = Convert.ToString(tbl_pat.Rows[0][2]);
//PSDevEx.cMessages.InformationBox(Convert.ToString(tbl_pat.Rows[0][3]));

int return_sessions;
int rfat;
string session_auth_date;

if(tbl_pat.Rows[0][3] == DBNull.Value){
return_sessions = Convert.ToInt32(0);
}else{
return_sessions = Convert.ToInt32(tbl_pat.Rows[0][3]);
}
if(tbl_pat.Rows[0][4] == DBNull.Value){
rfat = Convert.ToInt32(0);
}else{
rfat = Convert.ToInt32(tbl_pat.Rows[0][4]);
}
if(tbl_pat.Rows[0][5] == DBNull.Value){
session_auth_date = "";
}else{
session_auth_date = Convert.ToString(tbl_pat.Rows[0][5]);
}

//PSDevEx.cMessages.InformationBox(patient_category+" "+patient_status+ " "+clinic_name);

if(string.Equals(clinic_name, "MacFarlane Physiotherapy")){

if(string.Equals(patient_category, "Insurance") && string.Equals(patient_status, "Active")){

DataTable tbl_app = cGlobals.DB.GetCannedTable("select count(*) as c from app a left join staff s on s.id = a.staff_id left join apptype on apptype.id = a.apptype_id left join appstatus on appstatus.id = a.appstatus_id where appstatus.name='Completed' and apptype.name != 'Private Return' AND apptype.name != 'Private IA' and a.start >= '"+session_auth_date+"' and a.per_id = "+ app.per_id);

int sessions_taken = Convert.ToInt32(tbl_app.Rows[0][0]);

int ia = 1;

int total_sessions = ia + return_sessions + rfat;

int sessions_remaining = total_sessions - sessions_taken;

//PSDevEx.cMessages.InformationBox("Return:"+return_sessions+" RFAT:"+rfat+ " Total:"+total_sessions+" Taken:"+sessions_taken+" Remaining:"+sessions_remaining);

if (sessions_remaining == 0){
PSDevEx.cMessages.InformationBox("Patient's Last Session ("+Convert.ToString(sessions_remaining)+")");
}
if (sessions_remaining == -1){
PSDevEx.cMessages.InformationBox("No Sessions Left ("+Convert.ToString(sessions_remaining)+")");
}
if (sessions_remaining <= -2){
PSDevEx.cMessages.InformationBox("Patient has over ran their authorised treatment ("+Convert.ToString(sessions_remaining)+")");
}

}

}

frmEditorPatient.OnAfterLoadRecord()

using System.Collections.Generic;

using System.Data;

frmEditorBaseRec Editor = (Params[0] as frmEditorBaseRec);

cPer per = (Editor.Record as cPer);

cDBBaseRec custom = (Editor.Records[1]);

DataTable tbl_pat = cGlobals.DB.GetCannedTable("select pc.name as category, ps.name as status, cl.name as clinic from per p left join patcat pc on pc.id = p.patcat_id left join patstatus ps on ps.id = p.patstatus_id left join clinic cl on cl.id = p.clinic_id where p.id= "+ per.id);

string patient_category = Convert.ToString(tbl_pat.Rows[0][0]);
string patient_status = Convert.ToString(tbl_pat.Rows[0][1]);
string clinic_name = Convert.ToString(tbl_pat.Rows[0][2]);

//PSDevEx.cMessages.InformationBox(patient_category+" "+patient_status+ " "+clinic_name);

if(string.Equals(clinic_name, "MacFarlane Physiotherapy")){

if(string.Equals(patient_category, "Insurance") && string.Equals(patient_status, "Active")){

cDBBaseRec custom_patient_fields = Editor.CustomRec;

string session_auth_date = Convert.ToString(custom_patient_fields.Row["sessionauthdate"]);

if(string.Equals(session_auth_date,"")){
session_auth_date = "";
}

//DateTime dt_session_auth_date = DateTime.Parse(session_auth_date);

//PSDevEx.cMessages.InformationBox(session_auth_date);

DataTable tbl_app = cGlobals.DB.GetCannedTable("select count(*) as c from app a left join staff s on s.id = a.staff_id left join apptype on apptype.id = a.apptype_id left join appstatus on appstatus.id = a.appstatus_id where appstatus.name='Completed' and apptype.name != 'Private Return' AND apptype.name != 'Private IA' and a.start >= '"+session_auth_date+"' and a.per_id = "+ per.id);

int sessions_taken = Convert.ToInt32(tbl_app.Rows[0][0]);

int ia = 1;

int return_sessions = PSLib.cDBFuncs.ToInt(custom_patient_fields.Row["returnsessions"]);

int rfat = PSLib.cDBFuncs.ToInt(custom_patient_fields.Row["rfat"]);

int total_sessions = ia + return_sessions + rfat;

int sessions_remaining = total_sessions - sessions_taken;

//PSDevEx.cMessages.InformationBox("Return:"+return_sessions+" RFAT:"+rfat+ " Total:"+total_sessions+" Taken:"+sessions_taken+" Remaining:"+sessions_remaining);

string sql = "update custom_patient_fields set sessionsremaining=" + Convert.ToInt32(sessions_remaining) +" where id=" + Convert.ToString(custom_patient_fields.id);

PSDevEx.cMessages.InformationBox("Patient has "+sessions_remaining+" sessions remaining.");

cGlobals.DB.ExecSQL(sql);

}

}

frmEditorPatient.OnAfterSaveRecord();

using System.Collections.Generic;

using System.Data;

frmEditorBaseRec Editor = (Params[0] as frmEditorBaseRec);

cPer per = (Editor.Record as cPer);

cDBBaseRec custom = (Editor.Records[1]);

DataTable tbl_pat = cGlobals.DB.GetCannedTable("select pc.name as category, ps.name as status, cl.name as clinic from per p left join patcat pc on pc.id = p.patcat_id left join patstatus ps on ps.id = p.patstatus_id left join clinic cl on cl.id = p.clinic_id where p.id= "+ per.id);

string patient_category = Convert.ToString(tbl_pat.Rows[0][0]);
string patient_status = Convert.ToString(tbl_pat.Rows[0][1]);
string clinic_name = Convert.ToString(tbl_pat.Rows[0][2]);

//PSDevEx.cMessages.InformationBox(patient_category+" "+patient_status+ " "+clinic_name);

if(string.Equals(clinic_name, "MacFarlane Physiotherapy")){

if(string.Equals(patient_category, "Insurance") && string.Equals(patient_status, "Active")){

cDBBaseRec custom_patient_fields = Editor.CustomRec;

string session_auth_date = Convert.ToString(custom_patient_fields.Row["sessionauthdate"]);

DataTable tbl_app = cGlobals.DB.GetCannedTable("select count(*) as c from app a left join staff s on s.id = a.staff_id left join apptype on apptype.id = a.apptype_id left join appstatus on appstatus.id = a.appstatus_id where appstatus.name='Completed' and apptype.name != 'Private Return' AND apptype.name != 'Private IA' and a.start > '"+session_auth_date+"' and a.per_id = "+ per.id);

int sessions_taken = Convert.ToInt32(tbl_app.Rows[0][0]);

int ia = 1;

int return_sessions = PSLib.cDBFuncs.ToInt(custom_patient_fields.Row["returnsessions"]);

int rfat = PSLib.cDBFuncs.ToInt(custom_patient_fields.Row["rfat"]);

int total_sessions = ia + return_sessions + rfat;

int sessions_remaining = total_sessions - sessions_taken;

//PSDevEx.cMessages.InformationBox("Return:"+return_sessions+" RFAT:"+rfat+ " Total:"+total_sessions+" Taken:"+sessions_taken+" Remaining:"+sessions_remaining);

string sql = "update custom_patient_fields set sessionsremaining=" + Convert.ToInt32(sessions_remaining) +" where id=" + Convert.ToString(custom_patient_fields.id);

//PSDevEx.cMessages.InformationBox(sql);

cGlobals.DB.ExecSQL(sql);

PSDevEx.cMessages.InformationBox("Patient has "+Convert.ToString(sessions_remaining)+" sessions remaining.");

if (sessions_remaining == 0){
PSDevEx.cMessages.InformationBox("Patient's Last Session ("+Convert.ToString(sessions_remaining)+")");
}
if (sessions_remaining == -1){
PSDevEx.cMessages.InformationBox("No Sessions Left ("+Convert.ToString(sessions_remaining)+")");
}
if (sessions_remaining <= -2){
PSDevEx.cMessages.InformationBox("Patient has over ran their authorised treatment ("+Convert.ToString(sessions_remaining)+")");
}

if(Convert.ToInt32(sessions_remaining) <= 0){
//PSDevEx.cMessages.InformationBox("Patient has "+Convert.ToString(sessions_remaining)+" sessions remaining.");
}

}

}

 

 

 

[Author]

Posts