| | |
|---|
| | | * @throws BaseException |
|---|
| | | */ |
|---|
| | | public DataTableEntity paymentCollectionData()throws BaseException{ |
|---|
| | | return baseDao.listTable("SELECT b.project_name,g.customer_name,a.contract_name,c.clause_content,d.invoice_num,e.apply_time,d.invoice_amount,f.collection_amount,f.collection_time FROM \n" + |
|---|
| | | "product_project_contract_info a \n" + |
|---|
| | | "LEFT JOIN product_project_business b on a.project_uuid=b.uuid\n" + |
|---|
| | | "LEFT JOIN product_project_contract_info_sub c on a.uuid =c.contract_info_uuid\n" + |
|---|
| | | "LEFT JOIN product_project_contract_invoice_info d on c.uuid=d.contract_terms\n" + |
|---|
| | | "LEFT JOIN product_project_contract_invoice e on e.uuid=d.contract_invoice_uuid\n" + |
|---|
| | | "LEFT JOIN product_project_contract_payment_collection f on d.invoice_num=f.invoice_num\n" + |
|---|
| | | "LEFT JOIN product_project_customer g on b.customer_name=g.uuid",new String[]{}); |
|---|
| | | StringBuilder sbSql = new StringBuilder(); |
|---|
| | | sbSql.append("SELECT A.*,(A.contract_amount - A.amount_collected) AS unpaid_amount \n"); |
|---|
| | | sbSql.append("FROM ( \n"); |
|---|
| | | sbSql.append(" SELECT \n"); |
|---|
| | | sbSql.append(" b.project_name,g.customer_name,a.contract_name,a.contract_amount, \n"); |
|---|
| | | sbSql.append(" (SELECT IFNULL(SUM(amount_collected), 0) FROM product_project_contract_payment WHERE contract_name = A.uuid)AS amount_collected, \n"); |
|---|
| | | sbSql.append(" c.clause_content,d.invoice_num,e.apply_time,d.invoice_amount,f.collection_amount,f.collection_time \n"); |
|---|
| | | sbSql.append(" FROM product_project_contract_info a \n"); |
|---|
| | | sbSql.append(" LEFT JOIN product_project_business b on a.project_uuid=b.uuid \n"); |
|---|
| | | sbSql.append(" LEFT JOIN product_project_contract_info_sub c on a.uuid =c.contract_info_uuid \n"); |
|---|
| | | sbSql.append(" LEFT JOIN product_project_contract_invoice_info d on c.uuid=d.contract_terms \n"); |
|---|
| | | sbSql.append(" LEFT JOIN product_project_contract_invoice e on e.uuid=d.contract_invoice_uuid \n"); |
|---|
| | | sbSql.append(" LEFT JOIN product_project_contract_payment_collection f on d.invoice_num=f.invoice_num \n"); |
|---|
| | | sbSql.append(" LEFT JOIN product_project_customer g on b.customer_name=g.uuid \n"); |
|---|
| | | sbSql.append(")A \n"); |
|---|
| | | return baseDao.listTable(sbSql.toString(),new String[]{}); |
|---|
| | | } |
|---|
| | | |
|---|
| | | } |
|---|
