public ProductLicence Get(int id, string lang) { _licence = dbConnection.GetProductLicenceById(id, lang); _licence.secondary_brand_name_list = dbConnection.GetSecondaryBrandNameList(id, lang); _licence.route_list = dbConnection.GetProductRouteByLicenceNumber(id, lang); _licence.dose_list = dbConnection.GetProductDoseByLicenceNumber(id, lang); _licence.purpose_list = dbConnection.GetProductPurposeByLicenceNumber(id, lang); _licence.risk_list = dbConnection.GetRiskByLicenceNumber(id, lang); _licence.medicinal_ingredient_list = dbConnection.GetMedIngredientByLicenceNumber(id, lang); _licence.non_medicinal_ingredient_list = dbConnection.GetNonMedIngredientByLicenceNumber(id, lang); return _licence; }
public List<ProductLicence> GetAllProductLicence(string lang) { var items = new List<ProductLicence>(); string commandText = "SELECT FILE_NUMBER, SUBMISSION_ID, LICENCE_NUMBER, LICENCE_DATE, REVISED_DATE, TIME_RECEIPT, DATE_START, NOTES, PRODUCT_NAME_ID, PRODUCT_NAME, COMPANY_ID, COMPANY_NAME_ID, COMPANY_NAME, SUB_SUBMISSION_TYPE_CODE, FLAG_PRIMARY_NAME, FLAG_PRODUCT_STATUS, FLAG_ATTESTED_MONOGRAPH, "; if (lang.Equals("fr")) { commandText += "DOSAGE_FORM_F as DOSAGE_FORM, SUB_SUBMISSION_TYPE_DESC_F as SUB_SUBMISSION_TYPE_DESC "; } else { commandText += "DOSAGE_FORM, SUB_SUBMISSION_TYPE_DESC "; } commandText += "FROM NHPPLQ_OWNER.PRODUCT_LICENCE_ONLINE"; using (OracleConnection con = new OracleConnection(LnhpdDBConnection)) { OracleCommand cmd = new OracleCommand(commandText, con); try { con.Open(); using (OracleDataReader dr = cmd.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { var item = new ProductLicence(); item.file_number = dr["FILE_NUMBER"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FILE_NUMBER"]); item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]); item.licence_number = dr["LICENCE_NUMBER"] == DBNull.Value ? string.Empty : dr["LICENCE_NUMBER"].ToString().Trim(); item.licence_date = dr["LICENCE_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["LICENCE_DATE"]); item.revised_date = dr["REVISED_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["REVISED_DATE"]); item.time_receipt = dr["TIME_RECEIPT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["TIME_RECEIPT"]); item.date_start = dr["DATE_START"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["DATE_START"]); item.notes = dr["NOTES"] == DBNull.Value ? string.Empty : dr["NOTES"].ToString().Trim(); ; item.product_name_id = dr["PRODUCT_NAME_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["PRODUCT_NAME_ID"]); item.product_name = dr["PRODUCT_NAME"] == DBNull.Value ? string.Empty : dr["PRODUCT_NAME"].ToString().Trim(); item.dosage_form = dr["DOSAGE_FORM"] == DBNull.Value ? string.Empty : dr["DOSAGE_FORM"].ToString().Trim(); item.company_id = dr["COMPANY_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["COMPANY_ID"]); item.company_name_id = dr["COMPANY_NAME_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["COMPANY_NAME_ID"]); item.company_name = dr["COMPANY_NAME"] == DBNull.Value ? string.Empty : dr["COMPANY_NAME"].ToString().Trim(); item.sub_submission_type_code = dr["SUB_SUBMISSION_TYPE_CODE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUB_SUBMISSION_TYPE_CODE"]); item.sub_submission_type_desc = dr["SUB_SUBMISSION_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["SUB_SUBMISSION_TYPE_DESC"].ToString().Trim(); item.flag_primary_name = dr["FLAG_PRIMARY_NAME"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FLAG_PRIMARY_NAME"]); item.flag_product_status = dr["FLAG_PRODUCT_STATUS"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FLAG_PRODUCT_STATUS"]); item.flag_attested_monograph = dr["FLAG_ATTESTED_MONOGRAPH"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FLAG_ATTESTED_MONOGRAPH"]); items.Add(item); } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetAllProductLicence()"); ExceptionHelper.LogException(ex, errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return items; }
public List<ProductLicence> GetAllProductByCriteria(string brandname, string ingredient, string companyname, string din, string lang) { var orderClause = ""; var items = new List<ProductLicence>(); string commandText = "SELECT FILE_NUMBER, SUBMISSION_ID, LICENCE_NUMBER, LICENCE_DATE, REVISED_DATE, TIME_RECEIPT, DATE_START, NOTES, PRODUCT_NAME_ID, PRODUCT_NAME, COMPANY_ID, COMPANY_NAME_ID, COMPANY_NAME, SUB_SUBMISSION_TYPE_CODE, FLAG_PRIMARY_NAME, FLAG_PRODUCT_STATUS, FLAG_ATTESTED_MONOGRAPH, "; if (lang.Equals("fr")) { commandText += "DOSAGE_FORM_F as DOSAGE_FORM, SUB_SUBMISSION_TYPE_DESC_F as SUB_SUBMISSION_TYPE_DESC "; } else { commandText += "DOSAGE_FORM, SUB_SUBMISSION_TYPE_DESC "; } commandText += "FROM NHPPLQ_OWNER.PRODUCT_LICENCE_ONLINE"; //commandText += " LEFT OUTER JOIN NHPPLQ_OWNER.INGREDIENT_SUBMISSION_ONLINE B ON A.SUBMISSION_ID = B.SUBMISSION_ID"; commandText += " WHERE ("; //commandText += " B.SUBMISSION_ID IN (SELECT b.SUBMISSION_ID FROM NHPPLQ_OWNER.INGREDIENT_SUBMISSION_ONLINE B WHERE A.SUBMISSION_ID = B.SUBMISSION_ID) AND "; //commandText += " ("; if (din != null) { commandText += " UPPER(LICENCE_NUMBER) LIKE '%" + din.ToUpper() + "%'"; } if (brandname != null) { if (din != null) commandText += " OR"; commandText += " UPPER(PRODUCT_NAME) LIKE '%" + brandname.ToUpper() + "%'"; } if (ingredient != null) { // commandText += " UPPER(DRUG_IDENTIFICATION_NUMBER) LIKE '%" + din.ToUpper() + "%'"; } if (companyname != null) { if ((din != null) || (brandname != null)) commandText += " OR"; commandText += " UPPER(COMPANY_NAME) LIKE '%" + companyname.ToUpper() + "%'"; } commandText += ")"; if (lang.Equals("fr")) { orderClause += " translate(COMPANY_NAME,'ÀÂÄÇÈÉËÊÌÎÏÒÔÖÙÚÛÜ','AAACEEEEIIIOOOUUUU'), translate(BRAND_NAME_F,'ÀÂÄÇÈÉËÊÌÎÏÒÔÖÙÚÛÜ','AAACEEEEIIIOOOUUUU'),"; } else { orderClause += " translate(COMPANY_NAME,'ÀÂÄÇÈÉËÊÌÎÏÒÔÖÙÚÛÜ','AAACEEEEIIIOOOUUUU'), translate(BRAND_NAME,'ÀÂÄÇÈÉËÊÌÎÏÒÔÖÙÚÛÜ','AAACEEEEIIIOOOUUUU'),"; } commandText += " ORDER BY LICENCE_NUMBER, PRODUCT_NAME"; using (OracleConnection con = new OracleConnection(LnhpdDBConnection)) { OracleCommand cmd = new OracleCommand(commandText, con); try { con.Open(); using (OracleDataReader dr = cmd.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { var item = new ProductLicence(); item.file_number = dr["FILE_NUMBER"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FILE_NUMBER"]); item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]); item.licence_number = dr["LICENCE_NUMBER"] == DBNull.Value ? string.Empty : dr["LICENCE_NUMBER"].ToString().Trim(); item.licence_date = dr["LICENCE_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["LICENCE_DATE"]); item.revised_date = dr["REVISED_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["REVISED_DATE"]); item.time_receipt = dr["TIME_RECEIPT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["TIME_RECEIPT"]); item.date_start = dr["DATE_START"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["DATE_START"]); item.notes = dr["NOTES"] == DBNull.Value ? string.Empty : dr["NOTES"].ToString().Trim(); ; item.product_name_id = dr["PRODUCT_NAME_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["PRODUCT_NAME_ID"]); item.product_name = dr["PRODUCT_NAME"] == DBNull.Value ? string.Empty : dr["PRODUCT_NAME"].ToString().Trim(); item.dosage_form = dr["DOSAGE_FORM"] == DBNull.Value ? string.Empty : dr["DOSAGE_FORM"].ToString().Trim(); item.company_id = dr["COMPANY_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["COMPANY_ID"]); item.company_name_id = dr["COMPANY_NAME_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["COMPANY_NAME_ID"]); item.company_name = dr["COMPANY_NAME"] == DBNull.Value ? string.Empty : dr["COMPANY_NAME"].ToString().Trim(); item.sub_submission_type_code = dr["SUB_SUBMISSION_TYPE_CODE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUB_SUBMISSION_TYPE_CODE"]); item.sub_submission_type_desc = dr["SUB_SUBMISSION_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["SUB_SUBMISSION_TYPE_DESC"].ToString().Trim(); item.flag_primary_name = dr["FLAG_PRIMARY_NAME"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FLAG_PRIMARY_NAME"]); item.flag_product_status = dr["FLAG_PRODUCT_STATUS"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FLAG_PRODUCT_STATUS"]); item.flag_attested_monograph = dr["FLAG_ATTESTED_MONOGRAPH"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FLAG_ATTESTED_MONOGRAPH"]); items.Add(item); } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetAllProductByCriteria()"); ExceptionHelper.LogException(ex, errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return items; }
public List<ProductLicence> GetAllProductBySingleTerm(string term, string lang) { var items = new List<ProductLicence>(); var items1 = new List<ProductLicence>(); var items2 = new List<ProductLicence>(); string commandText1 = "SELECT DISTINCT SUBMISSION_ID, LICENCE_NUMBER, PRODUCT_NAME, COMPANY_NAME, FLAG_PRIMARY_NAME, FLAG_PRODUCT_STATUS "; commandText1 += "FROM NHPPLQ_OWNER.PRODUCT_LICENCE_ONLINE "; commandText1 += "WHERE ("; if (term != null) { commandText1 += "UPPER(LICENCE_NUMBER) LIKE '%" + term.ToUpper() + "%' OR "; commandText1 += "UPPER(PRODUCT_NAME) LIKE '%" + term.ToUpper() + "%' OR "; commandText1 += "UPPER(COMPANY_NAME) LIKE '%" + term.ToUpper() + "%'"; } commandText1 += ")"; commandText1 += " ORDER BY LICENCE_NUMBER, PRODUCT_NAME"; string commandText2 = "SELECT DISTINCT SUBMISSION_ID, LICENCE_NUMBER, PRODUCT_NAME, COMPANY_NAME, FLAG_PRIMARY_NAME, FLAG_PRODUCT_STATUS "; commandText2 += "FROM NHPPLQ_OWNER.PRODUCT_LICENCE_ONLINE "; commandText2 += "WHERE SUBMISSION_ID IN ( "; commandText2 += "SELECT SUBMISSION_ID "; commandText2 += "FROM NHPPLQ_OWNER.INGREDIENT_SUBMISSION_ONLINE "; commandText2 += "WHERE "; if (term != null) { commandText2 += "(UPPER(NAME_UPPER) LIKE '%" + term.ToUpper() + "%') AND (INGREDIENT_TYPE_CODE = 2)"; } commandText2 += ") "; commandText2 += "AND FLAG_PRIMARY_NAME = 1 "; commandText2 += "ORDER BY LICENCE_NUMBER, PRODUCT_NAME"; using (OracleConnection con = new OracleConnection(LnhpdDBConnection)) { OracleCommand cmd1 = new OracleCommand(commandText1, con); OracleCommand cmd2 = new OracleCommand(commandText2, con); try { con.Open(); using (OracleDataReader dr = cmd1.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { var item1 = new ProductLicence(); item1.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]); item1.licence_number = dr["LICENCE_NUMBER"] == DBNull.Value ? string.Empty : dr["LICENCE_NUMBER"].ToString().Trim(); item1.product_name = dr["PRODUCT_NAME"] == DBNull.Value ? string.Empty : dr["PRODUCT_NAME"].ToString().Trim(); //item1.secondary_brand_name_list = GetSecondaryBrandNameList(Convert.ToInt32(item1.licence_number), lang); item1.company_name = dr["COMPANY_NAME"] == DBNull.Value ? string.Empty : dr["COMPANY_NAME"].ToString().Trim(); item1.flag_primary_name = dr["FLAG_PRIMARY_NAME"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FLAG_PRIMARY_NAME"]); item1.flag_product_status = dr["FLAG_PRODUCT_STATUS"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FLAG_PRODUCT_STATUS"]); items1.Add(item1); } } } using (OracleDataReader dr = cmd2.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { var item2 = new ProductLicence(); item2.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]); item2.licence_number = dr["LICENCE_NUMBER"] == DBNull.Value ? string.Empty : dr["LICENCE_NUMBER"].ToString().Trim(); item2.product_name = dr["PRODUCT_NAME"] == DBNull.Value ? string.Empty : dr["PRODUCT_NAME"].ToString().Trim(); //item2.secondary_brand_name_list = GetSecondaryBrandNameList(Convert.ToInt32(item2.LicenceNumber), lang); item2.flag_primary_name = dr["FLAG_PRIMARY_NAME"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FLAG_PRIMARY_NAME"]); item2.flag_product_status = dr["FLAG_PRODUCT_STATUS"] == DBNull.Value ? 0 : Convert.ToInt32(dr["FLAG_PRODUCT_STATUS"]); items2.Add(item2); } } } if (items2 != null && items2.Count > 0) { var mergedList = items1.Union(items2, new ProductComparer()); items = mergedList.ToList(); } else { items = items1; } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetAllProductByCriteria()"); ExceptionHelper.LogException(ex, errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return items; }
public static ProductLicence GetByID(string lnhpdID, string lang) { // CertifySSL.EnableTrustedHosts(); var item = new ProductLicence(); var json = string.Empty; var postData = new Dictionary<string, string>(); var lnhpdJsonUrlbyID = string.Format("{0}&id={1}&lang={2}", ConfigurationManager.AppSettings["lnhpdJsonUrl"].ToString(), lnhpdID, lang); try { using (var webClient = new System.Net.WebClient()) { json = webClient.DownloadString(lnhpdJsonUrlbyID); if (!string.IsNullOrWhiteSpace(json)) { item = JsonConvert.DeserializeObject<ProductLicence>(json); } } } catch (Exception ex) { var errorMessages = string.Format("UtilityHelper - GetDrugProductByID()- Error Message:{0}", ex.Message); ExceptionHelper.LogException(ex, errorMessages); } finally { } return item; }