/*-----------------------------------------------------------------------------
* DESCRIPTION：采購訂單的“品質(zhì)要求”字段可隨基礎(chǔ)資料帶出并可修改的實現(xiàn)方式
* BY：Suntime
* DATE：2006-12

* 功能：采購訂單的“品質(zhì)要求”字段可隨基礎(chǔ)資料帶出,，并可修改
* 客戶要求：應(yīng)客戶要求,，設(shè)置此字段的目的
      1、向其供應(yīng)商和加工商在業(yè)務(wù)單據(jù)上說明相關(guān)物料的生產(chǎn),、加工要求
      2,、在一段時間內(nèi)，某一物料的外購或委外品質(zhì)要求是不變的
      3,、要減少輸單工作量,，訂單上的“品質(zhì)要求”字段可隨基礎(chǔ)資料帶出，并可修改,。
      4,、新增或選單生成業(yè)務(wù)單據(jù)時，最好能直接在新單據(jù)上直接顯示此字段相關(guān)信息
* 實現(xiàn)方式：
      1,、在物料基礎(chǔ)資料上新增“品質(zhì)要求”字段,，
      2,、在采購訂單上新增“品質(zhì)要求”字段，新增或選單生成業(yè)務(wù)單據(jù)時,數(shù)據(jù)值來源于物料的“品質(zhì)要求”,，并可修改,。
-----------------------------------------------------------------------------*/

/*在物料基礎(chǔ)資料上新增"品質(zhì)要求"字段*/

if not exists (SELECT * FROM sysobjects t1 ,syscolumns t2 where t1.id=t2.id and t1.name='t_ICItemCustom'  and t2.Name='QualityRequest')
Alter Table t_ICItemCustom Add QualityRequest varchar(800)
go

DECLARE @FPropID INT
SELECT @FPropID = ISNull(MAX(FPropID)+1,102) FROM t_ItemPropDesc WHERE FItemClassID=4 And (FPropID>=102 And FPropID<500)
PRINT @FPropID

INSERT INTO t_ItemPropDesc (FItemClassID,FPropID,FName,FName_en,FName_cht,FSQLColumnName,FDataType,FPrecision,FScale,FActualType,FActualSize,FBehavior,FSearch,FSrcTable,FSrcField,FDisplayField,FDefaultValue,FPageName,FPageName_en,FPageName_cht,FComCall,FIsShownList,FViewMask)
VALUES (4,@FPropID, '品質(zhì)要求', '品質(zhì)要求', '品質(zhì)要求', 'QualityRequest', 200, 800, 0, 200, 800, NULL, 0, NULL, NULL, NULL, NULL, '1.基本資料', '1. Basic Data', '1.基本資料', NULL, 0, 0)
go

If Not Exists(Select 1 from t_FieldDescription where FTableID =60 And FFieldName='QualityRequest')
 Insert into t_FieldDescription(FTableID,FFieldName,FFieldType,FDescription,FDescription_CHT,FDescription_EN,FSefDefSign) values(60,'QualityRequest','STRING','品質(zhì)要求','品質(zhì)要求','品質(zhì)要求',1)
go

UPdate t_ItemPropDesc set FBehavior=1  Where FItemClassID = 4 and Fname='品質(zhì)要求'
Go

exec SP_CREATE_ICItem_VIEW
go
exec SP_CREATE_ICItem_TRIGGER
go

/*在采購訂單上新增"品質(zhì)要求"字段*/

--向單據(jù)模板記錄表（ICTemplateEntry）中插入 QualityRequest 字段
DECLARE @FCtlIndex INT,
 @FCtlOrder INT
SELECT @FCtlIndex = MAX(FCtlIndex)+1 FROM ICTemplateEntry WHERE FID='P02'
SELECT @FCtlOrder = MAX(FCtlOrder)+1 FROM ICTemplateEntry WHERE FID='P02'
PRINT @FCtlIndex
PRINT @FCtlOrder

Insert Into ICTemplateEntry(FID,FCtlIndex,FCtlOrder,FHeadCaption,FHeadCaption_CHT,FHeadCaption_EN,FCtlType,FLookUpCls,FNeedSave,FValueType,FSaveValue,FFieldName,FWidth,FEnable,FRelateOutTbl,FPrint,FMustInput,FNeedCount,FFilter,FRelationID,FAction,FDefaultCtl,FVisForBillType, FStatCount,FFormat,FMaxValue,FMinValue)
Values('P02',@FCtlIndex,@FCtlOrder,'品質(zhì)要求','品質(zhì)要求','品質(zhì)要求',30,-1,-1,0,0,'QualityRequest',1500,48,0,-1,0,0,'','FItemID','.,QualityRequest',1,31,0,'','','')
go

--向采購訂單分錄表(POOrderEntry)中添加 QualityRequest 字段
if not exists (SELECT * FROM sysobjects t1 ,syscolumns t2 where t1.id=t2.id and t1.name='POOrderEntry'  and t2.Name='QualityRequest')
Alter Table POOrderEntry  Add QualityRequest VARCHAR(800) NULL
go

--向出套打格式字段表（GLNoteCitation）\單據(jù)序時薄表（ICChatBillTitle）中添加 QualityRequest 字段
DELETE FROM  GLNoteCitation WHERE FCode='QualityRequest' AND  FTemplateID='P02' AND FRelationID IN (1,2,3,20)

DECLARE @FID INT,@FInterID INT
SELECT @FID = MAX(FID)+1 FROM GLNoteCitation WHERE FTemplateID='P02'
SELECT @FInterID = MAX(FInterID)+1 FROM ICChatBillTitle Where FTypeID=81
PRINT @FID
PRINT @FInterID

Insert Into GLNoteCitation(FTemplateID,FID,FNoteTypeID,FCitationName,FInEntry,FIsMoney,FCode,FCtlIndex,FIsSum,FKeyFieldName,FExtFieldName,FTableName,FRelationID,FCitationName_CHT,FCitationName_EN)
Values ('P02',@FID ,50,'品質(zhì)要求$',1,0 ,'QualityRequest',41,0,'QualityRequest','', '', 2,'品質(zhì)要求$','品質(zhì)要求$')

Insert Into ICChatBillTitle(FInterID,FTypeID,FColCaption,FColCaption_CHT,FColCaption_EN,FMergeable,FColName,FName,FTableName,FTableAlias,FColType,FItemClassID,FReturnDataType,FCtlIndex,FStatistical,FNeedCount,FCountPriceType, FVisForQuest,FVisForOrder,FFormat)
Values(@FInterID,81,'品質(zhì)要求$','品質(zhì)要求$','品質(zhì)要求$',0,'QualityRequest','QualityRequest','POOrderEntry','u1',1,-1,0,41,1,0,0,1,1,'')
go

--整理單據(jù)求和統(tǒng)計公式計算表（ICVoucherTField）中先前添加 QualityRequest 字段記錄
Delete FROM  ICVoucherTField WHERE FBillType=71 AND FIsUserDefine=1 AND FUserDefineField='QualityRequest'
go

--向表描述表（t_TableDescription）和字段描述表（t_FieldDescription）添加QualityRequest 字段
 IF Exists(Select t2.* From t_TableDescription t1, t_FieldDescription t2  Where t1.FTableID=t2.FTableID And t1.FTableName ='POOrderEntry' And t2.FFieldName ='QualityRequest')
     Begin
         UPDATE t2 SET FFieldType ='STRING'
         ,FDescription ='品質(zhì)要求',FDescription_CHT ='品質(zhì)要求',FDescription_EN ='品質(zhì)要求'
         ,FFieldNote = '品質(zhì)要求',FFieldNote_CHT = '品質(zhì)要求',FFieldNote_EN = '品質(zhì)要求'
         From t_TableDescription t1, t_FieldDescription t2
         Where t1.FTableID=t2.FTableID And t1.FTableName ='POOrderEntry' And t2.FFieldName ='QualityRequest'
     End
 Else
     Begin
         INSERT INTO t_FieldDescription(FTableID,FFieldName,FFieldType
         ,FDescription,FDescription_CHT,FDescription_EN
         ,FFieldNote,FFieldNote_CHT,FFieldNote_EN
         ) Select t1.FTableID,'QualityRequest','STRING'
         ,'品質(zhì)要求','品質(zhì)要求','品質(zhì)要求'
         ,'品質(zhì)要求','品質(zhì)要求','品質(zhì)要求'
         From t_TableDescription t1
         Where t1.FTableName ='POOrderEntry'
     End
Go

--刪除并重整選單流轉(zhuǎn)表（ICSelBills）中的 QualityRequest 字段記錄
Delete ICSelBills Where FID ='P02' and  FFieldName='1007100' and  FDstCtlField='QualityRequest' and  FSelType=0
Delete ICSelBills Where FID ='P02' and  FFieldName='32' and  FDstCtlField='QualityRequest' and  FSelType=0
Delete ICSelBills Where FID ='P02' and  FFieldName='FContractID' and  FDstCtlField='QualityRequest' and  FSelType=0
Delete ICSelBills Where FID ='P02' and  FFieldName='FContractID' and  FDstCtlField='QualityRequest' and  FSelType=0
Delete ICSelBills Where FID ='P02' and  FFieldName='FRequestInterID' and  FDstCtlField='QualityRequest' and  FSelType=0
Insert Into ICSelBills(FID,FFieldName,FDstCtlField,FDK,FSelType,FColName,FName,FTableName,FTableAlias,FAction)
Values('P02','FRequestInterID','QualityRequest',0,0,'QualityRequest','QualityRequest','t_ICItem','t4','')
go

--設(shè)置采購訂單 品質(zhì)要求 字段來源于物料的 品質(zhì)要求 字段
UPdate ICTemplateEntry set FRelationID='FItemID',FAction='.,QualityRequest',FDefaultctl=1 where FFieldName='QualityRequest'
go