Q5/ws_objects/longjoe_report_uo.pbl.src/f_mysql.srf

$PBExportHeader$f_mysql.srf
$PBExportComments$获取创建动态dw的语法--函数
global type f_mysql from function_object
end type

forward prototypes
global function string f_mysql (datawindow dw_from, datawindow dw_field, datawindow dw_object, transaction it_f)
end prototypes

global function string f_mysql (datawindow dw_from, datawindow dw_field, datawindow dw_object, transaction it_f);//获取数据源的select语句及创建动态dw的语法,w_report_edit和w_reprot_preview调用
string ls_from,ls_where,ls_sort,ls_select,ls_sql,ls_error,ls_table,ls_tables,ls_sort2,ls_sort0,ls_sql0,ls_newpage
int li,li_rows,li_pos,li_rows_field
long ll_row1,ll_row2,ll_row3,ll_row4,ll_detail,ll_detail_befor,ll_footer,ll_footer_befor
string ls_temp,ls_sx_detail,ls_sx_footer,ls_obj,ls_group
boolean lb
lb=it_f.AutoCommit//取出原来属性
if dw_from.rowcount()=0 then//如果没有选数据源,则自动添加一个简单的数据源
	dw_from.insertrow(0)
	dw_from.setitem(1,1,'is_report_id')
	dw_from.setitem(1,2,'report1')
	dw_field.insertrow(0)
	dw_field.setitem(1,1,'is_report_id')
	dw_field.setitem(1,2,'report1')
	dw_field.setitem(1,3,'report_id')
	dw_field.setitem(1,4,'报表编号')
	dw_field.setitem(1,5,'char(54)')
	dw_field.setitem(1,6,1)
	dw_field.setitem(1,7,1)
	dw_field.setitem(1,8,'report1.report_id')
	dw_field.setitem(1,9,1)
end if
ls_from=dw_from.getitemstring(1,'from_oexp')
ls_where=dw_from.getitemstring(1,'where_oexp')
ls_sort=dw_from.getitemstring(1,'sort_oexp')
li_rows_field=dw_field.rowcount()
for li=1 to li_rows_field
	if ls_select>'' then ls_select=ls_select+','
	ls_select=ls_select+dw_field.getitemstring(li,'column_select')
next
ll_row1=0
li_rows=dw_object.rowcount()
for li=1 to li_rows//此循环处理分组,索引,分组高
	ll_row1=dw_object.find("left(oname,9)='band_g_h_'",ll_row1+1,li_rows)
	if ll_row1=0 then exit
	ll_detail=dw_object.getitemnumber(ll_row1,'oy1')//分组头
	ls_sx_detail=dw_object.getitemstring(ll_row1,'oformat')
	ls_newpage=dw_object.getitemstring(ll_row1,'oborder')//新组换页打印
	ls_temp=dw_object.getitemstring(ll_row1,'expr_english')//分组关键字
	if ls_temp>'' then//以下代码可以兼容字段和表名有'_'情况
		ls_sort2=f_replace(ls_temp,"'","")
		if pos(ls_from,',')>0 then//两个表以上ls_from中间有逗号
			for li=1 to li_rows_field
				ls_sort2=f_replace(ls_sort2,dw_field.getitemstring(li,'column_english'),dw_field.getitemstring(li,'column_select'))
			next
		end if
	end if
	ls_obj=dw_object.getitemstring(ll_row1,'oname')
	ls_obj='band_g_b_'+mid(ls_obj,10,1)
	ll_row2=dw_object.find("oformat='"+string(integer(ls_sx_detail) - 1)+"'",0,li_rows)
	if ll_row2>0 then // 分组头前一带区
		ll_detail_befor=dw_object.getitemnumber(ll_row2,'oy1')
	else
		ll_detail_befor=ll_detail - 14
	end if
	ll_row3=dw_object.find("oname='"+ls_obj+"'",ll_row1+1,li_rows)
	if ll_row3>0 then
		ll_footer=dw_object.getitemnumber(ll_row3,'oy1')//分组尾
		ls_sx_footer=dw_object.getitemstring(ll_row3,'oformat')
		ll_row4=dw_object.find("oformat='"+string(integer(ls_sx_footer) - 1)+"'",ll_row1+1,li_rows)
		if ll_row4>0 then // 分组尾前一带区
			ll_footer_befor=dw_object.getitemnumber(ll_row4,'oy1')
		else
			ll_footer_befor=ll_footer -14
		end if
	else
		ll_footer=0
		ll_footer_befor= -14
	end if
	if ls_newpage='1' then
		ls_newpage=' newpage=yes'
	else
		ls_newpage=''
	end if
	ls_group=ls_group+"group(level="+right(ls_obj,1)+" header.height="+string(ll_detail - ll_detail_befor - 14)+" trailer.height="+string(ll_footer - ll_footer_befor - 14)+" by=("+ls_temp+")"+ls_newpage+")"
next
ls_sql='select '+ls_select+' from '+ls_from
if ls_where>'' then ls_sql=ls_sql+' where '+ls_where

if ls_sort2>'' and pos(ls_sort,ls_sort2)=0 then//创建分组自动加的索引
	if ls_sort>'' then
		ls_sort0=ls_sort+','+ls_sort2
	else
		ls_sort0=ls_sort2
	end if
else
	ls_sort0=ls_sort
end if
ls_sql0=ls_sql
if ls_sort0>'' then ls_sql0=ls_sql0+' order by '+ls_sort0
it_f.AutoCommit=true
ls_sql0=it_f.SyntaxFromSQL(ls_sql0,"style(type=grid)",ls_error)
it_f.AutoCommit=lb

if isnull(ls_sql0) or ls_sql0='' then//分组自动排序ls_sort2=getrow()时会出错,去掉此排序后重新创建
	ls_sort0=ls_sort
	ls_sql0=ls_sql
	if ls_sort0>'' then ls_sql0=ls_sql0+' order by '+ls_sort0
	it_f.AutoCommit=true
	ls_sql0=it_f.SyntaxFromSQL(ls_sql0,"style(type=grid)",ls_error)
	it_f.AutoCommit=lb
end if

if isnull(ls_sql0) then ls_sql0=''
return ls_sql0+ls_group
end function