SELECT * FROM L_loan_info WHERE step_id=@step_id

SELECT * FROM L_loan_info WHERE loan_id IN (SELECT DISTINCT loan_id FROM l_tranct_proc WHERE step_id=@step_id)

/// <summary>
/// 方法：获得审批（或签批）数据列表
/// 开发：王洪剑http://www.cnblogs.com/walkingp     http://www.51obj.cn/
/// 时间：2010-6-29
/// 最后修改时间：2010-6-29
/// 修改详情：
/// </summary>
/// <param name="step_emp_id">签批人（默认为当前操作员）：0、本环节 其他、当前处理人</param>
/// <param name="action_id">步骤</param>
/// <param name="step_action">操作值：0：待审批/签批 1：已审批/签批 _：所有</param>
/// <param name="version">版本：3.0</param>
/// <returns></returns>
public List<Hope.Model.L_loan_info> GetModelByProcess(int step_emp_id, int action_id, string step_action, int version)
{
    #region
    string sql = "SELECT COUNT(*) FROM s_flow_info WHERE del_sign='1' and flow_id in(select flow_id from s_step_info where action_id=@action_id)";
    SqlParameter[] para ={ new SqlParameter("@action_id", SqlDbType.Int, 4) };
    para[0].Value = action_id;
    int count = int.Parse(DbHelperSQL.GetSingle(sql, para).ToString());
    string[] arrFlowId = new string[count];//流程信息
    sql = "SELECT flow_id FROM s_flow_info WHERE del_sign='1' and flow_id in(select flow_id from s_step_info where action_id=@action_id)";
    DataTable dt = DbHelperSQL.Query(sql, para).Tables[0];
    for (int i = 0; i < count; i++)
    {
        arrFlowId[i] = dt.Rows[i]["flow_id"].ToString();
    }

    string[] pre_action_id = new string[count];//当前流程中上一流程id
    string[] next_action_id = new string[count];//当前流程中下一流程id

    for (int i = 0; i < count; i++)
    {
        sql = "SELECT TOP 1 action_id FROM s_step_info WHERE step_order_no<(SELECT order_no FROM S_action_info WHERE action_id=@action_id) AND flow_id=" + arrFlowId[i] + " ORDER BY s_step_info.step_order_no DESC";
        SqlParameter[] paras ={ new SqlParameter("@action_id", SqlDbType.Int, 4) };
        paras[0].Value = action_id;
        dt.Clear();
        dt = DbHelperSQL.Query(sql, paras).Tables[0];
        if (dt.Rows.Count > 0)
            pre_action_id[i] = dt.Rows[0][0].ToString();

        sql = "SELECT TOP 1 action_id FROM s_step_info,l_loan_info WHERE step_order_no>(SELECT order_no FROM S_action_info WHERE action_id=@action_id) AND s_step_info.flow_id=" + arrFlowId[i] + " ORDER BY s_step_info.step_order_no";
        SqlParameter[] paras_ ={
                new SqlParameter("@action_id",SqlDbType.Int,4)
        };
        paras_[0].Value = action_id;
        dt.Clear();
        dt = DbHelperSQL.Query(sql, paras).Tables[0];
        if (dt.Rows.Count > 0)
            next_action_id[i] = dt.Rows[0][0].ToString();
    }

    DataSet ds = new DataSet();
    for (int k = 0; k < count; k++)
    {
        StringBuilder sbTmp = new StringBuilder();
        if (!string.IsNullOrEmpty(pre_action_id[k]))
            sbTmp.Append("(step_id=" + pre_action_id[k] + " AND step_action='1')");
        if (!string.IsNullOrEmpty(pre_action_id[k]) && !string.IsNullOrEmpty(next_action_id[k]))
            sbTmp.Append(" OR ");
        if (!string.IsNullOrEmpty(next_action_id[k]))
            sbTmp.Append("(step_id=" + next_action_id[k] + " AND step_action='2')");

        string strTemp = "1=1";
        if (!string.IsNullOrEmpty(sbTmp.ToString()))
            strTemp += " AND " + sbTmp.ToString();

        sql = "SELECT * FROM l_loan_info WHERE ";
        sql += "1=1";
        if (step_action == "1")//已审批（签批）
        {
            sql += " AND loan_id IN (SELECT loan_id FROM l_tranct_proc WHERE step_id=@action_id AND step_action='1'";
            if (step_emp_id == 0)
                sql += ")";
            else
                sql += " AND step_emp_id=@step_emp_id)";
        }
        else if (step_action == "0")//待审批（签批）
        {
            sql += " AND step_id=@action_id AND loan_id IN (SELECT loan_id FROM l_tranct_proc WHERE " + strTemp + ")";
            if (step_emp_id == 0)
                sql += "";
            else
                sql += " AND loan_id IN (select loan_id from l_loan_next_emp where next_emp_id=@step_emp_id)";
        }
        else if (step_action == "")//所有
        {
            sql += " AND loan_id IN (SELECT loan_id FROM l_tranct_proc WHERE step_id=@action_id AND step_action='1'";
            if (step_emp_id == 0)
                sql += ")";
            else
                sql += " AND step_emp_id=@step_emp_id)";//已审批（签批）
            sql += " UNION ";
            sql += " AND step_id=@action_id AND loan_id IN (SELECT loan_id FROM l_tranct_proc WHERE " + strTemp + ")";
            if (step_emp_id == 0)
                sql += "";
            else
                sql += " AND loan_id IN (select loan_id from l_loan_next_emp where next_emp_id=@step_emp_id)";//待审批（签批）
        }
        sql += " ORDER BY loan_id DESC";

        SqlParameter[] parameters ={
            new SqlParameter("@step_emp_id",SqlDbType.Int,4),
            new SqlParameter("@action_id",SqlDbType.Int,4)};
        parameters[0].Value = step_emp_id;
        parameters[1].Value = action_id;
        if (ds.Tables.Count == 0)
            ds = DbHelperSQL.Query(sql, parameters);
        else
        {
            ds.Merge(DbHelperSQL.Query(sql, parameters), true, MissingSchemaAction.AddWithKey);
        }
    }

    dt = ds.Tables[0];

    /*去除重复*/
    DataView dv = new DataView(dt);
    string[] strCol ={ "loan_id" };
    dt = dv.ToTable(true, strCol);

    Hope.Model.L_loan_info model;
    List<Hope.Model.L_loan_info> modelList = new List<Hope.Model.L_loan_info>();
    if (dt.Rows.Count > 0)
    {
        #region
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            model = new Hope.Model.L_loan_info();
            model = GetModel(int.Parse(dt.Rows[i]["loan_id"].ToString()));
            modelList.Add(model);
        }
        #endregion
        return modelList;
    }
    else
    {
        return null;
    }
}