public static bool isPasswordRight(string userName, string password, string products_Name)
{
string sqlStr = String.Format(@"SELECT 1 FROM USER_INFO WHERE UPPER(User_Name) = UPPER('{0}')
AND Password = '******' AND Product_Name = '{2}'", userName, password, products_Name);
return(OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false);
}
public static void read(PictureBox pictureBox)
{
// strid 主键字段名 strzp 照片字段名 strtb 数据库表名 strpath 存放照片的本地路径
byte[] img = new byte[0];
string sqlStr = string.Format(@"SELECT id,picture FROM Picture_Test where id = 1");
OracleConnection conn = new OracleConnection(OracleDaoHelper.conn_str);
OracleDataReader rs = OracleDaoHelper.getDR(sqlStr, out conn);
while (rs.Read())
{
string idStr = rs["id"].ToString();
/*
* string idStr = rs["id"].ToString();
* //img = (byte[])(rs["zp"]);
* img = System.Text.Encoding.GetEncoding(-0).GetBytes(rs["zp"].ToString());
* System.IO.File.WriteAllBytes("C:\\" + idStr + ".jpg", img);
*/
if (rs["picture"] != DBNull.Value)
{ //照片字段里有值才能进到方法体显示图片,否则清空pb{
MemoryStream ms = new MemoryStream((byte[])rs["picture"]);
Image imageBlob = Image.FromStream(ms, true); //用流创建Image
pictureBox.Image = imageBlob;
pictureBox.Image.Save("E:\\1.jpg", pictureBox.Image.RawFormat);
}
else //照片字段里没值,清空pb
{
pictureBox.Image = null;
}
}
rs.Close();
rs.Dispose();
conn.Close();
conn.Dispose();
}
private void cbName_TextChanged(object sender, EventArgs e)
{
//获取姓名。
string name = cbName.Text.Trim();
if (name.Length == 0)
{
return;
}
if (name.Length > 1)
{
return;
}
string strFirstIndexOfName = name.Substring(0, 1);
string sqlStr = string.Format(@"select distinct ar.name as name
from attendance_record ar
where ar.name like '{0}%'
and trunc(ar.fingerprint_date,'MM') >= ADD_MONTHS(trunc(sysdate,'MM'),-2)",
strFirstIndexOfName);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
cbName.DataSource = null;
this.cbName.DataSource = dt;
cbName.DisplayMember = "name";
cbName.ValueMember = "name";
}
/// <summary>
/// 获取汉字相同的记录的组数。
/// </summary>
/// <returns></returns>
private int getAmountOfGroupOfSameName()
{
string sqlStr = string.Format(@"SELECT ""姓名""
FROM
(
select distinct
AR_Temp.Job_Number AS ""工号"",
AR_Temp.name AS ""姓名"",
AR_Temp.Attendance_Machine_Flag AS ""卡机编号"",
AR_Temp.Row_Index AS ""行号"",
AR_Temp.Record_Time AS ""记录时间""
from AR_Temp, (
select job_number,
name,
attendance_machine_flag,
row_index,
record_time
from AR_Temp
) AR_T
WHERE AR_Temp.name = AR_T.Name
AND(
(AR_Temp.Attendance_Machine_Flag = AR_T.attendance_machine_flag
AND AR_Temp.Job_Number != AR_T.job_number)
OR(
AR_Temp.Attendance_Machine_Flag != AR_T.attendance_machine_flag
)
)
) T
group by ""姓名""");
return(OracleDaoHelper.getDTBySql(sqlStr).Rows.Count);
}
private System.Data.DataTable getSameNameInfo()
{
string sqlStr = string.Format(@"select distinct
AR_Temp.Job_Number AS ""工号"",
AR_Temp.name AS ""姓名"",
AR_Temp.Attendance_Machine_Flag AS ""卡机编号"",
AR_Temp.Row_Index AS ""行号"",
AR_Temp.Record_Time AS ""记录时间""
from AR_Temp, (
select job_number,
name,
attendance_machine_flag,
row_index,
record_time
from AR_Temp
) AR_T
WHERE AR_Temp.name = AR_T.Name
AND(
(AR_Temp.Attendance_Machine_Flag = AR_T.attendance_machine_flag
AND AR_Temp.Job_Number != AR_T.job_number)
OR(
AR_Temp.Attendance_Machine_Flag != AR_T.attendance_machine_flag
)
)
order by NLSSORT(""姓名"", 'NLS_SORT= SCHINESE_PINYIN_M')");
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
this.dgv_same_name.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv_same_name);
return(dt);
}
/// <summary>
/// 按日期范围查询考勤记录。
/// </summary>
/// <param name="startDateStr"></param>
/// <param name="endDateStr"></param>
/// <returns></returns>
public System.Data.DataTable getARByRange(string startDateStr, string endDateStr)
{
String sqlStr = String.Format(@"SELECT
start_date AS ""起始日期"",
end_date AS ""终止日期"",
tabulation_time AS ""制表日期"",
fingerprint_date AS ""指纹日期"",
dept AS ""部门"",
JOB_NUMBER AS ""工号"",
name AS ""姓名"",
SUBSTR(TO_CHAR(fpt_first_time,'yyyy-MM-dd HH24:MI:SS'),12,5) AS ""上班时间点"",
SUBSTR(TO_CHAR(fpt_last_time,'yyyy-MM-dd HH24:MI:SS'),12,5) AS ""下班时间点"",
sheet_name As ""表格名称"",
reord_time AS ""记录时间"",
come_num AS ""出勤天数"",
not_finger_print AS ""未打卡"",
delay_time AS ""延点"",
come_late_num AS ""迟到次数"",
leave_early_num AS ""早退次数"",
dinner_subsidy AS ""餐补"",
random_str AS ""随机字符串""
FROM Attendance_Record
WHERE fingerprint_date between TO_DATE('{0}','yyyy-MM-dd') and TO_DATE('{1}','yyyy-MM-dd')
OrDER bY
JOB_NUMBER ASC,
fingerprint_date ASC
", startDateStr, endDateStr);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
return(dt);
}
private System.Data.DataTable getAR(string random_Str)
{
String sqlStr = String.Format(@"SELECT
start_date;
end_date;
tabulation_time;
fingerprint_date;
dept;
job_number;
name;
sheet_name;
fpt_first_time;
fpt_last_time;
seq;
reord_time;
not_finger_print;
delay_time;
come_late;
leave_early;
dinner_subsidy;
random_str
FROM Attendance_Record
WHERE Random_Str= '{0}'
OrDER bY
FINGERPRINT_DATE DESC;
Dept DESC;
JOB_NUMBER DESC
", random_Str);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
return(dt);
}
public bool updateTheRecord()
{
string sqlStr_update_FPT_First_Time = string.Format(@"UPDATE Attendance_Record
SET FPT_FIRST_TIME = to_date('{2}','yyyy-MM-dd HH24:MI:SS'),
FILL_UP_REMARK = FILL_UP_REMARK || ' '|| '{3}' || ' '|| to_char(sysdate,'yyyy-MM-dd HH24:MI:SS') ||';'
WHERE NAME = '{0}'
AND TRUNC(FINGERPRINT_DATE,'DD')= TO_DATE('{1}','yyyy-MM-dd')",
this._name,
this._day,
this._day + " " + this._time,
"已补上班卡");
string sqlStr_update_FPT_Last_Time = string.Format(@"UPDATE Attendance_Record
SET FPT_LAST_TIME = to_date('{2}','yyyy-MM-dd HH24:MI:SS'),
FILL_UP_REMARK = FILL_UP_REMARK || ' '|| '{3}'|| ' '|| to_char(sysdate,'yyyy-MM-dd HH24:MI:SS') ||';'
WHERE NAME = '{0}'
AND TRUNC(FINGERPRINT_DATE,'DD')= TO_DATE('{1}','yyyy-MM-dd')",
this._name,
this._day,
this._day + " " + this._time,
"已补下班卡");
if (!ifNotHaveRecordOfAfternoon() && !ifNotHaveRecordOfMorning())
{
System.Windows.Forms.MessageBox.Show(this._day + ": 有出勤记录，无需补卡！", "提示：", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return(false);
}
if (ifNotHaveRecordOfMorning() && !ifNotHaveRecordOfAfternoon())
{
if (!ifTheTimeEarlierThanLastTime())
{
System.Windows.Forms.MessageBox.Show("所补上班时间: " + this._day + " " + this._time + " 必须 <= " + new V_AR(this._name, this._day).getLastTime(), "提示：", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return(false);
}
OracleDaoHelper.executeSQL(sqlStr_update_FPT_First_Time);
return(true);
}
if (!ifNotHaveRecordOfMorning() && ifNotHaveRecordOfAfternoon())
{
if (!ifTheTimeLaterThanFirstTime())
{
System.Windows.Forms.MessageBox.Show("所补下班时间：" + this._day + " " + this._time + " 必须 >= " + new V_AR(this._name, this._day).getFirstTime(), "提示：", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return(false);
}
OracleDaoHelper.executeSQL(sqlStr_update_FPT_Last_Time);
return(true);
}
if (ifNotHaveRecordOfMorning() && ifNotHaveRecordOfAfternoon())
{
if (ifMorning())
{
OracleDaoHelper.executeSQL(sqlStr_update_FPT_First_Time);
return(true);
}
OracleDaoHelper.executeSQL(sqlStr_update_FPT_Last_Time);
return(true);
}
return(false);
}
private void FrmRestDay_justConfiguration_Load(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(_year_and_month))
{
return;
}
/*
* string procedureName = "PKG_Rest_Day.Generate_Rest_Day";
*
* if (oH == null)
* {
* oH = OracleHelper.getBaseDao();
* }
* OracleParameter param_Name = new OracleParameter("v_Name", OracleDbType.Varchar2, ParameterDirection.Input);
* OracleParameter param_YearAndMonth = new OracleParameter("v_yearAndMonth", OracleDbType.NVarchar2, ParameterDirection.Input);
* OracleParameter param_restDay_cur = new OracleParameter("v_cur", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
* OracleParameter[] parameters = new OracleParameter[3] { param_restDay_cur, param_Name, param_YearAndMonth };
* parameters[1].Value = "everybody";
* parameters[2].Value = _year_and_month;
* DataTable dt = oH.getDT(procedureName, parameters);
* this.dgv.DataSource = null;
* this.dgv.DataSource = null;
* this.dgv.DataSource = dt;
* DGVHelper.AutoSizeForDGV(dgv);
*/
//先获取本月最后一天
int lastDay = 0;
string sqlStr = string.Format(@"select to_char(last_day(to_date('{0}','yyyy-MM')),'dd') as last_day
from dual", _year_and_month);
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
lastDay = int.Parse(dt.Rows[0]["last_day"].ToString());
for (int i = 1; i <= lastDay; i++)
{
string year_and_month_str = _year_and_month + "-" + i.ToString();
sqlStr = string.Format(@"SELECT TO_Char(to_date('{0}','yyyy-MM-dd'),'day') the_day
FROM DUAL
", year_and_month_str);
dt = OracleDaoHelper.getDTBySql(sqlStr);
if ("sunday".Equals(dt.Rows[0]["the_day"].ToString().Trim()))
{
sqlStr = string.Format(@"INSERT INTO Rest_Day(name,rest_day,update_time)values('everybody',to_date('{0}','yyyy-MM-dd'),sysdate)",
year_and_month_str);
OracleDaoHelper.executeSQL(sqlStr);
}
}
//获取该月得所有休息日
sqlStr = string.Format(@"SELECT name as ""姓名"",
rest_day as ""休息日"",
update_time as ""更新时间""
FROM Rest_day
WHERE trunc(rest_day,'MM') = to_date('{0}','yyyy-MM')",
_year_and_month);
dt = OracleDaoHelper.getDTBySql(sqlStr);
this.dgv.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv);
}
/// <summary>
/// 判断 给定的日期字符串，在Rest_day中是否为休息日。
/// </summary>
/// <param name="year_month_day"></param>
/// <returns></returns>
public static bool ifDayOfRest(string year_month_day)
{
string sqlStr = string.Format(@"SELECT 1
FROM Rest_Day RD
WHERE rest_day = TO_DATE('{0}','yyyy-MM-dd')", year_month_day);
return(OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false);
}
private bool ifTheRestDay(string year_and_month_day_str)
{
string sqlStr = string.Format(@"SELECT 1
FROM REST_DAY
WHERE TRUNC(rest_day,'dd') = to_date('{0}','yyyy-MM-dd')", year_and_month_day_str);
return(OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false);
}
private bool ifConfigRestDay(string year_and_month_str)
{
string sqlStr = string.Format(@"SELECT 1
FROM Rest_Day
WHERE trunc(rest_day,'MM') = to_date('{0}','yyyy-MM')", year_and_month_str);
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
return(dt.Rows.Count > 0 ? true : false);
}
internal static void delByNameAndMonth(string name, string year_month_day_str)
{
string sqlStr = string.Format(@"DELETE
FROM ASK_FOR_LEAVE
WHERE Name = '{0}'
AND Leave_date = to_date('{1}','yyyy-MM-dd')", name, year_month_day_str);
OracleDaoHelper.executeSQL(sqlStr);
}
public static bool isPasswordRight(string userName, string password)
{
string sqlStr = String.Format(@"SELECT 1 FROM USER_INFO WHERE User_Name = '{0}'
AND Password = '******'", userName, password);
int rows_num = 0;
rows_num = OracleDaoHelper.getDTBySql(sqlStr).Rows.Count;
return(rows_num > 0 ? true : false);
}
/// <summary>
///
/// Attendance_Record_Detail
/// </summary>
/// <param name="attendanceMachineFlag"></param>
/// <param name="year_and_month_str"></param>
/// <returns></returns>
///
/// <summary>
/// 删除本月的考勤记录
/// </summary>
/// <param name="year_and_month_str"></param>
private void delARDetailInfoByYearAndMonth(string year_and_month_str)
{
string sqlStr = string.Format(@"delete
from Attendance_record_Detail
where trunc(finger_print_date,'MM') = to_date('{0}','yyyy-MM')",
year_and_month_str);
OracleDaoHelper.executeSQL(sqlStr);
}
private void btnSwitch_Click(object sender, EventArgs e)
{
if ("查看同名记录".Equals(btnSwitch.Text.Trim()))
{
btnSwitch.Text = "查看拼音相同的记录";
dgv_same_name.Visible = true;
dgv_same_pinyin_of_name.Visible = false;
string sqlStr = string.Format(@"select distinct
AR_Temp.Job_Number AS ""工号"",
AR_Temp.name AS ""姓名"",
AR_Temp.Attendance_Machine_Flag AS ""卡机编号"",
AR_Temp.Row_Index AS ""行号"",
AR_Temp.Record_Time AS ""记录时间""
from AR_Temp, (
select job_number,
name,
attendance_machine_flag,
row_index,
record_time
from AR_Temp
) AR_T
WHERE AR_Temp.name = AR_T.Name
AND(
(AR_Temp.Attendance_Machine_Flag = AR_T.attendance_machine_flag
AND AR_Temp.Job_Number != AR_T.job_number)
OR(
AR_Temp.Attendance_Machine_Flag != AR_T.attendance_machine_flag
)
)
order by NLSSORT(""姓名"", 'NLS_SORT= SCHINESE_PINYIN_M')");
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
this.dgv_same_name.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv_same_name);
}
else
{
btnSwitch.Text = "查看同名记录";
string sqlStr = string.Format(@"select distinct
AR_Temp.Job_Number AS ""工号"",
AR_Temp.name AS ""姓名"",
AR_Temp.Attendance_Machine_Flag AS ""卡机编号"",
AR_Temp.Row_Index AS ""行号"",
AR_Temp.Record_Time AS ""记录时间""
from AR_Temp, (
select distinct name
from AR_Temp
) AR_T
WHERE AR_Temp.name ! = AR_T.Name
AND gethzpy.GetHzFullPY(AR_Temp.name) = gethzpy.GetHzFullPY(AR_T.name)
order by NLSSORT(""姓名"", 'NLS_SORT= SCHINESE_PINYIN_M')");
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
this.dgv_same_pinyin_of_name.DataSource = dt;
dgv_same_pinyin_of_name.Visible = true;
DGVHelper.AutoSizeForDGV(dgv_same_pinyin_of_name);
dgv_same_name.Visible = false;
}
}
/// <summary>
/// 获取指定月份：为休息日的 dd部分。
/// </summary>
/// <param name="year_and_month_str"></param>
/// <returns></returns>
public static DataTable getDaysOfRestDay(string year_and_month_str)
{
string sqlStr = string.Format(@"SELECT TO_NUMBER(TO_CHAR(rest_day,'dd')) AS dd
FROM Rest_Day RD
WHERE trunc(rest_day,'MM') = to_date('{0}','yyyy-MM')",
year_and_month_str);
return(OracleDaoHelper.getDTBySql(sqlStr));
}
/// <summary>
/// 本月是否已经设定了加班日。
/// </summary>
/// <returns></returns>
public bool ifHaveTheDayOfOvertime()
{
string sqlStr = string.Format(@"select 1
from Rest_Day
where name = '{0}'
and trunc(rest_day,'MM') = to_date('{1}','yyyy-MM')",
_name, _the_day_of_overtime);
return(OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false);
}
/// <summary>
/// 删除某天的休息日。
/// </summary>
public void delRestDay()
{
string sqlStr = string.Format(@"DELETE REST_DAY
WHERE NAME = '{0}'
AND TRUNC(Rest_Day,'DD') = to_date('{1}','yyyy-MM-dd')",
_name,
_the_day_of_overtime);
OracleDaoHelper.executeSQL(sqlStr);
}
public static int getARNumByYearAndMonth(string Year_And_Month_Str)
{
string sqlStr = string.Format(@"SELECT 1
FROM Attendance_Record AR
WHERE TRUNC(AR.Fingerprint_Date,'MM') = TO_DATE('{0}','YYYY-MM')
GROUP BY AR.JOB_NUMBER",
Year_And_Month_Str);
return(OracleDaoHelper.getDTBySql(sqlStr).Rows.Count);
}
/// <summary>
/// 删除制定日期的工作计划。
/// </summary>
/// <returns></returns>
public int delWS()
{
string sqlStr = String.Format(@"
delete
from Work_Schedule
where trunc(work_and_rest_date,'MM') = To_DATE('{0}','yyyy-MM')",
V_Work_Schedule._YearAndMonthStr);
return(OracleDaoHelper.executeSQL(sqlStr));
}
public int getDeptNum()
{
string sqlStr = String.Format(@"select DISTINCT(Dept) Dept
from v_Work_Schedule
where trunc(work_and_rest_date,'MM') = To_DATE('{0}','yyyy-MM')
order by dept asc", V_Work_Schedule._YearAndMonthStr);
System.Data.DataTable dt = (System.Data.DataTable)(OracleDaoHelper.getDTBySql(sqlStr));
return(dt.Rows.Count);
}
public static void getPictureByProductName(string productName, PictureBox pictureBox)
{
Image imageBlob = null;
// strid 主键字段名 strzp 照片字段名 strtb 数据库表名 strpath 存放照片的本地路径
byte[] img = new byte[0];
string sqlStr = string.Format(@"SELECT picture FROM Products_Picture where product_name = '{0}'", productName);
OracleConnection conn = new OracleConnection(OracleDaoHelper.conn_str);
OracleDataReader rs = OracleDaoHelper.getDR(sqlStr, out conn);
if (!rs.HasRows)
{
pictureBox.Image = null;
rs.Close();
rs.Dispose();
conn.Close();
conn.Dispose();
return;
}
while (rs.Read())
{
/*
* string idStr = rs["id"].ToString();
* //img = (byte[])(rs["zp"]);
* img = System.Text.Encoding.GetEncoding(-0).GetBytes(rs["zp"].ToString());
* System.IO.File.WriteAllBytes("C:\\" + idStr + ".jpg", img);
*/
if (rs["picture"] != DBNull.Value)
{ //照片字段里有值才能进到方法体显示图片,否则清空pb{
MemoryStream ms = new MemoryStream((byte[])rs["picture"]);
imageBlob = Image.FromStream(ms, true); //用流创建Image
pictureBox.Image = imageBlob;
try
{
pictureBox.Image.Save(string.Format(@"{0}\{1}.jpg",
Application.StartupPath + "\\pictures",
StringHelper.removeBlank(productName)
),
pictureBox.Image.RawFormat);;
}
catch (Exception ex) {
MessageBox.Show(ex.ToString());
}
}
else
{//照片字段里没值,清空pb
pictureBox.Image = null;
}
}
rs.Close();
rs.Dispose();
conn.Close();
conn.Dispose();
}
/*
#region 获取该日期范围内有多少日的考勤记录。
* /// <summary>
* /// 获取该日期范围内有多少日的考勤记录
* /// </summary>
* /// <param name="Year_And_Month_Str"></param>
* /// <returns></returns>
* public static int get_AR_Days_Num(string Year_And_Month_Str)
* {
* string sqlStr = string.Format(@"
* SELECT COUNT(1)
* FROM
* (
* select AR.Fingerprint_Date
* from Attendance_Record AR
* where TRUNC(AR.Fingerprint_Date,'MM') = To_DATE('{0}','yyyy-MM')
* GROUP BY AR.Fingerprint_Date
* ) TEMP",
* Year_And_Month_Str);
* int result = 0;
* int.TryParse(OracleDaoHelper.getDTBySql(sqlStr).Rows[0][0].ToString(), out result);
* return result;
* }
#endregion
*/
#region 获取该日期范围内有多少日的考勤记录。
/// <summary>
/// 获取该日期范围内有多少日的考勤记录
/// </summary>
/// <param name="Year_And_Month_Str"></param>
/// <returns></returns>
public static int get_AR_Days_Num(string Year_And_Month_Str)
{
string sqlStr = string.Format(@"select count(distinct(ARFinal.finger_print_date))
from Attendance_Record_Final ARFinal
where TRUNC(ARFinal.finger_print_date,'MM') = To_DATE('{0}','yyyy-MM')",
Year_And_Month_Str);
int result = 0;
int.TryParse(OracleDaoHelper.getDTBySql(sqlStr).Rows[0][0].ToString(), out result);
return(result);
}
/// <summary>
/// 删除指定月份的 某个考勤机对应的纪录
/// </summary>
/// <param name="attendanceMachineFlag"></param>
/// <param name="year_and_month_str"></param>
private void delTheInfoOfTheSpecificeMachineAndYearAndMonth(int attendanceMachineFlag, string year_and_month_str)
{
string sqlStr = string.Format(@"delete
from Attendance_record
where substr(job_number,1,1) = '{0}'
and trunc(start_date,'MM') = to_date('{1}','yyyy-MM')",
attendanceMachineFlag,
year_and_month_str);
OracleDaoHelper.executeSQL(sqlStr);
}
public DataTable getARNameLastThreeMonth()
{
string sqlstr = string.Format(@"select distinct ar.name
from attendance_record ar
where ar.name like '{0}%'
and ar.fingerprint_date >= trunc(add_months(sysdate,-2),'MM')
ORDER BY NLSSORT(name, 'NLS_SORT = SCHINESE_PINYIN_M') asc"
, this._name);
return(OracleDaoHelper.getDTBySql(sqlstr));
}
/// <summary>
/// 获取下班时间
/// </summary>
/// <returns></returns>
public string getLastTime()
{
string sqlStr = string.Format(@"select to_char(fpt_last_time,'yyyy-MM-dd hh24:mi:ss')
from Attendance_Record
where name = '{0}'
and fingerprint_date = to_date('{1}','yyyy-MM-dd')",
this._name,
this._day);
return(OracleDaoHelper.getDTBySql(sqlStr).Rows[0][0].ToString());
}
public int getAskForLeaveDays()
{
string sqlStr = String.Format(@"select(leave_end_Time - leave_start_time) * 24 leave_Hours
from Ask_For_Leave
where Job_Number = '{0}'
and trunc(leave_start_time, 'DD') = to_date('{1}', 'yyyy-MM-dd')",
this.Job_number,
this.Year_month_day);
return(Int32.Parse(OracleDaoHelper.getDTBySql(sqlStr).Rows[0]["leave_Hours"].ToString()));
}
public static DataTable getAllRestDays()
{
string sqlStr = string.Format(@"select
Name AS ""姓名"",
Rest_Day AS ""休息日"",
UPDATE_Time AS ""更新日期""
from Rest_Day
order by ""更新日期"" desc,""休息日"" desc");
return(OracleDaoHelper.getDTBySql(sqlStr));
}
/// <summary>
/// 获取该考勤机在后台已经存在的信息.
/// </summary>
/// <param name="attendanceMachineFlag"></param>
/// <param name="year_and_month_str"></param>
/// <returns></returns>
private System.Data.DataTable getSubmitInfoOfTheSpecificeMachineAndYearAndMonth(int attendanceMachineFlag, string year_and_month_str)
{
string sqlStr = string.Format(@"Select count(distinct job_number) as nums_of_staffs,
max(to_char(record_time,'yyyy-MM-dd HH24:MI:SS')) as latest_record_time
from Attendance_Record
where substr(job_number,1,1) = '{0}'
and trunc(fingerprint_date,'MM') = to_date('{1}','yyyy-MM')",
attendanceMachineFlag,
year_and_month_str);
return(OracleDaoHelper.getDTBySql(sqlStr));
}
