public static void InsertItemBeli(SqlConnection conn) { var price = Utilities.GetInt(f.ItemBeliHargaBeliTextBox.Text); var quantity = Utilities.GetInt(f.ItemBeliQtyTextBox.Text); var date = Utilities.GetDateNow(); var fid = Utilities.GetNoFaktur(f.FakturBeliNoFakturTextBox.Text); var sid = Stocks.GetStockIdFromStockCode(conn, f.ItemBeliStokIDComboBox.Text); var ppn = f.FakturBeliPPNCheckBox.Checked; //Get Quantity Before SqlCommand getQtyCount = new SqlCommand(QueryString.GetStockQty, conn); getQtyCount.Parameters.Add("@id", Int).Value = sid; int QtyBefore = (int)getQtyCount.ExecuteScalar(); //Get Harga Before SqlCommand getPriceBefore = new SqlCommand(QueryString.GetStockPrice, conn); getPriceBefore.Parameters.Add("@id", Int).Value = sid; int PriceBefore = (int)getPriceBefore.ExecuteScalar(); // Transaction SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.Serializable); SqlCommand com = new SqlCommand(QueryString.InsertItemBeli, conn); com.Parameters.Add("@date", Date).Value = date; com.Parameters.Add("@fid", Int).Value = fid; com.Parameters.Add("@sid", Int).Value = sid; // Get Id from Code com.Parameters.Add("@price", Int).Value = price; com.Parameters.Add("@quantity", Int).Value = quantity; com.Parameters.Add("@extra", VarChar).Value = ""; com.Parameters.Add("@del", Bit).Value = 0; com.Transaction = transaction; //Update Quantity Before + A new quantity SqlCommand updateItemQty = new SqlCommand(QueryString.UpdateStockQty, conn); int NewQty = QtyBefore + quantity; Console.WriteLine(PriceBefore + " " + QtyBefore + " " + price + " " + quantity + " " + NewQty); updateItemQty.Parameters.Add("@stockprice", Int).Value = ((PriceBefore * QtyBefore) + (price * quantity)) / NewQty; updateItemQty.Parameters.Add("@qty", Int).Value = NewQty; updateItemQty.Parameters.Add("@stockid", Int).Value = sid; try { if (conn.State != ConnectionState.Open) { conn.Open(); } int rowsAffected = com.ExecuteNonQuery(); if (rowsAffected != 1) { Console.WriteLine("Insert ItemBeli failed"); // throw Exception ? } updateItemQty.Transaction = transaction; rowsAffected = updateItemQty.ExecuteNonQuery(); if (rowsAffected != 1) { Console.WriteLine("Update Stock Qty failed"); // throw Exception ? } transaction.Commit(); var t = ppn ? price * quantity / 1.1 : price * quantity; var tax = ppn ? .1 * price * quantity : 0; var tt = t + tax; double total = Utilities.GetInt(f.FakturBeliTotalBeliTextBox.Text, true); double taxx = Utilities.GetInt(f.FakturBeliTotalPPNTextBox.Text, true); tt += total + taxx; f.FakturBeliTotalBeliTextBox.Text = String.Format(Utilities.FormatRupiah, total + t); f.FakturBeliTotalPPNTextBox.Text = String.Format(Utilities.FormatRupiah, tax + taxx); f.FakturBeliTotalAkhirTextBox.Text = string.Format(Utilities.FormatRupiah, tt); // Add to itembeliolv ? } catch (Exception e) { Console.WriteLine("Error :" + e.Message); try { transaction.Rollback(); } catch (SqlException ex) { if (transaction.Connection != null) { Console.WriteLine("An exception of type " + ex.GetType() + " was encountered while attempting to roll back the transaction."); } } } }
public static void InsertItemJual(SqlConnection conn) { var price = Utilities.GetInt(f.ItemJualHargaJualTextBox.Text); var quantity = Utilities.GetInt(f.ItemJualQtyTextBox.Text); var baseprice = Utilities.GetInt(f.ItemJualHargaPokokTextBox.Text, true); var date = Utilities.GetDateNow(); var fid = Utilities.GetNoFaktur(f.FakturJualNoFakturTextBox.Text); var sid = Stocks.GetStockIdFromStockCode(conn, f.ItemJualStokIDComboBox.Text); var ppn = f.FakturJualPPNCheckBox.Checked; int oldQ = Stocks.GetStockQuantity(conn, sid); var discount = Utilities.GetInt(f.ItemJualDiscountTextBox.Text); SqlCommand com = new SqlCommand(QueryString.InsertItemJual, conn); SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.Serializable); com.Transaction = transaction; com.Parameters.Add("@date", Date).Value = date; com.Parameters.Add("@fid", Int).Value = fid; com.Parameters.Add("@sid", Int).Value = sid; // Get Id from Code com.Parameters.Add("@price", Int).Value = price; com.Parameters.Add("@quantity", Int).Value = quantity; com.Parameters.Add("@baseprice", Int).Value = baseprice; com.Parameters.Add("@extra", VarChar).Value = ""; com.Parameters.Add("@del", Bit).Value = 0; com.Parameters.Add("@discount", Int).Value = discount; try { if (conn.State != ConnectionState.Open) { conn.Open(); } int rowsAffected = com.ExecuteNonQuery(); if (rowsAffected != 1) { Console.WriteLine("Insert ItemJual failed"); // throw Exception ? } Utilities.print("ItemJual inserted"); if (quantity > oldQ) { // FAIL MessageBox.Show("Stok Tidak Mencukupi"); transaction.Rollback(); return; } com.CommandText = QueryString.UpdateStockQuantity; com.Parameters.Add("@quantityStock", Int).Value = oldQ - quantity; com.Parameters.Add("@stockid", Int).Value = sid; rowsAffected = com.ExecuteNonQuery(); if (rowsAffected != 1) { Console.WriteLine("Update Stock Quantity failed"); // throw Exception ? } transaction.Commit(); var t = ppn ? price * quantity / 1.1 : price * quantity; var tax = ppn ? .1 * price * quantity : 0; var tt = t + tax; double total = Utilities.GetInt(f.FakturJualTotalJualTextBox.Text, true) / 100; double taxx = Utilities.GetInt(f.FakturJualTotalPPNTextBox.Text, true) / 100; tt += total + taxx; f.FakturJualTotalJualTextBox.Text = String.Format(Utilities.FormatRupiah, total + t); f.FakturJualTotalPPNTextBox.Text = String.Format(Utilities.FormatRupiah, tax + taxx); f.FakturJualTotalAkhirTextBox.Text = string.Format(Utilities.FormatRupiah, tt); // Add to itemjualolv ? } catch (Exception e) { Console.WriteLine("Error here:" + e.Message); try { transaction.Rollback(); } catch (SqlException ex) { if (transaction.Connection != null) { Console.WriteLine("An exception of type " + ex.GetType() + " was encountered while attempting to roll back the transaction."); } } } }