private string PopulateSheet2(string filePath, TargetTableSheet sheet)
{
var result = "";
var dao = new SqlDbHelper();
var sql = string.Format("EXEC spX_BLDKJC_X_2 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
Logger.Debug("Running " + sql);
var table = dao.ExecuteDataTable(sql);
result = ExcelHelper.PopulateX_BLDKJC_X_2(filePath, sheet, this.AsOfDate, table);
return result;
}
public override string GenerateReport()
{
var result = "";
var fileName = "SF6401-141-境内汇总数据-月-人民币.xls";
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.F_SF6401_141_M);
var filePath = CreateReportFile(report.TemplateName, fileName);
var dao = new SqlDbHelper();
var sql = string.Format("EXEC spSF6401_141 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
Logger.Debug("Running " + sql);
var table1 = dao.ExecuteDataTable(sql);
sql = string.Format("EXEC spSF6401_141_Count '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
Logger.Debug("Running " + sql);
var table2 = dao.ExecuteDataTable(sql);
if (table1 != null && table2 != null) {
result = ExcelHelper.PopulateSF6401_141(filePath, report.Sheets[0], this.AsOfDate, table1, table2);
}
else {
result = "Procedure returned zero rows";
}
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 = "GF1302-081-境内汇总数据-季-人民币.xls";
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.F_GF1302_081_S);
var filePath = CreateReportFile(report.TemplateName, fileName);
var sql = string.Format("EXEC spGF1301_081 '{0}', 'CJ'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var result = "";
var table = dao.ExecuteDataTable(sql);
if (table != null) {
result = ExcelHelper.PopulateGF1302_081(filePath, report.Sheets[0], this.AsOfDate, table);
}
else {
result = "Procedure returned zero rows";
}
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;
}
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();
}
