using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Library
{
    class Book
    {
        public string ID;
        public int CategoryID;
        public int PublisherID;
        public string Name;
        public string Author;
        public int Year;
        public bool IsLanguageFarsi;
        public string Translator;
        public string ISBN;
        public int Count;

        DataAccess md = new DataAccess();

        public void GetOne()
        {
            
            string sql = "Select * from book where bid='{0}'";
            sql = string.Format(sql, this.ID);
            md.connect();
            DataTable dt = md.select(sql);
            md.Disconnect();

            this.ID = dt.Rows[0]["bid"].ToString();
            this.Name = dt.Rows[0]["bname"].ToString();
            this.Count = Int32.Parse( dt.Rows[0]["bcount"].ToString());
            this.Year = Int32.Parse(dt.Rows[0]["byear"].ToString());
            this.ISBN = dt.Rows[0]["bISBN"].ToString();
            this.Author = dt.Rows[0]["bAuthor"].ToString();
            this.Translator = dt.Rows[0]["bTranslator"].ToString();
            this.IsLanguageFarsi = Convert.ToBoolean( dt.Rows[0]["blangisfarsi"].ToString());
            this.CategoryID = Int32.Parse(dt.Rows[0]["catid_fk"].ToString());
            this.PublisherID = Int32.Parse(dt.Rows[0]["pid_fk"].ToString());

            
        }
        public void Add()
        {
            string sql = "Insert into Book (catid_fk,pid_fk,bname,bAuthor,bYear,blangisfarsi,btranslator,bisbn,bcount,bid)";
            sql += "values ({0},{1},'{2}','{3}',{4},{5},'{6}','{7}',{8},N'{9}')";
            sql = string.Format(sql,
                this.CategoryID, this.PublisherID,
                this.Name, this.Author,
                this.Year,Convert.ToInt32(this.IsLanguageFarsi),
                this.Translator, this.ISBN, this.Count
                ,this.ID);

            md.connect();
            md.docommand(sql);
            md.Disconnect();

        }

        public DataTable SelectForreport()
        {
            DataTable dt = new DataTable();
            string sql = @"Select * from book";
            md.connect();
            dt = md.select(sql);
            md.Disconnect();
            return dt;
        }
        public DataTable GetListByCategory()
        {
            DataTable dt = new DataTable();
            string sql = @"Select * from 
                        book,publisher 
                        where 
                        publisher.pid = book.pid_fk and
                        catid_fk={0}";
            sql = string.Format(sql, this.CategoryID);
            md.connect();
            dt = md.select(sql);
            md.Disconnect();
            return dt;
        }

        public void Update()
        {
            string sql="Update Book set catid_fk={0},pid_fk={1},bname=N'{2}',bAuthor=N'{3}',bYear={4},blangisfarsi={5},btranslator=N'{6}',bisbn='{7}',bcount={8} where bid=N'{9}'";
            sql = string.Format(sql,
                this.CategoryID, this.PublisherID,
                this.Name, this.Author,
                this.Year, 
                Convert.ToInt32(this.IsLanguageFarsi),
                this.Translator, this.ISBN, this.Count
                , this.ID);
            md.connect();
            md.docommand(sql);
            md.Disconnect();
        }
    }
}
