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