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);
}
