bool EditSave()
{
if (CheckSubjectName(string.Format(" and train_class_subject_id!={0}", Convert.ToInt32(Request.QueryString["subjectid"]))))
{
try
{
OracleAccess oracleAccess = new OracleAccess();
string sql =
string.Format(
"update zj_train_class_subject set subject_name='{0}', exam_on_computer={1} ,pass_result={2}, memo='{3}',class_hour={4} where train_class_subject_id={5}",
txtSubjectName.Text, chkIs.Checked == true ? 1 : 2,
Convert.ToDouble(txtPassResult.Text),
txtMemo.Text == "" ? " " : txtMemo.Text, txtHour.Text.Trim() == "" ? 0 : Convert.ToInt32(txtHour.Text),
Convert.ToInt32(Request.QueryString["subjectid"])
);
oracleAccess.ExecuteNonQuery(sql);
oracleAccess.ExecuteNonQuery("delete from zj_train_class_subject_book where train_class_subject_id=" +
Convert.ToInt32(Request.QueryString.Get("subjectid")));
InserClassBook(Convert.ToInt32(Request.QueryString.Get("subjectid")));
return(true);
}
catch
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据保存失败！')", true);
return(false);
}
}
else
{
ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "", "alert('该培训班科目已经存在,请重新输入！')", true);
txtSubjectName.Focus();
return(false);
}
}
private void InsertResult(int classID)
{
OracleAccess access = new OracleAccess();
try
{
access.ExecuteNonQuery("delete from zj_train_class_subject_result where train_class_id=" + classID);
StringBuilder sqlInsert = new StringBuilder();
DataTable dtSub = GetSubjectByClassID(classID);
foreach (DataRow sr in dtSub.Rows)
{
DataTable dtEmp = GetEmpByClassID(classID);
if (dtEmp.Rows.Count > 0)
{
sqlInsert.Append("begin ");
foreach (DataRow r in dtEmp.Rows)
{
sqlInsert.Append(" insert into zj_train_class_subject_result(train_class_subject_result_id, ");
sqlInsert.Append(" train_class_id,train_class_subject_id,employee_id,exam_date,result,ispass)");
sqlInsert.AppendFormat(" values (TRAIN_CLASS_SUBJECT_RESULT_SEQ.nextval,{0},{1},{2},null,null,0);",
classID, sr["train_class_subject_id"],
Convert.ToInt32(r[0]));
}
sqlInsert.Append(" end; ");
access.ExecuteNonQuery(sqlInsert.ToString());
sqlInsert.Length = 0;
}
}
}
catch (Exception)
{
}
}
protected void grdEntity_RowCommand(object sender, GridViewCommandEventArgs e)
{
string Id = e.CommandArgument.ToString();
if (string.IsNullOrEmpty(Id))
{
return;
}
if (e.CommandName == "del")
{
try
{
OracleAccess oracleAccess = new OracleAccess();
oracleAccess.ExecuteNonQuery("update zj_train_plan_employee set train_class_id=null where train_class_id=" + Id);
string sql = "delete from ZJ_TRAIN_CLASS where TRAIN_CLASS_ID=" + Id;
oracleAccess.ExecuteNonQuery(sql);
}
catch
{
ClientScript.RegisterStartupScript(GetType(), "Error", "alert('该培训班已被引用，不能删除！');", true);
return;
}
//ClientScript.RegisterStartupScript(GetType(), "OK", "alert('删除成功！');", true);
hfSelect.Value = GetSql();
grdEntity.DataBind();
}
}
private void DeleteData(int nBookID)
{
RandomExamSubjectBLL paperStrategySubjectBLL = new RandomExamSubjectBLL();
RandomExamSubject paperStrategySubject = paperStrategySubjectBLL.GetRandomExamSubject(int.Parse(Request.QueryString.Get("id")));
if (paperStrategySubject != null)
{
RandomExamResultBLL reBll = new RandomExamResultBLL();
IList <RailExam.Model.RandomExamResult> examResults = reBll.GetRandomExamResultByExamID(paperStrategySubject.RandomExamId);
if (Pub.HasPaper(Convert.ToInt32(paperStrategySubject.RandomExamId)))
{
SessionSet.PageMessage = "该考试已生成试卷，取题范围不能被删除！";
return;
}
if (examResults.Count > 0)
{
SessionSet.PageMessage = "已有考生参加考试，取题范围不能被删除！";
return;
}
}
RandomExamStrategyBLL paperStrategyBookChapterBLL = new RandomExamStrategyBLL();
paperStrategyBookChapterBLL.DeleteRandomExamStrategy(nBookID);
OracleAccess db = new OracleAccess();
string strSql = "delete from Random_Exam_Strategy where Is_Mother_Item=1 and Mother_ID=" + nBookID;
db.ExecuteNonQuery(strSql);
strSql = "delete from Random_Exam_Item_Select where RANDOM_EXAM_STRATEGY_ID=" + nBookID;
db.ExecuteNonQuery(strSql);
}
/// <summary>
/// 更新职名的计划人数
/// </summary>
/// <param name="oldClassOrgID"></param>
/// <param name="newClassID"></param>
private void UpdatePostNum(string oldClassOrgID, string newClassID)
{
string sql = "select employee_number from zj_train_plan_post_class_org where train_plan_post_class_org_id=" +
oldClassOrgID;
int num = 0;
DataTable dt = new OracleAccess().RunSqlDataSet(sql).Tables[0];
if (dt != null)
{
if (dt.Rows[0][0] != "")
{
num = Convert.ToInt32(dt.Rows[0][0]);
}
}
OracleAccess access = new OracleAccess();
try
{
sql = string.Format(@"
update zj_train_plan_post set employee_number=employee_number-" + num
+ "where train_plan_post_id in (select distinct train_plan_post_id from zj_train_plan_post_class where" +
" train_plan_post_class_id in (select distinct train_plan_post_class_id from zj_train_plan_post_class_org" +
" where train_plan_post_class_org_id={0}))", oldClassOrgID);
access.ExecuteNonQuery(sql); //原来的职名
sql = @"update zj_train_plan_post set employee_number=employee_number+" +
num +
" where train_plan_post_id in (select distinct train_plan_post_id from zj_train_plan_post_class where train_plan_post_class_id=" +
newClassID + ")";
access.ExecuteNonQuery(sql); //新职名
}
catch
{
}
}
protected void btnOk_Click(object sender, EventArgs e)
{
OracleAccess db = new OracleAccess();
string strSql = "select * from Employee where Employee_ID=" + ViewState["EmployeeID"];
DataRow dr = db.RunSqlDataSet(strSql).Tables[0].Rows[0];
int orgID;
if (ddlWorkgroup.SelectedValue != "0")
{
orgID = Convert.ToInt32(ddlWorkgroup.SelectedValue);
}
else
{
orgID = Convert.ToInt32(ddlWorkshop.SelectedValue);
}
OrganizationBLL organizationBLL = new OrganizationBLL();
EmployeeBLL objEmployeeBll = new EmployeeBLL();
IList <RailExam.Model.Employee> objView =
objEmployeeBll.GetEmployeeByWhereClause("GetStationOrgID(a.Org_ID)=" + organizationBLL.GetStationOrgID(orgID) +
" and Work_No='" + txtWorkNo.Text.Trim() + "'");
if (objView.Count > 0)
{
SessionSet.PageMessage = "该员工编码在本单位已经存在！";
return;
}
strSql = "update Employee "
+ " set Employee_Name='" + txtName.Text + "',"
+ "Org_ID=" + orgID + ","
+ "Work_No='" + txtWorkNo.Text + "',"
+ "identity_CardNo='" + txtPostNo.Text + "',"
+ "PinYin_Code='" + Pub.GetChineseSpell(txtName.Text) + "' "
+ " where Employee_ID=" + ViewState["EmployeeID"];
db.ExecuteNonQuery(strSql);
strSql = "insert into ZJ_Employee_Work "
+ " values(Employee_Work_Seq.nextval, "
+ dr["EMPLOYEE_ID"]
+ ", to_char(sysdate,'yyyy-mm-dd')," + dr["org_id"] + "," + orgID + ","
+ dr["Post_ID"] + "," + dr["Post_ID"] + ","
+ "'',sysdate,'" + PrjPub.CurrentLoginUser.EmployeeName + "',"
+ "to_date('" + dr["Post_Date"] + "','YYYY-MM-DD HH24:MI:SS'))";
db.ExecuteNonQuery(strSql);
string transferID = Request.QueryString.Get("transferID");
EmployeeTransferBLL objTransferBll = new EmployeeTransferBLL();
objTransferBll.DeleteEmployeeTransfer(Convert.ToInt32(transferID));
SystemLogBLL objLogBll = new SystemLogBLL();
objLogBll.WriteLog("将" + dr["Employee_Name"] + "调至" + lblOrg.Text);
Response.Write("<script>window.returnValue='true';window.close();</script>");
}
//保存数据
private bool saveData()
{
int type = 2;
if (!string.IsNullOrEmpty(ViewState["ComputerDetailType"].ToString()))
{
type = int.Parse(ViewState["ComputerDetailType"].ToString());
}
string sqlStr = string.Empty;
if (type == 1)//修改
{
sqlStr = "update COMPUTER_ROOM set "
+ " ORG_ID =" + hfOrgID.Value + " ,"
+ "COMPUTER_ROOM_NAME='" + txtCOMPUTER_ROOM_NAME.Text + "',"
+ "ADDRESS='" + txtADDRESS.Text + "',"
+ "COMPUTER_NUMBER=" + txtCOMPUTER_NUMBER.Text + ","
+ "BAD_SEAT='" + hfBadSeatIDs.Value + "',"
+ "IS_EFFECT= " + ddlIS_EFFECT.SelectedValue + ","
+ "Contract_Person='" + txtContractPerson.Text.Trim() + "',"
+ "Contract_Phone='" + txtContractPhone.Text.Trim() + "',"
+ "COMPUTER_SERVER_ID = " + (dropServer.SelectedValue == "" ? "null" : dropServer.SelectedValue)
+ " where COMPUTER_ROOM_ID=" + ViewState["ComputerDetailID"];
}
else if (type == 2) //新增
{
sqlStr = "insert into Computer_room values (COMPUTER_ROOM_SEQ.nextval,"
+ hfOrgID.Value + ", '" + txtCOMPUTER_ROOM_NAME.Text + "','" + txtADDRESS.Text + "',"
+ txtCOMPUTER_NUMBER.Text + ",'" + hfBadSeatIDs.Value + "',"
+ ddlIS_EFFECT.SelectedValue + "," + (dropServer.SelectedValue == ""?"null":dropServer.SelectedValue)
+ ",'" + txtContractPerson.Text.Trim() + "','" + txtContractPhone.Text.Trim() + "',0)";
}
OracleAccess ora = new OracleAccess();
try
{
ora.ExecuteNonQuery(sqlStr);
if (ddlIS_EFFECT.SelectedValue == "0" && type == 1)
{
sqlStr = "delete from COMPUTER_ROOM_DETAIL where COMPUTER_ROOM_ID=" + ViewState["ComputerDetailID"];
ora.ExecuteNonQuery(sqlStr);
}
}
catch
{
OxMessageBox.alert(this, "编辑失败！");
return(false);
}
ViewState["ComputerDetailType"] = 1; //设置为编辑
return(true);
}
/// <summary>
/// 更新职名的实际上报人数（原班和新班）
/// </summary>
private void UpdatePostLastNum(string oldClassOrgID, string newClassID)
{
OracleAccess access = new OracleAccess();
int num = 0;
try
{
// string sql = "select last_employee_number from zj_train_plan_post_class_org where train_plan_post_class_org_id=" +
// oldClassOrgID;
// num = Convert.ToInt32(access.RunSqlDataSet(sql).Tables[0].Rows[0][0]);
// sql = @"update zj_train_plan_post set last_employee_number=last_employee_number+" +
// num +
// " where train_plan_post_id in (select distinct train_plan_post_id from zj_train_plan_post_class where train_plan_post_class_id=" +
// newClassID + ")";
// access.ExecuteNonQuery(sql); //新职名
// sql=string.Format(@"
// update zj_train_plan_post set last_employee_number=last_employee_number-"+num
// +"where train_plan_post_id in (select distinct train_plan_post_id from zj_train_plan_post_class where" +
// " train_plan_post_class_id in (select distinct train_plan_post_class_id from zj_train_plan_post_class_org" +
// " where train_plan_post_class_org_id={0}))", oldClassOrgID);
// access.ExecuteNonQuery(sql); //原来的职名
string sql = string.Format(@"
update zj_train_plan_post set last_employee_number=
(select sum(last_employee_number) from zj_train_plan_post_class where train_plan_post_id=
(select distinct train_plan_post_id from zj_train_plan_post_class where train_plan_post_class_id={0}))
where train_plan_post_id= (select distinct train_plan_post_id from zj_train_plan_post_class where train_plan_post_class_id={0})
", newClassID);
access.ExecuteNonQuery(sql); //新职名
newClassID =
string.Format(
"(select distinct train_plan_post_class_id from zj_train_plan_post_class_org where train_plan_post_class_org_id={0})",
oldClassOrgID);
sql = string.Format(@"
update zj_train_plan_post set last_employee_number=
(select sum(last_employee_number) from zj_train_plan_post_class where train_plan_post_id=
(select distinct train_plan_post_id from zj_train_plan_post_class where train_plan_post_class_id={0}))
where train_plan_post_id= (select distinct train_plan_post_id from zj_train_plan_post_class where train_plan_post_class_id={0})
", newClassID);
access.ExecuteNonQuery(sql); //原来的职名
}
catch
{
}
}
protected void btnOK_Click(object sender, EventArgs e)
{
string name = this.txtName.Text.Trim();
OracleAccess oa = new OracleAccess();
string sql;
string id = Request.QueryString.Get("id");
if (String.IsNullOrEmpty(id))
{
string typeID = Request["typeID"].ToString();
sql = String.Format("insert into ZJ_TRAINPLAN_PROJECT values({0},{1},'{2}')", "zj_trainplan_project_seq.NEXTVAL", typeID, name);
}
else
{
sql = String.Format("update ZJ_TRAINPLAN_PROJECT set trainplan_project_name = '{0}' where trainplan_project_id = {1}", name, id);
}
try
{
oa.ExecuteNonQuery(sql);
string typeID = Request["typeID"].ToString();
this.ClientScript.RegisterStartupScript(this.GetType(), "OK", "alert('编辑成功');opener.parent.location.href='TrainPlanProject.aspx?selectedID=" + typeID + "';close();", true);
}
catch
{
this.ClientScript.RegisterStartupScript(this.GetType(), "OK", "alert('有异常输入，编辑不成功');", true);
}
}
private void EditInfo()
{
try
{
OracleAccess access = new OracleAccess();
StringBuilder sql = new StringBuilder();
sql.Append(" update zj_employee_match set ");
sql.Append(" match_date=to_date('{0}','yyyy-mm-dd hh24:mi:ss'),");
sql.Append(" unit='{1}',match_project='{2}',match_type='{3}',");
sql.Append(" total_score={4},lilun_score={5},shizuo_score={6},");
sql.Append(" match_rank={7},memo='{8}',create_date=to_date('{9}','yyyy-mm-dd hh24:mi:ss'),");
sql.Append(" create_person='{10}' where employee_match_id={11}");
string sqlEdit = string.Format(sql.ToString(), match_date.DateValue.ToString(),
dropunit.SelectedItem.Text, txtmatch_project.Text,
dropmatch_type.SelectedItem.Text,
Convert.ToDouble(txttotal_score.Text),
txtlilun_score.Text.Trim() == ""
? 0
: Convert.ToDouble(txtlilun_score.Text),
txtshizuo_score.Text.Trim() == ""
? 0
: Convert.ToDouble(txtshizuo_score.Text),
Convert.ToInt32(txtmatch_rank.Text), txtmemo.Text,
Convert.ToDateTime(DateTime.Now.ToShortDateString()).ToString(
"yyyy-MM-dd"),
PrjPub.CurrentLoginUser.EmployeeName,
Convert.ToInt32(Request.QueryString.Get("id")));
access.ExecuteNonQuery(sqlEdit);
ClientScript.RegisterClientScriptBlock(GetType(), "", "window.returnValue=true;window.close();", true);
}
catch
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据输入有误！');", true);
}
}
/// <summary>
/// 新增数据
/// </summary>
private void InsertInfo()
{
//新增培训数据
access = new OracleAccess();
StringBuilder sqlInsert = new StringBuilder();
sqlInsert.Append("insert into zj_employee_train values(EMPLOYEE_TRAIN_SEQ.NEXTVAL,");
sqlInsert.Append("{0},{1},to_date('{2}','yyyy-mm-dd hh24:mi:ss'),to_date('{3}','yyyy-mm-dd hh24:mi:ss'),");
sqlInsert.Append("{4},'{5}',{6},'{7}',to_date('{8}','yyyy-mm-dd hh24:mi:ss'),'{9}')");
string sqlIns = string.Format(sqlInsert.ToString(), employeeID, trainClassID, beginDate, endDate,
trainPlanTypeID, location, hour, classSubject, createDate, createPerson);
try
{
if (trainClassID > 0)
{
access.ExecuteNonQuery(sqlIns);
}
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据更新成功！');", true);
}
catch (Exception)
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据更新失败！');", true);
}
}
private void editInfo()
{
try
{
//string oldPostDate = new OracleAccess().RunSqlDataSet("select POST_DATE from employee where employee_id=" + empID).Tables[0].Rows[0][0].ToString();
string sql =
string.Format(
@"update zj_employee_work set transfer_date='{0}',old_org_id={1},new_org_id={2},old_post_id={3},
new_post_id={4},mobilizationorderno='{5}',create_date=to_date('{6}','yyyy-mm-dd hh24:mi:ss'),create_person='{7}' where employee_work_id={8}",
txtTransfer.DateValue, oldOrgID.Value == ""?0:Convert.ToInt32(oldOrgID.Value), hfOrgID.Value, oldPostID.Value == ""?0:Convert.ToInt32(oldPostID.Value), hfPostID.Value, txtmobilizationorderno.Text,
Convert.ToDateTime(DateTime.Now.ToShortDateString()).ToString("yyyy-MM-dd"),
PrjPub.CurrentLoginUser.EmployeeName, Convert.ToInt32(Request.QueryString.Get("id")));
OracleAccess access = new OracleAccess();
access.ExecuteNonQuery(sql); //基本信息
if (GetNewWorkID() == Request.QueryString.Get("id")) //如果修改的是最新的动态
{
UpdateEmployeeInfo(empID.Value); //更新主表信息
}
ClientScript.RegisterClientScriptBlock(GetType(), "", "window.returnValue=true;window.close();", true);
}
catch
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据更新失败！');", true);
}
}
private void DeleteInfo()
{
try
{
string id = Request.Form["__EVENTARGUMENT"];
OracleAccess access = new OracleAccess();
string sql =
string.Format("select * from zj_employee_work where employee_id={0} and employee_work_id>{1}",
Request.QueryString.Get("ID"), id);
DataSet ds = access.RunSqlDataSet(sql);
if (ds.Tables[0].Rows.Count > 0)
{
SessionSet.PageMessage = "请按照工作动态先后顺序，从后向前删除数据！";
return;
}
sql = string.Format("delete from zj_employee_work where employee_work_id={0}", Convert.ToInt32(id));
UpdateEmployeeInfo(Request.QueryString.Get("ID")); //更新主表信息
access.ExecuteNonQuery(sql);
ClientScript.RegisterClientScriptBlock(GetType(), "", "window.parent.frames[0].location=window.parent.frames[0].location;", true);
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据删除成功！')", true);
}
catch
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据删除失败！')", true);
}
}
/// <summary>
/// 更新主表信息
/// </summary>
/// <param name="empID"></param>
private void UpdateEmployeeInfo(string empID)
{
OracleAccess access = new OracleAccess();
try
{
string skillID = Request.Form["__EVENTARGUMENT"]; //要删除的技能动态ID
string maxskillID = "";
string sql = "select max(employee_skill_id) from zj_employee_skill where employee_id=" + empID;
DataTable dtMax = access.RunSqlDataSet(sql).Tables[0];
if (dtMax.Rows.Count > 0)
{
maxskillID = dtMax.Rows[0][0].ToString(); //最新的学习动态ID
}
if (skillID.Equals(maxskillID))
{
//删除的是最新的动态
sql = "select * from zj_employee_skill where employee_skill_id=" + maxskillID;
DataTable dtNewInfo = access.RunSqlDataSet(sql).Tables[0];
if (dtNewInfo.Rows.Count > 0)
{
//还原之前的动态
string oldSkillID = dtNewInfo.Rows[0]["old_level_id"].ToString();
string oldSafeID = dtNewInfo.Rows[0]["old_safe_level_id"].ToString();
sql = string.Format("update employee set TECHNICIAN_TYPE_ID={0},safe_level_id={1} where employee_id={2}",
oldSkillID == "" ? "0" : oldSkillID, oldSafeID == "" ? "null" : oldSafeID, empID);
access.ExecuteNonQuery(sql);
}
}
}
catch
{
}
}
private void InserClassBook(int subjectID)
{
OracleAccess access = new OracleAccess();
if (hfBookIds.Value.Trim().Length > 0)
{
string[] strBookID = hfBookIds.Value.Split('|');
string sql = "begin ";
foreach (string s in strBookID)
{
sql +=
string.Format(
"insert into zj_train_class_subject_book(train_class_subject_id,book_id) values ({0},{1});",
subjectID, Convert.ToInt32(s));
}
sql += "end; ";
try
{
access.ExecuteNonQuery(sql);
}
catch
{
}
}
}
private void AddInfo()
{
try
{
OracleAccess access = new OracleAccess();
string sql =
string.Format(
@" insert into zj_employee_skill(employee_skill_id,employee_id,appoint_time,qualification_time,old_level_id,now_level_id,
certificate_no,appoint_order_no,create_date,create_person,old_safe_level_id,new_safe_level_id)
values(employee_skill_seq.nextval,{0},to_date('{1}','yyyy-mm-dd hh24:mi:ss'),
to_date('{2}','yyyy-mm-dd hh24:mi:ss'),{3},{4},'{5}','{6}',to_date('{7}','yyyy-mm-dd hh24:mi:ss'),'{8}',{9},{10})",
Convert.ToInt32(Request.QueryString.Get("id")), txtappoint_time.DateValue.ToString(),
qualification_time.DateValue.ToString(),
Convert.ToInt32(droponame.SelectedValue), Convert.ToInt32(dropnname.SelectedValue),
txtcertificate_no.Text, txtappoint_order_no.Text,
Convert.ToDateTime(DateTime.Now.ToShortDateString()).ToString("yyyy-MM-dd"),
PrjPub.CurrentLoginUser.EmployeeName, dropOldSafe.SelectedValue == "0"
? "null"
: dropOldSafe.SelectedValue,
dropNewSafe.SelectedValue == "0" ? "null" : dropNewSafe.SelectedValue);
access.ExecuteNonQuery(sql); //新增信息
UpdateEmployeeInfo(Request.QueryString.Get("ID")); //修改主表信息
ClientScript.RegisterClientScriptBlock(GetType(), "", "window.returnValue=true;window.close();", true);
}
catch
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据保存失败！');", true);
}
}
protected void grdEntity_RowCommand(object sender, GridViewCommandEventArgs e)
{
OracleAccess ora = new OracleAccess();
string Id = e.CommandArgument.ToString();
if (e.CommandName == "del")
{
try
{
int roomID = 0;
int.TryParse(Id, out roomID);
ora.ExecuteNonQuery("delete COMPUTER_ROOM_APPLY where COMPUTER_ROOM_APPLY_ID=" + roomID);
}
catch
{
ClientScript.RegisterStartupScript(GetType(), "Error", "alert('该微机教室正在使用，不能删除！')", true);
return;
}
ClientScript.RegisterStartupScript(GetType(), "OK", "alert('删除成功！')", true);
if (PrjPub.CurrentLoginUser == null)
{
Response.Redirect("../Common/Error.aspx?error=Session过期请重新登录本系统！");
return;
}
else
{
int orgID = PrjPub.CurrentLoginUser.OrgID;
grdEntity1Bind(orgID);
}
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
OracleAccess db = new OracleAccess();
string[] str = hfOldPostID.Value.Split(',');
string sql = "select count(*) from Post where Parent_ID=" + hfNewPostID.Value;
int orderIndex = Convert.ToInt32(db.RunSqlDataSet(sql).Tables[0].Rows[0][0]) + 1;
for (int i = 0; i < str.Length; i++)
{
orderIndex = orderIndex + i;
sql = "select id_path from Post where Parent_ID=" + hfNewPostID.Value;
string idPath = db.RunSqlDataSet(sql).Tables[0].Rows[0][0] + "/" + str[i];
sql = "update Post set Parent_ID=" + hfNewPostID.Value + ", id_path='" + idPath + "',order_index='" + orderIndex + "' where post_id=" + str[i];
db.ExecuteNonQuery(sql);
}
txtNewPost.Text = "";
txtOldPost.Text = "";
hfNewPostID.Value = "";
hfOldPostID.Value = "";
SessionSet.PageMessage = "Ð޸ijɹ¦£¡";
}
private void editInfo()
{
try
{
OracleAccess access = new OracleAccess();
string sql =
string.Format(
"update zj_employee_education set graduate_date=to_date('{0}','yyyy-mm-dd hh24:mi:ss'),old_education_id={1},now_education_id={2},school_subject='{3}',study_style='{4}',diploma_no='{5}',graducate_school='{6}',school_type={7},create_date=to_date('{8}','yyyy-mm-dd hh24:mi:ss'),create_person='{9}' where employee_education_id={10}",
graduate_date.DateValue.ToString(), Convert.ToInt32(droponame.SelectedValue),
Convert.ToInt32(dropnname.SelectedValue), txtsubject.Text,
txtstyle.Text, txtdiplomano.Text,
txtgraducateschool.Text, Convert.ToInt32(dropSchoolCategory.SelectedValue),
Convert.ToDateTime(DateTime.Now.ToShortDateString()).ToString("yyyy-MM-dd"),
PrjPub.CurrentLoginUser.EmployeeName, Convert.ToInt32(Request.QueryString.Get("id")));
access.ExecuteNonQuery(sql); //修改信息
if (GetNewEduID() == Request.QueryString.Get("id")) //修改的是最新的教育动态
{
UpdateEmployeeInfo(empID.Value, txtsubject.Text, txtgraducateschool.Text, graduate_date.DateValue.ToString()); //修改基本信息
}
ClientScript.RegisterClientScriptBlock(GetType(), "", "window.returnValue=true;window.close();", true);
}
catch
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据更新失败！');", true);
}
}
/// <summary>
/// 新增信息
/// </summary>
private void InsertInfo(string exam_date, int exam_style, string exam_address, string exam_subject, double exam_score, int exam_result, int random_exam_id)
{
access = new OracleAccess();
StringBuilder sqlInsert = new StringBuilder();
sqlInsert.Append(
"insert into zj_employee_exam(employee_exam_id,employee_id,exam_date,exam_style,exam_address,");
sqlInsert.Append("exam_subject,exam_score ,exam_result,create_date,create_person,random_exam_id");
sqlInsert.Append(") values(employee_exam_seq.nextval,{0},to_date('{1}','yyyy-mm-dd hh24:mi:ss'),{2},");
sqlInsert.Append(" '{3}','{4}','{5}',{6},to_date('{7}','yyyy-mm-dd hh24:mi:ss'),'{8}',{9})");
string sqlIns = string.Format(sqlInsert.ToString(), employeeID, exam_date, exam_style, exam_address,
exam_subject, exam_score, exam_result, createDate, createPerson,
random_exam_id);
try
{
if (random_exam_id > 0)
{
access.ExecuteNonQuery(sqlIns);
}
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据更新成功！');", true);
}
catch (Exception)
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据更新失败！');", true);
}
}
private void AddInfo()
{
try
{
OracleAccess access = new OracleAccess();
StringBuilder sql = new StringBuilder();
sql.Append("insert into zj_employee_match values");
sql.Append("(employee_match_seq.nextval ,{0},to_date('{1}','yyyy-mm-dd hh24:mi:ss'),'{2}','{3}',");
sql.Append("'{4}',{5},{6},{7},{8},'{9}',to_date('{10}','yyyy-mm-dd hh24:mi:ss'),'{11}')");
string inssql = string.Format(
sql.ToString(), Convert.ToInt32(Request.QueryString.Get("id")), match_date.DateValue.ToString(),
dropunit.SelectedItem.Text, txtmatch_project.Text, dropmatch_type.SelectedItem.Text,
Convert.ToDouble(txttotal_score.Text),
txtlilun_score.Text.Trim() == "" ? 0 : Convert.ToDouble(txtlilun_score.Text),
txtshizuo_score.Text.Trim() == "" ? 0 : Convert.ToDouble(txtshizuo_score.Text),
Convert.ToInt32(txtmatch_rank.Text), txtmemo.Text,
Convert.ToDateTime(DateTime.Now.ToShortDateString()).ToString("yyyy-MM-dd"),
PrjPub.CurrentLoginUser.EmployeeName);
access.ExecuteNonQuery(inssql);
ClientScript.RegisterClientScriptBlock(GetType(), "", "window.returnValue=true;window.close();", true);
}
catch
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据输入有误！');", true);
}
}
protected void btnOK_Click(object sender, EventArgs e)
{
string name = this.txtName.Text.Trim();
OracleAccess oa = new OracleAccess();
string sql;
string id = Request.QueryString.Get("id");
if (String.IsNullOrEmpty(id))
{
sql = String.Format("insert into ZJ_TRAINPLAN_TYPE values({0},'{1}')", "train_plan_type_seq.NEXTVAL", name);
}
else
{
sql = String.Format("update ZJ_TRAINPLAN_TYPE set trainplan_type_name = '{0}' where trainplan_type_id = {1}", name, id);
}
try
{
oa.ExecuteNonQuery(sql);
this.ClientScript.RegisterStartupScript(this.GetType(), "OK", "alert('编辑成功');opener.location.reload();close();", true);
}
catch
{
this.ClientScript.RegisterStartupScript(this.GetType(), "OK", "alert('有异常输入，编辑不成功');", true);
}
}
/// <summary>
/// 更新信息
/// </summary>
private void RefInfo()
{
//获取基本信息
StringBuilder sqlClass = new StringBuilder();
sqlClass.Append(" select C.TRAIN_CLASS_ID,begin_date,end_date,TP.TRAIN_PLAN_TYPE_ID,TP.Location");
sqlClass.Append(" from zj_train_class TC right join (select train_plan_id, train_class_id from zj_train_plan_employee");
sqlClass.AppendFormat(" where employee_id={0} and train_class_id is not null ", employeeID);
sqlClass.Append(" ) C on C.TRAIN_CLASS_ID=TC.TRAIN_CLASS_ID ");
sqlClass.Append(" left join zj_train_plan TP on TP.TRAIN_PLAN_ID=C.TRAIN_PLAN_ID");
access = new OracleAccess();
DataTable dtClass = access.RunSqlDataSet(sqlClass.ToString()).Tables[0];
access.ExecuteNonQuery(" delete from zj_employee_train where employee_id=" + employeeID);
if (dtClass != null && dtClass.Rows.Count > 0)
{
foreach (DataRow r in dtClass.Rows)
{
trainClassID = Convert.ToInt32(r["TRAIN_CLASS_ID"]);
beginDate = r["begin_date"].ToString();
endDate = r["end_date"].ToString();
trainPlanTypeID = Convert.ToInt32(r["TRAIN_PLAN_TYPE_ID"]);
location = r["Location"].ToString();
GetSubAndHour(trainClassID);
InsertInfo();
}
}
BindGrid();
}
/// <summary>
/// 更新职名中的人数
/// </summary>
private void UpdatePostNum()
{
try
{
OracleAccess access = new OracleAccess();
string sqlNum =
" select sum(total_employee_number) allNum from zj_train_plan_post_class where train_plan_post_id=";
sqlNum += " (select train_plan_post_id from zj_train_plan_post_class where train_plan_post_class_id=" +
Convert.ToInt32(dropPlanClass.SelectedValue) + ")";
DataTable dt = access.RunSqlDataSet(sqlNum).Tables[0];
if (dt.Rows[0][0].ToString().Length > 0)
{
int allNum = Convert.ToInt32(dt.Rows[0][0]);
string sql =
string.Format(" update zj_train_plan_post set employee_number={0} where train_plan_post_id={1}",
allNum, Convert.ToInt32(dropPlanPost.SelectedValue));
access.ExecuteNonQuery(sql);
}
}
catch (Exception)
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('计划职名人数更新失败！')", true);
}
}
/// <summary>
/// 删除该科目的结果
/// </summary>
/// <param name="classID"></param>
/// <param name="resultID"></param>
private void DeleteSubjectResult(int classID, int resultID)
{
OracleAccess access = new OracleAccess();
access.ExecuteNonQuery("delete from zj_train_class_subject_result where train_class_id=" + classID +
" and train_class_subject_id=" + resultID);
}
private void UpdatePost()
{
try
{
OracleAccess access = new OracleAccess();
string sqlSel = "select post_ids from zj_train_plan_post where train_plan_post_id!=" + Request.QueryString.Get("id");
sqlSel += " and train_plan_id in ( select train_plan_id from zj_train_plan_post where train_plan_post_id=" + Request.QueryString.Get("id") + ")";
if (CheckPost(access.RunSqlDataSet(sqlSel).Tables[0], hfPostIDs.Value))
{
string sql =
string.Format(
"update zj_train_plan_post set post_ids='{0}',is_work_group_leader={1} where train_plan_post_id={2}",
hfPostIDs.Value, chkIsLeader.Checked == true ? 1 : 0, Request.QueryString.Get("id"));
access.ExecuteNonQuery(sql);
hfPostIDs.Value = "";
ClientScript.RegisterClientScriptBlock(GetType(), "", "window.returnValue=true;window.close();", true);
}
else
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('该职名中存在已选择的项，请重新选择！')", true);
txtPostName.Text = hfPostNames.Value;
}
}
catch (Exception)
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据保存失败！')", true);
}
}
private void InsertPost()
{
string sqlSel = "select post_ids from zj_train_plan_post where train_plan_id=" + Convert.ToInt32(Request.QueryString.Get("id"));
string sql =
string.Format(
"insert into zj_train_plan_post(train_plan_post_id,train_plan_id,post_ids,employee_number,last_employee_number,is_work_group_leader) values (zj_train_plan_post_seq.nextval,{0},'{1}',{2},{3},{4})",
Convert.ToInt32(Request.QueryString.Get("id")), hfPostIDs.Value, 0, 0, chkIsLeader.Checked == true ? 1 : 0);
try
{
OracleAccess access = new OracleAccess();
if (CheckPost(access.RunSqlDataSet(sqlSel).Tables[0], hfPostIDs.Value))
{
access.ExecuteNonQuery(sql);
hfPostIDs.Value = "";
ClientScript.RegisterClientScriptBlock(GetType(), "", "window.returnValue=true;window.close();", true);
}
else
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('该职名中存在已选择的项，请重新选择！')", true);
}
txtPostName.Text = hfPostNames.Value;
}
catch (Exception)
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('数据保存失败！')", true);
}
}
/// <summary>
/// 新增学员
/// </summary>
private int InsertEmp()
{
List <string> lstOld = new List <string>();
List <string> newEmpID = new List <string>();
try
{
string[] employeeIDs = hfSelectEmpID.Value.Split(',');
//string[] OldEmpIDs = hfAllEmpID.Value.Split(',');
//foreach (string str in OldEmpIDs)
//{
// lstOld.Add(str);
//}
foreach (string s in employeeIDs)
{
// if(!lstOld.Contains(s))
newEmpID.Add(s);
}
if (newEmpID.Count > 0)
{
string sql = " declare p_count number; begin ";
foreach (string employeeID in newEmpID)
{
if (employeeID.Length != 0)
{
sql +=
string.Format(
" select count(1) into p_count from ZJ_TRAIN_PLAN_EMPLOYEE where TRAIN_PLAN_ID={0} and EMPLOYEE_ID={1} and TRAIN_PLAN_POST_CLASS_ID={2};if p_count =0 then insert into ZJ_TRAIN_PLAN_EMPLOYEE(TRAIN_PLAN_EMPLOYEE_ID,TRAIN_PLAN_ID,EMPLOYEE_ID,train_plan_post_class_id,TRAIN_PLAN_POST_CLASS_ORG_ID) values(Train_Plan_Employee_SEQ.NextVal,{0},{1},{2},{3}); end if;",
Convert.ToInt32(Request.QueryString.Get("planID")), employeeID, Convert.ToInt32(hfClassID.Value),
Convert.ToInt32(hfOrgID.Value));
}
}
sql += " end;";
OracleAccess oracleAccess = new OracleAccess();
oracleAccess.ExecuteNonQuery(sql);
//ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('学员添加成功！')", true);
}
}
catch (Exception)
{
ClientScript.RegisterClientScriptBlock(GetType(), "", "alert('学员添加失败！')", true);
}
/////// 通过orgid 查询所对应的人数加上新加的人数
OracleAccess acc = new OracleAccess();
//int Num =Convert.ToInt32(
// acc.RunSqlDataSet(
// "select last_employee_number from zj_train_plan_post_class_org where train_plan_post_class_org_id=" +
// Convert.ToInt32(hfOrgID.Value)).Tables[0].Rows[0][0]);
int Num = Convert.ToInt32(
acc.RunSqlDataSet(
"select count(1) from zj_train_plan_employee where train_plan_post_class_org_id=" +
Convert.ToInt32(hfOrgID.Value)).Tables[0].Rows[0][0]);
//return Num + newEmpID.Count;
return(Num);
}
/// <summary>
/// 把所选择的计划培训班的学员添加到新增的班级中
/// </summary>
private void UpdateClassIDByPostClassID()
{
try
{
OracleAccess access = new OracleAccess();
access.ExecuteNonQuery(
"update zj_train_plan_employee set train_class_id=null where train_class_id=" +
Convert.ToInt32(hfID.Value));
string sql =
"update zj_train_plan_employee set train_class_id=" + Convert.ToInt32(hfID.Value) + " where train_plan_post_class_id=" +
Convert.ToInt32(dropPlanClass.SelectedValue);
access.ExecuteNonQuery(sql);
}
catch
{
}
}
/// <summary>
/// 删除学员对应的科目结果
/// </summary>
/// <param name="stuID"></param>
private void DeleteResultByStuID(string stuID)
{
OracleAccess access = new OracleAccess();
access.ExecuteNonQuery("delete from zj_train_class_subject_result where train_class_id=" +
Convert.ToInt32(hidClassID.Value) +
" and employee_id in (select employee_id from zj_train_plan_employee where train_plan_employee_id in (" +
stuID + "))");
}