0

someone help to change this code to php please`

USE [FastJet]
GO
/****** Object:  Table [dbo].[service]    Script Date: 04/14/2015 11:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[service](
    [ser_id] [int] IDENTITY(1,1) NOT NULL,
    [ser_name] [varchar](100) NULL,
    [ser_status] [varchar](1) NULL,
 CONSTRAINT [PK_ison_service] PRIMARY KEY CLUSTERED 
(
    [ser_id] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[report]    Script Date: 04/14/2015 11:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[report](
    [rpt_id] [int] IDENTITY(1,1) NOT NULL,
    [rpt_name] [varchar](100) NULL,
    [rpt_usp] [varchar](100) NULL,
    [rpt_status] [varchar](1) NULL,
 CONSTRAINT [PK_ison_report] PRIMARY KEY CLUSTERED 
(
    [rpt_id] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[queue_out]    Script Date: 04/14/2015 11:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[queue_out](
    [q_id] [bigint] IDENTITY(1,1) NOT NULL,
    [q_from] [varchar](100) NULL,
    [q_start_time] [datetime] NULL,
    [q_end_date_time] [datetime] NULL,
    [q_type] [varchar](50) NULL,
    [q_type] [varchar](50) NULL,
    [q_assigned_to] [int] NULL,
    [q_box_no] [int] NULL,
    [q_message] [varchar](1000) NULL,
    [q_customer_id] [int] NULL,
 CONSTRAINT [PK_queue_out] PRIMARY KEY CLUSTERED 
(
    [q_id] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[queue_in]    Script Date: 04/14/2015 11:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[queue_in](
    [q_id] [bigint] IDENTITY(1,1) NOT NULL,
    [q_from] [varchar](100) NULL,
    [q_start_time] [datetime] NULL,
    [q_end_date_time] [datetime] NULL,
    [q_type] [varchar](50) NULL,
    [q_status] [varchar](50) NULL,
    [q_assigned_to] [int] NULL,
    [q_box_no] [int] NULL,
    [q_message] [varchar](1000) NULL,
    [q_reply_status] [varchar](50) NULL,
    [q_customer_id] [int] NULL,
 CONSTRAINT [PK_queue] PRIMARY KEY CLUSTERED 
(
    [q_id] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[login_session]    Script Date: 04/14/2015 11:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[login_session](
    [ses_id] [int] IDENTITY(1,1) NOT NULL,
    [ses_agent] [varchar](50) NULL,
    [ses_start_time] [datetime] NULL,
    [ses_end_time] [datetime] NULL,
    [ses_status] [varchar](1) NULL,
 CONSTRAINT [PK_ison_login_session] PRIMARY KEY CLUSTERED 
(
    [ses_id] 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
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[usp_call_details]    Script Date: 04/14/2015 11:37:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_call_details] 
(
    @report_id varchar(100),
    @service_id varchar(100),
    @from_date varchar(25),
    @to_date varchar(25)
)
as
begin

    if @report_id = 'Call Details'
    begin 
        select  convert(varchar(21),call_entry_time,13) as CallDate,
                agent_name as Agent,
                call_cli,
                call_alt_number,
                call_name as CallerName,
                call_address1 as Address1,
                call_address2 as Address2,
                call_city as City,
                call_pin as Pin,
                call_type as CallType,
                call_disposition as Disp,
                call_sub_disposition as SubDisp,
                call_remarks as Remarks 
        from    ison_caller_profile,ison_agent
        where   call_agent=agent_id
                and call_entry_time between @from_date and @to_date
    end


end
GO
/****** Object:  Table [dbo].[disposition]    Script Date: 04/14/2015 11:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[disposition](
    [disp_id] [int] IDENTITY(1,1) NOT NULL,
    [disp_call_type] [varchar](50) NULL,
    [disp_disp] [varchar](50) NULL,
    [disp_sub_disp] [varchar](50) NULL,
    [disp_status] [varchar](1) NULL,
 CONSTRAINT [PK_ison_disposition] PRIMARY KEY CLUSTERED 
(
    [disp_id] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[cust_profile]    Script Date: 04/14/2015 11:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cust_profile](
    [call_number] [int] IDENTITY(1,1) NOT NULL,
    [call_ref] [varchar](50) NULL,
    [call_entry_time] [datetime] NULL,
    [call_cli] [varchar](50) NULL,
    [call_email] [varchar](100) NULL,
    [call_alt_number] [varchar](15) NULL,
    [call_name] [varchar](100) NULL,
    [call_address1] [varchar](500) NULL,
    [call_address2] [varchar](500) NULL,
    [call_city] [varchar](100) NULL,
    [call_pin] [varchar](10) NULL,
    [call_type] [varchar](50) NULL,
    [call_disposition] [varchar](100) NULL,
    [call_sub_disposition] [varchar](100) NULL,
    [call_remarks] [varchar](5000) NULL,
    [call_agent] [varchar](100) NULL,
 CONSTRAINT [PK_cust_profile] PRIMARY KEY CLUSTERED 
(
    [call_number] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[agent]    Script Date: 04/14/2015 11:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[agent](
    [agent_id] [int] IDENTITY(1,1) NOT NULL,
    [agent_name] [varchar](50) NULL,
    [agent_login_id] [varchar](50) NULL,
    [agent_password] [varchar](50) NULL,
    [agent_default_page] [varchar](50) NULL,
    [agent_status] [varchar](50) NULL,
    [agent_type] [varchar](50) NULL,
    [agent_service_id] [int] NULL,
    [agent_location] [varchar](100) NULL,
 CONSTRAINT [PK_agent] PRIMARY KEY CLUSTERED 
(
    [agent_id] 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
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[usp_send_message]    Script Date: 04/14/2015 11:37:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_send_message]
(
    @message varchar(1000),
    @cust_id int,
    @agent_id int
)
as
begin
declare @q_type as varchar(50)
declare @q_from as varchar(50)

select top 1 @q_type = q_type,@q_from = q_from from queue_in where q_customer_id = @cust_id and q_assigned_to = @agent_id

insert into queue_out(q_from,q_start_time,q_type,q_status,q_assigned_to,q_box_no,q_message,q_customer_id)
values(@q_from,getdate(),@q_type,'N',@agent_id,0,@message,@cust_id)

Select 'Mesage Send!!!!!!!' as result

end
GO
/****** Object:  StoredProcedure [dbo].[usp_report]    Script Date: 04/14/2015 11:37:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_report] 
(
    @report_id varchar(100),
    @service_id varchar(100),
    @from_date varchar(25),
    @to_date varchar(25)
)
as
begin

    if @report_id = 'Call Details'
    begin 
        select  convert(varchar(20),call_entry_time,13) as CallDate,
                agent_name as Agent,
                call_cli as CallCLI,
                call_alt_number as AltNumber,
                call_name as CallerName,
                call_address1 as Address1,
                call_address2 as Address2,
                call_city as City,
                call_pin as Pin,
                call_type as CallType,
                call_disposition as Disp,
                call_sub_disposition as SubDisp,
                call_remarks as Remarks 
        from    caller_profile,agent
        where   call_agent=agent_id
                and call_entry_time between convert(datetime,@from_date) and convert(datetime,@to_date + ' 23:59:59')
    end
    if @report_id = 'Call Summary'
    begin
        select  call_type as CallType,
                call_disposition as Disp,
                call_sub_disposition as SubDisp,count(*) as CCount
        from    caller_profile,agent
        where   call_agent=agent_id
                and call_entry_time between convert(datetime,@from_date) 
                and convert(datetime,@to_date + ' 23:59:59')
        group by call_type,call_disposition,call_sub_disposition
    end

end
GO
/****** Object:  StoredProcedure [dbo].[usp_queue]    Script Date: 04/14/2015 11:37:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--usp_queue 1,3,1

CREATE procedure [dbo].[usp_queue] 
(
    @cust_id int,
    @agent_id int,
    @box_no int
)
as
begin
declare @header as varchar(100)
declare @strTemp as varchar(1000)
select top 1 @header = q_type+'['+q_from+']' from queue_in where q_assigned_to  = @agent_id and q_box_no = @box_no
order by q_id desc
set @strTemp = '
select q_message as '''+@header+''' from queue_in
where q_assigned_to = '+convert(varchar(10),@agent_id)+'
and q_box_no = '+convert(varchar(10),@box_no)+'
and q_end_date_time is null'
print @strTemp
exec (@strTemp)
end
GO
/****** Object:  StoredProcedure [dbo].[usp_messenger]    Script Date: 04/14/2015 11:37:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--usp_messenger 'Mukesh [SMS(8978374997)]'

CREATE procedure [dbo].[usp_messenger]
(
    @cust_id varchar(50)
)
as
begin
    create table #Temp(Messages varchar(1000),q_start_time datetime)
    insert into #temp
    select 'Cust: ' + q_message as 'Messages',q_start_time 
    from cust_profile,queue_in
    where q_customer_id = call_number
    and call_name+' ['+q_type+'('+q_from+')]' = @cust_id --q_customer_id = @cust_id
    and q_end_date_time is null

    insert into #temp
    select 'Me: ' + q_message as 'Messages',q_start_time
    from cust_profile,queue_out
    where q_customer_id = call_number
    and call_name+' ['+q_type+'('+q_from+')]' = @cust_id --q_customer_id = @cust_id
    and q_end_date_time is null
    select Messages from #temp order by q_start_time
    drop table #Temp
end
GO
/****** Object:  StoredProcedure [dbo].[usp_logout]    Script Date: 04/14/2015 11:37:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_logout] 
(
    @agent      varchar(10)
)
as
begin
--  @ser_id     varchar(10),    

update login_session set 
ses_end_time= GETDATE(),ses_status='C'
where ses_agent = @agent
and ses_end_time = '1 Jan 1900'
and ses_status = 'L'

end
GO
/****** Object:  StoredProcedure [dbo].[usp_login]    Script Date: 04/14/2015 11:37:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_login] 
(
    @agent      varchar(10)
)
as
begin
--  @ser_id     varchar(10),    

insert into login_session
(ses_agent,ses_start_time,ses_end_time,ses_status)
values(@agent,getdate(),'1 Jan 1900','L')
select @@IDENTITY as result
end
GO
/****** Object:  StoredProcedure [dbo].[usp_details]    Script Date: 04/14/2015 11:37:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_details]
(
    @cust_id varchar(100)
)
as
begin
declare @cc as int
    select top 1 call_number,call_name,call_cli,call_email,call_address1,call_address2,call_city
    from cust_profile,queue_in
    where q_customer_id = call_number
    and call_name+' ['+q_type+'('+q_from+')]' = @cust_id --q_customer_id = @cust_id
    and q_end_date_time is null


end
GO
/****** Object:  StoredProcedure [dbo].[usp_customer]    Script Date: 04/14/2015 11:37:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[usp_customer] 
(
    @agent_id int
)
as
begin

select distinct call_name+' ['+q_type+'('+q_from+')]' as Contacts from cust_profile,queue_in
where q_customer_id = call_number
and q_assigned_to = @agent_id
and q_end_date_time is null


end
GO
Inline Code Example Here

`

2
Contributors
1
Reply
14
Views
2 Years
Discussion Span
Last Post by cereal
0

Hi,

if I'm not wrong, this script is creating database tables and procedures, I suppose for MS SQL, so all you need is to convert them to queries that you would run in a database client. For example with the PDO API you can do:

try {

    # connect to the database
    $conn = new PDO("mssql:host=HOSTNAME;dbname=DATABASE", "USERNAME", "PASSWORD");

    # run queries
    $conn->query("SET ANSI_NULL ON");
    $conn->query("SET QUOTED_IDENTIFIER");
    $conn->query("SET ANSI_PADDING ON");
    $conn->query("CREATE TABLE `dbo`.`service`(`ser_id` int IDENTITY(1,1) NOT NULL, `ser_name` varchar(100) NULL, `ser_status` varchar(1) NULL, CONSTRAINT `PK_ison_service` PRIMARY KEY CLUSTERED (`ser_id` ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARY");
    $conn->query("SET ANSI_PADDING OFF");

} catch (PDOException $e) {
    die('Fail: ' . $e->getMessage());
}

In my example I'm using backticks to wrap the column names:

`test`

I'm not sure this is the correct syntax for MS SQL.

Note: the above example uses the PDO_DBLIB driver, but if you're on Windows and using PHP5.3+ you may have to load some modules and change the connection string according to the driver is use, as PDO_DBLIB is not anymore available, as alternatives you could use PDO_ODBC or PDO_SQLSRV. Here you can find some information:

If you can access the command line, consider also to create a simple SQL file and execute an import, something like:

exec("sqlcmd -S CONNECTION/STRING -i /path/to/FILE.sql");

Docs:

Edited by cereal

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.