public IList <Model.HierarchyStructure> ListAvailableByPurpose(string idCulture, int idPurpose)
{
using (var context = new ImobeNetContext())
{
StringBuilder sql = new StringBuilder();
object[] parameters = new object[] { new SqlParameter("IDPurpose", idPurpose),
new SqlParameter("Status", (short)Enums.StatusType.Active),
new SqlParameter("IDCulture", idCulture) };
sql.AppendLine("select ");
sql.AppendLine(" hs.*, ");
sql.AppendLine(" hsc.description,");
sql.AppendLine(" i.*");
sql.AppendLine(" from HierarchyStructure hs");
sql.AppendLine(" left join Icon i on (hs.IDIcon = i.IDIcon)");
sql.AppendLine(" inner join HierarchyStructureCulture hsc on (hs.IDHierarchyStructure = hsc.IDHierarchyStructure )");
//sql.AppendLine(" inner join HierarchyStructurePurpose hsp on (hs.IDHierarchyStructure = hsp.IDHierarchyStructure )");
sql.AppendLine(" where hsc.IDCulture = @IDCulture");
sql.AppendLine(" and hs.Status = @Status");
if (idPurpose == 0)
{
sql.AppendLine(" and hs.IDHierarchyStructure not in (select IDHierarchyStructure from HierarchyStructurePurpose where IDPurpose = @IDPurpose )");
}
sql.AppendLine(" order by hsc.description");
return(context.Database.SqlQuery <Model.HierarchyStructure>(sql.ToString(), parameters).ToList());
}
}
public IList <AttributeCultureExtended> GetAll(int?idAttribute, string idCulture)
{
StringBuilder sql = new StringBuilder();
object[] parameters = new object[]
{
new SqlParameter("IDCulture", idCulture),
new SqlParameter("IDAttribute", idAttribute)
};
using (var context = new ImobeNetContext())
{
sql.AppendLine("exec pAttributeCulture_List @IDAttribute, @IDCulture");
return(context.Database.SqlQuery <AttributeCultureExtended>(sql.ToString(), parameters).ToList());
}
//using (var context = new ImobeNetContext())
//{
// return (from pc in context.AttributeCulture//.ToList()
// join c in context.Culture on pc.IDCulture equals c.IDCulture
// join i in context.Icon on c.IDIcon equals i.IDIcon
// select new AttributeCulture
// {
// IDCulture = pc.IDCulture,
// IDAttribute = pc.IDAttribute,
// Name = pc.Name,
// Value = pc.Value,
// CultureName = c.Name,
// IconPath = i.Path,
// }).ToList();
//}
}
public IList <Model.Element> GetAll()
{
using (var context = new ImobeNetContext())
{
return(context.Database.SqlQuery <Model.Element>("select * from [element] with (nolock)").ToList());
}
}
public City GetInsert(string cityName, int idStateProvince)
{
using (var context = new ImobeNetContext())
{
var city = (from c in context.City
where c.Name.ToUpper() == cityName.ToUpper() &&
c.IDStateProvince == idStateProvince
select c).FirstOrDefault();
if (city == null)
{
city = new City()
{
CreateDate = DateTime.Now,
ModifyDate = DateTime.Now,
ModifiedBy = "AUTO",
CreatedBy = "AUTO",
IDStateProvince = idStateProvince,
Name = cityName,
Status = (short)Enums.StatusType.Active
};
this.Insert(city);
}
return(city);
}
}
public StateProvince GetInsert(string stateName, string idCountry)
{
using (var context = new ImobeNetContext())
{
var state = (from c in context.StateProvince
where c.Acronym.ToUpper() == stateName.ToUpper().Trim() &&
c.IDCountry == idCountry
select c).FirstOrDefault();
if (state == null)
{
state = new StateProvince()
{
Acronym = stateName,
CreateDate = DateTime.Now,
ModifyDate = DateTime.Now,
CreatedBy = "AUTO",
ModifiedBy = "AUTO",
IDCountry = idCountry,
Name = stateName,
Status = (short)Enums.StatusType.Active
};
this.Insert(state);
}
return(state);
}
}
public IList <Model.AttributeExtended> List(string idCulture, int idAttributeType, long idElement)
{
StringBuilder sql = new StringBuilder();
object[] parameters = new object[] { new SqlParameter("IDAttributeType", idAttributeType),
new SqlParameter("Status", (short)Enums.StatusType.Active),
new SqlParameter("IDElement", idElement),
new SqlParameter("IDCulture", idCulture) };
using (var context = new ImobeNetContext())
{
sql.AppendLine(" exec pAttribute_ListByAttributeType @IDAttributeType, @IDCulture, @Status, @IDElement");
var result = context.Database.SqlQuery <Model.AttributeExtended>(sql.ToString(), parameters).ToList();
for (int i = 0; i < result.Count; i++)
{
if (!string.IsNullOrEmpty(result[i].DisplayMask))
{
decimal value = 0;
if (decimal.TryParse(result[i].Value, out value))
{
result[i].Value = string.Format(result[i].DisplayMask, value);
}
}
}
return(result);
}
}
public IList <EmailExtended> GetAll(string idCulture, out int recordCount, long idEmail = -1, int idCustomerTo = -1, int idCustomerFrom = -1, short status = -1, string sortType = null, int startRowIndex = -1, int maximumRows = -1)
{
using (var context = new ImobeNetContext())
{
recordCount = 0;
SqlParameter recordCountParameter = new SqlParameter("RecordCount", 0);
recordCountParameter.Direction = System.Data.ParameterDirection.Output;
StringBuilder sql = new StringBuilder();
object[] parameters = new object[] {
new SqlParameter("IDCulture", idCulture),
new SqlParameter("IDEmail", idEmail),
new SqlParameter("IDCustomerTo", idCustomerTo),
new SqlParameter("IDCustomerFrom", idCustomerFrom),
new SqlParameter("Status", status),
new SqlParameter("StartRowIndex", startRowIndex),
new SqlParameter("MaximumRows", maximumRows),
recordCountParameter
};
sql.AppendLine("exec pEmail_GetAll @IDCulture, @IDEmail, @IDCustomerTo, @IDCustomerFrom, @Status, @StartRowIndex, @MaximumRows, @RecordCount out");
var result = context.Database.SqlQuery <Model.EmailExtended>(sql.ToString(), parameters).ToList();
recordCount = Convert.ToInt32(recordCountParameter.Value);
return(result);
}
}
public void Inactivate(int[] ids)
{
//TODO:REVER
using (var context = new ImobeNetContext())
{
StringBuilder sql = new StringBuilder();
string elements = "-1";
if (ids != null && ids.Length > 0)
{
elements = String.Join(",", ids);
}
object[] parameters = new object[] {
new SqlParameter("IDElement", elements),
new SqlParameter("ModifiedBy", ServiceContext.ActiveUserName),
new SqlParameter("ModifyDate", DateTime.Now)
};
sql.AppendLine(" update [dbo].[element] ");
sql.AppendLine(" set Status = 0, ");
sql.AppendLine(" ModifiedBy = @ModifiedBy, ");
sql.AppendLine(" ModifyDate = @ModifyDate ");
sql.AppendLine(" where IDElement in (select * from [dbo].[IntegerCommaSplit](@IDElement)) ");
context.Database.ExecuteSqlCommand(sql.ToString(), parameters);
}
}
public IList <Model.HierarchyStructureExtended> ListAvailable(string idCulture, int idHierarchyStructureParent)
{
using (var context = new ImobeNetContext())
{
var result = (from hs in context.HierarchyStructure.ToList()
join hsc in context.HierarchyStructureCulture on new { IDHierarchyStructure = hs.IDHierarchyStructure.Value, IDCulture = idCulture } equals new { IDHierarchyStructure = hsc.IDHierarchyStructure, IDCulture = hsc.IDCulture }
join i in context.Icon on hs.IDIcon equals i.IDIcon
where hs.IDHierarchyStructureParent != idHierarchyStructureParent
orderby hsc.Description
select new Model.HierarchyStructureExtended
{
Description = hsc.Description,
IDHierarchyStructure = hs.IDHierarchyStructure,
IDHierarchyStructureParent = hs.IDHierarchyStructureParent,
IconPath = i.Path,
IDIcon = hs.IDIcon,
IconName = i.Name,
CreateDate = hs.CreateDate,
CreatedBy = hs.CreatedBy,
ModifiedBy = hs.ModifiedBy,
ModifyDate = hs.ModifyDate,
Status = hs.Status,
});
return(result.ToList());
}
}
public IList <Model.HierarchyStructureBasic> ListAvailableCategory(int[] idPurpose, string idCulture)
{
using (var context = new ImobeNetContext())
{
StringBuilder sql = new StringBuilder();
string purposes = "-1";
if (idPurpose != null && idPurpose.Length > 0)
{
purposes = String.Join(",", idPurpose);
}
object[] parameters = new object[] { new SqlParameter("IDPurpose", purposes),
new SqlParameter("Status", (short)Enums.StatusType.Active),
new SqlParameter("IDCulture", idCulture) };
sql.AppendLine("select distinct");
sql.AppendLine(" hs.*, ");
sql.AppendLine(" hsc.Description,");
sql.AppendLine(" i.*");
sql.AppendLine(" from HierarchyStructure hs");
sql.AppendLine(" left join Icon i on (hs.IDIcon = i.IDIcon)");
sql.AppendLine(" inner join HierarchyStructureCulture hsc on (hs.IDHierarchyStructure = hsc.IDHierarchyStructure )");
sql.AppendLine(" where hsc.IDCulture = @IDCulture");
sql.AppendLine(" and hs.Status = @Status");
sql.AppendLine(" and hs.IDHierarchyStructureParent is null");
sql.AppendLine(" and hs.IDHierarchyStructure not in (select IDHierarchyStructure from HierarchyStructurePurpose where IDPurpose in ( (select * from [dbo].[IntegerCommaSplit](@IDPurpose)) ) )");
sql.AppendLine(" order by hsc.Description");
return(context.Database.SqlQuery <Model.HierarchyStructureBasic>(sql.ToString(), parameters).ToList());
}
}
public void InsertImage(long idElement, string path)
{
using (var context = new ImobeNetContext())
{
var repository = new BaseRepository <ElementAttribute>(context);
var elementRepository = new BaseRepository <Element>(context);
var element = elementRepository.First(p => p.IDElement == idElement);
//caso nao haja imagem default entao setar uma
if (string.IsNullOrEmpty(element.DefaultPicturePath))
{
element.DefaultPicturePath = path;
elementRepository.Update(element);
elementRepository.SaveChanges();
}
repository.Insert(new ElementAttribute()
{
IDElement = idElement,
IDAttribute = ServiceContext.AttributeService.GetByGroup("IMG").IDAttribute.Value,
Value = path
});
repository.SaveChanges();
}
}
public IList <AdsCategoryRelation> GetAll()
{
using (var context = new ImobeNetContext())
{
return(context.AdsCategoryRelation.ToList());
}
}
public IList <Purpose> GetAll()
{
using (var context = new ImobeNetContext())
{
var purposeRepository = new BaseRepository <Purpose>(context);
return(purposeRepository.GetAll().ToList());
}
}
public IList <Model.AttributeType> GetAll()
{
using (var context = new ImobeNetContext())
{
var attributeTypeRepository = new BaseRepository <Model.AttributeType>(context);
return(attributeTypeRepository.GetAll().ToList());
}
}
public Customer GetById(int id)
{
using (var context = new ImobeNetContext())
{
var customerRepository = new BaseRepository <Customer>(context);
return(customerRepository.Find(p => p.IDCustomer == id).FirstOrDefault());
}
}
public Customer GetByActivateKey(Guid activateKey)
{
using (var context = new ImobeNetContext())
{
var customerRepository = new BaseRepository <Customer>(context);
return(customerRepository.Find(p => p.ActivateKey == activateKey).FirstOrDefault());
}
}
public Customer GetByUserEmail(string email)
{
using (var context = new ImobeNetContext())
{
var customerRepository = new BaseRepository <Customer>(context);
return(customerRepository.Find(p => p.Email == email).FirstOrDefault());
}
}
public Customer GetBySiteName(string siteName)
{
using (var context = new ImobeNetContext())
{
var customerRepository = new BaseRepository <Customer>(context);
return(customerRepository.Find(p => p.SiteName == siteName).FirstOrDefault());
}
}
public IList <Country> GetAll()
{
using (var context = new ImobeNetContext())
{
var countryRepository = new BaseRepository <Country>(context);
return(countryRepository.GetAll().OrderBy(p => p.Name).ToList());
}
}
public IList <Country> GetByStatus(short status)
{
using (var context = new ImobeNetContext())
{
var countryRepository = new BaseRepository <Country>(context);
return(countryRepository.GetAll().Where(p => p.Status == status).ToList());
}
}
public Model.Attribute GetByGroup(string value)
{
using (var context = new ImobeNetContext())
{
var attributeRepository = new BaseRepository <Model.Attribute>(context);
return(attributeRepository.FirstOrDefault(p => p.Group == value));
}
}
public Model.Attribute GetById(int id)
{
using (var context = new ImobeNetContext())
{
var attributeRepository = new BaseRepository <Model.Attribute>(context);
return(attributeRepository.GetById(id));
}
}
public Country GetById(string id)
{
using (var context = new ImobeNetContext())
{
var countryRepository = new BaseRepository <Country, string>(context);
return(countryRepository.GetById(id));
}
}
public IList <HierarchyStructure> GetAll()
{
using (var context = new ImobeNetContext())
{
var hierarchyStructureRepository = new BaseRepository <Model.HierarchyStructure>(context);
return(hierarchyStructureRepository.GetAll().ToList());
}
}
public IList <Icon> GetAll()
{
using (var context = new ImobeNetContext())
{
var iconRepository = new BaseRepository <Icon>(context);
return(iconRepository.GetAll().OrderBy(p => p.Name).ToList());
}
}
public Icon GetById(int id)
{
using (var context = new ImobeNetContext())
{
var iconRepository = new BaseRepository <Icon>(context);
return(iconRepository.GetById(id));
}
}
public Element GetByIDItemSite(string idItemSite)
{
using (var context = new ImobeNetContext())
{
var objectRepository = new BaseRepository <Element>(context);
return(objectRepository.FirstOrDefault(p => p.IDItemSite == idItemSite));
}
}
public IList <Customer> GetByStatus(short status)
{
using (var context = new ImobeNetContext())
{
var customerRepository = new BaseRepository <Customer>(context);
return(customerRepository.Fetch().Where(p => p.Status == status).ToList());
}
}
public Model.HierarchyStructure GetById(int id)
{
using (var context = new ImobeNetContext())
{
var hierarchyStructureRepository = new BaseRepository <Model.HierarchyStructure>(context);
return(hierarchyStructureRepository.GetById(id));
}
}
public void Update(AdsCategory entity)
{
using (var context = new ImobeNetContext())
{
var adsCategoryRepository = new BaseRepository <AdsCategory>(context);
adsCategoryRepository.Update(entity);
adsCategoryRepository.SaveChanges();
}
}
