public void UpdateChangeReqstApproval() { //find not signed=2 status code in all activities table //if we do not find it then all are signed as there will be two status 1=signed 2=not signed int ownrnotsgnedactvty = 0; int aprvrnotsgnedactvty = 0; int rvwrnotsgnedactvty = 0; using (var dbctx = new RadiantSOPEntities()) { ownrnotsgnedactvty = dbctx.fileownersactivities.Where(o => o.changerequestid == ChangeRequestID && o.approvalstatuscode == 2).Select(o => o.owneractivityid).FirstOrDefault(); aprvrnotsgnedactvty = dbctx.fileapproversactivities.Where(o => o.changerequestid == ChangeRequestID && o.approvalstatuscode == 2).Select(o => o.approveractivityid).FirstOrDefault(); rvwrnotsgnedactvty = dbctx.filereviewersactivities.Where(o => o.changerequestid == ChangeRequestID && o.approvalstatuscode == 2).Select(o => o.revieweractivityid).FirstOrDefault(); //if all signed the SOP then update then update changeactivity table with signed status //so approver can publish the SOP if (ownrnotsgnedactvty == 0 && aprvrnotsgnedactvty == 0 && rvwrnotsgnedactvty == 0) { var result = dbctx.filechangerequestactivities.SingleOrDefault(b => b.changerequestid == ChangeRequestID); if (result != null) { result.approvalstatuscode = 1; //1=signed result.statusdatetime = DateTime.Today; dbctx.SaveChanges(); } } } }
public void GetUserByEmail() { //ctx.vwUsers.Where(i => i.departmentcode == departmentcode).FirstOrDefault(); //Query Entity Framework by using type of query- LINQ - Entities using (var ctx = new RadiantSOPEntities()) { //first LINQ query through class var employee = ctx.vwUsers.Select(x => new Employee() { userid = x.userid1, useremailaddress = x.useremailaddress, userfullname = x.userfullname, userjobtitle = x.jobtitle, departmentcode = (short)x.departmentcode, departmentname = x.departmentname }).Where(q => q.useremailaddress.Trim().ToLower() == useremailaddress.Trim().ToLower()); //assign property from returned object from linq above foreach (Employee emp in employee) { userid = emp.userid; userfullname = emp.userfullname; userjobtitle = emp.userjobtitle; departmentcode = emp.departmentcode; departmentname = emp.departmentname; } } }
public bool AuthenticateUserWithSOP(string pAuthtype, int pFileid) { bool bAuthenticate = false; GetUserByEmail(); //check activities database to find him/her as this file approver if (pAuthtype.ToLower().Trim() == "approver") { using (var ctx = new RadiantSOPEntities()) { var aprvr = ctx.vwApprvrsSignatures.Where(u => u.fileid == pFileid && u.approverid == userid).Select(u => u.approverid).FirstOrDefault(); if (aprvr > 0) { bAuthenticate = true; } } } //check activities database to find him/her as reviewer if (pAuthtype.ToLower().Trim() == "reviewer") { using (var ctx = new RadiantSOPEntities()) { var rvwr = ctx.vwRvwrsSignatures.Where(u => u.fileid == pFileid && u.reviewerid == userid).Select(u => u.reviewerid).FirstOrDefault(); if (rvwr > 0) { bAuthenticate = true; } } } //check activities database to find him/her as owner if (pAuthtype.ToLower().Trim() == "owner") { using (var ctx = new RadiantSOPEntities()) { var ownr = ctx.vwOwnerSignatures.Where(u => u.fileid == pFileid && u.ownerid == userid).Select(u => u.ownerid).FirstOrDefault(); if (ownr > 0) { bAuthenticate = true; } } } return(bAuthenticate); }
public bool GetSignStatusOfSignatory(string approvertype, string emailaddress) { bool signstatus = false; int userid = 0; int approveid = 0; int ownershipid = 0; int reviewid = 0; short signstatuscode = 0; using (var dbctx = new RadiantSOPEntities()) { if (approvertype == "onwer") { userid = dbctx.vwUsers.Where(u => u.useremailaddress.Trim().ToLower() == emailaddress.Trim().ToLower()).Select(u => u.userid1).FirstOrDefault(); ownershipid = dbctx.fileowners.Where(u => u.fileid == FileID && u.ownerid == userid && u.ownerstatuscode == 1).Select(u => u.ownershipid).FirstOrDefault(); signstatuscode = Convert.ToInt16(dbctx.fileownersactivities.Where(u => u.ownershipid == ownershipid && u.changerequestid == ChangeRequestID).Select(u => u.approvalstatuscode).FirstOrDefault()); if (signstatuscode == 1) //approver signed { signstatus = true; } } if (approvertype == "approver") { userid = dbctx.vwUsers.Where(u => u.useremailaddress.Trim().ToLower() == emailaddress.Trim().ToLower()).Select(u => u.userid1).FirstOrDefault(); approveid = dbctx.fileapprovers.Where(u => u.fileid == FileID && u.approverid == userid && u.approverstatuscode == 1).Select(u => u.approveid).FirstOrDefault(); signstatuscode = Convert.ToInt16(dbctx.fileapproversactivities.Where(u => u.approveid == approveid && u.changerequestid == ChangeRequestID).Select(u => u.approvalstatuscode).FirstOrDefault()); if (signstatuscode == 1) //approver signed { signstatus = true; } } if (approvertype == "reviewer") { userid = dbctx.vwUsers.Where(u => u.useremailaddress.Trim().ToLower() == emailaddress.Trim().ToLower()).Select(u => u.userid1).FirstOrDefault(); reviewid = dbctx.filereviewers.Where(u => u.fileid == FileID && u.reviewerid == userid && u.reviewerstatuscode == 1).Select(u => u.reviewid).FirstOrDefault(); signstatuscode = Convert.ToInt16(dbctx.filereviewersactivities.Where(u => u.reviewid == reviewid && u.changerequestid == ChangeRequestID).Select(u => u.approvalstatuscode).FirstOrDefault()); if (signstatuscode == 1) //approver signed { signstatus = true; } } } return(signstatus); }
public static string GetLoggedInUserFullName() { string fullname = ""; RadiantSOPEntities ctx = new RadiantSOPEntities(); //lsopno = foldername + "-001"; fullname = ctx.getUserFullNameByEmailUserID(GetCurrentLoggedInUserEmail(), 0).FirstOrDefault().ToString(); return(fullname); }
public void GetReviewerActivityID() { int rvwractvityid = 0; int reviewid = 0; using (var dbctx = new RadiantSOPEntities()) { reviewid = dbctx.filereviewers.Where(o => o.reviewerid == LoggedInUserID && o.fileid == FileID && o.reviewerstatuscode == 1).Select(o => o.reviewid).FirstOrDefault(); rvwractvityid = dbctx.filereviewersactivities.Where(o => o.reviewid == reviewid && o.changerequestid == ChangeRequestID).Select(o => o.revieweractivityid).FirstOrDefault(); ReviewerActivityID = rvwractvityid; } }
public void GetApproverActivityID() { int aprvractvityid = 0; int approveid = 0; using (var dbctx = new RadiantSOPEntities()) { approveid = dbctx.fileapprovers.Where(o => o.approverid == LoggedInUserID && o.fileid == FileID && o.approverstatuscode == 1).Select(o => o.approveid).FirstOrDefault(); aprvractvityid = dbctx.fileapproversactivities.Where(o => o.approveid == approveid && o.changerequestid == ChangeRequestID).Select(o => o.approveractivityid).FirstOrDefault(); ApproverActivityID = aprvractvityid; } }
public void GetOwnerActivityID() { int owneractvityid = 0; int ownershipid = 0; using (var dbctx = new RadiantSOPEntities()) { //verify logged in user is activer owner ownershipid = dbctx.fileowners.Where(o => o.ownerid == LoggedInUserID && o.fileid == FileID && o.ownerstatuscode == 1).Select(o => o.ownershipid).FirstOrDefault(); owneractvityid = dbctx.fileownersactivities.Where(o => o.ownershipid == ownershipid && o.changerequestid == ChangeRequestID).Select(o => o.owneractivityid).FirstOrDefault(); OwnerActivityID = owneractvityid; } }
public static string GetLoggedInUserSOPDeptName() { string useremail = GetCurrentLoggedInUserEmail(); // string useremail = "*****@*****.**"; string sopdeptname = ""; using (var dbctx = new RadiantSOPEntities()) { sopdeptname = dbctx.vwUsers.Where(u => u.useremailaddress.Trim().ToLower() == useremail.Trim().ToLower()).Select(u => u.departmentname).FirstOrDefault(); } return(sopdeptname); }
public static int GetLoggedInUserID() { string useremail = ""; int userid = 0; useremail = GetCurrentLoggedInUserEmail().Trim(); // useremail = "*****@*****.**"; using (var dbctx = new RadiantSOPEntities()) { userid = dbctx.users.Where(u => u.useremailaddress.Trim().ToLower() == useremail.Trim().ToLower() && u.userstatuscode == 1).Select(u => u.userid).FirstOrDefault(); } return(userid); }
public static short GetLoggedInUserSOPDeptCode() { string useremail = GetCurrentLoggedInUserEmail(); // string useremail = "*****@*****.**"; int userid = 0; short deptcode = 0; short sopdeptcode = 0; userid = GetLoggedInUserID(); using (var dbctx = new RadiantSOPEntities()) { deptcode = Convert.ToInt16(dbctx.users.Where(u => u.useremailaddress.Trim().ToLower() == useremail.Trim().ToLower() && u.userstatuscode == 1).Select(u => u.departmentcode).FirstOrDefault()); sopdeptcode = Convert.ToInt16(dbctx.codesdepartments.Where(u => u.departmentcode == deptcode).Select(u => u.sopdeptcode).FirstOrDefault()); } return(sopdeptcode); }
public static List <SOPClass> GetFolders() { List <SOPClass> folderlist; using (var ctx = new RadiantSOPEntities()) { var folders = ctx.deptsopfiles.Select(x => new SOPClass() { FileID = x.FileID, FileName = x.DeptFileName, FilePath = x.SPFilePath, FileLink = x.SPFileLink, SOPNo = x.SOPNo, FileStatuscode = x.filestatuscode }).Where(s => s.FilePath == "SOP/" && s.FileID != 193 && s.FileStatuscode == 3).OrderBy(s => s.FileName); folderlist = folders.ToList(); } return(folderlist); }
//public bool AuthenticateUser(string authtype, string useremail) //{ // bool bAuthenticate = false; // //check user database sopadminuser if authtype=admin // if (authtype.ToLower().Trim() == "admin") // { // using (var ctx = new RadiantSOPEntities()) // { // var admnu = ctx.users.Where(u => u.sopadminuser == true && u.useremailaddress == useremail).Select(u => u.userid).FirstOrDefault(); // if (admnu >0) // { // bAuthenticate = true; // } // } // } // return bAuthenticate; //} //public void GetUserInfoByEmail() //{ // RadiantSOPEntities ctx = new RadiantSOPEntities(); // //lsopno = foldername + "-001"; // userfullname = ctx.getUserFullNameByEmailUserID(useremailaddress, 0).FirstOrDefault().ToString(); // userjobtitle = ctx.GetUserJobTitleByEmailUserID(useremailaddress, 0).FirstOrDefault().ToString(); //} public void GetEmployeesByDeptCode() { //ctx.vwUsers.Where(i => i.departmentcode == departmentcode).FirstOrDefault(); //Query Entity Framework by using type of query- LINQ - Entities Employee[] empllist; using (var ctx = new RadiantSOPEntities()) { var employees = ctx.vwUsers.Select(x => new Employee() { userid = x.userid1, useremailaddress = x.useremailaddress, userfullname = x.userfullname, userjobtitle = x.jobtitle, departmentcode = (short)x.departmentcode, departmentname = x.departmentname }).Where(q => q.departmentcode == departmentcode || q.departmentcode == 12); //empllist = employees.ToList(); empllist = new Employee[employees.Count()]; int i = 0; foreach (Employee emp in employees) { empllist[i] = emp; i++; } } employees = empllist; }
public bool UpdateSignatures() { bool success = false; SOPClass oSOP = new SOPClass(); oSOP.SiteUrl = Utility.GetSiteUrl(); oSOP.FileID = FileID; oSOP.FileChangeRqstID = ChangeRequestID; // oSOP.GetSOPInfoByFileID(); oSOP.GetSOPInfo(); using (var dbcontext = new RadiantSOPEntities()) { if (LoggedInSignedAsOwner) { var result = dbcontext.fileownersactivities.SingleOrDefault(b => b.owneractivityid == OwnerActivityID); if (result != null) { result.approvalstatuscode = 1; //1=signed result.statusdatetime = DateTime.Today; dbcontext.SaveChanges(); success = true; } if (success) { int ownerid = 0; int ownershipid = 0; string owneremail = ""; bool removecontr = true; ownershipid = Convert.ToInt32(dbcontext.fileownersactivities.Where(o => o.owneractivityid == OwnerActivityID && o.changerequestid == ChangeRequestID).Select(o => o.ownershipid).FirstOrDefault()); ownerid = dbcontext.fileowners.Where(o => o.ownershipid == ownershipid && o.ownerstatuscode == 1).Select(o => o.ownerid).FirstOrDefault(); // fileid = dbcontext.fileowners.Where(o => o.ownershipid == ownershipid && o.ownerstatuscode == 1).Select(o => o.fileid).FirstOrDefault(); owneremail = dbcontext.vwUsers.Where(u => u.userid1 == ownerid).Select(o => o.useremailaddress).FirstOrDefault(); //first check if this owener is approver or reviewer and have signed it or not //if this person signs in all group then remove contribute and give read permission //if this person does not sign in any approver group then don't remove contribute permission if (owneremail.Trim().ToLower() == oSOP.FileApproverEmail.Trim().ToLower()) { if (oSOP.FileApprover.signstatuscode == 2) //has not signed as approver so keep contribute permission { removecontr = false; } } //if signed as approver then check if same person is reviewer and has not signed, if so do not remove contribute foreach (Employee rvwr in oSOP.FileReviewers) { if (owneremail.Trim().ToLower() == rvwr.useremailaddress.Trim().ToLower()) { if (rvwr.signstatuscode == 2) //has not signed as reviewer so keep contribute { removecontr = false; } break; } } if (removecontr) { oSOP.AssignFilePermissionToUsers("contribute", "remove", owneremail.Trim().ToLower()); // System.Threading.Thread.Sleep(3000); oSOP.AssignFilePermissionToUsers("View Only", "add", owneremail.Trim().ToLower()); } } //end checking success of signing } if (LoggedInSignedAsApprover) { oSOP.GetSOPInfo(); var result = dbcontext.fileapproversactivities.SingleOrDefault(b => b.approveractivityid == ApproverActivityID); if (result != null) { result.approvalstatuscode = 1; //1=signed result.statusdatetime = DateTime.Today; dbcontext.SaveChanges(); success = true; } if (success) { int apporverid = 0; int approveid = 0; string approveremail = ""; bool removecontr = true; approveid = Convert.ToInt32(dbcontext.fileapproversactivities.Where(o => o.approveractivityid == ApproverActivityID && o.changerequestid == ChangeRequestID).Select(o => o.approveid).FirstOrDefault()); apporverid = dbcontext.fileapprovers.Where(o => o.approveid == approveid && o.approverstatuscode == 1).Select(o => o.approverid).FirstOrDefault(); // fileid = dbcontext.fileowners.Where(o => o.ownershipid == ownershipid && o.ownerstatuscode == 1).Select(o => o.fileid).FirstOrDefault(); approveremail = dbcontext.vwUsers.Where(u => u.userid1 == apporverid).Select(o => o.useremailaddress).FirstOrDefault(); if (approveremail.Trim().ToLower() == oSOP.FileOwnerEmail.Trim().ToLower()) { if (oSOP.FileOwner.signstatuscode == 2) //has not signed as owner so keep contribute permission { removecontr = false; } } //if signed as approver then check if same person is reviewer and has not signed, if so do not remove contribute foreach (Employee rvwr in oSOP.FileReviewers) { if (approveremail.Trim().ToLower() == rvwr.useremailaddress.Trim().ToLower()) { if (rvwr.signstatuscode == 2) //has not signed as reviewer so keep contribute { removecontr = false; } break; } } if (removecontr) { oSOP.AssignFilePermissionToUsers("contribute", "remove", approveremail.Trim().ToLower()); // System.Threading.Thread.Sleep(3000); oSOP.AssignFilePermissionToUsers("View Only", "add", approveremail.Trim().ToLower()); } } } if (LoggedInSignedAsReviewer) { oSOP.GetSOPInfo(); var result = dbcontext.filereviewersactivities.SingleOrDefault(b => b.revieweractivityid == ReviewerActivityID); if (result != null) { result.approvalstatuscode = 1; //1=signed result.statusdatetime = DateTime.Today; dbcontext.SaveChanges(); success = true; } if (success) { int reviewerid = 0; int reviewid = 0; string revieweremail = ""; bool removecontr = true; reviewid = Convert.ToInt32(dbcontext.filereviewersactivities.Where(o => o.revieweractivityid == ReviewerActivityID && o.changerequestid == ChangeRequestID).Select(o => o.reviewid).FirstOrDefault()); reviewerid = dbcontext.filereviewers.Where(o => o.reviewid == reviewid && o.reviewerstatuscode == 1).Select(o => o.reviewerid).FirstOrDefault(); // fileid = dbcontext.fileowners.Where(o => o.ownershipid == ownershipid && o.ownerstatuscode == 1).Select(o => o.fileid).FirstOrDefault(); revieweremail = dbcontext.vwUsers.Where(u => u.userid1 == reviewerid).Select(o => o.useremailaddress).FirstOrDefault(); if (revieweremail.Trim().ToLower() == oSOP.FileOwnerEmail.Trim().ToLower()) { if (oSOP.FileOwner.signstatuscode == 2) //has not signed as owner so keep contribute permission { removecontr = false; } } if (revieweremail.Trim().ToLower() == oSOP.FileApproverEmail.Trim().ToLower()) { if (oSOP.FileApprover.signstatuscode == 2) //has not signed as approver so keep contribute permission { removecontr = false; } } if (removecontr) { oSOP.AssignFilePermissionToUsers("contribute", "remove", revieweremail.Trim().ToLower()); // System.Threading.Thread.Sleep(3000); oSOP.AssignFilePermissionToUsers("View Only", "add", revieweremail.Trim().ToLower()); } } } } oSOP = null; return(success); }