INSERT INTO tests
(test_code,name,short_name,category_id,department_id,price,normal_range,unit,sample_type,
 processing_time,method,interpretation,result_type,sub_parameters,test_structure,
 print_order,bold_result,show_department_heading,show_test_heading,show_in_report,status)
SELECT 'CBC','CBC (Complete Blood Count)','CBC',@cbc_cat,@cbc_dep,650,'See parameter ranges','',
       'Collect sample in EDTA tube','Same day','Automated hematology analyzer',
       'CBC results should always be interpreted in the context of clinical history and physical findings.',
       'text','[]','with_parameters',10,1,0,1,1,1
WHERE NOT EXISTS (SELECT 1 FROM tests WHERE name='CBC (Complete Blood Count)');

SET @cbc_id=(SELECT id FROM tests WHERE name IN ('CBC (Complete Blood Count)','Complete Blood Count (CBC)','Complete Blood Count') ORDER BY name='CBC (Complete Blood Count)' DESC LIMIT 1);

UPDATE tests SET
 test_code='CBC',
 name='CBC (Complete Blood Count)',short_name='CBC',category_id=@cbc_cat,department_id=@cbc_dep,
 price=650,normal_range='See parameter ranges',unit='',sample_type='Collect sample in EDTA tube',
 processing_time='Same day',method='Automated hematology analyzer',
 interpretation='CBC results should always be interpreted in the context of clinical history and physical findings.',
 result_type='text',test_structure='with_parameters',bold_result=1,
 show_department_heading=0,show_test_heading=1,show_in_report=1,status=1,
 sub_parameters='[
{"name":"HB","unit":"g/dl","formula_identifier":"hb","decimal_places":1,"order":1,"reference_ranges":[{"patient_type":"Male","critical_low":5,"range_min":13,"range_max":17,"critical_high":20,"unit":"g/dl"},{"patient_type":"Female","critical_low":5,"range_min":12,"range_max":15.5,"critical_high":18,"unit":"g/dl"},{"patient_type":"Child","critical_low":5,"range_min":11.5,"range_max":14.5,"critical_high":17,"unit":"g/dl"},{"patient_type":"Infant","critical_low":5,"range_min":9.5,"range_max":14,"critical_high":16,"unit":"g/dl"}]},
{"name":"Total RBC","unit":"x10^12/l","formula_identifier":"rbc","decimal_places":2,"order":2,"reference_ranges":[{"patient_type":"Male","critical_low":2,"range_min":4.5,"range_max":5.9,"critical_high":8,"unit":"x10^12/l"},{"patient_type":"Female","critical_low":2,"range_min":4.1,"range_max":5.1,"critical_high":8,"unit":"x10^12/l"},{"patient_type":"Child","critical_low":2,"range_min":4,"range_max":5.2,"critical_high":8,"unit":"x10^12/l"},{"patient_type":"Infant","critical_low":2,"range_min":3.8,"range_max":5.2,"critical_high":8,"unit":"x10^12/l"}]},
{"name":"HCT","unit":"%","formula_identifier":"hct","decimal_places":1,"order":3,"computation_formula":"hb*3","reference_ranges":[{"patient_type":"Male","critical_low":25,"range_min":40,"range_max":52,"critical_high":60,"unit":"%"},{"patient_type":"Female","critical_low":25,"range_min":36,"range_max":47,"critical_high":55,"unit":"%"},{"patient_type":"Child","critical_low":25,"range_min":35,"range_max":45,"critical_high":50,"unit":"%"},{"patient_type":"Infant","critical_low":25,"range_min":32,"range_max":44,"critical_high":50,"unit":"%"}]},
{"name":"MCV","unit":"fl","formula_identifier":"mcv","decimal_places":1,"order":4,"computation_formula":"hct/rbc*10","reference_ranges":[{"patient_type":"Male","critical_low":60,"range_min":80,"range_max":96,"critical_high":110,"unit":"fl"},{"patient_type":"Female","critical_low":60,"range_min":80,"range_max":96,"critical_high":110,"unit":"fl"},{"patient_type":"Child","critical_low":60,"range_min":75,"range_max":87,"critical_high":100,"unit":"fl"},{"patient_type":"Infant","critical_low":60,"range_min":70,"range_max":86,"critical_high":100,"unit":"fl"}]},
{"name":"MCH","unit":"pg","formula_identifier":"mch","decimal_places":1,"order":5,"computation_formula":"hb/rbc*10","reference_ranges":[{"patient_type":"Male","critical_low":20,"range_min":27,"range_max":33,"critical_high":36,"unit":"pg"},{"patient_type":"Female","critical_low":20,"range_min":27,"range_max":33,"critical_high":36,"unit":"pg"},{"patient_type":"Child","critical_low":20,"range_min":24,"range_max":30,"critical_high":36,"unit":"pg"},{"patient_type":"Infant","critical_low":20,"range_min":23,"range_max":31,"critical_high":36,"unit":"pg"}]},
{"name":"MCHC","unit":"g/dl","formula_identifier":"mchc","decimal_places":1,"order":6,"computation_formula":"hb/hct*100","reference_ranges":[{"patient_type":"Male","critical_low":25,"range_min":32,"range_max":36,"critical_high":40,"unit":"g/dl"},{"patient_type":"Female","critical_low":25,"range_min":32,"range_max":36,"critical_high":40,"unit":"g/dl"},{"patient_type":"Child","critical_low":25,"range_min":32,"range_max":36,"critical_high":40,"unit":"g/dl"},{"patient_type":"Infant","critical_low":25,"range_min":32,"range_max":36,"critical_high":40,"unit":"g/dl"}]},
{"name":"Platelets","unit":"x10^3/µL","decimal_places":0,"order":7,"reference_ranges":[{"patient_type":"Male","critical_low":50,"range_min":150,"range_max":450,"critical_high":600,"unit":"x10^3/µL"},{"patient_type":"Female","critical_low":50,"range_min":150,"range_max":450,"critical_high":600,"unit":"x10^3/µL"},{"patient_type":"Child","critical_low":50,"range_min":150,"range_max":450,"critical_high":600,"unit":"x10^3/µL"},{"patient_type":"Infant","critical_low":50,"range_min":150,"range_max":450,"critical_high":600,"unit":"x10^3/µL"}]},
{"name":"WBC","unit":"10^3/ul","decimal_places":1,"order":8,"group":"Differential Leukocytes Count","reference_ranges":[{"patient_type":"Male","critical_low":1,"range_min":4,"range_max":11,"critical_high":30,"unit":"10^3/ul"},{"patient_type":"Female","critical_low":1,"range_min":4,"range_max":11,"critical_high":30,"unit":"10^3/ul"},{"patient_type":"Child","critical_low":1,"range_min":5,"range_max":14.5,"critical_high":30,"unit":"10^3/ul"},{"patient_type":"Infant","critical_low":1,"range_min":6,"range_max":17.5,"critical_high":30,"unit":"10^3/ul"}]},
{"name":"Neutrophils","unit":"%","formula_identifier":"a","decimal_places":0,"order":9,"group":"Differential Leukocytes Count","reference_ranges":[{"patient_type":"Male","critical_low":0,"range_min":40,"range_max":75,"critical_high":95,"unit":"%"},{"patient_type":"Female","critical_low":0,"range_min":40,"range_max":75,"critical_high":95,"unit":"%"},{"patient_type":"Child","critical_low":0,"range_min":40,"range_max":75,"critical_high":95,"unit":"%"},{"patient_type":"Infant","critical_low":0,"range_min":40,"range_max":75,"critical_high":95,"unit":"%"}]},
{"name":"Lymphocytes","unit":"%","formula_identifier":"b","decimal_places":0,"order":10,"group":"Differential Leukocytes Count","reference_ranges":[{"patient_type":"Male","critical_low":0,"range_min":20,"range_max":45,"critical_high":90,"unit":"%"},{"patient_type":"Female","critical_low":0,"range_min":20,"range_max":45,"critical_high":90,"unit":"%"},{"patient_type":"Child","critical_low":0,"range_min":20,"range_max":45,"critical_high":90,"unit":"%"},{"patient_type":"Infant","critical_low":0,"range_min":20,"range_max":45,"critical_high":90,"unit":"%"}]},
{"name":"Eosinophils","unit":"%","formula_identifier":"c","decimal_places":0,"order":11,"group":"Differential Leukocytes Count","reference_ranges":[{"patient_type":"Male","critical_low":0,"range_min":1,"range_max":6,"critical_high":15,"unit":"%"},{"patient_type":"Female","critical_low":0,"range_min":1,"range_max":6,"critical_high":15,"unit":"%"},{"patient_type":"Child","critical_low":0,"range_min":1,"range_max":6,"critical_high":15,"unit":"%"},{"patient_type":"Infant","critical_low":0,"range_min":1,"range_max":6,"critical_high":15,"unit":"%"}]},
{"name":"Monocytes","unit":"%","formula_identifier":"mono","decimal_places":0,"order":12,"group":"Differential Leukocytes Count","computation_formula":"100-a-b-c-d","reference_ranges":[{"patient_type":"Male","critical_low":0,"range_min":2,"range_max":8,"critical_high":15,"unit":"%"},{"patient_type":"Female","critical_low":0,"range_min":2,"range_max":8,"critical_high":15,"unit":"%"},{"patient_type":"Child","critical_low":0,"range_min":2,"range_max":8,"critical_high":15,"unit":"%"},{"patient_type":"Infant","critical_low":0,"range_min":2,"range_max":8,"critical_high":15,"unit":"%"}]},
{"name":"Basophils","unit":"%","formula_identifier":"d","decimal_places":0,"order":13,"group":"Differential Leukocytes Count","reference_ranges":[{"patient_type":"Male","critical_low":0,"range_min":0,"range_max":1,"critical_high":5,"unit":"%"},{"patient_type":"Female","critical_low":0,"range_min":0,"range_max":1,"critical_high":5,"unit":"%"},{"patient_type":"Child","critical_low":0,"range_min":0,"range_max":1,"critical_high":5,"unit":"%"},{"patient_type":"Infant","critical_low":0,"range_min":0,"range_max":1,"critical_high":5,"unit":"%"}]}
]'
WHERE id=@cbc_id;

UPDATE tests SET status=0
WHERE id<>@cbc_id
  AND (
      LOWER(name) LIKE '%complete blood count%'
      OR LOWER(name)='cbc'
      OR short_name='CBC'
  );