public void Update(BPStokModel model)
{
var sSql = @"
UPDATE
BPStok
SET
ReffID = @ReffID,
Tgl = @Tgl,
StokControl = @StokControl,
Jam = @Jam,
BrgID = @BrgID,
NilaiHpp = @NilaiHpp,
QtyIn = @QtyIn,
QtySisa = @QtySisa
WHERE
BPStokID = @BPStokID ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BPStokID", model.BPStokID);
cmd.AddParam("@ReffID", model.ReffID);
cmd.AddParam("@StokControl", model.StokControl);
cmd.AddParam("@Tgl", model.Tgl.ToTglYMD());
cmd.AddParam("@Jam", model.Jam);
cmd.AddParam("@BrgID", model.BrgID);
cmd.AddParam("@NilaiHpp", model.NilaiHpp);
cmd.AddParam("@QtyIn", model.QtyIn);
cmd.AddParam("@QtySisa", model.QtySisa);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public void Insert(BPStokModel model)
{
var sSql = @"
INSERT INTO
BPStok (
BPStokID, ReffID, StokControl,
Tgl, Jam, BrgID,
NilaiHpp, QtyIn, QtySisa )
VALUES (
@BPStokID, @ReffID, @StokControl,
@Tgl, @Jam, @BrgID,
@NilaiHpp, @QtyIn, @QtySisa ) ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BPStokID", model.BPStokID);
cmd.AddParam("@ReffID", model.ReffID);
cmd.AddParam("@StokControl", model.StokControl);
cmd.AddParam("@Tgl", model.Tgl.ToTglYMD());
cmd.AddParam("@Jam", model.Jam);
cmd.AddParam("@BrgID", model.BrgID);
cmd.AddParam("@NilaiHpp", model.NilaiHpp);
cmd.AddParam("@QtyIn", model.QtyIn);
cmd.AddParam("@QtySisa", model.QtySisa);
conn.Open();
cmd.ExecuteNonQuery();
}
}
private BPStokModel AddStok(StokItem stokItem)
{
// null check
if (stokItem == null)
{
throw new ArgumentNullException(nameof(stokItem));
}
var result = new BPStokModel
{
BPStokID = stokItem.ReffID + stokItem.BrgID,
ReffID = stokItem.ReffID,
StokControl = stokItem.BPStokID,
Tgl = stokItem.Tgl,
Jam = stokItem.Jam,
BrgID = stokItem.BrgID,
BrgName = stokItem.BrgName,
QtyIn = stokItem.QtyIn,
NilaiHpp = stokItem.NilaiHpp,
QtySisa = stokItem.QtyOut,
};
var listDetil = new List <BPStokDetilModel>
{
new BPStokDetilModel
{
BPStokID = result.BPStokID,
BPStokDetilID = result.BPStokID + "-001",
NoUrut = 1,
ReffID = stokItem.ReffID,
Tgl = stokItem.Tgl,
Jam = stokItem.Jam,
QtyIn = stokItem.QtyIn,
NilaiHpp = stokItem.NilaiHpp,
QtyOut = 0,
HargaJual = 0,
}
};
result.ListDetil = listDetil;
result.QtySisa = result.QtyIn - listDetil.Sum(x => x.QtyOut);
// hapus data lama
_bpStokDetilDal.Delete(result.BPStokID);
_bpStokDal.Delete(result.BPStokID);
// insert data baru
_bpStokDal.Insert(result);
foreach (var item in listDetil)
{
_bpStokDetilDal.Insert(item);
}
return(result);
}
public IEnumerable <BPStokModel> ListData(string brgID, string tgl1, string tgl2)
{
List <BPStokModel> result = null;
var sSql = @"
SELECT
aa.BPStokID, aa.ReffID, aa.StokControl,
aa.Tgl, aa.Jam, aa.BrgID,
aa.NilaiHpp, aa.QtyIn, aa.QtySisa,
ISNULL(bb.BrgName, '') BrgName
FROM
BPStok aa
LEFT JOIN Brg bb ON aa.BrgID = bb.BrgID
WHERE
aa.BrgID = @BrgID
AND aa.Tgl BETWEEN @Tgl1 AND @Tgl2 ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BrgID", brgID);
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 <BPStokModel>();
while (dr.Read())
{
var item = new BPStokModel
{
BPStokID = dr["BPStokID"].ToString(),
ReffID = dr["ReffID"].ToString(),
StokControl = dr["StokControl"].ToString(),
Tgl = dr["Tgl"].ToString().ToTglDMY(),
Jam = dr["Jam"].ToString(),
BrgID = dr["BrgID"].ToString(),
BrgName = dr["BrgName"].ToString(),
NilaiHpp = Convert.ToDecimal(dr["NilaiHpp"]),
QtyIn = Convert.ToInt64(dr["QtyIn"]),
QtySisa = Convert.ToInt64(dr["QtySisa"])
};
result.Add(item);
}
}
}
return(result);
}
private BPStokModel BPStokHeaderDataFactory()
{
var result = new BPStokModel
{
BPStokID = "A1B1",
BrgID = "B1",
ReffID = "A1",
Tgl = "20-02-2019",
Jam = "21:39:00",
NilaiHpp = 2000,
QtyIn = 5,
QtySisa = 5,
};
return(result);
}
private BPStokModel BPStokDataFactory()
{
var result = new BPStokModel
{
BPStokID = "A1",
ReffID = "B1",
Tgl = "20-02-2019",
Jam = "20:15:00",
BrgID = "C1",
BrgName = "",
NilaiHpp = 12,
QtyIn = 13,
QtySisa = 14
};
return(result);
}
public BPStokModel GetData(string bpStokID)
{
BPStokModel result = null;
var sSql = @"
SELECT
aa.BPStokID, aa.ReffID, aa.StokControl,
aa.Tgl, aa.Jam, aa.BrgID,
aa.NilaiHpp, aa.QtyIn, aa.QtySisa,
ISNULL(bb.BrgName, '') BrgName
FROM
BPStok aa
LEFT JOIN Brg bb ON aa.BrgID = bb.BrgID
WHERE
BPStokID = @BPStokID ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BPStokID", bpStokID);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows)
{
return(null);
}
dr.Read();
result = new BPStokModel
{
BPStokID = bpStokID,
ReffID = dr["ReffID"].ToString(),
StokControl = dr["StokControl"].ToString(),
Tgl = dr["Tgl"].ToString().ToTglDMY(),
Jam = dr["Jam"].ToString(),
BrgID = dr["BrgID"].ToString(),
BrgName = dr["BrgName"].ToString(),
NilaiHpp = Convert.ToDecimal(dr["NilaiHpp"]),
QtyIn = Convert.ToInt64(dr["QtyIn"]),
QtySisa = Convert.ToInt64(dr["QtySisa"])
};
}
}
return(result);
}
