public DrugProduct Get(int id, string lang = "", string status = "") { drug = dbConnection.GetDrugProductById(id, lang, status); return(drug); }
public DrugProduct Get(int id, string lang) { _drug = dbConnection.GetDrugProductById(id, lang); return _drug; }
public List<DrugProduct> GetBySearchCriteria(string din, string brandname, string company, string lang) { var orderClause = ""; var items = new List<DrugProduct>(); string commandText = "SELECT DISTINCT D.DRUG_CODE, D.DRUG_IDENTIFICATION_NUMBER, D.NUMBER_OF_AIS, D.AI_GROUP_NO,"; commandText += " C.COMPANY_NAME, I.DOSAGE_VALUE, I.DOSAGE_UNIT, I.STRENGTH, "; if (lang.Equals("fr")) { commandText += " D.BRAND_NAME, D.BRAND_NAME_F, D.CLASS_F as CLASS, S.SCHEDULE_F as SCHEDULE, I.INGREDIENT_F as INGREDIENT,"; commandText += " I.STRENGTH_UNIT_F as STRENGTH_UNIT, EX.EXTERNAL_STATUS_FRENCH as EXTERNAL_STATUS, PM.PM_FRENCH_FNAME as PM_NAME"; commandText += ", CASE WHEN D.BRAND_NAME_F IS NOT NULL THEN UPPER(D.BRAND_NAME_F)"; commandText += " WHEN D.BRAND_NAME IS NOT NULL THEN upper(D.BRAND_NAME)"; commandText += " ELSE NULL END AS SORT_COLUMN"; } else { commandText += " D.BRAND_NAME, D.BRAND_NAME_F, D.CLASS, S.SCHEDULE, I.INGREDIENT, I.STRENGTH_UNIT, EX.EXTERNAL_STATUS_ENGLISH as EXTERNAL_STATUS, PM.PM_ENGLISH_FNAME as PM_NAME"; commandText += ", CASE WHEN D.BRAND_NAME IS NOT NULL THEN UPPER(D.BRAND_NAME)"; commandText += " WHEN D.BRAND_NAME_F IS NOT NULL THEN upper(D.BRAND_NAME_F)"; commandText += " ELSE NULL END AS SORT_COLUMN"; } commandText += " FROM DPD_ONLINE_OWNER.WQRY_DRUG_PRODUCT D"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_STATUS ST on D.DRUG_CODE = ST.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_STATUS_EXTERNAL EX on ST.EXTERNAL_STATUS_CODE = EX.EXTERNAL_STATUS_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_SCHEDULE S on D.DRUG_CODE = S.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_ACTIVE_INGREDIENTS I on D.DRUG_CODE = I.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_COMPANIES C ON D.COMPANY_CODE = C.COMPANY_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_PM_DRUG PM ON D.DRUG_CODE = PM.DRUG_CODE"; commandText += " WHERE"; commandText += " I.id = (select min(id) from DPD_ONLINE_OWNER.wqry_active_ingredients I where D.drug_code = I.drug_code) AND "; commandText += " ("; if (din != null) { commandText += " UPPER(D.DRUG_IDENTIFICATION_NUMBER) LIKE '%" + din.ToUpper() + "%'"; } if (brandname != null) { if (din != null) commandText += " OR"; commandText += " UPPER(D.BRAND_NAME_F) LIKE '%" + brandname.ToUpper() + "%'"; commandText += " OR UPPER(D.BRAND_NAME) LIKE '%" + brandname.ToUpper() + "%'"; } if (company != null) { if ((din != null) || (brandname != null)) commandText += " OR"; commandText += " UPPER(C.COMPANY_NAME) LIKE '%" + company.ToUpper() + "%'"; } commandText += ")"; if (lang.Equals("fr")) { orderClause += " translate(C.COMPANY_NAME,'ÀÂÄÇÈÉËÊÌÎÏÒÔÖÙÚÛÜ','AAACEEEEIIIOOOUUUU'), translate(D.BRAND_NAME_F,'ÀÂÄÇÈÉËÊÌÎÏÒÔÖÙÚÛÜ','AAACEEEEIIIOOOUUUU'),"; } else { orderClause += " translate(C.COMPANY_NAME,'ÀÂÄÇÈÉËÊÌÎÏÒÔÖÙÚÛÜ','AAACEEEEIIIOOOUUUU'), translate(D.BRAND_NAME,'ÀÂÄÇÈÉËÊÌÎÏÒÔÖÙÚÛÜ','AAACEEEEIIIOOOUUUU'),"; } using ( OracleConnection con = new OracleConnection(DpdDBConnection)) { OracleCommand cmd = new OracleCommand(commandText, con); try { con.Open(); using (OracleDataReader dr = cmd.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { var item = new DrugProduct(); if (lang.Equals("fr")) { item.brand_name = dr["BRAND_NAME_F"] == DBNull.Value ? dr["BRAND_NAME"].ToString().Trim() : dr["BRAND_NAME_F"].ToString().Trim(); } else { item.brand_name = dr["BRAND_NAME"] == DBNull.Value ? dr["BRAND_NAME_F"].ToString().Trim() : dr["BRAND_NAME"].ToString().Trim(); } item.class_name = dr["CLASS"] == DBNull.Value ? string.Empty : dr["CLASS"].ToString().Trim(); item.status_name = dr["EXTERNAL_STATUS"] == DBNull.Value ? string.Empty : dr["EXTERNAL_STATUS"].ToString().Trim(); item.schedule_name = dr["SCHEDULE"] == DBNull.Value ? string.Empty : dr["SCHEDULE"].ToString().Trim(); item.ai_name = dr["INGREDIENT"] == DBNull.Value ? string.Empty : dr["INGREDIENT"].ToString().Trim(); item.strength = dr["STRENGTH"] == DBNull.Value ? string.Empty : dr["STRENGTH"].ToString().Trim(); item.strength_unit_name = dr["STRENGTH_UNIT"] == DBNull.Value ? string.Empty : dr["STRENGTH_UNIT"].ToString().Trim(); item.dosage_value = dr["DOSAGE_VALUE"] == DBNull.Value ? string.Empty : dr["DOSAGE_VALUE"].ToString().Trim(); item.dosage_unit = dr["DOSAGE_UNIT"] == DBNull.Value ? string.Empty : dr["DOSAGE_UNIT"].ToString().Trim(); item.ai_group_no = dr["AI_GROUP_NO"] == DBNull.Value ? string.Empty : dr["AI_GROUP_NO"].ToString().Trim(); item.number_of_ais = dr["NUMBER_OF_AIS"] == DBNull.Value ? string.Empty : Convert.ToString(dr["NUMBER_OF_AIS"]); item.pm_name = dr["PM_NAME"] == DBNull.Value ? string.Empty : dr["PM_NAME"].ToString().Trim(); item.company_name = dr["COMPANY_NAME"] == DBNull.Value ? string.Empty : dr["COMPANY_NAME"].ToString().Trim(); item.drug_code = dr["DRUG_CODE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["DRUG_CODE"]); item.drug_identification_number = dr["DRUG_IDENTIFICATION_NUMBER"] == DBNull.Value ? string.Empty : dr["DRUG_IDENTIFICATION_NUMBER"].ToString().Trim(); items.Add(item); } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetBySearchCriteria()"); ExceptionHelper.LogException(ex, errorMessages); Console.WriteLine(errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return items; }
public List<DrugProduct> GetAllDrugProduct(string lang) { var orderClause = ""; var items = new List<DrugProduct>(); string commandText = "SELECT DISTINCT A.DRUG_CODE, A.DRUG_IDENTIFICATION_NUMBER, A.NUMBER_OF_AIS, A.AI_GROUP_NO,"; commandText += " B.COMPANY_NAME, E.DOSAGE_VALUE, E.DOSAGE_UNIT, E.STRENGTH, "; if (lang.Equals("fr")) { commandText += " A.BRAND_NAME, A.BRAND_NAME_F, A.CLASS_F as CLASS,D.SCHEDULE_F as SCHEDULE, E.INGREDIENT_F as INGREDIENT,"; commandText += " E.STRENGTH_UNIT_F as STRENGTH_UNIT, EX.EXTERNAL_STATUS_FRENCH as EXTERNAL_STATUS, pm.PM_FRENCH_FNAME as PM_NAME"; } else { commandText += " A.BRAND_NAME, A.BRAND_NAME_F, A.CLASS, D.SCHEDULE, E.INGREDIENT, E.STRENGTH_UNIT, EX.EXTERNAL_STATUS_ENGLISH as EXTERNAL_STATUS, pm.PM_ENGLISH_FNAME as PM_NAME"; } commandText += " FROM DPD_ONLINE_OWNER.WQRY_DRUG_PRODUCT A"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_STATUS C on A.DRUG_CODE = C.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_STATUS_EXTERNAL EX on C.EXTERNAL_STATUS_CODE = EX.EXTERNAL_STATUS_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_SCHEDULE D on A.DRUG_CODE = D.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_FORM F on A.DRUG_CODE = F.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_ROUTE R on A.DRUG_CODE = R.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_AHFS H on A.DRUG_CODE = H.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_ATC T on A.DRUG_CODE = T.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_ACTIVE_INGREDIENTS E on A.DRUG_CODE = E.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_COMPANIES B ON A.COMPANY_CODE = B.COMPANY_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_PM_DRUG pm ON A.DRUG_CODE = pm.DRUG_CODE"; commandText += " WHERE E.id = (select min(id) from DPD_ONLINE_OWNER.wqry_active_ingredients E where A.drug_code = E.drug_code)"; commandText += " ORDER BY" + orderClause + " A.DRUG_IDENTIFICATION_NUMBER"; using (OracleConnection con = new OracleConnection(DpdDBConnection)) { OracleCommand cmd = new OracleCommand(commandText, con); try { con.Open(); using (OracleDataReader dr = cmd.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { var item = new DrugProduct(); if (lang.Equals("fr")) { item.brand_name = dr["BRAND_NAME_F"] == DBNull.Value ? dr["BRAND_NAME"].ToString().Trim() : dr["BRAND_NAME_F"].ToString().Trim(); } else { item.brand_name = dr["BRAND_NAME"] == DBNull.Value ? dr["BRAND_NAME_F"].ToString().Trim() : dr["BRAND_NAME"].ToString().Trim(); } item.class_name = dr["CLASS"] == DBNull.Value ? string.Empty : dr["CLASS"].ToString().Trim(); item.status_name = dr["EXTERNAL_STATUS"] == DBNull.Value ? string.Empty : dr["EXTERNAL_STATUS"].ToString().Trim(); item.schedule_name = dr["SCHEDULE"] == DBNull.Value ? string.Empty : dr["SCHEDULE"].ToString().Trim(); item.ai_name = dr["INGREDIENT"] == DBNull.Value ? string.Empty : dr["INGREDIENT"].ToString().Trim(); item.strength = dr["STRENGTH"] == DBNull.Value ? string.Empty : dr["STRENGTH"].ToString().Trim(); item.strength_unit_name = dr["STRENGTH_UNIT"] == DBNull.Value ? string.Empty : dr["STRENGTH_UNIT"].ToString().Trim(); item.dosage_value = dr["DOSAGE_VALUE"] == DBNull.Value ? string.Empty : dr["DOSAGE_VALUE"].ToString().Trim(); item.dosage_unit = dr["DOSAGE_UNIT"] == DBNull.Value ? string.Empty : dr["DOSAGE_UNIT"].ToString().Trim(); item.ai_group_no = dr["AI_GROUP_NO"] == DBNull.Value ? string.Empty : dr["AI_GROUP_NO"].ToString().Trim(); item.number_of_ais = dr["NUMBER_OF_AIS"] == DBNull.Value ? string.Empty : Convert.ToString(dr["NUMBER_OF_AIS"]); item.pm_name = dr["PM_NAME"] == DBNull.Value ? string.Empty : dr["PM_NAME"].ToString().Trim(); item.company_name = dr["COMPANY_NAME"] == DBNull.Value ? string.Empty : dr["COMPANY_NAME"].ToString().Trim(); item.drug_code = dr["DRUG_CODE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["DRUG_CODE"]); item.drug_identification_number = dr["DRUG_IDENTIFICATION_NUMBER"] == DBNull.Value ? string.Empty : dr["DRUG_IDENTIFICATION_NUMBER"].ToString().Trim(); items.Add(item); } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetAllDrugProduct()"); ExceptionHelper.LogException(ex, errorMessages); Console.WriteLine(errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return items; }
public DrugProduct GetDrugProductById(int id, string lang) { var drugProduct = new DrugProduct(); string commandText = "SELECT A.DRUG_CODE, A.DRUG_IDENTIFICATION_NUMBER, A.NUMBER_OF_AIS, A.AI_GROUP_NO,"; commandText += " B.COMPANY_NAME, B.SUITE_NUMNER, B.CITY_NAME, B.POSTAL_CODE, C.ORIGINAL_MARKET_DATE, C.HISTORY_DATE, C.EXTERNAL_STATUS_CODE, E.DOSAGE_VALUE, E.DOSAGE_UNIT, E.STRENGTH, "; if (lang.Equals("fr")) { commandText += " A.BRAND_NAME, A.BRAND_NAME_F, A.CLASS_F as CLASS, B.STREET_NAME_F as STREET_NAME, B.PROVINCE_F as PROVINCE_NAME, B.COUNTRY_F as COUNTRY_NAME, EX.EXTERNAL_STATUS_FRENCH as STATUS, D.SCHEDULE_F as SCHEDULE, H.TC_AHFS_F AS AHFS, T.TC_ATC AS ATC, E.INGREDIENT_F as INGREDIENT,"; commandText += " E.STRENGTH_UNIT_F as STRENGTH_UNIT, F.PHARMACEUTICAL_FORM_F as FORM_NAME, pm.PM_FRENCH_FNAME as PM_NAME,"; commandText += " R.ROUTE_OF_ADMINISTRATION_F as ROUTE_NAME"; } else { commandText += " A.BRAND_NAME, A.BRAND_NAME_F, A.CLASS, B.STREET_NAME, B.PROVINCE as PROVINCE_NAME, B.COUNTRY as COUNTRY_NAME, EX.EXTERNAL_STATUS_ENGLISH as STATUS, D.SCHEDULE, F.PHARMACEUTICAL_FORM as FORM_NAME, H.TC_AHFS AS AHFS, T.TC_ATC AS ATC, E.INGREDIENT, E.STRENGTH_UNIT,"; commandText += " pm.PM_ENGLISH_FNAME as PM_NAME, R.ROUTE_OF_ADMINISTRATION as ROUTE_NAME"; } commandText += " FROM DPD_ONLINE_OWNER.WQRY_DRUG_PRODUCT A"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_STATUS C on A.DRUG_CODE = C.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_STATUS_EXTERNAL EX on C.EXTERNAL_STATUS_CODE = EX.EXTERNAL_STATUS_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_SCHEDULE D on A.DRUG_CODE = D.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_FORM F on A.DRUG_CODE = F.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_ROUTE R on A.DRUG_CODE = R.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_AHFS H on A.DRUG_CODE = H.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_ATC T on A.DRUG_CODE = T.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_ACTIVE_INGREDIENTS E on A.DRUG_CODE = E.DRUG_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_COMPANIES B ON A.COMPANY_CODE = B.COMPANY_CODE"; commandText += " LEFT OUTER JOIN DPD_ONLINE_OWNER.WQRY_PM_DRUG pm ON A.DRUG_CODE = pm.DRUG_CODE"; commandText += " WHERE E.id = (select min(id) from DPD_ONLINE_OWNER.wqry_active_ingredients E where A.drug_code = E.drug_code) AND A.DRUG_CODE = " + id; using (OracleConnection con = new OracleConnection(DpdDBConnection)) { OracleCommand cmd = new OracleCommand(commandText, con); try { con.Open(); using (OracleDataReader dr = cmd.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { var item = new DrugProduct(); if (lang.Equals("fr")) { item.brand_name = dr["BRAND_NAME_F"] == DBNull.Value ? dr["BRAND_NAME_F"].ToString().Trim() : dr["BRAND_NAME"].ToString().Trim(); } else { item.brand_name = dr["BRAND_NAME"] == DBNull.Value ? dr["BRAND_NAME"].ToString().Trim() : dr["BRAND_NAME_F"].ToString().Trim(); } item.class_name = dr["CLASS"] == DBNull.Value ? string.Empty : dr["CLASS"].ToString().Trim(); item.status_name = dr["STATUS"] == DBNull.Value ? string.Empty : dr["STATUS"].ToString().Trim(); item.schedule_name = dr["SCHEDULE"] == DBNull.Value ? string.Empty : dr["SCHEDULE"].ToString().Trim(); item.ai_name = dr["INGREDIENT"] == DBNull.Value ? string.Empty : dr["INGREDIENT"].ToString().Trim(); item.strength = dr["STRENGTH"] == DBNull.Value ? string.Empty : dr["STRENGTH"].ToString().Trim(); item.strength_unit_name = dr["STRENGTH_UNIT"] == DBNull.Value ? string.Empty : dr["STRENGTH_UNIT"].ToString().Trim(); item.ai_group_no = dr["AI_GROUP_NO"] == DBNull.Value ? string.Empty : dr["AI_GROUP_NO"].ToString().Trim(); item.number_of_ais = dr["NUMBER_OF_AIS"] == DBNull.Value ? string.Empty : Convert.ToString(dr["NUMBER_OF_AIS"]); item.company_name = dr["COMPANY_NAME"] == DBNull.Value ? string.Empty : dr["COMPANY_NAME"].ToString().Trim(); item.street_name = dr["STREET_NAME"] == DBNull.Value ? string.Empty : dr["STREET_NAME"].ToString().Trim(); item.city_name = dr["CITY_NAME"] == DBNull.Value ? string.Empty : dr["CITY_NAME"].ToString().Trim(); item.country_name = dr["COUNTRY_NAME"] == DBNull.Value ? string.Empty : dr["COUNTRY_NAME"].ToString().Trim(); item.province_name = dr["PROVINCE_NAME"] == DBNull.Value ? string.Empty : dr["PROVINCE_NAME"].ToString().Trim(); item.postal_code = dr["POSTAL_CODE"] == DBNull.Value ? string.Empty : dr["POSTAL_CODE"].ToString().Trim(); item.suite_number = dr["SUITE_NUMNER"] == DBNull.Value ? string.Empty : dr["SUITE_NUMNER"].ToString().Trim(); item.ahfs_name = dr["AHFS"] == DBNull.Value ? string.Empty : dr["AHFS"].ToString().Trim(); item.atc_name = dr["ATC"] == DBNull.Value ? string.Empty : dr["ATC"].ToString().Trim(); item.pm_name = dr["PM_NAME"] == DBNull.Value ? string.Empty : dr["PM_NAME"].ToString().Trim(); item.drug_code = dr["DRUG_CODE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["DRUG_CODE"]); item.drug_identification_number = dr["DRUG_IDENTIFICATION_NUMBER"] == DBNull.Value ? string.Empty : dr["DRUG_IDENTIFICATION_NUMBER"].ToString().Trim(); item.history_date = dr["HISTORY_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["HISTORY_DATE"]); item.external_status_code = dr["EXTERNAL_STATUS_CODE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["EXTERNAL_STATUS_CODE"]); item.original_market_date = dr["ORIGINAL_MARKET_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["ORIGINAL_MARKET_DATE"]); item.form_name = dr["FORM_NAME"] == DBNull.Value ? string.Empty : dr["FORM_NAME"].ToString().Trim(); item.route_name = dr["ROUTE_NAME"] == DBNull.Value ? string.Empty : dr["ROUTE_NAME"].ToString().Trim(); drugProduct = item; } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetDrugProductByDrugCode()"); ExceptionHelper.LogException(ex, errorMessages); Console.WriteLine(errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return drugProduct; }
public static DrugProduct GetDpdByID(string dpdID, string lang) { // CertifySSL.EnableTrustedHosts(); var item = new DrugProduct(); var json = string.Empty; var postData = new Dictionary<string, string>(); var dpdJsonUrlbyID = string.Format("{0}&id={1}&lang={2}", ConfigurationManager.AppSettings["dpdJsonUrl"].ToString(), dpdID, lang); try { using (var webClient = new System.Net.WebClient()) { webClient.Encoding = Encoding.UTF8; json = webClient.DownloadString(dpdJsonUrlbyID); if (!string.IsNullOrWhiteSpace(json)) { item = JsonConvert.DeserializeObject<DrugProduct>(json); } } } catch (Exception ex) { var errorMessages = string.Format("UtilityHelper - GetDrugProductByID()- Error Message:{0}", ex.Message); ExceptionHelper.LogException(ex, errorMessages); } finally { } return item; }