/// <summary> /// 匯入會計入帳時間 /// </summary> /// <returns></returns> public HttpResponseBase OrderMasterImport() { string json = string.Empty;//json字符串 //string shipment = Request.Params["shipment"].ToString(); //int total = 0; try { if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空 { HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流 FileManagement fileManagement = new FileManagement();//實例化 FileManagement string fileLastName = excelFile.FileName; string newExcelName = Server.MapPath(excelPath) + "會計賬款實收時間" + fileManagement.NewFileName(excelFile.FileName);//處理文件名,獲取新的文件名 excelFile.SaveAs(newExcelName);//上傳文件 DataTable dt = new DataTable(); NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName); dt = helper.SheetData(); List<OrderAccountCollection> oacli = new List<OrderAccountCollection>(); string errorStr = string.Empty; Int64[] orderArr = new Int64[dt.Rows.Count]; for (int j = 0; j < dt.Rows.Count; j++) { OrderAccountCollection model = new OrderAccountCollection(); uint order_id = 0; if (!string.IsNullOrEmpty(dt.Rows[j][0].ToString())) { if (uint.TryParse(dt.Rows[j][0].ToString(), out order_id)) { model.order_id = order_id; } else { errorStr += (j + 2) + ","; continue; } } else { errorStr += (j + 2) + ","; continue; } if (!string.IsNullOrEmpty(dt.Rows[j][1].ToString())) { DateTime st; if (DateTime.TryParse(dt.Rows[j][1].ToString(), out st)) { model.account_collection_time = st; } else { string strtime = Regex.Replace(dt.Rows[j][1].ToString().Trim(), "/(\\s+)|(,)|(-)|(,)|(.)/g", "/"); if (DateTime.TryParse(strtime, out st)) { model.account_collection_time = st; } else { string[] str = dt.Rows[j][1].ToString().Split('/'); int year = 0; if (str.Length == 3) { if (str[2].Length == 2) { year = Convert.ToInt32("20" + str[2]); } else { year = Convert.ToInt32(str[2]); } int month = Convert.ToInt32(str[0]); int day = Convert.ToInt32(str[1]); if (DateTime.TryParse(year + "/" + month + "/" + day, out st)) { model.account_collection_time = st; } else { errorStr += (j + 2) + ","; continue; } } else { errorStr += (j + 2) + ","; continue; } } } if (!string.IsNullOrEmpty(dt.Rows[j][2].ToString())) { int account_collection_money = 0; if (int.TryParse(dt.Rows[j][2].ToString(), out account_collection_money)) { model.account_collection_money = account_collection_money; } else { errorStr += (j + 2) + ","; continue; } } else { errorStr += (j + 2) + ","; continue; } if (!string.IsNullOrEmpty(dt.Rows[j][3].ToString())) { int poundage = 0; if (int.TryParse(dt.Rows[j][3].ToString(), out poundage)) { model.poundage = poundage; } else { errorStr += (j + 2) + ","; continue; } } else { errorStr += (j + 2) + ","; continue; } } if (!string.IsNullOrEmpty(dt.Rows[j][4].ToString())) { DateTime streturn; if (DateTime.TryParse(dt.Rows[j][4].ToString(), out streturn)) { model.return_collection_time = streturn; } else { string strtimeR = Regex.Replace(dt.Rows[j][4].ToString().Trim(), "/(\\s+)|(,)|(-)|(,)|(.)/g", "/"); if (DateTime.TryParse(strtimeR.ToString(), out streturn)) { model.return_collection_time = streturn; } else { string[] strR = dt.Rows[j][4].ToString().Split('/'); int yearR = 0; if (strR.Length == 3) { if (strR[2].Length == 2) { yearR = Convert.ToInt32("20" + strR[2]); } else { yearR = Convert.ToInt32(strR[2]); } int monthR = Convert.ToInt32(strR[0]); int dayR = Convert.ToInt32(strR[1]); if (DateTime.TryParse(yearR + "/" + monthR + "/" + dayR, out streturn)) { model.return_collection_time = streturn; } else { errorStr += (j + 2) + ","; continue; } } else { errorStr += (j + 2) + ","; continue; } } } if (!string.IsNullOrEmpty(dt.Rows[j][5].ToString())) { int return_collection_money = 0; if (int.TryParse(dt.Rows[j][5].ToString(), out return_collection_money)) { model.return_collection_money = return_collection_money; } else { errorStr += (j + 2) + ","; continue; } } else { errorStr += (j + 2) + ","; continue; } if (!string.IsNullOrEmpty(dt.Rows[j][6].ToString())) { int return_poundage = 0; if (int.TryParse(dt.Rows[j][6].ToString(), out return_poundage)) { model.return_poundage = return_poundage; } else { errorStr += (j + 2) + ","; continue; } } else { errorStr += (j + 2) + ","; continue; } } if (!string.IsNullOrEmpty(dt.Rows[j][7].ToString())) { DateTime st; if (DateTime.TryParse(dt.Rows[j][7].ToString(), out st)) { model.invoice_date_manual = st; } else { string strtime = Regex.Replace(dt.Rows[j][1].ToString().Trim(), "/(\\s+)|(,)|(-)|(,)|(.)/g", "/"); if (DateTime.TryParse(strtime, out st)) { model.invoice_date_manual = st; } else { string[] str = dt.Rows[j][7].ToString().Split('/'); int year = 0; if (str.Length == 3) { if (str[2].Length == 2) { year = Convert.ToInt32("20" + str[2]); } else { year = Convert.ToInt32(str[2]); } int month = Convert.ToInt32(str[0]); int day = Convert.ToInt32(str[1]); if (DateTime.TryParse(year + "/" + month + "/" + day, out st)) { model.invoice_date_manual = st; } else { errorStr += (j + 2) + ","; continue; } } else { errorStr += (j + 2) + ","; continue; } } } if (!string.IsNullOrEmpty(dt.Rows[j][8].ToString())) { int invoice_sale_manual = 0; if (int.TryParse(dt.Rows[j][8].ToString(), out invoice_sale_manual)) { model.invoice_sale_manual = invoice_sale_manual; } else { errorStr += (j + 2) + ","; continue; } } else { errorStr += (j + 2) + ","; continue; } if (!string.IsNullOrEmpty(dt.Rows[j][9].ToString())) { int invoice_tax_manual = 0; if (int.TryParse(dt.Rows[j][9].ToString(), out invoice_tax_manual)) { model.invoice_tax_manual = invoice_tax_manual; } else { errorStr += (j + 2) + ","; continue; } } else { errorStr += (j + 2) + ","; continue; } } model.remark = dt.Rows[j][10].ToString(); if (model != null && !(model.account_collection_time == model.return_collection_time && model.account_collection_time == model.invoice_date_manual && model.return_collection_time == DateTime.MinValue)) { if (!orderArr.Contains(model.order_id)) { orderArr[j] = order_id; oacli.Add(model); } else { errorStr += (j + 2) + ","; } } } int rowsnum = oacli.Count; if (!string.IsNullOrEmpty(errorStr)) { errorStr = errorStr.Remove(errorStr.Length - 1); } if (rowsnum > 0)//判斷是否是這個表 { _OrderMasterMgr = new OrderMasterMgr(connectionString); int i = _OrderMasterMgr.OrderMasterImport(oacli); if (i > 0) { if (i == 99999) { if (!string.IsNullOrEmpty(errorStr)) { json = "{success:true,msg:\"" + "無數據可匯入!另文件第" + errorStr + "行數據異常\"}"; } else { json = "{success:true,msg:\"" + "無數據可匯入!\"}"; } } else { if (!string.IsNullOrEmpty(errorStr)) { json = "{success:true,msg:\"" + "匯入成功!另文件第" + errorStr + "行數據異常\"}"; } else { json = "{success:true,msg:\"" + "匯入成功!\"}"; } } } else { if (!string.IsNullOrEmpty(errorStr)) { json = "{success:true,msg:\"" + "操作失敗!另文件第" + errorStr + "行數據異常\"}"; } else { json = "{success:true,msg:\"" + "操作失敗!\"}"; } } } else { json = "{success:true,total:0,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}"; this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; } } else//當直接點擊時會產生, { json = "{success:true,msg:\"" + "操作失敗!" + "\"}"; this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; } } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:true,msg:\"" + "操作失敗!" + "\"}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }