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);
}
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);
}
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 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);
}
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);
}
/// <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 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));
}
/*
#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);
}
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));
}
public List <string> getJNListByName()
{
string sqlStr = string.Format(@"select distinct job_number from Attendance_record_final where name = '{0}' and trunc(finger_print_date,'MM')>= trunc(add_months(sysdate,-3),'MM')", _name);
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
List <String> jNList = new List <String>();
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
jNList.Add(dt.Rows[i]["job_number"].ToString());
}
return(jNList);
}
public static int getARNumByAMFlag_YearAndMonth(string Attendance_Machine_Flag, string Year_And_Month_Str)
{
string sqlStr = string.Format(@"SELECT 1
FROM Attendance_Record AR
WHERE SubStr(job_number,1,1) in ({0})
AND TRUNC(AR.Fingerprint_Date,'MM') = TO_DATE('{1}','YYYY-MM')
GROUP BY AR.JOB_NUMBER",
Attendance_Machine_Flag,
Year_And_Month_Str);
return(OracleDaoHelper.getDTBySql(sqlStr).Rows.Count);
}
/// <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));
}
/// <summary>
/// 是否存在制定日期的工作计划。
/// </summary>
/// <returns></returns>
public bool ifExistsWS()
{
bool result = false;
string sqlStr = String.Format(@"
select 1
from Work_Schedule
where trunc(work_and_rest_date,'MM') = To_DATE('{0}','yyyy-MM')
", V_Work_Schedule._YearAndMonthStr);
result = OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false;
return(result);
}
public static bool ifDayOfRestAutomaticAnalysis(string year_month_day)
{
string sqlStr = String.Format(@"
select 1
from Attendance_Record AR
where (AR.Fpt_First_Time IS NOT NULL OR AR.Fpt_Last_Time IS NOT NULL)
AND trunc(AR.fingerprint_date,'DD') = to_date( '{0}','yyyy-MM-dd')
having count(1) < 99", year_month_day);
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
return(dt.Rows.Count > 0 ? true : false);
}
public bool ifNotHaveRecordOfMorning()
{
string sqlStr = string.Format(@"SELECT 1 FROM Attendance_Record
WHERE NAME = '{0}'
AND TRUNC(FINGERPRINT_DATE,'DD')= TO_DATE('{1}','yyyy-MM-dd')
AND FPT_FIRST_TIME IS NULL",
this.Name,
this.Day);
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
return(dt.Rows.Count > 0 ? true : false);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static DataTable getRestDays(string yearAndMonth)
{
string 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')
order by update_Time desc,Rest_Day desc",
yearAndMonth);
return(OracleDaoHelper.getDTBySql(sqlStr));
}
public bool ifExistsAtRange()
{
string sqlStr = String.Format(@"
SELECT 1
FROM ASK_FOR_LEAVE A_F_L
WHERE A_F_L.name = '{0}'
AND TRUNC(TO_DATE('{1}','yyyy-MM-dd hh24:mi'),'DD') = TRUNC(Leave_start_time,'DD')
",
this._name,
this._startTime);
return(OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false);
}
/// <summary>
/// 获取员工的基本信息。
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public static List <V_AR_DETAIL> get_V_A_R_Summary_Base_Info_By_Specific_Day(string dateStr)
{
string sqlStr = string.Format(@"select distinct
dept,
job_number,
name
from Attendance_Record_Summary
where fingerprint_date = to_date('{0}','yyyy-MM-dd')
order by job_number asc", dateStr);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
return(ConvertHelper <V_AR_DETAIL> .ConvertToList(dt));
}
public static DataTable getAllVacationListByNameAndDate(string name)
{
string sqlStr = String.Format(@"select job_number as ""工号"",
name as ""姓名"",
to_char(leave_date,'yyyy-MM-dd') ""请假日期"",
to_char(leave_start_time,'yyyy-MM-dd HH24:MI') ""离开时间"",
to_char(leave_end_time,'yyyy-MM-dd HH24:MI') ""终止时间""
from Ask_For_Leave
where name = '{0}'
order by leave_date desc", name);
return(OracleDaoHelper.getDTBySql(sqlStr));
}
public static User_Info getUserInfo(string userName)
{
string sqlStr = String.Format(@"SELECT user_name,
password,
TO_CHAR(update_time,'yyyy/MM/dd') AS UPDATE_TIME,
department,
ACTION
FROM USER_INFO WHERE User_Name = '{0}'", userName
);
List <User_Info> userInfoList = ConvertHelper <User_Info> .ConvertToList(OracleDaoHelper.getDTBySql(sqlStr));
return(userInfoList[0]);
}
/*
* /// <summary>
* /// 统计有多少人，此月此考勤机。
* /// </summary>
* /// <param name="Year_And_Month_Str"></param>
* /// <param name="prefix_Job_Number">工号前缀</param>
* /// <returns></returns>
* public static int get_Total_Num_Of_Staffs_By_YAndM_And_AMFlag(string attendance_machine_flag, string Year_And_Month_Str )
* {
* string sqlStr = string.Format(@"
* SELECT COUNT(1)
* FROM
* (
* select AR.JOB_NUMBER
* from Attendance_Record AR
* where substr(job_number,1,1) in ({0})
* AND TRUNC(AR.Fingerprint_Date,'MM') = To_DATE('{1}','yyyy-MM')
* GROUP BY AR.JOB_NUMBER
* ) TEMP",
* attendance_machine_flag,
* Year_And_Month_Str
* );
* int result = 0;
* int.TryParse(OracleDaoHelper.getDTBySql(sqlStr).Rows[0][0].ToString(), out result);
* return result;
* }
*/
/// <summary>
/// 统计有多少人，此月此考勤机。
/// </summary>
/// <param name="Year_And_Month_Str"></param>
/// <param name="prefix_Job_Number">工号前缀</param>
/// <returns></returns>
public static int get_Total_Num_Of_Staffs_By_YAndM_And_AMFlag(string attendance_machine_flag, string Year_And_Month_Str)
{
string sqlStr = string.Format(@"select count(distinct(job_number))
from Attendance_Record_Final
where substr(job_number,1,1) in ({0})
and trunc(finger_print_date,'MM') = to_date('{1}','yyyy-MM')",
attendance_machine_flag,
Year_And_Month_Str
);
int result = 0;
int.TryParse(OracleDaoHelper.getDTBySql(sqlStr).Rows[0][0].ToString(), out result);
return(result);
}
public static DataTable getAllVacationListLastThreeMonths()
{
string sqlStr = String.Format(@"select job_number as ""工号"",
name as ""姓名"",
to_char(leave_date,'yyyy-MM-dd') ""请假日期"",
to_char(leave_start_time,'yyyy-MM-dd HH24:MI') as ""起始时间"",
to_char(leave_end_time,'yyyy-MM-dd HH24:MI') as ""终止时间""
from Ask_For_Leave
where trunc(leave_date,'MM') >= TRUNC(ADD_MONTHS(sysdate,-3),'MM')
order by NLSSORT(name,'NLS_SORT= SCHINESE_PINYIN_M') ASC,
leave_date asc");
return(OracleDaoHelper.getDTBySql(sqlStr));
}
public bool ifTheTimeLaterThanFirstTime()
{
string sqlStr = string.Format(@"select 1
from Attendance_Record
where name = '{0}'
and fingerprint_date = to_date('{1}','yyyy-MM-dd')
and to_date('{1} {2}','yyyy-MM-dd HH24:MI:SS') > FPT_FIRST_TIME",
this._name,
this._day,
this._time);
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
return(dt.Rows.Count > 0 ? true : false);
}
private void loadAskForLeaveData()
{
string sqlStr = string.Format(@"select job_number as ""工号"",
name as ""姓名"",
to_char(leave_date,'yyyy-MM-dd') ""请假日期"",
to_char(leave_start_time,'yyyy-MM-dd HH24:MI') ""起始时间"",
to_char(leave_end_time,'yyyy-MM-dd HH24:MI') ""终止时间""
from Ask_For_Leave
where name = '{0}'
order by leave_date asc", cbName.Text.Trim());
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
this.dgv.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv);
}
public static DataTable getMSG(string randomStr)
{
string sqlStr = String.Format(@"
select
prompt,
flag,
ipaddr,
operate_time,
subject,
random_str
from Message
WHERE random_Str = '{0}'", randomStr);
return(OracleDaoHelper.getDTBySql(sqlStr));
}
private void btnQuery_Click(object sender, EventArgs e)
{
//获取姓名。
string name = cbName.Text.Trim();
if (name.Length == 0)
{
return;
}
string sqlStr = string.Format(@"select start_date AS ""起始时间"",
end_date AS ""终止时间"",
tabulation_time AS ""制表时间"",
fingerprint_date AS ""按指纹日期"",
job_number AS ""工号"",
name AS ""姓名"",
dept AS ""部门"",
to_char(fpt_first_time,'HH24:MI') AS ""上班时间"",
to_char(fpt_last_time,'HH24:MI') AS ""下班时间"",
come_num AS ""出勤天数"",
not_finger_print AS ""未打卡时间"",
ask_for_leave_days AS ""请假天数"",
ask_for_leave_type AS ""请假类型"",
(case come_late_num
when 0 THEN N''
else cast(come_late_num as nchar)
end) AS ""迟到次数"",
( case leave_early_num
when 0 THEN N''
else cast(leave_early_num as nchar)
end) AS ""早退次数"",
delay_time AS ""延时时间"",
meal_subsidy AS ""餐补"",
random_str AS ""随即字符串"",
record_time AS ""记录时间"",
sheet_name AS ""工作表""
from attendance_record ar
where ar.name ='{0}'
and trunc(ar.fingerprint_date,'DD') between to_date('{1}','YYYY-MM-DD')
and to_date('{2}','YYYY-MM-DD')
order by ar.fingerprint_date desc",
name,
dtStartDate.Value.ToString("yyyy-MM-dd"),
dtEndDate.Value.ToString("yyyy-MM-dd"));
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
this.dgv.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv);
}
/*
* /// <summary>
* /// 获取 某月某考勤机的 部门，工号，姓名。
* /// </summary>
* /// <param name="attendance_machine_flag"></param>
* /// <param name="date"></param>
* /// <returns></returns>
* public static List<V_AR_DETAIL> get_V_AR_Detail_By_Attendance_Machine_Flag_And_Specific_Day(string attendance_machine_flag,string date)
* {
* /*string proceName = "PKG_AR_Detail.get_Staffs_BI_by_AMFlag_YMStr";
* OracleParameter param__attendance_machine_flag = new OracleParameter("v_attendance_machine_flag", OracleDbType.Varchar2, ParameterDirection.Input);
* OracleParameter param_date_str = new OracleParameter("v_year_and_month_str", OracleDbType.Varchar2, ParameterDirection.Input);
* OracleParameter param_cur_result = new OracleParameter("v_cur_result", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
* param__attendance_machine_flag.Value = attendance_machine_flag;
* param_date_str.Value = date;
* param_date_str.Size = 20;
* OracleParameter[] parameters = new OracleParameter[3] { param_cur_result, param__attendance_machine_flag, param_date_str };
* OracleHelper oH = OracleHelper.getBaseDao();
* DataTable dt = oH.getDT(proceName, parameters);
*/
/*
* string sqlStr = string.Format(@"select distinct dept,job_number,name
* from Attendance_Record
* where substr(job_number,1,1) in ({0})
* and trunc(fingerprint_date,'MM') = to_date('{1}','yyyy-MM')
* order by job_number asc",
* attendance_machine_flag,
* date);
* System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
* return ConvertHelper<V_AR_DETAIL>.ConvertToList(dt);
* }
*/
/// <summary>
/// 获取 某月某考勤机的 部门，工号，姓名。
/// </summary>
/// <param name="attendance_machine_flag"></param>
/// <param name="date"></param>
/// <returns></returns>
public static List <V_AR_DETAIL> get_V_AR_BaseInfo_By_Attendance_Machine_Flag_And_Specific_Month(string attendance_machine_flag, string year_month_str)
{
string sqlStr = string.Format(@"select
distinct dept,
job_number,
name
from Attendance_Record_Final
where substr(job_number,1,1) in ({0})
and trunc(finger_print_date,'MM') = to_date('{1}','yyyy-MM')
order by job_number asc",
attendance_machine_flag,
year_month_str);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
return(ConvertHelper <V_AR_DETAIL> .ConvertToList(dt));
}
public List <string> getDeptList()
{
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 = OracleDaoHelper.getDTBySql(sqlStr);
List <String> deptList = new List <string>();
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
deptList.Add(dt.Rows[i]["Dept"].ToString());
}
return(deptList);
}
public V_W_S get_V_W_S_By_Date_And_Dept()
{
V_W_S v_W_S = null;
string sqlStr = String.Format(@"SELECT DEPT,
TO_CHAR(Work_And_Rest_Date,'YYYY-MM-DD') AS Work_And_Rest_Date,
CAST(Work_Rate AS VARCHAR2(10)) AS Work_Rate,
Work_OR_REST,
Day_Of_Week
FROM V_W_S
WHERE Dept= '{0}'
AND Work_And_Rest_Date = TO_DATE('{1}','YYYY-MM-DD')", this.dept, this.work_and_rest_date);
v_W_S = ConvertHelper <V_W_S> .ConvertToList(OracleDaoHelper.getDTBySql(sqlStr))[0];
return(v_W_S);
}
