using System;
using System.Data;
using System.Data.SqlClient;
using DSWeb.Models;
using WebSqlHelper;
using System.Collections;
using System.Collections.Generic;
using DSWeb.WorkFlow;

namespace DSWeb.EntityDA
{
    public class WorkFlowDoDA
    {
        //workflow_do
        private const string PARM_WORKFLOW_DO_GID = "@gid";
        private const string PARM_WORKFLOW_DO_WORKFLOW_ID = "@workflow_id";
                                                           
        private const string PARM_WORKFLOW_DO_BILL_NO = "@bill_no";
        private const string PARM_WORKFLOW_DO_STEP_ID = "@step_id";
        private const string PARM_WORKFLOW_DO_AUDITOR = "@auditor";
        private const string PARM_WORKFLOW_DO_AUDIT_TIME = "@audit_time";
        private const string PARM_WORKFLOW_DO_BSNO = "@bsno";
        private const string PARM_WORKFLOW_DO_FEE_ID = "@fee_id";
        private const string PARM_WORKFLOW_DO_FEE_STATUS = "@fee_status";
        private const string PARM_WORKFLOW_DO_DO_QUERY_SQL = "@do_query_sql";
        private const string PARM_WORKFLOW_DO_STEP_NO = "@step_no";
        private const string PARM_WORKFLOW_DO_IS_FINISH = "@is_finish";
        private const string PARM_WORKFLOW_DO_IS_DELETED = "@is_deleted";
        private const string PARM_WORKFLOW_DO_DELETE_STEP = "@delete_step";
        private const string PARM_WORKFLOW_DO_DELETE_OPERATOR = "@delete_operator";
        private const string PARM_WORKFLOW_DO_DELETE_STEP_NO = "@delete_stepno";
        private const string PARM_WORKFLOW_DO_DELETE_APPLICANT = "@applicant";

        //ch_fee_payapplication
        private const string PARM_SETTLE_APPL_GID = "@gid";
        private const string PARM_SETTLE_APPL_BILL_NO = "@bill_no";
        private const string PARM_SETTLE_APPL_BILL_STATUS = "@bill_status";
        private const string PARM_SETTLE_APPL_COMPANY_ID = "@company_id";
        //ch_fee
        private const string PARM_FEE_GID = "@gid";
        private const string PARM_FEE_ORDER_AMOUNT = "@order_amount";
        //ch_fee_do
        private const string PARM_CH_FEE_DO_GID = "@gid";

        private const string SQL_SELECT_WORKFLOW_DO_BY_GID = "SELECT GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH FROM workflow_do WHERE GID = @gid ";

        private const string SQL_SELECT_WORKFLOW_DO_BY_WORKFLOW_ID = "SELECT GID, WORKFLOWID,BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH FROM workflow_do WHERE WORKFLOWID = @workflow_id";

        private const string SQL_SELECT_WORKFLOW_DO_BY_FEEID_AND_WORKFLOWID = "SELECT GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH,APPLICANT FROM workflow_do WHERE WORKFLOWID = @workflow_id AND FEEID = @fee_id";

        private const string SQL_SELECT_WORKFLOW_DO_BY_BILLNO_AND_WORKFLOWID = "SELECT GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH,APPLICANT FROM workflow_do WHERE ISALLFINISH = 0 AND BILLNO = @bill_no AND WORKFLOWID =@workflow_id  ORDER BY STEPNO DESC ";

        private const string SQL_SELECT_WORKFLOW_DO_BY_AUDITORID_AND_WORKFLOWID = "SELECT GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH,APPLICANT FROM workflow_do WHERE WORKFLOWID = @workflow_id AND AUDITOR = @auditor ORDER BY STEPNO DESC ";

        private const string SQL_SELECT_WORKFLOW_DO_BY_BILLNO = "SELECT GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH,APPLICANT FROM workflow_do WHERE BILLNO = @bill_no ORDER BY STEPNO DESC ";

        private const string SQL_INSERT_WORKFLOW_DO = "INSERT INTO workflow_do (GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH,APPLICANT)"
                                                    + " VALUES(@gid,@workflow_id,@bill_no,@step_id,@auditor,GETDATE(),@bsno,@fee_id,@fee_status,@do_query_sql,@step_no,@is_finish,@applicant)";

        private const string SQL_UPDATE_WORKFLOW_DO_STEP = " UPDATE workflow_do SET ISFINISH = 1,AUDITTIME = GETDATE() WHERE GID = @gid ";

        private const string SQL_UPDATE_CH_FEE_PAY_APP = "UPDATE ch_fee_payapplication SET BILLSTATUS = @bill_status WHERE BILLNO = @bill_no";

        private const string SQL_UPDATE_WORKFLOW_DO_RECORD_REJECT_STEP = "UPDATE workflow_do SET ISDELETE = @is_deleted,DELETESTEP = @delete_step,DELETEOPERATOR = @delete_operator,DELETESTEPNO = @delete_stepno,ISFINISH = @is_finish,ISALLFINISH = 1 WHERE WORKFLOWID = @workflow_id AND BILLNO = @bill_no AND ISALLFINISH = 0 ";

        private const string SQL_SELECT_WORKFLOW_DO_STEP_NO = " SELECT AUDITOR+'#'+CAST(STEPNO as varchar) FROM workflow_do WHERE GID = @gid ";

        private const string SQL_UPDATE_WORKFLOW_DO_SAME_STEP = "UPDATE workflow_do SET ISFINISH = 1 WHERE WORKFLOWID = @workflow_id AND ISFINISH = 0 AND STEPNO = @step_no AND BILLNO = @bill_no";

        private string SQL_UPDATE_PAY_APPLICATION_STATUS = "UPDATE ch_fee_payapplication SET BILLSTATUS = @bill_status WHERE BILLNO = @bill_no AND COMPANYID = @company_id ";

        private string SQL_UPDATE_CH_FEE_FOR_REJECT = "UPDATE ch_fee SET ORDERAMOUNT = ORDERAMOUNT - @order_amount WHERE GID = @gid";

        private string SQL_UPDATE_CH_FEE_DO_FOR_DELETE = "UPDATE ch_fee_do SET ISDELETED = 1 WHERE GID = @gid";

        private string SQL_SELECT_WORKFLOW_DO_BY_BILLNO_FOR_MSG = "SELECT GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH,APPLICANT FROM workflow_do WHERE WORKFLOWID = @workflow_id AND BILLNO = @bill_no AND ISNULL(ISALLFINISH,0) <> 1 AND ISNULL(ISDELETE,0) <> 1 ";

        private string SQL_SELECT_WORKFLOW_DO_FEEAUDIT_PASS_FOR_MSG = "SELECT GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH,APPLICANT FROM workflow_do WHERE WORKFLOWID = @workflow_id AND BILLNO = @bill_no  AND ISNULL(ISDELETE,0) <> 1 ";

        private string SQL_SELECT_WORKFLOW_DO_FEEAUDIT_REJECT_FOR_MSG = "SELECT TOP 1 GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH,APPLICANT FROM workflow_do WHERE WORKFLOWID = @workflow_id AND BILLNO = @bill_no ORDER BY AUDITTIME DESC ";

        private string SQL_SELECT_WORKFLOW_DO_Exist_WorkflowID = "select count(GID) from workflow_do where workflowid=@workflowid ";

        private string SQL_SELECT_WORKFLOW_DO_Exist_WorkflowStepID = "select count(GID) from workflow_do where stepid=@step_id ";

        #region 检查是否为工作流（workflow.gid）定义了工作步骤（workflow_step）
        /// <summary>
        /// 检查工作流（workflow.gid）是否开始了审批流程（workflow_do）
        /// </summary>
        /// <param name="tempWorkFlowID">工作流GID</param>
        /// <returns></returns>
        public int GetExistWorkFlowStepID(string tempWorkFlowStepID)
        {
            int iResult = 0;
            int iExistState = 0;
            SqlParameter runningParm = new SqlParameter(PARM_WORKFLOW_DO_STEP_ID, SqlDbType.VarChar, 36);
            runningParm.Value = tempWorkFlowStepID;

            using (SqlConnection sqlConnection = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    iExistState = (int)SqlHelper.ExecuteScalar(sqlConnection, CommandType.Text, SQL_SELECT_WORKFLOW_DO_Exist_WorkflowStepID, runningParm);

                    if (iExistState > 0)
                    {
                        iResult = 1;//如果存在工作步骤，返回1
                    }
                }
                catch
                {
                    sqlConnection.Close();
                }
            }
            return iResult;
        }
        #endregion

        #region 检查是否为工作流（workflow.gid）定义了工作步骤（workflow_step）
        /// <summary>
        /// 检查工作流（workflow.gid）是否开始了审批流程（workflow_do）
        /// </summary>
        /// <param name="tempWorkFlowID">工作流GID</param>
        /// <returns></returns>
        public int GetExistWorkFlowID(string tempWorkFlowID)
        {
            int iResult = 0;
            int iExistState = 0;
            SqlParameter runningParm = new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID, SqlDbType.VarChar, 36);
            runningParm.Value = tempWorkFlowID;

            using (SqlConnection sqlConnection = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    iExistState = (int)SqlHelper.ExecuteScalar(sqlConnection, CommandType.Text, SQL_SELECT_WORKFLOW_DO_Exist_WorkflowID, runningParm);

                    if (iExistState > 0)
                    {
                        iResult = 1;//如果存在工作步骤，返回1
                    }
                    else
                    {
                        iResult = 0;
                    }
                }
                catch
                {
                    sqlConnection.Close();
                }
            }
            return iResult;
        }
        #endregion

        #region 获取审核驳回的工作流信息
        /// <summary>
        /// 获取审核驳回的工作流信息
        /// </summary>
        /// <param name="tempWorkFlowGID">工作流GID</param>
        /// <param name="tempBillNO">业务编号</param>
        /// <returns>返回实体类工作流记录</returns>
        public IList<WorkFlowDoEntity> GetWorkFlowDoAuditRejectForMessage(string tempWorkFlowGID, string tempBillNO)
        {
            //初始化返回变量
            IList<WorkFlowDoEntity> workFlowDoEntities = new List<WorkFlowDoEntity>();
            //初始化参数并赋值
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID, SqlDbType.VarChar, 36),
                new SqlParameter(PARM_WORKFLOW_DO_BILL_NO, SqlDbType.VarChar, 36)
            };

            parms[0].Value = tempWorkFlowGID;
            parms[1].Value = tempBillNO;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_WORKFLOW_DO_FEEAUDIT_REJECT_FOR_MSG, parms))
            {
                try
                {
                    //读取字段值
                    while (sqlRead.Read())
                    {
                        WorkFlowDoEntity workFlowDoEntity = new WorkFlowDoEntity();
                        if (!sqlRead.IsDBNull(0))
                        {
                            workFlowDoEntity.GID = sqlRead.GetString(0);
                        }
                        if (!sqlRead.IsDBNull(1))
                        {
                            workFlowDoEntity.WorkFlowID = sqlRead.GetString(1);
                        }
                        if (!sqlRead.IsDBNull(2))
                        {
                            workFlowDoEntity.BillNO = sqlRead.GetString(2);
                        }
                        if (!sqlRead.IsDBNull(3))
                        {
                            workFlowDoEntity.StepID = sqlRead.GetString(3);
                        }
                        if (!sqlRead.IsDBNull(4))
                        {
                            workFlowDoEntity.Auditor = sqlRead.GetString(4);
                        }
                        if (!sqlRead.IsDBNull(5))
                        {
                            workFlowDoEntity.AuditTime = sqlRead.GetDateTime(5);
                        }
                        if (!sqlRead.IsDBNull(6))
                        {
                            workFlowDoEntity.BSNO = sqlRead.GetString(6);
                        }
                        if (!sqlRead.IsDBNull(7))
                        {
                            workFlowDoEntity.FeeID = sqlRead.GetString(7);
                        }
                        if (!sqlRead.IsDBNull(8))
                        {
                            workFlowDoEntity.FeeStatus = sqlRead.GetInt32(8);
                        }
                        if (!sqlRead.IsDBNull(9))
                        {
                            workFlowDoEntity.DoQuerySql = sqlRead.GetString(9);
                        }
                        if (!sqlRead.IsDBNull(10))
                        {
                            workFlowDoEntity.StepNO = sqlRead.GetInt32(10);
                        }
                        if (!sqlRead.IsDBNull(11))
                        {
                            workFlowDoEntity.IsFinish = sqlRead.GetBoolean(11);
                        }
                        if (!sqlRead.IsDBNull(12))
                        {
                            workFlowDoEntity.Applicant = sqlRead.GetString(12);
                        }

                        workFlowDoEntities.Add(workFlowDoEntity);
                    }
                }
                catch (Exception exceError)
                {
                    //抛出异常
                    throw exceError;
                }
            }
            return workFlowDoEntities;
        }
        #endregion

        #region 获取审核通过和待审核的工作流信息
        /// <summary>
        /// 获取审核通过和待审核的工作流信息
        /// </summary>
        /// <param name="tempWorkFlowGID">工作流GID</param>
        /// <param name="tempBillNO">业务编号</param>
        /// <returns>返回实体类工作流记录</returns>
        public IList<WorkFlowDoEntity> GetWorkFlowDoAuditPassForMessage(string tempWorkFlowGID, string tempBillNO)
        {
            //初始化返回变量
            IList<WorkFlowDoEntity> workFlowDoEntities = new List<WorkFlowDoEntity>();
            //初始化参数并赋值
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID, SqlDbType.VarChar, 36),
                new SqlParameter(PARM_WORKFLOW_DO_BILL_NO, SqlDbType.VarChar, 36)
            };

            parms[0].Value = tempWorkFlowGID;
            parms[1].Value = tempBillNO;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_WORKFLOW_DO_FEEAUDIT_PASS_FOR_MSG, parms))
            {
                try
                {
                    //读取字段值
                    while (sqlRead.Read())
                    {
                        WorkFlowDoEntity workFlowDoEntity = new WorkFlowDoEntity();
                        if (!sqlRead.IsDBNull(0))
                        {
                            workFlowDoEntity.GID = sqlRead.GetString(0);
                        }
                        if (!sqlRead.IsDBNull(1))
                        {
                            workFlowDoEntity.WorkFlowID = sqlRead.GetString(1);
                        }
                        if (!sqlRead.IsDBNull(2))
                        {
                            workFlowDoEntity.BillNO = sqlRead.GetString(2);
                        }
                        if (!sqlRead.IsDBNull(3))
                        {
                            workFlowDoEntity.StepID = sqlRead.GetString(3);
                        }
                        if (!sqlRead.IsDBNull(4))
                        {
                            workFlowDoEntity.Auditor = sqlRead.GetString(4);
                        }
                        if (!sqlRead.IsDBNull(5))
                        {
                            workFlowDoEntity.AuditTime = sqlRead.GetDateTime(5);
                        }
                        if (!sqlRead.IsDBNull(6))
                        {
                            workFlowDoEntity.BSNO = sqlRead.GetString(6);
                        }
                        if (!sqlRead.IsDBNull(7))
                        {
                            workFlowDoEntity.FeeID = sqlRead.GetString(7);
                        }
                        if (!sqlRead.IsDBNull(8))
                        {
                            workFlowDoEntity.FeeStatus = sqlRead.GetInt32(8);
                        }
                        if (!sqlRead.IsDBNull(9))
                        {
                            workFlowDoEntity.DoQuerySql = sqlRead.GetString(9);
                        }
                        if (!sqlRead.IsDBNull(10))
                        {
                            workFlowDoEntity.StepNO = sqlRead.GetInt32(10);
                        }
                        if (!sqlRead.IsDBNull(11))
                        {
                            workFlowDoEntity.IsFinish = sqlRead.GetBoolean(11);
                        }
                        if (!sqlRead.IsDBNull(12))
                        {
                            workFlowDoEntity.Applicant = sqlRead.GetString(12);
                        }

                        workFlowDoEntities.Add(workFlowDoEntity);
                    }
                }
                catch (Exception exceError)
                {
                    //抛出异常
                    throw exceError;
                }
            }
            return workFlowDoEntities;
        }
        #endregion

        #region 通过工作流GID和申请编号BILLNO获取工作流运行表WorkFlowDo信息发送消息
        /// <summary>
        /// 通过工作流GID和申请编号BILLNO获取工作流运行表WorkFlowDo信息发送消息
        /// </summary>
        /// <param name="tempWorkFlowGID"></param>
        /// <param name="tempFeeGID"></param>
        /// <returns></returns>
        public IList<WorkFlowDoEntity> GetWorkFlowDoForMessage(string tempWorkFlowGID, string tempBillNO)
        {
            //初始化返回变量
            IList<WorkFlowDoEntity> workFlowDoEntities = new List<WorkFlowDoEntity>();
            //初始化参数并赋值
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID, SqlDbType.VarChar, 36),
                new SqlParameter(PARM_WORKFLOW_DO_BILL_NO, SqlDbType.VarChar, 36)
            };

            parms[0].Value = tempWorkFlowGID;
            parms[1].Value = tempBillNO;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_WORKFLOW_DO_BY_BILLNO_FOR_MSG, parms))
            {
                try
                {
                    //读取字段值
                    while (sqlRead.Read())
                    {
                        WorkFlowDoEntity workFlowDoEntity = new WorkFlowDoEntity();
                        if (!sqlRead.IsDBNull(0))
                        {
                            workFlowDoEntity.GID = sqlRead.GetString(0);
                        }
                        if (!sqlRead.IsDBNull(1))
                        {
                            workFlowDoEntity.WorkFlowID = sqlRead.GetString(1);
                        }
                        if (!sqlRead.IsDBNull(2))
                        {
                            workFlowDoEntity.BillNO = sqlRead.GetString(2);
                        }
                        if (!sqlRead.IsDBNull(3))
                        {
                            workFlowDoEntity.StepID = sqlRead.GetString(3);
                        }
                        if (!sqlRead.IsDBNull(4))
                        {
                            workFlowDoEntity.Auditor = sqlRead.GetString(4);
                        }
                        if (!sqlRead.IsDBNull(5))
                        {
                            workFlowDoEntity.AuditTime = sqlRead.GetDateTime(5);
                        }
                        if (!sqlRead.IsDBNull(6))
                        {
                            workFlowDoEntity.BSNO = sqlRead.GetString(6);
                        }
                        if (!sqlRead.IsDBNull(7))
                        {
                            workFlowDoEntity.FeeID = sqlRead.GetString(7);
                        }
                        if (!sqlRead.IsDBNull(8))
                        {
                            workFlowDoEntity.FeeStatus = sqlRead.GetInt32(8);
                        }
                        if (!sqlRead.IsDBNull(9))
                        {
                            workFlowDoEntity.DoQuerySql = sqlRead.GetString(9);
                        }
                        if (!sqlRead.IsDBNull(10))
                        {
                            workFlowDoEntity.StepNO = sqlRead.GetInt32(10);
                        }
                        if (!sqlRead.IsDBNull(11))
                        {
                            workFlowDoEntity.IsFinish = sqlRead.GetBoolean(11);
                        }
                        if (!sqlRead.IsDBNull(12))
                        {
                            workFlowDoEntity.Applicant = sqlRead.GetString(12);
                        }

                        workFlowDoEntities.Add(workFlowDoEntity);
                    }
                }
                catch (Exception exceError)
                {
                    //抛出异常
                    throw exceError;
                }
            }
            return workFlowDoEntities;
        }
        #endregion

        #region 通过工作流GID和费用GID获取工作流运行表WorkFlowDo信息
        /// <summary>
        /// 通过工作流GID和费用GID获取工作流运行表WorkFlowDo信息
        /// </summary>
        /// <param name="tempWorkFlowGID"></param>
        /// <param name="tempFeeGID"></param>
        /// <returns></returns>
        public WorkFlowDoEntity GetSigleWorkFlowDo(string tempWorkFlowGID,string tempFeeGID)
        {
            //初始化返回变量
            WorkFlowDoEntity workFlowDoEntity = null;
            //初始化参数并赋值
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID, SqlDbType.VarChar, 36),
                new SqlParameter(PARM_WORKFLOW_DO_FEE_ID, SqlDbType.VarChar, 36)
            };
                
            parms[0].Value = tempWorkFlowGID;
            parms[1].Value = tempFeeGID;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_WORKFLOW_DO_BY_FEEID_AND_WORKFLOWID, parms))
            {
                try
                {
                    workFlowDoEntity = new WorkFlowDoEntity();
                    //读取字段值
                    while (sqlRead.Read())
                    {
                        if (!sqlRead.IsDBNull(0))
                        {
                            workFlowDoEntity.GID = sqlRead.GetString(0);
                        }
                        if (!sqlRead.IsDBNull(1))
                        {
                            workFlowDoEntity.WorkFlowID = sqlRead.GetString(1);
                        }
                        if (!sqlRead.IsDBNull(2))
                        {
                            workFlowDoEntity.BillNO = sqlRead.GetString(2);
                        }
                        if (!sqlRead.IsDBNull(3))
                        {
                            workFlowDoEntity.StepID = sqlRead.GetString(3);
                        }
                        if (!sqlRead.IsDBNull(4))
                        {
                            workFlowDoEntity.Auditor = sqlRead.GetString(4);
                        }
                        if (!sqlRead.IsDBNull(5))
                        {
                            workFlowDoEntity.AuditTime = sqlRead.GetDateTime(5);
                        }
                        if (!sqlRead.IsDBNull(6))
                        {
                            workFlowDoEntity.BSNO = sqlRead.GetString(6);
                        }
                        if (!sqlRead.IsDBNull(7))
                        {
                            workFlowDoEntity.FeeID = sqlRead.GetString(7);
                        }
                        if (!sqlRead.IsDBNull(8))
                        {
                            workFlowDoEntity.FeeStatus = sqlRead.GetInt32(8);
                        }
                        if (!sqlRead.IsDBNull(9))
                        {
                            workFlowDoEntity.DoQuerySql = sqlRead.GetString(9);
                        }
                        if (!sqlRead.IsDBNull(10))
                        {
                            workFlowDoEntity.StepNO = sqlRead.GetInt32(10);
                        }
                        if (!sqlRead.IsDBNull(11))
                        {
                            workFlowDoEntity.IsFinish = sqlRead.GetBoolean(11);
                        }
                        if (!sqlRead.IsDBNull(12))
                        {
                            workFlowDoEntity.Applicant = sqlRead.GetString(12);
                        }
                    }
                }
                catch (Exception exceError)
                {
                    //抛出异常
                    throw exceError;
                }
            }
            return workFlowDoEntity;
        }
        #endregion

        #region 通过工作流GID和申请编号BILLNO获取工作流运行表WorkFlowDo信息
        /// <summary>
        /// 通过工作流GID和申请编号BILLNO获取工作流运行表WorkFlowDo信息
        /// </summary>
        /// <param name="tempWorkFlowGID"></param>
        /// <param name="tempFeeGID"></param>
        /// <returns></returns>
        public WorkFlowDoEntity GetSigleWorkFlowDoByBillNO(string tempWorkFlowGID, string tempBillNO)
        {
            //初始化返回变量
            WorkFlowDoEntity workFlowDoEntity = null;
            //初始化参数并赋值
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter(PARM_WORKFLOW_DO_BILL_NO, SqlDbType.VarChar, 36),
                new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID, SqlDbType.VarChar, 36)
            };

            parms[0].Value = tempBillNO;
            parms[1].Value = tempWorkFlowGID;
//            aparms[1].Value = tempBillNO;
            workFlowDoEntity = new WorkFlowDoEntity();

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_WORKFLOW_DO_BY_BILLNO_AND_WORKFLOWID, parms))
            {
                try
                {
                    //读取字段值
                    while (sqlRead.Read())
                    {
                        if (!sqlRead.IsDBNull(0))
                        {
                            workFlowDoEntity.GID = sqlRead.GetString(0);
                        }
                        if (!sqlRead.IsDBNull(1))
                        {
                            workFlowDoEntity.WorkFlowID = sqlRead.GetString(1);
                        }
                        if (!sqlRead.IsDBNull(2))
                        {
                            workFlowDoEntity.BillNO = sqlRead.GetString(2);
                        }
                        if (!sqlRead.IsDBNull(3))
                        {
                            workFlowDoEntity.StepID = sqlRead.GetString(3);
                        }
                        if (!sqlRead.IsDBNull(4))
                        {
                            workFlowDoEntity.Auditor = sqlRead.GetString(4);
                        }
                        if (!sqlRead.IsDBNull(5))
                        {
                            workFlowDoEntity.AuditTime = sqlRead.GetDateTime(5);
                        }
                        if (!sqlRead.IsDBNull(6))
                        {
                            workFlowDoEntity.BSNO = sqlRead.GetString(6);
                        }
                        if (!sqlRead.IsDBNull(7))
                        {
                            workFlowDoEntity.FeeID = sqlRead.GetString(7);
                        }
                        if (!sqlRead.IsDBNull(8))
                        {
                            workFlowDoEntity.FeeStatus = sqlRead.GetInt32(8);
                        }
                        if (!sqlRead.IsDBNull(9))
                        {
                            workFlowDoEntity.DoQuerySql = sqlRead.GetString(9);
                        }
                        if (!sqlRead.IsDBNull(10))
                        {
                            workFlowDoEntity.StepNO = sqlRead.GetInt32(10);
                        }
                        if (!sqlRead.IsDBNull(11))
                        {
                            workFlowDoEntity.IsFinish = sqlRead.GetBoolean(11);
                        }
                        if (!sqlRead.IsDBNull(12))
                        {
                            workFlowDoEntity.Applicant = sqlRead.GetString(12);
                        }
                    }
                }
                catch (Exception exceError)
                {
                    //抛出异常
                    throw exceError;
                }
            }
            return workFlowDoEntity;
        }
        #endregion

        #region 通过工作流GID和审核人GID获取工作流运行表WorkFlowDo信息
        /// <summary>
        /// 通过工作流GID和申请编号BILLNO获取工作流运行表WorkFlowDo信息
        /// </summary>
        /// <param name="tempWorkFlowGID"></param>
        /// <param name="tempFeeGID"></param>
        /// <returns></returns>
        public WorkFlowDoEntity GetSigleWorkFlowDoByAuditor(string tempWorkFlowGID, string tempUserID)
        {
            //初始化返回变量
            WorkFlowDoEntity workFlowDoEntity = null;
            //初始化参数并赋值
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID, SqlDbType.VarChar, 36),
                new SqlParameter(PARM_WORKFLOW_DO_AUDITOR, SqlDbType.VarChar, 36)
            };

            parms[0].Value = tempWorkFlowGID;
            parms[1].Value = tempUserID;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_WORKFLOW_DO_BY_AUDITORID_AND_WORKFLOWID, parms))
            {
                try
                {
                    workFlowDoEntity = new WorkFlowDoEntity();
                    //读取字段值
                    while (sqlRead.Read())
                    {
                        if (!sqlRead.IsDBNull(0))
                        {
                            workFlowDoEntity.GID = sqlRead.GetString(0);
                        }
                        if (!sqlRead.IsDBNull(1))
                        {
                            workFlowDoEntity.WorkFlowID = sqlRead.GetString(1);
                        }
                        if (!sqlRead.IsDBNull(2))
                        {
                            workFlowDoEntity.BillNO = sqlRead.GetString(2);
                        }
                        if (!sqlRead.IsDBNull(3))
                        {
                            workFlowDoEntity.StepID = sqlRead.GetString(3);
                        }
                        if (!sqlRead.IsDBNull(4))
                        {
                            workFlowDoEntity.Auditor = sqlRead.GetString(4);
                        }
                        if (!sqlRead.IsDBNull(5))
                        {
                            workFlowDoEntity.AuditTime = sqlRead.GetDateTime(5);
                        }
                        if (!sqlRead.IsDBNull(6))
                        {
                            workFlowDoEntity.BSNO = sqlRead.GetString(6);
                        }
                        if (!sqlRead.IsDBNull(7))
                        {
                            workFlowDoEntity.FeeID = sqlRead.GetString(7);
                        }
                        if (!sqlRead.IsDBNull(8))
                        {
                            workFlowDoEntity.FeeStatus = sqlRead.GetInt32(8);
                        }
                        if (!sqlRead.IsDBNull(9))
                        {
                            workFlowDoEntity.DoQuerySql = sqlRead.GetString(9);
                        }
                        if (!sqlRead.IsDBNull(10))
                        {
                            workFlowDoEntity.StepNO = sqlRead.GetInt32(10);
                        }
                        if (!sqlRead.IsDBNull(11))
                        {
                            workFlowDoEntity.IsFinish = sqlRead.GetBoolean(11);
                        }
                        if (!sqlRead.IsDBNull(12))
                        {
                            workFlowDoEntity.Applicant = sqlRead.GetString(12);
                        }
                    }
                }
                catch (Exception exceError)
                {
                    //抛出异常
                    throw exceError;
                }
            }
            return workFlowDoEntity;
        }
        #endregion

        #region 通过工作流GID和申请编号BILLNO获取工作流运行表WorkFlowDo信息
        /// <summary>
        /// 通过工作流GID和申请编号BILLNO获取工作流运行表WorkFlowDo信息
        /// </summary>
        /// <param name="tempWorkFlowGID"></param>
        /// <param name="tempFeeGID"></param>
        /// <returns></returns>
        public IList<WorkFlowDoEntity> GetAllWorkFlowDoByBillNO(string tempWorkFlowGID, string tempBillNO)
        {
            //初始化返回变量
            IList<WorkFlowDoEntity> workFlowDoEntities = new List<WorkFlowDoEntity>();
            //初始化参数并赋值
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID, SqlDbType.VarChar, 36),
                new SqlParameter(PARM_WORKFLOW_DO_BILL_NO, SqlDbType.VarChar, 36)
            };

            parms[0].Value = tempWorkFlowGID;
            parms[1].Value = tempBillNO;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_WORKFLOW_DO_BY_BILLNO_AND_WORKFLOWID, parms))
            {
                try
                {
                    
                    //读取字段值
                    while (sqlRead.Read())
                    {
                        WorkFlowDoEntity workFlowDoEntity = new WorkFlowDoEntity();
                        if (!sqlRead.IsDBNull(0))
                        {
                            workFlowDoEntity.GID = sqlRead.GetString(0);
                        }
                        if (!sqlRead.IsDBNull(1))
                        {
                            workFlowDoEntity.WorkFlowID = sqlRead.GetString(1);
                        }
                        if (!sqlRead.IsDBNull(2))
                        {
                            workFlowDoEntity.BillNO = sqlRead.GetString(2);
                        }
                        if (!sqlRead.IsDBNull(3))
                        {
                            workFlowDoEntity.StepID = sqlRead.GetString(3);
                        }
                        if (!sqlRead.IsDBNull(4))
                        {
                            workFlowDoEntity.Auditor = sqlRead.GetString(4);
                        }
                        if (!sqlRead.IsDBNull(5))
                        {
                            workFlowDoEntity.AuditTime = sqlRead.GetDateTime(5);
                        }
                        if (!sqlRead.IsDBNull(6))
                        {
                            workFlowDoEntity.BSNO = sqlRead.GetString(6);
                        }
                        if (!sqlRead.IsDBNull(7))
                        {
                            workFlowDoEntity.FeeID = sqlRead.GetString(7);
                        }
                        if (!sqlRead.IsDBNull(8))
                        {
                            workFlowDoEntity.FeeStatus = sqlRead.GetInt32(8);
                        }
                        if (!sqlRead.IsDBNull(9))
                        {
                            workFlowDoEntity.DoQuerySql = sqlRead.GetString(9);
                        }
                        if (!sqlRead.IsDBNull(10))
                        {
                            workFlowDoEntity.StepNO = sqlRead.GetInt32(10);
                        }
                        if (!sqlRead.IsDBNull(11))
                        {
                            workFlowDoEntity.IsFinish = sqlRead.GetBoolean(11);
                        }
                        if (!sqlRead.IsDBNull(12))
                        {
                            workFlowDoEntity.Applicant = sqlRead.GetString(12);
                        }
                        workFlowDoEntities.Add(workFlowDoEntity);
                    }
                }
                catch (Exception exceError)
                {
                    //抛出异常
                    throw exceError;
                }
            }
            return workFlowDoEntities;
        }
        #endregion

        #region 通过申请编号BILLNO获取工作流运行表WorkFlowDo信息
        /// <summary>
        /// 通过申请编号BILLNO获取工作流运行表WorkFlowDo信息
        /// </summary>
        /// <param name="tempWorkFlowGID"></param>
        /// <param name="tempFeeGID"></param>
        /// <returns></returns>
        public IList<WorkFlowDoEntity> GetWorkFlowDoByBillNO(string tempBillNO)
        {
            //初始化返回变量
            IList<WorkFlowDoEntity> workFlowDoEntities = new List<WorkFlowDoEntity>();
            //初始化参数并赋值
            SqlParameter parm = new SqlParameter(PARM_WORKFLOW_DO_BILL_NO, SqlDbType.VarChar, 36);
            parm.Value = tempBillNO;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_WORKFLOW_DO_BY_BILLNO, parm))
            {
                try
                {
                    //读取字段值
                    while (sqlRead.Read())
                    {
                        WorkFlowDoEntity workFlowDoEntity = new WorkFlowDoEntity();
                        if (!sqlRead.IsDBNull(0))
                        {
                            workFlowDoEntity.GID = sqlRead.GetString(0);
                        }
                        if (!sqlRead.IsDBNull(1))
                        {
                            workFlowDoEntity.WorkFlowID = sqlRead.GetString(1);
                        }
                        if (!sqlRead.IsDBNull(2))
                        {
                            workFlowDoEntity.BillNO = sqlRead.GetString(2);
                        }
                        if (!sqlRead.IsDBNull(3))
                        {
                            workFlowDoEntity.StepID = sqlRead.GetString(3);
                        }
                        if (!sqlRead.IsDBNull(4))
                        {
                            workFlowDoEntity.Auditor = sqlRead.GetString(4);
                        }
                        if (!sqlRead.IsDBNull(5))
                        {
                            workFlowDoEntity.AuditTime = sqlRead.GetDateTime(5);
                        }
                        if (!sqlRead.IsDBNull(6))
                        {
                            workFlowDoEntity.BSNO = sqlRead.GetString(6);
                        }
                        if (!sqlRead.IsDBNull(7))
                        {
                            workFlowDoEntity.FeeID = sqlRead.GetString(7);
                        }
                        if (!sqlRead.IsDBNull(8))
                        {
                            workFlowDoEntity.FeeStatus = sqlRead.GetInt32(8);
                        }
                        if (!sqlRead.IsDBNull(9))
                        {
                            workFlowDoEntity.DoQuerySql = sqlRead.GetString(9);
                        }
                        if (!sqlRead.IsDBNull(10))
                        {
                            workFlowDoEntity.StepNO = sqlRead.GetInt32(10);
                        }
                        if (!sqlRead.IsDBNull(11))
                        {
                            workFlowDoEntity.IsFinish = sqlRead.GetBoolean(11);
                        }
                        if (!sqlRead.IsDBNull(12))
                        {
                            workFlowDoEntity.Applicant = sqlRead.GetString(12);
                        }
                        workFlowDoEntities.Add(workFlowDoEntity);
                    }
                }
                catch (Exception exceError)
                {
                    //抛出异常
                    throw exceError;
                }
            }
            return workFlowDoEntities;
        }
        #endregion

        #region 插入WorkFlowDo信息
        /// <summary>
        /// 插入WorkFlowDo信息
        /// </summary>
        /// <param name="tempWorkFlowDoEntities">WorkFlowDo实体类数组</param>
        /// <returns>值1表示插入正常 值为-1-2表示插入异常失败</returns>
        public int InsertWorkFlowDo(IList<WorkFlowDoEntity> tempWorkFlowDoEntities)
        {
            int iResult = 0;

            using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    SqlParameter[] parms = new SqlParameter[] { 
                        new SqlParameter(PARM_WORKFLOW_DO_GID,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_WORKFLOW_DO_BILL_NO,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_WORKFLOW_DO_STEP_ID,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_WORKFLOW_DO_AUDITOR,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_WORKFLOW_DO_BSNO,SqlDbType.VarChar,100),
                        new SqlParameter(PARM_WORKFLOW_DO_FEE_ID,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_WORKFLOW_DO_FEE_STATUS,SqlDbType.Int),
                        new SqlParameter(PARM_WORKFLOW_DO_DO_QUERY_SQL,SqlDbType.VarChar,200),
                        new SqlParameter(PARM_WORKFLOW_DO_STEP_NO,SqlDbType.Int),
                        new SqlParameter(PARM_WORKFLOW_DO_IS_FINISH,SqlDbType.Bit),
                        new SqlParameter(PARM_WORKFLOW_DO_DELETE_APPLICANT,SqlDbType.VarChar,36)
                    };

                    foreach (WorkFlowDoEntity workFlowDoEntity in tempWorkFlowDoEntities)
                    {
                        parms[0].Value = workFlowDoEntity.GID;
                        parms[1].Value = workFlowDoEntity.WorkFlowID;
                        parms[2].Value = workFlowDoEntity.BillNO;
                        parms[3].Value = workFlowDoEntity.StepID;
                        parms[4].Value = workFlowDoEntity.Auditor;
                        parms[5].Value = workFlowDoEntity.BSNO;
                        parms[6].Value = workFlowDoEntity.FeeID;
                        parms[7].Value = workFlowDoEntity.FeeStatus;
                        parms[8].Value = workFlowDoEntity.DoQuerySql;
                        parms[9].Value = workFlowDoEntity.StepNO;
                        parms[10].Value = workFlowDoEntity.IsFinish;
                        parms[11].Value = workFlowDoEntity.Applicant;

                        SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_WORKFLOW_DO, parms);
                    }
                    //事务提交
                    sqlTran.Commit();

                    iResult = 1;//状态为1表示插入成功
                }
                catch (Exception execError)
                {
                    iResult = -1;//有异常,插入失败
                    sqlTran.Rollback();
                    iResult = -2;//插入异常,事务已回滚成功
                    throw execError;
                }
                finally
                {
                    SqlHelper.CloseConnection();
                }
            }
            return iResult;
        }
        #endregion

        #region 更新操作审核完毕
        /// <summary>
        /// 更新操作审核完毕
        /// </summary>
        /// <param name="tempWorkFlowDoEntity">工作流DO表实体类</param>
        /// <param name="tempIsLastStep">是否是最后一步操作</param>
        /// <returns>值1表示更新成功 值为-2表示更新异常事务回滚</returns>
        public int UpdateWorkFlowDoStep(IList<WorkFlowDoEntity> tempWorkFlowDoEntities, IList<WorkFlowDoEntity> tempNextWorkFlowDoEntities, bool tempIsLastStep, IList<WorkFlowStepEntity> tempWorkFlowStepEntities)
        {
            //审核人审核完毕后,先更新当前审核状态ISFINISH=True,然后将下一步需要其他审核人的步骤写入到workflow_do表
            int iResult = 0;

            #region 参数
            SqlParameter[] updateParms = new SqlParameter[] { 
                new SqlParameter(PARM_WORKFLOW_DO_IS_FINISH,SqlDbType.Bit),
                new SqlParameter(PARM_WORKFLOW_DO_GID,SqlDbType.VarChar,36)
            };

            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter(PARM_WORKFLOW_DO_GID,SqlDbType.VarChar,36),
                new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID,SqlDbType.VarChar,36),
                new SqlParameter(PARM_WORKFLOW_DO_BILL_NO,SqlDbType.VarChar,36),
                new SqlParameter(PARM_WORKFLOW_DO_STEP_ID,SqlDbType.VarChar,36),
                new SqlParameter(PARM_WORKFLOW_DO_AUDITOR,SqlDbType.VarChar,36),
                new SqlParameter(PARM_WORKFLOW_DO_BSNO,SqlDbType.VarChar,100),
                new SqlParameter(PARM_WORKFLOW_DO_FEE_ID,SqlDbType.VarChar,36),
                new SqlParameter(PARM_WORKFLOW_DO_FEE_STATUS,SqlDbType.Int),
                new SqlParameter(PARM_WORKFLOW_DO_DO_QUERY_SQL,SqlDbType.VarChar,200),
                new SqlParameter(PARM_WORKFLOW_DO_STEP_NO,SqlDbType.Int),
                new SqlParameter(PARM_WORKFLOW_DO_IS_FINISH,SqlDbType.Bit),
                new SqlParameter(PARM_WORKFLOW_DO_DELETE_APPLICANT,SqlDbType.VarChar,36)
            };

            SqlParameter[] updateAppParms = new SqlParameter[] { 
                new SqlParameter(PARM_SETTLE_APPL_BILL_STATUS,SqlDbType.Int),
                new SqlParameter(PARM_SETTLE_APPL_BILL_NO,SqlDbType.VarChar,20)
            };
            #endregion
            using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    SqlParameter stepNoParm = new SqlParameter(PARM_SETTLE_APPL_GID, SqlDbType.VarChar, 36);
                    stepNoParm.Value = tempWorkFlowDoEntities[0].GID;

                    string stepVal = (string)SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, SQL_SELECT_WORKFLOW_DO_STEP_NO, stepNoParm) as string;

                    if (stepVal == null)
                    {
                        throw new Exception();
                    }
                    string[] strSplit = stepVal.Split(new char[] { '#' });
                    string strCurrentAuditor = strSplit[0].ToString();
                    int iCurrentStepNo = int.Parse(strSplit[1].ToString());

                    foreach (WorkFlowStepEntity workFlowStepEntity in tempWorkFlowStepEntities)
                    {
                        if (workFlowStepEntity.StepNO == iCurrentStepNo && workFlowStepEntity.Auditor != strCurrentAuditor)
                        {
                            SqlParameter[] updateSameParms = new SqlParameter[] { 
                                new SqlParameter(PARM_WORKFLOW_DO_STEP_NO,SqlDbType.Int),
                                new SqlParameter(PARM_WORKFLOW_DO_BILL_NO,SqlDbType.VarChar,36),
                                new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID,SqlDbType.VarChar,36)
                            };

                            updateSameParms[0].Value = iCurrentStepNo;
                            updateSameParms[1].Value = tempWorkFlowDoEntities[0].BillNO;
                            updateSameParms[2].Value = tempWorkFlowDoEntities[0].WorkFlowID;

                            SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_WORKFLOW_DO_SAME_STEP, updateSameParms);
                        }
                    }

                    foreach (WorkFlowDoEntity workFlowDoEntity in tempWorkFlowDoEntities)
                    {

                        if (workFlowDoEntity.IsFinish == true)
                        {
                            updateParms[0].Value = 1;
                            updateParms[1].Value = workFlowDoEntity.GID;

                            if (tempIsLastStep)
                            {
                                updateAppParms[0].Value = 0;
                            }
                            else
                            {
                                updateAppParms[0].Value = 3;
                            }
                            updateAppParms[1].Value = workFlowDoEntity.BillNO;
                        }
                        else
                        {
                            //如果是工作流步骤的最后一步,则不自动向workflow_do表插入下一步骤信息
                            if (!tempIsLastStep)
                            {
                                updateAppParms[0].Value = 3;
                                updateAppParms[1].Value = workFlowDoEntity.BillNO;
                            }
                            else
                            {
                                updateAppParms[0].Value = 0;
                                updateAppParms[1].Value = workFlowDoEntity.BillNO;
                            }
                        }
                    }
                    SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_WORKFLOW_DO_STEP, updateParms);

                    SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_CH_FEE_PAY_APP, updateAppParms);
                    //如果是工作流步骤的最后一步,则不自动向workflow_do表插入下一步骤信息
                    if (!tempIsLastStep)
                    {
                        foreach (WorkFlowDoEntity workFlowDoEntity in tempNextWorkFlowDoEntities)
                        {
                            parms[0].Value = workFlowDoEntity.GID;
                            parms[1].Value = workFlowDoEntity.WorkFlowID;
                            parms[2].Value = workFlowDoEntity.BillNO;
                            parms[3].Value = workFlowDoEntity.StepID;
                            parms[4].Value = workFlowDoEntity.Auditor;
                            parms[5].Value = workFlowDoEntity.BSNO;
                            parms[6].Value = workFlowDoEntity.FeeID;
                            parms[7].Value = workFlowDoEntity.FeeStatus;
                            parms[8].Value = workFlowDoEntity.DoQuerySql;
                            parms[9].Value = workFlowDoEntity.StepNO;
                            parms[10].Value = workFlowDoEntity.IsFinish;
                            parms[11].Value = workFlowDoEntity.Applicant;

                            SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_WORKFLOW_DO, parms);
                        }

                    }
                    sqlTran.Commit();

                   
                    iResult = 1;//状态为1表示插入成功
                }
                catch (Exception execError)
                {
                    iResult = -1;//有异常,插入失败
                    sqlTran.Rollback();
                    iResult = -2;//插入异常,事务已回滚成功
                    throw execError;
                }
                finally
                {
                    SqlHelper.CloseConnection();
                }
            }

            return iResult;
        }
        #endregion

        #region 审核步骤驳回操作
        /// <summary>
        /// 审核步骤驳回操作
        /// </summary>
        /// <param name="tempWorkFlowDoEntity">WorkFlowDo 实体类</param>
        /// <returns>值1表示操作完成 值小于1表示操作失败</returns>
        public int ExcuteRejectWorkFlowDo(WorkFlowDoEntity tempWorkFlowDoEntity,WorkFlowType workFlowType,IList<FeeDoEntity> feeDoEntities,string tempBillNO,string tempCompanyID)
        {
            int iResult = 0;

            using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    
                    SqlParameter[] parms = new SqlParameter[] { 
                        new SqlParameter(PARM_WORKFLOW_DO_IS_DELETED,SqlDbType.Bit),
                        new SqlParameter(PARM_WORKFLOW_DO_DELETE_STEP,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_WORKFLOW_DO_DELETE_OPERATOR,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_WORKFLOW_DO_BILL_NO,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_WORKFLOW_DO_DELETE_STEP_NO,SqlDbType.Int),
                        new SqlParameter(PARM_WORKFLOW_DO_IS_FINISH,SqlDbType.Bit)
                    };

                    parms[0].Value = tempWorkFlowDoEntity.IsDelete;
                    parms[1].Value = tempWorkFlowDoEntity.DeleteStep;
                    parms[2].Value = tempWorkFlowDoEntity.DeleteOperator;
                    parms[3].Value = tempWorkFlowDoEntity.WorkFlowID;
                    parms[4].Value = tempWorkFlowDoEntity.BillNO;
                    parms[5].Value = tempWorkFlowDoEntity.DeleteStepNO;
                    parms[6].Value = tempWorkFlowDoEntity.IsFinish;

                    SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_WORKFLOW_DO_RECORD_REJECT_STEP, parms);

                    switch (workFlowType)
                    {
                        case WorkFlowType.PAYSETTLEAUDIT:
                            foreach(FeeDoEntity feeDoEntity in feeDoEntities)
                            {
                                SqlParameter[] updateAppParms = new SqlParameter[] { 
                                    new SqlParameter(PARM_SETTLE_APPL_BILL_STATUS,SqlDbType.Int),
                                    new SqlParameter(PARM_SETTLE_APPL_BILL_NO,SqlDbType.VarChar,20),
                                    new SqlParameter(PARM_SETTLE_APPL_COMPANY_ID,SqlDbType.VarChar,36)
                                };
                                updateAppParms[0].Value = 6;//付费申请费用驳回
                                updateAppParms[1].Value = tempBillNO;
                                updateAppParms[2].Value = tempCompanyID;

                                SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_PAY_APPLICATION_STATUS, updateAppParms);
                                
                                SqlParameter[] updateFeeDoParms = new SqlParameter[] { 
                                    new SqlParameter(PARM_CH_FEE_DO_GID,SqlDbType.VarChar,36)
                                };
                                updateFeeDoParms[0].Value = feeDoEntity.GID;

                                SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_CH_FEE_DO_FOR_DELETE, updateFeeDoParms);

                                SqlParameter[] updateFeeParms = new SqlParameter[] { 
                                    new SqlParameter(PARM_FEE_GID,SqlDbType.VarChar,36),
                                    new SqlParameter(PARM_FEE_ORDER_AMOUNT,SqlDbType.Decimal,20)
                                };

                                updateFeeParms[0].Value = feeDoEntity.FeeID;
                                updateFeeParms[1].Value = feeDoEntity.Amount;

                                SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_CH_FEE_FOR_REJECT, updateFeeParms);
                            }
                            break;
                        case WorkFlowType.FEEAUDIT:
                            break;
                        case WorkFlowType.FEEMODIFYAUDIT:
                            break;
                    }
                    sqlTran.Commit();

                    iResult = 1;//状态为1表示插入成功
                }
                catch (Exception execError)
                {
                    iResult = -1;//有异常,插入失败
                    sqlTran.Rollback();
                    iResult = -2;//插入异常,事务已回滚成功
                    throw execError;
                }
                finally
                {
                    SqlHelper.CloseConnection();
                }
            }
            return iResult;
        }
        #endregion

        #region 费用提交审核_事务
        public int FeeAudit(WorkFlowEntity workFlowEntity, string strGid, string strUserID)
        {
            int iResult = 0;
            FeeDA feeDA = new FeeDA();

            using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    #region 费用提交审核_工作流
                    string strDeptID = " SELECT B.GID FROM user_baseinfo as A INNER JOIN sys_dept as B ON A.DEPTNAME = B.DEPTNAME INNER JOIN user_company as C ON A.USERID = C.USERID WHERE A.USERID = '" + strUserID + "' ";
                    object statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, strDeptID, null);
                    if (statusObj == null)
                    {
                        strDeptID = "";
                    }
                    else
                    {
                        strDeptID = statusObj.ToString().Trim();
                    }
                    //
                    //根据Gid更新ch_fee.feestatus费用状态信息:为"提交审核"
                    iResult = -3;
                    string SQL_UPDATE_FEE_STATUS_BY_GID = "UPDATE ch_fee SET FEESTATUS=2 WHERE GID='" + strGid + "'";
                    SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_FEE_STATUS_BY_GID, null);
                    //
                    //查看当前提交的应收应付费用审核已经进入工作流，通过工作流GID和申请编号BILLNO获取工作流运行表WorkFlowDo信息
                    WorkFlowDoDA workFlowDoDA = new WorkFlowDoDA();
                    string strSQL = "SELECT GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH,APPLICANT FROM workflow_do WHERE ISALLFINISH = 0 AND BILLNO='" + strGid.Trim() + "' AND WORKFLOWID='" + workFlowEntity.GID.ToString().Trim() + "' ORDER BY STEPNO DESC ";
                    statusObj = SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, strSQL, null);
                    if (statusObj == null)
                    {
                        IList<WorkFlowDoEntity> workFlowDoEntities = new List<WorkFlowDoEntity>();
                        foreach (WorkFlowStepEntity step in workFlowEntity.WorkFlowSteps)
                        {
                            //遍历所有工作流下步骤,获取第一步骤GID信息
                            if (step.StepNO == 1)
                            {
                                if (step.IsDepartment)
                                {
                                    string strDepartmentID = step.DepartmentID == null ? "" : step.DepartmentID;
                                    if (strDepartmentID.Trim() != null)
                                    {
                                        SysDeptDA sysDeptDA = new SysDeptDA();
                                        if (strDeptID.Trim().Equals(strDepartmentID) && strDepartmentID != "")
                                        {
                                            WorkFlowDoEntity initWorkFlowDoEntity = new WorkFlowDoEntity();
                                            initWorkFlowDoEntity.GID = Guid.NewGuid().ToString();
                                            initWorkFlowDoEntity.WorkFlowID = workFlowEntity.GID;
                                            initWorkFlowDoEntity.StepID = step.GID;
                                            initWorkFlowDoEntity.StepNO = step.StepNO;
                                            initWorkFlowDoEntity.Auditor = step.Auditor == null ? step.DefaultAuditor : step.Auditor;
                                            initWorkFlowDoEntity.FeeID = "";
                                            initWorkFlowDoEntity.BSNO = "";
                                            initWorkFlowDoEntity.FeeStatus = 0;
                                            initWorkFlowDoEntity.DoQuerySql = "";
                                            initWorkFlowDoEntity.BillNO = strGid;
                                            initWorkFlowDoEntity.IsFinish = false;
                                            initWorkFlowDoEntity.Applicant = strUserID;
                                            workFlowDoEntities.Add(initWorkFlowDoEntity);
                                        }
                                    }
                                }
                                else
                                {
                                    WorkFlowDoEntity initWorkFlowDoEntity = new WorkFlowDoEntity();
                                    initWorkFlowDoEntity.GID = Guid.NewGuid().ToString();
                                    initWorkFlowDoEntity.WorkFlowID = workFlowEntity.GID;
                                    initWorkFlowDoEntity.StepID = step.GID;
                                    initWorkFlowDoEntity.StepNO = step.StepNO;
                                    initWorkFlowDoEntity.Auditor = step.Auditor == null ? step.DefaultAuditor : step.Auditor;
                                    initWorkFlowDoEntity.FeeID = "";
                                    initWorkFlowDoEntity.BSNO = "";
                                    initWorkFlowDoEntity.FeeStatus = 0;
                                    initWorkFlowDoEntity.DoQuerySql = "";
                                    initWorkFlowDoEntity.BillNO = strGid;
                                    initWorkFlowDoEntity.IsFinish = false;
                                    initWorkFlowDoEntity.Applicant = strUserID;
                                    workFlowDoEntities.Add(initWorkFlowDoEntity);
                                }
                            }
                        }
                        //插入WorkFlowDo信息
                        //iWorkFlowStatus = workFlowDoDA.InsertWorkFlowDo(workFlowDoEntities);
                        SqlParameter[] parms = new SqlParameter[] { 
                            new SqlParameter(PARM_WORKFLOW_DO_GID,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_BILL_NO,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_STEP_ID,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_AUDITOR,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_BSNO,SqlDbType.VarChar,100),
                            new SqlParameter(PARM_WORKFLOW_DO_FEE_ID,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_FEE_STATUS,SqlDbType.Int),
                            new SqlParameter(PARM_WORKFLOW_DO_DO_QUERY_SQL,SqlDbType.VarChar,200),
                            new SqlParameter(PARM_WORKFLOW_DO_STEP_NO,SqlDbType.Int),
                            new SqlParameter(PARM_WORKFLOW_DO_IS_FINISH,SqlDbType.Bit),
                            new SqlParameter(PARM_WORKFLOW_DO_DELETE_APPLICANT,SqlDbType.VarChar,36)
                        };
                        foreach (WorkFlowDoEntity workFlowDoEntity in workFlowDoEntities)
                        {
                            parms[0].Value = workFlowDoEntity.GID;
                            parms[1].Value = workFlowDoEntity.WorkFlowID;
                            parms[2].Value = workFlowDoEntity.BillNO;
                            parms[3].Value = workFlowDoEntity.StepID;
                            parms[4].Value = workFlowDoEntity.Auditor;
                            parms[5].Value = workFlowDoEntity.BSNO;
                            parms[6].Value = workFlowDoEntity.FeeID;
                            parms[7].Value = workFlowDoEntity.FeeStatus;
                            parms[8].Value = workFlowDoEntity.DoQuerySql;
                            parms[9].Value = workFlowDoEntity.StepNO;
                            parms[10].Value = workFlowDoEntity.IsFinish;
                            parms[11].Value = workFlowDoEntity.Applicant;

                            //string SQL_INSERT_WORKFLOW_DO = "INSERT INTO workflow_do (GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH,APPLICANT) VALUES(@gid,@workflow_id,@bill_no,@step_id,@auditor,GETDATE(),@bsno,@fee_id,@fee_status,@do_query_sql,@step_no,@is_finish,@applicant)";
                            iResult = -4;
                            SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_WORKFLOW_DO, parms);
                        }
                        //
                        iResult = -5;//更新费用状态失败
                        string SQL_UPDATE_FEE_AUDITSTATUS = "UPDATE ch_fee SET AUDITSTATUS=1 WHERE GID='" + strGid + "'";
                        SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_FEE_AUDITSTATUS, null);
                    }
                    else
                    {
                        IList<WorkFlowDoEntity> workFlowDoEntities = new List<WorkFlowDoEntity>();
                        foreach (WorkFlowStepEntity step in workFlowEntity.WorkFlowSteps)
                        {
                            //遍历所有工作流下步骤,获取第一步骤GID信息
                            if (step.StepNO == 1)
                            {
                                if (step.IsDepartment)
                                {
                                    string strDepartmentID = step.DepartmentID == null ? "" : step.DepartmentID;
                                    if (strDepartmentID.Trim() != null)
                                    {
                                        if (strDeptID.Trim().Equals(strDepartmentID) && strDepartmentID != "")
                                        {
                                            WorkFlowDoEntity initWorkFlowDoEntity = new WorkFlowDoEntity();
                                            initWorkFlowDoEntity.GID = Guid.NewGuid().ToString();
                                            initWorkFlowDoEntity.WorkFlowID = workFlowEntity.GID;
                                            initWorkFlowDoEntity.StepID = step.GID;
                                            initWorkFlowDoEntity.StepNO = step.StepNO;
                                            initWorkFlowDoEntity.Auditor = step.Auditor == null ? step.DefaultAuditor : step.Auditor;
                                            initWorkFlowDoEntity.FeeID = "";
                                            initWorkFlowDoEntity.BSNO = "";
                                            initWorkFlowDoEntity.FeeStatus = 0;
                                            initWorkFlowDoEntity.DoQuerySql = "";
                                            initWorkFlowDoEntity.BillNO = strGid;
                                            initWorkFlowDoEntity.IsFinish = false;
                                            initWorkFlowDoEntity.Applicant = strUserID;
                                            workFlowDoEntities.Add(initWorkFlowDoEntity);
                                        }
                                    }
                                }
                                else
                                {
                                    WorkFlowDoEntity initWorkFlowDoEntity = new WorkFlowDoEntity();
                                    initWorkFlowDoEntity.GID = Guid.NewGuid().ToString();
                                    initWorkFlowDoEntity.WorkFlowID = workFlowEntity.GID;
                                    initWorkFlowDoEntity.StepID = step.GID;
                                    initWorkFlowDoEntity.StepNO = step.StepNO;
                                    initWorkFlowDoEntity.Auditor = step.Auditor == null ? step.DefaultAuditor : step.Auditor;
                                    initWorkFlowDoEntity.FeeID = "";
                                    initWorkFlowDoEntity.BSNO = "";
                                    initWorkFlowDoEntity.FeeStatus = 0;
                                    initWorkFlowDoEntity.DoQuerySql = "";
                                    initWorkFlowDoEntity.BillNO = strGid;
                                    initWorkFlowDoEntity.IsFinish = false;
                                    initWorkFlowDoEntity.Applicant = strUserID;
                                    workFlowDoEntities.Add(initWorkFlowDoEntity);
                                }
                            }
                        }

                        //iWorkFlowStatus = workFlowDoDA.InsertWorkFlowDo(workFlowDoEntities);
                        SqlParameter[] parms = new SqlParameter[] { 
                            new SqlParameter(PARM_WORKFLOW_DO_GID,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_WORKFLOW_ID,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_BILL_NO,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_STEP_ID,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_AUDITOR,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_BSNO,SqlDbType.VarChar,100),
                            new SqlParameter(PARM_WORKFLOW_DO_FEE_ID,SqlDbType.VarChar,36),
                            new SqlParameter(PARM_WORKFLOW_DO_FEE_STATUS,SqlDbType.Int),
                            new SqlParameter(PARM_WORKFLOW_DO_DO_QUERY_SQL,SqlDbType.VarChar,200),
                            new SqlParameter(PARM_WORKFLOW_DO_STEP_NO,SqlDbType.Int),
                            new SqlParameter(PARM_WORKFLOW_DO_IS_FINISH,SqlDbType.Bit),
                            new SqlParameter(PARM_WORKFLOW_DO_DELETE_APPLICANT,SqlDbType.VarChar,36)
                        };
                        foreach (WorkFlowDoEntity workFlowDoEntity in workFlowDoEntities)
                        {
                            parms[0].Value = workFlowDoEntity.GID;
                            parms[1].Value = workFlowDoEntity.WorkFlowID;
                            parms[2].Value = workFlowDoEntity.BillNO;
                            parms[3].Value = workFlowDoEntity.StepID;
                            parms[4].Value = workFlowDoEntity.Auditor;
                            parms[5].Value = workFlowDoEntity.BSNO;
                            parms[6].Value = workFlowDoEntity.FeeID;
                            parms[7].Value = workFlowDoEntity.FeeStatus;
                            parms[8].Value = workFlowDoEntity.DoQuerySql;
                            parms[9].Value = workFlowDoEntity.StepNO;
                            parms[10].Value = workFlowDoEntity.IsFinish;
                            parms[11].Value = workFlowDoEntity.Applicant;

                            //string SQL_INSERT_WORKFLOW_DO = "INSERT INTO workflow_do (GID, WORKFLOWID, BILLNO, STEPID, AUDITOR, AUDITTIME, BSNO, FEEID, FEESTATUS, DOQUERYSQL, STEPNO, ISFINISH,APPLICANT) VALUES(@gid,@workflow_id,@bill_no,@step_id,@auditor,GETDATE(),@bsno,@fee_id,@fee_status,@do_query_sql,@step_no,@is_finish,@applicant)";
                            iResult = -6;
                            SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_WORKFLOW_DO, parms);
                        }

                        //int iTempStatus = feeDA.UpdateFeeAuditStatus(strGid, 1);
                        iResult = -7;//更新费用状态失败
                        string SQL_UPDATE_FEE_AUDITSTATUS = "UPDATE ch_fee SET AUDITSTATUS=1 WHERE GID='" + strGid + "'";
                        SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_FEE_AUDITSTATUS, null);
                    }
                    #endregion
                    sqlTran.Commit();
                    iResult = 1;//状态为1表示插入成功
                }
                catch (Exception execError)
                {
                    iResult = -1;//有异常,插入失败
                    sqlTran.Rollback();
                    iResult = -2;//插入异常,事务已回滚成功
                    throw execError;
                }
                finally
                {
                    SqlHelper.CloseConnection();
                }
            }
            return iResult;
        }
        #endregion
    }
}