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 PopulateSheet(string filePath, TargetTableSheet sheet)
{
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
ExcelHelper.InitSheet(filePath, sheet);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
Logger.Debug("Openning connction to " + filePath);
oleConn.Open();
var sql = string.Format("EXEC spX_FXDKTB_D '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var reader = dao.ExecuteReader(sql);
int rowCount = 0;
while (reader.Read()) {
rowCount++;
sql = GetInsertSql(reader, sheet);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
}
private string PopulateSheet3(string filePath, TargetTableSheet sheet)
{
var result = "";
var dao = new SqlDbHelper();
var sql = string.Format("EXEC spX_BLDKJC_X_3 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
Logger.Debug("Running " + sql);
var table = dao.ExecuteDataTable(sql);
result = ExcelHelper.PopulateX_BLDKJC_X_3(filePath, sheet, this.AsOfDate, table);
return result;
}
public override string GenerateReport()
{
var fileName = string.Format("{0}贷款质量分类情况汇总表.xls", this.AsOfDate.ToString("yyyy年M月"));
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.X_DKZLFL_M);
var filePath = CreateReportFile(report.TemplateName, fileName);
var sql = string.Format("EXEC spX_DKZLFL_M '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var table = dao.ExecuteDataTable(sql);
var result = ExcelHelper.PopulateX_DKZLFL_M(filePath, report.Sheets[0], this.AsOfDate, table);
return result;
}
private void frmFixGaoFeng_Load(object sender, EventArgs e)
{
var dao = new SqlDbHelper();
var table = dao.ExecuteDataTable("SELECT ImportDate FROM Import ORDER BY ImportDate DESC");
this.cmbDate.Items.Clear();
if (table != null) {
foreach (DataRow row in table.Rows) {
var value = ((DateTime)row[0]).ToString("yyyy-MM-dd");
this.cmbDate.Items.Add(value);
}
if (this.cmbDate.Items.Count > 0) {
if (this.cmbDate.SelectedIndex < 0) {
this.cmbDate.SelectedIndex = 0;
}
}
}
}
private void LoadData()
{
try {
var dao = new SqlDbHelper();
var sql = new StringBuilder();
sql.AppendLine("SELECT ImportDate AS [数据日期]");
sql.AppendLine(" , Id AS [编号]");
sql.AppendLine(" , DateStamp AS [创建时间]");
sql.AppendLine(" , WJFLDate AS [五级分类]");
sql.AppendLine(" , SUBSTRING(dbo.sfGetImportStatus(ImportDate), 1, 9) AS [导入状况]");
sql.AppendLine("FROM Import");
sql.AppendLine("ORDER BY ImportDate DESC");
var table = dao.ExecuteDataTable(sql.ToString());
this.dataGridView1.DataSource = table;
this.dataGridView1.Columns[0].DefaultCellStyle.Format = "yyyy-MM-dd";
this.dataGridView1.Columns[1].Width = 60;
this.dataGridView1.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
this.dataGridView1.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
this.dataGridView1.Columns[2].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss";
this.dataGridView1.Columns[2].Width = 140;
this.dataGridView1.Columns[3].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss";
this.dataGridView1.Columns[3].Width = 140;
//this.dataGridView1.Columns[3].SortMode = DataGridViewColumnSortMode.NotSortable;
this.dataGridView1.Columns[4].Width = 100;
//this.dataGridView1.Columns[4].SortMode = DataGridViewColumnSortMode.NotSortable;
//Status bar
this.toolStripStatusTime.Text = string.Format("更新时间：{0}", DateTime.Now.ToString("HH:mm:ss"));
this.toolStripStatusTotal.Text = string.Format("共{0}条记录", table.Rows.Count);
this.toolStripStatusSelected.Text = string.Format("选中{0}条记录", this.dataGridView1.SelectedRows.Count);
}
catch (System.Data.SqlClient.SqlException ex) {
logger.Error("Error in LoadData:\r\n", ex);
ShowError("数据库访问发生错误，请确保数据库可以访问。");
}
catch (Exception ex) {
logger.Error("Error in ShowReport:\r\n", ex);
ShowError(ex.Message);
}
}
public override string GenerateReport()
{
var fileName = string.Format("五级分类预测{0}.xls", this.AsOfDate.ToString("M月d日"));
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.X_WJFLPRD_D);
var filePath = CreateReportFile(report.TemplateName, fileName);
var sheet = report.Sheets[0];
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
ExcelHelper.InitSheet(filePath, sheet);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
Logger.Debug("Openning connction to " + filePath);
oleConn.Open();
var sql = string.Format("EXEC spX_WJFLPRD_D '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var reader = dao.ExecuteReader(sql);
int rowCount = 0;
while (reader.Read()) {
rowCount++;
sql = GetInsertSql(reader, sheet);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
return string.Empty;
}
public override string GenerateReport()
{
var fileName = "GF0102-081-境内汇总数据-月-人民币.xls";
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.F_GF0102_081_M);
var filePath = CreateReportFile(report.TemplateName, fileName);
var sql = string.Format("EXEC spGF0102_081 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var result = "";
var reader = dao.ExecuteReader(sql);
if (reader.Read()) {
result = ExcelHelper.PopulateGF0102_081(filePath, report.Sheets[0], this.AsOfDate, (decimal)reader[0], (decimal)reader[1], (decimal)reader[2], (decimal)reader[3], (decimal)reader[4]);
}
else {
result = "Procedure returned zero rows";
}
return result;
}
public override string GenerateReport()
{
var fileName = "SF6302-131-境内汇总数据-季-人民币.xls";
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.F_SF6302_131_S);
var filePath = CreateReportFile(report.TemplateName, fileName);
var sql = string.Format("EXEC spSF6302_131 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var result = "";
var table = dao.ExecuteDataTable(sql);
if (table != null) {
result = ExcelHelper.PopulateSF6302_131(filePath, report.Sheets[0], this.AsOfDate, table);
}
else {
result = "Procedure returned zero rows";
}
return result;
}
private void PopulateSheet(string filePath, TargetTableSheet sheet)
{
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
var sql = string.Format("EXEC spC_DQDKQK_M '{0}', '{1}', '{2}'", this.AsOfDate.ToString("yyyyMMdd")
, sheet.Index == 1 ? "ImportPublic" : "ImportPrivate"
, sheet.Index == 1 ? PublicColumns : PrivateColumns);
var dao = new SqlDbHelper();
Logger.Debug("Running: " + sql);
var reader = dao.ExecuteReader(sql);
var columnNames = new List<string>();
for (int i = 0; i < reader.FieldCount; i++) {
columnNames.Add(reader.GetName(i));
}
int rowCount = 0;
ExcelHelper.InitSheet(filePath, sheet, columnNames);
Logger.Debug("Openning connction to " + filePath);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
oleConn.Open();
while (reader.Read()) {
rowCount++;
sql = GetInsertSql(reader, sheet, columnNames);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
}
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);
}
}
}
public override string GenerateReport()
{
var fileName = string.Format("各支行风险贷款变化情况表{0}.xls", this.AsOfDate.ToString("M月d日"));
Logger.Debug("Generating " + fileName);
// Check YWNei import
var dao = new SqlDbHelper();
var sql = string.Format("SELECT COUNT(*) FROM ImportYWNei WHERE ImportId = (SELECT Id FROM Import I WHERE I.ImportDate = '{0}') AND OrgId < 100", this.AsOfDate.ToString("yyyyMMdd"));
var count = (int)dao.ExecuteScalar(sql);
if (count == 0) {
Logger.Error("支行业务状况表还没导入");
return string.Format("导入各支行{0}的业务状况表（表内）之后才能导出此报表。", this.AsOfDate.ToString("yyyy-M-d"));
}
var report = TargetTable.GetById(XEnum.ReportType.X_FXDKTB_D);
var filePath = CreateReportFile(report.TemplateName, fileName);
foreach (var sheet in report.Sheets) {
PopulateSheet(filePath, sheet);
}
return string.Empty;
}
private void InitReportPanel()
{
var report = this.Reports.Single(x => x.Id == (int)currentReport);
if (report == null)
{
ShowStop("Failed to get entity of current report.");
return;
}
this.lblReportTitle.Text = report.Name;
this.SelectedColumns.Clear();
this.SelectedColumns1.Clear();
this.SelectedColumns2.Clear();
ShowAsOfDate2();
ShowExportDate();
ShowSelectColumnButton();
var dao = new SqlDbHelper();
var lastSelectValue = "";
var lastSelectValue2 = "";
if (IsMonthly())
{
this.lblExportDate.Text = "数据月份：";
if (this.cmbReportMonth.SelectedIndex >= 0)
{
lastSelectValue = this.cmbReportMonth.Text;
}
this.cmbReportMonth.Items.Clear();
DataTable table;
if (currentReport == XEnum.ReportType.C_DQDKQK_M)
{
table = dao.ExecuteDataTable("SELECT * FROM dbo.sfGetMonthsInFuture()");
}
else
{
table = dao.ExecuteDataTable("SELECT ImportDate, dbo.sfGetImportStatus(ImportDate) AS status FROM Import WHERE DAY(ImportDate + 1) = 1 ORDER BY ImportDate DESC");
}
if (table != null)
{
foreach (DataRow row in table.Rows)
{
var value = ((DateTime)row[0]).ToString("yyyy-MM");
if (row.ItemArray.Length > 1 && !((string)row[1]).StartsWith("1111111"))
{
value += " *";
}
this.cmbReportMonth.Items.Add(value);
if (!string.IsNullOrEmpty(lastSelectValue) && value.Equals(lastSelectValue))
{
this.cmbReportMonth.Text = value;
}
}
// Select the latest one by default
if (this.cmbReportMonth.Items.Count > 0)
{
if (this.cmbReportMonth.SelectedIndex < 0)
{
this.cmbReportMonth.SelectedIndex = 0;
}
}
}
}
else
{
this.lblExportDate.Text = "数据日期：";
var table = dao.ExecuteDataTable("SELECT ImportDate FROM Import ORDER BY ImportDate DESC");
if (this.cmbReportMonth.SelectedIndex >= 0)
{
lastSelectValue = this.cmbReportMonth.Text;
}
if (this.cmbReportMonth2.SelectedIndex >= 0)
{
lastSelectValue2 = this.cmbReportMonth2.Text;
}
this.cmbReportMonth.Items.Clear();
this.cmbReportMonth2.Items.Clear();
if (table != null)
{
foreach (DataRow row in table.Rows)
{
var value = ((DateTime)row[0]).ToString("yyyy-MM-dd");
this.cmbReportMonth.Items.Add(value);
this.cmbReportMonth2.Items.Add(value);
if (!string.IsNullOrEmpty(lastSelectValue) && value.Equals(lastSelectValue))
{
this.cmbReportMonth.Text = value;
}
if (!string.IsNullOrEmpty(lastSelectValue2) && value.Equals(lastSelectValue2))
{
this.cmbReportMonth2.Text = value;
}
}
// Select the latest one by default
if (this.cmbReportMonth.Items.Count > 0)
{
if (this.cmbReportMonth.SelectedIndex < 0)
{
this.cmbReportMonth.SelectedIndex = 0;
}
}
if (this.cmbReportMonth2.Items.Count > 0)
{
if (this.cmbReportMonth2.SelectedIndex < 0)
{
this.cmbReportMonth2.SelectedIndex = 0;
}
}
}
}
this.txtReportPath.Text = BaseReport.GetReportFolder();
}
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);
}
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;
}
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);
}
private void InitReportPanel()
{
var report = this.Reports.Single(x => x.Id == (int)currentReport);
if (report == null) {
ShowStop("Failed to get entity of current report.");
return;
}
this.lblReportTitle.Text = report.Name;
this.SelectedColumns.Clear();
this.SelectedColumns1.Clear();
this.SelectedColumns2.Clear();
ShowAsOfDate2();
ShowExportDate();
ShowSelectColumnButton();
var dao = new SqlDbHelper();
var lastSelectValue = "";
var lastSelectValue2 = "";
if (IsMonthly()) {
this.lblExportDate.Text = "数据月份：";
if (this.cmbReportMonth.SelectedIndex >= 0) {
lastSelectValue = this.cmbReportMonth.Text;
}
this.cmbReportMonth.Items.Clear();
DataTable table;
if (currentReport == XEnum.ReportType.C_DQDKQK_M) {
table = dao.ExecuteDataTable("SELECT * FROM dbo.sfGetMonthsInFuture()");
}
else {
table = dao.ExecuteDataTable("SELECT ImportDate, dbo.sfGetImportStatus(ImportDate) AS status FROM Import WHERE DAY(ImportDate + 1) = 1 ORDER BY ImportDate DESC");
}
if (table != null) {
foreach (DataRow row in table.Rows) {
var value = ((DateTime)row[0]).ToString("yyyy-MM");
if (row.ItemArray.Length > 1 && !((string)row[1]).StartsWith("1111111")) {
value += " *";
}
this.cmbReportMonth.Items.Add(value);
if (!string.IsNullOrEmpty(lastSelectValue) && value.Equals(lastSelectValue)) {
this.cmbReportMonth.Text = value;
}
}
// Select the latest one by default
if (this.cmbReportMonth.Items.Count > 0) {
if (this.cmbReportMonth.SelectedIndex < 0) {
this.cmbReportMonth.SelectedIndex = 0;
}
}
}
}
else {
this.lblExportDate.Text = "数据日期：";
var table = dao.ExecuteDataTable("SELECT ImportDate FROM Import ORDER BY ImportDate DESC");
if (this.cmbReportMonth.SelectedIndex >= 0) {
lastSelectValue = this.cmbReportMonth.Text;
}
if (this.cmbReportMonth2.SelectedIndex >= 0) {
lastSelectValue2 = this.cmbReportMonth2.Text;
}
this.cmbReportMonth.Items.Clear();
this.cmbReportMonth2.Items.Clear();
if (table != null) {
foreach (DataRow row in table.Rows) {
var value = ((DateTime)row[0]).ToString("yyyy-MM-dd");
this.cmbReportMonth.Items.Add(value);
this.cmbReportMonth2.Items.Add(value);
if (!string.IsNullOrEmpty(lastSelectValue) && value.Equals(lastSelectValue)) {
this.cmbReportMonth.Text = value;
}
if (!string.IsNullOrEmpty(lastSelectValue2) && value.Equals(lastSelectValue2)) {
this.cmbReportMonth2.Text = value;
}
}
// Select the latest one by default
if (this.cmbReportMonth.Items.Count > 0) {
if (this.cmbReportMonth.SelectedIndex < 0) {
this.cmbReportMonth.SelectedIndex = 0;
}
}
if (this.cmbReportMonth2.Items.Count > 0) {
if (this.cmbReportMonth2.SelectedIndex < 0) {
this.cmbReportMonth2.SelectedIndex = 0;
}
}
}
}
this.txtReportPath.Text = BaseReport.GetReportFolder();
}
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);
}
private string PopulateSheet1(string filePath, TargetTableSheet sheet)
{
var result = "";
var dao = new SqlDbHelper();
var sql = string.Format("EXEC spX_CSHSX_M_1 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
Logger.Debug("Running " + sql);
var table = dao.ExecuteDataTable(sql);
if (table != null) {
result = ExcelHelper.PopulateX_CSHSX_M_1(filePath, sheet, this.AsOfDate, table);
}
else {
result = "Procedure returned zero rows";
}
return result;
}
public string ExportData(XEnum.ReportType report, DateTime asOfDate, DateTime asOfDate2, List<string> columnNames, List<string> columnNames2)
{
this.AsOfDate = asOfDate;
if (report != XEnum.ReportType.C_DQDKQK_M) {
var dao = new SqlDbHelper();
var import = dao.ExecuteScalar(string.Format("SELECT 1 FROM Import WHERE ImportDate = '{0}'", asOfDate.ToString("yyyyMMdd")));
if (import == null) {
return string.Format("{0}的数据还没导入系统", asOfDate.ToString("M月d日"));
}
}
var result = string.Empty;
switch (report) {
case XEnum.ReportType.X_WJFL_M:
result = new LoanRiskPerMonth(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_HYB_M:
result = new LoanRiskPerMonthHYB(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF0102_081_M:
result = new GF0102_081(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF0102_161_M:
result = new GF0102_161(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF0107_141_M:
result = new GF0107_141(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_SF6301_141_M:
result = new SF6301_141(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_SF6401_141_M:
result = new SF6401_141(asOfDate).GenerateReport();
break;
case XEnum.ReportType.X_FXDKTB_D:
result = new X_FXDKTB_D(asOfDate).GenerateReport();
break;
case XEnum.ReportType.X_FXDKBH_D:
result = new X_FXDKBH_D(asOfDate).GenerateReport();
break;
case XEnum.ReportType.C_DQDKQK_M:
result = new C_DQDKQK_M(asOfDate, columnNames, columnNames2).GenerateReport();
break;
case XEnum.ReportType.C_XZDKMX_D:
result = new C_XZDKMX_D(this.AsOfDate, this.AsOfDate2, Columns).GenerateReport();
break;
case XEnum.ReportType.C_JQDKMX_D:
result = new C_JQDKMX_D(this.AsOfDate, this.AsOfDate2, Columns).GenerateReport();
break;
case XEnum.ReportType.X_ZXQYZJXQ_S:
result = new X_ZXQYZJXQ_S(asOfDate).GenerateReport();
break;
case XEnum.ReportType.X_DKZLFL_M:
result = new X_DKZLFL_M(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF1101_121_S:
result = new GF1101_121(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF1103_121_S:
result = new GF1103_121(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF1200_101_S:
result = new GF1200_101(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF1301_081_S:
result = new GF1301_081(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF1302_081_S:
result = new GF1302_081(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF1303_081_S:
result = new GF1303_081(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF1304_081_S:
result = new GF1304_081(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF1403_111_S:
result = new GF1403_111(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_GF1900_151_S:
result = new GF1900_151(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_SF6302_131_S:
result = new SF6302_131(asOfDate).GenerateReport();
break;
case XEnum.ReportType.F_SF6402_131_S:
result = new SF6402_131(asOfDate).GenerateReport();
break;
case XEnum.ReportType.X_BLDKJC_X:
result = new X_BLDKJC_X(asOfDate).GenerateReport();
break;
case XEnum.ReportType.X_CSHSX_M:
result = new X_CSHSX_M(asOfDate).GenerateReport();
break;
case XEnum.ReportType.X_WJFLPRD_D:
result = new X_WJFLPRD_D(asOfDate).GenerateReport();
break;
default:
result = "Unknown report type: " + report;
break;
} return result;
}
