using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Terra.YaoGan.Common;

namespace Terra.YaoGan.Service
{
   public  class GetTableBySql
    {
        sqlserverDBhelper DB = new sqlserverDBhelper();

        /// <summary>
        /// 获取一级菜单，二级菜单
        /// </summary>
        /// <param name="where">查询条件</param>
        /// <returns></returns>
        public DataTable GetFisrtAndSecondTable(string where)
        {
            //string sql = " select  ft.name,st.name sname,st.id    from (select  *  from  FirstCatlog  where parentId='" + where + "') ft  LEFT JOIN SecondCatlog  st  on  ft.id=st.FirstPid ORDER BY  ft.name ;";
            string sql =string.Format(@"select rt.name rootname,fst.* from RootCatlog rt RIGHT  JOIN (select  ft.name,ft.parentId ,st.name sname,st.id ,st.Type  from (select  *  from  FirstCatlog  where parentId='{0}') ft 
 LEFT JOIN SecondCatlog st  on ft.id = st.FirstPid ) fst on rt.id = fst.parentId  ORDER by fst.name desc ,type ASC; ", where);
            DataTable dt = DB.QueryTable(sql);
            return dt;
        }


        public DataTable GetSecondAndThirdCount(string where)
        {
            string sql = "select  *  from  ThirdCatlog  where SecondPid='"+where+"'";
            DataTable dt = DB.QueryTable(sql);
            return dt;
        }

        public DataTable GetSecondAndThirdTable(string where)
        {
            //string sql =string.Format(@"select  st.name pname,tt.id,tt.name	,tt.timeZhou,tt.legend,tt.chart  from  (select  *  from SecondCatlog where id='{0}') st 
            //            LEFT JOIN ThirdCatlog tt on st.id = tt.SecondPid",where);
            string sql = string.Format(@"select rt.name rootname ,fsst.* from  RootCatlog rt RIGHT JOIN  
                                        (select ft.name fname,ft.parentId,stt.*  FROM FirstCatlog ft RIGHT JOIN  
                                        (select  st.name pname,st.FirstPid,st.Type ,tt.id,tt.name	,tt.timeZhou,tt.legend,tt.chart  from  
                                        (select  *  from SecondCatlog where id='{0}') st LEFT JOIN ThirdCatlog tt on st.id = tt.SecondPid)
                                        stt on ft.id=stt.FirstPid) fsst ON rt.id=fsst.parentId ORDER BY name DESC;", where);
            DataTable dt = DB.QueryTable(sql);
            return dt;
        }

        public  DataTable GetLakeUrl(string  where)
        {
            string sql = @"SELECT  *  from  (select  fst.*,rt.name rtname  from  (select tst.*,ft.name fname,ft.parentId  from  
                          ( select tt.name tname,st.name sname,st.FirstPid,st.Type  from ThirdCatlog  tt LEFT JOIN 
                           SecondCatlog st on tt.SecondPid=st.id) tst LEFT JOIN FirstCatlog ft on tst.FirstPid=ft.id)  fst LEFT 
                           JOIN RootCatlog rt on fst.parentId=rt.id) allt where allt.Type='"+where+"'";
            DataTable dt = DB.QueryTable(sql);
            return dt;
        }
    }
}