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