public bool IsCityNameExists(Int32 stateId, string cityName)
{
var isCityNameExists = false;
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.CheckCityNameIsExists))
{
database.AddInParameter(dbCommand, "@state_id", DbType.Int32, stateId);
database.AddInParameter(dbCommand, "@city_name", DbType.String, cityName);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
isCityNameExists = Convert.ToBoolean(DRE.GetInt32(reader, "is_city_name_exists"));
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(isCityNameExists);
}
public List <Entities.DrugDispenseReturn> SearchDrguDispenseReturn(Entities.DrugDispenseReturn drugDispenseReturn)
{
var drugDispenseReturns = new List <Entities.DrugDispenseReturn>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.SearchDrugDispenseReturn))
{
database.AddInParameter(dbCommand, "@financial_year", DbType.String, drugDispenseReturn.FinancialYear);
database.AddInParameter(dbCommand, "@first_name", DbType.String, drugDispenseReturn.FirstName);
database.AddInParameter(dbCommand, "@last_name", DbType.String, drugDispenseReturn.LastName);
database.AddInParameter(dbCommand, "@drug_return_from_date", DbType.String, drugDispenseReturn.DrugReturnFromDate);
database.AddInParameter(dbCommand, "@drug_return_to_date", DbType.String, drugDispenseReturn.DrugReturnToDate);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var drugReturn = new DataModel.DrugDispenseDrugReturn();
var drugDispenseDetails = new Entities.DrugDispenseReturn()
{
DrugDispenseReturnId = DRE.GetNullableInt32(reader, "drug_dispense_return_id", null),
DrugDispenseId = DRE.GetNullableInt32(reader, "drug_dispense_id", 0),
DrugReturnNo = DRE.GetNullableInt32(reader, "drug_return_no", null),
DrugReturnDate = DRE.GetNullableString(reader, "drug_return_date", null),
PatientId = DRE.GetNullableInt32(reader, "patient_id", null),
PatientCode = DRE.GetNullableInt32(reader, "patient_code", null),
PatientName = DRE.GetNullableString(reader, "full_name", null),
EmployerCode = DRE.GetNullableInt32(reader, "employer_code", null),
EmployerName = DRE.GetNullableString(reader, "employer_name", null),
WorkingPeriodId = DRE.GetNullableInt32(reader, "working_period_id", null),
FinancialYear = DRE.GetNullableString(reader, "financial_year", null),
DrugDispenseDrugReturns = drugReturn.GetDrugReturnDetailsByDrugDispenseReturnId(DRE.GetInt32(reader, "drug_dispense_return_id"))
};
drugDispenseReturns.Add(drugDispenseDetails);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(drugDispenseReturns);
}
private List <Entities.PurchaseBill> GetPurchaseBills(IDataReader reader)
{
var purchaseBills = new List <Entities.PurchaseBill>();
while (reader.Read())
{
var purchaseBillItem = new PurchaseBillItem();
var purchaseBill = new Entities.PurchaseBill
{
PurchaseBillId = DRE.GetNullableInt32(reader, "purchase_bill_id", 0),
PurchaseBillNo = DRE.GetNullableString(reader, "purchase_bill_no", null),
PurchaseBillDate = DRE.GetNullableString(reader, "purchase_bill_date", null),
VendorId = DRE.GetNullableInt32(reader, "vendor_id", null),
VendorName = DRE.GetNullableString(reader, "vendor_name", null),
PurchaseBillAmount = DRE.GetNullableDecimal(reader, "purchase_bill_amount", 0),
AdjustedAmount = DRE.GetNullableDecimal(reader, "adjusted_amount", 0),
TotalBillQty = DRE.GetNullableDecimal(reader, "total_bill_qty", 0),
TotalBillAmount = DRE.GetNullableDecimal(reader, "total_bill_amount", 0),
Remarks = DRE.GetNullableString(reader, "remarks", null),
WorkingPeriodId = DRE.GetNullableInt32(reader, "working_period_id", null),
FinancialYear = DRE.GetNullableString(reader, "financial_year", null),
PurchaseBillItems = purchaseBillItem.GetPurchaseBillItemDetailsByPurchaseBillId(DRE.GetInt32(reader, "purchase_bill_id"))
};
purchaseBills.Add(purchaseBill);
}
return(purchaseBills);
}
public List <Entities.PurchaseBillReturn> SearchPurchaseBillsReturnAll(Entities.PurchaseBillReturn purchaseBillReturn)
{
var purchaseBillReturns = new List <Entities.PurchaseBillReturn>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.SearchPurchaseBillsReturn))
{
database.AddInParameter(dbCommand, "@purchase_bill_no", DbType.String, purchaseBillReturn.PurchaseBillNo);
database.AddInParameter(dbCommand, "@working_period_id", DbType.Int32, purchaseBillReturn.WorkingPeriodId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var purchaseBillReturnItem = new PurchaseBillReturnItem();
var purchaseBillReturnInfo = new Entities.PurchaseBillReturn()
{
PurchaseBillReturnId = DRE.GetNullableInt32(reader, "purchase_bill_return_id", null),
PurchaseBillId = DRE.GetNullableInt32(reader, "purchase_bill_id", null),
PurchaseBillNo = DRE.GetNullableString(reader, "purchase_bill_no", null),
PurchaseBillDate = DRE.GetNullableString(reader, "purchase_bill_date", null),
VendorName = DRE.GetNullableString(reader, "vendor_name", null),
PurchaseBillReturnDate = DRE.GetNullableString(reader, "purchase_bill_return_date", null),
WorkingPeriodId = DRE.GetNullableInt32(reader, "working_period_id", null),
PurchaseBillReturnItems = purchaseBillReturnItem.GetPurchaseBillItemReturnDetailsByPurchaseBillReturnId(DRE.GetInt32(reader, "purchase_bill_return_id"))
};
purchaseBillReturns.Add(purchaseBillReturn);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(purchaseBillReturns);
}
public List <Entities.PreEmploymentDetails> SearchPreEmploymentDetails(Entities.PreEmploymentDetails preEmploymentDetails)
{
var preEmploymentDetailsList = new List <Entities.PreEmploymentDetails>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.SearchPreEmploymentDetails))
{
database.AddInParameter(dbCommand, "@pre_employment_or_health_checkup", DbType.String, preEmploymentDetails.PreEmploymentOrHealthCheckup);
database.AddInParameter(dbCommand, "@financial_year", DbType.String, preEmploymentDetails.FinancialYear);
database.AddInParameter(dbCommand, "@first_name", DbType.String, preEmploymentDetails.FirstName);
database.AddInParameter(dbCommand, "@last_name", DbType.String, preEmploymentDetails.LastName);
database.AddInParameter(dbCommand, "@consult_from_date", DbType.String, preEmploymentDetails.ConsultFromDate);
database.AddInParameter(dbCommand, "@consult_to_date", DbType.String, preEmploymentDetails.ConsultToDate);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var preEmploymentTestDetails = new DataModel.PreEmploymentTestDetails();
var preEmployment = new Entities.PreEmploymentDetails()
{
PreEmploymentId = DRE.GetNullableInt32(reader, "pre_employment_id", 0),
PreEmploymentCodeNo = DRE.GetNullableInt32(reader, "pre_employment_code_no", null),
PreEmploymentOrHealthCheckup = DRE.GetNullableString(reader, "pre_employment_or_health_checkup", null),
PatientId = DRE.GetNullableInt32(reader, "patient_id", null),
PatientCode = DRE.GetNullableInt32(reader, "patient_code", null),
PatientFullName = DRE.GetNullableString(reader, "full_name", null),
Gender = DRE.GetNullableString(reader, "gender", null),
Age = DRE.GetNullableInt32(reader, "age", null),
ConsultDate = DRE.GetNullableString(reader, "consult_date", null),
MaritalStatus = DRE.GetNullableString(reader, "marital_status", null),
NoOfSons = DRE.GetNullableInt32(reader, "no_of_sons", null),
NoOfDaughters = DRE.GetNullableInt32(reader, "no_of_daughters", null),
EmployerId = DRE.GetNullableInt32(reader, "employer_id", null),
EmployerCode = DRE.GetNullableInt32(reader, "employer_code", null),
EmployerName = DRE.GetNullableString(reader, "employer_name", null),
Designation = DRE.GetNullableString(reader, "designation", null),
IdentificationMark = DRE.GetNullableString(reader, "identification_mark", null),
AllergicTo = DRE.GetNullableString(reader, "allergic_to", null),
Micturation = DRE.GetNullableString(reader, "micturation", null),
Bowels = DRE.GetNullableString(reader, "bowels", null),
Sleep = DRE.GetNullableString(reader, "sleep", null),
Alcohol = DRE.GetNullableString(reader, "alcohol", null),
Smoking = DRE.GetNullableString(reader, "smoking", null),
MC = DRE.GetNullableString(reader, "mc", null),
PastHistory = DRE.GetNullableString(reader, "past_history", null),
FamilyHistory = DRE.GetNullableString(reader, "family_history", null),
OtherDetails = DRE.GetNullableString(reader, "other_details", null),
Remarks = DRE.GetNullableString(reader, "remarks", null),
WorkingPeriodId = DRE.GetNullableInt32(reader, "working_period_id", null),
FinancialYear = DRE.GetNullableString(reader, "financial_year", null),
PreEmploymentTestDetails = preEmploymentTestDetails.GetPreEmploymentTestDetailsByPreEmploymentId(DRE.GetInt32(reader, "pre_employment_id"))
};
preEmploymentDetailsList.Add(preEmployment);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(preEmploymentDetailsList);
}
private List <Entities.Patient> GetPatients(IDataReader reader)
{
var patients = new List <Entities.Patient>();
while (reader.Read())
{
PatientPersonalHistory patientPersonalHistory = new PatientPersonalHistory();
PatientExerciseHistory patientExerciseHistory = new PatientExerciseHistory();
var patient = new Entities.Patient
{
PatientId = DRE.GetNullableInt32(reader, "patient_id", 0),
PatientCode = DRE.GetNullableInt32(reader, "patient_code", null),
Title = DRE.GetNullableString(reader, "title", null),
FirstName = DRE.GetNullableString(reader, "first_name", null),
MiddleName = DRE.GetNullableString(reader, "middle_name", null),
LastName = DRE.GetNullableString(reader, "last_name", null),
FullName = DRE.GetNullableString(reader, "full_name", null),
Address = DRE.GetNullableString(reader, "address", null),
Gender = DRE.GetNullableString(reader, "gender", null),
DateOfBirth = DRE.GetNullableString(reader, "date_of_birth", null),
ContactNos = DRE.GetNullableString(reader, "contact_nos", null),
ContactNo1 = DRE.GetNullableString(reader, "contact_no_1", null),
ContactNo2 = DRE.GetNullableString(reader, "contact_no_2", null),
MobileNo1 = DRE.GetNullableString(reader, "mobile_no_1", null),
MobileNo2 = DRE.GetNullableString(reader, "mobile_no_2", null),
EmailId = DRE.GetNullableString(reader, "email_id", null),
PANNo = DRE.GetNullableString(reader, "pan_no", null),
Department = DRE.GetNullableString(reader, "department", null),
Designation = DRE.GetNullableString(reader, "designation", null),
EmployerId = DRE.GetNullableInt32(reader, "employer_id", null),
EmployerName = DRE.GetNullableString(reader, "employer_name", null),
PatientPersonalHistory = patientPersonalHistory.GetPatientPersonalHistoriesByPatientId(DRE.GetInt32(reader, "patient_id")),
PatientExerciseHistories = patientExerciseHistory.GetPatientExerciseHistoriesByPatientId(DRE.GetInt32(reader, "patient_id"))
};
patients.Add(patient);
}
return(patients);
}
public List <Entities.XRayIssue> SearchXRayIssue(Entities.XRayIssue xrayIssue)
{
var xRayIssues = new List <Entities.XRayIssue>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.SearchXRayIssue))
{
database.AddInParameter(dbCommand, "@financial_year", DbType.String, xrayIssue.FinancialYear);
database.AddInParameter(dbCommand, "@first_name", DbType.String, xrayIssue.FirstName);
database.AddInParameter(dbCommand, "@last_name", DbType.String, xrayIssue.LastName);
database.AddInParameter(dbCommand, "@xray_issue_from_date", DbType.String, xrayIssue.XRayIssueFromDate);
database.AddInParameter(dbCommand, "@xray_issue_to_date", DbType.String, xrayIssue.XRayIssueToDate);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var xrayFilmUsed = new DataModel.XRayFilmUsed();
var xRayIssue = new Entities.XRayIssue()
{
XRayIssueId = DRE.GetNullableInt32(reader, "xray_issue_id", 0),
XRayIssueNo = DRE.GetNullableInt32(reader, "xray_issue_no", null),
XRayIssueDate = DRE.GetNullableString(reader, "xray_issue_date", null),
PartOfBodyToXRay = DRE.GetNullableString(reader, "part_of_body_to_xray", null),
IsECGDone = DRE.GetNullableBoolean(reader, "is_ecg_done", null),
Purpose = DRE.GetNullableString(reader, "purpose", null),
Impression = DRE.GetNullableString(reader, "impression", null),
PatientId = DRE.GetNullableInt32(reader, "patient_id", null),
PatientCode = DRE.GetNullableInt32(reader, "patient_code", null),
PatientName = DRE.GetNullableString(reader, "full_name", null),
EmployerId = DRE.GetNullableInt32(reader, "employer_id", null),
EmployerCode = DRE.GetNullableInt32(reader, "employer_code", null),
EmployerName = DRE.GetNullableString(reader, "employer_name", null),
WorkingPeriodId = DRE.GetNullableInt32(reader, "working_period_id", null),
FinancialYear = DRE.GetNullableString(reader, "financial_year", null),
XRayFilmsUsed = xrayFilmUsed.GetFilmUsedDetailsByXRayIssueId(DRE.GetInt32(reader, "xray_issue_id"))
};
xRayIssues.Add(xRayIssue);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(xRayIssues);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public List <Entities.Client> GetAllClients()
{
var clients = new List <Entities.Client>();
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetAllClients))
{
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
ClientAddress clientAddress = new ClientAddress();
var client = new Entities.Client
{
ClientTypeId = DRE.GetNullableInt32(reader, "client_type_id", 0),
ClientTypeName = DRE.GetNullableString(reader, "client_type", null),
ClientId = DRE.GetNullableInt32(reader, "client_id", 0),
ClientCode = DRE.GetNullableString(reader, "client_code", null),
ClientName = DRE.GetNullableString(reader, "client_name", null),
PANNo = DRE.GetNullableString(reader, "pan_no", null),
SrNo = DRE.GetNullableInt64(reader, "sr_no", null),
ClientAddressess = clientAddress.GetAllClientAddressessByClientId(DRE.GetInt32(reader, "client_id"))
};
clients.Add(client);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(clients);
}
public List <Entities.Precautions> SearchDrguDispense(Entities.Precautions prescription)
{
var drugDispenses = new List <Entities.Precautions>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.SearchPrescription))
{
database.AddInParameter(dbCommand, "@patient_name", DbType.String, prescription.PatientName);
database.AddInParameter(dbCommand, "@employer_name", DbType.String, prescription.EmployerName);
database.AddInParameter(dbCommand, "@patient_code", DbType.Int32, prescription.PatientCode);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var drugUtlisation = new DataModel.DrugDispenseDrugUtilisation();
var drugDispenseDetails = new Entities.Precautions()
{
DrugDispenseId = DRE.GetNullableInt32(reader, "Precautions_id", 0),
DrugDispenseNo = DRE.GetNullableInt32(reader, "prescription_no", null),
DrugDispenseDate = DRE.GetNullableString(reader, "prescription_date", null),
PatientId = DRE.GetNullableInt32(reader, "patient_id", null),
PatientCode = DRE.GetNullableInt32(reader, "patient_code", null),
PatientName = DRE.GetNullableString(reader, "full_name", null),
EmployerId = DRE.GetNullableInt32(reader, "employer_id", null),
EmployerCode = DRE.GetNullableInt32(reader, "employer_code", null),
EmployerName = DRE.GetNullableString(reader, "employer_name", null),
DoctName = DRE.GetNullableString(reader, "DocName", null),
WorkingPeriodId = DRE.GetNullableInt32(reader, "working_period_id", null),
DrugDispenseDrugUtilisations = GetUtilisationByDrugDispenseId(DRE.GetInt32(reader, "Precautions_id"))
};
drugDispenses.Add(drugDispenseDetails);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(drugDispenses);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public List <Entities.MedicalTest> SearchAllMedicalTests()
{
var medicalTests = new List <Entities.MedicalTest>();
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.SearchMedicalTestAll))
{
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
MedicalTestParameters medicalTestParameters = new MedicalTestParameters();
var medicalTest = new Entities.MedicalTest
{
MedicalTestId = DRE.GetNullableInt32(reader, "medical_test_id", 0),
TestName = DRE.GetNullableString(reader, "medical_test_name", null),
IsParameters = DRE.GetNullableBoolean(reader, "is_parameters", null),
IsGeneralTest = DRE.GetNullableBoolean(reader, "is_test_general", null),
MedicalTestParameters = medicalTestParameters.GetMedicalTestParameterDetailsByTestId(DRE.GetInt32(reader, "medical_test_id"))
};
medicalTests.Add(medicalTest);
}
}
}
return(medicalTests);
}
