/// <summary> /// 统计指定存币机营收 /// </summary> /// <returns></returns> public static DataSet GetSaveCoinMachineRevenue(int merchId, int deviceId, string sDate = null, string eDate = null, int pageIndex = 1, int pageSize = 10) { string strWhere = string.Empty; if (!string.IsNullOrWhiteSpace(sDate) && string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime >= '{0}'", sDate); } else if (string.IsNullOrWhiteSpace(sDate) && !string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime <= '{0}'", eDate); } else if (!string.IsNullOrWhiteSpace(sDate) && !string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime BETWEEN '{0}' AND '{1}'", sDate, eDate); } else { sDate = DateTime.Now.ToString("yyyy-MM-dd"); eDate = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"); strWhere = string.Format(" AND RealTime BETWEEN '{0}' AND '{1}'", sDate, eDate); } #region Sql语句 string strSql = @" --存币 SELECT c.DeviceName, b.HeadAddress, ISNULL(SUM(Coins), 0) AS SaveCoins FROM flw_485_savecoin a INNER JOIN Data_MerchDevice b ON b.DeviceID = a.DeviceID INNER JOIN Base_DeviceInfo c ON c.ID = a.DeviceID WHERE a.MerchID = {0} AND a.DeviceID = {1} {2} GROUP BY c.DeviceName, b.HeadAddress ; SELECT ICCardID, MemberName, Mobile, '存币' AS FlowType, Coins, Balance, CONVERT(varchar(100), RealTime, 120) AS RealTime FROM ( SELECT ROW_NUMBER() OVER (ORDER BY RealTime DESC) rownumber , f.ICCardID, m.MemberName, m.Mobile, f.Coins, f.Balance, f.RealTime FROM flw_485_savecoin f INNER JOIN t_member m ON m.ICCardID = f.ICCardID WHERE f.MerchID = {0} {2} ) AS a WHERE rownumber BETWEEN {3} AND {4} "; #endregion strSql = string.Format(strSql, merchId, deviceId, strWhere, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize); DataSet ds = XCCloudRS232BLL.ExecuterSqlToDataSet(strSql); return(ds); }
/// <summary> /// 按控制器查询外设游戏币营收 /// </summary> /// <returns></returns> public static DataSet GetRoutersAmount(int merchId, Nullable <int> routerId = null, string sDate = null, string eDate = null) { string strWhere = string.Empty; if (!string.IsNullOrWhiteSpace(sDate) && string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime >= '{0}'", sDate); } else if (string.IsNullOrWhiteSpace(sDate) && !string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime <= '{0}'", eDate); } else if (!string.IsNullOrWhiteSpace(sDate) && !string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime BETWEEN '{0}' AND '{1}'", sDate, eDate); } else { sDate = DateTime.Now.ToString("yyyy-MM-dd"); eDate = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"); strWhere = string.Format(" AND RealTime BETWEEN '{0}' AND '{1}'", sDate, eDate); } #region Sql语句 string strSql = @" --存币 SELECT c.DeviceName, b.HeadAddress, ISNULL(SUM(Coins), 0) AS SaveCoins FROM flw_485_savecoin a INNER JOIN Data_MerchDevice b ON b.DeviceID = a.DeviceID INNER JOIN Base_DeviceInfo c ON c.ID = a.DeviceID WHERE a.MerchID = {0} AND b.ParentID = {1} {2} GROUP BY c.DeviceName, b.HeadAddress ; --提币 SELECT c.DeviceName, b.HeadAddress, ISNULL(SUM(a.Coins), 0) AS SaleCoins FROM flw_coin_sale a INNER JOIN Data_MerchDevice b ON b.DeviceID = a.DeviceID INNER JOIN Base_DeviceInfo c ON c.ID = a.DeviceID WHERE a.WorkType IN (3,6,7) AND a.MerchID = {0} AND b.ParentID = {1} {2} GROUP BY c.DeviceName, b.HeadAddress ; "; #endregion strSql = string.Format(strSql, merchId, routerId, strWhere); DataSet ds = XCCloudRS232BLL.ExecuterSqlToDataSet(strSql); return(ds); }
/// <summary> /// 会员数据统计 /// </summary> /// <returns></returns> public static DataSet GetMemberSummary(int merchId, string ICCardID = null, string sDate = null, string eDate = null, int pageIndex = 1, int pageSize = 10) { string strWhere = string.Empty; if (!string.IsNullOrWhiteSpace(ICCardID)) { strWhere += string.Format(" AND ICCardID = {0}", ICCardID); } if (!string.IsNullOrWhiteSpace(sDate) && string.IsNullOrWhiteSpace(eDate)) { strWhere += string.Format(" AND JoinTime >= '{0}'", sDate); } else if (string.IsNullOrWhiteSpace(sDate) && !string.IsNullOrWhiteSpace(eDate)) { strWhere += string.Format(" AND JoinTime <= '{0}'", eDate); } else if (!string.IsNullOrWhiteSpace(sDate) && !string.IsNullOrWhiteSpace(eDate)) { strWhere += string.Format(" AND JoinTime BETWEEN '{0}' AND '{1}'", sDate, eDate); } else { sDate = DateTime.Now.ToString("yyyy-MM-dd"); eDate = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"); strWhere += string.Format(" AND JoinTime BETWEEN '{0}' AND '{1}'", sDate, eDate); } #region Sql语句 string strSql = @" WITH MembersTotal AS ( --会员总数、币余额总数 SELECT COUNT(1) AS MemberTotal, SUM(Balance) AS BalanceTotal FROM t_member WHERE MerchID = {0} ), TodayJoinMembers AS ( --今日新增会员数 SELECT COUNT(1) AS TodayJoinMembers FROM t_member WHERE MerchID = {0} AND DATEDIFF(DAY, GETDATE(), JoinTime) = 0 ), SaveCoinMember AS ( --今日存币会员数 SELECT DISTINCT ICCardID FROM flw_485_savecoin WHERE MerchID = {0} AND DATEDIFF(DAY, GETDATE(), RealTime) = 0 ), SaleCoinMember AS ( --今日提币会员数 SELECT DISTINCT ICCardID FROM flw_coin_sale WHERE MerchID = {0} AND DATEDIFF(DAY, GETDATE(), RealTime) = 0 ), FoodSaleMember AS ( --今日购币会员数 SELECT DISTINCT ICCardID FROM flw_food_sale WHERE MerchID = {0} AND DATEDIFF(DAY, GETDATE(), RealTime) = 0 ), Members AS ( --今日全部游戏会员 SELECT ICCardID FROM FoodSaleMember UNION SELECT ICCardID FROM SaveCoinMember UNION SELECT ICCardID FROM SaleCoinMember ), TodayGameMembers AS ( --今日全部参与 SELECT COUNT(1) AS TodayGameMembers FROM Members ) SELECT ISNULL(a.MemberTotal,0) AS MemberTotal, ISNULL(a.BalanceTotal,0) AS BalanceTotal, ISNULL(b.TodayJoinMembers, 0) AS TodayJoinMembers, ISNULL(c.TodayGameMembers, 0) as TodayGameMembers FROM MembersTotal a LEFT JOIN TodayJoinMembers b ON 1=1 LEFT JOIN TodayGameMembers c ON 1=1 ; SELECT MemberID, ICCardID, ISNULL(MemberName, Mobile) AS MemberName, Mobile, Balance, CONVERT(varchar(100), JoinTime, 120) AS JoinTime FROM ( SELECT ROW_NUMBER() OVER (ORDER BY JoinTime DESC) rownumber , MemberID, ICCardID, MemberName, Mobile, Balance, JoinTime FROM t_member WHERE MerchID = {0} {1} ) AS a WHERE rownumber BETWEEN {2} AND {3} ;"; #endregion strSql = string.Format(strSql, merchId, strWhere, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize); DataSet ds = XCCloudRS232BLL.ExecuterSqlToDataSet(strSql); return(ds); }
/// <summary> /// 统计指定售币机营收 /// </summary> /// <returns></returns> public static DataSet GetSaleCoinMachineRevenue(int merchId, int deviceId, string sDate = null, string eDate = null, int pageIndex = 1, int pageSize = 10) { string strWhere = string.Empty, strSaleWhere = string.Empty; if (!string.IsNullOrWhiteSpace(sDate) && string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime >= '{0}'", sDate); strSaleWhere = string.Format(" AND RealTime >= '{0}'", sDate); } else if (string.IsNullOrWhiteSpace(sDate) && !string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime <= '{0}'", eDate); strSaleWhere = string.Format(" AND RealTime <= '{0}'", eDate); } else if (!string.IsNullOrWhiteSpace(sDate) && !string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime BETWEEN '{0}' AND '{1}'", sDate, eDate); strSaleWhere = string.Format(" AND RealTime BETWEEN '{0}' AND '{1}'", sDate, eDate); } else { sDate = DateTime.Now.ToString("yyyy-MM-dd"); eDate = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"); strWhere = string.Format(" AND RealTime BETWEEN '{0}' AND '{1}'", sDate, eDate); strSaleWhere = string.Format(" AND RealTime BETWEEN '{0}' AND '{1}'", sDate, eDate); } #region Sql语句 string strSql = @" WITH SaleCoinAmount AS ( --售币 SELECT f.DeviceID, SUM(CoinQuantity) AS SaleCoins, SUM(TotalMoney) AS SaleCoinAmount FROM flw_food_sale f INNER JOIN Base_DeviceInfo d ON d.ID = f.DeviceID WHERE FlowType = 1 AND PayState = 1 AND f.MerchID = {0} AND DeviceID = {1} {3} GROUP BY f.DeviceID ), PullCoins AS ( --提币 SELECT f.DeviceID, ISNULL(SUM(f.Coins), 0) AS PullCoins FROM flw_coin_sale f INNER JOIN Base_DeviceInfo d ON d.ID = f.DeviceID WHERE f.WorkType IN (3,6,7) AND f.MerchID = {0} AND f.DeviceID = {1} {2} GROUP BY f.DeviceID ) --提币 SELECT s.SaleCoins, s.SaleCoinAmount, p.PullCoins FROM Base_DeviceInfo d LEFT JOIN SaleCoinAmount s ON d.ID = s.DeviceID LEFT JOIN PullCoins p ON d.ID = p.DeviceID WHERE d.ID = {1} ; WITH PullCoinList AS ( SELECT f.ICCardID, m.MemberName, m.Mobile, '提币' AS FlowType, '0' AS Amount, f.Coins, f.Balance, f.RealTime FROM flw_coin_sale f INNER JOIN t_member m ON m.ICCardID = f.ICCardID WHERE f.MerchID = {0} AND f.DeviceID = {1} {2} UNION ALL SELECT f.ICCardID,m.MemberName, m.Mobile, '售币' AS FlowType, f.TotalMoney AS Amount, f.CoinQuantity, f.Balance, f.RealTime FROM flw_food_sale f INNER JOIN t_member m ON m.ICCardID = f.ICCardID WHERE f.MerchID = {0} AND f.DeviceID = {1} {2} ) SELECT ICCardID, MemberName, Mobile, FlowType, Amount, Coins, Balance, CONVERT(varchar(100), RealTime, 120) AS RealTime FROM ( SELECT ROW_NUMBER() OVER (ORDER BY RealTime DESC) rownumber , ICCardID, MemberName, Mobile, FlowType, Amount, Coins, Balance, RealTime FROM PullCoinList ) AS a WHERE rownumber BETWEEN {4} AND {5} ; "; #endregion strSql = string.Format(strSql, merchId, deviceId, strWhere, strSaleWhere, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize); DataSet ds = XCCloudRS232BLL.ExecuterSqlToDataSet(strSql); return(ds); }
/// <summary> /// 统计商户营收总览 /// </summary> /// <returns></returns> public static DataSet GetMerchRevenue(int merchId, Nullable <int> routerId = null, string sDate = null, string eDate = null) { string strRouterWhere = string.Empty, strParentWhere = string.Empty; if (routerId != null) { strRouterWhere += string.Format(" AND ID = {0}", routerId); strParentWhere += string.Format(" AND ParentID = {0}", routerId); } string strWhere = string.Empty; if (!string.IsNullOrWhiteSpace(sDate) && string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime >= '{0}'", sDate); } else if (string.IsNullOrWhiteSpace(sDate) && !string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime <= '{0}'", eDate); } else if (!string.IsNullOrWhiteSpace(sDate) && !string.IsNullOrWhiteSpace(eDate)) { strWhere = string.Format(" AND RealTime BETWEEN '{0}' AND '{1}'", sDate, eDate); } else { sDate = DateTime.Now.ToString("yyyy-MM-dd"); eDate = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"); strWhere = string.Format(" AND RealTime BETWEEN '{0}' AND '{1}'", sDate, eDate); } #region Sql语句 string strSql = @" WITH foodSale AS ( SELECT FoodID, MerchID, DeviceID, RealTime, SUM(CASE FlowType WHEN 0 THEN PayTotal END) AS RechargeAmount, SUM(CASE FlowType WHEN 1 THEN PayTotal END) AS SaleAmount, SUM(CASE PayType WHEN 0 THEN PayTotal END) AS CashPayAmount, SUM(CASE PayType WHEN 1 THEN PayTotal END) AS WechatPayAmount, SUM(CASE PayType WHEN 2 THEN PayTotal END) AS AliPayAmount, SUM(CASE PayType WHEN 3 THEN PayTotal END) AS UnionPayAmount FROM flw_food_sale WHERE MerchID = {0} {3} GROUP BY FoodID, MerchID, DeviceID, RealTime ), Foods AS ( SELECT FoodID FROM t_foods WHERE MerchID = {0} ), MerchInfo AS ( --商户 SELECT ID, MerchName, Token FROM Base_MerchInfo WHERE ID = {0} ) , Router AS ( --控制器 SELECT ID, MerchID FROM Base_DeviceInfo WHERE DeviceType = 0 {1} ), Devices AS ( --商户绑定的设备 SELECT ID FROM Base_DeviceInfo WHERE MerchID = {0} ) SELECT mi.MerchName, mi.Token, SUM(fs.RechargeAmount) AS RechargeAmount, SUM(fs.SaleAmount) AS SaleAmount, SUM(fs.CashPayAmount) AS CashPayAmount, SUM(fs.WechatPayAmount) AS WechatPayAmount, SUM(fs.AliPayAmount) AS AliPayAmount, SUM(fs.UnionPayAmount) AS UnionPayAmount, SUM(ISNULL(fs.RechargeAmount,0) + ISNULL(fs.SaleAmount,0)) AS Amount FROM foodSale fs INNER JOIN Foods f ON f.FoodID = fs.FoodID INNER JOIN MerchInfo mi ON mi.ID = fs.MerchID INNER JOIN Router r ON r.MerchID = mi.ID INNER JOIN Devices d ON d.ID = fs.DeviceID GROUP BY mi.MerchName, mi.Token ; --存币 SELECT ISNULL(SUM(Coins), 0) AS SaveCoins FROM flw_485_savecoin a INNER JOIN Data_MerchDevice b ON b.DeviceID = a.DeviceID WHERE MerchID = {0} {2} {3} ; --提币 SELECT ISNULL(SUM(fcs.Coins), 0) AS SaleCoins FROM flw_coin_sale fcs INNER JOIN Data_MerchDevice b ON b.DeviceID = fcs.DeviceID WHERE fcs.WorkType IN (3,6,7) AND MerchID = {0} {2} {3} "; #endregion strSql = string.Format(strSql, merchId, strRouterWhere, strParentWhere, strWhere); DataSet ds = XCCloudRS232BLL.ExecuterSqlToDataSet(strSql); return(ds); }