Esempio n. 1
0
        public List<List<MarketStat30StataDetailItem>> GetCommonStatNewUserDetail(string date, string detailTableName)
        {
            var start = date.ToInt32().ToString();
            var end = start;
            if (date.ToInt32() > 0)
            { end = start.ToExactDateTime(DateTimeFormat.yyyyMMdd).AddDays(1).ToString(DateTimeFormat.yyyyMMdd); }

            const string SQL_PATTERN = @"SELECT COUNT({2}) AS COUNT, {2} FROM YLTEST.{3}
            WHERE CREATE_DATE_TIME BETWEEN TO_DATE('{0}', 'YYYYMMDD') AND TO_DATE('{1}', 'YYYYMMDD')
            GROUP BY {2}
            ORDER BY COUNT";

            var ret = new List<List<MarketStat30StataDetailItem>>();
            var items = new List<string> { "MODEL", "CHANNEL" };
            var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG);

            foreach (var col in items)
            {
                var sql = string.Format(SQL_PATTERN, start, end, col, detailTableName);

                var itemResult = new List<MarketStat30StataDetailItem>();

                var queryCommand = new QueryCommand(sql, provider);
                var ds = provider.ExecuteDataSet(queryCommand);
                if (ds != null && ds.Tables.Count > 0)
                {
                    var dt = ds.Tables[0];
                    if (dt.Rows.Count > 0)
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            if (row[col] == null) continue;
                            var array = new MarketStat30StataDetailItem();
                            array.Count = row["COUNT"].ToString().ToInt32();
                            array.Column = row[col].ToString();

                            itemResult.Add(array);
                        }
                    }

                }

                ret.Add(itemResult);
            }

            return ret;
        }
Esempio n. 2
0
        public List<List<MarketStat30StataDetailItem>> GetCommonStatActiveUserDetail(string date, string sumTableName)
        {
            date = date.ToInt32().ToString();

            const string SQL_PATTERN = @"SELECT SUM(TIMES) AS COUNT, {1} FROM  YLTEST.{2}
            WHERE STAT_DATE={0}
            GROUP BY {1}
            ORDER BY COUNT";

            var ret = new List<List<MarketStat30StataDetailItem>>();
            var items = new List<string> { "MODEL", "CHANNEL" };
            var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG);

            foreach (var col in items)
            {
                var sql = string.Format(SQL_PATTERN, date, col, sumTableName);

                var itemResult = new List<MarketStat30StataDetailItem>();

                var queryCommand = new QueryCommand(sql, provider);
                var ds = provider.ExecuteDataSet(queryCommand);
                if (ds != null && ds.Tables.Count > 0)
                {
                    var dt = ds.Tables[0];
                    if (dt.Rows.Count > 0)
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            if (row[col] == null) continue;

                            var array = new MarketStat30StataDetailItem();

                            array.Count = row["COUNT"].ToString().ToInt32();
                            array.Column = row[col].ToString();
                            itemResult.Add(array);
                        }
                    }
                }

                ret.Add(itemResult);
            }

            return ret;
        }