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); }