public static void Delete(int id)//发完工资后删除需要删除的员工,先判断是否在删除队列中,如果在,就删除(已经发完工资了) { using (var db2 = new payrollEntities()) { var waits = db2.Database.SqlQuery <waitdelete>("select * from waitdelete"); foreach (var i in waits) { if (id == i.id) { using (var db3 = new payrollEntities()) { db3.Database.ExecuteSqlCommand("delete from employee where id = '" + id + "'"); } using (var db3 = new payrollEntities()) { db3.Database.ExecuteSqlCommand("delete from waitdelete where id = '" + id + "'"); } } } } }
public static string rec(string str) //客户端发来自定义消息,服务端处理消息(sql查询或执行)生成自定义消息并返回 { //strList[0]是消息头部,不同的strList[0]对应不同的操作请求 string[] strList = str.Split(' '); string sql = ""; //预留:检查sql权限及注入 if (strList[0] == "0")//admin login 管理登录 { sql = "select * from admin where account = '" + strList[1] + "' and pwd = '" + strList[2] + "'"; using (var db = new payrollEntities()) { if (db.Database.SqlQuery <admin>(sql).Count <admin>() == 0) { return("0"); } else { return("1"); } } } if (strList[0] == "1")//employee login 员工登录 { sql = "select * from employee where account = '" + strList[1] + "' and pwd = '" + strList[2] + "'"; using (var db = new payrollEntities()) { if (db.Database.SqlQuery <employee>(sql).Count <employee>() == 0) { return("0"); } else { return(db.Database.SqlQuery <employee>(sql).Single().id.ToString()); } } } string sqlStr = ""; string id = ""; if (strList[0] == "2")//create employee 创建员工 { //MessageBox.Show("2 " + account + " " + pwd + " " + name + " " + type + " " + mail + " " + //socialnum + " " + numericUpDown3.Value.ToString() + " " + numericUpDown4.Value.ToString() //+ " " + numericUpDown5.Value.ToString() + " " + phone + " " + hourlyrate + " " + salary + //" " + commissionedrate + " " + numericUpDown6.Value.ToString()); strList[0] = ""; sqlStr = "("; int c = 0; foreach (var i in strList) { if (c == 3) { sqlStr += "null,"; } if (i == "null") { sqlStr += "null,"; } else { sqlStr += "'" + i + "',"; } c++; } sqlStr = sqlStr.Remove(1, 3); sqlStr = sqlStr.Remove(sqlStr.Length - 1, 1) + ")"; try { using (var db = new payrollEntities()) { db.Database.ExecuteSqlCommand("insert into employee values " + sqlStr); id = db.Database.SqlQuery <employee>("select * from employee where account = '" + strList[1] + "'").Single().id.ToString(); //Console.WriteLine(id); db.Database.ExecuteSqlCommand("insert into perference values ('" + id + "','pickup',null,null,null)"); db.Database.ExecuteSqlCommand("insert into vacation values ('" + id + "','10')"); return(id); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "3")//select employee 查询员工 { try { using (var db = new payrollEntities()) { var emp = db.Database.SqlQuery <employee>("select * from employee where id = '" + strList[1] + "'").Single(); //Console.WriteLine(emp.account+" "+emp.pwd + " " + emp.name + " " + emp.type + " " + emp.mail + " " + emp.socialnum + //" " + emp.tax + " " + emp.pension + " " + emp.medical + " " + emp.phone + " " + emp.hourlyrate + " " + emp.salary //+ " " + emp.commisionedrate + " " + emp.hourlimit); return(emp.account + " " + emp.pwd + " " + emp.name + " " + emp.type + " " + emp.mail + " " + emp.socialnum + " " + emp.tax + " " + emp.pension + " " + emp.medical + " " + emp.phone + " " + emp.hourlyrate + " " + emp.salary + " " + emp.commissionedrate + " " + emp.hourlimit); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "4")//update employee 修改员工 { id = strList[1]; sql = "update employee set account = '" + strList[2] + "', pwd = '" + strList[3] + "', name = '" + strList[4] + "', type = '" + strList[5] + "', mail = '" + strList[6] + "', socialnum = '" + strList[7] + "', tax = '" + strList[8] + "', pension = '" + strList[9] + "', medical = '" + strList[10] + "', phone = '" + strList[11] + "', hourlyrate = "; if (strList[12] == "null") { sql += "null" + ", salary = "; } else { sql += "'" + strList[12] + "', salary = "; } if (strList[13] == "null") { sql += "null" + ", commissionedrate = "; } else { sql += "'" + strList[13] + "', commissionedrate = "; } if (strList[14] == "null") { sql += "null" + ", hourlimit = '" + strList[15] + "'"; } else { sql += "'" + strList[14] + "', hourlimit = '" + strList[15] + "'"; } sql += " where id = '" + id + "'"; //Console.WriteLine(sql); try { using (var db = new payrollEntities()) { db.Database.ExecuteSqlCommand(sql); return("1"); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "5")//delete employee 删除员工 { sql = "insert into waitdelete values ('" + strList[1] + "');"; //Console.WriteLine(sql); try { using (var db = new payrollEntities()) { db.Database.ExecuteSqlCommand(sql); return("1"); } } catch { return("0"); } } if (strList[0] == "6")//create order 创建订购单 { //string rec = Client.Rec("6 " + id + " " + contact + " " + address + " " + product + " " + date + " " + amount); sql = "insert into purchaseorder values (null,'" + strList[1] + "','" + strList[2] + "','" + strList[3] + "','" + strList[4] + "','" + strList[5] + "','" + strList[6] + "','open')"; //Console.WriteLine(sql); try { using (var db = new payrollEntities()) { db.Database.ExecuteSqlCommand(sql); return(db.Database.SqlQuery <purchaseorder>("select * from purchaseorder where pid = (select max(pid) from purchaseorder)").Single().pid.ToString()); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "7")//select order 查询订购单 { sql = "select * from purchaseorder where pid ='" + strList[1] + "'"; //Console.WriteLine(sql); try { using (var db = new payrollEntities()) { var i = db.Database.SqlQuery <purchaseorder>(sql).Single(); //Console.WriteLine(i.id.ToString() + " " + i.contact + " " + i.address + " " + i.product + " " + i.date.GetDateTimeFormats()[0] + " " + i.amount.ToString() + " " + i.status); return(i.id.ToString() + " " + i.contact + " " + i.address + " " + i.product + " " + i.date.GetDateTimeFormats()[0] + " " + i.amount.ToString() + " " + i.status); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "8")//update order 修改订购单 { //string rec = Client.Rec("8 " + ppid + " " + contact + " " + address + " " + product + " " + date + " " + amount); sql = "update purchaseorder set contact = '" + strList[2] + "',address = '" + strList[3] + "',product = '" + strList[4] + "',`date` = '" + strList[5] + "',amount = '" + strList[6] + "' where pid = '" + strList[1] + "'"; //Console.WriteLine(sql); try { using (var db = new payrollEntities()) { db.Database.ExecuteSqlCommand(sql); return("1"); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "9")//delete purchaseorder 删除订购单 { sql = "delete from purchaseorder where pid = '" + strList[1] + "'"; try { using (var db = new payrollEntities()) { db.Database.ExecuteSqlCommand(sql); return("1"); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "10")//update perference 修改员工个人偏好(支付方式) { //string rec = Client.Rec("10 " + pid + " " + payment + " " + address + " " + bankaccount + " " + bankname); if (strList[2] == "pickup") { sql = "update perference set payment = '" + strList[2] + "',address = null,bankaccount = null,bankname = null where id = '" + strList[1] + "'"; } if (strList[2] == "mail") { sql = "update perference set payment = '" + strList[2] + "',address = '" + strList[3] + "',bankaccount = null,bankname = null where id = '" + strList[1] + "'"; } if (strList[2] == "deposit") { sql = "update perference set payment = '" + strList[2] + "',address = null,bankaccount = '" + strList[4] + "',bankname = '" + strList[5] + "' where id = '" + strList[1] + "'"; } //Console.WriteLine(sql); try { using (var db = new payrollEntities()) { db.Database.ExecuteSqlCommand(sql); return("1"); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "11")//select timecard 查询考勤表,如果不存在则生成一个新的考勤表,记账编号为0时是所有记账编号对应这个员工在该考勤时间中的总和统计,为了便于检查工作时间是否超时 { DateTime mon = Clock.GetWeekFirstDayMon(); DateTime fri = Clock.GetWeekLastDayFri(); //Console.WriteLine(mon); //Console.WriteLine(fri); sql = "select * from timecard where id = '" + strList[1] + "' and `begin` = '" + mon + "' and `end` = '" + fri + "'"; try { using (var db = new payrollEntities()) { if (db.Database.SqlQuery <timecard>(sql).Count <timecard>() == 0) { db.Database.ExecuteSqlCommand("insert into timecard (id,`begin`,`end`,`status`) values ('" + strList[1] + "','" + mon + "','" + fri + "','submitted')"); } string re = mon + " " + fri; using (var db2 = new projectmanagementEntities()) { var projects = db2.Database.SqlQuery <project>("select * from project"); foreach (var i in projects) { re += " " + i.chargenum.ToString();//返回可用的记账编号 } } return(re); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "12")//select timecard 查询考勤表,根据记账编号查询考勤表 { sql = "select * from timecard where id = '" + strList[1] + "' and `begin` = '" + strList[3] + "' and `end` = '" + strList[5] + "' and chargenum = '" + strList[2] + "'"; try { using (var db = new payrollEntities()) { if (db.Database.SqlQuery <timecard>(sql).Count <timecard>() == 0) { db.Database.ExecuteSqlCommand("insert into timecard (id,`begin`,`end`,chargenum) values ('" + strList[1] + "','" + strList[3] + "','" + strList[5] + "','" + strList[2] + "')"); } var i = db.Database.SqlQuery <timecard>(sql).Single(); string re = ""; if (i.status == "submitted") { re = "submitted "; } re += i.mon + " " + i.tue + " " + i.wed + " " + i.thu + " " + i.fri + " " + i.tid; return(re); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "13")//update timecard 保存考勤表信息,保存时还未提交,无需检查工作时间是否超时 { sql = "update timecard set mon = '" + strList[1] + "',tue = '" + strList[2] + "',wed = '" + strList[3] + "',thu = '" + strList[4] + "',fri = '" + strList[5] + "' where tid = '" + strList[6] + "'"; try { using (var db = new payrollEntities()) { db.Database.ExecuteSqlCommand(sql); return("1"); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "14")//update timecard 提交考勤表信息,检查是否超时工作,同时将考勤表状态置为submitted { try { using (var db = new payrollEntities()) { var t0 = db.Database.SqlQuery <timecard>("select * from timecard where tid = '" + strList[6] + "'").Single(); var e0 = db.Database.SqlQuery <employee>("select * from employee where id = '" + t0.id + "'").Single(); int hourlimit = Convert.ToInt32(e0.hourlimit); var i0 = db.Database.SqlQuery <timecard>("select * from timecard where chargenum = '0' and id = '" + t0.id + "' and `begin` = '" + t0.begin + "' and end = '" + t0.end + "'").Single(); long[] a0 = new long[5]; a0[0] = i0.mon + long.Parse(strList[1]); a0[1] = i0.tue + long.Parse(strList[2]); a0[2] = i0.wed + long.Parse(strList[3]); a0[3] = i0.thu + long.Parse(strList[4]); a0[4] = i0.fri + long.Parse(strList[5]); long atime = a0[0] + a0[1] + a0[2] + a0[3] + a0[4]; long time = long.Parse(strList[1]) + long.Parse(strList[2]) + long.Parse(strList[3]) + long.Parse(strList[4]) + long.Parse(strList[5]); for (int i = 0; i < 5; ++i) { if (a0[i] > hourlimit) { return((i + 1).ToString()); } } DateTime now = DateTime.Now; sql = "update timecard set `status` = 'submitted',time = '" + time + "',subtime = '" + now.ToString() + "',mon = '" + strList[1] + "',tue = '" + strList[2] + "',wed = '" + strList[3] + "',thu = '" + strList[4] + "',fri = '" + strList[5] + "' where tid = '" + strList[6] + "'"; db.Database.ExecuteSqlCommand(sql); sql = "update timecard set time = '" + atime + "',mon = '" + a0[0] + "',tue = '" + a0[1] + "',wed = '" + a0[2] + "',thu = '" + a0[3] + "',fri = '" + a0[4] + "' where chargenum = '0' and id = '" + t0.id + "' and `begin` = '" + t0.begin + "' and `end` = '" + t0.end + "'"; db.Database.ExecuteSqlCommand(sql); return("ok"); } } catch (Exception ex) { Console.WriteLine(ex); return("0"); } } if (strList[0] == "15")//select chargenum 查询可用的记账编号 { try { using (var db2 = new projectmanagementEntities()) { var projects = db2.Database.SqlQuery <project>("select * from project"); string re = "ok"; foreach (var i in projects) { re += " " + i.chargenum.ToString();//返回可用的记账编号 } return(re); } } catch { return("0"); } } if (strList[0] == "16")//生成报表 总工作时间 { //string str = "16 "+id+" "+begin.Date+" "+end.Date; DateTime begin = Convert.ToDateTime(strList[2]); DateTime end = Convert.ToDateTime(strList[4]); try { using (var db = new payrollEntities()) { var timecards = db.Database.SqlQuery <timecard>("select * from timecard where id = '" + strList[1] + "' and chargenum = '0'"); long ans = 0; foreach (var i in timecards) { if (i.begin > end) { continue; } if (i.end < begin) { continue; } DateTime j = begin; //Console.WriteLine(i.begin); while (j <= end) { if (i.begin <= j && j <= i.end)//i.begin j i.end { if (j.DayOfWeek.ToString() == "Monday") { ans += i.mon; } if (j.DayOfWeek.ToString() == "Tuesday") { ans += i.tue; } if (j.DayOfWeek.ToString() == "Wednesday") { ans += i.wed; } if (j.DayOfWeek.ToString() == "Thursday") { ans += i.thu; } if (j.DayOfWeek.ToString() == "Friday") { ans += i.fri; } //Console.WriteLine(j); } j = j.AddDays(1); } } return(ans.ToString()); } } catch (Exception ex) { Console.WriteLine(ex); return("fail"); } } if (strList[0] == "17")//生成报表 项目总工作时间 { DateTime begin = Convert.ToDateTime(strList[2]); DateTime end = Convert.ToDateTime(strList[4]); try { using (var db = new payrollEntities()) { var timecards = db.Database.SqlQuery <timecard>("select * from timecard where id = '" + strList[1] + "' and chargenum = '" + strList[6] + "'"); long ans = 0; foreach (var i in timecards) { if (i.begin > end) { continue; } if (i.end < begin) { continue; } DateTime j = begin; while (j <= end) { if (i.begin <= j && j <= i.end)//i.begin j i.end { if (j.DayOfWeek.ToString() == "Monday") { ans += i.mon; } if (j.DayOfWeek.ToString() == "Tuesday") { ans += i.tue; } if (j.DayOfWeek.ToString() == "Wednesday") { ans += i.wed; } if (j.DayOfWeek.ToString() == "Thursday") { ans += i.thu; } if (j.DayOfWeek.ToString() == "Friday") { ans += i.fri; } //Console.WriteLine(j); } j = j.AddDays(1); } } return(ans.ToString()); } } catch (Exception ex) { Console.WriteLine(ex); return("fail"); } } if (strList[0] == "18")//生成报表 剩余假期 { sql = "select * from vacation where id = '" + strList[1] + "'"; try { using (var db = new payrollEntities()) { var i = db.Database.SqlQuery <vacation>(sql).Single(); return(i.ramain.ToString());//本来应该是remain的,打错了= = } } catch { return("fail"); } } if (strList[0] == "19")//生成报表 总工资 { //string str = "19 " + id + " " + begin.Date + " " + end.Date; DateTime begin = Convert.ToDateTime(strList[2]); DateTime end = Convert.ToDateTime(strList[4]); try { using (var db = new payrollEntities()) { var records = db.Database.SqlQuery <record>("select * from record where id = '" + strList[1] + "'"); long ans = 0; foreach (var i in records) { if (i.date > end) { continue; } if (i.date < begin) { continue; } if (i.date <= end && i.date >= begin)//begin i.date end { ans += i.amount; } } return(ans.ToString()); } } catch (Exception ex) { Console.WriteLine(ex); return("fail"); } } return("0"); }
public static void Payroll(object obj)//自动发工资的程序 { DateTime now = DateTime.Now; using (var db = new payrollEntities())//每小时检查一次未支付账单,支付(这里就是为了防止银行系统无法连接,所以每小时检查一次) { var records = db.Database.SqlQuery <record>("select * from record where `status` = 'no'"); foreach (var r in records) { int id = r.id; employee e; perference p; using (var db2 = new payrollEntities()) { e = db2.Database.SqlQuery <employee>("select * from employee where id = '" + id + "'").Single(); } using (var db2 = new payrollEntities()) { p = db2.Database.SqlQuery <perference>("select * from perference where id = '" + id + "'").Single(); } if (p.payment == "pickup")//直接领取 { Paycheck(e.name, r.amount); Console.WriteLine("pickup : " + e.name + " has been paid by " + DateTime.Now.ToString() + ".The amout is " + r.amount + ". "); mail(e.mail); Delete(id);//查询该员工是否需要删除 using (var db2 = new payrollEntities()) { db2.Database.ExecuteSqlCommand("update record set `status` = 'yes' where pid = '" + r.pid + "'"); } } if (p.payment == "mail")//邮寄支票 { Paycheck(e.name, r.amount, p.address); Console.WriteLine("mail : " + e.name + " has been paid by " + DateTime.Now.ToString() + ".The amout is " + r.amount + ". "); mail(e.mail); Delete(id);//查询该员工是否需要删除 using (var db2 = new payrollEntities()) { db2.Database.ExecuteSqlCommand("update record set `status` = 'yes' where pid = '" + r.pid + "'"); } } if (p.payment == "deposit")//存入银行账户 { if (Deposit(r.amount, p.bankaccount, p.bankname)) { Console.WriteLine("desposit : " + e.name + " has been paid by " + DateTime.Now.ToString() + ".The amout is " + r.amount + ". "); mail(e.mail); Delete(id);//查询该员工是否需要删除 using (var db2 = new payrollEntities()) { db2.Database.ExecuteSqlCommand("update record set `status` = 'yes' where pid = '" + r.pid + "'"); } } } } } if (now >= GetWeekLastDayFri().AddHours(20) && now < GetWeekLastDayFri().AddHours(21)) //每周五20点-21点执行小时工工资计算 //(主程序定时器每小时执行一次),为了避免刚好20点启动程序所以前面有=后面判断没有=,每周只执行一次 { //Console.WriteLine(now); using (var db = new payrollEntities()) { var employees = db.Database.SqlQuery <employee>("select * from employee where type = 'hour'");//小时工 foreach (var e in employees) { timecard t; using (var db2 = new payrollEntities()) { if (db2.Database.SqlQuery <timecard>("select * from timecard where id = '" + e.id + "' and begin = '" + GetWeekFirstDayMon().ToString() + "' and end = '" + GetWeekLastDayFri().ToString() + "' and chargenum = '0'").Count <timecard>() == 0) { return; } else { t = db2.Database.SqlQuery <timecard>("select * from timecard where id = '" + e.id + "' and begin = '" + GetWeekFirstDayMon().ToString() + "' and end = '" + GetWeekLastDayFri().ToString() + "' and chargenum = '0'").Single(); } } double pay = 0; long[] a = new long[5]; a[0] = t.mon; a[1] = t.tue; a[2] = t.wed; a[3] = t.thu; a[4] = t.fri; for (int i = 0; i < 5; ++i) { if (a[i] > 8) { pay += 8 * Convert.ToDouble(e.hourlyrate) + Convert.ToDouble(a[i] - 8) * Convert.ToDouble(e.hourlyrate) * 1.5; } else { pay += Convert.ToDouble(a[i]) * Convert.ToDouble(e.hourlyrate); } } int paya = Convert.ToInt32(Convert.ToInt64(pay) - e.tax - e.pension - e.medical); using (var db2 = new payrollEntities()) { db2.Database.ExecuteSqlCommand("insert into record (id,date,amount,`status`) values ('" + e.id + "','" + now.ToString() + "','" + paya.ToString() + "','no')");//生成工资记录,no表示尚未支付 } } } } if (now >= GetLastWorkingTime().AddHours(20) && now < GetLastWorkingTime().AddHours(21)) //每月最后一个工作日20点-21点执行月薪员工和提成员工工资计算 //(主程序定时器每小时执行一次),为了避免刚好20点启动程序所以前面有=后面判断没有=,每周只执行一次 { //Console.WriteLine(now); using (var db = new payrollEntities()) { var employees = db.Database.SqlQuery <employee>("select * from employee where type = 'salaried'");//月薪员工 foreach (var e in employees) { int paya = Convert.ToInt32(Convert.ToInt64(e.salary) - e.tax - e.pension - e.medical); using (var db2 = new payrollEntities()) { db2.Database.ExecuteSqlCommand("insert into record (id,date,amount,`status`) values ('" + e.id + "','" + now.ToString() + "','" + paya.ToString() + "','no')");//生成工资记录,no表示尚未支付 } } } using (var db = new payrollEntities()) { var employees = db.Database.SqlQuery <employee>("select * from employee where type = 'commissioned'");//提成员工 foreach (var e in employees) { double pay = 0; pay = Convert.ToDouble(Convert.ToInt64(e.salary) - e.tax - e.pension - e.medical); using (var db2 = new payrollEntities()) { var ps = db2.Database.SqlQuery <purchaseorder>("select * from purchaseorder where id = '" + e.id + "' and `status` = 'open'"); foreach (var p in ps) { pay += Convert.ToDouble(p.amount) * Convert.ToDouble(e.commissionedrate); using (var db3 = new payrollEntities()) { db3.Database.ExecuteSqlCommand("update purchaseorder set `status` = 'closed' where pid = '" + p.pid + "'"); } } } int paya = Convert.ToInt32(pay); using (var db2 = new payrollEntities()) { db2.Database.ExecuteSqlCommand("insert into record (id,date,amount,`status`) values ('" + e.id + "','" + now.ToString() + "','" + paya.ToString() + "','no')");//生成工资记录,no表示尚未支付 } } } } }