private void DocCarMoi()
{
ketnoi kn = new ketnoi();
gridControl1.DataSource = kn.laybang(@"select top 0 SBB='',
NgayPhatHanh=GetDate(),TaiLieu='',MoTaVanDe='',
PhanTichNguyenNhan='',HanhDongKPPN='',NgayHoanThanh=GETDATE(),
NguoiThucHien='',NguoiDuyet='',GiamSat='',NgayGiamSat=GETDATE(),
YKienBoSung='',KetQuaThoaMan='',KetQuaKhongThoaMan='',
GhiChuCarMoi='',NguoiGhi='' from tblCAR" );
kn.dongketnoi();
this.gridView1.Appearance.Row.TextOptions.WordWrap = DevExpress.Utils.WordWrap.Wrap;
}
private void LoadItemDatHang()
{
ketnoi Connect = new ketnoi();
repositoryItemDSDatHang.DataSource = Connect.laybang(@"select Iden,madh +';'
+ MaSP +'; '+Tenquicach+'; '+ngoaiquang +'; '
+ Khachhang Thongtin from tblDHCT");
repositoryItemDSDatHang.DisplayMember = "Iden";
repositoryItemDSDatHang.ValueMember = "Iden";
repositoryItemDSDatHang.NullText = @"Chọn id sản phẩm";
Iden.ColumnEdit = repositoryItemDSDatHang;
Connect.dongketnoi();
}
private void GlEditDoiTuongTinhGia()//Danh muc ky chi phi
{
ketnoi Connect = new ketnoi();
cbMaz.Properties.DataSource = Connect.laybang("select cp.id,kb.maz,masp,kb.tenz,cp.Kytinhgia from tblz_khaibao kb "
+ " left outer join tblz_doitongtaphopchiphi cp on cp.Maz=kb.maz");
cbMaz.Properties.DisplayMember = "maz";
cbMaz.Properties.ValueMember = "maz";
cbMaz.Properties.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.Standard;
cbMaz.Properties.BestFitMode = DevExpress.XtraEditors.Controls.BestFitMode.BestFitResizePopup;
cbMaz.Properties.ImmediatePopup = true;
Connect.dongketnoi();
}
private void BtnXuatphieuZ_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
ketnoi kn = new ketnoi();
dt = kn.laybang("select * from ViewRPZKEHOACH where id_baogia like N'" + txtMabaogia.Text + "'");
XR_ZDinhGia DUTOAN = new XR_ZDinhGia();
DUTOAN.DataSource = dt;
DUTOAN.DataMember = "Table";
DUTOAN.ShowPreviewDialog();
kn.dongketnoi();
}
private void BtnXuatPhieu_Click(object sender, EventArgs e)//Xuat Phieu dinh muc cong doan san xuat
{
DataTable dt = new DataTable();
ketnoi kn = new ketnoi();
dt = kn.laybang("select * from tblDMuc_LaoDong where Masp like N'" + txtMasp.Text + "'");
XRCongDoan CongDoan = new XRCongDoan();
CongDoan.DataSource = dt;
CongDoan.DataMember = "Table";
CongDoan.ShowPreviewDialog();
kn.dongketnoi();
}
private void btnAll_Click(object sender, EventArgs e)
{
ketnoi Connect = new ketnoi();
gridLookUpGiaDoiChieu.Properties.DataSource = Connect.laybang(@"SELECT Masp,Tenquicach,dongia,thoigianthaydoi
FROM tblDHCT where (MaSP <>'' and dongia >1)" );
gridLookUpGiaDoiChieu.Properties.DisplayMember = "Masp";
gridLookUpGiaDoiChieu.Properties.ValueMember = "Masp";
gridLookUpGiaDoiChieu.Properties.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.Standard;
gridLookUpGiaDoiChieu.Properties.BestFitMode = DevExpress.XtraEditors.Controls.BestFitMode.BestFitResizePopup;
gridLookUpGiaDoiChieu.Properties.ImmediatePopup = true;
Connect.dongketnoi();
}
private void btnPrint_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
ketnoi kn = new ketnoi();
dt = kn.laybang(@"SELECT IDSP,DanhGia,mabv,madh,SPLR,sanpham,soluongyc,BTPT11,
KetThucTo11,AllProgress,STATUS,ngoaiquang
FROM PHANTICH_CHUNG WHERE STATUS =N'Trễ' or STATUS =N'Đỏ'");
RpDonHangTre rpDonHangTre = new RpDonHangTre();
rpDonHangTre.DataSource = dt;
rpDonHangTre.DataMember = "Table";
rpDonHangTre.ShowPreviewDialog();
}
private void LoadDANHMUC_SANPHAM()
{
ketnoi Connect = new ketnoi();
gridControl2.DataSource = Connect.laybang("SELECT DHCT.Iden,Tenquicach,DHCT.Codedetail, "
+ "DHCT.madh,DHCT.Khachhang,DHCT.MaPo,DHCT.MaSP,dvt,SPCT.Mact,SPCT.Ten_ct,SPCT.Soluong_CT, "
+ "Mau_banve, Tonkho, Soluong, ngaygiao, ngoaiquang, "
+ "DHCT.Code, usd, tygia, ghichu, DHCT.nguoithaydoi, DHCT.thoigianthaydoi, pheduyet, Trangthai, "
+ "(case when pheduyet = N'Đã phê duyệt' then 'TRUE' else 'FALSE' end) Muc, "
+ "Tenkhachhang,Masp_KH,DHCT.MaKH,DH.Diengiai FROM dbo.tblDHCT DHCT "
+ "left outer join tblSANPHAM_CT SPCT on DHCT.MaSP = SPCT.Masp "
+ "left outer join tblDONHANG DH on DHCT.madh = DH.madh where DHCT.madh like N'" + lookupPSX.Text + "'");
Connect.dongketnoi();
}
private void ShowMetail()
{
repositoryItemComboBoxVatLieu.Items.Clear();
ketnoi kn = new ketnoi();
string sqlQuery =
string.Format(@"select TenVatlieu from tblVatLieuSanPham");
var data = kn.laybang(sqlQuery);
for (int i = 0; i < data.Rows.Count; i++)
{
repositoryItemComboBoxVatLieu.Items.Add(data.Rows[i]["TenVatlieu"]);
}
kn.dongketnoi();
}
private void DS()
{
ketnoi kn = new ketnoi();
DataTable dt = new DataTable();
dt = kn.laybang("select Nguoi,Ngay,TyleNangsuat,Ma_NguonLuc,id,Madh,PR.Masp,SP.Tensp,Ma_CongDoan,CongDoan,Thoigian_Dinhmuc,Soluong_DonHang, "
+ "TG_ChuanBi,TG_DuTru,CASE BatDau WHEN '1900-01-01' THEN Null ELSE BatDau END AS BatDau, "
+ "CASE KetThuc WHEN '1900-01-01' THEN Null ELSE KetThuc END AS KetThuc,WorkDay,TG_DUTINH, "
+ "TONGTG_DUTINH,TONGTG_DUTINHTYLE,idDonHang,PR.Ma_BoPhan,PB.To_bophan,ID_DMLD "
+ "from tblCalender_Product PR left outer join tblPHONGBAN PB on PR.Ma_BoPhan=PB.Ma_bophan "
+ "left outer join tblSANPHAM SP on SP.Masp = PR.Masp where Madh like N'" + txtMaDH.Text + "'");
gridControl2.DataSource = dt;
kn.dongketnoi(); gridView2.ExpandAllGroups();
}
private void TaoMoiNguyenCong()
{
ketnoi kn = new ketnoi();
gridControl2.DataSource = kn.laybang(@"SELECT TOP 1 MaNC='',NguyenCong='',ChiTietNC='',
TenNC='',SoChiTiet='',BoPhan='',DonGia='',DinhMuc='' FROM NguyenCong");
kn.dongketnoi();
gridView2.Columns["MaSP"].Visible = false;
gridView2.Columns["SanPham"].Visible = false;
gridView2.Columns["SanPham"].GroupIndex = -1;
gridView2.ExpandAllGroups();
this.gridView2.OptionsView.NewItemRowPosition
= DevExpress.XtraGrid.Views.Grid.NewItemRowPosition.Top;
}
private void btnprintdondathang_Click(object sender, EventArgs e)//IN ĐƠN ĐẶT HÀNG VẬT TƯ
{
DataTable dt = new DataTable();
ketnoi kn = new ketnoi();
dt = kn.laybang("SELECT INDONHANG.*, tblDONHANG.* "
+ "FROM INDONHANG INNER JOIN "
+ "tblDONHANG ON INDONHANG.Code = tblDONHANG.Code where INDONHANG.madh like N'" + cbMaPhieudexuat.Text + "'");
RpDatHangVatTu rpDatHangVatTu = new RpDatHangVatTu();
rpDatHangVatTu.DataSource = dt;
rpDatHangVatTu.DataMember = "Table";
rpDatHangVatTu.ShowPreviewDialog();
}
private void DocDanhMucNguonLuc()
{
ketnoi Connect = new ketnoi();
repositoryItemGridLookUpEdit1.DataSource = Connect.laybang("select Ma_Nguonluc,Ten_Nguonluc from tblResources");
repositoryItemGridLookUpEdit1.DisplayMember = "Ten_Nguonluc";
repositoryItemGridLookUpEdit1.ValueMember = "Ten_Nguonluc";
repositoryItemGridLookUpEdit1.NullText = null;
repositoryItemGridLookUpEdit1.View.OptionsView.ShowFilterPanelMode = DevExpress.XtraGrid.Views.Base.ShowFilterPanelMode.Default;
repositoryItemGridLookUpEdit1.View.OptionsView.ShowAutoFilterRow = true;
repositoryItemGridLookUpEdit1.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.Standard;
repositoryItemGridLookUpEdit1.ImmediatePopup = true;
Connect.dongketnoi();
}
private void DanhMucCongDoan()
{
ketnoi kn = new ketnoi();
grDanhMucCongDoan.DataSource = kn.laybang(@"select DonGiaHeSo,HeSo,ChiTietSanPham,SoChiTietSanPham,
case when TrungCongDoan='x'
then 0 else SoChiTiet*Dongia_CongDoan end DonGiaBoSanPham,SoChiTiet,
TrungCongDoan,NguyenCong,id,Ngayghi,LD.Masp,LD.Tensp,Macongdoan,Tencondoan,Dinhmuc,
Dongia_CongDoan,Tothuchien,Nguoilap,LD.Ngaylap,Trangthai,LD.DonGia_ApDung,
LD.NgayApDung from tblDMuc_LaoDong LD left outer join tblSANPHAM SP
on LD.Masp=SP.Masp where LD.Masp = N'" + txtMasp.Text + "' ");
kn.dongketnoi();
gvDanhMucCongDoan.ExpandAllGroups();
}
private void ListDM_CONGDOANSX(string masanpham)//Danh mục công đoan sản xuất
{
ketnoi kn = new ketnoi();
grDanhMucCongDoan.DataSource = kn.laybang(@"select DonGiaHeSo,HeSo,ChiTietSanPham,SoChiTietSanPham,
case when TrungCongDoan='x'
then 0 else SoChiTiet*Dongia_CongDoan end DonGiaBoSanPham,
SoChiTiet,TrungCongDoan,NguyenCong,id,Ngayghi,LD.Masp,LD.Tensp,Macongdoan,Tencondoan,Dinhmuc,
Dongia_CongDoan,Tothuchien,Nguoilap,LD.Ngaylap,Trangthai,LD.DonGia_ApDung,
LD.NgayApDung from tblDMuc_LaoDong LD left outer join tblSANPHAM SP
on LD.Masp=SP.Masp" );
kn.dongketnoi();
gvDanhMucCongDoan.ExpandAllGroups();
}
private void Xoa(object sender, EventArgs e) //Xóa vật liệu phụ nhập kho
{
UpdateZero(); //Trả giá trị xuất hàng hóa về 0
ketnoi kn = new ketnoi();
gridControl2.DataSource = kn.xulydulieu("delete from tblNHAP_VATLIEUPHU where id like " + txtId.Text + "");
kn.dongketnoi();
UpdateNhapKho(); //group số lượng nhập kho
GridlookupEditMaVatTu(); //Load danh mục xuất nhập tồn
DMNhapXuatTon(); //Lấy số lượng nhập xuất tồn từ Danh mục vật tư
TruTonKho(); //Trừ lấy số tồn kho
ListNK_NhapKho(); //Load lại danh mục nhập kho
UpdateTonKho(); //Cập nhật tồn kho vào danh mục xuất nhập tồn
}
private void LOADDMKHUON_DHSX()
{
ketnoi kn = new ketnoi();
gridControl3.DataSource = kn.laybang("SELECT Iden,Codedetail,Tenkhachhang,Masp_KH "
+ " , Tenquicach, madh, Khachhang, CT.MaSP, Ma_khuon, Mact, Manhom_khuon, Ten_khuon "
+ " , Dacdiem_khuon, Soluong_khuon, Ngaylap, Ngaybatdau, Ngayhoanthanh "
+ ", Nguoilap, Manv, KH.Ghichu, Ngaycat_Hoanthanh, Ngayrap_Hoanthanh, Mabp "
+ ", BPsudung, Nguoiluukhuon, Vitrikhuon, Tinhtrang_khuon "
+ ", Ngaylukho "
+ ", dvt, Mau_banve, Tonkho "
+ ", Soluong, ngaygiao, ngoaiquang, nguoithaydoi, thoigianthaydoi FROM dbo.tblDHCT CT "
+ "left outer join tblDM_KHUON KH on CT.MaSP = KH.Masp where convert(nvarchar,thoigianthaydoi,103) between '" + dptu_ngay.Value.ToString("dd/MM/yyyy") + "' and '" + dpden_ngay.Value.ToString("dd/MM/yyyy") + "'");
}
private void DocTatCaCar()
{
ketnoi kn = new ketnoi();
gridControl1.DataSource = kn.laybang(@"select CarID,SBB,NgayPhatHanh,TaiLieu,MoTaVanDe,
PhanTichNguyenNhan,HanhDongKPPN,NgayHoanThanh,
NguoiThucHien,NguoiDuyet,GiamSat,NgayGiamSat,
YKienBoSung,KetQuaThoaMan,KetQuaKhongThoaMan,
GhiChuCarMoi,NguoiGhi,NgayGhi from tblCAR" );
kn.dongketnoi();
this.gridView1.OptionsSelection.MultiSelectMode
= DevExpress.XtraGrid.Views.Grid.GridMultiSelectMode.CheckBoxRowSelect;
this.gridView1.Appearance.Row.TextOptions.WordWrap = DevExpress.Utils.WordWrap.Wrap;
}
private void LOAD_DMChiTiet()
{
ketnoi ketnoi = new ketnoi();
gridControl2.DataSource = ketnoi.laybang(string.Concat(new string[]
{
"select * from THONGKETONGDONHANG where daystar between '",
dptu_ngay.Value.ToString("yyyy/MM/dd"),
"' and '",
dpden_ngay.Value.ToString("yyyy/MM/dd"),
"'"
}));
ketnoi.dongketnoi();
}
private void LISTDANHMUC_SANPHAM()//LIST DANH MUC SAN PHAM SAN XUAT
{
DataTable Table = new DataTable();
ketnoi Connect = new ketnoi();
LookupCheck_Masp.Properties.DataSource = Connect.laybang("select Masp,Tensp,Makh,TenKH from tblSANPHAM SP "
+ " left join tblKHACHHANG KH on SP.Makh=KH.MKH where Tensp is not null and Tensp <>'' order by Makh ASC");
LookupCheck_Masp.Properties.DisplayMember = "Masp";
LookupCheck_Masp.Properties.ValueMember = "Masp";
LookupCheck_Masp.Properties.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.Standard;
LookupCheck_Masp.Properties.BestFitMode = DevExpress.XtraEditors.Controls.BestFitMode.BestFitResizePopup;
LookupCheck_Masp.Properties.ImmediatePopup = true;
Connect.dongketnoi();
}
private void LoadGrid2()
{
ketnoi kn = new ketnoi();
gridControl7.DataSource = kn.laybang("SELECT (case when SLthucxuat is not null and SLthucxuat<>'' and TLthucxuat is not null and TLthucxuat<>'' "
+ " then convert(float, SLthucxuat / TLthucxuat) end) QuiDoi,Code,NgayLapPhieu,MaPhieu,nvkd,madh,sanpham,chitietsanpham, "
+ " cdthanhpham, soluongsx, ngoaiquang, mabv, donvi, daystar, dayend, "
+ " TongSLgiaogiacong, TongTLgiaogiacong, Soluongbaobi, Loaibaobi, "
+ " TLbaobi, khachhang, SLthucxuat, TLthucxuat, MaGiaCong, TenGiaCong, Id, "
+ " Num, Deadline FROM tblXuat_GiaCong "
+ " where NgayLapPhieu between '" + dptu_ngay.Value.ToString("MM/dd/yyyy") + "' "
+ " and '" + dpden_ngay.Value.ToString("MM/dd/yyyy") + "' "
+ " order by NgayLapPhieu, substring(MaPhieu,3,2) ASC ");
}
private void TraCuuTatCaXuat()
{
ketnoi kn = new ketnoi();
string sqlStr = string.Format(@"select D.Iden IDDonHang,D.madh,
Soluongsanpham,a.* from tblXuatKho a
left outer join
tblvattu_dauvao D on
D.CodeVatllieu=a.idvattu
order by Ngaylap Desc");
grChiTietXuat.DataSource = kn.laybang(sqlStr);
gvChiTietXuat.ExpandAllGroups();
kn.dongketnoi();
}
private void btncap_nhat_thuong_ds_xuat_Click(object sender, EventArgs e)
{
ketnoi kn = new ketnoi();
gridControl2.DataSource = kn.laybang("update tbl11 set DonGiaThuong=S.DonGiaThuong," +
" TienThuongXuatKho=(ISNULL(BTPT11,0)*S.DonGiaThuong), " +
" NguoiGhiDGThuong=N'" + txtMember.Text + "', " +
" NgayGhiDGThuong=GetDate() " +
" from ( select Masp,DonGiaThuong from tblSANPHAM)S " +
" where tbl11.mabv=S.Masp and cast(ngaynhan as date) between '" + dptu_ngay.Value.ToString("MM/dd/yyyy") + "' and '" + dpden_ngay.Value.ToString("MM/dd/yyyy") + "'");
kn.dongketnoi();
DocDSDonHangXuat_TheoNgay();
TongHop();
}
private void DanhSachNguyenCong()
{
ketnoi kn = new ketnoi();
gridControl2.DataSource = kn.laybang(@"SELECT SanPhamID,NguyenCongID,MaNC,NguyenCong,ChiTietNC,MaSP,
SanPham,TenNC,SoChiTiet,BoPhan,DonGia,DinhMuc FROM NguyenCong");
kn.dongketnoi();
gridView2.Columns["MaSP"].Visible = true;
gridView2.Columns["SanPham"].Visible = true;
gridView2.Columns["SanPham"].GroupIndex = 0;
gridView2.ExpandAllGroups();
this.gridView2.OptionsView.NewItemRowPosition
= DevExpress.XtraGrid.Views.Grid.NewItemRowPosition.None;
}
private void TraCuTatCaNhap()
{
ketnoi kn = new ketnoi();
string sqlQuery = string.Format(@"select D.Iden IDDonHang,D.madh,
Soluongsanpham,Khachhang,AllProgress,a.* from tblNHAP_VATTU a
left outer join
tblvattu_dauvao D on
D.CodeVatllieu=a.idvattu
order by Ngay_lap Desc");
grChiTietNhap.DataSource = kn.laybang(sqlQuery);
gvChiTietNhap.ExpandAllGroups();
kn.dongketnoi();
}
private void TaoMoiBienBan()
{
ketnoi kn = new ketnoi();
gridControl2.DataSource = kn.laybang(@"select C.ThietHaiCongTy,N.ThietHaiNhanVien,
C.ThietHaiCongTy+N.ThietHaiNhanVien ThietHai,R.*
from tblNCR R left outer join
(select NcrID,Sum(ChiPhiDonHang) ThietHaiCongTy
from NcrDatHang group by NcrID) C on R.NcrID=C.NcrID
left outer join
(select NcrID,sum(ChiPhiNhanVien) ThietHaiNhanVien
from NcrNhanVien group by NcrID) N on C.NcrID=N.NcrID WHERE NcrID='" + txtNcrID.Text + "'");
kn.dongketnoi();
}
private void BtnList_CongDoan_Click(object sender, EventArgs e)//Danh sach đơn hàng chuyển qua tính lịch sản xuất
{
ketnoi kn = new ketnoi();
DataTable dt = new DataTable();
dt = kn.laybang("select Nguoi,Ngay,TyleNangsuat,Ma_NguonLuc,id,Madh,PR.Masp,SP.Tensp,Ma_CongDoan,CongDoan,Thoigian_Dinhmuc,Soluong_DonHang, "
+ "TG_ChuanBi,TG_DuTru,CASE BatDau WHEN '1900-01-01' THEN Null ELSE BatDau END AS BatDau, "
+ "CASE KetThuc WHEN '1900-01-01' THEN Null ELSE KetThuc END AS KetThuc,WorkDay,TG_DUTINH, "
+ "TONGTG_DUTINH,TONGTG_DUTINHTYLE,idDonHang,PR.Ma_BoPhan,PB.To_bophan,ID_DMLD "
+ "from tblCalender_Product PR left outer join tblPHONGBAN PB on PR.Ma_BoPhan=PB.Ma_bophan "
+ "left outer join tblSANPHAM SP on SP.Masp = PR.Masp");
gridControl2.DataSource = dt;
kn.dongketnoi(); gridView2.ExpandAllGroups();
}
private void DocLichSanXuatTheoDonHang()
{
ketnoi kn = new ketnoi();
string sqlStr = string.Format(@"select * from LichSanXuatTong
('{0}','{1}')
where madh like N'{2}'
order by NgayLapLich ASC" ,
dptu_ngay.Value.ToString("MM/dd/yyyy"),
dpden_ngay.Value.ToString("MM/dd/yyyy"),
cbMa_DH.Text);
gridControl3.DataSource = kn.laybang(sqlStr);
kn.dongketnoi();
}
private void DocDSSanPhamVaoGridLooKup()
{
ketnoi kn = new ketnoi();
string sqlStr = string.Format(@"select c.Masp,s.Tensp from
(select Masp from tblSANPHAM_CT group by Masp)c
left outer join
(select Masp,Tensp from tblSANPHAM)s
on c.Masp=s.Masp");
grlSanPham.Properties.DataSource = kn.laybang(sqlStr);
grlSanPham.Properties.DisplayMember = "Masp";
grlSanPham.Properties.ValueMember = "Masp";
kn.dongketnoi();
}
private void LOADGridLookupEdit()
{
DataTable Table = new DataTable();
ketnoi Connect = new ketnoi();
CbMadhLook.Properties.DataSource = Connect.laybang("select Donvisp,madh,SPLR,SLSPLR,nvkd,ngaytrienkhai,IDSP,sanpham,Mact,Ten_ct,So_CT,soluongyc,tonkho, "
+ " soluongsx, ngoaiquang, Maubv,mabv, donvi, daystar, dayend, "
+ " KetThucTo_HanBam,BTPT_HanBam,TRONGLUONG_HanBam, khachhang, xeploai,MaPo,LoaiDH, Ghichu from tblchitietkehoach where madh is not null and KetThucTo_HanBam is not null "
+ " and convert(Date, ngaytrienkhai, 103) between '" + dptu_ngay.Value.ToString("yyyy/MM/dd") + "' and '" + dpden_ngay.Value.ToString("yyyy/MM/dd") + "'");
CbMadhLook.Properties.DisplayMember = "madh";
CbMadhLook.Properties.ValueMember = "madh";
CbMadhLook.Properties.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.Standard;
CbMadhLook.Properties.BestFitMode = DevExpress.XtraEditors.Controls.BestFitMode.BestFitResizePopup;
CbMadhLook.Properties.ImmediatePopup = true; Connect.dongketnoi();
}
