public IList <EndAheadMinuteInfo> GetEndAheadMinuteList()
{
string sql = "SELECT DISTINCT [GameName],[SystemEndAheadMinute] FROM [T_Lottery_PlayTypes]";
DataTable table = DbAccess.GetDataTableBySQL(sql);
return(ORMHelper.DataTableToList <EndAheadMinuteInfo>(table));
}
public IList <IssuseInfo> GetCommonIsuseList(int lotteryId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine("SELECT * FROM (");
sqlBuilder.AppendLine("SELECT * FROM (");
sqlBuilder.AppendLine(" SELECT TOP 1 ID,Name,WinLotteryNumber,StartTime,EndTime,IsOpened ");
sqlBuilder.AppendLine(" FROM T_Lottery_Isuses ");
sqlBuilder.AppendLine(" WHERE LotteryID={0} AND ISNULL(WinLotteryNumber, '')<>'' ");
sqlBuilder.AppendLine(" ORDER BY EndTime DESC");
sqlBuilder.AppendLine(")a UNION ");
sqlBuilder.AppendLine("SELECT * FROM (");
sqlBuilder.AppendLine(" SELECT TOP 1 ID,Name,WinLotteryNumber,StartTime,EndTime,IsOpened ");
sqlBuilder.AppendLine(" FROM T_Lottery_Isuses ");
sqlBuilder.AppendLine(" WHERE LotteryID={0} AND GETDATE()>EndTime ");
sqlBuilder.AppendLine(" ORDER BY EndTime DESC");
sqlBuilder.AppendLine(")a UNION ");
sqlBuilder.AppendLine("SELECT TOP 1 ID,Name,WinLotteryNumber,StartTime,EndTime,IsOpened ");
sqlBuilder.AppendLine("FROM T_Lottery_Isuses ");
sqlBuilder.AppendLine("WHERE LotteryID={0} and IsOpened = 0 AND GETDATE() BETWEEN StartTime AND EndTime ");
sqlBuilder.AppendLine("UNION SELECT * FROM ( ");
sqlBuilder.AppendLine(" SELECT TOP 49 ID,Name,WinLotteryNumber,StartTime,EndTime,IsOpened ");
sqlBuilder.AppendLine(" FROM T_Lottery_Isuses ");
sqlBuilder.AppendLine(" WHERE LotteryID={0} AND GETDATE()<StartTime ");
sqlBuilder.AppendLine(" ORDER BY StartTime");
sqlBuilder.AppendLine(")a)b ORDER BY EndTime");
DataTable table = DbAccess.GetDataTableBySQL(sqlBuilder.ToString(), lotteryId);
return(ORMHelper.DataTableToList <IssuseInfo>(table));
}
public AgentWithdrawRecordCollection GetAgentWithdrawRecord(string userId, DateTime fromDate, DateTime toDate, int pageIndex, int pageSize)
{
Session.Clear();
int totalCount = 0;
pageIndex = pageIndex < 0 ? 0 : pageIndex;
pageSize = pageSize > BusinessHelper.MaxPageSize ? BusinessHelper.MaxPageSize : pageSize;
Dictionary <string, object> outputs;
var query = CreateOutputQuery(Session.GetNamedQuery("P_Agent_GetWithdrawRecord"));
query = query.AddInParameter("userId", userId);
query = query.AddInParameter("fromDate", fromDate);
query = query.AddInParameter("toDate", toDate);
query = query.AddInParameter("pageIndex", pageIndex);
query = query.AddInParameter("pageSize", pageSize);
query = query.AddOutParameter("totalCount", "Int32");
var ds = query.GetDataSet(out outputs);
totalCount = UsefullHelper.GetDbValue <int>(outputs["totalCount"]);
var list = ORMHelper.DataTableToList <AgentWithdrawRecordInfo>(ds.Tables[0]);
var requestingMoney = UsefullHelper.GetDbValue <decimal>(ds.Tables[1].Rows[0]["RequestingMoney"]);
var successMoney = UsefullHelper.GetDbValue <decimal>(ds.Tables[1].Rows[0]["SuccessMoney"]);
var refusedMoney = UsefullHelper.GetDbValue <decimal>(ds.Tables[1].Rows[0]["RefusedMoney"]);
return(new AgentWithdrawRecordCollection()
{
AgentWithdrawRecordList = list,
TotalCount = totalCount,
RequestingMoney = requestingMoney,
SuccessMoney = successMoney,
RefusedMoney = refusedMoney
});
}
public IList <AgentSchemeInfo> GetAgentScheme(string agentId, string userId, string displayName, int progressStatus, int ticketStatus
, DateTime fromDate, DateTime toDate, int pageIndex, int pageSize, out int totalCount, out int totalUser, out int totalScheme, out decimal totalMoney1)
{
Session.Clear();
pageIndex = pageIndex < 0 ? 0 : pageIndex;
pageSize = pageSize > BusinessHelper.MaxPageSize ? BusinessHelper.MaxPageSize : pageSize;
Dictionary <string, object> outputs;
var query = CreateOutputQuery(Session.GetNamedQuery("P_Agent_GetScheme"));
query = query.AddInParameter("agentId", agentId);
query = query.AddInParameter("userId", userId);
query = query.AddInParameter("displayName", displayName);
query = query.AddInParameter("progressStatus", progressStatus);
query = query.AddInParameter("ticketStatus", ticketStatus);
query = query.AddInParameter("fromDate", fromDate);
query = query.AddInParameter("toDate", toDate);
query = query.AddInParameter("pageIndex", pageIndex);
query = query.AddInParameter("pageSize", pageSize);
query = query.AddOutParameter("totalCount", "Int32");
var ds = query.GetDataSet(out outputs);
totalCount = UsefullHelper.GetDbValue <int>(outputs["totalCount"]);
totalUser = UsefullHelper.GetDbValue <int>(ds.Tables[1].Rows[0]["TotalUser"]);
totalScheme = UsefullHelper.GetDbValue <int>(ds.Tables[1].Rows[0]["TotalScheme"]);
totalMoney1 = UsefullHelper.GetDbValue <decimal>(ds.Tables[1].Rows[0]["TotalMoney1"]);
return(ORMHelper.DataTableToList <AgentSchemeInfo>(ds.Tables[0]));
}
/// <summary>
/// 查询代理用户
/// </summary>
public IList <AgentUserInfo> GetAgentUserByKeyword(string keyword, int isAgent, string pAgentId, int pageIndex, int pageSize, out int totalCount)
{
Session.Clear();
pageIndex = pageIndex < 0 ? 0 : pageIndex;
pageSize = pageSize > BusinessHelper.MaxPageSize ? BusinessHelper.MaxPageSize : pageSize;
Dictionary <string, object> outputs;
var query = CreateOutputQuery(Session.GetNamedQuery("P_Agent_QueryUserList"));
query = query.AddInParameter("keyword", keyword);
query = query.AddInParameter("pAgentId", pAgentId);
query = query.AddInParameter("isAgent", isAgent);
query = query.AddInParameter("pageIndex", pageIndex);
query = query.AddInParameter("pageSize", pageSize);
query = query.AddOutParameter("totalCount", "Int32");
var dt = query.GetDataTable(out outputs);
totalCount = UsefullHelper.GetDbValue <int>(outputs["totalCount"]);
return(ORMHelper.DataTableToList <AgentUserInfo>(dt));
}
public IList <AgentWaitingCommissionOrderInfo> QueryAgentWaitingCommissionOrderList(DateTime fromDate, DateTime toDate, int pageIndex, int pageSize, out int totalCount)
{
Session.Clear();
pageIndex = pageIndex < 0 ? 0 : pageIndex;
pageSize = pageSize > BusinessHelper.MaxPageSize ? BusinessHelper.MaxPageSize : pageSize;
Dictionary <string, object> outputs;
var query = CreateOutputQuery(Session.GetNamedQuery("P_Agent_QueryWaitingCommissionOrderList"));
query = query.AddInParameter("fromDate", fromDate);
query = query.AddInParameter("toDate", toDate);
query = query.AddInParameter("pageIndex", pageIndex);
query = query.AddInParameter("pageSize", pageSize);
query = query.AddOutParameter("totalCount", "Int32");
var dt = query.GetDataTable(out outputs);
totalCount = UsefullHelper.GetDbValue <int>(outputs["totalCount"]);
return(ORMHelper.DataTableToList <AgentWaitingCommissionOrderInfo>(dt));
}
public IList <AgentCommissionDetailInfo> GetCommissionReport(string userId, string displayName, DateTime fromDate, DateTime toDate, string pAgentId, int applyState, out decimal saleTotal, out decimal actualCommissionTotal)
{
Session.Clear();
Dictionary <string, object> outputs;
var query = CreateOutputQuery(Session.GetNamedQuery("P_Agent_GetCommissionReport"));
query = query.AddInParameter("pAgentId", pAgentId);
query = query.AddInParameter("userId", userId);
query = query.AddInParameter("displayName", displayName);
query = query.AddInParameter("fromDate", fromDate);
query = query.AddInParameter("toDate", toDate);
query = query.AddInParameter("applyState", applyState);
query = query.AddOutParameter("saleTotal", "Decimal");
query = query.AddOutParameter("actualCommissionTotal", "Decimal");
var dt = query.GetDataTable(out outputs);
var list = ORMHelper.DataTableToList <AgentCommissionDetailInfo>(dt);
saleTotal = list.Sum(o => o.Sale);
actualCommissionTotal = list.Sum(o => o.BeforeCommission);
return(list);
}
public IList <AgentCommissionDetailInfo> GetAgentCommissionDetailList(string pAgentId, string userId, string displayName, DateTime fromDate, DateTime toDate, int pageIndex, int pageSize, int applyState, out int totalCount)
{
Session.Clear();
pageIndex = pageIndex < 0 ? 0 : pageIndex;
pageSize = pageSize > BusinessHelper.MaxPageSize ? BusinessHelper.MaxPageSize : pageSize;
Dictionary <string, object> outputs;
var query = CreateOutputQuery(Session.GetNamedQuery("P_Agent_QueryCommissionDetailList"));
query = query.AddInParameter("fromDate", fromDate);
query = query.AddInParameter("toDate", toDate);
query = query.AddInParameter("pAgentId", pAgentId);
query = query.AddInParameter("userId", userId);
query = query.AddInParameter("displayName", displayName);
query = query.AddInParameter("pageIndex", pageIndex);
query = query.AddInParameter("pageSize", pageSize);
query = query.AddInParameter("applyState", applyState);
query = query.AddOutParameter("totalCount", "Int32");
var dt = query.GetDataTable(out outputs);
totalCount = UsefullHelper.GetDbValue <int>(outputs["totalCount"]);
return(ORMHelper.DataTableToList <AgentCommissionDetailInfo>(dt));
}
public AgentCommissionDetailCollection GetAgentGetCommissionReportList(string gameCode, string gameType, string pAgentId, string userId, string displayName, int category, DateTime fromDate, DateTime toDate, int pageIndex, int pageSize)
{
Session.Clear();
pageIndex = pageIndex < 0 ? 0 : pageIndex;
pageSize = pageSize > BusinessHelper.MaxPageSize ? BusinessHelper.MaxPageSize : pageSize;
Dictionary <string, object> outputs;
var query = CreateOutputQuery(Session.GetNamedQuery("P_Agent_GetCommissionReportList"));
query = query.AddInParameter("GameCode", gameCode);
query = query.AddInParameter("GameType", gameType);
query = query.AddInParameter("PAgentId", pAgentId);
query = query.AddInParameter("UserId", userId);
query = query.AddInParameter("DisplayName", displayName);
query = query.AddInParameter("Category", category);
query = query.AddInParameter("FromDate", fromDate);
query = query.AddInParameter("ToDate", toDate);
query = query.AddInParameter("PageIndex", pageIndex);
query = query.AddInParameter("PageSize", pageSize);
query = query.AddOutParameter("TotalCount", "Int32");
var ds = query.GetDataSet(out outputs);
var totalCount = UsefullHelper.GetDbValue <int>(outputs["TotalCount"]);
var listReport = ORMHelper.DataTableToList <AgentCommissionDetailInfo>(ds.Tables[0]);
var list = ORMHelper.DataTableToList <AgentCommissionDetailInfo>(ds.Tables[1]);
var saleTotal = listReport.Sum(o => o.Sale);
var actualCommissionTotal = listReport.Sum(o => o.BeforeCommission);
return(new AgentCommissionDetailCollection()
{
AgentCommissionDetailList = list,
AgentCommissionReport = listReport,
ActualCommissionTotal = actualCommissionTotal,
SaleTotal = saleTotal,
TotalCount = totalCount
});
}
public void AgentCommissionClose(string createByUserId)
{
var manager = new AgentManager();
var history = manager.GetAgentCaculateHistoryByDesc();
if (history == null)
{
history = new AgentCaculateHistory()
{
CaculateTimeFrom = DateTime.Parse("2012-10-01"),
CaculateTimeTo = DateTime.Now
};
}
else
{
history = new AgentCaculateHistory()
{
CaculateTimeFrom = history.CaculateTimeTo,
CaculateTimeTo = DateTime.Now
};
}
if (history.CaculateTimeFrom >= history.CaculateTimeTo)
{
return;
}
bool tag = true;
int totalCount = 0;
int pageIndex = 0;
int pageSize = 100;
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
do
{
var waitingOrderList = manager.QueryAgentWaitingCommissionOrderList(history.CaculateTimeFrom, history.CaculateTimeTo, pageIndex, pageSize, out totalCount);
foreach (var item in waitingOrderList)
{
try
{
AddAgentCommissionDetailByTicketing(item.UserId, item.GameCode, item.GameType, item.TotalBuyMoney, 5, item.SchemeId, item.SchemeType, item.ComplateTime, history);
history.TotalOrderCount += 1;
history.TotalOrderMoney += item.TotalMoney;
history.TotalBuyMoney += item.TotalBuyMoney;
}
catch (Exception ex)
{
history.ErrorCount += 1;
history.ErrorOrderMoney += item.TotalMoney;
history.ErrorBuyMoney += item.TotalBuyMoney;
history.ErrorSchemeIdList += item.SchemeId + ";";
var writer = Common.Log.LogWriterGetter.GetLogWriter();
}
}
pageIndex += 1;
if (((totalCount + pageSize - 1) / pageSize) <= pageIndex)
{
tag = false;
}
}while (tag);
stopwatch.Stop();
history.MillisecondSpan = stopwatch.ElapsedMilliseconds;
history.CreateBy = createByUserId;
history.CreateTime = DateTime.Now;
history.TotalAgentCount = ORMHelper.DataTableToList <AgentCloseReturnPointInfo>(AgentReturnPointCollection).GroupBy(o => o.AgentId).Count();
manager.AddAgentCaculateHistory(history);
}
