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.