public bool IsWellWorkoverQueryExisting(WellWorkoverQuery wellWorkoverQuery)
{
try
{
if (wellWorkoverQuery == null)
{
return(false);
}
using (var db = new QueryBuilderEntities())
{
if (db.WellWorkoverQueries.Count(m => m.WellId == wellWorkoverQuery.WellId &&
m.CompanyId == wellWorkoverQuery.CompanyId &&
m.WorkoverReasonId == wellWorkoverQuery.WorkoverReasonId &&
(m.StartDate != null && m.StartDate.Value.Year == wellWorkoverQuery.StartDate.Value.Year && wellWorkoverQuery.StartDate != null) &&
(m.EndDate != null && m.EndDate.Value.Year == wellWorkoverQuery.EndDate.Value.Year && wellWorkoverQuery.EndDate != null) &&
m.EquipmentId == wellWorkoverQuery.EquipmentId &&
m.TerrainId == wellWorkoverQuery.TerrainId &&
m.ZoneId == wellWorkoverQuery.ZoneId
) > 0)
{
return(true);
}
return(false);
}
}
catch (Exception ex)
{
ErrorLogger.LogEror(ex.StackTrace, ex.Source, ex.Message);
return(false);
}
}
public long AddWellWorkoverQueryCheckDuplicate(WellWorkoverQuery wellWorkoverQuery)
{
try
{
if (wellWorkoverQuery == null)
{
return(-2);
}
using (var db = new QueryBuilderEntities())
{
if (db.WellWorkoverQueries.Any())
{
if (db.WellWorkoverQueries.Count(m => m.WellId == wellWorkoverQuery.WellId &&
m.CompanyId == wellWorkoverQuery.CompanyId &&
m.WorkoverReasonId == wellWorkoverQuery.WorkoverReasonId &&
(m.StartDate != null && m.StartDate.Value.Year == wellWorkoverQuery.StartDate.Value.Year && wellWorkoverQuery.StartDate != null) &&
(m.EndDate != null && m.EndDate.Value.Year == wellWorkoverQuery.EndDate.Value.Year && wellWorkoverQuery.EndDate != null) &&
m.EquipmentId == wellWorkoverQuery.EquipmentId &&
m.TerrainId == wellWorkoverQuery.TerrainId &&
m.ZoneId == wellWorkoverQuery.ZoneId
) > 0)
{
return(-3);
}
if (db.WellWorkoverQueries.Count(m => m.WellWorkoverQueryName.ToLower().Trim().Replace(" ", "") == wellWorkoverQuery.WellWorkoverQueryName.ToLower().Trim().Replace(" ", "")
) > 0)
{
return(-4);
}
}
var processedWellWorkoverQuery = db.WellWorkoverQueries.Add(wellWorkoverQuery);
db.SaveChanges();
return(processedWellWorkoverQuery.WellWorkoverQueryId);
}
}
catch (Exception ex)
{
ErrorLogger.LogEror(ex.StackTrace, ex.Source, ex.Message);
return(0);
}
}
public ActionResult GetWellWorkovers(WellWorkoverQuery queryBuilder)
{
if (queryBuilder == null)
{
return(Json(new List <WellWorkoverReportObject>(), JsonRequestBehavior.AllowGet));
}
if (queryBuilder.EquipmentId < 1 && queryBuilder.WellTypeId < 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.WorkoverReasonId < 1 && queryBuilder.WellClassId < 1 && queryBuilder.TerrainId < 1 && queryBuilder.ZoneId < 1)
{
return(Json(new List <WellWorkoverReportObject>(), JsonRequestBehavior.AllowGet));
}
var wellWorkoverList = new WellWorkoverServices().GetOrderedWellWorkoverReportObjects(queryBuilder) ?? new List <WellWorkoverReportObject>();
if (!wellWorkoverList.Any())
{
return(Json(new List <WellWorkoverReportObject>(), JsonRequestBehavior.AllowGet));
}
Session["_successfulWellWorkoverQuery"] = queryBuilder;
var jsonResult = Json(wellWorkoverList, JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return(jsonResult);
}
public List <WellWorkoverReportObject> GetOrderedWellWorkoverReportObjects(WellWorkoverQuery 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) || (queryBuilder.EndDate == null)))
{
var startYear = queryBuilder.StartDate.Value.Year;
var startMonth = queryBuilder.StartDate.Value.Month;
var query = (
from
q in db.WellWorkovers.Where(m =>
(queryBuilder.WellId > 0 && m.WellId == queryBuilder.WellId && queryBuilder.WorkoverReasonId < 1 && queryBuilder.EquipmentId < 1 && (m.Year == startYear && m.Month == startMonth)) ||
(queryBuilder.EquipmentId > 0 && queryBuilder.WellId < 1 && queryBuilder.WorkoverReasonId < 1 && m.EquipmentId == queryBuilder.EquipmentId && (m.Year == startYear && m.Month == startMonth)) ||
(queryBuilder.WorkoverReasonId > 0 && queryBuilder.WellId < 1 && queryBuilder.EquipmentId < 1 && m.WellWorkOverReasonId == queryBuilder.WorkoverReasonId && (m.Year == startYear && m.Month == startMonth)) ||
(queryBuilder.WellId > 0 && queryBuilder.EquipmentId > 0 && queryBuilder.WorkoverReasonId < 1 && m.WellId == queryBuilder.WellId && m.EquipmentId == queryBuilder.EquipmentId && (m.Year == startYear && m.Month == startMonth)) ||
(queryBuilder.WellId > 0 && queryBuilder.EquipmentId < 1 && queryBuilder.WorkoverReasonId > 0 && m.WellId == queryBuilder.WellId && m.WellWorkOverReasonId == queryBuilder.WorkoverReasonId && (m.Year == startYear && m.Month == startMonth)) ||
(queryBuilder.WellId < 1 && queryBuilder.EquipmentId > 0 && queryBuilder.WorkoverReasonId > 0 && m.EquipmentId == queryBuilder.EquipmentId && m.WellWorkOverReasonId == queryBuilder.WorkoverReasonId && (m.Year == startYear && m.Month == startMonth)) ||
(queryBuilder.WellId < 1 && queryBuilder.EquipmentId < 1 && queryBuilder.WorkoverReasonId < 1 && (m.Year == startYear && m.Month == startMonth))).Include("Equipment").Include("Well").Include("WellWorkOverReason")
join
t in db.Fields on q.Well.FieldId equals t.FieldId
join i in db.Blocks on t.BlockId equals i.BlockId
join c in db.WellClassifications on q.Well.WellId equals c.WellId
join
p in db.WellClasses on c.WellClassId equals p.WellClassId
where ((p.WellClassId == queryBuilder.WellClassId && queryBuilder.WellClassId > 0 && c.WellClassId == p.WellClassId) || (p.WellClassId == c.WellClassId && queryBuilder.WellClassId < 1))
join
x in db.WellTypes on q.Well.WellTypeId equals x.WellTypeId
where
((queryBuilder.WellTypeId > 0 && x.WellTypeId == queryBuilder.WellTypeId && q.Well.WellTypeId == x.WellTypeId) ||
(q.Well.WellTypeId == x.WellTypeId && queryBuilder.WellTypeId < 1))
join
y in db.Terrains on t.TerrainId equals y.TerrainId
where ((queryBuilder.TerrainId > 0 && y.TerrainId == queryBuilder.TerrainId) || (y.TerrainId == t.TerrainId && queryBuilder.TerrainId < 1))
join
s in db.Zones on t.ZoneId equals s.ZoneId
where
((queryBuilder.ZoneId > 0 && s.ZoneId == queryBuilder.ZoneId)
||
(s.ZoneId == t.ZoneId && queryBuilder.ZoneId < 1))
join
h in db.Companies on t.CompanyId equals h.CompanyId
where ((queryBuilder.CompanyId > 0 && h.CompanyId == queryBuilder.CompanyId) || (queryBuilder.CompanyId < 1 && t.CompanyId == h.CompanyId))
select new WellWorkoverReportObject
{
EquipmentName = q.Equipment.Name,
CompanyName = h.Name,
WellTypeName = x.Title,
WellClassName = p.Name,
WellName = q.Well.Name,
ZoneName = s.Name,
TerrainName = y.Name,
Reason = q.WellWorkOverReason.Title,
Year = q.Year,
Month = q.Month,
DateCompleted = q.DateCompleted
}).ToList();
if (!query.Any())
{
return(new List <WellWorkoverReportObject>());
}
query.ForEach(j =>
{
if (j.Month > 0)
{
j.WorkoverPeriod = Enum.GetName(typeof(Months), j.Month) + "/" + j.Year;
}
if (j.Month < 1)
{
j.WorkoverPeriod = Enum.GetName(typeof(Months), 1) + "/" + j.Year;
}
});
return(query.OrderBy(v => v.WellName).ToList());
}
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
q in db.WellWorkovers.Where(m =>
(queryBuilder.WellId > 0 && m.WellId == queryBuilder.WellId && queryBuilder.WorkoverReasonId < 1 && queryBuilder.EquipmentId < 1 && ((m.Year >= startYear && m.Month >= startMonth) && (m.Year <= endYear && m.Month <= endMonth))) ||
(queryBuilder.EquipmentId > 0 && queryBuilder.WellId < 1 && queryBuilder.WorkoverReasonId < 1 && m.EquipmentId == queryBuilder.EquipmentId && ((m.Year >= startYear && m.Month >= startMonth) && (m.Year <= endYear && m.Month <= endMonth))) ||
(queryBuilder.WorkoverReasonId > 0 && queryBuilder.WellId < 1 && queryBuilder.EquipmentId < 1 && m.WellWorkOverReasonId == queryBuilder.WorkoverReasonId && ((m.Year >= startYear && m.Month >= startMonth) && (m.Year <= endYear && m.Month <= endMonth))) ||
(queryBuilder.WellId > 0 && queryBuilder.EquipmentId > 0 && queryBuilder.WorkoverReasonId < 1 && m.WellId == queryBuilder.WellId && m.EquipmentId == queryBuilder.EquipmentId && ((m.Year >= startYear && m.Month >= startMonth) && (m.Year <= endYear && m.Month <= endMonth))) ||
(queryBuilder.WellId > 0 && queryBuilder.EquipmentId < 1 && queryBuilder.WorkoverReasonId > 0 && m.WellId == queryBuilder.WellId && m.WellWorkOverReasonId == queryBuilder.WorkoverReasonId && ((m.Year >= startYear && m.Month >= startMonth) && (m.Year <= endYear && m.Month <= endMonth))) ||
(queryBuilder.WellId < 1 && queryBuilder.EquipmentId > 0 && queryBuilder.WorkoverReasonId > 0 && m.EquipmentId == queryBuilder.EquipmentId && m.WellWorkOverReasonId == queryBuilder.WorkoverReasonId && ((m.Year >= startYear && m.Month >= startMonth) && (m.Year <= endYear && m.Month <= endMonth))) ||
(queryBuilder.WellId < 1 && queryBuilder.EquipmentId < 1 && queryBuilder.WorkoverReasonId < 1 && ((m.Year >= startYear && m.Month >= startMonth) && (m.Year <= endYear && m.Month <= endMonth)))).Include("Equipment").Include("Well").Include("WellWorkOverReason")
join
t in db.Fields on q.Well.FieldId equals t.FieldId
join i in db.Blocks on t.BlockId equals i.BlockId
join c in db.WellClassifications on q.Well.WellId equals c.WellId
join
p in db.WellClasses on c.WellClassId equals p.WellClassId
where ((p.WellClassId == queryBuilder.WellClassId && queryBuilder.WellClassId > 0 && c.WellClassId == p.WellClassId) || (p.WellClassId == c.WellClassId && queryBuilder.WellClassId < 1))
join
x in db.WellTypes on q.Well.WellTypeId equals x.WellTypeId
where
((queryBuilder.WellTypeId > 0 && x.WellTypeId == queryBuilder.WellTypeId && q.Well.WellTypeId == x.WellTypeId) ||
(q.Well.WellTypeId == x.WellTypeId && queryBuilder.WellTypeId < 1))
join
y in db.Terrains on t.TerrainId equals y.TerrainId
where ((queryBuilder.TerrainId > 0 && y.TerrainId == queryBuilder.TerrainId) || (y.TerrainId == t.TerrainId && queryBuilder.TerrainId < 1))
join
s in db.Zones on t.ZoneId equals s.ZoneId
where
((queryBuilder.ZoneId > 0 && s.ZoneId == queryBuilder.ZoneId)
||
(s.ZoneId == t.ZoneId && queryBuilder.ZoneId < 1))
join
h in db.Companies on t.CompanyId equals h.CompanyId
where ((queryBuilder.CompanyId > 0 && h.CompanyId == queryBuilder.CompanyId) || (queryBuilder.CompanyId < 1 && t.CompanyId == h.CompanyId))
select new WellWorkoverReportObject
{
EquipmentName = q.Equipment.Name,
CompanyName = h.Name,
WellTypeName = x.Title,
WellClassName = p.Name,
WellName = q.Well.Name,
ZoneName = s.Name,
TerrainName = y.Name,
Reason = q.WellWorkOverReason.Title,
Year = q.Year,
Month = q.Month,
DateCompleted = q.DateCompleted
}).ToList();
if (!query.Any())
{
return(new List <WellWorkoverReportObject>());
}
query.ForEach(j =>
{
if (j.Month > 0)
{
j.WorkoverPeriod = Enum.GetName(typeof(Months), j.Month) + "/" + j.Year;
}
if (j.Month < 1)
{
j.WorkoverPeriod = Enum.GetName(typeof(Months), 1) + "/" + j.Year;
}
});
return(query.OrderBy(v => v.WellName).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
q in db.WellWorkovers.Where(m =>
(queryBuilder.WellId > 0 && m.WellId == queryBuilder.WellId && queryBuilder.WorkoverReasonId < 1 && queryBuilder.EquipmentId < 1) ||
(queryBuilder.EquipmentId > 0 && queryBuilder.WellId < 1 && queryBuilder.WorkoverReasonId < 1 && m.EquipmentId == queryBuilder.EquipmentId) ||
(queryBuilder.WorkoverReasonId > 0 && queryBuilder.WellId < 1 && queryBuilder.EquipmentId < 1 && m.WellWorkOverReasonId == queryBuilder.WorkoverReasonId) ||
(queryBuilder.WellId > 0 && queryBuilder.EquipmentId > 0 && queryBuilder.WorkoverReasonId < 1 && m.WellId == queryBuilder.WellId && m.EquipmentId == queryBuilder.EquipmentId) ||
(queryBuilder.WellId > 0 && queryBuilder.EquipmentId < 1 && queryBuilder.WorkoverReasonId > 0 && m.WellId == queryBuilder.WellId && m.WellWorkOverReasonId == queryBuilder.WorkoverReasonId) ||
(queryBuilder.WellId < 1 && queryBuilder.EquipmentId > 0 && queryBuilder.WorkoverReasonId > 0 && m.EquipmentId == queryBuilder.EquipmentId && m.WellWorkOverReasonId == queryBuilder.WorkoverReasonId) ||
(queryBuilder.WellId < 1 && queryBuilder.EquipmentId < 1 && queryBuilder.WorkoverReasonId < 1)).Include("Equipment").Include("Well").Include("WellWorkOverReason")
join
t in db.Fields on q.Well.FieldId equals t.FieldId
join i in db.Blocks on t.BlockId equals i.BlockId
join c in db.WellClassifications on q.Well.WellId equals c.WellId
join
p in db.WellClasses on c.WellClassId equals p.WellClassId
where ((p.WellClassId == queryBuilder.WellClassId && queryBuilder.WellClassId > 0 && c.WellClassId == p.WellClassId) || (p.WellClassId == c.WellClassId && queryBuilder.WellClassId < 1))
join
x in db.WellTypes on q.Well.WellTypeId equals x.WellTypeId
where
((queryBuilder.WellTypeId > 0 && x.WellTypeId == queryBuilder.WellTypeId && q.Well.WellTypeId == x.WellTypeId) ||
(q.Well.WellTypeId == x.WellTypeId && queryBuilder.WellTypeId < 1))
join
y in db.Terrains on t.TerrainId equals y.TerrainId
where ((queryBuilder.TerrainId > 0 && y.TerrainId == queryBuilder.TerrainId) || (y.TerrainId == t.TerrainId && queryBuilder.TerrainId < 1))
join
s in db.Zones on t.ZoneId equals s.ZoneId
where
((queryBuilder.ZoneId > 0 && s.ZoneId == queryBuilder.ZoneId)
||
(s.ZoneId == t.ZoneId && queryBuilder.ZoneId < 1))
join
h in db.Companies on t.CompanyId equals h.CompanyId
where ((queryBuilder.CompanyId > 0 && h.CompanyId == queryBuilder.CompanyId) || (queryBuilder.CompanyId < 1 && t.CompanyId == h.CompanyId))
select new WellWorkoverReportObject
{
EquipmentName = q.Equipment.Name,
CompanyName = h.Name,
WellTypeName = x.Title,
WellClassName = p.Name,
WellName = q.Well.Name,
ZoneName = s.Name,
TerrainName = y.Name,
Reason = q.WellWorkOverReason.Title,
Year = q.Year,
Month = q.Month,
DateCompleted = q.DateCompleted
}).ToList();
if (!query.Any())
{
return(new List <WellWorkoverReportObject>());
}
query.ForEach(j =>
{
var monthOut = j.Month;
if (monthOut > 0)
{
j.WorkoverPeriod = Enum.GetName(typeof(Months), monthOut) + "/" + j.Year;
}
if (monthOut < 1)
{
j.WorkoverPeriod = Enum.GetName(typeof(Months), 1) + "/" + j.Year;
}
});
return(query.OrderBy(v => v.WellName).ToList());
}
}
return(new List <WellWorkoverReportObject>());
}
}
catch (Exception ex)
{
ErrorLogger.LogEror(ex.StackTrace, ex.Source, ex.Message);
return(new List <WellWorkoverReportObject>());
}
}