Convertir campo

[chicorio]

CREATE DEFINER=`teletaxusr`@`%` PROCEDURE `Costs_Calls`(begindate nvarchar(10), enddate nvarchar (10), firstExt varchar (15), LastExt varchar (15), CallType int )
BEGIN


/* Creted by Calos Murillo April 2009

calaculate cost of customers calls */


Declare starDatecall datetime;
Declare EndDateCall datetime;
Declare BeginExt integer;

select cast(concat(begindate ,' 00:00:00') AS datetime) into starDatecall;
select cast(concat(enddate ,' 23:59:59') AS datetime) into EndDateCall ;
select cast(firstExt as unsigned) into BeginExt;

DROP TABLE IF EXISTS Calc;
create temporary table Calc
(Call_Date datetime, cost float (10,3), extension varchar (15), number varchar (10), Call_type varchar (15));


if CallType = 0 then

insert into Calc (Call_Date, cost, extension,number, Call_type )
select ctime,( CEIL(duration /60 ) *
Case when LEFT(calling_num , 1) = '2' and CHARACTER_LENGTH(calling_num )= 8
then 0.01
WHEN LEFT(calling_num , 1) = '8' and CHARACTER_LENGTH(calling_num )= 8
then 0.10
WHEN CHARACTER_LENGTH(calling_num )>= 10
then 0.16
end ) cost, dialed_num, calling_num , ('in')
from tbCalls
where ctime >= starDatecall
and ctime <= EndDateCall
and dialed_num between firstExt and lastExt
and duration > 0
and calling_num <> '';

insert into Calc (Call_Date, cost, extension,number, Call_type )
select ctime,( CEIL(duration /60 ) *
Case When LEFT(dialed_num, 1) = '2' and CHARACTER_LENGTH(dialed_num )= 8
then 0.05
When LEFT(dialed_num, 1) = '8' and CHARACTER_LENGTH(dialed_num)= 8
then 0.10
When LEFT(dialed_num, 1) = '1' and CHARACTER_LENGTH(dialed_num)=12
then 0.16
When LEFT(dialed_num, 2) = '00'
then 0.50
When CHARACTER_LENGTH(dialed_num)=10
then 0.16
else
0.05
End)cost, calling_num, dialed_num, ('out')
from tbCalls
where ctime >= starDatecall
and ctime <= EndDateCall
and calling_num between firstExt and lastExt
and duration > 0;

end if;


if CallType = 1 then

insert into Calc (Call_Date, cost, extension,number, Call_type )
select ctime,( CEIL(duration /60 ) *
Case when LEFT(calling_num , 1) = '2' and CHARACTER_LENGTH(calling_num )= 8
then 0.01
WHEN LEFT(calling_num , 1) = '8' and CHARACTER_LENGTH(calling_num )= 8
then 0.10
WHEN CHARACTER_LENGTH(calling_num )>= 10
then 0.16
end ) cost, dialed_num, calling_num , ('in')
from tbCalls
where ctime >= starDatecall
and ctime <= EndDateCall
and dialed_num between firstExt and lastExt
and duration > 0
and CHARACTER_LENGTH(calling_num )> 4;
end if;






if CallType = 2 then



insert into Calc (Call_Date, cost, extension,number, Call_type )
select ctime,( CEIL(duration /60 ) *
Case When LEFT(dialed_num, 1) = '2' and CHARACTER_LENGTH(dialed_num )= 8
then 0.05
When LEFT(dialed_num, 1) = '8' and CHARACTER_LENGTH(dialed_num)= 8
then 0.10
When LEFT(dialed_num, 1) = '1' and CHARACTER_LENGTH(dialed_num)=12
then 0.16
When LEFT(dialed_num, 2) = '00'
then 0.50
When CHARACTER_LENGTH(dialed_num)=10
then 0.16
else
0.05
End)cost, calling_num, dialed_num, ('out')
from tbCalls
where ctime >= starDatecall
and ctime <= EndDateCall
and dialed_num > 4
and calling_num >=firstExt
and calling_num <=lastExt
and duration > 0;

end if;



select * from Calc
order by extension,Call_Date;


END