public List <S_Station> GetStationsInArea(string id)
{
SqlParameter[] parms = { new SqlParameter("@AreaId", SqlDbType.VarChar, 100) };
parms[0].Value = SqlTypeConverter.DBNullStringChecker(id);
var entities = new List <S_Station>();
using (var rdr = SqlHelper.ExecuteReader(this._databaseConnectionString, CommandType.Text, SqlCommands_Rs.Sql_S_Station_Repository_GetStationsInArea, parms)) {
while (rdr.Read())
{
var entity = new S_Station();
entity.Id = SqlTypeConverter.DBNullStringHandler(rdr["Id"]);
entity.Code = SqlTypeConverter.DBNullStringHandler(rdr["Code"]);
entity.Name = SqlTypeConverter.DBNullStringHandler(rdr["Name"]);
entity.Type = new C_StationType {
Id = SqlTypeConverter.DBNullStringHandler(rdr["StaTypeId"]), Name = SqlTypeConverter.DBNullStringHandler(rdr["StaTypeName"])
};
entity.Vendor = SqlTypeConverter.DBNullStringHandler(rdr["Vendor"]);
entity.Longitude = SqlTypeConverter.DBNullStringHandler(rdr["Longitude"]);
entity.Latitude = SqlTypeConverter.DBNullStringHandler(rdr["Latitude"]);
entity.Altitude = SqlTypeConverter.DBNullStringHandler(rdr["Altitude"]);
entity.CityElecLoadTypeId = SqlTypeConverter.DBNullInt32Handler(rdr["CityElecLoadTypeID"]);
entity.CityElectNumber = SqlTypeConverter.DBNullInt32Handler(rdr["CityElectNumber"]);
entity.CityElecCap = SqlTypeConverter.DBNullStringHandler(rdr["CityElecCap"]);
entity.CityElecLoad = SqlTypeConverter.DBNullStringHandler(rdr["CityElecLoad"]);
entity.Contact = SqlTypeConverter.DBNullStringHandler(rdr["Contact"]);
entity.LineRadiusSize = SqlTypeConverter.DBNullStringHandler(rdr["LineRadiusSize"]);
entity.LineLength = SqlTypeConverter.DBNullStringHandler(rdr["LineLength"]);
entity.SuppPowerTypeId = SqlTypeConverter.DBNullInt32Handler(rdr["SuppPowerTypeID"]);
entity.TranInfo = SqlTypeConverter.DBNullStringHandler(rdr["TranInfo"]);
entity.TranContNo = SqlTypeConverter.DBNullStringHandler(rdr["TranContNo"]);
entity.TranPhone = SqlTypeConverter.DBNullStringHandler(rdr["TranPhone"]);
entity.AreaId = SqlTypeConverter.DBNullStringHandler(rdr["AreaId"]);
entity.AreaName = SqlTypeConverter.DBNullStringHandler(rdr["AreaName"]);
entity.Comment = SqlTypeConverter.DBNullStringHandler(rdr["Comment"]);
entity.Enabled = SqlTypeConverter.DBNullBooleanHandler(rdr["Enabled"]);
entities.Add(entity);
}
}
return(entities);
}
public List <S_Station> GetStationsWithPoints(IEnumerable <string> points)
{
if (points == null || !points.Any())
{
throw new ArgumentNullException("points");
}
var commands = points.Select(p => string.Format(@"SELECT '{0}' AS [PointId]", p));
var query = string.Format(@"
;WITH PointKeys AS (
{0}
),
StationKeys AS (
SELECT R.[StationID],COUNT(1) AS [PtCount] FROM [dbo].[D_Signal] S
INNER JOIN PointKeys PK ON S.[PointID]=PK.[PointId]
INNER JOIN [dbo].[D_Device] D ON S.[DeviceID]=D.[ID]
INNER JOIN [dbo].[S_Room] R ON D.[RoomID]=R.[ID]
GROUP BY R.[StationID]
)
SELECT S.[Id],S.[Code],S.[Name],S.[StaTypeId],ST.[Name] AS [StaTypeName],V.[Name] AS [Vendor],S.[Longitude],S.[Latitude],S.[Altitude],S.[CityElecLoadTypeId],S.[CityElectNumber],S.[CityElecCap],S.[CityElecLoad],S.[Contact],S.[LineRadiusSize],S.[LineLength],S.[SuppPowerTypeId],S.[TranInfo],S.[TranContNo],S.[TranPhone],S.[AreaId],CASE WHEN AA.[Name] IS NULL THEN A.[Name] ELSE AA.[Name] + ',' + A.[Name] END AS [AreaName],S.[Desc] AS [Comment],S.[Enabled],ISNULL(SK.[PtCount],0) AS [PtCount] FROM [dbo].[S_Station] S
INNER JOIN [dbo].[C_StationType] ST ON S.[StaTypeId] = ST.[Id]
INNER JOIN [dbo].[A_Area] A ON S.[AreaID] = A.[ID]
LEFT OUTER JOIN [dbo].[A_Area] AA ON A.[ParentID] = AA.[ID]
LEFT OUTER JOIN StationKeys SK ON S.[ID]=SK.[StationID]
LEFT OUTER JOIN [dbo].[C_SCVendor] V ON S.[VendorID]=V.[ID];", string.Join(@" UNION ALL ", commands));
var entities = new List <S_Station>();
using (var rdr = SqlHelper.ExecuteReader(this._databaseConnectionString, CommandType.Text, query, null)) {
while (rdr.Read())
{
var entity = new S_Station();
entity.Id = SqlTypeConverter.DBNullStringHandler(rdr["Id"]);
entity.Code = SqlTypeConverter.DBNullStringHandler(rdr["Code"]);
entity.Name = SqlTypeConverter.DBNullStringHandler(rdr["Name"]);
entity.Type = new C_StationType {
Id = SqlTypeConverter.DBNullStringHandler(rdr["StaTypeId"]), Name = SqlTypeConverter.DBNullStringHandler(rdr["StaTypeName"])
};
entity.Vendor = SqlTypeConverter.DBNullStringHandler(rdr["Vendor"]);
entity.Longitude = SqlTypeConverter.DBNullStringHandler(rdr["Longitude"]);
entity.Latitude = SqlTypeConverter.DBNullStringHandler(rdr["Latitude"]);
entity.Altitude = SqlTypeConverter.DBNullStringHandler(rdr["Altitude"]);
entity.CityElecLoadTypeId = SqlTypeConverter.DBNullInt32Handler(rdr["CityElecLoadTypeID"]);
//用CityElectNumber存储站点下指定信号的数量
entity.CityElectNumber = SqlTypeConverter.DBNullInt32Handler(rdr["PtCount"]);
entity.CityElecCap = SqlTypeConverter.DBNullStringHandler(rdr["CityElecCap"]);
entity.CityElecLoad = SqlTypeConverter.DBNullStringHandler(rdr["CityElecLoad"]);
entity.Contact = SqlTypeConverter.DBNullStringHandler(rdr["Contact"]);
entity.LineRadiusSize = SqlTypeConverter.DBNullStringHandler(rdr["LineRadiusSize"]);
entity.LineLength = SqlTypeConverter.DBNullStringHandler(rdr["LineLength"]);
entity.SuppPowerTypeId = SqlTypeConverter.DBNullInt32Handler(rdr["SuppPowerTypeID"]);
entity.TranInfo = SqlTypeConverter.DBNullStringHandler(rdr["TranInfo"]);
entity.TranContNo = SqlTypeConverter.DBNullStringHandler(rdr["TranContNo"]);
entity.TranPhone = SqlTypeConverter.DBNullStringHandler(rdr["TranPhone"]);
entity.AreaId = SqlTypeConverter.DBNullStringHandler(rdr["AreaId"]);
entity.AreaName = SqlTypeConverter.DBNullStringHandler(rdr["AreaName"]);
entity.Comment = SqlTypeConverter.DBNullStringHandler(rdr["Comment"]);
entity.Enabled = SqlTypeConverter.DBNullBooleanHandler(rdr["Enabled"]);
entities.Add(entity);
}
}
return(entities);
}
