internal static void Insert(EmailAttach attach){ if(PrefB.RandomKeys) { attach.EmailAttachNum=MiscDataB.GetKey("emailattach","EmailAttachNum"); } string command= "INSERT INTO emailattach ("; if(PrefB.RandomKeys) { command+="EmailAttachNum,"; } command+="EmailMessageNum, DisplayedFileName, ActualFileName) VALUES("; if(PrefB.RandomKeys) { command+="'"+POut.PInt(attach.EmailAttachNum)+"', "; } command+= "'"+POut.PInt (attach.EmailMessageNum)+"', " +"'"+POut.PString(attach.DisplayedFileName)+"', " +"'"+POut.PString(attach.ActualFileName)+"')"; //MessageBox.Show(cmd.CommandText); DataConnection dcon=new DataConnection(); dcon.NonQ(command); }
///<summary></summary> public static int Update(Document doc) { string command = "UPDATE document SET " + "Description = '" + POut.PString(doc.Description) + "'" + ",DateCreated = " + POut.PDate(doc.DateCreated) + ",DocCategory = '" + POut.PInt(doc.DocCategory) + "'" + ",WithPat = '" + POut.PInt(doc.WithPat) + "'" + ",FileName = '" + POut.PString(doc.FileName) + "'" + ",ImgType = '" + POut.PInt((int)doc.ImgType) + "'" + ",IsFlipped = '" + POut.PBool(doc.IsFlipped) + "'" + ",DegreesRotated = '" + POut.PInt(doc.DegreesRotated) + "'" + ",ToothNumbers = '" + POut.PString(doc.ToothNumbers) + "'" + ",Note = '" + POut.PString(doc.Note) + "'" + ",SigIsTopaz = '" + POut.PBool(doc.SigIsTopaz) + "'" + ",Signature = '" + POut.PString(doc.Signature) + "'" + " WHERE DocNum = '" + POut.PInt(doc.DocNum) + "'"; //MessageBox.Show(cmd.CommandText); DataConnection dcon = new DataConnection(); dcon.NonQ(command); return(0); }
///<summary>Returns the documents which correspond to the given mountitems.</summary> public static Document[] GetDocumentsForMountItems(MountItem[] mountItems) { if (mountItems == null || mountItems.Length < 1) { return(new Document[0]); } Document[] documents = new Document[mountItems.Length]; for (int i = 0; i < mountItems.Length; i++) { string command = "SELECT * FROM document WHERE MountItemNum='" + POut.PInt(mountItems[i].MountItemNum) + "'"; DataTable table = General2.GetTable(command); if (table.Rows.Count < 1) { documents[i] = null; } else { documents[i] = Fill(table)[0]; } } return(documents); }
internal static void Insert(ProcNote procNote) { if (PrefB.RandomKeys) { procNote.ProcNoteNum = MiscDataB.GetKey("procnote", "ProcNoteNum"); } string command = "INSERT INTO procnote ("; if (PrefB.RandomKeys) { command += "ProcNoteNum,"; } command += "PatNum, ProcNum, EntryDateTime, UserNum, Note, SigIsTopaz, Signature) VALUES("; if (PrefB.RandomKeys) { command += "'" + POut.PInt(procNote.ProcNoteNum) + "', "; } command += "'" + POut.PInt(procNote.PatNum) + "', " + "'" + POut.PInt(procNote.ProcNum) + "', "; if (DataConnection.DBtype == DatabaseType.Oracle) { command += POut.PDateT(MiscDataB.GetNowDateTime()); } else //Assume MySQL { command += "NOW()"; } command += ", " //EntryDateTime + "'" + POut.PInt(procNote.UserNum) + "', " + "'" + POut.PString(procNote.Note) + "', " + "'" + POut.PBool(procNote.SigIsTopaz) + "', " + "'" + POut.Base64(procNote.Signature) + "')"; //MessageBox.Show(cmd.CommandText); DataConnection dcon = new DataConnection(); dcon.NonQ(command); //Debug.WriteLine("Sig length: "+procNote.Signature.Length.ToString()); }
///<summary></summary> public static DataSet Refresh(int patNum) { string command = "SELECT * FROM procedurelog WHERE PatNum=" + POut.PInt(patNum); //if(!includeDeletedAndNotes){ command += " AND ProcStatus !=6"; //don't include deleted //} command += " ORDER BY ProcDate"; //,OldCode //notes: /* +";SELECT * FROM procnote WHERE PatNum="+POut.PInt(patNum) +" ORDER BY EntryDateTime";*/ DataConnection dcon = new DataConnection(); DataSet ds = dcon.GetDs(command); //add a note column to the proc table. //ds.Tables[0].Columns.Add("UserNum"); //ds.Tables[0].Columns.Add("Note");//,,typeof(string)); //ds.Tables[0].Columns.Add("SigIsTopaz");//,typeof(string)); //ds.Tables[0].Columns.Add("Signature");//,typeof(string)); /* * for(int i=0;i<ds.Tables[0].Rows.Count;i++){//loop through each proc * for(int n=ds.Tables[1].Rows.Count-1;n>=0;n--){//loop through each note, backwards. * if(ds.Tables[0].Rows[i]["ProcNum"].ToString() != ds.Tables[1].Rows[n]["ProcNum"].ToString()) { * continue; * } * //userNum=PIn.PInt(ds.Tables[1].Rows[n]["UserNum"].ToString()); * ds.Tables[0].Rows[i]["UserNum"] =ds.Tables[1].Rows[n]["UserNum"].ToString(); * ds.Tables[0].Rows[i]["Note"] =ds.Tables[1].Rows[n]["Note"].ToString(); * ds.Tables[0].Rows[i]["SigIsTopaz"]=ds.Tables[1].Rows[n]["SigIsTopaz"].ToString(); * ds.Tables[0].Rows[i]["Signature"] =ds.Tables[1].Rows[n]["Signature"].ToString(); * break;//out of note loop. * } * }*/ //ds.Tables.RemoveAt(1); return(ds); }
///<summary></summary> public static int Update(EmailMessage message) { string command = "UPDATE emailmessage SET " + "PatNum = '" + POut.PInt(message.PatNum) + "' " + ",ToAddress = '" + POut.PString(message.ToAddress) + "' " + ",FromAddress = '" + POut.PString(message.FromAddress) + "' " + ",Subject = '" + POut.PString(message.Subject) + "' " + ",BodyText = '" + POut.PString(message.BodyText) + "' " + ",MsgDateTime = " + POut.PDateT(message.MsgDateTime) + " " + ",SentOrReceived = '" + POut.PInt((int)message.SentOrReceived) + "' " + "WHERE EmailMessageNum = " + POut.PInt(message.EmailMessageNum); DataConnection dcon = new DataConnection(); dcon.NonQ(command); //now, delete all attachments and recreate. command = "DELETE FROM emailattach WHERE EmailMessageNum=" + POut.PInt(message.EmailMessageNum); dcon.NonQ(command); for (int i = 0; i < message.Attachments.Count; i++) { message.Attachments[i].EmailMessageNum = message.EmailMessageNum; EmailAttachB.Insert(message.Attachments[i]); } return(0); }
private static DataTable GetCommLog(int patNum) { DataConnection dcon = new DataConnection(); DataTable table = new DataTable("Commlog"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("CommDateTime", typeof(DateTime)); table.Columns.Add("commDate"); table.Columns.Add("commTime"); table.Columns.Add("CommlogNum"); table.Columns.Add("commType"); table.Columns.Add("EmailMessageNum"); table.Columns.Add("FormPatNum"); table.Columns.Add("mode"); table.Columns.Add("Note"); table.Columns.Add("patName"); //table.Columns.Add("sentOrReceived"); //table.Columns.Add(""); //but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now. List <DataRow> rows = new List <DataRow>(); //Commlog------------------------------------------------------------------------------------------ string command = "SELECT CommDateTime,CommType,Mode_,SentOrReceived,Note,CommlogNum,IsStatementSent,p1.FName,commlog.PatNum " + "FROM commlog,patient p1,patient p2 " + "WHERE commlog.PatNum=p1.PatNum " + "AND p1.Guarantor=p2.Guarantor " + "AND p2.PatNum =" + POut.PInt(patNum) + " ORDER BY CommDateTime"; DataTable rawComm = dcon.GetTable(command); DateTime dateT; for (int i = 0; i < rawComm.Rows.Count; i++) { if (rawComm.Rows[i]["IsStatementSent"].ToString() == "1") { continue; } row = table.NewRow(); dateT = PIn.PDateT(rawComm.Rows[i]["CommDateTime"].ToString()); row["CommDateTime"] = dateT; row["commDate"] = dateT.ToShortDateString(); if (dateT.TimeOfDay != TimeSpan.Zero) { row["commTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } row["CommlogNum"] = rawComm.Rows[i]["CommlogNum"].ToString(); row["commType"] = DefB.GetName(DefCat.CommLogTypes, PIn.PInt(rawComm.Rows[i]["CommType"].ToString())); row["EmailMessageNum"] = "0"; row["FormPatNum"] = "0"; if (rawComm.Rows[i]["Mode_"].ToString() != "0") //anything except none { row["mode"] = Lan.g("enumCommItemMode", ((CommItemMode)PIn.PInt(rawComm.Rows[i]["Mode_"].ToString())).ToString()); } row["Note"] = rawComm.Rows[i]["Note"].ToString(); if (rawComm.Rows[i]["PatNum"].ToString() != patNum.ToString()) { row["patName"] = rawComm.Rows[i]["FName"].ToString(); } //row["sentOrReceived"]=Lan.g("enumCommSentOrReceived", // ((CommSentOrReceived)PIn.PInt(rawComm.Rows[i]["SentOrReceived"].ToString())).ToString()); rows.Add(row); } //emailmessage--------------------------------------------------------------------------------------- command = "SELECT MsgDateTime,SentOrReceived,Subject,EmailMessageNum " + "FROM emailmessage WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY MsgDateTime"; DataTable rawEmail = dcon.GetTable(command); string txt; for (int i = 0; i < rawEmail.Rows.Count; i++) { row = table.NewRow(); dateT = PIn.PDateT(rawEmail.Rows[i]["MsgDateTime"].ToString()); row["CommDateTime"] = dateT; row["commDate"] = dateT.ToShortDateString(); if (dateT.TimeOfDay != TimeSpan.Zero) { row["commTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } row["CommlogNum"] = "0"; //type row["EmailMessageNum"] = rawEmail.Rows[i]["EmailMessageNum"].ToString(); row["FormPatNum"] = "0"; row["mode"] = Lan.g("enumCommItemMode", CommItemMode.Email.ToString()); txt = ""; if (rawEmail.Rows[i]["SentOrReceived"].ToString() == "0") { txt = "(" + Lan.g("AccountModule", "Unsent") + ") "; } row["Note"] = txt + rawEmail.Rows[i]["Subject"].ToString(); //if(rawEmail.Rows[i]["SentOrReceived"].ToString()=="0") { // row["sentOrReceived"]=Lan.g("AccountModule","Unsent"); //} //else { // row["sentOrReceived"]=Lan.g("enumCommSentOrReceived", // ((CommSentOrReceived)PIn.PInt(rawEmail.Rows[i]["SentOrReceived"].ToString())).ToString()); //} rows.Add(row); } //formpat--------------------------------------------------------------------------------------- command = "SELECT FormDateTime,FormPatNum " + "FROM formpat WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY FormDateTime"; DataTable rawForm = dcon.GetTable(command); for (int i = 0; i < rawForm.Rows.Count; i++) { row = table.NewRow(); dateT = PIn.PDateT(rawForm.Rows[i]["FormDateTime"].ToString()); row["CommDateTime"] = dateT; row["commDate"] = dateT.ToShortDateString(); if (dateT.TimeOfDay != TimeSpan.Zero) { row["commTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } row["CommlogNum"] = "0"; row["commType"] = Lan.g("AccountModule", "Questionnaire"); row["EmailMessageNum"] = "0"; row["FormPatNum"] = rawForm.Rows[i]["FormPatNum"].ToString(); row["mode"] = ""; row["Note"] = ""; //row["sentOrReceived"]=""; rows.Add(row); } //Sorting //rows.Sort(CompareCommRows); for (int i = 0; i < rows.Count; i++) { table.Rows.Add(rows[i]); } DataView view = table.DefaultView; view.Sort = "CommDateTime"; table = view.ToTable(); return(table); }
public static bool GetPatPict(int patNum, string patFolder, out Bitmap patientPict) { patientPict = null; //first establish which category pat pics are in int defNumPicts = 0; for (int i = 0; i < DefB.Short[(int)DefCat.ImageCats].Length; i++) { if (DefB.Short[(int)DefCat.ImageCats][i].ItemValue == "P" || DefB.Short[(int)DefCat.ImageCats][i].ItemValue == "XP") { defNumPicts = DefB.Short[(int)DefCat.ImageCats][i].DefNum; break; } } if (defNumPicts == 0) //no category set for picts { return(false); } //then find string command = "SELECT document.* FROM document,docattach " + "WHERE document.DocNum=docattach.DocNum " + "AND docattach.PatNum=" + POut.PInt(patNum) + " AND document.DocCategory=" + POut.PInt(defNumPicts) + " ORDER BY DateCreated DESC "; //gets the most recent if (DataSettings.DbType == DatabaseType.Oracle) { command = "SELECT * FROM (" + command + ") WHERE ROWNUM<=1"; } else //Assume MySQL { command += "LIMIT 1"; } Document[] pictureDocs = RefreshAndFill(command); if (pictureDocs == null || pictureDocs.Length < 1) //no pictures { return(false); } string shortFileName = pictureDocs[0].FileName; if (shortFileName.Length < 1) { return(false); } string fullName = ODFileUtils.CombinePaths(patFolder, shortFileName); if (!File.Exists(fullName)) { return(false); } //create Thumbnails folder string thumbPath = ODFileUtils.CombinePaths(patFolder, "Thumbnails"); if (!Directory.Exists(thumbPath)) { try { Directory.CreateDirectory(thumbPath); } catch { throw new ImageStoreCreationException(Lan.g("Documents", "Error. Could not create thumbnails folder. ")); } } string thumbFileName = ODFileUtils.CombinePaths(new string[] { patFolder, "Thumbnails", shortFileName }); if (!ImageHelper.HasImageExtension(thumbFileName)) { return(false); } if (File.Exists(thumbFileName)) //use existing thumbnail { patientPict = (Bitmap)Bitmap.FromFile(thumbFileName); return(true); } //add thumbnail Bitmap thumbBitmap; //Gets the cropped/flipped/rotated image with any color filtering applied. Bitmap sourceImage = new Bitmap(fullName); Bitmap fullImage = ImageHelper.ApplyDocumentSettingsToImage(pictureDocs[0], sourceImage, ApplySettings.ALL); sourceImage.Dispose(); thumbBitmap = ImageHelper.GetThumbnail(fullImage, 100); fullImage.Dispose(); try { thumbBitmap.Save(thumbFileName); } catch { //Oh well, we can regenerate it next time if we have to! } patientPict = thumbBitmap; return(true); }
///<summary></summary> public static Document[] GetAllWithPat(int patNum) { string command = "SELECT * FROM document WHERE PatNum='" + POut.PInt(patNum) + "'"; return(RefreshAndFill(command)); }
///<summary>This does not actually delete the procedure, but just changes the status to deleted. Throws exception if not allowed to delete.</summary> public static int Delete(int procNum) { //Test to see if any payment at all has been received for this proc string command = "SELECT COUNT(*) FROM claimproc WHERE ProcNum=" + POut.PInt(procNum) + " AND InsPayAmt > 0 AND Status != " + POut.PInt((int)ClaimProcStatus.Preauth); DataConnection dcon = new DataConnection(); if (dcon.GetCount(command) != "0") { throw new Exception(Lan.g("Procedures", "Not allowed to delete a procedure that is attached to a payment.")); } //delete adjustments command = "DELETE FROM adjustment WHERE ProcNum='" + POut.PInt(procNum) + "'"; dcon.NonQ(command); //delete claimprocs command = "DELETE from claimproc WHERE ProcNum = '" + POut.PInt(procNum) + "'"; dcon.NonQ(command); //resynch appointment description------------------------------------------------------------------------------------- command = "SELECT AptNum,PlannedAptNum FROM procedurelog WHERE ProcNum = " + POut.PInt(procNum); DataTable table = dcon.GetTable(command); string aptnum = table.Rows[0][0].ToString(); string plannedaptnum = table.Rows[0][1].ToString(); string procdescript; if (aptnum != "0") { command = @"SELECT AbbrDesc FROM procedurecode,procedurelog WHERE procedurecode.CodeNum=procedurelog.CodeNum AND ProcNum != " + POut.PInt(procNum) + " AND procedurelog.AptNum=" + aptnum; table = dcon.GetTable(command); procdescript = ""; for (int i = 0; i < table.Rows.Count; i++) { if (i > 0) { procdescript += ", "; } procdescript += table.Rows[i]["AbbrDesc"].ToString(); } command = "UPDATE appointment SET ProcDescript='" + POut.PString(procdescript) + "' " + "WHERE AptNum=" + aptnum; dcon.NonQ(command); } if (plannedaptnum != "0") { command = @"SELECT AbbrDesc FROM procedurecode,procedurelog WHERE procedurecode.CodeNum=procedurelog.CodeNum AND ProcNum != " + POut.PInt(procNum) + " AND procedurelog.PlannedAptNum=" + plannedaptnum; table = dcon.GetTable(command); procdescript = ""; for (int i = 0; i < table.Rows.Count; i++) { if (i > 0) { procdescript += ", "; } procdescript += table.Rows[i]["AbbrDesc"].ToString(); } command = "UPDATE appointment SET ProcDescript='" + POut.PString(procdescript) + "' " + "WHERE NextAptNum=" + plannedaptnum; dcon.NonQ(command); } //set the procedure deleted----------------------------------------------------------------------------------------- command = "UPDATE procedurelog SET ProcStatus = " + POut.PInt((int)ProcStat.D) + ", " + "AptNum=0, " + "PlannedAptNum=0 " + "WHERE ProcNum = '" + POut.PInt(procNum) + "'"; int rowsChanged = dcon.NonQ(command); //Recalls.Synch(ProcCur.PatNum);//later return(rowsChanged); }
public static int Insert(Procedure proc) { if (PrefB.RandomKeys) { proc.ProcNum = MiscDataB.GetKey("procedurelog", "ProcNum"); } string command = "INSERT INTO procedurelog ("; if (PrefB.RandomKeys) { command += "ProcNum,"; } command += "PatNum, AptNum, OldCode, ProcDate,ProcFee,Surf," + "ToothNum,ToothRange,Priority,ProcStatus,ProvNum," + "Dx,PlannedAptNum,PlaceService,Prosthesis,DateOriginalProsth,ClaimNote," + "DateEntryC,ClinicNum,MedicalCode,DiagnosticCode,IsPrincDiag,ProcNumLab," + "BillingTypeOne,BillingTypeTwo,CodeNum,CodeMod1,CodeMod2,CodeMod3,CodeMod4,RevCode,UnitCode,UnitQty,BaseUnits,StartTime,StopTime) VALUES("; if (PrefB.RandomKeys) { command += "'" + POut.PInt(proc.ProcNum) + "', "; } command += "'" + POut.PInt(proc.PatNum) + "', " + "'" + POut.PInt(proc.AptNum) + "', " + "'" + POut.PString(proc.OldCode) + "', " + POut.PDate(proc.ProcDate) + ", " + "'" + POut.PDouble(proc.ProcFee) + "', " + "'" + POut.PString(proc.Surf) + "', " + "'" + POut.PString(proc.ToothNum) + "', " + "'" + POut.PString(proc.ToothRange) + "', " + "'" + POut.PInt(proc.Priority) + "', " + "'" + POut.PInt((int)proc.ProcStatus) + "', " + "'" + POut.PInt(proc.ProvNum) + "', " + "'" + POut.PInt(proc.Dx) + "', " + "'" + POut.PInt(proc.PlannedAptNum) + "', " + "'" + POut.PInt((int)proc.PlaceService) + "', " + "'" + POut.PString(proc.Prosthesis) + "', " + POut.PDate(proc.DateOriginalProsth) + ", " + "'" + POut.PString(proc.ClaimNote) + "', "; if (DataConnection.DBtype == DatabaseType.Oracle) { command += POut.PDateT(MiscDataB.GetNowDateTime()); } else //Assume MySQL { command += "NOW()"; } command += ", " //DateEntryC + "'" + POut.PInt(proc.ClinicNum) + "', " + "'" + POut.PString(proc.MedicalCode) + "', " + "'" + POut.PString(proc.DiagnosticCode) + "', " + "'" + POut.PBool(proc.IsPrincDiag) + "', " + "'" + POut.PInt(proc.ProcNumLab) + "', " + "'" + POut.PInt(proc.BillingTypeOne) + "', " + "'" + POut.PInt(proc.BillingTypeTwo) + "', " + "'" + POut.PInt(proc.CodeNum) + "', " + "'" + POut.PString(proc.CodeMod1) + "', " + "'" + POut.PString(proc.CodeMod2) + "', " + "'" + POut.PString(proc.CodeMod3) + "', " + "'" + POut.PString(proc.CodeMod4) + "', " + "'" + POut.PString(proc.RevCode) + "', " + "'" + POut.PString(proc.UnitCode) + "', " + "'" + POut.PInt(proc.UnitQty) + "', " + "'" + POut.PInt(proc.BaseUnits) + "', " + "'" + POut.PInt(proc.StartTime) + "', " + "'" + POut.PInt(proc.StopTime) + "')"; //MessageBox.Show(cmd.CommandText); DataConnection dcon = new DataConnection(); if (PrefB.RandomKeys) { dcon.NonQ(command); } else { dcon.NonQ(command, true); proc.ProcNum = dcon.InsertID; } if (proc.Note != "") { ProcNote note = new ProcNote(); note.PatNum = proc.PatNum; note.ProcNum = proc.ProcNum; note.UserNum = proc.UserNum; note.Note = proc.Note; ProcNoteB.Insert(note); } return(proc.ProcNum); }
///<summary>Updates only the changed columns.</summary> public static int Update(Procedure proc, Procedure oldProc) { bool comma = false; string c = "UPDATE procedurelog SET "; if (proc.PatNum != oldProc.PatNum) { c += "PatNum = '" + POut.PInt(proc.PatNum) + "'"; comma = true; } if (proc.AptNum != oldProc.AptNum) { if (comma) { c += ","; } c += "AptNum = '" + POut.PInt(proc.AptNum) + "'"; comma = true; } if (proc.OldCode != oldProc.OldCode) { if (comma) { c += ","; } c += "OldCode = '" + POut.PString(proc.OldCode) + "'"; comma = true; } if (proc.ProcDate != oldProc.ProcDate) { if (comma) { c += ","; } c += "ProcDate = " + POut.PDate(proc.ProcDate); comma = true; } if (proc.ProcFee != oldProc.ProcFee) { if (comma) { c += ","; } c += "ProcFee = '" + POut.PDouble(proc.ProcFee) + "'"; comma = true; } if (proc.Surf != oldProc.Surf) { if (comma) { c += ","; } c += "Surf = '" + POut.PString(proc.Surf) + "'"; comma = true; } if (proc.ToothNum != oldProc.ToothNum) { if (comma) { c += ","; } c += "ToothNum = '" + POut.PString(proc.ToothNum) + "'"; comma = true; } if (proc.ToothRange != oldProc.ToothRange) { if (comma) { c += ","; } c += "ToothRange = '" + POut.PString(proc.ToothRange) + "'"; comma = true; } if (proc.Priority != oldProc.Priority) { if (comma) { c += ","; } c += "Priority = '" + POut.PInt(proc.Priority) + "'"; comma = true; } if (proc.ProcStatus != oldProc.ProcStatus) { if (comma) { c += ","; } c += "ProcStatus = '" + POut.PInt((int)proc.ProcStatus) + "'"; comma = true; } if (proc.ProvNum != oldProc.ProvNum) { if (comma) { c += ","; } c += "ProvNum = '" + POut.PInt(proc.ProvNum) + "'"; comma = true; } if (proc.Dx != oldProc.Dx) { if (comma) { c += ","; } c += "Dx = '" + POut.PInt(proc.Dx) + "'"; comma = true; } if (proc.PlannedAptNum != oldProc.PlannedAptNum) { if (comma) { c += ","; } c += "PlannedAptNum = '" + POut.PInt(proc.PlannedAptNum) + "'"; comma = true; } if (proc.PlaceService != oldProc.PlaceService) { if (comma) { c += ","; } c += "PlaceService = '" + POut.PInt((int)proc.PlaceService) + "'"; comma = true; } if (proc.Prosthesis != oldProc.Prosthesis) { if (comma) { c += ","; } c += "Prosthesis = '" + POut.PString(proc.Prosthesis) + "'"; comma = true; } if (proc.DateOriginalProsth != oldProc.DateOriginalProsth) { if (comma) { c += ","; } c += "DateOriginalProsth = " + POut.PDate(proc.DateOriginalProsth); comma = true; } if (proc.ClaimNote != oldProc.ClaimNote) { if (comma) { c += ","; } c += "ClaimNote = '" + POut.PString(proc.ClaimNote) + "'"; comma = true; } if (proc.DateEntryC != oldProc.DateEntryC) { if (comma) { c += ","; } c += "DateEntryC = "; if (DataConnection.DBtype == DatabaseType.Oracle) { c += POut.PDateT(MiscDataB.GetNowDateTime()); } else //Assume MySQL { c += "NOW()"; } comma = true; } if (proc.ClinicNum != oldProc.ClinicNum) { if (comma) { c += ","; } c += "ClinicNum = '" + POut.PInt(proc.ClinicNum) + "'"; comma = true; } if (proc.MedicalCode != oldProc.MedicalCode) { if (comma) { c += ","; } c += "MedicalCode = '" + POut.PString(proc.MedicalCode) + "'"; comma = true; } if (proc.DiagnosticCode != oldProc.DiagnosticCode) { if (comma) { c += ","; } c += "DiagnosticCode = '" + POut.PString(proc.DiagnosticCode) + "'"; comma = true; } if (proc.IsPrincDiag != oldProc.IsPrincDiag) { if (comma) { c += ","; } c += "IsPrincDiag = '" + POut.PBool(proc.IsPrincDiag) + "'"; comma = true; } if (proc.ProcNumLab != oldProc.ProcNumLab) { if (comma) { c += ","; } c += "ProcNumLab = '" + POut.PInt(proc.ProcNumLab) + "'"; comma = true; } if (proc.BillingTypeOne != oldProc.BillingTypeOne) { if (comma) { c += ","; } c += "BillingTypeOne = '" + POut.PInt(proc.BillingTypeOne) + "'"; comma = true; } if (proc.BillingTypeTwo != oldProc.BillingTypeTwo) { if (comma) { c += ","; } c += "BillingTypeTwo = '" + POut.PInt(proc.BillingTypeTwo) + "'"; comma = true; } if (proc.CodeNum != oldProc.CodeNum) { if (comma) { c += ","; } c += "CodeNum = '" + POut.PInt(proc.CodeNum) + "'"; comma = true; } if (proc.CodeMod1 != oldProc.CodeMod1) { if (comma) { c += ","; } c += "CodeMod1 = '" + POut.PString(proc.CodeMod1) + "'"; comma = true; } if (proc.CodeMod2 != oldProc.CodeMod2) { if (comma) { c += ","; } c += "CodeMod2 = '" + POut.PString(proc.CodeMod2) + "'"; comma = true; } if (proc.CodeMod3 != oldProc.CodeMod3) { if (comma) { c += ","; } c += "CodeMod3 = '" + POut.PString(proc.CodeMod3) + "'"; comma = true; } if (proc.CodeMod4 != oldProc.CodeMod4) { if (comma) { c += ","; } c += "CodeMod4 = '" + POut.PString(proc.CodeMod4) + "'"; comma = true; } if (proc.RevCode != oldProc.RevCode) { if (comma) { c += ","; } c += "RevCode = '" + POut.PString(proc.RevCode) + "'"; comma = true; } if (proc.UnitCode != oldProc.UnitCode) { if (comma) { c += ","; } c += "UnitCode = '" + POut.PString(proc.UnitCode) + "'"; comma = true; } if (proc.UnitQty != oldProc.UnitQty) { if (comma) { c += ","; } c += "UnitQty = '" + POut.PInt(proc.UnitQty) + "'"; comma = true; } if (proc.BaseUnits != oldProc.BaseUnits) { if (comma) { c += ","; } c += "BaseUnits = '" + POut.PInt(proc.BaseUnits) + "'"; comma = true; } if (proc.StartTime != oldProc.StartTime) { if (comma) { c += ","; } c += "StartTime = '" + POut.PInt(proc.StartTime) + "'"; comma = true; } if (proc.StopTime != oldProc.StopTime) { if (comma) { c += ","; } c += "StopTime = '" + POut.PInt(proc.StopTime) + "'"; comma = true; } int rowsChanged = 0; if (comma) { c += " WHERE ProcNum = '" + POut.PInt(proc.ProcNum) + "'"; DataConnection dcon = new DataConnection(); rowsChanged = dcon.NonQ(c); } else { //rowsChanged=0;//this means no change is actually required. } if (proc.Note != oldProc.Note || proc.UserNum != oldProc.UserNum || proc.SigIsTopaz != oldProc.SigIsTopaz || proc.Signature != oldProc.Signature) { ProcNote note = new ProcNote(); note.PatNum = proc.PatNum; note.ProcNum = proc.ProcNum; note.UserNum = proc.UserNum; note.Note = proc.Note; note.SigIsTopaz = proc.SigIsTopaz; note.Signature = proc.Signature; ProcNoteB.Insert(note); } return(rowsChanged); }
public static DataTable GetProgNotes(int patNum, bool isAuditMode) { DataConnection dcon = new DataConnection(); DataTable table = new DataTable("ProgNotes"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("aptDateTime", typeof(DateTime)); table.Columns.Add("AptNum"); table.Columns.Add("CodeNum"); table.Columns.Add("colorBackG"); table.Columns.Add("colorText"); table.Columns.Add("CommlogNum"); table.Columns.Add("description"); table.Columns.Add("dx"); table.Columns.Add("Dx"); table.Columns.Add("LabCaseNum"); table.Columns.Add("note"); table.Columns.Add("PatNum"); //only used for Commlog table.Columns.Add("Priority"); table.Columns.Add("ProcCode"); table.Columns.Add("procDate"); table.Columns.Add("ProcDate", typeof(DateTime)); table.Columns.Add("procFee"); table.Columns.Add("ProcNum"); table.Columns.Add("ProcNumLab"); table.Columns.Add("procStatus"); table.Columns.Add("ProcStatus"); table.Columns.Add("procTime"); table.Columns.Add("prov"); table.Columns.Add("RxNum"); table.Columns.Add("signature"); table.Columns.Add("Surf"); table.Columns.Add("toothNum"); table.Columns.Add("ToothNum"); table.Columns.Add("ToothRange"); table.Columns.Add("user"); //table.Columns.Add(""); //but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now. List <DataRow> rows = new List <DataRow>(); //Procedures----------------------------------------------------------------------------------------------------- string command = "SELECT LaymanTerm,ProcDate,ProcStatus,ToothNum,Surf,Dx,UnitQty,procedurelog.BaseUnits," + "procedurecode.ProcCode,ProcNum,procedurecode.Descript," + "provider.Abbr,ProcFee,ProcNumLab,appointment.AptDateTime,Priority,ToothRange,procedurelog.CodeNum " + "FROM procedurelog " + "LEFT JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum " + "LEFT JOIN provider ON provider.ProvNum=procedurelog.ProvNum " + "LEFT JOIN appointment ON appointment.AptNum=procedurelog.AptNum " + "AND (appointment.AptStatus=" + POut.PInt((int)ApptStatus.Scheduled) + " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.ASAP) + " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.Broken) + " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.Complete) + ") WHERE procedurelog.PatNum=" + POut.PInt(patNum); if (!isAuditMode) { command += " AND ProcStatus !=6"; //don't include deleted } command += " ORDER BY ProcDate"; //we'll just have to reorder it anyway DataTable rawProcs = dcon.GetTable(command); command = "SELECT ProcNum,EntryDateTime,UserNum,Note," + "CASE WHEN Signature!='' THEN 1 ELSE 0 END AS SigPresent " + "FROM procnote WHERE PatNum=" + POut.PInt(patNum) + " ORDER BY EntryDateTime"; // but this helps when looping for notes DataTable rawNotes = dcon.GetTable(command); DateTime dateT; List <DataRow> labRows = new List <DataRow>(); //Canadian lab procs, which must be added in a loop at the very end. for (int i = 0; i < rawProcs.Rows.Count; i++) { row = table.NewRow(); row["aptDateTime"] = PIn.PDateT(rawProcs.Rows[i]["AptDateTime"].ToString()); row["AptNum"] = 0; row["CodeNum"] = rawProcs.Rows[i]["CodeNum"].ToString(); row["colorBackG"] = Color.White.ToArgb(); if (((DateTime)row["aptDateTime"]).Date == DateTime.Today) { row["colorBackG"] = DefB.Long[(int)DefCat.MiscColors][6].ItemColor.ToArgb().ToString(); } switch ((ProcStat)PIn.PInt(rawProcs.Rows[i]["ProcStatus"].ToString())) { case ProcStat.TP: row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][0].ItemColor.ToArgb().ToString(); break; case ProcStat.C: row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][1].ItemColor.ToArgb().ToString(); break; case ProcStat.EC: row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][2].ItemColor.ToArgb().ToString(); break; case ProcStat.EO: row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][3].ItemColor.ToArgb().ToString(); break; case ProcStat.R: row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][4].ItemColor.ToArgb().ToString(); break; case ProcStat.D: row["colorText"] = Color.Black.ToArgb().ToString(); break; } row["CommlogNum"] = 0; if (rawProcs.Rows[i]["LaymanTerm"].ToString() == "") { row["description"] = rawProcs.Rows[i]["Descript"].ToString(); } else { row["description"] = rawProcs.Rows[i]["LaymanTerm"].ToString(); } row["dx"] = DefB.GetValue(DefCat.Diagnosis, PIn.PInt(rawProcs.Rows[i]["Dx"].ToString())); row["Dx"] = rawProcs.Rows[i]["Dx"].ToString(); row["LabCaseNum"] = 0; //note----------------------------------------------------------------------------------------------------------- if (isAuditMode) //we will include all notes for each proc. We will concat and make readable. { for (int n = 0; n < rawNotes.Rows.Count; n++) //loop through each note { if (rawProcs.Rows[i]["ProcNum"].ToString() != rawNotes.Rows[n]["ProcNum"].ToString()) { continue; } if (row["Note"].ToString() != "") //if there is an existing note { row["note"] += "\r\n------------------------------------------------------\r\n"; //start a new line } row["note"] += PIn.PDateT(rawNotes.Rows[n]["EntryDateTime"].ToString()).ToString(); row["note"] += " " + UserodB.GetName(PIn.PInt(rawNotes.Rows[n]["UserNum"].ToString())); if (rawNotes.Rows[n]["SigPresent"].ToString() == "1") { row["note"] += " " + Lan.g("ChartModule", "(signed)"); } row["note"] += "\r\n" + rawNotes.Rows[n]["Note"].ToString(); } } else //we just want the most recent note { for (int n = rawNotes.Rows.Count - 1; n >= 0; n--) //loop through each note, backwards. { if (rawProcs.Rows[i]["ProcNum"].ToString() != rawNotes.Rows[n]["ProcNum"].ToString()) { continue; } row["user"] = UserodB.GetName(PIn.PInt(rawNotes.Rows[n]["UserNum"].ToString())); row["note"] = rawNotes.Rows[n]["Note"].ToString(); if (rawNotes.Rows[n]["SigPresent"].ToString() == "1") { row["signature"] = Lan.g("ChartModule", "Signed"); } else { row["signature"] = ""; } break; //out of note loop. } } row["Priority"] = rawProcs.Rows[i]["Priority"].ToString(); row["ProcCode"] = rawProcs.Rows[i]["ProcCode"].ToString(); dateT = PIn.PDateT(rawProcs.Rows[i]["ProcDate"].ToString()); if (dateT.Year < 1880) { row["procDate"] = ""; } else { row["procDate"] = dateT.ToShortDateString(); } row["ProcDate"] = dateT; double amt = PIn.PDouble(rawProcs.Rows[i]["ProcFee"].ToString()); int qty = PIn.PInt(rawProcs.Rows[i]["UnitQty"].ToString()) + PIn.PInt(rawProcs.Rows[i]["BaseUnits"].ToString()); if (qty > 0) { amt *= qty; } row["procFee"] = amt.ToString("F"); row["ProcNum"] = rawProcs.Rows[i]["ProcNum"].ToString(); row["ProcNumLab"] = rawProcs.Rows[i]["ProcNumLab"].ToString(); row["procStatus"] = Lan.g("enumProcStat", ((ProcStat)PIn.PInt(rawProcs.Rows[i]["ProcStatus"].ToString())).ToString()); row["ProcStatus"] = rawProcs.Rows[i]["ProcStatus"].ToString(); if (dateT.TimeOfDay != TimeSpan.Zero) { row["procTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } row["prov"] = rawProcs.Rows[i]["Abbr"].ToString(); row["RxNum"] = 0; row["Surf"] = rawProcs.Rows[i]["Surf"].ToString(); row["toothNum"] = Tooth.ToInternat(rawProcs.Rows[i]["ToothNum"].ToString()); row["ToothNum"] = rawProcs.Rows[i]["ToothNum"].ToString(); row["ToothRange"] = rawProcs.Rows[i]["ToothRange"].ToString(); if (rawProcs.Rows[i]["ProcNumLab"].ToString() == "0") //normal proc { rows.Add(row); } else { row["description"] = "-----" + row["description"].ToString(); labRows.Add(row); //these will be added in the loop at the end } } //Commlog----------------------------------------------------------------------------------------------------------- command = "SELECT CommlogNum,CommDateTime,CommType,Note,commlog.PatNum,p1.FName " + "FROM patient p1,patient p2,commlog " + "WHERE commlog.PatNum=p1.PatNum " + "AND p1.Guarantor=p2.Guarantor " + "AND p2.PatNum=" + POut.PInt(patNum) + " AND IsStatementSent=0 ORDER BY CommDateTime"; DataTable rawComm = dcon.GetTable(command); string txt; for (int i = 0; i < rawComm.Rows.Count; i++) { row = table.NewRow(); row["AptNum"] = 0; row["colorBackG"] = Color.White.ToArgb(); row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][6].ItemColor.ToArgb().ToString(); row["CommlogNum"] = rawComm.Rows[i]["CommlogNum"].ToString(); if (rawComm.Rows[i]["PatNum"].ToString() == patNum.ToString()) { txt = ""; } else { txt = "(" + rawComm.Rows[i]["FName"].ToString() + ") "; } row["description"] = txt + Lan.g("ChartModule", "Comm - ") + DefB.GetName(DefCat.CommLogTypes, PIn.PInt(rawComm.Rows[i]["CommType"].ToString())); row["LabCaseNum"] = 0; row["note"] = rawComm.Rows[i]["Note"].ToString(); row["PatNum"] = rawComm.Rows[i]["PatNum"].ToString(); dateT = PIn.PDateT(rawComm.Rows[i]["CommDateTime"].ToString()); if (dateT.Year < 1880) { row["procDate"] = ""; } else { row["procDate"] = dateT.ToShortDateString(); } row["ProcDate"] = dateT; if (dateT.TimeOfDay != TimeSpan.Zero) { row["procTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } row["ProcNum"] = 0; row["RxNum"] = 0; rows.Add(row); } //Rx------------------------------------------------------------------------------------------------------------------ command = "SELECT RxNum,RxDate,Drug,Disp,ProvNum,Notes FROM rxpat WHERE PatNum=" + POut.PInt(patNum) + " ORDER BY RxDate"; DataTable rawRx = dcon.GetTable(command); for (int i = 0; i < rawRx.Rows.Count; i++) { row = table.NewRow(); row["AptNum"] = 0; row["colorBackG"] = Color.White.ToArgb(); row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][5].ItemColor.ToArgb().ToString(); row["CommlogNum"] = 0; row["description"] = Lan.g("ChartModule", "Rx - ") + rawRx.Rows[i]["Drug"].ToString() + " - #" + rawRx.Rows[i]["Disp"].ToString(); row["LabCaseNum"] = 0; row["note"] = rawRx.Rows[i]["Notes"].ToString(); dateT = PIn.PDate(rawRx.Rows[i]["RxDate"].ToString()); if (dateT.Year < 1880) { row["procDate"] = ""; } else { row["procDate"] = dateT.ToShortDateString(); } row["ProcDate"] = dateT; row["ProcNum"] = 0; //row["prov"]=ProviderB. PIn.PInt(rawRx.Rows[i]["ProvNum"].ToString()); row["RxNum"] = rawRx.Rows[i]["RxNum"].ToString(); rows.Add(row); } //LabCase------------------------------------------------------------------------------------------------------------------ command = "SELECT labcase.*,Description,Phone FROM labcase,laboratory " + "WHERE labcase.LaboratoryNum=laboratory.LaboratoryNum " + "AND PatNum=" + POut.PInt(patNum) + " ORDER BY DateTimeCreated"; DataTable rawLab = dcon.GetTable(command); DateTime duedate; for (int i = 0; i < rawLab.Rows.Count; i++) { row = table.NewRow(); row["AptNum"] = 0; row["colorBackG"] = Color.White.ToArgb(); row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][7].ItemColor.ToArgb().ToString(); row["CommlogNum"] = 0; row["description"] = Lan.g("ChartModule", "LabCase - ") + rawLab.Rows[i]["Description"].ToString() + " " + rawLab.Rows[i]["Phone"].ToString(); if (PIn.PDate(rawLab.Rows[i]["DateTimeDue"].ToString()).Year > 1880) { duedate = PIn.PDateT(rawLab.Rows[i]["DateTimeDue"].ToString()); row["description"] += "\r\n" + Lan.g("ChartModule", "Due") + " " + duedate.ToString("ddd") + " " + duedate.ToShortDateString() + " " + duedate.ToShortTimeString(); } if (PIn.PDate(rawLab.Rows[i]["DateTimeChecked"].ToString()).Year > 1880) { row["description"] += "\r\n" + Lan.g("ChartModule", "Quality Checked"); } else if (PIn.PDate(rawLab.Rows[i]["DateTimeRecd"].ToString()).Year > 1880) { row["description"] += "\r\n" + Lan.g("ChartModule", "Received"); } else if (PIn.PDate(rawLab.Rows[i]["DateTimeSent"].ToString()).Year > 1880) { row["description"] += "\r\n" + Lan.g("ChartModule", "Sent"); } row["LabCaseNum"] = rawLab.Rows[i]["LabCaseNum"].ToString(); row["note"] = rawLab.Rows[i]["Instructions"].ToString(); dateT = PIn.PDateT(rawLab.Rows[i]["DateTimeCreated"].ToString()); if (dateT.Year < 1880) { row["procDate"] = ""; } else { row["procDate"] = dateT.ToShortDateString(); } if (dateT.TimeOfDay != TimeSpan.Zero) { row["procTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } row["ProcDate"] = dateT; row["ProcNum"] = 0; row["RxNum"] = 0; rows.Add(row); } //Appointments--------------------------------------------------------------------------------------------------------- command = "SELECT * FROM appointment WHERE PatNum=" + POut.PInt(patNum) + " ORDER BY AptDateTime"; //+" AND AptStatus != 6"//do not include planned appts. DataTable rawApt = dcon.GetTable(command); int apptStatus; for (int i = 0; i < rawApt.Rows.Count; i++) { row = table.NewRow(); row["AptNum"] = rawApt.Rows[i]["AptNum"].ToString(); row["colorBackG"] = Color.White.ToArgb(); dateT = PIn.PDateT(rawApt.Rows[i]["AptDateTime"].ToString()); apptStatus = PIn.PInt(rawApt.Rows[i]["AptStatus"].ToString()); row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][8].ItemColor.ToArgb().ToString(); row["CommlogNum"] = 0; row["description"] = Lan.g("ChartModule", "Appointment - ") + dateT.ToShortTimeString() + "\r\n" + rawApt.Rows[i]["ProcDescript"].ToString(); if (dateT.Date.Date == DateTime.Today.Date) { row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][9].ItemColor.ToArgb().ToString(); //deliniates nicely between old appts row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][8].ItemColor.ToArgb().ToString(); } else if (dateT.Date < DateTime.Today) { row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][11].ItemColor.ToArgb().ToString(); row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][10].ItemColor.ToArgb().ToString(); } else if (dateT.Date > DateTime.Today) { row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][13].ItemColor.ToArgb().ToString(); //at a glace, you see green...the pt is good to go as they have a future appt scheduled row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][12].ItemColor.ToArgb().ToString(); } if (apptStatus == (int)ApptStatus.Broken) { row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][14].ItemColor.ToArgb().ToString(); row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][15].ItemColor.ToArgb().ToString(); row["description"] = Lan.g("ChartModule", "BROKEN Appointment - ") + dateT.ToShortTimeString() + "\r\n" + rawApt.Rows[i]["ProcDescript"].ToString(); } else if (apptStatus == (int)ApptStatus.UnschedList) { row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][14].ItemColor.ToArgb().ToString(); row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][15].ItemColor.ToArgb().ToString(); row["description"] = Lan.g("ChartModule", "UNSCHEDULED Appointment - ") + dateT.ToShortTimeString() + "\r\n" + rawApt.Rows[i]["ProcDescript"].ToString(); } else if (apptStatus == (int)ApptStatus.Planned) { row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][16].ItemColor.ToArgb().ToString(); row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][17].ItemColor.ToArgb().ToString(); row["description"] = Lan.g("ChartModule", "PLANNED Appointment") + "\r\n" + rawApt.Rows[i]["ProcDescript"].ToString(); } else if (apptStatus == (int)ApptStatus.PtNote) { row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][18].ItemColor.ToArgb().ToString(); row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][19].ItemColor.ToArgb().ToString(); row["description"] = Lan.g("ChartModule", "*** Patient NOTE *** - ") + dateT.ToShortTimeString(); } else if (apptStatus == (int)ApptStatus.PtNoteCompleted) { row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][20].ItemColor.ToArgb().ToString(); row["colorBackG"] = DefB.Long[(int)DefCat.ProgNoteColors][21].ItemColor.ToArgb().ToString(); row["description"] = Lan.g("ChartModule", "** Complete Patient NOTE ** - ") + dateT.ToShortTimeString(); } row["LabCaseNum"] = 0; row["note"] = rawApt.Rows[i]["Note"].ToString(); if (dateT.Year < 1880) { row["procDate"] = ""; } else { row["procDate"] = dateT.ToShortDateString(); } if (dateT.TimeOfDay != TimeSpan.Zero) { row["procTime"] = dateT.ToString("h:mm") + dateT.ToString("%t").ToLower(); } row["ProcDate"] = dateT; row["ProcNum"] = 0; row["RxNum"] = 0; rows.Add(row); } //Sorting rows.Sort(CompareChartRows); //Canadian lab procedures need to come immediately after their corresponding proc--------------------------------- for (int i = 0; i < labRows.Count; i++) { for (int r = 0; r < rows.Count; r++) { if (rows[r]["ProcNum"].ToString() == labRows[i]["ProcNumLab"].ToString()) { rows.Insert(r + 1, labRows[i]); break; } } } for (int i = 0; i < rows.Count; i++) { table.Rows.Add(rows[i]); } return(table); }
///<summary>No need to surround with try/catch, because all deletions are allowed.</summary> public static void Delete(int mountItemDefNum) { string command = "DELETE FROM mountitemdef WHERE MountItemDefNum=" + POut.PInt(mountItemDefNum); General2.NonQ(command); }
public static void Delete(MountItem mountItem) { string command = "DELETE FROM mountitem WHERE MountItemNum='" + POut.PInt(mountItem.MountItemNum) + "'"; General2.NonQEx(command); }
public static DataSet GetAccount(int patNum) { string command = "SELECT CommDateTime,CommType,Mode,SentOrReceived,Note,CommlogNum " + "FROM commlog WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY CommDateTime"; DataConnection dcon = new DataConnection(); DataTable tRaw = dcon.GetTable(command); DataSet retVal = new DataSet(); DataTable tComm = new DataTable("Commlog"); tComm.Columns.Add("CommDateTime", typeof(DateTime)); tComm.Columns.Add("CommType"); //1 tComm.Columns.Add("Mode"); //2 tComm.Columns.Add("SentOrReceived"); //3 tComm.Columns.Add("Note"); //4 tComm.Columns.Add("CommlogNum"); //5 tComm.Columns.Add("EmailMessageNum"); //6 tComm.Columns.Add("FormPatNum"); //7 DataRow row; //Commlog------------------------------------------------------------------------------------------ for (int i = 0; i < tRaw.Rows.Count; i++) { if ((CommItemType)PIn.PInt(tRaw.Rows[i][1].ToString()) == CommItemType.StatementSent) { continue; } row = tComm.NewRow(); row[0] = PIn.PDateT(tRaw.Rows[i][0].ToString()); row[1] = Lan.g("enumCommItemType", ((CommItemType)PIn.PInt(tRaw.Rows[i][1].ToString())).ToString()); row[2] = Lan.g("enumCommItemMode", ((CommItemMode)PIn.PInt(tRaw.Rows[i][2].ToString())).ToString()); row[3] = Lan.g("enumCommSentOrReceived", ((CommSentOrReceived)PIn.PInt(tRaw.Rows[i][3].ToString())).ToString()); row[4] = tRaw.Rows[i][3].ToString(); row[5] = tRaw.Rows[i][4].ToString(); row[6] = "0"; row[7] = "0"; tComm.Rows.Add(row); } //emailmessage--------------------------------------------------------------------------------------- command = "SELECT MsgDateTime,SentOrReceived,Subject,EmailMessageNum " + "FROM emailmessage WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY MsgDateTime"; tRaw = dcon.GetTable(command); for (int i = 0; i < tRaw.Rows.Count; i++) { row = tComm.NewRow(); row[0] = PIn.PDateT(tRaw.Rows[i][0].ToString()); //row[1]="";//type row[2] = Lan.g("enumCommItemMode", CommItemMode.Email.ToString()); if (tRaw.Rows[i][1].ToString() == "0") { row[3] = Lan.g("ModuleAccount", "Unsent"); } else { row[3] = Lan.g("enumCommSentOrReceived", ((CommSentOrReceived)PIn.PInt(tRaw.Rows[i][1].ToString())).ToString()); } row[4] = tRaw.Rows[i][2].ToString(); //note row[5] = "0"; row[6] = tRaw.Rows[i][3].ToString(); row[7] = "0"; tComm.Rows.Add(row); } //formpat--------------------------------------------------------------------------------------- command = "SELECT FormDateTime,FormPatNum " + "FROM formpat WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY FormDateTime"; tRaw = dcon.GetTable(command); for (int i = 0; i < tRaw.Rows.Count; i++) { row = tComm.NewRow(); row[0] = PIn.PDateT(tRaw.Rows[i][0].ToString()); row[1] = Lan.g("ModuleAccount", "Form"); //type row[2] = ""; //mode row[3] = Lan.g("enumCommSentOrReceived", "Received"); row[4] = ""; //note row[5] = "0"; row[6] = "0"; row[7] = tRaw.Rows[i][1].ToString(); tComm.Rows.Add(row); } DataView view = tComm.DefaultView; view.Sort = "CommDateTime"; tComm = view.ToTable(); retVal.Tables.Add(tComm); return(retVal); }
///<summary>Inserts a new document into db, creates a filename based on Cur.DocNum, and then updates the db with this filename. Also attaches the document to the current patient.</summary> public static int Insert(Document doc, string patLF, int patNum) { if (PrefB.RandomKeys) { doc.DocNum = MiscDataB.GetKey("document", "DocNum"); } string command = "INSERT INTO document ("; if (PrefB.RandomKeys) { command += "DocNum,"; } command += "Description,DateCreated,DocCategory,WithPat,FileName,ImgType," + "IsFlipped,DegreesRotated,ToothNumbers,Note,SigIsTopaz,Signature,CropX,CropY,CropW,CropH," + "WindowingMin,WindowingMax) VALUES("; if (PrefB.RandomKeys) { command += "'" + POut.PInt(doc.DocNum) + "', "; } command += "'" + POut.PString(doc.Description) + "', " + POut.PDate(doc.DateCreated) + ", " + "'" + POut.PInt(doc.DocCategory) + "', " + "'" + POut.PInt(doc.WithPat) + "', " + "'" + POut.PString(doc.FileName) + "', " //this may simply be the extension at this point, or it may be the full filename. + "'" + POut.PInt((int)doc.ImgType) + "', " + "'" + POut.PBool(doc.IsFlipped) + "', " + "'" + POut.PInt(doc.DegreesRotated) + "', " + "'" + POut.PString(doc.ToothNumbers) + "', " + "'" + POut.PString(doc.Note) + "', " + "'" + POut.PBool(doc.SigIsTopaz) + "', " + "'" + POut.PString(doc.Signature) + "'," + "'" + POut.PInt(doc.CropX) + "'," + "'" + POut.PInt(doc.CropY) + "'," + "'" + POut.PInt(doc.CropW) + "'," + "'" + POut.PInt(doc.CropH) + "'," + "'" + POut.PInt(doc.WindowingMin) + "'," + "'" + POut.PInt(doc.WindowingMax) + "')"; /*+"'"+POut.PDate (LastAltered)+"', "//will later be used in backups +"'"+POut.PBool (IsDeleted)+"')";//ditto*/ //MessageBox.Show(cmd.CommandText); DataConnection dcon = new DataConnection(); if (PrefB.RandomKeys) { dcon.NonQ(command); } else { dcon.NonQ(command, true); doc.DocNum = dcon.InsertID; } //If the current filename is just an extension, then assign it a unique name. if (doc.FileName == Path.GetExtension(doc.FileName)) { string extension = doc.FileName; doc.FileName = ""; string s = patLF; //pat.LName+pat.FName; for (int i = 0; i < s.Length; i++) { if (Char.IsLetter(s, i)) { doc.FileName += s.Substring(i, 1); } } doc.FileName += doc.DocNum.ToString() + extension; //ensures unique name //there is still a slight chance that someone manually added a file with this name, so quick fix: command = "SELECT FileName FROM document WHERE WithPat=" + POut.PInt(doc.WithPat); DataTable table = dcon.GetTable(command); string[] usedNames = new string[table.Rows.Count]; for (int i = 0; i < table.Rows.Count; i++) { usedNames[i] = PIn.PString(table.Rows[i][0].ToString()); } while (IsFileNameInList(doc.FileName, usedNames)) { doc.FileName = "x" + doc.FileName; } /*Document[] docList=GetAllWithPat(doc.WithPat); * while(IsFileNameInList(doc.FileName,docList)) { * doc.FileName="x"+doc.FileName; * }*/ Update(doc); } DocAttach docAttach = new DocAttach(); docAttach.DocNum = doc.DocNum; docAttach.PatNum = patNum; DocAttachB.Insert(docAttach); return(doc.DocNum); }
public static void Delete(Mount mount) { string command = "DELETE FROM mount WHERE MountNum='" + POut.PInt(mount.MountNum) + "'"; General2.NonQEx(command); }
private static DataTable GetCommLog(int patNum) { DataConnection dcon = new DataConnection(); DataTable table = new DataTable("Commlog"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("CommDateTime", typeof(DateTime)); table.Columns.Add("commDate"); table.Columns.Add("CommlogNum"); table.Columns.Add("commType"); table.Columns.Add("EmailMessageNum"); table.Columns.Add("FormPatNum"); table.Columns.Add("mode"); table.Columns.Add("Note"); table.Columns.Add("sentOrReceived"); //table.Columns.Add(""); //but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now. List <DataRow> rows = new List <DataRow>(); //Commlog------------------------------------------------------------------------------------------ string command = "SELECT CommDateTime,CommType,Mode_,SentOrReceived,Note,CommlogNum " + "FROM commlog WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY CommDateTime"; DataTable rawComm = dcon.GetTable(command); DateTime dateT; for (int i = 0; i < rawComm.Rows.Count; i++) { if ((CommItemType)PIn.PInt(rawComm.Rows[i]["CommType"].ToString()) == CommItemType.StatementSent) { continue; } row = table.NewRow(); dateT = PIn.PDateT(rawComm.Rows[i]["CommDateTime"].ToString()); row["CommDateTime"] = dateT; row["commDate"] = dateT.ToShortDateString(); row["CommlogNum"] = rawComm.Rows[i]["CommlogNum"].ToString(); row["commType"] = Lan.g("enumCommItemType", ((CommItemType)PIn.PInt(rawComm.Rows[i]["CommType"].ToString())).ToString()); row["EmailMessageNum"] = "0"; row["FormPatNum"] = "0"; row["mode"] = Lan.g("enumCommItemMode", ((CommItemMode)PIn.PInt(rawComm.Rows[i]["Mode_"].ToString())).ToString()); row["Note"] = rawComm.Rows[i]["Note"].ToString(); row["sentOrReceived"] = Lan.g("enumCommSentOrReceived", ((CommSentOrReceived)PIn.PInt(rawComm.Rows[i]["SentOrReceived"].ToString())).ToString()); rows.Add(row); } //emailmessage--------------------------------------------------------------------------------------- command = "SELECT MsgDateTime,SentOrReceived,Subject,EmailMessageNum " + "FROM emailmessage WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY MsgDateTime"; DataTable rawEmail = dcon.GetTable(command); for (int i = 0; i < rawEmail.Rows.Count; i++) { row = table.NewRow(); dateT = PIn.PDateT(rawEmail.Rows[i]["MsgDateTime"].ToString()); row["CommDateTime"] = dateT; row["commDate"] = dateT.ToShortDateString(); row["CommlogNum"] = "0"; //type row["EmailMessageNum"] = rawEmail.Rows[i]["EmailMessageNum"].ToString(); row["FormPatNum"] = "0"; row["mode"] = Lan.g("enumCommItemMode", CommItemMode.Email.ToString()); row["Note"] = rawEmail.Rows[i]["Subject"].ToString(); if (rawEmail.Rows[i]["SentOrReceived"].ToString() == "0") { row["sentOrReceived"] = Lan.g("AccountModule", "Unsent"); } else { row["sentOrReceived"] = Lan.g("enumCommSentOrReceived", ((CommSentOrReceived)PIn.PInt(rawEmail.Rows[i]["SentOrReceived"].ToString())).ToString()); } rows.Add(row); } //formpat--------------------------------------------------------------------------------------- command = "SELECT FormDateTime,FormPatNum " + "FROM formpat WHERE PatNum ='" + POut.PInt(patNum) + "' ORDER BY FormDateTime"; DataTable rawForm = dcon.GetTable(command); for (int i = 0; i < rawForm.Rows.Count; i++) { row = table.NewRow(); dateT = PIn.PDateT(rawForm.Rows[i]["FormDateTime"].ToString()); row["CommDateTime"] = dateT; row["commDate"] = dateT.ToShortDateString(); row["CommlogNum"] = "0"; row["commType"] = Lan.g("AccountModule", "Questionnaire"); row["EmailMessageNum"] = "0"; row["FormPatNum"] = rawForm.Rows[i]["FormPatNum"].ToString(); row["mode"] = ""; row["Note"] = ""; row["sentOrReceived"] = ""; rows.Add(row); } //Sorting //rows.Sort(CompareCommRows); for (int i = 0; i < rows.Count; i++) { table.Rows.Add(rows[i]); } DataView view = table.DefaultView; view.Sort = "CommDateTime"; table = view.ToTable(); return(table); }
///<summary>If aptnum is specified, then the dates are ignored. If getting data for one planned appt, then pass isPlanned=true. This changes which procedures are retrieved.</summary> private static DataTable GetPeriodApptsTable(string strDateStart, string strDateEnd, string strAptNum, string strIsPlanned) { DateTime dateStart = PIn.PDate(strDateStart); DateTime dateEnd = PIn.PDate(strDateEnd); int aptNum = PIn.PInt(strAptNum); bool isPlanned = PIn.PBool(strIsPlanned); DataConnection dcon = new DataConnection(); DataTable table = new DataTable("Appointments"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("age"); table.Columns.Add("addrNote"); table.Columns.Add("apptModNote"); table.Columns.Add("aptDate"); table.Columns.Add("aptDay"); table.Columns.Add("aptLength"); table.Columns.Add("aptTime"); table.Columns.Add("AptDateTime"); table.Columns.Add("AptNum"); table.Columns.Add("AptStatus"); table.Columns.Add("Assistant"); table.Columns.Add("billingType"); table.Columns.Add("chartNumber"); table.Columns.Add("chartNumAndName"); table.Columns.Add("confirmed"); table.Columns.Add("Confirmed"); table.Columns.Add("contactMethods"); table.Columns.Add("creditIns"); table.Columns.Add("famFinUrgNote"); table.Columns.Add("hmPhone"); table.Columns.Add("ImageFolder"); table.Columns.Add("insurance"); table.Columns.Add("IsHygiene"); table.Columns.Add("lab"); table.Columns.Add("MedUrgNote"); table.Columns.Add("Note"); table.Columns.Add("Op"); table.Columns.Add("patientName"); table.Columns.Add("PatNum"); table.Columns.Add("patNum"); table.Columns.Add("GuarNum"); table.Columns.Add("patNumAndName"); table.Columns.Add("Pattern"); table.Columns.Add("preMedFlag"); table.Columns.Add("procs"); table.Columns.Add("production"); table.Columns.Add("productionVal"); table.Columns.Add("provider"); table.Columns.Add("ProvHyg"); table.Columns.Add("ProvNum"); table.Columns.Add("wkPhone"); table.Columns.Add("wirelessPhone"); string command = "SELECT p1.Abbr ProvAbbr,p2.Abbr HygAbbr,patient.AddrNote," + "patient.ApptModNote,AptDateTime,appointment.AptNum,AptStatus,Assistant," + "patient.BillingType,patient.BirthDate,patient.Guarantor," + "patient.ChartNumber,Confirmed,patient.CreditType,DateTimeChecked,DateTimeRecd,DateTimeSent," + "guar.FamFinUrgNote,patient.FName,patient.HmPhone,patient.ImageFolder,IsHygiene,IsNewPatient," + "LabCaseNum,patient.LName,patient.MedUrgNote,patient.MiddleI,Note,Op,appointment.PatNum," + "Pattern,patplan.PlanNum,patient.PreferConfirmMethod,patient.PreferContactMethod,patient.Preferred," + "patient.PreferRecallMethod,patient.Premed," + "(SELECT SUM(ProcFee) FROM procedurelog "; if (isPlanned) { command += "WHERE procedurelog.PlannedAptNum=appointment.AptNum) Production, "; } else { command += "WHERE procedurelog.AptNum=appointment.AptNum) Production, "; } command += "ProvHyg,appointment.ProvNum,patient.WirelessPhone,patient.WkPhone " + "FROM appointment LEFT JOIN patient ON patient.PatNum=appointment.PatNum " + "LEFT JOIN provider p1 ON p1.ProvNum=appointment.ProvNum " + "LEFT JOIN provider p2 ON p2.ProvNum=appointment.ProvHyg "; if (isPlanned) { command += "LEFT JOIN labcase ON labcase.PlannedAptNum=appointment.AptNum "; } else { command += "LEFT JOIN labcase ON labcase.AptNum=appointment.AptNum "; } command += "LEFT JOIN patient guar ON guar.PatNum=patient.Guarantor " + "LEFT JOIN patplan ON patplan.PatNum=patient.PatNum "; if (aptNum == 0) { command += "WHERE AptDateTime >= " + POut.PDate(dateStart) + " " + "AND AptDateTime < " + POut.PDate(dateEnd.AddDays(1)) + " " + "AND (AptStatus=1 OR AptStatus=2 OR AptStatus=4 OR AptStatus=5 OR AptStatus=7 OR AptStatus=8) "; } else { command += "WHERE appointment.AptNum=" + POut.PInt(aptNum); } command += " GROUP BY appointment.AptNum"; DataTable raw = dcon.GetTable(command); command = "SELECT AbbrDesc,procedurelog.AptNum,procedurelog.CodeNum,PlannedAptNum,Surf,ToothNum,TreatArea " + "FROM procedurelog,appointment,procedurecode "; if (isPlanned) { command += "WHERE procedurelog.PlannedAptNum=appointment.AptNum "; } else { command += "WHERE procedurelog.AptNum=appointment.AptNum "; } command += "AND procedurelog.CodeNum=procedurecode.CodeNum "; if (aptNum == 0) { command += "AND AptDateTime >= " + POut.PDate(dateStart) + " " + "AND AptDateTime < " + POut.PDate(dateEnd.AddDays(1)) + " "; } else { command += "AND appointment.AptNum=" + POut.PInt(aptNum); } DataTable rawProc = dcon.GetTable(command); //procs for flag, InsNotSent command = "SELECT patient.PatNum, patient.Guarantor " + "FROM patient,procedurecode,procedurelog,claimproc " + "WHERE claimproc.procnum=procedurelog.procnum " + "AND patient.PatNum=procedurelog.PatNum " + "AND procedurelog.CodeNum=procedurecode.CodeNum " + "AND claimproc.NoBillIns=0 " + "AND procedurelog.ProcFee>0 " + "AND claimproc.Status=6 " //estimate + "AND procedurelog.procstatus=2 " + "AND procedurelog.ProcDate >= " + POut.PDate(DateTime.Now.AddYears(-1)) + " " + "AND procedurelog.ProcDate <= " + POut.PDate(DateTime.Now) + " " + "GROUP BY patient.Guarantor"; DataTable rawInsProc = dcon.GetTable(command); DateTime aptDate; TimeSpan span; int hours; int minutes; DateTime labDate; DateTime birthdate; for (int i = 0; i < raw.Rows.Count; i++) { row = table.NewRow(); if (raw.Rows[i]["AddrNote"].ToString() != "") { row["addrNote"] = Lan.g("Appointments", "AddrNote: ") + raw.Rows[i]["AddrNote"].ToString(); } aptDate = PIn.PDateT(raw.Rows[i]["AptDateTime"].ToString()); row["AptDateTime"] = aptDate; birthdate = PIn.PDate(raw.Rows[i]["Birthdate"].ToString()); row["age"] = Lan.g("Appointments", "Age: "); if (birthdate.Year > 1880) { row["age"] += PatientB.DateToAgeString(birthdate); } else { row["age"] += "?"; } if (raw.Rows[i]["ApptModNote"].ToString() != "") { row["apptModNote"] = Lan.g("Appointments", "ApptModNote: ") + raw.Rows[i]["ApptModNote"].ToString(); } row["aptDate"] = aptDate.ToShortDateString(); row["aptDay"] = aptDate.ToString("dddd"); span = TimeSpan.FromMinutes(raw.Rows[i]["Pattern"].ToString().Length *5); hours = span.Hours; minutes = span.Minutes; if (hours == 0) { row["aptLength"] = minutes.ToString() + Lan.g("Appointments", " Min"); } else if (hours == 1) { row["aptLength"] = hours.ToString() + Lan.g("Appointments", " Hr, ") + minutes.ToString() + Lan.g("Appointments", " Min"); } else { row["aptLength"] = hours.ToString() + Lan.g("Appointments", " Hrs, ") + minutes.ToString() + Lan.g("Appointments", " Min"); } row["aptTime"] = aptDate.ToShortTimeString(); row["AptNum"] = raw.Rows[i]["AptNum"].ToString(); row["AptStatus"] = raw.Rows[i]["AptStatus"].ToString(); row["Assistant"] = raw.Rows[i]["Assistant"].ToString(); row["billingType"] = DefB.GetName(DefCat.BillingTypes, PIn.PInt(raw.Rows[i]["BillingType"].ToString())); if (raw.Rows[i]["ChartNumber"].ToString() != "") { row["chartNumber"] = raw.Rows[i]["ChartNumber"].ToString(); } //row["ChartNumber"]=raw.Rows[i]["ChartNumber"].ToString(); row["chartNumAndName"] = ""; if (raw.Rows[i]["IsNewPatient"].ToString() == "1") { row["chartNumAndName"] = "NP-"; } row["chartNumAndName"] += raw.Rows[i]["ChartNumber"].ToString() + " " + PatientB.GetNameLF(raw.Rows[i]["LName"].ToString(), raw.Rows[i]["FName"].ToString(), raw.Rows[i]["Preferred"].ToString(), raw.Rows[i]["MiddleI"].ToString()); row["confirmed"] = DefB.GetName(DefCat.ApptConfirmed, PIn.PInt(raw.Rows[i]["Confirmed"].ToString())); row["Confirmed"] = raw.Rows[i]["Confirmed"].ToString(); row["contactMethods"] = ""; if (raw.Rows[i]["PreferConfirmMethod"].ToString() != "0") { row["contactMethods"] += Lan.g("Appointments", "Confirm Method: ") + ((ContactMethod)PIn.PInt(raw.Rows[i]["PreferConfirmMethod"].ToString())).ToString(); } if (raw.Rows[i]["PreferContactMethod"].ToString() != "0") { if (row["contactMethods"].ToString() != "") { row["contactMethods"] += "\r\n"; } row["contactMethods"] += Lan.g("Appointments", "Contact Method: ") + ((ContactMethod)PIn.PInt(raw.Rows[i]["PreferContactMethod"].ToString())).ToString(); } if (raw.Rows[i]["PreferRecallMethod"].ToString() != "0") { if (row["contactMethods"].ToString() != "") { row["contactMethods"] += "\r\n"; } row["contactMethods"] += Lan.g("Appointments", "Recall Method: ") + ((ContactMethod)PIn.PInt(raw.Rows[i]["PreferRecallMethod"].ToString())).ToString(); } row["creditIns"] = raw.Rows[i]["CreditType"].ToString(); //figure out if pt's family has ins claims that need to be created bool InsToSend = false; for (int j = 0; j < rawInsProc.Rows.Count; j++) { if (raw.Rows[i]["PlanNum"].ToString() != "" && raw.Rows[i]["PlanNum"].ToString() != "0") { if (raw.Rows[i]["Guarantor"].ToString() == rawInsProc.Rows[j]["Guarantor"].ToString() || raw.Rows[i]["Guarantor"].ToString() == rawInsProc.Rows[j]["PatNum"].ToString()) { InsToSend = true; } } } if (InsToSend) { row["creditIns"] += "!"; } else if (raw.Rows[i]["PlanNum"].ToString() != "" && raw.Rows[i]["PlanNum"].ToString() != "0") { row["creditIns"] += "I"; } if (raw.Rows[i]["FamFinUrgNote"].ToString() != "") { row["famFinUrgNote"] = Lan.g("Appointments", "FamFinUrgNote: ") + raw.Rows[i]["FamFinUrgNote"].ToString(); } row["hmPhone"] = Lan.g("Appointments", "Hm: ") + raw.Rows[i]["HmPhone"].ToString(); row["ImageFolder"] = raw.Rows[i]["ImageFolder"].ToString(); if (raw.Rows[i]["PlanNum"].ToString() != "" && raw.Rows[i]["PlanNum"].ToString() != "0") { row["insurance"] = Lan.g("Appointments", "Insured"); } row["IsHygiene"] = raw.Rows[i]["IsHygiene"].ToString(); row["lab"] = ""; if (raw.Rows[i]["LabCaseNum"].ToString() != "") { labDate = PIn.PDateT(raw.Rows[i]["DateTimeChecked"].ToString()); if (labDate.Year > 1880) { row["lab"] = Lan.g("Appointments", "Lab Quality Checked"); } else { labDate = PIn.PDateT(raw.Rows[i]["DateTimeRecd"].ToString()); if (labDate.Year > 1880) { row["lab"] = Lan.g("Appointments", "Lab Received"); } else { labDate = PIn.PDateT(raw.Rows[i]["DateTimeSent"].ToString()); if (labDate.Year > 1880) { row["lab"] = Lan.g("Appointments", "Lab Sent"); //sent but not received } else { row["lab"] = Lan.g("Appointments", "Lab Not Sent"); } } } } row["MedUrgNote"] = raw.Rows[i]["MedUrgNote"].ToString(); row["Note"] = raw.Rows[i]["Note"].ToString(); row["Op"] = raw.Rows[i]["Op"].ToString(); if (raw.Rows[i]["IsNewPatient"].ToString() == "1") { row["patientName"] = "NP-"; } row["patientName"] += PatientB.GetNameLF(raw.Rows[i]["LName"].ToString(), raw.Rows[i]["FName"].ToString(), raw.Rows[i]["Preferred"].ToString(), raw.Rows[i]["MiddleI"].ToString()); row["PatNum"] = raw.Rows[i]["PatNum"].ToString(); row["patNum"] = "PatNum: " + raw.Rows[i]["PatNum"].ToString(); row["GuarNum"] = raw.Rows[i]["Guarantor"].ToString(); row["patNumAndName"] = ""; if (raw.Rows[i]["IsNewPatient"].ToString() == "1") { row["patNumAndName"] = "NP-"; } row["patNumAndName"] += raw.Rows[i]["PatNum"].ToString() + " " + PatientB.GetNameLF(raw.Rows[i]["LName"].ToString(), raw.Rows[i]["FName"].ToString(), raw.Rows[i]["Preferred"].ToString(), raw.Rows[i]["MiddleI"].ToString()); row["Pattern"] = raw.Rows[i]["Pattern"].ToString(); if (raw.Rows[i]["Premed"].ToString() == "1") { row["preMedFlag"] = Lan.g("Appointments", "Premedicate"); } row["procs"] = ""; for (int p = 0; p < rawProc.Rows.Count; p++) { if (!isPlanned && rawProc.Rows[p]["AptNum"].ToString() != raw.Rows[i]["AptNum"].ToString()) { continue; } if (isPlanned && rawProc.Rows[p]["PlannedAptNum"].ToString() != raw.Rows[i]["AptNum"].ToString()) { continue; } if (row["procs"].ToString() != "") { row["procs"] += ", "; } switch (rawProc.Rows[p]["TreatArea"].ToString()) { case "1": //TreatmentArea.Surf: row["procs"] += "#" + Tooth.ToInternat(rawProc.Rows[p]["ToothNum"].ToString()) + "-" + rawProc.Rows[p]["Surf"].ToString() + "-"; //""#12-MOD-" break; case "2": //TreatmentArea.Tooth: row["procs"] += "#" + Tooth.ToInternat(rawProc.Rows[p]["ToothNum"].ToString()) + "-"; //"#12-" break; default: //area 3 or 0 (mouth) break; case "4": //TreatmentArea.Quad: row["procs"] += rawProc.Rows[p]["Surf"].ToString() + "-"; //"UL-" break; case "5": //TreatmentArea.Sextant: row["procs"] += "S" + rawProc.Rows[p]["Surf"].ToString() + "-"; //"S2-" break; case "6": //TreatmentArea.Arch: row["procs"] += rawProc.Rows[p]["Surf"].ToString() + "-"; //"U-" break; case "7": //TreatmentArea.ToothRange: //strLine+=table.Rows[j][13].ToString()+" ";//don't show range break; } row["procs"] += rawProc.Rows[p]["AbbrDesc"].ToString(); } row["production"] = PIn.PDouble(raw.Rows[i]["Production"].ToString()).ToString("c"); row["productionVal"] = raw.Rows[i]["Production"].ToString(); if (raw.Rows[i]["IsHygiene"].ToString() == "1") { row["provider"] = raw.Rows[i]["HygAbbr"].ToString(); if (raw.Rows[i]["ProvAbbr"].ToString() != "") { row["provider"] += " (" + raw.Rows[i]["ProvAbbr"].ToString() + ")"; } } else { row["provider"] = raw.Rows[i]["ProvAbbr"].ToString(); if (raw.Rows[i]["HygAbbr"].ToString() != "") { row["provider"] += " (" + raw.Rows[i]["HygAbbr"].ToString() + ")"; } } row["ProvNum"] = raw.Rows[i]["ProvNum"].ToString(); row["ProvHyg"] = raw.Rows[i]["ProvHyg"].ToString(); row["wirelessPhone"] = Lan.g("Appointments", "Cell: ") + raw.Rows[i]["WirelessPhone"].ToString(); row["wkPhone"] = Lan.g("Appointments", "Wk: ") + raw.Rows[i]["WkPhone"].ToString(); table.Rows.Add(row); } return(table); }
private static DataTable GetProgNotes(int patNum, bool isAuditMode) { DataConnection dcon = new DataConnection(); DataTable table = new DataTable("ProgNotes"); DataRow row; //columns that start with lowercase are altered for display rather than being raw data. table.Columns.Add("ADACode"); table.Columns.Add("aptDateTime", typeof(DateTime)); table.Columns.Add("colorBackG"); table.Columns.Add("colorText"); table.Columns.Add("CommlogNum"); table.Columns.Add("description"); table.Columns.Add("dx"); table.Columns.Add("Dx"); table.Columns.Add("note"); table.Columns.Add("Priority"); table.Columns.Add("procDate"); table.Columns.Add("ProcDate", typeof(DateTime)); table.Columns.Add("procFee"); table.Columns.Add("ProcNum"); table.Columns.Add("ProcNumLab"); table.Columns.Add("procStatus"); table.Columns.Add("ProcStatus"); table.Columns.Add("prov"); table.Columns.Add("RxNum"); table.Columns.Add("signature"); table.Columns.Add("Surf"); table.Columns.Add("toothNum"); table.Columns.Add("ToothNum"); table.Columns.Add("ToothRange"); table.Columns.Add("user"); //table.Columns.Add(""); //but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now. List <DataRow> rows = new List <DataRow>(); //Procedures----------------------------------------------------------------------------------------------------- string command = "SELECT ProcDate,ProcStatus,ToothNum,Surf,Dx,procedurelog.ADACode,ProcNum,procedurecode.Descript," + "provider.Abbr,ProcFee,ProcNumLab,appointment.AptDateTime,Priority,ToothRange " + "FROM procedurelog " + "LEFT JOIN procedurecode ON procedurecode.ADACode=procedurelog.ADACode " + "LEFT JOIN provider ON provider.ProvNum=procedurelog.ProvNum " + "LEFT JOIN appointment ON appointment.AptNum=procedurelog.AptNum " + "AND (appointment.AptStatus=" + POut.PInt((int)ApptStatus.Scheduled) + " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.ASAP) + " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.Broken) + " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.Complete) + ") WHERE procedurelog.PatNum=" + POut.PInt(patNum); if (!isAuditMode) { command += " AND ProcStatus !=6"; //don't include deleted } command += " ORDER BY ProcDate"; //we'll just have to reorder it anyway DataTable rawProcs = dcon.GetTable(command); command = "SELECT ProcNum,EntryDateTime,UserNum,Note," + "CASE WHEN Signature!='' THEN 1 ELSE 0 END AS SigPresent " + "FROM procnote WHERE PatNum=" + POut.PInt(patNum) + " ORDER BY EntryDateTime"; // but this helps when looping for notes DataTable rawNotes = dcon.GetTable(command); DateTime dateT; List <DataRow> labRows = new List <DataRow>(); //Canadian lab procs, which must be added in a loop at the very end. for (int i = 0; i < rawProcs.Rows.Count; i++) { row = table.NewRow(); row["ADACode"] = rawProcs.Rows[i]["ADACode"].ToString(); row["aptDateTime"] = PIn.PDateT(rawProcs.Rows[i]["AptDateTime"].ToString()); row["colorBackG"] = Color.White.ToArgb(); if (((DateTime)row["aptDateTime"]).Date == DateTime.Today) { row["colorBackG"] = DefB.Long[(int)DefCat.MiscColors][6].ItemColor.ToArgb().ToString(); } switch ((ProcStat)PIn.PInt(rawProcs.Rows[i]["ProcStatus"].ToString())) { case ProcStat.TP: row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][0].ItemColor.ToArgb().ToString(); break; case ProcStat.C: row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][1].ItemColor.ToArgb().ToString(); break; case ProcStat.EC: row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][2].ItemColor.ToArgb().ToString(); break; case ProcStat.EO: row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][3].ItemColor.ToArgb().ToString(); break; case ProcStat.R: row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][4].ItemColor.ToArgb().ToString(); break; case ProcStat.D: row["colorText"] = Color.Black.ToArgb().ToString(); break; } row["CommlogNum"] = 0; row["description"] = rawProcs.Rows[i]["Descript"].ToString(); row["dx"] = DefB.GetValue(DefCat.Diagnosis, PIn.PInt(rawProcs.Rows[i]["Dx"].ToString())); row["Dx"] = rawProcs.Rows[i]["Dx"].ToString(); //note----------------------------------------------------------------------------------------------------------- if (isAuditMode) //we will include all notes for each proc. We will concat and make readable. { for (int n = 0; n < rawNotes.Rows.Count; n++) //loop through each note { if (rawProcs.Rows[i]["ProcNum"].ToString() != rawNotes.Rows[n]["ProcNum"].ToString()) { continue; } if (row["Note"].ToString() != "") //if there is an existing note { row["note"] += "\r\n------------------------------------------------------\r\n"; //start a new line } row["note"] += PIn.PDateT(rawNotes.Rows[n]["EntryDateTime"].ToString()).ToString(); row["note"] += " " + UserodB.GetName(PIn.PInt(rawNotes.Rows[n]["UserNum"].ToString())); if (rawNotes.Rows[n]["SigPresent"].ToString() == "1") { row["note"] += " " + Lan.g("ChartModule", "(signed)"); } row["note"] += "\r\n" + rawNotes.Rows[n]["Note"].ToString(); } } else //we just want the most recent note { for (int n = rawNotes.Rows.Count - 1; n >= 0; n--) //loop through each note, backwards. { if (rawProcs.Rows[i]["ProcNum"].ToString() != rawNotes.Rows[n]["ProcNum"].ToString()) { continue; } row["user"] = UserodB.GetName(PIn.PInt(rawNotes.Rows[n]["UserNum"].ToString())); row["note"] = rawNotes.Rows[n]["Note"].ToString(); if (rawNotes.Rows[n]["SigPresent"].ToString() == "1") { row["signature"] = Lan.g("ChartModule", "Signed"); } else { row["signature"] = ""; } break; //out of note loop. } } row["Priority"] = rawProcs.Rows[i]["Priority"].ToString(); dateT = PIn.PDateT(rawProcs.Rows[i]["ProcDate"].ToString()); if (dateT.Year < 1880) { row["procDate"] = ""; } else { row["procDate"] = dateT.ToShortDateString(); } row["ProcDate"] = dateT; row["procFee"] = PIn.PDouble(rawProcs.Rows[i]["ProcFee"].ToString()).ToString("F"); row["ProcNum"] = rawProcs.Rows[i]["ProcNum"].ToString(); row["ProcNumLab"] = rawProcs.Rows[i]["ProcNumLab"].ToString(); row["procStatus"] = Lan.g("enumProcStat", ((ProcStat)PIn.PInt(rawProcs.Rows[i]["ProcStatus"].ToString())).ToString()); row["ProcStatus"] = rawProcs.Rows[i]["ProcStatus"].ToString(); row["prov"] = rawProcs.Rows[i]["Abbr"].ToString(); row["RxNum"] = 0; row["Surf"] = rawProcs.Rows[i]["Surf"].ToString(); row["toothNum"] = Tooth.ToInternat(rawProcs.Rows[i]["ToothNum"].ToString()); row["ToothNum"] = rawProcs.Rows[i]["ToothNum"].ToString(); row["ToothRange"] = rawProcs.Rows[i]["ToothRange"].ToString(); if (rawProcs.Rows[i]["ProcNumLab"].ToString() == "0") //normal proc { rows.Add(row); } else { row["description"] = "-----" + row["description"].ToString(); labRows.Add(row); //these will be added in the loop at the end } } //Commlog----------------------------------------------------------------------------------------------------------- command = "SELECT CommlogNum,CommDateTime,CommType,Note FROM commlog WHERE PatNum=" + POut.PInt(patNum) + " ORDER BY CommDateTime"; DataTable rawComm = dcon.GetTable(command); for (int i = 0; i < rawComm.Rows.Count; i++) { row = table.NewRow(); row["colorBackG"] = Color.White.ToArgb(); row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][6].ItemColor.ToArgb().ToString(); row["CommlogNum"] = rawComm.Rows[i]["CommlogNum"].ToString(); row["description"] = Lan.g("ChartModule", "Comm - ") + Lan.g("enumCommItemType", ((CommItemType)PIn.PInt(rawComm.Rows[i]["CommType"].ToString())).ToString()); row["note"] = rawComm.Rows[i]["Note"].ToString(); dateT = PIn.PDateT(rawComm.Rows[i]["CommDateTime"].ToString()); if (dateT.Year < 1880) { row["procDate"] = ""; } else { row["procDate"] = dateT.ToShortDateString(); } row["ProcDate"] = dateT; row["ProcNum"] = 0; row["RxNum"] = 0; rows.Add(row); } //Rx------------------------------------------------------------------------------------------------------------------ command = "SELECT RxNum,RxDate,Drug,Disp,ProvNum,Notes FROM rxpat WHERE PatNum=" + POut.PInt(patNum) + " ORDER BY RxDate"; DataTable rawRx = dcon.GetTable(command); for (int i = 0; i < rawRx.Rows.Count; i++) { row = table.NewRow(); row["colorBackG"] = Color.White.ToArgb(); row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][5].ItemColor.ToArgb().ToString(); row["CommlogNum"] = 0; row["description"] = Lan.g("ChartModule", "Rx - ") + rawRx.Rows[i]["Drug"].ToString() + " - #" + rawRx.Rows[i]["Disp"].ToString(); row["note"] = rawRx.Rows[i]["Notes"].ToString(); dateT = PIn.PDate(rawRx.Rows[i]["RxDate"].ToString()); if (dateT.Year < 1880) { row["procDate"] = ""; } else { row["procDate"] = dateT.ToShortDateString(); } row["ProcDate"] = dateT; row["ProcNum"] = 0; row["RxNum"] = rawRx.Rows[i]["RxNum"].ToString(); rows.Add(row); } //Sorting rows.Sort(CompareChartRows); //Canadian lab procedures need to come immediately after their corresponding proc--------------------------------- for (int i = 0; i < labRows.Count; i++) { for (int r = 0; r < rows.Count; r++) { if (rows[r]["ProcNum"].ToString() == labRows[i]["ProcNumLab"].ToString()) { rows.Insert(r + 1, labRows[i]); break; } } } for (int i = 0; i < rows.Count; i++) { table.Rows.Add(rows[i]); } return(table); }