public IEnumerable <StokInOutModel> ListData(string stokInID)
{
List <StokInOutModel> result = null;
var sSql = @"
SELECT
aa.StokInID, aa.StokInOutID, aa.TglTrs, aa.JamTrs,
aa.ReffTrsID, aa.JenisMutasiID,
aa.BrgID, aa.StokControlID,
aa.QtyIn, aa.QtyOut, aa.Hpp, aa.HargaJual,
ISNULL(bb.BrgName, '') BrgName,
ISNULL(cc.JenisMutasiName, '') JenisMutasiName
FROM
StokInOut aa
LEFT JOIN Brg bb ON aa.BrgId = bb.BrgID
LEFT JOIN JenisMutasi cc ON aa.JenisMutasiID = cc.JenisMutasiID
WHERE
StokInID = @StokInID ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@StokInID", stokInID);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
result = new List <StokInOutModel>();
while (dr.Read())
{
var item = new StokInOutModel
{
StokInID = stokInID,
StokInOutID = dr["StokInOutID"].ToString(),
TglTrs = dr["TglTrs"].ToString().ToTglDMY(),
JamTrs = dr["JamTrs"].ToString(),
ReffTrsID = dr["ReffTrsID"].ToString(),
JenisMutasiID = dr["JenisMutasiID"].ToString(),
JenisMutasiName = dr["JenisMutasiName"].ToString(),
BrgID = dr["BrgID"].ToString(),
BrgName = dr["BrgName"].ToString(),
StokControlID = dr["StokControlID"].ToString(),
QtyIn = Convert.ToInt64(dr["QtyIn"]),
QtyOut = Convert.ToInt64(dr["QtyOut"]),
Hpp = Convert.ToDouble(dr["Hpp"]),
HargaJual = Convert.ToDouble(dr["HargaJual"])
};
result.Add(item);
}
}
}
}
return(result);
}
StokInOutModel StokInOutDataFactory2()
{
var result = new StokInOutModel
{
StokInID = "A2",
StokInOutID = "B2",
TglTrs = "02-01-2000",
JamTrs = "02:09:09",
ReffTrsID = "C2",
JenisMutasiID = "D2",
BrgID = "E2",
BrgName = "F2",
StokControlID = "G2",
QtyIn = 0,
QtyOut = 4,
Hpp = 220000,
HargaJual = 32000,
};
return(result);
}
StokInOutModel StokInOutDataFactory()
{
var result = new StokInOutModel
{
StokInID = "A1",
StokInOutID = "B1",
TglTrs = "01-01-2000",
JamTrs = "09:09:09",
ReffTrsID = "C1",
JenisMutasiID = "D1",
BrgID = "E1",
BrgName = "F1",
StokControlID = "G1",
QtyIn = 8,
QtyOut = 0,
Hpp = 210000,
HargaJual = 22000,
};
return(result);
}
public void Insert(StokInOutModel stokInOut)
{
var sSql = @"
INSERT INTO
StokInOut (
StokInID, StokInOutID, TglTrs, JamTrs, ReffTrsID,
JenisMutasiID, BrgID, StokControlID,
QtyIn, QtyOut, Hpp, HargaJual)
VALUES (
@StokInID, @StokInOutID, @TglTrs, @JamTrs, @ReffTrsID,
@JenisMutasiID, @BrgID, @StokControlID,
@QtyIn, @QtyOut, @Hpp, @HargaJual) ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@StokInID", stokInOut.StokInID);
cmd.AddParam("@StokInOutID", stokInOut.StokInOutID);
cmd.AddParam("@TglTrs", stokInOut.TglTrs);
cmd.AddParam("@JamTrs", stokInOut.JamTrs);
cmd.AddParam("@ReffTrsID", stokInOut.ReffTrsID);
cmd.AddParam("@JenisMutasiID", stokInOut.JenisMutasiID);
cmd.AddParam("@BrgID", stokInOut.BrgID);
cmd.AddParam("@StokControlID", stokInOut.StokControlID);
cmd.AddParam("@QtyIn", stokInOut.QtyIn);
cmd.AddParam("@QtyOut", stokInOut.QtyOut);
cmd.AddParam("@Hpp", stokInOut.Hpp);
cmd.AddParam("@HargaJual", stokInOut.HargaJual);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public void AddStok(string brgID, decimal qty, double hpp,
string tgl, string jam, string trsMasukID,
string trsDOID, string jenisMutasiID)
{
#region VALIDASI-INPUT
// validasi brg
var brg = _brgBL.GetData(brgID);
if (brg == null)
{
var errMsg = string.Format("BrgID invalid: {0}", brgID);
throw new ArgumentException(errMsg);
}
// cek jenis mutasi
var jenisMutasi = _jenisMutasiBL.GetData(jenisMutasiID);
if (jenisMutasi == null)
{
var errMsg = string.Format("JenisMutasiID invalid : {0}", jenisMutasiID);
throw new ArgumentException(errMsg);
}
if (!jenisMutasi.IsBrgMasuk)
{
var errMsg = string.Format("JenisMutasiID invalid bukan BrgMasuk: {0}", jenisMutasiID);
throw new ArgumentException(errMsg);
}
// validasi tgl
if (!tgl.IsValidTgl("dd-MM-yyyy"))
{
var errMsg = string.Format("Tgl invalid: {0}", tgl);
throw new ArgumentException(errMsg);
}
// validasi jam
if (!tgl.IsValidJam("HH:mm:ss"))
{
var errMsg = string.Format("Jam invalid: {0}", jam);
throw new ArgumentException(errMsg);
}
#endregion
#region PERSIAPAN-ID-TABLE
// contoh StokInID = SI.1901.00031
var prefixStokIn = PREFIX_STOK_IN + DateTime.Now.ToString(".yyMM.");
var stokInID = _paramNoBL.GenNewID(prefixStokIn, 12);
// contoh StokInOutID = SI.1901.00031.001
var prefixStokInOut = stokInID + ".";
var stokInOutID = _paramNoBL.GenNewID(prefixStokInOut, 16);
// contoh StokControlID = DO.1901.00021-B5431
var stokControlID = string.Format("{0}-{1}", trsDOID, brgID);
#endregion
#region PROSES-INSERT-DATA
using (var trans = TransHelper.NewScope())
{
var stokIn = new StokInModel
{
StokInID = stokInID,
BrgID = brgID,
TglMasuk = tgl,
JamMasuk = jam,
TrsMasukID = trsMasukID,
QtyIn = qty,
QtySaldo = qty,
Hpp = hpp,
StokControlID = stokControlID,
TrsDOID = trsDOID,
};
_stokInDal.Insert(stokIn);
var stokInOut = new StokInOutModel
{
StokInID = stokInID,
StokInOutID = stokInOutID,
TglTrs = tgl,
JamTrs = jam,
ReffTrsID = trsMasukID,
JenisMutasiID = jenisMutasiID,
QtyIn = qty,
QtyOut = 0,
Hpp = hpp,
HargaJual = 0
};
_stokInOutDal.Insert(stokInOut);
trans.Complete();
}
#endregion
}
public void RemoveStok(string stokControlID, decimal qty, double hargaJual,
string tgl, string jam, string trsReffID,
string jenisMutasiID)
{
#region VALIDASI-INPUT
// cek jenis mutasi
var jenisMutasi = _jenisMutasiBL.GetData(jenisMutasiID);
if (jenisMutasi == null)
{
var errMsg = string.Format("JenisMutasiID invalid : {0}", jenisMutasiID);
throw new ArgumentException(errMsg);
}
if (!jenisMutasi.IsBrgMasuk)
{
var errMsg = string.Format("JenisMutasiID invalid bukan BrgMasuk: {0}", jenisMutasiID);
throw new ArgumentException(errMsg);
}
// validasi tgl
if (!tgl.IsValidTgl("dd-MM-yyyy"))
{
var errMsg = string.Format("Tgl invalid: {0}", tgl);
throw new ArgumentException(errMsg);
}
// validasi jam
if (!tgl.IsValidJam("HH:mm:ss"))
{
var errMsg = string.Format("Jam invalid: {0}", jam);
throw new ArgumentException(errMsg);
}
#endregion
#region CEK-SALDO-STOK
// ambil semua StokIn yang StokControlID-nya sesuai parameter
var listStokIn = _stokInDal.ListDataByStokControl(stokControlID);
if (listStokIn == null)
{
throw new ArgumentException("StokControlID invalid");
}
// sum qty saldo dan bandingkan
var qtySaldo = listStokIn.Sum(x => x.QtySaldo);
if (qtySaldo < qty)
{
throw new ArgumentException("Qty tidak mencukupi");
}
#endregion
#region INSERT-STOK-IN-OUT
var qtyRemoveSisa = qty;
foreach (var item in listStokIn)
{
// tentukan qty remove stok per StokIn
decimal qtyRemove = 0;
if (item.QtySaldo <= qtyRemoveSisa)
{
qtyRemove = item.QtySaldo;
}
else
{
qtyRemove = qtyRemoveSisa;
}
item.QtySaldo -= qtyRemove;
qtyRemoveSisa -= qtyRemove;
// update saldo (StokIn)
_stokInDal.Update(item);
// update detil history (StokInOut)
var prefixStokInOut = item.StokInID + ".";
var stokInOutID = _paramNoBL.GenNewID(prefixStokInOut, 16);
var stokInOut = new StokInOutModel
{
StokInID = item.StokInID,
StokInOutID = stokInOutID,
TglTrs = tgl,
JamTrs = jam,
ReffTrsID = trsReffID,
JenisMutasiID = jenisMutasiID,
QtyIn = 0,
QtyOut = qtyRemove,
Hpp = item.Hpp,
HargaJual = hargaJual
};
_stokInOutDal.Insert(stokInOut);
// jika sudah berhasil remove sesuai qty, keluar
if (qtyRemoveSisa <= 0)
{
break;
}
}
#endregion
}
