//查找当前会员已创建的未交易订单
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();
}
}
