public static Device GetDeviceByID_DB(long id)
{
try
{
string strSql = "select * from Device where IsValid=1 and Id=" + id;
DataTable table = DbHelperSQL.Query(strSql).Tables[0];
if (table.Rows.Count != 0)
{
ModelHandler <Device> modelHandler = new ModelHandler <Device>();
DataRow dataRow = table.Rows[0];
Device device = modelHandler.FillModel(dataRow);
lock (_deviceNodeConllection)
{
if (!_deviceNodeConllection.ContainsKey(device.Id))
{
_deviceNodeConllection.Add(device.Id, device);
}
else
{
_deviceNodeConllection[device.Id] = device;
}
}
District d5 = DistrictModule.ReturnDistrictInfo(device.DistrictId);
District d4 = DistrictModule.ReturnDistrictInfo(d5.ParentId);
District d3 = DistrictModule.ReturnDistrictInfo(d4.ParentId);
District d2 = DistrictModule.ReturnDistrictInfo(d3.ParentId);
District d1 = DistrictModule.ReturnDistrictInfo(d2.ParentId);
string key = d1.DistrictCode + d2.DistrictCode + d3.DistrictCode + d4.DistrictCode + d5.DistrictCode +
device.DeviceNo.PadLeft(3, '0');
lock (_deviceNodeConllection_DeviceNo)
{
if (!_deviceNodeConllection_DeviceNo.ContainsKey(key))
{
_deviceNodeConllection_DeviceNo.Add(key, device);
}
else
{
_deviceNodeConllection_DeviceNo[key] = device;
}
}
lock (_deviceConllection_IdFullDeviceNo)
{
if (!_deviceConllection_IdFullDeviceNo.ContainsKey(device.Id))
{
_deviceConllection_IdFullDeviceNo.Add(device.Id, key);
}
else
{
_deviceConllection_IdFullDeviceNo[device.Id] = key;
}
}
return(device);
}
}
catch { }
return(null);
}
public static List <CardDevice> GetCardDevicesByDistrict(District dist)
{
List <CardDevice> list = new List <CardDevice>();
try
{
if (dist != null)
{
District node2 = DistrictModule.ReturnDistrictInfo(dist.ParentId);
District node3 = DistrictModule.ReturnDistrictInfo(node2.ParentId);
District node4 = DistrictModule.ReturnDistrictInfo(node3.ParentId);
District node5 = DistrictModule.ReturnDistrictInfo(node4.ParentId);
string sql = "select * from CardDevice where AddressCode1='" + (node5.DistrictCode + node4.DistrictCode + node3.DistrictCode) +
"' and AddressCode2='" + (node2.DistrictCode + dist.DistrictCode) + "'";
DataTable table = DbHelperSQL.QueryDataTable(sql);
if (table.Rows.Count != 0)
{
ModelHandler <CardDevice> modelHandler = new ModelHandler <CardDevice>();
list = modelHandler.FillModel(table);
}
}
}
catch { }
return(list);
}
public static DataSet AlarmDataQuery(List <string> deviceIDs, DateTime startTime, DateTime endTime, string alarmType, bool isID, bool IsNew)
{
int num;
DataSet set = new DataSet();
DataTable table = new DataTable();
for (num = 0; num < deviceIDs.Count; num++)
{
string cz = "";
string name = "";
Device device = DeviceModule.GetDeviceByID(long.Parse(deviceIDs[num]));
if (device != null)
{
name = device.DeviceName;
cz = DistrictModule.GetDistrictName(device.DistrictId);
}
table.Clear();
table.Columns.Clear();
string[] strArray = new string[(endTime.Year - startTime.Year) + 1];
int year = startTime.Year;
while (year < (endTime.Year + 1))
{
strArray[year - startTime.Year] = "DeviceAlarm_" + year.ToString();
year++;
}
string strSql = "";
for (year = 0; year < strArray.Length; year++)
{
if (CheckTableExiste(strArray[year]))
{
strSql = "select '" + cz + "' 村庄,'" + name + "' 设备,DeviceNo 设备编号" +
",CONVERT(varchar(100),StartTime,120) 开始时间,case when State='New' then '-' else CONVERT(varchar(100),EndTime,120) end 结束时间" +
",(select top 1 BaseDesc from BaseInfo where BaseType='AlarmType' and BaseKey=AlarmType) 类型,AlarmValue" +
",case when AlarmValue=0 then (select top 1 BaseValue0 from BaseInfo where BaseType='AlarmType' and BaseKey=AlarmType) else (select top 1 BaseValue1 from BaseInfo where BaseType='AlarmType' and BaseKey=AlarmType) end 状态" +
",case when State='New' then '持续中' else cast(Duration as varchar)+'秒' end 时长" +
" from " + strArray[year] +
" where DeviceNo = '" + DeviceModule.GetFullDeviceNoByID(long.Parse(deviceIDs[num])) + "'" +
" and StartTime<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and StartTime>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if ((alarmType.Trim() != "") && (alarmType != "全部"))
{
strSql = strSql + " and AlarmType='" + BaseModule.GetAlarmTypeByDesc(alarmType).BaseKey + "'";
}
if (IsNew)
{
strSql = strSql + " and State = 'New'";
}
}
}
strSql = strSql + " order by StartTime desc";
for (int i = 0; (table.Columns.Count == 0) && (i < 3); i++)
{
table = DbHelperSQL.QueryDataTable(strSql);
}
if (table.Rows.Count != 0)
{
set.Tables.Add(table.Copy());
}
}
return(set);
}
public static void UpdateDeviceInfo(Device device)
{
lock (_deviceNodeConllection)
{
if (!_deviceNodeConllection.ContainsKey(device.Id))
{
_deviceNodeConllection.Add(device.Id, device);
}
else
{
_deviceNodeConllection[device.Id] = device;
}
}
try
{
District d5 = DistrictModule.ReturnDistrictInfo(device.DistrictId);
District d4 = DistrictModule.ReturnDistrictInfo(d5.ParentId);
District d3 = DistrictModule.ReturnDistrictInfo(d4.ParentId);
District d2 = DistrictModule.ReturnDistrictInfo(d3.ParentId);
District d1 = DistrictModule.ReturnDistrictInfo(d2.ParentId);
string key = d1.DistrictCode + d2.DistrictCode + d3.DistrictCode + d4.DistrictCode + d5.DistrictCode +
device.DeviceNo.PadLeft(3, '0');
lock (_deviceNodeConllection_DeviceNo)
{
if (!_deviceNodeConllection_DeviceNo.ContainsKey(key))
{
_deviceNodeConllection_DeviceNo.Add(key, device);
}
else
{
_deviceNodeConllection_DeviceNo[key] = device;
}
}
lock (_deviceConllection_IdFullDeviceNo)
{
if (!_deviceConllection_IdFullDeviceNo.ContainsKey(device.Id))
{
_deviceConllection_IdFullDeviceNo.Add(device.Id, key);
}
else
{
_deviceConllection_IdFullDeviceNo[device.Id] = key;
}
}
}
catch { }
}
public static JavaScriptObject WaterUserToJson(WaterUser waterUser)
{
JavaScriptObject obj2 = new JavaScriptObject();
obj2.Add("ID", waterUser.id);
obj2.Add("名称", waterUser.UserName);
//obj2.Add("户号", waterUser.UserNum);
obj2.Add("状态", waterUser.State);
District node = DistrictModule.ReturnDistrictInfo(waterUser.DistrictId);
District node2 = DistrictModule.ReturnDistrictInfo(node.ParentId);
District node3 = DistrictModule.ReturnDistrictInfo(node2.ParentId);
District node4 = DistrictModule.ReturnDistrictInfo(node3.ParentId);
District node5 = DistrictModule.ReturnDistrictInfo(node4.ParentId);
obj2.Add("省ID", node5.Id);
obj2.Add("市ID", node4.Id);
obj2.Add("区县ID", node3.Id);
obj2.Add("乡镇ID", node2.Id);
obj2.Add("村庄ID", node.Id);
obj2.Add("水价ID", waterUser.水价ID);
obj2.Add("电价ID", waterUser.电价ID);
obj2.Add("电话", waterUser.Telephone);
obj2.Add("身份证号", waterUser.IdentityNumber);
obj2.Add("地址", waterUser.Address);
obj2.Add("用水定额", waterUser.WaterQuota);
obj2.Add("用电定额", waterUser.ElectricQuota);
/*
* obj2.Add("累计购水金额", waterUser.TotalMoneyOfBuyWater);
* obj2.Add("累计购电金额", waterUser.TotalMoneyOfBuyPower);
* obj2.Add("累计购水量", waterUser.TotalAmountOfBuyWater);
* obj2.Add("累计购电量", waterUser.TotalAmountOfBuyPower);
* obj2.Add("累计用水量", waterUser.TotalAmountOfUseWater);
* obj2.Add("累计用电量", waterUser.TotalAmountOfUsePower);
* obj2.Add("年累计购水量", waterUser.YearAmountOfBuyWater);
* obj2.Add("年累计购电量", waterUser.YearAmountOfBuyPower);
* obj2.Add("最后用水时间", waterUser.LastUseWaterTime);
* obj2.Add("详细信息", waterUser.Details);
* string id = "";
* string str2 = "未分配";
* if (waterUser.WaterUserQuota != null)
* {
* str2 = waterUser.WaterUserQuota.Quota.ToString();
* id = waterUser.WaterUserQuota.Id;
* }
* obj2.Add("用水定额ID", id);
* obj2.Add("用水定额", str2);
* obj2.Add("卡序列号", waterUser.CardSerialNumber);
* */
return(obj2);
}
public static List <long> GetAllDevicesForManageID(long districtId)
{
List <long> list = new List <long>();
List <long> allLowerID = DistrictModule.GetAllDistrictID(districtId);
foreach (KeyValuePair <long, Device> pair in _deviceNodeConllection)
{
if (allLowerID.Contains(pair.Value.DistrictId))
{
list.Add(pair.Key);
}
}
return(list);
}
public static JavaScriptObject CardUserToJson(CardUser cardUser)
{
JavaScriptObject obj2 = new JavaScriptObject();
obj2.Add("ID", cardUser.Id);
obj2.Add("WaterUserId", cardUser.WaterUserId);
obj2.Add("SerialNumber", cardUser.SerialNumber);
obj2.Add("UserNo", cardUser.UserNo.Trim().TrimStart('0'));
obj2.Add("ResidualWater", cardUser.ResidualWater);
obj2.Add("ResidualElectric", cardUser.ResidualElectric);
obj2.Add("TotalWater", cardUser.TotalWater);
obj2.Add("TotalElectric", cardUser.TotalElectric);
obj2.Add("TotallMoney", cardUser.TotallMoney);
obj2.Add("DeviceList", cardUser.DeviceList);
obj2.Add("IsCountermand", cardUser.IsCountermand == 0 ? "否" : "是");
obj2.Add("OpenTime", cardUser.OpenTime.ToString("yyyy-MM-dd HH:mm:ss"));
obj2.Add("LastChargeTime", cardUser.LastChargeTime > DateTime.Parse("2010-1-1") ? cardUser.LastChargeTime.ToString("yyyy-MM-dd HH:mm:ss") : "");
WaterUser waterUser = WaterUserModule.GetWaterUserById(cardUser.WaterUserId);
if (waterUser != null)
{
obj2.Add("UserName", waterUser.UserName);
obj2.Add("IdentityNumber", waterUser.IdentityNumber);
obj2.Add("Telephone", waterUser.Telephone);
District node = DistrictModule.ReturnDistrictInfo(waterUser.DistrictId);
District node2 = DistrictModule.ReturnDistrictInfo(node.ParentId);
District node3 = DistrictModule.ReturnDistrictInfo(node2.ParentId);
District node4 = DistrictModule.ReturnDistrictInfo(node3.ParentId);
District node5 = DistrictModule.ReturnDistrictInfo(node4.ParentId);
obj2.Add("省ID", node5.Id);
obj2.Add("市ID", node4.Id);
obj2.Add("区县ID", node3.Id);
obj2.Add("乡镇ID", node2.Id);
obj2.Add("村庄ID", node.Id);
}
else
{
obj2.Add("UserName", "未知");
obj2.Add("IdentityNumber", "未知");
obj2.Add("Telephone", "未知");
obj2.Add("省ID", "未知");
obj2.Add("市ID", "未知");
obj2.Add("区县ID", "未知");
obj2.Add("乡镇ID", "未知");
obj2.Add("村庄ID", "未知");
}
return(obj2);
}
public static void RemoveDeviceInfo(long DeviceId)
{
Device device = GetDeviceByID(DeviceId);
if (device != null)
{
try
{
District d5 = DistrictModule.ReturnDistrictInfo(device.DistrictId);
District d4 = DistrictModule.ReturnDistrictInfo(d5.ParentId);
District d3 = DistrictModule.ReturnDistrictInfo(d4.ParentId);
District d2 = DistrictModule.ReturnDistrictInfo(d3.ParentId);
District d1 = DistrictModule.ReturnDistrictInfo(d2.ParentId);
string key = d1.DistrictCode + d2.DistrictCode + d3.DistrictCode + d4.DistrictCode + d5.DistrictCode +
device.DeviceNo.PadLeft(3, '0');
lock (_deviceNodeConllection_DeviceNo)
{
if (_deviceNodeConllection_DeviceNo.ContainsKey(key))
{
_deviceNodeConllection_DeviceNo.Remove(key);
}
}
}
catch { }
}
lock (_deviceNodeConllection)
{
if (_deviceNodeConllection.ContainsKey(DeviceId))
{
_deviceNodeConllection.Remove(DeviceId);
}
}
lock (_deviceConllection_IdFullDeviceNo)
{
if (_deviceConllection_IdFullDeviceNo.ContainsKey(DeviceId))
{
_deviceConllection_IdFullDeviceNo.Remove(DeviceId);
}
}
}
public static void LoadDevices()
{
string strSql = "select * from Device where IsValid=1";
try
{
DataTable table = DbHelperSQL.Query(strSql).Tables[0];
if (table.Rows.Count != 0)
{
ModelHandler <Device> modelHandler = new ModelHandler <Device>();
lock (_deviceNodeConllection)
{
_deviceNodeConllection.Clear();
_deviceNodeConllection_DeviceNo.Clear();
_deviceConllection_IdFullDeviceNo.Clear();
for (int i = 0; i < table.Rows.Count; i++)
{
try
{
DataRow dataRow = table.Rows[i];
Device device = modelHandler.FillModel(dataRow);
if (!_deviceNodeConllection.ContainsKey(device.Id))
{
_deviceNodeConllection.Add(device.Id, device);
}
else
{
_deviceNodeConllection[device.Id] = device;
}
District d5 = DistrictModule.ReturnDistrictInfo(device.DistrictId);
District d4 = DistrictModule.ReturnDistrictInfo(d5.ParentId);
District d3 = DistrictModule.ReturnDistrictInfo(d4.ParentId);
District d2 = DistrictModule.ReturnDistrictInfo(d3.ParentId);
District d1 = DistrictModule.ReturnDistrictInfo(d2.ParentId);
string key = d1.DistrictCode + d2.DistrictCode + d3.DistrictCode + d4.DistrictCode + d5.DistrictCode +
device.DeviceNo.PadLeft(3, '0');
if (!_deviceNodeConllection_DeviceNo.ContainsKey(key))
{
_deviceNodeConllection_DeviceNo.Add(key, device);
}
else
{
_deviceNodeConllection_DeviceNo[key] = device;
}
if (!_deviceConllection_IdFullDeviceNo.ContainsKey(device.Id))
{
_deviceConllection_IdFullDeviceNo.Add(device.Id, key);
}
else
{
_deviceConllection_IdFullDeviceNo[device.Id] = key;
}
}
catch (Exception e) { }
}
}
}
}
catch { }
}
public static Device GetDeviceByFullDeviceNo_DB(string FullDeviceNo)
{
if (FullDeviceNo.Length == 15)
{
try
{
string code1 = FullDeviceNo.Substring(0, 2);
string code2 = FullDeviceNo.Substring(2, 2);
string code3 = FullDeviceNo.Substring(4, 2);
string code4 = FullDeviceNo.Substring(6, 3);
string code5 = FullDeviceNo.Substring(9, 3);
string deviceNo = FullDeviceNo.Substring(12, 3);
District d1 = DistrictModule.ReturnDistrictInfo(code1, 2, 0);
if (d1 == null)
{
d1 = DistrictModule.ReturnDistrictInfo_DB(code1, 2, 0);
}
if (d1 == null)
{
return(null);
}
District d2 = DistrictModule.ReturnDistrictInfo(code2, 3, d1.Id);
if (d2 == null)
{
d2 = DistrictModule.ReturnDistrictInfo_DB(code2, 3, d1.Id);
}
if (d2 == null)
{
return(null);
}
District d3 = DistrictModule.ReturnDistrictInfo(code3, 4, d2.Id);
if (d3 == null)
{
d3 = DistrictModule.ReturnDistrictInfo_DB(code3, 4, d2.Id);
}
if (d3 == null)
{
return(null);
}
District d4 = DistrictModule.ReturnDistrictInfo(code4, 5, d3.Id);
if (d4 == null)
{
d4 = DistrictModule.ReturnDistrictInfo_DB(code4, 5, d3.Id);
}
if (d4 == null)
{
return(null);
}
District d5 = DistrictModule.ReturnDistrictInfo(code5, 6, d4.Id);
if (d5 == null)
{
d5 = DistrictModule.ReturnDistrictInfo_DB(code5, 6, d4.Id);
}
if (d5 == null)
{
return(null);
}
try
{
string strSql = "select * from Device where IsValid=1 and DeviceNo='" + deviceNo + "' and DistrictId=" + d5.Id;
DataTable table = DbHelperSQL.Query(strSql).Tables[0];
if (table.Rows.Count != 0)
{
ModelHandler <Device> modelHandler = new ModelHandler <Device>();
DataRow dataRow = table.Rows[0];
Device device = modelHandler.FillModel(dataRow);
lock (_deviceNodeConllection)
{
if (!_deviceNodeConllection.ContainsKey(device.Id))
{
_deviceNodeConllection.Add(device.Id, device);
}
else
{
_deviceNodeConllection[device.Id] = device;
}
}
lock (_deviceNodeConllection_DeviceNo)
{
if (!_deviceNodeConllection_DeviceNo.ContainsKey(FullDeviceNo))
{
_deviceNodeConllection_DeviceNo.Add(FullDeviceNo, device);
}
else
{
_deviceNodeConllection_DeviceNo[FullDeviceNo] = device;
}
}
lock (_deviceConllection_IdFullDeviceNo)
{
if (!_deviceConllection_IdFullDeviceNo.ContainsKey(device.Id))
{
_deviceConllection_IdFullDeviceNo.Add(device.Id, FullDeviceNo);
}
else
{
_deviceConllection_IdFullDeviceNo[device.Id] = FullDeviceNo;
}
}
return(device);
}
}
catch { }
}
catch { }
}
return(null);
}
public static List <CardDevice> GetCardDevicesByDistrictId(long DistrictId)
{
District dist = DistrictModule.ReturnDistrictInfo(DistrictId);
return(GetCardDevicesByDistrict(dist));
}
public static DataSet EventDataQuery2(List <string> deviceIDs, DateTime startTime, DateTime endTime, string eventType)
{
int num;
DataSet set = new DataSet();
DataTable table = new DataTable();
string[] strArray = new string[(endTime.Year - startTime.Year) + 1];
for (num = 0; num < deviceIDs.Count; num++)
{
string cz = "";
string name = "";
Device device = DeviceModule.GetDeviceByID(long.Parse(deviceIDs[num]));
if (device != null)
{
name = device.DeviceName;
cz = DistrictModule.GetDistrictName(device.DistrictId);
}
table.Clear();
table.Columns.Clear();
string strSql = "";
string str7 = "";
bool flag = true;
for (int i = startTime.Year; i < (endTime.Year + 1); i++)
{
string strTableName = "DeviceEvent_" + i.ToString();
if (CheckTableExiste(strTableName))
{
if (flag)
{
strSql = "select '" + cz + "' 村庄,'" + name + "' 设备,DeviceNo 设备编号,CONVERT(varchar(100),StartTime,120) 开泵时间,CONVERT(varchar(100),EndTime,120) 关泵时间,DATEDIFF(second,StartTime,EndTime) 灌溉时长,WaterUsed 用水量 from " + strTableName + " where EndTime>StartTime and DeviceTime<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and DeviceTime>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and DeviceNo='" + DeviceModule.GetFullDeviceNoByID(long.Parse(deviceIDs[num])) + "'";
if ((eventType != "") && (eventType != "全部"))
{
strSql = strSql + " and EventType='" + eventType + "'";
}
str7 = strSql;
flag = false;
}
else
{
string str9 = strSql;
strSql = str9 + " union select '" + cz + "' 村庄,'" + name + "' 设备,DeviceNo 设备编号,CONVERT(varchar(100),StartTime,120) 开泵时间,CONVERT(varchar(100),EndTime,120) 关泵时间,DATEDIFF(second,StartTime,EndTime) 灌溉时长,WaterUsed 用水量 from " + strTableName + " where EndTime>StartTime and DeviceTime<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and DeviceTime>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and DeviceNo='" + DeviceModule.GetFullDeviceNoByID(long.Parse(deviceIDs[num])) + "'";
if ((eventType != "") && (eventType != "全部"))
{
strSql = strSql + " and EventType='" + eventType + "'";
}
}
}
}
if (strSql != "")
{
strSql = strSql + " order by DeviceTime desc";
}
if (strSql != "")
{
table = DbHelperSQL.QueryDataTable(strSql);
}
if (table.Rows.Count != 0)
{
set.Tables.Add(table.Copy());
}
}
return(set);
}
