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