public override string GenerateReport() { var import = Import.GetByDate(this.AsOfDate); if (import == null) { var msg = string.Format("{0}的数据还没导入系统", this.AsOfDate.ToString("yyyy年M月d日")); Logger.Debug(msg); return(msg); } var fileName = string.Format("榆林分行{0}月末风险贷款情况表 - {1}.xls", this.AsOfDate.Month, import.WJFLDate == null ? "初" : "终"); Logger.Debug("Generating " + fileName); var report = TargetTable.GetById(XEnum.ReportType.X_WJFL_M); var filePath = CreateReportFile(report.TemplateName, fileName); foreach (var sheet in report.Sheets) { if (sheet.Name.Equals("累收累增")) { PopulateSheetVS(filePath, sheet); } else { PopulateSheet(filePath, sheet); } } ExcelHelper.ActivateSheet(filePath); return(string.Empty); }
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); }