Esempio n. 1
0
        // GET: api/TopBuyer/5
        public List <TopBuyer> Get(string id)
        {
            List <TopBuyer> TopBuyer = new List <TopBuyer>();

            using (var conn = new NpgsqlConnection("Host = localhost; Port = 5555; Username = db_201617z_va_proekt_invoke_mk_owner; Password = invoke_finki; Database = db_201617z_va_proekt_invoke_mk"))
            {
                conn.Open();

                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection  = conn;
                    cmd.CommandText = "(select u.usersid,c.costomersusername as Username, to_char(pgames.dateofpurchasegames, 'Month') as Datum, sum(g.gamesprice) as profitgames from invoke.users u, invoke.costomers c, invoke.creditcards cc, invoke.purchasegames pgames, invoke.games g where u.usersid = c.usersid and c.usersid = cc.cardsid and cc.cardsid = pgames.cardsid and pgames.gamesname = g.gamesname and g.gamesgenre = '" + id + "' and pgames.dateofpurchasegames between current_date - interval '4 month' and current_date group by u.usersid, c.costomersusername, Datum having sum(g.gamesprice) = (select max(profitGames) from(select c.costomersusername as Username, sum(g.gamesprice) as profitgames from invoke.users u, invoke.costomers c, invoke.creditcards cc, invoke.purchasegames pgames, invoke.games g where u.usersid = c.usersid and c.usersid = cc.cardsid and cc.cardsid = pgames.cardsid and pgames.gamesname = g.gamesname and g.gamesgenre = '" + id + "' and pgames.dateofpurchasegames between current_date - interval '4 month' and current_date group by c.costomersusername) as lista))";



                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            TopBuyer topbuyer = new TopBuyer();
                            topbuyer.UserID   = reader.GetInt64(0);
                            topbuyer.UserName = reader.GetString(1);
                            topbuyer.Month    = reader.GetString(2);
                            topbuyer.Profit   = reader.GetInt64(3);

                            TopBuyer.Add(topbuyer);
                        }
                    }
                }
            }
            return(TopBuyer);
        }
        public List <TopBuyer> GetTopBuyers()
        {
            List <TopBuyer> ret = new List <TopBuyer>();

            using (db)
                using (var command = db.Database.GetDbConnection().CreateCommand())
                {
                    command.CommandText = "EXEC	TopBuyers";
                    db.Database.OpenConnection();
                    using (var result = command.ExecuteReader())
                    {
                        while (result.Read())
                        {
                            var item = new TopBuyer();
                            item.BuyerId     = (int)result[0];
                            item.Name        = (string)result[1];
                            item.TotalAmount = (int)result[2];
                            item.Quantity    = (int)result[3];
                            ret.Add(item);
                        }
                    }
                }

            return(ret);
        }