2018-12-19离线统计模块总结4

DAL

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Entities;
using System.Data;
using DBUtility;
using BasicItem;

namespace DAL
{
    public class GatewayNodeOnOffDAL
    {

        //获取下拉框
        public DataTable GetGatewayNodeOnOff()
        {
            string strSql = @"select User_No,User_Name from User_Info,Role_Info,UserRole_Info where User_Info.User_ID=UserRole_Info.UR_UserID and Role_Info.Role_ID=UserRole_Info.UR_RoleID and Role_Info.Role_Level in ('Maintenance')";
            
            return DBCommon.GetDataTable(strSql);
        }

        //查询条件
        public DataTable GetGatewayNodeOnOffInfo(string selectDate, string userNo)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select a.DayOnLineStatistics_ID,User_Name,case when(DOLS_NodeOrEquip=0) then '传感器' when(DOLS_NodeOrEquip=1) then '网关' end as DOLS_NodeOrEquip,a.DOLS_Day ,a.DOLS_TotalCount,a.DOLS_OnLineCount,a.DOLS_OffLineCount, ");
            sb.Append(" CONVERT(float, CONVERT(decimal(18, 3),a.DOLS_OffLineCount * 1.0 / (DOLS_TotalCount-DOLS_StopUseCount)) * 100 ) as OfflineRate,");
            sb.Append(" (a.DOLS_OffLineCount-T1.DOLS_OffLineCount) as OffLineCountADD ");
            sb.Append(" from (select t1.DOLS_OffLineCount from DayOnLineStatistics_Infor as t1 where t1.DOLS_Day=CONVERT(date,DATEADD(DAY,-1,'2018-12-12'),23) group by t1.DOLS_OffLineCount) as T1, ");
            sb.Append(" User_Info,Role_Info,UserRole_Info,DayOnLineStatistics_Infor as a");
            sb.Append(" where User_Info.User_ID=UserRole_Info.UR_UserID");
            sb.Append(" and Role_Info.Role_ID=UserRole_Info.UR_RoleID and Role_Info.Role_Level in ('Maintenance')");
            if (selectDate != "")
                sb.Append(" and a.DOLS_Day in ('" + selectDate + "')");
            if (userNo != "")
                sb.Append(" and User_Info.User_No in ('" + userNo + "')");
            sb.Append("order by a.DOLS_Day");
            string strSql = sb.ToString();
            return DBCommon.GetDataTable(strSql);
        }



        //查询前一个月运维人员、统计单位
        public DataTable HisGetGatewayNodeOnOffInfo(string userName,  string DOLSNodeOrEquip,string HisSelectDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select a.DOLS_NodeOrEquip, CONVERT(BIGINT,DATEDIFF(MI,'1970-01-01', a.DOLS_Day)) * 60000 + DATEPART(S,a.DOLS_Day) * 1000 + DATEPART(MS, a.DOLS_Day) as DOLS_Day,");
            sb.Append(" CONVERT(float, CONVERT(decimal(18, 3),a.DOLS_OffLineCount * 1.0 / (a.DOLS_TotalCount-a.DOLS_StopUseCount)) * 100 ) as OfflineRate ");            
            sb.Append(" from User_Info,Role_Info,UserRole_Info,DayOnLineStatistics_Infor as a ");
            sb.Append(" where User_Info.User_ID=UserRole_Info.UR_UserID and Role_Info.Role_ID=UserRole_Info.UR_RoleID ");
            sb.Append(" and Role_Info.Role_Level in ('Maintenance') ");
            if (HisSelectDate != "")
                sb.Append(" and a.DOLS_Day BETWEEN DATEADD(MONTH,-1,'" + HisSelectDate + "') AND '" + HisSelectDate + "'");
            if (userName != "")
                sb.Append(" and User_Info.User_Name in ('" + userName + "')");
            if (DOLSNodeOrEquip != "")
                sb.Append(" and a.DOLS_NodeOrEquip in ('" + DOLSNodeOrEquip + "')");
            sb.Append("order by a.DOLS_Day");
            string strSql = sb.ToString();
            return DBCommon.GetDataTable(strSql);
        }

        //s跳转查询前一个月运维人员、统计单位
        public DataTable sHisGetGatewayNodeOnOffInfo(string userName, string DOLSNodeOrEquip, string HisSelectDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select a.DayOnLineStatistics_ID,User_Name,case when(DOLS_NodeOrEquip=0) then '传感器' when(DOLS_NodeOrEquip=1) then '网关' end as DOLS_NodeOrEquip,");
            sb.Append(" a.DOLS_Day ,a.DOLS_TotalCount,a.DOLS_OnLineCount,a.DOLS_OffLineCount,  ");
            sb.Append(" CONVERT(float, CONVERT(decimal(18, 3),a.DOLS_OffLineCount * 1.0 / (DOLS_TotalCount-DOLS_StopUseCount)) * 100 ) as OfflineRate, ");
            sb.Append(" (a.DOLS_OffLineCount-T1.DOLS_OffLineCount) as OffLineCountADD  from");
            sb.Append(" (select t1.DOLS_OffLineCount from DayOnLineStatistics_Infor as t1 where t1.DOLS_Day=CONVERT(date,DATEADD(DAY,-1,'2018-12-12'),23) group by t1.DOLS_OffLineCount) as T1,  ");
            sb.Append(" User_Info,Role_Info,UserRole_Info,DayOnLineStatistics_Infor as a ");
            sb.Append(" where User_Info.User_ID=UserRole_Info.UR_UserID and Role_Info.Role_ID=UserRole_Info.UR_RoleID and Role_Info.Role_Level in ('Maintenance') ");
            if (HisSelectDate != "")
                sb.Append(" and a.DOLS_Day BETWEEN DATEADD(MONTH,-1,'" + HisSelectDate + "') AND '" + HisSelectDate + "'");
            if (userName != "")
                sb.Append(" and User_Info.User_Name in ('" + userName + "')");
            if (DOLSNodeOrEquip != "")
                sb.Append(" and a.DOLS_NodeOrEquip in ('" + DOLSNodeOrEquip + "')");
            sb.Append("order by a.DOLS_Day");
            string strSql = sb.ToString();
            return DBCommon.GetDataTable(strSql);
        }
    }
}