Realization of warehousing management system by BP (net core) + easyUI + efcore

Time:2020-4-18

ABP (net core) + easyUI + efcore to realize the inventory management system directory

ABP (net core) + easyUI + efcore to realize the warehouse management system
ABP (net core) + easyUI + efcore to realize warehouse management system — introduction of solutions (2)
ABP (net core) + easyUI + efcore to realize warehouse management system — creating entity in domain layer (3)
 ABP (net core) + easyUI + efcore to realize warehouse management system — define warehouse and implement (4)
ABP (net core) + easyUI + efcore to realize warehouse management system — creating application services (5)
ABP (net core) + easyUI + efcore to realize warehouse management system
ABP (net core) + easyUI + efcore to realize the warehouse management system
ABP (net core) + easyUI + efcore to realize the warehouse management system
ABP (net core) + easyUI + efcore to realize warehouse management system
ABP (net core) + easyUI + efcore to realize warehouse management system — multilingual (10)
ABP (net core) + easyUI + efcore to realize warehouse management system
ABP (net core) + easyUI + efcore to realize the warehouse management system menu up (16)

ABP (net core) + easyUI + efcore to realize warehouse management system — easyUI front page framework (18)

ABP (net core) + easyUI + efcore to realize the storage management system — goods management of easyUI I I (19)
ABP (net core) + easyUI + efcore to realize warehouse management system
ABP (net core) + easyUI + efcore to realize the warehouse management system

ABP (net core) + easyUI + efcore to realize the warehouse management system

ABP (net core) + easyUI + efcore to realize the warehouse management system
ABP (net core) + easyUI + efcore to realize warehousing management system — one of warehousing management (37)
aRealization of warehousing management system by BP (net core) + easyUI + efcore
 
 

In the previous article aRealization of warehousing management system by BP (net core) + easyUI + efcoreAmong usCreated theSome relatedDTOclassPaging classSince I used the stored procedure of database for the warehouse entry, let’s learn how toThe stored procedure is called in ABP.

As we all know, the most basic requirement of the document number in the warehouse management system is unique, which must be met. Or for any system with a single number, the single number must be unique, which is a hard requirement.

Let’s talk about some rules for the naming of DOC No.:

1. No repetition.

I’m sure you all know this. There is no need to explain the uniqueness of the order number.

2. Security.

Try not to disclose the real operation information of your company with your order number. For example, if your order number is the serial number, then others can infer the overall operation summary of your company from the order number. So the number code must be something that few people in your company can’t understand. In fact, the best anti leakage coding rule is not to add any data with the company’s operation in the coding.

3. Random code.

When many people make the coding rules of the order number, the first idea is definitely not to repeat uniqueness, so the second idea may be security, and the third idea to meet the first two at the same time is to add random code to the order number. Add to order No2 ~ 3 random codes, combined with serial number, can hide the real data of serial number.

4. Prevent concurrency.

This rule is mainly for the setting of time in coding.

5. Number of control digits.

This is well understood. The function of the order number is to facilitate query.

 

There are several general ways to create a document number:

1. Generate a self growing order number (the order number is the primary key of the data table) by using the primary key value of the database

2. Date + order number of self increasing number (for example: 20200101100662202002100662、2002100662

3. Randomly generated doc No. (6512353245921)

4. Letter + number string, letters should have special meaning. Such as warehouse entry, gd202016652

 

Order number design user experience rules:

1. There is no repeatability in the order number;

2. If it is convenient for customer service, it is better to use the order number of “date + auto increment” style.

3. The length of order number shall be kept as short as possible (within 15 digits), which is convenient for users. The long number has a high probability of error reporting, which affects the efficiency of customer service;

4. If the number of users of your system is in the tens of millions level, the order number should be digital (pure integer). In the database order index query, the data index and retrieval efficiency of the long integer digital type is much higher than that of the text type. For small and medium-sized applications, you can use the string form of “letters + numbers”!

 

Five.Use stored procedure to create order number

in useABP frame structurebuildIf you want to call the stored procedure in the warehouse layer, weHow to achieve it? On this issue, I searched a lot of information and finally read the official documents:https://aspnetboilerplate.com/Pages/Documents/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html

After reading the official documents, how toThere is a corresponding way to use stored procedures in ABP. Now let’s do it.

  1. stayIn the solution explorer of visual studio 2017, right-click the irepositories folder of the abp.tplms.core project, and choose add > class from the pop-up menu, select in the pop-up dialog box“Interface”takeInterfaceNamed IInStockOrderRepository, and selectAdd.The following diagram.

 

2. In the iinstockorderrepository interface, define the methods we need to use. The code is as follows.

using Abp.Domain.Repositories;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Linq;
using System.Text;
using ABP.TPLMS.Entitys;
using System.Threading.Tasks;
using Abp.Dependency; 

namespace ABP.TPLMS.IRepositories
{

    public interface IInStockOrderRepository : IRepository, ITransientDependency
    {

        /// 
        ///Execute the given command
        /// 
        ///Command string
        ///Parameters to apply to the command string
        ///Results returned by the database after the command is executed
        int Execute(string sql, params object[] parameters);

        /// 
        ///Create a raw SQL query that returns elements of the given generic type.
        /// 
        ///Type of object returned by query
        ///SQL query string
        ///Parameters to apply to SQL query strings
        /// 

        IQueryable SqlQuery(string sql, params object[] parameters);

        DbCommand CreateCommand(string commandText, CommandType commandType, params object[] parameters);

        /// 
        ///Create doc No
        /// 
        ///Document name code
        /// 

        string GetNo(string name);

        /// 
        ///Import goods information
        /// 
        ///ID set of imported goods
        // / odd numbers
        void ImportCargo(string ids,string no);

    }
}
 
     3. stayIn solution explorer of visual studio 2017, right-click the repositories folder of abp.tplms.entityframeworkcore project, and choose add > class from the pop-up menu takeclassNamed InStockOrderRepositoryAnd inheritIinstockorderrepository interface. Implement the methods in the interface. The code is as follows.
using Abp.Data;
using Abp.Dependency;
using Abp.Domain.Entities;
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.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
 

namespace ABP.TPLMS.EntityFrameworkCore.Repositories
{

    public class InStockOrderRepository : TPLMSRepositoryBase ,IInStockOrderRepository, ITransientDependency
    {

        private readonly IActiveTransactionProvider _transactionProvider; 

        public InStockOrderRepository(IDbContextProvider dbContextProvider) : base(dbContextProvider)
        { }

        protected InStockOrderRepository(IDbContextProvider 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 IInStockOrderRepository.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();
            }

        }

        int IInStockOrderRepository.Execute(string sql, params object[] parameters)
        {
            throw new NotImplementedException();

        }

        private DbTransaction GetActiveTransaction()
        {
            return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
            {
                {"ContextType", typeof(TPLMSDbContext) },
                {"MultiTenancySide", MultiTenancySide }
            });

        }
 

         string IInStockOrderRepository.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 IInStockOrderRepository.ImportCargo(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_ImportCargo2GDE @id, @No",
parameters);
        }

        IQueryable IInStockOrderRepository.SqlQuery(string sql, params object[] parameters)
        {
            throw new NotImplementedException();
        }
    }
}

4. Here I use two stored procedures,p_NextBH AndSP_ImportCargo2GDE

5. Define a tableTPLMS_NOIt is specially used to store the type of all doc numbers that need unique doc numbers and the maximum value currently used for class doc numbers.

CREATE TABLE [dbo].[TPLMS_NO](
[Name] [nvarchar](10) NOT NULL,
[Head] [nvarchar](10) NOT NULL,
[CurrentNo] [int] NOT NULL,
[BHLen] [bigint] NOT NULL,
[IsYear] [int] NOT NULL,
[DESCRIPTION] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[TPLMS_NO] ADD  DEFAULT ('') FOR [Head]

GO
ALTER TABLE [dbo].[TPLMS_NO] ADD  DEFAULT ((0)) FOR [CurrentNo]

GO
ALTER TABLE [dbo].[TPLMS_NO] ADD  DEFAULT ((6)) FOR [BHLen]

GO
ALTER TABLE [dbo].[TPLMS_NO] ADD  DEFAULT ((1)) FOR [IsYear]

GO
Insert into [tplms [u no] ([name], [head], [currentno], [bhlen], [isyear], [description]) values ('gde ',' GD ', 0,6,1,' stock in order ')

GO
INSERT INTO [TPLMS_NO]([Name],[Head],[CurrentNo],[BHLen],[IsYear],[DESCRIPTION])
Values ('bat ',' a ', 0,7,0,' batch number ')

GO

6. Because this is a small application, the generation of DOC No. is letter + date + serial number. adoptP ﹣ nextbh to create a document number, which is specially used to get the order number in the previous table.The implementation of P ﹣ nextbh is as follows:

 



CREATE PROC [dbo].[p_NextBH]
@Name nvarchar(10),           
@BH nvarchar(20) OUTPUT 
AS

BEGIN TRAN
UPDATE [TPLMS_NO] WITH(ROWLOCK) SET 
@BH=Head+case isyear when 1 then convert(varchar(4),year(getdate())) else '' end 
+RIGHT(POWER(convert(bigint,10),BHLen)+CurrentNo+1,BHLen),
CurrentNo=CurrentNo+1
WHERE [email protected]
select @BH

COMMIT TRAN
GO

     7. About useWhat are the advantages and disadvantages of P? Nextbh? When using things in stored procedures, the performance of the database will decline sharply. For small applications, this is not a big problem. For medium and large applications, it may be a problem. The update lock obtained by update can be used directly, that is, SQL server will ensure the sequential execution of update.It is suitable for medium-sized applications, but it can not meet the requirements of high concurrent performance. Let’s change the stored procedure.


CREATE PROC [dbo].[p_NextBH]
@Name nvarchar(10),           
@BH nvarchar(20) OUTPUT 

AS

UPDATE [TPLMS_NO] WITH(ROWLOCK) SET 
@BH=Head+case isyear when 1 then convert(varchar(4),year(getdate())) else '' end 
+RIGHT(POWER(convert(bigint,10),BHLen)+CurrentNo+1,BHLen),
CurrentNo=CurrentNo+1
WHERE [email protected]

select @BH

GO

 

 

8. By transferring the ID of the goods information, the goods information is imported into the warehouse entry. This function is through the storage processSp ﹣ importcargo2gde.The implementation of this stored procedure is as follows:

CREATE Proc [dbo].[SP_ImportCargo2GDE]
@id varchar(1000),           
@No nvarchar(20)  

as 
 

CREATE TABLE #IdTable(Id int  NULL) 

DECLARE @PointerPrev int
    DECLARE @PointerCurr int
    DECLARE @TName nvarchar(100)
    Set @PointerPrev=1
    while (@PointerPrev < LEN(@id))
    Begin

        Set @PointerCurr=CharIndex(',',@id,@PointerPrev)
        if(@PointerCurr>0)
        Begin

            set @TName=SUBSTRING(@id,@PointerPrev,@[email protected])         
         insert into #IdTable (Id) VALUES (convert(int,@TName))
         SET @PointerPrev = @PointerCurr+1

        End
        else
            Break
    End 

DECLARE @BH nvarchar(20),@batch varchar(20),@maxseqno int
select @[email protected]
select @maxseqno=isnull(MAX(seqno),0) from [InStockOrderDetail] where InStockNo= @BH



EXEC [dbo].[p_NextBH] 'BAT', @batch OUTPUT 

INSERT INTO [dbo].[InStockOrderDetail]
           ([InStockNo],[SeqNo],[SupplierId],[CargoCode],[HSCode],[CargoName],[Spcf]
           ,[Unit],[Country],[Brand] ,[Curr],[Package],[Length],[Width],[Height],[Qty]
           ,[Vol],[LawfQty],[SecdLawfQty],[Price],[TotalAmt],[GrossWt],[NetWt]
           ,[LawfUnit] ,[SecdLawfUnit],[Batch],[DeliveryOrderDetailId],[CreationTime])

   SELECT @BH,convert(int,seqno)[email protected],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],@batch,a.id,getdate()
  FROM 
  (select row_number() OVER  ( order by id) seqno,* from [dbo].Cargos
   where id in (select id from #IdTable
  where  id not in (select [DeliveryOrderDetailId] 
  from [InStockOrderDetail]
  where InStockNo= @BH
  )
  )   
  ) a 

drop table #IdTable

GO

 

    9. For order number creation, in addition to using stored procedures, you can also use applications to create. However, to create an application, you need to ensure the high availability of the application, and it is recommended to save the maximum value to the database. I’m just going to give you the general code here.

public class BillNoBuilder{
    private static object locker = new object();      

    private static int seq = 0;      

    public static string NextBillNumber(string head){
       //Execute here or after a certain step. GetMaxSeq ();
        lock(locker){
            if(seq == 99999999)
                seq = 0;
            else
                seq++;

            return head+DateTime.Now.ToString("yyyyMMdd") + sn.ToString().PadLeft(8, '0');
        }
}

//Get the largest serial number in the database
private static void GetMaxSeq()
{
//Seq = maximum value in the database

}

 
    //Prevent instances of classes from being created
    private BillNoBuilder(){}
}