public List<RollNumbers> GetNumbers(Specialist spec, string step)
{
string sql = GetSQL(spec, step);
var reader = Data_Context.RunSelectSQLQuery(sql, 30);
var rolls = new List<RollNumbers>();
if (reader.HasRows)
{
while (reader.Read())
{
string rName = reader["RollName"] as string;
int imgCount = reader.GetInt32(1);
int sElapsed = reader.GetInt32(2);
int sPaused = reader.GetInt32(3);
double imgPerHour = imgCount / (((sElapsed - sPaused) / 60.0) / 60.0);
imgPerHour = Math.Round(imgPerHour, 2);
int seconds = sElapsed - sPaused;
var numbers = new RollNumbers(rName, imgCount, imgPerHour, seconds);
rolls.Add(numbers);
}
Data_Context.CloseConnection();
return rolls;
}
else return null;
}
/// <summary>
/// Returns rolls a specialist scanned/audited in a given time period
/// </summary>
public List<Roll> GetRolls(Specialist user, DateTime min, DateTime max, string step, string rollname = null)
{
//work on making this query faster and more accurate
string sql = MyRollsSQLString(user, step, rollname);
SqlDataReader reader = Data_Context.RunSelectSQLQuery(sql, 120, min, max);
if (reader.HasRows)
{
List<Roll> rollList = new List<Roll>();
while (reader.Read())
{
//var id = reader.GetInt32(0);
var i = reader["WorkItemID"];
int id = Convert.ToInt32(i);
string pId = reader["ProjectID"] as string;
//string pId = Convert.ToString(pi);
string rName = reader["Rollname"] as string;
//string qHist = reader["MyQueue"] as string;
string qName = reader["CurrentQueue"] as string;
string st = reader["State"] as string;
var lUpdate = reader.GetDateTime(5);
var roll = new RollDetails(id, pId, rName, user, st, qName, step, lUpdate);
rollList.Add(roll);
}
Data_Context.CloseConnection();
return rollList;
}
else return null;
}
/// <summary>
/// Finds the correct sql string based on caller
/// </summary>
private string MyRollsSQLString(Specialist user, string step, string rollname)
{
string sql = @"SELECT DISTINCT
WI.WorkItemID
,R.RollName Rollname
,R.ProjectId
,WI.State
,Q.QueueName CurrentQueue
,WI.LastUpdateTime
FROM Roll R (NOLOCK)
LEFT JOIN RollBatch RB (NOLOCK) ON R.RollID = RB.RollID
LEFT JOIN SOX.DetailedResult DR (NOLOCK) ON RB.RollBatchID = DR.RollBatchID
LEFT JOIN JWF_Live..WorkItem WI (NOLOCK) ON R.RollName = WI.WorkItemName
LEFT JOIN JWF_Live..Queue Q (NOLOCK) ON WI.QueueId = Q.QueueId";
sql += @" WHERE ";
if (step != "")
sql += @" DR.StepTypeID = '" + step + "'";
if (rollname != "" && step != "")
sql += @" AND R.RollName LIKE '%" + rollname + "%'";
else if (rollname != "" && step == "")
sql += @" R.RollName LIKE '%" + rollname + "%'";
if (step != "" || rollname != "")
sql += @" AND ";
sql += @" DR.Operator = 'myfamily\" + user.Username + "'";
sql += @" AND DR.LastDocDate BETWEEN CONVERT(DATETIME, @min) AND CONVERT(DATETIME, @max)
ORDER BY R.RollName";
return sql;
}
public Specialist GetSpecialist(string username = null, string fullName = null)
{
if (username != null || fullName != null)
{
foreach (var spec in GetSpecialists())
{
if (username != null)
{
if (spec.Username != null)
{
if (@"myfamily\" + spec.Username.ToLower() == username.ToLower()) return spec;
}
else
{
var newSpec = new Specialist("System", "Process", username.Substring(9));
return newSpec;
}
}
else if (fullName != null)
{
if (spec.SpecialistName != null)
if (fullName.ToLower() == spec.SpecialistName.ToLower()) return spec;
}
}
return null;
}
else return null;
}
public GetRollsArgument(Specialist spec, DateTime min, DateTime max, string step, string rollName)
{
this.Specialist = spec;
this.MinDate = min;
this.MaxDate = max;
this.Step = step;
this.Roll = rollName;
this.Tab = 0;
this.ToggleChecked = true;
}
public GetRollsArgument(string step, string state, string priority, string project, string roll, Specialist spec)
{
this.Step = step;
this.State = state;
this.Priority = priority;
this.Project = project;
this.Roll = roll;
this.Specialist = spec;
this.Tab = 0;
this.ToggleChecked = false;
}
public RollDetails(int id, string projectId, string rollName, Specialist spec, string state, string step, string historyStep, DateTime lastUpdate)
{
//var Data = new DataContext("EPDB01", "JWF_Live");
//Id = Data.GetWorkItemId(rollName);
Id = id;
ProjectId = projectId;
RollName = rollName;
Spec = spec;
State = state;
Step = step;
HistoryStep = historyStep;
LastUpdate = lastUpdate;
}
public RollDetails(int id, string projectId, string rollName, Specialist spec, string state, string step, DateTime lastUpdate, List<Note> notes, List<History> histories, List<ImageNote> imageNotes)
{
//var Data = new DataContext("EPDB01", "JWF_Live");
//Id = Data.GetWorkItemId(rollName);
Id = id;
ProjectId = projectId;
RollName = rollName;
Spec = spec;
State = state;
Step = step;
LastUpdate = lastUpdate;
Notes = notes;
Histories = histories;
ImageNotes = imageNotes;
}
private string GetSQL(Specialist spec, string step)
{
string sql = @"SELECT DISTINCT
R.RollName
,R.ImageCount
,DR.SecondsElapsed
,DR.SecondsPaused
FROM Roll R (NOLOCK)
LEFT JOIN RollBatch RB (NOLOCK) ON R.RollID = RB.RollID
LEFT JOIN SOX.DetailedResult DR (NOLOCK) ON RB.RollBatchID = DR.RollBatchID
WHERE DR.Operator = 'myfamily\" + spec.Username + @"'
AND DR.StepTypeID = '" + step + @"'
AND DR.LastDocDate > '" + System.DateTime.Today + "'";
sql += @"ORDER BY R.RollName";
return sql;
}
public List<Roll> GetRolls(string step, string state, string priority, Specialist user, string project, string rollname)
{
string sql = MyRollsSQLString(step, state, priority, user, project, rollname);
var reader = Data_Context.RunSelectSQLQuery(sql, 60);
if (reader.HasRows)
{
List<Roll> rollList = new List<Roll>();
while (reader.Read())
{
var roll = RollReader(reader);
rollList.Add(roll);
}
Data_Context.CloseConnection();
return rollList;
}
else return null;
}
/// <summary>
/// Finds the correct sql string based on caller
/// </summary>
private string MyRollsSQLString(string step, string state, string priority, Specialist user, string project, string rollname)
{
string sql = @"SELECT
WI.WorkItemID
,R.ProjectID
,WI.WorkItemName Rollname
,WI.Priority
,WI.UserName
,Q.QueueName
,WI.State
,WI.LastUpdateTime
FROM WorkItem WI (NOLOCK)
LEFT JOIN Queue Q (NOLOCK) ON WI.QueueId = Q.QueueId
LEFT JOIN Dexter_DeeDee..Roll R (NOLOCK) ON R.RollName = WI.WorkItemName
WHERE WI.Type = 'Roll'";
if (project != "")
sql += @" AND R.ProjectID = '" + project + "'";
if (rollname != "")
sql += @" AND WI.WorkItemName like '%" + rollname + "%'";
if (step != "")
{
if (step.ToLower() == "auditing")
{
sql += @" AND (Q.QueueName = 'ImageQA'
OR Q.QueueName = 'ImageQE')";
}
else if (step.ToLower() == "incomplete")
{
sql += @" AND Q.QueueName != 'BatchExporting'";
}
else
sql += @" AND Q.QueueName = '" + step + "'";
}
if (user != null)
sql += @" AND WI.UserName = '******'";
if (priority != "")
sql += @" AND WI.Priority = " + priority;
if (state != "")
sql += @" AND WI.State = '" + state + "'";
return sql;
}
public GetRollsArgument(Specialist spec, string step)
{
this.Specialist = spec;
this.Step = step;
this.Tab = 1;
}
public List<Specialist> GetSpecialists()
{
if (_specialists == null)
{
string sql = SpecialistsSQLString();
SqlDataReader reader = Data_Context.RunSelectSQLQuery(sql, 30);
var specialists = new List<Specialist>();
if (reader.HasRows)
{
List<Roll> rollList = new List<Roll>();
while (reader.Read())
{
string i = reader["employee_id"] as string;
int id = Convert.ToInt32(i);
string fName = reader["employee_firstname"] as string;
string lName = reader["employee_lastname"] as string;
string uName = reader["username"] as string;
Specialist spec = new Specialist(fName, lName, uName);
specialists.Add(spec);
}
//return specialists;
_specialists = specialists;
}
else return null;
}
Data_Context.CloseConnection();
return _specialists;
}