# 发短信接口日志说明

## 日志存储数据库位置

- ip：192.168.70.136

- 端口：9999

- 数据库：ts_acl

- 模式：sqmdb_rpt

  

## 接口调用日志表（sms_record）

<table border="1">
    <tr>
        <th>字段</a>
        <th>说明</a>
    </tr>
    <tr>
        <td>id</td>
        <td>id</td>
    </tr>
    <tr>
        <td>from_system</td>
        <td>来源系统，按照约定传固定值 如 tousuliucheng</td>
    </tr>
    <tr>
        <td>phone_numbers</td>
        <td>手机号列表</td>
    </tr>
    <tr>
        <td>content</td>
        <td>短信内容文本</td>
    </tr>
    <tr>
        <td>schedule_time</td>
        <td>定时发送,传空字符串或者省略表示即时发送</td>
    </tr>
    <tr>
        <td>from_system</td>
        <td>来源系统，按照约定传固定值 如 tousuliucheng</td>
    </tr>
    <tr>
        <td>sms_type</td>
        <td>各系统自行定义的消息类型，仅用于记录</td>
    </tr>
    <tr>
        <td>internal_id</td>
        <td>系统内编号,可以考虑群发使用相同编号</td>
    </tr>
    <tr>
        <td>result</td>
        <td>提交短信网关响应结果</td>
    </tr>
    <tr>
        <td>send_time</td>
        <td>发送时间</td>
    </tr>
    <tr>
        <td>status</td>
        <td>是否发送到网关</td>
    </tr>
    <tr>
        <td>login_names</td>
        <td>登录用户名</td>
    </tr>
</table>


## 短信报告日志表（sms_report）

<table border="1">
    <tr>
        <th>字段</a>
        <th>说明</a>
    </tr>
    <tr>
        <td>id</td>
        <td>id</td>
    </tr>
    <tr>
        <td>phone</td>
        <td>手机号</td>
    </tr>
    <tr>
        <td>flow_id</td>
        <td>该命令所涉及的Submit或deliver命令的序列号</td>
    </tr>
    <tr>
        <td>send_time</td>
        <td>发送时间</td>
    </tr>
    <tr>
        <td>report_time</td>
        <td>收到报告时间</td>
    </tr>
    <tr>
        <td>report_type</td>
        <td>Report命令类型
0：对先前一条Submit命令的状态报告
1：对先前一条前转Deliver命令的状态报告</td>
    </tr>
    <tr>
        <td>state</td>
        <td>该命令所涉及的短消息的当前执行状态
0：发送成功
1：等待发送
2：发送失败</td>
    </tr>
    <tr>
        <td>error_code</td>
        <td>当State=2时为错误码值，否则为0</td>
    </tr>
</table>



## 使用示例

### 根据手机号查询

```sql
select
    *
from
    sqmdb_rpt.sms_record a
join sqmdb_rpt.sms_report b on
    a.id = b.id
where
    b.phone = '13231899751'
order by
    a.send_time desc,
    b.phone,
    b.flow_id
```



### 根据短信内容模糊查询

```sql
select
    *
from
    sqmdb_rpt.sms_record a
join sqmdb_rpt.sms_report b on
    a.id = b.id
where
    a."content" like '%测试%'
order by
    a.send_time desc,
    b.phone,
    b.flow_id
```



### 根据发送日期查询

```sql
select
    *
from
    sqmdb_rpt.sms_record a
join sqmdb_rpt.sms_report b on
    a.id = b.id
where
    a.send_time >= to_timestamp('2024-05-16', 'YYYY-MM-DD')
    and a.send_time <= to_timestamp('2024-05-17', 'YYYY-MM-DD')
order by
    a.send_time desc,
    b.phone,
    b.flow_id
```



### 根据发送时间段查询

```sql
select
    *
from
    sqmdb_rpt.sms_record a
join sqmdb_rpt.sms_report b on
    a.id = b.id
where
    a.send_time >= '2024-05-16 16:00:00'
    and a.send_time <= '2024-05-16 17:00:00'
order by
    a.send_time desc,
    b.phone,
    b.flow_id
```