How to Track the Number of Authorised Sessions

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.");
}

}

}