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);
}
}
}
}
}