using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using MySql.Data.MySqlClient;

namespace RoadFlow.Data.ORACLE
{
    public class WorkFlowTask : RoadFlow.Data.Interface.IWorkFlowTask
    {
        private DBHelper dbHelper = new DBHelper();
        /// <summary>0
        /// 构造函数
        /// </summary>
        public WorkFlowTask()
        {
        }
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="model">RoadFlow.Data.Model.WorkFlowTask实体类</param>
        /// <returns>操作所影响的行数</returns>
        public int Add2(RoadFlow.Data.Model.WorkFlowTask model)
        {
            string sql = @"INSERT INTO WorkFlowTask
				(ID,PrevID,PrevStepID,FlowID,StepID,StepName,InstanceID,GroupID,Type,Title,SenderID,SenderName,SenderTime,ReceiveID,ReceiveName,ReceiveTime,OpenTime,CompletedTime,CompletedTime1,Comment2,IsSign,Status,Note,Sort,SubFlowGroupID) 
				VALUES(@ID,@PrevID,@PrevStepID,@FlowID,@StepID,@StepName,@InstanceID,@GroupID,@Type,@Title,@SenderID,@SenderName,@SenderTime,@ReceiveID,@ReceiveName,@ReceiveTime,@OpenTime,@CompletedTime,@CompletedTime1,@Comment2,@IsSign,@Status,@Note,@Sort,@SubFlowGroupID)";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", MySqlDbType.VarChar, 50){ Value = model.ID },
                new MySqlParameter("@PrevID", MySqlDbType.VarChar, 50){ Value = model.PrevID },
                new MySqlParameter("@PrevStepID", MySqlDbType.VarChar, 50){ Value = model.PrevStepID },
                new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50){ Value = model.FlowID },
                new MySqlParameter("@StepID", MySqlDbType.VarChar, 50){ Value = model.StepID },
                new MySqlParameter("@StepName", MySqlDbType.VarChar, 1000){ Value = model.StepName },
                new MySqlParameter("@InstanceID", MySqlDbType.VarChar, 50){ Value = model.InstanceID },
                new MySqlParameter("@GroupID", MySqlDbType.VarChar, 50){ Value = model.GroupID },
                new MySqlParameter("@Type",   MySqlDbType.Int32){ Value = model.Type },
                new MySqlParameter("@Title", MySqlDbType.VarChar, 4000){ Value = model.Title },
                new MySqlParameter("@SenderID", MySqlDbType.VarChar, 50){ Value = model.SenderID },
                new MySqlParameter("@SenderName", MySqlDbType.VarChar, 100){ Value = model.SenderName },
                new MySqlParameter("@SenderTime",model.SenderTime),
                new MySqlParameter("@ReceiveID", MySqlDbType.VarChar, 50){ Value = model.ReceiveID },
                new MySqlParameter("@ReceiveName", MySqlDbType.VarChar, 100){ Value = model.ReceiveName },
                new MySqlParameter("@ReceiveTime",model.ReceiveTime),
                model.OpenTime == null ? new MySqlParameter("@OpenTime",DBNull.Value) : new MySqlParameter("@OpenTime",model.OpenTime),
                model.CompletedTime == null ? new MySqlParameter("@CompletedTime",DBNull.Value) : new MySqlParameter("@CompletedTime",model.CompletedTime ),
                model.CompletedTime1 == null ? new MySqlParameter("@CompletedTime1",DBNull.Value) : new MySqlParameter("@CompletedTime1",model.CompletedTime1),
                model.Comment2 == null ? new MySqlParameter("@Comment2", DBNull.Value) : new MySqlParameter("@Comment2", model.Comment2),
                model.IsSign == null ? new MySqlParameter("@IsSign",DBNull.Value ) : new MySqlParameter("@IsSign",model.IsSign ),
                new MySqlParameter("@Status",   MySqlDbType.Int32){ Value = model.Status },
                model.Note == null ? new MySqlParameter("@Note",DBNull.Value) : new MySqlParameter("@Note", model.Note),
                new MySqlParameter("@Sort",   MySqlDbType.Int32){ Value = model.Sort },
                model.SubFlowGroupID == null ? new MySqlParameter("@SubFlowGroupID", MySqlDbType.VarChar, 50) { Value = DBNull.Value } : new MySqlParameter("@SubFlowGroupID", MySqlDbType.VarChar, 50) { Value = model.SubFlowGroupID }
            };
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="model">RoadFlow.Data.Model.WorkFlowTask实体类</param>
        /// <returns>操作所影响的行数</returns>
        public int Add(RoadFlow.Data.Model.WorkFlowTask model)
        {
            string sql = "INSERT INTO WorkFlowTask " +
                "(ID,PrevID,PrevStepID,FlowID,StepID,StepName,InstanceID,GroupID,Type,Title,SenderID,SenderName,SenderTime,ReceiveID,ReceiveName,ReceiveTime,OpenTime,CompletedTime,CompletedTime1,Comment2,IsSign,Status,Note,Sort,SubFlowGroupID) " +

                "VALUES(@ID,@PrevID,@PrevStepID,@FlowID,@StepID,@StepName,@InstanceID,@GroupID,@Type,@Title,@SenderID,@SenderName,@SenderTime,@ReceiveID,@ReceiveName,@ReceiveTime,@OpenTime,@CompletedTime,@CompletedTime1,@Comment2,@IsSign,@Status,@Note,@Sort,@SubFlowGroupID)";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", model.ID.ToString()){ Value = model.ID.ToString() },
                new MySqlParameter("@PrevID", model.PrevID.ToString()),
                new MySqlParameter("@PrevStepID", model.PrevStepID.ToString()),
                new MySqlParameter("@FlowID", model.FlowID.ToString()),
                new MySqlParameter("@StepID", model.StepID.ToString()),
                new MySqlParameter("@StepName", model.StepName),
                new MySqlParameter("@InstanceID", model.InstanceID),
                new MySqlParameter("@GroupID", model.GroupID.ToString()),
                new MySqlParameter("@Type",   model.Type),
                new MySqlParameter("@Title", model.Title),
                new MySqlParameter("@SenderID", model.SenderID),
                new MySqlParameter("@SenderName", model.SenderName),
                new MySqlParameter("@SenderTime",model.SenderTime),
                new MySqlParameter("@ReceiveID", model.ReceiveID),
                new MySqlParameter("@ReceiveName", model.ReceiveName),
                new MySqlParameter("@ReceiveTime",model.ReceiveTime),
                new MySqlParameter("@OpenTime",model.OpenTime) ,
                new MySqlParameter("@CompletedTime",model.CompletedTime) ,
                new MySqlParameter("@CompletedTime1",model.CompletedTime1) ,
                new MySqlParameter("@Comment2", model.Comment2) ,
                new MySqlParameter("@IsSign",   model.IsSign) ,
                new MySqlParameter("@Status",   model.Status),
                new MySqlParameter("@Note", model.Note) ,
                new MySqlParameter("@Sort",   model.Sort),
                new MySqlParameter("@SubFlowGroupID", model.SubFlowGroupID.HasValue?model.SubFlowGroupID.ToString():null)
            };
            return dbHelper.Execute(sql, parameters);

        }
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">RoadFlow.Data.Model.WorkFlowTask实体类</param>
        public int Update(RoadFlow.Data.Model.WorkFlowTask model)
        {
            string sql = @"UPDATE WorkFlowTask SET 
				PrevID=@PrevID,PrevStepID=@PrevStepID,FlowID=@FlowID,StepID=@StepID,StepName=@StepName,InstanceID=@InstanceID,GroupID=@GroupID,Type=@Type,Title=@Title,SenderID=@SenderID,SenderName=@SenderName,SenderTime=@SenderTime,ReceiveID=@ReceiveID,ReceiveName=@ReceiveName,ReceiveTime=@ReceiveTime,OpenTime=@OpenTime,CompletedTime=@CompletedTime,CompletedTime1=@CompletedTime1,Comment2=@Comment2,IsSign=@IsSign,Status=@Status,Note=@Note,Sort=@Sort,SubFlowGroupID=@SubFlowGroupID
				WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@PrevID", model.PrevID.ToString() ),
                new MySqlParameter("@PrevStepID", model.PrevStepID.ToString()),
                new MySqlParameter("@FlowID", model.FlowID.ToString()),
                new MySqlParameter("@StepID", model.StepID.ToString()),
                new MySqlParameter("@StepName", model.StepName),
                new MySqlParameter("@InstanceID", model.InstanceID),
                new MySqlParameter("@GroupID", model.GroupID.ToString()),
                new MySqlParameter("@Type",   model.Type),
                new MySqlParameter("@Title", model.Title),
                new MySqlParameter("@SenderID", model.SenderID),
                new MySqlParameter("@SenderName", model.SenderName),
                new MySqlParameter("@SenderTime",model.SenderTime),
                new MySqlParameter("@ReceiveID", model.ReceiveID),
                new MySqlParameter("@ReceiveName", model.ReceiveName),
                new MySqlParameter("@ReceiveTime",model.ReceiveTime),
                new MySqlParameter("@Status",   model.Status),
                new MySqlParameter("@Sort",   model.Sort),
                new MySqlParameter("@ID", model.ID.ToString()),
                model.Note == null ? new MySqlParameter("@Note", DBNull.Value): new MySqlParameter("@Note", model.Note),
                model.IsSign == null ? new MySqlParameter("@IsSign",   DBNull.Value) : new MySqlParameter("@IsSign",   model.IsSign) ,
                model.OpenTime == null ? new MySqlParameter("@OpenTime",DBNull.Value) : new MySqlParameter("@OpenTime",model.OpenTime),
                model.Comment2 == null ? new MySqlParameter("@Comment2", DBNull.Value) : new MySqlParameter("@Comment2", model.Comment2),
                model.CompletedTime == null ? new MySqlParameter("@CompletedTime", DBNull.Value): new MySqlParameter("@CompletedTime",model.CompletedTime) ,
                model.CompletedTime1 == null ? new MySqlParameter("@CompletedTime1",DBNull.Value): new MySqlParameter("@CompletedTime1",model.CompletedTime1) ,
                model.SubFlowGroupID == null ? new MySqlParameter("@SubFlowGroupID", DBNull.Value) : new MySqlParameter("@SubFlowGroupID", model.SubFlowGroupID),
            };
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        public int Delete(Guid id)
        {
            string sql = "DELETE FROM WorkFlowTask WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", MySqlDbType.VarChar, 50){ Value = id }
            };
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 将DataRedar转换为List
        /// </summary>
        private List<RoadFlow.Data.Model.WorkFlowTask> DataReaderToList(DataTable dataReader)
        {
            List<RoadFlow.Data.Model.WorkFlowTask> List = new List<RoadFlow.Data.Model.WorkFlowTask>();
            RoadFlow.Data.Model.WorkFlowTask model = null;
            //while (dataReader.Read())
            for (int i = 0; i < dataReader.Rows.Count; i++)
            {
                model = new RoadFlow.Data.Model.WorkFlowTask();
                model.ID = new Guid(dataReader.Rows[i][0] + "");
                model.PrevID = new Guid(dataReader.Rows[i][1] + "");
                model.PrevStepID = new Guid(dataReader.Rows[i][2] + "");
                model.FlowID = new Guid(dataReader.Rows[i][3] + "");
                model.StepID = new Guid(dataReader.Rows[i][4] + "");
                model.StepName = dataReader.Rows[i][5] + "";
                model.InstanceID = dataReader.Rows[i][6] + "";
                model.GroupID = new Guid(dataReader.Rows[i][7] + "");
                int.TryParse(dataReader.Rows[i][8] + "", out int n8);
                model.Type = n8;
                model.Title = dataReader.Rows[i][9] + "";
                model.SenderID = dataReader.Rows[i][10] + "";
                model.SenderName = dataReader.Rows[i][11] + "";
                DateTime.TryParse(dataReader.Rows[i][12] + "", out DateTime dtm12);
                model.SenderTime = dtm12;
                model.ReceiveID = dataReader.Rows[i][13] + "";
                model.ReceiveName = dataReader.Rows[i][14] + "";
                DateTime.TryParse(dataReader.Rows[i][15] + "", out DateTime dtm15);
                model.ReceiveTime = dtm15;
                DateTime.TryParse(dataReader.Rows[i][16] + "", out DateTime dtm16);
                model.OpenTime = dtm16;
                DateTime.TryParse(dataReader.Rows[i][17] + "", out DateTime dtm17);
                model.CompletedTime = dtm17;
                DateTime.TryParse(dataReader.Rows[i][18] + "", out DateTime dtm18);
                model.CompletedTime1 = dtm18;
                model.Comment2 = dataReader.Rows[i][19] + "";
                int.TryParse(dataReader.Rows[i][20] + "", out int n20);
                model.IsSign = n20;
                int.TryParse(dataReader.Rows[i][21] + "", out int n21);
                model.Status = n21;
                model.Note = dataReader.Rows[i][22] + "";
                int.TryParse(dataReader.Rows[i][23] + "", out int n23);
                model.Sort = n23;
                Guid.TryParse(dataReader.Rows[i][24] + "", out Guid guid24);
                model.SubFlowGroupID = guid24;
                model.EventFlag = dataReader.Rows[i][26] + "";
                List.Add(model);
            }
            return List;
        }

        /// <summary>
        /// 将DataRedar转换为List（APP）
        /// </summary>
        private List<RoadFlow.Data.Model.WorkFlowTask> AppDataReaderToList(DataTable dataReader)
        {
            List<RoadFlow.Data.Model.WorkFlowTask> List = new List<RoadFlow.Data.Model.WorkFlowTask>();
            RoadFlow.Data.Model.WorkFlowTask model = null;
            for (int i = 0; i < dataReader.Rows.Count; i++)
            {
                model = new RoadFlow.Data.Model.WorkFlowTask();
                model.ID = new Guid(dataReader.Rows[i][0] + "");
                model.PrevID = new Guid(dataReader.Rows[i][1] + "");
                model.PrevStepID = new Guid(dataReader.Rows[i][2] + "");
                model.FlowID = new Guid(dataReader.Rows[i][3] + "");
                model.StepID = new Guid(dataReader.Rows[i][4] + "");
                model.StepName = dataReader.Rows[i][5] + "";
                model.InstanceID = dataReader.Rows[i][6] + "";
                Guid.TryParse(dataReader.Rows[i][7] + "", out Guid guid7);
                model.GroupID = guid7;
                int.TryParse(dataReader.Rows[i][8] + "", out int n8);
                model.Type = n8;
                model.Title = dataReader.Rows[i][9] + "";
                model.SenderID = dataReader.Rows[i][10] + "";
                model.SenderName = dataReader.Rows[i][11] + "";
                DateTime.TryParse(dataReader.Rows[i][12] + "", out DateTime dtm12);
                model.SenderTime = dtm12;
                model.ReceiveID = dataReader.Rows[i][13] + "";
                model.ReceiveName = dataReader.Rows[i][14] + "";
                DateTime.TryParse(dataReader.Rows[i][15] + "", out DateTime dtm15);
                model.ReceiveTime = dtm15;
                DateTime.TryParse(dataReader.Rows[i][16] + "", out DateTime dtm16);
                model.OpenTime = dtm16;
                DateTime.TryParse(dataReader.Rows[i][17] + "", out DateTime dtm17);
                model.CompletedTime = dtm17;
                DateTime.TryParse(dataReader.Rows[i][18] + "", out DateTime dtm18);
                model.CompletedTime1 = dtm18;
                model.Comment2 = dataReader.Rows[i][19] + "";
                int.TryParse(dataReader.Rows[i][20] + "", out int n20);
                model.IsSign = n20;
                int.TryParse(dataReader.Rows[i][21] + "", out int n21);
                model.Status = n21;
                model.Note = dataReader.Rows[i][22] + "";
                int.TryParse(dataReader.Rows[i][23] + "", out int n23);
                model.Sort = n23;
                Guid.TryParse(dataReader.Rows[i][24] + "", out Guid guid24);
                model.SubFlowGroupID = guid24;
                model.FlowName = dataReader.Rows[i][26] + "";
                model.StepTime = dataReader.Rows[i][27] + "";
                List.Add(model);
            }
            return List;
        }

        /// <summary>
        /// 查询所有记录
        /// </summary>
        public List<RoadFlow.Data.Model.WorkFlowTask> GetAll()
        {
            string sql = "SELECT * FROM WorkFlowTask";
            var dataReader = dbHelper.GetDataReader(sql);
            List<RoadFlow.Data.Model.WorkFlowTask> List = DataReaderToList(dataReader);
            return List;
        }
        /// <summary>
        /// 查询记录数
        /// </summary>
        public long GetCount()
        {
            string sql = "SELECT COUNT(*) FROM WorkFlowTask";
            long count;
            return long.TryParse(dbHelper.GetFieldValue(sql), out count) ? count : 0;
        }
        /// <summary>
        /// 根据主键查询一条记录
        /// </summary>
        public RoadFlow.Data.Model.WorkFlowTask Get(Guid id)
        {
            string sql = "SELECT * FROM WorkFlowTask WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", MySqlDbType.VarChar, 50){ Value = id }
            };
            var dataReader = dbHelper.GetDataReader(sql, parameters);
            List<RoadFlow.Data.Model.WorkFlowTask> List = DataReaderToList(dataReader);
            return List.Count > 0 ? List[0] : null;
        }

        /// <summary>
        /// 删除一组实例
        /// </summary>
        public int Delete(Guid flowID, Guid groupID)
        {
            string sql = "DELETE FROM WorkFlowTask WHERE GroupID=@GroupID";
            List<MySqlParameter> parameters = new List<MySqlParameter>(){
                new MySqlParameter("@GroupID", MySqlDbType.VarChar, 50){ Value = groupID }
            };
            if (!flowID.IsEmptyGuid())
            {
                sql += " AND FlowID=@FlowID";
                parameters.Add(new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50) { Value = flowID });
            }
            return dbHelper.Execute(sql, parameters.ToArray());
        }

        /// <summary>
        /// 更新打开时间
        /// </summary>
        /// <param name="id"></param>
        /// <param name="openTime"></param>
        /// <param name="isStatus">是否将状态更新为1</param>
        public void UpdateOpenTime(Guid id, DateTime openTime, bool isStatus = false)
        {
            string sql = "UPDATE WorkFlowTask SET OpenTime=@OpenTime " + (isStatus ? ", Status=1" : "") + " WHERE ID=@ID AND OpenTime IS NULL";

            MySqlParameter[] parameters = new MySqlParameter[]{
                openTime==DateTime.MinValue? new MySqlParameter("@OpenTime", DBNull.Value):new MySqlParameter("@OpenTime", openTime),
                new MySqlParameter("@ID",MySqlDbType.VarChar, 50){ Value=id }
            };
            dbHelper.Execute(sql, parameters);
        }


        /// <summary>
        /// 查询待办任务
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="pager"></param>
        /// <param name="query"></param>
        /// <param name="title"></param>
        /// <param name="flowid"></param>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <param name="type">0待办 1已完成</param>
        /// <returns></returns>
        public List<RoadFlow.Data.Model.WorkFlowTask> GetTasks(string userID, out string pager, string query = "", string title = "", string flowid = "", string sender = "", string date1 = "", string date2 = "", int type = 0)
        {
            List<MySqlParameter> parList = new List<MySqlParameter>();
            StringBuilder sql = new StringBuilder("SELECT t.* FROM WorkFlowTask t WHERE ReceiveID=@ReceiveID");
            sql.Append(type == 0 ? " AND Status IN(0,1)" : " AND Status IN(2,3)");
            parList.Add(new MySqlParameter("@ReceiveID", MySqlDbType.VarChar, 50) { Value = userID });
            if (!title.IsNullOrEmpty())
            {
                sql.Append(" AND instr(Title,@Title)>0");
                parList.Add(new MySqlParameter("@Title", MySqlDbType.VarChar, 2000) { Value = title });
            }
            if (flowid.IsGuid())
            {
                sql.Append(" AND FlowID=@FlowID");
                parList.Add(new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50) { Value = flowid.ToGuid() });
            }
            else if (!flowid.IsNullOrEmpty() && flowid.IndexOf(',') >= 0)
            {
                sql.Append(" AND FlowID IN(" + RoadFlow.Utility.Tools.GetSqlInString(flowid) + ")");
            }
            if (!sender.IsNullOrEmpty())
            {
                sql.Append(" AND SenderID=@SenderID");
                parList.Add(new MySqlParameter("@SenderID", MySqlDbType.VarChar, 50) { Value = sender.ToGuid() });
            }
            if (date1.IsDateTime())
            {
                sql.Append(" AND ReceiveTime>=@ReceiveTime");
                parList.Add(new MySqlParameter("@ReceiveTime", date1.ToDateTime().Date));
            }
            if (date2.IsDateTime())
            {
                sql.Append(" AND ReceiveTime<=@ReceiveTime1");
                parList.Add(new MySqlParameter("@ReceiveTime1", date2.ToDateTime().AddDays(1).Date));
            }

            long count;
            int size = RoadFlow.Utility.Tools.GetPageSize();
            int number = RoadFlow.Utility.Tools.GetPageNumber();
            string sql1 = dbHelper.GetPaerSql(sql.ToString(), size, number, out count, parList.ToArray());
            pager = RoadFlow.Utility.Tools.GetPagerHtml(count, size, number, query);


            var dataReader = dbHelper.GetDataReader(sql1, parList.ToArray());
            List<RoadFlow.Data.Model.WorkFlowTask> List = DataReaderToList(dataReader);
            return List;
        }

        /// <summary>
        /// 查询待办任务（APP）
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="pager"></param>
        /// <param name="query"></param>
        /// <param name="title"></param>
        /// <param name="flowid"></param>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <param name="type">0待办 1已完成</param>
        /// <returns></returns>
        public List<RoadFlow.Data.Model.WorkFlowTask> AppGetTasks(string userID, int PageIndex, int PageSize, string title = "", string flowid = "", string sender = "", string date1 = "", string date2 = "", int type = 0)
        {
            PageIndex = PageIndex < 1 ? 1 : PageIndex;
            int startnum = (PageIndex - 1) * PageSize;
            List<MySqlParameter> parList = new List<MySqlParameter>();
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT t.*,(select wf.NAME from WORKFLOW wf where wf.ID = t.FLOWID) flowName,");
            sql.Append("case t.FLOWID when '48b51077-4bc7-4807-bcc2-b33e8ef0f5b2' then (select ol.e_time from OA_LEAVE ol where ol.ID = t.instanceid) when 'c6533642-dc36-4728-aac7-81e6989f6105' then (select carapply.FINISHTIME from TBL_SYS_CARAPPLY carapply where carapply.ID = t.instanceid) when '37337d05-251a-459d-8c5e-b545b8593f9c' then (select ot.e_time from OA_TOLERANCE ot where ot.ID = t.instanceid) else '' end as stepTime ");
            sql.Append(" FROM WorkFlowTask t WHERE ReceiveID=@ReceiveID");
            sql.Append(type == 0 ? " AND Status IN(0,1)" : " AND Status IN(2,3)");
            parList.Add(new MySqlParameter("@ReceiveID", MySqlDbType.VarChar, 50) { Value = userID });
            if (!title.IsNullOrEmpty())
            {
                sql.Append(" AND instr(@Title,Title)>0");
                parList.Add(new MySqlParameter("@Title", MySqlDbType.VarChar, 2000) { Value = title });
            }
            if (flowid.IsGuid())
            {
                sql.Append(" AND FlowID=@FlowID");
                parList.Add(new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50) { Value = flowid.ToGuid() });
            }
            else if (!flowid.IsNullOrEmpty() && flowid.IndexOf(',') >= 0)
            {
                sql.Append(" AND FlowID IN(" + RoadFlow.Utility.Tools.GetSqlInString(flowid) + ")");
            }
            if (sender.IsGuid())
            {
                sql.Append(" AND SenderID=@SenderID");
                parList.Add(new MySqlParameter("@SenderID", MySqlDbType.VarChar, 50) { Value = sender.ToGuid() });
            }
            if (date1.IsDateTime())
            {
                sql.Append(" AND ReceiveTime>=@ReceiveTime");
                parList.Add(new MySqlParameter("@ReceiveTime", date1.ToDateTime().Date));
            }
            if (date2.IsDateTime())
            {
                sql.Append(" AND ReceiveTime<=@ReceiveTime1");
                parList.Add(new MySqlParameter("@ReceiveTime1", date2.ToDateTime().AddDays(1).Date));
            }

            sql.Append(" order by SENDERTIME DESC limit " + startnum + ", " + PageSize);


            var dataReader = dbHelper.GetDataReader(sql.ToString(), parList.ToArray());
            List<RoadFlow.Data.Model.WorkFlowTask> List = AppDataReaderToList(dataReader);
            return List;
        }

        /// <summary>
        /// 得到流程实例列表
        /// </summary>
        /// <param name="flowID"></param>
        /// <param name="senderID"></param>
        /// <param name="receiveID"></param>
        /// <param name="pager"></param>
        /// <param name="query"></param>
        /// <param name="title"></param>
        /// <param name="flowid"></param>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <param name="status">是否完成 0:全部 1:未完成 2:已完成</param>
        /// <returns></returns>
        public List<RoadFlow.Data.Model.WorkFlowTask> GetInstances(Guid[] flowID, Guid[] senderID, Guid[] receiveID, out string pager, string query = "", string title = "", string flowid = "", string date1 = "", string date2 = "", int status = 0)
        {
            List<MySqlParameter> parList = new List<MySqlParameter>();
            StringBuilder sql = new StringBuilder(@" SELECT a.* FROM WorkFlowTask a where 1=1 ");

            if (status != 0)
            {
                if (status == 1)
                {
                    sql.Append(" AND a.Status IN(0,1,5)");
                }
                else if (status == 2)
                {
                    sql.Append(" AND a.Status IN(2,3,4)");
                }
            }

            if (flowID != null && flowID.Length > 0)
            {
                sql.Append(string.Format(" AND a.FlowID IN({0})", RoadFlow.Utility.Tools.GetSqlInString(flowID)));
            }
            if (senderID != null && senderID.Length > 0)
            {
                if (senderID.Length == 1)
                {
                    sql.Append(" AND a.SenderID=@SenderID");
                    parList.Add(new MySqlParameter("@SenderID", MySqlDbType.VarChar, 50) { Value = senderID[0] });
                }
                else
                {
                    sql.Append(string.Format(" AND a.SenderID IN({0})", RoadFlow.Utility.Tools.GetSqlInString(senderID)));
                }
            }
            if (receiveID != null && receiveID.Length > 0)
            {
                if (senderID.Length == 1)
                {
                    sql.Append(" AND a.ReceiveID=@ReceiveID");
                    parList.Add(new MySqlParameter("@ReceiveID", MySqlDbType.VarChar, 50) { Value = receiveID[0] });
                }
                else
                {
                    sql.Append(string.Format(" AND a.ReceiveID IN({0})", RoadFlow.Utility.Tools.GetSqlInString(receiveID)));
                }
            }
            if (!title.IsNullOrEmpty())
            {
                sql.Append(" AND instr(@Title,a.Title)>0");
                parList.Add(new MySqlParameter("@Title", MySqlDbType.VarChar, 2000) { Value = title });
            }
            if (flowid.IsGuid())
            {
                sql.Append(" AND a.FlowID=@FlowID");
                parList.Add(new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50) { Value = flowid.ToGuid() });
            }
            else if (!flowid.IsNullOrEmpty() && flowid.IndexOf(',') >= 0)
            {
                sql.Append(" AND a.FlowID IN(" + RoadFlow.Utility.Tools.GetSqlInString(flowid) + ")");
            }
            if (date1.IsDateTime())
            {
                sql.Append(" AND a.SenderTime>=@SenderTime");
                parList.Add(new MySqlParameter("@SenderTime", date1.ToDateTime().Date));
            }
            if (date2.IsDateTime())
            {
                sql.Append(" AND a.SenderTime<=@SenderTime1");
                parList.Add(new MySqlParameter("@SenderTime1", date1.ToDateTime().AddDays(1).Date));
            }

            long count;
            int size = RoadFlow.Utility.Tools.GetPageSize();
            int number = RoadFlow.Utility.Tools.GetPageNumber();
            string sql1 = dbHelper.GetPaerSql(sql.ToString(), size, number, out count, parList.ToArray());
            pager = RoadFlow.Utility.Tools.GetPagerHtml(count, size, number, query);

            var dataReader = dbHelper.GetDataReader(sql1, parList.ToArray());
            List<RoadFlow.Data.Model.WorkFlowTask> List = DataReaderToList(dataReader);
            return List;
        }

        /// <summary>
        /// 得到一个流程实例的发起者
        /// </summary>
        /// <param name="flowID"></param>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public string GetFirstSnderID(Guid flowID, string groupID)
        {
            string sql = "SELECT SenderID FROM WorkFlowTask WHERE FlowID=@FlowID AND GroupID=@GroupID AND PrevID=@PrevID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50){ Value = flowID },
                new MySqlParameter("@GroupID", MySqlDbType.VarChar, 50){ Value = groupID },
                new MySqlParameter("@PrevID", MySqlDbType.VarChar, 50){ Value = Guid.Empty }
            };
            string senderID = dbHelper.GetFieldValue(sql, parameters);
            return senderID;
        }

        /// <summary>
        /// 得到一个流程实例一个步骤的处理者
        /// </summary>
        /// <param name="flowID"></param>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public List<Guid> GetStepSnderID(Guid flowID, Guid stepID, Guid groupID)
        {
            string sql = "SELECT ReceiveID FROM WorkFlowTask WHERE FlowID=@FlowID AND StepID=@StepID AND GroupID=@GroupID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50){ Value = flowID },
                new MySqlParameter("@StepID", MySqlDbType.VarChar, 50){ Value = stepID },
                new MySqlParameter("@GroupID", MySqlDbType.VarChar, 50){ Value = groupID }
            };
            DataTable dt = dbHelper.GetDataTable(sql, parameters);
            List<Guid> senderList = new List<Guid>();
            foreach (DataRow dr in dt.Rows)
            {
                Guid senderID;
                if (Guid.TryParse(dr[0].ToString(), out senderID))
                {
                    senderList.Add(senderID);
                }
            }
            return senderList;
        }
        /// <summary>
        /// 得到一个流程实例前一步骤的处理者
        /// </summary>
        /// <param name="flowID"></param>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public List<Guid> GetPrevSnderID(Guid flowID, Guid stepID, Guid groupID)
        {
            string sql = "SELECT ReceiveID FROM WorkFlowTask WHERE ID=(SELECT PrevID FROM WorkFlowTask WHERE FlowID=@FlowID AND StepID=@StepID AND GroupID=@GroupID)";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50){ Value = flowID },
                new MySqlParameter("@StepID", MySqlDbType.VarChar, 50){ Value = stepID },
                new MySqlParameter("@GroupID", MySqlDbType.VarChar, 50){ Value = groupID }
            };
            DataTable dt = dbHelper.GetDataTable(sql, parameters);
            List<Guid> senderList = new List<Guid>();
            foreach (DataRow dr in dt.Rows)
            {
                Guid senderID;
                if (Guid.TryParse(dr[0].ToString(), out senderID))
                {
                    senderList.Add(senderID);
                }
            }
            return senderList;
        }

        /// <summary>
        /// 完成一个任务
        /// </summary>
        /// <param name="taskID"></param>
        /// <param name="comment"></param>
        /// <param name="isSign"></param>
        /// <returns></returns>
        public int Completed(Guid taskID, string comment = "", bool isSign = false, int status = 2, string note = "")
        {
            string sql = "UPDATE WorkFlowTask SET Comment2=@Comment2,CompletedTime1=@CompletedTime1,IsSign=@IsSign,Status=@Status,Note=@Note WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID",taskID.ToString()),
                new MySqlParameter("@Note",note),
                new MySqlParameter("@Status", status),
                new MySqlParameter("@Comment2",comment),
                new MySqlParameter("@IsSign",  isSign?1:0 ),
                new MySqlParameter("@CompletedTime1", RoadFlow.Utility.DateTimeNew.Now ),
            };
            return dbHelper.Execute(sql, parameters);
        }

        /// <summary>
        /// 更新一个任务后后续任务状态
        /// </summary>
        /// <param name="taskID"></param>
        /// <param name="comment"></param>
        /// <param name="isSign"></param>
        /// <returns></returns>
        public int UpdateNextTaskStatus(Guid taskID, int status)
        {
            string sql = "UPDATE WorkFlowTask SET Status=@Status WHERE PrevID=@PrevID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@Status", status) ,
                new MySqlParameter("@PrevID",MySqlDbType.VarChar, 50){ Value = taskID }
            };
            return dbHelper.Execute(sql, parameters);
        }


        /// <summary>
        /// 得到一个流程实例一个步骤的任务
        /// </summary>
        /// <param name="flowID"></param>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public List<Model.WorkFlowTask> GetTaskList(Guid flowID, Guid stepID, Guid groupID)
        {
            string sql = "SELECT * FROM WorkFlowTask WHERE FlowID=@FlowID AND StepID=@StepID AND GroupID=@GroupID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50){ Value = flowID },
                new MySqlParameter("@StepID", MySqlDbType.VarChar, 50){ Value = stepID },
                new MySqlParameter("@GroupID", MySqlDbType.VarChar, 50){ Value = groupID }
            };
            var dataReader = dbHelper.GetDataReader(sql, parameters);
            List<RoadFlow.Data.Model.WorkFlowTask> List = DataReaderToList(dataReader);
            return List;
        }

        /// <summary>
        /// 得到一个流程实例一个步骤一个人员的任务
        /// </summary>
        /// <param name="flowID"></param>
        /// <param name="stepID"></param>
        /// <param name="groupID"></param>
        /// <param name="userID"></param>
        /// <returns></returns>
        public List<Model.WorkFlowTask> GetUserTaskList(Guid flowID, Guid stepID, Guid groupID, Guid userID)
        {
            string sql = "SELECT * FROM WorkFlowTask WHERE FlowID=@FlowID AND StepID=@StepID AND GroupID=@GroupID AND ReceiveID=@ReceiveID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50){ Value = flowID },
                new MySqlParameter("@StepID", MySqlDbType.VarChar, 50){ Value = stepID },
                new MySqlParameter("@GroupID", MySqlDbType.VarChar, 50){ Value = groupID },
                new MySqlParameter("@ReceiveID", MySqlDbType.VarChar, 50){ Value = userID }
            };
            var dataReader = dbHelper.GetDataReader(sql, parameters);
            List<RoadFlow.Data.Model.WorkFlowTask> List = DataReaderToList(dataReader);
            return List;
        }


        /// <summary>
        /// 得到一个实例的任务
        /// </summary>
        /// <param name="flowID"></param>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public List<RoadFlow.Data.Model.WorkFlowTask> GetTaskList(Guid flowID, Guid groupID)
        {
            string sql = string.Empty;
            MySqlParameter[] parameters;
            if (flowID == null || flowID.IsEmptyGuid())
            {
                sql = "SELECT * FROM WorkFlowTask WHERE GroupID=@GroupID";
                parameters = new MySqlParameter[]{
                    new MySqlParameter("@GroupID", MySqlDbType.VarChar, 50){ Value = groupID }
                };
            }
            else
            {
                sql = "SELECT * FROM WorkFlowTask WHERE FlowID=@FlowID AND GroupID=@GroupID";
                parameters = new MySqlParameter[]{
                    new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50){ Value = flowID },
                    new MySqlParameter("@GroupID", MySqlDbType.VarChar, 50){ Value = groupID }
                };
            }
            var dataReader = dbHelper.GetDataReader(sql, parameters);
            List<RoadFlow.Data.Model.WorkFlowTask> List = DataReaderToList(dataReader);
            return List;
        }

        /// <summary>
        /// 得到和一个任务同级的任务
        /// </summary>
        /// <param name="taskID">任务ID</param>
        /// <param name="isStepID">是否区分步骤ID，多步骤会签区分的是上一步骤ID</param>
        /// <returns></returns>
        public List<RoadFlow.Data.Model.WorkFlowTask> GetTaskList(Guid taskID, bool isStepID = true)
        {
            var task = Get(taskID);
            if (task == null)
            {
                return new List<Model.WorkFlowTask>() { };
            }
            string sql = string.Format("SELECT * FROM WorkFlowTask WHERE PrevID=@PrevID AND {0}", isStepID ? "StepID=@StepID" : "PrevStepID=@StepID");
            MySqlParameter[] parameters1 = new MySqlParameter[]{
                new MySqlParameter("@PrevID", MySqlDbType.VarChar, 50){ Value = task.PrevID },
                new MySqlParameter("@StepID", MySqlDbType.VarChar, 50){ Value = isStepID ? task.StepID : task.PrevStepID }
            };
            var dataReader = dbHelper.GetDataReader(sql, parameters1);
            List<RoadFlow.Data.Model.WorkFlowTask> List = DataReaderToList(dataReader);
            return List;
        }

        /// <summary>
        /// 得到一个任务的前一任务
        /// </summary>
        /// <param name="flowID"></param>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public List<Model.WorkFlowTask> GetPrevTaskList(Guid taskID)
        {
            string sql = "SELECT * FROM WorkFlowTask WHERE ID=(SELECT PrevID FROM WorkFlowTask WHERE ID=@ID)";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", MySqlDbType.VarChar, 50){ Value = taskID }
            };
            var dataReader = dbHelper.GetDataReader(sql, parameters);
            List<RoadFlow.Data.Model.WorkFlowTask> List = DataReaderToList(dataReader);
            return List;
        }

        /// <summary>
        /// 得到一个任务的后续任务
        /// </summary>
        /// <param name="flowID"></param>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public List<Model.WorkFlowTask> GetNextTaskList(Guid taskID)
        {
            string sql = "SELECT * FROM WorkFlowTask WHERE PrevID=@PrevID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@PrevID", MySqlDbType.VarChar, 50){ Value = taskID }
            };
            var dataReader = dbHelper.GetDataReader(sql, parameters);
            List<RoadFlow.Data.Model.WorkFlowTask> List = DataReaderToList(dataReader);
            return List;
        }


        /// <summary>
        /// 查询一个流程是否有任务数据
        /// </summary>
        /// <param name="flowID"></param>
        /// <returns></returns>
        public bool HasTasks(Guid flowID)
        {
            string sql = "SELECT   ID FROM WorkFlowTask WHERE FlowID=@FlowID LIMIT 0,1";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50){ Value = flowID }
            };
            var dataReader = dbHelper.GetDataReader(sql, parameters);
            bool has = dataReader.Rows.Count > 0;
            return has;
        }

        /// <summary>
        /// 查询一个用户在一个步骤是否有未完成任务
        /// </summary>
        /// <param name="flowID"></param>
        /// <returns></returns>
        public bool HasNoCompletedTasks(Guid flowID, Guid stepID, Guid groupID, string userID)
        {
            string sql = "SELECT  ID FROM WorkFlowTask WHERE FlowID=@FlowID AND StepID=@StepID AND GroupID=@GroupID AND ReceiveID=@ReceiveID AND Status IN(-1,0,1) LIMIT 0,1";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50){ Value = flowID },
                new MySqlParameter("@StepID", MySqlDbType.VarChar, 50){ Value = stepID },
                new MySqlParameter("@GroupID", MySqlDbType.VarChar, 50){ Value = groupID },
                new MySqlParameter("@ReceiveID", MySqlDbType.VarChar, 50){ Value = userID }
            };
            var dataReader = dbHelper.GetDataReader(sql, parameters);
            bool has = dataReader.Rows.Count > 0;
            return has;
        }

        /// <summary>
        /// 激活临时任务
        /// </summary>
        /// <param name="flowID"></param>
        /// <param name="stepID"></param>
        /// <param name="groupID"></param>
        /// <param name="completedTime">要求完成时间</param>
        /// <returns></returns>
        public int UpdateTempTasks(Guid flowID, Guid stepID, Guid groupID, DateTime? completedTime, DateTime receiveTime)
        {
            string sql = "UPDATE WorkFlowTask SET CompletedTime=@CompletedTime,ReceiveTime=@ReceiveTime,SenderTime=@SenderTime,Status=0 WHERE FlowID=@FlowID AND StepID=@StepID AND GroupID=@GroupID AND Status=-1";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ReceiveTime",receiveTime),
                new MySqlParameter("@SenderTime", receiveTime),
                new MySqlParameter("@FlowID",flowID),
                new MySqlParameter("@StepID", stepID),
                new MySqlParameter("@GroupID", groupID),
                !completedTime.HasValue ? new MySqlParameter("@CompletedTime",DBNull.Value ) :new MySqlParameter("@CompletedTime", completedTime.Value),
            };
            return dbHelper.Execute(sql, parameters);
        }

        /// <summary>
        /// 删除临时任务
        /// </summary>
        /// <param name="flowID"></param>
        /// <param name="stepID"></param>
        /// <param name="groupID"></param>
        /// <param name="prevStepID"></param>
        /// <returns></returns>
        public int DeleteTempTasks(Guid flowID, Guid stepID, Guid groupID, Guid prevStepID)
        {
            string sql = "DELETE from WorkFlowTask WHERE FlowID=@FlowID AND StepID=@StepID AND GroupID=@GroupID AND Status=-1";
            List<MySqlParameter> parameters = new List<MySqlParameter>(){
                new MySqlParameter("@FlowID", MySqlDbType.VarChar, 50){ Value = flowID },
                new MySqlParameter("@StepID", MySqlDbType.VarChar, 50){ Value = stepID },
                new MySqlParameter("@GroupID", MySqlDbType.VarChar, 50){ Value = groupID }
            };
            if (!prevStepID.IsEmptyGuid())
            {
                sql += " AND PrevStepID=@PrevStepID";
                parameters.Add(new MySqlParameter("@PrevStepID", MySqlDbType.VarChar, 50) { Value = prevStepID });
            }
            return dbHelper.Execute(sql, parameters.ToArray());
        }

        /// <summary>
        /// 得到一个任务的状态
        /// </summary>
        /// <param name="taskID"></param>
        /// <returns></returns>
        public int GetTaskStatus(Guid taskID)
        {
            string sql = "SELECT Status FROM WorkFlowTask WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", MySqlDbType.VarChar, 50){ Value = taskID }
            };
            string status = dbHelper.GetFieldValue(sql, parameters);
            int s;
            return status.IsInt(out s) ? s : -1;
        }

        /// <summary>
        /// 根据SubFlowID得到一个任务
        /// </summary>
        /// <param name="subflowGroupID"></param>
        /// <returns></returns>
        public List<Model.WorkFlowTask> GetBySubFlowGroupID(Guid subflowGroupID)
        {
            string sql = "SELECT * FROM WorkFlowTask WHERE SubFlowGroupID=@SubFlowGroupID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@SubFlowGroupID", MySqlDbType.VarChar, 50){ Value = subflowGroupID }
            };
            var dataReader = dbHelper.GetDataReader(sql, parameters);
            List<RoadFlow.Data.Model.WorkFlowTask> List = DataReaderToList(dataReader);
            return List;
        }
    }
}