protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
int status = 0;
string msg = "未知错误";
int wkpID = Convert.ToInt32(Request.Params["wkpID"]);
float wlpReal = float.Parse(Request.Params["wlpReal"]);
int wlpAdmin = Convert.ToInt32(Session["adminID"].ToString());
string wlpTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
LNSql lnSql = new LNSql();
lnSql.conn.Open();
try
{
lnSql.cmd.CommandText = "update wkPay_tb set wlpReal=@wlpReal , wlpAdmin=@wlpAdmin,wlpTime=@wlpTime where wkpID=" + wkpID;
lnSql.cmd.Parameters.AddWithValue("@wlpReal", wlpReal);
lnSql.cmd.Parameters.AddWithValue("@wlpAdmin", wlpAdmin);
lnSql.cmd.Parameters.AddWithValue("@wlpTime", wlpTime);
lnSql.cmd.Parameters.AddWithValue("@wkpID", wkpID);
lnSql.cmd.ExecuteNonQuery();
status = 1;
msg = "保存成功！";
}
catch (Exception ex)
{
msg = ex.Message;
}
finally
{
lnSql.conn.Close();
JsonData jsonData = new JsonData();
jsonData["status"] = status;
jsonData["msg"] = msg;
string echoString = jsonData.ToJson();
Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
Response.Write(echoString);
Response.End();
}
}
else
{
int wkpID = Convert.ToInt32(Request.Params["did"]);
LNSql lnSql = new LNSql();
lnSql.conn.Open();
lnSql.cmd.CommandText = "select wlpReal from wkPay_tb where wkpID=" + wkpID;
lnSql.dr = lnSql.cmd.ExecuteReader();
if (lnSql.dr.Read())
{
if (lnSql.dr["wlpReal"] != null)
{
this.wlpReal.Text = lnSql.dr["wlpReal"].ToString();
}
}
lnSql.conn.Close();
this.wkpID.Value = wkpID.ToString();
}
}
protected void Page_Load(object sender, EventArgs e)
{
LNSql lnSql = new LNSql();
lnSql.conn.Open();
lnSql.cmd.CommandText = "select * from rate_tb ";
lnSql.dr = lnSql.cmd.ExecuteReader();
if (lnSql.dr.Read())
{
this.rOverWork.Text = lnSql.dr["rOverWork"].ToString();
this.rYL.Text = (float.Parse(lnSql.dr["rYL"].ToString()) * 100).ToString();
this.rSYu.Text = (float.Parse(lnSql.dr["rSYu"].ToString()) * 100).ToString();
this.rYiLiao.Text = (float.Parse(lnSql.dr["rYiLiao"].ToString()) * 100).ToString();
this.rSYe.Text = (float.Parse(lnSql.dr["rSYe"].ToString()) * 100).ToString();
this.rGS.Text = (float.Parse(lnSql.dr["rGS"].ToString()) * 100).ToString();
this.rZF.Text = (float.Parse(lnSql.dr["rZF"].ToString()) * 100).ToString();
this.rTaxPoint.Text = ( lnSql.dr["rTaxPoint"].ToString()) ;
}
lnSql.dr.Close();
//设置个税率
lnSql.cmd.CommandText = "select * from rateTax_tb ";
lnSql.da.SelectCommand = lnSql.cmd;
lnSql.da.Fill(lnSql.ds,"rts_tb");
this.RepeatRt.DataSource = lnSql.ds.Tables["rts_tb"].DefaultView;
this.RepeatRt.DataBind();
lnSql.conn.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
int status = 0;
string msg = "未知错误";
string account = Request.Form["account"];
string pwd = Request.Form["pwd"];
SrCom srCom = new SrCom();
//加密
pwd = srCom.HashPassword(pwd);
LNSql lnSql = new LNSql();
lnSql.conn.Open();
lnSql.cmd.CommandText = "select * from admins_tb where adminAccount=@account";
lnSql.cmd.Parameters.AddWithValue("@account",account);
lnSql.dr = lnSql.cmd.ExecuteReader();
if (lnSql.dr.Read())
{
if (pwd == lnSql.dr["adminPwd"].ToString())
{
//登录成功,设置登录sesseion
Session["adminID"] = lnSql.dr["adminID"].ToString();
Session["adminName"] = lnSql.dr["adminName"].ToString();
Session["adminClass"] = lnSql.dr["adminClass"].ToString();
status = 1;
msg = "登录成功";
}
else
{
msg = "密码错误！"; //+ pwd;
}
}
else
{
msg = "账号不存在！";
}
lnSql.conn.Close();
JsonData data = new JsonData();
data["status"] =status;
data["msg"] = msg;
string json = data.ToJson();
Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
Response.Write(json);
Response.End();
}
}
public void ProcessRequest(HttpContext context)
{
int status = 0;
string msg = "未知错误！";
if (context.Request.Params["did"] == "" || context.Request.Params["did"] == null)
{
msg = "未提交删除数据！";
}
else
{
LNSql lnSql = new LNSql();
lnSql.conn.Open();
lnSql.cmd.CommandText = "delete from workers_tb where wkID in (0," + context.Request.Params["did"] + ")";
try
{
int effect = lnSql.cmd.ExecuteNonQuery();
status = 1;
msg = "成功删除" + effect + "条记录！";
}
catch (Exception ex)
{
msg = ex.Message;
}
finally
{
lnSql.conn.Close();
}
}
JsonData jsonData = new JsonData();
jsonData["status"] = status;
jsonData["msg"] = msg;
string echoString = jsonData.ToJson();
context.Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
context.Response.Write(echoString);
context.Response.End();
}
public void ProcessRequest(HttpContext context)
{
int total = 0;
JsonData rows = new JsonData();
if (context.Session["adminID"] == null)
{
context.Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
context.Response.Write("未登录或登录超时！请重新登陆！");
context.Response.End();
}
else
{
int page = 0;
if (context.Request["page"] != null && context.Request["page"] != "")
{
page = Convert.ToInt32(context.Request["page"]) - 1;
}
int pageSize = 15;
if (context.Request["rows"] != null && context.Request["rows"] != "")
{
pageSize = Convert.ToInt32(context.Request["rows"]);
}
string sort = "adminID";
if (context.Request["sort"] != null && context.Request["sort"] != "")
{
sort = (context.Request["sort"]);
}
string order = "asc";
if (context.Request["order"] != null && context.Request["order"] != "")
{
order = (context.Request["order"]);
}
LNSql lnSql = new LNSql();
lnSql.conn.Open();
if (page == 0)
{
lnSql.cmd.CommandText = "select top " + pageSize
+ " * from admins_tb order by " + sort + " " + order + " ";
}
else
{
lnSql.cmd.CommandText = "select top " + pageSize +
" * from admins_tb where adminID not in ( select top " + page * pageSize +
" adminID from admins_tb order by order by " + sort + " " + order + " ) order by " + sort + " " + order + " )";
}
lnSql.dr = lnSql.cmd.ExecuteReader();
if (lnSql.dr.Read())
{
do
{
JsonData row = new JsonData();
row["adminID"] = lnSql.dr["adminID"].ToString();
row["adminName"] = lnSql.dr["adminName"].ToString();
row["adminAccount"] = lnSql.dr["adminAccount"].ToString();
row["adminClass"] = lnSql.dr["adminClass"].ToString();
rows.Add(row);
} while (lnSql.dr.Read());
}
else
{
rows = "";
}
//查询数量
lnSql.dr.Close();
lnSql.cmd.CommandText = "select count(adminID) from admins_tb";
total = Convert.ToInt32(lnSql.cmd.ExecuteScalar());
lnSql.conn.Close();
JsonData jsonData = new JsonData();
jsonData["total"] = total;
jsonData["rows"] = rows;
string echoString = jsonData.ToJson();
context.Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
context.Response.Write(echoString);
context.Response.End();
}
}
public void ProcessRequest(HttpContext context)
{
int total = 0;
JsonData rows = new JsonData();
if (context.Session["adminID"] == null)
{
context.Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
context.Response.Write("未登录或登录超时！请重新登陆！");
context.Response.End();
}
else
{
int y = DateTime.Now.Year;
int m = DateTime.Now.Month;
if (context.Request.Params["y"] == "" || context.Request.Params["y"] == null)
{
}
else
{
y = Convert.ToInt32(context.Request.Params["y"]);
}
if (context.Request.Params["y"] == "" || context.Request.Params["y"] == null)
{
}
else
{
m = Convert.ToInt32(context.Request.Params["m"]);
}
int page = 0;
if (context.Request["page"] != null && context.Request["page"] != "")
{
page = Convert.ToInt32(context.Request["page"]) - 1;
}
int pageSize = 15;
if (context.Request["rows"] != null && context.Request["rows"] != "")
{
pageSize = Convert.ToInt32(context.Request["rows"]);
}
string sort = "wkEntryTime";
if (context.Request["sort"] != null && context.Request["sort"] != "")
{
sort = (context.Request["sort"]);
}
string order = "asc";
if (context.Request["order"] != null && context.Request["order"] != "")
{
order = (context.Request["order"]);
}
LNSql lnSql = new LNSql();
lnSql.conn.Open();
if (page == 0)
{
lnSql.cmd.CommandText = "select top " + pageSize
+ " * FROM workers_tb order by " + sort + " " + order + " ";
}
else
{
lnSql.cmd.CommandText = "select top " + pageSize +
" * FROM workers_tb where wkID not in ( select top " + page * pageSize +
" wkID from from workers_tb order by order by " + sort + " " + order + " ) order by " + sort + " " + order + " )";
}
lnSql.da.SelectCommand = lnSql.cmd;
lnSql.da.Fill(lnSql.ds, "workers_tb");
//wkID字符串
string wstr = "0";
if (lnSql.ds.Tables["workers_tb"].Rows.Count > 0)
{
foreach (DataRow dr in lnSql.ds.Tables["workers_tb"].Rows)
{
wstr += "," + dr["wkID"].ToString();
}
//查询工资情况
lnSql.cmd.CommandText = "select * from ( wkPay_tb left join admins_tb on wkPay_tb.wlpAdmin= admins_tb.adminID ) where pWorker in (" + wstr + ") and wkpYear = " + y + " and wkpMonth = " + m + " ";
lnSql.da.SelectCommand = lnSql.cmd;
lnSql.da.Fill(lnSql.ds, "wkp_tb");
//转化为字符串
foreach (DataRow dr in lnSql.ds.Tables["workers_tb"].Rows)
{
JsonData row = new JsonData();
row["wkID"] = dr["wkID"].ToString();
row["wkName"] = dr["wkName"].ToString();
row["wkPosition"] = dr["wkPosition"].ToString();
row["wkSalary"] = float.Parse( dr["wkSalary"].ToString());
row["wkYear"] = y;
row["wkMonth"] = m;
//工资情况
DataRow[] wkpDRs = (lnSql.ds.Tables["wkp_tb"].Select("pWorker=" + dr["wkID"].ToString()));
if (wkpDRs.Length > 0)
{
row["wkpAll"] = float.Parse(wkpDRs[0]["wkpAll"].ToString());
row["wlpYL"] = float.Parse(wkpDRs[0]["wlpYL"].ToString());
row["wlpSY"] = float.Parse(wkpDRs[0]["wlpSY"].ToString());
row["wlpYLiao"] = float.Parse(wkpDRs[0]["wlpYLiao"].ToString());
row["wlpSYe"] = float.Parse(wkpDRs[0]["wlpSYe"].ToString());
row["wlpGS"] = float.Parse(wkpDRs[0]["wlpGS"].ToString());
row["wlpZF"] = float.Parse(wkpDRs[0]["wlpZF"].ToString());
row["wlpTax"] = float.Parse(wkpDRs[0]["wlpTax"].ToString());
row["wlpSubsidy"] = float.Parse(wkpDRs[0]["wlpSubsidy"].ToString());
row["wlpShould"] = float.Parse(wkpDRs[0]["wlpShould"].ToString());
row["wlpReal"] = float.Parse(wkpDRs[0]["wlpReal"].ToString());
row["wkpID"] =Convert.ToInt32( wkpDRs[0]["wkpID"].ToString());
if (wkpDRs[0]["wlpAdmin"].ToString() == null)
{
row["wlpAdmin"] = "";
row["adminName"] = "未发";
}
else
{
row["wlpAdmin"]= wkpDRs[0]["wlpAdmin"].ToString();
row["adminName"] = wkpDRs[0]["adminName"].ToString();
}
if (wkpDRs[0]["wlpTime"].ToString() == "")
{
row["wlpTime"] = "";
}
else
{
row["wlpTime"] = ((DateTime)wkpDRs[0]["wlpTime"]).ToString("yyyy-MM-dd");
}
}
else
{
row["wkpAll"] = "";
row["wlpYL"] = "";
row["wlpSY"] ="";
row["wlpYLiao"] = "";
row["wlpSYe"] = "";
row["wlpGS"] = "";
row["wlpZF"] = "";
row["wlpTax"] = "";
row["wlpSubsidy"] = "";
row["wlpShould"] = "";
row["wlpReal"] = "";
row["wlpAdmin"] ="";
row["wlpTime"] ="";
row["adminName"] = "未发";
row["wkpID"] = "";
}
rows.Add(row);
}
}
else
{
rows = "";
}
lnSql.cmd.CommandText = "";
//查询数量
lnSql.cmd.CommandText = "select count(wkID) from workers_tb";
total = Convert.ToInt32(lnSql.cmd.ExecuteScalar());
lnSql.conn.Close();
JsonData jsonData = new JsonData();
jsonData["total"] = total;
jsonData["rows"] = rows;
string echoString = jsonData.ToJson();
context.Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
context.Response.Write(echoString);
context.Response.End();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
int status = 0;
string msg = "未知错误！";
//统计
int y = Convert.ToInt32(Request.Params["wkYear"]);
int m = Convert.ToInt32(Request.Params["wkMonth"]);
int wkID = Convert.ToInt32(Request.Params["wkID"]);
//本月补贴
float wlpSubsidy = float.Parse(Request.Params["wlpSubsidy"]);
//查询起薪
LNSql lnSql = new LNSql();
lnSql.conn.Open();
lnSql.cmd.CommandText = "select wkSalary from workers_tb where wkID= " + wkID ;
float wkSalary = float.Parse(lnSql.cmd.ExecuteScalar().ToString());
//查询缺勤次数
float unwork = 0;
lnSql.cmd.CommandText = "select uwkTimes from unworks_tb where uworker= " + wkID + " and uwkYear= " + y + " and uwkMonth = "+m;
if (lnSql.cmd.ExecuteScalar()!= null)
{
unwork = float.Parse(lnSql.cmd.ExecuteScalar().ToString());
}
//本月天数
int mday = (m > 12 || m <= 0) ? 0 : (m == 2 ? ((y % 4 == 0 && (y % 100 == 0 ? y % 400 == 0 : true)) ? 29 : 28) : (30 + (m <= 7 ? m : m + 1) % 2));
//查询统计系数
lnSql.cmd.CommandText = "select * from rate_tb ";
lnSql.da.SelectCommand = lnSql.cmd;
lnSql.da.Fill(lnSql.ds,"rate_tb");
//查询加班次数
lnSql.cmd.CommandText = "select owkTimes from overworks_tb where oworker= " + wkID + " and owkYear= " + y + " and owkMonth = " + m;
float owk = 0;
if (lnSql.cmd.ExecuteScalar()!= null)
{
owk = float.Parse(lnSql.cmd.ExecuteScalar().ToString());
}
//统计工资=起薪/本月天数（本月天数-缺勤次数）+加班补贴系数*加班次数
float wkpAll = (wkSalary / mday) * (mday - unwork) + owk * (float.Parse(lnSql.ds.Tables["rate_tb"].Rows[0]["rOverWork"].ToString()));
//五险一金计算：
//养老保险=统计工资*养老保险金缴纳系数
float wlpYL = wkpAll * (float.Parse(lnSql.ds.Tables["rate_tb"].Rows[0]["rYL"].ToString()));
//生育险=统计工资*生育险缴纳系数
float wlpSY = wkpAll * (float.Parse(lnSql.ds.Tables["rate_tb"].Rows[0]["rSYu"].ToString()));
//医疗保险=统计工资*医疗保险缴纳系数
float wlpYLiao = wkpAll * (float.Parse(lnSql.ds.Tables["rate_tb"].Rows[0]["rYiLiao"].ToString()));
//失业保险=统计工资*失业保险缴纳系数
float wlpSYe = wkpAll * (float.Parse(lnSql.ds.Tables["rate_tb"].Rows[0]["rSYe"].ToString()));
//工伤保险=统计工资*工伤保险缴纳系数
float wlpGS = wkpAll * (float.Parse(lnSql.ds.Tables["rate_tb"].Rows[0]["rGS"].ToString()));
//住房公积金=统计工资*住房公积金缴纳系数
float wlpZF = wkpAll * (float.Parse(lnSql.ds.Tables["rate_tb"].Rows[0]["rZF"].ToString()));
//五险一金总额计算：
//五险一金总额=养老保险+生育险+医疗保险+失业保险+工伤保险+住房公积金
float issAll = wlpYL + wlpSY + wlpYLiao + wlpSYe + wlpGS + wlpZF;
//个人所得税计算
//个人所得税=(统计工资- 五险一金总额 – 免征额)*适用税率 - 速算扣除数
//征税额
float forTax = wkpAll - issAll - float.Parse(lnSql.ds.Tables["rate_tb"].Rows[0]["rTaxPoint"].ToString());
float wlpTax;
if (forTax > 0)
{
//查询税率
lnSql.cmd.CommandText = "select * from rateTax_tb order by rt desc";
lnSql.da.SelectCommand = lnSql.cmd;
lnSql.da.Fill(lnSql.ds, "tax_tb");
float tax = 0;
float rtQuick = 0;
int rocount = lnSql.ds.Tables["tax_tb"].Rows.Count;
for (int i = 1; i < rocount; i++)
{
if (i == rocount)
{
tax = float.Parse(lnSql.ds.Tables["tax_tb"].Rows[i - 1]["rt"].ToString());
rtQuick = float.Parse(lnSql.ds.Tables["tax_tb"].Rows[i - 1]["rtQuick"].ToString());
}
else
{
string rtp = lnSql.ds.Tables["tax_tb"].Rows[i]["rtPoint"].ToString();
if (forTax > float.Parse(rtp))
{
tax = float.Parse(lnSql.ds.Tables["tax_tb"].Rows[i - 1]["rt"].ToString());
rtQuick = float.Parse(lnSql.ds.Tables["tax_tb"].Rows[i - 1]["rtQuick"].ToString());
}
}
}
wlpTax = forTax * tax - rtQuick;
}
else
{
wlpTax = 0;
}
//应发工资
//本月员工应发工资 =统计工资 - 五险一金总额 - 个人所得税+补贴额度
float wlpShould = wkpAll - wlpTax - issAll + wlpSubsidy;
//保存至数据库
OleDbTransaction trans = lnSql.conn.BeginTransaction();
lnSql.cmd.Transaction = trans;
try
{
//删除原有当年月数据
lnSql.cmd.CommandText = "delete from wkPay_tb where pWorker = " +
wkID + "and wkpYear= " + y
+ " and wkpMonth = " + m;
lnSql.cmd.ExecuteNonQuery();
//插入新数据
lnSql.cmd.CommandText = "insert into wkPay_tb(pWorker,wkpYear,wkpMonth,wkpAll,wlpYL,wlpSY,wlpYLiao,wlpSYe,wlpGS,wlpZF,wlpTax,wlpSubsidy,wlpShould) values (@pWorker,@wkpYear,@wkpMonth,@wkpAll,@wlpYL,@wlpSY,@wlpYLiao,@wlpSYe,@wlpGS,@wlpZF,@wlpTax,@wlpSubsidy,@wlpShould) ";
lnSql.cmd.Parameters.Clear();
lnSql.cmd.Parameters.AddWithValue("@pWorker", wkID);
lnSql.cmd.Parameters.AddWithValue("@wkpYear", y);
lnSql.cmd.Parameters.AddWithValue("@wkpMonth", m);
lnSql.cmd.Parameters.AddWithValue("@wkpAll", wkpAll);
lnSql.cmd.Parameters.AddWithValue("@wlpYL", wlpYL);
lnSql.cmd.Parameters.AddWithValue("@wlpSY", wlpSY);
lnSql.cmd.Parameters.AddWithValue("@wlpYLiao", wlpYLiao);
lnSql.cmd.Parameters.AddWithValue("@wlpSYe", wlpSYe);
lnSql.cmd.Parameters.AddWithValue("@wlpGS", wlpGS);
lnSql.cmd.Parameters.AddWithValue("@wlpZF", wlpZF);
lnSql.cmd.Parameters.AddWithValue("@wlpTax", wlpTax);
lnSql.cmd.Parameters.AddWithValue("@wlpSubsidy", wlpSubsidy);
lnSql.cmd.Parameters.AddWithValue("@wlpShould", wlpShould);
lnSql.cmd.ExecuteNonQuery();
trans.Commit();
status = 1;
msg = "统计完成";
}
catch (Exception ex)
{
msg = ex.Message;
trans.Rollback();
}
finally
{
lnSql.conn.Close();
}
JsonData jsonData = new JsonData();
jsonData["status"] = status;
jsonData["msg"] = msg;
string echoString = jsonData.ToJson();
Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
Response.Write(echoString);
Response.End();
}
else
{
int y = Convert.ToInt32(Request.Params["wy"]);
int m = Convert.ToInt32(Request.Params["wm"]);
int wkID = Convert.ToInt32(Request.Params["did"]);
LNSql lnSql = new LNSql();
lnSql.conn.Open();
lnSql.cmd.CommandText = "select wlpSubsidy from wkPay_tb where pWorker= " + wkID + "and wkpYear= " + y + "and wkpMonth = " + m;
lnSql.dr = lnSql.cmd.ExecuteReader();
if (lnSql.dr.Read())
{
this.wlpSubsidy.Text = lnSql.dr["wlpSubsidy"].ToString();
}
else
{
this.wlpSubsidy.Text = "0";
}
this.wkID.Value = wkID.ToString();
this.wkMonth.Value = m.ToString();
this.wkYear.Value = y.ToString();
lnSql.dr.Close();
lnSql.conn.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
//验证登录
if (Session["adminID"] == null)
{
this.FormPanel.Visible = false;
this.LogOnLink.Visible = true;
}
else
{
this.FormPanel.Visible = true;
this.LogOnLink.Visible =false;
if (IsPostBack)
{
int status = 0;
string msg = "未知错误！";
string wkName = Request.Form["wkName"];
string wkEntryTime = Request.Form["wkEntryTime"];
string wkPosition = Request.Form["wkPosition"];
string wkSalary = Request.Form["wkSalary"];
LNSql lnSql = new LNSql();
lnSql.conn.Open();
if (Request.Form["wkID"] == "" || Request.Form["wkID"] == null)
{
//新增
lnSql.cmd.CommandText = "insert into workers_tb (wkName,wkEntryTime,wkPosition,wkSalary) values(@wkName,@wkEntryTime,@wkPosition,@wkSalary)";
lnSql.cmd.Parameters.AddWithValue("@wkName", wkName);
lnSql.cmd.Parameters.AddWithValue("@wkEntryTime", wkEntryTime);
lnSql.cmd.Parameters.AddWithValue("@wkPosition", wkPosition);
lnSql.cmd.Parameters.AddWithValue("@wkSalary", wkSalary);
}
else
{
//修改
int wkID = Convert.ToInt32(Request.Params["wkID"]);
lnSql.cmd.CommandText = "update workers_tb set wkName=@wkName,wkEntryTime=@wkEntryTime,wkPosition=@wkPosition,wkSalary=@wkSalary where wkID=@wkID";
lnSql.cmd.Parameters.AddWithValue("@wkName", wkName);
lnSql.cmd.Parameters.AddWithValue("@wkEntryTime", wkEntryTime);
lnSql.cmd.Parameters.AddWithValue("@wkPosition", wkPosition);
lnSql.cmd.Parameters.AddWithValue("@wkSalary", wkSalary);
lnSql.cmd.Parameters.AddWithValue("@wkID", wkID);
}
try
{
lnSql.cmd.ExecuteNonQuery();
status = 1;
msg = "保存成功！";
}
catch (Exception ex)
{
msg = ex.Message;
}
finally
{
JsonData jsonData = new JsonData();
jsonData["status"] = status;
jsonData["msg"] = msg;
string echoString = jsonData.ToJson();
Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
Response.Write(echoString);
Response.End();
}
}
else
{
if (Request.Params["did"] == "" || Request.Params["did"] == null)
{
//新增员工
this.wkEntryTime.Text = DateTime.Now.ToString("yyyy-MM-dd");
}
else
{
int wkID = Convert.ToInt32(Request.Params["did"]);
this.wkID.Value = wkID.ToString();
LNSql lnSql = new LNSql();
lnSql.conn.Open();
lnSql.cmd.CommandText = "select * from workers_tb where wkID="+wkID;
lnSql.dr = lnSql.cmd.ExecuteReader();
if (lnSql.dr.Read())
{
this.wkName.Text=lnSql.dr["wkName"].ToString();
this.wkPosition.Text=lnSql.dr["wkPosition"].ToString();
this.wkSalary.Text=lnSql.dr["wkSalary"].ToString();
this.wkEntryTime.Text=((DateTime)lnSql.dr["wkEntryTime"]).ToString("yyyy-MM-dd");
}
lnSql.dr.Close();
lnSql.conn.Close();
//修改员工
int did = Convert.ToInt32(Request.Params["did"]);
}
}
}
}
public void ProcessRequest(HttpContext context)
{
int total = 0;
JsonData rows = new JsonData();
if (context.Session["adminID"] == null)
{
context.Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
context.Response.Write("未登录或登录超时！请重新登陆！");
context.Response.End();
}
else
{
int y = DateTime.Now.Year;
int m = DateTime.Now.Month;
if (context.Request.Params["y"] == "" || context.Request.Params["y"] == null)
{
}
else
{
y = Convert.ToInt32(context.Request.Params["y"]);
}
if (context.Request.Params["y"] == "" || context.Request.Params["y"] == null)
{
}
else
{
m = Convert.ToInt32(context.Request.Params["m"]);
}
int page = 0;
if (context.Request["page"] != null && context.Request["page"] != "")
{
page = Convert.ToInt32(context.Request["page"]) - 1;
}
int pageSize = 15;
if (context.Request["rows"] != null && context.Request["rows"] != "")
{
pageSize = Convert.ToInt32(context.Request["rows"]);
}
string sort = "wkEntryTime";
if (context.Request["sort"] != null && context.Request["sort"] != "")
{
sort = (context.Request["sort"]);
}
string order = "asc";
if (context.Request["order"] != null && context.Request["order"] != "")
{
order = (context.Request["order"]);
}
LNSql lnSql = new LNSql();
lnSql.conn.Open();
if (page == 0)
{
lnSql.cmd.CommandText = "select top " + pageSize
+ " * FROM workers_tb order by " + sort + " " + order + " ";
}
else
{
lnSql.cmd.CommandText = "select top " + pageSize +
" * FROM workers_tb where wkID not in ( select top " + page * pageSize +
" wkID from from workers_tb order by order by " + sort + " " + order + " ) order by " + sort + " " + order + " )";
}
lnSql.da.SelectCommand = lnSql.cmd;
lnSql.da.Fill(lnSql.ds,"workers_tb");
//wkID字符串
string wstr = "0";
if (lnSql.ds.Tables["workers_tb"].Rows.Count > 0)
{
foreach (DataRow dr in lnSql.ds.Tables["workers_tb"].Rows)
{
wstr+=","+dr["wkID"].ToString();
}
//查询缺勤情况
lnSql.cmd.CommandText = "select * from unworks_tb where uworker in ("+wstr+") and uwkYear = "+y+" and uwkMonth = "+ m+" ";
lnSql.da.SelectCommand = lnSql.cmd;
lnSql.da.Fill(lnSql.ds,"uwks_tb");
//查询加班情况
lnSql.cmd.CommandText = "select * from overworks_tb where oworker in (" + wstr + ") and owkYear = " + y + " and owkMonth = " + m + " ";
lnSql.da.SelectCommand = lnSql.cmd;
lnSql.da.Fill(lnSql.ds, "owks_tb");
//转化为字符串
foreach (DataRow dr in lnSql.ds.Tables["workers_tb"].Rows)
{
JsonData row = new JsonData();
row["wkID"] = dr["wkID"].ToString();
row["wkName"] = dr["wkName"].ToString();
row["wkPosition"] = dr["wkPosition"].ToString();
row["wkYear"] = y;
row["wkMonth"] = m;
//缺勤
DataRow[] uwkDRs=(lnSql.ds.Tables["uwks_tb"].Select("uworker=" + dr["wkID"].ToString()));
if(uwkDRs.Length>0){
row["uwkTimes"] = float.Parse(uwkDRs[0]["uwkTimes"].ToString());
}else{
row["uwkTimes"] = 0;
}
//缺勤
DataRow[] owkDRs = (lnSql.ds.Tables["owks_tb"].Select("oworker=" + dr["wkID"].ToString()));
if (owkDRs.Length > 0)
{
row["owkTimes"] = float.Parse(owkDRs[0]["owkTimes"].ToString());
}
else
{
row["owkTimes"] = 0;
}
rows.Add(row);
}
}
else
{
rows = "";
}
lnSql.cmd.CommandText = "";
//查询数量
lnSql.cmd.CommandText = "select count(wkID) from workers_tb";
total = Convert.ToInt32(lnSql.cmd.ExecuteScalar());
lnSql.conn.Close();
JsonData jsonData = new JsonData();
jsonData["total"] = total;
jsonData["rows"] = rows;
string echoString = jsonData.ToJson();
context.Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
context.Response.Write(echoString);
context.Response.End();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
int status = 0;
string msg = "未定义错误";
SrCom srCom = new SrCom();
LNSql lnSql = new LNSql();
lnSql.conn.Open();
//保存提交数据
try
{
int resetPwd = Convert.ToInt32(Request.Params["resetPwd"]);
string adminName = Request.Params["adminName"];
string adminAccount = Request.Params["adminAccount"];
string adminCpPwd = srCom.HashPassword(Request.Params["adminCpPwd"]);
string adminPWD = srCom.HashPassword(Request.Params["adminPWD"]);
if (Request.Params["adminID"] == "" || Request.Params["adminID"] == null)
{
//添加用户，全部初始化密码
lnSql.cmd.CommandText = "insert into admins_tb (adminName,adminAccount,adminPWD) values(@adminName,@adminAccount,@adminPWD)";
lnSql.cmd.Parameters.AddWithValue("@adminName", adminName);
lnSql.cmd.Parameters.AddWithValue("@adminAccount", adminAccount);
lnSql.cmd.Parameters.AddWithValue("@adminPWD", adminPWD);
lnSql.cmd.ExecuteNonQuery();
}
else
{
int adminID = Convert.ToInt32(Request.Params["adminID"]);
//修改用户
if (resetPwd == 1)
{
lnSql.cmd.CommandText = "update admins_tb set adminName=@adminName,adminAccount=@adminAccount,adminPWD=@adminPWD where adminID=@adminID";
lnSql.cmd.Parameters.AddWithValue("@adminName", adminName);
lnSql.cmd.Parameters.AddWithValue("@adminAccount", adminAccount);
lnSql.cmd.Parameters.AddWithValue("@adminPWD", adminPWD);
lnSql.cmd.Parameters.AddWithValue("@adminID", adminID);
}
else
{
lnSql.cmd.CommandText = "update admins_tb set adminName=@adminName,adminAccount=@adminAccount where adminID=@adminID";
lnSql.cmd.Parameters.AddWithValue("@adminName", adminName);
lnSql.cmd.Parameters.AddWithValue("@adminAccount", adminAccount);
lnSql.cmd.Parameters.AddWithValue("@adminID", adminID);
}
lnSql.cmd.ExecuteNonQuery();
}
status = 1;
msg = "保存成功！";
}
catch (Exception ex)
{
msg = ex.Message;
}
finally
{
lnSql.conn.Close();
JsonData jsonData = new JsonData();
jsonData["status"] = status;
jsonData["msg"] = msg;
string echoString = jsonData.ToJson();
Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
Response.Write(echoString);
Response.End();
}
}
else
{
//初始化页面
if (Request.Params["did"] == null || Request.Params["did"] == "")
{
//初始化增加页面
this.adminCpPwd.TextMode = TextBoxMode.Password;
this.adminPWD.TextMode = TextBoxMode.Password;
this.adminPWD.Text = "12345";
this.adminCpPwd.Text ="12345";
}
else
{
//初始化修改页面
int adminID = Convert.ToInt32(Request.Params["did"]);
LNSql lnSql = new LNSql();
lnSql.conn.Open();
lnSql.cmd.CommandText = "select * from admins_tb where adminID="+adminID;
lnSql.dr = lnSql.cmd.ExecuteReader();
if (lnSql.dr.Read())
{
this.adminID.Value = adminID.ToString();
this.adminName.Text = lnSql.dr["adminName"].ToString();
this.adminAccount.Text = lnSql.dr["adminAccount"].ToString();
}
this.adminCpPwd.TextMode = TextBoxMode.Password;
this.adminPWD.TextMode = TextBoxMode.Password;
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
int status = 0;
string msg = "未知错误！";
int y = Convert.ToInt32(Request.Form["wkY"]);
int m = Convert.ToInt32(Request.Form["wkM"]);
int wkID = Convert.ToInt32(Request.Form["wkID"]);
float uwkTimes = float.Parse(Request.Form["uwkTimes"]);
float owkTimes = float.Parse(Request.Form["owkTimes"]);
LNSql lnSql = new LNSql();
lnSql.conn.Open();
OleDbTransaction trans = lnSql.conn.BeginTransaction();
lnSql.cmd.Transaction = trans;
try
{
//删除缺勤记录
lnSql.cmd.CommandText = "delete from unworks_tb where uworker = @wkID and uwkYear=@y and uwkMonth=@m ";
lnSql.cmd.Parameters.Clear();
lnSql.cmd.Parameters.AddWithValue("@wkID", wkID);
lnSql.cmd.Parameters.AddWithValue("@y", y);
lnSql.cmd.Parameters.AddWithValue("@m", m);
lnSql.cmd.ExecuteNonQuery();
//插入新缺勤记录
lnSql.cmd.CommandText = "insert into unworks_tb (uworker , uwkYear,uwkMonth , uwkTimes)values( @wkID, @y , @m ,@uwkTimes) ";
lnSql.cmd.Parameters.Clear();
lnSql.cmd.Parameters.AddWithValue("@wkID", wkID);
lnSql.cmd.Parameters.AddWithValue("@y", y);
lnSql.cmd.Parameters.AddWithValue("@m", m);
lnSql.cmd.Parameters.AddWithValue("@uwkTimes", uwkTimes);
lnSql.cmd.ExecuteNonQuery();
//删除加班记录
lnSql.cmd.CommandText = "delete from overworks_tb where oworker = @wkID and owkYear=@y and owkMonth=@m ";
lnSql.cmd.Parameters.Clear();
lnSql.cmd.Parameters.AddWithValue("@wkID", wkID);
lnSql.cmd.Parameters.AddWithValue("@y", y);
lnSql.cmd.Parameters.AddWithValue("@m", m);
lnSql.cmd.ExecuteNonQuery();
//插入新加班记录
lnSql.cmd.CommandText = "insert into overworks_tb (oworker , owkYear,owkMonth , owkTimes)values( @wkID, @y , @m ,@owkTimes) ";
lnSql.cmd.Parameters.Clear();
lnSql.cmd.Parameters.AddWithValue("@wkID", wkID);
lnSql.cmd.Parameters.AddWithValue("@y", y);
lnSql.cmd.Parameters.AddWithValue("@m", m);
lnSql.cmd.Parameters.AddWithValue("@owkTimes", owkTimes);
lnSql.cmd.ExecuteNonQuery();
trans.Commit();
status = 1;
msg = "保存成功！";
}
catch (Exception ex)
{
msg = ex.Message;
trans.Rollback();
}
finally
{
lnSql.conn.Close();
JsonData jsonData = new JsonData();
jsonData["status"] = status;
jsonData["msg"] = msg;
string echoString = jsonData.ToJson();
Response.AddHeader("Content-Type", "text/html; charset=UTF-8");
Response.Write(echoString);
Response.End();
}
}
else
{
int y = Convert.ToInt32(Request.Params["wy"]);
int m = Convert.ToInt32(Request.Params["wm"]);
int wkID = Convert.ToInt32(Request.Params["did"]);
LNSql lnSql = new LNSql();
lnSql.conn.Open();
//查询缺勤记录
lnSql.cmd.CommandText = "select uwkTimes from unworks_tb where uworker = @wkID and uwkYear=@y and uwkMonth=@m ";
lnSql.cmd.Parameters.AddWithValue("@wkID",wkID);
lnSql.cmd.Parameters.AddWithValue("@y", y);
lnSql.cmd.Parameters.AddWithValue("@m", m);
lnSql.dr = lnSql.cmd.ExecuteReader();
if (lnSql.dr.Read())
{
this.uwkTimes.Text = lnSql.dr["uwkTimes"].ToString();
}
lnSql.dr.Close();
//查询加班记录
lnSql.cmd.CommandText = "select owkTimes from overworks_tb where oworker = @wkID and owkYear=@y and owkMonth=@m ";
lnSql.cmd.Parameters.AddWithValue("@wkID", wkID);
lnSql.cmd.Parameters.AddWithValue("@y", y);
lnSql.cmd.Parameters.AddWithValue("@m", m);
lnSql.dr = lnSql.cmd.ExecuteReader();
if (lnSql.dr.Read())
{
this.owkTimes.Text = lnSql.dr["owkTimes"].ToString();
}
lnSql.dr.Close();
lnSql.conn.Close();
this.wkID.Value = wkID.ToString();
this.wkM.Value = m.ToString();
this.wkY.Value = y.ToString();
}
}