demlistbasemaster.asp

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

<%
'/
'/僥乕僽儖傾僋僙僗僋儔僗
Class DemListBaseMaster

	'/*******************************************************
	'/* 帒椏惪媮偺嵟怴挔昜堦棗夋柺偵昞帵偡傞儗僐乕僪傪拪弌偡傞
	'/* param丗l_dbaccess(DBAccess) 	DB傾僋僙僗
	'/* return ADODB.Recordset 専嶕寢壥
	'/*******************************************************
	Public Function SearchDemandList(l_dbaccess,l_seckind)
		Dim l_SQL	'String
		
		'SQL暥嶌惉
		'SELECT
		l_SQL = "SELECT DISTINCT LB.LIST_NAME," &_
					"LB.CLASS1," &_
					"LB.LIST_CD," & _
					"LB.LIST_EDA," & _
					"LB.UNIT_PRICE," &_
					"LB.USEPOSSIBLE_TERM_END," &_
					"LB.LIST_MANAGE_NO," &_
					"LB.OPEN_IMAGE_FLAG," &_
					"LB.OPEN_PERIOD_START," &_
					"LB.CLAIM_PERIOD_START," &_
					"LI.THUMBNAIL," &_
					"CD.NAME," &_
					"(SELECT NAME " &_
					"   FROM CODE_M " &_
					"  WHERE SYSTEM_ID = LB.SYSTEM_ID " &_
					"    AND CLASSIFIED_CODE = '" & CD_CLASS1_COLOR & "' " &_
					"    AND CODE = LB.CLASS1" &_
					"    AND DEL_FLG = '" & FLG_OFF & "') AS BG_COLLOR," &_
					"(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 CD_CLASS1_NAME," &_
					"LB.REG_YMD," &_
					"LB.UPDATE_YMD"
		'FROM
		l_SQL = l_SQL & " FROM LISTBASE_T LB,LISTIMAGE_M LI,CODE_M CD,STOCK_T ST,ORDER_T ODR "
		'WHERE 僔僗僥儉ID丄夛幮ID丄挔昜斣崋丄巬斣(LB,LI)
		l_SQL = l_SQL & " WHERE LB.SYSTEM_ID=LI.SYSTEM_ID(+)" &_
							" AND LB.COMPANY_ID=LI.COMPANY_ID(+)" &_
							" AND LB.LIST_CD=LI.LIST_CD(+)" &_
							" AND LB.LIST_EDA=LI.LIST_EDA(+)"

		'AND 僔僗僥儉ID丄夛幮ID丄僐乕僪(LB,CD)
		l_SQL = l_SQL & " AND LB.SYSTEM_ID=CD.SYSTEM_ID" &_
							" AND LB.CLASS1=CD.CODE"

		'AND 挔昜斣崋丄巬斣(LB,ODR)
		l_SQL = l_SQL & " AND LB.SYSTEM_ID=ODR.SYSTEM_ID(+) " &_
							" AND LB.COMPANY_ID=ODR.COMPANY_ID(+) " &_
							" AND LB.LIST_CD=ODR.LIST_CD(+) " &_
							" AND LB.LIST_EDA=ODR.LIST_EDA(+) "

		'AND 僔僗僥儉ID丄夛幮ID丄暘椶僐乕僪丄嶍彍僼儔僌
		l_SQL = l_SQL & " AND LB.SYSTEM_ID = " & "'" & Session("User").Item("SYSTEM_ID") & "'" &_
							" AND LB.COMPANY_ID = (SELECT COMPANY_ID " &_
							"                        FROM COMPANY_M " &_
							"                       WHERE SYSTEM_ID = '" & Session("User").Item("SYSTEM_ID") & "' " &_
							"                         AND COMPANY_KIND = '0')" &_
							" AND CD.CLASSIFIED_CODE='" & CD_CLASS1 & "'" &_
							" AND LB.DEL_FLG=" & "'0'"

		'AND 岞奐尃尷
		l_SQL = GetOpenControl(l_SQL,l_seckind)

		'AND 岞奐婜娫撪 1儢寧枹枮偺挔昜丂1/19廋惓
		l_SQL = l_SQL & " AND LB.OPEN_PERIOD_START <= SYSDATE " &_
						" AND LB.OPEN_PERIOD_END >= SYSDATE" &_
						" AND MONTHS_BETWEEN(SYSDATE,LB.OPEN_PERIOD_START) <= 1 "
		'AND 僗僥乕僞僗
		l_SQL = l_SQL & " AND LB.LIST_STATUS IN('" & LSTS_DRAFT & _
												"','" & LSTS_CONF & _
												"','" & LSTS_APPROVE & "')"
		'AND 嵼屔僥乕僽儖
		l_SQL = l_SQL & " AND LB.SYSTEM_ID = ST.SYSTEM_ID(+) " &_
						" AND LB.COMPANY_ID = ST.COMPANY_ID(+) " &_
						" AND LB.LIST_CD = ST.LIST_CD(+) " &_
						" AND LB.LIST_EDA = ST.LIST_EDA(+) "
		'AND 挔昜僗僥乕僞僗(桳岠)
		l_SQL = l_SQL & " AND LB.LIST_STATUS = '" & LSTS_APPROVE & "' "
		'AND 敪拲僗僥乕僞僗 OR 嵼屔桳傝 OR 嵼屔側偟偱擖壸懸偪壜擻
		l_SQL = l_SQL & " AND (ODR.ORDER_STATUS IN ('" & OSTS_APPROVE_FIN &_
						"','" & OSTS_ORDER &_
						"','" & OSTS_ORDER_CANCEL &_
						"','" & OSTS_DELIVERY_INST &_
						"','" & OSTS_DELIVERY &_
						"','" & OSTS_DELIVERY_CONF &_
						"','" & OSTS_PAYMENT_CONF &_
						"','" & OSTS_PAYMENT_FIN & "') " &_
						" OR ST.STOCK_NUM>0 " &_
						" OR (ST.STOCK_NUM <= 0 " &_
						" AND LB.ARRIVAL_WAIT = '1')) "



		'岞奐婜娫奐巒弴偵僜乕僩
	'	l_SQL = l_SQL & " ORDER BY LB.OPEN_PERIOD_START DESC"
		'岞奐婜娫奐巒 + 搊榐擔弴偵僜乕僩
	'	l_SQL = l_SQL & " ORDER BY LB.OPEN_PERIOD_START DESC,LB.REG_YMD DESC"
		'岞奐婜娫奐巒 + 惪媮婜娫奐巒 + 峏怴擔 + 搊榐擔 + 挔昜斣崋弴偵僜乕僩
		l_SQL = l_SQL & " ORDER BY LB.OPEN_PERIOD_START DESC,LB.CLAIM_PERIOD_START DESC,LB.UPDATE_YMD DESC,LB.REG_YMD DESC,LB.LIST_CD,LB.LIST_EDA"

		'DB傛傝堦棗傪庢摼偟偰曉偡
'response.Write l_SQL

		Set SearchDemandList = l_dbaccess.ProcRecordset(l_SQL,8,0)
debug_t(l_SQL)		
	End Function

	'/*******************************************************
	'/* 帒椏惪媮偺挔昜堦棗夋柺偵昞帵偡傞儗僐乕僪傪拪弌偡傞
	'/* param丗l_dbaccess(DBAccess) 	DB傾僋僙僗
	'/* 	丂 l_indata(Scripting.Dictionary)丂夋柺擖椡抣
	'/* return ADODB.Recordset 専嶕寢壥
	'/*******************************************************
	Public Function SearchDemandForm(l_dbaccess,l_insql,l_insort,l_seckind)
		Dim l_SQL	'String
		
		'SQL暥嶌惉
		'SELECT
		l_SQL = "SELECT DISTINCT LB.SYSTEM_ID,LB.LIST_NAME," &_
					"LB.CLASS1," &_
					"LB.LIST_CD," & _
					"LB.LIST_EDA," & _
					"LB.UNIT_PRICE," &_
					"LB.USEPOSSIBLE_TERM_END," &_
					"LB.LIST_MANAGE_NO," &_
					"LB.OPEN_IMAGE_FLAG," &_
					"LB.OPEN_PERIOD_START," &_
					"LI.THUMBNAIL," &_
					"CD.NAME," &_
					"(SELECT NAME " &_
					"   FROM CODE_M " &_
					"  WHERE SYSTEM_ID = LB.SYSTEM_ID " &_
					"    AND CLASSIFIED_CODE = '" & CD_CLASS1_COLOR & "' " &_
					"    AND CODE = LB.CLASS1" &_
					"    AND DEL_FLG = '" & FLG_OFF & "') AS BG_COLLOR," &_
					"(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 CD_CLASS1_NAME " 

		'FROM
		l_SQL = l_SQL & " FROM LISTBASE_T LB,LISTIMAGE_M LI,CODE_M CD,ORDER_T ODR,STOCK_T ST "
		'AND 僔僗僥儉ID丄夛幮ID丄挔昜斣崋丄巬斣(LB,LI)
		l_SQL = l_SQL & " WHERE LB.SYSTEM_ID=LI.SYSTEM_ID(+)" &_
							" AND LB.COMPANY_ID=LI.COMPANY_ID(+)" &_
							" AND LB.LIST_CD=LI.LIST_CD(+)" &_
							" AND LB.LIST_EDA=LI.LIST_EDA(+)"

		'AND 僔僗僥儉ID丄夛幮ID丄僐乕僪(LB,CD)
		l_SQL = l_SQL & " AND LB.SYSTEM_ID=CD.SYSTEM_ID" &_
							" AND LB.CLASS1=CD.CODE"

		'AND 僔僗僥儉ID丄夛幮ID=僙僢僔儑儞丄暘椶僐乕僪=暘椶侾丄嶍彍僼儔僌=0
		l_SQL = l_SQL & " AND LB.SYSTEM_ID = " & "'" & Session("User").Item("SYSTEM_ID") & "'" &_
							" AND LB.COMPANY_ID = (SELECT COMPANY_ID " &_
							"                        FROM COMPANY_M " &_
							"                       WHERE SYSTEM_ID = '" & Session("User").Item("SYSTEM_ID") & "' " &_
							"                         AND COMPANY_KIND = '0')" &_
							" AND CD.CLASSIFIED_CODE ='" & CD_CLASS1 & "'" &_
							" AND LB.DEL_FLG='0'"

		'AND 挔昜斣崋丄巬斣(LB,ODR)
		l_SQL = l_SQL & " AND LB.SYSTEM_ID=ODR.SYSTEM_ID(+) " &_
							" AND LB.COMPANY_ID=ODR.COMPANY_ID(+) " &_
							" AND LB.LIST_CD=ODR.LIST_CD(+) " &_
							" AND LB.LIST_EDA=ODR.LIST_EDA(+) "

		'AND 岞奐尃尷
		l_SQL = GetOpenControl(l_SQL,l_seckind)

		'AND 専嶕忦審
		l_SQL = l_SQL & l_insql

		'AND 岞奐婜娫撪
		l_SQL = l_SQL & " AND LB.OPEN_PERIOD_START <= SYSDATE" &_
		" AND LB.OPEN_PERIOD_END >= SYSDATE"

		'AND 僗僥乕僞僗(桳岠)
		l_SQL = l_SQL & " AND LB.LIST_STATUS IN('" & LSTS_APPROVE & "')"

		'AND 嵼屔僥乕僽儖
		l_SQL = l_SQL & " AND LB.SYSTEM_ID = ST.SYSTEM_ID(+) " &_
						" AND LB.COMPANY_ID = ST.COMPANY_ID(+) " &_
						" AND LB.LIST_CD = ST.LIST_CD(+) " &_
						" AND LB.LIST_EDA = ST.LIST_EDA(+) "
		'AND 敪拲僗僥乕僞僗 OR 嵼屔僗僥乕僞僗 OR 嵼屔側偟偱擖壸懸偪壜擻
		l_SQL = l_SQL & " AND (ODR.ORDER_STATUS IN ('" & OSTS_APPROVE_FIN &_
						"','" & OSTS_ORDER &_
						"','" & OSTS_ORDER_CANCEL &_
						"','" & OSTS_DELIVERY_INST &_
						"','" & OSTS_DELIVERY &_
						"','" & OSTS_DELIVERY_CONF &_
						"','" & OSTS_PAYMENT_CONF &_
						"','" & OSTS_PAYMENT_FIN & "') " &_
						" OR ST.STOCK_NUM>0 " &_
						" OR (ST.STOCK_NUM <= 0 " &_
						" AND LB.ARRIVAL_WAIT = '1')) "

		'僜乕僩偺愝掕
		l_SQL = l_SQL & l_insort
'response.write l_SQL
		debug_t(l_SQL)
		'DB傛傝堦棗傪庢摼偟偰曉偡
		Set SearchDemandForm = l_dbaccess.ProcRecordset(l_SQL,MAXROW_DEMAND_FORM_LIST,1)
	End Function

	'/*******************************************************
	'/* 帒椏惪媮偺徻嵶忣曬夋柺偵昞帵偡傞儗僐乕僪傪拪弌偡傞
	'/* param丗l_dbaccess(DBAccess) 	DB傾僋僙僗
	'/* 	丂 l_indata(Scripting.Dictionary)丂夋柺擖椡抣
	'/* return ADODB.Recordset 専嶕寢壥
	'/*******************************************************
	Public Function SearchFormDetails(l_dbaccess,l_indata)
		Dim l_SQL	'String
		
		'SQL暥嶌惉
		'SELECT
		l_SQL = "SELECT LB.SYSTEM_ID,LB.LIST_NAME," &_
					"LB.OPEN_PERIOD_START," &_
					"LB.CLASS1," &_
					"LB.LIST_MANAGE_NO," &_
					"LB.LIST_CD," & _
					"LB.LIST_EDA," & _
					"LB.CLASS1," & _
					"LB.CLASS2," & _
					"LB.CLASS3," & _
					"LB.RESULT_SIZE," & _
					"LB.RESULT_ROW," & _
					"LB.RESULT_COL," & _
					"LB.RESULT_UNIT," & _
					"LB.PAGE_COUNT," & _
					"LB.PACKING_COUNT," & _
					"LB.USEPOSSIBLE_TERM_END," &_
					"LB.STORAGE_PERIOD1," & _
					"LB.STORAGE_PERIOD_OTHER1," & _
					"LB.STORAGE_PERIOD2," & _
					"LB.STORAGE_PERIOD_OTHER2," & _
					"LB.STORAGE_PERIOD3," & _
					"LB.STORAGE_PERIOD_OTHER3," & _
					"LB.CLAIM_PERIOD_START," & _
					"TO_CHAR(LB.CLAIM_PERIOD_START,'YYYY/MM/DD') AS CLAIM_PERIOD_START2," &_
					"LB.CLAIM_PERIOD_END," & _
					"TO_CHAR(LB.CLAIM_PERIOD_END,'YYYY/MM/DD') AS CLAIM_PERIOD_END2," &_
					"LB.CLAIM_PERIOD_FLAG," & _
					"LB.LIST_CLASS1," & _
					"LB.STORAGE_PERIOD1," & _
					"LB.STORAGE_PERIOD_OTHER1," & _
					"LB.HEADOFFICE_KEEP1," & _
					"LB.LIST_CLASS2," & _
					"LB.STORAGE_PERIOD2," & _
					"LB.STORAGE_PERIOD_OTHER2," & _
					"LB.HEADOFFICE_KEEP2," & _
					"LB.LIST_CLASS3," & _
					"LB.STORAGE_PERIOD3," & _
					"LB.STORAGE_PERIOD_OTHER3," & _
					"LB.HEADOFFICE_KEEP3," & _
					"LB.OPEN_IMAGE_FLAG," &_
					"LB.LISTNUMBER_OLD1," &_
					"LB.LISTNUMBER_OLD2," &_
					"LB.LISTNUMBER_OLD3," &_
					"LB.LISTNUMBER_NEW1," &_
					"LB.LISTNUMBER_NEW2," &_
					"LB.LISTNUMBER_NEW3," &_
					"LB.DATACLAIM_NOTE," &_
					"LB.RELATED_LIST1," &_
					"LB.RELATED_LIST2," &_
					"LB.RELATED_LIST3," &_
					"LB.RELATED_LIST4," &_
					"LB.RELATED_LIST5," &_
					"LB.RELATED_LIST6," &_
					"LB.CATEGORY1," &_
					"LB.CATEGORY2," &_
					"LB.CATEGORY3," &_
					"LB.CATEGORY4," &_
					"LB.CATEGORY5," &_
					"LI.THUMBNAIL," &_
					"LI.LISTIMAGE1," &_
					"(SELECT NAME " &_
					"   FROM CODE_M " &_
					"  WHERE SYSTEM_ID = LB.SYSTEM_ID " &_
					"    AND CLASSIFIED_CODE = '" & CD_LIST_UNIT & "' " &_
					"    AND CODE = LB.LIST_UNIT" &_
					"    AND DEL_FLG = '" & FLG_OFF & "') AS LIST_UNIT," &_
					"(SELECT NAME " &_
					"   FROM CODE_M " &_
					"  WHERE SYSTEM_ID = LB.SYSTEM_ID " &_
					"    AND CLASSIFIED_CODE = '" & CD_CLASS1_COLOR & "' " &_
					"    AND CODE = LB.CLASS1" &_
					"    AND DEL_FLG = '" & FLG_OFF & "') AS BG_COLLOR," &_
					"(SELECT NAME " &_
					"   FROM CODE_M " &_
					"  WHERE SYSTEM_ID = LB.SYSTEM_ID " &_
					"    AND CLASSIFIED_CODE = '" & CD_CLASS1 & "' " &_
					"    AND CODE = LB.CLASS1" &_