---------------------------------------------------
-- 자재입출고 관리
---------------------------------------------------

-- 자재 입고 정보
-- MaenEnter
CREATE TABLE FmsMaterialStored
(
	SiteId					int					NOT NULL ,	-- 현장 고유번호
	BusinessFieldId			int					NOT NULL ,  -- 업무분야 고유번호
	StoredDate				datetime			NOT NULL ,	-- 입고일시
	MaterialStoredId		int	identity(1,1)	NOT NULL ,	-- 입고 고유번호

	PurchaseRequestId		int					NULL ,		-- 구매신청 고유번호

	WarehouseId				int					NOT NULL ,  -- 창고 고유번호 
	MaterialId				int					NOT NULL ,	-- 자재 고유번호
	StoredCount				int					NOT NULL ,	-- 자재 요청 수량
	UnitCost				int					NOT NULL ,  -- 자재 단가

	StockCount				int					NOT NULL ,  -- 입고한 것 자체에 대한 재고 개수, 선입선출 할 때 이 값이 감소한다. 0이되면 출고 불가능, 재고관리 테이블에 계산하여 넣는것도 방법
	
	Note					nvarchar(1024)		NULL ,		-- 비고

	StoredUserId			nvarchar(24)		NOT NULL ,	-- 입고한 사용자 고유번호

	ApprovalDate			datetime			NULL ,		-- 승인일시
	ApprovalUserId			nvarchar(24)		NULL ,		-- 승인자 고유번호
	
	IsApproval				bit					NOT NULL ,	-- 승인여부

	PRIMARY KEY (SiteId ASC , BusinessFieldId ASC, StoredDate ASC , MaterialStoredId ASC)
);

-- 자재 출고 정보
-- MadeDelivery
CREATE TABLE FmsMaterialRelease
(
	SiteId					int					NOT NULL ,	-- 현장 고유번호
	BusinessFieldId			int					NOT NULL ,  -- 업무분야 고유번호
	ReleaseDate				datetime			NOT NULL ,	-- 출고일시
	MaterialReleaseId		int	identity(1,1)	NOT NULL ,	-- 출고 고유번호

	ReleaseTypeId			smallint			NOT NULl ,  -- 출고구분 고유번호

	WorkOrderId				int					NOT NULL ,	-- 작업지시 고유번호

	WarehouseId				int					NOT NULL ,  -- 창고 고유번호 
	MaterialId				int					NOT NULL ,	-- 자재 고유번호

	ReleaseCount			int					NOT NULL ,	-- 자재 출고 수량
	ReleaseTotalCost		int					NOT NULL ,	-- 출고 된 자재 총 비용(각 단가 별로의 합산)
	

	PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , ReleaseDate ASC ,  MaterialReleaseId ASC)
);


-- 자재 이월 정보
CREATE TABLE FmsMaterialCarriedForward
(
	SiteId					int					NOT NULL ,	-- 현장 고유번호
	ClosedDate				datetime			NOT NULL ,	-- 마감 연월 (일: 1)
	
	BusinessFieldId			int					NOT NULL ,  -- 업무분야 고유번호
	WarehouseId				int					NOT NULL ,  -- 창고 고유번호 
	MaterialId				int					NOT NULL ,	-- 자재 고유번호

	StockCount				int					NOT NULL ,	-- 자재 재고 수량
	StockTotalCost			int					NOT NULL ,  -- 자재 총 비용(각 단가 별로의 합산)
		
	PRIMARY KEY (SiteId ASC , ClosedDate ASC , BusinessFieldId ASC , WarehouseId ASC , MaterialId ASC)
);


-- 입고 FK
ALTER TABLE FmsMaterialStored
	ADD CONSTRAINT FK__FmsMaterialStored__CmSite 
		FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
;

ALTER TABLE FmsMaterialStored
	ADD CONSTRAINT FK__FmsMaterialStored__FmsMaterialPurchaseRequest
		FOREIGN KEY (SiteId, BusinessFieldId, PurchaseRequestId) REFERENCES FmsMaterialPurchaseRequest(SiteId, BusinessFieldId, PurchaseRequestId)
;

ALTER TABLE FmsMaterialStored
	ADD CONSTRAINT FK__FmsMaterialStored__CmBusinessField 
		FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
;

ALTER TABLE FmsMaterialStored
	ADD CONSTRAINT FK__FmsMaterialStored__FmsMaterialWarehouse
		FOREIGN KEY (SiteId, BusinessFieldId, WarehouseId) REFERENCES FmsMaterialWarehouse(SiteId, BusinessFieldId,WarehouseId)
;

ALTER TABLE FmsMaterialStored
	ADD CONSTRAINT FK__FmsMaterialStored__FmsMaterial
		FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
;

ALTER TABLE FmsMaterialStored
	ADD CONSTRAINT FK__FmsMaterialStored__CmUser_AsStoredUserId
		FOREIGN KEY (SiteId, StoredUserId) REFERENCES CmUser(SiteId, UserId)
;

ALTER TABLE FmsMaterialStored
	ADD CONSTRAINT FK__FmsMaterialStored__CmUser1_AsApprovalUserId
		FOREIGN KEY (SiteId, ApprovalUserId) REFERENCES CmUser(SiteId, UserId)
;


-- 출고 FK
ALTER TABLE FmsMaterialRelease
	ADD CONSTRAINT FK__FmsMaterialRelease__CmSite 
		FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
;

ALTER TABLE FmsMaterialRelease
	ADD CONSTRAINT FK__FmsMaterialRelease__FmsWorkOrder
		FOREIGN KEY (SiteId, WorkOrderId) REFERENCES FmsWorkOrder(SiteId, WorkOrderId)
;

ALTER TABLE FmsMaterialRelease
	ADD CONSTRAINT FK__FmsMaterialRelease__CmBusinessField 
		FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
;

ALTER TABLE FmsMaterialRelease
	ADD CONSTRAINT FK__FmsMaterialRelease__FmsMaterialCodeReleaseType 
		FOREIGN KEY (ReleaseTypeId) REFERENCES FmsMaterialCodeReleaseType(ReleaseTypeId)
;

ALTER TABLE FmsMaterialRelease
	ADD CONSTRAINT FK__FmsMaterialRelease__FmsMaterialWarehouse
		FOREIGN KEY (SiteId, WarehouseId) REFERENCES FmsMaterialWarehouse(SiteId, WarehouseId)
;

ALTER TABLE FmsMaterialRelease
	ADD CONSTRAINT FK__FmsMaterialRelease__FmsMaterial
		FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
;


-- 자재 이월 FK
ALTER TABLE FmsMaterialCarriedForward
	ADD CONSTRAINT FK__FmsMaterialCarriedForward__CmSite 
		FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
;

ALTER TABLE FmsMaterialCarriedForward
	ADD CONSTRAINT FK__FmsMaterialCarriedForward__CmBusinessField 
		FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
;

ALTER TABLE FmsMaterialCarriedForward
	ADD CONSTRAINT FK__FmsMaterialCarriedForward__FmsMaterialWarehouse
		FOREIGN KEY (SiteId, BusinessFieldId, WarehouseId) REFERENCES FmsMaterialWarehouse(SiteId, BusinessFieldId, WarehouseId)
;

ALTER TABLE FmsMaterialCarriedForward
	ADD CONSTRAINT FK__FmsMaterialCarriedForward__FmsMaterial
		FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
;
