/// <summary> /// 获取支付报表数据 /// </summary> /// <param name="searchParas"></param> /// <returns></returns> public List <usp_Report_PayInformation> GetPaymentCount(U_PaymentHistory_Search searchParas) { using (var db = SugarDao_MsSql.GetInstance()) { var mainDep = Guid.Empty; if (CurrentUser.GetCurrentUser().LoginName != "sysAdmin") { Guid dep = Guid.Parse(CurrentUser.GetCurrentUser().Department); mainDep = dep; var listDep = db.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门 if (!string.IsNullOrEmpty(searchParas.Department)) { Guid searchDep = Guid.Parse(searchParas.Department); if (!listDep.Contains(searchDep)) { searchParas.Department = null; } } } else { mainDep = db.Queryable <Master_Organization>().Where(i => i.ParentVguid == null).Select(i => i.Vguid).SingleOrDefault(); } switch (searchParas.PaymentStatus) { case "支付成功": searchParas.PaymentStatus = "1"; break; case "支付失败": searchParas.PaymentStatus = "2"; break; case "待支付": searchParas.PaymentStatus = "3"; break; case "已退款": searchParas.PaymentStatus = "4"; break; default: searchParas.PaymentStatus = "1"; break; } var list = db.SqlQuery <usp_Report_PayInformation>("exec usp_Report_PayInformation @name,@phoneNumber,@OwnedFleet,@starDate,@endDate,@Status", new { name = searchParas.Name ?? "", phoneNumber = searchParas.PhoneNumber ?? "", OwnedFleet = string.IsNullOrEmpty(searchParas.Department) ? mainDep.ToString() : searchParas.Department, starDate = searchParas.PayDateFrom == null ? "1900-01-01 00:00:00" : string.Format("{0:yyyy-MM-dd HH:mm:ss}", searchParas.PayDateFrom), endDate = searchParas.PayDateTo == null ? "9999-12-31 23:59:59" : string.Format("{0:yyyy-MM-dd HH:mm:ss}", searchParas.PayDateTo), Status = searchParas.PaymentStatus }); return(list); } }
/// <summary> /// 获取所有的支付历史 /// </summary> /// <param name="searchParas"></param> /// <param name="para"></param> /// <returns></returns> public JsonResult GetAllPaymentHistoryInfo(U_PaymentHistory_Search searchParas, GridParams para) { if (para.sortdatafield == null) { para.sortdatafield = "PayDate"; para.sortorder = "desc"; } para.pagenum = para.pagenum + 1; //页0,+1 var list = _weChatRevenueLogic.GetAllPaymentHistoryInfo(searchParas, para); return(Json(list, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 获取月度统计报表 /// </summary> /// <param name="searchParas"></param> /// <returns></returns> public List <usp_Report_MonthPayInformation> GetMonthlyPayment(U_PaymentHistory_Search searchParas) { return(_paymentReportServer.GetMonthlyPayment(searchParas)); }
/// <summary> /// 获取所有的支付历史 /// </summary> /// <param name="searchParas"></param> /// <param name="para"></param> /// <returns></returns> public JsonResultModel <v_PaymentHistory_Information> GetAllPaymentHistoryInfo(U_PaymentHistory_Search searchParas, GridParams para) { return(_weChatRevenueServer.GetAllPaymentHistoryInfo(searchParas, para)); }
/// <summary> /// 导出 /// </summary> /// <param name="searchParas">导出条件</param> public void Export(U_PaymentHistory_Search searchParas) { using (var db = SugarDao_MsSql.GetInstance()) { var query = db.Queryable <v_PaymentHistory_Information>(); if (CurrentUser.GetCurrentUser().LoginName != "sysAdmin") { Guid dep = Guid.Parse(CurrentUser.GetCurrentUser().Department); var listDep = db.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门 if (listDep.Count > 0) { query.In(i => i.OwnedFleet, listDep); } } if (!string.IsNullOrEmpty(searchParas.Name)) { query.Where(i => i.Name.Contains(searchParas.Name)); } if (!string.IsNullOrEmpty(searchParas.PhoneNumber)) { query.Where(i => i.PhoneNumber.Contains(searchParas.PhoneNumber)); } if (!string.IsNullOrEmpty(searchParas.IDNumber)) { query.Where(i => i.IDNumber.Contains(searchParas.IDNumber)); } if (!string.IsNullOrEmpty(searchParas.JobNumber)) { query.Where(i => i.JobNumber.Contains(searchParas.JobNumber)); } if (!string.IsNullOrEmpty(searchParas.PaymentStatus)) { query.Where(i => i.PaymentStatus == searchParas.PaymentStatus); } if (!string.IsNullOrEmpty(searchParas.TransactionID)) { query.Where(i => i.TransactionID.Contains(searchParas.TransactionID)); } if (!string.IsNullOrEmpty(searchParas.Department)) { Guid department = Guid.Parse(searchParas.Department); var listDep = db.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + department + "')"); //找到该部门以及其所有子部门 if (listDep.Count > 0) { query.In(i => i.OwnedFleet, listDep); } } if (searchParas.PayDateFrom != null) { query.Where(i => i.PayDate >= searchParas.PayDateFrom); } if (searchParas.PayDateTo != null) { query.Where(i => i.PayDate <= searchParas.PayDateTo); } query.OrderBy(i => i.PayDate, OrderByType.Desc); var dt = query.ToDataTable(); dt.TableName = "PaymentHistoryInfo"; ExportExcel.ExportExcels("PaymentHistory.xlsx", "PaymentHistory" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", dt); _logLogic.SaveLog(13, 47, CurrentUser.GetCurrentUser().LoginName, "PaymentHistory", Common.Tools.DataTableHelper.Dtb2Json(dt)); } }
/// <summary> /// 获取所有的支付历史 /// </summary> /// <param name="searchParas"></param> /// <param name="para"></param> /// <returns></returns> public JsonResultModel <v_PaymentHistory_Information> GetAllPaymentHistoryInfo(U_PaymentHistory_Search searchParas, GridParams para) { var jsonResult = new JsonResultModel <v_PaymentHistory_Information>(); using (var db = SugarDao_MsSql.GetInstance()) { var query = db.Queryable <v_PaymentHistory_Information>(); if (CurrentUser.GetCurrentUser().LoginName != "sysAdmin") { Guid dep = Guid.Parse(CurrentUser.GetCurrentUser().Department); var listDep = db.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门 if (listDep.Count > 0) { query.In(i => i.OwnedFleet, listDep); } } if (!string.IsNullOrEmpty(searchParas.Name)) { query.Where(i => i.Name.Contains(searchParas.Name)); } if (!string.IsNullOrEmpty(searchParas.PhoneNumber)) { query.Where(i => i.PhoneNumber.Contains(searchParas.PhoneNumber)); } if (!string.IsNullOrEmpty(searchParas.IDNumber)) { query.Where(i => i.IDNumber.Contains(searchParas.IDNumber)); } if (!string.IsNullOrEmpty(searchParas.JobNumber)) { query.Where(i => i.JobNumber.Contains(searchParas.JobNumber)); } if (!string.IsNullOrEmpty(searchParas.PaymentStatus)) { query.Where(i => i.PaymentStatus == searchParas.PaymentStatus); } if (!string.IsNullOrEmpty(searchParas.TransactionID)) { query.Where(i => i.TransactionID.Contains(searchParas.TransactionID)); } if (!string.IsNullOrEmpty(searchParas.Department)) { Guid department = Guid.Parse(searchParas.Department); var listDep = db.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + department + "')"); //找到该部门以及其所有子部门 if (listDep.Count > 0) { query.In(i => i.OwnedFleet, listDep); } } if (searchParas.PayDateFrom != null) { query.Where(i => i.PayDate >= searchParas.PayDateFrom); } if (searchParas.PayDateTo != null) { query.Where(i => i.PayDate <= searchParas.PayDateTo); } query.OrderBy(para.sortdatafield + " " + para.sortorder); var pageCount = 0; jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize, ref pageCount); jsonResult.TotalRows = pageCount; //存入操作日志表 string logData = JsonHelper.ModelToJson(jsonResult); _logLogic.SaveLog(3, 47, CurrentUser.GetCurrentUser().LoginName, "支付历史", logData); } return(jsonResult); }
/// <summary> /// 导出报表 /// </summary> /// <param name="searchParas">搜索条件</param> public void Export(U_PaymentHistory_Search searchParas) { using (var db = SugarDao_MsSql.GetInstance()) { #region 获取支付历史数据 var query = db.Queryable <v_PaymentHistory_Information>(); if (CurrentUser.GetCurrentUser().LoginName != "sysAdmin") { Guid dep = Guid.Parse(CurrentUser.GetCurrentUser().Department); var listDep = db.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门 query.In(i => i.OwnedFleet, listDep); } if (!string.IsNullOrEmpty(searchParas.Name)) { query.Where(i => i.Name.Contains(searchParas.Name)); } if (!string.IsNullOrEmpty(searchParas.PhoneNumber)) { query.Where(i => i.PhoneNumber.Contains(searchParas.PhoneNumber)); } if (!string.IsNullOrEmpty(searchParas.IDNumber)) { query.Where(i => i.IDNumber.Contains(searchParas.IDNumber)); } if (!string.IsNullOrEmpty(searchParas.JobNumber)) { query.Where(i => i.JobNumber.Contains(searchParas.JobNumber)); } if (!string.IsNullOrEmpty(searchParas.TransactionID)) { query.Where(i => i.TransactionID.Contains(searchParas.TransactionID)); } if (!string.IsNullOrEmpty(searchParas.Department)) { Guid department = Guid.Parse(searchParas.Department); var listDep = db.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + department + "')"); //找到该部门以及其所有子部门 query.In(i => i.OwnedFleet, listDep); } if (searchParas.PayDateFrom != null) { query.Where(i => i.PayDate >= searchParas.PayDateFrom); } if (searchParas.PayDateTo != null) { query.Where(i => i.PayDate <= searchParas.PayDateTo); } query.OrderBy(i => i.PayDate, OrderByType.Desc); var dtPaymentHistory = query.ToDataTable(); dtPaymentHistory.TableName = "PaymentHistoryInfo"; #endregion #region 获取支付总和 var mainDep = Guid.Empty; if (CurrentUser.GetCurrentUser().LoginName != "sysAdmin") { Guid dep = Guid.Parse(CurrentUser.GetCurrentUser().Department); mainDep = dep; var listDep = db.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门 if (!string.IsNullOrEmpty(searchParas.Department)) { Guid searchDep = Guid.Parse(searchParas.Department); if (!listDep.Contains(searchDep)) { searchParas.Department = null; } } } else { mainDep = db.Queryable <Master_Organization>().Where(i => i.ParentVguid == null).Select(i => i.Vguid).SingleOrDefault(); } switch (searchParas.PaymentStatus) { case "支付成功": searchParas.PaymentStatus = "1"; break; case "支付失败": searchParas.PaymentStatus = "2"; break; case "待支付": searchParas.PaymentStatus = "3"; break; case "已退款": searchParas.PaymentStatus = "4"; break; default: searchParas.PaymentStatus = "1"; break; } var dtPaymentCount = db.GetDataTable("exec usp_Report_PayInformation @name,@phoneNumber,@OwnedFleet,@starDate,@endDate,@Status", new { name = searchParas.Name ?? "", phoneNumber = searchParas.PhoneNumber ?? "", OwnedFleet = string.IsNullOrEmpty(searchParas.Department) ? mainDep.ToString() : searchParas.Department, starDate = searchParas.PayDateFrom == null ? "1900-01-01 00:00:00" : string.Format("{0:yyyy-MM-dd HH:mm:ss}", searchParas.PayDateFrom), endDate = searchParas.PayDateTo == null ? "9999-12-31 23:59:59" : string.Format("{0:yyyy-MM-dd HH:mm:ss}", searchParas.PayDateTo), Status = searchParas.PaymentStatus }); dtPaymentCount.TableName = "PaymentCount"; #endregion var ds = new DataSet(); ds.Tables.Add(dtPaymentHistory); ds.Tables.Add(dtPaymentCount); ExportExcel.ExportExcels("PaymentReport.xlsx", "PaymentReport" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", ds); _logLogic.SaveLog(13, 48, CurrentUser.GetCurrentUser().LoginName, "支付报表", Common.Tools.DataTableHelper.Dtb2Json(dtPaymentHistory)); } }
/// <summary> /// 获取月度统计报表 /// </summary> /// <param name="searchParas"></param> /// <returns></returns> public JsonResult GetMonthlyPayment(U_PaymentHistory_Search searchParas) { var list = _paymentReportLogic.GetMonthlyPayment(searchParas); return(Json(list, JsonRequestBehavior.AllowGet)); }