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); }