ERP客户关系渠管理（二十）

原创

zhangyongshizhi 2022-08-06 00:24:08 博主文章分类：ERP项目 ©著作权

文章标签 sql 存储过程 ide 文章分类 运维

©著作权归作者所有：来自51CTO博客作者zhangyongshizhi的原创作品，请联系作者获取转载授权，否则将追究法律责任

渠道的需求：

ERP客户关系渠管理（二十）_sql

用力图：

ERP客户关系渠管理（二十）_存储过程_02

渠道对应客户关系的意义？

渠道是指产品从生产出来到终端消费的流通环节。

ERP客户关系渠管理（二十）_ide_03

添加的存储过程：

ALTER PROCEDURE [dbo].[BioErpCrmManageChannel_ADD]
@ChannelID int output,
@UserID int,
@SuperiorChannel int,
@ChannelName nvarchar(50),
@Tel1 nvarchar(20),
@Tel2 nvarchar(20),
@Fax nvarchar(20),
@Address nvarchar(50),
@Email nvarchar(50),
@ChannelLevel nvarchar(20),
@NetAddress nvarchar(50),
@CreditStanding nvarchar(20),
@EnrollTime datetime,
@NextContactTime datetime,
@ChannelSize nvarchar(50),
@Vocation nvarchar(50),
@ChannelType nvarchar(30),
@Status nvarchar(30),
@Areal nvarchar(20),
@Province nvarchar(20),
@City nvarchar(20),
@TaxNumber nvarchar(50),
@AccountName nvarchar(50),
@Accounts nvarchar(50),
@CoSummary nvarchar(1000),
@CoreOperttion nvarchar(500)


 AS 
  INSERT INTO [BioErpCrmManageChannel](
  [UserID],[SuperiorChannel],[ChannelName],[Tel1],[Tel2],[Fax],[Address],[Email],[ChannelLevel],[NetAddress],[CreditStanding],[EnrollTime],[NextContactTime],[ChannelSize],[Vocation],[ChannelType],[Status],[Areal],[Province],[City],[TaxNumber],[AccountName],[Accounts],[CoSummary],[CoreOperttion]
  )VALUES(
  @UserID,@SuperiorChannel,@ChannelName,@Tel1,@Tel2,@Fax,@Address,@Email,@ChannelLevel,@NetAddress,@CreditStanding,@EnrollTime,@NextContactTime,@ChannelSize,@Vocation,@ChannelType,@Status,@Areal,@Province,@City,@TaxNumber,@AccountName,@Accounts,@CoSummary,@CoreOperttion
  )
  SET @ChannelID = @@IDENTITY

渠道修改的存储过程：

ALTER PROCEDURE [dbo].[BioErpCrmManageChannel_Update]
@ChannelID int,
@UserID int,
@SuperiorChannel int,
@ChannelName nvarchar(50),
@Tel1 nvarchar(20),
@Tel2 nvarchar(20),
@Fax nvarchar(20),
@Address nvarchar(50),
@Email nvarchar(50),
@ChannelLevel nvarchar(20),
@NetAddress nvarchar(50),
@CreditStanding nvarchar(20),
@EnrollTime datetime,
@NextContactTime datetime,

@ChannelSize nvarchar(50),
@Vocation nvarchar(50),
@ChannelType nvarchar(30),
@Status nvarchar(30),
@Areal nvarchar(20),
@Province nvarchar(20),
@City nvarchar(20),
@TaxNumber nvarchar(50),
@AccountName nvarchar(50),
@Accounts nvarchar(50),
@CoSummary nvarchar(1000),
@CoreOperttion nvarchar(500),
@DeleteState bit
 AS 
  UPDATE [BioErpCrmManageChannel] SET 
  [UserID] = @UserID,[SuperiorChannel] = @SuperiorChannel,[ChannelName] = @ChannelName,[Tel1] = @Tel1,[Tel2] = @Tel2,[Fax] = @Fax,[Address] = @Address,[Email] = @Email,[ChannelLevel] = @ChannelLevel,[NetAddress] = @NetAddress,[CreditStanding] = @CreditStanding,[EnrollTime] = @EnrollTime,[NextContactTime] = @NextContactTime,[ChannelSize] = @ChannelSize,[Vocation] = @Vocation,[ChannelType] = @ChannelType,[Status] = @Status,[Areal] = @Areal,[Province] = @Province,[City] = @City,[TaxNumber] = @TaxNumber,[AccountName] = @AccountName,[Accounts] = @Accounts,[CoSummary] = @CoSummary,[CoreOperttion] = @CoreOperttion,[DeleteState] = @DeleteState
  WHERE ChannelID=@ChannelID

渠道的联系人添加的存储过程：

ALTER PROCEDURE [dbo].[BioErpCrmChannelLinkMan_ADD]
@ChannelID int,
@LinkmanName nvarchar(50),
@Sex bit,
@Birthday datetime,
@IsMainLinkman bit,
@OfficialPhone nvarchar(20),
@MobilePhone nvarchar(20),
@Email nvarchar(50),
@Address nvarchar(50),
@QQ nvarchar(20),
@Remark nvarchar(500)
 AS 
  INSERT INTO [BioErpCrmChannelLinkMan](
  [ChannelID],[LinkmanName],[Sex],[Birthday],[IsMainLinkman],[OfficialPhone],[MobilePhone],[Email],[Address],[QQ],[Remark]
  )VALUES(
  @ChannelID,@LinkmanName,@Sex,@Birthday,@IsMainLinkman,@OfficialPhone,@MobilePhone,@Email,@Address,@QQ,@Remark
  )

渠道联系人修改：

ALTER PROCEDURE [dbo].[BioErpCrmChannelLinkMan_Update]
@LinkmanID int,
@ChannelID int,
@LinkmanName nvarchar(50),
@Sex bit,
@Birthday datetime,
@IsMainLinkman bit,
@OfficialPhone nvarchar(20),
@MobilePhone nvarchar(20),
@Email nvarchar(50),
@Address nvarchar(50),
@QQ nvarchar(20),
@Remark nvarchar(500),
@DeleteState bit
 AS 
  UPDATE [BioErpCrmChannelLinkMan] SET 
  [ChannelID] = @ChannelID,[LinkmanName] = @LinkmanName,[Sex] = @Sex,[Birthday] = @Birthday,[IsMainLinkman] = @IsMainLinkman,[OfficialPhone] = @OfficialPhone,[MobilePhone] = @MobilePhone,[Email] = @Email,[Address] = @Address,[QQ] = @QQ,[Remark] = @Remark,[DeleteState] = @DeleteState
  WHERE LinkmanID=@LinkmanID

联系记录（添加）：（添加的时候状态给默认值）

ALTER PROCEDURE [dbo].[BioErpCrmChannelContactNote_ADD]
@ChannelID int,
@RelationRecord nvarchar(1000),
@RelationTime datetime,
@LinkmanID int,
@RelationType nvarchar(50),
@LinkMethod nvarchar(50),
@Userid int


 AS 
  INSERT INTO [BioErpCrmChannelContactNote](
  [ChannelID],[RelationRecord],[RelationTime],[LinkmanID],[RelationType],[LinkMethod],[Userid]
  )VALUES(
  @ChannelID,@RelationRecord,@RelationTime,@LinkmanID,@RelationType,@LinkMethod,@Userid
  )

修改：

ALTER PROCEDURE [dbo].[BioErpCrmChannelContactNote_Update]
@RelationID int,
@ChannelID int,
@RelationRecord nvarchar(1000),
@RelationTime datetime,
@LinkmanID int,
@RelationType nvarchar(50),
@LinkMethod nvarchar(50),
@Userid int,
@DeleteState bit
 AS 
  UPDATE [BioErpCrmChannelContactNote] SET 
  [ChannelID] = @ChannelID,[RelationRecord] = @RelationRecord,[RelationTime] = @RelationTime,[LinkmanID] = @LinkmanID,[RelationType] = @RelationType,[LinkMethod] = @LinkMethod,[Userid] = @Userid,[DeleteState] = @DeleteState
  WHERE RelationID=@RelationID

活动管理（添加）：

ALTER PROCEDURE [dbo].[BioErpCrmChannelActivity_ADD]
@ActiveName nvarchar(100),
@ChannelID int,
@ActivityTime datetime,
@ActivityAddress nvarchar(100),
@Principal nvarchar(10),
@ActivityDesc nvarchar(4000)
 AS 
  INSERT INTO [BioErpCrmChannelActivity](
  [ActiveName],[ChannelID],[ActivityTime],[ActivityAddress],[Principal],[ActivityDesc]
  )VALUES(
  @ActiveName,@ChannelID,@ActivityTime,@ActivityAddress,@Principal,@ActivityDesc
  )

修改：

ALTER PROCEDURE [dbo].[BioErpCrmChannelActivity_Update]
@ActivityID int,
@ActiveName nvarchar(100),
@ChannelID int,
@ActivityTime datetime,
@ActivityAddress nvarchar(100),
@Principal nvarchar(10),
@ActivityDesc nvarchar(4000),
@DeleteState bit
 AS 
  UPDATE [BioErpCrmChannelActivity] SET 
  [ActiveName] = @ActiveName,[ChannelID] = @ChannelID,[ActivityTime] = @ActivityTime,[ActivityAddress] = @ActivityAddress,[Principal] = @Principal,[ActivityDesc] = @ActivityDesc,[DeleteState] = @DeleteState
  WHERE ActivityID=@ActivityID

文档的添加：

ALTER PROCEDURE [dbo].[BioErpCrmChannelDocument_ADD]
@DocumentID int,
@ChannelID int
 AS 
  INSERT INTO [BioErpCrmChannelDocument](
  [DocumentID],[ChannelID]
  )VALUES(
  @DocumentID,@ChannelID
  )

文档的修改：

ALTER PROCEDURE [dbo].[BioErpCrmChannelDocument_Update]
@ChannelDocumentID int,
@DocumentID int,
@ChannelID int,
@DeleteState bit
 AS 
  UPDATE [BioErpCrmChannelDocument] SET 
  [DocumentID] = @DocumentID,[ChannelID] = @ChannelID,[DeleteState] = @DeleteState
  WHERE ChannelDocumentID=@ChannelDocumentID

添加的BLL层代码：

/// <summary>
      /// 添加一条渠道基本信息，返回当前添加的渠道的编号
      /// </summary>
      /// <param name="channel"></param>
        /// <returns>int</returns>
      public int BioErpCrmManageChannelAdd(BioErpCrmManageChannel model)
      {
          SqlParameter[] parameters = {
          new SqlParameter("@ChannelID", SqlDbType.Int,4),
          new SqlParameter("@UserID", SqlDbType.Int,4),
          new SqlParameter("@SuperiorChannel", SqlDbType.Int,4),
          new SqlParameter("@ChannelName", SqlDbType.NVarChar,50),
          new SqlParameter("@Tel1", SqlDbType.NVarChar,20),
          new SqlParameter("@Tel2", SqlDbType.NVarChar,20),
          new SqlParameter("@Fax", SqlDbType.NVarChar,20),
          new SqlParameter("@Address", SqlDbType.NVarChar,50),
          new SqlParameter("@Email", SqlDbType.NVarChar,50),
          new SqlParameter("@ChannelLevel", SqlDbType.NVarChar,20),
          new SqlParameter("@NetAddress", SqlDbType.NVarChar,50),
          new SqlParameter("@CreditStanding", SqlDbType.NVarChar,20),
          new SqlParameter("@EnrollTime", SqlDbType.DateTime),
          new SqlParameter("@NextContactTime", SqlDbType.DateTime),
          new SqlParameter("@AlternateTime", SqlDbType.DateTime),
          new SqlParameter("@StratTime", SqlDbType.DateTime),
          new SqlParameter("@ChannelSize", SqlDbType.NVarChar,50),
          new SqlParameter("@Vocation", SqlDbType.NVarChar,50),
          new SqlParameter("@ChannelType", SqlDbType.NVarChar,30),
          new SqlParameter("@Status", SqlDbType.NVarChar,30),
          new SqlParameter("@Areal", SqlDbType.NVarChar,20),
          new SqlParameter("@Province", SqlDbType.NVarChar,20),
          new SqlParameter("@City", SqlDbType.NVarChar,20),
          new SqlParameter("@TaxNumber", SqlDbType.NVarChar,50),
          new SqlParameter("@AccountName", SqlDbType.NVarChar,50),
          new SqlParameter("@Accounts", SqlDbType.NVarChar,50),
          new SqlParameter("@CoSummary", SqlDbType.NVarChar,1000),
          new SqlParameter("@CoreOperttion", SqlDbType.NVarChar,500),
          new SqlParameter("@DeleteState", SqlDbType.Bit,1)};
          parameters[0].Direction = ParameterDirection.Output;          
          parameters[1].Value = model.UserID;
          parameters[2].Value = model.SuperiorChannel;
          parameters[3].Value = model.ChannelName;
          parameters[4].Value = model.Tel1;
          parameters[5].Value = model.Tel2;
          parameters[6].Value = model.Fax;
          parameters[7].Value = model.Address;
          parameters[8].Value = model.Email;
          parameters[9].Value = model.ChannelLevel;
          parameters[10].Value = model.NetAddress;
          parameters[11].Value = model.CreditStanding;
          parameters[12].Value = model.EnrollTime;
          parameters[13].Value = model.NextContactTime;
          parameters[14].Value = model.AlternateTime;
          parameters[15].Value = model.StratTime;
          parameters[16].Value = model.ChannelSize;
          parameters[17].Value = model.Vocation;
          parameters[18].Value = model.ChannelType;
          parameters[19].Value = model.Status;
          parameters[20].Value = model.Areal;
          parameters[21].Value = model.Province;
          parameters[22].Value = model.City;
          parameters[23].Value = model.TaxNumber;
          parameters[24].Value = model.AccountName;
          parameters[25].Value = model.Accounts;
          parameters[26].Value = model.CoSummary;
          parameters[27].Value = model.CoreOperttion;
          parameters[28].Value = model.DeleteState;

        int count=   DataBaseHelper.ExcuteSqlReturnInt("BioErpCrmManageChannel_ADD", CommandType.StoredProcedure, parameters);
        if (count != 0)
        {
            return int.Parse(parameters[0].Value.ToString());
        }
        else
        {
            return 0;
        }
      
      }