//创建账户
//员工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 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 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 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();
}
//店长修改员工的职位
//需要传入员工的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();
}
}
