Esempio n. 1
0
        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);
            }
        }