protected void Page_Load(object sender, EventArgs e)
{
if (Session["userNo"] == null)
{
Response.Redirect("AuthorizationSelect.aspx");
}
string StaffID = Session["userNo"].ToString();
//personal info
string SQL_name = "select StaffName,License,LicenseExpired "
+ " from MSAS_HRInfo"
+ " where StaffID='" + StaffID + "'"
+ " and HRstatus='0'";//0是在职 ，1是离职
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(SqlHelper1.Conn, CommandType.Text, SQL_name))
{
if (rdr.Read())
{
Label1.Text = Convert.ToString(rdr.GetSqlValue(1)); //name
Label2.Text = Convert.ToString(rdr.GetSqlValue(0)); //license
Label3.Text = Convert.ToString(rdr.GetDateTime(2)); //licenseexpiry
Label4.Text = "AM" + StaffID; //staffid
}
}
//Authorization info
string SQL_Auth = "select Project,Range,Level,ExpireDate "
+ " from MSAS_AuthorizationList "
+ " where StaffID='" + StaffID + "'"
+ " AND Vaild ='1'" //有效
+ " and Status<>'2'"; //2是删除了的数据
DataTable dt = new DataTable();
dt.Columns.Add("Project", typeof(string));
dt.Columns.Add("Range", typeof(string));
dt.Columns.Add("Level", typeof(string));
dt.Columns.Add("ExpireDate", typeof(string));
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(SqlHelper1.Conn, CommandType.Text, SQL_Auth))
{
while (rdr.Read())
{
DataRow row = dt.NewRow();
row["Project"] = Convert.ToString(rdr.GetSqlValue(0)); //Project
row["Range"] = Convert.ToString(rdr.GetSqlValue(1)); //Range
row["Level"] = Convert.ToString(rdr.GetSqlValue(2)); //Level
row["ExpireDate"] = Convert.ToDateTime(rdr.GetDateTime(3)).ToShortDateString(); //ExpireDate
dt.Rows.Add(row);
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
public object gettrans(string fieldval, string fieldbname)
{
//检查数据是否已经存在
string SQL = "select FIELDVALUE from HR_PIF_TranslationTable WHERE FIELDNAME='" + fieldbname + "' and XLATLONGNAME='" + fieldval + "'";
SqlConnection sqlcnn = new SqlConnection(sqlstr);
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlcnn, CommandType.Text, SQL))
{
if (rdr.Read())
{
return(Convert.ToString(rdr.GetSqlValue(0)));
}
else
{
return(DBNull.Value);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["staffno"] == null)
{
Response.Redirect("Default.aspx");
}
else
{
Label1.Text = "Welcome " + Session["staffname"].ToString();
if (!IsPostBack)
{
//第一次打开设置下拉框
Station.Items.Clear();
Station.Items.Add("");
string SQL_Station = "select a.Station as Station2 from MSAS_Station a order by a.Station ";
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper1.Conn, CommandType.Text, SQL_Station))
{
while (rdr.Read())
{
Station.Items.Add(Convert.ToString(rdr.GetSqlValue(0)));
}
}
Department.Items.Clear();
Department.Items.Add("");
string SQL_Department = "select a.Department as Department2 from MSAS_Department a order by a.Department ";
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper1.Conn, CommandType.Text, SQL_Department))
{
while (rdr.Read())
{
Department.Items.Add(Convert.ToString(rdr.GetSqlValue(0)));
}
}
Training_Type.Items.Add("");
Training_Type.Items.Add("General Initial");
Training_Type.Items.Add("General Recurrent");
Training_Type.Items.Add("General One Time");
Training_Type.Items.Add("Position Initial");
Training_Type.Items.Add("Position Recurrent");
Training_Type.Items.Add("Position One Time");
Training_Type.Items.Add("Other");
Alert.Items.Add("");
Alert.Items.Add("Need Initial");
Alert.Items.Add("Need Recurrent");
Division.Items.Clear();
Division.Items.Add("");
string SQL_Div = "select a.Division as Division1 from MSAS_Division a order by a.Division ";
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper1.Conn, CommandType.Text, SQL_Div))
{
while (rdr.Read())
{
Division.Items.Add(Convert.ToString(rdr.GetSqlValue(0)));
}
}
Course.Items.Clear();
Course.Items.Add("");
string SQL_course_d = "select Course from MSAS_Course order by id";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(SqlHelper1.Conn, CommandType.Text, SQL_course_d))
{
while (rdr.Read())
{
Course.Items.Add(Convert.ToString(rdr.GetSqlValue(0)));
}
}
Course_re.Items.Clear();
Course_re.Items.Add("");
string SQL_course_re = "select Course_Ref from MSAS_Course_Reference order by id ";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(SqlHelper1.Conn, CommandType.Text, SQL_course_re))
{
while (rdr.Read())
{
Course_re.Items.Add(Convert.ToString(rdr.GetSqlValue(0)));
}
}
}
else
{
GetData();
}
if (Request.QueryString["Page"] != null)
{
if (Request.QueryString["Staffid"].ToString() != "")
{
Staff_Id.Text = Request.QueryString["Staffid"].ToString();
}
if (Request.QueryString["Station"].ToString() != "")
{
Station.SelectedValue = Request.QueryString["Station"].ToString();
}
if (Request.QueryString["Dept"].ToString() != "")
{
Department.SelectedValue = Request.QueryString["Dept"].ToString();
}
if (Request.QueryString["Div"].ToString() != "")
{
Division.SelectedValue = Request.QueryString["Div"].ToString();
}
if (Request.QueryString["From"].ToString() != "")
{
From.Text = Request.QueryString["From"].ToString();
}
if (Request.QueryString["To"].ToString() != "")
{
To.Text = Request.QueryString["To"].ToString();
}
if (Request.QueryString["Course"].ToString() != "")
{
Course.SelectedValue = Request.QueryString["Course"].ToString();
}
if (Request.QueryString["Course_ref"].ToString() != "")
{
Course_re.SelectedValue = Request.QueryString["Course_ref"].ToString();
}
if (Request.QueryString["Course_Type"].ToString() != "")
{
Training_Type.SelectedValue = Request.QueryString["Course_Type"].ToString();
}
if (Request.QueryString["Class"].ToString() != "")
{
Class.Text = Request.QueryString["Class"].ToString();
}
if (Request.QueryString["alert"].ToString() != "")
{
Alert.SelectedValue = Request.QueryString["alert"].ToString();
}
GetData();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["staffno"] == null)
{
Response.Redirect("Default.aspx");
}
else
{
Label1.Text = "Welcome " + Session["staffname"].ToString();
}
if (!IsPostBack)
{
//Class Type
string SQL = "select id,Course_Ref from MSAS_Course_Reference order by id ";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQL))
{
while (rdr.Read())
{
Course_Ref.Items.Add(Convert.ToString(rdr.GetSqlValue(1)));
}
}
//Training Type
Training_Type.Items.Clear();
Training_Type.Items.Add("Classroom");
Training_Type.Items.Add("Self Study");
Training_Type.Items.Add("OJT");
Training_Type.Items.Add("LEC");
Training_Type.Items.Add("RS");
Training_Type.Items.Add("Other");
id = Session["Class_ID"].ToString();
if (id != "" && id != null)
{
//基本信息
string SQL1 = "select Class_Name,Course_Ref,Batch,Instructor,CONVERT(varchar(16), Training_Date, 20),Training_Time,Training_type,Location,Training_Organization from MSAS_Class where 1=1 and ID='" + id + "'";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQL1))
{
if (rdr.Read())
{
Class_Name.Text = Convert.ToString(rdr.GetSqlValue(0));
Course_Ref.SelectedValue = Convert.ToString(rdr.GetSqlValue(1)).Trim();
Batch.Text = Convert.ToString(rdr.GetSqlValue(2));
Instructor.Text = Convert.ToString(rdr.GetSqlValue(3));
Training_Date.Text = Convert.ToString(rdr.GetSqlValue(4));
if (rdr.IsDBNull(5))
{
Training_Time.Text = "";
}
else
{
Training_Time.Text = Convert.ToString(rdr.GetSqlValue(5));
}
Training_Type.SelectedValue = Convert.ToString(rdr.GetSqlValue(6)).Trim();
Location.Text = Convert.ToString(rdr.GetSqlValue(7));
Training_Organization.Text = Convert.ToString(rdr.GetSqlValue(8));
}
}
}
//attend
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
sqlcmm.CommandText = "select Staff from MSAS_Class_Range where 1=1 and ID='" + id + "' ";
sqlcnn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm);
adapter.Fill(dtranglist);
}
}
}
//查询range
//range
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
string classid = Course_Ref.SelectedItem.Value;
string Course_Type = "";
string sql_Course_big = "SELECT Training_Type from MSAS_Course_Reference where Course_Ref='" + classid + "'";
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sqlstr, CommandType.Text, sql_Course_big))
{
if (rdr.Read())
{
Course_Type = Convert.ToString(rdr.GetSqlValue(0));
}
}
sqlcmm.CommandText = "select C.StaffID,C.StaffName "
+ "from MSAS_HRInfo C ";
if (Course_Type != "Other" && Course_Type != "Position Initial" && Course_Type != "Position One Time" && Course_Type != "General One Time")
{
sqlcmm.CommandText += "join "
+ "(select distinct(S.StaffID) "
+ "from MSAS_Course_Reference A,MSAS_Course_Ref_Range B,MSAS_Position_S S "
+ "where A.ID=B.ID ";
if (classid != "" && classid != null)
{
sqlcmm.CommandText += "and A.Course_Ref='" + classid + "'";
}
sqlcmm.CommandText += "and S.Position=B.Position) D on "
+ "C.StaffID=D.StaffID ";
}
sqlcmm.CommandText += "where 1=1 ";
if (id == "")
{
sqlcmm.CommandText += "and C.HRstatus=0 ";
}
sqlcmm.CommandText += "order by C.StaffID";
sqlcnn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm);
DataTable dt = new DataTable();
adapter.Fill(dt);
this.titlelist.DataSource = dt;
this.titlelist.DataBind();
}
}
}
protected void savedateClick(object sender, EventArgs e)
{
id = Session["Class_ID"].ToString();
if (id != "" && id != null)//修改
{
//更新记录
//基本信息
SqlConnection cn1 = new SqlConnection(sqlstr);//创建数据库连接对象
SqlCommand insertCmd = new SqlCommand("update MSAS_Class set Class_Name=@Class_Name,Course_Ref=@Course_Ref,Batch=@Batch,Instructor=@Instructor,Training_Date=@Training_Date,Training_Time=@Training_Time,Training_type=@Training_type,Location=@Location,Training_Organization=@Training_Organization where ID=@ID", cn1);
insertCmd.Parameters.Add("@ID", SqlDbType.Int);
insertCmd.Parameters.Add("@Class_Name", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Course_Ref", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Batch", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Instructor", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Training_Date", SqlDbType.Date);
insertCmd.Parameters.Add("@Training_Time", SqlDbType.Int);
insertCmd.Parameters.Add("@Training_type", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Location", SqlDbType.VarChar, 100);
insertCmd.Parameters.Add("@Training_Organization", SqlDbType.VarChar, 100);
insertCmd.Parameters["@ID"].Value = id;
insertCmd.Parameters["@Class_Name"].Value = Class_Name.Text;
insertCmd.Parameters["@Course_Ref"].Value = Course_Ref.Text;
insertCmd.Parameters["@Batch"].Value = Batch.Text;
insertCmd.Parameters["@Instructor"].Value = Instructor.Text;
if (Training_Date.Text == "")
{
insertCmd.Parameters["@Training_Date"].Value = "2000.1.1";
}
else
{
insertCmd.Parameters["@Training_Date"].Value = Training_Date.Text;
}
if (Training_Time.Text == "")
{
insertCmd.Parameters["@Training_Time"].Value = DBNull.Value;
}
else
{
insertCmd.Parameters["@Training_Time"].Value = Training_Time.Text;
}
insertCmd.Parameters["@Training_type"].Value = Training_Type.SelectedItem.Value.ToString().Trim();
insertCmd.Parameters["@Location"].Value = Location.Text;
insertCmd.Parameters["@Training_Organization"].Value = Training_Organization.Text;
cn1.Open();
int flag = insertCmd.ExecuteNonQuery();
if (flag > 0)
{
// Page.ClientScript.RegisterStartupScript(Page.GetType(), "msg", "<script type=\"text/javascript\">function ShowAlert(){alert('dd');}window.onload=ShowAlert;</script>");
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert success');</script>");
}
else
{
}
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert fail');</script>");
cn1.Close();
//删除历史记录
using (SqlConnection sqlcnn1 = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm1 = sqlcnn1.CreateCommand())
{
sqlcmm1.CommandText = "Delete MSAS_Class_Range where ID='" + id + "'";
sqlcnn1.Open();
int j = sqlcmm1.ExecuteNonQuery();
sqlcnn1.Close();
if (j > 0)
{
//ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('Delete data success');</script>");
}
else
{
//ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('Delete data faild');</script>");
}
}
}
}
else//新增
{
//生成新id
int currentid = 0;
string SQL = "select isnull(max(id),0) from MSAS_Class ";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQL))
{
if (rdr.Read())
{
currentid = Convert.ToInt32(Convert.ToString(rdr.GetSqlValue(0)));
}
}
int newid = currentid + 1;
id = newid.ToString();
//插入新记录
//基本信息
SqlConnection cn1 = new SqlConnection(sqlstr);//创建数据库连接对象
SqlCommand insertCmd = new SqlCommand("insert into MSAS_Class values(@ID,@Class_Name,@Course_Ref,@Batch,@Instructor,@Training_Date,@Training_Time,@Training_type,@Location,@Training_Organization)", cn1);
insertCmd.Parameters.Add("@ID", SqlDbType.Int);
insertCmd.Parameters.Add("@Class_Name", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Course_Ref", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Batch", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Instructor", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Training_Date", SqlDbType.Date);
insertCmd.Parameters.Add("@Training_Time", SqlDbType.Int);
insertCmd.Parameters.Add("@Training_type", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Location", SqlDbType.VarChar, 100);
insertCmd.Parameters.Add("@Training_Organization", SqlDbType.VarChar, 100);
insertCmd.Parameters["@ID"].Value = newid;
insertCmd.Parameters["@Class_Name"].Value = Class_Name.Text;
insertCmd.Parameters["@Course_Ref"].Value = Course_Ref.Text;
insertCmd.Parameters["@Batch"].Value = Batch.Text;
insertCmd.Parameters["@Instructor"].Value = Instructor.Text;
if (Training_Date.Text == "")
{
insertCmd.Parameters["@Training_Date"].Value = "2000.1.1";
}
else
{
insertCmd.Parameters["@Training_Date"].Value = Training_Date.Text;
}
if (Training_Time.Text == "")
{
insertCmd.Parameters["@Training_Time"].Value = DBNull.Value;
}
else
{
insertCmd.Parameters["@Training_Time"].Value = Training_Time.Text;
}
insertCmd.Parameters["@Training_type"].Value = Training_Type.SelectedItem.Value.ToString().Trim();
insertCmd.Parameters["@Location"].Value = Location.Text;
insertCmd.Parameters["@Training_Organization"].Value = Training_Organization.Text;
cn1.Open();
int flag = insertCmd.ExecuteNonQuery();
if (flag > 0)
{
// Page.ClientScript.RegisterStartupScript(Page.GetType(), "msg", "<script type=\"text/javascript\">function ShowAlert(){alert('dd');}window.onload=ShowAlert;</script>");
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert success');</script>");
}
else
{
}
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert fail');</script>");
cn1.Close();
}
SqlConnection cn2 = new SqlConnection(sqlstr);//创建数据库连接对象
string rangetext = Request.Form["savetxt"];
int flag1;
string[] sArray = rangetext.Split('|');
for (int i = 0; i < sArray.Length; i++)
{
if (sArray[i] != "")
{
string key = sArray[i].Replace("CheckBox", "");
SqlCommand insertCmd1 = new SqlCommand("insert into MSAS_Class_Range values(@ID,@staff)", cn2);
insertCmd1.Parameters.Add("@ID", SqlDbType.Int);
insertCmd1.Parameters.Add("@staff", SqlDbType.VarChar, 50);
insertCmd1.Parameters["@ID"].Value = id;
insertCmd1.Parameters["@staff"].Value = key;
cn2.Open();
flag1 = insertCmd1.ExecuteNonQuery();
if (flag1 > 0)
{
// Page.ClientScript.RegisterStartupScript(Page.GetType(), "msg", "<script type=\"text/javascript\">function ShowAlert(){alert('dd');}window.onload=ShowAlert;</script>");
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert success');</script>");
//ClientScript.RegisterStartupScript(GetType(), "message", "<script>window.open('Course_Setting.aspx','_blank')</script>");
}
else
{
}
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert fail');</script>");
cn2.Close();
}
}
//ClientScript.RegisterStartupScript(GetType(), "message", "<script>window.history.back(-2);</script>");
//Response.Redirect("Class_search.aspx");
ClientScript.RegisterStartupScript(GetType(), "message", "<script>if(confirm('Data saved , press \"OK\" to close this page?')){window.close(); }else{ window.close();}</script>");
//Response.Write("<script>window.close();</script>");
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["staffno"] == null)
{
Response.Redirect("Default.aspx");
}
else
{
Label1.Text = "Welcome " + Session["staffname"].ToString();
}
//下拉框
string SQL_course_d = "select Course from MSAS_Course order by id";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQL_course_d))
{
while (rdr.Read())
{
Course_d.Items.Add(Convert.ToString(rdr.GetSqlValue(0)));
}
}
Training_Unit.Items.Add("Year");
Training_Unit.Items.Add("Month");
Training_Unit.Items.Add("Day");
Training_Type.Items.Add("General Initial");
Training_Type.Items.Add("General Recurrent");
Training_Type.Items.Add("General One Time");
Training_Type.Items.Add("Position Initial");
Training_Type.Items.Add("Position Recurrent");
Training_Type.Items.Add("Position One Time");
Training_Type.Items.Add("Other");
//判断是否是review
id = Session["Course_Ref"].ToString();
if (id != "" && id != null)
{
//基本信息
string SQL = "select Course_Ref,Course,Training_Type,Duation,Traning_Unit from MSAS_Course_Reference where ID='" + id + "'";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQL))
{
if (rdr.Read())
{
Course_Ref_str = Convert.ToString(rdr.GetSqlValue(0));
Course_str = Convert.ToString(rdr.GetSqlValue(1));
Training_Type_str = Convert.ToString(rdr.GetSqlValue(2));
Duation_str = Convert.ToString(rdr.GetSqlValue(3));
Traning_Unit_str = Convert.ToString(rdr.GetSqlValue(4));
}
}
//原来选择的range
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
sqlcmm.CommandText = "select Position from MSAS_Course_Ref_Range where ID='" + id + "' ";
sqlcnn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm);
adapter.Fill(dtranglist);
}
}
}
if (!IsPostBack)
{
Course_Ref_t.Text = Course_Ref_str;
Course_d.SelectedValue = Course_str.Trim();
Training_Type.SelectedValue = Training_Type_str.Trim();
Duation.Text = Duation_str;
Training_Unit.SelectedValue = Traning_Unit_str.Trim();
}
//range
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
sqlcmm.CommandText = "select id,Position from MSAS_Position order by id";
sqlcnn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm);
DataTable dt = new DataTable();
adapter.Fill(dt);
this.titlelist.DataSource = dt;
this.titlelist.DataBind();
}
}
}
protected void savedateClick(object sender, EventArgs e)
{
//主表
if (id != "" && id != null)//更新数据
{
//插入新记录
//基本信息
SqlConnection cn1 = new SqlConnection(sqlstr);//创建数据库连接对象
SqlCommand insertCmd = new SqlCommand("update MSAS_Course_Reference set Course_Ref=@Course_Ref,Course=@Course,Training_Type=@Training_Type,Duation=@Duation,Traning_Unit=@Traning_Unit where id=@ID ", cn1);
insertCmd.Parameters.Add("@ID", SqlDbType.Int);
insertCmd.Parameters.Add("@Course_Ref", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Course", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Training_Type", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Duation", SqlDbType.Int);
insertCmd.Parameters.Add("@Traning_Unit", SqlDbType.VarChar, 50);
insertCmd.Parameters["@ID"].Value = id;
insertCmd.Parameters["@Course"].Value = Course_d.SelectedItem.Value.ToString().Trim();
insertCmd.Parameters["@Course_Ref"].Value = Course_Ref_t.Text;
insertCmd.Parameters["@Training_Type"].Value = Training_Type.SelectedItem.Value.ToString().Trim();
insertCmd.Parameters["@Duation"].Value = Duation.Text;
insertCmd.Parameters["@Traning_Unit"].Value = Training_Unit.SelectedItem.Value.ToString().Trim();
cn1.Open();
int flag = insertCmd.ExecuteNonQuery();
if (flag > 0)
{
// Page.ClientScript.RegisterStartupScript(Page.GetType(), "msg", "<script type=\"text/javascript\">function ShowAlert(){alert('dd');}window.onload=ShowAlert;</script>");
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert success');</script>");
}
else
{
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert fail');</script>");
cn1.Close();
}
}
else//新增数据
{
//生成序号
int currentid = 0;
string SQL = "select isnull(max(id),0) from MSAS_Course_Reference ";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQL))
{
if (rdr.Read())
{
currentid = Convert.ToInt32(Convert.ToString(rdr.GetSqlValue(0)));
}
}
int newid = currentid + 1;
id = Convert.ToString(newid);
//插入新记录
//基本信息
SqlConnection cn1 = new SqlConnection(sqlstr);//创建数据库连接对象
SqlCommand insertCmd = new SqlCommand("insert into MSAS_Course_Reference values(@ID,@Course_Ref,@Course,@Training_Type,@Duation,@Traning_Unit)", cn1);
insertCmd.Parameters.Add("@ID", SqlDbType.Int);
insertCmd.Parameters.Add("@Course_Ref", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Course", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Training_Type", SqlDbType.VarChar, 50);
insertCmd.Parameters.Add("@Duation", SqlDbType.Int);
insertCmd.Parameters.Add("@Traning_Unit", SqlDbType.VarChar, 50);
insertCmd.Parameters["@ID"].Value = newid;
insertCmd.Parameters["@Course"].Value = Course_d.SelectedItem.Value.ToString().Trim();
insertCmd.Parameters["@Course_Ref"].Value = Course_Ref_t.Text;
insertCmd.Parameters["@Training_Type"].Value = Training_Type.SelectedItem.Value.ToString().Trim();
if (Duation.Text == "")
{
insertCmd.Parameters["@Duation"].Value = 0;
}
else
{
insertCmd.Parameters["@Duation"].Value = Duation.Text;
}
insertCmd.Parameters["@Traning_Unit"].Value = Training_Unit.SelectedItem.Value.ToString().Trim();
cn1.Open();
int flag = insertCmd.ExecuteNonQuery();
if (flag > 0)
{
// Page.ClientScript.RegisterStartupScript(Page.GetType(), "msg", "<script type=\"text/javascript\">function ShowAlert(){alert('dd');}window.onload=ShowAlert;</script>");
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert success');</script>");
}
else
{
}
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert fail');</script>");
cn1.Close();
}
//删除range记录
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
sqlcmm.CommandText = "delete MSAS_Course_Ref_Range where ID='" + id + "'";
sqlcnn.Open();
int j = sqlcmm.ExecuteNonQuery();
sqlcnn.Close();
if (j > 0)
{
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('Delete data success');</script>");
}
else
{
//ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('Delete data faild');</script>");
}
}
}
//插入新的range
SqlConnection cn2 = new SqlConnection(sqlstr);//创建数据库连接对象
string rangetext = Request.Form["savetxt"];
int flag1;
string[] sArray = rangetext.Split('|');
for (int i = 0; i < sArray.Length; i++)
{
if (sArray[i] != "")
{
string key = sArray[i].Replace("CheckBox", "");
SqlCommand insertCmd1 = new SqlCommand("insert into MSAS_Course_Ref_Range values(@ID,@Position)", cn2);
insertCmd1.Parameters.Add("@ID", SqlDbType.Int);
insertCmd1.Parameters.Add("@Position", SqlDbType.VarChar, 50);
insertCmd1.Parameters["@ID"].Value = id;
insertCmd1.Parameters["@Position"].Value = key;
cn2.Open();
flag1 = insertCmd1.ExecuteNonQuery();
if (flag1 > 0)
{
// Page.ClientScript.RegisterStartupScript(Page.GetType(), "msg", "<script type=\"text/javascript\">function ShowAlert(){alert('dd');}window.onload=ShowAlert;</script>");
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert success');</script>");
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>window.open('Class_setting.aspx','_blank')</script>");
}
else
{
// ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('New data insert fail');</script>");
}
cn2.Close();
}
}
Response.Redirect("Course_ref_search.aspx");
}
protected void Page_Load(object sender, EventArgs e)
{
string idflag = "";
string jsonText = Request.Form["inputdate1"].ToString();
string[] stringSeparators = new string[] { "||" };
string[] sArray = jsonText.Split(stringSeparators, StringSplitOptions.None);
string Errorcode = "";
string staffid = "";
int dependent_id = 1;
bool Erroroc = false;
//分form数据
for (int i = 0; i < sArray.Length; i++)
{
if (sArray[i] == "")
{
}
else
{
//分键值对
string[] stringKey = new string[] { "#$#" };
string[] Keypair = sArray[i].Split(stringKey, StringSplitOptions.None);
//分类用于数据存储
int typeflag = 0;
List <Keyp> keylist = new List <Keyp>();
for (int j = 0; j < Keypair.Length; j++)
{
if (Keypair[j] != "")
{
//取键值对的 名和值
string[] SepareteNameandVal = new string[] { "^^" };
string[] NameVal = Keypair[j].Split(SepareteNameandVal, StringSplitOptions.None);
Keyp keyNV = new Keyp(NameVal[0].ToString().Trim(), NameVal[1].ToString().Trim());
//获得staffid
if (NameVal[0].ToString().Trim() == "UserStaffNumber")
{
staffid = NameVal[1].ToString().Trim();
}
keylist.Add(keyNV);
if (j == 0)
{
//判断form类型
switch (NameVal[0].ToString().Trim())
{
case "UserName":
typeflag = 1; //个人信息
break;
case "Familyrelationship": //家属
typeflag = 2;
break;
case "Emergencyrelationship": //紧急
typeflag = 3;
break;
default:
typeflag = 0;
break;
}
}
}
}
//根据类型存数据
switch (typeflag)
{
case 0:
break;
case 1:
//检查数据是否已经存在
string SQL = "select 'X' from HR_PIF_StaffList WHERE EMPLID='" + staffid + "' ";
SqlConnection sqlcnn = new SqlConnection(sqlstr);
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlcnn, CommandType.Text, SQL))
{
if (rdr.Read())
{
idflag = Convert.ToString(rdr.GetSqlValue(0));
}
}
if (idflag == "X" || Erroroc)
{
Errorcode = "The Staff ID is already exist"; //有重复数据
break;
}
//插入人员信息
using (SqlConnection sqlcnn1 = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn1.CreateCommand())
{
sqlcmm.CommandText = "INSERT INTO HR_PIF_StaffList values(@emplid,@name,@mar_s,@mar_d,@dl,@st) ";
sqlcmm.Parameters.AddWithValue("@emplid", getval(keylist, "UserStaffNumber"));
sqlcmm.Parameters.AddWithValue("@name", getval(keylist, "UserName"));
if (getval(keylist, "MaritalStatus").ToString() == "Single")
{
sqlcmm.Parameters.AddWithValue("@mar_s", "S");
}
else
{
sqlcmm.Parameters.AddWithValue("@mar_s", "M");
}
sqlcmm.Parameters.AddWithValue("@mar_d", getval(keylist, "MaritalDate"));
sqlcmm.Parameters.AddWithValue("@dl", 0);
sqlcmm.Parameters.AddWithValue("@st", 0);
sqlcnn1.Open();
int d = sqlcmm.ExecuteNonQuery();
if (d > 0)
{
Errorcode = "Insert success.";
}
else
{
Errorcode = "Insert Staff List failure.";
Erroroc = true;
}
}
}
//插入Macau 电话信息
using (SqlConnection sqlcnn2 = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn2.CreateCommand())
{
sqlcmm.CommandText = "INSERT INTO HR_PIF_Phone values(@emplid,@PHONE_TYPE,@PHONE,@EXTENSION,@PREF_PHONE_FLAG) ";
sqlcmm.Parameters.AddWithValue("@emplid", staffid);
sqlcmm.Parameters.AddWithValue("@PHONE_TYPE", "HOME");
sqlcmm.Parameters.AddWithValue("@PHONE", getval(keylist, "PhoneinMacau"));
if (getval(keylist, "MacauExtention") == DBNull.Value)
{
sqlcmm.Parameters.AddWithValue("@EXTENSION", DBNull.Value);
}
else
{
sqlcmm.Parameters.AddWithValue("@EXTENSION", getval(keylist, "MacauExtention").ToString().Substring(0, 6));
}
if (getval(keylist, "MAddressprefered").ToString() == "on")
{
sqlcmm.Parameters.AddWithValue("@PREF_PHONE_FLAG", "Y");
}
else
{
sqlcmm.Parameters.AddWithValue("@PREF_PHONE_FLAG", "N");
}
sqlcnn2.Open();
int d = sqlcmm.ExecuteNonQuery();
if (d > 0)
{
Errorcode = "Insert success.";
}
else
{
Errorcode = "Insert Macau Phone failure.";
Erroroc = true;
}
}
}
//插入HOME 电话信息
using (SqlConnection sqlcnn2 = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn2.CreateCommand())
{
sqlcmm.CommandText = "INSERT INTO HR_PIF_Phone values(@emplid,@PHONE_TYPE,@PHONE,@EXTENSION,@PREF_PHONE_FLAG) ";
sqlcmm.Parameters.AddWithValue("@emplid", staffid);
sqlcmm.Parameters.AddWithValue("@PHONE_TYPE", "OTR");
sqlcmm.Parameters.AddWithValue("@PHONE", getval(keylist, "PhoneinHomebase"));
if (getval(keylist, "HomebaseExtention") == DBNull.Value)
{
sqlcmm.Parameters.AddWithValue("@EXTENSION", DBNull.Value);
}
else
{
sqlcmm.Parameters.AddWithValue("@EXTENSION", getval(keylist, "HomebaseExtention").ToString().Substring(0, 6));
}
if (getval(keylist, "HAddressprefered").ToString() == "on")
{
sqlcmm.Parameters.AddWithValue("@PREF_PHONE_FLAG", "Y");
}
else
{
sqlcmm.Parameters.AddWithValue("@PREF_PHONE_FLAG", "N");
}
sqlcnn2.Open();
int d = sqlcmm.ExecuteNonQuery();
if (d > 0)
{
Errorcode = "Insert success.";
}
else
{
Errorcode = "Insert Macau Phone failure.";
Erroroc = true;
}
}
}
//导入Macau地址信息
using (SqlConnection sqlcnn3 = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn3.CreateCommand())
{
sqlcmm.CommandText = "INSERT INTO HR_PIF_Address values(@emplid,@KEYPROP_ADDRESS_TYPE,@KEYPROP_EFFDT,@COUNTRY,@ADDRESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4) ";
sqlcmm.Parameters.AddWithValue("@emplid", staffid);
sqlcmm.Parameters.AddWithValue("@KEYPROP_ADDRESS_TYPE", "HOME");
sqlcmm.Parameters.AddWithValue("@KEYPROP_EFFDT", DateTime.Now);
sqlcmm.Parameters.AddWithValue("@COUNTRY", "CHN");
string[] result = addressslip(getval(keylist, "AddressinMacau").ToString());
sqlcmm.Parameters.AddWithValue("@ADDRESS1", result[0]);
sqlcmm.Parameters.AddWithValue("@ADDRESS2", result[1]);
sqlcmm.Parameters.AddWithValue("@ADDRESS3", result[2]);
sqlcmm.Parameters.AddWithValue("@ADDRESS4", result[3]);
sqlcnn3.Open();
int d = sqlcmm.ExecuteNonQuery();
if (d > 0)
{
Errorcode = "Insert success.";
}
else
{
Errorcode = "Insert Macau Phone failure.";
Erroroc = true;
}
}
}
//导入Macau地址信息
using (SqlConnection sqlcnn3 = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn3.CreateCommand())
{
sqlcmm.CommandText = "INSERT INTO HR_PIF_Address values(@emplid,@KEYPROP_ADDRESS_TYPE,@KEYPROP_EFFDT,@COUNTRY,@ADDRESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4) ";
sqlcmm.Parameters.AddWithValue("@emplid", staffid);
sqlcmm.Parameters.AddWithValue("@KEYPROP_ADDRESS_TYPE", "OTH");
sqlcmm.Parameters.AddWithValue("@KEYPROP_EFFDT", DateTime.Now);
sqlcmm.Parameters.AddWithValue("@COUNTRY", gettrans(getval(keylist, "CountryinHomebase").ToString(), "COUNTRY").ToString());
string[] result = addressslip(getval(keylist, "AddressinHomebase").ToString());
sqlcmm.Parameters.AddWithValue("@ADDRESS1", result[0]);
sqlcmm.Parameters.AddWithValue("@ADDRESS2", result[1]);
sqlcmm.Parameters.AddWithValue("@ADDRESS3", result[2]);
sqlcmm.Parameters.AddWithValue("@ADDRESS4", result[3]);
sqlcnn3.Open();
int d = sqlcmm.ExecuteNonQuery();
if (d > 0)
{
Errorcode = "Insert success.";
}
else
{
Errorcode = "Insert Macau Phone failure.";
Erroroc = true;
}
}
}
break;
case 2:
if (idflag == "X" || Erroroc)
{
break;
}
//家庭信息
using (SqlConnection sqlcnn3 = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn3.CreateCommand())
{
sqlcmm.CommandText = "INSERT INTO HR_PIF_Dependent "
+ " ([EMPLID],[DEPENDENT_BENEF],[BIRTHDATE],[PHONE],[EFFDT],[COUNTRY_NM_FORMAT],[LAST_NAME],[FIRST_NAME],[NAME_PREFIX],[EFFDT_1],[COUNTRY],"
+ " [ADDRESS1],[ADDRESS2],[ADDRESS3],[ADDRESS4],[CITY],[EFFDT_3],[RELATIONSHIP],[MAR_STATUS],[SEX],[GC_DEP_COMMENT],[GC_DEP_PRMCONTACT])"
+ " values(@emplid,@DEPENDENT_BENEF,@BIRTHDATE,@PHONE,@EFFDT,@COUNTRY_NM_FORMAT,@LAST_NAME,@FIRST_NAME,@NAME_PREFIX,@EFFDT_1,@COUNTRY"
+ " ,@ADDRESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY,@EFFDT_3,@RELATIONSHIP,@MAR_STATUS,@SEX,@GC_DEP_COMMENT,@GC_DEP_PRMCONTACT) ";
sqlcmm.Parameters.AddWithValue("@emplid", staffid);
string did = "";
if (dependent_id < 10)
{
did = "0" + dependent_id.ToString();
}
else
{
did = dependent_id.ToString();
}
sqlcmm.Parameters.AddWithValue("@DEPENDENT_BENEF", did);
sqlcmm.Parameters.AddWithValue("@BIRTHDATE", getval(keylist, "FamilyDateofBirth"));
sqlcmm.Parameters.AddWithValue("@PHONE", getval(keylist, "FamilyPhone"));
sqlcmm.Parameters.AddWithValue("@EFFDT", DateTime.Now.ToShortDateString());
if (getval(keylist, "FamilyNameType").ToString() == "Chinese")
{
sqlcmm.Parameters.AddWithValue("@COUNTRY_NM_FORMAT", "CHN");
}
else
{
sqlcmm.Parameters.AddWithValue("@COUNTRY_NM_FORMAT", "ENG");
}
sqlcmm.Parameters.AddWithValue("@LAST_NAME", getval(keylist, "RelationshipLastname"));
sqlcmm.Parameters.AddWithValue("@FIRST_NAME", getval(keylist, "RelationshipFirstname"));
if (getval(keylist, "FamilySex").ToString() == "Male")
{
sqlcmm.Parameters.AddWithValue("@NAME_PREFIX", "Mr");
sqlcmm.Parameters.AddWithValue("@SEX", "M");
}
else
{
sqlcmm.Parameters.AddWithValue("@NAME_PREFIX", "Ms");
sqlcmm.Parameters.AddWithValue("@SEX", "F");
}
sqlcmm.Parameters.AddWithValue("@EFFDT_1", DateTime.Now);
sqlcmm.Parameters.AddWithValue("@COUNTRY", gettrans(getval(keylist, "FamilyCountry").ToString(), "COUNTRY").ToString());
string[] result = addressslip(getval(keylist, "FamilyAddress").ToString());
sqlcmm.Parameters.AddWithValue("@ADDRESS1", result[0]);
sqlcmm.Parameters.AddWithValue("@ADDRESS2", result[1]);
sqlcmm.Parameters.AddWithValue("@ADDRESS3", result[2]);
sqlcmm.Parameters.AddWithValue("@ADDRESS4", result[3]);
sqlcmm.Parameters.AddWithValue("@CITY", getval(keylist, "FamilyCity").ToString());
sqlcmm.Parameters.AddWithValue("@EFFDT_3", DateTime.Now);
sqlcmm.Parameters.AddWithValue("@RELATIONSHIP", gettrans(getval(keylist, "Familyrelationship").ToString(), "FAMILY").ToString());
if (getval(keylist, "FMaritalStatus").ToString() == "Single")
{
sqlcmm.Parameters.AddWithValue("@MAR_STATUS", "S");
}
else
{
sqlcmm.Parameters.AddWithValue("@MAR_STATUS", "M");
}
sqlcmm.Parameters.AddWithValue("@GC_DEP_COMMENT", getval(keylist, "FamilyOccupation"));
if (dependent_id == 0)
{
sqlcmm.Parameters.AddWithValue("@GC_DEP_PRMCONTACT", "Y");
}
else
{
sqlcmm.Parameters.AddWithValue("@GC_DEP_PRMCONTACT", "N");
}
sqlcnn3.Open();
int d = sqlcmm.ExecuteNonQuery();
if (d > 0)
{
Errorcode = "Insert success.";
}
else
{
Errorcode = "Insert Family " + dependent_id + " failure.";
Erroroc = true;
}
}
}
dependent_id++;
break;
case 3:
if (idflag == "X" || Erroroc)
{
break;
}
//紧急联系人信息
using (SqlConnection sqlcnn3 = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn3.CreateCommand())
{
sqlcmm.CommandText = "INSERT INTO HR_PIF_Dependent "
+ " ([EMPLID],[DEPENDENT_BENEF],[PHONE],[EFFDT],[COUNTRY_NM_FORMAT],[LAST_NAME],[FIRST_NAME],[NAME_PREFIX],[EFFDT_1],[COUNTRY],"
+ " [ADDRESS1],[ADDRESS2],[ADDRESS3],[ADDRESS4],[CITY],[EFFDT_3],[RELATIONSHIP],[MAR_STATUS],[SEX],[GC_DEP_PRMCONTACT],[GC_DEP_EMG_CONTACT])"
+ " values(@emplid,@DEPENDENT_BENEF,@PHONE,@EFFDT,@COUNTRY_NM_FORMAT,@LAST_NAME,@FIRST_NAME,@NAME_PREFIX,@EFFDT_1,@COUNTRY"
+ " ,@ADDRESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY,@EFFDT_3,@RELATIONSHIP,@MAR_STATUS,@SEX,@GC_DEP_PRMCONTACT,@GC_DEP_EMG_CONTACT) ";
sqlcmm.Parameters.AddWithValue("@emplid", staffid);
sqlcmm.Parameters.AddWithValue("@DEPENDENT_BENEF", dependent_id);
sqlcmm.Parameters.AddWithValue("@PHONE", getval(keylist, "EmergencyPhone").ToString());
sqlcmm.Parameters.AddWithValue("@EFFDT", DateTime.Now);
if (getval(keylist, "EmergencyNameType").ToString() == "Chinese")
{
sqlcmm.Parameters.AddWithValue("@COUNTRY_NM_FORMAT", "CHN");
}
else
{
sqlcmm.Parameters.AddWithValue("@COUNTRY_NM_FORMAT", "ENG");
}
sqlcmm.Parameters.AddWithValue("@LAST_NAME", getval(keylist, "EmergencyLastName").ToString());
sqlcmm.Parameters.AddWithValue("@FIRST_NAME", getval(keylist, "EmergencyFirstName").ToString());
if (getval(keylist, "EmergencySex").ToString() == "Male")
{
sqlcmm.Parameters.AddWithValue("@NAME_PREFIX", "Mr");
sqlcmm.Parameters.AddWithValue("@SEX", "M");
}
else
{
sqlcmm.Parameters.AddWithValue("@NAME_PREFIX", "Ms");
sqlcmm.Parameters.AddWithValue("@SEX", "F");
}
sqlcmm.Parameters.AddWithValue("@EFFDT_1", DateTime.Now);
sqlcmm.Parameters.AddWithValue("@COUNTRY", gettrans(getval(keylist, "EmergencyCountry").ToString(), "COUNTRY").ToString());
string[] result = addressslip(getval(keylist, "EmergencyAddress").ToString());
sqlcmm.Parameters.AddWithValue("@ADDRESS1", result[0]);
sqlcmm.Parameters.AddWithValue("@ADDRESS2", result[1]);
sqlcmm.Parameters.AddWithValue("@ADDRESS3", result[2]);
sqlcmm.Parameters.AddWithValue("@ADDRESS4", result[3]);
sqlcmm.Parameters.AddWithValue("@CITY", getval(keylist, "EmergencyCity").ToString());
sqlcmm.Parameters.AddWithValue("@EFFDT_3", DateTime.Now);
sqlcmm.Parameters.AddWithValue("@RELATIONSHIP", gettrans(getval(keylist, "Emergencyrelationship").ToString(), "RELATION").ToString());
if (getval(keylist, "EMaritalStatus").ToString() == "Single")
{
sqlcmm.Parameters.AddWithValue("@MAR_STATUS", "S");
}
else
{
sqlcmm.Parameters.AddWithValue("@MAR_STATUS", "M");
}
sqlcmm.Parameters.AddWithValue("@GC_DEP_PRMCONTACT", "N");
sqlcmm.Parameters.AddWithValue("@GC_DEP_EMG_CONTACT", "Y");
sqlcnn3.Open();
int d = sqlcmm.ExecuteNonQuery();
if (d > 0)
{
Errorcode = "Insert success.";
}
else
{
Errorcode = "Insert Emergency" + dependent_id + " failure.";
Erroroc = true;
}
}
}
dependent_id++;
break;
default:
if (idflag == "X" || Erroroc)
{
// Errorcode = "The Staff ID is already exist";//有重复数据
break;
}
break;
}
}
}
Response.Write(Errorcode);
}
public Resource_GetResListDAL(SqlHelper1 db) : base(db)
{
}
/// <summary>
/// 把资源库存储过程的结果插入本地表Transer_Resource3中
/// </summary>
/// <returns></returns>
public int GetResListByTime()
{
SqlHelper1 sh = new SqlHelper1();
sh.Open();
Resource_GetResListDAL dal = new Resource_GetResListDAL(sh);
SqlHelper db = new SqlHelper();
db.Open();
Transer_Resource2DAL trdal = new Transer_Resource2DAL(db);
DeleteOldDAL dod = new DeleteOldDAL(db);
dod.DeleteResList();
int pageIndex = 1;
int size = 5001;
int res = 0;
try
{
while (size >= 5000)
{
DataTable dt = dal.GetResListByTime(pageIndex);
size = dt.Rows.Count;
foreach (DataRow row in dt.Rows)
{
Transer_Resource2Model model = new Transer_Resource2Model();
model._RESOURCE_ID = row.Value("RESOURCE_ID", "");
model._RESOURCE_NAME = row.Value("RESOURCE_NAME", "");
model._RESOURCE_TYPE = row.Value("RESOURCE_TYPE", 0.00M);
model._RESOURCE_LEVEL = row.Value("RESOURCE_LEVEL", "");
model._STORE_DATE = row.Value("STORE_DATE", DateTime.Now);
model._THEME_CODE = row.Value("THEME_CODE", "");
model._THEME_TEXT = row.Value("THEME_TEXT", "");
model._IMPOR_KN_CODE = row.Value("IMPOR_KN_CODE", "");
model._IMPOR_KN_TEXT = row.Value("IMPOR_KN_TEXT", "");
model._MAIN_KN_CODE = row.Value("MAIN_KN_CODE", "");
model._MAIN_KN_TEXT = row.Value("MAIN_KN_TEXT", "");
model._UNIT_NUM = row.Value("UNIT_NUM", 0.00M);
model._RESOURCE_SIZE = row.Value("RESOURCE_SIZE", 0.00M);
model._ABANDON_NUM = row.Value("ABANDON_NUM", "");
model._APPLY_TOTAL_TIME = row.Value("APPLY_TOTAL_TIME", 0.00M);
model._ABANDON_RATE = row.Value("ABANDON_RATE", "");
model._DOWNLOAD_NUM = row.Value("DOWNLOAD_NUM", "");
model._SOURCE_LIBRARY = row.Value("SOURCE_LIBRARY", "");
model._RESOURCE_CLASS = row.Value("RESOURCE_CLASS", "");
model._MD5_CODE = row.Value("MD5_CODE", "");
model._INSTITU_UNIT = row.Value("INSTITU_UNIT", "");
model._RES_LENGTH = row.Value("RES_LENGTH", 0.00M);
model._DURATION_LENGTH = row.Value("DURATION_LENGTH", 0.00M);
model._FILE_PATH = row.Value("FILE_PATH", "");
model._FILE_CONTENT = row.Value("FILE_CONTENT", "");
model._IS_EXSIT_MEDIA = row.Value("IS_EXSIT_MEDIA", 0.00M);
model._DOWNLOAD_FLAG = row.Value("DOWNLOAD_FLAG", 0.00M);
model._SEQUENCE = row.Value("SEQUENCE", 0.00M);
model._HEAT_NUM = row.Value("HEAT_NUM", 0.00M);
model._SUBJECT_CODE = row.Value("SUBJECT_CODE", "");
res += trdal.AddResListByTime(model);
}
pageIndex++;
}
}
catch (Exception ex)
{
return(-1); //内部报错
//日志
}
db.Close();
return(res);
}
//ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
staffid = Request.QueryString["staffid"];
//stafflist
string SQLSTAFFLIST = "SELECT * FROM [CSD].[dbo].[HR_PIF_StaffList] where EMPLID='" + staffid + "' ";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQLSTAFFLIST))
{
if (rdr.Read())
{
username = Convert.ToString(rdr.GetSqlValue(1));
if (Convert.ToString(rdr.GetSqlValue(2)).ToString() == "S")
{
MarS = "Single";
}
else
{
MarS = "Married";
}
if (rdr.GetSqlValue(3) == DBNull.Value)
{
MarD = "";
}
else
{
MarD = Convert.ToDateTime(rdr.GetSqlValue(3)).ToShortDateString();
}
}
}
//macau phone
string SQLmacauphone = "SELECT * FROM [CSD].[dbo].[HR_PIF_Phone] where EMPLID='" + staffid + "' and PHONE_TYPE='HOME' ";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQLmacauphone))
{
if (rdr.Read())
{
if (Convert.ToString(rdr.GetSqlValue(2)) == "Null")
{
Macauphone = "";
}
else
{
Macauphone = Convert.ToString(rdr.GetSqlValue(2));
}
if (Convert.ToString(rdr.GetSqlValue(3)) == "Null")
{
Macauexention = "";
}
else
{
Macauexention = Convert.ToString(rdr.GetSqlValue(3));
}
if (Convert.ToString(rdr.GetSqlValue(4)) == "Y")
{
prefermacau.Checked = true;
}
}
}
//home phone
string SQLhomephone = "SELECT * FROM [CSD].[dbo].[HR_PIF_Phone] where EMPLID='" + staffid + "' and PHONE_TYPE='OTR' ";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQLhomephone))
{
if (rdr.Read())
{
if (Convert.ToString(rdr.GetSqlValue(2)) == "Null")
{
Homephone = "";
}
else
{
Homephone = Convert.ToString(rdr.GetSqlValue(2));
}
if (Convert.ToString(rdr.GetSqlValue(3)) == "Null")
{
Homeexention = "";
}
else
{
Homeexention = Convert.ToString(rdr.GetSqlValue(3));
}
if (Convert.ToString(rdr.GetSqlValue(4)) == "Y")
{
preferhombase.Checked = true;
}
}
}
//macau address
string SQLmacauadd = "SELECT * FROM [CSD].[dbo].[HR_PIF_Address] where EMPLID='" + staffid + "' and KEYPROP_ADDRESS_TYPE='HOME' ";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQLmacauadd))
{
if (rdr.Read())
{
Macauadd = Convert.ToString(rdr.GetSqlValue(4)) + " "
+ Convert.ToString(rdr.GetSqlValue(5)) + " "
+ Convert.ToString(rdr.GetSqlValue(6)) + " "
+ Convert.ToString(rdr.GetSqlValue(7));
}
}
string SQLhomeadd = "SELECT * FROM [CSD].[dbo].[HR_PIF_Address] where EMPLID='" + staffid + "' and KEYPROP_ADDRESS_TYPE='OTH' ";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQLhomeadd))
{
if (rdr.Read())
{
Homeadd = Convert.ToString(rdr.GetSqlValue(4)) + " "
+ Convert.ToString(rdr.GetSqlValue(5)) + " "
+ Convert.ToString(rdr.GetSqlValue(6)) + " "
+ Convert.ToString(rdr.GetSqlValue(7));
HomeCountry = gettrans(Convert.ToString(rdr.GetSqlValue(3)), "COUNTRY").ToString();
}
}
//家庭信息
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
string SQL = "SELECT * FROM [CSD].[dbo].[HR_PIF_Dependent] where EMPLID='" + staffid + "' and GC_DEP_EMG_CONTACT is null";
sqlcmm.CommandText = SQL;
sqlcnn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm);
DataTable dt1 = new DataTable();
adapter.Fill(dt1);
this.family.DataSource = dt1;
this.family.DataBind();
}
}
//EM信息
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
string SQL1 = "SELECT * FROM [CSD].[dbo].[HR_PIF_Dependent] where EMPLID='" + staffid + "' and GC_DEP_EMG_CONTACT ='Y'";
sqlcmm.CommandText = SQL1;
sqlcnn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm);
DataTable dt2 = new DataTable();
adapter.Fill(dt2);
this.Emergency.DataSource = dt2;
this.Emergency.DataBind();
}
}
}
public string resultsty(int i)
{
string ret_s = "";
string rowspan = "1";
string position = alllist[i - 1].ToString();
//check before
bool beforeflag = checkposition(beforelist, position);
string beforeback = "";
if (beforeflag)
{
beforeback = "style=\"background-color:#CAF7B7\"";
}
//check after
bool afterflag = checkposition(afterlist, position);
string afterback = "";
if (afterflag)
{
afterback = "style=\"background-color:#CAF7B7\"";
}
string falses = "<i class=\"fa fa-times\" aria-hidden=\"true\"></i>";
string truee = "<i class=\"fa fa-check\" aria-hidden=\"true\"></i>";
string falseback = "style=\"background-color:#EA7171\"";
ret_s = ret_s + "<tr>";
string sqlcount = "SELECT count(*) FROM MSAS_Course_VW where Position='" + position + "' and Training_Type <>'Other'";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, sqlcount))
{
if (rdr.Read())
{
rowspan = Convert.ToString(rdr.GetSqlValue(0));
}
}
string sqldetail = "SELECT Course_Ref FROM MSAS_Course_VW where Position='" + position + "' and Training_Type <>'Other'";
ret_s = ret_s + "<td class=\"td10\" rowspan=\"" + rowspan + "\" " + beforeback + ">" + position + "</td>";
string firstline = "";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, sqldetail))
{
if (rdr.Read())
{
firstline = Convert.ToString(rdr.GetSqlValue(0));
}
}
ret_s = ret_s + "<td class=\"td20\" " + beforeback + ">" + firstline + "</td>";
if (beforeflag)
{
string result = checkclass(userstring, firstline);
if (result == "OK")
{
ret_s = ret_s + "<td class=\"td20\" " + beforeback + ">" + truee + "</td>";
}
else
{
ret_s = ret_s + "<td class=\"td20\" " + falseback + ">" + falses + result + "</td>";
}
}
else
{
ret_s = ret_s + "<td class=\"td20\" " + beforeback + ">" + "</td>";
}
ret_s = ret_s + "<td class=\"td10\" rowspan=\"" + rowspan + "\" " + afterback + ">" + position + "</td>";
ret_s = ret_s + "<td class=\"td20\" " + afterback + ">" + firstline + "</td>";
if (afterflag)
{
string result = checkclass(userstring, firstline);
if (result == "OK")
{
ret_s = ret_s + "<td class=\"td20\" " + afterback + ">" + truee + "</td>";
}
else
{
ret_s = ret_s + "<td class=\"td20\" " + falseback + ">" + falses + result + "</td>";
}
}
else
{
ret_s = ret_s + "<td class=\"td20\" " + afterback + ">" + "</td>";
}
ret_s = ret_s + "</tr>";
int linecount = 1;
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, sqldetail))
{
while (rdr.Read())
{
if (linecount != 1)
{
ret_s = ret_s + "<tr>";
ret_s = ret_s + "<td class=\"td20\" " + beforeback + ">" + Convert.ToString(rdr.GetSqlValue(0)) + "</td>";
if (beforeflag)
{
string result = checkclass(userstring, Convert.ToString(rdr.GetSqlValue(0)));
if (result == "OK")
{
ret_s = ret_s + "<td class=\"td20\" " + beforeback + ">" + truee + "</td>";
}
else
{
ret_s = ret_s + "<td class=\"td20\" " + falseback + ">" + falses + result + "</td>";
}
}
else
{
ret_s = ret_s + "<td class=\"td20\" " + beforeback + ">" + "</td>";
}
ret_s = ret_s + "<td class=\"td10\" " + afterback + ">" + Convert.ToString(rdr.GetSqlValue(0)) + "</td>";
if (afterflag)
{
string result = checkclass(userstring, Convert.ToString(rdr.GetSqlValue(0)));
if (result == "OK")
{
ret_s = ret_s + "<td class=\"td20\" " + afterback + ">" + truee + "</td>";
}
else
{
ret_s = ret_s + "<td class=\"td20\" " + falseback + ">" + falses + result + "</td>";
}
}
else
{
ret_s = ret_s + "<td class=\"td20\" " + afterback + ">" + "</td>";
}
ret_s = ret_s + "</tr>";
}
linecount++;
}
}
return(ret_s);
}
public string checkclass(string staffid, string course_ref)
{
string ret = "";
//查询reference信息
string Course = "";
string Training_Type = "";
int Duation = 0;
string Traning_Unit = "";
string sqlreference = " select Course,Training_Type,Duation,Traning_Unit from MSAS_Course_Reference where Course_Ref='" + course_ref + "' ";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, sqlreference))
{
if (rdr.Read())
{
Course = Convert.ToString(rdr.GetSqlValue(0));
Training_Type = Convert.ToString(rdr.GetSqlValue(1));
Duation = Convert.ToInt32(Convert.ToString(rdr.GetSqlValue(2)));
Traning_Unit = Convert.ToString(rdr.GetSqlValue(3));
}
}
if (Training_Type == "Initial")//如果是初训
{
string SQL_int = "SELECT* from MSAS_Position_Gap_Class_VW B where B.staff='" + staffid + "' and B.Course='" + Course + "'"
+ " and Training_Date=(select max(A.Training_Date) from MSAS_Position_Gap_Class_VW A where A.Staff=B.Staff and A.Course=B.Course )";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQL_int))
{
if (rdr.Read())
{
ret = "OK";
}
else
{
ret = "No Records";
}
}
}
else if (Training_Type == "Recurrent")
{
string SQL_int = "SELECT B.* from MSAS_Position_Gap_Class_VW B where B.staff='" + staffid + "' and B.Course='" + Course + "'"
+ " and Training_Date=(select max(A.Training_Date) from MSAS_Position_Gap_Class_VW A where A.Staff=B.Staff and A.Course=B.Course )";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQL_int))
{
if (rdr.Read())
{
DateTime Training_Date = Convert.ToDateTime(rdr.GetSqlValue(5));
DateTime Training_Required_Date = DateTime.Now;
switch (Traning_Unit)
{
case "Day":
Training_Required_Date = Training_Date.AddDays(Duation);
break;
case "Month":
Training_Required_Date = Training_Date.AddMonths(Duation);
break;
case "Year":
Training_Required_Date = Training_Date.AddYears(Duation);
break;
}
if (Training_Required_Date < DateTime.Now)
{
ret = Training_Date.ToString("dd-MMM-yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo);
}
else
{
ret = "OK";
}
}
else
{
ret = "No Records";
}
}
}
else
{
string SQL_int = "SELECT* from MSAS_Position_Gap_Class_VW B where B.staff='" + staffid + "' and B.Course_Ref='" + course_ref + "'"
+ " and Training_Date=(select max(A.Training_Date) from MSAS_Position_Gap_Class_VW A where A.Staff=B.Staff and A.Course_Ref=B.Course_Ref )";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(sqlstr, CommandType.Text, SQL_int))
{
if (rdr.Read())
{
ret = "OK";
}
else
{
ret = "No Records";
}
}
}
return(ret);
}
protected void Submit_Click(object sender, EventArgs e)
{
string uid = Request.Form["lgnm"];
string pwd = Request.Form["pwd"];
string AccessRightSqlstr = "select a.* from ACARS_User a where username ='******' and password = '******'";
DataSet dscheck = SqlHelper.ExecuteDataset(SqlHelper.NX_APPconnectionString, CommandType.Text, AccessRightSqlstr);
if (dscheck.Tables[0].Rows.Count > 0)
{
string IPaddress = HttpContext.Current.Request.UserHostAddress.ToString();
string LoginWay = "";
//if (Equals(IPaddress.Substring(0, 7), "192.168") || Equals(IPaddress, "::1"))
//{
// LoginWay = "Intranet";
//}
//else
//{
// LoginWay = "Extranet";
//}
string sqlstr = "select a.* from ACARS_User a where username ='******' and password = '******'";
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.NX_APPconnectionString, CommandType.Text, sqlstr);
string insertlog = "";
if (ds.Tables[0].Rows.Count > 0)
{
Session["staffno"] = ds.Tables[0].Rows[0]["username"].ToString().Trim();
Session["staffname"] = (Convert.ToString(ds.Tables[0].Rows[0]["SURNAME"]) + " " + Convert.ToString(ds.Tables[0].Rows[0]["FORENAME"]) + "," + Convert.ToString(ds.Tables[0].Rows[0]["PREFERRED"])).TrimEnd(',');
insertlog = "EXEC CreatUserLoginLog '" + ds.Tables[0].Rows[0]["username"].ToString().Trim() + "',"
+ "'" + IPaddress + "','','" + LoginWay + "'";
int result = SqlHelper.ExecuteNonQuery(SqlHelper.NX_APPconnectionString, CommandType.Text, insertlog);
string Location = ds.Tables[0].Rows[0]["Location"].ToString().Trim();
//获取权限
string SQL_admin = "select MAD.Admin_Level from MSAS_Admin MAD"
+ " where MAD.UserID ='" + ds.Tables[0].Rows[0]["username"].ToString().Trim() + "'";
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(SqlHelper1.Conn, CommandType.Text, SQL_admin))
{
if (rdr.Read())
{
//如果有查询结果查出来的值等于权限
Session["Admin_Level"] = rdr["Admin_Level"].ToString().Trim();
}
else
{
//查不到就是0
Session["Admin_Level"] = "0";
}
}
//string str2 = Location.Substring(0,3);
//Session["Loca"] = str2;
Server.Transfer("Main.aspx");
}
else
{
ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('Login failed! Unauthorized access, please contact Administrator');</script>");
}
}
else
{
ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('Login failed! Please check your Staff NO. and Password.');</script>");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["userNo"] == null)
{
Response.Redirect("AuthorizationSelect.aspx");
}
string StaffID = Session["userNo"].ToString();
//personal info
string SQL_name = "select StaffName,License,(DATENAME(dd,a.LicenseExpired)+'-'+SUBSTRING(DATENAME(mm,a.LicenseExpired),0,4)+'-'+DATENAME(yyyy,a.LicenseExpired)),Category "
+ " from MSAS_HRInfo a"
+ " where StaffID='" + StaffID + "'"
+ " and HRstatus='0'";//0是在职 ，1是离职
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(SqlHelper1.Conn, CommandType.Text, SQL_name))
{
if (rdr.Read())
{
string a = Convert.ToString(rdr.GetSqlValue(1));
string b = Convert.ToString(rdr.GetSqlValue(2));
if (Convert.ToString(rdr.GetSqlValue(1)) != "")
{
Label1.Text = "MAR-66 " + Convert.ToString(rdr.GetSqlValue(1)); //license
Label4.Text = "MAR-66 " + Convert.ToString(rdr.GetSqlValue(1)); //license
}
else
{
Label1.Text = "N/A"; //license
Label4.Text = "N/A"; //license
}
if (Convert.ToString(rdr.GetSqlValue(2)) != "Null")
{
Label2.Text = Convert.ToString(rdr.GetSqlValue(2)); //LicenseExpired
Label3.Text = Convert.ToString(rdr.GetSqlValue(2)); //LicenseExpired
}
else
{
Label2.Text = "N/A"; //LicenseExpired
Label3.Text = "N/A"; //LicenseExpired
}
name.Text = Convert.ToString(rdr.GetSqlValue(0)); //name
name1.Text = Convert.ToString(rdr.GetSqlValue(0)); //name
if (Convert.ToString(rdr.GetSqlValue(3)) != "")
{
Label5.Text = Convert.ToString(rdr.GetSqlValue(3)); //Category
Label9.Text = Convert.ToString(rdr.GetSqlValue(3)); //Category
}
else
{
Label5.Text = "N/A"; //Category
Label9.Text = "N/A"; //Category
}
//Label2.Text = Convert.ToString(rdr.GetSqlValue(2));//LicenseExpired
//Label3.Text = Convert.ToString(rdr.GetSqlValue(2));//LicenseExpired
staffid.Text = "AM" + StaffID; //staffid
staffid1.Text = "AM" + StaffID; //staffid
}
}
//Authorization info
string SQL_Auth = "select Project,Range,Level, isnull(stamp,''),replace(Convert(varchar(11),ExpireDate,106 ),' ','-') "
+ " from MSAS_AuthorizationList "
+ " where StaffID='" + StaffID + "'"
+ " AND Vaild ='1'" //有效
+ " and Status<>'2'"; //2是删除了的数据
DataTable dt = new DataTable();
dt.Columns.Add("Project", typeof(string));
dt.Columns.Add("Range", typeof(string));
dt.Columns.Add("Level", typeof(string));
dt.Columns.Add("Stamp", typeof(string));
dt.Columns.Add("ExpireDate", typeof(string));
using (SqlDataReader rdr = SqlHelper1.ExecuteReader(SqlHelper1.Conn, CommandType.Text, SQL_Auth))
{
while (rdr.Read())
{
DataRow row = dt.NewRow();
row["Project"] = Convert.ToString(rdr.GetSqlValue(0)); //Project
row["Range"] = Convert.ToString(rdr.GetSqlValue(1)); //Range
row["Level"] = Convert.ToString(rdr.GetSqlValue(2)); //Level
row["Stamp"] = Convert.ToString(rdr.GetSqlValue(3)); //Stamp
row["ExpireDate"] = Convert.ToString(rdr.GetSqlValue(4)); //ExpireDate
dt.Rows.Add(row);
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
GridView2.DataSource = dt;
GridView2.DataBind();
}
