/// <summary>
/// 获取支出账龄分析合计信息（按供应商单位）
/// </summary>
/// <param name="companyId">公司编号</param>
/// <param name="chaXun">查询信息</param>
/// <param name="weiFuKuanHeJi">未付金额合计</param>
/// <param name="zongJinEHeJi">交易总金额合计</param>
public void GetZhiChuZhangLing(int companyId, EyouSoft.Model.StatisticStructure.FXZhiChuZhangLingChaXunInfo chaXun, out decimal weiFuKuanHeJi, out decimal zongJinEHeJi)
{
weiFuKuanHeJi = 0;
zongJinEHeJi = 0;
if (companyId < 1)
{
return;
}
dal.GetZhiChuZhangLing(companyId, chaXun, out weiFuKuanHeJi, out zongJinEHeJi);
}
/// <summary>
/// 获取查询信息
/// </summary>
/// <returns></returns>
EyouSoft.Model.StatisticStructure.FXZhiChuZhangLingChaXunInfo GetSearchInfo()
{
var info = new EyouSoft.Model.StatisticStructure.FXZhiChuZhangLingChaXunInfo();
info.GongYingShang = Utils.GetQueryStringValue("gongyingshang");
info.SortType = Utils.GetInt(Utils.GetQueryStringValue("sorttype"));
info.LSDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("lsdate"));
info.LEDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("ledate"));
info.OperatorDepartIds = Utils.GetIntArray(Utils.GetQueryStringValue("departids"), ",");
info.OperatorIds = Utils.GetIntArray(Utils.GetQueryStringValue("operatorids"), ",");
info.GongYingShangLeiXing = (EyouSoft.Model.EnumType.CompanyStructure.SupplierType?)Utils.GetEnumValue(typeof(EyouSoft.Model.EnumType.CompanyStructure.SupplierType), Utils.GetQueryStringValue("gongyingshangleixing"), null);
return(info);
}
/// <summary>
/// 获取支出账龄分析合计信息（按供应商单位）
/// </summary>
/// <param name="companyId">公司编号</param>
/// <param name="chaXun">查询信息</param>
/// <param name="weiFuKuanHeJi">未付金额合计</param>
/// <param name="zongJinEHeJi">交易总金额合计</param>
public void GetZhiChuZhangLing(int companyId, EyouSoft.Model.StatisticStructure.FXZhiChuZhangLingChaXunInfo chaXun, out decimal weiFuKuanHeJi, out decimal zongJinEHeJi)
{
weiFuKuanHeJi = 0;
zongJinEHeJi = 0;
StringBuilder cmdText = new StringBuilder();
cmdText.Append("SELECT SUM(WeiFuZongE) AS WeiFuZongEHeJi,SUM(ZongJinE) AS ZongJinEHeJi FROM ( ");
cmdText.Append(" SELECT ");
cmdText.Append(" (SELECT ISNULL(SUM(TotalAmount-HasCheckAmount),0) FROM tbl_StatAllOut AS B WHERE B.SupplierId=A.Id AND B.IsDelete='0' {0}) AS WeiFuZongE ");
cmdText.Append(" ,(SELECT ISNULL(SUM(TotalAmount),0) FROM tbl_StatAllOut AS B WHERE B.SupplierId=A.Id AND B.IsDelete='0' {0}) AS ZongJinE");
cmdText.AppendFormat(" FROM tbl_CompanySupplier AS A WHERE A.CompanyId={0} ", companyId);
string _tourQuery = string.Empty;
if (chaXun != null)
{
if (chaXun.LEDate.HasValue ||
chaXun.LSDate.HasValue ||
(chaXun.OperatorDepartIds != null && chaXun.OperatorDepartIds.Length > 0) ||
(chaXun.OperatorIds != null && chaXun.OperatorIds.Length > 0))
{
_tourQuery += " AND EXISTS(SELECT 1 FROM tbl_Tour AS C WHERE C.TourId=B.TourId ";
if (chaXun.LEDate.HasValue)
{
_tourQuery += string.Format(" AND C.LeaveDate<='{0}' ", chaXun.LEDate.Value.AddDays(1).AddMilliseconds(-1));
}
if (chaXun.LSDate.HasValue)
{
_tourQuery += string.Format(" AND C.LeaveDate>='{0}' ", chaXun.LSDate.Value);
}
if (chaXun.OperatorDepartIds != null && chaXun.OperatorDepartIds.Length > 0)
{
_tourQuery += string.Format(" AND OperatorId IN(SELECT Id FROM tbl_CompanyUser WHERE DepartId IN({0})) ", Utils.GetSqlIdStrByArray(chaXun.OperatorDepartIds));
}
if (chaXun.OperatorIds != null && chaXun.OperatorIds.Length > 0)
{
_tourQuery += string.Format(" AND OperatorId IN({0}) ", Utils.GetSqlIdStrByArray(chaXun.OperatorIds));
}
_tourQuery += " ) ";
}
}
if (chaXun != null)
{
if (chaXun.GongYingShangLeiXing.HasValue)
{
cmdText.AppendFormat(" AND A.SupplierType={0} ", (int)chaXun.GongYingShangLeiXing.Value);
}
if (!string.IsNullOrEmpty(chaXun.GongYingShang))
{
cmdText.AppendFormat(" AND A.UnitName LIKE '%{0}%' ", chaXun.GongYingShang);
}
}
cmdText.Append(")D WHERE D.ZongJinE>0 ");
DbCommand cmd = _db.GetSqlStringCommand(string.Format(cmdText.ToString(), _tourQuery));
using (IDataReader rdr = DbHelper.ExecuteReader(cmd, _db))
{
if (rdr.Read())
{
if (!rdr.IsDBNull(0))
{
weiFuKuanHeJi = rdr.GetDecimal(0);
}
if (!rdr.IsDBNull(1))
{
zongJinEHeJi = rdr.GetDecimal(1);
}
}
}
}
/// <summary>
/// 获取支出账龄分析（按供应商单位）
/// </summary>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageIndex">当前页</param>
/// <param name="recordCount">总记录数</param>
/// <param name="companyId">公司编号</param>
/// <param name="chaXun">查询信息</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.FXZhiChuZhangLingInfo> GetZhiChuZhangLing(int pageSize, int pageIndex, ref int recordCount, int companyId, EyouSoft.Model.StatisticStructure.FXZhiChuZhangLingChaXunInfo chaXun)
{
if (companyId < 1)
{
return(null);
}
return(dal.GetZhiChuZhangLing(pageSize, pageIndex, ref recordCount, companyId, chaXun));
}
/// <summary>
/// 获取支出账龄分析（按供应商单位）
/// </summary>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageIndex">当前页</param>
/// <param name="recordCount">总记录数</param>
/// <param name="companyId">公司编号</param>
/// <param name="chaXun">查询信息</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.FXZhiChuZhangLingInfo> GetZhiChuZhangLing(int pageSize, int pageIndex, ref int recordCount, int companyId, EyouSoft.Model.StatisticStructure.FXZhiChuZhangLingChaXunInfo chaXun)
{
IList <EyouSoft.Model.StatisticStructure.FXZhiChuZhangLingInfo> items = new List <EyouSoft.Model.StatisticStructure.FXZhiChuZhangLingInfo>();
#region SQL
StringBuilder _tbl = new StringBuilder();
_tbl.Append(" SELECT A.Id,A.UnitName AS GongYingShang,A.SupplierType ");
_tbl.Append(" ,(SELECT ISNULL(SUM(TotalAmount),0) FROM tbl_StatAllOut AS B WHERE B.SupplierId=A.Id AND B.IsDelete='0' {0}) AS JiaoYiZongE ");
//_tbl.Append(" ,(SELECT ISNULL(SUM(HasCheckAmount),0) FROM tbl_StatAllOut AS B WHERE B.SupplierId=A.Id AND B.IsDelete='0') AS YiFuZongE ");
_tbl.Append(" ,(SELECT ISNULL(SUM(TotalAmount-HasCheckAmount),0) FROM tbl_StatAllOut AS B WHERE B.SupplierId=A.Id AND B.IsDelete='0' {0}) AS WeiFuZongE ");
_tbl.Append(" ,(SELECT ISNULL(MIN(CreateTime),GETDATE()) FROM tbl_StatAllOut AS B WHERE B.SupplierId=A.Id AND B.IsDelete='0' {0}) AS EarlyTime ");
_tbl.AppendFormat(" FROM tbl_CompanySupplier AS A WHERE A.CompanyId={0} ", companyId);
if (chaXun != null)
{
if (chaXun.GongYingShangLeiXing.HasValue)
{
_tbl.AppendFormat(" AND A.SupplierType={0} ", (int)chaXun.GongYingShangLeiXing.Value);
}
if (!string.IsNullOrEmpty(chaXun.GongYingShang))
{
_tbl.AppendFormat(" AND A.UnitName LIKE '%{0}%' ", chaXun.GongYingShang);
}
}
string _tourQuery = string.Empty;
if (chaXun != null)
{
if (chaXun.LEDate.HasValue ||
chaXun.LSDate.HasValue ||
(chaXun.OperatorDepartIds != null && chaXun.OperatorDepartIds.Length > 0) ||
(chaXun.OperatorIds != null && chaXun.OperatorIds.Length > 0))
{
_tourQuery += " AND EXISTS(SELECT 1 FROM tbl_Tour AS C WHERE C.TourId=B.TourId ";
if (chaXun.LEDate.HasValue)
{
_tourQuery += string.Format(" AND C.LeaveDate<='{0}' ", chaXun.LEDate.Value.AddDays(1).AddMilliseconds(-1));
}
if (chaXun.LSDate.HasValue)
{
_tourQuery += string.Format(" AND C.LeaveDate>='{0}' ", chaXun.LSDate.Value);
}
if (chaXun.OperatorDepartIds != null && chaXun.OperatorDepartIds.Length > 0)
{
_tourQuery += string.Format(" AND OperatorId IN(SELECT Id FROM tbl_CompanyUser WHERE DepartId IN({0})) ", Utils.GetSqlIdStrByArray(chaXun.OperatorDepartIds));
}
if (chaXun.OperatorIds != null && chaXun.OperatorIds.Length > 0)
{
_tourQuery += string.Format(" AND OperatorId IN({0}) ", Utils.GetSqlIdStrByArray(chaXun.OperatorIds));
}
_tourQuery += " ) ";
}
}
string fields = "*";
string query = " JiaoYiZongE > 0 ";//WeiFuZongE>0
string orderByString = "WeiFuZongE DESC";
if (chaXun != null)
{
switch (chaXun.SortType)
{
case 1: orderByString = "WeiFuZongE ASC"; break;
case 2: orderByString = "EarlyTime DESC"; break;
case 3: orderByString = "EarlyTime ASC"; break;
case 4: orderByString = "JiaoYiZongE DESC"; break;
case 5: orderByString = "JiaoYiZongE ASC"; break;
}
}
#endregion
using (IDataReader rdr = DbHelper.ExecuteReaderBySqlTbl(_db, pageSize, pageIndex, ref recordCount, string.Format(_tbl.ToString(), _tourQuery), fields, query, orderByString, false))
{
while (rdr.Read())
{
var item = new EyouSoft.Model.StatisticStructure.FXZhiChuZhangLingInfo();
item.EarlyTime = rdr.GetDateTime(rdr.GetOrdinal("EarlyTime"));
item.GongYingShang = rdr["GongYingShang"].ToString();
item.JiaoYiZongE = rdr.GetDecimal(rdr.GetOrdinal("JiaoYiZongE"));
item.QianKuanZongE = rdr.GetDecimal(rdr.GetOrdinal("WeiFuZongE"));
item.GongYingShangLeiXing = (EyouSoft.Model.EnumType.CompanyStructure.SupplierType)rdr.GetByte(rdr.GetOrdinal("SupplierType"));
items.Add(item);
}
}
return(items);
}
