读卡

功能描述

插卡 然后读取卡上信息，通过卡号，别名，分公司的信息查询出用户信息 项目：SaleClient

组件：ChargeList

主要查询 sql

查询sql

select
  i.f_userinfo_code,
  i.f_olduserinfo_code,
  i.f_user_nature,
  i.f_userinfo_id,
  i.f_user_state,
  i.f_user_name,
  i.f_user_phone,
  i.f_balance,
  i.f_sys_balance,
  i.f_idnumber,
  i.f_credentials,
  i.f_people_num,
  i.f_comments,
  i.f_process_id,
  i.f_book_no,
  i.f_createfile_person,
  isnull(i.f_zzhh , -1) f_zzhh,
  i.f_user_level,
  i.f_createfile_date,
  i.f_rent_phone,
  i.f_jayin,
  i.version as userinfo_version,
  i.f_paper_name,
  i.f_address_phone,
  i.f_taxpayer_id,
  i.f_paper_account,
  i.f_paper_type,
  i.f_ins_stop_date,
  u.f_user_id,
  isnull(u.f_write_totalgas,0) f_write_totalgas,
  isnull(u.f_write_totalfee,0) f_write_totalfee,
  u.f_userfiles_id,
  u.f_register,
  u.f_remanent_price,
  u.f_remanent_gas,
  u.f_remanent_type,
  u.f_remanent_money,
  u.f_deduction_gas,
  u.f_table_state,
  u.f_card_id,
  u.f_install_date,
  u.f_card_password,
  u.f_meterid,
  u.f_area_code,
  u.f_tag,
  u.f_meternumber,
  u.f_metertitles,
  u.f_meter_book_num,
  u.f_meter_book_sort,
  mb.f_book_name,
  mb.f_book_code,
  u.f_times,
  u.f_iot_times,
  u.f_fillcard_times,
  u.f_gasbrand_id,
  u.f_gasmodel_id,
  i.f_contract_id,
  u.f_user_type,
  u.f_gasproperties,
  isnull(u.f_aroundmeter,'') f_aroundmeter,
  u.f_meter_base,
  u.f_initial_base,
  u.f_total_gas,
  u.f_total_fee,
  gb.f_meter_type + '('+ u.f_whether_hairpin +')' fenleixinxi,
  u.f_total_usegas_amount,
  u.f_whether_hairpin,
  u.f_startup_state,
  u.f_balance_amount,
  u.f_balance_gas,
  u.version,
  u.f_operatorid,
  u.f_filialeid,
  u.f_orgname,
  u.f_depid,
  u.f_depname,
  u.f_input_date,
  u.f_hand_date,
  u.f_gas_date,
  u.f_gas_person,
  u.f_position,
  u.f_comments comments,
  u.f_open_date,
  u.f_riser_code,
  -- 卡控流量计相关字段
  u.f_control_pass,
  u.f_control_mpa_level,
  u.f_flowmeter_model,
  u.f_flowmeter_pass,
  u.f_flowmeter_mpa_level,
  u.f_flowmeter_number,
  u.f_flowmeter_brand,
  u.f_flowmeter_factory_date,
  u.f_flowmeter_accuracy,
  u.f_flowmeter_factory_num,
  u.f_flowmeter_install_date,
  u.f_orgid,
  ab.f_adjustable_id,
  ab.id adjust_id,
--   u.f_gas_date_2,
  address.f_address,
  address.f_residential_area,
  address.f_street,
  address.f_unit,
  address.f_building,
  address.f_floor,
  address.f_room,
  address.f_slice_area,
  gb.id as brand_id,
  gb.f_meter_type,
  gb.f_meter_brand,
  gb.f_alias,
  gb.f_collection_type,
  gb.f_calculation,
  gb.f_isdecimal,
  gb.f_hascard,
  isnull(gb.f_share_times,'否') f_share_times,
  gb.f_is_check,
  gb.f_is_step,
  gm.f_topup_ceil,
  gm.f_police_gas,
  gm.f_overdr_lines,
  gm.f_meter_style,
  gm.f_coding,
  gb.f_support_purchase,
  case when isnull(u.f_capacity, 0) > 0 then u.f_capacity else gm.f_capacity end f_capacity, --表最大量程
  isnull(up.f_price_id, u.f_price_id) f_price_id,
  isnull(up.id, sp.id) f_stairprice_id,
  isnull(up.f_price_type, sp.f_price_type) f_price_type,
  isnull(up.f_price_name, sp.f_price_name) f_price_name,
  isnull(up.f_version, sp.f_version) f_version,
  isnull(cb.f_open_type,'') f_open_type,
  u.f_garbage_fee,
  u.f_inputtor,
  k.f_bank_name,
  k.f_bank_accopen,
  mi.f_imei,
  uf.f_total_gas_all,
  uf.f_total_fee_all,
  mr.f_jval,
 case when DATEDIFF(DAY,GETDATE(),ufe.f_expiration_date) <= 0 then '否'
       else case when DATEDIFF(DAY,GETDATE(),ufe.f_expiration_date) > 0 then '是'end
 end f_insurance_user
from t_userfiles u
  left join (select f_meternumber meternumber,f_imei from t_meterinfo) mi on u.f_meternumber = mi.meternumber
  left join (select id,f_jval from t_meteread) mr on u.f_meteread_maxid = mr.id
  LEFT JOIN  t_adjustablebox ab
    on ab.id = u.f_adjustable_id
  LEFT JOIN  t_userinfo i
    on u.f_userinfo_id = i.f_userinfo_id
  left join (
    select f_userinfo_id,sum(f_total_gas - isnull(f_newtable_fillgas,0)) f_total_gas_all,
    sum(f_total_fee - isnull(f_newtable_fillfee,0)) f_total_fee_all
    from t_userfiles group by f_userinfo_id
  ) uf on u.f_userinfo_id = uf.f_userinfo_id
    LEFT JOIN (select f_bank_name,f_bank_accopen,f_bank_pay_number,f_userinfo_id  from t_infobank) k
    on i.f_userinfo_id=k.f_userinfo_id
  LEFT JOIN t_user_address address
    on u.f_useraddress_id = address.id
  LEFT JOIN t_gasbrand gb
    on u.f_gasbrand_id = gb.id
  LEFT JOIN t_gasmodel gm
    on u.f_gasmodel_id = gm.id
  LEFT JOIN (select f_userfiles_id userfiles_id,f_open_type from t_changetables where f_type = '换新表' and f_state = '有效') cb
    on u.f_userfiles_id = cb.userfiles_id
  LEFT JOIN
  (select * from  t_stairprice
  where getdate()>=f_perform_date and getdate()<=f_end_date
        and f_state = '有效'
  ) sp on u.f_price_id = sp.f_price_id and u.f_filialeid = sp.f_filialeid
  left join
  (
      select * from (
        select
            DATEDIFF(day,getdate(),f_parameter_value) days,
            b.f_userinfo_id,b.f_userfiles_id,sp.*
        from t_userproperties p left join t_basicliving b on p.f_userinfo_id = b.f_userinfo_id
        LEFT JOIN
        (select * from  t_stairprice where getdate()>=f_perform_date and getdate()<=f_end_date and f_state = '有效')
        sp on b.f_price_id = sp.f_price_id
        where p.f_state='有效' and p.f_parameter_key = 'dibaohu_endDate' and b.f_state = '有效'
    ) up where days > 0
  ) up on u.f_userinfo_id = up.f_userinfo_id
  left join t_meter_book mb on u.f_meter_book_num = mb.id
  left join (select f_expiration_date,f_userinfo_id from t_userfees where f_state ='有效')ufe on ufe.f_userinfo_id = i.f_userinfo_id
where {condition} and (f_user_state = '正常' or f_user_state='预备') and u.f_table_state != '换表' and u.f_table_state != '无效'
order by u.f_userfiles_id desc

信息

{condition} 通常为 gb.f_meter_type in ('气量卡表', '金额卡表','物联网表') and u.f_card_id='卡号' and u.f_filialeid = '当前用户所属分公司' and f_alias='卡上别名' and f_whether_hairpin != '未发'

功能权限

当有 读卡限定 权限时只能通过读卡找到用户