/// <summary>
/// 获取列表
/// </summary>
/// <param name="start">开始日期</param>
/// <param name="end">结束日期</param>
/// <param name="sType">汇总方式（应用名称、开发者邮箱）</param>
/// <param name="sKeys">汇总值</param>
/// <returns></returns>
public DataTable GetLists(string start, string end, string sType, string sKeys)
{
string DbBase = System.Configuration.ConfigurationManager.AppSettings["BaseDb"];
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("select isnull(sum(r_users),0) r_users,isnull(sum(r_moneys),0) r_moneys,isnull(sum(r_orders),0) r_orders,r_date from jmp_revenue_add temp ");
strSql.AppendLine("left join [" + DbBase + "].[dbo].[jmp_app] t_app on t_app.a_id=temp.r_appid");
strSql.AppendLine("left join [" + DbBase + "].[dbo].[jmp_user] t_user on t_user.u_id=t_app.a_user_id");
strSql.Append("where 1=1");
strSql.AppendLine(" and convert(nvarchar(10),r_date,120)>='" + start + "'");
strSql.AppendLine(" and convert(nvarchar(10),r_date,120)<='" + end + "'");
if (!string.IsNullOrEmpty(sType) && !string.IsNullOrEmpty(sKeys))
{
switch (sType)
{
case "0":
strSql.AppendFormat(" and t_app.a_name='{0}'", sKeys);
break;
case "1":
strSql.AppendFormat(" and t_user.u_email='{0}'", sKeys);
break;
}
}
strSql.AppendLine(" group by r_date ");
return(DbHelperSQLTotal.Query(strSql.ToString()).Tables[0]);
}
/// <summary>
/// 根据条件查询商务下开发者结算数据(详情)
/// </summary>
/// <param name="where">查询条件</param>
/// <returns></returns>
public DataSet FindPagedListBpUid(int developerId, int searchType, string where)
{
var sql = new StringBuilder();
if (searchType == 1)
{
sql.AppendFormat(@"select * from {0}.dbo.CoSettlementDeveloperOverview total
inner join {1}.dbo.jmp_user users on total.DeveloperId=users.u_id
where users.relation_type=1
and users.relation_person_id={2} {3} ", ReportDbName, BaseDbName, developerId, where);
}
else
{
sql.AppendFormat(@"select SettlementDay,DisplayName,BpPushMoneyRatio,ISNULL(SUM(TotalAmount),0) as TotalAmount,ISNULL(SUM(ServiceFee),0) as ServiceFee,
ISNULL(SUM(BpPushMoney),0) as BpPushMoney
from {0}.dbo.CoSettlementDeveloperOverview total
inner join(
select a.u_id,DisplayName
from {1}.dbo.jmp_user a
left join {1}.dbo.CoAgent c on c.Id=a.relation_person_id
where a.relation_type=2 and c.OwnerId={2}
) as user_bp on total.DeveloperId=user_bp.u_id {3}
group by SettlementDay,DisplayName,BpPushMoneyRatio
", ReportDbName, BaseDbName, developerId, where);
}
return(DbHelperSQLTotal.Query(sql.ToString()));
}
/// <summary>
/// 代理商首页曲线图表
/// </summary>
/// <param name="uid">代理商ID</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="startTimeAdy">三日开始时间</param>
/// <param name="endTimeAdy">三日结束时间</param>
/// <returns></returns>
public DataSet FindPagedListSqlAgent(int uid, string startTime, string endTime, string startTimeAdy, string endTimeAdy)
{
var sql = new StringBuilder();
sql.AppendFormat(@" select [hour].[Hours],isnull(sum(a_success),0) as a_success,isnull(sum(a_curr),0) as a_curr,isnull(sum(b_success),0) as b_success from
(select convert(int, DATENAME(HOUR, dateadd(hour, number, CONVERT(varchar(12), getdate(), 111)))) as [Hours] from master.dbo.spt_values where type = 'P'
and number < DATENAME(HOUR, '{2}') + 1) as [hour] left join
(
select total.a_success,total.a_curr, total.b_success, total.[HOUR] from (
select SUM( a.a_success) as a_success,SUM(a.a_curr) as a_curr,0 as b_success,DATENAME(HOUR,a_datetime) as [HOUR]
from {5}.dbo.jmp_appcount a
inner join
(
select a.u_id from {6}.dbo.jmp_user a
left join {6}.dbo.CoAgent b on b.Id=a.relation_person_id
where a.relation_type=2 and b.id={0}
)as b on a.a_uerid=b.u_id and a.a_datetime >= '{1}' and a.a_datetime <= '{2}' group by a.a_datetime
) as total
union all
select average.a_success, average.a_curr, average.b_success, average.[HOUR] from (
select 0 as a_success,0 as a_curr,convert(decimal(18,0),(SUM(a.a_success)/3)) as b_success ,DATENAME(HOUR,a_datetime) as [HOUR]
from {5}.dbo.jmp_appcount a
inner join
(
select a.u_id from {6}.dbo.jmp_user a
left join {6}.dbo.CoAgent b on b.Id=a.relation_person_id
where a.relation_type=2 and b.id={0}
)as b on a.a_uerid=b.u_id and a.a_datetime >= '{3}' and a.a_datetime <= '{4}' group by a.a_datetime
) as average
) as total on [hour].[Hours]= total.[HOUR] group by [hour].[Hours] order by [hour].[Hours] asc
", uid, startTime, endTime, startTimeAdy, endTimeAdy, PubDbName.dbtotal, PubDbName.dbbase);
// return sql.ToString().QueryList<jmp_appcount>();
return(DbHelperSQLTotal.Query(sql.ToString()));
}
/// <summary>
/// 获得数据列表用于图标统计
/// </summary>
public List <JMP.MDL.jmp_statistics> GetListTjCount(string stime, string etime, int searchType, string searchname)
{
string strSql = string.Format(" select top 10 a.s_brand,sum(a.s_count)as s_count from jmp_statistics a left join " + JMP.DbName.PubDbName.dbbase + ".dbo.jmp_app b on b.a_id=a.s_app_id left join " + JMP.DbName.PubDbName.dbbase + ".dbo.jmp_user c on c.u_id=b.a_user_id where 1=1 ");
if (!string.IsNullOrEmpty(stime))
{
strSql += " and convert(varchar(10),a.s_time,120)>='" + stime + "' ";
}
if (!string.IsNullOrEmpty(etime))
{
strSql += " and convert(varchar(10),a.s_time,120)<='" + etime + "' ";
}
if (searchType > 0 && !string.IsNullOrEmpty(searchname))
{
switch (searchType)
{
case 1:
strSql += " and b.a_name ='" + searchname + "' ";
break;
case 2:
strSql += " and c.u_email='" + searchname + "' ";
break;
}
}
strSql += " group by a.s_brand order by s_count desc ";
DataTable dt = DbHelperSQLTotal.Query(strSql.ToString()).Tables[0];
return(DbHelperSQLTotal.ToList <JMP.MDL.jmp_statistics>(dt));
}
/// <summary>
/// 根据sql语句查询信息
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable CountSect(string sql)
{
DataTable dt = new DataTable();
dt = DbHelperSQLTotal.Query(sql).Tables[0];
return(dt);
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="start">开始日期</param>
/// <param name="end">结束日期</param>
/// <param name="sType">查询字段</param>
/// <param name="sKey">查询字段值</param>
/// <param name="tops">前几条</param>
/// <returns></returns>
public DataTable GetLists(string start, string end, string sType, string sKey, int tops)
{
string BsaeDb = System.Configuration.ConfigurationManager.AppSettings["BaseDb"];
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("select " + (tops > 0 ? "top " + tops : "") + " b_user.u_realname as tname,sum(r_moneys) r_moneys from (");
strSql.AppendLine(" select r_appid,sum(r_moneys) r_moneys from jmp_sales_user");
strSql.AppendLine(" where convert(nvarchar(10),r_date,120)>='" + start + "'");
strSql.AppendLine(" and convert(nvarchar(10),r_date,120)<='" + end + "'");
strSql.AppendLine(" group by r_appid");
strSql.AppendLine(") temp");
strSql.AppendLine("left join [" + BsaeDb + "].[dbo].[jmp_app] b_app on temp.r_appid=b_app.a_id");
strSql.AppendLine("left join [" + BsaeDb + "].[dbo].[jmp_user] b_user on b_app.a_user_id=b_user.u_id");
if (!string.IsNullOrEmpty(sType) && !string.IsNullOrEmpty(sKey))
{
strSql.Append("where ");
switch (sType)
{
case "0":
strSql.AppendFormat("b_app.a_name='{0}'", sKey);
break;
case "1":
strSql.AppendFormat("b_user.u_email='{0}'", sKey);
break;
}
}
strSql.AppendLine("group by b_user.u_realname");
strSql.AppendLine("order by r_moneys desc");
return(DbHelperSQLTotal.Query(strSql.ToString()).Tables[0]);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetListSet(string stime, string etime, int searchType, string searchname)
{
string strSql = string.Format(" select SUM(a.t_newcount)as t_newcount,SUM(a.t_activecount) as t_activecount, CONVERT(varchar(10),a.t_time,120) as t_time from jmp_trends a left join " + JMP.DbName.PubDbName.dbbase + ".dbo.jmp_app b on b.a_id=a.t_app_id left join " + JMP.DbName.PubDbName.dbbase + ".dbo.jmp_user c on c.u_id=b.a_user_id where 1=1 ");
if (!string.IsNullOrEmpty(stime))
{
strSql += " and convert(varchar(10),a.t_time,120)>='" + stime + "' ";
}
if (!string.IsNullOrEmpty(etime))
{
strSql += " and convert(varchar(10),a.t_time,120)<='" + etime + "' ";
}
if (searchType > 0 && !string.IsNullOrEmpty(searchname))
{
switch (searchType)
{
case 1:
strSql += " and b.a_name ='" + searchname + "' ";
break;
case 2:
strSql += " and c.u_email='" + searchname + "' ";
break;
}
}
strSql += " group by a.t_time order by a.t_time ";
return(DbHelperSQLTotal.Query(strSql));
}
/// <summary>
/// 获取列表(营收概况)
/// </summary>
/// <param name="start">开始日期</param>
/// <param name="end">结束日期</param>
/// <param name="sType">汇总方式（应用名称、开发者邮箱）</param>
/// <param name="sKeys">汇总值</param>
/// <returns></returns>
public DataTable GetListys(string start, string end, string sType, string sKeys)
{
string DbBase = System.Configuration.ConfigurationManager.AppSettings["BaseDb"];
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("select isnull( sum(temp.a_number),0) a_number, ISNULL( SUM(temp.a_curr),0 )a_curr ,ISNULL(SUM(temp.a_success),0)a_success,ISNULL(SUM(temp.a_notpay),0)a_notpay,temp.a_time from jmp_appreport temp ");
strSql.AppendLine("left join [" + DbBase + "].[dbo].[jmp_app] t_app on t_app.a_id=temp.a_appid");
strSql.AppendLine("left join [" + DbBase + "].[dbo].[jmp_user] t_user on t_user.u_id=t_app.a_user_id");
strSql.Append("where 1=1");
strSql.AppendLine(" and convert(nvarchar(10), temp.a_time,120)>='" + start + "'");
strSql.AppendLine(" and convert(nvarchar(10), temp.a_time,120)<='" + end + "'");
if (!string.IsNullOrEmpty(sType) && !string.IsNullOrEmpty(sKeys))
{
switch (sType)
{
case "0":
strSql.AppendFormat(" and t_app.a_name='{0}'", sKeys);
break;
case "1":
strSql.AppendFormat(" and t_user.u_email='{0}'", sKeys);
break;
}
}
strSql.AppendLine(" group by temp.a_time ");
return(DbHelperSQLTotal.Query(strSql.ToString()).Tables[0]);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public JMP.MDL.jmp_defect GetModel(int d_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select d_id, d_type, d_aapid, d_losscount, d_lossproportion, d_usercount, d_datatype, d_time ");
strSql.Append(" from jmp_defect ");
strSql.Append(" where d_id=@d_id");
SqlParameter[] parameters =
{
new SqlParameter("@d_id", SqlDbType.Int, 4)
};
parameters[0].Value = d_id;
JMP.MDL.jmp_defect model = new JMP.MDL.jmp_defect();
DataSet ds = DbHelperSQLTotal.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["d_id"].ToString() != "")
{
model.d_id = int.Parse(ds.Tables[0].Rows[0]["d_id"].ToString());
}
if (ds.Tables[0].Rows[0]["d_type"].ToString() != "")
{
model.d_type = int.Parse(ds.Tables[0].Rows[0]["d_type"].ToString());
}
if (ds.Tables[0].Rows[0]["d_aapid"].ToString() != "")
{
model.d_aapid = int.Parse(ds.Tables[0].Rows[0]["d_aapid"].ToString());
}
if (ds.Tables[0].Rows[0]["d_losscount"].ToString() != "")
{
model.d_losscount = int.Parse(ds.Tables[0].Rows[0]["d_losscount"].ToString());
}
if (ds.Tables[0].Rows[0]["d_lossproportion"].ToString() != "")
{
model.d_lossproportion = decimal.Parse(ds.Tables[0].Rows[0]["d_lossproportion"].ToString());
}
if (ds.Tables[0].Rows[0]["d_usercount"].ToString() != "")
{
model.d_usercount = int.Parse(ds.Tables[0].Rows[0]["d_usercount"].ToString());
}
if (ds.Tables[0].Rows[0]["d_datatype"].ToString() != "")
{
model.d_datatype = int.Parse(ds.Tables[0].Rows[0]["d_datatype"].ToString());
}
if (ds.Tables[0].Rows[0]["d_time"].ToString() != "")
{
model.d_time = DateTime.Parse(ds.Tables[0].Rows[0]["d_time"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public JMP.MDL.jmp_paychannel GetModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id, payname, payid, money, datetimes,paytype ");
strSql.Append(" from jmp_paychannel ");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = id;
JMP.MDL.jmp_paychannel model = new JMP.MDL.jmp_paychannel();
DataSet ds = DbHelperSQLTotal.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["id"].ToString() != "")
{
model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
}
model.payname = ds.Tables[0].Rows[0]["payname"].ToString();
model.paytype = ds.Tables[0].Rows[0]["paytype"].ToString();
if (ds.Tables[0].Rows[0]["payid"].ToString() != "")
{
model.payid = int.Parse(ds.Tables[0].Rows[0]["payid"].ToString());
}
if (ds.Tables[0].Rows[0]["money"].ToString() != "")
{
model.money = decimal.Parse(ds.Tables[0].Rows[0]["money"].ToString());
}
if (ds.Tables[0].Rows[0]["datetimes"].ToString() != "")
{
model.datetimes = DateTime.Parse(ds.Tables[0].Rows[0]["datetimes"].ToString());
}
if (ds.Tables[0].Rows[0]["success"].ToString() != "")
{
model.success = int.Parse(ds.Tables[0].Rows[0]["success"].ToString());
}
if (ds.Tables[0].Rows[0]["successratio"].ToString() != "")
{
model.successratio = decimal.Parse(ds.Tables[0].Rows[0]["successratio"].ToString());
}
if (ds.Tables[0].Rows[0]["notpay"].ToString() != "")
{
model.notpay = int.Parse(ds.Tables[0].Rows[0]["notpay"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 根据商务id查询前三天平均交易量
/// </summary>
/// <param name="kst_time">开始日期（2016-03-18）</param>
/// <param name="jst_time">结束日期（2016-03-18）</param>
/// <param name="swid">商务id</param>
/// <returns></returns>
public DataSet GetListAverageSw(string kst_time, string jst_time, int swid)
{
string sql = string.Format("select (SUM(a_success)/3) as a_success ,CONVERT(nvarchar(2),a_datetime,108) as a_datetime from jmp_appcount a left join " + JMP.DbName.PubDbName.dbbase + ".dbo.jmp_user b on a.a_uerid=b.u_id ");
string kstime = kst_time + " 00:00:00";
string endtime = jst_time + " 23:59:59";
sql += " where b.u_merchant_id=" + swid + " and a_datetime>='" + kstime + "' and a_datetime<='" + endtime + "' ";
sql += " group by CONVERT(nvarchar(2),a_datetime,108) order by CONVERT(nvarchar(2),a_datetime,108) desc ";
return(DbHelperSQLTotal.Query(sql.ToString()));
}
/// <summary>
/// 查询明细根据应用分组
/// </summary>
/// <returns></returns>
public DataSet GetModelAppName(int id, string date)
{
StringBuilder strsql = new StringBuilder();
strsql.Append("select AppId,SettlementDay,AppName,a_platform_id,(isnull(SUM(TotalAmount), 0) - isnull(SUM(ChannelRefundAmount), 0)) as TotalAmount,isnull(SUM(PortFee), 0) as PortFee,ISNULL(SUM(ChannelRefundAmount),0) as ChannelRefundAmount,(isnull(SUM(TotalAmount), 0) - isnull(SUM(ChannelRefundAmount), 0) - isnull(SUM(PortFee), 0) - isnull(SUM(ServiceFee), 0)) as KFZIncome");
strsql.Append(" from CoSettlementDeveloperAppDetails a inner join dx_base.dbo.jmp_app b on a.AppId = b.a_id");
strsql.Append(" where DeveloperId='" + id + "' and SettlementDay='" + date + "' ");
strsql.Append(" group by AppId,SettlementDay,AppName,a_platform_id");
return(DbHelperSQLTotal.Query(strsql.ToString()));
}
/// <summary>
/// 查询明细根据支付方式分组
/// </summary>
/// <param name="id">APPID</param>
/// <param name="date">日期</param>
/// <returns></returns>
public DataSet GetModelPayType(int id, string date)
{
StringBuilder strsql = new StringBuilder();
strsql.Append("select PayModeName,PortFeeRatio,ServiceFeeRatio,IsSpecialApproval,SpecialApprovalFeeRatio,(isnull(SUM(TotalAmount), 0) - isnull(SUM(ChannelRefundAmount), 0)) as TotalAmount, isnull(SUM(PortFee),0) as PortFee,ISNULL(SUM(ChannelRefundAmount),0) as ChannelRefundAmount, isnull(SUM(OrderCount),0) as OrderCount,isnull(SUM(ServiceFee), 0) as ServiceFee, (isnull(SUM(TotalAmount), 0) - isnull(SUM(ChannelRefundAmount), 0) - isnull(SUM(PortFee), 0) - isnull(SUM(ServiceFee), 0)) as KFZIncome ");
strsql.Append(" from [dbo].[CoSettlementDeveloperAppDetails] where AppId='" + id + "'");
strsql.Append(" and SettlementDay='" + date + "'");
strsql.Append(" group by PayModeName,PortFeeRatio,ServiceFeeRatio,IsSpecialApproval,SpecialApprovalFeeRatio");
return(DbHelperSQLTotal.Query(strsql.ToString()));
}
/// <summary>
/// 根据条件查询账单管理结算详情（账单管理）
/// </summary>
/// <param name="where">条件</param>
/// <returns></returns>
public DataSet FindPagedModel(string @where)
{
var sql = new StringBuilder();
sql.AppendFormat(@"select PayModeName,PortFeeRatio,ServiceFeeRatio,isnull(SUM(TotalAmount),0) as TotalAmount, isnull(SUM(PortFee),0) as PortFee, isnull(SUM(OrderCount),0) as OrderCount,
isnull(SUM(ServiceFee), 0) as ServiceFee, from {0}.[dbo].[CoSettlementDeveloperAppDetails] {1} group by PayModeName,PortFeeRatio,ServiceFeeRatio",
ReportDbName, @where.PrependWhere());
return(DbHelperSQLTotal.Query(sql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select t_app_id,t_newcount,t_activecount,t_time ");
strSql.Append(" FROM jmp_trends ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(DbHelperSQLTotal.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select r_id,r_times,r_starttime,r_username,r_uid,r_app_key,r_app_name,r_equipment,r_succeed,r_notpay,r_alipay ");
strSql.Append(" FROM jmp_app_report ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(DbHelperSQLTotal.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * ");
strSql.Append(" FROM jmp_defect ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(DbHelperSQLTotal.Query(strSql.ToString()));
}
/// <summary>
/// 根据条件查询代理商下开发者结算数据(详情)
/// </summary>
/// <param name="where">查询条件</param>
/// <returns></returns>
public DataSet FindPagedListAgentUid(int developerId, string where)
{
var sql = new StringBuilder();
sql.AppendFormat(@"select * from {0}.dbo.CoSettlementDeveloperOverview total
inner join(
select a.u_id from
{1}.dbo.jmp_user a
left join {1}.dbo.CoAgent c on c.Id=a.relation_person_id
where a.relation_type=2 and c.Id={2}
) as user_bp on total.DeveloperId=user_bp.u_id {3}", ReportDbName, BaseDbName, developerId, where);
return(DbHelperSQLTotal.Query(sql.ToString()));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public JMP.MDL.jmp_revenue_active GetModel(int r_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select r_id,r_users,r_moneys,r_orders,r_date,r_appid");
strSql.Append(" from jmp_revenue_active ");
strSql.Append(" where r_id=@r_id");
SqlParameter[] parameters =
{
new SqlParameter("@r_id", SqlDbType.Int, 4)
};
parameters[0].Value = r_id;
JMP.MDL.jmp_revenue_active model = new JMP.MDL.jmp_revenue_active();
DataSet ds = DbHelperSQLTotal.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["r_id"].ToString() != "")
{
model.r_id = int.Parse(ds.Tables[0].Rows[0]["r_id"].ToString());
}
if (ds.Tables[0].Rows[0]["r_users"].ToString() != "")
{
model.r_users = int.Parse(ds.Tables[0].Rows[0]["r_users"].ToString());
}
if (ds.Tables[0].Rows[0]["r_moneys"].ToString() != "")
{
model.r_moneys = decimal.Parse(ds.Tables[0].Rows[0]["r_moneys"].ToString());
}
if (ds.Tables[0].Rows[0]["r_orders"].ToString() != "")
{
model.r_orders = int.Parse(ds.Tables[0].Rows[0]["r_orders"].ToString());
}
if (ds.Tables[0].Rows[0]["r_appid"].ToString() != "")
{
model.r_appid = int.Parse(ds.Tables[0].Rows[0]["r_appid"].ToString());
}
if (ds.Tables[0].Rows[0]["r_date"].ToString() != "")
{
model.r_date = DateTime.Parse(ds.Tables[0].Rows[0]["r_date"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 查询订单每天走势图（设备量、成功量、请求量）
/// </summary>
/// <param name="t_time">查询日期</param>
/// <param name="u_merchant_id">商务ID</param>
/// <returns></returns>
public DataSet GetListReportOrderSuccessByID(string t_time, int u_merchant_id)
{
string sql = string.Format("select a.a_datetime ,SUM(a. a_equipment)as a_equipment,SUM( a.a_success) as a_success,SUM( a.a_count) as a_count from dbo.jmp_appcount as a left join " + JMP.DbName.PubDbName.dbbase + ".dbo.jmp_user b on a.a_uerid=b.u_id where a.a_datetime>=@kstime and a.a_datetime<=@endtime and b.u_merchant_id=@u_merchant_id group by a.a_datetime ");
string kstime = t_time + " 00:00:00";
string endtime = t_time + " 23:59:59";
SqlParameter[] par =
{
new SqlParameter("@kstime", kstime),
new SqlParameter("@endtime", endtime),
new SqlParameter("@u_merchant_id", u_merchant_id)
};
return(DbHelperSQLTotal.Query(sql.ToString(), par));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * ");
strSql.Append(" FROM jmp_user_report ");
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(" where " + strWhere);
}
return(DbHelperSQLTotal.Query(strSql.ToString()));
}
/// <summary>
/// 根据用户查询交易数据
/// </summary>
/// <param name="t_time">查询时间</param>
/// <param name="u_id">用户id</param>
/// <returns></returns>
public DataTable GetListDay(string t_time, int u_id)
{
string sql = string.Format("select a_datetime,SUM(a_curr)as a_curr,SUM(a_success) as a_success,SUM(a_count) as a_count from dbo.jmp_appcount where a_datetime>=@kstime and a_datetime<=@endtime and a_uerid=@u_id group by a_datetime ");
string kstime = t_time + " 00:00:00";
string endtime = t_time + " 23:59:59";
SqlParameter[] par =
{
new SqlParameter("@kstime", kstime),
new SqlParameter("@endtime", endtime),
new SqlParameter("@u_id", u_id)
};
return(DbHelperSQLTotal.Query(sql.ToString(), par).Tables[0]);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select r_id,r_users,r_moneys,r_orders,r_date,r_appid ");
strSql.Append(" FROM jmp_revenue_add ");
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(" where " + strWhere);
}
return(DbHelperSQLTotal.Query(strSql.ToString()));
}
/// <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);
}
/// <summary>
/// 获得数据列表用于图标统计
/// </summary>
public JMP.MDL.jmp_operatingsystem ModelTjCount(string stime, string etime)
{
string strSql = string.Format(" select sum(o_count)as o_count from jmp_operatingsystem where 1=1 ");
if (!string.IsNullOrEmpty(stime))
{
strSql += " and convert(varchar(10),o_time,120)>='" + stime + "' ";
}
if (!string.IsNullOrEmpty(etime))
{
strSql += " and convert(varchar(10),o_time,120)<='" + etime + "' ";
}
DataTable dt = DbHelperSQLTotal.Query(strSql.ToString()).Tables[0];
return(DbHelperSQLTotal.ToModel <JMP.MDL.jmp_operatingsystem>(dt));
}
/// <summary>
/// 查询总数用户报表统计
/// </summary>
/// <param name="stime"></param>
/// <param name="etime"></param>
/// <param name="searchType"></param>
/// <param name="searchname"></param>
/// <returns></returns>
public JMP.MDL.jmp_statistics modelCoutn(string stime, string etime)
{
string strSql = string.Format(" select sum(s_count) as s_count from jmp_statistics where 1=1 ");
if (!string.IsNullOrEmpty(stime))
{
strSql += " and convert(varchar(10),s_time,120)>='" + stime + "' ";
}
if (!string.IsNullOrEmpty(etime))
{
strSql += " and convert(varchar(10),s_time,120)<='" + etime + "' ";
}
DataTable dt = DbHelperSQLTotal.Query(strSql.ToString()).Tables[0];
return(DbHelperSQLTotal.ToModel <JMP.MDL.jmp_statistics>(dt));
}
/// <summary>
/// 根据开发者ID查询统计图像报表
/// </summary>
/// <param name="uid">开发者ID</param>
/// <param name="startTime">开始日期</param>
/// <param name="endTime">结束日期</param>
/// <param name="startTimeAdy">三日数据开始日期</param>
/// <param name="endTimeAdy">三日数据结束日期</param>
/// <returns></returns>
public DataSet DataStatisticsAdy(int uid, string startTime, string endTime, string startTimeAdy, string endTimeAdy)
{
StringBuilder sql = new StringBuilder();
sql.Append("select a.[Hours],isnull(sum(a_success),0) as a_success,isnull(sum(a_curr),0) as a_curr,isnull(sum(b_success),0) as b_success from ");
sql.Append(" (select convert(int, DATENAME(HOUR, dateadd(hour, number, CONVERT(varchar(12), getdate(), 111)))) as [Hours] from master.dbo.spt_values where type = 'P' and number < DATENAME(HOUR, '" + endTime + "') + 1) as a ");
sql.Append(" left join");
sql.Append(" (");
sql.Append(" select SUM( a.a_success) as a_success,SUM(a.a_curr) as a_curr,0 as b_success,DATENAME(HOUR,a_datetime) as [HOUR] from dbo.jmp_appcount a left join dx_base.[dbo].[jmp_user] b on a.a_uerid = b.u_id where a_datetime >= '" + startTime + "' and a_datetime <= '" + endTime + "' and b.u_id =" + uid + " group by a.a_datetime ");
sql.Append(" union all");
sql.Append(" select 0 as a_success,0 as a_curr,convert(decimal(18,0),(SUM(a.a_success)/3)) as b_success ,DATENAME(HOUR,a_datetime) as [HOUR] from jmp_appcount a left join dx_base.[dbo].[jmp_user] b on a.a_uerid = b.u_id where a_datetime >= '" + startTimeAdy + "' and a_datetime <= '" + endTimeAdy + "' and b.u_id=" + uid + " group by a.a_datetime");
sql.Append(" ) as b");
sql.Append(" on a.[Hours]= b.[HOUR] group by a.[Hours] order by a.[Hours] asc");
return(DbHelperSQLTotal.Query(sql.ToString()));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public JMP.MDL.jmp_trends GetModel(int t_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select t_id, t_app_id, t_newcount, t_activecount, t_time ");
strSql.Append(" from jmp_trends ");
strSql.Append(" where t_id=@t_id");
SqlParameter[] parameters =
{
new SqlParameter("@t_id", SqlDbType.Int, 4)
};
parameters[0].Value = t_id;
JMP.MDL.jmp_trends model = new JMP.MDL.jmp_trends();
DataSet ds = DbHelperSQLTotal.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["t_id"].ToString() != "")
{
model.t_id = int.Parse(ds.Tables[0].Rows[0]["t_id"].ToString());
}
if (ds.Tables[0].Rows[0]["t_app_id"].ToString() != "")
{
model.t_app_id = int.Parse(ds.Tables[0].Rows[0]["t_app_id"].ToString());
}
if (ds.Tables[0].Rows[0]["t_newcount"].ToString() != "")
{
model.t_newcount = int.Parse(ds.Tables[0].Rows[0]["t_newcount"].ToString());
}
if (ds.Tables[0].Rows[0]["t_activecount"].ToString() != "")
{
model.t_activecount = int.Parse(ds.Tables[0].Rows[0]["t_activecount"].ToString());
}
if (ds.Tables[0].Rows[0]["t_time"].ToString() != "")
{
model.t_time = DateTime.Parse(ds.Tables[0].Rows[0]["t_time"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 根据条件查询商务平台首页开发者结算数据（首页统计）
/// </summary>
/// <param name="id">商务ID</param>
/// <param name="date">日期</param>
/// <param name="start">状态</param>
/// <returns></returns>
public JMP.MDL.CoSettlementDeveloperAppDetails GetModelKFZ(int id, string date, int start)
{
string time = "";
if (start == 0)
{
time = " and SettlementDay='" + date + "'";
}
else
{
time = " and CONVERT(varchar(7),SettlementDay,120)='" + date + "'";
}
var sql = new StringBuilder();
sql.AppendFormat(@"select ISNULL(SUM(OrderCount),0) as OrderCount,ISNULL(SUM(TotalAmount),0) as TotalAmount,
ISNULL(SUM(BpPushMoney),0) as BpPushMoney
from(
select ISNULL(SUM(OrderCount),0) as OrderCount,0 as TotalAmount,
0 as BpPushMoney
from {0}.[dbo].[CoSettlementDeveloperAppDetails] a
inner join
(select u_id from {1}.dbo.jmp_user where relation_type=1 and relation_person_id={2}
union all
select a.u_id from {1}.dbo.jmp_user a
left join {1}.dbo.CoAgent b on b.Id=a.relation_person_id
where a.relation_type=2 and b.OwnerId={2}
)as b on a.DeveloperId=b.u_id {3}
UNION
select 0 as OrderCount,ISNULL(SUM(a.TotalAmount),0) as TotalAmount,
ISNULL(SUM(a.BpPushMoney),0) as BpPushMoney
from {0}.[dbo].[CoSettlementDeveloperOverview] a
inner join
(select u_id from {1}.dbo.jmp_user where relation_type=1 and relation_person_id={2}
union all
select a.u_id from {1}.dbo.jmp_user a
left join {1}.dbo.CoAgent b on b.Id=a.relation_person_id
where a.relation_type=2 and b.OwnerId={2}
)as b on a.DeveloperId=b.u_id {3}
) as a", ReportDbName, BaseDbName, id, time);
var ds = DbHelperSQLTotal.Query(sql.ToString());
return(ds.Tables[0].ToEntity <JMP.MDL.CoSettlementDeveloperAppDetails>());
}
/// <summary>
/// 获得前几行数据
/// </summary>
public DataSet GetList(int Top, string strWhere, string filedOrder)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
if (Top > 0)
{
strSql.Append(" top " + Top.ToString());
}
strSql.Append(" * ");
strSql.Append(" FROM jmp_defect ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by " + filedOrder);
return(DbHelperSQLTotal.Query(strSql.ToString()));
}
