package online.zhaopei.myproject.sqlprovide.ecssent; import java.io.Serializable; import org.apache.ibatis.jdbc.SQL; import com.alibaba.druid.util.StringUtils; import online.zhaopei.myproject.domain.ecssent.DistHead; public class DistHeadSqlProvide implements Serializable { /** * serialVersionUID */ private static final long serialVersionUID = -4962289950024210261L; public String getDistHeadByInvtNoSql(final String invtNo) { return new SQL() {{ this.SELECT("pdh.seq_no"); this.SELECT("pdh.dist_no"); this.SELECT("pdh.customs_code"); this.SELECT("pdh.decl_code"); this.SELECT("pdh.decl_name"); this.SELECT("pdh.dist_stat"); this.SELECT("pdh.input_date"); this.SELECT("pdh.decl_date"); this.SELECT("pdh.return_reason"); this.SELECT("pdh.note"); this.FROM("pre_dist_head pdh"); this.INNER_JOIN("pre_dist_bill_list pdbl on pdh.seq_no = pdbl.seq_no"); if(!StringUtils.isEmpty(invtNo)) { this.WHERE("pdbl.bill_no = '" + invtNo + "'"); } else { this.WHERE("1 = 2"); } }}.toString(); } public String getDistHeadListSql(final DistHead distHead) { return new SQL() {{ this.SELECT("pdh.seq_no"); this.SELECT("pdh.dist_no"); this.SELECT("pdh.dist_type"); this.SELECT("pdh.dist_stat"); this.SELECT("pdh.customs_code"); this.SELECT("pdh.i_e_flag"); this.SELECT("pdh.i_e_type"); this.SELECT("pdh.total_pack_num"); this.SELECT("pdh.total_gross_wt"); this.SELECT("pdh.decl_code"); this.SELECT("pdh.decl_name"); this.SELECT("pdh.input_date"); this.SELECT("pdh.district_code"); this.SELECT("pdh.discharge_code"); this.SELECT("pdh.return_reason"); this.SELECT("pdvl.ve_no"); this.FROM("pre_dist_head pdh"); this.LEFT_OUTER_JOIN("pre_dist_ve_list pdvl on pdh.seq_no = pdvl.seq_no"); if (!StringUtils.isEmpty(distHead.getSeqNo())) { this.WHERE("pdh.seq_no = '" + distHead.getSeqNo() + "'"); } if (!StringUtils.isEmpty(distHead.getDistNo())) { this.WHERE("pdh.dist_no like '%" + distHead.getDistNo() + "%'"); } if (!StringUtils.isEmpty(distHead.getDistStat())) { this.WHERE("pdh.dist_stat = '" + distHead.getDistStat() + "'"); } if (!StringUtils.isEmpty(distHead.getVeNo())) { this.WHERE("pdvl.ve_no like '%" + distHead.getVeNo() + "%'"); } if (!StringUtils.isEmpty(distHead.getCustomsCode())) { this.WHERE("pdh.customs_code = '" + distHead.getCustomsCode() + "'"); } if (!StringUtils.isEmpty(distHead.getIeFlag())) { this.WHERE("pdh.i_e_flag = '" + distHead.getIeFlag() + "'"); } if (!StringUtils.isEmpty(distHead.getIeType())) { this.WHERE("pdh.i_e_type = '" + distHead.getIeType() + "'"); } if (!StringUtils.isEmpty(distHead.getDeclCode())) { this.WHERE("pdh.decl_code = '" + distHead.getDeclCode() + "'"); } if (!StringUtils.isEmpty(distHead.getDeclName())) { this.WHERE("pdh.decl_name like '%" + distHead.getDeclName() + "%'"); } if (!StringUtils.isEmpty(distHead.getIePort())) { this.WHERE("pdh.i_e_port = '" + distHead.getIePort() + "'"); } if (!StringUtils.isEmpty(distHead.getBeginInputDate())) { this.WHERE("to_char(pdh.input_date, 'yyyy-mm-dd') >= '" + distHead.getBeginInputDate() + "'"); } if (!StringUtils.isEmpty(distHead.getEndInputDate())) { this.WHERE("to_char(pdh.input_date, 'yyyy-mm-dd') <= '" + distHead.getEndInputDate() + "'"); } if (!StringUtils.isEmpty(distHead.getInputDateStr())) { this.WHERE("to_char(pdh.input_date, 'yyyy-mm-dd') = '" + distHead.getInputDateStr() + "'"); } }}.toString(); } public String getDistHeadBySeqNoSql(final String seqNo) { return new SQL() {{ this.SELECT("pdh.seq_no"); this.SELECT("pdh.dist_no"); this.SELECT("pdh.cop_dist_no"); this.SELECT("pdh.dist_type"); this.SELECT("pdh.dist_stat"); this.SELECT("pdh.customs_code"); this.SELECT("pdh.i_e_flag"); this.SELECT("pdh.i_e_type"); this.SELECT("pdh.total_pack_num"); this.SELECT("pdh.total_gross_wt"); this.SELECT("pdh.decl_code"); this.SELECT("pdh.decl_name"); this.SELECT("pdh.input_person"); this.SELECT("pdh.input_date"); this.SELECT("pdh.district_code"); this.SELECT("pdh.discharge_code"); this.SELECT("pdh.return_reason"); this.SELECT("pdh.cfs_code"); this.SELECT("pdh.i_e_port"); this.SELECT("pdh.note"); this.SELECT("pdvl.ve_no"); this.FROM("pre_dist_head pdh"); this.LEFT_OUTER_JOIN("pre_dist_ve_list pdvl on pdh.seq_no = pdvl.seq_no"); if (!StringUtils.isEmpty(seqNo)) { this.WHERE("pdh.seq_no = '" + seqNo + "'"); } else { this.WHERE("1 = 2"); } }}.toString(); } public String calculationPackWtSql(final String distNo) { return new SQL() {{ this.UPDATE("pre_dist_head pdh"); this.SET("(pdh.total_pack_num, pdh.total_gross_wt) = (select count(pdbl.pack_num), sum(pdbl.gross_wt) from pre_dist_bill_list pdbl where pdbl.dist_no = '" + distNo + "')"); this.WHERE("pdh.dist_no = '" + distNo + "'"); }}.toString(); } }