work – Records And Memorys

2018-10-8

解决2个问题

a) 如果csl在通话中，关闭diameterr connection , callflow 这边应该也要断掉，但是在10月4-5日的测试中，通话仍然能继续，是csl-dcca 没有检查return code = 3003 的原因，已修改并上传至 gitlab

b) maxis 据说要新换billing 系统，要重新测试SOS，也就是借钱功能，提供的测试号码是： 60142600677 .

需要做出的修改是把 193 collector 的 core.properties 从 live webservice 改为 UAT webservice , 并在supervisor重启 maxis collector 模块。

同时发现， callflow.cpp 中设置 extraNumber 的方法不好，改为在ccloud.cpp 实现。

重新测试 maxis 借钱功能

8001 + 60142600677

把 maxis collector 的

#live-system
service.authentication.url=https://apigateway.maxis.com.my:443/processCommonFworkAPI
service.authentication.tps=2
service.apikey=l7xxad0a5d46597c48ee98bc1fff73520051
service.timeout.socket=10000
service.timeout.connect=10000
service.timeout.connectrequest=10000

#live-system

service.authentication.url=https://apigateway.maxis.com.my:443/processCommonFworkAPI

service.authentication.tps=2

service.apikey=l7xxad0a5d46597c48ee98bc1fff73520051

service.timeout.socket=10000

service.timeout.connect=10000

service.timeout.connectrequest=10000

注释掉 , 用 UAT service

2018-10-9

答应Wong将添加功能：把不需要的ratePlan format 删除，回家后用另一个电脑做，因为是vs2015
为什么diameter-relay 程序可以把log输出到 /logs/out.log ? 把这个程序在centos 电脑跑一遍，看是不是也可以出log

2018-10-10

2018-10-11

实现这个功能：把不需要的ratePlan format 删除，回家后用另一个电脑做，因为是vs2015

格式显示在 cmb_RatePlanFormat 这个listbox ，

完成

2018-10-12

临近下班， lam说： maxis tsg 线路有一条lose connection , 2个tsg的配置页面分别是：

http://10.10.20.123:12358
http://10.10.20.125:12358

1 2	http://10.10.20.123:12358 http://10.10.20.125:12358

从 cctb1 或者 cctb2 访问

一条 tsg断线并不影响cctb2的 live traffic , 因为在 /var/modules/cap-gw/config/m3ua.config 文件中，设置有：

即：可实现2条线路之间的备用切换

2018-10-15

在 cctb1 DB 机器上运行新的 cdrCatcher , 抓VMS CDR , 这个lam 在 FTP server 上upload了 620 个文件，经观察， catcher的速度是2小时600万条cdr .

2018-10-16

把 MyCatcher 加上 switch_setting 的配置界面 — 完成
完善 BulkHttpGen 功能，以实现完整的电话流程模仿

2018-10-17

做一个发送 http request 的测试工具， 1个call由三个request 组成

new ,start ,end

2018-10-18

继续做昨天的

2018-10-19

请假

2018-10-22

下午16：00 据说csl要开始测试
lam说 VMS Format 又可能会增加一个field ，到时改catcher
malay autorest 有一个supply not work

2018-10-23

与xueliang 测试发送 http request 的测试工具

2018-10-24

want to use c# make a smart bill suggest program . can draw cured line base on live deal data , also can find previous similar going and make a suggest .

2018-10-25

vmscdr catcher 新增字段 prov_id , 改cdr catcher

2018-10-26

测试 xueliang的 thrift server

2018-10-29

测试 xueliang的 thrift server

完成 catcher的修改

2018-10-30

Wong 说 rate importer 可能需要在日期之外，还导入时间，此外，旧格式已经不需要

测试 xueliang的 thrift server

2018-10-31

完成Wong 说的修改，把旧的supplier格式去掉, 注意不包括 customer format 。

修改并上传代码到gitee， httpTestTools

2018-11-01

处理 salinda 转发的邮件里提到的 maxis 更新 webservice 的问题，填写所需的unit test case

关于这次更新： a) You will continue to use the same URL that is configured at your application side —- URL不变

2018-11-2

还是maxis要更新的问题

a) 怎么调用 maxis 的 webservice ?

使用 curl 命令，在head 指明 api key

curl -X POST https://apigateway.maxis.com.my:443/processCommonFworkAPI  -H "Content-Type: application/json" -H "apiKey:l7xxad0a5d46597c48ee98bc1fff73520051"  -d '{"processCommonFworkAPI":{"cmnFworkInput":{"msisdn":"60176206804","actionType":"1","channelRefId":"abc123456","channel":"WORLDHUB","tranxId":"7"},"eaiHeader":{"timestamp":"20181118110559","to":"ESB","correlationId":null,"appId":"VASHLR","msgId":"7cb82b85-7836-4727-921a-22564ab23e7a","msgType":"Request","from":"WORLDHUB"}}}'

curl -X POST https://apigateway.maxis.com.my:443/processCommonFworkAPI -H "Content-Type: application/json" -H "apiKey:l7xxad0a5d46597c48ee98bc1fff73520051" -d '{"processCommonFworkAPI":{"cmnFworkInput":{"msisdn":"60176206804","actionType":"1","channelRefId":"abc123456","channel":"WORLDHUB","tranxId":"7"},"eaiHeader":{"timestamp":"20181118110559","to":"ESB","correlationId":null,"appId":"VASHLR","msgId":"7cb82b85-7836-4727-921a-22564ab23e7a","msgType":"Request","from":"WORLDHUB"}}}'

2018-11-5

ccloud cctb2 serviceauthen 又 can not get reply in time , 重启后解决

lam 说 SG autorest 有一个任务不启动，检查后发现是以前的，改设置使其重新运行

How to capture and analyze Mir2 package

2018-11-12

Wong说想在 mera helper 看到 rate
cctb2出现问题，重启后好了
cctb2 loan confirm 出现delay , 把这句 LogTrace(TBCAF_TRACE_LEVEL_3, FYELLOW”try to get loan confirm again

之后的等待时间从 300改为 500

最后出现的错误报告邮件是下午 3：52

2018-11-14

准备解决为什么autoreset task有时候会执行2次的问题
新的项目，做report 的汇总服务器，想用wpf写

2018-11-19

继续做

新的项目，做report 的汇总服务器，想用wpf写

Wong 说需要在 mera 显示的里面看到 Rate

2018-11-23

Yasser 问 csl 的 prompt file 需要什么格式，我回答

need wav format file
for this wav file ‘s detail , better same with this one

wave, mono 8000 hz with 32 bit

salinda 说 ccloud mgr 看到的 report 1 和她自己统计的不一样

也就是说， 2018 年10月份 globe 进的总数不等于 maxis 和 singtel 出的总数

但是， globe进的并不保证都是 maxis和 singtel 的，经检查，发现

globel in total + maxis out total + singtel out total + csl out total + unknown out total

2018-12-4

CSL 的 leon 说：

[Leon] When a call is initialized by user and reached WorldHub, now a check of the B-Num via our API. The call will only be continued if it is belonged to our “prepaid” MSISDN (Otherwise, it will play a prompt and should end the call), We need your help to add one more checking from the response of our API, which to limit the configured cardtype can be continued. Other than those configured cardtype, the handling should be like a call to postpaid. Please call me to discuss for detail if needed.
so i guess for Globe call to CSL, will need to check if prepaid + cardtype
amend this call flow will affect Globe calls to Singtel / Maxis?

意思是要求：对 csl的serviceauthen 返回集，除了以前的code =0 之外，还要检查是不是prepaid ,并且 cardtype只允许这四种（501/ 517 / 525 / 554）

只有满足以上条件才通过

解决办法：修改 spi-impl-hkcsl

加

					int cardType =999;
					
					cardType = responseJson.getJSONObject("profile").getInt("prepaidCardType");

int cardType =999;

cardType = responseJson.getJSONObject("profile").getInt("prepaidCardType");

					if (bSaved) {
						if (subType != 8) {
							requestJson.put("status", ServiceAuthentication.Subscriber_Is_Postpaid);
						} else {
							
							if((cardType != 501 ) && (cardType != 517 ) && (cardType != 525 ) && (cardType != 554 )) {
								requestJson.put("status", ServiceAuthentication.Wrong_CardType);
							
							}
							else {
								requestJson.put("status", ServiceAuthentication.Success);
								
							}
									
						}
						
						
					}

if (bSaved) {

if (subType != 8) {

requestJson.put("status", ServiceAuthentication.Subscriber_Is_Postpaid);

} else {

if((cardType != 501 ) && (cardType != 517 ) && (cardType != 525 ) && (cardType != 554 )) {

requestJson.put("status", ServiceAuthentication.Wrong_CardType);

}

else {

requestJson.put("status", ServiceAuthentication.Success);

}

2018-12-27

回家后：

1 android service

2 back up samsung photo

2019-2-11

salinda 说 197机器空间不足
为什么xl还能收到旧SOA的邮件

已解决：是197的 /var/modules/cap-gw/logs log文件过多的原因，

检查用： sudo du -sh *

删除用： rm -f *

2019-02-12

发邮件程序新增 sp_SOA_Yesterday_Low_ASR_Alert_LY 任务，在。24机器上

2019-02-14

2019-2-14

在 24 机器上新建存储过程 sp_monitor_soabal_checker_4_LY

是 uncheck_bill 的后续

邮件标题是

SOA Over Threshhold(Main Bill Server)

发给：

mzcw2011@sina.cn;accounts@worldhubcom.com;liang.xue@worldhubcom.com;billing.expandnet@gmail.com;th.ang@worldhubcom.com;sp.kor@worldhubcom.com;chloe.ang@worldhubcom.com

把 24机器的soa daily monitor —> uncheck bill 任务停止

2019-3-14

在209 机器的 ANumDB 数据库执行查询，实现kit要求的报表

ANumDB 每一个表代表一个 Anumber ，表中可能有多个 BNumber

查上周六到周三的每一个 a number 的 acd
并且，每天新出现的 a number

学 RouteDB 的 [sp_ui_blacklist_recal] 存储过程

建表
CREATE TABLE [dbo].[TableName_LY](
	[tablename] varchar(50),
	[processid] int
	)

	
-------------


insert into 
    TableName_LY
SELECT TABLE_NAME,0
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='ANumDB'

建表

CREATE TABLE [dbo].[TableName_LY](

[tablename] varchar(50),

[processid] int

)

-------------

insert into

TableName_LY

SELECT TABLE_NAME,0

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='ANumDB'

	DECLARE @icount int
DECLARE @tablename VARCHAR(50)
declare @sql nvarchar(4000)  
declare @sumDuration int 
declare @startday datetime
declare @endday datetime


set @icount =1 
set @startday = '2019-03-9 00:00:00'
set  @endday = '2019-03-14'

select top 1  @tablename=tablename
FROM TableName_LY
WHERE processid = 0

while (@@ROWCOUNT > 0) --and @icount<5
begin
	--set @icount= @icount+1
	--print @tablename; 

	--select @sumDuration = sum(Duration_B) from @tablename

	set @sql = 'select @sd = Avg(duration_b)/60.00 from ANumDB.dbo.['+@tablename + ']' + ' where duration_b > 0 and setuptime_a > ''' + CONVERT(VARCHAR(10),DATEADD(DD,0,@startday), 101) + '''' + ' and setuptime_a < ''' + CONVERT(VARCHAR(10),DATEADD(DD,0,@endday), 101) + ''''
	
	--print @sql

	BEGIN TRY
		execute sp_executesql @sql , N'@sd int output', @sd = @sumDuration OUTPUT
	END TRY
	BEGIN CATCH
		print ERROR_MESSAGE()
		print 'skip'
	END CATCH

	--print @sumDuration

	update TableName_LY set processid = 1, sumDuration = @sumDuration where tablename=@tablename
	select top 1  @tablename=tablename
		FROM TableName_LY
		WHERE processid = 0

end


--select * from TableName_LY where processid = 1

--update TableName_LY set processid = 0 , sumDuration =0  where processid = 1



--select count(*) from TableName_LY where sumDuration>0

--select count(*) from TableName_LY where processid=1


--select * from [232_60175682721]

DECLARE @icount int

DECLARE @tablename VARCHAR(50)

declare @sql nvarchar(4000)

declare @sumDuration int

declare @startday datetime

declare @endday datetime

set @icount =1

set @startday = '2019-03-9 00:00:00'

set @endday = '2019-03-14'

select top 1 @tablename=tablename

FROM TableName_LY

WHERE processid = 0

while (@@ROWCOUNT > 0) --and @icount<5

begin

--set @icount= @icount+1

--print @tablename;

--select @sumDuration = sum(Duration_B) from @tablename

set @sql = 'select @sd = Avg(duration_b)/60.00 from ANumDB.dbo.['+@tablename + ']' + ' where duration_b > 0 and setuptime_a > ''' + CONVERT(VARCHAR(10),DATEADD(DD,0,@startday), 101) + '''' + ' and setuptime_a < ''' + CONVERT(VARCHAR(10),DATEADD(DD,0,@endday), 101) + ''''

--print @sql

BEGIN TRY

execute sp_executesql @sql , N'@sd int output', @sd = @sumDuration OUTPUT

END TRY

BEGIN CATCH

print ERROR_MESSAGE()

print 'skip'

END CATCH

--print @sumDuration

update TableName_LY set processid = 1, sumDuration = @sumDuration where tablename=@tablename

select top 1 @tablename=tablename

FROM TableName_LY

WHERE processid = 0

end

--select * from TableName_LY where processid = 1

--update TableName_LY set processid = 0 , sumDuration =0 where processid = 1

--select count(*) from TableName_LY where sumDuration>0

--select count(*) from TableName_LY where processid=1

--select * from [232_60175682721]

此查询耗时大约24小时，待优化

待完成：用 java spring boot 做一个 server ，和 whole sale httprequest 配合用

2019-03-15

给 sheypei 修改 mera help , 要显示 rate , 根据 carrier name

2019-03-18

完成 salinda 需要的查询

完成 sheypei 的修改

2019-03-20

维护 209 route server 和

C:\inetpub\logs\LogFiles\W3SVC3 IIS log

C:\inetpub\logs\LogFiles\W3SVC3

2019-04-09

salinda 让查 4-1 到 4-7 号的unique number
phooiyen 昨天说 mera help 查 pakistan area 查不到 , 因为现在不支持按照 area 查询
calvin 说有一个号码可能加错黑名单了， 6285261708972 ，说一般拉黑的都是马来号码，这个是印度的，可能是 roaming

2019-04-10

完成 calvin 说的 routemanagement : frmSIMBlackList 查询日期不起作用的修改，并上传 git

select top 5 * from SIMBlackList where DATEADD(month,-1,ExpiryTime) > '2019-3-21'

1	select top 5 * from SIMBlackList where DATEADD(month,-1,ExpiryTime) > '2019-3-21'

salinda 昨天5点多说：要 3月全月的unique number 和 total duration

因为cdr只保存1个月的，所以要查3月的，要在3月最后一天查

2019-4-11

和 calvin 确认修改是否生效和正确？ calvin回复说是的

2019-04-12

准备让 csl callflow 同时在 cctb1 和 cctb2 上线，现在只在cctb1 可以

数据库

在 cctb2 的数据库上执行

select * from Operator

insert into Operator values(22,'csl','admin','2019-4-12','','hk.csl')

select * from AllRegion

insert into AllRegion values(852,'HongKong')

select * from NAP

select * from NAPGroup

insert into NAPGroup values(16,'HongKong198',0,1,'')
insert into NAPGroup values(17,'HongKong199',0,1,'')

insert into nap values(18,16,'HongKongNap198','VMS198',1,0,1,'')
insert into nap values(19,17,'HongKongNap199','VMS199',1,0,1,'')

select * from OperatorRoute

insert into OperatorRoute values(9,22,'Sequence',16,1,17,2,0,0,0,0,0,0,0,'admin','2019-04-12','','HongKong')


select * from SettingOut

select * from OpOut

insert into OpOut values(16,22,'','852->9002852',2,'admin','2019-4-12','')
insert into OpOut values(17,22,'','852->9002852',2,'admin','2019-4-12','')

select * from Operator

insert into Operator values(22,'csl','admin','2019-4-12','','hk.csl')

select * from AllRegion

insert into AllRegion values(852,'HongKong')

select * from NAP

select * from NAPGroup

insert into NAPGroup values(16,'HongKong198',0,1,'')

insert into NAPGroup values(17,'HongKong199',0,1,'')

insert into nap values(18,16,'HongKongNap198','VMS198',1,0,1,'')

insert into nap values(19,17,'HongKongNap199','VMS199',1,0,1,'')

select * from OperatorRoute

insert into OperatorRoute values(9,22,'Sequence',16,1,17,2,0,0,0,0,0,0,0,'admin','2019-04-12','','HongKong')

select * from SettingOut

select * from OpOut

insert into OpOut values(16,22,'','852->9002852',2,'admin','2019-4-12','')

insert into OpOut values(17,22,'','852->9002852',2,'admin','2019-4-12','')

DONE

prompt

把 csl 的 prompt 加到 cctb2 去 DONE

program

备份 cctb2 的 CVHCCloud.hpp , CVHCCloud.cpp , CVHCallFlow.hpp , CVHCallFlow.cpp ,

然后视情况合并 cctb1 和 cctb2 的版本

当前 cctb1 和 cctb2 的区别 :

cctb1 有 pack callfow , cctb2 没有

cctb1 用的是 tb 12360 端口， cctb2 是 12359

结论： CSimpleCall.cpp 和 CSimpleCall.hpp 不用改

cctb2 的 CVHCallFlow.cpp 注释掉这句

	if((nap == "VMS198") || (nap == "VMS199")) 

	{
		ptrLegAttr->GetProfile().SetByProfileName( "profile729" );

	}

if((nap == "VMS198") || (nap == "VMS199"))

{

ptrLegAttr->GetProfile().SetByProfileName( "profile729" );

}

java program

在 cctb2 194机器需要加上的 java 程序有： mediator , collector , csl-dcca , csl-relay

194 add

collector 加上 mkdir hkg.csl-collector2

mediator 新建 hkg.csl

让 csl-dcca 能写日志

Done

但是 supervisor 显示的状态不正常，不过程序似乎已经启动了

supervisor 的配置文件在 /etc/supercisord.conf

修改完需要

重新加载程序

supervisorctl reload

更新配置

supervisorctl update

从 193 往 194 传文件：

scp -P 9900 diameter-relay-0.0.1-SNAPSHOT.jar waqas@103.244.191.194:/var/modules/diameter-relay

1	scp -P 9900 diameter-relay-0.0.1-SNAPSHOT.jar waqas@103.244.191.194:/var/modules/diameter-relay

diameter-relay

首先，需要 java-sctp 库支持，在 cctb2 /var/modules 新建 java-sctp 文件夹

用 scp 命令：

scp -P 9900 -r /var/modules/java-sctp waqas@103.244.191.194:/var/modules

1	scp -P 9900 -r /var/modules/java-sctp waqas@103.244.191.194:/var/modules

diameter 怎么和 pccw 连接

cctb2 的 config 文件是：

diameter:
    origin-host: pxy02.worldhubcom.com
    origin-port: 3868
    channel: sccp
    origin-realm: worldhubcom.com
    vendor-id: 99999
    diameter-peers:
      -
        host: dra01-n1-hk05.pccwglobal.ipxnetwork.org
        realm: pccw.com
        port: 3868
        channel: sccp
        initiate-connection: true
      -
        host: dcca-hkcsl-01.worldhubcom.com
        realm: worldhubcom.com
        port: 3869
        channel: tcp
        initiate-connection: false

server:
    port: 8082

diameter:

origin-host: pxy02.worldhubcom.com

origin-port: 3868

channel: sccp

origin-realm: worldhubcom.com

vendor-id: 99999

diameter-peers:

host: dra01-n1-hk05.pccwglobal.ipxnetwork.org

realm: pccw.com

port: 3868

channel: sccp

initiate-connection: true

host: dcca-hkcsl-01.worldhubcom.com

realm: worldhubcom.com

port: 3869

channel: tcp

initiate-connection: false

server:

port: 8082

当启动 diameter-relay 时，显示：

Disconnecting due to no CER/CEA

1	Disconnecting due to no CER/CEA

Closing connection to aaa://dra01-n1-hk05.pccwglobal.ipxnetwork.org:3868

1	Closing connection to aaa://dra01-n1-hk05.pccwglobal.ipxnetwork.org:3868

2019-04-23

改 103.244.191.218 1455 的 ReportDB —> [sp_rpt_country_cust_src_date_0]

逻辑是：先根据日期检查数据库有没有这个表（表名假定为：D20190429 这个是每天一个的），以及这个表的 rowcount 是不是为零

有且不为零：那么就查这个表

有但为零：查 rpt_daily_today , 可能是因为今天的表还没生成

没有：查 rpt_daily_today , 可能是因为今天的表还没生成

改完以后的存储过程：

USE [ReportDB]
GO
/****** Object:  StoredProcedure [dbo].[sp_rpt_country_cust_src_date_0]    Script Date: 2019/4/23 9:17:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_rpt_country_cust_src_date_0]
@Date date
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	declare @sql nvarchar(4000)  
	declare @table as nvarchar(50)

	declare @sTableName varchar(50) 
	declare @iRowCount int 

	set @table= dbo.fGetDailyTableName(@Date)

	set @sTableName = ''
	set @iRowCount = 0


    --- check if this table exist 
	set @sql = 'SELECT @ic = count(*) FROM dbo.sysobjects where id = object_id(''dbo.['+@table + ']'') and OBJECTPROPERTY(id, ''IsTable'') = 1 '

	set @sql = 'SELECT @name =
				[Tables].name ,
				@rowcount = SUM([Partitions].[rows]) 
				FROM sys.tables AS [Tables]
				JOIN sys.partitions AS [Partitions]
				ON [Tables].[object_id] = [Partitions].[object_id]
				AND [Partitions].index_id IN ( 0, 1 )
				WHERE [Tables].name =''' + @table + '''
				group by  [Tables].name'

				--EXEC sp_executesql @sql, N'@p1 INT, @p2 INT, @p3 INT', @p1, @p2, @p3;

	execute sp_executesql @sql ,N'@name varchar(50) output , @rowcount int output', @name =@sTableName OUTPUT, @rowcount = @iRowCount OUTPUT

	--print @sql 
	--print @sTableName
	--print @iRowCount
	
	if (@sTableName<>'')   --  exist this table    
	BEGIN
		---if table row count =0 
		if (@iRowCount>0)
		BEGIN

			set @sql = ''
			set @sql = @sql+ ' select CountryDST,sum(SuccCalls) as SuccCalls,sum(TotalCalls-FailedButNotFinal) as TotalCalls, '
			set @sql = @sql+ ' CAST(sum(TotalMinutes) AS NUMERIC(18,2))  as TotalMinutes, '
			set @sql = @sql+ ' (case '
			set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
			set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
			set @sql = @sql+ ' end) as ASR,'
			set @sql = @sql+ ' (case '
			set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
			set @sql = @sql+ ' else sum(SuccCalls+FailedButConnectCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
			set @sql = @sql+ ' end) as CSR,'
			set @sql = @sql+ ' (case '
			set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
			set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'
			set @sql = @sql+ ' end) as ACD,'
			set @sql = @sql+ ' (case '
			set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
			set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
			set @sql = @sql+ ' end) * (case '
			set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
			set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'
			set @sql = @sql+ ' end)  *10000 as RQI,'
			set @sql = @sql+ ' CAST(SUM(Fee) AS NUMERIC(18,2)) as Fee, '
			set @sql = @sql+ ' CAST(sum(Cost) AS NUMERIC(18,2)) as Cost, '
			set @sql = @sql+ ' CAST((SUM(Fee)-SUM(Cost)) AS NUMERIC(18,2)) as Profit,'
			set @sql = @sql+ ' (case '
			set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
			set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/sum(SuccCalls)'
			set @sql = @sql+ ' end) as MarginPerCall,'
			set @sql = @sql+ '  (case '
			set @sql = @sql+ ' when sum(TotalMinutes) = 0 then 0'
			set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/ sum(TotalMinutes)'
			set @sql = @sql+ ' end) as MarginPerMin'
			set @sql = @sql+ ' from '+ @table + ''
			set @sql = @sql+ ' group by  CountryDST'
	
			execute sp_executesql @sql

		END
		ELSE   --  table  row count =0 
		BEGIN

			set @sql = ''
			set @sql = @sql+ ' select CountryDST,sum(SuccCalls) as SuccCalls,sum(TotalCalls-FailedButNotFinal) as TotalCalls, '
			set @sql = @sql+ ' CAST(sum(TotalMinutes) AS NUMERIC(18,2))  as TotalMinutes, '
			set @sql = @sql+ ' (case '
			set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
			set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
			set @sql = @sql+ ' end) as ASR,'
			set @sql = @sql+ ' (case '
			set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
			set @sql = @sql+ ' else sum(SuccCalls+FailedButConnectCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
			set @sql = @sql+ ' end) as CSR,'
			set @sql = @sql+ ' (case '
			set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
			set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'
			set @sql = @sql+ ' end) as ACD,'
			set @sql = @sql+ ' (case '
			set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
			set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
			set @sql = @sql+ ' end) * (case '
			set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
			set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'
			set @sql = @sql+ ' end)  *10000 as RQI,'
			set @sql = @sql+ ' CAST(SUM(Fee) AS NUMERIC(18,2)) as Fee, '
			set @sql = @sql+ ' CAST(sum(Cost) AS NUMERIC(18,2)) as Cost, '
			set @sql = @sql+ ' CAST((SUM(Fee)-SUM(Cost)) AS NUMERIC(18,2)) as Profit,'
			set @sql = @sql+ ' (case '
			set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
			set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/sum(SuccCalls)'
			set @sql = @sql+ ' end) as MarginPerCall,'
			set @sql = @sql+ '  (case '
			set @sql = @sql+ ' when sum(TotalMinutes) = 0 then 0'
			set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/ sum(TotalMinutes)'
			set @sql = @sql+ ' end) as MarginPerMin'
			set @sql = @sql+ ' from rpt_daily_today' + ''
			set @sql = @sql+ ' group by  CountryDST'
	
			execute sp_executesql @sql


		END    --  end of  table  row count =0 

		
	
	
	END   --  end of  exist this table     
	ELSE  --  not exist this table  
	BEGIN


		-- Insert statements for procedure here
		set @sql = ''
		set @sql = @sql+ ' select CountryDST,sum(SuccCalls) as SuccCalls,sum(TotalCalls-FailedButNotFinal) as TotalCalls, '
		set @sql = @sql+ ' CAST(sum(TotalMinutes) AS NUMERIC(18,2))  as TotalMinutes, '
		set @sql = @sql+ ' (case '
		set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
		set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
		set @sql = @sql+ ' end) as ASR,'
		set @sql = @sql+ ' (case '
		set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
		set @sql = @sql+ ' else sum(SuccCalls+FailedButConnectCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
		set @sql = @sql+ ' end) as CSR,'
		set @sql = @sql+ ' (case '
		set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
		set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'
		set @sql = @sql+ ' end) as ACD,'
		set @sql = @sql+ ' (case '
		set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
		set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
		set @sql = @sql+ ' end) * (case '
		set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
		set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'
		set @sql = @sql+ ' end)  *10000 as RQI,'
		set @sql = @sql+ ' CAST(SUM(Fee) AS NUMERIC(18,2)) as Fee, '
		set @sql = @sql+ ' CAST(sum(Cost) AS NUMERIC(18,2)) as Cost, '
		set @sql = @sql+ ' CAST((SUM(Fee)-SUM(Cost)) AS NUMERIC(18,2)) as Profit,'
		set @sql = @sql+ ' (case '
		set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
		set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/sum(SuccCalls)'
		set @sql = @sql+ ' end) as MarginPerCall,'
		set @sql = @sql+ '  (case '
		set @sql = @sql+ ' when sum(TotalMinutes) = 0 then 0'
		set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/ sum(TotalMinutes)'
		set @sql = @sql+ ' end) as MarginPerMin'
		set @sql = @sql+ ' from rpt_daily_today' + ''
		set @sql = @sql+ ' group by  CountryDST'
	
		execute sp_executesql @sql


	END
END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

USE [ReportDB]

/****** Object: StoredProcedure [dbo].[sp_rpt_country_cust_src_date_0] Script Date: 2019/4/23 9:17:40 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[sp_rpt_country_cust_src_date_0]

@Date date

-- Add the parameters for the stored procedure here

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare @sql nvarchar(4000)

declare @table as nvarchar(50)

declare @sTableName varchar(50)

declare @iRowCount int

set @table= dbo.fGetDailyTableName(@Date)

set @sTableName = ''

set @iRowCount = 0

--- check if this table exist

set @sql = 'SELECT @ic = count(*) FROM dbo.sysobjects where id = object_id(''dbo.['+@table + ']'') and OBJECTPROPERTY(id, ''IsTable'') = 1 '

set @sql = 'SELECT @name =

[Tables].name ,

@rowcount = SUM([Partitions].[rows])

FROM sys.tables AS [Tables]

JOIN sys.partitions AS [Partitions]

ON [Tables].[object_id] = [Partitions].[object_id]

AND [Partitions].index_id IN ( 0, 1 )

WHERE [Tables].name =''' + @table + '''

group by [Tables].name'

--EXEC sp_executesql @sql, N'@p1 INT, @p2 INT, @p3 INT', @p1, @p2, @p3;

execute sp_executesql @sql ,N'@name varchar(50) output , @rowcount int output', @name =@sTableName OUTPUT, @rowcount = @iRowCount OUTPUT

--print @sql

--print @sTableName

--print @iRowCount

if (@sTableName<>'') -- exist this table

BEGIN

---if table row count =0

if (@iRowCount>0)

BEGIN

set @sql = ''

set @sql = @sql+ ' select CountryDST,sum(SuccCalls) as SuccCalls,sum(TotalCalls-FailedButNotFinal) as TotalCalls, '

set @sql = @sql+ ' CAST(sum(TotalMinutes) AS NUMERIC(18,2)) as TotalMinutes, '

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) as ASR,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls+FailedButConnectCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) as CSR,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'

set @sql = @sql+ ' end) as ACD,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) * (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'

set @sql = @sql+ ' end) *10000 as RQI,'

set @sql = @sql+ ' CAST(SUM(Fee) AS NUMERIC(18,2)) as Fee, '

set @sql = @sql+ ' CAST(sum(Cost) AS NUMERIC(18,2)) as Cost, '

set @sql = @sql+ ' CAST((SUM(Fee)-SUM(Cost)) AS NUMERIC(18,2)) as Profit,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/sum(SuccCalls)'

set @sql = @sql+ ' end) as MarginPerCall,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalMinutes) = 0 then 0'

set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/ sum(TotalMinutes)'

set @sql = @sql+ ' end) as MarginPerMin'

set @sql = @sql+ ' from '+ @table + ''

set @sql = @sql+ ' group by CountryDST'

execute sp_executesql @sql

END

ELSE -- table row count =0

BEGIN

set @sql = ''

set @sql = @sql+ ' select CountryDST,sum(SuccCalls) as SuccCalls,sum(TotalCalls-FailedButNotFinal) as TotalCalls, '

set @sql = @sql+ ' CAST(sum(TotalMinutes) AS NUMERIC(18,2)) as TotalMinutes, '

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) as ASR,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls+FailedButConnectCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) as CSR,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'

set @sql = @sql+ ' end) as ACD,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) * (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'

set @sql = @sql+ ' end) *10000 as RQI,'

set @sql = @sql+ ' CAST(SUM(Fee) AS NUMERIC(18,2)) as Fee, '

set @sql = @sql+ ' CAST(sum(Cost) AS NUMERIC(18,2)) as Cost, '

set @sql = @sql+ ' CAST((SUM(Fee)-SUM(Cost)) AS NUMERIC(18,2)) as Profit,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/sum(SuccCalls)'

set @sql = @sql+ ' end) as MarginPerCall,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalMinutes) = 0 then 0'

set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/ sum(TotalMinutes)'

set @sql = @sql+ ' end) as MarginPerMin'

set @sql = @sql+ ' from rpt_daily_today' + ''

set @sql = @sql+ ' group by CountryDST'

execute sp_executesql @sql

END -- end of table row count =0

END -- end of exist this table

ELSE -- not exist this table

BEGIN

-- Insert statements for procedure here

set @sql = ''

set @sql = @sql+ ' select CountryDST,sum(SuccCalls) as SuccCalls,sum(TotalCalls-FailedButNotFinal) as TotalCalls, '

set @sql = @sql+ ' CAST(sum(TotalMinutes) AS NUMERIC(18,2)) as TotalMinutes, '

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) as ASR,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls+FailedButConnectCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) as CSR,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'

set @sql = @sql+ ' end) as ACD,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) * (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'

set @sql = @sql+ ' end) *10000 as RQI,'

set @sql = @sql+ ' CAST(SUM(Fee) AS NUMERIC(18,2)) as Fee, '

set @sql = @sql+ ' CAST(sum(Cost) AS NUMERIC(18,2)) as Cost, '

set @sql = @sql+ ' CAST((SUM(Fee)-SUM(Cost)) AS NUMERIC(18,2)) as Profit,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/sum(SuccCalls)'

set @sql = @sql+ ' end) as MarginPerCall,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalMinutes) = 0 then 0'

set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/ sum(TotalMinutes)'

set @sql = @sql+ ' end) as MarginPerMin'

set @sql = @sql+ ' from rpt_daily_today' + ''

set @sql = @sql+ ' group by CountryDST'

execute sp_executesql @sql

END

测试用的 sql

select top 5 * from rpt_daily_today


declare @dt as date 
set @dt = '2019-4-1 09:30:00'

select 'D' + convert (nvarchar,@dt,112)


--如果还没有这个表 
--select top 5 * from D20190429


---  
declare @table as nvarchar(50)
declare @sql nvarchar(4000)  
declare @iCount int  
declare @Date date

set @Date = '2019-4-1 09:30:00'

set @table= dbo.fGetDailyTableName(@Date)

set @sql = ''

--set @sql = 'select @sd = IsNull(sum(Duration_B),0) from ANumDB.dbo.['+@tablename + '] where duration_b > 0 '

--SELECT @iCount = count(*) FROM dbo.sysobjects where id = object_id('dbo.[D20190401]') and OBJECTPROPERTY(id, 'IsTable') = 1 
--print @iCount

set @sql = 'SELECT @ic = count(*) FROM dbo.sysobjects where id = object_id(''dbo.['+@table + ']'') and OBJECTPROPERTY(id, ''IsTable'') = 1 '
--print  @sql
execute sp_executesql @sql , N'@ic int output', @ic = @iCount OUTPUT
print @iCount

------  row count 

set @sql = ''

set @sql = 'SELECT @ic = count(*) FROM dbo.['+@table + ']'
execute sp_executesql @sql , N'@ic int output', @ic = @iCount OUTPUT
print @iCount







SELECT @iCount = count(*) FROM dbo.sysobjects where id = object_id(N'dbo.[D20190401]') and OBJECTPROPERTY(id, N'IsTable') = 1
SELECT count(*) FROM dbo.sysobjects where id = object_id('dbo.[D20190401]') and OBJECTPROPERTY(id, N'IsTable') = 1
print @iCount

IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[D20190401]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		print '1'
	END


set @sql = 'select @sd = IsNull(sum(Duration_B),0) from ANumDB.dbo.['+@tablename + '] where duration_b > 0 and setuptime_a > ''' + CONVERT(VARCHAR(10),DATEADD(DD,0,@startday), 101) + '''' + ' and setuptime_a < ''' + CONVERT(VARCHAR(10),DATEADD(DD,0,@endday), 101) + '''' 
--print @sql

BEGIN TRY
	execute sp_executesql @sql , N'@sd int output', @sd = @sumDuration OUTPUT
	set @sumDuration = IsNull(@sumDuration,0)
END TRY
BEGIN CATCH
	print ERROR_MESSAGE()
	print 'skip'
END CATCH





    -- Insert statements for procedure here
	set @sql = ''
	set @sql = @sql+ ' select CountryDST,sum(SuccCalls) as SuccCalls,sum(TotalCalls-FailedButNotFinal) as TotalCalls, '
	set @sql = @sql+ ' CAST(sum(TotalMinutes) AS NUMERIC(18,2))  as TotalMinutes, '
	set @sql = @sql+ ' (case '
	set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
	set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
	set @sql = @sql+ ' end) as ASR,'
	set @sql = @sql+ ' (case '
	set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
	set @sql = @sql+ ' else sum(SuccCalls+FailedButConnectCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
	set @sql = @sql+ ' end) as CSR,'
	set @sql = @sql+ ' (case '
	set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
	set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'
	set @sql = @sql+ ' end) as ACD,'
	set @sql = @sql+ ' (case '
	set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'
	set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '
	set @sql = @sql+ ' end) * (case '
	set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
	set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'
	set @sql = @sql+ ' end)  *10000 as RQI,'
	set @sql = @sql+ ' CAST(SUM(Fee) AS NUMERIC(18,2)) as Fee, '
	set @sql = @sql+ ' CAST(sum(Cost) AS NUMERIC(18,2)) as Cost, '
	set @sql = @sql+ ' CAST((SUM(Fee)-SUM(Cost)) AS NUMERIC(18,2)) as Profit,'
	set @sql = @sql+ ' (case '
	set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'
	set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/sum(SuccCalls)'
	set @sql = @sql+ ' end) as MarginPerCall,'
	set @sql = @sql+ '  (case '
	set @sql = @sql+ ' when sum(TotalMinutes) = 0 then 0'
	set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/ sum(TotalMinutes)'
	set @sql = @sql+ ' end) as MarginPerMin'
	--set @sql = @sql+ ' from '+ @table + ''
	set @sql = @sql+ ' from D20190429'
	set @sql = @sql+ ' group by  CountryDST'
	
	execute sp_executesql @sql



---  check  if table exist   

IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[D20190429]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		print '1'
	END



SELECT CONVERT(bigint, rows)

FROM sysindexes

WHERE id = OBJECT_ID('D20190401')

AND indid < 2


SELECT COUNT(*) FROM D20190401


exec  [sp_rpt_country_cust_src_date_0] '2019-4-22 09:30:00'


SELECT * FROM dbo.sysobjects where id = object_id('dbo.[D20190401]') and OBJECTPROPERTY(id, N'IsTable') = 1
select top 1 * from sys.partitions


SELECT    A.Name, SUM(B.rows) AS 'RowCount'

FROM        sys.objects A

INNER JOIN sys.partitions B ON A.id = B.object_id

WHERE       A.id = object_id('dbo.[D20190401]') and OBJECTPROPERTY(A.id, N'IsTable') = 1


SELECT 
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
WHERE [Tables].name = N'D20190401'
group by  [Tables].name




SELECT 
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
WHERE [Tables].name = N'D20190423'
group by  [Tables].name


exec  [sp_rpt_country_cust_src_date_0] '2019-4-23 09:30:00'

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

select top 5 * from rpt_daily_today

declare @dt as date

set @dt = '2019-4-1 09:30:00'

select 'D' + convert (nvarchar,@dt,112)

--如果还没有这个表

--select top 5 * from D20190429

---

declare @table as nvarchar(50)

declare @sql nvarchar(4000)

declare @iCount int

declare @Date date

set @Date = '2019-4-1 09:30:00'

set @table= dbo.fGetDailyTableName(@Date)

set @sql = ''

--set @sql = 'select @sd = IsNull(sum(Duration_B),0) from ANumDB.dbo.['+@tablename + '] where duration_b > 0 '

--SELECT @iCount = count(*) FROM dbo.sysobjects where id = object_id('dbo.[D20190401]') and OBJECTPROPERTY(id, 'IsTable') = 1

--print @iCount

set @sql = 'SELECT @ic = count(*) FROM dbo.sysobjects where id = object_id(''dbo.['+@table + ']'') and OBJECTPROPERTY(id, ''IsTable'') = 1 '

--print @sql

execute sp_executesql @sql , N'@ic int output', @ic = @iCount OUTPUT

print @iCount

------ row count

set @sql = ''

set @sql = 'SELECT @ic = count(*) FROM dbo.['+@table + ']'

execute sp_executesql @sql , N'@ic int output', @ic = @iCount OUTPUT

print @iCount

SELECT @iCount = count(*) FROM dbo.sysobjects where id = object_id(N'dbo.[D20190401]') and OBJECTPROPERTY(id, N'IsTable') = 1

SELECT count(*) FROM dbo.sysobjects where id = object_id('dbo.[D20190401]') and OBJECTPROPERTY(id, N'IsTable') = 1

print @iCount

IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[D20190401]') and OBJECTPROPERTY(id, N'IsTable') = 1)

BEGIN

print '1'

END

set @sql = 'select @sd = IsNull(sum(Duration_B),0) from ANumDB.dbo.['+@tablename + '] where duration_b > 0 and setuptime_a > ''' + CONVERT(VARCHAR(10),DATEADD(DD,0,@startday), 101) + '''' + ' and setuptime_a < ''' + CONVERT(VARCHAR(10),DATEADD(DD,0,@endday), 101) + ''''

--print @sql

BEGIN TRY

execute sp_executesql @sql , N'@sd int output', @sd = @sumDuration OUTPUT

set @sumDuration = IsNull(@sumDuration,0)

END TRY

BEGIN CATCH

print ERROR_MESSAGE()

print 'skip'

END CATCH

-- Insert statements for procedure here

set @sql = ''

set @sql = @sql+ ' select CountryDST,sum(SuccCalls) as SuccCalls,sum(TotalCalls-FailedButNotFinal) as TotalCalls, '

set @sql = @sql+ ' CAST(sum(TotalMinutes) AS NUMERIC(18,2)) as TotalMinutes, '

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) as ASR,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls+FailedButConnectCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) as CSR,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'

set @sql = @sql+ ' end) as ACD,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalCalls-FailedButNotFinal) = 0 then 0'

set @sql = @sql+ ' else sum(SuccCalls)*1.000/sum(TotalCalls-FailedButNotFinal) '

set @sql = @sql+ ' end) * (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else sum(TotalMinutes)/sum(SuccCalls)'

set @sql = @sql+ ' end) *10000 as RQI,'

set @sql = @sql+ ' CAST(SUM(Fee) AS NUMERIC(18,2)) as Fee, '

set @sql = @sql+ ' CAST(sum(Cost) AS NUMERIC(18,2)) as Cost, '

set @sql = @sql+ ' CAST((SUM(Fee)-SUM(Cost)) AS NUMERIC(18,2)) as Profit,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(SuccCalls) = 0 then 0'

set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/sum(SuccCalls)'

set @sql = @sql+ ' end) as MarginPerCall,'

set @sql = @sql+ ' (case '

set @sql = @sql+ ' when sum(TotalMinutes) = 0 then 0'

set @sql = @sql+ ' else (SUM(Fee)-SUM(Cost))/ sum(TotalMinutes)'

set @sql = @sql+ ' end) as MarginPerMin'

--set @sql = @sql+ ' from '+ @table + ''

set @sql = @sql+ ' from D20190429'

set @sql = @sql+ ' group by CountryDST'

execute sp_executesql @sql

--- check if table exist

IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[D20190429]') and OBJECTPROPERTY(id, N'IsTable') = 1)

BEGIN

print '1'

END

SELECT CONVERT(bigint, rows)

FROM sysindexes

WHERE id = OBJECT_ID('D20190401')

AND indid < 2

SELECT COUNT(*) FROM D20190401

exec [sp_rpt_country_cust_src_date_0] '2019-4-22 09:30:00'

SELECT * FROM dbo.sysobjects where id = object_id('dbo.[D20190401]') and OBJECTPROPERTY(id, N'IsTable') = 1

select top 1 * from sys.partitions

SELECT A.Name, SUM(B.rows) AS 'RowCount'

FROM sys.objects A

INNER JOIN sys.partitions B ON A.id = B.object_id

WHERE A.id = object_id('dbo.[D20190401]') and OBJECTPROPERTY(A.id, N'IsTable') = 1

SELECT

[Tables].name AS [TableName],

SUM([Partitions].[rows]) AS [TotalRowCount]

FROM sys.tables AS [Tables]

JOIN sys.partitions AS [Partitions]

ON [Tables].[object_id] = [Partitions].[object_id]

AND [Partitions].index_id IN ( 0, 1 )

WHERE [Tables].name = N'D20190401'

group by [Tables].name

SELECT

[Tables].name AS [TableName],

SUM([Partitions].[rows]) AS [TotalRowCount]

FROM sys.tables AS [Tables]

JOIN sys.partitions AS [Partitions]

ON [Tables].[object_id] = [Partitions].[object_id]

AND [Partitions].index_id IN ( 0, 1 )

WHERE [Tables].name = N'D20190423'

group by [Tables].name

exec [sp_rpt_country_cust_src_date_0] '2019-4-23 09:30:00'

新建存储过程 [sp_rpt_check_tableName_and_rowcount] ，用来查 table 是否存在， rowcount 是不是为零

2019-04-24

修改xueliang的存储过程

2019-04-25

早上 , salinda说总收到报错邮件， bridge 1 好像有问题

经查，是因为 collector 得不到 maxis的api 调用返回，

如果单独运行 curl 命令

curl -X POST https://apigateway.maxis.com.my:443/processCommonFworkAPI -H "Content-Type: application/json" -H "apiKey:l7xxad0a5d46597c48ee98bc1fff73520051" -d '{"processCommonFworkAPI":{"cmnFworkInput":{"msisdn":"60176206804","actionType":"1","channelRefId":"abc123456","channel":"WORLDHUB","tranxId":"7"},"eaiHeader":{"timestamp":"20181118110559","to":"ESB","correlationId":null,"appId":"VASHLR","msgId":"7cb82b85-7836-4727-921a-22564ab23e7a","msgType":"Request","from":"WORLDHUB"}}}'

也显示出错，错误信息是： Cannot communicate securely with peer: no common encryption algorithm(s)

这证明了不是collector的问题，而是maxis那边不知做了什么改动

然后， lam 发邮件问 maxis , 下午问题解决了

2019-4-26

写 falconDB 的手册

2019-4-29

lam说 cctb1 有8个csl call service authen 超时，重启各modules后解决，重启方法是：

[waqas@pxy01 modules]$ supervisorctl
bridge.1 RUNNING pid 105124, uptime 4 days, 0:54:07
cap-gateway RUNNING pid 79287, uptime 26 days, 23:19:39
csl.collector.1 RUNNING pid 80631, uptime 26 days, 22:41:55
hkg.csl.1 RUNNING pid 80718, uptime 26 days, 22:38:33
hkg.csl.dcca FATAL Exited too quickly (process log may have details)
maxis.1 RUNNING pid 105136, uptime 4 days, 0:54:02
maxis.collector.1 RUNNING pid 105162, uptime 4 days, 0:53:55
orientdb RUNNING pid 13019, uptime 397 days, 0:00:44
sg.singtel.1 RUNNING pid 105190, uptime 4 days, 0:53:48
sg.singtel.collector.1 RUNNING pid 105215, uptime 4 days, 0:53:42
sg.singtel.verify-service RUNNING pid 12924, uptime 397 days, 0:00:45
supervisor> restart bridge.1
bridge.1: stopped
bridge.1: started
supervisor> restart csl.collector.1
csl.collector.1: stopped
csl.collector.1: started
supervisor> restart hkg.csl.1
hkg.csl.1: stopped
hkg.csl.1: started
supervisor> restart maxis.1
maxis.1: stopped
maxis.1: started
supervisor> restart maxis.collector.1
maxis.collector.1: stopped
maxis.collector.1: started
supervisor> restart sg.singtel.1
sg.singtel.1: stopped
sg.singtel.1: started
supervisor> restart sg.singtel.collector.1
sg.singtel.collector.1: stopped
sg.singtel.collector.1: started
supervisor>
[waqas@pxy01 modules]$

[waqas@pxy01 modules]$ supervisorctl

bridge.1 RUNNING pid 105124, uptime 4 days, 0:54:07

cap-gateway RUNNING pid 79287, uptime 26 days, 23:19:39

csl.collector.1 RUNNING pid 80631, uptime 26 days, 22:41:55

hkg.csl.1 RUNNING pid 80718, uptime 26 days, 22:38:33

hkg.csl.dcca FATAL Exited too quickly (process log may have details)

maxis.1 RUNNING pid 105136, uptime 4 days, 0:54:02

maxis.collector.1 RUNNING pid 105162, uptime 4 days, 0:53:55

orientdb RUNNING pid 13019, uptime 397 days, 0:00:44

sg.singtel.1 RUNNING pid 105190, uptime 4 days, 0:53:48

sg.singtel.collector.1 RUNNING pid 105215, uptime 4 days, 0:53:42

sg.singtel.verify-service RUNNING pid 12924, uptime 397 days, 0:00:45

supervisor> restart bridge.1

bridge.1: stopped

bridge.1: started

supervisor> restart csl.collector.1

csl.collector.1: stopped

csl.collector.1: started

supervisor> restart hkg.csl.1

hkg.csl.1: stopped

hkg.csl.1: started

supervisor> restart maxis.1

maxis.1: stopped

maxis.1: started

supervisor> restart maxis.collector.1

maxis.collector.1: stopped

maxis.collector.1: started

supervisor> restart sg.singtel.1

sg.singtel.1: stopped

sg.singtel.1: started

supervisor> restart sg.singtel.collector.1

sg.singtel.collector.1: stopped

sg.singtel.collector.1: started

supervisor>

[waqas@pxy01 modules]$

2019-4-30

修改后 cctb1 和 cctb2 的区别

cctb1有 pack callflow , cctb2 没有
cctb1 的端口是 12360 ， cctb2 是 12359
以上区别体现在 CSimplecall.cpp

2019-5-5

新的 falcon 位于 103.244.191.252

D盘和E盘是 intel ssd , 数据存在D，日志存 E , 但也有数据库是反过来，数据在 E

如何把 application 变服务
nssm remove serviceroute
nssm install XReport

cd serviverate
nssm install XRate

cd ServerRoute
nssm install XRoute

cd serverconfig
nssm install XConfig

然后在 service 里 start

cd ServerHttp
nssm install XHttp

XHttp 依赖于：
XConfig
XRate
XRoute
XReport

2019-5-8

转移 218的数据库到 209

第一步，先把209 现有的 simbox detection 数据库删除

第二步，恢复 218 的数据备份到 209

第三步 for 209 DB , port = 1445 pwd = Passw0rdPassw0rd ， instance name is

第四步，改json配置文件的数据库连接串，还有 server http 的本地地址，从 10.10.10.61 （218的本地ip）改为 10.10.10.31 （209）

第五步，安装 .net core runtime 2.2 , 64 bits

第六步新建 windows schedule , 执行 python 脚本，备份日志到 d:/LogBackup 的各目录中

第七步，把安装在 D 盘的serverConfig等转移到 ssd 硬盘 E

2019-5-9

lam说 falcon mgt 看到的cdr和 sb&MVTS不一样

检查方法：

先登陆 218 数据库，找出所有包括 2019-5-8 cdr的表

select count(*) from cdr81
select count(*) from cdr80


select count(*) from cdr79
select count(*) from cdr78

select count(*) from cdr77
select count(*) from cdr76

select * from cdr76

select * from cdr70

select * from cdr66

select * from cdr53

select * from cdr54
-----------------------------


select * from cdr62
select * from cdr82


---- 从 cdr62 开始 ，有 5月8号的数据   到 cdr81  为止 

---  以  

select * from  cdr62 where customerid =7 and duration_B > 0    --0
select * from  cdr63 where customerid =7 and duration_B > 0    --39
select * from  cdr64 where customerid =7 and duration_B > 0    --220
select * from  cdr65 where customerid =7 and duration_B > 0    --124
select * from  cdr66 where customerid =7 and duration_B > 0    --246 
select * from  cdr67 where customerid =7 and duration_B > 0    --190
select * from  cdr68 where customerid =7 and duration_B > 0    --251
select * from  cdr69 where customerid =7 and duration_B > 0    --144

select * from  cdr70 where customerid =7 and duration_B > 0   --33
select * from  cdr71 where customerid =7 and duration_B > 0    --298
select * from  cdr72 where customerid =7 and duration_B > 0   --   344


select * from  cdr73 where customerid =7 and duration_B > 0    -- 209 
select * from  cdr74 where customerid =7 and duration_B > 0   --34
select * from  cdr75 where customerid =7 and duration_B > 0    -- 81
select * from  cdr76 where customerid =7 and duration_B > 0   -- 274
select * from  cdr77 where customerid =7 and duration_B > 0   --- 337
select * from  cdr78 where customerid =7 and duration_B > 0   ---43
select * from  cdr79 where customerid =7 and duration_B > 0   --- 19
select * from  cdr80 where customerid =7 and duration_B > 0   -- 55
select * from  cdr81 where customerid =7 and duration_B > 0   -- 0

select count(*) from cdr81

select count(*) from cdr80

select count(*) from cdr79

select count(*) from cdr78

select count(*) from cdr77

select count(*) from cdr76

select * from cdr76

select * from cdr70

select * from cdr66

select * from cdr53

select * from cdr54

-----------------------------

select * from cdr62

select * from cdr82

---- 从 cdr62 开始，有 5月8号的数据到 cdr81 为止

--- 以

select * from cdr62 where customerid =7 and duration_B > 0 --0

select * from cdr63 where customerid =7 and duration_B > 0 --39

select * from cdr64 where customerid =7 and duration_B > 0 --220

select * from cdr65 where customerid =7 and duration_B > 0 --124

select * from cdr66 where customerid =7 and duration_B > 0 --246

select * from cdr67 where customerid =7 and duration_B > 0 --190

select * from cdr68 where customerid =7 and duration_B > 0 --251

select * from cdr69 where customerid =7 and duration_B > 0 --144

select * from cdr70 where customerid =7 and duration_B > 0 --33

select * from cdr71 where customerid =7 and duration_B > 0 --298

select * from cdr72 where customerid =7 and duration_B > 0 -- 344

select * from cdr73 where customerid =7 and duration_B > 0 -- 209

select * from cdr74 where customerid =7 and duration_B > 0 --34

select * from cdr75 where customerid =7 and duration_B > 0 -- 81

select * from cdr76 where customerid =7 and duration_B > 0 -- 274

select * from cdr77 where customerid =7 and duration_B > 0 --- 337

select * from cdr78 where customerid =7 and duration_B > 0 ---43

select * from cdr79 where customerid =7 and duration_B > 0 --- 19

select * from cdr80 where customerid =7 and duration_B > 0 -- 55

select * from cdr81 where customerid =7 and duration_B > 0 -- 0

把 select 出来的结果放到一个 excel文件，和 mvts CDR 转换后的excel 文件做 cdr compare

falcon 是 falconCDR – 副本

MVTS : SBCDRCopy

cdrCompare比较程序设置：

得出的结论是 falcon CDR 比 MVTS 大多数都少一秒，如果2000cdr，则少 2000/60 = 33 分钟

2019-05-13

检查在 24 rateDB 数据库有没有prefix长度超过 20 的rate 数据

declare @sumCount int 
declare @sql nvarchar(4000)  
declare @tablename nvarchar(100) 


DECLARE db_cursor CURSOR FOR 
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='SmartBilling_Rate'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @tablename  

WHILE @@FETCH_STATUS = 0  
BEGIN  
	set @sql = 'select @sd=count(*) from SmartBilling_Rate.dbo.['+@tablename + ']' + ' where len(prefix)>20'

	BEGIN TRY
		execute sp_executesql @sql,N'@sd int output', @sd = @sumCount OUTPUT
	END TRY
	BEGIN CATCH
		print ERROR_MESSAGE()
		print 'skip'
	END CATCH

	if(@sumCount>0) 
	BEGIN
		print @tablename
	END

     FETCH NEXT FROM db_cursor INTO @tablename 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

declare @sumCount int

declare @sql nvarchar(4000)

declare @tablename nvarchar(100)

DECLARE db_cursor CURSOR FOR

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='SmartBilling_Rate'

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

set @sql = 'select @sd=count(*) from SmartBilling_Rate.dbo.['+@tablename + ']' + ' where len(prefix)>20'

BEGIN TRY

execute sp_executesql @sql,N'@sd int output', @sd = @sumCount OUTPUT

END TRY

BEGIN CATCH

print ERROR_MESSAGE()

print 'skip'

END CATCH

if(@sumCount>0)

BEGIN

print @tablename

END

FETCH NEXT FROM db_cursor INTO @tablename

END

CLOSE db_cursor

DEALLOCATE db_cursor

准备做 Falcon cdr download 功能

2019-5-14

还是 cdr compare 的问题， lam说：在 falcon managent 里，查出 2019-5-13：

succussCalls = 489 , 然而，在 smartbilling 和 MVTS 都查出 succuss calls = 490

解决方法：先在 falcon DB的 cdr DB 找出所有 2019-5-13 的 cdr , 包括全部当日cdr,不管是不是 success ,

select * from CDRDB.dbo.cdr83 
select * from CDRDB.dbo.cdr84 
select * from CDRDB.dbo.cdr85 
select * from CDRDB.dbo.cdr86 
select * from CDRDB.dbo.cdr87 
select * from CDRDB.dbo.cdr88 
select * from CDRDB.dbo.cdr89 
select * from CDRDB.dbo.cdr90 
select * from CDRDB.dbo.cdr91 
select * from CDRDB.dbo.cdr92 
select * from CDRDB.dbo.cdr93 
select * from CDRDB.dbo.cdr94

select * from CDRDB.dbo.cdr83

select * from CDRDB.dbo.cdr84

select * from CDRDB.dbo.cdr85

select * from CDRDB.dbo.cdr86

select * from CDRDB.dbo.cdr87

select * from CDRDB.dbo.cdr88

select * from CDRDB.dbo.cdr89

select * from CDRDB.dbo.cdr90

select * from CDRDB.dbo.cdr91

select * from CDRDB.dbo.cdr92

select * from CDRDB.dbo.cdr93

select * from CDRDB.dbo.cdr94

把查出的结果集放到一个excel里，和 smartbilling 的 cdr 转换完的excel 文件比，先以 smartbilling 为准，找出那 490 个 cdr , 比对结果设为文件A

然后，

select * from CDRDB.dbo.cdr94  where duration_A > 0

1	select * from CDRDB.dbo.cdr94 where duration_A > 0

找出 489个 cdr 从 falcon , 设为文件 B

把文件 A和 B对比，找出差的那一条 cdr , 是

select * from CDRDB.dbo.cdr84 where SRCOrg='29745417385' and DSTOrg = '60038801796834984'

1	select * from CDRDB.dbo.cdr84 where SRCOrg='29745417385' and DSTOrg = '60038801796834984'

此 cdr的 session ID = ‘s199-2ccc8b08’

去 logbackup文件夹，打开 info_2019-05-13.log , 找出带有此 sessionID的记录，

Line 2826: 2019-05-13 15:31:36,505 INFO  - /init.aspx?cmd=ocs_new&sid=S199-2ccc8b08&calling=29745417385&called=60038801796834984&trunk_in=7&setup_time=2019-05-13%2015:31:36&a_media_ip=103.244.191.148
	Line 2827: 2019-05-13 15:31:36,547 INFO  - {"SessionID":"s199-2ccc8b08","InboundNAP":"7","SRCOrg":"29745417385","DSTOrg":"60038801796834984","GUIDCustAuthID":"38bf2b33-6e8a-4b94-9f9d-1b5da8a22595","GUIDRouteID":"ea0edb1b-e5f8-404e-b441-d7d99a13af6b","SRCRegular":"29745417385","DSTRegular":"8801796834984","CountrySRC":"ARUBA","CountryDST":"BANGLADESH","CitySRC":"Not Found","CityDST":"BANGLADESH - MOBILE (GRAMEEN)","PGSRC":"ARUBA","PGDST":"BANGLADESH MOBILE","CustID":7,"CustRatePlanID":"123","CustRatePerMin":0.0230000000,"CustChargePrefix":"8801","MinuteLeft":240.0000,"AuthFlag":"SIM02|AI"}
	Line 2829: 2019-05-13 15:31:36,549 INFO  - {"duration":14400,"routetable":[{"nap":"33","src":"29745417385","dst":"70238801796834984"},{"nap":"67","src":"29745417385","dst":"70678801796834984"},{"nap":"39","src":"29745417385","dst":"70348801796834984"},{"nap":"30","src":"29745417385","dst":"70188801796834984"},{"nap":"59","src":"29745417385","dst":"70598801796834984"},{"nap":"63","src":"29745417385","dst":"70638801796834984"},{"nap":"40","src":"29745417385","dst":"70368801796834984"},{"nap":"61","src":"29745417385","dst":"70618801796834984"},{"nap":"32","src":"29745417385","dst":"70228801796834984"},{"nap":"58","src":"29745417385","dst":"70588801796834984"}],"next_update_time":14400,"cmd":"ocs_new","sid":"s199-2ccc8b08","result":0,"msg":null}
	Line 2950: 2019-05-13 15:31:48,014 INFO  - /init.aspx?cmd=ocs_start&sid=S199-2ccc8b08&a_setup_time=2019-05-13%2015:31:36&a_connect_time=2019-05-13%2015:31:48&b_setup_time=2019-05-13%2015:31:36&b_connect_time=2019-05-13%2015:31:48&connected_trunk=34&prov_id=33
	Line 2952: 2019-05-13 15:31:48,799 INFO  - /init.aspx?cmd=ocs_end&sid=S199-2ccc8b08&duration_a=0&duration_b=0&disconnect_reason_a=200&disconnect_reason_b=200&a_hangup_time=2019-05-13%2015:31:48&b_hangup_time=2019-05-13%2015:31:48&final_release=1&prov_id=33&trunk_out=34

Line 2826: 2019-05-13 15:31:36,505 INFO - /init.aspx?cmd=ocs_new&sid=S199-2ccc8b08&calling=29745417385&called=60038801796834984&trunk_in=7&setup_time=2019-05-13%2015:31:36&a_media_ip=103.244.191.148

Line 2827: 2019-05-13 15:31:36,547 INFO - {"SessionID":"s199-2ccc8b08","InboundNAP":"7","SRCOrg":"29745417385","DSTOrg":"60038801796834984","GUIDCustAuthID":"38bf2b33-6e8a-4b94-9f9d-1b5da8a22595","GUIDRouteID":"ea0edb1b-e5f8-404e-b441-d7d99a13af6b","SRCRegular":"29745417385","DSTRegular":"8801796834984","CountrySRC":"ARUBA","CountryDST":"BANGLADESH","CitySRC":"Not Found","CityDST":"BANGLADESH - MOBILE (GRAMEEN)","PGSRC":"ARUBA","PGDST":"BANGLADESH MOBILE","CustID":7,"CustRatePlanID":"123","CustRatePerMin":0.0230000000,"CustChargePrefix":"8801","MinuteLeft":240.0000,"AuthFlag":"SIM02|AI"}

Line 2829: 2019-05-13 15:31:36,549 INFO - {"duration":14400,"routetable":[{"nap":"33","src":"29745417385","dst":"70238801796834984"},{"nap":"67","src":"29745417385","dst":"70678801796834984"},{"nap":"39","src":"29745417385","dst":"70348801796834984"},{"nap":"30","src":"29745417385","dst":"70188801796834984"},{"nap":"59","src":"29745417385","dst":"70598801796834984"},{"nap":"63","src":"29745417385","dst":"70638801796834984"},{"nap":"40","src":"29745417385","dst":"70368801796834984"},{"nap":"61","src":"29745417385","dst":"70618801796834984"},{"nap":"32","src":"29745417385","dst":"70228801796834984"},{"nap":"58","src":"29745417385","dst":"70588801796834984"}],"next_update_time":14400,"cmd":"ocs_new","sid":"s199-2ccc8b08","result":0,"msg":null}

Line 2950: 2019-05-13 15:31:48,014 INFO - /init.aspx?cmd=ocs_start&sid=S199-2ccc8b08&a_setup_time=2019-05-13%2015:31:36&a_connect_time=2019-05-13%2015:31:48&b_setup_time=2019-05-13%2015:31:36&b_connect_time=2019-05-13%2015:31:48&connected_trunk=34&prov_id=33

Line 2952: 2019-05-13 15:31:48,799 INFO - /init.aspx?cmd=ocs_end&sid=S199-2ccc8b08&duration_a=0&duration_b=0&disconnect_reason_a=200&disconnect_reason_b=200&a_hangup_time=2019-05-13%2015:31:48&b_hangup_time=2019-05-13%2015:31:48&final_release=1&prov_id=33&trunk_out=34

注意到最后一条记录 ocs_end ， duration_a=0&duration_b=0 ，因为 falcon 的cdr都是 vms send http request 告诉的，这就证明了duration =0 并不是falcon算错，而是 vms 送错

注：对于不是今天的查询，存储过程找的是

select * from D20190513 order by succCalls desc

select sum(succCalls) from D20190513  where carrierID_IN=7

select * from D20190513 order by succCalls desc

select sum(succCalls) from D20190513 where carrierID_IN=7

carrierID 7 is maxis2

2019-05-15

怎么在 falcon 数据库找出某一天或者几天的cdr 数据？

与 smartbilling cdr download 大概一样：

首先在： falcon DB 的 report DB 找出：

select * from CDR where CDRDate = ‘2019-05-13’ ，找出的就是所有含有 5月13号cdr的table

在 report DB 执行此sql

declare @tablename nvarchar(100) 
declare @sql nvarchar(4000)  

set @sql = ''

DECLARE db_cursor CURSOR FOR 
SELECT CDRID 
FROM CDR where CDRDate = '2019-05-13'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @tablename  

WHILE @@FETCH_STATUS = 0  
BEGIN  
	--print @tablename
	if(@sql='')
	BEGIN
		set @sql = 'select sessionID,SRCRegular,DSTRegular,CustomerID,SupplierID,setuptime_A,connectTime_A,setupTime_B,connectTime_B,DisconnectTime_A,DisconnectTime_B,Duration_A,Duration_B from CDRDB.dbo.[cdr'+@tablename + ']  ' 
	END
	ELSE
	BEGIN
		set @sql = @sql + ' union select sessionID,SRCRegular,DSTRegular,CustomerID,SupplierID,setuptime_A,connectTime_A,setupTime_B,connectTime_B,DisconnectTime_A,DisconnectTime_B,Duration_A,Duration_B from CDRDB.dbo.[cdr'+@tablename + ']  ' 

	END

    FETCH NEXT FROM db_cursor INTO @tablename 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor


print @sql 

execute sp_executesql @sql

declare @tablename nvarchar(100)

declare @sql nvarchar(4000)

set @sql = ''

DECLARE db_cursor CURSOR FOR

SELECT CDRID

FROM CDR where CDRDate = '2019-05-13'

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

--print @tablename

if(@sql='')

BEGIN

set @sql = 'select sessionID,SRCRegular,DSTRegular,CustomerID,SupplierID,setuptime_A,connectTime_A,setupTime_B,connectTime_B,DisconnectTime_A,DisconnectTime_B,Duration_A,Duration_B from CDRDB.dbo.[cdr'+@tablename + '] '

END

ELSE

BEGIN

set @sql = @sql + ' union select sessionID,SRCRegular,DSTRegular,CustomerID,SupplierID,setuptime_A,connectTime_A,setupTime_B,connectTime_B,DisconnectTime_A,DisconnectTime_B,Duration_A,Duration_B from CDRDB.dbo.[cdr'+@tablename + '] '

END

FETCH NEXT FROM db_cursor INTO @tablename

END

CLOSE db_cursor

DEALLOCATE db_cursor

print @sql

execute sp_executesql @sql

即可找出当天的全部cdr , 将查询结果存 csv

然后将 smartbilling 的 txt cdr 用 excel 打开，以逗号分隔，

将转换完的2个文件用cdr compare 程序比较，记住这2个文件第一行要空着因为不读

比较方法为

这样就可以知道有多少 cdr match

附注； excel 如何把某列为空的行全删除

Select Column C
Press F5, then Special
Check Blanks, then OK (see this step in the pic at bottom)
Delete the rows that are now selected (e.g. right click in selection > Delete cells… > Entire row or via the ribbon (see second screenshot))

this answer come from https://superuser.com/questions/474530/excel-delete-row-if-cell-in-certain-column-is-blank

2019-5-16

继续做 cdr download 的界面，在 falcon mgnt

查询cdr的存储过程参数： date ‘2019-5-13’ customer : supplier :

新增存储过程 sp_tools_downloadCDR on 218 ‘s ReportDB

测试用sql：

exec [sp_tools_downloadCDR] '2019-5-15 00:00:00','2019-5-15 00:00:00',1,1


exec [sp_tools_downloadCDR] '2019-5-15 00:00:00','2019-5-15 00:00:00',7,940

declare @dt as date
set @dt = '2019-5-1 00:00:00'


--SELECT DATE_FORMAT(@dt, '%d %m %Y') AS your_date;
select CAST(@dt as varchar)


	SELECT * 
	FROM CDR where CDRDate >= '2019-5-1' and CDRDate <= '2019-5-15'

	SELECT top 5 *
	FROM CDR where CDRDate = '2019-5-1'

exec [sp_tools_downloadCDR] '2019-5-15 00:00:00','2019-5-15 00:00:00',1,1

exec [sp_tools_downloadCDR] '2019-5-15 00:00:00','2019-5-15 00:00:00',7,940

declare @dt as date

set @dt = '2019-5-1 00:00:00'

--SELECT DATE_FORMAT(@dt, '%d %m %Y') AS your_date;

select CAST(@dt as varchar)

SELECT *

FROM CDR where CDRDate >= '2019-5-1' and CDRDate <= '2019-5-15'

SELECT top 5 *

FROM CDR where CDRDate = '2019-5-1'

2015-05-20

改马来任务 575 ：

update timeScheduler set nextRunTime = '2019-5-20 19:00:00.000'
where id = 575

1 2	update timeScheduler set nextRunTime = '2019-5-20 19:00:00.000' where id = 575

上周完成的 falcon cdrDownLoad , 等待 lam 确认

继续修改 cdrDownload :

1 首字母大写 done
2 排列整齐 done
3 sipplier 和 custome 默认应该是 select all ,在 select all 状态下，下拉条不可选 done

4 列选择加上 src number , dst number

5 20000应该保留， 20000行换一个文件

6 有进度条

7 : 左下 list 显示 cdr table list done

8 :

在 103.244.191.218,1455 机器上新建存储过程

sp_tools_GetCDRList 和

sp_tools_downloadCDR_V2

测试用存储sql

exec [sp_tools_downloadCDR] '2019-5-15 00:00:00','2019-5-15 00:00:00',1,1


exec [sp_tools_downloadCDR] '2019-5-15 00:00:00','2019-5-15 00:00:00',7,940


exec [sp_tools_GetCDRList] '2019-5-1 00:00:00','2019-5-15 00:00:00','7','214'

exec [sp_tools_GetCDRList] '2019-5-1 00:00:00','2019-5-15 00:00:00','all','214'

exec [sp_tools_GetCDRList] '2019-5-1 00:00:00','2019-5-15 00:00:00','all','all'



select * from cdr6

select * from CDRDB.dbo.[cdr6]


select SessionID,InboundNAP,OutboundNAP,SRCRegular,DSTRegular,CountrySRC,CountryDST,Duration_A,Duration_B from CDRDB.dbo.[cdr6]



declare @dt as date
set @dt = '2019-5-1 00:00:00'


--SELECT DATE_FORMAT(@dt, '%d %m %Y') AS your_date;
select CAST(@dt as varchar)


	SELECT * 
	FROM CDR where CDRDate >= '2019-5-1' and CDRDate <= '2019-5-15'


	SELECT * 
	FROM CDR where CDRDate >= '2019-5-1' and CDRDate <= '2019-5-15'
	and CustomerID=7 and SupplierID=214



	SELECT top 5 *
	FROM CDR where CDRDate = '2019-5-1'


DECLARE @SQLStr varchar(100)
SELECT @SQLStr = 'Mickey Mouse, Goofy, Donald Duck, Pluto, Minnie Mouse'

SELECT
    element
FROM
    dbo.func_split(@SQLStr, ',')



exec [sp_tools_downloadCDR_V2] '95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114','InboundNAP,OutboundNAP,SRCRegular,DSTRegular,Duration_A,Duration_B,SessionID,CountrySRC,CountryDST',''

exec [sp_tools_downloadCDR_V2] '95','InboundNAP,OutboundNAP,SRCRegular,DSTRegular,Duration_A,Duration_B,SessionID,CountrySRC,CountryDST','and SRCRegular like ''%95355700764%'''


95355700764     8801913426977








declare @Tables nvarchar(4000)
declare @tablename nvarchar(100) 
set @Tables = '6,7,8,9'


DECLARE db_cursor CURSOR FOR 
SELECT element FROM dbo.func_split(@Tables, ',')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @tablename  


WHILE @@FETCH_STATUS = 0  
BEGIN  

	print @tablename
	FETCH NEXT FROM db_cursor INTO @tablename 

END

CLOSE db_cursor  
DEALLOCATE db_cursor

exec [sp_tools_downloadCDR] '2019-5-15 00:00:00','2019-5-15 00:00:00',1,1

exec [sp_tools_downloadCDR] '2019-5-15 00:00:00','2019-5-15 00:00:00',7,940

exec [sp_tools_GetCDRList] '2019-5-1 00:00:00','2019-5-15 00:00:00','7','214'

exec [sp_tools_GetCDRList] '2019-5-1 00:00:00','2019-5-15 00:00:00','all','214'

exec [sp_tools_GetCDRList] '2019-5-1 00:00:00','2019-5-15 00:00:00','all','all'

select * from cdr6

select * from CDRDB.dbo.[cdr6]

select SessionID,InboundNAP,OutboundNAP,SRCRegular,DSTRegular,CountrySRC,CountryDST,Duration_A,Duration_B from CDRDB.dbo.[cdr6]

declare @dt as date

set @dt = '2019-5-1 00:00:00'

--SELECT DATE_FORMAT(@dt, '%d %m %Y') AS your_date;

select CAST(@dt as varchar)

SELECT *

FROM CDR where CDRDate >= '2019-5-1' and CDRDate <= '2019-5-15'

SELECT *

FROM CDR where CDRDate >= '2019-5-1' and CDRDate <= '2019-5-15'

and CustomerID=7 and SupplierID=214

SELECT top 5 *

FROM CDR where CDRDate = '2019-5-1'

DECLARE @SQLStr varchar(100)

SELECT @SQLStr = 'Mickey Mouse, Goofy, Donald Duck, Pluto, Minnie Mouse'

SELECT

element

FROM

dbo.func_split(@SQLStr, ',')

exec [sp_tools_downloadCDR_V2] '95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114','InboundNAP,OutboundNAP,SRCRegular,DSTRegular,Duration_A,Duration_B,SessionID,CountrySRC,CountryDST',''

exec [sp_tools_downloadCDR_V2] '95','InboundNAP,OutboundNAP,SRCRegular,DSTRegular,Duration_A,Duration_B,SessionID,CountrySRC,CountryDST','and SRCRegular like ''%95355700764%'''

95355700764 8801913426977

declare @Tables nvarchar(4000)

declare @tablename nvarchar(100)

set @Tables = '6,7,8,9'

DECLARE db_cursor CURSOR FOR

SELECT element FROM dbo.func_split(@Tables, ',')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

print @tablename

FETCH NEXT FROM db_cursor INTO @tablename

END

CLOSE db_cursor

DEALLOCATE db_cursor

2019-05-23

修改 cdr download , 加上 6个 time

2019-05-27

lam 说 5.21 falcon 和 VMS 的比较仍有2个success call not match

解决方法：无，这是 vms 和 mera 的误差问题

sheypei 说： cdr compare 程序有一点问题

解决方法：把其中一个文件的首行删除留空白即可

2019-06-04

2019-06-10

falcon—->ZWebAPI 的CRUD

完成 for :

carrier

equipment

equipmentgroup

equipmentgroup detail

— 6.11

route

routedetail

rateplan

2019-6-12

继续找 django 模板

github 的 https://github.com/guohongze/adminset

2019-06-13

做一个 web app , 完成 CRUD 功能，调用 rest api

可选方案有 angularjs : https://www.baeldung.com/angularjs-crud-with-spring-data-rest

*****************************

VUE

https://grokonez.com/frontend/vue-js/spring-boot-vue-js-example-spring-data-rest-mongodb-crud-example

Category: work

在iis发布net core web api 方法

2017-7-22 从 218 到 209 转移数据库及相关服务等

everyday work