/* * 商户交易统计 */ using System; using System.Collections.Generic; using System.Linq; using System.Data; using MySystem.Models.Main; using Library; using LitJson; namespace MySystem.Service.Main { public class MerchantTradeSummaryService { static string _conn = ConfigurationManager.AppSettings["SqlConnStr"].ToString(); /// /// 查询列表 /// /// 关联表 /// 查询条件(sql语句) /// 总数(输出) /// 页码 /// 每页条数 /// public static List> List(List relationData, string condition, string TradeTime, out int count, int page = 1, int limit = 30, string orderBy = "Sort desc,Id desc") { List fields = new List(); //要显示的列 fields.Add("Id"); fields.Add("Sort"); fields.Add("QueryCount"); fields.Add("Status"); fields.Add("CreateDate"); fields.Add("UpdateDate"); fields.Add("SeoTitle"); fields.Add("SeoKeyword"); fields.Add("SeoDescription"); fields.Add("OderCount"); // 订单数 fields.Add("PayMode"); //支付方式 fields.Add("TradeAmount"); //订单金额 fields.Add("InFactAmount"); //实收金额 fields.Add("WeChatInfactAmount"); //微信实收金额 fields.Add("AliPayInFactAmount"); //支付宝实收金额 fields.Add("ActAmount"); //活动总金额 fields.Add("NonActAmount"); //非活动总金额 fields.Add("TradeDate"); //日期 fields.Add("TradeMonth"); //月份 fields.Add("MerchantId"); //商户Id Dictionary obj = new DbService(AppConfig.Base.mainTables, _conn).IndexData("MerchantTradeSummary" + TradeTime, relationData, orderBy, page, limit, condition, fields); List> diclist = obj["data"] as List>; count = int.Parse(obj["count"].ToString()); return diclist; } public static List> List(List relationData, string condition, string TradeTime, int page = 1, int limit = 30, string orderBy = "Sort desc,Id desc") { List fields = new List(); //要显示的列 fields.Add("Id"); fields.Add("Sort"); fields.Add("QueryCount"); fields.Add("Status"); fields.Add("CreateDate"); fields.Add("UpdateDate"); fields.Add("SeoTitle"); fields.Add("SeoKeyword"); fields.Add("SeoDescription"); fields.Add("OderCount"); // 订单数 fields.Add("PayMode"); //支付方式 fields.Add("TradeAmount"); //订单金额 fields.Add("InFactAmount"); //实收金额 fields.Add("WeChatInfactAmount"); //微信实收金额 fields.Add("AliPayInFactAmount"); //支付宝实收金额 fields.Add("ActAmount"); //活动总金额 fields.Add("NonActAmount"); //非活动总金额 fields.Add("TradeDate"); //日期 fields.Add("TradeMonth"); //月份 fields.Add("MerchantId"); //商户Id Dictionary obj = new DbService(AppConfig.Base.mainTables, _conn).IndexData("MerchantTradeSummary" + TradeTime, relationData, orderBy, page, limit, condition, fields); List> diclist = obj["data"] as List>; return diclist; } public static List> List2(List relationData, string condition, string TradeTime, int page = 1, int limit = 30, string orderBy = "Sort desc,Id desc") { List fields = new List(); //要显示的列 fields.Add("Id"); fields.Add("Sort"); fields.Add("QueryCount"); fields.Add("Status"); fields.Add("CreateDate"); fields.Add("UpdateDate"); fields.Add("SeoTitle"); fields.Add("SeoKeyword"); fields.Add("SeoDescription"); fields.Add("OderCount"); // 订单数 fields.Add("TradeAmount"); //订单金额 fields.Add("InFactAmount"); //实收金额 fields.Add("WeChatInfactAmount"); //微信实收金额 fields.Add("AliPayInFactAmount"); //支付宝实收金额 fields.Add("ActAmount"); //活动总金额 fields.Add("NonActAmount"); //非活动总金额 fields.Add("TradeDate"); //日期 fields.Add("TradeMonth"); //月份 fields.Add("MerchantId"); //商户Id Dictionary obj = new DbService(AppConfig.Base.statTables, AppConfig.Base.StatConnRds).IndexData("MerchantTradeSummaryList" + TradeTime, relationData, orderBy, page, limit, condition, fields); List> diclist = obj["data"] as List>; return diclist; } public bool CheckTableExist(string date) { DataTable dt = CustomerSqlConn.dtable("show Tables like 'MerchantTradeSummaryList" + date + "'", AppConfig.Base.StatConnRds); if(dt.Rows.Count > 0) { return true; } return false; } /// /// 查询一条记录 /// /// 主键Id /// public static MerchantTradeSummary Query(int Id, string TradeTime) { WebCMSEntities db = new WebCMSEntities(); MerchantTradeSummary editData = db.MerchantTradeSummary.FirstOrDefault(m => m.Id == Id) ?? new MerchantTradeSummary(); db.Dispose(); return editData; } public static MerchantTradeSummary Query(string condition, string TradeTime, string fields = "*") { var merchantTradeSummary = new DbService(AppConfig.Base.mainTables, _conn).Query(fields, "MerchantTradeSummary" + TradeTime, condition); if (merchantTradeSummary.Count > 0) { return Newtonsoft.Json.JsonConvert.DeserializeObject(Newtonsoft.Json.JsonConvert.SerializeObject(merchantTradeSummary)); } return new MerchantTradeSummary(); } public static decimal Sum(string condition, string TradeTime, string field) { var dt = new DbService(AppConfig.Base.mainTables, _conn).Query("Sum(" + field + ") " + field, "MerchantTradeSummary" + TradeTime, condition); decimal amount = 0; if (dt.Count > 0) { amount = decimal.Parse(dt[field].ToString()); } return amount; } /// /// 查询记录数 /// /// 主键Id /// public static int Count(string TradeTime, string condition = "", string field = "Id") { var dt = new DbService(AppConfig.Base.mainTables, _conn).Query("Count(" + field + ") " + field, "MerchantTradeSummary" + TradeTime, condition); int result = 0; if (dt.Count > 0) { result = int.Parse(dt[field].ToString()); } return result; } /// /// 查询是否存在 /// /// 主键Id /// public static bool Exist(int Id, string TradeTime) { WebCMSEntities db = new WebCMSEntities(); bool check = db.MerchantTradeSummary.Any(m => m.Id == Id); db.Dispose(); return check; } /// /// 添加数据 /// /// 要设置的字段 /// public static AppResultJson Add(Dictionary fields, bool check = true) { if (check) { } int Id = new DbService(AppConfig.Base.mainTables, _conn).Add("MerchantTradeSummary", fields, 0); return new AppResultJson() { Status = "1", Data = Id }; } /// /// 修改数据 /// /// 要设置的字段 /// 主键Id public static AppResultJson Edit(Dictionary fields, int Id, bool check = true) { if (check) { if (string.IsNullOrEmpty(fields["NickName"].ToString())) { return new AppResultJson() { Status = "-1", Info = "请填写昵称" }; } } new DbService(AppConfig.Base.mainTables, _conn).Edit("MerchantTradeSummary", fields, Id); return new AppResultJson() { Status = "1", Data = Id }; } /// /// 逻辑删除 /// /// 主键Id public static void Remove(int Id) { Dictionary fields = new Dictionary(); fields.Add("Status", -1); new DbService(AppConfig.Base.mainTables, _conn).Edit("MerchantTradeSummary", fields, Id); } /// /// 删除数据 /// /// 主键Id public static void Delete(int Id) { new DbService(AppConfig.Base.mainTables, _conn).Delete("MerchantTradeSummary", Id); } /// /// 排序 /// /// 主键Id /// 排序序号 public static void Sort(int Id, int Sort) { new DbService(AppConfig.Base.mainTables, _conn).Sort("MerchantTradeSummary", Sort, Id); } /// /// 导入数据 /// /// json数据 public static void Import(string ExcelData) { WebCMSEntities db = new WebCMSEntities(); JsonData list = JsonMapper.ToObject(ExcelData); for (int i = 1; i < list.Count; i++) { JsonData dr = list[i]; db.MerchantTradeSummary.Add(new MerchantTradeSummary() { CreateDate = DateTime.Now, UpdateDate = DateTime.Now, }); db.SaveChanges(); } db.Dispose(); } /// /// 导出excel表格 /// /// 查询条件(单个字段) /// 查询条件(sql语句) /// // public static void ExportExcel(List relationData, string condition) // { // } } }