diff --git a/Excel2SQL/CTfromExcel.m b/Excel2SQL/CTfromExcel.m new file mode 100644 index 0000000..2b2a328 --- /dev/null +++ b/Excel2SQL/CTfromExcel.m @@ -0,0 +1,400 @@ +function [CT, scapula, glenoid, glenoid_density, humerus, muscle] = CTfromExcel(shoulder, patient, rawExcel) +% ctFromExcel build the ct structure array from the excelRaw, patient and +% shoulder structure array. +% ct contains all data directly related to the imaging + +fprintf('\nGet CT & glenoid from Excel...'); + +% define CT structure array +CT = struct(... + 'CT_id', [], ... + 'shoulder_id', [], ... + 'date', [], ... + 'kernel', [], ... + 'pixel_size', [], ... + 'slice_spacing', [], ... + 'slice_thickness', [], ... + 'tension', [], ... + 'current', [], ... + 'manufacturer', [], ... + 'model', [], ... + 'institution', [], ... + 'folder_name', [] ... + ); + +% define scapula array +scapula = struct(... + 'CT_id' , [], ... + 'CTangle', []... + ); + +% define glenoid structure array +glenoid = struct(... + 'CT_id', [], ... + 'radius', [], ... + 'sphericity', [], ... + 'biconcave', [], ... + 'depth', [], ... + 'width', [], ... + 'height', [], ... + 'version_amp', [], ... + 'version_orient', [], ... + 'center_PA', [], ... + 'center_IS', [], ... + 'center_ML', [], ... + 'version', [], ... + 'inclination', [], ... + 'version_2D', [], ... + 'walch_class', [] ... + ); + +% define glenoid_density structure array +glenoid_density = struct(... + 'CT_id',[], ... + 'CO' ,[], ... + 'SC' ,[], ... + 'ST' ,[], ... + 'T1' ,[], ... + 'T2' ,[], ... + 'T3' ,[], ... + 'RE' ,[] ... + ); + +% define humerus structure array +humerus = struct(... + 'CT_id' ,[], ... + 'joint_radius' ,[], ... + 'head_radius' ,[], ... + 'SHsublux_ampl' ,[], ... + 'SHsublux_orient',[], ... + 'GHsublux_ampl' ,[], ... + 'GHsublux_orient',[], ... + 'SHsublux_2D' ,[], ... + 'GHsublux_2D' ,[] ... +); + +%define muscle structure array +muscle = struct(... + 'CT_id',[], ... + 'SSA' ,[],... + 'SSI' ,[],... + 'SSO' ,[],... + 'SSD' ,[],... + 'ISA' ,[],... + 'ISI' ,[],... + 'ISO' ,[],... + 'ISD' ,[],... + 'SCA' ,[],... + 'SCI' ,[],... + 'SCO' ,[],... + 'SCD' ,[],... + 'TMA' ,[],... + 'TMI' ,[],... + 'TMO' ,[],... + 'TMD' ,[] ... + ); + +% get column of variables +header = rawExcel(1,:); +sCase_id_col = find(strcmp([header],'sCase.id')); +IPP_col = find(strcmp([header],'patient.IPP')); +side_col = find(strcmp([header],'shoulder.side')); + +CT_date_col = find(strcmp([header],'CT.date')); +CT_kernel_col = find(strcmp([header],'CT.kernel')); +CT_pixel_size_col = find(strcmp([header],'CT.pixel_size')); +CT_slice_spacing_col = find(strcmp([header],'CT.slice_spacing')); +CT_slice_thickness_col = find(strcmp([header],'CT.slice_thickness')); +CT_tension_col = find(strcmp([header],'CT.tension')); +CT_current_col = find(strcmp([header],'CT.current')); +CT_manufacturer_col = find(strcmp([header],'CT.manufacturer')); +CT_model_col = find(strcmp([header],'CT.model')); +CT_institution_col = find(strcmp([header],'CT.institution')); + +scapula_CTangle_col = find(strcmp([header],'scapula.CTangle')); + +glenoid_radius_col = find(strcmp([header],'glenoid.radius')); +% glenoid_sphericity_col = find(strcmp([header],'glenoid.sphericity')); +% glenoid_biconcave_col = find(strcmp([header],'glenoid.biconcave')); +glenoid_depth_col = find(strcmp([header],'glenoid.depth')); +glenoid_width_col = find(strcmp([header],'glenoid.width')); +glenoid_height_col = find(strcmp([header],'glenoid.height')); +glenoid_version_amp_col = find(strcmp([header],'glenoid.version_amp')); +glenoid_version_orient_col = find(strcmp([header],'glenoid.version_orient')); +glenoid_center_PA_col = find(strcmp([header],'glenoid.center_PA')); +glenoid_center_IS_col = find(strcmp([header],'glenoid.center_IS')); +glenoid_center_ML_col = find(strcmp([header],'glenoid.center_ML')); +glenoid_version_col = find(strcmp([header],'glenoid.version')); +glenoid_inclination_col = find(strcmp([header],'glenoid.inclination')); +glenoid_version_2D_col = find(strcmp([header],'glenoid.version_2D')); +glenoid_walch_class_col = find(strcmp([header],'glenoid.walch_class')); + +glenoid_density_CO_col = find(strcmp([header],'glenoid_density.CO')); +glenoid_density_SC_col = find(strcmp([header],'glenoid_density.SC')); +glenoid_density_ST_col = find(strcmp([header],'glenoid_density.ST')); +glenoid_density_T1_col = find(strcmp([header],'glenoid_density.T1')); +glenoid_density_T2_col = find(strcmp([header],'glenoid_density.T2')); +glenoid_density_T3_col = find(strcmp([header],'glenoid_density.T3')); +glenoid_density_RE_col = find(strcmp([header],'glenoid_density.RE')); + +humerus_joint_radius_col = find(strcmp([header],'humerus.joint_radius')); +humerus_head_radius_col = find(strcmp([header],'humerus.head_radius')); +humerus_SHsublux_ampl_col = find(strcmp([header],'humerus.SHsublux_ampl')); +humerus_SHsublux_orient_col = find(strcmp([header],'humerus.SHsublux_orient')); +humerus_GHsublux_ampl_col = find(strcmp([header],'humerus.GHsublux_ampl')); +humerus_GHsublux_orient_col = find(strcmp([header],'humerus.GHsublux_orient')); +humerus_SHsublux_2D_col = find(strcmp([header],'humerus.SHsublux_2D')); +humerus_GHsublux_2D_col = find(strcmp([header],'humerus.GHsublux_2D')); + +muscle_SSA_col = find(strcmp([header],'muscle.SSA')); +muscle_SSI_col = find(strcmp([header],'muscle.SSI')); +muscle_SSO_col = find(strcmp([header],'muscle.SSO')); +muscle_SSD_col = find(strcmp([header],'muscle.SSD')); +muscle_ISA_col = find(strcmp([header],'muscle.ISA')); +muscle_ISI_col = find(strcmp([header],'muscle.ISI')); +muscle_ISO_col = find(strcmp([header],'muscle.ISO')); +muscle_ISD_col = find(strcmp([header],'muscle.ISD')); +muscle_SCA_col = find(strcmp([header],'muscle.SCA')); +muscle_SCI_col = find(strcmp([header],'muscle.SCI')); +muscle_SCO_col = find(strcmp([header],'muscle.SCO')); +muscle_SCD_col = find(strcmp([header],'muscle.SCD')); +muscle_TMA_col = find(strcmp([header],'muscle.TMA')); +muscle_TMI_col = find(strcmp([header],'muscle.TMI')); +muscle_TMO_col = find(strcmp([header],'muscle.TMO')); +muscle_TMD_col = find(strcmp([header],'muscle.TMD')); + +% initialize counters +[rowN, ~] = size(rawExcel); +CT_id = 0; +CT_idx = 0; +scapula_idx = 0; +glenoid_idx = 0; +glenoid_density_idx = 0; +humerus_idx = 0; +muscle_idx = 0; + +% loop over rows of Excel table +for row_idx = 2:rowN + row = rawExcel(row_idx, :); % get the entire row + sCaseE = row{sCase_id_col}; + if ~isnan(sCaseE) % check that this sCase is not empty + IPP = row{IPP_col}; % IPP of the patient + patient_idx = find([patient.IPP] == IPP); + % get patient index of this sCase + % this patient should already exist in patient structure array and + % should be unique + if ~isempty(patient_idx) + patient_id = patient(patient_idx).patient_id; % get patient_id + + shoulder_idx = find([shoulder.patient_id] == patient_id); % get shoulder_id + % should be 1 or 2 (R/L), but not 0 (test it however) + shoulder_idxN = length(shoulder_idx); + side = row{side_col}; + switch shoulder_idxN + case 0 + % this sCase patient is not in the shoulder array + % report an error (probably not side identified) + fprintf('\nsCase %s not in shoulder array', sCaseE); + addCT = 0; + case 1 + % this sCase patient has 1 match in the shoulder array + % so we have to check side + if side == shoulder(shoulder_idx).side + shoulder_id = shoulder(shoulder_idx).shoulder_id; + addCT = 1; + else + % not same side so report a problem, probably side not + % defined + fprintf('\nsCase %s in shoulder array but not same side', sCaseE); + end + case 2 + % both shoulder sides of this patient are in the + % shoulder array, so we have to check side + if side == shoulder(shoulder_idx(1)).side + shoulder_id = shoulder(shoulder_idx(1)).shoulder_id; + addCT = 1; + else + if side == shoulder(shoulder_idx(2)).side + shoulder_id = shoulder(shoulder_idx(2)).shoulder_id; + addCT = 1; + end + end + end + % check if CT kernel is defined + kernel = row{CT_kernel_col}; + if isnan(kernel) + addCT = 0; + end + + if addCT % add CT to the array + CT_id = CT_id + 1; + CT_idx = CT_id; + + CT_date = row{CT_date_col}; + CT_date = datetime(CT_date,'ConvertFrom','excel','InputFormat','dd.MM.yyyy'); + CT_date.Format = 'yyyy-MM-dd'; + + CT(CT_idx).CT_id = CT_id; + CT(CT_idx).shoulder_id = shoulder_id; + CT(CT_idx).date = CT_date; + CT(CT_idx).kernel = row{CT_kernel_col}; + CT(CT_idx).pixel_size = row{CT_pixel_size_col}; + CT(CT_idx).slice_spacing = row{CT_slice_spacing_col}; + CT(CT_idx).slice_thickness = row{CT_slice_thickness_col}; + CT(CT_idx).tension = row{CT_tension_col}; + CT(CT_idx).current = row{CT_current_col}; + CT(CT_idx).manufacturer = row{CT_manufacturer_col}; + CT(CT_idx).model = row{CT_model_col}; + CT(CT_idx).institution = row{CT_institution_col}; + CT(CT_idx).folder_name = sCaseE; + + % check for adding in scapula structure array + CTangle = row{scapula_CTangle_col}; + if ~isnan(CTangle) + scapula_idx = scapula_idx + 1; + + scapula(scapula_idx).CT_id = CT_id; + scapula(scapula_idx).CTangle = CTangle; + end + + % check for adding in glenoid structure array + radius = row{glenoid_radius_col}; + if ~isnan(radius) + % Assumes that all glenoid data are present & valid is + % radius is defined + glenoid_idx = glenoid_idx + 1; + +% sphericity = row{glenoid_sphericity_col}; +% biconcave = row{glenoid_biconcave_col}; + depth = row{glenoid_depth_col}; + width = row{glenoid_width_col}; + height = row{glenoid_height_col}; + version_amp = row{glenoid_version_amp_col}; + version_orient = row{glenoid_version_orient_col}; + center_PA = row{glenoid_center_PA_col}; + center_IS = row{glenoid_center_IS_col}; + center_ML = row{glenoid_center_ML_col}; + version = row{glenoid_version_col}; + inclination = row{glenoid_inclination_col}; + version_2D = row{glenoid_version_2D_col}; + walch_class = row{glenoid_walch_class_col}; + + glenoid(glenoid_idx).CT_id = CT_id; + glenoid(glenoid_idx).radius = radius; +% glenoid(glenoid_idx).sphericity = sphericity; +% glenoid(glenoid_idx).biconcave = biconcave; + glenoid(glenoid_idx).depth = depth; + glenoid(glenoid_idx).width = width; + glenoid(glenoid_idx).height = height; + glenoid(glenoid_idx).version_amp = version_amp; + glenoid(glenoid_idx).version_orient = version_orient; + glenoid(glenoid_idx).center_PA = center_PA; + glenoid(glenoid_idx).center_IS = center_IS; + glenoid(glenoid_idx).center_ML = center_ML; + glenoid(glenoid_idx).version = version; + glenoid(glenoid_idx).inclination = inclination; + glenoid(glenoid_idx).version_2D = version_2D; + glenoid(glenoid_idx).walch_class = walch_class; + end + + % check for adding in glenoid_density structure array + CO = row{glenoid_density_CO_col}; + if ~isnan(CO) + % Assumes that all glenoid_density data are present & valid is + % CO is defined. RE can be empty + glenoid_density_idx = glenoid_density_idx + 1; + + SC = row{glenoid_density_SC_col}; + ST = row{glenoid_density_ST_col}; + T1 = row{glenoid_density_T1_col}; + T2 = row{glenoid_density_T2_col}; + T3 = row{glenoid_density_T3_col}; + RE = row{glenoid_density_RE_col}; + + glenoid_density(glenoid_density_idx).CT_id = CT_id; + glenoid_density(glenoid_density_idx).CO = CO; + glenoid_density(glenoid_density_idx).SC = SC; + glenoid_density(glenoid_density_idx).ST = ST; + glenoid_density(glenoid_density_idx).T1 = T1; + glenoid_density(glenoid_density_idx).T2 = T2; + glenoid_density(glenoid_density_idx).T3 = T3; + glenoid_density(glenoid_density_idx).RE = RE; + end + + % check for adding in humerus structure array + head_radius = row{humerus_head_radius_col}; + if ~isnan(head_radius) + % Assumes that all humerus data are present & valid is + % head_radius is defined. + humerus_idx = humerus_idx + 1; + + joint_radius = row{humerus_joint_radius_col}; + SHsublux_ampl = row{humerus_SHsublux_ampl_col}; + SHsublux_orient = row{humerus_SHsublux_orient_col}; + GHsublux_ampl = row{humerus_GHsublux_ampl_col}; + GHsublux_orient = row{humerus_GHsublux_orient_col}; + SHsublux_2D = row{humerus_SHsublux_2D_col}; + GHsublux_2D = row{humerus_GHsublux_2D_col}; + + humerus(humerus_idx).CT_id = CT_id; + humerus(humerus_idx).head_radius = head_radius; + humerus(humerus_idx).joint_radius = joint_radius; + humerus(humerus_idx).SHsublux_ampl = SHsublux_ampl; + humerus(humerus_idx).SHsublux_orient = SHsublux_orient; + humerus(humerus_idx).GHsublux_ampl = GHsublux_ampl; + humerus(humerus_idx).GHsublux_orient = GHsublux_orient; + humerus(humerus_idx).SHsublux_2D = SHsublux_2D; + humerus(humerus_idx).GHsublux_2D = GHsublux_2D; + end + + % check for adding in muscle structure array + SSA = row{muscle_SSA_col}; + if ~isnan(SSA) + % Assumes that all muscle data are present & valid is + % SSA is defined. + muscle_idx = muscle_idx + 1; + + SSA = row{muscle_SSA_col}; + SSI = row{muscle_SSI_col}; + SSO = row{muscle_SSO_col}; + SSD = row{muscle_SSD_col}; + ISA = row{muscle_ISA_col}; + ISI = row{muscle_ISI_col}; + ISO = row{muscle_ISO_col}; + ISD = row{muscle_ISD_col}; + SCA = row{muscle_SCA_col}; + SCI = row{muscle_SCI_col}; + SCO = row{muscle_SCO_col}; + SCD = row{muscle_SCD_col}; + TMA = row{muscle_ISA_col}; + TMI = row{muscle_ISI_col}; + TMO = row{muscle_ISO_col}; + TMD = row{muscle_ISD_col}; + + muscle(muscle_idx).CT_id = CT_id; + muscle(muscle_idx).SSA = SSA; + muscle(muscle_idx).SSI = SSI; + muscle(muscle_idx).SSO = SSO; + muscle(muscle_idx).SSD = SSD; + muscle(muscle_idx).ISA = ISA; + muscle(muscle_idx).ISI = ISI; + muscle(muscle_idx).ISO = ISO; + muscle(muscle_idx).ISD = ISD; + muscle(muscle_idx).SCA = SCA; + muscle(muscle_idx).SCI = SCI; + muscle(muscle_idx).SCO = SCO; + muscle(muscle_idx).SCD = SCD; + muscle(muscle_idx).TMA = TMA; + muscle(muscle_idx).TMI = TMI; + muscle(muscle_idx).TMO = TMO; + muscle(muscle_idx).TMD = TMD; + end + end + end + end +end + +fprintf(' Done\n'); + +end + diff --git a/Excel2SQL/CTtoSQL.m b/Excel2SQL/CTtoSQL.m new file mode 100644 index 0000000..ff26efa --- /dev/null +++ b/Excel2SQL/CTtoSQL.m @@ -0,0 +1,50 @@ +function CTtoSQL(CT, conn) +%CTTOSQL fill ct structure array in mySQL +% Detailed explanation goes here + +fprintf('\nCT to mySQL... '); + +testquery= 'SET FOREIGN_KEY_CHECKS = 0'; +exec(conn,testquery); + +% Empty CT table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE CT'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +testquery= 'SET FOREIGN_KEY_CHECKS = 1'; +exec(conn,testquery); + +colnames = {'CT_id','shoulder_id','date','kernel','pixel_size',... + 'slice_spacing','slice_thickness','tension','current',... + 'manufacturer','model','institution','folder_name'}; + +% Loop on ct structure array +for i=1:numel(CT) + CT_id = CT(i).CT_id; + shoulder_id = CT(i).shoulder_id; + date = datestr(CT(i).date,'yyyy-mm-dd'); + kernel = CT(i).kernel; + pixel_size = CT(i).pixel_size; + slice_spacing = CT(i).slice_spacing; + slice_thickness = CT(i).slice_thickness; + tension = CT(i).tension; + current = CT(i).current; + manufacturer = CT(i).manufacturer; + model = CT(i).model; + institution = CT(i).institution; + folder_name = CT(i).folder_name; + + exdata = {CT_id,shoulder_id,date,kernel,pixel_size,... + slice_spacing,slice_thickness,tension,current,... + manufacturer,model,institution,folder_name}; + % insert data in SQL + datainsert(conn,'CT',colnames,exdata); +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/Copy_of_sCaseToSQL.m b/Excel2SQL/Copy_of_sCaseToSQL.m new file mode 100644 index 0000000..6e16b00 --- /dev/null +++ b/Excel2SQL/Copy_of_sCaseToSQL.m @@ -0,0 +1,38 @@ +function sCaseToSQL(sCase, conn) +%PCASETOSQL fill sCase structure array in mySQL +% Detailed explanation goes here + +fprintf('\nsCase to mySQL... '); + +% Empty sCase table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE sCase'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +colnames = {'sCase_id','shoulder_id','folder_name'}; + +% Loop on pCase structure array +for i=1:numel(sCase) + sCase_id = sCase(i).sCase_id; + shoulder_id = sCase(i).shoulder_id; + pathology_id = sCase(i).pathology_id; + treatment_id = pCase(i).treatment_id; + operation_date = pCase(i).operation_date; + if ~isnat(operation_date) + operation_date = datestr(pCase(i).operation_date,'yyyy-mm-dd'); + else + operation_date = ''; + end + folder_name = pCase(i).folder_name; + + exdata = {pCase_id,shoulder_id,pathology_id,treatment_id,operation_date,folder_name}; + % insert data in SQL + datainsert(conn,'pCase',colnames,exdata); +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/MainExcel2SQL.m b/Excel2SQL/MainExcel2SQL.m new file mode 100644 index 0000000..4a29ea3 --- /dev/null +++ b/Excel2SQL/MainExcel2SQL.m @@ -0,0 +1,134 @@ +%% MainExcel2SQL +% +% This script reads the shoulder database Excel file and +% write in the mySQL database. The existing tables are overwritten and +% auto-counter are reset. + +% Author: Alexandre Terrier, EPFL-LBO +% Date: 2016-09-29 +% + +%% +% Initialization + +clearvars; % Clear all matlab variables + +%% +%************************************************************************** +% +% Read data from Excel +% + +excelRaw = rawFromExcel(); + +%% +% Read from Excel: pathology array + +diagnosisList = diagnosisListFromExcel(excelRaw); + +%% +% Read from Excel: treatement array + +treatmentList = treatmentListFromExcel(excelRaw); + +%% +% Read from Excel: patient structure array + +patient = patientFromExcel(excelRaw); + +%% +% Read from Excel: shoulder structure array + +shoulder = shoulderFromExcel(patient, excelRaw); + +%% +% Read from Excel: sCase struture array, and associated + +[sCase, diagnosis, treatment, outcome, study] = sCaseFromExcel(... + shoulder, patient, diagnosisList, treatmentList, excelRaw); + +%% +% Read from Excel: ct structure array, nd associated + +[CT, scapula, glenoid, glenoid_density, humerus, muscle] = CTfromExcel(... + shoulder, patient, excelRaw); + +%% +%************************************************************************** +% +% Write data to mySQL database + +%% +% open mySQL connection + +conn = openSQL(); + +%% +% Initialize tables of mySQL database + +initializeSQL(conn); + +%% +% Write in mySQL: patient table + +patientToSQL(patient, conn); + +%% +% Write in mySQL: anonymity table + +anonymityToSQL(patient, conn); + +%% +% Write in mySQL: shoulder table + +shoulderToSQL(shoulder, conn); + +%% +% Write in mySQL: sCase + +sCaseToSQL(sCase, conn); + +%% +% Write in mySQL: CT + +CTtoSQL(CT, conn); + +%% +% Write in mySQL: glenoid + +scapulaToSQL(scapula, conn); + +%% +% Write in mySQL: glenoid + +glenoidToSQL(glenoid, conn); + +%% +% Write in mySQL: diagnosisList + +diagnosisListToSQL(diagnosisList, conn); + +%% +% Write in mySQL: diagnosis + +diagnosisToSQL(diagnosis, conn); + +%% +% Write in mySQL: treatmentList + +treatmentListToSQL(treatmentList, conn); + +%% +% Write in mySQL: treatment + +treatmentToSQL(treatment, conn); + +%% +% Write in mySQL: outcome + +outcomeToSQL(outcome, conn); + +%% +% Close mySQL database + +closeSQL(conn); diff --git a/Excel2SQL/anonymityToSQL.m b/Excel2SQL/anonymityToSQL.m new file mode 100644 index 0000000..f87163c --- /dev/null +++ b/Excel2SQL/anonymityToSQL.m @@ -0,0 +1,29 @@ +function anonymity(patient, conn) +%PATIENDBUPDATE fill patient structure array in mySQL +% Detailed explanation goes here + +fprintf('\nanonymity to mySQL... '); + +% Empty anonymity table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE anonymity'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +colnames = {'ipp','initials'}; + +% Loop on patient structure array +for i=1:numel(patient) + ipp = patient(i).IPP; + initials = patient(i).initials; + + exdata = {ipp,initials}; + % insert data in SQL + datainsert(conn,'anonymity',colnames,exdata); +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/closeSQL.m b/Excel2SQL/closeSQL.m new file mode 100644 index 0000000..438f213 --- /dev/null +++ b/Excel2SQL/closeSQL.m @@ -0,0 +1,12 @@ +function closeSQL(conn) +%CLOSESQL Close SQL connection +% Detailed explanation goes here + +fprintf('\nClose mySQL... '); + +close(conn); % close SQL connection + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/diagnosisListFromExcel.m b/Excel2SQL/diagnosisListFromExcel.m new file mode 100644 index 0000000..515c573 --- /dev/null +++ b/Excel2SQL/diagnosisListFromExcel.m @@ -0,0 +1,51 @@ +function [ diagnosisList ] = diagnosisListFromExcel( rawExcel ) +%DIAGNOSISLISTFROMEXCEL Summary of this function goes here +% Detailed explanation goes here + +fprintf('\nGet diagnosisList from Excel... '); + +% define diagnosis structure array +diagnosisList = struct(... + 'diagnosisList_id' ,[], ... + 'name' ,[], ... + 'description' ,[] ... + ); + +% set normal subnormaland diagnosis names +diagnosisList(1).diagnosisList_id = 1; +diagnosisList(1).name = 'normal'; +diagnosisList(1).description = 'fully normal'; + +diagnosisList(2).diagnosisList_id = 2; +diagnosisList(2).name = 'subnormal'; +diagnosisList(2).description = 'no OA but small abnormalities'; + +% get column of variables +header = rawExcel(1,:); +diagnosisName_col = find(strcmp([header],'diagnosis.name')); + +% loop over rows of Excel table +[rowN, ~] = size(rawExcel); +diagnosisList_id = 2; +for row_idx = 2:rowN + row = rawExcel(row_idx, :); % get the entire row + diagnosisName = row(diagnosisName_col); + diagnosisName = diagnosisName{1}; + if ~isempty(diagnosisName) & ~isnan(diagnosisName) + sameDiagnosis = find(strcmp({diagnosisList.name}, diagnosisName)==1); + % check if this diagnosis is already present in the array + if isempty(sameDiagnosis) + % add this diagnosis to the array + diagnosisList_id = diagnosisList_id + 1; + diagnosisList_idx = diagnosisList_id; + diagnosisList(diagnosisList_idx).diagnosisList_id = diagnosisList_id; + diagnosisList(diagnosisList_idx).name = diagnosisName; + diagnosisList(diagnosisList_idx).description = ''; + end + end +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/diagnosisListToSQL.m b/Excel2SQL/diagnosisListToSQL.m new file mode 100644 index 0000000..f1244c1 --- /dev/null +++ b/Excel2SQL/diagnosisListToSQL.m @@ -0,0 +1,36 @@ +function diagnosisListToSQL(diagnosisList, conn) +%diagnosisTOSQL Summary of this function goes here +% Detailed explanation goes here + +fprintf('\ndiagnosisList to mySQL...'); + +testquery= 'SET FOREIGN_KEY_CHECKS = 0'; +exec(conn,testquery); + +% Empty diagnosisList table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE diagnosisList'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +testquery= 'SET FOREIGN_KEY_CHECKS = 1'; +exec(conn,testquery); + +colnames = {'diagnosisList_id','name','description'}; + +% Loop on pCase structure array +for i=1:numel(diagnosisList) + diagnosisList_id = diagnosisList(i).diagnosisList_id; + name = diagnosisList(i).name; + description = diagnosisList(i).description; + + exdata = {diagnosisList_id,name,description}; + % insert data in SQL + datainsert(conn,'diagnosisList',colnames,exdata); +end + +fprintf(' Done\n'); + +end + diff --git a/Excel2SQL/diagnosisToSQL.m b/Excel2SQL/diagnosisToSQL.m new file mode 100644 index 0000000..ed124b8 --- /dev/null +++ b/Excel2SQL/diagnosisToSQL.m @@ -0,0 +1,32 @@ +function diagnosisToSQL(diagnosis, conn) +%diagnosisTOSQL Summary of this function goes here +% Detailed explanation goes here + +fprintf('\ndiagnosis to mySQL...'); + +% Empty diagnosis table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE diagnosis'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +colnames = {'diagnosis_id','sCase_id','diagnosisList_id','date','comment'}; + +% Loop on pCase structure array +for i=1:numel(diagnosis) + diagnosis_id = diagnosis(i).diagnosis_id; + sCase_id = diagnosis(i).sCase_id; + diagnosisList_id = diagnosis(i).diagnosisList_id; + date = diagnosis(i).date; + comment = diagnosis(i).comment; + + exdata = {diagnosis_id,sCase_id,diagnosisList_id,date,comment}; + % insert data in SQL + datainsert(conn,'diagnosis',colnames,exdata); +end + +fprintf(' Done\n'); + +end + diff --git a/Excel2SQL/glenoidToSQL.m b/Excel2SQL/glenoidToSQL.m new file mode 100644 index 0000000..f79a7d6 --- /dev/null +++ b/Excel2SQL/glenoidToSQL.m @@ -0,0 +1,69 @@ +function glenoidToSQL(glenoid, conn) +%GLENOIDTOSQL fill glenoid structure array in mySQL +% Detailed explanation goes here + +fprintf('\nglenoid to mySQL... '); + +% Empty glenoid table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE glenoid'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +colnames = {'CT_id','radius','sphericity','biconcave','depth',... + 'width','height','version_amp','version_orient',... + 'center_PA','center_IS','center_ML','version','inclination',... + 'version_2D','walch_class'}; + + +% Loop on glenoid structure array +for i=1:numel(glenoid) + + CT_id = glenoid(i).CT_id; + radius = glenoid(i).radius; + biconcave = ''; + sphericity = 0; +% sphericity = glenoid(i).sphericity; +% biconcave = glenoid(i).biconcave; + depth = glenoid(i).depth; + width = glenoid(i).width; + height = glenoid(i).height; + version_amp = glenoid(i).version_amp; + version_orient = glenoid(i).version_orient; + center_PA = glenoid(i).center_PA; + center_IS = glenoid(i).center_IS; + center_ML = glenoid(i).center_ML; + version = glenoid(i).version; + inclination = glenoid(i).inclination; + version_2D = glenoid(i).version_2D; + walch_class = glenoid(i).walch_class; + + if isnan(version_2D) + version_2D = ''; + end + if isnan(walch_class) + walch_class = ''; + end + +% if isnan(biconcave) +% biconcave = ''; +% end + if isnan(width) + width = ''; + height = ''; + end + + exdata = {CT_id,radius,sphericity,biconcave,depth,... + width,height,version_amp,version_orient,... + center_PA,center_IS,center_ML,version,inclination,... + version_2D,walch_class}; + + % insert data in SQL + + datainsert(conn,'glenoid',colnames,exdata); +end + +fprintf('Done\n'); + +end \ No newline at end of file diff --git a/Excel2SQL/initializeSQL.m b/Excel2SQL/initializeSQL.m new file mode 100644 index 0000000..53a8cf5 --- /dev/null +++ b/Excel2SQL/initializeSQL.m @@ -0,0 +1,134 @@ +function [ curs ] = initializeSQL( conn ) +%INITIALIZESQL Summary of this function goes here +% Detailed explanation goes here + +fprintf('\nInitialize mySQL database... '); + +testquery= 'SET FOREIGN_KEY_CHECKS = 0'; +exec(conn,testquery); + +% Empty diagnosis table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE diagnosis'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty diagnosisList table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE diagnosisList'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty treatment table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE treatment'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty treatmentList table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE treatmentList'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty outcome table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE outcome'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty study table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE study'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty studyList table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE studyList'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty glenoid table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE glenoid'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty glenoid_density table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE glenoid_density'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty humerus table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE humerus'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty muscle table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE muscle'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty scapula table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE scapula'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty CT table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE CT'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty shoulder table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE shoulder'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty patient table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE patient'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty anonymity table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE anonymity'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +% Empty sCase table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE sCase'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +testquery= 'SET FOREIGN_KEY_CHECKS = 1'; +exec(conn,testquery); + +fprintf('Done\n'); + +end \ No newline at end of file diff --git a/Excel2SQL/openSQL.m b/Excel2SQL/openSQL.m new file mode 100644 index 0000000..f836de4 --- /dev/null +++ b/Excel2SQL/openSQL.m @@ -0,0 +1,25 @@ +function [ conn ] = openSQL( ~ ) +% OPENSQL open the mySQL database +% The JDBC driver is required to connect MATLAB to the MySQL database. +% Refer to documentation at: +% https://ch.mathworks.com/help/database/ug/mysql-jdbc-windows.html + +fprintf('\nOpen mySQL connection... '); + +instance = 'shoulder_test'; +username = 'matlabA'; +password = 'M@tL@b1,'; +url = 'lbovenus.epfl.ch'; + +% Open SQL databse +conn = database(instance,username,password,'Vendor','MySQL',... + 'Server',url); + +if ~isempty(conn.Message) + fprintf('\nMessage: %s\n', conn.Message); +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/outcomeToSQL.m b/Excel2SQL/outcomeToSQL.m new file mode 100644 index 0000000..90af358 --- /dev/null +++ b/Excel2SQL/outcomeToSQL.m @@ -0,0 +1,32 @@ +function outcomeToSQL( outcome, conn ) +%OUTCOMETOSQL Summary of this function goes here +% Detailed explanation goes here + +fprintf('\noutcome to mySQL...'); + +% Empty outcome table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE outcome'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +colnames = {'outcome_id','sCase_id','date','comment','loosening'}; + +% Loop on pCase structure array +for i=1:numel(outcome) + + outcome_id = outcome(i).outcome_id; + sCase_id = outcome(i).sCase_id; + date = outcome(i).date; + comment = outcome(i).comment; + loosening = outcome(i).loosening; + + exdata = {outcome_id, sCase_id, date, comment, loosening}; + % insert data in SQL + datainsert(conn,'outcome',colnames,exdata); +end + +fprintf(' Done\n'); + +end diff --git a/Excel2SQL/patientFromExcel.m b/Excel2SQL/patientFromExcel.m new file mode 100644 index 0000000..250923c --- /dev/null +++ b/Excel2SQL/patientFromExcel.m @@ -0,0 +1,92 @@ +function [patient] = patientFromExcel(rawExcel) +%PATIENT builds the structure array patients from +% Detailed explanation goes here + +fprintf('\nGet patient from Excel... '); + +% define the patient structure array +patient = struct(... + 'patient_id', [], ... + 'IPP', [], ... % to be later move to another table for anonymous constrain + 'initials', [], ... % to be later move to another table for anonymous constrain + 'gender', [], ... + 'birth_date', [], ... + 'height', [], ... + 'weight', []... % should we put weight here since it can vary? + ); + +% get column of variables +header = rawExcel(1,:); +sCase_id_col = find(strcmp([header],'sCase.id')); +IPP_col = find(strcmp([header],'patient.IPP')); +initials_col = find(strcmp([header],'patient.initials')); +gender_col = find(strcmp([header],'patient.gender')); +birth_date_col = find(strcmp([header],'patient.birth_date')); +height_col = find(strcmp([header],'patient.height')); +weight_col = find(strcmp([header],'patient.weight')); + +% loop over rows of Excel table to build patient structure +% patients are not included if caseExcel, IPP, initials, gender, birth_date are not +% defined +[rowN, ~] = size(rawExcel); +patient_id = 0; +for row_idx = 2:rowN + row = rawExcel(row_idx, :); % get the entire row + + % check that there is a sCase for this row + sCase_id = row{sCase_id_col}; % sCase_id from Excel + if ~isempty(sCase_id) + + % check validity of data + IPP = row{IPP_col}; + initials = row{initials_col}; + gender = row{gender_col}; + birth_date = row{birth_date_col}; + birth_date = datetime(birth_date,'InputFormat','dd.MM.yyyy','ConvertFrom','excel'); + birth_date.Format = 'yyyy-MM-dd'; + + if ~isnan(IPP) & ... + ~isnan(initials) & ... + ~isnan(gender) & ... + ~isnat(birth_date) + + % Check that patient in row sCase is not already in structure + % We asume here that IPP uniquely identifies a patient + sameIPP = find([patient.IPP] == IPP); + if ~isempty(sameIPP) + % IPP is allready in patient array, extra-checks to avoid false same patient + if birth_date ~= patient(sameIPP).birth_date % not same birthdate + fprintf('\nSame IPP (%i) but different birth date in case %s !', IPP, sCase_id); + end + if gender ~= patient(sameIPP).gender % not same gender + fprintf('\nSame IPP (%i) but different gender in case %s !', IPP, sCase_id); + end + if ~strcmp(initials, patient(sameIPP).initials) % not same initials + fprintf('\nSame IPP (%i) but different initials in case %s !', IPP, sCase_id); + end + else % patient is not in patient structure array, so add it + patient_id = patient_id + 1; + patient_idx = patient_id; + + height = row{height_col}; + if height == ' ' % to avoid strange behavior of the excel import + height = NaN; + end + weight = row{weight_col}; + + patient(patient_idx).patient_id = patient_id; + patient(patient_idx).IPP = IPP; + patient(patient_idx).initials = initials; + patient(patient_idx).gender = gender; + patient(patient_idx).birth_date = birth_date; + patient(patient_idx).height = height; + patient(patient_idx).weight = weight; + end + end + end +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/patientToSQL.m b/Excel2SQL/patientToSQL.m new file mode 100644 index 0000000..818339f --- /dev/null +++ b/Excel2SQL/patientToSQL.m @@ -0,0 +1,43 @@ +function patientToSQL(patient, conn) +%PATIENDBUPDATE fill patient structure array in mySQL +% Detailed explanation goes here + +fprintf('\npatient to mySQL... '); + +testquery= 'SET FOREIGN_KEY_CHECKS = 0'; +exec(conn,testquery); + +% Empty patient table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE patient'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +testquery= 'SET FOREIGN_KEY_CHECKS = 1'; +exec(conn,testquery); + +colnames = {'gender','birth_date','height','weight'}; + +% Loop on patient structure array +for i=1:numel(patient) + gender = patient(i).gender; + birth_date = datestr(patient(i).birth_date,'yyyy-mm-dd'); + height = patient(i).height; + if isnan(height) + height = ''; + end; + weight = patient(i).weight; + if isnan(weight) + weight = ''; + end; + + exdata = {gender,birth_date,height,weight}; + % insert data in SQL + datainsert(conn,'patient',colnames,exdata); +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/rawFromExcel.m b/Excel2SQL/rawFromExcel.m new file mode 100644 index 0000000..9843ba5 --- /dev/null +++ b/Excel2SQL/rawFromExcel.m @@ -0,0 +1,38 @@ +function [ rawExcel ] = rawFromExcel() +%RAWFROMEXCERL read the Excel file filename and output content in raw +% Set the sheet and the range +% Returns the raw data + +fprintf('\nGet rawExcel from Excel... '); + +% Set Excel file name & directory +directory = 'C:\Users\vmalfroy\Dropbox\shoulderDatabase\'; +filename = 'ShoulderDataBaseTest.xlsx'; +filename = strcat(directory,filename); +sheet = 'Cases'; % 'Normal' & 'TSA' +xlRange = 'A1:CM999'; % range of column and row to be imported + +[~,~,rawExcel] = xlsread(filename,sheet,xlRange); + +% get column of variables +header = rawExcel(1,:); +sCase_id_col = find(strcmp([header],'sCase.id')); + +% delete rows without sCase) +row_idx = 2; +[rowN, ~] = size(rawExcel); +while row_idx <= rowN + row = rawExcel(row_idx, :); % get the entire row + sCase_id = row{sCase_id_col}; + if isnan(sCase_id) + rawExcel(row_idx, :) = []; + rowN = rowN - 1; + else + row_idx = row_idx + 1; + end +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/sCaseFromExcel.m b/Excel2SQL/sCaseFromExcel.m new file mode 100644 index 0000000..6553b4f --- /dev/null +++ b/Excel2SQL/sCaseFromExcel.m @@ -0,0 +1,213 @@ +function [ sCase, diagnosis, treatment, outcome, study ] = sCaseFromExcel(shoulder, patient, diagnosisList, treatmentList, rawExcel) +% PCASEFROMEXCEL build the sCase (shoulder case) structure array from Excel and from +% patient and shoulder structure. A sCase is associated to a shoulder. The +% same shoulder can be associated to more than one sCase. +% pCase stand for patient case. The variable case could not be used since +% alrewad usd by Matlab + +fprintf('\nGet sCase from Excel... '); + +% define sCase structure array +sCase = struct(... + 'sCase_id' ,[],... + 'shoulder_id' ,[],... + 'folder_name' ,[] ... + ); + +% defne diagnosis structure +diagnosis = struct(... + 'diagnosis_id' ,[],... + 'sCase_id' ,[],... + 'diagnosisList_id',[],... + 'date' ,[],... + 'comment' ,[]... + ); + +% defne treatment structure +treatment = struct(... + 'treatment_id' ,[],... + 'sCase_id' ,[],... + 'treatmentList_id',[],... + 'date' ,[],... + 'comment' ,[]... + ); + +% defne outcome structure +outcome = struct(... + 'outcome_id' ,[],... + 'sCase_id' ,[],... + 'date' ,[],... + 'comment' ,[],... + 'loosening' ,[]... + ); + +% define study structure +study = struct(... + 'study_id' ,[],... + 'sCase_id' ,[],... + 'name' ,[],... + 'comment' ,[]... + ); + +% get column of variables +header = rawExcel(1,:); +sCase_id_col = find(strcmp([header],'sCase.id')); +diagnosisName_col = find(strcmp([header],'diagnosis.name')); +diagnosisDate_col = find(strcmp([header],'diagnosis.date')); +diagnosisComm_col = find(strcmp([header],'diagnosis.comment')); +treatmentName_col = find(strcmp([header],'treatment.name')); +treatmentDate_col = find(strcmp([header],'treatment.date')); +treatmentComm_col = find(strcmp([header],'treatment.comment')); +outcomeDate_col = find(strcmp([header],'outcome.date')); +outcomeComm_col = find(strcmp([header],'outcome.comment')); +outcomeLoos_col = find(strcmp([header],'outcome.loosening')); +IPP_col = find(strcmp([header],'patient.IPP')); +side_col = find(strcmp([header],'shoulder.side')); + +% loop over rows of Excel table +[rowN, ~] = size(rawExcel); +sCase_id = 0; +diagnosis_id = 0; +treatment_id = 0; +outcome_id = 0; +for row_idx = 2:rowN + row = rawExcel(row_idx, :); % get the entire row + sCaseE = row{sCase_id_col}; % sCase id from Excel -> folder_name + side = row{side_col}; + if ~isnan(sCaseE) & ~isnan(side) % check that sCase & side are defined + IPP = row{IPP_col}; + patient_idx = find([patient.IPP] == IPP); + % get patient index of this caseExcel + if ~isempty(patient_idx) + % there is a patient with same IPP + patient_idx = patient(patient_idx).patient_id; % get patient_id + shoulder_idx = find([shoulder.patient_id] == patient_idx); % get shoulder_id + % should be 1 or 2 (R/L), but not 0 (test it however) + shoulder_idxN = length(shoulder_idx); + switch shoulder_idxN + case 0 + % this sCase patient is not in the shoulder array + % report an error + fprintf('\nsCase %s not in shoulder array', sCaseE); + addCase = 0; + case 1 + % this sCase patient has 1 match in the shoulder array + % so we have to check side + if side == shoulder(shoulder_idx).side + shoulder_id = shoulder(shoulder_idx).shoulder_id; + addCase = 1; + else + % not same side so report a problem + fprintf('\nsCase in soulder array but not same side'); + end + case 2 + % both shoulder sides of this patient are in the + % shoulder array, so we have to check side + if side == shoulder(shoulder_idx(1)).side + shoulder_id = shoulder(shoulder_idx(1)).shoulder_id; + addCase = 1; + else + if side == shoulder(shoulder_idx(2)).side + shoulder_id = shoulder(shoulder_idx(2)).shoulder_id; + addCase = 1; + end + end + end + if addCase + % add sCase + sCase_id = sCase_id + 1; + sCase_idx = sCase_id; + folder_name = sCaseE; + + sCase(sCase_idx).sCase_id = sCase_id; + sCase(sCase_idx).shoulder_id = shoulder_id; + sCase(sCase_idx).folder_name = folder_name; + + + % check & add diagnosis + diagnosisName = row{diagnosisName_col}; + diagnosisList_idx = find(strcmp({diagnosisList.name}, diagnosisName) == 1); + if isempty(diagnosisList_idx) + fprintf('\nsCase %s has not associated diagnosisList_id', sCaseE); + else + diagnosis_id = diagnosis_id + 1; + diagnosis_idx = diagnosis_id; + % get diagnosis date + diagnosis_date = row{diagnosisDate_col}; + diagnosis_date = datetime(diagnosis_date,'ConvertFrom','excel'); + diagnosis_date.Format = 'yyyy-MM-dd'; + % get diagnosis comment + diagnosis_comment = row{diagnosisComm_col}; + if isnan(diagnosis_comment) + diagnosis_comment = ''; + end + + diagnosis(diagnosis_idx).diagnosis_id = diagnosis_id; + diagnosis(diagnosis_idx).sCase_id = sCase_id; + diagnosis(diagnosis_idx).diagnosisList_id = diagnosisList(diagnosisList_idx).diagnosisList_id; + diagnosis(diagnosis_idx).date = diagnosis_date; + diagnosis(diagnosis_idx).comment = diagnosis_comment; + end + + % check & add treatment + treatmentName = row{treatmentName_col}; + treatmentList_idx = find(strcmp({treatmentList.name}, treatmentName) == 1); + if isempty(treatmentList_idx) + fprintf('\nsCase %s has not associated treatmentList_id', sCaseE); + else + treatment_id = treatment_id + 1; + treatment_idx = treatment_id; + % get treatment date + treatment_date = row{treatmentDate_col}; + treatment_date = datetime(treatment_date,'InputFormat','dd.MM.yyyy','ConvertFrom','excel'); + treatment_date.Format = 'yyyy-MM-dd'; + % get treatment comment + treatment_comment = row{treatmentComm_col}; + + treatment(treatment_idx).treatment_id = treatment_id; + treatment(treatment_idx).sCase_id = sCase_id; + treatment(treatment_idx).treatmentList_id = treatmentList(treatmentList_idx).treatmentList_id; + treatment(treatment_idx).date = treatment_date; + treatment(treatment_idx).comment = treatment_comment; + end + + % check & add outcome + % get outcome date + outcome_date = row{outcomeDate_col}; + if ~isempty(outcome_date) + outcome_date = datetime(outcome_date,'InputFormat','dd.MM.yyyy','ConvertFrom','excel'); + outcome_date.Format = 'yyyy-MM-dd'; + end + % get outcome comment + outcome_comment = row{outcomeComm_col}; + % get outcome loosening + outcome_loosening = row{outcomeLoos_col}; + + if isempty(outcome_comment) || isempty(outcome_loosening) + fprintf('\nsCase %s has not associated outcome', sCaseE); + else + outcome_id = outcome_id + 1; + outcome_idx = outcome_id; + + outcome(outcome_idx).outcome_id = outcome_id; + outcome(outcome_idx).sCase_id = sCase_id; + outcome(outcome_idx).date = outcome_date; + outcome(outcome_idx).comment = outcome_comment; + outcome(outcome_idx).loosening = outcome_loosening; + end + + % check & add study + % to be done + study(1).study_id = 1; + study(1).sCase_id = 1; + study(1).name = 'studyTest'; + study(1).comment = 'studyComment'; + end + end + end +end + +fprintf(' Done\n'); + +end + diff --git a/Excel2SQL/sCaseToSQL.m b/Excel2SQL/sCaseToSQL.m new file mode 100644 index 0000000..e061331 --- /dev/null +++ b/Excel2SQL/sCaseToSQL.m @@ -0,0 +1,36 @@ +function sCaseToSQL(sCase, conn) +%PCASETOSQL fill sCase structure array in mySQL +% Detailed explanation goes here + +fprintf('\nsCase to mySQL... '); + +testquery= 'SET FOREIGN_KEY_CHECKS = 0'; +exec(conn,testquery); + +% Empty sCase table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE sCase'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +testquery= 'SET FOREIGN_KEY_CHECKS = 1'; +exec(conn,testquery); + +colnames = {'sCase_id','shoulder_id','folder_name'}; + +% Loop on pCase structure array +for i=1:numel(sCase) + sCase_id = sCase(i).sCase_id; + shoulder_id = sCase(i).shoulder_id; + folder_name = sCase(i).folder_name; + + exdata = {sCase_id,shoulder_id,folder_name}; + % insert data in SQL + datainsert(conn,'sCase',colnames,exdata); +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/scapulaToSQL.m b/Excel2SQL/scapulaToSQL.m new file mode 100644 index 0000000..3b23a54 --- /dev/null +++ b/Excel2SQL/scapulaToSQL.m @@ -0,0 +1,31 @@ +function scapulaToSQL(scapula, conn) +% fill scapula structure array in mySQL +% Detailed explanation goes here + +fprintf('\nscapula to mySQL... '); + +% Empty scapula table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE scapula'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +colnames = {'CT_id','CT_angle'}; + +% Loop on glenoid structure array +for i=1:numel(scapula) + + CT_id = scapula(i).CT_id; + CT_angle = scapula(i).CTangle; + + + exdata = {CT_id,CT_angle}; + % insert data in SQL + + datainsert(conn,'scapula',colnames,exdata); +end + +fprintf('Done\n'); + +end \ No newline at end of file diff --git a/Excel2SQL/shoulderFromExcel.m b/Excel2SQL/shoulderFromExcel.m new file mode 100644 index 0000000..893c04a --- /dev/null +++ b/Excel2SQL/shoulderFromExcel.m @@ -0,0 +1,80 @@ +function [ shoulder ] = shoulderFromExcel(patient, rawExcel) +%SHOULDERFROMEXCEL build the shoulder structure array from Excel and from +%the patient structure +% The output is a shoulder strucure array that has the same form as the +% shoulder table of the mySQL database + +fprintf('\nGet shoulder from Excel... '); + +% define houlder structure array +shoulder = struct(... + 'shoulder_id', [], ... + 'patient_id', [], ... + 'side', [] ... + ); + +% get column of variables +header = rawExcel(1,:); +sCase_id_col = find(strcmp([header],'sCase.id')); +IPP_col = find(strcmp([header],'patient.IPP')); +side_col = find(strcmp([header],'shoulder.side')); + +% loop over rows of Excel table +[rowN, ~] = size(rawExcel); +shoulder_id = 0; +for row_idx = 2:rowN + row = rawExcel(row_idx, :); % get the entire row + sCaseE = row{sCase_id_col}; % sCase id from Excel + side = row{side_col}; + if ~isnan(sCaseE) & ~isnan(side) % check that sCase & side are defined + IPP = row{IPP_col}; + patient_idx = find([patient.IPP] == IPP); + % get patient index of this sCase + if ~isempty(patient_idx) + % there is a patient with same IPP + % check that this shoulder patient_id & side is already in the shoulder array + patient_id = patient(patient_idx).patient_id; % patient_id of existing patient + % find this patient_id in shoulder array + shoulder_idx = find([shoulder.patient_id] == patient_id); + % might be 0, 1, or 2 (R/L) + shoulder_idxN = length(shoulder_idx) == 1; % number of matching patient_id in shoulder + addShoulder = 0; % 1 to add the shoulder in the shoulder array + switch shoulder_idxN + case 0 + % this sCase patient is not in the shoulder array + % so the shoulder of this sCase is also not, so add it + addShoulder = 1; + case 1 + % this sCase patient is in the shoulder array + % so we have to check the shoulder side + if side ~= shoulder(shoulder_idx).side + % this shoulder side in not yet in the shoulder array + % so add it + addShoulder = 1; + end + % fprintf('\nSecond shoulder in %s', sCase); + case 2 + % both shoulder sides of this patient are in the + % shoulder array, meaning that this sCase is either + % a duplicate, or a multiple case of the same shoulder + % Nothing to add, but report for control + fprintf('\nDuplicate or multiple case in %s', sCaseE); + otherwise + % should not happend, so report in this case + fprintf('\nMore than 2 shoulders in %s', sCaseE); + end + if addShoulder + shoulder_id = shoulder_id + 1; + shoulder_idx = shoulder_id; + shoulder(shoulder_idx).shoulder_id = shoulder_id; + shoulder(shoulder_idx).patient_id = patient_id; + shoulder(shoulder_idx).side = side; + end + end + end +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/shoulderToSQL.m b/Excel2SQL/shoulderToSQL.m new file mode 100644 index 0000000..08e97dc --- /dev/null +++ b/Excel2SQL/shoulderToSQL.m @@ -0,0 +1,23 @@ +function shoulderToSQL(shoulder, conn) +%SHOULDERTOSQL fill shoulder structure array in mySQL +% Detailed explanation goes here + +fprintf('\nshoulder to mySQL... '); + +colnames = {'shoulder_id','patient_id','side'}; + +% Loop on patient structure array +for i=1:numel(shoulder) + shoulder_id = shoulder(i).shoulder_id; + patient_id = shoulder(i).patient_id; + side = shoulder(i).side; + + exdata = {shoulder_id,patient_id,side}; + % insert data in SQL + datainsert(conn,'shoulder',colnames,exdata); +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/treatmentListFromExcel.m b/Excel2SQL/treatmentListFromExcel.m new file mode 100644 index 0000000..9381fe7 --- /dev/null +++ b/Excel2SQL/treatmentListFromExcel.m @@ -0,0 +1,50 @@ +function [ treatmentList ] = treatmentListFromExcel( rawExcel ) +%TREATEMENT built the tratement structure array that identify the treatments +% Detailed explanation goes here + +fprintf('\nGet treatmentList from Excel... '); + +% define treatment structure array +treatmentList = struct(... + 'treatmentList_id',[], ... + 'name' ,[], ... + 'description' ,[] ... + ); + +% define undefined treatment +treatmentList(1).treatmentList_id = 1; +treatmentList(1).name = 'undefined'; +treatmentList(1).description = 'The treatement is not defined yet'; + +% define no treatment +treatmentList(2).treatmentList_id = 2; +treatmentList(2).name = 'none'; +treatmentList(2).description = 'No planed treatment, for healthy shoulders'; + +% get column of variables +header = rawExcel(1,:); +treatmentName_col = find(strcmp([header],'treatment.name')); + +% loop over rows of Excel table +[rowN, ~] = size(rawExcel); +treatmentList_id = 2; +for row_idx = 2:rowN + row = rawExcel(row_idx, :); % get the entire row + treatmentName = row(treatmentName_col); + treatmentName = treatmentName{1}; + if ~isempty(treatmentName) & ~isnan(treatmentName) + sameTreatment = find(strcmp({treatmentList.name}, treatmentName)==1); + if isempty(sameTreatment) + treatmentList_id = treatmentList_id + 1; + treatmentList_idx = treatmentList_id; + treatmentList(treatmentList_idx).treatmentList_id = treatmentList_id; + treatmentList(treatmentList_idx).name = treatmentName; + treatmentList(treatmentList_idx).description = ''; + end + end +end + +fprintf('Done\n'); + +end + diff --git a/Excel2SQL/treatmentListToSQL.m b/Excel2SQL/treatmentListToSQL.m new file mode 100644 index 0000000..ff58fee --- /dev/null +++ b/Excel2SQL/treatmentListToSQL.m @@ -0,0 +1,23 @@ +function treatmentListToSQL(treatmentList, conn) +%TREATMENTTOSQL Summary of this function goes here +% Detailed explanation goes here + +fprintf('\ntreatmentList to mySQL...'); + +colnames = {'treatmentList_id','name','description'}; + +% Loop on pCase structure array +for i=1:numel(treatmentList) + treatmentList_id = treatmentList(i).treatmentList_id; + name = treatmentList(i).name; + description = treatmentList(i).description; + + exdata = {treatmentList_id,name,description}; + % insert data in SQL + datainsert(conn,'treatmentList',colnames,exdata); +end + +fprintf(' Done\n'); + +end + diff --git a/Excel2SQL/treatmentToSQL.m b/Excel2SQL/treatmentToSQL.m new file mode 100644 index 0000000..f8ad801 --- /dev/null +++ b/Excel2SQL/treatmentToSQL.m @@ -0,0 +1,33 @@ +function treatmentToSQL(treatment, conn) +%TREATMENTTOSQL Summary of this function goes here +% Detailed explanation goes here + +fprintf('\ntreatment to mySQL...'); + +% Empty treatment table and reset AUTO_INCREMENT +sqlquery = 'TRUNCATE treatment'; +curs = exec(conn,sqlquery); +if ~isempty(curs.Message) + fprintf('\nMessage: %s\n', curs.Message); +end + +colnames = {'treatment_id','sCase_id','treatmentList_id','date','comment'}; + +% Loop on pCase structure array +for i=1:numel(treatment) + treatment_id = treatment(i).treatment_id; + sCase_id = treatment(i).sCase_id; + treatmentList_id = treatment(i).treatmentList_id; + date = treatment(i).date; + comment = treatment(i).comment; + + + exdata = {treatment_id,sCase_id,treatmentList_id,date,comment}; + % insert data in SQL + datainsert(conn,'treatment',colnames,exdata); +end + +fprintf(' Done\n'); + +end + diff --git a/jdbcConfig.mat b/jdbcConfig.mat new file mode 100644 index 0000000..83fdbe5 Binary files /dev/null and b/jdbcConfig.mat differ diff --git a/scapula_calculation_files/fitLine2.m b/scapula_calculation_files/fitLine2.m new file mode 100644 index 0000000..7a79d66 --- /dev/null +++ b/scapula_calculation_files/fitLine2.m @@ -0,0 +1,22 @@ +function [dirVect,meanX,residuals,rmse,R2] = fitLine2(X) + +% Fits a line to a group of points in X + +[coeff,score,roots] = princomp(X); +dirVect = coeff(:,1); +[Xn,Xm] = size(X); +meanX = mean(X,1); +Xfit1 = repmat(meanX,Xn,1) + score(:,1)*coeff(:,1)'; +residuals = X-Xfit1; +error = diag(pdist2(residuals,zeros(Xn,Xm))); +sse = sum(error.^2); +rmse = norm(error)/sqrt(Xn); + +for i=1:Xn; + tot(i) = norm(meanX-X(i,:)); +end + +sst = sum(tot.^2); + +R2 = 1-(sse/sst); %http://en.wikipedia.org/wiki/Coefficient_of_determination +end \ No newline at end of file diff --git a/scapula_calculation_files/fitPlane2.m b/scapula_calculation_files/fitPlane2.m new file mode 100644 index 0000000..2a12dcd --- /dev/null +++ b/scapula_calculation_files/fitPlane2.m @@ -0,0 +1,20 @@ +function [normal,meanX,residuals,rmse,R2] = fitPlane2(X) + +[coeff,score,roots] = princomp(X); +normal = coeff(:,3); +[Xn,Xm] = size(X); +meanX = mean(X,1); +Xfit = repmat(meanX,Xn,1) + score(:,1:2)*coeff(:,1:2)'; +residuals = X - Xfit; +error = diag(pdist2(residuals,zeros(Xn,Xm))); +sse = sum(error.^2); +rmse = norm(error)/sqrt(Xn); + +for i=1:Xn; + tot(i) = norm(meanX-X(i,:)); +end + +sst = sum(tot.^2); + +R2 = 1-(sse/sst); %http://en.wikipedia.org/wiki/Coefficient_of_determination +end \ No newline at end of file diff --git a/scapula_calculation_files/fitSphere2.m b/scapula_calculation_files/fitSphere2.m new file mode 100644 index 0000000..127da1a --- /dev/null +++ b/scapula_calculation_files/fitSphere2.m @@ -0,0 +1,76 @@ +function [center,radius,residuals,R2] = fitSphere2(x,y,z) +%SPHEREFIT find least squares sphere +% +% Fit a sphere to a set of xyz data points +% [center,radius,residuals] = shperefit(X) +% [center,radius,residuals] = spherefit(x,y,z); +% Input +% x,y,z Cartesian data, n x 3 matrix or three vectors (n x 1 or 1 x n) +% Output +% center: least squares sphere center coordinates, == [xc yc zc] +% radius: radius of curvature +% residuals: residuals in the radial direction +% +% Fit the equation of a sphere in Cartesian coordinates to a set of xyz +% data points by solving the overdetermined system of normal equations, +% ie, x^2 + y^2 + z^2 + a*x + b*y + c*z + d = 0 +% The least squares sphere has radius R = sqrt((a^2+b^2+c^2)/4-d) and +% center coordinates (x,y,z) = (-a/2,-b/2,-c/2) + +error(nargchk(1,3,nargin)); % check input arguments +if nargin == 1 % n x 3 matrix + if size(x,2) ~= 3 + error ('input data must have three columns') + else + z = x(:,3); % save columns as x,y,z vectors + y = x(:,2); + x = x(:,1); + end +elseif nargin == 3 % three x,y,z vectors + x = x(:); % force into columns + y = y(:); + z = z(:); + if ~isequal(length(x),length(y),length(z)) % same length ? + error('input vectors must be same length'); + end +else % must have one or three inputs + error('invalid input, n x 3 matrix or 3 n x 1 vectors expected'); +end + +% need four or more data points +if length(x) < 4 + error('must have at least four points to fit a unique sphere'); +end + +% solve linear system of normal equations +A = [x y z ones(size(x))]; +b = -(x.^2 + y.^2 + z.^2); +a = A \ b; + +% return center coordinates and sphere radius +center = -a(1:3)./2; +radius = sqrt(sum(center.^2)-a(4)); + +% calculate residuals +if nargout > 2 + residuals = radius - sqrt(sum(bsxfun(@minus,[x y z],center.').^2,2)); +end +% whichstats = {'adjrsquare' 'rsquare'}; +% stats = regstats(b,A(:,1:3),'linear',whichstats); +% R2 = stats.rsquare; +% R2adj = stats.adjrsquare; + +sse = sum(residuals.^2); + +meanX = [mean(x) mean(y) mean(z)]; + +for i=1:size(x) + X = [x(i) y(i) z(i)]; + tot(i) = norm(meanX-X); +end + +sst = sum(tot.^2); + +R2 = 1-sse/sst; + +end \ No newline at end of file diff --git a/scapula_calculation_files/importStl.m b/scapula_calculation_files/importStl.m new file mode 100644 index 0000000..033ca43 --- /dev/null +++ b/scapula_calculation_files/importStl.m @@ -0,0 +1,266 @@ +function varargout=importStl(filename,mode) +% STL_Import is a tool designed to import into MATLAB both binary and ASCII STL files. +% +% This scprit is mainly a collage betwwen file axchange fileid 22409 and 3642, plus +% some other features that can be considered new on FEX. +% +% SYNOPSIS: +% +% +% %mode 1 (default) +% [p,t,tnorm]=STL_Import(filename,mode) +% +% %mode 2 +% [v,tnorm])=STL_Import(filename,mode) +% +% +% INPUT: +% +% filename: string representing the name fo the file +% +% mode: +% +% +% mode=1 (if omitted is automatically set to one) +% +% set the the output to: +% +% output=[p,t,tnorm] +% +% where +% +% p=points (unique) of the model nx3 array +% +% t=triangles indexes of the model +% +% tnorm= normals of triangles +% +% +% mode=2 +% +% set the the output to: +% +% output=[v,tnorm] +% +% where +% +% v= vertex of the model(not unique points) of the model nx3 array. Each +% three points we have a triangle in consecutive order. +% +% tnorm= normals of triangles +% +% EXAMPLES: +% +% [p,t,tnorm]=STL_Import('link1.stl',1); +% [pv,tnorm]=STL_Import('link1.stl',2); +% +% +% Visit: +% +% http://giaccariluigi.altervista.org/blog/ +% +% Author: Giaccari Luigi (giaccariluigi@msn.com) + + + +if nargin<2 + mode=1;%default value +end + + +if ~(mode==1 || mode==2) + error('invalid mode') +end + +if nargout<3 && mode==1 + error('invalid input number /mode setting') +end +if nargout>2 && mode==2 + error('invalid input number /mode setting') +end + + +%open file +fid=fopen(filename, 'r'); %Open the file, assumes STL ASCII format. +if fid == -1 + error('File could not be opened, check name or path.') +end + + +M = fread(fid,inf,'uint8=>uint8'); +fclose(fid); + +if( isbinary(M) ) + [v,tnorm]=ImportSTL_binary(M); + +else + clear M; + [v,tnorm]=ImportSTL_ASCII(filename); + +end + +clear M + +varargout = cell(1,nargout); +switch mode + case 1 + [p,t]=fv2pt(v,length(v)/3);%gets points and triangles + + varargout{1} = p; + varargout{2} = t; + varargout{3} = tnorm; + case 2 + varargout{1} = v; + varargout{2} = tnorm; +end +end + + + +function [v,tnorm]=ImportSTL_ASCII(filename) + +%counting the number of vertex +vnum=0; +fid=fopen(filename, 'r'); %Open the file, assumes STL ASCII format. +while feof(fid) == 0 % test for end of file, if not then do stuff + tline = fgetl(fid); % reads a line of data from file. + fword = sscanf(tline, '%s '); % make the line a character string + if strncmpi(fword, 'v',1) ; % Checking if a "V"ertex line, as "V" is 1st char. + vnum = vnum + 1; % If a V we count the # of V's + end +end +fclose(fid); +numt=ceil(vnum/3);%triangles number equals vertex number/3 + +tnorm=zeros(numt,3);%preallocate for normals +v=zeros(vnum,3);%not unique vertex + +c=0;%vertex counter +fnum=0; +fid=fopen(filename, 'r'); %REOpen the file +while feof(fid) == 0 % test for end of file, if not then do stuff + tline = fgetl(fid); % reads a line of data from file. + fword = sscanf(tline, '%s '); % make the line a character string + + %% Check vertex + if strncmpi(fword, 'v',1) ; % Checking if a "V"ertex line, as "V" is 1st char. + c = c + 1; % If a V we count the # of V's + v(c,:) = sscanf(tline, '%*s %f %f %f'); % & if a V, get the XYZ data of it. + + %% Check facet normal + elseif strncmpi(fword, 'f',1) ; % Checking if a "V"ertex line, as "V" is 1st char. + fnum =fnum + 1; % If a V we count the # of V's + tnorm(fnum,:) = sscanf(tline, '%*s %*s %f %f %f'); % & if a V, get the XYZ data of it. + + % %% Check for color + % elseif strncmpi(fword, 'c',1) ; % Checking if a "C"olor line, as "C" is 1st char. + % VColor = sscanf(tline, '%*s %f %f %f'); % & if a C, get the RGB color data of the face. + % % Keep this color, until the next color is used. + + end + +end +fclose(fid); +end + + +function [p,t]=fv2pt(v,fnum) + +%gets points and triangle indexes given vertex and facet number + +c=size(v,1); + +%triangles with vertex id data +t=zeros(3,fnum); +t(:)=1:c; + + +%now we have to keep unique points fro vertex +[p,i,j]=unique(v,'rows'); %now v=p(j) p(i)=v; +t(:)=j(t(:)); +t=t'; + +end + +% + + +function tf = isbinary(A) +% ISBINARY determines if an STL file is binary or ASCII. + +% Look for the string 'endsolid' near the end of the file +if isempty(A) || length(A) < 16 + error('MATLAB:stlread:incorrectFormat', ... + 'File does not appear to be an ASCII or binary STL file.'); +end + +% Read final 16 characters of M +i2 = length(A); +i1 = i2 - 100;%100 empirical value +str = char( A(i1:i2)' ); + +k = strfind(lower(str), 'endsolid'); +if ~isempty(k) + tf = false; % ASCII +else + tf = true; % Binary +end +end + + +function [V,N]=ImportSTL_binary(M) + + + +if length(M) < 84 + error('MATLAB:stlread:incorrectFormat', ... + 'Incomplete header information in binary STL file.'); +end + +% Bytes 81-84 are an unsigned 32-bit integer specifying the number of faces +% that follow. +numFaces = typecast(M(81:84),'uint32'); +%numFaces = double(numFaces); +if numFaces == 0 + warning('MATLAB:stlread:nodata','No data in STL file.'); + return +end + +T = M(85:end); + +V = NaN(3*numFaces,3); +N = NaN(numFaces,3); + +numRead = 0; +while numRead < numFaces + % Each facet is 50 bytes + % - Three single precision values specifying the face normal vector + % - Three single precision values specifying the first vertex (XYZ) + % - Three single precision values specifying the second vertex (XYZ) + % - Three single precision values specifying the third vertex (XYZ) + % - Two unused bytes + i1 = 50 * numRead + 1; + i2 = i1 + 50 - 1; + facet = T(i1:i2)'; + + n = typecast(facet(1:12),'single'); + v1 = typecast(facet(13:24),'single'); + v2 = typecast(facet(25:36),'single'); + v3 = typecast(facet(37:48),'single'); + + n = double(n); + v = double([v1; v2; v3]); + + % Figure out where to fit these new vertices, and the face, in the + % larger F and V collections. + fInd = numRead + 1; + vInd1 = 3 * (fInd - 1) + 1; + vInd2 = vInd1 + 3 - 1; + + V(vInd1:vInd2,:) = v; + N(fInd,:) = n; + + numRead = numRead + 1; +end + +end \ No newline at end of file diff --git a/scapula_calculation_files/project2Plane.m b/scapula_calculation_files/project2Plane.m new file mode 100644 index 0000000..f70b3f9 --- /dev/null +++ b/scapula_calculation_files/project2Plane.m @@ -0,0 +1,18 @@ +function [P0] = project2Plane(P,N,Q,m) + +% Let P be the m x 3 array of the 3D points to be projected, let Q be the +% 1 x 3 vector of the given point on the plane, let N be the 1 x 3 vector +% of the normal direction to the plane, and let P0 be the m x 3 array of +% points orthogonally projected from P onto the plane. Then do this: +% +N = N/norm(N); % <-- do this if N is not normalized +% V = P-Q; +% D = dot(V,D); +% P0 = P-D*N + + +N2 = N.'*N; +P0 = P*(eye(3)-N2)+repmat(Q*N2,m,1); +%P0 = P0/norm(P0); + +end \ No newline at end of file diff --git a/upsert/license.txt b/upsert/license.txt new file mode 100644 index 0000000..4e65529 --- /dev/null +++ b/upsert/license.txt @@ -0,0 +1,24 @@ +Copyright (c) 2015, Sven +All rights reserved. + +Redistribution and use in source and binary forms, with or without +modification, are permitted provided that the following conditions are +met: + + * Redistributions of source code must retain the above copyright + notice, this list of conditions and the following disclaimer. + * Redistributions in binary form must reproduce the above copyright + notice, this list of conditions and the following disclaimer in + the documentation and/or other materials provided with the distribution + +THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" +AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE +IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE +ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE +LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR +CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF +SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS +INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN +CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) +ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE +POSSIBILITY OF SUCH DAMAGE. diff --git a/upsert/upsert.m b/upsert/upsert.m new file mode 100644 index 0000000..8ae2b3f --- /dev/null +++ b/upsert/upsert.m @@ -0,0 +1,247 @@ +function [insertMask, returnedKeys] = upsert(conn,tableName,fieldNames,keyFields,data, varargin) +% UPSERT inserts new and updates old data to a database table +% +% UPSERT(CONNECT,TABLENAME,FIELDNAMES,KEYFIELDS,DATA). +% CONNECT is a database connection object. TABLENAME is the database +% table. FIELDNAMES is a string array of database column names. KEYFIELDS +% is the list of primary key fields that must be matched to perform an +% UPDATE rather than an INSERT. It may be given as a logical (or 0s, 1s) +% array the same length as FIELDNAMES, or a string or cell array of +% strings of key column names (in which case KEYFIELDS must be a subset +% of FIELDNAMES). DATA is a MATLAB cell array. +% +% INSERTEDMASK = UPSERT(...) returns a logical vector with one element for +% each row of DATA, indicating whether the "upsert" operation meant that +% corresponding row of DATA was inserted (TRUE) or merely updated (FALSE). +% +% UPSERT(...,'dateFields',DATEFIELDS) allows a DATE type field to be used +% as one of the primary key fields. DATEFIELDS is specified equivalently to +% KEYFIELDS. Each primary key DATE type field's data MUST be given as an +% ANSI string literal (i.e., '1998-12-25'), rather than a MATLAB datenum +% number or a differently formatted date string. +% (see http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF51062) +% +% UPSERT(...,'updateFcn',FUNCTION_HANDLE) +% UPSERT(...,'insertFcn',FUNCTION_HANDLE) optionally allows replacement +% functions for the default use of MATLAB's "update" and "fastinsert". +% +% UPSERT(...,'debug',true) prints out diagnostic information. +% +% Example: +% +% Imagine a database table "PHONE_NOS" with data like: +% PERSONID | TYPE | NUMBER +% 1 'HOME' 1234567 +% 1 'MOB' 1222222 +% 2 'HOME' 9888888 +% +% Then the MATLAB commands: +% newNos = {1 'MOB' 4444444 +% 2 'MOB' 5555555}; +% INS = upsert(conn, 'PHONE_NOS', {'PERSONID','TYPE','NUMBER'}, [1 1 0], newNos) +% +% Would result in the table having contents: +% PERSONID | TYPE | NUMBER +% 1 'HOME' 1234567 +% 1 'MOB' 4444444 +% 2 'HOME' 9888888 +% 2 'MOB' 5555555 +% +% The returned variable (INS) would be [0; 1], meaning the second row was +% updated, the first row was inserted. + +% Author: Sven Holcombe, 2015-09-01 + +% Handle user configuration input +IP = inputParser; +IP.addParameter('updateFcn', @update, @(x)isa(x,'function_handle')); +IP.addParameter('insertFcn', @fastinsert, @(x)isa(x,'function_handle')); +IP.addParameter('dateFields', []); +IP.addParameter('returnKeys',false,@(x)iscellstr(x)||(isscalar(x)&&nnz(x==[0 1])==1)); +IP.addParameter('debug', false); +IP.parse(varargin{:}); +doPrint = IP.Results.debug; +updateFcn = IP.Results.updateFcn; +insertFcn = IP.Results.insertFcn; +doReturnKeys = iscellstr(IP.Results.returnKeys) || IP.Results.returnKeys; +if ~doReturnKeys && nargout>1 + warning('upsert:noKeys', 'Second output (returned keys) will be empty because ''returnKeys'' option was not set') +end + +% Firstly, handle large data sets. Later we will use an IN clause with a +% comma-separated list to check key fields. Oracle has a limit of 1000 +% items in a list, so let's process 1000 at a time at most. Not an optimal +% solution but best solutions will differ by database flavour so this is +% adequate for the moment. +numRows = size(data,1); +returnedKeys = zeros(numRows,0); +if numRows>1000 + insertMask = true(numRows,1); + chunks = unique([1:999:numRows numRows+1]); + for i = 1:length(chunks)-1 + dataInds = chunks(i):chunks(i+1)-1; + [insertMask(dataInds), rk] = ... + upsert(conn,tableName,fieldNames,keyFields,data(dataInds,:), varargin{:}); + if doReturnKeys + if i==1 + returnedKeys = zeros(numRows,size(rk,2)); + end + returnedKeys(dataInds,:) = rk; + end + end + return; +end + +% keyFields may be input as: +% - A string: 'id' +% - A cellstr: {'id','groupNo'} +% - A logical mask the same size as fieldNames (true where field is a key) +% - Indices into fieldNames of the key fields +% keyFields will be transformed to the indices representation below +keyFields = convertSubsetOfFieldsToIndices(keyFields, fieldNames); +% Get a numeric array of which fields are DATE types for comparison +dateFields = convertSubsetOfFieldsToIndices(IP.Results.dateFields, fieldNames); + +if isempty(data) + insertMask = true(0,1); + return; +end + +% Currently it's easier (if perhaps slightly slower) to treat data as a +% cell regardless of what format it was provided as. +if isnumeric(data) + data = num2cell(data); +end + +% Which fields are keyFields? Build lists of them for an SQL fetch +keyFieldsCell = fieldNames(keyFields); +keyFieldsIsnumeric = cellfun(@(x)isnumeric(x)||islogical(x), data(1,keyFields)); +keyFieldsIsdatestr = ismember(keyFields, dateFields); +keyFieldsListStr = sprintf('%s,',keyFieldsCell{:}); + +% We don't want to gather the whole table. Only the rows matching the +% primary key fields. This is most generalised by building IN () lists from +% a single database query, rather than sending/recieving one query for +% every row of "data" being upserted. +inClauses = cell(length(keyFields),1); +for i=1:length(keyFields) + if keyFieldsIsnumeric(i) + inSet = unique([data{:,keyFields(i)}]); + if all(inSet==round(inSet)) + inStr = sprintf('%d,',inSet); + else + inStr = sprintf('%g,',inSet); + end + elseif ischar(data{1,keyFields(i)}) + inSet = unique(data(:,keyFields(i))); + if keyFieldsIsdatestr(i) + inStr = sprintf('date ''%s'',',inSet{:}); + else + inStr = sprintf('''%s'',',inSet{:}); + end + else + error('upsert:badKey', 'Primary key field cannot contain %s data',class(data{1,keyFields(i)})) + end + if length(inSet)>1 + inClauses{i} = sprintf('%s IN (%s)', keyFieldsCell{i}, inStr(1:end-1)); + else + inClauses{i} = sprintf('%s = %s', keyFieldsCell{i}, inStr(1:end-1)); + end +end + +% Fetch all table rows potentially matching the data we want to upsert +fetchWhereClause = sprintf(' %s AND', inClauses{:}); +fetchSqlStr = sprintf('SELECT %s FROM %s WHERE %s', keyFieldsListStr(1:end-1), tableName, fetchWhereClause(1:end-3)); +if doPrint, fprintf('Fetching %s data in %s matching given data...', keyFieldsListStr(1:end-1), tableName), end +fetchedData = fetch(conn, fetchSqlStr); +if doPrint, fprintf(' done. (%d potential matches found)\n', size(fetchedData,1)), end + +% Build a map of which rows to be upserted already exist in the table. +insertMask = true(size(data,1),1); % One +if ~isempty(fetchedData) + eqMap = false(size(data,1), size(fetchedData,1), length(keyFields)); + for i = 1:length(keyFields) + if keyFieldsIsnumeric(i) + thisUpsertData = cell2mat(data(:,keyFields(i))); + thisFetchedData = cast(cell2mat(fetchedData(:,i)), 'like',thisUpsertData); + eqMap(:,:,i) = bsxfun(@eq, thisUpsertData, thisFetchedData'); + elseif keyFieldsIsdatestr(i) + thisUpsertData = datenum(data(:,keyFields(i))); + thisFetchedData = datenum(fetchedData(:,i)); + eqMap(:,:,i) = bsxfun(@eq, thisUpsertData, thisFetchedData'); + else + thisUpsertData = data(:,keyFields(i)); + thisFetchedData = fetchedData(:,i)'; + eqCell = cellfun(@(x)strcmp(x, thisFetchedData), thisUpsertData, 'Un',0); + eqMap(:,:,i) = cat(1, eqCell{:}); + end + end + pkeysMatchMap = all(eqMap,3); + insertMask = ~any(pkeysMatchMap,2); +end + +% First find any data rows that do NOT yet exist in table. Insert them. +if any(insertMask) + if doPrint, fprintf('Inserting %d data rows not currently in %s...', nnz(insertMask), tableName), end + if doReturnKeys + insertedRK = insertFcn(conn,tableName,fieldNames,data(insertMask,:),'returnKeys',IP.Results.returnKeys); + else + insertFcn(conn,tableName,fieldNames,data(insertMask,:)); + end + if doPrint, fprintf(' done.\n'), end +else + insertedRK = []; +end + +% Next, update ALL rows to the values given in data. First build WHERE. +whereEqClauses = cell(numRows, length(keyFields)); +for i=1:length(keyFields) + if keyFieldsIsnumeric(i) + whereEqClauses(:,i) = cellfun(@(dat)sprintf('%s = %g', keyFieldsCell{i}, dat), data(:,keyFields(i)),'Un',0); + elseif keyFieldsIsdatestr(i) + whereEqClauses(:,i) = cellfun(@(dat)sprintf('%s = date ''%s''', keyFieldsCell{i}, dat), data(:,keyFields(i)),'Un',0); + else + whereEqClauses(:,i) = cellfun(@(dat)sprintf('%s = ''%s''', keyFieldsCell{i}, dat), data(:,keyFields(i)),'Un',0); + end +end +dataWhereClauses = cellfun(@(strs)sprintf(' %s AND',strs{:}), num2cell(whereEqClauses,2),'Un',0); +dataWhereClauses = cellfun(@(str)['WHERE ' str(1:end-3)], dataWhereClauses, 'Un',0); + +% Next, run the update on all the NON-keyField fields (since the key fields +% themselves are being matched, so won't change). Note that the "update" +% function can be replaced by a user's modified update function. +otherFields = setdiff(1:length(fieldNames), keyFields); +if doPrint, fprintf('Updating %d data rows (%d new, %d old) in %s...', length(insertMask), nnz(insertMask), nnz(~insertMask), tableName), end +if doReturnKeys + updatedRK = updateFcn(conn,tableName,fieldNames(otherFields),data(~insertMask,otherFields), dataWhereClauses(~insertMask),'returnKeys',IP.Results.returnKeys); + returnedKeys = zeros(size(cat(1,insertedRK, updatedRK))); + if any(insertMask) + returnedKeys(insertMask,:) = insertedRK; + end + if any(~insertMask) + returnedKeys(~insertMask,:) = updatedRK; + end +else + updateFcn(conn,tableName,fieldNames(otherFields),data(~insertMask,otherFields), dataWhereClauses(~insertMask)); +end + +if doPrint, fprintf(' done.\n'); end + + +function subFields = convertSubsetOfFieldsToIndices(subFields, allFields) +% convert subFields from various classes to a numeric index of allFields +% may be input as: +% - A string: 'id' +% - A cellstr: {'id','groupNo'} +% - A logical (or 0,1) mask the same size as fieldNames +% - Indices into fieldNames of the key fields +% keyFields will be transformed to the indices representation below +if ischar(subFields) || iscellstr(subFields) + subFields = ismember(upper(allFields), upper(subFields)); +end +if isnumeric(subFields) && (any(subFields==0) || nnz(subFields==1)>1) + subFields = logical(subFields); +end +if islogical(subFields) + subFields = find(subFields); +end \ No newline at end of file