打印点分组

简介

增加运营商表，每个打印点属于某个运营商（通过ComSN关联），提供基于运营商的统计功能

新增运营商表

• tblCompany

新增运营商月度统计

• tblCompanyMonthStat

接口

• MSREQ_COMPANY_SET 创建运营商

• MSREQ_COMPANY_DEL 删除营运商

• MSREQ_COMPANY_CHG 修改营运商信息

• MSREQ_COMPANY_GET 获取运营商列表

• MSREQ_COMPANY_MONTH_ADD 生成营运商月度统计记录

• MSREQ_COMPANY_MONTH_GET 获取营运商月度统计记录

• MSREQ_COMPANY_MONTH_CHG 修改营运商月度统计状态

统计生成

    select d.comsn as 'ComSN', d.comname as '运营商', 
    count(*) as '使用次数', 
    count(DISTINCT a.logonname) as '使用人数', 
    sum(a.printpages) as '使用页数', sum(a.papernum) as '使用张数', 
    sum(CASE WHEN (a.PrintType&1<>0  AND a.PaperId=9 AND a.property&8=0) THEN a.printpages END) as '打印A4黑白页数', 
    sum(CASE WHEN (a.PrintType&1<>0  AND a.PaperId=9 AND a.property&8<>0) THEN a.printpages END) as '打印A4彩色页数', 
    sum(CASE WHEN (a.PrintType&1<>0  AND a.PaperId<>9 AND a.property&8=0) THEN a.printpages END) as '打印A3黑白页数', 
    sum(CASE WHEN (a.PrintType&1<>0  AND a.PaperId<>9 AND a.property&8<>0) THEN a.printpages END) as '打印A3彩色页数', 
    sum(CASE WHEN a.PrintType&1<>0 THEN a.printpages END) as '打印页数', 
    sum(CASE WHEN (a.PrintType&2<>0  AND a.PaperId=9 AND a.property&8=0) THEN a.printpages END) as '打印A4黑白页数', 
    sum(CASE WHEN (a.PrintType&2<>0  AND a.PaperId=9 AND a.property&8<>0) THEN a.printpages END) as '打印A4彩色页数', 
    sum(CASE WHEN (a.PrintType&2<>0  AND a.PaperId<>9 AND a.property&8=0) THEN a.printpages END) as '打印A3黑白页数', 
    sum(CASE WHEN (a.PrintType&2<>0  AND a.PaperId<>9 AND a.property&8<>0) THEN a.printpages END) as '打印A3彩色页数', 
    sum(CASE WHEN a.PrintType&2<>0 THEN a.printpages END) as '复印页数', 
    sum(CASE WHEN a.PrintType&4<>0 THEN a.printpages END) as '扫描页数', 
    sum(a.papernum) as '使用张数', sum(a.UsedMoney) as '使用金额', 
    count(CASE WHEN(b.status&0x300<>0) THEN 1 END) as '退款次数', 
    sum(CASE WHEN(b.status&0x300<>0) THEN a.UsedMoney END) as '退款金额' from tblprintrec a left join tblprintjob b on a.jobid=b.jobid INNER JOIN tblprinter c on a.printersn=c.printersn INNER JOIN tblCompany d on d.comsn=c.comsn  where a.printdate>20241000 and a.printdate<20241100 group by  d.comsn, d.comname