public DataSet GetQXList(string jsbm, string qxType, string _dwbm, string _bmbm, string key)
{
string strWhere = " JSBM=:JSBM AND QXLX=:QXLX AND trim(DWBM)=:DWBM AND BMBM=:BMBM";
StringBuilder strSql = new StringBuilder();
strSql.Append("select distinct QXBM,QXMC,JSBM,QXLX ");
strSql.Append(" FROM XT_DM_QX ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
strSql.Append(" where 1=1 AND " + strWhere);
object[] objValues = new object[4];
objValues[0] = jsbm.Trim();
objValues[1] = qxType;
objValues[2] = _dwbm.Trim();
objValues[3] = _bmbm.Trim();
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetQXList(string jsbm, string qxType, string _dwbm, string _bmbm)", "EDRS.OracleDAL.XT_DM_QX", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select BMBM,DWBM,FBMBM,BMMC,BMJC,BMAHJC,BMWHJC,SFLSJG,SFCBBM,BMXH,BZ,SFSC,BMYS ");
strSql.Append(" FROM XT_ZZJG_BMBM ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.XT_ZZJG_BMBM", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetListByPageEx(string strWhere, string orderby, int startIndex, int endIndex, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by T.XH desc");
}
strSql.AppendFormat(")AS Ro, T.* from {0} T ", @"(select * from YX_DZJZ_LSGL)");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE 1=1 " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Ro between {0} and {1}", startIndex, endIndex);
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetListByPageEx(string strWhere, string orderby, int startIndex, int endIndex, params object[] objValues)", "EDRS.OracleDAL.YX_DZJZ_LSGL", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获取记录总数
/// </summary>
public int GetRecordCountAndGn(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("select count(1) from xt_zzjg_rybm{0} T right join xt_qx_rygnfp{0} b on T.dwbm=b.dwbm and T.gh=b.gh", ConfigHelper.GetConfigString("OrcDBLinq"));
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
object obj = 0;
try
{
obj = DbHelperOra.GetSingle(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public int GetRecordCount(string strWhere, params object[] objValues)", "EDRS.OracleDAL.XT_ZZJG_RYBM", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 获取记录总数
/// </summary>
public int GetRecordCount(string dwbm, string jsbm, string strWhere, params object[] objValues)
{
string sql = "(select b.*,a.dwmc,a.fdwbm from xt_zzjg_dwbm a right join xt_dm_qx c on a.dwbm=c.qxbm and c.dwbm='" + dwbm + "' and jsbm in (" + jsbm + ") and qxlx=0 right join XY_DZJZ_SBDJ b on c.qxbm =b.dwbm)";
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("select count(1) FROM {0} ", sql);
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
object obj = null;
try
{
obj = DbHelperOra.GetSingle(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public int GetRecordCount(string dwbm, string jsbm, string strWhere, params object[] objValues)", "EDRS.OracleDAL.XY_DZJZ_SBDJ", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select MBJZBH,MLBH,FMLBH,MLXSMC,MLBM,MLLX,MLXX,MLSXH,SSLBBM,SSLBMC,SFBX,SFBXQFBXH ");
strSql.Append(" FROM XT_DZJZ_JZMBML ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.XT_DZJZ_JZMBML", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetListByBm(string strWhere, string orderby, int startIndex, int endIndex, params object[] objValues)
{
string tableName = string.Format("(select r.* from xt_qx_ryjsfp rj join xt_zzjg_rybm r on rj.gh = r.gh and rj.dwbm = r.dwbm where 1=1 {0})", strWhere);
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by T.GH desc");
}
strSql.AppendFormat(")AS Ro, T.GH,T.DWBM,T.MC,T.DLBM,T.KL,T.YDDHHM,T.DZYJ,T.GZZH,T.YDWBM,T.YDWMC,T.SFLSRY,T.SFTZ,T.SFSC,T.XB,T.CAID from {1}{0} T ", ConfigHelper.GetConfigString("OrcDBLinq"), tableName);
//if (!string.IsNullOrEmpty(strWhere.Trim()))
//{
// strSql.Append(" WHERE 1=1 " + strWhere);
//}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Ro between {0} and {1}", startIndex, endIndex);
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, params object[] objValues)", "EDRS.OracleDAL.XT_ZZJG_RYBM", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select JZBH,SFSC,CJSJ,ZHXGSJ,FQDWBM,FQL,DWBM,BMSAH,TYSAH,JZMC,JZLJ,JZSCSJ,JZSCRGH,JZSCRXM,JZMS,JZXGH,SFKYGX,GXYWBMJH,MRSFGKPZ,ZZZT,JZPZ,JZSHRBH,JZSHR,JZSHSJ,AJBH,WSBH,WSMC ");
strSql.Append(" FROM YX_DZJZ_JZJBXX ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.YX_DZJZ_JZJBXX", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere, params object[] objValues)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("select CSBH,CSKEY,CSVALUE,DYSJ,FZBS ");
strSql.Append(" FROM XT_DZJZ_ZZCS ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
if(strWhere.Trim()!="")
{
strSql.Append(" where 1=1 "+strWhere);
}
try
{
return DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.XT_DZJZ_ZZCS", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return null;
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select BMSAH,DJBH,YJBH,YJKSSJ,YJJZSJ,JZZTXS,YJFS,FZFS,FYDYSL,SFSC,CJSJ,ZHXGSJ,FQDWBM,FQL,CBDW_BM,CBDW_MC ");
strSql.Append(" FROM YX_AG_YJAP ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.YX_AG_YJAP", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select DWBM,BMBM,JSBM,GH,ZJLDGH ");
strSql.Append(" FROM XT_QX_RYJSFP ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.XT_QX_RYJSFP", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select BMSAH,TYSAH,JZWJBH,JZWJMC,JZWJXSMC,JZWJCFLJ,JZWJSCSJ,JZWJMD5,JZWJDX,FQL,SFSC,CJSJ,XZCS,WJHZ,WJSXH,WJBZXX,JZWJXXMC,JZMLBH,JBH ");
strSql.Append(" FROM YX_DZJZ_JZWJCC ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.YX_DZJZ_JZWJCC", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
public DataSet GetTreeList(string strWhere, string strQxWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
if (strWhere.IndexOf(":DWBM") == -1)//根据名称筛选
{
strSql.AppendFormat("select distinct t1.*");
strSql.AppendFormat(",(select COUNT(1) from xt_dm_qx qx WHERE QXLX = 0 AND TRIM(qx.Qxbm) = TRIM(t1.dwbm) ");
strSql.AppendFormat("{0}", strQxWhere);
strSql.AppendFormat(") qx ");
strSql.AppendFormat(" from XT_ZZJG_DWBM{0} t1 left join XT_ZZJG_DWBM{0} t2 on(t2.dwbm = t1.fdwbm) ", ConfigHelper.GetConfigString("OrcDBLinq"));
}
else
{
strSql.AppendFormat("select distinct t1.*");
strSql.AppendFormat(",(select COUNT(1) from xt_dm_qx qx WHERE QXLX = 0 AND TRIM(qx.Qxbm) = TRIM(t1.dwbm)");
strSql.AppendFormat("{0}", strQxWhere);
strSql.AppendFormat(") qx ");
strSql.AppendFormat(" from XT_ZZJG_DWBM{0} t1", ConfigHelper.GetConfigString("OrcDBLinq"));
}
strSql.AppendFormat(" where 1=1 {0}", strWhere);
strSql.Append(" order by t1.DWBM");
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strQxWhere + strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetTreeList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.XT_DM_QX", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
public DataSet GetLBList(string jsbm, string _dwbm, string _bmbm, string key)
{
string strWhere = " JSBM IN (" + jsbm + ") AND QXLX=:QXLX AND trim(DWBM)=:DWBM AND BMBM IN (" + _bmbm + ") ";
object[] objValues = new object[5];
objValues[0] = 1;//权限类型
objValues[1] = _dwbm.Trim();
if (!string.IsNullOrEmpty(key))
{
strWhere += " and QXMC like :QXMC ";
objValues[2] = "%" + key + "%";
}
StringBuilder strSql = new StringBuilder();
strSql.Append("select distinct QXBM,QXMC");
strSql.Append(" FROM XT_DM_QX ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
strSql.Append(" where 1=1 AND " + strWhere);
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetLBList(string jsbm, string _dwbm, string _bmbm)", "EDRS.OracleDAL.XT_DM_QX", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 根据分类获取案件
/// </summary>
/// <param name="strWhere"></param>
/// <param name="objValues"></param>
/// <returns></returns>
public DataTable GetListGroupByAjLx(string strWhere, string strWhere1, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT
COUNT(1) AJCount,
SUM(Case When IsRegard > 0 then 1 else 0 end) RegardCount,
COUNT(1) - SUM(Case When IsRegard > 0 then 1 else 0 end) NotRegardCount,
AJLB_Bm,
AJLB_Mc ");
strSql.Append(@" FROM
(
SELECT
(select count(1) from yx_dzjz_jzjbxx ML where ML.Bmsah=AJ.Bmsah) IsRegard ,
AJ.Ajlb_Bm,
AJ.Ajlb_Mc
FROM
TYYW_GG_AJJBXX AJ
WHERE 1=1");
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(" " + strWhere);
}
strSql.AppendFormat(") AJ");
strSql.AppendFormat(" WHERE 1=1 " + strWhere1);
strSql.Append(" GROUP BY AJLB_Bm,AJLB_Mc");
strSql.Append(" having COUNT(1) > 0 ");//去除无案件的类别
try
{
DataSet ds = DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere + strWhere1, objValues));
if (ds != null && ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataTable GetListGroupByAjLx(string strWhere, params object[] objValues)", "EDRS.OracleDAL.EDRS_Report", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select FPBM,YJXH,WJXH,ADDTIME,SFSC ");
strSql.Append(" FROM YX_DZJZ_LSAJWJFP ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.YX_DZJZ_LSAJWJFP", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 根据案件信息赛选案件
/// </summary>
/// <param name="strWhere"></param>
/// <param name="objValues"></param>
/// <returns></returns>
public DataTable GetListGroupByAj(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"
select JCount,MCount,WCount,PageCount,(select count(1) from yx_dzjz_jzjbxx y where y.Bmsah=T1.Bmsah) IsRegard,
T1.AJMC,
T1.BMSAH,
T1.AJLB_MC,
T1.CBDW_MC,
T1.CBBM_MC,
T1.CBR,
T1.DQJD,
T1.SLRQ,
T1.AJZT,
T1.DQRQ,
T1.BJRQ,
T1.WCRQ,
T1.GDRQ,
T1.AJLB_BM,
T1.CBDW_BM
");
strSql.Append(@"
from TYYW_GG_AJJBXX T1
LEFT JOIN
(
SELECT BMSAH,COUNT(1) JCount FROM YX_DZJZ_JZML WHERE MLLX = 1 AND SFSC='N' GROUP BY BMSAH
) T2 ON (T1.BMSAH = T2.BMSAH)
LEFT JOIN
(
SELECT BMSAH,COUNT(1) MCount FROM YX_DZJZ_JZML WHERE MLLX = 2 AND SFSC='N' GROUP BY BMSAH
) T3 ON (T1.BMSAH = T3.BMSAH)
LEFT JOIN
(
SELECT BMSAH,COUNT(1) WCount FROM YX_DZJZ_JZML WHERE MLLX = 3 AND SFSC='N' GROUP BY BMSAH
) T4 ON (T1.BMSAH = T4.BMSAH)
LEFT JOIN
(
SELECT BMSAH,COUNT(1) PageCount FROM yx_dzjz_jzmlwj WHERE SFSC = 'N' GROUP BY BMSAH
) T5 ON (T1.BMSAH = T5.BMSAH)");
strSql.Append(@" where T1.SFSC='N' ");
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(strWhere);
}
try
{
DataSet ds = DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
if (ds != null && ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataTable GetListGroupByAj(string strWhere, params object[] objValues)", "EDRS.OracleDAL.EDRS_Report", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select DYSQDH,SQDM,DYSQSJ,LSZH,SQBZ,DYZT,DYR,DYRGH,DYZFY,DYSJ,CLSM ");
strSql.Append(" FROM YX_DZJZ_DYSQD ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.YX_DZJZ_DYSQD", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetListByPageAndGn(string strWhere, string orderby, int startIndex, int endIndex, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by T.GH desc");
}
strSql.AppendFormat(")AS Ro, T.GH,T.DWBM,T.MC,T.DLBM,T.KL,T.YDDHHM,T.DZYJ,T.GZZH,T.YDWBM,T.YDWMC,T.SFLSRY,T.SFTZ,T.SFSC,T.XB,T.CAID,c.GNBM,c.GNMC from XT_ZZJG_RYBM{0} T ", ConfigHelper.GetConfigString("OrcDBLinq"));
strSql.AppendFormat(" right join xt_qx_rygnfp{0} b on T.dwbm=b.dwbm and T.gh=b.gh", ConfigHelper.GetConfigString("OrcDBLinq"));
strSql.AppendFormat(" left join xt_qx_gndy{0} c on b.gnbm=c.gnbm", ConfigHelper.GetConfigString("OrcDBLinq"));
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE 1=1 " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Ro between {0} and {1}", startIndex, endIndex);
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, params object[] objValues)", "EDRS.OracleDAL.XT_ZZJG_RYBM", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获取功能分类和功能列表
/// </summary>
public DataSet GetListByType(string dwbm, string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select flbm,flmc,fflbm,dwbm,flxh,gnct,sfdjy from ( ");
strSql.AppendFormat(" select a.flbm,a.flmc,a.fflbm,a.dwbm,a.flxh,'' as gnct,'N' as sfdjy From XT_QX_GNFL {0} a where a.sfsc='N' ", ConfigHelper.GetConfigString("OrcDBLinq"));
strSql.Append(" union ");
strSql.AppendFormat(" select a.gnbm,a.gnmc,a.flbm,a.dwbm,a.gnxh,a.gnct,a.sfdjy From xt_qx_gndy {0} a where a.sfsc='N' ", ConfigHelper.GetConfigString("OrcDBLinq"));
strSql.Append(" ) t");
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
strSql.Append(" start with fflbm Is null connect by fflbm=prior flbm group by flbm,flmc,fflbm,dwbm,flxh,gnct,sfdjy order by flxh asc ");
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetListByType(string dwbm, string strWhere, params object[] objValues)", "EDRS.OracleDAL.XT_QX_GNDY", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetListAndDWBM(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select GH,XT_ZZJG_RYBM.DWBM,DWMC,MC,DLBM,KL,YDDHHM,DZYJ,GZZH,YDWBM,YDWMC,SFLSRY,SFTZ,XT_ZZJG_RYBM.SFSC,XB,CAID ");
strSql.Append(" FROM XT_ZZJG_RYBM ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
strSql.Append(" join xt_zzjg_dwbm ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
strSql.Append(" on XT_ZZJG_RYBM.DWBM=xt_zzjg_dwbm.DWBM ");
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetListAndDWBM(string strWhere, params object[] objValues)", "EDRS.OracleDAL.XT_ZZJG_RYBM", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获取角色对应功能
/// </summary>
public DataSet GetListByBound(string dwbm, string gh, string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select distinct flbm,flmc,fflbm,dwbm,flxh,gnct from ( ");
strSql.AppendFormat(" select a.flbm,a.flmc,a.fflbm,a.dwbm,a.flxh,'' gnct,'' jsbm, '' bmbm From XT_QX_GNFL {0} a where a.sfsc='N' ", ConfigHelper.GetConfigString("OrcDBLinq"));
strSql.Append(" union all ");
strSql.Append(" select b.gnbm,b.gnmc,b.flbm,b.dwbm,b.gnxh,b.gnct,a.jsbm,a.bmbm from xt_qx_jsgnfp ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
strSql.Append(" a join xt_qx_gndy ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
//strSql.Append(" b on a.gnbm=b.gnbm and a.dwbm='"+dwbm+"' and b.dwbm='"+dwbm+"'");
strSql.Append(" b on a.gnbm=b.gnbm ");
strSql.Append(" right join XT_QX_RYJSFP c on (a.jsbm = c.jsbm and a.dwbm=c.dwbm and a.bmbm=c.bmbm) where c.DWBM='" + dwbm + "' and c.GH='" + gh + "'");// or a.bmbm= c.bmbm
strSql.Append(" union all ");
strSql.Append("select e.gnbm,e.gnmc,e.flbm,f.dwbm,e.gnxh,e.gnct,'',f.bmbm from xt_qx_gndy e ");
strSql.Append("right join xt_qx_rygnfp f on e.gnbm = f.gnbm and f.dwbm='" + dwbm + "' and f.gh='" + gh + "'");
strSql.Append(") t ");
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
strSql.Append(" start with fflbm Is not null connect by prior fflbm= flbm order by flxh asc ");
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetListByBound(string dwbm,string gh, string strWhere, params object[] objValues)", "EDRS.OracleDAL.XT_QX_GNDY", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetListByPage(string dwbm, string jsbm, string strWhere, string orderby, int startIndex, int endIndex, params object[] objValues)
{
string sql = "(select b.*,a.dwmc,a.fdwbm from xt_zzjg_dwbm a join xt_dm_qx c on a.dwbm=c.qxbm and c.dwbm='" + dwbm + "' and jsbm in (" + jsbm + ") and qxlx=0 join XY_DZJZ_SBDJ b on c.qxbm =b.dwbm)";
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by T.DEVSN desc");
}
strSql.AppendFormat(")AS Ro, T.* from {0} T ", sql);
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE 1=1 " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Ro between {0} and {1}", startIndex, endIndex);
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetListByPage(string dwbm, string jsbm, string strWhere, string orderby, int startIndex, int endIndex, params object[] objValues)", "EDRS.OracleDAL.XY_DZJZ_SBDJ", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 根据卷筛选案件
/// </summary>
/// <param name="strWhere"></param>
/// <param name="objValues"></param>
/// <returns></returns>
public DataTable GetListGroupByJ(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT T1.BMSAH,T1.MLBH,CJSJ,ZHXGSJ,MLXSMC,SSLBBM,SSLBMC,T1.MLLX");
strSql.Append(@" FROM YX_DZJZ_JZML T1");
//strSql.Append(@"LEFT JOIN
//(
// SELECT FMLBH,COUNT(1) MCount FROM YX_DZJZ_JZML WHERE MLLX = 2 AND SFSC = 'N' GROUP BY FMLBH
//) T2 ON (T1.MLBH = T2.FMLBH)
//LEFT JOIN
//(
// SELECT CASE WHEN T1.FMLBH IS NULL THEN T2.FMLBH ELSE T1.FMLBH END FMLBH,T2.WCount FROM
// (
// SELECT FMLBH,COUNT(1) WCount FROM YX_DZJZ_JZML WHERE MLLX = 3 AND SFSC = 'N' GROUP BY FMLBH
// ) T2
// LEFT JOIN
// (
// SELECT FMLBH,MLBH FROM YX_DZJZ_JZML WHERE MLLX = 2 AND SFSC = 'N'
// ) T1
// ON (T1.MLBH = T2.FMLBH)
//) T3 ON (T1.MLBH = T3.FMLBH)");
strSql.Append(" WHERE T1.MLLX = 1");
strSql.Append(@" AND T1.SFSC = 'N'");
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(strWhere);
}
strSql.Append(" order by T1.MLSXH");
try
{
DataSet ds = DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
if (ds != null && ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataTable GetListGroupByJ(string strWhere, params object[] objValues)", "EDRS.OracleDAL.EDRS_Report", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获取所有下级部门的人员
/// </summary>
public DataSet GetListChildrenBmAll(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from xt_qx_ryjsfp where bmbm in(select bmbm from xt_zzjg_bmbm connect by prior bmbm=fbmbm start with ");
if (strWhere.Trim() != "")
{
strSql.Append(" 1=1 " + strWhere);
}
strSql.Append(" )");
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.XT_QX_RYJSFP", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 根据文件赛选案件
/// </summary>
/// <param name="strWhere"></param>
/// <param name="objValues"></param>
/// <returns></returns>
public DataTable GetListGroupByM(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT T1.BMSAH,T1.MLBH,T1.CJSJ,T1.ZHXGSJ,T1.MLXSMC,T1.SSLBBM,T1.SSLBMC,T1.MLLX,
(SELECT COUNT(1) FROM YX_DZJZ_JZML T2 WHERE T2.FMLBH = T1.MLBH) WCount");
strSql.Append(@" FROM YX_DZJZ_JZML T1");
strSql.Append(@" WHERE T1.SFSC = 'N'");
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(strWhere);
}
strSql.Append(" order by T1.MLSXH");
try
{
DataSet ds = DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
if (ds != null && ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataTable GetListGroupByM(string strWhere, params object[] objValues)", "EDRS.OracleDAL.EDRS_Report", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获取记录总数
/// </summary>
public int GetRecordCount(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) FROM XT_ZZJG_BMBM ");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
object obj = 0;
try
{
obj = DbHelperOra.GetSingle(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public int GetRecordCount(string strWhere, params object[] objValues)", "EDRS.OracleDAL.XT_ZZJG_BMBM", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
public DataTable GetMLList(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT distinct T1.BMSAH,MLBH,CJSJ,ZHXGSJ,MLXSMC,SSLBBM,SSLBMC,MLLX ");
strSql.Append(@" FROM YX_DZJZ_JZML T1 WHERE T1.SFSC = 'N'");
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(strWhere);
}
try
{
DataSet ds = DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
if (ds != null && ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataTable GetMLList(string strWhere, params object[] objValues)", "EDRS.OracleDAL.EDRS_Report", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetListfile(string strWhere, params object[] objValues)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select YX_DZJZ_LSGL.*,(select count(1) from yx_dzjz_lszzwj a where a.sfsc='N' and a.lszh=YX_DZJZ_LSGL.Lszh) as wjnum ");
strSql.Append(" from YX_DZJZ_LSGL ");
strSql.Append(" where YX_DZJZ_LSGL.SFSC='N' " + strWhere);
try
{
return(DbHelperOra.Query(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues)));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public DataSet GetListfile(string strWhere, params object[] objValues)", "EDRS.OracleDAL.YX_DZJZ_LSGL", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
return(null);
}
/// <summary>
/// 扩展获取记录总数
/// </summary>
public int GetRecordCountUnite(string strWhere, params object[] objValues)
{
//string tableName = "(select JZ.JZSCRXM,JZ.CJSJ,JZ.AJBH,JZ.WSBH,JZ.WSMC,JZ.ZZZT,AJMC,d.BMSAH,AJLB_MC,CBDW_MC,CBBM_MC,DQJD,SLRQ,AJZT,DQRQ,BJRQ,WCRQ,AJLB_BM,CBDW_BM,d.SFSC,XYR,SFZH,TARYXX,SHR,ZJS,DJJ,ZYS,JZPZ,JZSHRBH,JZSHR,JZSHSJ,JZBH from TYYW_GG_AJJBXX d JOIN tyyw_gg_ajjbxxkz dk on d.bmsah = dk.ahmc LEFT JOIN yx_dzjz_jzjbxx JZ ON (d.bmsah = JZ.BMSAH) where d.SFSC='N' and JZ.SFSC = 'N') T ";
string tableName = string.Format("(select JZ.JZSCRXM,JZ.CJSJ,d.CJSJ GGCJSJ,JZ.ZZZT,JZ.LAZZZT,d.BMSAH,AJLB_MC,CBDW_MC,CBBM_MC,d.CBR,DQJD, SLRQ,AJZT,DQRQ,BJRQ,WCRQ,dk.GDRQ,AJLB_BM,CBDW_BM,d.SFSC,SHR,ZJS,DJJ,ZYS,JZPZ,JZSHRBH,JZSHR,JZSHSJ,JZBH ,SMAJLA,SMAJCD");
tableName += ",dk.ay AJMC,dk.sajg,dk.yg,dk.bg,dk.yyr,dk.sqzxr,dk.bzxr,dk.sarq,dk.jarq,dk.cjjg,dk.zxbd,dk.sljg,dk.zxjg,dk.jafs,dk.gldaxlh,dk.hytcy,dk.sjy,dk.zcs ,dk.djc,dk.ys,dk.ywt,dk.bgqx ";
tableName += string.Format(" from TYYW_GG_AJJBXX d JOIN tyyw_gg_ajjbxxkz dk on d.bmsah = dk.ahmc LEFT JOIN yx_dzjz_jzjbxx JZ ON (d.bmsah = JZ.BMSAH) where d.SFSC='N' and JZ.SFSC = 'N')");
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("select count(1) FROM {0} ", tableName);
if (strWhere.Trim() != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
object obj = null;
try
{
obj = DbHelperOra.GetSingle(strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public int GetRecordCountUnite(string strWhere, params object[] objValues)", "EDRS.OracleDAL.TYYW_GG_AJJBXX_Ex", strSql.ToString(), ParameterHelp.ParameterReset(strWhere, objValues));
}
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
