public PhysicalPersonLicence Delete(Guid identifier)
{
PhysicalPersonLicence dbEntry = context.PhysicalPersonLicences
.Union(context.ChangeTracker.Entries()
.Where(x => x.State == EntityState.Added && x.Entity.GetType() == typeof(PhysicalPersonLicence))
.Select(x => x.Entity as PhysicalPersonLicence))
.FirstOrDefault(x => x.Identifier == identifier);
if (dbEntry != null)
{
dbEntry.Active = false;
dbEntry.UpdatedAt = DateTime.Now;
}
return(dbEntry);
}
public PhysicalPersonLicence Create(PhysicalPersonLicence PhysicalPersonLicence)
{
if (context.PhysicalPersonLicences.Where(x => x.Identifier != null && x.Identifier == PhysicalPersonLicence.Identifier).Count() == 0)
{
PhysicalPersonLicence.Id = 0;
PhysicalPersonLicence.Active = true;
PhysicalPersonLicence.UpdatedAt = DateTime.Now;
PhysicalPersonLicence.CreatedAt = DateTime.Now;
context.PhysicalPersonLicences.Add(PhysicalPersonLicence);
return(PhysicalPersonLicence);
}
else
{
// Load item that will be updated
PhysicalPersonLicence dbEntry = context.PhysicalPersonLicences
.FirstOrDefault(x => x.Identifier == PhysicalPersonLicence.Identifier && x.Active == true);
if (dbEntry != null)
{
dbEntry.LicenceId = PhysicalPersonLicence.LicenceId ?? null;
dbEntry.CountryId = PhysicalPersonLicence.CountryId ?? null;
dbEntry.CompanyId = PhysicalPersonLicence.CompanyId ?? null;
dbEntry.CreatedById = PhysicalPersonLicence.CreatedById ?? null;
dbEntry.ValidFrom = PhysicalPersonLicence.ValidFrom;
dbEntry.ValidTo = PhysicalPersonLicence.ValidTo;
dbEntry.ItemStatus = PhysicalPersonLicence.ItemStatus;
// Set timestamp
dbEntry.UpdatedAt = DateTime.Now;
}
return(dbEntry);
}
}
public static PhysicalPersonLicence ConvertToPhysicalPersonLicence(this PhysicalPersonLicenceViewModel PhysicalPersonItemViewModel)
{
PhysicalPersonLicence PhysicalPersonItem = new PhysicalPersonLicence()
{
Id = PhysicalPersonItemViewModel.Id,
Identifier = PhysicalPersonItemViewModel.Identifier,
PhysicalPersonId = PhysicalPersonItemViewModel.PhysicalPerson?.Id ?? null,
LicenceId = PhysicalPersonItemViewModel.Licence?.Id ?? null,
CountryId = PhysicalPersonItemViewModel.Country?.Id ?? null,
ValidFrom = PhysicalPersonItemViewModel.ValidFrom,
ValidTo = PhysicalPersonItemViewModel.ValidTo,
ItemStatus = PhysicalPersonItemViewModel.ItemStatus,
CreatedById = PhysicalPersonItemViewModel.CreatedBy?.Id ?? null,
CompanyId = PhysicalPersonItemViewModel.Company?.Id ?? null,
CreatedAt = PhysicalPersonItemViewModel.CreatedAt,
UpdatedAt = PhysicalPersonItemViewModel.UpdatedAt
};
return(PhysicalPersonItem);
}
public List <PhysicalPersonLicence> GetPhysicalPersonItems(int companyId)
{
List <PhysicalPersonLicence> PhysicalPersonLicences = new List <PhysicalPersonLicence>();
string queryString =
"SELECT PhysicalPersonLicenceId, PhysicalPersonLicenceIdentifier, " +
"PhysicalPersonId, PhysicalPersonIdentifier, PhysicalPersonCode, PhysicalPersonName, " +
"LicenceId, LicenceIdentifier, LicenceCode, LicenceCategory, LicenceDescription, " +
"CountryId, CountryIdentifier, CountryCode, CountryName, " +
"ValidFrom, ValidTo, ItemStatus, " +
"Active, UpdatedAt, CreatedById, CreatedByFirstName, CreatedByLastName, " +
"CompanyId, CompanyName " +
"FROM vPhysicalPersonLicences " +
"WHERE CompanyId = @CompanyId;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = queryString;
command.Parameters.Add(new SqlParameter("@CompanyId", companyId));
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
PhysicalPersonLicence physicalPersonLicence;
while (reader.Read())
{
physicalPersonLicence = new PhysicalPersonLicence();
physicalPersonLicence.Id = Int32.Parse(reader["PhysicalPersonLicenceId"].ToString());
physicalPersonLicence.Identifier = Guid.Parse(reader["PhysicalPersonLicenceIdentifier"].ToString());
if (reader["PhysicalPersonId"] != DBNull.Value)
{
physicalPersonLicence.PhysicalPerson = new PhysicalPerson();
physicalPersonLicence.PhysicalPersonId = Int32.Parse(reader["PhysicalPersonId"].ToString());
physicalPersonLicence.PhysicalPerson.Id = Int32.Parse(reader["PhysicalPersonId"].ToString());
physicalPersonLicence.PhysicalPerson.Identifier = Guid.Parse(reader["PhysicalPersonIdentifier"].ToString());
physicalPersonLicence.PhysicalPerson.Code = reader["PhysicalPersonCode"].ToString();
physicalPersonLicence.PhysicalPerson.Name = reader["PhysicalPersonName"].ToString();
}
if (reader["LicenceId"] != DBNull.Value)
{
physicalPersonLicence.Licence = new LicenceType();
physicalPersonLicence.LicenceId = Int32.Parse(reader["LicenceId"].ToString());
physicalPersonLicence.Licence.Id = Int32.Parse(reader["LicenceId"].ToString());
physicalPersonLicence.Licence.Identifier = Guid.Parse(reader["LicenceIdentifier"].ToString());
physicalPersonLicence.Licence.Code = reader["LicenceCode"].ToString();
physicalPersonLicence.Licence.Category = reader["LicenceCategory"].ToString();
physicalPersonLicence.Licence.Description = reader["LicenceDescription"].ToString();
}
if (reader["CountryId"] != DBNull.Value)
{
physicalPersonLicence.Country = new Country();
physicalPersonLicence.CountryId = Int32.Parse(reader["CountryId"].ToString());
physicalPersonLicence.Country.Id = Int32.Parse(reader["CountryId"].ToString());
physicalPersonLicence.Country.Identifier = Guid.Parse(reader["CountryIdentifier"].ToString());
physicalPersonLicence.Country.Mark = reader["CountryCode"].ToString();
physicalPersonLicence.Country.Name = reader["CountryName"].ToString();
}
if (reader["ValidFrom"] != DBNull.Value)
{
physicalPersonLicence.ValidFrom = DateTime.Parse(reader["ValidFrom"].ToString());
}
if (reader["ValidTo"] != DBNull.Value)
{
physicalPersonLicence.ValidTo = DateTime.Parse(reader["ValidTo"].ToString());
}
if (reader["ItemStatus"] != DBNull.Value)
{
physicalPersonLicence.ItemStatus = Int32.Parse(reader["ItemStatus"].ToString());
}
physicalPersonLicence.Active = bool.Parse(reader["Active"].ToString());
physicalPersonLicence.UpdatedAt = DateTime.Parse(reader["UpdatedAt"].ToString());
if (reader["CreatedById"] != DBNull.Value)
{
physicalPersonLicence.CreatedBy = new User();
physicalPersonLicence.CreatedById = Int32.Parse(reader["CreatedById"].ToString());
physicalPersonLicence.CreatedBy.Id = Int32.Parse(reader["CreatedById"].ToString());
physicalPersonLicence.CreatedBy.FirstName = reader["CreatedByFirstName"]?.ToString();
physicalPersonLicence.CreatedBy.LastName = reader["CreatedByLastName"]?.ToString();
}
if (reader["CompanyId"] != DBNull.Value)
{
physicalPersonLicence.Company = new Company();
physicalPersonLicence.CompanyId = Int32.Parse(reader["CompanyId"].ToString());
physicalPersonLicence.Company.Id = Int32.Parse(reader["CompanyId"].ToString());
physicalPersonLicence.Company.Name = reader["CompanyName"].ToString();
}
PhysicalPersonLicences.Add(physicalPersonLicence);
}
}
}
return(PhysicalPersonLicences);
//List<PhysicalPersonLicence> PhysicalPersonLicences = context.PhysicalPersonLicences
// .Include(x => x.PhysicalPerson)
// .Include(x => x.Licence)
// .Include(x => x.Country)
// .Include(x => x.Company)
// .Include(x => x.CreatedBy)
// .Where(x => x.Active == true && x.CompanyId == companyId)
// .AsNoTracking()
// .ToList();
//return PhysicalPersonLicences;
}
public static PhysicalPersonLicenceViewModel ConvertToPhysicalPersonLicenceViewModelLite(this PhysicalPersonLicence PhysicalPersonItem)
{
PhysicalPersonLicenceViewModel PhysicalPersonItemViewModel = new PhysicalPersonLicenceViewModel()
{
Id = PhysicalPersonItem.Id,
Identifier = PhysicalPersonItem.Identifier,
ValidFrom = PhysicalPersonItem.ValidFrom,
ValidTo = PhysicalPersonItem.ValidTo,
ItemStatus = PhysicalPersonItem.ItemStatus,
IsActive = PhysicalPersonItem.Active,
UpdatedAt = PhysicalPersonItem.UpdatedAt,
CreatedAt = PhysicalPersonItem.CreatedAt
};
return(PhysicalPersonItemViewModel);
}
public static PhysicalPersonLicenceViewModel ConvertToPhysicalPersonLicenceViewModel(this PhysicalPersonLicence PhysicalPersonItem)
{
PhysicalPersonLicenceViewModel PhysicalPersonItemViewModel = new PhysicalPersonLicenceViewModel()
{
Id = PhysicalPersonItem.Id,
Identifier = PhysicalPersonItem.Identifier,
PhysicalPerson = PhysicalPersonItem.PhysicalPerson?.ConvertToPhysicalPersonViewModelLite(),
Licence = PhysicalPersonItem.Licence?.ConvertToLicenceTypeViewModelLite(),
Country = PhysicalPersonItem.Country?.ConvertToCountryViewModelLite(),
ValidFrom = PhysicalPersonItem.ValidFrom,
ValidTo = PhysicalPersonItem.ValidTo,
ItemStatus = PhysicalPersonItem.ItemStatus,
IsActive = PhysicalPersonItem.Active,
CreatedBy = PhysicalPersonItem.CreatedBy?.ConvertToUserViewModelLite(),
Company = PhysicalPersonItem.Company?.ConvertToCompanyViewModelLite(),
UpdatedAt = PhysicalPersonItem.UpdatedAt,
CreatedAt = PhysicalPersonItem.CreatedAt
};
return(PhysicalPersonItemViewModel);
}