IF OBJECT_ID('tempdb..#ClientPercents') IS NOT NULL
  DROP TABLE #ClientPercents

/**********************************************
 *       Тип обновляемых счетов               *
 **********************************************/

DECLARE
  @ClientPurseType UNIQUEIDENTIFIER

SELECT TOP 1 @ClientPurseType = cptp_ID
FROM %ClientPurseTypes



/***********************************************************************
 *     Оплаченные пречеки из указанных подразделений в торговых днях,  *
 *  где дата закрытия попадает в диапазон просмотра                    *
 ***********************************************************************/

SELECT prch_ID 'prdate_prch_ID'--, chck_date = max(chck_date) 
INTO #PreChecksDate
FROM Prechecks
     JOIN Checks ON chck_prch_ID = prch_ID
WHERE prch_pcst_ID = 1
and chck_date >=  (select (GETDATE()-30))
and chck_date > '19.08.2019 23:59:00'
GROUP BY prch_ID

SELECT gest_id, clnt_ID,  sum(pcit_Price * pcit_Count) 'Pcit_Sum', prch_dvsn_id
INTO #PreChecksInDivisions
FROM PreChecks
JOIN #PreChecksDate     ON prdate_prch_ID = prch_ID
JOIN Divisions         ON prch_dvsn_ID = dvsn_ID
--JOIN Archives           ON prch_arch_ID = arch_ID
JOIN PrecheckItems      ON pcit_prch_ID = prch_ID
JOIN PrecheckStates     ON pcst_ID      = prch_pcst_ID
JOIN
(
  SELECT DISTINCT orit_pcit_ID, ordr_gest_ID
  FROM OrderItems
       JOIN Orders              ON orit_ordr_ID = ordr_ID
  UNION
  SELECT DISTINCT orti_pcit_ID, ordr_gest_ID
  FROM OrderTimeItems
       JOIN Orders              ON orti_ordr_ID = ordr_ID
) OI                     ON orit_pcit_ID = pcit_ID
JOIN Guests              ON ordr_gest_ID = gest_ID
JOIN Clients             ON gest_clnt_ID = clnt_ID
JOIN %ClientGroups       ON clnt_clgr_ID = clgr_ID

JOIN ClientPurseOperations on ('Начисление баллов ГС: ' + CAST(gest_id AS NVARCHAR(max))) like cpop_Description
JOIN ClientPurseTypes on cpop_cptp_ID = cptp_id

where cptp_id = @ClientPurseType
and cpop_date > '19.08.2019 23:59:00'
GROUP BY gest_id, clnt_ID, prch_dvsn_id


SELECT gest_id, clnt_ID,  sum(pcit_Price * pcit_Count) 'Pcit_Sum', prch_dvsn_id
INTO #PreChecksInDivisions1
FROM PreChecks
JOIN #PreChecksDate     ON prdate_prch_ID = prch_ID
JOIN Divisions         ON prch_dvsn_ID = dvsn_ID
--JOIN Archives           ON prch_arch_ID = arch_ID
JOIN PrecheckItems      ON pcit_prch_ID = prch_ID
JOIN PrecheckStates     ON pcst_ID      = prch_pcst_ID
JOIN
(
  SELECT DISTINCT orit_pcit_ID, ordr_gest_ID
  FROM OrderItems
       JOIN Orders              ON orit_ordr_ID = ordr_ID
  UNION
  SELECT DISTINCT orti_pcit_ID, ordr_gest_ID
  FROM OrderTimeItems
       JOIN Orders              ON orti_ordr_ID = ordr_ID
) OI                     ON orit_pcit_ID = pcit_ID
JOIN Guests              ON ordr_gest_ID = gest_ID
JOIN Clients             ON gest_clnt_ID = clnt_ID
JOIN %ClientGroups       ON clnt_clgr_ID = clgr_ID

join ClientPurseRegister on cprg_clnt_ID = gest_clnt_ID
JOIN ClientPurseTypes on cprg_cptp_ID = cptp_id
where cptp_id = @ClientPurseType
GROUP BY gest_id, clnt_ID, prch_dvsn_id


select p1.gest_ID, p1.clnt_ID, p1.Pcit_Sum, p1.prch_dvsn_ID
INTO #PreChecksInDivisions2
from #PreChecksInDivisions1 p1
left join #PreChecksInDivisions p on p.gest_ID  = p1.gest_id 
where p.gest_ID is null

------------------------------------------------



SELECT gest_id, clnt_ID 'cp_clnt_ID', @ClientPurseType 'cp_cptp_ID', getdate() 'cp_Date',
       --sum(pcit_Sum) * %percent 'cp_Sum'
  sum(pcit_Sum) * %percent 'cp_Sum'
 

, dvsn_name--prch_dvsn_id 'dev_id'
INTO #ClientPercents
FROM #PreChecksInDivisions2
  JOIN Divisions  ON  dvsn_ID = prch_dvsn_id
GROUP BY gest_id, clnt_ID, dvsn_name


INSERT INTO ClientPurseOperations(cpop_ID, cpop_Description,    cpop_clnt_ID, cpop_cptp_ID, cpop_Date, cpop_Sum, cpop_Name)
SELECT                            NEWID(), 'Начисление баллов  ГС: '+ CAST(gest_id AS NVARCHAR(max)),  cp_clnt_ID,   cp_cptp_ID,   cp_Date,   cp_Sum, dvsn_name                                 
FROM #ClientPercents


DROP TABLE #PreChecksInDivisions
DROP TABLE #PreChecksInDivisions1
DROP TABLE #PreChecksInDivisions2
DROP TABLE #PreChecksDate
DROP TABLE #ClientPercents