private DataTable GetData(string sql)
{
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 删除指定的员工
/// </summary>
/// <param name="empNo"></param>
/// <returns></returns>
public static int Del(string empNo)
{
sqlHandler sh = new sqlHandler();
/*string sqlcheckKeeper = string.Format("select Count(*) from keeper where id ={0}", empNo);
* if (int.Parse(sh.GetData(sqlcheckKeeper).Rows[0][0].ToString()) == 0)
* {
* string sqlcheck = string.Format("select count(*)from Borrow where Borrwer={0}", empNo);
* if (int.Parse(sh.GetData(sqlcheck).Rows[0][0].ToString()) == 0)
* {
* string sqlDel = string.Format("delete from employee where empid={0}", empNo);
* return sh.ExecuteNonQuery(sqlDel);
* }
* else
* {
* return -2;
*
* }
*
* }
* else
* {
* return -1;
*
* }*/
string sqlcheckSyUsers = string.Format("select Count(*) from SyUsers where id ='{0}'", empNo);
if (int.Parse(sh.GetData(sqlcheckSyUsers).Rows[0][0].ToString()) == 0)
{
string sqlDel = string.Format("delete from employee where empid='{0}'", empNo);
return(sh.ExecuteNonQuery(sqlDel));
}
return(-1);
}
/// <summary>
/// 查询最近1年内的资产归还记录
/// </summary>
/// <returns></returns>
public static DataTable getInfoLastYear()
{
string sql = "select * from view_return where datediff(yy,归还日期,getdate())<=1";
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 得到某一笔资产的信息
/// </summary>
/// <returns></returns>
public static DataTable GetOneInfo(string eqno)
{
sqlHandler sh = new sqlHandler();
string sql = string.Format("select eqno,type,name,label,price,birthday,model,plus,keepplace,keeper,maker from Equipment where eqno='{0}'", eqno);
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 得到某员工注销的某一笔资产的相关信息
/// </summary>
/// <param name="serialNo">单号号</param>
/// <returns></returns>
public static DataTable GetOneClear(string serialNo)
{
string sql = string.Format("SELECT cType, cDate, remark, EqNo FROM ClearLog WHERE serialNo='{0}'", serialNo);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 根据用户登录名得到用户的真实姓名
/// </summary>
/// <param name="loginname"></param>
/// <returns></returns>
public static string GetANameByLoginName(string loginname)
{
string sql = string.Format("select name from syUsers where loginname='{0}'", loginname);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt.Rows[0][0].ToString() : "");
}
/// <summary>
/// 得到某员工领用的某一笔资产的相关信息
/// </summary>
/// <param name="eqno">资产编码</param>
/// <param name="boorow">领用人id</param>
/// <returns></returns>
public static DataTable GetOneFix(string ID)
{
string sql = string.Format("SELECT maintainer,mDate,rDate,mRemark, EqNo FROM FixLog WHERE serialNo='{0}'", ID);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 得到某员工领用的某一笔资产的相关信息
/// </summary>
/// <param name="eqno">资产编号</param>
/// <param name="boorow">领用人id</param>
/// <returns></returns>
public static DataTable GetOneBorrow(string eqno, int boorow)
{
string sql = string.Format("select a.count,a.depart,b.name,a.date from borrow a left join employee b on(a.borrwer=b.empid) where eqno='{0}'and borrwer={1}", eqno, boorow);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 统计各类资产领用的总量
/// </summary>
/// <returns></returns>
public static DataTable TypeBorrowSatat()
{
string sql = "select * from view_BorrowBytype ";
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 得到的用户部门ID
/// </summary>
/// <returns></returns>
public static string GetDepartmentIDByUser(string user)
{
string sql = string.Format("select DepartId from SyUsers where LoginName='{0}'", user);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt.Rows[0][0].ToString() : "");
}
/// <summary>
/// 得到数据库的备份日志
/// </summary>
/// <param name="filename">备份文件的完整文件名</param>
/// <returns></returns>
public static DataTable GetBackupLog(string filename)
{
string sql = string.Format("restore headeronly from disk='{0}'", filename);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 按资产增长方式查询资产信息
/// </summary>
/// <param name="name">资产名称</param>
/// <returns></returns>
public static DataTable GetInfobyAddtype(string addtype)
{
sqlHandler sh = new sqlHandler();
string sql = string.Format("select * from view_eqInfo where 增长方式='{0}'", addtype);
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 按资产登记人查询资产信息
/// </summary>
/// <param name="name">资产名称</param>
/// <returns></returns>
public static DataTable GetInfobyBooker(string booker)
{
sqlHandler sh = new sqlHandler();
string sql = string.Format("select * from view_eqInfo where 保存地点='{0}'", booker);
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 按资产名称查询资产信息
/// </summary>
/// <param name="name">资产名称</param>
/// <returns></returns>
public static DataTable GetInfobyName(string name)
{
sqlHandler sh = new sqlHandler();
string sql = string.Format("select * from view_eqInfo where 资产名称='{0}'", name);
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 按编号查询资产信息
/// </summary>
/// <returns></returns>
public static DataTable GetInfobyEqno(string eqno)
{
sqlHandler sh = new sqlHandler();
string sql = string.Format("select * from view_eqInfo where 资产编号='{0}'", eqno);
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 得到某一笔资产的详细信息
/// </summary>
/// <param name="eqno"></param>
/// <returns></returns>
public static DataTable GetOneMostInfo(string eqno)
{
sqlHandler sh = new sqlHandler();
string sql = string.Format("select * from Equipment where eqno='{0}'", eqno);
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 得到各比资产的利用率
/// </summary>
/// <returns></returns>
public static DataTable eqUing()
{
string sql = "select * from view_EqUseing";
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 统计所有资产的折旧信息
/// </summary>
/// <returns></returns>
public static DataTable BecomeOld()
{
sqlHandler sh = new sqlHandler();
string sql = "select * from view_BecomeOld";
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 根据登录ID获取其他信息
/// </summary>
/// <param name="loginid"></param>
/// <returns></returns>
public static DataTable GetOneUser(string loginid)
{
string sql = string.Format("select * from syUsers where id='{0}'", loginid);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 查询指定日期的资产归还记录
/// </summary>
/// <returns></returns>
public static DataTable getInfoYearMonth(int year, int month)
{
string sql = string.Format("select * from view_return where year(归还日期)={0} and month(归还日期)={1}", year, month);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 得到某笔资产最大可领用数量
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static int GetMaxBoroow(string id)
{
sqlHandler sh = new sqlHandler();
string sql = string.Format("exec proc_GetMaxCountCanUse '{0}'", id);
DataTable dt = sh.GetData(sql);
return(dt != null?int.Parse(dt.Rows[0][0].ToString()) : -1);
}
/// <summary>
/// 查询某一笔资产的归还情况(模糊查询)
/// </summary>
/// <param name="eqno">资产编码</param>
/// <returns></returns>
public static DataTable GetInfoByEqno2(string eqno)
{
string sql = string.Format("select * from view_return where 资产编码 like '{0}%'", eqno);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 按部门统计资产的领用
/// </summary>
/// <returns></returns>
public static DataTable BorrowStat()
{
string sql = "select * from view_ReportBorrowByDepart";
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 查询某人领用的资产归还记录（模糊查寻）
/// </summary>
/// <param name="Borrower">领用人工号</param>
/// <returns></returns>
public static DataTable GetInfoByBorrower2(int Borrower)
{
string sql = string.Format("select * from view_return where 领用人工号 like '{0}%'", Borrower);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 查询所有的保管人员
/// </summary>
/// <returns></returns>
public static DataTable GetAll()
{
string sql = "select * from keeper";
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null?dt:null);
}
/// <summary>
/// 得到某一个员工的信息
/// </summary>
/// <param name="empid"></param>
/// <returns></returns>
public static DataTable GetOneEmp(int empid)
{
string sql = string.Format("select * from employee where empid={0}", empid);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 得到所有用户除了密码以外的信息
/// </summary>
/// <returns></returns>
public static DataTable GetAllUser()
{
string sql = "select loginname,name from syUsers";
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 得到资产的全部信息
/// </summary>
/// <returns></returns>
public static DataTable GetAll()
{
sqlHandler sh = new sqlHandler();
string sql = "select 资产编号=eqno,资产类型=type,资产名称=name,品牌=label,数量=count,单位=unit,单价=price,生产时间=birthday,型号=model,配置=plus,增长方式=addtype,保存地点=keepplace,保管人=keeper,预计寿命=usetime,登记人=booker,登记时间=booddate,生产厂家=maker from Equipment";
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 得到某员工领用的某一笔资产的完整信息
/// </summary>
/// <param name="eqno">资产编码</param>
/// <param name="boorow">领用人id</param>
/// <returns></returns>
public static DataTable GetOneAllFix(string ID)
{
string sql = string.Format("SELECT * FROM View_FixLog WHERE 流水号='{0}'", ID);
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
/// <summary>
/// 得到所有有员工的部门
/// </summary>
/// <returns></returns>
public static DataTable Getdepart()
{
string sql = "select id,departname from department where id in (select departid from employee) ";
sqlHandler sh = new sqlHandler();
DataTable dt = sh.GetData(sql);
return(dt != null ? dt : null);
}
