internal static DBI GetDBI_wmgis()
{
if (_d1 == null)
{
//
//
//string conn = "server=WWW-9B16331897D/SQL2005;initial catalog=wmgis;user=sa;password=1";
string conn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString_wmgis"];
_d1 = new DBI(conn);
}
return _d1;
}
internal static DBI GetDBI_yhxx()
{
if (_d2 == null)
{
//
//
//string conn = "server=192.168.1.32;initial catalog=YeHeSLDB;user=sa;password=sa";
string conn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString_yhxx"];
_d2 = new DBI(conn);
}
return _d2;
}
/// <summary>
/// 水库信息查询
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public DataTable sk_query(string id)
{
string sql = "";
string conn = System.Configuration.ConfigurationSettings.AppSettings["wmgis"];
if (id.ToString() != "")
{
sql = string.Format("select * from 水库 where id='{0}'", id);
}
else
{
sql = string.Format("select * from 水库");
}
DataTable dt = DBI.GetDBI_wmgis().ExecuteDataTable(sql);
return(dt);
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
static public int GetUserID(string name)
{
string s = "select userID from tb_user where name = @name";
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "name", name);
object obj = DBI.GetDBI().ExecuteScalar(cmd);
if (obj != null && obj != DBNull.Value)
{
return(Convert.ToInt32(obj));
}
else
{
return(0);
}
}
/// <summary>
/// 获取低于某个用户的管理单位
/// </summary>
/// <param name="waterUserID"></param>
/// <param name="waterUserLevelNumber"></param>
/// <returns></returns>
public static DataTable GetManagementOrganization(int waterUserID, int waterUserLevelNumber)
{
//string s = string.Format(
// "select * from vWaterUser where ( ordinal >= {0} ) and (ordinal < {1} or WaterUserID = {2})",
// ManagementOrganizationLevelNumber ,
// waterUserLevelNumber,
// waterUserID
// );
string s = string.Format(
"select * from vWaterUser where (ordinal < {0} or WaterUserID = {1})",
waterUserLevelNumber,
waterUserID
);
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <param name="waterUserID"></param>
/// <returns></returns>
static public int[] GetDeviceIDsByWaterUserID(int waterUserID)
{
string s = string.Format(
"select deviceID from vWaterUserChannelstationdevice where waterUserID = {0}",
waterUserID);
ArrayList list = new ArrayList();
DataTable tbl = DBI.GetDBI().ExecuteDataTable(s);
foreach (DataRow row in tbl.Rows)
{
int id = Convert.ToInt32(row["DeviceID"]);
list.Add(id);
}
return((int[])list.ToArray(typeof(int)));
}
/// <summary>
///
/// </summary>
/// <param name="channelname"></param>
/// <returns></returns>
static public int GetChannelID(string channelname)
{
string s = "select channelid from tblChannel where channelname = @n";
SqlCommand cmd = new SqlCommand(s);
DBIBase.AddSqlParameter(cmd, "n", channelname);
object obj = DBI.GetDBI().ExecuteScalar(cmd);
if (obj != null && obj != DBNull.Value)
{
return(Convert.ToInt32(obj));
}
else
{
return(0);
}
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
static public int GetWaterUserID(string name)
{
string s = "select wateruserID from tblWaterUser where wateruserName = @n";
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "n", name);
object obj = DBI.GetDBI().ExecuteScalar(cmd);
if (obj == null || obj == DBNull.Value)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
///
/// </summary>
/// <param name="begin"></param>
/// <param name="end"></param>
/// <returns></returns>
static public double GetPayedFee(int waterUserID, DateTime begin, DateTime end)
{
string s = string.Format(
"select sum(totalPrice) from tblFee " +
"where wateruserid = {0} and ( beginDT >='{1}' and endDT < '{2}')",
waterUserID, begin, end);
object obj = DBI.GetDBI().ExecuteScalar(s);
if (obj != null && obj != DBNull.Value)
{
return(Convert.ToDouble(obj));
}
else
{
return(0d);
}
}
/// <summary>
///
/// </summary>
/// <param name="inOrOutTableName"></param>
/// <param name="waterUserId"></param>
/// <returns></returns>
private static DeviceCollection CreateDeviceCollection(string inOrOutTableName, int waterUserId)
{
DeviceCollection ds = new DeviceCollection();
string s = string.Format(
@"select * from tblDevice
where deleted = 0 and
deviceid in (select deviceid from {0} where waterUserID = {1})",
inOrOutTableName, waterUserId);
DataTable tbl = DBI.GetDBI().ExecuteDataTable(s);
foreach (DataRow row in tbl.Rows)
{
DeviceClass d = CreateDevice(row);
ds.Add(d);
}
return(ds);
}
/// <summary>
///
/// </summary>
/// <param name="channelName"></param>
/// <param name="remark"></param>
/// <param name="stationIDs"></param>
static public void AddChannel(string channelName, string remark, int[] stationIDs)
{
string s = "insert into tblChannel(ChannelName, Remark) values(@channelName, @remark)";
SqlCommand cmd = new SqlCommand(s);
DBIBase.AddSqlParameter(cmd, "channelName", channelName);
DBIBase.AddSqlParameter(cmd, "remark", remark);
DBI.GetDBI().ExecuteScalar(cmd);
//int newID = DBI.GetDBI().GetIdentity();
int newID = GetChannelID(channelName);
if (newID != 0)
{
//AddChannelStationMap(newID, stationIDs);
AssociateChannalStation(newID, stationIDs);
}
}
/// <summary>
///
/// </summary>
/// <param name="userID"></param>
/// <param name="name"></param>
/// <param name="pwd"></param>
/// <param name="waterUserID"></param>
static public void UpdateUser(int userID, string name, string pwd, int waterUserID,
int role, bool allowEdit, string editPassword)
{
string s = string.Format(
"update tb_User set [Name] = @name, [Password] = @pwd, " +
"[WaterUserID] = @waterUserID, Role = @role, AllowEdit = @allowEdit, " +
"editPassword = @editPassword " +
"where userid = " + userID);
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "name", name);
DBI.AddSqlParameter(cmd, "pwd", pwd);
DBI.AddSqlParameter(cmd, "waterUserID", waterUserID);
DBI.AddSqlParameter(cmd, "role", role);
DBI.AddSqlParameter(cmd, "allowEdit", allowEdit);
DBI.AddSqlParameter(cmd, "editPassword", editPassword);
DBI.GetDBI().ExecuteScalar(cmd);
}
/// <summary>
/// add user return id
/// </summary>
/// <param name="name"></param>
/// <param name="pwd"></param>
/// <param name="WaterUserID"></param>
/// <returns>0 - fail, otherwise - success</returns>
static public int AddUser(string name, string pwd, int WaterUserID, int role, bool allowEdit, string editPassword)
{
//string s = string.Format("INSERT INTO tb_User( [Name], [Password], [role_id], [WaterUserID]) " +
// " VALUES(@name, @pwd, 1, @waterUserID)");
string s = string.Format("INSERT INTO tb_User( [Name], [Password], [WaterUserID], role, allowEdit, editPassword) " +
" VALUES(@name, @pwd, @waterUserID, @role, @allowEdit, @editPassword)");
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "name", name);
DBI.AddSqlParameter(cmd, "pwd", pwd);
DBI.AddSqlParameter(cmd, "waterUserID", WaterUserID);
DBI.AddSqlParameter(cmd, "role", role);
DBI.AddSqlParameter(cmd, "allowEdit", allowEdit);
DBI.AddSqlParameter(cmd, "editPassword", editPassword);
DBI.GetDBI().ExecuteScalar(cmd);
return(GetUserID(name));
}
/// <summary>
///
/// </summary>
/// <param name="waterUserID"></param>
/// <param name="feeName"></param>
/// <param name="beginDT"></param>
/// <param name="endDT"></param>
/// <param name="unitPrice"></param>
/// <param name="totalPrice"></param>
/// <param name="usedAmount"></param>
/// <param name="remark"></param>
static public void InsertFee(int waterUserID, string feeName, DateTime beginDT, DateTime endDT, int tianShu,
double unitPrice, double waterAmount, double waterLost, double usedWater, double usedPrice,
double payPrice, double leftPrice, string remark)
{
string format =
"INSERT INTO tblFee(WaterUserID, FeeName, BeginDT, EndDT, NumberOfDay, " +
"UnitPrice, WaterAmount, WaterLost, UsedWater, UsedPrice, " +
"PayPrice, LeftPrice, Remark)" +
"VALUES('{0}', '{1}', '{2}', '{3}', {4}, " +
"'{5}', '{6}', '{7}', '{8}' ,'{9}', " +
"'{10}','{11}', '{12}') ";
string sql = string.Format(
format,
waterUserID, feeName, beginDT, endDT, tianShu,
unitPrice, waterAmount, waterLost, usedWater, usedPrice,
payPrice, leftPrice, remark);
DBI.GetDBI().ExecuteScalar(sql);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
static public string[] GetWaterLevelDeviceType()
{
List <string> r = new List <string>();
string WATER_LEVEL_DEVICE_TYPE = "WaterLevelDeviceType";
string sql = "select configValue from tblConfig where configName = '{0}'";
sql = string.Format(sql, WATER_LEVEL_DEVICE_TYPE);
DataTable tbl = DBI.GetDBI().ExecuteDataTable(sql);
foreach (DataRow row in tbl.Rows)
{
object obj = row[0];
if (obj != null && obj != DBNull.Value)
{
string type = obj.ToString().Trim();
r.Add(type);
}
}
return(r.ToArray());
}
public DataTable gcsjtj_query(string gcsj_id, string year)
{
string str1 = "";
if (!string.IsNullOrEmpty(gcsj_id))
{
str1 = "where tb_gcsjtj.gcsj_id='" + gcsj_id + "'";
}
if (!string.IsNullOrEmpty(year))
{
if (str1.Length > 0)
{
str1 = str1 + " and tb_gcsjtj.year='" + year + "'";
}
else
{
str1 = "where tb_gcsjtj.year='" + year + "'";
}
}
string str = "select * from tb_gcsjtj " + str1;
DataTable ds = DBI.GetDBI_yhxx().ExecuteDataTable(str);
return(ds);
}
/// <summary>
/// 根据登录角色构建一级菜单
/// </summary>
/// <param name="admin"></param>
/// <returns></returns>
public DataTable getFirstMenu(string role_id)
{
string strQuery;
string roletype = role_type(role_id);
if (int.Parse(roletype) == 1)//roletype=1所有管理局用户
{
if (role_id == "1")
{
strQuery = "select menu_id,name,menulink from tb_menu where fatherid=(select menu_id from tb_menu where fatherid=-1 and role_type=1) order by seq";
}
else
{
strQuery = "select menu_id,name,menulink from tb_menu where fatherid=(select menu_id from tb_menu where fatherid=-1 and role_type=1) and name!='系统维护管理' order by seq";
}
}
else//所有管理处用户
{
if (role_id == "2")
{
strQuery = "select menu_id,name,menulink from tb_menu where fatherid=(select menu_id from tb_menu where fatherid=-1 and role_type=2) order by seq";
}
else
{
strQuery = "select menu_id,name,menulink from tb_menu where fatherid=(select menu_id from tb_menu where fatherid=-1 and role_type=2) and name!='系统维护管理' order by seq";
}
}
try
{
return(DBI.GetDBI().ExecuteDataTable(strQuery));
}
catch
{
return(null);
}
}
/// <summary>
///
/// </summary>
/// <param name="waterUserID"></param>
/// <returns></returns>
static public DataTable GetStationDeviceDataTable()
{
string s = "select * from vStationDevice";
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
static public DataTable GetDataTable()
{
string s = "select * from vDitchDataLast";
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static DataTable GetDuringDataTable(int id)
{
string s = "select * from tblDuringWater where DuringWaterID = " + id;
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
static public DataTable GetDuringDataTable()
{
string s = "select * from tblDuringWater";
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
/// 获取登陆用户表
/// </summary>
/// <returns></returns>
static public DataTable GetUserDataTable()
{
string s = string.Format("select * from vUserWaterUser");
return(DBI.GetDBI().ExecuteDataTable(s));
}
static public DataTable GetUserDataTable(int userID)
{
string s = string.Format("select * from vUserWaterUser where userid = {0}", userID);
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <param name="userID"></param>
static public void DeleteUser(int userID)
{
string s = "delete from tb_user where userid = " + userID;
DBI.GetDBI().ExecuteScalar(s);
}
/// <summary>
///
/// </summary>
static public void DeleteFee(int feeID)
{
string s = "delete from tblFee where FeeID = " + feeID;
DBI.GetDBI().ExecuteDataTable(s);
}
/// <summary>
///
/// </summary>
/// <param name="waterUserID"></param>
static public void DeleteWaterUserStationOut(int waterUserID)
{
string s = string.Format("delete from tblOut where wateruserID = {0}", waterUserID);
DBI.GetDBI().ExecuteScalar(s);
}
static public DataTable Execute(string sql)
{
DBI dbi = DBI.GetDBI();
return(dbi.ExecuteDataTable(sql));
}
//#region GetWaterUserDataTable
/// <summary>
///
/// </summary>
/// <returns></returns>
static public DataTable GetWaterUserDataTable()
{
string s = "select * from vWaterUser";
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
static public DataTable GetWaterUserLevelDataTable()
{
string s = string.Format("select * from tblWaterUserLevel where deleted=0");
return(DBI.GetDBI().ExecuteDataTable(s));
}
/// <summary>
///
/// </summary>
/// <param name="needWaterID"></param>
static public void Deleted(int needWaterID)
{
string s = string.Format("delete from tblNeedWater where needWaterID = {0}", needWaterID);
DBI.GetDBI().ExecuteScalar(s);
}
/// <summary>
///
/// </summary>
/// <param name="distributeID"></param>
static public void Delete(int distributeID)
{
string s = string.Format("delete from tblDistribute where distributeWaterID = {0}", distributeID);
DBI.GetDBI().ExecuteScalar(s);
}
/// <summary>
///
/// </summary>
/// <param name="needWaterID"></param>
/// <returns></returns>
static public DataTable GetNeedWaterByID(int needWaterID)
{
string s = "select * from tblNeedWater where NeedWaterID = " + needWaterID;
return(DBI.GetDBI().ExecuteDataTable(s));
}