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
TRUNCATE TABLE Dbo.UserHistory
TRUNCATE TABLE Dbo.WarehouseHistory
TRUNCATE TABLE dbo.TrackingPrescription
TRUNCATE TABLE dbo.TblQueue

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

-------------------------------------------(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
  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 = '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 = 'FK_TaminPrescriptionDetail_TaminPrescription'))
    ALTER TABLE dbo.TaminPrescriptionDetail DROP CONSTRAINT FK_TaminPrescriptionDetail_TaminPrescription;
 
  IF (EXISTS(SELECT name FROM sysobjects WHERE name = 'FK_TaminSavedPrescriptionDetail_TaminSavedPrescription'))
    ALTER TABLE dbo.TaminSavedPrescriptionDetail DROP CONSTRAINT FK_TaminSavedPrescriptionDetail_TaminSavedPrescription;

  TRUNCATE TABLE dbo.IHIOPrescription
  TRUNCATE TABLE dbo.IHIOPrescriptionDetail
  TRUNCATE TABLE dbo.IHIOActivityHistory
  TRUNCATE TABLE TrackingHeader
  TRUNCATE TABLE TrackingDetail
  TRUNCATE TABLE TrackingPrescription
  TRUNCATE TABLE WarehouseHistory
  TRUNCATE TABLE TaminPrescription
  TRUNCATE TABLE TaminPrescriptionDetail
  TRUNCATE TABLE TaminSavedPrescription
  TRUNCATE TABLE TaminSavedPrescriptionDetail
  
  ALTER TABLE dbo.IHIOPrescriptionDetail ADD CONSTRAINT FK_IHIOPrescriptionDetail_IHIOPrescription
    FOREIGN KEY (PrescriptionId) REFERENCES dbo.IHIOPrescription (Id) ON DELETE CASCADE;

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

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