public List <ZzhaStation> GetAllStations()
{
List <ZzhaStation> list = new List <ZzhaStation>();
string selectstr = "select stationaddress,stationheadaddress,stationheadx,stationheady from Station_Head_Info order by stationaddress,stationheadaddress";
//SqlDataReader dr = dba.GetDataReader(selectstr);
DataTable dt = dba.GetDataSet(selectstr).Tables[0];
//while (dr.Read())
//{
// ZzhaStation zzhastation = new ZzhaStation();
// zzhastation.StationAddressNum = dr.GetInt32(0).ToString() + "." + dr.GetInt32(1).ToString();
// zzhastation.Position = Convert.ToString(dr.GetDouble(2)) + "," + Convert.ToString(dr.GetDouble(3));
// //zzhastation.Position = Convert.ToString(dr.GetDouble(2) + 25) + "," + Convert.ToString(dr.GetDouble(3) + 20);
// list.Add(zzhastation);
//}
for (int i = 0; i < dt.Rows.Count; i++)
{
ZzhaStation zzhastation = new ZzhaStation();
//zzhastation.StationAddressNum = dr.GetInt32(0).ToString() + "." + dr.GetInt32(1).ToString();
zzhastation.StationAddressNum = dt.Rows[i][0].ToString() + "." + dt.Rows[i][1].ToString();
zzhastation.Position = Convert.ToString(dt.Rows[i][2]) + "," + Convert.ToString(dt.Rows[i][3]);
//zzhastation.Position = Convert.ToString(dr.GetDouble(2) + 25) + "," + Convert.ToString(dr.GetDouble(3) + 20);
list.Add(zzhastation);
}
return(list);
}
/*
* 方法
*/
#region [ 方法: 查询设备信息 ]
public DataSet N_GetEquInfo(string strEquNO, string strEquName, string strFacID)
{
strSql = " Select " +
" EquNO as 设备编号, " +
" EquName as 设备名称, " +
" DeptName as 所属部门, " +
" Et1.Title as 设备类型, " +
" Et2.Title as 设备状态, " +
" FactoryName as 生产厂家, " +
" Ebi.Remark as 备注 " +
" From " +
" Equ_BaseInfo as Ebi left join EnumTable Et1 on Ebi.EquType=Et1.EnumID and Et1.FunID=9 " +
" left join EnumTable Et2 on Ebi.EquState=Et2.EnumID and Et2.FunID=10 " +
" left join Dept_Info as Di on Di.DeptID=Ebi.DeptID " +
" left join FactoryInfo as Fi on Fi.FactoryID=Ebi.FactoryID " +
" Where " +
" EquID<>0 ";
if (!(strEquNO.Equals("") | strEquNO.Equals(null)))
{
strSql += " And EquNO like '%" + strEquNO + "%'";
}
if (!(strEquName.Equals("") | strEquName.Equals(null)))
{
strSql += " And EquName like '%" + strEquName + "%'";
}
if (!strFacID.Equals("0"))
{
strSql += " And Ebi.FactoryID = " + strFacID;
}
return(dbacc.GetDataSet(strSql));
}
public void setCboUserGroup(ComboBox comUserName, string user)
{
string strSql = "Select UGname,id From UserGroups";
//if (user == "3shine")
//{
// strSql = " Select UGname,id From UserGroups ";
//}
//else
//{
// strSql = " getUserGroupsByAccount " + user;
//}
using (ds = new DataSet())
{
ds = dbacc.GetDataSet(strSql);
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
comUserName.DataSource = ds.Tables[0].DefaultView;
comUserName.DisplayMember = "UGname";
comUserName.ValueMember = "id";
comUserName.SelectedIndex = 0;
}
else
{
DataRow dr = ds.Tables[0].NewRow();
dr["UGname"] = "无";
dr["id"] = "0";
ds.Tables[0].Rows.InsertAt(dr, 0);
}
}
}
}
public DataSet GetHisBadStationInfo(string strStartTime, string strEndTime, string strStationAddress)
{
if (strStationAddress.Trim().Equals(""))
{
strSQL = " Select DISTINCT StationAddress as 分站编号, " +
" Count(StationAddress)as '故障次数', " +
" (Select dbo.FunConvertTime( sum(BadTime) ) From HistoryBadStations as Hbs2 " +
" Where Hbs1.StationAddress=Hbs2.StationAddress and Hbs2.StationHeadAddress=0 " +
" And Hbs2.BadBeginTime >='" + strStartTime + "' And Hbs2.BadBeginTime <='" + strEndTime + "' ) as '故障时间' " +
" From HistoryBadStations as Hbs1 " +
" Where StationHeadAddress =0 And Hbs1.BadBeginTime >='" + strStartTime + "' And Hbs1.BadBeginTime <='" + strEndTime + "' " +
" Group by StationAddress ";
}
else
{
strSQL = " Select DISTINCT StationAddress as 分站编号, " +
" Count(StationAddress)as '故障次数', " +
" (Select dbo.FunConvertTime( sum(BadTime) ) From HistoryBadStations as Hbs2 " +
" Where Hbs1.StationAddress=Hbs2.StationAddress and Hbs2.StationHeadAddress=0 " +
" And Hbs2.BadBeginTime >='" + strStartTime + "' And Hbs2.BadBeginTime <='" + strEndTime + "' And Hbs2.StationAddress=" + strStationAddress + " ) as '故障时间' " +
" From HistoryBadStations as Hbs1 " +
" Where StationHeadAddress =0 And Hbs1.BadBeginTime >='" + strStartTime + "' And Hbs1.BadBeginTime <='" + strEndTime + "' And Hbs1.StationAddress=" + strStationAddress +
" Group by StationAddress ";
}
return(dbacc.GetDataSet(strSQL));
}
public DataSet Get_EmpInWellStatement(string strDate)
{
SqlParameter[] para =
{
new SqlParameter("@strDate", SqlDbType.NVarChar, 20),
new SqlParameter("@strTable", SqlDbType.NVarChar, 20)
};
para[0].Value = strDate;
para[1].Value = DateTime.Parse(strDate).ToString("yyyyM");
return(dba.GetDataSet("A_Statement_EmpInWell", para));
}
/// <summary>
/// 获取部门树信息
/// </summary>
public DataSet GetHisDeptTree(string strWhere)
{
string strWhereSql = "1=1";
if (!strWhere.Trim().Equals(""))
{
strWhereSql = strWhere;
}
string strSQL = "select Dei.DeptID as ID,Dei.DeptName as Name ,Dei.ParentDeptID as ParentID,'true' as IsChild ,'false' as IsUserNum ,0 as Num From Dept_Info as Dei order by SerialNo, [name]";
return(dba.GetDataSet(strSQL));
}
/// <summary>
/// 根据FunID获得对应的数据
/// </summary>
/// <param name="funId"></param>
/// <returns>Title,EnumID</returns>
public DataTable getEnumInfo(int funId)
{
strSQL = string.Format("select Title,EnumID from EnumTable where FunID = {0}", funId);
using (DataSet ds = dba.GetDataSet(strSQL))
{
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
}
return(new DataTable());
}
/*
* 方法
*/
#region [ 方法: 查询分站信息 ]
/// <summary>
/// 查询分站信息
/// </summary>
/// <returns></returns>
public DataSet N_GetStationInfo()
{
strSql = " Select " +
" StationAddress as " + HardwareName.Value(CorpsName.StationAddress) + ", " +
" StationPlace as " + HardwareName.Value(CorpsName.StationSplace) + ", " +
" StationTel as 联系电话, " +
" Et1.Title as 状态 " +
" From " +
" Station_Info as Si left join EnumTable as Et1 on Si.StationState=Et1.EnumID and Et1.FunID=7 " +
" left join EnumTable as Et2 on Si.StationTypeID=Et2.EnumID and Et2.FunID=1 " +
" Order By " +
" StationAddress ";
return(dbacc.GetDataSet(strSql));
}
public DataTable GetAllRoute()
{
string sqlstr = "select routefrom,routeto,routelength from route";
DataSet ds = dba.GetDataSet(sqlstr);
if (ds != null)
{
return(ds.Tables[0]);
}
else
{
return(new DataTable());
}
}
public DataTable GetUserGroups()
{
string sqlstr = "select [id],ugname from UserGroups";
DataSet ds = dba.GetDataSet(sqlstr);
if (ds != null)
{
return(ds.Tables[0]);
}
else
{
return(new DataTable());
}
}
public DataTable GetAllFileName()
{
string sqlstr = "select Filename from ConfigFile_ZZHA";
DataSet ds = dba.GetDataSet(sqlstr);
if (ds != null)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}
public DataTable GetTerrialInfo()
{
string sqlstr = "select Territorialid,territorialName from Territorial_Info";
DataSet ds = dba.GetDataSet(sqlstr);
if (ds != null)
{
return(ds.Tables[0]);
}
else
{
return(new DataTable());
}
}
/// <summary>
/// 获取部门信息
/// </summary>
/// <returns>返回 部门信息(DataSet)</returns>
public DataSet N_GetDeptInfo()
{
string strSql;
strSql = " select DeptID,DeptName,ParentDeptID, " +
" (select count(distinct EmpID ) from (select EmpID From dbo.Emp_NowCompany where DeptID=Di.DeptID or DeptID in(select DeptID From Dept_Info Where ParentDeptID=Di.DeptID ) " +
" or DeptID in(Select DeptID From Dept_Info where DeptID in(Select DeptID From Dept_Info Where ParentDeptID=Di.DeptID)) " +
" or DeptID in(select DeptID From Dept_Info Where DeptID in(select DeptID From Dept_Info Where DeptID in(Select DeptID From Dept_Info where ParentDeptID =Di.DeptID))) " +
" )as T1 ) " +
" as Counts " +
" from Dept_Info as Di " +
" Order By DeptLevelID ";
return(dbacc.GetDataSet(strSql));
}
public byte[] GetBackMapByFileName(string filename)
{
string sqlstr = string.Format("select fileimg from A_GraphicsMapFile where [filename]='{0}'", filename);
DataSet ds = dba.GetDataSet(sqlstr);
if (ds != null)
{
return((byte[])ds.Tables[0].Rows[0][0]);
}
else
{
return(null);
}
}
public DataTable GetAllStation()
{
string sqlstr = "select StationAddress,stationplace from Station_Info";
DataSet ds = dba.GetDataSet(sqlstr);
if (ds != null)
{
return(ds.Tables[0]);
}
else
{
return(new DataTable());
}
}
public DataSet N_SearchDirectionalManage(string txtDetection, string strWhere)
{
bool blIsFirst = false;
strSql = " Select " +
" DetectionInfo as 标识, " +
" dbo.FunTrendInfo(DetectionInfo) as 位置, " +
" Directional as 方向性描述 " +
" From " +
" CodeSender_Directional ";
if (!(txtDetection.Equals("") | txtDetection.Equals(null)))
{
strSql += " Where DetectionInfo like '%" + txtDetection + "%'";
blIsFirst = true;
}
if (!(strWhere.Equals("") | strWhere.Equals(null)))
{
if (blIsFirst)
{
strSql += " And Directional like '%" + strWhere + "%'";
}
else
{
strSql += " Where Directional like '%" + strWhere + "%'";
}
}
return(dbacc.GetDataSet(strSql));
}
public DataTable GetHisStationHeadInfo(string sdate, string edate, int stationid, int stationheadid)
{
string sqlstr = string.Format("SELECT dbo.His_InOutStation.CodeSenderAddress as 标识卡,dbo.Emp_Info.EmpName as 姓名, dbo.Dept_Info.DeptName as 部门, " +
"dbo.His_InOutStation.StationHeadDetectTime as 监测时间 " +
"FROM dbo.His_InOutStation INNER JOIN " +
"dbo.Emp_Info ON " +
"dbo.His_InOutStation.UserID = dbo.Emp_Info.EmpID INNER JOIN " +
"dbo.Emp_NowCompany ON " +
"dbo.Emp_Info.EmpID = dbo.Emp_NowCompany.EmpID INNER JOIN " +
"dbo.Dept_Info ON dbo.Emp_NowCompany.DeptID = dbo.Dept_Info.DeptID " +
"WHERE (dbo.His_InOutStation.codesenderaddress IN " +
"(SELECT DISTINCT codesenderaddress " +
"FROM His_InOutMine " +
"WHERE outtime > '{0}' AND intime < '{1}')) AND " +
"(dbo.His_InOutStation.StationHeadDetectTime < '{1}') " +
"and dbo.His_InOutStation.StationAddress={2} and His_InOutStation.StationHeadAddress={3} " +
"order by dbo.His_InOutStation.StationHeadDetectTime", sdate, edate, stationid, stationheadid);
DataSet ds = dba.GetDataSet(sqlstr);
if (ds != null)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}
/*
* 方法
*/
#region [ 方法: 获取低电量信息 ]
/// <summary>
/// 获取低电量信息
/// </summary>
/// <param name="CodeSenderStateID">发码器状态,4:低电量</param>
/// <param name="CsTypeID">发码器配置类型;0:人员;1:设备;2:发码器</param>
/// <returns></returns>
public DataSet N_GetAlarmElectricity(int CodeSenderStateID, int intCsTypeID)
{
//string procName = "KJ128N_CodeAlarmElectricity_Select";
//SqlParameter[] sqlParmeters ={
// new SqlParameter("CodeSenderStateID",SqlDbType.Int)
//};
//sqlParmeters[0].Value = CodeSenderStateID;
//return dbacc.GetDataSet(procName, sqlParmeters);
strSql = " Select Csi.CodeSenderAddress as 发码器, " +
" Et.Title as 发码器状态, " +
" 配置类型= case when CsTypeID= 0 then '人员' when CsTypeID=1 then '设备' else '' end, " +
" 名称=case when CsTypeID=0 then EmpName when CsTypeID=1 then EquName else '' end, " +
" 部门=case when CsTypeID=0 then Di1.DeptName else '' end " +
" From CodeSender_Info as Csi left join CodeSender_Set as Css on Csi.CodeSenderAddress =Css.CodeSenderAddress " +
" Left Join Emp_Info as Ei on Ei.EmpID=Css.UserID and CsTypeID=0 " +
" Left Join Emp_NowCompany as Enc on Enc.EmpID=Ei.EmpID " +
" Left Join Dept_Info as Di1 on Di1.DeptID=Enc.DeptID " +
" Left Join Equ_BaseInfo as Ebi on Ebi.EquID=Css.UserID and Css.CsTypeID=1 " +
" Left Join Dept_Info as Di2 on Di2.DeptID=Ebi.DeptID " +
" Left Join EnumTable as Et on Et.EnumID=Csi.CodeSenderStateID and Et.FunID=2 " +
" Where Csi.CodeSenderStateID = " + CodeSenderStateID;
if (intCsTypeID.Equals(0) || intCsTypeID.Equals(1))
{
strSql += " And CsTypeID =" + intCsTypeID;
}
else if (intCsTypeID.Equals(2))
{
strSql += " And CsTypeID is Null ";
}
return(dbacc.GetDataSet(strSql));
}
/*
* 方法
*/
#region [ 方法: 获取人员信息 ]
public DataSet GetEmpInfo(string strID, bool blIsEmp)
{
strSql = " Select Css.CodeSenderAddress as 发码器编号, Ei.EmpName as 姓名, " +
" 身份证号= case when Ed.Idcard is null or Ed.Idcard='' then '无' else Ed.Idcard end, " +
" 出生年月= case when Ed.BirthDay is null then '无' else substring(convert(varchar(10), Ed.BirthDay,120),1,7) end ," +
" 职务= case when Dui.DutyName is null then '无' else Dui.DutyName end , " +
" 工种=case when Wti.WtName is null then '无' else Wti.WtName end, " +
" 所在区队班组= case when Enc.ClassGroup is null or Enc.ClassGroup='' then '无' else Enc.ClassGroup end, " +
" 工作地点= case when Enc.WorkPlace is null or Enc.WorkPlace='' then '无' else Enc.WorkPlace end " +
" From Emp_Info as Ei Left Join Emp_Detail as Ed on Ei.EmpID= Ed.EmpID " +
" Left Join Emp_NowCompany as Enc on Ei.EmpID=Enc.EmpID " +
" Left Join Emp_WorkType as Ewt on Ei.EmpID=Ewt.EmpID " +
" Left Join WorkType_Info as Wti on Ewt.WorkTypeID=Wti.WorkTypeID " +
" Left Join Duty_Info as Dui on Enc.DutyID=Dui.DutyID " +
" Left Join CodeSender_Set as Css on Ei.EmpID=Css.UserID and CsTypeID=0 ";
if (blIsEmp)
{
strSql += " Where Ei.EmpID =" + strID;
}
else
{
strSql += " Where Css.CodeSenderAddress =" + strID;
}
return(dbacc.GetDataSet(strSql));
}
public DataSet A_RT_PathCheck(string StrWhere)
{
string sql = "SELECT r.ID, dbo.CodeSender_Set.CodeSenderAddress AS 标识卡号, e.EmpName AS 姓名,wtif.WtName as 工种,"
+ "dbo.Dept_Info.DeptName AS 部门,du.DutyName 职务, t.IntervalName AS 班次,"
+ "dbo.Path_Info.PathName AS 巡检路线, r.CheckTime AS 开始巡检时间, "
+ "dbo.RealTimeCodeSender.LastPlace AS 现处位置, "
+ "dbo.RealTimeCodeSender.StationHeadDetectTime AS 入现处位置时间, r.EmpID, "
+ "dbo.Path_Info.Id AS pid, t.ID AS tid "
+ "FROM dbo.Path_Info INNER JOIN "
+ "dbo.RealTimePathCheck r INNER JOIN "
+ "dbo.Emp_Info e ON r.EmpID = e.EmpID left join "
+ "dbo.Emp_NowCompany as enow on e.EmpID=enow.EmpID left join "
+ "dbo.Duty_Info as du on enow.DutyID=du.DutyID LEFT JOIN "
+ "dbo.Path_Emp_Relation p ON r.EmpID = p.EmpID left JOIN "
+ "dbo.Emp_WorkType as empw on p.EmpID=empw.EmpID left join "
+ "dbo.WorkType_Info as wtif on empw.WorkTypeID=wtif.WorkTypeID left join "
+ "dbo.TimerInterval t ON r.[Interval] = t.ID INNER JOIN "
+ "dbo.CodeSender_Set ON e.EmpID = dbo.CodeSender_Set.UserID ON "
+ "dbo.Path_Info.PathNo = p.PathNo INNER JOIN "
+ "dbo.RealTimeCodeSender ON "
+ "dbo.CodeSender_Set.CsSetID = dbo.RealTimeCodeSender.CsSetID LEFT JOIN "
+ "dbo.Dept_Info INNER JOIN "
+ "dbo.Emp_NowCompany ON "
+ "dbo.Dept_Info.DeptID = dbo.Emp_NowCompany.DeptID ON "
+ "e.EmpID = dbo.Emp_NowCompany.EmpID where " + StrWhere;
return(db.GetDataSet(sql));
}
public DataSet SelectSpeedConfig(string strWhere)
{
strSQL = " Select FirstStationAddress as 起始传输分站编号, " +
" FirstStationHeadAddress as 起始读卡分站编号, " +
" Shi1.StationHeadPlace as 起始读卡分站位置, " +
" LastStationAddress as 终点传输分站编号, " +
" LastStationHeadAddress as 终点读卡分站编号, " +
" Shi2.StationHeadPlace as 终点读卡分站位置, " +
" OverSpeedTime = case when WalkTime=-1 then null else dbo.FunConvertTime(WalkTime) end, " +
" LackSpeedTime = case When LackWalkTime =-1 then null else dbo.FunConvertTime(LackWalkTime) end," +
" Os.Remark as 备注,OverSpeedID " +
" From OverSpeed as Os left join Station_Head_Info as Shi1 on Os.FirstStationAddress=Shi1.StationAddress and Os.FirstStationHeadAddress=Shi1.StationHeadAddress " +
" Left join Station_Head_Info as Shi2 on Os.LastStationAddress = Shi2.StationAddress and Os.LastStationHeadAddress=Shi2.StationHeadAddress " +
" Where " + strWhere;
return(dba.GetDataSet(strSQL));
}
public DataRow getStationInfo(int stationAddress)
{
string sqlString = string.Format("select * from Station_Info where StationAddress = {0}", stationAddress);
DataSet ds = dba.GetDataSet(sqlString);
if (ds.Tables != null && ds.Tables[0].Rows.Count != 0)
{
return(ds.Tables[0].Rows[0]);
}
return(ds.Tables[0].Rows[0]);
}
/// <summary>
/// 根据分站状态获取接收器信息
/// </summary>
/// <param name="strState">分站状态</param>
/// <returns></returns>
public DataSet N_GetStaHeadBreakInfo(string strState)
{
strSql = " Select * From KJ128N_RTStaHead_Info_View ";
if (!(strState.Equals("所有") | strState.Equals(null)))
{
strSql += " Where 接收器状态='" + strState + "' ";
}
strSql += " Order By 分站地址,接收器地址 ";
return(dbacc.GetDataSet(strSql));
}
/// <summary>
/// 查询 实时分站信息
/// </summary>
/// <returns></returns>
public DataSet N_StaBreakInfo(string strState)
{
strSql = " Select * From KJ128N_RTStation_Info_View ";
if (!(strState == "" | strState.Equals("所有") | strState.Equals(null)))
{
strSql += " Where 分站状态='" + strState + "' ";
}
strSql += " Order By 分站地址 ";
return(dbacc.GetDataSet(strSql));
}
public DataSet N_SearchRTInOutAntennaInfo(
string strStartTime,
string strEndTime,
string strCard,
string strStationAddress,
string strStationHeadAddress,
int intUserType)
{
strSql = " Select " +
" Ri.CodeSenderAddress As " + HardwareName.Value(CorpsName.CodeSenderAddress) + ", " +
" StationAddress As " + HardwareName.Value(CorpsName.StationAddress) + ", " +
" StationHeadAddress As " + HardwareName.Value(CorpsName.StaHeadAddress) + ", " +
" StationHeadDetectTime As 接收器监测时间, " +
" StationHeadAntennaA As 天线A, " +
" StationHeadAntennaB As 天线B, " +
" 进出状态=case InStationHeadAntenna when 1 then '进读卡分站' when 2 then '进读卡分站' else '出读卡分站' end, " +
" LastStationAddress As 上次" + HardwareName.Value(CorpsName.StationAddress) + ", " +
" LastStationHeadAddress As 上次" + HardwareName.Value(CorpsName.StaHeadAddress) + ", " +
" LastStationHeadAntennaA As 上次天线A, " +
" LastStationHeadAntennaB As 上次天线B " +
" From " +
" RT_InOutStation as Ri " +
" left join CodeSender_Set as Css on Ri.CodeSenderAddress=Css.CodeSenderAddress " +
" Where " +
" StationHeadDetectTime >= '" + strStartTime + "' And StationHeadDetectTime <= '" + strEndTime + "' ";
if (!(strCard.Equals("") | strCard.Equals(null)))
{
strSql += " And Ri.CodeSenderAddress = " + strCard;
}
////发码器类型
if (intUserType.Equals(2)) //未登记发码器
{
strSql += " And CsTypeID is Null ";
}
else if (intUserType.Equals(1) || intUserType.Equals(0)) //人员、设备
{
strSql += " And CsTypeID =" + intUserType;
}
if (!(strStationAddress.Equals("0")))
{
strSql += " And StationAddress = " + strStationAddress;
}
if (!(strStationHeadAddress.Equals("0")))
{
strSql += " And StationHeadAddress = " + strStationHeadAddress;
}
return(dbacc.GetDataSet(strSql));
}
public string GetDeptCounts(string strDeptID, string strStartTime, string strEndTime)
{
string strSql;
if (strDeptID == "0")
{
strSql = " Select count(distinct Hi.UserID) as Counts " +
" From His_InOutMine as Hi left join Emp_NowCompany as Enc on Enc.EmpID=Hi.UserID and CsTypeID=0 " +
" Where InTime>='" + strStartTime + "' And InTime<='" + strEndTime + "'" + " And CsTypeID=0 ";
}
else
{
strSql = " Select count(distinct Hi.UserID) as Counts " +
" From His_InOutMine as Hi left join Emp_NowCompany as Enc on Enc.EmpID=Hi.UserID and CsTypeID=0 " +
" Where ( DeptID=" + strDeptID + " or DeptID in(select DeptID From Dept_Info Where ParentDeptID=" + strDeptID + " ) " +
" or DeptID in(Select DeptID From Dept_Info where DeptID in(Select DeptID From Dept_Info Where ParentDeptID=" + strDeptID + ")) " +
" or DeptID in(select DeptID From Dept_Info Where DeptID in(select DeptID From Dept_Info Where DeptID in(Select DeptID From Dept_Info where ParentDeptID =" + strDeptID + ")))) " +
" And InTime>='" + strStartTime + "' And InTime<='" + strEndTime + "'" + " And CsTypeID=0 ";
}
DataSet tempDs;
using (tempDs = new DataSet())
{
tempDs = dbacc.GetDataSet(strSql);
if (tempDs != null && tempDs.Tables.Count > 0)
{
if (tempDs.Tables[0].Rows.Count > 0)
{
return(tempDs.Tables[0].Rows[0]["Counts"].ToString());
}
}
}
return(null);
}
public DataSet GetDeptTree_Emp(bool bl)
{
//if (bl) //显示上井口信息
//{
// strSQL = " Select * From A_DeptTree_RTStaHead_Emp ";
//}
//else //不显示上井口信息
//{
// //(Select count(1) From A_RTStaHead_Emp as Rt Where Rt.DeptID=Dei.DeptID and StationHeadTypeID=32)
// strSQL = " Select Dei.DeptID as ID, " +
// " Dei.DeptName as Name , " +
// " Dei.ParentDeptID as ParentID, " +
// " 'true' as IsChild , " +
// " 'false' as IsUserNum , " +
// " 0 as Num " +
// " From Dept_Info as Dei ";
//}
strSQL = " Select Dei.DeptID as ID, " +
" Dei.DeptName as Name , " +
" Dei.ParentDeptID as ParentID, " +
" 'true' as IsChild , " +
" 'false' as IsUserNum , " +
" 0 as Num " +
" From Dept_Info as Dei order by serialno,DeptName ";
return(dba.GetDataSet(strSQL));
}
/// <summary>
/// 得到所有探头信息
/// </summary>
/// <returns>信息表</returns>
public DataTable GetStationInfo()
{
string selectstring = "select * from A_Graphics_StationHeadState";
DataSet ds = dba.GetDataSet(selectstring);
if (ds != null)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}
public DataSet SelectOverEmp(int intOverEmpType)
{
strSql = " Select " +
" RT_RatingEmployeesCount as 额定下井人数 , " +
" RT_FactEmployeeCount as 当前下井人数, " +
" RT_FactEmployeeCount-RT_RatingEmployeesCount as 超员人数, " +
" RT_OverEmployeeBeginTime as 超员开始时间, " +
" dbo.FunConvertTime((Select DateDiff(ss, Ro.RT_OverEmployeeBeginTime, getDate()))) As 持续时间" +
" From " +
" RT_OverEmployees as Ro" +
" Where " +
" Ro.RT_OverEmployeeTypeID = " + intOverEmpType.ToString();
return(dbacc.GetDataSet(strSql));
}
/// <summary>
/// 根据部门获取标识卡号信息
/// </summary>
/// <param name="deptID"></param>
/// <returns></returns>
public DataSet GetEmpInfo(string deptID)
{
string strWhere = "";
if (deptID.Trim() != "")
{
strWhere = "e.DeptID=" + deptID;
}
else
{
strWhere = "1=1";
}
string strSql = "select convert(varchar(20),cs.CodeSenderAddress) as CodeSenderAddress,e.empName from emp_info e join CodeSender_Set cs ON e.empid=cs.userID AND cs.CsTypeID = 0 where " + strWhere;
return(DB.GetDataSet(strSql));
}
/// <summary>
/// 备份指定数据库文件
/// </summary>
/// <param name="strDbName">数据库名称</param>
/// <param name="strFileName">备份数据库的路径</param>
/// <param name="strServerName">服务器名称</param>
/// <param name="strUserName">用户名</param>
/// <param name="strPassword">密码</param>
/// <param name="prosBar">进度条</param>
/// <returns></returns>
public static bool BackUPDB(string strDbName, string strFileName, string strServerName, string strUserName, string strPassword, ProgressBar bar)
{
#region [ 判断磁盘剩余空间是否可进行备份 ]
DBAcess dba = new DBAcess();
DataSet tmpds = dba.GetDataSet("exec sp_spaceused");
if (tmpds.Tables.Count > 0 && tmpds.Tables[0] != null)
{
// 得到数据库大小
string tmpStr = tmpds.Tables[0].Rows[0]["database_size"].ToString();
int dbSize = Convert.ToInt32(tmpStr.Substring(0, tmpStr.LastIndexOf(".")));
// 获取磁盘剩余空间大小
try
{
DriveInfo d = new DriveInfo(strFileName.Substring(0, 1));
int diskSize = Convert.ToInt32(d.AvailableFreeSpace / 1024 / 1024);
if (diskSize < dbSize)
{
MessageBox.Show("备份数据库所需空间不能小于" + dbSize + "M\r\n磁盘[" + d.Name + "]存储空间过小,无法备份");
return false;
}
}
catch (Exception)
{
MessageBox.Show("路径不正确");
return false;
//throw;
}
}
#endregion
pBar = bar;
string strTmp = "";
string tmpPath = strFileName.Substring(0, strFileName.LastIndexOf("\\")).ToString();
int isEmpty = tmpPath.IndexOf(" ");
SQLDMO.SQLServer svr = null;
try
{
svr = new SQLDMO.SQLServerClass();
// 连接到数据库
svr.Connect(strServerName, strUserName, strPassword);
SQLDMO.Backup bak = new SQLDMO.BackupClass();
bak.Action = 0;
bak.Initialize = true;
#region 进度条处理
if (pBar != null)
{
pBar.Visible = true;
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
bak.PercentComplete += pceh;
}
#endregion
#region [ 文件夹名称中有空格: 备份前的处理 ]
// 文件夹不存在时自动创建
if (!Directory.Exists(tmpPath))
{
Directory.CreateDirectory(tmpPath);
}
// 文件夹名称 中有空格 备份文件路径设置为根目录的临时文件夹tmpBackup中
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
strTmp = strFileName.Substring(0, 1).ToString() + ":\\tmp_backup.kj";
}
else
{
strTmp = strFileName;
}
#endregion
// 数据库的备份的名称及文件存放位置
bak.Files = strTmp;
bak.Database = strDbName;
// 备份
bak.SQLBackup(svr);
}
catch (Exception err)
{
if (SqlErrRepair(err.Message.ToString()))
{
BackUPDB(strDbName, strFileName, strServerName, strUserName, strPassword, pBar);
return true;
}
return false;
//MessageBox.Show("备份数据库失败");
}
finally
{
if (svr != null)
{
svr.DisConnect();
}
#region [ 文件夹名称中有空格: 备份完成后的处理 ]
// 文件夹名称 中有空格 将备份的文件移动到用户指定的文件夹并将临时目录删除
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
// 文件存在则替换
if (File.Exists(strFileName.Substring(strFileName.LastIndexOf("\\") + 2)))
{
File.Delete(strFileName.Substring(strFileName.LastIndexOf("\\") + 2));
}
File.Move(strTmp, strFileName);
}
#endregion
}
return true;
}
public static void ShrinkDataBase()
{
DBAcess dba = new DBAcess();
try
{
// 数据库名从连接字符串中得到
string dbName = ConfigurationSettings.AppSettings["ConnectionString"];//dba.ConnectionStringKJ128N;
dbName = dbName.Substring(dbName.IndexOf("database=") + 9);
dbName = dbName.Substring(0, dbName.IndexOf(";"));
dba.GetDataSet("DUMP TRANSACTION " + dbName + " WITH NO_LOG");
MessageBox.Show("清空日志成功");
}
catch (Exception ex)
{
MessageBox.Show("清空日志失败");
}
}