using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using TradeModel;
using NetLibrary.Data;
using NetLibrary;
using NetLibrary.Log;
using TradeData;
using NetLibrary.OnlineTrade;


namespace TradeManageNew
{
    public class EBookData
    {
        #region 保存
        public static int Save_BookList(E_BookList Model)
        {
            string tsql = @"
if @id>0
begin
Update [E_BookList] set [bookname]=@bookname,[remark]=@remark,[url]=@url,[fristimg]=@fristimg,[pages]=@pages,[indate]=@indate,[inname]=@inname,[sortid]=@sortid,[sort]=@sort,[isuse]=@isuse,pagetype=@pagetype,[type_name]=@type_name,type_name2=@type_name2,type_name3=@type_name3,type_name4=@type_name4,type_name5=@type_name5,popedom=@popedom where id=@id
end
else
begin
INSERT INTO [E_BookList]([bookname],[remark],[url],[fristimg],[pages],[indate],[inname],[sortid],[sort],[isuse],pagetype,[type_name],type_name2,type_name3,type_name4,type_name5,popedom)values(@bookname,@remark,@url,@fristimg,@pages,@indate,@inname,@sortid,@sort,@isuse,@pagetype,@type_name,@type_name2,@type_name3,@type_name4,@type_name5,@popedom)
set @id=SCOPE_IDENTITY()
end
select @id";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@id", DbType.Int32, Model.id);
            db.AddInParameter(cmd, "@bookname", DbType.String, Model.bookname);
            db.AddInParameter(cmd, "@remark", DbType.String, Model.remark);
            db.AddInParameter(cmd, "@url", DbType.String, Model.url);
            db.AddInParameter(cmd, "@fristimg", DbType.String, Model.fristimg);
            db.AddInParameter(cmd, "@pages", DbType.Int32, Model.pages);
            db.AddInParameter(cmd, "@indate", DbType.DateTime, Model.indate);
            db.AddInParameter(cmd, "@inname", DbType.String, Model.inname);
            db.AddInParameter(cmd, "@sortid", DbType.String, Model.sortid);
            db.AddInParameter(cmd, "@sort", DbType.Int32, Model.sort);
            db.AddInParameter(cmd, "@isuse", DbType.Int32, Model.isuse);
            db.AddInParameter(cmd, "@go_code", DbType.String, Model.go_code);
            db.AddInParameter(cmd, "@pagetype", DbType.String, Model.pagetype);
            db.AddInParameter(cmd, "@type_name", DbType.String, Model.type_name);
            db.AddInParameter(cmd, "@type_name2", DbType.String, Model.type_name2);
            db.AddInParameter(cmd, "@type_name3", DbType.String, Model.type_name3);
            db.AddInParameter(cmd, "@type_name4", DbType.String, Model.type_name4);
            db.AddInParameter(cmd, "@type_name5", DbType.String, Model.type_name5);
            db.AddInParameter(cmd, "@popedom", DbType.Int32, Model.popedom);
            int a = Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion
        #region 删除
        public static void Delete_BookList(int id)
        {
            string tsql = @"
            update E_BookList set isuse=0 where id=@id";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@id", DbType.Int32, id);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 返回列表
        public static List<E_BookList> GetListBookList()
        {
            //string tsql = "select a.id,a.bookname,a.remark,a.url,a.fristimg,a.pages,a.indate,a.inname,a.sortid,a.sort,a.isuse,a.go_code from E_BookList";
            string tsql = "select * from E_BookList  where isuse=1 order by  sort";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            DataTable tb = db.ExecuteDataTable(cmd);
            List<E_BookList> ListModel = tb.ToList<E_BookList>();
            return ListModel;
        }
        #endregion

        #region 分页动态条件查询
        public static List<E_BookList> GetListBookList(string where, int PageIndex, int PageSize, string Sort, out int RowCount)
        {
            QueryService2 ser = new QueryService2();
            ser.Tsql = "select id=cast(a.id as int),a.bookname,a.remark,url='http://60.12.218.120:8080//EBOOK/EBook_Info.aspx?gocode='+a.go_code,a.fristimg,a.pages,a.indate,a.inname,a.sortid,a.sort,a.isuse,a.go_code,a.type_name,a.type_name2,a.type_name3,a.type_name4,a.type_name5,a.popedom from E_BookList a";
            ser.Tsql += " " + ser.Filter(where);
            ser.PageIndex = PageIndex;
            ser.PageSize = PageSize;
            if (string.IsNullOrEmpty(Sort) == true) { ser.Sort = "a.id desc"; }
            else { ser.Sort = Sort; }
            string tsql = ser.GetText();
            List<E_BookList> ListModel = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddOutParameter(cmd, "@RowCount", DbType.Int32, 4);
            DataTable tb = db.ExecuteDataTable(cmd);
            RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
            ListModel = tb.ToList<E_BookList>();
            return ListModel;
        }
        #endregion

        #region 分页动态条件查询
        public static List<E_BookList> GetListMyBookList(string where,int UserId, int PageIndex, int PageSize, string Sort, out int RowCount)
        {
            QueryService2 ser = new QueryService2();
            if(UserId==1)
                ser.Tsql = "select id=cast(a.id as int),a.bookname,a.remark,url='http://60.12.218.120:8080//EBOOK/EBook_Info.aspx?gocode='+a.go_code,a.fristimg,a.pages,a.indate,a.inname,a.sortid,a.sort,a.isuse,a.go_code,a.type_name,a.type_name2,a.type_name3,a.type_name4,a.type_name5,a.popedom from E_BookList a";
            else
            ser.Tsql = @"select id=cast(a.id as int),a.bookname,a.remark,url='http://60.12.218.120:8080//EBOOK/EBook_Info.aspx?gocode='+a.go_code,a.fristimg,a.pages,a.indate,a.inname,a.sortid,a.sort,a.isuse,a.go_code,a.type_name,a.type_name2,a.type_name3,a.type_name4,a.type_name5,a.popedom from (select * from E_BookList where popedom=0 or id in (select distinct a.bookid from E_BookPopedom a
inner join JC_RoleUser b on a.roleid = b.RoleId
where b.UserId = " + UserId + "))a";
            ser.Tsql += " " + ser.Filter(where);
            ser.PageIndex = PageIndex;
            ser.PageSize = PageSize;
            if (string.IsNullOrEmpty(Sort) == true) { ser.Sort = "a.id desc"; }
            else { ser.Sort = Sort; }
            string tsql = ser.GetText();
            List<E_BookList> ListModel = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddOutParameter(cmd, "@RowCount", DbType.Int32, 4);
            DataTable tb = db.ExecuteDataTable(cmd);
            RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
            ListModel = tb.ToList<E_BookList>();
            return ListModel;
        }
        #endregion

        #region 分页动态条件查询
        public static List<E_BookList> GetListOutBookList(string where, string code, int PageIndex, int PageSize, string Sort, out int RowCount)
        {
            QueryService2 ser = new QueryService2();
        
                ser.Tsql = @"select id=cast(a.id as int),a.bookname,a.remark,a.fristimg,a.pages,a.indate,a.inname,a.sortid,a.sort,a.isuse,a.go_code,a.type_name,a.type_name2,a.type_name3,a.type_name4,a.type_name5,a.popedom from (select * from E_BookList where  id in (select distinct bookid from E_BookPopedom 
where code ='" + code + "'))a";
            ser.Tsql += " " + ser.Filter(where);
            ser.PageIndex = PageIndex;
            ser.PageSize = PageSize;
            if (string.IsNullOrEmpty(Sort) == true) { ser.Sort = "a.id desc"; }
            else { ser.Sort = Sort; }
            string tsql = ser.GetText();
            List<E_BookList> ListModel = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddOutParameter(cmd, "@RowCount", DbType.Int32, 4);
            DataTable tb = db.ExecuteDataTable(cmd);
            RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
            ListModel = tb.ToList<E_BookList>();
            return ListModel;
        }
        #endregion
        #region 保存
        public static int Save_BookPages(E_BookPages Model)
        {
            string tsql = @"
if @id>0
begin
Update [E_BookPages] set [bookid]=@bookid,[pageimg]=@pageimg,[sort]=@sort,[pagedesc]=@pagedesc,[pagetitle]=@pagetitle,Type=@Type where id=@id
end
else
begin
INSERT INTO [E_BookPages]([bookid],[pageimg],[sort],[pagedesc],[pagetitle],Type)values(@bookid,@pageimg,@sort,@pagedesc,@pagetitle,@Type)
set @id=SCOPE_IDENTITY()
end
select @id";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@id", DbType.Int32, Model.id);
            db.AddInParameter(cmd, "@bookid", DbType.Int32, Model.bookid);
            db.AddInParameter(cmd, "@pageimg", DbType.String, Model.pageimg);
            db.AddInParameter(cmd, "@sort", DbType.Int32, Model.sort);
            db.AddInParameter(cmd, "@pagedesc", DbType.String, Model.pagedesc);
            db.AddInParameter(cmd, "@pagetitle", DbType.String, Model.pagetitle);
            db.AddInParameter(cmd, "@Type", DbType.Int32, Model.Type);
            int a = Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion
        #region 删除
        public static void Delete_BookPagesAll(int bookid,int Type)
        {
            string tsql = @"
            delete from E_BookPages where bookid=@bookid and  isnull(type,1)=@Type";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@bookid", DbType.Int32, bookid);
            db.AddInParameter(cmd, "@Type", DbType.Int32, Type);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 删除
        public static void Delete_BookPages(int id)
        {
            string tsql = @"
            delete from E_BookPages where id=@id";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@id", DbType.Int32, id);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 返回Model
        public static E_BookList GetModel_Book(int bookid)
        {
            E_BookList model = null;
            //string tsql = "select a.deptid,a.deptname,a.doctor,a.isuse,a.sort from HZTJ_Dept";
            string tsql = @"
            select * from E_BookList where id=@bookid";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@bookid", DbType.Int32, bookid);
            DataSet ds = db.ExecuteDataSet(cmd);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model = ds.Tables[0].Rows[0].ToModel<E_BookList>();
            }
            return model;
        }
        #endregion
        #region 返回列表
        public static List<E_BookPages> GetListBookPages(int bookid,int Type)
        {
            //string tsql = "select a.id,a.bookid,a.pageimg,a.sort,a.pagedesc,a.pagetitle from E_BookPages";
            string tsql = "select * from E_BookPages where bookid=@bookid  and isnull(Type,1)=@Type order  by sort";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@bookid", DbType.Int32, bookid);
            db.AddInParameter(cmd, "@Type", DbType.Int32, Type);
            DataTable tb = db.ExecuteDataTable(cmd);
            List<E_BookPages> ListModel = tb.ToList<E_BookPages>();
            return ListModel;
        }
        #endregion

        #region 返回列表
        public static List<E_BookPages> GetListBookPages2(string gocode)
        {
            //string tsql = "select a.id,a.bookid,a.pageimg,a.sort,a.pagedesc,a.pagetitle from E_BookPages";
            string tsql = @"select a.*,b.bookname from E_BookPages a
inner join  E_BookList b on a.bookid = b.id
 where b.go_code =@gocode order  by a.sort";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@gocode", DbType.String, gocode);
            DataTable tb = db.ExecuteDataTable(cmd);
            List<E_BookPages> ListModel = tb.ToList<E_BookPages>();
            return ListModel;
        }
        #endregion

        #region 返回列表
        public static List<E_BookPages> GetListBookPagesImgs(string gocode,int Type)
        {
            //string tsql = "select a.id,a.bookid,a.pageimg,a.sort,a.pagedesc,a.pagetitle from E_BookPages";
            string tsql = @"select a.*,b.bookname from E_BookPages a
inner join  E_BookList b on a.bookid = b.id
 where b.go_code =@gocode and isnull(a.type,1)=@Type order  by a.sort";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@gocode", DbType.String, gocode);
            db.AddInParameter(cmd, "@Type", DbType.Int32, Type);
            DataTable tb = db.ExecuteDataTable(cmd);
            List<E_BookPages> ListModel = tb.ToList<E_BookPages>();
            return ListModel;
        }
        #endregion

        #region 返回列表
        public static List<JC_RoleInfo> GetListJC_RoleInfo()
        {
            //string tsql = "select a.Code,a.KeyName,a.Name,a.IsUse,a.Remark,a.SortNo,a.CompanyId from JC_BaseCodeDetail";
            string tsql = "select * from JC_RoleInfo order by sort";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            DataTable tb = db.ExecuteDataTable(cmd);
            List<JC_RoleInfo> ListModel = tb.ToList<JC_RoleInfo>();
            return ListModel;
        }
        #endregion

        #region 保存
        public static int Save_BookPopedom(E_BookPopedom Model)
        {
            string tsql = @"
if @id>0
begin
Update [E_BookPopedom] set [bookid]=@bookid,[roleid]=@roleid,[popedom]=@popedom,[Code]=@Code where id=@id
end
else
begin
INSERT INTO [E_BookPopedom]([bookid],[roleid],[popedom],[Code])values(@bookid,@roleid,@popedom,@Code)
set @id=SCOPE_IDENTITY()
end
select @id";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@id", DbType.Int32, Model.id);
            db.AddInParameter(cmd, "@bookid", DbType.Int32, Model.bookid);
            db.AddInParameter(cmd, "@roleid", DbType.Int32, Model.roleid);
            db.AddInParameter(cmd, "@popedom", DbType.Int32, Model.popedom);
            db.AddInParameter(cmd, "@Code", DbType.String, Model.Code);
            int a = Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion

        #region 删除书权限
        public static void DeleteBookPopedom(int bookid)
        {
            string tsql = @"
delete from E_BookPopedom where bookid=@bookid and roleid>0 
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@bookid", DbType.Int32, bookid);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 返回列表
        public static List<E_BookPopedom> GetListBookPopedom(int bookid)
        {
            //string tsql = "select a.id,a.bookid,a.roleid,a.popedom,a.Code from E_BookPopedom";
            string tsql = "select * from E_BookPopedom where bookid=@bookid and roleid>0 ";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@bookid", DbType.Int32, bookid);
            DataTable tb = db.ExecuteDataTable(cmd);
            List<E_BookPopedom> ListModel = tb.ToList<E_BookPopedom>();
            return ListModel;
        }
        #endregion
        #region 返回列表
        public static List<E_BookList> GetListGC()
        {
            //string tsql = "select a.id,a.bookid,a.pageimg,a.sort,a.pagedesc,a.pagetitle from E_BookPages";
            string tsql = "select distinct type_name3 from E_BookList where isuse=1 and isnull(type_name3,'')<>'' ";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            DataTable tb = db.ExecuteDataTable(cmd);
            List<E_BookList> ListModel = tb.ToList<E_BookList>();
            return ListModel;
        }
        #endregion
        #region 返回列表
        public static List<E_BookList> GetListCD()
        {
            //string tsql = "select a.id,a.bookid,a.pageimg,a.sort,a.pagedesc,a.pagetitle from E_BookPages";
            string tsql = "select distinct type_name4 from E_BookList where isuse=1  and isnull(type_name4,'')<>''  ";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            DataTable tb = db.ExecuteDataTable(cmd);
            List<E_BookList> ListModel = tb.ToList<E_BookList>();
            return ListModel;
        }
        #endregion
        #region 返回列表
        public static List<E_BookList> GetListFL()
        {
            //string tsql = "select a.id,a.bookid,a.pageimg,a.sort,a.pagedesc,a.pagetitle from E_BookPages";
            string tsql = "select distinct type_name from E_BookList where isuse=1  and isnull(type_name,'')<>'' ";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            DataTable tb = db.ExecuteDataTable(cmd);
            List<E_BookList> ListModel = tb.ToList<E_BookList>();
            return ListModel;
        }
        #endregion
        #region 返回列表
        public static List<E_BookList> GetListGCXZ()
        {
            //string tsql = "select a.id,a.bookid,a.pageimg,a.sort,a.pagedesc,a.pagetitle from E_BookPages";
            string tsql = "select distinct type_name5 from E_BookList where isuse=1  and isnull(type_name5,'')<>''  ";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            DataTable tb = db.ExecuteDataTable(cmd);
            List<E_BookList> ListModel = tb.ToList<E_BookList>();
            return ListModel;
        }
        #endregion
    }
}