root/trunk/Wierszowki/Wierszowki.Model/Linq/WierszowkiDataContext.cs @ 900

using System;
using System.Collections.Generic;
using System.Linq;

namespace Wierszowki.Core.Linq
{
    public partial class WierszowkiDataContext
    {
        public Author GetAuthorById(int id)
        {
            return Authors.SingleOrDefault(a => a.Id == id);
        }
        
        public List<Author> GetAuthors()
        {
            return Authors.ToList();
        }

        public List<User> GetUsers()
        {
            this.GetTable<User>();
            
            return Users.ToList();
        }
        public List<Issue> GetIssue()
        {
            return Issues.ToList();
        }

        public User GetUserById(int id)
        {
            return Users.SingleOrDefault(u => u.Id == id);
        }

        public User GetUserByLogin(string login)
        {
            return Users.SingleOrDefault(u => u.Login == login);
        }

        public bool LoginUser(string login, string password)
        {
            var query = from u in Users
                        where u.Login == login && u.Password == password
                        select u;

            return query.Count() == 1;
        }

        public List<Magazine> GetMagazines()
        {
            return Magazines.ToList();
        }

        public List<EmploymentType> GetEmploymentTypes()
        {
            return EmploymentTypes.ToList();
        }

        public List<ItemType> GetItemTypes()
        {
            return ItemTypes.ToList();
        }

        public IQueryable<Issue> FindIssuesByMagazineId(int magazineId, int addId)
        {
            var startDate = DateTime.Now.AddMonths(-3);
            var endDate = DateTime.Now.AddMonths(1);
            if (addId != 0)
            {
                var query = from i in Issues
                            where (i.Id == addId) || 
                                  (i.MagazineId == magazineId && i.Date >= startDate && i.Date <= endDate )                                  
                            select i;
                return query;
            }
            else
            {
                var query = from i in Issues
                            where i.MagazineId == magazineId
                                  && i.Date >= startDate
                                  && i.Date <= endDate
                            select i;
                return query;
            }
        }

        public IQueryable<Issue> FindIssuesByMagazineItemId(int issueId, int addId)
        {
            var issue = Issues.Single(i => i.Id == issueId);
            return FindIssuesByMagazineId(issue.MagazineId, addId);
        }

        public List<Report> FindItemsByYearMonthAndEmploymentType(int year, int month, int employmentType)
        {
            var query = from magazineItem in MagazineItems
                        where magazineItem.Date.Month == month
                              && magazineItem.Date.Year == year
                        group magazineItem by new { magazineItem.Author, magazineItem.Issue.MagazineId}
                            into m
                            select new Report { FirstName = m.Key.Author.FirstName, LastName = m.Key.Author.LastName, Price = m.Sum(magazineItem => magazineItem.Price), Bonus = m.Sum(magazineItem => magazineItem.Bonus), Magazine = m.Key.MagazineId.ToString() };

            return query.ToList();
        }

        public List<MagazineItem> FindItemsByYearMonthAndAuthor(int year, int month, int author)
        {
            var query = from magazineItem in MagazineItems
                        where magazineItem.Date.Month == month
                              && magazineItem.Date.Year == year
                              && magazineItem.AuthorId == author
                        orderby magazineItem.Issue.MagazineId descending 
                        select magazineItem;
            return query.ToList();
        }

        public List<Report> GetAuthorsReport(int year, int month)
        {
            var authors = GetAuthors(year, month);

            var reports = new List<Report>();
            foreach (var author in authors)
            {
                var report = new Report
                                 {
                                     FirstName = author.FirstName,
                                     LastName = author.LastName,
                                     Price = author.Price.Value,
                                     Bonus = author.Bonus.Value
                                 };
                reports.Add(report);
            }

            return reports;
        }

        public Dictionary<int, Dictionary<string, decimal>> GetAuthorsByMagzinesReport(int year, int month, List<Author> authors, List<Magazine> magazines)
        {
            var result = new Dictionary<int, Dictionary<string, decimal>>();

            foreach (var author in authors)
            {
                foreach (var magazine in magazines)
                {
                    if (!result.ContainsKey(author.Id))
                        result.Add(author.Id, new Dictionary<string, decimal>());

                    if (!result[author.Id].ContainsKey(magazine.NickName))
                        result[author.Id].Add(magazine.NickName, decimal.Zero);
                }
            }

            var authorsByMagazines = GetAuthorsByMagzines(year, month);
            foreach (var authorsByMagazine in authorsByMagazines)
            {
                if (result.ContainsKey(authorsByMagazine.Id))
                    result[authorsByMagazine.Id][authorsByMagazine.NickName] = authorsByMagazine.Price.Value +
                                                                               authorsByMagazine.Bonus.Value;
            }

            return result;
        }

        public void AddUser(User user)
        {
            InsertUser(user);
        }
        
    }
}