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);
}
public List <Entities.Company> SearchCompanyByName(string companyName)
{
var companies = new List <Entities.Company>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.SearchCompany))
{
database.AddInParameter(dbCommand, "@company_name", DbType.String, companyName);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var company = new Entities.Company
{
CompanyId = DRE.GetNullableInt32(reader, "company_id", 0),
CompanyCode = DRE.GetNullableString(reader, "company_code", null),
CompanyName = DRE.GetNullableString(reader, "company_name", null),
ShortName = DRE.GetNullableString(reader, "short_name", null),
CompanyAddress = DRE.GetNullableString(reader, "company_address", null),
CountryId = DRE.GetNullableInt32(reader, "country_id", null),
CountryName = DRE.GetNullableString(reader, "country_name", null),
StateId = DRE.GetNullableInt32(reader, "state_id", null),
StateName = DRE.GetNullableString(reader, "state_name", null),
CityId = DRE.GetNullableInt32(reader, "city_id", null),
CityName = DRE.GetNullableString(reader, "city_name", null),
PinCode = DRE.GetNullableString(reader, "pin_code", null),
Locality = DRE.GetNullableString(reader, "locality", null),
Website = DRE.GetNullableString(reader, "website", null),
GSTINNo = DRE.GetNullableString(reader, "gstin_no", null),
ContactPerson = DRE.GetNullableString(reader, "contact_person", null),
ContactNo = DRE.GetNullableString(reader, "contact_no", null),
EmailId = DRE.GetNullableString(reader, "email_id", null)
};
companies.Add(company);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(companies);
}
/// <summary>
///
/// </summary>
/// <param name="companyId"></param>
/// <returns></returns>
public Entities.Company GetCompanyDetailsById(Int32 companyId)
{
var companyDetails = new Entities.Company();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetCompanyDetailsById))
{
database.AddInParameter(dbCommand, "@company_id", DbType.Int32, companyId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var company = new Entities.Company
{
CompanyId = DRE.GetNullableInt32(reader, "company_id", 0),
CompanyCode = DRE.GetNullableString(reader, "company_code", null),
CompanyName = DRE.GetNullableString(reader, "company_name", null),
ShortName = DRE.GetNullableString(reader, "short_name", null),
CompanyAddress = DRE.GetNullableString(reader, "company_address", null),
CountryId = DRE.GetNullableInt32(reader, "country_id", null),
CountryName = DRE.GetNullableString(reader, "country_name", null),
StateId = DRE.GetNullableInt32(reader, "state_id", null),
StateName = DRE.GetNullableString(reader, "state_name", null),
CityId = DRE.GetNullableInt32(reader, "city_id", null),
CityName = DRE.GetNullableString(reader, "city_name", null),
Locality = DRE.GetNullableString(reader, "locality", null),
Website = DRE.GetNullableString(reader, "website", null),
GSTINNo = DRE.GetNullableString(reader, "gstin_no", null),
ContactPerson = DRE.GetNullableString(reader, "contact_person", null),
ContactNo = DRE.GetNullableString(reader, "contact_no", null),
EmailId = DRE.GetNullableString(reader, "email_id", null),
guid = DRE.GetNullableGuid(reader, "row_guid", null)
};
companyDetails = company;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(companyDetails);
}
/// <summary>
///
/// </summary>
/// <param name="clientAddressId"></param>
/// <returns></returns>
public Entities.ClientAddress GetClientAddressById(Int32 clientAddressId)
{
var clientAddress = new Entities.ClientAddress();
DbCommand dbCommand = null;
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetClientAddressById))
{
database.AddInParameter(dbCommand, "@client_address_id", DbType.Int32, clientAddressId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var _clientAddress = new Entities.ClientAddress
{
ClientTypeId = DRE.GetNullableInt32(reader, "client_type_id", null),
ClientType = DRE.GetNullableString(reader, "client_type", null),
ClientId = DRE.GetNullableInt32(reader, "client_id", 0),
ClientName = DRE.GetNullableString(reader, "client_name", null),
ClientAddressCode = DRE.GetNullableString(reader, "client_address_code", null),
AddressTypeId = DRE.GetNullableInt32(reader, "address_type_id", 0),
AddressType = DRE.GetNullableString(reader, "address_type", null),
ClientAddressId = DRE.GetNullableInt32(reader, "client_address_id", 0),
ClientAddressName = DRE.GetNullableString(reader, "client_address_name", null),
Address = DRE.GetNullableString(reader, "address", null),
CountryId = DRE.GetNullableInt32(reader, "country_id", null),
CountryName = DRE.GetNullableString(reader, "country_name", null),
StateId = DRE.GetNullableInt32(reader, "state_id", 0),
StateName = DRE.GetNullableString(reader, "state_name", null),
CityId = DRE.GetNullableInt32(reader, "city_id", 0),
CityName = DRE.GetNullableString(reader, "city_name", null),
Area = DRE.GetNullableString(reader, "area", null),
EmailId = DRE.GetNullableString(reader, "email_id", null),
ContactNos = DRE.GetNullableString(reader, "contact_nos", null),
GSTNo = DRE.GetNullableString(reader, "gst_no", null),
SrNo = DRE.GetNullableInt64(reader, "sr_no", null)
};
clientAddress = _clientAddress;
}
}
}
return(clientAddress);
}
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);
}
public List <Entities.PurchaseBillItem> GetPurchaseBillItemDetailsByPurchaseBillId(Int32 purchaseBillId)
{
var purchaseBillItems = new List <Entities.PurchaseBillItem>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetPurchaseBillItemDetailsByPurchaseBillId))
{
database.AddInParameter(dbCommand, "@purchase_bill_id", DbType.Int32, purchaseBillId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var purchaseBillItem = new Entities.PurchaseBillItem
{
PurchaseBillItemId = DRE.GetNullableInt32(reader, "purchase_bill_item_id", null),
PurchaseBillId = DRE.GetNullableInt32(reader, "purchase_bill_id", 0),
DrugId = DRE.GetNullableInt32(reader, "drug_id", null),
DrugCode = DRE.GetNullableInt32(reader, "drug_code", null),
DrugName = DRE.GetNullableString(reader, "drug_name", null),
GenericName = DRE.GetNullableString(reader, "generic_name", null),
BatchNo = DRE.GetNullableString(reader, "batch_no", null),
Pack1 = DRE.GetNullableDecimal(reader, "pack_1", null),
Pack2 = DRE.GetNullableDecimal(reader, "pack_2", null),
FreeQty = DRE.GetNullableDecimal(reader, "free_qty", null),
RatePerPack1 = DRE.GetNullableDecimal(reader, "rate_per_pack_1", null),
ExpiryDate = DRE.GetNullableString(reader, "expiry_date", null),
TaxPercent = DRE.GetNullableDecimal(reader, "tax_percent", null),
TaxAmount = DRE.GetNullableDecimal(reader, "tax_amount", null),
ItemAmount = DRE.GetNullableDecimal(reader, "item_amount", null)
};
purchaseBillItems.Add(purchaseBillItem);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(purchaseBillItems);
}
public List <Entities.Drug> SearchDrugsAll()
{
var drugs = new List <Entities.Drug>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.SearchDrugsAll))
{
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var drugLinkWithDrugRoutes = new DrugsLinkWithDrugRoute();
var drug = new Entities.Drug
{
DrugId = DRE.GetNullableInt32(reader, "drug_id", 0),
DrugOrXRay = DRE.GetNullableString(reader, "drug_or_xray", "D"),
DrugCode = DRE.GetNullableInt32(reader, "drug_code", null),
GenericName = DRE.GetNullableString(reader, "generic_name", null),
DrugName = DRE.GetNullableString(reader, "drug_name", null),
DrugGroupId = DRE.GetNullableInt32(reader, "drug_group_id", null),
BrandId = DRE.GetNullableInt32(reader, "brand_id", null),
DrugFormulationId = DRE.GetNullableInt32(reader, "drug_formulation_id", null),
DrugFormulationCode = DRE.GetNullableString(reader, "drug_formulation_code", null),
Strength = DRE.GetNullableString(reader, "strength", null),
Unit = DRE.GetNullableString(reader, "unit", null),
AdverseEffects = DRE.GetNullableString(reader, "adverse_effects", null),
Precautions = DRE.GetNullableString(reader, "precautions", null),
Remarks = DRE.GetNullableString(reader, "remarks", null),
DrugLinkWithDrugRoutes = drugLinkWithDrugRoutes.GetDrugLinkByDrugId(DRE.GetNullableInt32(reader, "drug_id", 0))
};
drugs.Add(drug);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(drugs);
}
/// <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.DrugDispenseDrugReturn> GetDrugDispenseDetailsByPatientId(Int32 patientId)
{
var drugDispenses = new List <Entities.DrugDispenseDrugReturn>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetDrugDispenseDetailsByPatientId))
{
database.AddInParameter(dbCommand, "@patient_id", DbType.Int32, patientId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var drugReturn = new Entities.DrugDispenseDrugReturn()
{
DrugDispenseDrugReturnId = DRE.GetNullableInt32(reader, "drug_dispense_drug_return_id", null),
DrugDispenseReturnId = DRE.GetNullableInt32(reader, "drug_dispense_return_id", null),
DrugUtilisationId = DRE.GetNullableInt32(reader, "drug_utilisation_id", null),
DrugDispenseId = DRE.GetNullableInt32(reader, "drug_dispense_id", null),
DrugDispenseNo = DRE.GetNullableInt32(reader, "drug_dispense_no", null),
DrugDispenseDate = DRE.GetNullableString(reader, "drug_dispense_date", null),
DrugId = DRE.GetNullableInt32(reader, "drug_id", null),
DrugCode = DRE.GetNullableInt32(reader, "drug_code", null),
DrugName = DRE.GetNullableString(reader, "drug_name", null),
DispenseQty = DRE.GetNullableDecimal(reader, "dispense_qty", null),
ReturnQty = DRE.GetNullableDecimal(reader, "return_qty", null),
BalanceQty = DRE.GetNullableDecimal(reader, "balance_qty", null),
Rate = DRE.GetNullableDecimal(reader, "purchase_rate", null),
Amount = DRE.GetNullableDecimal(reader, "amount", null)
};
drugDispenses.Add(drugReturn);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(drugDispenses);
}
/// <summary>
/// Gets list of menus by role id.
/// </summary>
/// <param name="roleId">Specifies the role id.</param>
/// <returns>A collection of menus.</returns>
public List <Entities.Menu> GetMenusByRoleId(Int32 roleId)
{
var menus = new List <Entities.Menu>();
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetListOfMenusByRole))
{
database.AddInParameter(dbCommand, "@role_id", DbType.Int32, roleId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var menu = new Entities.Menu
{
MenuId = DRE.GetNullableInt32(reader, "menu_id", 0),
MenuGroupId = DRE.GetNullableInt32(reader, "menu_group_id", 0),
MenuGroupName = DRE.GetNullableString(reader, "menu_group", null),
MenuName = DRE.GetNullableString(reader, "menu_name", null),
PageLink = DRE.GetNullableString(reader, "page_link", null),
MenuSequence = DRE.GetNullableDecimal(reader, "menu_sequence", 0),
MenuIcon = DRE.GetNullableString(reader, "menu_icon", null)
};
menus.Add(menu);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(menus);
}
/// <summary>
///
/// </summary>
/// <param name="employeeId"></param>
/// <returns></returns>
public Entities.Employee GetEmployeeDetailsById(Int32 employeeId)
{
var employee = new Entities.Employee();
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetEmployeeDetailsById)) {
database.AddInParameter(dbCommand, "@employee_id", DbType.Int32, employeeId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var _employee = new Entities.Employee
{
EmployeeId = DRE.GetNullableInt32(reader, "employee_id", 0),
EmployeeCode = DRE.GetNullableString(reader, "employee_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),
Gender = DRE.GetNullableString(reader, "gender", null),
Address = DRE.GetNullableString(reader, "address", null),
DateOfBirth = DRE.GetNullableString(reader, "date_of_birth", 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),
CompanyId = DRE.GetNullableInt32(reader, "company_id", null),
CompanyName = DRE.GetNullableString(reader, "company_name", null)
};
employee = _employee;
}
}
}
return(employee);
}
/// <summary>
///
/// </summary>
/// <param name="patientId"></param>
/// <returns></returns>
public Entities.Patient GetPatientDetailsById(Int32 patientId)
{
var patientInfo = new Entities.Patient();
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetPatientDetailsById)) {
database.AddInParameter(dbCommand, "@patient_id", DbType.Int32, patientId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
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),
Gender = DRE.GetNullableString(reader, "gender", null),
Address = DRE.GetNullableString(reader, "address", null),
DateOfBirth = DRE.GetNullableString(reader, "date_of_birth", 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)
};
patientInfo = patient;
}
}
}
return(patientInfo);
}
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.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);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public List <Entities.MedicalTestParameters> GetMedicalTestParameterDetailsByTestId(Int32 medicalTestId)
{
var medicalTestParameters = new List <Entities.MedicalTestParameters>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetMedicalTestParametersByTestId))
{
database.AddInParameter(dbCommand, "@medical_test_id", DbType.Int32, medicalTestId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var medicalTestParameter = new Entities.MedicalTestParameters()
{
MedicalTestParameterId = DRE.GetNullableInt32(reader, "medical_test_parameter_id", null),
MedicalTestId = DRE.GetNullableInt32(reader, "medical_test_id", 0),
TestParameterName = DRE.GetNullableString(reader, "test_parameter_name", null),
TestParameterDesc = DRE.GetNullableString(reader, "test_parameter_desc", null),
TestParameterSequence = DRE.GetNullableDecimal(reader, "test_parameter_sequence", null),
MinimumValue = DRE.GetNullableDecimal(reader, "minimum_value", null),
MaximumValue = DRE.GetNullableDecimal(reader, "maximum_value", null),
NormalValue = DRE.GetNullableDecimal(reader, "normal_value", null),
UnitOfMeasurementId = DRE.GetNullableInt32(reader, "unit_of_measurement_id", null),
UnitCode = DRE.GetNullableString(reader, "unit_code", null),
SrNo = DRE.GetNullableInt64(reader, "sr_no", null)
};
medicalTestParameters.Add(medicalTestParameter);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(medicalTestParameters);
}
/// <summary>
///
/// </summary>
/// <param name="workingPeriodId"></param>
/// <returns></returns>
public string GetFinancialYearById(Int32 workingPeriodId)
{
var financialYear = string.Empty;
DbCommand dbCommand = null;
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetListOfFinancialYearsById))
{
database.AddInParameter(dbCommand, "@working_period_id", DbType.Int32, workingPeriodId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
financialYear = DRE.GetNullableString(reader, "financial_year", null);
}
}
}
return(financialYear);
}
public List <Entities.State> GetStatesByCountry(Int32 countryId)
{
var states = new List <Entities.State>();
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetListOfStatesByCountryId))
{
database.AddInParameter(dbCommand, "@country_id", DbType.Int32, countryId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var state = new Entities.State
{
StateId = DRE.GetNullableInt32(reader, "state_id", 0),
StateName = DRE.GetNullableString(reader, "state_name", null),
StateCode = DRE.GetNullableString(reader, "state_code", null),
TINNo = DRE.GetNullableString(reader, "tin_no", null)
};
states.Add(state);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(states);
}
//public List<Entities.PrecautionsList> GetDrugUtilisationByDrugDispenseId(Int32 drugDispenseId)
//{
// ///var drugDetails = new PrecautionsList();
// return GetDrugUtilisationByDrugDispenseId(drugDispenseId);
//}
public List <Entities.PrecautionsList> GetDrugUtilisationByDrugDispenseId(Int32 drugDispenseId)
{
var drugDispenseDrugUtilisations = new List <Entities.PrecautionsList>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetDrugDispenseDrugUtilisationDetailsByDrugDispenseId))
{
database.AddInParameter(dbCommand, "@drug_dispense_id", DbType.Int32, drugDispenseId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var drugUtilisation = new Entities.PrecautionsList()
{
DrugUtilisationId = DRE.GetNullableInt32(reader, "drug_utilisation_id", null),
DrugDispenseId = DRE.GetNullableInt32(reader, "drug_dispense_id", null),
DrugId = DRE.GetNullableInt32(reader, "drug_id", null),
DrugCode = DRE.GetNullableInt32(reader, "drug_code", null),
DrugName = DRE.GetNullableString(reader, "drug_name", null),
DispenseQty = Convert.ToString(DRE.GetNullableDecimal(reader, "dispense_qty", null)),
BalanceQty = DRE.GetNullableDecimal(reader, "balance_qty", null),
PurchaseRate = DRE.GetNullableDecimal(reader, "purchase_rate", null),
Amount = DRE.GetNullableDecimal(reader, "amount", null)
};
drugDispenseDrugUtilisations.Add(drugUtilisation);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(drugDispenseDrugUtilisations);
}
public List <Entities.Employer> SearchEmployerByName(string employerName)
{
var employers = new List <Entities.Employer>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.SearchEmployerByName))
{
database.AddInParameter(dbCommand, "@employer_name", DbType.String, employerName);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var employer = new Entities.Employer
{
EmployerId = DRE.GetNullableInt32(reader, "employer_id", 0),
EmployerName = DRE.GetNullableString(reader, "employer_name", null),
EmployerAddress = DRE.GetNullableString(reader, "employer_address", null),
CountryName = DRE.GetNullableString(reader, "country_name", null),
StateName = DRE.GetNullableString(reader, "state_name", null),
CityName = DRE.GetNullableString(reader, "city_name", null),
PinCode = DRE.GetNullableString(reader, "pin_code", null),
Website = DRE.GetNullableString(reader, "website", null),
GSTINNo = DRE.GetNullableString(reader, "gstin_no", null)
};
employers.Add(employer);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(employers);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public List <Entities.WorkingPeriod> GetAllWorkingPeriods()
{
var workingPeriods = new List <Entities.WorkingPeriod>();
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetAllWorkingPeriods))
{
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var workingPeriod = new Entities.WorkingPeriod
{
WorkingPeriodId = DRE.GetNullableInt32(reader, "working_period_id", 0),
StartDate = DRE.GetNullableString(reader, "start_date", null),
EndDate = DRE.GetNullableString(reader, "end_date", null),
FinancialYear = DRE.GetNullableString(reader, "financial_year", null),
SrNo = DRE.GetNullableInt64(reader, "sr_no", null)
};
workingPeriods.Add(workingPeriod);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(workingPeriods);
}
public List <Entities.City> GetCitiesByState(Int32 stateId)
{
var cities = new List <Entities.City>();
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetListofCitiesByState))
{
database.AddInParameter(dbCommand, "@state_id", DbType.Int32, stateId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var city = new Entities.City
{
CityId = DRE.GetNullableInt32(reader, "city_id", 0),
CityName = DRE.GetNullableString(reader, "city_name", null),
SrNo = DRE.GetNullableInt64(reader, "sr_no", null)
};
cities.Add(city);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(cities);
}
public List <Entities.XRayFilmUsed> GetFilmUsedDetailsByXRayIssueId(Int32 xrayIssueId)
{
var xRayFilmsUsed = new List <Entities.XRayFilmUsed>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetXRayFilmsUsedDetailsByXRayIssueId))
{
database.AddInParameter(dbCommand, "@xray_issue_id", DbType.Int32, xrayIssueId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var xRayFilmUsed = new Entities.XRayFilmUsed()
{
XRayFilmUsedId = DRE.GetNullableInt32(reader, "xray_film_used_id", null),
XRayIssueId = DRE.GetNullableInt32(reader, "xray_issue_id", null),
DrugId = DRE.GetNullableInt32(reader, "drug_id", null),
DrugCode = DRE.GetNullableInt32(reader, "drug_code", null),
DrugName = DRE.GetNullableString(reader, "drug_name", null),
DispenseQty = DRE.GetNullableDecimal(reader, "dispense_qty", null),
BalanceQty = DRE.GetNullableDecimal(reader, "balance_qty", null),
PurchaseRate = DRE.GetNullableDecimal(reader, "purchase_rate", null),
Amount = DRE.GetNullableDecimal(reader, "amount", null)
};
xRayFilmsUsed.Add(xRayFilmUsed);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(xRayFilmsUsed);
}
/// <summary>
///
/// </summary>
/// <param name="clientAddressId"></param>
/// <returns></returns>
public List <Entities.ClientAddressContact> GetAllClientAddressContats(Int32 clientAddressId)
{
var clientAddressContacts = new List <Entities.ClientAddressContact>();
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetAllClientAddressContactsByClientAddressId))
{
database.AddInParameter(dbCommand, "@client_address_id", DbType.Int32, clientAddressId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var clientAddressContact = new Entities.ClientAddressContact
{
ContactId = DRE.GetNullableInt32(reader, "contact_id", 0),
ContactName = DRE.GetNullableString(reader, "contact_name", null)
};
clientAddressContacts.Add(clientAddressContact);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(clientAddressContacts);
}
/// <summary>
///
/// </summary>
/// <param name="clientAddressName"></param>
/// <returns></returns>
public List <Entities.ClientAddress> SearchClientAddressNameByClientAddressName(string clientAddressName)
{
var clientAddresses = new List <Entities.ClientAddress>();
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.SearchClientAddressNameByClientAddressName))
{
database.AddInParameter(dbCommand, "@client_address_name", DbType.String, clientAddressName);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var clientAddress = new Entities.ClientAddress
{
ClientAddressId = DRE.GetNullableInt32(reader, "client_address_id", 0),
ClientAddressName = DRE.GetNullableString(reader, "client_address_name", null)
};
clientAddresses.Add(clientAddress);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(clientAddresses);
}
/// <summary>
/// /
/// </summary>
/// <returns></returns>
public List <Entities.Pettycash> GetAllPettyCashDocno()
{
var Pettys = new List <Entities.Pettycash>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetListOfAllPettycase))
{
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var Petty = new Entities.Pettycash
{
PettycaseId = DRE.GetNullableInt32(reader, "pettycase_id", 0),
DocNo = DRE.GetNullableString(reader, "docno", null),
EntryDate = DRE.GetNullableString(reader, "Date", null),
DateRange = DRE.GetNullableString(reader, "daterange", null),
AccountHead = DRE.GetNullableString(reader, "account_head_name", null),
Supplier = DRE.GetNullableString(reader, "nameofsupplier", null),
VoucherNo = DRE.GetNullableString(reader, "voucherno", null),
Amount = DRE.GetNullableDecimal(reader, "Amount", 0),
guid = DRE.GetNullableGuid(reader, "row_guid", null),
SrNo = DRE.GetNullableInt64(reader, "sr_no", null)
};
Pettys.Add(Petty);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(Pettys);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public List <Entities.Branch> GetAllBranchNames()
{
var branches = new List <Entities.Branch>();
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetListOfAllBranches))
{
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var branch = new Entities.Branch
{
BranchId = DRE.GetNullableInt32(reader, "branch_id", 0),
BranchName = DRE.GetNullableString(reader, "branch_name", null),
guid = DRE.GetNullableGuid(reader, "row_guid", null),
SrNo = DRE.GetNullableInt64(reader, "sr_no", null)
};
branches.Add(branch);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(branches);
}
public List <Entities.Branch> GetAllBranchNamesByCompany(Int32 companyId)
{
var branches = new List <Entities.Branch>();
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetListOfAllBranchesByCompany))
{
database.AddInParameter(dbCommand, "@company_id", DbType.Int32, companyId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var branch = new Entities.Branch
{
BranchId = DRE.GetNullableInt32(reader, "branch_id", 0),
BranchName = DRE.GetNullableString(reader, "branch_name", null)
};
branches.Add(branch);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(branches);
}
public List <Entities.PurchaseBillReturnItem> GetPurchaseBillItemReturnDetailsByPurchaseBillReturnId(Int32 purchaseBillReturnId)
{
var purchaseBillItemsReturn = new List <Entities.PurchaseBillReturnItem>();
try
{
using (DbCommand dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetPurchaseBillItemReturnDetailsByReturnId))
{
database.AddInParameter(dbCommand, "@purchase_bill_return_id", DbType.Int32, purchaseBillReturnId);
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var purchaseBillItemReturn = new Entities.PurchaseBillReturnItem
{
PurchaseBillItemReturnId = DRE.GetNullableInt32(reader, "purchase_bill_item_return_id", null),
PurchaseBillItemId = DRE.GetNullableInt32(reader, "purchase_bill_item_id", null),
PurchaseBillId = DRE.GetNullableInt32(reader, "purchase_bill_id", 0),
DrugId = DRE.GetNullableInt32(reader, "drug_id", null),
DrugCode = DRE.GetNullableInt32(reader, "drug_code", null),
DrugName = DRE.GetNullableString(reader, "drug_name", null),
PurchaseQty = DRE.GetNullableDecimal(reader, "free_qty", null),
ReturnQty = DRE.GetNullableDecimal(reader, "rate_per_pack_1", null)
};
purchaseBillItemsReturn.Add(purchaseBillItemReturn);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(purchaseBillItemsReturn);
}
/// <summary>
/// Gets a list of all roles
/// </summary>
/// <returns>A collection of all roles.</returns>
public List <Entities.Role> GetAllRoles()
{
var roles = new List <Entities.Role>();
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetListOfAllRoles))
{
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var role = new Entities.Role
{
RoleId = DRE.GetNullableInt32(reader, "role_id", 0),
RoleName = DRE.GetNullableString(reader, "role_name", null),
RoleDesc = DRE.GetNullableString(reader, "role_desc", null),
SrNo = DRE.GetNullableInt64(reader, "sr_no", null)
};
roles.Add(role);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(roles);
}
public List <Entities.Country> GetAllCountries()
{
var countries = new List <Entities.Country>();
DbCommand dbCommand = null;
try
{
using (dbCommand = database.GetStoredProcCommand(DBStoredProcedure.GetListOfAllCountries))
{
using (IDataReader reader = database.ExecuteReader(dbCommand))
{
while (reader.Read())
{
var country = new Entities.Country
{
CountryId = DRE.GetNullableInt32(reader, "country_id", 0),
CountryName = DRE.GetNullableString(reader, "country_name", null),
CountryCode = DRE.GetNullableString(reader, "country_code", null)
};
countries.Add(country);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbCommand = null;
}
return(countries);
}
