// Get all categories of products
 public List<Category> GetCategory()
 {
     try
     {
         List<Category> ListOfCategory = new List<Category>();
         connect = new Connection().Connect;
         connect.Open();
         Command = new SqlCommand("sp_getAllCategory", connect);
         Command.CommandType = System.Data.CommandType.StoredProcedure;
         SqlDataReader dr = Command.ExecuteReader();
         while (dr.Read())
         {
             Category objCategory = new Category();
             objCategory.CategoryID = dr.GetInt32(0);
             objCategory.CategoryName = dr.GetString(1);
             ListOfCategory.Add(objCategory);
         }
         connect.Close();
         return ListOfCategory;
     }
     catch (Exception)
     {
         connect.Close();
         return null;
     }
 }

        public override IQuery GetHsql(Object data)
        {
            StringBuilder sql = new StringBuilder("select a from C_CasNumber a    where  ");
            C_CasNumber C_CasNumber = (C_CasNumber)data;
            if (C_CasNumber != null)
            {
                Parms = new List<Object[]>();
                if (C_CasNumber.CasNumberID != 0)
                {
                    sql.Append(" a.CasNumberID = :id     and   ");
                    Parms.Add(new Object[] { "id", C_CasNumber.CasNumberID });
                }

                if (!String.IsNullOrEmpty(C_CasNumber.Name))
                {
                    sql.Append(" (a.Name like :nom  OR a.Code like :nomz)  and   "); 
                    Parms.Add(new Object[] { "nom", "%" + C_CasNumber.Name + "%" });
                    Parms.Add(new Object[] { "nomz",  C_CasNumber.Name + "%" });
                }

                if (!String.IsNullOrEmpty(C_CasNumber.Code))
                {
                    sql.Append(" a.Code = :nom1     and   "); 
                    Parms.Add(new Object[] { "nom1", C_CasNumber.Code });
                }
            }

            sql = new StringBuilder(sql.ToString());
            sql.Append(" 1=1 order by a.CasNumberID asc ");
            IQuery query = Factory.Session.CreateQuery(sql.ToString());
            SetParameters(query);            
            return query;
        }

#pragma warning disable 1587
        /// <summary>
        /// Set bet team to template by ticket bet team pattern with following rules:
        /// <para>ho: Home and Over, hu: Home and Under</para>
        /// <para>ao: Away and Over, au: Away and Under</para>
        /// <para>do: Draw and Over, du: Draw and Over</para>
        /// </summary>
        /// <param name="ticket"> Ticket with bet type id is 143.</param>
        /// <param name="ticketHelper"> Not null ticket helper.</param>
        /// <param name="ticketData"> Not need this.</param>
#pragma warning restore 1587
        protected override void BuildBetTeam(ITicket ticket, ITicketHelper ticketHelper, List<ITicketData> ticketData)
        {
            var teamName = string.Empty;

            // Bet team pattern length is always 2
            if (ticket.BetTeam.Length == 2)
            {
                switch (ticket.BetTeam[0])
                {
                    case 'h':
                        teamName = ticketHelper.GetTeamNameById(ticket.HomeId);
                        break;

                    case 'a':
                        teamName = ticketHelper.GetTeamNameById(ticket.AwayId);
                        break;

                    case 'd':
                        teamName = CoreBetList.draw;
                        break;
                }

                var resultOverUnder = (ticket.BetTeam[1].ToString(CultureInfo.InvariantCulture) == BetTeamValue.O)
                    ? CoreBetList.over
                    : CoreBetList.under;

                Template.betTeam = string.Join(" & ", new string[] { teamName, resultOverUnder });
            }
        }

        public List<Movie> GetTopTenWorstMovies()
        {
            using (var ctx = new DatabaseContext())
            {
                var bests = (from r in ctx.Review
                             group r by r.MovieId into groupIds
                             select new { groupIds.Key, Average = groupIds.Sum(x => x.Note) / groupIds.Count() })
                             .OrderByDescending(x => x.Average)
                             .Take(10)
                             .ToList();

                var worst = (from r in ctx.Review
                             group r by r.MovieId into groupIds
                             select new { groupIds.Key, Average = groupIds.Sum(x => x.Note) / groupIds.Count() })
                             .ToList()
                             .Where(r => !bests.Any(y => y.Key == r.Key))
                             .OrderBy(x => x.Average)
                             .Take(10)
                             .ToList();

                var listWorst = new List<Movie>();
                foreach (var movie in worst)
                    listWorst.Add(GetById(movie.Key));

                return listWorst;
            }
        }

        public static List<Yazar> getAuthors()
        {
            List<Yazar> Value = new List<Yazar>();
            SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");

            SqlCommand cmd = new SqlCommand("select * from dbo.Yazar", con);
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string adi = reader.GetString(1);
                string soyadi = reader.GetString(2);
                Yazar Current = new Yazar(id, adi, soyadi);
                string ozgecmis;
                try
                {
                    ozgecmis = reader.GetString(3);
                }
                catch(SqlNullValueException snve)
                {
                    ozgecmis = "";
                }
                Current.Ozgecmis = ozgecmis;
                Value.Add(Current);
            }
            reader.Close();
            con.Close();
            return Value;
        }

 /// <summary>
 /// Constructor with user's way keeping
 /// </summary>
 /// <param name="repository">Object describing storage kind</param>
 public BookList(IRepository repository)
 {
     if (repository == null)
         throw new ArgumentNullException("Repository is undefined");
     books = new List<Book>();
     this.repository = repository;
 }

 public List<Team> FillTeams(Team homeTeam, Team awayTeam)
 {
     List<Team> teams = new List<Team>();
     teams.Add(homeTeam);
     teams.Add(awayTeam);
     return teams;
 }

        public IQueryable<ComplejoModelResponse> GetAllComplejos()
        {
            try
            {
                var complejos = from complejo in complejoRepository.All
                                select complejo;

                List<ComplejoModelResponse> list = new List<ComplejoModelResponse>();
                list.Add(new ComplejoModelResponse()
                {
                    HttpStatusCode = 200,
                    HttpStatusDesc = "Ok",
                    Complejos = complejos
                });
                return list.AsQueryable();
            }
            catch (Exception ex)
            {
                List<ComplejoModelResponse> list = new List<ComplejoModelResponse>();
                list.Add(new ComplejoModelResponse()
                {
                    HttpStatusCode = 400,
                    HttpStatusDesc = "Bad Request",
                    Complejos = new List<Complejo>().AsQueryable(),
                    Exception = ex
                });

                return list.AsQueryable();
            }
        }

        protected override void BuildBetTeam(ITicket ticket, ITicketHelper ticketHelper, List<ITicketData> ticketData)
        {
            var betTeam = string.Empty;

            switch (ticket.BetTeam)
            {
                case "0:0":
                    betTeam = CoreBetList.NoGoalBetType14;
                    break;
                case "1:1":
                    betTeam = CoreBetList.HomeFG;
                    break;
                case "1:2":
                    betTeam = CoreBetList.HomeLG;
                    break;
                case "2:1":
                    betTeam = CoreBetList.AwayFG;
                    break;
                case "2:2":
                    betTeam = CoreBetList.AwayLG;
                    break;
            }

            Template.betTeam = betTeam;
        }

        public IList<KitAssembly> Select(KitAssembly data, int showRegs)
        {
            IList<KitAssembly> datos = new List<KitAssembly>();

            /*
                datos = GetHsql(data).List<KitAssembly>();
                if (!Factory.IsTransactional)
                    Factory.Commit();
                return datos;
            */
                try
                {
                    if (showRegs > 0)
                        datos = GetHsql(data).SetMaxResults(showRegs).List<KitAssembly>();
                    else
                        datos = GetHsql(data).List<KitAssembly>();

                    if (!Factory.IsTransactional)
                        Factory.Commit();
                }

                catch (Exception e)
                {
                    NHibernateHelper.WriteEventLog(WriteLog.GetTechMessage(e));
                }


                return datos;
        }

        public override IQuery GetHsql(Object data)
        {
            StringBuilder sql = new StringBuilder("select a from NodeRoute a    where  ");
            NodeRoute noderoute = (NodeRoute)data;
            if (noderoute != null)
            {
                Parms = new List<Object[]>();
                if (noderoute.RowID != 0)
                {
                    sql.Append(" a.RowID = :id     and   ");
                    Parms.Add(new Object[] { "id", noderoute.RowID });
                }

                if (noderoute.NextNode != null && noderoute.NextNode.NodeID != 0)
                {
                    sql.Append(" a.NextNode.NodeID = :id1     and   ");
                    Parms.Add(new Object[] { "id1", noderoute.NextNode.NodeID });
                }

                if (noderoute.CurNode != null && noderoute.CurNode.NodeID != 0)
                {
                    sql.Append(" a.CurNode.NodeID = :id2     and   ");
                    Parms.Add(new Object[] { "id2", noderoute.CurNode.NodeID });
                }


            }

            sql = new StringBuilder(sql.ToString());
            sql.Append("1=1 order by a.RowID asc ");
            IQuery query = Factory.Session.CreateQuery(sql.ToString());
            SetParameters(query);
            return query;
        }

        protected override void BuildBetTeam(ITicket ticket, ITicketHelper ticketHelper, List<ITicketData> ticketData)
        {
            string team = ticket.BetTeam != null && ticket.BetTeam.Contains(BetTeamValue.H) ? Template.Match.homeTeam : Template.Match.awayTeam;
            string yesNo = ticket.BetTeam != null && ticket.BetTeam.Contains(BetTeamValue.Y) ? CoreBetList.lblYes : CoreBetList.lblNo;

            Template.betTeam = string.Join(null, new string[] { team, " <span class=\"", Favorite, "\">", yesNo.ToUpper(), "</span>" });
        }

        public List<Person> getExamplePersons()
        {
            List<Person> PERSON = null;

            try
            {
                CONNECTION.Open();

                String query = "SELECT TOP 100 [FirstName],[MiddleName],[LastName] FROM [Person].[Person]";
                COMMAND = new SqlCommand(query, CONNECTION);
                READER = COMMAND.ExecuteReader();

                if (READER.HasRows)
                {
                    PERSON = new List<Person>();
                    EntityMapper MAPPER = new EntityMapper();

                    while (READER.Read())
                        PERSON.Add(MAPPER.convertToPerson(READER));
                }

                CONNECTION.Close();
            }
            catch (Exception EX)
            {
                errorMessage = EX.Message;
                Console.WriteLine("Error: " + EX.Message);
            }

            return PERSON;
        }

 public static List<Kategori> getCategories()
 {
     List<Kategori> Value = new List<Kategori>();
     SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
     SqlCommand cmd = new SqlCommand("select * from dbo.Kategori", con);
     con.Open();
     SqlDataReader reader = cmd.ExecuteReader();
     while (reader.Read())
     {
         int id = reader.GetInt32(0);
         string adi = reader.GetString(1);
         Kategori Current = new Kategori(id, adi);
         string aciklama;
         try
         {
             aciklama = reader.GetString(2);
         }
         catch (SqlNullValueException snve)
         {
             aciklama = "";
         }
         Current.Aciklama = aciklama;
         Value.Add(Current);
     }
     reader.Close();
     con.Close();
     return Value;
 }

        /// <summary>
        /// Builds the bet team.
        /// </summary>
        /// <param name="ticket">The ticket.</param>
        /// <param name="ticketHelper">The ticket helper.</param>
        /// <param name="ticketData">The ticket data.</param>
        protected override void BuildBetTeam(ITicket ticket, ITicketHelper ticketHelper, List<ITicketData> ticketData)
        {
            string choice = ticket.BetTeam;
            var betTeam = string.Empty;
            switch (choice)
            {
                case "01":
                    betTeam = "4 " + CoreBetList.points;
                    break;

                case "02":
                    betTeam = "5 " + CoreBetList.points;
                    break;

                case "03":
                    betTeam = "6 " + CoreBetList.points;
                    break;

                case "04":
                    betTeam = "7 " + CoreBetList.upPoints;
                    break;
            }

            // "&nbsp" because export format
            Template.betTeam = HtmlCharacters.NoneBreakingSpace + betTeam;
        }

        public override IQuery GetHsql(Object data)
        {
            StringBuilder sql = new StringBuilder("select a from Rol a    where  ");
            Rol rol = (Rol)data;
            if (rol != null)
            {
                Parms = new List<Object[]>();
                if (rol.RolID != 0)
                {
                    sql.Append(" a.RolID = :id     and   ");
                    Parms.Add(new Object[] { "id", rol.RolID });
                }

                if (!String.IsNullOrEmpty(rol.RolCode))
                {
                    sql.Append(" a.RolCode = :nom     and   ");
                    Parms.Add(new Object[] { "nom", rol.RolCode });
                }

                if (!String.IsNullOrEmpty(rol.Name))
                {
                    sql.Append(" a.Name = :nom1     and   ");
                    Parms.Add(new Object[] { "nom1", rol.Name });
                }



            }

            sql = new StringBuilder(sql.ToString());
            sql.Append("1=1 order by a.RolID asc ");
            IQuery query = Factory.Session.CreateQuery(sql.ToString());
            SetParameters(query);
            return query;
        }

        public List<Item> ItemsGridview(string name)
        {
            var griditems = new List<Item>();
            Item item = null;
            using (var db = new Database())
            {
                db.OpenConnection();
                db.CreateCommand(
                    "SELECT Item.*, Quality.Name AS Quality FROM Item, Quality WHERE Item.Quality_ID=Quality.Quality_ID AND Item.Name LIKE @name");
                db.AddParameter("@name", "%" + name + "%");
                MySqlDataReader dr = db.Command.ExecuteReader();

                while (dr.Read())
                {
                    item = new Item();
                    item.Name = (string)dr["name"];
                    item.Level = (int)dr["level"];
                    item.ReqLevel = (int)dr["Reqlevel"];
                    item.Quality = (string)dr["Quality"];
                    griditems.Add(item);
                }
            }

            return griditems;
        }

        public List<Book> LoadToList()
        {
            List<Book> books = new List<Book>();

            try
            {
                XDocument document = XDocument.Load(filePath);
                var boooks = document.Elements("books").Elements("book");

                foreach (XElement e in boooks)
                {
                    Book b = new Book();
                    b.Title = e.Element("title").Value;
                    b.Author = e.Element("author").Value;
                    b.Publiser = e.Element("publisher").Value;
                    b.Year = int.Parse(e.Element("year").Value);
                    b.NumberOfPages = int.Parse(e.Element("numberOfPages").Value);

                    books.Add(b);
                }
                return books;
            }
            catch (FileNotFoundException)
            {
                throw new InvalidDataException("Error while saving");
            }
        }

        public static List<BookModel> GetAllBooks(string skip, string top)
        {
            var cache = HttpContext.Current.Cache["allBooks"];
            var model = new BookModel();
            List<BookModel> listAllBooks = new List<BookModel>();

            if (cache == null)
            {
                GetApiResponse<BookModel> apiModelBook= new GetApiResponse<BookModel>();
                listAllBooks = apiModelBook.GetAllBooksFromDb("api/APIDbBook?");
                BookCount = listAllBooks.Count;

                if (top=="0")
                {
                    listAllBooks = apiModelBook.GetAllBooksFromDb("api/APIDbBook?");
                }
                else
                {
                    listAllBooks = apiModelBook.GetAllBooksFromDb("api/APIDbBook?$skip=" + skip + "&$top=" + top );
                }

                cache = listAllBooks;
            }
            else
            {
                listAllBooks = cache as List<BookModel>;
            }
            return listAllBooks;
        }

        private IList<byte[]> CreateBlocks(byte[] file)
        {
            var size = chunkSize;
            var blocksCount = Math.Ceiling((decimal)file.Length / size);
            var remainingSize = file.Length;
            var blocks = new List<byte[]>();

            for (int i = 0; i < blocksCount; i++)
            {
                if (remainingSize > size)
                {
                    remainingSize -= size;
                }
                else
                {
                    size = remainingSize;
                }

                var block = file.Skip(i * chunkSize).Take(size).ToArray();

                if (block.Length >= chunkSize)
                {
                    blocks.Add(block);
                }
                else
                {
                    var chunk = new byte[chunkSize];
                    Array.Copy(block, 0, chunk, 0, block.Length);
                    blocks.Add(chunk);
                }
            }

            return blocks;
        }