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