public Report() { InitializeComponent(); // Connect to database file sql_con = new SQLiteConnection("Data Source=" + applicationPath + "\\ExpenseTracker.db;Version=3;New=False;Compress=True;"); sql_cmd = new SQLiteCommand(); sql_con.Open(); sql_cmd.Connection = sql_con; sql_cmd.CommandText = "SELECT * FROM Month"; sql_reader = sql_cmd.ExecuteReader(); while (sql_reader.Read()) { dataGridView.Rows.Add( sql_reader.GetInt32(0), CustomDate.GetThaiMonth(sql_reader.GetInt32(1)), sql_reader.GetDecimal(2).ToString("#,#0.00#"), sql_reader.GetDecimal(3).ToString("#,#0.00#"), sql_reader.GetDecimal(4).ToString("#,#0.00#") ); } sql_reader.Close(); dataGridView.ClearSelection(); }
// 输出 RML 格式的表格 // 本函数负责写入 <table> 元素 // parameters: // nTopLines 顶部预留多少行 public void OutputRmlTable( Report report, SQLiteDataReader table, XmlTextWriter writer, int nMaxLines = -1) { // StringBuilder strResult = new StringBuilder(4096); int i, j; #if NO if (nMaxLines == -1) nMaxLines = table.Count; #endif writer.WriteStartElement("table"); writer.WriteAttributeString("class", "table"); writer.WriteStartElement("thead"); writer.WriteStartElement("tr"); int nEvalCount = 0; // 具有 eval 的栏目个数 for (j = 0; j < report.Count; j++) { PrintColumn column = (PrintColumn)report[j]; if (column.Colspan == 0) continue; if (string.IsNullOrEmpty(column.Eval) == false) nEvalCount++; writer.WriteStartElement("th"); if (string.IsNullOrEmpty(column.CssClass) == false) writer.WriteAttributeString("class", column.CssClass); if (column.Colspan > 1) writer.WriteAttributeString("colspan", column.Colspan.ToString()); writer.WriteString(column.Title); writer.WriteEndElement(); // </th> } writer.WriteEndElement(); // </tr> writer.WriteEndElement(); // </thead> // 合计数组 object[] sums = null; // 2008/12/1 new changed if (report.SumLine) { sums = new object[report.Count]; for (i = 0; i < sums.Length; i++) { sums[i] = null; } } NumberFormatInfo nfi = new CultureInfo("zh-CN", false).NumberFormat; nfi.NumberDecimalDigits = 2; writer.WriteStartElement("tbody"); // Jurassic.ScriptEngine engine = null; if (nEvalCount > 0 && engine == null) { engine = new Jurassic.ScriptEngine(); engine.EnableExposedClrTypes = true; } // 内容行循环 for (i = 0; ; i++) // i < Math.Min(nMaxLines, table.Count) { if (table.HasRows == false) break; // Line line = table[i]; if (engine != null) engine.SetGlobalValue("reader", table); string strLineCssClass = "content"; #if NO if (report.OutputLine != null) { OutputLineEventArgs e = new OutputLineEventArgs(); e.Line = line; e.Index = i; e.LineCssClass = strLineCssClass; report.OutputLine(this, e); if (e.Output == false) continue; strLineCssClass = e.LineCssClass; } #endif // strResult.Append("<tr class='" + strLineCssClass + "'>\r\n"); writer.WriteStartElement("tr"); writer.WriteAttributeString("class", strLineCssClass); // 列循环 for (j = 0; j < report.Count; j++) { PrintColumn column = (PrintColumn)report[j]; if (column.ColumnNumber < -1) { throw (new Exception("PrintColumn对象ColumnNumber列尚未初始化,位置" + Convert.ToString(j))); } string strText = ""; if (column.ColumnNumber != -1) { if (string.IsNullOrEmpty(column.Eval) == false) { // engine.SetGlobalValue("cell", line.GetObject(column.ColumnNumber)); strText = engine.Evaluate(column.Eval).ToString(); } else if (column.DataType == DataType.PriceDouble) { if (table.IsDBNull(column.ColumnNumber /**/) == true) strText = column.DefaultValue; else { double v = table.GetDouble(column.ColumnNumber); /* NumberFormatInfo provider = new NumberFormatInfo(); provider.NumberDecimalDigits = 2; provider.NumberGroupSeparator = "."; provider.NumberGroupSizes = new int[] { 3 }; strText = Convert.ToString(v, provider); * */ strText = v.ToString("N", nfi); } } else if (column.DataType == DataType.PriceDecimal) { if (table.IsDBNull(column.ColumnNumber) == true) strText = column.DefaultValue; else { decimal v = table.GetDecimal(column.ColumnNumber); strText = v.ToString("N", nfi); } } else if (column.DataType == DataType.PriceDecimal) { if (table.IsDBNull(column.ColumnNumber) == true) strText = column.DefaultValue; else { decimal v = table.GetDecimal(column.ColumnNumber); strText = v.ToString("N", nfi); } } else if (column.DataType == DataType.Price) { // Debug.Assert(false, ""); if (table.IsDBNull(column.ColumnNumber) == true) strText = column.DefaultValue; // 2005/5/26 else strText = table.GetString(column.ColumnNumber); // } else strText = table.GetString(column.ColumnNumber/*, column.DefaultValue*/); } else { strText = table.GetString(0); // line.Entry; } writer.WriteStartElement(j == 0 ? "th" : "td"); if (string.IsNullOrEmpty(column.CssClass) == false) writer.WriteAttributeString("class", column.CssClass); writer.WriteString(strText); writer.WriteEndElement(); // </td> if (report.SumLine == true && column.Sum == true && column.ColumnNumber != -1) { try { // if (column.DataType != DataType.Currency) { object v = table.GetValue(column.ColumnNumber); #if NO if (report.SumCell != null) { SumCellEventArgs e = new SumCellEventArgs(); e.DataType = column.DataType; e.ColumnNumber = column.ColumnNumber; e.LineIndex = i; e.Line = line; e.Value = v; report.SumCell(this, e); if (e.Value == null) continue; v = e.Value; } #endif if (sums[j] == null) sums[j] = v; else { sums[j] = AddValue(column.DataType, sums[j], v); // sums[j] = ((decimal)sums[j]) + v; } } } catch (Exception ex) // 俘获可能因字符串转换为整数抛出的异常 { throw new Exception("在累加 行 " + i.ToString() + " 列 " + column.ColumnNumber.ToString() + " 值的时候,抛出异常: " + ex.Message); } } } // strResult.Append("</tr>\r\n"); writer.WriteEndElement(); // </tr> }
private void LoadHistory() { toolStripHeader.Text = "บัญชีรับ-จ่าย ประจำเดือน " + cmbMonth.SelectedItem +" พ.ศ. " + cmbYear.SelectedItem; ClearTable(); sql_cmd.CommandText = "SELECT Date, Description, Type, Amount FROM Detail WHERE Year = '" + cmbYear.SelectedItem + "' AND Month = '" + cmbMonth.SelectedItem + "'"; sql_reader = sql_cmd.ExecuteReader(); while (sql_reader.Read()) { dataGridView.Rows.Add(new Object[] { sql_reader.GetString(0), sql_reader.GetString(1), sql_reader.GetString(2), (sql_reader.GetString(2) == "รับ")? Properties.Resources._in : Properties.Resources._out, sql_reader.GetDecimal(3) }); } sql_reader.Close(); dataGridView.Sort(dataGridView.Columns[0], System.ComponentModel.ListSortDirection.Ascending); dataGridView.ClearSelection(); }
private void LoadCurrentMonth() { toolStripHeader.Text = "บัญชีรับ-จ่าย ประจำเดือน " + CustomDate.GetThaiMonth(DateTime.Today.Month) + " พ.ศ. " + CustomDate.GetThaiYear(DateTime.Today.Year); ClearTable(); // Load data for this month sql_cmd.CommandText = "SELECT Date, Description, Type, Amount FROM Detail WHERE Year = '" + CustomDate.GetThaiYear(DateTime.Today.Year) + "' AND Month = '" + DateTime.Today.Month + "'"; sql_reader = sql_cmd.ExecuteReader(); while (sql_reader.Read()) { dataGridView.Rows.Add(new Object[] { sql_reader.GetString(0), sql_reader.GetString(1), sql_reader.GetString(2), (sql_reader.GetString(2) == "รับ")? Properties.Resources._in : Properties.Resources._out, sql_reader.GetDecimal(3) }); } sql_reader.Close(); dataGridView.Sort(dataGridView.Columns[0], System.ComponentModel.ListSortDirection.Ascending); dataGridView.ClearSelection(); }
private static void ReadQueryResult(QueryResolution resolution, SQLiteDataReader reader, List<KeyValuePair<DateTime, decimal>> values) { switch (resolution) { case QueryResolution.All: var date = reader.GetDateTime(0); var value = reader.GetDecimal(1); values.Add(new KeyValuePair<DateTime, decimal>(date, value)); break; default: var rawDate = Convert.ToString(reader["GroupedDate"]); // Partial time resolutions will cause the raw date to have a minute value // of :0 instead of :00, which will cause parsing errors, so try and fix that if (rawDate.EndsWith(":0")) rawDate += "0"; var resolutionDate = DateTime.Parse(rawDate); var resolutionValue = reader.GetDecimal(reader.GetOrdinal("Value")); values.Add(new KeyValuePair<DateTime, decimal>(resolutionDate, resolutionValue)); break; } }