public void Insert(BPPurchaseModel model)
{
var sSql = @"
INSERT INTO
BPPurchase (
BPPurchaseID, Tgl, Jam, SupplierID, Keterangan,
TotHargaPurchase, TotHargaReceipt, Diskon,
BiayaLain, GrandTotal, IsClosed)
VALUES (
@BPPurchaseID, @Tgl, @Jam, @SupplierID, @Keterangan,
@TotHargaPurchase, @TotHargaReceipt, @Diskon,
@BiayaLain, @GrandTotal, @IsClosed) ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BPPurchaseID", model.BPPurchaseID);
cmd.AddParam("@Tgl", model.Tgl.ToTglYMD());
cmd.AddParam("@Jam", model.Jam);
cmd.AddParam("@SupplierID", model.SupplierID);
cmd.AddParam("@Keterangan", model.Keterangan);
cmd.AddParam("@TotHargaPurchase", model.TotHargaPurchase);
cmd.AddParam("@TotHargaReceipt", model.TotHargaReceipt);
cmd.AddParam("@Diskon", model.Diskon);
cmd.AddParam("@BiayaLain", model.BiayaLain);
cmd.AddParam("@GrandTotal", model.GrandTotal);
cmd.AddParam("@IsClosed", model.IsClosed);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public IEnumerable <BPPurchaseModel> ListData()
{
List <BPPurchaseModel> result = null;
var sSql = @"
SELECT
aa.BPPurchaseID, aa.Tgl, aa.Jam, aa.SupplierID, aa.Keterangan,
aa.TotHargaPurchase, aa.TotHargaReceipt, aa.Diskon,
aa.BiayaLain, aa.GrandTotal, aa.IsClosed,
ISNULL(bb.SupplierName, '') SupplierName
FROM
BPPurchase aa
LEFT JOIN Supplier bb ON aa.SupplierID = bb.SupplierID
WHERE
TotHargaReceipt <> TotHargaPurchase ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows)
{
return(null);
}
result = new List <BPPurchaseModel>();
while (dr.Read())
{
var item = new BPPurchaseModel
{
BPPurchaseID = dr["BPPurchaseID"].ToString(),
Tgl = dr["Tgl"].ToString().ToTglDMY(),
Jam = dr["Jam"].ToString(),
SupplierID = dr["SupplierID"].ToString(),
SupplierName = dr["SupplierName"].ToString(),
Keterangan = dr["Keterangan"].ToString(),
TotHargaPurchase = Convert.ToDecimal(dr["TotHargaPurchase"]),
TotHargaReceipt = Convert.ToDecimal(dr["TotHargaReceipt"]),
Diskon = Convert.ToDecimal(dr["Diskon"]),
BiayaLain = Convert.ToDecimal(dr["BiayaLain"]),
GrandTotal = Convert.ToDecimal(dr["GrandTotal"]),
IsClosed = Convert.ToBoolean(dr["IsClosed"])
};
result.Add(item);
}
}
}
return(result);
}
public IEnumerable <BPPurchaseModel> ListData(string tgl1, string tgl2)
{
List <BPPurchaseModel> result = null;
var sSql = @"
SELECT
BPPurchaseID, Tgl, Jam, SupplierID, Keterangan,
TotHargaPurchase, TotHargaReceipt, Diskon,
BiayaLain, GrandTotal, IsClosed,
FROM
BPPurchase
WHERE
Tgl BETWEEN @Tgl1 AND @Tgl2 ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@Tgl1", tgl1.ToTglYMD());
cmd.AddParam("@Tgl2", tgl2.ToTglYMD());
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows)
{
return(null);
}
result = new List <BPPurchaseModel>();
while (dr.Read())
{
var item = new BPPurchaseModel
{
BPPurchaseID = dr["BPPurchaseID"].ToString(),
Tgl = dr["Tgl"].ToString().ToTglDMY(),
Jam = dr["Jam"].ToString(),
SupplierID = dr["SupplierID"].ToString(),
Keterangan = dr["Keterangan"].ToString(),
TotHargaPurchase = Convert.ToDecimal(dr["TotHargaPurchase"]),
TotHargaReceipt = Convert.ToDecimal(dr["TotHargaReceipt"]),
Diskon = Convert.ToDecimal(dr["Diskon"]),
BiayaLain = Convert.ToDecimal(dr["BiayaLain"]),
GrandTotal = Convert.ToDecimal(dr["GrandTotal"]),
IsClosed = Convert.ToBoolean(dr["IsClosed"])
};
result.Add(item);
}
}
}
return(result);
}
private BPPurchaseModel BPPurchaseDataFactory()
{
var result = new BPPurchaseModel
{
BPPurchaseID = "A1",
Tgl = "23-02-2019",
Jam = "08:00:21",
SupplierID = "B1",
Keterangan = "",
TotHargaPurchase = 12,
TotHargaReceipt = 31,
Diskon = 200,
BiayaLain = 100,
GrandTotal = 300,
};
return(result);
}
public void Update(BPPurchaseModel model)
{
var sSql = @"
UPDATE
BPPurchase
SET
BPPurchaseID = @BPPurchaseID,
Tgl = @Tgl,
Jam = @Jam,
SupplierID = @SupplierID,
Keterangan = @Keterangan,
TotHargaPurchase = @TotHargaPurchase,
TotHargaReceipt = @TotHargaReceipt,
Diskon = @Diskon,
BiayaLain = @BiayaLain,
GrandTotal = @GrandTotal,
IsClosed = @IsClosed
WHERE
BPPurchaseID = @BPPurchaseID ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BPPurchaseID", model.BPPurchaseID);
cmd.AddParam("@Tgl", model.Tgl.ToTglYMD());
cmd.AddParam("@Jam", model.Jam);
cmd.AddParam("@SupplierID", model.SupplierID);
cmd.AddParam("@Keterangan", model.Keterangan);
cmd.AddParam("@TotHargaPurchase", model.TotHargaPurchase);
cmd.AddParam("@TotHargaReceipt", model.TotHargaReceipt);
cmd.AddParam("@Diskon", model.Diskon);
cmd.AddParam("@BiayaLain", model.BiayaLain);
cmd.AddParam("@GrandTotal", model.GrandTotal);
cmd.AddParam("@IsClosed", model.IsClosed);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public BPPurchaseModel Generate(PurchaseModel purchase)
{
if (purchase == null)
{
throw new ArgumentNullException(nameof(purchase));
}
// ambil data purchase yang sudah ada
BPPurchaseModel newPurchase = _bpPurchaseDal.GetData(purchase.PurchaseID);
if (newPurchase == null)
{
newPurchase = new BPPurchaseModel();
}
// header
newPurchase.BPPurchaseID = purchase.PurchaseID;
newPurchase.Tgl = purchase.Tgl;
newPurchase.Jam = purchase.Jam;
newPurchase.SupplierID = purchase.SupplierID;
newPurchase.SupplierName = purchase.SupplierName;
newPurchase.Keterangan = purchase.Keterangan;
newPurchase.Diskon = purchase.Diskon;
newPurchase.BiayaLain = purchase.BiayaLain;
newPurchase.IsClosed = purchase.IsClosed;
// detil; hilangkan detil Purchase-nya
if (newPurchase.ListBrg != null)
{
newPurchase.ListBrg = newPurchase.ListBrg.Where(x => x.BPReceiptID.Trim() != "").ToList();
}
// tambahkan detil purchase baru;
foreach (var item in purchase.ListBrg)
{
var newItem = new BPPurchaseReceiptModel()
{
BPPurchaseID = purchase.PurchaseID,
BPReceiptID = "",
BPDetilID = item.PurchaseDetilID,
Tgl = purchase.Tgl,
Jam = purchase.Jam,
BrgID = item.BrgID,
QtyPurchase = item.Qty,
QtyReceipt = 0,
Harga = item.Harga,
Diskon = item.Diskon,
Tax = item.TaxRupiah,
};
if (newPurchase.ListBrg == null)
{
newPurchase.ListBrg = new List <BPPurchaseReceiptModel>();
}
newPurchase.ListBrg.Add(newItem);
}
var result = Save(newPurchase);
return(result);
}
public void ClosePO(BPPurchaseModel bpPurchase)
{
bpPurchase.IsClosed = true;
_bpPurchaseDal.Update(bpPurchase);
}
private BPPurchaseModel Save(BPPurchaseModel model)
{
if (model == null)
{
throw new ArgumentNullException(nameof(model));
}
// validate supplier
var supplier = _supplierDal.GetData(model.SupplierID);
if (supplier == null)
{
throw new ArgumentException("SupplierID invalie");
}
else
{
model.SupplierName = supplier.SupplierName;
}
// update nilai Total Harga dari detil
model.TotHargaPurchase = model.ListBrg.Sum(x => x.QtyPurchase * (x.Harga - x.Diskon + x.Tax));
model.TotHargaReceipt = model.ListBrg.Sum(x => x.QtyReceipt * (x.Harga - x.Diskon + x.Tax));
// validasi; penerimaan tidak boleh melebihi purchasing
if (model.TotHargaReceipt > model.TotHargaPurchase)
{
throw new ArgumentException("Penerimaan melebihi pembelian");
}
// update subtotal di detil
foreach (var item in model.ListBrg)
{
item.SubTotal =
(item.QtyPurchase - item.QtyReceipt) *
(item.Harga - item.Diskon + item.Tax);
}
// update keterangan di detil
foreach (var item in model.ListBrg)
{
var brg = _brgDal.GetData(item.BrgID);
if (brg == null)
{
throw new ArgumentException("BrgID invalid");
}
if (item.BPReceiptID.Trim() == "")
{
item.Keterangan = string.Format("Purchase {0}", brg.BrgName);
}
else
{
item.Keterangan = string.Format(" Received {0}", brg.BrgName);
}
}
// atur urutannya sedemikian rupa kelihatan history penerimaan barang
var listData = model.ListBrg.OrderBy(x => x.BrgID + x.Tgl + x.Jam);
int i = 1;
foreach (var item in listData)
{
item.NoUrut = i;
i++;
}
model.ListBrg = listData.ToList();
// update kode Purchase di Detil harus sama dengan header
foreach (var item in model.ListBrg)
{
item.BPPurchaseID = model.BPPurchaseID;
}
if (model.TotHargaPurchase == model.TotHargaReceipt)
{
model.IsClosed = true;
}
// save
using (var trans = TransHelper.NewScope())
{
// hapus data lama
_bpPurchaseReceiptDal.Delete(model.BPPurchaseID);
_bpPurchaseDal.Delete(model.BPPurchaseID);
// insert data baru
_bpPurchaseDal.Insert(model);
foreach (var item in model.ListBrg)
{
_bpPurchaseReceiptDal.Insert(item);
}
trans.Complete();
}
return(model);
}
public BPPurchaseModel Generate(ReceiptModel receipt)
{
if (receipt == null)
{
throw new ArgumentNullException(nameof(receipt));
}
#region UPDATE-PURCHASE-LAMA (KASUS EDIT)
// cari Purchase atas receipt ini (jika ada sebelumnya)
BPPurchaseModel bpPurchaseLama = null;
var listPurchase = _bpPurchaseReceiptDal.ListData(receipt);
// hilangkan receipt ini dari purchase tsb
if (listPurchase != null)
{
var bpPurchasIDLama = listPurchase.First().BPPurchaseID;
bpPurchaseLama = GetData(bpPurchasIDLama);
bpPurchaseLama.ListBrg = bpPurchaseLama.ListBrg
.Where(x => x.BPReceiptID != receipt.ReceiptID)
.ToList();
}
#endregion
// update pruchase baru
BPPurchaseModel bpPurchaseBaru = null;
bpPurchaseBaru = GetData(receipt.PurchaseID);
if (bpPurchaseBaru == null)
{
throw new ArgumentException("Purchase Invalid");
}
// hilangka receipt ini dari purchase (jika ada / kasus edit)
// sebelum ditambahkan kembali
bpPurchaseBaru.ListBrg = bpPurchaseBaru.ListBrg
.Where(x => x.BPReceiptID != receipt.ReceiptID).ToList();
// tambahkan detil baru
foreach (var item in receipt.ListBrg)
{
var newItem = new BPPurchaseReceiptModel()
{
BPReceiptID = receipt.ReceiptID,
BPDetilID = item.ReceiptDetilID,
Tgl = receipt.Tgl,
Jam = receipt.Jam,
BrgID = item.BrgID,
Harga = item.Harga,
Diskon = item.Diskon,
Tax = item.TaxRupiah,
SubTotal = item.SubTotal,
QtyPurchase = 0,
QtyReceipt = item.Qty,
BPPurchaseID = receipt.PurchaseID,
};
bpPurchaseBaru.ListBrg.Add(newItem);
}
// simpan data
BPPurchaseModel result = null;
BPPurchaseModel resultLama = null;
using (var trans = TransHelper.NewScope())
{
if (bpPurchaseLama != null)
{
resultLama = Save(bpPurchaseLama);
}
result = Save(bpPurchaseBaru);
trans.Complete();
}
return(result);
}
