第一步
备份 218 localhost, 1455 的13个数据库 ， DW开头的不用， ReportServer开头的也不用
并还原到 209 的 smartrouter instance
第二步
把 原先 218 ： sql server agent 的2个 job 也还原到 209
Create Report & Date Transfer
MaintenanceBackupPlan.Subplan_1
everyday work
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
修改完需要
重新加载程序
supervisorctl reload
更新配置
supervisorctl update
- 从 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