ALTER PROCEDURE [dbo].[AddOrderBatchPayment]		
	@OrderNos nvarchar(max),
	@TotalPaymentAmount decimal(18,2),	
	@PaymentType nvarchar(50),
	@PaymentMemo nvarchar(150),	 
	@UserName nvarchar(150),
	@Result int output --1:支付金额大于未付金额 2:不是同一个客户
	
AS
BEGIN
	declare @BalanceReduceAmount decimal(18,2)
	declare @UnPaidAmount decimal(18,2)
	declare @Sql nvarchar(max),@CustomerNo nvarchar(50),@Balance decimal(18,2),@IsSystemReversed bit
	set @Balance=0
	set @IsSystemReversed=0
	
	create table #NosTable(OrderNo nvarchar(50))
	set @Sql='insert into #NosTable select ''' +  replace(@OrderNos,',',''' union select ''') + ''''
	exec(@Sql)
	
	select @UnPaidAmount=sum(Amount-ISNULL(Paid,0)) from [Order] where OrderNo in (select OrderNo from #NosTable) and IsActive=1
	
	
	if @UnPaidAmount<@TotalPaymentAmount
	begin
		set @Result=1
		drop table #NosTable
		return
	end
	
	select distinct @CustomerNo=CustomerNo from [Order] where OrderNo in (select OrderNo from #NosTable) and IsActive=1
	if @@rowcount!=1
	begin
		set @Result=2
		drop table #NosTable
		return
	end	
	 
	select @IsSystemReversed=IsSystemReversed from PaymentType where TypeName=@PaymentType
	 
	if @IsSystemReversed=1
	begin
		select @Balance=isnull(Balance,0) from Customer where CustomerNo=@CustomerNo		
		if @Balance<@TotalPaymentAmount
		begin
			set @Result=3 --账户余额不足
			drop table #NosTable
			return
		end
		set @BalanceReduceAmount=@TotalPaymentAmount
	end
	
	
	declare @OrderNo varchar(50) ,@Amount decimal(18,2),@Paid decimal(18,2),@UnPaid decimal(18,2)
	declare @Now datetime
	set @Now=GETDATE()
	declare CurOrder cursor for select OrderNo,Amount,Paid from [Order] where OrderNo in (select OrderNo from #NosTable) and IsActive=1 order by TransactionDate 
	OPEN CurOrder
	
	FETCH NEXT FROM CurOrder INTO @OrderNo, @Amount,@Paid
	
	BEGIN TRY
	
		begin tran tran_pay
		
		WHILE @@FETCH_STATUS=0
		begin			
			set @UnPaid=@Amount-ISNULL(@Paid,0)
			
			if @UnPaid=0 or @TotalPaymentAmount=0 -- 余额已分配完毕或此订单无需支付
			begin		
				FETCH NEXT FROM CurOrder INTO @OrderNo, @Amount,@Paid
				continue			
			end
			
			if @TotalPaymentAmount>=@UnPaid --余额足够完全支付
			begin			 
				update [Order] set Paid=Amount,PaymentStatus=3 where OrderNo=@OrderNo
				insert into OrderPayment(OrderNo,Amount,PaymentType,Creator,CreateTime,Memo)
					values(@OrderNo,@UnPaid,@PaymentType,@UserName,@Now,@PaymentMemo)				
				set @TotalPaymentAmount=@TotalPaymentAmount-@UnPaid
			end
			else 
			begin
				-- 余额仅能部分支付
				update [Order] set Paid=ISNULL(Paid,0) + @TotalPaymentAmount,PaymentStatus=2 where OrderNo=@OrderNo
				insert into OrderPayment(OrderNo,Amount,PaymentType,Creator,CreateTime,Memo)
					values(@OrderNo,@TotalPaymentAmount,@PaymentType,@UserName,@Now,@PaymentMemo)	
				set @TotalPaymentAmount=0 --无更多余额可供支付
				break
			end 			
			FETCH NEXT FROM CurOrder INTO @OrderNo, @Amount,@Paid
		end
		if @TotalPaymentAmount=0
		begin
			if @IsSystemReversed=1
			begin
				update [Customer] set Balance=Balance - @BalanceReduceAmount where CustomerNo=@CustomerNo
			end 
			commit tran tran_pay
			set @Result=0
		end			
		else
		begin
			ROLLBACK TRAN tran_pay
			set @Result=4
		end
	end try
	begin catch
		IF @@TRANCOUNT > 0 
		ROLLBACK TRAN tran_pay
	end catch	
	
	CLOSE CurOrder
	DEALLOCATE CurOrder 	
	drop table #NosTable	
	
end
go


create PROCEDURE [dbo].[AddFactoryOrderBatchPayment]		
	@FactoryOrderNos nvarchar(max),
	@TotalPaymentAmount decimal(18,2),	
	@PaymentType nvarchar(50),
	@PaymentMemo nvarchar(150),	 
	@UserName nvarchar(150),
	@Result int output --1:支付金额大于未付金额 2:不是同一个外协商
	
AS
BEGIN
	declare @UnPaidAmount decimal(18,2)
	declare @Sql nvarchar(max),@FactoryNo nvarchar(50),@IsSystemReversed bit
		
	create table #NosTable(FactoryOrderNo nvarchar(50))
	set @Sql='insert into #NosTable select ''' +  replace(@FactoryOrderNos,',',''' union select ''') + ''''
	exec(@Sql)
	
	select @UnPaidAmount=sum(Amount-ISNULL(Paid,0)) from [FactoryOrder] where FactoryOrderNo in (select FactoryOrderNo from #NosTable) and IsDeleted=0
	
	
	if @UnPaidAmount<@TotalPaymentAmount
	begin
		set @Result=1
		drop table #NosTable
		return
	end
	 
	select distinct @FactoryNo=FactoryNo from [FactoryOrder] where FactoryOrderNo in (select FactoryOrderNo from #NosTable) and IsDeleted=0
	 
	if @@rowcount!=1
	begin
		set @Result=2
		drop table #NosTable
		return
	end	
	
	declare @FactoryOrderNo varchar(50) ,@Amount decimal(18,2),@Paid decimal(18,2),@UnPaid decimal(18,2)
	declare @Now datetime
	set @Now=GETDATE()
	declare CurOrder cursor for select FactoryOrderNo,Amount,Paid from [FactoryOrder] where FactoryOrderNo in (select FactoryOrderNo from #NosTable) and IsDeleted=0 order by TransactionDate 
	OPEN CurOrder
	
	FETCH NEXT FROM CurOrder INTO @FactoryOrderNo, @Amount,@Paid
	
	BEGIN TRY
	
		begin tran tran_pay
		
		WHILE @@FETCH_STATUS=0
		begin			
			set @UnPaid=@Amount-ISNULL(@Paid,0)
			
			if @UnPaid=0 or @TotalPaymentAmount=0 -- 余额已分配完毕或此订单无需支付
			begin		
				FETCH NEXT FROM CurOrder INTO @FactoryOrderNo, @Amount,@Paid
				continue			
			end
			
			if @TotalPaymentAmount>=@UnPaid --余额足够完全支付
			begin			 
				update [FactoryOrder] set Paid=Amount,PaymentStatus=3 where FactoryOrderNo=@FactoryOrderNo
				insert into FactoryOrderPayment(FactoryOrderNo,Amount,PaymentType,Creator,CreateTime,Memo)
					values(@FactoryOrderNo,@UnPaid,@PaymentType,@UserName,@Now,@PaymentMemo)				
				set @TotalPaymentAmount=@TotalPaymentAmount-@UnPaid
			end
			else 
			begin
				-- 余额仅能部分支付
				update [FactoryOrder] set Paid=ISNULL(Paid,0) + @TotalPaymentAmount,PaymentStatus=2 where FactoryOrderNo=@FactoryOrderNo
				insert into FactoryOrderPayment(FactoryOrderNo,Amount,PaymentType,Creator,CreateTime,Memo)
					values(@FactoryOrderNo,@TotalPaymentAmount,@PaymentType,@UserName,@Now,@PaymentMemo)	
				set @TotalPaymentAmount=0 --无更多余额可供支付
				break
			end 			
			FETCH NEXT FROM CurOrder INTO @FactoryOrderNo, @Amount,@Paid
		end
		if @TotalPaymentAmount=0
		begin
			commit tran tran_pay
			set @Result=0
		end			
		else
		begin
			ROLLBACK TRAN tran_pay
			set @Result=4
		end
	end try
	begin catch
		IF @@TRANCOUNT > 0 
		ROLLBACK TRAN tran_pay
	end catch	
	
	CLOSE CurOrder
	DEALLOCATE CurOrder 	
	drop table #NosTable	
	
end
go


create PROCEDURE [dbo].[AddStockInBatchPayment]		
	@StockInIds nvarchar(max),
	@TotalPaymentAmount decimal(18,2),	
	@PaymentType nvarchar(50),
	@PaymentMemo nvarchar(150),	 
	@UserName nvarchar(150),
	@Result int output --1:支付金额大于未付金额 2:不是同一个外协商
	
AS
BEGIN
	declare @UnPaidAmount decimal(18,2)
	declare @Sql nvarchar(max),@SupplierId int,@IsSystemReversed bit
		
	create table #NosTable(Id int)
	set @Sql='insert into #NosTable select ''' +  replace(@StockInIds,',',''' union select ''') + ''''
	exec(@Sql)
	
	select @UnPaidAmount=sum(Amount-ISNULL(Paid,0)) from [StockIn] where Id in (select Id from #NosTable)
	
	
	if @UnPaidAmount<@TotalPaymentAmount
	begin
		set @Result=1
		drop table #NosTable
		return
	end
	
	select distinct @SupplierId=SupplierId from [StockIn] where Id in (select Id from #NosTable)
	if @@rowcount!=1
	begin
		set @Result=2
		drop table #NosTable
		return
	end	
	
	declare @Id varchar(50) ,@Amount decimal(18,2),@Paid decimal(18,2),@UnPaid decimal(18,2)
	declare @Now datetime
	set @Now=GETDATE()
	declare CurOrder cursor for select Id,Amount,Paid from [StockIn] where Id in (select Id from #NosTable)  order by StockInTime 
	OPEN CurOrder
	
	FETCH NEXT FROM CurOrder INTO @Id, @Amount,@Paid
	
	BEGIN TRY
	
		begin tran tran_pay
		
		WHILE @@FETCH_STATUS=0
		begin			
			set @UnPaid=@Amount-ISNULL(@Paid,0)
			
			if @UnPaid=0 or @TotalPaymentAmount=0 -- 余额已分配完毕或此订单无需支付
			begin		
				FETCH NEXT FROM CurOrder INTO @Id, @Amount,@Paid
				continue			
			end
			
			if @TotalPaymentAmount>=@UnPaid --余额足够完全支付
			begin			 
				update [StockIn] set Paid=Amount,PaymentStatus=3 where Id=@Id
				insert into StockInPayment(StockInId,Amount,PaymentType,Creator,CreateTime,Memo)
					values(@Id,@UnPaid,@PaymentType,@UserName,@Now,@PaymentMemo)				
				set @TotalPaymentAmount=@TotalPaymentAmount-@UnPaid
			end
			else 
			begin
				-- 余额仅能部分支付
				update [StockIn] set Paid=ISNULL(Paid,0) + @TotalPaymentAmount,PaymentStatus=2 where Id=@Id
				insert into StockInPayment(StockInId,Amount,PaymentType,Creator,CreateTime,Memo)
					values(@Id,@TotalPaymentAmount,@PaymentType,@UserName,@Now,@PaymentMemo)	
				set @TotalPaymentAmount=0 --无更多余额可供支付
				break
			end 			
			FETCH NEXT FROM CurOrder INTO @Id, @Amount,@Paid
		end
		if @TotalPaymentAmount=0
		begin
			commit tran tran_pay
			set @Result=0
		end			
		else
		begin
			ROLLBACK TRAN tran_pay
			set @Result=4
		end
	end try
	begin catch
		IF @@TRANCOUNT > 0 
		ROLLBACK TRAN tran_pay
	end catch	
	
	CLOSE CurOrder
	DEALLOCATE CurOrder 	
	drop table #NosTable	
	
end
go

insert into Permission
values ('P0410','04','客户欠款统计',0) 
update [User] set [Permissions]=[PERMISSIONS]+',P0410' where CHARINDEX('P0404',[Permissions])>0 or IsAdmin=1
go


create procedure UpdateDomainUrl
	@OldUrl nvarchar(250),
	@NewUrl nvarchar(250)
as
begin
	update OrderAttachment set FileUrl= REPLACE(FileUrl,@OldUrl,@NewUrl) where FileUrl like '%'+@OldUrl+'%'
	update OrderAttachment set ThumbnailUrl= REPLACE(ThumbnailUrl,@OldUrl,@NewUrl) where ThumbnailUrl like '%'+@OldUrl+'%'
	update FactoryOrderAttachment set FileUrl= REPLACE(FileUrl,@OldUrl,@NewUrl) where FileUrl like '%'+@OldUrl+'%'
	update FactoryOrderAttachment set ThumbnailUrl= REPLACE(ThumbnailUrl,@OldUrl,@NewUrl) where ThumbnailUrl like '%'+@OldUrl+'%'
	
end
go

update configuration set SettingValue='4.9.8' where SettingName='DatabaseVersion'
 go
 
 