---

Copy Job Templates     

{This script enables you create a Job Template in Ostendo by copying an existing Job template}

var

TheOldTemplate: String;

TheNewTemplate: String;

 

procedure InsertTemplateMaster(TheOldTemplateCode,TheNewTemplateCode:String);

var

TempQuery,TempQuery2: TpFIBQuery;

TempTrans,TempTrans2: TpFIBTransaction;

x: Integer;

insertSQL: String;

begin

TempTrans := TpFIBTransaction.Create(nil);

TempTrans2 := TpFIBTransaction.Create(nil);

TempQuery := TpFIBQuery.Create(nil);

TempQuery2 := TpFIBQuery.Create(nil);

try

begin

  TempTrans.DefaultDatabase := OstendoDB;

  TempTrans2.DefaultDatabase := OstendoDB;

  TempQuery.Database := OstendoDB;

  TempQuery.Transaction := TempTrans;

  TempQuery.Options := qoStartTransaction;

  TempQuery.SQL.Clear;

  TempQuery2.Database := OstendoDB;

  TempQuery2.Transaction := TempTrans2;

  TempQuery2.Options := qoStartTransaction + qoAutoCommit;

  TempQuery2.SQL.Clear;

  TempQuery.SQL.ADD('select TEMPLATECODE,TEMPLATEDESCRIPTION,TEMPLATESTATUS,TEMPLATENOTES,');

  TempQuery.SQL.ADD('CONFIGUREDBYTASK,TASKSEQUENCING from TEMPLATEMASTER where TEMPLATECODE = ''' + TheOldTemplateCode + '''');

  TempQuery.ExecQuery;

  While not(TempQuery.eof) do

  begin

    insertSQL := 'insert into TEMPLATEMASTER (TEMPLATECODE,TEMPLATEDESCRIPTION,TEMPLATESTATUS,TEMPLATENOTES,' +

                 'CONFIGUREDBYTASK,TASKSEQUENCING) values (' +

                 ':TEMPLATECODE,:TEMPLATEDESCRIPTION,:TEMPLATESTATUS,:TEMPLATENOTES,' +

                 ':CONFIGUREDBYTASK,:TASKSEQUENCING)';

    TempQuery2.SQL.ADD(insertSQL);

    TempQuery2.PN('TEMPLATECODE').asstring := TheNewTemplateCode;

    TempQuery2.PN('TEMPLATEDESCRIPTION').asstring := TempQuery.FN('TEMPLATEDESCRIPTION').asstring;

    TempQuery2.PN('TEMPLATESTATUS').asstring := TempQuery.FN('TEMPLATESTATUS').asstring;

    TempQuery2.PN('TEMPLATENOTES').asstring := TempQuery.FN('TEMPLATENOTES').asstring;

    TempQuery2.PN('CONFIGUREDBYTASK').asstring := TempQuery.FN('CONFIGUREDBYTASK').asstring;

    TempQuery2.PN('TASKSEQUENCING').asstring := TempQuery.FN('TASKSEQUENCING').asstring;

    TempQuery2.ExecQuery;

    TempQuery.next;

  end;

end;

finally

   TempTrans.Free;

   TempTrans2.Free;

   TempQuery.Close;

   TempQuery2.Close;

   TempQuery.Free;

   TempQuery2.Free;

end;

end;

 

procedure InsertTemplateTasks(TheOldTemplateCode,TheNewTemplateCode:String);

var

TempQuery,TempQuery2: TpFIBQuery;

TempTrans,TempTrans2: TpFIBTransaction;

x: Integer;

insertSQL: String;

begin

TempTrans := TpFIBTransaction.Create(nil);

TempTrans2 := TpFIBTransaction.Create(nil);

TempQuery := TpFIBQuery.Create(nil);

TempQuery2 := TpFIBQuery.Create(nil);

try

begin

  TempTrans.DefaultDatabase := OstendoDB;

  TempTrans2.DefaultDatabase := OstendoDB;

  TempQuery.Database := OstendoDB;

  TempQuery.Transaction := TempTrans;

  TempQuery.Options := qoStartTransaction;

  TempQuery.SQL.Clear;

  TempQuery2.Database := OstendoDB;

  TempQuery2.Transaction := TempTrans2;

  TempQuery2.Options := qoStartTransaction + qoAutoCommit;

  TempQuery.SQL.ADD('select TASKSEQUENCE,TASKNAME,TASKDESCRIPTION,TASKTOJOBLINES,');

  TempQuery.SQL.ADD('TASKBILLCODE,TASKBILLDESCRIPTION,TASKBILLUNIT,TASKBILLQTY,');

  TempQuery.SQL.ADD('TASKINSTRUCTIONS,TASKESTIMATEDDURATION,DURATIONSCALE,DEPARTMENTCODE');

  TempQuery.SQL.ADD(' from TEMPLATETASKS where TEMPLATECODE = ''' + TheOldTemplateCode + '''');

  TempQuery.ExecQuery;

  While not(TempQuery.eof) do

  begin

    InsertSQL := 'insert into TemplateTasks (TEMPLATECODE,TASKSEQUENCE,TASKNAME,TASKDESCRIPTION,TASKTOJOBLINES,' +

                 'TASKBILLCODE,TASKBILLDESCRIPTION,TASKBILLUNIT,TASKBILLQTY,' +

                 'TASKINSTRUCTIONS,TASKESTIMATEDDURATION,DURATIONSCALE,DEPARTMENTCODE) values (' +

                 ':TEMPLATECODE,:TASKSEQUENCE,:TASKNAME,:TASKDESCRIPTION,:TASKTOJOBLINES,' +

                 ':TASKBILLCODE,:TASKBILLDESCRIPTION,:TASKBILLUNIT,:TASKBILLQTY,' +

                 ':TASKINSTRUCTIONS,:TASKESTIMATEDDURATION,:DURATIONSCALE,:DEPARTMENTCODE)';

    TempQuery2.SQL.Clear;

    TempQuery2.SQL.ADD(insertSQL);

    TempQuery2.PN('TEMPLATECODE').asstring := TheNewTemplateCode;

    TempQuery2.PN('TASKSEQUENCE').asinteger := TempQuery.FN('TASKSEQUENCE').asinteger;

    TempQuery2.PN('TASKNAME').asstring := TempQuery.FN('TASKNAME').asstring;

    TempQuery2.PN('TASKDESCRIPTION').asstring := TempQuery.FN('TASKDESCRIPTION').asstring;

    TempQuery2.PN('TASKTOJOBLINES').asstring := TempQuery.FN('TASKTOJOBLINES').asstring;

    TempQuery2.PN('TASKBILLCODE').asstring := TempQuery.FN('TASKBILLCODE').asstring;

    TempQuery2.PN('TASKBILLDESCRIPTION').asstring := TempQuery.FN('TASKBILLDESCRIPTION').asstring;

    TempQuery2.PN('TASKBILLUNIT').asstring := TempQuery.FN('TASKBILLUNIT').asstring;

    TempQuery2.PN('TASKBILLQTY').value := TempQuery.FN('TASKBILLQTY').value;

    TempQuery2.PN('TASKINSTRUCTIONS').asstring := TempQuery.FN('TASKINSTRUCTIONS').asstring;

    TempQuery2.PN('TASKESTIMATEDDURATION').value := TempQuery.FN('TASKESTIMATEDDURATION').value;

    TempQuery2.PN('DURATIONSCALE').asstring := TempQuery.FN('DURATIONSCALE').asstring;

    TempQuery2.PN('DEPARTMENTCODE').asstring := TempQuery.FN('DEPARTMENTCODE').asstring;

    TempQuery2.ExecQuery;

    TempQuery.next;

  end;

end;

finally

   TempTrans.Free;

   TempTrans2.Free;

   TempQuery.Close;

   TempQuery2.Close;

   TempQuery.Free;

   TempQuery2.Free;

end;

end;

 

procedure InsertTemplateResources(TheOldTemplateCode,TheNewTemplateCode:String);

var

TempQuery,TempQuery2: TpFIBQuery;

TempTrans,TempTrans2: TpFIBTransaction;

x: Integer;

insertSQL: String;

begin

TempTrans := TpFIBTransaction.Create(nil);

TempTrans2 := TpFIBTransaction.Create(nil);

TempQuery := TpFIBQuery.Create(nil);

TempQuery2 := TpFIBQuery.Create(nil);

try

begin

  TempTrans.DefaultDatabase := OstendoDB;

  TempTrans2.DefaultDatabase := OstendoDB;

  TempQuery.Database := OstendoDB;

  TempQuery.Transaction := TempTrans;

  TempQuery.Options := qoStartTransaction;

  TempQuery.SQL.Clear;

  TempQuery2.Database := OstendoDB;

  TempQuery2.Transaction := TempTrans2;

  TempQuery2.Options := qoStartTransaction + qoAutoCommit;

  TempQuery.SQL.ADD('select TASKNAME,RESOURCETYPE, RESOURCENAME');

  TempQuery.SQL.ADD(' from TEMPLATERESOURCES where TEMPLATECODE = ''' + TheOldTemplateCode + '''');

  TempQuery.ExecQuery;

  While not(TempQuery.eof) do

  begin

    InsertSQL := 'insert into TEMPLATERESOURCES (TEMPLATECODE,TASKNAME,RESOURCETYPE, RESOURCENAME) values (' +

                 ':TEMPLATECODE,:TASKNAME,:RESOURCETYPE, :RESOURCENAME)';

    TempQuery2.SQL.Clear;

    TempQuery2.SQL.ADD(insertSQL);

    TempQuery2.PN('TEMPLATECODE').asstring := TheNewTemplateCode;

    TempQuery2.PN('TASKNAME').asstring := TempQuery.FN('TASKNAME').asstring;

    TempQuery2.PN('RESOURCETYPE').asstring := TempQuery.FN('RESOURCETYPE').asstring;

    TempQuery2.PN('RESOURCENAME').asstring := TempQuery.FN('RESOURCENAME').asstring;

    TempQuery2.ExecQuery;

    TempQuery.next;

  end;

end;

finally

   TempTrans.Free;

   TempTrans2.Free;

   TempQuery.Close;

   TempQuery2.Close;

   TempQuery.Free;

   TempQuery2.Free;

end;

end;

 

procedure InsertTemplateLines(TheOldTemplateCode,TheNewTemplateCode:String);

var

TempQuery,TempQuery2: TpFIBQuery;

TempTrans,TempTrans2: TpFIBTransaction;

x: Integer;

insertSQL: String;

begin

TempTrans := TpFIBTransaction.Create(nil);

TempTrans2 := TpFIBTransaction.Create(nil);

TempQuery := TpFIBQuery.Create(nil);

TempQuery2 := TpFIBQuery.Create(nil);

try

begin

  TempTrans.DefaultDatabase := OstendoDB;

  TempTrans2.DefaultDatabase := OstendoDB;

  TempQuery.Database := OstendoDB;

  TempQuery.Transaction := TempTrans;

  TempQuery.Options := qoStartTransaction;

  TempQuery.SQL.Clear;

  TempQuery2.Database := OstendoDB;

  TempQuery2.Transaction := TempTrans2;

  TempQuery2.Options := qoStartTransaction + qoAutoCommit;

  TempQuery.SQL.ADD('select TASKNAME,LINENUMBER,CODETYPE,LINECODE,LINEDESCRIPTION, LINEUNIT,');

  TempQuery.SQL.ADD('TEMPLATEQTY,LINENOTES ');

  TempQuery.SQL.ADD(' from TEMPLATELINES where TEMPLATECODE = ''' + TheOldTemplateCode + '''');

  TempQuery.ExecQuery;

  While not(TempQuery.eof) do

  begin

    InsertSQL := 'insert into TemplateLines (TEMPLATECODE,TASKNAME,LINENUMBER,CODETYPE,LINECODE,LINEDESCRIPTION, LINEUNIT,' +

                 'TEMPLATEQTY,LINENOTES) values (' +

                 ':TEMPLATECODE,:TASKNAME,:LINENUMBER,:CODETYPE,:LINECODE,:LINEDESCRIPTION, :LINEUNIT,' +

                 ':TEMPLATEQTY,:LINENOTES)';

    TempQuery2.SQL.Clear;

    TempQuery2.SQL.ADD(insertSQL);

    TempQuery2.PN('TEMPLATECODE').asstring := TheNewTemplateCode;

    TempQuery2.PN('TASKNAME').asstring := TempQuery.FN('TASKNAME').asstring;

    TempQuery2.PN('LINENUMBER').asinteger := TempQuery.FN('LINENUMBER').asinteger;

    TempQuery2.PN('CODETYPE').asstring := TempQuery.FN('CODETYPE').asstring;

    TempQuery2.PN('LINECODE').asstring := TempQuery.FN('LINECODE').asstring;

    TempQuery2.PN('LINEDESCRIPTION').asstring := TempQuery.FN('LINEDESCRIPTION').asstring;

    TempQuery2.PN('LINEUNIT').asstring := TempQuery.FN('LINEUNIT').asstring;

    TempQuery2.PN('TEMPLATEQTY').value := TempQuery.FN('TEMPLATEQTY').value;

    TempQuery2.PN('LINENOTES').asstring := TempQuery.FN('LINENOTES').asstring;

    TempQuery2.ExecQuery;

    TempQuery.next;

  end;

end;

finally

   TempTrans.Free;

   TempTrans2.Free;

   TempQuery.Close;

   TempQuery2.Close;

   TempQuery.Free;

   TempQuery2.Free;

end;

end;

 

begin

TheOldTemplate := AskMandatoryQuestionWithLookup('Old Template','Please select an Existing Template to Copy',1058);

TheNewTemplate := AskMandatoryQuestion('New Template','TEXT','Please Enter the New Template Code','');

 

InsertTemplateMaster(TheOldTemplate,TheNewTemplate);

InsertTemplateTasks(TheOldTemplate,TheNewTemplate);

InsertTemplateResources(TheOldTemplate,TheNewTemplate);

InsertTemplateLines(TheOldTemplate,TheNewTemplate);

end.