/// <summary>
/// 读取所有可用的开发文档集合
/// </summary>
/// <returns></returns>
public List <DocMenuQueryModel> FindAllEnabledDocList()
{
var sql = @"select jhc.ID as ArticleId,jhc.Title,jhc.ClassId,jhc2.* from dbo.jmp_Help_Content as jhc left join dbo.jmp_Help_Classification as jhc2 on jhc.ClassId=jhc2.ID where jhc2.Type=1 and jhc2.State=0 and jhc.State=0
union select 0 as ArticleId,jhc.ClassName as Title,0 as ClassId,* from dbo.jmp_Help_Classification as jhc where jhc.State=0 and jhc.Type=1";
var ds = DbHelperSQL.Query(sql);
var list = DbHelperSQL.ConvertToList <DocMenuQueryModel>(ds.Tables[0]).ToList();
return(list);
}
protected override void LoadData(IDataLoader dataLoader)
{
var ds = (DataSet)dataLoader.Load(_procName);
_data = DbHelperSQL.ConvertToList <AppPaySuccessRatioSinceLatest>(ds.Tables[0]).ToList();
DoingWork(string.Format("数据加载完成,共{0}条数据,准备处理...", _data.Count));
if (_data == null || _data.Count <= 0)
{
AllowContinue = false;
}
}
/// <summary>
/// 查询运营平台的角色-权限映射关系集合
/// </summary>
/// <returns></returns>
public List <RolePermissionMappingQueryModel> FindAdminRolePermissionMappingList()
{
var sql = @";with T as ( SELECT A.r_id,
Split.a.value('.', 'INT') AS PermissionId
FROM (SELECT jr.r_id,
CAST ('<M>' + REPLACE(jr.r_value, ',', '</M><M>') + '</M>' AS XML) AS PermissionId
FROM dbo.jmp_role as jr where jr.r_state=1 and jr.r_type=0) AS A CROSS APPLY PermissionId.nodes ('/M') AS Split(a)
)
select t.r_id as RoleId,t.PermissionId,jl.l_url as PermissionUrl from T
left join dbo.jmp_limit as jl on jl.l_id=T.PermissionId";
var ds = DbHelperSQL.Query(sql);
var list = DbHelperSQL.ConvertToList <RolePermissionMappingQueryModel>(ds.Tables[0]).ToList();
return(list);
}
/// <summary>
/// 查询当日有订单的所有应用的支付衰减详情(与前三天数据比较)
/// </summary>
/// <param name="today"></param>
/// <returns></returns>
public IEnumerable <AppPaySuccessAttenuation> GetTodayAppPaySuccessAttenuation(DateTime today)
{
var sql = string.Format(@"WITH T1 AS(
SELECT a_appid,a_appname,SUM(a_count) AS TodayTotalRequest,SUM(a_success) AS TodayPaySuccess,CAST(SUM(a_success)/SUM(a_count)*100 AS DECIMAL(8,2)) AS TodaySuccessRatio FROM jmp_appcount WHERE a_datetime >='{0} 00:00:00'
GROUP BY a_appid,a_appname ),
T2 AS(
SELECT a_appid,SUM(a_count) AS FirstThreeDaysTotalRequest,SUM(a_success) AS FirstThreeDaysPaySuccess,CAST(SUM(a_success)/SUM(a_count)*100 AS DECIMAL(8,2)) AS FirstThreeDaysSuccessRatio FROM jmp_appreport WHERE a_datetime BETWEEN '{1} 00:00:00' AND '{2} 23:59:59'
GROUP BY a_appid )
SELECT T1.a_appid AS AppId,T1.a_appname AS AppName,T1.TodayTotalRequest,T1.TodayPaySuccess,T1.TodaySuccessRatio,T2.FirstThreeDaysTotalRequest,T2.FirstThreeDaysPaySuccess,T2.FirstThreeDaysSuccessRatio,(T1.TodaySuccessRatio-T2.FirstThreeDaysSuccessRatio) AS SuccessAttenuation FROM T1
LEFT JOIN T2 ON T2.a_appid = T1.a_appid ", today.ToString("yyyy-MM-dd"), today.AddDays(-3).ToString("yyyy-MM-dd"), today.ToString("yyyy-MM-dd"));
var dt = DbHelperSQLTotal.Query(sql).Tables[0];
var list = DbHelperSQL.ConvertToList <AppPaySuccessAttenuation>(dt);
return(list);
}
public List <MDL.jmp_Help_Content> Search(string[] keywords)
{
var sb = new List <string>();
foreach (var kw in keywords)
{
var q = kw.Replace("'", "");
sb.Add(string.Format("SELECT * FROM jmp_Help_Content WHERE Title LIKE '%{0}%'", q));
sb.Add(string.Format("SELECT * FROM jmp_Help_Content WHERE Content LIKE '%{0}%'", q));
}
var ds = DbHelperSQL.Query(string.Join(" UNION ", sb));
var list = DbHelperSQL.ConvertToList <MDL.jmp_Help_Content>(ds.Tables[0]).ToList();
return(list);
}
/// <summary>
/// 返回主分类下按子分类分组的前5篇文章
/// </summary>
/// <param name="mainCatalogId">主分类ID</param>
/// <returns></returns>
public List <ArticleWithTotalCountQueryModel> FindTopArticleListGroupBySubCatalog(int mainCatalogId)
{
var sql = @";with t as (
select *,row_number() over (partition by jhc.ClassId order by ID desc) as Rn from dbo.jmp_Help_Content as jhc where jhc.PrentID={0}
),t1 as (
select t.ClassId,count(1) as SubCatalogArticleCount from t group by t.ClassId
),t2 as(
select t.*,jhc.ClassName from t left join dbo.jmp_Help_Classification as jhc on t.ClassId=jhc.ID
)
select t2.ID,t2.PrentID as MainCatalogId,t2.ClassId as SubCatalogId,t2.ClassName as SubCatalogName,t2.Title,t1.SubCatalogArticleCount from t2,t1 where t2.ClassId=t1.ClassId and t2.Rn<=5";
var ds = DbHelperSQL.Query(string.Format(sql, mainCatalogId));
var list = DbHelperSQL.ConvertToList <ArticleWithTotalCountQueryModel>(ds.Tables[0]).ToList();
return(list);
}
/// <summary>
/// 加载指定通知次数的订单到队列
/// </summary>
/// <param name="notifyTimes">要加载的订单通知次数</param>
/// <param name="topCount">每次从数据加载的记录行数</param>
public Queue <OrderNotifyQueueListModel> LoadSpecifiedTimesData(int notifyTimes, int topCount)
{
try
{
var dt =
DbHelperSQL.Query(string.Format("EXEC updatequeue @times={0},@top={1}", notifyTimes, topCount))
.Tables[0];
var list = DbHelperSQL.ConvertToList <OrderNotifyQueueListModel>(dt);
return(new Queue <OrderNotifyQueueListModel>(list));
}
catch (Exception ex)
{
throw ex;
//return new Queue<OrderNotifyQueueListModel>(new List<OrderNotifyQueueListModel>());
}
}
private void LoadData()
{
_appMonitorList = new JMP.BLL.appmonitor().GetEnabledList();
if (_appMonitorList.Count <= 0)
{
return;
}
var bll = new JMP.BLL.appmonitor();
var dt = bll.GetNoOrderApp().Tables[0];
//最近一次下单时间到当前时间的所有应用的集合
var noOrderApps = DbHelperSQL.ConvertToList <NoOrdersAppSinceLatest>(dt).ToList();
foreach (var app in noOrderApps)
{
var appMonitor = _appMonitorList.Find(x => x.a_appid == app.AppId);
if (appMonitor == null)
{
continue;
}
var timeRanges = appMonitor.a_time_range.ParseAppMonitorTimeRange();
var t = timeRanges.Find(x => x.WhichHour == app.WhichHour);
if (t == null)
{
t = timeRanges.Find(x => x.WhichHour == 100);
//if (t == null)
//{
// t = new AppMonitorTimeRange();
//}
}
if (t == null)
{
continue;
}
if (app.LatestOrderTimespan > 60 * t.Minutes)
{
_noOrderApps.Add(app);
}
}
}
