
UPDATE pos_payments
SET sales_voucher_id = (
		SELECT sv.CANCEL_FOR_SALES_VOUCHER_ID
		FROM sales_vouchers sv
		WHERE sv.CANCEL_FOR_SALES_VOUCHER_ID IS NOT NULL
			AND sv.tenant_no = pos_payments.tenant_no
			AND sv.pos_cd = pos_payments.SALES_VOUCHER_POS_CD
			AND sv.sales_voucher_id = pos_payments.SALES_VOUCHER_ID
);





Delete from sales_vouchers  where CANCEL_FOR_SALES_VOUCHER_ID is not null;

--Geht nur so hässlich auf der Derby. Kein Umsetzen mehrerer Werte mit einem Select möglich
UPDATE pos_payments
SET SALES_VOUCHER_ID = (Select distinct sv.SALES_VOUCHER_ID from sales_vouchers sv   
			where pos_payments.PAYMENT_REF is not null and 
			sv.tenant_no = pos_payments.tenant_no
			AND sv.pos_cd = pos_payments.POS_CD
			AND lower(pos_payments.PAYMENT_REF) = lower(sv.SALES_VOUCHER_CD)),
SALES_VOUCHER_POS_CD= (Select distinct sv.POS_CD from sales_vouchers sv   
			where pos_payments.PAYMENT_REF is not null and 
			sv.tenant_no = pos_payments.tenant_no
			AND sv.pos_cd = pos_payments.POS_CD
			AND lower(pos_payments.PAYMENT_REF) = lower(sv.SALES_VOUCHER_CD))
		;
			


CREATE SEQUENCE sales_voucher_use_id
AS INTEGER
START WITH 1;

INSERT INTO sales_voucher_uses(TENANT_NO,POS_CD,SALES_VOUCHER_ID,SALES_VOUCHER_USE_ID,SALES_VOUCHER_USE_TS,CANCEL_REASON,USED_POS_CD,USED_DRAWER_NO,USED_POS_SESSION_ID,USED_POS_PAYMENT_ID,USED_SALES_INV_ID,USED_SALES_INV_CD,CUSTOMER_NO,CUSTOMER_SALUTATION,CUSTOMER_TITLE,CUSTOMER_FIRST_NM,CUSTOMER_CONTACT_NM,CUSTOMER_STREET,CUSTOMER_COUNTRY_CD,CUSTOMER_POSTAL_CD,CUSTOMER_CITY,CUSTOMER_CONTRACT_NO,CUSTOMER_CONTRACT_NM,EMPLOYEE_NO,EMPLOYEE_NM,VOUCHER_STATE,IS_LATEST 
 )
SELECT pp.TENANT_NO
	,pp.pos_cd
	,pp.sales_voucher_id
	,next value for sales_voucher_use_id
	,pp.PAYMENT_TS
	,pp.CANCEL_REASON
	,pp.SALES_VOUCHER_POS_CD
	,pp.DRAWER_NO
	,pp.POS_SESSION_ID
	,pp.POS_PAYMENT_ID
	,pp.SALES_INV_ID
	,si.SALES_INV_CD AS USED_SALES_INV_CD
	,si.CUSTOMER_NO
	,si.CUSTOMER_SALUTATION
	,si.CUSTOMER_TITLE
	,si.CUSTOMER_FIRST_NM
	,si.CUSTOMER_CONTACT_NM
	,si.CUSTOMER_STREET
	,si.CUSTOMER_COUNTRY_CD
	,si.CUSTOMER_POSTAL_CD
	,si.CUSTOMER_CITY
	,si.CUSTOMER_CONTRACT_NO
	,si.CUSTOMER_CONTRACT_NM
	,si.EMPLOYEE_NO
	,si.EMPLOYEE_NM
	,case pp.PAYMENT_DIRECTION  when  1 then (case  when pp.CANCEL_FOR_POS_SESSION_ID is not null then 2 else 3 end) 
		when  2 then(case when  pp.CANCEL_FOR_POS_SESSION_ID is not null then 5 else 1 end) end   as VOUCHER_STATE 
	, 0  as IS_LATEST
FROM POS_PAYMENTS pp
join SALES_INVS si on pp.tenant_no = si.tenant_no and pp.pos_cd = si.pos_cd and pp.SALES_INV_ID = si.SALES_INV_ID
where pp.sales_voucher_id > 0;
-- das gab Performance Probleme
--where pp.sales_voucher_id is not null;

DROP SEQUENCE sales_voucher_use_id RESTRICT;

UPDATE SALES_VOUCHER_USES 
SET IS_LATEST = 
(
Select case when SALES_VOUCHER_USES.sales_voucher_use_ts = MaxTS then 1 else 0 end as isLatest FROM
(Select tenant_no, pos_cd,  sales_voucher_id, max(sales_voucher_use_ts) as MaxTS from sales_voucher_uses svu2 group by tenant_no, pos_cd,  sales_voucher_id ) t
where  t.tenant_no = SALES_VOUCHER_USES.tenant_no and t.pos_cd = SALES_VOUCHER_USES.pos_cd and SALES_VOUCHER_USES.sales_voucher_id = t.sales_voucher_id 
);


UPDATE pos_payments set payment_booking_ts = payment_ts ;

UPDATE d_sales_dln_pos_work_records set operating_employee_factor = 1;

UPDATE sales_dln_pos_work_records set operating_employee_factor = 1;


INSERT INTO pos_day_closures (TENANT_NO, POS_CD, CLOSURE_DAY, CLOSURE_CHANGE_TS, EMPLOYEE_NM, STATUS, UPDATE_CNT)
Select distinct tenant_no, pos_cd, cast(cast(payment_ts as DATE)as timestamp), current_timestamp, 'Automated entry', 3, 1 FROM pos_payments;

INSERT INTO DRAWER_DAY_CLOSURES
Select distinct pd.tenant_no, pd.pos_cd, pd.DRAWER_NO , cast(cast(payment_ts as DATE)as timestamp),2, 'auto closed', 1 FROM pos_payments pp join pos_drawers pd on 1=1;


INSERT INTO EMPLOYEE_GROUPS Select tenant_no, company_no, 'Abschluss öffnen', 'Kassenabschluss öffnen' FROM COMPANIES where not exists(SELECT 1 from employee_groups eg where eg.employee_group_cd = 'Abschluss öffnen');
INSERT INTO EMPLOYEE_GROUPS Select tenant_no, company_no, 'Fillialbericht', 'Fillialbericht' FROM COMPANIES  where not exists(SELECT 1 from employee_groups eg where eg.employee_group_cd = 'Fillialbericht');
INSERT INTO EMPLOYEE_GROUPS Select tenant_no, company_no, 'Kassenabschluss', 'Kassenabschluss' FROM COMPANIES  where not exists(SELECT 1 from employee_groups eg where eg.employee_group_cd = 'Kassenabschluss');

insert into EMPLOYEE_GROUP_PERMISSIONS  Select tenant_no, company_no, 'Abschluss öffnen', 'P_OPEN_DAY_CLOSURE', 'Kassenabschluss öffnen' FROM COMPANIES where not exists(
SELECT 1 from EMPLOYEE_GROUP_PERMISSIONS egp where egp.EMPLOYEE_GROUP_CD = 'Abschluss öffnen' and egp.PERMISSION_CD = 'P_OPEN_DAY_CLOSURE');
insert into EMPLOYEE_GROUP_PERMISSIONS  Select tenant_no, company_no, 'Fillialbericht', 'P_POS_REPORT', 'Fillialbericht' FROM COMPANIES where not exists(
SELECT 1 from EMPLOYEE_GROUP_PERMISSIONS egp where egp.EMPLOYEE_GROUP_CD = 'Fillialbericht' and egp.PERMISSION_CD = 'P_POS_REPORT');
insert into EMPLOYEE_GROUP_PERMISSIONS  Select tenant_no, company_no, 'Kassenabschluss', 'P_DO_DAY_CLOSURE', 'Kassenabschluss' FROM COMPANIES where not exists(
SELECT 1 from EMPLOYEE_GROUP_PERMISSIONS egp where egp.EMPLOYEE_GROUP_CD = 'Kassenabschluss' and egp.PERMISSION_CD = 'P_DO_DAY_CLOSURE');

UPdate sales_inv_positions 
SET profession_no = (
SELECT distinct ec.profession_no
	FROM 
	sales_invs si 
JOIN employee_contracts ec ON si.tenant_no = ec.tenant_no
	AND si.company_no = ec.company_no
	AND ec.department_no = si.department_no
	AND ec.businessunit_no = si.businessunit_no
	AND cast(ec.valid_from AS DATE) <= cast(si.sales_inv_ts AS DATE)
 AND (cast(ec.valid_to AS DATE) >= cast(si.sales_inv_ts AS DATE) or ec.valid_to is null)
WHERE ec.employee_no = sales_inv_positions.employee_no AND sales_inv_positions.tenant_no = si.tenant_no
	AND si.pos_cd = sales_inv_positions.pos_cd
	AND si.sales_inv_id = sales_inv_positions.sales_inv_id)
	where profession_no IS NULL AND employee_no IS NOT NULL;
	
	
	UPdate sales_inv_positions 
SET profession_nm = (
SELECT distinct p.profession_nm
FROM 
	 professions p 
WHERE p.tenant_no = sales_inv_positions.tenant_no AND p.company_no = sales_inv_positions.company_no AND p.profession_no =sales_inv_positions.profession_no  )
WHERE sales_inv_positions.profession_no is NOT null AND sales_inv_positions.profession_nm IS NULL;

	

CREATE TABLE "APP"."SALES_DLN_POS_WORK_RECORDS_2"
(
   TENANT_NO int NOT NULL,
   POS_CD varchar(10) NOT NULL,
   SALES_DLN_ID int NOT NULL,
   SALES_DLN_POSITION_ID int NOT NULL,
   SALES_DLN_POS_REC_ID int NOT NULL,
   COMPANY_NO int,
   OPERATING_EMPLOYEE_NO int,
   OPERATING_EMPLOYEE_NM varchar(200),
   OPERATING_PROFESSION_NO int,
   OPERATING_PROFESSION_NM varchar(200),
   WORK_AMOUNT decimal(20),
   OPERATING_EMPLOYEE_FACTOR decimal(10),
   PRIMARY KEY (TENANT_NO, POS_CD,SALES_DLN_ID,SALES_DLN_POSITION_ID,SALES_DLN_POS_REC_ID)
);

Insert into "SALES_DLN_POS_WORK_RECORDS_2"
SELECT  pwr.TENANT_NO,
	pwr.POS_CD,
	pwr.SALES_DLN_ID,
	pwr.SALES_DLN_POSITION_ID,
	pwr.SALES_DLN_POS_REC_ID,
	pwr.COMPANY_NO,
	pwr.OPERATING_EMPLOYEE_NO,
	pwr.OPERATING_EMPLOYEE_NM,
	max(ec.profession_no),
	'N/A',
	pwr.WORK_AMOUNT,
	pwr.OPERATING_EMPLOYEE_FACTOR
FROM sales_dln_pos_work_records pwr 
	join sales_invs si ON pwr.tenant_no = si.tenant_no
	AND si.pos_cd = pwr.pos_cd
	AND si.sales_dln_id = pwr.sales_dln_id
JOIN employee_contracts ec ON si.tenant_no = ec.tenant_no
	AND si.company_no = ec.company_no
	AND ec.department_no = si.department_no
	AND ec.businessunit_no = si.businessunit_no
	AND cast(ec.valid_from AS DATE) <= cast(si.sales_inv_ts AS DATE)
    AND (cast(ec.valid_to AS DATE) >= cast(si.sales_inv_ts AS DATE) or ec.valid_to is null)
WHERE ec.employee_no = pwr.operating_employee_no  and pwr.operating_profession_no IS NULL AND pwr.operating_employee_no is not null 
group by pwr.TENANT_NO,
	pwr.POS_CD,
	pwr.SALES_DLN_ID,
	pwr.SALES_DLN_POSITION_ID,
	pwr.SALES_DLN_POS_REC_ID,
	pwr.COMPANY_NO,
	pwr.OPERATING_EMPLOYEE_NO,
	pwr.OPERATING_EMPLOYEE_NM,
	pwr.WORK_AMOUNT,
	pwr.OPERATING_EMPLOYEE_FACTOR;

UPDATE SALES_DLN_POS_WORK_RECORDS SET OPERATING_PROFESSION_NO = (SELECT distinct operating_profession_no from SALES_DLN_POS_WORK_RECORDS_2 pwr where
pwr.TENANT_NO = SALES_DLN_POS_WORK_RECORDS.TENANT_NO and pwr.POS_CD = SALES_DLN_POS_WORK_RECORDS.pos_cd and 
	pwr.SALES_DLN_ID = SALES_DLN_POS_WORK_RECORDS.SALES_DLN_ID
and 	pwr.SALES_DLN_POSITION_ID = SALES_DLN_POS_WORK_RECORDS.SALES_DLN_POSITION_ID
and	pwr.SALES_DLN_POS_REC_ID = SALES_DLN_POS_WORK_RECORDS.SALES_DLN_POS_REC_ID)
where operating_profession_no is null and operating_employee_no is not null;

DROP TABLE SALES_DLN_POS_WORK_RECORDS_2;

UPdate sales_dln_pos_work_records 
SET operating_profession_nm = (
SELECT distinct p.profession_nm
FROM 
	 professions p 
WHERE p.tenant_no = sales_dln_pos_work_records.tenant_no AND p.company_no = sales_dln_pos_work_records.company_no AND p.profession_no =sales_dln_pos_work_records.operating_profession_no  )
WHERE sales_dln_pos_work_records.operating_profession_no is NOT null AND sales_dln_pos_work_records.operating_profession_nm IS NULL;