mp_consumerap/AppStart/Redis/Mysql/UserTradeDaySummaryVsDbconn.cs

using System;
using System.Collections.Generic;
using Library;
using System.Linq;
using System.Data;
using MySystem.MainModels;

namespace MySystem
{
    public class UserTradeDaySummaryVsDbconn
    {
        public readonly static UserTradeDaySummaryVsDbconn Instance = new UserTradeDaySummaryVsDbconn();
        public UserTradeDaySummaryVsDbconn()
        { }

        //数据库链接
        public string StatConn = Library.ConfigurationManager.AppSettings["TSqlConnStr"].ToString();

        // TODO:读取很慢，感觉redis没生效

        #region 获取单个字段
        //个人业绩
        public decimal GetTrade(int UserId, string kind = "self")
        {
            DataTable dt = CustomerSqlConn.dtable("", StatConn);
            decimal amt = 0;
            WebCMSEntities db = new WebCMSEntities();
            bool check = db.TradeDaySummary.Any(m => m.UserId == UserId && m.SeoTitle == kind);
            if (check)
            {
                if (kind == "self")
                {
                    amt = db.TradeDaySummary.Where(m => m.UserId == UserId && m.SeoTitle == kind).Sum(m => m.HelpDirectTradeAmt + m.HelpDirectDebitTradeAmt + m.NotHelpDirectTradeAmt + m.NotHelpDirectDebitTradeAmt + m.ProfitDirectTradeAmt + m.ProfitDirectDebitTradeAmt);
                }
                else if (kind == "team")
                {
                    amt = db.TradeDaySummary.Where(m => m.UserId == UserId && m.SeoTitle == kind).Sum(m => m.HelpNonDirectTradeAmt + m.HelpNonDirectDebitTradeAmt + m.NotHelpNonDirectTradeAmt + m.NotHelpNonDirectDebitTradeAmt + m.ProfitNonDirectTradeAmt + m.ProfitNonDirectDebitTradeAmt);
                }
            }
            // RedisDbconn.Instance.Set(key, amt);
            db.Dispose();
            return amt;
        }
        public decimal GetDateTrade(int UserId, string TradeDate, string kind = "self")
        {
            DataTable dt = new DataTable();
            decimal amt = 0;
            if (kind == "self")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(TradeAmount) TradeAmount FROM TradeStat" + TradeDate + " WHERE UserId=" + UserId + " AND DirectFlag=1", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }
            else if (kind == "team")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(TradeAmount) TradeAmount FROM TradeStat" + TradeDate + " WHERE UserId=" + UserId + " AND DirectFlag=0", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }
            // RedisDbconn.Instance.Set(key, amt);
            return amt;
        }
        public decimal GetMonthTrade(int UserId, string TradeMonth, string kind = "self")
        {
            DataTable dt = new DataTable();
            decimal amt = 0;
            if (kind == "self")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(TradeAmount) TradeAmount FROM TradeStat" + TradeMonth + " WHERE UserId=" + UserId + " AND DirectFlag=1", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }
            else if (kind == "team")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(TradeAmount) TradeAmount FROM TradeStat" + TradeMonth + " WHERE UserId=" + UserId + " AND DirectFlag=0", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }

            // RedisDbconn.Instance.Set(key, amt);
            return amt;
        }
        public decimal GetDateTradeForBrand(int UserId, string TradeDate, int BrandId, string kind = "self")
        {
            DataTable dt = new DataTable();
            decimal amt = 0;
            if (kind == "self")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(TradeAmount) TradeAmount FROM TradeStat" + TradeDate + " WHERE UserId=" + UserId + " AND DirectFlag=1 AND BrandId=" + BrandId + "", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }
            else if (kind == "team")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(TradeAmount) TradeAmount FROM TradeStat" + TradeDate + " WHERE UserId=" + UserId + " AND DirectFlag=0 AND BrandId=" + BrandId + "", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }
            // RedisDbconn.Instance.Set(key, amt);
            return amt;
        }
        public decimal GetMonthTradeForBrand(int UserId, string TradeMonth, int BrandId, string kind = "self")
        {
            DataTable dt = new DataTable();
            decimal amt = 0;
            if (kind == "self")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(TradeAmount) TradeAmount FROM TradeStat" + TradeMonth + " WHERE UserId=" + UserId + " AND DirectFlag=1 AND BrandId=" + BrandId + "", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }
            else if (kind == "team")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(TradeAmount) TradeAmount FROM TradeStat" + TradeMonth + " WHERE UserId=" + UserId + " AND DirectFlag=0 AND BrandId=" + BrandId + "", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }
            // RedisDbconn.Instance.Set(key, amt);
            return amt;
        }
        public decimal GetDateTradeForCloud(int UserId, string TradeDate, int QrPayFlag, string kind = "self")
        {
            DataTable dt = new DataTable();
            decimal amt = 0;
            if (kind == "self")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(QrTradeAmount) TradeAmount FROM TradeStat" + TradeDate + " WHERE UserId=" + UserId + " AND DirectFlag=1", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }
            else if (kind == "team")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(QrTradeAmount) TradeAmount FROM TradeStat" + TradeDate + " WHERE UserId=" + UserId + " AND DirectFlag=0", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }
            // RedisDbconn.Instance.Set(key, amt);
            return amt;
        }
        public decimal GetMonthTradeForCloud(int UserId, string TradeMonth, int QrPayFlag, string kind = "self")
        {
            DataTable dt = new DataTable();
            decimal amt = 0;
            if (kind == "self")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(QrTradeAmount) TradeAmount FROM TradeStat" + TradeMonth + " WHERE UserId=" + UserId + " AND DirectFlag=1", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }
            else if (kind == "team")
            {
                dt = CustomerSqlConn.dtable("SELECT SUM(QrTradeAmount) TradeAmount FROM TradeStat" + TradeMonth + " WHERE UserId=" + UserId + " AND DirectFlag=0", StatConn);
                foreach (DataRow item in dt.Rows)
                {
                    amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                }
            }
            // RedisDbconn.Instance.Set(key, amt);
            return amt;
        }


        public decimal GetDateTradeForClouds(int UserId, string TradeDate, int QrPayFlag, int BankCardType, string kind = "self")
        {
            DataTable dt = new DataTable();
            decimal amt = 0;
            WebCMSEntities db = new WebCMSEntities();
            bool check = db.TradeDaySummary.Any(m => m.UserId == UserId && m.QueryCount == QrPayFlag && m.TradeDate == TradeDate && m.
            SeoTitle == kind);
            if (check)
            {
                if (kind == "self")
                {
                    if (BankCardType == 0)
                    {
                        amt = db.TradeDaySummary.Where(m => m.UserId == UserId && m.QueryCount == QrPayFlag && m.TradeDate == TradeDate && m.SeoTitle == kind).Sum(m => m.HelpDirectDebitTradeAmt + m.NotHelpDirectDebitTradeAmt + m.ProfitDirectDebitTradeAmt);
                        dt = CustomerSqlConn.dtable("SELECT SUM(DebitTradeAmount) TradeAmount FROM TradeStat" + TradeDate + " WHERE UserId=" + UserId + " AND DirectFlag=1", StatConn);
                        foreach (DataRow item in dt.Rows)
                        {
                            amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                        }
                    }
                    else
                    {
                        amt = db.TradeDaySummary.Where(m => m.UserId == UserId && m.QueryCount == QrPayFlag && m.TradeDate == TradeDate && m.SeoTitle == kind).Sum(m => m.HelpDirectTradeAmt + m.NotHelpDirectTradeAmt + m.ProfitDirectTradeAmt);
                        dt = CustomerSqlConn.dtable("SELECT SUM(CreditTradeAmount) TradeAmount FROM TradeStat" + TradeDate + " WHERE UserId=" + UserId + " AND DirectFlag=1", StatConn);
                        foreach (DataRow item in dt.Rows)
                        {
                            amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                        }
                    }
                }
                else if (kind == "team")
                {
                    if (BankCardType == 0)
                    {
                        amt = db.TradeDaySummary.Where(m => m.UserId == UserId && m.QueryCount == QrPayFlag && m.TradeDate == TradeDate && m.SeoTitle == kind).Sum(m => m.HelpNonDirectDebitTradeAmt + m.NotHelpNonDirectDebitTradeAmt + m.ProfitNonDirectDebitTradeAmt);
                        dt = CustomerSqlConn.dtable("SELECT SUM(DebitTradeAmount) TradeAmount FROM TradeStat" + TradeDate + " WHERE UserId=" + UserId + " AND DirectFlag=0", StatConn);
                        foreach (DataRow item in dt.Rows)
                        {
                            amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                        }
                    }
                    else
                    {
                        amt = db.TradeDaySummary.Where(m => m.UserId == UserId && m.QueryCount == QrPayFlag && m.TradeDate == TradeDate && m.SeoTitle == kind).Sum(m => m.HelpNonDirectTradeAmt + m.NotHelpNonDirectTradeAmt + m.ProfitNonDirectTradeAmt);
                        dt = CustomerSqlConn.dtable("SELECT SUM(CreditTradeAmount) TradeAmount FROM TradeStat" + TradeDate + " WHERE UserId=" + UserId + " AND DirectFlag=0", StatConn);
                        foreach (DataRow item in dt.Rows)
                        {
                            amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                        }
                    }
                }
            }
            // RedisDbconn.Instance.Set(key, amt);
            db.Dispose();
            return amt;
        }


        public decimal GetMonthTradeForClouds(int UserId, string TradeMonth, int QrPayFlag, int BankCardType, string kind = "self")
        {
            DataTable dt = new DataTable();
            decimal amt = 0;
            if (kind == "self")
            {
                if (BankCardType == 0)
                {
                    dt = CustomerSqlConn.dtable("SELECT SUM(DebitTradeAmount) TradeAmount FROM TradeStat" + TradeMonth + " WHERE UserId=" + UserId + " AND DirectFlag=1", StatConn);
                    foreach (DataRow item in dt.Rows)
                    {
                        amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                    }
                }
                else
                {
                    dt = CustomerSqlConn.dtable("SELECT SUM(CreditTradeAmount) TradeAmount FROM TradeStat" + TradeMonth + " WHERE UserId=" + UserId + " AND DirectFlag=1", StatConn);
                    foreach (DataRow item in dt.Rows)
                    {
                        amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                    }
                }
            }
            else if (kind == "team")
            {
                if (BankCardType == 0)
                {
                    dt = CustomerSqlConn.dtable("SELECT SUM(DebitTradeAmount) TradeAmount FROM TradeStat" + TradeMonth + " WHERE UserId=" + UserId + " AND DirectFlag=0", StatConn);
                    foreach (DataRow item in dt.Rows)
                    {
                        amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                    }
                }
                else
                {
                    dt = CustomerSqlConn.dtable("SELECT SUM(CreditTradeAmount) TradeAmount FROM TradeStat" + TradeMonth + " WHERE UserId=" + UserId + " AND DirectFlag=0", StatConn);
                    foreach (DataRow item in dt.Rows)
                    {
                        amt = decimal.Parse(function.CheckNum(item["TradeAmount"].ToString()));
                    }
                }
            }
            // RedisDbconn.Instance.Set(key, amt);
            return amt;
        }
        #endregion

    }
}