统计模板【mapper】mapper_{mkey}_sql.json

~/Config/Mapper/{ownerId}/mapper_{mkey}_sql.json

{
  "diffTableNo": 0,
  "isRESTful": true,
  "base": {
    "connName": "ConnBt",
    "isRearEnd": true,
    "isFrontEnd": true
  },
  "responseField": {
    //rcfx  统计日常分析数据
    "my-rcfx": {
      //不使用分页拼接 SQL语句
      "isList": true,
      "show": "data_alias,data_value",
      "selectSql": {
        //"page": null,
        //"detail": null,
        //"list": null,
        "sqlList": [
          {
            "value": "select * from (",
            "sortId": 1
          },
          //2、生  日：统计家庭底下当月被保人生日的数据。
          {
            "value": "select null policy_item_id, 'birthday' data_alias, ifnull(count(bd_bbr_day),0) data_value from bt_policy where create_user_id=@_userId and month(bd_bbr_day)=@_currentMonth",
            "sortId": 2
          },
          {
            //两边必须有空格，避免sql拼接错误
            "value": " union ",
            "sortId": 3
          },
          //3、待核对：统计保单已提交，保障缴费状态未修改数据。
          {
            "value": "select null policy_item_id,'dhd' data_alias, ifnull(count(tb_i.policy_item_id),0) data_value  from bt_policy_item tb_i left join bt_policy_status tb_s on(tb_s.policy_id=tb_i.policy_id ) where tb_i.create_user_id=@_userId and tb_s.payment_date is null",
            "sortId": 4
          },
          {
            "value": " union ",
            "sortId": 5
          },
          //1、待缴纳：统计未来1个月需要缴费的保单项数据。
          {
            "value": "select null policy_item_id,'djl' data_alias, ifnull(count(tb_i.policy_item_id),0) data_value from bt_policy_item tb_i left join bt_policy_status tb_s on(tb_s.policy_id=tb_i.policy_id ) where tb_i.create_user_id=@_userId  and tb_s.status='0' and tb_s.payment_date>=@_futureMonth01S and tb_s.payment_date<=@_futureMonth01E",
            "sortId": 6
          },
          {
            "value": ") t",
            "sortId": 7
          }
        ]
        //,
        //"count": null,
        //"listField": "*",
        //"pageField": "*",
        //"detailField": "*"
      },
      "guidMethod": 0,
      "dataFromType": 0,
      "original": ""
      //"where": "and sign_id in @_filterPrimaryIds"
    },
    //按月统计分析
    "my-month": {
      //不使用分页拼接 SQL语句
      "njcResult": {
        "args": [],
        "moduleName": "~/Config/AppNodeJs/bt-plugins/policy-statistics/my-month",
        "functionName": null
      },
      "isList": true,
      "show": "data_alias,data_value,data_type",
      "selectSql": {
        //配合sqlList 里面 value=tempalte 使用  {tempvalue}原始 {addvalue} 加1
        "templateStart": 1,
        "templateEnd": 12,
        //模板连接符号
        "templateUnion": " union ",
        "template": [
          {
            "value": "select null policy_item_id,'all' data_type,'{tempvalue}' data_alias,sum(tb_i.mq_price) data_value from bt_policy_status tb_s inner join bt_policy_item tb_i on (tb_i.policy_item_id=tb_s.policy_item_id) where tb_i.create_user_id=@_userId and (tb_s.status=1 or tb_s.status=0) and month(payment_date)={tempvalue} and year(payment_date)=@_currentYear",
            "sortId": 10
          },
          {
            "value": " union ",
            "sortId": 12
          },
          {
            "value": "select null policy_item_id,'dj' data_type,'{tempvalue}' data_alias,sum(tb_i.mq_price) data_value from bt_policy_status tb_s inner join bt_policy_item tb_i on (tb_i.policy_item_id=tb_s.policy_item_id) where  tb_i.create_user_id=@_userId and (tb_s.status=0) and month(payment_date)={tempvalue} and year(payment_date)=@_currentYear",
            "sortId": 14
          },
          {
            "value": " union ",
            "sortId": 15
          },
          {
            "value": "select null policy_item_id,'yj' data_type,'{tempvalue}' data_alias,sum(tb_i.mq_price) data_value from bt_policy_status tb_s inner join bt_policy_item tb_i on (tb_i.policy_item_id=tb_s.policy_item_id) where tb_i.create_user_id=@_userId and (tb_s.status=1 ) and month(payment_date)={tempvalue} and year(payment_date)=@_currentYear",
            "sortId": 18
          }
        ],
        //"page": null,
        //"detail": null,
        //"list": null,
        "sqlList": [
          {
            "value": "select * from (",
            "sortId": 1
          },
          //表示从模板里面组合后的sql获取
          {
            "value": "template",
            "sortId": 10
          },
          {
            "value": ") t",
            "sortId": 400
          }
        ]
        //,
        //"count": null,
        //"listField": "*",
        //"pageField": "*",
        //"detailField": "*"
      },
      "guidMethod": 0,
      "dataFromType": 0,
      "original": ""
      //"where": "and sign_id in @_filterPrimaryIds"
    }
  },
  "tables": [
    {
      "id": 1,
      "name": "bt_policy_item",
      "isPrimary": true,
      "primaryColumn": [
        {
          "colName": "policy_item_id",
          "alias": "policy_item_id",
          "field": "policyItemId"
        }
      ],
      "type": "Table"
    }
  ]
}




//select 'birthday' name,count(policy_id) value from bt_policy where policy_id='3913181546147876864' and month(bd_bbr_day)=2

//union

//select 'dhd' name,count(tb_i.policy_item_id) value  from bt_policy_item tb_i left join bt_policy_status tb_s on(tb_s.policy_id=tb_i.policy_id ) where tb_i.policy_id='3913998358531936256' and tb_s.payment_date is null

//union

//select'djl' name, count(tb_i.policy_item_id) value from bt_policy_item tb_i left join bt_policy_status tb_s on(tb_s.policy_id=tb_i.policy_id and tb_s.payment_date>='2004-05-12 16:00:00' and tb_s.payment_date<'2004-06-12 16:00:00') where tb_i.policy_id='3913181546147876864' and tb_s.status='0'