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 IncomeAndExpenseReportPreviewForm(IncomeAndExpenseTimePeriod timePeriod, SemesterToLoad semesterToLoad, string INN)
{
InitializeComponent();
this.timePeriod = timePeriod;
this.semesterToLoad = semesterToLoad;
this.INN = INN;
}
public void Load(IncomeAndExpenseTimePeriod timePeriod, string INN)
{
if (INN == string.Empty)
{
return;
}
incomeAndExpense = incomeAndExpenseDataProvider.Load(timePeriod, INN);
Bind();
}
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 AddIncomeAndExpenseForm(IncomeAndExpenseTimePeriod timePeriod, string INN)
{
InitializeComponent();
this.timePeriod = timePeriod;
this.INN = INN;
incomeTextBox.KeyPress += TextBoxProcessor.ProcessOnlyFloats;
expenseTextBox.KeyPress += TextBoxProcessor.ProcessOnlyFloats;
dateDateTimePicker.Format = DateTimePickerFormat.Custom;
dateDateTimePicker.CustomFormat = Constants.SQLiteDateFormat;
}
public void DeleteIncomeAndExpense(string internalIndex, IncomeAndExpenseTimePeriod timePeriod)
{
OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();
StringBuilder commandText = new StringBuilder();
commandText.Append("DELETE FROM [Доходы и расходы " + timePeriod.Year + "] WHERE [№ п/п]=");
DataProvider.AddCommandTextDelete(commandText, internalIndex);
myOleDbCommand.CommandText = commandText.ToString();
myOleDbConnection.Open();
myOleDbCommand.ExecuteScalar();
myOleDbConnection.Close();
}
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);
}
public void Delete(string internalIndex, IncomeAndExpenseTimePeriod timePeriod)
{
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("DELETE FROM ДоходыИРасходы WHERE [Индекс]=");
DataProvider.AddCommandTextDelete(commandText, internalIndex);
sqlite_cmd.CommandText = commandText.ToString();
sqlite_cmd.ExecuteScalar();
sqlite_conn.Close();
}
private void MainForm_Load(object sender, EventArgs e)
{
try
{
CheckDataBase();
}
catch (Exception)
{
Environment.Exit(1);
}
keyData = CheckLicense();
AddAvailableINN();
SetESHNDeclarationPartOneLimits();
SetESHNDeclarationPartTwoLimits();
semesterToLoad = SemesterToLoad.BothSemesters;
incomeAndExpenseTimePeriod = new IncomeAndExpenseTimePeriod(GetSelectedYear());
UpdateIncomeAndExpenseDateIntervals();
programLogic.incomeAndExpenseLogic.Load(incomeAndExpenseTimePeriod, GetSelectedINN());
programLogic.commonAssetsLogic.Load(GetSelectedINN());
//IncomeAndExpenseGridView.Columns[0].Width = 60;
//IncomeAndExpenseGridView.Columns[1].Width = 80;
//IncomeAndExpenseGridView.Columns[3].Width = 80;
//IncomeAndExpenseGridView.Columns[4].Width = 80;
saveBackupDialog.FileName = DateTime.Now.ToShortDateString();
CheckSemesterButtonsState();
UseOfPropertyDataGridView.DataSource = useOfProperty;
RequiredSoftwareChecker.CheckApplicationShortcut();
}
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 Delete(int indexInProgramsList, IncomeAndExpenseTimePeriod timePeriod, string INN)
{
incomeAndExpenseDataProvider.Delete(incomeAndExpense[indexInProgramsList].GetInternalIndex(), timePeriod);
Load(timePeriod, INN);
}
public void Insert(IncomeAndExpense incomeAndExpense, IncomeAndExpenseTimePeriod timePeriod, string INN)
{
incomeAndExpenseDataProvider.Insert(incomeAndExpense, INN);
Load(timePeriod, INN);
}
