Oracle SQL Convert Seconds to TIme Format

SELECT TO_CHAR(TRUNC(x/3600),'FM9900')|| chr(58) || TO_CHAR(TRUNC(MOD(x,3600)/60),'FM00')|| chr(58) || TO_CHAR(MOD(x,60),'FM00') FROM DUAL Note: where x is the time field such as departuretime

Camel Case in SQL

Below is a query to help make the Camel Case in the LAST_NAME. The first name has already been Camel Cased so I left it alone. SELECT concat(first_name,' ', concat(UPPER(LEFT(LAST_NAME,1)),'',LOWER(SUBSTRING(LAST_NAME,2,60)))) name FROM table;

Below is just a query of all the fields I needed then export the resultset into a file name email3.txt SELECT concat(first_name,' ', concat(UPPER(LEFT(LAST_NAME,1)),'',LOWER(SUBSTRING(LAST_NAME,2,60)))) name, email, 'Saigon South International School' school, job_title FROM cardinfo_cardinfo where email <> '' and is_leaving_teacher = 0 and job_title <> '' into outfile '/tmp/email3.txt';

PowerSchool Mass Delete Contacts

Attractive Email Signature

Here is my email signature design for SSIS. Vinh Vong made it happen with his code and was able to push out using Active Directory sync to Google. I've added the first div to show the background color was white for our Google G Suite is by default white. Change it to make it fit to your theme. Also all the images are png transparency.

John Nguyen
+84 28 5413 0901 ext 13173

System Administrator
jnguyen@ssis.edu.vn

Saigon South International School 78 Nguyen Duc Canh, Tan Phong, D7, HCMC, VN +84 28 5413 0901 info@ssis.edu.vn

Stay Connected! Follow Us!

PowerSchool: Add Slide In Page for Custom Contact

Credit goes out to Sheldon from PSUG

Page fragment to add the button:

<script>

  jQuery(document).ready(function() {

    var url_string = window.location.href;

    console.log(url_string);

    var pStart = url_string.indexOf("contactid=", url_string.indexOf("#?")) + 10;

    var pEnd = url_string.indexOf("&", pStart);

    var contactID = 0;

    if (pEnd > 0)

      contactID = url_string.substring(pStart, pEnd);

    else

      contactID = url_string.substring(pStart);

    console.log(contactID);

    if (contactID != 0) {

      var html = '<div id="extensionfields-div"><label for="extensionfields-input">Extension Fields</label><a href="edit_extensionfields.html?id='+contactID+'" class="button dialogC dockedDialog" id="extensionfields-input" title="Extension Fields">Display</a></div>';

      jQuery("#suffix-div").after(html);

    }

  });

</script>

It looks like this:

The slide out page that has the input boxes (edit_extensionfields.html):

<script>

var existing = false;



jQuery.getJSON("edit_extensionfields.json?id=~(gpv.id)", function(data) {

  if (data.length > 1) {

    jQuery("#myfield1").val(data[0].myfield1);

    jQuery("#myfield2").val(data[0].myfield2);

    jQuery("#lastupdate").html(data[0].lastupdate);

    existing = true;

  }

});



function submitSave() {

  if (!existing) {

    jQuery.ajax({

      url: "/ws/schema/table/u_def_ext_person",

      async: false,

      method: "POST",

      contentType: "application/json",

      data: JSON.stringify({

        "tables":{

          "u_def_ext_person":{

            "personid":"~(gpv.id)",

            "myfield1":jQuery("#myfield1").val(),

            "myfield2":jQuery("#myfield2").val()

          }

        }

      }),

      complete: function(xhr, textStatus) {

        if (xhr.status == 200) {

          jQuery("#save-error").hide();

          jQuery("#save-complete").show();

        } else {

          jQuery("#save-complete").hide();

          jQuery("#save-error").show();

        }

      },

      error: function(result) {

        jQuery("#save-complete").hide();

        jQuery("#save-error").show();

      }

    });



  } else {

    jQuery.ajax({

      url: "/ws/schema/table/u_def_ext_person/~(gpv.id)",

      async: false,

      method: "PUT",

      contentType: "application/json",

      data: JSON.stringify({

        "tables":{

          "u_def_ext_person":{

            "myfield1":jQuery("#myfield1").val(),

            "myfield2":jQuery("#myfield2").val()

          }

        }

      }),

      complete: function(xhr, textStatus) {

        if (xhr.status == 200) {

          jQuery("#save-error").hide();

          jQuery("#save-complete").show();

        } else {

          jQuery("#save-complete").hide();

          jQuery("#save-error").show();

        }

      },

      error: function(result) {

        jQuery("#save-complete").hide();

        jQuery("#save-error").show();

      }

    });

  }



}

</script>



<div class="feedback-confirm" id="save-complete" style="display:none;">Record saved</div>

<div class="feedback-error" id="save-error" style="display:none;">There was a problem saving the record, please try again later</div>



<div class="box-round">

  <form action="edit.html" method="POST" id="saveForm">

  <table class="linkDescList">

    <tr>

      <td class="bold" style="width:180px;">My field 1</td>

      <td>

        <input type="text" name="myfield1" id="myfield1" value="">

      </td>

    </tr>

    <tr>

      <td class="bold">My field 2</td>

      <td>

        <input type="text" name="myfield2" id="myfield2" value="">

      </td>

    </tr>

    <tr>

      <td class="bold">Last Updated</td>

      <td>

        <span id="lastupdate"></span>

      </td>

    </tr>

  </table>

  </form>

</div>

<div class="button-row">

  <button type="button" onclick="submitSave()">Save</button>

</div>

Looks like this:

There's also a JSON (edit_extensionfields.json) that gets the existing values into slide in page:

[

~[tlist_sql;

select

myfield1,

myfield2,

nvl(whenmodified,whencreated) as lastupdate



from u_def_ext_person

where

personid = '~(gpv.id)'

;]

  {

    "myfield1":"~(myfield1;json)",

    "myfield2":"~(myfield2;json)",

    "lastupdate":"~(lastupdate;json)"

  },

[/tlist_sql]

  {}

]

And lastly, you'll need to install a plugin that gives permission to the slide in page to make the insert and update API calls (attached).  You'll need to edit the files inside to include your extension tables/fields.

Download: Contact Extension Plugin