public AgentLottoTopInfo QueryLowerAgentSaleByUserId(string[] arrayUserId, DateTime startTime, DateTime endTime)
{
Session.Clear();
AgentLottoTopInfo info = new AgentLottoTopInfo();
startTime = startTime.Date;
endTime = endTime.AddDays(1).Date;
string strSql = "select sum(tab.CTZQ) CTZQ,sum(tab.JCZQ)JCZQ,sum(tab.JCLQ)JCLQ,sum(tab.SZC)SZC,sum(tab.GPC)GPC,SUM(tab.BJDC) BJDC,sum(tab.CTZQ+tab.JCZQ+tab.JCLQ+tab.SZC+tab.GPC+tab.BJDC) as totalMoney from (select r.UserId,r.DisplayName,SUM((case o.GameCode when 'CTZQ' then o.CurrentBettingMoney else 0 end)) CTZQ,SUM((case o.GameCode when 'JCZQ' then o.CurrentBettingMoney else 0 end)) JCZQ,SUM((case o.GameCode when 'JCLQ' then o.CurrentBettingMoney else 0 end)) JCLQ,SUM((case o.GameCode when 'DLT' then o.CurrentBettingMoney when 'SSQ' then o.CurrentBettingMoney when 'FC3D' then o.CurrentBettingMoney when 'PL3' then o.CurrentBettingMoney else 0 end)) SZC,SUM((case o.GameCode when 'CQSSC' then o.CurrentBettingMoney when 'JX11X5' then o.CurrentBettingMoney else 0 end)) GPC,SUM((case o.GameCode when 'BJDC' then o.CurrentBettingMoney else 0 end)) BJDC from C_OrderDetail o inner join C_User_Register r on o.UserId=r.UserId where IsVirtualOrder=0 and o.TicketStatus=90 and o.UserId in (:UserId) and o.BetTime>=:StartTime and o.BetTime<:EndTime group by r.UserId,r.DisplayName) tab";
var result = Session.CreateSQLQuery(strSql)
.SetParameterList("UserId", arrayUserId)
.SetDateTime("StartTime", startTime)
.SetDateTime("EndTime", endTime)
.List();
if (result != null && result.Count > 0)
{
foreach (var item in result)
{
var array = item as object[];
info.CTZQ = Convert.ToDecimal(array[0]);
info.JCZQ = Convert.ToDecimal(array[1]);
info.JCLQ = Convert.ToDecimal(array[2]);
info.SZC = Convert.ToDecimal(array[3]);
info.BJDC = Convert.ToDecimal(array[4]);
info.GPC = Convert.ToDecimal(array[5]);
info.TotalMoney = Convert.ToDecimal(array[6]);
}
}
return(info);
}
/// <summary>
/// 推广销量新版查询(试用)
/// </summary>
/// <returns></returns>
public List <AgentLottoTopInfo> QueryNewLowerAgentSaleListByUserId(string agentId, DateTime startTime, DateTime endTime)
{
Session.Clear();
List <AgentLottoTopInfo> collection = new List <AgentLottoTopInfo>();
startTime = startTime.Date;
endTime = endTime.AddDays(1).Date;
string strSql = "select tabl.CTZQ,tabl.JCZQ,tabl.JCLQ,tabl.SZC,tabl.GPC,tabl.BJDC,(tabl.CTZQ+tabl.JCZQ+tabl.JCLQ+tabl.SZC+tabl.GPC+tabl.BJDC) totalMoney,tabl.UserId,tabl.DisplayName from ( select SUM((case p.GameCode when'CTZQ' then p.TotalSales else 0 end)) CTZQ,SUM((case p.GameCode when'JCZQ' then p.TotalSales else 0 end)) JCZQ,SUM((case p.GameCode when'JCLQ' then p.TotalSales else 0 end)) JCLQ,SUM((case p.GameCode when 'DLT' then p.TotalSales when 'SSQ' then p.TotalSales when 'FC3D' then p.TotalSales when 'PL3' then p.TotalSales else 0 end)) SZC,SUM((case p.GameCode when 'CQSSC' then p.TotalSales when 'JX11X5' then p.TotalSales else 0 end)) GPC,SUM((case p.GameCode when'BJDC' then p.TotalSales else 0 end)) BJDC,u.UserId,u.DisplayName from P_OCAgent_ReportSales p inner join C_User_Register u on p.UserId=p.UserId where ParentUserId=:AgentId and p.CreateTime>=:StartTime and p.CreateTime<:EndTime group by u.UserId,u.DisplayName ) tabl";
var result = Session.CreateSQLQuery(strSql)
.SetString("AgentId", agentId)
.SetDateTime("StartTime", startTime)
.SetDateTime("EndTime", endTime)
.List();
if (result != null && result.Count > 0)
{
foreach (var item in result)
{
AgentLottoTopInfo info = new AgentLottoTopInfo();
var array = item as object[];
info.CTZQ = Convert.ToDecimal(array[0]);
info.JCZQ = Convert.ToDecimal(array[1]);
info.JCLQ = Convert.ToDecimal(array[2]);
info.SZC = Convert.ToDecimal(array[3]);
info.BJDC = Convert.ToDecimal(array[4]);
info.GPC = Convert.ToDecimal(array[5]);
info.TotalMoney = Convert.ToDecimal(array[6]);
info.UserId = Convert.ToString(array[7]);
info.DisplayName = Convert.ToString(array[8]);
collection.Add(info);
}
}
return(collection);
}
public List <AgentLottoTopInfo> QueryLowerAgentSaleListByUserId(string agentId, DateTime startTime, DateTime endTime)
{
Session.Clear();
List <AgentLottoTopInfo> listInfo = new List <AgentLottoTopInfo>();
startTime = startTime.Date;
endTime = endTime.AddDays(1).Date;
//原始
string strSql = "select sum(tab.CTZQ) CTZQ,sum(tab.JCZQ)JCZQ,sum(tab.JCLQ)JCLQ,sum(tab.SZC)SZC,sum(tab.GPC)GPC,SUM(tab.BJDC) BJDC,sum(tab.CTZQ+tab.JCZQ+tab.JCLQ+tab.SZC+tab.GPC+tab.BJDC) as totalMoney,UserId,DisplayName from (select r.UserId,r.DisplayName,SUM((case o.GameCode when 'CTZQ' then o.CurrentBettingMoney else 0 end)) CTZQ,SUM((case o.GameCode when 'JCZQ' then o.CurrentBettingMoney else 0 end)) JCZQ,SUM((case o.GameCode when 'JCLQ' then o.CurrentBettingMoney else 0 end)) JCLQ,SUM((case o.GameCode when 'DLT' then o.CurrentBettingMoney when 'SSQ' then o.CurrentBettingMoney when 'FC3D' then o.CurrentBettingMoney when 'PL3' then o.CurrentBettingMoney else 0 end)) SZC,SUM((case o.GameCode when 'CQSSC' then o.CurrentBettingMoney when 'JX11X5' then o.CurrentBettingMoney else 0 end)) GPC,SUM((case o.GameCode when 'BJDC' then o.CurrentBettingMoney else 0 end)) BJDC from C_OrderDetail o inner join C_User_Register r on o.UserId=r.UserId where IsVirtualOrder=0 and o.TicketStatus=90 and o.BetTime>=:StartTime and o.BetTime<:EndTime and r.UserId in (select UserId from C_User_Register where ParentPath like '%" + agentId + "%' or UserId='" + agentId + "') group by r.UserId,r.DisplayName) tab group by UserId,DisplayName";
var result = Session.CreateSQLQuery(strSql)
//.SetParameterList("UserId", agentId)
.SetDateTime("StartTime", startTime)
.SetDateTime("EndTime", endTime)
.List();
//20150925优化后SQl
//string strSql = "select CTZQ,JCZQ,JCLQ,SZC,GPC,BJDC,(tab.CTZQ+tab.JCZQ+tab.JCLQ+tab.SZC+tab.GPC+tab.BJDC) as totalMoney,utab.UserId,utab.DisplayName from ( select o.UserId,SUM((case o.GameCode when 'CTZQ' then o.CurrentBettingMoney else 0 end)) CTZQ,SUM((case o.GameCode when 'JCZQ' then o.CurrentBettingMoney else 0 end)) JCZQ,SUM((case o.GameCode when 'JCLQ' then o.CurrentBettingMoney else 0 end)) JCLQ,SUM((case o.GameCode when 'DLT' then o.CurrentBettingMoney when 'SSQ' then o.CurrentBettingMoney when 'FC3D' then o.CurrentBettingMoney when 'PL3' then o.CurrentBettingMoney else 0 end)) SZC,SUM((case o.GameCode when 'CQSSC' then o.CurrentBettingMoney when 'JX11X5' then o.CurrentBettingMoney else 0 end)) GPC,SUM((case o.GameCode when 'BJDC' then o.CurrentBettingMoney else 0 end)) BJDC from C_OrderDetail o where IsVirtualOrder=0 and o.TicketStatus=90 and o.BetTime>=:StartTime and o.BetTime<:EndTime group by o.UserId) tab inner join (select UserId,DisplayName from C_User_Register where ParentPath like '%" + agentId + "%' or UserId='" + agentId + "') utab on tab.UserId=utab.UserId";
//string strSql = "select CTZQ,JCZQ,JCLQ,SZC,GPC,BJDC,(tab.CTZQ+tab.JCZQ+tab.JCLQ+tab.SZC+tab.GPC+tab.BJDC) as totalMoney,tab.UserId,tab.DisplayName from ( select o.UserId,SUM((case o.GameCode when 'CTZQ' then o.CurrentBettingMoney else 0 end)) CTZQ,SUM((case o.GameCode when 'JCZQ' then o.CurrentBettingMoney else 0 end)) JCZQ,SUM((case o.GameCode when 'JCLQ' then o.CurrentBettingMoney else 0 end)) JCLQ,SUM((case o.GameCode when 'DLT' then o.CurrentBettingMoney when 'SSQ' then o.CurrentBettingMoney when 'FC3D' then o.CurrentBettingMoney when 'PL3' then o.CurrentBettingMoney else 0 end)) SZC,SUM((case o.GameCode when 'CQSSC' then o.CurrentBettingMoney when 'JX11X5' then o.CurrentBettingMoney else 0 end)) GPC,SUM((case o.GameCode when 'BJDC' then o.CurrentBettingMoney else 0 end)) BJDC,u.DisplayName from C_OrderDetail o inner join C_User_Register u on o.UserId=u.UserId where IsVirtualOrder=0 and o.TicketStatus=90 and o.BetTime>=:StartTime and o.BetTime<:EndTime and (u.ParentPath like '%" + agentId + "%' or u.UserId='" + agentId + "') group by o.UserId,u.DisplayName ) tab";
//var result = Session.CreateSQLQuery(strSql)
// .SetDateTime("StartTime", startTime)
// .SetDateTime("EndTime", endTime)
// .List();
if (result != null && result.Count > 0)
{
foreach (var item in result)
{
AgentLottoTopInfo info = new AgentLottoTopInfo();
var array = item as object[];
info.CTZQ = Convert.ToDecimal(array[0]);
info.JCZQ = Convert.ToDecimal(array[1]);
info.JCLQ = Convert.ToDecimal(array[2]);
info.SZC = Convert.ToDecimal(array[3]);
info.BJDC = Convert.ToDecimal(array[4]);
info.GPC = Convert.ToDecimal(array[5]);
info.TotalMoney = Convert.ToDecimal(array[6]);
info.UserId = Convert.ToString(array[7]);
info.DisplayName = Convert.ToString(array[8]);
listInfo.Add(info);
}
}
return(listInfo);
}
/// <summary>
/// 测试
/// </summary>
/// <param name="agentId"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public List <AgentLottoTopInfo> Test_QueryLowerAgentSaleListByUserId(string agentId, DateTime startTime, DateTime endTime)
{
Session.Clear();
List <AgentLottoTopInfo> listInfo = new List <AgentLottoTopInfo>();
startTime = startTime.Date;
endTime = endTime.AddDays(1).Date;
// 通过数据库存储过程进行查询
Dictionary <string, object> outputs;
var query = CreateOutputQuery(Session.GetNamedQuery("P_Agent_QueryLowerAgentSaleList"))
.AddInParameter("AgentId", agentId)
.AddInParameter("StartTime", startTime)
.AddInParameter("EndTime", endTime)
.AddOutParameter("TotalCount", "Int32");
var dt = query.GetDataTable(out outputs);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
AgentLottoTopInfo info = new AgentLottoTopInfo();
info.CTZQ = Convert.ToDecimal(row["CTZQ"]);
info.JCZQ = Convert.ToDecimal(row["JCZQ"]);
info.JCLQ = Convert.ToDecimal(row["JCLQ"]);
info.SZC = Convert.ToDecimal(row["SZC"]);
info.BJDC = Convert.ToDecimal(row["BJDC"]);
info.GPC = Convert.ToDecimal(row["GPC"]);
info.TotalMoney = Convert.ToDecimal(row["TotalMoney"]);
info.UserId = Convert.ToString(row["UserId"]);
info.DisplayName = Convert.ToString(row["DisplayName"]);
listInfo.Add(info);
}
}
return(listInfo);
}