//某个员工在某年某月的销售额
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));
}
}
