storecancel.pas

物流管理系统是一个典型的数据库应用程序

unit storecancel;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, instore, DB, DBGrids, StdCtrls, Buttons, ComCtrls, Grids,
  ExtCtrls,ADODB;

type
  Tf_storecancel = class(Tf_instore)
    procedure SaveClick(Sender: TObject); override;
    procedure ReginfoSetEditText(Sender: TObject; ACol, ARow: Integer;
      const Value: String); override;
    procedure ReginfoKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState); override;
    procedure FormCreate(Sender: TObject); override;
  private
    { Private declarations }
  public
    Function CurIsExit(index: Integer): Boolean;//判断表格中当前行在其后面的行中是否存在
    Function StoreIsExis: Boolean;//判断物资在库存中是否存在
    Function Calculatenum(index: Integer): Real;//在当前行统计物资数量
    Function CheckStoreEnough: Boolean;//判断表格中的物资在库存中是否充足
    { Public declarations }
  end;

var
  f_storecancel: Tf_storecancel;

implementation
  uses data;
{$R *.dfm}
//在当前行统计物资数量
function Tf_storecancel.Calculatenum(index: Integer): Real;
var
  i: integer;
begin
  Result := 0.0;
  for i := 1 to Index do
  begin
    if (Trim(Reginfo.Cells[storename,index])= Trim(Reginfo.Cells[storename,i]))and(Trim(Reginfo.Cells[storagename,index])=Trim(Reginfo.Cells[storagename,i]))then
      Result := Result + StrToFloat(Reginfo.Cells[num,i]);
  end;
end;
//判断表格中的物资在库存中是否充足
function Tf_storecancel.CheckStoreEnough: Boolean;
var
  i: Integer;
  Storenum: Real;
begin
  Result := True;
  for i := 1 to Reginfo.RowCount-1 do
  begin
    if CurIsExit(i)= False then  //判断表格中当前行物资在其后面的行中是否存在
    begin
      Storenum :=  Calculatenum(i);
      with t_data.Query1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select storesum from tb_comstorage where storagename = :storagename and storename = :storename');
        Parameters.ParamByName('storagename').Value := Trim(Reginfo.Cells[storagename,i]);
        Parameters.ParamByName('Storename').Value := Trim(Reginfo.Cells[storename,i]);
        Open;
      end;
      if t_data.Query1.RecordCount<1 then
      begin
        Result := False;
        Application.MessageBox(Pchar(Reginfo.Cells[storename,i]+' 在库存中没有足够的数量.'),'提示',64);
        Exit;
      end;
      if t_data.Query1.Fields[0].Value < Storenum then
      begin
        Result := False;
        Application.MessageBox(Pchar(Reginfo.Cells[storename,i]+' 在库存中没有足够的数量.'),'提示',64);
        Exit;
      end;
    end;
  end;
end;
//判断表格中当前行在其后面的行中是否存在
function Tf_storecancel.CurIsExit(index: Integer): Boolean;
var
  i: Integer;
begin
  Result := False;
  if index = Reginfo.RowCount-1 then
  begin
    Result := False;
    Exit;
  end;
  For i := index+1 to Reginfo.RowCount-1 do
  begin
    if (Trim(Reginfo.Cells[storename,index])= Trim(Reginfo.Cells[storename,i]))and(Trim(Reginfo.Cells[storagename,index])=Trim(Reginfo.Cells[storagename,i]))then
    begin
      Result := True;
      Break;
    end;
  end;
end;
//处理保存按钮的单击事件
procedure Tf_storecancel.SaveClick(Sender: TObject);
var
  Connect1: TADOConnection;
  Query,Query1: TADOQuery;
  x: Integer;
begin
  Connect1 := nil;
  Query := nil;
  Query1 := nil;
  if EditIsNull = True then //判断编辑框文本是否为空
  begin
    Application.MessageBox('信息不能为空.','提示',64);
    Exit;
  end;
  if EndRowIsNull= True then  //判断最后一行是否为空
  begin
    if Reginfo.RowCount>2 then
    begin
      ClearEndRow; //清空最后一行
      Reginfo.RowCount := Reginfo.RowCount-1; //删除最后一行
    end;
  end;
  if GridIsNull = True then  //表格是否为空
  begin
    Application.MessageBox('表格中数据不能为空.','提示',64);
    Exit;
  end;
  if StoreIsExis = True then //物资是否存在
  begin
    if CheckStoreEnough = True then //物资是否充足
    begin
      Try
        //动态创建TADOConnection组件,用于进行事务控制
        Connect1 := TADOConnection.Create(nil);
        Connect1.ConnectionString := Trim(t_data.Connection1.ConnectionString);
        Connect1.LoginPrompt := False; //取消登录窗口
        Connect1.Connected := True;
        Query := TADOQuery.Create(nil);
        Query.Connection := Connect1;
        Query1 := TADOQuery.Create(nil);
        Query1.Connection := Connect1;
        Try
          Connect1.BeginTrans; //开始一个事务
          With Query do
          begin
            CLose;
            SQL.Clear;
            //利用存储过程生成退货票号,保存退货主表信息
            SQL.Add('Exec Add_storeCancel :providername,:principal,:operator,:total,:rebate,:paymoney,:factmoney,:date, :storeid output');
            Parameters.ParamByName('providername').Value := Trim(Providername.Text);
            Parameters.ParamByName('principal').Value := Trim(Principal.Text);
            Parameters.ParamByName('operator').Value := Trim(Operator.Text);
            Parameters.ParamByName('total').Value := StrToFloat(Stocktotal.Text);
            Parameters.ParamByName('rebate').Value := StrToFloat(Rebate.Text);
            Parameters.ParamByName('paymoney').Value := StrToFloat(paymoney.Text);
            Parameters.ParamByName('factmoney').Value := StrToFloat(Factmoney.Text);
            Parameters.ParamByName('date').Value := Trunc(Date.DateTime);
            Parameters.ParamByName('storeid').Value := 'temporary';
            ExecSQL;
          end;
          for x := 1 to Reginfo.RowCount-1 do  //利用循环向明细表中插入数据,修改物资库存
          begin
            With Query1 do
            begin
              CLose;
              SQL.Clear;
              SQL.Add('insert into tb_storecancellist values(:cancelid,:storagename,:storename,:unitprice,:storesum,:cancelmoney)');
              Parameters.ParamByName('cancelid').Value := Trim(query.Parameters.ParamByName('storeid').Value);
              Parameters.ParamByName('storagename').Value := Trim(Reginfo.Cells[storagename,x]);
              Parameters.ParamByName('storesum').Value := StrToFloat(Reginfo.Cells[num,x]);
              Parameters.ParamByName('storename').Value := Trim(Reginfo.Cells[storename,x]);
              Parameters.ParamByName('unitprice').Value := StrToFloat(Reginfo.Cells[unitprice,x]);
              Parameters.ParamByName('cancelmoney').Value := StrToFloat(Reginfo.Cells[money,x]);
              ExecSQL;
            end;
            With Query1 do
            begin
              CLose;
              SQL.Clear;
              SQL.Add('update tb_comstorage set storesum = storesum - :storesum where storagename = :storagename and storename = :storename');
              Parameters.ParamByName('storagename').Value := Trim(Reginfo.Cells[storagename,x]);
              Parameters.ParamByName('storename').Value := Trim(Reginfo.Cells[storename,x]);
              Parameters.ParamByName('storesum').Value := StrToFloat(Reginfo.Cells[num,x]);
              ExecSQL;
            end;
          end;
          //修改供应商结款信息
          With query1 do
          begin
            CLose;
            SQL.Clear;
            SQL.Add('update tb_providerpayment set paymoney = paymoney -:paymoney where providername = :providername');
            Parameters.ParamByName('paymoney').Value := StrToFloat(Paymoney.Text)-StrToFloat(Factmoney.Text);
            Parameters.ParamByName('providername').Value := Trim(Providername.Text);
            ExecSQL;
          end;
          Connect1.CommitTrans; //提交事务
          Application.MessageBox(Pchar('操作成功,票号为: '+Trim(query.Parameters.ParamByName('storeid').Value)),'提示',64);
          Cancel.Click;
        Except
          Connect1.RollbackTrans; //产生异常则回滚事务
          Application.MessageBox('操作失败.','提示',64);
        End;
      Finally
        Connect1.Free;
        Query.Free;
        Query1.Free;
      End;
    end;
  end;
end;
//自定义函数,判断物资是否存在,如果存在,返回值为True,否则为False
function Tf_storecancel.StoreIsExis: Boolean;
var
  i: Integer;
begin
  Result := True;
  for i := 1 to Reginfo.RowCount-1 do
  begin
    with t_data.Query1 do
    begin