/// <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>
/// 查找一个资产的所有更新记录.根据资产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>
/// 计算按风险等级分组的总市值.含字段:risk, value ,Comment
/// </summary>
/// <returns></returns>
public static Dictionary <string, object>[] ValueTotalByRisk()
{
string sql = $@"
SELECT kv.title Risk,s.Value,kv.Comment
FROM(
SELECT title,id,comment FROM keyval
WHERE category={(int)KVEnumKind.风险等级}) kv
LEFT JOIN(
SELECT risk,SUM(value) value,SUM(profit) profit
FROM(
SELECT *,ROW_NUMBER() OVER(PARTITION BY itemCode ORDER BY ctime DESC) rn
FROM(
SELECT * FROM Asset WHERE status=1
) a
) b
WHERE b.rn=1 and b.value>0
GROUP BY b.risk) s
ON kv.id=s.risk
ORDER BY kv.title";
SQLServer db = new SQLServer();
var data = db.ExecuteQuery(sql);
if (data != null)
{
foreach (var item in data)
{
if (item["Value"] == DBNull.Value)
{
item["Value"] = 0;
}
}
}
return(data);
}
/// <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);
}
/// <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>
/// 数据:查找出符合条件的所有记录
/// </summary>
/// <param name="para">查询条件参数</param>
/// <returns></returns>
public static List <KeyvalM> All(KeyvalM para)
{
StringBuilder sb = new StringBuilder("1=1");
if (para.Category > 0)
{
sb.Append(" and category=@category");
}
string where = sb.ToString();
string sql = $@"
SELECT id,title,category,comment,ctime,CASE WHEN status=1 THEN 1 ELSE 0 END [enabled]
FROM Keyval
WHERE {where}
ORDER BY orderby , ctime DESC";
//
SQLServer db = new SQLServer();
// 数据列表
KeyvalM[] data = db.ExecuteQuery <KeyvalM, KeyvalM>(sql, para);
if (data == null)
{
return(new List <KeyvalM>());
}
para.ErrorCode = ErrCode.Success;
return(data.ToList());
}
static void Main(string[] args)
{
string word = "fast";
string character = Convert.ToString(word[0]);
var ss = new SQLServer();
var ok = ss.Connect("localhost", "sqlexpress", "EdDb");
var students = ss.ExecuteQuery("SELECT * From Student;");
ss.Disconnect();
}
/// <summary>
/// 获取最近30个总值数据,(用于首页显示)
/// </summary>
/// <returns></returns>
public static List <AssetM> Last30TotalVal()
{
string sql = @"SELECT TOP 31 value,totaldate FROM totalday ORDER BY totaldate DESC";
SQLServer db = new SQLServer();
var data = db.ExecuteQuery <AssetM>(sql);
if (data == null)
{
return(null);
}
return(data.ToList());
}
/// <summary>
/// 获取最新日期总值.如果无值返回null
/// 如果指定日期,则获取指定日期的总值
/// </summary>
/// <returns></returns>
public static AssetM GetLastValue(int date = 0)
{
string where = "";
if (date != 0)
{
where = "where totaldate=@totaldate";
}
SQLServer db = new SQLServer();
string last = $@"SELECT TOP 1 value,totaldate FROM totalday {where}
order by totaldate desc";
AssetM[] lastVal = db.ExecuteQuery <AssetM>(last, date);
return(lastVal?[0]);
}
/// <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分组,返回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>
/// 计算按资产种类的市值.含字段:kind, value
/// </summary>
/// <returns></returns>
public static List <AssetM> ValueTotalByKind()
{
string sql = $@"
SELECT kv.title kind,s.value FROM(
SELECT kind, SUM(value) value,SUM(profit) profit
FROM(
SELECT *,ROW_NUMBER() OVER(PARTITION BY itemCode ORDER BY ctime DESC) rn
FROM(
SELECT * FROM Asset WHERE status=1
) a
) b
WHERE b.rn=1 and b.value>0
GROUP BY b.kind) s
JOIN KeyVal kv
ON kv.id=s.kind";
SQLServer db = new SQLServer();
var data = db.ExecuteQuery <AssetM>(sql);
if (data == null)
{
return(null);
}
return(data.ToList());
}
/// <summary>
/// 计算当前(最后一次更新后)总资产.含字段: value
/// </summary>
/// <returns></returns>
public static AssetM ValueTotal()
{
string sql = $@"
SELECT SUM(value) value
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 AND s.value>0";
SQLServer db = new SQLServer();
var data = db.ExecuteQuery <AssetM>(sql);
if (data == null)
{
return(new AssetM()
{
ErrorMsg = AlertMsg.没有数据.ToString()
});
}
data[0].ErrorCode = 200;
return(data[0]);
}