FXApp/ws_objects/plugins_publ.pbl.src/w_extr_query.srw

$PBExportHeader$w_extr_query.srw
forward
global type w_extr_query from window
end type
type cb_4 from commandbutton within w_extr_query
end type
type cb_3 from commandbutton within w_extr_query
end type
type cb_check from commandbutton within w_extr_query
end type
type cb_1 from commandbutton within w_extr_query
end type
type st_1 from statictext within w_extr_query
end type
type cbx_case_sensitive from checkbox within w_extr_query
end type
type cb_find from commandbutton within w_extr_query
end type
type dw_criteria from u_dw within w_extr_query
end type
end forward

global type w_extr_query from window
integer x = 553
integer y = 248
integer width = 2363
integer height = 1256
boolean titlebar = true
boolean controlmenu = true
windowtype windowtype = response!
long backcolor = 134217739
toolbaralignment toolbaralignment = alignatleft!
cb_4 cb_4
cb_3 cb_3
cb_check cb_check
cb_1 cb_1
st_1 st_1
cbx_case_sensitive cbx_case_sensitive
cb_find cb_find
dw_criteria dw_criteria
end type
global w_extr_query w_extr_query

type variables
Long i_lRow
String i_aszTypes[]
DataWindow i_dwToActOn
datastore ls_ds
s_extrtriequery_tran s_dbdw
blob blob_dw

STRING RT_STR='   '
end variables

forward prototypes
private function string wf_escapechars (string a_szvalue)
private subroutine wf_resetfind ()
private function long wf_find (long a_lstartrow)
end prototypes

private function string wf_escapechars (string a_szvalue);//WF_ESCAPECHARS
Char cChar
Integer nLength, nPos

nLength = Len( a_szValue)

For nPos = nLength To 1 Step -1
	cChar = Mid( a_szValue, nPos, 1)
	Choose Case cChar
		Case "~t", "~r", "~n", '"', "'", "~~"
			a_szValue = Replace( a_szValue, nPos, 1, "~~" + cChar)
	End Choose
Next

Return Trim( a_szValue)
end function

private subroutine wf_resetfind ();i_lRow = 0
cb_find.Text = "查询(&S)"
end subroutine

private function long wf_find (long a_lstartrow);//Long lNoOfCriteria, lRow, lColumnNo
//Integer nPos
//String szFind,szFind1, szColumn, szOperator, szValue, szExpression, szJoin, szFormat, szValue1, szValue2, szValue3
//szfind1 = i_dwtoacton.describe("datawindow.table.select")
//lNoOfCriteria = dw_criteria.RowCount()
//
//For lRow = 1 To lNoOfCriteria
//	lColumnNo = dw_criteria.GetItemNumber( lRow, "column_number")
//
//	szColumn = i_dwToActOn.Describe( "#" + String( lColumnNo) + ".dbName")
//	szJoin = dw_criteria.GetItemString( lRow, "join_operator")
//	If IsNull( szJoin) Then szJoin = ""
//	szValue = wf_escapechars( dw_criteria.GetItemString( lRow, "value"))
//If IsNull( dw_criteria.GetItemNumber( 1, "column_number")) Then
//	Return 1
//End If
//
//	Choose Case Left( i_aszTypes[ lColumnNo], 5)
//	Case "char("
//		If IsNull( szValue) Then
//			szExpression = szColumn
//		Else
//			szOperator = ( dw_criteria.GetItemString( lRow, "string_operators"))
//			If Right( szOperator, 4) = "LIKE" Then
//				If Pos( szValue, "%") = 0 Then
//					szValue = "%"+ szValue + "%"
//				End If
//			End If
//			szExpression = szOperator + " '" + szValue + "' "
//
//			If cbx_case_sensitive.Checked Then
//				szExpression = szColumn + " " + szExpression
//			Else
//				szExpression = "( " + szColumn + ") " + ( szExpression)
//			End If
//		End If
//	Case "date", "time"
//		If IsNull( szValue) Then
//			szExpression = szColumn
//		Else
//			szOperator = ( dw_criteria.GetItemString( lRow, "datetime_operators"))
//			If Right( szOperator, 7) = "BETWEEN" Then
//				nPos = Pos( ( szValue), " AND ")
//				If nPos > 0 Then
//					szValue1 = Left( szValue, nPos - 1)
//					szValue2 = Mid( szValue, nPos + 5)
//				End If
//			Else					
//				nPos = 0
//			End If
//
//			If i_aszTypes[ lColumnNo] = "date" Then
//				szFormat = "yyyymmdd"
//				szValue = String( Date( szValue), szFormat)
//				szValue1 = String( Date( szValue1), szFormat)
//				szValue2 = String( Date( szValue2), szFormat)
//			ElseIf i_aszTypes[ lColumnNo] = "time" Then
//				szFormat = "hhmmss"
//				szValue = String( Time( szValue), szFormat)
//				szValue1 = String( Time( szValue1), szFormat)
//				szValue2 = String( Time( szValue2), szFormat)
//			End If
//
//			If nPos = 0 Then
//				szExpression = "Long( String( " + szColumn + ", '" + szFormat + "')) " + szOperator + szValue
//			Else
//				szExpression = "Long( String( " + szColumn + ", '" + szFormat + "')) " + szOperator + &
//  	                        " " + szValue1 + " AND " + szValue2
//			End If
//		End If
//	Case Else
//		If IsNull( szValue) Then
//			szExpression = szColumn
//		Else
//			szOperator = ( dw_criteria.GetItemString( lRow, "numeric_operators"))
//			If szOperator = "IN" Or szOperator = "NOT IN" Then
//				szExpression = szColumn + " " + szOperator + " (" + szValue + ") "
//			Else
//				szExpression = szColumn + " " + szOperator + " " + szValue + " "
//			End If
//		End If
//	End Choose
//
//	szFind = szFind + " ( " + Trim( szExpression) + ") " + szJoin
//	
//	
//Next
//   
//	szfind=szfind1+" where "+szfind
//	i_dwToActON.Modify("datawindow.table.select=~""+szfind+"~"")
//   i_dwToActon.retrieve()
//	i_dwtoacton.Modify("datawindow.table.select=~""+szfind1+"~"")
//
//
//Return lRow
return 0
end function

event open;environment   exerun_env 
GetEnvironment(exerun_env ) 
this.Move ( (PixelsToUnits(exerun_env.screenwidth, XPixelsToUnits!) - this.Width)/2,&
	(PixelsToUnits(exerun_env.screenheight, XPixelsToUnits!) - this.Height - 300 )/2)
	
//	
Integer nColumnCount, nColumnIndex
String  szColumn
Long lRow
DataWindowChild dwcColumns
string des_1,des_2


s_dbdw= Message.PowerObjectParm
//s_dbdw.ptn_ds.sharedata(dw_criteria)
s_dbdw.ptn_ds.GetFullState(blob_dw)

dw_criteria.setfullState(blob_dw)
ls_ds=create datastore
i_dwToActOn =s_dbdw.query_dw
ls_ds.dataobject=s_dbdw.query_dw.dataobject

i_lRow = 0
IF i_dwToActOn.DATAOBJECT='' THEN RETURN
nColumnCount = Integer( i_dwToActOn.Object.DataWindow.Column.Count)

dw_criteria.GetChild( "column_number", dwcColumns)

i_aszTypes[ nColumnCount] = ""

For nColumnIndex = 1 To nColumnCount
	des_1= ls_ds.Describe( "#" + String(nColumnIndex) + ".Name") 
	des_2= ls_ds.describe(des_1+ "_t.Text")
	szColumn = f_strip( ls_ds.Describe( ls_ds.Describe( "#" + String(nColumnIndex) + ".Name") + "_t.Text"))
	If szColumn <> "!" Then
		lRow = dwcColumns.InsertRow( 0)
		dwcColumns.SetItem( lRow, "column_name", szColumn)
		dwcColumns.SetItem( lRow, "column_number", nColumnIndex)
		i_aszTypes[ nColumnIndex] = ls_ds.Describe( "#" + String(nColumnIndex) + ".ColType")
	End If
Next
dw_criteria.TriggerEvent( "NewlastRow")
CB_3.ENABLED= dw_criteria.rowCOUNT()=0
//if dw_criteria.rowcount()=0 then dw_criteria.TriggerEvent( "NewRow")
end event

on w_extr_query.create
this.cb_4=create cb_4
this.cb_3=create cb_3
this.cb_check=create cb_check
this.cb_1=create cb_1
this.st_1=create st_1
this.cbx_case_sensitive=create cbx_case_sensitive
this.cb_find=create cb_find
this.dw_criteria=create dw_criteria
this.Control[]={this.cb_4,&
this.cb_3,&
this.cb_check,&
this.cb_1,&
this.st_1,&
this.cbx_case_sensitive,&
this.cb_find,&
this.dw_criteria}
end on

on w_extr_query.destroy
destroy(this.cb_4)
destroy(this.cb_3)
destroy(this.cb_check)
destroy(this.cb_1)
destroy(this.st_1)
destroy(this.cbx_case_sensitive)
destroy(this.cb_find)
destroy(this.dw_criteria)
end on

event close;CLOSEWITHRETURN(THIS,RT_STR)
end event

type cb_4 from commandbutton within w_extr_query
integer x = 2107
integer y = 16
integer width = 219
integer height = 96
integer taborder = 60
integer textsize = -9
integer weight = 400
fontcharset fontcharset = gb2312charset!
fontpitch fontpitch = variable!
string facename = "宋体"
string text = "删除行"
end type

event clicked;IF dw_criteria.GETROW()<=0 THEN 
	MESSAGEBOX('','请指定目标行!')
	RETURN
END IF
dw_criteria.deleterow(dw_criteria.GETROW())

CB_3.ENABLED= dw_criteria.rowCOUNT()=0

end event

type cb_3 from commandbutton within w_extr_query
integer x = 1723
integer y = 16
integer width = 325
integer height = 96
integer taborder = 10
integer textsize = -9
integer weight = 400
fontcharset fontcharset = gb2312charset!
fontpitch fontpitch = variable!
string facename = "宋体"
string text = "增加行"
end type

event clicked;//INT LS_ROW
//LS_ROW=dw_criteria.INSERTrow(dw_criteria.GETROW())
//dw_criteria.SETrow(LS_ROW)
//dw_criteria.ScrollToRow(LS_ROW)

dw_criteria.TriggerEvent( "NewlastRow")
THIS.ENABLED=FALSE
end event

type cb_check from commandbutton within w_extr_query
integer x = 32
integer y = 1016
integer width = 315
integer height = 96
integer taborder = 50
integer textsize = -9
integer weight = 400
fontcharset fontcharset = gb2312charset!
fontpitch fontpitch = variable!
string facename = "宋体"
string text = "语法检查"
end type

event clicked;Long lNoOfCriteria, lRow, lColumnNo,lLen
Integer nPos,LEFT_ICLNO=0,RIGHT_ICLNO=0
String szFind,szFind1, szColumn, szOperator, szValue, szExpression, szJoin, szFormat, szValue1, szValue2, szValue3
szfind1 = Lower(i_dwtoacton.describe("datawindow.table.select"))
lNoOfCriteria = dw_criteria.RowCount()
STRING LS_not_operator='',LS_left_icl='',LS_RIGHT_icl=''

For lRow = 1 To lNoOfCriteria
	If IsNull( dw_criteria.GetItemNumber( lrow, "column_number")) Then
	   MESSAGEBOX('发现错误','发现空比较列!')
		RETURN
   End If
	IF dw_criteria.OBJECT.LEFT_ICL[lRow]='(' THEN LEFT_ICLNO=LEFT_ICLNO+1
	IF dw_criteria.OBJECT.RIGHT_ICL[lRow]=')' THEN RIGHT_ICLNO=RIGHT_ICLNO+1
Next

IF RIGHT_ICLNO<>LEFT_ICLNO THEN 
	MESSAGEBOX('发现错误','左右括号不配对!')
	RETURN
END IF

For lRow = 1 To lNoOfCriteria
	lColumnNo = dw_criteria.GetItemNumber( lRow, "column_number")
   szColumn = ls_ds.Describe( "#" + String( lColumnNo) + ".dbName")
	szJoin = dw_criteria.GetItemString( lRow, "join_operator")
	If IsNull( szJoin) Then szJoin = ""
	szValue = wf_escapechars( dw_criteria.GetItemString( lRow, "value"))
	if isnull(szValue) then szValue=''
	szValue1 = string(dw_criteria.getitemdatetime(lrow,"datetime_value"),"yyyy-mm-dd")
	
	LS_not_operator=dw_criteria.GetItemString( lRow, "not_operator")
	LS_left_icl=dw_criteria.GetItemString( lRow, "left_icl")
	LS_RIGHT_icl=dw_criteria.GetItemString( lRow, "RIGHT_icl")
	
	
	If IsNull( dw_criteria.GetItemNumber( lrow, "column_number")) Then
	   exit
   End If

   STRING GG
   GG=Left( i_aszTypes[ lColumnNo], 5)
	
	Choose Case Left( i_aszTypes[ lColumnNo], 5)
   	Case "char("
	   	szOperator = ( dw_criteria.GetItemString( lRow, "string_operators"))
		   If Right( szOperator, 4) = "LIKE" Then
			   If Pos( szValue, "%") = 0 Then
			   	szValue = "%"+ szValue + "%"
			   End If
   		End If
			
	   szExpression =szColumn +" "+ szOperator + " '" + szValue + "' "
   Case "datet"
	   szOperator = ( dw_criteria.GetItemString( lRow, "datetime_operators"))
			szExpression =szColumn + szOperator + "'"+szValue1+"' "
  	Case Else
			szOperator = ( dw_criteria.GetItemString( lRow, "numeric_operators"))
			If szOperator = "IN" Or szOperator = "NOT IN" Then
				szExpression = szColumn + " " + szOperator + " (" + szValue + ") "
			Else
				szExpression = szColumn + " " + szOperator + " " + szValue + " "
			End If
	End Choose

   szFind = szFind + ' '+LS_not_operator+' '+LS_left_icl+' '+ Trim( szExpression) +' '+LS_RIGHT_icl+' ' + szJoin
	setnull(szValue)
	setnull(szValue1)
	LS_not_operator=''
	LS_left_icl=''
	LS_RIGHT_icl=''
Next
IF ISNULL(szFind) THEN
 MESSAGEBOX('错误','语法错误!')
RETURN
END IF

MESSAGEBOX('成功','通过语法检查!')


end event

type cb_1 from commandbutton within w_extr_query
integer x = 1874
integer y = 1016
integer width = 329
integer height = 96
integer taborder = 40
integer textsize = -9
integer weight = 400
fontcharset fontcharset = gb2312charset!
fontpitch fontpitch = variable!
string facename = "宋体"
string text = "取消"
boolean cancel = true
end type

event clicked;close(parent)
end event

type st_1 from statictext within w_extr_query
integer x = 55
integer y = 32
integer width = 571
integer height = 76
integer textsize = -11
integer weight = 700
fontcharset fontcharset = gb2312charset!
fontpitch fontpitch = variable!
string facename = "宋体"
long textcolor = 33554432
long backcolor = 134217739
boolean enabled = false
string text = "查询条件编辑"
boolean focusrectangle = false
end type

type cbx_case_sensitive from checkbox within w_extr_query
boolean visible = false
integer x = 366
integer y = 1024
integer width = 681
integer height = 72
integer textsize = -8
integer weight = 400
fontpitch fontpitch = variable!
fontfamily fontfamily = swiss!
string facename = "Arial"
long textcolor = 33554432
long backcolor = 134217739
string text = "Case &Sensitive Search"
end type

event clicked;wf_ResetFind()
end event

type cb_find from commandbutton within w_extr_query
event key pbm_keydown
integer x = 1486
integer y = 1016
integer width = 329
integer height = 96
integer taborder = 30
integer textsize = -9
integer weight = 400
fontcharset fontcharset = gb2312charset!
fontpitch fontpitch = variable!
string facename = "宋体"
string text = "确定"
end type

event key;If  key = KeyEnter!  Then //
THIS.POSTEVENT(CLICKED!)
End If 
Return 0
end event

event clicked;Long lNoOfCriteria, lRow, lColumnNo,lLen
Integer nPos,LEFT_ICLNO=0,RIGHT_ICLNO=0
String szFind,szFind1, szColumn, szOperator, szValue, szExpression, szJoin, szFormat, szValue1, szValue2, szValue3
//szfind1 = Lower(i_dwtoacton.describe("datawindow.table.select"))
szfind1 = Lower(s_dbdw.query_oldselect)  //新的

lNoOfCriteria = dw_criteria.RowCount()
STRING LS_not_operator='',LS_left_icl='',LS_RIGHT_icl=''

For lRow = 1 To lNoOfCriteria
	If IsNull( dw_criteria.GetItemNumber( lrow, "column_number")) Then
	   MESSAGEBOX('发现错误','发现空比较列!')
		RETURN
   End If
	IF dw_criteria.OBJECT.LEFT_ICL[lRow]='(' THEN LEFT_ICLNO=LEFT_ICLNO+1
	IF dw_criteria.OBJECT.RIGHT_ICL[lRow]=')' THEN RIGHT_ICLNO=RIGHT_ICLNO+1
Next

IF RIGHT_ICLNO<>LEFT_ICLNO THEN 
	MESSAGEBOX('发现错误','左右括号不配对!')
	RETURN
END IF

For lRow = 1 To lNoOfCriteria
	lColumnNo = dw_criteria.GetItemNumber( lRow, "column_number")
   szColumn = ls_ds.Describe( "#" + String( lColumnNo) + ".dbName")
	szJoin = dw_criteria.GetItemString( lRow, "join_operator")
	If IsNull( szJoin) Then szJoin = ""
	szValue = wf_escapechars( dw_criteria.GetItemString( lRow, "value"))
	if isnull(szValue) then szValue=''
	szValue1 = string(dw_criteria.getitemdatetime(lrow,"datetime_value"),"yyyy-mm-dd")
	
	LS_not_operator=dw_criteria.GetItemString( lRow, "not_operator")
	LS_left_icl=dw_criteria.GetItemString( lRow, "left_icl")
	LS_RIGHT_icl=dw_criteria.GetItemString( lRow, "RIGHT_icl")
	
	If IsNull( dw_criteria.GetItemNumber( lrow, "column_number")) Then
	   exit
   End If

   STRING GG
   GG=Left( i_aszTypes[ lColumnNo], 5)
	Choose Case Left( i_aszTypes[ lColumnNo], 5)
   	Case "char("
	   	szOperator = ( dw_criteria.GetItemString( lRow, "string_operators"))
		   If Right( szOperator, 4) = "LIKE" Then
			   If Pos( szValue, "%") = 0 Then
			   	szValue = "%"+ szValue + "%"
			   End If
   		End If
	   szExpression =szColumn +" "+ szOperator + " '" + szValue + "' "
	   Case "datet"
		   szOperator = ( dw_criteria.GetItemString( lRow, "datetime_operators"))
			szExpression =szColumn + szOperator + "'"+szValue1+"' "
   	Case Else
			szOperator = ( dw_criteria.GetItemString( lRow, "numeric_operators"))
			If szOperator = "IN" Or szOperator = "NOT IN" Then
				szExpression = szColumn + " " + szOperator + " (" + szValue + ") "
			Else
				szExpression = szColumn + " " + szOperator + " " + szValue + " "
			End If
	End Choose
   szFind = szFind + ' '+LS_not_operator+' '+LS_left_icl+' '+ Trim( szExpression) +' '+LS_RIGHT_icl+' ' + szJoin
	setnull(szValue)
	setnull(szValue1)
	LS_not_operator=''
	LS_left_icl=''
	LS_RIGHT_icl=''
Next

   if szfind='' or isnull(szfind) then
		szfind = szfind1
	else
		if Pos(szFind1," where ") <> 0 then
		   szfind=szfind1+" AND ("+szfind+')'
   	else
	   	szfind = szfind1+" where ("+szfind+')'
      end if
	end if

SZFIND=trim(SZFIND)
if UPPER(right(SZFIND,3))=' OR' THEN 
	SZFIND=LEFT(SZFIND,LEN(SZFIND) - 3)
ELSEIF UPPER(right(SZFIND,4))=' OR)' THEN 
	SZFIND=LEFT(SZFIND,LEN(SZFIND) - 4)	+')'
ELSEIF UPPER(right(SZFIND,4))=' AND' THEN 
	SZFIND=LEFT(SZFIND,LEN(SZFIND) - 4)	
ELSEIF UPPER(right(SZFIND,5))=' AND)' THEN 
	SZFIND=LEFT(SZFIND,LEN(SZFIND) - 5)	+')'
END IF

dw_criteria.GetfullState(blob_dw)
s_dbdw.ptn_ds.setFullState(blob_dw)

//messagebox('',SZFIND)
//Closewithreturn(parent,szfind)
rt_str=szfind
close(parent)
end event

type dw_criteria from u_dw within w_extr_query
event key pbm_dwnkey
integer x = 27
integer y = 128
integer width = 2295
integer height = 872
integer taborder = 20
string dataobject = "d_extr_find"
boolean vscrollbar = true
end type

event key;If  key = KeyEnter!  Then //
keybd_event ( 9, 0, 0 , 0 ) // 按下tab 
keybd_event ( 9, 0, 2, 0 ) // 释放tab 
Return 1
End If 
end event

event itemchanged;Long lRow, lTotalRows, lIndex, lReturn = 0
String szValue
string szcolumn,szvalues,szstyle,szdisp,szdata,szdacol,szdiscol
long lcolumnno,l_count,l_curr
datawindowchild dwc_query

lRow = this.GetRow()
lTotalRows = this.RowCount()
szValue = this.GetText()
this.accepttext()
Choose Case this.GetcolumnName()
	Case "column_number" 
		lIndex = Long( szValue)
		If lIndex > 0 Then
			If i_aszTypes[ lIndex] <> "" Then
				this.modify("value.values="+"'"+"~t"+"'")
				this.SetItem( lRow, "column_datatype", i_aszTypes[ lIndex])
				lColumnNo = dw_criteria.GetItemNumber( lRow, "column_number")
            szColumn = i_dwToActOn.Describe( "#" + String( lColumnNo) + ".Name") 
				szvalues = i_dwToActOn.Describe(szColumn+".values")
				if szvalues = "?" then
				else
				   this.modify("value.values="+"'"+szvalues+"'")
				end if
				if i_dwToActOn.Describe(szColumn+".Edit.Style") = "dddw" then
   				szdacol = i_dwToActon.Describe(szColumn+".dddw.Datacolumn")
	   			szdiscol = i_dwToActOn.Describe(szColumn+".dddw.DisplayColumn")
					i_dwToActOn.GetChild(szColumn,dwc_query)
					l_count = dwc_query.rowcount()
				   szvalues = ''
				   for l_curr = 1 to l_count
					   szdata = dwc_query.getitemstring(l_curr,szdacol)
					   szdisp = dwc_query.getitemstring(l_curr,szdiscol)
					   szvalues = szvalues + szdisp + "~t" +szdata + "/"
					next
					this.modify("value.values="+"'"+szvalues+"'")
				end if
			End If
		End If
	Case "value"
		If Trim( szValue) <> "" Then
			Choose Case Lower( this.GetItemString( lRow, "column_datatype"))
				Case "date"
					If Not IsDate( szValue) Then
						lReturn = 1
					End If
				Case "time"
					If Not IsTime( szValue) Then
						lReturn = 1
					End If
				Case "long", "ulong", "real"
					If Not IsNumber( szValue) Then
						lReturn = 1
					End If
			End Choose
		End If
	Case "join_operator"
		If lRow = lTotalRows And Not IsNull( this.GetItemNumber( lRow, "column_number")) Then
			this.TriggerEvent( "NewRow")
		ElseIf Trim( szValue) = "" And lTotalRows > 1 And lRow <> lTotalRows Then
			dw_criteria.SetRow( lRow + 1)
			dw_criteria.TriggerEvent( "DeleteRow")
		End If
End Choose

return lReturn
end event

event newfirstrow;call super::newfirstrow;if dw_criteria.getrow()>0 then
//dw_criteria.object.rptid[dw_criteria.getrow()]=s_dbdw.rptid
CB_3.ENABLED= dw_criteria.rowCOUNT()=0
end if
end event

event newlastrow;call super::newlastrow;if dw_criteria.getrow()>0 then
//dw_criteria.object.rptid[dw_criteria.getrow()]=s_dbdw.rptid
//dw_criteria.object.modelcode[dw_criteria.getrow()]=s_dbdw.modelcode
CB_3.ENABLED= dw_criteria.rowCOUNT()=0
end if
end event

event newrow;call super::newrow;if dw_criteria.getrow()>0 then
//dw_criteria.object.rptid[dw_criteria.getrow()]=s_dbdw.rptid
CB_3.ENABLED= dw_criteria.rowCOUNT()=0
end if
end event