void BindData()
{
string sql = "SELECT tmp_pos_id, profix_name, tmp_pos_emp_id, ep.emp_name, ep.emp_lname, tp.tmp_pos_date, CONCAT(p.pos_name, ' / ', ta.affi_name,' / ',te.type_emp_name) AS pos_name, CONCAT(po.pos_name, ' / ', taf.affi_name,' / ',tem.type_emp_name) AS pos_name2,tmp_pos_status_approve FROM tbl_tmp_pos tp JOIN tbl_emp_profile ep ON tp.tmp_pos_emp_id = ep.emp_id JOIN tbl_profix px ON px.profix_id = ep.emp_profix_id JOIN tbl_pos p ON p.pos_id = tp.tmp_pos_pos_id JOIN tbl_pos po ON po.pos_id = tp.tmp_pos_pos_old_id JOIN tbl_affiliation ta ON ta.affi_id = tp.tmp_pos_aff_id JOIN tbl_affiliation taf ON taf.affi_id = tp.tmp_pos_aff_old_id JOIN tbl_type_emp te ON te.type_emp_id = tp.tmp_pos_emp_type_id JOIN tbl_type_emp tem ON tp.tmp_pos_emp_type_old_id = tem.type_emp_id WHERE tp.tmp_pos_status = 0 ORDER BY STR_TO_DATE(tmp_pos_date,'%d-%m-%Y')";
MySqlDataAdapter da = dBScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
TmpPosGridView.DataSource = ds.Tables[0];
TmpPosGridView.DataBind();
LaGridViewData.Text = "พบข้อมูลจำนวน " + ds.Tables[0].Rows.Count + " แถว";
}
void BindData()
{
string sql = "SELECT tmp_cpoint_id, profix_name, tmp_cpoint_emp_id, ep.emp_name, ep.emp_lname, cp.tmp_cpoint_date, c.cpoint_name, co.cpoint_name AS cpoint_name2,tmp_cpoint_status_approve FROM tbl_tmp_cpoint cp JOIN tbl_emp_profile ep ON cp.tmp_cpoint_emp_id = ep.emp_id JOIN tbl_profix px ON px.profix_id = ep.emp_profix_id JOIN tbl_cpoint c ON c.cpoint_id = cp.tmp_cpoint_cpoint_id JOIN tbl_cpoint co ON co.cpoint_id = ep.emp_cpoint_id WHERE cp.tmp_cpoint_status = 0 ORDER BY STR_TO_DATE(tmp_cpoint_date, '%d-%m-%Y')";
MySqlDataAdapter da = dBScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
TmpCopintGridView.DataSource = ds.Tables[0];
TmpCopintGridView.DataBind();
LaGridViewData.Text = "พบข้อมูลจำนวน " + ds.Tables[0].Rows.Count + " แถว";
}
void BindData()
{
string sql = "SELECT * FROM tbl_tmp_ex ex JOIN tbl_emp_profile ep ON ep.emp_id = ex.tmp_ex_emp JOIN tbl_profix px ON px.profix_id = ep.emp_profix_id JOIN tbl_status_working sw ON ex.tmp_ex_working_status = sw.status_working_id WHERE tmp_ex_status = 0 ORDER BY STR_TO_DATE(ex.tmp_ex_date, '%d-%m-%Y')";
MySqlDataAdapter da = dBScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
TmpExGridView.DataSource = ds.Tables[0];
TmpExGridView.DataBind();
LaGridViewData.Text = "พบข้อมูลจำนวน " + ds.Tables[0].Rows.Count + " แถว";
}
public void getMampowerData(string year)
{
string sql = "SELECT pos_id,pos_name, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '701' THEN tbl_emp_profile.emp_id END ) AS LB, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '702' THEN tbl_emp_profile.emp_id END ) AS BB, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '703' THEN tbl_emp_profile.emp_id END ) AS BK, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '704' THEN tbl_emp_profile.emp_id END ) AS PN, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '706' THEN tbl_emp_profile.emp_id END ) AS BG, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '707' THEN tbl_emp_profile.emp_id END ) AS BP, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '708' THEN tbl_emp_profile.emp_id END ) AS NK, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '709' THEN tbl_emp_profile.emp_id END ) AS PO, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '710' THEN tbl_emp_profile.emp_id END ) AS PY, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '902' THEN tbl_emp_profile.emp_id END ) AS TC1, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '903' THEN tbl_emp_profile.emp_id END ) AS TC2, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '904' THEN tbl_emp_profile.emp_id END ) AS TY1, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '905' THEN tbl_emp_profile.emp_id END ) AS TY2, COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id LIKE '60%' THEN tbl_emp_profile.emp_id END ) AS Center, ( COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '701' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '702' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '703' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '704' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '706' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '707' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '708' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '709' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '710' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '902' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '903' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '904' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id = '905' THEN tbl_emp_profile.emp_id END ) + COUNT( CASE WHEN tbl_emp_profile.emp_cpoint_id LIKE '60%' THEN tbl_emp_profile.emp_id END ) ) AS total FROM tbl_manpower LEFT JOIN tbl_emp_profile ON manpower_cpoint_id = emp_cpoint_id AND manpower_pos_id = emp_pos_id AND emp_staus_working = 1 LEFT JOIN tbl_cpoint ON manpower_cpoint_id = cpoint_id LEFT JOIN tbl_pos ON pos_id = manpower_pos_id WHERE manpower_year = '" + year + "' AND pos_name IS NOT NULL GROUP BY manpower_pos_id ORDER BY pos_priorty ASC";
//MySqlDataReader rs = dBScript.selectSQL(sql);
MySqlDataAdapter da = dBScript.getDataSelect(sql);
DataTable dt = new DataTable();
da.Fill(dt);
ManpowerSubGridView.DataSource = dt;
ManpowerSubGridView.DataBind();
}
void BindDataLeave()
{
string sql = "SELECT emp_leave_emp_id,CONCAT(p.profix_name,' ',e.emp_name,' ',e.emp_lname) AS emp_name, COUNT(emp_leave_emp_id) AS total,SUM(emp_leave_sick) AS sick,SUM(emp_leave_relax) AS relax FROM tbl_emp_leave l JOIN tbl_emp_profile e ON l.emp_leave_emp_id = e.emp_id JOIN tbl_profix p ON p.profix_id = e.emp_profix_id WHERE emp_leave_year ='" + dBScript.getBudgetYear() + "' GROUP BY emp_leave_emp_id ORDER BY total,sick DESC LIMIT 0,10";
MySqlDataAdapter da = dBScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
LeaveStatisticsGridView.DataSource = ds.Tables[0];
LeaveStatisticsGridView.DataBind();
if (ds.Tables[0].Rows.Count <= 0)
{
lbLeaveStatisticsNull.Text = "ไม่พบข้อมูลการลาของพนักงาน";
}
}
public void BindData(string sortDate, string sortType)
{
string strSeclctEmp = "";
if (txtSearchId.Text != "")
{
strSeclctEmp += "emp_id LIKE '%" + txtSearchId.Text.Trim() + "%' ";
}
if (txtSearchName.Text != "")
{
if (strSeclctEmp != "")
{
strSeclctEmp += " AND ";
}
strSeclctEmp += "(profix_name LIKE '%" + txtSearchName.Text.Trim() + "%' OR emp_name LIKE '%" + txtSearchName.Text.Trim() + "%' OR emp_lname LIKE '%" + txtSearchName.Text.Trim() + "%')";
}
if (txtSearchCpoint.SelectedValue != "")
{
if (strSeclctEmp != "")
{
strSeclctEmp += " AND ";
}
strSeclctEmp += "cpoint_name LIKE '%" + txtSearchCpoint.SelectedItem + "%' ";
}
if (txtSearchPos.SelectedValue != "")
{
if (strSeclctEmp != "")
{
strSeclctEmp += " AND ";
}
strSeclctEmp += "pos_name LIKE '%" + txtSearchPos.SelectedItem + "%' ";
}
if (txtSearchAffi.SelectedValue != "")
{
if (strSeclctEmp != "")
{
strSeclctEmp += " AND ";
}
strSeclctEmp += "affi_name LIKE '%" + txtSearchAffi.SelectedItem + "%' ";
}
if (strSeclctEmp != "")
{
strSeclctEmp = "(" + strSeclctEmp + ") AND ";
}
string sql = "SELECT * FROM tbl_emp_profile JOIN tbl_profix ON emp_profix_id = profix_id JOIN tbl_cpoint ON emp_cpoint_id = cpoint_id JOIN tbl_pos ON emp_pos_id = pos_id JOIN tbl_affiliation ON affi_id = emp_affi_id JOIN tbl_type_emp ON type_emp_id = emp_type_emp_id JOIN tbl_type_add ON type_add_id = emp_add_type WHERE " + strSeclctEmp + " emp_staus_working = '1' ORDER BY " + sortDate + " " + sortType + " LIMIT 0,20";
Session["sqlEmp"] = sql;
MySqlDataAdapter da = dbScript.getDataSelect(sql);
DataTable ds = new DataTable();
da.Fill(ds);
GridViewEmp.DataSource = ds;
GridViewEmp.DataBind();
LaGridViewData.Text = "พบข้อมูลจำนวน " + ds.Rows.Count + " แถว";
resultCard.Visible = true;
dbScript.CloseConnection();
}
void BindData()
{
string sql = "SELECT * FROM tbl_year";
MySqlDataAdapter da = dbScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
YearGridView.DataSource = ds.Tables[0];
YearGridView.DataBind();
lbYearNull.Text = "พบข้อมูลจำนวน " + ds.Tables[0].Rows.Count + " แถว";
}
void BindData()
{
string sql = "SELECT * FROM tbl_affiliation ORDER BY affi_name";
MySqlDataAdapter da = dbScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
AffGridView.DataSource = ds.Tables[0];
AffGridView.DataBind();
lbAffNull.Text = "พบข้อมูลจำนวน " + ds.Tables[0].Rows.Count + " แถว";
}
void BindData()
{
string sql = "SELECT * FROM tbl_guest guest LEFT JOIN tbl_guest_list li ON guest.guest_id = li.guest_id LEFT JOIN tbl_profix profix ON li.guest_list_profix = profix.profix_id LEFT JOIN tbl_pos pos ON pos.pos_id = li.guest_list_pos LEFT JOIN tbl_cpoint cp ON cp.cpoint_id = li.guest_list_cpoint WHERE li.guest_id = '" + txtGuest_id.Value + "' ORDER BY STR_TO_DATE( guest.guest_offer_date, '%d-%m-%Y' ) DESC";
MySqlDataAdapter da = dBScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
GridViewGuestList.DataSource = ds.Tables[0];
GridViewGuestList.DataBind();
LaGridViewData.Text = "พนักงานทั้งหมด " + ds.Tables[0].Rows.Count + " คน";
}
void BindData()
{
string sql = "SELECT * FROM tbl_guest guest ORDER BY STR_TO_DATE( guest.guest_offer_date, '%d-%m-%Y' ) DESC";
MySqlDataAdapter da = dBScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
GridViewGuest.DataSource = ds.Tables[0];
GridViewGuest.DataBind();
LaGridViewData.Text = "พบข้อมูลจำนวน " + ds.Tables[0].Rows.Count + " แถว";
}
void BindData()
{
string sql = "SELECT * FROM tbl_emp_user JOIN tbl_privilege ON emp_user_privilege=privilege_id";
MySqlDataAdapter da = dBScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
UserGridView.DataSource = ds.Tables[0];
UserGridView.DataBind();
lbUserNull.Text = "พบข้อมูลจำนวน " + ds.Tables[0].Rows.Count + " แถว";
}
public void BindData()
{
string sql = "SELECT emp_leave_id,emp_leave_date_start, emp_leave_date_end, emp_leave_sick, emp_leave_relax, emp_leave_maternity, emp_medical_certificate, emp_leave_deduction_wages, emp_leave_note FROM tbl_emp_leave WHERE emp_leave_emp_id = '" + dBScript.getEmpIDMD5("emp_id", Request.Params["empID"]) + "' AND emp_leave_year ='" + dBScript.getBudgetYear() + "' ORDER BY emp_leave_id ASC";
MySqlDataAdapter da = dBScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
GridLeaveEmp.DataSource = ds.Tables[0];
GridLeaveEmp.DataBind();
if (ds.Tables[0].Rows.Count != 0)
{
Labeltxt.Text = "พบข้อมูลจำนวน " + ds.Tables[0].Rows.Count + " แถว";
}
else
{
Labeltxt.Text = "ไม่พบข้อมูล";
}
}
void BindData()
{
string sql = "SELECT * FROM tbl_manpower LEFT JOIN tbl_cpoint ON manpower_cpoint_id = cpoint_id LEFT JOIN tbl_pos ON pos_id = manpower_pos_id WHERE manpower_year = '" + txtYear.SelectedValue + "' AND manpower_cpoint_id = '" + txtCpoint.SelectedValue + "'";
MySqlDataAdapter da = dBScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
ManPowerGridView.DataSource = ds.Tables[0];
ManPowerGridView.DataBind();
if (ds.Tables[0].Rows.Count > 0)
{
lbManPowerNull.Text = "พบข้อมูลจำนวน " + ds.Tables[0].Rows.Count + " แถว";
}
else
{
lbManPowerNull.Text = "ไม่พบข้อมูล";
}
}
public void BindData()
{
string sql = "SELECT * FROM tbl_emp_profile JOIN tbl_history ON id = history_emp_id JOIN tbl_profix ON profix_id=emp_profix_id JOIN tbl_status_working ON status_working_id = history_status_id WHERE emp_id_card = '" + txtSearchIDCard.Text.Trim() + "' ORDER BY history_id DESC";
MySqlDataAdapter da = dbScript.getDataSelect(sql);
DataSet ds = new DataSet();
da.Fill(ds);
GridViewEmp.DataSource = ds.Tables[0];
GridViewEmp.DataBind();
if (ds.Tables[0].Rows.Count != 0)
{
LaGridViewData.Text = "พบข้อมูลจำนวน " + ds.Tables[0].Rows.Count + " แถว";
}
else
{
LaGridViewData.Text = "ไม่พบข้อมูล";
}
resultCard.Visible = true;
}
public void getSeclctEmp(string sortDate, string sortType)
{
//System.Threading.Thread.Sleep(5000);
//DivLoad.Visible = true;
string strSeclctEmp = "";
if (txtSearchId.Text != "")
{
strSeclctEmp += "emp_id LIKE '%" + txtSearchId.Text.Trim() + "%' ";
}
if (txtSearchName.Text != "")
{
if (strSeclctEmp != "")
{
strSeclctEmp += " AND ";
}
strSeclctEmp += "(profix_name LIKE '%" + txtSearchName.Text.Trim() + "%' OR emp_name LIKE '%" + txtSearchName.Text.Trim() + "%' OR emp_lname LIKE '%" + txtSearchName.Text.Trim() + "%')";
}
if (txtSearchCpoint.SelectedValue != "")
{
if (strSeclctEmp != "")
{
strSeclctEmp += " AND ";
}
strSeclctEmp += "cpoint_id LIKE '" + txtSearchCpoint.SelectedValue + "%' ";
}
if (txtSearchPos.SelectedValue != "")
{
if (strSeclctEmp != "")
{
strSeclctEmp += " AND ";
}
strSeclctEmp += "pos_id = '" + txtSearchPos.SelectedValue + "' ";
}
if (txtSearchAffi.SelectedValue != "")
{
if (strSeclctEmp != "")
{
strSeclctEmp += " AND ";
}
strSeclctEmp += "affi_id = '" + txtSearchAffi.SelectedValue + "' ";
}
if (txtSearchType.SelectedValue != "")
{
if (strSeclctEmp != "")
{
strSeclctEmp += " AND ";
}
strSeclctEmp += "emp_type_emp_id = '" + txtSearchType.SelectedValue + "'";
}
if (strSeclctEmp != "")
{
strSeclctEmp = "(" + strSeclctEmp + ") AND ";
}
string sql = "SELECT * FROM tbl_emp_profile JOIN tbl_profix ON emp_profix_id = profix_id JOIN tbl_cpoint ON emp_cpoint_id = cpoint_id JOIN tbl_pos ON emp_pos_id = pos_id JOIN tbl_affiliation ON affi_id = emp_affi_id JOIN tbl_type_emp ON type_emp_id = emp_type_emp_id LEFT JOIN tbl_type_add ON type_add_id = emp_add_type WHERE " + strSeclctEmp + " emp_staus_working = '1' ORDER BY " + sortDate + " " + sortType;
Session["sqlEmp"] = sql;
MySqlDataAdapter da = dbScript.getDataSelect(sql);
ds = new DataTable();
da.Fill(ds);
GridViewEmp.DataSource = ds;
GridViewEmp.DataBind();
LaGridViewData.Text = "พบข้อมูลจำนวน " + ds.Rows.Count + " แถว";
if (GridViewEmp.DataSource != null)
{
DivEmp.Visible = true;
}
dbScript.CloseConnection();
//DivLoad.Visible = false;
}