Maøn hình Saûn phaåm - THIEÁT KEÁ ÖÙNG DUÏNG (Caùc- 123docz.net

IV. THIEÁT KEÁ ÖÙNG DUÏNG (Caùc maøn hình)

7. Maøn hình Saûn phaåm

Hình 9: Maøn hình tra cöùu, nhaäp saûn phaåm.

Source code laáy döõ lieäu leân löôùi:

Dim strSQL As String

Dim Cnn As New ADODB.Connection strSQL = "select * FROM sanpham"

Set Rst = New ADODB.Recordset Rst.CursorLocation = adUseClient 'Conn.Open SQLConn

Cnn.Open SQLConn

Rst.Open strSQL, Conn, adOpenKeyset, adLockOptimistic SSOSP.Columns(0).DataField = "sanpham"

SSOSP.Columns(1).DataField = "tensanpham"

SSOSP.Columns(2).DataField = "donvitinh"

SSOSP.Columns(3).DataField = "dongia"

Set SSOSP.DataSource = Nothing

Set SSOSP.DataSource = Rst

: Khi nhaán nuùt môùi thì caùc textbox nhaän focus nhaäp thoâng tin saûn phaåm môùi.

: Löu döõ lieäu môùi xuoáng Cô sôû döõ lieäu.

Source code Löu:

Private Sub cmdLuu_Click() Dim Str As String

Dim Str1 As String

If Trim(txtSP) = "" Or Trim(txtTSP) = "" Then

MsgBox "Phai nhap MaSP & TenSP truoc khi luu ", vbOKOnly + vbExclamation,

"WARNING"

Exit Sub End If

Str = "insert into SANPHAM values('" & Trim(txtSP) & "','" & Trim(txtTSP) & "','"

& Trim(txtDVT) & "','" & Trim(txtDG) & "')"

Conn.Execute Str

Str1 = "update SANPHAM Set sanpham='" & Trim(txtSP) & "',tensanpham='" &

Trim(txtTSP) & "',donvitinh='" & Trim(txtDVT) & "',dongia='" & Trim(txtDG) & "' "

Conn.Execute Str1 SSOSP.Update

MsgBox "Da Luu San Pham '" & Trim(txtSP) & "'' Vao CSDL", vbOKOnly + vbExclamation, "WARNING"

CmdLuu.Enabled = False End Sub

: Thoaùt maøn hình saûn phaåm.

8. Maøn hình Ñònh möùc saûn xuaát :

Source code laáy döõ lieäu leân löôùi:

Dim strSQL As String

Dim Cnn As New ADODB.Connection strSQL = "select * from DinhMucSanXuat"

Set Rst = New ADODB.Recordset Rst.CursorLocation = adUseClient Cnn.Open SQLConn

Rst.Open strSQL, Conn, adOpenKeyset, adLockOptimistic SSODMSX.Columns(0).DataField = "nguyenLieu"

SSODMSX.Columns(1).DataField = "sanpham"

SSODMSX.Columns(2).DataField = "soluongsanxuat"

Set SSODMSX.DataSource = Nothing Set SSODMSX.DataSource = Rst

: Khi nhaán nuùt môùi thì caùc textbox nhaän focus nhaäp thoâng tin môùi.

: Löu döõ lieäu môùi xuoáng Cô sôû döõ lieäu.

: Thoaùt maøn hình ñònh möùc saûn xuaát.

Tính toaùn:

Menu Coâng nôï: Baùo caùo coâng nôï.

Menu Toàn : Baùo caùo toàn nguyeân lieäu.

Menu Thanh Toaùn: Hieån thò tieàn thanh toaùn cuûa khaùch haøng vôùi coâng ty.

Maøn hình coâng nôï:

: Khi nhaán nuùt môùi thì caùc textbox nhaän focus nhaäp thoâng tin phieáu Coâng nôï môùi.

: Löu döõ lieäu môùi xuoáng Cô sôû döõ lieäu.

: Thoaùt maøn hình Toàn Nguyeân Lieäu.

Code xöû lyù tính coâng nôï:

Q1:

SELECT max(ngaycongNo) AS NgDK FROM CongNo;

Q2:

SELECT MaKhachHang,TienCongNo As DAU, 0 As SONO , 0 As TTOAN from Q1, CongNo Where NgayCongNo = NgDK

UNION Select MaKhachHang, 0 as DAU, Sum(SoLuongDathang * donGia) As SONO, 0 As TTOAN from Q1,ChiTietDondatHang where NgayKiKet = NgDK and NgayKiKet <

DateAdd("m",1,NgDK)

UNION Select maKhachHang, 0 As DAU,0 As SONO, Sum(ChiTietGiaoNhan.soluong * ChiTietGiaoNhan.dongia) As TTOAN from Q1, ChiTietGiaoNhan where

NgayThanhToan = NgDK and NgayThanhToan < DateAdd("m",1,NgDK) group by MaKhachHang;

Q3:

Insert Into CongNo(NgayCongNo,MaKhachHangTiencongNo) Select

DateAdd("m",1,ngDK), MaKhachHang,Sum(Dau) + Sum(soNo) – Sum(TToan) from Q1, Q2 Group By DateAdd(‘m’,1,ngDK), MaKhachHang Having Sum(Dau) + Sum(soNo) – Sum(TToan) <> 0;

Q4:

Delete * from CongNo where NgayCongNo In(Select max(NgayCongNo) from CongNo) and NgayCongNo Not in (Select min(NgayCongNo) from CongNo);

Maøn hình Toàn Nguyeân Lieäu:

: Khi nhaán nuùt môùi thì caùc textbox nhaän focus nhaäp thoâng tin saûn phaåm môùi.

: Löu döõ lieäu môùi xuoáng Cô sôû döõ lieäu.

: Thoaùt maøn hình Toàn Nguyeân Lieäu.

Code xöû lyù tính toàn:

SELECT max(ngayton) AS NgDK FR1OM Ton;

SELECT MaKhachHang,NguyenLieu,Soluongton as Dau,0 as Nhap,0 as Xuat from Q1,Ton where ngayTon=NgDK

union select NguyenLieu,SoLuongTon, 0 as Dau,Sum(SoHoaDonNhapXuat) as Nhap,0 as xuat from Q1,NhapXuatNguyenLieu inner join ChitietNguyenLieu on

NhapXuatNguyenLieu.SohoaDonNhap=ChitietNguyenLieu.SohoaDonNhap where NgayNhap = NgDK and NgayNhap<=DateAdd("m",1,ngDK) group by

MaKhachHang,NguyenLieu

UNION select soPhieu,SoDondatHang, 0 as Dau,0 as Nhap, sum(soluong) as Xuat from Q1,GiaoNhan Inner Join ChiTietGiaoNhan On GiaoNhan.SoPhieu=

ChiTietGiaoNhan.SoPhieu where NgayGiao=ngDK and NgayGiao <=

dateAdd("m",1,ngDK) group by soPhieu, SoDonDatHang;

Q3:

SELECT SoPhieu,SoDonDatHang,Sum(dau) + sum(Nhap) - Sum(Xuat) as SoTon from Q2 group by SoPhieu,SoDonDatHang Into TAM;

Q4:

Insert Into TAM select soPhieu,Sodondathang, SoLuong* SoLuongSanXuat as SoTon from TAM Inner Join GiaoNhan on Tam.SoDonDatHang=GiaoNhan.SoDonDatHang;

Q5: (xoaù nhöõng thaønh phaàn coù soá toàn < 0) Delete * From TAM where SoTon < 0;

Q6: (Tính, löu laïi):

Insert into Ton(MakhachHang, NguyenLieu,SoLuongTon) select MakhachHang, NguyenLieu,Sum(SoTon), DateAdd(‘m’,1,NgDK) from Q1, TAM group by maKhachHang, NguyenLieu, Having soTon > 0, DateAdd("m",1,ngDK);

Q7: (Xoaù Table TAM):

Drop Table TAM;

Maøn Hình Thanh toaùn:

Code xöû lyù cho nuùt Löu:

Private Sub cmdLuu_Click() Dim Str As String

Dim Str1 As String

If Trim(cboKH.Text) = "" Or Trim(txtsott.Text) = "" Then

MsgBox " Nhap So TT va MaKH truoc khi luu ", vbOKOnly + vbExclamation,

"WARNING"

Exit Sub End If

Str = "Insert Into ThanhToan values('" & Trim(txtsott) & "','" & Trim(txtNgay) &

"','" & Trim(cboKH) & "','" & Trim(txtTientt) & "' ) "

Conn.Execute Str

Str1 = "update ThanhToan set sothanhToan='" & Trim(txtsott) & "',

NgayThanhToan='" & Trim(txtNgay) & "', MaKhachHang='" & Trim(cboKH) & "', TienThangToan='" & Trim(txtTientt) & "' WHERE soThanhToan='" & Trim(txtsott) &

"' "

Conn.Execute Str1 SSOTT.Update

MsgBox "Da luu So Thanh Toan '" & Trim(txtsott) & "'' vao CSDL", vbOKOnly + vbExclamation, "WARNING"

cmdLuu.Enabled = False cmdmoi.Enabled = True End Sub

Baùo Bieåu:

Menu Ñôn ñaët haøng: Baùo caùo danh saùch ñaët haøng cuûa khaùch haøng.

Menu Nhaäp xuaát NL: Baùo caùo nhaäp nguyeân lieäu.

Menu Giao nhaän: Baùo caùo giao nhaän saûn phaåm.

Menu coäng nôï: Baùo caùo coâng nôï.

Menu Toàn: Baùo caùo toàn nguyeân lieäu.

Menu thanh toaùn: Baùo caùo thanh toaùn cuûa khaùch haøng.

Maøn Hình baùo bieåu Ñôn ñaët Haøng :

Maøn Hình baùo bieåu Nhaäp xuaát Nguyeân lieäu :

Maøn Hình baùo bieåu Giao nhaän :

Maøn Hình baùo bieåu Coâng nôï Khaùch haøng:

Maøn Hình baùo bieåu Toàn:

Maøn hình baùo bieåu Thanh toaùn :