using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using YX.Entity;
namespace YX.DAL
{
    public static class System_Dal
    {
        
        /// <summary>
        /// 获取数据库所有表名
        /// </summary>
        /// <returns></returns>
        public static List<string > GetTableName()
        {
            List<string>list=new List<string>();
            try
            {
                using (Sam_DBEntities db=new Sam_DBEntities())
                {
                    string sql = @"select Name as TABLE_NAME from sysobjects where xtype = 'u' and status >= 0 and Name != 'sysdiagrams'";

                    DataTable dt= SqlHelper.ExecuteDataset(db.Database.Connection.ConnectionString, CommandType.Text, sql).Tables[0];
                    if (dt != null)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            list.Add(dr["TABLE_NAME"].ToString());
                        }
                    }

                    return list;
                }

       
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 获取某一个表的所有字段
        /// </summary>
        /// <param name="object_id">表名</param>
        /// <returns></returns>
        public static DataTable GetSyscolumns(string object_id)
        {
            DataTable dt = new DataTable();
            try
            {
                using (Sam_DBEntities db=new Sam_DBEntities())
                {
                    string strSql = "";
                    if (!string.IsNullOrEmpty(object_id) && object_id != "未选择")
                    {
                        strSql = @"SELECT
                                     [列名]=a.name,
                                     [数据类型]=b.name,
                                     [长度]=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
                                     [是否为空]=case when a.isnullable=1 then '√'else '' end,
                                     [默认值]=isnull(e.text,''),
                                     [说明]=isnull(g.[value],'未填说明')
                                     FROM syscolumns a
                                     left join systypes b on a.xusertype=b.xusertype
                                     inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
                                     left join syscomments e on a.cdefault=e.id
                                     left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
                                     left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
                        where d.name='" + object_id + "' order by a.id,a.colorder";
                        return SqlHelper.ExecuteDataset(db.Database.Connection.ConnectionString, CommandType.Text, strSql).Tables[0];
                    }
                }
                    
            }
            catch (Exception)
            {

                throw;
            }
            return dt;
        }
        public static int DbBackup(string FilePath)
        {
            try
            {
                using (Sam_DBEntities db=new Sam_DBEntities())
                {
                    string sql = string.Format(@"BACKUP DATABASE Sam_DB
                      TO  DISK = N'{0}'
                      WITH NOFORMAT
                      , NOINIT
                      , NAME = N'Sam_DB'
                      , SKIP
                      , REWIND
                      , NOUNLOAD
                      , STATS = 10", FilePath);

                    int i = SqlHelper.ExecuteNonQuery(db.Database.Connection.ConnectionString, CommandType.Text, sql);
                    return i;
                }
    
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 日志写数据库中
        /// </summary>
        /// <param name="loginfo"></param>
        /// <returns></returns>
        public static int WriteLogToDB(Base_Log loginfo)
        {
            try
            {
                using (Sam_DBEntities db=new Sam_DBEntities())
                {
                    loginfo.CreateTime = DateTime.Now;
                    loginfo.LogID = Guid.NewGuid().ToString();
                    db.Base_Log.Add(loginfo);
                    return db.SaveChanges(); ;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public static DataTable GetSystemLog(string ip ,string type) 
        {
            DataTable dt = new DataTable();
            try
            {
                using (Sam_DBEntities db = new Sam_DBEntities()) 
                {
                    var sql = @"SELECT top 35 LogMessage,CreateTime FROM Base_Log where type='" + type + "' and LocalIP= '" + ip+ "'  ORDER BY CreateTime DESC ";
                    dt = SqlHelper.ExecuteDataset(db.Database.Connection.ConnectionString, CommandType.Text, sql).Tables[0];
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return dt;
        }
    }
}