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