没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
USE [DZTWMS]
GO
/****** Object: StoredProcedure [dbo].[JobTmpAPIDataGet] Script Date: 2019/8/2 12:16:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
对传入接口TmpAPIData的数据进行处理
数据类型:0商品分类;1销售单;2退货单;3退厂单;4采购单;5盘库单; 7客户;8供应商;9商品基础;10商品包装;11销售单撤销;12退货单撤销;13退厂撤销;14采购撤销;15 盘库单撤销;
--210,销售单回传-已取消 211,销售单回传-已同步 212,销售单回传-装车完成发货中 213,销售单回传-已完成220,退货单回传-已取消 221,司机带回商品回传230,退厂单回传-已取消 231,退厂单回传-完成 240,采购单回传-已取消 241,采购单回传-验货完成 250,盘库单回传-已取消 251,盘库单回传-完成 26,报损回传
*/
ALTER PROC [dbo].[JobTmpAPIDataGet]
AS
BEGIN TRY
BEGIN TRAN [JobTmpAPIDataGet]
DECLARE @result int,@tipMsg varchar(100),@result2 int,@tipMsg2 varchar(100),@cnt int
DECLARE @n int,@rows int
DECLARE @Id int,@DataType int=0,@SheetId int,@OwnerId int,@APIData varchar(max),@WareHouseId int,@DetailList varchar(7000)
create table #tmp_TmpAPIData(num int IDENTITY(1,1),Id int,DataType int,OwnerId int,WareHouseId int,SheetId int,APIData varchar(max))
INSERT INTO #tmp_TmpAPIData(Id,DataType,OwnerId,WareHouseId,SheetId,APIData)
select top 500 Id,DataType,OwnerId,WareHouseId,SheetId,APIData
from TmpAPIData where OutIn=1 and Status=0 order by DataType
SELECT @rows =@@rowcount
GO
/****** Object: StoredProcedure [dbo].[JobTmpAPIDataGet] Script Date: 2019/8/2 12:16:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
对传入接口TmpAPIData的数据进行处理
数据类型:0商品分类;1销售单;2退货单;3退厂单;4采购单;5盘库单; 7客户;8供应商;9商品基础;10商品包装;11销售单撤销;12退货单撤销;13退厂撤销;14采购撤销;15 盘库单撤销;
--210,销售单回传-已取消 211,销售单回传-已同步 212,销售单回传-装车完成发货中 213,销售单回传-已完成220,退货单回传-已取消 221,司机带回商品回传230,退厂单回传-已取消 231,退厂单回传-完成 240,采购单回传-已取消 241,采购单回传-验货完成 250,盘库单回传-已取消 251,盘库单回传-完成 26,报损回传
*/
ALTER PROC [dbo].[JobTmpAPIDataGet]
AS
BEGIN TRY
BEGIN TRAN [JobTmpAPIDataGet]
DECLARE @result int,@tipMsg varchar(100),@result2 int,@tipMsg2 varchar(100),@cnt int
DECLARE @n int,@rows int
DECLARE @Id int,@DataType int=0,@SheetId int,@OwnerId int,@APIData varchar(max),@WareHouseId int,@DetailList varchar(7000)
create table #tmp_TmpAPIData(num int IDENTITY(1,1),Id int,DataType int,OwnerId int,WareHouseId int,SheetId int,APIData varchar(max))
INSERT INTO #tmp_TmpAPIData(Id,DataType,OwnerId,WareHouseId,SheetId,APIData)
select top 500 Id,DataType,OwnerId,WareHouseId,SheetId,APIData
from TmpAPIData where OutIn=1 and Status=0 order by DataType
SELECT @rows =@@rowcount
set @n=1
while @n<=@rows
BEGIN
SELECT @Id=Id,@APIData=APIData,@OwnerId=OwnerId,@SheetId=SheetId,@WareHouseId=WareHouseId,@DataType=DataType
FROM #tmp_TmpAPIData WHERE num=@n
--定义一些共同的变量
DECLARE @WareHouseName varchar(50),@OwnerName varchar(50),@Name varchar(250),@Remark nvarchar(1000),@OrderId varchar(20),@Status int,
@CustomNo varchar(20),@CustomName varchar(150),@Phone char(15),@PCANames nvarchar(300),@Addr nvarchar(200),@SalesMan varchar(20),@SalesMobile char(15),@OrderNum varchar(150),
@SupplierNo varchar(20),@Amount decimal(10,2)
SELECT @OwnerName=[Name],@WareHouseName=WareHouseName FROM [Owner] WHERE Id=@OwnerId
--商户订单
if(@DataType=1)
BEGIN
DECLARE @IsCollect tinyint,@DeliverType tinyint,@CostTotal decimal(10,2),@Total decimal(10,2), @Freight decimal(5),@StartTime tinyint,@EndTime tinyint
select @OrderNum=JSON_VALUE(value,'$.OrderNum'),@IsCollect=JSON_VALUE(value,'$.IsCollect'),@DeliverType=JSON_VALUE(value,'$.DeliverType'),@CustomNo=JSON_VALUE(value,'$.CustomNo'),@CustomName=JSON_VALUE(value,'$.CustomName'),@Phone=JSON_VALUE(value,'$.Phone'),@PCANames=JSON_VALUE(value,'$.PCANames'),@Addr=JSON_VALUE(value,'$.Addr'),@SalesMan=JSON_VALUE(value,'$.SalesMan'),@SalesMobile=JSON_VALUE(value,'$.SalesMobile'),@CostTotal=JSON_VALUE(value,'$.CostTotal'),@Total=JSON_VALUE(value,'$.Total'),@Freight=JSON_VALUE(value,'$.Freight'),@StartTime=JSON_VALUE(value,'$.StartTime'),@EndTime=JSON_VALUE(value,'$.EndTime'),@Remark=JSON_VALUE(value,'$.Remark'),@DetailList=JSON_QUERY(VALUE,'$.ItemList') from openjson(@APIData)
EXEC [AddEditSalesOrder] @WareHouseId,@OwnerId,@SheetId,@OrderNum,@IsCollect,@DeliverType,@CustomNo,@CustomName,@Phone,@PCANames,@Addr,@SalesMan,@SalesMobile,@CostTotal,@Total,@Freight,@StartTime,@EndTime,@Remark,@result OUTPUT,@tipMsg OUTPUT
if(@result>0)
begin
set @OrderId=@SheetId
--DECLARE @ItemBaseId varchar(20),@Spec decimal(10,2),@Unit varchar(4),@Cost decimal(10,2),@Price decimal(10,2),@BuyNum decimal(10,2)
--select @SheetId=JSON_VALUE(value,'$.SheetId'),@ItemBaseId=JSON_VALUE(value,'$.ItemBaseId'),@Spec=JSON_VALUE(value,'$.Spec'),@Unit=JSON_VALUE(value,'$.Unit'),@Cost=JSON_VALUE(value,'$.Cost'),@Price=JSON_VALUE(value,'$.Price'),@BuyNum=JSON_VALUE(value,'$.BuyNum')
select JSON_VALUE(value,'$.SheetId') SheetId,JSON_VALUE(value,'$.ItemBaseId') ItemBaseId,JSON_VALUE(value,'$.Spec') Spec,JSON_VALUE(value,'$.Unit') Unit,JSON_VALUE(value,'$.Cost') Cost,JSON_VALUE(value,'$.Price') Price,JSON_VALUE(value,'$.BuyNum') BuyNum
into #tmp_saleorderdetail from openjson(@DetailList)
INSERT INTO SalesOrderDetail(WareHouseId,OwnerId,SheetId,OrderId,OrderNum,WmsOrderId,ItemBaseId,ItemId,Item,ItemType,Spec,Unit,Cost,Price,BuyNum)
select @WareHouseId,@OwnerId,a.SheetId,@OrderId,@OrderNum,@result,a.ItemBaseId,b.Id,b.Name,b.ItemType,a.Spec,a.Unit,a.Cost,a.Price,a.BuyNum
from #tmp_saleorderdetail a inner join ItemBase b on a.ItemBaseId=b.SheetId and b.OwnerId=@OwnerId and b.WareHouseId=@WareHouseId
while @n<=@rows
BEGIN
SELECT @Id=Id,@APIData=APIData,@OwnerId=OwnerId,@SheetId=SheetId,@WareHouseId=WareHouseId,@DataType=DataType
FROM #tmp_TmpAPIData WHERE num=@n
--定义一些共同的变量
DECLARE @WareHouseName varchar(50),@OwnerName varchar(50),@Name varchar(250),@Remark nvarchar(1000),@OrderId varchar(20),@Status int,
@CustomNo varchar(20),@CustomName varchar(150),@Phone char(15),@PCANames nvarchar(300),@Addr nvarchar(200),@SalesMan varchar(20),@SalesMobile char(15),@OrderNum varchar(150),
@SupplierNo varchar(20),@Amount decimal(10,2)
SELECT @OwnerName=[Name],@WareHouseName=WareHouseName FROM [Owner] WHERE Id=@OwnerId
--商户订单
if(@DataType=1)
BEGIN
DECLARE @IsCollect tinyint,@DeliverType tinyint,@CostTotal decimal(10,2),@Total decimal(10,2), @Freight decimal(5),@StartTime tinyint,@EndTime tinyint
select @OrderNum=JSON_VALUE(value,'$.OrderNum'),@IsCollect=JSON_VALUE(value,'$.IsCollect'),@DeliverType=JSON_VALUE(value,'$.DeliverType'),@CustomNo=JSON_VALUE(value,'$.CustomNo'),@CustomName=JSON_VALUE(value,'$.CustomName'),@Phone=JSON_VALUE(value,'$.Phone'),@PCANames=JSON_VALUE(value,'$.PCANames'),@Addr=JSON_VALUE(value,'$.Addr'),@SalesMan=JSON_VALUE(value,'$.SalesMan'),@SalesMobile=JSON_VALUE(value,'$.SalesMobile'),@CostTotal=JSON_VALUE(value,'$.CostTotal'),@Total=JSON_VALUE(value,'$.Total'),@Freight=JSON_VALUE(value,'$.Freight'),@StartTime=JSON_VALUE(value,'$.StartTime'),@EndTime=JSON_VALUE(value,'$.EndTime'),@Remark=JSON_VALUE(value,'$.Remark'),@DetailList=JSON_QUERY(VALUE,'$.ItemList') from openjson(@APIData)
EXEC [AddEditSalesOrder] @WareHouseId,@OwnerId,@SheetId,@OrderNum,@IsCollect,@DeliverType,@CustomNo,@CustomName,@Phone,@PCANames,@Addr,@SalesMan,@SalesMobile,@CostTotal,@Total,@Freight,@StartTime,@EndTime,@Remark,@result OUTPUT,@tipMsg OUTPUT
if(@result>0)
begin
set @OrderId=@SheetId
--DECLARE @ItemBaseId varchar(20),@Spec decimal(10,2),@Unit varchar(4),@Cost decimal(10,2),@Price decimal(10,2),@BuyNum decimal(10,2)
--select @SheetId=JSON_VALUE(value,'$.SheetId'),@ItemBaseId=JSON_VALUE(value,'$.ItemBaseId'),@Spec=JSON_VALUE(value,'$.Spec'),@Unit=JSON_VALUE(value,'$.Unit'),@Cost=JSON_VALUE(value,'$.Cost'),@Price=JSON_VALUE(value,'$.Price'),@BuyNum=JSON_VALUE(value,'$.BuyNum')
select JSON_VALUE(value,'$.SheetId') SheetId,JSON_VALUE(value,'$.ItemBaseId') ItemBaseId,JSON_VALUE(value,'$.Spec') Spec,JSON_VALUE(value,'$.Unit') Unit,JSON_VALUE(value,'$.Cost') Cost,JSON_VALUE(value,'$.Price') Price,JSON_VALUE(value,'$.BuyNum') BuyNum
into #tmp_saleorderdetail from openjson(@DetailList)
INSERT INTO SalesOrderDetail(WareHouseId,OwnerId,SheetId,OrderId,OrderNum,WmsOrderId,ItemBaseId,ItemId,Item,ItemType,Spec,Unit,Cost,Price,BuyNum)
select @WareHouseId,@OwnerId,a.SheetId,@OrderId,@OrderNum,@result,a.ItemBaseId,b.Id,b.Name,b.ItemType,a.Spec,a.Unit,a.Cost,a.Price,a.BuyNum
from #tmp_saleorderdetail a inner join ItemBase b on a.ItemBaseId=b.SheetId and b.OwnerId=@OwnerId and b.WareHouseId=@WareHouseId
剩余9页未读,继续阅读
资源评论
luhongbo8113
- 粉丝: 0
- 资源: 7
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- AIS2024 valid
- 最入门的爬虫代码 python.docx
- 爬虫零基础入门-爬取天气预报.pdf
- 最通俗易懂的 MongoDB 非结构化文档存储数据库教程.zip
- 以mongodb为数据库的订单物流小项目.zip
- 腾讯云-mongodb数据库, 项目部署.zip
- 腾讯 APIJSON 的 MongoDB 数据库插件.zip
- 理解非关系型数据库和关系型数据库的区别.zip
- 操作简单的Mongodb网页web管理工具,基于Spring Boot2.0支持mongodb集群.zip
- tms-mongodb-web,提供访问mongodb数据的REST API和可灵活扩展的mongodb web 客户端.zip
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功