SET @salesman_name ='陈奕迅';
-- SELECt * FROM `ls_customer` WHERe relename =@salesman_name;
-- SELECt id FROM `ls_customer` WHERe relename =@salesman_name;
-- SELECt * FROM`ls_salesman` where customer_id='100697'
SELECt* FROM `ls_salesman_customer` WHERe salesman_id =(SELECt id FROM`ls_salesman` WHERe customer_id=(SELECt id FROM `ls_customer` WHERe relename =@salesman_name)) AND del_flag=0;
SET @customer_name ='脑袋疼';
SET @salesman_name ='陈奕迅';
SELECt* FROM `ls_salesman_customer` WHERe salesman_id =(SELECt id FROM`ls_salesman` WHERe customer_id=(SELECt id FROM `ls_customer` WHERe relename =@salesman_name)) AND del_flag=0;
SET @SQL = CONCAT_Ws('SELECt* FROM `ls_salesman_customer` WHERe salesman_id =(SELECt id FROM`ls_salesman` WHERe
customer_id=(SELECt id FROM `ls_customer` WHERe relename =',@salesman_name,' ','and mobile=',@phone,' ))','AND del_flag=0;');
PREPARE test FROM @SQL;
EXECUTE test;
DEALLOCATE PREPARE test;
#查看业务员私海用户;
#查看业务员私海用户;
SET @salesman_name ='蔡华飞业务员'; -- 这里不可以注入
SET @phone='';
SET @test=CONCAt('concat(relename,mobile)','like "%',IF(@salesman_name ='',@phone,@salesman_name),'%"');
SET @SQL = CONCAt('SELECt* FROM `ls_salesman_customer` WHERe salesman_id in (SELECt id FROM`ls_salesman` WHERe
customer_id in (SELECt id FROM `ls_customer` WHERe', ' ',@test,' ))','AND del_flag=0'); -- 这里可以注入
SELECt @SQL;
PREPARE test FROM @SQL;
EXECUTE test;
DEALLOCATE PREPARE test;
#查看客户名称电话查看业务员;
SET @phone='13500000552';
SET @name='弟弟哦';
SET @customer_2 = CONCAt('concat(customer_name,phone)','like','"%',CASE WHEN @name='' THEN @phone
WHEN @name!='' THEN @name END ,'%"');
SET @customer = CONCAt('concat(customer_name,phone)','like','"%',IF(@name='',@phone,@name),'%"');
SELECt @customer;
SET @sql = CONCAt('SELECT * FROM
`ls_customer` WHERe id IN(
SELECt customer_id FROM `ls_salesman` WHERe id IN(
SELECt salesman_id FROM `ls_salesman_customer` WHERe customer_id IN(
SELECt customer_id FROM
`customer_license_info` WHERe',' ',@customer,')))');
SELECt @sql;
PREPARE query_salesman FROM @sql;
EXECUTE query_salesman;
DEALLOCATE PREPARE query_salesman;
#查看客户关联业务员关系
SELECt* FROM `ls_salesman_customer` WHERe customer_id=(SELECt customer_id FROM`customer_license_info` WHERe customer_name=@customer_name);
SELECt* FROM `ls_salesman_customer` WHERe customer_id=(SELECt customer_id FROM`customer_license_info` WHERe customer_name='解忧杂货店');
#拜访记录排序
SET @salesman_name ='陈奕迅';
SELECt * FROM
`sm_visit` WHERe visit_person_id=(SELECt id FROM `ls_customer` WHERe relename =@salesman_name) ORDER BY visit_end_time DESC
