private string CreateSearchQuery(HeatTreat heatTreat, bool MatchAll, int take, int skip, string DatePartial = "")
{
string op = "AND";
if (MatchAll == false)
{
op = "OR";
}
string query = "SELECT * FROM HeatTreat ";
//Date Parsing
int year = -1;
int month = -1;
int day = -1;
int hour = -1;
int minute = -1;
int second = -1;
bool SearchDate = false;
try
{
if (DatePartial.Length > 0)
{
year = Convert.ToInt32(DatePartial.Substring(0, 4));
SearchDate = true;//At least the Date exists, so search records.
month = Convert.ToInt32(DatePartial.Substring(5, 2));
day = Convert.ToInt32(DatePartial.Substring(8, 2));
hour = Convert.ToInt32(DatePartial.Substring(11, 2));
minute = Convert.ToInt32(DatePartial.Substring(14, 2));
second = Convert.ToInt32(DatePartial.Substring(17, 2));
}
}
catch (Exception ex) { }
if (heatTreat.JobCardNumber != null)
{
if (query.IndexOf("WHERE") < 1)
{
query += " WHERE ";
}
else
{
query += op;
}
query += " JobCardNumber LIKE '%" + heatTreat.JobCardNumber + "%' ";
}
if (heatTreat.FirstName != null)
{
if (query.IndexOf("WHERE") < 1)
{
query += " WHERE ";
}
else
{
query += op;
}
query += " FirstName LIKE '%" + heatTreat.FirstName + "%' ";
}
if (heatTreat.StationNumber != null)
{
if (query.IndexOf("WHERE") < 1)
{
query += " WHERE ";
}
else
{
query += op;
}
query += " StationNumber LIKE '%" + heatTreat.StationNumber + "%' ";
}
if (heatTreat.Temperature != null)
{
if (query.IndexOf("WHERE") < 1)
{
query += " WHERE ";
}
else
{
query += op;
}
query += " Temperature LIKE '%" + heatTreat.Temperature + "%' ";
}
if (heatTreat.Load != null)
{
if (query.IndexOf("WHERE") < 1)
{
query += " WHERE ";
}
else
{
query += op;
}
query += " Load LIKE '%" + heatTreat.Load + "%' ";
}
//Now the parsed dates are validated
if (SearchDate)
{
if (query.IndexOf("WHERE") < 1)
{
query += " WHERE ";
}
else
{
query += op;
}
query += " ( ";
query += " 1 = 1 ";
if (year > 0)
{
query += " AND DATEPART(YEAR, Date) = " + year + " ";
}
if (month > 0)
{
query += " AND DATEPART(MONTH, Date) = " + month + " ";
}
if (day > 0)
{
query += " AND DATEPART(DAY, Date) = " + day + " ";
}
if (hour > 0)
{
query += " AND DATEPART(HOUR, Date) = " + hour + " ";
}
if (minute > 0)
{
query += " AND DATEPART(MINUTE, Date) = " + minute + " ";
}
if (second > 0)
{
query += " AND DATEPART(SECOND, Date) = " + second + " ";
}
query += " ) ";
}
query += " ORDER BY Date DESC";
query += " OFFSET (" + skip + ") ROWS FETCH NEXT (" + take + ") ROWS ONLY";
return query;
}
public async Task<ActionResult> LoadData(HeatTreat heatTreat, int PageSize, int PageNumber, bool MatchAll, string Role, string DatePartial = "", int OperatorsId = 0)
{
int rows = await db.HeatTreat.CountAsync();
int pages = rows / PageSize;
if (pages * PageSize < rows)
{
pages++;
}
if (PageNumber < 1)
{
PageNumber = 1;
}
if (PageNumber > pages)
{
PageNumber = pages;
}
ViewBag.Pages = pages;
ViewBag.PageSize = PageSize;
ViewBag.Rows = rows;
ViewBag.Current = PageNumber;
ViewBag.Role = Role;
ViewBag.Columns = await db.ColumnNames.OrderBy(x => x.Order).Select(x => x.Label).ToListAsync();
ViewBag.ColumnsFull = await db.ColumnNames.OrderBy(x => x.Order).ToListAsync();
ViewBag.ColumnsToHideUser = await db.UserHideColumns.Where(x => x.OperatorsId == OperatorsId).ToListAsync();
string query = CreateSearchQuery(heatTreat, MatchAll, PageSize, (PageNumber - 1) * PageSize, DatePartial);
List<HeatTreat> results = await db.Database.SqlQuery<HeatTreat>(query).ToListAsync();
return PartialView("Index", results);
}
public async Task<ActionResult> AddRow(HeatTreat heatTreat, HttpPostedFileBase uImage, HttpPostedFileBase uImage2, HttpPostedFileBase uImage3, HttpPostedFileBase uImage4, HttpPostedFileBase uImage5, string Role)
{
if (Role.Equals("User"))
{
if (
heatTreat.JobCardNumber == null &&
heatTreat.Temperature == null &&
heatTreat.Load == null &&
heatTreat.StationNumber == null
)
{
ModelState.AddModelError("NoData", "Please enter some data");
}
}
if (Role.Equals("Admin"))
{
if (
heatTreat.FirstName == null &&
heatTreat.JobCardNumber == null &&
heatTreat.Temperature == null &&
heatTreat.Load == null &&
heatTreat.StationNumber == null
)
{
ModelState.AddModelError("NoData", "Please enter some data");
}
}
heatTreat.Date = DateTime.Now;
heatTreat.Image = heatTreat.Image2 = heatTreat.Image3 = heatTreat.Image4 = heatTreat.Image5 = false;
if (ModelState.IsValid)
{
//---------------------------------------------------------
//We add the record for real-time update - EDIT APPLIES TO USER AND ADMIN
//Get the admin id
if (Role.Equals("User"))
{
int OprId = heatTreat.OperatorsId.GetValueOrDefault();
//Now check the record
RT rt = db.RT.Where(x => x.OperatorsId == OprId).FirstOrDefault();
if (rt == null)
{
rt = new RT();
rt.OperatorsId = OprId;
rt.LastModified = DateTime.UtcNow;
db.Entry(rt).State = EntityState.Added;
}
else
{
rt.LastModified = DateTime.UtcNow;
db.Entry(rt).State = EntityState.Modified;
}
}
if (Role.Equals("Admin"))
{
string username = HttpContext.User.Identity.Name;
string AdminId = db.Users.Where(x => x.UserName == username).Select(x => x.Id).FirstOrDefault();
//Now check the record
RT rt = db.RT.Where(x => x.AdminId == AdminId).FirstOrDefault();
if (rt == null)
{
rt = new RT();
rt.AdminId = AdminId;
rt.LastModified = DateTime.UtcNow;
db.Entry(rt).State = EntityState.Added;
}
else
{
rt.LastModified = DateTime.UtcNow;
db.Entry(rt).State = EntityState.Modified;
}
}
//End of realtime update - EDIT APPLIES TO USER AND ADMIN
//-----------------------------------------------------------
db.HeatTreat.Add(heatTreat);
await db.SaveChangesAsync();
ViewBag.Result = "DataSaved";
bool CorrectImage = true;
if (uImage != null)
{
var fileName = Path.GetFileName(uImage.FileName);
string[] SplitFileName = fileName.Split('.');
if (SplitFileName.Count() < 2)
{
//ModelState.AddModelError("uImage", "No extension");
CorrectImage = false;
}
if (!(SplitFileName[SplitFileName.Count() - 1].Equals("jpg")
||
SplitFileName[SplitFileName.Count() - 1].Equals("jpeg")
||
SplitFileName[SplitFileName.Count() - 1].Equals("png")
||
SplitFileName[SplitFileName.Count() - 1].Equals("ico")
||
SplitFileName[SplitFileName.Count() - 1].Equals("bmp")
||
SplitFileName[SplitFileName.Count() - 1].Equals("gif")
)
)
{
//ModelState.AddModelError("uImage", "File uImage Required");
CorrectImage = false;
}
if (CorrectImage)
{
var path = Path.Combine(Server.MapPath("~/Attachments"), heatTreat.HeatTreatId + ".png");
uImage.SaveAs(path);
heatTreat.Image = true;
db.Entry(heatTreat).State = EntityState.Modified;
await db.SaveChangesAsync();
}
}
//2
CorrectImage = true;
if (uImage2 != null)
{
var fileName = Path.GetFileName(uImage2.FileName);
string[] SplitFileName = fileName.Split('.');
if (SplitFileName.Count() < 2)
{
//ModelState.AddModelError("uImage", "No extension");
CorrectImage = false;
}
if (!(SplitFileName[SplitFileName.Count() - 1].Equals("jpg")
||
SplitFileName[SplitFileName.Count() - 1].Equals("jpeg")
||
SplitFileName[SplitFileName.Count() - 1].Equals("png")
||
SplitFileName[SplitFileName.Count() - 1].Equals("ico")
||
SplitFileName[SplitFileName.Count() - 1].Equals("bmp")
||
SplitFileName[SplitFileName.Count() - 1].Equals("gif")
)
)
{
//ModelState.AddModelError("uImage", "File uImage Required");
CorrectImage = false;
}
if (CorrectImage)
{
var path = Path.Combine(Server.MapPath("~/Attachments"), heatTreat.HeatTreatId + "_2.png");
uImage2.SaveAs(path);
heatTreat.Image2 = true;
db.Entry(heatTreat).State = EntityState.Modified;
await db.SaveChangesAsync();
}
}
//3
CorrectImage = true;
if (uImage3 != null)
{
var fileName = Path.GetFileName(uImage3.FileName);
string[] SplitFileName = fileName.Split('.');
if (SplitFileName.Count() < 2)
{
//ModelState.AddModelError("uImage", "No extension");
CorrectImage = false;
}
if (!(SplitFileName[SplitFileName.Count() - 1].Equals("jpg")
||
SplitFileName[SplitFileName.Count() - 1].Equals("jpeg")
||
SplitFileName[SplitFileName.Count() - 1].Equals("png")
||
SplitFileName[SplitFileName.Count() - 1].Equals("ico")
||
SplitFileName[SplitFileName.Count() - 1].Equals("bmp")
||
SplitFileName[SplitFileName.Count() - 1].Equals("gif")
)
)
{
//ModelState.AddModelError("uImage", "File uImage Required");
CorrectImage = false;
}
if (CorrectImage)
{
var path = Path.Combine(Server.MapPath("~/Attachments"), heatTreat.HeatTreatId + "_3.png");
uImage3.SaveAs(path);
heatTreat.Image3 = true;
db.Entry(heatTreat).State = EntityState.Modified;
await db.SaveChangesAsync();
}
}
//4
CorrectImage = true;
if (uImage4 != null)
{
var fileName = Path.GetFileName(uImage4.FileName);
string[] SplitFileName = fileName.Split('.');
if (SplitFileName.Count() < 2)
{
//ModelState.AddModelError("uImage", "No extension");
CorrectImage = false;
}
if (!(SplitFileName[SplitFileName.Count() - 1].Equals("jpg")
||
SplitFileName[SplitFileName.Count() - 1].Equals("jpeg")
||
SplitFileName[SplitFileName.Count() - 1].Equals("png")
||
SplitFileName[SplitFileName.Count() - 1].Equals("ico")
||
SplitFileName[SplitFileName.Count() - 1].Equals("bmp")
||
SplitFileName[SplitFileName.Count() - 1].Equals("gif")
)
)
{
//ModelState.AddModelError("uImage", "File uImage Required");
CorrectImage = false;
}
if (CorrectImage)
{
var path = Path.Combine(Server.MapPath("~/Attachments"), heatTreat.HeatTreatId + "_4.png");
uImage4.SaveAs(path);
heatTreat.Image4 = true;
db.Entry(heatTreat).State = EntityState.Modified;
await db.SaveChangesAsync();
}
}
//5
CorrectImage = true;
if (uImage5 != null)
{
var fileName = Path.GetFileName(uImage5.FileName);
string[] SplitFileName = fileName.Split('.');
if (SplitFileName.Count() < 2)
{
//ModelState.AddModelError("uImage", "No extension");
CorrectImage = false;
}
if (!(SplitFileName[SplitFileName.Count() - 1].Equals("jpg")
||
SplitFileName[SplitFileName.Count() - 1].Equals("jpeg")
||
SplitFileName[SplitFileName.Count() - 1].Equals("png")
||
SplitFileName[SplitFileName.Count() - 1].Equals("ico")
||
SplitFileName[SplitFileName.Count() - 1].Equals("bmp")
||
SplitFileName[SplitFileName.Count() - 1].Equals("gif")
)
)
{
//ModelState.AddModelError("uImage", "File uImage Required");
CorrectImage = false;
}
if (CorrectImage)
{
var path = Path.Combine(Server.MapPath("~/Attachments"), heatTreat.HeatTreatId + "_5.png");
uImage5.SaveAs(path);
heatTreat.Image5 = true;
db.Entry(heatTreat).State = EntityState.Modified;
await db.SaveChangesAsync();
}
}
int tempid = heatTreat.OperatorsId.GetValueOrDefault();
string tempname = heatTreat.FirstName;
heatTreat = new HeatTreat();
ModelState.Clear();
if (Role.Equals("User"))
{
heatTreat.OperatorsId = tempid;
heatTreat.FirstName = tempname;
}
}
else
{
ViewBag.Result = "DataError";
}
ViewBag.Role = Role;
ViewBag.OperatorId = heatTreat.OperatorsId;
ViewBag.OperatorName = heatTreat.FirstName;
ViewBag.Columns = await db.ColumnNames.OrderBy(x => x.Order).Select(x => x.Label).ToListAsync();
ViewBag.ColumnsFull = await db.ColumnNames.OrderBy(x => x.Order).ToListAsync();
ViewBag.ColumnsToHideUser = await db.UserHideColumns.Where(x => x.OperatorsId == heatTreat.OperatorsId).ToListAsync();
return View("User", heatTreat);
}
public async Task<ActionResult> Embed(string jobCardNo, string firstName, string stationNumber, string temp, string load)
{
HeatTreat heatTreat = new HeatTreat();
//Inject data to the model
heatTreat.JobCardNumber = jobCardNo;
heatTreat.FirstName = firstName;
heatTreat.StationNumber = stationNumber;
heatTreat.Temperature = temp;
heatTreat.Load = load;
heatTreat.Image = heatTreat.Image2 = heatTreat.Image3 = heatTreat.Image4 = heatTreat.Image5 = false;
heatTreat.Date = DateTime.Now;
//Validate the data
if (
heatTreat.FirstName == null &&
heatTreat.JobCardNumber == null &&
heatTreat.Temperature == null &&
heatTreat.Load == null &&
heatTreat.StationNumber == null
)
{
ViewBag.Result = "DataError";
ViewBag.ResultDetail = "Please enter some data.";
goto SkipSaving;
}
//Validate the first name
Operators op = await db.Operators.Where(x => x.Number == firstName).FirstOrDefaultAsync();
if (op == null)
{
ViewBag.Result = "DataError";
ViewBag.ResultDetail = "Operator Number not found.";
goto SkipSaving;
}
//Change the values
heatTreat.FirstName = op.Name;
heatTreat.OperatorsId = op.OperatorsId;
try
{
//---------------------------------------------------------
//We add the record for real-time update EMBED
RT rt = db.RT.Where(x => x.bitDevice == true).FirstOrDefault();
if (rt == null)
{
rt = new RT();
rt.bitDevice = true;
rt.LastModified = DateTime.UtcNow;
db.Entry(rt).State = EntityState.Added;
}
else
{
rt.LastModified = DateTime.UtcNow;
db.Entry(rt).State = EntityState.Modified;
}
//End of realtime update - EMBED
//-----------------------------------------------------------
db.HeatTreat.Add(heatTreat);
await db.SaveChangesAsync();
ViewBag.Result = "DataSaved";
}
catch (Exception ex)
{
ViewBag.Result = "DataError";
ViewBag.ResultDetail = "Invalid data found.";
}
SkipSaving:
ViewBag.Role = "Embed";
ViewBag.Columns = await db.ColumnNames.OrderBy(x => x.Order).Select(x => x.Label).ToListAsync();
ViewBag.ColumnsFull = await db.ColumnNames.OrderBy(x => x.Order).ToListAsync();
ViewBag.ColumnsToHideUser = await db.UserHideColumns.Where(x => x.OperatorsId == 0).ToListAsync();
return View("User", heatTreat);
}
