// https://tediousjs.github.io/tedious/index.html
//
// mkdir project01
// cd    project01
// npm install tedious
//
// notepad test2.js (請參考下方程式碼)
// node test2.js 

async function getStrConn(strIP, iPort, strUsername, strPassword, strDatabase, oPara)
{
  // oPara.oConn 連線物件
  
  var oConnection = require('tedious').Connection;
  var strErr="";
  oPara.oConn=null;
  
  var oConfig = 
  {  
    server: strIP,  //update me
    authentication: 
    {
      type: 'default',
      options: 
      {
        userName: strUsername, //update me
        password: strPassword  //update me
      }
    },
    options: 
    {
      trustServerCertificate: false,
      // If you are on Microsoft Azure, you need encryption:
      encrypt: false,
      database: strDatabase,  //update me
      port: iPort
    }
  };
  
  await new Promise
  (
    (成功, 失敗)=>
    {
      oPara.oConn = new oConnection(oConfig);
      
      oPara.oConn.on
      (
        'connect',
        function(err)
        {
          if(err) { 失敗(err); }
          else    { 成功(); }
        }
      )
      
      oPara.oConn.connect();
    }
  )
  .then(     ()=>{ strErr="";                           } )
  .catch( (err)=>{ strErr="err:"+err; oPara.oConn=null; } );
  
  return strErr;
}

async function getStrQuery(strSQL, oConn, oPara)
{
  // oPara.lRowCount 總異動筆數
  // oPara.straColName 欄位名稱一維陣列
  // oPara.oaArray 欄位值二維陣列
  
  var Request = require('tedious').Request;
  var strErr="";
  oPara.straColName=[];
  oPara.oaArray=[];
  
  await new Promise
  (
    (成功, 失敗)=>
    {
      request=new Request
      (
        strSQL,
        function(err, rowCount)
        {
          if(err) { 失敗(err);      }
          else    { 成功(rowCount); }
        }
      );
      
      request.on
      (
        'row',
        function(columns)
        {
          // 標題
          if(oPara.straColName.length==0)
          {
            columns.forEach
            (
              function(column)
              {
                oPara.straColName.push(column.metadata.colName);
              }
            );
          }
          
          // 值
          var oaAry1D=[];
          columns.forEach
          (
            function(column)
            {
              if(column.value===null) { oaAry1D.push("");           }
              else                    { oaAry1D.push(column.value); }
            }
          );
          oPara.oaArray.push(oaAry1D);
        }
      );

      oConn.execSql(request);
    }
  )
  .then( (rowCount)=>{ strErr=""; oPara.lRowCount=rowCount; } )
  .catch( (err)=>    { strErr="err:"+err; } );
  
  return strErr;
}

async function main()
{
  var oConn=null;
  
  var oPara;
  var strErr="";
  
  while(true)
  {
    { // 建立資料庫連線
      oPara={};
      strErr=await getStrConn("192.168.1.88", 1433, "username", "password", "databasename", oPara);
      if(strErr!="") 
      {
        console.log("抱歉，連線失敗！\r\n"+strErr);
       break;
      } 
      oConn=oPara.oConn;
    }
    
    if(1==0)
    { // 執行指令 ins,upd,del,sel,create,drop,......
      oPara={};
      // create table tb2(i int, nvc nvarchar(32) not null default '')
      // insert into tb2(i,nvc) select 1,'haha' union select 2,'good'
      // select nvc,i from tb2
      // drop table tb2
      strErr=await getStrQuery("select nvc,i from tb2", oConn, oPara);
      if(strErr!="")
      {
        console.log("抱歉，失敗！\r\n"+strErr);
      }
      else
      {
        console.log("rowCount="+oPara.lRowCount);
        console.log(oPara.straColName);
        console.log(oPara.oaArray);
      }
    }
    
    { // 執行查詢,物品
      oPara={};
      strErr=await getStrQuery("select top 6 物品代碼, 綜合名稱 from vw物品 where 物品代碼 like 'BP%'", oConn, oPara);
      if(strErr!="")
      {
        console.log("抱歉，查詢失敗！\r\n"+strErr);
      }
      else
      {
        console.log("rowCount="+oPara.lRowCount);
        console.log(oPara.straColName);
        console.log(oPara.oaArray);
      }
    }
    
    { // 執行查詢,個體
      oPara={};
      strErr=await getStrQuery("select top 3 個體代碼, 中文名稱 from 個體 where 個體代碼 like '1003%'", oConn, oPara);
      if(strErr!="")
      {
        console.log("抱歉，查詢失敗！\r\n"+strErr);
      }
      else
      {
        console.log("rowCount="+oPara.lRowCount);
        console.log(oPara.straColName);
        console.log(oPara.oaArray);
      }
    }
    
    break;
  }
  
  if(oConn!==null) { oConn.close(); oConn==null; } // 關閉資料庫連線
  
  console.log("Finished");
}

main();