/// <summary>
/// 统计属性排行
/// </summary>
/// <param name="type">类型（0：今天，-1：昨天，-7：最近7天，-30：最近30天）</param>
/// <param name="field">字段列</param>
/// <param name="dicWhere">条件</param>
/// <returns></returns>
public static LoggingResultVM StatsTop(int type, string field, Dictionary <string, string> dicWhere = null)
{
var vm = new LoggingResultVM();
var now = DateTime.Now;
var end = now;
DateTime begin;
switch (type)
{
case 0:
case -1:
{
begin = now.AddDays(type).Date;
end = DateTime.Parse(now.AddDays(type).ToString("yyyy-MM-dd 23:59:59.999"));
}
break;
default:
{
type++;
begin = now.AddDays(type).Date;
}
break;
}
var sql = GetSqlForQuery(begin, end, out SQLiteHelper db, out int lost, out List <string> listPreSql);
if (sql != null)
{
var listName = new LoggingModel().GetType().GetProperties().ToList().Select(x => x.Name).ToList();
if (listName.Contains(field))
{
var whereSql = DicWhereJoin(dicWhere);
if (!string.IsNullOrWhiteSpace(whereSql))
{
whereSql += " and ";
}
sql = $"select {field} as field,count({field}) as total from ({sql}) where {whereSql} LogCreateTime>={begin.Date.Ticks} and LogCreateTime<={end.Ticks} group by {field} order by total desc";
var dt = db.Query(sql, listPreSql).Tables[0];
while (dt.Rows.Count > 50)
{
dt.Rows.RemoveAt(dt.Rows.Count - 1);
}
vm.Lost = lost;
vm.Data = dt;
}
}
return(vm);
}
/// <summary>
/// 新增SQL
/// </summary>
/// <param name="path"></param>
/// <param name="list"></param>
/// <returns></returns>
public static bool InsertAll(string path, IEnumerable <LoggingModel> list)
{
var listSql = new List <string>();
var pis = new LoggingModel().GetType().GetProperties().ToList();
var fields = string.Join(",", pis.Select(x => x.Name));
foreach (var item in list)
{
var values = string.Join("','", new string[] {
item.LogId,
item.LogApp?.Replace("'", "''"),
item.LogUid?.Replace("'", "''"),
item.LogNickname?.Replace("'", "''"),
item.LogAction?.Replace("'", "''"),
item.LogContent?.Replace("'", "''"),
item.LogUrl?.Replace("'", "''"),
item.LogReferer?.Replace("'", "''"),
item.LogIp?.Replace("'", "''"),
item.LogArea?.Replace("'", "''"),
item.LogUserAgent?.Replace("'", "''"),
item.LogBrowserName?.Replace("'", "''"),
item.LogSystemName?.Replace("'", "''"),
item.LogGroup?.Replace("'", "''"),
item.LogLevel?.Replace("'", "''"),
item.LogCreateTime.Ticks.ToString(),
item.LogRemark?.Replace("'", "''"),
item.LogSpare1?.Replace("'", "''"),
item.LogSpare2?.Replace("'", "''"),
item.LogSpare3?.Replace("'", "''")
});
var sql = $"insert into {DbTableName} ({fields}) values ('{values}')";
listSql.Add(sql);
}
return(new SQLiteHelper(PathToConn(path)).ExecuteNonQuery(listSql));
}
/// <summary>
/// 条件拼接
/// </summary>
/// <param name="dicWhere">条件（列名：值）</param>
private static string DicWhereJoin(Dictionary <string, string> dicWhere)
{
if (dicWhere != null)
{
var listWhere = new List <string>();
var listField = new LoggingModel().GetType().GetProperties().ToList().Select(x => x.Name).ToList();
foreach (var field in dicWhere.Keys)
{
if (listField.Contains(field))
{
listWhere.Add($"{field}='{dicWhere[field].Replace("'", "''")}'");
}
}
if (listWhere.Count > 0)
{
return(string.Join(" and ", listWhere));
}
}
return("");
}
/// <summary>
/// 新增
/// </summary>
/// <param name="log">日志实体</param>
public static void Add(LoggingModel log)
{
Add(new List <LoggingModel> {
log
});
}