/// <summary>
 /// 发码器配置查询
 /// </summary>
 /// <param name="strCodeSender">发码器地址</param>
 /// <param name="strName">设备或员工的名称</param>
 /// <param name="intUserType">0表示人员,1表示设备,2表示人员和设备</param>
 /// <param name="dv">要绑定的DataGridView</param>
 /// <returns>true 成功,false 不成功</returns>
 public DataSet N_SearchCodeSetInfo(string strCodeSender, string strName, int intUserType)
 {
     strSql = " Select " +
              " CodeSenderAddress as " + HardwareName.Value(CorpsName.CodeSenderAddress) + ", " +
              " 配置类型=case when CsTypeID=0 then '人员' when CsTypeID=1 then '设备' end, " +
              " 名称= case when CsTypeID=0 then Ei.EmpName when CsTypeID=1 then Ebi.EquName end, " +
              " 所属部门=case when CsTypeID=0 then Di1.DeptName when CsTypeID=1 then Di2.DeptName end, " +
              " Cs.Remark as 备注 " +
              " From " +
              " CodeSender_Set as Cs left join Emp_Info as Ei on Ei.EmpID= Cs.UserID and CsTypeID=0 " +
              " left join Emp_NowCompany as Enc on Enc.EmpID=Ei.EmpID " +
              " left join Dept_Info as Di1 on Di1.DeptID=Enc.DeptID " +
              " left join Equ_BaseInfo as Ebi on Ebi.EquID=Cs.UserID and CsTypeID=1 " +
              " left join Dept_Info as Di2 on Di2.DeptID=Ebi.DeptID " +
              " Where " +
              " CsSetID<>0 ";
     if (!(strCodeSender.Equals("") | strCodeSender.Equals(null)))
     {
         strSql += " And CodeSenderAddress =" + strCodeSender;
     }
     if (!(strName.Equals("") | strName.Equals(null)))
     {
         strSql += " And (Ei.EmpName like '%" + strName + "%' or Ebi.EquName like '%" + strName + "%' )";
     }
     if (!intUserType.Equals(2))
     {
         strSql += " And CsTypeID =" + intUserType.ToString();
     }
     return(dbacc.GetDataSet(strSql));
 }
Example #2
0
        /// <summary>
        /// 查询 区域设置
        /// </summary>
        /// <param name="strTerID">区域ID</param>
        /// <param name="strTerTypeID">区域类别ID</param>
        /// <returns>true: 成功</returns>
        public DataSet N_SearchTerSet(string strTerID, string strTerTypeID)
        {
            bool blIsFirst = false;

            strSql = " Select " +
                     " TerritorialName as 区域名称, " +
                     " TypeName as 区域类别, " +
                     " Si.StationAddress as " + HardwareName.Value(CorpsName.StationAddress) + ", " +
                     " StationPlace as " + HardwareName.Value(CorpsName.StationSplace) + ", " +
                     " StationHeadAddress as " + HardwareName.Value(CorpsName.StaHeadAddress) + ", " +
                     " StationHeadPlace as " + HardwareName.Value(CorpsName.StaHeadSplace) +
                     " From " +
                     " Territorial_Set as Ts left join Territorial_Info as Ti on Ts.TerritorialID=Ti.TerritorialID " +
                     " left join Territorial_Type as Tt on Tt.TerritorialTypeID=Ti.TerritorialTypeID " +
                     " left join Station_Info as Si on Si.StationID=Ts.StationID " +
                     " left join Station_Head_Info as Shi on Shi.StationHeadID=Ts.StationHeadID ";
            if (!strTerID.Equals("0"))
            {
                strSql   += " Where Ti.TerritorialID =" + strTerID;
                blIsFirst = true;
            }
            if (!strTerTypeID.Equals("0"))
            {
                if (blIsFirst)
                {
                    strSql += " And Tt.TerritorialTypeID =" + strTerTypeID;
                }
                else
                {
                    strSql += " Where Tt.TerritorialTypeID =" + strTerTypeID;
                }
            }

            return(dbacc.GetDataSet(strSql));
        }
Example #3
0
 public DataSet N_GetRTInTerritorialInfo_G(int intTerTypeID, string strTerName, bool blIsEmp, string strIsAlarm)
 {
     if (blIsEmp)                                            //人员
     {
         strSql = " Select " +
                  " CodeSenderAddress as " + HardwareName.Value(CorpsName.CodeSenderAddress) + "," +
                  " EmpName as 员工姓名 ," +
                  " DeptName as 部门名称 ," +
                  " TypeName as  区域类别 ," +
                  " Ti.TerritorialName as 区域名称 ," +
                  " 是否报警 = case when Tt.IsAlarm=1 then '报警' else '不报警' end , " +
                  " InTerritorialTime as " + HardwareName.Value(CorpsName.InTerritorialTime) + " , " +
                  " dbo.FunConvertTime(DATEDIFF(ss,InTerritorialTime, getDate())) AS " + HardwareName.Value(CorpsName.StandingTime) +
                  " From " +
                  " RT_TerritorialInfo as Ri left join Emp_Info As Ei on Ei.EmpID=Ri.UserID " +
                  //Czlt-2010-10-21-注销
                  //" left join Emp_NowCompany as En on Ei.EmpID=En.EmpID " +
                  //" left join Dept_Info as Di on En.DeptID=Di.DeptID " +
                  " left join Territorial_Info as Ti on Ri.TerritorialID=Ti.TerritorialID " +
                  " left join Territorial_Type as Tt on Ti.TerritorialTypeID=Tt.TerritorialTypeID " +
                  " Where " +
                  " CsTypeID=0 and CodeSenderaddress in(Select CodeSenderAddress From dbo.RT_InOutMine)";
     }
     else                                                    //设备
     {
         strSql = " Select " +
                  " CodeSenderAddress as " + HardwareName.Value(CorpsName.CodeSenderAddress) + "," +
                  " EquName as 设备名称 ," +
                  " DeptName as 所属部门," +
                  " TypeName as  区域类别," +
                  " Ti.TerritorialName as 区域名称 ," +
                  " 是否报警 = case when Tt.IsAlarm=1 then '报警' else '不报警' end , " +
                  " InTerritorialTime as " + HardwareName.Value(CorpsName.InTerritorialTime) + " , " +
                  " dbo.FunConvertTime(DATEDIFF(ss,InTerritorialTime, getDate())) AS " + HardwareName.Value(CorpsName.StandingTime) +
                  " From " +
                  " RT_TerritorialInfo as Ri left join Equ_BaseInfo as Eb on Ri.UserID=Eb.EquID " +
                  " left join Dept_Info as Di on Eb.DeptID=Di.DeptID " +
                  " left join Territorial_Info as Ti on Ri.TerritorialID=Ti.TerritorialID " +
                  " left join Territorial_Type as Tt on Ti.TerritorialTypeID=Tt.TerritorialTypeID " +
                  " Where " +
                  " CsTypeID=1 and CodeSenderaddress in(Select CodeSenderAddress From dbo.RT_InOutMine)";
     }
     if (intTerTypeID != 0)                                     //区域类型
     {
         strSql += " And Tt.TerritorialTypeID= " + intTerTypeID.ToString();
     }
     if (strTerName != "所有" && strTerName != null)                                  //区域名称
     {
         strSql += " And Ri.TerritorialName='" + strTerName + "'";
     }
     if (strIsAlarm == "报警")
     {
         strSql += " And Tt.IsAlarm =1";
     }
     else if (strIsAlarm == "不报警")
     {
         strSql += " And Tt.IsAlarm =0";
     }
     return(dbacc.GetDataSet(strSql));
 }
        /// <summary>
        /// 发码器查询
        /// </summary>
        /// <param name="strCodeSender">发码器编号</param>
        /// <param name="strCodeSenderStateID">发码器状态ID</param>
        /// <param name="dv">要绑定的DataGridView</param>
        /// <returns>true 成功,false 不成功</returns>
        public DataSet N_SearchCodeInfo(string strCodeSender, string strCodeSenderStateID)
        {
            bool blIsFirst = false;

            strSql = " Select " +
                     " CodeSenderAddress as " + HardwareName.Value(CorpsName.CodeSenderAddress) + ", " +
                     " Et.Title as 发码器状态, " +
                     " 是否使用=case when IsCodeSenderUser=1 then '使用' when IsCodeSenderUser=2 then '未使用' end, " +
                     " Ci.Remark as 备注 " +
                     " From " +
                     " CodeSender_Info as Ci left join EnumTable as Et on Ci.CodeSenderStateID=Et.EnumID and Et.FunID=2 ";
            if (!(strCodeSender.Equals("") | strCodeSender.Equals(null)))
            {
                strSql   += " where CodeSenderAddress =" + strCodeSender;
                blIsFirst = true;
            }
            if (!strCodeSenderStateID.Equals("0"))
            {
                if (blIsFirst)
                {
                    strSql += " And CodeSenderStateID = " + strCodeSenderStateID;
                }
                else
                {
                    strSql += " Where CodeSenderStateID =" + strCodeSenderStateID;
                }
            }

            return(dbacc.GetDataSet(strSql));
        }
        /// <summary>
        /// 查询实时进出接收器信息_设备
        /// </summary>
        /// <param name="strStartTime">开始时间</param>
        /// <param name="strEndTime">结束时间</param>
        /// <param name="strStationAddress">分站编号</param>
        /// <param name="strStationHeadAddress">接收器号</param>
        /// <param name="strName">设备名称</param>
        /// <param name="strCard">发码器编号</param>
        /// <param name="strEquNO">设备编号</param>
        /// <param name="strDeptName">部门</param>
        /// <returns></returns>
        public DataSet N_GetRTInOutStationHeadInfo_Equ(
            string strStartTime,
            string strEndTime,
            string strStationAddress,
            string strStationHeadAddress,
            string strName,
            string strCard,
            string strEquNO,
            string strDeptName)
        {
            strSql = " Select " +
                     " Ri.CodeSenderAddress As " + HardwareName.Value(CorpsName.CodeSenderAddress) + ", " +
                     " Eb.EquName As 设备名称, " +
                     " Eb.EquNO As 设备编号, " +
                     " Di.DeptName As 部门, " +
                     " Shi.StationAddress As " + HardwareName.Value(CorpsName.StationAddress) + ", " +
                     " Shi.StationHeadAddress As " + HardwareName.Value(CorpsName.StaHeadAddress) + ", " +
                     " Shi.StationHeadPlace As " + HardwareName.Value(CorpsName.StaHeadSplace) + ", " +
                     " Ri.InAntennaPlace As 所在位置, " +
                     " Ri.InStationHeadTime As 进入接收器时间, " +
                     " dbo.FunConvertTime((Select DateDiff(ss, Ri.InStationHeadTime, getDate()))) As 持续时间 " +
                     " From RT_InStationHeadInfo As Ri " +
                     " Left Join Equ_BaseInfo as Eb on Ri.UserID=Eb.EquID and CsTypeID=1 " +
                     " Left Join Dept_Info as Di on Eb.DeptID=Di.DeptID " +
                     " Left Join Station_Head_Info As Shi On Shi.StationHeadID = Ri.StationHeadID " +
                     " Where " +
                     " Ri.CsTypeID = 1 And Ri.InStationHeadTime >= '" + strStartTime + "' And Ri.InStationHeadTime <= '" + strEndTime + "' ";

            if (!(strName.Equals("") | strName.Equals(null)))
            {
                strSql += " And Eb.EquName like '%" + strName + "%' ";
            }

            if (!(strEquNO.Equals("") | strEquNO.Equals(null)))
            {
                strSql += " And Eb.EquNO = '" + strEquNO + "' ";
            }

            if (!(strCard.Equals("") | strCard.Equals(null)))
            {
                strSql += " And Ri.CodeSenderAddress = " + strCard;
            }

            if (!(strDeptName.Equals("")))
            {
                strSql += " And ( Di.DeptID = " + strDeptName + " ) ";
            }

            if (!strStationAddress.Equals("0"))
            {
                strSql += " And Shi.StationAddress = " + strStationAddress;
            }

            if (!strStationHeadAddress.Equals("0"))
            {
                strSql += " And Shi.StationHeadAddress = " + strStationHeadAddress;
            }

            return(dbacc.GetDataSet(strSql));
        }
        public DataSet N_SearchRTInOutAntennaInfo(
            string strStartTime,
            string strEndTime,
            string strCard,
            string strStationAddress,
            string strStationHeadAddress,
            int intUserType)
        {
            strSql = " Select " +
                     " Ri.CodeSenderAddress As " + HardwareName.Value(CorpsName.CodeSenderAddress) + ", " +
                     " StationAddress As " + HardwareName.Value(CorpsName.StationAddress) + ", " +
                     " StationHeadAddress As " + HardwareName.Value(CorpsName.StaHeadAddress) + ", " +
                     " StationHeadDetectTime As 接收器监测时间, " +
                     " StationHeadAntennaA As 天线A, " +
                     " StationHeadAntennaB As 天线B, " +
                     " 进出状态=case InStationHeadAntenna when 1 then '进读卡分站' when 2 then '进读卡分站' else '出读卡分站' end, " +
                     " LastStationAddress As 上次" + HardwareName.Value(CorpsName.StationAddress) + ", " +
                     " LastStationHeadAddress As 上次" + HardwareName.Value(CorpsName.StaHeadAddress) + ", " +
                     " LastStationHeadAntennaA As 上次天线A, " +
                     " LastStationHeadAntennaB As 上次天线B " +
                     " From " +
                     " RT_InOutStation  as Ri " +
                     " left join CodeSender_Set as Css on Ri.CodeSenderAddress=Css.CodeSenderAddress " +
                     " Where " +
                     " StationHeadDetectTime >= '" + strStartTime + "' And StationHeadDetectTime <= '" + strEndTime + "' ";

            if (!(strCard.Equals("") | strCard.Equals(null)))
            {
                strSql += " And Ri.CodeSenderAddress = " + strCard;
            }

            ////发码器类型
            if (intUserType.Equals(2))  //未登记发码器
            {
                strSql += " And CsTypeID is Null ";
            }
            else if (intUserType.Equals(1) || intUserType.Equals(0))    //人员、设备
            {
                strSql += " And CsTypeID =" + intUserType;
            }

            if (!(strStationAddress.Equals("0")))
            {
                strSql += " And StationAddress = " + strStationAddress;
            }

            if (!(strStationHeadAddress.Equals("0")))
            {
                strSql += " And StationHeadAddress = " + strStationHeadAddress;
            }

            return(dbacc.GetDataSet(strSql));
        }
        /*
         * 方法
         */

        #region [ 方法: 查询分站信息 ]

        /// <summary>
        /// 查询分站信息
        /// </summary>
        /// <returns></returns>
        public DataSet N_GetStationInfo()
        {
            strSql = " Select " +
                     " StationAddress as " + HardwareName.Value(CorpsName.StationAddress) + ", " +
                     " StationPlace as " + HardwareName.Value(CorpsName.StationSplace) + ", " +
                     " StationTel as 联系电话, " +
                     " Et1.Title as 状态 " +
                     " From " +
                     " Station_Info  as Si left join EnumTable as Et1 on Si.StationState=Et1.EnumID and Et1.FunID=7 " +
                     " left join EnumTable as Et2 on Si.StationTypeID=Et2.EnumID and Et2.FunID=1 " +
                     " Order By " +
                     " StationAddress ";
            return(dbacc.GetDataSet(strSql));
        }
 /// <summary>
 /// 按分站编号查询接收器信息
 /// </summary>
 /// <param name="strStationAddress">分站编号</param>
 /// <returns></returns>
 public DataSet N_GetStationInfo(string strStationAddress)
 {
     strSql = " Select " +
              " StationHeadAddress as " + HardwareName.Value(CorpsName.StaHeadAddress) + ", " +
              " StationHeadPlace as " + HardwareName.Value(CorpsName.StaHeadSplace) + ", " +
              " StationHeadTel as 联系电话, " +
              " Et1.Title as 状态, " +
              " AntennaA as 天线A位置, " +
              " AntennaB as 天线B位置 " +
              " From " +
              " Station_Head_Info  as Shi left join EnumTable as Et1 on Shi.StationHeadState=Et1.EnumID and Et1.FunID=7 " +
              " left join EnumTable as Et2 on Shi.StationHeadTypeID=Et2.EnumID and Et2.FunID=1 " +
              " Where " +
              " StationAddress=" + strStationAddress;
     return(dbacc.GetDataSet(strSql));
 }
        /// <summary>
        /// 获取超时信息
        /// </summary>
        /// <param name="strName">姓名</param>
        /// <param name="strCard">发码器编号</param>
        /// <param name="strDeptID">部门ID</param>
        /// <param name="strWorkTypeId">工种ID</param>
        /// <param name="strDutyId">职务ID</param>
        /// <returns></returns>
        public DataSet N_GetOverTimeInfo(string strName, string strCard, string strDeptID, string strWorkTypeId, string strDutyId)
        {
            strSql = " Select " +
                     " CodeSenderAddress as " + HardwareName.Value(CorpsName.CodeSenderAddress) + ", " +
                     " EmpName as 员工姓名 , " +
                     " DeptName as 部门 , " +
                     " DutyName as 职务 , " +
                     " WtName as 工种 , " +
                     " StartOverTime as 开始超时时间 , " +
                     " dbo.FunConvertTime(DATEDIFF(second, StartOverTime, getdate())) as 持续时长 " +
                     " From" +
                     //" RT_OverTimeInfo as Ro left join Emp_Info as Ei on Ro.UserID=Ei.EmpID " +
                     " RT_OverTimeInfo_View as Ro left join Emp_Info as Ei on Ro.UserID=Ei.EmpID " +
                     " left join Emp_NowCompany as En on Ei.EmpID=En.EmpID " +
                     " left join Dept_Info as Dei on En.DeptID=Dei.DeptID " +
                     " left join Duty_Info as Dio on En.DutyID=Dio.DutyID " +
                     " left join Emp_WorkType as Ew on Ew.EmpID=Ei.EmpID and IsEnable=1 " +
                     " left join WorkType_Info as Wi on Wi.WorkTypeID=Ew.WorkTypeID " +
                     " Where " +
                     " CsTypeID=0 ";
            // " Hi.StationHeadDetectTime >= '" + strStartTime + "' And Hi.StationHeadDetectTime <= '" + strEndTime + "' ";
            if (strName != "" && strName != null)
            {
                strSql += " And Ei.EmpName like  '%" + strName + "%' ";
            }

            if (strCard != "" && strCard != null)
            {
                strSql += " And Ro.CodeSenderAddress = " + strCard;
            }

            if (strDeptID != "" && strDeptID != null)
            {
                strSql += " And ( Dei.DeptID = " + strDeptID + ") ";
            }

            if (strWorkTypeId != "0" && strWorkTypeId != null)
            {
                strSql += " And Wi.WorkTypeID = " + strWorkTypeId;
            }

            if (strDutyId != "0" && strDutyId != null)
            {
                strSql += " And Dio.DutyID = " + strDutyId;
            }
            return(dbacc.GetDataSet(strSql));
        }
Example #10
0
 public DataSet GetAreaTable(string str)
 {
     strSql = " Select " +
              " CodeSenderAddress as " + HardwareName.Value(CorpsName.CodeSenderAddress) + "," +
              " EmpName as 员工姓名 ," +
              " DeptName as 部门名称 ," +
              " TypeName as  区域类别 ," +
              " Ti.TerritorialName as 区域名称 ," +
              " 是否报警 = case when Tt.IsAlarm=1 then '报警' else '不报警' end , " +
              " InTerritorialTime as " + HardwareName.Value(CorpsName.InTerritorialTime) + " , " +
              " dbo.FunConvertTime(DATEDIFF(ss,InTerritorialTime, getDate())) AS " + HardwareName.Value(CorpsName.StandingTime) +
              " From " +
              " RT_TerritorialInfo as Ri left join Emp_Info As Ei on Ei.EmpID=Ri.UserID " +
              " left join Emp_NowCompany as En on Ei.EmpID=En.EmpID " +
              " left join Dept_Info as Di on En.DeptID=Di.DeptID " +
              " left join Territorial_Info as Ti on Ri.TerritorialID=Ti.TerritorialID " +
              " left join Territorial_Type as Tt on Ti.TerritorialTypeID=Tt.TerritorialTypeID " +
              " Where CsTypeID=0";
     strSql += " And TypeName like '%" + str + "%' And Tt.IsAlarm =1";
     return(dbacc.GetDataSet(strSql));
 }
        /// <summary>
        /// 查询实时进出接收器信息_人员
        /// </summary>
        /// <param name="strStartTime">开始时间</param>
        /// <param name="strEndTime">结束时间</param>
        /// <param name="strStationAddress">分站编号</param>
        /// <param name="strStationHeadAddress">接收器号</param>
        /// <param name="strName">姓名</param>
        /// <param name="strCard">发码器</param>
        /// <param name="strWorkTypeId">工种</param>
        /// <param name="strDeptName">部门</param>
        /// <param name="intUserType">用户类型 1人 2设备</param>
        /// <returns></returns>
        public DataSet N_GetRTInOutStationHeadInfo(
            string strStartTime,
            string strEndTime,
            string strStationAddress,
            string strStationHeadAddress,
            string strName,
            string strCard,
            string strWorkTypeId,
            string strDeptName,
            int intUserType)
        {
            strSql = " Select " +
                     " Ri.CodeSenderAddress As " + HardwareName.Value(CorpsName.CodeSenderAddress) + ", " +
                     " Ei.EmpName As 姓名, " +
                     " Di.DeptName As 部门, " +
                     " Wi.WtName As 工种, " +
                     " Si.StationAddress As " + HardwareName.Value(CorpsName.StationAddress) + ", " +
                     " Si.StationHeadAddress As " + HardwareName.Value(CorpsName.StaHeadAddress) + ", " +
                     " Si.StationHeadPlace As " + HardwareName.Value(CorpsName.StaHeadSplace) + ", " +
                     " Ri.InAntennaPlace As 所在位置, " +
                     " Ri.InStationHeadTime As 进入接收器时间, " +
                     " dbo.FunConvertTime((Select DateDiff(ss, Ri.InStationHeadTime, getDate()))) As 持续时间 " +
                     " From RT_InStationHeadInfo As Ri " +
                     " Left Join Emp_Info As Ei On Ei.EmpID = Ri.UserID " +
                     " Left Join Emp_NowCompany As En On En.EmpID = Ri.UserID " +
                     " Left Join Dept_Info As Di On Di.DeptID = En.DeptID " +
                     " Left Join Emp_WorkType As Ew On Ew.EmpID = Ri.UserID and Ew.IsEnable=1" +
                     " Left Join WorkType_Info As Wi On Wi.WorkTypeID = Ew.WorkTypeID " +
                     " Left Join Station_Head_Info As Si On Si.StationHeadID = Ri.StationHeadID " +
                     " Where " +
                     " Ri.CsTypeID = " + intUserType.ToString() + " And Ri.InStationHeadTime >= '" + strStartTime + "' And Ri.InStationHeadTime <= '" + strEndTime + "' ";

            if (!(strName.Equals("") | strName.Equals(null)))
            {
                strSql += " And Ei.EmpName like '%" + strName + "%' ";
            }

            if (!(strCard.Equals("") | strCard.Equals(null)))
            {
                strSql += " And Ri.CodeSenderAddress = " + strCard;
            }

            if (!(strDeptName.Equals("")))
            {
                strSql += " And ( Di.DeptID = " + strDeptName + " ) ";
            }

            if (!strWorkTypeId.Equals("0"))
            {
                strSql += " And Wi.WorkTypeID = " + strWorkTypeId;
            }

            if (!strStationAddress.Equals("0"))
            {
                strSql += " And Si.StationAddress = " + strStationAddress;
            }

            if (!strStationHeadAddress.Equals("0"))
            {
                strSql += " And Si.StationHeadAddress = " + strStationHeadAddress;
            }

            return(dbacc.GetDataSet(strSql));
        }