/// <summary> /// 添加一个:增加一个新的记录.返回受影响行数 /// </summary> /// <param name="para">新记录实体</param> /// <returns></returns> public static int Add(AssetM para) { string insert = @"INSERT INTO Asset (id,title,code,amount,value,positions,price,remark,risk,profit,excorg,kind,valuedate,expdate,rate,action,ctime,itemCode,status)"; SQLServer db = new SQLServer(); return(db.Insert <AssetM>(insert, para)); }
/// <summary> /// 列表:查找出符合条件的多个记录.如果没找到返回空列表 /// </summary> /// <param name="para">查询条件参数</param> /// <returns></returns> public static List <AssetM> List(AssetM para) { AssetM[] data; if (para.IsPagePart == 0) { data = AssetDal.All(para); } else { // 若分页则要验证分页参数 para.SetPageIndexAndSize(); data = AssetDal.List(para); } if (data == null) { para.ErrorMsg = AlertMsg.没有数据.ToString(); return(new List <AssetM>()); } for (int i = 0; i < data.Length; i++) { AssetM item = data[i]; item.RowNumber = para.IsPagePart == 0 ? 1 + i : para.StartRowIndex + i; } return(data.ToList()); }
/// <summary> /// 查找一个资产的所有更新记录.根据资产id /// </summary> /// <param name="para"></param> /// <returns></returns> public static List <AssetM> HistoryList(AssetM para) { if (string.IsNullOrWhiteSpace(para.Id)) { para.ErrorMsg = "资产id无效"; return(new List <AssetM>()); } bool hasItemcode = AssetItemCode(para); if (hasItemcode == false) { para.ErrorMsg = "资产itemCode无效"; return(new List <AssetM>()); } string sql = $@" SELECT id,title,code,amount,positions,price,value,remark,profit,risk,excorg,kind,valuedate,expdate,rate,action,ctime,itemCode,CASE WHEN status=1 THEN 1 ELSE 0 END [enabled] FROM Asset WHERE itemCode=@itemCode ORDER BY ctime DESC"; SQLServer db = new SQLServer(); AssetM[] data = db.ExecuteQuery <AssetM, AssetM>(sql, para); if (data == null) { para.ErrorMsg = AlertMsg.没有数据.ToString(); return(new List <AssetM>()); } return(data.ToList()); }
/// <summary> /// 数据:查找出符合条件的所有记录 /// </summary> /// <param name="para">查询条件参数</param> /// <returns></returns> public static AssetM[] All(AssetM para) { StringBuilder sb = new StringBuilder(); // 条件:市值大于0的,如果市值为0,说明已经清仓 sb.Append("AND value > 0"); string where = sb.ToString(); // 先排除禁用的记录,再开窗 string sql = $@" SELECT id,title,code,amount,value,positions,price,remark,profit,risk,excorg,kind,valuedate,expdate,rate,action,ctime,itemCode,status FROM( SELECT *,ROW_NUMBER() OVER(PARTITION BY itemCode ORDER BY ctime DESC) rn FROM( SELECT * FROM Asset WHERE status=1 ) b ) s WHERE s.rn=1 {where} ORDER BY s.ctime DESC"; // SQLServer db = new SQLServer(); // 数据列表 AssetM[] data = db.ExecuteQuery <AssetM, AssetM>(sql, para); return(data ?? null); }
public async Task Add() { AssetM para = this.ParaForm <AssetM>(); AssetBll.Add(para); await this.Json(new { errcode = para.ErrorCode, errmsg = para.ErrorMsg }); }
/// <summary> /// 数据(分页):查找出符合条件的多个记录 /// </summary> /// <param name="para">查询条件参数</param> /// <returns></returns> public static AssetM[] List(AssetM para) { StringBuilder sb = new StringBuilder("1=1"); string where = sb.ToString(); string sql = $@"SELECT id,title,code,amount,value,positions,price,remark,profit,excorg,risk,kind,valuedate,expdate,rate,action,ctime,itemCode FROM Asset WHERE {where} ORDER BY ctime DESC OFFSET @OffSetRows ROWS FETCH NEXT @PageSize ROWS ONLY"; string sqlcount = $@"SELECT COUNT(id) FROM Asset WHERE {where}"; // SQLServer db = new SQLServer(); // 总条数(如果为0无需再查询) int count = 0; var listcount = db.ExecuteScalar <AssetM>(sqlcount, para); if (listcount == null || !int.TryParse(listcount.ToString(), out count) || count == 0) { return(null); } para.ListCount = count; // 数据列表 AssetM[] data = db.ExecuteQuery <AssetM, AssetM>(sql, para); return(data); }
/// <summary> /// add 验证表单数据 /// </summary> /// <returns></returns> private static bool FormCheck(AssetM data) { if (AssetItemCode(data) == false) { data.ErrorMsg = $"{data.Title}的itemCode获取失败."; return(false); } string verrmsg = Validate.CheckEntity <AssetM>(data); if (verrmsg != null) { data.ErrorMsg = verrmsg; return(false); } foreach (string item in new string[] { data.Action, data.ExcOrg, data.Kind }) { if (KeyValBll.CheckByCode(item) == false) { data.ErrorMsg = $"{item}无效."; return(false); } } // return(true); }
/// <summary> /// 更新一个:查找指定ID(主键)的一个记录,然后更新之.返回受影响行数 /// </summary> /// <param name="para">新记录实体</param> /// <returns></returns> public static int UpdateById(AssetM para) { StringBuilder sb = new StringBuilder("1=1"); string where = sb.ToString(); string update = $@"UPDATE Asset (id,title,code,amount,value,positions,price,remark,risk,profit,excorg,kind,valuedate,expdate,rate,action,ctime,itemCode) WHERE id=@id and {where}"; SQLServer db = new SQLServer(); return(db.Update <AssetM>(update, para)); }
/// <summary> /// 总值更新只在每天23.50分前操作 /// </summary> /// <param name="para"></param> private static void TotalUp1_LastTime(AssetM para) { // 为了不跨天,总值更新只在每天23.50分前操作 string limitTime = DateTimeOffset.Now.ToString("yyyy/MM/dd 23:50:00"); if (DateTimeOffset.Now > DateTimeOffset.Parse(limitTime)) { para.ErrorCode = 301; para.ErrorMsg = AlertMsg.当前时段禁止该操作.ToString(); } }
/// <summary> /// 历史最大最小总值.0位是大,1位是小 /// </summary> /// <returns></returns> public static AssetM[] ValueMaxMinOfHis() { string max = @"SELECT top 1 value,totaldate FROM totalday ORDER BY value DESC"; string min = @"SELECT top 1 value,totaldate FROM totalday ORDER BY value"; SQLServer db = new SQLServer(); var maxdata = db.ExecuteQuery <AssetM>(max); var mindata = db.ExecuteQuery <AssetM>(min); AssetM[] redata = new AssetM[2]; redata[0] = maxdata?[0]; redata[1] = mindata?[0]; return(redata); }
/// <summary> /// 补录情况 /// </summary> /// <param name="para"></param> private static void TotalUp2_BuLu(AssetM para) { // 查询最后更新资产日期 DateTimeOffset lastUpDay = AssetDal.GetLastUpDay(); // 表里没有有效更新数据,这种情况也不更新总值. if (lastUpDay == default) { para.ErrorCode = 302; para.ErrorMsg = "没有任何更新记录,不可更新总值!"; return; } // 如果日期不是今天,(也就是今天没有更新过资产).再查询这个日期的总值记录, // 如果没有记录,那么补录(总值日期为这天). // 如果有记录,那么不可以更新. int lastday = int.Parse(lastUpDay.ToString("yyyyMMdd")); if (lastday < int.Parse(DateTimeOffset.Now.ToString("yyyyMMdd"))) { AssetM totalByDay = TotalDayDal.GetLastValue(lastday); if (totalByDay == null) { // 补录 AssetM totalVal = ValueTotal(); if (totalVal.ErrorCode != 200) { para.ErrorCode = 304; para.ErrorMsg = "补录失败,获取总值出错!"; return; } para.Id = RandHelp.NewGuid(); para.Value = totalVal.Value; para.Ctime = DateTimeOffset.Now; para.TotalDate = lastday; TotalDayDal.UpdateVal(para); if (para.ErrorCode == 200) { para.ErrorCode = 201; para.ErrorMsg = "补录成功!"; } } else { para.ErrorCode = 303; para.ErrorMsg = "今天没更新资产,不可更新总值!"; } } // }
public void HistoryList() { AssetM para = new AssetM(); para.Id = "5ce71c0d131f46cf9c36d1d68e48b763"; List <AssetM> data = AssetBll.HistoryList(para); foreach (var item in data) { log.WriteLine(item.Title); log.WriteLine(item.Ctime.ToString()); log.WriteLine(item.ItemCode); log.WriteLine("-------------"); } }
/// <summary> /// 一个:查找指定ID(主键)的一个记录 /// </summary> /// <param name="id">主键ID</param> /// <returns></returns> public static AssetM GetById(AssetM para) { StringBuilder sb = new StringBuilder("1=1"); string where = sb.ToString(); string sql = $@"SELECT id,title,code,amount,value,positions,price,remark,risk,profit,excorg,kind,valuedate,expdate,rate,action,ctime,itemCode,status FROM Asset WHERE id=@id and {where}"; // SQLServer db = new SQLServer(); // 数据列表 AssetM[] data = db.ExecuteQuery <AssetM>(sql, para.Id, 1); return(data?[0]); }
/// <summary> /// ItemCode值标识同一个资产的更新周期,从首次添加起到清仓止,首次添加资产时生成. /// 以后更新这个资产时会传来资产Id,据此查得ItemCode值. /// 举例说明:510300这个资产,在第一次添加时生成ItemCode "xxx",后续更新资产时, /// 此值都是"xxx",一直到清仓都是. /// 假如下一次又添加510300时,会生成新的ItemCode "xxx2". /// </summary> /// <param name="data"></param> private static bool AssetItemCode(AssetM data) { if (string.IsNullOrWhiteSpace(data.Id)) { data.ItemCode = RandHelp.NewGuid(); return(true); } string sql = "SELECT itemCode FROM Asset WHERE Id=@Id"; SQLServer db = new SQLServer(); object itemcode = db.ExecuteScalar(sql, data.Id, 1); if (itemcode == null || string.IsNullOrWhiteSpace(itemcode.ToString())) { return(false); } data.ItemCode = itemcode.ToString(); return(true); }
public async Task Item() { var para = this.ParaForm <AssetM>(); AssetM data = AssetBll.GetById(para); if (data == null) { await this.Json(new { errmsg = para.ErrorMsg, errcode = para.ErrorCode }); return; } // 返回字段 var redata = new { data.Id, data.Title, data.Code, data.Amount, data.Value, data.Positions, data.Price, data.Remark, data.Profit, data.ExcOrg, data.Kind, data.Risk, ValueDate = data.ValueDate.ToString("yyyy/MM/dd HH:mm"), ExpDate = data.ExpDate.ToString("yyyy/MM/dd HH:mm"), data.Rate, data.Action, Ctime = data.Ctime.ToString("yyyy/MM/dd HH:mm"), data.ItemCode }; // 按需字段列表 if (!string.IsNullOrWhiteSpace(para.Fields) && para.Fields.Split(',').Length > 0) { var list = SerializeHelp.ObjectToDict(redata, para.Fields.Split(',')); await this.Json(new { list, errcode = ErrCode.Success }); return; } await this.Json(new { item = redata, errcode = ErrCode.Success }); }
public async Task TotalUp() { AssetM data = AssetBll.TotalUp(); if (data.ErrorCode >= 300) { await this.Json(new { errcode = data.ErrorCode, errmsg = data.ErrorMsg }); return; } var redata = new { date = data.TotalDate, time = data.Ctime.ToString("yyyy/MM/dd HH:mm:ss"), errcode = data.ErrorCode }; await this.Json(redata); }
/// <summary> /// 返回所有资产的标题.按资产itemcode分组,返回title,id(其中一个记录的id) /// </summary> /// <param name="para"></param> /// <returns></returns> public static List <AssetM> HistoryTitles(AssetM para) { string sql = $@" SELECT a.title,a.id FROM ( SELECT *,ROW_NUMBER() OVER(PARTITION BY itemCode ORDER BY ctime DESC) rn FROM Asset ) a WHERE a.rn=1"; SQLServer db = new SQLServer(); AssetM[] data = db.ExecuteQuery <AssetM, AssetM>(sql, para); if (data == null) { para.ErrorMsg = AlertMsg.没有数据.ToString(); return(new List <AssetM>()); } return(data.ToList()); }
/// <summary> /// 新增资产 资产id设置在para.id,成功返回true,失败返回false /// </summary> /// <param name="para"></param> /// <returns></returns> public static void Add(AssetM para) { if (FormCheck(para) == false) { return; } // para.Id = RandHelp.NewGuid(); para.Ctime = DateTime.Now; para.Status = 1; // string sql = "INSERT INTO Asset(id,title,code,amount,value,positions,price,risk,remark,profit,excorg,kind,valuedate,expdate,rate,action,ctime,itemCode,status)"; SQLServer db = new SQLServer(); int re = db.Insert <AssetM>(sql, para); if (re == 1) { para.ErrorCode = 200; } }
/// <summary> /// 更新总值 /// </summary> /// <param name="para"></param> public static void UpdateVal(AssetM para) { para.ErrorCode = 305; SQLServer db = new SQLServer(); string del = @"delete totalday where totaldate=@totaldate"; string sql = @"insert into totalday(id,value,totaldate,ctime)"; db.BeginTransaction(); if (db.ExecuteNoQuery(del, para.TotalDate, 1) < 0 || db.Insert <AssetM>(sql, para) != 1) { db.RollBackTransaction(); para.ErrorMsg = AlertMsg.数据库错误.ToString(); return; } if (db.CommitTransaction()) { para.ErrorCode = 200; return; } para.ErrorMsg = AlertMsg.数据库错误.ToString(); }
public async Task Statistic() { AssetM data = AssetBll.ValueTotal(); if (data.ErrorCode != 200) { await this.Json(new { errcode = 301, errmsg = data.ErrorMsg }); return; } // 风险等级 Dictionary <string, object>[] byRisk = AssetBll.ValueTotalByRisk(); // 机构 List <AssetM> byExcOrg = AssetBll.ValueTotalByExcOrg(); // 资产类型 List <AssetM> byKind = AssetBll.ValueTotalByKind(); // 近30更新日总值 List <AssetM> lasttotal30 = AssetBll.Last30TotalVal(); // 历史最高/低 AssetM[] maxmin = AssetBll.ValueMaxMinOfHis(); var redata = new { data.Value, byRisk = data.Value == 0 ? null : byRisk, byExcOrg = byExcOrg == null ? null : SerializeHelp.ObjectsToDicts(byExcOrg, nameof(AssetM.ExcOrg), nameof(AssetM.Value)), byKind = byKind == null ? null : SerializeHelp.ObjectsToDicts(byKind, nameof(AssetM.Kind), nameof(AssetM.Value)), lasttotal30 = lasttotal30 == null ? null : SerializeHelp.ObjectsToDicts(lasttotal30, nameof(AssetM.Value), nameof(AssetM.TotalDate)), max = maxmin[0] == null ? null : new { maxmin[0].Value, maxmin[0].TotalDate }, min = maxmin[1] == null ? null : new { maxmin[1].Value, maxmin[1].TotalDate }, errcode = data.ErrorCode }; await this.Json(redata); }
/// <summary> /// 更新总资产数据表 /// </summary> /// <returns></returns> public static AssetM TotalUp() { AssetM m = new AssetM(); // 1.过时检查 TotalUp1_LastTime(m); if (m.ErrorCode > 300) { return(m); } // 2.补录情况 TotalUp2_BuLu(m); if (m.ErrorCode > 300) { return(m); } // 2.1补录成功情况 if (m.ErrorCode == 201) { return(m); } // 写入数据 AssetM totalVal = ValueTotal(); if (totalVal.ErrorCode != ErrCode.Success) { m.ErrorCode = 306; m.ErrorMsg = "更新失败,获取总值出错!"; return(m); } m.Id = RandHelp.NewGuid(); m.Value = totalVal.Value; m.Ctime = DateTimeOffset.Now; m.TotalDate = int.Parse(DateTimeOffset.Now.ToString("yyyyMMdd")); TotalDayDal.UpdateVal(m); return(m); }
/// <summary> /// 一个:查找指定ID(主键)的一个记录.如果没找到返回null /// </summary> /// <param name="id">主键ID</param> /// <returns></returns> public static AssetM GetById(AssetM para) { return(AssetDal.GetById(para)); }