/// <summary> /// This checks to determine if ALL grants for the given month have been approved. /// </summary> /// <param name="mo"></param> /// <param name="conn"></param> /// <returns></returns> private static List<GrantMonth> getTheFinalApproved(MailObject mo, OleDbConnection conn) { if (conn.State != ConnectionState.Open) { conn.Open(); } string str = "select * from WorkMonth where EmpID=" + mo.emp.ID.ToString() + " and WorkingMonth=" + mo.month.ToString(); str += " and WorkYear=" + mo.year.ToString(); OleDbCommand comm = new OleDbCommand(str, conn); OleDbDataAdapter adapter = new OleDbDataAdapter(comm); DataSet set = new DataSet(); try { adapter.Fill(set); } catch (System.Exception e) { return null; } if (set.Tables == null || set.Tables.Count < 1) { return null; } List<GrantMonth> grants = new List<GrantMonth>(); foreach (DataRow dr in set.Tables[0].Rows) { GrantMonth gm = new GrantMonth(dr); grants.Add(gm); } var sloopG = grants.Where(g => g.curStatus == System.Convert.ToInt32(GrantMonth.status.approved)).ToList(); if (sloopG != null && sloopG.Count > 0) { conn.Close(); return sloopG; } return null; }
public static bool updateGrantStatus(Employee sup, OleDbConnection conn, Employee emp, DateTime selDate, int grantID, GrantMonth.status stat) { if (conn.State != ConnectionState.Open) { conn.Open(); } int istat = System.Convert.ToInt32(stat); string select = "select * from WorkMonth where EmpID=" + emp.ID.ToString() + " and WorkingMonth=" + (selDate.Month - 1).ToString() + " and WorkYear=" + selDate.Year.ToString() + " and GrantID=" + grantID.ToString(); string insert = "insert into WorkMonth (EmpID, WorkingMonth, WorkYear, GrantID, SupervisorID, Status) "; insert += "values(" + emp.ID.ToString() + "," + (selDate.Month - 1).ToString() + "," + selDate.Year.ToString() + "," + grantID.ToString() + "," + sup.ID.ToString() + ",1);"; string update = "update WorkMonth set status=" + istat.ToString() + " where EmpID=" + emp.ID.ToString() + " and WorkingMonth=" + (selDate.Month - 1).ToString() + " and WorkYear=" + selDate.Year.ToString() + " and GrantID=" + grantID.ToString(); OleDbCommand comm = new OleDbCommand(select, conn); OleDbCommand up = new OleDbCommand(update, conn); OleDbCommand ins = new OleDbCommand(insert, conn); DataSet set = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; try { adapter.Fill(set); } catch (System.Exception ex) { return false; } if (set.Tables.Count > 0 && set.Tables[0].Rows.Count > 0) { set.Reset(); up.ExecuteNonQuery(); } else { set.Reset(); ins.ExecuteNonQuery(); } return true; }