public void Update(IncomeAndExpense incomeAndExpense) { SQLiteConnection sqlite_conn; SQLiteCommand sqlite_cmd; sqlite_conn = new SQLiteConnection(DataProvider.SQLiteConnectionString); sqlite_conn.Open(); sqlite_cmd = sqlite_conn.CreateCommand(); StringBuilder commandText = new StringBuilder(); commandText.Append("UPDATE ДоходыИРасходы SET "); DataProvider.AddCommandTextUpdate(commandText, "Дата", DataProvider.ConvertDateToSQLiteFormat(incomeAndExpense.Date)); DataProvider.AddCommandTextUpdate(commandText, "НомерДокумента", incomeAndExpense.DocumentsNumber); DataProvider.AddCommandTextUpdate(commandText, "СодержаниеОперации", incomeAndExpense.SubstanceOfTransaction); DataProvider.AddCommandTextUpdate(commandText, "Доход", incomeAndExpense.Income); DataProvider.AddCommandTextUpdate(commandText, "Расход", incomeAndExpense.Expense, last: true); commandText.Append(" WHERE Индекс = " + incomeAndExpense.GetInternalIndex() + ";"); sqlite_cmd.CommandText = commandText.ToString(); sqlite_cmd.ExecuteNonQuery(); sqlite_conn.Close(); }
public void Insert(IncomeAndExpense incomeAndExpense, string INN) { SQLiteConnection sqlite_conn; SQLiteCommand sqlite_cmd; sqlite_conn = new SQLiteConnection(DataProvider.SQLiteConnectionString); sqlite_conn.Open(); sqlite_cmd = sqlite_conn.CreateCommand(); StringBuilder commandText = new StringBuilder(); commandText.Append("INSERT INTO ДоходыИРасходы"); commandText.Append("(ИНН, Дата, НомерДокумента, СодержаниеОперации, Доход, Расход"); commandText.Append(") VALUES("); DataProvider.AddCommandTextInsert(commandText, INN); DataProvider.AddCommandTextInsert(commandText, DataProvider.ConvertDateToSQLiteFormat(incomeAndExpense.Date)); DataProvider.AddCommandTextInsert(commandText, incomeAndExpense.DocumentsNumber); DataProvider.AddCommandTextInsert(commandText, incomeAndExpense.SubstanceOfTransaction); DataProvider.AddCommandTextInsert(commandText, incomeAndExpense.Income); DataProvider.AddCommandTextInsert(commandText, incomeAndExpense.Expense, last: true); commandText.Append(");"); sqlite_cmd.CommandText = commandText.ToString(); sqlite_cmd.ExecuteNonQuery(); sqlite_conn.Close(); }
public void InsertIncomeAndExpense(IncomeAndExpense incomeAndExpense, IncomeAndExpenseTimePeriod timePeriod) { OleDbConnection myOleDbConnection = new OleDbConnection(connectionString); OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand(); StringBuilder commandText = new StringBuilder(); commandText.Append("INSERT INTO [Доходы и расходы " + timePeriod.Year + "]"); commandText.Append("([Дата], [№ документа], [Содержание операции],"); commandText.Append("[Доход], [Расход]"); commandText.Append(") VALUES("); DataProvider.AddCommandTextInsert(commandText, incomeAndExpense.Date); DataProvider.AddCommandTextInsert(commandText, incomeAndExpense.DocumentsNumber); DataProvider.AddCommandTextInsert(commandText, incomeAndExpense.SubstanceOfTransaction); DataProvider.AddCommandTextInsert(commandText, incomeAndExpense.Income); DataProvider.AddCommandTextInsert(commandText, incomeAndExpense.Expense, last: true); commandText.Append(");"); myOleDbCommand.CommandText = commandText.ToString(); myOleDbConnection.Open(); myOleDbCommand.ExecuteScalar(); myOleDbConnection.Close(); }
public List <IncomeAndExpense> LoadIncomeAndExpense(IncomeAndExpenseTimePeriod timePeriod) { List <IncomeAndExpense> incomeAndExpense = new List <IncomeAndExpense>(); OleDbConnection myOleDbConnection = new OleDbConnection(connectionString); OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand(); string commandText = ""; commandText += "SELECT * FROM [Доходы и расходы " + timePeriod.Year + "] WHERE [Дата] >= DateSerial(" + timePeriod.StartDate.Year + "," + timePeriod.StartDate.Month + "," + timePeriod.StartDate.Day + ")"; commandText += "AND [Дата] <= DateSerial(" + timePeriod.EndDate.Year + "," + timePeriod.EndDate.Month + "," + timePeriod.EndDate.Day + ")"; myOleDbCommand.CommandText = commandText; myOleDbConnection.Open(); OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader(); while (myOleDbDataReader.Read()) { IncomeAndExpense temporaryIncomeAndExpense = new IncomeAndExpense(); string timeFormat = "dd.MM.yyyy h:mm:ss"; CultureInfo provider = CultureInfo.InvariantCulture; string internalIndex = myOleDbDataReader["№ п/п"].ToString(); temporaryIncomeAndExpense.SetInternalIndex(internalIndex); string billingDate = myOleDbDataReader["Дата"].ToString(); if (billingDate != "") { temporaryIncomeAndExpense.Date = DateTime.ParseExact(billingDate, timeFormat, provider).ToShortDateString(); } else { temporaryIncomeAndExpense.Date = "Не задано"; } temporaryIncomeAndExpense.DocumentsNumber = myOleDbDataReader["№ документа"].ToString(); temporaryIncomeAndExpense.Expense = myOleDbDataReader["Расход"].ToString(); temporaryIncomeAndExpense.Income = myOleDbDataReader["Доход"].ToString(); temporaryIncomeAndExpense.SubstanceOfTransaction = myOleDbDataReader["Содержание операции"].ToString(); incomeAndExpense.Add(temporaryIncomeAndExpense); } myOleDbConnection.Close(); return(incomeAndExpense); }
public EditIncomeAndExpenseForm(IncomeAndExpense incomeAndExpense, IncomeAndExpenseTimePeriod timePeriod, string INN) { InitializeComponent(); this.incomeAndExpense = incomeAndExpense; this.timePeriod = timePeriod; this.INN = INN; incomeTextBox.KeyPress += TextBoxProcessor.ProcessOnlyFloats; expenseTextBox.KeyPress += TextBoxProcessor.ProcessOnlyFloats; dateDateTimePicker.Format = DateTimePickerFormat.Custom; dateDateTimePicker.CustomFormat = Constants.SQLiteDateFormat; }
public List <IncomeAndExpense> Load(IncomeAndExpenseTimePeriod timePeriod, string INN) { List <IncomeAndExpense> incomeAndExpense = new List <IncomeAndExpense>(); SQLiteConnection sqlite_conn; SQLiteCommand sqlite_cmd; SQLiteDataReader sqlite_datareader; sqlite_conn = new SQLiteConnection(DataProvider.SQLiteConnectionString); sqlite_conn.Open(); sqlite_cmd = sqlite_conn.CreateCommand(); StringBuilder commandText = new StringBuilder(); commandText.Append("SELECT Индекс, НомерДокумента, Дата, СодержаниеОперации, Доход, Расход FROM ДоходыИРасходы WHERE date([Дата]) >= date(\"" + timePeriod.StartDate.Year + "-" + timePeriod.StartDate.Month.ToString("00") + "-" + timePeriod.StartDate.Day.ToString("00") + "\")"); commandText.Append("AND date([Дата]) <= date(\"" + timePeriod.EndDate.Year + "-" + timePeriod.EndDate.Month.ToString("00") + "-" + timePeriod.EndDate.Day.ToString("00") + "\") "); commandText.Append("AND ИНН=" + INN + " "); commandText.Append("ORDER BY Дата"); sqlite_cmd.CommandText = commandText.ToString(); sqlite_datareader = sqlite_cmd.ExecuteReader(); while (sqlite_datareader.Read()) { IncomeAndExpense temporaryIncomeAndExpense = new IncomeAndExpense(); string internalIndex = sqlite_datareader["Индекс"].ToString(); temporaryIncomeAndExpense.SetInternalIndex(internalIndex); string billingDate = sqlite_datareader["Дата"].ToString(); temporaryIncomeAndExpense.Date = DataProvider.ConvertDateThatCantBeEmpty(billingDate); temporaryIncomeAndExpense.DocumentsNumber = sqlite_datareader["НомерДокумента"].ToString(); temporaryIncomeAndExpense.Expense = sqlite_datareader["Расход"].ToString(); temporaryIncomeAndExpense.Income = sqlite_datareader["Доход"].ToString(); temporaryIncomeAndExpense.SubstanceOfTransaction = sqlite_datareader["СодержаниеОперации"].ToString(); incomeAndExpense.Add(temporaryIncomeAndExpense); } sqlite_datareader.Close(); sqlite_conn.Close(); return(incomeAndExpense); }
private void AddIncomeAndExpenseButton_Click(object sender, EventArgs e) { if ((incomeTextBox.Text == "0" && expenseTextBox.Text == "0") || (incomeTextBox.Text != "0" && expenseTextBox.Text != "0")) { MessageBox.Show("Заполните расход либо доход."); return; } IncomeAndExpense incomeAndExpense = new IncomeAndExpense(); incomeAndExpense.Date = dateDateTimePicker.Text; incomeAndExpense.DocumentsNumber = documentsNumberTextBox.Text; incomeAndExpense.SubstanceOfTransaction = substanceOfTransactionTextBox.Text; incomeAndExpense.Income = incomeTextBox.Text; incomeAndExpense.Expense = expenseTextBox.Text; try { Convert.ToDouble(incomeAndExpense.Income); } catch (FormatException) { MessageBox.Show("Поле дохода указано неверно."); return; } try { Convert.ToDouble(incomeAndExpense.Expense); } catch (FormatException) { MessageBox.Show("Поле расхода указано не верно"); return; } MainForm.programLogic.incomeAndExpenseLogic.Insert(incomeAndExpense, timePeriod, INN); Close(); }
private void editIncomeAndExpenseButton_Click(object sender, EventArgs e) { string internalIndex = incomeAndExpense.GetInternalIndex(); incomeAndExpense = new IncomeAndExpense(); incomeAndExpense.SetInternalIndex(internalIndex); incomeAndExpense.Date = dateDateTimePicker.Text; incomeAndExpense.DocumentsNumber = documentsNumberTextBox.Text; incomeAndExpense.SubstanceOfTransaction = substanceOfTransactionTextBox.Text; incomeAndExpense.Income = incomeTextBox.Text; incomeAndExpense.Expense = expenseTextBox.Text; try { Convert.ToDouble(incomeAndExpense.Income); } catch (FormatException) { MessageBox.Show("Поле дохода указано неверно."); return; } try { Convert.ToDouble(incomeAndExpense.Expense); } catch (FormatException) { MessageBox.Show("Поле расхода указано не верно"); return; } MainForm.programLogic.incomeAndExpenseLogic.Update(incomeAndExpense, timePeriod, INN); Close(); }
public void UpdateIncomeAndExpense(IncomeAndExpense incomeAndExpense, IncomeAndExpenseTimePeriod timePeriod) { OleDbConnection myOleDbConnection = new OleDbConnection(connectionString); OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand(); StringBuilder commandText = new StringBuilder(); commandText.Append("UPDATE [Доходы и расходы " + timePeriod.Year + "] SET "); DataProvider.AddCommandTextUpdate(commandText, "Дата", incomeAndExpense.Date); DataProvider.AddCommandTextUpdate(commandText, "№ документа", incomeAndExpense.DocumentsNumber); DataProvider.AddCommandTextUpdate(commandText, "Содержание операции", incomeAndExpense.SubstanceOfTransaction); DataProvider.AddCommandTextUpdate(commandText, "Доход", incomeAndExpense.Income); DataProvider.AddCommandTextUpdate(commandText, "Расход", incomeAndExpense.Expense, last: true); commandText.Append(" WHERE [№ п/п] = "); commandText.Append(incomeAndExpense.GetInternalIndex()); commandText.Append(";"); myOleDbCommand.CommandText = commandText.ToString(); myOleDbConnection.Open(); myOleDbCommand.ExecuteScalar(); myOleDbConnection.Close(); }
public void Update(IncomeAndExpense incomeAndExpense, IncomeAndExpenseTimePeriod timePeriod, string INN) { incomeAndExpenseDataProvider.Update(incomeAndExpense); Load(timePeriod, INN); }
public void Insert(IncomeAndExpense incomeAndExpense, IncomeAndExpenseTimePeriod timePeriod, string INN) { incomeAndExpenseDataProvider.Insert(incomeAndExpense, INN); Load(timePeriod, INN); }