StoreDao.java
6.84 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
package vion.dao;
import cn.hutool.core.util.StrUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import vion.model.Address;
import vion.model.Store;
import vion.vo.StoreVO;
import java.util.Date;
import java.util.List;
@Repository
public class StoreDao {
@Autowired
private JdbcTemplate jdbcTemplate;
private static final String SELECT_STORE_ALL= "SELECT * FROM tbl_store_info";
public List<Store> getStoreAll() {
return jdbcTemplate.query(SELECT_STORE_ALL, new BeanPropertyRowMapper<Store>(Store.class));
}
private static final String SAVE_STORE= "INSERT INTO tbl_store_info(\n" +
" storenum, name, \"number\", contract_code, amount, orderdate, \n" +
" salesperson, warranty_period, customer_name, implement_type, \n" +
" project_state, contacts, create_user,remark, project_stage,account_id)\n" +
" VALUES (?, ?, ?, ?, ?, ?, \n" +
" ?, ?, ?, ?, ?,\n" +
" ?, ?, ?, ?, ?);";
public Integer addStore(StoreVO store) {
return jdbcTemplate.update(SAVE_STORE,
new Object[]{
store.getStorenum(),store.getName(),store.getNumber(),store.getContract_code(),store.getAmount(),
store.getOrderdate(),store.getSalesperson(),store.getWarranty_period(),store.getCustomer_name(),
store.getImplement_type(),store.getProject_state(),
store.getContacts(),store.getCreate_user(),store.getRemark(),
store.getProject_stage(),store.getAccount_id()});
}
private static final String UPDATE_STORE= "UPDATE tbl_store_info\n" +
" SET storenum=?, name=?, \"number\"=?, contract_code=?, amount=?, \n" +
" orderdate=?, salesperson=?, warranty_period=?, customer_name=?, \n" +
" implement_type=?, project_state=?, contacts=?, \n" +
" modify_user=?, modify_time=?, remark=?, \n" +
" project_stage=?,account_id=?\n" +
" WHERE id=?;";
public Integer updateStore(StoreVO store) {
return jdbcTemplate.update(UPDATE_STORE,
new Object[]{
store.getStorenum(),store.getName(),store.getNumber(),store.getContract_code(),store.getAmount(),
store.getOrderdate(),store.getSalesperson(),store.getWarranty_period(),store.getCustomer_name(),
store.getImplement_type(),store.getProject_state(),store.getContacts(),
store.getModify_user(),store.getModify_time(),store.getRemark(),store.getProject_stage(),
store.getAccount_id(),store.getId()});
}
private static final String UPDATE_STORE_STAGE= "UPDATE tbl_store_info SET project_stage=? WHERE id=?;";
public Integer updateStoreStage(Integer id,Integer project_stage) {
return jdbcTemplate.update(UPDATE_STORE_STAGE,
new Object[]{project_stage,id});
}
private static final String SELECT_STORE_BY_PARM= "SELECT * FROM tbl_store_info WHERE account_id=? and id=? ";
public List<Store> getStoreByParm(Integer accountid, Integer id, String storenum, Integer project_state,
String salesperson, Integer implement_type, Date startdate, Date enddate) {
StringBuffer getPlazaSb = new StringBuffer(SELECT_STORE_BY_PARM);
if(!StrUtil.isBlank(storenum)){
getPlazaSb.append(" and storenum='"+storenum+"'");
}
if(project_state!=null){
getPlazaSb.append(" and project_state="+project_state+"");
}
if(!StrUtil.isBlank(salesperson)){
getPlazaSb.append(" and salesperson='"+salesperson+"'");
}
if(implement_type!=null){
getPlazaSb.append(" and implement_type="+implement_type+"");
}
if(startdate!=null){
getPlazaSb.append(" and orderdate>='"+startdate+"'");
}
if(enddate!=null){
getPlazaSb.append(" and orderdate>='"+enddate+"'");
}
return jdbcTemplate.query(getPlazaSb.toString(),new Object[]{accountid,id}, new BeanPropertyRowMapper<Store>(Store.class));
}
private static final String SELECT_STORE_BY_ID= "select s.*,a.consignee_name,a.consignee_phone,a.consignee_address,a.contract_name,a.contract_phone,a.contract_address,a.invoice_name,a.invoice_phone,a.invoice_address,a.invoice_info \n" +
"from tbl_store_info as s left join tbl_address_info as a on a.store_id=s.id and s.id=?";
public StoreVO getStoreByID(Integer id) {
List<StoreVO> stores= jdbcTemplate.query(SELECT_STORE_BY_ID,new Object[]{id}, new BeanPropertyRowMapper<StoreVO>(StoreVO.class));
if(stores.size()>0){
return stores.get(0);
}else{
return null;
}
}
private static final String SAVE_ADDRESS= "INSERT INTO tbl_address_info(\n" +
" store_id, consignee_name, consignee_phone, consignee_address, \n" +
" contract_name, contract_phone, contract_address, invoice_name, \n" +
" invoice_phone, invoice_address, invoice_info)\n" +
" VALUES (?, ?, ?, ?, \n" +
" ?, ?, ?, ?, \n" +
" ?, ?, ?);";
public Integer addAddress(StoreVO address) {
return jdbcTemplate.update(SAVE_ADDRESS,
new Object[]{
address.getId(),address.getConsignee_name(),address.getConsignee_phone(),address.getConsignee_address(),
address.getContract_name(),address.getContract_phone(),address.getContract_address(),address.getInvoice_name(),
address.getInvoice_phone(),address.getInvoice_address(),address.getInvoice_info()
});
}
private static final String UPDATE_ADDRESS= "UPDATE tbl_address_info\n" +
" SET consignee_name=?, consignee_phone=?, consignee_address=?, \n" +
" contract_name=?, contract_phone=?, contract_address=?, invoice_name=?, \n" +
" invoice_phone=?, invoice_address=?, invoice_info=?\n" +
" WHERE store_id=?;";
public Integer updateAddress(StoreVO address) {
return jdbcTemplate.update(UPDATE_ADDRESS,
new Object[]{
address.getConsignee_name(),address.getConsignee_phone(),address.getConsignee_address(),
address.getContract_name(),address.getContract_phone(),address.getContract_address(),address.getInvoice_name(),
address.getInvoice_phone(),address.getInvoice_address(),address.getInvoice_info(),address.getId()
});
}
}