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 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 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)); } } }
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 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 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 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 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 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 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 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); }
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(); } }
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 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 void getLeaveType() { ListItem li; class_is.dbconfig db = new class_is.dbconfig(); string sql; DataSet ds; sql = "select * "; sql += "from [LEAVE_TYPE] "; ds = db.getData(sql); if (ds.Tables[0].Rows.Count > 0) { li = new ListItem("ทั้งหมด", "*"); ddlLeaveType.Items.Add(li); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { li = new ListItem(ds.Tables[0].Rows[i]["TYPE"].ToString(), ds.Tables[0].Rows[i]["ID"].ToString()); ddlLeaveType.Items.Add(li); } } }
private void getDataGroup() { string sql; DataSet ds; int count; ListItem li; class_is.dbconfig db = new class_is.dbconfig(); sql = "select * "; sql += "from [USER_GROUP] "; ds = db.getData(sql); count = ds.Tables[0].Rows.Count; for (int i = 0; i < count; i++) { string groupID = ds.Tables[0].Rows[i]["GroupID"].ToString(); string groupName = ds.Tables[0].Rows[i]["GroupName"].ToString(); li = new ListItem(groupName, groupID); ddlGroup.Items.Add(li); } }
private Boolean DateHolliday(DateTime _Date) { string sql; class_is.dbconfig db = new class_is.dbconfig(); DataSet ds = new DataSet(); sql = "select * "; sql += "from [HOLIDAY] "; sql += "where convert(varchar(10),DATE_HOLIDAY,120) = '" + _Date.ToString("yyyy-MM-dd") + "'"; ds = db.getData(sql); if (ds.Tables[0].Rows.Count > 0) { return(true); } else { return(false); } }
private Boolean hasData() { string sql; Boolean _hasData; 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 = '" + ddlRole.SelectedValue + "'"; sql += "and LEAVE_TYPE = '" + ddlLeaveType.SelectedValue + "'"; ds = db.getData(sql); if (ds.Tables[0].Rows.Count > 0) { _hasData = true; } else { _hasData = false; } return(_hasData); }
private int getNoLeave() { int noLeave; string sql; class_is.dbconfig db = new class_is.dbconfig(); DataSet ds = new DataSet(); sql = "select sum(NO_LEAVE) as noLeaveDate "; sql += "from [REQUEST_LEAVE] "; sql += "where CREATE_BY = '" + Session["empCode"] + "'"; sql += "and LEAVETYPE_ID = '" + ddlLeaveType.Text + "'"; sql += "group by LEAVETYPE_ID "; ds = db.getData(sql); if (ds.Tables[0].Rows.Count > 0) { noLeave = Convert.ToInt16(ds.Tables[0].Rows[0]["noLeaveDate"].ToString()); } else { noLeave = 0; } return(noLeave); }
private void getDataList() { CultureInfo cultureinfo = new CultureInfo("en-US"); Thread.CurrentThread.CurrentCulture = cultureinfo; string Type = Request.QueryString["Type"]; string FDate = Request.QueryString["FDate"]; string TDate = Request.QueryString["TDate"]; string LType = Request.QueryString["LeaveType"]; TimeSpan FTime = TimeSpan.Parse("00:00:00"); TimeSpan TTime = TimeSpan.Parse("23:59:59"); DateTime _FDate = DateTime.ParseExact(FDate, "dd/MM/yyyy", cultureinfo); DateTime _TDate = DateTime.ParseExact(TDate, "dd/MM/yyyy", cultureinfo); _FDate = _FDate + FTime; _TDate = _TDate + TTime; //DateTime dt = DateTime.Parse("11/04/2013", cultureinfo); string sql; DataSet ds = new DataSet(); class_is.dbconfig db = new class_is.dbconfig(); sql = "with reqLeaveData as ( "; sql += "select LEAVE_ID as LeaveID "; sql += ",convert(varchar(10),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 += ",NO_LEAVE AS NoLeave "; sql += ",LTYPE.TYPE AS LeaveType "; sql += ",NOTE "; 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 += ",REQ_CONFIRM AS ConfirmStatus "; sql += ",reqLeave.CONFIRM_BY AS CONFIRM_BY "; sql += "from [REQUEST_LEAVE] reqLeave , [LEAVE_TYPE] LTYPE "; sql += "where reqLeave.LEAVETYPE_ID = LTYPE.ID "; sql += "and STATUS <> 'C'"; sql += "and CREATE_BY = '" + Session["empCode"] + "' "; if (Type == "L") { sql += "AND FROM_LEAVE_DATE >= '" + _FDate + "' AND TO_LEAVE_DATE <= '" + _TDate + "'"; } else { sql += "AND CREATE_DATE between '" + _FDate + "' AND '" + _TDate + "'"; } if (LType != "*") { sql += "AND LEAVETYPE_ID =" + LType; } sql += ")"; sql += "select * , "; sql += "(select usr.FIRST_NAME + ' ' + usr.LAST_NAME from [USER] usr where usr.EMP_CODE = APPROVE_BY and STATUS <> 'รอพิจารณา') as APPROVE_NAME , "; sql += "(select usr.FIRST_NAME + ' ' + usr.LAST_NAME from [USER] usr where usr.EMP_CODE = CONFIRM_BY and ConfirmStatus <> 'false') AS COMFIRM_NAME "; sql += "from reqLeaveData "; ds = db.getData(sql); if (ds.Tables[0].Rows.Count > 0) { dtgList.DataSource = ds; dtgList.DataBind(); HideColumn(); } else { BuildNoRecords(dtgList, ds); } }
private void BindData() { string sql; DataSet ds; class_is.dbconfig db = new class_is.dbconfig(); string Type = Request.QueryString["Type"]; string _FDate = Request.QueryString["FDate"]; string _TDate = Request.QueryString["TDate"]; TimeSpan FTime = TimeSpan.Parse("00:00:00"); TimeSpan TTime = TimeSpan.Parse("23:59:59"); string Name = Request.QueryString["Name"]; string EmpCode = Request.QueryString["EmpCode"]; string LType = Request.QueryString["LeaveType"]; string Status = Request.QueryString["Status"]; _FDate = _FDate + " " + FTime; _TDate = _TDate + " " + TTime; sql = "select tbl1.*,isnull(RemainDate,(select NO_LEAVE from [ROLE_NO_LEAVE] where LEAVE_TYPE = tbl1.TypeID and ROLE_ID = tbl1.ROLE_ID)) as RemainDate "; sql += "from ( "; sql += "SELECT reqLeave.LEAVE_ID as LeaveID , usr.EMP_CODE AS EmpCode , usr.FIRST_NAME + ' ' + usr.LAST_NAME AS NAME , convert(varchar(10),CREATE_DATE,103) AS CreateRequest , "; 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 += "reqLeave.NO_LEAVE AS NoLeave , STATUS , LType.ID as TypeID, "; sql += "LType.TYPE AS Type ,NOTE AS Note ,usr.EMP_CODE ,usr.ROLE_ID "; sql += "from [REQUEST_LEAVE] reqLeave , [USER] usr , LEAVE_TYPE LType "; sql += "where reqLeave.CREATE_BY = usr.EMP_CODE "; sql += "and reqLeave.LEAVETYPE_ID = LType.ID "; sql += "and STATUS = 'I'"; if (Type == "L") { sql += "AND reqLeave.FROM_LEAVE_DATE >= '" + _FDate + "' AND reqLeave.TO_LEAVE_DATE <= '" + _TDate + "'"; } else if (Type == "C") { sql += "AND CREATE_DATE between '" + _FDate + "' AND '" + _TDate + "'"; } if (Name != "") { sql += "AND usr.FIRST_NAME like '%" + Name + "%' "; } if (EmpCode != "") { sql += "AND reqLeave.CREATE_BY = '" + EmpCode + "' "; } if (LType != "*") { sql += "AND reqLeave.LEAVETYPE_ID = '" + LType + "' "; } if (Status != "*") { sql += "AND STATUS = '" + Status + "'"; } sql += "and reqLeave.APPROVE_BY = '" + Session["empCode"] + "'"; sql += ") as tbl1 "; sql += "left join "; sql += "( "; sql += "select (t2.NO_LEAVE - t1.sumLeaveDay) as RemainDate , EMP_CODE , LEAVE_TYPE "; sql += "from ( "; sql += "select CREATE_BY, LEAVETYPE_ID, sum(NO_LEAVE) as sumLeaveDay , sum(NO_LEAVE_HOUR) as sumLeaveHour "; sql += "from [REQUEST_LEAVE] "; sql += "where STATUS = 'A' "; sql += "and REQ_CONFIRM = 'true' "; sql += "group by LEAVETYPE_ID , CREATE_BY "; sql += ") as t1 "; sql += ", "; sql += "( "; sql += "select noLeave.LEAVE_TYPE , noLeave.NO_LEAVE , usr.* "; sql += "from [USER] usr , [ROLE_NO_LEAVE] noLeave "; sql += "where usr.ROLE_ID = noLeave.ROLE_ID "; sql += ") as t2 "; sql += "where t1.CREATE_BY = t2.EMP_CODE "; sql += "and t1.LEAVETYPE_ID = t2.LEAVE_TYPE "; sql += ") as tbl2 "; sql += "on tbl1.EMP_CODE = tbl2.EMP_CODE "; sql += "and tbl1.TypeID = tbl2.LEAVE_TYPE "; ds = db.getData(sql); if (ds.Tables[0].Rows.Count > 0) { dtgList.DataSource = ds; dtgList.DataBind(); } else { BuildNoRecords(dtgList, ds); } }
private void getDataList() { string sql; int intval = 0; DataSet ds = new DataSet(); class_is.dbconfig db = new class_is.dbconfig(); sql = "with reqList as ( "; sql += "select convert(varchar(5),sum(NO_LEAVE)) + '/' + convert(varchar(5),COUNT(LEAVETYPE_ID)) as NoLeave ,YEAR(FROM_LEAVE_DATE) as yLeaveDate,MONTH(FROM_LEAVE_DATE) as mLeaveDate , CREATE_BY "; sql += "from [REQUEST_LEAVE] "; sql += "where LEAVETYPE_ID = '1' "; sql += "and REQ_CONFIRM = 'true' "; sql += "and STATUS = 'A' "; if (rblPeriod.SelectedValue == "2") { sql += "and MONTH(FROM_LEAVE_DATE) in (1,2,3,10,11,12)"; } else if (rblPeriod.SelectedValue == "1") { sql += "and MONTH(FROM_LEAVE_DATE) in (4,5,6,7,8,9)"; } sql += "and YEAR(FROM_LEAVE_DATE) = '" + ddlYears.SelectedValue + "'"; sql += "group by CREATE_BY , LEAVETYPE_ID , YEAR(FROM_LEAVE_DATE) , MONTH(FROM_LEAVE_DATE) "; sql += ") "; sql += "SELECT distinct usr.FIRST_NAME + ' ' + usr.LAST_NAME as Name , usrRole.ROLE_NAME as Position , req.CREATE_BY , "; if (rblPeriod.SelectedValue == "1") { sql += "(select NoLeave from reqList where mLeaveDate = '4' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true' ) as Time1, "; sql += "(select NoLeave from reqList where mLeaveDate = '5' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true') as Time2, "; sql += "(select NoLeave from reqList where mLeaveDate = '6' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true') as Time3, "; sql += "(select NoLeave from reqList where mLeaveDate = '7' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true') as Time4, "; sql += "(select NoLeave from reqList where mLeaveDate = '8' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true') as Time5, "; sql += "(select NoLeave from reqList where mLeaveDate = '9' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true') as Time6, "; } else if (rblPeriod.SelectedValue == "2") { sql += "(select NoLeave from reqList where mLeaveDate = '10' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true') as Time1, "; sql += "(select NoLeave from reqList where mLeaveDate = '11' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true') as Time2, "; sql += "(select NoLeave from reqList where mLeaveDate = '12' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true') as Time3, "; sql += "(select NoLeave from reqList where mLeaveDate = '1' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true') as Time4, "; sql += "(select NoLeave from reqList where mLeaveDate = '2' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true') as Time5, "; sql += "(select NoLeave from reqList where mLeaveDate = '3' and CREATE_BY = req.CREATE_BY and REQ_CONFIRM = 'true') as Time6, "; } sql += "(select convert(varchar(5),sum(NO_LEAVE)) + '/' + convert(varchar(5),COUNT(LEAVETYPE_ID)) from [REQUEST_LEAVE] "; sql += "where LEAVETYPE_ID = '1' "; sql += "and CREATE_BY = req.CREATE_BY "; sql += "and REQ_CONFIRM = 'true' "; sql += "and STATUS = 'A' "; if (rblPeriod.SelectedValue == "2") { sql += "and MONTH(FROM_LEAVE_DATE) in (1,2,3,10,11,12)"; } else if (rblPeriod.SelectedValue == "1") { sql += "and MONTH(FROM_LEAVE_DATE) in (4,5,6,7,8,9)"; } sql += "group by CREATE_BY , LEAVETYPE_ID) as TotalLeave , "; sql += "(select convert(varchar(5),sum(NO_LEAVE)) + '/' + convert(varchar(5),COUNT(LEAVETYPE_ID)) as NoLeave "; sql += "from [REQUEST_LEAVE] "; sql += "where LEAVETYPE_ID = '2' "; sql += "and CREATE_BY = req.CREATE_BY "; sql += "and REQ_CONFIRM = 'true' "; if (rblPeriod.SelectedValue == "2") { sql += "and MONTH(FROM_LEAVE_DATE) in (1,2,3,10,11,12)"; } else if (rblPeriod.SelectedValue == "1") { sql += "and MONTH(FROM_LEAVE_DATE) in (4,5,6,7,8,9)"; } sql += "and YEAR(FROM_LEAVE_DATE) = '" + ddlYears.SelectedValue + "'"; sql += "group by CREATE_BY , LEAVETYPE_ID) as Errand, "; sql += "(select convert(varchar(5),sum(NO_LEAVE)) + '/' + convert(varchar(5),COUNT(LEAVETYPE_ID)) as NoLeave "; sql += " from [REQUEST_LEAVE] "; sql += "where LEAVETYPE_ID = '3' "; sql += "and CREATE_BY = req.CREATE_BY "; sql += "and REQ_CONFIRM = 'true' "; if (rblPeriod.SelectedValue == "2") { sql += "and MONTH(FROM_LEAVE_DATE) in (1,2,3,10,11,12)"; } else if (rblPeriod.SelectedValue == "1") { sql += "and MONTH(FROM_LEAVE_DATE) in (4,5,6,7,8,9)"; } sql += "and YEAR(FROM_LEAVE_DATE) = '" + ddlYears.SelectedValue + "'"; sql += "group by CREATE_BY , LEAVETYPE_ID "; sql += ") as Vacation "; sql += "FROM [REQUEST_LEAVE] req , "; sql += "[USER] usr , "; sql += "[USER_ROLE] usrRole "; sql += "where req.CREATE_BY = usr.EMP_CODE "; sql += "and usr.ROLE_ID = usrRole.ROLE_ID "; sql += "and REQ_CONFIRM = 'true'"; if (rblPeriod.SelectedValue == "2") { sql += "and MONTH(FROM_LEAVE_DATE) in (1,2,3,10,11,12)"; } else if (rblPeriod.SelectedValue == "1") { sql += "and MONTH(FROM_LEAVE_DATE) in (4,5,6,7,8,9)"; } sql += "and YEAR(FROM_LEAVE_DATE) = '" + ddlYears.SelectedValue + "'"; ds = db.getData(sql); if (ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { intval += 1; tblist.Rows.Add(genTableRow(intval, ds.Tables[0].Rows[i]["Name"].ToString(), ds.Tables[0].Rows[i]["Position"].ToString(), ds.Tables[0].Rows[i]["Time1"].ToString(), ds.Tables[0].Rows[i]["Time2"].ToString(), ds.Tables[0].Rows[i]["Time3"].ToString(), ds.Tables[0].Rows[i]["Time4"].ToString(), ds.Tables[0].Rows[i]["Time5"].ToString(), ds.Tables[0].Rows[i]["Time6"].ToString(), ds.Tables[0].Rows[i]["TotalLeave"].ToString(), ds.Tables[0].Rows[i]["Errand"].ToString(), ds.Tables[0].Rows[i]["Vacation"].ToString() )); } } else { tblist.Rows.Add(genTableRowEmpty()); } System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); divContainData.RenderControl(htmlWrite); Session["dataList"] = stringWrite.ToString(); }
private void bindData() { string sEmpCode; string sName; DataSet ds = new DataSet(); string sql; string whereCause = ""; sEmpCode = txtEmpCode.Text; //else sEmpCode = Session["empCode"].ToString(); sName = txtName.Text; if (sEmpCode != "") { whereCause += "and t1.EMP_CODE = '" + sEmpCode + "' "; } if (sName != "") { whereCause += "and t1.Name like N'%" + sName + "%'"; } if (ddlTypeLeave.SelectedValue != "*" && ddlTypeLeave.SelectedValue != "") { whereCause += "and t2.LEAVETYPE_ID = '" + ddlTypeLeave.SelectedValue + "'"; } class_is.dbconfig db = new class_is.dbconfig(); sql = "select t1.EMP_CODE as EmpCode , t1.Name ,t1.NO_LEAVE , t1.TYPE , iif(t2.NoLeave is null,0,t2.NoLeave) as NoLeave , "; sql += "(t1.NO_LEAVE- iif(t2.NoLeave is null,0,t2.NoLeave) ) as remain "; sql += "from ( select usr.EMP_CODE , usr.FIRST_NAME + ' ' + usr.LAST_NAME as Name, noLeave.NO_LEAVE , leave.TYPE , leave.ID "; sql += "from [USER] usr , [ROLE_NO_LEAVE] noLeave right join [LEAVE_TYPE] leave on noLeave.LEAVE_TYPE = leave.ID "; sql += "where usr.ROLE_ID = noLeave.ROLE_ID "; //sql += "and usr.EMP_CODE = '580009' "; sql += ")as t1 "; sql += "left join "; sql += "(select LEAVETYPE_ID , SUM(NO_LEAVE) as NoLeave , CREATE_BY "; sql += "from REQUEST_LEAVE "; sql += "where REQ_CONFIRM = 'true' "; sql += "and STATUS = 'A' "; sql += "group by LEAVETYPE_ID , CREATE_BY "; sql += ")as t2 "; sql += "on t1.ID = t2.LEAVETYPE_ID "; sql += "and t1.EMP_CODE = t2.CREATE_BY "; if (whereCause.Length > 0) { sql += "where "; sql += whereCause.Substring(4, whereCause.Length - 4); } ds = db.getData(sql); if (ds.Tables[0].Rows.Count > 0) { dtgList.DataSource = ds; dtgList.DataBind(); } else { BuildNoRecords(dtgList, ds); } System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); dtgList.RenderControl(htmlWrite); Session["dataList"] = stringWrite.ToString(); }
private void DisplayMenu() { string sql; class_is.dbconfig db = new class_is.dbconfig(); DataSet ds = new DataSet(); DataSet ds1 = new DataSet(); int cnt; sql = "select menu.MENU_ID ,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 += "and (MENU_SUB_LINK is null or MENU_LINK = '')"; 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 divDropdown = new HtmlGenericControl(); HtmlAnchor aDropdown = new HtmlAnchor(); if (ds.Tables[0].Rows[i]["MENU_SUB_LINK"].ToString() == "") { if (Request.QueryString["Menu"] == ds.Tables[0].Rows[i]["MENU_NAME"].ToString()) { li.Attributes["class"] = "nav-item"; li.Attributes.Add("Style", "display: table-cell;"); a.Attributes["class"] = "nav-link active"; a.Attributes.Add("Style", "color:#ffffff; background-color:#e5b0f2; display: block;"); 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"; li.Attributes.Add("Style", "display: table-cell;"); a.Attributes["class"] = "nav-link"; a.Attributes.Add("Style", "display: block;"); 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 { if (Request.QueryString["Menu"] == "รายงานวันลา" || Request.QueryString["Menu"] == "รายงานสถานะวันลา" || Request.QueryString["Menu"] == "รายงานวันลาคงเหลือ") { ds1 = getMenuSubLink(ds.Tables[0].Rows[i]["MENU_ID"].ToString()); li.Attributes["class"] = "nav-item dropdown"; li.Attributes.Add("Style", "display: table-cell;"); a.Attributes["class"] = "nav-link dropdown-toggle active"; //a.Attributes["class"] = "nav-link active"; a.Attributes["data-toggle"] = "dropdown"; a.Attributes.Add("Style", "color:#ffffff; background-color:#e5b0f2; display: block;"); a.HRef = ds.Tables[0].Rows[i]["MENU_LINK"].ToString(); a.InnerText = ds.Tables[0].Rows[i]["MENU_NAME"].ToString(); if (ds1.Tables[0].Rows.Count > 0) { //divDropdown.Attributes["class"] = "dropdown-menu"; for (int j = 0; j < ds1.Tables[0].Rows.Count; j++) { //string dropdownName = "aDropdown" + j; divDropdown.Attributes["class"] = "dropdown-menu"; aDropdown = new HtmlAnchor(); aDropdown.Attributes["class"] = "dropdown-item"; aDropdown.HRef = ds1.Tables[0].Rows[j]["MENU_LINK"].ToString(); aDropdown.InnerText = ds1.Tables[0].Rows[j]["MENU_NAME"].ToString(); divDropdown.Controls.Add(aDropdown); } //divDropdown.Controls.Add(aDropdown); } li.Controls.Add(a); li.Controls.Add(divDropdown); ulMenu.Controls.Add(li); } else { ds1 = getMenuSubLink(ds.Tables[0].Rows[i]["MENU_ID"].ToString()); li.Attributes["class"] = "nav-item dropdown"; li.Attributes.Add("Style", "display: table-cell;"); a.Attributes["class"] = "nav-link dropdown-toggle"; //a.Attributes["class"] = "nav-link active"; a.Attributes["data-toggle"] = "dropdown"; a.Attributes.Add("Style", "color:#007bff; display: block;"); a.HRef = ds.Tables[0].Rows[i]["MENU_LINK"].ToString(); a.InnerText = ds.Tables[0].Rows[i]["MENU_NAME"].ToString(); if (ds1.Tables[0].Rows.Count > 0) { //divDropdown.Attributes["class"] = "dropdown-menu"; for (int j = 0; j < ds1.Tables[0].Rows.Count; j++) { //string dropdownName = "aDropdown" + j; divDropdown.Attributes["class"] = "dropdown-menu"; aDropdown = new HtmlAnchor(); aDropdown.Attributes["class"] = "dropdown-item"; aDropdown.HRef = ds1.Tables[0].Rows[j]["MENU_LINK"].ToString(); aDropdown.InnerText = ds1.Tables[0].Rows[j]["MENU_NAME"].ToString(); divDropdown.Controls.Add(aDropdown); } //divDropdown.Controls.Add(aDropdown); } li.Controls.Add(a); li.Controls.Add(divDropdown); ulMenu.Controls.Add(li); } } } } }