public DataTable SelectMissingProcedure(model.account.Account p_oAccount,model.appointment.Appointment p_StartDate, model.appointment.Appointment p_EndDate, List<model.specialty.Specialty> p_oSpecialty, List<model.carrier.Carrier> p_oCarrier,List<model.procedure.Procedure> p_oProcedure,model.casetype.CaseType p_oCasetype,model.casestatus.CaseStatus p_oCaseStatus) { SqlConnection oConnection = new SqlConnection(DBUtil.ConnectionString); oConnection.Open(); SqlTransaction oTransaction = null; try { oTransaction = oConnection.BeginTransaction(IsolationLevel.ReadUncommitted); DataSet ds = new DataSet(); DataTable oDTCarrier = null; if (p_oCarrier != null && p_oCarrier.Count > 0) { oDTCarrier = gbmodel.carrier.type.TypeCarrier.FillDBType(p_oCarrier); } DataTable oDTProcedure = null; if (p_oProcedure != null && p_oProcedure.Count > 0) { oDTProcedure = gbmodel.procedure.type.TypeProcedure.FillDBType(p_oProcedure); } string sQuery = null; if (p_oProcedure != null && p_oProcedure.Count > 0) { sQuery = " select t6.sz_case_no [CaseNo], t7.sz_patient_last_name + ', ' + t7.sz_patient_first_name [PatientName], t8.sz_insurance_name [CarrierName],"; sQuery += " t6.dt_date_of_accident[AccidentDate],t7.sz_patient_cellno[Phone1],t7.sz_patient_phone[Phone2] from mst_case_master t6 JOIN mst_patient t7 on t7.sz_patient_id = t6.sz_patient_id AND t7.sz_company_id = @sz_company_id"; sQuery += " left join mst_insurance_company t8 on t8.sz_insurance_id = t6.sz_insurance_id where t6.sz_case_id not in (select t2.sz_case_id from mst_procedure_group t1"; sQuery += " left join txn_doctor_speciality t5 on t5.sz_procedure_group_id = t1.sz_procedure_group_id AND t1.sz_company_id = @sz_company_id"; sQuery += " left join mst_doctor t3 on t3.sz_doctor_id = t5.sz_doctor_id AND t3.sz_company_id = @sz_company_id"; sQuery += " left join txn_calendar_event t2 on t2.sz_doctor_id = t3.sz_doctor_id AND t2.sz_company_id = @sz_company_id"; sQuery += " left join txn_calender_event_prpcedure t6 on t6.i_event_id = t2.i_event_id and t2.sz_company_id = @sz_company_id "; sQuery += " where t1.sz_company_id = @sz_company_id and t1.sz_procedure_group_id IN (@sz_specialty_id) "; sQuery += " AND t6.sz_proc_code IN (SELECT sz_procedure_id FROM @tvp_procedure) "; sQuery += " and t2.sz_case_id IS NOT NULL AND t2.dt_event_date between @dt_start AND @dt_end ) AND t6.sz_company_id = @sz_company_id "; if (p_oCasetype != null && p_oCasetype.ID != null && p_oCasetype.ID != "NA") sQuery += "and t6.sz_case_type_id=@sz_case_type_id"; if (p_oCaseStatus != null && p_oCaseStatus.ID != null && p_oCaseStatus.ID != "NA") sQuery += " and t6.sz_case_status_id=@sz_case_status_id"; } else { sQuery = " select t6.sz_case_no [CaseNo], t7.sz_patient_last_name + ', ' + t7.sz_patient_first_name [PatientName], t8.sz_insurance_name [CarrierName],"; sQuery += " t6.dt_date_of_accident[AccidentDate],t7.sz_patient_cellno[Phone1],t7.sz_patient_phone[Phone2] from mst_case_master t6 JOIN mst_patient t7 on t7.sz_patient_id = t6.sz_patient_id AND t7.sz_company_id = @sz_company_id"; sQuery += " left join mst_insurance_company t8 on t8.sz_insurance_id = t6.sz_insurance_id where t6.sz_case_id not in (select t2.sz_case_id from mst_procedure_group t1"; sQuery += " left join txn_doctor_speciality t5 on t5.sz_procedure_group_id = t1.sz_procedure_group_id AND t1.sz_company_id = @sz_company_id"; sQuery += " left join mst_doctor t3 on t3.sz_doctor_id = t5.sz_doctor_id AND t3.sz_company_id = @sz_company_id"; sQuery += " left join txn_calendar_event t2 on t2.sz_doctor_id = t3.sz_doctor_id AND t2.sz_company_id = @sz_company_id"; sQuery += " where t1.sz_company_id = @sz_company_id and t1.sz_procedure_group_id IN (@sz_specialty_id)"; sQuery += " and t2.sz_case_id IS NOT NULL AND t2.dt_event_date between @dt_start AND @dt_end ) AND t6.sz_company_id = @sz_company_id "; if (p_oCasetype != null && p_oCasetype.ID != null && p_oCasetype.ID != "NA") sQuery += "and t6.sz_case_type_id=@sz_case_type_id"; if (p_oCaseStatus != null && p_oCaseStatus.ID != null && p_oCaseStatus.ID != "NA") sQuery += " and t6.sz_case_status_id=@sz_case_status_id"; } if (oDTCarrier != null && oDTCarrier.Rows.Count > 0) { sQuery += " AND (t6.sz_insurance_id IN (SELECT sz_insurance_id FROM mst_insurance_company tc where tc.sz_company_id = @sz_company_id and ltrim(rtrim(tc.sz_insurance_name)) IN (SELECT ltrim(rtrim(sz_name)) FROM @tvp_carrier)))"; } sQuery += " order by t6.sz_case_id "; SqlCommand com = new SqlCommand(sQuery, oConnection); com.Transaction = oTransaction; com.Parameters.AddWithValue("@sz_company_id", p_oAccount.ID); com.Parameters.AddWithValue("@dt_start", p_StartDate.Date); com.Parameters.AddWithValue("@dt_end", p_EndDate.Date); com.Parameters.AddWithValue("@sz_specialty_id", p_oSpecialty[0].ID); com.Parameters.AddWithValue("@sz_case_type_id", p_oCasetype.ID); com.Parameters.AddWithValue("@sz_case_status_id", p_oCaseStatus.ID); if (p_oProcedure != null && p_oProcedure.Count > 0) { SqlParameter tvpParamProcedure = com.Parameters.AddWithValue( "@tvp_procedure", oDTProcedure); tvpParamProcedure.SqlDbType = SqlDbType.Structured; tvpParamProcedure.TypeName = gbmodel.procedure.type.TypeProcedure.GetTypeName(); } if (oDTCarrier != null) { SqlParameter tvpParamCarrier = com.Parameters.AddWithValue( "@tvp_carrier", oDTCarrier); tvpParamCarrier.SqlDbType = SqlDbType.Structured; tvpParamCarrier.TypeName = gbmodel.carrier.type.TypeCarrier.GetTypeName(); } SqlDataAdapter adapter = new SqlDataAdapter(com); adapter.Fill(ds); oTransaction.Commit(); if (ds != null) { if (ds.Tables[0] != null) { return ds.Tables[0]; } } } catch(Exception x) { oTransaction.Rollback(); } finally { if(oConnection != null) { oConnection.Close(); oConnection = null; } } return null; }
public ArrayList SelectTreatmentPlanNameAndLocation(model.account.Account p_oAccount) { DataSet ds = null; SqlConnection connection = new SqlConnection(DBUtil.ConnectionString); ArrayList list = new ArrayList(); try { connection.Open(); SqlCommand selectCommand = new SqlCommand(); ds = new DataSet(); SqlDataAdapter adapter_one = new SqlDataAdapter("exec sp_select_ch_treatment_plan_name @sz_company_id='" + p_oAccount.ID + "' EXEC sp_select_ch_treatment_plan_location @sz_company_id='" + p_oAccount.ID + "' EXEC sp_select_treatment_plan @sz_company_id='" + p_oAccount.ID + "'", connection); adapter_one.SelectCommand.CommandTimeout = 0; adapter_one.Fill(ds); gbmodel.appointment.Appointment oAppointment = new gbmodel.appointment.Appointment(); oAppointment.Note = new gbmodel.appointment.note.Note(); List <gbmodel.appointment.note.ch.TreatmentPlan> oTreatementPlanName = new List <gbmodel.appointment.note.ch.TreatmentPlan>(); List <gbmodel.appointment.note.ch.TreatmentPlan> oTreatmentPlanLocation = new List <gbmodel.appointment.note.ch.TreatmentPlan>(); gbmodel.appointment.Appointment p_oAppointment = new gbmodel.appointment.Appointment(); p_oAppointment.Note = new gbmodel.appointment.note.Note(); p_oAppointment.Note.TreatmentPlan = new List <gbmodel.appointment.note.ch.TreatmentPlan>(); gbmodel.appointment.note.ch.TreatmentPlan ch_plan_name = new gbmodel.appointment.note.ch.TreatmentPlan(); gbmodel.appointment.note.ch.TreatmentPlan ch_plan_location = new gbmodel.appointment.note.ch.TreatmentPlan(); foreach (DataRow dr in ds.Tables[0].Rows) { ch_plan_name = new gbmodel.appointment.note.ch.TreatmentPlan(); ch_plan_name.ID = Convert.ToInt32(dr["i_id"]); ch_plan_name.Name = dr["sz_treatment_plan_name"].ToString(); oTreatementPlanName.Add(ch_plan_name); } list.Add(oTreatementPlanName); foreach (DataRow dr_location in ds.Tables[1].Rows) { ch_plan_location = new gbmodel.appointment.note.ch.TreatmentPlan(); ch_plan_location.ID = Convert.ToInt32(dr_location["i_id"]); ch_plan_location.Location = dr_location["sz_treatment_plan_location_name"].ToString(); oTreatmentPlanLocation.Add(ch_plan_location); } list.Add(oTreatmentPlanLocation); list.Add(ds.Tables[2]); } finally { if (connection != null) { if (connection.State == ConnectionState.Open) { connection.Close(); } connection = null; } } return(list); }
public DataTable Select(model.account.Account p_oAccount, model.appointment.Appointment p_oAppointment, List <model.office.Office> p_oOffice, List <model.specialty.Specialty> p_oSpecialty, List <model.physician.Physician> p_lstReferringDoctor, List <model.carrier.Carrier> p_oCarrier, List <model.carriergroup.CarrierGroup> p_oCarrierGroup, List <model.procedure.Procedure> p_oProcedure, List <model.provider.Provider> p_lstProvider) { SqlConnection oConnection = new SqlConnection(DBUtil.ConnectionString); oConnection.Open(); SqlTransaction oTransaction = null; try { oTransaction = oConnection.BeginTransaction(IsolationLevel.ReadUncommitted); DataSet ds = new DataSet(); DataTable oDTReferringDoctor = null; if (p_lstReferringDoctor != null && p_lstReferringDoctor.Count > 0) { oDTReferringDoctor = gbmodel.physician.type.TypePhysician.FillDBType(p_lstReferringDoctor); } DataTable oDTSpecialty = null; if (p_oSpecialty != null && p_oSpecialty.Count > 0) { oDTSpecialty = gbmodel.specialty.type.TypeSpecialty.FillDBType(p_oSpecialty); } DataTable oDTOffice = null; if (p_oOffice != null && p_oOffice.Count > 0) { oDTOffice = gbmodel.office.type.TypeOffice.FillDBType(p_oOffice); } DataTable oDTCarrier = null; if (p_oCarrier != null && p_oCarrier.Count > 0) { oDTCarrier = gbmodel.carrier.type.TypeCarrier.FillDBType(p_oCarrier); } DataTable oDTCarrierGroup = null; oDTCarrierGroup = gbmodel.carriergroup.type.TypeCarrierGroup.FillDBType(p_oCarrierGroup); //Procedure Codes DataTable oDTProcedure = null; if (p_oProcedure != null && p_oProcedure.Count > 0) { oDTProcedure = gbmodel.procedure.type.TypeProcedure.FillDBType(p_oProcedure); } // Provider DataTable oDTProvider = null; if (p_lstProvider != null && p_lstProvider.Count > 0) { oDTProvider = gbmodel.provider.type.TypeProvider.FillDBType(p_lstProvider); } string sQuery = null; sQuery = " SELECT DISTINCT t2.sz_case_no [CaseNo],t3.sz_patient_last_name + ', ' + t3.sz_patient_first_name [PatientName],"; sQuery += " t4.sz_case_type_name [CaseType],t5.sz_insurance_id [InsuranceID],t5.sz_insurance_name [InsuranceName],dt_event_date [AppointmentDate],"; sQuery += " t1.i_status [AppointmentStatus],CASE WHEN t1.i_status = 2 THEN 'Completed' WHEN t1.i_status = 0 THEN 'Scheduled' END [Appointment Status] , CASE WHEN ISNULL(t1.bt_status, 0) = 1 THEN 'Billed' ELSE 'UnBilled'END [IsBilled],"; sQuery += " t8.sz_bill_number [BillNumber],ISNULL(t6.sz_doctor_name, 'N/A') [ReferringDoctor],ISNULL(t7.sz_office, 'N/A') [ReferringOffice],"; sQuery += " dbo.fnc_get_procedure_codes_selected(t1.i_event_id,@tvp_procedure) [EventProcedures],dbo.fnc_get_procedure_sum_for_event(t1.i_event_id,@tvp_procedure) [ProcedureSum],t8.flt_bill_amount [BillAmount]"; sQuery += " FROM txn_calendar_event t1 JOIN mst_case_master t2 ON t2.sz_case_id = t1.sz_case_id"; sQuery += " JOIN mst_patient t3 ON t3.sz_patient_id = t2.sz_patient_id JOIN mst_case_type t4 ON t4.sz_case_type_id = t2.sz_case_type_id "; sQuery += " JOIN mst_office t7 ON t7.sz_office_id = t1.sz_reffering_office_id AND t1.sz_reffering_office_id IN (SELECT sz_office_id FROM @tvp_office) "; sQuery += " JOIN mst_doctor t6 ON t6.sz_doctor_id = t1.sz_reffering_doctor_id "; // add referring doctor only if selected if (p_lstReferringDoctor != null && p_lstReferringDoctor.Count > 0) { sQuery += "AND t1.sz_reffering_doctor_id IN (SELECT sz_doctor_id FROM @tvp_doctor) "; } sQuery += " JOIN mst_doctor t9 ON t9.sz_doctor_id = t1.sz_doctor_id AND t9.sz_doctor_id IN (select sz_doctor_id from txn_doctor_speciality"; sQuery += " WHERE txn_doctor_speciality.sz_procedure_group_id IN (SELECT sz_specialty_id FROM @tvp_specialty)"; sQuery += " AND sz_company_id = @sz_company_id) AND t9.sz_doctor_id IN (SELECT sz_doctor_id FROM mst_doctor WHERE sz_office_id IN (SELECT sz_provider_id FROM @tvp_provider)) "; sQuery += " LEFT JOIN mst_insurance_company t5 ON t5.sz_insurance_id = t2.sz_insurance_id"; sQuery += " LEFT JOIN txn_bill_transactions t8 on t8.sz_bill_number = t1.sz_bill_number "; //when procedure code is selected if (oDTProcedure != null && oDTProcedure.Rows.Count > 0) { sQuery += "JOIN txn_calender_event_prpcedure t10 ON t10.i_event_id = t1.i_event_id AND (t10.sz_proc_code IN (SELECT sz_procedure_id FROM @tvp_procedure))"; } sQuery += " WHERE t1.sz_company_id = @sz_company_id AND t1.sz_reffering_office_id IS NOT NULL "; sQuery += " AND t1.sz_reffering_office_id <> ''"; // when start date and end date is selected if (p_oAppointment.Date != null && p_oAppointment.LastVisitDate != null) { if (p_oAppointment.Date != String.Empty && p_oAppointment.LastVisitDate != String.Empty) { sQuery += " AND t1.dt_event_date BETWEEN @dt_visit_start AND @dt_visit_end"; } } sQuery += " AND (t5.sz_insurance_id IN (SELECT sz_insurance_id FROM @tvp_carrier)"; sQuery += " OR t5.sz_insurance_id IN (select sz_insurance_id from mst_insurance_groups where sz_company_id = @sz_company_id AND sz_group_name IN (select sz_name from @tvp_carriergroup)))"; sQuery += " ORDER BY t1.dt_event_date DESC "; SqlCommand com = new SqlCommand(sQuery, oConnection); com.Transaction = oTransaction; com.Parameters.AddWithValue("@sz_company_id", p_oAccount.ID); if (p_oAppointment.Date != null && p_oAppointment.LastVisitDate != null) { if (p_oAppointment.Date != String.Empty && p_oAppointment.LastVisitDate != String.Empty) { com.Parameters.AddWithValue("@dt_visit_start", p_oAppointment.Date); com.Parameters.AddWithValue("@dt_visit_end", p_oAppointment.LastVisitDate); } } if (oDTReferringDoctor != null && oDTReferringDoctor.Rows.Count > 0) { SqlParameter tvpParamPhysician = com.Parameters.AddWithValue( "@tvp_doctor", oDTReferringDoctor); tvpParamPhysician.SqlDbType = SqlDbType.Structured; tvpParamPhysician.TypeName = gbmodel.physician.type.TypePhysician.GetTypeName(); } if (oDTSpecialty != null && oDTSpecialty.Rows.Count > 0) { SqlParameter tvpParamSpecialty = com.Parameters.AddWithValue( "@tvp_specialty", oDTSpecialty); tvpParamSpecialty.SqlDbType = SqlDbType.Structured; tvpParamSpecialty.TypeName = gbmodel.specialty.type.TypeSpecialty.GetTypeName(); } if (oDTOffice != null && oDTOffice.Rows.Count > 0) { SqlParameter tvpParamOffice = com.Parameters.AddWithValue( "@tvp_office", oDTOffice); tvpParamOffice.SqlDbType = SqlDbType.Structured; tvpParamOffice.TypeName = gbmodel.office.type.TypeOffice.GetTypeName(); } if (oDTCarrier != null) { SqlParameter tvpParamCarrier = com.Parameters.AddWithValue( "@tvp_carrier", oDTCarrier); tvpParamCarrier.SqlDbType = SqlDbType.Structured; tvpParamCarrier.TypeName = gbmodel.carrier.type.TypeCarrier.GetTypeName(); } SqlParameter tvpParamcarriergroup = com.Parameters.AddWithValue( "@tvp_carriergroup", oDTCarrierGroup); tvpParamcarriergroup.SqlDbType = SqlDbType.Structured; tvpParamcarriergroup.TypeName = gbmodel.carriergroup.type.TypeCarrierGroup.GetTypeName(); if (p_oProcedure != null) { SqlParameter tvpParamProcedure = com.Parameters.AddWithValue( "@tvp_procedure", oDTProcedure); tvpParamProcedure.SqlDbType = SqlDbType.Structured; tvpParamProcedure.TypeName = gbmodel.procedure.type.TypeProcedure.GetTypeName(); } if (p_lstProvider != null) { SqlParameter tvpParamProcedure = com.Parameters.AddWithValue( "@tvp_provider", oDTProvider); tvpParamProcedure.SqlDbType = SqlDbType.Structured; tvpParamProcedure.TypeName = gbmodel.provider.type.TypeProvider.GetTypeName(); } SqlDataAdapter adapter = new SqlDataAdapter(com); adapter.Fill(ds); oTransaction.Commit(); if (ds != null) { if (ds.Tables[0] != null) { return(ds.Tables[0]); } } } catch (Exception x) { oTransaction.Rollback(); throw x; } finally { if (oConnection != null) { oConnection.Close(); oConnection = null; } } return(null); }
public DataSet IsSpecialtyNodeConfigured(DataTable dataTable, List <gbmodel.specialty.Specialty> p_oSpecialty, model.account.Account p_oAccount) { DataSet dataSet = null; SqlConnection oConnection = new SqlConnection(DBUtil.ConnectionString); oConnection.Open(); DataTable oDTProcedure = null; if (p_oSpecialty != null && p_oSpecialty.Count > 0) { oDTProcedure = gbmodel.specialty.type.TypeSpecialty.FillDBType(p_oSpecialty); } try { SqlCommand com = new SqlCommand(); com = new SqlCommand("sp_ifexists_specialty_node", oConnection); com.CommandType = CommandType.StoredProcedure; com.Parameters.AddWithValue("@tvp_specialty", oDTProcedure); com.Parameters.AddWithValue("@sz_process_name", "POM"); com.Parameters.AddWithValue("@sz_company_id", p_oAccount.ID); dataSet = new DataSet(); SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(com); sqlDataAdapter.Fill(dataSet); } catch (Exception ex) { } finally { if (oConnection.State == ConnectionState.Open) { oConnection.Close(); } } return(dataSet); }
public int DeleteVerificationDescription(gbmodel.bill.Bill p_oBill, gbmodel.bill.verification.Verification p_oVerification, model.account.Account p_oAccount) { string strConn = ConfigurationManager.AppSettings["Connection_String"].ToString(); SqlConnection sqlCon = new SqlConnection(strConn); int i = 0; try { sqlCon.Open(); SqlCommand sqlCmd = new SqlCommand("sp_delete_verification_received_details", sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue("@sz_bill_number", p_oBill.Number); sqlCmd.Parameters.AddWithValue("@i_verification_id", p_oVerification.Id); sqlCmd.Parameters.AddWithValue("@sz_company_id", p_oAccount.ID); i = sqlCmd.ExecuteNonQuery(); } catch (Exception ex) { } finally { if (sqlCon.State == ConnectionState.Open) { sqlCon.Close(); } } return(i); }
public DataSet GetBilVerificationlDetails(DataTable dataTable, List <gbmodel.bill.Bill> p_oBill, model.account.Account p_oAccount) { DataSet dataSet = null; SqlConnection oConnection = new SqlConnection(DBUtil.ConnectionString); oConnection.Open(); DataTable oDTBill = null; if (p_oBill != null && p_oBill.Count > 0) { oDTBill = gbmodel.bill.type.TypeBill.FillDBType(p_oBill); } try { SqlCommand sqlCmd = new SqlCommand("sp_get_verification_received_description", oConnection); sqlCmd.CommandType = CommandType.StoredProcedure; SqlParameter tvpParamPhysician = sqlCmd.Parameters.AddWithValue( "@typ_bill", oDTBill); sqlCmd.Parameters.AddWithValue("@sz_company_id", p_oAccount.ID); sqlCmd.ExecuteNonQuery(); SqlDataAdapter sqlda = new SqlDataAdapter(sqlCmd); dataSet = new DataSet(); sqlda.Fill(dataSet); return(dataSet); } catch (Exception ex) { return(null); } finally { if (oConnection.State == ConnectionState.Open) { oConnection.Close(); } } }