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); } }