采购订单支付金额解决方法

（postgres数据库）

采购订单表基本结构: inbound_order

 

 database_id bigint NOT NULL, //数据库主键
 order_number character varying(255),  //订单号
 total_amount double precision,  //订单总价格
 paid_amount double precision,  //已经支付的金额

 

采购订单明细表基本结构: inbound_order_item

 

database_id bigint NOT NULL,  //数据库主键
order_number character varying(255),  //订单号
item_number integer,   //订单明细项编号
refer_order_number character varying(255),  //引用订单号
refer_item_number integer,  //引用订单明细项编号

 

 

采购支付表基本结构: inbound_payment
 database_id bigint NOT NULL,  //数据库主键
 order_number character varying(255),  //订单号

采购支付明细表基本结构: inbound_payment_item

  database_id bigint NOT NULL, //数据库主键
  order_number character varying(255),  //订单号
  item_number integer,  //订单明细项
  refer_order_number character varying(255),  //引用订单号
  sub_total_amt double precision,  //支付总金额
  refer_order_amount double precision,  //引用订单总金额
  refer_order_paid double precision,   //引用订单支付金额
  refer_order_returned double precision,  //引用订单退款金额
  refer_order_unpaid double precision,  //引用订单未支付金额

 

 

 

在程序中通过查询数据库视图，此视图结构如下：: view_payment_info

 

CREATE OR REPLACE VIEW view_payment_info AS
 SELECT v.order_number, v.class_name, v.paid_amount, v.paid_amount_real
   FROM view_temp_payment_info v
  WHERE v.paid_amount <> v.paid_amount_real;

 

 

通过比较paid_amount(采购订单总金额)和paid_amount_real(支付订单明细总金额)，要是此视图中存在记录，则存在对应采购订单的支付记录，然后通过修改class_name(inboud_order)设置paid_amount=paid_amount_real即可。。