/// <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));
}
}
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="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 GetQuerySysMaintainCallDt(string clientJsonQuery)
{
string sql = "select * from fg3_sysmaintaincall";
if (!string.IsNullOrEmpty(clientJsonQuery))
{
string query = string.Empty;
clientJsonQuery = clientJsonQuery.Replace("\"state*str*eq*1\":\"1\"", "\"state*str*eq*1\":\"新增\"")
.Replace("\"state*str*eq*1\":\"2\"", "\"state*str*eq*1\":\"维护开始\"")
.Replace("\"state*str*eq*1\":\"3\"", "\"state*str*eq*1\":\"维护中\"")
.Replace("\"state*str*eq*1\":\"4\"", "\"state*str*eq*1\":\"维护完成\"");
IDataParameter[] p = DataConverterHelper.BuildQueryWithParam(clientJsonQuery, string.Empty, ref query);
if (!string.IsNullOrEmpty(query))
{
sql += " where " + query;
}
sql += " order by filldate desc";
return(DbHelper.GetDataTable(NG3.AppInfoBase.PubConnectString, sql, p));
}
else
{
sql += " order by filldate desc";
return(DbHelper.GetDataTable(NG3.AppInfoBase.PubConnectString, sql));
}
}
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);
}
public DataTable GetQueryHrRightApplyDt(string clientJsonQuery)
{
string sql = "select * from fg3_hrrightapply";
if (!string.IsNullOrEmpty(clientJsonQuery))
{
string query = string.Empty;
IDataParameter[] p = DataConverterHelper.BuildQueryWithParam(clientJsonQuery, string.Empty, ref query);
if (!string.IsNullOrEmpty(query))
{
sql += " where " + query;
}
sql += " order by filldate desc";
return(DbHelper.GetDataTable(sql, p));
}
else
{
sql += " order by filldate desc";
return(DbHelper.GetDataTable(sql));
}
}
/// <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="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>
/// 获得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);
}
