private Boolean SaveData()
{
string sql;
int Group = Int16.Parse(ddlGroup.SelectedValue);
class_is.dbconfig db = new class_is.dbconfig();
if (deleteAllPermission(Group))
{
for (int i = 1; i <= Int32.Parse(hdCountLine.Value); i++)
{
//HtmlInputCheckBox chk = (HtmlInputCheckBox)this.Master.FindControl("ContentPlaceHolder1").FindControl("Chk_" + i);
CheckBox chkTest = (CheckBox)tbList.FindControl("Chk_" + i);
if (chkTest != null)
{
if (chkTest.Checked == true)
{
sql = "insert [GROUP_MENU_PERMISSION] ([GROUP_ID],[MENU_ID_PERMISSION]) VALUES ";
sql += "(" + Group + "," + i + ")";
if (db.ExecuteSQL(sql) == false)
{
return(false);
}
}
}
;
}
}
return(true);
}
private Boolean SaveData()
{
SqlConnection conn;
SqlCommand command;
string sql;
DataSet ds;
class_is.dbconfig db = new class_is.dbconfig();
//config = db.config();
conn = new SqlConnection(db.config());
sql = "INSERT INTO [IS].[dbo].[USER] ";
sql += "(USER_NAME,PASSWORD,USER_GROUP,LINE_ID,FIRST_NAME,LAST_NAME,USER_CODE,DEPARTMENT,EMAIL) ";
sql += "VALUES ( ";
sql += "'" + txtUserName.Text + "'";
sql += "'Password'";
sql += "'" + ddlGroup.SelectedValue + "'";
sql += "'" + txtLine.Text + "'";
sql += "'" + txtFName.Text + "'";
sql += "'" + txtLName.Text + "'";
sql += "'" + txtEmpID.Text + "'";
sql += "'" + ddlDep.SelectedValue + "'";
sql += "'" + txtEmail.Text + "'";
sql += ")";
if (db.ExecuteSQL(sql))
{
return(true);
}
else
{
return(false);
}
}
private void GetData()
{
string sql;
class_is.dbconfig db = new class_is.dbconfig();
DataSet ds;
sql = "select * ";
sql += "from [REQUEST_LEAVE] ";
sql += "where LEAVE_ID = '" + Request.QueryString["Req"] + "'";
ds = db.getData(sql);
if (ds.Tables[0].Rows.Count > 0)
{
DateTime _FDateLeave = Convert.ToDateTime(ds.Tables[0].Rows[0]["FROM_LEAVE_DATE"]);
TimeSpan _FTimeLeave = TimeSpan.Parse(ds.Tables[0].Rows[0]["FROM_LEAVE_TIME"].ToString());
DateTime _TDateLeave = Convert.ToDateTime(ds.Tables[0].Rows[0]["TO_LEAVE_DATE"]);
TimeSpan _TTimeLeave = TimeSpan.Parse(ds.Tables[0].Rows[0]["TO_LEAVE_TIME"].ToString());
ddlLeaveType.SelectedValue = ds.Tables[0].Rows[0]["LEAVETYPE_ID"].ToString();
txtFDateLeave.Text = _FDateLeave.ToString("dd/MM/yyyy");
txtFTimeLeave.Text = _FTimeLeave.ToString(@"hh\:mm");
txtTDateLeave.Text = _TDateLeave.ToString("dd/MM/yyyy");
txtTTimeLeave.Text = _TTimeLeave.ToString(@"hh\:mm");
txtDay.Text = ds.Tables[0].Rows[0]["NO_LEAVE"].ToString();
txtHour.Text = ds.Tables[0].Rows[0]["NO_LEAVE_HOUR"].ToString();
txtCauseleave.Text = ds.Tables[0].Rows[0]["NOTE"].ToString();
txtContact.Text = ds.Tables[0].Rows[0]["CONTACT"].ToString();
txtTelContact.Text = ds.Tables[0].Rows[0]["CONTACT_TEL"].ToString();
hdftime.Value = txtFTimeLeave.Text;
hdttime.Value = txtTTimeLeave.Text;
}
}
private Boolean CreateGroupPermission()
{
string sql;
class_is.dbconfig db = new class_is.dbconfig();
DataSet ds = new DataSet();
int idGroup;
sql = "INSERT [USER_GROUP] (GroupName) VALUES ('" + txtAddGroup.Text + "') ";
//sql += "select SCOPE_IDENTITY() ";
db.ExecuteSQL(sql);
sql = "SELECT IDENT_CURRENT('[USER_GROUP]') ";
ds = db.getData(sql);
idGroup = int.Parse(ds.Tables[0].Rows[0][0].ToString());
for (int i = 1; i <= Int32.Parse(hdCountLine.Value); i++)
{
CheckBox chk = (CheckBox)tbList.FindControl("Chk_" + i);
if (chk != null)
{
if (chk.Checked == true)
{
sql = "insert [GROUP_MENU_PERMISSION] ([GROUP_ID],[MENU_ID_PERMISSION]) VALUES ";
sql += "(" + idGroup + "," + i + ")";
if (db.ExecuteSQL(sql) == false)
{
return(false);
}
}
}
;
}
return(true);
}
private void gentableListPermission()
{
string sql;
DataSet ds;
int count;
int intval = 0;
class_is.dbconfig db = new class_is.dbconfig();
sql = "select * ";
sql += "from [MENU] menu left join [GROUP_MENU_PERMISSION] per ";
sql += "on menu.MENU_ID = per.MENU_ID_PERMISSION ";
sql += "and per.GROUP_ID = '" + ddlGroup.SelectedValue + "' ";
sql += "order by menu.MENU_ID ";
ds = db.getData(sql);
count = ds.Tables[0].Rows.Count;
hdCountLine.Value = count.ToString();
tbList.Rows.Clear();
tbList.Rows.Add(gentableHeader());
if (count > 0)
{
for (int i = 0; i < count; i++)
{
intval += 1;
string menu = ds.Tables[0].Rows[i]["MENU_NAME"].ToString();
Boolean menuPermission = ds.Tables[0].Rows[i]["MENU_ID_PERMISSION"].ToString() == "" ? false : true;
tbList.Rows.Add(gentableRow(intval, menu, menuPermission));
}
}
}
private Boolean UpdateData()
{
string sql;
class_is.dbconfig db = new class_is.dbconfig();
if (FileUpload.FileName != "")
{
FileUpload.SaveAs(Server.MapPath("Files") + "//" + FileUpload.FileName);
}
sql = "UPDATE [REQUEST_LEAVE] SET ";
sql += "LEAVETYPE_ID = '" + ddlLeaveType.SelectedValue + "',";
sql += "FROM_LEAVE_DATE = '" + setFormatDate(txtFDateLeave.Text) + "',";
sql += "FROM_LEAVE_TIME = '" + txtFTimeLeave.Text + "',";
sql += "TO_LEAVE_DATE = '" + setFormatDate(txtTDateLeave.Text) + "',";
sql += "TO_LEAVE_TIME = '" + txtTTimeLeave.Text + "',";
sql += "NO_LEAVE = '" + txtDay.Text + "',";
sql += "NO_LEAVE_HOUR = '" + txtHour.Text + "',";
sql += "NOTE = '" + txtCauseleave.Text + "',";
sql += "CONTACT = '" + txtContact.Text + "',";
sql += "CONTACT_TEL = '" + txtTelContact.Text + "',";
sql += "STATUS = 'I',";
sql += "UPDATE_DATE = sysdatetime(),";
sql += "UPDATE_BY = '" + Session["empCode"] + "'";
sql += "WHERE LEAVE_ID = '" + Request.QueryString["Req"] + "'";
return(db.ExecuteSQL(sql));
}
protected void btnSave_Click(object sender, EventArgs e)
{
class_is.dbconfig db = new class_is.dbconfig();
string sql = "";
string LeaveID;
string Comment;
//foreach (DataGridItem item in dtgList.Items)
for (int i = 0; i <= dtgList.Items.Count - 1; i++)
{
RadioButton rbValid = (RadioButton)dtgList.Items[i].Cells[0].FindControl("rd1");
RadioButton rbNValid = (RadioButton)dtgList.Items[i].Cells[1].FindControl("rd2");
TextBox txtComment = (TextBox)dtgList.Items[i].Cells[12].FindControl("txtComment");
if (rbValid != null && rbValid.Checked)
{
//update Status Request = A
LeaveID = dtgList.Items[i].Cells[2].Text.ToString();
Comment = txtComment.Text.ToString();
sql += UpdateStatusRequest(LeaveID, "A", Comment);
}
else if (rbNValid != null && rbNValid.Checked)
{
// Update Status Request = R
LeaveID = dtgList.Items[i].Cells[2].Text.ToString();
Comment = txtComment.Text.ToString();
sql += UpdateStatusRequest(LeaveID, "R", Comment);
}
}
if (db.ExecuteSQL(sql))
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Redit", "alert('Save Successfully!!!'); window.location='SearchRequestLeave.aspx';", true);
//BindData();
}
}
private void genTableMenu()
{
string sql;
DataSet ds;
int count;
int intval = 0;
class_is.dbconfig db = new class_is.dbconfig();
sql = "select * ";
sql += "from [MENU] menu ";
sql += "order by SEQ ";
ds = db.getData(sql);
count = ds.Tables[0].Rows.Count;
hdCountLine.Value = count.ToString();
if (count > 0)
{
for (int i = 0; i < count; i++)
{
intval += 1;
string menu = ds.Tables[0].Rows[i]["MENU_NAME"].ToString();
Boolean menuPermission = false;
tbList.Rows.Add(gentableRow(intval, menu, menuPermission));
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("ยืนยันยกเลิกการลางาน", "", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
Boolean DeleteSuccess = false;
string LeaveID = Request.QueryString["Req"];
string sql;
class_is.dbconfig db = new class_is.dbconfig();
sql = "UPDATE [REQUEST_LEAVE] SET ";
sql += "STATUS = 'C',";
sql += "CANCEL_DATE = sysdatetime()";
sql += "WHERE LEAVE_ID = '" + LeaveID + "'";
//sql = "DELETE FROM [REQUEST_LEAVE] ";
//sql += "WHERE LEAVE_ID = '" + reqID + "' ";
DeleteSuccess = db.ExecuteSQL(sql);
if (DeleteSuccess)
{
Response.Write("<script> alert('Delete Complete') </script>");
Response.Write("<script> window.close(); </script>");
}
else
{
Response.Write("<script> alert('Delete Error') </script>");
Response.Write("<script> window.close(); </script>");
}
}
else if (dialogResult == DialogResult.No)
{
Response.Write("<script> window.close(); </script>");
}
}
private void BindData()
{
string sql;
DataSet ds = new DataSet();
class_is.dbconfig db = new class_is.dbconfig();
sql = "select t1.NO_LEAVE , t1.TYPE , iif(t2.NoLeave is null,0,t2.NoLeave) as NoLeave , (t1.NO_LEAVE- iif(t2.NoLeave is null,0,t2.NoLeave) ) as remain ";
sql += "from ( select noLeave.NO_LEAVE , leave.TYPE , leave.ID ";
sql += "from [USER] usr , [ROLE_NO_LEAVE] noLeave ";
sql += "right join [LEAVE_TYPE] leave ";
sql += "on noLeave.LEAVE_TYPE = leave.ID ";
sql += "where usr.ROLE_ID = noLeave.ROLE_ID ";
sql += "and usr.EMP_CODE = '" + Session["empCode"] + "' ";
sql += ")as t1 ";
sql += "left join ";
sql += "(select LEAVETYPE_ID , SUM(NO_LEAVE) as NoLeave ";
sql += "from REQUEST_LEAVE ";
sql += "where CREATE_BY = '" + Session["empCode"] + "' ";
sql += "and REQ_CONFIRM = 'true' ";
sql += "and STATUS = 'A' ";
sql += "group by LEAVETYPE_ID ";
sql += ")as t2 ";
sql += "on t1.ID = t2.LEAVETYPE_ID";
ds = db.getData(sql);
gvState.DataSource = ds;
gvState.DataBind();
}
private void getUserList()
{
string depName = Request.QueryString["depName"];
string userName = Request.QueryString["userName"];
string group = Request.QueryString["GroupName"];
string Name = Request.QueryString["Name"];
string empCode = Request.QueryString["empCode"];
class_is.dbconfig db = new class_is.dbconfig();
DataSet ds;
string sql;
string whereClause = "";
if (userName != "")
{
whereClause += "AND USER_NAME like '%" + userName + "%'";
}
if (Name != "")
{
whereClause += "AND FIRST_NAME like N'%" + Name + "%' ";
}
if (group != "*")
{
whereClause += "AND USER_GROUP = '" + group + "' ";
}
if (empCode != "")
{
whereClause += "AND EMP_CODE like '%" + empCode + "%' ";
}
if (depName != "*")
{
whereClause += "AND DeptID = '" + depName + "' ";
}
//if (whereClause != "") whereClause = whereClause.Substring(4, whereClause.Length - 4);
sql = "with listUser as ( ";
sql += "SELECT EMP_CODE,FIRST_NAME + ' ' + LAST_NAME AS NAME , USER_NAME,usrGroup.GroupName,LINE_ID,DeptID,dep.DEPARTMENT_NAME,EMAIL ";
sql += "FROM [USER] usr , [DEPARTMENT] dep , [USER_GROUP] usrGroup ";
sql += "where usr.DeptID = dep.DEPARTMENT_ID ";
sql += "and usr.USER_GROUP = usrGroup.GroupID ";
if (whereClause != "")
{
sql += whereClause;
}
sql += ")";
sql += "select * ";
sql += ",(select usr.FIRST_NAME + ' ' + usr.LAST_NAME from [USER] usr where DeptID = listUser.DeptID and usr.USER_GROUP = '3') as APPROVE_NAME ";
sql += ",(select usr.FIRST_NAME + ' ' + usr.LAST_NAME from [USER] usr where DeptID = listUser.DeptID and usr.USER_GROUP = '4') as COMFIRM_NAME ";
sql += "from listUser ";
//if(whereClause != "") sql += "WHERE " + whereClause;
ds = db.getData(sql);
if (ds.Tables[0].Rows.Count > 0)
{
dtg.DataSource = ds;
dtg.DataBind();
}
else
{
BuildNoRecords(dtg, ds);
}
}
private void GetUserGroup()
{
DataSet ds = new DataSet();
string sql;
int i;
int count;
string GroupID, GroupName;
SqlCommand cmd;
ListItem li;
class_is.dbconfig db = new class_is.dbconfig();
SqlConnection conn = new SqlConnection(db.config());
SqlDataAdapter data;
conn.Open();
sql = "select * ";
sql += "from [IS].[dbo].[UserGroup] ";
cmd = new SqlCommand(sql);
data = new SqlDataAdapter(sql, conn);
data.Fill(ds);
count = ds.Tables[0].Rows.Count;
for (i = 0; i <= count - 1; i++)
{
GroupID = ds.Tables[0].Rows[i]["GroupID"].ToString();
GroupName = ds.Tables[0].Rows[i]["GroupName"].ToString();
li = new ListItem(GroupName, GroupID);
ddlGroup.Items.Add(li);
}
conn.Close();
}
private Boolean SaveData()
{
class_is.dbconfig db = new class_is.dbconfig();
string sql = "";
string reqID;
for (int i = 0; i <= dtgList.Items.Count - 1; i++)
{
RadioButton rbValid = (RadioButton)dtgList.Items[i].Cells[0].FindControl("rd1");
//RadioButton rbNValid = (RadioButton)dtgList.Items[i].Cells[1].FindControl("rd2");
if (rbValid != null && rbValid.Checked)
{
//update Status Request = A
reqID = dtgList.Items[i].Cells[1].Text.ToString();
sql += UpdateStatusConfirm(reqID, "A");
}
//else if (rbNValid != null && rbNValid.Checked)
//{
// // Update Status Request = R
// reqID = dtgList.Items[i].Cells[2].Text.ToString();
// sql += UpdateStatusConfirm(reqID, "R");
//}
}
if (db.ExecuteSQL(sql))
{
return(true);
}
else
{
return(false);
}
}
private void bindData()
{
string sql;
DataSet ds = new DataSet();
class_is.dbconfig db = new class_is.dbconfig();
sql = "select reqLeave.LEAVE_ID as reqID , usr.EMP_CODE as EmpCode , usr.FIRST_NAME + ' ' + usr.LAST_NAME as Name , lType.TYPE , ";
sql += "convert(varchar(10),FROM_LEAVE_DATE,103) + ' ' + convert(varchar(5), reqLeave.FROM_LEAVE_TIME,114) + ' - ' + ";
sql += "convert(varchar(10),TO_LEAVE_DATE,103) + ' ' + convert(varchar(5), reqLeave.TO_LEAVE_TIME,114) as LeaveDate, ";
sql += "(select FIRST_NAME + ' ' + LAST_NAME from [USER] where EMP_CODE = '" + Session["empCode"] + "' ) as confirmName , ";
sql += "reqLeave.NOTE as Note ";
sql += "from [USER] usr , [REQUEST_LEAVE] reqLeave , ";
sql += "[LEAVE_TYPE] lType ";
sql += "where usr.EMP_CODE = reqLeave.CREATE_BY ";
sql += "and reqLeave.LEAVETYPE_ID = lType.ID ";
sql += "and reqLeave.REQ_CONFIRM = 'false' ";
sql += "and reqLeave.[STATUS] = 'A'";
sql += "and reqLeave.CONFIRM_BY = '" + Session["empCode"] + "'";
ds = db.getData(sql);
if (ds.Tables[0].Rows.Count > 0)
{
dtgList.DataSource = ds;
dtgList.DataBind();
}
else
{
BuildNoRecords(dtgList, ds);
}
}
private Boolean deleteAllPermission(int _Group)
{
string sql;
class_is.dbconfig db = new class_is.dbconfig();
sql = "delete from [GROUP_MENU_PERMISSION] ";
sql += "where [GROUP_ID] = " + _Group + "";
return(db.ExecuteSQL(sql));
}
private void bindData()
{
string sEmpCode;
string sName;
DataSet ds = new DataSet();
string sql;
class_is.dbconfig db = new class_is.dbconfig();
sEmpCode = txtEmpCode.Text;
//else sEmpCode = Session["empCode"].ToString();
sName = txtName.Text;
sql = "with reqData as ( ";
sql += "SELECT usr.EMP_CODE as EmpCode , usr.[FIRST_NAME] + ' ' + usr.[LAST_NAME] as Name, convert(varchar(10), reqLeave.CREATE_DATE ,103) as CreateDate , ";
sql += "convert(varchar(10),FROM_LEAVE_DATE,103) + ' ' + convert(varchar(5), reqLeave.FROM_LEAVE_TIME,114) + ' - ' + ";
sql += "convert(varchar(10),TO_LEAVE_DATE,103) + ' ' + convert(varchar(5), reqLeave.TO_LEAVE_TIME,114) as LeaveDate , ";
sql += "lType.TYPE as LeaveType , ";
sql += "CASE ";
sql += "WHEN STATUS = 'A' THEN 'อนุญาต' ";
sql += "WHEN STATUS = 'R' THEN 'ไม่อนุญาต' ";
sql += "WHEN STATUS = 'I' THEN 'รอพิจารณา' ";
sql += "END as STATUS , ";
sql += "reqLeave.APPROVE_BY as APPROVE_BY , ";
sql += "convert(varchar(10),reqLeave.APPROVE_DATE,103) + ' ' + convert(varchar(5), reqLeave.APPROVE_DATE,114) as ApprDate , ";
sql += "reqLeave.REQ_CONFIRM AS confirmStatus , ";
sql += "reqLeave.CONFIRM_BY as empConfirm, ";
sql += "convert(varchar(15),reqLeave.CONFIRM_DATE,103) + ' ' + convert(varchar(5), reqLeave.CONFIRM_DATE,114) as confirmDate ";
sql += "FROM [REQUEST_LEAVE] reqLeave , [USER] usr , [LEAVE_TYPE] lType ";
sql += "where reqLeave.CREATE_BY = usr.EMP_CODE ";
sql += "and STATUS <> 'C'";
sql += "and reqLeave.LEAVETYPE_ID = lType.ID ";
if (sEmpCode != "")
{
sql += "and reqLeave.CREATE_BY = '" + sEmpCode + "' ";
}
if (sName != "")
{
sql += "and usr.FIRST_NAME like N'%" + sName + "%'";
}
sql += "and year(CREATE_DATE) = YEAR(GETDATE()) ";
sql += ") ";
sql += "select * , ";
sql += "(select FIRST_NAME + ' ' + LAST_NAME from [USER] where EMP_CODE = APPROVE_BY) as ApprName , ";
sql += "(select FIRST_NAME + ' ' + LAST_NAME from [USER] where EMP_CODE = reqData.empConfirm) as confirmName ";
sql += "from reqData ";
ds = db.getData(sql);
if (ds.Tables[0].Rows.Count > 0)
{
dtgList.DataSource = ds;
dtgList.DataBind();
insertSymbol();
}
else
{
BuildNoRecords(dtgList, ds);
}
}
public DataSet getDepartment()
{
class_is.dbconfig db = new class_is.dbconfig();
DataSet ds = new DataSet();
string sql;
sql = "select * ";
sql += "from [DEPARTMENT] ";
ds = db.getData(sql);
return(ds);
}
public DataSet getUserRole()
{
class_is.dbconfig db = new class_is.dbconfig();
DataSet ds = new DataSet();
string sql;
sql = "select * ";
sql += "from [USER_ROLE] ";
ds = db.getData(sql);
return(ds);
}
public DataSet getUserData(string userName)
{
class_is.dbconfig db = new class_is.dbconfig();
DataSet ds = new DataSet();
string sql;
sql = "SELECT * ";
sql += "FROM [USER] ";
sql += "WHERE USER_NAME = '" + userName + "'";
ds = db.getData(sql);
return(ds);
}
private DataSet getMenuSubLink(string MenuID)
{
DataSet ds = new DataSet();
class_is.dbconfig db = new class_is.dbconfig();
string sql;
sql = "select * ";
sql += "from [MENU] ";
sql += "where MENU_SUB_LINK = '" + MenuID + "'";
sql += "and MENU_ID <> '" + MenuID + "'";
sql += "order by seq ";
ds = db.getData(sql);
return(ds);
}
private void GetData()
{
string sql;
DataSet ds = new DataSet();
class_is.dbconfig db = new class_is.dbconfig();
sql = "SELECT NoL.ROLE_ID as RoleID , NoL.LEAVE_TYPE as Leave , Urole.ROLE_NAME as Role , Ltype.TYPE as LeaveType , NoL.NO_LEAVE as NoLeave ";
sql += "FROM [ROLE_NO_LEAVE] NoL , [LEAVE_TYPE] Ltype , [USER_ROLE] Urole ";
sql += "where Ltype.ID = NoL.LEAVE_TYPE ";
sql += "and Urole.ROLE_ID = NoL.ROLE_ID ";
sql += "order by NoL.ROLE_ID , NoL.LEAVE_TYPE ";
ds = db.getData(sql);
dtg.DataSource = ds;
dtg.DataBind();
}
private int getValidDate()
{
int ValidDate;
string sql;
class_is.dbconfig db = new class_is.dbconfig();
DataSet ds = new DataSet();
sql = "select NO_LEAVE ";
sql += "from [ROLE_NO_LEAVE] ";
sql += "where ROLE_ID = '" + Session["ROLE"] + "'";
sql += "and LEAVE_TYPE = '" + ddlLeaveType.SelectedValue + "'";
ds = db.getData(sql);
ValidDate = Convert.ToInt16(ds.Tables[0].Rows[0]["NO_LEAVE"].ToString());
return(ValidDate);
}
private void getData()
{
string sql;
DataSet ds = new DataSet();
class_is.dbconfig db = new class_is.dbconfig();
sql = "select NO_LEAVE ";
sql += "from [ROLE_NO_LEAVE] ";
sql += "where ROLE_ID = '" + Role + "'";
sql += "and LEAVE_TYPE = '" + Leave + "'";
ds = db.getData(sql);
if (ds.Tables[0].Rows.Count > 0)
{
txtNoLeave.Text = ds.Tables[0].Rows[0]["NO_LEAVE"].ToString();
}
}
private Boolean deleteData()
{
string sql;
DataSet ds = new DataSet();
class_is.dbconfig db = new class_is.dbconfig();
sql = "delete from [USER] where USER_NAME = '" + txtUserName.Text + "'";
if (db.ExecuteSQL(sql))
{
return(true);
}
else
{
return(false);
}
}
private string getuserApprComfirm(string TypeGroupAppr)
{
string sql;
class_is.dbconfig db = new class_is.dbconfig();
DataSet ds = new DataSet();
string empCode;
sql = "select EMP_CODE ";
sql += "from [USER] usr ";
sql += "where usr.DeptID = '" + Session["dep"] + "'";
sql += "and usr.USER_GROUP = '" + TypeGroupAppr + "'";
ds = db.getData(sql);
empCode = ds.Tables[0].Rows[0]["EMP_CODE"].ToString();
return(empCode);
}
protected void Page_Load(object sender, EventArgs e)
{
DisplayMenu();
if (Session["userName"] != null)
{
string sql;
DataSet ds = new DataSet();
class_is.dbconfig db = new class_is.dbconfig();
sql = "SELECT FIRST_NAME , LAST_NAME , ROLE_ID , USER_GROUP , GroupName ";
sql += "FROM [USER] usr , [USER_GROUP] gr ";
sql += "WHERE USER_NAME = '" + Session["userName"] + "'";
sql += "AND usr.USER_GROUP = gr.GroupID ";
ds = db.getData(sql);
lblUsername.Text = ds.Tables[0].Rows[0]["FIRST_NAME"].ToString() + ' ' + ds.Tables[0].Rows[0]["LAST_NAME"].ToString();
lblGroup.Text = ds.Tables[0].Rows[0]["GroupName"].ToString();
}
}
private void DisplayMenu()
{
string sql;
class_is.dbconfig db = new class_is.dbconfig();
DataSet ds = new DataSet();
int cnt;
sql = "select menu.MENU_NAME , menu.MENU_LINK , menu.MENU_SUB_LINK ";
sql += "from [USER] usr , [GROUP_MENU_PERMISSION] per , [MENU] menu ";
sql += "where USER_NAME = '" + Session["userName"] + "'";
sql += "and usr.USER_GROUP = per.GROUP_ID ";
sql += "and per.MENU_ID_PERMISSION = menu.MENU_ID ";
sql += "order by menu.SEQ ";
ds = db.getData(sql);
cnt = ds.Tables[0].Rows.Count;
if (cnt > 0)
{
for (int i = 0; i < cnt; i++)
{
HtmlAnchor a = new HtmlAnchor();
HtmlGenericControl li = new HtmlGenericControl();
HtmlGenericControl div = new HtmlGenericControl();
if (ds.Tables[0].Rows[i]["MENU_SUB_LINK"].ToString() == "")
{
li.Attributes["class"] = "nav-item";
a.Attributes["class"] = "nav-link";
a.HRef = ds.Tables[0].Rows[i]["MENU_LINK"].ToString();
a.InnerText = ds.Tables[0].Rows[i]["MENU_NAME"].ToString();
li.Controls.Add(a);
ulMenu.Controls.Add(li);
}
else
{
li.Attributes["class"] = "nav-item dropdown";
a.Attributes["class"] = "nav-link dropdown-toggle";
a.HRef = ds.Tables[0].Rows[i]["MENU_LINK"].ToString();
a.InnerText = ds.Tables[0].Rows[i]["MENU_NAME"].ToString();
div.Attributes["class"] = "dropdown-menu";
li.Controls.Add(a);
ulMenu.Controls.Add(li);
}
}
}
}
private Boolean UpdateData()
{
string sql;
class_is.dbconfig db = new class_is.dbconfig();
sql = "update [ROLE_NO_LEAVE] set ";
sql += "NO_LEAVE = '" + txtNoLeave.Text + "'";
sql += "where ROLE_ID = '" + ddlRole.SelectedValue + "'";
sql += "and LEAVE_TYPE = '" + ddlLeaveType.SelectedValue + "'";
if (db.ExecuteSQL(sql))
{
return(true);
}
else
{
return(false);
}
}
private Boolean empCodeExist()
{
string sql;
DataSet ds = new DataSet();
class_is.dbconfig db = new class_is.dbconfig();
sql = "select * ";
sql += "from [USER] ";
sql += "where EMP_CODE = '" + txtEmpID.Text + "' ";
ds = db.getData(sql);
if (ds.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
private Boolean SaveData()
{
string sql;
class_is.dbconfig db = new class_is.dbconfig();
if (FileUpload.FileName != "")
{
FileUpload.SaveAs(Server.MapPath("Files") + "//" + FileUpload.FileName);
}
sql = "INSERT INTO [REQUEST_LEAVE] ( ";
sql += "[LEAVETYPE_ID],[FROM_LEAVE_DATE],[FROM_LEAVE_TIME] ";
sql += ",[TO_LEAVE_DATE],[TO_LEAVE_TIME],[NO_LEAVE],[NO_LEAVE_HOUR] ";
sql += ",[NOTE],[CONTACT],[CONTACT_TEL],[STATUS],[APPROVE_BY],[REQ_CONFIRM],CONFIRM_BY ";
sql += ",[CREATE_DATE],[CREATE_BY],[UPDATE_DATE],[UPDATE_BY] ";
sql += ") VALUES (";
sql += "'" + ddlLeaveType.SelectedValue + "',";
sql += "'" + setFormatDate(txtFDateLeave.Text) + "',";
sql += "'" + txtFTimeLeave.Text + "',";
sql += "'" + setFormatDate(txtTDateLeave.Text) + "',";
sql += "'" + txtTTimeLeave.Text + "',";
sql += "'" + txtDay.Text + "',";
sql += "'" + txtHour.Text + "',";
sql += "'" + txtCauseleave.Text + "',";
sql += "'" + txtContact.Text + "',";
sql += "'" + txtTelContact.Text + "',";
sql += "'I',";
sql += "'" + getuserApprComfirm("3") + "',";
if (ddlLeaveType.SelectedValue == "3") // ถ้าประเภทการลาเป็นลาพักร้อนจะ set req_confirm เป็น FALSE เพื่อมีการยืนยันการ confirm ต่อไป
{
sql += "'FALSE',";
}
else
{
sql += "'TRUE',";
}
sql += "'" + getuserApprComfirm("4") + "',";
sql += "sysdatetime(),";
sql += "'" + Session["empCode"] + "',";
sql += "sysdatetime(),";
sql += "'" + Session["empCode"] + "'";
sql += ")";
return(db.ExecuteSQL(sql));
}