/// <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);
}
