public DataTable GetList(string userId, string controlId, string names)
{
string sql = @"
SELECT
phid,
userid,
controlid,
names,
lasttimes,
counts
FROM fg3_useful where userid={0} and controlid={1} and names LIKE '" + names + "%'";
IDataParameter[] p = new NGDataParameter[2];
p[0] = new NGDataParameter("userid", userId);
p[1] = new NGDataParameter("controlid", controlId);
string sortString = "counts desc,lasttimes desc";
int totalRecord = 0;
int pageSize = 10;
int pageIndex = 0;
string strSql = PaginationAdapter.GetPageDataSql(sql, pageSize, ref pageIndex, ref totalRecord, sortString, p);
return(DbHelper.GetDataTable(strSql, p));
}
public DataTable GetUserList(string connectString, int pageSize, int pageIndex, string searchkey, ref int totalRecord)
{
StringBuilder sql = new StringBuilder();
sql.Append("select phid,hrid,userno,username from fg3_user");
if (!string.IsNullOrEmpty(searchkey))
{
string[] keys = new string[] { };
if (!string.IsNullOrWhiteSpace(searchkey))
{
keys = Newtonsoft.Json.JsonConvert.DeserializeObject <string[]>(searchkey);
}
sql.Append(" where ");
for (int i = 0; i < keys.Length; i++)
{
if (i != 0)
{
sql.Append(" or ");
}
sql.Append(" userno like '%" + keys[i] + "%' or username like '%" + keys[i] + "%'");
}
}
string sortString = " phid asc";
string strSql = PaginationAdapter.GetPageDataSql(sql.ToString(), pageSize, ref pageIndex, ref totalRecord, sortString, null, connectString);
return(DbHelper.GetDataTable(connectString, strSql));
}
public DataTable GetIndividualRegList(string clientJson, int pageSize, int PageIndex, ref int totalRecord)
{
string sql = "select * from fg_individualinfo_reg";
string sortField = " bustype asc ";
if (!string.IsNullOrEmpty(clientJson))
{
string query = string.Empty;
IDataParameter[] p = DataConverterHelper.BuildQueryWithParam(clientJson, string.Empty, ref query);
if (!string.IsNullOrEmpty(query))
{
sql += " where " + query;
}
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref PageIndex, ref totalRecord, sortField, p);
return(DbHelper.GetDataTable(sqlstr, p));
}
else
{
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref PageIndex, ref totalRecord, sortField);
return(DbHelper.GetDataTable(sqlstr));
}
}
/// <summary>
/// 获取业务类型列表
/// </summary>
/// <param name="clientJson">客户端查询条件</param>
/// <param name="pageSize">页大小</param>
/// <param name="PageIndex">页号</param>
/// <param name="totalRecord">总记录数</param>
/// <returns></returns>
public DataTable GetBusTypeList(string clientJson, int pageSize, int PageIndex, ref int totalRecord)
{
string sql = "select c_code,sys_module,user_code,doc_title from c_pfc_register ";
string sortField = " c_code asc ";
if (!string.IsNullOrEmpty(clientJson))
{
string query = string.Empty;
IDataParameter[] p = DataConverterHelper.BuildQueryWithParam(clientJson, string.Empty, ref query);
if (!string.IsNullOrEmpty(query))
{
if (sql.IndexOf("where") > 0)
{
sql += " and " + query;
}
else
{
sql += " where " + query;
}
}
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref PageIndex, ref totalRecord, sortField, p);
return(DbHelper.GetDataTable(sqlstr, p));
}
else
{
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref PageIndex, ref totalRecord, sortField);
return(DbHelper.GetDataTable(sqlstr));
}
}
/// <summary>
/// 取二维码规则列表
/// </summary>
/// <param name="query"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="totalRecord"></param>
/// <returns></returns>
public DataTable GetList(string query, int pageSize, int pageIndex, ref int totalRecord)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT phid,code,content FROM fg3_qrcode_rule");
DataTable dt;
string sortString = "code asc";
if (!string.IsNullOrEmpty(query))
{
string q = string.Empty;
IDataParameter[] p = DataConverterHelper.BuildQueryWithParam(query, string.Empty, ref q);
if (!string.IsNullOrEmpty(q))
{
sql.Append(" where " + q);
}
string strSql = PaginationAdapter.GetPageDataSql(sql.ToString(), pageSize, ref pageIndex, ref totalRecord, sortString, p);
dt = DbHelper.GetDataTable(strSql, p);
}
else
{
string strSql = PaginationAdapter.GetPageDataSql(sql.ToString(), pageSize, ref pageIndex, ref totalRecord, sortString, null);
dt = DbHelper.GetDataTable(strSql);
}
return(dt);
}
public DataTable GetList(string clientJsonQuery, int pageSize, int pageIndex, ref int totalRecord)
{
string sql = "select * from fg_helpinfo_master";
string sortField = " helpid asc ";
DataTable dt;
if (!string.IsNullOrEmpty(clientJsonQuery))
{
string query = string.Empty;
IDataParameter[] p = DataConverterHelper.BuildQueryWithParam(clientJsonQuery, string.Empty, ref query);
if (!string.IsNullOrEmpty(query))
{
sql += " where " + query;
}
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref pageIndex, ref totalRecord, sortField, p);
dt = DbHelper.GetDataTable(sqlstr, p);
}
else
{
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref pageIndex, ref totalRecord, sortField);
dt = DbHelper.GetDataTable(sqlstr);
}
return(dt);
}
///// <summary>
///// 获取grid列表数据
///// </summary>
///// <param name="phid"></param>
///// <param name="pageSize"></param>
///// <param name="pageIndex"></param>
///// <param name="totalRecord"></param>
///// <param name="type"></param>
///// <returns></returns>
//public DataTable GetGridList(Int64 phid, int pageSize, int pageIndex, ref int totalRecord, string type)
//{
// try
// {
// string sortField = " phid asc ";
// StringBuilder strbuilder = new StringBuilder();
// DataTable dt = new DataTable();
// strbuilder.Append("SELECT a.phid, a.moduleid,a.range, a.option_group, a.option_code, a.option_name, a.option_value, a.option_type, a.isunify, a.scope, a.tips, a.createtime, a.createuser, a.modifytime, a.modifyuser, a.property, a.user_mod_flg, a.sysflg, a.control_type, b.username FROM fg3_option_detail a LEFT JOIN fg3_user b ON a.modifyuser = b.userno");
// if (type == "0")//初始化设置
// strbuilder.Append(" where moduleid=" + phid + " and property in ('2','3')");
// else//选项设置
// strbuilder.Append(" where moduleid=" + phid + " and property in ('1','3')");
// strbuilder.Append(" order by phid asc");
// string sqlstr = PaginationAdapter.GetPageDataSql(strbuilder.ToString(), pageSize, ref pageIndex, ref totalRecord, sortField);
// dt = DbHelper.GetDataTable(strbuilder.ToString());
// DataTable dtOption = new DataTable();
// if (dt != null && dt.Rows.Count > 0)
// {
// DataColumn dc = new DataColumn("option_value_name", typeof(string));
// dt.Columns.Add(dc);
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// if (dt.Rows[i]["modifyuser"] != DBNull.Value && dt.Rows[i]["modifyuser"].ToString() != "")
// {
// if (dt.Rows[i]["username"] == DBNull.Value || dt.Rows[i]["username"].ToString() == "")
// {
// dt.Rows[i]["username"] = "******";
// }
// }
// if (dt.Rows[i]["control_type"].ToString() == "0")
// {
// dtOption = GetOptionValue(Convert.ToInt64(dt.Rows[i]["phid"]));
// DataRow dr = dtOption.Select("option_code='" + dt.Rows[i]["option_value"].ToString() + "'").FirstOrDefault();
// if (dr != null)
// {
// dt.Rows[i]["option_value_name"] = dr["option_name"];
// }
// }
// else
// {
// dt.Rows[i]["option_value_name"] = dt.Rows[i]["option_value"];
// }
// }
// }
// return dt;
// }
// catch (Exception ex)
// {
// throw new Exception(ex.Message);
// }
//}
/// <summary>
/// 获取grid列表数据
/// </summary>
/// <param name="phid"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="totalRecord"></param>
/// <param name="type"></param>
/// <returns></returns>
public DataTable GetGridList(Int64 phid, int pageSize, int pageIndex, ref int totalRecord, string type)
{
try
{
string sortField = " phid asc ";
StringBuilder strbuilder = new StringBuilder();
DataTable dt = new DataTable();
strbuilder.Append("SELECT c.phid, c.moduleid,c.range, c.option_group, c.option_code, c.option_name, c.option_value, c.option_type, c.isunify, ");
strbuilder.Append(" c.scope, c.tips, c.createtime,c.option_value_name,c.createuser, c.modifytime, c.modifyuser,c.property,c.control_type, d.username ");
strbuilder.Append(" FROM (SELECT a.phid, a.moduleid,a.range, a.option_group, a.option_code, a.option_name, a.option_value, a.option_type, a.isunify, ");
strbuilder.Append(" a.scope, a.tips, a.createtime, a.createuser, a.modifytime, a.modifyuser, a.property, a.user_mod_flg, a.sysflg, ");
strbuilder.Append(" a.control_type,b.OPTION_NAME AS option_value_name FROM FG3_OPTION_DETAIL a LEFT JOIN fg3_option_value b ON a.PHID=b.DETAIL_PHID ");
strbuilder.Append(" AND a.OPTION_VALUE=b.OPTION_CODE) c LEFT JOIN FG3_USER d ON c.modifyuser=d.USERNO ");
if (type == "0")//初始化设置
{
strbuilder.Append(" where moduleid=" + phid + " and property in ('2','3')");
}
else//选项设置
{
strbuilder.Append(" where moduleid=" + phid + " and property in ('1','3')");
}
strbuilder.Append(" order by phid asc");
string sqlstr = PaginationAdapter.GetPageDataSql(strbuilder.ToString(), pageSize, ref pageIndex, ref totalRecord, sortField);
dt = DbHelper.GetDataTable(strbuilder.ToString());
DataTable dtOption = new DataTable();
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["modifyuser"] != DBNull.Value && dt.Rows[i]["modifyuser"].ToString() != "")
{
if (dt.Rows[i]["username"] == DBNull.Value || dt.Rows[i]["username"].ToString() == "")
{
dt.Rows[i]["username"] = "******";
}
}
if (dt.Rows[i]["control_type"].ToString() == "1")
{
dt.Rows[i]["option_value_name"] = dt.Rows[i]["option_value"];
}
}
}
return(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 获取纳税组织grid列表数据
/// </summary>
/// <returns></returns>
public DataTable GetTaxOrgGrid(string phid)
{
try
{
StringBuilder strbuilder = new StringBuilder();
DataTable dt = new DataTable();
string sortField = " phid asc ";
strbuilder.Append("SELECT a.phid id, a.ocode,a.oname name,b.phid,b.detail_phid,b.type,b.argument from fg_orglist a left join ");
strbuilder.Append("(SELECT * FROM fg3_argument_setting WHERE detail_phid=" + Convert.ToInt64(phid) + ") b on a.phid = b.id ");
strbuilder.Append(" where a.isactive = '1' and a.ifcorp = 'Y' and a.is_ratepay = '1'");
int pageIndex = 0;
int totalRecord = 0;
string sqlstr = PaginationAdapter.GetPageDataSql(strbuilder.ToString(), 20, ref pageIndex, ref totalRecord, sortField);
dt = DbHelper.GetDataTable(strbuilder.ToString());
DataTable dtOption = new DataTable();
if (dt != null && dt.Rows.Count > 0)
{
DataColumn dc = new DataColumn("option_value_name", typeof(string));
dt.Columns.Add(dc);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["detail_phid"] != DBNull.Value)
{
dtOption = GetOptionValue(Convert.ToInt64(dt.Rows[i]["detail_phid"]));
DataRow dr = dtOption.Select("option_code='" + dt.Rows[i]["argument"].ToString() + "'").FirstOrDefault();
if (dr != null)
{
dt.Rows[i]["argument"] = dr["option_code"];
dt.Rows[i]["option_value_name"] = dr["option_name"];
}
}
}
}
return(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 获取grid列表数据
/// </summary>
/// <param name="phid"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="totalRecord"></param>
/// <returns></returns>
public DataTable GetShortcutMenuList(Int64 phid, int pageSize, int pageIndex, ref int totalRecord)
{
try
{
long userid = NG3.AppInfoBase.UserID;
string sortField = " seq asc ";
StringBuilder strbuilder = new StringBuilder();
DataTable dt = new DataTable();
strbuilder.Append("select * from fg3_shortcutmenu");
strbuilder.Append(" where userid=" + userid + "");
string sqlstr = PaginationAdapter.GetPageDataSql(strbuilder.ToString(), pageSize, ref pageIndex, ref totalRecord, sortField);
dt = DbHelper.GetDataTable(sqlstr);
DataTable dtOption = new DataTable();
return(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 返回套打列表数据
/// </summary>
/// <param name="clientJson"></param>
/// <param name="pageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="totalRecord"></param>
/// <returns></returns>
public DataTable GetLFormList(DataTable dtModules, string clientJson, int pageSize, int pageIndex, ref int totalRecord)
{
string sql = "select printid,typefile,billname,moduleno,filename,dateflg,def_int2,remarks,previeweditflg, def_int1,ispub,hide from printfm where " + GetDefaultSqlFilter(dtModules);
string sortField = " order by moduleno,printid";
if (!string.IsNullOrEmpty(clientJson))
{
string query = string.Empty;
IDataParameter[] p = DataConverterHelper.BuildQueryWithParam(clientJson, ref query);
if (!string.IsNullOrEmpty(query))
{
sql += " and " + query;
}
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref pageIndex, ref totalRecord, sortField, p, PubConnectString);
return(DbHelper.GetDataTable(PubConnectString, sqlstr, p));
}
else
{
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref pageIndex, ref totalRecord, sortField, null, PubConnectString);
return(DbHelper.GetDataTable(PubConnectString, sqlstr));
}
}
/// <summary>
/// 获取表注册列表
/// </summary>
/// <param name="clientJson"></param>
/// <param name="pageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="totalRecord"></param>
/// <returns></returns>
public DataTable GetTableRegList(string clientJson, int pageSize, int PageIndex, ref int totalRecord)
{
//string sql = "select * from fg_table_reg";
//string sql = " select fg_table_reg.*, fg_individialinfo_reg.busname from fg_table_reg,fg_individialinfo_reg "
// + " where fg_table_reg.individualreg_code = fg_individialinfo_reg.code ";
string sql = " select c_code,c_name,chn from fg_table ";
string sortField = " c_code asc ";
if (!string.IsNullOrEmpty(clientJson))
{
string query = string.Empty;
IDataParameter[] p = DataConverterHelper.BuildQueryWithParam(clientJson, string.Empty, ref query);
if (!string.IsNullOrEmpty(query))
{
if (sql.IndexOf("where") > 0)
{
sql += " and " + query;
}
else
{
sql += " where " + query;
}
}
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref PageIndex, ref totalRecord, sortField, p);
return(DbHelper.GetDataTable(sqlstr, p));
}
else
{
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref PageIndex, ref totalRecord, sortField);
return(DbHelper.GetDataTable(sqlstr));
}
}
/// <summary>
/// 获取员工数据列表
/// </summary>
/// <param name="sqlFilter"></param>
/// <param name="pageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="totalRecord"></param>
/// <param name="oCode"></param>
/// <param name="leaf"></param>
/// <param name="relatIndex"></param>
/// <param name="isRgt">是否需要组织查询权限</param>
/// <returns></returns>
public DataTable GetEmpList(string sqlFilter, int pageSize, int PageIndex, ref int totalRecord, string oCode, bool leaf, string relatIndex, bool isRgt = false)
{
string isNvl = DbHelper.Vendor == DbVendor.Oracle ? "nvl" : "isnull";
string sql = "select hr_epm_main.cno,hr_epm_main.cname,'1' ctype," + isNvl + "(hr_epm_station.dept,hr_epm_main.dept) deptno,hr_epm_station.assigntype "
+ "from hr_epm_main LEFT JOIN hr_epm_station ON hr_epm_station.ccode=hr_epm_main.ccode AND systype='0'";
string sortField = "order by cno";
if (!string.IsNullOrEmpty(sqlFilter))
{
sql += " where " + sqlFilter;
}
if (!string.IsNullOrEmpty(oCode))
{
string tmpWhere = string.Empty;;
if (leaf) //叶子节点
{
tmpWhere = string.Format("deptno='{0}'", oCode);
}
else
{
string relatId = GetHrRelatCode();
tmpWhere = string.Format("deptno in (select ocode from fg_orgrelatitem where fg_orgrelatitem.relatid ='{0}' and relatindex like '{1}%')", relatId, relatIndex);
}
sql = string.Format("select * from ({0}) tmpTable where {1}", sql, tmpWhere);
}
else
{
sql = string.Format("select * from ({0}) tmpTable", sql);
}
if (isRgt)
{
//增加组织查询权限
}
sql = PaginationAdapter.GetPageDataSql(sql, pageSize, ref PageIndex, ref totalRecord, sortField);
return(DbHelper.GetDataTable(sql));
}
/// <summary>
/// 获取列表数据
/// </summary>
/// <param name="helpid">帮助标记</param>
/// <param name="clientQuery">客户端查询条件</param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="totalRecord"></param>
/// <param name="clientSqlFilter">客户端传来的sql查询条件</param>
/// <param name="isAutoComplete">联想搜索</param>
/// <returns></returns>
public DataTable GetList(string helpid, int pageSize, int pageIndex, ref int totalRecord, string clientQuery, string outJsonQuery, string leftLikeJsonQuery, string clientSqlFilter, bool isAutoComplete, bool ormMode)
{
DataTable dt = null;
CommonHelpEntity item = ormMode ? this.GetHelpItem(helpid) : this.GetCommonHelpItem(helpid);
//拼装sql语句方式
if (item.Mode == HelpMode.Default)
{
StringBuilder sql = new StringBuilder();
if (item.AllField.Length > 0)
{
sql.Append("select " + item.Distinct + item.AllField)
.Append(" from " + item.TableName)
.Append(" where ");
}
else
{
sql.Append("select " + item.Distinct + item.CodeField + " " + item.CodeProperty + " , "
+ item.NameField + " " + item.NameProperty)
.Append(" from " + item.TableName)
.Append(" where ");
}
#region where
if (!string.IsNullOrEmpty(item.SqlFilter))
{
sql.Append(item.SqlFilter);
}
else
{
sql.Append(" 1=1 ");
}
if (!string.IsNullOrEmpty(clientSqlFilter))
{
sql.Append(" and " + clientSqlFilter);
}
//if(!string.IsNullOrEmpty(clientQuery))
//{
// sql.Append(" and " + clientQuery);
//}
#endregion
#region 分页
string sortString = string.Empty;
if (!string.IsNullOrEmpty(item.SortField))
{
sortString = item.SortField.Trim().IndexOf(" ") > 0 ? item.SortField : item.SortField + " asc ";
}
else
{
sortString = item.CodeField + " asc ";
}
if (string.IsNullOrEmpty(clientQuery) && string.IsNullOrEmpty(outJsonQuery))
{
string strSql = PaginationAdapter.GetPageDataSql(sql.ToString(), pageSize, ref pageIndex, ref totalRecord, sortString, null);
dt = DbHelper.GetDataTable(strSql);
}
else
{
string query = string.Empty;
IDataParameter[] p = null;
if (isAutoComplete)
{
p = this.BuildInputQuery(helpid, clientQuery, item.PYField, outJsonQuery, leftLikeJsonQuery, ref query);
}
else
{
p = DataConverterHelper.BuildQueryWithParam(clientQuery, outJsonQuery, leftLikeJsonQuery, ref query);
}
if (!string.IsNullOrEmpty(query))
{
sql.Append(" and " + query);
}
string strSql = PaginationAdapter.GetPageDataSql(sql.ToString(), pageSize, ref pageIndex, ref totalRecord, sortString, p);
dt = DbHelper.GetDataTable(strSql, p);
}
#endregion
}
else if (item.Mode == HelpMode.GetHelpResult)//插件模式通过反射获取帮助列表
{
string fullpath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "I6Rules" + Path.DirectorySeparatorChar + item.Assembly);
if (!File.Exists(fullpath))
{
fullpath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Rules" + Path.DirectorySeparatorChar + item.Assembly);
}
if (File.Exists(fullpath))
{
Assembly assem = Assembly.LoadFile(fullpath);
object instance = assem.CreateInstance(item.ClassName);
ICommonHelp help = instance as ICommonHelp;
if (help != null)
{
dt = help.GetHelpList(pageSize, pageIndex, ref totalRecord, clientQuery, outJsonQuery, isAutoComplete);
}
else
{
throw new Exception(string.Format("程序集{0}中的{1}类未实现SUP.Common.Base.ICommonHelp接口", item.Assembly, item.ClassName));
}
}
else
{
throw new Exception(string.Format("服务端Rules目录下找不到程序集{0}", item.Assembly));
}
}
if (ormMode)
{
dt.TableName = item.TableName;
return(ConvertFieldColToPropertyCol(dt, item.FieldPropertyDic, item.FieldDic));
}
return(dt);
}
/// <summary>
/// 获得help数据列表;
/// </summary>
/// <param name="helpId">帮助id;</param>
/// <param name="pageSize">分页大小;</param>
/// <param name="pageIndex">页码;</param>
/// <param name="totalRecord">总记录长度;</param>
/// <param name="clientFilter">过滤条件;</param>
/// <returns></returns>
public DataTable GetHelpList(string helpId, int pageSize, int pageIndex, ref int totalRecord, string clientFilter, bool isAutoComplete)
{
CustomHelpEntity item = GetHelpItem(helpId);
DataTable dt;
/*如果没有sql则返回;*/
if (item.FromSql != "1")
{
return(new DataTable());
}
string sortField = string.Empty;
string sql = item.Sql;
int n = sql.ToUpper().IndexOf("ORDER BY");
if (n > 0)
{
sortField = sql.Substring(n);
sql = sql.Substring(0, n - 1);
}
else
{
sortField = item.CodeField + " asc";
}
if (!string.IsNullOrEmpty(clientFilter))
{
IDataParameter[] p;
if (isAutoComplete)
{
string query = string.Empty;
p = BuildInputQuery(helpId, clientFilter, string.Empty, string.Empty, ref query);
if (!string.IsNullOrEmpty(query))
{
sql += " where " + query;
}
}
else
{
string query = string.Empty;
p = DataConverterHelper.BuildQueryWithParam(clientFilter, string.Empty, ref query);
if (!string.IsNullOrEmpty(query))
{
sql += " where " + query;
}
}
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref pageIndex, ref totalRecord, sortField, p);
dt = DbHelper.GetDataTable(sqlstr, p);
}
else
{
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref pageIndex, ref totalRecord, sortField);
dt = DbHelper.GetDataTable(sqlstr);
}
return(dt);
}
/// <summary>
/// 获取用户列表
/// </summary>
/// <param name="sqlString"></param>
/// <param name="pageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="totalRecord"></param>
/// <returns></returns>
public DataTable GetUserList(string sqlString, int pageSize, int PageIndex, ref int totalRecord)
{
string tmpSql = PaginationAdapter.GetPageDataSql(sqlString, pageSize, ref PageIndex, ref totalRecord, "order by cno");
return(DbHelper.GetDataTable(tmpSql));
}
/// <summary>
/// 获得help数据列表;
/// </summary>
/// <param name="helpId">帮助id;</param>
/// <param name="pageSize">分页大小;</param>
/// <param name="pageIndex">页码;</param>
/// <param name="totalRecord">总记录长度;</param>
/// <param name="clientFilter">过滤条件;</param>
/// <returns></returns>
public DataTable GetHelpList(string helpId, int pageSize, int pageIndex, ref int totalRecord, string clientFilter, bool isAutoComplete, string outJsonQuery)
{
HelpEntity item = GetHelpItem(helpId);
DataTable dt = null;
/*如果没有sql则返回;*/
if (item.FromSql == "1")
{
string sortField = string.Empty;
string sql = item.Sql;
//填充“:参数”
if (sql.IndexOf(":") > 0)
{
if (!string.IsNullOrEmpty(outJsonQuery))
{
JObject jo = JObject.Parse(outJsonQuery.ToString());
foreach (var it in jo)
{
if (string.IsNullOrWhiteSpace(it.Value.ToString()))
{
sql = sql.Replace(":" + it.Key.ToString(), "''");
}
else
{
sql = sql.Replace(":" + it.Key.ToString(), it.Value.ToString());
}
}
}
}
int n = sql.ToUpper().IndexOf("ORDER BY", StringComparison.OrdinalIgnoreCase);
if (n > 0)
{
sortField = sql.Substring(n);
sql = sql.Substring(0, n - 1);
}
else
{
sortField = item.CodeField + " asc";
}
if (!string.IsNullOrEmpty(clientFilter))
{
IDataParameter[] p;
string joinStr = string.Empty;
//sql中有无where
if (sql.IndexOf("where", StringComparison.OrdinalIgnoreCase) > 0)
{
joinStr = " and ";
}
else
{
joinStr = " where ";
}
if (isAutoComplete)
{
string query = string.Empty;
p = BuildInputQuery(helpId, clientFilter, string.Empty, string.Empty, ref query);
if (!string.IsNullOrEmpty(query))
{
sql += joinStr + query;
}
}
else
{
string query = string.Empty;
p = DataConverterHelper.BuildQueryWithParam(clientFilter, string.Empty, ref query);
if (!string.IsNullOrEmpty(query))
{
sql += joinStr + query;
}
}
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref pageIndex, ref totalRecord, sortField, p);
dt = DbHelper.GetDataTable(sqlstr, p);
}
else
{
string sqlstr = PaginationAdapter.GetPageDataSql(sql, pageSize, ref pageIndex, ref totalRecord, sortField);
dt = DbHelper.GetDataTable(sqlstr);
}
}
else
{
dt = Common.GetHelpInfo(item.HelpId);
}
return(dt);
}
