大象数据库SQL存储过程（函数）

时间：2022-09-11 09:25:24
-- Function: antifraudjudge(character varying)

-- DROP FUNCTION antifraudjudge(character varying);

CREATE OR REPLACE FUNCTION antifraudjudge(IN loancode character varying, OUT outvalue text)

  RETURNS text AS

$BODY$

	declare stmt record;                                            -- 声明被执行SQL变量

	        _flag varchar (50);                                     -- 用于区分被插入表的变更

	        exet record;                                            -- 声明被执行SQL返回结果变更

	        message text;                                           -- 处理结果信息

                sqltxt text;                                            -- sql

                idstr varchar(36);                                      -- 生成表中的ID

                opuser varchar (50);                                    -- 操作员

	begin

		for stmt in select sql_auto, rules_tables,rules_type,id,rules_content,rules_relation from jk.t_jk_rules_config

		loop

		  _flag := stmt.rules_type;

                  sqltxt:=replace(stmt.sql_auto,'<','<');

                  sqltxt:=replace(sqltxt,'>','>');

                  sqltxt:=replace(sqltxt,'{loan_code}',''||loancode||'');

                  execute sqltxt into exet;

                  idstr:=uuid_generate_v4();

                  idstr:=replace(idstr,'-','');

                  message := idstr;

                  opuser:='sql';

		  if exet.loan_code IS NOT NULL then

                          message := '处理中';

			  if _flag = '1' then         -- 反欺诈_销售人员信息匹配

			    insert into jk.t_jk_antifraud_offend_sales(id, r_offend_id, loan_code, offend_sales_name, offend_tel, offend_name, work_flag, dict_job_grade, create_by, create_time, modify_by, modify_time)

                            values(idstr,stmt.id,exet.loan_code,exet.offend_sales_name, exet.offend_tel, exet.offend_name, exet.work_flag, exet.dict_job_grade,opuser,now(),opuser,now());

			  elseif _flag = '2' then     -- 反欺诈_黑名单数据库信息匹配

			    insert into jk.t_jk_antifraud_blacklist(id, r_offend_id, loan_code, dict_mark_type, dict_blacklist_type, blacklist_msg, blacklist_risk_msg, blacklist_relation, create_by, create_time, modify_by, modify_time)

                            values(idstr,stmt.id,exet.loan_code,exet.dict_mark_type, exet.dict_blacklist_type, exet.blacklist_msg, exet.blacklist_risk_msg, exet.blacklist_relation,opuser,now(),opuser,now());

			  elseif _flag = '3' then     -- 反欺诈_查重内容

			    insert into jk.t_jk_antifraud_repeat(ID,R_OFFEND_ID,LOAN_CODE,CUSTOMER_NAME,REPEAT_INTO_TIME,REPEAT_RESULT_MSG,REPEAT_RELATION,CREATE_BY,CREATE_TIME,MODIFY_BY,MODIFY_TIME)

                            values(idstr,stmt.id,exet.loan_code,exet.customer_name,exet.customer_into_time,stmt.rules_content,stmt.rules_relation,opuser,now(),opuser,now());

			  elseif _flag = '4' then     -- 反欺诈_案件信息表

			    insert into jk.t_jk_antifraud_case(id, r_judge_id, case_code, loan_code, case_handle_day, case_handle_by, loan_customer_name, dict_antifraud_type, case_risk_msg, create_by, create_time, modify_by, modify_time)

                            values(idstr,stmt.id,exet.loan_code,exet.case_handle_day, exet.case_handle_by, exet.loan_customer_name, exet.dict_antifraud_type, exet.case_risk_msg,opuser,now(),opuser,now());

			  end if;

		  end if;

		end loop;

		--message := '处理成功';

                OutValue:=message;

	end;

    $BODY$

  LANGUAGE plsrsql VOLATILE

  COST 100;

ALTER FUNCTION antifraudjudge(character varying)

  OWNER TO jk;

　　

jsp文件，主要是javascript，setsql

<%@ page contentType="text/html;charset=UTF-8" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<%@ include file="/WEB-INF/views/include/taglib.jsp"%>

<meta name="renderer" content="webkit">

<meta http-equiv="Expires" content="0">

<meta http-equiv="Cache-Control" content="no-cache"><meta http-equiv="Cache-Control" content="no-store">

<link href="${ctxStatic}/bootstrap/3.3.5/css_${not empty cookie.theme.value ? cookie.theme.value : 'cerulean'}/bootstrap.min.css" type="text/css" rel="stylesheet" />

<link href="${ctxStatic}/bootstrap/3.3.5/css_${not empty cookie.theme.value ? cookie.theme.value : 'cerulean'}/font-awesome.min.css" type="text/css" rel="stylesheet" />

<link href="${ctxStatic}/bootstrap/3.3.5/css_${not empty cookie.theme.value ? cookie.theme.value : 'cerulean'}/ace.min.css" type="text/css" rel="stylesheet" />

<script src="${ctxStatic}/jquery/jquery-1.10.2.min.js" type="text/javascript"></script>

<link href="${ctxStatic}/jquery-validation/1.11.0/jquery.validate.min.css" type="text/css" rel="stylesheet" />

<script src="${ctxStatic}/jquery-validation/1.11.0/jquery.validate.min.js" type="text/javascript"></script>

<script type="text/javascript">var ctx = '${ctx}', ctxStatic='${ctxStatic}';</script>

<script type="text/javascript">var basePath='${ctx}';</script>

<title>新增--反欺诈查重规则设置</title>

<script type="text/javascript">

//获取查重内容列表

function getnext(tablename,nextindex,buttontype) {

	if(tablename!=""){

		$.ajax({

            type: "post",

            url: "${ctx}/management/rulesconfig/findfields",

            data: {tablename:tablename,dbname:'jk'},

            success: function(data){

            	if(buttontype==0 || buttontype==1){

            	$('#fieldSelect'+nextindex).empty();

            	}

            	else if(buttontype==2){

            	$('#wherefieldSelect'+nextindex).empty();

            	}

            	$.each(data, function (i, item) {

                    //alert(item.id + ","  + item.name);

                    var v=item.fieldName;

            		var c=item.fieldComment;

            		if(buttontype==0){

            			$('#fieldSelect'+nextindex).append('<input type="radio" name="fieldcheck'+nextindex+'" checked="checked" value="'+v+'" data-value="'+c+'"/>'+c+'');

            		}

            		else if(buttontype==1){

            			$('#fieldSelect'+nextindex).append('<input type="checkbox" name="fieldcheck'+nextindex+'" value="'+v+'" data-value="'+c+'" />'+c+'');

            		}

            		else if(buttontype==2){

            			$('#wherefieldSelect'+nextindex).append('<option value="'+v+'" >'+c+'</option>');

            		}

                });

            	if(buttontype==2){

            		//设置jjdata的值为当前下拉框选择数据

            		setjjdata($('#wherefieldSelect'+nextindex),nextindex);

        		}

            }

         });

	}

}

$(function($) {

	   //初始化隐藏table

	   $('#dataTable').hide();

	   $("#toolsTable").hide();

	   $('#whereTable').hide();

	   $('#fieldSelect').hide();//暂不显示

	   $('#constraintSelect').hide();//暂不显示

	   $('#sqltable').hide();//暂不显示

	   $('#numcheckspan').hide();//设置次数的时候再显示

	   $('#numcheck1').hide();//设置次数的时候再显示

	  // 你可以在这里继续使用$作为别名...

	  //sql字段数据设置

	  $('#rulesType').change(function(){

		  var p=$(this).children('option:selected').val();

		  var fields='T_JK_LOAN_INFO.LOAN_CODE,T_JK_LOAN_CUSTOMER.CUSTOMER_NAME,T_JK_LOAN_INFO.CUSTOMER_INTO_TIME,NULL,NULL';

		  if(p=='3'){

			  //借款编码、客户姓名,进件时间,----信息内容,与本次借款人关系

			  fields='T_JK_LOAN_INFO.LOAN_CODE,T_JK_LOAN_CUSTOMER.CUSTOMER_NAME,T_JK_LOAN_INFO.CUSTOMER_INTO_TIME,NULL,NULL';

		  }

		  $('#sqlFields').val(fields);

		  });

	  $('#tableSelect').change(function(){

		  var p1=$(this).children('option:selected').val();//这就是selected的值

		  getnext(p1,'',0);

		  })

		  //弹窗里的数据

		  $('#tableSelect1').change(function(){

		  var p1=$(this).children('option:selected').val();

		  var idtxt=$(this).attr('id').replace('tableSelect','');

		  getnext(p1,idtxt,1);

		 // getnext(p1,idtxt,2);

		  })

	 $('#wherefieldSelect1').change(function(){

		 debugger;

		 var id=$(this).attr('id').replace('wherefieldSelect','');

    			  setjjdata($(this),id);

    			  });

	 //打开数据源窗口

	 $('#setdataBtn').click(function(){

		 $("#dataTable").toggle();

		 $("#toolsTable").toggle();

		 $("#whereTable").toggle();

		 //showDataFrm();

	 })

	 //打开数据源条件窗口

	 $('#setwhereBtn1').click(function(){

		 $("#whereTable").show();

		 //先绑定数据

		 var idtxt=$(this).attr('id').replace('setwhereBtn','');

		 if(idtxt!='1'){

		   AddRow('whereTable');

		 }

		 var p1=$('#tableSelect'+idtxt).children('option:selected').val();

		 //找到where下拉框

		 var vNum=$('#whereTable tr').filter('.CaseRow').size();//表格有多少个数据行

		 getnext(p1,vNum,2);

         //设置表名

         $('#tableHid'+vNum).val(p1);

	 })

	 //新增where行

	 $('#newwhereBtn').click(function(){

		AddRow('whereTable');

	 })

	 //已有删除按钮初始化绑定删除事件

	    $("#whereTable .del").click(deltr);

	    $("#dataTable .del").click(deltr);

	 //新增表对象

	 $('#newtableBtn').click(function(){

		AddTableRow('dataTable');

	 })

	 //新增设置次数条件

	  $('#addrelationBtn').click(function(){

		AddRow('whereTable');

		//设置数据

		var vNum=$('#whereTable tr').filter('.CaseRow').size();//表格有多少个数据行  

		$('#wherefieldSelect'+vNum).empty();

		$('#wherefieldSelect'+vNum).append('<option value="" >次数</option>');

		//$('#whererelationSelect'+vNum).hide();

		$('#whereconstraintSelect'+vNum).attr('value','6');

		var txt='<input type="text" id="jjdata_text'+vNum+'" name="jjdata_text'+vNum+'" class="jjdatacount_width"  value="2">';

		 $("#jjdata" + vNum).html(txt);

		 //显示numcheck

		 $('#numcheckspan').show();

		 $("[name = numcheck]:checkbox").show();

		 $('#numcheck'+vNum).hide();

	 })

	 //新增设置天数条件

	  $('#adddayBtn').click(function(){

		AddRow('whereTable');

		//设置数据

		var vNum=$('#whereTable tr').filter('.CaseRow').size();//表格有多少个数据行  

		$('#wherefieldSelect'+vNum).empty();

		$('#wherefieldSelect'+vNum).append('<option value="" >天数</option>');

		//$('#whererelationSelect'+vNum).hide();

		$('#whereconstraintSelect'+vNum).attr('value','7');

		var txt='<input type="text" id="jjdata_text'+vNum+'" name="jjdata_text'+vNum+'" class="jjdatacount_width"  value="180">';

		 $("#jjdata" + vNum).html(txt);

	 })

	  //新增设置排序条件

	  $('#addorderBtn').click(function(){

		AddRow('whereTable');

		//设置数据

		var vNum=$('#whereTable tr').filter('.CaseRow').size();//表格有多少个数据行  

		$('#wherefieldSelect'+vNum).empty();

		$('#wherefieldSelect'+vNum).append('<option value="" >前一次进件</option>');

		//$('#whererelationSelect'+vNum).hide();

		$('#whereconstraintSelect'+vNum).hide();

		$('#whererelationSelect'+vNum).hide();

		 $("#jjdata" + vNum).hide();

	 })

	 //保存数据

	 $('#savesqlBtn').click(function(){

		     var sql=getsql();

		     $('#sqltable').show();

		     $('#sqlAuto').val(sql);

		     saveSqdata(this);

		 });

	 //回显数据

	 $('#testsqlBtn').click(function(){

		 $.ajax({

				type : "POST",

				data :{id:"439a3c6811984fd0a04296ff2143ca4f"},

				url : ctx+"/management/rulesconfig/asyncGetSqdata",

				success : function(data) {

					alert(data);

					 $('#sqltable').show();

					 $('#sqlAuto').empty();

					 $('#sqlAuto').val(data);

				}

			});

		 });

	 //生成sql

	 $('#setsqlBtn').click(function(){

		 $('#sqltable').show();

		 var sql= getsql();

		 $('#sqlAuto').val(sql);

	 })

	});

//生成SQL

function getsql(){

	//表名

	 var tables=new Array()

	 var tableid='dataTable';

	  var vNum=$('#'+tableid+' tr').filter('.CaseRow').size()+1;

	  $('#'+tableid+' tr').each(function() {

		  var sNum=$(this).find($('select')).size();

		  if(sNum>0){

			  var tableSelect=$(this).find($('select'));

			  if(tableSelect.attr("id").indexOf('tableSelect')!=-1){

		    	 var tableName=tableSelect.val();

		    	 if(!contains(tables, tableName))

		    	 {

		    		 tables.push(tableName);

		    	 }

			  }

		  }

	  });

	 //字段

	 var fields=new Array();

	 var tableid='dataTable';

	 $('#'+tableid+' tr').each(function() {

		  var sNum=$(this).find($('div')).size();

		  if(sNum>0){

			  var fieldSelect=$(this).find($('div'));

			  if(fieldSelect.attr("id").indexOf('fieldSelect')!=-1){

				 var id=fieldSelect.attr('id').replace('fieldSelect','');

		    	 var tableName=$('#tableSelect'+id).val();

		    	 /* $("input[name='fieldcheck"+id+"']:checked").each(function(){

		    		 var fieldName=tableName+'.'+$(this).val();

	    			 fields.push(fieldName);

		    	 }); */

		    	 //保存所有字段待查，不以选中为准， fields数组用于待查jjdata，sqlfields用于sql

		    	 $("input[name='fieldcheck"+id+"']").each(function(){

		    		 var fieldName=tableName+'.'+$(this).val();

	    			 fields.push(fieldName);

		    	 });

			  }

		  }

	  });

	 //条件

	var where_fields=new Array();

	var where_fields_id=new Array();

	var where_numcheck_id=new Array();

	var where_constraints=new Array();

	var where_data=new Array();

	var where_relation=new Array();

	var whereList=new Array();

	var whereListNum=new Array();//次数where

	var tableListNum=new Array();//次数表名

	var where_id=new Array();

	var strwhere='';

	 var tableid='whereTable';

	  var vNum=$('#'+tableid+' tr').filter('.CaseRow').size()+1;

	  $('#'+tableid+' tr').each(function() {

		  var sNum=$(this).find($('select')).size();

		  if(sNum>2){

			  var fieldName='';

			  var tableSelect=$(this).find($('select'));

			  if(tableSelect.attr("id").indexOf('wherefieldSelect')!=-1){

				  var id=tableSelect.attr('id').replace('wherefieldSelect','');

				  //存入ID（Rowid）目的是串起每个元素

				  where_id.push(id);

				  if(tableSelect.val()!=''){//有时候wherefieldSelect为空，比如设置前次进件、次数等

		    	     fieldName= $('#tableHid'+id).val()+'.'+tableSelect.val();

		    	     where_fields_id.push(id+'-'+fieldName);

		    	     if(!contains(where_fields, fieldName))

		    	     {

		    		     where_fields.push(fieldName);

		    	     }

				  }

			  }

			  //约束条件

			   var constraintName='';

			   var tjSelect=$('#whereconstraintSelect'+id);

				 var constraintName=tjSelect.val();

			//入库需要，前面加id，目的是限定

			 var constraintName1=id+'-'+constraintName;

			     if(!contains(where_constraints, constraintName1))

		    	 {

		    		 where_constraints.push(constraintName1);

		    	 }

		    	 if(constraintName=='1')

		    	 {

		    		 constraintName='like';

		    	 }

		    	 else if(constraintName=='2')

		    	 {

		    		 constraintName='=';

		    	 }

		    	 else if(constraintName=='3')

		    	 {

		    		 constraintName='!=';

		    	 }

		    	 else if(constraintName=='4')

		    	 {

		    		 constraintName='>';

		    	 }

		    	 else if(constraintName=='5')

		    	 {

		    		 constraintName='<';

		    	 }

		    	 else if(constraintName=='6')

		    	 {

		    		 constraintName='>=';

		    	 }

		    	 else if(constraintName=='7')

		    	 {

		    		 constraintName='<=';

		    	 }

		     //连接语句and or

		      var whererelationName=$('#whererelationSelect'+id).val();

			  //进件数据

			  if($(this).find($('span')).attr('id').indexOf('jjdata')!=-1

					  && constraintName!=''

						  && !$('#numcheck'+id).prop("checked"))

			  {

				     where_numcheck_id.push(id+'-0'); //次数设置数据,没有选中置0

				     var jjdata=$('#jjdata_select'+id);

			    	 var jjdataName=jjdata.val();

			    	 debugger;

			    	 if(undefined!=jjdataName && jjdataName.indexOf('CUREET.')!=-1)

			    	 {

			    		 //生成一般where语句

			    		strwhere=fieldName+' '+constraintName+' '+jjdataName+' '+whererelationName;

			    		var jjdataName_id=id+'-'+jjdataName;

			    		if(!contains(where_data, jjdataName_id))

				    	{

			    			where_data.push(jjdataName_id);

				        }

			    		var whererelationName1=id+'-'+whererelationName;

			    		if(!contains(where_relation, whererelationName1))

				    	{

			    			where_relation.push(whererelationName1);

				        }

			    		if(!contains(whereList, strwhere))

				    	{

				    		 whereList.push(strwhere);

				        }

			         }

			  }

			  else  if($(this).find($('span')).attr('id').indexOf('jjdata')!=-1

					  && constraintName!=''

						  && $('#numcheck'+id).prop("checked"))

			  {

				  where_numcheck_id.push(id+'-1'); //次数设置数据,选中置1

				     var jjdata=$('#jjdata_select'+id);

			    	 var jjdataName=jjdata.val();

			    	 if(undefined!=jjdataName && jjdataName.indexOf('CUREET.')!=-1)

			    	 {

			    	    var strTableNum= $('#tableHid'+id).val();

			    		 //生成一般where语句

			    		strwhere=fieldName+' '+constraintName+' '+jjdataName+' '+whererelationName;

			    		var jjdataName_id=id+'-'+jjdataName;

			    		if(!contains(where_data, jjdataName_id))

				    	{

			    			where_data.push(jjdataName_id);

				        }

			    		var whererelationName1=id+'-'+whererelationName;

			    		if(!contains(where_relation, whererelationName1))

				    	{

			    			where_relation.push(whererelationName1);

				        }

			    		if(!contains(whereListNum, strwhere))

				    	{

			    			whereListNum.push(strwhere);

				        }

			    		if(!contains(tableListNum, strTableNum)){

			    			tableListNum.push(strTableNum);

			    		}

			         }

			  }

			  //次数

			  if($('#wherefieldSelect'+id).find("option:selected").text()=='次数'){

				  var cnum=$('#jjdata_text'+id).val();

				  //次数子句的生成

					 var strWheres='';

					 for (var i = 0; i < whereListNum.length; i++) {

						 strWheres+=' '+whereListNum[i];

					    }

					 if(strWheres.substring(0,1)==','){

						 strWheres=strWheres.substr(1);

					 }

					 //表名tableListNum

					 var strTables='';

					 if(tableListNum.length==1)

					 {

						 strTables=tableListNum[0];

					 }

					 else

					 {

						 for (var i = 0; i < tableListNum.length; i++) {

							 var tbName=tableListNum[i];

							 if(i==0){

								 strTables=' '+ tbName+' ';

							 }

							 if(i+1<tableListNum.length )

							 {

								 var tbNext=tableListNum[i+1];

								 strTables+=' inner join '+tbNext+' on '+tbName+'.LOAN_CODE='+tbNext+'.LOAN_CODE'

							 }

						    }

					 }

					 if(strTables.substring(0,1)==','){

						 strTables=strTables.substr(1);

					 }

				  strwhere="(select count(1) from "+strTables+" where "+strWheres+" )"+constraintName+cnum+' '+whererelationName;

				  if(!contains(whereList, strwhere))

			    	{

			    		 whereList.push(strwhere);

			        }

			  }

			  //天数

			  if($('#wherefieldSelect'+id).find("option:selected").text()=='天数'){

				  var cnum=$('#jjdata_text'+id).val();

				  strwhere='sysdate-T_JK_LOAN_INFO.CUSTOMER_INTO_TIME'+constraintName+cnum+' '+whererelationName;

				  if(!contains(whereList, strwhere))

			    	{

			    		 whereList.push(strwhere);

			        }

			  }

			  //排序

			  if($('#wherefieldSelect'+id).find("option:selected").text()=='前一次进件'){

				  strwhere='  T_JK_LOAN_INFO.CUSTOMER_INTO_TIME=(SELECT MAX(T_JK_LOAN_INFO.CUSTOMER_INTO_TIME) FROM T_JK_LOAN_INFO WHERE T_JK_LOAN_INFO.LOAN_CODE=T_JK_LOAN_CUSTOMER.LOAN_CODE )';

				  if(!contains(whereList, strwhere))

			    	{

			    		 whereList.push(strwhere);

			        }

			  }

			  //

		  }//end num>2

	  });

	 //次数

	 //排序

	 //语句

	 var strField='';

	 for (var i = 0; i < fields.length; i++) {

	       strField+=','+fields[i];

	    }

	 if(strField.substring(0,1)==','){

		 strField=strField.substr(1);

	 }

	 var strTable='';

	 for (var i = 0; i < tables.length; i++) {

		 var tbName=tables[i];

		 if(i==0 && tables[i]!='T_JK_LOAN_INFO')

		 {

			 if(tbName.indexOf('T_JK_CONTACT_')!=-1){

			     var relationType=tbName.substring(tbName.length-1,tbName.length);

				 var tbNameEx='T_JK_CONTACT as T_JK_CONTACT_'+relationType;

				 strrelation=' and T_JK_CONTACT_'+relationType+'.CONTACT_RELATION=\''+relationType+'\' '

				 strTable+= ' '+tbNameEx+' inner join T_JK_LOAN_INFO on '+tbName+'.LOAN_CODE= T_JK_LOAN_INFO.LOAN_CODE '+strrelation;

			 }

			 else{

				 strTable+= ' '+tbName+' inner join T_JK_LOAN_INFO on '+tbName+'.LOAN_CODE= T_JK_LOAN_INFO.LOAN_CODE ';

			 }

		 }

		 if(i+1<tables.length && tables[i+1]!='T_JK_LOAN_INFO')

		 {

			 var tbNext=tables[i+1];

			 var strrelation='';

			 //处理联系人信息别名

			 if(tbNext.indexOf('T_JK_CONTACT_')!=-1){

			     var relationType=tbNext.substring(tbNext.length-1,tbNext.length);

				 var tbNextEx='T_JK_CONTACT as T_JK_CONTACT_'+relationType;

				 strrelation=' and T_JK_CONTACT_'+relationType+'.CONTACT_RELATION=\''+relationType+'\' '

				 strTable+= ' inner join '+tbNextEx+' on '+tbName+'.LOAN_CODE='+tbNext+'.LOAN_CODE'+' '+strrelation;

			 }

			 else{

				 strTable+= ' inner join '+tbNext+' on '+tbName+'.LOAN_CODE='+tbNext+'.LOAN_CODE';

			 }

		 }

	    }

	 //去掉当前进件数据

	 var loan_code='{loan_code}';

	 var cTable=strTable; //没有去除当前进件的表集合

	 strTable+=' and T_JK_LOAN_INFO.LOAN_CODE!=\''+loan_code+'\' ';

	 //筛选当前进件数据

	 var strWhereFields='';

	 for (var i = 0; i < where_fields.length; i++) {

		 strWhereFields+=','+where_fields[i];

	    }

	 if(strWhereFields.substring(0,1)==','){

		 strWhereFields=strWhereFields.substr(1);

	 }

	 //var csql='select '+strWhereFields+' from '+cTable+' where T_JK_LOAN_INFO.LOAN_CODE=\''+loan_code+'\'';

	 //取所有字段

	 var csql='select '+strField+' from '+cTable+' where T_JK_LOAN_INFO.LOAN_CODE=\''+loan_code+'\'';

	 strTable+=' left join ('+csql+') as CUREET on 1=1 ';

	 if(strTable.substring(0,1)==','){

		 strTable=strTable.substr(1);

	 }

	 var strWheres='';

	 for (var i = 0; i < whereList.length; i++) {

		 strWheres+=' '+whereList[i];

	    }

	 if(strWheres.substring(0,1)==','){

		 strWheres=strWheres.substr(1);

	 }

	 if(strField==''){

		 alert('请选择查重项目！');

		 return false;

	 }

	 if(strTable==''){

		 alert('请选择所属对象！');

		 return false;

	 }

	 if(strWheres==''){

		 alert('请选择限制条件！');

		 return false;

	 }

	// var sql='SELECT '+strField+' FROM '+strTable+' WHERE '+strWheres;

	 //新的sql，按查重类型固定字段

	 var sqlfields=$('#sqlFields').val();

	 if(sqlfields==''){

		 alert('请选择规则类型，匹配对应待查字段');

		 return false;

	 }

	 var sql='SELECT '+sqlfields+' FROM '+strTable+' WHERE '+strWheres;

	 //赋值

	 var whereid_check='';

	 for (var i = 0; i < where_numcheck_id.length; i++) {

		 whereid_check+=','+where_numcheck_id[i];

	    }

	 if(whereid_check.substring(0,1)==','){

		 whereid_check=whereid_check.substr(1);

	 }

	 $('#whereNumcheck').val(whereid_check);

	 var whereid_db='';

	 for (var i = 0; i < where_id.length; i++) {

		 whereid_db+=','+where_id[i];

	    }

	 if(whereid_db.substring(0,1)==','){

		 whereid_db=whereid_db.substr(1);

	 }

	 $('#whereRowid').val(whereid_db);

	 var strtables_db='';

	 for (var i = 0; i < tables.length; i++) {

		 strtables_db+=','+tables[i];

	    }

	 if(strtables_db.substring(0,1)==','){

		 strtables_db=strtables_db.substr(1);

	 }

	 $('#rulesTables').val(strtables_db);

	 //查重字段rulesSameColumn fields

	 var strfields_db='';

	 for (var i = 0; i < fields.length; i++) {

		 strfields_db+=','+fields[i];

	    }

	 if(strfields_db.substring(0,1)==','){

		 strfields_db=strfields_db.substr(1);

	 }

	 $('#rulesSameColumn').val(strfields_db);

	 //where字段

	 var strwheres_db='';

	 for (var i = 0; i < where_fields_id.length; i++) {

		 strwheres_db+=','+where_fields_id[i];

	    }

	 if(strwheres_db.substring(0,1)==','){

		 strwheres_db=strwheres_db.substr(1);

	 }

	 $('#whereFields').val(strwheres_db);

	 //where >=

	 var strwheres1_db='';

	 for (var i = 0; i < where_constraints.length; i++) {

		 strwheres1_db+=','+where_constraints[i];

	    }

	 if(strwheres1_db.substring(0,1)==','){

		 strwheres1_db=strwheres1_db.substr(1);

	 }

	 $('#whereConstraints').val(strwheres1_db);

	 //where data

	 var strwheres2_db='';

	 for (var i = 0; i < where_data.length; i++) {

		 strwheres2_db+=','+where_data[i];

	    }

	 if(strwheres2_db.substring(0,1)==','){

		 strwheres2_db=strwheres2_db.substr(1);

	 }

	 $('#whereData').val(strwheres2_db);

	 //whereRelation

	 var strwheres3_db='';

	 for (var i = 0; i < where_relation.length; i++) {

		 strwheres3_db+=','+where_relation[i];

	    }

	 if(strwheres3_db.substring(0,1)==','){

		 strwheres3_db=strwheres3_db.substr(1);

	 }

	 $('#whereRelation').val(strwheres3_db);

	 //是否启用

	 var useState=0;

	if( $('#useState1').attr("checked")){

		useState=1;

	}

	$('#useState').val(useState);

	 var rulesRelation=$('#tableSelect').find("option:selected").text();

	 $('#rulesRelation').val(rulesRelation);

	 //end 赋值 

	 return sql;

}

//保存表单

function saveSqdata(t){

	if($(t).parents("form").validate().form()){

$.ajax({

		type : "POST",

		data : $(t).parents("form").serialize(),

		url : ctx+"/management/rulesconfig/asyncSaveSqdata",

		success : function(data) {

			$(t).parents("form").find("input[name=id]").val(data);

			$(t).parents("form").find("input").attr("disabled", true);

			$(t).parents("form").find("select").attr("disabled", true);

			//saveFormCommon(t);

		}

	});

}

}

//删除行

function deltr(){

	if($(this).parents("tr").attr("id").indexOf("_1")==-1)

      $(this).parents("tr").remove();

 };

 //设置进件数据

 function setjjdata(obj,vNum)

 {

	 var idtxt=obj.attr('id').replace('wherefieldSelect','');

	 var tableName='CUREET.';

	 var v=tableName+obj.find("option:selected").val();

	 var txt=obj.find("option:selected").text();

	 var idtxt=obj.attr('id').replace('wherefieldSelect','');

	// $("#jjdata" + idtxt).text(txt);

	 //设定jjdata中的select

	 //var txtSelect='<select id="jjdata_select'+vNum+'" name="jjdata_select'+vNum+'" ></select>';

	// $("#jjdata" + vNum).html(txtSelect);

	//初始化jjdata_select1 中的下拉选项

            		//字段

	  $('#jjdata_select'+vNum).empty();

	 var tableid='dataTable';

	 $('#'+tableid+' tr').each(function() {

		  var sNum=$(this).find($('div')).size();

		  if(sNum>0){

			  var fieldSelect=$(this).find($('div'));

			  if(fieldSelect.attr("id").indexOf('fieldSelect')!=-1){

				 var id=fieldSelect.attr('id').replace('fieldSelect','');

		    	 var tableName=$('#tableSelect'+id).find("option:selected").text();

		    	 //保存所有字段待查，不以选中为准， fields数组用于待查jjdata，sqlfields用于sql

		    	 $("input[name='fieldcheck"+id+"']").each(function(){

		    		 debugger;

		    		 var v='CUREET.'+$(this).val();

		    		 var txt=tableName+'.'+$(this).attr('data-value');

		    		 $('#jjdata_select'+vNum).append('<option value="'+v+'" >'+txt+'</option>');

		    	 });

			  }

		  }

	  });

	// $("#jjdata_select'+vNum option[value='"+v+"']").attr("selected", "selected");

	 $("#jjdata_select"+vNum).find("option[value='"+v+"']").attr("selected",true);

		//$('#jjdata_select'+vNum).append('<option value="'+v+'" >'+txt+'</option>');

 }

function   AddRow(tableid){ 

    var vTb=$('#'+tableid);

    var vNum=$('#'+tableid+' tr').filter('.CaseRow').size()+1;//表格有多少个数据行

    var vTr=$('#'+tableid+' #'+tableid+'_trRow_1'); //得到表格中的第一行数据

    var vTrClone=vTr.clone(true);//创建第一行的副本对象vTrClone

    vTrClone[0].id=tableid+'_trRow_'+vNum;

    vTrClone.appendTo(vTb);

  //找tr中的对象，并赋ID

    $("#"+tableid+"_trRow_" + vNum + " td").each(function() {

    	$(this).find($("[name = numcheck]:checkbox")).attr("id", "numcheck" + vNum);

        $(this).find($("input[name=tableHid1")).attr("id", "tableHid" + vNum).attr("name", "tableHid" + vNum);

    	if($(this).find($('select')).attr("id")=='wherefieldSelect1')

    	{

    		$(this).find($('select')).attr("id", "wherefieldSelect" + vNum).attr("name", "wherefieldSelect" + vNum);

    	}

    	else if($(this).find($('select')).attr("id")=='whereconstraintSelect1')

    	{

    		$(this).find($('select')).attr("id", "whereconstraintSelect" + vNum).attr("name", "whereconstraintSelect" + vNum);

    	}

    	else if($(this).find($('select')).attr("id")=='whererelationSelect1')

    	{

    		$(this).find($('select')).attr("id", "whererelationSelect" + vNum).attr("name", "whererelationSelect" + vNum);

    	}

    	else if($(this).find($('span')).attr("id")=='jjdata1')

    	{

    		$(this).find($('span')).attr("id", "jjdata" + vNum).attr("name", "jjdata" + vNum);

    	}

    	$(this).find($("select[name=jjdata_select1")).attr("id", "jjdata_select" + vNum).attr("name", "jjdata_select" + vNum);

         });

} 

function   AddTableRow(tableid){ 

    var vTb=$('#'+tableid);

    var vNum=$('#'+tableid+' tr').filter('.CaseRow').size()+1;//表格有多少个数据行

    var vTr=$('#'+tableid+' #'+tableid+'_trRow_1'); //得到表格中的第一行数据

    var vTrClone=vTr.clone(true);//创建第一行的副本对象vTrClone

    vTrClone[0].id=tableid+'_trRow_'+vNum;

    vTrClone.appendTo(vTb);

  //找tr中的对象，并赋ID

    $("#"+tableid+"_trRow_" + vNum + " td").each(function() {

    	if($(this).find($('select')).attr("id")=='tableSelect1')

    	{

    		$(this).find($('select')).attr("id", "tableSelect" + vNum).attr("name", "tableSelect" + vNum);

    	}

    	else if($(this).find($('div')).attr("id")=='fieldSelect1')

    	{

    		$(this).find($('div')).attr("id", "fieldSelect" + vNum).attr("name", "fieldSelect" + vNum);

    	}

    	else if($(this).find($('input')).attr("id")=='setwhereBtn1')

    	{

    		$(this).find($('input')).attr("id", "setwhereBtn" + vNum).attr("name", "setwhereBtn" + vNum);

    	}

         });

}

//判断元素是否在数组中

function contains(a, obj) {

    for (var i = 0; i < a.length; i++) {

        if (a[i] === obj) {

            return true;

        }

    }

    return false;

}

</script>

<style type="text/css">

.jiajia{

width:12px;

}

.jjdatacount_width{

width:40px;

}

</style>

</head>

<body>

<div class="container" style="margin:10px;">

 <form>

<table class="table">

<tbody>

<tr>

<td cols='8'>

规则名称：<input type="text" style="width:150px;" id="rulesCode" name="rulesCode">

内容：

<input type="text" style="width:150px;" id="rulesContent" name="rulesContent">

描述：<input type="text" style="width:350px;" id="rulesRemark" name="rulesRemark">

<select name="rulesType" id="rulesType" style="width:110px;">

	    		   <option value="" >规则分类</option>

	    		   <option value="1" >反欺诈_销售人员</option>

	    		   <option value="2" >反欺诈_黑名单</option>

	    		   <option value="3" selected="selected" >反欺诈_查重</option>

	    		   <option value="4" >反欺诈_案件信息</option>

	         </select>

<input type="checkbox" id="useState1" name="useState1" checked="checked">是否启用

<!-- 启用时间：

					<input type="text" style="width:100px;" class="input_text178" onclick="WdatePicker()" name="minCustomerIntoTime" value="">-

					<input type="text" style="width:100px;" class="input_text178" onclick="WdatePicker()" name="maxCustomerIntoTime" value=""> -->

</td>

</tr>

</tbody></table>

<table class="table">

<tbody>

<tr>

<td></td>

<td>

与主借款人关系

</td>

<td>

<!-- 查重项目 -->

</td>

<td>

<!-- 约束 -->

</td>

<td>

待查数据

</td>

<td>

<a href="javascript:return void();"   id="newruleBtn" title="新增同级别规则">+</a>

</td>

</tr>

<tr>

<td></td>

<td>

<select name="tableSelect" id="tableSelect">

	    		   <option value="" >与主借款人关系</option>

	    		<c:forEach var="item" items="${dataList}" >

	    		<c:if test="${item.tableAlias != '' && item.tableAlias!= null}">

	    		   <option value="${item.tableName}" >${item.tableAlias} </option>

	    		   </c:if>

				</c:forEach>

	         </select>

</td>

<td>

<div name="fieldSelect" id="fieldSelect">

</div>

</td>

<td>

<select name="constraintSelect" id="constraintSelect" class="select78">

	    		   <option value="" >选择约束条件</option>

	    		<option value="1" >存在于</option>

	    		<option value="2" >等于</option>

	    		<option value="3" >不等于</option>

	         </select>

</td>

<td>

<input type="button" id="setdataBtn" name="setdataBtn" class="button" value="设置数据集">

</td>

<td></td>

</tr>

</tbody>

</table>

 <table class="table" id="dataTable">

	  <tbody>

	  <tr>

	  <td></td><td>所属对象</td><td>查重项目</td><td></td><td>条件</td><td>

	  <a href="javascript:return void();"  id="newtableBtn" title="新增对象">+</a>

	  </td>

	  </tr>

	   <tr id="dataTable_trRow_1"  class="CaseRow">

	  <td></td><td><select name="tableSelect1" id="tableSelect1">

	    		   <option value="" >选择查重项目</option>

	    		<c:forEach var="item" items="${dataList}" >

	    		   <option value="${item.tableName}" >${item.tableComment} </option>

				</c:forEach>

	         </select></td>

	         <td>

	         <div name="fieldSelect1" id="fieldSelect1">

<%-- <c:forEach var="item" items="${fieldList}" >

<input type="checkbox" name="fieldcheck1"  value="${item.fieldname}" />${item.fieldcomment}

</c:forEach> --%>

</div>

	         </td>

	         <td>

	         </td>

	         <td>

	         <input type="button" id="setwhereBtn1" name="setwhereBtn1" class="button" value="设置条件">

	         </td>

	         <td>

	        <a href="javascript:return void();"  class="del"  id="deltableBtn" title="减少条件">-</a>

	         </td>

	  </tr>

	  </tbody>

	  </table>

	  <!-- where table -->

	   <table class="table" id="whereTable">

	  <tbody>

	  <tr>

	  <td><span id='numcheckspan'>次数</span></td><td>字段</td><td>约束</td><td>本次进件数据</td>

	  <td>条件关系    

	  </td><td class="jiajia">

	  <a href="javascript:return void();"  id="newwhereBtn" title="新增条件">+</a>

	  </td>

	  </tr>

	  <tr id="whereTable_trRow_1" class="CaseRow">

	  <td>

	  <input type="checkbox" name="numcheck" id="numcheck1" >

	  <input type="hidden" id="tableHid1" name="tableHid1">

	  </td><td>

	  <select name="wherefieldSelect1" id="wherefieldSelect1">

	    		   <option value="" >选择字段</option>

	         </select>

	  </td><td>

	  <select name="whereconstraintSelect1" id="whereconstraintSelect1" >

	    	   <option value="" >选择约束条件</option>

	    		<option value="1" >存在于</option>

	    		<option value="2" >等于</option>

	    		<option value="3" >不等于</option>

	    		<option value="4" >大于</option>

	    		<option value="5" >小于</option>

	    		<option value="6" >大于等于</option>

	    		<option value="7" >小于等于</option>

	         </select>

</td><td>

<span name="jjdata1" id="jjdata1">

 <select name="jjdata_select1" id="jjdata_select1" >

	    		   <option value="">进件数据</option>

	         </select>

</span>

</td><td>

 <select name="whererelationSelect1" id="whererelationSelect1" >

	    		   <option value="" >选择条件关系</option>

	    		<option value="and" >并且</option>

	    		<option value="or" >或者</option>	

	         </select>

</td>

	  <td>

	  <a href="javascript:return void();"  class="del"  id="delwhereBtn" title="减少条件">-</a>

	  </td>

	  </tr>

	   <tr>

	    </tbody>

	  </table>

	  <!-- /where table -->

	  	  <!-- tools table -->

	  <table class="table" id="toolsTable">

	  <tbody>

	  <tr>

	  <td></td><td></td>

	  <td>

	  <input type="button" class="btn btn-default" id="addorderBtn" value="设置前一次进件">

	  <input type="button" class="btn btn-default" id="adddayBtn" value="设置天数">

	  <input type="button" class="btn btn-default" id="addrelationBtn" value="设置次数">

	  </td><td>

	  </td>

	  <td>

	  </td><td class="jiajia">

	  </td>

	  </tr>

	  </tbody>

	  </table>

	  <!-- /tools table -->

<table>

<tbody>

<tr>

<td cols="6"><input type="button" id="setsqlBtn" name="setsqlBtn" class="button" value="查看SQL">

<input type="button" id="savesqlBtn" name="savesqlBtn" class="button" value="   保存   ">

<input type="button" id="testsqlBtn" name="testsqlBtn" class="button" value="   回显   ">

</td>

</tr>

</tbody>

</table>

<table id="sqltable">

<tbody>

<tr>

<td cols="16">

<textarea id="sqlAuto" name="sqlAuto" rows="10" cols="8" style="width:900px;"></textarea>

</td>

</tr>

</tbody></table>

<!-- data form -->

<input type="hidden" name="rulesRelation" id="rulesRelation" >

<input type="hidden" name="rulesTables" id="rulesTables" >

<input type="hidden" name="useState" id="useState" >

<input type="hidden" name="whereFields" id="whereFields" >

<input type="hidden" name="rulesSameColumn" id="rulesSameColumn" >

<input type="hidden" name="whereConstraints" id="whereConstraints" >

<input type="hidden" name="whereData" id="whereData" >

<input type="hidden" name="whereRelation" id="whereRelation" >

<input type="hidden" name="whereRelation" id="whereRelation" >

<input type="hidden" name="whereRelation" id="whereRelation" >

<input type="hidden" name="tableRowid" id="tableRowid" >

<input type="hidden" name="whereRowid" id="whereRowid" >

<input type="hidden" name="whereNumcheck" id="whereNumcheck" >

<input type="hidden" name="sqlFields" id="sqlFields" >

<!-- /data form -->

 </form>

	         </div>

</body>

</html>

　　

相关文章