using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using Uninpho.DBOperation.Model;


namespace Uninpho.DBOperation.Operation
{
    public class SqlQueryable_QX
    {
        static SqlSugarClient db = Config.GetPgClient();
        /// <summary>
        /// 获取曲线信息管理数据
        /// </summary>
        public static List<T_quxian_account> Search_QX()
        {
            List<T_quxian_account> listqx = db.SqlQueryable<T_quxian_account>("select * from \"t_quxian_account\"").ToList();
            return listqx;
        }

        /// <summary>
        /// 获取道岔信息管理数据
        /// </summary>
        public static List<T_daocha_account> Search_DC()
        {

            List<T_daocha_account> listdc = db.SqlQueryable<T_daocha_account>("select * from \"t_daocha_account\"").ToList();
            return listdc;

        }

        /// <summary>
        /// 获取管界信息管理数据
        /// </summary>
        public static List<T_guanjie_account> Search_GJ()
        {
            List<T_guanjie_account> listgj = db.SqlQueryable<T_guanjie_account>("select * from \"t_guanjie_account\" ").ToList();
            return listgj;
        }


        /// <summary>
        /// 获取功率信息管理数据
        /// </summary>
        public static List<T_gonglv_account> Search_GLData()
        {
            List<T_gonglv_account> listgl = db.SqlQueryable<T_gonglv_account>("select * from \"t_gonglv_account\" ").ToList();
            return listgl;
        }

        /// <summary>
        /// 获取缺口信息管理数据
        /// </summary>
        public static List<T_quekou_account> Search_QKData()
        {
            List<T_quekou_account> listqk = db.SqlQueryable<T_quekou_account>("select * from \"t_quekou_account\" ").ToList();
            return listqk;
        }

        /// <summary>
        /// 插入功率信息数据表
        /// </summary>
        /// <param name="listgj"></param>
        public static void Insertdata_GL(List<T_gonglv_account> listgj)
        {
            db.Insertable<T_gonglv_account>(listgj).ExecuteCommand();//get change row count
        }

        /// <summary>
        /// 插入曲线信息数据表
        /// </summary>
        /// <param name="listqx"></param>
        public static void Insertdata_QX(List<T_quxian_account> listqx)
        {
             db.Insertable<T_quxian_account>(listqx).ExecuteCommand();//get change row count
        }

        /// <summary>
        /// 插入道岔信息数据表
        /// </summary>
        /// <param name="listdc"></param>
        public static void Insertdata_DC(List<T_daocha_account> listdc)
        {
                db.Insertable<T_daocha_account>(listdc).ExecuteCommand();//get change row count

        }

        /// <summary>
        /// 插入管界信息数据表
        /// </summary>
        /// <param name="listgj"></param>
        public static void Insertdata_GJ(List<T_guanjie_account> listgj)
        {
            db.Insertable<T_guanjie_account>(listgj).ExecuteCommand();//get change row count
        }

        /// <summary>
        /// 获取曲线数据局名信息
        /// </summary>
        /// <returns></returns>
        public static void SelectJMInform(ref List<string> qxjminform, ref List<string> dcjminform, ref List<string> qxgjjminform)
        {
            List<T_quxian_account> qxjminformList = db.SqlQueryable<T_quxian_account>("SELECT t1.juming FROM (SELECT t_quxian_account.juming,COUNT(*) AS cn FROM t_quxian_account GROUP BY t_quxian_account.juming)t1 WHERE t1.cn>0").ToList();
            List<T_daocha_account> dcjminformList = db.SqlQueryable<T_daocha_account>("SELECT t1.juming FROM (SELECT t_daocha_account.juming,COUNT(*) AS cn FROM t_daocha_account GROUP BY t_daocha_account.juming)t1 WHERE t1.cn>0").ToList();
            List<T_guanjie_account> gjjminformList = db.SqlQueryable<T_guanjie_account>("SELECT t1.juming FROM (SELECT t_guanjie_account.juming,COUNT(*) AS cn FROM t_guanjie_account GROUP BY t_guanjie_account.juming)t1 WHERE t1.cn>0").ToList();
            foreach (var item in qxjminformList)
            {
                qxjminform.Add(item.Juming);
            }
            foreach (var item in dcjminformList)
            {
                dcjminform.Add(item.Juming);
            }
            foreach (var item in gjjminformList)
            {
                qxgjjminform.Add(item.Juming);
            }

        } 
        //道岔删除行
        public static void DCDeleteRows(List<T_daocha_account> deletelist)
        {
            foreach (var item in deletelist)
            {
                db.Deleteable<T_daocha_account>().Where(it => it.Id == item.Id).ExecuteCommand();
            }

        }
        //管界删除行
        public static void GJDeleteRows(List<T_guanjie_account> deletelist)
        {
            foreach (var item in deletelist)
            {
                db.Deleteable<T_guanjie_account>().Where(it => it.Id == item.Id).ExecuteCommand();
            }

        }
        //道岔删除行
        public static void QXDeleteRows(List<T_quxian_account> deletelist)
        {
            foreach (var item in deletelist)
            {
                db.Deleteable<T_quxian_account>().Where(it => it.Id == item.Id).ExecuteCommand();
            }

        }
        //功率删除行
        public static void GLDeleteRows(List<T_gonglv_account> deletelist)
        {
            foreach (var item in deletelist)
            {
                db.Deleteable<T_gonglv_account>().Where(it => it.Id == item.Id).ExecuteCommand();
            }

        }

        //缺口删除行 
        public static void QKDeleteRows(List<T_quekou_account> deletelist)
        {
            foreach (var item in deletelist)
            {
                db.Deleteable<T_quekou_account>().Where(it => it.Id == item.Id).ExecuteCommand();
            }

        }

        //曲线更新数据
        public static void QXUpdataRowData(T_quxian_account rowdata)
        {            
            var qxrest = db.Updateable(rowdata).ExecuteCommand();
        }
        //道岔更新数据
        public static void DCUpdataRowData(T_daocha_account rowdata)
        {
            var qxrest = db.Updateable(rowdata).ExecuteCommand();
        }
        //管界更新数据
        public static void GJUpdataRowData(T_guanjie_account rowdata)
        {
            var qxrest = db.Updateable(rowdata).ExecuteCommand();
        }


        //
        //var list = db.SqlQueryable<User>("select * from \"user\"").ToPageList(1, 2, ref total);


        ////by expression
        //var list2 = db.SqlQueryable<User>("select * from \"user\"").Where(it => it.Id == 1).ToPageList(1, 2);
        ////by sql
        //var list3 = db.SqlQueryable<User>("select * from \"user\"").Where("id=@id", new { id = 1 }).ToPageList(1, 2);

    }
}