public string CreateTask(Dictionary <string, object> d)
{
Guid TASK_ID = Guid.NewGuid();
List <string> list = new List <string>();
string sql = "INSERT INTO wy_check_task (TASK_ID,PLAN_DETAIL_ID,RWBH,RWMC,RWKSSJ,RWJSSJ,RWNR,RWFW,REMARK,CJR,CJSJ,IS_DELETE,IS_PUSH)VALUES(";
sql += GetSqlStr(TASK_ID);
sql += GetSqlStr(d["PLAN_DETAIL_ID"]);
sql += GetSqlStr(d["RWBH"]);
sql += GetSqlStr(d["RWMC"]);
sql += GetSqlStr(d["RWKSSJ"]);
sql += GetSqlStr(d["RWJSSJ"]);
sql += GetSqlStr(d["RWNR"]);
sql += GetSqlStr(d["RWFW"]);
sql += GetSqlStr(d["REMARK"]);
sql += GetSqlStr(d["userId"]);
sql += GetSqlStr(DateTime.Now);
sql += GetSqlStr(0, 1);
sql += GetSqlStr(0, 1);
sql = sql.TrimEnd(',') + ")";
list.Add(sql);
string[] arr = JArray.FromObject(d["PLAN_DETAIL_ARR"]).ToObject <string[]>();
foreach (string str in arr)
{
string mapsql = "INSERT INTO wy_map_checkplandetail(TASK_ID,PLAN_DETAIL_ID)values(";
mapsql += GetSqlStr(TASK_ID);
mapsql += GetSqlStr(str);
mapsql = mapsql.TrimEnd(',') + ")";
list.Add(mapsql);
}
return(db.Executs(list));
}
public string CreateInfo(Dictionary <string, object> d, List <Dictionary <string, object> > list, string XMBH)
{
List <string> sqllist = new List <string>();
string sql = "INSERT INTO jy_cbjh (XMBH,XMMC,CBDW,JSNR,JHZJE,TZHJHZJE,LSJE,BNJE,WLJE,XMPC,CJR,CJSJ,IS_DELETE,CZWZ,WZJHJE,XMLB,HASINCOME,XMCODE,JHND,PROCESS_STATE,IS_ADJUSTMENT,TZJE)values(";
sql += GetSQLStr(XMBH);
sql += GetSQLStr(d["XMMC"]);
sql += GetSQLStr(d["CBDW"]);
sql += GetSQLStr(d["JSNR"]);
sql += GetSQLStr(d["JHZJE"], 1);
sql += GetSQLStr(d["JHZJE"], 1);
sql += GetSQLStr(d["LSJE"], 1);
sql += GetSQLStr(d["BNJE"], 1);
sql += GetSQLStr(d["WLJE"], 1);
sql += GetSQLStr(d["XMPC"]);
sql += GetSQLStr(d["CJR"]);
sql += GetSQLStr(DateTime.Now);
sql += GetSQLStr(0, 1);
sql += GetSQLStr(d["CZWZ"], 1);
sql += GetSQLStr(d["WZJHJE"], 1);
//sql += GetSQLStr(d["SFCW"], 1);
sql += GetSQLStr(d["XMLB"]);
sql += GetSQLStr(d["HASINCOME"], 1);
sql += GetSQLStr(d["XMCODE"]);
sql += GetSQLStr(d["JHND"]);
sql += GetSQLStr(0, 1);
sql += GetSQLStr(d["IS_ADJUSTMENT"], 1);
sql += GetSQLStr(d["TZJE"], 1);
sql = sql.TrimEnd(',');
sql += ")";
sqllist.Add(sql);
int i;
if (int.TryParse(d["CZWZ"].ToString(), out i))
{
if (i == 1)
{
foreach (Dictionary <string, object> dic in list)
{
string sql1 = "INSERT INTO jy_cbwz (WZID,XMBH,WZMC,WZSL,WZLX,WZSM,IS_DELETE) VALUES(";
sql1 += GetSQLStr(Guid.NewGuid());
sql1 += GetSQLStr(XMBH);
sql1 += GetSQLStr(dic["WZMC"]);
sql1 += GetSQLStr(dic["WZSL"], 1);
sql1 += GetSQLStr(dic["WZLX"]);
sql1 += GetSQLStr(dic["WZSM"]);
sql1 += GetSQLStr(0, 1);
sql1 = sql1.TrimEnd(',');
sql1 += ")";
sqllist.Add(sql1);
}
}
}
return(db.Executs(sqllist));
}
//public string examineApplyData(Dictionary<string, object> d)
//{
// StringBuilder sb = new StringBuilder();
// sb.Append(" update ts_store_application set ");
// sb.Append(" CHECK_STATE='");
// sb.Append(d["CHECK_STATE"] == null ? "" : GetIsNullStr(d["CHECK_STATE"]) + "', ");
// sb.Append(" CHECK_CONTENT='");
// sb.Append(d["CHECK_CONTENT"] == null ? "" : GetIsNullStr(d["CHECK_CONTENT"]) + "', ");
// sb.Append(" CHECK_PERSON_ID='");
// sb.Append(d["CHECK_PERSON_ID"] == null ? "" : GetIsNullStr(d["CHECK_PERSON_ID"]) + "', ");
// sb.Append(" CHECK_PERSON_NAME='");
// sb.Append(d["CHECK_PERSON_NAME"] == null ? "" : GetIsNullStr(d["CHECK_PERSON_NAME"]) + "', ");
// sb.Append(" CHECK_DATE='");
// sb.Append( DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', ");
// sb.Append("' where APPLY_ID='" + GetIsNullStr(d["APPLY_ID"].ToString()) + "' ");
// return db.ExecutByStringResult(sb.ToString());
//}
public string updateApplyArticle(string id)
{
List <string> lst = new List <string>();
string sql = "update ts_store_application set IS_DELETE=1 where APPLY_ID ='" + id + "'";
string sql2 = "update ts_store_application_record set IS_DELETE=1 where APPLY_ID ='" + id + "'";
lst.Add(sql);
lst.Add(sql2);
return(db.Executs(lst));
//return db.ExecutByStringResult(sql);
}
public string CreateCheckPlan(Dictionary <string, object> d)
{
List <string> SqlList = new List <string>();
string PLAN_ID = Guid.NewGuid().ToString();
string CheckPlanSql = "INSERT INTO wy_checkPlan (PLAN_ID,JHND,JHMC,JHSM,JHSJ,REMARK,CJR,CJSJ,IS_DELETE)values(";
CheckPlanSql += GetSqlStr(PLAN_ID);
CheckPlanSql += GetSqlStr(d["JHND"]);
CheckPlanSql += GetSqlStr(d["JHMC"]);
CheckPlanSql += GetSqlStr(d["JHSM"]);
CheckPlanSql += GetSqlStr(d["JHSJ"]);
CheckPlanSql += GetSqlStr(d["REMARK"]);
CheckPlanSql += GetSqlStr(d["userId"]);
CheckPlanSql += GetSqlStr(DateTime.Now.ToString("yyyyMMdd"));
CheckPlanSql += GetSqlStr(0, 1);
CheckPlanSql = CheckPlanSql.TrimEnd(',') + ")";
SqlList.Add(CheckPlanSql);
foreach (Dictionary <string, object> CheckPlanDetail in JArray.FromObject(d["CheckPlanDetail"]).ToObject <List <Dictionary <string, object> > >())
{
if (CheckPlanDetail.ContainsValue(""))
{
return("您输入的详细计划内有空值！请仔细修改后再提交表单");
}
else
{
string PLAN_DETAIL_ID = Guid.NewGuid().ToString();
string DetailSql = "INSERT INTO wy_checkPlan_detail(PLAN_DETAIL_ID,PLAN_ID,JCQY,JCNR,JCLX,PCCS,CJR,CJSJ,IS_DELETE)VALUES(";
string JHQYstr = "";
foreach (string jhqy in JArray.FromObject(CheckPlanDetail["JCQY"]).ToObject <string[]>())
{
JHQYstr += jhqy + ",";
string mapSql = "INSERT INTO wy_map_region(PLAN_DETAIL_ID,REGION_CODE)values(";
mapSql += GetSqlStr(PLAN_DETAIL_ID);
mapSql += GetSqlStr(jhqy);
mapSql = mapSql.TrimEnd(',') + ")";
SqlList.Add(mapSql);
}
DetailSql += GetSqlStr(PLAN_DETAIL_ID);
DetailSql += GetSqlStr(PLAN_ID);
DetailSql += GetSqlStr(JHQYstr);
DetailSql += GetSqlStr(CheckPlanDetail["JCNR"]);
DetailSql += GetSqlStr(CheckPlanDetail["JCLX"]);
DetailSql += GetSqlStr(CheckPlanDetail["PCCS"], 1);
DetailSql += GetSqlStr(d["userId"]);
DetailSql += GetSqlStr(DateTime.Now.ToString("yyyyMMdd"));
DetailSql += GetSqlStr(0, 1);
DetailSql = DetailSql.TrimEnd(',') + ")";
SqlList.Add(DetailSql);
}
}
return(db.Executs(SqlList));
}
public string UpLoadHouseInfo(DataTable dt, Dictionary <string, object> userinfo)
{
DataTable DicTable = db.GetDataTable("SELECT Name,Code,ParentCode from tax_dictionary");
List <string> list = new List <string>();
foreach (DataRow dr in dt.Rows)
{
string sql = "INSERT INTO wy_houseinfo (FWID,FWBH,FWMC,JZMJ,LSFGS,ZLWZ,JGLX,ZCYZ,SSQY,WATER_NUMBER,ELE_NUMBER,ZFK,IS_DELETE,FWSX,CID,CJR,CJSJ,ORG_CODE)" +
" VALUES(";
sql += GetSqlStr(Guid.NewGuid());
sql += GetSqlStr(dr["房屋编号"]);
sql += GetSqlStr(dr["房屋名称"]);
sql += GetSqlStr(dr["建筑面积"]);
sql += GetSqlStr(DicTable.Select("Name='" + dr["隶属分公司"] + "'AND ParentCode='LSFGS'")[0]["Code"]);
sql += GetSqlStr(dr["坐落位置"]);
sql += GetSqlStr(DicTable.Select("Name='" + dr["结构类型"] + "'AND ParentCode='JGLX'")[0]["Code"]);
sql += GetSqlStr(dr["资产原值"]);
sql += GetSqlStr(DicTable.Select("Name='" + dr["所属区域"] + "'AND ParentCode='SSQY'")[0]["Code"]);
sql += GetSqlStr(dr["水表编号"]);
sql += GetSqlStr(dr["电表编号"]);
sql += GetSqlStr(dr["总房款"]);
sql += GetSqlStr(0, 1);
sql += GetSqlStr(0, 1);
sql += GetSqlStr(dr["电表采集器ID"]);
sql += GetSqlStr(userinfo["userId"]);
sql += GetSqlStr(DateTime.Now);
sql += GetSqlStr(userinfo["ORG_CODE"]);
sql = sql.TrimEnd(',') + ")";
list.Add(sql);
}
return(db.Executs(list));
}
/// <summary>
/// 分配角色给用户
/// </summary>
/// <param name="d"></param>
/// <returns></returns>
public string updateUserRoleArticle(Dictionary <string, object> d)
{
// string[] array = d["multipleSelection"].ToString().Split(',');
var array = (JArray)d["arr"];
if (array == null || array.Count == 0)
{
return("");
}
string fengefu = "";
string sql = " insert into ts_uidp_group_user(GROUP_ID,USER_ID)values ";
string delSql = " delete from ts_uidp_group_user where GROUP_ID='" + d["GROUP_ID"].ToString() + "' and USER_ID in(";
foreach (var item in array)
{
delSql += fengefu + "'" + item.ToString() + "'";
sql += fengefu + "(";
sql += "'" + d["GROUP_ID"].ToString() + "','" + item.ToString() + "'";
sql += ")";
fengefu = ",";
}
delSql += ")";
List <string> list = new List <string>();
list.Add(delSql);
list.Add(sql);
return(db.Executs(list));
}
public string delPostByID(Dictionary <string, object> d)
{
string sql1 = "delete from ts_community_comment where POST_ID='" + d["POST_ID"].ToString() + "'";
string sql2 = "delete from ts_community_post where POST_ID='" + d["POST_ID"].ToString() + "'";
List <string> sql = new List <string>();
sql.Add(sql1);
sql.Add(sql2);
return(db.Executs(sql));
}
/// <summary>
/// 保管员提交修改的数据
/// </summary>
/// <param name="d"></param>
/// <returns></returns>
public string BGYSendForm(Dictionary <string, object> d)
{
List <string> list = new List <string>();
string sql = "UPDATE JJRK SET APPROVAL_STATUS=6,";//将入库单状态置为保管员已已提交
sql += " BGY_ID=" + GetSQLStr(d["userid"]);
sql += " BGY_DATE=" + GetSQLStr(DateTime.Now, 1);
sql += " EFFECTIVE_STATUS=0";//将紧急入库单的状态置为有效
sql += " WHERE ID='" + d["ID"] + "'";
string sql1 = " INSERT INTO CONVERT_SWKC (WERKS,ZDHTZD,MATNR,MAKTX,MEINS,GESME,LGORT,KCTYPE,LGPLA,ID,MATKL,ZSTATUS)VALUES(";
sql1 += "(SELECT DW_CODE FROM TS_UIDP_ORG WHERE ORG_CODE='" + d["DW_CODE"] + "'),";
sql1 += GetSQLStr(d["CODE"]);
sql1 += GetSQLStr(d["MATNR"]);
sql1 += GetSQLStr(d["MATNX"]);
sql1 += GetSQLStr(d["MEINS"]);
sql1 += GetSQLStr(d["RKNUMBER1"]);
sql1 += GetSQLStr(d["KCDD"]);
sql1 += GetSQLStr(1, 2);
sql1 += GetSQLStr(d["LGPLA"]);
sql1 += GetSQLStr(d["ID"]);
sql1 += GetSQLStr(d["MATKL"]);
sql1 += GetSQLStr("04");
sql1 = sql1.TrimEnd(',');
sql1 += ")";
string sql2 = "INSERT INTO CONVERT_ZWKC (BWKEY,MATNR,SALK3,LBKUM,DANJIA,DLDATE,KCTYPE,ID,MATKL)VALUES(";
sql2 += "(SELECT DW_CODE FROM TS_UIDP_ORG WHERE ORG_CODE='" + d["DW_CODE"] + "'),";
sql2 += GetSQLStr(d["MATNR"]);
sql2 += GetSQLStr(d["TOTALPRICE1"], 2);
sql2 += GetSQLStr(d["RKNUMBER1"], 2);
sql2 += GetSQLStr(Convert.ToDecimal(Convert.ToDecimal(d["TOTALPRICE1"].ToString()) / Convert.ToDecimal(d["RKNUMBER1"].ToString())), 2);
sql2 += GetSQLStr(DateTime.Now.ToString("yyyyMMdd"));
sql2 += GetSQLStr(1, 2);
sql2 += GetSQLStr(d["ID"]);
sql2 += GetSQLStr(d["MATKL"]);
sql2 = sql2.TrimEnd(',');
sql2 += ")";
list.Add(sql);
list.Add(sql1);
list.Add(sql2);
return(db.Executs(list));
}
/// <summary>
/// 分配组织结构给用户
/// </summary>
/// <param name="d"></param>
/// <returns></returns>
public string updateUserForLoginArticle(Dictionary <string, object> d)
{
// string[] array = d["multipleSelection"].ToString().Split(',');
var array = (JArray)d["arr"];
string fengefu = "";
string sql = " insert into ts_uidp_login_user(LOGIN_ID,USER_ID)values ";
string delSql = "delete from ts_uidp_login_user where LOGIN_ID='" + d["LOGIN_ID"].ToString() + "' and USER_ID in (";
string userid = "";
foreach (var item in array)
{
userid += fengefu + "'" + item.ToString() + "'";
delSql += fengefu + "'" + item.ToString() + "'";
sql += fengefu + "(";
sql += "'" + d["LOGIN_ID"].ToString() + "','" + item.ToString() + "'";
sql += ")";
fengefu = ",";
}
delSql += ")";
string sqlUser = "******" + userid + ") ";
sqlUser += " union select a.USER_DOMAIN from ts_uidp_userinfo a join ts_uidp_login_user b on a.USER_ID=b.USER_ID where b.LOGIN_ID='" + d["LOGIN_ID"].ToString() + "'";
DataTable dt = db.GetDataTable(sqlUser);
fengefu = "";
userid = "";
foreach (DataRow row in dt.Rows)
{
userid += fengefu + row[0].ToString();
fengefu = ",";
}
// string sqlUpdateUserInfo = " update ts_uidp_userinfo set ASSOCIATED_ACCOUNT= case when ASSOCIATED_ACCOUNT<>'' or ASSOCIATED_ACCOUNT is not null then CONCAT(ASSOCIATED_ACCOUNT,'," + userid + "') else '"+ userid + "' where USER_ID='"+ d["LOGIN_ID"].ToString()+"'";
string sqlUpdateUserInfo = " update ts_uidp_userinfo set ASSOCIATED_ACCOUNT='" + userid + "' where USER_ID='" + d["LOGIN_ID"].ToString() + "'";
List <string> list = new List <string>();
list.Add(delSql);
list.Add(sql);
list.Add(sqlUpdateUserInfo);
return(db.Executs(list));
}
/// <summary>
/// 分配组织结构给用户
/// </summary>
/// <param name="d"></param>
/// <returns></returns>
public string updateUserOrgArticle(Dictionary <string, object> d)
{
// string[] array = d["multipleSelection"].ToString().Split(',');
var array = (JArray)d["arr"];
string fengefu = "";
string sql = " insert into ts_uidp_org_user(ORG_ID,USER_ID)values ";
string delSql = "delete from ts_uidp_org_user where USER_ID in (";
foreach (var item in array)
{
delSql += fengefu + "'" + item.ToString() + "'";
sql += fengefu + "(";
sql += "'" + d["orgId"].ToString() + "','" + item.ToString() + "'";
sql += ")";
fengefu = ",";
}
delSql += ")";
List <string> list = new List <string>();
list.Add(delSql);
list.Add(sql);
return(db.Executs(list));
}
public string DeleteShopInfo(string CZ_SHID, string FWID)
{
string ShopSql = " UPDATE wy_shopinfo set IS_DELETE=1 where CZ_SHID='" + CZ_SHID + "'";
string HouseSql = " UPDATE wy_houseinfo SET CZ_SHID=NULL,FWSX=0 WHERE FWID='" + FWID + "'";
string FeeSql = "UPDATE wy_RopertyCosts SET IS_DELETE=1 WHERE FEE_ID in(SELECT FEE_ID FROM wy_shopinfo WHERE CZ_SHID='" + CZ_SHID + "')";
string LeaseSql = " UPDATE wy_Leasinginfo SET IS_DELETE=1 WHERE LEASE_ID in (SELECT LEASE_ID FROM wy_shopinfo WHERE CZ_SHID='" + CZ_SHID + "')";
List <string> list = new List <string>()
{
{ ShopSql },
{ HouseSql },
{ FeeSql },
{ LeaseSql }
};
return(db.Executs(list));;
}
public string createUserArticle(Dictionary <string, object> d)
{
List <string> list = new List <string>();
string sql = "insert into ts_uidp_org_user(ORG_ID,USER_ID)values ('";
sql += d["orgId"].ToString() + "','" + d["USER_ID"].ToString() + "')";
list.Add(sql);
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO ts_uidp_userinfo(USER_ID,USER_DOMAIN,USER_CODE,USER_NAME,USER_PASS,PHONE_MOBILE,PHONE_OFFICE," +
"USER_EMAIL,USER_SEX,AUTHENTICATION_TYPE,FLAG,REG_TIME,USER_TYPE,LEADER_TYPE,REMARK) VALUES(");
sb.Append("'");
sb.Append(d["USER_ID"] == null ? "" : d["USER_ID"] + "', ");
sb.Append("'");
sb.Append(d["USER_DOMAIN"] == null ? "" : d["USER_DOMAIN"] + "', ");
sb.Append("'");
sb.Append(d["USER_CODE"] == null ? "" : d["USER_CODE"] + "', ");
sb.Append("'");
sb.Append(d["USER_NAME"] == null ? "" : d["USER_NAME"] + "', ");
sb.Append("'");
sb.Append(d["USER_PASS"] == null ? "" : d["USER_PASS"] + "', ");
sb.Append("'");
sb.Append(d["PHONE_MOBILE"] == null ? "" : d["PHONE_MOBILE"] + "', ");
sb.Append("'");
sb.Append(d["PHONE_OFFICE"] == null ? "" : d["PHONE_OFFICE"] + "', ");
sb.Append("'");
sb.Append(d["USER_EMAIL"] == null ? "" : d["USER_EMAIL"] + "', ");
sb.Append(d["USER_SEX"] == null ? "1" : d["USER_SEX"] + ",");
sb.Append(d["AUTHENTICATION_TYPE"] == null ? "" : d["AUTHENTICATION_TYPE"] + ", ");
sb.Append(d["FLAG"] == null ? "1" : d["FLAG"] + ", ");
sb.Append("'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',");
sb.Append(d["USER_TYPE"] == null?"1" : d["USER_TYPE"].ToString() + ",");
sb.Append(d["LEADER_TYPE"] == null ? "0" : d["LEADER_TYPE"].ToString() + ",'");
sb.Append(d["REMARK"] == null ? "" : d["REMARK"] + "' )");
list.Add(sb.ToString());
return(db.Executs(list));
}
public string updateTaxComputeconfig(List <Dictionary <string, object> > arr, Dictionary <string, object> d)
{
List <string> SQL = new List <string>();
foreach (Dictionary <string, object> obj in arr)
{
string sql = "UPDATE tax_computeconfig SET TermName='" + obj["TermName"] + "',";
sql += "TermCode='" + obj["TermCode"] + "',";
sql += "MonthCompute=" + obj["MonthCompute"] + ",";
sql += "MonthProportion='" + obj["MonthProportion"] + "',";
sql += " S_UpdateBy='" + d["userid"] + "',";
sql += " S_UpdateDate='" + d["S_UpdateDate"] + "'";
sql += " WHERE S_Id='" + obj["S_Id"] + "'";
SQL.Add(sql);
}
return(db.Executs(SQL));
}
public string CreateInfo(Dictionary <string, object> d)
{
List <string> sqllist = new List <string>();
string sql = "INSERT INTO jy_cbjh_adjust (S_ID,XMBH,XMMC,TZQJE,TZJE,TZSM,TZSJ,CJR,CJSJ)values(";
sql += GetSQLStr(Guid.NewGuid().ToString());
sql += GetSQLStr(d["XMBH"]);
sql += GetSQLStr(d["XMMC"]);
sql += GetSQLStr(d["TZQJE"]);
sql += GetSQLStr(d["TZJE"]);
sql += GetSQLStr(d["TZSM"]);
sql += GetSQLStr(d["TZSJ"]);
sql += GetSQLStr(d["CJR"]);
sql += GetSQLStr(DateTime.Now);
sql = sql.TrimEnd(',');
sql += ")";
string updatesql = "update jy_cbjh set TZHJHZJE=TZHJHZJE+" + d["TZJE"].ToString() + " where XMBH='" + d["XMBH"].ToString() + "'";
sqllist.Add(sql);
sqllist.Add(updatesql);
return(db.Executs(sqllist));
//return db.ExecutByStringResult(sql);
}
public IActionResult revokeFlow(string instanceid, string senderid, int formtype)
{
Dictionary <string, object> r = new Dictionary <string, object>();
DataTable firsttesp = new DataTable();
RoadFlow.Business.FlowTask flowTask = new RoadFlow.Business.FlowTask();
var taskid = string.Empty;
using (RoadFlow.Mapper.DataContext context = new RoadFlow.Mapper.DataContext())
{
firsttesp = context.GetDataTable("select top 1 * from RF_FlowTask where Status =2 and InstanceId = '" + instanceid + "' and ReceiveId='" + senderid + "' ORDER BY ReceiveTime desc");
}
if (firsttesp != null && firsttesp.Rows.Count == 1)
{
taskid = firsttesp.Rows[0]["Id"].ToString();
}
if (!string.IsNullOrEmpty(taskid) && !string.IsNullOrEmpty(instanceid))
{
DBTool db = new DBTool("");
string flag = db.GetString("select count(1) from RF_FlowTask where previd = '" + taskid + "' and Status in(0,1) "); //判断是否可以撤回
if (!string.IsNullOrEmpty(flag) && flag != "0") //只允许发起人撤回下一步未审批的流程
//if (!string.IsNullOrEmpty(flag))//发起人可撤回任意状态的流程
{
string updateSql = string.Empty;
string revokeSql = "delete from RF_FlowTask where InstanceId = '" + instanceid + "' and (previd ='" + taskid + "' or id='" + taskid + "')";//只允许发起人撤回下一步未审批的流程
//string revokeSql = "delete from RF_FlowTask where InstanceId = '" + instanceid + "'";//发起人可撤回任意状态的流程
if (formtype == 0)
{
updateSql = "UPDATE jy_cbjh set PROCESS_STATE=0 where XMBH='" + instanceid + "'";
}
else if (formtype == 1)
{
updateSql = "UPDATE jy_fybx set PROCESS_STATE=0 where BXDH='" + instanceid + "'";
}
else
{
updateSql = "UPDATE jy_clbx set PROCESS_STATE=0 where CLBH='" + instanceid + "'";
}
List <string> list = new List <string>();
list.Add(revokeSql);
list.Add(updateSql);
string result = db.Executs(list);
if (result == "")
{
r["code"] = 2000;
r["message"] = "审批已撤回！";
}
}
else
{
r["code"] = -1;
r["message"] = "撤销失败！流程已被审批！";
}
}
else
{
r["code"] = -1;
r["message"] = "撤销失败！参数异常！";
}
return(Json(r));
}
public string createCommunityPostArticle(Dictionary <string, object> d)
{
string col = "";
string val = "";
string score = "0";
string type = "";
List <string> sqllist = new List <string>();
foreach (var v in d)
{
if (v.Value != null)
{
col += "," + v.Key;
val += ",'" + v.Value + "'";
}
else
{
col += "," + v.Key;
val += ",''";
}
}
if (col != "")
{
col = col.Substring(1);
}
if (val != "")
{
val = val.Substring(1);
}
if (d["POST_TYPE"].ToString() == "1")
{
score = db.GetString("select SCORE from ts_community_score_conf WHERE OPER_TYPE='share'");
type = "share";
}
else if (d["POST_TYPE"].ToString() == "2")
{
score = db.GetString("select SCORE from ts_community_score_conf WHERE OPER_TYPE='feedback'");
type = "feedback";
}
else
{
score = db.GetString("select SCORE from ts_community_score_conf WHERE OPER_TYPE='help'");
type = "help";
}
string sql = "INSERT INTO ts_community_post(" + col + ",SEND_DATE,CREATE_DATE,IS_DELETE,POST_STATUS,BROWSE_NUM) VALUES(" + val + ",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',0,0,0)";
string sql1 = "insert into ts_community_score_detail(SCORE_DETAIL_ID,USER_ID,SCORE,POST_DATE,USER_OPER,SOURCE_ID) values('" + Guid.NewGuid().ToString() + "','" + d["USER_ID"].ToString() + "'," + score + ",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + type + "','" + d["POST_ID"].ToString() + "')";//发帖积分明细
string sql2 = "update ts_uidp_userinfo set SCORE=SCORE+" + Convert.ToDouble(score) + "-" + Convert.ToDouble(d["SCORE_POINT"].ToString()) + " where USER_ID='" + d["USER_ID"].ToString() + "'";
if (Convert.ToDouble(d["SCORE_POINT"].ToString()) > 0 && d["POST_TYPE"].ToString() == "3")
{
string sql3 = "insert into ts_community_score_detail(SCORE_DETAIL_ID,USER_ID,SCORE,POST_DATE,USER_OPER,SOURCE_ID) values('" + Guid.NewGuid().ToString() + "','" + d["USER_ID"].ToString() + "',-" + d["SCORE_POINT"].ToString() + ",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','sendhelp','" + d["POST_ID"].ToString() + "')";//发帖积分明细
sqllist.Add(sql3);
}
if (score != "0")
{
sqllist.Add(sql);
sqllist.Add(sql1);
sqllist.Add(sql2);
return(db.Executs(sqllist));
}
else
{
sqllist.Add(sql);
return(db.Executs(sqllist));
}
//string sql = "INSERT INTO ts_community_post(" + col + ",CREATE_DATE,IS_DELETE) VALUES(" + val + ",'"+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',0)";
//return db.ExecutByStringResult(sql);
}
public string createTaxSalary1(List <ImportTaxSalary1> d, string userId, string guid)
{
List <string> sqllst = new List <string>();
string fengefu = "";
StringBuilder sb = new StringBuilder();
sb.Append(@" insert into tax_calculatoryearsalary (S_Id,S_CreateDate,S_CreateBy,S_WorkerCode,S_WorkerName,S_OrgName,S_OrgCode,G_GWJGZ,G_BLGZ,G_GLJT,G_SGJT,G_JSJNJT,G_ZFBT,G_BLJT,G_BYKT,
G_QTJT,G_YBJT,G_JBJDGZ,G_JCYJ,G_YJJJ,G_DSZNBJFS,G_WCBTSF,G_BFK,T_YFHJ,K_YiLiaoBX,K_SYBX,K_YangLaoBX,K_ZFGJJ,K_QYNJ,
K_QTKX,T_YSHJ,K_KS,T_SFHJ,Adjust1,Adjust2,Adjust3,Adjust4,Adjust5,Adjust6,Adjust7,Adjust8,S_WorkDate) values ");
foreach (ImportTaxSalary1 item in d)
{
sb.Append(fengefu + "('" + guid + "',");
sb.Append("'" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "',");
sb.Append("'" + userId + "',");
sb.Append("'" + "',");
sb.Append("'" + "',");
sb.Append("'" + "',");
sb.Append("'" + "',");
sb.Append(item.G_GWJGZ + ",");
sb.Append(item.G_BLGZ + ",");
sb.Append(item.G_GLJT + ",");
sb.Append(item.G_SGJT + ",");
sb.Append(item.G_JSJNJT + ",");
sb.Append(item.G_ZFBT + ",");
sb.Append(item.G_BLJT + ",");
sb.Append(item.G_BYKT + ",");
sb.Append(item.G_QTJT + ",");
sb.Append(item.G_YBJT + ",");
sb.Append(item.G_JBJDGZ + ",");
sb.Append(item.G_JCYJ + ",");
sb.Append(item.G_YJJJ + ",");
sb.Append(item.G_DSZNBJFS + ",");
sb.Append(item.G_WCBTSF + ",");
sb.Append(item.G_BFK + ",");
sb.Append(item.T_YFHJ + ",");
sb.Append(item.K_YiLiaoBX + ",");
sb.Append(item.K_SYBX + ",");
sb.Append(item.K_YangLaoBX + ",");
sb.Append(item.K_ZFGJJ + ",");
sb.Append(item.K_QYNJ + ",");
sb.Append(item.K_QTKX + ",");
sb.Append(item.T_YSHJ + ",");
sb.Append(item.K_KS + ",");
sb.Append(item.T_SFHJ + ",");
sb.Append(item.Adjust1 + ",");
sb.Append(item.Adjust2 + ",");
sb.Append(item.Adjust3 + ",");
sb.Append(item.Adjust4 + ",");
sb.Append(item.Adjust5 + ",");
sb.Append(item.Adjust6 + ",");
sb.Append(item.Adjust7 + ",");
sb.Append(item.Adjust8 + ",");
sb.Append("'" + DateTime.Now.ToString("yyyy-MM-dd") + "')");
fengefu = ",";
}
string sql = sb.ToString();
sqllst.Add(sql);
return(db.Executs(sqllst));
}
//public string createTaxSalary1(List<ImportInfo1> d,DateTime dateMonth,string userId,string orgCode,string orgName)
public string createTaxSalary1(List <ImportTaxSalary1> list, DateTime dateMonth, string userId, string orgCode, string orgName, string personChange)
{
List <string> sqllst = new List <string>();
string delrecord = "delete from tax_salary where DATEDIFF(m, S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 and S_OrgCode='" + orgCode + "'";
string deladjust = "delete from tax_adjust where DATEDIFF(m, S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 and S_OrgCode='" + orgCode + "'";
string delonebonus = "delete from tax_onebonus where DATEDIFF(m, S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 and S_OrgCode='" + orgCode + "'";
string deltaxpayerrecord = "delete from tax_taxpayerrecord where S_OrgCode = '" + orgCode + "' and DATEDIFF(m, ImportMonth, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 ";
sqllst.Add(delrecord);
sqllst.Add(deladjust);
sqllst.Add(delonebonus);
sqllst.Add(deltaxpayerrecord);
int truckNum = Convert.ToInt32(Convert.ToDecimal(list.Count / 500));
int yushu = list.Count % 500;
if (yushu > 0)
{
truckNum++;
}
for (int i = 1; i < truckNum + 1; i++)
{
string fengefu = "";
StringBuilder sb = new StringBuilder();
int rowbegin = (i - 1) * 500;
int rowend = i * 500;
if (rowend > list.Count)
{
rowend = list.Count;
}
sb.Append(@" insert into tax_salary (S_Id,S_CreateDate,S_CreateBy,S_WorkerCode,S_WorkerName,S_OrgName,S_OrgCode,G_GWJGZ,G_BLGZ,G_GLJT,G_SGJT,G_JSJNJT,G_ZFBT,G_BLJT,G_BYKT,
G_QTJT,G_YBJT,G_JBJDGZ,G_JCYJ,G_YJJJ,G_DSZNBJFS,G_WCBTSF,G_BFK,T_YFHJ,K_YiLiaoBX,K_SYBX,K_YangLaoBX,K_ZFGJJ,K_QYNJ,
K_QTKX,T_YSHJ,K_KS,T_SFHJ,Adjust1,Adjust2,Adjust3,Adjust4,Adjust5,Adjust6,Adjust7,Adjust8,S_Department,S_WorkDate) values ");
for (int j = rowbegin; j < rowend; j++)
//foreach (ImportTaxSalary1 item in list)
{
sb.Append(fengefu + "('" + Guid.NewGuid().ToString() + "',");
sb.Append("'" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "',");
sb.Append("'" + userId + "',");
sb.Append("'" + list[j].S_WorkerCode + "',");
sb.Append("'" + list[j].S_WorkerName + "',");
sb.Append("'" + orgName + "',");
sb.Append("'" + orgCode + "',");
sb.Append(list[j].G_GWJGZ + ",");
sb.Append(list[j].G_BLGZ + ",");
sb.Append(list[j].G_GLJT + ",");
sb.Append(list[j].G_SGJT + ",");
sb.Append(list[j].G_JSJNJT + ",");
sb.Append(list[j].G_ZFBT + ",");
sb.Append(list[j].G_BLJT + ",");
sb.Append(list[j].G_BYKT + ",");
sb.Append(list[j].G_QTJT + ",");
sb.Append(list[j].G_YBJT + ",");
sb.Append(list[j].G_JBJDGZ + ",");
sb.Append(list[j].G_JCYJ + ",");
sb.Append(list[j].G_YJJJ + ",");
sb.Append(list[j].G_DSZNBJFS + ",");
sb.Append(list[j].G_WCBTSF + ",");
sb.Append(list[j].G_BFK + ",");
sb.Append(list[j].T_YFHJ + ",");
sb.Append(list[j].K_YiLiaoBX + ",");
sb.Append(list[j].K_SYBX + ",");
sb.Append(list[j].K_YangLaoBX + ",");
sb.Append(list[j].K_ZFGJJ + ",");
sb.Append(list[j].K_QYNJ + ",");
sb.Append(list[j].K_QTKX + ",");
sb.Append(list[j].T_YSHJ + ",");
sb.Append(list[j].K_KS + ",");
sb.Append(list[j].T_SFHJ + ",");
sb.Append(list[j].Adjust1 + ",");
sb.Append(list[j].Adjust2 + ",");
sb.Append(list[j].Adjust3 + ",");
sb.Append(list[j].Adjust4 + ",");
sb.Append(list[j].Adjust5 + ",");
sb.Append(list[j].Adjust6 + ",");
sb.Append(list[j].Adjust7 + ",");
sb.Append(list[j].Adjust8 + ",");
sb.Append("'" + list[j].S_OrgName + "',");
sb.Append("'" + dateMonth.ToString("yyyy-MM-dd") + "')");
fengefu = ",";
}
string sql = sb.ToString();
sqllst.Add(sql);
}
// string fengefu = "";
//StringBuilder sb = new StringBuilder();
//foreach (var v in d)
//{
//foreach (ImportTaxSalary1 item in list)
//{
// sb.Append(fengefu + "('" + Guid.NewGuid().ToString() + "',");
// sb.Append("'" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "',");
// sb.Append("'" + userId + "',");
// sb.Append("'" + item.S_WorkerCode + "',");
// sb.Append("'" + item.S_WorkerName + "',");
// sb.Append("'" + orgName + "',");
// sb.Append("'" + orgCode + "',");
// sb.Append(item.G_GWJGZ + ",");
// sb.Append(item.G_BLGZ + ",");
// sb.Append(item.G_GLJT + ",");
// sb.Append(item.G_SGJT + ",");
// sb.Append(item.G_JSJNJT + ",");
// sb.Append(item.G_ZFBT + ",");
// sb.Append(item.G_BLJT + ",");
// sb.Append(item.G_BYKT + ",");
// sb.Append(item.G_QTJT + ",");
// sb.Append(item.G_YBJT + ",");
// sb.Append(item.G_JBJDGZ + ",");
// sb.Append(item.G_JCYJ + ",");
// sb.Append(item.G_YJJJ + ",");
// sb.Append(item.G_DSZNBJFS + ",");
// sb.Append(item.G_WCBTSF + ",");
// sb.Append(item.G_BFK + ",");
// sb.Append(item.T_YFHJ + ",");
// sb.Append(item.K_YiLiaoBX + ",");
// sb.Append(item.K_SYBX + ",");
// sb.Append(item.K_YangLaoBX + ",");
// sb.Append(item.K_ZFGJJ + ",");
// sb.Append(item.K_QYNJ + ",");
// sb.Append(item.K_QTKX + ",");
// sb.Append(item.T_YSHJ + ",");
// sb.Append(item.K_KS + ",");
// sb.Append(item.T_SFHJ + ",");
// sb.Append(item.Adjust1 + ",");
// sb.Append(item.Adjust2 + ",");
// sb.Append(item.Adjust3 + ",");
// sb.Append(item.Adjust4 + ",");
// sb.Append(item.Adjust5 + ",");
// sb.Append(item.Adjust6 + ",");
// sb.Append(item.Adjust7 + ",");
// sb.Append(item.Adjust8 + ",");
// sb.Append("'" + item.S_OrgName + "',");
// sb.Append("'" + dateMonth.ToString("yyyy-MM-dd") + "')");
// fengefu = ",";
//}
string sqldel = "delete from tax_reportstatus where datediff(m,S_WorkDate,'" + dateMonth.ToString("yyyy-MM-dd") + "')=0 and S_OrgCode ='" + orgCode + "'";
sqllst.Add(sqldel);
string insertRecord = "insert into tax_reportstatus(S_Id,S_CreateDate,S_CreateBy,S_OrgName,S_OrgCode,ReportStatus,S_WorkDate) values('" + Guid.NewGuid().ToString() + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + userId + "','" + orgName + "','" + orgCode + "',1,'" + dateMonth.ToString("yyyy-MM-dd") + "')";//YZ修改 插入工资Excel根据需求从状态0修改为1
sqllst.Add(insertRecord);
//}
if (!string.IsNullOrEmpty(personChange))
{
sqllst.Add(personChange);
}
return(db.Executs(sqllst));
//return db.ExecutByStringResult(sql);
}
public string createTaxAdjust(List <ImportTaxAdjust> d, DateTime dateMonth, string userId, string orgCode, string orgName)
{
List <string> sqllst = new List <string>();
string batch = Guid.NewGuid().ToString();
string fengefu = "";
int i = 0; //循环数，用于判断添加SQL语句头部；
const int Num = 600; //Insert插入分条数目
string headersql = @" insert into tax_adjust (S_Id,S_CreateDate,S_CreateBy,S_WorkerCode,S_WorkerName,S_OrgName,S_OrgCode,Adjust9,Adjust10,Adjust11,Adjust12,Adjust13,Adjust14,Adjust15,Adjust16,Adjust17,Adjust18,Adjust19,Adjust20,Adjust21,Adjust22,Adjust23,Adjust24,Adjust25,Adjust26,Adjust27,Adjust28,S_Remark,S_Batch,S_WorkDate) values ";
StringBuilder sb = new StringBuilder();
//StringBuilder updatesb = new StringBuilder();
// string delAdjust = @"delete from tax_adjust
//where S_OrgCode in (select S_OrgCode from tax_org where DATEDIFF(m, S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 and ((S_OrgCode like '" + orgCode + "%' and IsComputeTax = 0) or S_OrgCode = '"+ orgCode + "'))";
//sb.Append(@" insert into tax_adjust (S_Id,S_CreateDate,S_CreateBy,S_WorkerCode,S_WorkerName,S_OrgName,S_OrgCode,Adjust9,Adjust10,Adjust11,Adjust12,Adjust13,Adjust14,Adjust15,Adjust16,Adjust17,Adjust18,Adjust19,Adjust20,Adjust21,Adjust22,Adjust23,Adjust24,Adjust25,Adjust26,Adjust27,Adjust28,S_Remark,S_WorkDate) values ");
foreach (ImportTaxAdjust item in d)
{
if (i % Num == 0)
{
sb.Append(headersql);
}
sb.Append(fengefu + "('" + Guid.NewGuid().ToString() + "',");
sb.Append("'" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "',");
sb.Append("'" + userId + "',");
sb.Append("'" + item.S_WorkerCode + "',");
sb.Append("'" + item.S_WorkerName + "',");
//sb.Append("'" + item.S_OrgName + "',");
//sb.Append("'" + item.S_OrgCode + "',");
sb.Append("'" + orgName + "',");
sb.Append("'" + orgCode + "',");
sb.Append(item.Adjust9 + ",");
sb.Append(item.Adjust10 + ",");
sb.Append(item.Adjust11 + ",");
sb.Append(item.Adjust12 + ",");
sb.Append(item.Adjust13 + ",");
sb.Append(item.Adjust14 + ",");
sb.Append(item.Adjust15 + ",");
sb.Append(item.Adjust16 + ",");
sb.Append(item.Adjust17 + ",");
sb.Append(item.Adjust18 + ",");
sb.Append(item.Adjust19 + ",");
sb.Append(item.Adjust20 + ",");
sb.Append(item.Adjust21 + ",");
sb.Append(item.Adjust22 + ",");
sb.Append(item.Adjust23 + ",");
sb.Append(item.Adjust24 + ",");
sb.Append(item.Adjust25 + ",");
sb.Append(item.Adjust26 + ",");
sb.Append(item.Adjust27 + ",");
sb.Append(item.Adjust28 + ",");
sb.Append("'" + item.S_Remark + "',");
sb.Append("'" + batch + "',");
sb.Append("'" + dateMonth.ToString("yyyy-MM-dd") + "')");
i++;
if (i % Num == 0 || item.Equals(d[d.Count - 1]))
{
sb.Append(fengefu);
}
else
{
sb.Append(",");
}
//fengefu = ",";
//string updatesql = @"update tax_salary set S_UpdateBy='" + userId + "',S_UpdateDate='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "',Adjust9=isnull(Adjust9,0)+isnull(" + item.Adjust9 + ",0),Adjust10=isnull(Adjust10,0)+isnull(" + item.Adjust10 + ",0),Adjust11=isnull(Adjust11,0)+isnull(" + item.Adjust11
// + ",0),Adjust12=isnull(Adjust12,0)+isnull(" + item.Adjust12 + ",0),Adjust13=isnull(Adjust13,0)+isnull(" + item.Adjust13 + ",0),Adjust14=isnull(Adjust14,0)+isnull(" + item.Adjust14 + ",0),Adjust15=isnull(Adjust15,0)+isnull(" + item.Adjust15
// + ",0),Adjust16=isnull(Adjust16,0)+isnull(" + item.Adjust16 + ",0),Adjust17=isnull(Adjust17,0)+isnull(" + item.Adjust17 + ",0),Adjust18=isnull(Adjust18,0)+isnull(" + item.Adjust18 + ",0),Adjust19=isnull(Adjust19,0)+isnull(" + item.Adjust19
// + ",0),Adjust20=isnull(Adjust20,0)+isnull(" + item.Adjust20 + ",0),Adjust21=isnull(Adjust21,0)+isnull(" + item.Adjust21 + ",0),Adjust22=isnull(Adjust22,0)+isnull(" + item.Adjust22 + ",0),Adjust23=isnull(Adjust23,0)+isnull(" + item.Adjust23
// + ",0),Adjust24=isnull(Adjust24,0)+isnull(" + item.Adjust24 + ",0),Adjust25=isnull(Adjust25,0)+isnull(" + item.Adjust25 + ",0),Adjust26=isnull(Adjust26,0)+isnull(" + item.Adjust26 + ",0),Adjust27=isnull(Adjust27,0)+isnull(" + item.Adjust27
// + ",0),Adjust28=isnull(Adjust28,0)+isnull(" + item.Adjust28+ ",0) where S_OrgCode = '" + orgCode + "' and DATEDIFF(m,S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 and S_WorkerCode='"+ item.S_WorkerCode + "'; ";
//string updatesql = @"update tax_salary set S_UpdateBy='" + userId + "',S_UpdateDate='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "',Adjust9=isnull(Adjust9,0)+isnull(" + item.Adjust9 + ",0) where S_OrgCode = '" + orgCode + "' and DATEDIFF(m,S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 and S_WorkerCode='" + item.S_WorkerCode + "'; ";
//sqllst.Add(updatesql);
//updatesb.Append(updatesql);
}
//string sql = sb.ToString();
sqllst.Add(sb.ToString());
string updateSql = @"update a set a.S_UpdateBy ='" + userId + "',a.S_UpdateDate='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "'," + @"
a.Adjust9=isnull(a.Adjust9,0)+isnull(b.Adjust9,0),
a.Adjust10=isnull(a.Adjust10,0)+isnull(b.Adjust10,0),
a.Adjust11=isnull(a.Adjust11,0)+isnull(b.Adjust11,0),
a.Adjust12=isnull(a.Adjust12,0)+isnull(b.Adjust12,0),
a.Adjust13=isnull(a.Adjust13,0)+isnull(b.Adjust13,0),
a.Adjust14=isnull(a.Adjust14,0)+isnull(b.Adjust14,0),
a.Adjust15=isnull(a.Adjust15,0)+isnull(b.Adjust15,0),
a.Adjust16=isnull(a.Adjust16,0)+isnull(b.Adjust16,0),
a.Adjust17=isnull(a.Adjust17,0)+isnull(b.Adjust17,0),
a.Adjust18=isnull(a.Adjust18,0)+isnull(b.Adjust18,0),
a.Adjust19=isnull(a.Adjust19,0)+isnull(b.Adjust19,0),
a.Adjust20=isnull(a.Adjust20,0)+isnull(b.Adjust20,0),
a.Adjust21=isnull(a.Adjust21,0)+isnull(b.Adjust21,0),
a.Adjust22=isnull(a.Adjust22,0)+isnull(b.Adjust22,0),
a.Adjust23=isnull(a.Adjust23,0)+isnull(b.Adjust23,0),
a.Adjust24=isnull(a.Adjust24,0)+isnull(b.Adjust24,0),
a.Adjust25=isnull(a.Adjust25,0)+isnull(b.Adjust25,0),
a.Adjust26=isnull(a.Adjust26,0)+isnull(b.Adjust26,0),
a.Adjust27=isnull(a.Adjust27,0)+isnull(b.Adjust27,0),
a.Adjust28=isnull(a.Adjust28,0)+isnull(b.Adjust28,0)
from tax_salary a,tax_adjust b
where DATEDIFF(m,b.S_WorkDate,'" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 and b.S_Batch='" + batch + "' and DATEDIFF(m,a.S_WorkDate,b.S_WorkDate ) = 0 and a.S_WorkerCode = b.S_WorkerCode ";
sqllst.Add(updateSql);
//string sqlupdatesalary = "update tax_salary set T_DJJE=0 WHERE S_OrgCode = '" + orgCode + "' and DATEDIFF(m,S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 ";
//sqlupdatesalary += " UPDATE tax_reportstatus SET ReportStatus=0, S_UpdateBy='" + userId + "',S_UpdateDate='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "' where S_OrgCode = '" + orgCode + "' and DATEDIFF(m,S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 ";
//sqllst.Add(sqlupdatesalary);
return(db.Executs(sqllst));
//return db.ExecutByStringResult(sql);
}
public string createOneTimeBonus(List <ImportOneTimeBonus> d, DateTime dateMonth, string userId, string orgCode, string orgName)
{
DataTable dictiondt = db.GetDataTable(" SELECT * FROM tax_dictionary WHERE ParentCode='ZJLX' ");
List <string> sqllst = new List <string>();
int truckNum = Convert.ToInt32(Convert.ToDecimal(d.Count / 500));
int yushu = d.Count % 500;
if (yushu > 0)
{
truckNum++;
}
for (int i = 1; i < truckNum + 1; i++)
{
string fengefu = "";
StringBuilder sb = new StringBuilder();
sb.Append(@" insert into tax_onetimebonus (S_Id,S_CreateDate,S_CreateBy,S_WorkerCode,S_WorkerName,S_OrgName,S_OrgCode,OneTimeBonus,DeductibleTax,IdTypeName,IdType,IdNumber,FreeIncome,Other,AllowDeduction,TaxSaving,Remark,S_WorkDate) values ");
int rowbegin = (i - 1) * 500;
int rowend = i * 500;
if (rowend > d.Count)
{
rowend = d.Count;
}
for (int j = rowbegin; j < rowend; j++)
//foreach (ImportOneTimeBonus item in d)
{
DataRow[] ZJLX = dictiondt.Select("Name='" + d[j].IdTypeName + "'");
if (!string.IsNullOrEmpty(d[j].IdTypeName))
{
d[j].IdType = ZJLX[0]["Code"].ToString();
}
sb.Append(fengefu + "('" + Guid.NewGuid().ToString() + "',");
sb.Append("'" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "',");
sb.Append("'" + userId + "',");
sb.Append("'" + d[j].S_WorkerCode + "',");
sb.Append("'" + d[j].S_WorkerName + "',");
sb.Append("'" + orgName + "',");
sb.Append("'" + orgCode + "',");
sb.Append(d[j].OneTimeBonus + ",");
sb.Append(d[j].DeductibleTax + ",");
sb.Append(defaultNull(d[j].IdTypeName) + ",");
sb.Append(defaultNull(d[j].IdType) + ",");
sb.Append(defaultNull(d[j].IdNumber) + ",");
sb.Append(d[j].FreeIncome + ",");
sb.Append(d[j].Other + ",");
sb.Append(d[j].AllowDeduction + ",");
sb.Append(d[j].TaxSaving + ",");
sb.Append(defaultNull(d[j].Remark) + ",");
sb.Append("'" + dateMonth.ToString("yyyy-MM-dd") + "')");
fengefu = ",";
//string updatesql = @"update tax_salary set S_UpdateBy='" + userId + "',S_UpdateDate='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "',G_YCXJJ=isnull(G_YCXJJ,0)+isnull(" + item.OneTimeBonus + ",0),K_YCXJJYKS=isnull(K_YCXJJYKS,0)+isnull(" + item.DeductibleTax
// + ",0) where S_OrgCode = '" + orgCode + "' and DATEDIFF(m,S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 and S_WorkerCode='" + item.S_WorkerCode + "'";
//sqllst.Add(updatesql);
}
string sql = sb.ToString();
sqllst.Add(sql);
}
//string insertRecord = "insert into tax_reportstatus(S_Id,S_CreateDate,S_CreateBy,S_OrgName,S_OrgCode,ReportStatus,S_WorkDate) values('" + Guid.NewGuid().ToString() + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + userId + "','" + v.S_OrgName + "','" + v.S_OrgCode + "',0,'" + dateMonth.ToString("yyyy-MM-dd") + "')";
//sqllst.Add(insertRecord);
//string sqlupdatesalary = "update tax_salary set T_DJJE=0 WHERE S_OrgCode = '" + orgCode + "' and DATEDIFF(m,S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 ";
//sqlupdatesalary += " UPDATE tax_reportstatus SET ReportStatus=0, S_UpdateBy='" + userId + "',S_UpdateDate='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "' where S_OrgCode = '" + orgCode + "' and DATEDIFF(m,S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 ";
//sqllst.Add(sqlupdatesalary);
return(db.Executs(sqllst));
//return db.ExecutByStringResult(sql);
}
public string createTaxBusiness(List <ImportTaxBusiness> list, DateTime dateMonth, string userId, string importOrgCode, string importOrgName)
{
//string fengefu = "";
//int i = 0;//循环数，用于判断添加SQL语句头部；
//const int Num= 699;//Insert插入分条数目
//string headersql = "INSERT INTO tax_specialdeductions (S_Id,CreateBy,CreateDate,ImportOrgCode,S_WorkName,S_WorkNumber,IDtype,IDNumber,income,Tax,OlderInsurance,HeathInsurance,JobInsurance,HousingFund," +
// "ChildEdu,HousingLoan,HousingRent,Support,ContinueEdu,EnterpriseAnnuity,CommercialHealthinsurance,EndowmentInsurance,Other,Donation,Deductions," +
// "TaxSavings,Reduction,WithholdingTax,Remark,StartTime,EndTime,S_WorkDate,AccumulatedIncome,AccumulatedSpecialDeduction,CumulativeOther,TaxRate,QuickDeduction," +
// "AccumulatedTax,CumulativeWithholding,Drawback,IncomeItem,Cost) VALUES";
//StringBuilder sb = new StringBuilder();
//foreach (var item in list)
//{
// if (i%Num==0)
// {
// sb.Append(headersql);
// }
// sb.Append("('" + Guid.NewGuid() + "',");
// sb.Append("'" + userId + "',");
// sb.Append("'" + DateTime.Now.ToString("yyyy - MM - dd hh: mm:ss") + "',");
// sb.Append("'" + importOrgCode + "',");
// sb.Append("'" + item.Name + "',");
// sb.Append("'" + item.WorkNumber + "',");
// sb.Append("'" + item.IDType + "',");
// sb.Append("'" + item.IDNumber + "',");
// sb.Append(item.Income + ",");
// sb.Append(item.Tax + ",");
// sb.Append(item.OlderInsurance + ",");
// sb.Append(item.HeathInsurance + ",");
// sb.Append(item.JobInsurance + ",");
// sb.Append(item.HousingFund + ",");
// sb.Append(item.ChildEdu + ",");
// sb.Append(item.HousingLoan + ",");
// sb.Append(item.HousingRent + ",");
// sb.Append(item.Support + ",");
// sb.Append(item.ContinueEdu + ",");
// sb.Append(item.EnterpriseAnnuity + ",");
// sb.Append(item.CommercialHealthinsurance + ",");
// sb.Append(item.EndowmentInsurance + ",");
// sb.Append(item.Other + ",");
// sb.Append(item.Donation + ",");
// sb.Append(item.Deductions + ",");
// sb.Append(item.TaxSavings + ",");
// sb.Append(item.Reduction + ",");
// sb.Append(item.WithholdingTax + ",");
// sb.Append("'"+item.Remark + "',");
// sb.Append("'" + item.StartTime + "',");
// sb.Append("'" + item.EndTime + "',");
// sb.Append("'" + dateMonth + "',");
// sb.Append(item.AccumulatedIncome + ",");
// sb.Append(item.AccumulatedSpecialDeduction + ",");
// sb.Append(item.CumulativeOther + ",");
// sb.Append(item.TaxRate + ",");
// sb.Append(item.QuickDeduction + ",");
// sb.Append(item.AccumulatedTax + ",");
// sb.Append(item.CumulativeWithholding + ",");
// sb.Append(item.Drawback + ",");
// sb.Append("'"+item.IncomeItem + "',");
// sb.Append(item.Cost + ") ");
// i++;
// if (i% Num == 0||item.Equals(list[list.Count-1]))
// {
// sb.Append(fengefu);
// }
// else
// {
// sb.Append(",");
// }
//}
//string sql = sb.ToString();
//return db.ExecutByStringResult(sql);
ImportSqlServer importSql = new ImportSqlServer();
string sql = "SELECT * from tax_specialdeductions where CreateBy='构造新的dt'";
DataTable dt = db.GetDataTable(sql);
foreach (var item in list)
{
DataRow dr = dt.NewRow();
dr["S_Id"] = Guid.NewGuid();
dr["CreateBy"] = userId;
dr["CreateDate"] = DateTime.Now.ToString("yyyy - MM - dd hh: mm:ss");
dr["S_WorkName"] = item.Name;
dr["S_WorkNumber"] = item.WorkNumber;
dr["IDType"] = item.IDType;
dr["IDNumber"] = item.IDNumber;
dr["Income"] = item.Income;
dr["Tax"] = item.Tax;
dr["OlderInsurance"] = item.OlderInsurance;
dr["HeathInsurance"] = item.HeathInsurance;
dr["JobInsurance"] = item.JobInsurance;
dr["HousingFund"] = item.HousingFund;
dr["ChildEdu"] = item.ChildEdu;
dr["HousingLoan"] = item.HousingLoan;
dr["HousingRent"] = item.HousingRent;
dr["Support"] = item.Support;
dr["ContinueEdu"] = item.ContinueEdu;
dr["EnterpriseAnnuity"] = item.EnterpriseAnnuity;
dr["CommercialHealthinsurance"] = item.CommercialHealthinsurance;
dr["EndowmentInsurance"] = item.EndowmentInsurance;
dr["Other"] = item.Other;
dr["Donation"] = item.Donation;
dr["Deductions"] = item.Deductions;
dr["TaxSavings"] = item.TaxSavings;
dr["Reduction"] = item.Reduction;
dr["WithholdingTax"] = item.WithholdingTax;
dr["Remark"] = item.Remark;
dr["S_WorkDate"] = dateMonth;
dr["ImportOrgCode"] = importOrgCode;
dr["StartTime"] = item.StartTime;
dr["EndTime"] = item.EndTime;
dr["AccumulatedIncome"] = item.AccumulatedIncome;
dr["AccumulatedSpecialDeduction"] = item.AccumulatedSpecialDeduction;
dr["CumulativeOther"] = item.CumulativeOther;
dr["TaxRate"] = item.TaxRate;
dr["QuickDeduction"] = item.QuickDeduction;
dr["AccumulatedTax"] = item.AccumulatedTax;
dr["CumulativeWithholding"] = item.CumulativeWithholding;
dr["Drawback"] = item.Drawback;
dr["IncomeItem"] = item.IncomeItem;
dr["Cost"] = item.Cost;
dt.Rows.Add(dr);
}
string msg = importSql.Import(dt, "tax_specialdeductions_copy1");
if (msg == "2000")
{
List <string> sqllist = new List <string>();
string sql1 = "INSERT INTO tax_specialdeductions SELECT * FROM tax_specialdeductions_copy1";
sql1 += " WHERE S_Id NOT IN";
sql1 += " (SELECT S_Id FROM tax_specialdeductions WHERE DATEDIFF(mm, S_WorkDate, '" + dateMonth + "')=0)";
string sql2 = " DELETE FROM tax_specialdeductions_copy1";
sqllist.Add(sql1);
sqllist.Add(sql2);
return(db.Executs(sqllist));
}
else
{
return("失败");
}
}
public string createTaxBonus(List <ImportTaxBonus> d, DateTime dateMonth, string userId, string orgCode, string orgName)
{
List <string> sqllst = new List <string>();
string fengefu = "";
int i = 0; //循环数，用于判断添加SQL语句头部；
const int Num = 600; //Insert插入分条数目
string headersql = " insert into tax_onebonus (S_Id,S_CreateDate,S_CreateBy,S_WorkerCode,S_WorkerName,S_OrgName,S_OrgCode,OneTimeBonus,DeductibleTax,S_Batch,S_WorkDate) values ";
StringBuilder sb = new StringBuilder();
//StringBuilder updatesb = new StringBuilder();
string batch = Guid.NewGuid().ToString();
//sb.Append(@" insert into tax_onebonus (S_Id,S_CreateDate,S_CreateBy,S_WorkerCode,S_WorkerName,S_OrgName,S_OrgCode,OneTimeBonus,DeductibleTax,S_WorkDate) values ");
foreach (ImportTaxBonus item in d)
{
if (i % Num == 0)
{
sb.Append(headersql);
}
sb.Append(fengefu + "('" + Guid.NewGuid().ToString() + "',");
sb.Append("'" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "',");
sb.Append("'" + userId + "',");
sb.Append("'" + item.S_WorkerCode + "',");
sb.Append("'" + item.S_WorkerName + "',");
sb.Append("'" + orgName + "',");
sb.Append("'" + orgCode + "',");
sb.Append(item.OneTimeBonus + ",");
sb.Append(item.DeductibleTax + ",");
sb.Append("'" + batch + "',");
sb.Append("'" + dateMonth.ToString("yyyy-MM-dd") + "')");
i++;
if (i % Num == 0 || item.Equals(d[d.Count - 1]))
{
sb.Append(fengefu);
}
else
{
sb.Append(",");
}
//fengefu = ",";
//string updatesql = @"update tax_salary set S_UpdateBy='" + userId + "',S_UpdateDate='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "',G_YCXJJ=isnull(G_YCXJJ,0)+isnull(" + item.OneTimeBonus + ",0),K_YCXJJYKS=isnull(K_YCXJJYKS,0)+isnull(" + item.DeductibleTax
// + ",0) where S_OrgCode = '" + orgCode + "' and DATEDIFF(m,S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 and S_WorkerCode='" + item.S_WorkerCode + "'; ";
//updatesb.Append(updatesql);
//sqllst.Add(updatesql);
}
//string insertRecord = "insert into tax_reportstatus(S_Id,S_CreateDate,S_CreateBy,S_OrgName,S_OrgCode,ReportStatus,S_WorkDate) values('" + Guid.NewGuid().ToString() + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + userId + "','" + v.S_OrgName + "','" + v.S_OrgCode + "',0,'" + dateMonth.ToString("yyyy-MM-dd") + "')";
//sqllst.Add(insertRecord);
//string sql = sb.ToString();
sqllst.Add(sb.ToString());
//sqllst.Add(updatesb.ToString());
//string sqlupdatesalary = "update tax_salary set T_DJJE=0 WHERE S_OrgCode = '" + orgCode + "' and DATEDIFF(m,S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 ";
//sqlupdatesalary += " UPDATE tax_reportstatus SET ReportStatus=0, S_UpdateBy='" + userId + "',S_UpdateDate='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "' where S_OrgCode = '" + orgCode + "' and DATEDIFF(m,S_WorkDate, '" + dateMonth.ToString("yyyy-MM-dd") + "') = 0 ";
//sqllst.Add(sqlupdatesalary);
string updateSql = @"update a set a.S_UpdateBy ='" + userId + "',a.S_UpdateDate='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "'," + @"
a.G_YCXJJ=isnull(a.G_YCXJJ,0)+isnull(b.OneTimeBonus,0),
a.K_YCXJJYKS=isnull(a.K_YCXJJYKS,0)+isnull(b.DeductibleTax,0)
from tax_salary a,tax_onebonus b
where b.S_WorkDate='" + dateMonth.ToString("yyyy-MM-dd") + "' and b.S_Batch='" + batch + "' and DATEDIFF(m,a.S_WorkDate,b.S_WorkDate ) = 0 and a.S_WorkerCode = b.S_WorkerCode ";
sqllst.Add(updateSql);
return(db.Executs(sqllst));
//return db.ExecutByStringResult(sql);
}
public string CreateInfo(Dictionary <string, object> d)
{
List <string> sqllist = new List <string>();
string sql = " INSERT INTO jy_clbx (CLBH,DWBM,CCXM,CCSY,CCKSSJ,CCJSSJ,CCTS,HJJE,HJDX,YJCLF,YTBJE,REMARK,SKRXM,CJR,CJSJ,IS_DELETE,XMBH,XMMC,DWMC,PROCESS_STATE) VALUES (";
sql += GetSQLStr(d["CLBH"]);
sql += GetSQLStr(d["DWBM"]);
sql += GetSQLStr(d["CCXM"]);
sql += GetSQLStr(d["CCSY"]);
sql += GetSQLStr(d["CCKSSJ"]);
sql += GetSQLStr(d["CCJSSJ"]);
sql += GetSQLStr(d["CCTS"], 1);
sql += GetSQLStr(d["HJJE"], 1);
sql += GetSQLStr(d["HJDX"]);
sql += GetSQLStr(d["YJCLF"], 1);
sql += GetSQLStr(d["YTBJE"], 1);
sql += GetSQLStr(d["REMARK"]);
sql += GetSQLStr(d["SKRXM"]);
sql += GetSQLStr(d["userId"]);
sql += GetSQLStr(DateTime.Now);
sql += GetSQLStr("0", 1);
sql += GetSQLStr(d["XMBH"]);
sql += GetSQLStr(d["XMMC"]);
sql += GetSQLStr(d["DWMC"]);
sql += GetSQLStr(0, 1);
sql = sql.TrimEnd(',');
sql += ")";
sqllist.Add(sql);
List <Dictionary <string, object> > diclist = JArray.Parse(d["XCList"].ToString()).ToObject <List <Dictionary <string, object> > >();
foreach (Dictionary <string, object> dd in diclist)
{
if ((dd["CFRQ"] == null || dd["CFRQ"].ToString() == "") && (d["DDRQ"] == null || d["DDRQ"].ToString() == ""))
{
continue;
}
else
{
string sql1 = "INSERT INTO jy_clxc (XCID,CLBH,CFRQ,DDRQ,CCDD,CQTS,CQBZ,BZJE,BFB,TS,HCFY,FCLB,FCXB,FCJE,ZFLB,ZFJE,CJR,CJSJ,IS_DELETE,CFDD) VALUES(";
sql1 += GetSQLStr(Guid.NewGuid());
sql1 += GetSQLStr(d["CLBH"]);
sql1 += GetSQLStr(dd["CFRQ"]);
sql1 += GetSQLStr(dd["DDRQ"]);
sql1 += GetSQLStr(dd["CCDD"]);
sql1 += GetSQLStr(dd["CQTS"], 1);
sql1 += GetSQLStr(dd["CQBZ"], 1);
sql1 += GetSQLStr(dd["BZJE"], 1);
sql1 += GetSQLStr(dd["BFB"]);
sql1 += GetSQLStr(dd["TS"], 1);
sql1 += GetSQLStr(dd["HCFY"], 1);
sql1 += GetSQLStr(dd["FCLB"]);
sql1 += GetSQLStr(dd["FCXB"]);
sql1 += GetSQLStr(dd["FCJE"], 1);
sql1 += GetSQLStr(dd["ZFLB"]);
sql1 += GetSQLStr(dd["ZFJE"], 1);
sql1 += GetSQLStr(d["userId"]);
sql1 += GetSQLStr(DateTime.Now);
sql1 += GetSQLStr(0, 1);
sql1 += GetSQLStr(dd["CFDD"]);
sql1 = sql1.TrimEnd(',');
sql1 += ")";
sqllist.Add(sql1);
}
}
return(db.Executs(sqllist));
}
public string CreateNotification(List <string> list)
{
return(db.Executs(list));
}
public string createTaxPayerInfo(List <ImportTaxPayerInfo> list, string userId)
{
//return DB.GetDataTable(sql);
int errorNum = 1;//错误行数
DataTable taxpayerdt = DB.GetDataTable(" select WorkerNumber from tax_taxpayerinfo ");
DataTable dictiondt = DB.GetDataTable(" SELECT * FROM tax_dictionary WHERE ParentCode='ZJLX' OR ParentCode='GJ' OR ParentCode='XL' OR ParentCode='ZW' " +
"OR ParentCode='ZY' OR ParentCode='QTZJLX' OR ParentCode='RZSGCYLX'");
List <string> sqllst = new List <string>();
string fengefu = "";
string delsql = "DELETE FROM tax_taxpayerinfo WHERE WorkerNumber='";
string headerSQL = "INSERT INTO tax_taxpayerinfo(S_Id,S_CreateDate,S_CreateBy,S_OrgName,WorkerNumber,WorkerName,IdType,IdNumber,Nationality,Sex,WorkerStatus,WorkerStatusId,BirthDate,JobType," +
"Tel,EmployeeDate,QuitDate,IsDisability,IsLieShu,IsAlone,DisabilityNo,LiShuZH,Investment,PerInvestment,Remark,IsAbroad,BroadName,BirthPlace,FirstEntryTime,EstimatedDepartureTime,OtherIdType," +
"OtherIdNumber,Province,City,County,Domicile,Adress_Province,Adress_City,Adress_County,PostalAddress,L_Province,L_City,L_County,L_Adress,Email,Education,BankName,BankNumber,WorkPost)VALUES(";
StringBuilder sb = new StringBuilder();
StringBuilder sb1 = new StringBuilder();
try
{
foreach (ImportTaxPayerInfo item in list)
{
sb.Append(headerSQL);
DataRow[] rows = taxpayerdt.Select("WorkerNumber='" + item.WorkerNumber + "'");
if (rows.Length > 0)
{
//sqllst.Add("delete from tax_taxpayerinfo where WorkerNumber='" + item.WorkerNumber + "'");
sb1.Append(delsql);
sb1.Append(item.WorkerNumber + "' ");
}
DataRow[] GJ = dictiondt.Select("Name='" + item.Nationality + "'");
DataRow[] ZJLX = dictiondt.Select("Name='" + item.IdType + "'");
DataRow[] XL = dictiondt.Select("Name='" + item.Education + "'");
DataRow[] ZW = dictiondt.Select("Name='" + item.WorkPost + "'");
DataRow[] QTZJLX = dictiondt.Select("Name='" + item.OtherIdType + "'");
DataRow[] RZSGLX = dictiondt.Select("Name='" + item.JobType + "'");
if (!string.IsNullOrEmpty(item.IdType))
{
if (ZJLX.Length > 0)
{
item.IdTypeCode = ZJLX[0]["Code"].ToString();
}
//item.IdTypeCode = ZJLX[0]["Code"].ToString();
}
if (!string.IsNullOrEmpty(item.Nationality))
{
//item.NationalityId = GJ[0]["Code"].ToString();
if (GJ.Length > 0)
{
item.NationalityId = GJ[0]["Code"].ToString();
}
}
if (!string.IsNullOrEmpty(item.Education))
{
//item.EducationCode = XL[0]["Code"].ToString();
if (XL.Length > 0)
{
item.EducationCode = XL[0]["Code"].ToString();
}
}
if (!string.IsNullOrEmpty(item.WorkPost))
{
//item.WorkPostCode = ZW[0]["Code"].ToString();
if (ZW.Length > 0)
{
item.WorkPostCode = ZW[0]["Code"].ToString();
}
}
if (!string.IsNullOrEmpty(item.OtherIdType))
{
//item.OtherIdTypeCode = QTZJLX[0]["Code"].ToString();
if (QTZJLX.Length > 0)
{
item.OtherIdTypeCode = QTZJLX[0]["Code"].ToString();
}
}
if (!string.IsNullOrEmpty(item.JobType))
{
//item.JobTypeCode = RZSGLX[0]["Code"].ToString();
if (RZSGLX.Length > 0)
{
item.JobTypeCode = RZSGLX[0]["Code"].ToString();
}
}
sb.Append("'" + Guid.NewGuid() + "',");
sb.Append("'" + DateTime.Now + "',");
sb.Append("'" + userId + "',");
sb.Append(defaultNull(item.S_OrgName) + ",");
sb.Append(defaultNull(item.WorkerNumber) + ",");
sb.Append(defaultNull(item.WorkerName) + ",");
sb.Append(defaultNull(item.IdTypeCode) + ",");
sb.Append(defaultNull(item.IdNumber) + ",");
sb.Append(defaultNull(item.NationalityId) + ",");
sb.Append(defaultZero(item.Sex) + ",");
sb.Append(defaultNull(item.WorkerStatus) + ",");
sb.Append(Convert.ToInt32(item.WorkerStatus) + ",");
sb.Append(defaultNull(item.BirthDate) + ",");
sb.Append(defaultNull(item.JobTypeCode) + ",");
sb.Append(defaultNull(item.Tel) + ",");
sb.Append(defaultNull(item.EmployeeDate) + ",");
sb.Append(defaultNull(item.QuitDate) + ",");
sb.Append(Convert.ToInt32(item.IsDisability) + ",");
sb.Append(Convert.ToInt32(item.IsLieShu) + ",");
sb.Append(Convert.ToInt32(item.IsAlone) + ",");
sb.Append(defaultNull(item.DisabilityNo) + ",");
sb.Append(defaultNull(item.LiShuZH) + ",");
sb.Append(defaultNull(item.Investment) + ",");
sb.Append(item.PerInvestment + ",");
sb.Append(defaultNull(item.Remark) + ",");
sb.Append(Convert.ToInt32(item.IsAbroad) + ",");
sb.Append(defaultNull(item.BroadName) + ",");
sb.Append(defaultNull(item.BirthPlace) + ",");
sb.Append(defaultNull(item.FirstEntryTime) + ",");
sb.Append(defaultNull(item.EstimatedDepartureTime) + ",");
sb.Append(defaultNull(item.OtherIdTypeCode) + ",");
sb.Append(defaultNull(item.OtherIdNumber) + ",");
sb.Append(defaultNull(item.Province) + ",");
sb.Append(defaultNull(item.City) + ",");
sb.Append(defaultNull(item.County) + ",");
sb.Append(defaultNull(item.Domicile) + ",");
sb.Append(defaultNull(item.Adress_Province) + ",");
sb.Append(defaultNull(item.Adress_City) + ",");
sb.Append(defaultNull(item.Adress_County) + ",");
sb.Append(defaultNull(item.PostalAddress) + ",");
sb.Append(defaultNull(item.L_Province) + ",");
sb.Append(defaultNull(item.L_City) + ",");
sb.Append(defaultNull(item.L_County) + ",");
sb.Append(defaultNull(item.L_Adress) + ",");
sb.Append(defaultNull(item.Email) + ",");
sb.Append(defaultNull(item.EducationCode) + ",");
sb.Append(defaultNull(item.BankName) + ",");
sb.Append(defaultNull(item.BankNumber) + ",");
sb.Append(defaultNull(item.WorkPostCode) + ")");
fengefu = ",";
errorNum++;
}
string sql = sb.ToString();
string sql1 = sb1.ToString();
if (sql1.Length > 0)
{
sqllst.Add(sql1);
}
sqllst.Add(sql);
}
catch (Exception e)
{
return("转换失败，第" + errorNum + "条");
}
#region 以下是老模板的insert语句
//sb.Append(@" insert into tax_taxpayerinfo (S_Id,S_CreateDate,S_CreateBy,WorkerNumber,WorkerName,S_OrgName,IdType,IdNumber,Nationality,Sex,BirthDate,WorkerStatus,
// IsEmployee,Tel,EmployeeDate,IsDisability,Education,Occupation,WorkPost,DisabilityNo,IsLieShu,LiShuZH,IsAlone,Email,BankName,BankNumber,
// IsSpecialIndustry,PostalAddress,Domicile,Remark,IsWorking,QuitDate,IsShareholder,Investment,Province,City,County,Adress_Province,Adress_City,Adress_County,
// IsAbroad,BroadName,IsLive,BirthPlace,FirstEntryTime,ThisYearEntryTime,EstimatedDepartureTime,S_Province,S_City,S_County,S_Address,PayPlace,
// OtherPayPlace,ChinaPost,UnChinaPost,OfficeTime,TaxpayersNumber) values ");
//foreach (ImportTaxPayerInfo item in list)
//{
// DataRow[] rows = taxpayerdt.Select("WorkerNumber='" + item.WorkerNumber + "'");
// if (rows.Length > 0)
// {
// sqllst.Add("delete from tax_taxpayerinfo where WorkerNumber='" + item.WorkerNumber + "'");
// }
// DataRow[] GJ = dictiondt.Select("Name='" + item.Nationality + "'");
// DataRow[] ZJLX = dictiondt.Select("Name='" + item.IdType + "'");
// DataRow[] XL = dictiondt.Select("Name='" + item.Education + "'");
// //DataRow[] ZY = dictiondt.Select("Name='" + item.Occupation + "'");
// DataRow[] ZW = dictiondt.Select("Name='" + item.WorkPost + "'");
// if (!string.IsNullOrEmpty(item.IdType)){
// item.IdTypeCode = ZJLX[0]["Code"].ToString();
// }
// if (!string.IsNullOrEmpty(item.Nationality))
// {
// item.NationalityId = GJ[0]["Code"].ToString();
// }
// if (!string.IsNullOrEmpty(item.Education))
// {
// item.EducationCode = XL[0]["Code"].ToString();
// }
// //if (!string.IsNullOrEmpty(item.Occupation))
// //{
// // item.OccupationCode = ZY[0]["Code"].ToString();
// //}
// if (!string.IsNullOrEmpty(item.WorkPost))
// {
// item.WorkPostCode = ZW[0]["Code"].ToString();
// }
// sb.Append(fengefu + "('" + Guid.NewGuid().ToString() + "',");
// sb.Append("'" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "',");
// sb.Append(defaultNull(userId) + ",");
// sb.Append(defaultNull(item.WorkerNumber) + ",");
// sb.Append(defaultNull(item.WorkerName) + ",");
// sb.Append(defaultNull(item.S_OrgName) + ",");
// //sb.Append("'" + orgCode + "',");
// sb.Append(defaultNull(item.IdTypeCode) + ",");
// sb.Append(defaultNull(item.IdNumber) + ",");
// sb.Append(defaultNull(item.NationalityId) + ",");
// sb.Append(item.Sex + ",");
// sb.Append(defaultNull(item.BirthDate) + ",");
// sb.Append(defaultOne(item.WorkerStatus) + ",");
// sb.Append(defaultZero(item.IsEmployee) + ",");
// sb.Append(defaultNull(item.Tel) + ",");
// sb.Append(defaultNull(item.EmployeeDate)+ ",");
// sb.Append(defaultZero(item.IsDisability) + ",");
// sb.Append(defaultNull(item.EducationCode) + ",");
// sb.Append(defaultNull(item.OccupationCode) + ",");
// sb.Append(defaultNull(item.WorkPostCode) + ",");
// sb.Append(defaultNull(item.DisabilityNo) + ",");
// sb.Append(defaultZero(item.IsLieShu) + ",");
// sb.Append(defaultNull(item.LiShuZH) + ",");
// sb.Append(defaultZero(item.IsAlone) + ",");
// sb.Append(defaultNull(item.Email) + ",");
// sb.Append(defaultNull(item.BankName) + ",");
// sb.Append(defaultNull(item.BankNumber) + ",");
// sb.Append(defaultZero(item.IsSpecialIndustry) + ",");
// sb.Append(defaultNull(item.PostalAddress) + ",");
// sb.Append(defaultNull(item.Domicile) + ",");
// sb.Append(defaultNull(item.Remark) + ",");
// sb.Append(defaultZero(item.IsWorking) + ",");
// sb.Append(defaultNull(item.QuitDate) + ",");
// sb.Append(defaultZero(item.IsShareholder) + ",");
// sb.Append(defaultNull(item.Investment) + ",");
// sb.Append(defaultNull(item.Province) + ",");
// sb.Append(defaultNull(item.City) + ",");
// sb.Append(defaultNull(item.County) + ",");
// sb.Append(defaultNull(item.Adress_Province) + ",");
// sb.Append(defaultNull(item.Adress_City) + ",");
// sb.Append(defaultNull(item.Adress_County) + ",");
// sb.Append(defaultZero(item.IsAbroad)+ ",");
// sb.Append(defaultNull(item.BroadName) + ",");
// sb.Append(defaultZero(item.IsLive) + ",");
// sb.Append(defaultNull(item.BirthPlace) + ",");
// sb.Append(defaultNull(item.FirstEntryTime)+ ",");
// sb.Append(defaultNull(item.ThisYearEntryTime) + ",");
// sb.Append(defaultNull(item.EstimatedDepartureTime) + ",");
// sb.Append(defaultNull(item.S_Province) + ",");
// sb.Append(defaultNull(item.S_City) + ",");
// sb.Append(defaultNull(item.S_County) + ",");
// sb.Append(defaultNull(item.S_Address) + ",");
// sb.Append(defaultNull(item.PayPlace) + ",");
// sb.Append(defaultNull(item.OtherPayPlace) + ",");
// sb.Append(defaultNull(item.ChinaPost) + ",");
// sb.Append(defaultNull(item.UnChinaPost) + ",");
// sb.Append(defaultZero(item.OfficeTime) + ",");
// sb.Append(defaultNull(item.TaxpayersNumber)+")");
// fengefu = ",";
//}
#endregion
#region 很早就注释了
////string sqldel = "delete from tax_reportstatus where datediff(m,S_WorkDate,'" + dateMonth.ToString("yyyy-MM-dd") + "')=0 and S_OrgCode ='" + orgCode + "'";
////sqllst.Add(sqldel);
////string insertRecord = "insert into tax_reportstatus(S_Id,S_CreateDate,S_CreateBy,S_OrgName,S_OrgCode,ReportStatus,S_WorkDate) values('" + Guid.NewGuid().ToString() + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + userId + "','" + orgName + "','" + orgCode + "',0,'" + dateMonth.ToString("yyyy-MM-dd") + "')";
////sqllst.Add(insertRecord);
#endregion
string upsql = @"update a set a.S_OrgCode = b.ORG_CODE from tax_taxpayerinfo a,ts_uidp_org b
where a.S_OrgName = b.ORG_NAME";
sqllst.Add(upsql);
return(DB.Executs(sqllst));
//return db.ExecutByStringResult(sql);
}
