拼sql

一、查询部分

protected override async Task Protected_HandlerRequestAsync(AllCustomerList_Request request)
        {

            Response.PageInfo = request.PageInfo;


            //主干 sql 语句
            string base_sql = $@"
from [dbo].[Customer] a
left join [dbo].[CustomerDetail] b on a.Id=b.CustomerId
left join [dbo].[SysUser] c on a.CreateUserId = c.SysUserId
left join [dbo].[UserCustomer] d on a.Id=d.CustomerId and d.Deleted={(int)DataDeleted.Normal}
left join [dbo].[SysUser] e on d.SysUserId = e.SysUserId
left join [dbo].[SysRelation] f on e.SysUserId = f.SysUserId and e.PartnerId=f.PartnerId
left join [dbo].[SysGroup] g on f.SysGroupId=g.SysGroupId and e.PartnerId=f.PartnerId
where a.PartnerId={Token_PartnerId}  
";

            #region 查询条件
            //查询sql语句
            string where_sql = string.Empty;
            //字符串条件参数化，防注入
            DynamicParameters parms = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(request.KeyWord))
            {
                where_sql += " and (a.PhoneNo like '%'+@KeyWord+'%' or a.Name like '%'+@KeyWord+'%' )  ";
                parms.Add("KeyWord", request.KeyWord);
            }
            if (!string.IsNullOrWhiteSpace(request.CompanyName))
            {
                where_sql += " and b.CompanyName like  '%'+@CompanyName+'%'  ";
                parms.Add("CompanyName", request.CompanyName);
            }
            if (request.Sex != null)
            {
                where_sql += $" and b.Sex={(int)request.Sex.Value}  ";
            }
            if (request.DeleteState != null)
            {
                if (request.DeleteState.Value == DeleteState.未删除)
                {
                    where_sql += $" and a.Deleted={(int)DataDeleted.Normal}  ";
                }
                else if (request.DeleteState.Value == DeleteState.已删除)
                {
                    where_sql += $" and a.Deleted={(int)DataDeleted.Deleted}  ";
                }
            }
            if (request.SysUserId != null)
            {
                List<int> customerId_list = await (from a in this.DbCtx.UserCustomers.AsNoTracking()
                                                   where a.SysUserId == request.SysUserId.Value && a.Deleted == (int)DataDeleted.Normal
                                                   select a.CustomerId
                                                                    ).ToListAsync();

                //没有结果直接返回
                if (customerId_list.Count < 1)
                {
                    this.Response.List = new List<CustomerListItemPlus>();
                    this.Response.PageInfo.RowCount = 0;
                    this.Response.Message = "查询成功";
                    return;
                }
                where_sql += $" and a.Id in ({string.Join(",", customerId_list)})  ";
            }
            if (request.AddSourceList != null && request.AddSourceList.Count > 0)
            {
                where_sql += $" and a.AddSource in ({string.Join(",", request.AddSourceList.Select(a => (int)a).ToList())})  ";
            }
            if (request.StartDate != null)
            {
                where_sql += $" and a.CreateTime >='{request.StartDate.Value}'  ";
            }
            if (request.EndDate != null)
            {
                request.EndDate = request.EndDate.Value.Date.AddDays(1).AddSeconds(-1);
                where_sql += $" and a.CreateTime <='{request.EndDate.Value}'  ";
            }
            #endregion

            //追加查询条件
            base_sql += where_sql;

            //查询总数量
            string queryTotalSql = $" select count(*)  {base_sql} ;";

            var query_count= await DbConn.QueryAsync<int>(queryTotalSql, parms);
            int count = query_count.FirstOrDefault();
            Response.PageInfo.RowCount = count;
            //如果没有结果直接返回
            if (count < 1)
            {
                this.Response.List = new List<CustomerListItemPlus>();
                this.Response.Message = "查询成功";
                return;
            }


            #region 分页参数转换
            int start = 1;
            int end = 10;
            int page = 1;
            int pageCount = 10;

            if (request.PageInfo == null)
            {
                request.PageInfo = new Models.Base.Page() { PageIndex = 1, PageSize = 10 };
            }

            if (request.PageInfo.PageIndex.HasValue)
            {
                page = request.PageInfo.PageIndex.Value;
            }

            if (request.PageInfo.PageSize.HasValue)
            {
                pageCount = request.PageInfo.PageSize.Value;
            }

            if (request.PageInfo.PageIndex > 0)
            {
                start = (page - 1) * pageCount + 1;
                end = page * pageCount;
            }
            #endregion
            //分页查询
            string queryPageSql = $@" 
select * from (
        select a.Id as'CustomerId',
        a.Name as 'CustomerName',
        a.PhoneNo as 'PhoneNo',
        c.UserName  as 'CreateUser',
        a.CreateTime  as 'CreateTime',
        a.AddSource as 'AddSource',
        a.Deleted  as 'Deleted',
        b.Sex as 'Sex',
        b.WeChatNo as 'WeChatNo',
        b.QQ as 'QQ',
        b.Email as 'Email',
        b.CompanyName as 'CompanyName',
        b.CompanyAddress as 'CompanyAddress',
        b.CompanyWebSite as 'CompanyWebSite',
        b.Level as 'Level',
        g.GroupName  as 'PossessorDepartment',
        e.UserName  as 'PossessorName',
        row_number() OVER (ORDER BY a.Id DESC) AS [row_number]
{base_sql}
) t
where t.[row_number]   between {start} and {end}
;
";

            IEnumerable<CustomerListItemPlus> List =  await DbConn.QueryAsync<CustomerListItemPlus>(queryPageSql, parms);


            this.Response.List = List.ToList();
            this.Response.Message = "查询成功";


        }

 

二、添加或修改

protected override async Task Protected_HandlerRequestAsync(AddTag_Request request)
        {

            #region 参数验证
            if (request == null)
            {
                this.Response.Code = 1;
                this.Response.Message = "参数对象不能为空。";
                return;
            }
            if (request.CustomerId == null)
            {
                this.Response.Code = 2;
                this.Response.Message = "【客户id】 不能为空。";
                return;
            }
            if (string.IsNullOrWhiteSpace(request.Name))
            {
                this.Response.Code = 3;
                this.Response.Message = "【标签名】 不能为空。";
                return;
            }

            #endregion 参数验证

            #region 数据库查询
            CustomerTag db_tag = await DbCtx.CustomerTags.AsNoTracking().FirstOrDefaultAsync(a => a.Name == request.Name
            && a.PartnerId == this.Token_PartnerId);

            #endregion
            //sql语句
            string sql = string.Empty;
            //sql语句相关的 参数
            DynamicParameters parms = new DynamicParameters();
            if (db_tag == null)
            {
                sql += "declare @CurrentId int; " +
                    "INSERT INTO [dbo].[CustomerTag] ([Name] ,[CreateUserId] ,[CreateTime] ,[PartnerId] ) " +
                    "VALUES (@Name ,@CreateUserId ,@CreateTime ,@PartnerId ) ;" +
                    " set @CurrentId=SCOPE_IDENTITY(); " +
                    "INSERT INTO [dbo].[MyCustomerTag] ([CustomerId] ,[UserId] ,[TagId] ,[UpdateTime]) " +
                    "VALUES (@CustomerId ,@UserId ,@CurrentId  ,@UpdateTime);";

                parms.Add("Name", request.Name);
                parms.Add("CreateUserId", this.Token_UserId);
                parms.Add("CreateTime", DateTime.Now);
                parms.Add("PartnerId", this.Token_PartnerId);
                parms.Add("CustomerId", request.CustomerId);
                parms.Add("UserId", this.Token_UserId);
                parms.Add("UpdateTime", DateTime.Now);

            }
            else
            {

                #region  
                if (db_tag.Deleted == (int)DataDeleted.Deleted)
                {
                    sql += "UPDATE [dbo].[CustomerTag] SET [Deleted] = @Deleted  WHERE [Id] =@Id";

                    parms.Add("Deleted", (int)DataDeleted.Normal);
                    parms.Add("Id", db_tag.Id);

                }

                //获取原来是否有关系
                MyCustomerTag db_myCustomerTag = await DbCtx.MyCustomerTags.AsNoTracking().FirstOrDefaultAsync(a => a.UserId == this.Token_UserId && a.TagId == db_tag.Id && a.CustomerId == request.CustomerId);
                if (db_myCustomerTag == null)
                {
                    sql += "INSERT INTO [dbo].[MyCustomerTag] ([CustomerId] ,[UserId] ,[TagId] ,[UpdateTime]) " +
                                "VALUES (@CustomerId ,@UserId ,@TagId ,@UpdateTime)";

                    parms.Add("CustomerId", request.CustomerId);
                    parms.Add("UserId", this.Token_UserId);
                    parms.Add("TagId", db_tag.Id);
                    parms.Add("UpdateTime", DateTime.Now);

                }
                else
                {
                    if (db_myCustomerTag.Deleted== (int)DataDeleted.Normal)
                    {
                        this.Response.Code = 4;
                        this.Response.Message = "标签已存在！";
                        return;
                    }

                    sql += $" UPDATE [dbo].[MyCustomerTag] SET [Deleted] = @mcDeleted ,[UpdateTime] = @UpdateTime  WHERE Id=@MyCustomerTagId ";
                    parms.Add("mcDeleted", (int)DataDeleted.Normal);
                    parms.Add("UpdateTime", DateTime.Now);
                    parms.Add("MyCustomerTagId", db_myCustomerTag.Id);

                } 
                #endregion


            }

            //执行sql
            int count = await this.DbConn.ExecuteAsync(sql, parms);

            //判断 缓存存在，数据来源读库，否则来源 写库
            await this.Set_Change_Async(typeof(MyCustomerListApiHandler));

            this.Response.Message = "添加成功";
        }