02.集成成品料号数据进入金蝶K3

Create PROCEDURE [dbo].[sp_addProductCode]
@materialName varchar(100),
@materailCategoryCode varchar(50),
@materailCategoryName varchar(500),
@result varchar(100) output
AS
BEGIN
	--声明参数
	--DECLARE @materialCode VARCHAR(100) = '11.02.01.000004';
	--DECLARE @materialName VARCHAR(100) = '测试3';
	DECLARE @materialNamePinYin VARCHAR(100) = '';
	DECLARE @materialNamePY VARCHAR(50) = '';
	-- 定义分隔符	
	DECLARE @Separator VARCHAR(10)='.';
	-- 获取上级物料编码
	DECLARE @ParentMaterialCode VARCHAR(100)=REPLACE(@materailCategoryCode,@Separator,'.')

	DECLARE @ParentId int;
	DECLARE @ParentFullName VARCHAR(100);
	DECLARE @ParentFLevel int;
	DECLARE @FItemID int;
	DECLARE @dataMaterialCode VARCHAR(100)='';
	DECLARE @UserId int;

	select @UserId=FUserID from t_Base_User where FName='Administrator'
	-- 查询物料是否存在
			select top 1  @dataMaterialCode =FNumber from t_Item
		where FItemClassID = 4 and FName=@materialName

		if @dataMaterialCode!=''
		begin 
		set @result=@dataMaterialCode;
		--print @dataMaterialCode;
		return 1;
		end

	-- 开始事务
	BEGIN TRY 
        BEGIN TRANSACTION;			

	--插入上级物料
	
DECLARE @MaxPosition INT;

SELECT @MaxPosition = LEN(@materailCategoryName) - LEN(REPLACE(@materailCategoryName, @Separator, '')) + 1;

DECLARE @StartPos INT = 1; -- 从第一段开始
DECLARE @StartPos2 INT = 1; -- 从第一段开始

DECLARE @OutputString NVARCHAR(100);
DECLARE @OutputString2 NVARCHAR(100);
DECLARE @ResultCode NVARCHAR(100)='';


DECLARE @CurrentPos INT = 1;
DECLARE @EndPos INT = 1;
DECLARE @EndPos2 INT = 1;
	
WHILE @CurrentPos <= @MaxPosition
BEGIN

   -- 代码
    SET @EndPos2 = CHARINDEX(@Separator, @materailCategoryCode, @StartPos2);
    IF @EndPos2 = 0
    BEGIN
        SET @EndPos2 = LEN(@materailCategoryCode) + 1;
    END

    SET @OutputString2 = SUBSTRING(@materailCategoryCode, @StartPos2, @EndPos2-@StartPos2);
	SET @ResultCode = @ResultCode +'.'+ @OutputString2;
		IF LEFT(@ResultCode, 1) ='.'
			BEGIN
				SET @ResultCode =STUFF(@ResultCode, 1, 1, '');
		END
    --PRINT @ResultCode;

    -- 名称
    SET @EndPos = CHARINDEX(@Separator, @materailCategoryName, @StartPos);
    IF @EndPos = 0
    BEGIN
        SET @EndPos = LEN(@materailCategoryName) + 1;
    END

    SET @OutputString = SUBSTRING(@materailCategoryName, @StartPos, @EndPos-@StartPos);
    --PRINT @OutputString;
    SET @StartPos = @EndPos + 1;
    SET @StartPos2 = @EndPos2 + 1;
    SET @CurrentPos = @CurrentPos + 1;

	DECLARE @AddItemResult varchar(100)='' ;
	exec sp_In3AddItem @ResultCode,@OutputString,'','L01','PCS','PCS',0,@AddItemResult output;
END


	--  查询parent Info
	--select @ParentId=FItemID,@ParentFLevel=FLevel,@ParentFullName=FFullName from t_Item
	--where FItemClassID = 4 and FNumber =(SELECT LEFT(@materialCode, LEN(@materialCode) - CHARINDEX('.', REVERSE(@materialCode))))
	
		declare @FItemClassIDMaterial int;
				declare @FItemClassIDWarehouse int;
				select @FItemClassIDMaterial=FItemClassID from t_ItemClass where FName='物料'
				select @FItemClassIDWarehouse=FItemClassID from t_ItemClass where FName='仓库'

	select @ParentId=FItemID,@ParentFLevel=FLevel,@ParentFullName=FFullName from t_Item
	where FItemClassID = @FItemClassIDMaterial and FNumber =@ParentMaterialCode	

	-- 自动生成短号
	DECLARE @FShortNumber varchar(20)='60000000';
	select top 1@FShortNumber=FShortNumber from t_Item
	where FItemClassID = 4 and FParentID=@ParentId and   LEFT(FShortNumber, 1)='6'  order by FShortNumber desc
	set @FShortNumber=@FShortNumber+1
	-- 生成完整成品编码Code
	DECLARE @materialCode varchar(50)=@ParentMaterialCode+'.'+RIGHT(REPLICATE('0', 8) + @FShortNumber, 8);
	
	-- 插入 t_Item 表数据
	INSERT INTO t_Item (FItemClassID,FParentID,FLevel,FName,FNumber,FShortNumber,FFullNumber,FDetail,UUID,FDeleted,FFullName)
				VALUES (@FItemClassIDMaterial,@ParentId,@ParentFLevel+1,@materialName,@materialCode,(SELECT RIGHT(@materialCode, CHARINDEX('.',REVERSE(@materialCode)) - 1)),@materialCode,1,(Select newid()),0,@ParentFullName+'_'+@materialName)

	select @FItemID=FItemID from t_Item where FItemClassID=4 and FFullNumber=@materialCode


	 -- 获取数量组ID
			DECLARE @FUnitGroupID int ;
			 select @FUnitGroupID=FUnitGroupID from t_UnitGroup where FName='数量组'
	 	-- 基本单位  默认为数量组
		--基本单位
		DECLARE @FUnitID int;
		--采购单位
		DECLARE @FOrderUnitID int;
		DECLARE @FSaleUnitID int;
		DECLARE @FProductUnitID int;
		select @FUnitID=FMeasureUnitID,@FOrderUnitID=FMeasureUnitID ,
		@FSaleUnitID=FMeasureUnitID,@FProductUnitID=FMeasureUnitID
		from t_MeasureUnit t1 
		inner join t_UnitGroup t2 On '数量组'= t2.FName And t1.FUnitGroupID = t2.FUnitGroupID
		where t1.FName='PCS'	

		--  获取仓库ID
		DECLARE @FDefaultLoc int;
		select @FDefaultLoc=FItemID from t_Item where FItemClassID = @FItemClassIDWarehouse and FNumber='SZ002'

		-- 查询使用状态ID
		DECLARE @FUseState int;
		select @FUseState=FInterID from t_Submessage where FName='使用' and FTypeID = 170
		
		-- 查询计价方法
		DECLARE @FTrack int ;
		select @FTrack=FInterID from t_SubMessage  where  FName='加权平均法' and FTypeID = 200
		
		-- 查询存货科目
			declare @FAcctID int;
			select @FAcctID=FAccountID from t_Account   where FNumber='1405'
			

			--销售收入科目代码
			declare @FSaleAcctID int;
			select @FSaleAcctID=FAccountID from t_Account   where FNumber='6001.001.02'

			--销售成本科目代码
			declare @FCostAcctID int;
			select @FCostAcctID=FAccountID from t_Account   where FNumber='6401.02'
			

			-- 查询计划策略			
			declare @FPlanTrategy int;
			select @FPlanTrategy=FInterID from t_Submessage   where FName='无'  and FTypeID = 167
			-- 查询计划模式
			declare @FPlanMode int;
			select @FPlanMode=FInterID from t_Submessage   where  FName='MTS计划模式' and FTypeID = 606

			--查询订货策略
			declare @FOrderTrategy int;
			select @FOrderTrategy=FInterID  from t_Submessage   where  FName='批对批(LFL)' and FTypeID = 169
			
			-- 查询 采购检验方式			
			--采购检验方式
			declare @FInspectionLevel int;
			--产品检验方式
			declare @FProChkMde int;
			--委外加工检验方式
			declare @FWWChkMde int;
			--发货检验方式
			declare @FSOChkMde int;
			--退货检验方式
			declare @FWthDrwChkMde int;
			--库存检验方式
			declare @FStkChkMde int;
			--其他检验方式
			declare @FOtherChkMde int;
			select @FInspectionLevel=FInterID,
			 @FProChkMde=FInterID,
			 @FWWChkMde=FInterID,
			 @FSOChkMde=FInterID,
			 @FWthDrwChkMde=FInterID,
			 @FStkChkMde=FInterID,
			 @FOtherChkMde=FInterID
			from t_Submessage where FName='免检' and FTypeID = 171

				-- 查询控制类型
			declare @FCtrlType int;
			select @FCtrlType=FInterID from t_Submessage   where FName='ERP' and FTypeID = 607


	-- 插入 t_ICItem 表数据
	INSERT INTO t_ICItem 
(FHelpCode,FModel,FAuxClassID,FErpClsID ,FTypeID,FUnitGroupID,FUnitID,FOrderUnitID,FSaleUnitID,FProductUnitID,FStoreUnitID,
FSecUnitID,FSecCoefficient,FDefaultLoc,FSPID,FSource,FQtyDecimal,FLowLimit,FHighLimit,FSecInv,FUseState,FIsEquipment,FEquipmentNum,FIsSparePart,FFullName,FApproveNo,FAlias,FOrderRector,
FPOHighPrice,FPOHghPrcMnyType,FWWHghPrc,FWWHghPrcMnyType,FSOLowPrc,FSOLowPrcMnyType,FIsSale,FProfitRate,FOrderPrice,FSalePrice,FIsSpecialTax,FISKFPeriod,FKFPeriod,FStockTime,FBatchManager,FBookPlan,FBeforeExpire,FCheckCycUnit,FOIHighLimit,FOILowLimit,FSOHighLimit,FSOLowLimit,FInHighLimit,FInLowLimit,FPickHighLimit,FPickLowLimit,
FTrack,FPlanPrice,FPriceDecimal,FAcctID,FSaleAcctID,FCostAcctID,FAPAcctID,FAdminAcctID,FGoodSpec,FTaxRate,FCostProject,FIsSNManage,F_101,F_102,F_103,FCBRestore,FNote,
FPlanTrategy,FPlanMode,FOrderTrategy,FFixLeadTime,FLeadTime,FTotalTQQ,FOrderInterVal,FQtyMin,FQtyMax,FBatchAppendQty,FOrderPoint,FBatFixEconomy,FBatChangeEconomy,FRequirePoint,FPlanPoint,FDefaultRoutingID,FDefaultWorkTypeID,FProductPrincipal,FPlanner,FIsBackFlush,FBackFlushStockID,FBackFlushSPID,FPutInteger,FDailyConsume,FMRPCon,FMRPOrder,FChartNumber,FIsKeyItem,FGrossWeight,FNetWeight,FMaund,FLength,FWidth,FHeight,FSize,FCubicMeasure,FStandardCost,FCBAppendRate,FCBAppendProject,FCostBomID,FCBRouting,FStdBatchQty,FStandardManHour,FStdPayRate,FChgFeeRate,FStdFixFeeRate,FOutMachFee,FOutMachFeeProject,FPieceRate,FPOVAcctID,FPIVAcctID,FMCVAcctID,FPCVAcctID,FSLAcctID,FCAVAcctID,
FInspectionLevel,FProChkMde,FWWChkMde,FSOChkMde,FWthDrwChkMde,FStkChkMde,FOtherChkMde,FSampStdCritical,FSampStdStrict,FSampStdSlight,FStkChkPrd,FStkChkAlrm,FInspectionProject,FIdentifier,FVersion,FNameEn,FModelEn,FHSNumber,FExportRate,FFirstUnit,FSecondUnit,FImpostTaxRate,FConsumeTaxRate,FFirstUnitRate,FSecondUnitRate,FIsManage,FManageType,FLenDecimal,FCubageDecimal,FWeightDecimal,FIsCharSourceItem,
FCtrlType,FCtrlStraregy,FContainerName,FKanBanCapability,FBatchSplitDays,FBatchSplit,FDefaultReadyLoc,FSPIDReady,FStartService,FMakeFile,FIsFix,FTtermOfService,FTtermOfUsefulTime,FIsFixedReOrder,FOnlineShopPNo,FOnlineShopPName,FForbbitBarcodeEdit,FDSManagerID,FUnitPackageNumber,FOrderDept,FAuxInMrpCal,FProductDesigner,
FPinYin,FPY,FIsSNBanDing,FIsSNRpt,FShortNumber,FNumber,FName,
FParentID,FItemID) 
VALUES 
(
NULL,'',0,2 ,0,@FUnitGroupID,@FUnitID,@FOrderUnitID,@FSaleUnitID,@FProductUnitID,@FOrderUnitID,
0,'0',@FDefaultLoc,0,0,4,'0','1000','0',@FUseState,0,NULL,0,NULL,NULL,NULL,0,
'0',1,'0',1,'0',1,0,'0','0','0',0,0,0,0,0,0,0,0,'0','0','0','0','0','0','0','0',
@FTrack,'0',4,@FAcctID,@FSaleAcctID,@FCostAcctID,0,0,0,'13',0,0,0,NULL,NULL,1,NULL,
@FPlanTrategy,@FPlanMode,@FOrderTrategy,0,0,0,0,'1','10000','1','0','0','1',1,1,0,0,0,0,0,0,0,0,'0',1,0,NULL,0,'0','0',0,'0','0','0','0',0,'0','0',0,0,0,'1','0','0','0','0','0',0,'0',0,0,0,0,0,0,
@FInspectionLevel,@FProChkMde,@FWWChkMde,@FSOChkMde,@FWthDrwChkMde,@FStkChkMde,@FOtherChkMde,0,0,0,9999,0,0,0,NULL,NULL,NULL,0,'0',NULL,NULL,'0','0','0','0',0,0,2,4,2,0,
@FCtrlType,0,NULL,1,0,0,0,0,0,0,0,0,0,1,NULL,NULL,0,0,'0',0,0,0,
@materialNamePinYin,@materialNamePY,0,0,(	SELECT RIGHT(@materialCode, CHARINDEX('.',REVERSE(@materialCode)) - 1)),@materialCode,@materialName,
@ParentId,@FItemID
)

-- 插入 log
 INSERT INTO t_Log (FDate,FUserID,FFunctionID,FStatement,FDescription,FMachineName,FIPAddress)
 VALUES (getdate(),@UserId,'A00701',5,'新建核算项目:'+@materialCode+' 核算项目类别:物料','admin','存储过程:sp_In3AddProductCode')

 -- 插入 t_BaseProperty 表数据
  Insert Into t_BaseProperty(FTypeID, FItemID, FCreateDate, FCreateUser, FLastModDate, FLastModUser, FDeleteDate, FDeleteUser)
 Values(3, @FItemID,getdate(),'admin', Null, Null, Null, Null)

 -- 插入 Access_t_ICItem 表数据
  Delete from Access_t_ICItem where FItemID=@FItemID
 Insert into Access_t_ICItem(FItemID,FParentIDX,FDataAccessView,FDataAccessEdit,FDataAccessDelete)
 Values(@FItemID,@ParentId,convert(varbinary(7200),REPLICATE(char(255),100)),convert(varbinary(7200),REPLICATE(char(255),100)),convert(varbinary(7200),REPLICATE(char(255),100)))

  -- 审核 
  Update t_Item set FChkUserID= @UserId Where Isnull(FChkUserID,'')='' And FItemID in (@FItemID)
	DECLARE @param VARCHAR(100) = '(param)';
	SET @param = REPLACE(@param, 'param', CAST(@FItemID AS VARCHAR));
	exec p_BASE_AutoCheckParentItems @UserId,@param

	-- 提交事务
    COMMIT TRANSACTION; 
	set @result=@materialCode;
return 1;
	--print @materialCode
	END TRY

	BEGIN CATCH
	IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION; -- 回滚事务
        SELECT ERROR_MESSAGE(); -- 输出错误信息（如果有）
set @result='false'; 
return 0;
    END CATCH
END;
GO