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