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 }); }