public void Insert(BPPiutangModel model)
{
var sSql = @"
INSERT INTO
BPPiutang (
BPPiutangID, Tgl, Jam, PihakKeduaID,
Keterangan, NilaiPiutang, NilaiLunas)
VALUES (
@BPPiutangID, @Tgl, @Jam, @PihakKeduaID,
@Keterangan, @NilaiPiutang, @NilaiLunas ) ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BPPiutangID", model.BPPiutangID);
cmd.AddParam("@Tgl", model.Tgl.ToTglYMD());
cmd.AddParam("@Jam", model.Jam);
cmd.AddParam("@PihakKeduaID", model.PihakKeduaID);
cmd.AddParam("@Keterangan", model.Keterangan);
cmd.AddParam("@NilaiPiutang", model.NilaiPiutang);
cmd.AddParam("@NilaiLunas", model.NilaiLunas);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public void Update(BPPiutangModel model)
{
var sSql = @"
UPDATE
BPPiutang
SET
Tgl = @Tgl,
Jam = @Jam,
PihakKeduaID = @PihakKeduaID,
Keterangan = @Keterangan,
NilaiPiutang = @NilaiPiutang,
NilaiLunas = @NilaiLunas
WHERE
BPPiutangID = @BPPiutangID ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BPPiutangID", model.BPPiutangID);
cmd.AddParam("@Tgl", model.Tgl.ToTglYMD());
cmd.AddParam("@Jam", model.Jam);
cmd.AddParam("@PihakKeduaID", model.PihakKeduaID);
cmd.AddParam("@Keterangan", model.Keterangan);
cmd.AddParam("@NilaiPiutang", model.NilaiPiutang);
cmd.AddParam("@NilaiLunas", model.NilaiLunas);
conn.Open();
cmd.ExecuteNonQuery();
}
}
private BPPiutangModel BPPiutangFactory2()
{
var result = new BPPiutangModel
{
BPPiutangID = "A2",
Tgl = "02-01-2019",
Jam = "09:00:00",
PihakKeduaID = "B1",
PihakKeduaName = "C2",
Keterangan = "D2",
NilaiLunas = 1500,
NilaiPiutang = 1500,
ListLunas = new List <BPPiutangDetilModel>
{
new BPPiutangDetilModel
{
BPPiutangID = "A2",
BPPiutangDetilID = "E2",
ReffID = "F2",
Tgl = "01-01-2019",
Jam = "09:00:00",
Keterangan = "G2",
NilaiPiutang = 1500,
NilaiLunas = 1500
}
}
};
return(result);
}
private BPPiutangModel BPPiutangFactory1()
{
var result = new BPPiutangModel
{
BPPiutangID = "A1",
Tgl = "01-01-2019",
Jam = "09:00:00",
PihakKeduaID = "B1",
PihakKeduaName = "C1",
Keterangan = "D1",
NilaiLunas = 0,
NilaiPiutang = 25000,
ListLunas = new List <BPPiutangDetilModel>
{
new BPPiutangDetilModel
{
BPPiutangID = "A1",
BPPiutangDetilID = "E1",
ReffID = "F1",
Tgl = "01-01-2019",
Jam = "09:00:00",
Keterangan = "G1",
NilaiPiutang = 1000,
NilaiLunas = 0
}
}
};
return(result);
}
private BPPiutangModel BPPiutangDataFactory()
{
var result = new BPPiutangModel
{
BPPiutangID = "A1",
Tgl = "11-02-2019",
Jam = "01:38:00",
PihakKeduaID = "B1",
PihakKeduaName = "",
Keterangan = "C1",
NilaiPiutang = 300000,
NilaiLunas = 250000
};
return(result);
}
public IEnumerable <BPPiutangModel> ListData(string tgl1, string tgl2)
{
List <BPPiutangModel> result = null;
var sSql = @"
SELECT
aa.BPPiutangID, aa.Tgl, aa.Jam, aa.PihakKeduaID,
aa.Keterangan, aa.NilaiPiutang, aa.NilaiLunas,
ISNULL(bb.PihakKeduaName, '') PihakKeduaName
FROM
BPPiutang aa
LEFT JOIN PihakKedua bb ON aa.PihakKeduaID = bb.PihakKeduaID
WHERE
aa.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 <BPPiutangModel>();
while (dr.Read())
{
var item = new BPPiutangModel
{
BPPiutangID = dr["BPPiutangID"].ToString(),
Tgl = dr["Tgl"].ToString().ToTglDMY(),
Jam = dr["Jam"].ToString(),
PihakKeduaID = dr["PihakKeduaID"].ToString(),
PihakKeduaName = dr["PihakKeduaName"].ToString(),
Keterangan = dr["Keterangan"].ToString(),
NilaiPiutang = Convert.ToDecimal(dr["NilaiPiutang"]),
NilaiLunas = Convert.ToDecimal(dr["NilaiLunas"]),
};
result.Add(item);
}
}
}
return(result);
}
public BPPiutangModel GenPiutang(PenjualanModel penjualan)
{
if (penjualan == null)
{
throw new ArgumentNullException(nameof(penjualan));
}
if (!IsJualPiutang(penjualan)) return null;
var nilaiPiutang = GetNilaiPiutangJual(penjualan);
// header piutang
var bpPiutang = new BPPiutangModel
{
BPPiutangID = penjualan.PenjualanID,
Tgl = penjualan.TglPenjualan,
Jam = penjualan.JamPenjualan,
Keterangan = "Penjualan",
PihakKeduaID = penjualan.CustomerID,
PihakKeduaName = penjualan.CustomerName,
NilaiPiutang = nilaiPiutang,
NilaiLunas = 0
};
// detil piutang
var resultDetil = new BPPiutangDetilModel
{
BPPiutangID = penjualan.PenjualanID,
BPPiutangDetilID = penjualan.PenjualanID + '-' + "01",
Tgl = penjualan.TglPenjualan,
Jam = penjualan.JamPenjualan,
Keterangan = "Piutang Jual",
ReffID = penjualan.PenjualanID,
NilaiPiutang = nilaiPiutang,
NilaiLunas = 0
};
var listDetil = new List<BPPiutangDetilModel>
{
resultDetil
};
bpPiutang.ListLunas = listDetil;
var result = Save(bpPiutang);
return result;
}
public BPPiutangModel GetData(string id)
{
BPPiutangModel result = null;
var sSql = @"
SELECT
aa.BPPiutangID, aa.Tgl, aa.Jam, aa.PihakKeduaID,
aa.Keterangan, aa.NilaiPiutang, aa.NilaiLunas,
ISNULL(bb.PihakKeduaName, '') PihakKeduaName
FROM
BPPiutang aa
LEFT JOIN PihakKedua bb ON aa.PihakKeduaID = bb.PihakKeduaID
WHERE
aa.BPPiutangID = @BPPiutangID ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BPPiutangID", id);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows)
{
return(null);
}
dr.Read();
result = new BPPiutangModel
{
BPPiutangID = dr["BPPiutangID"].ToString(),
Tgl = dr["Tgl"].ToString().ToTglDMY(),
Jam = dr["Jam"].ToString(),
PihakKeduaID = dr["PihakKeduaID"].ToString(),
PihakKeduaName = dr["PihakKeduaName"].ToString(),
Keterangan = dr["Keterangan"].ToString(),
NilaiPiutang = Convert.ToDecimal(dr["NilaiPiutang"]),
NilaiLunas = Convert.ToDecimal(dr["NilaiLunas"]),
};
}
}
return(result);
}
private BPPiutangModel Save(BPPiutangModel model)
{
if (model == null)
{
throw new ArgumentNullException(nameof(model));
}
// validate pihak kedua;
var pihakKedua = _pihakKeduaBL.GetData(model.PihakKeduaID);
if (pihakKedua == null)
throw new ArgumentException("PihakKeduaID invalid");
else
model.PihakKeduaName = pihakKedua.PihakKeduaName;
// kasus simpan ulang; pastikan belum ada pelunasan
var bpPiutang = GetData(model.BPPiutangID);
if(bpPiutang != null)
{
//if (bpPiutang.NilaiLunas > 0)
//throw new ArgumentException("Piutang sudah ada pelunasan, tidak bisa simpan ulang");
}
// update nilai total di header
model.NilaiPiutang = model.ListLunas.Sum(x => x.NilaiPiutang);
model.NilaiLunas = model.ListLunas.Sum(x => x.NilaiLunas);
// delete data lama
using (var trans = TransHelper.NewScope())
{
_bpPiutangDetilDal.Delete(model.BPPiutangID);
_bpPiutangDal.Delete(model.BPPiutangID);
_bpPiutangDal.Insert(model);
foreach (var item in model.ListLunas)
_bpPiutangDetilDal.Insert(item);
trans.Complete();
}
return model;
}
