1 public HandlerResponse UpLoadBank() 2 { 3 HandlerResponse hres = new HandlerResponse(); 4 this.Context.Response.AddHeader("Content-Type", "text/html; charset=utf-8"); 5 DataTable dt = new DataTable("BankT"); 6 7 #region 规则 8 //必须有商户号和生效日期,才进行导入, 9 //且必商户号和生效日期存在，才更新，这里不进行插入数据 10 #endregion 11 int pointIndex = Context.Request.Files[0].FileName.LastIndexOf("."); 12 string lastName = Context.Request.Files[0].FileName.Substring(pointIndex); 13 #region 检查文件格式 14 if (lastName != ".xlsx") 15 { 16 hres.ErrorCode = "0000000001"; 17 hres.ErrorMessage = "文件上传格式不正确，只支持.xlsx文件"; 18 return hres; 19 } 20 #endregion 21 // string userAccount =ObjectToData.getString(context.Request["userAccount"]); 22 if (Context.Request.Files[0].ContentLength < 1) { throw new Exception("您上传的文件大小为0K,文件名:" + Context.Request.Files[0].FileName); } 23 XLWorkbook workbook = new XLWorkbook(Context.Request.Files[0].InputStream); 24 IXLWorksheet worksheet = workbook.Worksheet(1); 25 #region 制作表 26 27 // dt.Columns.Add("ID"); 28 dt.Columns.Add("merchant_id");//商户ID 29 dt.Columns.Add("bank_account");//银行账户 30 dt.Columns.Add("bank_name");//开户银行 31 dt.Columns.Add("sub_bank_name");//开户支行 32 dt.Columns.Add("lian_hang_number");//联行号 33 dt.Columns.Add("account_name");//开户人姓名 34 dt.Columns.Add("iden_sn");//身份证号码 35 dt.Columns.Add("telephone");//持卡人联系电话 36 37 #endregion 38 int merchant_id; 39 HashSet<string> merchanthashset = new HashSet<string>(); 40 41 #region 赋值 42 for (int i = 2; i <= 50000; i++) 43 { 44 int is_permit = 0;//必须有商户号和生效日期，且 45 var row = worksheet.Row(i); 46 //得到标题，然后动态的获取列 47 if (row != null && row.Cell(1).Value.ToString().Length > 0) 48 { 49 DataRow dataRow = dt.NewRow(); 50 int merchantColumn = GetColumnNum(dt, "商户号", worksheet); 51 52 if (merchantColumn > 0) 53 { 54 is_permit++; 55 if (!int.TryParse(row.Cell(merchantColumn).Value.ToString(), out merchant_id)) 56 { 57 hres.ErrorCode = "0000000001"; 58 hres.ErrorMessage = "第" + i + "行，" + merchantColumn + "列，" + row.Cell(merchantColumn).Value.ToString() + "商户号格式不正确"; 59 return hres; 60 } 61 dataRow["merchant_id"] = row.Cell(merchantColumn).Value; 62 merchanthashset.Add(row.Cell(merchantColumn).Value.ToString().Trim()); 63 } 64 // /^\d{1,30}$/ 65 int bank_accountColumn = GetColumnNum(dt, "银行账号", worksheet); 66 if (bank_accountColumn > 0) //1收单手续J卡。 67 { 68 is_permit++; 69 if (isBank_account(row.Cell(bank_accountColumn).Value.ToString()) == false) 70 { 71 hres.ErrorCode = "0000000001"; 72 hres.ErrorMessage = "第" + i + "行，" + bank_accountColumn + "列，银行账号为30位以内的数字!"; 73 return hres; 74 // throw new Exception(); 75 } 76 77 dataRow["bank_account"] = row.Cell(bank_accountColumn).Value; 78 } 79 int bank_nameColumn = GetColumnNum(dt, "开户银行", worksheet); 80 if (bank_nameColumn > 0) //2 收单手续费V卡 81 { 82 is_permit++; 83 if (row.Cell(bank_nameColumn).Value.ToString().Trim().Length > 50 || row.Cell(bank_nameColumn).Value.ToString().Trim().Length == 0) 84 { 85 hres.ErrorCode = "0000000001"; 86 hres.ErrorMessage = "第" + i + "行，" + bank_nameColumn + "列，开户银行长度应大于0小于50"; 87 return hres; 88 //throw new Exception("收单手续费M卡允许是0-100之间的有效数字"); 89 } 90 91 92 dataRow["bank_name"] = row.Cell(bank_nameColumn).Value; 93 } 94 int sub_bank_nameColumn = GetColumnNum(dt, "开户支行", worksheet); 95 if (sub_bank_nameColumn > 0) //3 收单手续费M卡 96 { 97 is_permit++; 98 if (row.Cell(sub_bank_nameColumn).Value.ToString().Trim().Length > 50 || row.Cell(sub_bank_nameColumn).Value.ToString().Trim().Length == 0) 99 { 100 hres.ErrorCode = "0000000001"; 101 hres.ErrorMessage = "第" + i + "行，" + sub_bank_nameColumn + "列，开户支行长度应大于0小于50"; 102 return hres; 103 //throw new Exception("收单手续费M卡允许是0-100之间的有效数字"); 104 } 105 106 dataRow["sub_bank_name"] = row.Cell(sub_bank_nameColumn).Value; 107 } 108 ///^\d{1,20}$/ 109 int lian_hang_numberColumn = GetColumnNum(dt, "联行号", worksheet); 110 if (lian_hang_numberColumn > 0) 111 { 112 is_permit++; 113 if (isLian_hang_numberColumn(row.Cell(lian_hang_numberColumn).Value.ToString()) == false) 114 { 115 hres.ErrorCode = "0000000001"; 116 hres.ErrorMessage = "第" + i + "行，" + lian_hang_numberColumn + "列，联行号为20位以内的数字"; 117 return hres; 118 // throw new Exception("结算周期格式错误"); 119 } 120 dataRow["lian_hang_number"] = row.Cell(lian_hang_numberColumn).Value; 121 } 122 int account_nameColumn = GetColumnNum(dt, "开户人姓名", worksheet); 123 if (account_nameColumn > 0) 124 { 125 is_permit++; 126 if (row.Cell(account_nameColumn).Value.ToString().Trim().Length > 50 || row.Cell(account_nameColumn).Value.ToString().Trim().Length == 0) 127 { 128 hres.ErrorCode = "0000000001"; 129 hres.ErrorMessage = "第" + i + "行，" + account_nameColumn + "列，开户人姓名长度应大于0小于50"; 130 return hres; 131 //throw new Exception("收单手续费M卡允许是0-100之间的有效数字"); 132 } 133 dataRow["account_name"] = row.Cell(account_nameColumn).Value; 134 } 135 int iden_snColumn = GetColumnNum(dt, "身份证号码", worksheet); 136 137 if (iden_snColumn > 0) 138 { 139 if (row.Cell(iden_snColumn).Value.ToString().Trim().Length > 20 || row.Cell(iden_snColumn).Value.ToString().Trim().Length == 0) 140 { 141 hres.ErrorCode = "0000000001"; 142 hres.ErrorMessage = "第" + i + "行，" + iden_snColumn + "列，身份证号码名长度应大于0小于20"; 143 return hres; 144 //throw new Exception("收单手续费M卡允许是0-100之间的有效数字"); 145 } 146 dataRow["iden_sn"] = row.Cell(iden_snColumn).Value; 147 } 148 int telephoneColumn = GetColumnNum(dt, "手机号码", worksheet); 149 if (telephoneColumn > 0) 150 { 151 if (isLian_hang_numberColumn(row.Cell(telephoneColumn).Value.ToString()) == false) 152 { 153 hres.ErrorCode = "0000000001"; 154 hres.ErrorMessage = "第" + i + "行，" + telephoneColumn + "列，手机号码为20位以内的数字"; 155 return hres; 156 // throw new Exception(" "); 157 } 158 dataRow["telephone"] = row.Cell(telephoneColumn).Value; 159 } 160 if (is_permit < 6) 161 { 162 hres.ErrorMessage = "导入数据中必须存在商户号,银行账号,开户银行,开户支行,联行号,开户人姓名"; 163 hres.ErrorCode = "0000000001"; 164 return hres; 165 } 166 dt.Rows.Add(dataRow); 167 } 168 else 169 { 170 break; 171 } 172 } 173 #endregion 174 #region 更新 175 MB_DAL.MerManage.MerMerchBankAccount mermerchbank = new MB_DAL.MerManage.MerMerchBankAccount(); 176 MB_DAL.MerManage.MerMerch mermerch = new MB_DAL.MerManage.MerMerch(); 177 178 179 eap.share.DAO trandao = new eap.share.DAO(); 180 DataTable temp = new DataTable(); 181 182 #region 查询数据是否存在 183 184 for (int i = 0; i < dt.Rows.Count; i++) 185 { 186 temp = mermerchbank.GetMerMerchBankAccount(dt.Rows[i]["merchant_id"].ToString(), dt.Rows[i]["bank_account"].ToString()); 187 if (temp.Rows.Count > 0) 188 { 189 hres.ErrorCode = "0000000001"; 190 hres.ErrorMessage = "商户号：" + dt.Rows[0]["merchant_id"].ToString() + ",银行账号" + dt.Rows[i]["bank_account"].ToString() + "已存在"; 191 return hres; 192 } 193 else 194 { 195 196 } 197 } 198 199 #endregion 200 #region 检查商户是否存在 201 foreach (var s in merchanthashset) 202 { 203 if (mermerch.CheckMerchant(s.ToString(), 1).Rows.Count <= 0) 204 { 205 hres.ErrorCode = "0000000001"; 206 hres.ErrorMessage = "商户号：" + s + "不存在或者未开通"; 207 return hres; 208 } 209 } 210 #endregion 211 StringBuilder log_desc = new StringBuilder(); 212 CMLoger.WriteDebugLog("[商户管理-结算信息维护]－银行账户导入【开始】，操作人" + "[" + base.strUserAccount + "-" + base.strUserDisplayName + "]"); 213 log_desc.Append("[商户管理-结算信息维护]-银行账户导入"); 214 //trandao.BeginTransaction(); 215 try 216 { 217 MB_DAL.MerManage.MerMerchFee mermerchFee = new MB_DAL.MerManage.MerMerchFee(); 218 MB_DAL.MerManage.MerMerchFeeDetail mermerchFeeDetail = new MB_DAL.MerManage.MerMerchFeeDetail(); 219 for (int i = 0; i < dt.Rows.Count; i++) 220 { 221 try 222 { 223 mermerchbank.InsertMerMerchBankAccountTran(trandao, dt.Rows[i]["merchant_id"].ToString().Trim(), dt.Rows[i]["bank_account"].ToString().Trim(), dt.Rows[i]["bank_name"].ToString().Trim(), dt.Rows[i]["sub_bank_name"].ToString().Trim(), dt.Rows[i]["lian_hang_number"].ToString().Trim(), 224 dt.Rows[i]["account_name"].ToString().Trim(), dt.Rows[i]["iden_sn"].ToString().Trim(), dt.Rows[i]["telephone"].ToString().Trim(), base.strUserDisplayName); 225 log_desc.Append("[商户号:" + dt.Rows[i]["merchant_id"].ToString().Trim() + "],[银行账号" + dt.Rows[i]["bank_account"].ToString().Trim()); 226 CMLoger.WriteDebugLog("[商户管理-结算信息维护]－银行账户导入中" + "[商户号:" + dt.Rows[i]["merchant_id"].ToString().Trim() + "],[银行账号" + dt.Rows[i]["bank_account"].ToString().Trim() + "]"); 227 } 228 catch (Exception ex) 229 { 230 231 } 232 } 233 // trandao.Commit(); 234 CMLoger.WriteDebugLog("[商户管理-结算信息维护]－银行账户导入【完成】,上传了" + dt.Rows.Count.ToString() + "条数据"); 235 Public.PublicClass.InsertLog(4, "[商户管理-结算信息维护]－银行账户导入：上传了" + dt.Rows.Count.ToString() + "条数据"); 236 hres.ErrorCode = "0000000000"; 237 hres.ErrorMessage = "操作成功"; 238 } 239 catch (Exception ex) 240 { 241 trandao.Rollback(); 242 hres.ErrorCode = "0000000001"; 243 hres.ErrorMessage = ex.Message; 244 CMLoger.WriteErrorLog(ex.Message + "[" + base.strUserAccount + "-" + base.strUserDisplayName + "]"); 245 246 } 247 #endregion 248 249 return hres; 250 251 252 } 253 public bool isBank_account(string bank_account) 254 { 255 string strRegex = @"^\d{1,30}$"; 256 Regex re = new Regex(strRegex); 257 if (re.IsMatch(bank_account)) 258 { 259 return true; 260 } 261 else 262 { 263 return false; 264 } 265 } 266 public bool isLian_hang_numberColumn(string lian_hang_numberColumn) 267 { 268 string strRegex = @"^\d{1,20}$"; 269 Regex re = new Regex(strRegex); 270 if (re.IsMatch(lian_hang_numberColumn)) 271 { 272 return true; 273 } 274 else 275 { 276 return false; 277 } 278 } 279 /// <summary> 280 /// 根据标题去匹配所属列 281 /// </summary> 282 /// <param name="title"></param> 283 /// <returns></returns> 284 private int GetColumnNum(DataTable dt, string title, IXLWorksheet worksheet) 285 { 286 int columNumber = 0; 287 for (int i = 1; i <= dt.Columns.Count; i++) 288 { 289 string workTitle = worksheet.Row(1).Cell(i).Value.ToString(); 290 //判断相不相等 291 if (title == workTitle) 292 { 293 columNumber = i; 294 } 295 } 296 return columNumber; 297 } 298 /// <summary> 299 /// 保证金周期 300 /// </summary> 301 /// <param name="marginCycle"></param> 302 /// <returns></returns> 303 public bool isMarginCycle(string marginCycle) 304 { 305 string strRegex = @"^[0-9]*[1-9][0-9]*$"; 306 Regex re = new Regex(strRegex); 307 if (re.IsMatch(marginCycle)) 308 { 309 return true; 310 } 311 else 312 { 313 return false; 314 } 315 } 316 317 private string showJsonError(string msg) 318 { 319 return "{" + string.Format("success:false,msg:'{0}'", msg) + "}"; 320 } 321 public override bool IsReusable 322 { 323 get 324 { 325 return false; 326 } 327 }
1644
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
