/// <summary> /// 获取账龄分析-按客户单位统计合计 /// </summary> /// <param name="companyId">公司编号</param> /// <param name="searchInfo">查询信息</param> /// <param name="weiShouHeJi">未收款合计</param> public void GetZhangLingAnKeHuDanWei(int companyId, EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWeiChaXun searchInfo, out decimal weiShouHeJi) { weiShouHeJi = 0; if (companyId < 1) { return; } dal.GetZhangLingAnKeHuDanWei(companyId, searchInfo, out weiShouHeJi, HaveUserIds); }
/// <summary> /// 获取账龄分析-按客户单位统计合计 /// </summary> /// <param name="companyId">公司编号</param> /// <param name="searchInfo">查询信息</param> /// <param name="us">用户编号集合 用,间隔</param> /// <param name="weiShouHeJi">未收款合计</param> public void GetZhangLingAnKeHuDanWei(int companyId, EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWeiChaXun searchInfo, out decimal weiShouHeJi, string us) { weiShouHeJi = 0; StringBuilder cmdText = new StringBuilder(); #region SQL cmdText.AppendFormat(" SELECT SUM(FinanceSum-HasCheckMoney) AS WeiShouHeJi FROM tbl_TourOrder WHERE IsDelete = '0' AND SellCompanyId={0}", companyId); if (searchInfo != null || !string.IsNullOrEmpty(us)) { if (searchInfo.LSDate.HasValue || searchInfo.LEDate.HasValue || !string.IsNullOrEmpty(us)) { cmdText.Append(" AND EXISTS(SELECT 1 FROM tbl_Tour AS A WHERE A.TourId=tbl_TourOrder.TourId "); if (searchInfo.LSDate.HasValue) { cmdText.AppendFormat(" AND LeaveDate>='{0}' ", searchInfo.LSDate.Value); } if (searchInfo.LEDate.HasValue) { cmdText.AppendFormat(" AND LeaveDate<='{0}' ", searchInfo.LEDate.Value.AddDays(1).AddMilliseconds(-1)); } if (!string.IsNullOrEmpty(us)) { cmdText.AppendFormat(" AND OperatorId IN({0}) ", us); } cmdText.Append(")"); } } if (searchInfo != null) { if (!string.IsNullOrEmpty(searchInfo.KeHuName)) { cmdText.AppendFormat(" AND EXISTS(SELECT 1 FROM tbl_Customer WHERE Id=tbl_TourOrder.BuyCompanyId AND Name LIKE '%{0}%') ", searchInfo.KeHuName); } } #endregion DbCommand cmd = _db.GetSqlStringCommand(cmdText.ToString()); using (IDataReader rdr = DbHelper.ExecuteReader(cmd, _db)) { if (rdr.Read()) { weiShouHeJi = rdr.IsDBNull(0) ? 0 : rdr.GetDecimal(0); } } }
/// <summary> /// 绑定统计分析账龄分析按客户单位 /// </summary> private void BindZhangLingAnKeHuDanWei() { int pageSize = 20; int pageIndex = Utils.GetInt(Utils.GetQueryStringValue("Page"), 1); int recordCount = 0; var searchInfo = new EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWeiChaXun(); searchInfo.KeHuName = Utils.GetQueryStringValue("kehumingcheng"); searchInfo.SortType = Utils.GetInt(Utils.GetQueryStringValue("sorttype")); searchInfo.LSDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("lsdate")); searchInfo.LEDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("ledate")); EyouSoft.BLL.StatisticStructure.AccountAgeStatistic bll = new EyouSoft.BLL.StatisticStructure.AccountAgeStatistic(SiteUserInfo); var items = bll.GetZhangLingAnKeHuDanWei(pageSize, pageIndex, ref recordCount, CurrentUserCompanyID, searchInfo); if (items != null && items.Count > 0) { ph.Visible = true; phEmpty.Visible = false; rpt.DataSource = items; rpt.DataBind(); decimal weiShouKuanHeJi; bll.GetZhangLingAnKeHuDanWei(CurrentUserCompanyID, searchInfo, out weiShouKuanHeJi); ltrWeiShouHeJi.Text = weiShouKuanHeJi.ToString("C2"); this.ExportPageInfo1.PageLinkURL = Request.ServerVariables["SCRIPT_NAME"].ToString() + "?"; this.ExportPageInfo1.UrlParams = Request.QueryString; this.ExportPageInfo1.intPageSize = pageSize; this.ExportPageInfo1.CurrencyPage = pageIndex; this.ExportPageInfo1.intRecordCount = recordCount; } else { ph.Visible = false; phEmpty.Visible = true; } RegisterScript(string.Format("var recordCount={0};", recordCount)); }
/// <summary> /// to xls /// </summary> private void ToXls() { if (!CheckGrant(TravelPermission.统计分析_帐龄分析_帐龄分析栏目)) { ResponseToXls(string.Empty); } int pageSize = Utils.GetInt(Utils.GetQueryStringValue("recordcount")); if (pageSize < 1) { ResponseToXls(string.Empty); } int recordCount = 0; var searchInfo = new EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWeiChaXun(); searchInfo.KeHuName = Utils.GetQueryStringValue("kehumingcheng"); searchInfo.SortType = Utils.GetInt(Utils.GetQueryStringValue("sorttype")); searchInfo.LSDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("lsdate")); searchInfo.LEDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("ledate")); EyouSoft.BLL.StatisticStructure.AccountAgeStatistic bll = new EyouSoft.BLL.StatisticStructure.AccountAgeStatistic(SiteUserInfo); var items = bll.GetZhangLingAnKeHuDanWei(pageSize, 1, ref recordCount, CurrentUserCompanyID, searchInfo); System.Text.StringBuilder s = new System.Text.StringBuilder(); if (items != null && items.Count > 0) { s.Append("客户单位\t拖欠款总额\t最长拖欠时间\n"); foreach (var item in items) { s.AppendFormat("{0}\t{1}\t{2}\n", item.KeHuName, item.WeiShouKuan.ToString("C2"), item.EarlyTime.ToString("yyyy-MM-dd")); } } ResponseToXls(s.ToString()); }
/// <summary> /// 获取账龄分析-按客户单位统计 /// </summary> /// <param name="pageSize">每页记录数</param> /// <param name="pageIndex">当前页</param> /// <param name="recordCount">总记录数</param> /// <param name="companyId">公司编号</param> /// <param name="searchInfo">查询信息</param> /// <returns></returns> public IList <EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWei> GetZhangLingAnKeHuDanWei(int pageSize, int pageIndex, ref int recordCount, int companyId, EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWeiChaXun searchInfo) { if (companyId < 1) { return(null); } return(dal.GetZhangLingAnKeHuDanWei(pageSize, pageIndex, ref recordCount, companyId, searchInfo, HaveUserIds)); }
/*/// <summary> * /// 获取账龄分析-按客户单位统计 * /// </summary> * /// <param name="pageSize">每页记录数</param> * /// <param name="pageIndex">当前页</param> * /// <param name="recordCount">总记录数</param> * /// <param name="companyId">公司编号</param> * /// <param name="searchInfo">查询信息</param> * /// <returns></returns> * public IList<EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWei> GetZhangLingAnKeHuDanWei(int pageSize, int pageIndex, ref int recordCount, int companyId, EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWeiChaXun searchInfo) * { * IList<EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWei> items = new List<EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWei>(); * string cmdQuery = string.Empty; * string tableName = "view_ZhangLingAnKeHuDanWei"; * string primaryKey = "Id"; * string orderByString = "WeiShouKuan DESC"; * string fields = "*"; * #region SQL * cmdQuery += string.Format("CompanyId={0} AND WeiShouKuan>0 ", companyId); * * if (searchInfo != null) * { * if (!string.IsNullOrEmpty(searchInfo.KeHuName)) * { * cmdQuery += string.Format(" AND Name LIKE '%{0}%' ",searchInfo.KeHuName); * } * * if (searchInfo.SortType.HasValue) * { * switch (searchInfo.SortType.Value) * { * case 0: orderByString = "WeiShouKuan DESC"; break; * case 1: orderByString = "WeiShouKuan ASC"; break; * case 2: orderByString = "EarlyTime DESC"; break; * case 3: orderByString = "EarlyTime ASC"; break; * } * } * } #endregion * * using (IDataReader rdr = DbHelper.ExecuteReader(this._db, pageSize, pageIndex, ref recordCount, tableName, primaryKey, fields, cmdQuery, orderByString)) * { * while (rdr.Read()) * { * var item = new EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWei(); * if (!rdr.IsDBNull(rdr.GetOrdinal("EarlyTime"))) item.EarlyTime = rdr.GetDateTime(rdr.GetOrdinal("EarlyTime")); * item.KeHuId = rdr.GetInt32(rdr.GetOrdinal("Id")); * item.KeHuName = rdr["Name"].ToString(); * if (!rdr.IsDBNull(rdr.GetOrdinal("WeiShouKuan"))) item.WeiShouKuan = rdr.GetDecimal(rdr.GetOrdinal("WeiShouKuan")); * * items.Add(item); * } * } * * return items; * } * * /// <summary> * /// 获取账龄分析-按客户单位统计合计 * /// </summary> * /// <param name="companyId">公司编号</param> * /// <param name="searchInfo">查询信息</param> * /// <param name="weiShouHeJi">未收款合计</param> * public void GetZhangLingAnKeHuDanWei(int companyId, EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWeiChaXun searchInfo, out decimal weiShouHeJi) * { * weiShouHeJi = 0; * string cmdText = string.Empty; * #region SQL * cmdText += string.Format("SELECT SUM(WeiShouKuan) AS WeiShouKuanHeJi FROM view_ZhangLingAnKeHuDanWei WHERE CompanyId={0}", companyId); * if (searchInfo != null) * { * cmdText += string.Format("AND Name LIKE '%{0}%'", searchInfo.KeHuName); * } #endregion * * * DbCommand cmd = _db.GetSqlStringCommand(cmdText.ToString()); * * using (IDataReader rdr = DbHelper.ExecuteReader(cmd, _db)) * { * if (rdr.Read()) * { * weiShouHeJi = rdr.IsDBNull(0) ? 0 : rdr.GetDecimal(0); * } * } * }*/ /// <summary> /// 获取账龄分析-按客户单位统计 /// </summary> /// <param name="pageSize">每页记录数</param> /// <param name="pageIndex">当前页</param> /// <param name="recordCount">总记录数</param> /// <param name="companyId">公司编号</param> /// <param name="searchInfo">查询信息</param> /// <param name="us">用户编号集合 用,间隔</param> /// <returns></returns> public IList <EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWei> GetZhangLingAnKeHuDanWei(int pageSize, int pageIndex, ref int recordCount, int companyId, EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWeiChaXun searchInfo, string us) { IList <EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWei> items = new List <EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWei>(); #region SQL StringBuilder table = new StringBuilder(); table.Append(" SELECT BuyCompanyId AS KeHuId,SUM(FinanceSum-HasCheckMoney) AS WeiShouKuan,MIN(LeaveDate) AS EarlyTime "); table.AppendFormat(" FROM tbl_TourOrder WHERE IsDelete = '0' AND SellCompanyid={0} ", companyId); if (searchInfo != null || !string.IsNullOrEmpty(us)) { if (searchInfo.LSDate.HasValue || searchInfo.LEDate.HasValue || !string.IsNullOrEmpty(us)) { table.Append(" AND EXISTS(SELECT 1 FROM tbl_Tour AS A WHERE A.TourId=tbl_TourOrder.TourId "); if (searchInfo.LSDate.HasValue) { table.AppendFormat(" AND LeaveDate>='{0}' ", searchInfo.LSDate.Value); } if (searchInfo.LEDate.HasValue) { table.AppendFormat(" AND LeaveDate<='{0}' ", searchInfo.LEDate.Value.AddDays(1).AddMilliseconds(-1)); } if (!string.IsNullOrEmpty(us)) { table.AppendFormat(" AND OperatorId IN({0}) ", us); } table.Append(")"); } } table.AppendFormat(" GROUP BY BuyCompanyId "); string fields = "*,(SELECT Name FROM tbl_Customer AS A WHERE A.Id=KeHuId) AS KeHuName"; string query = " 1=1 "; if (searchInfo != null) { if (!string.IsNullOrEmpty(searchInfo.KeHuName)) { query += string.Format(" AND EXISTS(SELECT 1 FROM tbl_Customer WHERE Id=KeHuId AND Name LIKE '%{0}%') ", searchInfo.KeHuName); } } string orderByString = "WeiShouKuan DESC"; if (searchInfo.SortType.HasValue) { switch (searchInfo.SortType.Value) { case 0: orderByString = "WeiShouKuan DESC"; break; case 1: orderByString = "WeiShouKuan ASC"; break; case 2: orderByString = "EarlyTime DESC"; break; case 3: orderByString = "EarlyTime ASC"; break; } } #endregion using (IDataReader rdr = DbHelper.ExecuteReaderBySqlTbl(_db, pageSize, pageIndex, ref recordCount, table.ToString(), fields, query, orderByString, false)) { while (rdr.Read()) { var item = new EyouSoft.Model.StatisticStructure.ZhangLingAnKeHuDanWei(); if (!rdr.IsDBNull(rdr.GetOrdinal("EarlyTime"))) { item.EarlyTime = rdr.GetDateTime(rdr.GetOrdinal("EarlyTime")); } item.KeHuId = rdr.GetInt32(rdr.GetOrdinal("KeHuId")); item.KeHuName = rdr["KeHuName"].ToString(); if (!rdr.IsDBNull(rdr.GetOrdinal("WeiShouKuan"))) { item.WeiShouKuan = rdr.GetDecimal(rdr.GetOrdinal("WeiShouKuan")); } items.Add(item); } } return(items); }