week-5-day-1

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using PetaPoco;

namespace week5day1
{
    public class Return
    {
        public int booksId { get; set; }
        public string isbn { get; set; }
        public DateTime returnDate { get; set; }
    }
    class Program
    {
        // Represents a record in the tables from librarybooks2
        public class CheckedOutBook
        {
            public int booksId { get; set; }
            public string author { get; set; }
            public  int categoryId { get; set; }
            public DateTime dateOfPublication { get; set; }
            public int isbn { get; set; }
        }
    public class Students
        {
            
            public string studentAddress { get; set; }
            public DateTime dateOfBirth { get; set; }
            public int studentId { get; set; }
        }
        public class Books
        {
            public int isbn { get; set; }
        }
    

        static void Main(string[] args)
        {
            // Create a PetaPoco database object
            var db = new PetaPoco.Database("Database");
            //Show all articles 
            //question 1 How many books are there total in the library?   
            foreach (var b in db.Query<CheckedOutBook>("SELECT * FROM Books"))
            {
                Console.WriteLine("{0} - {1}", b.booksId, b.isbn);
            }
            Console.ReadLine();

            //question 2 Which books are currently checked out?
            foreach (var c in db.Query<week5day1.CheckedOutBook>("select library.booksid, books.isbn from library join books on books.BooksID = library.BooksID where returndate <> getdate()"))
            {
                Console.WriteLine("{0}-{1}", c.booksId, c.isbn);
            }
            Console.ReadLine();
            //question 3 How many students are there?
            var s = db.Single<int>("select count(studentid) from students");

                Console.WriteLine($"There are {s} students!");
            Console.ReadLine();

            //question 4 How many books are in each category?
            var l = db.Single<int>("select count(BooksID) as 'books', (select distinct category.categoryid) as 'category' from books join category on category.categoryid = books.categoryid group by category.categoryid", 123);
            Console.WriteLine($"There are {l} books in each category");
            Console.ReadLine();
            //question 5 How many books are checked out by category?
            var a = db.Single<int>("select count(books.BooksID) as 'books that are checked out', (select distinct category.categoryid) as 'category' from books join category on category.categoryid = books.categoryid join library on library.booksid = books.BooksID where library.returndate > getdate() group by category.categoryid", 123);
            Console.WriteLine($"There are {a} books checked out by category");
            Console.ReadLine();
            //question 6 Which books will be returned in December?
            var r = db.Query<Return>(@"select count(books.BooksID) as 'books that are checked out', (select distinct category.categoryid) as 'category' from books join category on category.categoryid = books.categoryid join library on library.booksid = books.BooksID where library.returndate > getdate() group by category.categoryid");
            foreach(var t in r)
            {
                Console.WriteLine($"Book ID:{t.booksId} and ISBN: {t.isbn} Return Date:{t.returnDate}");
            }
            Console.ReadLine();
            //question 7 Which books are missing authors?

            var missingAuthors = db.Query<Books>(@"select isbn from books
            where author = ' '");
            foreach (var b in missingAuthors)
            {
                Console.WriteLine($"{b.isbn} is missing an author.");
            }
            Console.ReadLine();

        }
    }
}