public PagedResponse <ProductionDto> Execute(ProductionQuery request) { var query = Context.Production.AsQueryable(); query = query.Where(p => p.IsDeleted == false); if (request.Name != null) { query = query.Where(p => p.Name.ToLower().Contains(request.Name.ToLower())); } var totalCount = query.Count(); query = query.Skip((request.PageNumber - 1) * request.PerPage).Take(request.PerPage); var pagesCount = (int)Math.Ceiling((double)totalCount / request.PerPage); return(new PagedResponse <ProductionDto> { CurrentPage = request.PageNumber, PagesCount = pagesCount, TotalCount = totalCount, Data = query.Select(p => new ProductionDto { Id = p.Id, Name = p.Name }) }); }
public bool IsProductionQueryExisting(ProductionQuery productionQuery) { try { if (productionQuery == null) { return(false); } using (var db = new QueryBuilderEntities()) { if (db.ProductionQueries.Any()) { if (db.ProductionQueries.Count(m => m.ProductId == productionQuery.ProductId && m.CompanyId == productionQuery.CompanyId && m.FieldId == productionQuery.FieldId && (m.StartDate != null && m.StartDate.Value.Year == productionQuery.StartDate.Value.Year && productionQuery.StartDate != null) && (m.EndDate != null && m.EndDate.Value.Year == productionQuery.EndDate.Value.Year && productionQuery.EndDate != null) && m.BlockId == productionQuery.BlockId ) > 0) { return(true); } return(false); } return(false); } } catch (Exception ex) { ErrorLogger.LogEror(ex.StackTrace, ex.Source, ex.Message); return(false); } }
public ActionResult GetProductions(ProductionQuery queryBuilder) { if (queryBuilder == null) { return(Json(new List <ProductionObject>(), JsonRequestBehavior.AllowGet)); } if (queryBuilder.BlockId < 1 && queryBuilder.CompanyId < 1 && ((queryBuilder.StartDate != null && queryBuilder.StartDate.Value.Year == 0001) || (queryBuilder.StartDate == null)) && ((queryBuilder.EndDate != null && queryBuilder.EndDate.Value.Year == 0001) || (queryBuilder.EndDate == null)) && queryBuilder.FieldId < 1 && queryBuilder.ProductId < 1 && queryBuilder.TerrainId < 1 && queryBuilder.ZoneId < 1) { return(Json(new List <ProductionObject>(), JsonRequestBehavior.AllowGet)); } var productionList = new ProductionServices().GetOrderedProductionReportObjects(queryBuilder) ?? new List <ProductionObject>(); if (!productionList.Any()) { return(Json(new List <ProductionObject>(), JsonRequestBehavior.AllowGet)); } Session["_successfulProductionQuery"] = queryBuilder; var jsonResult = Json(productionList, JsonRequestBehavior.AllowGet); jsonResult.MaxJsonLength = int.MaxValue; return(jsonResult); }
// GET: Productions public ActionResult Index([FromQuery] ProductionQuery query) { try { return(View(executor.ExecuteQuery(getProductions, query))); } catch (EntityNotAllowedException) { return(RedirectToAction("PageNotFound", "Redirections")); } catch (Exception e) { TempData["error"] = e.Message; } return(RedirectToAction("Home", "Index")); }
public IActionResult Get([FromQuery] ProductionQuery query) { try { return(Ok(getProductions.Execute(query))); } catch (Exception e) { return(StatusCode(500, new { Errors = new List <string> { e.Message } })); } }
public long AddProductionQueryCheckDuplicate(ProductionQuery productionQuery) { try { if (productionQuery == null) { return(-2); } using (var db = new QueryBuilderEntities()) { if (db.ProductionQueries.Any()) { if (db.ProductionQueries.Count(m => (m.ProductId == productionQuery.ProductId && m.CompanyId == productionQuery.CompanyId && m.FieldId == productionQuery.FieldId && (m.StartDate != null && m.StartDate.Value.Year == productionQuery.StartDate.Value.Year && productionQuery.StartDate != null) && (m.EndDate != null && m.EndDate.Value.Year == productionQuery.EndDate.Value.Year && productionQuery.EndDate != null) && m.BlockId == productionQuery.BlockId) ) > 0) { return(-3); } if (db.ProductionQueries.Count(m => m.ProductionQueryName.ToLower().Trim().Replace(" ", "") == productionQuery.ProductionQueryName.ToLower().Trim().Replace(" ", "") ) > 0) { return(-4); } } var processedProductionQuery = db.ProductionQueries.Add(productionQuery); db.SaveChanges(); return(processedProductionQuery.ProductionQueryId); } } catch (Exception ex) { ErrorLogger.LogEror(ex.StackTrace, ex.Source, ex.Message); return(0); } }
public List <ProductionObject> GetOrderedProductionReportObjects(ProductionQuery queryBuilder) { try { using (var db = new DPRDataMigrationEngineDBEntities()) { if (queryBuilder != null) { if (queryBuilder.StartDate != null && queryBuilder.StartDate.Value.Year > 0001 && queryBuilder.EndDate != null && queryBuilder.EndDate.Value.Year > 0001) { var startYear = queryBuilder.StartDate.Value.Year; var startMonth = queryBuilder.StartDate.Value.Month; var endYear = queryBuilder.EndDate.Value.Year; var endMonth = queryBuilder.EndDate.Value.Month; var query = ( from p in db.Productions.Where(m => (queryBuilder.FieldId > 0 && m.FieldId == queryBuilder.FieldId && queryBuilder.ProductId < 1 && ((m.Year >= startYear && m.Month >= startMonth) && (m.Year <= endYear && m.Month <= endMonth))) || (queryBuilder.ProductId > 0 && queryBuilder.FieldId < 1 && m.ProductId == queryBuilder.ProductId && ((m.Year >= startYear && m.Month >= startMonth) && (m.Year <= endYear && m.Month <= endMonth))) || (queryBuilder.FieldId > 0 && queryBuilder.ProductId > 0 && queryBuilder.ProductId > 0 && queryBuilder.FieldId > 0 && m.FieldId == queryBuilder.FieldId && m.ProductId == queryBuilder.ProductId && ((m.Year >= startYear && m.Month >= startMonth) && (m.Year <= endYear && m.Month <= endMonth))) || (queryBuilder.FieldId < 1 && queryBuilder.ProductId < 1 && ((m.Year >= startYear && m.Month >= startMonth) && (m.Year <= endYear && m.Month <= endMonth)))) join f in db.Fields on p.FieldId equals f.FieldId where ((queryBuilder.FieldId > 0 && f.FieldId == queryBuilder.FieldId) || (f.FieldId == p.FieldId && queryBuilder.FieldId < 1)) join c in db.Companies on f.CompanyId equals c.CompanyId where ((queryBuilder.CompanyId > 0 && c.CompanyId == queryBuilder.CompanyId) || (queryBuilder.CompanyId < 1 && c.CompanyId == f.CompanyId)) join z in db.Zones on f.ZoneId equals z.ZoneId //where ((queryBuilder.ZoneId > 0 && z.ZoneId == queryBuilder.ZoneId && z.ZoneId == f.ZoneId) || (z.ZoneId == f.ZoneId && queryBuilder.ZoneId < 1)) join t in db.Terrains on f.TerrainId equals t.TerrainId //where ((queryBuilder.TerrainId > 0 && // t.TerrainId == queryBuilder.TerrainId) || (queryBuilder.TerrainId < 1 && t.TerrainId == f.TerrainId)) join pd in db.Products on p.ProductId equals pd.ProductId where ((queryBuilder.ProductId > 0 && pd.ProductId == queryBuilder.ProductId) || (queryBuilder.ProductId < 1 && p.ProductId == pd.ProductId)) join b in db.Blocks on f.BlockId equals b.BlockId where ((queryBuilder.BlockId > 0 && b.BlockId == queryBuilder.BlockId) || (b.BlockId == f.BlockId && queryBuilder.BlockId < 1)) select new ProductionObject() { ProductName = pd.Name, BlockName = b.Name, FieldName = f.Name, CompanyName = c.Name, ZoneName = z.Name, TerrainName = t.Name, Quantity = p.Quantity, Year = p.Year, Month = p.Month, Remark = p.Remark, APIGravity = p.APIGravity }).ToList(); if (!query.Any()) { return(new List <ProductionObject>()); } query.ForEach(j => { var quantityOut = 0; var result2 = int.TryParse(j.Quantity, out quantityOut); if (result2) { j.Quantity = Convert.ToDecimal(j.Quantity).ToString("#,##0"); } else { j.Quantity = "0"; } var monthOut = j.Month; if (monthOut > 0) { j.Period = Enum.GetName(typeof(Months), monthOut) + "/" + j.Year; } if (monthOut < 1) { j.Period = j.Year.ToString(CultureInfo.InvariantCulture); } }); return(query.OrderBy(v => v.ProductName).ToList()); } if (queryBuilder.StartDate != null && queryBuilder.StartDate.Value.Year > 0001 && ((queryBuilder.EndDate != null && queryBuilder.EndDate.Value.Year == 0001) || (queryBuilder.EndDate == null))) { var startYear = queryBuilder.StartDate.Value.Year; var startMonth = queryBuilder.StartDate.Value.Month; var query = ( from p in db.Productions.Where(m => (queryBuilder.FieldId > 0 && m.FieldId == queryBuilder.FieldId && queryBuilder.ProductId < 1 && (m.Year == startYear && m.Month == startMonth)) || (queryBuilder.ProductId > 0 && queryBuilder.FieldId < 1 && m.ProductId == queryBuilder.ProductId && (m.Year == startYear && m.Month == startMonth)) || (queryBuilder.FieldId > 0 && queryBuilder.ProductId > 0 && queryBuilder.ProductId > 0 && queryBuilder.FieldId > 0 && m.FieldId == queryBuilder.FieldId && m.ProductId == queryBuilder.ProductId && (m.Year == startYear && m.Month == startMonth)) || (queryBuilder.FieldId < 1 && queryBuilder.ProductId < 1 && (m.Year == startYear && m.Month == startMonth))) join f in db.Fields on p.FieldId equals f.FieldId where ((queryBuilder.FieldId > 0 && f.FieldId == queryBuilder.FieldId) || (f.FieldId == p.FieldId && queryBuilder.FieldId < 1)) join c in db.Companies on f.CompanyId equals c.CompanyId where ((queryBuilder.CompanyId > 0 && c.CompanyId == queryBuilder.CompanyId) || (queryBuilder.CompanyId < 1 && c.CompanyId == f.CompanyId)) join z in db.Zones on f.ZoneId equals z.ZoneId //where ((queryBuilder.ZoneId > 0 && z.ZoneId == queryBuilder.ZoneId && z.ZoneId == f.ZoneId) || (z.ZoneId == f.ZoneId && queryBuilder.ZoneId < 1)) join t in db.Terrains on f.TerrainId equals t.TerrainId //where ((queryBuilder.TerrainId > 0 && // t.TerrainId == queryBuilder.TerrainId) || (queryBuilder.TerrainId < 1 && t.TerrainId == f.TerrainId)) join pd in db.Products on p.ProductId equals pd.ProductId where ((queryBuilder.ProductId > 0 && pd.ProductId == queryBuilder.ProductId) || (queryBuilder.ProductId < 1 && p.ProductId == pd.ProductId)) join b in db.Blocks on f.BlockId equals b.BlockId where ((queryBuilder.BlockId > 0 && f.BlockId == queryBuilder.BlockId) || (b.BlockId == f.BlockId && queryBuilder.BlockId < 1)) select new { pp = p, pdd = pd, bb = b, ff = f, cc = c, zz = z, tt = t, }).ToList(); if (!query.Any()) { return(new List <ProductionObject>()); } var xlt = new List <ProductionObject>(); query.ForEach(m => { var p = m.pp; var pd = m.pdd; var b = m.bb; var f = m.ff; var c = m.cc; var z = m.zz; var t = m.tt; var j = new ProductionObject() { ProductName = pd.Name, BlockName = b.Name, FieldName = f.Name, CompanyName = c.Name, ZoneName = z.Name, TerrainName = t.Name, Quantity = p.Quantity, Year = p.Year, Month = p.Month, Remark = p.Remark, APIGravity = p.APIGravity }; var quantityOut = 0; var result2 = int.TryParse(j.Quantity, out quantityOut); if (result2) { j.Quantity = Convert.ToDecimal(j.Quantity).ToString("#,##0"); } else { j.Quantity = "0"; } var monthOut = j.Month; if (monthOut > 0) { j.Period = Enum.GetName(typeof(Months), monthOut) + "/" + j.Year; } if (monthOut < 1) { j.Period = j.Year.ToString(CultureInfo.InvariantCulture); } xlt.Add(j); }); return(xlt.OrderBy(v => v.ProductName).ToList()); //return new List<ProductionObject>(); } //if (queryBuilder.Year < 1 && queryBuilder.Month > 0) //{ // var query = ( // from // p in db.Productions.Where(m => // (queryBuilder.FieldId > 0 && m.FieldId == queryBuilder.FieldId && queryBuilder.ProductId < 1 && (m.Month == queryBuilder.Month)) || // (queryBuilder.ProductId > 0 && queryBuilder.FieldId < 1 && m.ProductId == queryBuilder.ProductId && (m.Month == queryBuilder.Month)) || // (queryBuilder.FieldId > 0 && queryBuilder.ProductId > 0 && queryBuilder.ProductId > 0 && queryBuilder.FieldId > 0 && m.FieldId == queryBuilder.FieldId && m.ProductId == queryBuilder.ProductId && (m.Month == queryBuilder.Month)) || // (queryBuilder.FieldId < 1 && queryBuilder.ProductId < 1 && (m.Month == queryBuilder.Month))) // join f in db.Fields on p.FieldId equals f.FieldId where ((queryBuilder.FieldId > 0 && f.FieldId == queryBuilder.FieldId) || (f.FieldId == p.FieldId && queryBuilder.FieldId < 1)) // join c in db.Companies on f.CompanyId equals c.CompanyId where // ((queryBuilder.CompanyId > 0 && c.CompanyId == queryBuilder.CompanyId) || (queryBuilder.CompanyId < 1 && c.CompanyId == f.CompanyId)) // join z in db.Zones on f.ZoneId equals z.ZoneId // where ((queryBuilder.ZoneId > 0 && z.ZoneId == queryBuilder.ZoneId && z.ZoneId == f.ZoneId) || (z.ZoneId == f.ZoneId && queryBuilder.ZoneId < 1)) // join t in db.Terrains on f.TerrainId equals t.TerrainId // where ((queryBuilder.TerrainId > 0 && // t.TerrainId == queryBuilder.TerrainId) || (queryBuilder.TerrainId < 1 && t.TerrainId == f.TerrainId)) // join pd in db.Products on p.ProductId equals pd.ProductId // where // ((queryBuilder.ProductId > 0 && pd.ProductId == queryBuilder.ProductId) || // (queryBuilder.ProductId < 1 && p.ProductId == pd.ProductId)) // join b in db.Blocks on f.BlockId equals b.BlockId // where // ((queryBuilder.BlockId > 0 && f.BlockId == queryBuilder.BlockId) || // (b.BlockId == f.BlockId && queryBuilder.BlockId < 1)) // select new ProductionObject() // { // ProductName = pd.Name, // BlockName = b.Name, // FieldName = f.Name, // CompanyName = c.Name, // ZoneName = z.Name, // TerrainName = t.Name, // Quantity = p.Quantity, // Year = p.Year, // Month = p.Month, // Remark = p.Remark, // APIGravity = p.APIGravity // }).ToList(); // if (!query.Any()) // { // return new List<ProductionObject>(); // } // query.ForEach(j => // { // var quantityOut = 0; // var result2 = int.TryParse(j.Quantity, out quantityOut); // if (result2) // { // j.Quantity = Convert.ToDecimal(j.Quantity).ToString("#,##0"); // } // else // { // j.Quantity = "0"; // } // var monthOut = j.Month; // if (monthOut > 0) // { // j.Period = Enum.GetName(typeof (Months), monthOut) + "/" + j.Year; // } // if (monthOut < 1) // { // j.Period = j.Year.ToString(CultureInfo.InvariantCulture); // } // }); // return query.OrderBy(v => v.ProductName).ToList(); //} if (((queryBuilder.StartDate != null && queryBuilder.StartDate.Value.Year == 0001) || (queryBuilder.StartDate == null)) && ((queryBuilder.EndDate != null && queryBuilder.EndDate.Value.Year == 0001) || queryBuilder.EndDate == null)) { var query = ( from p in db.Productions.Where(m => (queryBuilder.FieldId > 0 && m.FieldId == queryBuilder.FieldId && queryBuilder.ProductId < 1) || (queryBuilder.ProductId > 0 && queryBuilder.FieldId < 1 && m.ProductId == queryBuilder.ProductId) || (queryBuilder.FieldId > 0 && queryBuilder.ProductId > 0 && queryBuilder.ProductId > 0 && queryBuilder.FieldId > 0 && m.FieldId == queryBuilder.FieldId && m.ProductId == queryBuilder.ProductId) || (queryBuilder.FieldId < 1 && queryBuilder.ProductId < 1)) join f in db.Fields on p.FieldId equals f.FieldId where ((queryBuilder.FieldId > 0 && f.FieldId == queryBuilder.FieldId) || (f.FieldId == p.FieldId && queryBuilder.FieldId < 1)) join c in db.Companies on f.CompanyId equals c.CompanyId where ((queryBuilder.CompanyId > 0 && c.CompanyId == queryBuilder.CompanyId) || (queryBuilder.CompanyId < 1 && c.CompanyId == f.CompanyId)) join z in db.Zones on f.ZoneId equals z.ZoneId //where ((queryBuilder.ZoneId > 0 && z.ZoneId == queryBuilder.ZoneId && z.ZoneId == f.ZoneId) || (z.ZoneId == f.ZoneId && queryBuilder.ZoneId < 1)) join t in db.Terrains on f.TerrainId equals t.TerrainId //where ((queryBuilder.TerrainId > 0 && // t.TerrainId == queryBuilder.TerrainId) || (queryBuilder.TerrainId < 1 && t.TerrainId == f.TerrainId)) join pd in db.Products on p.ProductId equals pd.ProductId where ((queryBuilder.ProductId > 0 && pd.ProductId == queryBuilder.ProductId) || (queryBuilder.ProductId < 1 && p.ProductId == pd.ProductId)) join b in db.Blocks on f.BlockId equals b.BlockId where ((queryBuilder.BlockId > 0 && f.BlockId == queryBuilder.BlockId) || (b.BlockId == f.BlockId && queryBuilder.BlockId < 1)) select new ProductionObject() { ProductName = pd.Name, BlockName = b.Name, FieldName = f.Name, CompanyName = c.Name, ZoneName = z.Name, TerrainName = t.Name, Quantity = p.Quantity, Year = p.Year, Month = p.Month, Remark = p.Remark, APIGravity = p.APIGravity }).ToList(); if (!query.Any()) { return(new List <ProductionObject>()); } query.ForEach(j => { var quantityOut = 0; var result2 = int.TryParse(j.Quantity, out quantityOut); if (result2) { j.Quantity = Convert.ToDecimal(j.Quantity).ToString("#,##0"); } else { j.Quantity = "0"; } var monthOut = j.Month; if (monthOut > 0) { j.Period = Enum.GetName(typeof(Months), monthOut) + "/" + j.Year; } if (monthOut < 1) { j.Period = j.Year.ToString(CultureInfo.InvariantCulture); } }); return(query.OrderBy(v => v.ProductName).ToList()); } } return(new List <ProductionObject>()); } } catch (Exception e) { ErrorLogger.LogEror(e.StackTrace, e.Source, e.Message); return(new List <ProductionObject>()); } }