abp(net core)+easyui+efcore實現倉儲管理系統目錄
abp(net core)+easyui+efcore實現倉儲管理系統——EasyUI前端頁面框架 (十八)
五、使用儲存過程建立單號
1. 在Visual Studio 2017的“解決方案資源管理器”中，右鍵單擊“ABP.TPLMS.Core”專案的“IRepositories”資料夾，在彈出選單中選擇“新增” > “類”。如下圖。
2．在彈出對話方塊中選擇“介面”， 將介面命名為 IOutStockOrderRepository，然後選擇“新增”。如下圖。
3.在IOutStockOrderRepository介面定義我們需要用到的方法，程式碼如下。
using Abp.Dependency;
using Abp.Domain.Repositories;
using ABP.TPLMS.Entitys;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
namespace ABP.TPLMS.IRepositories
{
public interface IOutStockOrderRepository: IRepository<OutStockOrder, int>, ITransientDependency
{
DbCommand CreateCommand(string commandText, CommandType commandType, params object[] parameters);
/// <summary>
/// 建立單號
/// </summary>
/// <param name="name">單證名稱程式碼</param>
/// <returns></returns>
string GetNo(string name);
/// <summary>
/// 匯入入庫貨物資訊
/// </summary>
/// <param name="ids">匯入庫單的ID集合</param>
/// <param name="no">單號</param>
void ImportInStockOrder(string ids, string no);
}
}
4. 在Visual Studio 2017的“解決方案資源管理器”中，右鍵單擊“ABP.TPLMS.EntityFrameworkCore”專案的“Repositories”資料夾，在彈出選單中選擇“新增” > “類”， 將類命名為 OutStockOrderRepository，並繼承IOutStockOrderRepository介面。實現介面中的方法。程式碼如下。
using Abp.Data;
using Abp.Dependency;
using Abp.EntityFrameworkCore;
using ABP.TPLMS.Entitys;
using ABP.TPLMS.IRepositories;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
namespace ABP.TPLMS.EntityFrameworkCore.Repositories
{
public class OutStockOrderRepository: TPLMSRepositoryBase<OutStockOrder, int>, IOutStockOrderRepository, ITransientDependency
{
private readonly IActiveTransactionProvider _transactionProvider;
public OutStockOrderRepository(IDbContextProvider<TPLMSDbContext> dbContextProvider) : base(dbContextProvider)
{ }
protected OutStockOrderRepository(IDbContextProvider<TPLMSDbContext> dbContextProvider,
IActiveTransactionProvider transactionProvider)
: base(dbContextProvider)
{
_transactionProvider = transactionProvider;
}
public DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
{
EnsureConnectionOpen();
var dbFacade = Context.Database;
var connection = Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.GetDbConnection(dbFacade);
var command = connection.CreateCommand();
command.CommandText = commandText;
command.CommandType = commandType;
command.Transaction = GetActiveTransaction();
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}
DbCommand IOutStockOrderRepository.CreateCommand(string commandText, CommandType commandType, params object[] parameters)
{
EnsureConnectionOpen();
var dbFacade = Context.Database;
var connection = Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.GetDbConnection(dbFacade);
var command = connection.CreateCommand();
command.CommandText = commandText;
command.CommandType = commandType;
command.Transaction = GetActiveTransaction();
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}
private void EnsureConnectionOpen()
{
var dbFacade = Context.Database;
var connection = Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.GetDbConnection(dbFacade);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
}
private DbTransaction GetActiveTransaction()
{
return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
{
{"ContextType", typeof(TPLMSDbContext) },
{"MultiTenancySide", MultiTenancySide }
});
}
string IOutStockOrderRepository.GetNo(string name)
{
SqlParameter[] parameters = {
new SqlParameter("Name",System.Data.SqlDbType.NVarChar,10),
new SqlParameter("BH", System.Data.SqlDbType.NVarChar,20)
};
parameters[0].Value = name;
parameters[1].Direction = System.Data.ParameterDirection.Output;
int cnt = Context.Database.ExecuteSqlCommand(
"EXEC p_NextBH @Name, @BH output",
parameters);
string no = parameters[1].Value.ToString();
if (cnt < 0)
{
no = string.Empty;
}
return no;
}
void IOutStockOrderRepository.ImportInStockOrder(string ids, string no)
{
SqlParameter[] parameters = {
new SqlParameter("id",System.Data.SqlDbType.VarChar,500),
new SqlParameter("No", System.Data.SqlDbType.NVarChar,20)
};
parameters[0].Value = ids + ",";
parameters[1].Value = no;
int cnt = Context.Database.ExecuteSqlCommand(
"EXEC SP_ImportInStockOrder2ODO @id, @No",
parameters);
}
}
}
5. 在這裡我一共使用了兩個儲存過程，p_NextBH 與SP_ImportInStockOrder2ODO。其中p_NextBH在入庫單管理中已經介紹過了。
6. 在這裡介紹一下SP_ImportInStockOrder2ODO這個儲存過程的實現，程式碼如下：
Create Proc [dbo].[SP_ImportInStockOrder2ODO]
@id varchar(500), --id組合
@No nvarchar(20) --編號
as
CREATE TABLE #IdTable(Id int NULL)
DECLARE @str VARCHAR(1000)
DECLARE @x xml
select @str=@id
insert #IdTable
SELECT convert(int,id) id
FROM (SELECT [value] = CONVERT(XML,'<v>' +REPLACE(@str,',' ,'</v><v>')+ '</v>')) A
OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)')
FROM A.[value].nodes('/v') N (v)
) B
DECLARE @BH nvarchar(20),@batch varchar(20),@maxseqno int
select @BH=@No
select @maxseqno=isnull(MAX(seqno),0) from [InStockOrderDetail] where InStockNo= @BH
INSERT INTO [dbo].[OutStockOrderDetail]
([OutStockNo],[SupplierId],[CargoCode],[HSCode],[CargoName],[Spcf]
,[Unit],[Country],[Brand] ,[Curr],[Package],[Length],[Width],[Height],[Qty]
,[Vol],[LawfQty],[SecdLawfQty],[Price],[TotalAmt],[GrossWt],[NetWt]
,[LawfUnit] ,[SecdLawfUnit],[Batch],[InStockOrderDetailLocId],[Loc],[CreationTime])
SELECT @BH,supplierid,[CargoCode],[HSCode],[CargoName],[Spcf]
,[Unit],[Country],[Brand],[Curr] ,[Package],[Length],[Width],[Height],0 [Qty]
,[Vol] ,0 [LawfQty], 0 [SecdLawfQty] ,[Price],0 [TotalAmt],[GrossWt],[NetWt]
,'' [LawfUnit],'' [SecdLawfUnit],batch,id,loc,getdate()
FROM
(select row_number() OVER ( order by b.id) seqno,a.supplierid,[CargoCode],[HSCode]
,[CargoName],[Spcf]
,[Unit],[Country],[Brand],[Curr] ,[Package],[Length],[Width],[Height],0 [Qty]
,[Vol] ,0 [LawfQty], 0 [SecdLawfQty] ,[Price],0 [TotalAmt],[GrossWt],[NetWt]
,'' [LawfUnit],'' [SecdLawfUnit],b.id,b.loc,Batch
from [dbo].InStockOrderDetail a inner join InStockOrderDetailLoc b
on a.Id=b.InStockOrderDetailId
left join (select InStockOrderDetailLocId,SUM(isnull(qty,0)) as qty from OutStockOrderDetail
group by InStockOrderDetailLocId
)c on b.Id=c.InStockOrderDetailLocId
where b.Qty-ISNULL(c.qty,0)>0
and b.Id in (select Id from #IdTable)
) iso
drop table #IdTable