static void Main(string[] args)
{
Faker.PopularBase();
var ctx = new Context();
var t1 = ctx.TipoParte.ToList();
var t2 = ctx.ClasseTipoParte.ToList();
var filter = PredicateBuilder.True <ClasseTipoParte>();
filter = filter.And(x => x.ExisteVinculo == true);
//filter = filter.And(x => x.CodigoClasse == 1);
var exp = filter.Compile();
var pageSettings = new PaginationSettings();
pageSettings.SortSettings.OrderBy = "CodigoClasse";
var result = (from classes in ctx.ClasseTipoParte
join tipoparte in ctx.TipoParte
on 1 equals 1 into Details
from m in Details.DefaultIfEmpty()
let tp = m
select new ClasseTipoParte
{
CodigoClasse = classes.CodigoClasse,
CodigoTipoParte = m.CodigoTipoParte,
Tipo = classes.Tipo,
TipoParte = classes.CodigoTipoParte == m.CodigoTipoParte ? tp : null,
ExisteVinculo = classes.CodigoTipoParte == m.CodigoTipoParte ? true : false,
}).Where(exp).AsQueryable().Page(pageSettings).ToList();
Console.WriteLine("Hello World!");
}
public IQueryable <UserCommentModel> GetAll(int bookId)
{
var result = from comments in _onlibContext.Comments
where comments.BookId == bookId
join userComments in _onlibContext.UsersComments
on comments.Id equals userComments.CommentId
join users in _onlibContext.Users
on userComments.UserId equals users.Id into Details
from m in Details.DefaultIfEmpty()
select new UserCommentModel
{
CommentId = comments.Id,
Comment = new CommentModel
{
Comment = comments.Comment,
BookId = comments.BookId,
Id = comments.Id,
},
UserId = m.Id,
User = new UserModel
{
Id = m.Id,
UserName = m.UserName,
FirstName = m.FirstName,
LastName = m.LastName
}
};
return(result);
}
//=====================================================================
// Page load
//=====================================================================
public async Task OnGetAsync()
{
ViewData["AreaId"] = new SelectList(_context.Area, "AreaId", "Classification");
ViewData["VisitorId"] = new SelectList(_context.Visitor, "VisitorId", "FirstName");
SiteList = await _context.Site.ToListAsync();
OrganisationList = await _context.Organisation.ToListAsync();
// generate a list of sites based on organisation
IQueryable <OrganisationSiteViewModel> orgSites =
from org in _context.Organisation
join site in _context.Site on org.OrganisationId equals site.OrganisationId into Details
from m in Details.DefaultIfEmpty()
where m.SiteId > 0
select new OrganisationSiteViewModel
{
OrgIDView = org.OrganisationId,
SiteIDView = m.SiteId,
OrgNameView = org.Name,
SiteStreetAddressView = m.StreetAddress
};
// display the organisation names when selecting organisation id
ViewData["SiteId"] = new SelectList(_context.Site, "SiteId", "City");
ViewData["OrganisationId"] = new SelectList(_context.Organisation, "OrganisationId", "Name");
OrgSiteList = orgSites.ToList();
}
//=====================================================================
// Get list of all sites and associated organisation
//=====================================================================
public IList <OrganisationSiteModel> GetOrganisationSites()
{
SiteList = _context.Site.ToList();
OrganisationList = _context.Organisation.ToList();
// generate a list of sites based on organisation
IQueryable <OrganisationSiteModel> orgSites =
from org in _context.Organisation
join site in _context.Site on org.OrganisationId equals site.OrganisationId into Details
from orgSite in Details.DefaultIfEmpty()
where orgSite.SiteId > 0
select new OrganisationSiteModel
{
OrgIDView = org.OrganisationId,
SiteIDView = orgSite.SiteId,
OrgNameView = org.Name,
SiteStreetAddressView = orgSite.StreetAddress,
SiteCityView = orgSite.City,
SiteStateView = orgSite.State,
SitePostCodeView = orgSite.PostCode,
};
OrganisationSiteList = orgSites.ToList();
return(OrganisationSiteList);
}
public async Task <UserDetailsVM> Handle(GetUserDetailQuery request, CancellationToken cancellationToken)
{
var a = await(from us in PmContext.Users
where us.Username == request.Username
select new { us.Username }
).FirstOrDefaultAsync();
var d = from role in PmContext.Roles
join userRole in PmContext.UserRoles.Where(item => item.Username == a.Username) on role.Name equals userRole.RoleName
into Details
from m in Details.DefaultIfEmpty()
select new UserRoleItemVM
{
IsAssigned = (m != null),
RoleName = role.Name
};
var e = d.ToList();
var c = new UserDetailsVM()
{
Password = "",
Username = a.Username,
UserRoles = e
};
if (c != null)
{
c.Password = "";
}
return(c);
}
// GET: /Usuarios/ @Html.ActionLink("Inicio", "Index", "Home")
public ActionResult Index()
{
if (User.Identity.IsAuthenticated == false || (GlobalVariables.Acceso("17") == false && GlobalVariables.Acceso("18") == false))
{
return(RedirectToAction("../Home/Index/"));
}
else
{
if (GlobalVariables.idUsuario == null || GlobalVariables.idUsuario == "" || GlobalVariables.idOrganizacion == null)
{
var usr_actual = User.Identity.GetUserName();
foreach (var item in db.MUB_USUARIOS.Where(u => u.EMAIL == usr_actual.ToString()))
{
GlobalVariables.idUsuario = item.ID_USUARIO.ToString();
GlobalVariables.idOrganizacion = item.ID_ORGANIZACION.ToString();
}
}
}
var ResultadoQuery = (from p in db.MUB_USUARIOS
join q in db.MUB_ORGANIZACIONES on p.ID_ORGANIZACION equals q.ID_ORGANIZACION into Details
from m in Details.DefaultIfEmpty()
select p).OrderBy(a => a.NOMBRE);
//return View(db.MUB_USUARIOS.Include(s => s.MUB_ORGANIZACIONES).ToList());
return(View(ResultadoQuery));
}
public IQueryable <DayDishViewModelPerGategory> EnabledDishesPerDay(DateTime daydate, int companyid)
{
var query1 = from dish in _context.Dish.Where(d => d.HotelId == companyid)
join dd in _context.DayDish.Where(d => d.HotelId == companyid && d.Date == daydate) on dish.Id equals dd.DishId into Details
from dayd in Details.DefaultIfEmpty()
where dayd.Date == daydate
select new DayDishViewModel
{
DishId = dish.Id,
CategoryId = dayd.CategoriesId,
PictureId = dish.PictureId,
//DishKindId = dayd.DishKindId,
DishName = dish.Name,
Date = daydate,
Enabled = dayd.Date == daydate, /*dayd != null*/
//CatId= cat.Id,
// CatName=cat.Name,
//CatCode=cat.Code
};
var query2 = from cat in _context.Categories
select new DayDishViewModelPerGategory()
{
CategoryId = cat.Id,
CategoryCode = cat.Code,
CategoryName = cat.Name,
CategoryDate = daydate,
DayDishes = from dd in query1.Where(q => q.CategoryId == cat.Id)
select new DayDishViewModel()
{
Date = dd.Date,
DishId = dd.DishId,
DishName = dd.DishName,
IsWeight = dd.IsWeight,
CategoryId = cat.Id,
//DishKindId = dd.DishKindId,
PictureId = dd.PictureId,
Enabled = dd.Enabled
}
};
//var ordered = _udaydishrepo.OrderedDishesDay(daydate, userId, companyid);
//List<DayDishViewModelPerGategory> res = new List<DayDishViewModelPerGategory>();
//query2.ForEachAsync(c => {
// res.Add(c);
// c.DayDishes.ToList().ForEach(d => {
// var ordDish = ordered.FirstOrDefault(ord => ord.CategoryId == d.CategoryId && ord.DishId == d.DishId && ord.DishKindId == DishKindId);
// if (ordDish != null)
// {
// res.Last().DayDishes.ToList().Remove(d);
// }
// });
//});
query2 = query2.OrderBy(c => c.CategoryCode);
return(query2);
}
public ActionResult Index([Bind(Include = "ID_USUARIO,NOMBRE,CARGO,DIRECCION,TELEFONO,CELULAR,EXTENSION,FAX,EMAIL,ESTADO,PWDHASH,ID_ORGANIZACION")] MUB_USUARIOS mub_usuarios)
{
var txt_buscar = Request.Form["txt_buscar"];
if (txt_buscar == "" || txt_buscar == null)
{ //RETORNA A LA LISTA DE INICIO
return(RedirectToAction("Index", "Mapas"));
}
var ResultadoQuery = (from p in db.MUB_USUARIOS
join q in db.MUB_ORGANIZACIONES on p.ID_ORGANIZACION equals q.ID_ORGANIZACION into Details
from m in Details.DefaultIfEmpty()
select p).Where(h => h.NOMBRE.Contains(txt_buscar)).OrderBy(a => a.NOMBRE);
return(View(ResultadoQuery));
}
//000000000
public List <SecurityUserAccess> GetByUserAppForm(SecurityUser pSecurityUser, SecurityForm pSecurityForm)
{
List <SecurityUserAccess> vList = new List <SecurityUserAccess>();
IRepository <SecurityUserAccess> repSUA = new Repository <SecurityUserAccess>(ctx);
try
{
var query = from a in ctx.SecurityFormActions
join b in ctx.SecurityUserAccesses.Where(o => o.UserCode == pSecurityUser.UserCode) on
new { a.ApplicationCode, a.FormCode, a.FormActionCode } equals new { b.ApplicationCode, b.FormCode, b.FormActionCode } into Details
from ua in Details.DefaultIfEmpty()
where a.ApplicationCode == pSecurityForm.ApplicationCode && a.FormCode == pSecurityForm.FormCode
select new { a, ua };
//string strQuery = "exec sp_SecurityUserActionALL @UserID='" + pSecurityUser.ID + "', @UserCode='" + pSecurityUser.UserCode + "', @FormID='" + pSecurityForm.ID + "'";
foreach (var eachItem in query)
{
if (eachItem.ua == null)
{
SecurityUserAccess oNew = new SecurityUserAccess();
oNew.UserCode = pSecurityUser.UserCode;
oNew.ApplicationCode = eachItem.a.ApplicationCode;
oNew.FormCode = eachItem.a.FormCode;
oNew.FormName = pSecurityForm.FormName;
oNew.FormActionCode = eachItem.a.FormActionCode;
oNew.FormActionName = eachItem.a.FormActionName;
oNew.ActionValue = "N";
vList.Add(oNew);
}
else
{
eachItem.ua.FormActionName = eachItem.a.FormActionName;
vList.Add(eachItem.ua);
}
}
return(vList);
}
catch (Exception ex)
{
throw ex;
}
}
public ActionResult GetAllAnsweredPersonalityQuestions()
{
try
{
if (string.IsNullOrEmpty(Convert.ToString(Request.Headers["api_key"])))
{
return(StatusCode(StatusCodes.Status403Forbidden, (new { Status = "Error", Error = "Invalid ApiKey" })));
}
string strUserId = UserLoginSessionKeys.GetUserIdByApiKey(_appDbContext, Guid.Parse(Request.Headers["api_key"]));
if (string.IsNullOrEmpty(strUserId))
{
return(StatusCode(StatusCodes.Status403Forbidden, (new { Status = "Error", Error = "Invalid ApiKey" })));
}
var objPersonalityQuestions = from upqd in _appDbContext.UserPersonalityQuestionaireDetails
join pq in _appDbContext.PersonalityQuestionaire on upqd.PersonalityQuestonId equals pq.Id into Details
from pq1 in Details.DefaultIfEmpty()
where (upqd.UserId == strUserId)
select new
{
UserId = upqd.UserId,
PersonalityQuestonId = upqd.PersonalityQuestonId,
PersonalityQAnswer = upqd.PersonalityQAnswer,
PersonalityQuestion = pq1.PersonalityQuestion,
Option1 = pq1.Option1,
Option2 = pq1.Option2,
Option3 = pq1.Option3,
Option4 = pq1.Option4,
MediaAssociatedId = pq1.MediaAssociatedId
};
if (objPersonalityQuestions == null || objPersonalityQuestions.Count() <= 0)
{
return(StatusCode(StatusCodes.Status500InternalServerError, (new { Status = "Error", Error = "No result found." })));
}
return(Ok(new { Status = "OK", PersonalityQuestion = objPersonalityQuestions }));
}
catch (Exception ex)
{
return(StatusCode(StatusCodes.Status500InternalServerError, (new { Status = "Error", Error = "Internal Server error. Please contact customer support", SystemError = ex.Message })));
}
}
private ICollection <RequisitoTupaResponse> GetRequisitos(int id_documento, int?id_tupa)
{
if (id_tupa == null)
{
return(null);
}
var requisitos = _context.Query <Modelo.vw_dat_requisito_tupa>()
.Where(x => x.ID_TUPA == id_tupa && x.ESTADO == ESTADO_REQUISITO_TUPA.ACTIVO);
var osbervaciones = _context.Query <Modelo.observaciones_requisitos_tramite>()
.Where(x => x.ID_DOCUMENTO == id_documento && x.ESTADO != ESTADO_OBSERVACION_REQUISITO.ELIMINADO && x.ID_REQUISITO_TUPA != 0);
var result = (from requisito in requisitos
join observacion in osbervaciones on requisito.ID_REQUISITO equals observacion.ID_REQUISITO_TUPA into Details
from req in Details.DefaultIfEmpty()
select new RequisitoTupaResponse
{
id_requisito = requisito.ID_REQUISITO,
descripcion = requisito.DESCRIPCION,
id_tupa = requisito.ID_TUPA,
numero_requisito = requisito.NUMERO_REQUISITO,
estado = requisito.ESTADO,
id_tipo_requisito = requisito.ID_TIPO_REQUISITO,
valor_uit = requisito.VALOR_UIT,
monto = requisito.MONTO,
estado_observacion = req.ESTADO == null || req.ESTADO == ESTADO_OBSERVACION_REQUISITO.SUBSANADO ? true : false,
observaciones = req.OBSERVACIONES
}).ToList();
var observacion_general = _context.Query <Modelo.observaciones_requisitos_tramite>()
.Where(x => x.ID_DOCUMENTO == id_documento && x.ID_REQUISITO_TUPA == 0 && x.ESTADO != ESTADO_OBSERVACION_REQUISITO.ELIMINADO).FirstOrDefault();
if (observacion_general == null)
{
return(result.ToList());
}
result.Add(new RequisitoTupaResponse
{
observaciones = observacion_general.OBSERVACIONES
});
return(result);
}
public async Task <IActionResult> GetFollowing(int userid)
{
var followings = await _repo.Getfollowing(userid);
var result = from f in followings
join post in _context.Posts on f.Following_AccountId equals post.AccountId
join acc in _context.Accounts on f.Following_AccountId equals acc.Id
where post.Status == 1 && acc.Status == 1
select new
{
AccountId = post.AccountId,
PostId = post.Id,
Name = acc.Name,
Text = post.Text,
Link = post.Video_link,
Date = post.Created_date
};
var likes = from pl in _context.Post_Likes
join ac in _context.Accounts on pl.AccountId equals ac.Id
group pl by pl.Post_id into g
select new {
Post = g.Key,
Likes = g.Count()
};
var res = from r in result
join like in likes on r.PostId equals like.Post into Details
from m in Details.DefaultIfEmpty()
select new
{
AccountId = r.AccountId,
PostId = r.PostId,
Name = r.Name,
Text = r.Text,
Link = r.Link,
Date = r.Date,
Like = m?.Likes ?? 0
};
res = res.OrderByDescending(o => o.Date);
return(Ok(res));
}
public async Task <IActionResult> GetUserPosts(int id)
{
var posts = await _repo.GetPosts();
posts = posts.Where(i => i.AccountId == id);
var result = from post in posts
join acc in _context.Accounts on post.AccountId equals acc.Id
select new
{
AccountId = post.AccountId,
PostId = post.Id,
Name = acc.Name,
Text = post.Text,
Link = post.Video_link,
Date = post.Created_date
};
var likes = from pl in _context.Post_Likes
join ac in _context.Accounts on pl.AccountId equals ac.Id
group pl by pl.Post_id into g
select new {
Post = g.Key,
Likes = g.Count()
};
var res = from r in result
join like in likes on r.PostId equals like.Post into Details
from m in Details.DefaultIfEmpty()
select new
{
AccountId = r.AccountId,
PostId = r.PostId,
Name = r.Name,
Text = r.Text,
Link = r.Link,
Date = r.Date,
Like = m?.Likes ?? 0
};
res = res.OrderByDescending(i => i.Date);
return(Ok(res));
}
public IEnumerable <SecurityUserApplication> GetByUserCode(string pUserCode)
{
List <SecurityUserApplication> vList = new List <SecurityUserApplication>();
//IRepository<SecurityApplication> repSA = new Repository<SecurityApplication>(ctx);
try
{
var query = from app in ctx.SecurityApplications
join userApp in ctx.SecurityUserApplications.Where(o => o.UserCode == pUserCode) on
app.ApplicationCode equals userApp.ApplicationCode into Details
from ua in Details.DefaultIfEmpty()
select new { app.ID, app.ApplicationCode, app.ApplicationName, ua };
foreach (var eachItem in query)
{
if (eachItem.ua != null)
{
eachItem.ua.ApplicationID = eachItem.ID;
eachItem.ua.ApplicationName = eachItem.ApplicationName;
vList.Add(eachItem.ua);
}
else
{
SecurityUserApplication oNew = new SecurityUserApplication();
oNew.ApplicationID = eachItem.ID;
oNew.ApplicationCode = eachItem.ApplicationCode;
oNew.UserCode = pUserCode;
oNew.AllowFlag = "N";
oNew.ApplicationName = eachItem.ApplicationName;
vList.Add(oNew);
}
}
return(vList);
}
catch (Exception ex)
{
throw ex;
}
}
public List <SecurityUserRole> GetByUserCode(string pUserCode)
{
List <SecurityUserRole> vList = new List <SecurityUserRole>();
IRepository <SecurityRole> repSA = new Repository <SecurityRole>(ctx);
try
{
var query = from rol in ctx.SecurityRoles
join userRol in ctx.SecurityUserRoles.Where(o => o.UserCode == pUserCode) on
rol.RoleCode equals userRol.RoleCode into Details
from ur in Details.DefaultIfEmpty()
select new { rol, ur };
foreach (var eachItem in query)
{
if (eachItem.ur == null)
{
SecurityUserRole oNew = new SecurityUserRole();
oNew.RoleCode = eachItem.rol.RoleCode;
oNew.RoleName = eachItem.rol.RoleName;
oNew.UserCode = pUserCode;
oNew.EnableFlag = "N";
vList.Add(oNew);
}
else
{
eachItem.ur.RoleName = eachItem.rol.RoleName;
vList.Add(eachItem.ur);
}
}
return(vList);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <ActionResult <Users> > Get()
{
_logger.LogInformation(LoggingEvents.ListItems, "LİSTİNG ALL USERS DATA");
try
{
using (var db = new WebApiContext())
{
var list = (from t1 in db.Users
join detail in db.Products on t1.User_ID equals detail.Product_ID into Details
from m in Details.DefaultIfEmpty()
select new { t1.Username, t1.Products }).ToList();
_logger.LogInformation(LoggingEvents.ListItems, "GET LİST DATA Successful");
return(Ok(list));
}
}
catch (Exception ex)
{
_logger.LogError(LoggingEvents.Error, ex, "AN ERROR OCCURRED.");
return(null);
}
}// THE END GET ALL
public List <SecurityRoleApplication> GetByRole(SecurityRole pRole)
{
List <SecurityRoleApplication> vList = new List <SecurityRoleApplication>();
IRepository <SecurityApplication> repSA = new Repository <SecurityApplication>(ctx);
try
{
var query = from app in ctx.SecurityApplications
join roleApp in ctx.SecurityRoleApplications.Where(o => o.RoleCode == pRole.RoleCode) on
app.ApplicationCode equals roleApp.ApplicationCode into Details
from rapp in Details.DefaultIfEmpty()
select new { app, rapp };
foreach (var eachItem in query)
{
if (eachItem.rapp == null)
{
SecurityRoleApplication oNew = new SecurityRoleApplication();
oNew.ApplicationCode = eachItem.app.ApplicationCode;
oNew.RoleCode = pRole.RoleCode;
oNew.AllowFlag = "N";
oNew.ApplicationName = eachItem.app.ApplicationName;
vList.Add(oNew);
}
else
{
eachItem.rapp.ApplicationName = eachItem.app.ApplicationName;
vList.Add(eachItem.rapp);
}
}
return(vList);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <ActionResult <Users> > Get(int id)
{
_logger.LogInformation(LoggingEvents.GetItem, "GET USER DATA ITEMS {ID}", id);
try
{
using (var db = new WebApiContext())
{
var list = (from t1 in db.Users
join detail in db.Products on t1.User_ID equals detail.Product_ID into Details
from m in Details.DefaultIfEmpty()
where (t1.User_ID == id)
select new { t1.Username, t1.Products }).ToList();
_logger.LogInformation(LoggingEvents.GetItem, "GET USER DATA ITEM {ID} Successful", id);
return(Ok(list));
}
}
catch (Exception ex)
{
_logger.LogError(LoggingEvents.Error, ex, "GetById{ID} AN ERROR OCCURRED.");
return(null);
}
}// THE END GET
public async Task <IActionResult> GetVisitedProfiles(int id)
{
/*if(id != int.Parse(User.FindFirst(ClaimTypes.NameIdentifier).Value))
* return Unauthorized();*/
var visited_profiles = await _repo.GetVisitedProfiles(id);
var ans = preference(id);
IEnumerable <Id_PercentageDto> firstThreeItems;
firstThreeItems = ans.Take(3);
firstThreeItems = firstThreeItems.OrderBy(i => i.Id);
var sumOfPercentage = firstThreeItems.Sum(i => i.Percentage);
if (sumOfPercentage == 0.0)
{
var pre = await _repo.GetAccountsPreference(id);
List <Id_PercentageDto> parts = new List <Id_PercentageDto>();
foreach (var p in pre)
{
parts.Add(new Id_PercentageDto {
Id = p.Music_type_id, Percentage = 33
});
}
firstThreeItems = parts;
}
var accounts = await _repo.GetAccounts();
accounts = accounts.Where(i => i.Id != id);
var accountToReturn = _mapper.Map <IEnumerable <AccountToReturnDto> >(accounts);
List <Id_PercentageDto> account_percentage = new List <Id_PercentageDto>();
foreach (var account in accounts)
{
var music_type = preference(account.Id);
var percent = 0.0;
foreach (var element in firstThreeItems)
{
foreach (var type in music_type)
{
if (element.Id == type.Id)
{
percent += type.Percentage;
}
}
}
account_percentage.Add(new Id_PercentageDto {
Id = account.Id, Percentage = percent
});
}
account_percentage = account_percentage.OrderByDescending(i => i.Percentage).ToList();
var dateCriteria = DateTime.Now.Date.AddDays(-7);
var likes = from pl in _context.Post_Likes
join ac in _context.Accounts on pl.AccountId equals ac.Id
group pl by pl.Post_id into g
select new {
Post = g.Key,
Likes = g.Count()
};
var result = from ap in account_percentage
join post in _context.Posts on ap.Id equals post.AccountId
join acc in _context.Accounts on post.AccountId equals acc.Id
where post.Created_date > dateCriteria && post.Status == 1 && acc.Status == 1
select new
{
AccountId = post.AccountId,
PostId = post.Id,
Name = acc.Name,
Text = post.Text,
Link = post.Video_link,
Date = post.Created_date
};
var res = from r in result
join like in likes on r.PostId equals like.Post into Details
from m in Details.DefaultIfEmpty()
select new
{
AccountId = r.AccountId,
PostId = r.PostId,
Name = r.Name,
Text = r.Text,
Link = r.Link,
Date = r.Date,
Like = m?.Likes ?? 0
};
/* foreach(var p in result)
* {
* var like = await _repo.GetLike(id,p.PostId);
* if(like != null ) p.Like = true;
* }*/
return(Ok(res));
}
public IQueryable <DayDishViewModelPerGategory> CategorizedDishesPerDay(DateTime daydate, int companyid)
{
var query1 = from dish in _context.Dish.Where(d => d.HotelId == companyid)
join dd in _context.DayDish.Where(d => d.HotelId == companyid && d.Date == daydate) on dish.Id equals dd.DishId into Details
from dayd in Details.DefaultIfEmpty()
select new DayDishViewModel
{
DishId = dish.Id,
CategoryId = dish.CategoriesId,
PictureId = dish.PictureId,
DishName = dish.Name,
Date = daydate,
Enabled = dayd.Date == daydate, /*dayd != null*/
//CatId= cat.Id,
// CatName=cat.Name,
//CatCode=cat.Code
};
var query2 = from cat in _context.Categories
select new DayDishViewModelPerGategory()
{
CategoryCode = cat.Code,
CategoryName = cat.Name,
DayDishes = from dd in query1.Where(q => q.CategoryId == cat.Id) select new DayDishViewModel()
{
Date = dd.Date,
DishId = dd.DishId,
DishName = dd.DishName,
IsWeight = dd.IsWeight,
PictureId = dd.PictureId,
Enabled = dd.Enabled
}
};
/* !! not more working on EF 3.0*/
/*
* var query = from entry in
* (
* from dish in _context.Dishes
* where dish.CompanyId == companyid
* //join cat in _context.Categories on dish.CategoriesId equals cat.Id
* join dd in (from subday in _context.DayDish where subday.Date == daydate && subday.CompanyId == companyid select subday ) on dish.Id equals dd.DishId into Details
*
* from dayd in Details.DefaultIfEmpty()
* select new {
* DishId = dish.Id,
* CategoryID = dish.CategoriesId,
* DishName = dish.Name,
* Date = daydate,
* Enabled = dayd.Date== daydate
* }
* )
* group entry by entry.CategoryID into catgroup
* join cat in _context.Categories on new { id = catgroup.Key, cid = companyid } equals new { id = cat.Id, cid = cat.CompanyId }
* orderby cat.Code
* select new DayDishViewModelPerGategory()
* {
* CategoryCode = cat.Code,
* CategoryName = cat.Name,
* DayDishes = from dentry in catgroup
* select new DayDishViewModel()
* {
* DishId = dentry.DishId,
* DishName = dentry.DishName,
* Date = dentry.Date,
* Enabled = dentry.Enabled
* }
* };
* // group dish by dish.CategoriesId into catGroup
* // select new DayDishViewModelPerGategory() {CategoryCode=cat;
*
*
*/
return(query2);
}
public ListDataResponse <GetItemsResponse> GetItemsByPurchaseId(int purchaseId)
{
ListDataResponse <GetItemsResponse> response = new ListDataResponse <GetItemsResponse>();
try
{
var res = _appContext.Inventories.GroupBy(n => new { n.PurchaseOrderId, n.ItemId })
.Select(g => new
{
PurchaseOrderId = g.Key.PurchaseOrderId,
ItemId = g.Key.ItemId,
ReceivedCost = 0,
Quantity = g.Sum(x => x.Quantity)
}).Where(x => x.PurchaseOrderId == purchaseId).ToList();
//var res = _appContext.Inventories.GroupBy(n => new { n.PurchaseOrderId, n.ItemId, n.ReceivedCost, n.Quantity })
//.Select(g => new
//{
// PurchaseOrderId = g.Key.PurchaseOrderId,
// ItemId = g.Key.ItemId,
// ReceivedCost = g.Key.ReceivedCost,
// Quantity = g.Key.Quantity
//}).Where(x => x.PurchaseOrderId == purchaseId).ToList();
var result = (from pi in _appContext.PurchageItemXrefs.Include(x => x.Item_Id).Include(x => x.Purchage_Id).Where(x => x.PurchageId == purchaseId).ToList()
join iv in res on pi.ItemId equals iv.ItemId into Details
from m in Details.DefaultIfEmpty()
select new GetItemsResponse
{
PurchaseId = pi.PurchageId,
PurchaseReference = pi.Purchage_Id.PurchaseReference,
ItemReference = pi.Item_Id.ItemReference,
ItemId = pi.ItemId,
ItemName = pi.Item_Id.Name1,
Quantity = pi.Quantity,
ExpectedCost = pi.ExpectdCost,
Comments = pi.Comments,
RemainingQuantity = m != null ? pi.Quantity - m.Quantity : 0,
ReceivedQuantity = m != null ? m.Quantity : 0,
ReceivedCost = m != null ? 0.0 : 0.0
}).Where(x => x.PurchaseId == purchaseId).Distinct().ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Item Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Item Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}