fmstockmaster.asp

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

<%
'/
'/挔昜嶌惉婡擻
'/嵼屔婎杮僥乕僽儖傾僋僙僗僋儔僗
'/
Class FmStockMaster
	'/*******************************************************
	'/* 嵼屔堦棗夋柺偵昞帵偡傞儗僐乕僪傪拪弌偡傞
	'/* param丗dbaccess(DBAccess) 	DB傾僋僙僗
	'/*        sysid(String) 僔僗僥儉ID
	'/*        coid(String) 夛幮ID
	'/*		   l_indata(Scripting.Dictionary) 夋柺擖椡抣
	'/* return ADODB.Recordset 専嶕寢壥
	'/*******************************************************
	Public Function SearchStockList(l_dbaccess, l_cutil, l_sysid, l_coid, l_indata)
		Dim l_strsql	'String
		
		'SQL暥嶌惉
		'SELECT
		l_strsql = "SELECT ST.SYSTEM_ID," &_
			"ST.COMPANY_ID," &_
			"ST.LIST_CD," &_
			"ST.LIST_EDA," 
		'	"ST.STOCK_NUM," &_
		l_strsql = l_strsql & "TO_CHAR(ST.STOCK_NUM,'999,999,999,999,999') AS STOCK_NUM," &_
			"ST.STOCK_STATUS," 
		'	"ST.ORDER_POINT," &_
		l_strsql = l_strsql & "TO_CHAR(ST.ORDER_POINT,'999,999,999,999') AS 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," &_
			"ST.REG_YMD," &_
			"ST.UPDATE_YMD," &_
			"ST.DEL_FLG," &_
			"LB.LIST_STATUS," &_
			"LB.LIST_MANAGE_NO," &_
			"LB.LIST_NAME," &_
			"LB.SECTION_ID," &_
			"LB.CLASS1," &_
			"LB.LIST_UNIT," &_
			"LB.SECRET_FLG," &_
			"LB.SECRET_PASSWORD," &_
       		"(SELECT NAME " &_
       		"   FROM CODE_M " &_
            "   WHERE SYSTEM_ID = ST.SYSTEM_ID " &_
	        "    AND CLASSIFIED_CODE = '" & CD_LIST_UNIT & "' " &_
       		"    AND CODE=LB.LIST_UNIT) AS LIST_UNIT_NAME," &_
       		"(SELECT NAME " &_
       		"   FROM CODE_M " &_
            "   WHERE SYSTEM_ID = ST.SYSTEM_ID " &_
	        "    AND CLASSIFIED_CODE = '" & CD_STOCK_STATUS & "' " &_
       		"    AND CODE=ST.STOCK_STATUS) AS STOCK_STATUS_NAME," &_
      		"(SELECT SECTION_NAME " &_
		    "   FROM SECTION_M " &_ 
        	"  WHERE SYSTEM_ID = LB.SYSTEM_ID " &_   
          	"    AND COMPANY_ID=LB.COMPANY_ID " &_
          	"    AND SECTION_ID=LB.SECTION_ID) AS SECTION_NAME," &_
	        "(SELECT NAME " &_
       	    "   FROM CODE_M " &_ 
            "   WHERE SYSTEM_ID = LB.SYSTEM_ID " &_
       		"     AND CLASSIFIED_CODE='" & CD_CLASS1 & "' " &_
       		"     AND CODE=LB.CLASS1 " &_
       		"     AND DEL_FLG = '" & FLG_OFF & "') AS CLASS1_NAME" 

		'FROM LISTBSE_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.COMPANY_ID = LB.COMPANY_ID" & _
					" AND ST.LIST_CD = LB.LIST_CD" & _
					" AND ST.LIST_EDA = LB.LIST_EDA" & _
					" AND LB.DEL_FLG <> '" & FLG_ON & "'"

		'僔僗僥儉ID AND 夛幮ID
		l_strsql = l_strsql & " AND ST.SYSTEM_ID =" & "'" & l_sysid & "'"
'		l_strsql = l_strsql & " AND ST.COMPANY_ID = '" & l_coid & "'"

		If IsObject(l_indata) Then
			'AND 挔昜斣崋
			If l_indata.Item("listcd") <> "" Then
				l_strsql = l_strsql & " AND ST.LIST_CD=" & "'" & l_indata.Item("listcd") & "'"
			End If
			'AND 挔昜斣崋巬斣
			If l_indata.Item("listeda") <> "" Then
				l_strsql = l_strsql & " AND ST.LIST_EDA=" & "'" & l_indata.Item("listeda") & "'"
			End If
			'AND 挔昜柤 2006/1/11 慜曽堦抳偵廋惓
			If l_indata.Item("listname") <> "" Then
				l_strsql = l_strsql & " AND LB.LIST_NAME LIKE('" & l_cutil.chg_HantoZen(l_indata.Item("listname")) & "%')"
			End If
			'AND 暘椶侾
			If l_indata.Item("pdclass1") <> ""  Then
				l_strsql = l_strsql & " AND LB.CLASS1=" & "'" & l_indata.Item("pdclass1") & "'"
			End If
			'AND 庡娗晹栧
			If l_indata.Item("pdsel") <> "" Then
				l_strsql = l_strsql & " AND LB.SECTION_ID=" & "'" & l_indata.Item("pdsel") & "'"
			End If
			'AND 嵼屔忬嫷
			If l_indata.Item("chkststatus") <> "" Then
				Dim l_checkboxArray,l_row
				l_checkboxArray = Split(l_indata.Item("chkststatus"),",")
				l_strsql = l_strsql & " AND ST.STOCK_STATUS IN ('" & l_checkboxArray(0) & "'"
				For l_row = 1 To UBound(l_checkboxArray)
					l_strsql = l_strsql & ",'" & Trim(l_checkboxArray(l_row)) & "'"
				Next
				l_strsql = l_strsql & ")"
			End If
		End If

		If IsObject(l_indata) Then
			'ORDER BY
			l_strsql = l_strsql & " ORDER BY " & GetOrderByStr(l_indata.Item("sortkey"),l_indata.Item("sortdesc"))
		Else
			'ORDER BY (擖椡抣偑側偄応崌偼僨僼僅儖僩偺僜乕僩弴愝掕)
		'	l_strsql = l_strsql & " ORDER BY ST.STOCK_STATUS DESC, ST.ORDER_POINT DESC, ST.STOCK_NUM, LB.LIST_CD DESC, LB.LIST_EDA DESC"
			l_strsql = l_strsql & " ORDER BY ST.STOCK_STATUS DESC, ORDER_POINT DESC, STOCK_NUM, LB.LIST_CD DESC, LB.LIST_EDA DESC"
		End If
		'僨僶僢僌暥
		debug_t("SQL丗" & l_strsql)
		'DB傛傝堦棗傪庢摼偟偰曉偡(1儁乕僕偺峴悢傪愝掕)
		Set SearchStockList = l_dbaccess.ProcRecordset(l_strsql,MAXROW_STOCK_LIST,1)

	End Function

	'/*******************************************************
	'/* 僜乕僩暥庢摼
	'/* param丗l_sortkey(String) 僜乕僩僉乕
	'/* return String 僜乕僩暥
	'/*******************************************************
	Public Function GetOrderByStr(l_sortkey, l_sortdesc)
		Dim l_retStr	'String
		Dim l_seq		'String

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

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

		GetOrderByStr = l_retStr
	End Function

	'/*******************************************
	'/* 嵼屔僥乕僽儖(嬻僨乕僞)Insert
	'/* 擺昳応強偵偼僨僼僅儖僩偱憅屔夛幮偺忋偐傜俁偮傪愝掕
	'/* param丗l_db DBAccess
	'/* 	   l_stockmdl StockMdl僆僽僕僃僋僩
	'/*******************************************
	Public Function InsertStockRec(l_db,l_stockmdl)
		Dim l_strSql

		l_strSql = "INSERT INTO STOCK_T(" &_
				   "SYSTEM_ID," &_
				   "COMPANY_ID," &_
				   "LIST_CD," &_
				   "LIST_EDA," &_
				   "STOCK_NUM," &_
				   "STOCK_STATUS," &_
				   "ABC_RANK," &_
				   "ORDER_POINT," &_
				   "ORDER_POINT_YMD," &_
				   "URGENT_ORDER_POINT," &_
				   "URGENT_ORDER_POINT_YMD," &_
				   "ORDER_YMD," &_
				   "STOCK_RUN_OUT_YMD," &_
				   "DELIVERY_PLACE1," &_
				   "NUM1," &_
				   "RESERVE_STOCK_NUM1," &_
				   "DELIVERY_PLACE2," &_
				   "NUM2," &_
				   "RESERVE_STOCK_NUM2," &_
				   "DELIVERY_PLACE3," &_
				   "NUM3," &_
				   "RESERVE_STOCK_NUM3," &_
				   "REG_YMD," &_
				   "DEL_FLG)" &_
				   "VALUES('" &_
				   l_stockmdl.l_sysId & "','" &_
				   l_stockmdl.l_coId & "','" &_
				   l_stockmdl.l_listCd & "','" &_
				   l_stockmdl.l_listEda & "'," &_
				   "0,'000001',null,0,null,0,null,null,null,'" &_
				   l_stockmdl.l_deliveryPlace1 & "'," &_
				   "0,0,'" &_
				   l_stockmdl.l_deliveryPlace2 & "'," &_
				   "0,0,'" &_
				   l_stockmdl.l_deliveryPlace3 & "'," &_
				   "0,0," &_
				   "SYSDATE,'0')"

		InsertStockRec = true
'Response.Write l_strsql
'		If l_db.ProcBeginTrans Then
			If l_db.ProcExecute(l_strSql) Then
				'Call l_db.ProcCommit
			Else
				'Call l_db.ProcRollBack
				InsertStockRec = false
			End If
'		Else
			'InsertStockRec = false
'		End If
	End Function

	'/*******************************************************
	'/* 嵼屔僥乕僽儖偺擺昳応強傪峏怴偡傞(挔昜娗棟丒敪拲忣曬擖椡夋柺偱巊梡偡傞)
	'/* param丗dbaccess(DBAccess) 	DB傾僋僙僗
	'/*        l_inrec(FormStockMdl) 峏怴儗僐乕僪
	'/* return ADODB.Recordset 専嶕寢壥
	'/*******************************************************
	Public Function UpdateDeliveryPlace(l_dbaccess, l_inrec)
		Dim l_sql 

		UpdateDeliveryPlace = True

		'SQL暥惗惉
		'UPDATE  STOCK_T SET 
		l_sql = "UPDATE STOCK_T SET " & _
				"DELIVERY_PLACE1 = '" & l_inrec.l_deliveryPlace1 & "'," & _
				"DELIVERY_PLACE2 = '" & l_inrec.l_deliveryPlace2 & "'," & _
				"DELIVERY_PLACE3 = '" & l_inrec.l_deliveryPlace3 & "'," & _
				"UPDATE_YMD= SYSDATE"

		'WHERE 僔僗僥儉ID AND 夛幮ID AND 挔昜斣崋 AND 挔昜斣崋巬斣 AND 嶍彍僼儔僌亗1
		l_sql = l_sql & " WHERE SYSTEM_ID  = '" & l_inrec.l_sysId & "'"  & _
				" AND COMPANY_ID = '" & l_inrec.l_coId & "'" & _
				" 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
			UpdateDeliveryPlace = False
			Exit Function
		End If
	End Function

	'/*******************************************************
	'/* 嵼屔丄帒椏惪媮敪拲偺僠僃僢僋
	'/* param丗dbaccess(DBAccess) 	DB傾僋僙僗
	'/*        sysid(String) 僔僗僥儉ID
	'/*        coid(String) 夛幮ID
	'/*        l_listcd(String) 挔昜斣崋
	'/*        l_listeda(String) 挔昜巬斣
	'/* return boolean True丗巜掕儗僐乕僪偑懚嵼偡傞丄False丗懚嵼偟側偄
	'/*******************************************************
	Public Function CheckStockDataclaim(l_db,l_sysId,l_coId,l_listcd,l_listeda)
		Dim l_recSet,l_recSet2
		Dim l_sql
		Dim l_stock,l_dataclaim

		CheckStockDataclaim = False
		l_stock = false
		l_dataclaim = false

		'嵼屔僥乕僽儖僠僃僢僋
		'嵼屔悢亜侽偺儗僐乕僪偑偁傟偽true丄側偗傟偽false
		l_sql = "SELECT COUNT(*) FROM STOCK_T" & _
				" WHERE SYSTEM_ID  = '" & l_sysId & "'"  & _
				"   AND COMPANY_ID = '" & l_coId & "'" & _
				"   AND LIST_CD = '" & l_listcd & "'" & _
				"   AND LIST_EDA = '" & l_listeda & "'" & _
				"   AND STOCK_NUM>0 " & _
				"   AND DEL_FLG <> '" & FLG_ON & "'"

		Set l_recSet = l_db.ProcRecordset(l_sql,1,0)

		If l_recSet.Fields.Item("COUNT(*)") <> 0 Then
			l_stock = true
		End If

		'帒椏惪媮敪拲儅僗僞僠僃僢僋
		'巜掕偟偨挔昜偑帒椏惪媮偝傟偰偄傟偽true丄偦傟埲奜偼false
		l_sql = "SELECT COUNT(*) FROM DATACLAIMORDER_M" & _
				" WHERE SYSTEM_ID  = '" & l_sysId & "'"  & _
				"   AND LIST_CD = '" & l_listcd & "'" & _
				"   AND LIST_EDA = '" & l_listeda & "'" & _
				"   AND DEL_FLG <> '" & FLG_ON & "'"

		Set l_recSet2 = l_db.ProcRecordset(l_sql,1,0)

		If l_recSet2.Fields.Item("COUNT(*)") <> 0 Then
			l_dataclaim = true
		End If

		'侽埲忋偺嵼屔偑懚嵼偟側偄丄偐偮丄帒椏惪媮偝傟偰偄側偗傟偽true
		If Not l_stock and Not l_dataclaim Then
			CheckStockDataclaim = True
		End If
	End Function

	'/*******************************************************
	'/* 嵼屔僨乕僞懚嵼僠僃僢僋
	'/* param丗dbaccess(DBAccess) 	DB傾僋僙僗
	'/*        sysid(String) 僔僗僥儉ID
	'/*        l_listcd(String) 挔昜斣崋
	'/*        l_listeda(String) 挔昜巬斣
	'/* return boolean True丗巜掕儗僐乕僪偑懚嵼偡傞丄False丗懚嵼偟側偄
	'/*******************************************************
	Public Function ExistStockT(dbaccess,sysid,l_listcd,l_listeda)
		Dim l_SQL
		Dim l_rec

		l_SQL = "SELECT COUNT(*) FROM STOCK_T " &_
				" WHERE SYSTEM_ID = '" & sysid & "' " &_
				"   AND LIST_CD = '" & l_listcd & "' " &_
				"   AND LIST_EDA = '" & l_listeda & "' "

'Response.Write l_sql

		Set l_rec = dbaccess.ProcRecordset(l_SQL,1,0)

		If l_rec.Fields.Item("COUNT(*)") <> 0 Then
			ExistStockT = true
		Else
			ExistStockT = false
		End If

	End Function
End Class
%>