public object SendBatchNotice(LogicData arg)
{
var touser = ComFunc.nvl(arg["touser"]);
var remark = ComFunc.nvl(arg["remark"]);
var taskname = ComFunc.nvl(arg["taskname"]);
var starttime = DateTimeStd.IsDateTimeThen(arg["starttime"], "yyyy-MM-dd HH:mm:ss");
var endtime = DateTimeStd.IsDateTimeThen(arg["endtime"], "yyyy-MM-dd HH:mm:ss");
var resultdesc = ComFunc.nvl(arg["result"]);
var up = DB.NewDBUnitParameter();
var s = from t in DB.LamdaTable("user_info", "")
where t.userid == touser || t.UserName == touser || t.weixinid == touser
select t;
var result = DB.ExcuteLamda(up, s);
if (result.QueryTable.RowLength <= 0)
{
return(new
{
issuccess = false,
msg = "用户不存在"
});
}
touser = ComFunc.nvl(result.QueryTable[0, "weixinid"]);
//var sendresult = Weixin.SendTemplateMsg(touser, "qQvNxlTbAtnkN7dM6M3-Wk0BRMLNg1f-pghYNcn3VVw", "", "", "",
// "批次执行通知", remark, taskname, starttime, endtime, resultdesc);
return("");
}
public object QueryList()
{
var estate_id = ComFunc.nvl(PostDataD.estate);
var position = ComFunc.nvl(PostDataD.position);
var start_time = DateTimeStd.IsDateTimeThen(PostDataD.start_time, "yyyy-MM-dd HH:mm:ss");
var end_time = DateTimeStd.IsDateTimeThen(PostDataD.start_time, "yyyy-MM-dd HH:mm:ss");
var plate_no = ComFunc.nvl(PostDataD.plate_no);
var up = DB.NewDBUnitParameter();
var result = (from t in DB.LamdaTable(up, "IC_Car_Monitor", "a")
join t2 in DB.LamdaTable(up, "IC_Vehicle", "b").LeftJoin() on t.CM_Plate_No equals t2.V_No
join t3 in DB.LamdaTable(up, "IC_Device", "c").LeftJoin() on t.CM_Device equals t3.D_UID
join t4 in DB.LamdaTable(up, "IC_Estate", "d").LeftJoin() on t.CM_Estate_ID equals t4.E_ID
join t5 in DB.LamdaTable(up, "IC_Position", "e").LeftJoin() on t.CM_Position_XY equals t5.P_GUID
where t.notnull(estate_id, t.CM_Estate_ID == estate_id) &&
t.notnull(position, t.CM_Position_XY == position) &&
t.notnull(start_time, t.CM_Occur_Time >= start_time) &&
t.notnull(end_time, t.CM_Occur_Time <= end_time) &&
t.notnull(plate_no, t.CM_Plate_No.contains(plate_no))
select new
{
id = t.CM_GUID,
plate_no = t.CM_Plate_No,
plate_color = t.CM_Plate_Color,
vertical_color = t.CM_Color,
occur_time = t.CM_Occur_Time,
position = t5.P_Name,
device_id = t3.D_UID,
device_code = t3.D_Code,
device_name = t3.D_Name,
estate_id = t4.E_ID,
estate = t4.E_Name,
driver_uid = t2.V_Driver,
t.add_time,
t.last_time
}).QueryByPage(up, "occur_time desc,add_time desc,last_time desc");
var data = result.QueryData <FrameDLRObject>().Select((d) =>
{
var dobj = (FrameDLRObject)d;
d.occur_time = DateTimeStd.IsDateTimeThen(d.occur_time, "yyyy-MM-dd HH:mm:ss");
d.driver_add_time = DateTimeStd.IsDateTimeThen(d.driver_add_time, "yyyy-MM-dd HH:mm:ss");
d.is_recorded = ComFunc.nvl(d.driver_uid) == "" ? false : true;
dobj.Remove("add_time");
dobj.Remove("last_time");
return(d);
});
return(new
{
code = "success",
msg = "",
total_count = result.TotalRow,
page = result.CurrentPage,
total_page = result.TotalPage,
limit = result.Count_Of_OnePage,
data = from t in data
select t
});
}
object DeliverySign()
{
SetCacheEnable(false);
var free_text = ComFunc.nvl(QueryStringD.free_text);
var up = DB.NewDBUnitParameter();
var s = from t in DB.LamdaTable("HW_DeliveryStatusInfo", "a")
where t.notnull(free_text, t.free_text == free_text) &&
t.pod_sign_date != null
select t;
var result = DB.LamdaQueryByPage(up, s, "actual_shipped_date desc,pod_sign_date desc");
return(new
{
total_num = result.TotalRow,
current_page = result.CurrentPage,
total_page = result.TotalPage,
page_size = result.Count_Of_OnePage,
data = from t in result.QueryData <FrameDLRObject>()
select new
{
t.delivery_id,
t.packing_list_no,
t.hw_contract_no,
t.cust_contract_no,
t.cust_purchase_no,
//t.cust_code_cbg,
//t.cust_code_hw,
//t.cust_name,
//t.channel_id,
//t.cust_type_lv1,
//t.cust_type_lv2,
t.country_code,
//t.prod_type_cn,
//t.prod_type_en,
//t.prod_business_type,
t.prod_code_sale,
//t.prod_code_make,
t.prod_desc_hw,
//t.prod_desc_hw_en,
t.prod_code_cust,
t.prod_desc_cust,
//t.prod_model_hw,
//t.prod_model_ext,
//t.prod_color_cn,
//t.prod_color_en,
t.cust_purchase_item,
estimate_arrive_date = DateTimeStd.IsDateTimeThen(t.estimate_arrive_date, "yyyy-MM-dd HH:mm:ss"),
actual_shipped_date = DateTimeStd.IsDateTimeThen(t.actual_shipped_date, "yyyy-MM-dd HH:mm:ss"),
t.destination_city,
t.destination_province,
t.destination_detail,
delivery_quantity = IntStd.IsInt(t.delivery_quantity) ? IntStd.ParseStd(t.delivery_quantity).Value : 0,
pod_sign_date = DateTimeStd.IsDateTimeThen(t.pod_sign_date, "yyyy-MM-dd HH:mm:ss"),
sign_quantity = IntStd.IsInt(t.sign_quantity) ? IntStd.ParseStd(t.sign_quantity).Value : 0,
t.order_line_note,
t.free_text
}
});
}
object QueryIMEI()
{
SetCacheEnable(false);
var delivery_id = QueryStringD.delivery_id;
string start_time = DateTimeStd.IsDateTimeThen(QueryStringD.start_time, "yyyy-MM-dd HH:mm:ss");
string end_time = DateTimeStd.IsDateTimeThen(QueryStringD.end_time, "yyyy-MM-dd HH:mm:ss");
var up = DB.NewDBUnitParameter();
var s = from t in DB.LamdaTable("HW_IMEI_Info", "a")
join t2 in DB.LamdaTable("hw_deliverystatusinfo", "b") on t.delivery_id equals t2.delivery_id
where t.notnull(delivery_id, t.delivery_id == delivery_id) &&
t.notnull(start_time, t.create_time >= start_time) &&
t.notnull(end_time, t.end_time <= end_time)
select new
{
t.delivery_id,
t.prod_model_ext,
t.prod_code_sale,
t.prod_code_make,
t.prod_color_cn,
t.prod_color_en,
t.prod_barcode,
t.imei,
t.imei_1,
t.imei_2,
t.meid_hex,
t.meid_dec,
t.meid_dec_18,
t.meid_hex_14,
t.wifi,
t.mac,
t.mac_1,
t.mac_2,
t.ean_upc_code,
t.software_version,
t.spc,
t.sim_card_no,
t.caton_id_hw,
t.pallet_id_hw,
t.pallet_id_cust,
t.caton_id_cust,
t2.prod_code_cust,
t2.prod_desc_cust
};
var result = DB.LamdaQueryByPage(up, s, "delivery_id");
return(new
{
total_num = result.TotalRow,
current_page = result.CurrentPage,
total_page = result.TotalPage,
page_size = result.Count_Of_OnePage,
data = result.QueryData <FrameDLRObject>()
});
}
object GetLog()
{
SetCacheEnable(false);
string userids = ComFunc.nvl(QueryStringD.user_ids);
var start_date = DateTimeStd.IsDateTimeThen(QueryStringD.start_date, "yyyy-MM-dd HH:mm:ss");
var end_date = DateTimeStd.IsDateTimeThen(QueryStringD.end_date, "yyyy-MM-dd HH:mm:ss");
var events = ComFunc.nvl(QueryStringD.events);
var up = DB.NewDBUnitParameter();
var s = from t in DB.LamdaTable("ecs_user_wallet_log", "a")
where t.notnull(start_date, t.last_time >= start_date) &&
t.notnull(end_date, t.last_time <= end_date) &&
t.notnull(events, t.events.contains(events)) &&
t.notnull(userids, t.user_id.within(userids))
select t;
var result = DB.LamdaQueryByPage(up, s, "last_time desc,seq desc");
var list = result.QueryData <FrameDLRObject>();
return(new
{
code = "success",
total_num = result.TotalRow,
current_page = result.CurrentPage,
total_page = result.TotalPage,
page_size = result.Count_Of_OnePage,
data = (from t in list
select new
{
t.user_id,
t.amount,
t.ebs_wd_no,
t.events,
t.memo,
modi_time = DateTimeStd.IsDateTimeThen(t.last_time, "yyyy-MM-dd HH:mm:ss"),
t.seq,
in_amount = (t.events == "打款") ? t.amount : 0,
out_amount = (t.events == "核销") ? t.amount : 0,
t.total_amount,
t.available_amount,
t.freeze_amount,
t.used_amount,
t.line_total_amount,
t.line_available_amount,
t.line_freeze_amount,
t.line_used_amount
}).ToList()
});
}
protected override object Convert2DateTime(object[] args)
{
if (args == null || args.Length <= 0)
{
return(this);
}
var rtn = LinqDLRColumn.New <MysqlDLRColumn>("", this, SqlFlags);
object value = null;
var format = "%Y-%m-%d %H:%i:%s";
if (args.Length == 1)
{
if (args[0] is LinqDLRColumn)
{
value = args[0];
}
else
{
value = DateTimeStd.IsDateTimeThen(args[0], "yyyy-MM-dd HH:mm:ss");
}
}
else if (args.Length == 3)
{
var year = ComFunc.nvl(args[0]);
var month = ComFunc.nvl(args[1]);
var day = ComFunc.nvl(args[2]);
value = $"{year}-{month.PadLeft(2, '0')}-{day.PadLeft(2, '0')}";
format = "%Y-%m-%d";
}
else if (args.Length > 3)
{
var year = ComFunc.nvl(args[0]);
var month = ComFunc.nvl(args[1]);
var day = ComFunc.nvl(args[2]);
var hour = args.Length > 3 ? ComFunc.nvl(args[3]) : "00";
var minute = args.Length > 4 ? ComFunc.nvl(args[4]) : "00";
var second = args.Length > 5 ? ComFunc.nvl(args[5]) : "00";
value = $"{year}-{month.PadLeft(2, '0')}-{day.PadLeft(2, '0')} {hour.PadLeft(2, '0')}:{minute.PadLeft(2, '0')}:{second.PadLeft(2, '0')}";
}
var express = $"STR_TO_DATE({rtn.Convert2Express(value)},'{format}')";
rtn.ColumnExpress = express;
return(rtn);
}
private object ConvertConditionValue(string datatype, object v)
{
if (new string[] { "datetime" }.Contains(datatype.ToLower()))
{
return(DateTimeStd.IsDateTimeThen(v, "yyyy-MM-dd HH:mm:ss"));
}
else if (new string[] { "bit" }.Contains(datatype.ToLower()))
{
return(BoolStd.ConvertTo(v, 1, 0));
}
else if (new string[] { "text", "picture", "file" }.Contains(datatype.ToLower()))
{
return("");
}
else
{
return(v);
}
}
object GetDriverInfo(string id)
{
var up = DB.NewDBUnitParameter();
var result = (from t in DB.LamdaTable(up, "IC_Resident", "f")
join t1 in DB.LamdaTable(up, "IC_House", "g").LeftJoin() on t.R_House_ID equals t1.H_ID
join t2 in DB.LamdaTable(up, "IC_Building", "h").LeftJoin() on t1.H_Building_ID equals t2.B_ID
where t.R_ID == id
select new
{
driver_uid = t.R_ID,
driver_name = t.R_Name,
driver_id_no = t.R_ID_No,
driver_add_time = t.add_time,
driver_house_id = t1.R_House_ID,
driver_house_no = t1.H_No,
driver_house_unit = t1.H_Unit,
driver_building_name = t2.B_Name
}).GetQueryList(up);
if (result.Count <= 0)
{
return(new
{
code = "failed",
msg = "无该车主资料"
});
}
dynamic data = result.First();
data.driver_add_time = DateTimeStd.IsDateTimeThen(data.driver_add_time, "yyyy-MM-dd HH:mm:ss");
return(new
{
code = "success",
msg = "",
data
});
}
public DataCollection DoOperate(ParameterStd p)
{
UnitDataCollection rtn = new UnitDataCollection();
string flag = p.GetValue <string>("_unit_action_flag_");
UnitParameter up = (UnitParameter)p;
if (up.Dao is ADBAccess)
{
T t = (T)Activator.CreateInstance(typeof(T), true);
var sqlobj = t.GetSqlFunc(flag)(up);
if (!(sqlobj is FrameDLRObject))
{
throw new TypeRequiredException("需要指定的动态数据对象：FrameDLRObject");
}
string sql = sqlobj.sql;
ADBAccess dba = (ADBAccess)up.Dao;
DBOParameterCollection dbc = new DBOParameterCollection();
if (!string.IsNullOrEmpty(sql))
{
string regstr = "";
regstr = @"(?<=" + (dba.ParameterFlagChar == "$"?"\\$": dba.ParameterFlagChar) + @")[A-Za-z0-9_]+\d*";
string regexpress = @"(?isx)
(') #开始标记“<tag...>”
(?> #分组构造，用来限定量词“*”修饰范围
\1 (?<Open>) #命名捕获组，遇到开始标记，入栈，Open计数加1
|\1 (?<-Open>) #狭义平衡组，遇到结束标记，出栈，Open计数减1
|[^']* #右侧不为开始或结束标记的任意字符
)
(?(Open)(?!)) #判断是否还有'OPEN'，有则说明不配对，什么都不匹配
\1 #结束标记“</tag>”
";
Regex re = new Regex(regstr);
string tmpsql = "";
Regex re2 = new Regex(regexpress);
tmpsql = sql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
if (up.GetValue(m.ToString()) is byte[])
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Binary);
}
else if (up.GetValue(m.ToString()) is DateTime)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.DateTime);
}
else if (up.GetValue(m.ToString()) is int)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Int32);
}
else if (up.GetValue(m.ToString()) is double)
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Double);
}
else
{
dbc.Add(m.ToString(), up.GetValue(m.ToString()));
}
}
}
try
{
rtn.QueryDatas = dba.Query(sql, dbc);
}catch (Exception ex)
{
FrameDLRObject dp = FrameDLRObject.CreateInstance(Base.Constants.FrameDLRFlags.SensitiveCase);
foreach (var item in dbc)
{
if (item.Value.ParameterValue is DateTime)
{
dp.SetValue(item.Key, DateTimeStd.IsDateTimeThen(item.Value.ParameterValue, "yyyy-MM-dd HH:mm:ss"));
}
else if (item.Value.ParameterValue is DBNull)
{
dp.SetValue(item.Key, null);
}
else
{
dp.SetValue(item.Key, item.Value.ParameterValue);
}
}
GlobalCommon.Logger.WriteLog(Base.Constants.LoggerLevel.ERROR, $"QuerySql={sql};\nParameters={dp.ToJSONString()}");
throw ex;
}
if (rtn.QueryDatas != null && rtn.QueryDatas.Tables.Count > 0)
{
rtn.QueryTable = rtn.QueryDatas[0];
}
}
return(rtn);
}
void load(dynamic args)
{
var uppsql = DB.NewPSQLDBUnitParameter();
var up = DB.NewDBUnitParameter();
var s2 = from t in DB.LamdaTable("common_sale_proxy", "a")
select new
{
t.prod_code,
t.state,
t.dealer_type,
t.dealer_code
};
var s3 = from t in DB.LamdaTable("common_hw_prod_map", "a")
select new
{
t.prod_code,
t.prod_code_hw
};
var salemapresult = DB.ExcuteLamda(up, s2);
var prodmapresult = DB.ExcuteLamda(up, s3);
var salemaplist = (from t in salemapresult.QueryData <FrameDLRObject>()
select new
{
key = t.prod_code + "|" + t.dealer_type + "|" + t.dealer_code,
value = t.dealer_code
}).ToList();
salemaplist.AddRange(from t in salemapresult.QueryData <FrameDLRObject>()
select new
{
key = t.prod_code + "|" + t.state.Replace("省", "").Replace("市", "").Replace("自治区", ""),
value = t.dealer_code
});
salemaplist.AddRange(from t in salemapresult.QueryData <FrameDLRObject>()
select new
{
key = t.prod_code + "|" + t.plateform_code,
value = t.dealer_code
});
var salemap = salemaplist.ToDictionary <string, string>(k => k.key, v => v.value);
var prodmap = (from t in prodmapresult.QueryData <FrameDLRObject>()
select new
{
key = t.prod_code_hw,
value = t.prod_code
}).ToDictionary <string, string>(k => k.key, v => v.value);
var smax = (from t in DB.PSQLLamdaTable("t_sale_so", "a")
select t.modi_time).Max("a.modi_time");
var maxresult = DB.ExcuteLamda(up, smax);
var max_modi_time = maxresult.QueryTable.RowLength > 0 ? ComFunc.nvl(maxresult.QueryTable[0, 0]) : "";
var s1 = from t in DB.PSQLLamdaTable("t_terminal_so_line", "a")
where t.modi_time != null && t.province_name_so != "" && t.notnull(max_modi_time, t.append(t.modi_time > max_modi_time, "::timestamp"))
select new
{
t.id,
t.comp_code,
t.prod_code,
t.qty,
t.form_date,
t.modi_time,
type = t.busi_type,
state = t.province_name_so,
t.dealer_code,
t.dealer_code2,
t.dealer_code3
};
uppsql.Count_Of_OnePage = 10000;
uppsql.ToPage = 1;
var result = DB.LamdaQueryByPage(uppsql, s1, "id");
do
{
var list = result.QueryData <FrameDLRObject>().Select(d =>
{
dynamic rtn = FrameDLRObject.CreateInstance();
//采用bulkinsert，必须保证栏位顺序与db中的一致才行
rtn.id = d.id;
rtn.dealer_code = "";
rtn.comp_code = d.comp_code;
rtn.prod_code = prodmap.ContainsKey(d.prod_code) ? prodmap[d.prod_code] : "";
rtn.qty = d.qty;
rtn.form_date = d.form_date;
rtn.modi_time = DateTimeStd.IsDateTimeThen(d.modi_time, "yyyy-MM-dd HH:mm:ss");
rtn.type = null;
rtn.state = d.state;
if (d.type == "总部发货")
{
rtn.type = 1;
}
else if (d.type == "本省发货")
{
rtn.type = 2;
}
else
{
rtn.type = null;
}
var dealercode_ok = "";
//一、用dealer_code，dealer_code2，dealer_code3分别去“主辅省包-平台对应关系”匹配，找到辅省包由记该省包SO，没找到继续下一步
//二、用dealer_code，dealer_code2，dealer_code3分别去“主辅省包-平台对应关系”匹配，找到主省包由记该省包SO，没找到继续下一步
//三、以上两步都没找到匹配的，则用省份名称去“主辅省包-平台对应关系”找主省包，记该省包SO
var key11 = rtn.prod_code + "|2|" + d.dealer_code;
var key12 = rtn.prod_code + "|2|" + d.dealer_code2;
var key13 = rtn.prod_code + "|2|" + d.dealer_code3;
var key21 = rtn.prod_code + "|1|" + d.dealer_code;
var key22 = rtn.prod_code + "|1|" + d.dealer_code2;
var key23 = rtn.prod_code + "|1|" + d.dealer_code3;
var key3 = rtn.prod_code + "|" + d.state;
if (salemap.ContainsKey(key11) && salemap[key11].StartsWith("R"))
{
dealercode_ok = salemap[key11];
}
else if (salemap.ContainsKey(key12) && salemap[key12].StartsWith("R"))
{
dealercode_ok = salemap[key12];
}
else if (salemap.ContainsKey(key13) && salemap[key13].StartsWith("R"))
{
dealercode_ok = salemap[key13];
}
else if (salemap.ContainsKey(key21) && salemap[key21].StartsWith("R"))
{
dealercode_ok = salemap[key21];
}
else if (salemap.ContainsKey(key22) && salemap[key22].StartsWith("R"))
{
dealercode_ok = salemap[key22];
}
else if (salemap.ContainsKey(key23) && salemap[key23].StartsWith("R"))
{
dealercode_ok = salemap[key23];
}
else if (salemap.ContainsKey(key3))
{
dealercode_ok = salemap[key3];
}
else
{
dealercode_ok = "";
}
rtn.dealer_code = dealercode_ok;
return(rtn);
}).ToList();
DB.BulkInsert(up, "t_sale_so", list);
//foreach (dynamic item in list)
//{
// DB.QuickInsertNotExists(up, "t_sale_so", item, new
// {
// id = item.id
// });
//}
list.Clear();
uppsql.ToPage++;
result.Clear();
result = DB.LamdaQueryByPage(uppsql, s1, "id");
} while (result.QueryTable.RowLength > 0 && result.CurrentPage == uppsql.ToPage);
}
public DataCollection DoOperate(ParameterStd p)
{
string flag = p.GetValue <string>("_unit_action_flag_");
//预执行
T t = (T)Activator.CreateInstance(typeof(T), true);
UnitParameter up = (UnitParameter)p;
UnitDataCollection rtn = new UnitDataCollection();
if (up.Dao is ADBAccess)
{
var sqlobj = t.GetSqlFunc(flag)(up);
ADBAccess dba = (ADBAccess)up.Dao;
string regstr = "";
string regexpress = @"(?isx)
(') #开始标记“<tag...>”
(?> #分组构造，用来限定量词“*”修饰范围
\1 (?<Open>) #命名捕获组，遇到开始标记，入栈，Open计数加1
|\1 (?<-Open>) #狭义平衡组，遇到结束标记，出栈，Open计数减1
|[^']* #右侧不为开始或结束标记的任意字符
)
(?(Open)(?!)) #判断是否还有'OPEN'，有则说明不配对，什么都不匹配
\1 #结束标记“</tag>”
";
string tmpsql = "";
regstr = @"(?<=" + (dba.ParameterFlagChar == "$" ? "\\$" : dba.ParameterFlagChar) + @")[A-Za-z0-9_]+\d*";
Regex re = new Regex(regstr);
Regex re2 = new Regex(regexpress);
try
{
if (!(sqlobj is FrameDLRObject))
{
throw new TypeRequiredException("需要指定的动态数据对象：FrameDLRObject");
}
string presql = sqlobj.presql;
DBAPageP dbc = new DBAPageP();
//dba.BeginTransaction();
if (!string.IsNullOrEmpty(presql))
{
tmpsql = presql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()));
}
try
{
dba.ExecuteNoQuery(presql, dbc.SQL_Parameters);
}
catch (Exception ex)
{
FrameDLRObject dp = FrameDLRObject.CreateInstance(Base.Constants.FrameDLRFlags.SensitiveCase);
foreach (var item in dbc.SQL_Parameters)
{
if (item.Value.ParameterValue is DateTime)
{
dp.SetValue(item.Key, DateTimeStd.IsDateTimeThen(item.Value.ParameterValue, "yyyy-MM-dd HH:mm:ss"));
}
else if (item.Value.ParameterValue is DBNull)
{
dp.SetValue(item.Key, null);
}
else
{
dp.SetValue(item.Key, item.Value.ParameterValue);
}
}
GlobalCommon.Logger.WriteLog(Base.Constants.LoggerLevel.ERROR, $"QueryByPage PreSql={presql};\nParameters={dp.ToJSONString()}");
throw ex;
}
}
//执行翻页查询
string sql = sqlobj.sql;
string orderby = sqlobj.orderby;
if (!string.IsNullOrEmpty(sql))
{
tmpsql = sql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
dbc.SQL_Parameters.Clear();
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
if (up.GetValue(m.ToString()) is byte[])
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()), System.Data.DbType.Binary);
}
else
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()));
}
}
try
{
dbc.SQL = sql;
dbc.OrderBy = orderby;
dbc.Count_of_OnePage = up.Count_Of_OnePage;
dbc.CurrentPage = up.CurrentPage;
dba.StartPageByCondition(dbc);
rtn.QueryTable = dba.GoToPage(up.ToPage);
rtn.QueryDatas = new DataSetStd();
rtn.QueryDatas.Tables.Add(rtn.QueryTable);
rtn.Count_Of_OnePage = up.Count_Of_OnePage;
rtn.CurrentPage = dba.CurrentPage;
rtn.TotalPage = dba.TotalPage;
rtn.TotalRow = dba.TotalRow;
}
catch (Exception ex)
{
FrameDLRObject dp = FrameDLRObject.CreateInstance(Base.Constants.FrameDLRFlags.SensitiveCase);
foreach (var item in dbc.SQL_Parameters)
{
if (item.Value.ParameterValue is DateTime)
{
dp.SetValue(item.Key, DateTimeStd.IsDateTimeThen(item.Value.ParameterValue, "yyyy-MM-dd HH:mm:ss"));
}
else if (item.Value.ParameterValue is DBNull)
{
dp.SetValue(item.Key, null);
}
else
{
dp.SetValue(item.Key, item.Value.ParameterValue);
}
}
GlobalCommon.Logger.WriteLog(Base.Constants.LoggerLevel.ERROR, $"QueryByPage sql={sql};\n order by={orderby};\nParameters={dp.ToJSONString()}");
throw ex;
}
}
//收尾处理
string aftersql = sqlobj.aftersql;
if (!string.IsNullOrEmpty(aftersql))
{
tmpsql = aftersql.Replace("''", "#sp#");
foreach (Match m in re2.Matches(tmpsql))
{
tmpsql = tmpsql.Replace(m.Value, "#sp#");
}
dbc.SQL_Parameters.Clear();
foreach (System.Text.RegularExpressions.Match m in re.Matches(tmpsql))
{
dbc.SQL_Parameters.Add(m.ToString(), up.GetValue(m.ToString()));
}
try
{
dba.ExecuteNoQuery(aftersql, dbc.SQL_Parameters);
}catch (Exception ex)
{
FrameDLRObject dp = FrameDLRObject.CreateInstance(Base.Constants.FrameDLRFlags.SensitiveCase);
foreach (var item in dbc.SQL_Parameters)
{
if (item.Value.ParameterValue is DateTime)
{
dp.SetValue(item.Key, DateTimeStd.IsDateTimeThen(item.Value.ParameterValue, "yyyy-MM-dd HH:mm:ss"));
}
else if (item.Value.ParameterValue is DBNull)
{
dp.SetValue(item.Key, null);
}
else
{
dp.SetValue(item.Key, item.Value.ParameterValue);
}
}
GlobalCommon.Logger.WriteLog(Base.Constants.LoggerLevel.ERROR, $"QueryByPage AfterSql={aftersql};\nParameters={dp.ToJSONString()}");
throw ex;
}
}
//dba.CommitTransaction();
}
catch
{
//if (dba != null)
// dba.RollbackTransaction();
throw;
}
}
return(rtn);
}
public object QueryList(string id)
{
SetCacheEnable(false);
var up = DB.NewDBUnitParameter();
var s = from t in DB.LamdaTable(up, "EXTEND_SINGLETABLE_OP", "a")
where (t.s_uid == id || t.S_Name == id)
select t;
var list = s.GetQueryList(up);
if (list.Count <= 0)
{
return(new
{
code = "failed",
msg = "设定资料不存在"
});
}
dynamic info = list.First();
var metainfo = (from t in DB.LamdaTable(up, "EXTEND_METADATA", "a")
where t.MetaUID == info.MetaUID && t.IsCreated == 1
select new
{
table_name = t.MetaName
}).GetQueryList(up);
if (metainfo.Count <= 0)
{
return(new
{
code = "failed",
msg = "元数据表不存在，无法继续进行操作"
});
}
var table_name = ComFunc.nvl(metainfo.First().GetValue("table_name"));
//组织查询表达式
FrameDLRObject express = FrameDLRObject.CreateInstance($@"{{
$acttype : 'QueryByPage',
$orderby : 'sort_no',
$table:'{table_name}',
sort_no:true
}}", EFFC.Frame.Net.Base.Constants.FrameDLRFlags.SensitiveCase);
#region 获取查询条件信息
var conditionlist = (from t in DB.LamdaTable(up, "EXTEND_SINGLETABLE_OP_CONDITIONS", "a")
join t2 in DB.LamdaTable(up, "EXTEND_METADATA_COLUMNS", "b").LeftJoin() on t.S_ColumnName equals t2.MetaColumnName
where t.S_UID == info.S_UID && t2.MetaUID == info.MetaUID
select new
{
op = t.S_ConditionOP,
column_name = t.S_ColumnName,
parameter_name = t.S_Parameter,
data_type = t2.MetaDataType
}).GetQueryList(up);
FrameDLRObject where_express = FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase);
foreach (dynamic citem in conditionlist)
{
if (ComFunc.nvl(QueryString[citem.parameter_name]) == "")
{
continue;
}
var value = ConvertConditionValue(citem.data_type, ComFunc.nvl(QueryString[citem.parameter_name]));
FrameDLRObject exp = where_express.GetValue(citem.column_name);
if (exp == null)
{
exp = FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase);
}
switch (citem.op)
{
case "like":
exp.SetValue("$like", value);
where_express.SetValue(citem.column_name, exp);
break;
case "=":
where_express.SetValue(citem.column_name, value);
break;
case ">":
exp.SetValue("$gt", value);
where_express.SetValue(citem.column_name, exp);
break;
case ">=":
exp.SetValue("$gte", value);
where_express.SetValue(citem.column_name, exp);
break;
case "<":
exp.SetValue("$lt", value);
where_express.SetValue(citem.column_name, exp);
break;
case "<=":
exp.SetValue("$lte", value);
where_express.SetValue(citem.column_name, exp);
break;
}
}
if (where_express.Items.Count > 0)
{
express.SetValue("$where", where_express);
}
#endregion
#region 组织select栏位
var columns = (from t in DB.LamdaTable(up, "EXTEND_SINGLETABLE_OP_COLUMNS", "a")
where t.S_UID == info.S_UID
select new
{
column_name = t.S_ColumnName
}).GetQueryList(up);
foreach (dynamic item in columns)
{
express.SetValue(item.column_name, true);
}
#endregion
var result = DB.Excute(up, express, true);
return(new
{
code = "success",
msg = "",
total_count = result.TotalRow,
page = result.CurrentPage,
total_page = result.TotalPage,
limit = result.Count_Of_OnePage,
data = result.QueryData <FrameDLRObject>().Select((d) =>
{
FrameDLRObject dobj = (FrameDLRObject)d;
dobj.Remove("RowNumber");
//时间类型需要转string
foreach (var item in dobj.Items)
{
if (item.Value is DateTime)
{
dobj.SetValue(item.Key, DateTimeStd.IsDateTimeThen(item.Value));
}
}
return dobj;
})
});
}
object ReportRetails_Users_Plat(string report_uid)
{
SetCacheEnable(false);
string user_uid = ComFunc.nvl(TokenPayLoad["no"]);
var up = DB.NewDBUnitParameter();
var list = (from t in DB.LamdaTable(up, "RetailSummaryPerDay", "a")
where t.uid == report_uid
select t).GetQueryList(up);
if (list.Count <= 0)
{
return(new
{
code = "failed",
msg = "没有该报表的资料"
});
}
dynamic info = list.First();
var dt = DateTimeStd.IsDateTimeThen(info.log_time, "yyyy-MM-dd");
var s = from t in DB.LamdaTable(up, "APPExchange", "a")
join t2 in DB.LamdaTable(up, "UserInfo", "b").LeftJoin() on t.mobile equals t2.mobile
where t2.recommend_uid == info.retail_uid && t.log_time >= $"{dt} 00:00:00" && t.log_time <= $"{dt} 23:59:59"
select new
{
t.log_time,
t.mobile,
t2.name,
t.is_deal,
t.is_register,
t.is_perfection,
t.deal_amount
};
var result = s.QueryByPage(up, "log_time desc");
var tdata = from t in result.QueryData <FrameDLRObject>()
select new
{
log_time = DateTimeStd.IsDateTimeThen(t.log_time, "yyyy-MM-dd HH:mm:ss"),
mobile = ComFunc.nvl(t.mobile),
name = ComFunc.nvl(t.name).Length > 0 ? ComFunc.AESDecrypt(ComFunc.nvl(t.name)) : "",
is_deal = BoolStd.IsNotBoolThen(t.is_deal),
is_register = BoolStd.IsNotBoolThen(t.is_register),
is_perfection = BoolStd.IsNotBoolThen(t.is_perfection),
t.deal_amount
};
return(new
{
code = "success",
msg = "",
total_count = result.TotalRow,
page = result.CurrentPage,
total_page = result.TotalPage,
limit = result.Count_Of_OnePage,
data = from t in tdata
select new
{
t.log_time,
mobile = ComFunc.nvl(t.mobile).Length > 3 ? $"{ ComFunc.nvl(t.mobile).Substring(0, 3)}***{ComFunc.nvl(t.mobile).Substring(ComFunc.nvl(t.mobile).Length - 2, 2)}" : "",
name = $"{(ComFunc.nvl(t.name).Length > 0 ? ComFunc.nvl(t.name).Substring(0, 1) : "")}***",
t.is_deal,
t.is_register,
t.is_perfection,
t.deal_amount
}
});
}
object Search()
{
SetCacheEnable(false);
var no = ComFunc.nvl(QueryStringD.no);
var userno = ComFunc.nvl(QueryStringD.userno);
var status = ComFunc.nvl(QueryStringD.status);
var start_time = DateTimeStd.IsDateTimeThen(QueryStringD.start_time, "yyyy-MM-dd HH:mm:ss");
var end_time = DateTimeStd.IsDateTimeThen(QueryStringD.end_time, "yyyy-MM-dd HH:mm:ss");
var sortcolumn = ComFunc.nvl(QueryStringD.sortcolumn);
var sort = ComFunc.nvl(QueryStringD.sort);
//当前页超过末页时，自动显示最后一页的资料
DB.Is_Auto_To_Last_Page = true;
var up = DB.NewDBUnitParameter();
var s = from t in DB.LamdaTable("ecs_forpay", "a")
join t2 in DB.LamdaTable("ecs_users", "b") on t.user_id equals t2.user_id
where t.notnull(no, t.forpay_no.contains(no)) &&
t2.notnull(userno, (t2.user_name.contains(userno) || t2.company_name.contains(userno) || t2.customer_num.contains(userno))) &&
t.notnull(status, t.status == status) &&
t.notnull(start_time, t.add_time >= start_time) &&
t.notnull(end_time, t.add_time <= end_time)
select new
{
t.uid,
t.forpay_no,
t2.company_name,
t.foruse,
t.charge_amount,
t.charge_type,
t.bank_name,
t.bank_no,
t.trans_seq_no,
t.add_time,
t.status,
t.uploadfile_name
};
var sortstr = "forpay_no desc";
var sortrtn = "";
var dicsort = new Dictionary <string, string>();
dicsort.Add("seq_no", "forpay_no");
dicsort.Add("company_name", "company_name");
dicsort.Add("add_time", "add_time");
dicsort.Add("foruse", "foruse");
dicsort.Add("charge_type", "charge_type");
dicsort.Add("charge_amount", "charge_amount");
dicsort.Add("bank_name", "bank_name");
dicsort.Add("bank_no", "bank_no");
dicsort.Add("trans_seq_no", "trans_seq_no");
dicsort.Add("status", "status");
if (sortcolumn != "" && dicsort.ContainsKey(sortcolumn))
{
sortstr = dicsort[sortcolumn] + (sort == "" ? "" : " " + sort);
sortrtn = sortcolumn + "#" + (sort == "" ? "asc" : sort);
}
var result = DB.LamdaQueryByPage(up, s, sortstr);
var dic = new Dictionary <string, string>();
dic.Add("Create", "待审核");
dic.Add("Review", "已审核");
dic.Add("Success", "充值成功");
dic.Add("Failed", "充值失败");
return(new
{
code = "success",
totalrow = result.TotalRow,
totalpage = result.TotalPage,
currentpage = result.CurrentPage,
pagesize = result.Count_Of_OnePage,
data = from t in result.QueryData <FrameDLRObject>()
select new
{
t.uid,
seq_no = t.forpay_no,
charge_amount = "￥" + DecimalStd.IsNotDecimalThen(t.charge_amount).ToString("###,###.##"),
t.charge_type,
t.bank_name,
t.bank_no,
t.trans_seq_no,
t.company_name,
t.foruse,
add_time = DateTimeStd.IsDateTimeThen(t.add_time, "yyyy-MM-dd HH:mm:ss"),
status = dic.ContainsKey(t.status) ? dic[t.status] : "",
filename = t.uploadfile_name
},
sort = sortrtn
});
}
object QueryPO()
{
SetCacheEnable(false);
string start_date = ComFunc.nvl(QueryStringD.start_date);
string end_date = ComFunc.nvl(QueryStringD.end_date);
if (!IsValidBy("日期格式不正确", () =>
{
return((start_date == "" || DateTimeStd.IsDateTime(start_date)) && (end_date == "" || DateTimeStd.IsDateTime(end_date)));
}))
{
return(null);
}
var up = DB.NewDBUnitParameter();
var s = from t in DB.LamdaTable("HW_PO", "a")
where t.notnull(start_date, t.accepted_date >= DateTimeStd.IsDateTimeThen(start_date)) &&
t.notnull(end_date, t.accepted_date <= DateTimeStd.IsDateTimeThen(end_date)) &&
t.is_processed == 0
select t;
var result = DB.LamdaQueryByPage(up, s, "hw_contract_no");
var polist = (from t in result.QueryData <FrameDLRObject>()
select new
{
t.hw_contract_no,
t.cust_contract_no,
t.version,
t.status,
t.hw_frame_contract_no,
t.cust_frame_contract_no,
t.hw_sign_entity,
t.cust_sign_entity,
t.cust_type_lv1,
t.cust_type_lv2,
t.trade_terms,
t.transport_mode,
t.payment_terms,
t.is_vat_included,
contract_amount_cny = DoubleStd.IsDouble(t.contract_amount_cny) ? DoubleStd.ParseStd(t.contract_amount_cny).Value : 0.0,
contract_amount_usd = DoubleStd.IsDouble(t.contract_amount_usd) ? DoubleStd.ParseStd(t.contract_amount_usd).Value : 0.0,
t.retail_sample,
t.created_by,
created_date = DateTimeStd.IsDateTimeThen(t.created_date, "yyyy-MM-dd HH:mm:ss"),
accepted_date = DateTimeStd.IsDateTimeThen(t.accepted_date, "yyyy-MM-dd HH:mm:ss"),
review_completed_date = DateTimeStd.IsDateTimeThen(t.review_completed_date, "yyyy-MM-dd HH:mm:ss"),
signed_date = DateTimeStd.IsDateTimeThen(t.signed_date, "yyyy-MM-dd HH:mm:ss")
}).ToList();
foreach (dynamic item in polist)
{
var sline = from t in DB.LamdaTable("HW_PO_Line", "a")
where t.hw_contract_no == item.hw_contract_no
select new
{
t.po_line_no,
t.line_type,
t.prod_code_cust,
t.prod_desc_cust,
t.prod_code_sale,
t.prod_desc_hw,
t.prod_model,
t.color,
t.quantity,
t.currency,
t.unit_price_cny,
t.line_amount_cny,
t.line_amount_usd
};
item.po_line = DB.ExcuteLamda(up, sline).QueryData <object>();
var sfee = from t in DB.LamdaTable("HW_PO_Fee", "a")
where t.hw_contract_no == item.hw_contract_no
select new
{
t.line_no,
t.line_type,
t.desc,
t.prod_model,
t.amount,
t.comments
};
item.other_fee = DB.ExcuteLamda(up, sfee).QueryData <object>();
}
return(new
{
data = polist,
total_num = result.TotalRow,
current_page = result.CurrentPage,
total_page = result.TotalPage,
page_size = result.Count_Of_OnePage
});
}
public override object post()
{
var estate_id = ComFunc.nvl(PostDataD.estate);
var position_id = ComFunc.nvl(PostDataD.position_id);
var device_id = ComFunc.nvl(PostDataD.device_id);
var occur_time = DateTimeStd.IsDateTimeThen(PostDataD.occur_time, "yyyy-MM-dd HH:mm:ss");
var plate_no = ComFunc.nvl(PostDataD.plate_no);
var plate_color = ComFunc.nvl(PostDataD.plate_color);
var vertical_color = ComFunc.nvl(PostDataD.vertical_color);
string pic = ComFunc.nvl(PostDataD.file_content).Replace(" ", "+");
string file_name = ComFunc.nvl(PostDataD.file_name);
long file_length = Int64Std.IsNotInt64Then(PostDataD.file_length);
BeginTrans();
var up = DB.NewDBUnitParameter();
if (!(from t in DB.LamdaTable(up, "IC_Position")
where t.P_GUID == position_id
select t).IsExists(up))
{
return(new
{
code = "failed",
msg = "不是合法的位置信息"
});
}
if (!(from t in DB.LamdaTable(up, "IC_Device")
where t.D_UID == device_id
select t).IsExists(up))
{
return(new
{
code = "failed",
msg = "不是合法的设备"
});
}
if (!(from t in DB.LamdaTable(up, "IC_Estate")
where t.E_ID == estate_id
select t).IsExists(up))
{
return(new
{
code = "failed",
msg = "小区信息不正确"
});
}
if (!ComFunc.IsBase64(pic) || file_length == 0)
{
return(new
{
code = "failed",
msg = "抓拍图片格式不正确"
});
}
if (occur_time == "")
{
return(new
{
code = "failed",
msg = "抓拍时间格式不正确"
});
}
var uid = Guid.NewGuid().ToString();
DB.QuickInsert(up, "IC_Car_Monitor", new
{
CM_GUID = uid,
CM_Estate_ID = estate_id,
CM_Occur_Time = occur_time,
CM_Position_XY = position_id,
CM_Device = device_id,
CM_Pic1 = "",
CM_IsProcessed = 0,
add_id = TokenPayLoad.ID,
add_name = ComFunc.nvl(TokenPayLoad["username"]),
add_ip = ClientInfo.IP,
add_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
last_id = TokenPayLoad.ID,
last_name = ComFunc.nvl(TokenPayLoad["username"]),
last_ip = ClientInfo.IP,
last_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
});
CommitTrans();
return(new
{
code = "success",
msg = "",
uid
});
}