public PhieuThuDTO GetRow(string maPhieuThu)
{
try
{
var obj = new PhieuThuDTO();
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
SqlCommand command = new SqlCommand("SELECT * FROM PhieuThu WHERE MaPhieuThu = @maphieuthu", _connection);
command.Parameters.Add("@maphieuthu", SqlDbType.Char).Value = maPhieuThu;
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
obj.MaPhieuThu = reader["MaPhieuThu"].ToString();
obj.MaNhanVien = reader["MaNhanVien"].ToString();
obj.MaKhachHang = reader["MaKhachHang"].ToString();
obj.NgayThu = (DateTime)reader["NgayThu"];
obj.SoTienThu = (decimal)reader["SoTienThu"];
obj.LyDoThu = reader["LyDoThu"] as string;
reader.Close();
}
return(obj);
}
catch (Exception ex)
{
_connection.Close();
Console.WriteLine(ex.Message);
}
return(null);
}
public string InsertPhieuThu(PhieuThuDTO dtoPhieuThu)
{
string strError = "";
if (dtoPhieuThu.KhachHang.Trim().Equals("") == true)
{
strError += " Tên người nhận không được rỗng.";
}
if (dtoPhieuThu.SoTien.Equals("") == true)
{
strError += " Số tiền không được rỗng.";
}
if (dtoPhieuThu.VietBangChu.Trim().Equals("") == true)
{
strError += "Số tiền viết bằng chữ không được rỗng.";
}
if (strError == "")
{
bool boolResult = dalPhieuThu.InsertPhieuThu(dtoPhieuThu);
return("ok");
}
else
{
return(strError);
}
}
private void buttonThemPhieuThuTien_Click(object sender, EventArgs e)
{
PhieuThuDTO obj = new PhieuThuDTO();
obj.MaPhieuThu = this.textBoxMaPhieuThu.Text;
obj.MaHoaDon = this.comboBoxMaHoaDon.Text;
obj.NgayThuTien = Convert.ToDateTime(this.dateTimePickerNgayThuTien.Text);
obj.SoTienThu = Convert.ToInt32(this.textBoxSoTienThu.Text);
string result = this.buspt.insert(obj);
//HienThiDanhSachHoaDon();
//HienThiDSSach();
HienThiDanhSachPhieuThu();
if (result == "0")
{
MessageBox.Show("Thêm phiếu thu thành công");
return;
}
else
{
MessageBox.Show("Thêm mới phiếu thu thất bại.\n" + result);
return;
}
}
public void Update(PhieuThuDTO pt)
{
try
{
string sql = "Update PHIEUTHU set NgayThu =('" + pt.NgayThu + "'),SoTienThu=(" + pt.SoTienThu + ") where MaPhieuThu=" + pt.MaPhieuThu + "";
kn.Connect();
kn.ExecuteNonQuery(CommandType.Text, sql, new SqlParameter {
ParameterName = "MaPhieuThu", Value = pt.MaPhieuThu
},
new SqlParameter {
ParameterName = "NgayThu", Value = pt.NgayThu
},
new SqlParameter {
ParameterName = "SoTienThu", Value = pt.SoTienThu
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
kn.Disconnect();
}
}
public int Insert(PhieuThuDTO pt)
{
int flag = -1;
try
{
string sql = "insert into PHIEUTHU(MaPhieuThu,NgayThu,SoTienThu,MaKhachHang) values('" + pt.MaPhieuThu + "'," + pt.NgayThu + "'," + pt.SoTienThu + "," + pt.MaKhachHang + ")";
kn.Connect();
int nRow = kn.ExecuteNonQuery(CommandType.Text, sql, new SqlParameter {
ParameterName = "MaPhieuThu", Value = pt.MaPhieuThu
},
new SqlParameter {
ParameterName = "NgayThu", Value = pt.NgayThu
},
new SqlParameter {
ParameterName = "SoTienThu", Value = pt.SoTienThu
},
new SqlParameter {
ParameterName = "MaKhanhHang", Value = pt.MaKhachHang
});
if (nRow > 0)
{
flag = 1;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
kn.Disconnect();
}
return(flag);
}
public string insert(PhieuThuDTO obj)
{
string query = string.Empty;
query += "INSERT INTO [tblphieuthu] ([maphieuthu],[mahd],[ngaythutien],[sotienthu])";
query += "VALUES (@maphieuthu,@mahd,@ngaythutien,@sotienthu)";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand comm = new SqlCommand())
{
comm.Connection = conn;
comm.CommandType = CommandType.Text;
comm.CommandText = query;
comm.Parameters.AddWithValue("@maphieuthu", obj.MaPhieuThu);
comm.Parameters.AddWithValue("@mahd", obj.MaHoaDon);
comm.Parameters.AddWithValue("@ngaythutien", obj.NgayThuTien);
comm.Parameters.AddWithValue("@sotienthu", obj.SoTienThu);
try
{
conn.Open();
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
conn.Close();
return("Thêm phiếu thu thất bại\n" + ex.Message + "\n");
}
}
}
return("0");
}
private void btnLuuKho_Click(object sender, EventArgs e)
{
PhieuThuDTO dtoPhieuThu = new PhieuThuDTO();
dtoPhieuThu.MaPhieuThu = txtSoPhieu.Text;
dtoPhieuThu.MaNV = Variable.strMaNhanVien;
dtoPhieuThu.KhachHang = cmbKhachHang.SelectedValue.ToString();
dtoPhieuThu.NgayLap = dtpNgayThu.Value.ToShortDateString();
dtoPhieuThu.DiaChi = txtDiaChi.Text;
dtoPhieuThu.LyDoThu = txtLyDoThu.Text;
dtoPhieuThu.SoTien = float.Parse(txtSoTien.Text);
dtoPhieuThu.SoPhieu = txtSoPhieu.Text;
dtoPhieuThu.VietBangChu = txtVietBangChu.Text;
dtoPhieuThu.KemTheo = txtKemTheo.Text;
string strResult = bllPhieuThu.InsertPhieuThu(dtoPhieuThu);
if (strResult == "ok")
{
MessageBox.Show("Lưu Thành Công!", "Lưu Phiếu Thu", MessageBoxButtons.OK, MessageBoxIcon.Information);
btnIn.Enabled = true;
}
else
{
MessageBox.Show(strResult, "Lưu Phiếu Thu", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public bool UpdatePhieuThu(PhieuThuDTO pt)
{
if (objPhieuThu.IsRowExists(pt.MaPhieuThu) && objNV.IsRowExists(pt.MaNhanVien) && objKH.IsRowExists(pt.MaKhachHang))
{
return(objPhieuThu.UpdateRow(pt));
}
else
{
return(false);
}
}
private void btnxoa_Click(object sender, EventArgs e)
{
PhieuThuDTO pt = new PhieuThuDTO();
pt.MaPhieuThu = int.Parse(txtmaphieuthu.Text);
if (PhieuThuBUS.XoaPhieuThu(pt) == false)
{
MessageBox.Show("Xóa thất bại", "Thông báo");
}
hienthiphieuthu();
}
public bool AddPhieuThu(PhieuThuDTO pt)
{
if (!objPhieuThu.IsRowExists(pt.MaPhieuThu))
{
return(objPhieuThu.AddRow(pt));
}
else
{
return(false);
}
}
public bool ThemPhieuThu(PhieuThuDTO pt)
{
if (ptDAO.GetPhieuThuByMa(pt.MaPhieuThu) == null)
{
ptDAO.Insert(pt);
return(true);
}
else
{
return(false);
}
}
public bool SuaPhieuThu(PhieuThuDTO pt)
{
if (ptDAO.GetPhieuThuByMa(pt.MaPhieuThu) != null)
{
ptDAO.Update(pt);
return(true);
}
else
{
return(false);
}
}
public static bool XoaPhieuThu(PhieuThuDTO pt)
{
if (PhieuThuDAO.GetPhieuThuByMa(pt.MaPhieuThu) != null)
{
PhieuThuDAO.Delete(pt);
return(true);
}
else
{
return(false);
}
}
public static bool Delete(PhieuThuDTO phieuThu)
{
try
{
string sql = "delete from PHIEUTHU where MaPhieuThu= " + phieuThu.MaPhieuThu + "";
DataAccess.ExcuNonQuery(sql);
return(true);
}
catch
{
return(false);
}
}
private void LuuPhieuThu()
{
PhieuThuDTO dtoPhieuThu = new PhieuThuDTO();
dtoPhieuThu.DiaChi = txtDiaChi.Text;
dtoPhieuThu.KemTheo = txtKemTheo.Text;
dtoPhieuThu.KhachHang = cmbKhachHang.Text;
dtoPhieuThu.LyDoThu = txtLyDoThu.Text;
dtoPhieuThu.SoPhieu = txtSoPhieu.Text;
dtoPhieuThu.SoTien = double.Parse(txtSoTien.Text);
dtoPhieuThu.VietBangChu = txtVietBangChu.Text;
Variable.dtoPhieuThu = dtoPhieuThu;
}
public async Task <IActionResult> Post([FromBody] PhieuThuDTO model)
{
using (var context = new UserProfileDbContext())
{
context.PhieuThus.Add(new PhieuThu
{
Id = model.Id,
NgayThu = model.NgayThu,
TongTien = model.TongTien,
IdNguoiThu = model.IdNguoiThu,
IdChiTietPhieuThu = model.IdChiTietPhieuThu
});
//context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[UserProfiles] ON");
return(Ok(await context.SaveChangesAsync()));
}
}
public bool InsertPhieuThu(PhieuThuDTO dtoPhieuThu)
{
string strQuery = "Insert Into PHIEUTHU Values(";
strQuery += "N'" + dtoPhieuThu.MaPhieuThu + "',";
strQuery += "N'" + dtoPhieuThu.MaNV + "',";
strQuery += "N'" + dtoPhieuThu.KhachHang + "',";
strQuery += "N'" + dtoPhieuThu.NgayLap + "',";
strQuery += "N'" + dtoPhieuThu.DiaChi + "',";
strQuery += "N'" + dtoPhieuThu.SoPhieu + "',";
strQuery += dtoPhieuThu.No + ",";
strQuery += dtoPhieuThu.Co + ",";
strQuery += "N'" + dtoPhieuThu.LyDoThu + "',";
strQuery += dtoPhieuThu.SoTien + ",";
strQuery += "N'" + dtoPhieuThu.VietBangChu + "',";
strQuery += "N'" + dtoPhieuThu.KemTheo + "')";
return(dp.ExecuteNonQuery(strQuery));
}
public async Task <IActionResult> Put(int id, [FromBody] PhieuThuDTO model)
{
using (var context = new UserProfileDbContext())
{
var user = await context.PhieuThus.FirstOrDefaultAsync(x => x.Id.Equals(id));
if (user == null)
{
return(NotFound());
}
user.Id = model.Id;
user.NgayThu = model.NgayThu;
user.TongTien = model.TongTien;
user.IdNguoiThu = model.IdNguoiThu;
user.IdChiTietPhieuThu = model.IdChiTietPhieuThu;
return(Ok(await context.SaveChangesAsync()));
}
}
public static PhieuThuDTO GetByMa(int ma)
{
string sql = "select * from PHIEUTHU where MaPhieuThu=" + ma + "";
DataTable dt = DataAccess.ExcuQuery(sql);
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
PhieuThuDTO phieuThu = new PhieuThuDTO();
phieuThu.MaPhieuThu = (int)dt.Rows[0].ItemArray[0];
//phieuThu.NgayThu = (DateTime)dt.Rows[0].ItemArray[1];
//phieuThu.SoTienThu = (int)dt.Rows[0].ItemArray[2];
//phieuThu.MaKhachHang = (int)dt.Rows[0].ItemArray[3];
return(phieuThu);
}
}
public bool UpdateRow(PhieuThuDTO obj)
{
try
{
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
//PhieuThu pt = this.GetRow(obj.MaPhieuThu);
//KhachHang kh = new KhachHangTable().GetRow(pt.MaKhachHang);
//kh.SoTienNo += pt.SoTienThu;
//kh.SoTienNo -= obj.SoTienThu;
//Console.WriteLine("ma khach hang: " + kh.MaKhachHang+" "+kh.SoTienNo+" "+pt.SoTienThu);
//new KhachHangTable().UpdateRow(kh);
string sql = "UPDATE [dbo].[PhieuThu]\n"
+ " SET [MaKhachHang] = @MaKhachHang\n"
+ " ,[MaNhanVien] = @MaNhanVien\n"
+ " ,[NgayThu] = @NgayThu\n"
+ " ,[SoTienThu] = @SoTienThu\n"
+ " ,[LyDoThu] = @LyDoThu\n"
+ " WHERE [MaPhieuThu] = @MaPhieuThu";
var cmd = new SqlCommand(sql, _connection);
cmd.Parameters.Add("@MaPhieuThu", SqlDbType.Char).Value = obj.MaPhieuThu;
cmd.Parameters.Add("@MaKhachHang", SqlDbType.Char).Value = obj.MaKhachHang;
cmd.Parameters.Add("@MaNhanVien", SqlDbType.Char).Value = obj.MaNhanVien;
cmd.Parameters.Add("@NgayThu", SqlDbType.Date).Value = obj.NgayThu;
cmd.Parameters.Add("@SoTienThu", SqlDbType.Money).Value = obj.SoTienThu;
cmd.Parameters.Add("@LyDoThu", SqlDbType.NVarChar).Value = obj.LyDoThu ?? (object)DBNull.Value;
cmd.ExecuteNonQuery();
_connection.Close();
return(true);
}
catch (Exception ex)
{
_connection.Close();
Console.WriteLine(ex.Message);
}
return(false);
}
public PhieuThuDTO GetPhieuThuByMa(string Ma)
{
string sql = "select * from PHIEUTHU where MaPhieuThu=" + Ma + "";
kn.Connect();
DataTable dt = kn.Select(CommandType.Text, sql, new SqlParameter {
ParameterName = "MaPhieuThu", Value = Ma
});
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
PhieuThuDTO pt = new PhieuThuDTO();
pt.MaPhieuThu = dt.Rows[0].ItemArray[0].ToString();
return(pt);
}
}
private void FrmInPhieuThu_Load(object sender, EventArgs e)
{
crpPhieuThu pc = new crpPhieuThu();
PhieuThuDTO dtoPhieuThu = new PhieuThuDTO();
TT_CongTyBLL bllCty = new TT_CongTyBLL();
dtoPhieuThu = Variable.dtoPhieuThu;
pc.SetParameterValue("SoPhieu", dtoPhieuThu.SoPhieu);
pc.SetParameterValue("NguoiNhanTien", dtoPhieuThu.KhachHang);
pc.SetParameterValue("DiaChiNguoiNhan", dtoPhieuThu.DiaChi);
pc.SetParameterValue("LyDoChi", dtoPhieuThu.LyDoThu);
pc.SetParameterValue("TienBangChu", dtoPhieuThu.VietBangChu);
pc.SetParameterValue("KemTheo", dtoPhieuThu.KemTheo);
pc.SetParameterValue("SoTien", dtoPhieuThu.SoTien);
TT_CongTyDTO dtoCty = new TT_CongTyDTO();
dtoCty = bllCty.GetThongTinDoanhNghiep();
pc.SetParameterValue("TenCongTy", dtoCty.TenCT);
pc.SetParameterValue("DiaChi", dtoCty.DiaChi);
cvrPhieuThu.ReportSource = pc;
}
public string selectAll(List <PhieuThuDTO> lsObj)
{
string query = string.Empty;
query += " SELECT *";
query += " FROM [tblphieuthu]";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand comm = new SqlCommand())
{
comm.Connection = conn;
comm.CommandType = CommandType.Text;
comm.CommandText = query;
try
{
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
if (reader.HasRows == true)
{
lsObj.Clear();
while (reader.Read())
{
PhieuThuDTO obj = new PhieuThuDTO();
obj.MaPhieuThu = reader["maphieuthu"].ToString();
obj.MaHoaDon = reader["mahd"].ToString();
obj.NgayThuTien = Convert.ToDateTime(reader["ngaythutien"].ToString());
obj.SoTienThu = Convert.ToInt32(reader["sotienthu"].ToString());
lsObj.Add(obj);
}
}
}
catch (Exception ex)
{
conn.Close();
return("Lấy phiếu thu thất bại\n" + ex.Message + "\n" + ex.StackTrace);
}
}
}
return("0");
}
public bool AddRow(PhieuThuDTO obj)
{
try
{
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
string sql = "INSERT INTO [dbo].[PhieuThu]\n"
+ " ([MaPhieuThu]\n"
+ " ,[MaKhachHang]\n"
+ " ,[MaNhanVien]\n"
+ " ,[NgayThu]\n"
+ " ,[SoTienThu]\n"
+ " ,[LyDoThu])\n"
+ " VALUES\n"
+ " (@MaPhieuThu\n"
+ " ,@MaKhachHang\n"
+ " ,@MaNhanVien\n"
+ " ,@NgayThu\n"
+ " ,@SoTienThu\n"
+ " ,@LyDoThu)";
SqlCommand cmd = new SqlCommand(sql, _connection);
cmd.Parameters.Add("@MaPhieuThu", SqlDbType.Char).Value = obj.MaPhieuThu;
cmd.Parameters.Add("@MaKhachHang", SqlDbType.Char).Value = obj.MaKhachHang;
cmd.Parameters.Add("@MaNhanVien", SqlDbType.Char).Value = obj.MaNhanVien;
cmd.Parameters.Add("@NgayThu", SqlDbType.Date).Value = obj.NgayThu;
cmd.Parameters.Add("@SoTienThu", SqlDbType.Money).Value = obj.SoTienThu;
cmd.Parameters.Add("@LyDoThu", SqlDbType.NVarChar).Value = obj.LyDoThu ?? (object)DBNull.Value;
cmd.ExecuteNonQuery();
_connection.Close();
return(true);
}
catch (Exception ex)
{
_connection.Close();
Console.WriteLine(ex.Message);
}
return(false);
}
public bool Delete(PhieuThuDTO pt)
{
try
{
string sql = "delete from PHIEUTHU where MaPhieuThu= " + pt.MaPhieuThu + "";
kn.Connect();
kn.ExecuteNonQuery(CommandType.Text, sql, new SqlParameter {
ParameterName = "MaPhieuThu", Value = pt.MaPhieuThu
});
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
kn.Disconnect();
}
}
private void btnthem_Click(object sender, EventArgs e)
{
PhieuThuDTO pt = new PhieuThuDTO();
try
{
pt.MaKhachHang = int.Parse(txtmakh.Text);
}
catch (FormatException)
{
MessageBox.Show("Mã khách hàng không được bỏ trống");
btnkh_Click(sender, e);
return;
}
pt.NgayThu = dtngaythutien.Value;
DataTable dt = ThamSoDAO.GetThamSoAll();
int ktchophep = int.Parse(dt.Rows[0].ItemArray[5].ToString());
DataTable dt2 = ThamSoDAO.SlectTienNoKH(int.Parse(txtmakh.Text));
int tienno = int.Parse(dt2.Rows[0].ItemArray[5].ToString());
Int64 tienthu = Int64.Parse(txtsotien.Text);
try
{
pt.SoTienThu = UInt64.Parse(txtsotien.Text);
}
catch (FormatException)
{
MessageBox.Show("Số tiền thu chưa nhập đúng quy định");
return;
}
catch (OverflowException)
{
MessageBox.Show("Số tiền thu không được âm");
return;
}
if (ktchophep == 1)
{
if (tienno < tienthu)
{
MessageBox.Show("Tiền thu đã lớn hơn tiền khách hàng đang nợ");
return;
}
}
Int64 tiennonew = tienno - tienthu;
if (PhieuThuBUS.ThemPhieuThu(pt) == false)
{
MessageBox.Show("Thêm thất bại", "Thông báo");
}
else
{
KhachHangDTO kh = new KhachHangDTO();
kh.MaKhachHang = int.Parse(txtmakh.Text);
kh.TienNo = tiennonew;
KhachHangDAO.UpdateTienNo(kh);
BaoCaoCongNoDTO cn = new BaoCaoCongNoDTO();
cn.MaKhachHang = kh.MaKhachHang;
cn.NgayPhatSinh = pt.NgayThu;
cn.NoDau = tienno;
cn.NoCuoi = tiennonew;
cn.PhatSinh = "Phiếu thu tiền";
BaoCaoCongNoDAO.Insert(cn);
MessageBox.Show("Thành công");
hienthiphieuthu();
}
}
public string insert(PhieuThuDTO obj)
{
return(dal.insert(obj));
}
private void btncapnhat_Click(object sender, EventArgs e)
{
PhieuThuDTO pt = new PhieuThuDTO();
pt.MaPhieuThu = txtmaphieuthu.Text;
pt.NgayThu = dtngaythutien.Value;
try
{
string kh = txtmakh.Text;
}
catch (FormatException)
{
MessageBox.Show("Mã khách hàng không được bỏ trống");
btnkh_Click(sender, e);
return;
}
DataTable dt = tsDAO.GetAllThamSo();
int ktchophep = int.Parse(dt.Rows[0].ItemArray[5].ToString());
DataTable dt2 = tsDAO.GetTienNoKH(txtmakh.Text);
int tienno = int.Parse(dt2.Rows[0].ItemArray[5].ToString());
int tienthu = int.Parse(txtsotien.Text);
try
{
pt.SoTienThu = int.Parse(txtsotien.Text);
}
catch (FormatException)
{
MessageBox.Show("Số tiền thu chưa nhập đúng quy định");
return;
}
catch (OverflowException)
{
MessageBox.Show("Số tiền thu không được âm");
return;
}
if (ktchophep == 1)
{
if (tienno < tienthu)
{
MessageBox.Show("Tiền thu đã lớn hơn tiền khách hàng đang nợ");
return;
}
}
int tienthunew = tienthu - luusotienthu;
int tiennonew = tienno - tienthunew;
if (ptBUS.SuaPhieuThu(pt) == false)
{
MessageBox.Show("Sửa thất bại", "Thông báo");
}
else
{
KhachHangDTO kh = new KhachHangDTO();
kh.MaKhachHang = txtmakh.Text;
kh.TienNo = tiennonew;
khDAO.UpdateTienNoKH(kh);
BaoCaoCongNoDTO cn = new BaoCaoCongNoDTO();
cn.MaKhachHang = kh.MaKhachHang;
cn.NgayPhatSinh = pt.NgayThu;
cn.NoDau = tienno;
cn.NoCuoi = tiennonew;
cn.PhatSinh = "Cập nhật phiếu thu tiền";
bccnDAO.Insert(cn);
MessageBox.Show("Thành công");
hienthiphieuthu();
}
}
public static void Insert(PhieuThuDTO phieuThu)
{
string sql = "insert into PHIEUTHU(NgayThu,SoTienThu,MaKhachHang) values('" + phieuThu.NgayThu + "'," + phieuThu.SoTienThu + "," + phieuThu.MaKhachHang + ")";
DataAccess.ExcuNonQuery(sql);
}
public static void Update(PhieuThuDTO phieuThu)
{
string sql = "Update PHIEUTHU set NgayThu =('" + phieuThu.NgayThu + "'),SoTienThu=(" + phieuThu.SoTienThu + ") where MaPhieuThu=" + phieuThu.MaPhieuThu + "";
DataAccess.ExcuNonQuery(sql);
}
