/// <summary> /// Get's OperatoryNotes based on PatientId, ClinicId, UserId passing from the front end /// </summary> /// <param name="patientId"></param> /// <param name="clinicId"></param> /// <param name="UserId"></param> /// <returns>OperatoryNotes table filtered by PatientId, ClinicId, UserId</returns> #region GetOperatoryNotes public Tuple <List <operatory_notes>, int> GetOperatoryNotesByPatientIdByClinicIDByUserId(string patientId, string clinicId, string UserId, string OrderBy, int?pageSize, int currentPage = 1) { List <operatory_notes> lstNotes = new List <operatory_notes>(); int totalCount = 0; using (OdbcConnection con = new OdbcConnection(ConnectionPath)) { string query1 = string.Format(@"SELECT Count(*) as totalCount FROM operatory_notes o_n INNER JOIN patient p ON o_n.patient_Id=p.patient_Id INNER JOIN provider pr ON o_n.user_Id=pr.provider_Id where p.patient_id ='{0}' AND o_n.practice_id= '{1}' AND o_n.user_id= '{2}'", patientId, clinicId, UserId); OdbcCommand cmd1 = new OdbcCommand(query1, con); string pagination = ""; if (pageSize.HasValue) { pagination = string.Format("Top {0} start at(({0}*{1}+1)-{0})", pageSize, currentPage); } string query2 = string.Format(@"SELECT {0} p.first_name as patientFirstName,p.last_name as patientLastName,pr.first_name ,pr.last_name ,* FROM operatory_notes o_n INNER JOIN patient p ON o_n.patient_Id=p.patient_Id INNER JOIN provider pr ON o_n.user_Id=pr.provider_Id where p.patient_id ='{1}' AND o_n.practice_id= '{2}'AND o_n.user_id= '{3}' order by {4}", pagination, patientId, clinicId, UserId, OrderBy); OdbcCommand cmd2 = new OdbcCommand(query2, con); try { con.Open(); if (con.State == System.Data.ConnectionState.Open) { SettingsExtensions.IsDBConnected = true; } OdbcDataReader rdr1 = cmd1.ExecuteReader(); while (rdr1.Read()) { totalCount = Convert.ToInt32(rdr1["totalCount"]); } OdbcDataReader rdr2 = cmd2.ExecuteReader(); while (rdr2.Read()) { operatory_notes on = new operatory_notes(); #region Operatory Data Table on.note_id = Convert.ToInt32(rdr2["note_id"]); on.date_entered = Convert.ToDateTime(rdr2["Date_entered"]); on.note_class = Convert.ToChar(rdr2["note_class"]); on.note_type = rdr2["note_type"].ToString(); on.note_type_id = Convert.ToInt32(rdr2["note_type_id"]); on.description = rdr2["description"].ToString(); on.note = rdr2["note"].ToString(); on.color = Convert.ToInt32(rdr2["color"]); on.post_proc_status = Convert.ToChar(rdr2["post_proc_status"]); on.date_modified = rdr2["date_modified"].ToString(); on.modified_by = rdr2["modified_by"].ToString(); on.locked_eod = Convert.ToInt32(rdr2["locked_eod"]); on.status = Convert.ToChar(rdr2["status"]); on.tooth_data = rdr2["tooth_data"].ToString(); on.claim_id = Convert.ToInt32(rdr2["claim_id"]); on.statement_yn = Convert.ToChar(rdr2["statement_yn"]); on.resp_party_id = rdr2["resp_party_id"].ToString(); on.tooth = rdr2["tooth"].ToString(); on.tran_num = Convert.ToInt32(rdr2["tran_num"]); on.archive_name = rdr2["archive_name"].ToString(); on.archive_path = rdr2["archive_path"].ToString(); on.service_code = rdr2["service_code"].ToString(); on.practice_id = Convert.ToInt16(rdr2["practice_id"]); on.freshness = rdr2["freshness"].ToString(); on.surface_detail = rdr2["surface_detail"].ToString(); on.surface = rdr2["surface"].ToString(); #endregion Operatory Data Table #region Patient Data Table on.patient_id = rdr2["patient_id"].ToString(); on.patientFirstName = rdr2["patientFirstName"].ToString(); on.patientLastName = rdr2["patientLastName"].ToString(); #endregion Patient Data Table #region Provider Data Table on.provider_id = rdr2["user_id"].ToString(); on.first_name = rdr2["first_name"].ToString(); on.last_name = rdr2["last_name"].ToString(); #endregion Provider Data Table lstNotes.Add(on); } con.Close(); } catch (Exception ex) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("Error:Unable to connect to the database for GET notes api."); Console.ResetColor(); SettingsExtensions.IsDBConnected = false; } } return(Tuple.Create(lstNotes, totalCount)); }
/// <summary> /// Insert or updates data into Operatory Notes /// </summary> /// <param name="operatoryNotes"></param> /// <param name="autoNoteId"></param> #region InsertOrUpdateNotes public string InsertOrUpdateNotes(operatory_notes operatoryNotes, int?autoNoteId, string noteType) { return(_context.InsertOrUpdateOperatoryNotes(operatoryNotes, autoNoteId, noteType)); }
/// <summary> /// Insert or Update Notes basedon the autoNoteId passing from the front end or not /// </summary> /// <param name="operatoryNotes"></param> /// <param name="autoNoteId"></param> #region InsertOrUpdateOperatoryNotes public string InsertOrUpdateOperatoryNotes(operatory_notes operatoryNotes, int?autoNoteId, string noteType) { string rowsAffected = "false"; int rows = 0; string noteTypeSelected = null; if (operatoryNotes.note_id != 0) { using (OdbcConnection con = new OdbcConnection(ConnectionPath)) { string queryNT = string.Format(@"Select note_type from operatory_notes where note_id='{0}'", operatoryNotes.note_id); OdbcCommand cmdNT = new OdbcCommand(queryNT, con); try { con.Open(); if (con.State == System.Data.ConnectionState.Open) { SettingsExtensions.IsDBConnected = true; } OdbcDataReader rdrNT = cmdNT.ExecuteReader(); while (rdrNT.Read()) { operatory_notes op = new operatory_notes(); op.note_type = rdrNT["note_type"].ToString(); noteTypeSelected = op.note_type; } if (noteTypeSelected == "N") { con.Close(); return("Contract Note"); } else { } } catch (Exception ex) { } } } { using (OdbcConnection con = new OdbcConnection(ConnectionPath)) { var dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); if (autoNoteId != null) { string query = string.Format(@"Select note_text, description from autonotes where note_id='{0}'", autoNoteId); string querydesc = string.Format(@"Select id, note_type, description from operatory_notes_type where note_type='{0}'", noteType); OdbcCommand cmd1 = new OdbcCommand(query, con); OdbcCommand cmd5 = new OdbcCommand(querydesc, con); try { con.Open(); if (con.State == System.Data.ConnectionState.Open) { SettingsExtensions.IsDBConnected = true; } OdbcDataReader rdr = cmd1.ExecuteReader(); OdbcDataReader rdr1 = cmd5.ExecuteReader(); while (rdr1.Read()) { operatory_notes opn = new operatory_notes(); opn.note_type_id = Convert.ToInt32(rdr1["id"]); opn.note_type = rdr1["note_type"].ToString(); opn.description = rdr1["description"].ToString(); while (rdr.Read()) { operatory_notes on = new operatory_notes(); on.note = rdr["note_text"].ToString(); // If AutoNote Id is passing from the Api to an Existing Note. if (operatoryNotes.note_id != 0 && operatoryNotes.note_type != "N") { string query1 = string.Format(@"Update operatory_notes SET note_type_id='{0}',note_type='{1}',modified_by='{3}', date_modified = '{2}', freshness = '{2}' , user_id='{3}', description='{4}',tooth='{5}', surface='{6}', color='{7}', note = note +','+'{8}', note_class='{9}' where note_id ='{10}' AND patient_id ='{11}' AND practice_id= '{12}' AND user_id= '{3}'", opn.note_type_id, opn.note_type, dateTimeNow, operatoryNotes.user_id, opn.description, operatoryNotes.tooth, operatoryNotes.surface, operatoryNotes.color, operatoryNotes.note, operatoryNotes.note_class, operatoryNotes.note_id, operatoryNotes.patient_id, operatoryNotes.practice_id); OdbcCommand cmd2 = new OdbcCommand(query1, con); rows = cmd2.ExecuteNonQuery(); } else { // If AutoNote Id is passing from the Api to an Fresh Note. string query2 = "Insert into operatory_notes (patient_id,Date_entered,user_id,note_class,note_type,note_type_id,description,note,color,post_proc_status,date_modified,modified_by,locked_eod,status,tooth_data,claim_id,statement_yn,resp_party_id,tooth,tran_num,archive_name,archive_path,service_code,practice_id,freshness,surface_detail,surface) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";//, $data['patient_id'],$Date_entered,$user_id,$note_class,$note_type,$note_type_id,$description,$note,$color,$post_proc_status,$date_modified,$modified_by,$locked_eod,$status,$tooth_data,$claim_id,$statement_yn,$resp_party_id,$tooth,$tran_num,$archive_name,$archive_path,$service_code,$practice_id,$freshness,$surface_detail,$surface)"; OdbcCommand cmd = new OdbcCommand(query2, con); #region Operatory Data Table cmd.Parameters.AddWithValue("?", operatoryNotes.patient_id); cmd.Parameters.AddWithValue("?", dateTimeNow); cmd.Parameters.AddWithValue("?", operatoryNotes.user_id); cmd.Parameters.AddWithValue("?", operatoryNotes.note_class);// Note class should be always T cmd.Parameters.AddWithValue("?", opn.note_type); cmd.Parameters.AddWithValue("?", opn.note_type_id); cmd.Parameters.AddWithValue("?", opn.description); cmd.Parameters.AddWithValue("?", on.note + operatoryNotes.note); cmd.Parameters.AddWithValue("?", operatoryNotes.color); cmd.Parameters.AddWithValue("?", operatoryNotes.post_proc_status); cmd.Parameters.AddWithValue("?", dateTimeNow); cmd.Parameters.AddWithValue("?", operatoryNotes.user_id); //same as user_id/provider_id cmd.Parameters.AddWithValue("?", operatoryNotes.locked_eod); cmd.Parameters.AddWithValue("?", operatoryNotes.status); cmd.Parameters.AddWithValue("?", operatoryNotes.tooth_data); cmd.Parameters.AddWithValue("?", operatoryNotes.claim_id); cmd.Parameters.AddWithValue("?", operatoryNotes.statement_yn); cmd.Parameters.AddWithValue("?", operatoryNotes.patient_id); //same as the patient_id as per the spread sheet given. cmd.Parameters.AddWithValue("?", operatoryNotes.tooth); cmd.Parameters.AddWithValue("?", operatoryNotes.tran_num); cmd.Parameters.AddWithValue("?", operatoryNotes.archive_name); cmd.Parameters.AddWithValue("?", operatoryNotes.archive_path); cmd.Parameters.AddWithValue("?", operatoryNotes.service_code); cmd.Parameters.AddWithValue("?", operatoryNotes.practice_id); cmd.Parameters.AddWithValue("?", dateTimeNow); cmd.Parameters.AddWithValue("?", operatoryNotes.surface_detail); cmd.Parameters.AddWithValue("?", operatoryNotes.surface); #endregion Operatory Data Table rows = cmd.ExecuteNonQuery(); } } } con.Close(); } catch (Exception ex) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("Error:Unable to connect to the database for GET notes api."); Console.ResetColor(); SettingsExtensions.IsDBConnected = true; } } else { try { con.Open(); string query = string.Format(@"Select id, note_type, description from operatory_notes_type where note_type='{0}'", noteType); OdbcCommand cmd1 = new OdbcCommand(query, con); if (con.State == System.Data.ConnectionState.Open) { SettingsExtensions.IsDBConnected = true; } OdbcDataReader rdr = cmd1.ExecuteReader(); while (rdr.Read()) { operatory_notes opn = new operatory_notes(); opn.note_type_id = Convert.ToInt32(rdr["id"]); opn.note_type = rdr["note_type"].ToString(); opn.description = rdr["description"].ToString(); // If AutoNote Id is NOT passed from the Api and provider is Modifing an existing note if (operatoryNotes.note_id != 0 && operatoryNotes.note_type != "N") { string query3 = string.Format(@"Update operatory_notes SET note_type_id='{0}',note_type='{1}',modified_by='{3}', date_modified = '{2}', freshness = '{2}' , user_id='{3}', description='{4}',tooth='{5}', surface='{6}', color='{7}', note = note +','+'{8}', note_class='{9}' where note_id ='{10}' AND patient_id ='{11}' AND practice_id= '{12}' AND user_id= '{3}'", opn.note_type_id, opn.note_type, dateTimeNow, operatoryNotes.user_id, opn.description, operatoryNotes.tooth, operatoryNotes.surface, operatoryNotes.color, operatoryNotes.note, operatoryNotes.note_class, operatoryNotes.note_id, operatoryNotes.patient_id, operatoryNotes.practice_id, operatoryNotes.user_id); OdbcCommand cmd3 = new OdbcCommand(query3, con); rows = cmd3.ExecuteNonQuery(); } // If AutoNote Id is NOT passed from the Api and provider is creating an new note else { string query4 = "Insert into operatory_notes (patient_id,Date_entered,user_id,note_class,note_type,note_type_id,description,note,color,post_proc_status,date_modified,modified_by,locked_eod,status,tooth_data,claim_id,statement_yn,resp_party_id,tooth,tran_num,archive_name,archive_path,service_code,practice_id,freshness,surface_detail,surface) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";//, $data['patient_id'],$Date_entered,$user_id,$note_class,$note_type,$note_type_id,$description,$note,$color,$post_proc_status,$date_modified,$modified_by,$locked_eod,$status,$tooth_data,$claim_id,$statement_yn,$resp_party_id,$tooth,$tran_num,$archive_name,$archive_path,$service_code,$practice_id,$freshness,$surface_detail,$surface)"; OdbcCommand cmd4 = new OdbcCommand(query4, con); #region Operatory Data Table cmd4.Parameters.AddWithValue("?", operatoryNotes.patient_id); cmd4.Parameters.AddWithValue("?", dateTimeNow); cmd4.Parameters.AddWithValue("?", operatoryNotes.user_id); cmd4.Parameters.AddWithValue("?", operatoryNotes.note_class);// Note class should be always T cmd4.Parameters.AddWithValue("?", opn.note_type); cmd4.Parameters.AddWithValue("?", opn.note_type_id); cmd4.Parameters.AddWithValue("?", opn.description); cmd4.Parameters.AddWithValue("?", operatoryNotes.note); cmd4.Parameters.AddWithValue("?", operatoryNotes.color); cmd4.Parameters.AddWithValue("?", operatoryNotes.post_proc_status); cmd4.Parameters.AddWithValue("?", dateTimeNow); cmd4.Parameters.AddWithValue("?", operatoryNotes.user_id);//same as user_id/provider_id cmd4.Parameters.AddWithValue("?", operatoryNotes.locked_eod); cmd4.Parameters.AddWithValue("?", operatoryNotes.status); cmd4.Parameters.AddWithValue("?", operatoryNotes.tooth_data); cmd4.Parameters.AddWithValue("?", operatoryNotes.claim_id); cmd4.Parameters.AddWithValue("?", operatoryNotes.statement_yn); cmd4.Parameters.AddWithValue("?", operatoryNotes.patient_id);//same as the patient_id as per the spread sheet given. cmd4.Parameters.AddWithValue("?", operatoryNotes.tooth); cmd4.Parameters.AddWithValue("?", operatoryNotes.tran_num); cmd4.Parameters.AddWithValue("?", operatoryNotes.archive_name); cmd4.Parameters.AddWithValue("?", operatoryNotes.archive_path); cmd4.Parameters.AddWithValue("?", operatoryNotes.service_code); cmd4.Parameters.AddWithValue("?", operatoryNotes.practice_id); cmd4.Parameters.AddWithValue("?", dateTimeNow); cmd4.Parameters.AddWithValue("?", operatoryNotes.surface_detail); cmd4.Parameters.AddWithValue("?", operatoryNotes.surface); #endregion Operatory Data Table rows = cmd4.ExecuteNonQuery(); } } con.Close(); } catch (Exception ex) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("Error:Unable to connect to the database for GET notes api."); Console.ResetColor(); SettingsExtensions.IsDBConnected = true; } } } if (rows > 0) { rowsAffected = "true"; } return(rowsAffected); } // return rowsAffected; }