/// <summary>
/// Function used for check status & DraftList Based on the search condition
/// </summary>
/// <param name="ReferID">ReferenceID (Optional)</param>
/// <param name="CStat">Status (Optional)</param>
/// <param name="FDate">FromDate (Optional)</param>
/// <param name="TDate">ToDate (Optional)</param>
/// <param name="Appl">Applicant Name (Optional)</param>
/// <param name="UserId">UserId for checking authentication</param>
/// <param name="Type">Review or DraftEdit</param>
/// SP : K2_CheckStatus
/// <returns></returns>
/// <summary>
public List <ChkFrmStatus> GetRequestList(string ReferID, string CStat, string FDate, string TDate, string Appl, string UserId, string Type, string ApplicantEmployeeId, int offset = 0, int limit = 999999, string sort = null)
{
List <CheckStatus> StatusList = new List <CheckStatus>();
List <ChkFrmStatus> FormRequests = new List <ChkFrmStatus>();
try
{
SqlParameter[] sqlp =
{
new SqlParameter("ReferID", DBNull.Value),
new SqlParameter("CStatus", DBNull.Value),
new SqlParameter("FDate", DBNull.Value),
new SqlParameter("TDate", DBNull.Value),
new SqlParameter("Applicant", DBNull.Value),
new SqlParameter("UserId", UserId),
new SqlParameter("Type", Type),
new SqlParameter("offset", offset),
new SqlParameter("limit", limit),
new SqlParameter("sort", sort),
new SqlParameter("ApplicantEmployeeId", DBNull.Value)
};
if (!string.IsNullOrEmpty(ReferID))
{
sqlp[0].Value = ReferID;
}
if (!string.IsNullOrEmpty(CStat))
{
sqlp[1].Value = CStat;
}
if (!string.IsNullOrEmpty(FDate))
{
sqlp[2].Value = FDate;
}
if (!string.IsNullOrEmpty(TDate))
{
sqlp[3].Value = TDate;
}
if (!string.IsNullOrEmpty(Appl))
{
sqlp[4].Value = Appl;
}
if (!string.IsNullOrEmpty(ApplicantEmployeeId))
{
sqlp[10].Value = ApplicantEmployeeId;
}
StatusList = Db.Database.SqlQuery <CheckStatus>("exec [K2_CheckStatus_new] @ReferID,@CStatus,@FDate,@TDate,@Applicant,@UserId,@Type,@offset,@limit,@sort,@ApplicantEmployeeId", sqlp).ToList();
foreach (var request in StatusList.DistinctBy(p => new { p.ProcInstID, p.FormID }))
{
ChkFrmStatus status = new ChkFrmStatus();
//var request = StatusList.Where(P => P.FormID == FormID).FirstOrDefault();
status.FormID = request.FormID;
status.ProcInstID = request.ProcInstID;
status.ReferenceID = request.ReferenceID;
status.FormStatus = request.FormStatus;
status.SubmittedOn = request.SubmittedOn;
status.ApplicantUserId = request.ApplicantUserID;
status.ApplicantEMP = request.ApplicantEMP;
status.ApplicantFNAME = request.ApplicantFNAME;
status.ApproverEmp = request.ApproverEMP;
status.ApproverFNAME = request.ApproverFNAME;
status.DisplayStatus = request.DisplayStatus;
status.LastUser = request.LastUser;
status.PreparerFNAME = request.PreparerFullName;
status.ActionTakerFullName = request.ActionTakerFullName;
//status.ActionTakerStatus = request.ActionTakerStatus;
status.ITSApproverFullName = request.ITSApproverFullName;
//status.ITSApproverStatus = request.ITSApproverStatus;
status.CurrentActor = request.CurrentActor;
List <ServiceLevel1> Level1lst = new List <ServiceLevel1>();
//foreach (string FirstLevelService in StatusList.Where(P => P.FormID == FormID).Select(P => P.MMenu).Distinct())
foreach (var FirstLevelService in StatusList.Where(P => P.FormID == request.FormID && P.ProcInstID == request.ProcInstID).Select(P => new { P.MMenu, P.MMenuGUID }).Distinct())
{
if (!string.IsNullOrEmpty(FirstLevelService.MMenu))
{
ServiceLevel1 Level1 = new ServiceLevel1();
Level1.Name = FirstLevelService.MMenu;
Level1.GUID = new Guid(FirstLevelService.MMenuGUID);
List <ServiceLevel2> Level2lst = new List <ServiceLevel2>();
foreach (var SecondLevelService in StatusList.Where(P => P.FormID == request.FormID && P.ProcInstID == request.ProcInstID && P.MMenu == FirstLevelService.MMenu).DistinctBy(P => P.SubMenu))
{
ServiceLevel2 Level2 = new ServiceLevel2();
Level2.Name = SecondLevelService.SubMenu;
Level2.GUID = new Guid(SecondLevelService.SubMenuGUID);
Level2.SValue = SecondLevelService.ServiceTypeValue;
List <ServiceLevel3> Level3lst = new List <ServiceLevel3>();
foreach (var ThirsLevelService in StatusList.Where(P => P.FormID == request.FormID && P.ProcInstID == request.ProcInstID && P.MMenu == FirstLevelService.MMenu && P.SubMenu == SecondLevelService.SubMenu).OrderBy(P => P.ServiceGUID))
{
ServiceLevel3 Level3 = new ServiceLevel3();
Level3.Name = ThirsLevelService.SSubMenu;
Level3.GUID = new Guid(ThirsLevelService.SSubMenuGUID);
Level3.SValue = ThirsLevelService.ServiceTypeValue;
Level3.ControlFlag = ThirsLevelService.ControlFlag;
Level3lst.Add(Level3);
}
if (Level3lst.Count > 0)
{
Level2.Level3 = Level3lst;
//Level2.GUID = Guid.Empty; // To return Null Value.
Level2.SValue = null;
}
else
{
Level2.Level3 = null;
}
Level2lst.Add(Level2);
}
if (Level2lst.Count > 0)
{
Level1.Level2 = Level2lst;
}
else
{
Level1.Level2 = null;
}
Level1lst.Add(Level1);
}
}
if (Level1lst.Count > 0)
{
status.RequestList = Level1lst;
}
else
{
status.RequestList = null;
}
FormRequests.Add(status);
}
}
catch (Exception ex)
{
FormRequests = null;
throw ex;
}
return(FormRequests);
}
/// <summary>
/// Function used for Worklist Based on the search condition
/// </summary>
/// <param name="ReferID">ReferenceID (Optional)</param>
/// <param name="CStat">Status (Optional)</param>
/// <param name="FDate">FromDate (Optional)</param>
/// <param name="TDate">ToDate (Optional)</param>
/// <param name="Appl">Applicant Name (Optional)</param>
/// <param name="UserId">UserId for checking authentication</param>
/// <param name="Type">Task Owner</param>
/// SP : K2_WorkList
/// <returns></returns>
/// <summary>
public List <ChkFrmStatus> GetWorklist(string ReferID, string CStat, string FDate, string TDate, string UserId, string SUser, string ProsIncId, int offset = 0, int limit = 999999, string sort = null, string applicant = null, string applicantEmpNo = null)
{
string proinsid = "";
WorkflowFacade workfacade = new WorkflowFacade();
List <WorklistItem> worklist = new List <WorklistItem>();
if (string.IsNullOrEmpty(SUser))
{
worklist = workfacade.GetWorklistItemForAllTasks(UserId);
//if (worklist.Count() > 0)
//{
// proinsid = String.Join(",", worklist.Select(P => P.ProcInstID)).TrimEnd(',');
//}
}
else
{
List <WorklistItem> sharedworklist = workfacade.GetWorklistItemForAllTasks(SUser);
var sharePermit = (from a in Db.SharingList
join b in Db.DelegationProcess on a.ActivityGroupID equals b.GroupID into ps
from b in ps.DefaultIfEmpty()
where a.DelegationType == "Sharing" &&
a.FromWorkerID == SUser &&
a.ToWorkerID == UserId
select b.K2StepName
).ToList();
foreach (var i in sharedworklist)
{
if (sharePermit.Contains(i.ActivityName))
{
worklist.Add(i);
}
}
}
List <CheckStatus> StatusList = new List <CheckStatus>();
List <ChkFrmStatus> FormRequests = new List <ChkFrmStatus>();
if (worklist.Count() > 0)
{
try
{
SqlParameter[] sqlp =
{
new SqlParameter("ReferID", DBNull.Value),
new SqlParameter("CStatus", DBNull.Value),
new SqlParameter("FDate", DBNull.Value),
new SqlParameter("TDate", DBNull.Value),
new SqlParameter("SUser", DBNull.Value),
new SqlParameter("userid", DBNull.Value),
new SqlParameter("TOwner", DBNull.Value),
new SqlParameter("ProcIncId", DBNull.Value),
new SqlParameter("offset", offset),
new SqlParameter("limit", limit),
new SqlParameter("sort", sort),
new SqlParameter("applicant", DBNull.Value),
new SqlParameter("applicantEmpNo", DBNull.Value)
};
if (!string.IsNullOrEmpty(ReferID))
{
sqlp[0].Value = ReferID;
}
if (!string.IsNullOrEmpty(CStat))
{
sqlp[1].Value = CStat;
}
if (!string.IsNullOrEmpty(FDate))
{
sqlp[2].Value = FDate;
}
if (!string.IsNullOrEmpty(TDate))
{
sqlp[3].Value = TDate;
}
if (!string.IsNullOrEmpty(SUser))
{
sqlp[4].Value = SUser;
}
if (!string.IsNullOrEmpty(UserId))
{
sqlp[5].Value = UserId;
}
if (!string.IsNullOrEmpty(applicant))
{
sqlp[11].Value = applicant;
}
if (!string.IsNullOrEmpty(applicantEmpNo))
{
sqlp[12].Value = applicantEmpNo;
}
if (string.IsNullOrEmpty(ProsIncId))
{
if (worklist.Count > 0)
{
sqlp[7].Value = String.Join(",", worklist.Select(P => P.ProcInstID)).TrimEnd(',');
}
}
else
{
sqlp[7].Value = ProsIncId;
}
StatusList = Db.Database.SqlQuery <CheckStatus>("exec [K2_WorkList] @ReferID,@CStatus,@FDate,@TDate,@SUser,@UserId,@TOwner,@ProcIncId,@offset,@limit,@sort,@applicant,@applicantEmpNo", sqlp).ToList();
//foreach (int FormID in StatusList.Select(P => P.FormID).Distinct())
foreach (var request in StatusList.DistinctBy(P => P.ProcInstID))
{
ChkFrmStatus status = new ChkFrmStatus();
//var request = StatusList.Where(P => P.FormID == FormID).FirstOrDefault();
if (!string.IsNullOrEmpty(request.ProcInstID))
{
int procid = 0;
procid = Convert.ToInt32(request.ProcInstID);
//if (worklist.Select(P => P.ProcInstID).Contains(procid))
//{
status.FormID = request.FormID;
status.ProcInstID = request.ProcInstID;
status.ReferenceID = request.ReferenceID;
status.FormStatus = request.FormStatus;
status.SubmittedOn = request.SubmittedOn;
status.ApplicantUserId = request.ApplicantUserID;
status.ApplicantEMP = request.ApplicantEMP;
status.ApplicantFNAME = request.ApplicantFNAME;
status.ApproverEmp = request.ApproverEMP;
status.ApproverFNAME = request.ApproverFNAME;
status.ProcInstID = request.ProcInstID;
status.DisplayStatus = request.DisplayStatus;
status.LastUser = request.LastUser;
status.ActionTakerFullName = request.ActionTakerFullName;
status.ITSApproverFullName = request.ITSApproverFullName;
status.PreparerFNAME = request.PreparerFullName;
status.CurrentActor = request.CurrentActor;
status.SUser = request.SUser;
//if (!string.IsNullOrEmpty(request.ProcInstID))
//{
//if (!proinsid.Split(',').Contains(request.ProcInstID))
//{
// status.ProcessUrl = worklist.Where(p => p.ProcInstID == procid).Select(P => P.Data).FirstOrDefault() + "&SharedBy=" + SUser;
// status.Type = "Sharing";
//}
//else
// status.ProcessUrl = worklist.Where(p => p.ProcInstID == procid).Select(P => P.Data).FirstOrDefault();
if (!string.IsNullOrEmpty(SUser))
{
status.Type = "Sharing";
}
//}
//status.ProcessUrl = worklist.Where(p => p.ProcInstID == procid).Select(P => P.Data).FirstOrDefault();
status.SN = worklist.Where(p => p.ProcInstID == procid).Select(P => P.SN).FirstOrDefault();
//var actions = worklist.Where(p => p.ProcInstID == procid).Select(P => P.Actions).FirstOrDefault();
//if (actions != null)
// status.actions = actions.Select(P => P.Name).ToList();
if (request.Permission != "2")
{
List <ServiceLevel1> Level1lst = new List <ServiceLevel1>();
foreach (var FirstLevelService in StatusList.Where(P => P.FormID == request.FormID && P.ProcInstID == request.ProcInstID).Select(P => new { P.MMenu, P.MMenuGUID }).Distinct())
{
if (!string.IsNullOrEmpty(FirstLevelService.MMenu))
{
ServiceLevel1 Level1 = new ServiceLevel1();
Level1.Name = FirstLevelService.MMenu;
Level1.GUID = new Guid(FirstLevelService.MMenuGUID);
List <ServiceLevel2> Level2lst = new List <ServiceLevel2>();
foreach (var SecondLevelService in StatusList.Where(P => P.FormID == request.FormID && P.ProcInstID == request.ProcInstID && P.MMenu == FirstLevelService.MMenu).DistinctBy(P => P.SubMenu))
{
ServiceLevel2 Level2 = new ServiceLevel2();
Level2.Name = SecondLevelService.SubMenu;
Level2.GUID = new Guid(SecondLevelService.SubMenuGUID);
Level2.SValue = SecondLevelService.ServiceTypeValue;
List <ServiceLevel3> Level3lst = new List <ServiceLevel3>();
foreach (var ThirsLevelService in StatusList.Where(P => P.FormID == request.FormID && P.ProcInstID == request.ProcInstID && P.MMenu == FirstLevelService.MMenu && P.SubMenu == SecondLevelService.SubMenu).OrderBy(P => P.ServiceGUID))
{
ServiceLevel3 Level3 = new ServiceLevel3();
Level3.Name = ThirsLevelService.SSubMenu;
Level3.GUID = new Guid(ThirsLevelService.SSubMenuGUID);
Level3.SValue = ThirsLevelService.ServiceTypeValue;
Level3.ControlFlag = ThirsLevelService.ControlFlag;
Level3lst.Add(Level3);
}
if (Level3lst.Count > 0)
{
Level2.Level3 = Level3lst;
//Level2.GUID = Guid.Empty; // To return Null Value.
Level2.SValue = null;
}
else
{
Level2.Level3 = null;
}
Level2lst.Add(Level2);
}
if (Level2lst.Count > 0)
{
Level1.Level2 = Level2lst;
}
else
{
Level1.Level2 = null;
}
Level1lst.Add(Level1);
}
}
if (Level1lst.Count > 0)
{
status.RequestList = Level1lst;
}
else
{
status.RequestList = null;
}
}
FormRequests.Add(status);
//}
}
}
}
catch (Exception ex)
{
FormRequests = null;
throw ex;
}
}
return(FormRequests);
}
public List <ChkFrmStatus> getApprovalList(string userid, string employeeid, string applicant, string approvalStartDate, string approvalEndDate, string status, string refid, string createStartDate, string createEndDate, string keyword)
{
try
{
List <ApproverHistory> rawHistory = new List <ApproverHistory>();
List <ChkFrmStatus> historyList = new List <ChkFrmStatus>();
string queryParameter = "@userid,@applicantEmpNo,@applicant,@approvalStart,@approvalEnd,@status,@refid,@createStart,@createEnd,@keyword";
SqlParameter[] sqlp =
{
new SqlParameter("userid", userid),
new SqlParameter("applicantEmpNo", DBNull.Value),
new SqlParameter("applicant", DBNull.Value),
new SqlParameter("approvalStart", DBNull.Value),
new SqlParameter("approvalEnd", DBNull.Value),
new SqlParameter("status", DBNull.Value),
new SqlParameter("refid", DBNull.Value),
new SqlParameter("createStart", DBNull.Value),
new SqlParameter("createEnd", DBNull.Value),
new SqlParameter("keyword", DBNull.Value)
};
if (!string.IsNullOrEmpty(employeeid))
{
sqlp[1].Value = employeeid;
}
if (!String.IsNullOrEmpty(applicant))
{
sqlp[2].Value = applicant;
}
if (!String.IsNullOrEmpty(approvalStartDate))
{
sqlp[3].Value = approvalStartDate;
}
if (!String.IsNullOrEmpty(approvalEndDate))
{
sqlp[4].Value = approvalEndDate;
}
if (!String.IsNullOrEmpty(status))
{
sqlp[5].Value = status;
}
if (!String.IsNullOrEmpty(refid))
{
sqlp[6].Value = refid;
}
if (!String.IsNullOrEmpty(createStartDate))
{
sqlp[7].Value = createStartDate;
}
if (!String.IsNullOrEmpty(createEndDate))
{
sqlp[8].Value = createEndDate;
}
if (!String.IsNullOrEmpty(keyword))
{
sqlp[9].Value = keyword;
}
rawHistory = Db.Database.SqlQuery <ApproverHistory>("exec [K2_ApproverHistory] " + queryParameter, sqlp.ToArray()).ToList();
foreach (var request in rawHistory.DistinctBy(p => p.ProcInstID))
{
ChkFrmStatus tmpStatus = new ChkFrmStatus();
tmpStatus.FormID = request.FormID;
tmpStatus.ProcInstID = request.ProcInstID.ToString();
tmpStatus.ReferenceID = request.ReferenceID;
tmpStatus.FormStatus = request.FormStatus;
tmpStatus.SubmittedOn = request.CommentedOn;
tmpStatus.ApplicantEMP = request.ApplicantEmployeeID;
tmpStatus.ApplicantFNAME = request.ApplicantFullName;
tmpStatus.ApproverEmp = request.ApproverEmployeeID;
tmpStatus.ApproverFNAME = request.ApproverFullName;
tmpStatus.DisplayStatus = request.DisplayStatus;
tmpStatus.LastUser = request.LastUser;
tmpStatus.PreparerFNAME = request.PreparerFullName;
tmpStatus.ActionTakerFullName = request.ActionTakerFullName;
tmpStatus.ITSApproverFullName = request.ITSApproverFullName;
tmpStatus.ApplicantUserId = request.ApplicantUserId;
List <ServiceLevel1> Level1lst = new List <ServiceLevel1>();
foreach (var FirstLevelService in rawHistory.Where(P => P.ProcessLogID == request.ProcessLogID && P.ProcInstID == request.ProcInstID).Select(P => new { P.MMenu, P.MMenuGUID }).Distinct())
{
ServiceLevel1 Level1 = new ServiceLevel1();
Level1.Name = FirstLevelService.MMenu;
Level1.GUID = FirstLevelService.MMenuGUID;
List <ServiceLevel2> Level2lst = new List <ServiceLevel2>();
foreach (var SecondLevelService in rawHistory.Where(P => P.ProcessLogID == request.ProcessLogID && P.ProcInstID == request.ProcInstID && P.MMenu == FirstLevelService.MMenu).DistinctBy(P => P.SubMenu))
{
ServiceLevel2 Level2 = new ServiceLevel2();
Level2.Name = SecondLevelService.SubMenu;
Level2.GUID = SecondLevelService.SubMenuGUID;
Level2.SValue = SecondLevelService.ServiceTypeValue;
List <ServiceLevel3> Level3lst = new List <ServiceLevel3>();
foreach (var ThirsLevelService in rawHistory.Where(P => P.ProcessLogID == request.ProcessLogID && P.ProcInstID == request.ProcInstID && P.MMenu == FirstLevelService.MMenu && P.SubMenu == SecondLevelService.SubMenu).OrderBy(P => P.ServiceGUID))
{
ServiceLevel3 Level3 = new ServiceLevel3();
Level3.Name = ThirsLevelService.SSubMenu;
Level3.GUID = ThirsLevelService.SSubMenuGUID;
Level3.SValue = ThirsLevelService.ServiceTypeValue;
Level3.ControlFlag = ThirsLevelService.ControlFlag;
Level3lst.Add(Level3);
}
if (Level3lst.Count > 0)
{
Level2.Level3 = Level3lst;
//Level2.GUID = Guid.Empty; // To return Null Value.
Level2.SValue = null;
}
else
{
Level2.Level3 = null;
}
Level2lst.Add(Level2);
}
if (Level2lst.Count > 0)
{
Level1.Level2 = Level2lst;
}
else
{
Level1.Level2 = null;
}
Level1lst.Add(Level1);
}
if (Level1lst.Count > 0)
{
tmpStatus.RequestList = Level1lst;
}
else
{
tmpStatus.RequestList = null;
}
historyList.Add(tmpStatus);
}
return(historyList);
} catch (Exception ex)
{
throw ex;
}
}