CREATE  PROCEDURE  [dbo].[Delete_Mandeh_Data]  As

-----(وايجاد مجدد آنها TmpOver_Under_Row - TmpOver_Under_Factor - TmpHeadH - TmpHavaleh - TmpSerial_File - TmpExp_File حذف لينكهاي جداول )--------------------------------------

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TmpExp_TmpHavaleh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TmpExp] DROP CONSTRAINT FK_TmpExp_TmpHavaleh

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TmpSerial_TmpHavaleh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TmpSerial] DROP CONSTRAINT FK_TmpSerial_TmpHavaleh

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tmp_Over_Under_Factor_TmpHeadH]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tmp_Over_Under_Factor] DROP CONSTRAINT FK_Tmp_Over_Under_Factor_TmpHeadH

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TmpHavaleh_TmpHeadH]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TmpHavaleh] DROP CONSTRAINT FK_TmpHavaleh_TmpHeadH

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tmp_Over_Under_Row_TmpHavaleh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tmp_Over_Under_Row] DROP CONSTRAINT FK_Tmp_Over_Under_Row_TmpHavaleh

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tmp_Over_Under_Row_TmpDrugHavaleh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tmp_Over_Under_Row] DROP CONSTRAINT FK_Tmp_Over_Under_Row_TmpDrugHavaleh

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Over_Under_Row_Havaleh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Over_Under_Row] DROP CONSTRAINT FK_Over_Under_Row_Havaleh

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Over_Under_Row_DrugHavaleh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Over_Under_Row] DROP CONSTRAINT FK_Over_Under_Row_DrugHavaleh


Truncate Table TmpExp
Truncate Table TmpSerial
Truncate Table TmpHavaleh
Truncate Table TmpHeadH

if exists (select * from sysobjects where name='Tmp_Over_Under_Factor')
  Truncate Table Tmp_Over_Under_Factor

if exists (select * from sysobjects where name='Tmp_Over_Under_Row')
Truncate Table Tmp_Over_Under_Row

if exists (select * from sysobjects where name='Over_Under_Row')
Truncate Table Over_Under_Row

if exists (select * from sysobjects where name='History_Delete_Sanad')
Truncate Table History_Delete_Sanad

if exists (select * from sysobjects where name='History_Delete_Ghest')
  Truncate Table History_Delete_Ghest

if exists (select * from sysobjects where name='History_Delete_HeadGH')
Truncate Table History_Delete_HeadGH

if exists (select * from sysobjects where name='History_Detail_Factors')
Truncate Table History_Detail_Factors


ALTER TABLE [dbo].[TmpHavaleh] ADD 
	CONSTRAINT [FK_TmpHavaleh_TmpHeadH] FOREIGN KEY 
	(
		[Id_Havaleh]
	) REFERENCES [dbo].[TmpHeadH] (
		[Id_Havaleh]
	) ON DELETE CASCADE 

ALTER TABLE [dbo].[TmpSerial] ADD 
	CONSTRAINT [FK_TmpSerial_TmpHavaleh] FOREIGN KEY 
	(
		[AutoId]
	) REFERENCES [dbo].[TmpHavaleh] (
		[AutoId]
	) ON DELETE CASCADE 


ALTER TABLE [dbo].[TmpExp] ADD 
	CONSTRAINT [FK_TmpExp_TmpHavaleh] FOREIGN KEY 
	(
		[AutoId]
	) REFERENCES [dbo].[TmpHavaleh] (
		[AutoId]
	) ON DELETE CASCADE 

ALTER TABLE [dbo].[Tmp_Over_Under_Factor] ADD 
	CONSTRAINT [FK_Tmp_Over_Under_Factor_TmpHeadH] FOREIGN KEY 
	(
		[Id_Havaleh]
	) REFERENCES [dbo].[TmpHeadH] (
		[Id_Havaleh]
	) ON DELETE CASCADE  NOT FOR REPLICATION 


------(وايجاد مجدد آنها Over_Under_Row - Over_Under_Factor - HeadH - Havaleh - Serial_File - Exp_File حذف لينكهاي جداول )--------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Exp_File_Havaleh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Exp_File] DROP CONSTRAINT FK_Exp_File_Havaleh

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Serial_File_Havaleh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Serial_File] DROP CONSTRAINT FK_Serial_File_Havaleh

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Havaleh_headh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Havaleh] DROP CONSTRAINT FK_Havaleh_headh

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Over_Under_Factor_headh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Over_Under_Factor] DROP CONSTRAINT FK_Over_Under_Factor_headh


Truncate Table Exp_File
Truncate Table Serial_File
Truncate Table Over_Under_Factor
Truncate Table Havaleh
Truncate Table HeadH
Truncate Table Karkerd
Truncate Table Stock

	

ALTER TABLE [dbo].[Havaleh] ADD 
	CONSTRAINT [FK_Havaleh_headh] FOREIGN KEY 
	(
		[Id_Havaleh]
	) REFERENCES [dbo].[headH] (
		[Id_Havaleh]


	) ON DELETE CASCADE 

ALTER TABLE [dbo].[Exp_File] ADD 
	CONSTRAINT [FK_Exp_File_Havaleh] FOREIGN KEY 
	(
		[AutoId]
	) REFERENCES [dbo].[Havaleh] (
		[AutoId]
	) ON DELETE CASCADE  ON UPDATE CASCADE 

ALTER TABLE [dbo].[Serial_File] ADD 
	CONSTRAINT [FK_Serial_File_Havaleh] FOREIGN KEY 
	(
		[AutoId]
	) REFERENCES [dbo].[Havaleh] (
		[AutoId]
	) ON DELETE CASCADE  ON UPDATE CASCADE 

ALTER TABLE [dbo].[Over_Under_Factor] ADD 
	CONSTRAINT [FK_Over_Under_Factor_headh] FOREIGN KEY 
	(
		[Id_Havaleh]
	) REFERENCES [dbo].[headH] (
		[Id_Havaleh]
	) ON DELETE CASCADE  ON UPDATE CASCADE 


-----------------------(TmpHeadS و TmpSanad حذف لينكها و ايجاد مجدد آنها در جداول)---------------------
Truncate Table TmpSanad
Truncate Table TmpAcc_Cheqe_Link


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TmpSanad_TmpHeadS]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TmpSanad] DROP CONSTRAINT FK_TmpSanad_TmpHeadS

Truncate Table TmpHeadS

ALTER TABLE [dbo].[TmpSanad] ADD 
	CONSTRAINT [FK_TmpSanad_TmpHeadS] FOREIGN KEY 
	(
		[Id_Sanad]
	) REFERENCES [dbo].[TmpHeadS] (
		[Id_Sanad]
	) ON DELETE CASCADE 

-----------------------(HeadS و Sanad حذف لينكها و ايجاد مجدد آنها در جداول)----------------------------
IF Exists(Select Name From SysObjects Where Name = 'FloatSanad')
    IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_FloatSanad_Sanad]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
        ALTER TABLE [dbo].[FloatSanad] DROP CONSTRAINT FK_FloatSanad_Sanad

Truncate Table Sanad
Truncate Table FloatSanad
Truncate Table Acc_Cheqe_Link

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Sanad_HeadS]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Sanad] DROP CONSTRAINT FK_Sanad_HeadS

Truncate Table HeadS

ALTER TABLE [dbo].[Sanad] ADD 
	CONSTRAINT [FK_Sanad_HeadS] FOREIGN KEY 
	(
		[Id_Sanad]
	) REFERENCES [dbo].[HeadS] (
		[Id_Sanad]
	) ON DELETE CASCADE 



IF Exists(Select Name From SysObjects Where Name = 'FloatSanad')
Begin
   ALTER TABLE [dbo].[FloatSanad] ADD 
	  CONSTRAINT [FK_FloatSanad_Sanad] FOREIGN KEY 
    	  (
		[Guid_Sanad]
	  ) REFERENCES [dbo].[Sanad] (
		[Guid_Sanad]
	  ) ON DELETE CASCADE 
End


----------(حذف وجوه نقد و همه چكها به غي از وصول نشده - درجريان وصول - پاس نشده و هدرهاي بدون ديتيل)-------------------------------------------------------------
Delete From Cheqe Where Ch_Pass not in('0','3','10') or Ch_Pass_New='20'
Delete From Cheqe Where Ch_No is Null
Delete From HeadC Where not Exists (Select * From Cheqe Where Id_Cheqe = HeadC.Id_Cheqe)
Update HeadC Set Id_Havaleh = 0 , Id_Sanad = 0

IF Exists(select * From dbo.sysColumns where id = object_id('Cheqe') And Name = 'PreGuid_Cheqe')
Begin
  ALTER TABLE [dbo].[Cheqe] DROP Column PreGuid_Cheqe
  ALTER TABLE Cheqe ADD PreGuid_Cheqe uniqueidentifier Null
End

Update HeadC Set Guy_Code = Guy_Bank Where (Guy_Code is Null or Guy_Code = '') and (Guy_Bank is Not null  and Guy_Bank <> '')
-------------------------------------------------------------------------------------------
IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TU_Account]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
  ALTER TABLE Account  DISABLE TRIGGER  TU_Account

Update Account Set Bedeh = 0 , Bestan = 0

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TU_Account]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
  ALTER TABLE Account  ENABLE TRIGGER  TU_Account

-------------------------------------------------------------------------------------------
Truncate Table History_Price
Update Anbar Set Mojodi = 0 , Exit_Fa = 0 , Resive_Fa = 0

------------------حذف اقساطي که قسط آنها تمام شده و صفر کردن شماره فاکتور هاي باقيمانده----------------------------------------------------------------------------------
delete from ghest where bedeh=0 and flagtypeaghsat='0'
Delete From HeadGh  Where T_Bedeh=T_Bestan
update ghest set numberfactor=0  where  numberfactor <> '0'


Declare @IdGhest Bigint
Declare @iCount Int
Declare Cursor_Delete Cursor Local For
Select Id_Ghest From HeadGh 
Open Cursor_Delete
Fetch Next From Cursor_Delete Into @IdGhest
While @@Fetch_Status = 0 
Begin
  Delete From HeadGh Where Id_Ghest = (Select Id_Ghest  From (
                                       Select Count(*) ICount, ghest.Id_Ghest ,
                                       case 
					when Ghest.FlagTypeaghsat = '1' then (Select Count(FlagTypeAghsat) From Ghest Where Id_Ghest = @IdGhest           And FlagTypeAghsat = '1')
					end as CountTypeFlag
					From Headgh Inner join Ghest on Ghest.Id_Ghest = HeadGh.Id_Ghest
					Where Ghest.Id_Ghest = @IdGhest
					Group By Ghest.Id_Ghest, Ghest.FlagTypeAghsat)as table1
					Group By Id_Ghest
					Having Sum(iCount) = Sum(Isnull(CountTypeFlag,0)))
  Fetch Next From Cursor_Delete Into @IdGhest
end
 Close Cursor_Delete
 Deallocate Cursor_Delete


-------------------------------------------(Amval va Daraei)--------------------------------------
Update Capital Set Es_Cash = IsNull(Es_Cash,0) + IsNull(Es_Current_Year,0)
Update Capital Set CurPrice = IsNull(CurPrice,0) - IsNull(Es_Current_Year,0)
Update Capital set Es_Current_Year = 0
-------------------------------------------(Amval va Daraei)--------------------------------------


IF Exists(Select Name From SysObjects Where Name in('DrugHavaleh', 'DrugH'))
Begin
-------------------------------------------(DrugStore)---------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TmpDrugHavaleh_TmpDrugH]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TmpDrugHavaleh] DROP CONSTRAINT FK_TmpDrugHavaleh_TmpDrugH

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tmp_Over_Under_Factor_Drug_TmpDrugH]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tmp_Over_Under_Factor_Drug] DROP CONSTRAINT FK_Tmp_Over_Under_Factor_Drug_TmpDrugH

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TmpDrugH_Sahmiyeh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TmpDrugH] DROP CONSTRAINT FK_TmpDrugH_Sahmiyeh

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_History_TmpDrugHavaleh_History_TmpDrugH]') AND parent_object_id = OBJECT_ID(N'[dbo].[History_TmpDrugHavaleh]'))
ALTER TABLE [dbo].[History_TmpDrugHavaleh] DROP CONSTRAINT [FK_History_TmpDrugHavaleh_History_TmpDrugH]

Truncate Table TmpDrugH
Truncate Table TmpDrugHavaleh
Truncate Table Tmp_Over_Under_Factor_Drug
Truncate Table History_Delete_DrugH
Truncate Table History_Delete_DrugHavaleh
Truncate Table History_TmpDrugh
Truncate Table History_TmpDrugHavaleh
Truncate Table Stock


ALTER TABLE [dbo].[TmpDrugH] ADD 
	CONSTRAINT [FK_TmpDrugH_Sahmiyeh] FOREIGN KEY 
	(
		[Sazman_Code]
	) REFERENCES [dbo].[Sahmiyeh] (
		[Sazman_Code]
	) ON UPDATE CASCADE 
	

ALTER TABLE [dbo].[TmpDrugHavaleh] ADD 
	CONSTRAINT [FK_TmpDrugHavaleh_TmpDrugH] FOREIGN KEY 
	(
		[Id_Havaleh]
	) REFERENCES [dbo].[TmpDrugH] (
		[Id_Havaleh]
	) ON DELETE CASCADE 
	

	
ALTER TABLE [dbo].[Tmp_Over_Under_Factor_Drug] ADD 
	CONSTRAINT [FK_Tmp_Over_Under_Factor_Drug_TmpDrugH] FOREIGN KEY 
	(
		[Id_Havaleh]
	) REFERENCES [dbo].[TmpDrugH] (
		[Id_Havaleh]
	) ON DELETE CASCADE 	 


ALTER TABLE [dbo].[History_TmpDrugHavaleh] ADD 
	CONSTRAINT [FK_History_TmpDrugHavaleh_History_TmpDrugH] FOREIGN KEY 
	(
		[Id_Havaleh]
	) REFERENCES [dbo].[History_TmpDrugH] (
		[Id_Havaleh]
	) ON UPDATE CASCADE 


IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Over_Under_Factor_Drug_DrugH]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Over_Under_Factor_Drug] DROP CONSTRAINT FK_Over_Under_Factor_Drug_DrugH

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_DrugHavaleh_Drugh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DrugHavaleh] DROP CONSTRAINT FK_DrugHavaleh_Drugh


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_DrugH_Doctor]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DrugH] DROP CONSTRAINT FK_DrugH_Doctor

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_DrugH_Sahmiyeh]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DrugH] DROP CONSTRAINT FK_DrugH_Sahmiyeh

Truncate Table DrugH
Truncate Table DrugHavaleh
Truncate Table Over_Under_Factor_Drug
Truncate Table Last_Dumy

ALTER TABLE [dbo].[DrugH] ADD 
	CONSTRAINT [FK_DrugH_Doctor] FOREIGN KEY 
	(
		[Nezam_No]
	) REFERENCES [dbo].[Doctor] (
		[Nezam_No]
	) ON UPDATE CASCADE ,
	CONSTRAINT [FK_DrugH_Sahmiyeh] FOREIGN KEY 
	(
		[Sazman_Code]
	) REFERENCES [dbo].[Sahmiyeh] (
		[Sazman_Code]
	) ON UPDATE CASCADE 
	
ALTER TABLE [dbo].[DrugHavaleh] ADD 
	CONSTRAINT [FK_DrugHavaleh_Drugh] FOREIGN KEY 
	(
		[Id_Havaleh]
	) REFERENCES [dbo].[DrugH] (
		[Id_Havaleh]
	)
	
ALTER TABLE [dbo].[Over_Under_Factor_Drug] ADD 
	CONSTRAINT [FK_Over_Under_Factor_Drug_DrugH] FOREIGN KEY 
	(
		[Id_Havaleh]
	) REFERENCES [dbo].[DrugH] (
		[Id_Havaleh]
	) ON DELETE CASCADE 
	

if exists (select * from sysobjects where name='tblekhtetam')
  truncate table tblekhtetam	
-------------------------------------------(DrugStore)---------------------------------------------------------
End
