public DataTable tim(CongNoDTO cn)
{
DataTable loadSearch = new DataTable();
loadSearch = cnDAL.searchCongNo(cn);
return(loadSearch);
}
public static int updateDeb(CongNoDTO deb)
{
string querry = @"UPDATE CongNo SET NoDau=" + deb.NoDau + ", NoCuoi=" + deb.NoCuoi + ", PhatSinh=" + deb.PhatSinh + "WHERE Id=" + deb.Id;
int result = DataProvider.ExecuteNonQuerry(querry);
return(result);
}
private void tim_btn_Click(object sender, EventArgs e)
{
CongNoDTO cn = new CongNoDTO();
cn.thangCongNo = int.Parse(Thang_combo.Text);
congNoGrid.DataSource = cnBus.tim(cn);
}
public DataTable searchCongNo(CongNoDTO cn)
{
DataTable loadSearch = new DataTable();
string query = string.Empty;
query += "Select [maCongNo] as N'Mã Công Nợ', [maDaiLy] as N'Mã Đại Lý', [ngayCongNo] as N'Ngày Công Nợ', [noDau] as N'Nợ Đầu' , [phatSinh] as N'Phát Sinh', [noCuoi] as N'Nợ Cuối' from [BaoCaoCongNo]";
query += " where DATEPART(mm, [ngayCongNo]) = @thangCongNo";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@thangCongNo", cn.thangCongNo);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
loadSearch.Load(reader);
con.Close();
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
}
return(loadSearch);
}
public bool Them(CongNoDTO cn)
{
string query = string.Empty;
query += "Insert into [BaoCaoCongNo]([maCongNo], [maDaiLy], [ngayCongNo], [noDau], [phatSinh] , [noCuoi]) ";
query += "Values(@maCongNo , @maDaiLy, @ngayCongNo, @noDau, @phatSinh , @noCuoi)";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@maCongNo", cn.maCongNo);
cmd.Parameters.AddWithValue("@maDaiLy", cn.maDaiLy);
cmd.Parameters.AddWithValue("@ngayCongNo", cn.ngayCongNo);
cmd.Parameters.AddWithValue("@noDau", cn.noDau);
cmd.Parameters.AddWithValue("@phatSinh", cn.phatSinh);
cmd.Parameters.AddWithValue("@noCuoi", cn.noCuoi);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
query = string.Empty;
query += "Update [DaiLy] set [DaiLy].[tienNo] = (select sum([BaoCaoCongNo].[noCuoi]) from [BaoCaoCongNo] where [BaoCaoCongNo].[maDaiLy] = @maDaiLy) where [DaiLy].[maDaiLy] = @maDaiLy";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@maDaiLy", cn.maDaiLy);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
return(true);
}
public static string addDeb(CongNoDTO deb)
{
string querry = @"INSERT INTO CongNo (NoDau, NoCuoi, PhatSinh) VALUES (" + deb.NoDau + ", " + deb.NoCuoi +
", " + deb.PhatSinh + ")";
int result = DataProvider.ExecuteNonQuerry(querry);
querry = @"SELECT IDENT_CURRENT('CongNo') AS Id";
string id = DataProvider.ExecuteQuerry(querry).Rows[0]["Id"].ToString();
return(id);
}
public static CongNoDTO getDeb(string Id)
{
string querry = @"SELECT * FROM CongNo WHERE Id=" + Id;
DataTable table = DataProvider.ExecuteQuerry(querry);
CongNoDTO result = new CongNoDTO();
result.Id = table.Rows[0]["Id"].ToString();
result.NoDau = table.Rows[0]["NoDau"].ToString();
result.NoCuoi = table.Rows[0]["NoCuoi"].ToString();
result.PhatSinh = table.Rows[0]["PhatSinh"].ToString();
return(result);
}
public bool Xoa(CongNoDTO cn)
{
string query = string.Empty;
query += "Update [DaiLy] set [DaiLy].[tienNo] = ((select [DaiLy].[tienNo] where [DaiLy].[maDaiLy] = (Select [BaoCaoCongNo].[maDaiLy] from [BaoCaoCongNo] where [BaoCaoCongNo].[maCongNo] = @maCongNo)) - ";
query += "(select [BaoCaoCongNo].[noCuoi] from [BaoCaoCongNo] where [BaoCaoCongNo].[maCongNo] = @maCongNo))";
query += "where [DaiLy].[maDaiLy] = (select [BaoCaoCongNo].[maDaiLy] from [BaoCaoCongNo] where [BaoCaoCongNo].[maCongNo] = @maCongNo)";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@maCongNo", cn.maCongNo);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
query = string.Empty;
query += "Delete from [BaoCaoCongNo] where [BaoCaoCongNo].[maCongNo] = @maCongNo";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@maCongNo", cn.maCongNo);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
return(true);
}
private void xoa_btn_Click(object sender, EventArgs e)
{
CongNoDTO cn = new CongNoDTO();
cn.maCongNo = int.Parse(maCongNo.Text);
bool kq = cnBus.xoa(cn);
if (kq == true)
{
MessageBox.Show("Xóa Công Nợ Thành Công");
}
else
{
MessageBox.Show("Xóa công nợ thất bại");
}
}
private void Confirm_btn_Click(object sender, EventArgs e)
{
CongNoDTO cn = new CongNoDTO();
cn.maCongNo = int.Parse(maCongNo.Text);
cn.maDaiLy = int.Parse(maDaiLy.Text);
cn.ngayCongNo = ngayCongNo.Value;
cn.noDau = int.Parse(noDau.Text);
cn.phatSinh = int.Parse(phatSinh.Text);
cn.noCuoi = cn.noDau + cn.phatSinh;
bool kq = cnBus.them(cn);
if (kq == true)
{
MessageBox.Show("Thêm thành công");
}
else
{
MessageBox.Show("Thêm thất bại");
}
dataGridView.DataSource = cnBus.loadCongNo();
}
public bool xoa(CongNoDTO cn)
{
bool re = cnDAL.Xoa(cn);
return(re);
}
public bool them(CongNoDTO cn)
{
bool re = cnDAL.Them(cn);
return(re);
}
public static int updateDeb(CongNoDTO deb) => CongNoDAO.updateDeb(deb);
public static string addDeb(CongNoDTO congno) => CongNoDAO.addDeb(congno);