大型CRM系统.rar

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: eapp -- Create date: 2010-12-2 12:30 -- Description: 客户发展趋势统计表,根据参数,计算本年到现在为止的客户发展趋势 -- ============================================= CREATE PROCEDURE [Proc_AnalyseByTrend] @date datetime ,@UserId int AS BEGIN declare @mm int declare @yyyy int set @mm = month(@date) set @yyyy = year(@date) declare @tem int --循环变量 set @tem = 1 --初始为1 declare @sql varchar(4000) set @sql = 'select * from (' while(@tem <= @mm) begin set @sql = @sql + 'select isnull(Count(enterDate),0) as [Count] ,'''+ cast(@yyyy as varchar(10)) +''' as [Year] , '+ cast(@tem as varchar(10)) +' as [Month] from crm_Customer where (Deleted=0 and IsShare=0) and (AssignObject=''user'' and (AssignObjectId = ' + cast(@UserId as varchar(100)) + ' or ' + cast(@UserId as varchar(20)) + '=0) ) and (year(enterDate) = '+ cast(@yyyy as varchar(10)) +' and month(enterdate)='+cast(@tem as varchar(10))+') union ' set @tem = @tem + 1 end set @sql = substring(@sql,0,len(@sql)-5) set @sql = @sql + ') as tab order by tab.[Month]' exec(@sql) END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [Proc_Analyse] @AssignObject varchar(20) = 'user' ,@AssignObjectId int = 0 ,@Type int AS BEGIN declare @strWhere varchar(1000) --主查询条件 declare @strSql varchar(2000) --完整查询语句 if(@AssignObject='') set @AssignObject = 'user' set @strWhere = 'where 1=1 and (IsShare=0 and Deleted=0)' if(@AssignObjectId <> 0) set @strWhere = @strWhere + ' and (AssignObject = ''' + @AssignObject + ''' and AssignObjectId = ' + cast(@AssignObjectId as varchar(10)) + ')' else set @strWhere = @strWhere + ' and (AssignObject = ''' + @AssignObject + ''')' --{0} id {1} name {2} 表 set @strSql = 'select count({0}) as [Count],isnull((select {1} from {2} where {2}.{0} = crm_Customer.{0}) ,''其他'') as [DisplayName] from crm_Customer ' + @strWhere + ' group by crm_Customer.{0}' if(@Type=0) begin set @strSql = REPLACE(@strSql,'{0}','SourceId') set @strSql = REPLACE(@strSql,'{1}','Source') set @strSql = REPLACE(@strSql,'{2}','crm_CustomerSource') end else if(@Type=1) begin set @strSql = REPLACE(@strSql,'{0}','StateId') set @strSql = REPLACE(@strSql,'{1}','Name') set @strSql = REPLACE(@strSql,'{2}','crm_CustomerState') end else if(@Type=2) begin set @strSql = REPLACE(@strSql,'{0}','TypeId') set @strSql = REPLACE(@strSql,'{1}','TypeName') set @strSql = REPLACE(@strSql,'{2}','crm_CustomerType') end else if(@Type=3) begin set @strSql = REPLACE(@strSql,'{0}','TradeId') set @strSql = REPLACE(@strSql,'{1}','TradeName') set @strSql = REPLACE(@strSql,'{2}','crm_Trade') end --print @strSql execute(@strSql) end GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create PROCEDURE [Proc_CommonSelect] ( @Top varchar(5), @Columns varchar(2000), @Table varchar(50), @Where varchar(1000), @Order varchar(100) ) AS begin declare @sql varchar(4000) set @sql = 'select ' if ''<>@Top and @Top <> '0' set @sql = @sql + ' top ' + @Top + ' ' if ''=@Columns set @sql = @sql + ' * ' else set @sql = @sql + ' ' + @Columns + ' ' set @sql = @sql + ' from ' + @Table + ' ' If @Where <> '' set @sql = @sql + ' where ' + @Where + ' ' If @Order <> '' set @sql = @sql + ' Order By ' + @Order + ' ' execute(@sql) end GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: eapp -- Create date: 2010-11-17 14:46 -- Description: 根据表名和条件删除指定表的一条或多条记录。 -- ==========================�