Пример #1
0
        /// <summary>
        /// 按键值获取统计数字列表
        /// </summary>
        /// <param name="keyName"></param>
        /// <param name="keyValue"></param>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List <PVKeyCountView> GetKeyCountList(string keyName, string keyValue, DateTime begin, DateTime end)
        {
            IDatabase    db  = Assistant.GetDatabases()["We7.CMS.Common"];
            SqlStatement sql = new SqlStatement();

            string sqlCommandTxt =
                @"SELECT  DISTINCT  [{1}],COUNT([{1}]) AS [pvcount] FROM [PageVisitorHistory]  {0} 
           GROUP BY [{1}] ORDER BY COUNT([{1}]) DESC ";
            string strWhere = " where 1=1  ";

            if (begin <= end)
            {
                if (begin != DateTime.MinValue)
                {
                    strWhere += "and  [VisitDate] > {0}BEGIN";
                    DataParameter dp = new DataParameter();
                    dp.ParameterName = db.DbDriver.Prefix + "BEGIN";
                    dp.DbType        = DbType.DateTime;
                    dp.SourceColumn  = "VisitDate";
                    dp.Value         = begin;

                    sql.Parameters.Add(dp);
                }
                if (end != DateTime.MaxValue)
                {
                    strWhere += " and [VisitDate] < {0}END";
                    DataParameter dp2 = new DataParameter();
                    dp2.ParameterName = db.DbDriver.Prefix + "END";
                    dp2.Value         = end.AddDays(1);
                    dp2.DbType        = DbType.DateTime;
                    dp2.SourceColumn  = "VisitDate";
                    sql.Parameters.Add(dp2);
                }
            }
            if (keyValue.Trim() != "")
            {
                strWhere += " and [{1}] like {0}KEY";
                DataParameter dp3 = new DataParameter();
                dp3.ParameterName = db.DbDriver.Prefix + "KEY";
                dp3.Value         = "%" + keyValue.Trim() + "%";
                dp3.DbType        = DbType.String;
                sql.Parameters.Add(dp3);
            }

            strWhere      = string.Format(strWhere, db.DbDriver.Prefix, keyName);
            sqlCommandTxt = string.Format(sqlCommandTxt, strWhere, keyName);
            sql.SqlClause = sqlCommandTxt;

            db.DbDriver.FormatSQL(sql);
            List <PVKeyCountView> list = new List <PVKeyCountView>();

            using (IConnection conn = db.CreateConnection())
            {
                DataTable dt    = conn.Query(sql);
                int       total = 0;
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        PVKeyCountView kv = new PVKeyCountView();
                        kv.KeyValue = dr[keyName].ToString();
                        kv.Count    = Int32.Parse(dr["pvcount"].ToString());
                        total      += kv.Count;
                        list.Add(kv);
                    }
                    foreach (PVKeyCountView v in list)
                    {
                        v.Percent = (double)v.Count / (double)total * 100;
                    }
                }
            }
            return(list);
        }
Пример #2
0
        /// <summary>
        /// 获取月访问统计
        /// </summary>
        /// <param name="channelID"></param>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <param name="orderKey"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public List <PVKeyCountView> GetStatisticsMonthsList
            (string channelID, DateTime begin, DateTime end, string orderKey, bool sort)
        {
            IDatabase    db  = Assistant.GetDatabases()["We7.CMS.Common"];
            SqlStatement sql = new SqlStatement();

            string sqlCommandTxt = @"SELECT DISTINCT MONTH([VisitDate]) AS MyMonth, COUNT( MONTH([VisitDate]) ) AS [pvcount] FROM [StatisticsHistory] {0} GROUP BY MONTH([VisitDate]) ORDER BY MONTH([VisitDate])";

            //根据条件组织追加相应的sql语句
            string strWhere = " where 1=1  ";

            if (begin > end)
            {
                begin = new DateTime(DateTime.Now.Year, 01, 01);
                end   = new DateTime(DateTime.Now.Year, 01, 31);
            }
            if (begin != DateTime.MinValue)
            {
                strWhere += "and  [VisitDate] >= {0}BEGIN";
                DataParameter dp = new DataParameter();
                dp.ParameterName = db.DbDriver.Prefix + "BEGIN";
                dp.DbType        = DbType.DateTime;
                dp.SourceColumn  = "VisitDate";
                dp.Value         = begin;
                sql.Parameters.Add(dp);
            }
            if (end != DateTime.MaxValue)
            {
                strWhere += " and [VisitDate] < {0}END";
                DataParameter dp2 = new DataParameter();
                dp2.ParameterName = db.DbDriver.Prefix + "END";
                dp2.Value         = end.AddDays(1);
                dp2.DbType        = DbType.DateTime;
                dp2.SourceColumn  = "VisitDate";
                sql.Parameters.Add(dp2);
            }
            if (channelID.Trim() != "")
            {
                HttpContext Context   = HttpContext.Current;
                string      chFullUrl = ChannelHelper.GetFullUrl(channelID);
                if (chFullUrl != "")
                {
                    strWhere += " and Url like {0}Url";
                    DataParameter dp3 = new DataParameter();
                    dp3.ParameterName = db.DbDriver.Prefix + "Url";
                    dp3.Value         = chFullUrl + '%';
                    dp3.DbType        = DbType.String;
                    dp3.Size          = 255;
                    sql.Parameters.Add(dp3);
                }
            }

            strWhere      = string.Format(strWhere, db.DbDriver.Prefix);
            sqlCommandTxt = string.Format(sqlCommandTxt, strWhere);
            sql.SqlClause = sqlCommandTxt;

            sql = db.DbDriver.FormatSQL(sql);
            List <PVKeyCountView> list = new List <PVKeyCountView>();

            using (IConnection conn = db.CreateConnection())
            {
                DataTable dt    = conn.Query(sql);
                int       total = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    PVKeyCountView kv = new PVKeyCountView();
                    kv.KeyValue = dr["MyMonth"].ToString();
                    kv.Count    = Int32.Parse(dr["pvcount"].ToString());
                    total      += kv.Count;
                    list.Add(kv);
                }
                //判断月份是不是十二个月份
                if (list.Count != 12)
                {
                    int num = 1;
                    for (int i = 1; i <= 12; i++)
                    {
                        PVKeyCountView pv = list.Find(delegate(PVKeyCountView view)
                        {
                            if (view.KeyValue != "0" + i.ToString() && view.KeyValue.IndexOf("0") == 0)
                            {
                                num = 0;
                            }
                            return(view.KeyValue == i.ToString() || view.KeyValue == "0" + i.ToString());
                        });

                        //如果没有 i 这个月份,则进行追加
                        if (pv == null)
                        {
                            PVKeyCountView kv = new PVKeyCountView();
                            if (num == 0 && i.ToString().Length < 2)
                            {
                                kv.KeyValue = "0" + i.ToString();
                            }
                            else
                            {
                                kv.KeyValue = i.ToString();
                            }
                            kv.Count = 0;
                            total   += kv.Count;
                            list.Add(kv);
                        }
                    }
                }
                //合算比例
                foreach (PVKeyCountView v in list)
                {
                    if (v.Count != 0)
                    {
                        v.Percent = (double)v.Count / (double)total * 100;
                    }
                }
            }
            list.Sort(new DinoComparer(orderKey, sort));
            return(list);
        }