程序设计报告

CREATE OR REPLACE FUNCTION "public"."del_goods_func"()
  RETURNS "pg_catalog"."trigger" AS $BODY$  
    BEGIN
				IF NEW.is_del = TRUE
				THEN
					UPDATE erp_purchase_details SET is_del = TRUE where purchase_details_goods = NEW.goods_id;
				END IF;
				RETURN NEW;
    END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

CREATE TRIGGER "del_goods_trigger" AFTER UPDATE OF "is_del" ON "public"."erp_goods"
FOR EACH ROW
EXECUTE PROCEDURE "public"."del_goods_func"();

CREATE OR REPLACE FUNCTION "public"."del_staff_func"()
  RETURNS "pg_catalog"."trigger" AS $BODY$  
    BEGIN
				IF NEW.is_del = TRUE
				THEN
					UPDATE erp_purchase_list SET is_del = TRUE where purchase_list_staff = NEW.staff_id;
				END IF;
				RETURN NEW;
    END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

CREATE TRIGGER "del_staff_trigger" AFTER UPDATE OF "is_del" ON "public"."erp_staff"
FOR EACH ROW
EXECUTE PROCEDURE "public"."del_staff_func"();

CREATE OR REPLACE FUNCTION "public"."del_supplier_func"()
  RETURNS "pg_catalog"."trigger" AS $BODY$  
    BEGIN
				IF NEW.is_del = TRUE
				THEN
					UPDATE erp_goods SET is_del = TRUE where goods_supplier = NEW.supplier_id;
				END IF;
				RETURN NEW;
    END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

CREATE TRIGGER "del_supplier_trigger" AFTER UPDATE OF "is_del" ON "public"."erp_supplier"
FOR EACH ROW
EXECUTE PROCEDURE "public"."del_supplier_func"();

CREATE OR REPLACE FUNCTION "public"."del_purchase_list_func"()
  RETURNS "pg_catalog"."trigger" AS $BODY$  
    BEGIN
				IF NEW.is_del = TRUE
				THEN
					UPDATE erp_purchase_details SET is_del = TRUE where purchase_details_list = NEW.purchase_list_id;
				END IF;
				RETURN NEW;
    END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

CREATE TRIGGER "del_purchase_list_trigger" AFTER UPDATE OF "is_del" ON "public"."erp_purchase_list"
FOR EACH ROW
EXECUTE PROCEDURE "public"."del_purchase_list_func"();

CREATE OR REPLACE FUNCTION "public"."insert_details_func"()
  RETURNS "pg_catalog"."trigger" AS $BODY$  
    BEGIN  
        UPDATE erp_purchase_list SET purchase_list_number = purchase_list_number+NEW.purchase_details_number where purchase_list_id = NEW.purchase_details_list;
				UPDATE erp_purchase_list SET purchase_list_price = purchase_list_price+NEW.purchase_details_price where purchase_list_id = NEW.purchase_details_list;
				RETURN NEW;
    END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

CREATE TRIGGER "insert_detail_trigger" AFTER INSERT ON "public"."erp_purchase_details"
FOR EACH ROW
EXECUTE PROCEDURE "public"."insert_details_func"();

CREATE OR REPLACE FUNCTION "public"."update_details_func"()
  RETURNS "pg_catalog"."trigger" AS $BODY$  
    BEGIN  
				IF NEW.is_del = TRUE
				THEN
					UPDATE erp_purchase_list SET purchase_list_number = purchase_list_number-OLD.purchase_details_number where purchase_list_id = NEW.purchase_details_list;
					UPDATE erp_purchase_list SET purchase_list_price = purchase_list_price-OLD.purchase_details_price where purchase_list_id = NEW.purchase_details_list;
				ELSE
					UPDATE erp_purchase_list SET purchase_list_number = purchase_list_number+(NEW.purchase_details_number-OLD.purchase_details_number) where purchase_list_id = NEW.purchase_details_list;
					UPDATE erp_purchase_list SET purchase_list_price = purchase_list_price+(NEW.purchase_details_price-OLD.purchase_details_price) where purchase_list_id = NEW.purchase_details_list;
				END IF;
				RETURN NEW;
    END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

CREATE TRIGGER "update_detail_trigger" AFTER UPDATE ON "public"."erp_purchase_details"
FOR EACH ROW
EXECUTE PROCEDURE "public"."update_details_func"();

CREATE UNIQUE INDEX "unip_name" ON "public"."erp_goods" USING btree (
  "goods_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE UNIQUE INDEX "uniq_staff_name" ON "public"."erp_staff" USING btree (
  "staff_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE UNIQUE INDEX "uniq_supplier_name" ON "public"."erp_supplier" USING btree (
  "supplier_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE INDEX "list_goods_btree" ON "public"."erp_purchase_details" USING btree (
  "purchase_details_list" "pg_catalog"."int8_ops" ASC NULLS LAST,
  "purchase_details_goods" "pg_catalog"."int8_ops" ASC NULLS LAST
);

err := db.Table("erp_goods g").Select("g.goods_id, g.goods_name, g.goods_price, g.goods_supplier,s.supplier_name goods_supplier_name, g.goods_remarks,g.goods_synopsis,g.is_del, g.update_time").Joins("INNER JOIN erp_supplier s ON g.goods_supplier = s.supplier_id").Where("g.goods_name like ? and g.is_del = false", searchName+"%").Order("goods_id ASC").Count(&total).Offset((page - 1) * pageSize).Limit(pageSize).Find(&goodsData).Error

err := db.Table("erp_purchase_details d").Select("d.purchase_details_id, d.purchase_details_list, d.purchase_details_goods, g.goods_name purchase_details_goods_name,d.purchase_details_number, d.purchase_details_price, d.purchase_details_remarks, d.update_time, d.is_del").Joins("INNER JOIN erp_goods g ON d.purchase_details_goods = g.goods_id").Where("d.purchase_details_list = ?", searchList).Where("d.is_del = false").Where("g.goods_name like ?",searchGoodsName+"%").Order("purchase_details_id ASC").Count(&total).Offset((page - 1) * pageSize).Limit(pageSize).Find(&purchaseDetailsData).Error

err := db.Table("erp_purchase_list p").Select("p.purchase_list_id, p.purchase_list_number, p.purchase_list_price, p.purchase_list_remarks, p.purchase_list_staff, s.staff_name purchase_list_staff_name, p.purchase_list_time, p.update_time, p.is_del").Joins("INNER JOIN erp_staff s ON p.purchase_list_staff = s.staff_id").Where("s.staff_name like ? and p.is_del = false", searchName+"%").Order("purchase_list_id ASC").Count(&total).Offset((page - 1) * pageSize).Limit(pageSize).Find(&purchaseListData).Error