/// <summary>
/// 加载SSPC-CRM客户-商务人员对应数据
/// </summary>
/// <returns></returns>
private List <CRM_UserCustomer> InitSSPC_CRM_UserCustomers()
{
string sql = SQLLoaderComponent.GetSQLQuery("SyncCRM", "InitSSPC_CRM_UserCustomers", GetCRMHashTable());
var list = _db.Query <CRM_UserCustomer>(sql);
return(list.ToList());
}
public static DataTable searchInAllRecord(IIdentity _identity, string xml, string sql)
{
string[] sqls = { };
string lists = "";
var list = Utility.RecordFromPage(_identity);
Hashtable ht = new Hashtable();
int i = 0;
string RecordIDs = "'";
foreach (var id in list)
{
if (string.IsNullOrEmpty(id))
{
continue;
}
RecordIDs += id + "','";
}
ht.Remove("RecordID");
ht.Add("RecordID", RecordIDs.Substring(0, RecordIDs.Length - 2));
lists = SQLLoaderComponent.GetSQLQuery(xml, sql, ht);
var dar = DapperContext.QueryDataTable(lists);
return(dar);
}
/// <summary>
/// 加载0622商务人员提供的excel数据
/// </summary>
/// <returns></returns>
private List <T_ExcelCustomer20180622> InitSSPC_EOrder_KehuExcels()
{
string sql = SQLLoaderComponent.GetSQLQuery("SyncCRM", "InitSSPC_EOrder_KehuExcels", GetSSPCHashTable());
var list = _db.Query <T_ExcelCustomer20180622>(sql);
return(list.ToList());
}
/// <summary>
/// 加载SSPC-EOrder客户SHIP数据
/// </summary>
/// <returns></returns>
private List <tbl_eorder_cusship> InitSSPC_EOrder_CusShips()
{
string sql = SQLLoaderComponent.GetSQLQuery("SyncCRM", "InitSSPC_EOrder_CusShips", GetSSPCHashTable());
var list = _db.Query <tbl_eorder_cusship>(sql);
return(list.ToList());
}
/// <summary>
/// 加载系统中的所有人员信息
/// </summary>
/// <returns></returns>
private List <mstrenyuan> InitSSPC_EOrder_Renyuans()
{
string sql = SQLLoaderComponent.GetSQLQuery("SyncCRM", "InitSSPC_EOrder_Renyuans", GetSSPCHashTable());
var list = _db.Query <mstrenyuan>(sql);
return(list.ToList());
}
/// <summary>
/// 加载SSPC-EOrder客户-商务人员对应数据
/// </summary>
/// <returns></returns>
private List <tbl_eorder_usercustomer> InitSSPC_EOrder_UserCustomers()
{
string sql = SQLLoaderComponent.GetSQLQuery("SyncCRM", "InitSSPC_EOrder_UserCustomers", GetSSPCHashTable());
var list = _db.Query <tbl_eorder_usercustomer>(sql);
return(list.ToList());
}
/// <summary>
///加载SSPC-CRM客户产品数据
/// </summary>
/// <returns></returns>
private List <tbl_eorder_list> InitSSPC_CRM_CusProducts()
{
string sql = SQLLoaderComponent.GetSQLQuery("SyncCRM", "InitSSPC_CRM_CusProducts", GetCRMHashTable());
var list = _db.Query <tbl_eorder_list>(sql);
return(list.ToList());
}
/// <summary>
///加载SSPC-CRM客户数据
/// </summary>
/// <returns></returns>
private List <tbl_eorder_cussale> InitSSPC_CRM_CUSTOMERS()
{
string sql = SQLLoaderComponent.GetSQLQuery("SyncCRM", "InitSSPC_CRM_CUSTOMERS", GetCRMHashTable());
var list = _db.Query <tbl_eorder_cussale>(sql);
return(list.ToList());
}
/// <summary>
/// 加载SSPC-CRM客户产品价格数据
/// </summary>
/// <returns></returns>
private List <tbl_eorder_price> InitSSPC_EOrder_CusProductPrices()
{
string sql = SQLLoaderComponent.GetSQLQuery("SyncCRM", "InitSSPC_EOrder_CusProductPrices", GetSSPCHashTable());
var list = _db.Query <tbl_eorder_price>(sql);
return(list.ToList());
}
public static DapperResult SeachResult(IIdentity _identity, string xml, string sql)
{
Hashtable ht = Utility.HtFromPage(_identity);
var esql = SQLLoaderComponent.GetSQLQuery(xml, sql, ht);
var dar = DapperContext.QueryNormal(esql);
dar.msg = esql;
return(dar);
}
public static DapperResult ExcuteResult(IIdentity _identity, string xml, string sql)
{
Hashtable ht = new Hashtable();
ht = Utility.HtFromPage(_identity);
//ht.Add("CompanyCode", CharacterUtil.SQLEncode(orgId));
var esql = SQLLoaderComponent.GetSQLQuery(xml, sql, ht);
var dar = DapperContext.Execute(esql);
return(dar);
}
/// <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 PageResult(string aa, string xml, string sql)
{
//Hashtable ht = Utility.HtFromPage(_identity);
Hashtable ht = new Hashtable();
var esql = SQLLoaderComponent.GetSQLQuery(xml, sql, ht);
var dar = new DapperResult();
if (ht["offset"] == null)
{
dar = DapperContext.QueryNormal(esql);
}
else
{
dar = DapperContext.QueryPage(esql, int.Parse(ht["offset"].ToString().Replace("'", "")), int.Parse(ht["limit"].ToString().Replace("'", "")));
}
dar.msg = esql;
return(dar);
}
public static DapperResult QueryPageToContract(IIdentity _identity, string xml, string sql)
{
Hashtable ht = new Hashtable();
ht = Utility.HtFromPage(_identity);
//ht.Add("CompanyCode", CharacterUtil.SQLEncode(orgId));
var esql = SQLLoaderComponent.GetSQLQuery(xml, sql, ht);
var dar = new DapperResult();
if (ht["offset"] == null)
{
dar = DapperContext.QueryNormal(esql);
}
else
{
dar = DapperContext.QueryPageToContract(esql, int.Parse(ht["offset"].ToString().Replace("'", "")), int.Parse(ht["limit"].ToString().Replace("'", "")));
}
return(dar);
}
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 SaveResult(IIdentity _identity, string xml, string sqlId = "")
{
if (string.IsNullOrEmpty(sqlId))
{
sqlId = xml;
}
Hashtable ht = Utility.HtFromPage(_identity);
if (ht["RecordID"] == null || string.IsNullOrEmpty(ht["RecordID"].ToString()))
{
sqlId += "_Insert";
}
else
{
sqlId += "_Update";
}
var sql = SQLLoaderComponent.GetSQLQuery(xml, sqlId, ht);
var dar = DapperContext.QueryNormal(sql);
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);
}