/// <summary>
/// 直接执行union query操作，并返回结果
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <param name="order_by"></param>
/// <param name="is_all"></param>
/// <param name="express"></param>
/// <returns></returns>
public static UnitDataCollection UnionQuery <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up, string order_by, bool is_all = false, params LinqDLR2Sql <TSource>[] express)
{
var sql = source.ToSql();
var data = source.SQLGenerator.ConditionValues;
if (express != null)
{
foreach (var e in express)
{
sql += $@" {(is_all?"union all":"union")}
{e.ToSql()}";
foreach (var item in e.SQLGenerator.ConditionValues)
{
data.Add(item.Key, item.Value);
}
}
}
sql = $"select * from ({sql}) t";
if (!string.IsNullOrEmpty(order_by))
{
sql += $" order by {order_by}";
}
up.SetValue("sql", sql);
foreach (var item in data)
{
up.SetValue(item.Key, item.Value);
}
return(DBUnitProxy.Query <LamdaExpressUnit>(up, ""));
}
/// <summary>
/// 直接执行query操作，并返回结果
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <returns></returns>
public static UnitDataCollection Query <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up)
{
var sql = source.ToSql();
var data = source.SQLGenerator.ConditionValues;
up.SetValue("sql", sql);
foreach (var item in data)
{
up.SetValue(item.Key, item.Value);
}
return(DBUnitProxy.Query <LamdaExpressUnit>(up, ""));
}
/// <summary>
/// 将Logic的数据写入到UnitParameter
/// </summary>
/// <param name="ld"></param>
/// <param name="up"></param>
public void SetUnitParameter(FlowLogicData ld, UnitParameter up, string domain)
{
foreach (var v in ld.Domain(domain))
{
up.SetValue(v.Key, ComFunc.nvl(v.Value));
}
}
/// <summary>
/// 标准化DB执行操作
/// </summary>
/// <param name="p"></param>
/// <param name="express"></param>
/// <returns></returns>
public virtual UnitDataCollection Excute(UnitParameter p, DBExpress express)
{
UnitDataCollection rtn = new UnitDataCollection();
if (express != null)
{
p.SetValue("__json__", express);
if (p.Dao is ADBAccess)
{
if (express.CurrentAct == DBExpress.ActType.Query)
{
rtn = Query <JsonExpressUnit>(p, "");
}
else if (express.CurrentAct == DBExpress.ActType.QueryByPage)
{
rtn = QueryByPage <JsonExpressUnit>(p, "");
}
else
{
NonQuery <JsonExpressUnit>(p, "");
}
}
else if (p.Dao is MongoAccess26)
{
var result = ((MongoAccess26)p.Dao).Excute(express);
if (express.CurrentAct == DBExpress.ActType.Query)
{
rtn.MongoListData = (List <FrameDLRObject>)result;
}
}
}
return(rtn);
}
/// <summary>
/// 将Logic的数据写入到UnitParameter
/// </summary>
/// <param name="ld"></param>
/// <param name="up"></param>
public void SetUnitParameter(LogicData ld, UnitParameter up, string domain)
{
foreach (var val in ld.Domain(domain))
{
up.SetValue(val.Key, val.Value);
}
}
/// <summary>
/// 将Logic的数据写入到UnitParameter
/// </summary>
/// <param name="ld"></param>
/// <param name="up"></param>
public void SetUnitParameter(LogicData ld, UnitParameter up)
{
foreach (var val in ld)
{
up.SetValue(val.Key, val.Value);
}
}
/// <summary>
/// 将Logic的数据写入到UnitParameter
/// </summary>
/// <param name="ld"></param>
/// <param name="up"></param>
public void SetUnitParameter(FlowParameter ld, UnitParameter up)
{
foreach (var v in ld.Domain(DomainKey.INPUT_PARAMETER))
{
up.SetValue(v.Key, ComFunc.nvl(v.Value));
}
}
/// <summary>
/// 将Logic的数据写入到UnitParameter
/// </summary>
/// <param name="ld"></param>
/// <param name="up"></param>
public void SetUnitParameter(FlowLogicData ld, UnitParameter up)
{
foreach (var v in ld)
{
up.SetValue(v.Key, ComFunc.nvl(v.Value));
}
}
/// <summary>
/// 直接执行Insert操作
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <param name="up"></param>
/// <param name="columns"></param>
public static void Insert <TSource>(this LinqDLR2Sql <TSource> source, UnitParameter up, object columns)
{
if (source.SQLGenerator is DaoSqlGenerator)
{
((DaoSqlGenerator)source.SQLGenerator).DoInsert(source, columns);
var sql = ((DaoSqlGenerator)source.SQLGenerator).CurrentSQL;
var data = source.SQLGenerator.ConditionValues;
up.SetValue("sql", sql);
foreach (var item in data)
{
up.SetValue(item.Key, item.Value);
}
DBUnitProxy.NonQuery <LamdaExpressUnit>(up, "nonquery");
}
else
{
throw new NotSupportedException("当前对象不支持Insert操作");
}
}
public virtual object Query(string sql, Dictionary <string, object> data)
{
_up.SetValue("___host_sql___", sql);
if (data != null)
{
foreach (var item in data)
{
_up.SetValue(item.Key, item.Value);
}
}
var udc = _logic.DB.Query <HostQueryUnit>(_up, "");
var list = udc.QueryData <FrameDLRObject>();
list = list == null ? new List <FrameDLRObject>() : list;
var rtn = FrameDLRObject.CreateInstance();
rtn.data = list;
return(((FrameDLRObject)rtn).ToDictionary());
}
public virtual UnitDataCollection LamdaQueryByPage <TSource>(UnitParameter p, LinqDLR2Sql <TSource> ltable, string orderbyexpress)
{
var sql = ltable.ToSql();
var data = ltable.SQLGenerator.ConditionValues;
//GlobalCommon.Logger.WriteLog(LoggerLevel.DEBUG, $"LamdaExpress解析后的sql为:{sql}");
p.SetValue("sql", sql);
p.SetValue("orderby", orderbyexpress);
foreach (var item in data)
{
p.SetValue(item.Key, item.Value);
}
if (ltable.CurrentOperationType == LinqDLR2SQLOperation.Select ||
ltable.CurrentOperationType == LinqDLR2SQLOperation.SelectJoin ||
ltable.CurrentOperationType == LinqDLR2SQLOperation.SelectMany)
{
return(QueryByPage <LamdaExpressUnit>(p, ""));
}
else
{
throw new FrameException("LamdaQueryByPage只适用于select类型的操作");
}
}
/// <summary>
/// 执行DDL操作
/// </summary>
/// <typeparam name="TUnit"></typeparam>
/// <param name="p"></param>
/// <param name="actionflag"></param>
/// <returns>返回为空，则表示操作成功，否则为错误提示信息</returns>
public static string ExcuteDDL <TUnit>(UnitParameter p, string actionflag)
where TUnit : IDBUnit <UnitParameter>
{
try
{
p.SetValue("_unit_action_flag_", actionflag);
Call <NonQueryUnit <TUnit> >(p);
return("");
}
catch (Exception ex)
{
return(ex.Message);
}
}
public virtual UnitDataCollection ExcuteLamda <TSource>(UnitParameter p, LinqDLR2Sql <TSource> ltable)
{
var sql = ltable.ToSql();
var data = ltable.SQLGenerator.ConditionValues;
//GlobalCommon.Logger.WriteLog(LoggerLevel.DEBUG, $"LamdaExpress解析后的sql为:{sql}");
p.SetValue("sql", sql);
foreach (var item in data)
{
p.SetValue(item.Key, item.Value);
}
if ((ltable.CurrentOperationType & LinqDLR2SQLOperation.Select) == LinqDLR2SQLOperation.Select ||
(ltable.CurrentOperationType & LinqDLR2SQLOperation.SelectJoin) == LinqDLR2SQLOperation.SelectJoin ||
(ltable.CurrentOperationType & LinqDLR2SQLOperation.SelectMany) == LinqDLR2SQLOperation.SelectMany)
{
return(Query <LamdaExpressUnit>(p, ""));
}
else
{
NonQuery <LamdaExpressUnit>(p, "");
return(new UnitDataCollection());
}
}
/// <summary>
/// 标准化DB执行操作
/// </summary>
/// <param name="p"></param>
/// <param name="express"></param>
/// <returns></returns>
public static UnitDataCollection Excute(UnitParameter p, DBExpress express)
{
UnitDataCollection rtn = new UnitDataCollection();
if (express != null)
{
p.SetValue("__json__", express);
if (p.Dao is ADBAccess)
{
if (express.CurrentAct == DBExpress.ActType.Query)
{
rtn = Query <JsonExpressUnit>(p, "");
}
else if (express.CurrentAct == DBExpress.ActType.QueryByPage)
{
rtn = QueryByPage <JsonExpressUnit>(p, "");
}
else
{
switch (express.CurrentAct)
{
case DBExpress.ActType.CreateTable:
rtn.ErrorMsg = ExcuteDDL <JsonExpressUnit>(p, "");
break;
case DBExpress.ActType.AlterColumn:
rtn.ErrorMsg = ExcuteDDL <JsonExpressUnit>(p, "");
break;
case DBExpress.ActType.DropTable:
rtn.ErrorMsg = ExcuteDDL <JsonExpressUnit>(p, "");
break;
default:
NonQuery <JsonExpressUnit>(p, "");
break;
}
}
}
}
return(rtn);
}
/// <summary>
/// 非查询类的db操作
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="p"></param>
/// <param name="actionflag">动作区分标记</param>
public virtual void NonQuery <T>(UnitParameter p, string actionflag) where T : IDBUnit <UnitParameter>
{
p.SetValue("_unit_action_flag_", actionflag);
UnitProxy.Call <NonQueryUnit <T> >(p);
}
/// <summary>
/// 执行存储过程操作
/// </summary>
/// <typeparam name="TUnit"></typeparam>
/// <param name="p"></param>
/// <param name="actionflag">动作区分标记</param>
/// <returns></returns>
public static UnitDataCollection ExcuteSP <TUnit>(UnitParameter p, string actionflag)
where TUnit : IDBUnit <UnitParameter>
{
p.SetValue("_unit_action_flag_", actionflag);
return((UnitDataCollection)Call <SPUnit <TUnit> >(p));
}
public void insertTemp(UnitParameter up, Dictionary <string, string> prodmap, Dictionary <string, string> salemap, string compcoderesult, string today)
{
//先把tempt_terminal_so_line为N的都改为今天的数据
up.SetValue("today", today);
DB.NonQuery <SoUnit>(up, "updatesstatus");
var temp = from t in DB.LamdaTable("tempt_terminal_so_line", "a")
where t.cdate == today
select new
{
t.id,
t.comp_code,
t.prod_code,
t.qty,
t.form_date,
t.modi_time,
t.busi_type,
t.province_name_st,
t.dealer_code,
t.dealer_code2,
t.dealer_code3,
t.unit,
t.created_date,
t.province_name_so,
t.status,
t.dealer_name,
t.dealer_name2,
t.dealer_name3
}; //筛选为N的数据
up.Count_Of_OnePage = 100000;
up.ToPage = 1;
var dt = DateTime.Now;
var tempresult = DB.LamdaQueryByPage(up, temp, "id");
var tempcount = 0;
var c1 = 0; //总部发货c1
var c2 = 0; //非总部发货c2
var p3 = 0; //排除掉的p3
var p4 = 0; //排除掉的p4
do
{
GlobalCommon.Logger.WriteLog(LoggerLevel.INFO, $"执行第{up.ToPage}页数据:{ tempresult.QueryData<FrameDLRObject>().Count}"); dt = DateTime.Now;
var list = new List <dynamic>();
var listall = (from t in tempresult.QueryData <FrameDLRObject>()
select t).ToList();
foreach (dynamic item in listall)
{
//GlobalCommon.Logger.WriteLog(LoggerLevel.INFO, $"执行第{up.ToPage}页数据ID:{item.id}"); dt = DateTime.Now;
//Prod_code Dealercode_ok 这2个只要一个 为空 就算没成功的数据
if (prodmap.ContainsKey(item.prod_code))//Prod_code
{
dynamic rtn = FrameDLRObject.CreateInstance();
rtn.id = item.id;
rtn.dealer_code = "";
rtn.comp_code = item.comp_code;
rtn.prod_code = prodmap[item.prod_code];
rtn.qty = item.qty;
rtn.prod_code_hw = item.prod_code;
rtn.dealer_code1 = item.dealer_code;
rtn.dealer_code2 = item.dealer_code2;
rtn.dealer_code3 = item.dealer_code3;
rtn.busi_type = item.busi_type;
rtn.province_name_st = item.province_name_st;
rtn.form_date = item.form_date;
rtn.modi_time = item.modi_time;
rtn.province_name_st_desc = "";
var strunit = "Sample";
rtn.unit = item.unit.IndexOf(strunit) > -1 ? "演示机" : "商品机";
rtn.created_date = item.created_date;
rtn.province_name_so = item.province_name_so;
rtn.dealer_name = item.dealer_name;
rtn.dealer_name2 = item.dealer_name2;
rtn.dealer_name3 = item.dealer_name3;
if (item.busi_type == "总部发货")
{
if (string.IsNullOrWhiteSpace(ComFunc.nvl(item.province_name_st)))//特殊情况 唯一dealer_code为空的情况
{
rtn.dealer_code = "";
rtn.province_name_st_desc = "中国";
}
else
{
rtn.dealer_code = compcoderesult;
}
list.Add(rtn);
}
else
{
var dealercode_ok = "";
if (isExist(rtn.prod_code, item.dealer_code, item.dealer_code2, item.dealer_code3, item.province_name_st, salemap, ref dealercode_ok))//dealercode_ok
{
rtn.dealer_code = dealercode_ok;
list.Add(rtn);
}
else
{
p3++;
}
}
}
else
{
p4++;
GlobalCommon.Logger.WriteLog(LoggerLevel.INFO, "prod_code为空排除数据ID:" + item.id + ",prod_code:" + item.prod_code);
}
}
foreach (dynamic listitem in list)
{
up.SetValue("uid", listitem.id);
var table_name = !String.IsNullOrEmpty(ComFunc.nvl(listitem.dealer_code)) ? "t_sale_so" : "t_sale_so_empty";
up.SetValue("tabelname", table_name);
DB.NonQuery <SoUnit>(up, "updateinfo");
}
GlobalCommon.Logger.WriteLog(LoggerLevel.INFO, $"执行删除更新:{(DateTime.Now - dt).TotalMilliseconds}"); dt = DateTime.Now;
var l1 = (from t in list
where !String.IsNullOrEmpty(ComFunc.nvl(t.dealer_code))
select new
{
t.id,
t.dealer_code,
t.comp_code,
t.prod_code,
t.qty,
t.prod_code_hw,
t.dealer_code1,
t.dealer_code2,
t.dealer_code3,
t.busi_type,
t.province_name_st,
t.form_date,
t.modi_time,
t.province_name_st_desc,
t.created_date,
t.dealer_name,
t.unit,
t.province_name_so,
t.dealer_name2,
t.dealer_name3
}).ToList();
DB.BulkInsert(up, "t_sale_so", l1);
c1 += l1.Count;
GlobalCommon.Logger.WriteLog(LoggerLevel.INFO, $"执行bulkinsert-t_sale_so:{(DateTime.Now - dt).TotalMilliseconds}"); dt = DateTime.Now;
var l2 = (from t in list
where String.IsNullOrEmpty(ComFunc.nvl(t.dealer_code))
select new
{
t.id,
t.dealer_code,
t.comp_code,
t.prod_code,
t.qty,
t.prod_code_hw,
t.dealer_code1,
t.dealer_code2,
t.dealer_code3,
t.busi_type,
t.province_name_st,
t.form_date,
t.modi_time,
t.province_name_st_desc,
t.unit,
t.created_date,
t.province_name_so,
t.dealer_name,
t.dealer_name2,
t.dealer_name3
}).ToList();
DB.BulkInsert(up, "t_sale_so_empty", l2);
c2 += l2.Count;
tempcount += tempresult.QueryData <FrameDLRObject>().Count();
list.Clear();
up.ToPage++;
tempresult.Clear();
tempresult = DB.LamdaQueryByPage(up, temp, "id");
GlobalCommon.Logger.WriteLog(LoggerLevel.INFO, $"执行第{up.ToPage}页查询:{(DateTime.Now - dt).TotalMilliseconds}"); dt = DateTime.Now;
} while (tempresult.QueryTable.RowLength > 0 && tempresult.CurrentPage == up.ToPage);
GlobalCommon.Logger.WriteLog(LoggerLevel.INFO, DateTime.Now.ToString() + "总count数:" + tempcount + "汇入t_sale_so:" + c1 + ",t_sale_so_empty:" + c2 + "笔资料,dealercode_ok为空排除数据ID" + p3 + ",prod_code为空排除掉数据" + p4);
}
/// <summary>
/// 查询操作
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="p"></param>
/// <param name="actionflag">动作区分标记</param>
/// <returns></returns>
public virtual UnitDataCollection Query <T>(UnitParameter p, string actionflag) where T : IDBUnit <UnitParameter>
{
p.SetValue("_unit_action_flag_", actionflag);
return((UnitDataCollection)UnitProxy.Call <QueryUnit <T> >(p));
}
/// <summary>
/// 非查询类的db操作
/// </summary>
/// <typeparam name="TUnit"></typeparam>
/// <param name="p"></param>
/// <param name="actionflag">动作区分标记</param>
public static void NonQuery <TUnit>(UnitParameter p, string actionflag)
where TUnit : IDBUnit <UnitParameter>
{
p.SetValue("_unit_action_flag_", actionflag);
Call <NonQueryUnit <TUnit> >(p);
}