SunSystem và VBA (Phần I) (2 người xem)

[levanduyet]

Chào các bạn,
Một số các bạn làm kế toán, sales, quản lý sản xuất... và đang dùng Sunsystem thì tôi xin giới thiệu với các bạn một số đoạn code nhằm trích rút dữ liệu từ database của SunSystem (SQL server) và đưa nó vào Excel. Nó sẽ rất hữu ít cho việc quản lý. Đồng thời tôi nghĩ, để công việc trích rút của các bạn tốt hơn các bạn phải biết cấu trúc của các bảng dữ liệu trong cơ sở dữ liệu.
Nói tóm lại để làm tốt công việc trích rút dữ liệu từ cơ sở dữ liệu Sunsystem các bạn cần phải biết:
_ Một ít về SQL Server.
_ ADO.
_ VBA (dĩ nhiên rồi!).
_ Cấu trúc bảng dữ liệu.
Và thế là các bạn có thể bắt tay vào được rồi.
Tôi xin chú ý các bạn rằng, tôi chỉ xin chia sẻ, các đoạn code tôi giới thiệu ở đây chưa phải là tối ưu. Và tôi biết rằng các bạn còn có những người cao thủ hơn tôi về vấn đề này (Ví dụ như anh Workman,...).
_ Đoạn code kết nối với cơ sở dữ liệu mỗi khi mở workbook chẳng hạn, sau đó đóng lại. Khi nào cần các bạn lại kết nối. Chú ý rằng các bạn nên khai báo kiểu Public cho các biến:
Public gcnSQL As ADODB.Connection 'Global connection SQL

'Tôi xin không đưa các đoạn khai báo biến
sConnect = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=SUNDB;Data Source=SUN-SERVER"
'Có thể các bạn phải có UserID khác và cả Passworld để truy cập vào Database
'Bạn chú ý rằng ở đây DSN đã được tạo là SUNDB
Set gcnSQL = New ADODB.Connection
With gcnSQL
    .Mode = adModeReadWrite
    .ConnectionTimeout = 10
    .ConnectionString = sConnect
    .Open
End With
'Đóng Connection
MsgBox "You have connected to the Sunsystem!", vbOKOnly, "Information"
gcnSQL.Close

Tôi sẽ tiếp tục trong phần II.

Lê Văn Duyệt

 

[levanduyet]

SunSytem và VBA (Phần II)

Các bạn chú ý rằng trong SunSytem các trường dữ liệu chúng ta phải thường xuyên làm việc là:
_ PERIOD: Kỳ làm việc. Thông thường tùy thuộc vào từng công ty mà kỳ làm việc của năm tài chánh có thể khác nhau. Ví dụ có công ty kỳ I của năm tài chánh 2005-2006 là tháng 07/2005, nhưng cũng có công ty lại khác.
Đây là trường kiểu dữ liệu là chuổi. Ví dụ: 2006005 i.e kỳ 5.
_ MVMNT_DATE: là ngày của movement. Các bạn có thể hiểu là ngày của giao dịch (xin múa rìu qua mắt thợ một tý! Có gì các bạn có thể góp ý)
_ LOCATION: có thể xem như kho. Ví dụ tôi có kho thành phẩm, kho vật tư, kho hàng trả về, kho hàng hư, kho hàng ở phân xưởng I, kho hàng ở phân xưởng II,...
_ ITEM_CODE: mã sản phẩm, vật tư,...
_ UNITS: đơn vị tính.
_ QUANTITY: số lượng.
_ MVMNT_TYPE: loại movement. Cái này thì tùy thuộc vào mỗi công ty định nghĩa. Ví dụ: I-PRO: tức là xuất vào sản xuất, R-FGD: nhận thành phẩm...
_ MVMNT_REF: mã số tham khảo. Ví dụ như LDSH00025 là Loadsheet số 00025,...
Sau đây là đoạn SQL để lấy số lượng vật tư xuất cho bộ phận sản xuất:

sSQL = "SELECT ITEM_CODE, UNITS, SUM(QUANTITY) AS [TONG], MVMNT_DATE, SUM(AVE_VALUE) AS [GIATRI], MVMNT_REF, BATCH_NO " & _
                           "FROM dbo.SMMFMOVSDM " & _
                           "WHERE PERIOD='" & sPeriod & "' " & " AND (MVMNT_TYPE='R-FGD' OR MVMNT_TYPE='I-FGD') " & _
                           "AND MVMNT_DATE BETWEEN '" & dteDate1 & "' AND '" & dteDate2 & "' " & _
                           "AND LOCATION='" & sLocation & "' AND LEFT(LTRIM(MVMNT_REF),2)='LD' " & _
                           "GROUP BY ITEM_CODE, UNITS, MVMNT_DATE, MVMNT_REF, BATCH_NO " & _
                           "ORDER BY ITEM_CODE; "

Chú ý:
_ Bảng dữ liệu là: dbo.SMMFMOVSDM
_ sPeriod là biến để lưu kỳ làm việc. Ví dụ bạn dùng validation để chọn kỳ. Sau đó các bạn gán kỳ này cho biến này. Và sau đó đưa nó vào chuỗi SQL.
_ Tương tự các bạn có thể làm với MVMNT_TYPE. Tức là các bạn cũng dùng validation để nhập liệu cho trường này.
_ sLocation là biến để lưu Location mà bạn muốn truy xuất.
_ BATCH_NO: khi các bạn nhập liệu mỗi khi các bạn hoàn tất (đồng ý) nhập các dữ liệu của bạn và cơ sở dữ liệu thì hệ thống sẽ tạo ra số này. Nó sẽ cho bạn biết ai Nhập liệu, Ngày giờ,...
Các bạn có thể bỏ ra chuỗi sau từ chuỗi SQL trên

AND LEFT(LTRIM(MVMNT_REF),2)='LD'

Có thể giải thích câu SQL trên nhằm:

"SELECT ITEM_CODE, UNITS, SUM(QUANTITY) AS [TONG], MVMNT_DATE, SUM(AVE_VALUE) AS [GIATRI], MVMNT_REF, BATCH_NO " & _
                           "FROM dbo.SMMFMOVSDM "

Lấy trường ITEM_CODE, UNITS, Tổng (QUANTITY), MVMNT_DATE, Tổng (AVE_VALUE), MVMNT_REF, BATCH_NO Từ bảng dữ liệu dbo.SMMFMOVSDM
Với điều kiện:

"WHERE PERIOD='" & sPeriod & "' " & " AND (MVMNT_TYPE='R-FGD' OR MVMNT_TYPE='I-FGD') " & _
                           "AND MVMNT_DATE BETWEEN '" & dteDate1 & "' AND '" & dteDate2 & "' " & _
                           "AND LOCATION='" & sLocation & "' AND LEFT(LTRIM(MVMNT_REF),2)='LD' "

Kỳ phải bằng sPeriod, kiểu movement là R-FGD hay I-FGD, ngày của chứng từ là từ ngày dteDate1 đến dteDate2, chúng ta lấy từ sLocation và chứng từ tham khảo có 2 ký tự bắt đầu là LD. Bạn chú ý rằng ở đây có phân biệt chữ thường và chữ hoa.

Lê Văn Duyệt

 

[levanduyet]

SunSytem và VBA (Phần III)

Trong phần II, liên quan đến việc lấy dữ liệu:
_ Xuất dữ liệu vào sản xuất.
_ Nhập thành phẩm từ bộ phận sản xuất.
Trong phần này tôi xin giới thiệu với các bạn các số liệu liên quan đến bán hàng:

sSQL = "SELECT ITEM_CODE, UNIT_SALE, SUM(VALUE_1) AS [TONG], DEL_DATE, SUM(VALUE_3) AS [GIATRI], TRANS_REF " & _
                           "FROM dbo.SOMFDETSDM " & _
                           "WHERE ORD_PRD='" & sPeriod & "' " & _
                           "AND DEL_DATE BETWEEN '" & dteDate1 & "' AND '" & dteDate2 & "' " & _
                           "GROUP BY ITEM_CODE, UNIT_SALE, DEL_DATE, TRANS_REF " & _
                           "HAVING SUM(VALUE_1)>0 " & _
                           "ORDER BY ITEM_CODE; "

_ UNIT_SALE: đơn vị bán.
_ VALUE_1: số lượng bán.
_ DEL_DATE: ngày giao hàng
_ VALUE_3: giá trị bán.
_ TRANS_REF: số tham chiếu transaction.
_ dbo.SOMFDETSDM: bảng dữ liệu liên quan đến Sales.
_ ORD_DATE: kỳ bán hàng
Trên đây chỉ là các câu SQL để lấy dữ liệu về.
Các bạn phải dùng Recordset để lấy dữ liệu về

rs As New ADODB.Recordset

Sau đó tùy thuộc vào từng yêu cầu của trường dữ liệu mà các bạn đưa dữ liệu vào Excel.

'(1)
rs.Open sSQL, gcnSQL, adOpenStatic, adLockReadOnly, adCmdText
'(2)     
       lRecordCount = rs.RecordCount
            If lRecordCount = 0 Then
                MsgBox "There are no data as your requirements!", vbOKOnly, "Notice"
                GoTo ErrorExit
            End If
            If lRecordCount > 0 Then
'(3)                Application.Worksheets("RMISSUEDFROMSUN").Range("B5").Offset(0, 0) _
                        .CopyFromRecordset rs
                Range("B5").Select
            End If
            Set rs = Nothing

Sau khi định nghĩa câu sSQL bạn gán câu sSQL này nhằm mở Recordset (1).
(2) Kiểm tra xem có record nào được trả về hay không

 If lRecordCount = 0 Then

(3) Đưa dữ liệu ra worksheet. Ở đây tôi đưa ra worksheet RMISSUEDFROMSUN tại ô B5

Application.Worksheets("RMISSUEDFROMSUN").Range("B5").Offset(0, 0) _
                        .CopyFromRecordset rs

Ở đây chúng ta sử dụng phương thức CopyFromRecordset để đưa tất cả ra tại vị trí B5.

Hy vọng rằng các điều trên đây sẽ bổ ích cho một số kế toán viên, hay nhân viên kho, sản xuất...nhằm lấy số liệu mà không phải "mất tiền" (tức là phải mua User, mà chưa chắc theo như ý của mình).
Xin một số bạn có kinh nghiệm cùng xây dựng mục này...để mục này thật hữu ích.

Lê Văn Duyệt
PS:
_ Các bạn chú ý rằng chỉ nên dùng "SELECT" để không làm ảnh hưởng đến số liệu được nhập từ phần mềm.
_ Với số tiền tiết kiệm được từ vấn đề mua User, chắc các xếp sẽ thưởng cho các bạn mà thôi.

From TuanVniVnu:

Đầu xuân anh Duyệt có rất nhiều bài mừng tuổi anh em. Em cũng muốn du nhập thế giới OpenOffice quá mà chưa thu xếp được. Chúc anh năm mới có nhiều niềm vui, hạnH phúc, đặc biệt GFE sẽ cùng đón chào bé Duyệt nhí năm 2007! __________________

From LaChinhAn:
Bác Duyệt đầu năm phát pháo mạnh mẽ quá, rất nhiều bài viết hay. Chúc Bác cùng gia đình nhiều sức khỏe, hạnh phát và luôn luôn phát triển. Chúc tập thể GPE chúng ta luôn là 1 tập thể thống nhất đoàn kết và chia sẽ.

HAPPY NEW YEAR

Các bạn nào thích VB6 thì có thể vào đây tãi cái Portable VB6 về nè, có cái này chép vào USB bạn có thể lập trình ở bất cứ đâu.
http://sanchoituoitre.vn/download/article/1/
ID đăng nhập là : lachinhan pass: nhan123456

 

[luongkiengxuong]

Các bạn chú ý rằng trong SunSytem các trường dữ liệu chúng ta phải thường xuyên làm việc là:
_ PERIOD: Kỳ làm việc. Thông thường tùy thuộc vào từng công ty mà kỳ làm việc của năm tài chánh có thể khác nhau. Ví dụ có công ty kỳ I của năm tài chánh 2005-2006 là tháng 07/2005, nhưng cũng có công ty lại khác.
Đây là trường kiểu dữ liệu là chuổi. Ví dụ: 2006005 i.e kỳ 5.
_ MVMNT_DATE: là ngày của movement. Các bạn có thể hiểu là ngày của giao dịch (xin múa rìu qua mắt thợ một tý! Có gì các bạn có thể góp ý)
_ LOCATION: có thể xem như kho. Ví dụ tôi có kho thành phẩm, kho vật tư, kho hàng trả về, kho hàng hư, kho hàng ở phân xưởng I, kho hàng ở phân xưởng II,...
_ ITEM_CODE: mã sản phẩm, vật tư,...
_ UNITS: đơn vị tính.
_ QUANTITY: số lượng.
_ MVMNT_TYPE: loại movement. Cái này thì tùy thuộc vào mỗi công ty định nghĩa. Ví dụ: I-PRO: tức là xuất vào sản xuất, R-FGD: nhận thành phẩm...
_ MVMNT_REF: mã số tham khảo. Ví dụ như LDSH00025 là Loadsheet số 00025,...
Sau đây là đoạn SQL để lấy số lượng vật tư xuất cho bộ phận sản xuất:

sSQL = "SELECT ITEM_CODE, UNITS, SUM(QUANTITY) AS [TONG], MVMNT_DATE, SUM(AVE_VALUE) AS [GIATRI], MVMNT_REF, BATCH_NO " & _
                           "FROM dbo.SMMFMOVSDM " & _
                           "WHERE PERIOD='" & sPeriod & "' " & " AND (MVMNT_TYPE='R-FGD' OR MVMNT_TYPE='I-FGD') " & _
                           "AND MVMNT_DATE BETWEEN '" & dteDate1 & "' AND '" & dteDate2 & "' " & _
                           "AND LOCATION='" & sLocation & "' AND LEFT(LTRIM(MVMNT_REF),2)='LD' " & _
                           "GROUP BY ITEM_CODE, UNITS, MVMNT_DATE, MVMNT_REF, BATCH_NO " & _
                           "ORDER BY ITEM_CODE; "

Chú ý:
_ Bảng dữ liệu là: dbo.SMMFMOVSDM
_ sPeriod là biến để lưu kỳ làm việc. Ví dụ bạn dùng validation để chọn kỳ. Sau đó các bạn gán kỳ này cho biến này. Và sau đó đưa nó vào chuỗi SQL.
_ Tương tự các bạn có thể làm với MVMNT_TYPE. Tức là các bạn cũng dùng validation để nhập liệu cho trường này.
_ sLocation là biến để lưu Location mà bạn muốn truy xuất.
_ BATCH_NO: khi các bạn nhập liệu mỗi khi các bạn hoàn tất (đồng ý) nhập các dữ liệu của bạn và cơ sở dữ liệu thì hệ thống sẽ tạo ra số này. Nó sẽ cho bạn biết ai Nhập liệu, Ngày giờ,...
Các bạn có thể bỏ ra chuỗi sau từ chuỗi SQL trên

AND LEFT(LTRIM(MVMNT_REF),2)='LD'

Có thể giải thích câu SQL trên nhằm:

"SELECT ITEM_CODE, UNITS, SUM(QUANTITY) AS [TONG], MVMNT_DATE, SUM(AVE_VALUE) AS [GIATRI], MVMNT_REF, BATCH_NO " & _
                           "FROM dbo.SMMFMOVSDM "

Lấy trường ITEM_CODE, UNITS, Tổng (QUANTITY), MVMNT_DATE, Tổng (AVE_VALUE), MVMNT_REF, BATCH_NO Từ bảng dữ liệu dbo.SMMFMOVSDM
Với điều kiện:

"WHERE PERIOD='" & sPeriod & "' " & " AND (MVMNT_TYPE='R-FGD' OR MVMNT_TYPE='I-FGD') " & _
                           "AND MVMNT_DATE BETWEEN '" & dteDate1 & "' AND '" & dteDate2 & "' " & _
                           "AND LOCATION='" & sLocation & "' AND LEFT(LTRIM(MVMNT_REF),2)='LD' "

Kỳ phải bằng sPeriod, kiểu movement là R-FGD hay I-FGD, ngày của chứng từ là từ ngày dteDate1 đến dteDate2, chúng ta lấy từ sLocation và chứng từ tham khảo có 2 ký tự bắt đầu là LD. Bạn chú ý rằng ở đây có phân biệt chữ thường và chữ hoa.

Lê Văn Duyệt

Chào anh Lê Văn Duyệt , anh có tài liệu hướng dẫn cài sun system chế độ Server và client không ? nếu có anh share cho em với em đang cần setup sun dươi dạng server và client và cách kết nối database giữa Sun với SQL server . Anh Duyệt cho em hỏi ở HCM chỗ nào dạy Sun không. Em cảm ơn anh Duyệt rất nhiều.