jiance-analysis/Uninpho.DBOperation/Operation/DMControl.cs

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using Uninpho.DBOperation.Model;

namespace Uninpho.DBOperation.Operation
{
    public class DMControl
    {
        /// <summary>
        /// 获取曲线信息管理数据
        /// </summary>
        public static object GetUnionTable()
        {
            SqlSugarClient db = Config.GetPgClient();

            List<string> ignoreColumn = new List<string>() { "remark", "xuhao" };
            //List<string> ignoreColumn = new List<string>() { "xingbie", "jgjlc", "xlm", "remark", "xuhao" };


            List<T_metadata_account> DaoChaMeta = MetaDataCtrl.GetMetaData("t_daocha_account");
            List<T_metadata_account> JHFXMeta = MetaDataCtrl.GetMetaData("t_jihefenxi_account");
            List<T_metadata_account> LGLMeta = MetaDataCtrl.GetMetaData("t_lunguili_account");


            //StringBuilder sql = new StringBuilder();
            //sql.Append(" select c.*,ab.* from(   ;");
            //List<string> dcList = new List<string>();
            //DaoChaMeta.ForEach( i => dcList.Add("dc."+i.name));
            //List<string> jhfxList = new List<string>();
            //JHFXMeta.ForEach(i => dcList.Add("jhfx." + i.name));

            List<string> lglList = new List<string>();
            LGLMeta.ForEach(i =>
            {
                if (!ignoreColumn.Contains(i.name))
                {
                    //if (i.name == "id")
                        if (i.name == "id" || i.name == "xlm" || i.name == "jgjlc" || i.name == "xingbie")
                        {
                        lglList.Add(string.Format("lgl.{0} as lgl_{0}", i.name));
                    }
                    else
                    {
                        lglList.Add("lgl." + i.name);
                    }
                }
            }
         );

            List<string> jhfxList = new List<string>();
            JHFXMeta.ForEach(i =>
            {

                //if (i.name == "id")
                if (i.name == "id" || i.name == "xlm" || i.name == "jgjlc" || i.name == "xingbie")
                {
                    jhfxList.Add(string.Format("jhfx.{0} as jhfx_{0}", i.name));
                }
                else
                {
                    jhfxList.Add("jhfx." + i.name);
                }

            }
         );


            List<string> dcList = new List<string>();
            DaoChaMeta.ForEach(i =>
            {
                if (!ignoreColumn.Contains(i.name))
                {
                    //if (i.name == "id")
                    //{
                    //    dcList.Add(string.Format("dc.{0} as dc_{0}", i.name));
                    //}
                    //else
                    //{
                    dcList.Add("dc." + i.name);
                    //}
                }

            }
         );

            string sql = @" select {dcColumns},ab.* from(
                                 select {lglColumns}, {jhfxColumns}  from t_lunguili_account lgl, t_jihefenxi_account jhfx 
                                 where 
                                     lgl.xlm = jhfx.xlm and 
                                     lgl.xingbie = jhfx.xingbie and 
                                     lgl.jgjlc = jhfx.jgjlc) 
                                 as 
                                     ab,t_daocha_account dc
                            where 
                                ab.jhfx_xlm = dc.xlm and 
                                ab.jhfx_xingbie = dc.xingbie and 
                                ab.jhfx_jgjlc = dc.jgjlc";

            string sql2 = @"select * from (select {jhfxColumns},{dcColumns}
                            from public.t_jihefenxi_account jhfx ,public.t_daocha_account dc
                            where dc.xlm = jhfx.xlm and dc.xingbie = jhfx.xingbie and dc.jgjlc = jhfx.jgjlc) as newtab 
                            where newtab.jhfx_id not in 
                            (select ab.id from(
	                        select jhfx.id,jhfx.xlm,jhfx.xingbie,jhfx.jgjlc
	                        from t_lunguili_account lgl, t_jihefenxi_account jhfx 
	                        where lgl.xlm = jhfx.xlm and lgl.xingbie = jhfx.xingbie and lgl.jgjlc = jhfx.jgjlc) 
	                        as ab,t_daocha_account dc
	                        where ab.xlm = dc.xlm and ab.xingbie = dc.xingbie and ab.jgjlc = dc.jgjlc)";
            string sql3 = @"select * from (select {lglColumns},{dcColumns}
                            from public.t_lunguili_account lgl ,public.t_daocha_account dc
                            where dc.xlm = lgl.xlm and dc.xingbie = lgl.xingbie and dc.jgjlc = lgl.jgjlc) as newtab 
                            where newtab.lgl_id not in 
                            (select ab.id from(
	                        select lgl.id,lgl.xlm,lgl.xingbie,lgl.jgjlc
	                        from t_lunguili_account lgl, t_jihefenxi_account jhfx 
	                        where lgl.xlm = jhfx.xlm and lgl.xingbie = jhfx.xingbie and lgl.jgjlc = jhfx.jgjlc) 
	                        as ab,t_daocha_account dc
	                        where ab.xlm = dc.xlm and ab.xingbie = dc.xingbie and ab.jgjlc = dc.jgjlc)";
            //string sql2 = @"select {jhfxColumns},{dcColumns} from public.t_jihefenxi_account jhfx ,public.t_daocha_account dc
            //        where 		
            //  dc.xlm = jhfx.xlm and 
            //        dc.xingbie = jhfx.xingbie and 
            //        dc.jgjlc = jhfx.jgjlc";
            //string sql3 = @"select {lglColumns},{dcColumns} from public.t_lunguili_account lgl ,public.t_daocha_account dc
            //                where 		
            //           dc.xlm = lgl.xlm and 
            //                 dc.xingbie = lgl.xingbie and 
            //                dc.jgjlc = lgl.jgjlc";
            sql = sql.Replace("{lglColumns}", string.Join(",", lglList)).Replace("{dcColumns}", string.Join(",", dcList)).Replace("{jhfxColumns}", string.Join(",", jhfxList));
            sql2 = sql2.Replace("{dcColumns}", string.Join(",", dcList)).Replace("{jhfxColumns}", string.Join(",", jhfxList));
            sql3 = sql3.Replace("{lglColumns}", string.Join(",", lglList)).Replace("{dcColumns}", string.Join(",", dcList));

            //var list = db.Queryable<dynamic>().AS("datamanage").Where("id=@id", new { id = 1 }).ToList();
            //var list2 = db.Queryable<dynamic>("o").AS("order")
            //            .AddJoinInfo("OrderDetail", "i", "o.id=i.OrderId")
            //            .Where("id=@id", new { id = 1 })
            //            .Select("o.*").ToList();

            var listqx = db.SqlQueryable<dynamic>(sql).ToList();
            var listqx1 = db.SqlQueryable<dynamic>(sql2).ToList();
            var listqx2 = db.SqlQueryable<dynamic>(sql3).ToList();

            var fristUnion = listqx.Union(listqx1).Distinct().ToList();
            var secondUnion = fristUnion.Union(listqx2).Distinct().ToList();

            return secondUnion;


            //if (listqx1.Count > listqx2.Count)
            //{
            //    #region MyRegion

            //    //    List<int> indexs = new List<int>();
            //    //    for (int i = 0; i < listqx1.Count; i++)
            //    //    {
            //    //        for (int j = 0; j < listqx.Count; j++)
            //    //        {
            //    //            bool issame = true;
            //    //            foreach (KeyValuePair<string, object> col in listqx1[i])
            //    //            {
            //    //                string aa = col.Key;//属性
            //    //                string bb;
            //    //                if (col.Value.ToString() == null)
            //    //                {
            //    //                    bb = "asdvbasghjdsadguhjasgdhjas";
            //    //                }
            //    //                else
            //    //                {
            //    //                    bb = col.Value.ToString();//值
            //    //                }

            //    //                foreach (KeyValuePair<string, object> cola in listqx[j])
            //    //                {
            //    //                    string aaa = cola.Key;//属性
            //    //                    string bba;
            //    //                    if (col.Value.ToString() == null)
            //    //                    {
            //    //                        bba = "a34567235473625462345672346";
            //    //                    }
            //    //                    else
            //    //                    {
            //    //                        bba = cola.Value.ToString();//值
            //    //                    }
            //    //                    if (aa == aaa && bb == bba)
            //    //                    {
            //    //                        issame = true;
            //    //                    }
            //    //                }
            //    //                if (issame)
            //    //                {                               
            //    //                    break;
            //    //                }
            //    //            }
            //    //            if (issame)
            //    //            {
            //    //                indexs.Add(i);
            //    //                break;
            //    //            }
            //    //        }
            //    //    }
            //    //    for (int i = indexs.Count; i >= 0; i--)
            //    //    {
            //    //        listqx1.RemoveAt(indexs[i]);
            //    //    }
            //    #endregion
            //    return listqx.Union(listqx1).Distinct().ToList();
            //}
            //else if (listqx1.Count < listqx2.Count)
            //{
            //    return listqx.Union(listqx2).Distinct().ToList();
            //}
            //else
            //{
            //    return listqx;
            //}
        }

        /// <summary>
        /// 获取轮轨力波形数据
        /// </summary>
        /// <param name="yswjm">映射文件名</param>
        /// <returns></returns>
        public static List<T_lunguili_boxing> GetLGL_BXData(string yswjm)
        {
            SqlSugarClient db = Config.GetPgClient();
            string strSQL = string.Format("select * from  t_lunguili_boxing where yswjm = '{0}'", yswjm);
            //by expression
            return db.SqlQueryable<T_lunguili_boxing>(strSQL).ToList();

        }

        public static List<T_lunguili_boxing> GetLGL_BXDataByIDS(string ids)
        {
            string[] idList = ids.Split(',');
            if (idList.Length == 2)
            {
                SqlSugarClient db = Config.GetPgClient();

                int startId = Convert.ToInt32(idList[0]);
                int endId = Convert.ToInt32(idList[1]);
                string strSQL = string.Format("select * from  t_lunguili_boxing where id BETWEEN {0} and {1}", startId, endId);
                //by expression
                return db.SqlQueryable<T_lunguili_boxing>(strSQL).ToList();
            }
            else
            {
                return null;
            }

        }


        /// <summary>
        /// 获取几何分析波形数据
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static List<T_jihefenxi_boxing> GetJHFX_BXDataByIDS(string ids)
        {
            string[] idList = ids.Split(',');
            if (idList.Length == 2)
            {
                SqlSugarClient db = Config.GetPgClient();

                int startId = Convert.ToInt32(idList[0]);
                int endId = Convert.ToInt32(idList[1]);
                string strSQL = string.Format("select * from  t_jihefenxi_boxing where id BETWEEN {0} and {1}", startId, endId);
                //by expression
                return db.SqlQueryable<T_jihefenxi_boxing>(strSQL).ToList();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取几何分析波形数据
        /// </summary>
        /// <param name="yswjm">映射文件名</param>
        /// <returns></returns>
        public static List<T_jihefenxi_boxing> GetJHFX_BXData(string yswjm)
        {
            SqlSugarClient db = Config.GetPgClient();
            string strSQL = string.Format("select * from  t_jihefenxi_boxing where yswjm = '{0}'", yswjm);
            //by expression
            return db.SqlQueryable<T_jihefenxi_boxing>(strSQL).ToList();


        }

    }
}