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