salarymanage.java

本系统是基于JAVA语言的办公系统

		sqlStr = "select * from salary";
		if(!searchYear.equals("")) {
			sqlStr = sqlStr + " where year = " + searchYear;
			sqlCondition = true;
		}
		if(!searchMonth.equals("")) {
			if(sqlCondition) {
				sqlStr = sqlStr + " and month = " + searchMonth;
				sqlCondition = true;
			} else {
				sqlStr = sqlStr + " where month = " + searchMonth;
			}
		}
		if(!(searchZiduan.equals(""))) {
			if(sqlCondition) {
				sqlStr = sqlStr + " and "+ searchZiduan + " like '%" + keyword + "%'";
			} else {
				sqlStr = sqlStr + " where " + searchZiduan + " like '%" + keyword + "%'";
			}
		}
		try {
			rs = stmt.executeQuery(sqlStr);
			salarylist = new Vector(rscount);
			for(int i=0;i<pageSize*(page-1);i++)rs.next();//跳过前面页的记录
			while (rs.next())
			{
				salary aSalaryRecord = new salary();
				aSalaryRecord.setSalaryId(rs.getLong("salaryId"));
				aSalaryRecord.setEmployeeId(rs.getString("employeeId"));
				aSalaryRecord.setEmployeeName(rs.getString("employeeName"));
				aSalaryRecord.setYear(rs.getInt("year"));
				aSalaryRecord.setMonth(rs.getInt("month"));
				aSalaryRecord.setBasicWage(rs.getFloat("basicWage"));
				aSalaryRecord.setOvertimeWage(rs.getFloat("overtimeWage"));
				aSalaryRecord.setTrafficWage(rs.getFloat("trafficWage"));
				aSalaryRecord.setTotalWage(rs.getFloat("totalWage"));
				aSalaryRecord.setKaoqinReduce(rs.getFloat("kaoqinReduce"));
				aSalaryRecord.setSecureReduce(rs.getFloat("secureReduce"));
				aSalaryRecord.setTaxReduce(rs.getFloat("taxReduce"));
				aSalaryRecord.setTotalReduce(rs.getFloat("totalReduce"));
				aSalaryRecord.setRealWage(rs.getFloat("realWage"));
				aSalaryRecord.setEditTime(rs.getString("editTime"));
				aSalaryRecord.setMemo(rs.getString("memo"));
				salarylist.addElement(aSalaryRecord);
			}
			rs.close();
			return true;
		}
		catch (SQLException e)
		{
			errorMessage = errorMessage + sqlStr + e.getMessage();
			System.out.println(e);
			return false;
		}
	}

  //向数据库中工资信息表中插入一条新的记录
	public boolean insert() throws Exception {
		String editTime;
    Calendar cal=Calendar.getInstance(); 
    Integer y,m,d,h,mi,s;
    y=cal.get(Calendar.YEAR); 
    m=cal.get(Calendar.MONTH) + 1; 
    d=cal.get(Calendar.DATE); 
    h=cal.get(Calendar.HOUR_OF_DAY); 
    mi=cal.get(Calendar.MINUTE); 
    s=cal.get(Calendar.SECOND); 
    editTime = y+"年"+m+"月"+d+"日"+h+"时"+mi+"分"+s+"秒"; 
    
    Float totalWage = oneSalaryRecord.getBasicWage() + oneSalaryRecord.getOvertimeWage() + oneSalaryRecord.getTrafficWage();
    Float totalReduce = oneSalaryRecord.getKaoqinReduce() + oneSalaryRecord.getSecureReduce() + oneSalaryRecord.getTaxReduce();
		sqlStr = "insert into salary(employeeId,employeeName,year,month,basicWage,overtimeWage,trafficWage,totalWage,kaoqinReduce,secureReduce,taxReduce,totalReduce,realWage,editTime,memo) values ('";
		sqlStr = sqlStr + strFormat.toSql(oneSalaryRecord.getEmployeeId()) + "','";
		sqlStr = sqlStr + strFormat.toSql(oneSalaryRecord.getEmployeeName()) + "',";
	  sqlStr = sqlStr + oneSalaryRecord.getYear() + ",";
	  sqlStr = sqlStr + oneSalaryRecord.getMonth() + ",";
	  sqlStr = sqlStr + oneSalaryRecord.getBasicWage() + ",";
	  sqlStr = sqlStr + oneSalaryRecord.getOvertimeWage() + ",";
	  sqlStr = sqlStr + oneSalaryRecord.getTrafficWage() + ",";
	  sqlStr = sqlStr + totalWage + ",";
	  sqlStr = sqlStr + oneSalaryRecord.getKaoqinReduce() + ",";
	  sqlStr = sqlStr + oneSalaryRecord.getSecureReduce() + ",";
	  sqlStr = sqlStr + oneSalaryRecord.getTaxReduce() + ",";
	  sqlStr = sqlStr + totalReduce + ",";
	  sqlStr = sqlStr + (totalWage - totalReduce) + ",'";
	  sqlStr = sqlStr + strFormat.toSql(editTime) + "','";
	  sqlStr = sqlStr + strFormat.toSql(oneSalaryRecord.getMemo()) + "')";
		try
		{
			stmt.execute(sqlStr);
			return true;
		}
		catch (SQLException sqle)
		{	
			errorMessage = errorMessage + sqlStr + sqle.getMessage();
			return false;
		}
	}
	
	//更新假条表中的一条记录
	public boolean update() throws Exception {
		String editTime;
    Calendar cal=Calendar.getInstance(); 
    Integer y,m,d,h,mi,s;
    y=cal.get(Calendar.YEAR); 
    m=cal.get(Calendar.MONTH) + 1; 
    d=cal.get(Calendar.DATE); 
    h=cal.get(Calendar.HOUR_OF_DAY); 
    mi=cal.get(Calendar.MINUTE); 
    s=cal.get(Calendar.SECOND); 
    editTime = y+"年"+m+"月"+d+"日"+h+"时"+mi+"分"+s+"秒"; 
    Float totalWage = oneSalaryRecord.getBasicWage() + oneSalaryRecord.getOvertimeWage() + oneSalaryRecord.getTrafficWage();
    Float totalReduce = oneSalaryRecord.getKaoqinReduce() + oneSalaryRecord.getSecureReduce() + oneSalaryRecord.getTaxReduce();
		sqlStr = "update salary set ";
		sqlStr = sqlStr + "employeeId = '" + strFormat.toSql(oneSalaryRecord.getEmployeeId()) + "',";
		sqlStr = sqlStr + "employeeName = '" + strFormat.toSql(oneSalaryRecord.getEmployeeName()) + "',";
		sqlStr = sqlStr + "year =" + oneSalaryRecord.getYear() + ",";
		sqlStr = sqlStr + "month =" + oneSalaryRecord.getMonth() + ",";
		sqlStr = sqlStr + "basicWage =" + oneSalaryRecord.getBasicWage() + ",";
		sqlStr = sqlStr + "overtimeWage =" + oneSalaryRecord.getOvertimeWage() + ",";
		sqlStr = sqlStr + "trafficWage =" + oneSalaryRecord.getTrafficWage() + ",";
		sqlStr = sqlStr + "totalWage =" + totalWage + ",";
		sqlStr = sqlStr + "kaoqinReduce =" + oneSalaryRecord.getKaoqinReduce() + ",";
		sqlStr = sqlStr + "secureReduce =" + oneSalaryRecord.getSecureReduce() + ",";
		sqlStr = sqlStr + "taxReduce =" + oneSalaryRecord.getTaxReduce() + ",";
		sqlStr = sqlStr + "totalReduce =" + totalReduce + ",";
		sqlStr = sqlStr + "realWage =" + (totalWage - totalReduce) + ",";
		sqlStr = sqlStr + "editTime = '" + editTime + "',";
		sqlStr = sqlStr + "memo = '" + strFormat.toSql(oneSalaryRecord.getMemo()) + "' ";
		sqlStr = sqlStr + "where salaryId =" + oneSalaryRecord.getSalaryId();
		System.out.println(sqlStr);
		try
		{		
			stmt.execute(sqlStr);
			return true;
		}
		catch (SQLException e)
		{
			errorMessage = sqlStr + e.getMessage();
			return false;
		}
	}

  //删除编号为salaryId的工资信息
	public boolean delete(String salaryId) throws Exception {
		sqlStr = "delete from salary where salaryId ="  + salaryId;
		try
		{
			stmt.execute(sqlStr);
			return true;
		}
		catch (SQLException e)
		{
			System.out.println(e);
			return false;
		}
	}

  //得到编号为salaryId的工资信息并保存的salarylist向量数组中
	public boolean getOneSalary(long salaryId) throws Exception {
		try
		{
			sqlStr="select * from salary where salaryId =" + salaryId;
			rs = stmt.executeQuery(sqlStr);
			if (rs.next())
			{	
				salarylist = new Vector(1);
				salary aSalaryRecord = new salary();
				aSalaryRecord.setSalaryId(rs.getLong("salaryId"));
				aSalaryRecord.setEmployeeId(rs.getString("employeeId"));
				aSalaryRecord.setEmployeeName(rs.getString("employeeName"));
				aSalaryRecord.setYear(rs.getInt("year"));
				aSalaryRecord.setMonth(rs.getInt("month"));
				aSalaryRecord.setBasicWage(rs.getFloat("basicWage"));
				aSalaryRecord.setOvertimeWage(rs.getFloat("overtimeWage"));
				aSalaryRecord.setTrafficWage(rs.getFloat("trafficWage"));
				aSalaryRecord.setTotalWage(rs.getFloat("totalWage"));
				aSalaryRecord.setKaoqinReduce(rs.getFloat("kaoqinReduce"));
				aSalaryRecord.setSecureReduce(rs.getFloat("secureReduce"));
				aSalaryRecord.setTaxReduce(rs.getFloat("taxReduce"));
				aSalaryRecord.setTotalReduce(rs.getFloat("totalReduce"));
				aSalaryRecord.setRealWage(rs.getFloat("realWage"));
				aSalaryRecord.setEditTime(rs.getString("editTime"));
				aSalaryRecord.setMemo(rs.getString("memo"));
				salarylist.addElement(aSalaryRecord);
			} else {
				rs.close();
				errorMessage = sqlStr;
				return false;
			}
			rs.close();
			return true;
		}
		catch (SQLException e)
		{
			errorMessage = sqlStr + e.getMessage();
			return false;
		}
		
	}

	public int getPage() {				//显示的页码
		return page;
	}
	public void setPage(int newpage) {
		page = newpage;
	}

	public int getPageSize(){			//每页显示的图书数
		return pageSize;
	}
	public void setPageSize(int newpsize) {
		pageSize = newpsize;
	}

	public int getPageCount() {				//页面总数
		return pageCount;
	}
	public void setPageCount(int newpcount) {
		pageCount = newpcount;
	}

	public long getRecordCount() {
		return recordCount;
	}
	public void setRecordCount(long newrcount) {
		recordCount= newrcount;
	}
};