/// <summary>
/// 绑定列表
/// </summary>
void BindInfo()
{
string selValue = Utils.GetFormValue(selOperator.UniqueID);
selValue = selValue == "" ? "0" : selValue;
BindUserList(selValue);
IList <EyouSoft.Model.FinanceStructure.OutRegisterInfo> list = new
List <EyouSoft.Model.FinanceStructure.OutRegisterInfo>();
EyouSoft.BLL.FinanceStructure.OutRegister bll = new EyouSoft.BLL.FinanceStructure.OutRegister(SiteUserInfo);
EyouSoft.Model.FinanceStructure.QueryOutRegisterInfo searchInfo = new EyouSoft.Model.FinanceStructure.QueryOutRegisterInfo();
int count = 0;
#region 查询参数
searchInfo.CompanyId = CurrentUserCompanyID;
//searchInfo.FTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("date"));
txt_Date.Value = Utils.GetQueryStringValue("date");
searchInfo.RegisterType = EyouSoft.Model.EnumType.FinanceStructure.OutRegisterType.团队;
searchInfo.TourNo = Utils.GetQueryStringValue("teamNum");
txt_teamNum.Value = searchInfo.TourNo;
searchInfo.OperatorId = Utils.GetInt(Utils.GetQueryStringValue("Operator"));
searchInfo.RegisterStatus = Utils.GetIntNull(Utils.GetQueryStringValue("Status"));
if (selOperator.Items.FindByValue(searchInfo.OperatorId.ToString()) != null)
{
selOperator.Items.FindByValue(searchInfo.OperatorId.ToString()).Selected = true;
}
if (seleState.Items.FindByValue(searchInfo.RegisterStatus.ToString()) != null)
{
seleState.Items.FindByValue(searchInfo.RegisterStatus.ToString()).Selected = true;
}
searchInfo.ReceiveCompanyName = Utils.GetQueryStringValue("com");
txt_com.Value = searchInfo.ReceiveCompanyName;
searchInfo.StartTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("date"));
searchInfo.EndTime = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("regedate"));
#endregion
list = bll.GetOutRegisterList(searchInfo, 20, Utils.GetInt(Utils.GetQueryStringValue("page")), ref count);
rpt_list.DataSource = list;
rpt_list.DataBind();
bll.GetOutRegisterList(searchInfo, ref paymentAmount);
#region 分页
ExportPageInfo1.intPageSize = 20;
ExportPageInfo1.intRecordCount = count;
ExportPageInfo1.PageLinkURL = Request.Path + "?";
ExportPageInfo1.UrlParams = Request.QueryString;
ExportPageInfo1.CurrencyPage = EyouSoft.Common.Utils.GetInt(Request.QueryString["page"], 1);
#endregion
rpt_list.EmptyText = "<tr><td id=\"EmptyData\" colspan='8' bgcolor='#e3f1fc' height='50px' align='center'>暂时没有数据！</td></tr>";
}
/// <summary>
/// 根据查询实体生成SqlWhere子句
/// </summary>
/// <param name="model">查询实体</param>
/// <param name="HaveUserIds">用户Id集合，半角逗号分割</param>
/// <param name="strOrder">排序语句</param>
/// <returns>SqlWhere子句</returns>
private string GetSqlWhere(EyouSoft.Model.FinanceStructure.QueryOutRegisterInfo model, string HaveUserIds
, ref string strOrder)
{
if (model == null)
{
return(string.Empty);
}
StringBuilder strSqlWhere = new StringBuilder(" 1 = 1 ");
if (model.CompanyId > 0)
{
strSqlWhere.AppendFormat(" and CompanyID = {0} ", model.CompanyId);
}
if (!string.IsNullOrEmpty(model.ItemId))
{
strSqlWhere.AppendFormat(" and ItemId = '{0}' ", model.ItemId);
}
if (model.RegisterType.HasValue)
{
strSqlWhere.AppendFormat(" and ItemType = {0} ", (int)model.RegisterType);
}
if (!string.IsNullOrEmpty(model.ReceiveId))
{
strSqlWhere.AppendFormat(" and ReceiveId = '{0}' ", model.ReceiveId);
}
if (model.ReceiveType.HasValue)
{
strSqlWhere.AppendFormat(" and ReceiveType = {0} ", (int)model.ReceiveType);
}
if (!string.IsNullOrEmpty(model.TourNo))
{
strSqlWhere.AppendFormat(" and (ItemType = 0 and ItemId in (select TourId from tbl_Tour where TourCode like '%{0}%')) ", model.TourNo);
}
/*if (model.OperatorId > 0)
* strSqlWhere.AppendFormat(" and StaffNo = {0} ", model.OperatorId);*/
if (model.StartTime.HasValue)
{
strSqlWhere.AppendFormat(" and DATEDIFF(dd,PaymentDate,'{0}') <= 0 ", model.StartTime.Value.ToShortDateString());
}
if (model.EndTime.HasValue)
{
strSqlWhere.AppendFormat(" and DATEDIFF(dd,PaymentDate,'{0}') >= 0 ", model.EndTime.Value.ToShortDateString());
}
if (!string.IsNullOrEmpty(HaveUserIds))
{
//strSqlWhere.AppendFormat(" and OperatorID in ({0}) ", HaveUserIds);
strSqlWhere.AppendFormat(" AND EXISTS (SELECT 1 FROM tbl_Tour AS A WHERE A.TourId=tbl_FinancialPayInfo.ItemId AND A.OperatorId IN({0}) ) ", HaveUserIds);
}
if (model.ReceiveCompanyId > 0)
{
strSqlWhere.AppendFormat(" and ReceiveCompanyId = {0} ", model.ReceiveCompanyId);
}
if (!string.IsNullOrEmpty(model.ReceiveCompanyName))
{
strSqlWhere.AppendFormat(" and ReceiveCompanyName like '%{0}%' ", model.ReceiveCompanyName);
}
if (model.OperatorId > 0)
{
strSqlWhere.AppendFormat(" AND OperatorID={0} ", model.OperatorId);
}
if (model.FTime.HasValue)
{
strSqlWhere.AppendFormat(" AND DATEDIFF(DAY,'{0}',PaymentDate)=0 ", model.FTime.Value);
}
if (model.RegisterStatus.HasValue)
{
switch (model.RegisterStatus.Value)
{
case 1:
strSqlWhere.Append(" AND IsChecked='0' ");
break;
case 2:
strSqlWhere.Append(" AND IsChecked='1' AND IsPay='0' ");
break;
}
}
switch (model.OrderIndex)
{
case 0:
strOrder = " PaymentDate asc ";
break;
case 1:
strOrder = " PaymentDate desc ";
break;
}
return(strSqlWhere.ToString());
}
/// <summary>
/// 获取支出登记明细
/// </summary>
/// <param name="model">支出登记查询实体</param>
/// <param name="HaveUserIds">用户Id集合，半角逗号分割</param>
/// <param name="PageSize">每页条数</param>
/// <param name="PageIndex">当前页数</param>
/// <param name="RecordCount">总记录数</param>
/// <returns></returns>
public IList <EyouSoft.Model.FinanceStructure.OutRegisterInfo> GetOutRegisterList(EyouSoft.Model.FinanceStructure.QueryOutRegisterInfo model, string HaveUserIds, int PageSize, int PageIndex, ref int RecordCount)
{
IList <EyouSoft.Model.FinanceStructure.OutRegisterInfo> list = new List <EyouSoft.Model.FinanceStructure.OutRegisterInfo>();
string strFiles = @" [Id],[CompanyID],[ItemId],[ItemType],[ReceiveId],[ReceiveType],[ReceiveCompanyId],[ReceiveCompanyName],[PaymentDate],[StaffNo],[StaffName],[PaymentAmount],[PaymentType],[IsBill],[BillAmount],[BillNo],[Remark],[IsChecked],[OperatorID],[IssueTime],[CheckerId],[IsPay],
(case when [ItemType] = 0 then
(select TourCode,RouteName from tbl_tour where tourid = [ItemId] for xml auto,root('root'))
else
''
end
) as TourInfo ";
string strOrder = string.Empty;
string strWhere = this.GetSqlWhere(model, HaveUserIds, ref strOrder);
using (IDataReader dr = DbHelper.ExecuteReader(_db, PageSize, PageIndex, ref RecordCount, "tbl_FinancialPayInfo"
, "Id", strFiles, strWhere, strOrder))
{
EyouSoft.Model.FinanceStructure.OutRegisterInfo tmpModel = null;
System.Xml.XmlDocument xml = null;
System.Xml.XmlNodeList xmlNodeList = null;
while (dr.Read())
{
tmpModel = new EyouSoft.Model.FinanceStructure.OutRegisterInfo();
#region 实体赋值
tmpModel.RegisterId = dr["Id"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("CompanyID")))
{
tmpModel.CompanyId = int.Parse(dr["CompanyID"].ToString());
}
tmpModel.ItemId = dr["ItemId"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("ItemType")))
{
tmpModel.RegisterType = (Model.EnumType.FinanceStructure.OutRegisterType) int.Parse(dr["ItemType"].ToString());
}
tmpModel.ReceiveId = dr["ReceiveId"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("ReceiveType")))
{
tmpModel.ReceiveType = (Model.EnumType.FinanceStructure.OutPlanType) int.Parse(dr["ReceiveType"].ToString());
}
if (!dr.IsDBNull(dr.GetOrdinal("ReceiveCompanyId")))
{
tmpModel.ReceiveCompanyId = int.Parse(dr["ReceiveCompanyId"].ToString());
}
tmpModel.ReceiveCompanyName = dr["ReceiveCompanyName"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("PaymentDate")))
{
tmpModel.PaymentDate = DateTime.Parse(dr["PaymentDate"].ToString());
}
if (!dr.IsDBNull(dr.GetOrdinal("StaffNo")))
{
tmpModel.StaffNo = int.Parse(dr["StaffNo"].ToString());
}
tmpModel.StaffName = dr["StaffName"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("PaymentAmount")))
{
tmpModel.PaymentAmount = decimal.Parse(dr["PaymentAmount"].ToString());
}
if (!dr.IsDBNull(dr.GetOrdinal("PaymentType")))
{
tmpModel.PaymentType = (Model.EnumType.TourStructure.RefundType) int.Parse(dr["PaymentType"].ToString());
}
if (!string.IsNullOrEmpty(dr["IsBill"].ToString()) && (dr["IsBill"].ToString() == "1" || dr["IsBill"].ToString().ToLower() == "true"))
{
tmpModel.IsBill = true;
}
if (!dr.IsDBNull(dr.GetOrdinal("BillAmount")))
{
tmpModel.BillAmount = decimal.Parse(dr["BillAmount"].ToString());
}
tmpModel.BillNo = dr["BillNo"].ToString();
tmpModel.Remark = dr["Remark"].ToString();
if (!string.IsNullOrEmpty(dr["IsChecked"].ToString()) && (dr["IsChecked"].ToString() == "1" || dr["IsChecked"].ToString().ToLower() == "true"))
{
tmpModel.IsChecked = true;
}
if (!dr.IsDBNull(dr.GetOrdinal("OperatorID")))
{
tmpModel.OperatorId = int.Parse(dr["OperatorID"].ToString());
}
if (!dr.IsDBNull(dr.GetOrdinal("IssueTime")))
{
tmpModel.IssueTime = DateTime.Parse(dr["IssueTime"].ToString());
}
if (!dr.IsDBNull(dr.GetOrdinal("CheckerId")))
{
tmpModel.CheckerId = int.Parse(dr["CheckerId"].ToString());
}
if (!string.IsNullOrEmpty(dr["IsPay"].ToString()) && (dr["IsPay"].ToString() == "1" || dr["IsPay"].ToString().ToLower() == "true"))
{
tmpModel.IsPay = true;
}
if (!dr.IsDBNull(dr.GetOrdinal("TourInfo")))
{
xml = new System.Xml.XmlDocument();
xml.LoadXml(dr.GetString(dr.GetOrdinal("TourInfo")));
xmlNodeList = xml.GetElementsByTagName("tbl_tour");
if (xmlNodeList != null && xmlNodeList.Count > 0)
{
if (xmlNodeList[0].Attributes["TourCode"] != null && !string.IsNullOrEmpty(xmlNodeList[0].Attributes["TourCode"].Value))
{
tmpModel.TourCode = xmlNodeList[0].Attributes["TourCode"].Value;
}
if (xmlNodeList[0].Attributes["RouteName"] != null && !string.IsNullOrEmpty(xmlNodeList[0].Attributes["RouteName"].Value))
{
tmpModel.RouteName = xmlNodeList[0].Attributes["RouteName"].Value;
}
}
}
#endregion
list.Add(tmpModel);
}
}
return(list);
}
/// <summary>
/// 获取支出登记明细
/// </summary>
/// <param name="model">支出登记查询实体</param>
/// <returns></returns>
public IList <EyouSoft.Model.FinanceStructure.OutRegisterInfo> GetOutRegisterList(EyouSoft.Model.FinanceStructure.QueryOutRegisterInfo model)
{
IList <EyouSoft.Model.FinanceStructure.OutRegisterInfo> list = new List <EyouSoft.Model.FinanceStructure.OutRegisterInfo>();
string strOrder = string.Empty;
DbCommand dc = _db.GetSqlStringCommand(Sql_OutRegister_Select + " where " + this.GetSqlWhere(model, string.Empty, ref strOrder) + (string.IsNullOrEmpty(strOrder) ? string.Empty : " order by " + strOrder));
using (IDataReader dr = DbHelper.ExecuteReader(dc, _db))
{
EyouSoft.Model.FinanceStructure.OutRegisterInfo tmpModel = null;
while (dr.Read())
{
tmpModel = new EyouSoft.Model.FinanceStructure.OutRegisterInfo();
#region 实体赋值
tmpModel.RegisterId = dr["Id"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("CompanyID")))
{
tmpModel.CompanyId = int.Parse(dr["CompanyID"].ToString());
}
tmpModel.ItemId = dr["ItemId"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("ItemType")))
{
tmpModel.RegisterType = (Model.EnumType.FinanceStructure.OutRegisterType) int.Parse(dr["ItemType"].ToString());
}
tmpModel.ReceiveId = dr["ReceiveId"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("ReceiveType")))
{
tmpModel.ReceiveType = (Model.EnumType.FinanceStructure.OutPlanType) int.Parse(dr["ReceiveType"].ToString());
}
if (!dr.IsDBNull(dr.GetOrdinal("ReceiveCompanyId")))
{
tmpModel.ReceiveCompanyId = int.Parse(dr["ReceiveCompanyId"].ToString());
}
tmpModel.ReceiveCompanyName = dr["ReceiveCompanyName"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("PaymentDate")))
{
tmpModel.PaymentDate = DateTime.Parse(dr["PaymentDate"].ToString());
}
if (!dr.IsDBNull(dr.GetOrdinal("StaffNo")))
{
tmpModel.StaffNo = int.Parse(dr["StaffNo"].ToString());
}
tmpModel.StaffName = dr["StaffName"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("PaymentAmount")))
{
tmpModel.PaymentAmount = decimal.Parse(dr["PaymentAmount"].ToString());
}
if (!dr.IsDBNull(dr.GetOrdinal("PaymentType")))
{
tmpModel.PaymentType = (Model.EnumType.TourStructure.RefundType) int.Parse(dr["PaymentType"].ToString());
}
if (!string.IsNullOrEmpty(dr["IsBill"].ToString()) && (dr["IsBill"].ToString() == "1" || dr["IsBill"].ToString().ToLower() == "true"))
{
tmpModel.IsBill = true;
}
if (!dr.IsDBNull(dr.GetOrdinal("BillAmount")))
{
tmpModel.BillAmount = decimal.Parse(dr["BillAmount"].ToString());
}
tmpModel.BillNo = dr["BillNo"].ToString();
tmpModel.Remark = dr["Remark"].ToString();
if (!string.IsNullOrEmpty(dr["IsChecked"].ToString()) && (dr["IsChecked"].ToString() == "1" || dr["IsChecked"].ToString().ToLower() == "true"))
{
tmpModel.IsChecked = true;
}
if (!dr.IsDBNull(dr.GetOrdinal("OperatorID")))
{
tmpModel.OperatorId = int.Parse(dr["OperatorID"].ToString());
}
if (!dr.IsDBNull(dr.GetOrdinal("IssueTime")))
{
tmpModel.IssueTime = DateTime.Parse(dr["IssueTime"].ToString());
}
if (!dr.IsDBNull(dr.GetOrdinal("CheckerId")))
{
tmpModel.CheckerId = int.Parse(dr["CheckerId"].ToString());
}
if (!string.IsNullOrEmpty(dr["IsPay"].ToString()) && (dr["IsPay"].ToString() == "1" || dr["IsPay"].ToString().ToLower() == "true"))
{
tmpModel.IsPay = true;
}
#endregion
list.Add(tmpModel);
}
}
return(list);
}
/// <summary>
/// 获取支出登记明细
/// </summary>
/// <param name="model">支出登记查询实体</param>
/// <returns></returns>
public IList <EyouSoft.Model.FinanceStructure.OutRegisterInfo> GetOutRegisterList(EyouSoft.Model.FinanceStructure.QueryOutRegisterInfo model)
{
return(idal.GetOutRegisterList(model));
}
/// <summary>
/// 获取支出登记明细
/// </summary>
/// <param name="model">支出登记查询实体</param>
/// <param name="PageSize">每页条数</param>
/// <param name="PageIndex">当前页数</param>
/// <param name="RecordCount">总记录数</param>
/// <returns></returns>
public IList <EyouSoft.Model.FinanceStructure.OutRegisterInfo> GetOutRegisterList(EyouSoft.Model.FinanceStructure.QueryOutRegisterInfo model, int PageSize, int PageIndex, ref int RecordCount)
{
return(idal.GetOutRegisterList(model, base.HaveUserIds, PageSize, PageIndex, ref RecordCount));
}
