dts = new DataTable();
dts.Columns.Add("Code", Type.GetType("System.String"));
dts.Columns.Add("Data", Type.GetType("System.String"));
dts.Rows.Add(new object[] { "SPType", "640" });
dts.Rows.Add(new object[] { "CallID", GridView_packcs.Rows[e.RowIndex].Cells[2].Text.Trim() });
dts.Rows.Add(new object[] { "Editor", userid.Trim().ToUpper() });
SqlConnection conn = new SqlConnection(ReportStr);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "dbo.usp_PackingCallMaterial_Save";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@InPutData", SqlDbType.Structured));
cmd.Parameters.Add(new SqlParameter("@RetnCode", SqlDbType.VarChar, 1));
cmd.Parameters.Add(new SqlParameter("@RetnInfo", SqlDbType.VarChar, 50));
cmd.Parameters["@InPutData"].Value = dts;
cmd.Parameters["@RetnCode"].Direction = ParameterDirection.Output;
cmd.Parameters["@RetnInfo"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
string fd = cmd.Parameters["@RetnInfo"].Value.ToString().Trim();
cmd.Dispose();
Response.Write("<script>alert('" + fd + "');</script>");
}
catch (Exception i)
{
Response.Write("<script>alert('" + i.Message + "');</script>");
//Response.Write("<script>alert('保存至數據庫失敗');</script>");
}
finally
{
conn.Close();
}
SP主要代码
USE [SCC]
GO
/****** Object: StoredProcedure [dbo].[usp_PackingCallMaterial_Save] Script Date: 7/9/2019 8:27:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*============================================================================================================================
2019011101 2019/01/11 mhx 倉庫叫料系統數據保存
declare @InPutData CodeDataType , @RetnCode varchar(1),@RetnInfo varchar(100)
insert @InPutData select 'SPType','100'
insert @InPutData select 'Area','100'
insert @InPutData select 'PdLine','100'
exec usp_PackingCallMaterial_Save @InPutData, @RetnCode output,@RetnInfo output
select @RetnCode ,@RetnInfo
===========================================================================================================================*/
CREATE Proc [dbo].[usp_PackingCallMaterial_Save] @InPutData CodeDataType READONLY , @RetnCode varchar(1) output, @RetnInfo nvarchar(50) output AS
declare
@SPType varchar(4) , --保存那個界面的數據
@Area nvarchar(10) , -- 區域
@PdLine varchar(5), -- 線別
@Model varchar(12), -- WB 機型
@Type varchar(10), --@Model 的Type
@Pn varchar(12), -- 包材料號
@BOMQty float, -- BOM 中包材用量
@PO varchar(20), -- FIS 工單
@CallID varchar(20), -- 叫料ID
@QTY int, --叫料套數
@Editor varchar(10),
@PlantCode varchar(5), --倉別
@PoQty int, -- FIS 工单的Qty
@DataId int
select @SPType=Data from @InPutData where Code='SPType'
select @Area=Data from @InPutData where Code='Area'
select @PdLine=Data from @InPutData where Code='PdLine'
select @Model=Data from @InPutData where Code='Model'
select @Type=Data from @InPutData where Code='Type'
select @Pn=Data from @InPutData where Code='Pn'
select @BOMQty=Data from @InPutData where Code='BOMQty'
select @PO =Data from @InPutData where Code='PO'
select @PoQty=Data from @InPutData where Code='PoQty'
select @CallID=Data from @InPutData where Code='CallID'
select @QTY =Data from @InPutData where Code='QTY'
select @Editor=Data from @InPutData where Code='Editor'
select @PlantCode=Data from @InPutData where Code='PlantCode'
if @SPType not in ('100','200','300','400','500','501','600','610','620','630','640','700','710','800','900') begin
select @RetnCode= '1',@RetnInfo=N'未知界面'
return
end
if(@SPType='100') begin-- 區域維護資料保存
if @PdLine='' or @Area='' or @PdLine='' or @Editor='' begin
select @RetnCode= '1',@RetnInfo=N'数据不能为空'
return
end
if exists (select 1 from MFG_Area_Line where PdLine=@PdLine AND InUse='1' ) begin
select @RetnCode= '1',@RetnInfo=@PdLine+N'已維護區域'
return
end
else begin
insert MFG_Area_Line (Area ,PdLine ,Editor ,InUse,Cdt,Udt) values(@Area,@PdLine,@Editor,'1',GETDATE(),getdate())
select @RetnCode='0',@RetnInfo=@PdLine+'數據保存成功'
return
end
end
if(@SPType='200') begin -- BOM PARTS 維護
if (@Model='' or @Type='' or @Editor='') begin
select @RetnCode= '1',@RetnInfo=N'數據不能未空'
return
end
if exists (select 1 from SCC.dbo.MFG_Packing_BomParts where PN=@Model and InUse='1') begin
select @RetnCode= '1',@RetnInfo=@Model+'已維護'
return
end
insert SCC.dbo.MFG_Packing_BomParts (PN,Type,Descr,InUse,Editor,Cdt,Udt) Values( @Model,@Type,'','1',@Editor,getdate(),getdate())
select @RetnCode= '1',@RetnInfo=@Model+'數據保存成功'
return
end
if(@SPType='300') begin --維護Bom關係 SP(300)
if (@Model='' or @Pn='' or @BOMQty='' or @Editor='' or @PlantCode='') begin
select @RetnCode= '1',@RetnInfo=N'數據不能未空'
return
end
if exists(select 1 from SCC..MFG_Packing_Bom where InUse ='1' and Model=@Model and PN=@Pn) begin
select @RetnCode= '1',@RetnInfo=@Model+'的'+@Pn+'已維護'
return
end
insert SCC..MFG_Packing_Bom(Model,PN,Qty,InUse,Editor ,Cdt,Plant ) values(@Model,@Pn,@BOMQty,'1',@Editor,getdate(),@PlantCode)
end
if(@SPType='400') begin --產線包材需求输入
if ( @Model='' or @PO='' or @PoQty='' or @QTY='') begin
select @RetnCode= '1',@RetnInfo=N'數據不能未空'
return
end
if exists (select 1 from MFG_CallId_Status where Status='10') begin
select @CallID=CallId FROM MFG_CallId_Status where Status='10'
end
else begin
select @CallID=convert(varchar,getdate(),112)+ replace(convert(varchar,getdate(),114) ,':','')
insert MFG_CallId_Status_Log values( @CallID,'10',getdate())
insert MFG_CallId_Status values(@CallID ,'10',getdate(),getdate())
end
if exists (select 1 from MFG_Call_PackingMaterial where WkNo=@PO) begin
select @PoQty=Qty from PCA..PCA_LOT WHERE PcaNo=@PO
if ((select sum(WkNo_CallQty)+@QTY from MFG_Call_PackingMaterial where WkNo=@PO)>@PoQty ) begin
select @RetnCode='0',@RetnInfo=@PO+'叫料數量已超過工單總數量!!'
return
end
end
insert MFG_Call_PackingMaterial select @CallID,@Model,@PO,@PoQty,'',@QTY,'1',@Editor,getdate(),getdate()
select @DataId=max(DataId) from MFG_Call_PackingMaterial
insert MFG_Call_PackingMaterial_Detail
select DataId,@PdLine,@Model,@PO,'0',b.PN,b.Qty,@QTY, CEILING(b.Qty*@QTY),'1',@Editor ,GETDATE() from MFG_Call_PackingMaterial a, MFG_Packing_Bom b where a.Model=b.Model
and CallId =@CallID and a.Model=@Model and WkNo = @PO and WkNo_CallQty =@QTY and a.InUse ='1'and b.InUse ='1' and a.DataId =@DataId
select @RetnCode= '1',@RetnInfo=@Model+'數據保存成功!!'
return
end
if(@SPType ='500') begin --倉庫包材備料sp 500
if (@CallID='' ) begin
select @RetnCode= '1',@RetnInfo=N'数据不能为空'
return
end
update dbo.MFG_CallId_Status set Status='2',Udt=getdate() where CallId=@CallID
insert MFG_CallId_Status_Log values( @CallID,'2',getdate())
/*20190612 www 倉庫要求初始備料數量同叫料數量一致,減少輸入*/
UPDATE MFG_Stock_PackingMaterial SET Stock_Qty=Call_Qty WHERE CallId=@CallID
--insert MFG_Stock_PackingMaterial(CallId,Area,PN,Call_Qty,Stock_Qty,Editor,InUse ,Cdt,Udt)
--SELECT b.CallId,c.Area,a.PN,SUM(a.Call_Qty)Call_Qty,'','FIS','1',getdate(),getdate() FROM
-- MFG_Call_PackingMaterial_Detail a,MFG_Call_PackingMaterial b,dbo.MFG_Area_Line c WHERE b.CallId=@CallID and a.DataId=b.DataId and a.Line=c.PdLine and c.InUse=1 and a.InUse=1 and b.InUse=1
-- GROUP BY a.PN,b.CallId,c.Area ORDER BY c.Area,a.PN
--MFG_Call_PackingMaterial_Detail
select @RetnCode= '1',@RetnInfo=@Model+'數據保存成功'
return
END
if(@SPType ='501') begin --倉庫包材備料完成sp 501
if (@CallID='' ) begin
select @RetnCode= '1',@RetnInfo=N'数据不能为空'
return
end
DECLARE @station VARCHAR(5)
SELECT @station=Status FROM MFG_CallId_Status where CallId=@CallID
IF @station='2' BEGIN
CREATE TABLE #wip
(
Area VARCHAR(10),
PN VARCHAR(15),
diff INT
)
INSERT #wip
SELECT Area,PN,SUM((Stock_Qty-Call_Qty))diff FROM MFG_Stock_PackingMaterial WHERE (Stock_Qty-Call_Qty)<>'0' AND CallId=@CallID GROUP BY Area,PN
UPDATE MFG_Call_PackingMaterial_Diff SET Diff=a.Diff+b.diff,Udt=GETDATE() FROM MFG_Call_PackingMaterial_Diff a, #wip b WHERE a.Area=b.Area and a.PN=b.PN and a.InUse='1'
DELETE #wip FROM MFG_Call_PackingMaterial_Diff a, #wip b WHERE a.Area=b.Area and a.PN=b.PN and a.InUse='1'
INSERT MFG_Call_PackingMaterial_Diff (Area,PN,Diff,Editor,InUse,Cdt,Udt)
SELECT Area,PN,diff,'FIS','1',GETDATE(),GETDATE() FROM #wip
END
update dbo.MFG_CallId_Status set Status='3',Udt=getdate() where CallId=@CallID
insert MFG_CallId_Status_Log values( @CallID,'3',getdate())
select @RetnCode= '1',@RetnInfo=@Model+'數據保存成功'
return
END
if(@SPType ='610') begin --產線包材叫料&領料界面sp 600 中的(查询)
if exists (select * from MFG_CallId_Status where CallId=@CallID and Status =10) begin
insert MFG_Stock_PackingMaterial(CallId,Area,PN,Call_Qty,Stock_Qty,Editor,InUse ,Cdt,Udt)
SELECT b.CallId,c.Area,a.PN,SUM(a.Call_Qty)Call_Qty,'','FIS','1',getdate(),getdate() FROM
MFG_Call_PackingMaterial_Detail a,MFG_Call_PackingMaterial b,dbo.MFG_Area_Line c WHERE b.CallId=@CallID and a.DataId=b.DataId and a.Line=c.PdLine and c.InUse=1 and a.InUse=1 and b.InUse=1
GROUP BY a.PN,b.CallId,c.Area ORDER BY c.Area,a.PN
end
return
end
if(@SPType ='620') begin --產線包材叫料&領料界面sp 600 中的(叫料)
if not exists (select 1 from MFG_CallId_Status where CallId=@CallID and Status ='10') begin
select @RetnCode= '1',@RetnInfo=@CallID+N'狀態不為10'
return
end
update MFG_CallId_Status set Status='1' where CallId=@CallID
insert MFG_CallId_Status_Log values(@CallID,'1',getdate())
select @RetnCode= '1',@RetnInfo='數據保存成功'
return
end
if(@SPType ='630') begin --產線包材叫料&領料界面sp 600 中的(領料)
if not exists (select 1 from MFG_CallId_Status where CallId=@CallID and Status ='3') begin
select @RetnCode= '1',@RetnInfo=@CallID+N'状态不为3'
return
end
update MFG_CallId_Status set Status='4' where CallId=@CallID
insert MFG_CallId_Status_Log values(@CallID,'4',getdate())
select @RetnCode= '1',@RetnInfo='數據保存成功'
return
end
if(@SPType ='640') begin --產線包材叫料&領料界面sp 600 中的(删除)
if not exists (select 1 from MFG_CallId_Status where CallId=@CallID and Status ='5') begin
select @RetnCode= '1',@RetnInfo=@CallID+N'狀態不為退回'
return
end
update MFG_CallId_Status set Status='0' where CallId=@CallID
insert MFG_CallId_Status_Log values(@CallID,'0',getdate())
select @RetnCode= '1',@RetnInfo='数据删除成功'
return
end
if(@SPType ='700') begin --產線材料WIP 700(查询按钮)
if @Pn='' begin
select * from dbo.MFG_Call_PackingMaterial_Diff where InUse ='1'
end
else begin
select * from dbo.MFG_Call_PackingMaterial_Diff where PN=@Pn and InUse ='1'
end
end
if(@SPType ='710') begin --產線材料WIP 700(新增加按钮)
insert MFG_Call_PackingMaterial_Diff values(@Area,@Pn ,@QTY,@Editor ,'1',getdate(),getdate())
select @RetnCode= '1',@RetnInfo='OK'
return
end
if(@SPType ='800') begin --產線倉庫叫料備料看板 800
select @RetnCode= '1',@RetnInfo=@Model+'數據保存成功'
return
end
if(@SPType ='900') begin --叫料备料信息查询 900
-- 1395T Model
if left(@Type,2)<>'WB' begin
SELECT top 1 a.CallId,a.Area,a.PN,a.Call_Qty,a.Stock_Qty,b.Status,a.Editor,a.Cdt,a.Udt FROM dbo.MFG_Stock_PackingMaterial a, dbo.MFG_CallId_Status b
WHERE a.CallId=@CallID AND a.InUse=1 AND a.CallId=b.CallId
end
else if left(@Type,2)='WB' begin
-- WB Model
SELECT top 1 b.CallId,c.Area,a.DataId,a.Model,a.WkNo,a.PN,b.WkNo_Qty,a.[Set],SUM(a.Call_Qty)Call_Qty FROM MFG_Call_PackingMaterial_Detail a,MFG_Call_PackingMaterial b,dbo.MFG_Area_Line c WHERE b.CallId='2018121901'
AND a.DataId=b.DataId AND a.Line=c.PdLine AND c.InUse=1 AND a.InUse=1 AND b.InUse=1 and CallId =@CallID
GROUP BY a.PN,b.CallId,c.Area,a.Model,a.DataId,a.WkNo,b.WkNo_Qty,a.[Set] ORDER BY a.DataId, a.Model,a.PN
end
select @RetnCode='1',@RetnInfo=@Model+'數據保存成功'
return
end
GO