private async Task Add() { try { ProductInputDialog _defaultInputDialog = new ProductInputDialog("Please fill in the details of new product", mode: "add"); if (_defaultInputDialog.ShowDialog() == true) { if ( _defaultInputDialog.ProductResult is null || _defaultInputDialog.ProductResult.Name == "" || _defaultInputDialog.ProductResult.Category == "" || _defaultInputDialog.ProductResult.Design_code == "" || _defaultInputDialog.ProductResult.Colour_code == "" || _defaultInputDialog.ProductResult.Price == "" ) { IsLoading = false; MessageBox.Show("Empty column detected, all columns can't be empty", "UPO$$"); } else { dynamic param = new { name = _defaultInputDialog.ProductResult.Name, category = _defaultInputDialog.ProductResult.Category, designCode = _defaultInputDialog.ProductResult.Design_code, colourCode = _defaultInputDialog.ProductResult.Colour_code, price = _defaultInputDialog.ProductResult.Price }; RootProductObject Response = await ObjProductService.PostAPI("addProduct", param, _Path); MessageBox.Show(Response.Msg, "UPO$$"); if (Response.Status is "ok") { RefreshTextBox(); await Search(); } } } }
private async Task ResetProductsTable() { System.Diagnostics.Trace.WriteLine("Reset Products Table"); var currentDateTime = DateTime.Now; string lastUpdate = null; using (var connection = new SQLiteConnection("Data Source=SQLiteDatabase.db")) { connection.Open(); try { using var command = new SQLiteCommand(connection); // drop products table command.CommandText = "DROP TABLE IF EXISTS products"; command.ExecuteNonQuery(); // create products table command.CommandText = "CREATE TABLE IF NOT EXISTS products(" + "id INTEGER, " + "product_no TEXT UNIQUE, " + "name TEXT, " + "category TEXT, " + "design_code TEXT, " + "colour_code TEXT, " + "price REAL, " + "barcode TEXT, " + "is_active INTEGER, " + "remaining_stock REAL" + ")"; command.ExecuteNonQuery(); // request all products and stocks data from remote server dynamic param = new { currentBranch = Properties.Settings.Default.CurrentBranch }; RootProductObject Response = await ObjProductService.PostAPI("getProductAndStockList", param, _Path); if (Response.Status != "ok") { //API Error 1 MessageBox.Show("Failed to reset products table, please contact IT support [AE1]", "UPO$$"); } else { ProductList = new ObservableCollection <Product>(Response.Data); if (ProductList.Count > 0) { // insert into products table foreach (var product in ProductList) { command.CommandText = "INSERT INTO products(id, product_no, name, category, design_code, colour_code, price, barcode, is_active, remaining_stock)" + " VALUES(@id, @product_no, @name, @category, @design_code, @colour_code, @price, @barcode, @is_active, @remaining_stock)"; command.Parameters.AddWithValue("@id", product.Id); command.Parameters.AddWithValue("@product_no", product.Product_no); command.Parameters.AddWithValue("@name", product.Name); command.Parameters.AddWithValue("@category", product.Category); command.Parameters.AddWithValue("@design_code", product.Design_code); command.Parameters.AddWithValue("@colour_code", product.Colour_code); command.Parameters.AddWithValue("@price", product.Price); command.Parameters.AddWithValue("@barcode", product.Barcode); command.Parameters.AddWithValue("@is_active", product.Is_active); command.Parameters.AddWithValue("@remaining_stock", product.Remaining_stock); command.Prepare(); command.ExecuteNonQuery(); } } // get last update record command.CommandText = "SELECT * FROM update_record"; using (SQLiteDataReader rdr = command.ExecuteReader()) { if (rdr.Read()) { lastUpdate = rdr[0].ToString(); } rdr.Close(); } if (lastUpdate == null) { // create product update record command.CommandText = "INSERT INTO update_record(type, last_update) VALUES(@type, @currentDateTime)"; command.Parameters.AddWithValue("@type", "product"); command.Parameters.AddWithValue("@currentDateTime", currentDateTime); command.Prepare(); command.ExecuteNonQuery(); // create remaining_stock update record command.CommandText = "INSERT INTO update_record(type, last_update) VALUES(@type, @currentDateTime)"; command.Parameters.AddWithValue("@type", "remaining_stock"); command.Parameters.AddWithValue("@currentDateTime", currentDateTime); command.Prepare(); command.ExecuteNonQuery(); } else { // update last_update record command.CommandText = "UPDATE update_record SET last_update = @currentDateTime"; command.Parameters.AddWithValue("@currentDateTime", currentDateTime); command.Prepare(); command.ExecuteNonQuery(); } } } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "UPO$$"); } connection.Close(); System.Diagnostics.Trace.WriteLine("finished Reset Products Table"); } }
public async Task CheckBranch() { System.Diagnostics.Trace.WriteLine("Check User Branch"); string currentBranch = ""; using (var connection = new SQLiteConnection("Data Source=SQLiteDatabase.db")) { connection.Open(); try { using var command = new SQLiteCommand(connection); command.CommandText = "SELECT branch FROM users"; using (SQLiteDataReader rdr = command.ExecuteReader()) { if (rdr.Read()) { currentBranch = rdr[0].ToString(); } rdr.Close(); } if (currentBranch != Properties.Settings.Default.CurrentBranch) { // update remaining_stock to match current branch // request current branch stocks data from remote server dynamic param = new { currentBranch = Properties.Settings.Default.CurrentBranch }; RootProductObject Response = await ObjProductService.PostAPI("getBranchStock", param, _Path); if (Response.Status != "ok") { //API Error 1 MessageBox.Show("Failed to renew product stock, please contact IT support [AE1]", "UPO$$"); } else { ProductList = new ObservableCollection <Product>(Response.Data); if (ProductList.Count > 0) { // insert into products table foreach (var product in ProductList) { command.CommandText = "UPDATE products SET remaining_stock = " + product.Remaining_stock + " WHERE id = " + product.Id; command.ExecuteNonQuery(); } } // change current local db branch command.CommandText = "UPDATE users SET branch = @branch"; command.Parameters.AddWithValue("@branch", Properties.Settings.Default.CurrentBranch); command.Prepare(); command.ExecuteNonQuery(); } } } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "UPO$$"); } connection.Close(); } }
private async Task Search() { try { var currentPage = Pagination.CurrentPage; dynamic param = new { page = currentPage, productNo = InputProduct.Product_no, name = InputProduct.Name, category = InputProduct.Category, design_code = InputProduct.Design_code, colour_code = InputProduct.Colour_code, price = InputProduct.Price, barcode = InputProduct.Barcode, branchName = SelectedBranch == "All" ? null : SelectedBranch, is_active = SelectedStatus == "All" ? null : SelectedStatus }; RootProductObject Response = await ObjProductService.PostAPI("getProductList", param, _Path); if (Response.Status != "ok") { IsLoading = false; MessageBox.Show(Response.Msg, "UPO$$"); ProductList = null; QuantityList = null; Pagination = new Pagination { CurrentPage = 1, CurrentRecord = "0 - 0", TotalPage = 1, TotalRecord = 0 }; } else { //record section var totalRecord = Response.Total; var fromRecord = currentPage == 0 ? 1 : (currentPage * 70) - 69; var toRecord = currentPage == 0 ? totalRecord : (fromRecord + 69 < totalRecord ? fromRecord + 69 : totalRecord); //page section var totalPage = currentPage == 0 ? 1 : Convert.ToInt32(Math.Ceiling((double)totalRecord / 70)); Pagination = new Pagination { CurrentRecord = fromRecord.ToString() + " ~ " + toRecord.ToString(), TotalRecord = totalRecord, CurrentPage = currentPage == 0 ? 1 : currentPage, TotalPage = totalPage }; //datagrid ProductList = new ObservableCollection <Product>(Response.Data.OrderBy(property => property.Product_no)); for (int i = 0; i < ProductList.Count; i++) { ProductList[i].Id = i + 1; } } } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "UPO$$"); ProductList = null; QuantityList = null; Pagination = new Pagination { CurrentPage = 1, CurrentRecord = "0 - 0", TotalPage = 1, TotalRecord = 0 }; } RefreshTextBox(); }