@Before(Tx.class)
public void insert(List<Map<String, Object>> list, String id) {
Db.update("delete from " + tableName + " where id=?", id);
Object[][] paras = new Object[list.size()][12];
int i = 0;
for (Map<String, Object> a : list) {
paras[i][0] = id;
paras[i][1] = a.get("product_id");
if (StringUtils.isNotEmpty((String) a.get("purchase_price")))
paras[i][2] = new BigDecimal((String) a.get("purchase_price"));
if (StringUtils.isNotEmpty((String) a.get("sale_price")))
paras[i][3] = new BigDecimal((String) a.get("sale_price"));
if (StringUtils.isNotEmpty((String) a.get("amount")))
paras[i][4] = Float.parseFloat((String) a.get("amount"));
if (StringUtils.isNotEmpty((String) a.get("zkl")))
paras[i][5] = Float.parseFloat((String) a.get("zkl"));
if (StringUtils.isNotEmpty((String) a.get("zhamt")))
paras[i][6] = new BigDecimal((String) a.get("zhamt"));
if (StringUtils.isNotEmpty((String) a.get("amt")))
paras[i][7] = new BigDecimal((String) a.get("amt"));
paras[i][8] = a.get("description");
if (StringUtils.isNotEmpty((String) a.get("quoted_price")))
paras[i][9] = new BigDecimal((String) a.get("quoted_price"));
if (StringUtils.isNotEmpty((String) a.get("tax_rate")))
paras[i][10] = Float.parseFloat((String) a.get("tax_rate"));
if (StringUtils.isNotEmpty((String) a.get("tax")))
paras[i][11] = new BigDecimal((String) a.get("tax"));
i++;
}
Db.batch(
"INSERT INTO scm_order_product(id,product_id,purchase_price,sale_price,amount,zkl,zhamt,amt,description,quoted_price,tax_rate,tax)VALUES (?,?,?,?,?,?,?,?,?,?,?,?);",
paras,
list.size());
}
public static JSONObject headerEdit(String username, File header) {
JSONObject items = new JSONObject();
boolean rs = false;
if (!username.equals("")) {
Record user =
Db.findFirst("SELECT * FROM b_user WHERE username = ? AND status = 1", username);
if (user != null) {
System.out.println(header.getName());
String fileName =
"head_"
+ username
+ String.valueOf(System.currentTimeMillis())
+ "."
+ header.getName().split("\\.")[1];
String QNToken = qiNiuKit.getUpToken();
boolean qnRs = new qiNiuKit().uploadFile(header, fileName, QNToken);
if (qnRs) {
user.set("head", qiNiuKit.headSave(fileName));
rs = Db.update("b_user", user);
if (rs) {
user =
Db.findFirst(
"SELECT bu.*,fur.content AS roleValue,fus.content AS sexValue FROM b_user AS bu LEFT JOIN f_user_role AS fur ON bu.role = fur.id LEFT JOIN f_user_sex AS fus ON bu.sex = fus.id WHERE bu.username = ? AND bu.status = 1",
username);
items.put("user", user.toJson());
}
}
}
}
items.put("rs", rs);
return items;
}
/** 提交 */
@Before(Tx.class)
public String[] submit(String id, String company_id, String uid, String now, String nowtime) {
String[] billsnList = new String[2];
Db.update(
"update "
+ tableName
+ " set submit_status=1 where id=? and company_id=? and (head_id=? or creater_id=?) ",
id,
company_id,
uid,
uid);
StockAllot po = this.findById(id, company_id);
String to_depot_id = po.getStr("to_depot_id");
String out_depot_id = po.getStr("out_depot_id");
billsnList[0] = SnCreater.dao.create("OUTSTOCK", company_id);
billsnList[1] = SnCreater.dao.create("INSTOCK", company_id);
// 生成出入库
StorageBill sb = new StorageBill();
// 出库单
sb.set("billsn", billsnList[0]);
sb.set("type", 2); // 2调拨出库 6调拨入库
sb.set("depot_id", out_depot_id);
sb.set("head_id", po.getStr("head_id"));
sb.set("creater_id", uid);
sb.set("create_datetime", nowtime);
sb.set("bill_date", now);
sb.set("company_id", company_id);
sb.save();
String bill_id = sb.getStr("id");
Db.update(
"insert into "
+ StorageBillList.tableName
+ " (id,product_id,amount) select '"
+ bill_id
+ "',product_id,amount from "
+ StockAllotList.tableName
+ " a where a.id=?",
id);
// 入库单
sb.set("id", null);
sb.set("type", 6); // 6调拨入库
sb.set("depot_id", to_depot_id);
sb.set("billsn", billsnList[1]);
sb.save();
bill_id = sb.getStr("id");
Db.update(
"insert into "
+ StorageBillList.tableName
+ " (id,product_id,amount) select '"
+ bill_id
+ "',product_id,amount from "
+ StockAllotList.tableName
+ " a where a.id=?",
id);
return billsnList;
}
public Record init(String username) {
Record user = Db.findFirst("SELECT * FROM b_users WHERE username = ? AND status = 0");
if (user == null) {
// user初始化
user = new Record();
user.set("username", username).set("mobile", username).set("status", 0);
Db.save("b_user", user);
}
return user;
}
/**
* 保存节假日信息和对应的交易时间 事物处理
*
* @param message
* @param date
* @return
*/
@Before(Tx.class)
public void saveHolidayTime() {
TradeTime tradeTime =
new TradeTime()
.set("id", "20160101")
.set("startTime", "00:01:02")
.set("endTime", "16:00:00");
Db.save("tradeTime", tradeTime.toRecord());
log.info("save:" + tradeTime.toJson());
HolidayList holidayList = new HolidayList();
Db.save("", holidayList.toRecord());
log.info("save:" + holidayList.toJson());
return;
}
public Page<Record> getUserPage(int pageNumber, int pageSize) {
return Db.paginate(
pageNumber,
pageSize,
SqlXmlKit.sql("user.findUserPageBySelect") + blank,
SqlXmlKit.sql("user.findUserPageByFrom"));
}
/**
* 获取评价列表 参数 userid 用户ID
*
* <p>返回值 true or false
*/
public void getPjList() {
Map<String, Object> resultMap = new HashMap<String, Object>();
String uid = this.getPara("uid");
String roleId = this.getPara("roleId");
if (StringUtil.isNull(uid) || StringUtil.isNull(roleId)) {
formatInvalidParamResponse(resultMap);
} else {
StringBuffer sb = new StringBuffer("select * from (select ");
sb.append(" aa.id,aa.user_id,aa.mer_id,aa.order_id, aa.qdr_id, aa.datetime, ");
sb.append(" aa.type,aa.content, aa.dync_mah, aa.dync_ser, aa.dync_spd, ");
sb.append(" aa.dync_val, aa.private AS privates,");
sb.append(" su.mc, am.mc mmc, ao.sn, aai.img_url from ");
if (CommonStaticData.USER_TYPE_MERCHANT.equals(roleId)) { // 商家
sb.append("(select * from ab_appraise where mer_id = '" + uid + "') aa ");
} else if (CommonStaticData.USER_TYPE_SERVICE.equals(roleId)) { // 司机
sb.append("(select * from ab_appraise where qdr_id = '" + uid + "') aa ");
} else { // 用户
sb.append("(select * from ab_appraise where user_id = '" + uid + "') aa ");
}
sb.append("left join sys_user su on aa.user_id = su.id ");
sb.append("left join ab_merchant am on aa.mer_id = am.id ");
sb.append("left join ab_order ao on aa.order_id = ao.id ");
sb.append("LEFT JOIN ab_appraise_img aai ON aai.app_id = ao.id ");
sb.append(") t where 1=1 ");
List<Record> apps = Db.find(sb.toString());
resultMap.put("plList", apps);
resultMap.put("result", RESULT_SUCCESS);
resultMap.put("msg", "评价列表获取成功");
}
renderJson(resultMap);
}
/** 获取订单对应的维权列表 */
public void acc() {
Map<String, Object> resultMap = new HashMap<String, Object>();
String userid = this.getPara("uid");
String orderId = this.getPara("orderid");
if (StringUtil.isNull(userid) || StringUtil.isNull(orderId)) {
formatInvalidParamResponse(resultMap);
} else {
AbOrder order = AbOrder.dao.findById(orderId);
if (order == null) {
AbTcExpressOrder tc = AbTcExpressOrder.dao.findById(orderId);
if (tc != null) {
order = AbOrder.dao.findFirst("select * from ab_order where sn = ?", tc.getStr("sn"));
if (order != null) {
orderId = order.getStr("id");
}
}
}
Record r =
Db.findFirst(
"SELECT aocb.*,tu.mc FROM ab_order_chargeback AS aocb,sys_user AS tu WHERE aocb.apply_id = tu.id AND aocb.order_id=?",
new Object[] {orderId});
resultMap.put("result", RESULT_SUCCESS);
resultMap.put("msg", "查询成功");
resultMap.put("data", r);
}
renderJson(resultMap);
}
// 未审核
@Before(Tx.class)
public void unreview() throws Exception {
Long id = getParaToLong(0, 0L);
try {
if (id != null) {
Ckjhcheck r = Ckjhcheck.dao.findById(id);
/*
* if (r.getInt("SettleTypeFlag") == 2) { toDwzJson(300,
* "已清货不能反审核！", navTabId); return; }
*/
if (r.getInt("CheckFlag") != null && r.getInt("CheckFlag") != 0) {
// 登录人即审核者
Record m = getCurrentUser();
r.set("CheckFlag", 0);
r.set("checkman", m.getStr("usr_name"));
r.set("CheckDate", new Date());
r.update();
// 审核后更新库存表及供应商表应付款
CkjhDbProc oracleDbK =
new CkjhDbProc(
r.getStr("StoreCode"), r.getStr("orderCode"), r.getStr("SupplierCode"), 2);
Db.execute(oracleDbK);
}
toDwzJson(200, "反审核通过！", navTabId);
}
} catch (Exception e) {
toDwzJson(300, "删除失败！");
throw e;
}
}
/**
* 根据故障类型id，获得相应运维人员信息
*
* @param paras
* @return
*/
public List<Record> getOperatorsList(Object... paras) {
return Db.find(
SqlXmlKit.sql("user.findOperatorsBySelect")
+ blank
+ SqlXmlKit.sql("user.findOperatorsByFrom"),
paras);
}
public int updateByEmail(String email, String newPass) {
return Db.update(
"update user u set u.password = ?, u.token = ? where u.email = ?",
newPass,
StrUtil.getUUID(),
email);
}
public void list() {
Map<String, Object> resultMap = new HashMap<String, Object>();
String userid = this.getPara("uid");
Integer pi = this.getParaToInt("pi");
Integer ps = this.getParaToInt("ps");
if (!checkParam(pi)) {
pi = 1;
}
if (!checkParam(ps)) {
ps = 10;
}
if (StringUtil.isNull(userid)) {
formatInvalidParamResponse(resultMap);
} else {
List<Record> list =
Db.find(
"SELECT a.*,b.sn as sn FROM ab_order_chargeback a join ab_order b where a.order_id=b.id AND a.xdr_id=? order by apply_time desc limit ?,?",
userid,
getStart(pi, ps),
ps);
resultMap.put("result", RESULT_SUCCESS);
resultMap.put("msg", "查询成功");
resultMap.put("data", list);
}
renderJson(resultMap);
}
/**
* 根据管理员ID去查询城市ID集合
*
* @param id
* @return
*/
public static String getCityByAdmin(String id) {
String result = "";
String SQL = "";
if ("admin".equals(id)) {
SQL = "select id from ab_cityarea where id != 'ROOT'";
} else {
List<AbCityarea> tempList =
AbCityarea.dao.find("select * from ab_cityarea where user_id= '" + id + "' order by ccm");
if (null != tempList && !tempList.isEmpty()) {
for (AbCityarea temp : tempList) {
if (SQL.length() > 0) {
SQL += " union ";
}
SQL += "select id from ab_cityarea where id like '" + temp.getStr("id") + "%'";
}
} else {
SQL = "select id from ab_cityarea where id ='111111111111111111'";
}
}
List<String> resultList = Db.query(SQL);
if (null != resultList && !resultList.isEmpty()) {
for (String str : resultList) {
result += str + ",";
}
result = result.substring(0, result.length() - 1);
} else {
result = "1234567890";
}
return result;
}
public List<Record> findOperatesByUserId(String where, String orderby, Object... paras) {
return Db.find(
getSql("order.findOfferQueryBySelect")
+ getSql("order.findOfferQueryByFrom")
+ getWhere(where)
+ orderby,
paras);
}
/**
* @描述 查询Report 详细内容
*
* @param where
* @param paras
* @return
*/
public Record findReportById(String where, Object... paras) {
return Db.findFirst(
getSql("order.findOrderInfoBySelect")
+ blank
+ getSql("order.findOrderInfoByFrom")
+ getWhere(where),
paras);
}
/**
* @描述 查询order 管理员
*
* @param where
* @param paras
* @return
*/
public Record query(String where, Object... paras) {
return Db.findFirst(
getSql("order.findOfferQueryBySelect")
+ getSql("order.findOfferQueryByFrom")
+ getWhere(where),
paras);
}
public void getProjectInfo() {
Map<String, String> mapParam = ParamUtil.convertStringParamToMapParam(getPara());
String projectId = mapParam.get("projectId");
Record record = Db.findFirst("SELECT * FROM T_PROJECT T WHERE T.ID = ?", projectId);
record.set("PROJECT_TYPE", getRealType(record.getStr("PROJECT_TYPE")));
setAttr("project", record);
renderJsp("/mainPage/gcgk.jsp");
}
/**
* 根据条件获取Order。
*
* @param where
* @param paras
* @return
*/
public Page<Record> findExceptionOrders(
int pageNumber, int pageSize, String where, Object... paras) {
return Db.paginate(
pageNumber,
pageSize,
getSql("order.findOrderInfoBySelect"),
getSql("order.findOrderInfoByFrom") + getWhere(where),
paras);
}
/**
* @描述 查询Operates 分页
*
* @param pageNumber
* @param pageSize
* @param where
* @param orderby
* @param paras
* @return
*/
public Page<Record> findOperatesByUserId(
int pageNumber, int pageSize, String where, String orderby, Object... paras) {
return Db.paginate(
pageNumber,
pageSize,
getSql("order.findOfferQueryBySelect"),
getSql("order.findOfferQueryByFrom") + getWhere(where) + orderby,
paras);
}
/** get all data_bases in db_user's privilege */
public List<Record> getInDBUserPri(int dbuid, int spaceid) {
StringBuilder sb =
new StringBuilder("select * from" + TABLE + " as A")
.append(" join " + UserDbPriL.TABLE + " as B on")
.append("A." + ID + "=B." + UserDbPriL.DBID)
.append("where B." + UserDbPriL.USERID + "=? and A." + SPACEID + "=?");
List<Record> list = Db.use("local").find(sb.toString(), dbuid, spaceid);
return list;
}
/*退单处理显示*/
public void showcb() {
SysUser abuser = this.getSessionAttr("abuser");
if (abuser == null || abuser.get("id") == null) {
redirect("/ab/login");
} else {
String cbid = this.getPara("id");
List<Record> list = Db.find(CommonSQL.getChargebackById(cbid));
CommonProcess.createChargebackImageFile(list);
// 查询补充说明
List<Record> items = Db.find(CommonSQL.getChargebackItemByCbid(cbid));
CommonProcess.createImageFile(items);
this.setAttr("items", items);
this.setAttr("role", abuser.getStr(SysUser.ROLE_ID));
this.setAttr("cb", list.get(0));
this.render("/ab/user/listcbdetail.html");
}
}
public static JSONObject login(String username, String validateNum, String deviceId) {
JSONObject items = new JSONObject();
boolean rs = false;
if (!username.equals("") && !validateNum.equals("") && !deviceId.equals("")) {
// status 0=初始化 1=正常
Record user =
Db.findFirst(
"SELECT bu.*,fur.content AS roleValue,fus.content AS sexValue FROM b_user AS bu LEFT JOIN f_user_role AS fur ON bu.role = fur.id LEFT JOIN f_user_sex AS fus ON bu.sex = fus.id WHERE bu.username = ? AND bu.validateNum = ? AND bu.status < 2",
username,
validateNum);
Long nowTime = System.currentTimeMillis();
// validate时会初始化b_user 这里null 一定有误
if (user != null) {
if ((nowTime - user.getTimestamp("validateTime").getTime()) < 300000) {
Db.update(
"UPDATE b_user SET validateNum = ? , status = 1 WHERE username = ?",
"----",
user.getStr("username"));
items.put("user", user.toJson());
String agentToken = Token.add(username, deviceId);
items.put("agentToken", agentToken);
Record auth =
Db.findFirst(
"SELECT * FROM b_auth WHERE username = ? AND role = ?",
username,
user.getInt("role"));
if (auth != null) {
items.put("auth", auth.toJson());
} else {
auth = new Record();
auth.set("username", username).set("role", user.getInt("role")).set("status", 0);
boolean authRs = Db.save("b_auth", auth);
if (authRs) {
items.put("auth", auth.toJson());
}
}
rs = true;
}
}
}
items.put("rs", rs);
return items;
}
/** get all data_bases by db_user's privilege page-form */
public Page<Record> getByDBUserPri(int dbuid, int spaceid, int number, int size) {
StringBuilder sb =
new StringBuilder("from " + TABLE + " as A")
.append(" join " + UserDbPriL.TABLE + " as B on ")
.append("A." + ID + "=B." + UserDbPriL.DBID)
.append(" where B." + UserDbPriL.USERID + "=? and A." + SPACEID + "=?");
Page<Record> list =
Db.use(DSBox.LOCAL).paginate(number, size, "select * ", sb.toString(), dbuid, spaceid);
return list;
}
/** 直接删除 未提交的时间 */
@Before(Tx.class)
public void del(String id, String company_id) {
if (StringUtils.isNotEmpty(id)) {
String[] ids = id.split(",");
StringBuffer ids_ = new StringBuffer();
List<String> parame = new ArrayList<String>();
for (String id_ : ids) {
ids_.append("?,");
parame.add(id_);
}
ids_.append("'-'");
Db.update(
"delete from scm_stock_allot_list where id in (" + ids_.toString() + ")",
parame.toArray());
parame.add(company_id);
Db.update(
"delete from " + tableName + " where id in (" + ids_.toString() + ") and company_id=? ",
parame.toArray());
}
}
/**
* 删除
*
* @param ids
* @return
*/
public boolean delete(String ids) {
Record record =
Db.findFirst(
"select count(*) as counts from pt_department where parentdepartmentids=?", ids);
Long counts = record.getNumber("counts").longValue();
if (counts > 1) {
return false;
}
Department.dao.deleteById(ids);
return true;
}
// 客单价
public List<Record> getPricePer(String startTime, String endTime) {
return Db.use("main2")
.find(
"select ROUND(SUM(total) / count(uid),2) data1,province,city from sl_rpt_life_cycle_users_consume \n"
+ "\t\twhere FROM_UNIXTIME(order_addtime,'%Y%m%d') >= '"
+ startTime
+ "' and FROM_UNIXTIME(order_addtime,'%Y%m%d') <= '"
+ endTime
+ "' and province <>''\n"
+ "\t\t GROUP BY province ORDER BY ROUND(SUM(total) / count(uid),2) desc limit 5;");
}
private void select() {
StringBuffer whee = new StringBuffer();
List<Object> param = new ArrayList<Object>();
String startTime = getPara("startTime");
if (startTime != null && !"".equals(startTime.trim())) {
whee.append(" and UNIX_TIMESTAMP(p.OrderDate) >= UNIX_TIMESTAMP(?)");
param.add(startTime);
}
setAttr("startTime", startTime);
String endTime = getPara("endTime");
if (endTime != null && !"".equals(endTime.trim())) {
whee.append(" and UNIX_TIMESTAMP(p.OrderDate) <= UNIX_TIMESTAMP(?)");
param.add(endTime);
}
setAttr("endTime", endTime);
String supplierCode = getPara("supplier.supplierCode");
if (supplierCodePara != null) supplierCode = supplierCodePara;
if (supplierCode != null && !"".equals(supplierCode.trim())) {
whee.append(" and p.SupplierCode = ?");
param.add(Long.parseLong(supplierCode));
}
setAttr("supplierCode", supplierCode);
setAttr("supplierName", getPara("supplier.supplierName"));
String storeCd = getPara("store.StoreCode");
if (storeCd != null && !"".equals(storeCd.trim())) {
whee.append(" and p.StoreCode = ?");
param.add(Long.parseLong(storeCd));
}
setAttr("StoreCode", storeCd);
setAttr("StoreName", getPara("store.StoreName"));
String sql = " from ckjhcheck p left join jbsupplier b on p.SupplierCode = b.supplierCode ";
sql += " left join jbstore c on p.StoreCode = c.StoreCode ";
sql += " left join employee d on d.usr_no = p.EmpCode ";
sql += " left join partment e on e.deptCode = p.deptCode ";
sql += " left join types t on t.id=p.inoutTypeNo and t.function='入库类型'";
sql += " where 1=1 ";
setAttr(
"page",
Db.paginate(
getParaToInt("pageNum", 1),
getParaToInt("numPerPage", 10),
"select p.id,p.OrderCode 订单号, p.OrderDate 进货日期,p.remark 备注, t.name 入库类型,p.BillOrderNo 采购单号,p.CKAmount 入库金额, p.payAmount 已付金额,p.checkflag 审核, b.supplierName 供应商,c.StoreName 订货仓库,d.usr_name 业务员, e.`name` 部门 ",
sql + whee.toString(),
param.toArray()));
setAttr(
"collist",
new String[] {
"订单号", "进货日期", "供应商", "业务员", "部门", "收货日期", "订货仓库", "入库类型", "采购单号", "入库金额", "已付金额", "审核",
"备注"
});
}
/** 客户消费top5分析* * */
public List<Record> getTop5(String startTime, String endTime) {
return Db.use("main2")
.find(
"select COUNT(t.type_name) as 'COUNT(type_name)',t.type_name as type_name\n"
+ "\tfrom sl_rpt_life_cycle_users_consume t\n"
+ "\twhere FROM_UNIXTIME(t.order_addtime, '%Y%m%d') >= '"
+ startTime
+ "'\n"
+ "\tand FROM_UNIXTIME(t.order_addtime, '%Y%m%d') <= '"
+ endTime
+ "'\n"
+ "\tGROUP BY t.type_name ORDER BY COUNT(t.type_name) DESC LIMIT 0,5;");
}
public void del() {
Long id = getParaToLong(0, 0L);
try {
if (id != null) {
Ckjhcheck r = Ckjhcheck.dao.findById(id);
Ckjhcheck.dao.deleteById(id);
Db.update("delete from ckjhcheckdetail where OrderCode=?", r.getStr("OrderCode"));
}
toDwzJson(200, "删除成功！", navTabId);
} catch (Exception e) {
toDwzJson(300, "删除失败！");
}
}
public void updateOrderInfo(String orderId, String productId, String num, String price) {
Db.update(
"UPDATE "
+ tableName
+ " SET sale_price = '"
+ price
+ "', amount = '"
+ num
+ "' WHERE id = '"
+ orderId
+ "' and product_id = '"
+ productId
+ "'");
}