/// <summary>
///
/// </summary>
/// <param name="channelID"></param>
static public void DeleteChannalStationMap(int channelID)
{
string s = string.Format("Delete from tblChannelStationMap where channelID = {0}",
channelID);
DBI.GetDBI().ExecuteScalar(s);
}
//根据登录角色查询角色类型
public string role_type(string role_id)
{
string roletype = "";
DataTable dt = new DataTable();
if (role_id.Length > 0)
{
dt = DBI.GetDBI().ExecuteDataTable("select role_type from tb_role where role_id=" + role_id);
}
else
{
dt = DBI.GetDBI().ExecuteDataTable("select role_type from tb_role");
}
try
{
if (dt.Rows.Count > 0)
{
roletype = dt.Rows[0]["role_type"].ToString();
}
}
catch
{
roletype = "";
}
return(roletype);
}
public DataTable get_menu2(string sql)
{
DataTable dt = new DataTable();
dt = DBI.GetDBI().ExecuteDataTable(sql);
return(dt);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
static public DataTable GetWaterUserTypeDataTable()
{
//string s = "select * from tblWaterUserType where isManager = 0";
string s = "select * from tblWaterUserType";
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <param name="deviceID"></param>
/// <returns></returns>
static public DataTable GetDeviceDataTable(int deviceID)
{
string s = string.Format("select * from tblDevice where deleted = 0 and deviceid = {0}",
deviceID);
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
static public void UpdateWaterUser(int id, string newName, string newRemark, int waterUserTypeID, bool hasParent,
int parentWaterUserID, int waterUserLevelID)
{
string s = @"update tblWaterUser set
waterUserName = @name,
Remark = @remark,
waterUserTypeID = @waterUserTypeID,
parentWaterUserid = @parentWaterUserID,
waterUserLevelID = @waterUserLevelID
where WaterUserID = @id";
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "id", id);
DBI.AddSqlParameter(cmd, "name", newName);
DBI.AddSqlParameter(cmd, "remark", newRemark);
DBI.AddSqlParameter(cmd, "waterUserTypeID", waterUserTypeID);
// TODO:
//
if (hasParent)
{
DBI.AddSqlParameter(cmd, "parentWaterUserID", parentWaterUserID);
}
else
{
DBI.AddSqlParameter(cmd, "parentWaterUserID", DBNull.Value);
}
DBI.AddSqlParameter(cmd, "waterUserLevelID", waterUserLevelID);
//DBI.AddSqlParameter(cmd, "", );
DBI.GetDBI().ExecuteScalar(cmd);
}
/// <summary>
///
/// </summary>
static public void DeleteWaterUser(int id)
{
string s = string.Format("delete from tblWaterUser where wateruserid = {0}",
id);
DBI.GetDBI().ExecuteScalar(s);
}
/// 按站点名称取最后一条数据
/// </summary>
/// <param name="deviceid"></param>
/// <returns></returns>
public DataTable ditch_station_query(string station)
{
string sql = string.Format("select top 1 ditchdataid,dt,wl1,wl2,instantflux,usedamount,stationname from vDitchData where stationid='{0}' order by ditchdataid desc", station);
DataTable dt = DBI.GetDBI().ExecuteDataTable(sql);
return(dt);
}
/// <summary>
/// 降雨量
/// </summary>
/// <param name="station"></param>
/// <returns></returns>
public DataTable ditch_stationrain_query(string station)
{
string sql = string.Format("select top 1 RainFillDataID,name,DT,Remark,RainFill from Vrainfilldata where stationid='{0}' order by RainFillDataID desc", station);
DataTable dt = DBI.GetDBI().ExecuteDataTable(sql);
return(dt);
}
/// <summary>
///
/// </summary>
/// <param name="stationID"></param>
/// <returns></returns>
public static DataTable GetStationDataTable(int stationID)
{
string s = string.Format("select * from tblstation where stationid = {0} and deleted = 0",
stationID);
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
/// 根据站点返回闸控最后一条数据
/// </summary>
/// <param name="station_name"></param>
/// <returns></returns>
public DataTable sluice_station_query(string station)
{
string sql = string.Format("select top 1 sluicedataid,dt,height,beforewl,behindwl,stationname from vSluiceData where stationid='{0}' order by sluicedataid desc", station);
DataTable dt = DBI.GetDBI().ExecuteDataTable(sql);
return(dt);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
static public DataTable GetNotAssociateStationDataTable()
{
string s = @"select * from tblstation where deleted = 0 and
stationid not in (select stationid from tblchannelstationmap)";
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
static public DataTable GetStationDataTable()
{
string s = "select * from tblStation";
//string s = "select * from vStationDevice";
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <param name="channelID"></param>
/// <param name="exStationIDs"></param>
/// <returns></returns>
//static public DataTable GetStationDataTable(int channelID, int[] exStationIDs)
//{
// string s = string.Format ("select * from tblChannelStationMap where channelID = {0}",
// channelID);
// DataTable tbl = DBI.GetDBI().ExecuteDataTable(s);
// // use tbl.Merge ?
// //
// string ids = string.Empty;
// foreach (DataRow row in tbl.Rows)
// {
// string id = row["stationID"].ToString();
// ids += id + ",";
// }
// if (exStationIDs != null && exStationIDs.Length > 0)
// {
// for (int i = 0; i < exStationIDs.Length; i++)
// {
// ids += exStationIDs[i] + ",";
// }
// }
// if (ids.Length > 0)
// {
// ids = ids.Substring(0, ids.Length - 1);
// }
// if (ids.Length == 0)
// {
// ids = "-1";
// }
// string sql = string.Empty;
// //if (ids.Length > 0)
// //{
// sql = string.Format(
// "select * from tblStation where deleted = 0 and stationid in ({0})",
// ids);
// //}
// //else
// //{
// // sql = "select * from tblStation where deleted = 0";
// //}
// return DBI.GetDBI().ExecuteDataTable(sql);
//}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
public static void DeleteChannel(int id)
{
string s = string.Format("delete from tblChannel where channelID = {0}",
id);
DBI.GetDBI().ExecuteScalar(s);
}
/// <summary>
///
/// </summary>
/// <param name="waterUserID"></param>
static public void DeleteByWaterUserID(int waterUserID)
{
string s = string.Format("delete from tblWaterUserChannelMap where waterUserID = {0}",
waterUserID);
DBI.GetDBI().ExecuteScalar(s);
}
public static void Delete(int deviceID)
{
string s = string.Format("delete from tblAlarm where DeviceID = {0}",
deviceID);
DBI.GetDBI().ExecuteScalar(s);
}
///// <summary>
/////
///// </summary>
///// <param name="waterUserID"></param>
///// <param name="channelID"></param>
//static public void Associate(int waterUserID, int channelID)
//{
// // TODO:
// //
//}
/// <summary>
///
/// </summary>
/// <param name="waterUserID"></param>
/// <param name="channelID"></param>
static public void Insert(int waterUserID, int channelID)
{
string s = string.Format("insert into tblWaterUserChannelMap(waterUserID, channelID) values({0}, {1})",
waterUserID, channelID);
DBI.GetDBI().ExecuteScalar(s);
}
/// <summary>
///
/// </summary>
/// <param name="userName"></param>
/// <param name="pwd"></param>
/// <returns></returns>
static public bool CanLogin(string userName, string password, out int userID, out int waterUserID)
{
userID = 0;
waterUserID = 0;
string s = "select * from tb_user where name = @n and password = @p";
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "n", userName);
DBI.AddSqlParameter(cmd, "p", password);
bool isLogin = false;
DataTable tbl = DBI.GetDBI().ExecuteDataTable(cmd);
if (tbl.Rows.Count > 0)
{
DataRow row = tbl.Rows[0];
userID = Convert.ToInt32(row["userID"]);
waterUserID = Convert.ToInt32(row["waterUserID"]);
isLogin = true;
}
return(isLogin);
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
static public void InsertWaterUser(string name, string remark, int waterUserTypeID,
bool hasParentWaterUser, int parentWaterUserID, int waterUserLevelID)
{
string s = @"insert into tblWaterUser(waterUserName, Remark, waterUserTypeID, parentWaterUserID, waterUserLevelID)
values(@name, @remark, @waterUserTypeID, @parentWaterUserID, @waterUserLevelID)";
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "name", name);
DBI.AddSqlParameter(cmd, "remark", remark);
DBI.AddSqlParameter(cmd, "waterUserTypeID", waterUserTypeID);
// TODO:
//
if (hasParentWaterUser)
{
DBI.AddSqlParameter(cmd, "parentWaterUserID", parentWaterUserID);
}
else
{
DBI.AddSqlParameter(cmd, "parentWaterUserID", DBNull.Value);
}
DBI.AddSqlParameter(cmd, "waterUserLevelID", waterUserLevelID);
DBI.GetDBI().ExecuteScalar(cmd);
int waterUserID = GetWaterUserID(name);
}
/// <summary>
///
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
internal static DataTable GetWaterUserLevelDataTableByOrdinal(int ordinal)
{
string s = string.Format("select * from tblWaterUserLevel where ordinal = {0}",
ordinal);
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <param name="waterUserID"></param>
/// <returns></returns>
static public DataTable GetWaterUserDataTable(int waterUserID)
{
string s = string.Format("select * from tblWaterUser where deleted = 0 and waterUserID = {0}",
waterUserID);
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <param name="waterUserID"></param>
/// <param name="id"></param>
private static void Insert(int waterUserID, int id)
{
string s = string.Format("insert into tblOut (waterUserID, deviceID) values ({0}, {1})",
waterUserID, id);
DBI.GetDBI().ExecuteScalar(s);
}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
static public void DeleteDuring(int id)
{
string s = string.Format("delete from tblDuringWater where duringWaterID = {0}",
id);
DBI.GetDBI().ExecuteScalar(s);
}
/// <summary>
///
/// </summary>
/// <param name="waterUserID"></param>
/// <returns></returns>
static public DataTable GetWaterUserChannelMapDataTable(int waterUserID)
{
string s = string.Format("select * from tblWaterUserChannelMap where waterUserID = {0}",
waterUserID);
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <param name="formulaID"></param>
static public void Delete(int formulaID)
{
string s = string.Format(
"delete from tblFormula where formulaID = {0}", formulaID);
DBI.GetDBI().ExecuteScalar(s);
}
/// <summary>
///
/// </summary>
/// <param name="deviceID"></param>
/// <param name="begin"></param>
/// <param name="end"></param>
/// <returns>unit m3</returns>
internal static double CalcUsedAmount(int deviceID, DateTime begin, DateTime end)
{
string sql =
"select deviceid, convert(varchar(10), dt, 120) as dt, avg(wl1) as wl1, avg(wl2) as wl2, " +
// 2012-07-05
//
//
//"avg(convert(bigint, instantflux)) as instantFlux " +
"avg(convert(real, instantflux)) as instantFlux " +
"from tblDitchData " +
"where DeviceID = {0} and DT > '{1}' and dt < '{2}' " +
"group by deviceid, convert(varchar(10), dt, 120) ";
sql = string.Format(sql, deviceID, begin, end);
DataTable tbl = DBI.GetDBI().ExecuteDataTable(sql);
// instant flux unit m3/s
//
object totalInstantFluxObj = tbl.Compute("sum(instantflux)", true.ToString());
if (totalInstantFluxObj != DBNull.Value)
{
double totalInstantFlux = Convert.ToDouble(totalInstantFluxObj);
double amount = totalInstantFlux * 24 * 60 * 60;
return(amount);
}
else
{
return(0d);
}
}
/// <summary>
///
/// </summary>
/// <param name="waterUserID"></param>
/// <returns></returns>
static public DataTable GetStationDeviceDataTable(int waterUserID)
{
string s = string.Format(
"select * from vWaterUserChannelStationDevice where waterUserID = {0}",
waterUserID);
return(DBI.GetDBI().ExecuteDataTable(s));
}
///// <summary>
/////
///// </summary>
///// <param name="waterUserID"></param>
///// <returns></returns>
//static public DataTable GetDitchDataLastDataTable(int waterUserID)
//{
// // TODO: 2011-06-17
// //
// return null;
//}
/// <summary>
///
/// </summary>
/// <param name="stationIDs"></param>
internal static DataTable GetDitchDataLastDataTable(int[] stationIDs)
{
string inString = SqlHelper.MakeInQueryCondition(stationIDs);
string s = "select * from vDitchDataLast where StationID in ({0})";
s = string.Format(s, inString);
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <param name="begin"></param>
/// <param name="end"></param>
/// <returns></returns>
static public DataTable GetDayDitchData(int deviceID, DateTime begin, DateTime end)
{
string s = string.Format(
"select * from vDitchDataDaySD where deviceid={0} and dt >= '{1}' and dt < '{2}' order by DT",
deviceID, begin, end);
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <param name="stationID"></param>
/// <returns></returns>
static public DataTable GetDeviceDataTableByStationID(int stationID)
{
string s = string.Format(
"select * from tblDevice where deleted = 0 and stationID = {0}",
stationID);
return(DBI.GetDBI().ExecuteDataTable(s));
}
