//某个员工在某年某月的销售额 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(); }
//员工每月的月薪的计算 //迟到早退扣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(); } }
//获取符合书籍描述的类型id, 若无对应类型,则新增一条元组 public string GetType_idByDescription(string description) { if (description == null || description == "") { return(null); } string sql = "select tp_id from Type where tp_name like '%" + description + "%'"; OracleDataReader reader = ctrl.ExecuteReader(sql); if (reader.Read()) { return(reader[0].ToString()); } else { return(InsertType(description)); } }