🧑Xóa dữ liệu 1 khách hàng

// Some code
//DECLARE @BranchID NVARCHAR(200) = '159,'
//DECLARE @Modified_By INT = -1 ----vết

SELECT ID = Item
INTO #DT
FROM SplitString(@BranchID,',')

SELECT CustID = C.ID
INTO #DTCust
FROM VTT_Customer C
INNER JOIN #DT B ON C.Branch_ID = B.ID
WHERE C.State = 1


UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer							C	INNER JOIN #DTCust CC ON CC.CustID = C.ID   WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Assay					C	INNER JOIN #DTCust CC ON CC.CustID = C.CustomerID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Card					C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Consult_Less			C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Delete					C	INNER JOIN #DTCust CC ON CC.CustID = C.CustomerID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Desposit				C	INNER JOIN #DTCust CC ON CC.CustID = C.CustomerID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_History					C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM [VTT_Customer_Image_Folder]			C	INNER JOIN #DTCust CC ON CC.CustID = C.CustomerID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Labo					C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Patient_Record			C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Payment					C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Payment_Card			C	INNER JOIN #DTCust CC ON CC.CustID = C.CustomerID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Payment_Detail			C	INNER JOIN #DTCust CC ON CC.CustID = C.CustomerID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Payment_Medicine		C	INNER JOIN #DTCust CC ON CC.CustID = C.CustomerID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Prescription_Medicine	C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.State= 0 FROM VTT_Customer_Relationship			C	INNER JOIN #DTCust CC ON ','+ C.Customer_Token + ',' LIKE '%,' + CAST(CC.CustID AS NVARCHAR(200))  + ',%'	WHERE C.State = 1 
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Status					C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Status_Dental			C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0,IsChoose = -1 FROM VTT_Customer_Tab		C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE (C.State=1 OR (C.State=0 AND C.IsChoose=0))  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Tab_Commission			C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Card_Commission			C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Tele_Commission			C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Treatment_Commission	C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Treatment_Less			C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Treatment_Text      	C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Treatment_Work			C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0 FROM VTT_Customer_Tab_Treatment_Plan 		C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0,IsCancel = 0 FROM VTT_Schedule			C	INNER JOIN #DTCust CC ON CC.CustID = C.Customer_ID 	WHERE (C.State = 1 OR (C.State =0 AND C.IsCancel = 1))
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0  FROM VTT_Customer_StatusTeeth			C INNER JOIN #DTCust B ON B.CustID = C.CustomerID 		WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0  FROM VTT_CustomerCare_Value				C INNER JOIN #DTCust B ON B.CustID = C.CustomerID 		WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State= 0  FROM VTT_Ticket							C INNER JOIN #DTCust B ON B.CustID = C.customer_id		WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State = 0 FROM VTT_Customer_Anamnesis_History C INNER JOIN #DTCust D ON D.CustID = C.CustomerID			WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State = 0 FROM VTT_Customer_Dynamic C INNER JOIN #DTCust D ON D.CustID = C.CustomerID						WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State = 0 FROM VTT_Customer_Properties C INNER JOIN #DTCust D ON D.CustID = C.CustomerID					WHERE C.State = 1  
UPDATE C SET C.ModifiedBy = @Modified_By, C.State = 0 FROM VTT_Customer_Measure C INNER JOIN #DTCust D ON D.CustID = C.CustomerID						WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State = 0 FROM VTT_Customer_Diagnose C INNER JOIN #DTCust D ON D.CustID = C.CustomerID						WHERE C.State = 1  
UPDATE C SET C.Modified_by = @Modified_By, C.State = 0 FROM VTT_Customer_Insurance C INNER JOIN #DTCust D ON D.CustID = C.Customer_ID					WHERE C.State = 1  

UPDATE C
SET C.State = 0
,C.Modified_by = @Modified_By
FROM VTT_Customer_InsuranceDetail C 
INNER JOIN VTT_Customer_Insurance L ON L.ID = C.CustInsurID
INNER JOIN #DTCust B ON B.CustID = L.Customer_ID
 WHERE C.State = 1  

UPDATE C
SET C.State = 0
,C.ModifiedBy = @Modified_By
FROM VTT_Customer_Diagnose_Detail C 
INNER JOIN VTT_Customer_Diagnose L ON L.ID = C.DiaID
INNER JOIN #DTCust B ON B.CustID = L.CustomerID
WHERE C.State = 1  

UPDATE C
SET C.State = 0 
FROM VTT_Customer_MeasureDetail C 
INNER JOIN VTT_Customer_Measure L ON L.ID = C.MeasureID
INNER JOIN #DTCust B ON B.CustID = L.CustomerID
WHERE C.State = 1  

UPDATE C
SET C.State = 0
,C.Modified_by = @Modified_By
FROM VTT_Customer_Properties_Detail C 
INNER JOIN VTT_Customer_Properties L ON L.ID = C.PropertyID
INNER JOIN #DTCust B ON B.CustID = L.CustomerID
WHERE C.State = 1  

UPDATE C
SET C.State = 0
,C.Modified_by = @Modified_By
FROM VTT_Customer_Dynamic_Detail C 
INNER JOIN VTT_Customer_Dynamic L ON L.ID = C.DynID
INNER JOIN #DTCust B ON B.CustID = L.CustomerID
WHERE C.State = 1  

UPDATE C
SET C.State = 0
,C.Modified_by = @Modified_By
FROM VTT_Customer_Labo_Value C 
INNER JOIN VTT_Customer_Labo L ON L.ID = C.LaboID
INNER JOIN #DTCust B ON B.CustID = L.Customer_ID
WHERE C.State = 1  

UPDATE C
SET C.State = 0
,C.Modified_by = @Modified_By
FROM VTT_Customer_Prescription_Medicine_Detail	C 
INNER JOIN VTT_Customer_Prescription_Medicine M ON M.ID = C.Customer_Medicine_ID
INNER JOIN #DTCust B ON B.CustID = M.Customer_ID 
WHERE C.State = 1  

UPDATE C
SET C.State = 0
,C.Modified_by = @Modified_By
FROM VTT_Customer_StatusTeeth_Detail C 
INNER JOIN VTT_Customer_StatusTeeth T ON T.ID = C.MasterID
INNER JOIN #DTCust B ON B.CustID = T.CustomerID 
 WHERE C.State = 1  
   

UPDATE C
SET C.State = 0
,C.modified_by = @Modified_By
FROM VTT_Ticket_Value		C 
INNER JOIN VTT_Ticket T ON T.ID = C.ticket_id
INNER JOIN #DTCust B ON B.CustID = T.customer_id 
WHERE C.State = 1  

 

UPDATE C
SET C.State = 0 
FROM VTT_Schedule_Cancel	C
INNER JOIN VTT_Schedule S ON C.Schedule_ID = C.Schedule_ID
INNER JOIN #DTCust B ON B.CustID = S.Customer_ID
WHERE C.State = 1  
 

UPDATE C 
SET C.State= 0 
,C.Modified_by = @Modified_By
FROM [VTT_Customer_Image_Folder_Image]	C 
INNER JOIN VTT_Customer_Image_Folder F ON F.ID = C.FolderID
INNER JOIN #DTCust CC ON CC.CustID = F.CustomerID
WHERE C.State = 1  

UPDATE C SET
C.State= 0 
,C.Modified_by = @Modified_By
FROM VTT_Schedule_RealTime C	
INNER JOIN VTT_Schedule S ON S.ID = C.Schedule_ID
INNER JOIN #DTCust CC ON CC.CustID = S.Customer_ID
WHERE C.State = 1  
													  
DROP TABLE #DT,#DTCust