/// <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>
/// <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>
/// <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 int Add(JMP.MDL.jmp_trends model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into jmp_trends(");
strSql.Append("t_app_id,t_newcount,t_activecount,t_time");
strSql.Append(") values (");
strSql.Append("@t_app_id,@t_newcount,@t_activecount,@t_time");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@t_app_id", SqlDbType.Int, 4),
new SqlParameter("@t_newcount", SqlDbType.Int, 4),
new SqlParameter("@t_activecount", SqlDbType.Int, 4),
new SqlParameter("@t_time", SqlDbType.DateTime)
};
parameters[0].Value = model.t_app_id;
parameters[1].Value = model.t_newcount;
parameters[2].Value = model.t_activecount;
parameters[3].Value = model.t_time;
object obj = DbHelperSQLTotal.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(JMP.MDL.jmp_revenue_active model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into jmp_revenue_active(");
strSql.Append("r_users,r_moneys,r_orders,r_date,r_appid");
strSql.Append(") values (");
strSql.Append("@r_users,@r_moneys,@r_orders,@r_date,@r_appid");
strSql.Append(") ");
SqlParameter[] parameters =
{
new SqlParameter("@r_users", SqlDbType.Int, 4),
new SqlParameter("@r_moneys", SqlDbType.Decimal, 17),
new SqlParameter("@r_orders", SqlDbType.Int, 4),
new SqlParameter("@r_date", SqlDbType.DateTime),
new SqlParameter("@r_appid", SqlDbType.Int, 4)
};
parameters[0].Value = model.r_users;
parameters[1].Value = model.r_moneys;
parameters[2].Value = model.r_orders;
parameters[3].Value = model.r_date;
parameters[4].Value = model.r_appid;
return(DbHelperSQLTotal.ExecuteSql(strSql.ToString(), parameters) > 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>
/// 根据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>
public int Add(JMP.MDL.jmp_sales_user model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into jmp_sales_user(");
strSql.Append("r_moneys,r_date,r_appid");
strSql.Append(") values (");
strSql.Append("@r_moneys,@r_date,@r_appid");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@r_moneys", SqlDbType.Money, 8),
new SqlParameter("@r_date", SqlDbType.DateTime),
new SqlParameter("@r_appid", SqlDbType.Int, 4)
};
parameters[0].Value = model.r_moneys;
parameters[1].Value = model.r_date;
parameters[2].Value = model.r_appid;
object obj = DbHelperSQLTotal.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="sqls">SQL语句</param>
/// <param name="pageIndexs">当前页</param>
/// <param name="PageSize">每页显示数量</param>
/// <param name="pageCount">总条数</param>
/// <returns></returns>
public List <JMP.MDL.jmp_app_report> SelectList(string sqls, int pageIndexs, int PageSize, out int pageCount)
{
string sql = string.Format(sqls);
SqlParameter[] s = new[] {
new SqlParameter("@sqlstr", sql.ToString()),
new SqlParameter("@pageIndex", pageIndexs),
new SqlParameter("@pageSize", PageSize)
};
SqlDataReader reader = DbHelperSQLTotal.RunProcedure("page", s);
pageCount = 0;
if (reader.NextResult())
{
while (reader.Read())
{
pageCount = Convert.ToInt32(reader[0].ToString());
}
if (reader.NextResult())
{
if (reader.HasRows)
{
dt.Load(reader);
}
}
}
reader.Close();
return(DbHelperSQLTotal.ToList <JMP.MDL.jmp_app_report>(dt));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(JMP.MDL.jmp_statistics model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into jmp_statistics(");
strSql.Append("s_brand,s_app_id,s_count,s_time");
strSql.Append(") values (");
strSql.Append("@s_brand,@s_app_id,@s_count,@s_time");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@s_brand", SqlDbType.NVarChar, -1),
new SqlParameter("@s_app_id", SqlDbType.Int, 4),
new SqlParameter("@s_count", SqlDbType.Int, 4),
new SqlParameter("@s_time", SqlDbType.DateTime)
};
parameters[0].Value = model.s_brand;
parameters[1].Value = model.s_app_id;
parameters[2].Value = model.s_count;
parameters[3].Value = model.s_time;
object obj = DbHelperSQLTotal.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(JMP.MDL.jmp_sales_user model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update jmp_sales_user set ");
strSql.Append("r_appid=@r_appid,");
strSql.Append("r_moneys=@r_moneys,");
strSql.Append("r_date=@r_date");
strSql.Append(" where r_id=@r_id ");
SqlParameter[] parameters =
{
new SqlParameter("@r_id", SqlDbType.Int, 4),
new SqlParameter("@r_appid", SqlDbType.Int, 4),
new SqlParameter("@r_moneys", SqlDbType.Money, 8),
new SqlParameter("@r_date", SqlDbType.DateTime)
};
parameters[0].Value = model.r_id;
parameters[1].Value = model.r_appid;
parameters[2].Value = model.r_moneys;
parameters[3].Value = model.r_date;
int rows = DbHelperSQLTotal.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(JMP.MDL.jmp_appreport model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into jmp_appreport(");
strSql.Append("a_successratio,a_alipay,a_wechat,a_curr,a_arpur,a_datetime,a_time,a_appname,a_appid,a_uerid,a_equipment,a_count,a_success,a_notpay,a_request,a_money,a_qqwallet");
strSql.Append(") values (");
strSql.Append("@a_successratio,@a_alipay,@a_wechat,@a_curr,@a_arpur,@a_datetime,@a_time,@a_appname,@a_appid,@a_uerid,@a_equipment,@a_count,@a_success,@a_notpay,@a_request,@a_money,@a_qqwallet");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@a_successratio", SqlDbType.Decimal, 9),
new SqlParameter("@a_alipay", SqlDbType.Decimal, 9),
new SqlParameter("@a_wechat", SqlDbType.Decimal, 9),
new SqlParameter("@a_curr", SqlDbType.Decimal, 9),
new SqlParameter("@a_arpur", SqlDbType.Decimal, 9),
new SqlParameter("@a_datetime", SqlDbType.DateTime),
new SqlParameter("@a_time", SqlDbType.DateTime),
new SqlParameter("@a_appname", SqlDbType.NVarChar, -1),
new SqlParameter("@a_appid", SqlDbType.Int, 4),
new SqlParameter("@a_uerid", SqlDbType.Int, 4),
new SqlParameter("@a_equipment", SqlDbType.Decimal, 9),
new SqlParameter("@a_count", SqlDbType.Decimal, 9),
new SqlParameter("@a_success", SqlDbType.Decimal, 9),
new SqlParameter("@a_notpay", SqlDbType.Decimal, 9),
new SqlParameter("@a_request", SqlDbType.Decimal, 9),
new SqlParameter("@a_money", SqlDbType.Decimal, 9),
new SqlParameter("@a_qqwallet", SqlDbType.Decimal, 9)
};
parameters[0].Value = model.a_successratio;
parameters[1].Value = model.a_alipay;
parameters[2].Value = model.a_wechat;
parameters[3].Value = model.a_curr;
parameters[4].Value = model.a_arpur;
parameters[5].Value = model.a_datetime;
parameters[6].Value = model.a_time;
parameters[7].Value = model.a_appname;
parameters[8].Value = model.a_appid;
parameters[9].Value = model.a_uerid;
parameters[10].Value = model.a_equipment;
parameters[11].Value = model.a_count;
parameters[12].Value = model.a_success;
parameters[13].Value = model.a_notpay;
parameters[14].Value = model.a_request;
parameters[15].Value = model.a_money;
parameters[16].Value = model.a_qqwallet;
object obj = DbHelperSQLTotal.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <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>
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>
/// 获取列表
/// </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>
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="merchantId"> 商务ID</param>
/// <returns></returns>
public string TodayResults(int merchantId, string kstime, string endtime)
{
string sqlstr = string.Format(" select isnull(SUM(a_curr),0) a_curr from dbo.jmp_appcount as a left join " + JMP.DbName.PubDbName.dbbase + ".dbo.jmp_user as b on a.a_uerid=b.u_id where b.u_merchant_id='" + merchantId + "' and a.a_datetime>=@kstime and a.a_datetime<=@endtime ");
SqlParameter[] par =
{
new SqlParameter("@kstime", kstime),
new SqlParameter("@endtime", endtime)
};
return(DbHelperSQLTotal.GetSingle(sqlstr.ToString(), par).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 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 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 bool Update(JMP.MDL.jmp_app_report model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update jmp_app_report set ");
strSql.Append("r_times=@r_times,");
strSql.Append("r_starttime=@r_starttime,");
strSql.Append("r_username=@r_username,");
strSql.Append("r_uid=@r_uid,");
strSql.Append("r_app_key=@r_app_key,");
strSql.Append("r_app_name=@r_app_name,");
strSql.Append("r_equipment=@r_equipment,");
strSql.Append("r_succeed=@r_succeed,");
strSql.Append("r_notpay=@r_notpay,");
strSql.Append("r_alipay=@r_alipay");
strSql.Append(" where r_id=@r_id");
SqlParameter[] parameters =
{
new SqlParameter("@r_times", SqlDbType.DateTime),
new SqlParameter("@r_starttime", SqlDbType.DateTime),
new SqlParameter("@r_username", SqlDbType.NVarChar, -1),
new SqlParameter("@r_uid", SqlDbType.Int, 4),
new SqlParameter("@r_app_key", SqlDbType.NVarChar, -1),
new SqlParameter("@r_app_name", SqlDbType.NVarChar, -1),
new SqlParameter("@r_equipment", SqlDbType.Int, 4),
new SqlParameter("@r_succeed", SqlDbType.Int, 4),
new SqlParameter("@r_notpay", SqlDbType.Int, 4),
new SqlParameter("@r_alipay", SqlDbType.Money, 8),
new SqlParameter("@r_id", SqlDbType.Int, 4)
};
parameters[0].Value = model.r_times;
parameters[1].Value = model.r_starttime;
parameters[2].Value = model.r_username;
parameters[3].Value = model.r_uid;
parameters[4].Value = model.r_app_key;
parameters[5].Value = model.r_app_name;
parameters[6].Value = model.r_equipment;
parameters[7].Value = model.r_succeed;
parameters[8].Value = model.r_notpay;
parameters[9].Value = model.r_alipay;
parameters[10].Value = model.r_id;
int rows = DbHelperSQLTotal.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <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>
public int Add(JMP.MDL.jmp_keep model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into jmp_keep(");
strSql.Append("k_day6,k_day7,k_day14,k_day30,k_time,k_app_id,k_type,k_usercount,k_day1,k_day2,k_day3,k_day4,k_day5");
strSql.Append(") values (");
strSql.Append("@k_day6,@k_day7,@k_day14,@k_day30,@k_time,@k_app_id,@k_type,@k_usercount,@k_day1,@k_day2,@k_day3,@k_day4,@k_day5");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@k_day6", SqlDbType.Decimal, 5),
new SqlParameter("@k_day7", SqlDbType.Decimal, 5),
new SqlParameter("@k_day14", SqlDbType.Decimal, 5),
new SqlParameter("@k_day30", SqlDbType.Decimal, 5),
new SqlParameter("@k_time", SqlDbType.DateTime),
new SqlParameter("@k_app_id", SqlDbType.Int, 4),
new SqlParameter("@k_type", SqlDbType.Int, 4),
new SqlParameter("@k_usercount", SqlDbType.Int, 4),
new SqlParameter("@k_day1", SqlDbType.Decimal, 5),
new SqlParameter("@k_day2", SqlDbType.Decimal, 5),
new SqlParameter("@k_day3", SqlDbType.Decimal, 5),
new SqlParameter("@k_day4", SqlDbType.Decimal, 5),
new SqlParameter("@k_day5", SqlDbType.Decimal, 5)
};
parameters[0].Value = model.k_day6;
parameters[1].Value = model.k_day7;
parameters[2].Value = model.k_day14;
parameters[3].Value = model.k_day30;
parameters[4].Value = model.k_time;
parameters[5].Value = model.k_app_id;
parameters[6].Value = model.k_type;
parameters[7].Value = model.k_usercount;
parameters[8].Value = model.k_day1;
parameters[9].Value = model.k_day2;
parameters[10].Value = model.k_day3;
parameters[11].Value = model.k_day4;
parameters[12].Value = model.k_day5;
object obj = DbHelperSQLTotal.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 查询今日成功金额
/// </summary>
/// <param name="appid"></param>
/// <returns></returns>
public object dayCj(int appid)
{
string sql = string.Format("SELECT SUM(a_alipay)+ SUM(a_wechat)+SUM(a_qqwallet) AS moeny FROM " + JMP.DbName.PubDbName.dbtotal + ".dbo.jmp_appcount WHERE a_appid=@appid AND a_datetime>'" + System.DateTime.Now.AddDays(-1).ToShortDateString() + "'");
SqlParameter[] parameters =
{
new SqlParameter("@appid", SqlDbType.Int, 4)
};
parameters[0].Value = appid;
object obj = DbHelperSQLTotal.GetSingle(sql.ToString(), parameters);
return(obj);
}
/// <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>
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()));
}
