/**
*
* 获取保理的金额,开票金额，税额，抵扣价税合计
*/
@Rest(method = RestMethod.POST)
@ActionKey("/api/qhyf/abs/BaoLi/getBaoLiMoney")
public void getBaoLiMoney() {
//返回json数据
JSONObject returnJson = new JSONObject();
JSONArray data = new JSONArray();
//财务科目key值
String financialSubjectsKey = this.getPara("financialSubjectsKey");
try {
if(!fsKey(financialSubjectsKey)){
returnJson.put("errcode","2");
returnJson.put("errmsg","key值验证不通过");
// 返回错误结果
this.renderJson(returnJson);
}else{
returnJson.put("errcode","0");
returnJson.put("errmsg","");
List<Object> paras = new ArrayList<Object>();
String where = "";
// 核心企业ID
String coreEnterpriseId = this.getPara("coreEnterpriseId");
if (StringUtils.notBlank(coreEnterpriseId)) {
if(StringUtils.isBlank(where)){
where = " Where x.core_enterprise_id = ? ";
}else{
where += " and x.core_enterprise_id = ? ";
}
paras.add(coreEnterpriseId);
}
// 年份
String years = this.getPara("years");
if (StringUtils.notBlank(years)) {
if(StringUtils.isBlank(where)){
where = " Where x.years = ? ";
}else{
where += " and x.years = ? ";
}
paras.add(years);
}
// 月份
String months = this.getPara("months");
if (StringUtils.notBlank(months)) {
if(StringUtils.isBlank(where)){
where = " Where x.months = ? ";
}else{
where += " and x.months = ? ";
}
paras.add(months);
}
String select =""+
"SELECT " +
" x.core_enterprise_id, " +
" x.years, " +
" x.months, " +
" SUM( x.invoice_amount ) as invoice_amount , " +
" SUM( x.invoice_tax_amount ) as invoice_tax_amount , " +
" SUM( x.total_amount ) as total_amount " +
"FROM " +
" ( " +
" SELECT " +
" a.core_enterprise_id, " +
" a.account_receivable, " +
" b.pay_id, " +
" b.invoice_amount, " +
" b.invoice_tax_amount, " +
" b.total_amount, " +
" b.invoice_date, " +
" substring( DATE_FORMAT( b.invoice_date, '%Y%m' ), 5 ) months, " +
" DATE_FORMAT( b.invoice_date, '%Y' ) years " +
" FROM " +
" biz_pay_billing_info b, " +
" biz_pay_comfirm_info a " +
" WHERE " +
" b.sys_status = 1 " +
" AND b.pay_id = a.uuid " +
" AND a.sys_status = 1 " +
" ) x " +
where +
"GROUP BY " +
" x.core_enterprise_id, " +
" x.months, " +
" x.years " ;
List<Record> aList = Db.find(select, paras);
if (aList != null ) {
//遍历上面的结果集
for (Record record : aList) {
//获取年
String yers = record.getStr("years");
//获取月
String mons = record.getStr("months");
//获取核心企业
String coreEnterId = record.getStr("coreEnterpriseId");
String getSelectSql="SELECT " +
" SUM(z.account_receivable) AS account_receivable " +
"FROM " +
" ( " +
" SELECT DISTINCT " +
" y.uuid, " +
" y.account_receivable " +
" FROM " +
" ( " +
" SELECT " +
" b.pay_id, " +
" b.invoice_date, " +
" substring( " +
" DATE_FORMAT(b.invoice_date, '%Y%m'), " +
" 5 " +
" ) months, " +
" DATE_FORMAT(b.invoice_date, '%Y') years " +
" FROM " +
" biz_pay_billing_info b " +
" WHERE " +
" b.sys_status = 1 " +
" ) x, " +
" biz_pay_comfirm_info y " +
" WHERE " +
" x.pay_id = y.uuid " +
" AND y.sys_status = 1 " +
" AND y.core_enterprise_id = ? " +
" AND x.years = ? " +
" AND x.months = ? " +
" ORDER BY " +
" y.pay_id " +
" ) z" ;
Record retc = Db.findFirst(getSelectSql,coreEnterId,yers,mons);
record.set("accountReceivable", retc.get("accountReceivable"));
}
//遍历返回的结果集，放入json 里面
for (Record record2 : aList) {
JSONObject json = new JSONObject();
//保理金额
json.put("accountReceivable", record2.get("accountReceivable"));
//开票金额invoice_amount
json.put("invoiceAmount", record2.get("invoiceAmount"));
//税额invoice_tax_amount
json.put("invoiceTaxAmount", record2.get("invoiceTaxAmount"));
//抵扣价税合计total_amount
json.put("totalAmount", record2.get("totalAmount"));
data.add(json);
}
}
returnJson.put("data", data);
//返回正确结果
this.renderJson(returnJson);
}
} catch (Exception e) {
logger.error(AlertUtils.getErrMsgWithCode("CC004E003"),e);
returnJson.put("errcode","4");
returnJson.put("errmsg",AlertUtils.getErrMsgWithCode("CC004E003"));
returnJson.put("data", data);
//返回错误结果
this.renderJson(returnJson);
}
}
