赛捷软件论坛's Archiver

stone 发表于 2013-3-5 20:17

item本期采购入库金额差错

【Q】item本期采购入库金额为负
有一张crystal报表,叫收发存汇总表,表中有字段“本期收进/采购入库”无Item数量但有金额,或有Item数据无金额。
【A】从表的公式来看,如“本期收进/采购入库”,本期“采购入库”的数量和金额公式如下:
1)金额
if  mid({?fromdate},1,4)=mid({?todate},1,4)  then //从fromdate字段中把第1位到第4位取出,判断数值在范围内否,在这里是“如果输入的开始年度和结束年度是一样的”,那么……
  if {ICHIST.FISCYEAR} = mid({?fromdate},1,4) //IC Tansaction History的Fisc Year如果等于输入的年度
AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2))  AND
     {ICHIST.FISCPERIOD} <= ToNumber( mid({?todate},5,2) ) and
     (({ICHIST.TRANSTYPE}=1 OR {ICHIST.TRANSTYPE}=2 OR {ICHIST.TRANSTYPE}=3 ) )
//IC Tansaction History的交易类型为Receipt、Receipt Ajustment、Receipt Return。
      then  {ICHIST.HOMEEXTCST} else 0 //Extended Cost - Functional,用本位币显示扩展成本
else
  (if ({ICHIST.FISCYEAR} = mid({?fromdate},1,4) AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2)) ) OR //输入的开始年度对应并且大于等于输入的期间,或者……
    ( {ICHIST.FISCYEAR} = mid({?todate},1,4) AND {ICHIST.FISCPERIOD} <= ToNumber(mid({?todate},5,2) )) OR //输入的结束年度并且小于等于输入的期间,或者……
    ( {ICHIST.FISCYEAR} > mid({?fromdate},1,4) AND {ICHIST.FISCYEAR} < mid({?todate},1,4) ) AND
    (({ICHIST.TRANSTYPE}=1 OR {ICHIST.TRANSTYPE}=2 OR {ICHIST.TRANSTYPE}=3 ) )
    then {ICHIST.HOMEEXTCST} else 0 )

2)数量
if  mid({?fromdate},1,4)=mid({?todate},1,4)  then
  if {ICHIST.FISCYEAR} = mid({?fromdate},1,4) AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2))  AND
     {ICHIST.FISCPERIOD} <= ToNumber( mid({?todate},5,2) ) AND
     (({ICHIST.TRANSTYPE}=1 OR {ICHIST.TRANSTYPE}=2 OR {ICHIST.TRANSTYPE}=3 ) )

      then  {ICHIST.QUANTITY}*{ICUNIT.CONVERSION} else 0
else
  (if ({ICHIST.FISCYEAR} = mid({?fromdate},1,4) AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2)) ) OR
    ( {ICHIST.FISCYEAR} = mid({?todate},1,4) AND {ICHIST.FISCPERIOD} <= ToNumber(mid({?todate},5,2) )) OR
    ( {ICHIST.FISCYEAR} > mid({?fromdate},1,4) AND {ICHIST.FISCYEAR} < mid({?todate},1,4) ) AND
    (({ICHIST.TRANSTYPE}=1 OR {ICHIST.TRANSTYPE}=2 OR {ICHIST.TRANSTYPE}=3 ) )
    then {ICHIST.QUANTITY}*{ICUNIT.CONVERSION} else 0 )
----------------------------------------------------------
if  mid({?fromdate},1,4)=mid({?todate},1,4)  then
  if {ICHIST.FISCYEAR} = mid({?fromdate},1,4) AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2))  AND
     {ICHIST.FISCPERIOD} <= ToNumber( mid({?todate},5,2) ) AND
     (({ICHIST.TRANSTYPE}=13) )//Stock Transfer To
      then  {ICHIST.QUANTITY}*{ICUNIT.CONVERSION} else 0
else
  (if ({ICHIST.FISCYEAR} = mid({?fromdate},1,4) AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2)) ) OR
    ( {ICHIST.FISCYEAR} = mid({?todate},1,4) AND {ICHIST.FISCPERIOD} <= ToNumber(mid({?todate},5,2) )) OR
    ( {ICHIST.FISCYEAR} > mid({?fromdate},1,4) AND {ICHIST.FISCYEAR} < mid({?todate},1,4) ) AND
    (({ICHIST.TRANSTYPE}=13) )
    then {ICHIST.QUANTITY}*{ICUNIT.CONVERSION} else 0 )

页: [1]

Powered by Discuz! Archiver 7.2  © 2001-2009 Comsenz Inc.