Beispiel #1
0
        /// <summary>
        /// 获取每行初始Model并获得初始数据
        /// </summary>
        /// <param name="timePeriod"></param>
        /// <param name="usrType"></param>
        /// <param name="regSource"></param>
        /// <returns></returns>
        public RetentionList GetInitialAccidByCondition(TimePeriodModel timePeriod, string usrName)
        {
            StringBuilder strSql    = new StringBuilder();
            StringBuilder strSource = new StringBuilder();

            RetentionList rowFirstObj   = new RetentionList();
            int           initialCount  = 0;
            List <int>    initAccidList = new List <int>();

            int        firstDayLog    = 0;
            List <int> firstDayAccids = new List <int>();

            if (usrName == "all")
            {
                strSql.Append("select count(distinct accid) from Sys_VisitInfo where insertTime between @stTime and @edTime and insertName<>'系统' ");
                initialCount = DapperHelper.ExecuteScalar <int>(strSql.ToString(),
                                                                new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate });
                strSql.Clear();
                strSql.Append(
                    "select distinct accid from Sys_VisitInfo where insertTime between @stTime and @edTime and insertName<>'系统'");
                initAccidList =
                    DapperHelper.Query <int>(strSql.ToString(),
                                             new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate }).ToList();
            }
            else
            {
                strSql.Append(
                    "select count(distinct accid) from Sys_VisitInfo where insertTime between @stTime and @edTime and insertName='" +
                    usrName + "' ");
                initialCount = DapperHelper.ExecuteScalar <int>(strSql.ToString(),
                                                                new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate });
                strSql.Clear();
                strSql.Append(
                    "select distinct accid from Sys_VisitInfo where insertTime between @stTime and @edTime and insertName='" +
                    usrName + "' ");
                initAccidList =
                    DapperHelper.Query <int>(strSql.ToString(),
                                             new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate }).ToList();
            }


            //添加一个新的留存率日期行对象
            rowFirstObj.Date         = timePeriod.TimePeriod;
            rowFirstObj.InitialAccId = initAccidList;
            rowFirstObj.RowMark      = CommonLib.Helper.Md5Hash(rowFirstObj.Date);
            rowFirstObj.InitialCount = initialCount;
            DailyRetention monthlyRetention = new DailyRetention();

            //首日注册用户登录量
            //拼接初始化的AccId列表,为零则返回null
            string accids = "(";

            foreach (int accid in rowFirstObj.InitialAccId)
            {
                accids += accid.ToString() + ",";
            }
            if (accids.Length > 1)
            {
                accids = accids.Substring(0, accids.LastIndexOf(',')) + ")";
            }
            else
            {
                return(null);
            }

            strSql.Clear();
            strSql.Append("select count(distinct accountid) from i200.dbo.T_LOG where OperDate between @bgTime and @edTime and accountid in " + accids + ";");
            firstDayLog = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { bgTime = timePeriod.StDate, edTime = timePeriod.EdDate });

            strSql.Clear();
            strSql.Append("select distinct accountid from i200.dbo.T_LOG where OperDate between @bgTime and @edTime and accountid in " + accids + ";");
            firstDayAccids =
                DapperHelper.Query <int>(strSql.ToString(), new { bgTime = timePeriod.StDate, edTime = timePeriod.EdDate })
                .ToList();

            //添加首日初始化数据,日期标号为0
            monthlyRetention.DayNum    = 0;
            monthlyRetention.NowActive = firstDayLog;
            monthlyRetention.NoActive  = initialCount;

            if (initialCount != 0)
            {
                monthlyRetention.Ratio = firstDayLog * 100 / initialCount;
            }
            else
            {
                monthlyRetention.Ratio = 0;
            }
            monthlyRetention.DayAccids = CommonLib.Helper.JsonSerializeObject(firstDayAccids);

            string mdString = firstDayLog.ToString() + monthlyRetention.DayNum.ToString() +
                              monthlyRetention.Ratio.ToString();            //定制列标记符,(规则为记录数+天数标识+比例数值)转MD5

            monthlyRetention.ColumnMark = CommonLib.Helper.Md5Hash(mdString);

            rowFirstObj.DataList.Add(monthlyRetention);

            return(rowFirstObj);
        }
        /// <summary>
        /// 获取每行初始Model并获得初始数据
        /// </summary>
        /// <param name="timePeriod"></param>
        /// <param name="usrType"></param>
        /// <param name="regSource"></param>
        /// <returns></returns>
        public RetentionList GetInitialAccidByCondition(TimePeriodModel timePeriod, string usrType, string regSource, string agent)
        {
            StringBuilder strSql    = new StringBuilder();
            StringBuilder strSource = new StringBuilder();

            RetentionList rowFirstObj   = new RetentionList();
            int           initialCount  = 0;
            List <int>    initAccidList = new List <int>();

            int        firstDayLog    = 0;
            List <int> firstDayAccids = new List <int>();

            switch (usrType)
            {
            case "regUser":
                if (agent == "all")
                {
                    strSql.Append("select count(*) from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1 and AgentId<>0 ");
                }
                else if (agent == "")
                {
                    strSql.Append("select count(*) from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1");
                }
                else
                {
                    strSql.Append("select count(*) from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1 and AgentId=" + Convert.ToInt32(agent) + " ");
                }

                if (regSource != "all")
                {
                    strSql.Append(" and Remark='" + regSource + "';");
                }
                else
                {
                    strSql.Append(";");
                }
                initialCount = DapperHelper.ExecuteScalar <int>(strSql.ToString(),
                                                                new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate });

                strSql.Clear();

                if (agent == "all")
                {
                    strSql.Append("select Id from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1 and AgentId<>0 ");
                }
                else if (agent == "")
                {
                    strSql.Append("select Id from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1");
                }
                else
                {
                    strSql.Append("select Id from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1 and AgentId=" + Convert.ToInt32(agent) + " ");
                }

                if (regSource != "all")
                {
                    strSql.Append(" and Remark='" + regSource + "';");
                }
                else
                {
                    strSql.Append(";");
                }
                initAccidList =
                    DapperHelper.Query <int>(strSql.ToString(),
                                             new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate }).ToList();
                break;

            case "paidUser":
                string where = "";
                if (agent == "all")
                {
                    where += " a.AgentId<>0 ";
                }
                else if (agent == "")
                {
                    where += " 1=1 ";
                }
                else
                {
                    where += " a.AgentId=" + Convert.ToInt32(agent) + " ";
                }

                strSql.Append("select count(distinct accId) from i200.dbo.T_OrderInfo o left join i200.dbo.T_Account a on o.accId=a.ID ");

                if (regSource != "all")
                {
                    strSql.Append(" where a.Remark='" + regSource + "' and o.transactionDate between @stTime and @edTime and " + where + ";");
                }
                else
                {
                    strSql.Append(" where transactionDate between @stTime and @edTime and " + where + ";");
                }
                initialCount = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate });

                strSql.Clear();

                strSql.Append("select accId from i200.dbo.T_OrderInfo o left join i200.dbo.T_Account a on o.accId=a.ID ");

                if (regSource != "all")
                {
                    strSql.Append(" where a.Remark='" + regSource + "' and o.transactionDate between @stTime and @edTime  and " + where + " group by o.accId;");
                }
                else
                {
                    strSql.Append(" where transactionDate between @stTime and @edTime and " + where + " group by accId;");
                }
                initAccidList = DapperHelper.Query <int>(strSql.ToString(), new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate }).ToList();
                break;
            }

            //添加一个新的留存率日期行对象
            rowFirstObj.Date         = timePeriod.TimePeriod;
            rowFirstObj.InitialAccId = initAccidList;
            rowFirstObj.RowMark      = CommonLib.Helper.Md5Hash(rowFirstObj.Date);
            rowFirstObj.InitialCount = initialCount;
            DailyRetention monthlyRetention = new DailyRetention();

            //首日注册用户登录量
            //拼接初始化的AccId列表,为零则返回null
            string accids = "(";

            foreach (int accid in rowFirstObj.InitialAccId)
            {
                accids += accid.ToString() + ",";
            }
            if (accids.Length > 1)
            {
                accids = accids.Substring(0, accids.LastIndexOf(',')) + ")";
            }
            else
            {
                return(null);
            }

            strSql.Clear();
            strSql.Append("select count(distinct accountid) from i200.dbo.T_LOG where OperDate between @bgTime and @edTime and accountid in " + accids + ";");
            firstDayLog = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { bgTime = timePeriod.StDate, edTime = timePeriod.EdDate });

            strSql.Clear();
            strSql.Append("select distinct accountid from i200.dbo.T_LOG where OperDate between @bgTime and @edTime and accountid in " + accids + ";");
            firstDayAccids =
                DapperHelper.Query <int>(strSql.ToString(), new { bgTime = timePeriod.StDate, edTime = timePeriod.EdDate })
                .ToList();

            //添加首日初始化数据,日期标号为0
            monthlyRetention.DayNum    = 0;
            monthlyRetention.NowActive = firstDayLog;

            if (initialCount != 0)
            {
                monthlyRetention.Ratio = firstDayLog * 100 / initialCount;
            }
            else
            {
                monthlyRetention.Ratio = 0;
            }
            monthlyRetention.DayAccids = CommonLib.Helper.JsonSerializeObject(firstDayAccids);

            string mdString = firstDayLog.ToString() + monthlyRetention.DayNum.ToString() +
                              monthlyRetention.Ratio.ToString();            //定制列标记符,(规则为记录数+天数标识+比例数值)转MD5

            monthlyRetention.ColumnMark = CommonLib.Helper.Md5Hash(mdString);

            rowFirstObj.DataList.Add(monthlyRetention);

            return(rowFirstObj);
        }