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