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));
}
public static DapperResult SaveResultForDiscountForBFP(IIdentity _identity, string xml, string sql, string Op)
{
string[] sqls = { };
List <string> lists = new List <string>();
Hashtable ht = Utility.HtFromPage(_identity);
string jsonText = Newtonsoft.Json.Linq.JObject.Parse(Utility.HtFromPageForDiscount(_identity))["RecordIDList"].ToString();
var mJObj = Newtonsoft.Json.Linq.JArray.Parse(jsonText);
if (!string.IsNullOrEmpty(ht["OrderId"].ToString()))
{
Hashtable htd = new Hashtable();
htd.Add("OrderId", CharacterUtil.SQLEncode(ht["OrderId"].ToString()));
htd.Add("OrderProductId", CharacterUtil.SQLEncode(ht["OrderProductId"].ToString()));
lists.Add(SQLLoaderComponent.GetSQLQuery(xml, sql + "_Delete", ht));
}
for (int i = 0; i < mJObj.Count; i++)
{
Newtonsoft.Json.Linq.JObject oData = Newtonsoft.Json.Linq.JObject.Parse(mJObj[i].ToString());
ht.Remove("ApplyNo");
ht.Add("ApplyNo", CharacterUtil.SQLEncode(oData["ApplyNo"].ToString()));
ht.Remove("OrderProductId");
ht.Add("OrderProductId", CharacterUtil.SQLEncode(oData["OrderProductId"].ToString()));
ht.Remove("OrderId");
ht.Add("OrderId", CharacterUtil.SQLEncode(oData["OrderId"].ToString()));
ht.Remove("ProductCode");
ht.Add("ProductCode", CharacterUtil.SQLEncode(oData["ProductCode"].ToString()));
ht.Remove("SerSalesDiscountAttachId");
ht.Add("SerSalesDiscountAttachId", CharacterUtil.SQLEncode(oData["SerSalesDiscountAttachId"].ToString()));
ht.Remove("UseAmount");
ht.Add("UseAmount", CharacterUtil.SQLEncode(oData["UseAmount"].ToString()));
ht.Remove("ActualAmount");
ht.Add("ActualAmount", CharacterUtil.SQLEncode(oData["ActualAmount"].ToString()));
if (Op == "S")
{
lists.Add(SQLLoaderComponent.GetSQLQuery(xml, sql + "_Insert", ht));
}
}
var dar = DapperContext.ExecuteTrans(lists);
return(dar);
}
public static DapperResult DeleteResultForRole(IIdentity _identity, string xml, string sql)
{
string[] sqls = { };
List <string> lists = new List <string>();
var list = Utility.GetRecordIDList(_identity);
Hashtable ht = Utility.HtFromPage(_identity);
int i = 0;
foreach (var id in list)
{
if (string.IsNullOrEmpty(id))
{
continue;
}
ht.Remove("RecordID");
ht.Add("RecordID", CharacterUtil.SQLEncode(id));
lists.Add(SQLLoaderComponent.GetSQLQuery(xml, sql, ht));
i++;
}
var dar = DapperContext.ExecuteTrans(lists);
return(dar);
}
public static DapperResult SaveResultForSigleProduct(IIdentity _identity, string xml, string sql)
{
string[] sqls = { };
List <string> lists = new List <string>();
Hashtable ht = Utility.HtFromPage(_identity);
string jsonText = Newtonsoft.Json.Linq.JObject.Parse(Utility.HtFromPageForDiscount(_identity))["RecordIDList"].ToString();
var mJObj = Newtonsoft.Json.Linq.JArray.Parse(jsonText);
for (int i = 0; i < mJObj.Count; i++)
{
Newtonsoft.Json.Linq.JObject oData = Newtonsoft.Json.Linq.JObject.Parse(mJObj[i].ToString());
ht.Remove("OrderProductId");
ht.Add("OrderProductId", CharacterUtil.SQLEncode(oData["OrderProductId"].ToString()));
ht.Remove("OrderId");
ht.Add("OrderId", CharacterUtil.SQLEncode(oData["OrderId"].ToString()));
if (i == 0)
{
lists.Add(SQLLoaderComponent.GetSQLQuery(xml, sql + "_Update", ht));
}
}
var dar = DapperContext.ExecuteTrans(lists);
return(dar);
}