dl110.pas

利用delhpi 开发的一个关于零售业进销管理系统

    SQL.Add('AND SalesYear = :SalesYear AND SalesMonth = :SalesMonth ');
    SQL.Add('AND CustomerID = :CustomerID AND ProductID = :ProductID ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('SalesYear').AsString := LeftStr(DeliveryDate, 2);
    ParamByName('SalesMonth').AsString := SubStr(DeliveryDate, 4, 2);
    ParamByName('CustomerID').AsString := CustomerID;
    ParamByName('ProductID').AsString := ProductID;
    Open;
  end;
  if qyTemp.FieldByName('CustomerID').AsString = '' then
  begin
    with qyTemp2 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('INSERT INTO SalesAnalyst ');
      SQL.Add(' (CompanyID, SalesYear, SalesMonth, CustomerID, ProductID, ');
      SQL.Add('  SalesQuantity, SalesAmount, SalesReturnQuantity, SalesReturnAmount) ');
      SQL.Add('VALUES ');
      SQL.Add(' (:CompanyID, :SalesYear, :SalesMonth, :CustomerID, :ProductID, ');
      SQL.Add('  :SalesQuantity, :SalesAmount, :SalesReturnQuantity, :SalesReturnAmount) ');
      ParamByName('CompanyID').AsString := sCompanyID;
      ParamByName('SalesYear').AsString := LeftStr(DeliveryDate, 2);
      ParamByName('SalesMonth').AsString := SubStr(DeliveryDate, 4, 2);
      ParamByName('CustomerID').AsString := CustomerID;
      ParamByName('ProductID').AsString := ProductID;
      if DeliveryProperty = '1' then
      begin
        ParamByName('SalesQuantity').AsFloat := SalesQuantity;
        ParamByName('SalesAmount').AsFloat := SalesAmount;
        ParamByName('SalesReturnQuantity').AsFloat := 0;
        ParamByName('SalesReturnAmount').AsFloat := 0;
      end
      else
      begin
        ParamByName('SalesQuantity').AsFloat := 0;
        ParamByName('SalesAmount').AsFloat := 0;
        ParamByName('SalesReturnQuantity').AsFloat := SalesQuantity;
        ParamByName('SalesReturnAmount').AsFloat := SalesAmount;
      end;
      ExecSQL;
    end;
  end
  else
  begin
    with qyTemp2 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('UPDATE SalesAnalyst ');
      SQL.Add('SET SalesQuantity = SalesQuantity + :SalesQuantity, ');
      SQL.Add('    SalesAmount = SalesAmount + :SalesAmount, ');
      SQL.Add('    SalesReturnQuantity = SalesReturnQuantity + :SalesReturnQuantity, ');
      SQL.Add('    SalesReturnAmount = SalesReturnAmount + :SalesReturnAmount ');
      SQL.Add('WHERE CompanyID = :CompanyID ');
      SQL.Add('AND SalesYear = :SalesYear AND SalesMonth = :SalesMonth ');
      SQL.Add('AND CustomerID = :CustomerID AND ProductID = :ProductID ');
      ParamByName('CompanyID').AsString := sCompanyID;
      ParamByName('SalesYear').AsString := LeftStr(DeliveryDate, 2);
      ParamByName('SalesMonth').AsString := SubStr(DeliveryDate, 4, 2);
      ParamByName('CustomerID').AsString := CustomerID;
      ParamByName('ProductID').AsString := ProductID;
      if DeliveryProperty = '1' then
      begin
        ParamByName('SalesQuantity').AsFloat := SalesQuantity;
        ParamByName('SalesAmount').AsFloat := SalesAmount;
        ParamByName('SalesReturnQuantity').AsFloat := 0;
        ParamByName('SalesReturnAmount').AsFloat := 0;
      end
      else
      begin
        ParamByName('SalesQuantity').AsFloat := 0;
        ParamByName('SalesAmount').AsFloat := 0;
        ParamByName('SalesReturnQuantity').AsFloat := SalesQuantity;
        ParamByName('SalesReturnAmount').AsFloat := SalesAmount;
      end;
      ExecSQL;
    end;
  end;
end;

procedure TfmDL110.UpdateCustomerInventory(CustomerID, ProductID: String;
  Quantity: Extended);
begin
  with qyTemp do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT CustomerID ');
    SQL.Add('FROM CustomerInventory ');
    SQL.Add('WHERE CompanyID = :CompanyID ');
    SQL.Add('AND CustomerID = :CustomerID AND ProductID = :ProductID ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('CustomerID').AsString := CustomerID;
    ParamByName('ProductID').AsString := ProductID;
    Open;
  end;
  if qyTemp.FieldByName('CustomerID').AsString = '' then
  begin
    with qyTemp2 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('INSERT INTO CustomerInventory ');
      SQL.Add(' (CompanyID, CustomerID, ProductID, Quantity)');
      SQL.Add('VALUES ');
      SQL.Add(' (:CompanyID, :CustomerID, :ProductID, :Quantity) ');
      ParamByName('CompanyID').AsString := sCompanyID;
      ParamByName('CustomerID').AsString := CustomerID;
      ParamByName('ProductID').AsString := ProductID;
      ParamByName('Quantity').AsFloat := Quantity;
      ExecSQL;
    end;
  end
  else
  begin
    with qyTemp2 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('UPDATE CustomerInventory ');
      SQL.Add('SET Quantity = Quantity + :Quantity ');
      SQL.Add('WHERE CompanyID = :CompanyID ');
      SQL.Add('AND CustomerID = :CustomerID AND ProductID = :ProductID ');
      ParamByName('CompanyID').AsString := sCompanyID;
      ParamByName('CustomerID').AsString := CustomerID;
      ParamByName('ProductID').AsString := ProductID;
      ParamByName('Quantity').AsFloat := Quantity;
      ExecSQL;
    end;
  end;
end;

procedure TfmDL110.qyMasterCustomerIDValidate(Sender: TField);
begin
  inherited;
  with qyTemp do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT CustomerAttribName, SalesManID, DeliveryAddressID, InvoiceType ');
    SQL.Add('FROM CustomerMaster ');
    SQL.Add('WHERE CompanyID = :CompanyID ');
    SQL.Add('AND CustomerID = :CustomerID ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('CustomerID').AsString := qyMaster.FieldByName('CustomerID').AsString;
    Open;
  end;
  qyMaster.FieldByName('CustomerAttribName').AsString :=
           qyTemp.FieldByName('CustomerAttribName').AsString;
  if Trim(qyMaster.FieldByName('SalesManID').AsString) = '' then
    qyMaster.FieldByName('SalesManID').AsString :=
             qyTemp.FieldByName('SalesManID').AsString;
  if Trim(qyMaster.FieldByName('DeliveryAddressID').AsString) = '' then
    qyMaster.FieldByName('DeliveryAddressID').AsString :=
             qyTemp.FieldByName('DeliveryAddressID').AsString;
  qyMaster.FieldByName('InvoiceType').AsString :=
           qyTemp.FieldByName('InvoiceType').AsString;
end;

procedure TfmDL110.qyMasterDeliveryAddressIDValidate(Sender: TField);
begin
  inherited;
  qyMaster.FieldByName('AddressDescription').AsString :=
           GetAddressDescription(qyMaster.FieldByName('CustomerID').AsString,
                                 qyMaster.FieldByName('DeliveryAddressID').AsString);
end;

procedure TfmDL110.qyMasterSalesManIDValidate(Sender: TField);
begin
  inherited;
  qyMaster.FieldByName('ChineseName').AsString :=
           GetSalesName(qyMaster.FieldByName('SalesManID').AsString);
end;

procedure TfmDL110.qyMasterWarehouseIDValidate(Sender: TField);
begin
  inherited;
  qyMaster.FieldByName('WarehouseName').AsString :=
           GetWarehouseName(qyMaster.FieldByName('WarehouseID').AsString);
end;

procedure TfmDL110.qyDetailProductIDValidate(Sender: TField);
begin
  inherited;
  qyDetail.FieldByName('ProductName').AsString :=
           GetProductName(qyDetail.FieldByName('ProductID').AsString);
end;

procedure TfmDL110.gdDetailEditButtonClick(Sender: TObject);
begin
  inherited;
  if (gdDetail.SelectedField.FieldName = 'ProductID') and
     (qyDetail.State in [dsInsert, dsEdit]) then
    qyDetail.FieldByName('ProductID').AsString := SearchData(edProductID, DM.qyProduct);
end;

procedure TfmDL110.sbSalesManIDClick(Sender: TObject);
begin
  inherited;
  SearchData(ED4, DM.qySalesMan);
end;

procedure TfmDL110.sbCustomerIDClick(Sender: TObject);
begin
  inherited;
  SearchData(ED2, DM.qyCustomerMaster);
end;

procedure TfmDL110.sbDeliveryAddressIDClick(Sender: TObject);
begin
  inherited;
  SearchData(ED3, qyCustomerAddress);
end;

procedure TfmDL110.qyCustomerAddressBeforeOpen(DataSet: TDataSet);
begin
  inherited;
  with qyCustomerAddress do
  begin
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('CustomerID').AsString := qyMaster.FieldByName('CustomerID').AsString;
  end;
end;

procedure TfmDL110.sbWarehouseIDClick(Sender: TObject);
begin
  inherited;
  SearchData(ED5, DM.qyWarehouse);
end;

procedure TfmDL110.qyDetailAfterInsert(DataSet: TDataSet);
begin
  inherited;
  gdDetail.SelectedField := qyDetailProductID;
end;

procedure TfmDL110.qyDetailUpdateRecord(DataSet: TDataSet;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
var
  fNewSalesQuantity, fOldSalesQuantity : Extended;
  fNewSalesAnalystAmount, fOldSalesAnalystAmount : Extended;
  sNewProductID, sOldProductID : String;
begin
  inherited;
  if ReSortting then Exit;
  fNewSalesQuantity := 0;
  fOldSalesQuantity := 0;
  fNewSalesAnalystAmount := 0;
  fOldSalesAnalystAmount := 0;
  sNewProductID := '';
  sOldProductID := '';
  case UpdateKind of
    ukInsert :
      begin
        fNewSalesQuantity := DataSet.FieldByName('SalesQuantity').NewValue;
        fOldSalesQuantity := 0;
        fNewSalesAnalystAmount := DataSet.FieldByName('SalesAnalystAmount').NewValue;
        fOldSalesAnalystAmount := 0;
        sNewProductID := DataSet.FieldByName('ProductID').NewValue;
        sOldProductID := DataSet.FieldByName('ProductID').NewValue;
        with DM.qyTemp1 do
        begin
          Close;
          SQL.Clear;
          SQL.Add('UPDATE Product SET LastDeliveryDate = :LastDeliveryDate ');
          SQL.Add('WHERE CompanyID = :CompanyID AND ProductID = :ProductID ');
          ParamByName('CompanyID').AsString := sCompanyID;
          ParamByName('ProductID').AsString := DataSet.FieldByName('ProductID').AsString;
          ParamByName('LastDeliveryDate').AsString := qyMaster.FieldByName('DeliveryDate').AsString;
          ExecSQL;
        end;
      end;
    ukModify :
      begin
        fNewSalesQuantity := DataSet.FieldByName('SalesQuantity').NewValue;
        fOldSalesQuantity := DataSet.FieldByName('SalesQuantity').OldValue;
        fNewSalesAnalystAmount := DataSet.FieldByName('SalesAnalystAmount').NewValue;
        fOldSalesAnalystAmount := DataSet.FieldByName('SalesAnalystAmount').OldValue;
        sNewProductID := DataSet.FieldByName('ProductID').NewValue;
        sOldProductID := DataSet.FieldByName('ProductID').OldValue;
      end;
    ukDelete :
      begin
        fNewSalesQuantity := 0;
        fOldSalesQuantity := DataSet.FieldByName('SalesQuantity').OldValue;
        fNewSalesAnalystAmount := 0;
        fOldSalesAnalystAmount := DataSet.FieldByName('SalesAnalystAmount').OldValue;
        sNewProductID := DataSet.FieldByName('ProductID').OldValue;
        sOldProductID := DataSet.FieldByName('ProductID').OldValue;
      end;
  end;
  if (sPrevDeliveryProperty = '1') or (sPrevDeliveryProperty = '2') then
  begin
    UpdateSalesAnalyst(sPrevDeliveryDate, sPrevCustomerID,
                       sOldProductID, sPrevDeliveryProperty,
                       (fOldSalesQuantity * -1),
                       (fOldSalesAnalystAmount * -1));
    UpdateSalesAnalyst(sPrevDeliveryDate, sPrevCustomerID,
                       sNewProductID, sPrevDeliveryProperty,
                       fNewSalesQuantity,
                       fNewSalesAnalystAmount);
  end;
  if (sPrevDeliveryProperty = '3') then
  begin
    UpdateCustomerInventory(sPrevCustomerID, sOldProductID,
                            (fOldSalesQuantity * -1));
    UpdateCustomerInventory(sPrevCustomerID, sNewProductID,
                            fNewSalesQuantity);
  end;
  if (sPrevDeliveryProperty = '4') then
  begin
    UpdateCustomerInventory(sPrevCustomerID, sOldProductID,
                            fOldSalesQuantity);
    UpdateCustomerInventory(sPrevCustomerID, sNewProductID,
                            (fNewSalesQuantity * -1));
  end;
  UpdateInventoryAnalyst(sPrevDeliveryDate, sOldProductID,
                         sPrevWarehouseID, '0' + sPrevDeliveryProperty,
                         (fOldSalesQuantity * -1),
                         (fOldSalesAnalystAmount * -1));
  UpdateInventoryAnalyst(sPrevDeliveryDate, sNewProductID,
                         sPrevWarehouseID, '0' + sPrevDeliveryProperty,
                         fNewSalesQuantity,
                         fNewSalesAnalystAmount);
end;

procedure TfmDL110.GetPrevValue;
begin
  inherited;
  sPrevDeliveryDate := qyMaster.FieldByName('DeliveryDate').AsString;
  sPrevCustomerID := qyMaster.FieldByName('CustomerID').AsString;
  sPrevWarehouseID := qyMaster.FieldByName('WarehouseID').AsString;
  sPrevDeliveryProperty := qyMaster.FieldByName('DeliveryProperty').AsString;
end;

procedure TfmDL110.qyMasterUpdateRecord(DataSet: TDataSet;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
var
  fNewAmount, fOldAmount : Extended;
  sLastDeliveryDate, sCustomerID : String;
begin
  inherited;
  fNewAmount := 0;
  fOldAmount := 0;
  sLastDeliveryDate := '';
  case UpdateKind of
    ukInsert :
      begin
        fNewAmount := DataSet.FieldByName('AccountReceivable').NewValue;
        fOldAmount := 0;
        sLastDeliveryDate := DataSet.FieldByName('DeliveryDate').NewValue;
        sCustomerID := DataSet.FieldByName('CustomerID').NewValue;
      end;
    ukModify :
      begin
        fNewAmount := DataSet.FieldByName('AccountReceivable').NewValue;
        fOldAmount := DataSet.FieldByName('AccountReceivable').OldValue;
        sLastDeliveryDate := DataSet.FieldByName('DeliveryDate').OldValue;
        sCustomerID := DataSet.FieldByName('CustomerID').OldValue;
      end;
    ukDelete :
      begin
        fNewAmount := DataSet.FieldByName('AccountReceivable').OldValue;
        fOldAmount := DataSet.FieldByName('AccountReceivable').OldValue;
        sLastDeliveryDate := DataSet.FieldByName('DeliveryDate').OldValue;
        sCustomerID := DataSet.FieldByName('CustomerID').OldValue;
      end;
  end;
  with qyTemp do
  begin
    Close;
    SQL.Clear;
    SQL.Add('UPDATE CustomerMaster SET ');
    SQL.Add('LastDeliveryDate = :LastDeliveryDate, ');
    SQL.Add('CreditBalance = CreditBalance + :CreditBalance ');
    SQL.Add('WHERE CompanyID = :CompanyID AND CustomerID = :CustomerID ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('CustomerID').AsString := sCustomerID;
    ParamByName('LastDeliveryDate').AsString := sLastDeliveryDate;
    ParamByName('CreditBalance').AsFloat := fOldAmount - fNewAmount;
    ExecSQL;
  end;
end;

procedure TfmDL110.sbSelectClick(Sender: TObject);
begin
  inherited;
  ReData := False;
  qyMaster.DisableControls;
  try
    fmDL110Select := TfmDL110Select.Create(Application);
    fmDL110Select.ShowModal;
    if ReData then begin
      try
        fmLoading := TfmLoading.Create(Application);
        fmLoading.Show;
        fmLoading.Update;
        qyMaster.Close;
        qyMaster.Open;
      finally
        fmLoading.Hide;
        fmLoading.Free;
      end;
    end;
  finally
    qyMaster.EnableControls;
    fmDL110Select.Free;
  end;
end;

procedure TfmDL110.sbReportClick(Sender: TObject);
begin
  inherited;
  DL110Print(qyMaster.FieldByName('DeliveryID').AsString);
end;

end.