/// <summary>
/// 判断护林员当前是否在线
/// </summary>
/// <example>
/// 传递参数：
/// sw.USERID 护林员ID列表，多用户以逗号分隔
/// sw.SearchTime 查询时间，空默认取当前时间
/// sw.ORGNO 组织机构编码，非空取该组织机构下的所有护林员
/// </example>
/// <param name="sw">参见模型</param>
/// <returns>参见模型</returns>
public static DataTable getOnLineDtByOrgno(T_IPS_REALDATATEMPORARYSW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" SELECT a.BYORGNO,a.HNAME,a.PHONE,a.ONSTATE,a.HID,b.SBTIME,b.ISOUTRAIL");
sb.AppendFormat(" FROM T_IPSFR_USER a left outer join T_IPS_REALDATATEMPORARY b on a.HID=b.USERID");
sb.AppendFormat(" where a.ISENABLE=1");//护林员启用状态
if (string.IsNullOrEmpty(sw.USERID) == false)
{
sb.AppendFormat(" and a.HID in({0})", sw.USERID);
}
if (string.IsNullOrEmpty(sw.SearchTime))
{
sb.AppendFormat(" and DATEDIFF(mi,SBTIME,'{1}') <= '{0}' ", ConfigCls.inLineTimeInterval().ToString(), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
sb.AppendFormat(" and DATEDIFF(mi,SBTIME,'{1}') >= '{0}'", ConfigCls.inLineTimeInterval().ToString(), sw.SearchTime);
}
if (string.IsNullOrEmpty(sw.ORGNO) == false)
{
if (PublicCls.OrgIsShi(sw.ORGNO))
{
sb.AppendFormat(" and a.BYORGNO like'{0}%'", PublicCls.getShiIncOrgNo(sw.ORGNO));
}
else if (PublicCls.OrgIsXian(sw.ORGNO))
{
sb.AppendFormat(" and a.BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.ORGNO));
}
else if (PublicCls.OrgIsZhen(sw.ORGNO))
{
sb.AppendFormat(" and a.BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(sw.ORGNO));
}
else if (PublicCls.OrgIsCun(sw.ORGNO))
{
sb.AppendFormat(" and a.BYORGNO like '{0}'", PublicCls.getCunIncOrgNo(sw.ORGNO));
}
}
if (string.IsNullOrEmpty(sw.PhoneHname) == false)
{
sb.AppendFormat(" AND (a.PHONE like '%{0}%' or a.HNAME like '%{0}%')", ClsSql.EncodeSql(sw.PhoneHname));
}
return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]);
}
/// <summary>
/// 获取数据
/// </summary>
/// <returns>参见模型</returns>
public static DataTable getDT(T_IPSFR_USER_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT a.HID, a.HNAME, a.SN, a.PHONE, a.SEX, a.BIRTH, a.ONSTATE, a.BYORGNO,a.MOBILEPARAMLIST, a.ISENABLE,a.PATROLLENGTH, b.ORGNAME");
sb.AppendFormat(" FROM T_IPSFR_USER AS a LEFT OUTER JOIN T_SYS_ORG AS b ON a.BYORGNO = b.ORGNO");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.HID) == false)
{
if (sw.HID.Split(',').Length > 1)
{
sb.AppendFormat(" AND a.HID in({0})", ClsSql.EncodeSql(sw.HID));
}
else
{
sb.AppendFormat(" AND a.HID ='{0}'", ClsSql.EncodeSql(sw.HID));
}
}
if (string.IsNullOrEmpty(sw.HNAME) == false)
{
sb.AppendFormat(" AND a.HNAME like '%{0}%'", ClsSql.EncodeSql(sw.HNAME));
}
if (string.IsNullOrEmpty(sw.SN) == false)
{
sb.AppendFormat(" AND a.SN like '%{0}%'", ClsSql.EncodeSql(sw.SN));
}
if (string.IsNullOrEmpty(sw.PHONE) == false)
{
if (sw.PHONE.Length == 11)//精确查询
{
sb.AppendFormat(" AND a.PHONE= '{0}'", ClsSql.EncodeSql(sw.PHONE));
}
else //模糊查询
{
sb.AppendFormat(" AND a.PHONE like '%{0}%'", ClsSql.EncodeSql(sw.PHONE));
}
}
if (string.IsNullOrEmpty(sw.PHONELIST) == false)
{
if (sw.PHONELIST.Split(',').Length > 1)
{
sb.AppendFormat(" AND PHONE in({0})", ClsSql.SwitchStrToSqlIn(sw.PHONELIST));
}
else
{
sb.AppendFormat(" AND PHONE ='{0}'", ClsSql.EncodeSql(sw.PHONELIST));
}
}
if (string.IsNullOrEmpty(sw.PATROLLENGTH) == false)
{
sb.AppendFormat(" AND a.PATROLLENGTH = '{0}'", ClsSql.EncodeSql(sw.PATROLLENGTH));
}
if (string.IsNullOrEmpty(sw.SEX) == false)
{
sb.AppendFormat(" AND a.SEX = '{0}'", ClsSql.EncodeSql(sw.SEX));
}
if (string.IsNullOrEmpty(sw.ONSTATE) == false)
{
sb.AppendFormat(" AND a.ONSTATE = '{0}'", ClsSql.EncodeSql(sw.ONSTATE));
}
if (string.IsNullOrEmpty(sw.ISENABLE) == false)//默认取有效用户
{
sb.AppendFormat(" AND a.ISENABLE = '{0}'", ClsSql.EncodeSql(sw.ISENABLE));
}
if (string.IsNullOrEmpty(sw.PhoneHname) == false)
{
sb.AppendFormat(" AND (a.PHONE like '%{0}%' or a.HNAME like '%{0}%')", ClsSql.EncodeSql(sw.PhoneHname));
}
if (string.IsNullOrEmpty(sw.BYORGNO) == false)
{
if (PublicCls.OrgIsShi(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.BYORGNO));
}
else if (PublicCls.OrgIsXian(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.BYORGNO));
}
else if (PublicCls.OrgIsZhen(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(sw.BYORGNO));
}
else if (PublicCls.OrgIsCun(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getCunIncOrgNo(sw.BYORGNO));
}
}
if (string.IsNullOrEmpty(sw.Orgs) == false)
{
string[] arr = sw.Orgs.Split(',');
string tmpOrg = "";
for (int i = 0; i < arr.Length; i++)
{
if (arr[i].Length == 15)
{
if (tmpOrg != "")
{
tmpOrg += ",";
}
tmpOrg += arr[i];
}
}
if (tmpOrg != "")
{
string[] arr1 = tmpOrg.Split(',');//循环每个单位
sb.AppendFormat(" and (");
for (int i = 0; i < arr1.Length; i++)
{
if (i > 0)
{
sb.AppendFormat(" or");
}
if (PublicCls.OrgIsShi(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(arr1[i]));
}
else if (PublicCls.OrgIsXian(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(arr1[i]));
}
else if (PublicCls.OrgIsZhen(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(arr1[i]));
}
else if (PublicCls.OrgIsCun(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getCunIncOrgNo(arr1[i]));
}
}
sb.AppendFormat(" )");
}
}
sb.AppendFormat(" ORDER BY a.BYORGNO,a.HNAME ");
DataSet ds = DataBaseClass.FullDataSet(sb.ToString());
return(ds.Tables[0]);
}
