public ClientEntity GetClient(String email)
{
if (string.IsNullOrEmpty(email))
{
throw new ArgumentNullException("email", "email is null");
}
ClientEntity client;
using (Petz_dbEntities db = new Petz_dbEntities())
{
client = db.petz_Clients
.Where(x => x.client_email == email &&
x.date_delete == null)
.Select(x => new ClientEntity()
{
Id = x.client_id,
Email = x.client_email,
Document = x.client_document,
Facebook = x.client_profile_facebook,
Name = x.client_name,
NickName = x.client_nickname,
Birthday = x.client_birthday,
Sex = (x.client_sex == null ? EnumSex.Other : (x.client_sex == "F" ? EnumSex.Female : EnumSex.Male)),
Rating = x.client_rating ?? 0
}).FirstOrDefault();
}
if (client != null)
{
client.Phones = GetClientPhone(client.Id);
client.Address = GetClientAddress(client.Id);
}
return(client);
}
public CompanyCalenderEntity[] GetCompanyCalender(int id)
{
if (id <= 0)
{
return(null);
}
using (Petz_dbEntities db = new Petz_dbEntities())
{
return((from s in db.petz_Pet_Scheduling
join a in db.petz_Company_Address on s.company_address_id equals a.company_address_id
where a.company_id == id &&
s.scheduling_date_start >= _filterStart &&
s.scheduling_date_start <= _filterEnd
select new CompanyCalenderEntity
{
Id = s.scheduling_id,
CompanyId = a.company_id,
AddressId = a.address_id,
DateStart = s.scheduling_date_start,
DateEnd = s.scheduling_date_end,
Day = s.scheduling_date_start.Day,
Month = s.scheduling_date_start.Month,
Year = s.scheduling_date_start.Year,
Hour = s.scheduling_date_start.Hour,
Minute = s.scheduling_date_start.Minute,
Status = (StatusEnum)Enum.ToObject(typeof(StatusEnum), s.status_id)
}).ToArray());
}
}
public EmployeesEntity[] GetCompanyEmployees(int id)
{
UsersController controller = new UsersController();
List <EmployeesEntity> arrayEmployees = new List <EmployeesEntity>();
Petz_dbEntities db = new Petz_dbEntities();
var employees = db.petz_Employees
.Where(x => x.company_id == id)
.Select(x => new
{
Admin = x.employee_admin,
CompanyID = x.company_id,
Id = x.employees_id,
UserID = x.user_id
}).ToArray();
foreach (var employee in employees)
{
arrayEmployees.Add(new EmployeesEntity()
{
IsCompanyAdmin = employee.Admin,
Id = employee.Id,
CompanyId = employee.CompanyID,
User = controller.GetUser(employee.UserID)
});
}
return(arrayEmployees.ToArray());
}
public int AuthenticateClient(String email, String password)
{
if (string.IsNullOrEmpty(email))
{
throw new ArgumentNullException("email", "Email is null");
}
if (string.IsNullOrEmpty(password))
{
throw new ArgumentNullException("password", "Password is null");
}
using (Petz_dbEntities db = new Petz_dbEntities())
{
var entity = db.petz_Clients
.Where(x => x.client_email == email &&
x.client_password == password &&
x.date_delete == null)
.Select(x => x.client_id)
.ToArray();
if (entity.Length == 1)
{
return(entity[0]);
}
else
{
return(-1);
}
}
}
public EmployeesEntity GetEmployees(int id)
{
UsersController controller = new UsersController();
Petz_dbEntities db = new Petz_dbEntities();
var employee = db.petz_Employees
.Where(x => x.employees_id == id)
.Select(x => new
{
Admin = x.employee_admin,
CompanyID = x.company_id,
Id = x.employees_id,
UserID = x.user_id
}).FirstOrDefault();
if (employee != null)
{
return(new EmployeesEntity()
{
IsCompanyAdmin = employee.Admin,
Id = employee.Id,
CompanyId = employee.CompanyID,
User = controller.GetUser(employee.UserID)
});
}
else
{
return(null);
}
}
public UserEntity GetUser(int id)
{
using (Petz_dbEntities db = new Petz_dbEntities())
{
var entity = db.petz_Users
.Where(x => x.user_id == id)
.Select(x => new UserEntity()
{
Email = x.user_email,
Id = x.user_id,
IsSystemAdmin = x.user_admin,
Name = x.user_name,
UserName = x.user_login
}
).ToArray();
if (entity.Length == 1)
{
return(entity[0]);
}
else
{
return(null);
}
}
}
public ClientEntity GetClient(Int32 id)
{
ClientEntity client;
using (Petz_dbEntities db = new Petz_dbEntities())
{
client = db.petz_Clients
.Where(x => x.client_id == id)
.Select(x => new ClientEntity()
{
Id = x.client_id,
Email = x.client_email,
Document = x.client_document,
Facebook = x.client_profile_facebook,
Name = x.client_name,
NickName = x.client_nickname,
Birthday = x.client_birthday,
Sex = (x.client_sex == null ? EnumSex.Other : (x.client_sex == "F" ? EnumSex.Female : EnumSex.Male)),
Rating = x.client_rating ?? 0
}).FirstOrDefault();
}
if (client != null)
{
client.Phones = GetClientPhone(id);
client.Address = GetClientAddress(id);
}
return(client);
}
public HistoricEntity[] GetPetHistoric(int id)
{
if (id <= 0)
{
throw new ArgumentNullException("id", "ID is null");
}
var arrayOfHistoricEntity = new List <HistoricEntity>();
CompaniesController companyController = new CompaniesController();
using (Petz_dbEntities db = new Petz_dbEntities())
{
var arrayHistoric = db.petz_Pet_Historic.Where(x => x.pet_id == id).ToArray();
foreach (var hist in arrayHistoric)
{
HistoricEntity entity = new HistoricEntity {
Comments = hist.history_comments
};
if (hist.history_date != null)
{
entity.Date = hist.history_date.Value;
}
entity.Id = hist.history_id;
entity.Employee = companyController.GetEmployees(hist.employees_id);
arrayOfHistoricEntity.Add(entity);
}
}
return(arrayOfHistoricEntity.ToArray());
}
public ClientEntity[] GetCompanyClient(int id, int clientId = 0)
{
List <ClientEntity> arrayOfClientEntity = new List <ClientEntity>();
ClientsController controller = new ClientsController();
Petz_dbEntities db = new Petz_dbEntities();
int[] idsByService = (from s in db.petz_Pet_Scheduling
join a in db.petz_Company_Address on s.company_address_id equals a.company_address_id
where a.company_id == id &&
s.date_delete == null &&
s.client_id == (clientId <= 0 ? s.client_id : clientId)
select s.client_id)
.Distinct()
.ToArray();
int[] idsByFavorite = db.petz_Client_Company
.Where(x => x.company_id == id &&
x.client_id == (clientId <= 0 ? x.client_id : clientId))
.Select(x => x.client_id)
.Distinct()
.ToArray();
int[] ids = idsByService.Concat(idsByFavorite)
.Distinct()
.ToArray();
foreach (int i in ids)
{
arrayOfClientEntity.Add(controller.GetClient(i));
}
return(arrayOfClientEntity.ToArray());
}
public int AuthenticateUser(String userName, String password)
{
if (string.IsNullOrEmpty(userName))
{
throw new ArgumentNullException("userName", "UserName is null");
}
if (string.IsNullOrEmpty(password))
{
throw new ArgumentNullException("password", "Password is null");
}
using (Petz_dbEntities db = new Petz_dbEntities())
{
var entity = db.petz_Users
.Where(x => x.user_login == userName &&
x.user_password == password &&
x.date_delete == null)
.Select(x => x.user_id)
.ToArray();
if (entity.Length == 1)
{
return(entity[0]);
}
else
{
return(-1);
}
}
}
public SubSpeciesEntity[] GetSubSpecies(int?speciesId = 0, int?id = 0, String name = "")
{
using (Petz_dbEntities db = new Petz_dbEntities())
{
var subSpecies = db.petz_Sub_Species
.Where(x => (x.species_id == speciesId || speciesId == 0) &&
(x.sub_species_id == id || id == 0))
.Select(x => new SubSpeciesEntity()
{
Id = x.sub_species_id,
Name = x.sub_species_name,
Species = new SpeciesEntity()
{
Id = x.species_id, Name = x.petz_Species.species_name
}
}).ToArray();
if (!string.IsNullOrEmpty(name))
{
return(subSpecies.Where(x => x.Name.Contains(name)).ToArray());
}
else
{
return(subSpecies);
}
}
}
public CompaniesEntity[] GetClientCompanies(int id)
{
if (id <= 0)
{
throw new ArgumentNullException("id", "ID is null");
}
CompaniesController controller = new CompaniesController();
using (Petz_dbEntities db = new Petz_dbEntities())
{
int[] company = db.petz_Client_Company
.Where(x => x.client_id == id)
.Select(x => x.company_id)
.ToArray();
List <CompaniesEntity> array = new List <CompaniesEntity>();
foreach (int c in company)
{
array.Add(controller.GetCompany(c));
}
return(array.ToArray());
}
}
public void DeleteScheduling(int clientId, int schedulingId)
{
if (clientId <= 0)
{
throw new ArgumentNullException("clientId", "ClientID is null");
}
if (schedulingId <= 0)
{
throw new ArgumentNullException("schedulingId", "SchedulingID is null");
}
bool schedulingClient;
using (Petz_dbEntities db = new Petz_dbEntities())
{
schedulingClient = db.petz_Pet_Scheduling.Count(x => x.client_id == clientId && x.scheduling_id == schedulingId) == 1;
}
if (schedulingClient)
{
SchedulingController controller = new SchedulingController();
controller.DeleteSheduling(schedulingId);
}
else
{
throw new Exception("Scheduling (" + schedulingId + ") not found!");
}
}
public SpeciesEntity[] GetSpecies(String name = "")
{
using (Petz_dbEntities db = new Petz_dbEntities())
{
if (!string.IsNullOrEmpty(name))
{
return(db.petz_Species
.Where(x => x.species_name.Contains(name))
.Select(x => new SpeciesEntity()
{
Id = x.species_id,
Name = x.species_name
}).ToArray());
}
else
{
return(db.petz_Species
.Select(x => new SpeciesEntity()
{
Id = x.species_id,
Name = x.species_name
}).ToArray());
}
}
}
public void SetConfirmShedulingByCompany(int schedulingId, int company)
{
if (company <= 0)
{
throw new ArgumentNullException("company", "Company is null");
}
if (schedulingId <= 0)
{
throw new ArgumentNullException("schedulingId", "SchedulingID is null");
}
var schedulingClientUpd = GetSchedulingCompany(company).FirstOrDefault(x => x.Id == schedulingId);
if (schedulingClientUpd == null)
{
throw new ArgumentOutOfRangeException("schedulingId", schedulingId, "This schedule does not belong to you.");
}
if (schedulingClientUpd.Status.Id != (int)StatusEnum.EventChangedByClient)
{
throw new OperationCanceledException("This schedule is not waiting for confirmation.");
}
using (Petz_dbEntities db = new Petz_dbEntities())
{
var upd = db.petz_Pet_Scheduling.FirstOrDefault(x => x.scheduling_id == schedulingId);
if (upd != null)
{
upd.status_id = (int)StatusEnum.EventConfirmedByCompany;
}
db.SaveChanges();
}
}
public VaccinationEntity[] GetPetVaccination(int clientId, int petId)
{
if (clientId <= 0)
{
throw new ArgumentNullException("clientId", "ClientID is null");
}
if (petId <= 0)
{
throw new ArgumentNullException("petId", "PetID is null");
}
bool petClient;
using (Petz_dbEntities db = new Petz_dbEntities())
{
petClient = db.petz_Client_Pet.Count(x => x.client_id == clientId && x.pet_id == petId) == 1;
}
if (petClient)
{
PetsController controller = new PetsController();
return(controller.GetPetVaccination(petId));
}
else
{
throw new Exception("Pet (" + petId + ") not found!");
}
}
public void DeleteClientPhone(int id, PhoneEntity phone)
{
if (phone == null)
{
throw new ArgumentNullException("phone", "phone is null");
}
if (phone.Id <= 0)
{
throw new ArgumentNullException("phone.Id", "phone.ID is null");
}
if (id <= 0)
{
throw new ArgumentNullException("id", "ID is null");
}
using (Petz_dbEntities db = new Petz_dbEntities())
{
petz_Client_Phone p = db.petz_Client_Phone.FirstOrDefault(x => x.phone_id == phone.Id && x.client_id == id);
if (p != null)
{
db.petz_Client_Phone.Remove(p);
db.SaveChanges();
}
else
{
throw new Exception("This phone (" + phone.Id + ") is not this client (" + id + ")");
}
}
}
public SchedulingEntity[] GetSchedulingCompany(int companyId, int schedulingId = 0)
{
List <SchedulingEntity> arrayOfSchedulingEntity = new List <SchedulingEntity>();
using (Petz_dbEntities db = new Petz_dbEntities())
{
var petScheduling = (from s in db.petz_Pet_Scheduling
join a in db.petz_Company_Address on s.company_address_id equals a.company_address_id
where a.company_id == companyId &&
s.scheduling_date_start >= _filterStart &&
s.scheduling_date_start <= _filterEnd &&
s.scheduling_id == (schedulingId == 0 ? s.scheduling_id : schedulingId)
select new {
s.scheduling_id,
s.scheduling_comments,
s.scheduling_date_start,
s.scheduling_date_end,
s.pet_id,
s.client_id,
a.company_id,
a.address_id,
s.service_id,
s.employees_id,
s.status_id
}
).ToArray();
foreach (var ps in petScheduling)
{
SchedulingEntity entity = new SchedulingEntity
{
Id = ps.scheduling_id,
CompanyId = ps.company_id,
Comments = ps.scheduling_comments,
DateStart = ps.scheduling_date_start,
DateEnd = ps.scheduling_date_end,
AddressId = ps.address_id,
Address = _addressController.GetAddress(ps.address_id),
Pet = _petController.GetPet(ps.pet_id),
Client = _clientController.GetClient(ps.client_id),
Status = _statusController.GetStatusCompany(ps.company_id, ps.status_id),
Company = _companyController.GetCompany(ps.company_id)
};
if (ps.service_id.HasValue)
{
entity.Service = _serviceController.GetService(ps.service_id.Value);
}
if (ps.employees_id.HasValue)
{
entity.Employee = _companyController.GetEmployees(ps.employees_id.Value);
}
arrayOfSchedulingEntity.Add(entity);
}
}
return(arrayOfSchedulingEntity.ToArray());
}
public BreedEntity[] GetBreed(int?subSpeciesId = 0, int?id = 0, String name = "")
{
using (Petz_dbEntities db = new Petz_dbEntities())
{
var breed = db.petz_Breed
.Where(x => (x.sub_species_id == subSpeciesId || subSpeciesId == 0) &&
(x.breed_id == id || id == 0))
.Select(x => new BreedEntity()
{
Name = x.breed_name,
Id = x.breed_id,
UrlReference = x.breed_url_ref,
SubSpecies = new SubSpeciesEntity()
{
Id = x.sub_species_id,
Name = x.petz_Sub_Species.sub_species_name,
Species = new SpeciesEntity()
{
Id = x.petz_Sub_Species.species_id,
Name = x.petz_Sub_Species.petz_Species.species_name
}
}
}).ToArray();
if (!string.IsNullOrEmpty(name))
{
return(breed.Where(x => x.Name.Contains(name)).ToArray());
}
else
{
return(breed);
}
}
}
public void SetClientPicture(int id, byte[] bytes)
{
if (bytes == null)
{
throw new ArgumentNullException("bytes", "bytes is null");
}
if (id <= 0)
{
throw new ArgumentNullException("id", "ID is null");
}
if (bytes.Length > 512000) // 512KB = 500 * 1024
{
throw new Exception("File too large! Limit of 512KB!");
}
using (Petz_dbEntities db = new Petz_dbEntities())
{
var client = db.petz_Clients.FirstOrDefault(x => x.client_id == id && x.date_delete == null);
if (client != null)
{
client.client_picture = Compressor.Compress(ImageHelper.ConvertImage(bytes, System.Drawing.Imaging.ImageFormat.Jpeg));
}
db.SaveChanges();
}
}
public VaccinationEntity[] GetPetVaccination(int id)
{
if (id <= 0)
{
throw new ArgumentNullException("id", "ID is null");
}
List <VaccinationEntity> arrayOfVaccinationEntity = new List <VaccinationEntity>();
using (Petz_dbEntities db = new Petz_dbEntities())
{
var arrayVaccination = db.petz_Pet_Vaccination.Where(x => x.pet_id == id).ToArray();
foreach (var vaccination in arrayVaccination)
{
VaccinationEntity entity = new VaccinationEntity
{
Comments = vaccination.vaccination_comments,
Id = vaccination.vaccination_id
};
if (vaccination.vaccination_date != null)
{
entity.Date = vaccination.vaccination_date.Value;
}
arrayOfVaccinationEntity.Add(entity);
}
}
return(arrayOfVaccinationEntity.ToArray());
}
public AddressEntity GetAddress(int id)
{
using (Petz_dbEntities db = new Petz_dbEntities())
{
return((from a in db.petz_Address
join s in db.petz_States on a.state_id equals s.state_id
join co in db.petz_Countries on s.country_id equals co.country_id
where a.address_id == id &&
a.date_delete == null
select new AddressEntity()
{
Id = a.address_id,
Name = a.address_nickname,
Address = a.address_name,
Complement = a.address_complement,
Latitude = a.address_latitude,
Longitude = a.address_longitude,
Number = a.address_number,
ZipCode = a.address_zip,
State = new StatesEntity()
{
Id = s.state_id,
Name = s.state_name,
Abbreviation = s.state_abbreviation,
Country = new CountriesEntity()
{
Id = co.country_id,
Name = co.country_name
}
}
}).FirstOrDefault());
}
}
public StatusEntity GetStatusCompany(int companyId, int statusId)
{
using (Petz_dbEntities db = new Petz_dbEntities())
{
var companySt = db.petz_Company_Status_Color
.Where(x => x.company_id == companyId && x.status_id == statusId && x.date_delete == null)
.Select(x => new StatusEntity()
{
Id = x.status_id,
//Name = x.status_name,
//Description = x.status_description,
BackgroundColor = x.status_background_color,
BorderColor = x.status_border_color,
TextColor = x.status_text_color
}).FirstOrDefault();
if (companySt == null)
{
return(GetStatus(statusId));
}
else
{
var info = GetStatus(statusId);
companySt.Name = info.Name;
companySt.Description = info.Description;
companySt.BackgroundColor = (string.IsNullOrEmpty(companySt.BackgroundColor) ? info.BackgroundColor : companySt.BackgroundColor);
companySt.BorderColor = (string.IsNullOrEmpty(companySt.BorderColor) ? info.BorderColor : companySt.BorderColor);
companySt.TextColor = (string.IsNullOrEmpty(companySt.TextColor) ? info.TextColor : companySt.TextColor);
return(companySt);
}
}
}
public RatingEntity[] GetPetRating(int id)
{
if (id <= 0)
{
return(null);
}
using (Petz_dbEntities db = new Petz_dbEntities())
{
return(db.petz_Rating
.Where(x => x.rating_pet_id == id)
.Select(x => new RatingEntity()
{
Comments = x.rating_comments,
Date = x.date_insert.Value,
Id = x.rating_id,
InsertByClientId = x.insert_client_id ?? 0,
InsertByUserId = x.insert_user_id ?? 0,
RatingClientId = x.rating_client_id ?? 0,
RatingPetId = x.rating_pet_id ?? 0,
RatingUserId = x.rating_user_id ?? 0,
RatingValue = x.rating_value.Value
})
.OrderBy(x => x.Date)
.ToArray());
}
}
public byte[] GetCompanyPicture(int id)
{
if (id <= 0)
{
throw new ArgumentNullException("id", "ID is null");
}
Petz_dbEntities db = new Petz_dbEntities();
return(Compressor.Decompress(db.petz_Companies.Where(x => x.company_id == id && x.date_delete == null).Select(x => x.company_picture).FirstOrDefault()));
}
public bool IsAdmin(int companyId, int userId)
{
using (Petz_dbEntities db = new Petz_dbEntities())
{
return(db.petz_Employees
.Where(x => x.company_id == companyId &&
x.user_id == userId)
.Select(x => x.employee_admin)
.FirstOrDefault());
}
}
public byte[] GetUserPicture(int id)
{
if (id <= 0)
{
throw new ArgumentNullException("id", "ID is null");
}
using (Petz_dbEntities db = new Petz_dbEntities())
{
return(Compressor.Decompress(db.petz_Users.Where(x => x.user_id == id).Select(x => x.user_picture).FirstOrDefault()));
}
}
public PhoneEntity[] GetCompanyPhone(int id, int?addressId = 0)
{
Petz_dbEntities db = new Petz_dbEntities();
return(db.petz_Company_Phone
.Where(x => x.company_id == id &&
(addressId == 0 || x.address_id == addressId))
.Select(x => new PhoneEntity()
{
Id = x.phone_id,
Phone = x.company_phone
}).ToArray());
}
public ItemEntity[] GetCompaniesUser(int userId)
{
Petz_dbEntities db = new Petz_dbEntities();
return((from e in db.petz_Employees
join c in db.petz_Companies on e.company_id equals c.company_id
where e.user_id == userId && e.date_delete == null
select new ItemEntity()
{
Value = e.company_id.ToString(),
Text = c.company_name
}).ToArray());
}
public PhoneEntity[] GetClientPhone(int id)
{
using (Petz_dbEntities db = new Petz_dbEntities())
{
return(db.petz_Client_Phone
.Where(x => x.client_id == id)
.Select(x => new PhoneEntity()
{
Id = x.phone_id,
Phone = x.client_phone
}).ToArray());
}
}