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