Beispiel #1
0
        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 = "";
        }
Beispiel #2
0
        //        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;
        }