public bool checkLogin(string sta_id, string password)
{
sta_id = sta_id.Trim();
password = password.Trim();
ControlAccess ctrl = new ControlAccess();
string sqlStr1 = "select count(sta_id) from STAFF where sta_id='" + sta_id + "'";
if (Convert.ToInt32(ctrl.ExecuteScalar(sqlStr1)) <= 0)
{
System.Windows.Forms.MessageBox.Show("没有这个账号，请重新输入！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
string sqlStr2 = string.Format("select count(*) from STAFF where sta_id='{0}'and sta_password='******' ", sta_id, password);
if (Convert.ToInt32(ctrl.ExecuteScalar(sqlStr2)) == 1)
{
string sql = string.Format("select po_title from STAFF where sta_id='{0}'", sta_id);
position = Convert.ToString(ctrl.ExecuteScalar(sql));
string sql1 = string.Format("select sta_name from STAFF where sta_id='{0}'", sta_id);
name = Convert.ToString(ctrl.ExecuteScalar(sql1));
return(true);
}
else
{
System.Windows.Forms.MessageBox.Show("密码不对，请重新输入密码！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
}
///功能：查询员工信息
///输入：1.员工id,用于存放员工信息的Staff对象；2.通过Staff sta=new Staff()创建；3.数据库连接ControlAccess对象
///输出：bool,查询到返回true,未查询到返回false
public bool InquireStaffInfo(string staId, Staff sta, ControlAccess con)
{
if (InquireStaffInfo(staId, con) == false)
{
return(false);
}
else
{
string sqlcommand = "select STA_ID,STA_NAME,STA_GENDER,STA_ADDRESS," +
"STA_BIRTH,STA_TELE,STA_SIGN_DATE,PO_TITLE,STA_PASSWORD,STA_ON_JOB from STAFF where STA_ID="
+ "'" + staId + "'";
DataSet dataset = con.GetDataSet(sqlcommand);
sta.Sta_id = Convert.ToString(dataset.Tables[0].Rows[0][0]);
sta.Sta_name = Convert.ToString(dataset.Tables[0].Rows[0][1]);
sta.Sta_gender = Convert.ToString(dataset.Tables[0].Rows[0][2]);
sta.Sta_address = Convert.ToString(dataset.Tables[0].Rows[0][3]);
sta.Sta_birth = Convert.ToString(dataset.Tables[0].Rows[0][4]);
sta.Sta_tele = Convert.ToString(dataset.Tables[0].Rows[0][5]);
sta.Sta_sign_date = Convert.ToString(dataset.Tables[0].Rows[0][6]);
sta.Po_title = Convert.ToString(dataset.Tables[0].Rows[0][7]);
sta.Sta_password = Convert.ToString(dataset.Tables[0].Rows[0][8]);
sta.Sta_on_job = Convert.ToString(dataset.Tables[0].Rows[0][9]);
return(true);
}
}
//某个员工在某年某月的销售额
public float permonthsell(string sta_id, string year, string month)
{
float monthSell = 0;
int year1 = Convert.ToInt32(year);
int month1 = Convert.ToInt32(month);
string sql = string.Format("select tra_money,tra_time from TRADERECORD where sta_id='{0}' ", sta_id);
ControlAccess ctrl = new ControlAccess();
OracleDataReader odr = ctrl.ExecuteReader(sql);
if (odr.HasRows)
{
while (odr.Read())
{
float amount = Convert.ToInt32(odr[0]);
DateTime time = Convert.ToDateTime(odr[1]);
if (time.Year == year1 && time.Month == month1)
{
monthSell = monthSell + amount;
}
}
}
return(monthSell);
ctrl.Close();
}
//每月员工上班状态
//需要输入年月
public DataSet monthstatics(string ms_year, string ms_month)
{
salaryPerMonth(ms_year, ms_month);
ControlAccess ctrl = new ControlAccess();
string str = string.Format("select sta_id,ms_attend,ms_absent,ms_lea_early,ms_late,ms_leave,ms_salary from MONTHSTATISTICS where ms_year='{0}' and ms_month='{1}'order by sta_id", ms_year, ms_month);
return(ctrl.GetDataSet(str));
}
public String findName(string sta_id)
{
ControlAccess ctrl = new ControlAccess();
string str = String.Format("select sta_name from STAFF where sta_id='{0]'", sta_id);
string sta_name = Convert.ToString(ctrl.ExecuteScalar(str));
return(sta_name);
}
private void Grid_Loaded(object sender, RoutedEventArgs e)
{
dbutil = new ControlAccess();
newrecord(); //创建交易窗口
tra_id1.Text = traderecord.trade_number;
newreserve(); //创建预订窗口
rev_id1.Text = reserverecord.reserve_number;
}
public reserve_record()
{
reserve_number = null;
books = new List <book_sell>();
mem_id = null;
time = new System.DateTime();
dbutil = new ControlAccess();
}
public book_sell(string mbook_id)
{
book_id = mbook_id;
sell_amount = 0;
left_amount = 0;
res_amount = 0;
money = 0;
dbutil = new ControlAccess();
}
///功能：删除员工
///输入：员工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);
}
public DataSet InquireMonthStatistics2(string staId, ControlAccess con)
{
if (InquireMonthStatistics(staId, con) == true)
{
string sqlcommand = "select STA_ID,MS_YEAR,MS_MONTH,MS_ATTEND,MS_ABSENT,MS_ABSENT,MS_LEA_EARLY,MS_LATE,MS_SALARY" +
" from MONTHSTATISTICS where STA_ID=" + "'" + staId + "'";
DataSet dataset = con.GetDataSet(sqlcommand);
//拉到dataGrid1中
return(dataset);
}
return(null);
}
/// <summary>
/// 查询请假信息
/// </summary>
/// <param name="staId"></param>
/// <param name="lrStartTime"></param>
/// <param name="lrEndTime"></param>
/// <param name="lrDays"></param>
/// <param name="iNumOfData"></param>
/// <returns></returns>
public DataSet InquireLeave2(string staId, ControlAccess con)
{
if (InquireLeave(staId, con) == true)
{
string sqlcommand = "select LR_START_TIME,STA_ID,LR_END_TIME,LR_DAYS" +
" from LEAVERECORD where STA_ID=" + "'" + staId + "'";
DataSet dataset = con.GetDataSet(sqlcommand);
return(dataset);
}
return(null);
}
//创建账户
//员工ID需要在数据库中设置主键为自动生成
//需要用到的参数是员工的姓名，等级，地址，出生日期，电话号码，职位，密码，
public bool createUser(string sta_name,
string sta_gender,
string sta_address,
string sta_birth,
string sta_tele,
string po_title,
string sta_password)
{
ControlAccess ctrl = new ControlAccess();
System.DateTime currentTime = new System.DateTime();
string strYMD = currentTime.ToString("d"); //生成入职时间
string strmax = "select max(sta_id) from STAFF";
int a = 1;
int number = Convert.ToInt32(ctrl.ExecuteScalar(strmax));
msta_id = Convert.ToString(number + 1); //生成员工编号
Staff sta = new Staff();
sta.Sta_id = msta_id;
sta.Sta_name = sta_name;
sta.Sta_gender = sta_gender;
sta.Sta_address = sta_address;
sta.Sta_birth = sta_birth;
sta.Sta_tele = sta_tele;
sta.Po_title = po_title;
sta.Sta_password = sta_password;
sta.Sta_sign_date = strYMD;
try
{
string sqlstr = "insert into STAFF values('" + sta.Sta_id + "'" + "," + "'" + sta.Sta_name + "'" + "," + "'" + sta.Sta_gender + "'" + "," + "'" + sta.Sta_address + "'" + "," + "to_date('" + sta.Sta_birth + "'" + "," + "'" + "yyyy-mm-dd" + "'" + ")" + "," + "'" + sta.Sta_tele + "'" + "," + "to_date('" + sta.Sta_birth + "'" + "," + "'" + "yyyy-mm-dd" + "'" + ")" + "," + "'" + sta.Po_title + "'" + "," + "'" + sta.Sta_password + "'" + "," + "'" + "yes" + "'" + ")";
Console.WriteLine(sqlstr);
ctrl.ExecuteScalar(sqlstr);
}
catch (Exception ex)
{
ctrl.Close();
return(false);
}
finally
{
ctrl.Close();
}
return(true);
}
//按月分组统计每一种类型的书籍的销售量
public DataSet tpyeSell()
{
// string time=ms_year+"/"+ms_month;
// DateTime timeend =Convert.ToDateTime(time).AddMonths(1).AddDays(-1);
// DateTime timebegin= Convert.ToDateTime(time);
string sql = "select tp_name ID,sum(sel_amount) Amount,to_char(tra_time,'yyyy-mm') as Month from TRADERECORD natural join SELL natural join BOOK natural join TYPE group by tp_name,to_char(tra_time,'yyyy-mm') order by sum(sel_amount)";
ControlAccess ctrl = new ControlAccess();
DataSet set = ctrl.GetDataSet(sql);
return(set);
ctrl.Close();
}
///内部调用函数
public bool InquireStaffInfo(string staId, ControlAccess con)
{
string sqlcommand = "select count(*) from STAFF where STA_ID=" + "'" + staId + "'";
DataSet dateset = con.GetDataSet(sqlcommand);
int iCount = Convert.ToInt32(dateset.Tables[0].Rows[0][0]);
if (iCount > 0)
{
return(true);
}
else
{
return(false);
}
}
public trade_record()
{
trade_number = null;
books = new List <book_sell>();
mem_id = null;
trade_way = null;
res_id = null;
money = 0;
discount = 1;
sta_id = null;
time = new System.DateTime();
dbutil = new ControlAccess();
}
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);
}
public bool InquireMonthStatistics(string staId, ControlAccess con)
{
string sqlcommand = "select count(*) from MONTHSTATISTICS where STA_ID=" + "'" + staId + "'";
DataSet dateset = con.GetDataSet(sqlcommand);
int iCount = Convert.ToInt32(dateset.Tables[0].Rows[0][0]);
Console.WriteLine(iCount);
if (iCount > 0)
{
return(true);
}
else
{
return(false);
}
}
///内部接口
public bool InquireLeave(string staId, string lrStartTime, ControlAccess con)
{
string sqlcommand = "select count(*) from LEAVERECORD where STA_ID=" + "'" + staId + "' and LR_START_TIME=" +
"to_date('" + lrStartTime + "','yyyy-mm-dd') ";
DataSet dateset = con.GetDataSet(sqlcommand);
int iCount = Convert.ToInt32(dateset.Tables[0].Rows[0][0]);
if (iCount > 0)
{
return(true);
}
else
{
return(false);
}
}
public DataSet monthsell()
{
string sql = "select sta_id as ID,sum(tra_money) Amount,to_char(tra_time,'yyyy-mm') as Month from TRADERECORD group by sta_id,to_char(tra_time,'yyyy-mm')";
ControlAccess ctrl = new ControlAccess();
DataSet set = ctrl.GetDataSet(sql);
// set.Tables[0].Columns.Add("sta_name", typeof(System.String));
// foreach (DataRow row in set.Tables[0].Rows)
// {
// row[3] = findName(Convert.ToString(row[0]));
// }
return(set);
ctrl.Close();
}
/// 内部接口
public bool InquireAttendance(string staId, string arDate, ControlAccess con)
{
string sqlcommand = "select count(*) from ATTENDANCERECORD where STA_ID=" + "'" + staId + "' and AR_DATE=" +
"to_date('" + arDate + "','yyyy-mm-dd') ";
DataSet dateset = con.GetDataSet(sqlcommand);
int iCount = Convert.ToInt32(dateset.Tables[0].Rows[0][0]);
if (iCount > 0)
{
return(true);
}
else
{
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="iNumOfData"></param>数据条数
/// <param name="con"></param>数据库连接ControlAccess对象
/// <returns></returns>若查询到返回true,否则返回false
public DataSet InquireAttendance2(string staId, string arDate, ControlAccess con)
{
if (InquireAttendance(staId, arDate, con) == true)
{
string sqlcommand = "select STA_ID,AR_DATE,IS_LATE,IS_LEV_EARLY,IS_ABSENT" +
" from ATTENDANCERECORD where STA_ID="
+ "'" + staId + "' and AR_DATE=" + "to_date('" + arDate + "','yyyy-mm-dd') ";
DataSet dataset = con.GetDataSet(sqlcommand);
//ar.StaId1 = Convert.ToString(dataset.Tables[0].Rows[0][0]);
//ar.ArDate1 = Convert.ToString(dataset.Tables[0].Rows[0][1]);
//ar.IsLate1 = Convert.ToChar(dataset.Tables[0].Rows[0][2]);
//ar.IsLevEarly1 = Convert.ToChar(dataset.Tables[0].Rows[0][3]);
//ar.IsAbsent1 = Convert.ToChar(dataset.Tables[0].Rows[0][4]);
return(dataset);
}
return(null);
}
//店长修改员工的职位
//需要传入员工的ID和被修改为 的职位
public bool changePower(string sta_id, string po_title)
{
ControlAccess ctrl = new ControlAccess();
try
{
string sqlstr = string.Format("update STAFF set po_title='{0}' where sta_id='{1}'", po_title, sta_id);
// ctrl.Open();
ctrl.ExecuteScalar(sqlstr);
}
catch (Exception ex)
{
return(false);
}
finally
{
ctrl.Close();
}
return(true);
}
//员工每月的月薪的计算
//迟到早退扣20，缺勤扣100,请假扣80
//需要输入员工的ID，年，月
//结果返回int型的salary
public void salaryPerMonth(string ms_year, string ms_month)
{
int ms_year1 = Convert.ToInt32(ms_year);
int ms_month1 = Convert.ToInt32(ms_month);
int salary = 0;
ControlAccess ctrl = new ControlAccess();
try
{
//System.DateTime currentTime = new System.DateTime();
// string strYMD = currentTime.ToString("d");
string sqlid = "select sta_id from staff where sta_on_job='yes'";
OracleDataReader odr = ctrl.ExecuteReader(sqlid);
if (odr.HasRows)
{
while (odr.Read())
{
string sta_id = Convert.ToString(odr[0]);
string sqlsal = string.Format("select po_salary from POSITION natural join STAFF where STAFF.sta_id='{0}'", sta_id);
string sqlday = string.Format("select ms_absent,ms_lea_early,ms_late,ms_leave from MONTHSTATISTICS where sta_id='{0}' and ms_year='{1}' and ms_month='{2}'", sta_id, ms_year1, ms_month1);
DataSet set = ctrl.GetDataSet(sqlday);
salary = Convert.ToInt32(ctrl.ExecuteScalar(sqlsal)) - Convert.ToInt32(set.Tables[0].Rows[0][0]) * 100 - (Convert.ToInt32(set.Tables[0].Rows[0][1]) + Convert.ToInt32(set.Tables[0].Rows[0][2])) * 20 - Convert.ToInt32(set.Tables[0].Rows[0][3]) * 80;
string sqlupdate = string.Format("update MONTHSTATISTICS set ms_salary='{0}'", salary);
ctrl.ExecuteScalar(sqlupdate);
}
}
}
catch (Exception ex)
{
}
finally
{
ctrl.Close();
}
}
/// <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);
}
/// <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);
}
