消息中心 - 数据抽取 - 《数据分析》

1、表结构1
主表：scWageDetailInfo

public class TempWageDetailInfo implements Serializable { /
id
/
@Id
private String id;
/
方案id
/
private String wageSheetId;
/
员工id
/
private String workerSid;
/
客户名
/
private String custName;

/**<br />     * 证件号<br />     */<br />    private String idCardNo;
/**<br />     * 银行卡<br />     */<br />    private String bankCard;<br />    /**<br />     * 开卡行代码<br />     */<br />    private String bankCode;<br />    /**<br />     * 开卡银行<br />     */<br />    private String bankName;
/**<br />     * 开卡联行号    ？<br />     *  ----- >  给空  空字符串<br />     */<br />    private String bankBranch;
/**<br />     * 应发工资  ？<br />     *  ----- >  实发工资<br />     */<br />    private BigDecimal shouldTotalAmt;<br />    /**<br />     * 实发工资<br />     */<br />    private BigDecimal realTotalAmt;<br />    /**<br />     * 扣除工资  ？<br />     *  ----- >  0<br />     */<br />    private BigDecimal deductTotalAmt;<br />    /**<br />     * 工资内容  ？<br />     *  ----- >  给空  空字符串<br />     */<br />    private String content;<br />    /**<br />     * 寄语   ？<br />     *  ----- >  给空  空字符串<br />     */<br />    private String bless;<br />    /**<br />     * 代发状态<br />     */<br />    private String payStatus;<br />    private String payStatusDesc;
/**<br />     * 是否拆分(0:否 1：是)<br />     */<br />    private String isSplit;<br />    /**<br />     * 拆分联合id  ？<br />     *  ----- >  给空  空字符串<br />     */<br />    private String splitUnion;
/**<br />     * 发放时间<br />     */<br />    private LocalDateTime payDateTime;
/**<br />     * 代发备注<br />     *  ----- >  成功  空<br />     *  ----- >  不成功  failsMsg<br />     */<br />    private String remark1;
/**<br />     * 创建时间<br />     */<br />    private LocalDateTime crtDateTime;<br />    /**<br />     * 最后一次修改时间<br />     */<br />    private LocalDateTime updDateTime;

}

{ “_id” : “402881667a0763cc017a0b7c3bbf1a3b”, “yearm” : “202106”, “wageSheetId” : “60c78c3e6f2f626eabfa109a”, “groupId” : “40288167708d7e4301708f23f73f002d”, “entId” : “40288165706ea132017070c784dd0001”, “employeeId” : “”, “custName” : “许廷福”, “idNumber” : “412724196506011511”, “bankCard” : “6222031702005559537”, “bankCode” : “01020000”, “bankName” : “工商银行”, “realTotalAmt” : NumberDecimal(“25.8900”), “phone” : “”, “payStatus” : “1”, “payStatusDesc” : “代发成功”, “failsMsg” : “”, “receiptsStatus” : “3”, “receiptsStatusDesc” : “未确认”, “pushStatus” : “5”, “pushStatusDesc” : “不推送”, “detailCrtDateTime” : ISODate(“2021-06-14T17:05:29.022Z”), “payDateTime” : ISODate(“2021-06-14T18:11:06.158Z”), “pushId” : “”, “pushType” : “9999”, “pushTypeDesc” : “未解析出数据”, “pushStyle” : “9999”, “pushStyleDesc” : “未解析出数据”, “isSplit” : “0”, “isSplitDesc” : “否”, “updDateTime” : ISODate(“2021-06-15T17:18:55.114Z”), “channelTransNo” : “1223788018”, “storeId” : “ff80808168a1fe2c0168a21bc30c001e”, “content” : “[ {\n \”colNum\” : 0,\n \”value\” : \”许廷福\”\n}, {\n \”colNum\” : 1,\n \”value\” : \”acSJU7VCds657KnYNrZVAfM8c7mi6luu\”\n}, {\n \”colNum\” : 2\n}, {\n \”colNum\” : 3,\n \”value\” : \”DgtP8T+fiEp+X7RUzIY5b2ze9W/QORmZPjcbKSnO+nA=\”\n}, {\n \”colNum\” : 4,\n \”value\” : \”0.25\”\n}, {\n \”colNum\” : 5\n} ]”, “remark4” : “”, “_class” : “chain.cloud.data.mongo.model.ScWageDetailInfo” }

{ “_id” : “402881657a0c8a27017a0fd384061f28”, “yearm” : “202106”, “wageSheetId” : “60c8a7d46f2f624bad921760”, “groupId” : “40288167708d7e4301708f23f73f002d”, “entId” : “40288165706ea132017070c784dd0001”, “employeeId” : “”, “custName” : “余小冰”, “idNumber” : “440982199701225867”, “bankCard” : “6210958540037413207”, “bankCode” : “01000000”, “bankName” : “邮储银行”, “realTotalAmt” : NumberDecimal(“22.2900”), “phone” : “”, “payStatus” : “2”, “payStatusDesc” : “代发失败”, “failsMsg” : “对方行已拒绝:账号不存在”, “receiptsStatus” : “3”, “receiptsStatusDesc” : “未确认”, “pushStatus” : “5”, “pushStatusDesc” : “不推送”, “detailCrtDateTime” : ISODate(“2021-06-15T13:19:18.021Z”), “payDateTime” : ISODate(“2021-06-15T13:41:11.066Z”), “pushId” : “”, “pushType” : “9999”, “pushTypeDesc” : “未解析出数据”, “pushStyle” : “9999”, “pushStyleDesc” : “未解析出数据”, “isSplit” : “0”, “isSplitDesc” : “否”, “updDateTime” : ISODate(“2021-06-15T17:18:55.792Z”), “channelTransNo” : “1227781023”, “storeId” : “ff80808168a1fe2c0168a21bc30c001e”, “content” : “[ {\n \”colNum\” : 0,\n \”value\” : \”余小冰\”\n}, {\n \”colNum\” : 1,\n \”value\” : \”Hep5Qejw2mvw17Ut5G1Bl6xTNHCjb3uL\”\n}, {\n \”colNum\” : 2\n}, {\n \”colNum\” : 3,\n \”value\” : \”rYwwMNo+HNxU/rN0Ds6Ap+g7FVohh4XOXczxUm/buoA=\”\n}, {\n \”colNum\” : 4,\n \”value\” : \”0.22\”\n}, {\n \”colNum\” : 5\n} ]”, “remark4” : “”, “_class” : “chain.cloud.data.mongo.model.ScWageDetailInfo” }

{ “_id” : “4028816776c51d920176c6ab3c2c063f”, “yearm” : “202101”, “wageSheetId” : “d830c5c6bbdb40a68d27b2feda579888”, “groupId” : “40288167708d7e4301708f23f73f002d”, “entId” : “40288165706ea132017070c784dd0001”, “employeeId” : “”, “custName” : “冯建军”, “idNumber” : “413001197009264035”, “bankCard” : “6216918000883000”, “bankCode” : “03050000”, “bankName” : “民生银行”, “realTotalAmt” : NumberDecimal(“65.2100”), “phone” : “”, “payStatus” : “2”, “payStatusDesc” : “代发失败”, “failsMsg” : “对方行已拒绝:账号不存在或其他错误”, “receiptsStatus” : “3”, “receiptsStatusDesc” : “未确认”, “pushStatus” : “5”, “pushStatusDesc” : “不推送”, “detailCrtDateTime” : ISODate(“2021-01-03T05:14:39.531Z”), “payDateTime” : ISODate(“2021-01-03T05:35:59.785Z”), “pushId” : “”, “pushType” : “9999”, “pushTypeDesc” : “未解析出数据”, “pushStyle” : “9999”, “pushStyleDesc” : “未解析出数据”, “isSplit” : “0”, “isSplitDesc” : “否”, “updDateTime” : ISODate(“2021-01-03T17:14:49.599Z”), “channelTransNo” : “519544838”, “storeId” : “ff80808168a1fe2c0168a21bc30c001e”, “content” : “[ {\n \”colNum\” : 0,\n \”value\” : \”冯建军\”\n}, {\n \”colNum\” : 1,\n \”value\” : \”Nf75qiWDItVpj0kGwS5JrwIy4wMDXkyV\”\n}, {\n \”colNum\” : 2\n}, {\n \”colNum\” : 3,\n \”value\” : \”UPLFT7pRCIp4Sgvr6yj9AB+WofYwyUNyELdg3zpxozw=\”\n}, {\n \”colNum\” : 4,\n \”value\” : \”0.65\”\n}, {\n \”colNum\” : 5\n} ]”, “remark4” : “”, “_class” : “chain.cloud.data.mongo.model.ScWageDetailInfo”, “deductTotalAmt” : NumberDecimal(“0.0000”) }

2、表结构2
主表：scWageSheetInfo 2w 一家企业开/结束 ent_id group_id . app_id

public class TempWageSheetInfo implements Serializable { /*
id
*/
@Id
private String id;

/**<br />     * 方案名称<br />     */<br />    private String wageName;

/**<br />     * 资金所属日期 ？<br />     *  ----- >  给空  空字符串<br />     */<br />    private String fundDate;<br />    private String fundDateDesc;

/**<br />     * 资金类型<br />     */<br />    private String fundTypeDesc;

/**<br />     * 发放总人数<br />     */<br />    private Integer cnt;<br />    /**<br />     * 代发总金额<br />     */<br />    private BigDecimal realTotalAmt;<br />    /**<br />     * 代发成功人数<br />     */<br />    @Builder.Default<br />    private Integer successCnt = 0;<br />    /**<br />     * 代发成功金额<br />     */<br />    @Builder.Default<br />    private BigDecimal successAmt = BigDecimal.ZERO;<br />    /**<br />     * 代发失败人数<br />     */<br />    @Builder.Default<br />    private Integer failCnt = 0;<br />    /**<br />     * 代发失败金额<br />     */<br />    @Builder.Default<br />    private BigDecimal failAmt = BigDecimal.ZERO;

/**<br />     * 方案状态<br />     */<br />    private String wageStatus;<br />    private String wageStatusDesc;

/**<br />     * 代发错误信息 ？<br />     */<br />    private String failMsg;

/**<br />     * 创建时间<br />     */<br />    private LocalDateTime crtDateTime;<br />    /**<br />     * 最后一次修改时间<br />     */<br />    private LocalDateTime updDateTime;<br />}

注意：红色字体为Mongo表中没有，或者是并未找到的字段

{ “_id” : ObjectId(“60c7f104eb4a721502e5da0c”), “yearm” : “202106”, “entId” : “40288165706ea132017070c784dd0001”, “groupId” : “40288167708d7e4301708f23f73f002d”, “liquidation” : “0”, “liquidationDesc” : “华夏银行”, “wageName” : “2021-06-15T08:15至2021-06-15T08:19:59.999999999第1批次”, “fundType” : “2”, “fundTypeDesc” : “”, “accountId” : “4028815d6f43e73b016f4553248303a8”, “account” : “14550000000667592”, “wageStatus” : “3”, “wageStatusDesc” : “代发完成”, “employeeCnt” : 206, “realTotalAmt” : NumberDecimal(“52457.6300”), “successCnt” : 0, “successAmt” : NumberDecimal(“52192.4100”), “failCnt” : 1, “failAmt” : NumberDecimal(“265.2200”), “sheetCrtDateTime” : ISODate(“2021-06-15T00:15:00.524Z”), “checkDateTime” : ISODate(“2021-06-15T00:15:00.524Z”), “payDateTime” : ISODate(“2021-06-15T00:36:05Z”), “wxSuccessCnt” : 1, “cardCnt” : 0, “xcardCnt” : 0, “delStatus” : “0”, “delStatusDesc” : “正常”, “sheetUpdDateTime” : ISODate(“2021-06-15T00:36:05.061Z”), “proStatus” : “0”, “updDateTime” : ISODate(“2021-06-15T17:18:55.292Z”), “ascriptionType” : 4, “ascriptionTypeDesc” : “API监管账户”, “storeId” : “ff80808168a1fe2c0168a21bc30c001e”, “_class” : “chain.cloud.data.mongo.model.ScWageSheetInfo”, “cardSuccessAmount” : 0, “fundTypeCode” : “2”, “successAmount” : 0, “successCount” : 0, “totalAmount” : 0, “totalCount” : 0, “xcardSuccessAmount” : 0 }

============================
1~4月中，一个方案一个明细 1：1 “_id”:”d830c5c6bbdb40a68d27b2feda579888”
4月中一个方案，对多个明细 1：N “_id” : ObjectId(“60c7f104eb4a721502e5da0c”)

mongos> db.scWageSheetInfo.find({“_id”:”d830c5c6bbdb40a68d27b2feda579888”});{ “_id” : “d830c5c6bbdb40a68d27b2feda579888”, “yearm” : “202101”, “entId” : “40288165706ea132017070c784dd0001”, “groupId” : “40288167708d7e4301708f23f73f002d”, “liquidation” : “0”, “liquidationDesc” : “华夏银行”, “wageName” : “奖金”, “fundType” : “2”, “fundTypeDesc” : “”, “accountId” : “4028815d6f43e73b016f4553248303a8”, “account” : “14550000000667592”, “wageStatus” : “3”, “wageStatusDesc” : “代发完成”, “employeeCnt” : 1, “realTotalAmt” : NumberDecimal(“65.2100”), “successCnt” : 0, “successAmt” : NumberDecimal(“0.0000”), “failCnt” : 1, “failAmt” : NumberDecimal(“65.2100”), “sheetCrtDateTime” : ISODate(“2021-01-03T05:14:39.518Z”), “checkDateTime” : ISODate(“2021-01-03T05:14:39.518Z”), “payDateTime” : ISODate(“2021-01-03T05:36:00Z”), “wxSuccessCnt” : 1, “cardCnt” : 0, “xcardCnt” : 0, “delStatus” : “0”, “delStatusDesc” : “正常”, “sheetUpdDateTime” : ISODate(“2021-01-03T05:35:59.815Z”), “proStatus” : “0”, “updDateTime” : ISODate(“2021-01-03T17:14:49.599Z”), “ascriptionType” : 4, “ascriptionTypeDesc” : “API监管账户”, “storeId” : “ff80808168a1fe2c0168a21bc30c001e”, “_class” : “chain.cloud.data.mongo.model.ScWageSheetInfo”, “cardSuccessAmount” : 0, “fundTypeCode” : “2”, “successAmount” : 0, “successCount” : 0, “totalAmount” : NumberDecimal(“65.2100”), “totalCount” : 1, “xcardSuccessAmount” : 0 }

db.scWageDetailInfo.find({“wageSheetId”:”d830c5c6bbdb40a68d27b2feda579888”});{ “_id” : “4028816776c51d920176c6ab3c2c063f”, “yearm” : “202101”, “wageSheetId” : “d830c5c6bbdb40a68d27b2feda579888”, “groupId” : “40288167708d7e4301708f23f73f002d”, “entId” : “40288165706ea132017070c784dd0001”, “employeeId” : “”, “custName” : “冯建军”, “idNumber” : “413001197009264035”, “bankCard” : “6216918000883000”, “bankCode” : “03050000”, “bankName” : “民生银行”, “realTotalAmt” : NumberDecimal(“65.2100”), “phone” : “”, “payStatus” : “2”, “payStatusDesc” : “代发失败”, “failsMsg” : “对方行已拒绝:账号不存在或其他错误”, “receiptsStatus” : “3”, “receiptsStatusDesc” : “未确认”, “pushStatus” : “5”, “pushStatusDesc” : “不推送”, “detailCrtDateTime” : ISODate(“2021-01-03T05:14:39.531Z”), “payDateTime” : ISODate(“2021-01-03T05:35:59.785Z”), “pushId” : “”, “pushType” : “9999”, “pushTypeDesc” : “未解析出数据”, “pushStyle” : “9999”, “pushStyleDesc” : “未解析出数据”, “isSplit” : “0”, “isSplitDesc” : “否”, “updDateTime” : ISODate(“2021-01-03T17:14:49.599Z”), “channelTransNo” : “519544838”, “storeId” : “ff80808168a1fe2c0168a21bc30c001e”, “content” : “[ {\n \”colNum\” : 0,\n \”value\” : \”冯建军\”\n}, {\n \”colNum\” : 1,\n \”value\” : \”Nf75qiWDItVpj0kGwS5JrwIy4wMDXkyV\”\n}, {\n \”colNum\” : 2\n}, {\n \”colNum\” : 3,\n \”value\” : \”UPLFT7pRCIp4Sgvr6yj9AB+WofYwyUNyELdg3zpxozw=\”\n}, {\n \”colNum\” : 4,\n \”value\” : \”0.65\”\n}, {\n \”colNum\” : 5\n} ]”, “remark4” : “”, “_class” : “chain.cloud.data.mongo.model.ScWageDetailInfo”, “deductTotalAmt” : NumberDecimal(“0.0000”) }