private int getSoldCountForPeriod(DateTime start, DateTime end, string goodName)
{
start = start.AddHours(1);
end = end.AddHours(1);
OracleConnection connection = OracleDbManager.GetConnection();
OracleCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "demand_count_by_name";
command.Parameters.Add("start_time", OracleDbType.Varchar2).Value = start.ToString();
command.Parameters.Add("end_time", OracleDbType.Varchar2).Value = end.ToString();
command.Parameters.Add("good_name", OracleDbType.Varchar2).Value = goodName;
command.Parameters.Add("count_", OracleDbType.Int32).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
int result;
if (!int.TryParse(command.Parameters["count_"].Value.ToString(), out result))
{
result = 0;
}
OracleDbManager.CloseConnection();
return(result);
}
private void UpdateWh2View()
{
var command = new OracleCommand("SELECT good_id, good_count FROM WAREHOUSE2");
command.Connection = OracleDbManager.GetConnection();
var oraReader = command.ExecuteReader();
wh2Items = new List <WhItem>();
if (oraReader.HasRows)
{
while (oraReader.Read())
{
WhItem whItem = new WhItem();
whItem.Name = goodsDictionary[oraReader.GetInt32(0)];
whItem.Amount = oraReader.GetInt32(1);
wh2Items.Add(whItem);
}
}
List <string> goodsNames = new List <string>();
foreach (var good in goods)
{
goodsNames.Add(good.Name);
}
wh2DataList.ItemsSource = wh2Items;
wh2GoodCmbBox.ItemsSource = goodsNames;
}
private void UpdateGoodsTableView()
{
var command = new OracleCommand("SELECT ID, NAME, PRIORITY FROM GOODS ORDER BY NAME, PRIORITY DESC");
command.Connection = OracleDbManager.GetConnection();
var oraReader = command.ExecuteReader();
goods = new List <Good>();
goodNames = new List <string>();
goodsDictionary = new Dictionary <int, string>();
if (oraReader.HasRows)
{
while (oraReader.Read())
{
Good good = new Good();
good.Name = oraReader.GetString(1);
good.Priority = oraReader.GetInt32(2);
goodsDictionary.Add(oraReader.GetInt32(0), good.Name);
goods.Add(good);
goodNames.Add(good.Name);
}
}
goodsList.ItemsSource = goods;
}
private void DeleteFromWh2Btn_Click(object sender, RoutedEventArgs e)
{
OracleCommand command = new OracleCommand(
String.Format(
"delete from WAREHOUSE2 where GOOD_ID = (select max(ID) FROM GOODS where NAME = '{0}')",
wh2GoodCmbBox.Text
));
command.Connection = OracleDbManager.GetConnection();
var oraReader = command.ExecuteReader();
UpdateAll();
}
private void AddToWh2Btn_Click(object sender, RoutedEventArgs e)
{
OracleCommand command = new OracleCommand(
String.Format(
"INSERT INTO WAREHOUSE2(good_id, good_count) VALUES((SELECT MAX(id) FROM GOODS WHERE NAME = '{0}'), {1})",
wh2GoodCmbBox.Text, wh2GoodCountTxtBox.Text
));
command.Connection = OracleDbManager.GetConnection();
var oraReader = command.ExecuteReader();
UpdateAll();
}
private void UpdatePositionWh2Btn_Click(object sender, RoutedEventArgs e)
{
OracleCommand command = new OracleCommand(
String.Format(
"UPDATE WAREHOUSE2 SET good_count = {0} where good_id = (SELECT MAX(id) FROM GOODS WHERE NAME = '{1}')",
wh2GoodCountTxtBox.Text, wh2GoodCmbBox.Text
));
command.Connection = OracleDbManager.GetConnection();
var oraReader = command.ExecuteReader();
UpdateAll();
}
private void AddValBtn_Click(object sender, RoutedEventArgs e)
{
int priority;
if (Int32.TryParse(priorityTextBox.Text, out priority) == false)
{
MessageBox.Show("Приоритет должен быть целым числом");
return;
}
OracleCommand command = new OracleCommand(
String.Format(
"INSERT INTO GOODS (NAME, PRIORITY) VALUES('{0}', {1})",
goodNameTextBox.Text, priority
));
command.Connection = OracleDbManager.GetConnection();
var oraReader = command.ExecuteReader();
UpdateAll();
}
private void ChangeValBtn_Click(object sender, RoutedEventArgs e)
{
OracleCommand command = new OracleCommand(
String.Format(
"UPDATE GOODS SET NAME = '{0}', PRIORITY = {1} where NAME = '{2}'",
goodNameTextBox.Text, priorityTextBox.Text, oldName
));
command.Connection = OracleDbManager.GetConnection();
try
{
var oraReader = command.ExecuteReader();
}
catch (Exception exception)
{
Console.WriteLine(exception);
MessageBox.Show("Имена товаров должны быть уникальными");
}
UpdateAll();
}
private void Login_Button_Click(object sender, RoutedEventArgs e)
{
string hashedPassword = getHashSha256(password.Password);
OracleCommand command = new OracleCommand(
string.Format(
"SELECT * FROM USERS_LIST WHERE USER_NAME = '{0}' and USER_PASSWORD = '******'",
userName.Text, hashedPassword
)
);
command.Connection = OracleDbManager.GetConnection();
var oraReader = command.ExecuteReader();
if (oraReader.HasRows)
{
(new ManagerWindow()).Show();
this.Close();
}
else
{
MessageBox.Show("Неверный логин или пароль");
}
}
private void UpdateSalesView()
{
var command = new OracleCommand("SELECT SALES.ID, GOODS.NAME, SALES.GOOD_COUNT, to_char(SALES.CREATE_DATE, 'DD-MM-YYYY HH24:MI:SS')as Sale_Date FROM SALES, GOODS WHERE GOODS.ID = SALES.GOOD_ID ORDER BY Sale_Date DESC, GOODS.NAME");
command.Connection = OracleDbManager.GetConnection();
var oraReader = command.ExecuteReader();
itemsOnSale = new List <SaleItem>();
if (oraReader.HasRows)
{
while (oraReader.Read())
{
var itemOnSale = new SaleItem();
itemOnSale.Id = oraReader.GetInt32(0);
itemOnSale.Name = oraReader.GetString(1);
itemOnSale.Count = oraReader.GetInt32(2);
itemOnSale.CreateDate = DateTime.ParseExact(oraReader.GetString(3), "dd-MM-yyyy HH:mm:ss", null);
itemsOnSale.Add(itemOnSale);
}
}
salesGoodCmbBox.ItemsSource = goodNames;
salesList.ItemsSource = itemsOnSale;
}
private void AddToSalesBtn_Click(object sender, RoutedEventArgs e)
{
int count = int.Parse(saleCountTxtBox.Text);
int wh1Count = 0;
int wh2Count = 0;
foreach (var wh1Item in wh1Items)
{
if (wh1Item.Name == salesGoodCmbBox.Text)
{
wh1Count = wh1Item.Amount;
}
}
foreach (var wh2Item in wh2Items)
{
if (wh2Item.Name == salesGoodCmbBox.Text)
{
wh2Count = wh2Item.Amount;
}
}
// add to sales table whiout view update
OracleCommand command = new OracleCommand(
string.Format(
"INSERT INTO SALES (GOOD_ID, GOOD_COUNT, CREATE_DATE) VALUES((SELECT MAX(ID) FROM GOODS WHERE NAME = '{0}'), {1}, CURRENT_TIMESTAMP)",
salesGoodCmbBox.Text, count
));
command.Connection = OracleDbManager.GetConnection();
var oraReader = command.ExecuteReader();
// decide, that we should update only one WH or two we should
if (wh2Count >= count)
{
// update note in spb WH with Wh2 view update
command = new OracleCommand(
string.Format(
"UPDATE WAREHOUSE2 SET good_count = {0} where good_id = (SELECT MAX(id) FROM GOODS WHERE NAME = '{1}')",
wh2Count - count, salesGoodCmbBox.Text
));
command.Connection = OracleDbManager.GetConnection();
oraReader = command.ExecuteReader();
UpdateWh2View();
}
else
{
// delete note from spb WH with Wh2 view update
command = new OracleCommand(
string.Format(
"DELETE FROM WAREHOUSE2 WHERE GOOD_ID = (SELECT MAX(id) FROM GOODS WHERE NAME = '{0}')",
salesGoodCmbBox.Text
));
command.Connection = OracleDbManager.GetConnection();
oraReader = command.ExecuteReader();
count = count - wh2Count;
UpdateWh2View();
// update note in regional WH with Wh1 view update
command = new OracleCommand(
string.Format(
"UPDATE WAREHOUSE1 SET good_count = {0} where good_id = (SELECT MAX(id) FROM GOODS WHERE NAME = '{1}')",
wh1Count - count, salesGoodCmbBox.Text
));
command.Connection = OracleDbManager.GetConnection();
oraReader = command.ExecuteReader();
UpdateWh1View();
}
// update sales view after all
UpdateAll();
}