Esempio n. 1
0
        public List <Statist> GetAppNameList(string UserName, Nullable <DateTime> start, Nullable <DateTime> end)
        {
            List <Statist> list = new List <Statist>();
            StringBuilder  sql  = new StringBuilder();

            sql.Append("select UserName,UnitName,");
            sql.AppendLine("sum(case AppName when 'mail' then UsageCount else 0 end)'mail',");
            sql.AppendLine("sum(case AppName when 'pt.calendar' then UsageCount else 0 end)'calendar',");
            sql.AppendLine("sum(case AppName when 'magazine' then UsageCount else 0 end)'magazine',");
            sql.AppendLine("sum(case AppName when 'contact' then UsageCount else 0 end)'contact',");
            sql.AppendLine("sum(case AppName when 'callboard' then UsageCount else 0 end)'callboard',");
            sql.AppendLine("sum(case AppName when 'sms' then UsageCount else 0 end)'sms',");
            sql.AppendLine("sum(case AppName when 'pt.keywork' then UsageCount else 0 end)'keywork',");
            sql.AppendLine("sum(case AppName when 'pt.dowork' then UsageCount else 0 end)'dowork',");
            sql.AppendLine("sum(case AppName when 'pt.yqzg' then UsageCount else 0 end)'yqzg',");
            sql.AppendLine("sum(case AppName when 'pt.instruction' then UsageCount else 0 end)'instruction',");
            sql.AppendLine("sum(case AppName when 'pt.dbd' then UsageCount else 0 end)'dbd',");
            sql.AppendLine("sum(case AppName when 'pt.fourdo' then UsageCount else 0 end)'fourdo'");
            sql.AppendLine("  from (select * from [UsageLogDaily] where 1=1");
            if (!string.IsNullOrEmpty(UserName))
            {
                sql.Append(string.Format(" and UserName='******'", UserName));
            }
            if (start != null && end == null)
            {
                sql.AppendLine("  and OpTime='" + start + "'");
            }
            if (end != null && start == null)
            {
                sql.AppendLine("  and OpTime='" + end + "'");
            }
            if (start != null && end != null)
            {
                if (start <= end)
                {
                    sql.Append(string.Format("  and  OpTime between'" + start + "' and '" + end + "'"));
                }
            }
            sql.AppendLine(")a group by UserName,UnitName");
            DbSqlHelper.connectionString = @"Data Source=192.168.2.16\sql2008r2,2244;Initial Catalog=SmartBoxApp;User ID=SmartBox;Password=App1234";
            var ds = DbSqlHelper.Query(sql.ToString());

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Statist entity = new Statist();
                if (!row.IsNull("UserName"))
                {
                    entity.UserName = row["UserName"].ToString();
                }
                if (!row.IsNull("UnitName"))
                {
                    entity.UnitName = row["UnitName"].ToString();
                }
                if (!row.IsNull("mail"))
                {
                    entity.mail = row["mail"].ToString();
                }
                if (!row.IsNull("calendar"))
                {
                    entity.calendar = row["calendar"].ToString();
                }
                if (!row.IsNull("magazine"))
                {
                    entity.magazine = row["magazine"].ToString();
                }
                if (!row.IsNull("contact"))
                {
                    entity.contact = row["contact"].ToString();
                }
                if (!row.IsNull("callboard"))
                {
                    entity.callboard = row["callboard"].ToString();
                }
                if (!row.IsNull("sms"))
                {
                    entity.sms = row["sms"].ToString();
                }
                if (!row.IsNull("keywork"))
                {
                    entity.keywork = row["keywork"].ToString();
                }
                if (!row.IsNull("dowork"))
                {
                    entity.dowork = row["dowork"].ToString();
                }
                if (!row.IsNull("yqzg"))
                {
                    entity.yqzg = row["yqzg"].ToString();
                }
                if (!row.IsNull("instruction"))
                {
                    entity.instruction = row["instruction"].ToString();
                }
                if (!row.IsNull("dbd"))
                {
                    entity.dbd = row["dbd"].ToString();
                }
                if (!row.IsNull("fourdo"))
                {
                    entity.fourdo = row["fourdo"].ToString();
                }
                list.Add(entity);
            }
            return(list);
        }
Esempio n. 2
0
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="UntitName">单位名称</param>
        /// <param name="OpTime">访问时间</param>
        /// <returns></returns>
        public List <Statist> GetList(string UntitName, Nullable <DateTime> start, Nullable <DateTime> end)
        {
            List <Statist> list = new List <Statist>();
            StringBuilder  sql  = new StringBuilder();

            sql.Append("select UnitName,COUNT(distinct UserName) as UserCount,");
            sql.AppendLine("sum(case Device when 'PAd/Android' then UsageCount else 0 end)'Pad/Android',");
            sql.AppendLine("sum(case Device when 'PAd/iOS' then UsageCount else 0 end)'Pad/iOS',");
            sql.AppendLine("sum(case Device when 'PC/Windows' then UsageCount else 0 end)'PC/Windows',");
            sql.AppendLine("sum(case Device when 'Phone/Android' then UsageCount else 0 end)'Phone/Android',");
            sql.AppendLine("sum(case Device when 'Phone/iOS' then UsageCount else 0 end)'Phone/iOS'");
            sql.AppendLine(",SUM(UsageCount) as UsageCount from (select * from [UsageLogDaily] where 1=1");
            if (!string.IsNullOrEmpty(UntitName))
            {
                sql.Append(string.Format(" and unitName='{0}'", UntitName));
            }
            if (start != null && end == null)
            {
                sql.AppendLine("  and OpTime='" + start + "'");
            }
            if (end != null && start == null)
            {
                sql.AppendLine("  and OpTime='" + end + "'");
            }
            if (start != null && end != null)
            {
                if (start <= end)
                {
                    sql.Append(string.Format("  and  OpTime between'" + start + "' and '" + end + "'"));
                }
            }
            sql.AppendLine(")a group by UnitName");
            DbSqlHelper.connectionString = @"Data Source=192.168.200.141;Uid=sa;Pwd=App1234;Initial Catalog=SmartBoxApp";
            var ds = DbSqlHelper.Query(sql.ToString());

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Statist entity = new Statist();
                if (!row.IsNull("UsageCount"))
                {
                    entity.UsageCount = Convert.ToInt32(row["UsageCount"].ToString());
                }
                if (!row.IsNull("UserCount"))
                {
                    entity.UserCount = Convert.ToInt32(row["UserCount"].ToString());
                }
                if (!row.IsNull("UnitName"))
                {
                    entity.UnitName = row["UnitName"].ToString();
                }
                if (!row.IsNull("PAd/Android"))
                {
                    entity.Pad_Android = Convert.ToInt32(row["PAd/Android"].ToString());
                }
                if (!row.IsNull("Pad/iOS"))
                {
                    entity.Pad_iOS = Convert.ToInt32(row["Pad/iOS"].ToString());
                }
                if (!row.IsNull("PC/Windows"))
                {
                    entity.PC_Windows = Convert.ToInt32(row["PC/Windows"].ToString());
                }
                if (!row.IsNull("Phone/Android"))
                {
                    entity.Phone_Android = Convert.ToInt32(row["Phone/Android"].ToString());
                }
                if (!row.IsNull("Phone/iOS"))
                {
                    entity.Phone_iOS = Convert.ToInt32(row["Phone/iOS"].ToString());
                }
                list.Add(entity);
            }
            return(list);
        }
Esempio n. 3
0
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="UntitName">用户名称</param>
        /// <param name="OpTime">访问时间</param>
        /// <returns></returns>
        public List <Statist> GetUserList(string UserName, Nullable <DateTime> start, Nullable <DateTime> end)
        {
            List <Statist> list = new List <Statist>();
            StringBuilder  sql  = new StringBuilder();

            sql.Append("select UserName,UnitName,");
            sql.AppendLine("sum(case Device when 'PAD/ANDROID' then UsageCount else 0 end)'PAD/ANDROID',");
            sql.AppendLine("sum(case Device when 'PAD/IOS' then UsageCount else 0 end)'PAD/IOS',");
            sql.AppendLine("sum(case Device when 'PHONE/ANDROID' then UsageCount else 0 end)'PHONE/ANDROID',");
            sql.AppendLine("sum(case Device when 'PHONE/IOS' then UsageCount else 0 end)'PHONE/IOS'");
            sql.AppendLine(" from (select * from [UsageLogDaily] where 1=1");
            if (!string.IsNullOrEmpty(UserName))
            {
                sql.Append(string.Format(" and UserName='******'", UserName));
            }
            if (start != null && end == null)
            {
                sql.AppendLine("  and OpTime='" + start + "'");
            }
            if (end != null && start == null)
            {
                sql.AppendLine("  and OpTime='" + end + "'");
            }
            if (start != null && end != null)
            {
                if (start <= end)
                {
                    sql.Append(string.Format("  and  OpTime between'" + start + "' and '" + end + "'"));
                }
            }
            sql.AppendLine(")a group by UserName,UnitName");
            DbSqlHelper.connectionString = @"Data Source=192.168.2.16\sql2008r2,2244;Initial Catalog=SmartBoxApp;User ID=SmartBox;Password=App1234";
            var ds = DbSqlHelper.Query(sql.ToString());

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Statist entity = new Statist();
                if (!row.IsNull("UserName"))
                {
                    entity.UserName = row["UserName"].ToString();
                }
                if (!row.IsNull("UnitName"))
                {
                    entity.UnitName = row["UnitName"].ToString();
                }
                if (!row.IsNull("PAD/ANDROID"))
                {
                    entity.Pad_Android = Convert.ToInt32(row["PAD/ANDROID"].ToString());
                }
                if (!row.IsNull("PAD/IOS"))
                {
                    entity.Pad_iOS = Convert.ToInt32(row["PAD/IOS"].ToString());
                }
                if (!row.IsNull("PHONE/ANDROID"))
                {
                    entity.Phone_Android = Convert.ToInt32(row["PHONE/ANDROID"].ToString());
                }
                if (!row.IsNull("PHONE/IOS"))
                {
                    entity.Phone_iOS = Convert.ToInt32(row["PHONE/IOS"].ToString());
                }
                list.Add(entity);
            }
            return(list);
        }