public WFInfo GetActivities(Ctx ctx, int AD_User_ID, int AD_Client_ID, int pageNo, int pageSize, bool refresh)
{
string sql = @"SELECT * FROM (SELECT a.*,rownum as abcx
FROM AD_WF_Activity a
WHERE a.Processed ='N'
AND a.WFState ='OS'
AND a.AD_Client_ID =@clientid
AND ( (a.AD_User_ID=@userid
OR a.AD_User_ID IN
(SELECT AD_User_ID
FROM AD_User_Substitute
WHERE IsActive ='Y'
AND Substitute_ID=@userid
AND (validfrom <=sysdate)
AND (sysdate <=validto )
))
OR EXISTS
(SELECT *
FROM AD_WF_Responsible r
WHERE a.AD_WF_Responsible_ID=r.AD_WF_Responsible_ID
AND COALESCE(r.AD_User_ID,0)=0
AND (a.AD_User_ID =@userid
OR a.AD_User_ID IS NULL
OR a.AD_User_ID IN
(SELECT AD_User_ID
FROM AD_User_Substitute
WHERE IsActive ='Y'
AND Substitute_ID=@userid
AND (validfrom <=sysdate)
AND (sysdate <=validto )
))
)
OR EXISTS
(SELECT *
FROM AD_WF_Responsible r
WHERE a.AD_WF_Responsible_ID=r.AD_WF_Responsible_ID
AND (r.AD_User_ID =@userid
OR a.AD_User_ID IN
(SELECT AD_User_ID
FROM AD_User_Substitute
WHERE IsActive ='Y'
AND Substitute_ID=@userid
AND (validfrom <=sysdate)
AND (sysdate <=validto )
))
)
OR EXISTS
(SELECT *
FROM AD_WF_Responsible r
INNER JOIN AD_User_Roles ur
ON (r.AD_Role_ID =ur.AD_Role_ID)
WHERE a.AD_WF_Responsible_ID=r.AD_WF_Responsible_ID
AND (ur.AD_User_ID =@userid
OR a.AD_User_ID IN
(SELECT AD_User_ID
FROM AD_User_Substitute
WHERE IsActive ='Y'
AND Substitute_ID=@userid
AND (validfrom <=sysdate)
AND (sysdate <=validto )
))
AND r.responsibletype !='H'
) )
ORDER BY a.Priority DESC,Created ) WHERE abcx BETWEEN " + (((pageNo - 1) * pageSize) + 1) + " AND " + (((pageNo - 1) * pageSize) + pageSize);
//temp ORDER BY Created desc,a.Priority DESC
//final ORDER BY a.Priority DESC,Created
//int AD_User_ID = Envs.GetContext().GetAD_User_ID();
try
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@clientid", AD_Client_ID);
param[1] = new SqlParameter("@userid", AD_User_ID);
DataSet ds = DB.ExecuteDataset(sql, param);
if (ds == null || ds.Tables[0].Rows.Count == 0)
{
return(null);
}
List <WFActivityInfo> lstInfo = new List <WFActivityInfo>();
WFActivityInfo itm = null;
foreach (DataRow dr in ds.Tables[0].Rows)
{
itm = new WFActivityInfo();
itm.AD_Table_ID = Util.GetValueOfInt(dr["AD_Table_ID"]);
itm.AD_User_ID = Util.GetValueOfInt(dr["AD_User_ID"]);
itm.AD_WF_Activity_ID = Util.GetValueOfInt(dr["AD_WF_Activity_ID"]);
itm.AD_Node_ID = Util.GetValueOfInt(dr["AD_WF_Node_ID"]);
itm.AD_WF_Process_ID = Util.GetValueOfInt(dr["AD_WF_Process_ID"]);
itm.AD_WF_Responsible_ID = Util.GetValueOfInt(dr["AD_WF_Responsible_ID"]);
itm.AD_Workflow_ID = Util.GetValueOfInt(dr["AD_Workflow_ID"]);
itm.CreatedBy = Util.GetValueOfInt(dr["CreatedBy"]);
itm.DynPriorityStart = Util.GetValueOfInt(dr["DynPriorityStart"]);
itm.Record_ID = Util.GetValueOfInt(dr["Record_ID"]);
itm.TxtMsg = Util.GetValueOfString(dr["TextMsg"]);
itm.WfState = Util.GetValueOfString(dr["WfState"]);
itm.EndWaitTime = Util.GetValueOfDateTime(dr["EndWaitTime"]);
itm.Created = Util.GetValueOfString(dr["Created"]);
MWFActivity act = new MWFActivity(ctx, itm.AD_WF_Activity_ID, null);
itm.NodeName = act.GetNodeName();
itm.Summary = act.GetSummary();
itm.Description = act.GetNodeDescription();
itm.Help = act.GetNodeHelp();
itm.History = act.GetHistoryHTML();
itm.Priority = Util.GetValueOfInt(dr["Priority"]);
lstInfo.Add(itm);
}
WFInfo info = new WFInfo();
info.LstInfo = lstInfo;
//return lstInfo;
if (refresh)
{
sql = @"SELECT COUNT(*)
FROM AD_WF_Activity a
WHERE a.Processed ='N'
AND a.WFState ='OS'
AND a.AD_Client_ID =" + ctx.GetAD_Client_ID() + @"
AND ( (a.AD_User_ID=" + ctx.GetAD_User_ID() + @"
OR a.AD_User_ID IN
(SELECT AD_User_ID
FROM AD_User_Substitute
WHERE IsActive ='Y'
AND Substitute_ID=" + ctx.GetAD_User_ID() + @"
AND (validfrom <=sysdate)
AND (sysdate <=validto )
))
OR EXISTS
(SELECT *
FROM AD_WF_Responsible r
WHERE a.AD_WF_Responsible_ID=r.AD_WF_Responsible_ID
AND COALESCE(r.AD_User_ID,0)=0
AND (a.AD_User_ID =" + ctx.GetAD_User_ID() + @"
OR a.AD_User_ID IS NULL
OR a.AD_User_ID IN
(SELECT AD_User_ID
FROM AD_User_Substitute
WHERE IsActive ='Y'
AND Substitute_ID=" + ctx.GetAD_User_ID() + @"
AND (validfrom <=sysdate)
AND (sysdate <=validto )
))
)
OR EXISTS
(SELECT *
FROM AD_WF_Responsible r
WHERE a.AD_WF_Responsible_ID=r.AD_WF_Responsible_ID
AND (r.AD_User_ID =" + ctx.GetAD_User_ID() + @"
OR a.AD_User_ID IN
(SELECT AD_User_ID
FROM AD_User_Substitute
WHERE IsActive ='Y'
AND Substitute_ID=" + ctx.GetAD_User_ID() + @"
AND (validfrom <=sysdate)
AND (sysdate <=validto )
))
)
OR EXISTS
(SELECT *
FROM AD_WF_Responsible r
INNER JOIN AD_User_Roles ur
ON (r.AD_Role_ID =ur.AD_Role_ID)
WHERE a.AD_WF_Responsible_ID=r.AD_WF_Responsible_ID
AND (ur.AD_User_ID =" + ctx.GetAD_User_ID() + @"
OR a.AD_User_ID IN
(SELECT AD_User_ID
FROM AD_User_Substitute
WHERE IsActive ='Y'
AND Substitute_ID=" + ctx.GetAD_User_ID() + @"
AND (validfrom <=sysdate)
AND (sysdate <=validto )
))
AND r.responsibletype !='H'
) )
";
info.count = Util.GetValueOfInt(DB.ExecuteScalar(sql));
}
return(info);
}
catch
{
return(null);
}
}