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