public static List <Subjects> ReadMainTable()
{
string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DataBase.mdb";
var dbConnection = new OleDbConnection(connection);
var dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = "SELECT * FROM MainTable";
dbConnection.Open();
var dbReader = dbCommand.ExecuteReader();
List <Subjects> list = new List <Subjects>();
while (dbReader.Read())
{
Subjects obj = new Subjects();
obj.ID = int.Parse(dbReader["ID"].ToString());
obj.Name = dbReader["Name"].ToString();
obj.Number = int.Parse(dbReader["Individual number"].ToString());
obj.InitalCost = Convert.ToDouble(dbReader["Initial cost"].ToString());
obj.Depreciation = Convert.ToDouble(dbReader["Depreciation"].ToString());
obj.Group = int.Parse(dbReader["Group Number"].ToString());
obj.UsefulTime = int.Parse(dbReader["Useful time"].ToString());
obj.MonthAdd = int.Parse(dbReader["Month add"].ToString());
obj.YearAdd = int.Parse(dbReader["Year add"].ToString());
bool boolean = Convert.ToBoolean(dbReader["IsWorking"].ToString());
if (boolean)
{
obj.IsWorking = "Эксплуатируется";
}
else
{
obj.IsWorking = "Списано";
}
obj.Account = dbReader["Account"].ToString();
list.Add(obj);
}
dbReader.Close();
dbConnection.Close();
return(list);
}
private void AddSubject(Subjects subject)
{
string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DataBase.mdb";
var dbConnection = new OleDbConnection(connection);
var dbCommand = dbConnection.CreateCommand();
dbConnection.Open();
dbCommand.CommandText = $"INSERT INTO MainTable ([Name], [Individual number],[Initial cost],[Depreciation],[Month add],[Year add],[Useful time],[Group Number],[Account]) " +
"VALUES (@Name, @Number,@InitalCost,@Depreciation,@MonthAdd,@YearAdd,@UsefulTime,@Group,@Account)";
dbCommand.Parameters.Add(new OleDbParameter("@Name", OleDbType.VarChar));
dbCommand.Parameters.Add(new OleDbParameter("@Number", OleDbType.Integer));
dbCommand.Parameters.Add(new OleDbParameter("@InitalCost", OleDbType.Double));
dbCommand.Parameters.Add(new OleDbParameter("@Depreciation", OleDbType.Double));
dbCommand.Parameters.Add(new OleDbParameter("@MonthAdd", OleDbType.Integer));
dbCommand.Parameters.Add(new OleDbParameter("@YearAdd", OleDbType.Integer));
dbCommand.Parameters.Add(new OleDbParameter("@UsefulTime", OleDbType.Integer));
dbCommand.Parameters.Add(new OleDbParameter("@Group", OleDbType.Integer));
dbCommand.Parameters.Add(new OleDbParameter("@Account", OleDbType.VarChar));
dbCommand.Parameters[0].Value = subject.Name;
dbCommand.Parameters[1].Value = subject.Number;
dbCommand.Parameters[2].Value = subject.InitalCost;
dbCommand.Parameters[3].Value = subject.Depreciation;
dbCommand.Parameters[4].Value = subject.MonthAdd;
dbCommand.Parameters[5].Value = subject.YearAdd;
dbCommand.Parameters[6].Value = subject.UsefulTime;
dbCommand.Parameters[7].Value = subject.Group;
dbCommand.Parameters[8].Value = subject.Account;
dbCommand.ExecuteNonQuery();
dbConnection.Close();
}
public void CreateTable(string SetMonth, string SetYear)
{
string strdate = SetMonth + SetYear;
if (int.Parse(SetYear) < 2013)
{
throw new ArgumentException("Введенный год меньше 2013");
}
int date = int.Parse(strdate);
string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DataBase.mdb";
var dbConnection = new OleDbConnection(connection);
var dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = $"SELECT ID,[Residual cost],[Residual date] FROM Orders WHERE [Order date] = {date}";
dbConnection.Open();
var dbReader = dbCommand.ExecuteReader();
if (!dbReader.HasRows)
{
subjects = Subjects.ReadDB(SetYear);
dbReader.Close();
SetResidual(SetMonth, SetYear);
dbCommand.CommandText = "INSERT INTO Orders ([ID], [Residual cost], [Residual date],[Order date])" +
" VALUES (@ID, @ResidualCost, @ResidualDate, @date)";
dbCommand.Parameters.Add(new OleDbParameter("@ID", OleDbType.Integer));
dbCommand.Parameters.Add(new OleDbParameter("@ResidualCost", OleDbType.Double));
dbCommand.Parameters.Add(new OleDbParameter("@ResidualDate", OleDbType.Integer));
dbCommand.Parameters.Add(new OleDbParameter("@date", OleDbType.Integer));
foreach (var item in subjects)
{
dbCommand.Parameters[0].Value = item.ID;
dbCommand.Parameters[1].Value = item.ResidualCost;
dbCommand.Parameters[2].Value = item.ResidualDate;
dbCommand.Parameters[3].Value = date;
dbCommand.ExecuteNonQuery();
}
Title = "Отчет за " + SetMonth + "." + SetYear;
MyGrid2.IsEnabled = false;
MyGrid2.Visibility = Visibility.Hidden;
MyGrid.IsEnabled = true;
MyGrid.Visibility = Visibility.Visible;
MyGrid.ItemsSource = subjects;
dbConnection.Close();
}
else
{
subjects = Subjects.ReadDB(SetYear);
int[] arrID = new int[0];
int index = 0;
while (dbReader.Read())
{
for (int j = 0; j < subjects.Count; j++)
{
if (subjects[j].ID == int.Parse(dbReader["ID"].ToString()))
{
subjects[j].ResidualCost = Convert.ToDouble(dbReader["Residual cost"].ToString());
subjects[j].ResidualDate = int.Parse(dbReader["Residual date"].ToString());
Array.Resize(ref arrID, arrID.Length + 1);
arrID[index] = subjects[j].ID;
index++;
}
}
}
dbReader.Close();
index = 0;
if (arrID.Length != subjects.Count)
{
for (int j = 0; j < subjects.Count; j++)
{
bool check = false;
for (int i = 0; i < arrID.Length; i++)
{
if (arrID[i] == subjects[j].ID)
{
check = true;
break;
}
}
if (!check)
{
int summMonth = int.Parse(SetMonth) - subjects[j].MonthAdd;
summMonth += (int.Parse(SetYear) - subjects[j].YearAdd) * 12;
subjects[j].ResidualCost = subjects[j].InitalCost - subjects[j].Depreciation * summMonth;
subjects[j].ResidualCost = Math.Round(subjects[j].ResidualCost, 2);
if (subjects[j].ResidualCost < 0)
{
subjects[j].ResidualCost = 0;
}
subjects[j].ResidualDate = subjects[j].UsefulTime - summMonth;
if (subjects[j].ResidualCost < 0)
{
subjects[j].ResidualCost = 0;
}
dbCommand.CommandText = "INSERT INTO Orders ([ID], [Residual cost], [Residual date],[Order date])" +
" VALUES (@ID, @ResidualCost, @ResidualDate, @date)";
dbCommand.Parameters.Add(new OleDbParameter("@ID", OleDbType.Integer));
dbCommand.Parameters.Add(new OleDbParameter("@ResidualCost", OleDbType.Double));
dbCommand.Parameters.Add(new OleDbParameter("@ResidualDate", OleDbType.Integer));
dbCommand.Parameters.Add(new OleDbParameter("@date", OleDbType.Integer));
dbCommand.Parameters[0].Value = subjects[j].ID;
dbCommand.Parameters[1].Value = subjects[j].ResidualCost;
dbCommand.Parameters[2].Value = subjects[j].ResidualDate;
dbCommand.Parameters[3].Value = date;
dbCommand.ExecuteNonQuery();
index++;
}
}
}
Title = "Отчет за " + SetMonth + "." + SetYear;
MyGrid2.IsEnabled = false;
MyGrid2.Visibility = Visibility.Hidden;
MyGrid.IsEnabled = true;
MyGrid.Visibility = Visibility.Visible;
MyGrid.ItemsSource = subjects;
dbConnection.Close();
}
}