context: using System;
  using System.Collections.Generic;
  using System.Text;
  using System.Data;
  using BasicPersonData.Forms;
  using System.Windows.Forms;
  using CommonCode;
  using Microsoft.VisualBasic.Devices;
  
  namespace BasicPersonData.Classes
  {
      /// <summary>
      /// A  class containing variables and 
      /// functions we will like to call directly from 
      /// anywhere in the project without creating an instance first
      /// </summary>
      class Global
      {
          #region "GLOBAL DECLARATIONS..."
          public static BasicPersonData myPrsn = new BasicPersonData();
          public static mainForm mnFrm = null;
          public static string[] dfltPrvldgs = {"View Person", "View Basic Person Data", 
  		/*2*/ "View Curriculum Vitae", "View Basic Person Assignments", 
      /*4*/ "View Person Pay Item Assignments", "View SQL", "View Record History",
      /*7*/ "Add Person Info","Edit Person Info","Delete Person Info",
      /*10*/"Add Basic Assignments", "Edit Basic Assignments", "Delete Basic Assignments",
      /*13*/"Add Pay Item Assignments", "Edit Pay Item Assignments", "Delete Pay Item Assignments","View Banks",
      /*17*/"Define Assignment Templates", "Edit Assignment Templates", "Delete Assignment Templates",
      /*20*/"View Assignment Templates", "Manage My Firm",
      /* 22 */ "View Leave Management", "Add Leave Management", "Edit Leave Management", "Delete Leave Management", 
      /* 26 */"View Other Person's Absences"};
  
          public static string[] subGrpNames = { "Person Data" };
          public static string[] mainTableNames = { "prs.prsn_names_nos" };
          public static string[] keyColumnNames = { "person_id" };
          public static string currentPanel = "";
          #endregion
  
          #region "SQL STATEMENTS..."
          #region "INSERT STATEMENTS..."
          public static void createPrsnBasic(string frstnm, string surname, string othnm, string title
           , string loc_id, int orgid, string gender, string marsts, string dob, string pob, string birthcert,
           string resaddrs, string pstladrs, string email, string tel, string mobl, string fax, string homtwn
              , string ntlty, string imgNm, long firmID, long siteID)
          {
              string dateStr = Global.mnFrm.cmCde.getDB_Date_time();
              dob = DateTime.ParseExact(
                    dob, "dd-MMM-yyyy",
                    System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
              string insSQL = "INSERT INTO prs.prsn_names_nos(" +
                     "created_by, creation_date, last_update_by, last_update_date, " +
                     "first_name, sur_name, other_names, title, local_id_no, org_id, " +
                     "gender, marital_status, date_of_birth, place_of_birth, religion, " +
                     "res_address, pstl_addrs, email, cntct_no_tel, cntct_no_mobl, " +
                     "cntct_no_fax, hometown, nationality, img_location,lnkd_firm_org_id,lnkd_firm_site_id)" +
             "VALUES (" + Global.myPrsn.user_id + ", '" + dateStr + "', " +
             Global.myPrsn.user_id + ", '" + dateStr + "', '" + frstnm.Replace("'", "''") + "', " +
                     "'" + surname.Replace("'", "''") + "', '" + othnm.Replace("'", "''") +
                     "', '" + title.Replace("'", "''") + "', '" + loc_id.Replace("'", "''") +
                     "', " + orgid + ", '" + gender.Replace("'", "''") + "', " +
                     "'" + marsts.Replace("'", "''") + "', '" + dob.Replace("'", "''") +
                     "', '" + pob.Replace("'", "''") + "', '" + birthcert.Replace("'", "''") +
                     "', '" + resaddrs.Replace("'", "''") + "', " +
                     "'" + pstladrs.Replace("'", "''") + "', '" + email.Replace("'", "''") +
                     "', '" + tel.Replace("'", "''") + "', '" + mobl.Replace("'", "''") +
                     "', '" + fax.Replace("'", "''") + "', '" + homtwn.Replace("'", "''") +
                     "', '" + ntlty.Replace("'", "''") + "', '" + imgNm.Replace("'", "''") + "'," + firmID + "," + siteID + ")";
              Global.mnFrm.cmCde.insertDataNoParams(insSQL);
          }
  
          public static void createPrsnExtrData(long prsnID, string[] colData)
          {
              string dateStr = Global.mnFrm.cmCde.getDB_Date_time();
              string insSQL = @"INSERT INTO prs.prsn_extra_data(
              person_id, data_col1, data_col2, data_col3, data_col4, 
              data_col5, data_col6, data_col7, data_col8, data_col9, data_col10, 
              data_col11, data_col12, data_col13, data_col14, data_col15, data_col16, 
              data_col17, data_col18, data_col19, data_col20, data_col21, data_col22, 
              data_col23, data_col24, data_col25, data_col26, data_col27, data_col28, 
              data_col29, data_col30, data_col31, data_col32, data_col33, data_col34, 
              data_col35, data_col36, data_col37, data_col38, data_col39, data_col40, 
              data_col41, data_col42, data_col43, data_col44, data_col45, data_col46, 
              data_col47, data_col48, data_col49, data_col50, created_by, creation_date, 
              last_update_by, last_update_date)" +
             "VALUES (" + prsnID + ", '" + colData[0].Replace("'", "''") +
             "', '" + colData[1].Replace("'", "''") + "', '" + colData[2].Replace("'", "''") +
             "', '" + colData[3].Replace("'", "''") + "', '" + colData[4].Replace("'", "''") +
             "', '" + colData[5].Replace("'", "''") + "', '" + colData[6].Replace("'", "''") +
             "', '" + colData[7].Replace("'", "''") + "', '" + colData[8].Replace("'", "''") +
             "', '" + colData[9].Replace("'", "''") + "', '" + colData[10].Replace("'", "''") +
             "', '" + colData[11].Replace("'", "''") + "', '" + colData[12].Replace("'", "''") +
             "', '" + colData[13].Replace("'", "''") + "', '" + colData[14].Replace("'", "''") +
             "', '" + colData[15].Replace("'", "''") + "', '" + colData[16].Replace("'", "''") +
             "', '" + colData[17].Replace("'", "''") + "', '" + colData[18].Replace("'", "''") +
             "', '" + colData[19].Replace("'", "''") + "', '" + colData[20].Replace("'", "''") +
             "', '" + colData[21].Replace("'", "''") + "', '" + colData[22].Replace("'", "''") +
             "', '" + colData[23].Replace("'", "''") + "', '" + colData[24].Replace("'", "''") +
             "', '" + colData[25].Replace("'", "''") + "', '" + colData[26].Replace("'", "''") +
             "', '" + colData[27].Replace("'", "''") + "', '" + colData[28].Replace("'", "''") +
             "', '" + colData[29].Replace("'", "''") + "', '" + colData[30].Replace("'", "''") +
             "', '" + colData[31].Replace("'", "''") + "', '" + colData[32].Replace("'", "''") +
             "', '" + colData[33].Replace("'", "''") + "', '" + colData[34].Replace("'", "''") +
             "', '" + colData[35].Replace("'", "''") + "', '" + colData[36].Replace("'", "''") +
             "', '" + colData[37].Replace("'", "''") + "', '" + colData[38].Replace("'", "''") +
             "', '" + colData[39].Replace("'", "''") + "', '" + colData[40].Replace("'", "''") +
             "', '" + colData[41].Replace("'", "''") + "', '" + colData[42].Replace("'", "''") +
             "', '" + colData[43].Replace("'", "''") + "', '" + colData[44].Replace("'", "''") +
             "', '" + colData[45].Replace("'", "''") + "', '" + colData[46].Replace("'", "''") +
             "', '" + colData[47].Replace("'", "''") + "', '" + colData[48].Replace("'", "''") +
             "', '" + colData[49].Replace("'", "''") + "', " + Global.myPrsn.user_id +
             ", '" + dateStr + "', " + Global.myPrsn.user_id + ", '" + dateStr + "')";
              Global.mnFrm.cmCde.insertDataNoParams(insSQL);
          }
  
          public static void updatePrsnExtrData(long prsnID, string[] colData)
          {
              string dateStr = Global.mnFrm.cmCde.getDB_Date_time();
              string insSQL = @"UPDATE prs.prsn_extra_data 
     SET data_col1='" + colData[0].Replace("'", "''") +
             "', data_col2='" + colData[1].Replace("'", "''") + "', data_col3='" + colData[2].Replace("'", "''") +
             "', data_col4='" + colData[3].Replace("'", "''") + "', data_col5='" + colData[4].Replace("'", "''") +
             "', data_col6='" + colData[5].Replace("'", "''") + "', data_col7='" + colData[6].Replace("'", "''") +
             "', data_col8='" + colData[7].Replace("'", "''") + "', data_col9='" + colData[8].Replace("'", "''") +
             "', data_col10='" + colData[9].Replace("'", "''") + "', data_col11='" + colData[10].Replace("'", "''") +
             "', data_col12='" + colData[11].Replace("'", "''") + "', data_col13='" + colData[12].Replace("'", "''") +
             "', data_col14='" + colData[13].Replace("'", "''") + "', data_col15='" + colData[14].Replace("'", "''") +
             "', data_col16='" + colData[15].Replace("'", "''") + "', data_col17='" + colData[16].Replace("'", "''") +
             "', data_col18='" + colData[17].Replace("'", "''") + "', data_col19='" + colData[18].Replace("'", "''") +
             "', data_col20='" + colData[19].Replace("'", "''") + "', data_col21='" + colData[20].Replace("'", "''") +
             "', data_col22='" + colData[21].Replace("'", "''") + "', data_col23='" + colData[22].Replace("'", "''") +
             "', data_col24='" + colData[23].Replace("'", "''") + "', data_col25='" + colData[24].Replace("'", "''") +
             "', data_col26='" + colData[25].Replace("'", "''") + "', data_col27='" + colData[26].Replace("'", "''") +
             "', data_col28='" + colData[27].Replace("'", "''") + "', data_col29='" + colData[28].Replace("'", "''") +
             "', data_col30='" + colData[29].Replace("'", "''") + "', data_col31='" + colData[30].Replace("'", "''") +
             "', data_col32='" + colData[31].Replace("'", "''") + "', data_col33='" + colData[32].Replace("'", "''") +
             "', data_col34='" + colData[33].Replace("'", "''") + "', data_col35='" + colData[34].Replace("'", "''") +
             "', data_col36='" + colData[35].Replace("'", "''") + "', data_col37='" + colData[36].Replace("'", "''") +
             "', data_col38='" + colData[37].Replace("'", "''") + "', data_col39='" + colData[38].Replace("'", "''") +
             "', data_col40='" + colData[39].Replace("'", "''") + "', data_col41='" + colData[40].Replace("'", "''") +
             "', data_col42='" + colData[41].Replace("'", "''") + "', data_col43='" + colData[42].Replace("'", "''") +
             "', data_col44='" + colData[43].Replace("'", "''") + "', data_col45='" + colData[44].Replace("'", "''") +
             "', data_col46='" + colData[45].Replace("'", "''") + "', data_col47='" + colData[46].Replace("'", "''") +
             "', data_col48='" + colData[47].Replace("'", "''") + "', data_col49='" + colData[48].Replace("'", "''") +
             "', data_col50='" + colData[49].Replace("'", "''") + "', last_update_by=" + Global.myPrsn.user_id +
             ",  last_update_date='" + dateStr + "' WHERE person_id=" + prsnID;
              Global.mnFrm.cmCde.insertDataNoParams(insSQL);
          }
  
          public static void createExtrDataCol(int colno, string collabel, string lovnm, string datatyp
        , string catgry, int lngth, string dsplytyp, int orgid, int tblrnumcols, int ordr,
            string csvTblColNms, bool isrqrd)
          {
              string dateStr = Global.mnFrm.cmCde.getDB_Date_time();
              string insSQL = @"INSERT INTO prs.prsn_extra_data_cols(
              column_no, column_label, attchd_lov_name, 
              column_data_type, column_data_category, data_length, data_dsply_type, 
              org_id, no_cols_tblr_dsply, col_order, csv_tblr_col_nms, created_by, creation_date, 
              last_update_by, last_update_date,is_required)" +
             "VALUES (" + colno + ", '" + collabel.Replace("'", "''") + "', '" + lovnm.Replace("'", "''") + "', " +
                     "'" + datatyp.Replace("'", "''") + "', '" + catgry.Replace("'", "''") +
                     "', " + lngth + ", '" + dsplytyp.Replace("'", "''") +
                     "', " + orgid + ", " + tblrnumcols + ", " + ordr + ", '" + csvTblColNms.Replace("'", "''") +
                     "', " + Global.myPrsn.user_id +
             ", '" + dateStr + "', " + Global.myPrsn.user_id + ", '" + dateStr +
             "', '" + Global.mnFrm.cmCde.cnvrtBoolToBitStr(isrqrd) + "')";
              Global.mnFrm.cmCde.insertDataNoParams(insSQL);
          }
  
          public static void updateExtrDataCol(int colno, string collabel, string lovnm, string datatyp
      , string catgry, int lngth, string dsplytyp, int orgid, int tblrnumcols,
            long rowid, int ordr, string csvTblColNms, bool isrqrd)
          {
              string dateStr = Global.mnFrm.cmCde.getDB_Date_time();
              string updtSQL = @"UPDATE prs.prsn_extra_data_cols SET 
              column_no=" + colno + ", column_label='" + collabel.Replace("'", "''") +
                              "', attchd_lov_name='" + lovnm.Replace("'", "''") +
                              "', column_data_type='" + datatyp.Replace("'", "''") +
                              "', column_data_category='" + catgry.Replace("'", "''") +
                     "', data_length=" + lngth + ", data_dsply_type='" + dsplytyp.Replace("'", "''") +
                     "', org_id=" + orgid + ", no_cols_tblr_dsply=" + tblrnumcols +
                     ", col_order=" + ordr + ", csv_tblr_col_nms='" + csvTblColNms.Replace("'", "''") +
                     "', last_update_by=" + Global.myPrsn.user_id +
                      ", last_update_date='" + dateStr +
                      "', is_required='" + Global.mnFrm.cmCde.cnvrtBoolToBitStr(isrqrd) + "' WHERE extra_data_cols_id = " + rowid;
              Global.mnFrm.cmCde.updateDataNoParams(updtSQL);
          }
  
          public static void createPrsnsType(long prsnid, string rsn, string date1, string date2,
           string futhDet, string prsntyp)
          {
              string dateStr = Global.mnFrm.cmCde.getDB_Date_time();
              date1 = DateTime.ParseExact(
              date1, "dd-MMM-yyyy",
              System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
  
              date2 = DateTime.ParseExact(
              date2, "dd-MMM-yyyy",
              System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
  
              string insSQL = "INSERT INTO pasn.prsn_prsntyps(" +
                       "person_id, prn_typ_asgnmnt_rsn, valid_start_date, valid_end_date, " +
                       "created_by, creation_date, last_update_by, last_update_date, " +
                       "further_details, prsn_type)" +
               "VALUES (" + prsnid + ", '" + rsn.Replace("'", "''") +
               "', '" + date1.Replace("'", "''") + "', '" + date2.Replace("'", "''") + "', " +
                       "" + Global.myPrsn.user_id + ", '" + dateStr + "', " + Global.myPrsn.user_id + ", '" + dateStr + "', " +
                       "'" + futhDet.Replace("'", "''") + "', '" + prsntyp.Replace("'", "''") + "')";
              Global.mnFrm.cmCde.insertDataNoParams(insSQL);
          }
  
          public static void createNatnlty(long prsnid, string ntnlty,
           string ntnlty_typ, string idnum, string detIssd, string expryDte, string otherinfo)
          {
  
gt:             if (ntnlty.Length > 100)