gen_view.prg

自写的仓库管理系统,之前在工厂打工时写的

***********************************************
*-- 该过程用于以编程的方法建立视图，在程序运行时该
*-- 过程绝不会运行，仅在系统建立初期，建立数据模型时使用。
***********************************************
OPEN DATABASE DATA\IT
*-- 建立销售单
CREATE SQL VIEW "view_sales" AS ;
	SELECT 	Sales.销售单序号,;
	Sales.客户名字,;
	Sales.日期,;
	PAYMENT.付款方式,;
	medicine.药品名称,;
	medicine.规格,;
	medicine.单位,;
	SaleItem.单价,;
	SaleItem.数量,;
	SaleItem.金额 ;
	FROM Sales,PAYMENT,SaleItem,medicine ;
	WHERE 	Sales.销售单号 = SaleItem.销售单号 AND ;
	PAYMENT.付款方式码 = Sales.付款方式码 AND ;
	SaleItem.药品码 = medicine.药品码  AND ;
	Sales.审核 = .T. ;
	ORDER BY Sales.销售单号
***********************************************
*-- 建立进货单
CREATE SQL VIEW "view_purchase" AS ;
	SELECT 	Purchase.进货单序号,;
	Purchase.日期,;
	Purchase.供货厂商,;
	PAYMENT.付款方式,;
	medicine.药品名称,;
	medicine.规格,;
	medicine.单位,;
	PurItem.单价,;
	PurItem.数量,;
	PurItem.金额 ;
	FROM Purchase,PAYMENT,PurItem,medicine ;
	WHERE 	Purchase.进货单号 = PurItem.进货单号 AND ;
	PAYMENT.付款方式码 = Purchase.付款方式码 AND ;
	PurItem.药品码 = medicine.药品码  AND ;
	Purchase.审核 = .T. ;
	ORDER BY Purchase.进货单号
***********************************************
*-- 建立盘存表
CREATE SQL VIEW "view_inventory" AS ;
	SELECT 	inventory.盘点表序号,;
	inventory.日期,;
	medicine.药品名称,;
	medicine.规格,;
	medicine.单位,;
	InvItem.盈亏,;
	Reason.盈亏原因,;
	InvItem.单价,;
	InvItem.数量,;
	InvItem.单价*InvItem.数量 AS 金额 ;
	FROM inventory,Reason,InvItem,medicine ;
	WHERE 	inventory.盘点表号 = InvItem.盘点表号 AND ;
	Reason.盈亏原因码 = invitem.盈亏原因码 AND ;
	InvItem.药品码 = medicine.药品码  AND ;
	inventory.审核 = .T. ;
	ORDER BY inventory.盘点表号
***********************************************
*-- 把进货，销售，盘存三个表的数据汇总到一起
CREATE SQL VIEW "view_currentstocks" AS ;
	SELECT "进货" AS 来源,;
			Medicine.药品码, ;
			Medicine.药品名称, ;
			Medicine.规格, ;
			Medicine.单位,;
			Medicine.进货单价,;			
			SUM(Puritem.数量) AS 当前库存数;
	FROM  it!medicine,it!puritem ;
	WHERE Medicine.药品码 = Puritem.药品码 ;
	GROUP BY Medicine.药品码;
	UNION ALL ;
	SELECT "销售" AS 来源,;
			Medicine.药品码, ;
			Medicine.药品名称, ;
			Medicine.规格, ;
			Medicine.单位,;
			Medicine.进货单价,;
	SUM(0-saleitem.数量) AS 当前库存数;
	FROM  it!medicine,it!saleitem ;
	WHERE Medicine.药品码 = Saleitem.药品码 ;
	GROUP BY Medicine.药品码;
	UNION ALL ;
	SELECT "盘点" AS 来源,;
			Medicine.药品码, ;
			Medicine.药品名称, ;
			Medicine.规格, ;
			Medicine.单位,;
			Medicine.进货单价,;			
	SUM(Invitem.数量) AS 当前库存数;
	FROM  it!medicine,it!invitem ;
	WHERE Medicine.药品码 = Invitem.药品码 ;
	GROUP BY Medicine.药品码

*-- 当前库存量	
CREATE SQL VIEW "view_sum_stocks" AS ;
	SELECT  药品码,;
	        药品名称,;
			规格,;
			单位,;
			SUM(当前库存数) AS 当前库存, ;
			进货单价,;
			SUM(当前库存数) * 进货单价 as  库存金额 ;
	FROM view_currentstocks ;
	GROUP BY 药品名称 ;
	ORDER BY 药品码
***********************************************
***********************************************
*-- 销售按商品汇总
CREATE SQL VIEW "view_sum_sales" AS ;
	SELECT 	;
	medicine.药品名称,;
	medicine.规格,;
	medicine.单位,;
	SaleItem.单价,;
	SUM(SaleItem.数量) AS 数量,;
	SUM(SaleItem.金额) AS 金额;
	FROM Sales,SaleItem,medicine ;
	WHERE 	Sales.销售单号 = SaleItem.销售单号 AND ;
	SaleItem.药品码 = medicine.药品码  AND ;
	Sales.审核 = .T. AND ;
    Sales.日期 >= ?起始日期 AND Sales.日期 <= ?终止日期 ;	
	GROUP BY medicine.药品码;
	ORDER BY Sales.销售单号
*-- 销售按日期汇总
CREATE SQL VIEW "view_date_sales" AS ;
	SELECT 	;
	Sales.日期,;
	SUM(SaleItem.金额) AS 销售额;
	FROM Sales,SaleItem ;
	WHERE Sales.销售单号 = SaleItem.销售单号 AND ;
	Sales.审核 = .T. ;
	GROUP BY Sales.日期;
	ORDER BY Sales.日期
*-- 销售按销售单汇总
CREATE SQL VIEW "view_sheet_sales" AS ;
	SELECT 	;
    Sales.销售单序号,;
	SUM(SaleItem.金额) AS 金额;
	FROM Sales,SaleItem ;
	WHERE Sales.销售单号 = SaleItem.销售单号 AND ;
	Sales.审核 = .T. ;
	GROUP BY Sales.销售单序号;
	ORDER BY Sales.销售单序号
***********************************************
*-- 进货按商品汇总
CREATE SQL VIEW "view_sum_purchase" AS ;
	SELECT 	;
	medicine.药品名称,;
	medicine.规格,;
	medicine.单位,;
	PurItem.单价,;
	SUM(PurItem.数量) AS 数量,;
	SUM(PurItem.金额) AS 金额;
	FROM Purchase,PurItem,medicine ;
	WHERE 	Purchase.进货单号 = PurItem.进货单号 AND ;
	PurItem.药品码 = medicine.药品码  AND ;
	Purchase.审核 = .T. AND ;
    Purchase.日期 >= ?起始日期 AND Purchase.日期 <= ?终止日期 ;	
	GROUP BY medicine.药品码;
	ORDER BY Purchase.进货单号
*-- 进货按进货单号汇总
CREATE SQL VIEW "view_sheet_purchase" AS ;
	SELECT 	Purchase.进货单序号,;
	SUM(PurItem.金额) AS 金额;
	FROM Purchase,PurItem ;
	WHERE Purchase.进货单号 = PurItem.进货单号 AND ;
	Purchase.审核 = .T. ;
	GROUP BY Purchase.进货单序号;
	ORDER BY Purchase.进货单序号
*-- 进货按日期汇总
CREATE SQL VIEW "view_date_purchase" AS ;
	SELECT 	;
	Purchase.日期,;
	SUM(PurItem.金额) AS 进货金额;
	FROM Purchase,PurItem ;
	WHERE Purchase.进货单号 = PurItem.进货单号 AND ;
	Purchase.审核 = .T. ;
	GROUP BY Purchase.日期;
	ORDER BY Purchase.日期
***********************************************
*-- 盘点按盘点表汇总
CREATE SQL VIEW "view_sheet_inv" AS ;
	SELECT 	inventory.盘点表序号,;
	SUM(InvItem.金额) AS 金额;
	FROM inventory,invItem ;
	WHERE inventory.盘点表号 = InvItem.盘点表号 AND ;
	inventory.审核 = .T. ;
	GROUP BY inventory.盘点表序号;
	ORDER BY inventory.盘点表序号