dm_unit.pas

仓库管理系统 貌似是ACCESS的数据库

unit DM_Unit;

interface

uses
  SysUtils, Classes, DBXpress, DB, DBClient, SimpleDS, SqlExpr,Dialogs,Controls;

type
  TDM = class(TDataModule)
    SQLConn: TSQLConnection;
    sdsOperator: TSimpleDataSet;
    sdsUsers: TSimpleDataSet;
    dsUsers: TDataSource;
    sdsDB: TSimpleDataSet;
    sdsMaterialType: TSimpleDataSet;
    sdsUpdateDB: TSimpleDataSet;
    dsMaterialType: TDataSource;
    sdsMaxID: TSimpleDataSet;
    sdsMaterialInfo: TSimpleDataSet;
    dsMaterialInfo: TDataSource;
    dsWareInfo: TDataSource;
    sdsWareInfo: TSimpleDataSet;
    sdsVerdor: TSimpleDataSet;
    dsVendor: TDataSource;
    sdsCustomer: TSimpleDataSet;
    dsCustomer: TDataSource;
    sdsWarehouseDetail: TSimpleDataSet;
    sdsInWarehouse: TSimpleDataSet;
    dsWarehouseDetail: TDataSource;
    dsInWarehouse: TDataSource;
    sdsWarehouseInSearch: TSimpleDataSet;
    dsWarehouseInSearch: TDataSource;
    sdsWarehouseOutSearch: TSimpleDataSet;
    dsWarehouseOutSearch: TDataSource;
    sdsWarehouseSearch: TSimpleDataSet;
    dsWarehouseSearch: TDataSource;
    sdspub: TSimpleDataSet;
    sdsWarehouse: TSimpleDataSet;
    sdsOutWarehouse: TSimpleDataSet;
    sdsOutWarehouseDetail: TSimpleDataSet;
    dsOutWarehouse: TDataSource;
    dsOutWarehouseDetail: TDataSource;
    dsWare: TDataSource;
    sdsWare: TSimpleDataSet;
    sdsCheckInfo: TSimpleDataSet;
    dsCheckInfo: TDataSource;
    procedure DataModuleCreate(Sender: TObject);
  private
    str_sql: string;
    procedure Run_Query_Sql(const str_sql:string);
    procedure Run_Execute_Sql(const str_sql:string);
  public
    OperatorID,OperatorName: String;
    function CheckOperatorLogin(UName:String;UPwd:String):Boolean;      //验证用户是否有权限登陆
    procedure DataSetFirst(DataSet: TSimpleDataSet); 
    procedure DataSetPrior(DataSet: TSimpleDataSet);
    procedure DataSetNext(DataSet: TSimpleDataSet);
    procedure DataSetLast(DataSet: TSimpleDataSet);
    procedure DataSetInsert(DataSet: TSimpleDataSet);
    procedure DataSetSave(DataSet: TSimpleDataSet);
    procedure DataSetCancel(DataSet: TSimpleDataSet);
    procedure DataSetDelete(DataSet: TSimpleDataSet);
    procedure DataSetSearch(DataSet: TSimpleDataSet; sqlText: string);
    {“操作员表”}
    function OfferStaff(const AEmployeeCode:string;
                      var   APassWord:string):boolean;
    //在“操作员表”修改一条记录的口令。参数：职工编码，口令。
    procedure UpdateStaff(employeecode,password:string);
    {" 物品类别表 "}
    procedure OfferTypeID(const Asql: string;var ATypeID:string);
    {" 入库登记表 "}
    procedure OfferWarehouseInInfo(const BeginTime,Endtime,ACode:string;
                            var SumMoney:string); //入库查询
    {" 出库登记表 "}
    procedure OfferWarehouseOutInfo(const BeginTime,Endtime,ACode:string;
                            var SumMoney:string); //出库查询
    {" 库存表 "}


  end;

var
  DM: TDM;

implementation

uses OperatorLogin_Unit;

{$R *.dfm}

{ TDM }

function TDM.CheckOperatorLogin(UName, UPwd: String): Boolean;
begin
  Result:=false;
  With sdsOperator do
  begin
    Close;
    DataSet.CommandText :=  'SELECT OperatorID, OperatorName, OperatorPwd FROM WMS_Operator where OperatorID='''+UName+''' and OperatorPwd='''+UPwd+'''';
    Open;First;
    if IsEmpty then
    begin
      Result:=false;
      MessageDlg('用户名或密码不正确,拒绝登陆,请联系管理员！', mtWarning, [mbOk], 0);
    end
    else
    begin
      OperatorID:=sdsOperator.FieldByName('OperatorID').AsString;
      OperatorName:=sdsOperator.FieldByName('OperatorName').AsString;
      frmOperatorLogin.MR := True;
      Result:=true;
    end;
    Close;
  end;
end;

procedure TDM.DataModuleCreate(Sender: TObject);
begin
  {----打开数据库连接-----------------------------}
  try
    SQLConn.Connected := True;
  except
    MessageDlg('打开数据库连接时失败，请联系管理员！', mtError, [mbOk], 0);
    Exit;
  end;
end;



procedure TDM.DataSetCancel(DataSet: TSimpleDataSet);
begin
  //////判断数据集是否为新增或编辑状态并取消操作////////////////
  if DataSet.Active then
    if DataSet.State in [dsInsert, dsEdit] then
      DataSet.Cancel;
end;

procedure TDM.DataSetDelete(DataSet: TSimpleDataSet);
begin
  /////判断数据集当前记录是否可以删除//////////////////////////
  if DataSet.Active then
    if DataSet.RecordCount > 0 then
      if MessageDlg('确定删除当前记录吗?', mtWarning, [mbOk, mbCancel], 0) = mrOK then
      begin
        DataSet.Delete;
        DataSet.ApplyUpdates(-1);
      end;
end;

procedure TDM.DataSetFirst(DataSet: TSimpleDataSet);
begin
  ////将数据的记录指向第一条////////////////
  if DataSet.Active then
    if not DataSet.Bof then
      DataSet.First;
end;

procedure TDM.DataSetInsert(DataSet: TSimpleDataSet);
begin
  ////数据集新增记录//////////////////////////////////
  if DataSet.Active then
    DataSet.Append;
end;

procedure TDM.DataSetLast(DataSet: TSimpleDataSet);
begin
  ////将数据的记录指向最后一条//////////////////////
  if DataSet.Active then
    if not DataSet.Eof then
      DataSet.Last;
end;

procedure TDM.DataSetNext(DataSet: TSimpleDataSet);
begin
  ////将数据的记录指向下一条////////////////////////
  if DataSet.Active then
    if not DataSet.Eof then
      DataSet.Next;
end;

procedure TDM.DataSetPrior(DataSet: TSimpleDataSet);
begin
  ////将数据的记录指向上一条//////////////////////
  if DataSet.Active then
    if not DataSet.Bof then
      DataSet.Prior;
end;

procedure TDM.DataSetSave(DataSet: TSimpleDataSet);
begin
  ////判断数据集是否为新增或编辑状态并保存//////////
  if DataSet.Active then
    if DataSet.State in [dsInsert, dsEdit] then
      DataSet.ApplyUpdates(-1);
end;

procedure TDM.DataSetSearch(DataSet: TSimpleDataSet; sqlText: string);
begin
  with TSimpleDataSet(DataSet) do
  begin
    Close;
    DataSet.CommandText := sqlText;
    try
      Open; First;
    except
      MessageDlg('查询数据时失败！', mtError, [mbOk], 0);
    end;
  end;
end;

function TDM.OfferStaff(const AEmployeeCode: string;
  var APassWord: string): boolean;
begin
  str_sql:='select OperatorPwd '+
             'from wms_Operator '+
             'where OperatorId='''+AEmployeeCode+'''';
  Run_Query_Sql(str_sql);

  if sdsDB.RecordCount=1 then
  begin
    APassWord:=sdsDB.FieldByName('OperatorPwd').AsString;
    result:=true;
  end
  else
    result:=false;
end;

procedure TDM.OfferTypeID(const Asql: string; var ATypeID: string);
var
  temp:integer;
begin
  temp:=0;
  sdsMaxID.Close;
  sdsMaxID.DataSet.CommandText := Asql;
  sdsMaxID.Open;
  sdsMaxID.First;
  temp := sdsMaxID.RecordCount;

  ATypeID:=IntToStr(temp+1);
end;

procedure TDM.OfferWarehouseInInfo(const BeginTime, Endtime, ACode: string;
  var SumMoney: string);
var
   Temp:string;
   TempMoney:Integer;
begin
  if ACode='' then
     Temp:='INRECEIVEDATE>='''+BeginTime+''' and INRECEIVEDATE<='''+EndTime+''''
  else
     Temp:='INRECEIVEDATE>='''+BeginTime+''' and INRECEIVEDATE<='''+EndTime+
           ''' and VENDORDEPT='''+ACode+'''';
  str_sql:=' select INRECEIVEID,VENDORDEPT,WAREHOUSENAME,INRECEIVEDATE,'+
         ' INVOICENO,OPERATOR,MATERIALNAME,MATERIALMODEL,MATERIALSPECS,MATERIALUNIT,'+
         ' MATERIALPRICE,MATERIALTYPE,MATERIALNUM,MATERIALMONEY,Remark'+
         '  from WMS_InWarehouseInfo a,WMS_InWarehouseDetail  b where a.INRECEIVEID=b.InreceiveID'+
         ' and '+Temp+' '+
         ' order by INRECEIVEID,INRECEIVEDATE';

  with sdsWarehouseInSearch do
  begin
    Close;
    DataSet.CommandText := str_sql;
    Open;
  end;

  TempMoney:=0;

  with sdsWarehouseInSearch do
  begin
     first;
     while not eof do
     begin
       TempMoney:=TempMoney+fieldbyname('MATERIALMONEY').AsInteger;
       next;
     end;
  end;
  SumMoney := IntToStr(TempMoney);

end;

procedure TDM.OfferWarehouseOutInfo(const BeginTime, Endtime,
  ACode: string; var SumMoney: string);
var
   Temp:string;
   TempMoney:Integer;
begin
  if ACode='' then
     Temp:='INRECEIVEDATE>='''+BeginTime+''' and INRECEIVEDATE<='''+EndTime+''''
  else
     Temp:='INRECEIVEDATE>='''+BeginTime+''' and INRECEIVEDATE<='''+EndTime+
           ''' and VENDORDEPT='''+ACode+'''';
  str_sql:=' select INRECEIVEID,VENDORDEPT,WAREHOUSENAME,INRECEIVEDATE,'+
         ' INVOICENO,OPERATOR,MATERIALNAME,MATERIALMODEL,MATERIALSPECS,MATERIALUNIT,'+
         ' MATERIALPRICE,MATERIALTYPE,MATERIALNUM,MATERIALMONEY,Remark'+
         '  from WMS_OutWarehouseInfo a,WMS_OutWarehouseDetail  b where a.INRECEIVEID=b.InreceiveID'+
         ' and '+Temp+' '+
         ' order by INRECEIVEID,INRECEIVEDATE';

  with sdsWarehouseOutSearch do
  begin
    Close;
    DataSet.CommandText := str_sql;
    Open;
  end;

  TempMoney:=0;

  with sdsWarehouseOutSearch do
  begin
     first;
     while not eof do
     begin
       TempMoney:=TempMoney+fieldbyname('MATERIALMONEY').AsInteger;
       next;
     end;
  end;
  SumMoney := IntToStr(TempMoney);

end;

procedure TDM.Run_Execute_Sql(const str_sql: string);
begin
  sdsUpdateDB.Active:=False;
  sdsUpdateDB.DataSet.CommandText := str_sql;
  sdsUpdateDB.Execute;
end;

procedure TDM.Run_Query_Sql(const str_sql: string);
begin
  sdsDB.Active:=False;
  sdsDB.DataSet.CommandText := str_sql;
  sdsDB.open;
end;

procedure TDM.UpdateStaff(employeecode, password: string);
begin
  str_sql:='update wms_Operator set OperatorPwd='''+password+
                   ''' where OperatorID='''+employeecode+'''';
  Run_Execute_Sql(str_sql);
end;

end.