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);
}
public override string GenerateReport()
{
if (this.ReportType == XEnum.ReportType.C_XZDKMX_D)
{
this.ReportFileName = string.Format("各支行新增风险贷款明细表（{0}-{1}）.xls", this.AsOfDate.ToString("yyyy.M.d"), this.AsOfDate2.ToString("yyyy.M.d"));
this.SPName = "spC_XZDKMX_D";
}
else if (this.ReportType == XEnum.ReportType.C_JQDKMX_D)
{
this.ReportFileName = string.Format("各支行化解及清息贷款明细表（{0}-{1}）.xls", this.AsOfDate.ToString("yyyy.M.d"), this.AsOfDate2.ToString("yyyy.M.d"));
this.SPName = "spC_JQDKMX_D";
}
else
{
var msg = "Unknown Import loan daily report type: " + this.ReportType.ToString();
Logger.Error(msg);
return(msg);
}
var fileName = this.ReportFileName;
Logger.Debug("Generating " + fileName);
Logger.Debug("Selected columns Loan: " + string.Join(", ", this.DbColumnNames));
var report = TargetTable.GetById(this.ReportType);
var filePath = CreateReportFile(report.TemplateName, fileName, this.AsOfDate2);
foreach (var sheet in report.Sheets)
{
PopulateSheet(filePath, sheet);
}
ExcelHelper.ActivateSheet(filePath);
return(string.Empty);
}
public override string GenerateReport()
{
var fileName = "风险贷款变化情况表.xls";
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_FXDKBH_D);
var filePath = CreateReportFile(report.TemplateName, fileName);
foreach (var sheet in report.Sheets)
{
PopulateSheet(filePath, sheet);
}
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 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 override string GenerateReport()
{
var fileName = "中小企业资金需求及银行业支持情况报表.xls";
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.X_ZXQYZJXQ_S);
var filePath = CreateReportFile(report.TemplateName, fileName);
PopulateSheet(filePath, report.Sheets[0]);
return(string.Empty);
}
public override string GenerateReport()
{
var fileName = string.Format("榆林分行{0}月末风险贷款情况表-行业版.xls", this.AsOfDate.Month);
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.F_HYB_M);
var filePath = CreateReportFile(report.TemplateName, fileName);
foreach (var sheet in report.Sheets)
{
PopulateSheet(filePath, sheet);
}
return(string.Empty);
}
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 = 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);
}
public override string GenerateReport()
{
var fileName = string.Format("城商行榆林地区{0}月授信情况统计表.xls", this.AsOfDate.Month);
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.X_CSHSX_M);
var filePath = CreateReportFile(report.TemplateName, fileName);
var result = string.Empty;
foreach (var sheet in report.Sheets)
{
switch (sheet.Index)
{
case 1:
result = PopulateSheet1(filePath, sheet);
break;
case 2:
result = PopulateSheet2(filePath, sheet);
break;
case 3:
PopulateSheet3(filePath, sheet);
break;
case 4:
result = PopulateSheet4(filePath, sheet);
break;
default:
break;
}
if (!string.IsNullOrEmpty(result))
{
break;
}
}
ExcelHelper.ActivateSheet(filePath);
return(result);
}
public override string GenerateReport()
{
var fileName = string.Format("{0}月到期贷款情况.xls", this.AsOfDate.ToString("MM").TrimStart('0'));
Logger.Debug("Generating " + fileName);
Logger.Debug("Selected columns Public: " + string.Join(", ", this.PublicColumns));
Logger.Debug("Selected columns Private: " + string.Join(", ", this.PrivateColumns));
var report = TargetTable.GetById(XEnum.ReportType.C_DQDKQK_M);
var filePath = CreateReportFile(report.TemplateName, fileName);
foreach (var sheet in report.Sheets)
{
PopulateSheet(filePath, sheet);
}
ExcelHelper.ActivateSheet(filePath);
return(string.Empty);
}
public override string GenerateReport()
{
var fileName = string.Format("{0}新榆林地区不良贷款监测旬报（旬报）.xls", this.AsOfDate.ToString("M.dd"));
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.X_BLDKJC_X);
var filePath = CreateReportFile(report.TemplateName, fileName);
var result = string.Empty;
foreach (var sheet in report.Sheets)
{
if (sheet.Name.Equals("附表1"))
{
result = PopulateSheet1(filePath, sheet);
}
else if (sheet.Name.Equals("附表2"))
{
result = PopulateSheet2(filePath, sheet);
}
else if (sheet.Name.Equals("附表3"))
{
result = PopulateSheet3(filePath, sheet);
}
else if (sheet.Name.Equals("附表4"))
{
result = PopulateSheet4(filePath, sheet);
}
if (!string.IsNullOrEmpty(result))
{
break;
}
}
ExcelHelper.ActivateSheet(filePath);
return(result);
}