/// <summary> /// 个税测算 /// </summary> /// <param name="d"></param> /// <returns></returns> public string calPreCalculateTax(Dictionary <string, object> d) { // string w = @" update a set T_DJJE = dbo.fn_TaxRateOfPersonalYear ( b.T_YSSDE ),K_YCXJJYKS = dbo.fn_TaxRateOfPersonalYear ( b.G_YCXJJ ) from tax_salary a,View_PreCalculateTax b // where a.S_WorkerCode = b.S_WorkerCode AND DATEDIFF( m, a.S_WorkDate, '2018-11-01' ) = 0 AND DATEDIFF( m, b.S_WorkDate, '2018-11-01' ) = 0 //AND a.S_OrgCode LIKE '100%' AND b.S_OrgCode LIKE '100%'"; //StringBuilder sb = new StringBuilder(); //sb.Append(" update a set "); //sb.Append(" a.S_UpdateBy="); //sb.Append(GetIsNullStr(d["S_UpdateBy"].ToString()) + ", "); //sb.Append(" a.S_UpdateDate="); //sb.Append(GetIsNullStr(d["S_UpdateDate"].ToString()) + ", "); //sb.Append(" T_LJYJS = dbo.fn_TaxRateOfPersonalYear ( b.T_YSSDE,b.MonthNum ),"); //sb.Append(" T_DJJE =case when IsDisability=1 then CONVERT(DECIMAL(18,4),(dbo.fn_TaxRateOfPersonalYear ( b.T_YSSDE,b.MonthNum )-b.WithholdingTax)/2) when IsDisability<>1 then dbo.fn_TaxRateOfPersonalYear ( b.T_YSSDE,b.MonthNum )-b.WithholdingTax end,"); //sb.Append(" T_BQSR = b.T_SRE,"); //sb.Append(" D_SL = (select TaxRate from [dbo].[fn_getsubstructconfigyear]( b.T_YSSDE,b.MonthNum )),"); //sb.Append(" D_SSKCS = (select TaxDeduction from [dbo].[fn_getsubstructconfigyear]( b.T_YSSDE,b.MonthNum )),"); //sb.Append(" D_FYJCBZ = (select Deduction from [dbo].[fn_getsubstructconfigyear]( b.T_YSSDE,b.MonthNum )),"); //sb.Append(" K_YCXJJYKS = dbo.fn_onetimecal ( b.G_YCXJJ ) from tax_salary a,View_PreCalculateTax b"); ////sb.Append(" where S_Id=" + GetIsNullStr(d["S_Id"].ToString())); //sb.Append(" where a.S_WorkerCode = b.S_WorkerCode ") ; //if (d.Keys.Contains("S_Department") && d["S_Department"] != null && d["S_Department"].ToString() != "") //{ // sb.Append(" and a.S_Department like '%" + d["S_Department"].ToString() + "%'"); // sb.Append(" and b.S_Department like '%" + d["S_Department"].ToString() + "%'"); //} //if (d.Keys.Contains("S_WorkerName") && d["S_WorkerName"] != null && d["S_WorkerName"].ToString() != "") //{ // sb.Append(" and a.S_WorkerName like '%" + d["S_WorkerName"].ToString() + "%' "); // sb.Append(" and b.S_WorkerName like '%" + d["S_WorkerName"].ToString() + "%' "); //} //if (d.Keys.Contains("S_WorkDate") && d["S_WorkDate"] != null && d["S_WorkDate"].ToString() != "") //{ // DateTime bdate = Convert.ToDateTime(d["S_WorkDate"].ToString()); // sb.Append(" and DATEDIFF(m, a.S_WorkDate, '" + bdate.ToString("yyyy-MM-dd") + "') = 0 "); // sb.Append(" and DATEDIFF(m, b.S_WorkDate, '" + bdate.ToString("yyyy-MM-dd") + "') = 0 "); //} //if (d.Keys.Contains("S_OrgCode") && d["S_OrgCode"] != null && d["S_OrgCode"].ToString() != "") //{ // sb.Append(" and a.S_OrgCode like '" + d["S_OrgCode"].ToString() + "%'"); // sb.Append(" and b.S_OrgCode like '" + d["S_OrgCode"].ToString() + "%'"); //} //return db.ExecutByStringResult(sb.ToString()); IDataParameter[] parm = new SqlParameter[6]; parm[0] = new SqlParameter("@OrgCode", SqlDbType.NVarChar, 50); parm[1] = new SqlParameter("@WorkDate", SqlDbType.NVarChar, 50); parm[2] = new SqlParameter("@WorkerName", SqlDbType.NVarChar, 50); parm[3] = new SqlParameter("@Department", SqlDbType.NVarChar, 50); parm[4] = new SqlParameter("@UpdateBy", SqlDbType.NVarChar, 50); parm[5] = new SqlParameter("@Output_Value", SqlDbType.NVarChar, 2000); if (d.Keys.Contains("S_OrgCode") && d["S_OrgCode"] != null && d["S_OrgCode"].ToString() != "") { parm[0].Value = d["S_OrgCode"].ToString(); } else { parm[0].Value = ""; } if (d.Keys.Contains("S_WorkDate") && d["S_WorkDate"] != null && d["S_WorkDate"].ToString() != "") { parm[1].Value = d["S_WorkDate"].ToString(); } else { parm[1].Value = ""; } if (d.Keys.Contains("S_WorkerName") && d["S_WorkerName"] != null && d["S_WorkerName"].ToString() != "") { parm[2].Value = d["S_WorkerName"].ToString(); } else { parm[2].Value = ""; } if (d.Keys.Contains("S_Department") && d["S_Department"] != null && d["S_Department"].ToString() != "") { parm[3].Value = d["S_Department"].ToString(); } else { parm[3].Value = ""; } if (d.Keys.Contains("S_UpdateBy") && d["S_UpdateBy"] != null && d["S_UpdateBy"].ToString() != "") { parm[4].Value = d["S_UpdateBy"].ToString(); } else { parm[4].Value = ""; } string result = db.ExcuteProcedure("pro_PreCalculateTax", parm).ToString(); return(result); }