warelistmaster.asp

印刷物の発注、在庫管理、出庫処理を一元管理

<%
'/
'/塣梡娗棟婡擻
'/嵼屔僥乕僽儖傾僋僙僗僋儔僗
'/
Class WareListMaster
	Dim l_SQL

	'嵼屔忣曬庢摼
	'敪拲忣曬偑側偄傕偺偼昞帵懳徾奜偲偡傞
	Public Function SearchStockBase(Param1,Param2,Param3)
		Dim l_StrYMD,l_EndYMD

		l_SQL = "SELECT S_T.LIST_CD,S_T.LIST_EDA,L_T.LIST_NAME," & _
				"       TO_CHAR(S_T.STOCK_NUM,'999,999,999,999') AS STOCK_NUM," & _
				"       TO_CHAR(S_T.ORDER_POINT,'999,999,999,999') AS ORDER_POINT," & _
				"       L_T.LIST_UNIT," &_
				"       (SELECT NAME " &_
				"          FROM CODE_M " &_
				"         WHERE SYSTEM_ID = L_T.SYSTEM_ID " &_
				"           AND CLASSIFIED_CODE = '" & CD_LIST_UNIT & "' " &_
				"           AND CODE = L_T.LIST_UNIT) AS LIST_UNIT_NAME," &_
				"       S_M.SECTION_NAME,L_T.CLASS1," & _
				"       (SELECT NAME " &_
				"          FROM CODE_M " &_
				"         WHERE SYSTEM_ID = L_T.SYSTEM_ID " &_
				"           AND CLASSIFIED_CODE = '" & CD_CLASS1 & "' " &_
				"           AND CODE = L_T.CLASS1 " &_
				"           AND DEL_FLG = '" & FLG_OFF & "') AS CODE_NAME," &_
				"       S_T.STOCK_STATUS," &_
				"       (SELECT NAME " &_
				"          FROM CODE_M " &_
				"         WHERE SYSTEM_ID = L_T.SYSTEM_ID " &_
				"           AND CLASSIFIED_CODE = '" & CD_STOCK_STATUS & "' " &_
				"           AND CODE = S_T.STOCK_STATUS) AS STOCK_STATUS_NAME," &_
				"       L_T.SECRET_FLG " & _
				"  FROM STOCK_T S_T,LISTBASE_T L_T,SECTION_M S_M" & _
				" WHERE S_T.SYSTEM_ID = '" & Param2.l_SystemId & "'" & _
				"   AND S_T.SYSTEM_ID = L_T.SYSTEM_ID" & _
				"   AND S_T.SYSTEM_ID = S_M.SYSTEM_ID" & _
				"   AND L_T.COMPANY_ID = S_M.COMPANY_ID" & _
				"   AND L_T.SECTION_ID = S_M.SECTION_ID" & _
				"   AND L_T.LIST_STATUS = '" & LSTS_APPROVE & "' " &_
				"   AND S_T.LIST_CD = L_T.LIST_CD" & _
				"   AND S_T.LIST_EDA = L_T.LIST_EDA" & _
				"   AND S_T.DEL_FLG = '0'" & _
				"   AND S_T.DEL_FLG = l_T.DEL_FLG"

		If IsObject(Param3) Then
			If Param3.Item("list_cd") <> "" Then
				l_SQL = l_SQL & " AND S_T.LIST_CD = '" & Param3.Item("list_cd") & "'"
			End If
			If Param3.Item("list_eda") <> "" Then
				l_SQL = l_SQL & " AND S_T.LIST_EDA = '" & Param3.Item("list_eda") & "'"
			End If
			'挔昜柤 2006/1/11 慜曽堦抳偵廋惓
			If Param3.Item("list_name") <> "" Then
				l_SQL = l_SQL & " AND L_T.LIST_NAME LIKE '" & l_Fmm.l_CutilObject.chg_HantoZen(Param3.Item("list_name")) & "%'"
			End If
			If Param3.Item("template") <> "" Then
				l_SQL = l_SQL & " AND L_T.TEMPLATE_ID = '" & Param3.Item("template") & "'"
			End If
			If Param3.Item("category") <> "" Then
				l_SQL = l_SQL & " AND L_T.CLASS1 = '" & Param3.Item("category") & "'"
			End If
			If Param3.Item("section") <> "" Then
				l_SQL = l_SQL & " AND L_T.SECTION_ID = '" & Param3.Item("section") & "'"
			End If
			If Param3.Item("stock_status1") <> "" Then
				l_SQL = l_SQL & " AND S_T.STOCK_STATUS IN ('" & Param3.Item("stock_status1") & "'"
				If Param3.Item("stock_status2") <> "" Then
					l_SQL = l_SQL & ",'" & Param3.Item("stock_status2") & "'"
				End If
				If Param3.Item("stock_status3") <> "" Then
					l_SQL = l_SQL & ",'" & Param3.Item("stock_status3") & "'"
				End If
				l_SQL = l_SQL + ")"
			Else
				If Param3.Item("stock_status2") <> "" Then
					l_SQL = l_SQL & " AND S_T.STOCK_STATUS IN ('" & Param3.Item("stock_status2") & "'"
					If Param3.Item("stock_status3") <> "" Then
						l_SQL = l_SQL & ",'" & Param3.Item("stock_status3") & "'"
					End If
					l_SQL = l_SQL + ")"
				Else
					If Param3.Item("stock_status3") <> "" Then
						l_SQL = l_SQL & " AND S_T.STOCK_STATUS IN ('" & Param3.Item("stock_status3") & "')"
					End If
				End If
			End If

			l_SQL = l_SQL & " ORDER BY " & GetOrderByStr(Param3.Item("sortkey"),Param3.Item("sortdesc"))
		Else
			l_SQL = l_SQL & " ORDER BY S_T.STOCK_STATUS DESC,S_T.ORDER_POINT DESC,S_T.STOCK_NUM,S_T.LIST_CD,S_T.LIST_EDA"
		End If
'Response.Write l_SQL
		debug_t(l_SQL)
		Set SearchStockBase = Param1.ProcRecordset(l_SQL,20,0)
	End Function

	Public Function GetOrderByStr(l_sortkey, l_sortdesc)
		Dim l_retStr
		Dim l_seq

		'僜乕僩弴愝掕
		If l_sortdesc = FLG_ON Then
			l_seq = " DESC"
		End If

		'僜乕僩暥嶌惉
		If l_sortkey = SORT_KEY1 Then		'僨僼僅儖僩(嵼屔忬嫷(崀弴)丒敪拲揰(崀弴)丒嵼屔悢(徃弴)丒挔昜斣崋(徃弴)
			l_retStr = "S_T.STOCK_STATUS DESC,S_T.ORDER_POINT DESC,S_T.STOCK_NUM,S_T.LIST_CD,S_T.LIST_EDA"
		ElseIf l_sortkey = SORT_KEY2 Then			'挔昜斣崋
			l_retStr = "S_T.LIST_CD" & l_seq & ",S_T.LIST_EDA" & l_seq
		ElseIf l_sortkey = SORT_KEY3 Then			'挔昜柤
			l_retStr = "L_T.LIST_NAME" & l_seq
		ElseIf  l_sortkey = SORT_KEY4 Then			'嵼屔悢
			l_retStr = "S_T.STOCK_NUM" & l_seq
		ElseIf  l_sortkey = SORT_KEY5 Then			'敪拲揰
			l_retStr = "S_T.ORDER_POINT" & l_seq
		End If

		GetOrderByStr = l_retStr
	End Function

	'/*******************************************************
	'/* 嵼屔忣曬儗僐乕僪懚嵼僠僃僢僋
	'/* param丗l_dbaccess(DBAccess) DB傾僋僙僗
	'/*        l_sysid(String) 		僔僗僥儉ID
	'/*		   l_listCd				挔昜斣崋
	'/*		   l_listEda			挔昜斣崋巬斣
	'/* return boolean True丗巜掕儗僐乕僪偑懚嵼偡傞丄False丗懚嵼偟側偄
	'/*******************************************************
	Public Function CheckStockRec(l_dbaccess, l_sysId, l_listCd, l_listEda)
		Dim l_recSet
		Dim l_sql

		CheckStockRec = False
		If l_listCd = "" Then
			Exit Function
		End If

		'SQL暥惗惉
		l_sql = "SELECT COUNT(*) FROM STOCK_T" & _
				" WHERE SYSTEM_ID  = '" & l_sysId & "'"  & _
				" AND LIST_CD = '" & l_listCd & "'" & _
				" AND LIST_EDA = '" & l_listEda & "'" & _
				" AND DEL_FLG <> '1'"

		Set l_recSet = l_dbaccess.ProcRecordset(l_sql,10,0)

		'懚嵼偡傟偽True丄側偗傟偽False
		If l_recSet.Fields.Item("COUNT(*)") <> 0 Then
			CheckStockRec = True
		End If
	End Function

	'/*******************************************************
	'/* 嵼屔忣曬傪侾儗僐乕僪拪弌偡傞
	'/* param丗l_dbaccess(DBAccess) DB傾僋僙僗
	'/*        l_sysid(String) 		僔僗僥儉ID
	'/*		   l_listCd				挔昜斣崋
	'/*		   l_listEda			挔昜斣崋巬斣
	'/* return ADODB.Recordset 専嶕寢壥
	'/*******************************************************
	Public Function SearchStockRec(l_dbaccess, l_sysid, l_listCd, l_listEda)
		Dim l_strsql	'String
		Dim l_recSet

		'SQL暥嶌惉
		'SELECT
		l_strsql = "SELECT ST.LIST_CD,ST.LIST_EDA,ST.STOCK_NUM,ST.STOCK_STATUS,ST.ORDER_POINT,ST.URGENT_ORDER_POINT," & _
				   "ST.DELIVERY_PLACE1,ST.NUM1,ST.RESERVE_STOCK_NUM1,ST.DELIVERY_PLACE2,ST.NUM2," & _
				   "ST.RESERVE_STOCK_NUM2,ST.DELIVERY_PLACE3,ST.NUM3,ST.RESERVE_STOCK_NUM3," & _
				   "LB.LIST_MANAGE_NO AS LIST_MANAGE_NO, LB.LIST_NAME,LB.LIST_UNIT AS LIST_UNIT, LB.SETLIST_FLG"
					
		'FROM STOCK_T
		l_strsql = l_strsql & " FROM STOCK_T ST, LISTBASE_T LB"

		'WHERE 寢崌忦審
		l_strsql  = l_strsql & " WHERE ST.SYSTEM_ID = LB.SYSTEM_ID(+)" & _
					" AND ST.LIST_CD = LB.LIST_CD(+)" & _
					" AND ST.LIST_EDA = LB.LIST_EDA(+)" 

		'僔僗僥儉ID AND 挔昜斣崋 AND 挔昜斣崋巬斣 AND 嶍彍僼儔僌亗1
		l_strsql = l_strsql & " AND ST.SYSTEM_ID  = '" & l_sysId & "'"  & _
				" AND ST.LIST_CD = '" & l_listCd & "'" & _
				" AND ST.LIST_EDA = '" & l_listEda & "'" & _
				" AND ST.DEL_FLG <> '1'"

		'DB傛傝儗僐乕僪傪庢摼偟偰曉偡
		Set SearchStockRec = l_dbaccess.ProcRecordset(l_strsql,1,0)
	End Function

	'/*******************************************************
	'/* 嵼屔忣曬傪峏怴偡傞(塣梡娗棟丒嵼屔儗僐乕僪擖椡夋柺)
	'/* param丗dbaccess(DBAccess) 	DB傾僋僙僗
	'/*        l_inRec(FormStockMdl) 峏怴儗僐乕僪
	'/* return ADODB.Recordset 専嶕寢壥
	'/*******************************************************
	Public Function UpdateFormStockRec(l_dbaccess, l_inrec)
		Dim l_sql 
		Dim l_charUtil
		Set l_charUtil = New CharUtil

		UpdateFormStockRec = True

		'SQL暥惗惉
		'嵼屔悢亖僥乕僽儖偺悢検侾亄僥乕僽儖偺悢検俀亄僥乕僽儖偺悢検俁
		'悢検侾乣俁亖僥乕僽儖偺嵼屔悢亄擖椡抣(嵎暘擖椡)偟偨傕偺傪愝掕偡傞
		'UPDATE  STOCK_T SET 
		l_sql = "UPDATE STOCK_T SET " & _
				"STOCK_NUM = NVL(NUM1, 0)+" & l_charUtil.GetNumSql(l_inrec.l_Num1) & _
				"+ NVL(NUM2, 0)+" & l_charUtil.GetNumSql(l_inrec.l_Num2) & _
				"+ NVL(NUM3, 0)+" & l_charUtil.GetNumSql(l_inrec.l_Num3) & "," & _
				"ORDER_POINT = " & l_charUtil.GetNumSql(l_inrec.l_orderPoint) & "," & _
				"URGENT_ORDER_POINT = " & l_charUtil.GetNumSql(l_inrec.l_urgentOrderPoint) & "," & _
				"DELIVERY_PLACE1 = '" & l_inrec.l_deliveryPlace1 & "'," & _
				"NUM1 =  NVL(NUM1, 0)+" & l_charUtil.GetNumSql(l_inrec.l_Num1) & "," & _
				"DELIVERY_PLACE2 = '" & l_inrec.l_deliveryPlace2 & "'," & _
				"NUM2 = NVL(NUM2, 0)+" & l_charUtil.GetNumSql(l_inrec.l_Num2) & "," & _
				"DELIVERY_PLACE3 = '" & l_inrec.l_deliveryPlace3 & "'," & _
				"NUM3 = NVL(NUM3, 0)+" & l_charUtil.GetNumSql(l_inrec.l_Num3) & "," & _
				"UPDATE_YMD= SYSDATE"

		'WHERE 僔僗僥儉ID AND 挔昜斣崋 AND 挔昜斣崋巬斣 AND 嶍彍僼儔僌亗1
		l_sql = l_sql & " WHERE SYSTEM_ID  = '" & l_inrec.l_sysId & "'"  & _
				" AND LIST_CD = '" & l_inrec.l_listCd & "'" & _
				" AND LIST_EDA = '" & l_inrec.l_listEda & "'" & _
				" AND DEL_FLG <> '1'"

		'SQL敪峴
		If Not l_dbaccess.ProcExecute(l_sql) Then
			'僄儔乕偺応崌偼儘乕儖僶僢僋
			l_dbaccess.ProcRollBack
			UpdateFormStockRec = False
			Exit Function
		End If
	End Function

	'/*******************************************************
	'/* 嵼屔忬嫷傪峏怴偡傞
	'/* param丗dbaccess(DBAccess) 	DB傾僋僙僗
	'/*        l_sysid(String) 		僔僗僥儉ID
	'/*		   l_listCd				挔昜斣崋
	'/*		   l_listEda			挔昜斣崋巬斣
	'/*		   l_stockStatus		嵼屔忬嫷
	'/* return 峏怴寢壥丂True丗惓忢丄False丗堎忢
	'/*******************************************************
	Public Function UpdateStockStatus(l_dbaccess, l_sysId, l_listCd, l_listEda, l_stockStatus)
		Dim l_sql 

		UpdateStockStatus = True

		'SQL暥惗惉
		'UPDATE  STOCK_T SET 
		l_sql = "UPDATE STOCK_T SET " & _
				"STOCK_STATUS = '" & l_stockStatus & "'," & _
				"UPDATE_YMD= SYSDATE"

		'WHERE 僔僗僥儉ID AND 夛幮ID AND 挔昜斣崋 AND 挔昜斣崋巬斣 AND 嶍彍僼儔僌亗1
		l_sql = l_sql & " WHERE SYSTEM_ID  = '" & l_sysId & "'"  & _
				" AND LIST_CD = '" & l_listCd & "'" & _
				" AND LIST_EDA = '" & l_listEda & "'" & _
				" AND DEL_FLG <> '1'"

		'SQL敪峴
		If Not l_dbaccess.ProcExecute(l_sql) Then
			'僄儔乕偺応崌偼儘乕儖僶僢僋
			l_dbaccess.ProcRollBack
			UpdateStockStatus = False
			Exit Function
		End If
	End Function
End Class
%>