public List<Device> GetAllDevice(string status, string deviceName, int licenceId)
{
var items = new List<Device>();
string commandText = "SELECT DISTINCT D.* FROM PUB_ACS.PAS_LICENCE_DEVICE D";
if ((!string.IsNullOrEmpty(status)) || (licenceId > 0))
{
commandText += " , PUB_ACS.PAS_LICENCE L WHERE D.ORIGINAL_LICENCE_NO = L.ORIGINAL_LICENCE_NO";
} else if (!string.IsNullOrEmpty(deviceName))
{
commandText += " WHERE";
}
if (!string.IsNullOrEmpty(status))
{
if (status.Equals("active"))
{
commandText += " AND L.END_DATE IS NULL";
}
}
if (!string.IsNullOrEmpty(deviceName))
{
if (!string.IsNullOrEmpty(status)) commandText += " AND";
commandText += " D.TRADE_NAME LIKE '%" + deviceName.ToUpper().Trim() + "%'";
}
else if(licenceId > 0)
{
commandText += " AND L.ORIGINAL_LICENCE_NO = " + licenceId;
}
commandText += " ORDER BY UPPER(D.TRADE_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 Device();
item.deviceIdentifierList = new List<DeviceIdentifier>();
item.original_licence_no = dr["ORIGINAL_LICENCE_NO"] == DBNull.Value ? 0 : Convert.ToInt32(dr["ORIGINAL_LICENCE_NO"]);
item.device_id = dr["DEVICE_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["DEVICE_ID"]);
item.device_first_issue_dt = dr["FIRST_LICENCE_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["FIRST_LICENCE_DT"]);
item.end_date = dr["END_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["END_DATE"]);
item.trade_name = dr["TRADE_NAME"] == DBNull.Value ? string.Empty : dr["TRADE_NAME"].ToString().Trim();
var deviceIdentifierList = GetAllDeviceIdentifier("", 0, item.device_id);
if(deviceIdentifierList != null && deviceIdentifierList.Count > 0)
{
item.deviceIdentifierList = deviceIdentifierList;
}
items.Add(item);
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetAllLicenceDevice()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return items;
}
public Device GetDeviceById(int id)
{
var device = new Device();
string commandText = "SELECT * FROM PUB_ACS.PAS_LICENCE_DEVICE WHERE DEVICE_ID = " + 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 Device();
item.original_licence_no = dr["ORIGINAL_LICENCE_NO"] == DBNull.Value ? 0 : Convert.ToInt32(dr["ORIGINAL_LICENCE_NO"]);
item.device_id = dr["DEVICE_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["DEVICE_ID"]);
item.device_first_issue_dt = dr["FIRST_LICENCE_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["FIRST_LICENCE_DT"]);
item.end_date = dr["END_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["END_DATE"]);
item.trade_name = dr["TRADE_NAME"] == DBNull.Value ? string.Empty : dr["TRADE_NAME"].ToString().Trim();
device = item;
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetLicenceDevice()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return device;
}
public Device Get(int id)
{
_device = dbConnection.GetDeviceById(id);
return _device;
}