Extjs 完整的一个查询脚本
if ("查询".equals(buttonList.get(i).getFunctionName())) {
buttonSbf.append("iconCls:'queryIcon',\n");
buttonSbf.append("handler:function(){queryRecord(store);}\n");
}
---------------------------------------------------------
//设置为同步
dwr.engine.setAsync(false);
//查询Form高度
var queryFormWidth=110;
//共用参数
var params={
gridParams:[
{name:"id",type:"renderer",header:"ID",ishref:"false"},//第一个暂定为主键
{name:"gdType",type:"String",header:"工单类型",ishref:"false"},
{name:"tsFrom",type:"String",header:"工单来源",ishref:"false"},
{name:"gdid",type:"renderer1",header:"工单编号",ishref:"false"},
{name:"contact",type:"String",header:"会员姓名",ishref:"false"},
{name:"orderId",type:"String",header:"产品订单号",ishref:"false"},
{name:"DIFFERENT_GD",type:"String",header:"新建/历史",ishref:"false"},
{name:"jjCd",type:"String",header:"紧急程度",ishref:"false"},
{name:"gdStatus",type:"String",header:"工单状态",ishref:"false"},
{name:"tsLevel",type:"String",header:"工单级别",ishref:"false"},
{name:"eventconnect",type:"String",header:"事件联系人",ishref:"false"},
{name:"eventtel",type:"String",header:"事件联系人电话",ishref:"false"},
{name:"hylevel",type:"String",header:"会员级别",ishref:"false"},
{name:"associateDepts",type:"String",header:"关联部门",ishref:"false"},
{name:"associatePersons",type:"String",header:"关联责任人",ishref:"false"},
{name:"burdenDepts",type:"String",header:"责任部门",ishref:"false"},
{name:"burdenPersons",type:"String",header:"责任人",ishref:"false"}
],
listOperater:{
list:"ComplaintsManager.dwrListPage",
deleteRecord:"ComplaintsManager.dwrDelete"
},
queryCondition:[ //对应 表别名,数据类型,数据字段,操作符,值1,值2,连接关系,括号,控件类型
["","String","hysimple","skip","","","","","关联人/关联部门/责任人/责任部门-快速查询"],
["","String","gdType","like","","","","","工单类型"],
["","String","orderId","like","","","","","订单号"],
["","String","gdid","like","","","","","工单编号"],
["","String","contact","like","","","","","联系人"],
["","String","telphoneNum","like","","","","","联系电话"],
["","String","hyid","like","","","","","会员号"],
["","DateBetween","gdTime","DateBetween","","","","","工单创建日期"],
["","String","gdStatus","like","","","","","工单状态"],
["","String","eventconnect","like","","","","","事件联系人姓名"],
["","String","DIFFERENT_GD","like","","","","","历史/新建"],
["","String","associateDepts","like","","","","","关联部门"],
["","String","associatePersons","like","","","","","关联责任人"],
["","String","burdenDepts","like","","","","","责任部门"],
["","String","burdenPersons","like","","","","","责任人"]
],
title:"",
orderdirection:"desc",//排序方向(升序asc,降序desc)
div:"list"
};
//工单状态查询
var gdStatus = [['全部',''],['预处理','预处理'],['待分配','待分配'],['待处理','待处理'],['处理中','处理中'],['流转中','流转中'],['流转完毕','流转完毕'],['关闭','关闭'],['审批中','审批中'],['审批超时','审批超时'],['审批完毕','审批完毕'],['审批退回','审批退回'],['已归档','已归档']];
var gdstyle_value = new Ext.data.ArrayStore({
fields:['text','value'],
data:gdStatus
});
var gdstyles = new Ext.form.ComboBox({
store: gdstyle_value,
valueField:'value',
displayField:'text',
typeAhead: true,
mode: 'local',
triggerAction: 'all',
hiddenName:'gdStatus',
emptyText:'工单状态',
selectOnFocus:true,
fieldLabel:"工单状态",
width:80
});
/**历史与新建工单之查询**/
var gddiffent = [['新建工单','新建工单'],['历史工单','历史工单']];
var gddiffent_value = new Ext.data.ArrayStore({
fields:['text','value'],
data:gddiffent
});
var gddiffents = new Ext.form.ComboBox({
store: gddiffent_value,
valueField:'value',
displayField:'text',
typeAhead: true,
mode: 'local',
triggerAction: 'all',
hiddenName:'DIFFERENT_GD',
emptyText:'历史/新建',
selectOnFocus:true,
fieldLabel:"历史/新建",
width:80
});
//工单类型
var gdtype = [['全部',''],['投诉','投诉'],['差错','差错'],['建议','建议'],['表扬','表扬']];
var box_value = new Ext.data.ArrayStore({
fields:['text','value'],
data:gdtype
});
var comboBxSend = new Ext.form.ComboBox({
store: box_value,
valueField:'value',
displayField:'text',
typeAhead: true,
mode: 'local',
triggerAction: 'all',
hiddenName:'gdType',
emptyText:'请选择工单类型',
selectOnFocus:true,
fieldLabel:"工单类型",
width:80
});
var listOperater=params["listOperater"];
//查询条件 Form
var queryForm = new Ext.FormPanel({
labelAlign: 'top',
frame:true,
title: '',
el:'qform',
keys:[{//支持回车提交
key:13,
fn:function(){
queryRecord(store);
},
scope:this
}],
listeners:{//默认第一个文本框获取焦点
"render":function(){
this.findByType("textfield")[0].focus(true,600);
}
},
bodyStyle:'padding:5px 5px 0',
autoWidth : true,
height:queryFormWidth,
plain:true,
layout:"form",
labelWidth:80,
labelAlign:"right",
items: [{
xtype:"panel",
layout:"column",
fieldLabel:"快速查询",
isFormField:true,
items:[{
columnWidth:.16,
xtype:"textfield",
blankText:"不能为空，请填写",
emptyText:'关联人/关联部门/责任人/责任部门',
name:"hysimple",
anchor:"4%"
},{
columnWidth:.28,
layout:"form",
labelWidth:80,//标签宽度
labelAlign:"right",
items:[{
columnWidth:.30,
xtype:"textfield",
fieldLabel:"工单编号",
name:"gdid",
anchor:"95%"//文本框占比率
}]
},{
columnWidth:.25,
layout:"form",
labelWidth:70,//标签宽度
labelAlign:"right",
items:[{
columnWidth:.30,
xtype:"textfield",
fieldLabel:"会员姓名",
name:"contact",
anchor:"95%"//文本框占比率
}]
},{
columnWidth:.25,
layout:"form",
labelWidth:70,//标签宽度
labelAlign:"right",
items:[{
columnWidth:.30,
xtype:"textfield",
fieldLabel:"会员电话",
name:"telphoneNum",
anchor:"95%"//文本框占比率
}]
}]
},{
xtype:"panel",
layout:"column",
fieldLabel:"创建时间",
isFormField:true,
format: 'Y-m-d H:i:s',
items:[{
columnWidth:.16,
xtype:"datefield",
//allowBlank:false,//必填项设置
blankText:"不能为空，请填写",
format: 'Y-m-d',
name:"gdTime",
id:"from",
anchor:"95%"
},{
columnWidth:.28,
layout:"form",
labelWidth:80,//标签宽度
labelAlign:"right",
items:[{
xtype:"datefield",
fieldLabel:"至",
format: 'Y-m-d',
name:"gdTime",
id:"to",
anchor:"95%"//文本框占比率
}]
},{
columnWidth:.25,
layout:"form",
labelWidth:70,//标签宽度
labelAlign:"right",
items:[{
columnWidth:.30,
xtype:"textfield",
fieldLabel:"会员号",
name:"hyid",
anchor:"95%"//文本框占比率
}]
},{
//工单类型
columnWidth:.28,
layout:"form",
labelWidth:70,//标签宽度
labelAlign:"right",
items:[comboBxSend],
anchor:"95%"
}
]
},{
xtype:"panel",
layout:"column",
fieldLabel:"事件联系人",
isFormField:true,
items:[{
columnWidth:.16,
xtype:"textfield",
name:"eventconnect",
emptyText:'事件联系人姓名',
anchor:"95%"
},{
//工单类型
columnWidth:.25,
layout:"form",
labelWidth:80,//标签宽度
labelAlign:"right",
items:[gdstyles],
anchor:"95%"
},{
columnWidth:.25,
layout:"form",
labelWidth:102,//标签宽度
labelAlign:"right",
items:[gddiffents],
anchor:"95%"
},{
columnWidth:.28,
layout:"form",
labelWidth:100,//标签宽度
labelAlign:"right",
items:[{
columnWidth:.30,
xtype:"textfield",
fieldLabel:"订单号",
name:"orderId",
anchor:"95%"//文本框占比率
}]
}]
}
]
});
function renderer(v,store,data){
return "<a href='javascript:view("+data.data.gdid+")'>"+data.data.id+"</a>";
}
function renderer1(v,store,data){
return "<a href='javascript:sendpubnote("+data.data.gdid+")'>"+data.data.gdid+"</a>";
}
-----------------------------------------------------------
queryRecord = function(store)
{
conditions=[];
queryflag=1;//是否查询 避免进入页面就查询
orderby = params["orderby"]=(params["orderby"]==undefined?pk:params["orderby"]);//排序字段
orderdirection = params["orderdirection"]=(params["orderdirection"]==undefined?"desc":params["orderdirection"]);//排序方向
var queryConfig=new QueryConfig(queryCondition);
var querygrid=queryConfig.getQueryGrid();
var dataLength=querygrid.store.data.length;
var queryGridRds=querygrid.getStore().getRange(0,dataLength-1);
for(var i=0;i<queryGridRds.length;i++)
{
if(queryGridRds[i].get("operator"))
{
conditions.push(queryGridRds[i].data);
}
}
fitFormValue(conditions);
store.load({params:{start:0,limit:pagesize,conditions:conditions,queryflag:queryflag,orderby:params["orderby"],orderdirection:params["orderdirection"]}});
}
}
--------------------------------------------------------
@RemoteMethod
@Override
public Page<Complaints> dwrListPage(DwrBackParams params) {
StringBuffer hql = new StringBuffer("from Complaints where 1=1");// 在这里拼HQL
StringBuffer sql = new StringBuffer("select * from Complaints C left outer join COMP_APPROVE_OPINION O on (C.gdid=O.gdid ) ");// 在这里拼SQL
sql.append("left outer join COMP_DISPOSAL D on (C.gdid=D.gdid) where 1=1");
int temp = 0;//是否模糊查询，1代表是，0代表否
Page<Complaints> page = null;
temp = geneHQLS(params.getConditions(), sql);
if(temp == 0){
page = super.dwrListPageHQL(params, hql.toString());
}else{
List<Complaints> cpList = complaintsdao.findBySql(sql.toString());
if(cpList != null){
page = new Page<Complaints>();
page.setItems(cpList);
page.setTotalCount(cpList.size());
}
}
return page;
}
---------------------------------------------------------------
private int geneHQLS(List<Condition> conditionsone, StringBuffer sql) {
int temp = 0;
for (Condition one : conditionsone) {
if (("hysimple".equals(one.getColumnName()))
&& (StringUtils.isNotEmpty(one.getFirstValue())))// 快速查询
{
temp = 1;
sql.append(" and (D.BURDEN_PERSON like '%" + one.getFirstValue()//责任人
+ "%' or D.BURDEN_DEPT like '%" + one.getFirstValue()//责任部门
+ "%' or D.ASSOCIATE_PERSON like '%" + one.getFirstValue()//关联人
+ "%' or D.ASSOCIATE_DEPT like '%" + one.getFirstValue()//关联部门
+ "%' or C.hyid like '%" + one.getFirstValue()//会员编号
+ "%' or C.eventtel like '%" + one.getFirstValue()//事件联系人电话
+ "%' or C.tsFrom like '%" + one.getFirstValue()//有素来源
+ "%' or C.pcQk like '%" + one.getFirstValue()//赔偿情况
+ "%' or C.tsContent like '%" + one.getFirstValue()//投诉内容
+ "%' or C.zjContent like '%" + one.getFirstValue()//追加内容
+ "%' or C.city like '%" + one.getFirstValue()//来源城市
+ "%' or C.cjrId like '%" + one.getFirstValue()//创建人
+ "%' or D.CURR_DEAL_PERSON like '%" + one.getFirstValue()//当前处理人
+ "%' or D.validity like '%" + one.getFirstValue()//投诉有效性
+ "%' or D.FACT_PAYFOR like '%" + one.getFirstValue()//实际赔偿情况
+ "%' or D.tsreason like '%" + one.getFirstValue()//投诉原因
+ "%' or D.DISPOSAL_REG like '%" + one.getFirstValue()//原因核实
+ "%' or D.problems like '%" + one.getFirstValue()//处理情况
+ "%' or D.DISPOSAL_PRECEPT like '%" + one.getFirstValue()//处理结果
+ "%' or D.DEAL_INFO like '%" + one.getFirstValue()//处理信息
+ "%' or D.DEAL_INFO_ADD like '%" + one.getFirstValue()//追加处理信息
+ "%' or O.APPROVE_INFO like '%" + one.getFirstValue()//历史审批信息
+ "%' or O.APPROVE_INFO_ADD like '%" + one.getFirstValue()//当前审批信息
+ "%') ");
}
if(temp == 1 && "gdType".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.gdType like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "orderId".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.orderId like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "gdid".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.gdid like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "contact".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.contact like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "telphoneNum".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.telphoneNum like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "hyid".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.hyid like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "gdStatus".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.gdStatus like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "eventconnect".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.eventconnect like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "DIFFERENT_GD".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.DIFFERENT_GD like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "associateDepts".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.associateDepts like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "associatePersons".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.associatePersons like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "burdenDepts".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.burdenDepts like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "burdenPersons".equals(one.getColumnName())
&& (StringUtils.isNotEmpty(one.getFirstValue()))){
sql.append(" and (C.burdenPersons like '%" + one.getFirstValue() + "%')");
}
if(temp == 1 && "gdTime".equals(one.getColumnName()) &&
(StringUtils.isNotBlank(one.getFirstValue()) || StringUtils.isNotBlank(one.getSecondValue()))){
if(StringUtils.isNotBlank(one.getFirstValue())){
sql.append(" and (to_char(C.gdTime,'yyyy-mm-dd') >= '" + one.getFirstValue() + "')");
}
if(StringUtils.isNotBlank(one.getSecondValue())){
sql.append(" and (to_char(C.gdTime,'yyyy-mm-dd') <= '" + one.getSecondValue() + "')");
}
}
}
return temp;
}
------------------------------------------------------------------------
@RemoteMethod
@Transactional(readOnly = true)
public Page<T> dwrListPageHQL(DwrBackParams params, String hql) {
Page<T> page = new Page<T>();
try {
int pageSize = params.getLimit();
int currentPage = params.getStart() / pageSize + 1;
int queryflag = params.getQueryflag();
String orderby = params.getOrderby();
String orderDirection = params.getOrderdirection();
if (queryflag == 1) {
List<Condition> conditions = params.getConditions();
List<PropertyFilter> filters = DWRUtil
.buildPropertyFilters(conditions);
Page<T> argPage = new Page<T>(currentPage, pageSize);
if (orderby != null) {
argPage.setOrderBy(orderby);// 设置排序参数
argPage.setOrder(orderDirection);// 设置排序方向 降序或者降序
}
StringBuffer sbHQL = new StringBuffer(hql);
List<Object> values = new ArrayList<Object>();
getEntityDao().buildFilterConditions(filters, sbHQL, values);
page = getEntityDao().findPage(sbHQL.toString(), values.toArray(),
argPage);
}
} catch (Exception e) {
logger.error("dwrListPageHQL列表查询出现异常",e);
}
return page;
}
--------------------------------------------------------------------------
public List<T> findBySql(final String sql) {
Session session = this.getSession();
List<T> catNameList = null;
try {
catNameList = session.createSQLQuery(sql).addEntity(entityClass).list();
return catNameList ;
}catch(Exception ex)
{
ex.printStackTrace();
}
return catNameList;
}