public IptAnggotaDto InserOrUpdate(IptAnggotaDto Dto)
{
if (Dto.Id == 0)
{
Dto = Insert(Dto);
}
else
{
Dto = Update(Dto);
}
return(Dto);
}
public IptAnggotaDto Insert(IptAnggotaDto Dto)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn = DBConnection.connect();
try
{
using (OleDbCommand _command = new OleDbCommand())
{
_command.CommandType = CommandType.Text;
_command.Connection = conn;
_command.CommandText = "INSERT INTO IPT_ANGGOTA " +
"(id_anggota, tanggal, pokok, wajib, sukarela, belanja, bunga_pinjaman, created_date, created_by, modified_date, modified_by) " +
"VALUES (@IdAnggota, @Tanggal, @Pokok, @Wajib, @Sukarela, @Belanja, @BungaPinjaman, @created_date, @created_by, @modified_date, @modified_by);";
_command.Parameters.AddWithValue("@IdAnggota", Dto.IdAnggota);
_command.Parameters.AddWithValue("@Tanggal", Dto.Tanggal);
_command.Parameters.AddWithValue("@Pokok", Dto.Pokok);
_command.Parameters.AddWithValue("@Wajib", Dto.Wajib);
_command.Parameters.AddWithValue("@Sukarela", Dto.Sukarela);
_command.Parameters.AddWithValue("@Belanja", Dto.Belanja);
_command.Parameters.AddWithValue("@BungaPinjaman", Dto.BungaPinjaman);
_command.Parameters.AddWithValue("@created_date", DateTime.Today);
_command.Parameters.AddWithValue("@created_by", "Admin");
_command.Parameters.AddWithValue("@modified_date", DateTime.Today);
_command.Parameters.AddWithValue("@modified_by", "Admin");
_command.ExecuteNonQuery();
string query2 = "Select @@Identity";
_command.CommandText = query2;
int ID = (int)_command.ExecuteScalar();
Dto.CreatedBy = "Admin";
Dto.CreatedDate = DateTime.Today;
Dto.ModifiedBy = "Admin";
Dto.ModifiedDate = DateTime.Today;
Dto.Id = ID;
return(Dto);
}
}
catch (Exception)
{
throw;
}
}
public List <IptAnggotaDto> GetAllByIdAnggotaDanTahun(string IdAnggota, int Tahun)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn = DBConnection.connect();
var ListDto = new List <IptAnggotaDto>();
using (OleDbCommand _command = new OleDbCommand())
{
_command.CommandType = CommandType.Text;
_command.Connection = conn;
_command.CommandText = "Select B.NAMA_ANGGOTA AS nama_anggota, A.* from IPT_ANGGOTA A INNER JOIN DATA_ANGGOTA B ON A.ID_ANGGOTA = B.ID_ANGGOTA WHERE YEAR(A.tanggal) = @TAHUN and A.id_anggota = @IdAnggota";
_command.Parameters.AddWithValue("@TAHUN", Tahun);
_command.Parameters.AddWithValue("@IdAnggota", IdAnggota);
using (OleDbDataReader _dr = _command.ExecuteReader())
{
DataTable _dt = new DataTable();
_dt.Load(_dr);
foreach (DataRow row in _dt.AsEnumerable())
{
var Dto = new IptAnggotaDto();
Dto.Id = Convert.ToInt64(row["id"]);
Dto.IdAnggota = Convert.ToString(row["id_anggota"]);
Dto.NamaAnggota = Convert.ToString(row["nama_anggota"]);
Dto.Tanggal = Convert.ToDateTime(row["tanggal"]);
Dto.Pokok = Convert.ToDouble(row["pokok"]);
Dto.Wajib = Convert.ToDouble(row["wajib"]);
Dto.Sukarela = Convert.ToDouble(row["sukarela"]);
Dto.Belanja = Convert.ToDouble(row["belanja"]);
Dto.BungaPinjaman = Convert.ToDouble(row["bunga_pinjaman"]);
Dto.CreatedBy = Convert.ToString(row["created_by"]);
Dto.CreatedDate = Convert.ToDateTime(row["created_date"]);
Dto.ModifiedDate = Convert.ToDateTime(row["modified_date"]);
Dto.ModifiedBy = Convert.ToString(row["modified_by"]);
ListDto.Add(Dto);
}
}
}
return(ListDto);
}
public IptAnggotaDto Update(IptAnggotaDto Dto)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn = DBConnection.connect();
try
{
var GetData = GetById(Dto.Id);
using (OleDbCommand _command = new OleDbCommand())
{
_command.CommandType = CommandType.Text;
_command.Connection = conn;
_command.CommandText = "UPDATE IPT_ANGGOTA " +
"SET id_anggota = @IdAnggota, tanggal=@Tanggal, pokok = @Pokok, wajib=@Wajib, sukarela=@Sukarela, belanja=@Belanja, bunga_pinjaman=@BungaPinjaman, created_date=@created_date, created_by=@created_by, modified_date=@modified_date, modified_by=@modified_by where id=@Id;";
_command.Parameters.AddWithValue("@IdAnggota", Dto.IdAnggota);
_command.Parameters.AddWithValue("@Tanggal", Dto.Tanggal);
_command.Parameters.AddWithValue("@Pokok", Dto.Pokok);
_command.Parameters.AddWithValue("@Wajib", Dto.Wajib);
_command.Parameters.AddWithValue("@Sukarela", Dto.Sukarela);
_command.Parameters.AddWithValue("@Belanja", Dto.Belanja);
_command.Parameters.AddWithValue("@BungaPinjaman", Dto.BungaPinjaman);
_command.Parameters.AddWithValue("@created_date", GetData.CreatedDate);
_command.Parameters.AddWithValue("@created_by", GetData.CreatedBy);
_command.Parameters.AddWithValue("@modified_date", DateTime.Today);
_command.Parameters.AddWithValue("@modified_by", "Admin");
_command.Parameters.AddWithValue("@Id", Dto.Id);
_command.ExecuteNonQuery();
Dto.ModifiedDate = DateTime.Today;
Dto.ModifiedBy = "Admin";
}
return(Dto);
}
catch (Exception)
{
throw;
}
}
private void BtnSimpan_Click(object sender, RoutedEventArgs e)
{
try
{
var Dto = new IptAnggotaDto();
if (string.IsNullOrEmpty(IdAnggota.Text))
{
System.Windows.MessageBox.Show("Id Anggota tidak boleh kosong", "Info", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
if (string.IsNullOrEmpty(Tanggal.Text))
{
System.Windows.MessageBox.Show("Tanggal tidak boleh kosong", "Info", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
if (string.IsNullOrEmpty(Pokok.Text))
{
Pokok.Text = "0";
}
if (string.IsNullOrEmpty(Wajib.Text))
{
Wajib.Text = "0";
}
if (string.IsNullOrEmpty(Sukarela.Text))
{
Sukarela.Text = "0";
}
if (string.IsNullOrEmpty(Belanja.Text))
{
Belanja.Text = "0";
}
if (string.IsNullOrEmpty(BungaPinjaman.Text))
{
BungaPinjaman.Text = "0";
}
Dto.Id = Convert.ToInt64(Id.Text);
Dto.CreatedBy = CreatedBy.Text;
Dto.CreatedDate = Convert.ToDateTime(CreatedDate.Text);
Dto.IdAnggota = IdAnggota.Text;
var CheckAnggota = CheckAnggotaExist(Dto.IdAnggota);
if (CheckAnggota == null)
{
System.Windows.MessageBox.Show("ID tersebut tidak terdaftar", "Info", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
Dto.NamaAnggota = CheckAnggota.NamaAnggota;
Dto.Tanggal = Convert.ToDateTime(Tanggal.Text);
Dto.Pokok = Convert.ToDouble(Pokok.Text);
Dto.Wajib = Convert.ToDouble(Wajib.Text);
Dto.Sukarela = Convert.ToDouble(Sukarela.Text);
Dto.Belanja = Convert.ToDouble(Belanja.Text);
Dto.BungaPinjaman = Convert.ToDouble(BungaPinjaman.Text);
Dto = _iptAnggotaServices.InserOrUpdate(Dto);
System.Windows.MessageBox.Show("Data sudah tersimpan", "Info", MessageBoxButton.OK, MessageBoxImage.Information);
CloseWin();
}
catch (Exception ex)
{
LogError.WriteError(ex);
System.Windows.MessageBox.Show("Error!! \n telah terjadi kesalahan, Hubungi administrator", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void BtnImport_Click(object sender, RoutedEventArgs e)
{
try
{
List <IptAnggotaDto> ListData = new List <IptAnggotaDto>();
if (File.Exists(txtUpload.Text))
{
if (System.IO.Path.GetExtension(txtUpload.Text) == ".xls" || System.IO.Path.GetExtension(txtUpload.Text) == ".xlsx")
{
IWorkbook workbook = null;
string fileName = txtUpload.Text;
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
//First sheet
ISheet sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
int rowCount = sheet.LastRowNum; // This may not be valid row count.
string Message = "";
for (int i = 2; i <= rowCount; i++)
{
IRow curRow = sheet.GetRow(i);
// Works for consecutive data. Use continue otherwise
if (curRow == null)
{
// Valid row count
rowCount = i - 1;
continue;
}
// Get data from the 4th column (4th cell of each row)
var Data = new IptAnggotaDto();
try
{
if (!NpoiExtensions.IsNullOrEmpty(curRow.GetCell(0)))
{
var cellValue = curRow.GetCell(0).ToString();
Data.IdAnggota = cellValue;
var CheckAnggota = CheckAnggotaExist(Data.IdAnggota);
if (CheckAnggota == null)
{
Data.Message = "ID Anggota tidak terdaftar";
}
}
}
catch (Exception ex) { Data.Message = ex.Message; }
try
{
if (!NpoiExtensions.IsNullOrEmpty(curRow.GetCell(1)))
{
var cellValue = curRow.GetCell(1).ToString();
Data.NamaAnggota = cellValue;
}
}
catch (Exception ex) { Data.Message = ex.Message; }
try
{
if (!NpoiExtensions.IsNullOrEmpty(curRow.GetCell(2)))
{
var cellValue = curRow.GetCell(2).DateCellValue;
Data.Tanggal = cellValue;
}
}
catch (Exception ex) { Data.Message = ex.Message; }
try
{
if (!NpoiExtensions.IsNullOrEmpty(curRow.GetCell(3)))
{
var cellValue = curRow.GetCell(3).NumericCellValue;
Data.Pokok = Convert.ToDouble(cellValue);
}
}
catch (Exception ex) { Data.Message = ex.Message; }
try
{
if (!NpoiExtensions.IsNullOrEmpty(curRow.GetCell(4)))
{
var cellValue = curRow.GetCell(4).NumericCellValue;
Data.Wajib = Convert.ToDouble(cellValue);
}
}
catch (Exception ex) { Data.Message = ex.Message; }
try
{
if (!NpoiExtensions.IsNullOrEmpty(curRow.GetCell(5)))
{
var cellValue = curRow.GetCell(5).NumericCellValue;
Data.Sukarela = Convert.ToDouble(cellValue);
}
}
catch (Exception ex) { Data.Message = ex.Message; }
try
{
if (!NpoiExtensions.IsNullOrEmpty(curRow.GetCell(6)))
{
var cellValue = curRow.GetCell(6).NumericCellValue;
Data.Belanja = Convert.ToDouble(cellValue);
}
}
catch (Exception ex) { Data.Message = ex.Message; }
try
{
if (!NpoiExtensions.IsNullOrEmpty(curRow.GetCell(7)))
{
var cellValue = curRow.GetCell(7).NumericCellValue;
Data.BungaPinjaman = Convert.ToDouble(cellValue);
}
}
catch (Exception ex) { Data.Message = ex.Message; }
Data.CreatedBy = "Admin";
Data.CreatedDate = DateTime.Now;
Data.ModifiedBy = "Admin";
Data.ModifiedDate = DateTime.Now;
Message = Message + Data.Message;
ListData.Add(Data);
}
ICollectionView _data = CollectionViewSource.GetDefaultView(ListData);
Dgv_Home.ItemsSource = _data;
if (string.IsNullOrEmpty(Message))
{
BtnSave.Visibility = Visibility.Visible;
_listDto = ListData;
return;
}
System.Windows.MessageBox.Show("Terdapat error pada file yang di upload \n check di kolom keterangan", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
else
{
System.Windows.MessageBox.Show("File Tersebut tidak mendukung \n Silahkan download file template", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
else
{
System.Windows.MessageBox.Show("File Tersebut Tidak ada", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
catch (Exception ex)
{
LogError.WriteError(ex);
System.Windows.MessageBox.Show("Error!! \n telah terjadi kesalahan, Hubungi administrator", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
