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>());
}
}
