searchdataclaimordermaster.asp

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

<%
Class SearchDataClaimOrderMaster
	Dim l_SQL

	'帒椏惪媮敪拲忣曬庢摼
	Public Function SearchDataClaimOrderBase(Param1,Param2,Param3)
		Dim l_StrYMD,l_EndYMD

		l_SQL = "SELECT DISTINCT D_M.RECEPT_NO,TO_CHAR(D_M.RECEPT_YMD,'YYYY/MM/DD HH24:MI') AS RECEPT_YMD," & _
				"D_M.DELIVERY_ID,D_M.RECEIVE_NAME,D_M.BUILDING,D_M.COMPANY_NAME,D_M.SECTION_NAME,D_M.CLAIM_METHOD,D_M.TOTAL_SITUATION," &_
				"(SELECT SUM(SUB_TOTAL) FROM DATACLAIMORDER_M " &_
				"                      WHERE SYSTEM_ID = D_M.SYSTEM_ID " &_
				"                        AND COMPANY_ID = D_M.COMPANY_ID " &_
				"                        AND RECEPT_NO = D_M.RECEPT_NO " &_
				"                        AND DELIVERY_ID = D_M.DELIVERY_ID " &_
				"                        AND DEL_FLG <> '1' ) AS SUB_TOTAL " &_
				"  FROM DATACLAIMORDER_M D_M " & _
				" WHERE D_M.SYSTEM_ID = '" & Param2.l_SystemId & "'" & _
				"   AND D_M.COMPANY_ID = '" & Param2.l_CompanyId & "'" & _
				"   AND D_M.SECTION_ID = '" & Param2.l_SectionId & "'" &_
				"   AND D_M.USER_ID = '" & Param2.l_UserId & "'" &_
				"   AND D_M.DEL_FLG = '0' " & _
				"   AND D_M.CLAIM_METHOD <> '" & CLM_METHOD_DIRECT & "' "

		If IsObject(Param3) Then
			If Param3.Item("recept_no") <> "" Then
				l_SQL = l_SQL & " AND D_M.RECEPT_NO = '" & Param3.Item("recept_no") & "'"
			End If

			If Param3.Item("claim_situation") <> "" Then
				l_SQL = l_SQL & " AND D_M.TOTAL_SITUATION = '" & Param3.Item("claim_situation") & "'"
			End If

			If Param3.Item("claim_situation") <> "" Then
				l_SQL = l_SQL & " AND D_M.TOTAL_SITUATION = '" & Param3.Item("claim_situation") & "'"
			End If

			If Param3.Item("recept_start_year") <> "" And _
			   Param3.Item("recept_start_mon") <> "" And _
			   Param3.Item("recept_start_day") <> "" Then
				l_StrYMD = Param3.Item("recept_start_year") & _
						   Param3.Item("recept_start_mon") & _
						   Param3.Item("recept_start_day")
				'l_SQL = l_SQL & " AND RECEPT_YMD >= TO_DATE('" & l_StrYMD & "','YYYYMMDD')"
				l_SQL = l_SQL & " AND TO_CHAR(RECEPT_YMD,'YYYYMMDD') >= '" & l_StrYMD & "'"
			End If

			If Param3.Item("recept_end_year") <> "" And _
			   Param3.Item("recept_end_mon") <> "" And _
			   Param3.Item("recept_end_day") <> "" Then
				l_EndYMD = Param3.Item("recept_end_year") & _
						   Param3.Item("recept_end_mon") & _
						   Param3.Item("recept_end_day")
				'l_SQL = l_SQL & " AND RECEPT_YMD <= TO_DATE('" & l_EndYMD & "','YYYYMMDD')"
				l_SQL = l_SQL & " AND TO_CHAR(RECEPT_YMD,'YYYYMMDD') <= '" & l_EndYMD & "'"
			End If

			If Param3.Item("claim_method1") <> "" Then
				l_SQL = l_SQL & " AND D_M.CLAIM_METHOD IN ('" & Param3.Item("claim_method1") & "'"
				If Param3.Item("claim_method2") <> "" Then
					l_SQL = l_SQL & ",'" & Param3.Item("claim_method2") & "'"
				End If
				If Param3.Item("claim_method3") <> "" Then
					l_SQL = l_SQL & ",'" & Param3.Item("claim_method3") & "'"
				End If
				l_SQL = l_SQL & ")"
			Else
				If Param3.Item("claim_method2") <> "" Then
					l_SQL = l_SQL & " AND D_M.CLAIM_METHOD IN ('" & Param3.Item("claim_method2") & "'"
					If Param3.Item("claim_method3") <> "" Then
						l_SQL = l_SQL & ",'" & Param3.Item("claim_method3") & "'"
					End If
					l_SQL = l_SQL & ")"
				Else
					If Param3.Item("claim_method3") <> "" Then
						l_SQL = l_SQL & " AND D_M.CLAIM_METHOD IN ('" & Param3.Item("claim_method3") & "')"
					End If
				End If
			End If
			l_SQL = l_SQL & " ORDER BY " & GetOrderByStr(Param3.Item("sortkey"),Param3.Item("sortdesc"))
		Else
			l_SQL = l_SQL & " AND D_M.CLAIM_METHOD IN ('" &_
								CLM_METHOD_NOMAL & "','" &_
								CLM_METHOD_KANI & "')" &_
							" ORDER BY D_M.RECEPT_NO DESC,RECEPT_YMD DESC,SUB_TOTAL"
		End If

	'Response.Write l_SQL & "<br>"
		debug_t(l_SQL)
		Set SearchDataClaimOrderBase = 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 = "RECEPT_NO DESC,RECEPT_YMD DESC,SUB_TOTAL"
		ElseIf l_sortkey = SORT_KEY2 Then			'庴晅斣崋弴
			l_retStr = "RECEPT_NO" & l_seq			
		ElseIf l_sortkey = SORT_KEY3 Then			'庴晅擔帪
			l_retStr = "RECEPT_YMD" & l_seq
		ElseIf  l_sortkey = SORT_KEY4 Then			'攝憲愭
			l_retStr = "DELIVERY_ID" & l_seq
		ElseIf  l_sortkey = SORT_KEY5 Then			'嬥妟
			l_retStr = "SUB_TOTAL" & l_seq
		ElseIf  l_sortkey = SORT_KEY6 Then			'惪媮曽朄
			l_retStr = "CLAIM_METHOD" & l_seq
		ElseIf  l_sortkey = SORT_KEY7 Then			'忬嫷
			l_retStr = "TOTAL_SITUATION" & l_seq
		End If

		GetOrderByStr = l_retStr
	End Function

End Class
%>