public IEnumerable <BPPurchaseReceiptModel> ListData(string tgl1, string tgl2)
{
List <BPPurchaseReceiptModel> result = null;
var sSql = @"
SELECT
aa.BPPurchaseID, aa.BPReceiptID, aa.BPDetilID,
aa.NoUrut, aa.Tgl, aa.Jam, aa.Keterangan, aa.BrgID,
aa.QtyPurchase, aa.QtyReceipt, aa.Harga, aa.Diskon,
aa.Tax, aa.SubTotal
FROM
BPPurchaseReceipt aa
WHERE
aa.Tgl BETWEEN @Tgl1 AND @Tgl2
ORDER BY
BPPurchaseID, Tgl, Jam ";
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 <BPPurchaseReceiptModel>();
while (dr.Read())
{
var item = new BPPurchaseReceiptModel
{
BPPurchaseID = dr["BPPurchaseID"].ToString(),
BPReceiptID = dr["BPReceiptID"].ToString(),
BPDetilID = dr["BPDetilID"].ToString(),
NoUrut = Convert.ToInt16(dr["NoUrut"]),
Tgl = dr["Tgl"].ToString().ToTglDMY(),
Jam = dr["Jam"].ToString(),
Keterangan = dr["Keterangan"].ToString(),
BrgID = dr["BrgID"].ToString(),
QtyPurchase = Convert.ToInt32(dr["QtyPurchase"]),
QtyReceipt = Convert.ToInt32(dr["QtyReceipt"]),
Harga = Convert.ToDecimal(dr["Harga"]),
Diskon = Convert.ToDecimal(dr["Diskon"]),
Tax = Convert.ToDecimal(dr["Tax"]),
SubTotal = Convert.ToDecimal(dr["SubTotal"])
};
result.Add(item);
}
}
}
return(result);
}
public IEnumerable <BPPurchaseReceiptModel> ListData(string bpPurchaseID)
{
List <BPPurchaseReceiptModel> result = null;
var sSql = @"
SELECT
BPPurchaseID, BPReceiptID, BPDetilID,
NoUrut, Tgl, Jam, Keterangan, BrgID,
QtyPurchase, QtyReceipt, Harga, Diskon,
Tax, SubTotal
FROM
BPPurchaseReceipt
WHERE
BPPurchaseID = @BPPurchaseID ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BPPurchaseID", bpPurchaseID);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows)
{
return(null);
}
result = new List <BPPurchaseReceiptModel>();
while (dr.Read())
{
var item = new BPPurchaseReceiptModel
{
BPPurchaseID = dr["BPPurchaseID"].ToString(),
BPReceiptID = dr["BPReceiptID"].ToString(),
BPDetilID = dr["BPDetilID"].ToString(),
NoUrut = Convert.ToInt16(dr["NoUrut"]),
Tgl = dr["Tgl"].ToString().ToTglDMY(),
Jam = dr["Jam"].ToString(),
Keterangan = dr["Keterangan"].ToString(),
BrgID = dr["BrgID"].ToString(),
QtyPurchase = Convert.ToInt32(dr["QtyPurchase"]),
QtyReceipt = Convert.ToInt32(dr["QtyReceipt"]),
Harga = Convert.ToDecimal(dr["Harga"]),
Diskon = Convert.ToDecimal(dr["Diskon"]),
Tax = Convert.ToDecimal(dr["Tax"]),
SubTotal = Convert.ToDecimal(dr["SubTotal"])
};
result.Add(item);
}
}
}
return(result);
}
private BPPurchaseReceiptModel BPPurchaseReceiptDataFactory()
{
var result = new BPPurchaseReceiptModel
{
BPPurchaseID = "A1",
BPReceiptID = "B1",
BPDetilID = "C1",
Tgl = "23-02-2019",
Jam = "08:00:21",
Keterangan = "",
BrgID = "D1",
QtyPurchase = 12,
QtyReceipt = 31,
Harga = 33,
Diskon = 200,
Tax = 100,
SubTotal = 300,
};
return(result);
}
public void Insert(BPPurchaseReceiptModel model)
{
var sSql = @"
INSERT INTO
BPPurchaseReceipt (
BPPurchaseID, BPReceiptID, BPDetilID,
NoUrut, Tgl, Jam, Keterangan, BrgID,
QtyPurchase, QtyReceipt, Harga, Diskon,
Tax, SubTotal)
VALUES (
@BPPurchaseID, @BPReceiptID, @BPDetilID,
@NoUrut, @Tgl, @Jam, @Keterangan, @BrgID,
@QtyPurchase, @QtyReceipt, @Harga, @Diskon,
@Tax, @SubTotal) ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BPPurchaseID", model.BPPurchaseID);
cmd.AddParam("@BPReceiptID", model.BPReceiptID);
cmd.AddParam("@BPDetilID", model.BPDetilID);
cmd.AddParam("@NoUrut", model.NoUrut);
cmd.AddParam("@Tgl", model.Tgl.ToTglYMD());
cmd.AddParam("@Jam", model.Jam);
cmd.AddParam("@Keterangan", model.Keterangan);
cmd.AddParam("@BrgID", model.BrgID);
cmd.AddParam("@QtyPurchase", model.QtyPurchase);
cmd.AddParam("@QtyReceipt", model.QtyReceipt);
cmd.AddParam("@Harga", model.Harga);
cmd.AddParam("@Diskon", model.Diskon);
cmd.AddParam("@Tax", model.Tax);
cmd.AddParam("@SubTotal", model.SubTotal);
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 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);
}
