Du lieu co ban ma hang

Thong tin co ban

Tham so yeu cau

Truong phan hoi

Vi du phan hoi

{
  "item_data": [
    {
      "item_no": "ITEM-001",
      "item_name": "San pham thu nghiem",
      "item_spec": "Mo ta quy cach",
      "unit_no": "PCS",
      "second_unit_no": "BOX",
      "process_item": "ITEM-001",
      "process_code": "RT001",
      "item_sort": "A",
      "modify_no": "USER001",
      "tran_status": "AU"
    }
  ]
}

Logic xu ly

SQL cap nhat co so du lieu

Xac dinh them moi hoac cap nhat

SELECT COUNT(1) FROM ITEM WHERE ID = N'{item_no}'
-- count > 0 → UPDATE, count = 0 → INSERT

INSERT (Them moi ma hang)

INSERT INTO ITEM (
    ID, NAME, DESCRIPTION, UNIT, RELEASEUNIT,
    STDROUTEITEMID, STDROUTEID, ITEM001, ITEM002,
    ITEM004, ITEM005, CONVERTUNIT,
    LASTMAINTAINUSER, LASTMAINTAINDATETIME, FLAG
) VALUES (
    :ID, :NAME, :DESCRIPTION, :UNIT, :UNIT,
    :STDROUTEITEMID, :STDROUTEID, :ITEM001, :ITEM002,
    :ITEM004, :ITEM005, :CONVERTUNIT,
    :userid, N'{thoi gian hien tai}', 0
)

UPDATE (Cap nhat ma hang)

UPDATE ITEM SET
    NAME = :NAME,
    DESCRIPTION = :DESCRIPTION,
    UNIT = :UNIT,
    RELEASEUNIT = :UNIT,
    STDROUTEITEMID = CASE WHEN STDROUTEITEMID IS NULL OR STDROUTEITEMID = ''
                     THEN :STDROUTEITEMID ELSE STDROUTEITEMID END,
    STDROUTEID = CASE WHEN STDROUTEID IS NULL OR STDROUTEID = ''
                 THEN :STDROUTEID ELSE STDROUTEID END,
    ITEM001 = :ITEM001,
    ITEM002 = :ITEM002,
    ITEM004 = :ITEM004,
    ITEM005 = :ITEM005,
    CONVERTUNIT = :CONVERTUNIT,
    LASTMAINTAINUSER = :userid,
    LASTMAINTAINDATETIME = N'{thoi gian hien tai}',
    FLAG = (SELECT MAX(FLAG)+1 FROM ITEM WHERE ID = :ID)
WHERE ID = :ID

Bang material_catalog (item_sort = "M")

Xac dinh them moi hoac cap nhat

SELECT COUNT(1) FROM material_catalog WITH (NOLOCK)
WHERE material_id = N'{item_no}'
-- count > 0 → UPDATE, count = 0 → INSERT

INSERT (Them moi vat lieu)

INSERT INTO material_catalog (
    material_id, material_name, norm, unit_no,
    oper_date, oper_id, ENABLED, MODI_DATE
) VALUES (
    :ID,                    -- Ma hang (item_no)
    :NAME,                  -- Ten hang (item_name)
    :DESCRIPTION,           -- Quy cach (item_spec)
    :UNIT,                  -- Don vi (unit_no)
    N'{thoi gian hien tai}',
    :userid,
    :ENABLED,               -- Y/N (tran_status: AU→Y, D→N)
    N'{thoi gian hien tai}'
)

UPDATE (Cap nhat vat lieu)

UPDATE material_catalog SET
    material_name = :NAME,
    norm = :DESCRIPTION,
    unit_no = :UNIT,
    ENABLED = :ENABLED,
    MODI_DATE = N'{thoi gian hien tai}'
WHERE material_id = :ID