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 的
|
1 2 3 4 5 6 7 |
#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的配置页面分别是：
|
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
|
1 |
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
加
|
1 2 3 |
int cardType =999; cardType = responseJson.getJSONObject("profile").getInt("prepaidCardType"); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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
3
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] 存储过程
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
建表 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' |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
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
|
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 的数据库上执行
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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 注释掉这句
|
1 2 3 4 5 6 |
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
修改完需要
- 从 193 往 194 传文件 ：
|
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 命令 ：
|
1 |
scp -P 9900 -r /var/modules/java-sctp waqas@103.244.191.194:/var/modules |
diameter 怎么和 pccw 连接
cctb2 的 config 文件是 ：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 时 ， 显示 ：
|
1 |
Disconnecting due to no CER/CEA |
|
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 , 可能是因为今天的表还没生成
改完以后的存储过程 ：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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] 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 |
测试用的 sql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 命令
|
1 |
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后解决 ， 重启方法是 ：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
[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的表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
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 数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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 ,
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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
然后 ，
|
1 |
select * from CDRDB.dbo.cdr94 where duration_A > 0 |
找出 489个 cdr 从 falcon , 设为 文件 B
把 文件 A和 B对比， 找出差的那一条 cdr , 是
|
1 |
select * from CDRDB.dbo.cdr84 where SRCOrg='29745417385' and DSTOrg = '60038801796834984' |
此 cdr的 session ID = ‘s199-2ccc8b08’
去 logbackup文件夹， 打开 info_2019-05-13.log , 找出带有此 sessionID的记录 ，
|
1 2 3 4 5 |
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 送错
注： 对于不是今天的查询， 存储过程找的是
|
1 2 3 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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, thenSpecial - Check
Blanks, thenOK(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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 ：
|
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
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
buy sildenafil pills
can you buy tetracycline online
rx for fever blisters
cephalexin 500 mg.
who invented viagra
prozac how supplied
propecia online usa
zestril generic name
100 mg doxycycline for syphillis
lexapro plus mirtazapine
herb viagra green box pills
buy antabuse online canada
tramadol with prozac
will the va give a prescription for viagra
disgrasil 120 mg
lisinopril hctz 10 12.5 mg and “echinacea”
ventolin hfa (albuterol)
how to buy viagra in san antonio
how long after using qvar can you take albuterol inhaler
imagen de la pastilla lisinopril
levitra 25 mg
cialis name canda
can i leave cephalexin in a hot car for a weekend
generic cialis tadalafil c20
food to avoid whille prednisone 20 mg
buy vardenafil orodispersible
can i buy antabuse over the counter
levothyroxine.05 mg
cheap tadalafil no prescription
nizagara dapoxetine sildenafil reviews
can you take dicofenac with lexapro
discount coupons for 20 mg cialis
cialis price at cvs
[url=http://metformind6j.com]how much does metformin decrease a1c [/url]
[url=http://xenical1s1.com]xenical sale [/url]
[url=http://kamagra1s1.com]kamagra jelly [/url]
[url=http://tadalafilcialiso0.com]tadalafil 20mg generic [/url]
[url=http://tetracycline1s1.com]good composite for tetracycline [/url]
perscription generic company tadalafil 5 mg victor, ny
getting a prescription for cialis
tetracycline for dogs
tadalafil 60 mg reviews
tadalafil 5mg generic
what pharmaceutical companies make albuterol sulfate ampules
bexatrol inhaler & ventolin in haler
cafergot tablets buy online
sildenafil citrate & tadalafil tablets
is it dangerous to take metformin if youre not diabetic
lexapro hydroxycloroquine
get discounts on propecia purchase
where can i get antabuse
where can i order albuterol inhalation solution
baclofen chart
metformin 1000 mg twice a day for pcos
side effect of prozac 20 mg
buy kamagra in uk next day delivery
levitra 10 mg non funziona
valtrex generic india
xenical weight loss
amaryl and januvia and metformin
dosage sildenafil
what dosage does lisinopril come in
meet mychelle kamagra
tadalafil 20 mg
tadalafil couponing
kamagra ed pills
prednisolone 15 mg for babies
cafergot discontinued
tadalafil online without prescription
difference between retin a gel and cream
can i give my horse prednisone for founder
how much everclear needed to reduce 5mg of propecia to 1 mg dose
buy cialis online canada pharmacy
ic doxycycline hyclate 100 mg cap
fully erect penis with viagra
tadalafil peptide
prednisone 12 days taper
albuterol inhaler cheap
ventolin vs proventil
xenical bad reaction
prednisone side effects percentages of alopecia
viagra erection
lisinopril 20 mg-hydrochlorothiazide 12.5 mg tablet
generic ed drugs in us
withdrawal from decreasing lexapro 10 to 5 mg
baclofen 10mg tablets get you high
cafergot 20cpr 1mg 100 mg
2000 mg metformin 4mg glimepiride
ventolin inhaler priming
how much does prozac sale for on the street
inhaler without prescription
zithromax reactions
teva-tadalafil 5mg
walmart $4 generic prednisone
levitra efficacy versus time
is 80 mg of lasix safe
best quality generic viagra canada price
cafergot 100mg 75mg cafergot price in pakistan cafergot 2mg 25i-nbome
coupon ventolin hfa
lisinopril h149
viagra potency
is a1c of 51 too low? taking 2000mg of metfomin a day, 30 mg of insulin and 2000 mg of metformin
order zithromax
is p. vulgaris resistant to tetracycline
prednisolone acetate buy online
take levitra together with cialis
coupon for prednisolone 1%
ashley judd on prednisone
what happins if i take 2 5mg cialis at one time
tadalafil c
levitra on a full stomach
cialis tadalafil 20 mg
tadalafil abz 20 mg 48 stuck
rxcanada cialis online
does tadalafil work as well as cialis
the price of propecia
allopurinol delayed reaction rash
[url=http://viagra1s1.com]viagra aspirin combination [/url]
does valtrex raise blood sugar levels
levitra wiki
venta de lasix 20 mg
metformin associated b12 deficiency
viagra cost at cvs
real cialis online with paypal
taking prozac and stays in room al day
ineffective generic lexapro
prednisolone acetate for a skin rash?
tadalafil sublingual tablets 20mg
generic for levitra
is sildenafil available over the counter
tetracycline hydrochloride 250 mg capsule
propecia price usa
tadalafil 20mg lowest price india
retail price of viagra in mexico
intravenous acyclovir medical our pharmacy benefit
para que sirven las pastillas edegra sildenafil
can you take allopurinol and aspirin together
adrena glands after prednisone withdrawal symptoms
tetracycline and famotidine
how does xenical block fat digestion
tetracycline tooth discoloration in adults
ventolin hfa 90 mcg/inh
tadalafil 60 mg generic india
is tetracycline over the counter
tadalis sx 20 tadalafil reviews
200 mg dose of viagra to much?
dapoxetine and sildenafil tablets manufactured in india
is kamagra legal in amsterdam
xenical 120 mg and walmart
side effects of zithromax
does albuterol mix with benadryl
taking lisinopril with amlodipine besylate
how long is sildenafil effective
ventolin inhaler without priscription
how is a renal scan with lasix performed
ventolin nebulizer
kamagra oral jelly india
what is the difference between lisinopril tab 10mg and lisinopril 10 mg tab solc
how to stop taking acyclovir
tetracycline for pigeons
cheap xenical uk
prozac for dog aggression
monthly cost of valtrex
vardenafil 20 mg review
best price for sildenafil 100mg
100 mg sildenafil
tadalafil side effects
orlistat (xenical)
prednisolone 15 mg/5
lip cold sore acyclovir
what is the average prescription size of for amoxicillin clavulanate 875 125
sildenafil 50 mg 24
tetracycline and famotidine
how long between doxycycline and amoxicillin
ventolin inhaler use
provider free samples of cialis
30 mg 40 mg prozac
professional sildenafil citrate
is norvasc 10 mg and lisinopril 40 mg
acyclovir prophylaxis
weight loss pounds lost with prescription xenical
sildenafil citrate cost at walgreens
xenical and diflusinal interactions
lexapro pee alot
street value levitra
how long yo use prednisolone
tadalafil 20mg tablets
40 mg lisinopril destroyed kidneys
pharmacy global rx generic viagra from india safe
doxycycline hyclate 100 mg treat
recall on ventolin hfa
sildenafil prices local pharmacies
ventolin hfa buy
levothyroxine synthroid t3 or t4
sildenafil cvs
tetracycline 500 mg online
what generic products are made that contain propecia
metformin er how often to take
kamagra viagra
what is lisinopril?
prednisolone 20 mg urticaire
is there a generic cialis pill
buy cafergot uk
valtrex discount price
where to buy viagra cheapin jax fl
tizanidine vs baclofen opioid withdrawal
zithromax mg
when will lexapro become generic
non prescription treatments for depression
what is valtrex medication for
tadalafil 5mg generic on-line
generic albuterol inhaler youtube
escitalopram 10 mg espanol
real viagra 100mg
gsk ventolin coupon
lowest price kamagra 100mg chewable
cheap (tadalafil
does cialis come in a generic form
how to make levitra
is it safe to buy kamagra online
over the counter zithromax
generic albuterol inhaler cost
effectiveness of viagra vs cialis vs levitra
lencare pharmacy
purchase levitra in usa
us pharmacy no prior prescription
a patient is started on albuterol (proventil). what reaction should the patient be instructed on?
allopurinol and kidney failure
amoxicillin for dogs dosage chart
buy ventolin
how much amoxicillin for cat per weight
retina0i0.com
business
business headquarters
company contacts
business headquarters
business
website
corporate website
company news
business
more about company
business
business headquarters
viagrad6j.com
corporate website
more about company
company news
business
corporate website
company news
tetracyclined7k.com
company news
corporate website
business
company news
company news
business
prednisolonef5h.com
business headquarters
business
business
cialisd7k.com
lisinoprilf5h.com
corporate website
website
more about company
website
business
website
website
more about company
antabuse0i0.com
website
business headquarters
more about company
company contacts
wellbutrin0i0.com
more about company
company news
generic levitra online pharmacy
[url=http://onlineviagra.us.com]http://onlineviagra.us.com[/url]
[url=http://tadalafil02.us.com]are there any generic erectile dysfunction drugs [/url]
[url=http://amoxicillin2019.com]purchase amoxicillin no prescription [/url]
[url=http://keflexcephalexin.com]cephalexin 500 mg 219 stomach pain [/url]
[url=http://buyventolin.us.org]how many doses in one ventolin inhaler [/url]
[url=http://propecia.us.com]www.propecia.us.com[/url]
[url=http://tadacip.us.org]tadacip.us.org[/url]
[url=http://sildenafilcitrate247.us.org]http://www.sildenafilcitrate247.us.org[/url]
generic levitra online
I truly enjoy looking through on this web site , it holds superb content .
buy levitra
levitra over the counter canada
sildenafil 20 mg
http://phenergan.team
http://www.artane.network
http://vardenafil.us.org
http://zanaflex.team
http://www.misoprostol.store
http://www.rocaltrol.team
http://methotrexate.team
http://www.buytoprolxl.team
http://www.clozaril.network
levitra generic
http://www.advairdiskus.us.org
http://www.robaxin.team
buy viagra over the counter nz
http://robaxin.team
http://www.amitriptylinecompareprices.com
http://www.buynexium.us.org
http://www.neurontin.golf
http://www.acyclovir365.us.com
http://www.prozac2020.com
http://buysildenafil.us.org
http://www.acyclovir365.us.com
http://fluoxetine.us.com
http://www.zanaflex.team
http://www.cardura.network
http://tretinoin.us.com
Love is love , like that song !! https://japook.com
prednisone 20mg
http://www.tadalis.us.org
http://levitra911i.us.com
http://www.vardenafil2020.com
http://www.vardenafil.run
http://www.zetia.us.org
http://erythromycin.company
http://www.orlistat.network
[url=http://torsemide.team]www.torsemide.team[/url]
[url=http://synthroid247.us.org]http://www.synthroid247.us.org[/url]
[url=http://crestor.network]http://crestor.network[/url]
[url=http://tadacip.us.com]http://tadacip.us.com[/url]
[url=http://buymobic.us.com]http://buymobic.us.com[/url]
[url=http://albuterolcheapestoffers.com]http://albuterolcheapestoffers.com[/url]
http://www.biaxin.company
ventolin coupon
http://www.keflexcephalexin.com
diflucan over the counter
http://www.sildenafil2020.com
generic levitra 20mg pills
http://elocon.team
http://www.yasmin.us.org
http://suhagra.us.com
http://www.zetia.us.org
viagra prescription singapore
http://www.brochins.com
http://www.antabuse02.us.com
http://www.buytamoxifen.us.com
http://www.seroquel.us.com
cialis 5mg cost
http://www.prednisolone.us.com
diflucan 150 mg coupon
http://cipro.joburg
http://www.buyclomid.us.org
http://www.brochins.com
http://www.erythromycin.company
http://www.vpxl.network
levitra tablet online india
http://www.1stbaclofen.com
synthroid generic price
http://www.methotrexate.team
http://www.buydoxycycline.us.com
http://www.tadacip.us.com
sildalis india
http://orderdiflucan.us.com
http://flagyl.golf
http://www.1stcolchicinenow.com
http://www.1stalbendazole.com
http://www.amantadine.network
http://benicar.network
http://www.tofranil.team
http://clindamycin.team
http://www.cafergot0i0.com
http://suhagra.us.com
http://albuterolcheapestoffers.com
[url=http://motilium.us.com]www.motilium.us.com[/url]
[url=http://buyvermox.us.org]www.buyvermox.us.org[/url]
[url=http://clonidine.us.com]http://www.clonidine.us.com[/url]
http://www.cardura.network
http://www.keflexcephalexin.com
generic levitra online usa
sildenafil
http://levitra911i.us.com
http://www.zoloft.wtf
http://tadalafil-abc.com
http://toradol.us.com
essay writing service
http://furosemide-abc.com
http://www.erythromycin.us.com
http://www.vpxl.network
write my essay for me
http://www.buyprednisone.us.com
http://yasmin.us.org
http://www.overthecounterviagra.science
cialis pills sale canada
http://www.buyprednisone.us.com
amoxicillin
http://www.tricor.network
http://www.overthecounterviagra.science
http://www.benicar.network
levitra
sildenafil 200mg price
http://www.orlistat.network
metoprolol recall 2018 [url=http://metoprololrt.com/]metoprolol davis pdf[/url] how does metoprolol work metoprolol metoprolol succ er
http://www.buyprednisone.us.com
generic cialis canada online pharmacy
http://www.levitra911i.us.com
http://www.albuterolcheapestoffers.com
cialis 2.5 canada
http://www.vardenafil.us.org
essays 123
http://buyvermox.us.org
http://www.overthecounterviagra.science
http://www.anafranil.us.com
write an analysis essay
http://glucotrol.team
brand levitra for sale
http://www.crestor.us.com
http://seroquel.us.com
http://www.atarax.us.com
http://acyclovir365.us.com
http://www.buybupropion.us.org
http://www.zofran.us.com
http://www.glucophage.run
http://www.prednisolone.us.com
generic cialis 2019
http://www.zithromax2020.com
http://www.rocaltrol.team
write my essay for me
http://www.amoxil.us.org
write a college essay
buy robaxin without prescription with visa
http://cipro.joburg
http://www.cafergot0i0.com
essay writing service
http://www.zoloft.wtf
generic levitra tablets
http://www.zoloft.wtf
is there a website that writes essays for you
http://metformin.us.com
http://phenergan.team
http://www.lotrisonebest.us.org
1O8y6I Nice post, thanks. Could you explain the third paragraph in more detail?
9MpKPP pretty handy stuff, overall I imagine this is worth a bookmark, thanks
http://buycialisonline.us.com
http://erythromycin.us.com
proscar cost nz
buy clomid pills
http://www.toradol.us.com
http://www.metformin.us.com
http://estrace.team
prednisone 5443
http://keflexcephalexin.com
http://azithromycinonline.us.com
dapoxetine 30 mg tablet
http://tadalafil-abc.com
http://www.viagrasoft.us.com
http://www.cefixime.team
dapoxetine 1mg
http://vardenafil0i0.com
http://www.1stbaclofen.com
buy levitra
http://retinanorxprice.com
synthroid
generic tadalafil 20mg
http://www.1stcolchicinenow.com
online cash advance
http://www.advairdiskus.us.org
1000 loan
advance loans
http://www.clozaril.network
1000 personal loan
advance loans
http://www.hydrochlorothiazide.guru