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