Hi,
I need to add datetime field to my table and I don’t know if how can I insert datetime with condition.
I add this fields:
chemicalweighing_dateEntry
compounding_dateEntry
extrusion_dateEntry
forming_dateEntry
deflashing_dateEntry
kanban_dateEntry
virtual_dateEntry
total_dateEntry
here is the scenario.
first I need to used query insert which have now that I only need is to add datetime fields.
I have 5 forms , first I have a form for chemicalweighing so in my query I need to save datetime for chemicalweighing_dateEntry, then after chemicalweighing the second is compounding, i need to update the table and because I am on the second form the chemicalweighing_dateEntry should be 0000-00-00 00:00:00 or null and the dateEntry will be on the compounding_dateEntry because I am on the second form. also in the other forms.
here is my code now where I need your help to add code condition for dateentry.
$dateEntry = date("Y-m-d H:i:s");
$sql = "INSERT INTO kanban_data (REFNUM, LOT_CODE, PCODE, wip_chemicalweighing, wip_compounding, wip_extrusion, wip_forming, wip_deflashing,
chemicalweighing_dateEntry, compounding_dateEntry, extrusion_dateEntry, forming_dateEntry, deflashing_dateEntry,
kanban_dateEntry, virtual_dateEntry, total_dateEntry)
(SELECT REFNUM, LOT_CODE, SUBSTRING(LOT_CODE, 9,4), ROUND(IF (NOT ISNULL(SUM(compounding)), 0, SUM(chemicalweighing)),2) AS wip_chemicalweighing,
ROUND(IF (NOT ISNULL(SUM(extrusion)), 0, SUM(compounding)),2) AS wip_compounding,
ROUND(IF (NOT ISNULL(SUM(forming)), 0, SUM(extrusion)),2) AS wip_extrusion,
ROUND(IF (NOT ISNULL(SUM(deflashing)), 0, SUM(forming)),2) AS wip_forming,
ROUND(SUM(deflashing),2) AS wip_deflashing
FROM kanban WHERE REFNUM = '$currentReference' AND LOT_CODE = '$lotCode' GROUP BY REFNUM)
";
$sql = "UPDATE kanban_data kd SET wip_chemicalweighing = (SELECT ROUND(IF (NOT ISNULL(SUM(compounding)), 0, SUM(chemicalweighing)),2) AS wip_chemicalweighing FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM),
wip_compounding = (SELECT ROUND(IF (NOT ISNULL(SUM(extrusion)), 0, SUM(compounding)),2) AS wip_compounding FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM),
wip_extrusion = (SELECT ROUND(IF (NOT ISNULL(SUM(forming)), 0, SUM(extrusion)),2) AS wip_extrusion FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM),
wip_forming = (SELECT ROUND(IF (NOT ISNULL(SUM(deflashing)), 0, SUM(forming)),2) AS wip_forming FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM),
wip_deflashing = (SELECT ROUND(SUM(deflashing),2) AS wip_deflashing FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM)
";