Exemple #1
0
        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);
        }