LogisticsSystem/Business/FileFlow/Vo/FlowListVo.cs

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using Business.FileFlow.Entity;
using Common;

namespace Business.FileFlow.Vo
{
    public class FlowListVo
    {
        /// <summary>
        /// 参数赋值
        /// </summary>
        /// <param name="flowList">实体类FlowListEty</param>
        /// <returns>参数数组</returns>
        private SqlParameter[] SetPara(FlowListEty flowList)
        {
            return new[]
                {
                    new SqlParameter("@id", flowList.Id),
                    new SqlParameter("@name", flowList.Name),
                    new SqlParameter("@remark", flowList.Remark),
                    new SqlParameter("@flowid", flowList.FlowId),
                    new SqlParameter("@actionid", flowList.ActionId),
                    new SqlParameter("@delflag", flowList.DelFlag),
                    new SqlParameter("@addUserID", flowList.AddUserId),
                    new SqlParameter("@addtime", flowList.AddTime),
                    new SqlParameter("@updateUserID", flowList.UpdateUserId),
                    new SqlParameter("@lastTime", flowList.LastTime),
                    new SqlParameter("@fcontent", flowList.Fcontent),
                    new SqlParameter("@sAddr", flowList.SAddr),
                    new SqlParameter("@isEnd", flowList.IsEnd),
                    new SqlParameter("@isPass", flowList.IsPass)
                };
        }

        /// <summary>
        /// 新增flowList记录
        /// </summary>
        /// <param name="flowList">实体类FlowListEty</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public string InsertFlowList(FlowListEty flowList)
        {
            var sql =
                "insert into flowList(id,name,remark,flowid,actionid,delflag,addUserID,addtime,updateUserID,lastTime,fcontent,sAddr,isEnd,isPass) values(@id,@name,@remark,@flowid,@actionid,@delflag,@addUserID,@addtime,@updateUserID,@lastTime,@fcontent,@sAddr,@isEnd,@isPass)";
            return SqlHelper.ExecSql(sql, SetPara(flowList));
        }

        /// <summary>
        /// 根据用户ID 获取我的公文列表信息
        /// </summary>
        /// <param name="searchText">查询字段</param>
        /// <param name="userid">用户ID</param>
        /// <returns>数据表</returns>
        public DataTable AllFlowList(string searchText,string userid)
        {
            var sql =
                "select p.id,p.name,f.name as flowname,f.actionIDs,p.actionid, p.lastTime,s.username as addusername,p.isPass,p.isEnd,p.sAddr from flowList p left join SysUser s on s.userid=p.addUserID left join flow f on f.id=p.flowid where p.addUserID=@addUserID and p.delflag ='false' ";

            if (!string.IsNullOrEmpty(searchText))
            {
                //根据查询字段 进行查询
                sql +=
                    string.Format(
                        "and( p.name like '%{0}%' or s.username like '%{0}%' or a.name like '%{0}%' or f.name like '%{0}%')",
                        searchText);
            }
            sql += " order by p.isEnd, p.lastTime DESC";

            return SqlHelper.ExecSqlDateTable(sql, new SqlParameter("@addUserID",userid));
        }

        /// <summary>
        /// 更新公文列表信息
        /// </summary>
        /// <param name="flowList">实体类FlowListEty</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public string UpdateFlowList(FlowListEty flowList)
        {
            var sq1 =
                "update flowList set actionid=@actionid,lastTime=@lastTime,remark=@remark,updateUserID=@updateUserID,isEnd=@isEnd,isPass=@isPass where id=@id";
            return SqlHelper.ExecSql(sq1, SetPara(flowList));
        }

        /// <summary>
        /// 根据id删除公文列表信息
        /// </summary>
        /// <param name="id">公文列表id</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public string DelFlowList(string id)
        {
            var sql = "update flowList set delflag='true' where id in (" + id + ")";
            return SqlHelper.ExecSql(sql);
        }

        /// <summary>
        /// 根据id返回FlowListEty实体类
        /// </summary>
        /// <param name="id">flowid</param>
        public FlowListEty GetFlowContById(string id)
        {
            var con = new SqlConnection(SqlHelper.ConStr);
            var cmd = new SqlCommand { Connection = con, CommandType = CommandType.Text, CommandText = "select * from  flowList where id=@id and delflag='false'" };
            cmd.Parameters.AddWithValue("@id", id);
            var flow = new FlowListEty();
            try
            {
                con.Open();
            }
            catch
            {
                return flow;
            }
            using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                if (!reader.HasRows)
                    return flow;
                while (reader.Read())
                {
                    flow.Id = SqlHelper.ReaderString("id", reader);
                    flow.Num = SqlHelper.ReaderInt("num", reader);
                    flow.Name = SqlHelper.ReaderString("name", reader);
                    flow.Remark = SqlHelper.ReaderString("remark", reader);
                    flow.FlowId = SqlHelper.ReaderString("flowid", reader);
                    flow.ActionId = SqlHelper.ReaderInt("actionid", reader);
                    flow.DelFlag = SqlHelper.ReaderBool("delflag", reader);
                    flow.AddUserId = SqlHelper.ReaderString("addUserID", reader);
                    flow.AddTime = SqlHelper.ReaderDateTime("addtime", reader);
                    flow.UpdateUserId = SqlHelper.ReaderString("updateUserID", reader);
                    flow.LastTime = SqlHelper.ReaderDateTime("lastTime", reader);
                    flow.IsEnd = SqlHelper.ReaderBool("isEnd", reader);
                    flow.IsPass = SqlHelper.ReaderBool("isPass", reader);
                    flow.Fcontent = SqlHelper.ReaderString("fcontent", reader);
                    flow.SAddr = SqlHelper.ReaderString("sAddr", reader);
                }
            }
            return flow;
        }


        /// <summary>
        /// 根据用户id返回FlowListEty实体类链表(我的审批列表)
        /// </summary>
        /// <param name="userid">userid</param>
        /// <param name="sT">查询字段</param>
        public Hashtable GetFlowListByUserid(string userid,string sT)
        {
            var con = new SqlConnection(SqlHelper.ConStr);
            var cmd = new SqlCommand { Connection = con, CommandType = CommandType.StoredProcedure, CommandText = "myflowList" };
            SqlParameter para = cmd.Parameters.Add("@user", SqlDbType.VarChar,32);
            para.Direction = ParameterDirection.Input;
            para.Value = userid;
            SqlParameter para2 = cmd.Parameters.Add("@sT", SqlDbType.VarChar, 100);
            para2.Direction = ParameterDirection.Input;
            para2.Value = sT;
            var list = new Hashtable();
            var adp=new SqlDataAdapter(cmd);
            var ds = new DataSet();
            adp.Fill(ds);
            foreach (DataTable table in ds.Tables)
            {
                foreach (DataRow row in table.Rows)
                {
                   if(list.ContainsKey(row["id"].ToString()))continue;
                   var flow = new FlowListEty
                        {
                            Id =row["id"].ToString(),
                            Num = Convert.ToInt32(row["num"]),
                            Name =row["name"].ToString() ,
                            Remark =row["remark"].ToString() ,
                            FlowId =row["flowid"].ToString() ,
                            ActionId = Convert.ToInt32(row["actionid"]),
                            DelFlag =(bool)row["delflag"] ,
                            AddUserId = row["addUserID"].ToString(),
                            AddTime = Convert.ToDateTime(row["addtime"]),
                            UpdateUserId =row["updateUserID"].ToString() ,
                            LastTime = Convert.ToDateTime(row["lastTime"]),
                            IsEnd =(bool)row["isEnd"] ,
                            IsPass =(bool) row["isPass"],
                            Fcontent = row["fcontent"].ToString(),
                            SAddr =row["sAddr"].ToString(),
                        };
                    list[flow.Id] = flow;
                }
            }
            return list;
        }

        /// <summary>
        /// 获取我的审批 集合
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <param name="sT">查询字段</param>
        /// <returns>我的审批 集合</returns>
        public Hashtable MyList(string userid,string sT)
        {
            var flowVo = new FlowVo();
            string myActions = new ActionVo().MyActions(userid);        //获取由我审批的操作集合
            if (myActions.Equals(string.Empty))
            {
                return new Hashtable();
            }

            DataTable actions = new ActionVo().AllAction("");       //读取所有流程操作
            Hashtable list = GetFlowListByUserid(userid,sT);           //获取所有有关我的公文列表
            string ids = string.Empty;
            //过滤 到我审批的公文
            foreach (FlowListEty flowList in list.Values)
            {
                string actionIDs = flowVo.GetAction(flowList.FlowId);
                ActionEty actionEty = flowVo.NowActionId(actionIDs, actions, flowList.ActionId.ToString());
                if (!myActions.Contains(actionEty.Id))
                {
                    ids += flowList.Id + ",";
                   
                }
            }
            foreach (string key in ids.TrimEnd(',').Split(','))
            {
                list.Remove(key);
            }
            return list;        //我的审批公文
        }
    }
}