/// <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));
}
/// <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));
}
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));
}
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));
}