zjmxf 发表于 2021-11-11 16:21:30

SQL 高手请进!

本帖最后由 zjmxf 于 2021-11-11 16:33 编辑

打印通5.0 版本,打印格式,没有“辅助数量”字段,

哪位高手写一下, 更新一下辉煌II V10.5及以上_PrintDataColumns.DB文件。




zjmxf 发表于 2021-11-11 16:32:16

<BillTable>
    <BillID>1</BillID>
    <BillName>销售单</BillName>
    <BillType>Sale</BillType>
    <SqlStr_M>
      declare @s varchar(100),@sql varchar(1000),@paymoney decimal(18,2);
      if(EXISTS(select * from Woolinte_BillIndex where billnumberid='@@'))
      begin
      select @s=isnull(paymoney,0) from Woolinte_BillIndex where billnumberid='@@';
      end
      else
      begin
       set @s='0';
      end
      set @sql='select col='''+ replace(case when @s='' then '0' else @s end,',',''' union all select ''')+''''
       set @sql='select * into ##bb from ('+@sql+') b';
       --PRINT @sql
       exec (@sql);
       select @paymoney=SUM(convert(decimal(18,2),isnull(col,0.0))) from ##bb ;
       --print @paymoney
       drop table ##bb;

       select Convert(varchar(10),getdate(),120) as date,* from
      (select a.*,
      case when a.state='1' then CAST(b.ARTotal as numeric(12,2))
      else
      case when dly.draft='1' then CAST(b.ARTotal as numeric(12,2))
      else
      CAST(b.ARTotal as numeric(12,2))+@paymoney+isnull(PreferenceMoney,0)-DlySumTotal end end as ARTotal,
      case when a.state='1' then
      (cast((CAST(b.ARTotal as numeric(12,2))-CAST(b.APTotal as numeric(12,2))+DlySumTotal-@paymoney-isnull(PreferenceMoney,0)) as numeric(12,2)))
      else
       case when dly.draft='1' then
       (cast((CAST(b.ARTotal as numeric(12,2))-CAST(b.APTotal as numeric(12,2))+DlySumTotal-@paymoney-isnull(PreferenceMoney,0)) as numeric(12,2)))
       else
       CAST(b.ARTotal as numeric(12,2))
       end end as SumARTotal ,
      b.bfullname as bfname,b.area as area,b.person,b.mophone as mophone,b.TelAndAddress,c.kfullname as changku,g.kfullname as changku2,d.efullname as zhidanren,e.efullname as jinshouren, f.dfullname
          ,@paymoney as SumPayMoney
      from Woolinte_BillIndex a
      left join dlyndx dly on a.billcode=dly.number
      left join btype b on a.btypeid_=b.btypeid
      left join Stock c on a.ktypeid_=c.ktypeid
      left join Stock g on a.ktypeid_=g.ktypeid
      left join employee d on a.usertypeid=d.etypeid
      left join employee e on a.etypeid_=e.etypeid
      left join Department f on a.DTypeID_=f.dtypeid
      where a.billtype='Sale')D where billnumberid='@@';
    </SqlStr_M>
    <SqlStr_S>
        select DetailRowID AS xuhao,case when GBL.DetailType='1' then '赠品' else'' end as zengping,GBL.ptypeid_,p.UsefulLifeDay AS PUsefulLifeDay, P.pusercode AS pucode,P.pfullname AS Ptypename,GBL.UintName AS UintName,P.Standard AS Stand,p.type as Type,x.BarCode AS BarCode,xw.barcode as jbBarCode,
GBL.PDAQty as pdaqtygroup,GBL.PDAPrice,GBL.PDADiscount,GBL.PDAPrice*GBL.PDADiscount AS PDADistPrice,
GBL.pdaprice*GBL.PDAQty as pdasum,GBL.pdaprice*GBL.PDADiscount*GBL.PDAQty as distpdasum,GBL.Comment,GBL.GoodsNumber,GBL.ProduceDate,GBL.ValidDate,
CASE WHEN ISNULL(GBL.PDAQty*GBL.UintRate, 0) = 0 THEN ''
    WHEN ISNULL(GBL.PDAQty*GBL.UintRate,0) &gt;= CAST(isnull(UnitRate1, 0) AS INT) AND (ISNULL(GBL.PDAQty*GBL.UintRate,0)&lt; CAST(isnull(UnitRate2, 0) AS INT)OR CAST(isnull(UnitRate2, 0) AS INT)=0 )
   THEN CAST(CAST(GBL.PDAQty*GBL.UintRate AS float) AS VARCHAR(20))+PU.Unit1
    WHEN ISNULL(GBL.PDAQty*GBL.UintRate,0)&gt;= CAST(isnull(UnitRate2, 0) AS INT) AND (ISNULL(GBL.PDAQty*GBL.UintRate,0)&lt;CAST(isnull(UnitRate3, 0)AS INT) OR CAST(isnull(UnitRate3, 0)AS INT)=0)
      THEN CONVERT(VARCHAR(20),CAST(GBL.PDAQty*GBL.UintRate / ISNULL(UnitRate2, 0)AS INT)) +isnull(unit2, '')
      + (CASE WHEN Cast(GBL.PDAQty*GBL.UintRate as INT) % cast(ISNULL(UnitRate2, 0) as int) != 0 THEN CONVERT(VARCHAR(20), cAST(GBL.PDAQty*GBL.UintRate AS int) % cast(ISNULL(UnitRate2, 0) as int)) +isnull(PU.Unit1, '') ELSE '' END)   
    WHEN ISNULL(GBL.PDAQty*GBL.UintRate,0)&gt;= cast(UnitRate3 AS INT)
       THEN CONVERT(VARCHAR(20),CAST(GBL.PDAQty*GBL.UintRate / ISNULL(UnitRate3, 0) AS INT)) +unit3 + (CASE WHEN cast(GBL.PDAQty*GBL.UintRate as int)% cast(ISNULL(UnitRate3, 0) as int) &gt;= UnitRate2 THEN CONVERT(VARCHAR(20),CAST((cast(GBL.PDAQty*GBL.UintRate as int) % cast(ISNULL(UnitRate3, 0) as int)) / ISNULL(UnitRate2, 0) AS INT))+Unit2 ELSE '' END)
      + (CASE WHEN CAST((cast(GBL.PDAQty*GBL.UintRate as int)% cast(ISNULL(UnitRate3, 0) as int))% cast(ISNULL(UnitRate2, 0) as int) AS int)!= 0 THEN CONVERT(VARCHAR(20),CAST((cast(GBL.PDAQty*GBL.UintRate as int) % cast(ISNULL(UnitRate3, 0) as int)) % cast(ISNULL(UnitRate2, 0) as int)AS FLOAT)) +PU.Unit1 ELSE '' END) ELSE '' END AS fzdw
      ,CAST(pps.Price as numeric(12,5))RetailPrice,CAST((CAST(pps.Price as numeric(12,6)))*GBL.PDAQty AS NUMERIC(12,2)) RetailTotal,s.kfullname as KFullName
      From Woolinte_BillIndex_List GBL
      left join PType P on GBL.ptypeid_=P.ptypeid
      left join xw_PtypeBarCode x on GBL.ptypeid_=x.Ptypeid and x.UnitID+1=GBL.UintNum and x.ordid=0
      left join xw_PtypeBarCode xw on GBL.ptypeid_=xw.Ptypeid and xw.UnitID=0 and xw.ordid=0
      LEFT JOIN xw_PtypeUnit pv ON Pv.Ordid=1 AND Pv.PTypeID=P.PTypeID
      LEFT JOIN xw_PtypeUnit PU ON pu.IsBase=1 AND PU.PTypeID=P.PTypeID
      left join
      (
      SELECT ISNULL(pps.Price,'') Price,pps.PTypeId,pps.UnitID FROM xw_P_PtypePrice PPS
LEFT JOIN(select BillNumberID,PTypeId_,UintNum,DetailType FROM dbo.Woolinte_Billindex_list WBL GROUP BY BillNumberID,PTypeId_,UintNum,DetailType) WBL
ON pps.UnitID=WBL.UintNum-1 AND pps.PTypeId=WBL.PTypeID_
WHERE WBL.BillNumberID='@@' AND pps.PRTypeId='0001' AND wbl.DetailType='0'
      )pps on GBL.PTypeID_=pps.PTypeId ANDpps.UnitID=GBL.UintNum-1
      left join Stock s on gbl.KTypeID_=s.ktypeid
       LEFT JOIN (    SELECTu1.PTypeId, u3.Unit1unit3,u2.Unit1unit2,u1.Unit1 unit1,
      u3.URate UnitRate3,u2.URate UnitRate2,u1.URate UnitRate1FROM xw_PtypeUnit u1LEFT JOIN xw_PtypeUnit u2ON u1.PTypeId=u2.PTypeId AND u2.Ordid=1
      LEFT JOIN xw_PtypeUnit u3 ON u1.PTypeId=u3.PTypeId AND u3.Ordid=2 WHERE u1.IsBase=1) u ON u.PTypeId=p.ptypeid
where billnumberid='@@' ORDER BY GBL.num asc;
    </SqlStr_S>

xdwy 发表于 2021-11-11 16:45:05

你试试看用6.0的就好了。。。。。

qyhua 发表于 2021-11-11 17:13:52

启用邀请码注册,提高发帖质量,建设交流社区

lxw1107 发表于 2021-11-11 22:26:31

启用邀请码注册,提高发帖质量,建设交流社区

ytthome 发表于 2021-11-12 08:28:50

启用邀请码注册,提高发帖质量,建设交流社区

zjmxf 发表于 2021-11-12 09:08:42

xdwy 发表于 2021-11-11 16:45


版本太低,6.0不能用...................

erman 发表于 2021-11-12 09:17:37

启用邀请码注册,提高发帖质量,建设交流社区

zjmxf 发表于 2021-11-26 08:44:36

打印通5.0 版本,打印格式,没有“辅助数量”字段,

哪位高手写一下, 更新一下辉煌II V10.5及以上_PrintDataColumns.DB文件。:lol
页: [1]
查看完整版本: SQL 高手请进!