public LicenceCompany GetLicenceCompanyById(string lang, Int64 licence_id, string status)
{
var licence = new LicenceCompany();
string commandText = "SELECT L.ORIGINAL_LICENCE_NO, L.LICENCE_STATUS, L.APPLICATION_ID, L.APPL_RISK_CLASS, L.LICENCE_NAME, L.FIRST_LICENCE_STATUS_DT, L.LAST_REFRESH_DT, L.END_DATE, L.LICENCE_TYPE_CD, ";
commandText += " C.COMPANY_NAME, C.ADDR_LINE_1, C.ADDR_LINE_2, C.ADDR_LINE_3, C.POSTAL_CODE, C.CITY, C.COUNTRY_CD, C.REGION_CD, C.COMPANY_STATUS, C.COMPANY_ID";
commandText += " T.LICENCE_TYPE_CD";
if (lang.Equals("fr"))
{
commandText += " T.LICENCE_TYPE_DESC_F AS LICENCE_TYPE_DESC";
}
else {
commandText += " T.LICENCE_TYPE_DESC_E AS LICENCE_TYPE_DESC";
}
commandText += " FROM PUB_ACS.PAS_LICENCE L, PUB_ACS.PAS_LICENCE_COMPANY C, PUB_ACS.PAS_LICENCE_TYPE T";
commandText += " WHERE L.COMPANY_ID = C.COMPANY_ID AND L.LICENCE_TYPE_CD = T.LICENCE_TYPE_CD";
if (!string.IsNullOrEmpty(status))
{
if (status.Equals("active"))
{
commandText += " AND L.END_DATE IS NULL";
}
}
//if(companyId > 0)
//{
// commandText += " AND L.COMPANY_ID = " + companyId;
//}
if (licence_id > 0)
{
commandText += " AND L.ORIGINAL_LICENCE_NO = " + licence_id;
}
using (
OracleConnection con = new OracleConnection(MdallDBConnection))
{
OracleCommand cmd = new OracleCommand(commandText, con);
try
{
con.Open();
using (OracleDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
var item = new LicenceCompany();
item.original_licence_no = dr["ORIGINAL_LICENCE_NO"] == DBNull.Value ? 0 : Convert.ToInt32(dr["ORIGINAL_LICENCE_NO"]);
item.licence_status = dr["LICENCE_STATUS"] == DBNull.Value ? string.Empty : dr["LICENCE_STATUS"].ToString().Trim();
item.application_id = dr["APPLICATION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["APPLICATION_ID"]);
item.appl_risk_class = dr["APPL_RISK_CLASS"] == DBNull.Value ? 0 : Convert.ToInt32(dr["APPL_RISK_CLASS"]);
item.licence_name = dr["LICENCE_NAME"] == DBNull.Value ? string.Empty : dr["LICENCE_NAME"].ToString().Trim();
item.first_licence_status_dt = dr["FIRST_LICENCE_STATUS_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["FIRST_LICENCE_STATUS_DT"]);
item.last_refresh_dt = dr["LAST_REFRESH_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["LAST_REFRESH_DT"]);
item.end_date = dr["END_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["END_DATE"]);
item.licence_type_cd = dr["LICENCE_TYPE_CD"] == DBNull.Value ? string.Empty : dr["LICENCE_TYPE_CD"].ToString().Trim();
item.licence_type_desc = dr["LICENCE_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["LICENCE_TYPE_DESC"].ToString().Trim();
item.company_id = dr["COMPANY_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["COMPANY_ID"]);
item.company_name = dr["COMPANY_NAME"] == DBNull.Value ? string.Empty : dr["COMPANY_NAME"].ToString().Trim();
item.addr_line_1 = dr["ADDR_LINE_1"] == DBNull.Value ? string.Empty : dr["ADDR_LINE_1"].ToString().Trim();
item.addr_line_2 = dr["ADDR_LINE_2"] == DBNull.Value ? string.Empty : dr["ADDR_LINE_2"].ToString().Trim();
item.addr_line_3 = dr["ADDR_LINE_3"] == DBNull.Value ? string.Empty : dr["ADDR_LINE_3"].ToString().Trim();
item.postal_code = dr["POSTAL_CODE"] == DBNull.Value ? string.Empty : dr["POSTAL_CODE"].ToString().Trim();
item.city = dr["CITY"] == DBNull.Value ? string.Empty : dr["CITY"].ToString().Trim();
item.country_cd = dr["COUNTRY_CD"] == DBNull.Value ? string.Empty : dr["COUNTRY_CD"].ToString().Trim();
item.region_cd = dr["REGION_CD"] == DBNull.Value ? string.Empty : dr["REGION_CD"].ToString().Trim();
item.company_status = dr["COMPANY_STATUS"] == DBNull.Value ? string.Empty : dr["COMPANY_STATUS"].ToString().Trim();
licence = item;
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetLicenceById()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return licence;
}
public LicenceCompany Get(string lang, long licence_id, string status = "")
{
_licencecompany = dbConnection.GetLicenceCompanyById(lang, licence_id, status);
return _licencecompany;
}
public List<LicenceCompany> GetLicenceCompanyByCriteria(string lang, string status, string licenceName, Int64 licenceNumber, string companyName, int companyId)
{
var items = new List<LicenceCompany>();
string commandText = "SELECT DISTINCT L.ORIGINAL_LICENCE_NO, L.LICENCE_STATUS, L.APPLICATION_ID, L.APPL_RISK_CLASS, L.LICENCE_NAME, L.FIRST_LICENCE_STATUS_DT, L.LAST_REFRESH_DT, L.END_DATE, L.LICENCE_TYPE_CD, ";
commandText += " C.COMPANY_NAME, C.ADDR_LINE_1, C.ADDR_LINE_2, C.ADDR_LINE_3, C.POSTAL_CODE, C.CITY, C.COUNTRY_CD, C.REGION_CD, C.COMPANY_STATUS, C.COMPANY_ID";
//commandText += " T.LICENCE_TYPE_CD,";
//if (lang.Equals("fr"))
//{
// commandText += " T.LICENCE_TYPE_DESC_F AS LICENCE_TYPE_DESC";
//}
//else {
// commandText += " T.LICENCE_TYPE_DESC_E AS LICENCE_TYPE_DESC";
//}
commandText += " FROM PUB_ACS.PAS_LICENCE L";
commandText += " LEFT OUTER JOIN PUB_ACS.PAS_LICENCE_COMPANY C ON L.COMPANY_ID = C.COMPANY_ID";
//commandText += " LEFT OUTER JOIN PUB_ACS.PAS_LICENCE_TYPE T ON L.LICENCE_TYPE_CD = T.LICENCE_TYPE_CD";
commandText += " WHERE";
if (!string.IsNullOrEmpty(status)) {
if (status.Equals("active"))
{
commandText += " L.END_DATE IS NULL";
}
}
if ((!string.IsNullOrEmpty(status)) && (!string.IsNullOrEmpty(licenceName)) || (licenceNumber > 0) || (!string.IsNullOrEmpty(companyName)) || (companyId > 0))
{
commandText += " AND";
}
if (!string.IsNullOrEmpty(licenceName))
{
commandText += " UPPER(L.LICENCE_NAME) LIKE '%" + licenceName.ToUpper().Trim() + "%'";
commandText += " ORDER BY UPPER(L.LICENCE_NAME)";
}
if (licenceNumber > 0)
{
commandText += " UPPER(L.ORIGINAL_LICENCE_NO) LIKE '%" + licenceNumber + "%'";
}
if (!string.IsNullOrEmpty(companyName))
{
commandText += " UPPER(C.COMPANY_NAME) LIKE '%" + companyName.ToUpper().Trim() + "%'";
commandText += " ORDER BY UPPER(L.LICENCE_NAME)";
}
if (companyId > 0)
{
commandText += " UPPER(L.COMPANY_ID) LIKE '%" + companyId + "%'";
commandText += " ORDER BY UPPER(L.LICENCE_NAME)";
}
using (OracleConnection con = new OracleConnection(MdallDBConnection))
{
OracleCommand cmd = new OracleCommand(commandText, con);
try
{
con.Open();
using (OracleDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
var item = new LicenceCompany();
item.original_licence_no = dr["ORIGINAL_LICENCE_NO"] == DBNull.Value ? 0 : Convert.ToInt32(dr["ORIGINAL_LICENCE_NO"]);
item.licence_status = dr["LICENCE_STATUS"] == DBNull.Value ? string.Empty : dr["LICENCE_STATUS"].ToString().Trim();
item.application_id = dr["APPLICATION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["APPLICATION_ID"]);
item.appl_risk_class = dr["APPL_RISK_CLASS"] == DBNull.Value ? 0 : Convert.ToInt32(dr["APPL_RISK_CLASS"]);
item.licence_name = dr["LICENCE_NAME"] == DBNull.Value ? string.Empty : dr["LICENCE_NAME"].ToString().Trim();
item.first_licence_status_dt = dr["FIRST_LICENCE_STATUS_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["FIRST_LICENCE_STATUS_DT"]);
item.last_refresh_dt = dr["LAST_REFRESH_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["LAST_REFRESH_DT"]);
item.end_date = dr["END_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["END_DATE"]);
//item.licence_type_cd = dr["LICENCE_TYPE_CD"] == DBNull.Value ? string.Empty : dr["LICENCE_TYPE_CD"].ToString().Trim();
//item.licence_type_desc = dr["LICENCE_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["LICENCE_TYPE_DESC"].ToString().Trim();
item.company_id = dr["COMPANY_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["COMPANY_ID"]);
item.company_name = dr["COMPANY_NAME"] == DBNull.Value ? string.Empty : dr["COMPANY_NAME"].ToString().Trim();
item.addr_line_1 = dr["ADDR_LINE_1"] == DBNull.Value ? string.Empty : dr["ADDR_LINE_1"].ToString().Trim();
item.addr_line_2 = dr["ADDR_LINE_2"] == DBNull.Value ? string.Empty : dr["ADDR_LINE_2"].ToString().Trim();
item.addr_line_3 = dr["ADDR_LINE_3"] == DBNull.Value ? string.Empty : dr["ADDR_LINE_3"].ToString().Trim();
item.postal_code = dr["POSTAL_CODE"] == DBNull.Value ? string.Empty : dr["POSTAL_CODE"].ToString().Trim();
item.city = dr["CITY"] == DBNull.Value ? string.Empty : dr["CITY"].ToString().Trim();
item.country_cd = dr["COUNTRY_CD"] == DBNull.Value ? string.Empty : dr["COUNTRY_CD"].ToString().Trim();
item.region_cd = dr["REGION_CD"] == DBNull.Value ? string.Empty : dr["REGION_CD"].ToString().Trim();
item.company_status = dr["COMPANY_STATUS"] == DBNull.Value ? string.Empty : dr["COMPANY_STATUS"].ToString().Trim();
items.Add(item);
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetAllLicence()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return items;
}
public LicenceCompany Get(string lang, long licence_id, string state = "")
{
_licencecompany = dbConnection.GetLicenceCompanyById(lang, licence_id, state);
return(_licencecompany);
}
