/// <summary> /// 获取发票管理列表合计信息 /// </summary> /// <param name="companyId">公司编号</param> /// <param name="searchInfo">查询实体</param> /// <param name="jiaoYiJinE">交易金额合计</param> /// <param name="kaiPiaoJinE">开票金额合计</param> public void GetFaPiaoGuanLisHeJi(int companyId, EyouSoft.Model.FinanceStructure.MFaPiaoGuanLiSearchInfo searchInfo, out decimal jiaoYiJinE, out decimal kaiPiaoJinE) { jiaoYiJinE = 0; kaiPiaoJinE = 0; if (companyId < 1) { return; } dal.GetFaPiaoGuanLisHeJi(companyId, searchInfo, out jiaoYiJinE, out kaiPiaoJinE); }
/// <summary> /// 初始化发票管理列表 /// </summary> void InitLB() { int pageSize = 20; int pageIndex = Utils.GetInt(Utils.GetQueryStringValue("page"), 1); int recordCount = 0; var searchInfo = new EyouSoft.Model.FinanceStructure.MFaPiaoGuanLiSearchInfo(); searchInfo.CrmName = Utils.GetQueryStringValue("kehumingcheng"); searchInfo.CTETime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("ctetime")); searchInfo.CTSTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("ctstime")); searchInfo.KPETime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("kpetime")); searchInfo.KPRen = Utils.GetQueryStringValue("kaipiaoren"); searchInfo.KPSTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("kpstime")); var bll = new EyouSoft.BLL.FinanceStructure.BFaPiao(); var items = bll.GetFaPiaoGuanLis(CurrentUserCompanyID, pageSize, pageIndex, ref recordCount, searchInfo); if (items != null && items.Count > 0) { phLB.Visible = true; phEmpty.Visible = false; rptFaPiaoGuanLi.DataSource = items; rptFaPiaoGuanLi.DataBind(); paging.PageLinkURL = Request.ServerVariables["SCRIPT_NAME"].ToString() + "?"; paging.UrlParams = Request.QueryString; paging.intPageSize = pageSize; paging.CurrencyPage = pageIndex; paging.intRecordCount = recordCount; decimal jiaoYiJinE; decimal kaiPiaoJinE; bll.GetFaPiaoGuanLisHeJi(CurrentUserCompanyID, searchInfo, out jiaoYiJinE, out kaiPiaoJinE); ltrJiaoYiJinEHeJi.Text = jiaoYiJinE.ToString("C2"); ltrKaiPiaoJinEHeJi.Text = kaiPiaoJinE.ToString("C2"); ltrWeiKaiPiaoJinEHeJi.Text = (jiaoYiJinE - kaiPiaoJinE).ToString("C2"); } else { phLB.Visible = false; phEmpty.Visible = true; } bll = null; }
/// <summary> /// 获取发票管理列表合计信息 /// </summary> /// <param name="companyId">公司编号</param> /// <param name="searchInfo">查询实体</param> /// <param name="jiaoYiJinE">交易金额合计</param> /// <param name="kaiPiaoJinE">开票金额合计</param> public void GetFaPiaoGuanLisHeJi(int companyId, EyouSoft.Model.FinanceStructure.MFaPiaoGuanLiSearchInfo searchInfo, out decimal jiaoYiJinE, out decimal kaiPiaoJinE) { StringBuilder cmdText = new StringBuilder(); jiaoYiJinE = 0; kaiPiaoJinE = 0; #region SQL //cmdtext:SELECT SUM(C.F1),SUM(C.F2) FROM (SELECT (交易金额查询) AS F1,(开票金额查询) AS F2 FROM A )C cmdText.Append("SELECT SUM(JiaoYiJinE) AS JiaoYiJinE,SUM(KaiPiaoJinE) AS KaiPiaoJinE FROM ("); cmdText.AppendFormat(" SELECT "); cmdText.Append(" (SELECT ISNULL(SUM(B.FinanceSum),0) FROM tbl_TourOrder AS B WHERE B.BuyCompanyId=A.Id AND B.IsDelete='0' AND B.OrderState=5 "); if (searchInfo != null) { if (searchInfo.CTETime.HasValue) { cmdText.AppendFormat(" AND B.LeaveDate<'{0}' ", searchInfo.CTETime.Value.AddDays(1)); } if (searchInfo.CTSTime.HasValue) { cmdText.AppendFormat(" AND B.LeaveDate>'{0}' ", searchInfo.CTSTime.Value.AddDays(-1)); } } cmdText.Append(" ) AS JiaoYiJinE "); cmdText.Append(" ,(SELECT ISNULL(SUM(B.JinE),0) FROM tbl_FinFaPiao AS B WHERE B.CrmId=A.Id AND B.IsDelete='0' "); if (searchInfo != null) { if (searchInfo.KPETime.HasValue) { cmdText.AppendFormat(" AND B.RiQi<'{0}' ", searchInfo.KPETime.Value.AddDays(1)); } if (!string.IsNullOrEmpty(searchInfo.KPRen)) { cmdText.AppendFormat(" AND B.KaiPiaoRen LIKE '%{0}%' ", searchInfo.KPRen); } if (searchInfo.KPSTime.HasValue) { cmdText.AppendFormat(" AND B.RiQi>'{0}' ", searchInfo.KPSTime.Value.AddDays(-1)); } } cmdText.Append(" ) AS KaiPiaoJinE "); cmdText.AppendFormat(" FROM tbl_Customer AS A "); cmdText.AppendFormat(" WHERE A.CompanyId={0} AND A.IsDelete='0' ", companyId); if (searchInfo != null) { if (!string.IsNullOrEmpty(searchInfo.CrmName)) { cmdText.AppendFormat(" AND A.Name LIKE '%{0}%' ", searchInfo.CrmName); } } cmdText.Append(")C"); #endregion DbCommand cmd = _db.GetSqlStringCommand(cmdText.ToString()); using (IDataReader rdr = DbHelper.ExecuteReader(cmd, _db)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { jiaoYiJinE = rdr.GetDecimal(0); } if (!rdr.IsDBNull(1)) { kaiPiaoJinE = rdr.GetDecimal(1); } } } }
/// <summary> /// 获取发票管理列表 /// </summary> /// <param name="companyId">公司编号</param> /// <param name="pageSize">每页记录数</param> /// <param name="pageIndex">当前页索引</param> /// <param name="recordCount">总记录数</param> /// <param name="searchInfo">查询实体</param> /// <returns></returns> public IList <EyouSoft.Model.FinanceStructure.MFaPiaoGuanLiInfo> GetFaPiaoGuanLis(int companyId, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.FinanceStructure.MFaPiaoGuanLiSearchInfo searchInfo) { IList <EyouSoft.Model.FinanceStructure.MFaPiaoGuanLiInfo> items = new List <EyouSoft.Model.FinanceStructure.MFaPiaoGuanLiInfo>(); StringBuilder cmdQuery = new StringBuilder(); string tableName = "tbl_Customer"; string primaryKey = "Id"; string orderByString = "IssueTime DESC"; StringBuilder fields = new StringBuilder(); #region fields fields.Append(" Id,Name "); //交易金额 fields.Append(" ,(SELECT SUM(A.FinanceSum) FROM tbl_TourOrder AS A WHERE A.BuyCompanyId=tbl_Customer.Id AND A.IsDelete='0' AND A.OrderState=5 "); if (searchInfo != null) { if (searchInfo.CTETime.HasValue) { fields.AppendFormat(" AND A.LeaveDate<'{0}' ", searchInfo.CTETime.Value.AddDays(1)); } if (searchInfo.CTSTime.HasValue) { fields.AppendFormat(" AND A.LeaveDate>'{0}' ", searchInfo.CTSTime.Value.AddDays(-1)); } } fields.Append(" ) AS JiaoYiJinE "); //开票金额 fields.Append(" ,(SELECT SUM(A.JinE) FROM tbl_FinFaPiao AS A WHERE A.CrmId=tbl_Customer.Id AND A.IsDelete='0' "); if (searchInfo != null) { if (searchInfo.KPETime.HasValue) { fields.AppendFormat(" AND A.RiQi<'{0}' ", searchInfo.KPETime.Value.AddDays(1)); } if (!string.IsNullOrEmpty(searchInfo.KPRen)) { fields.AppendFormat(" AND A.KaiPiaoRen LIKE '%{0}%' ", searchInfo.KPRen); } if (searchInfo.KPSTime.HasValue) { fields.AppendFormat(" AND A.RiQi>'{0}' ", searchInfo.KPSTime.Value.AddDays(-1)); } } fields.Append(" ) AS KaiPiaoJinE "); #endregion #region 拼接查询条件 cmdQuery.AppendFormat(" CompanyId={0} AND IsDelete='0' ", companyId); if (searchInfo != null) { if (!string.IsNullOrEmpty(searchInfo.CrmName)) { cmdQuery.AppendFormat(" AND Name LIKE '%{0}%' ", searchInfo.CrmName); } } #endregion using (IDataReader rdr = DbHelper.ExecuteReader(this._db, pageSize, pageIndex, ref recordCount, tableName, primaryKey, fields.ToString(), cmdQuery.ToString(), orderByString)) { int _index_Id = rdr.GetOrdinal("Id"); int _index_Name = rdr.GetOrdinal("Name"); int _index_JiaoYiJinE = rdr.GetOrdinal("JiaoYiJinE"); int _index_KaiPiaoJinE = rdr.GetOrdinal("KaiPiaoJinE"); while (rdr.Read()) { var item = new EyouSoft.Model.FinanceStructure.MFaPiaoGuanLiInfo(); item.CrmId = rdr.GetInt32(_index_Id); item.CrmName = rdr[_index_Name].ToString(); if (!rdr.IsDBNull(_index_JiaoYiJinE)) { item.JiaoYiJinE = rdr.GetDecimal(_index_JiaoYiJinE); } if (!rdr.IsDBNull(_index_KaiPiaoJinE)) { item.KaiPiaoJinE = rdr.GetDecimal(_index_KaiPiaoJinE); } items.Add(item); } } return(items); }
/// <summary> /// 获取发票管理列表 /// </summary> /// <param name="companyId">公司编号</param> /// <param name="pageSize">每页记录数</param> /// <param name="pageIndex">当前页索引</param> /// <param name="recordCount">总记录数</param> /// <param name="searchInfo">查询实体</param> /// <returns></returns> public IList <EyouSoft.Model.FinanceStructure.MFaPiaoGuanLiInfo> GetFaPiaoGuanLis(int companyId, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.FinanceStructure.MFaPiaoGuanLiSearchInfo searchInfo) { if (companyId < 1) { return(null); } return(dal.GetFaPiaoGuanLis(companyId, pageSize, pageIndex, ref recordCount, searchInfo)); }