untquery.pas

数据库实习个人作品

    if ('M' = qryCustomer.FieldByName('Sex').AsString) then
      rdoSexMale.Checked := true
    else
      rdoSexFemale.Checked := true;
  end;
  btnUpDateCust.Enabled := true;  //  使修改顾客信息按钮可用
  btnQry0.Enabled := true;        //  使查询按钮可用
end;

procedure TfrmQuery.btnUpdateCustClick(Sender: TObject);
begin
  with datGoodsOrder do
  begin
    //  开始事务提交
    conGoodsOrder.BeginTrans;
    try
      qryCustomer.SQL.Text := 'SELECT  Address,Zip,Tel FROM Customer WHERE CustID=''' + Trim(cbCustID.Text) + '''';
      //qryCustomer.LockType := ltBatchOptimistic;  //  锁类型
      //qryCustomer.CursorType := ctKeyset; //  指针类型
      qryCustomer.Open;

      qryCustomer.Edit;
      qryCustomer.FieldByName('Address').AsString := Trim(edtAddress.Text);
      qryCustomer.FieldByName('Zip').AsString := Trim(edtZip.Text);
      qryCustomer.FieldByName('Tel').AsString := Trim(edtTel.Text);
      qryCustomer.Post;
      //  批更新数据表
      qryCustomer.UpdateBatch();
      //  事务提交
      conGoodsOrder.CommitTrans;
      MessageDlg('修改顾客信息成功！',mtInformation,[mbOK],0);
    except
      //  回滚事务
      conGoodsOrder.RollbackTrans;
      MessageDlg('修改顾客信息失败！',mtError,[mbOK],0);
    end;
  end;
end;

procedure TfrmQuery.btnQry0Click(Sender: TObject);
var
  beginDateStr,endDateStr : string;
begin
  beginDateStr := cbBeginYear0.Text + cbBeginMonth0.Text + cbBeginDay0.Text;
  endDateStr := cbEndYear0.Text + cbEndMonth0.Text + cbEndDay0.Text;
  if (StrToInt(beginDateStr) > StrToInt(endDateStr)) then
    MessageDlg('时间段错误！' + Chr(13) + '起始时间不能大于终止时间！',mtError,[mbOK],0)
  else begin
    QryCustInfo(Trim(cbCustID.Text),beginDateStr,endDateStr);
  end;
end;

procedure TfrmQuery.QryCustInfo(CustID: string;BeginDate:string;EndDate:string);
const
  cSqlCustInfo1 = 'SELECT A.OrderID,B.ItemNO,C.GoodsName,B.Quantity,C.Price*B.Quantity sMoney,A.State ';
  cSqlCustInfo2 = 'FROM Orders A,OrderItem B,Goods C ';
  cSqlCustInfo3 = 'WHERE A.CustID=''%s'' AND SUBSTRING(A.OrderID,1,8)>=''%s''AND SUBSTRING(A.OrderID,1,8)<=''%s'' ';
	cSqlCustInfo4 = 'AND A.OrderID=B.OrderID AND B.GoodsCode=C.GoodsCode';
var
  sumMoney : Double;
begin
  try
    grdQryCust.DataSource := souQryGrd;
    //grdQryStock.DataSource := '';
    //grdQryInStock.DataSource := '';
    //grdQryOutStock.DataSource := '';
    qryFromGrd.SQL.Clear;
    qryFromGrd.SQL.Text := cSqlCustInfo1 + cSqlCustInfo2 + Format(cSqlCustInfo3,[CustID,BeginDate,EndDate])+cSqlCustInfo4;
    qryFromGrd.Open;
    
    qryFromGrd.First;
    sumMoney := 0.0;
    while not qryFromGrd.Eof do
    begin
      sumMoney := sumMoney+qryFromGrd.FieldByName('sMoney').AsFloat;
      qryFromGrd.Next;
    end;
    labShowSum0.Caption := FloatToStr(sumMoney)+' 元';
  except
    ShowMessage('查询失败');
  end;
end;

////////////////////////////////////////////////////////////////////////////////
//  商品库存查询选项卡

procedure TfrmQuery.cbGoodsCode1Change(Sender: TObject);
const
  cSqlStock1 = 'SELECT B.GoodsCode,A.GoodsName,B.QtyStock,B.DateInStock,B.Price ';
  cSqlStock2 = 'FROM Goods A,Stock B WHERE B.GoodsCode=''%s'' AND A.GoodsCode=B.GoodsCode';
var
  sumMoney : Double;
  sumStock : integer;
begin
  try
    with datGoodsOrder do
    begin
      qryGoods.SQL.Clear;
      qryGoods.SQL.Text := 'SELECT * FROM Goods WHERE GoodsCode=''' + Trim(cbGoodsCode1.Text) + '''';
      qryGoods.Open;

      edtStockGoodsName.Text := qryGoods.FieldByName('GoodsName').AsString;
      edtUnit.Text := qryGoods.FieldByName('Unit').AsString;
      edtPrice.Text := qryGoods.FieldByName('Price').AsString;
      edtSafeStock.Text := qryGoods.FieldByName('SafeStock').AsString;
      edtQtyBuyMin.Text := qryGoods.FieldByName('QtyBuyMin').AsString;
    end;
    btnUpdateGoods.Enabled := true;  //  使修改商品信息按钮可用

    //grdQryCust.DataSource := '';
    grdQryStock.DataSource := souQryGrd;
    //grdQryInStock.DataSource := '';
    //grdQryOutStock.DataSource := '';
    qryFromGrd.SQL.Clear;
    qryFromGrd.SQL.Text := cSqlStock1 + Format(cSqlStock2,[Trim(cbGoodsCode1.Text)]);
    qryFromGrd.Open;

    qryFromGrd.First;
    sumStock := 0;
    sumMoney := 0.0;
    while not qryFromGrd.Eof do
    begin
      sumStock := sumStock + qryFromGrd.FieldByName('QtyStock').AsInteger;
      sumMoney := sumMoney + qryFromGrd.FieldByName('Price').AsFloat*qryFromGrd.FieldByName('QtyStock').AsFloat;
      qryFromGrd.Next;
    end;
    labTotalStock.Caption := IntToStr(sumStock)+' '+edtUnit.Text;
    labShowSum1.Caption := FloatToStr(sumMoney)+' 元';
  except
      ShowMessage('查询失败');
  end;
end;

procedure TfrmQuery.btnUpdateGoodsClick(Sender: TObject);
begin
  with datGoodsOrder do
  begin
    //  开始事务提交
    conGoodsOrder.BeginTrans;
    try
      qryGoods.SQL.Text := 'SELECT  GoodsName,Unit,Price,SafeStock,QtyBuyMin FROM Goods WHERE GoodsCode=''' + Trim(cbGoodsCode1.Text)+'''';
      qryGoods.Open;

      qryGoods.Edit;
      qryGoods.FieldByName('GoodsName').AsString := Trim(edtStockGoodsName.Text);
      qryGoods.FieldByName('Unit').AsString := Trim(edtUnit.Text);
      qryGoods.FieldByName('Price').AsCurrency := StrToFloat(Trim(edtPrice.Text));
      qryGoods.FieldByName('SafeStock').AsCurrency := StrToInt(Trim(edtSafeStock.Text));
      qryGoods.FieldByName('QtyBuyMin').AsCurrency := StrToInt(Trim(edtQtyBuyMin.Text));
      qryGoods.Post;
      //  批更新数据表
      qryGoods.UpdateBatch();
      //  事务提交
      conGoodsOrder.CommitTrans;
      MessageDlg('修改商品信息成功！',mtInformation,[mbOK],0);
    except
      //  回滚事务
      conGoodsOrder.RollbackTrans;
      MessageDlg('修改商品信息失败！',mtError,[mbOK],0);
    end;
  end;
end;

////////////////////////////////////////////////////////////////////////////////
//  商品入库查询选项卡

procedure TfrmQuery.cbGoodsCode2Change(Sender: TObject);
begin
  with datGoodsOrder do
  begin
    qryGoods.SQL.Clear;
    qryGoods.SQL.Text := 'SELECT GoodsName FROM Goods WHERE GoodsCode='''+ Trim(cbGoodsCode2.Text)+'''';
    qryGoods.Open;
    edtInStockGoodsName.Text := qryGoods.FieldByName('GoodsName').AsString;
  end;
  btnQry2.Enabled := true;
end;

procedure TfrmQuery.btnQry2Click(Sender: TObject);
const
  cSqlInStock1 = 'SELECT B.GoodsCode,A.GoodsName,B.DateInStock,B.QtyInStock,A.Unit,B.Price ';
  cSqlInStock2 = 'FROM Goods A,InStock B ';
  cSqlInStock3 = 'WHERE A.GoodsCode=B.GoodsCode AND B.GoodsCode=''%s'' AND B.DateInStock>=''%s'' AND B.DateInStock<=''%s''';
var
  beginDateStr,endDateStr : string;
  sumMoney : Double;
  sumInStock : integer;
begin
  beginDateStr := cbBeginYear2.Text + cbBeginMonth2.Text + cbBeginDay2.Text;
  endDateStr := cbEndYear2.Text + cbEndMonth2.Text + cbEndDay2.Text;
  if (StrToInt(beginDateStr) > StrToInt(endDateStr)) then
    MessageDlg('时间段错误！' + Chr(13) + '起始时间不能大于终止时间！',mtError,[mbOK],0)
  else begin
    //grdQryCust.DataSource := '';
    //grdQryStock.DataSource := '';
    grdQryInStock.DataSource := souQryGrd;
    //grdQryOutStock.DataSource := '';
    qryFromGrd.SQL.Clear;
    qryFromGrd.SQL.Text := cSqlInStock1 + cSqlInStock2
            + Format(cSqlInStock3,[Trim(cbGoodsCode2.Text),beginDateStr,endDateStr]);
    qryFromGrd.Open;
  end;
  qryFromGrd.First;
  while not qryFromGrd.Eof do
  begin
    sumInStock := sumInStock + qryFromGrd.FieldByName('QtyInStock').AsInteger;
    sumMoney := sumMoney + qryFromGrd.FieldByName('QtyInStock').AsFloat*QryFromGrd.FieldByName('Price').AsFloat;
    qryFromGrd.Next;
  end;
  labTotalInStock.Caption := IntToStr(sumInStock)+' '+qryFromGrd.FieldByName('Unit').AsString;
  labShowSum2.Caption := FloatToStr(sumMoney)+' 元';
end;

////////////////////////////////////////////////////////////////////////////////
//  商品出库查询选项卡

procedure TfrmQuery.cbGoodsCode3Change(Sender: TObject);
begin
  with datGoodsOrder do
  begin
    qryGoods.SQL.Clear;
    qryGoods.SQL.Text := 'SELECT GoodsName FROM Goods WHERE GoodsCode='''+ Trim(cbGoodsCode3.Text)+'''';
    qryGoods.Open;
    edtOutStockGoodsName.Text := qryGoods.FieldByName('GoodsName').AsString;
  end;    
  btnQry3.Enabled := true;
end;

procedure TfrmQuery.btnQry3Click(Sender: TObject);
const
  cSqlOutStock1 = 'SELECT B.GoodsCode,A.GoodsName,B.DateOutStock,B.QtyOutStock,A.Unit,B.Price ';
  cSqlOutStock2 = 'FROM Goods A,OutStock B ';
  cSqlOutStock3 = 'WHERE A.GoodsCode=B.GoodsCode AND B.GoodsCode=''%s'' AND B.DateOutStock>=''%s'' AND B.DateOutStock<=''%s''';
var
  beginDateStr,endDateStr : string;
  sumMoney : Double;
  sumOutStock : integer;
begin
  beginDateStr := cbBeginYear3.Text + cbBeginMonth3.Text + cbBeginDay3.Text;
  endDateStr := cbEndYear3.Text + cbEndMonth3.Text + cbEndDay3.Text;
  if (StrToInt(beginDateStr) > StrToInt(endDateStr)) then
    MessageDlg('时间段错误！' + Chr(13) + '起始时间不能大于终止时间！',mtError,[mbOK],0)
  else begin
    //grdQryCust.DataSource := '';
    //grdQryStock.DataSource := '';
    //grdQryInStock.DataSource := '';
    grdQryOutStock.DataSource := souQryGrd;
    qryFromGrd.SQL.Clear;
    qryFromGrd.SQL.Text := cSqlOutStock1 + cSqlOutStock2
            + Format(cSqlOutStock3,[Trim(cbGoodsCode3.Text),beginDateStr,endDateStr]);
    qryFromGrd.Open;
  end;
  qryFromGrd.First;
  while not qryFromGrd.Eof do
  begin
    sumOutStock := sumOutStock + qryFromGrd.FieldByName('QtyOutStock').AsInteger;
    sumMoney := sumMoney + qryFromGrd.FieldByName('QtyOutStock').AsFloat*QryFromGrd.FieldByName('Price').AsFloat;
    qryFromGrd.Next;
  end;
  labTotalOutStock.Caption := IntToStr(sumOutStock)+' '+qryFromGrd.FieldByName('Unit').AsString;
  labShowSum3.Caption := FloatToStr(sumMoney)+' 元';
end;

end.