
Create PROCEDURE  [dbo].[Delete_Mandeh_Data]  AS

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

EXEC dbo.TruncateTableAndDropCreateRelations
TRUNCATE TABLE Karkerd
TRUNCATE TABLE Stock
TRUNCATE TABLE TmpAcc_Cheqe_Link
TRUNCATE TABLE Acc_Cheqe_Link
TRUNCATE TABLE dbo.Tbl_RandomLottery
TRUNCATE TABLE Dbo.History_Delete_HeadGh
TRUNCATE TABLE Dbo.History_Delete_Ghest
TRUNCATE TABLE Dbo.History_Delete_Sanad
TRUNCATE TABLE Dbo.History_Detail_Factors

IF Exists(Select Name From SysObjects Where Name = 'UserHistory')
TRUNCATE TABLE Dbo.UserHistory
IF Exists(Select Name From SysObjects Where Name = 'WarehouseHistory')
TRUNCATE TABLE Dbo.WarehouseHistory
TRUNCATE TABLE dbo.TblQueue

IF Exists(Select Name From SysObjects Where Name = 'Tmp_Over_Under_Factor_Cash')
TRUNCATE TABLE Dbo.Tmp_Over_Under_Factor_Cash

IF Exists(Select Name From SysObjects Where Name = 'Tmp_Over_Under_Factor_Drug')
TRUNCATE TABLE Dbo.Tmp_Over_Under_Factor_Drug

IF Exists(Select Name From SysObjects Where Name = 'Tmp_Over_Under_Row_Drug')
TRUNCATE TABLE Dbo.Tmp_Over_Under_Row_Drug

IF Exists(Select Name From SysObjects Where Name = 'EShopTablesChanges')
TRUNCATE TABLE Dbo.EShopTablesChanges


--*************************************************************************************
Delete FROM ClubCreditSetting WHERE (IsActive=0) OR (EndDate < GETDATE())
DELETE FROM ClubDetailScore WHERE  (ISNULL(PriceUse,0) > 0) OR ( EndDate < GETDATE())
DELETE FROM dbo.Tbl_ScoreClub WHERE Autoid NOT IN(SELECT ScoreId FROM dbo.ClubDetailScore)
--EXEC dbo.CorrectExpirationStock 
----------(حذف وجوه نقد و همه چكها به غي از وصول نشده - درجريان وصول - پاس نشده و هدرهاي بدون ديتيل)-------------------------------------------------------------
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)--------------------------------------

-------------------------------------------(DrugStore)--------------------------------------------------
IF Exists(Select Name From SysObjects Where Name = 'Last_Dumy')
  TRUNCATE TABLE Last_Dumy
IF Exists(Select Name From SysObjects Where Name = 'tblekhtetam')
  TRUNCATE TABLE tblekhtetam	
IF Exists(Select Name From SysObjects Where Name = 'IRI_Prescriptions')
  TRUNCATE TABLE IRI_Prescriptions
IF Exists(Select Name From SysObjects Where Name = 'IRI_InsuredContracts')
  TRUNCATE TABLE IRI_InsuredContracts
IF Exists(Select Name From SysObjects Where Name = 'HIO_Prescriptions')
  Truncate Table HIO_Prescriptions
IF Exists(Select Name From SysObjects Where Name = 'HIX_Prescriptions')
  Truncate Table HIX_Prescriptions
IF Exists(Select Name From SysObjects Where Name = 'History_Delete_DrugH')
  TRUNCATE Table dbo.History_Delete_DrugH
IF Exists(Select Name From SysObjects Where Name = 'History_Delete_DrugHavaleh')
  TRUNCATE Table dbo.History_Delete_DrugHavaleh
IF Exists(Select Name From SysObjects Where Name = 'History_TmpDrugH')
BEGIN 
  IF (EXISTS(SELECT name FROM sysobjects WHERE name = 'FK_History_TmpDrugHavaleh_History_TmpDrugH'))
    ALTER TABLE dbo.History_TmpDrugHavaleh DROP CONSTRAINT FK_History_TmpDrugHavaleh_History_TmpDrugH;
  TRUNCATE Table dbo.History_TmpDrugH
END 
IF Exists(Select Name From SysObjects Where Name = 'History_TmpDrugHavaleh')
BEGIN 
  TRUNCATE Table dbo.History_TmpDrugHavaleh
  ALTER TABLE dbo.History_TmpDrugHavaleh ADD CONSTRAINT FK_History_TmpDrugHavaleh_History_TmpDrugH
	FOREIGN KEY (Id_Havaleh) REFERENCES dbo.History_TmpDrugH (Id_Havaleh) ON DELETE CASCADE;
END 
--------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'TrackingHeader')
    TRUNCATE TABLE TrackingHeader;
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'TrackingDetail')
    TRUNCATE TABLE TrackingDetail;
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'TrackingPrescription')
    TRUNCATE TABLE TrackingPrescription;
----------------------------------------------------------------------------------------------------------------- IHIO 
IF Exists(Select Name From SysObjects Where Name = 'IHIOPrescription')
BEGIN
IF (EXISTS( SELECT name FROM sysobjects WHERE name = 'FK_IHIOPrescriptionDetail_IHIOPrescription') )
    ALTER TABLE dbo.IHIOPrescriptionDetail DROP CONSTRAINT FK_IHIOPrescriptionDetail_IHIOPrescription;
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'IHIOPrescription')
    TRUNCATE TABLE dbo.IHIOPrescription;
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'IHIOPrescriptionDetail')
    TRUNCATE TABLE dbo.IHIOPrescriptionDetail;
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'IHIOActivityHistory')
    TRUNCATE TABLE dbo.IHIOActivityHistory;
ALTER TABLE dbo.IHIOPrescriptionDetail
ADD CONSTRAINT FK_IHIOPrescriptionDetail_IHIOPrescription
    FOREIGN KEY (PrescriptionId)
    REFERENCES dbo.IHIOPrescription (Id) ON DELETE CASCADE;
END;
----------------------------------------------------------------------------------------------------------------- IHIO
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'FK_IHIOSavedPresciptionDetail_IHIOSavedPrescription' )
BEGIN 
    ALTER TABLE dbo.IHIOSavedPrescriptionDetail DROP CONSTRAINT FK_IHIOSavedPresciptionDetail_IHIOSavedPrescription;
	TRUNCATE TABLE dbo.IHIOSavedPrescription
    TRUNCATE TABLE dbo.IHIOSavedPrescriptionDetail
	
	ALTER TABLE dbo.IHIOSavedPrescriptionDetail
	ADD CONSTRAINT FK_IHIOSavedPresciptionDetail_IHIOSavedPrescription
    FOREIGN KEY (PrescriptionId)
    REFERENCES dbo.IHIOSavedPrescription (Id) ON DELETE CASCADE;
END;
---------------------------------------------------------------------------------------------------------------------- EPM
IF Exists(Select Name From SysObjects Where Name = 'EPMDrugCodeError')
	TRUNCATE TABLE dbo.EPMDrugCodeError;

---------------------------------------------------------------------------------------------------------------------- TAMIN

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'FK_TaminUIDInfo_TaminPrescriptionDetail')
BEGIN  
	ALTER TABLE dbo.TaminPrescriptionDetailUID DROP CONSTRAINT FK_TaminUIDInfo_TaminPrescriptionDetail;
	TRUNCATE TABLE dbo.TaminPrescriptionDetailUID  
END

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'FK_TaminSavedPrescriptionDetail_TaminSavedPrescription')
BEGIN 

ALTER TABLE dbo.TaminSavedPrescriptionDetail DROP CONSTRAINT FK_TaminSavedPrescriptionDetail_TaminSavedPrescription;
	TRUNCATE TABLE dbo.TaminSavedPrescription
	TRUNCATE TABLE dbo.TaminSavedPrescriptionDetail  

	ALTER TABLE dbo.TaminSavedPrescriptionDetail
	ADD CONSTRAINT FK_TaminSavedPrescriptionDetail_TaminSavedPrescription
    FOREIGN KEY (PrescriptionId)
    REFERENCES dbo.TaminSavedPrescription (Id) ON DELETE CASCADE;
END;

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'FK_TaminPrescriptionDetail_TaminPrescription')
BEGIN     
	ALTER TABLE dbo.TaminPrescriptionDetail DROP CONSTRAINT FK_TaminPrescriptionDetail_TaminPrescription;
	TRUNCATE TABLE dbo.TaminPrescription
	TRUNCATE TABLE dbo.TaminPrescriptionDetail  

	ALTER TABLE dbo.TaminPrescriptionDetail
	ADD CONSTRAINT FK_TaminPrescriptionDetail_TaminPrescription
    FOREIGN KEY (PrescriptionId)
    REFERENCES dbo.TaminPrescription (Id) ON DELETE CASCADE;
END;

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'FK_TaminSavedPrescriptionDetail_TaminSavedPrescription' )
BEGIN 
    ALTER TABLE dbo.TaminSavedPrescriptionDetail DROP CONSTRAINT FK_TaminSavedPrescriptionDetail_TaminSavedPrescription;
	TRUNCATE TABLE dbo.TaminSavedPrescription
    TRUNCATE TABLE dbo.TaminSavedPrescriptionDetail
	
	ALTER TABLE dbo.TaminSavedPrescriptionDetail
	ADD CONSTRAINT FK_TaminSavedPrescriptionDetail_TaminSavedPrescription
    FOREIGN KEY (PrescriptionId)
    REFERENCES dbo.TaminSavedPrescription (Id) ON DELETE CASCADE;
END;

IF Exists(Select Name From SysObjects Where Name = 'TaminPrescriptionDetailUID')
BEGIN
    ALTER TABLE dbo.TaminPrescriptionDetailUID
	ADD CONSTRAINT FK_TaminUIDInfo_TaminPrescriptionDetail
    FOREIGN KEY (PrescriptionDetailId)
    REFERENCES dbo.TaminPrescriptionDetail (Id) ON DELETE CASCADE;
END 

-----------------------------------------------------------------------------------------------------------------
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'TblUMDrugHavaleh')
  TRUNCATE TABLE TblUMDrugHavaleh
--------------------------------------------------------------------------------------------------------
IF Exists(Select Name From SysObjects Where Name = 'UserShiftWork')
DELETE FROM UserShiftWork

