Beispiel #1
0
        protected void BindSum(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic model)
        {
            Decimal total = 0; //总收入
            Decimal ys    = 0; //应收
            Decimal ws    = 0; //未收

            //BLL声明
            EyouSoft.BLL.StatisticStructure.StatAllOut outBLL = new EyouSoft.BLL.StatisticStructure.StatAllOut(SiteUserInfo);
            //获取总计数据
            outBLL.GetAllTotalAmount(model, ref total, ref ys);
            if (total != 0)
            {
                res = true;
            }
            //计算未收金额
            ws = total - ys;
            //总金额赋值_已结清
            this.sum_total_account.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(total.ToString()).ToString("0.00"));
            //总金额赋值_所有
            this.sum_total_noaccount.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(total.ToString()).ToString("0.00"));
            //应收金额赋值_已结清
            this.sum_ys_account.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(ys.ToString()).ToString("0.00"));
            //应收金额赋值_所有
            this.sum_ys_noaccount.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(ys.ToString()).ToString("0.00"));
            //未收金额赋值_已结清
            this.sum_ws_account.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(ws.ToString()).ToString("0.00"));
            //未收金额赋值_所有
            this.sum_ws_noaccount.Text = EyouSoft.Common.Utils.FilterEndOfTheZeroString(EyouSoft.Common.Utils.GetDecimal(ws.ToString()).ToString("0.00"));
        }
Beispiel #2
0
        /// <summary>
        /// 获取支出对帐单的汇总信息(统计分析-支出对账单列表合计)
        /// </summary>
        /// <param name="queryInfo">查询实体</param>
        /// <param name="totalAmount">总支出</param>
        /// <param name="hasCheckAmount">已付</param>
        /// <param name="haveUserIds">用户Id集合,半角逗号分割</param>
        public void GetAllTotalAmount(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo
                                      , ref decimal totalAmount, ref decimal hasCheckAmount, string haveUserIds)
        {
            if (queryInfo == null || queryInfo.CompanyId <= 0)
            {
                return;
            }

            string        orderByStr = string.Empty;
            string        Query      = this.GetSqlWhereByQuery(queryInfo, haveUserIds, ref orderByStr);
            StringBuilder strSql     = new StringBuilder();

            strSql.Append(" select sum(a.TotalAmount) as AllTotalAmount,sum(a.HasCheckAmount) as AllHasCheckAmount from (select sum(TotalAmount) as TotalAmount,sum(HasCheckAmount) as HasCheckAmount from tbl_StatAllOut ");
            strSql.Append(" where " + Query);
            strSql.Append(" ) as a ");

            DbCommand dc = this._db.GetSqlStringCommand(strSql.ToString());

            using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db))
            {
                if (dr.Read())
                {
                    if (!dr.IsDBNull(0))
                    {
                        totalAmount = dr.GetDecimal(0);
                    }
                    if (!dr.IsDBNull(1))
                    {
                        hasCheckAmount = dr.GetDecimal(1);
                    }
                }
            }
        }
Beispiel #3
0
        /// <summary>
        /// 获取支出对帐单的汇总信息(统计分析-支出对账单列表合计)
        /// </summary>
        /// <param name="queryInfo">查询实体</param>
        /// <param name="TotalAmount">总支出</param>
        /// <param name="HasCheckAmount">已付</param>
        public void GetAllTotalAmount(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo
                                      , ref decimal TotalAmount, ref decimal HasCheckAmount)
        {
            if (queryInfo == null || queryInfo.CompanyId <= 0)
            {
                return;
            }

            DateTime?_StartDate = null;
            DateTime?_EndDate   = null;

            _EndDate   = queryInfo.LeaveDateEnd;
            _StartDate = queryInfo.LeaveDateStart;

            if (!queryInfo.LeaveDateEnd.HasValue)
            {
                queryInfo.LeaveDateEnd = new DateTime(DateTime.Now.Year + 1, 1, 1).AddMilliseconds(-1);
            }
            else
            {
                queryInfo.LeaveDateEnd = queryInfo.LeaveDateEnd.Value.AddDays(1).AddMilliseconds(-1);
            }

            if (!queryInfo.LeaveDateStart.HasValue)
            {
                queryInfo.LeaveDateStart = new DateTime(DateTime.Now.Year, 1, 1);
            }

            idal.GetAllTotalAmount(queryInfo, ref TotalAmount, ref HasCheckAmount, HaveUserIds);

            queryInfo.LeaveDateEnd   = _EndDate;
            queryInfo.LeaveDateStart = _StartDate;
        }
Beispiel #4
0
        /// <summary>
        /// 获取收入对帐单的汇总信息
        /// </summary>
        /// <param name="queryInfo">查询实体</param>
        /// <param name="TotalAmount">总收入</param>
        /// <param name="HasCheckAmount">已收</param>
        public void GetAllTotalAmount(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo
                                      , ref decimal TotalAmount, ref decimal HasCheckAmount)
        {
            if (queryInfo == null || queryInfo.CompanyId <= 0)
            {
                return;
            }

            idal.GetAllTotalAmount(queryInfo, ref TotalAmount, ref HasCheckAmount, HaveUserIds);
        }
Beispiel #5
0
        private void InitOutDepartStaticlist()
        {
            #region 获取表单值
            PageIndex     = Utils.GetInt(Utils.GetQueryStringValue("Page"), 1);
            RouteAreaName = Utils.GetQueryStringValue("RouteArea");
            OperatorName  = Utils.GetQueryStringValue("OperatorName");
            OperatorId    = Utils.GetQueryStringValue("OperatorId");
            StartDate     = Utils.GetDateTimeNullable(Request.QueryString["StartDate"]);
            EndDate       = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("EndDate"));
            TourType      = Utils.GetQueryStringValue("TourType");
            CompanyName   = Utils.GetQueryStringValue("CompanyName");
            isAccount     = Utils.GetQueryStringValue("IsAccount");
            #endregion

            EyouSoft.BLL.StatisticStructure.StatAllOut OutlayBll = new EyouSoft.BLL.StatisticStructure.StatAllOut(SiteUserInfo);

            #region 查询实体赋值
            EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic SearchModel = new EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic();
            SearchModel.LeaveDateStart = StartDate;
            SearchModel.LeaveDateEnd   = EndDate;
            SearchModel.CompanyId      = this.SiteUserInfo.CompanyID;
            SearchModel.SaleIds        = Utils.GetIntArray(OperatorId, ",");
            if (RouteAreaName != "" && RouteAreaName != "0")
            {
                SearchModel.AreaId = int.Parse(RouteAreaName);
            }

            if (TourType != "" && TourType != "-1")
            {
                SearchModel.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType) int.Parse(TourType);
            }
            SearchModel.BuyCompanyName = CompanyName;
            #endregion

            #region 表单初始化
            this.txtStartDate.Value = StartDate.HasValue ? StartDate.Value.ToString("yyyy-MM-dd") : new DateTime(DateTime.Now.Year, 1, 1).ToString("yyyy-MM-dd");
            this.txtEndDate.Value   = EndDate.HasValue ? EndDate.Value.ToString("yyyy-MM-dd") : new DateTime(DateTime.Now.Year, 12, 31).ToString("yyyy-MM-dd");
            if (TourType != "")
            {
                this.TourTypeList1.TourType = int.Parse(TourType);
            }
            if (RouteAreaName != "")
            {
                this.RouteAreaList1.RouteAreaId = int.Parse(RouteAreaName);
            }
            this.SelectOperator.OperName = OperatorName;
            this.SelectOperator.OperId   = OperatorId;
            this.txt_com.Value           = CompanyName;
            if (isAccount == "-1")
            {
                SearchModel.IsAccount = null;
            }
            else if (isAccount == "0")
            {
                SearchModel.IsAccount = true;
            }
            else if (isAccount == "1")
            {
                SearchModel.IsAccount = false;
            }
            BindSum(SearchModel);
            if (isAccount != "")
            {
                this.drpIsAccount.SelectedValue = isAccount;
            }
            #endregion

            //调用查询方法 获取列表
            list = OutlayBll.GetList(PageSize, PageIndex, ref RecordCount, SearchModel);
            if (Utils.GetInt(Request.QueryString["IsCartogram"], 0) == 1)//异步请求统计图时不执行列表绑定
            {
                return;
            }

            #region 绑定列表
            if (list != null && list.Count != 0)
            {
                this.tbl_ExportPage.Visible            = true;
                this.crp_OutDepartStatiList.DataSource = list;
                this.crp_OutDepartStatiList.DataBind();
                BindPage();//绑定分页
            }
            else
            {
                this.tbl_ExportPage.Visible           = false;
                this.crp_OutDepartStatiList.EmptyText = "<tr bgcolor='#e3f1fc'><td colspan='4' id=\"EmptyData\" height='50px' align='center'>暂时没有数据!</td></tr>";
            }
            #endregion
        }
Beispiel #6
0
        /// <summary>
        /// 分页获取收入明细列表
        /// </summary>
        /// <param name="PageSize">每页显示条数</param>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="RecordCount">总记录数</param>
        /// <param name="queryInfo">查询实体</param>
        /// <param name="haveUserIds">用户Id集合,半角逗号分割</param>
        /// <returns></returns>
        public IList <EyouSoft.Model.StatisticStructure.StatAllIncomeList> GetList(int PageSize, int PageIndex, ref int RecordCount, EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo, string haveUserIds)
        {
            if (queryInfo == null || queryInfo.CompanyId <= 0)
            {
                return(null);
            }

            IList <EyouSoft.Model.StatisticStructure.StatAllIncomeList> list = new List <EyouSoft.Model.StatisticStructure.StatAllIncomeList>();

            #region 构造分页存储过程参数

            string        tableName  = "view_StatAllIncomeStatement";
            string        primaryKey = "salerid";
            string        orderByStr = string.Empty;
            StringBuilder strfield   = new StringBuilder();
            strfield.Append(" sum(totalamount) as TotalAmount,sum(hascheckamount) as AccountAmount,salerid,(select contactname from tbl_companyuser as a where a.id=view_StatAllIncomeStatement.salerid) as SalerName");
            string Query = this.GetSqlWhereByQuery(queryInfo, haveUserIds, ref orderByStr);

            #endregion

            using (IDataReader dr = DbHelper.ExecuteReaderIsGroupBy(this._db, PageSize, PageIndex, ref RecordCount, tableName, primaryKey, strfield.ToString(), Query, orderByStr, true))
            {
                while (dr.Read())
                {
                    EyouSoft.Model.StatisticStructure.StatAllIncomeList model = new EyouSoft.Model.StatisticStructure.StatAllIncomeList();
                    if (!dr.IsDBNull(dr.GetOrdinal("SalerId")))
                    {
                        model.OperatorId = dr.GetInt32(dr.GetOrdinal("SalerId"));
                    }
                    if (!dr.IsDBNull(dr.GetOrdinal("SalerName")))
                    {
                        model.OperatorName = dr[dr.GetOrdinal("SalerName")].ToString();
                    }
                    if (!dr.IsDBNull(dr.GetOrdinal("AccountAmount")))
                    {
                        model.AccountAmount = dr.GetDecimal(dr.GetOrdinal("AccountAmount"));
                    }
                    if (!dr.IsDBNull(dr.GetOrdinal("TotalAmount")))
                    {
                        model.TotalAmount = dr.GetDecimal(dr.GetOrdinal("TotalAmount"));
                    }
                    list.Add(model);
                    model = null;
                }
            }
            return(list);
        }
Beispiel #7
0
        /// <summary>
        /// 根据查询条件生成SqlWhere语句
        /// </summary>
        /// <param name="queryInfo">查询条件实体</param>
        /// <param name="orderByStr">排序字符串</param>
        /// <param name="haveUserIds">用户Id集合,半角逗号分割</param>
        /// <returns>SqlWhere语句</returns>
        private string GetSqlWhereByQuery(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo
                                          , string haveUserIds, ref string orderByStr)
        {
            if (queryInfo == null || queryInfo.CompanyId <= 0)
            {
                return(" group by salerid ");
            }

            StringBuilder Query = new StringBuilder();

            Query.AppendFormat(" CompanyId={0} ", queryInfo.CompanyId);

            orderByStr = queryInfo.OrderIndex == 0 ? " salerid asc" : " salerid desc ";

            EyouSoft.DAL.TourStructure.TourOrder ConvertDal = new EyouSoft.DAL.TourStructure.TourOrder();
            if (queryInfo.AreaId > 0)
            {
                Query.AppendFormat(" AND areaid={0} ", queryInfo.AreaId);
            }
            if (queryInfo.SaleIds != null && queryInfo.SaleIds.Length > 0)
            {
                string salerIds = ConvertDal.ConvertIntArrayTostring(queryInfo.SaleIds);
                Query.AppendFormat(" AND SalerId in({0})", salerIds);
            }
            if (queryInfo.BuyCompanyId != null && queryInfo.BuyCompanyId.Length > 0)
            {
                string BuyCompanyIds = ConvertDal.ConvertIntArrayTostring(queryInfo.BuyCompanyId);
                Query.AppendFormat(" AND BuyCompanyId in({0})", BuyCompanyIds);
            }
            if (!string.IsNullOrEmpty(queryInfo.BuyCompanyName))
            {
                Query.AppendFormat(" AND BuyCompanyName LIKE '%{0}%' ", queryInfo.BuyCompanyName);
            }
            if (queryInfo.TourType.HasValue)
            {
                Query.AppendFormat(" AND TourType={0}", (int)queryInfo.TourType.Value);
            }
            if (queryInfo.LeaveDateStart.HasValue)
            {
                Query.AppendFormat(" AND DATEDIFF(DAY,'{0}',LeaveDate)>=0", queryInfo.LeaveDateStart);
            }
            if (queryInfo.LeaveDateEnd.HasValue)
            {
                Query.AppendFormat(" AND DATEDIFF(DAY,LeaveDate,'{0}')>=0", queryInfo.LeaveDateEnd);
            }

            if (queryInfo.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计有效订单)
            {
                Query.AppendFormat(" AND OrderState in ({0},{1},{2})", (int)Model.EnumType.TourStructure.OrderState.未处理, (int)Model.EnumType.TourStructure.OrderState.已成交, (int)Model.EnumType.TourStructure.OrderState.已留位);
            }
            else if (queryInfo.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计确认成交订单)
            {
                Query.AppendFormat(" AND OrderState = {0} ", (int)Model.EnumType.TourStructure.OrderState.已成交);
            }

            if (queryInfo.IsAccount.HasValue)
            {
                if (queryInfo.IsAccount.Value)
                {
                    Query.Append(" and TotalAmount <> 0 and TotalAmount = HasCheckAmount ");// + NotCheckAmount
                }
                else
                {
                    Query.Append(" and TotalAmount <> 0 and TotalAmount <> HasCheckAmount ");//+ + NotCheckAmount
                }
            }

            if (!string.IsNullOrEmpty(haveUserIds))
            {
                Query.AppendFormat(" and TourOperatorId in ({0}) ", haveUserIds);
            }

            Query.Append(" group by salerid  ");

            return(Query.ToString());
        }
Beispiel #8
0
        /// <summary>
        /// 根据查询实体生成Where子句
        /// </summary>
        /// <param name="model">查询实体</param>
        /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param>
        /// <param name="strOrder">排序语句</param>
        /// <returns>Where子句</returns>
        private string GetSqlWhere(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic model, string HaveUserIds
                                   , ref string strOrder)
        {
            if (model == null)
            {
                return(string.Empty);
            }

            StringBuilder strSqlWhere = new StringBuilder();
            string        strIds      = string.Empty;

            if (model.CompanyId > 0)
            {
                strSqlWhere.AppendFormat(" and SellCompanyId = {0} ", model.CompanyId);
            }
            if (model.TourType.HasValue)
            {
                strSqlWhere.AppendFormat(" and TourClassId = {0} ", (int)model.TourType.Value);
            }
            if (model.SaleIds != null && model.SaleIds.Length > 0)
            {
                strIds = string.Empty;
                foreach (int i in model.SaleIds)
                {
                    if (i <= 0)
                    {
                        continue;
                    }

                    strIds += i.ToString() + ",";
                }
                strIds = strIds.Trim(',');
                if (!string.IsNullOrEmpty(strIds))
                {
                    strSqlWhere.AppendFormat(" and SalerId in ({0}) ", strIds);
                }
            }
            if (!string.IsNullOrEmpty(HaveUserIds))
            {
                strSqlWhere.AppendFormat(" and ViewOperatorId in ({0}) ", HaveUserIds);
            }
            if (model.LeaveDateStart.HasValue)
            {
                strSqlWhere.AppendFormat(" and LeaveDate>='{0}' ", model.LeaveDateStart.Value);
            }
            if (model.LeaveDateEnd.HasValue)
            {
                strSqlWhere.AppendFormat(" and LeaveDate<='{0}' ", model.LeaveDateEnd.Value.AddDays(1).AddMilliseconds(-1));
            }
            if (model.AreaId > 0)
            {
                strSqlWhere.AppendFormat(" and AreaId = {0} ", model.AreaId);
            }

            switch (model.OrderIndex)
            {
            case 0:
                strOrder = " SalerId asc ";
                break;

            case 1:
                strOrder = " SalerId desc ";
                break;
            }

            return(strSqlWhere.ToString());
        }
Beispiel #9
0
        /// <summary>
        /// 获取帐龄分析统计
        /// </summary>
        /// <param name="model">帐龄分析查询实体</param>
        /// <returns></returns>
        public IList <EyouSoft.Model.StatisticStructure.AccountAgeStatistic> GetAccountAgeStatistic(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic model, string HaveUserIds)
        {
            IList <EyouSoft.Model.StatisticStructure.AccountAgeStatistic> list = new List <EyouSoft.Model.StatisticStructure.AccountAgeStatistic>();
            string        strOrder = string.Empty;
            StringBuilder strSql   = new StringBuilder(" select ");

            strSql.Append(" SalerId, ");
            strSql.Append(" (select ContactName from tbl_CompanyUser where tbl_CompanyUser.id = tbl_TourOrder.SalerId and tbl_CompanyUser.isdelete = '0') as SalerName, ");
            strSql.Append(" sum(FinanceSum - HasCheckMoney) as ArrearageSum, ");
            strSql.Append(" min(IssueTime) as MaxArrearageTime ");
            strSql.Append(" from tbl_TourOrder ");
            //strSql.AppendFormat(" where IsDelete = '0' and (FinanceSum - HasCheckMoney - NotCheckMoney) > 0 and SalerId > 0 and exists (select 1 from tbl_CompanyUser where tbl_CompanyUser.isdelete = '0') ");
            strSql.AppendFormat(" where IsDelete = '0' and (FinanceSum - HasCheckMoney) <> 0 and SalerId > 0 ");
            strSql.AppendFormat(" {0} ", this.GetSqlWhere(model, HaveUserIds, ref strOrder));
            strSql.Append(" group by SalerId ");
            if (!string.IsNullOrEmpty(strOrder))
            {
                strSql.AppendFormat(" order by {0} ", strOrder);
            }

            DbCommand dc = _db.GetSqlStringCommand(strSql.ToString());

            using (IDataReader dr = DbHelper.ExecuteReader(dc, _db))
            {
                EyouSoft.Model.StatisticStructure.AccountAgeStatistic tmpModel = null;
                while (dr.Read())
                {
                    tmpModel = new EyouSoft.Model.StatisticStructure.AccountAgeStatistic();
                    if (!dr.IsDBNull(0))
                    {
                        tmpModel.SalesClerk            = new EyouSoft.Model.StatisticStructure.StatisticOperator();
                        tmpModel.SalesClerk.OperatorId = dr.GetInt32(0);
                        if (!dr.IsDBNull(1))
                        {
                            tmpModel.SalesClerk.OperatorName = dr.GetString(1);
                        }
                    }
                    if (!dr.IsDBNull(2))
                    {
                        tmpModel.ArrearageSum = dr.GetDecimal(2);
                    }
                    if (!dr.IsDBNull(3))
                    {
                        tmpModel.MaxArrearageTime = dr.GetDateTime(3);
                    }

                    list.Add(tmpModel);
                }
            }

            return(list);
        }
Beispiel #10
0
        private void InitIncDepartAreaStaticlist()
        {
            #region 获取参数
            PageIndex         = Utils.GetInt(Request.QueryString["Page"], 1);
            TourType          = Utils.GetQueryStringValue("TourType");
            RouteArea         = Utils.GetQueryStringValue("RouteArea");
            Salser            = Utils.GetQueryStringValue("Salser");
            SalserID          = Utils.GetQueryStringValue("SalserId");
            LeaveTourStarDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("LeaveTourStartDate"));
            LeaveTourEndDate  = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("LeaveTourEndDate"));
            CompanyName       = Utils.GetQueryStringValue("CompanyName");
            isAccount         = Utils.GetQueryStringValue("IsAccount");

            #endregion

            //私有变量
            EyouSoft.BLL.StatisticStructure.StatAllIncome IncomeBLL = new EyouSoft.BLL.StatisticStructure.StatAllIncome(SiteUserInfo);

            #region 实体赋值
            EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic model = new EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic();
            model.CompanyId = this.SiteUserInfo.CompanyID;
            model.SaleIds   = Utils.GetIntArray(SalserID, ",");
            if (TourType != "" && TourType != "-1")
            {
                model.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType) int.Parse(TourType);
            }
            if (RouteArea != "" && RouteArea != "0")
            {
                model.AreaId = int.Parse(RouteArea);
            }
            //设置查询model的ComputeOrderType值
            EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType?computerOrderType = new EyouSoft.BLL.CompanyStructure.CompanySetting().GetComputeOrderType(SiteUserInfo.CompanyID);
            if (computerOrderType.HasValue)
            {
                model.ComputeOrderType = computerOrderType.Value;
            }
            model.LeaveDateStart = LeaveTourStarDate;
            model.LeaveDateEnd   = LeaveTourEndDate;
            model.BuyCompanyName = CompanyName;
            if (isAccount == "-1")
            {
                model.IsAccount = null;
            }
            else if (isAccount == "0")
            {
                model.IsAccount = true;
            }
            else if (isAccount == "1")
            {
                model.IsAccount = false;
            }
            BindSum(model);
            #endregion

            #region 表单初始化
            if (TourType != "")
            {
                this.TourTypeList1.TourType = int.Parse(TourType);
            }
            if (RouteArea != "")
            {
                this.RouteAreaList1.RouteAreaId = int.Parse(RouteArea);
            }
            this.SelectSalser.OperName      = Salser;
            this.SelectSalser.OperId        = SalserID;
            this.txtLeaveTourStarTime.Value = LeaveTourStarDate.HasValue ? LeaveTourStarDate.Value.ToString("yyyy-MM-dd") : "";
            this.txtLeaveTourEndTime.Value  = LeaveTourEndDate.HasValue ? LeaveTourEndDate.Value.ToString("yyyy-MM-dd") : "";
            this.txt_com.Value = CompanyName;
            if (isAccount != "")
            {
                this.drpIsAccount.SelectedValue = isAccount;
            }

            #endregion

            //调用底层方法
            IncomeDepartList = IncomeBLL.GetList(PageSize, PageIndex, ref RecordCount, model);
            //如果为统计图异步请求时,不执行列表绑定
            if (Utils.GetInt(Request.QueryString["IsCartogram"], 0) == 1)
            {
                return;
            }

            #region 列表绑定
            if (IncomeDepartList != null && IncomeDepartList.Count != 0)
            {
                this.tbl_ExportPage.Visible          = true;
                this.crp_IncDepartStaList.DataSource = IncomeDepartList;
                this.crp_IncDepartStaList.DataBind();
                BindPage();//绑定分页
            }
            else
            {
                this.tbl_ExportPage.Visible         = false;
                this.crp_IncDepartStaList.EmptyText = "<tr bgcolor='#e3f1fc'><td colspan='4' id=\"EmptyData\" height='50px' align='center'>暂时没有数据!</td></tr>";
            }
            #endregion
        }
Beispiel #11
0
        private void InitAgeAreaStaList()
        {
            #region 获取参数
            PageIndex     = Utils.GetInt(Request.QueryString["Page"], 1);
            TourType      = Utils.GetQueryStringValue("tourType");
            RouteAreaName = Utils.GetQueryStringValue("routeAreaName");
            Salser        = Utils.GetQueryStringValue("Salser");
            SalerId       = Utils.GetQueryStringValue("SalserId");
            StartDate     = Utils.GetDateTimeNullable(Request.QueryString["startDate"], new DateTime(DateTime.Now.Year, 1, 1));
            EndDate       = Utils.GetDateTimeNullable(Request.QueryString["endDate"], new DateTime(DateTime.Now.Year, 12, 31));
            #endregion

            //调用底层方法
            EyouSoft.BLL.StatisticStructure.AccountAgeStatistic        AgeStaBll = new EyouSoft.BLL.StatisticStructure.AccountAgeStatistic(this.SiteUserInfo);
            EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic model     = new EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic();

            #region 查询实体赋值
            if (TourType != "" && TourType != "-1")
            {
                model.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType) int.Parse(TourType);
            }
            model.LeaveDateStart = StartDate;
            model.LeaveDateEnd   = EndDate;
            if (RouteAreaName != "" && RouteAreaName != "0")
            {
                model.AreaId = int.Parse(RouteAreaName);
            }
            if (SalerId != "")
            {
                model.SaleIds = Utils.GetIntArray(SalerId, ",");
            }
            #endregion

            #region 初始化表单赋值

            txtStartDate.Value = StartDate.HasValue ? StartDate.Value.ToString("yyyy-MM-dd") : "";
            txtEndDate.Value   = EndDate.HasValue ? EndDate.Value.ToString("yyyy-MM-dd") : "";
            if (TourType != "")
            {
                this.TourTypeList1.TourType = int.Parse(TourType);
            }
            if (RouteAreaName != "")
            {
                this.RouteAreaList1.RouteAreaId = int.Parse(RouteAreaName);
            }
            this.SelectSalers.OperName = Salser;
            this.SelectSalers.OperId   = SalerId;
            #endregion

            #region 绑定列表
            list = EyouSoft.Common.Function.SelfExportPage.GetList <EyouSoft.Model.StatisticStructure.AccountAgeStatistic>(PageIndex, PageSize, out RecordCount, AgeStaBll.GetAccountAgeStatistic(model));
            if (list != null && list.Count != 0)
            {
                this.tbl_ExportPage.Visible             = true;
                this.crp_GetAgeDepartStaList.DataSource = list;
                this.crp_GetAgeDepartStaList.DataBind();
                BindPage();
            }
            else
            {
                this.tbl_ExportPage.Visible            = false;
                this.crp_GetAgeDepartStaList.EmptyText = "<tr bgcolor='#e3f1fc'><td id=\"EmptyData\" colspan='3' height='50px' align='center'>暂时没有数据!</td></tr>";
            }
            #endregion
        }
Beispiel #12
0
 /// <summary>
 /// 分页获取收入明细列表
 /// </summary>
 /// <param name="PageSize">每页显示条数</param>
 /// <param name="PageIndex">当前页码</param>
 /// <param name="RecordCount">总记录数</param>
 /// <param name="queryInfo">查询实体</param>
 /// <returns></returns>
 public IList <EyouSoft.Model.StatisticStructure.StatAllIncomeList> GetList(int PageSize, int PageIndex, ref int RecordCount, EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo)
 {
     return(idal.GetList(PageSize, PageIndex, ref RecordCount, queryInfo, HaveUserIds));
 }
Beispiel #13
0
        /// <summary>
        /// 分页获取支出明细列表(统计分析-支出对账单列表)
        /// </summary>
        /// <param name="PageSize">每页现实条数</param>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="RecordCount">总记录数</param>
        /// <param name="queryInfo">查询实体</param>
        /// <param name="haveUserIds">用户Id集合,半角逗号分割</param>
        /// <returns></returns>
        public IList <EyouSoft.Model.StatisticStructure.StatAllOutList> GetList(int PageSize, int PageIndex, ref int RecordCount, EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo, string haveUserIds)
        {
            if (queryInfo == null || queryInfo.CompanyId <= 0)
            {
                return(null);
            }

            IList <EyouSoft.Model.StatisticStructure.StatAllOutList> list = new List <EyouSoft.Model.StatisticStructure.StatAllOutList>();

            #region 构造分页存储过程参数

            string        tableName  = "tbl_StatAllOut";
            string        primaryKey = "Id";
            string        orderByStr = string.Empty;
            StringBuilder strfield   = new StringBuilder();
            strfield.Append("OperatorId,");
            strfield.Append("sum(TotalAmount) as TotalAmount,");
            strfield.Append("sum(HasCheckAmount) as PaidAmount,");
            strfield.Append("(select ContactName from tbl_CompanyUser where id=tbl_StatAllOut.OperatorId) as OperatorName");
            string strWhere = this.GetSqlWhereByQuery(queryInfo, haveUserIds, ref orderByStr);

            #endregion

            using (IDataReader dr = DbHelper.ExecuteReaderIsGroupBy(this._db, PageSize, PageIndex, ref RecordCount, tableName, primaryKey, strfield.ToString(), strWhere, orderByStr, true))
            {
                while (dr.Read())
                {
                    EyouSoft.Model.StatisticStructure.StatAllOutList model = new EyouSoft.Model.StatisticStructure.StatAllOutList();
                    if (!dr.IsDBNull(dr.GetOrdinal("OperatorId")))
                    {
                        model.OperatorId = dr.GetInt32(dr.GetOrdinal("OperatorId"));
                    }
                    if (!dr.IsDBNull(dr.GetOrdinal("OperatorName")))
                    {
                        model.OperatorName = dr[dr.GetOrdinal("OperatorName")].ToString();
                    }
                    if (!dr.IsDBNull(dr.GetOrdinal("PaidAmount")))
                    {
                        model.PaidAmount = dr.GetDecimal(dr.GetOrdinal("PaidAmount"));
                    }
                    if (!dr.IsDBNull(dr.GetOrdinal("TotalAmount")))
                    {
                        model.TotalAmount = dr.GetDecimal(dr.GetOrdinal("TotalAmount"));
                    }
                    list.Add(model);
                    model = null;
                }
            }
            return(list);
        }
Beispiel #14
0
        /// <summary>
        /// 根据查询条件生成SqlWhere语句
        /// </summary>
        /// <param name="queryInfo">查询条件实体</param>
        /// <param name="orderByStr">排序字符串</param>
        /// <param name="haveUserIds">用户Id集合,半角逗号分割</param>
        /// <returns>SqlWhere语句</returns>
        private string GetSqlWhereByQuery(EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo, string haveUserIds, ref string orderByStr)
        {
            StringBuilder strWhere = new StringBuilder(" (ItemType=1 OR ItemType=2 OR ItemType=4) AND IsDelete='0' ");

            if (queryInfo != null)
            {
                orderByStr = queryInfo.OrderIndex == 0 ? " OperatorId asc" : " OperatorId desc ";

                //StringBuilder strAreaSql = new StringBuilder("select areaid from tbl_StatAllOut where  IsDelete='0' ");
                if (queryInfo.AreaId > 0)
                {
                    strWhere.AppendFormat(" and areaid={0} ", queryInfo.AreaId);
                }
                if (queryInfo.CompanyId > 0)
                {
                    strWhere.AppendFormat(" and CompanyId={0} ", queryInfo.CompanyId);
                }
                if (queryInfo.SaleIds != null && queryInfo.SaleIds.Length > 0)
                {
                    string SaleIds = string.Empty;
                    foreach (int i in queryInfo.SaleIds)
                    {
                        if (i > 0)
                        {
                            SaleIds += i.ToString() + ",";
                        }
                    }
                    SaleIds = SaleIds.TrimEnd(',');
                    strWhere.AppendFormat(" and OperatorId in({0})", SaleIds);
                }
                if (queryInfo.TourType.HasValue)
                {
                    strWhere.AppendFormat(" and TourType={0}", (int)queryInfo.TourType.Value);
                }

                if (queryInfo.BuyCompanyId != null && queryInfo.BuyCompanyId.Length > 0)
                {
                    string strBuyCompanyIds = string.Empty;
                    if (queryInfo.BuyCompanyId != null && queryInfo.BuyCompanyId.Length > 0)
                    {
                        foreach (int i in queryInfo.BuyCompanyId)
                        {
                            if (i > 0)
                            {
                                strBuyCompanyIds += i.ToString() + ",";
                            }
                        }
                    }
                    strBuyCompanyIds = strBuyCompanyIds.Trim(',');
                    if (!string.IsNullOrEmpty(strBuyCompanyIds))
                    {
                        strWhere.AppendFormat(" and SupplierId in ({0}) ", strBuyCompanyIds);
                    }
                }
                if (!string.IsNullOrEmpty(queryInfo.BuyCompanyName))
                {
                    strWhere.AppendFormat(" and SupplierName like '%{0}%' ", queryInfo.BuyCompanyName);
                }

                if (queryInfo.IsAccount.HasValue)
                {
                    if (queryInfo.IsAccount.Value)
                    {
                        strWhere.Append(" and TotalAmount <> 0 and TotalAmount = HasCheckAmount ");// + NotCheckAmount
                    }
                    else
                    {
                        strWhere.Append(" and TotalAmount <> 0 and TotalAmount <> HasCheckAmount ");// + NotCheckAmount
                    }
                }

                /*if (!string.IsNullOrEmpty(haveUserIds))
                 *  strWhere.AppendFormat(@" and (TourId is null and len(TourId) <> 36 and OperatorId in ({0}) or (TourId is not null and len(TourId) = 36 and TourId in (select TourId from tbl_Tour where tbl_Tour.OperatorId in ({0})))) ", haveUserIds);*/

                //strWhere.AppendFormat(" and areaid in({0})", strAreaSql.ToString());

                if (queryInfo.LeaveDateStart.HasValue || queryInfo.LeaveDateEnd.HasValue)
                {
                    strWhere.AppendFormat(" AND EXISTS(SELECT 1 FROM tbl_Tour AS A WHERE 1=1 AND A.TourId=tbl_StatAllOut.TourId ");
                    if (queryInfo.LeaveDateStart.HasValue)
                    {
                        strWhere.AppendFormat(" AND A.LeaveDate>='{0}' ", queryInfo.LeaveDateStart.Value);
                    }

                    if (queryInfo.LeaveDateEnd.HasValue)
                    {
                        strWhere.AppendFormat(" AND A.LeaveDate<='{0}' ", queryInfo.LeaveDateEnd.Value);
                    }
                    strWhere.AppendFormat(" ) ");
                }
            }
            strWhere.Append(" group by OperatorId  ");

            return(strWhere.ToString());
        }
Beispiel #15
0
        /// <summary>
        /// 分页获取支出明细列表(统计分析-支出对账单列表)
        /// </summary>
        /// <param name="PageSize">每页显示条数</param>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="RecordCount">总记录数</param>
        /// <param name="queryInfo">查询实体</param>
        /// <returns></returns>
        public IList <EyouSoft.Model.StatisticStructure.StatAllOutList> GetList(int PageSize, int PageIndex, ref int RecordCount, EyouSoft.Model.StatisticStructure.QueryAccountAgeStatistic queryInfo)
        {
            DateTime?_StartDate = null;
            DateTime?_EndDate   = null;

            if (queryInfo != null)
            {
                _StartDate = queryInfo.LeaveDateStart;
                _EndDate   = queryInfo.LeaveDateEnd;

                if (!queryInfo.LeaveDateEnd.HasValue)
                {
                    queryInfo.LeaveDateEnd = new DateTime(DateTime.Now.Year + 1, 1, 1).AddMilliseconds(-1);
                }
                else
                {
                    queryInfo.LeaveDateEnd = queryInfo.LeaveDateEnd.Value.AddDays(1).AddMilliseconds(-1);
                }

                if (!queryInfo.LeaveDateStart.HasValue)
                {
                    queryInfo.LeaveDateStart = new DateTime(DateTime.Now.Year, 1, 1);
                }
            }

            var items = idal.GetList(PageSize, PageIndex, ref RecordCount, queryInfo, HaveUserIds);

            queryInfo.LeaveDateEnd   = _EndDate;
            queryInfo.LeaveDateStart = _StartDate;

            return(items);
        }