public override string UpdateWJFL(DateTime asOfDate, string sourceFilePath) { logger.DebugFormat("Updating WJFL for {0}", asOfDate.ToString("yyyy-MM-dd")); var result = string.Empty; if (!File.Exists(sourceFilePath)) { result = "风险贷款情况表的初表修订结果在这个路径下没找到:\r\n" + sourceFilePath; logger.Error(result); return result; } var dao = new SqlDbHelper(); var dateString = asOfDate.ToString("yyyyMMdd"); logger.DebugFormat("Getting existing import id for {0}", dateString); var import = Import.GetByDate(asOfDate); if (import == null || !import.Items.Exists(x => x.ItemType == XEnum.ImportItemType.LoanSF)) { result = string.Format("神府{0}的《贷款欠款查询》数据还没导入系统,请先导入这项数据。", asOfDate.ToString("yyyy年M月d日")); logger.Debug(result); return result; } var importFolder = System.Environment.CurrentDirectory + "\\Import\\" + import.Id.ToString(); var targetFileName = "WJFLSF.xls"; //Original var originalFolder = importFolder + @"\Original\"; if (!Directory.Exists(originalFolder)) { Directory.CreateDirectory(originalFolder); } File.Copy(sourceFilePath, originalFolder + @"\" + targetFileName, true); //Processed var processedFolder = importFolder + @"\Processed\"; if (!Directory.Exists(processedFolder)) { Directory.CreateDirectory(processedFolder); } File.Copy(sourceFilePath, processedFolder + @"\" + targetFileName, true); var targetFilePath = processedFolder + @"\" + targetFileName; File.Copy(sourceFilePath, targetFilePath, true); result = ExcelHelper.ProcessWJFLSF(targetFilePath); if (!string.IsNullOrEmpty(result)) { return result; } result = CreateImportItem(import.Id, sourceFilePath); if (!string.IsNullOrEmpty(result)) { logger.Error(result); return result; } result = ImportWjflSF(import.Id, importFolder); if (!string.IsNullOrEmpty(result)) { logger.Error(result); return result; } logger.Debug("Updating WJFL to LoanSF"); var sql = new StringBuilder(); //"SELECT Id, OrgId, CustomerName, CapitalAmount, LoanStartDate, LoanEndDate, DangerLevel FROM ImportWjflSF WHERE ImportId = {0} AND WjflType = {1}"; sql.AppendLine("UPDATE L SET DangerLevel = W.DangerLevel"); sql.AppendLine("FROM ImportLoanSF L"); sql.AppendLine(" INNER JOIN ImportWjflSF W ON L.ImportId = W.ImportId"); sql.AppendLine(" AND L.CustomerName = W.CustomerName"); //sql.AppendLine(" AND L.CapitalAmount = W.CapitalAmount"); sql.AppendLine(" AND L.LoanStartDate = W.LoanStartDate"); sql.AppendLine(" AND L.LoanEndDate = W.LoanEndDate"); sql.AppendLine("WHERE L.ImportId = {0} AND ISNULL(L.DangerLevel, '') != ISNULL(W.DangerLevel, '')"); sql.AppendLine(" AND W.WjflType = {1}"); logger.Debug("Updating from No Accrual sheet"); var count = dao.ExecuteNonQuery(string.Format(sql.ToString(), import.Id, (int)XEnum.WjflSheetSF.FYJ)); logger.DebugFormat("Done. {0} rows affected", count); logger.Debug("Updating from Overdue sheet"); count = dao.ExecuteNonQuery(string.Format(sql.ToString(), import.Id, (int)XEnum.WjflSheetSF.YQ)); logger.DebugFormat("Done. {0} rows affected", count); logger.Debug("Updating from ZQX sheet"); count = dao.ExecuteNonQuery(string.Format(sql.ToString(), import.Id, (int)XEnum.WjflSheetSF.ZQX)); logger.DebugFormat("Done. {0} rows affected", count); logger.Debug("Assigning LoanAccount to ImportWjflSF"); sql.Clear(); sql.AppendLine("UPDATE W SET LoanAccount = L.LoanAccount"); sql.AppendLine("FROM ImportWjflSF W"); sql.AppendLine(" INNER JOIN ImportLoanSF L ON W.ImportId = L.ImportId"); sql.AppendLine(" AND W.OrgId = L.OrgId"); sql.AppendLine(" AND W.CustomerName = L.CustomerName"); sql.AppendLine(" AND W.CapitalAmount = L.CapitalAmount"); sql.AppendLine(" AND W.LoanStartDate = L.LoanStartDate"); sql.AppendLine(" AND W.LoanEndDate = L.LoanEndDate"); sql.AppendLine("WHERE W.ImportId = {0} AND W.LoanAccount IS NULL"); count = dao.ExecuteNonQuery(string.Format(sql.ToString(), import.Id)); logger.DebugFormat("Done. {0} rows affected", count); return result; }
private void btnFix_Click(object sender, EventArgs e) { DateTime date; if (DateTime.TryParse(this.cmbDate.Text, out date)) { var dao = new SqlDbHelper(); var importId = (int) dao.ExecuteScalar(string.Format("SELECT ISNULL(MAX(Id), 0) FROM Import WHERE ImportDate = '{0}'", date.ToString("yyyyMMdd"))); if (importId == 0) { MessageBox.Show(string.Format("无效的导入日期。没有找到{0}日的数据", date), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); return; } var affected = dao.ExecuteNonQuery("UPDATE ImportPrivate SET CustomerName = '高峰' WHERE CustomerName = '高锋' AND ImportId = " + importId.ToString()); affected += dao.ExecuteNonQuery("UPDATE ImportLoan SET CustomerName = '高峰' WHERE CustomerName = '高锋' AND ImportId = " + importId.ToString()); affected += dao.ExecuteNonQuery("UPDATE ImportNonAccrual SET CustomerName = '高峰' WHERE CustomerName = '高锋' AND ImportId = " + importId.ToString()); affected += dao.ExecuteNonQuery("UPDATE ImportOverdue SET CustomerName = '高峰' WHERE CustomerName = '高锋' AND ImportId = " + importId.ToString()); if (affected > 0) { MessageBox.Show("修改完毕。", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("没有发现'高锋',可能已经修改过了。", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); } } }
private string AssignDangerLevel(int importId) { logger.Debug("Assigning Danger Level to Loan"); try { var dao = new SqlDbHelper(); var sql = new StringBuilder(); sql.AppendLine("UPDATE ImportLoan SET DangerLevel = dbo.sfGetDangerLevel({0}, LoanAccount)"); sql.AppendLine("WHERE ImportId = {0} AND DangerLevel IS NULL"); dao.ExecuteNonQuery(string.Format(sql.ToString(), importId)); } catch (Exception ex) { return(ex.Message);; } return(string.Empty); }
// Run this function after OrgId is assigned private string AssignLoanAccount(int importId) { try { logger.Debug("Assigning LoanAccount column to Private"); var sql = new StringBuilder(); sql.AppendLine("UPDATE P SET LoanAccount = L.LoanAccount"); sql.AppendLine("FROM ImportPrivate P"); sql.AppendLine(" INNER JOIN ImportLoan L ON P.ImportId = L.ImportId"); sql.AppendLine(" AND P.CustomerName = L.CustomerName"); sql.AppendLine(" AND ABS(P.LoanBalance*10000 - L.CapitalAmount) < 1.0"); sql.AppendLine(" AND P.ContractStartDate = L.LoanStartDate AND P.ContractEndDate = L.LoanEndDate"); sql.AppendLine("WHERE P.ImportId = {0} AND P.LoanAccount IS NULL"); var dao = new SqlDbHelper(); var count = dao.ExecuteNonQuery(string.Format(sql.ToString(), importId)); logger.DebugFormat("Done ({0} affected)", count); } catch (Exception ex) { return(ex.Message); } return(string.Empty); }
private string ImportWjflSF(int importId, string importFolder) { logger.Debug("Importing WjflSF data"); // Import to database string targetFilePath = importFolder + "\\Processed\\" + targetFileNames[(int)XEnum.ImportItemType.WjflSF]; var table = SourceTable.GetById(XEnum.ImportItemType.WjflSF); for (int sheetIndex = 1; sheetIndex <= table.Sheets.Count; sheetIndex++) { var excelColumns = "[行名], [客户名称], [贷款余额], [违约金额], [七级分类], [欠息金额], [放款日期], [到期日期], [逾期天数], [欠息天数], [担保方式], [行业], [客户类型], [贷款类型], [是否本月新增], [备注]"; var dbColumns = "OrgName, CustomerName, CapitalAmount, OweCapital, DangerLevel, OweInterestAmount, LoanStartDate, LoanEndDate, OverdueDays, OweInterestDays, DanBaoFangShi, Industry, CustomerType, LoanType, IsNew, [Comment]"; if (sheetIndex > 1) { excelColumns = excelColumns.Replace(", [违约金额]", ""); dbColumns = dbColumns.Replace(", OweCapital", ""); } var result = ImportTable(importId, targetFilePath, XEnum.ImportItemType.WjflSF, excelColumns, dbColumns, "WjflType", sheetIndex, sheetIndex); if (!String.IsNullOrEmpty(result)) { return result; } } logger.Debug("Importing WjflSF done"); logger.Debug("Assigning OrgId to WjflSF"); var dao = new SqlDbHelper(); var sql = string.Format("UPDATE ImportWjflSF SET OrgId = dbo.sfGetOrgId(OrgName) WHERE ImportId = {0}", importId); var count = dao.ExecuteNonQuery(sql); logger.DebugFormat("Done. {0} rows affected", count); return ""; }
private string ImportLoanSF(int importId, string importFolder, string sourceFilePath) { logger.Debug("Importing LoanSF data"); var done = CopyItem(importId, importFolder, sourceFilePath, XEnum.ImportItemType.LoanSF); if (!done) { logger.Debug("Source file not provided"); return ""; // Do nothing if user hasn't select a file for this table } // Import to database string targetFilePath = importFolder + "\\Processed\\" + targetFileNames[(int)XEnum.ImportItemType.LoanSF]; var excelColumns = "[机构号码], [贷款科目], [贷款帐号], [客户名称], [客户编号], [客户类型], [币种], [贷款总额], [本金余额], [拖欠本金], [拖欠应收利息], [拖欠催收利息], [借据编号], [放款日期], [到期日期], [置换/转让], [核销标志], [贷款状态], [贷款种类], [贷款种类说明], [贷款用途], [转列逾期], [转列非应计日期], [利息计至日], [利率种类], [利率加减符号], [利率加减码], [逾期利率依据方式], [逾期利率种类], [逾期利率加减符号], [逾期利率加减码], [利率依据方式], [合同最初计息利率], [合同最初逾期利率], [扣款账号]"; var dbColumns = "OrgNo, LoanCatalog, LoanAccount, CustomerName, CustomerNo, CustomerType, CurrencyType, LoanAmount, CapitalAmount, OweCapital, OweYingShouInterest, OweCuiShouInterest, DueBillNo, LoanStartDate, LoanEndDate, ZhiHuanZhuanRang, HeXiaoFlag, LoanState, LoanType, LoanTypeName, Direction, ZhuanLieYuQi, ZhuanLieFYJ, InterestEndDate, LiLvType, LiLvSymbol, LiLvJiaJianMa, YuQiLiLvYiJu, YuQiLiLvType, YuQiLiLvSymbol, YuQiLiLvJiaJianMa, LiLvYiJu, ContractInterestRatio, ContractOverdueInterestRate, ChargeAccount"; var result = ImportTable(importId, targetFilePath, XEnum.ImportItemType.LoanSF, excelColumns, dbColumns); if (!string.IsNullOrEmpty(result)) { return result; } logger.Debug("Assigning OrgId to LoanSF"); var dao = new SqlDbHelper(); var sql = string.Format("UPDATE ImportLoanSF SET OrgId = dbo.sfGetOrgId(OrgNo) WHERE ImportId = {0}", importId); var count = dao.ExecuteNonQuery(sql); logger.DebugFormat("Done. {0} rows affected", count); return ""; }
private string CreateImportItem(int importId, string sourceFilePath) { logger.Debug("Updating ImportItem table"); int itemTypeId = (int)XEnum.ImportItemType.WjflSF; var dao = new SqlDbHelper(); var sql = new StringBuilder(); sql.AppendFormat("SELECT ISNULL(MAX(Id), 0) FROM ImportItem WHERE ImportId = {0} AND ItemType = {1}", importId, itemTypeId); var importItemId = (int)dao.ExecuteScalar(sql.ToString()); if (importItemId == 0) { sql.Clear(); sql.AppendLine(string.Format("INSERT INTO ImportItem (ImportId, ItemType, FilePath) VALUES ({0}, {1}, '{2}')", importId, itemTypeId, sourceFilePath)); sql.AppendLine("SELECT SCOPE_IDENTITY()"); importItemId = (int)((decimal)dao.ExecuteScalar(sql.ToString())); logger.Debug("New record created. ImportItemId = " + importItemId.ToString()); } else { sql.Clear(); sql.AppendFormat("UPDATE ImportItem SET FilePath = '{0}', ModifyDate = getdate() WHERE Id = {1}", sourceFilePath, importItemId); dao.ExecuteNonQuery(sql.ToString()); logger.Debug("Existing record updated. ImportItemId = " + importItemId.ToString()); } return string.Empty; }
protected string ImportTable(int importId, string filePath, XEnum.ImportItemType itemType, string excelColumns, string dbColumns, string dbColumns2, object dbValues2, int sheetIndex = 1, int roundIndex = 1) { int columnCount = dbColumns.Split(',').Length; string suffix = GetTableSuffix(itemType); logger.DebugFormat("Importing {0} to database", suffix); if (String.IsNullOrWhiteSpace(filePath) || !File.Exists(filePath)) { return(string.Format("File {0} cannot be found", filePath ?? "<empty>")); } logger.Debug("Getting source table"); var sourceTable = SourceTable.GetById(itemType); var sheetEntry = sourceTable.Sheets[sheetIndex - 1]; var dataRowEnding = sheetEntry.DataRowEndingFlag; logger.DebugFormat("Ending is {0}", dataRowEnding == "" ? "empty string" : dataRowEnding); var oleOpened = false; OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0"); try { logger.Debug("Opening connection to " + filePath); oconn.Open(); oleOpened = true; logger.Debug("Opened"); DataTable dt = oconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheet1 = dt.Rows[(sheetIndex - 1) * 2][2].ToString(); if (!IsSheetMatched(sheet1, sheetEntry.Name)) { logger.WarnFormat("Sheet \"{0}\" is not found at index {1}. This may be caused by extra sheets added. Searching in all sheets...", sheetEntry.Name, sheetIndex); for (int i = 0; i < dt.Rows.Count; i++) { sheet1 = dt.Rows[i][2].ToString(); if (IsSheetMatched(sheet1, sheetEntry.Name)) { logger.WarnFormat("Got sheet \"{0}\"", sheet1.Substring(0, sheet1.Length - 1)); break; } } } if (!IsSheetMatched(sheet1, sheetEntry.Name)) { var msg = string.Format("没有找到工作表\"{0}\"", sheetEntry.Name); logger.Error(msg); return(msg); } logger.Debug("Importing sheet " + sheet1.Substring(0, sheet1.Length - 1)); var sql = new StringBuilder(); sql.AppendFormat("SELECT {0} FROM [{1}]", excelColumns, sheet1); sql.AppendLine(GetImportWhereSql(itemType)); var s = sql.ToString(); OleDbCommand ocmd = new OleDbCommand(s, oconn); OleDbDataReader reader = ocmd.ExecuteReader(); int dataRowIndex = 0; var dao = new SqlDbHelper(); if (sheetIndex == 1 && roundIndex == 1) // Delete existing records only when importing the first sheet { dao.ExecuteNonQuery(string.Format("DELETE FROM Import{0} WHERE ImportId = {1}", suffix, importId)); } sql.Clear(); while (reader.Read()) { if (DataUtility.GetValue(reader, 0).Equals(dataRowEnding)) // Going to end { break; } dataRowIndex++; sql.AppendLine(GetInsertSql(reader, importId, suffix, columnCount, dbColumns, dbColumns2, dbValues2)); // Top 1 trial for exception track if (dataRowIndex == 1) { try { dao.ExecuteNonQuery(sql.ToString()); sql.Clear(); } catch (Exception ex) { logger.Error("Running INSERT: " + sql.ToString(), ex); throw ex; } } // Batch inserts if (dataRowIndex > 1 && dataRowIndex % 1000 == 0) { dao.ExecuteNonQuery(sql.ToString()); sql.Clear(); } } if (sql.Length > 0) { try { dao.ExecuteNonQuery(sql.ToString()); sql.Clear(); } catch (Exception ex) { logger.Error("Running INSERT: " + sql.ToString(), ex); throw ex; } } logger.DebugFormat("{0} records imported.", dataRowIndex); if (itemType == XEnum.ImportItemType.Loan) { // Cleanup zero-records since the oledb query cannot filter them out var query = string.Format("DELETE FROM ImportLoan WHERE ImportId = {0} AND ISNULL(CapitalAmount + OweCapital + OweYingShouInterest + OweCuiShouInterest, 0) = 0", importId); var cleaned = dao.ExecuteNonQuery(query); logger.DebugFormat("{0} records have been cleaned because of 4-zeros.", cleaned); } } catch (Exception ex) { logger.Error("Outest catch: ", ex); throw ex; } finally { if (oleOpened) { oconn.Close(); } } return(string.Empty); }
protected bool CopyItem(int importId, string importFolder, string sourceFilePath, XEnum.ImportItemType itemType) { int itemTypeId = (int)itemType; if (sourceFilePath.Length == 0 || !File.Exists(sourceFilePath)) { return(false); } string targetFileName = this.targetFileNames[itemTypeId]; if (itemType == XEnum.ImportItemType.YWNei || itemType == XEnum.ImportItemType.YWWai || itemType == XEnum.ImportItemType.Loan) { var orgId = GetOrgId4YW(sourceFilePath); targetFileName = GetYWTargetFileName(itemType, orgId); } //Original var originalFolder = importFolder + @"\Original\"; if (!Directory.Exists(originalFolder)) { Directory.CreateDirectory(originalFolder); } File.Copy(sourceFilePath, originalFolder + @"\" + targetFileName, true); //Processed var processedFolder = importFolder + @"\Processed\"; if (!Directory.Exists(processedFolder)) { Directory.CreateDirectory(processedFolder); } File.Copy(sourceFilePath, processedFolder + @"\" + targetFileName, true); logger.Debug("Process copied item for " + itemType.ToString()); ExcelHelper.ProcessCopiedItem(processedFolder + @"\" + targetFileName, itemType); logger.Debug("Updating ImportItem table"); var dao = new SqlDbHelper(); var sql = new StringBuilder(); sql.AppendFormat("SELECT ISNULL(MAX(Id), 0) FROM ImportItem WHERE ImportId = {0} AND ItemType = {1}", importId, itemTypeId); var importItemId = (int)dao.ExecuteScalar(sql.ToString()); if (importItemId == 0) { sql.Clear(); sql.AppendLine(string.Format("INSERT INTO ImportItem (ImportId, ItemType, FilePath) VALUES ({0}, {1}, '{2}')", importId, itemTypeId, sourceFilePath)); sql.AppendLine("SELECT SCOPE_IDENTITY()"); importItemId = (int)((decimal)dao.ExecuteScalar(sql.ToString())); logger.Debug("New record created. ImportItemId = " + importItemId.ToString()); } else { sql.Clear(); sql.AppendFormat("UPDATE ImportItem SET FilePath = '{0}', ModifyDate = getdate() WHERE Id = {1}", sourceFilePath, importItemId); dao.ExecuteNonQuery(sql.ToString()); logger.Debug("Existing record updated. ImportItemId = " + importItemId.ToString()); } return(true); }
private string UpdateWJFLSheet(int importId, OleDbDataReader reader) { var result = string.Empty; try { int readRows = 0; int updatedRows = 0; int failedRows = 0; var sql = new StringBuilder(); var sqlSingle = ""; var firstColumn = ""; var dao = new SqlDbHelper(); var failedCustomers = new StringBuilder(); while (reader.Read()) { firstColumn = DataUtility.GetValue(reader, 0); if (string.IsNullOrEmpty(firstColumn) || firstColumn.Replace(" ", "").Equals("合计")) // Going to end { break; } readRows++; sql.Clear(); //替换掉下面两行,解决五级分类中营业部与公司部混乱的问题 //sql.AppendLine("SELECT Id FROM ImportLoan"); //sql.AppendLine("WHERE OrgId = dbo.sfGetOrgId('{0}')"); sql.AppendLine("SELECT L.Id FROM ImportLoan L INNER JOIN Org O ON L.OrgId = O.Id"); sql.AppendLine("WHERE O.OrgNo = (SELECT OrgNo FROM Org WHERE Id = dbo.sfGetOrgId('{0}'))"); sql.AppendLine(" AND CustomerName = '{1}'"); sql.AppendLine(" AND CapitalAmount = {2}"); if (!string.IsNullOrEmpty(DataUtility.GetValue(reader, 3))) { sql.AppendLine(" AND LoanStartDate = '{3}'"); } if (!string.IsNullOrEmpty(DataUtility.GetValue(reader, 4))) { sql.AppendLine(" AND LoanEndDate = '{4}'"); } sql.AppendLine(" AND ImportId = '{5}'"); sqlSingle = string.Format(sql.ToString(), DataUtility.GetValue(reader, 0), DataUtility.GetValue(reader, 1), DataUtility.GetValue(reader, 2), DataUtility.GetValue(reader, 3), DataUtility.GetValue(reader, 4), importId); var o = dao.ExecuteScalar(sqlSingle); if (o == null) { failedRows++; if (failedRows <= 10) { var msg = GetMismatchMessage(importId, DataUtility.GetValue(reader, 0), DataUtility.GetValue(reader, 1), DataUtility.GetValue(reader, 2), DataUtility.GetValue(reader, 3), DataUtility.GetValue(reader, 4)); failedCustomers.AppendLine(msg + "\r\n" + new string('-', 60)); logger.WarnFormat("No record matched for {0}-{1}-{2}-{3}-{4}", DataUtility.GetValue(reader, 0), DataUtility.GetValue(reader, 1), DataUtility.GetValue(reader, 2), DataUtility.GetValue(reader, 3), DataUtility.GetValue(reader, 4)); } else { failedCustomers.AppendFormat("还有更多……\r\n", DataUtility.GetValue(reader, 1), DataUtility.GetValue(reader, 2), DataUtility.GetValue(reader, 3), DataUtility.GetValue(reader, 4)); logger.Warn("Stopped because of more un-matched records."); break; } } else { int loanId = (int)o; sqlSingle = string.Format("UPDATE ImportLoan SET DangerLevel = '{0}' WHERE Id = {1} AND ISNULL(DangerLevel, '') != '{0}'", DataUtility.GetValue(reader, 5), loanId); try { var affected = dao.ExecuteNonQuery(sqlSingle); updatedRows += affected; if (affected > 0) { logger.DebugFormat("#{0} update to '{1}'", loanId, DataUtility.GetValue(reader, 5)); } } catch (Exception ex) { logger.Error("Running: " + sql.ToString(), ex); throw ex; } } } logger.DebugFormat("Rows read in toal: {0}", readRows); logger.DebugFormat("Rows updated: {0}", updatedRows); logger.DebugFormat("Rows not match: {0}", failedRows); if (failedRows == 1) { result = Consts.MESSAGE_FORM_PREFIX + "下面客户的五级分类无法导入:\r\n" + failedCustomers.ToString() + "\r\n请确保新修改的五级分类Excel文件中,该客户的贷款余额、放款日期和到期日期格式正确。"; } else if (failedRows > 1) { result = Consts.MESSAGE_FORM_PREFIX + "下列客户的五级分类无法导入:\r\n" + (new string('-', 80)) + "\r\n" + failedCustomers.ToString() + "\r\n" + (new string('-', 80)) + "\r\n请确保新修改的五级分类Excel文件中,他们的贷款余额、放款日期和到期日期格式正确。"; } } catch (Exception ex) { logger.Error("Outest catch", ex); return(ex.Message); } return(result); }
public virtual string UpdateWJFL(DateTime asOfDate, string sourceFilePath) { logger.DebugFormat("Updating WJFL for {0}", asOfDate.ToString("yyyy-MM-dd")); var result = string.Empty; if (!File.Exists(sourceFilePath)) { result = "风险贷款情况表的初表修订结果在这个路径下没找到:\r\n" + sourceFilePath; logger.Debug(result); return(result); } var dao = new SqlDbHelper(); var dateString = asOfDate.ToString("yyyyMMdd"); logger.DebugFormat("Getting existing import id for {0}", dateString); var import = Import.GetByDate(asOfDate); if (import == null || !import.Items.Exists(x => x.ItemType == XEnum.ImportItemType.Loan)) { result = string.Format("{0}的《贷款欠款查询》数据还没导入系统,请先导入这项数据。", asOfDate.ToString("yyyy年M月d日")); logger.Debug(result); return(result); } var importFolder = System.Environment.CurrentDirectory + "\\Import\\" + import.Id.ToString(); var targetFileName = "WJFL.xls"; //Original var originalFolder = importFolder + @"\Original\"; if (!Directory.Exists(originalFolder)) { Directory.CreateDirectory(originalFolder); } File.Copy(sourceFilePath, originalFolder + @"\" + targetFileName, true); //Processed var processedFolder = importFolder + @"\Processed\"; if (!Directory.Exists(processedFolder)) { Directory.CreateDirectory(processedFolder); } File.Copy(sourceFilePath, processedFolder + @"\" + targetFileName, true); var targetFilePath = processedFolder + @"\" + targetFileName; result = ExcelHelper.ProcessWJFL(targetFilePath); if (!string.IsNullOrEmpty(result)) { return(result); } logger.Debug("Updating in database"); var oleOpened = false; OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + targetFilePath + ";Extended Properties=Excel 8.0"); try { logger.Debug("Opening connection to " + targetFilePath); oconn.Open(); oleOpened = true; logger.Debug("Opened"); logger.Debug("Reading from No Accrual sheet"); OleDbCommand ocmd = new OleDbCommand("SELECT [行名], [客户名称], [贷款余额], [放款日期], [到期日期], [七级分类] FROM [非应计$]", oconn); OleDbDataReader reader = ocmd.ExecuteReader(); logger.Debug("Executed"); result = UpdateWJFLSheet(import.Id, reader); if (!string.IsNullOrEmpty(result)) { return(result); } logger.Debug("Reading from Overdue sheet"); ocmd = new OleDbCommand("SELECT [行名], [客户名称], [贷款余额], [放款日期], [到期日期], [七级分类] FROM [逾期$]", oconn); reader = ocmd.ExecuteReader(); logger.Debug("Executed"); result = UpdateWJFLSheet(import.Id, reader); if (!string.IsNullOrEmpty(result)) { return(result); } logger.Debug("Reading from ZQX sheet"); ocmd = new OleDbCommand("SELECT [行名], [客户名称], [贷款余额], [放款日期], [到期日期], [七级分类] FROM [只欠息$]", oconn); reader = ocmd.ExecuteReader(); logger.Debug("Executed"); result = UpdateWJFLSheet(import.Id, reader); if (!string.IsNullOrEmpty(result)) { return(result); } logger.Debug("Updating WJFLDate field for import #" + import.Id.ToString()); dao.ExecuteNonQuery("UPDATE Import SET WJFLDate = GETDATE() WHERE Id = " + import.Id.ToString()); logger.Debug("Updated"); } catch (Exception ex) { logger.Error("Outest catch", ex); return(ex.Message); } finally { if (oleOpened) { oconn.Close(); } } return(result); }
public override string UpdateWJFL(DateTime asOfDate, string sourceFilePath) { logger.DebugFormat("Updating WJFL for {0}", asOfDate.ToString("yyyy-MM-dd")); var result = string.Empty; if (!File.Exists(sourceFilePath)) { result = "风险贷款情况表的初表修订结果在这个路径下没找到:\r\n" + sourceFilePath; logger.Error(result); return(result); } var dao = new SqlDbHelper(); var dateString = asOfDate.ToString("yyyyMMdd"); logger.DebugFormat("Getting existing import id for {0}", dateString); var import = Import.GetByDate(asOfDate); if (import == null || !import.Items.Exists(x => x.ItemType == XEnum.ImportItemType.LoanSF)) { result = string.Format("神府{0}的《贷款欠款查询》数据还没导入系统,请先导入这项数据。", asOfDate.ToString("yyyy年M月d日")); logger.Debug(result); return(result); } var importFolder = System.Environment.CurrentDirectory + "\\Import\\" + import.Id.ToString(); var targetFileName = "WJFLSF.xls"; //Original var originalFolder = importFolder + @"\Original\"; if (!Directory.Exists(originalFolder)) { Directory.CreateDirectory(originalFolder); } File.Copy(sourceFilePath, originalFolder + @"\" + targetFileName, true); //Processed var processedFolder = importFolder + @"\Processed\"; if (!Directory.Exists(processedFolder)) { Directory.CreateDirectory(processedFolder); } File.Copy(sourceFilePath, processedFolder + @"\" + targetFileName, true); var targetFilePath = processedFolder + @"\" + targetFileName; File.Copy(sourceFilePath, targetFilePath, true); result = ExcelHelper.ProcessWJFLSF(targetFilePath); if (!string.IsNullOrEmpty(result)) { return(result); } result = CreateImportItem(import.Id, sourceFilePath); if (!string.IsNullOrEmpty(result)) { logger.Error(result); return(result); } result = ImportWjflSF(import.Id, importFolder); if (!string.IsNullOrEmpty(result)) { logger.Error(result); return(result); } logger.Debug("Updating WJFL to LoanSF"); var sql = new StringBuilder(); //"SELECT Id, OrgId, CustomerName, CapitalAmount, LoanStartDate, LoanEndDate, DangerLevel FROM ImportWjflSF WHERE ImportId = {0} AND WjflType = {1}"; sql.AppendLine("UPDATE L SET DangerLevel = W.DangerLevel"); sql.AppendLine("FROM ImportLoanSF L"); sql.AppendLine(" INNER JOIN ImportWjflSF W ON L.ImportId = W.ImportId"); sql.AppendLine(" AND L.CustomerName = W.CustomerName"); //sql.AppendLine(" AND L.CapitalAmount = W.CapitalAmount"); sql.AppendLine(" AND L.LoanStartDate = W.LoanStartDate"); sql.AppendLine(" AND L.LoanEndDate = W.LoanEndDate"); sql.AppendLine("WHERE L.ImportId = {0} AND ISNULL(L.DangerLevel, '') != ISNULL(W.DangerLevel, '')"); sql.AppendLine(" AND W.WjflType = {1}"); logger.Debug("Updating from No Accrual sheet"); var count = dao.ExecuteNonQuery(string.Format(sql.ToString(), import.Id, (int)XEnum.WjflSheetSF.FYJ)); logger.DebugFormat("Done. {0} rows affected", count); logger.Debug("Updating from Overdue sheet"); count = dao.ExecuteNonQuery(string.Format(sql.ToString(), import.Id, (int)XEnum.WjflSheetSF.YQ)); logger.DebugFormat("Done. {0} rows affected", count); logger.Debug("Updating from ZQX sheet"); count = dao.ExecuteNonQuery(string.Format(sql.ToString(), import.Id, (int)XEnum.WjflSheetSF.ZQX)); logger.DebugFormat("Done. {0} rows affected", count); logger.Debug("Updating from BL sheet"); count = dao.ExecuteNonQuery(string.Format(sql.ToString(), import.Id, (int)XEnum.WjflSheetSF.BL)); logger.DebugFormat("Done. {0} rows affected", count); logger.Debug("Updating from GZ sheet"); count = dao.ExecuteNonQuery(string.Format(sql.ToString(), import.Id, (int)XEnum.WjflSheetSF.GZ)); logger.DebugFormat("Done. {0} rows affected", count); logger.Debug("Assigning LoanAccount to ImportWjflSF"); sql.Clear(); sql.AppendLine("UPDATE W SET LoanAccount = L.LoanAccount"); sql.AppendLine("FROM ImportWjflSF W"); sql.AppendLine(" INNER JOIN ImportLoanSF L ON W.ImportId = L.ImportId"); sql.AppendLine(" AND W.OrgId = L.OrgId"); sql.AppendLine(" AND W.CustomerName = L.CustomerName"); sql.AppendLine(" AND W.CapitalAmount = L.CapitalAmount"); sql.AppendLine(" AND W.LoanStartDate = L.LoanStartDate"); sql.AppendLine(" AND W.LoanEndDate = L.LoanEndDate"); sql.AppendLine("WHERE W.ImportId = {0} AND W.LoanAccount IS NULL"); count = dao.ExecuteNonQuery(string.Format(sql.ToString(), import.Id)); logger.DebugFormat("Done. {0} rows affected", count); return(result); }