using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace FangYar.BLL
{
    /// <summary>
    /// 设备（BLL）
    /// </summary>
    public class CommomBLL
    {
        private static readonly FangYar.IDAL.CommomIDAL dal = FangYar.DALFactory.Factory.GeCommomDAL();

        /// <summary>
        /// 获取记录数
        /// </summary>
        public int Count(string table, string where)
        {
            return dal.Count(table, where);
        }
        /// <summary>
        /// 获取列表
        /// </summary>

        /// <returns></returns>
        public string GetJsonList(string field, string table, string strwhere)
        {
            return dal.GetJsonList(field, table, strwhere);
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetTableList(string field, string table, string strwhere)
        {
            return dal.GetTableList(field, table, strwhere);
        }
        public DataTable GetTableList(string sql)
        {
            return dal.GetTableList(sql);
        }
        /// <summary>
        /// 获取列表
        /// </summary>

        /// <returns></returns>
        public string GetJsonMonthCount(string field, string table, string year, string where)
        {
            return dal.GetJsonMonthCount(field, table, year, where);
        }
        /// <returns></returns>
        public DataTable GetMonthCount(string field, string table, string year, string where)
        {
            return dal.GetMonthCount(field, table, year, where);
        }


        public string GetJsonXQHYCount(string field, string table, string where)
        {
            return FangYar.Common.JsonHelper.ToJson(GetXQHYCount(field, table, where));
        }
        public DataTable GetXQHYCount(string field, string table, string where)
        {
            string sql = " select t.dic_text as hy, IFNULL(q.sl,0) as sl from (select * from TBL_SYS_DICDETAIL where  MOD_CODE='HYTYPE') t left join (";

            if (table == "ZHSQ_WARN_RECORD")
            {//报警记录

                sql += "  select sum(b.sl) as sl,dev_hy   from ZHSQ_DEVICE_INFO m left join (select  count(*) as sl, dev_id from ZHSQ_WARN_RECORD  group by dev_id ) b  on m.dev_code=b.dev_id  ";
            }
            else if (table == "ZHSQ_DEVICE_INFO")
            {//安装记录

                sql += "select count(*) as sl,DEV_HY from ZHSQ_DEVICE_INFO t  ";
            }

            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }
            sql += " group by " + field + ")q on t.dic_value=q." + field + " order by IFNULL(q.sl,0) desc ";

            return FangYar.Common.MySqlHelper.QueryTable(sql);
        }



        public string GetJsonXQDWCount(string field, string table, string where, string where2)
        {
            return FangYar.Common.JsonHelper.ToJson(GetXQDWCount(field, table, where, where2));
        }
        public DataTable GetXQDWCount(string field, string table, string where, string where2)
        {
            string sql = "select t.* ,IFNULL(d.sl,0) as sl from (select * from tbl_sys_org ";
            sql += " where area_id in( select area_code from TBL_SYS_AREA t ";
            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }
            sql += " )) t left join  ";
            sql += " (select count(*) as sl," + field + " from " + table + " t  ";
            if (!string.IsNullOrEmpty(where2))
            {
                sql += " WHERE " + where2;
            }
            sql += " group by " + field + ") d on t.id=d." + field + "  order by IFNULL(d.sl,0) desc ";


            return FangYar.Common.MySqlHelper.QueryTable(sql);
        }
        public string GetJsonWeekCount(string field, string table, string where)
        {
            return FangYar.Common.JsonHelper.ToJson(GetWeekCount(field, table, where));
        }
        public DataTable GetWeekCount(string field, string table, string where)
        {
            string dayStr = "";
            int daynum = 7;
            for (int i = daynum; i > 0; i--)
            {
                if (i < daynum)
                {
                    dayStr += " union ";
                }
                dayStr += "select '" + DateTime.Now.AddDays(-i).ToString("yyyy-MM-dd HH:mm:ss") + "' D";
            }
            string sql = " with t1 as (" + dayStr + ") ";
            sql += " select date_format(t1.d,'%Y-%m-%d') as rq, count(t2." + field + " )  as sl ";
            sql += " from t1 left join (select record_time from " + table;
            if (!string.IsNullOrEmpty(where))
            {
                sql += " WHERE " + where;
            }
            sql += " ) t2  ";
            sql += "on  date_format(t1.d,'%Y-%m-%d') = date_format(t2." + field + ",'%Y-%m-%d') ";

            sql += " group by t1.d order by t1.d  ";


            return FangYar.Common.MySqlHelper.QueryTable(sql);
        }
        public int GetXQCount(string field, string table, string where, string where2)
        {
            string sql = " select count(1) as num from " + table + " t WHERE ";
            if (!string.IsNullOrEmpty(where))
            {
                sql += where + " and ";
            }
            sql += field + " in(select id from tbl_sys_org where area_id  ";

            sql += " in(select area_code from tbl_sys_area ";
            if (!string.IsNullOrEmpty(where2))
            {
                sql += " WHERE " + where2;
            }

            sql += " )) ";
            return FangYar.Common.MySqlHelper.GetCount(sql);
        }

        public DataTable GetTodayWarnList(string field, string table, string where)
        {
            string sql = "select t.id,d.dev_code,t.record_time, t.record_state,t.warn_val,t.warn_type,t.dev_hy,d.dev_name,d.group_ID,d.dev_loc,d.dev_addr,d.org_code,d.Cpy_Id,d.dev_lxr,d.dev_lxrtel from ZHSQ_WARN_RECORD t left join zhsq_device_info d on t.dev_id=d.dev_code  where   t.record_time>=date_format(now(),'%Y-%m-%d %H:%i:%s') and  t.record_time<date_format(now(),'%Y-%m-%d %H:%i:%s')";
            if (!string.IsNullOrEmpty(where))
            {
                sql += where;
            }

            return FangYar.Common.MySqlHelper.QueryTable(sql);
        }
        public DataTable GetTodayWarnList(string where)
        {
            string sql = " select b.*,a.record_state,a.alarmvalue,a.gathertype,a.creattime  from ZHSQ_DEVICE_INFO  b  right join zhsq_warn_record  a on a.gather=b.dev_code ";

            if (!string.IsNullOrEmpty(where))
            {
                sql += " where " + where;
            }

            sql += " order by a.creattime desc ";

            return FangYar.Common.MySqlHelper.QueryTable(sql);
        }
        public string GetALLWarnList(int pageindex, int pagesize, string where)
        {
            string sql = " select b.*,a.record_state,a.alarmvalue,a.gathertype,a.creattime  from ZHSQ_DEVICE_INFO  b  right join zhsq_warn_record  a on a.gather=b.dev_code ";

            if (!string.IsNullOrEmpty(where))
            {
                sql += " where " + where;
            }

            sql += " order by a.creattime desc ";
            string table = "(" + sql + ")";
            return QueryJsonList(pageindex, pagesize, table, "1=1");
        }
        public string GetRealDataJson(string devcode)
        {
            string loudian = "", wendu = "", dianliu = "", dianya = "";
            for (int fg = 1; fg <= 4; fg++)
            {
                DataTable dt = GetRealData(devcode, fg);


                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string cid = dt.Rows[i]["CIRCUINUM"].ToString();
                    string abc = "";
                    if (cid == "1") { abc = "A"; }
                    else if (cid == "2") { abc = "B"; }
                    else if (cid == "3") { abc = "C"; }
                    if (dt.Rows[i]["GATHERTYPE"].ToString() == "1")
                    {
                        if (!loudian.Contains(cid + ":"))
                        {
                            loudian += dt.Rows[i]["CIRCUINUM"].ToString() + ":" + dt.Rows[i]["READVALUE"].ToString() + "|";
                        }
                    }
                    else if (dt.Rows[i]["GATHERTYPE"].ToString() == "2")
                    {
                        if (!loudian.Contains(cid + ":"))
                        {
                            wendu += dt.Rows[i]["CIRCUINUM"].ToString() + ":" + dt.Rows[i]["READVALUE"].ToString() + "|";
                        }
                    }
                    else if (dt.Rows[i]["GATHERTYPE"].ToString() == "3")
                    {
                        if (!loudian.Contains(abc + ":"))
                        {
                            dianliu += abc + ":" + dt.Rows[i]["READVALUE"].ToString() + "|";
                        }
                    }
                    else if (dt.Rows[i]["GATHERTYPE"].ToString() == "4")
                    {
                        if (!loudian.Contains(abc + ":"))
                        {
                            dianya += abc + ":" + dt.Rows[i]["READVALUE"].ToString() + "|";
                        }
                    }
                }
            }
            string json = "{";
            json += "\"loudian\":\"" + loudian.Trim('|') + "\",";
            json += "\"wendu\":\"" + wendu.Trim('|') + "\",";
            json += "\"dianliu\":\"" + dianliu.Trim('|') + "\",";
            json += "\"dianya\":\"" + dianya.Trim('|') + "\"";
            // json=json.Trim(',');
            json += "}";
            return json;
        }
        public string GetRealDataJson2(string devcode)
        {
            DataTable dt = GetRealData(devcode);
            string json = "{";
            string loudian = "", wendu = "", dianliu = "", dianya = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["GATHERTYPE"].ToString() == "1")
                {
                    loudian += dt.Rows[i]["CIRCUINUM"].ToString() + ":" + dt.Rows[i]["READVALUE"].ToString() + "|";
                }
                else if (dt.Rows[i]["GATHERTYPE"].ToString() == "2")
                {
                    wendu += dt.Rows[i]["CIRCUINUM"].ToString() + ":" + dt.Rows[i]["READVALUE"].ToString() + "|";
                }
                else if (dt.Rows[i]["GATHERTYPE"].ToString() == "3")
                {
                    dianliu += dt.Rows[i]["CIRCUINUM"].ToString() + ":" + dt.Rows[i]["READVALUE"].ToString() + "|";
                }
                else if (dt.Rows[i]["GATHERTYPE"].ToString() == "4")
                {
                    dianya += dt.Rows[i]["CIRCUINUM"].ToString() + ":" + dt.Rows[i]["READVALUE"].ToString() + "|";
                }
            }
            json += "\"loudian\":\"" + loudian.Trim('|') + "\",";
            json += "\"wendu\":\"" + wendu.Trim('|') + "\",";
            json += "\"dianliu\":\"" + dianliu.Trim('|') + "\",";
            json += "\"dianya\":\"" + dianya.Trim('|') + "\"";
            // json=json.Trim(',');
            json += "}";
            return json;
        }
        public DataTable GetRealData(string devcode, int type)
        {
            //  string sql = " select t.* from (select row_number()over(partition by gathertype order by creattime desc)rn,  z.gathertype,z.readvalue from  ZHSQ_REAL_RECORD z where z.gather=" + devcode + " ) t where t.rn=1";

            string sql = "select  readvalue,gathertype, circuinum from ZHSQ_REAL_RECORD where gather='" + devcode + "' and gathertype='" + type + "'   and creattime= (select max(creattime)  from ZHSQ_REAL_RECORD  where gather='" + devcode + "'  and gathertype='" + type + "' )";
            return FangYar.Common.MySqlHelper.QueryTable(sql);
        }

        public DataTable GetRealData(string devcode)
        {
            //  string sql = " select t.* from (select row_number()over(partition by gathertype order by creattime desc)rn,  z.gathertype,z.readvalue from  ZHSQ_REAL_RECORD z where z.gather=" + devcode + " ) t where t.rn=1";

            string sql = "select  readvalue,gathertype, circuinum from ZHSQ_REAL_RECORD where gather='" + devcode + "'   and creattime= (select max(creattime)  from ZHSQ_REAL_RECORD  where gather='" + devcode + "')";
            return FangYar.Common.MySqlHelper.QueryTable(sql);
        }

        #region 分页
        public string QueryJsonList(int PageIndex, int PageSize, string table, string strwhere)
        {
            return dal.QueryJsonList(PageIndex, PageSize, table, strwhere);
        }
        public string QueryJsonList(int PageIndex, int PageSize, string table, string strwhere, string order)
        {
            return dal.QueryJsonList(PageIndex, PageSize, table, strwhere, order);
        }
        public DataTable QueryPage(int PageIndex, int PageSize, string table, string strwhere)
        {
            return dal.QueryPage(PageIndex, PageSize, table, strwhere);
        }
        public DataTable QueryPage(int PageIndex, int PageSize, string table, string strwhere, string order)
        {
            return dal.QueryPage(PageIndex, PageSize, table, strwhere, order);
        }
        #endregion

        public long getCode(string field, string table, string where)
        {
            return FangYar.Common.MySqlHelper.GetMaxID(field, table, where);
        }
        public int getCount(string sqlwhere, string table)
        {
            return FangYar.Common.MySqlHelper.GetCount(sqlwhere, table);
        }
        #region  

        /// <summary>
        /// 分组查询,逗号隔开
        /// </summary>
        /// <param name="keyField"></param>
        /// <param name="groupField"></param>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static string GetTableIDS(string keyField, string groupField, string tableName, string condition)
        {
            string RecordCount = "";
            string sql = "select group_concat(" + keyField + ") as " + keyField + " from " + tableName + " " + condition + " group by " + groupField;
            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql);
            if (dt.Rows.Count > 0)
            {
                RecordCount = dt.Rows[0][0].ToString();
            }
            dt.Clear();
            dt.Dispose();
            return RecordCount;
        }
        #endregion
        //获取报警原因
        public string GetBJReasonJson(string table, string where)
        {
            return FangYar.Common.JsonHelper.ToJson(GetBjReasonDT(table, where));
        }
        //获取报警原因
        public DataTable GetBjReasonDT(string table, string where)
        {

            string sql = "select count(*) as sl  ,q.warn_type from (";
            sql += "select r.dev_id, r.warn_type,i.org_code from " + table + " r inner  join  ZHSQ_DEVICE_INFO i on i.dev_code=r.dev_id ";
            sql += "where" + where;
            sql += ")q group by q.warn_type";
            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql);
            return dt;

        }

        public DataTable GetAllWarnList(string table, string where)
        {
            //  string sql = " select b.*,a.record_state,a.alarmvalue,a.gathertype,a.creattime  from ZHSQ_DEVICE_INFO  b  right join zhsq_warn_record  a on a.gather=b.dev_code  where   b.org_code in(select id from tbl_sys_org where area_id   in(select area_code from tbl_sys_area  WHERE   area_code like  '37%'   ))order by a.creattime desc ";
            string sql = "select b.*,a.record_state,a.alarmvalue,a.gathertype,a.creattime ,o.org_name from ZHSQ_DEVICE_INFO  b ";
            sql += " right join  " + table + "  a on a.gather=b.dev_code left join tbl_sys_org o on o.id=b.org_code ";
            sql += " where b. " + where;
            sql += "order by a.creattime desc ";
            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql);
            return dt;
        }

        public DataTable getUserMenu(string where)
        {
            string sql = " select m2.* from tbl_sys_rolemenu m1 left join TBL_SYS_MENU m2 on m1.menu_id=m2.id ";
            sql += " where  " + where;

            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql);
            return dt;
        }

        //根据入库登录名，更新登录名获取相应 姓名
        public DataTable getUserName(string A_PER, string U_PER)
        {
            string sql = "select (select t1.users_name from tbl_sys_users t1 where t1.users_uid = '" + A_PER + "' ) A_PER_NAME,(select t2.users_name from tbl_sys_users t2 where t2.users_uid = '" + U_PER + "') U_PER_NAME from dual";
            //string sql = " select t1.users_name A_PER_NAME,t2.users_name U_PER_NAME from tbl_sys_users t1 , tbl_sys_users t2 where t1.users_uid = '" + A_PER + "' and t2.users_uid = '" + U_PER + "' ";
            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql);
            return dt;
        }

        //根据入库登录名，更新登录名获取相应 姓名
        public DataTable getHomeCount(string strwhere)
        {
            //string sql = " select t1.users_name A_PER_NAME,t2.users_name U_PER_NAME from tbl_sys_users t1 , tbl_sys_users t2 where t1.users_uid = '" + A_PER + "' and t2.users_uid = '" + U_PER + "' ";

            StringBuilder sql = new StringBuilder();
            //在岗
            sql.Append("SELECT 'PERSON'as BC,0 as ty, count(1) as count FROM TBL_SYS_EMP h where  h.IS_WORK = '0' and IS_ADMIN='0' " + strwhere);
            //请假
            sql.Append(" union all ");
            sql.Append("SELECT 'PERSON',3 as tyz, count(1) FROM TBL_SYS_EMP h where  h.IS_WORK = '3' and IS_ADMIN='0' " + strwhere);
            //执勤可用
            sql.Append(" union all ");
            sql.Append("SELECT 'CAR1' as bc,0 as ty, count(1) as num FROM TBL_SYS_CAR t  where t.class='1' and t.isrun='0' " + strwhere);
            //执勤在用
            sql.Append(" union all ");
            sql.Append("SELECT 'CAR1' as bc,1 as ty, count(1) as num FROM TBL_SYS_CAR t  where t.class='1' and t.isrun='1' " + strwhere);
            //执勤故障
            sql.Append(" union all ");
            sql.Append("SELECT 'CAR1' as bc,2 as ty, count(1) as num FROM TBL_SYS_CAR t  where t.class='1' and t.isrun='2' " + strwhere);
            //公务可用
            sql.Append(" union all ");
            sql.Append("SELECT 'CAR0' as bc,0 as ty, count(1) as num FROM TBL_SYS_CAR t  where t.class='0' and t.isrun='0'" + strwhere);
            //公务在用
            sql.Append(" union all ");
            sql.Append("SELECT 'CAR0' as bc,1 as ty, count(1) as num FROM TBL_SYS_CAR t  where t.class='0' and t.isrun='1'" + strwhere);
            //公务故障
            sql.Append(" union all ");
            sql.Append("SELECT 'CAR0' as bc,2 as ty, count(1) as num FROM TBL_SYS_CAR t  where t.class='0' and t.isrun='2'" + strwhere);
            //摄像头
            sql.Append(" union all ");
            sql.Append("SELECT 'CAMERA',0, count(1) FROM TBL_CAMERA y where 1=1  " + strwhere);

            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql.ToString());
            return dt;
        }


    }
}