unitmb.pas

飞恒进销存(超市批发)管理系统V5.1（含源程序） 语言：Delphi 6/7 相关控件：FastReport 2.4以上, Ehlib 3.4以上 1.数据库为fhe2db_V51.da

      end;
    end;
  end;
  //橡胶商行,备注里要输入条数
  with dataE2 do
  begin
    sMemo1:=Trim(QueryDetail.FieldByName('memo1').AsString);
    if sMemo1<>'' then
    begin
      for i:=1 to length(sMemo1) do
        if sMemo1[i] = '*' then
          sMemo2:=copy(sMemo1,i+1,length(sMemo1));
      try
        if sMemo2<>'' then
          StrToFloat(sMemo2);
      except
        begin
          Application.MessageBox('请在*号后面输入正确的数值！','提示',64);
          exit;
        end;
      end;
      if sMemo2<>'' then
      begin
        adoCmd.commandtext:='update onHand set  qty3=qty3 '+sTmp+' :qty where storeid= :storeid and goodsid= :goodsid';
        adoCmd.parameters[0].value:=StrToFloat(sMemo2);
        adoCmd.parameters[1].value:=nTarStoreId;
        adoCmd.parameters[2].value:=nGoodsid;
        adoCmd.execute;
      end;
    end;
  end;  
end;

procedure TMbForm.UpdateGoodsInPrice ;
var
  nInprice,ntotalQty:real;
  iPerQty:Integer;
  s:string;
begin
  with dataE2 do
  begin
    nInPrice:=queryDetail.fieldbyname('price').AsCurrency;
    iPerQty:=queryDetail.fieldbyname('PerQty').AsVariant;
    //计算平均价
    if nInpricetype=0 then
    begin
      s:=  ' select g.inprice,g.perqty,isnull(sum(o.qty),0) as SumQty,isnull(sum(o.qty1),0) as SumQty1 from goods g inner join onhand o';
      s:=s+' on g.goodsid=o.goodsid where g.goodsid= :goodsid group by g.inprice,g.perqty';
      if querysum.active then querysum.close;
      querySum.SQL.Clear;
      querySum.SQL.Add(s);
      querysum.Parameters[0].Value :=nGoodsid;
      querySum.open;
      if not querySum.Eof then
      begin
        iPerQty := querySum.fieldbyname('PerQty').AsVariant;
        if QueryDetail.FieldByName('disc').AsBoolean=false then
        begin
          //nQty:=nQty*iPerQty;
          //ntotalQty:=querySum.fieldbyname('sumqty').asfloat*iPerQty
          //ntotalQty:=(nQty+querySum.fieldbyname('sumqty').asfloat)*iPerQty
          //else if QueryDetail.FieldByName('disc').AsBoolean=true then
          ntotalQty:=querySum.fieldbyname('sumqty').asfloat;//+querySum.fieldbyname('sumqty1').asfloat;
          if nTotalQty > 0 then
            nInPrice:=toMyValue((nInprice*nQty+(ntotalQty-nQty)*querySum.fieldbyname('inprice').asCurrency)/ntotalQty);
        end;
      end;
    end
    else if QueryDetail.FieldByName('disc').AsBoolean then
    begin
      nInPrice:=nInPrice*iPerQty;
    end;
    if (Not QueryDetail.FieldByName('disc').AsBoolean) and (nInPrice>0) then  //支数进仓不更改进价
    begin
      adoCmd.commandtext:='update Goods set Inprice= :Inprice,Inprice2= :Inprice2 where goodsid= :goodsid';
      adoCmd.parameters[0].value:=nInprice;
      adoCmd.parameters[1].value:=toMyValue(nInprice/iPerQty);
      adoCmd.parameters[2].value:=nGoodsid;
      adoCmd.execute;
    end;
  end;
end;

//由现有明细单,修改库存 nType:update=1,delete=2;nDire: 0 normal,1 back
procedure TmbForm.UpdateOnHandBack(nType,nNowStoreId,nDire:integer) ;
var
  sSql,sTmp,sMemo1,sMemo2:string;
  nTmpTableid,i:integer;
  nQty0,nQty1,nQty2,nQty3:Real;
  nPerQty:Real;
  bDisc:Boolean;
begin
  //other in out,old direction saved in OldCusId =0 In,=1 Out
  if (nTableid=26) or (nTableId=27) then
    nTmpTableid:=26+nOldOutStoreId
  else
    nTmpTableid:=nTableId;

  sTmp:=GetOnHandDirection(nDire,nTmpTableId);

  if DataE2.bTwoUnit then   //双单位
  begin
    with dataE2 do  //syx 2005-06-05判断零数是否大于规格
    begin
      qryGet.Close;
      qryGet.SQL.Clear;
      qryGet.SQL.Add('select o.qty,o.qty1,n.qty as qty2,n.disc,g.perqty,n.Memo1,n.itemno from onhand o,'+myTable+'D n,goods g');
      qryGet.SQL.Add(' where n.Billid= :Billid and storeid= :storeid and n.goodsid= o.goodsid and o.goodsid=g.goodsid');
      if nType=1 then
        qryGet.SQL.Add(' and ItemNo= :itemno ')
      else
        qryGet.SQL.Add(' and charindex('''+','''+'+ltrim(str(n.itemno))+'''+','''+', '''+sDelItemNo+''''+') >0 ');
      qryGet.parameters[0].value:=dataE2.nBillId;
      qryGet.parameters[1].value:=nNowStoreId;
      if nType=1 then
        qryGet.parameters[2].value:=dataE2.queryDetail.fieldbyname('itemno').asinteger;
      qryGet.Open;
      qryGet.First;
      While not qryGet.Eof do
      begin
        nQty0 :=qryGet.FieldByName('qty').AsFloat;
        nqty1:=qryGet.FieldByName('qty1').AsFloat;
        nQty2:=qryGet.FieldByName('qty2').AsFloat;
        nPerQty:=qryGet.FieldByName('perqty').AsFloat;
        bDisc:=qryGet.FieldByName('disc').AsBoolean;
        sMemo1:=qryGet.FieldByName('Memo1').AsString;
        if not bDisc then
          sSql:='update onHand set qty=o.qty '+sTmp+' n.qty'
        else begin
          if (nqty1>=nqty2) and (sTmp='-') then
            sSql:='update onHand set qty1=o.qty1 '+sTmp+' n.qty';
          if (nqty1<nqty2) and (sTmp='-') then
            sSql:='update onHand set qty=o.qty '+sTmp+' :qty,qty1=o.qty1 '+sTmp+' :qty1';
          if (nqty1+nqty2<nPerQty) and (sTmp='+') then
            sSql:='update onHand set qty1=o.qty1 '+sTmp+' n.qty';
          if (nqty1+nqty2>=nPerQty) and (sTmp='+') then
            sSql:='update onHand set qty=o.qty '+sTmp+' :qty,qty1=o.qty1 '+sTmp+' :qty1';
          end;
          //2005-12-08 开始
          sSql:=sSql+' from onhand o,'+myTable+'D n ';
          sSql:=sSql+' where n.Billid= :Billid and storeid= :storeid and n.goodsid= o.goodsid';
          if nType=1 then
            sSql:=sSql+' and ItemNo= :itemno '
          else
            sSql:=sSql+' and ItemNo= :itemno ';
          adoCmd.commandtext:=sSql;
          adoCmd.parameters.ParamByName('Billid').Value:=nBillId;
          adoCmd.parameters.ParamByName('storeid').Value:=nNowStoreId;
          //if nType=1 then
          adoCmd.parameters.ParamByName('ItemNo').value:=qryGet.fieldbyname('itemno').asinteger;;
          if (nqty1<nqty2) and (sTmp='-') and bDisc then
          begin
            adoCmd.parameters.ParamByName('qty').Value:=Ceil((nqty2-nqty1)/nPerQty);
            adoCmd.parameters.ParamByName('qty1').Value:=nqty2-Ceil((nqty2-nqty1)/nPerQty)*nPerQty;
          end;
          if (nqty1+nqty2>=nPerQty) and (sTmp='+') and bDisc then
          begin
            adoCmd.parameters.ParamByName('qty').Value:=Int((nqty2+nqty1)/nPerQty);
            adoCmd.parameters.ParamByName('qty1').Value:=nqty2-Int((nqty2+nqty1)/nPerQty)*nPerQty;
          end;
          adoCmd.execute;
          //2005-12-08 结束
        qryGet.next;
      end;
    end;
    //sSql:=     'update onHand set  qty= o.qty '+sTmp+' (case when n.disc=0 then n.qty else 0 end)';
    //sSql:=sSql+'                 , qty1= o.qty1 '+sTmp+' (case when n.disc=1 then n.qty else 0 end)';
  end
  else  //下面是更新单位的库存
  begin
    sSql:= 'update onHand set  qty= o.qty '+sTmp+' n.qty';
    sSql:=sSql+' from onhand o,'+myTable+'D n ';
    sSql:=sSql+' where n.Billid= :Billid and storeid= :storeid and n.goodsid= o.goodsid';
    if nType=1 then
      sSql:=sSql+' and ItemNo= :itemno '
    else
      //sSql:=sSql+' and charindex(ltrim(str(itemno)), '''+sDelItemNo+''''+') >0 ';
      sSql:=  sSql+' and charindex('''+','''+'+ltrim(str(n.itemno))+'''+','''+', '''+sDelItemNo+''''+') >0 ';
    with dataE2 do
      begin
        adoCmd.commandtext:=sSql;
        adoCmd.parameters.ParamByName('Billid').Value:=nBillId;
        adoCmd.parameters.ParamByName('storeid').Value:=nNowStoreId;
        if nType=1 then
          adoCmd.parameters.ParamByName('ItemNo').value:=queryDetail.fieldbyname('itemno').asinteger;;
        {if (nqty1<nqty2) and (sTmp='-') and bDisc then
        begin
          adoCmd.parameters.ParamByName('qty').Value:=Ceil((nqty2-nqty1)/nPerQty);
          adoCmd.parameters.ParamByName('qty1').Value:=nqty2-Ceil((nqty2-nqty1)/nPerQty)*nPerQty;
        end;
        if (nqty1+nqty2>=nPerQty) and (sTmp='+') and bDisc then
        begin
          adoCmd.parameters.ParamByName('qty').Value:=Int((nqty2+nqty1)/nPerQty);
          adoCmd.parameters.ParamByName('qty1').Value:=nqty2-Int((nqty2+nqty1)/nPerQty)*nPerQty;
        end;}

        adoCmd.execute;
      end;
  end;
    //橡胶商行,备注里要输入条数
    sMemo1:=Trim(sMemo1);
    if sMemo1<>'' then
    begin
      for i:=1 to length(sMemo1) do
        if sMemo1[i] = '*' then
          sMemo2:=copy(sMemo1,i+1,length(sMemo1));
      if sMemo2<>'' then
      begin
        nQty3:=StrToFloat(sMemo2);
        with dataE2 do
        begin
          adoCmd.commandtext:='update onHand set qty3=o.qty3'+sTmp+':Qty3';
          adoCmd.commandtext:=adoCmd.commandtext+' from onhand o,'+myTable+'D n ';
          adoCmd.commandtext:=adoCmd.commandtext+' where n.Billid= :Billid and storeid= :storeid and n.goodsid= o.goodsid';
          if nType=2 then
            adoCmd.commandtext:=adoCmd.commandtext+' and charindex('''+','''+'+ltrim(str(n.itemno))+'''+','''+', '''+sDelItemNo+''''+') >0 ';
          adoCmd.parameters[0].value:=nQty3;
          adoCmd.parameters[1].Value:=nBillId;
          adoCmd.parameters[2].Value:=nNowStoreId;
          adoCmd.execute;
        end;
      end;
    end;
end;

procedure TmbForm.InsertDetail;
var
  sSql:string;
begin
  with dataE2 do
  begin
    sSql:='( :billid, :itemno, :goodsid, :units, :qty, :price,0, :disc,0, :memo1)';
    adoCmd.commandtext:='insert into '+myTable+'D values '+sSql;
    adoCmd.parameters[0].value:=nBillId;
    adoCmd.parameters[1].value:=queryDetail.fieldbyname('itemno').asinteger;
    adoCmd.parameters[2].value:=nGoodsId;
    adoCmd.parameters[3].value:=queryDetail.fieldbyname('units').asstring;
    adoCmd.parameters[4].value:=nQty;
    adoCmd.parameters[5].value:=queryDetail.fieldbyname('price').ascurrency;
    adoCmd.parameters[6].value:=queryDetail.fieldbyname('disc').asboolean;// asinteger;
    adoCmd.parameters[7].value:=queryDetail.fieldbyname('memo1').asstring;

    adoCmd.execute;
  end;
end;

procedure TmbForm.UpdateDetail;
var
  s:string;
begin
  with dataE2 do
  begin
    s:='update '+myTable+'D set goodsId = :goodsid, units= :units,qty = :qty, price= :price,disc=:disc, memo1= :memo1 ';
    //只对出仓、退货修改成本（进货价）
    if ( nTableId=11 ) or ( nTableid= 12 ) or (nTableid=18) then
      s:=s+',cost= :cost ';
    s:=s+' where billid= :billid and itemno= :itemno';
    adoCmd.commandtext:=s;//'update '+myTable+'D set goodsId = :goodsid, units= :units,qty = :qty, price= :price, memo1= :memo1 where billid= :billid and itemno= :itemno';
    adoCmd.parameters[0].value:=queryDetail.fieldbyname('goodsId').asinteger;
    adoCmd.parameters[1].value:=queryDetail.fieldbyname('units').asstring;
    adoCmd.parameters[2].value:=queryDetail.fieldbyname('qty').asfloat;
    adoCmd.parameters[3].value:=queryDetail.fieldbyname('price').ascurrency;
    adoCmd.parameters[4].value:=queryDetail.fieldbyname('disc').AsBoolean;
    adoCmd.parameters[5].value:=queryDetail.fieldbyname('memo1').asstring;
    if ( nTableId=11 ) or ( nTableid= 12 ) or (nTableid=18) then begin
      adoCmd.parameters[6].value:=queryDetail.fieldbyname('cost').asCurrency;
      adoCmd.parameters[7].value:=nBillId;
      adoCmd.parameters[8].value:=queryDetail.fieldbyname('itemno').asstring;
    end else begin
      adoCmd.parameters[6].value:=nBillId;
      adoCmd.parameters[7].value:=queryDetail.fieldbyname('itemno').asstring;
    end;
    adoCmd.execute;
    if nTableId = 11 then
      UpdateCusbill(nCusId,nGoodsId,queryDetail.fieldbyname('price').ascurrency,queryDetail.fieldbyname('disc').AsBoolean);
  end;
end;

procedure TmbForm.DeleteDetail;
var
  s:string;
begin
  with dataE2 do
  begin
    //s:='delete from '+myTable+'D  where billid= :billid and charindex(ltrim(str(itemno)), '''+sDelItemNo+''''+') >0 ';
    s:='delete from '+myTable+'D  where billid= :billid and charindex('''+','''+'+ltrim(str(itemno))+'''+','''+', '''+sDelItemNo+''''+') >0 ';
    adoCmd.commandtext:=s;
    adoCmd.parameters[0].value:=nBillId;

    adoCmd.execute;
  end;
end;

procedure TmbForm.InsertMaster(sInvoNo:string);
begin
//
end;

procedure TmbForm.UpdateMaster;
begin
//
end;


procedure TmbForm.FormResize(Sender: TObject);
begin
//DBGrid1.Columns[0].Width:=w1+J;
//DBGrid1.Columns[1].Width:=w2+J+m;
end;

Function  TmbForm.GetDetailSql:string;
begin
  case nTableId of
    3,13:
      result:='select o.*,i.invono,i.billdate,i.Billamt,i.TakeAmt,i.RtnAmt  from '+myTable+
              'D o inner join '+dataE2.GetReftblName(nTableid) +' i  on o.xBillId=i.Billid where  o.billid= :billid  ';
    30:
      result:='select o.*,G.name,g.code,g.perqty from '+myTable+
              'D o inner join goods g  on o.goodsId=g.goodsid where  billid= :billid  ';
    33,43:
      result:='select o.* from '+myTable+'D o  where  o.billid= :billid  ';
    18:
      result:='select o.*,G.name,g.code,g.perqty from '+myTable+
              'D o inner join goods g  on o.goodsId=g.goodsid where  billid= :billid  ';
    else
      //result:='select o.*,G.name,case when disc=0 then g.units else g.unit2 end as units,g.code from '+myTable+
      result:='select o.*,G.name,g.code,g.perqty from '+myTable+
              'D o inner join goods g  on o.goodsId=g.goodsid where  billid= :billid  ';
  end;
end;

procedure TMbForm.InitInfo;
begin
  sCodeBuff:='';
  if edtName.Visible then
    edtName.SetFocus ;

  edtStore.Text:='';
  edtEmp.Text:='';
  edtName.text:='';
  edtInvono.Text:='';
  edtMemo.text:='';

  //100在此画面不用此项
  nStoreId:=100;
  nEmployid:=-1;
  nCusid:=-1;
end;

procedure TmbForm.FormShow(Sender: TObject);
begin
  cmdNext.Enabled:=false;
  w1:=DBGrid1.Columns[0].Width;
  w2:=DBGrid1.Columns[1].Width;
  i:=DBGrid1.Width;
  edtBillDate.Date :=date;

  //mySql:='select o.*,o.qty*o.price as total,G.name,g.units,g.code from '+myTable+'D o inner join goods g  on o.goodsId=g.goodsid where  billid= :billid  ';

  mySql:=GetDetailSql;

  with DataE2 do
  begin
    nBillId:=999999999;//888888888;