Пример #1
0
        /// <summary>
        /// 获取利润--时间统计
        /// </summary>
        /// <param name="model">利润统计查询实体</param>
        /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param>
        /// <returns></returns>
        public IList <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic> GetEarningsTimeStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds)
        {
            IList <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic> list = new List <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic>();
            string        strOrder = string.Empty;
            string        strWhere = this.GetSqlWhere(model, HaveUserIds, ref strOrder);
            StringBuilder strSql   = new StringBuilder(" select ");

            strSql.Append(" year(LeaveDate) as CurrYear,month(LeaveDate) as CurrMonth, ");
            strSql.Append(" count(TourId) as TourCount, ");
            strSql.Append(" sum(TotalAllExpenses) as TotalAllExpenses, ");
            strSql.Append(" 0 as GrossProfit, ");
            strSql.Append(" sum(DistributionAmount) as DistributionAmount, ");
            strSql.AppendFormat(" (select Orders from View_EarningsStatistic as tmpVes where year(tmpVes.LeaveDate) = year(ves.LeaveDate) and month(tmpVes.LeaveDate) = month(ves.LeaveDate) and len(Orders) > 1 {0} for xml auto,root('root')) as Orders, ", strWhere.Replace("ves.", "tmpVes."));
            strSql.Append(" Sum(TotalOtherIncome) as TotalOtherIncome");
            strSql.Append(" from View_EarningsStatistic as ves ");
            strSql.AppendFormat(" where IsDelete = '0' ");
            strSql.AppendFormat(" {0} ", strWhere);
            strSql.Append(" group by year(LeaveDate),month(LeaveDate) ");
            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.EarningsTimeStatistic tmpModel = null;
                decimal FinanceSum   = 0;
                int     PeopleNumber = 0;
                while (dr.Read())
                {
                    tmpModel = new EyouSoft.Model.StatisticStructure.EarningsTimeStatistic();
                    if (!dr.IsDBNull(0))
                    {
                        tmpModel.CurrYear = dr.GetInt32(0);
                    }
                    if (!dr.IsDBNull(1))
                    {
                        tmpModel.CurrMonth = dr.GetInt32(1);
                    }
                    if (!dr.IsDBNull(2))
                    {
                        tmpModel.TourNum = dr.GetInt32(2);
                    }
                    if (!dr.IsDBNull(3))
                    {
                        tmpModel.GrossOut = dr.GetDecimal(3);
                    }
                    if (!dr.IsDBNull(4))
                    {
                        tmpModel.TourGross = 0;//dr.GetDecimal(4);
                    }
                    if (!dr.IsDBNull(5))
                    {
                        tmpModel.TourShare = dr.GetDecimal(5);
                    }

                    FinanceSum   = 0;
                    PeopleNumber = 0;
                    IList <Model.StatisticStructure.StatisticOperator> tmp = null;
                    if (!dr.IsDBNull(6))
                    {
                        this.GetOrderInfoByXML(dr.GetString(6), model, ref FinanceSum, ref PeopleNumber, ref tmp, false);
                    }

                    tmpModel.GrossIncome   = FinanceSum; //此处只计算团款收入
                    tmpModel.TourPeopleNum = PeopleNumber;
                    if (!dr.IsDBNull(7))
                    {
                        tmpModel.GrossIncome += dr.GetDecimal(7); //此处加上杂费收入
                    }
                    //团队毛利=总收入-总支出
                    tmpModel.TourGross = tmpModel.GrossIncome - tmpModel.GrossOut;

                    list.Add(tmpModel);
                }
            }

            return(list);
        }
Пример #2
0
        /// <summary>
        /// 获取利润--时间统计
        /// </summary>
        /// <param name="model">利润统计查询实体</param>
        /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param>
        /// <returns></returns>
        public IList <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic> GetEarningsTimeStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds)
        {
            IList <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic> items = new List <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic>();

            StringBuilder cmdText = new StringBuilder();

            #region SQL
            cmdText.Append(" SELECT YEAR(A.LeaveDate) AS Year ");
            cmdText.Append(" ,MONTH(A.LeaveDate) AS Month ");
            cmdText.Append(" ,COUNT(A.TourId) AS TuanDuiShu ");
            cmdText.Append(" ,SUM(A.TotalAllExpenses) AS ZhiChuJinE ");
            cmdText.Append(" ,SUM(A.DistributionAmount) AS LiRunFenPeiJinE ");

            if (model.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计确认成交订单)
            {
                cmdText.Append(" ,SUM(A.TotalOtherIncome+A.ChengJiaoJinE) AS ShouRuJinE ");
                cmdText.Append(" ,SUM(A.ChengJiaoRenShu) AS RenShu ");
            }
            else
            {
                cmdText.Append(" ,SUM(A.TotalOtherIncome+A.YouXiaoJinE) AS ShouRuJinE ");
                cmdText.Append(" ,SUM(A.YouXiaoRenShu) AS RenShu ");
            }

            cmdText.Append(" FROM [View_EarningsStatistic] AS A ");
            cmdText.AppendFormat(" WHERE A.CompanyId={0} ", model.CompanyId);

            if (!string.IsNullOrEmpty(HaveUserIds))
            {
                cmdText.AppendFormat(" AND A.OperatorId IN({0}) ", HaveUserIds);
            }

            cmdText.Append(GetSQL(model));

            cmdText.Append(" GROUP BY YEAR(A.LeaveDate),MONTH(A.LeaveDate) ");

            if (model.OrderIndex == 6)
            {
                cmdText.Append(" ORDER BY Year ASC,Month ASC ");
            }
            else
            {
                cmdText.Append(" ORDER BY Year DESC,Month DESC ");
            }
            #endregion

            DbCommand cmd = _db.GetSqlStringCommand(cmdText.ToString());
            using (IDataReader rdr = DbHelper.ExecuteReader(cmd, _db))
            {
                while (rdr.Read())
                {
                    var item = new EyouSoft.Model.StatisticStructure.EarningsTimeStatistic();

                    item.CurrMonth = rdr.GetInt32(rdr.GetOrdinal("Month"));
                    item.CurrYear  = rdr.GetInt32(rdr.GetOrdinal("Year"));
                    if (!rdr.IsDBNull(rdr.GetOrdinal("ShouRuJinE")))
                    {
                        item.GrossIncome = rdr.GetDecimal(rdr.GetOrdinal("ShouRuJinE"));
                    }
                    if (!rdr.IsDBNull(rdr.GetOrdinal("ZhiChuJinE")))
                    {
                        item.GrossOut = rdr.GetDecimal(rdr.GetOrdinal("ZhiChuJinE"));
                    }
                    if (!rdr.IsDBNull(rdr.GetOrdinal("TuanDuiShu")))
                    {
                        item.TourNum = rdr.GetInt32(rdr.GetOrdinal("TuanDuiShu"));
                    }
                    if (!rdr.IsDBNull(rdr.GetOrdinal("LiRunFenPeiJinE")))
                    {
                        item.TourShare = rdr.GetDecimal(rdr.GetOrdinal("LiRunFenPeiJinE"));
                    }
                    if (!rdr.IsDBNull(rdr.GetOrdinal("RenShu")))
                    {
                        item.TourPeopleNum = rdr.GetInt32(rdr.GetOrdinal("RenShu"));
                    }
                    item.TourGross = item.GrossIncome - item.GrossOut;

                    items.Add(item);
                }
            }

            return(items);
        }