private void btnGetSalaryRecordByEmployee_Click(object sender, EventArgs e)
{
DateTime date = DateTime.Parse(txtStartDate.Text);
string sql = @"select distinct a.employeesalaryrecordid,
a.employeename,
a.salaryyear,
a.salarymonth,
a.actuallypay,
e.employeecname as 结算人,
a.createdate,
a.createuserid,
a.ownerid,
a.ownerpostid,
a.ownerdepartmentid,
a.ownercompanyid,
a.*
from smthrm.t_hr_employeesalaryrecord a
inner join smthrm.t_hr_employeesalaryrecorditem b
on a.employeesalaryrecordid = b.employeesalaryrecordid
inner join smthrm.t_hr_employee e
on a.createuserid = e.employeeid
where a.createuserid='" + txtGenerEmployeeid.Text + @"'
and a.salaryyear = " + date.Year + @"
and a.salarymonth = " + date.Month + @"
order by a.employeename,a.createdate desc";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dtSalaryRecord.DataSource = dt;
OracleHelp.close();
}
private void btnCompanyAttend_Click(object sender, EventArgs e)
{
GlobalParameters.employeeMasterCompanyid = txtCompanyid.Text;
GlobalParameters.employeeMasterCompanyid = txtCompanyid.Text;
GlobalParameters.StartDate = txtStartDate.Text;
GlobalParameters.EndDate = txtEndDate.Text;
string sql = @"select t.cname, b.employeename,b.employeeid,count(b.attendancedate)
from smthrm.t_hr_company t
inner join smthrm.t_hr_attendancerecord b
on t.companyid = b.ownercompanyid
where t.companyid='" + GlobalParameters.employeeMasterCompanyid + @"'
and b.attendancedate >= to_date('" + GlobalParameters.StartDate + @"', 'yyyy-mm-dd')
and b.attendancedate <= to_date('" + GlobalParameters.EndDate + @"', 'yyyy-mm-dd')
group by t.cname, b.employeename,b.employeeid
order by t.cname, b.employeename";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
if (dt != null)
{
dataGridEmployees.DataSource = dt;
OracleHelp.close();
txtMessagebox.Text += System.Environment.NewLine + "查询完成，共：" + dt.Rows.Count.ToString() + "条数据";
}
else
{
txtMessagebox.Text += System.Environment.NewLine + "查询完成，共：0条数据";
}
}
private void btnGetEmployeeid_Click(object sender, EventArgs e)
{
string sql = @"select e.employeeename,
e.employeecname,
case when ep.isagency='0' then '主岗位' else '兼职岗位' end 岗位类型,
--ep.isagency 岗位类型 ,--0主岗位 1兼职岗位
case when ep.editstate='0' then '已失效' else '生效中' end 生效状态,
--ep.editstate 岗位生效状态,--0失效，1生效,
ep.checkstate 岗位审核状态,--0未提交，1审核中，2审核通过，3审核不通过
pd.postname,
dp.departmentname,
c.cname,
case
when e.employeestate = '0' then '试用'
when e.employeestate = '1' then '在职'
when e.employeestate = '2' then '已离职'
when e.employeestate = '3' then '离职中'
end 员工状态,-- 0使用，1在职，2已离职，3离职中
ep.postlevel 岗位级别,
e.fingerprintid 指纹编码,
ep.employeepostid,
e.employeeid,
ep.postid,
d.departmentid,
c.companyid,
et.entrydate 入职日期,
et.checkstate 入职业审核状态,
e.idnumber
from smthrm.t_hr_employee e
left join smthrm.t_hr_employeepost ep
on e.employeeid = ep.employeeid
left join smthrm.t_hr_employeeentry et
on e.employeeid=et.employeeid
left join smthrm.t_hr_post p
on ep.postid = p.postid
left join smthrm.t_hr_postdictionary pd
on p.postdictionaryid = pd.postdictionaryid
left join smthrm.t_hr_company c
on p.companyid = c.companyid
left join smthrm.t_hr_department d
on p.departmentid = d.departmentid
left join smthrm.t_hr_departmentdictionary dp
on d.departmentdictionaryid = dp.departmentdictionaryid
where
e.employeecname like '%" + txtEmployeeName.Text + @"%'
--and ep.editstate=1
--and ep.checkstate=2
order by ep.isagency,ep.editstate desc ";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dataGridEmployees.DataSource = dt;
OracleHelp.close();
txtMessagebox.Text = "获取员工信息完成！";
}
private void dataGridEmployees_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
DataGridViewColumn column = dataGridEmployees.Columns[e.ColumnIndex];
if (column is DataGridViewButtonColumn)
{
if (column.Name == "ColumnDealPost")
{
//这里可以编写你需要的任意关于按钮事件的操作~
string isAgencePost = "0";
string isActivedPost = "0";
string strEmployeePostid = dataGridEmployees.Rows[e.RowIndex].Cells["employeepostid"].EditedFormattedValue.ToString();;
string strIsAgence = dataGridEmployees.Rows[e.RowIndex].Cells["ColumnIsAgence"].EditedFormattedValue.ToString();
string strIsActived = dataGridEmployees.Rows[e.RowIndex].Cells["ColumnIsActived"].EditedFormattedValue.ToString();
if (strIsAgence == "True")
{
isAgencePost = "1";
}
if (strIsActived == "True")
{
isActivedPost = "1";
}
UpdatePost(isAgencePost, isActivedPost, strEmployeePostid);
}
if (column.Name == "ColumnUpEmplooState")
{
}
}
}
for (int i = 0; i < dataGridEmployees.RowCount; i++)
{
string selectValue = dataGridEmployees.Rows[i].Cells["ColumnSelect"].EditedFormattedValue.ToString();
if (selectValue == "True")
{
Txtid.Text = dataGridEmployees.Rows[i].Cells["employeeid"].EditedFormattedValue.ToString();
txtCompanyid.Text = dataGridEmployees.Rows[i].Cells["companyid"].EditedFormattedValue.ToString();
txtEmployeeName.Text = dataGridEmployees.Rows[i].Cells["employeecname"].EditedFormattedValue.ToString();
GlobalParameters.employeeid = Txtid.Text;
GlobalParameters.employeeName = txtEmployeeName.Text;
GlobalParameters.employeeMasterPostid = dataGridEmployees.Rows[i].Cells["postid"].EditedFormattedValue.ToString();
GlobalParameters.employeeMasterDepartmentid = dataGridEmployees.Rows[i].Cells["departmentid"].EditedFormattedValue.ToString();
GlobalParameters.employeeMasterCompanyid = dataGridEmployees.Rows[i].Cells["companyid"].EditedFormattedValue.ToString();
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(GetEmployeeEntry(Txtid.Text));
dtEmployeeEntry.DataSource = dt;
OracleHelp.close();
txtMessagebox.Text = txtMessagebox.Text + System.Environment.NewLine + "获取员工入职信息完成！";
}
}
}
private void GetEmployeeBlance()
{
int year = int.Parse(GlobalParameters.StartDate.Substring(0, 4));
int month = int.Parse(GlobalParameters.StartDate.Substring(5, 2));
string sql = @"select
t.EMPLOYEENAME
,t.BALANCEYEAR
,t.BALANCEMONTH
,t.BALANCEDATE 结算日期
,t.WORKTIMEPERDAY 每日工作时长
,t.NEEDATTENDDAYS 应出勤天数
,t.REALNEEDATTENDDAYS 实际出勤天数
,t.LATEDAYS 迟到天数
,t.LEAVEEARLYDAYS 早退天数
,t.ABSENTDAYS 旷工天数
,t.AFFAIRLEAVEDAYS 事假天数
,t.LATETIMES 迟到次数
,t.LATEMINUTES 迟到分钟数
,t.leaveearlytimes 早退次数
,t.FORGETCARDTIMES 漏打卡次数
,t.ABSENTDAYS 旷工天数
,t.ABSENTMINUTES 旷工分钟数
,t.AFFAIRLEAVEDAYS 事假天数
,t.SICKLEAVEDAYS 病假天数
,t.OTHERLEAVEDAYS 其他假期天数
,t.ANNUALLEVELDAYS 年休假天数
,t.LEAVEUSEDDAYS 调休假天数
,t.MARRYDAYS 婚假天数
,t.MATERNITYLEAVEDAYS 产假天数
,t.NURSESDAYS 看护假天数
,t.FUNERALLEAVEDAYS 丧假天数
,t.TRIPDAYS 路程假天数
,t.INJURYLEAVEDAYS 工伤假天数
,t.PRENATALCARELEAVEDAYS 产前检查假天数
,t.EVECTIONTIME 出差时长
,t.CHECKSTATE 审核状态
from smthrm.T_HR_ATTENDMONTHLYBALANCE t
where t.employeename='" + GlobalParameters.employeeName + @"'
and t.balanceyear=" + year + @"
and t.balancemonth=" + month;
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
if (dt != null)
{
dtSalaryRecord.DataSource = dt;
OracleHelp.close();
txtMessagebox.Text += System.Environment.NewLine + "查询完成，共：" + dt.Rows.Count.ToString() + "条数据";
}
else
{
txtMessagebox.Text += System.Environment.NewLine + "查询完成，共：0条数据";
}
}
private void startClearAndInitAttendRecord()
{
DateTime dtstart = DateTime.Parse(txtStartDate.Text);
DateTime dtEnd = DateTime.Parse(txtEndDate.Text);
DialogResult MsgBoxResult;//设置对话框的返回值
MsgBoxResult = MessageBox.Show("开始处理：" + dtstart.ToShortDateString() + "-" + dtEnd.ToShortDateString()
+ "的异常及考勤，确认继续？", //对话框的显示内容
"提示", //对话框的标题
MessageBoxButtons.YesNo, //定义对话框的按钮，这里定义了YSE和NO两个按钮
MessageBoxIcon.Exclamation, //定义对话框内的图表式样，这里是一个黄色三角型内加一个感叹号
MessageBoxDefaultButton.Button2); //定义对话框的按钮式样
if (MsgBoxResult == DialogResult.Yes) //如果对话框的返回值是YES（按"Y"按钮）
{
ShowMessageAsny("*********************************开始处理指定公司员工的考勤异常及初始化考勤记录");
int j = 0;
OracleHelp.Connect();
for (int i = 0; i < dataGridEmployees.RowCount; i++)
{
try
{
GlobalParameters.employeeName = dataGridEmployees.Rows[i].Cells["employeename"].EditedFormattedValue.ToString();
GlobalParameters.employeeid = dataGridEmployees.Rows[i].Cells["employeeid"].EditedFormattedValue.ToString();
string sql = CleareEployeesignindetail1()
+ ClearePersonalrecord()
+ Clearet_wf_dotask3()
+ CleareEmployeesigninrecord4()
+ CleareEmployeeabnormrecord5()
+ CleareEmployeeabnormrecord6();
ShowMessageAsny("*******开始处理：" + GlobalParameters.employeeName + " 清理考勤异常及初始化考勤记录");
j = OracleHelp.Excute(CleareEployeesignindetail1());
ShowMessageAsny("第一步完成，处理了：" + j + "条数据！");
j = OracleHelp.Excute(ClearePersonalrecord());
ShowMessageAsny("第二步完成，处理了：" + j + "条数据！");
j = OracleHelp.Excute(Clearet_wf_dotask3());
ShowMessageAsny("第三步完成，处理了：" + j + "条数据！");
j = OracleHelp.Excute(CleareEmployeesigninrecord4());
ShowMessageAsny("第四步完成，处理了：" + j + "条数据！");
j = OracleHelp.Excute(CleareEmployeeabnormrecord5());
ShowMessageAsny("第五步完成，处理了：" + j + "条数据！");
j = OracleHelp.Excute(CleareEmployeeabnormrecord6());
ShowMessageAsny("第六步完成，处理了：" + j + "条数据！");
ShowMessageAsny("*******处理完成：" + GlobalParameters.employeeName + " 清理考勤异常及初始化考勤记录");
}
catch (Exception ex)
{
ShowMessageAsny("处理异常：" + GlobalParameters.employeeName + " 异常信息：" + ex.ToString());
}
}
OracleHelp.close();
ShowMessageAsny("*********************************已处理完成所有指定公司员工的考勤异常及初始化考勤记录");
}
}
private void btnGetSalaryItem_Click(object sender, EventArgs e)
{
string sql = @"select * from smthrm.T_HR_SALARYITEM t
where t.OWNERCOMPANYID = '" + paycompanyId + "'";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dtSalaryAchive.DataSource = dt;
OracleHelp.close();
}
private void btnGetTravalReme_Click(object sender, EventArgs e)
{
string sql = @"select t.COMPUTINGTIME,t.* from smtoa.T_OA_TRAVELREIMBURSEMENT t
where t.nobudgetclaims='" + txtTravNomber.Text + "'";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dtTravSolution.DataSource = dt;
OracleHelp.close();
}
private void btnGetEmployeeid_Click(object sender, EventArgs e)
{
string sql = @"select t.employeeid,t.employeecname from smthrm.t_hr_employee t
where t.employeecname='" + txtEmployeeName.Text + "'";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dataGridEmployees.DataSource = dt;
OracleHelp.close();
Txtid.Text = dt.Rows[0][0].ToString();
}
private void btnGetPentionMaster_Click(object sender, EventArgs e)
{
DateTime date = DateTime.Parse(txtStartDate.Text);
string sql = @"select * from smthrm.t_hr_pensiondetail t
where t.employeeid='" + GlobalParameters.employeeid + @"'
and t.pensionyear=" + date.Year + @"
and t.pensionmoth=" + date.Month + @"";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dtPentionRecord.DataSource = dt;
OracleHelp.close();
}
private void btnSelectPention_Click(object sender, EventArgs e)
{
string sql = @"select s.SOCIALSERVICEYEAR 社保缴纳起始时间
,e.idnumber 身份证号, s.cardid 社保卡号,s.computerno 电脑号,s.startdate,s.checkstate,
s.createdate,s.updatedate from smthrm.t_hr_employee e
inner join smthrm.t_hr_pensionmaster s on e.employeeid=s.employeeid
where e.employeeid='" + GlobalParameters.employeeid + @"'
order by s.updatedate
";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dtPention.DataSource = dt;
OracleHelp.close();
txtMessagebox.Text = "获取员工社保信息完成！";
}
private void GetCompanyDoc()
{
string sql = @"select t.senddocid,t.senddoctitle,t.*
from smtoa.t_oa_senddoc t
where t.senddoctitle like '%" + txtDocName.Text + "%'";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
if (dt.Rows.Count > 1)
{
MessageBox.Show("存在多条记录，请检查查询条件");
return;
}
TxtSendDocid.Text = dt.Rows[0]["senddocid"].ToString();
txtDocName.Text = dt.Rows[0]["senddoctitle"].ToString();
}
string sqldoc = @"select c.cname,c.companyid,d.*
from smtoa.T_OA_DISTRIBUTEUSER d
inner join smthrm.t_hr_company c on d.viewer=c.companyid
where d.formid='" + TxtSendDocid.Text + "'";
DataTable dtdoc = OracleHelp.getTable(sqldoc);
if (dtdoc.Rows.Count > 0)
{
dataGridSendDoc.DataSource = dtdoc;
}
else
{
MessageBox.Show("没找到公文发布范围。");
}
OracleHelp.close();
txtMessagebox.Text += System.Environment.NewLine + "查询完成，共：" + dt.Rows.Count.ToString() + "条数据";
}
else
{
txtMessagebox.Text += System.Environment.NewLine + "查询完成，共：0条数据";
}
}
private void btnGetTravlTools_Click(object sender, EventArgs e)
{
string sql = @"--查询可乘坐的交通级别
select t.endpostlevel 岗位级别,t.typeoftraveltools 交通工具类型,
d.dictionaryname,t.TAKETHETOOLLEVEL 交通工具级别,
dl.dictionaryname
from smtoa.T_OA_TAKETHESTANDARDTRANSPORT t
inner join smtsystem.t_sys_dictionary d on t.typeoftraveltools = d.dictionaryvalue and d.dictioncategoryname='交通工具类型'
inner join smtsystem.t_sys_dictionary dl on t.TAKETHETOOLLEVEL = dl.dictionaryvalue and dl.dictioncategory='VICHILELEVEL'--'交通工具类型'
where t.travelsolutionsid='" + txtTraveSolutionid.Text + @"'
order by t.typeoftraveltools";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dtTravSolution.DataSource = dt;
OracleHelp.close();
}
private void UpdateSalaryBlancePostid(string salaryarchiveid)
{
bool needAdd = false;
DateTime date = DateTime.Parse(txtStartDate.Text);
string sql = @"update smthrm.t_hr_salaryarchive t
set ";
if (!string.IsNullOrEmpty(txBalancePostid.Text))
{
sql += @" t.balancepostid='" + txBalancePostid.Text + @"'
,t.balancepostname='" + txtBlancePostName.Text + @"'";
needAdd = true;
}
if (!string.IsNullOrEmpty(txtPayCompany.Text))
{
if (needAdd)
{
sql += @" ,";
}
sql += @" t.paycompany='" + txtPayCompany.Text + @"'";
needAdd = true;
}
if (!string.IsNullOrEmpty(txtAttendCompany.Text))
{
if (needAdd)
{
sql += @" ,";
}
sql += @" t.attendanceorgid='" + txtAttendCompany.Text + @"'
,t.attendanceorgname='" + txtAttendCompanyName.Text + @"'";
}
sql += @" where t.salaryarchiveid='" + salaryarchiveid + @"'";
OracleHelp.Connect();
int i = OracleHelp.Excute(sql);
txtMessagebox.Text = "更新员工薪资档案信息成功，影响记录数：" + i;
OracleHelp.close();
}
private void btnGetCompanyid_Click(object sender, EventArgs e)
{
string sql = @"select c.companyid--,c.cname
from smthrm.t_hr_company c
where c.cname like '%" + textSalaryItemCompany.Text + @"%'";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
if (dt.Rows.Count == 1)
{
txtSalaryItemCompanyid.Text = dt.Rows[0]["companyid"].ToString();
}
else
{
MessageBox.Show("公司不存在或有同名！");
}
OracleHelp.close();
}
private void btnSelect_Click(object sender, EventArgs e)
{
string sql = @"select c.cname,c.companyid from smthrm.t_hr_company c
where c.cname like '%" + txtCompanyName.Text + @"%'";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
if (dt != null)
{
dataGridEmployees.DataSource = dt;
OracleHelp.close();
txtMessagebox.Text += System.Environment.NewLine + "查询完成，共：" + dt.Rows.Count.ToString() + "条数据";
}
else
{
txtMessagebox.Text += System.Environment.NewLine + "查询完成，共：0条数据";
}
}
private void btnGetTraveSolution_Click(object sender, EventArgs e)
{
string sql = @"--查询指定公司应用的考勤方案
select a.companyid,b.travelsolutionsid,b.programmename from smtoa.T_OA_PROGRAMAPPLICATIONS a--出差方案应用
inner join smtoa.T_OA_TRAVELSOLUTIONS b --出差方案
on a.travelsolutionsid=b.travelsolutionsid
inner join smthrm.t_hr_company c on c.companyid=a.companyid
where c.companyid='" + txtCompanyid.Text + "'";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dtTravSolution.DataSource = dt;
OracleHelp.close();
if (dt.Rows.Count > 0)
{
txtTraveSolutionid.Text = dt.Rows[0]["travelsolutionsid"].ToString();
}
}
private void btnSelect_Click(object sender, EventArgs e)
{
string sql = @"select e.employeeename,
e.employeecname,
e.employeestate 员工状态,-- 0使用，1在职，2已离职，3离职中
--e.editstate 员工状态,
ep.isagency 岗位类型,--0主岗位，1兼职岗位
ep.editstate 岗位生效状态,--0失效，1生效,
ep.checkstate 岗位审核状态,--0未提交，1审核中，2审核通过，3审核不通过
ep.postlevel,
ep.postid,
ep.employeepostid 员工岗位主键,
pd.postname,
dp.departmentname,
c.cname,
c.briefname,
c.companyid
from smthrm.t_hr_employee e
inner join smthrm.t_hr_employeepost ep
on e.employeeid = ep.employeeid
inner join smthrm.t_hr_post p
on ep.postid = p.postid
inner join smthrm.t_hr_postdictionary pd
on p.postdictionaryid = pd.postdictionaryid
inner join smthrm.t_hr_company c
on p.companyid = c.companyid
inner join smthrm.t_hr_department d
on p.departmentid = d.departmentid
inner join smthrm.t_hr_departmentdictionary dp
on d.departmentdictionaryid = dp.departmentdictionaryid
where
e.employeeid='" + Txtid.Text + @"'
and ep.editstate=1
and ep.checkstate=2;
";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dataGridEmployees.DataSource = dt;
OracleHelp.close();
}
private void UpdateEmployeeState(string employeeId)
{
DialogResult MsgBoxResult; //设置对话框的返回值
MsgBoxResult = MessageBox.Show("是否更新此员工状态为在职？", //对话框的显示内容
"提示", //对话框的标题
MessageBoxButtons.YesNo, //定义对话框的按钮，这里定义了YSE和NO两个按钮
MessageBoxIcon.Exclamation, //定义对话框内的图表式样，这里是一个黄色三角型内加一个感叹号
MessageBoxDefaultButton.Button2); //定义对话框的按钮式样
if (MsgBoxResult == DialogResult.Yes) //如果对话框的返回值是YES（按"Y"按钮）
{
string sql = @"update smthrm.t_hr_employee t set t.employeestate='1'
where t.employeeid='" + GlobalParameters.employeeid + "'";
OracleHelp.Connect();
int i = OracleHelp.Excute(sql);
txtMessagebox.Text = "更新员工状态完成，处理了：" + i + "条数据！" + System.Environment.NewLine + txtMessagebox.Text;
OracleHelp.close();
}
}
private void delAttendrecordAll()
{
ShowMessageAsny("----------------------开始删除员工考勤初始化记录......");
OracleHelp.Connect();
for (int i = 0; i < dataGridEmployees.RowCount; i++)
{
GlobalParameters.employeeid = dataGridEmployees.Rows[i].Cells["employeeid"].EditedFormattedValue.ToString();
GlobalParameters.employeeName = dataGridEmployees.Rows[i].Cells["employeename"].EditedFormattedValue.ToString();
string sql = @"delete smthrm.t_hr_attendancerecord a
where a.ownercompanyid = '" + GlobalParameters.employeeMasterCompanyid + @"'
and a.ownerid = '" + GlobalParameters.employeeid + @"'
and a.attendancedate >= To_Date('" + GlobalParameters.StartDate + @"', 'yyyy-MM-dd')
and a.attendancedate <= To_Date('" + GlobalParameters.EndDate + @"', 'yyyy-MM-dd')";
int j = OracleHelp.Excute(sql);
ShowMessageAsny("删除员工:" + GlobalParameters.employeeName + " 考勤初始化记录完成，处理了：" + j + "条数据！");
}
OracleHelp.close();
ShowMessageAsny("----------------------删除员工考勤初始化记录完成!");
}
private void btnGetEmployeeId_Click_1(object sender, EventArgs e)
{
string sql = @"select e.employeeename,e.employeeid
from smthrm.t_hr_employee e
where
e.employeecname like '%" + txtGenEmployeeName.Text + @"%'";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
if (dt.Rows.Count == 1)
{
txtGenerEmployeeid.Text = dt.Rows[0]["employeeid"].ToString();
txtGenEmployeeName.Text = dt.Rows[0]["employeeename"].ToString();
}
else
{
MessageBox.Show("员工不存在或有同名！");
}
OracleHelp.close();
}
private void UpdatePost(string isAgence, string isActived, string employeePostId)
{
DialogResult MsgBoxResult; //设置对话框的返回值
MsgBoxResult = MessageBox.Show("是否确认更新此员工岗位？", //对话框的显示内容
"提示", //对话框的标题
MessageBoxButtons.YesNo, //定义对话框的按钮，这里定义了YSE和NO两个按钮
MessageBoxIcon.Exclamation, //定义对话框内的图表式样，这里是一个黄色三角型内加一个感叹号
MessageBoxDefaultButton.Button2); //定义对话框的按钮式样
if (MsgBoxResult == DialogResult.Yes) //如果对话框的返回值是YES（按"Y"按钮）
{
string sql = @"update smthrm.t_hr_employeepost t set t.isagency='" + isAgence
+ @"',t.editstate='" + isActived + @"'
where t.employeepostid='" + employeePostId + "'";
OracleHelp.Connect();
int i = OracleHelp.Excute(sql);
txtMessagebox.Text = "处理完成，处理了：" + i + "条数据！" + System.Environment.NewLine + txtMessagebox.Text;
OracleHelp.close();
}
}
private void btnGetSalaryAchive_Click(object sender, EventArgs e)
{
string sql = @"select e.employeeid,s.salaryarchiveid,e.employeecname,c.cname 发薪机构,
s.attendanceorgid,ac.cname 考勤机构,s.paycompany 发薪结构id,
s.balancepostid 结算岗位,
s.balancepostname 结算岗位名称,
e.employeestate,
s.balancepostid,s.balancepostname,
ec.cname 员工档案所属公司,ec.companyid,
s.othersubjoin 生效年份,
s.otheradddeduct 生效月份
,s.postlevel 岗位级别
,s.salarylevel 薪资级别
,s.checkstate
,s.createuserid
,s.updateuserid
,s.createdate
,s.updatedate
,s.salaryarchiveid
from smthrm.t_hr_employee e
inner join smthrm.t_hr_salaryarchive s on e.employeeid=s.employeeid
left join smthrm.t_hr_company c on s.paycompany=c.companyid
left join smthrm.t_hr_company ec on e.ownercompanyid=ec.companyid
left join smthrm.t_hr_company ac on s.attendanceorgid=ac.companyid
where
s.employeeid='" + GlobalParameters.employeeid + @"'
--and s.checkstate='2'
order by s.othersubjoin desc,s.otheradddeduct desc";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dtSalaryAchive.DataSource = dt;
if (dt.Rows.Count > 0)
{
salaryAchiveId = dt.Rows[0]["salaryarchiveid"].ToString();
paycompanyId = dt.Rows[0]["发薪结构id"].ToString();
}
OracleHelp.close();
}
private void delSalaryrecord(string salaryrecordid)
{
string sql = @"delete from smthrm.T_HR_EmployeeSalaryRecordItem t
where t.employeesalaryrecordid in
(
select a.employeesalaryrecordid from smthrm.t_hr_employeesalaryrecord a
where a.EMPLOYEESALARYRECORDID='" + salaryrecordid + @"'
)";
OracleHelp.Connect();
int i = OracleHelp.Excute(sql);
txtMessagebox.Text = "删除员工薪资记录项目成功，影响记录数：" + i + System.Environment.NewLine + txtMessagebox.Text;
sql = @"delete from smthrm.t_hr_employeesalaryrecord a
where a.EMPLOYEESALARYRECORDID ='" + salaryrecordid + @"'";
i = OracleHelp.Excute(sql);
txtMessagebox.Text = "删除员工薪资记录成功，影响记录数：" + i + System.Environment.NewLine + txtMessagebox.Text;;
OracleHelp.close();
}
/// <summary>
/// 验证发薪机构及考勤机构
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCompanyCheck_Click(object sender, EventArgs e)
{
string sql = @"select c.companyid,c.cname from smthrm.t_hr_company c
where c.cname like '%" + txtPayCompanyName.Text + "%'";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
if (dt.Rows.Count == 1)
{
txtPayCompanyName.Text = dt.Rows[0]["cname"].ToString();
txtPayCompany.Text = dt.Rows[0]["companyid"].ToString();
}
else
{
MessageBox.Show("发薪机构多条或没有找到记录！");
}
sql = @"select c.companyid,c.cname from smthrm.t_hr_company c
where c.cname like '%" + txtAttendCompanyName.Text + "%'";
DataTable dt2 = OracleHelp.getTable(sql);
if (dt2.Rows.Count == 1)
{
txtAttendCompanyName.Text = dt2.Rows[0]["cname"].ToString();
txtAttendCompany.Text = dt2.Rows[0]["companyid"].ToString();
}
else
{
MessageBox.Show("考勤机构多条或没有找到记录！");
}
OracleHelp.close();
txtMessagebox.Text = "获取组织架构信息完成！";
}
private void btnGetTravButie_Click(object sender, EventArgs e)
{
string sql = @"
--查询地区补贴
select c.city,a.travelsolutionsid,a.postlevel
,a.Accommodation 住宿补贴
,a.Transportationsubsidies 交通补贴
,a.Mealsubsidies 餐费补贴
from smtoa.T_OA_AreaAllowance a--地区差异分类补贴
inner join smtoa.T_OA_AreaDifference b --地区差异分类表
on b.areadifferenceid=a.areadifferenceid
inner join smtoa.T_OA_AreaCity c--地区分类城市
on c.areadifferenceid=b.areadifferenceid
where
c.city=(
select t.dictionaryvalue from smtsystem.t_sys_dictionary t--查询城市字典值
where t.dictionaryname='" + txtTraveCityName.Text + @"'
and t.dictioncategory='CITY')
and a.postlevel in (
select --查询员工出差岗位级别,所属公司
ep.postlevel
from smthrm.t_hr_employee e
inner join smthrm.t_hr_employeepost ep
on e.employeeid = ep.employeeid
where
e.employeeid = '" + Txtid.Text + @"'
and ep.editstate=1 and ep.isagency=0 and ep.checkstate=2
)
and
a.travelsolutionsid='" + txtTraveSolutionid.Text + "'";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
dtTravSolution.DataSource = dt;
OracleHelp.close();
}
private void SendDocByCompanyid(string companyid)
{
Dictionary <string, string> dicCompany = new Dictionary <string, string>();
string strSunCompany = @"select c.companyid,c.cname from smthrm.t_hr_company c
where c.fatherid='" + companyid + "'";
OracleHelp.Connect();
DataTable dtsun = OracleHelp.getTable(strSunCompany);
if (dtsun != null)
{
if (dtsun.Rows.Count > 0)
{
for (int i = 0; i < dtsun.Rows.Count; i++)
{
dicCompany.Add(dtsun.Rows[i]["cname"].ToString(), dtsun.Rows[i]["companyid"].ToString());
string strGrandSunCompany = @"select c.companyid,c.cname from smthrm.t_hr_company c
where c.fatherid='" + dtsun.Rows[i]["companyid"].ToString() + "'";
DataTable dtGrandSun = OracleHelp.getTable(strGrandSunCompany);
if (dtGrandSun != null)
{
if (dtGrandSun.Rows.Count > 0)
{
for (int j = 0; j < dtGrandSun.Rows.Count; j++)
{
dicCompany.Add(dtGrandSun.Rows[j]["cname"].ToString(), dtGrandSun.Rows[j]["companyid"].ToString());
}
}
}
}
}
}
OracleHelp.close();
if (string.IsNullOrEmpty(TxtSendDocid.Text))
{
MessageBox.Show("请先查询公文id");
return;
}
string msg = string.Empty;
ObservableCollection <T_OA_DISTRIBUTEUSER> distributeAddList = new ObservableCollection <T_OA_DISTRIBUTEUSER>();
SmtOACommonOfficeClient DocDistrbuteClient = new SmtOACommonOfficeClient();
ObservableCollection <string> CompanyIDsList = new ObservableCollection <string>();
ObservableCollection <string> StrDepartmentIDsList = new ObservableCollection <string>(); //获取部门ID
ObservableCollection <string> StrPositionIDsList = new ObservableCollection <string>();
ObservableCollection <string> StrStaffList = new ObservableCollection <string>(); //员工ID
if (dicCompany.Count > 0)
{
foreach (var q in dicCompany)
{
//if (q.Key == "杭州新神州通货物运输有限公司") continue;
ShowMessage("发布公文给这个公司：" + q.Key + " 公司id：" + q.Value);
CompanyIDsList.Add(q.Value);
T_OA_DISTRIBUTEUSER distributeTmp = new T_OA_DISTRIBUTEUSER();
distributeTmp.DISTRIBUTEUSERID = Guid.NewGuid().ToString();
distributeTmp.MODELNAME = "CompanySendDoc";
distributeTmp.FORMID = TxtSendDocid.Text;
distributeTmp.VIEWTYPE = "0";//公司
distributeTmp.VIEWER = q.Value;
distributeTmp.CREATEDATE = DateTime.Now;
distributeTmp.CREATEUSERID = GlobalParameters.employeeid;
distributeTmp.CREATEUSERNAME = GlobalParameters.employeeName;
distributeTmp.CREATEPOSTID = GlobalParameters.employeeMasterPostid;
distributeTmp.CREATEDEPARTMENTID = GlobalParameters.employeeMasterDepartmentid;
distributeTmp.CREATECOMPANYID = GlobalParameters.employeeMasterCompanyid;
distributeTmp.OWNERID = GlobalParameters.employeeid;
distributeTmp.OWNERNAME = GlobalParameters.employeeName;
distributeTmp.OWNERPOSTID = GlobalParameters.employeeMasterPostid;
distributeTmp.OWNERDEPARTMENTID = GlobalParameters.employeeMasterDepartmentid;
distributeTmp.OWNERCOMPANYID = GlobalParameters.employeeMasterCompanyid;
distributeAddList.Add(distributeTmp);
}
ShowMessage("发布公文给所有公司完毕，共发送：" + dicCompany.Count + " 条数据");
}
V_BumfCompanySendDoc tmpSenddoc = DocDistrbuteClient.GetBumfDocInfo(TxtSendDocid.Text);
if (tmpSenddoc.OACompanySendDoc == null)
{
MessageBox.Show("通过id获取的公文为空，请检查后再试");
return;
}
T_OA_SENDDOC sendDoc = tmpSenddoc.OACompanySendDoc;
sendDoc.T_OA_SENDDOCTYPE = null;
sendDoc.T_OA_SENDDOCTYPEReference = null;
DocDistrbuteClient.BatchAddCompanyDocDistrbuteForNew(distributeAddList.ToArray(), CompanyIDsList.ToArray(), StrDepartmentIDsList.ToArray(), StrPositionIDsList.ToArray(), StrStaffList.ToArray(), sendDoc);
//MessageBox.Show("发布公文成功！");
}
/// <summary>
/// 获取结算岗位
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnGetBalancePost_Click(object sender, EventArgs e)
{
string sql = @"select e.employeeename,
e.employeecname,
case when ep.isagency='0' then '主岗位' else '兼职岗位' end 岗位类型,
--ep.isagency 岗位类型 ,--0主岗位 1兼职岗位
case when ep.editstate='0' then '已失效' else '生效中' end 生效状态,
--ep.editstate 岗位生效状态,--0失效，1生效,
ep.checkstate 岗位审核状态,--0未提交，1审核中，2审核通过，3审核不通过
pd.postname,
dp.departmentname,
c.cname,
case
when e.employeestate = '0' then '试用'
when e.employeestate = '1' then '在职'
when e.employeestate = '2' then '已离职'
when e.employeestate = '3' then '离职中'
end 员工状态,-- 0使用，1在职，2已离职，3离职中
ep.postlevel 岗位级别,
e.fingerprintid 指纹编码,
ep.employeepostid,
e.employeeid,
ep.postid,
c.companyid,
et.entrydate 入职日期,
et.checkstate 入职业审核状态
from smthrm.t_hr_employee e
inner join smthrm.t_hr_employeepost ep
on e.employeeid = ep.employeeid
inner join smthrm.t_hr_employeeentry et
on e.employeeid=et.employeeid
inner join smthrm.t_hr_post p
on ep.postid = p.postid
inner join smthrm.t_hr_postdictionary pd
on p.postdictionaryid = pd.postdictionaryid
inner join smthrm.t_hr_company c
on p.companyid = c.companyid
inner join smthrm.t_hr_department d
on p.departmentid = d.departmentid
inner join smthrm.t_hr_departmentdictionary dp
on d.departmentdictionaryid = dp.departmentdictionaryid
where
e.employeecname like '%" + txtBalanceEmployeeName.Text + @"%'
and ep.editstate='1'
and ep.isagency='0'
and ep.checkstate=2
order by ep.isagency,ep.editstate desc ";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
if (dt.Rows.Count == 1)
{
txBalancePostid.Text = dt.Rows[0]["postid"].ToString();
if (txBalancePostid.Text == GlobalParameters.employeeMasterPostid)
{
MessageBox.Show("不需要更新结算岗位，两人在同一家公司");
}
txtBlancePostName.Text = dt.Rows[0]["postname"].ToString()
+ "-" + dt.Rows[0]["departmentname"].ToString()
+ "-" + dt.Rows[0]["cname"].ToString();
}
else
{
MessageBox.Show("结算员工有同名或主岗位异常！");
}
OracleHelp.close();
txtMessagebox.Text = "获取员工信息完成！";
}
