Editor with stored procedures

Khalid Teli

Hi @allan

I understand that Editor cant be used with stored procedures but I did a bit if research and found a way to do it with raw SQL query.

1) JS code

<script type="text/javascript" language="javascript">
var table;
var filter_product_code;
var filter_product_name;


  editor = new $.fn.dataTable.Editor( {
        ajax:  "/xxxx_fetch.php",
        table: "#example",
        idSrc:  'contract_id',

        fields: [
          {
                label: "Contract ID:",
                name: "contract_id"
            }, 

            {
                label: " Size1:",
                name: "This has to be a dynamic field"
            },

             {
                label: "Size2:",
                name: "This has to be a dynamic field"
            }
        ]
    } );

var columns = [];

 $('#example').on( 'click', 'tbody td:not(:first-child)', function (e) {
       editor.inline( this, {
    } );
    } );

Function to populate table

function getDT(filter_product_code,filter_product_name) {

  $.ajax({
                type:'POST',

         url: "/xxx_fetch.php",
          data:  {filter_product_code: JSON.stringify(filter_product_code),
           filter_product_name: JSON.stringify(filter_product_name)},

              success: function (data) {
        data = JSON.parse(data);
        columnNames = Object.keys(data.data[0]);
        for (var i in columnNames) {
          columns.push({data: columnNames[i], 
                    title: columnNames[i]});
        }


         if ( $.fn.dataTable.isDataTable( '#example' ) ) {
    $('#example').DataTable().destroy();
    $('#example').empty();
}

      table =    $('#example').DataTable( {
          data: data.data,
            columns: columns,
            colReorder: true,
             dom: "Bfrtip",
              destroy: true,
               select: true,
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ]
        } );

//When the editing is done the datable has to be refreshed again to reflect / show the changes

    editor.on( 'postSubmit', function (  e, json, data, action, xhr  ) {
       getDT(filter_product_code,filter_product_name);
          });

//This select function gets the data which will be used in WHERE sql query to insert data in base table
table.on( 'select', function ( e, dt, type, indexes ) {

    var abc = table.rows({selected:true} ).data().toArray();
    var pcode = abc[0].product_code;
    var sdate = abc[0].start_date;
    var mname = abc[0].member_name;
    $.ajax({
            type:'POST',
           url: "/Editor/Editor-1.9.5/controllers/contracts/contracts_forecast_fetch.php",
          data:  {pcode: JSON.stringify(pcode),
            sdate: JSON.stringify(sdate),
            mname: JSON.stringify(mname)

    },
     });

     } );


     }
      });

    }


$(document).ready(function() {

//When the button is clicked the function is called again ro populate the tablke with filtered data

    $('#filter').click(function(){
       filte_product_code = $('#filter_product_code').val();
       filter_product_name = $('#filter_product_name').val();

       if(filter_product_code != '' && filter_product_name != '')
       {
        getDT( filter_product_code, filter_product_name);
       }
       else
       {
        alert('Select Both filter option');

       }
      });

    } );

Two main issues I am facing is

1) When is click in the filter , the expected data gets sent to server and server returns correct data but the issue is , the previous datatable doesn't get destroyed , it add the data vertically with each filtered data. In other words, each filtered data will be added vertically to already displayed table

2) As you might have noticed with the editor , the editor field names need to be changed dynamically with each returned json. because each time data is returned either number of columns change or the heading name change.
Is there a way to populate the editor fields dynamically much like we did for datatable?

Thank you
Kind Regards,
KT

Khalid Teli

My serverside code is just simple few SQL queries:

**This one just calls a stored procedure and pass parameters received from post variables **

    if (isset($_POST["filter_product_code"]) && isset($_POST["filter_product_name"]))
{

  $abc  = $_POST["filter_product_name"];
$def = $_POST["filter_product_code"];


  $rawquery = "CALL contract_forecast($abc, $def)";
   $data = $db->sql( $rawquery )->fetchAll();
   echo json_encode( array(
      'data' => $data
   ) );


}

This checks if it is in edit mode and then updates the base table based on different conditions

if ( isset($_POST['pcode'])  && isset($_POST['sdate'])){


    $_SESSION['pc'] = $_POST['pcode'];
    $_SESSION['sd'] = $_POST['sdate'];
    $_SESSION['mn'] = $_POST['mname'];

}


if ( isset($_POST['action']) &&  $_POST['action'] === 'edit') {

 $array =  $_POST['data'];

   $initialkey = array_key_first($array);
   $firstkey = array_key_first($array[$initialkey]);
   $firstkeyvalue = $array[$initialkey][$firstkey];

   $mn = $_SESSION['mn'];
   $sd = $_SESSION['sd'];
   $pc = $_SESSION['pc'];

  $rawquery2 = "UPDATE contracts_copy c  LEFT JOIN products AS p ON p.product_id = c.product_id_fk SET c.ord_qty = '$firstkeyvalue' WHERE p.product_code = $pc  AND c.member_name = $mn AND c.type_of = '$firstkey'";
  $rawquery3 = "SELECT * FROM  ccontracts_copy c  LEFT JOIN crg_products AS p ON p.product_id = c.product_id_fk  WHERE c.ord_qty = '$firstkeyvalue' AND p.product_code = $pc  AND c.member_name = $mn AND c.type_of = '$firstkey'";

 $db->sql( $rawquery2 );
 $updateddata = $db->sql( $rawquery3)->fetchall();

  echo json_encode( array(
      'datanew' => $updateddata
   ) );





}

allan

Could you give me a link to your page showing the issue please? I'm not seeing why the data would be getting added to the existing table there - the destroy and empty look correct to me.

Thanks,
Allan

Khalid Teli

@allan Thank you.
I have managed to sort the first problem out. After trying different things it turns out because of the changing no of columns for each table draw I had to empty table headers (not sure if this was the only problem!):

      if ( $.fn.dataTable.isDataTable( '#contracts_forecast' ) ) {

      $('#example').DataTable().clear();
      $('#example').DataTable().destroy();
       $('#example' + " tbody").empty();
        $('#examplet' + " thead").empty();

    }

And the re initialize table again.

Can you please help me to address this issue

2) As you might have noticed with the editor , the editor field names need to be changed dynamically with each returned json. because each time data is returned either number of columns change or the heading name change.
Is there a way to populate the editor fields dynamically much like we did for datatable?

Thank you
As always, appreciate your help

allan

Is there a way to populate the editor fields dynamically much like we did for datatable?

Yes - use clear() to remove old fields and add() to create new fields.

Allan

Khalid Teli

@allan Thank you.

I am sorry but I don't understand how can I add fields to the editor based on dynamic columns returned. I mean how does the editor know how many fields should be added based on no. of columns returned ?

For example if the retuned data has 6 columns there should be 6 editor fields initialized and when the returned data contains 2 columns there should be 2 editor fields initialized? So how I tell that to Editor?

**How I approached to do this is ** :

CASE 1 : In the case where there are 6 size columns returned(size1,size2.....size6) and then I use ColumnNames[i]to define filed.names. as shown in code below and this works fine

CASE2:
in case when the returned JSON contains only two size columns (size1 and size2)
How can I tell editor to use only first two fields and ignore rest fields

                  success: function (data) {
            data = JSON.parse(data);
            columnNames = Object.keys(data.data[0]);

            for (var i in columnNames) {
              columns.push({data: columnNames[i], 
                        title: capitalizeFirstLetter(columnNames[i])});

            }

               editor = new $.fn.dataTable.Editor( {
            ajax:  "/xxx_fetch.php",
            table: "#contracts_forecast",
            idSrc:  'contract_id',

            fields: [

                {
                    label: "Size1:",
                    name: columnNames[1]
                },

                 {
                    label: "Size2:",
                    name: columnNames[2]
                },
                  {
                    label: "Size3:",
                    name: columnNames[3]
                },

                 {
                    label: "Size4:",
                    name: columnNames[4]
                },
                {
                    label: "Size5:",
                    name: columnNames[5]
                },
                {
                    label: "Size6:",
                    name: columnNames[6]
                },

            ]
        } );
       var table = $('#contracts_forecast').DataTable({
              data: data.data,
                columns: columns,
                dom: "Bfrtip",
                   select: true,
                    //retrieve:true,
            buttons: [
                { extend: "create", editor: editor },
                { extend: "edit",   editor: editor },
                { extend: "remove", editor: editor }
            ],
             "columnDefs": [
                {
                    "targets": [0],
                    "visible": false,
                    "searchable": false
                },
                 { className: "tablecolumns", targets: "_all" },
                ]
            } );
     }

Thank you

kthorngren

Use the loop in line 5 to populate an array of fields lie you have for the columns. The loop will build both the columns and fields. The use the fields variable to assign the fields like you do with columns.

Kevin

Khalid Teli

@kthorngren That is amazing. Works perfectly for my use case. Thank you very much.

for (var i in columnNames) {

          columns.push({data: columnNames[i], 
                    title: capitalizeFirstLetter(columnNames[i])});
           fields.push({name: columnNames[i]});                        
        }
        editor = new $.fn.dataTable.Editor( {
        ajax:  "/XXX/fetch.php",
        table: "#contracts_forecast",
        idSrc:  'contract_id',
        fields: fields
  } );

As always appreciate your support. You guys are just amazing