'รายงานทางบัญชี สต๊อคการ์ด (IC_B4_02xIC_21)
'OrgCode = OrgName/OrgNameUS/etc
'PARTHIST.PROCESS_SEQ = 60-รับ, 100-จ่าย
'SQLite Date String Format
'yyyy-MM-dd HH:MM:SS.SSS
Public Shared Function IC_B4_02xIC_21(ByVal Warehouse As String,
ByVal From_Part_NO As String,
ByVal To_Part_NO As String,
ByVal From_Date As String,
ByVal To_Date As String,
ByVal OrgCode As String) As DataTable
Dim strSQL As XCData = Nothing
'Dim xFrom_Date As String = WL_Utility.DateUtils.GetFirstDayOfMonth(ZMonth, Zyear).ToString("yyyy/MM/dd") '2014-01-01
'Dim xTo_Date As String = WL_Utility.DateUtils.GetLastDayOfMonth(ZMonth, Zyear).ToString("yyyy/MM/dd 23:59:59") '2014-01-01 23:59
'SQL Server DateAdd(day, -30, GETDATE())
'SQLite SELECT DATE('NOW','-30 day')
'Firebird CAST('TODAY' AS TIMESTAMP) - 30
Dim curDateDec30 As String = If(RDBMSType = "Firebird", "CAST('TODAY' AS TIMESTAMP) - @P_Cycle_NO", If(RDBMSType = "SQLite", "DATE('NOW','-@P_Cycle_NO day')", "Not Implement yet."))
Select Case (WL_Common.PubConstant.RDBMSType)
Case "MsAccess"
'Not Implement yet.
Case Else
strSQL =
<![CDATA[
SELECT 'HED' AS REP_GROUP, WHS_BAL.WAREHOUSE, WHS_BAL.PART_NO, 'AAAAAAAA' AS DOC_NO, ' ' AS SEQ, CAST('3/3/2014' AS TIMESTAMP) AS DOC_DATE, CAST('00' AS INTEGER) AS PROCESS_SEQ,
'AA' AS TRAN_CODE, 'AAA' AS TRAN_TYPE, MIN(WHS_STAT.BEGIN_BAL) AS BEGIN_BAL, MIN(WHS_STAT.BEG_AMT) AS BEG_AMT, SUM(HIS_STAT.REC_QTY) AS REC_QTY,
SUM(HIS_STAT.ISS_QTY) AS ISS_QTY, SUM(HIS_STAT.ADJ_INC) AS ADJ_INC, SUM(HIS_STAT.ADJ_DEC) AS ADJ_DEC, 0.00 AS TRAN_QTY, 0.00 AS TRAN_QTYSORT,
SUM(HIS_STAT.TRAN_AMT) AS TRAN_AMT, '0' AS POST_FLAG, MIN(WHS_BAL.ACCODE) AS ACCODE, MIN(AC_TAB.ACCOUNT_NO) AS ACCOUNT_NO, MIN(UM_TAB.UM) AS UM,
MIN(UM_TAB.UM_DESC) AS UM_DESC, MIN(WHS_BAL.PART_DESC) AS PART_DESC, 0.00 AS UNIT_COST, (
SELECT MIN(WHS_TAB.WHS_DESC)
FROM WHS_TAB
WHERE WHS_TAB.WAREHOUSE = WHS_BAL.WAREHOUSE
AND WHS_TAB.STK_LOCATION = (
SELECT MIN(WHB.STK_LOCATION)
FROM WHS_TAB WHB
WHERE WHB.WAREHOUSE = WHS_BAL.WAREHOUSE
)
) AS WHS_DESC, (
SELECT MIN(TRAN_DESC)
FROM PARTHIST
WHERE 1 <> 1
) AS TRAN_DESC, MIN(REP1.NOTE) AS NOTE, MIN(REP2.NOTE) AS NOTE2, CAST('0' AS NUMERIC(15, 2)) AS HDL_TRAN_QTY, CAST('0' AS NUMERIC(15, 2)) AS HDL_UNIT_COST
FROM WHS_BAL
LEFT JOIN WHS_STAT ON WHS_BAL.WAREHOUSE = WHS_STAT.WAREHOUSE AND WHS_BAL.PART_NO = WHS_STAT.PART_NO AND WHS_STAT.ZYEAR = '57' AND WHS_STAT.PERIOD = '3'
LEFT JOIN HIS_STAT ON WHS_BAL.WAREHOUSE = HIS_STAT.WAREHOUSE AND WHS_BAL.PART_NO = HIS_STAT.PART_NO AND HIS_STAT.DOC_DATE < '3/1/2014'
LEFT JOIN AC_TAB ON WHS_BAL.WAREHOUSE = AC_TAB.WAREHOUSE AND WHS_BAL.ACCODE = AC_TAB.ACCODE
LEFT JOIN PART_STD ON WHS_BAL.PART_NO = PART_STD.PART_NO
LEFT JOIN UM_TAB ON PART_STD.UM = UM_TAB.UM
LEFT JOIN (SELECT @COMPANY AS NOTE) AS REP1 ON 1=1
LEFT JOIN REP REP2 ON REP2.ID = 'LAST_POST'
WHERE WHS_BAL.WAREHOUSE = @Warehouse AND WHS_BAL.PART_NO BETWEEN @From_Part_NO AND @To_Part_NO
GROUP BY WHS_BAL.WAREHOUSE, WHS_BAL.PART_NO
UNION ALL
SELECT 'DET' AS REP_GROUP, PARTHIST.WAREHOUSE, PARTHIST.PART_NO, PARTHIST.DOC_NO, PARTHIST.SEQ, PARTHIST.DOC_DATE, PARTHIST.PROCESS_SEQ, PARTHIST.TRAN_CODE,
PARTHIST.TRAN_TYPE, 0.00 AS BEGIN_BAL, 0.00 AS BEG_AMT, 0.00 AS REC_QTY, 0.00 AS ISS_QTY, 0.00 AS ADJ_INC, 0.00 AS ADJ_DEC, PARTHIST.TRAN_QTY, CASE
WHEN ABS(PARTHIST.TRAN_QTY) > 0
THEN (PARTHIST.TRAN_QTY / (ABS(PARTHIST.TRAN_QTY)))
ELSE 0
END AS TRAN_QTYSORT, PARTHIST.TRAN_AMT, PARTHIST.POST_FLAG, WHS_BAL.ACCODE, AC_TAB.ACCOUNT_NO, UM_TAB.UM, UM_TAB.UM_DESC, WHS_BAL.PART_DESC, PARTHIST.UNIT_COST, (
SELECT MIN(WHS_TAB.WHS_DESC)
FROM WHS_TAB
WHERE WHS_TAB.WAREHOUSE = WHS_BAL.WAREHOUSE
AND WHS_TAB.STK_LOCATION = (
SELECT MIN(WHB.STK_LOCATION)
FROM WHS_TAB WHB
WHERE WHB.WAREHOUSE = WHS_BAL.WAREHOUSE
)
) AS WHS_DESC, PARTHIST.TRAN_DESC, REP1.NOTE AS NOTE, REP2.NOTE AS NOTE2, CAST('0' AS NUMERIC(15, 2)) AS HDL_TRAN_QTY, CAST('0' AS NUMERIC(15, 2)) AS HDL_UNIT_COST
FROM WHS_BAL
LEFT JOIN PARTHIST ON WHS_BAL.WAREHOUSE = PARTHIST.WAREHOUSE AND WHS_BAL.PART_NO = PARTHIST.PART_NO AND PARTHIST.DOC_DATE BETWEEN '3/1/2014' AND '3/3/2014 23:59'
LEFT JOIN AC_TAB ON WHS_BAL.WAREHOUSE = AC_TAB.WAREHOUSE AND WHS_BAL.ACCODE = AC_TAB.ACCODE
LEFT JOIN PART_STD ON WHS_BAL.PART_NO = PART_STD.PART_NO
LEFT JOIN UM_TAB ON PART_STD.UM = UM_TAB.UM
LEFT JOIN (SELECT @COMPANY AS NOTE) AS REP1 ON 1=1
LEFT JOIN REP REP2 ON REP2.ID = 'LAST_POST'
WHERE WHS_BAL.WAREHOUSE = 'หอยหมี' AND WHS_BAL.PART_NO BETWEEN 'หอย' AND 'เหม็น'
UNION ALL
SELECT 'DET' AS REP_GROUP, PARTHIST.WAREHOUSE, PARTHIST.PART_NO, PARTHIST.DOC_NO, PARTHIST.SEQ, PARTHIST.DOC_DATE, PARTHIST.PROCESS_SEQ, PARTHIST.TRAN_CODE, PARTHIST.TRAN_TYPE, 0.00 AS BEGIN_BAL, 0.00 AS BEG_AMT, 0.00 AS REC_QTY, 0.00 AS ISS_QTY, 0.00 AS ADJ_INC, 0.00 AS ADJ_DEC, PARTHIST.TRAN_QTY, CASE
WHEN ABS(PARTHIST.TRAN_QTY) > 0
THEN (PARTHIST.TRAN_QTY / (ABS(PARTHIST.TRAN_QTY)))
ELSE 0
END AS TRAN_QTYSORT, PARTHIST.TRAN_AMT, PARTHIST.POST_FLAG, WHS_BAL.ACCODE, AC_TAB.ACCOUNT_NO, UM_TAB.UM, UM_TAB.UM_DESC, WHS_BAL.PART_DESC, PARTHIST.UNIT_COST, (
SELECT MIN(WHS_TAB.WHS_DESC)
FROM WHS_TAB
WHERE WHS_TAB.WAREHOUSE = WHS_BAL.WAREHOUSE
AND WHS_TAB.STK_LOCATION = (
SELECT MIN(WHB.STK_LOCATION)
FROM WHS_TAB WHB
WHERE WHB.WAREHOUSE = WHS_BAL.WAREHOUSE
)
) AS WHS_DESC, PARTHIST.TRAN_DESC, REP1.NOTE AS NOTE, REP2.NOTE AS NOTE2, CASE
WHEN (- HDL.TRAN_QTY) > 0
THEN CAST((- HDL.TRAN_QTY) AS NUMERIC(15, 2))
ELSE 0
END AS HDL_TRAN_QTY, CASE
WHEN HDL.UNIT_COST > 0
THEN CAST(HDL.UNIT_COST AS NUMERIC(15, 2))
ELSE 0
END AS HDL_UNIT_COST
FROM WHS_BAL
LEFT JOIN PARTHIST ON WHS_BAL.WAREHOUSE = PARTHIST.WAREHOUSE AND WHS_BAL.PART_NO = PARTHIST.PART_NO AND PARTHIST.DOC_DATE BETWEEN '3/1/2014' AND '3/3/2014 23:59'
LEFT JOIN HIS_DTL HDL ON HDL.WAREHOUSE = PARTHIST.WAREHOUSE AND HDL.DOC_DATE = PARTHIST.DOC_DATE AND HDL.DOC_NO = PARTHIST.DOC_NO AND HDL.SEQ = PARTHIST.SEQ
LEFT JOIN AC_TAB ON WHS_BAL.WAREHOUSE = AC_TAB.WAREHOUSE AND WHS_BAL.ACCODE = AC_TAB.ACCODE
LEFT JOIN PART_STD ON WHS_BAL.PART_NO = PART_STD.PART_NO
LEFT JOIN UM_TAB ON PART_STD.UM = UM_TAB.UM
LEFT JOIN (SELECT @COMPANY AS NOTE) AS REP1 ON 1=1
LEFT JOIN REP REP2 ON REP2.ID = 'LAST_POST'
WHERE WHS_BAL.WAREHOUSE = 'หอยหมี' AND HDL.UNIT_COST > 0 AND (- HDL.TRAN_QTY) > 0 AND WHS_BAL.PART_NO BETWEEN 'หอย' AND 'เหม็น'
ORDER BY 2, 3, 6, 7, 4, 5
]]>
End Select
'ตั้งใจลืมเช็ค WHS_TAB.WAREHOUSE.OrgCode = 'OrgCode' ด้วย
Return WL_DatabaseVB.DbHelper.Query(strSQL.Value, New List(Of DbParameter) From
{
BuildParameter("@From_Date", From_Date),
BuildParameter("@To_Date", To_Date),
BuildParameter("@Tran_Code", Warehouse),
BuildParameter("@Warehouse", Warehouse)
})
End Function
Load balance : Server 00
Quote:
