MetroGauges-Old/MetroGauges/Database/BlockDAL.cs

using MetroGauges.Database.Enitys;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MetroGauges.Database
{
    public class BlockDAL
    {

        public static ObservableCollection<BlockGroup> GetBlockGroups()
        {
            ObservableCollection<BlockGroup> groups = new ObservableCollection<BlockGroup>();
            DataTable dt = SQLiteHelper.ExecuteQuery("select * from blockgroup");
            groups = SQLiteHelper.FillModel<BlockGroup>(dt);
            foreach (var item in groups)
            {
                item.Blocks = GetBlocksByGroup(item.Id);
                if (item.Id == 4) //模板
                {
                    foreach (var block in item.Blocks)
                    {
                        block.ToolTip = block.Name;
                    }
                }
            }
            return groups;
        }

        public static void GetBlockGroupsChild(BlockGroup blockGroup)
        {
            //ObservableCollection<BlockInfo> groups = new ObservableCollection<BlockInfo>();
            DataTable dt = SQLiteHelper.ExecuteQuery("select * from blockgroup where parentid="+ blockGroup.Id);
            ObservableCollection < BlockGroup > datas = SQLiteHelper.FillModel<BlockGroup>(dt);

            foreach (var item in datas)
            {
                item.Blocks = GetBlocksByGroup(item.Id);
                if (item.Id == 4) //模板
                {
                    foreach (var block in item.Blocks)
                    {
                        block.ToolTip = block.Name;
                    }
                }
            }
            //return groups;
        }



        #region 分组管理
        public static int CreateGroup(string name)
        {
           int result=  SQLiteHelper.ExecuteNonQuery($"insert into blockgroup(name) values('{ name }')");
            return result;
        }
        public static void delGroup(int id)
        {
            DataTable dt = SQLiteHelper.ExecuteQuery($"select id from blockinfo where groupid={id}");
            foreach (DataRow dr in dt.Rows)
            {
                DeleteBlock(int.Parse(dr["id"].ToString()));
            }
            SQLiteHelper.ExecuteNonQuery($"delete from blockgroup where id={ id}");
        }
        public static void updateGroup(string name,int id)
        {

            SQLiteHelper.ExecuteNonQuery($"update blockgroup set name='{ name }' where id={ id}");
        }
        #endregion

        #region 块操作

        public static ObservableCollection<BlockInfo> GetBlocksByGroup(long groupid)
        {
            ObservableCollection<BlockInfo> blockInfos = new ObservableCollection<BlockInfo>();
            DataTable dt = SQLiteHelper.ExecuteQuery($"select * from blockinfo where groupid={groupid}");
            if (dt.Rows.Count > 0)
            {
                blockInfos = SQLiteHelper.FillModel<BlockInfo>(dt);
                foreach (var item in blockInfos)
                {
                    item.FieldData = GetFiledsByBlock(item.Id);
                }
            }
            
            return blockInfos;
        }

        public static void UpdateBlockLocation(long id, double x, double y)
        {
           int row= SQLiteHelper.ExecuteNonQuery($"update blockinfo set locationx={x},locationy={y} where id={ id}");
        }

        public static void UpdateBlockName(long id, string name)
        {
            int row = SQLiteHelper.ExecuteNonQuery($"update blockinfo set name='{name}' where id={ id}");
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="blockInfo"></param>
        /// <returns></returns>
        public static bool CreateBlock(BlockInfo blockInfo)
        {
            string sql = $"insert into blockinfo(name,groupid,filepath,istemplate) values('{ blockInfo.Name }',{blockInfo.Groupid},'{blockInfo.Filepath}',{ blockInfo.Istemplate })";
            int result = SQLiteHelper.ExecuteNonQuery(sql);

            return result > 0;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static int CheckBlock(string name)
        {
            DataTable dt = SQLiteHelper.ExecuteQuery($"select id from blockinfo where name='{name}'");
            if (dt.Rows.Count > 0)
            {
                return  int.Parse(dt.Rows[0]["id"].ToString());
            }
            else
            {
                return 0;
            }
        }

        public static bool DeleteBlock(long blockid)
        {
            SQLiteHelper.ExecuteNonQuery($"delete from fieldinfo where blockid={blockid}");
            int result = SQLiteHelper.ExecuteNonQuery($"delete from blockinfo where id={blockid}");
            return result > 0;
        }

        #endregion

        #region 字段操作

        public static bool CreateField(FieldInfo info)
        {
            string sql = $"insert into fieldinfo(blockid,fieldname,display,fieldvalue) values({ info.Blockid },'{info.Fieldname}','{info.Display}','{info.Fieldvalue}')";
            int result = SQLiteHelper.ExecuteNonQuery(sql);

            return result > 0;
        }

        public static bool DeleteFiled(long id)
        {
            int result = SQLiteHelper.ExecuteNonQuery($"delete from fieldinfo where id={id}");
            return result > 0;
        }

        public static bool UpdateField(FieldInfo info)
        {
            string sql = $"update fieldinfo set fieldvalue='{ info.Fieldvalue }' where id={ info.Id}";
            int result = SQLiteHelper.ExecuteNonQuery(sql);

            return result > 0;
        }

        public static ObservableCollection<FieldInfo> GetFiledsByBlock(long blockid)
        {
            ObservableCollection<FieldInfo> infos = new ObservableCollection<FieldInfo>();
            DataTable dt = SQLiteHelper.ExecuteQuery($"select * from fieldinfo where blockid={blockid}");
            if (dt.Rows.Count > 0)
            {
                infos = SQLiteHelper.FillModel<FieldInfo>(dt);
            }
            return infos;
        }


        #endregion


    }
}