public UserSoupKitchen SaveUserSoupKitchen(UserSoupKitchen dto)
{
try
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
USER_SOUP_KITCHEN ut = db.USER_SOUP_KITCHEN.FirstOrDefault(v => v.Id_User == dto.Id_User && v.Id_Soup_Kitchen == dto.Id_Soup_Kitchen);
if (ut != null)
{
dto.Message = "Se ha ingresado previamente.";
}
else
{
ut = new USER_SOUP_KITCHEN();
ut.Id_Soup_Kitchen = dto.Id_Soup_Kitchen;
ut.Id_User = dto.Id_User;
ut.IsActive = true;
db.USER_SOUP_KITCHEN.Add(ut);
if (db.SaveChanges() > 0)
{
dto.Id = ut.Id;
dto.Message = "SUCCESS";
}
}
return(dto);
}
}
catch (Exception ex)
{
return(new UserSoupKitchen());
}
}
public string DeleteUserSoupKitchen(int idSk, int idUser)
{
string result = "";
try
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
USER_SOUP_KITCHEN att = db.USER_SOUP_KITCHEN.FirstOrDefault(v => v.Id_Soup_Kitchen == idSk && v.Id_User == idUser);
if (att != null)
{
db.USER_SOUP_KITCHEN.Remove(att);
db.SaveChanges();
result = "SUCCESS";
}
else
{
result = "No se pudo desasociar el comedor.";
}
}
}
catch (Exception ex)
{
result = "No se pudo desasociar el comedor";
}
return(result);
}
public SkUserTypeDTOcs SaveUserTypeSK(SkUserTypeDTOcs dto)
{
try
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
SOUP_KITCHEN_USER_TYPE ut = db.SOUP_KITCHEN_USER_TYPE.FirstOrDefault(v => v.Id_UserType == dto.Id_UserType && v.Id_SoupKitchen == dto.Id_SoupKitchen);
if (ut != null)
{
dto.Message = "Se ha ingresado previamente.";
}
else
{
ut = new SOUP_KITCHEN_USER_TYPE();
ut.Id_SoupKitchen = dto.Id_SoupKitchen;
ut.Id_UserType = dto.Id_UserType;
ut.IsActive = true;
db.SOUP_KITCHEN_USER_TYPE.Add(ut);
if (db.SaveChanges() > 0)
{
dto.Id = ut.Id;
dto.Message = "SUCCESS";
}
}
return(dto);
}
}
catch (Exception ex)
{
return(new SkUserTypeDTOcs());
}
}
public string GenerateCode(int pUserId)
{
string code = string.Empty;
bool ok = false;
while (!ok)
{
int longitud = 10;
Guid miGuid = Guid.NewGuid();
code = Convert.ToBase64String(miGuid.ToByteArray());
code = code.Replace("=", "").Replace("+", "");
code = code.Substring(0, longitud);
using (SEDESOLEntities db = new SEDESOLEntities())
{
INSPECTION_CODE insCode = db.INSPECTION_CODE.FirstOrDefault(v => v.InspectionCode == code && v.Id_User == pUserId);
if (insCode != null)
{
ok = false;
}
else
{
ok = true;
break;
}
}
}
return(code);
}
public List <UserDTO> GetUserAll()
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
var query = from user in entities.USERs
select new UserDTO
{
Id = user.Id,
Username = user.Username,
Password = user.Password,
Name = user.Name,
LastName = user.LastName,
PhoneNumber = user.PhoneNumber,
Email = user.Email,
Dni = user.Dni,
IsActive = user.IsActive,
Id_User_Type = user.Id_User_Type,
UserType = new UserTypeDTO
{
Id = user.USER_TYPE.Id,
Description = user.USER_TYPE.Description,
IsActive = user.USER_TYPE.IsActive
}
};
return(query.ToList <UserDTO>());
}
}
public AttendanceDTO GetAttendanceById(int id)
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
var query = db.ATTENDANCEs.Where(f => f.Id == id).Select(p =>
new AttendanceDTO
{
Id = p.Id,
Name = p.Name,
LastName = p.LastName,
LastName2 = p.LastName2,
Curp = p.Curp,
Birthdate = p.Birthdate,
CreateDate = p.CreateDate,
Quantity = p.Quantity,
Id_Capture = p.Id_Capture,
Id_Receiver = p.Id_Receiver,
IsActive = p.IsActive,
IsAnonym = p.IsAnonym,
Gender = p.Gender,
Id_Condition = p.Id_Condition,
Id_State_Birth = p.Id_State_Birth,
Address = p.Address,
PhoneNumber = p.PhoneNumber
});
return(query.FirstOrDefault());
}
}
public string Delete(int id)
{
string result = "";
try
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
ATTENDANCE att = db.ATTENDANCEs.FirstOrDefault(v => v.Id == id);
if (att != null)
{
db.ATTENDANCEs.Remove(att);
db.SaveChanges();
result = "SUCCESS";
}
else
{
result = "No se pudo eliminar la asistencia.";
}
}
}
catch (Exception ex)
{
result = "No se pudo eliminar la asistencia";
}
return(result);
}
public List <CaptureImageDTO> GetImageByCaptureId(int CaptureId)
{
List <CaptureImageDTO> list = new List <CaptureImageDTO>();
try
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
var query = from img in db.CAPTURE_IMAGE
where img.Id_Capture == CaptureId
select new CaptureImageDTO
{
Id = img.Id,
Id_Capture = img.Id_Capture,
ImageFile = img.ImageFile,
ImageFileB64 = string.Empty,
Name = string.Empty
};
return(query.ToList <CaptureImageDTO>());
}
}
catch (Exception ex)
{
return(new List <CaptureImageDTO>());
}
}
public string Delete(int id)
{
string result = "";
try
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
CAPTURE_IMAGE att = db.CAPTURE_IMAGE.FirstOrDefault(v => v.Id == id);
if (att != null)
{
db.CAPTURE_IMAGE.Remove(att);
db.SaveChanges();
result = "SUCCESS";
}
else
{
result = "No se pudo eliminar el archivo.";
}
}
}
catch (Exception ex)
{
result = "No se pudo eliminar el archivo";
}
return(result);
}
public List <RationDTO> GetRationsByState(int pIdState, int pIdYear, int pIdMonth)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
try
{
var query = from ration in entities.RATIONs
where ration.Id_Year == pIdYear && ration.Id_Month == pIdMonth && ration.SOUP_KITCHEN.Id_State == pIdState
select new RationDTO
{
Id = ration.Id,
Description = ration.Description,
CreateDate = ration.CreateDate,
IsActive = ration.IsActive,
RationQuantity = ration.RationQuantity,
HasCoupon = ration.HasCoupon
};
return(query.ToList <RationDTO>());
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
public List <CaptureApprovalDTO> GetCaptureApprovalByCaptureId(int captureId)
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
var query = from cap in db.CAPTURE_APPROVAL.Where(f => f.Id_Capture == captureId)
select new CaptureApprovalDTO
{
Id = cap.Id,
Id_User = cap.Id_User,
Id_Capture = cap.Id_Capture,
Id_Status = cap.Id_Status,
CreateDate = cap.CreateDate,
UserDto = new UserDTO
{
Id = cap.USER.Id,
Name = cap.USER.Name,
LastName = cap.USER.LastName,
Username = cap.USER.Username,
UserType = new UserTypeDTO
{
Description = cap.USER.USER_TYPE.Description
}
},
StatusDto = new StatusDTO
{
Id = cap.STATUS.Id,
Description = cap.STATUS.Description
}
};
return(query.ToList <CaptureApprovalDTO>());
}
}
public string Delete(int id)
{
string result = "";
try
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
SOUP_KITCHEN_USER_TYPE att = db.SOUP_KITCHEN_USER_TYPE.FirstOrDefault(v => v.Id == id);
if (att != null)
{
db.SOUP_KITCHEN_USER_TYPE.Remove(att);
db.SaveChanges();
result = "SUCCESS";
}
else
{
result = "No se pudo eliminar el nivel de aprobación.";
}
}
}
catch (Exception ex)
{
result = "No se pudo eliminar el nivel de aprobación";
}
return(result);
}
public List <StateDTO> GetStates()
{
try
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
var query = from state in entities.STATEs
//join month in entities.MONTHs on capture.Id_Month equals month.Id
//join year in entities.YEARs on capture.Id_Year equals year.Id
//join status in entities.STATUS on capture.Id_Status equals status.Id
select new StateDTO
{
Id = state.Id,
Name = state.Name,
Abbreviation = state.Abbreviation,
IsActive = state.IsActive
};
return(query.ToList <StateDTO>());
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List <GencodeDayDTO> GetGenerationCodeDayAll()
{
List <GencodeDayDTO> list = new List <GencodeDayDTO>();
try
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
var query = from c in db.GEN_CODE_DAY
//where c.Id_User == userId
select new GencodeDayDTO
{
Id = c.Id,
Id_Month = c.Id_Month,
Id_Year = c.Id_Year,
Day = c.Day,
MonthDto = new MonthDTO
{
Id = c.MONTH.Id,
Description = c.MONTH.Description
},
YearDto = new YearDTO
{
Id = c.YEAR.Id,
Description = c.YEAR.Description
}
};
return(query.ToList <GencodeDayDTO>());
}
}
catch (Exception ex)
{
return(new List <GencodeDayDTO>());
}
}
public List <SoupKitchenDTO> GetSoupKitchenAll()
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
var query = from soupK in entities.SOUP_KITCHEN
select new SoupKitchenDTO
{
Id = soupK.Id,
Name = soupK.Name,
Description = soupK.Name + " | " + soupK.STATE.Name,
Capacity = soupK.Capacity,
Address = soupK.Address,
PhoneNumber = soupK.PhoneNumber,
ContactName = soupK.ContactName,
IsActive = soupK.IsActive,
Id_State = soupK.Id_State,
AllowAnonym = soupK.AllowAnonym,
Folio = soupK.Folio,
State = new StateDTO
{
Id = soupK.STATE.Id,
Name = soupK.STATE.Name,
IsActive = soupK.STATE.IsActive
},
RegionDto = new RegionDTO
{
Id = soupK.REGION.Id,
Name = soupK.REGION.Name
},
Id_Region = (int)soupK.Id_Region
};
return(query.ToList <SoupKitchenDTO>());
}
}
public SoupKitchenDTO GetSoupKitchenById(int id)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
var query = from soupK in entities.SOUP_KITCHEN
where soupK.Id == id
select new SoupKitchenDTO
{
Id = soupK.Id,
Name = soupK.Name,
Description = soupK.Description,
Capacity = soupK.Capacity,
Address = soupK.Address,
PhoneNumber = soupK.PhoneNumber,
ContactName = soupK.ContactName,
IsActive = soupK.IsActive,
Id_State = soupK.Id_State,
AllowAnonym = soupK.AllowAnonym,
Folio = soupK.Folio,
State = new StateDTO
{
Id = soupK.STATE.Id,
Name = soupK.STATE.Name,
IsActive = soupK.STATE.IsActive
},
RegionDto = new RegionDTO
{
Id = soupK.REGION.Id,
Name = soupK.REGION.Name
},
Id_Region = (int)soupK.Id_Region
};
return(query.FirstOrDefault());
}
}
public List <StateDTO> GetStatesFilter(int userTypeId, int userId)
{
try
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
var query = from state in entities.STATEs
select new StateDTO
{
Id = state.Id,
Name = state.Name,
Abbreviation = state.Abbreviation,
IsActive = state.IsActive
};
if (userTypeId != 2)
{
// obtener estados de comedores asignados al usuario y filtrar estados
var querySk = entities.USER_SOUP_KITCHEN.Where(p => p.Id_User == userId && p.IsActive == true).Distinct();
query = query.Where(e => querySk.Any(f => f.SOUP_KITCHEN.Id_State == e.Id));
}
return(query.ToList <StateDTO>());
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List <SoupKitchenDTO> GetSKAssignedUser(int id)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
var query = from soupK in entities.SOUP_KITCHEN
join userSk in entities.USER_SOUP_KITCHEN on soupK.Id equals userSk.Id_Soup_Kitchen
where userSk.Id_User == id
select new SoupKitchenDTO
{
Id = soupK.Id,
Name = soupK.Name,
Description = soupK.Name + " | " + soupK.STATE.Name,
Capacity = soupK.Capacity,
Address = soupK.Address,
PhoneNumber = soupK.PhoneNumber,
ContactName = soupK.ContactName,
IsActive = soupK.IsActive,
Id_State = soupK.Id_State,
AllowAnonym = soupK.AllowAnonym,
Folio = soupK.Folio,
State = new StateDTO
{
Id = soupK.STATE.Id,
Name = soupK.STATE.Name
},
RegionDto = new RegionDTO
{
Id = soupK.REGION.Id,
Name = soupK.REGION.Name
},
Id_Region = (int)soupK.Id_Region
};
return(query.ToList <SoupKitchenDTO>());
}
}
public UserDTO GetUser(UserValidationRequest req)
{
UserDTO uDto = new UserDTO();
using (SEDESOLEntities db = new SEDESOLEntities())
{
var query = from user in db.USERs
where user.Password == req.Password && user.Username == req.Username
select new UserDTO
{
Id = user.Id,
Email = user.Email,
Dni = user.Dni,
Username = user.Username,
Password = user.Password,
Name = user.Name,
LastName = user.LastName,
PhoneNumber = user.PhoneNumber,
IsActive = user.IsActive,
Id_User_Type = user.Id_User_Type,
UserType = new UserTypeDTO
{
Id = user.USER_TYPE.Id,
Description = user.USER_TYPE.Description,
IsActive = user.USER_TYPE.IsActive
}
};
return(query.FirstOrDefault());
}
}
public GencodeDayDTO GetGenerationCodeDayById(int Id)
{
List <GencodeDayDTO> list = new List <GencodeDayDTO>();
try
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
var query = from c in db.GEN_CODE_DAY
where c.Id == Id
select new GencodeDayDTO
{
Id = c.Id,
Id_Month = c.Id_Month,
Id_Year = c.Id_Year,
Day = c.Day,
MonthDto = new MonthDTO
{
Id = c.MONTH.Id,
Description = c.MONTH.Description
},
YearDto = new YearDTO
{
Id = c.YEAR.Id,
Description = c.YEAR.Description
}
};
return(query.FirstOrDefault());
}
}
catch (Exception ex)
{
return(new GencodeDayDTO());
}
}
public List <RationDTO> GetSearch(int pYear, int pMonth)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
try
{
var query = from ration in entities.RATIONs
join coup in entities.COUPONs on ration.Id equals coup.Id_Ration
select new RationDTO
{
Id = ration.Id,
Description = ration.Description,
CreateDate = ration.CreateDate,
IsActive = ration.IsActive,
RationQuantity = ration.RationQuantity,
HasCoupon = ration.HasCoupon,
MONTHDTO = new MonthDTO
{
Id = ration.MONTH.Id,
Description = ration.MONTH.Description,
IsActive = ration.MONTH.IsActive
},
YEARDTO = new YearDTO
{
Id = ration.YEAR.Id,
Description = ration.YEAR.Description,
IsActive = ration.YEAR.IsActive
},
SOUP_KITCHENDTO = new SoupKitchenDTO
{
Id = ration.SOUP_KITCHEN.Id,
Name = ration.SOUP_KITCHEN.Name,
Description = ration.SOUP_KITCHEN.Description,
Capacity = ration.SOUP_KITCHEN.Capacity,
Address = ration.SOUP_KITCHEN.Address,
PhoneNumber = ration.SOUP_KITCHEN.PhoneNumber,
ContactName = ration.SOUP_KITCHEN.ContactName,
IsActive = ration.SOUP_KITCHEN.IsActive,
AllowAnonym = ration.SOUP_KITCHEN.AllowAnonym,
Folio = ration.SOUP_KITCHEN.Folio,
State = new StateDTO
{
Id = ration.SOUP_KITCHEN.STATE.Id,
Name = ration.SOUP_KITCHEN.STATE.Name
}
},
Sequential = coup.Sequential
};
query = query.Where(e => e.YEARDTO.Id == pYear && e.MONTHDTO.Id == pMonth);
return(query.ToList <RationDTO>());
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
public void DeactivateStatus(int id)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
STATUS status = entities.STATUS.Find(id);
status.IsActive = false;
entities.SaveChanges();
}
}
public void DeactivateMonth(int id)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
MONTH month = entities.MONTHs.Find(id);
month.IsActive = false;
entities.SaveChanges();
}
}
public void ActivateState(int id)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
STATE state = entities.STATEs.Find(id);
state.IsActive = true;
entities.SaveChanges();
}
}
public void DeactivateYear(int id)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
YEAR year = entities.YEARs.Find(id);
year.IsActive = false;
entities.SaveChanges();
}
}
public GencodeDayDTO Save(GencodeDayDTO dto)
{
try
{
using (SEDESOLEntities db = new SEDESOLEntities())
{
GEN_CODE_DAY day = db.GEN_CODE_DAY.FirstOrDefault(v => v.Id == dto.Id);
if (day != null)
{
GEN_CODE_DAY day2 = db.GEN_CODE_DAY.FirstOrDefault(v => v.Id_Year == dto.Id_Year && v.Id_Month == dto.Id_Month && v.Id != dto.Id);
if (day2 != null)
{
dto.Message = "Se ha ingresado previamente una fecha para el mes y año seleccionados.";
}
else
{
day.Id_Month = dto.Id_Month;
day.Id_Year = dto.Id_Year;
day.Day = dto.Day;
if (db.SaveChanges() > 0)
{
dto.Id = day.Id;
dto.Message = "SUCCESS";
}
}
}
else
{
GEN_CODE_DAY day2 = db.GEN_CODE_DAY.FirstOrDefault(v => v.Id_Year == dto.Id_Year && v.Id_Month == dto.Id_Month);
if (day2 != null)
{
dto.Message = "Se ha ingresado previamente una fecha para el mes y año seleccionados.";
}
else
{
day = new GEN_CODE_DAY();
day.Id_Month = dto.Id_Month;
day.Id_Year = dto.Id_Year;
day.Day = dto.Day;
db.GEN_CODE_DAY.Add(day);
if (db.SaveChanges() > 0)
{
dto.Id = day.Id;
dto.Message = "SUCCESS";
}
}
}
return(dto);
}
}
catch (Exception ex)
{
return(new GencodeDayDTO());
}
}
public void Activate(int id)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
CAPTURE b = entities.CAPTUREs.Find(id);
b.IsActive = true;
entities.SaveChanges();
}
}
public void Activate(int id)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
USER sk = entities.USERs.Find(id);
sk.IsActive = true;
entities.SaveChanges();
}
}
public void Activate(int id)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
SOUP_KITCHEN sk = entities.SOUP_KITCHEN.Find(id);
sk.IsActive = true;
entities.SaveChanges();
}
}
public ProductDTO GetProductById(int IdProduct)
{
using (SEDESOLEntities entities = new SEDESOLEntities())
{
var query = from prod in entities.PRODUCTs
//join regProd in entities.REGION_PRODUCT on prod.Id equals regProd.Id_Product
//join region in entities.REGIONs on regProd.Id_Region equals region.Id
where prod.Id == IdProduct
select new ProductDTO
{
Id = prod.Id,
Description = prod.Description,
Measure = prod.Measure,
UnitMeasure = prod.UnitMeasure,
Momio = prod.Momio,
Price = prod.Price,
IsActive = prod.IsActive,
ListRegion = prod.REGION_PRODUCT.Where(x => x.IsActive == true).Select(a => new RegionDTO
{
Id = a.REGION.Id,
Name = a.REGION.Name,
Description = a.REGION.Description,
IsActive = a.REGION.IsActive
}).ToList <RegionDTO>()
};
var productQuery = query.FirstOrDefault();
var queryRegion = from region in entities.REGIONs
where region.IsActive == true
select new RegionDTO
{
Id = region.Id,
Name = region.Name,
Description = region.Description,
IsActive = region.IsActive
};
var listRegion = queryRegion.ToList <RegionDTO>();
foreach (var item in listRegion)
{
if (productQuery.ListRegion.Any(x => x.Id == item.Id))
{
item.IsAdded = true;
}
else
{
item.IsAdded = false;
}
}
productQuery.ListRegion = listRegion;
return(productQuery);
}
}
