/// <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);
}