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);
}
public List <DrugProduct> GetAllDrugProduct()
{
var items = new List <DrugProduct>();
//string commandText = "SELECT * FROM NOC_ONLINE_OWNER.QRY_NOC_BRAND";
string commandText = "SELECT A.NOC_DP_DIN_PRODUCT_ID, A.NOC_DP_DIN, B.* FROM QRY_NOC_DIN_PRODUCT A, QRY_NOC_BRAND B";
commandText += " WHERE A.NOC_DP_BRAND_ID = B.NOC_BR_BRAND_ID AND A.NOC_NUMBER = B.NOC_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.noc_number = dr["NOC_NUMBER"] == DBNull.Value ? 0 : Convert.ToInt32(dr["NOC_NUMBER"]);
// item.noc_br_brand_id = dr["NOC_BR_BRAND_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["NOC_BR_BRAND_ID"]);
// item.noc_br_brandname = dr["NOC_BR_BRANDNAME_FR"] == DBNull.Value ? string.Empty : dr["NOC_BR_BRANDNAME_FR"].ToString().Trim();
//}
//else
//{
item.noc_number = dr["NOC_NUMBER"] == DBNull.Value ? 0 : Convert.ToInt32(dr["NOC_NUMBER"]);
item.noc_br_brand_id = dr["NOC_BR_BRAND_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["NOC_BR_BRAND_ID"]);
item.noc_br_brandname = dr["NOC_BR_BRANDNAME"] == DBNull.Value ? string.Empty : dr["NOC_BR_BRANDNAME"].ToString().Trim();
item.noc_br_product_id = dr["NOC_DP_DIN_PRODUCT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["NOC_DP_DIN_PRODUCT_ID"]);
item.noc_br_din = dr["NOC_DP_DIN"] == DBNull.Value ? string.Empty : dr["NOC_DP_DIN"].ToString().Trim();
//}
items.Add(item);
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetAllDrugProduct()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
return(items);
}
public DrugProduct GetDrugProductByID(int id)
{
DrugProduct drugProduct = databasePlaceholder.Get(id);
if (drugProduct == null)
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
return(drugProduct);
}
public DrugProduct GetDrugProductById(int id)
{
var drugProduct = new DrugProduct();
string commandText = "SELECT * FROM CVPONL_OWNER.DRUG_PRODUCTS WHERE DRUG_PRODUCT_ID = " + id;
//using (SqlConnection con = new SqlConnection(DpdDBConnection))
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();
item.DrugProductId = dr["DRUG_PRODUCT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["DRUG_PRODUCT_ID"]);
item.ProductId = dr["PRODUCT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["PRODUCT_ID"]);
item.DrugName = dr["DRUGNAME"] == DBNull.Value ? string.Empty : dr["DRUGNAME"].ToString().Trim();
item.CpdFlag = dr["CPD_FLAG"] == DBNull.Value ? 0 : Convert.ToInt32(dr["CPD_FLAG"]);
drugProduct = item;
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetDrugProductById()");
ExceptionHelper.LogException(ex, errorMessages);
Console.WriteLine(errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
return(drugProduct);
}
public List <DrugProduct> GetDrugProductByDrugName(string drugName)
{
var items = new List <DrugProduct>();
string commandText = "SELECT DISTINCT DRUG_PRODUCT_ID, PRODUCT_ID, DRUGNAME, CPD_FLAG FROM CVPONL_OWNER.DRUG_PRODUCTS WHERE UPPER(DRUGNAME) LIKE '%" + drugName.ToUpper() + "%' ORDER BY DRUGNAME ASC";
//using (SqlConnection con = new SqlConnection(DpdDBConnection))
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();
item.DrugProductId = dr["DRUG_PRODUCT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["DRUG_PRODUCT_ID"]);
item.ProductId = dr["PRODUCT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["PRODUCT_ID"]);
item.DrugName = dr["DRUGNAME"] == DBNull.Value ? string.Empty : dr["DRUGNAME"].ToString().Trim();
item.CpdFlag = dr["CPD_FLAG"] == DBNull.Value ? 0 : Convert.ToInt32(dr["CPD_FLAG"]);
items.Add(item);
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetDrugProductByName()");
ExceptionHelper.LogException(ex, errorMessages);
Console.WriteLine(errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
return(items);
}
public DrugProduct GetDrugProductById(int id)
{
var drugproduct = new DrugProduct();
string commandText = "SELECT A.CTA_PROTOCOL_ID, A.SUBMISSION_NO, A.BRAND_ID, A.MANUFACTURER_ID, C.MANUFACTURER_NAME, A.BRAND_NAME_FR AS BRAND_NAME_FR, A.BRAND_NAME_EN AS BRAND_NAME_EN ";
//if (this.Lang.Equals("fr"))
//{
// commandText += " A.BRAND_NAME_FR AS BRAND_NAME ";
//}
//else
//{
// commandText += " A.BRAND_NAME_EN AS BRAND_NAME ";
//}
commandText += "FROM CTA_OWNER.PRODUCT_BRAND A, CTA_OWNER.MANUFACTURER C WHERE A.MANUFACTURER_ID = C.MANUFACTURER_ID(+) AND A.BRAND_ID = :id ";
using (OracleConnection con = new OracleConnection(CtDBConnection))
{
OracleCommand cmd = new OracleCommand(commandText, con);
cmd.Parameters.Add(":id", id);
try
{
con.Open();
using (OracleDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
drugproduct.protocol_id = dr["CTA_PROTOCOL_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["CTA_PROTOCOL_ID"]);
drugproduct.submission_no = dr["SUBMISSION_NO"] == DBNull.Value ? string.Empty : dr["SUBMISSION_NO"].ToString().Trim();
drugproduct.brand_id = dr["BRAND_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["BRAND_ID"]);
drugproduct.manufacturer_id = dr["MANUFACTURER_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MANUFACTURER_ID"]);
//drugproduct.brand_name = dr["BRAND_NAME"] == DBNull.Value ? string.Empty : dr["BRAND_NAME"].ToString().Trim();
drugproduct.manufacturer_name = dr["MANUFACTURER_NAME"] == DBNull.Value ? string.Empty : dr["MANUFACTURER_NAME"].ToString().Trim();
if (this.Lang != null && this.Lang.Equals("fr"))
{
drugproduct.brand_name = dr["BRAND_NAME_FR"] == DBNull.Value ? dr["BRAND_NAME_EN"].ToString().Trim() : dr["BRAND_NAME_FR"].ToString().Trim();
}
else
{
drugproduct.brand_name = dr["BRAND_NAME_EN"] == DBNull.Value ? dr["BRAND_NAME_FR"].ToString().Trim() : dr["BRAND_NAME_EN"].ToString().Trim();
}
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetProductBrandById()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
return(drugproduct);
}//END - 3 Product Brand 20170205
