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