///<summary></summary>
public static void Update(LetterMerge merge){
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
Meth.GetVoid(MethodBase.GetCurrentMethod(),merge);
return;
}
Crud.LetterMergeCrud.Update(merge);
}
///<summary>Inserts this lettermerge into database.</summary>
public static long Insert(LetterMerge merge) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
merge.LetterMergeNum=Meth.GetLong(MethodBase.GetCurrentMethod(),merge);
return merge.LetterMergeNum;
}
return Crud.LetterMergeCrud.Insert(merge);
}
//private ArrayList ALpatSelect;
///<summary></summary>
public FormLetterMergeEdit(LetterMerge letterMergeCur)
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
LetterMergeCur=letterMergeCur;
Lan.F(this);
}
///<summary></summary>
public static void Update(LetterMerge merge)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), merge);
return;
}
Crud.LetterMergeCrud.Update(merge);
}
///<summary>Inserts this lettermerge into database.</summary>
public static long Insert(LetterMerge merge)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
merge.LetterMergeNum = Meth.GetLong(MethodBase.GetCurrentMethod(), merge);
return(merge.LetterMergeNum);
}
return(Crud.LetterMergeCrud.Insert(merge));
}
///<summary></summary>
public static void Delete(LetterMerge merge)
{
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
Meth.GetVoid(MethodBase.GetCurrentMethod(),merge);
return;
}
string command="DELETE FROM lettermerge "
+"WHERE LetterMergeNum = "+POut.Long(merge.LetterMergeNum);
Db.NonQ(command);
}
///<summary></summary>
public static void Delete(LetterMerge merge)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), merge);
return;
}
string command = "DELETE FROM lettermerge "
+ "WHERE LetterMergeNum = " + POut.Long(merge.LetterMergeNum);
Db.NonQ(command);
}
private void butAdd_Click(object sender, System.EventArgs e)
{
if(listCategories.SelectedIndex==-1){
MsgBox.Show(this,"Please select a category first.");
return;
}
LetterMerge letter=new LetterMerge();
letter.Category=DefC.Short[(int)DefCat.LetterMergeCats][listCategories.SelectedIndex].DefNum;
letter.Fields=new List<string>();
FormLetterMergeEdit FormL=new FormLetterMergeEdit(letter);
FormL.IsNew=true;
FormL.ShowDialog();
FillLetters();
changed=true;
}
private bool CreateDataFile(string fileName,LetterMerge letter)
{
DataTable table=LetterMergesQueries.GetLetterMergeInfo(PatCur,letter);
table=FormQuery.MakeReadable(table,null);
try{
using(StreamWriter sw=new StreamWriter(fileName,false)){
string line="";
for(int i=0;i<letter.Fields.Count;i++){
if(letter.Fields[i].StartsWith("referral.")){
line+="Ref"+letter.Fields[i].Substring(9);
}
else{
line+=letter.Fields[i];
}
if(i<letter.Fields.Count-1){
line+="\t";
}
}
sw.WriteLine(line);
string cell;
for(int i=0;i<table.Rows.Count;i++){
line="";
for(int j=0;j<table.Columns.Count;j++){
cell=table.Rows[i][j].ToString();
cell=cell.Replace("\r","");
cell=cell.Replace("\n","");
cell=cell.Replace("\t","");
cell=cell.Replace("\"","");
line+=cell;
if(j<table.Columns.Count-1){
line+="\t";
}
}
sw.WriteLine(line);
}
}
}
catch{
MsgBox.Show(this,"File in use by another program. Close and try again.");
return false;
}
return true;
}
public static DataTable GetLetterMergeInfo(Patient PatCur,LetterMerge letter){
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetTable(MethodBase.GetCurrentMethod(),PatCur,letter);
}
//jsparks- This is messy and prone to bugs. It needs to be reworked to work just like
//in SheetFiller.FillFieldsInStaticText. Just grab a bunch of separate objects
//instead of one result row.
string command;
//We need a very small table that tells us which tp is the most recent.
//command="DROP TABLE IF EXISTS temptp;";
//Db.NonQ(command);
//command=@"CREATE TABLE temptp(
// DateTP date NOT NULL default '0001-01-01')";
//Db.NonQ(command);
//command+=@"CREATE TABLE temptp
// SELECT MAX(treatplan.DateTP) DateTP
// FROM treatplan
// WHERE PatNum="+POut.PInt(PatCur.PatNum)+";";
//Db.NonQ(command);
command="SET @maxTpDate=(SELECT MAX(treatplan.DateTP) FROM treatplan WHERE PatNum="+POut.Long(PatCur.PatNum)+");";
command+="SELECT ";
for(int i=0;i<letter.Fields.Count;i++) {
if(i>0) {
command+=",";
}
if(letter.Fields[i]=="NextAptNum") {
command+="MAX(plannedappt.AptNum) NextAptNum";
}
//other:
else if(letter.Fields[i]=="TPResponsPartyNameFL") {
command+=DbHelper.Concat("MAX(patResp.FName)","' '","MAX(patResp.LName)")+" TPResponsPartyNameFL";
}
else if(letter.Fields[i]=="TPResponsPartyAddress") {
command+="MAX(patResp.Address) TPResponsPartyAddress";
}
else if(letter.Fields[i]=="TPResponsPartyCityStZip") {
command+=DbHelper.Concat("MAX(patResp.City)","', '","MAX(patResp.State)","' '","MAX(patResp.Zip)")+" TPResponsPartyCityStZip";
}
else if(letter.Fields[i]=="SiteDescription") {
command+="MAX(site.Description) SiteDescription";
}
else if(letter.Fields[i]=="DateOfLastSavedTP") {
command+=DbHelper.DtimeToDate("MAX(treatplan.DateTP)")+" DateOfLastSavedTP";
}
else if(letter.Fields[i]=="DateRecallDue") {
command+="MAX(recall.DateDue) DateRecallDue";
}
else if(letter.Fields[i]=="CarrierName") {
command+="MAX(CarrierName) CarrierName";
}
else if(letter.Fields[i]=="CarrierAddress") {
command+="MAX(carrier.Address) CarrierAddress";
}
else if(letter.Fields[i]=="CarrierCityStZip") {
command+=DbHelper.Concat("MAX(carrier.City)","', '","MAX(carrier.State)","' '","MAX(carrier.Zip)")+" CarrierCityStZip";
}
else if(letter.Fields[i]=="SubscriberNameFL") {
command+=DbHelper.Concat("MAX(patSubsc.FName)","' '","MAX(patSubsc.LName)")+" SubscriberNameFL";
}
else if(letter.Fields[i]=="SubscriberID") {
command+="MAX(inssub.SubscriberID) SubscriberID";
}
else if(letter.Fields[i]=="NextSchedAppt") {
command+="MIN(appointment.AptDateTime) NextSchedAppt";
}
else if(letter.Fields[i]=="Age") {
command+="MAX(patient.Birthdate) BirthdateForAge";
}
else if(letter.Fields[i]=="Guarantor") {
command+=DbHelper.Concat("MAX(patGuar.FName)","' '","MAX(patGuar.LName)")+" Guarantor";
}
else if(letter.Fields[i]=="GradeSchool"){
command+="MAX(site.Description) GradeSchool";
}
else if(letter.Fields[i].StartsWith("referral.")) {
command+="MAX(referral."+letter.Fields[i].Substring(9)+") "+letter.Fields[i].Substring(9);
}
else if(letter.Fields[i]=="Race") {//This is to accomodate the depricated patient.Race column that no longer exists
command+="'"+POut.String(string.Join(",",PatientRaces.GetPatRaceList(PatCur.PatNum)))+"'"+" Race";//gets comma delimited list of PatRace values as ints stored in the patientrace table. MakeReadable is called on the results of this query and this value is converted into a comma delimited list of race strings.
}
else {
command+="MAX(patient."+letter.Fields[i]+") "+letter.Fields[i];
}
}
command+=" FROM patient "
+"LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.IsFrom=1 "
+"LEFT JOIN referral ON refattach.ReferralNum=referral.ReferralNum "
+"LEFT JOIN plannedappt ON plannedappt.PatNum=patient.PatNum AND plannedappt.ItemOrder=1 "
+"LEFT JOIN site ON patient.SiteNum=site.SiteNum "
+"LEFT JOIN treatplan ON patient.PatNum=treatplan.PatNum AND DateTP=@maxTpDate "
+"LEFT JOIN patient patResp ON treatplan.ResponsParty=patResp.PatNum "
+"LEFT JOIN recall ON recall.PatNum=patient.PatNum "
+"AND (recall.RecallTypeNum="+POut.Long(PrefC.GetLong(PrefName.RecallTypeSpecialProphy))
+" OR recall.RecallTypeNum="+POut.Long(PrefC.GetLong(PrefName.RecallTypeSpecialPerio))+") "
+"LEFT JOIN patplan ON patplan.PatNum=patient.PatNum AND Ordinal=1 "
+"LEFT JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum "
+"LEFT JOIN insplan ON inssub.PlanNum=insplan.PlanNum "
+"LEFT JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum "
+"LEFT JOIN patient patSubsc ON patSubsc.PatNum=inssub.Subscriber "
+"LEFT JOIN appointment ON appointment.PatNum=patient.PatNum "
+"AND AptStatus="+POut.Long((int)ApptStatus.Scheduled)+" "
+"AND AptDateTime > "+DbHelper.Now()+" "
+"LEFT JOIN patient patGuar ON patGuar.PatNum=patient.Guarantor "
+"WHERE patient.PatNum="+POut.Long(PatCur.PatNum)
+" GROUP BY patient.PatNum "
+"ORDER BY refattach.ItemOrder";
return Db.GetTable(command);
}
public static DataTable GetLetterMergeInfo(Patient PatCur, LetterMerge letter)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), PatCur, letter));
}
//jsparks- This is messy and prone to bugs. It needs to be reworked to work just like
//in SheetFiller.FillFieldsInStaticText. Just grab a bunch of separate objects
//instead of one result row.
string command;
//We need a very small table that tells us which tp is the most recent.
//command="DROP TABLE IF EXISTS temptp;";
//Db.NonQ(command);
//command=@"CREATE TABLE temptp(
// DateTP date NOT NULL default '0001-01-01')";
//Db.NonQ(command);
//command+=@"CREATE TABLE temptp
// SELECT MAX(treatplan.DateTP) DateTP
// FROM treatplan
// WHERE PatNum="+POut.PInt(PatCur.PatNum)+";";
//Db.NonQ(command);
command = "SET @maxTpDate=(SELECT MAX(treatplan.DateTP) FROM treatplan WHERE PatNum=" + POut.Long(PatCur.PatNum) + ");";
command += "SELECT ";
for (int i = 0; i < letter.Fields.Count; i++)
{
if (i > 0)
{
command += ",";
}
if (letter.Fields[i] == "NextAptNum")
{
command += "MAX(plannedappt.AptNum) NextAptNum";
}
//other:
else if (letter.Fields[i] == "TPResponsPartyNameFL")
{
command += DbHelper.Concat("MAX(patResp.FName)", "' '", "MAX(patResp.LName)") + " TPResponsPartyNameFL";
}
else if (letter.Fields[i] == "TPResponsPartyAddress")
{
command += "MAX(patResp.Address) TPResponsPartyAddress";
}
else if (letter.Fields[i] == "TPResponsPartyCityStZip")
{
command += DbHelper.Concat("MAX(patResp.City)", "', '", "MAX(patResp.State)", "' '", "MAX(patResp.Zip)") + " TPResponsPartyCityStZip";
}
else if (letter.Fields[i] == "SiteDescription")
{
command += "MAX(site.Description) SiteDescription";
}
else if (letter.Fields[i] == "DateOfLastSavedTP")
{
command += DbHelper.DateColumn("MAX(treatplan.DateTP)") + " DateOfLastSavedTP";
}
else if (letter.Fields[i] == "DateRecallDue")
{
command += "MAX(recall.DateDue) DateRecallDue";
}
else if (letter.Fields[i] == "CarrierName")
{
command += "MAX(CarrierName) CarrierName";
}
else if (letter.Fields[i] == "CarrierAddress")
{
command += "MAX(carrier.Address) CarrierAddress";
}
else if (letter.Fields[i] == "CarrierCityStZip")
{
command += DbHelper.Concat("MAX(carrier.City)", "', '", "MAX(carrier.State)", "' '", "MAX(carrier.Zip)") + " CarrierCityStZip";
}
else if (letter.Fields[i] == "SubscriberNameFL")
{
command += DbHelper.Concat("MAX(patSubsc.FName)", "' '", "MAX(patSubsc.LName)") + " SubscriberNameFL";
}
else if (letter.Fields[i] == "SubscriberID")
{
command += "MAX(inssub.SubscriberID) SubscriberID";
}
else if (letter.Fields[i] == "NextSchedAppt")
{
command += "MIN(appointment.AptDateTime) NextSchedAppt";
}
else if (letter.Fields[i] == "Age")
{
command += "MAX(patient.Birthdate) BirthdateForAge";
}
else if (letter.Fields[i] == "Guarantor")
{
command += DbHelper.Concat("MAX(patGuar.FName)", "' '", "MAX(patGuar.LName)") + " Guarantor";
}
else if (letter.Fields[i] == "GradeSchool")
{
command += "MAX(site.Description) GradeSchool";
}
else if (letter.Fields[i].StartsWith("referral."))
{
command += "MAX(referral." + letter.Fields[i].Substring(9) + ") " + letter.Fields[i].Substring(9);
}
else
{
command += "MAX(patient." + letter.Fields[i] + ") " + letter.Fields[i];
}
}
command += " FROM patient "
+ "LEFT JOIN refattach ON patient.PatNum=refattach.PatNum AND refattach.IsFrom=1 "
+ "LEFT JOIN referral ON refattach.ReferralNum=referral.ReferralNum "
+ "LEFT JOIN plannedappt ON plannedappt.PatNum=patient.PatNum AND plannedappt.ItemOrder=1 "
+ "LEFT JOIN site ON patient.SiteNum=site.SiteNum "
+ "LEFT JOIN treatplan ON patient.PatNum=treatplan.PatNum AND DateTP=@maxTpDate "
+ "LEFT JOIN patient patResp ON treatplan.ResponsParty=patResp.PatNum "
+ "LEFT JOIN recall ON recall.PatNum=patient.PatNum "
+ "AND (recall.RecallTypeNum=" + POut.Long(PrefC.GetLong(PrefName.RecallTypeSpecialProphy))
+ " OR recall.RecallTypeNum=" + POut.Long(PrefC.GetLong(PrefName.RecallTypeSpecialPerio)) + ") "
+ "LEFT JOIN patplan ON patplan.PatNum=patient.PatNum AND Ordinal=1 "
+ "LEFT JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum "
+ "LEFT JOIN insplan ON inssub.PlanNum=insplan.PlanNum "
+ "LEFT JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum "
+ "LEFT JOIN patient patSubsc ON patSubsc.PatNum=inssub.Subscriber "
+ "LEFT JOIN appointment ON appointment.PatNum=patient.PatNum "
+ "AND AptStatus=" + POut.Long((int)ApptStatus.Scheduled) + " "
+ "AND AptDateTime > " + DbHelper.Now() + " "
+ "LEFT JOIN patient patGuar ON patGuar.PatNum=patient.Guarantor "
+ "WHERE patient.PatNum=" + POut.Long(PatCur.PatNum)
+ " GROUP BY patient.PatNum "
+ "ORDER BY refattach.ItemOrder";
return(Db.GetTable(command));
}
