IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('HeinemanToNavision')))
THEN Drop procedure HeinemanToNavision
END IF

GO

create function HeinemanToNavision(in @row varchar(1024), in @filename varchar(64))
returns integer
begin
  declare @rowtype varchar(2);
  declare @invoice_item_id integer;
  declare @invoice_id integer;
  declare @d1 varchar(64);
  declare @d2 varchar(64);
  declare @d3 varchar(64);
  declare @d4 varchar(64);
  declare @d5 varchar(64);
  declare @d6 varchar(64);
  declare @d7 varchar(64);
  declare @d8 varchar(64);
  declare @d9 varchar(64);
  declare @d10 varchar(64);
  declare @d11 varchar(64);
  declare @d12 varchar(64);
  declare @d13 varchar(64);
  declare @d14 varchar(64);
  declare @d15 varchar(64);
  declare @d16 varchar(64);
  declare @d17 varchar(64);
  declare @d18 varchar(64);
  -- ---------------------
  set @rowtype = substring(@row, 5, 2);
  case @rowtype
    when 43 then
      -- [Invoice Data - Widened Header Record]
      -- set @d1 = '';
      -- cargo ??
      set @d2 = '1';
      -- source_syscode (1: Navision, 2: Heineman)
      set @d3 = '2';
      -- import_document ( Heinamnn filename or navision document )
      set @d4 = @filename;
      -- import_timestamp ( FTP import timestamp; Filled by DTA when all items from the invoice are imported )
      set @d5 = DateFormat(current timestamp, 'DD.MM.YYYY HH:NN:SS');
      -- process_a_timestamp ( Navision processing Part.A )
      set @d6 = '';
      -- process_b_timestamp ( Navision processing Part.B )
      set @d7 = '';
      -- supplier_customer_no ( Customer number, coming from the supplier; Heineman field(message 43) : Customer-no)
      set @d8 = substring(@row, 8, 7);
      -- invoice_no ( Invoice number(supplier); Heineman field(message 43) : Invoice No )
      set @d9 = substring(@row, 32, 7);
      -- invoice_type ( 1 : Debit, 2 : Credit; Heineman field(message 43) : Type of Invoice )
      set @d10 = substring(@row, 47, 1);
      if @d10 = 'R' then set @d10 = '1' else set @d10 = '2' end if;
      -- invoice_date ( Invoice date; Heineman field (message 43) : Invoice date )
      set @d11 = substring(@row, 45, 2) || '.' || substring(@row, 43, 2) || '.' || substring(@row, 39, 4);
      -- supplier_order_no ( Order number,coming from supplier; Heineman field (message 43) : Order-no (internal order-no GH) )
      set @d12 = substring(@row, 136, 20);
      -- invoice_amount ( Net invoice ammount; Heineman field (message 43) : Net-total )
      set @d13 = substring(@row, 59, 9) || '.' || substring(@row, 68, 2);
      -- currency_code ( Currency code - 3 symbol code; Heineman field (message 43) : Currency )
      set @d14 = substring(@row, 123, 3);
      -- items_count ( Number of the items in the invoice; Heineman field (message 43) : No of items per invoice )
      set @d15 = substring(@row, 156, 5);
      
      Insert Into Invoice_Data(cargo_id, source_syscode, import_document, import_timestamp, process_a_timestamp,
        process_b_timestamp, supplier_customer_no, invoice_no, invoice_type, invoice_date, supplier_order_no,
        invoice_amount, currency_code, items_count)
      Values(@d2, @d3, @d4, @d5, @d6, @d7, @d8, @d9, @d10, @d11, @d12, @d13, @d14, @d15);

    when 46 then
      -- [Invoice Information - Widened Position Record]
	  -- priema se che redovete za fakturite se podavat vednaga sled kato se dobavi fakturata
      set @invoice_id = (select max(id) from Invoice_Data);
      -- import_invoice (Reference to the import_invoice identity)
      set @d1 = substring(@row, 86, 7);
      -- item_order_index (1, 2, ... Filled by DTA, starting from 1 for every invoice)
	  set @d2 = IsNull((select max(item_order_index) from Item_Data where import_invoice=@d1), 0) + 1;
      -- Part.A: Heinemann invoices
      -- supplier_item_no (Supplier article code; Heineman field(message 46) : Article-No GH)
	  set @d3 = substring(@row, 8, 13);
	  -- navision_item_no (Navision commodity ID)
	  set @d4 = '';
	  -- supplier_qty (Original qty; Heineman field (message 46) : Quantity)
      set @d5 = substring(@row, 21, 10) || '.' || substring(@row, 31, 3);
      -- qty (In retail unit. Calculated by Navision during Part A, based on supplier_qty and qty_factor)
	  set @d6 = '';
	  -- price_syscode (1, 2, 3 from Heinemann Price Unit filed; Heineman field (message 46): Price Unit)
      set @d7 = substring(@row, 44, 1);
      -- qty_factor (Filled by Navision after part A.)
	  set @d8 = '';
	  -- supplier_price (Original price; Heineman field(message 46): Price)
      set @d9 = substring(@row, 36, 6) || '.' || substring(@row, 42, 2);
      -- price (For sales unit. Calculated by Navision during Part A, based on supplier_price and qty_factor)
	  set @d10 = '';
	  -- supplier_name (Article description, coming from the supplier; Heineman field (message 46) : Article description)
      set @d11 = substring(@row, 104, 30);
      -- barcode (Barcode, coming from the supplier. Need correction if UPC code - has leading 0, that needs toberemoved; heineman field(message 46) : EAN-Code)
      set @d12 = substring(@row, 134, 13);
      while ((left(@d12, 1) = '0') and (length(@d12) > 0)) loop
        set @d12 = substring(@d12, 2);
      end loop;
      -- reference_code (Reference code from supplier; Heineman field (message 46) : Reference-No.supplier)
	  set @d13 = substring(@row, 147, 14);
      -- Part.B: After delivery control (phisical checks)
      -- qty_adjust
	  set @d14 = '';
	  -- qty_damaged
	  set @d15 = '';
	  -- good_before_date
      set @d16 = '';
      -- vendor_batch_code
	  set @d17 = '';
	  -- real_navision_item_no navision_code (For identified commodities (via barcodes and humans))
      set @d18 = '';

      Insert Into Item_Data(invoice_id,import_invoice, item_order_index, supplier_item_no, navision_item_no,
	    supplier_qty, qty, price_syscode, qty_factor, supplier_price, price, supplier_name, barcode,
        reference_code, qty_adjust, qty_damaged, good_before_date, vendor_batch_code, navision_code)
      Values(@invoice_id, @d1, @d2, @d3, @d4, @d5, @d6, @d7, @d8, @d9, @d10, @d11, @d12, @d13, @d14, @d15, @d16, @d17, @d18);

    when 47 then
      -- [Invoice Information - Shipping data (main/subposition)]
	  -- priema se che reda za cargo-to se podava vednaga sled reda za reda ot fakturata
      set @invoice_item_id = (select max(id) from Item_Data);
	  -- order no
	  set @d1 = substring(@row, 8, 7);
	  -- dispatch note position
	  set @d2 = substring(@row, 15, 5);
      -- supplier_item_no 
	  set @d3 = substring(@row, 20, 13);
	  -- supplier_qty
      set @d4 = substring(@row, 33, 10) || '.' || substring(@row, 43, 3);
	  -- box_from
	  set @d5 = substring(@row, 46, 6);
	  -- box_to
	  set @d6 = substring(@row, 52, 6);
	  -- no_boxes
	  set @d7 = substring(@row, 58, 6);
	  -- pallet barcode
	  set @d8 = substring(@row, 64, 20);
	  
	  insert into item_cargo(item_id, order_no, dispatch_note_position, supplier_item_no, supplier_qty, box_no_from, box_no_to, no_boxes, pallet_barcode)
	  values (@invoice_item_id, @d1, @d2, @d3, @d4, @d5, @d6, @d7, @d8);
    else return(1);
  end case;
  return(0);
end
-- [05.Mar.2008] - Yanko, created
-- [16.July.2008] - Nixon, message 47 added