billedit.pas

进销存·完整的·有数据库的·非常完整·只得参考

        lblUnit.Visible := True;
        edtUnit.Visible := True;
        lblUnit.Caption := '客    户：';
        lblEmploye.Visible := True;
        edtEmploye.Visible := True;
        lblEmploye.Caption := '经 手 人：';
        lblPayment.Visible := True;
        edtPayment.Visible := True;
        lblPayment.Caption := '收款方式：';
        lblBillNum.Visible := True;
        edtBillNum.Visible := True;
        lblBillNum.Caption := '票    号：';
        lblInvoice.Visible := true;
        edtInvoice.Visible := True;
        lblInvoice.Caption := '发票类型：';
        lblMoney.Visible := True;
        edtMoney.Visible := True;
        lblMoney.Caption := '收款金额：';
        lblMemo.Visible := True;
        edtMemo.Visible := True;
        lblMemo.Caption := '备    注：';
        lblMemo.Left := lblAddress.Left;
        edtMemo.Left := edtAddress.Left;
        edtMemo.Width := edtMemo.Width * 2;
        bbAutoAssign.Visible := True;
      end;
    SALE_READY_EDIT:
      begin
        lblUnit.Visible := True;
        edtUnit.Visible := True;
        lblUnit.Caption := '客    户：';
        lblEmploye.Visible := True;
        edtEmploye.Visible := True;
        lblEmploye.Caption := '经 手 人：';
        lblDepot.Visible := true;
        edtDepot.Visible := True;
        lblDepot.Caption := '仓    库：';
        lblMemo.Visible := True;
        edtMemo.Visible := True;
        lblMemo.Caption := '备    注：';
        lblMemo.Left := lblAddress.Left;
        edtMemo.Left := edtAddress.Left;
        edtMemo.Width := edtMemo.Width * 2;
      end;
    SALE_POS_EDIT:
      begin
        lblUnit.Visible := True;
        edtUnit.Visible := True;
        lblUnit.Caption := '客    户：';
        lblEmploye.Visible := True;
        edtEmploye.Visible := True;
        lblEmploye.Caption := '经 手 人：';
        lblDepot.Visible := true;
        edtDepot.Visible := True;
        lblDepot.Caption := '仓    库：';
        lblMemo.Visible := True;
        edtMemo.Visible := True;
        lblMemo.Caption := '备    注：';
        edtUnit.Enabled := False;
        edtEmploye.Enabled := False;
        edtDepot.Enabled := False;
        lblMemo.Left := lblAddress.Left;
        edtMemo.Left := edtAddress.Left;
        edtMemo.Width := edtMemo.Width * 2;
        gridMain.Height := pnlPOS.Top - gridMain.Top - 5;
        pnlPOS.Visible := True;
      end;
    SALE_BACK_EDIT:
      begin
        lblUnit.Visible := True;
        edtUnit.Visible := True;
        lblUnit.Caption := '客    户：';
        lblEmploye.Visible := True;
        edtEmploye.Visible := True;
        lblEmploye.Caption := '经 手 人：';
        lblDepot.Visible := true;
        edtDepot.Visible := True;
        lblDepot.Caption := '仓    库：';
        //lblInvoice.Visible := true; edtInvoice.Visible := True; lblInvoice.Caption := '发票类型：';
        lblMemo.Visible := True;
        edtMemo.Visible := True;
        lblMemo.Caption := '备    注：';
        lblMemo.Left := lblAddress.Left;
        edtMemo.Left := edtAddress.Left;
        edtMemo.Width := edtMemo.Width * 2;
      end;
  else
    ShowMsg('内部参数出错,请与开发商联系！');
  end;
end;

procedure TfrmBillEdit.LoadData;
var
  lUnitID, lDepotID: Integer;
begin
  lPubType := 0;
  OpenDataSet(ADOSetMaster, ReturnSQL(1, lMode, lID));
  if (lID > 0) and ADOSetMaster.IsEmpty then //除错，如果没有找到单据按ID为0处理
  begin
    lID := 0;
    OpenDataSet(ADOSetMaster, ReturnSQL(1, lMode, lID));
  end;

  case lMode of //零售单部份不能修改
    SALE_POS_EDIT:
      begin
        edtBillDate.Enabled := False;
        edtUserCode.Enabled := False;
        edtUnit.Enabled := False;
        edtEmploye.Enabled := False;
        edtDepot.Enabled := False;
      end;
  end;

  if lID = 0 then
  begin
    ADOSetMaster.Append; //新增
  end
  else
  begin
    DataSetLocate(ADOSetMaster, 'ID', lID); //修改
    lPubType := ADOSetMaster.FieldByName('Type').AsInteger
  end;
  if OpenDataSet(ADOSetList, ReturnSQL(2, lMode, lID)) then
  begin
    ADOSetList.Recordset.Properties.Item['Unique Table'].Value := sUniqueTable;
  end;
  if lID = 0 then
  begin
    ADOSetMaster.FieldByName('BillDate').AsDateTime := SYSStartDate;
    ADOSetMaster.FieldByName('UserCode').AsString := GetAutoSerial(lMode, 0);
    ADOSetMaster.FieldByName('Name').AsString := Caption;
    ADOSetMaster.FieldByName('Finish').AsInteger := 0;
    //ADOSetMaster.FieldByName('EmployeID').AsInteger := LoginEmployeID;//默认经手人
    //edtEmploye.Text := LoginEmployeName;

    case lMode of
      SALE_POS_EDIT:
        begin
          lUnitID := StrToInt2(GetIniValue(frmData.ADOConnet, 'POSUnitID'));
          lDepotID := StrToInt2(GetIniValue(frmData.ADOConnet, 'POSDepotID'));
          ADOSetMaster.FieldByName('UnitID').AsInteger := lUnitID;
          ADOSetMaster.FieldByName('DepotID').AsInteger := lDepotID;
          if lUnitID > 0 then
            edtUnit.Text := FieldIdToName('Unit', IntToStr(lUnitID), 'Name');
          if lDepotID > 0 then
            edtDepot.Text := FieldIdToName('Depot', IntToStr(lDepotID), 'Name');
        end;
    end;
    ADOSetList.Insert;
  end
  else
  begin
    ADOSetMaster.Edit;
    lID := ADOSetMaster.FieldByName('ID').AsInteger;
  end;
  FieldToEdit;
  ToolShow;
end;

function TfrmBillEdit.ReturnSQL(lType, lMode, lBillID: Integer): string;
var
  sSql: string;
begin
  sSql := '';
  if lType = 1 then       //lType =1 ,1代表主表，2代表副表，返回Sql的语句
  begin //1
    sSql := ' SELECT * FROM BillIndex B where b.mode=' + IntToStr(lMode) + ' order by b.ID';
  end //1
  else
    if lType = 2 then
    begin //2
      case lMode of
        STOCK_ORDER_EDIT, STOCK_FORMAL_EDIT:
          begin
            sSql := ' SELECT bs.ID,bs.Mode,w.UserCode as WareCode,w.Name as WareName,w.Unit as WareUnit,w.Unit2,w.Scale,w.Model,w.Spec,w.Area,bs.WareID,bs.Number,bs.Number as aNumber,'
              + ' bs.Price,bs.ConstPrice, bs.Total, bs.ToNumber, bs.UnNumBer, bs.otherID, bs.Memo, bs.IndexID, bs.Date, bs.BillName, bs.Sum,bs.Type' +
              ' FROM BillStock bs INNER JOIN Ware w on bs.WareID=w.ID WHERE bs.IndexID =' +
              IntToStr(lBillID);
            sUniqueTable := 'BillStock';
          end;
        STOCK_BACK_EDIT:
          begin
            sSql := ' SELECT bs.ID,bs.Mode,w.UserCode as WareCode,w.Name as WareName,w.Unit as WareUnit,w.Unit2,w.Scale,w.Model,w.Spec,w.Area,bs.WareID,bs.Number as [Number],bs.Number as aNumber,'
              + ' bs.Price,bs.ConstPrice, bs.Total As Total, bs.ToNumber, bs.UnNumBer, bs.otherID, bs.Memo, bs.IndexID, bs.Date, bs.BillName, bs.Sum,bs.Type' +
              ' FROM BillStock bs INNER JOIN Ware w on bs.WareID=w.ID WHERE bs.IndexID =' +
              IntToStr(lBillID);
            sUniqueTable := 'BillStock';
          end;
        SALE_ORDER_EDIT, SALE_FORMAL_EDIT, SALE_READY_EDIT, SALE_POS_EDIT:
          begin
            sSql := ' SELECT bs.ID,bs.Mode,w.UserCode as WareCode,w.Name as WareName,w.Unit as WareUnit,w.Unit2,w.Scale,w.Model,w.Spec,w.Area,bs.WareID,bs.Number,'
              +
              ' bs.Price,bs.ConstPrice, bs.Total,bs.TaxPrice,bs.TaxTotal, bs.ToNumber,bs.UnNumBer,bs.otherID,bs.Memo, bs.IndexID, bs.Date, bs.BillName, bs.Sum,bs.PeiCi,bs.Type' +
              ' FROM BillSale bs INNER JOIN Ware w on bs.WareID=w.ID WHERE  bs.IndexID =' +
              IntToStr(lBillID);
            sUniqueTable := 'BillSale';
          end;
        SALE_BACK_EDIT:
          begin
            sSql := ' SELECT bs.ID,bs.Mode,w.UserCode as WareCode,w.Name as WareName,w.Unit as WareUnit,w.Unit2,w.Scale,w.Model,w.Spec,w.Area,bs.WareID,bs.Number as [Number],'
              +
              ' bs.Price,bs.ConstPrice, bs.Total as Total,bs.TaxPrice,bs.TaxTotal, bs.ToNumber,bs.UnNumBer,bs.otherID,bs.Memo, bs.IndexID, bs.Date, bs.BillName, bs.Sum,bs.PeiCi,bs.Type' +
              ' FROM BillSale bs INNER JOIN Ware w on bs.WareID=w.ID WHERE  bs.IndexID =' +
              IntToStr(lBillID);
            sUniqueTable := 'BillSale';
          end;
        STOCK_MONEY_EDIT:
          begin
            sSql := ' SELECT bs.ID, bs.Mode, bs.WareID, bs.Number, bs.Price, bs.Total,' +
              ' bs.ToNumber, bs.UnNumber, bs.OtherID, bs.Memo, bs.IndexID, bs.Date,' +
              ' bs.BillName, bs.Sum,bs.Finish,bi.UserCode, bi.Name, bi.BillDate,bs.Type' +
              ' FROM BillStock AS bs, BillIndex AS bi ' +
              ' WHERE (bs.OtherID=bi.[ID]) and  bs.IndexID =' +
              IntToStr(lBillID);
            sUniqueTable := 'BillStock';
          end;
        SALE_MONEY_EDIT:
          begin
            sSql := ' SELECT bs.ID, bs.Mode, bs.WareID, bs.Number, bs.Price, bs.Total,' +
              ' bs.ToNumber, bs.UnNumber, bs.OtherID, bs.Memo, bs.IndexID, bs.Date,' +
              ' bs.BillName, bs.Sum,bs.Finish, bi.UserCode, bi.Name, bi.BillDate,bs.Type' +
              ' FROM BillSale AS bs, BillIndex AS bi ' +
              ' WHERE (bs.OtherID=bi.[ID]) and  bs.IndexID =' +
              IntToStr(lBillID);
            sUniqueTable := 'BillSale';
          end;
      end; //case 结束
    end;//2
  Result := sSql;
end;

procedure TfrmBillEdit.FieldToEdit;
begin
  edtUnit.Text := FieldIdToName('Unit', ADOSetMaster.FieldByName('UnitID').AsString, 'Name');
  edtEmploye.Text := FieldIdToName('Employe', ADOSetMaster.FieldByName('EmployeID').AsString, 'Name');
  edtDepot.Text := FieldIdToName('Depot', ADOSetMaster.FieldByName('DepotID').AsString, 'Name');
end;

procedure TfrmBillEdit.SaveData(lType: Integer);
var
  lWareID, lDepotID, lUnitID: Integer;
  dNumber, dPrice, dConstPrice, dTotal, dARTotal, dDoARTotal: Double;
begin
  dARTotal := 0;
  dDoARTotal := 0;
  BeginTrans; //开始事务
  if (ADOSetMaster.State = dsEdit) or (ADOSetMaster.State = dsInsert) then
  begin
    ADOSetMaster.FieldByName('Mode').AsInteger := lMode;
    ADOSetMaster.FieldByName('Type').AsInteger := lType;
    if lType = 3 then
      ADOSetMaster.FieldByName('Name').AsString := Caption + '(草稿)'
    else
      ADOSetMaster.FieldByName('Name').AsString := Caption;
  end;
  if not SaveDataSet(ADOSetMaster, false) then
  begin
    RollbackTrans;
    Exit;
  end;
  lID := ADOSetMaster.FieldByName('ID').AsInteger;
  lDepotID := ADOSetMaster.FieldByName('DepotID').AsInteger;
  lUnitID := ADOSetMaster.FieldByName('UnitID').AsInteger;
  ADOSetList.DisableControls;
  ADOSetList.First;
  while not ADOSetList.Eof do
  begin
    ADOSetList.Edit;
    ADOSetList.FieldByName('IndexID').AsInteger := lID;
    ADOSetList.FieldByName('Mode').AsInteger := lMode;
    ADOSetList.FieldByName('Type').AsInteger := lType;
    lWareID := ADOSetList.FieldByName('WareID').AsInteger;
    dNumber := ADOSetList.FieldByName('Number').AsFloat;
    dPrice := ADOSetList.FieldByName('Price').AsFloat;
    dTotal := 23.00; //ADOSetList.FieldByName('Total').AsFloat;

    if lType = 2 then
      case lMode of //修改商品库存
        STOCK_FORMAL_EDIT:
          if not UpdateStock(1, lWareID, lDepotID, dNumber, dPrice, dTotal) then Exit; //入库
        SALE_BACK_EDIT:
          begin
            dConstPrice := ADOSetList.FieldByName('ConstPrice').AsFloat;
            if not UpdateStock(1, lWareID, lDepotID, dNumber, dConstPrice, dTotal) then Exit; //出库
          end;
        STOCK_BACK_EDIT, SALE_FORMAL_EDIT, SALE_READY_EDIT, SALE_POS_EDIT:
          begin
            dConstPrice := ADOSetList.FieldByName('ConstPrice').AsFloat;
            if not UpdateStock(1, lWareID, lDepotID, -dNumber, dConstPrice, -dTotal) then Exit; //出库
          end;
      end;
    if lType = 2 then
      case lMode of //修改应收应付
        STOCK_FORMAL_EDIT, SALE_FORMAL_EDIT, SALE_READY_EDIT, SALE_POS_EDIT: dARTotal := dARTotal + dTotal;
        SALE_BACK_EDIT, STOCK_BACK_EDIT: dARTotal := dARTotal - dTotal;
      end;
    ADOSetList.Next;
  end;
  if lType = 2 then
    case lMode of //零售单和现款销售单直接写入收款金额
      SALE_READY_EDIT, SALE_POS_EDIT: dDoARTotal := dARTotal;
     // STOCK_MONEY_EDIT, SALE_MONEY_EDIT: dDoARTotal := ADOSetMaster.FieldByName('Money').AsFloat;
        STOCK_MONEY_EDIT, SALE_MONEY_EDIT: dDoARTotal := ADOSetMaster.FieldByName('RPtotal').AsFloat;
    end;
  if not SaveDataSet2(ADOSetList, sUniqueTable) then
  begin
    RollbackTrans;
    Exit;
  end;
  ADOSetList.EnableControls;
  if lType = 2 then
  begin
    if (dARTotal <> 0) or (dDoARTotal <> 0) then
      if not UpdateUnitARTotal(1, lUnitID, GetPeriod, ADOSetMaster.FieldValues['BillDate'], dARTotal, dDoARTotal) then
      begin
        RollbackTrans;
        Exit;
      end;
  end;
  GetAutoSerial(lMode, 1);
  CommitTrans;
  lID := 0;
end;

procedure TfrmBillEdit.MoneySelectUnit(lUID, lMMode: Integer);
var
  sSql: string;
  ADOSetTmp: TADODataSet;
  function MoneyToFormal(lID: Integer): Integer;
  begin
    case lID of
      STOCK_MONEY_EDIT: Result := STOCK_FORMAL_EDIT;
      SALE_MONEY_EDIT: Result := SALE_FORMAL_EDIT;
    else
      Result := 0;
    end;
  end;
begin
  ADOSetTmp := TADODataSet.Create(nil);
  ADOSetTmp.LockType := ltReadOnly;
  case lMMode of
    STOCK_MONEY_EDIT, SALE_MONEY_EDIT:
      begin
        case lMMode of
          STOCK_MONEY_EDIT:
            sSql := ' SELECT T1.ID, T1.UserCode, T1.Name, T1.BillDate, T1.AllTotal,iif(isnull(T2.YFTotal),0,T2.YFTotal) as YFTotal,'
              +
              ' (T1.AllTotal-YFTotal) AS WFTotal FROM' +
              ' (SELECT I.ID,I.UserCode, I.Name, I.BillDate,Sum(S.Total)as AllTotal' +
              ' FROM BillIndex as I,BillStock as S' +
              ' WHERE I.ID=S.IndexID And I.UnitID=' + IntToStr(lUID) +
              ' And I.Mode=' + IntToStr(MoneyToFormal(lMMode)) +
              ' GROUP BY I.ID,I.UserCode, I.Name, I.BillDate) T1 LEFT JOIN' +
              ' (SELECT S.OtherID,Sum(S.Total)as YFTotal FROM BillStock as S GROUP BY S.OtherID) T2' +
              ' on T1.ID=T2.OtherID ' +
              ' WHERE not (T1.AllTotal<>0 and T1.AllTotal-iif(isnull(T2.YFTotal),0,T2.YFTotal)=0)';
          SALE_MONEY_EDIT:
            sSql := ' SELECT T1.ID, T1.UserCode, T1.Name, T1.BillDate, T1.AllTotal, iif(isnull(T2.YFTotal),0,T2.YFTotal) as YFTotal,'
              +
              ' (T1.AllTotal-YFTotal) AS WFTotal FROM' +
              ' (SELECT I.ID,I.UserCode, I.Name, I.BillDate,Sum(S.Total)as AllTotal' +
              ' FROM BillIndex as I,BillSale as S' +
              ' WHERE I.ID=S.IndexID And I.UnitID=' + IntToStr(lUID) +
              ' And I.Mode=' + IntToStr(MoneyToFormal(lMMode)) +
              ' GROUP BY I.ID,I.UserCode, I.Name, I.BillDate) T1 LEFT JOIN' +
              ' (SELECT S.OtherID,Sum(S.Total)as YFTotal FROM BillSale as S GROUP BY S.OtherID) T2' +
              ' on T1.ID=T2.OtherID' +
              ' WHERE not (T1.AllTotal<>0 and T1.AllTotal-iif(isnull(T2.YFTotal),0,T2.YFTotal)=0)';
        end;
        OpenDataSet(ADOSetTmp, sSql);
        if not ADOSetTmp.IsEmpty then
        begin
          ADOSetTmp.First;
          ClearDataSet(ADOSetList);
          while not ADOSetTmp.Eof do
          begin
            ADOSetList.Append;
            ADOSetList.FieldByName('OtherID').AsInteger := ADOSetTmp.FieldByName('ID').AsInteger;
            ADOSetList.FieldByName('UserCode').AsString := ADOSetTmp.FieldByName('UserCode').AsString;
            ADOSetList.FieldByName('Date').Value := ADOSetTmp.FieldByName('BillDate').Value;
            ADOSetList.FieldByName('Number').AsFloat := ADOSetTmp.FieldByName('AllTotal').AsFloat; //总金额
            ADOSetList.FieldByName('ToNumber').AsFloat := ADOSetTmp.FieldByName('YFTotal').AsFloat; //已付金额
            ADOSetList.FieldByName('UnNumber').AsFloat := ADOSetTmp.FieldByName('WFTotal').AsFloat; //未付金额
            ADOSetTmp.Next;
          end;
        end;
      end;
  end;
  ADOSetTmp.Close;
  ADOSetTmp.Free;
end;

procedure TfrmBillEdit.edtUnitButtonClick(Sender: TObject;
  AbsoluteIndex: Integer);
var
  sUnitID: string;
begin
  inherited;
  if edtUnit.ReadOnly then
    Exit;
  if (lMode >= STOCK_ORDER_EDIT) and (lMode <= STOCK_BACK_EDIT) then
    sUnitID := BaseSelect(BASE_PROVIDE, 1, edtUnit.Text)
  else
    sUnitID := BaseSelect(BASE_CLIENT, 1, edtUnit.Text);
  if (sUnitID <> '') then
  begin
    ADOSetMaster.FieldByName('UnitID').AsInteger := StrToInt2(sUnitID);
    edtUnit.Text := FieldIdToName('Unit', sUnitID, 'Name');
    MoneySelectUnit(StrToInt2(sUnitID), lMode);
  end;
end;

procedure TfrmBillEdit.edtEmployeButtonClick(Sender: TObject;
  AbsoluteIndex: Integer);
var
  sEmployeID: string;
begin
  inherited;
  if edtEmploye.ReadOnly then
    Exit;
  sEmployeID := BaseSelect(BASE_EMPLOYE, 1, edtEmploye.Text);
  if (sEmployeID <> '') then
  begin
    ADOSetMaster.FieldByName('EmployeID').AsInteger := StrToInt2(sEmployeID);