private void simpleButton1_Click_1(object sender, EventArgs e)
{
int idDaiLy = dsIdDL[cbDaiLy.SelectedIndex];
DateTime ngayXuat = dtNgayLap.Value;
//Console.WriteLine(ngayXuat.ToString("yyyy-MM-dd"));
float tienCon = (float)Convert.ToDouble(txtTienCon.Text);
float tienNo = BUS_DaiLy.GetTienNo(idDaiLy);
float tienNoMoi = tienCon + tienNo;
int kqUpdate = BUS_DaiLy.UpdateTienNo(idDaiLy, tienNoMoi);
if (kqUpdate == 1)
{
string cmnd = taiKhoan.Cmnd;
DTO_PhieuXuatHang phieuXuatHang = new DTO_PhieuXuatHang(0, ngayXuat, idDaiLy, cmnd);
int idPhieuXuat = BUS_PhieuXuat.ThemPhieuXuatGetId(phieuXuatHang);
foreach (DataRow r in tb.Rows)
{
DTO_ChiTietXuat ctx = new DTO_ChiTietXuat(idPhieuXuat, (int)r[0], (int)r[2], (float)r[4], r[3].ToString(), (float)r[5]);
BUS_ChiTietXuat.ThemChiTietXuat(ctx);
}
}
else
{
}
}
//public void Xoapxbyid(int id)
//{
// string query = "delete PhieuXuatHang where IdPhieuXuat=" + id;
//}
/*
* CapNhatPhieuXuat trả về
* 1:thành công
* 0:thất bại
*/
public static int CapNhatPhieuXuat(DTO_PhieuXuatHang pxh)
{
string ngayXuatSql = pxh.NgayXuat.ToString("yyyy-MM-dd");
string query = "update PhieuXuatHang set NgayXuat ='" + ngayXuatSql + "',IdDaiLy=" + pxh.IdDaiLy + " ,CMND='" + pxh.Cmnd + "' where IdPhieuXuat=" + pxh.IdPhieuXuat;
int result = ResultQuery.GetResultQuery(conn, query);
return(result);
}
/*
* ThemPhieuXuat trả về
* 1:thành công
* 0:thất bại
*/
public static int ThemPhieuXuat(DTO_PhieuXuatHang pxh)
{
int kq = 0;
string ngayXuatSql = pxh.NgayXuat.ToString("yyyy-MM-dd");
string query = "insert into PhieuXuatHang(NgayXuat,IdDaiLy,CMND) values ('" + ngayXuatSql + "'," + pxh.IdDaiLy + ",'" + pxh.Cmnd + "')";
kq = ResultQuery.GetResultQuery(conn, query);
return(kq);
}
public List <DTO_PhieuXuatHang> hienthidoanhso(int startmonth, int startyear, int endmonth, int endyear)
{
List <DTO_PhieuXuatHang> List = new List <DTO_PhieuXuatHang>();
String query = string.Empty;
//query = "select b.id,tenDL,tyle,sophieuxuat,tongtrigia from tblBaoCaoDoanhSo b join tbldaily d on b.manv = d.id where maTG in (select id from tblThoiGian where( (nam > @startyear and nam < @endyear) or (nam = @startyear and thang >= @startmonth and nam < @endyear ) or (nam = @endyear and thang <= @endmonth and nam > @startyear) or ( nam = @startyear and nam = @endyear and thang >= @startmonth and thang <= @endmonth) ))";
query = "select * from tblhoadonxuat as hd where ( ( (YEAR(hd.ngayxuat) > @startyear and YEAR(hd.ngayxuat) < @endyear) or (YEAR(hd.ngayxuat) = @startyear and MONTH(hd.ngayxuat) >= @startmonth and YEAR(hd.ngayxuat) < @endyear ) or (YEAR(hd.ngayxuat) = @endyear and MONTH(hd.ngayxuat) <= @endmonth and YEAR(hd.ngayxuat) > @startyear) or ( YEAR(hd.ngayxuat) = @startyear and YEAR(hd.ngayxuat) = @endyear and MONTH(hd.ngayxuat) >= @startmonth and MONTH(hd.ngayxuat) <= @endmonth) ) )";
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("@startmonth", startmonth);
cmd.Parameters.AddWithValue("@startyear", startyear);
cmd.Parameters.AddWithValue("@endmonth", endmonth);
cmd.Parameters.AddWithValue("@endyear", endyear);
try {
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
//DTO_BaoCaoDoanhSo bcds = new DTO_BaoCaoDoanhSo();
//bcds.Id = long.Parse(reader["id"].ToString());
//bcds.Tendaily = reader["tenDL"].ToString();
//bcds.Tyle = float.Parse(reader["tyle"].ToString());
//bcds.Sophieuxuat = int.Parse(reader["soPhieuXuat"].ToString());
//bcds.Tongtrigia = (uint)reader.GetDecimal(4);
DTO_PhieuXuatHang px = new DTO_PhieuXuatHang();
px.Id = long.Parse(reader["id"].ToString());
px.MaNV = long.Parse(reader["manv"].ToString());
px.MaKH = long.Parse(reader["makh"].ToString());
px.NgayLapPhieu = reader.GetDateTime(3);
px.TongTriGia = (uint)reader.GetDecimal(4);
List.Add(px);
}
}
con.Close();
con.Dispose();
} catch {
con.Close();
return(null);
}
listpx = List;
return(List);
}
}
}
public List <DTO_PhieuXuatHang> TimKiemPhieuXuatHang(string tukhoa)
{
List <DTO_PhieuXuatHang> ds = new List <DTO_PhieuXuatHang>();
string query = string.Empty;
query += "SELECT * FROM [tblPhieuXuat]";
query += "WHERE [maDL] = @tukhoa";
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("@tukhoa", tukhoa);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
DTO_PhieuXuatHang pxh = new DTO_PhieuXuatHang();
pxh.Id = long.Parse(reader["id"].ToString());
pxh.MaDl = long.Parse(reader["maDL"].ToString());
pxh.NgayLapPhieu = reader.GetDateTime(2);
pxh.TongTriGia = (uint)reader.GetDecimal(3);
ds.Add(pxh);
}
}
con.Close();
con.Dispose();
}
catch
{
con.Close();
return(null);
}
}
}
return(ds);
}
private void BtnThemPhieu_Click(object sender, EventArgs e)
{
if (btnThemPhieu.Text == "Thêm phiếu xuất hàng")
{
btnThemPhieu.Text = "Lưu";
btnSua.Enabled = false;
btnXoa.Text = "Hủy";
btnXoa.Enabled = true;
SetDefault(true);
txtMaPhieuXuat.Focus();
}
else
{
DialogResult result = MessageBox.Show("Bạn chắc chắn muốn thêm đại lý", "THÊM ĐẠI LÝ", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
if (KiemTra())
{
DTO_PhieuXuatHang pxh = new DTO_PhieuXuatHang();
//pxh.MaDl = long.Parse(this.cbbMaDaiLy.Text);
pxh.MaNV = long.Parse(cbManv.SelectedValue.ToString());
pxh.MaKH = long.Parse(cbMakh.SelectedValue.ToString());
pxh.NgayLapPhieu = this.dNgayTiepNhan.Value;
pxh.TongTriGia = uint.Parse(this.txtTongTriGia.Text);
if (phieuxuat.ThemPhieuXuat(pxh))
{
btnThemPhieu.Text = "Thêm phiếu xuất hàng";
btnXoa.Text = "Xóa";
dataPhieuXuat.DataSource = phieuxuat.laydanhsach();
CurrencyManager myCurrencyManager = (CurrencyManager)this.BindingContext[dataPhieuXuat.DataSource];
myCurrencyManager.Refresh();
SetDefault(false);
ResetValue();
MessageBox.Show("Thêm phiếu xuất thành công", "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
MessageBox.Show("Vui lòng kiểm tra lại quy định và dữ liệu", "Thêm phiếu xuất thất bại", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}
private void btnThem_Click(object sender, EventArgs e)
{
if (txtTienCon.Text.Equals("") == false)
{
int idDaiLy = dsIdDL[cbDaiLy.SelectedIndex];
DateTime ngayXuat = dtNgayLap.Value;
//Console.WriteLine(ngayXuat.ToString("yyyy-MM-dd"));
float tienCon = (float)Convert.ToDouble(txtTienCon.Text);
float tienNo = BUS_DaiLy.GetTienNo(idDaiLy);
Console.WriteLine(tienNo);
float tienNoMoi = tienCon + tienNo;
int kqUpdate = BUS_DaiLy.UpdateTienNo(idDaiLy, tienNoMoi);
if (kqUpdate == 1)
{
string cmnd = taiKhoan.Cmnd;
DTO_PhieuXuatHang phieuXuatHang = new DTO_PhieuXuatHang(0, ngayXuat, idDaiLy, cmnd);
int idPhieuXuat = BUS_PhieuXuat.ThemPhieuXuatGetId(phieuXuatHang);
foreach (DataRow r in tb.Rows)
{
int idMatHang = (int)r[0];
int soLuong = int.Parse(r[2].ToString());
float donGia = (float)Convert.ToDouble(r[4]);
string donViTinh = r[3].ToString();
float thanhTien = (float)Convert.ToDouble(r[5]);
// DTO_ChiTietXuat ctx = new DTO_ChiTietXuat(idPhieuXuat, (int)r[0], (int)r[2], (float)Convert.ToDouble(r[4]), r[3].ToString(), (float)Convert.ToDouble(r[5]));
DTO_ChiTietXuat ctx = new DTO_ChiTietXuat(idPhieuXuat, idMatHang, soLuong, donGia, donViTinh, thanhTien);
BUS_ChiTietXuat.ThemChiTietXuat(ctx);
}
MessageBox.Show("Xuất thành công");
}
else if (kqUpdate == 2)
{
MessageBox.Show("Số tiền còn lại cộng với nợ cũ vượt quy định");
}
else
{
MessageBox.Show("Xuất thất bại");
}
//tb.Columns.Add("Id", typeof(int));
//tb.Columns.Add("Mặt hàng", typeof(string));
//tb.Columns.Add("Số lượng", typeof(string));
//tb.Columns.Add("đơn vị tính", typeof(string));
//tb.Columns.Add("Đơn giá", typeof(string));
//tb.Columns.Add("Thành tiền", typeof(string));
}
}
public List <DTO_PhieuXuatHang> LayDanhSachPhieuXuat()
{
List <DTO_PhieuXuatHang> ds = new List <DTO_PhieuXuatHang>();
string query = string.Empty;
query = "select * from tblhoadonxuat ";
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
DTO_PhieuXuatHang pxh = new DTO_PhieuXuatHang();
pxh.Id = long.Parse(reader["id"].ToString());
pxh.MaNV = long.Parse(reader["manv"].ToString());
pxh.MaKH = long.Parse(reader["makh"].ToString());
pxh.NgayLapPhieu = reader.GetDateTime(3);
pxh.TongTriGia = (uint)reader.GetDecimal(4);
ds.Add(pxh);
}
}
con.Close();
con.Dispose();
}
catch
{
con.Close();
return(null);
}
}
}
return(ds);
}
public bool SuaPhieuXuat(DTO_PhieuXuatHang pxh)
{
string query = string.Empty;
query = "UPDATE [tblhoadonxuat] " +
"SET [manv] = @manv , [makh] = @makh , [tongtien] = @tongtien " +
"WHERE [id] = @id ";
//query = "SuaDaiLy";
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
//cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@manv", pxh.MaNV);
cmd.Parameters.AddWithValue("@makh", pxh.MaKH);
cmd.Parameters.AddWithValue("@tongtien", Decimal.Parse(pxh.TongTriGia.ToString()));
cmd.Parameters.AddWithValue("@id", pxh.Id);
try
{
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
con.Close();
con.Dispose();
return(true);
}
else
{
con.Close();
return(false);
}
}
catch
{
con.Close();
return(false);
}
}
}
}
public bool ThemPhieuXuat(DTO_PhieuXuatHang pxh)
{
string query = string.Empty;
query += "INSERT INTO [tblhoadonxuat] ([manv], [makh], [ngayxuat], [tongtien]) ";
query += "VALUES (@manv, @makh, @ngayxuat, @tongtien)";
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("@manv", pxh.MaNV);
cmd.Parameters.AddWithValue("@makh", pxh.MaKH);
cmd.Parameters.AddWithValue("@ngayxuat", pxh.NgayLapPhieu);
cmd.Parameters.AddWithValue("@tongtien", Decimal.Parse(pxh.TongTriGia.ToString()));
try
{
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
con.Close();
con.Dispose();
return(true);
}
else
{
con.Close();
return(false);
}
}
catch
{
con.Close();
return(false);
}
}
}
}
public static int ThemPhieuXuatTraVeId(DTO_PhieuXuatHang px)
{
int kq;
string ngayXuatSql = px.NgayXuat.ToString("yyyy-MM-dd");
SqlCommand cmd = new SqlCommand("PR_InsertPhieuXuat", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@NgayXuat", SqlDbType.DateTime).Value = ngayXuatSql;
cmd.Parameters.Add("@IdDaiLy", SqlDbType.Int).Value = px.IdDaiLy;
cmd.Parameters.Add("@CMND", SqlDbType.VarChar).Value = px.Cmnd;
cmd.Parameters.Add("@out", SqlDbType.Int).Direction = ParameterDirection.Output;
try
{
conn.Open();
cmd.ExecuteNonQuery();
kq = (int)cmd.Parameters["@out"].Value;
return(kq);
}
finally
{
conn.Close();
}
}
public static ArrayList DSPhieuXuat()
{
ArrayList arrList = new ArrayList();
DataTable dt = new DataTable();
dt = ResultQuery.GetTableResult(conn, "SELECT * FROM PhieuXuatHang ");
int idPhieuXuat;
DateTime ngayXuat;
int idDaiLy;
string cmnd;
foreach (DataRow r in dt.Rows)
{
idPhieuXuat = (int)r["IdPhieuXuat"];
ngayXuat = Convert.ToDateTime(r["NgayXuat"]);
idDaiLy = (int)r["IdDaiLy"];
cmnd = r["CMND"].ToString();
DTO_PhieuXuatHang phieuXuat = new DTO_PhieuXuatHang(idPhieuXuat, ngayXuat, idDaiLy, cmnd);
arrList.Add(phieuXuat);
}
return(arrList);
}
public static int ThemPhieuXuatGetId(DTO_PhieuXuatHang px)
{
return(DAL_PhieuXuat.ThemPhieuXuatTraVeId(px));
}
public bool SuaPhieuXuat(DTO_PhieuXuatHang Phieuxh)
{
return(pxh.SuaPhieuXuat(Phieuxh));
}
public bool ThemPhieuXuat(DTO_PhieuXuatHang Phieuxh)
{
return(pxh.ThemPhieuXuat(Phieuxh));
}
