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