// tot = old total
public static void UpdateItemBeli(SqlConnection conn, int idx)
{
var price = Utilities.GetInt(f.ItemBeliHargaBeliTextBox.Text);
var quantity = Utilities.GetInt(f.ItemBeliQtyTextBox.Text);
SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.Serializable);
List <ItemBeli> list = f.ItemBeliOLV.Objects.Cast <ItemBeli>().ToList();
ItemBeli item = list[idx];
int id = item.StockId;
int p = 0;
int q = 0;
int old = 0;
int oPrice = 0;
int stockQ = 0;
SqlConnectionStringBuilder connectionToLocal = new SqlConnectionStringBuilder();
connectionToLocal.ConnectionString = "Data Source=localhost;Initial Catalog=Vera;";
connectionToLocal.UserID = "sa";
connectionToLocal.Password = "******";
using (var connection = new SqlConnection(connectionToLocal.ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(QueryString.GetItemBeliTotal, connection);
command.Parameters.Add("@id", Int).Value = item.Id; // NEED ID LABEL HERE
var rec = command.ExecuteReader();
if (rec.Read())
{
p = rec.GetInt32(0);
q = rec.GetInt32(1);
old = p * q;
}
rec.Close();
oPrice = Stocks.GetStockPrice(connection, id);
stockQ = Stocks.GetStockQuantity(connection, id);
}
try
{
SqlCommand com = new SqlCommand(QueryString.UpdateItemBeli, conn);
com.Transaction = transaction;
com.Parameters.Add("@price", Int).Value = price;
com.Parameters.Add("@quantity", Int).Value = quantity;
com.Parameters.Add("@id", Int).Value = item.Id;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
int rowsAffected = com.ExecuteNonQuery();
com.CommandText = QueryString.UpdateStockQty;
Utilities.print(oPrice + " " + stockQ + " " + old + " " + price + " " + quantity + " " + p + " " + q);
com.Parameters.Add("@stockprice", BigInt).Value = ((oPrice * stockQ) - old + (price * quantity)) / (stockQ - q + quantity);
com.Parameters.Add("@qty", Int).Value = stockQ - q + quantity;
com.Parameters.Add("@stockid", Int).Value = id;
rowsAffected = com.ExecuteNonQuery();
if (rowsAffected != 1)
{
Utilities.print("Update ItemIndex({0}) failed", idx);
// throw Exception ?
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.");
}
}
return;
}
transaction.Commit();
item.Price = price;
item.Quantity = quantity;
list[idx] = item;
// update total faktur
var total = price * quantity;
var ppn = f.FakturBeliPPNCheckBox.Checked;
double oldT = Utilities.GetInt(f.FakturBeliTotalBeliTextBox.Text, true);
double oldTax = Utilities.GetInt(f.FakturBeliTotalPPNTextBox.Text, true);
double oldTotal = Utilities.GetInt(f.FakturBeliTotalAkhirTextBox.Text, true);
oldT = ppn ? (oldT - old) / 1.1 : oldT - old; // - oldItemTotal
oldTax = ppn ? oldTax - old * .1: 0; // - oldItemPPN
oldTotal = oldTotal - old; // - oldItemTotal ?
oldT += ppn ? total / 1.1 : total;
oldTax += ppn ? .1 * total : 0;
oldTotal += total;
f.FakturBeliTotalAkhirTextBox.Text = string.Format(Utilities.FormatRupiah, oldTotal);
f.FakturBeliTotalBeliTextBox.Text = string.Format(Utilities.FormatRupiah, oldT);
f.FakturBeliTotalPPNTextBox.Text = string.Format(Utilities.FormatRupiah, oldTax);
item.Price = price;
item.Quantity = quantity;
f.ItemBeliOLV.RefreshObject(item);
}
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.");
}
}
return;
}
}
// tot = old total
public static void UpdateItemJual(SqlConnection conn, int idx)
{
var sellingprice = Utilities.GetInt(f.ItemJualHargaJualTextBox.Text);
var quantity = Utilities.GetInt(f.ItemJualQtyTextBox.Text);
var baseprice = Utilities.GetInt(f.ItemJualHargaPokokTextBox.Text, true);
var discount = Utilities.GetInt(f.ItemJualDiscountTextBox.Text);
SqlCommand command = new SqlCommand(QueryString.GetItemJualTotal, conn);
SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.Serializable);
List <ItemJual> list = f.ItemJualOLV.Objects.Cast <ItemJual>().ToList();
ItemJual item = list[idx];
command.Parameters.Add("@id", Int).Value = item.Id; // NEED ID LABEL HERE
command.Transaction = transaction;
var old = (int)command.ExecuteScalar();
SqlCommand com = new SqlCommand(QueryString.GetItemJualQuantity, conn);
com.Parameters.Add("@id", Int).Value = item.Id;
com.Transaction = transaction;
int pre = (int)com.ExecuteScalar();
com.CommandText = QueryString.UpdateItemJual;
com.Parameters.Add("@sellingprice", Int).Value = sellingprice;
com.Parameters.Add("@quantity", Int).Value = quantity;
com.Parameters.Add("@itemid", Int).Value = item.Id;
com.Parameters.Add("@baseprice", Int).Value = baseprice;
com.Parameters.Add("@discount", Int).Value = discount;
com.Transaction = transaction;
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
int rowsAffected = com.ExecuteNonQuery();
if (rowsAffected != 1)
{
Utilities.print("Update ItemIndex({0}) failed", idx);
// throw Exception ?
return;
}
int q = Stocks.GetStockQuantity(conn, item.StockId, transaction);
Utilities.print("pre({0}), q({1}), quantity({2})", pre, q, quantity);
if (quantity > q + pre)
{
// FAIL
transaction.Rollback();
return;
}
com.CommandText = QueryString.UpdateStockQuantity;
com.Parameters.Add("@quantityStock", Int).Value = q + pre - quantity;
com.Parameters.Add("@stockid", Int).Value = item.StockId;
com.Transaction = transaction;
rowsAffected = com.ExecuteNonQuery();
if (rowsAffected != 1)
{
Console.WriteLine("Update Stock Quantity failed");
// throw Exception ?
}
transaction.Commit();
item.SellingPrice = sellingprice;
item.Quantity = quantity;
list[idx] = item;
// update total faktur
var total = sellingprice * quantity;
var ppn = f.FakturJualPPNCheckBox.Checked;
double oldT = Utilities.GetInt(f.FakturJualTotalJualTextBox.Text, true) / 100;
double oldTax = Utilities.GetInt(f.FakturJualTotalPPNTextBox.Text, true) / 100;
double oldTotal = Utilities.GetInt(f.FakturJualTotalAkhirTextBox.Text, true) / 100;
oldT = ppn ? (oldT - old) / 1.1 : oldT - old; // - oldItemTotal
oldTax = ppn ? oldTax - old * .1 : 0; // - oldItemPPN
oldTotal = oldTotal - old; // - oldItemTotal ?
oldT += ppn ? total / 1.1 : total;
oldTax += ppn ? .1 * total : 0;
oldTotal += total;
f.FakturJualTotalAkhirTextBox.Text = string.Format(Utilities.FormatRupiah, oldTotal);
f.FakturJualTotalJualTextBox.Text = string.Format(Utilities.FormatRupiah, oldT);
f.FakturJualTotalPPNTextBox.Text = string.Format(Utilities.FormatRupiah, oldTax);
item.SellingPrice = sellingprice;
item.Quantity = quantity;
item.Discount = discount;
f.ItemJualOLV.RefreshObject(item);
}
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 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.");
}
}
}
}
