//查找当前会员已创建的未交易订单 void selectrev() { string sql = "select rev_id from ReserveRecord where mem_id='" + mem_id1.Text + "' and is_success=0"; DataSet set = dbutil.GetDataSet(sql); for (int i = 0; i < set.Tables[0].Rows.Count; i++) { res_id.Items.Add(Convert.ToString(set.Tables[0].Rows[i]["rev_id"])); } res_id.UpdateLayout(); }
///功能:查询员工信息 ///输入: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 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 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); }
//按月分组统计每一种类型的书籍的销售量 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 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 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); } }
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(); }
/// <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); }
//员工每月的月薪的计算 //迟到早退扣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(); } }