public void addMember(string mID, string mName, string mTel, string mGender, DateTime mBirth,
string mAddr, DateTime mOpenTime, DateTime mExpireTime, string mLevel) //向Member表里添加一行数据
{
string sql = "insert into Member values('" + mID + "','" + mName + "','" + mTel + "','" + mGender + "'," +
"to_date('" + mBirth + "','yyyy-mm-dd,hh24:mi:ss')" + ",'" + mAddr + "'," + "to_date('" + mOpenTime +
"','yyyy-mm-dd,hh24:mi:ss')" + "," + "to_date('" + mExpireTime + "','yyyy-mm-dd,hh24:mi:ss')" + ",'" + mLevel + "')";
int result = dbutil.ExecNonQuery(sql);
}
public void writerecord()
{
//添加预订订单
time = System.DateTime.Now;
string sql1 = "insert into ReserveRecord(rev_id,rev_time,is_success,mem_id) values('" + reserve_number + "'," + "to_date('" + Convert.ToString(time) + "','yyyy-mm-dd,hh24:mi:ss')" + ",'0'," + "'" + mem_id + "')";
dbutil.ExecNonQuery(sql1);
//添加预订订单号-书籍-数量
for (int i = 0; i < books.Count; i++)
{
string sql = "insert into Include(rev_id,b_isbn,incl_amount) values('" + reserve_number + "','" + books[i].book_id + "'," + books[i].res_amount + ")";
dbutil.ExecNonQuery(sql);
}
}
///功能：删除员工
///输入：员工id,数据库连接ControlAccess对象
///输出：bool,删除成功返回true,否则返回false
public bool DeleteStaff(string staId, ControlAccess conn)
{
if (InquireStaffInfo(staId, conn))//数据库中能查询到此员工
{
string sqlcommand = "update STAFF set STA_ON_JOB='NO' where STA_ID='" + staId + "'";
int ires = conn.ExecNonQuery(sqlcommand);
//Console.WriteLine("delete successfully!");
return(true);
}
return(false);
}
/// <summary>
/// 创建出勤记录
/// </summary>
/// <param name="staId"></param>员工id
/// <param name="arDate"></param>出勤日期
/// <param name="isLate"></param>是否迟到
/// <param name="isLevEarly"></param>是否早退
/// <param name="isAbsent"></param>是否缺勤
/// <param name="conn"></param>数据库连接ControlAccess对象
/// <returns></returns>若操作成功返回true,否则返回false
public bool CreateAttendInfo(string staId, string arDate, char isLate, char isLevEarly, char isAbsent, ControlAccess conn)
{
if (InquireAttendance(staId, arDate, conn) == false)
{
string sqlcommand = "insert into ATTENDANCERECORD values('" +
staId + "'," + "to_date('" + arDate + "','yyyy-mm-dd'), " + "'" + isLate + "','" + isLevEarly + "','" + isAbsent + "')";
int ires = conn.ExecNonQuery(sqlcommand);
return(true);
}
return(false);
}
public bool CreateLeaveInfo(string staId, string lrStartTime, string lrEndTime,
int lrDays, ControlAccess conn)
{
if (InquireLeave(staId, lrStartTime, conn) == false)
{
string sqlcommand = "insert into LEAVERECORD values(" +
"to_date('" + lrStartTime + "','yyyy-mm-dd'), " + "'" + staId + "'," + "to_date('" + lrEndTime + "','yyyy-mm-dd'), " + "'" + lrDays + "')";
int ires = conn.ExecNonQuery(sqlcommand);
//sqlcommand = "select extract(year from" + "to_date('" + lrStartTime + "','yyyy-mm-dd')"+")";
//DataSet dataset = conn.GetDataSet(sqlcommand);
return(true);
}
return(false);
}
/// <summary>
/// 更新员工信息
/// </summary>
/// <param name="staId"></param>员工id
/// <param name="staName"></param>员工姓名
/// <param name="staGender"></param>员工性别
/// <param name="staAddress"></param>员工住址
/// <param name="staBirth"></param>员工生日
/// <param name="staTele"></param>员工电话
/// <param name="staSignDate"></param>员工入职日期
/// <param name="poTitle"></param>员工职位
/// <param name="staPassword"></param>员工密码
/// <param name="conn"></param>数据库连接ControlAccess对象
/// <returns></returns>更新是否成功,若成功返回true,否则返回false
public bool UpdateStaffInfo(string staId, string staName, string staGender, string staAddress,
string staBirth, string staTele, string staSignDate, string poTitle, string state,
ControlAccess conn)
{
if (InquireStaffInfo(staId, conn))
{
string sqlCommand = "update STAFF set STA_NAME=" + "'" + staName + "'" + "where STA_ID=" +
"'" + staId + "'";
int ires = conn.ExecNonQuery(sqlCommand);
sqlCommand = "update STAFF set STA_GENDER=" + "'" + staGender + "'" + "where STA_ID=" +
"'" + staId + "'";
ires = conn.ExecNonQuery(sqlCommand);
sqlCommand = "update STAFF set STA_ADDRESS=" + "'" + staAddress + "'" + "where STA_ID=" +
"'" + staId + "'";
ires = conn.ExecNonQuery(sqlCommand);
sqlCommand = "update STAFF set STA_BIRTH=" + "to_date('" + staBirth + "','yyyy-mm-dd') "
+ "where STA_ID=" +
"'" + staId + "'";
ires = conn.ExecNonQuery(sqlCommand);
sqlCommand = "update STAFF set STA_TELE=" + "'" + staTele + "'" + "where STA_ID=" +
"'" + staId + "'";
ires = conn.ExecNonQuery(sqlCommand);
sqlCommand = "update STAFF set STA_SIGN_DATE=" + "to_date('" + staSignDate + "','yyyy-mm-dd') " + "where STA_ID=" +
"'" + staId + "'";
ires = conn.ExecNonQuery(sqlCommand);
sqlCommand = "update STAFF set PO_TITLE=" + "'" + poTitle + "'" + "where STA_ID=" +
"'" + staId + "'";
ires = conn.ExecNonQuery(sqlCommand);
sqlCommand = "update STAFF set STA_ON_JOB=" + "'" + state + "'" + "where STA_ID=" +
"'" + staId + "'";
ires = conn.ExecNonQuery(sqlCommand);
Console.WriteLine("update successfully!");
return(true);
}
return(false);
}
//新建一条操作记录,并返回操作id
public string CreateManageRecord(string staff_id, int remark, string manage_id)
{
if (remark != 0 && remark != 1)
{
return(null);
}
string manage_time = DateTime.Now.ToShortDateString();
string sql2 = "insert into ManageRecord(mag_id, mag_time,mag_remark, sta_id) values('" +
manage_id + "'," + "to_date('" + manage_time + "','yyyy-mm-dd')" + ",'" + remark + "','" + staff_id + "')";
ctrl.ExecNonQuery(sql2);
return(manage_id);
}
//更改预定状态
public void change_reserve()
{
string sql = "update ReserveRecord set is_success=1 where rev_id='" + res_id + "'";
dbutil.ExecNonQuery(sql);
}
