public void Insert(BrgModel brg)
{
var sSql = @"
INSERT INTO
Brg (
BrgID, BrgName, BrgNamePrint, Keterangan,
SubJenisBrgID, MerkID, ColorID,
Kemasan, IsAktif)
VALUES (
@BrgID, @BrgName, @BrgNamePrint, @Keterangan,
@SubJenisBrgID, @MerkID, @ColorID,
@Kemasan, @IsAktif) ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BrgID", brg.BrgID);
cmd.AddParam("@BrgName", brg.BrgName);
cmd.AddParam("@BrgNamePrint", brg.BrgNamePrint);
cmd.AddParam("@Keterangan", brg.Keterangan);
cmd.AddParam("@SubJenisBrgID", brg.SubJenisBrgID);
cmd.AddParam("@MerkID", brg.MerkID);
cmd.AddParam("@ColorID", brg.ColorID);
cmd.AddParam("@Kemasan", brg.Kemasan);
cmd.AddParam("@IsAktif", brg.IsAktif);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public void Update(BrgModel brg)
{
var sSql = @"
UPDATE
Brg
SET
BrgName = @BrgName,
BrgNamePrint = @BrgNamePrint,
Keterangan = @Keterangan,
SubJenisBrgID = @SubJenisBrgID,
MerkID = @MerkID,
ColorID = @ColorID,
Kemasan = @Kemasan,
UpdateTimestamp = @UpdateTimestamp,
IsAktif = @IsAktif
WHERE
BrgID = @BrgID ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BrgID", brg.BrgID);
cmd.AddParam("@BrgName", brg.BrgName);
cmd.AddParam("@BrgNamePrint", brg.BrgName);
cmd.AddParam("@Keterangan", brg.Keterangan);
cmd.AddParam("@SubJenisBrgID", brg.SubJenisBrgID);
cmd.AddParam("@MerkID", brg.MerkID);
cmd.AddParam("@ColorID", brg.ColorID);
cmd.AddParam("@Kemasan", brg.Kemasan);
cmd.AddParam("@UpdateTimestamp", DateTime.Now);
cmd.AddParam("@IsAktif", brg.IsAktif);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public BrgModel GetData(string id)
{
BrgModel result = null;
var sSql = @"
SELECT
aa.BrgName, aa. BrgNamePrint, aa.Keterangan,
aa.SubJenisBrgID, aa.MerkID, aa.ColorID,
aa.Kemasan,
aa.CreateTimestamp, aa.UpdateTimestamp,
aa.IsAktif,
ISNULL(bb.SubJenisBrgName, '') SubJenisBrgName,
ISNULL(cc.MerkName, '') MerkName
FROM
Brg aa
LEFT JOIN SubJenisBrg bb ON aa.SubJenisBrgID = bb.SubJenisBrgID
LEFT JOIN Merk cc ON aa.MerkID = cc.MerkID
WHERE
aa.BrgID = @BrgID ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@BrgID", id);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
dr.Read();
result = new BrgModel
{
BrgID = id,
BrgName = dr["BrgName"].ToString(),
BrgNamePrint = dr["BrgNamePrint"].ToString(),
Keterangan = dr["Keterangan"].ToString(),
SubJenisBrgID = dr["SubJenisBrgID"].ToString(),
SubJenisBrgName = dr["SubJenisBrgName"].ToString(),
MerkID = dr["MerkID"].ToString(),
MerkName = dr["MerkName"].ToString(),
ColorID = dr["ColorID"].ToString(),
Kemasan = dr["Kemasan"].ToString(),
CreateTimestamp = Convert.ToDateTime(dr["CreateTimestamp"]),
UpdateTimestamp = Convert.ToDateTime(dr["UpdateTimestamp"]),
IsAktif = Convert.ToBoolean(dr["IsAktif"])
};
}
}
}
return(result);
}
BrgModel Brg2DataFactory()
{
var result = new BrgModel
{
BrgID = "A2",
BrgName = "B2",
Keterangan = "C2",
SubJenisBrgID = "D2",
MerkID = "E2",
ColorID = "F2",
Kemasan = "G2"
};
return(result);
}
BrgModel BrgDataFactory()
{
var result = new BrgModel
{
BrgID = "A1",
BrgName = "B1",
Keterangan = "C1",
SubJenisBrgID = "D1",
MerkID = "E1",
ColorID = "F1",
Kemasan = "G1",
};
return(result);
}
public BrgModel TryValidate(BrgModel brg)
{
var result = brg;
if (brg == null)
{
throw new ArgumentNullException(nameof(brg));
}
if (brg.BrgName.Trim() == "")
{
throw new ArgumentException("BrgName empty");
}
var subJenisBrg = _subJenisBrgBL.GetData(brg.SubJenisBrgID);
if (subJenisBrg == null)
{
throw new ArgumentException("SubJenisBrgID invalid");
}
else
{
brg.SubJenisBrgName = subJenisBrg.SubJenisBrgName;
}
if (brg.MerkID.Trim() != "")
{
var merk = _merkBL.GetData(brg.MerkID);
if (merk == null)
{
throw new ArgumentException("MerkID invalid");
}
else
{
brg.MerkName = merk.MerkName;
}
}
if (brg.ColorID.Trim() != "")
{
var color = _colorBL.GetData(brg.ColorID);
if (color == null)
{
throw new ArgumentException("ColorID invalid");
}
}
return(result);
}
private void SaveButton_Click(object sender, EventArgs e)
{
// cek inputan combobox
var subJenisID = SubJenisBrgComboBox.SelectedValue ?? "";
var merkID = MerkComboBox.SelectedValue ?? "";
var colorID = ColorComboBox.SelectedValue ?? "";
var kemasan = KemasanComboBox.Text;
var brg = new BrgModel
{
BrgID = BrgIDText.Text,
BrgName = BrgNameText.Text,
Keterangan = KeteranganText.Text,
ColorID = colorID.ToString(),
SubJenisBrgID = subJenisID.ToString(),
MerkID = merkID.ToString(),
Kemasan = kemasan,
BrgNamePrint = String.Concat(BrgNameText.Text, 27),
IsAktif = AktifCheckBox.Checked
};
try
{
var result = _brgBL.Save(brg);
}
catch (Exception x)
{
MessageBox.Show(x.Message);
return;
}
// kosongkan tampilan jika berhasil
BrgIDText.Clear();
BrgNameText.Clear();
KeteranganText.Clear();
LoadJenisBrgCombo();
LoadColorCombo();
LoadMerkCombo();
LoadKemasanComboBox();
}
public BrgModel Save(BrgModel brg)
{
// validasi
var result = brg;
result = TryValidate(brg);
using (var trans = TransHelper.NewScope())
{
// save
if (brg.BrgID.Trim() == "")
{
brg.BrgID = _paramNoBL.GenNewID("B", 5);
_brgDal.Insert(result);
// insert ke barangStokHarga
_brgStokHargaBL.UpdateStok(brg.BrgID);
}
else
{
var dummyBrg = _brgDal.GetData(brg.BrgID);
if (dummyBrg == null)
{
_brgDal.Insert(result);
// insert ke barangStokHarga
_brgStokHargaBL.UpdateStok(brg.BrgID);
}
else
{
_brgDal.Update(result);
// insert ke barangStokHarga
_brgStokHargaBL.UpdateStok(brg.BrgID);
}
}
trans.Complete();
}
return(result);
}
public void RemoveStok(BrgModel brg, decimal Qty, double hargaJual, string jenisMutasiID, string trsReffID)
{
throw new NotImplementedException();
}
public IEnumerable <BrgModel> ListData(string jenisBrgID, string subID, string merkID, string colorID)
{
const string NO_ID = "X";
List <BrgModel> result = null;
var sSql = @"
SELECT
aa.BrgID, aa.BrgName, aa.BrgNamePrint, aa.Keterangan,
aa.SubJenisBrgID, aa.MerkID, aa.ColorID,
aa.Kemasan,
aa.CreateTimestamp, aa.UpdateTimestamp,
aa.IsAktif,
ISNULL(bb.SubJenisBrgName, '') SubJenisBrgName,
ISNULL(cc.MerkName, '') MerkName,
ISNULL(bb.JenisBrgID, '') JenisBrgID,
ISNULL(dd.JenisBrgName, '') JenisBrgName
FROM
Brg aa
LEFT JOIN SubJenisBrg bb ON aa.SubJenisBrgID = bb.SubJenisBrgID
LEFT JOIN Merk cc ON aa.MerkID = cc.MerkID
LEFT JOIN JenisBrg dd ON bb.JenisBrgID = dd.JenisBrgID
WHERE
dd.JenisBrgID = @JenisBrgID ";
if (subID.Trim() != "")
{
sSql += @"
AND aa.SubJenisBrgID = @SubJenisBrgID ";
}
if (merkID.Trim() != "")
{
sSql += @"
AND aa.MerkID = @MerkID ";
}
if (colorID.Trim() != "")
{
sSql += @"
AND aa.ColorID = @ColorID ";
}
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@JenisBrgID", jenisBrgID);
if (subID.Trim() != "")
{
cmd.AddParam("@SubJenisBrgID", subID);
}
if (merkID.Trim() != "")
{
if (merkID.ToUpper() == NO_ID.ToUpper())
{
merkID = "";
}
cmd.AddParam("@MerkID", merkID);
}
if (colorID.Trim() != "")
{
if (colorID.ToUpper() == NO_ID.ToUpper())
{
colorID = "";
}
cmd.AddParam("@ColorID", colorID);
}
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
result = new List <BrgModel>();
while (dr.Read())
{
var item = new BrgModel
{
BrgID = dr["BrgID"].ToString(),
BrgName = dr["BrgName"].ToString(),
BrgNamePrint = dr["BrgNamePrint"].ToString(),
Keterangan = dr["Keterangan"].ToString(),
SubJenisBrgID = dr["SubJenisBrgID"].ToString(),
SubJenisBrgName = dr["SubJenisBrgName"].ToString(),
MerkID = dr["MerkID"].ToString(),
MerkName = dr["MerkName"].ToString(),
ColorID = dr["ColorID"].ToString(),
JenisBrgID = dr["JenisBrgID"].ToString(),
JenisBrgName = dr["JenisBrgName"].ToString(),
Kemasan = dr["Kemasan"].ToString(),
CreateTimestamp = Convert.ToDateTime(dr["CreateTimestamp"]),
UpdateTimestamp = Convert.ToDateTime(dr["UpdateTimestamp"]),
IsAktif = Convert.ToBoolean(dr["IsAktif"])
};
result.Add(item);
}
}
}
}
return(result);
}
private IEnumerable <BrgModel> ListData(TipeListDataEnum tipeList, string data)
{
List <BrgModel> result = null;
var sSql = @"
SELECT
aa.BrgID, aa.BrgName, aa.BrgNamePrint, aa.Keterangan,
aa.SubJenisBrgID, aa.MerkID, aa.ColorID,
aa.Kemasan,
aa.CreateTimestamp, aa.UpdateTimestamp,
aa.IsAktif,
ISNULL(bb.SubJenisBrgName, '') SubJenisBrgName,
ISNULL(cc.MerkName, '') MerkName,
ISNULL(bb.JenisBrgID, '') JenisBrgID,
ISNULL(dd.JenisBrgName, '') JenisBrgName
FROM
Brg aa
LEFT JOIN SubJenisBrg bb ON aa.SubJenisBrgID = bb.SubJenisBrgID
LEFT JOIN Merk cc ON aa.MerkID = cc.MerkID
LEFT JOIN JenisBrg dd ON bb.JenisBrgID = dd.JenisBrgID ";
switch (tipeList)
{
case TipeListDataEnum.All:
break;
case TipeListDataEnum.SubJenisBrg:
sSql += " WHERE aa.SubJenisBrgID = @SubJenisBrgID ";
break;
case TipeListDataEnum.SearchName:
sSql += " WHERE aa.BrgName LIKE @BrgName ";
break;
default:
break;
}
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
switch (tipeList)
{
case TipeListDataEnum.All:
break;
case TipeListDataEnum.SubJenisBrg:
cmd.AddParam("@SubJenisBrgID", data);
break;
case TipeListDataEnum.SearchName:
cmd.AddParam("@BrgName", "%" + data + "%");
break;
default:
break;
}
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
result = new List <BrgModel>();
while (dr.Read())
{
var item = new BrgModel
{
BrgID = dr["BrgID"].ToString(),
BrgName = dr["BrgName"].ToString(),
BrgNamePrint = dr["BrgNamePrint"].ToString(),
Keterangan = dr["Keterangan"].ToString(),
SubJenisBrgID = dr["SubJenisBrgID"].ToString(),
SubJenisBrgName = dr["SubJenisBrgName"].ToString(),
MerkID = dr["MerkID"].ToString(),
MerkName = dr["MerkName"].ToString(),
ColorID = dr["ColorID"].ToString(),
JenisBrgID = dr["JenisBrgID"].ToString(),
JenisBrgName = dr["JenisBrgName"].ToString(),
Kemasan = dr["Kemasan"].ToString(),
CreateTimestamp = Convert.ToDateTime(dr["CreateTimestamp"]),
UpdateTimestamp = Convert.ToDateTime(dr["UpdateTimestamp"]),
IsAktif = Convert.ToBoolean(dr["IsAktif"])
};
result.Add(item);
}
}
}
}
return(result);
}