public static void getDataUserLogin(string usrlogin)
{
userLogin = usrlogin;
My_Db db = new My_Db();
db.openConnection();
SqlCommand command = new SqlCommand("SELECT *FROM [User] WHERE [Username]=@un", db.GetConnection);
command.Parameters.Add("@un", SqlDbType.VarChar).Value = userLogin;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dtbl = new DataTable();
adapter.Fill(dtbl);
nama = dtbl.Rows[0]["Name"].ToString();
jabatan = dtbl.Rows[0]["Jabatan"].ToString();
password = dtbl.Rows[0]["Password"].ToString();
try
{
byte[] pic = (byte[])dtbl.Rows[0]["Image"];
MemoryStream pict = new MemoryStream(pic);
image = pict;
}
catch { image = null; }
db.closeConnection();
FormUtama.instance.showDataUserLogin();
}
public static bool updateDataUser(string userLogin, string nama, string jabatan, string username, string password, byte[] image)
{
My_Db db = new My_Db();
db.openConnection();
SqlCommand command = new SqlCommand("UPDATE [User] SET [Name]=@un,[Jabatan]=@jb,[Username]=@usn,[Password]=@pw,[Image]=@im WHERE [Username]=@usl", db.GetConnection);
command.Parameters.Add("@usl", SqlDbType.VarChar).Value = userLogin;
command.Parameters.Add("@un", SqlDbType.VarChar).Value = nama;
command.Parameters.Add("@jb", SqlDbType.VarChar).Value = jabatan;
command.Parameters.Add("@usn", SqlDbType.VarChar).Value = username;
command.Parameters.Add("@pw", SqlDbType.VarChar).Value = password;
command.Parameters.Add("@im", SqlDbType.Image).Value = image;
if (command.ExecuteNonQuery() == 1)
{
db.closeConnection();
getDataUserLogin(username);
return(true);
}
else
{
return(false);
}
}
private void loadData()
{
try
{
My_Db db = new My_Db();
db.openConnection();
SqlCommand command = new SqlCommand("SELECT *FROM equipment", db.GetConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dtbl);
dtbl.Columns.Remove("Id");
dtbl.Columns.Remove("Keterangan");
dtbl.Columns.Remove("Image");
if (User.statusLogin == true)
{
dtbl.Columns.Add("Edit/Delete");
for (int i = 0; i < dtbl.Rows.Count; i++)
{
dtbl.Rows[i]["Edit/Delete"] = "...";
}
}
dgv.DataSource = dtbl;
Setting setEq = new Setting();
setEq.buildColoumEquipment();
buildTable(setEq.kolomEquipment);
//color row
db.closeConnection();
}
catch { }
}
private void deleteData()
{
My_Db db = new My_Db();
db.openConnection();
SqlCommand command = new SqlCommand("DELETE FROM [Equipment] WHERE [Kode Alat]=@ko", db.GetConnection);
command.Parameters.AddWithValue("@ko", itemEdit);
command.ExecuteScalar();
}
public bool UserNameExist(string username)
{
try
{
My_Db db = new My_Db();
db.openConnection();
SqlCommand command = new SqlCommand("SELECT *FROM [User] WHERE [Username]=@un", db.GetConnection);
command.Parameters.Add("@un", SqlDbType.VarChar).Value = username;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dtbl = new DataTable();
adapter.Fill(dtbl);
db.closeConnection();
if (dtbl.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch { return(false); }
}
private bool insertData()
{
My_Db db = new My_Db();
db.openConnection();
//History
string history = tglMasuk.Value.ToString("dd-MMM-yyyy") + " : Pembelian Alat";
//Posisi Alat
string posisiAlat = "";
string keterangan = "";
if (comboBoxStatus.SelectedItem.Equals("Cadangan"))
{
posisiAlat = "Office WWT";
}
else
{
posisiAlat = txtKet.Text;
history += "\n" + tglPemakaian.Value.ToString("dd-MMM-yyyy") + " : Alat Terpasang di " + txtKet.Text;
}
//kondisi alat
string kondisiAlat = "OK";
//image
byte[] pict = new byte[0];
bool noImage = false;
try
{
MemoryStream img = new MemoryStream();
boxGambarAlat.Image.Save(img, boxGambarAlat.Image.RawFormat);
pict = img.GetBuffer();
}
catch { noImage = true; }
try
{
string noImg = "insert into [Equipment] ([Kode Alat],[Nama Alat],[Tahun Beli],[Posisi Alat],[Kondisi Alat],[Keterangan],[No. NCR],[No. Buang Aktiva Tetap],[History]) values (@ka,@na,@tb,@po,@ko,@ket,@ncr,@at,@hi)";
string withImg = "insert into [Equipment] ([Kode Alat],[Nama Alat],[Tahun Beli],[Posisi Alat],[Kondisi Alat],[Keterangan],[No. NCR],[No. Buang Aktiva Tetap],[History],[Image]) values (@ka,@na,@tb,@po,@ko,@ket,@ncr,@at,@hi,@im)";
SqlCommand command = new SqlCommand(noImage == true ? noImg : withImg, db.GetConnection);
command.Parameters.AddWithValue("@ka", txtKode.Text);
command.Parameters.AddWithValue("@na", txtNama.Text);
command.Parameters.AddWithValue("@tb", tglMasuk.Value);
command.Parameters.AddWithValue("@po", posisiAlat == "" ? "Terpasang" : posisiAlat);
command.Parameters.AddWithValue("@ko", kondisiAlat);
command.Parameters.AddWithValue("@ket", keterangan);
command.Parameters.AddWithValue("@ncr", txtNCR.Text);
command.Parameters.AddWithValue("@at", txtNoBuang.Text);
command.Parameters.AddWithValue("@hi", history);
if (noImage == false)
{
command.Parameters.AddWithValue("@im", pict);
}
command.ExecuteScalar();
db.closeConnection();
return(true);
}
catch
{
return(false);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Setiap Melakukan Update Data Akan Tercatat di History\nPeriksa Kembali Data yang Anda Masukkan\nApakah Data yang dimasukkan sudah benar?", "Konfirmasi Update", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
{
My_Db db = new My_Db();
db.openConnection();
//Posisi Alat
string posisiAlat = "";
string ket = "";
if (comboBoxStatus.SelectedItem.Equals("Cadangan"))
{
posisiAlat = "Office WWT";
}
else if (comboBoxStatus.SelectedItem.Equals("Terpasang"))
{
posisiAlat = txtKet.Text;
}
else if (comboBoxStatus.SelectedItem.Equals("Rusak"))
{
posisiAlat = "Maintenance"; ket = txtKet.Text;
}
else if (comboBoxStatus.SelectedItem.Equals("Dibuang"))
{
posisiAlat = "Dibuang";
}
//kondisi alat
string kondisiAlat;
if (comboBoxStatus.SelectedItem.Equals("Cadangan") || comboBoxStatus.SelectedItem.Equals("Terpasang"))
{
kondisiAlat = "OK";
}
else if (comboBoxStatus.SelectedItem.Equals("Rusak"))
{
kondisiAlat = "Rusak";
}
else
{
kondisiAlat = "Dibuang";
}
//image
//byte[] pict;
//try
//{
// MemoryStream img = new MemoryStream();
// boxGambarAlat.Image.Save(img, boxGambarAlat.Image.RawFormat);
// pict = img.GetBuffer();
//}
//catch
//{
// pict = new byte[0];
//}
//history
SqlCommand getHistory = new SqlCommand("SELECT *FROM [Equipment] WHERE [Kode Alat]=@ko", db.GetConnection);
getHistory.Parameters.Add("@ko", SqlDbType.VarChar).Value = itemEdit;
DataTable history = new DataTable();
SqlDataAdapter adap = new SqlDataAdapter(getHistory);
adap.Fill(history);
string hstory = history.Rows[0]["History"].ToString() + "\n" + tglPemakaian.Value.ToString("dd-MMM-yyyy");
if (posisiAlat == "Office WWT")
{
hstory += " : Disimpan Sebagai Stok";
}
else if (posisiAlat == txtKet.Text)
{
hstory += " : Terpasang di" + txtKet.Text;
}
else if (posisiAlat == "Maintenance")
{
hstory += " : Terjadi Kerusakan ( " + txtKet.Text + " )";
}
else
{
hstory += " : Alat Dibuang";
}
try
{
SqlCommand command = new SqlCommand("UPDATE [Equipment] SET [Kode Alat]=@ko,[Nama Alat]=@na,[Posisi Alat]=@po,[Kondisi Alat]=@ka,[Keterangan]=@ket,[No. NCR]=@ncr,[No. Buang Aktiva Tetap]=@at,[History]=@hi WHERE [Kode Alat]=@kode", db.GetConnection);
command.Parameters.AddWithValue("@kode", itemEdit);
command.Parameters.AddWithValue("@ko", txtKode.Text);
command.Parameters.AddWithValue("@na", txtNama.Text);
if (posisiAlat == "")
{
command.Parameters.AddWithValue("@po", "Terpasang");
}
else
{
command.Parameters.AddWithValue("@po", posisiAlat);
}
command.Parameters.AddWithValue("@ka", kondisiAlat);
command.Parameters.AddWithValue("@ket", ket);
command.Parameters.AddWithValue("@ncr", txtNCR.Text);
command.Parameters.AddWithValue("@at", txtNoBuang.Text);
//command.Parameters.AddWithValue("@im", pict);
command.Parameters.AddWithValue("@hi", hstory);
command.ExecuteScalar();
db.closeConnection();
if (MessageBox.Show("Data Berhasil diupdate.", "Update Data Berhasil", MessageBoxButtons.OK, MessageBoxIcon.Information) == DialogResult.OK)
{
equipment eq = new equipment();
Menu menu = new Menu();
menu.menuClick(eq);
}
}
catch
{
db.closeConnection();
MessageBox.Show("Gagal Update Data!\nMohon periksa kembali data yang anda masukkan", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void editData(string kode)
{
itemEdit = kode;
btnDelete.Visible = true;
panelTglMasuk.Visible = false;
My_Db db = new My_Db();
db.openConnection();
SqlCommand command = new SqlCommand("SELECT *FROM [Equipment] WHERE [Kode Alat]=@ko", db.GetConnection);
command.Parameters.Add("@ko", SqlDbType.VarChar).Value = kode;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dtbl = new DataTable();
adapter.Fill(dtbl);
//posisi alat
string dataStatus = dtbl.Rows[0]["Posisi Alat"].ToString();
string statusAlat;
if (dataStatus == "Office WWT")
{
statusAlat = "Cadangan";
}
else if (dataStatus == "Maintenance")
{
statusAlat = "Rusak";
}
else if (dataStatus == "Dibuang")
{
statusAlat = "Dibuang";
}
else
{
statusAlat = "Terpasang";
}
txtKode.Text = dtbl.Rows[0]["Kode Alat"].ToString();
txtNama.Text = dtbl.Rows[0]["Nama Alat"].ToString();
comboBoxStatus.Text = statusAlat;
if (statusAlat == "Terpasang")
{
txtKet.Text = dataStatus;
}
if (statusAlat == "Maintenance")
{
txtKet.Text = dtbl.Rows[0]["Keterangan"].ToString();
}
if (dtbl.Rows[0]["No. NCR"].ToString() != "")
{
txtNCR.Text = dtbl.Rows[0]["No. NCR"].ToString();
}
if (dtbl.Rows[0]["No. Buang Aktiva Tetap"].ToString() != "")
{
txtNoBuang.Text = dtbl.Rows[0]["No. Buang Aktiva Tetap"].ToString();
}
//try
//{
// byte[] pict = (byte[])dtbl.Rows[0]["Image"];
// MemoryStream pic = new MemoryStream(pict);
// boxGambarAlat.Image = Image.FromStream(pic);
//}
//catch { }
db.closeConnection();
}