/// <summary> /// 生成一个报表 /// </summary> protected void btn_report_Click(object sender, EventArgs e) { strStatus.InnerText = ""; string year = slt_year.SelectedValue; string month = slt_month.SelectedValue; /* if (!(year==DateTime.Now.AddMonths(-1).Year.ToString()&&month==DateTime.Now.AddMonths(-1).Month.ToString())) * { * strStatus.InnerText = "只能生成上月个税报表"; * return; * }*/ bll.Delete(year, month);//删除选择月个税数据后重新生成 //获取选择月数据 StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT c.id,c.year,c.month,c.depart,c.departid,c.empid,c.name,c.gzjb,c.yfje,c.kk,c.gjj,c.bxkk,c.zxkc,c.jckk "); strSql.Append(" ,(c.gjj+c.bxkk+c.zxkc+c.jckk) as total "); strSql.Append(" FROM ( "); strSql.Append(" SELECT a.id,a.depart,a.departid,a.year,a.month, a.empid,a.name,a.gzjb,a.yfje,(a.kqtk+a.kk+a.gsk) as kk,(a.gjj-a.gjj2) as gjj,a.bxkk,"); strSql.Append(" ISNULL((b.znjy+b.jxjy+b.dbyl+b.zfdk+b.zfzj+b.sylr),0) as zxkc,5000 as jckk"); strSql.Append(" FROM t_gzmx a "); strSql.Append(" LEFT JOIN t_zxkc b "); strSql.Append(" on a.empid=b.empid and a.year=b.year and a.month=b.month "); //strSql.Append(" where a.year='2019' and a.month='2' "); strSql.Append(" group by a.id,a.depart,a.departid, a.year,a.month, a.empid,a.name,a.gzjb,a.yfje,a.kqtk,a.kk,a.gsk,a.gjj2,a.gjj,a.bxkk, "); strSql.Append(" b.znjy,b.jxjy,b.dbyl,b.zfdk,b.zfzj,b.sylr "); strSql.Append(" ) c "); strSql.Append(" where c.year='" + year + "' and c.month='" + month + "' "); strSql.Append(" group by c.id,c.depart,c.departid,c.year,c.month,c.empid,c.name,c.gzjb,c.yfje,c.kk,c.gjj,c.bxkk,c.zxkc,c.jckk "); double ysgz; //本月应缴个税工资 SqlDataReader reader = DBUtility.DbHelperSql.ExecuteReader(strSql.ToString()); while (reader.Read()) { ysgz = 0; Model.gsjs model = new Model.gsjs(); model.year = year; model.month = month; model.depart = reader["depart"].ToString(); model.departid = reader["departid"].ToString(); model.empid = reader["empid"].ToString(); model.name = reader["name"].ToString(); model.gzjb = reader["gzjb"].ToString(); model.yfje = double.Parse(reader["yfje"].ToString()); model.kk = double.Parse(reader["kk"].ToString()); model.gjj = double.Parse(reader["gjj"].ToString()); model.bxkk = double.Parse(reader["bxkk"].ToString()); model.zxkk = double.Parse(reader["zxkc"].ToString()); model.jckk = double.Parse(reader["jckk"].ToString()); //计算本年累计应税工资,如果是1月份:应发工资-扣除-专项扣除-五险一金-5000基本扣除 // 生成月分不为1月份时: 应发工资-扣除-专项扣除-五险一金-5000基本扣除+ 上月全年累计应税工资 if (month == "1") { ysgz = double.Parse(reader["yfje"].ToString()) - double.Parse(reader["total"].ToString()); //本月应缴个税 // model.ysgzlj = ysgz > 0 ? ysgz : 0; //本年应缴个税工资累计 model.ysgzlj = ysgz; //本年应缴个税工资累计 model.gslj = Common.PITTotal.PITSum(ysgz > 0 ? ysgz : 0); //本年度应缴个税累计 model.bygs = Common.PITTotal.PITSum(ysgz > 0 ? ysgz : 0); //本月应缴个税 } else { ysgz = double.Parse(reader["yfje"].ToString()) - double.Parse(reader["total"].ToString());//本月应缴个税 //获取上月年度应缴工资累计、个税累计 Model.gsjs topMonthModel = bll.GetModel(year, (int.Parse(month) - 1).ToString(), reader["empid"].ToString()); if (topMonthModel != null) { double ysgzlj = 0; // 本年度应缴个税总额 double syndgs = 0; //上月年度应缴个总额个税计算 double byndgs = 0; //本月年度个税 //年度应缴个税工资累计 ysgzlj = ysgz + topMonthModel.ysgzlj; //本年应缴个税工资累计 model.ysgzlj = ysgzlj; //计算本年度个税累计 syndgs = topMonthModel.gslj; //Common.PITTotal.PITSum(topMonthModel.ysgzlj>0?topMonthModel.ysgzlj:0); //上月年度应缴个税 byndgs = Common.PITTotal.PITSum(ysgzlj > 0 ? ysgzlj : 0); //本月年度应缴个税 model.gslj = topMonthModel.gslj + ((byndgs - syndgs) > 0 ? byndgs - syndgs : 0); model.bygs = (byndgs - syndgs) > 0 ? byndgs - syndgs : 0; //model.ysgzlj = (ysgz > 0 ? ysgz : 0) + topMonthModel.ysgzlj; //本年应缴个税工资累计 // model.ysgzlj = ysgz + topMonthModel.ysgzlj; //本年应缴个税工资累计 // model.gslj = Common.PITTotal.PITSum((ysgz + topMonthModel.ysgzlj) > 0 ? (ysgz + topMonthModel.ysgzlj) : 0);//本年度应缴个税累计 //当月个税=本年应缴个税累计-上月本年应缴个税累计 // model.bygs = Common.PITTotal.PITSum((ysgz + topMonthModel.ysgzlj) > 0 ? (ysgz + topMonthModel.ysgzlj) : 0) - topMonthModel.gslj; } else { //如果员工新入职获取上个月的工资报表为空,则当月开始累计 // model.ysgzlj = ysgz > 0 ? ysgz : 0; //本年应缴个税工资累计 model.ysgzlj = ysgz; //本年应缴个税工资累计 model.gslj = Common.PITTotal.PITSum(ysgz > 0 ? ysgz : 0); //本年度应缴个税累计 model.bygs = Common.PITTotal.PITSum(ysgz > 0 ? ysgz : 0); //本月应缴个税 } } bll.Add(model); } reader.Close(); strStatus.InnerText = slt_year.SelectedValue + "年" + slt_month.SelectedValue + "月个税报表已生成。"; }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.gsjs model) { return(dal.Update(model)); }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.gsjs model) { return(dal.Add(model)); }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.gsjs model) { int newId; using (SqlConnection conn = new SqlConnection(DbHelperSql.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into r_jshz("); strSql.Append("year,month,depart,departid,empid,name,gzjb,yfje,kk,gjj,bxkk,zxkk,jckk,ysgzlj,gslj,bygs)"); strSql.Append(" values ("); strSql.Append("@year,@month,@depart,@departid,@empid,@name,@gzjb,@yfje,@kk,@gjj,@bxkk,@zxkk,@jckk,@ysgzlj,@gslj,@bygs)"); SqlParameter[] parameters = { new SqlParameter("@year", SqlDbType.VarChar, 50), new SqlParameter("@month", SqlDbType.VarChar, 50), new SqlParameter("@depart", SqlDbType.VarChar, 50), new SqlParameter("@departid", SqlDbType.VarChar, 50), new SqlParameter("@empid", SqlDbType.VarChar, 50), new SqlParameter("@name", SqlDbType.VarChar, 50), new SqlParameter("@gzjb", SqlDbType.VarChar, 50), new SqlParameter("@yfje", SqlDbType.Decimal), new SqlParameter("@kk", SqlDbType.Decimal), new SqlParameter("@gjj", SqlDbType.Decimal), new SqlParameter("@bxkk", SqlDbType.Decimal), new SqlParameter("@zxkk", SqlDbType.Decimal), new SqlParameter("@jckk", SqlDbType.Decimal), new SqlParameter("@ysgzlj", SqlDbType.Decimal), new SqlParameter("@gslj", SqlDbType.Decimal), new SqlParameter("@bygs", SqlDbType.Decimal) }; parameters[0].Value = model.year; parameters[1].Value = model.month; parameters[2].Value = model.depart; parameters[3].Value = model.departid; parameters[4].Value = model.empid; parameters[5].Value = model.name; parameters[6].Value = model.gzjb; parameters[7].Value = model.yfje; parameters[8].Value = model.kk; parameters[9].Value = model.gjj; parameters[10].Value = model.bxkk; parameters[11].Value = model.zxkk; parameters[12].Value = model.jckk; parameters[13].Value = model.ysgzlj; parameters[14].Value = model.gslj; parameters[15].Value = model.bygs; DbHelperSql.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID newId = GetMaxId(conn, trans); trans.Commit(); } catch { trans.Rollback(); return(-1); } } } return(newId); }
/// <summary> /// 得到一个对象实体 /// </summary> public Model.gsjs GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 id,year,month,depart,departid,empid,name,gzjb,yfje,kk,gjj,bxkk,zxkk,jckk,ysgzlj,gslj,bygs from r_jshz "); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = id; Model.gsjs model = new Model.gsjs(); DataSet ds = DbHelperSql.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "") { model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString()); } if (ds.Tables[0].Rows[0]["year"] != null && ds.Tables[0].Rows[0]["year"].ToString() != "") { model.year = ds.Tables[0].Rows[0]["year"].ToString(); } if (ds.Tables[0].Rows[0]["month"] != null && ds.Tables[0].Rows[0]["month"].ToString() != "") { model.month = ds.Tables[0].Rows[0]["month"].ToString(); } if (ds.Tables[0].Rows[0]["depart"] != null && ds.Tables[0].Rows[0]["depart"].ToString() != "") { model.depart = ds.Tables[0].Rows[0]["depart"].ToString(); } if (ds.Tables[0].Rows[0]["departid"] != null && ds.Tables[0].Rows[0]["departid"].ToString() != "") { model.departid = ds.Tables[0].Rows[0]["departid"].ToString(); } if (ds.Tables[0].Rows[0]["empid"] != null && ds.Tables[0].Rows[0]["empid"].ToString() != "") { model.empid = ds.Tables[0].Rows[0]["empid"].ToString(); } if (ds.Tables[0].Rows[0]["name"] != null && ds.Tables[0].Rows[0]["name"].ToString() != "") { model.name = ds.Tables[0].Rows[0]["name"].ToString(); } if (ds.Tables[0].Rows[0]["gzjb"] != null && ds.Tables[0].Rows[0]["gzjb"].ToString() != "") { model.gzjb = ds.Tables[0].Rows[0]["gzjb"].ToString(); } if (ds.Tables[0].Rows[0]["yfje"] != null && ds.Tables[0].Rows[0]["yfje"].ToString() != "") { model.yfje = double.Parse(ds.Tables[0].Rows[0]["yfje"].ToString()); } if (ds.Tables[0].Rows[0]["kk"] != null && ds.Tables[0].Rows[0]["kk"].ToString() != "") { model.kk = double.Parse(ds.Tables[0].Rows[0]["kk"].ToString()); } if (ds.Tables[0].Rows[0]["gjj"] != null && ds.Tables[0].Rows[0]["gjj"].ToString() != "") { model.gjj = double.Parse(ds.Tables[0].Rows[0]["gjj"].ToString()); } if (ds.Tables[0].Rows[0]["bxkk"] != null && ds.Tables[0].Rows[0]["bxkk"].ToString() != "") { model.bxkk = double.Parse(ds.Tables[0].Rows[0]["bxkk"].ToString()); } if (ds.Tables[0].Rows[0]["zxkk"] != null && ds.Tables[0].Rows[0]["zxkk"].ToString() != "") { model.zxkk = double.Parse(ds.Tables[0].Rows[0]["zxkk"].ToString()); } if (ds.Tables[0].Rows[0]["jckk"] != null && ds.Tables[0].Rows[0]["jckk"].ToString() != "") { model.jckk = double.Parse(ds.Tables[0].Rows[0]["jckk"].ToString()); } if (ds.Tables[0].Rows[0]["ysgzlj"] != null && ds.Tables[0].Rows[0]["ysgzlj"].ToString() != "") { model.ysgzlj = double.Parse(ds.Tables[0].Rows[0]["ysgzlj"].ToString()); } if (ds.Tables[0].Rows[0]["gslj"] != null && ds.Tables[0].Rows[0]["gslj"].ToString() != "") { model.gslj = double.Parse(ds.Tables[0].Rows[0]["gslj"].ToString()); } if (ds.Tables[0].Rows[0]["bygs"] != null && ds.Tables[0].Rows[0]["bygs"].ToString() != "") { model.bygs = double.Parse(ds.Tables[0].Rows[0]["bygs"].ToString()); } return(model); } else { return(null); } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.gsjs model) { using (SqlConnection conn = new SqlConnection(DbHelperSql.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("update r_jshz set "); strSql.Append("year=@year,"); strSql.Append("month=@month,"); strSql.Append("depart=@depart,"); strSql.Append("departid=@departid,"); strSql.Append("empid=@empid,"); strSql.Append("name=@name,"); strSql.Append("yfje=@yfje,"); strSql.Append("kk=@kk,"); strSql.Append("gjj=@gjj,"); strSql.Append("bxkk=@bxkk,"); strSql.Append("zxkk=@zxkk,"); strSql.Append("jckk=@jckk,"); strSql.Append("ysgzlj=@ysgzlj,"); strSql.Append("gslj=@gslj,"); strSql.Append("bygs=@bygs"); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@year", SqlDbType.VarChar, 50), new SqlParameter("@month", SqlDbType.VarChar, 50), new SqlParameter("@depart", SqlDbType.VarChar, 50), new SqlParameter("@departid", SqlDbType.VarChar, 50), new SqlParameter("@empid", SqlDbType.VarChar, 50), new SqlParameter("@name", SqlDbType.VarChar, 50), new SqlParameter("@gzjb", SqlDbType.VarChar, 50), new SqlParameter("@yfje", SqlDbType.Decimal), new SqlParameter("@kk", SqlDbType.Decimal), new SqlParameter("@gjj", SqlDbType.Decimal), new SqlParameter("@bxkk", SqlDbType.Decimal), new SqlParameter("@zxkk", SqlDbType.Decimal), new SqlParameter("@jckk", SqlDbType.Decimal), new SqlParameter("@ysgzlj", SqlDbType.Decimal), new SqlParameter("@gslj", SqlDbType.Decimal), new SqlParameter("@bygs", SqlDbType.Decimal), new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = model.year; parameters[1].Value = model.month; parameters[2].Value = model.depart; parameters[3].Value = model.departid; parameters[4].Value = model.empid; parameters[5].Value = model.name; parameters[6].Value = model.gzjb; parameters[7].Value = model.yfje; parameters[9].Value = model.kk; parameters[10].Value = model.gjj; parameters[11].Value = model.bxkk; parameters[12].Value = model.zxkk; parameters[13].Value = model.jckk; parameters[14].Value = model.ysgzlj; parameters[15].Value = model.gslj; parameters[16].Value = model.bygs; parameters[17].Value = model.id; DbHelperSql.ExecuteSql(conn, trans, strSql.ToString(), parameters); trans.Commit(); } catch { trans.Rollback(); return(false); } } } return(true); }