/// <summary>
/// 删除日志
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
public static int Delete(long timestamp)
{
string sql = "detele from app_log where create_timestamp < {0}";
sql = string.Format(sql, timestamp);
return(MysqlHelper.Execute(sql));
}
/// <summary>
/// 插入
/// </summary>
/// <param name="appLog"></param>
/// <returns></returns>
public static int Insert(AppLog appLog)
{
string sql = "insert into app_log (create_time, create_timestamp, log_type, lottery_code, data_source, log_message)"
+ "values(@create_time, @create_timestamp, @log_type, @lottery_code, @data_source, @log_message)";
return(MysqlHelper.Execute(sql, appLog));
}
/// <summary>
/// 获取彩种采集详情
/// </summary>
/// <param name="lotteryCode"></param>
/// <returns></returns>
public static List <LotteryPickDetail> GetPickDetail(string lotteryCode)
{
string sql = $@"select c.code Code, ifnull(b.TodayCount, 0) TodayCount, ifnull(a.TotalCount, 0) TotalCount from data_source c
left join (select data_source, count(1) TotalCount from open_result where lottery_code = '{lotteryCode}' group by data_source) a on a.data_source = c.code
left join (select data_source, count(1) TodayCount from open_result where lottery_code = '{lotteryCode}' and create_timestamp >= {DateTime.Now.Date.ToTimestamp()} group by data_source) b
on b.data_source = c.code";
return(MysqlHelper.GetList <LotteryPickDetail>(sql).ToList());
}
/// <summary>
/// 获取采集数量
/// </summary>
/// <param name="codeList"></param>
/// <returns></returns>
public static List <PickCount> GetPickCount(string codeList)
{
string sql = $@"select c.code Code, ifnull(b.TodayCount, 0) TodayCount, ifnull(a.TotalCount, 0) TotalCount from lottery c
left join (select lottery_code, count(1) TotalCount from open_result a where lottery_code in ({codeList}) group by lottery_code) a on a.lottery_code = c.code
left join (select lottery_code, count(1) TodayCount from open_result where lottery_code in ({codeList}) and create_timestamp >= {DateTime.Now.Date.ToTimestamp()} group by lottery_code) b
on b.lottery_code = c.code where c.code in ({codeList})";
return(MysqlHelper.GetList <PickCount>(sql).ToList());
}
/// <summary>
/// 批量插入
/// </summary>
/// <param name="resultList"></param>
/// <returns></returns>
public static int Insert(List <OpenResult> resultList)
{
if (resultList.Count == 0)
{
return(1);
}
string sql = "insert into open_result (create_time, create_timestamp, open_time, open_timestamp, lottery_code, issue_number, open_data, data_source, is_legal) "
+ "values (@create_time, @create_timestamp, @open_time, @open_timestamp, @lottery_code, @issue_number, @open_data, @data_source, @is_legal)";
return(MysqlHelper.Execute(sql, resultList.Where(o => !Exists(o.issue_number, o.lottery_code, o.data_source)).OrderBy(o => o.issue_number)));
}
/// <summary>
/// 取某一个彩种最新的开奖结果
/// </summary>
/// <param name="lotteryType"></param>
/// <returns></returns>
public static List <OpenResult> GetNewestResult(string lotteryType)
{
string sql = $"select issue_number, open_time, open_data from open_result where lottery_code = '{lotteryType}' group by issue_number order by issue_number desc limit 20";
return(MysqlHelper.GetList <OpenResult>(sql).ToList());
}
/// <summary>
/// 修改状态
/// </summary>
/// <param name="id"></param>
/// <param name="status"></param>
/// <returns></returns>
public static int UpdateStatus(string id, int status)
{
string sql = $"update lottery set status = {status}, update_time='{DateTime.Now}', update_timestamp={DateTime.Now.ToTimestamp()} where id in ({id})";
return(MysqlHelper.Execute(sql));
}
/// <summary>
/// 获取所有彩种
/// </summary>
/// <returns></returns>
public static List <Lottery> GetList()
{
string sql = "select * from lottery";
return(MysqlHelper.GetList <Lottery>(sql).ToList());
}
private static bool Exist(string lotteryCode)
{
string sql = $"select * from lottery where code = '{lotteryCode}'";
return(MysqlHelper.GetOne <OpenResult>(sql) != null);
}
/// <summary>
/// 插入
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static int Insert(List <Lottery> list)
{
string sql = "insert into lottery (create_time, create_timestamp, name, code) values (@create_time, @create_timestamp, @name, @code)";
return(MysqlHelper.Execute(sql, list.Where(l => !Exist(l.code))));
}
/// <summary>
/// 修改状态
/// </summary>
/// <param name="id"></param>
/// <param name="status"></param>
/// <returns></returns>
public static int UpdateStatus(int id, int status)
{
string sql = $"update data_source set status = {status}, update_time='{DateTime.Now}', update_timestamp={DateTime.Now.ToTimestamp()} where id = {id}";
return(MysqlHelper.Execute(sql));
}
/// <summary>
/// 获取所有彩种
/// </summary>
/// <returns></returns>
public static List <DataSource> GetList()
{
string sql = "select * from data_source";
return(MysqlHelper.GetList <DataSource>(sql).ToList());
}
/// <summary>
/// 插入
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static int Insert(List <DataSource> list)
{
string sql = "insert into data_source (create_time, create_timestamp, name, code) values (@create_time, @create_timestamp, @name, @code)";
return(MysqlHelper.Execute(sql, list));
}
/// <summary>
/// 根据id获取日志信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static AppLog GetLogById(string id)
{
string sql = $"select log_message from app_log where id = {id}";
return(MysqlHelper.GetOne <AppLog>(sql));
}
