// ! Support file only, run Grid.html instead ! //. This file is used as layout url, data url and SaveUrl //. Generates layout for FastGrid by getlayout() function, data for FastGrid by getdata() function or saves received changes to database by set() function // ----------------------------------------------------------------------------------------------------------------------------- //. Reads data from database and returns them in JSON function getdef(req,res){ var sql = require('better-sqlite3'), db = new sql('SQLite.db'); //. It uses SQLite3 database engine on database in file SQLite.sb try { var rows = db.prepare("SELECT MIN(Week) as min,MAX(Week) as max FROM TableData WHERE Week>0 AND Week<53").all(); } //. Selects the whole table and calls the process function with the results catch(e){ err("SQL select error: "+e.message,res); return; } var r = rows[0]; var Cols = [], CSum = "", DRes = { Sum:'sum()' }, FRes = { Sum:'sum(null,"Body")' }, DDef = {}; for(var i=r.min;i<=r.max;i++){ var Week = "W" + i; Cols.push({ id:Week, Type:'Number', Format:',0.##' }); //. Column definitions CSum += Week+"+"; //. Right fixed result column formula DRes[Week] = 'sum()'; //. Parent row formulas FRes[Week] = 'sum(null,\'Body\')'; //. Result row formulas DDef[Week] = 0; //. Default values for data rows } var Data = { Cfg: { id:'ResourceGrid', DateFormatOut:"yyyy-MM-dd", //. Date format for all saved dates SaveUrl: { Url:"Grid.js?func=set", Debug:1 }, //. Uploads changes to this url SaveFlags:"Changed,State,Quote,Parent,ParentId", //. Uploads parent row ids for moved / added rows SaveIdCols:"Project", //. Uses values of Parent column instead of row id for upload PdfUrl:'../Pdf.js?func=getpdf', //. Uses this url for PDF export RowChanges:239, //. Marks moved rows only between parents, to support ParentId in SaveFlags }, DefCols: { Col: { UseMove:1 } }, LeftCols: [ { id: 'Panel', D:'Panel', LeftIcons:"SelectRow,DeleteRow,AddRow" }, { id: 'Project', Width:200, Type:'String' } ], Cols: Cols, RightCols: [ { id:'Sum', Width:60, Type:'Number', Formula:CSum.slice(0,-1), NoEdit:1, Format:',0.##' } ], DefRows: { Row: { V: { Project:'New resource' }, CD:'', DragId:'Row', Accept:'', V: DDef }, Node: { V: { Project:'New project' }, CD:'Row', DragId:'Node', Accept:'Row', NoEdit:1, F: DRes, A:{ Project: { TextStyle:1, NoEdit:0 }} } }, Body: { CD:"Node", Accept:"Node", Tree:"Project" }, Head: [ { D:'Header', id:'Header', V: { Project:'Project / resource' } } ], Foot: [ { D:'Node', id:"Results", F: FRes, V:{ Project:'Total results'}, A: { Project: { NoEdit:1 } } } ], Top: [ { id:"Data", Cells:"Settings,Save,Reload,PrintPdf,Export,Copy,AddRowEnd,AddRows,Undo,Redo" }, "Layout" ], Media: { Tablet: { DefCols: { Col: { UseMove:0 } }, LeftCols:[{ id:"Panel",UseMove:1 } ] }, Mobile: { LeftCols: [{ id:'Project', Width:180 }] } } } end("FGrid.Data="+JSON.stringify(Data),res); } // ----------------------------------------------------------------------------------------------------------------------------- //. Reads data from database and returns them in JSON function getdata(req,res){ var sql = require('better-sqlite3'), db = new sql('SQLite.db'); //. It uses SQLite3 database engine on database in file SQLite.sb try { var rows = db.prepare("SELECT * FROM TableData WHERE Week>0 AND Week<53 ORDER BY Project,Resource").all(); } //. Selects the whole table and calls the process function with the results catch(e){ err("SQL select error: "+e.message,res); return; } var Prj = null, Res = null, s = "FGrid.Data={Body:["; for(var i=0;i