public static void createNewRequisitionList(Dictionary <string, List <RequisitionDetail> > dictionary) { UniversityStoreEntities context = new UniversityStoreEntities(); foreach (KeyValuePair <string, List <RequisitionDetail> > kvp in dictionary) { DateTime d = DateTime.Now; Employee emp = context.Employees.First <Employee>(x => x.EmployeeNumber == kvp.Key); RequisitionList rl = new RequisitionList(); rl.DepartmentCode = emp.DepartmentCode; rl.EmployeeNumber = emp.EmployeeNumber; rl.DateCreated = DateTime.Now; rl.Status = STATUS_PENDING; EntityCollection <RequisitionDetail> listDetails = new EntityCollection <RequisitionDetail>(); foreach (RequisitionDetail detail in kvp.Value) { listDetails.Add(detail); } rl.RequisitionDetails = listDetails; context.AddToRequisitionLists(rl); context.SaveChanges(); } }
public List <RequisitionList> getRequisitionListForApproval(string DeptID) { List <RequisitionList> Lt_Requisitions = new List <RequisitionList>(); using (SqlConnection C = new SqlConnection(DataLink.connectionString)) { C.Open(); string cmdtext = @"select u.Username, RL.RequisitionID,Rl.Comments,RL.statusOfRequest,RL.DateofSubmission,RL.UserID_FK,Rl.DeptID_FK from RequisitionList RL inner join Users u on RL.UserID_FK = u.UserID where RL.statusOfRequest = 'PendingforApproval' and Rl.DeptID_FK = '" + DeptID + "'"; SqlCommand cmd = new SqlCommand(cmdtext, C); SqlDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { RequisitionList St = new RequisitionList(); St.Username = (string)sdr["Username"]; St.RequisitionID = (int)sdr["RequisitionID"]; St.Comments = sdr["Comments"] != DBNull.Value ? (String)sdr["Comments"] : " "; St.statusOfRequest = (string)sdr["statusOfRequest"]; St.DateofSubmission = (DateTime)sdr["DateofSubmission"]; St.UserID_FK = (int)sdr["UserID_FK"]; St.DeptID_FK = (string)sdr["DeptID_FK"]; Lt_Requisitions.Add(St); } return(Lt_Requisitions); } }
public void createDisbursementDetail(RequisitionList rl) { using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string getItems = @"select Sum(rd.RequisitionQuantity) as amount,rd.ItemID, rl.DeptID_FK, dl.DisbursementID from RequisitionDetail rd, RequisitionList rl, DisbursementList dl where rl.DeptID_FK= dl.DepID and rd.RequisitionID= rl.RequisitionID and dl.DisbursementStatus='pending'" + "and rl.DeptID_FK='" + rl.DeptID_FK + "' and rl.statusOfRequest='approved'" + "group by rd.ItemID, rl.DeptID_FK, dl.DisbursementID"; SqlCommand cmd = new SqlCommand(getItems, connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { RequisitionList ds = new RequisitionList(); ds.amount = (int)reader["amount"]; ds.ItemID = (string)reader["ItemID"]; ds.DeptID_FK = (string)reader["DeptID_FK"]; //ds.RequisitionQuantity = (int)reader["RequisitionQuantity"]; ds.DisbursementID = (int)reader["DisbursementID"]; addIntoDisbursementDetail(ds); //rlList.Add(ds); } } }
public List <RequisitionList> GetRequisitionList() { List <RequisitionList> requisitions = new List <RequisitionList>(); using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string cmdquery = @" select rl.DateofSubmission,rl.RequisitionID,d.Departmentname,rl.DeptID_FK from RequisitionList rl,Department d where rl.DeptID_FK=d.DepartmentID group by d.Departmentname,rl.DeptID_FK,rl.RequisitionID,rl.DateofSubmission"; SqlCommand cmd = new SqlCommand(cmdquery, connection); //Call Execute reader to get query results SqlDataReader reader = cmd.ExecuteReader(); //Print out each record while (reader.Read()) { RequisitionList s = new RequisitionList(); s.RequisitionID = (int)reader["RequisitionID"]; s.Departmentname = (string)reader["Departmentname"]; s.DeptID_FK = (string)reader["DeptID_FK"]; s.DateofSubmission = (string)reader["DateofSubmission"]; requisitions.Add(s); } return(requisitions); } }
public List <RequisitionList> getRequisitionList(int UserID) { List <RequisitionList> Lt_Requisitions = new List <RequisitionList>(); using (SqlConnection C = new SqlConnection(DataLink.connectionString)) { C.Open(); string cmdtext = @"SELECT * from RequisitionList WHERE UserID_FK = '" + UserID + "' order by RequisitionID desc"; SqlCommand cmd = new SqlCommand(cmdtext, C); SqlDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { RequisitionList St = new RequisitionList(); St.RequisitionID = (int)sdr[0]; St.statusOfRequest = (string)sdr[1]; St.DateofSubmission = (DateTime)sdr[2]; St.DeptID_FK = (string)sdr[4]; St.UserID_FK = (int)sdr[5]; Lt_Requisitions.Add(St); } return(Lt_Requisitions); } }
private void LoadRequisitions() { String buildingID = rBuildings[cmbBuilding.SelectedIndex].ID.ToString(); String query = "SELECT r.id, r.trnDate, b.Building, r.account, r.processed, r.paid, r.reference, r.contractor, r.payreference, r.amount, r.ledger, b.acc, b.ownbank, b.datapath"; query += " FROM tblRequisition AS r INNER JOIN tblBuildings AS b ON r.building = b.id"; query += " WHERE b.id = " + buildingID + " ORDER BY trnDate"; Dictionary <String, Object> sqlParms = new Dictionary <string, object>(); DataSet dsRequisitions = dh.GetData(query, null, out status); unProcessedRequisitions.Clear(); unPaidRequisitions.Clear(); paidRequisitions.Clear(); if (dsRequisitions != null && dsRequisitions.Tables.Count > 0 && dsRequisitions.Tables[0].Rows.Count > 0) { foreach (DataRow dr in dsRequisitions.Tables[0].Rows) { RequisitionList r = new RequisitionList() { ID = Convert.ToString(dr["id"]), trnDate = Convert.ToDateTime(dr["trnDate"]), building = Convert.ToString(dr["Building"]), account = Convert.ToString(dr["account"]), reference = Convert.ToString(dr["reference"]), payreference = Convert.ToString(dr["payreference"]), amount = Convert.ToDouble(dr["amount"]), ledger = Convert.ToString(dr["ledger"]), paid = Convert.ToBoolean(dr["paid"]), }; bool paid = r.paid; bool matched = false; bool processed = Convert.ToBoolean(dr["processed"]); //handled by a batch process now //String ledger = r.ledger.Split(new String[] { ":" }, StringSplitOptions.None)[0]; //if (r.account.ToUpper() == "TRUST" && !paid) //{ // matched = GetTransactions(Utilities.GetTrustPath(), r.trnDate, dr["acc"].ToString(), ledger, r.amount); //} //else if (!paid) //{ // matched = GetTransactions(Convert.ToString(dr["datapath"]), r.trnDate, Convert.ToString(dr["ownbank"]), ledger, r.amount * -1); //} if (!processed) { unProcessedRequisitions.Add(r); } else if (!matched && !paid) { unPaidRequisitions.Add(r); } } } }
public void addIntoDisbursementDetail(RequisitionList rl) { // System.Guid DisbursementDetailID = System.Guid.NewGuid(); using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string getItems = @"insert into DisbursementDetails(DisbursementID, DepID, ActualQty, ItemID, DeliveredQty) values('" + rl.DisbursementID + "','" + rl.DeptID_FK + "','" + rl.amount + "','" + rl.ItemID + "'," + 0 + ")"; SqlCommand cmd = new SqlCommand(getItems, connection); cmd.ExecuteNonQuery(); } }
public void addIntoDisbursementList(RequisitionList rl) { string DisbursementStatus = "pending"; using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string getItems = @"insert into DisbursementList( DepID, DisbursementStatus, OTP) values('" + rl.DeptID_FK + "','" + DisbursementStatus + "'," + 0 + ")"; SqlCommand cmd = new SqlCommand(getItems, connection); cmd.ExecuteNonQuery(); } }
public static void changeReqListStatusClosed(string rlistNumber) { UniversityStoreEntities context = new UniversityStoreEntities(); System.Int32 rln = Convert.ToInt32(rlistNumber); var query = from x in context.RequisitionLists where x.RequisitionListNumber == rln select x; RequisitionList rl = query.First <RequisitionList>(); rl.Status = STATUS_CLOSED; context.SaveChanges(); }
public ActionResult ApproveRequisitionDetails(RequisitionList r) { Data_RequestDetails ReqDetails = new Data_RequestDetails(); List <WishList> DetailsOfRequest = ReqDetails.RequestDetails(r.RequisitionID); ViewBag.DetailsOfRequest = DetailsOfRequest; ViewData["RequestID"] = r.RequisitionID; ViewData["Dateofsub"] = r.DateofSubmission; ViewData["status"] = r.statusOfRequest; return(View()); }
public static RequisitionList addNewReqList(string empNumber) { UniversityStoreEntities context = new UniversityStoreEntities(); DateTime d = DateTime.Now; Employee emp = context.Employees.First <Employee>(x => x.EmployeeNumber == empNumber); RequisitionList rl = new RequisitionList(); rl.DepartmentCode = emp.DepartmentCode; rl.EmployeeNumber = emp.EmployeeNumber; rl.DateCreated = DateTime.Now; rl.Status = STATUS_PENDING; context.AddToRequisitionLists(rl); context.SaveChanges(); return(rl); }
public ActionResult ViewRequestInfo(RequisitionList r) { Data_RequestDetails ReqDetails = new Data_RequestDetails(); List <WishList> DetailsOfRequest = ReqDetails.RequestDetails(r.RequisitionID); ViewBag.DetailsOfRequest = DetailsOfRequest; ViewData["RequestID"] = r.RequisitionID; ViewData["Dateofsub"] = r.DateofSubmission; ViewData["status"] = r.statusOfRequest; Users users = Data_Users.GetInfoByUserID((int)Session["UserID"]); ViewData["Userinfo"] = users; return(View()); }
public void showAllDeptName() { using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string getItems = @"select d.DepartmentID from Department d"; SqlCommand cmd = new SqlCommand(getItems, connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { RequisitionList ds = new RequisitionList(); ds.DeptID_FK = (string)reader["DepartmentID"]; createDisbursementDetail(ds); } } }
public ActionResult RejectRequest(int ID, string Comments) { Data_CancelRequest DC = new Data_CancelRequest(); if (Comments.Length == 0) { DC.RejectRequest(ID); } else { DC.RejectRequestwithComments(ID, Comments); } //return RedirectToAction("ApproveRequisition"); //Email Alert to users for Reject request. SendEmailNotification sen = new SendEmailNotification(); // Users userInfo = Data_Users.GetInfoByUserID((int)Session["UserID"]); RequisitionList requestInfo = Data_CancelRequest.RequestInfo(ID); int empID = (int)requestInfo.UserID_FK; Users userInfo = Data_Users.GetInfoByUserID(empID); Department department = Data_Department.GetDepartmentInfoByID(userInfo.DeptID_FK); String Useremail = userInfo.EmailID; String EmailSubject = "Request Rejected for RequisitionID#" + ID; String EmailBody = "<p> Dear " + userInfo.Username + ",</p>"; EmailBody += "<p>Your request " + ID + " has been Rejected by the the your Department Head " + department.DepartmentHead + ". For more details please login into your Account or Please contact your Department Head."; EmailBody += "<p>Thank you<br/>Logic University Staionery Store</p>"; EmailBody += "<p> Please do not reply to this email it is auto-generated.</p>"; sen.SendEmailHTML(Useremail, EmailSubject, EmailBody); return(RedirectToAction("ApproveRequisition")); }
/* public void DeleteRequestDetails(int RequestID) * { * * using (SqlConnection conn = new SqlConnection(DataLink.connectionString)) * { * conn.Open(); * string cmdtext = @"UPDATE RequisitionList SET statusOfRequest='Cancelled' where RequisitionID = '" + RequestID + "'"; // Delete request details(items ) from Requsistion details tabel * SqlCommand cmd = new SqlCommand(cmdtext, conn); * cmd.ExecuteNonQuery(); * } * * }*/ public static RequisitionList RequestInfo(int requestID) { RequisitionList resquestinfo = new RequisitionList(); using (SqlConnection conn = new SqlConnection(DataLink.connectionString)) { conn.Open(); string cmdtext = @"select * from RequisitionList where RequisitionID= '" + requestID + "'"; SqlCommand cmd = new SqlCommand(cmdtext, conn); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { //Debug.WriteLine("Hello"); resquestinfo = new RequisitionList(); resquestinfo.UserID_FK = (int)reader["UserID_FK"]; resquestinfo.DeptID_FK = (string)reader["DeptID_FK"]; } return(resquestinfo); } }
public int getRequisitionID() { RequisitionList reqInfo = new RequisitionList(); using (SqlConnection conn = new SqlConnection(DataLink.connectionString)) { conn.Open(); string cmdtext = @"SELECT TOP 1 RequisitionID from RequisitionList WHERE UserID_FK = '" + (int)Session["UserID"] + "' AND DateOfSubmission = '" + DateTime.Today.ToString("yyyy-MM-dd") + "' ORDER BY RequisitionID DESC"; SqlCommand cmd = new SqlCommand(cmdtext, conn); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { reqInfo = new RequisitionList(); reqInfo.RequisitionID = (int)reader["RequisitionID"]; } } int RequetID = reqInfo.RequisitionID; return(RequetID); }
public void createDisbursementList() { using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string getItems = @"select d.DepartmentID from Department d, RequisitionDetail rd, RequisitionList rl, Stationery s where rd.RequisitionID = rl.RequisitionID and d.DepartmentID = rl.DeptID_FK and s.ItemID = rd.ItemID and rl.statusOfRequest ='Approved' group by d.DepartmentID"; SqlCommand cmd = new SqlCommand(getItems, connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { // System.Guid DisbursementID = System.Guid.NewGuid(); RequisitionList ds = new RequisitionList(); ds.DeptID_FK = (string)reader["DepartmentID"]; addIntoDisbursementList(ds); } } }