public Company Get(int id, string lang)
{
DBConnection dbConnection = new DBConnection(lang);
company = dbConnection.GetCompanyById(id);
//company.licenceList = new List<Licence>();
//if (company != null && company.company_id > 0)
//{
// var licenceList = dbConnection.GetAllLicenceByCompanyId(company.company_id, "active");
// if (licenceList != null && licenceList.Count > 0)
// {
// company.licenceList = licenceList;
// foreach (var licence in company.licenceList)
// {
// licence.deviceList = new List<Device>();
// //Get Device
// var deviceList = dbConnection.GetAllDevice("", "", licence.original_licence_no);
// if (deviceList != null && deviceList.Count > 0)
// {
// licence.deviceList = deviceList;
// }
// }
// }
//}
return company;
}
public ActionResult GetCompanyByIDForJson(int id,string lang)
{
var companyController = new CompanyController();
var licenceController = new LicenceController();
var deviceController = new DeviceController();
var data = new CompanyDetail();
data.licenceList = new List<Licence>();
var licenceDetail = new LicenceDetail();
var company = new Company();
var deviceList = new List<Device>();
company = companyController.GetCompanyById(id, lang);
if (company != null && company.company_id > 0)
{
var address = new StringBuilder();
data.company_id = company.company_id;
data.company_name = company.company_name;
address.AppendFormat(company.addr_line_1).Append(" ");
address.Append(company.addr_line_2).Append(" ");
address.Append(company.addr_line_3).Append(",");
address.Append(company.city).Append(",");
address.Append(company.region_cd).Append(",");
address.Append(company.country_cd).Append(",");
address.Append(company.postal_code);
data.company_address = address.ToString();
data.licenceList = licenceController.GetAllLicenceByCompanyId(company.company_id, "active").ToList();
if (data.licenceList != null && data.licenceList.Count > 0)
{
//Get Device
foreach (var licence in data.licenceList)
{
licence.deviceList = new List<Device>();
//Get Device
deviceList = deviceController.GetAllDevice("", "", licence.original_licence_no).ToList();
if (deviceList != null && deviceList.Count > 0)
{
licence.deviceList = deviceList;
}
}
}
}
return Json(new { data }, JsonRequestBehavior.AllowGet);
}
public ActionResult GetAllListForJsonByCategory(string lang, string status, string term, int categoryType)
{
var companyResult = new List<Company>();
var companyController = new CompanyController();
switch (categoryType)
{
case (int)category.company:
companyResult = companyController.GetAllCompany(status, term).ToList();
return Json(new { companyResult }, JsonRequestBehavior.AllowGet);
case (int)category.licence:
var searchResult = new List<Search>();
var licenceResult = new List<Licence>();
var licenceController = new LicenceController();
licenceResult = licenceController.GetAllLicence(status, term).ToList();
if (licenceResult.Count > 0)
{
foreach (var licence in licenceResult)
{
var search = new Search();
var company = new Company();
var address = new StringBuilder();
company = companyController.GetCompanyById(licence.company_id, lang);
search.original_licence_no = licence.original_licence_no;
search.licence_status = licence.licence_status;
search.application_id = licence.application_id;
search.company_id = licence.company_id;
search.licence_name = licence.licence_name;
if (company != null && company.company_id > 0)
{
search.company_name = company.company_name;
address.AppendFormat(company.addr_line_1).Append(" ");
address.Append(company.addr_line_2).Append(" ");
address.Append(company.addr_line_3).AppendLine();
address.Append(company.city).Append(",");
address.Append(company.region_cd).Append(",");
address.Append(company.country_cd).Append(",");
address.Append(company.postal_code);
search.company_address = address.ToString();
}
searchResult.Add(search);
}
}
return Json(new { searchResult }, JsonRequestBehavior.AllowGet);
}
return Json(new { companyResult }, JsonRequestBehavior.AllowGet);
}
public Company GetCompanyById(int id)
{
var company = new Company();
string commandText = "SELECT * FROM PUB_ACS.PAS_LICENCE_COMPANY WHERE COMPANY_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 Company();
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();
company = item;
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetLicenceCompanyById()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return company;
}
public List<Company> GetAllCompany(string status, string companyName)
{
var items = new List<Company>();
string commandText = "SELECT DISTINCT C.* FROM PUB_ACS.PAS_LICENCE_COMPANY C";
if ((!string.IsNullOrEmpty(status)) || (!string.IsNullOrEmpty(companyName))) commandText += " , PUB_ACS.PAS_LICENCE L WHERE ";
if(!string.IsNullOrEmpty(status))
{
if (status.Equals("active"))
{
commandText += " C.COMPANY_ID = L.COMPANY_ID AND";
commandText += " L.END_DATE IS NULL";
}
}
if (!string.IsNullOrEmpty(companyName))
{
if (!string.IsNullOrEmpty(status)) commandText += " AND";
commandText += " UPPER(C.COMPANY_NAME) LIKE '%" + companyName.ToUpper().Trim() + "%'";
}
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 Company();
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 - GetAllLicenceCompany()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return items;
}
