public static DapperResult CommonSqlPage(string sqlid, IIdentity _identity, int startpage, int limit, string orderby)
{
var result = string.Empty;
var dar = new DapperResult();
var _sql = $@"
SELECT sqlcontent
FROM TBL_T_DataSql a
LEFT JOIN tb_SQL b ON a.sqlid = b.NewSqlID
WHERE
CAST(a.sqlid AS NVARCHAR(50)) = '{sqlid}'
OR b.sqlid = '{sqlid}'";
List <Dictionary <string, object> > sqls = QueryNormal(_sql, false).rows;
if (sqls.Count == 0)
{
dar.rows = null;
dar.total = 0;
dar.msg = "Querynodata";
dar.success = 0;
return(dar);
}
string sql = sqls[0]["sqlcontent"].ToString();
sql = sql.Replace("@RenYuanId", CharacterUtil.SQLEncode(ExtendIdentity.GetUserId(_identity))).Replace("@CompanyCode", CharacterUtil.SQLEncode(ExtendIdentity.GetOrganizationId(_identity)));
return(QueryPage(sql, startpage, limit, orderby));
}
/// <summary>
/// 对指定列名使用IN表达式
/// </summary>
/// <param name="_identity"></param>
/// <param name="xml"></param>
/// <param name="sql"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static DapperResult searchInSpecifiedColunm(IIdentity _identity, string xml, string sql, string columnName)
{
var executeSql = string.Empty;
var parameterValue = "'";
var ht = Utility.HtFromPage(_identity);
var valueList = ht[columnName]?.ToString().Replace("'[", "").Replace("]'", "").Replace("\r\n", "").Replace(" ", "").Split(',');
if (valueList == null || valueList.Count() == 0)
{
ht.Remove($"{columnName}_Off");
ht.Add($"{columnName}_Off", "--");
}
else
{
ht.Remove($"{columnName}_Off");
ht.Add($"{columnName}_Off", string.Empty);
foreach (var value in valueList)
{
if (string.IsNullOrEmpty(value))
{
continue;
}
parameterValue += value + "','";
}
ht.Remove(columnName);
ht.Add(columnName, parameterValue.Substring(0, parameterValue.Length - 2));
}
ht.Remove("CompanyCode");
var orgId = ExtendIdentity.GetOrganizationId(_identity);
ht.Add("CompanyCode", CharacterUtil.SQLEncode(orgId));
executeSql = SQLLoaderComponent.GetSQLQuery(xml, sql, ht);
var dar = DapperContext.QueryPage(executeSql, int.Parse(ht["offset"].ToString().Replace("'", "")), int.Parse(ht["limit"].ToString().Replace("'", "")));
return(dar);
}
public static DapperResult CommonSql(string sqlid, IIdentity _identity)
{
var result = string.Empty;
var dar = new DapperResult();
var _sql = string.Format(@"
SELECT sqlcontent
FROM TBL_T_DataSql a
LEFT JOIN tb_SQL b ON a.sqlid = b.NewSqlID
WHERE
CAST(a.sqlid AS NVARCHAR(50)) = '{0}'
OR b.sqlid = '{0}'", sqlid);
var dr = QueryNormal(_sql, false);
if (dr == null || dr.success == 0 || dr.rows == null)
{
dar.rows = null;
dar.total = 0;
dar.msg = "Querynodata";
dar.success = 0;
return(dar);
}
List <Dictionary <string, object> > sqls = dr.rows;
if (sqls.Count == 0)
{
dar.rows = null;
dar.total = 0;
dar.msg = "Querynodata";
dar.success = 0;
return(dar);
}
string sql = sqls[0]["sqlcontent"].ToString();
sql = sql.Replace("@RenYuanId", CharacterUtil.SQLEncode(ExtendIdentity.GetUserId(_identity))).Replace("@CompanyCode", CharacterUtil.SQLEncode(ExtendIdentity.GetOrganizationId(_identity)));
// sql = ControlNPrefix(sql);
return(QueryNormal(sql, false));
}
