// upgrade_pt7hd.script : Copy PT7-HelpDesk data to nService 4
// Run this script in Perfect Tracker 7 Studio. You must set up nService4 
// first. Correct the source and target ODBC data sources before running 
// this script.

set local.sourceDsn = 'pt7data';  // source data source name
set local.sourceUid = 'pt7';         // source user ID
set local.sourcePwd = 'pt7';         // source password

set local.targetDsn = 'ns4data';  // target data source name
set local.targetUid = 'pt7';         // target user ID
set local.targetPwd = 'pt7';         // target password

// Set default data source to target
local.useDataSource(local.targetDsn, local.targetUid, local.targetPwd);

// Make sure target data source does not have any data. 
// You can remove this check only if you are sure that the target 
// data source is truely the target data source with useless data.
//
sql target_sr = 'SELECT sr_id FROM ns4_service_request';
if (target_sr.fetch)
  error 
    'Target data source ' + local.targetDsn + ' has data in it. '
    'Make sure you did not switch the source and target data '
    'sources by mistake. ';
endif
target_sr.close;

//-------------------------- users --------------------------------------
server.log('Copying from ' + local.sourceDsn + '.pt7hd_user to ' + local.targetDsn);
sql s1c = 'SELECT * FROM pt7hd_user ORDER BY user_id';
s1c.useDataSource(local.sourceDsn, local.sourceUid, local.sourcePwd);

set local.count = 0;
set local.dept_count = 0;

while (s1c.fetch)

  set local.ou_id = 90;
  if (s1c.dept <> '')
    sql findOu = SELECT ou_id FROM ns4_org_unit WHERE ou_name = s1c.dept;
    if (findOu.fetch)
      set local.ou_id = findOu.ou_id;
    else
      set local.ou_id = local.dept_count + 200;
      sql i1b = 
        INSERT INTO ns4_org_unit(ou_id, ou_parent_id, ou_name)
        VALUES(local.ou_id, 90, s1c.dept);
      i1b.execute;
      set local.dept_count = 1 + local.dept_count;
    endif
    findOu.close;
  endif

  set local.site_id = 90;
  set local.usr_status = 1;
  if (s1c.status <> 'active')
    set local.usr_status = 2;
  endif
  
  set local.display_name = s1c.user_id;
  if (string.trim(s1c.full_name) <> '')
    set local.display_name = s1c.full_name;
  endif

  sql i1c = 
    INSERT INTO ns4_user
    (
      usr_id, usr_logon_name, usr_status, 
      usr_org_id, usr_ou_id, usr_site_id, usr_password, 
      usr_display_name, usr_title, usr_phone, usr_fax, usr_email
    )
    VALUES
    (
      local.count + 200, s1c.user_id, local.usr_status, 
      90, local.ou_id, local.site_id, to.decrypted(s1c.user_pwd), 
      local.display_name, s1c.title, string.substring(s1c.phone, 0, 30), s1c.fax, s1c.email 
    );
  i1c.execute;
  i1c.close;

  set local.group_id = 0;
  if (s1c.user_type = 'StaffMember')
    set local.group_id = 92;
  elseif (s1c.user_type = 'Manager')
    set local.group_id = 91;
  endif
  if (local.group_id > 0)
    sql addUserToGrp =
      INSERT INTO ns4_user_in_group(uig_usr_id, uig_grp_id) 
      VALUES(local.count + 200, local.group_id);
    
    addUserToGrp.execute;
  endif
  
  set local.count = 1 + local.count;

  server.logstr(s1c.user_id);
  if (local.count > 0 AND local.count % 50 = 0)
    server.log('.');
  else
    server.logstr('.');
  endif
endwhile
s1c.close;
server.log(local.count);

//--------------------------- knowledge base ------------------------
server.log('Copying from ' + local.sourceDsn + '.pt7hd_kb to ' + local.targetDsn);
set local.count = 0;

set local.i = 0;  
while (1)
  set local.i = 1 + local.i;
  if (local.i = 7)
    break;
  elseif (local.i = 1)
    set local.where = 'kb_p1 IS NULL';
  elseif (local.i = 2)
    set local.where = 'kb_p1 IS NOT NULL AND kb_p2 IS NULL';
  elseif (local.i = 3)
    set local.where = 'kb_p1 IS NOT NULL AND kb_p2 IS NOT NULL AND '
                      'kb_p3 IS NULL';
  elseif (local.i = 4)
    set local.where = 'kb_p1 IS NOT NULL AND kb_p2 IS NOT NULL AND '
                      'kb_p3 IS NOT NULL AND kb_p4 IS NULL';
  elseif (local.i = 5)
    set local.where = 'kb_p1 IS NOT NULL AND kb_p2 IS NOT NULL AND '
                      'kb_p3 IS NOT NULL AND kb_p4 IS NOT NULL AND '
                      'kb_p5 IS NULL';
  elseif (local.i = 6)
    set local.where = 'kb_p1 IS NOT NULL AND kb_p2 IS NOT NULL AND '
                      'kb_p3 IS NOT NULL AND kb_p4 IS NOT NULL AND '
                      'kb_p5 IS NOT NULL';
  endif

  sql s2 = 'SELECT * FROM pt7hd_kb WHERE ' + local.where + ' ORDER BY kb_id ASC';
  s2.useDataSource(local.sourceDsn, local.sourceUid, local.sourcePwd);
  while (s2.fetch)
    set local.parent_id = 90; // new kb root
    if (s2.kb_p1 <> '')
      set local.parent_id = s2.kb_p1;
    endif

    set local.keywords = s2.kb_k1 + ' ' + s2.kb_k2 + ' ' + s2.kb_k3 + ' ' +
                         s2.kb_k4 + ' ' + s2.kb_k5;
    if (s2.kb_type = 1)
      sql i2 = 
        INSERT INTO ns4_kbase
        (
          kb_id, kb_parent_id, kb_name, 
          kb_keywords, kb_description, kb_user_grp, kb_tech_grp, kb_admin_grp
        )
        VALUES
        (
          s2.kb_id, local.parent_id, s2.kb_title, 
          string.trim(local.keywords), s2.kb_summary, 93, 92, 91
        );
      i2.execute;
    else
      sql i2art = 
        INSERT INTO ns4_article
        (
          art_id, art_kb_id, art_title, 
          art_keywords, art_description
        )
        VALUES
        (
          s2.kb_id, local.parent_id, s2.kb_title, 
          string.trim(local.keywords), s2.kb_summary, 
        );
      i2art.execute;
    endif

    set local.count = 1 + local.count;
    server.logstr(s2.kb_id);
    if (local.count > 0 AND local.count % 50 = 0)
      server.log('.');
    else
      server.logstr('.');
    endif
  endwhile
  s2.close;
endwhile
server.log(local.count);

server.log(
  'Copying article content from ' + local.sourceDsn + '.pt7hd_kb_file to ' + 
  local.targetDsn);
sql s3 = 'SELECT * FROM pt7hd_kb_file WHERE kbf_type = 1 ORDER BY kbf_id ASC';
s3.useDataSource(local.sourceDsn, local.sourceUid, local.sourcePwd);

set local.count = 0;
while (s3.fetch)
  sql i3 = 
    INSERT INTO ns4_article_note
    (
      an_id, an_art_id, an_format, an_detail 
    )
    VALUES
    (
      s3.kbf_id, s3.kbf_kb_id, 2, s3.kbf_content 
    );
  i3.execute;
  i3.close;
  set local.count = 1 + local.count;
  server.logstr(s3.kbf_id);
  if (local.count > 0 AND local.count % 50 = 0)
    server.log('.');
  else
    server.logstr('.');
  endif
endwhile
s3.close;
server.log(local.count);

server.log('Copying kb files from ' + local.sourceDsn + '.pt7hd_kb_file to ' + local.targetDsn);
sql s3b = 'SELECT * FROM pt7hd_kb_file WHERE kbf_type <> 1 ORDER BY kbf_id';
s3b.useDataSource(local.sourceDsn, local.sourceUid, local.sourcePwd);

set local.count = 0;
while (s3b.fetch)
  sql findAnchorNoteID = 
    SELECT an_id FROM ns4_article_note 
    WHERE an_type = 1 AND an_art_id = s3b.kbf_kb_id;
  set local.note_id = findAnchorNoteID.an_id;
  findAnchorNoteID.close;
  
  sql i3b = 
    INSERT INTO ns4_article_file
    (
      af_id, af_an_id, af_file_name, af_mime_type, 
      af_file_size, af_file_data
    )
    VALUES
    (
      s3b.kbf_id, local.note_id, s3b.kbf_name, 'application/octet-stream',
      s3b.kbf_size, s3b.kbf_content
    );
  i3b.execute;
  i3b.close;
  set local.count = 1 + local.count;
  server.logstr(s3b.kbf_id);
  if (local.count > 0 AND local.count % 50 = 0)
    server.log('.');
  else
    server.logstr('.');
  endif
endwhile
s3b.close;
server.log(local.count);

//-------------------------- request status -----------------------------
server.log(
  'Copying from ' + local.sourceDsn + '.pt7hd_status to ' + 
  local.targetDsn);
sql s6 = 'SELECT * FROM pt7hd_status ORDER BY name ASC';
s6.useDataSource(local.sourceDsn, local.sourceUid, local.sourcePwd);

set local.count = 0;
while (s6.fetch)
  sql i6 = 
    INSERT INTO ns4_req_status
    (
      rs_id, rs_srv_id, rs_name, rs_sequence, rs_description
    )
    VALUES
    (
      local.count + 200, 90, s6.name, s6.seq_num, s6.description
    );
  i6.execute;
  i6.close;
  set local.count = 1 + local.count;
  if (local.count > 0 AND local.count % 50 = 0)
    server.log('.');
  else
    server.logstr('.');
  endif
endwhile
server.log(local.count);

//-------------------------- request priority ---------------------------
server.log(
  'Copying from ' + local.sourceDsn + '.pt7hd_priority to ' + 
  local.targetDsn);
sql s7 = 'SELECT * FROM pt7hd_priority ORDER BY name ASC';
s7.useDataSource(local.sourceDsn, local.sourceUid, local.sourcePwd);

set local.count = 0;
while (s7.fetch)
  sql i7 = 
    INSERT INTO ns4_req_priority
    (
      rp_id, rp_srv_id, rp_name, rp_default, rp_description
    )
    VALUES
    (
      local.count + 200, 90, s7.name, s7.is_def, s7.description
    );
  i7.execute;
  i7.close;
  set local.count = 1 + local.count;
  if (local.count > 0 AND local.count % 50 = 0)
    server.log('.');
  else
    server.logstr('.');
  endif
endwhile
server.log(local.count);

//-------------------------- products -----------------------------------
server.log(
  'Copying from ' + local.sourceDsn + '.pt7hd_product to ' + 
  local.targetDsn);
sql s9c = 'SELECT * FROM pt7hd_product ORDER BY name';
s9c.useDataSource(local.sourceDsn, local.sourceUid, local.sourcePwd);

set local.count = 0;
while (s9c.fetch)

  sql i9c = 
    INSERT INTO ns4_product
    (
      pd_id, pd_parent_id, pd_name, pd_description
    )
    values
    (
      local.count + 200, 90, s9c.name, s9c.description
    );
  i9c.execute;
  i9c.close;
  set local.count = 1 + local.count;
  if (local.count > 0 AND local.count % 50 = 0)
    server.log('.');
  else
    server.logstr('.');
  endif
endwhile
s9c.close;
server.log(local.count);

//-------------------------- asset --------------------------------------
server.log(
  'Copying from ' + local.sourceDsn + '.pt7hd_asset to ' + 
  local.targetDsn);
sql s9 = 'SELECT * FROM pt7hd_asset ORDER BY as_id';
s9.useDataSource(local.sourceDsn, local.sourceUid, local.sourcePwd);

set local.count = 0;
while (s9.fetch)
  
  set local.pd_id = '';
  if (s9.as_product <> '')
    sql findPdID = 
      SELECT pd_id FROM ns4_product WHERE pd_name = s9.as_product;
    local.pd_id = findPdID.pd_id;
    findPdID.close;
  endif
  
  set local.usr_id = '';
  if (s9.as_used_by <> '')
    sql findUser =
      SELECT usr_id FROM ns4_user WHERE usr_logon_name = s9.as_used_by;
      set local.usr_id = findUser.usr_id;
  endif

  set local.status = 1;
  if (s9.as_status = 'In Repair')
    set local.status = 3;
  elseif (s9.as_status = 'Idle')
    set local.status = 4;
  endif

  sql i9 = 
    INSERT INTO ns4_asset
    (
      as_id, as_service_tag, as_pd_id, as_org_id, as_ou_id, as_usr_id, 
      as_purchase_date, as_serial_num, as_name, as_status, 
      as_location
    )
    values
    (
      local.count + 200, s9.as_id, local.pd_id, 90, 90, local.usr_id, 
      s9.as_bought_on, s9.as_serial, s9.as_name, local.status, 
      s9.as_location
    );
  i9.execute;
  i9.close;
  
  if (s9.as_notes <> '')
    sql i9b = 
      INSERT INTO ns4_as_note(asn_id, asn_as_id, asn_detail)
      VALUES(local.count + 200, local.count + 200, s9.as_notes);
      
    i9b.execute;
  endif
  
  set local.count = 1 + local.count;
  if (local.count > 0 AND local.count % 50 = 0)
    server.log('.');
  else
    server.logstr('.');
  endif
endwhile
s9.close;
server.log(local.count);

//-------------------------- request ------------------------------------
server.log(
  'Copying from ' + local.sourceDsn + '.pt7hd_ticket to ' + 
  local.targetDsn);
sql s11 = 'SELECT * FROM pt7hd_ticket ORDER BY ticket_id ASC';
s11.useDataSource(local.sourceDsn, local.sourceUid, local.sourcePwd);

set local.count = 0;
while (s11.fetch)

  set local.srv_id = 90;

  sql findStatusID =
    SELECT rs_id FROM ns4_req_status WHERE rs_name = s11.status;
  set local.status_id = findStatusID.rs_id;
  findStatusID.close;

  sql findPriorityID = 
    SELECT rp_id FROM ns4_req_priority WHERE rp_name = s11.priority;
  set local.priority_id = findPriorityID.rp_id;
  findPriorityID.close;

  set local.submitter = '';
  if (s11.submitted_by <> '')
    sql findSubmitter =
      SELECT usr_id FROM ns4_user WHERE usr_logon_name = s11.submitted_by;
    set local.submitter = findSubmitter.usr_id;
    findSubmitter.close;
  endif

  set local.assigned_to = '';
  if (s11.assigned_to <> '')
    sql findAssignedTo =
      SELECT usr_id FROM ns4_user WHERE usr_logon_name = s11.assigned_to;
    set local.assigned_to = findAssignedTo.usr_id;
    findAssignedTo.close;
  endif

  set local.closed_by = '';
  if (s11.closed_by <> '')
    sql findClosedBy =
      SELECT usr_id FROM ns4_user WHERE usr_logon_name = s11.closed_by;
    set local.closed_by = findClosedBy.usr_id;
    findClosedBy.close;
  endif

  set local.due_reminded = 1;
  if (s11.due_rem_flag = 'y')
    set local.due_reminded = 0;
  endif

  set local.as_id = '';
  if (s11.asset_id <> '')
    sql findAsset = 
      SELECT as_id FROM ns4_asset WHERE as_service_tag = s11.asset_id;
    set local.as_id = findAsset.as_id;
    findAsset.close;
  endif

  sql i11 = 
    INSERT INTO ns4_service_request
    (
      sr_id, sr_service_id, sr_asset_id, 
      sr_status_id, sr_priority_id, sr_close_due, 
      sr_submitter, sr_submitted_on, 
      sr_assigned_to, sr_assigned_on, 
      sr_closed_by, sr_closed_on, sr_updated_on, 
      sr_subject, sr_nbill_hour, sr_turn_around, sr_close_due_rem
    )
    VALUES
    (
      s11.ticket_id, local.srv_id, local.as_id, 
      local.status_id, local.priority_id, s11.deadline, 
      local.submitter, s11.submitted_on, 
      local.assigned_to, s11.assigned_on, 
      local.closed_by, s11.closed_on, s11.submitted_on, 
      s11.title, s11.cost, s11.turn_around, local.due_reminded
    );
  i11.execute;
  i11.close;
  set local.count = 1 + local.count;
  server.logstr(s11.ticket_id);
  server.logstr('.');
  if (local.count > 0 AND local.count % 50 = 0)
    server.log(local.count);
  endif
endwhile
s11.close;
server.log(local.count);

//-------------------------- request history ----------------------------
server.log(
  'Copying from ' + local.sourceDsn + '.pt7hd_ti_change to ' + 
  local.targetDsn);
sql s12 = 'SELECT * FROM pt7hd_ti_change ORDER BY ch_id ASC';
s12.useDataSource(local.sourceDsn, local.sourceUid, local.sourcePwd);

set local.count = 0;
while (s12.fetch)

  // In pt7mssql.sql, the delete trigger for pt7hd_ticket is missing.
  // That may result in some orphan ti_file and ti_change
  sql sqlTicketExist1 = 
    'SELECT sr_id FROM ns4_service_request '
    'WHERE sr_id = ' + s12.ch_ticket_id;

  if (sqlTicketExist1.fetch)

    set local.type = 1;
    if (s12.ch_type = 'internal')
      set local.type = 2;
    elseif (s12.ch_type = 'log')
      set local.type = 3;
    endif
 
    set local.added_by = '';
    if (s12.ch_made_by <> '')
      sql findMadeby = 
        SELECT usr_id FROM ns4_user WHERE usr_logon_name = s12.ch_made_by;
      set local.added_by = findMadeby.usr_id;
      findMadeby.close;
    endif

    sql i12 = 
      INSERT INTO ns4_sr_note
      (
        srn_id, srn_sr_id, srn_added_by, srn_added_on, 
        srn_nbill_hour, srn_type, srn_format, srn_detail
      )
      VALUES
      (
        s12.ch_id, s12.ch_ticket_id, local.added_by, s12.ch_made_on, 
        s12.ch_cost/60, local.type, s12.ch_format + 1, s12.ch_description
      );
    i12.execute;
    i12.close;
    set local.count = 1 + local.count;
    server.logstr(s12.ch_id);
    server.logstr('.');
    if (local.count > 0 AND local.count % 50 = 0)
      server.log(local.count);
    endif
  endif

  sqlTicketExist1.close;

endwhile
s12.close;
server.log(local.count);

//-------------------------- request attachments ------------------------
server.log(
  'Copying from ' + local.sourceDsn + '.pt7hd_ti_file to ' + 
  local.targetDsn + '. This operation can be slow, please be patient.');
sql s13 = 'SELECT * FROM pt7hd_ti_file ORDER BY tif_id ASC';
s13.useDataSource(local.sourceDsn, local.sourceUid, local.sourcePwd);

set local.count = 0;
while (s13.fetch)

  sql i13 = 
    INSERT INTO ns4_srn_file
    (
      snf_id, snf_srn_id, snf_name, snf_mime_type,
      snf_size, snf_content
    )
    VALUES
    (
      s13.tif_id, s13.tif_ch_id, s13.tif_name, 'application/octet-stream', 
      s13.tif_size, s13.tif_content
    );
  i13.execute;
  i13.close;
  set local.count = 1 + local.count;
  server.logstr(s13.tif_id);
  if (local.count > 0 AND local.count % 50 = 0)
    server.log('.');
  else
    server.logstr('.');
  endif

endwhile
s13.close;
server.log(local.count);

server.log('pt7-HelpDesk to nService 4 data upgrade finished successfully');