unit ModuleLogistique_RechercheClasses;

interface

uses System.SysUtils, System.Classes,
  Data.DB,
  xxxx.DB, xxxx.DB.Entity,
  xxxx.Entity.Colis, xxxx.Business.Colis,
  ModuleLogistique_Classes,
  ModuleLogistique_ReceptionClasses;

type
  { Forward class declarations }
  TModuleLogistiqueRechercheColis = class;

  /// <summary>Erreur de base liée à la reception de colis</summary>
  EModuleLogistiqueRechercheColisError = class(Exception);

  /// <summary>Moteur de gestion de reception de colis</summary>
  TModuleLogistiqueRechercheColis = class(TObject)
  public
    // Constantes publiques
    const
      COL_NATURE_COLIS_LIB_ALIAS = 'F_NATURE_LIB';
      COL_MAG_SRC_ALIAS = 'F_SOURCE';
      COL_MAG_DEST_ALIAS = 'F_DESTINATION';
      COL_MAG_RECEPT_ALIAS = 'F_RECEPTION';
      COL_HIGHLIGHT_ALIAS = 'F_HIGHLIGHT';
      COL_TRANSPORT_ALIAS = 'F_TRANSPORTEUR';
    // Types publiques
    type
      TSearchType = (stNone, stPick, stColisXYZ, stDate);
      TSearchDateType = (sdtNone, sdtPreReception, sdtOuverture, sdtReceptionColis, sdtRemiseTransporteur);
      TSearchString = string[15]; // 9 + 5 Zéros pour un colis scanné, 12 + 1 Zéro pour un bordereau scanné
      TSearchCriteria = record
      public
        class function Create(ASearchType: TSearchType; const ANumero: string): TSearchCriteria; overload; static;
        class function Create(ASearchDateType: TSearchDateType; ADate: TDate): TSearchCriteria; overload; static;
      public
        MagasinOrigine: string;
        MagasinDestination: string;
        MagasinReception: string;
        case SearchType: TSearchType of
          stPick: (NumeroBordereau: TSearchString);
          stColisXYZ: (NumeroColis: TSearchString);
          stDate: (DateValue: TDate; DateType: TSearchDateType);
      end;

  private
    // Membres privés - Entrée\Sortie
    FSearchResult: TxxxxDBQuery;
    FExpeditionColis: TxxxxEntityExpeditionColis;
    FColis: TxxxxEntityColis;
    FSearchCriteria: TSearchCriteria;
    FLastSearchCriteria: TSearchCriteria;

    // Méthodes privées
    procedure MakeSearchQuery(ASearchCriteria: TSearchCriteria);
    function LoadBordereauQuery(): TxxxxDBQuery;
    function LoadColisQuery(): TxxxxDBQuery;
    procedure CriteriaToParam(AQuery: TxxxxDBQuery);
    function NearSearch(ACriteriaOne, ACriteriaTwo: TSearchCriteria): Boolean;

    // Accesseurs
    function GetColisDuBorderau(): TxxxxEntityColis;
    function GetExpeditionColis(): TxxxxEntityExpeditionColis;
  public
    // Constructeurs d'Instance
    destructor Destroy(); override;

    // Méthodes publiques
    function Search(ASearchCriteria: TSearchCriteria): Boolean;

    procedure ScrollColisOfBordereau();
    procedure ScrollBordereauOfColis();
    function IsScrolledBordereauAndColis(): Boolean;

    // Propriétés - Entrée\Sortie
    property ExpeditionColis: TxxxxEntityExpeditionColis read GetExpeditionColis;
    property ColisDuBorderau: TxxxxEntityColis read GetColisDuBorderau;
  end;

implementation

uses
  System.DateUtils;

{ TModuleLogistiqueRechercheColis }

//------------------------------------------------------------------------------
procedure TModuleLogistiqueRechercheColis.CriteriaToParam(AQuery: TxxxxDBQuery);
begin
  if FSearchCriteria.SearchType = stPick then
    AQuery.ParamByName('pAWB').Value := FSearchCriteria.NumeroBordereau;
  if (FSearchCriteria.SearchType = stDate) and (FSearchCriteria.DateType = sdtPreReception) then
    AQuery.ParamByName('pDatePreReception').Value := DateOf(FSearchCriteria.DateValue);
  if (FSearchCriteria.SearchType = stDate) and (FSearchCriteria.DateType = sdtOuverture) then
    AQuery.ParamByName('pDateOuverture').Value := DateOf(FSearchCriteria.DateValue);
  if FSearchCriteria.SearchType = stColisXYZ then
    AQuery.ParamByName('pNumColisXYZ').Value := FSearchCriteria.NumeroColis;
  if (FSearchCriteria.SearchType = stDate) and (FSearchCriteria.DateType = sdtReceptionColis) then
    AQuery.ParamByName('pDateReception').Value := DateOf(FSearchCriteria.DateValue);
  if (FSearchCriteria.SearchType = stDate) and (FSearchCriteria.DateType = sdtRemiseTransporteur) then
    AQuery.ParamByName('pDateTransporteur').Value := DateOf(FSearchCriteria.DateValue);
  if FSearchCriteria.MagasinOrigine <> '' then
    AQuery.ParamByName('pMagasinOrigine').Value := FSearchCriteria.MagasinOrigine;
  if FSearchCriteria.MagasinDestination <> '' then
    AQuery.ParamByName('pMagasinDestination').Value := FSearchCriteria.MagasinDestination;
  if (FSearchCriteria.SearchType <> stColisXYZ) and (FSearchCriteria.MagasinReception <> '') then
    AQuery.ParamByName('pMagasinReception').Value := FSearchCriteria.MagasinReception;
end;

//------------------------------------------------------------------------------
destructor TModuleLogistiqueRechercheColis.Destroy();
begin
  FreeAndNil(FSearchResult);

  FreeAndNil(FExpeditionColis);
  FreeAndNil(FColis);

  inherited Destroy();
end;

//------------------------------------------------------------------------------
function TModuleLogistiqueRechercheColis.GetColisDuBorderau(): TxxxxEntityColis;
begin
  if Assigned(FSearchResult) then
  begin
    if not Assigned(FColis) then
    begin
      FColis := TxxxxEntityColis.CreateAsSet(nil);
      FColis.ReadOnly := True;
      FColis.OwnsQuery := True;
    end;

    if not Assigned(FColis.Query) then
      FColis.Query := LoadColisQuery();
    Result := FColis;
  end
  else
    raise EModuleLogistiqueRechercheColisError.Create('Pas de recherche active');
end;

//------------------------------------------------------------------------------
function TModuleLogistiqueRechercheColis.GetExpeditionColis(): TxxxxEntityExpeditionColis;
begin
  if Assigned(FSearchResult) then
  begin
    if not Assigned(FExpeditionColis) then
    begin
      FExpeditionColis := TxxxxEntityExpeditionColis.CreateAsSet(nil);
      FExpeditionColis.ReadOnly := True;
      FExpeditionColis.OwnsQuery := True;
    end;

    if not Assigned(FExpeditionColis.Query) then
      FExpeditionColis.Query := LoadBordereauQuery();
    Result := FExpeditionColis;
  end
  else
    raise EModuleLogistiqueRechercheColisError.Create('Pas de recherche active');
end;

//------------------------------------------------------------------------------
function TModuleLogistiqueRechercheColis.IsScrolledBordereauAndColis(): Boolean;
begin
  if Assigned(FExpeditionColis) and Assigned(FColis) and Assigned(FExpeditionColis.DataSet) and Assigned(FColis.DataSet) and not FExpeditionColis.DataSet.ControlsDisabled and not FColis.DataSet.ControlsDisabled then
    Result := FColis.DataSet.FieldByName('EXP_N_ID').AsFloat = FExpeditionColis.DataSet.FieldByName('EXP_N_ID').AsFloat
  else
    Result := False;
end;

//------------------------------------------------------------------------------
function TModuleLogistiqueRechercheColis.LoadBordereauQuery(): TxxxxDBQuery;
const
  SQL_SELECT_CLAUSE_FIELDS = '[SelectClauseFields]';
  SQL_SELECT_BASE =
    ' SELECT ' + SQL_SELECT_CLAUSE_FIELDS + ', ' +
    '        ms.MAG_CH_NOM AS ' + COL_MAG_SRC_ALIAS + ', ' +
    '        md.MAG_CH_NOM AS ' + COL_MAG_DEST_ALIAS + ', ' +
    '        mr.MAG_CH_NOM AS ' + COL_MAG_RECEPT_ALIAS + ', ' +
    '        trp.TRP_CH_LIBELLE AS ' + COL_TRANSPORT_ALIAS + ', ' +
    '        CASE WHEN (:pAWB IS NOT NULL AND ec.EXP_CH_AWB = :pAWB) ' +
    '               OR (:pDatePreReception IS NOT NULL AND TRUNC(ec.EXP_DT_RECEPTION) = :pDatePreReception) ' +
    '               OR (:pDateOuverture IS NOT NULL AND TRUNC(ec.EXP_DT_OUVERTURE) = :pDateOuverture) THEN 1 ELSE 0 END AS ' + COL_HIGHLIGHT_ALIAS +
    ' FROM T_EXPEDITIONCOLIS ec ' +
    ' LEFT JOIN T_MAGASINXY ms ON ms.MAG_CH_CODE = ec.MAG_CH_SRC ' +
    ' LEFT JOIN T_MAGASINXY md ON md.MAG_CH_CODE = ec.MAG_CH_DST ' +
    ' LEFT JOIN T_MAGASINXY mr ON mr.MAG_CH_CODE = ec.EXP_CH_MAG_RECEPT ' +
    ' LEFT JOIN T_TRANSPORTEUR trp ON trp.TRP_N_TRANSPORT = ec.TRP_N_TRANSPORT ' ;
  SQL_SELECT_WHERE =
    ' WHERE ec.EXP_N_ID IN (%s) ' ;
  SQL_SELECT_ORDER =
    ' ORDER BY ec.EXP_CH_AWB ' ;
var
  SQL: string;
begin
  SQL := SQL_SELECT_BASE;
  SQL := StringReplace(SQL, SQL_SELECT_CLAUSE_FIELDS, TxxxxBusinessExpeditionColis.MakeSQLSelectClauseFields(TxxxxBusinessExpeditionColis.xxxxEntity, 'ec'), [])
    + Format(SQL_SELECT_WHERE, [FSearchResult.SQLText])
    + SQL_SELECT_ORDER;

  Result := TxxxxDBQuery.Create(TModuleLogistique.Instance.DBConnection);
  try
    Result.SQLText := SQL;
    CriteriaToParam(Result);
    Result.Open();
  except
    on E: Exception do
      FreeAndNil(Result);
  end;
end;

//------------------------------------------------------------------------------
function TModuleLogistiqueRechercheColis.LoadColisQuery(): TxxxxDBQuery;
const
  SQL_JOIN_TYPE_EXPEDITION = '[JoinTypeExpedition]';
  SQL_SELECT_CLAUSE_FIELDS = '[SelectClauseFields]';
  SQL_SELECT_BASE =
    ' SELECT ec.EXP_N_ID, ' + SQL_SELECT_CLAUSE_FIELDS + ', ' +
    '        ms.MAG_CH_NOM AS ' + COL_MAG_SRC_ALIAS + ', ' +
    '        md.MAG_CH_NOM AS ' + COL_MAG_DEST_ALIAS + ', ' +
    '        ctpnc.NAT_CH_LIBELLE AS ' + COL_NATURE_COLIS_LIB_ALIAS + ', ' +
    '        CASE WHEN (:pNumColisXYZ IS NOT NULL AND c.COL_N_NUMCOLIS = :pNumColisXYZ) ' +
    '               OR (:pDateReception IS NOT NULL AND TRUNC(c.COL_DT_RECEPTION) = :pDateReception) THEN 1 ELSE 0 END AS ' + COL_HIGHLIGHT_ALIAS +
    ' FROM T_EXPEDITIONCOLIS ec ' 
    ' INNER JOIN T_COLIS_EXPEDIE ce ON ce.EXP_N_ID = ec.EXP_N_ID ' +
    SQL_JOIN_TYPE_EXPEDITION + ' JOIN T_COLIS c ON c.COL_N_NUMCOLIS = ce.COL_N_NUMCOLIS ' +
    ' LEFT JOIN TP_ETATCOLIS ctpec ON ctpec.COL_C_ETATCOLIS = c.COL_C_ETATCOLIS ' +
    ' LEFT JOIN TP_TYPECOLIS ctptc ON ctptc.COL_C_TYPE = c.COL_C_TYPE ' +
    ' LEFT JOIN TP_NATURECOLIS ctpnc ON ctpnc.COL_N_NATURE = c.COL_N_NATURE ' +
    ' LEFT JOIN T_MAGASINXY ms ON ms.MAG_CH_CODE = c.MAG_CH_CODE_DEPART ' +
    ' LEFT JOIN T_MAGASINXY md ON md.MAG_CH_CODE = c.MAG_CH_CODE ' ;
  SQL_SELECT_WHERE =
    ' WHERE ec.EXP_N_ID IN (%s) ' ;
  SQL_SELECT_WHERE_COLIS =
    ' WHERE c.COL_N_NUMCOLIS = :pNumColisXYZ ' ;
  SQL_SELECT_ORDER =
    ' ORDER BY ec.EXP_CH_AWB ' ;
var
  SQL, ClauseWhere: string;
  ExpeditionJoinType: string;
begin
  SQL := SQL_SELECT_BASE

  if FSearchCriteria.SearchType = stColisXYZ then
  begin
    ExpeditionJoinType := 'RIGHT OUTER';
    ClauseWhere := SQL_SELECT_WHERE_COLIS;
  end
  else
  begin
    ExpeditionJoinType := 'INNER';
    ClauseWhere := Format(SQL_SELECT_WHERE, [FSearchResult.SQLText]);
  end;

  SQL := StringReplace(SQL, SQL_JOIN_TYPE_EXPEDITION, ExpeditionJoinType, []);
  SQL := StringReplace(SQL, SQL_SELECT_CLAUSE_FIELDS, TxxxxBusinessColis.MakeSQLSelectClauseFields(TxxxxBusinessColis.xxxxEntity, 'c'), [])
    + ClauseWhere
    + SQL_SELECT_ORDER;

  Result := TxxxxDBQuery.Create(TModuleLogistique.Instance.DBConnection);
  try
    Result.SQLText := SQL;
    CriteriaToParam(Result);
    Result.Open();
  except
    on E: Exception do
      FreeAndNil(Result);
  end;
end;


//------------------------------------------------------------------------------
procedure TModuleLogistiqueRechercheColis.MakeSearchQuery(ASearchCriteria: TSearchCriteria);
const
  SQL_JOIN_TYPE_EXPEDITION = '[JoinTypeExpedition]';
  SQL_SELECT_BASE =
    ' SELECT s_ec.EXP_N_ID ' +
    ' FROM T_EXPEDITIONCOLIS s_ec ' +
    ' INNER JOIN T_COLIS_EXPEDIE s_ce ON s_ce.EXP_N_ID = s_ec.EXP_N_ID ' +
    SQL_JOIN_TYPE_EXPEDITION + ' JOIN T_COLIS s_c ON s_c.COL_N_NUMCOLIS = s_ce.COL_N_NUMCOLIS ' ;

  // UPPER = INSENSITIVE CASE : NLS_COMP en LINGUISTIC est trop risqué car impacte toute la session !
  SQL_WHERE_NUMEROS: array[stNone..stColisXYZ] of string =
    ( ' WHERE 1 = 1 ', ' WHERE UPPER(s_ec.EXP_CH_AWB) = UPPER(:pAWB) ' , ' WHERE s_c.COL_N_NUMCOLIS = :pNumColisXYZ ' );
  SQL_WHERE_DATES: array[Succ(Low(TSearchDateType))..High(TSearchDateType)] of string =
    ( ' WHERE TRUNC(s_ec.EXP_DT_RECEPTION) = :pDatePreReception ' ,
      ' WHERE TRUNC(s_ec.EXP_DT_OUVERTURE) = :pDateOuverture ' ,
      ' WHERE TRUNC(s_c.COL_DT_RECEPTION) = :pDateReception ',
      ' WHERE TRUNC(s_ec.EXP_DT_REMISETRP) = :pDateTransporteur '
    );

  SQL_FILTER_SRC = ' AND ( s_ec.MAG_CH_SRC = :pMagasinOrigine OR s_c.MAG_CH_CODE_DEPART = :pMagasinOrigine ) ' ;
  SQL_FILTER_DST = ' AND ( s_ec.MAG_CH_DST = :pMagasinDestination OR s_c.MAG_CH_CODE = :pMagasinDestination ) ' ;
  SQL_FILTER_RECEPT = ' AND ( s_ec.EXP_CH_MAG_RECEPT = :pMagasinReception ) ' ;

var
  SQL: string;
  ExpeditionJoinType: string;
begin
  FSearchCriteria := ASearchCriteria;

  if not NearSearch(FSearchCriteria, FLastSearchCriteria) then
    FreeAndNil(FSearchResult);

  if not Assigned(FSearchResult) then
  begin
    FSearchResult := TxxxxDBQuery.Create(TModuleLogistique.Instance.DBConnection);

    SQL := SQL_SELECT_BASE

    if FSearchCriteria.SearchType = stColisXYZ then
      ExpeditionJoinType := 'RIGHT OUTER'
    else
      ExpeditionJoinType := 'INNER';

    SQL := StringReplace(SQL, SQL_JOIN_TYPE_EXPEDITION, ExpeditionJoinType, []);

    if FSearchCriteria.SearchType = stDate then
    begin
      if FSearchCriteria.DateType in [Low(SQL_WHERE_DATES)..High(SQL_WHERE_DATES)] then
      begin
        SQL := SQL + SQL_WHERE_DATES[FSearchCriteria.DateType];
      end
      else
        raise EModuleLogistiqueRechercheColisError.Create('Type de recherche avec date inconnu');
    end
    else
    begin
      if FSearchCriteria.SearchType in [Low(SQL_WHERE_NUMEROS)..High(SQL_WHERE_NUMEROS)] then
        SQL := SQL + SQL_WHERE_NUMEROS[FSearchCriteria.SearchType]
      else
        raise EModuleLogistiqueRechercheColisError.Create('Type de recherche inconnu');
    end;

    if FSearchCriteria.MagasinOrigine <> '' then
      SQL := SQL + SQL_FILTER_SRC;
    if FSearchCriteria.MagasinDestination <> '' then
      SQL := SQL + SQL_FILTER_DST;
    if (FSearchCriteria.SearchType <> stColisXYZ) and (FSearchCriteria.MagasinReception <> '') then
      SQL := SQL + SQL_FILTER_RECEPT;

    FSearchResult.SQLText := SQL;
    FSearchResult.Prepared := True;
  end
  else
    FSearchResult.Close();

  try
    CriteriaToParam(FSearchResult);
    FSearchResult.Open();
    FLastSearchCriteria := FSearchCriteria;
  except
    on E: Exception do
    begin
      FreeAndNil(FSearchResult);
      FLastSearchCriteria.SearchType := stNone;
    end;
  end;
end;

//------------------------------------------------------------------------------
function TModuleLogistiqueRechercheColis.NearSearch(ACriteriaOne, ACriteriaTwo: TSearchCriteria): Boolean;
begin
  Result := False;

  if ACriteriaOne.SearchType = ACriteriaTwo.SearchType then
  begin
    if (ACriteriaOne.SearchType = stDate) and (ACriteriaOne.DateType <> ACriteriaTwo.DateType) then
      Exit;

    if ACriteriaOne.MagasinOrigine <> ACriteriaTwo.MagasinOrigine then
      Exit;
    if ACriteriaOne.MagasinDestination <> ACriteriaTwo.MagasinDestination then
      Exit;
    if ACriteriaOne.MagasinReception <> ACriteriaTwo.MagasinReception then
      Exit;

    Result := True;
  end;
end;

//------------------------------------------------------------------------------
procedure TModuleLogistiqueRechercheColis.ScrollBordereauOfColis();
begin
  if Assigned(FExpeditionColis) and Assigned(FColis) and Assigned(FExpeditionColis.DataSet) and Assigned(FColis.DataSet) and not FExpeditionColis.DataSet.ControlsDisabled and not FColis.DataSet.ControlsDisabled then
    FExpeditionColis.DataSet.Locate('EXP_N_ID', FColis.DataSet.FieldByName('EXP_N_ID').AsFloat, []);
end;

//------------------------------------------------------------------------------
procedure TModuleLogistiqueRechercheColis.ScrollColisOfBordereau();
begin
  if Assigned(FExpeditionColis) and Assigned(FColis) and Assigned(FExpeditionColis.DataSet) and Assigned(FColis.DataSet) and not FExpeditionColis.DataSet.ControlsDisabled and not FColis.DataSet.ControlsDisabled then
    FColis.DataSet.Locate('EXP_N_ID', FExpeditionColis.DataSet.FieldByName('EXP_N_ID').AsFloat, []);
end;

//------------------------------------------------------------------------------
function TModuleLogistiqueRechercheColis.Search(ASearchCriteria: TSearchCriteria): Boolean;
begin
  if Assigned(FExpeditionColis) then
    FExpeditionColis.Query := nil;

  if Assigned(FColis) then
    FColis.Query := nil;

  MakeSearchQuery(ASearchCriteria);
  Result := Assigned(FSearchResult) and not FSearchResult.IsEmpty();
end;

{ TModuleLogistiqueRechercheColis.TSearchCriteria }

//------------------------------------------------------------------------------
class function TModuleLogistiqueRechercheColis.TSearchCriteria.Create(ASearchType: TSearchType; const ANumero: string): TSearchCriteria;
begin
  Result.SearchType := ASearchType;
  case ASearchType of
    stPick : Result.NumeroBordereau := TSearchString(ANumero);
    stColisXYZ : Result.NumeroColis := TSearchString(ANumero);
  end;
end;

//------------------------------------------------------------------------------
class function TModuleLogistiqueRechercheColis.TSearchCriteria.Create(ASearchDateType: TSearchDateType; ADate: TDate): TSearchCriteria;
begin
  Result.SearchType := stDate;
  Result.DateType := ASearchDateType;
  Result.DateValue := ADate;
end;


end.