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 }
浙公网安备 33010602011771号