protected void Page_Load(object sender, EventArgs e) { DataHandler dh = new DataHandler(); StringBuilder sb = new StringBuilder(); StringReader rd = new StringReader(ExpList.Text.Trim()); string ln, rest = null; string predToAdd = null; while ((ln = rd.ReadLine()) != null) { if (ln.Length == 0) continue; //skip empty lines. if (ln.ToLower().StartsWith("add,")) { string pred = ln.Substring(4).Trim(); string[] vals = pred.Split(','); if (vals.Length == 2) predToAdd += pred + "\r\n"; else rest += ln + "\r\n"; continue; } string[] parts = ln.Split(','); string amt = parts[2].Replace("$", ""); string sql = string.Format("SELECT TOP 1 TransactionDate FROM Expenses WHERE TransactionDate = '{0}' AND description = '{1}' AND amount = {2}", parts[0].Trim(), parts[1].Trim().Replace("'", "''"), amt); if (Util.SafeDBNull(dh.ExecuteScalar(sql)) == "") { sql = string.Format("INSERT INTO Expenses (TransactionDate, description, amount, Category, PaymentMethod) VALUES('{0}', '{1}', {2}, '{3}', '{4}')", parts[0].Trim(), parts[1].Trim().Replace("'", "''"), amt, parts[3].Trim(), PaymentMethod); dh.ExecuteNonQuery(sql); } else rest += ln + "\n"; } if (predToAdd != null) { File.AppendAllText(Server.MapPath("pred_type.txt"), predToAdd); } ExpList.Text = rest; PaymentMethod = ""; }
// protected void ClearBtn_Click(object sender, System.EventArgs e) // { // KeywordBox.Text = AmountLowBox.Text = AmountHightBox.Text = ""; // EndDateBox.Text = StartDateBox.Text = ""; // TypeDD.SelectedIndex = MethodDD.SelectedIndex = 0; // } // protected void ReportCycle_Click(object sender, System.EventArgs e) // { // DataHandler dh = new DataHandler(); // string sql, s, whr = "1=1"; // if (KeywordBox.Text.Trim().Length > 0) // whr += " AND description like '%" + KeywordBox.Text.Trim().Replace("'", "''") + "%'"; // if (AmountLowBox.Text.Trim().Length > 0) // whr += " AND amount >= " + AmountLowBox.Text.Trim(); // if (AmountHightBox.Text.Trim().Length > 0) // whr += " AND amount < " + AmountHightBox.Text.Trim(); // if (StartDateBox.Text.Trim().Length > 0) // whr += " AND TransactionDate >= '" + StartDateBox.Text.Trim() + "'"; // if (EndDateBox.Text.Trim().Length > 0) // whr += " AND TransactionDate < '" + EndDateBox.Text.Trim() + "'"; // if (TypeDD.SelectedValue.Length > 0) // whr += " AND Category = '" + TypeDD.SelectedValue + "'"; // if (MethodDD.SelectedValue.Length > 0) // whr += " AND PaymentMethod = '" + MethodDD.SelectedValue + "'"; // s = "Search Results:\n"; // sql = "SELECT TOP 300 PaymentMethod, TransactionDate, description, amount FROM Expenses WHERE " + whr + " ORDER BY TransactionDate DESC"; // SqlDataReader rd = dh.ExecuteDataReader(sql); // while (rd.Read()) // { // string desc = (string)rd["description"]; // desc = (desc.Length > 45 ? desc.Substring(0, 45) : desc + new string(' ', 45 - desc.Length)); // s += string.Format("{0}\t{1}\t{2}\t{3:C2}\n", // rd["PaymentMethod"], ((DateTime)rd["TransactionDate"]).ToShortDateString(), desc, rd["amount"]); // } // rd.Close(); // sql = "SELECT SUM(amount) AS amount FROM Expenses WHERE " + whr; // object sum = dh.ExecuteScalar(sql); // if (sum != null && sum != DBNull.Value) // s += string.Format("Total: {0:C2}\n", (Decimal)sum); // ExpList.Text = s; // } // protected void CustomReports_SelectedIndexChanged(object sender, EventArgs e) // { // string sql = null, s = null; // string daterange = " AND TransactionDate >= '" + (DateTime.Now.Year - 1) + "/12/16'"; // switch (CustomReports.SelectedIndex) // { // case 1: sql = @"select month(dateadd(d, 15, TransactionDate)) as data1, sum(amount) as data2 // from Expenses // where (description like '%Shell Oil%' or description like '%76 / Circle%' // or description like '%Chevron%' or description like '%Union 76%' // or description like '%Homeland Petroleum%' or description like '%Exxonmobil%' // or description like '%ULTRA-FUEL%')" + daterange + @" // group by month(dateadd(d, 15, TransactionDate)) // UNION // select 100 as data1, sum(amount) as data2 // from Expenses // where (description like '%Shell Oil%' or description like '%76 / Circle%' // or description like '%Chevron%' or description like '%Union 76%' // or description like '%Homeland Petroleum%' or description like '%Exxonmobil%' // or description like '%ULTRA-FUEL%')" + daterange + @" // order by month(dateadd(d, 15, TransactionDate)) // "; // break; // case 2: sql = @"select month(dateadd(d, 15, TransactionDate)) as data1, sum(amount) as data2 // from Expenses // where Category='1' and (description like '%Vons%' or description like '%99 Ranch%' // or description like '%Seafood City%' or description like '%North Park Produce%' // or description like '%ALBERTSONS%' or description like '%LUCKY SEAFOOD%' // or description like '%Costco%' or description like '%henrys%')" + daterange + @" // group by month(dateadd(d, 15, TransactionDate)) // UNION // select 100 as data1, sum(amount) as data2 // from Expenses // where Category='1' and (description like '%Vons%' or description like '%99 Ranch%' // or description like '%Seafood City%' or description like '%North Park Produce%' // or description like '%ALBERTSONS%' or description like '%LUCKY SEAFOOD%' // or description like '%Costco%' or description like '%henrys%')" + daterange + @" // order by month(dateadd(d, 15, TransactionDate)) // "; // break; // case 3: sql = @"select month(dateadd(d, 15, TransactionDate)) as data1, sum(amount) as data2 // from Expenses // where Category='1' and (description like '%Kfc%' or description like '%Subway Sandwiches%' // or description like '%Starbucks%' or description like '%Quiznos%' // or description like '%JITB #%' or description like '%COTIXAN%' // or description like '%BURGER KING%' or description like '%JACK IN%' // or description like '%EL POLLO LOCO%' or description like '%SUBMARINA%' // or description like '%CARL%' or description like '%GREEK BITES%')" + daterange + @" // group by month(dateadd(d, 15, TransactionDate)) // UNION // select 100 as data1, sum(amount) as data2 // from Expenses // where Category='1' and (description like '%Kfc%' or description like '%Subway Sandwiches%' // or description like '%Starbucks%' or description like '%Quiznos%' // or description like '%JITB #%' or description like '%COTIXAN%' // or description like '%BURGER KING%' or description like '%JACK IN%' // or description like '%EL POLLO LOCO%' or description like '%SUBMARINA%' // or description like '%CARL%' or description like '%GREEK BITES%')" + daterange + @" // order by month(dateadd(d, 15, TransactionDate)) // "; // break; // default: return; // } // DataHandler dh = new DataHandler(); // SqlDataReader rd = dh.ExecuteDataReader(sql); // while (rd.Read()) // s += rd["data1"] + " " + rd["data2"] + "\r\n"; // rd.Close(); // ExpList.Text = s; // } protected void RunSql_Click(object sender, EventArgs e) { DataHandler dh = new DataHandler(); SqlDataReader rd = dh.ExecuteDataReader(ExpList.Text.Trim()); string s = ""; while (rd.Read()) { for (int i = 0; i < rd.FieldCount; i++) s += rd[i] + " | "; s += "\r\n"; } rd.Close(); ExpList.Text = s; }