packagecom.transsnet.sims.business;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importorg.apache.commons.lang3.StringUtils;importorg.jooq.Condition;importorg.jooq.DSLContext;importorg.jooq.Record;importorg.jooq.Record1;importorg.jooq.Record2;importorg.jooq.Result;importorg.jooq.SelectJoinStep;importorg.jooq.UpdateSetFirstStep;importorg.jooq.UpdateSetMoreStep;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Component;importorg.springframework.transaction.annotation.Transactional;importorg.springframework.util.CollectionUtils;importcom.transsnet.sims.common.dto.PageDto;importcom.transsnet.sims.common.dto.PosDto;importcom.transsnet.sims.common.dto.RetailerDto;importcom.transsnet.sims.common.dto.SaDto;importcom.transsnet.sims.storage.easybuy_aad.Tables;importcom.transsnet.sims.storage.easybuy_aad.tables.daos.AadSaConfigDao;importcom.transsnet.sims.storage.easybuy_aad.tables.pojos.AadSaConfig;importcom.transsnet.sims.storage.easybuy_aad.tables.records.AadSaConfigRecord;
@Componentpublic classDemoBis {
@AutowiredprivateAadSaConfigDao aadSaConfigDao;
@AutowiredprivateDSLContext dslContext;private com.transsnet.sims.storage.easybuy_aad.tables.AadSaConfig aadSaConfig =Tables.AAD_SA_CONFIG;private com.transsnet.sims.storage.easybuy_aad.tables.AadPosConfig aadPosConfig =Tables.AAD_POS_CONFIG;private com.transsnet.sims.storage.easybuy_aad.tables.AadRetailerConfig aadRetailerConfig =Tables.AAD_RETAILER_CONFIG;private com.transsnet.sims.storage.easybuy_aad.tables.AadIndexTable aadIndexTable =Tables.AAD_INDEX_TABLE;/*** 分页查询list
*@parampageNum
*@parampageSize
*@paramdto
*@return
*/
public PageDto pageList(int pageNum, intpageSize, SaDto dto) {
SelectJoinStep selectRecord =dslContext.select().from(aadSaConfig);
SelectJoinStep> selectCountRecord =dslContext.selectCount().from(aadSaConfig);//写一个一定成立的条件,这里写主键不为空
Condition eq =aadSaConfig.ID.isNotNull();//按需要拼接查询条件
if(StringUtils.isNotBlank(dto.getSaId())) {
eq= eq.and(aadSaConfig.SA_ID.like("%" + dto.getSaId() + "%"));
}if(StringUtils.isNotBlank(dto.getStatus())) {
eq=eq.and(aadSaConfig.STATUS.eq(dto.getStatus()));
}//查询总数
Integer count = selectCountRecord.where(eq).fetchOne().into(Integer.class);//构造分页组件
PageDto pageDto = newPageDto(pageNum, pageSize, count);//查询数据
List list =selectRecord.where(eq).orderBy(aadSaConfig.ID.desc())
.limit(pageSize)
.offset(pageSize* (pageNum - 1))
.fetchInto(AadSaConfig.class);
pageDto.setList(list);returnpageDto;
}/*** 查询list
*@paramdto
*@return
*/
public ListfetchList(SaDto dto) {
SelectJoinStep selectRecord =dslContext.select().from(aadSaConfig);//写一个一定成立的条件,这里写主键不为空
Condition eq =aadSaConfig.ID.isNotNull();//按需要拼接查询条件
if(StringUtils.isNotBlank(dto.getSaId())) {
eq= eq.and(aadSaConfig.SA_ID.like("%" + dto.getSaId() + "%"));
}if(StringUtils.isNotBlank(dto.getStatus())) {
eq=eq.and(aadSaConfig.STATUS.eq(dto.getStatus()));
}//查询数据
List list =selectRecord.where(eq).orderBy(aadSaConfig.ID.desc())
.fetchInto(AadSaConfig.class);returnlist;
}/*** 根据销售id查询详细
*@paramsaId
*@return
*/
publicAadSaConfig detail(String saId) {
List beans =aadSaConfigDao.fetchBySaId(saId);if(!CollectionUtils.isEmpty(beans)) {return beans.get(0);
}return null;
}/*** 新增记录*/@Transactionalpublic voidcreate(SaDto dto) {//查询销售序列值,行锁select for update
Integer index =dslContext.select(aadIndexTable.INDEX).from(aadIndexTable)
.where(aadIndexTable.TABLE.eq("aad_sa_config").and(aadIndexTable.FIELD.eq("sa_id")))
.forUpdate()
.fetchOneInto(Integer.class);//填充销售ID为定长字段,5位长度
String saId = "SA" + String.format("%05d", index);
dslContext.insertInto(aadSaConfig)
.set(aadSaConfig.SA_ID, saId)//销售Id按规则自增
.set(aadSaConfig.STATUS, dto.getStatus())
.execute();//添加记录成功后需要将index加1
dslContext.update(aadIndexTable).set(aadIndexTable.INDEX, ++index)
.where(aadIndexTable.TABLE.eq("aad_sa_config").and(aadIndexTable.FIELD.eq("sa_id")))
.execute();
}/*** 更新记录*/
public voidupdate(SaDto dto) {
UpdateSetFirstStep updateRecord =dslContext.update(aadSaConfig);
UpdateSetMoreStep setRecord = null;if(StringUtils.isNotBlank(dto.getSaName())) {
setRecord=updateRecord.set(aadSaConfig.SA_NAME, dto.getSaName().toUpperCase());
}if(StringUtils.isNotBlank(dto.getStatus())) {
setRecord=updateRecord.set(aadSaConfig.STATUS, dto.getStatus());
}if(setRecord == null) {return;
}
setRecord.where(aadSaConfig.SA_ID.eq(dto.getSaId())).execute();
}/*** 删除记录*/
public voiddelete(String saId) {
dslContext.delete(aadSaConfig).where(aadSaConfig.SA_ID.eq(saId)).execute();
}/*** 根据销售名称查询销售信息
*@paramsaName
*@return
*/
public List>fetchSaByName(String saName){//查询指定的字段
Result> resultRecord =dslContext.select(aadSaConfig.SA_ID, aadSaConfig.SA_NAME)
.from(aadSaConfig)
.where(aadSaConfig.SA_NAME.like("%" + saName + "%"))
.fetch();
List> list = new ArrayList>();//获取查询出来的字段值
for(Record2record : resultRecord) {
Map item = new HashMap();
item.put("label", record.getValue(aadSaConfig.SA_NAME));
item.put("value", record.getValue(aadSaConfig.SA_ID));
list.add(item);
}returnlist;
}/*** 联表更新数据
*@paramdto*/
public voidupdatePos(PosDto dto) {/** 最初是想使用insert into select,但是jooq中只能整表复制,但我们只需要某几个字段
* 尝试过先insert,然后再update,但这样比较消耗性能
* 试了几次后发现最好还是先把所需数据查出来,再进行插入*/
//获取商户相关信息,赋值到门店上
RetailerDto retailer = dslContext.selectFrom(aadRetailerConfig).where(aadRetailerConfig.RETAILER_ID.eq(dto.getRetailerId())).fetchOneInto(RetailerDto.class);//新增数据
dslContext.insertInto(aadPosConfig)
.set(aadPosConfig.POS_ID, dto.getPosId())
.set(aadPosConfig.POS_NAME, dto.getPosName())
.set(aadPosConfig.RETAILER_ID, retailer.getRetailerId())
.set(aadPosConfig.RETAILER_NAME, retailer.getRetailerName())
.execute();
}
}