mp-ap/Util/UserTradeDbconn.cs

using System;
using System.Collections.Generic;
using Library;
using System.Data;

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

        /// <summary>
        /// 获取交易统计数据
        /// </summary>
        /// <param name="MerchantId">商户Id</param>
        /// <param name="TradeDate">日期/月份（yyyyMMdd或yyyyMM）</param>
        /// <param name="fields">字段（多个用逗号隔开）</param>
        /// <returns></returns>
        public Dictionary<string, object> GetTrade(int MerchantId, string TradeDate, string fields)
        {
            Dictionary<string, object> AmtList = new Dictionary<string, object>();
            string fieldList = "";
            foreach(string field in fields.Split(','))
            {
                fieldList += "sum(" + fieldList + ") " + fieldList + ",";
            }
            DataTable dt = CustomerSqlConn.dtable("select " + fieldList.TrimEnd(',') + " from MerchantTradeSummary" + TradeDate + " where MerchantId=" + MerchantId + " and ", AppConfig.Base.StatConnRds);
            if(dt.Rows.Count > 0)
            {
                foreach(string field in fields.Split(','))
                {
                    AmtList.Add(field, dt.Rows[0][0].ToString());
                }
            }
            return AmtList;
        }

        /// <summary>
        /// 获取交易统计列表
        /// </summary>
        /// <param name="MerchantId">商户Id</param>
        /// <param name="TradeMonth">月份（yyyyMM）</param>
        /// <param name="fields">字段（多个用逗号隔开）</param>
        /// <returns></returns>
        public List<Dictionary<string, object>> GetDateTradeList(int MerchantId, string TradeMonth, string fields)
        {
            List<Dictionary<string, object>> AmtList = new List<Dictionary<string, object>>();
            DataTable dt = CustomerSqlConn.dtable("select " + fields + " from MerchantTradeSummary" + TradeMonth + " where MerchantId=" + MerchantId + " and Trade", AppConfig.Base.StatConnRds);
            if(dt.Rows.Count > 0)
            {
                Dictionary<string, object> Item = new Dictionary<string, object>();
                foreach(string field in fields.Split(','))
                {
                    Item.Add(field, dt.Rows[0][0].ToString());
                }
                AmtList.Add(Item);
            }
            return AmtList;
        }

        /// <summary>
        /// 获取交易统计列表
        /// </summary>
        /// <param name="MerchantId">商户Id</param>
        /// <param name="TradeMonth">月份（yyyyMM）</param>
        /// <param name="fields">字段（多个用逗号隔开）</param>
        /// <returns></returns>
        public List<Dictionary<string, object>> GetMonthTradeList(int MerchantId, string fields)
        {
            List<Dictionary<string, object>> AmtList = new List<Dictionary<string, object>>();
            string fieldList = "";
            foreach(string field in fields.Split(','))
            {
                fieldList += "sum(" + field + ") " + field + ",";
            }
            for(int i = 0; i < 6; i ++)
            {
                Dictionary<string, object> Item = new Dictionary<string, object>();
                Item.Add("TradeDate", DateTime.Now.AddMonths(-i).ToString("yyyy-MM"));
                DataTable dt = CustomerSqlConn.dtable("select " + fieldList.TrimEnd(',') + " from MerchantTradeSummary" + DateTime.Now.AddMonths(-i).ToString("yyyyMM") + " where MerchantId=" + MerchantId + "", AppConfig.Base.StatConnRds);
                if(dt.Rows.Count > 0)
                {
                    foreach(string field in fields.Split(','))
                    {
                        Item.Add(field, function.CheckNum(dt.Rows[0][0].ToString()));
                    }
                }
                else
                {
                    foreach(string field in fields.Split(','))
                    {
                        Item.Add(field, "0");
                    }
                }
                AmtList.Add(Item);
            }
            return AmtList;
        }
    }
}