// GET: dashboardcontroller
public ActionResult Index()
{
DashDBContext db = new DashDBContext();
var workflow = db.Database.SqlQuery <Searchworkflow>("SELECT workflows.wfnumber, workflows.wftitle, workflows.Requestor, workflows.createddate, workflows.deadline, Count(*) count FROM workflows " +
" INNER JOIN workflowtimestamps ON workflows.wfNumber = workflowtimestamps.wfnumber " +
" GROUP BY workflows.wfnumber, workflows.wftitle, workflows.Requestor, workflows.createddate, workflows.deadline " +
" HAVING COUNT(*) = 1");
var AvgCountToClose = db.Database.SqlQuery <int>("SELECT AVG(DATEDIFF(hour, createddate, stamp)) AS avgclosetime " +
" FROM Workflows " +
" INNER JOIN workflowtimestamps ON workflows.wfNumber = workflowtimestamps.wfnumber " +
" WHERE workflowtimestamps.workflowstatusID = 4");
var workingworkflow = db.Database.SqlQuery <ActiveWorkflows>(";with cteRowNumber as (SELECT wfNumber, stamp, workflowtimestamps.workflowstatusID, participant, row_number() over(partition by wfNumber order by stamp desc) as RowNum " +
" FROM workflowtimestamps " +
" ) select cteRowNumber.wfNumber, wfTitle, Requestor, createddate, deadline, participant " +
" FROM cteRowNumber INNER JOIN Workflows ON cteRowNumber.wfNumber = workflows.wfNumber " +
" WHERE RowNum = 1 AND workflowstatusID = '2'");
var model = new AllDashInfo {
ViewAllOpenWorkflows = workflow.ToList(), avgclosetime = AvgCountToClose.ToList().FirstOrDefault(), workingworkflows = workingworkflow.ToList()
};
return(View(model));
}
public JsonResult GetBusyTime()
{
using (var context = new DashDBContext())
{
var query = context.Database.SqlQuery <busytime>("SELECT COUNT(createddate) AS startHourCount, startHour FROM ( SELECT datePart(hour, createddate) AS startHour, createddate FROM Workflows ) A GROUP BY startHour").ToList();
return(Json(query, JsonRequestBehavior.AllowGet));
}
}
public JsonResult GetCurrOwner(string wfNumber)
{
using (var context = new DashDBContext())
{
var query = context.Database.SqlQuery <userlist>("SELECT TOP 1 participant as UserId, Namer as fullname FROM [HNBACT01].[dw_HB1].[dbo].[dw_Timekeeper] INNER JOIN [dbo].workflowtimestamps ON UserId = [dbo].workflowtimestamps.participant WHERE wfNumber = " + wfNumber + " AND workflowstatusid = 2 ORDER BY stamp DESC").ToList();
return(Json(query, JsonRequestBehavior.AllowGet));
}
}
public JsonResult getWorkFlowUserList(string searchPhrase)
{
using (var context = new DashDBContext())
{
var query = context.Database.SqlQuery <userlist>("SELECT TK.[UserId], [Namer] as fullname FROM [HNBACT01].[dw_HB1].[dbo].[dw_Timekeeper] AS TK INNER JOIN [dbo].[workflowusers] ON TK.tkinit = [dbo].[workflowusers].tkinit WHERE Namer like '%" + searchPhrase + "%' ORDER BY Namer").ToList();
return(Json(query, JsonRequestBehavior.AllowGet));
}
}
public JsonResult getWorkflowList(string searchPhrase)
{
using (var context = new DashDBContext())
{
var query = context.Database.SqlQuery <Searchworkflow>("SELECT wfTitle, wfNumber, Requestor, Createddate, deadline FROM workflows WHERE wfTitle like '%" + searchPhrase + "%' OR wfNumber like '%" + searchPhrase + "%' ORDER BY wfTitle").ToList();
return(Json(query, JsonRequestBehavior.AllowGet));
}
}
public ActionResult Index()
{
//get the list of current workflows and display on the home page
DashDBContext db = new DashDBContext();
var vWorkflows = from wf in db.vAllWFLastStatusLists
select wf;
vWorkflows = vWorkflows.Where(w => w.workflowstatusID != 4);
return(View(vWorkflows));
}
public JsonResult getOpenWorkflows()
{
using (var context = new DashDBContext())
{
var query = context.Database.SqlQuery <Searchworkflow>("SELECT workflows.wfnumber, workflows.wftitle, workflows.Requestor, workflows.createddate, workflows.deadline, Count(*) count FROM workflows " +
" INNER JOIN workflowtimestamps ON workflows.wfNumber = workflowtimestamps.wfnumber " +
" GROUP BY workflows.wfnumber, workflows.wftitle, workflows.Requestor, workflows.createddate, workflows.deadline " +
" HAVING COUNT(*) = 1").ToList();
return(Json(query, JsonRequestBehavior.AllowGet));
}
}
public JsonResult GetWFTimeStampsJson(int wfNumber)
{
using (DashDBContext db = new DashDBContext())
{
var workflownts = from wfts in db.workflowtimestamps
join wfstatus in db.workflowstatus on wfts.workflowstatusID equals wfstatus.wfsid
orderby wfts.stamp descending
select new { timestamp = wfts.stamp, participant = wfts.participant, wfStatus = wfstatus.wfstatus, wfID = wfts.wfNumber, wftsid = wfts.wftsid };
workflownts = workflownts.Where(w => w.wfID == wfNumber);
return(Json(workflownts.ToList(), JsonRequestBehavior.AllowGet));
}
}
public void SaveClientMatter(string ClientMatter, int wfNumber)
{
DashDBContext db = new DashDBContext();
workflows wf = (from x in db.workflows
where x.wfNumber == wfNumber
select x).First();
wf.clientmatters = ClientMatter;
db.SaveChanges();
LogWFEdits(User.Identity.Name.Substring(User.Identity.Name.LastIndexOf('\\') + 1), wfNumber, "Client Matter change");
return;
}
public void SaveDeadlineDate(DateTime deadlineDate, int wfNumber)
{
DashDBContext db = new DashDBContext();
workflows wf = (from x in db.workflows
where x.wfNumber == wfNumber
select x).First();
wf.deadline = deadlineDate;
db.SaveChanges();
LogWFEdits(User.Identity.Name.Substring(User.Identity.Name.LastIndexOf('\\') + 1), wfNumber, "Deadline date change");
return;
}
public void SaveFilesToWorkflow(int wfid)
{
//now that the workflow has been created we need to clean up the files that they uploaded and put the workflow ID on them
using (var db = new DashDBContext())
{
foreach (var wffiles in db.workflowfiles.Where(x => x.wfsessionid.Equals(Session.SessionID.ToString())).ToList())
{
wffiles.wfID = wfid;
}
db.SaveChanges();
}
return;
}
public void SaveDescOfWork(string desctext, int wfNumber)
{
DashDBContext db = new DashDBContext();
workflows wf = (from x in db.workflows
where x.wfNumber == wfNumber
select x).First();
wf.descofwork = desctext;
db.SaveChanges();
LogWFEdits(User.Identity.Name.Substring(User.Identity.Name.LastIndexOf('\\') + 1), wfNumber, "Description of work change");
return;
}
public void SaveAttorneyName(string attyID, int wfNumber)
{
DashDBContext db = new DashDBContext();
workflows wf = (from x in db.workflows
where x.wfNumber == wfNumber
select x).First();
wf.Requestor = attyID;
db.SaveChanges();
LogWFEdits(User.Identity.Name.Substring(User.Identity.Name.LastIndexOf('\\') + 1), wfNumber, "Requesting attorney change");
return;
}
public void SaveWFTSNewTimeStamp(DateTime newTSDate, Guid wfTSID, string reason)
{
DashDBContext db = new DashDBContext();
workflowtimestamps wf = (from x in db.workflowtimestamps
where x.wftsid == wfTSID
select x).First();
wf.stamp = newTSDate;
db.SaveChanges();
LogWFEdits(User.Identity.Name.Substring(User.Identity.Name.LastIndexOf('\\') + 1), wf.wfNumber, wfTSID + " had the date changed because of " + reason);
AddNewNote(wfTSID + " had the date changed because of " + reason, wf.wfNumber);
return;
}
public void SaveDocumentToDB(string filename, string uniquename)
{
DashDBContext db = new DashDBContext();
workflowfiles wff = new workflowfiles
{
filename = filename,
wfsessionid = Session.SessionID.ToString(),
upload_dtim = DateTime.Now,
uniquefilename = uniquename
};
db.workflowfiles.Add(wff);
db.SaveChanges();
return;
}
public void AddNewNote(string newnotetext, int wfNumber)
{
DashDBContext db = new DashDBContext();
workflownotes wfnote = new workflownotes
{
wfID = wfNumber,
wfnote = newnotetext,
author = User.Identity.Name.Substring(User.Identity.Name.LastIndexOf('\\') + 1).ToString(),
notetimestamp = DateTime.Now
};
db.workflownotes.Add(wfnote);
db.SaveChanges();
return;
}
public void ChangeWorkflowStatus(int newstatus, int wfNumber)
{
DashDBContext db = new DashDBContext();
workflowtimestamps wfts = new workflowtimestamps
{
wfNumber = wfNumber,
workflowstatusID = newstatus,
stamp = DateTime.Now,
participant = User.Identity.Name.Substring(User.Identity.Name.LastIndexOf('\\') + 1).ToString()
};
db.workflowtimestamps.Add(wfts);
db.SaveChanges();
return;
}
private void LogWFEdits(string tkinit, int wfNumber, string changeevent)
{
DashDBContext db = new DashDBContext();
Edit_Log el = new Edit_Log
{
wfNumber = wfNumber,
dtim = DateTime.Now,
tkinit = tkinit,
changeevent = changeevent
};
db.Edit_Log.Add(el);
db.SaveChanges();
return;
}
private void SaveTimeStamp(int wfNumber, int wfstatus, string participant)
{
DashDBContext db = new DashDBContext();
workflowtimestamps t = new workflowtimestamps
{
wfNumber = wfNumber,
workflowstatusID = wfstatus,
participant = participant,
stamp = DateTime.Now
};
db.workflowtimestamps.Add(t);
db.SaveChanges();
return;
}
public ActionResult edit(int id)
{
DashDBContext db = new DashDBContext();
vAllWFLastStatusLists workflow = db.vAllWFLastStatusLists.Find(id);
if (workflow == null)
{
return(HttpNotFound());
}
IEnumerable <workflownotes> workflownts = from wfn in db.workflownotes
orderby wfn.notetimestamp descending
select wfn;
workflownts = workflownts.Where(w => w.wfID == id);
var workflowHist = from wfn in db.workflowtimestamps
join workflowstatus wfs in db.workflowstatus on wfn.workflowstatusID equals wfs.wfsid
orderby wfn.stamp descending
select new workflowtimestampshistory {
stamp = wfn.stamp, participant = wfn.participant, wfstatus = wfs.wfstatus, wfNumber = wfn.wfNumber
};
workflowHist = workflowHist.Where(w => w.wfNumber == id);
/*
* Get the files associated with this workflow
*
* var model = new allWFfiles { wffilelist = vWorkflowfiles.ToList() };
*
*/
var wffiles = from wf in db.workflowfiles
select wf;
wffiles = wffiles.Where(w => w.wfID == id);
var model = new AllWFInfo {
ViewWorkFlowData = workflow, wfnotes = workflownts.ToList(), workflowHistory = workflowHist.ToList(), workflowfileList = wffiles.ToList()
};
return(View(model));
}
public ActionResult Create([Bind(Exclude = "wfNumber, startdate, enddate", Include = "wfTitle, Requestor, Submitter, createddate, clientmatters, descofwork, deadline")] workflows workflow)
{
DashDBContext db = new DashDBContext();
if (ModelState.IsValid)
{
workflow.createddate = DateTime.Now;
db.workflows.Add(workflow);
try
{
db.SaveChanges();
int wfid = workflow.wfNumber;
SaveTimeStamp(wfid, 1, Request.Form["Submitter"].ToString());
SaveFilesToWorkflow(wfid);
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}
}
return(View());
}
