public void update_contact()
{
CultureInfo en = new CultureInfo("EN");
dbFile dbClass = new dbFile();
//string table = "tab_emp_contact";
//string[] Columns = { "contact_email", "contact_table", "contact_phone", "contact_mobile1", "contact_mobile2", "contact_admin_id", "contact_submit_date"};
//string[] Value = { contact_email, contact_table, contact_phone, contact_mobile1, contact_mobile2, "1", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", en)};
//string where = "contact_id = '" + contact_id+"'";
//db.update(table, Columns, Value, where);
using (SqlConnection conn = new SqlConnection(dbClass.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("UPDATE tab_emp_contact SET contact_email = @contact_email, contact_table = @contact_table, contact_phone = @contact_phone, contact_mobile1 = @contact_mobile1, contact_mobile2 = @contact_mobile2, contact_emp_id = @contact_emp_id, contact_admin_id = @contact_admin_id, contact_submit_date = @contact_submit_date, contact_status = @contact_status WHERE contact_id = '" + contact_id + "'", conn);
cmd.Parameters.AddWithValue("@contact_email", contact_email);
cmd.Parameters.AddWithValue("@contact_table", contact_table);
cmd.Parameters.AddWithValue("@contact_phone", contact_phone);
cmd.Parameters.AddWithValue("@contact_mobile1", contact_mobile1);
cmd.Parameters.AddWithValue("@contact_mobile2", contact_mobile2);
cmd.Parameters.AddWithValue("@contact_emp_id", contact_emp_id);
cmd.Parameters.AddWithValue("@contact_admin_id", contact_admin_id);
cmd.Parameters.AddWithValue("@contact_submit_date", Convert.ToDateTime(DateTime.Now).ToString("yyyy-MM-dd HH:mm:ss", en));
cmd.Parameters.AddWithValue("@contact_status", contact_status);
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
}
}
public string insert_contact_log()
{
databaseClassLOG dbClass = new databaseClassLOG();
dbFile db = new dbFile();
string txt = "";
using (SqlConnection conn = new SqlConnection(db.sqlConnection_log)) {
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO tab_emp_contact(contact_id, contact_email, contact_table, contact_phone, contact_mobile1, contact_mobile2, contact_emp_id, contact_admin_id, contact_submit_date, contact_status) VALUES(@contact_id, @contact_email, @contact_table, @contact_phone, @contact_mobile1, @contact_mobile2, @contact_emp_id, @contact_admin_id, @contact_submit_date, @contact_status)", conn);
cmd.Parameters.AddWithValue("@contact_id", contact_id);
cmd.Parameters.AddWithValue("@contact_email", contact_email);
cmd.Parameters.AddWithValue("@contact_table", contact_table);
cmd.Parameters.AddWithValue("@contact_phone", contact_phone);
cmd.Parameters.AddWithValue("@contact_mobile1", contact_mobile1);
cmd.Parameters.AddWithValue("@contact_mobile2", contact_mobile2);
cmd.Parameters.AddWithValue("@contact_emp_id", contact_emp_id);
cmd.Parameters.AddWithValue("@contact_admin_id", contact_admin_id);
cmd.Parameters.AddWithValue("@contact_submit_date", Convert.ToDateTime(contact_submit_date).ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@contact_status", contact_status);
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
}
return(txt);
}
public void selectContact_Person(string person)
{
dbFile constr = new dbFile();
using (SqlConnection conn = new SqlConnection(constr.sqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_emp_contact WHERE contact_emp_id = '" + person + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
contact_id = rdr["contact_id"].ToString();
contact_email = rdr["contact_email"].ToString();
contact_table = rdr["contact_table"].ToString();
contact_phone = rdr["contact_phone"].ToString();
contact_mobile1 = rdr["contact_mobile1"].ToString();
contact_mobile2 = rdr["contact_mobile2"].ToString();
contact_emp_id = rdr["contact_emp_id"].ToString();
contact_admin_id = rdr["contact_admin_id"].ToString();
contact_submit_date = rdr["contact_submit_date"].ToString();
contact_status = rdr["contact_status"].ToString();
personal_id = rdr["contact_emp_id"].ToString();
}
conn.Close();
}
}
public List <emp_minusSalaryModel> emp_minus(string emp)
{
dbFile db = new dbFile();
List <emp_minusSalaryModel> obj = new List <emp_minusSalaryModel>();
CultureInfo en = new CultureInfo("EN");
List <string> ls = new List <string>();
List <string> le = new List <string>();
try
{
using (SqlConnection conn = new SqlConnection(db.sqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT ms_start_date, ms_end_date FROM tab_emp_minusSalary WHERE ms_ref_emp_id = '" + emp + "' AND ms_status = 'Y' AND event_status != 'D' GROUP BY ms_start_date , ms_end_date", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
emp_minusSalaryModel pay = new emp_minusSalaryModel();
pay.ms_start_date = Convert.ToDateTime(rdr["ms_start_date"]).ToString("dd MMMM yyyy", th);
pay.ms_end_date = Convert.ToDateTime(rdr["ms_end_date"]).ToString("dd MMMM yyyy", th);
pay.start = Convert.ToDateTime(rdr["ms_start_date"]).ToString("yyyy-MM-dd", en);
pay.end = Convert.ToDateTime(rdr["ms_end_date"]).ToString("yyyy-MM-dd", en);
obj.Add(pay);
}
}
}
catch {
}
return(obj);
}
public string check_img(string emp_id)
{
dbFile db = new dbFile();
string txt = "";
string check = "";
using (SqlConnection conn = new SqlConnection(db.sqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_emp_profile_img WHERE img_ref_emp_id = '" + emp_id + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
check = rdr["img_ref_emp_id"].ToString();
if (string.IsNullOrEmpty(check) == true)
{
txt = "N";
//img_name = "face-2.jpg";
}
else
{
txt = "Y";
}
}
conn.Close();
}
return(txt);
}
public void select_person_byEmp(string emp)
{
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_personal INNER JOIN tab_emp ON tab_emp.ep_ref_personal_id = ps_id WHERE ep_id = '" + emp + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
prefix_th = rdr["ps_prefix_th"].ToString();
prefix_en = rdr["ps_prefix_en"].ToString();
name_th = rdr["ps_name_th"].ToString();
name_en = rdr["ps_name_en"].ToString();
lastname_th = rdr["ps_lastname_th"].ToString();
lastname_en = rdr["ps_lastname_en"].ToString();
gender = rdr["ps_gender"].ToString();
blood = rdr["ps_blood"].ToString();
national_id = rdr["ps_national_id"].ToString();
national_start = Convert.ToDateTime(rdr["ps_national_date_start"]).ToString("dd/MM/yyyy", th);
national_end = Convert.ToDateTime(rdr["ps_national_date_end"]).ToString("dd/MM/yyyy", th);
birthday = Convert.ToDateTime(rdr["ps_birthday"]).ToString("dd/MM/yyyy", th);
nationality = rdr["ps_nationality"].ToString();
race = rdr["ps_race"].ToString();
religion = rdr["ps_religion"].ToString();
marital = rdr["ps_status_marital"].ToString();
personal_id = rdr["ps_id"].ToString();
}
conn.Close();
}
}
public List <SelectListItem> personal_for_edit()
{
List <SelectListItem> item = new List <SelectListItem>();
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT ps_name_th, ps_id, ps_lastname_th, ps_national_id FROM tab_personal", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
item.Add(new SelectListItem {
Text = rdr["ps_national_id"] + " | " + rdr["ps_name_th"].ToString() + " " + rdr["ps_lastname_th"], Value = rdr["ps_id"].ToString()
});
}
conn.Close();
}
item.Insert(0, new SelectListItem {
Text = "เลือก", Value = "0"
});
return(item);
}
public List <SelectListItem> national()
{
List <SelectListItem> item = new List <SelectListItem>();
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT ps_national_id, ps_id FROM tab_personal", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
item.Add(new SelectListItem {
Text = rdr["ps_national_id"].ToString(), Value = rdr["ps_id"].ToString()
});
}
conn.Close();
}
item.Insert(0, new SelectListItem {
Text = "ค้นหาจากเลขประจำตัวประชาชน", Value = "0"
});
return(item);
}
public void select_emp(string emp_id)
{
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_emp WHERE ep_id = '" + emp_id + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ep_id = rdr["ep_id"].ToString();
ep_code = rdr["ep_code"].ToString();
ep_ref_personal_id = rdr["ep_ref_personal_id"].ToString();
ep_ref_type_id = rdr["ep_ref_type_id"].ToString();
ep_start = rdr["ep_start"].ToString();
ep_end = rdr["ep_end"].ToString();
ep_status = rdr["ep_status"].ToString();
ep_submit_date = rdr["ep_submit_date"].ToString();
ep_ref_admin_id = rdr["ep_ref_admin_id"].ToString();
event_status = rdr["event_status"].ToString();
}
conn.Close();
}
}
public void select_resign_position()
{
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_emp_position_resign WHERE pres_id = '" + prs_id + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
prs_id = rdr["prs_id"].ToString();
prs_ref_emp_posit_id = rdr["prs_ref_emp_posit_id"].ToString();
prs_ref_emp_id = rdr["prs_ref_emp_id"].ToString();
prs_position_id = rdr["prs_position_id"].ToString();
prs_comp_id = rdr["prs_comp_id"].ToString();
prs_section_id = rdr["prs_section_id"].ToString();
prs_dept_id = rdr["prs_dept_id"].ToString();
prs_type = rdr["prs_type"].ToString();
prs_start_date = rdr["prs_start_date"].ToString();
prs_resign_date = rdr["prs_resign_date"].ToString();
prs_submit_date = rdr["prs_submit_date"].ToString();
prs_admin_id = rdr["prs_admin_id"].ToString();
event_status = rdr["event_status"].ToString();
prs_resign_type = rdr["prs_resign_type"].ToString();
prs_resign_detail = rdr["prs_resign_detail"].ToString();
}
conn.Close();
}
}
public List <info_departmentModel> list_dept()
{
dbFile db = new dbFile();
List <info_departmentModel> obj = new List <info_departmentModel>();
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_department", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
info_departmentModel dt = new info_departmentModel();
dt.dept_id = rdr["dept_id"].ToString();
dt.dept_name = rdr["dept_name"].ToString();
obj.Add(dt);
}
conn.Close();
}
return(obj);
}
public List <info_prefixModels> select_prefix()
{
List <info_prefixModels> objSt = new List <info_prefixModels>();
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_info_prefix WHERE event_status != 'D'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
info_prefixModels pf = new info_prefixModels();
pf.name_th = rdr["prefix_name_th"].ToString();
pf.name_en = rdr["prefix_name_en"].ToString();
pf.prefix_id = rdr["prefix_id"].ToString();
objSt.Add(pf);
}
conn.Close();
}
return(objSt);
}
public void select_resign()
{
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_emp_resign WHERE re_id = '" + re_id + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
re_id = rdr["re_id"].ToString();
re_ref_emp_id = rdr["re_ref_emp_id"].ToString();
re_emp_code = rdr["re_emp_code"].ToString();
re_type = rdr["re_type"].ToString();
re_detail = rdr["re_detail"].ToString();
re_resign_date = rdr["re_resign_date"].ToString();
re_submit_date = rdr["re_submit_date"].ToString();
re_admin_id = rdr["re_admin_id"].ToString();
event_status = rdr["event_status"].ToString();
}
conn.Close();
}
}
public List <emp_resignModel> list_resign()
{
List <emp_resignModel> obj = new List <emp_resignModel>();
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_emp_resign", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
emp_resignModel rs = new emp_resignModel();
rs.re_id = rdr["re_id"].ToString();
rs.re_ref_emp_id = rdr["re_ref_emp_id"].ToString();
rs.re_emp_code = rdr["re_emp_code"].ToString();
rs.re_type = rdr["re_type"].ToString();
rs.re_detail = rdr["re_detail"].ToString();
rs.re_resign_date = rdr["re_resign_date"].ToString();
rs.re_submit_date = rdr["re_submit_date"].ToString();
rs.re_admin_id = rdr["re_admin_id"].ToString();
rs.event_status = rdr["event_status"].ToString();
obj.Add(rs);
}
conn.Close();
}
return(obj);
}
public List <empPositionChangeModel> list_posiotnChane_emp_id()
{
dbFile db = new dbFile();
List <empPositionChangeModel> obj = new List <empPositionChangeModel>();
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT pc_id, pc_emp_id, " +
" pc_start_date, pc_position_id AS p1, ps1.post_name AS p1_name, pc_comp_id AS c1, cp1.T_Company AS c1_name, pc_sect_id AS s1, st1.Section_name AS s1_name, pc_dept_id AS d1, dp1.dept_name AS d1_name, pc_type, tp1.type_name AS t1_name," +
" pc_start_old, pc_position_id_old AS p2, ps2.post_name AS p2_name, pc_company_old AS c2, cp2.T_Company AS c2_name, pc_section_old AS s2, st2.Section_name AS s2_name, pc_dept_old As d2, dp2.dept_name AS d2_name, pc_type_old, tp2.type_name AS t2_name," +
" pc_submit_date " +
" FROM tab_emp_position_change " +
" INNER JOIN tab_position AS ps1 ON ps1.post_id = pc_position_id" +
" INNER JOIN tab_position AS ps2 ON ps2.post_id = pc_position_id_old" +
" INNER JOIN tab_company AS cp1 ON cp1.comp_id = pc_comp_id" +
" INNER JOIN tab_company AS cp2 ON cp2.comp_id = pc_company_old" +
" INNER JOIN tab_section AS st1 ON st1.section_id = pc_sect_id" +
" INNER JOIN tab_section AS st2 ON st2.section_id = pc_section_old" +
" INNER JOIN tab_department AS dp1 ON dp1.dept_id = pc_dept_id" +
" INNER JOIN tab_department AS dp2 ON dp2.dept_id = pc_dept_old" +
" INNER JOIN tab_emp_type AS tp1 ON tp1.type_id = pc_type" +
" INNER JOIN tab_emp_type AS tp2 ON tp2.type_id = pc_type_old" +
" WHERE pc_emp_id = '" + pc_emp_id + "'" +
" ORDER BY pc_submit_date DESC", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
empPositionChangeModel pc = new empPositionChangeModel();
pc.pc_id = rdr["pc_id"].ToString();
pc.pc_emp_id = rdr["pc_emp_id"].ToString();
pc.pc_comp_id = rdr["c1_name"].ToString();
pc.pc_sect_id = rdr["s1_name"].ToString();
pc.pc_dept_id = rdr["d1_name"].ToString();
pc.pc_position_id = rdr["p1_name"].ToString();
pc.pc_type = rdr["t1_name"].ToString();
pc.pc_start_date = Convert.ToDateTime(rdr["pc_start_date"]).ToString("dd-MM-yyyy");
pc.pc_submit_date = rdr["pc_submit_date"].ToString();
pc.pc_position_id_old = rdr["p2_name"].ToString();
pc.pc_company_old = rdr["c2_name"].ToString();
pc.pc_section_old = rdr["s2_name"].ToString();
pc.pc_dept_old = rdr["d2_name"].ToString();
pc.pc_type_old = rdr["t2_name"].ToString();
pc.pc_start_old = Convert.ToDateTime(rdr["pc_start_old"]).ToString("dd-MM-yyyy");
obj.Add(pc);
}
conn.Close();
}
return(obj);
}
public List <EmpPositionResignModel> list_position_resign_emp(string emp)
{
List <EmpPositionResignModel> obj = new List <EmpPositionResignModel>();
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT " +
" rs.prs_id, rs.prs_ref_emp_posit_id, rs.prs_ref_emp_id, rs.prs_start_date, rs.prs_resign_date, rs.prs_submit_date, rs.prs_admin_id, rs.event_status, rs.prs_resign_type, rs.prs_resign_detail, " +
" rs.prs_position_id, pos.post_name AS position_name, rs.prs_comp_id, comp.T_Company AS comp_name, rs.prs_section_id, sect.Section_name AS section_name, rs.prs_dept_id, dept.dept_name AS dept_name, rs.prs_type, type.type_name" +
" FROM tab_emp_position_resign AS rs" +
" INNER JOIN tab_position AS pos ON pos.post_id = rs.prs_position_id" +
" INNER JOIN tab_company AS comp ON comp.comp_id = rs.prs_comp_id" +
" INNER JOIN tab_section AS sect ON sect.section_id = rs.prs_section_id" +
" INNER JOIN tab_department AS dept ON dept.dept_id = rs.prs_dept_id" +
" INNER JOIN tab_emp_type AS type ON type.type_id = rs.prs_type" +
" WHERE rs.prs_ref_emp_id = '" + emp + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
EmpPositionResignModel prs = new EmpPositionResignModel();
prs.prs_id = rdr["prs_id"].ToString();
prs.prs_ref_emp_posit_id = rdr["prs_ref_emp_posit_id"].ToString();
prs.prs_ref_emp_id = rdr["prs_ref_emp_id"].ToString();
prs.prs_position_id = rdr["prs_position_id"].ToString();
prs.prs_comp_id = rdr["prs_comp_id"].ToString();
prs.prs_section_id = rdr["prs_section_id"].ToString();
prs.prs_dept_id = rdr["prs_dept_id"].ToString();
prs.prs_type = rdr["prs_type"].ToString();
prs.prs_start_date = Convert.ToDateTime(rdr["prs_start_date"]).ToString("dd MMMM yyyy");
prs.prs_resign_date = Convert.ToDateTime(rdr["prs_resign_date"]).ToString("dd MMMM yyyy");
prs.prs_submit_date = rdr["prs_submit_date"].ToString();
prs.prs_admin_id = rdr["prs_admin_id"].ToString();
prs.event_status = rdr["event_status"].ToString();
prs.prs_resign_type = rdr["prs_resign_type"].ToString();
prs.prs_resign_detail = rdr["prs_resign_detail"].ToString();
prs.position_name = rdr["position_name"].ToString();
prs.comp_name = rdr["comp_name"].ToString();
prs.section_name = rdr["section_name"].ToString();
prs.dept_name = rdr["dept_name"].ToString();
prs.type_name = rdr["type_name"].ToString();
obj.Add(prs);
}
conn.Close();
}
return(obj);
}
public void loginAccount()
{
dbFile db = new dbFile();
string return_page = "";
try
{
using (SqlConnection conn = new SqlConnection(db.sqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_admin INNER JOIN tab_emp_action ON tab_emp_action.emp_id = tab_admin.emp_id WHERE username = '******' AND password ='******' ", conn);
SqlDataReader rdr = cmd.ExecuteReader();
Boolean flag = false; // กำหนดให้ flag = เท็จ
while (rdr.Read())
{
admin_id = rdr.GetInt32(0);
flag = true;
username = rdr["username"].ToString();
emp_id = rdr["emp_id"].ToString();
admin_status = rdr["admin_status"].ToString();// Session["username"] = name;
}
if (flag) // ถ้าเป็นจริงให้ทำ...
{
controll = "Employee";
action = "Index";
}
else
{
controll = "Login";
action = "Index";
}
conn.Close();
}
}
catch
{
controll = "Login";
action = "Index";
}
}
public databaseClassLOG()
{
dbFile db = new dbFile();
try
{
conn = new SqlConnection(db.sqlConnection_log);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
catch
{
conn.Close();
}
}
public string getdata_personal(string id)
{
dbFile db = new dbFile();
string txt = "";
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM view_personal WHERE ps_id = '" + id + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (rdr["ps_national_id"] != DBNull.Value)
{
txt = rdr["ps_national_id"] + "^" + rdr["prefix_name_th"] + "^" + rdr["prefix_name_en"] + "^" + rdr["ps_name_th"] + " " + rdr["ps_lastname_th"] + "^" + rdr["ps_name_en"] + " " + rdr["ps_lastname_en"];
}
}
}
return(txt);
}
public string empCode(string id)
{
string txt = "";
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT ep_code FROM tab_emp WHERE ep_id = '" + id + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
txt = rdr["ep_code"].ToString();
}
conn.Close();
}
return(txt);
}
public void check_img(string emp_id)
{
dbFile db = new dbFile();
// string txt = "";
using (SqlConnection conn = new SqlConnection(db.sqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_emp_profile_img WHERE img_ref_emp_id = '" + emp_id + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (string.IsNullOrEmpty(rdr["img_ref_emp_id"].ToString()) == true)
{
img_status = "N";
img_name = "face-2.jpg";
}
else
{
img_status = "Y";
img_id = rdr["img_id"].ToString();
img_name = rdr["img_name"].ToString();
img_ref_emp_id = rdr["img_ref_emp_id"].ToString();
img_ref_person_id = rdr["img_ref_person_id"].ToString();
img_ref_admin_id = rdr["img_ref_admin_id"].ToString();
img_submit_date = rdr["img_submit_date"].ToString();
}
}
conn.Close();
}
// return txt;
}
public void select_position_change_id()
{
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FORM tab_emp_position_change WHERE pc_id = '" + pc_id + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
pc_id = rdr["pc_id"].ToString();
pc_emp_id = rdr["pc_emp_id"].ToString();
pc_comp_id = rdr["pc_comp_id"].ToString();
pc_sect_id = rdr["pc_sect_id"].ToString();
pc_dept_id = rdr["pc_dept_id"].ToString();
pc_position_id = rdr["pc_position_id"].ToString();
pc_type = rdr["pc_type"].ToString();
pc_start_date = rdr["pc_start_date"].ToString();
pc_resign_date = rdr["pc_resign_date"].ToString();
pc_admin_id = rdr["pc_admin_id"].ToString();
pc_submit_date = rdr["pc_submit_date"].ToString();
pc_status = rdr["pc_status"].ToString();
pc_active = rdr["pc_active"].ToString();
event_status = rdr["event_status"].ToString();
pc_position_id_old = rdr["pc_position_id_old"].ToString();
pc_company_old = rdr["pc_company_old"].ToString();
pc_section_old = rdr["pc_section_old"].ToString();
pc_dept_old = rdr["pc_dept_old"].ToString();
pc_type_old = rdr["pc_type_old"].ToString();
pc_start_old = rdr["pc_start_old"].ToString();
pc_end_old = rdr["pc_end_old"].ToString();
pc_ref_emp_position_id = rdr["pc_ref_emp_position_id"].ToString();
}
conn.Close();
}
}
public void select_personal(string ps)
{
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tab_personal INNER JOIN tab_info_prefix ON tab_info_prefix.prefix_id = ps_prefix_th WHERE ps_id = '" + ps + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
prefix_th = rdr["ps_prefix_th"].ToString();
prefix_en = rdr["ps_prefix_en"].ToString();
prefix_th_name = rdr["prefix_name_th"].ToString();
prefix_en_name = rdr["prefix_name_en"].ToString();
name_th = rdr["ps_name_th"].ToString();
name_en = rdr["ps_name_en"].ToString();
lastname_th = rdr["ps_lastname_th"].ToString();
lastname_en = rdr["ps_lastname_en"].ToString();
gender = rdr["ps_gender"].ToString();
blood = rdr["ps_blood"].ToString();
national_id = rdr["ps_national_id"].ToString();
national_start = Convert.ToDateTime(rdr["ps_national_date_start"]).ToString("dd/MM/yyyy", th);
national_end = Convert.ToDateTime(rdr["ps_national_date_end"]).ToString("dd/MM/yyyy", th);
birthday = Convert.ToDateTime(rdr["ps_birthday"]).ToString("dd/MM/yyyy", th);
age = (Convert.ToInt32(DateTime.Now.Year) - Convert.ToInt32(Convert.ToDateTime(birthday).Year)).ToString();
nationality = rdr["ps_nationality"].ToString();
race = rdr["ps_race"].ToString();
religion = rdr["ps_religion"].ToString();
marital = rdr["ps_status_marital"].ToString();
event_status = rdr["ps_status_marital"].ToString();
personal_id = rdr["ps_id"].ToString();
}
conn.Close();
}
}
public void countEmpforCode()
{
DateTime dt = Convert.ToDateTime(ep_start);
year = dt.Year.ToString();
month = dt.Month.ToString();
dbFile db = new dbFile();
using (SqlConnection conn = new SqlConnection(db.sqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT COUNT(ep_id) AS numCount FROM tab_emp WHERE YEAR(ep_start) = '" + Convert.ToDateTime(ep_start).ToString("yyyy", en) + "' AND MONTH(ep_start) = '" + Convert.ToDateTime(ep_start).ToString("MM", en) + "'", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
count = (Convert.ToInt32(rdr["numCount"]) + 1);
}
conn.Close();
}
shotYear = Convert.ToDateTime(ep_start).ToString("yy", th);
resultCode = shotYear + month.ToString().PadLeft(2, '0') + count.ToString().PadLeft(2, '0');
}
public string connected()
{
dbFile dbStr = new dbFile();
return(dbStr.sqlConnection);
}
public List <SelectListItem> drop_emp_resign(string emp = "")
{
List <SelectListItem> item = new List <SelectListItem>();
dbFile db = new dbFile();
string where = "";
if (string.IsNullOrEmpty(emp) == true)
{
// where = " WHERE ep_end BETWEEN '" + DateTime.Now.ToString("yyyy-MM-dd", en) + "' AND '3000-01-01'";
where = " WHERE ep_status = 'Y'";
}
int i = 0;
using (SqlConnection conn = new SqlConnection(db.sqlConnection)) {
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT ep_code, ps_name_full, ep_id FROM view_employee " + where + " GROUP BY ep_code, ps_name_full, ep_id", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (string.IsNullOrEmpty(emp) == true)
{
if (i == 0)
{
item.Insert(0, new SelectListItem {
Text = "เลือกพนักงาน"
});
item.Add(new SelectListItem {
Text = rdr["ep_code"] + "|" + rdr["ps_name_full"], Value = rdr["ep_id"].ToString()
});
}
else
{
item.Add(new SelectListItem {
Text = rdr["ep_code"] + "|" + rdr["ps_name_full"], Value = rdr["ep_id"].ToString()
});
}
}
else
{
if (emp == rdr["ep_id"].ToString())
{
item.Add(new SelectListItem {
Text = rdr["ep_code"] + "|" + rdr["ps_name_full"], Value = rdr["ep_id"].ToString(), Selected = true
});
}
else
{
item.Add(new SelectListItem {
Text = rdr["ep_code"] + "|" + rdr["ps_name_full"], Value = rdr["ep_id"].ToString()
});
}
}
i++;
}
conn.Close();
}
return(item);
}
public List <SelectListItem> drop_emp_Fund(string emp = "")
{
List <SelectListItem> item = new List <SelectListItem>();
dbFile db = new dbFile();
string where = "";
if (string.IsNullOrEmpty(emp) == true)
{
// where = " WHERE ep_end BETWEEN '" + DateTime.Now.ToString("yyyy-MM-dd", en) + "' AND '3000-01-01'";
where = " WHERE fund.fr_status = 'Y' ";
}
int i = 0;
using (SqlConnection conn = new SqlConnection(db.sqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT emp.ep_id, emp.ep_code,ps.ps_name_th +' '+ ps.ps_lastname_th AS fullName" +
" FROM tab_emp_fundRegis AS fund" +
" INNER JOIN tab_emp AS emp ON emp.ep_id = fund.fr_ref_emp_id" +
" INNER JOIN tab_personal AS ps ON ps.ps_id = emp.ep_ref_personal_id" +
where +
" GROUP BY emp.ep_id, ps.ps_name_th, ps.ps_lastname_th, emp.ep_code", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (string.IsNullOrEmpty(emp) == true)
{
if (i == 0)
{
item.Insert(0, new SelectListItem {
Text = "เลือกพนักงาน"
});
item.Add(new SelectListItem {
Text = rdr["ep_code"] + "|" + rdr["fullName"], Value = rdr["ep_id"].ToString()
});
}
else
{
item.Add(new SelectListItem {
Text = rdr["ep_code"] + "|" + rdr["fullName"], Value = rdr["ep_id"].ToString()
});
}
}
else
{
if (emp == rdr["ep_id"].ToString())
{
item.Add(new SelectListItem {
Text = rdr["ep_code"] + "|" + rdr["ps_name_full"], Value = rdr["ep_id"].ToString(), Selected = true
});
}
else
{
item.Add(new SelectListItem {
Text = rdr["ep_code"] + "|" + rdr["ps_name_full"], Value = rdr["ep_id"].ToString()
});
}
}
i++;
}
conn.Close();
}
return(item);
}