use sales1390
/****** Object:  Table [dbo].[unitkala]    Script Date: 05/20/2023 19:53:34 ******/

/****** Object:  Table [dbo].[unitkala]    Script Date: 05/20/2023 19:53:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
alter table company add updatekala bit
go
alter table company add lastprice bit
go
update company set lastprice=1,updatekala=1
go

alter table company add clientid nvarchar(10)
go
alter table company add privatekey nvarchar(max)
go
alter table kala add irancodekala nvarchar(50)
go
alter table hfactor add moadianuid nvarchar(50)
go
alter table company add moadianapi nvarchar(200)
go
alter table customer  add branchcode nvarchar(20)
go
alter table review  add serialcheck int
go



CREATE TABLE [dbo].[unitkala](
	[Id] [int] NOT NULL,
	[Description] [nvarchar](50) NULL,
 CONSTRAINT [PK_unitkala] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


/****** Object:  View [dbo].[Moadian]    Script Date: 05/23/2023 20:10:44 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Moadian]'))
DROP VIEW [dbo].[Moadian]
GO



/****** Object:  View [dbo].[Moadian]    Script Date: 06/24/2023 20:37:59 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Moadian]'))
DROP VIEW [dbo].[Moadian]
GO



/****** Object:  View [dbo].[Moadian]    Script Date: 06/24/2023 20:38:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[Moadian]
AS
SELECT     dbo.hfactor.ftime, dbo.hfactor.fdate AS Indatim, dbo.hfactor.factorcode, dbo.dfactor.sum AS Tsstam, dbo.dfactor.rebate AS Dis, dbo.dfactor.tax AS Vam, dbo.kala.tax AS Vra, dbo.dfactor.number AS Am, 
                      dbo.kala.irancodekala AS Sstid, dbo.kala.kalaname AS Sstt, dbo.dfactor.fe AS Fee, dbo.customer.codemeli AS Bid, dbo.company.codemeli AS Tins, dbo.company.privatekey, dbo.company.clientid, 
                      dbo.kala.unit1 AS Mu, dbo.hfactor.cash, dbo.hfactor.factortype, dbo.hfactor.moadianuid, dbo.customer.codeposti AS Bpc, dbo.customer.codeeghtesadi AS Tinb, dbo.customer.branchcode AS bbc
FROM         dbo.dfactor INNER JOIN
                      dbo.hfactor ON dbo.dfactor.factorcode = dbo.hfactor.factorcode INNER JOIN
                      dbo.kala ON dbo.dfactor.codekala = dbo.kala.kalacode INNER JOIN
                      dbo.customer ON dbo.hfactor.customercode = dbo.customer.customercode CROSS JOIN
                      dbo.company

GO
/****** Object:  View [dbo].[factorfail]    Script Date: 11/05/2023 11:47:05 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[factorfail]'))
DROP VIEW [dbo].[factorfail]
GO



/****** Object:  View [dbo].[factorfail]    Script Date: 11/05/2023 11:47:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[factorfail]
AS
SELECT     dbo.sanad.serial, dbo.sanad.codesanad, dbo.sanad.codemoen, dbo.sanad.codecustomer, dbo.sanad.sdate, dbo.sanad.sharh, dbo.sanad.bed, dbo.sanad.best, dbo.sanad.serialfactor, 
                      dbo.sanad.serialcheck, dbo.sanad.serialmoen, dbo.sanad.year, dbo.sanad.usercode, dbo.sanad.savesanad, dbo.sanad.sandogh
FROM         dbo.sanad INNER JOIN
                      dbo.hfactor ON dbo.sanad.serialfactor = dbo.hfactor.factorcode AND dbo.sanad.best > 0 AND dbo.hfactor.factortype = 'فروش' AND dbo.sanad.codecustomer > 0 AND dbo.sanad.sharh LIKE '%خرید%'

GO
INSERT [dbo].[usergroup] ([groupname], [optionallow]) VALUES ( N'مديرفروش', N'جستجوی  کالا')
go
INSERT [dbo].[menuoption] (menuid,[menusharh]) VALUES (158, N'جستجوی  کالا')
go
alter table review add customercode int
go
/****** Object:  StoredProcedure [dbo].[reviewcheck]    Script Date: 11/20/2023 10:55:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reviewcheck]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[reviewcheck]
GO

/****** Object:  StoredProcedure [dbo].[reviewcheck]    Script Date: 11/20/2023 10:55:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[reviewcheck]
AS INSERT INTO dbo.review
                (sharh, state, reviewdate, serial, [check], customercode, serialcheck)
SELECT 'چک شماره[' + RTRIM(dbo.[check].checkcode) + '] به تاريخ[' + SUBSTRING(STR(dbo.[check].checkdate, 8), 1, 4) + '/' + SUBSTRING(STR(dbo.[check].checkdate, 8), 5, 2) 
                + '/' + SUBSTRING(STR(dbo.[check].checkdate, 8), 7, 2) + ']به مبلغ[' + STR(dbo.[check].mablag) + '] به نام ' + RTRIM(dbo.customer.customername) + '   ' + RTRIM(dbo.[check].bankname) AS sharh, 
                CASE dbo.[CHECK].type WHEN 'دريافتني' THEN 'دريافتني پاس نشده' ELSE 'پرداختني پاس نشده' END AS state, dbo.[check].checkdate, dbo.[check].serial + ROUND(RAND(DATEPART(mm, GETDATE()) 
                * 100000 + DATEPART(ss, GETDATE()) * 1000 + DATEPART(ms, GETDATE())) * 1000, 0) AS Expr1, SIGN(1) AS Expr2, dbo.[check].customercode, dbo.[check].serial
FROM   dbo.[check] INNER JOIN
                dbo.customer ON dbo.[check].customercode = dbo.customer.customercode
WHERE (dbo.[check].serial IN
                    (SELECT serial
                     FROM    checknotpass))

GO
/****** Object:  UserDefinedFunction [dbo].[lastfactorbuy1]    Script Date: 11/29/2023 08:10:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[lastfactorbuy1]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[lastfactorbuy1]
GO


/****** Object:  UserDefinedFunction [dbo].[lastfactorbuy1]    Script Date: 11/29/2023 08:10:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[lastfactorbuy1](@todate int = 99999999)
RETURNS TABLE
AS
RETURN ( select dfactor.codekala,dfactor.fe from dfactor inner join hfactor on hfactor.factorcode=dfactor.factorcode,
                (select dfactor.codekala ,max(fdate) as transaction_date,max(serial) as serial
                     from dfactor inner join hfactor on hfactor.factorcode=dfactor.factorcode
                     group by dfactor.codekala,hfactor.factortype having hfactor.factortype='خرید') max_user
                  where dfactor.serial=max_user.serial   
                  and hfactor.fdate=max_user.transaction_date and hfactor.fdate<=@todate and dfactor.codekala is not null                                
  )


GO
/****** Object:  Table [dbo].[barcode]    Script Date: 12/11/2023 17:38:22 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[barcode]') AND type in (N'U'))
DROP TABLE [dbo].[barcode]
GO

/****** Object:  Table [dbo].[barcode]    Script Date: 12/11/2023 17:38:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[barcode](
	[kalacode] [int] NOT NULL,
	[barcodekala] [nvarchar](50) NULL,
	[kalaname] [nvarchar](100) NULL,
	[sales] [real] NULL,
	[rebate] [real] NULL,
	[salesrebate]  AS ([sales]-([sales]*[rebate])/(100))
) ON [PRIMARY]

GO
/****** Object:  StoredProcedure [dbo].[sbarcode]    Script Date: 12/11/2023 17:42:08 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sbarcode]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sbarcode]
GO

/****** Object:  StoredProcedure [dbo].[sbarcode]    Script Date: 12/11/2023 17:42:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sbarcode]
AS INSERT INTO dbo.barcode
                      (kalacode, barcodekala, kalaname, sales,rebate)
SELECT     kalacode, CASE WHEN (barcodekala IS NULL) THEN ltrim(rtrim(str(kalacode))) ELSE barcodekala END AS barcodekala, kalaname, 
                      ROUND(sales + sales * tax / 100, - 2) AS sales,rebate
FROM         dbo.kala
ORDER BY kalacode

GO
alter table customer  ALTER COLUMN codemeli nvarchar(20)

go
alter table customer  ALTER COLUMN codeeghtesadi nvarchar(20)
go


/****** Object:  StoredProcedure [dbo].[CopyRecordBySalesCode]    Script Date: 12/30/2023 09:46:07 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CopyRecordBySalesCode]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CopyRecordBySalesCode]
GO



/****** Object:  StoredProcedure [dbo].[CopyRecordBySalesCode]    Script Date: 12/30/2023 09:46:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CopyRecordBySalesCode]
    @InputSalesCode INT
AS
BEGIN
    SET NOCOUNT ON;

    -- برای نگهداری مقادیر بالاترین factorcode و salescode
    DECLARE @MaxFactorCode INT, @MaxSalesCode INT;

    -- بدست آوردن بالاترین مقدار factorcode و salescode برای مقادیر جدید
    SELECT @MaxFactorCode = MAX(factorcode) FROM hfactor;
    SELECT @MaxSalesCode = MAX(salescode) FROM hfactor;

    -- اطمینان از اینکه مقادیر NULL نیستند و افزودن 1 برای ایجاد مقادیر جدید
    SET @MaxFactorCode = ISNULL(@MaxFactorCode, 0) + 1;
    SET @MaxSalesCode = ISNULL(@MaxSalesCode, 0) + 1;

    -- ایجاد کپی از رکوردی از hfactor که salescode با مقدار ورودی مطابقت دارد
    INSERT INTO hfactor (factorcode, manualcode, fdate, ftime, customercode, cash, factortype, 
                         comment, codecompany, codeuser, spacialsales, salescode, buycode, bsalescode, bbuycode, 
                         year, sarresed, statepay, comment1, psalescode, codevisitor, moadianuid)
    SELECT @MaxFactorCode, manualcode, fdate, ftime, customercode, cash, factortype, 
           comment, codecompany, codeuser, spacialsales, @MaxSalesCode, buycode, bsalescode, bbuycode, 
           year, sarresed, statepay, comment1, psalescode, codevisitor, moadianuid
    FROM hfactor
    WHERE salescode = @InputSalesCode;

    -- کپی کردن رکوردهای مرتبط از جدول dfactor با استفاده از factorcode مرتبط با salescode ورودی
    INSERT INTO dfactor (factorcode, codemoen, codekala, sharh, number, fe, rebate, tax, 
                         rebatepercent, number1)
    SELECT @MaxFactorCode, codemoen, codekala, sharh, 0, fe, rebate, tax, 
           rebatepercent, 0
    FROM dfactor
    WHERE factorcode = (SELECT factorcode FROM hfactor WHERE salescode = @InputSalesCode);

    SET NOCOUNT OFF;
END
 
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[reviewcheck]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[reviewcheck]
GO


/****** Object:  StoredProcedure [dbo].[reviewcheck]    Script Date: 05/05/2024 11:47:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[reviewcheck]
AS INSERT INTO dbo.review
                (sharh, state, reviewdate, serial, [check], serialcheck)
SELECT 'چک شماره[' + RTRIM(dbo.[check].checkcode) + '] به تاريخ[' + SUBSTRING(STR(dbo.[check].checkdate, 8), 1, 4) + '/' + SUBSTRING(STR(dbo.[check].checkdate, 8), 5, 2) 
                + '/' + SUBSTRING(STR(dbo.[check].checkdate, 8), 7, 2) + ']به مبلغ[' + STR(dbo.[check].mablag) + '] به نام ' + RTRIM(dbo.customer.customername) + '   ' + RTRIM(dbo.[check].bankname) AS sharh, 
                CASE dbo.[CHECK].type WHEN 'دريافتني' THEN 'دريافتني پاس نشده' ELSE 'پرداختني پاس نشده' END AS state, dbo.[check].checkdate, dbo.[check].serial + ROUND(RAND(DATEPART(mm, GETDATE()) 
                * 100000 + DATEPART(ss, GETDATE()) * 1000 + DATEPART(ms, GETDATE())) * 1000, 0) AS Expr1, SIGN(1) AS Expr2, dbo.[check].serial
FROM   dbo.[check] INNER JOIN
                dbo.customer ON dbo.[check].customercode = dbo.customer.customercode
WHERE (dbo.[check].serial IN
                    (SELECT serial
                     FROM    checknotpass))
GO


GO

/****** Object:  StoredProcedure [dbo].[sortsanad]    Script Date: 07/24/2024 10:46:08 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sortsanad]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sortsanad]
GO



/****** Object:  StoredProcedure [dbo].[sortsanad]    Script Date: 07/24/2024 10:46:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[sortsanad]
AS 
WITH SortedRecords AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY sdate) AS ncodesanad,
        sdate,
        [year]
    FROM 
        sanad group by sdate,year
    having [year] = (SELECT top(1) year FROM [year] where state=1 )  -- فقط رکوردهایی که سال آن‌ها برابر بیشترین سال است
)
UPDATE sanad
SET codesanad = ncodesanad
FROM SortedRecords
WHERE sanad.sdate = SortedRecords.sdate
AND sanad.[year] = SortedRecords.[year];  -- مطابقت سال در جدول اصلی و رکوردهای مرتب شده
GO

GO
/****** Object:  StoredProcedure [dbo].[CopyRecordByCodeSanad]    Script Date: 09/02/2024 12:00:56 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CopyRecordByCodeSanad]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CopyRecordByCodeSanad]
GO


/****** Object:  StoredProcedure [dbo].[CopyRecordByCodeSanad]    Script Date: 09/02/2024 12:00:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CopyRecordByCodeSanad]
    @InputCodeSanad INT, @InputSdate INT
AS
BEGIN
    SET NOCOUNT ON;

    -- برای نگهداری مقادیر بالاترین CodeSanad
    DECLARE @MaxCodeSanad INT;

    -- بدست آوردن بالاترین مقدار  CodeSanad برای مقادیر جدید
    SELECT @MaxCodeSanad = MAX(CodeSanad) FROM sanad;
   

    -- اطمینان از اینکه مقادیر NULL نیستند و افزودن 1 برای ایجاد مقادیر جدید
    SET @MaxCodeSanad = ISNULL(@MaxCodeSanad, 0) + 1;
   
    -- ایجاد کپی از رکوردی از hfactor که salescode با مقدار ورودی مطابقت دارد
    INSERT INTO sanad ([codesanad],[codemoen],[codecustomer],[sdate],[sharh],[bed],[best],[year],[usercode],[savesanad],[sandogh])
    SELECT @MaxCodeSanad,[codemoen],[codecustomer],[sdate],[sharh],[bed],[best],[year],[usercode],[savesanad],[sandogh]
    FROM sanad
    WHERE [codesanad] = @InputCodeSanad and [sdate]=@InputSdate;

   

    SET NOCOUNT OFF;
END
GO
/****** Object:  View [dbo].[vsanad]    Script Date: 12/25/2024 21:32:21 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vsanad]'))
DROP VIEW [dbo].[vsanad]
GO


/****** Object:  View [dbo].[vsanad]    Script Date: 12/25/2024 21:32:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vsanad]
AS
SELECT     TOP (100) PERCENT s1.codesanad, s1.sdate, CASE WHEN s1.codemoen > 0 THEN ISNULL(m1.description, 'Unknown') WHEN s1.codecustomer > 0 THEN ISNULL(c1.customername, 'Unknown') 
                      ELSE 'Unknown' END AS description, s1.sharh, s1.bed, s1.best, s1.codemoen, s1.codecustomer, s1.serial, s1.serialfactor, s1.serialcheck, s1.serialmoen, s1.year, 0 AS RunningTotal
FROM         dbo.sanad AS s1 LEFT OUTER JOIN
                      dbo.moen AS m1 ON s1.codemoen = m1.codemoen LEFT OUTER JOIN
                      dbo.customer AS c1 ON s1.codecustomer = c1.customercode
ORDER BY s1.serial


GO
/****** Object:  Table [dbo].[temp_filtered_records]    Script Date: 12/28/2024 20:03:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_filtered_records]') AND type in (N'U'))
DROP TABLE [dbo].[temp_filtered_records]
GO

/****** Object:  Table [dbo].[temp_filtered_records]    Script Date: 12/28/2024 20:03:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[temp_filtered_records](
	[codesanad] [int] NULL,
	[sdate] [int] NULL,
	[description] [nvarchar](255) NULL,
	[sharh] [nvarchar](255) NULL,
	[bed] [numeric](19, 2) NULL,
	[best] [numeric](19, 2) NULL,
	[codemoen] [int] NULL,
	[codecustomer] [int] NULL,
	[serial] [int] NULL,
	[serialfactor] [int] NULL,
	[serialcheck] [int] NULL,
	[serialmoen] [int] NULL,
	[year] [int] NULL,
	[RunningTotal] [numeric](19, 2) NULL
) ON [PRIMARY]

GO


ALTER FUNCTION [dbo].[tarazemoencustomer1](@fromdate int = 0,
@todate int = 99999999)
RETURNS TABLE
AS
RETURN ( SELECT     dbo.sanad.codecustomer, dbo.customer.customername, SUM(dbo.sanad.bed) AS sumbed, SUM(dbo.sanad.best) AS sumbest, dbo.sanad.year, 
                      CASE WHEN SUM(dbo.sanad.bed) - SUM(dbo.sanad.best) > 0 THEN SUM(dbo.sanad.bed) - SUM(dbo.sanad.best) ELSE 0 END AS rbed, 
                      CASE WHEN SUM(dbo.sanad.best) - SUM(dbo.sanad.bed) >= 0 THEN SUM(dbo.sanad.best) - SUM(dbo.sanad.bed) ELSE 0 END AS rbest, 
                      dbo.customergroup.customergroup, dbo.company.nameco, dbo.customer.customermobil, dbo.customer.customertel,CONVERT(varchar(10), MAX(dbo.sanad.sdate), 1379) as LastDebtDate
FROM         dbo.sanad INNER JOIN
                      dbo.customer ON dbo.sanad.codecustomer = dbo.customer.customercode INNER JOIN
                      dbo.customergroup ON dbo.customer.customergroup = dbo.customergroup.customergroupcode CROSS JOIN
                      dbo.company
WHERE     (dbo.sanad.sdate BETWEEN @fromdate AND @todate)
GROUP BY dbo.sanad.codecustomer, dbo.sanad.year, dbo.customer.customername, dbo.customergroup.customergroup, dbo.company.nameco, 
                      dbo.customer.customermobil, dbo.customer.customertel )
go
alter table company add ZoomValue int
go
update company set ZoomValue=100
go


ALTER TABLE company ADD template INT NOT NULL DEFAULT 0
GO
/****** Object:  Table [dbo].[importcustomer]    Script Date: 02/03/2025 19:28:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[importcustomer]') AND type in (N'U'))
DROP TABLE [dbo].[importcustomer]
GO



/****** Object:  Table [dbo].[importcustomer]    Script Date: 02/03/2025 19:28:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO


CREATE TABLE [dbo].[importcustomer](
	[customercode] [int] NOT NULL,
	[customername] [varchar](50) NULL,
	[customermobil] [varchar](50) NULL,
	[customertel] [varchar](50) NULL,
	[customeraddress] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[customercode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
alter table company add Panelkala bit Not Null DEFAULT 0 
go
ALTER TABLE sanad ALTER COLUMN sharh NVARCHAR(MAX);
go
/****** Object:  StoredProcedure [dbo].[sbuykala1]    Script Date: 02/28/2025 20:09:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sbuykala1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sbuykala1]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[sbuykala1](@todate int = 99999999)
AS
BEGIN
    SET NOCOUNT ON;

    -- جدول موقت برای ذخیره تمامی تراکنش‌ها
    CREATE TABLE #Transactions (
        RowNum INT IDENTITY(1,1),
        codekala INT,
        serial INT,
        transaction_type VARCHAR(20),
        quantity NUMERIC(18,4),
        unit_price NUMERIC(19,0)
    );

    -- درج تراکنش‌ها
    INSERT INTO #Transactions (codekala, serial, transaction_type, quantity, unit_price)
    SELECT 
        d.codekala,
        d.serial,
        CASE 
            WHEN h.buycode > 0 THEN 'PURCHASE'
            WHEN h.bbuycode > 0 THEN 'PURCHASE_RETURN'
            WHEN h.salescode > 0 THEN 'SALE'
            WHEN h.bsalescode > 0 THEN 'SALE_RETURN'
        END,
        d.number,
        d.fe
    FROM 
        dbo.dfactor d
        INNER JOIN dbo.hfactor h ON d.factorcode = h.factorcode
    WHERE 
        (h.buycode > 0 OR h.bbuycode > 0 OR h.salescode > 0 OR h.bsalescode > 0)
        AND h.fdate <= @todate
    ORDER BY 
        d.serial;

    -- جدول موقت برای وضعیت کالاها (شامل تعداد ورودی و خروجی)
    CREATE TABLE #KalaState (
        kalacode INT PRIMARY KEY,
        current_quantity NUMERIC(38,4),
        current_avg_price NUMERIC(24, 2),
        total_input NUMERIC(38,4) DEFAULT 0,
        total_output NUMERIC(38,4) DEFAULT 0
    );

    -- مقداردهی اولیه با داده‌های موجود
    INSERT INTO #KalaState (kalacode, current_quantity, current_avg_price)
    SELECT 
        kalacode,
        ISNULL(firststock, 0),
        CASE 
            WHEN ISNULL(firststock, 0) = 0 THEN 0.00
            ELSE CAST(firstprice AS NUMERIC(24, 2))
        END
    FROM 
        dbo.kala;

    -- پردازش تراکنش‌ها
    DECLARE 
        @codekala INT,
        @transaction_type VARCHAR(20),
        @quantity NUMERIC(18,4),
        @unit_price NUMERIC(19,0),
        @current_quantity NUMERIC(38,4),
        @current_avg_price NUMERIC(24, 2);

    DECLARE trans_cursor CURSOR FOR
        SELECT codekala, transaction_type, quantity, unit_price
        FROM #Transactions
        ORDER BY RowNum;

    OPEN trans_cursor;
    FETCH NEXT FROM trans_cursor INTO @codekala, @transaction_type, @quantity, @unit_price;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT 
            @current_quantity = current_quantity,
            @current_avg_price = current_avg_price
        FROM 
            #KalaState 
        WHERE 
            kalacode = @codekala;

        -- محاسبه تعداد ورودی و خروجی
        IF @transaction_type IN ('PURCHASE', 'SALE_RETURN')
        BEGIN
            UPDATE #KalaState
            SET 
                total_input = total_input + @quantity
            WHERE 
                kalacode = @codekala;
        END
        ELSE IF @transaction_type IN ('SALE', 'PURCHASE_RETURN')
        BEGIN
            UPDATE #KalaState
            SET 
                total_output = total_output + @quantity
            WHERE 
                kalacode = @codekala;
        END

        -- پردازش موجودی و میانگین قیمت
        IF @transaction_type = 'PURCHASE'
        BEGIN
            DECLARE @new_quantity NUMERIC(38,4) = @current_quantity + @quantity;
            DECLARE @new_avg_price NUMERIC(24, 2) = 
                CASE 
                    WHEN @new_quantity = 0 THEN 0.00
                    ELSE CAST( 
                        ( (@current_quantity * @current_avg_price) + (@quantity * @unit_price) ) / @new_quantity 
                        AS NUMERIC(24, 2) 
                    )
                END;

            UPDATE #KalaState
            SET 
                current_quantity = @new_quantity,
                current_avg_price = @new_avg_price
            WHERE 
                kalacode = @codekala;
        END
        ELSE IF @transaction_type = 'PURCHASE_RETURN'
        BEGIN
            DECLARE @updated_quantity_p NUMERIC(38,4) = @current_quantity - @quantity;
            DECLARE @updated_avg_price_p NUMERIC(24, 2) = 
                CASE 
                    WHEN @updated_quantity_p <= 0 THEN 0.00
                    ELSE @current_avg_price 
                END;

            UPDATE #KalaState
            SET 
                current_quantity = @updated_quantity_p,
                current_avg_price = @updated_avg_price_p
            WHERE 
                kalacode = @codekala;
        END
        ELSE IF @transaction_type IN ('SALE', 'SALE_RETURN')
        BEGIN
            DECLARE @updated_quantity_s NUMERIC(38,4) = 
                CASE 
                    WHEN @transaction_type = 'SALE' THEN @current_quantity - @quantity
                    ELSE @current_quantity + @quantity
                END;

            UPDATE #KalaState
            SET 
                current_quantity = @updated_quantity_s
            WHERE 
                kalacode = @codekala;
        END

        FETCH NEXT FROM trans_cursor INTO @codekala, @transaction_type, @quantity, @unit_price;
    END

    CLOSE trans_cursor;
    DEALLOCATE trans_cursor;

    -- به‌روزرسانی جدول اصلی kala
    UPDATE k
    SET 
        buy = CAST(ks.current_avg_price AS NUMERIC(24, 0)),
        numberinput = ks.total_input,
        numberoutput = ks.total_output
    FROM 
        dbo.kala k
        INNER JOIN #KalaState ks ON k.kalacode = ks.kalacode;

    DROP TABLE #Transactions;
    DROP TABLE #KalaState;
END;
GO
/****** Object:  StoredProcedure [dbo].[sbuykala]    Script Date: 03/23/2025 17:36:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sbuykala]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sbuykala]
GO


/****** Object:  StoredProcedure [dbo].[sbuykala]    Script Date: 03/23/2025 17:36:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sbuykala](@todate int = 99999999)
AS begin
update kala set numberinput=0,numberoutput=0,buy=0;
INSERT INTO          dbo.buykala
                                      (kalacode, buy)
SELECT                     dbo.kala.kalacode, 
                                      CASE WHEN lastfactorbuy1.fe > 0 THEN lastfactorbuy1.fe ELSE (CASE WHEN isnull(dbo.kala.buy,0) > 0 THEN kala.buy ELSE kala.firstprice
                                       END) END AS buykala
FROM                         dbo.lastfactorbuy1(@todate) lastfactorbuy1 RIGHT OUTER JOIN
                                      dbo.kala ON lastfactorbuy1.codekala = dbo.kala.kalacode
ORDER BY              dbo.kala.kalacode
end



GO
/****** Object:  View [dbo].[Moadian]    Script Date: 03/25/2025 18:33:32 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Moadian]'))
DROP VIEW [dbo].[Moadian]
GO


/****** Object:  View [dbo].[Moadian]    Script Date: 03/25/2025 18:33:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[Moadian]
AS
SELECT     dbo.hfactor.ftime, dbo.hfactor.fdate AS Indatim, dbo.hfactor.factorcode, dbo.dfactor.sum AS Tsstam, dbo.dfactor.rebate AS Dis, dbo.dfactor.tax AS Vam, 
                      dbo.kala.tax AS Vra, dbo.dfactor.number AS Am, dbo.kala.irancodekala AS Sstid, dbo.kala.kalaname AS Sstt, dbo.dfactor.fe AS Fee, dbo.customer.codemeli AS Bid, 
                      dbo.company.codeeghtesadi AS Tins, dbo.company.privatekey, dbo.company.clientid, dbo.kala.unit1 AS Mu, dbo.hfactor.cash, dbo.hfactor.factortype, 
                      dbo.hfactor.moadianuid, dbo.customer.codeposti AS Bpc, dbo.customer.codeeghtesadi AS Tinb, dbo.customer.branchcode AS bbc
FROM         dbo.dfactor INNER JOIN
                      dbo.hfactor ON dbo.dfactor.factorcode = dbo.hfactor.factorcode INNER JOIN
                      dbo.kala ON dbo.dfactor.codekala = dbo.kala.kalacode INNER JOIN
                      dbo.customer ON dbo.hfactor.customercode = dbo.customer.customercode CROSS JOIN
                      dbo.company

GO
/****** Object:  View [dbo].[checknotpass]    Script Date: 04/06/2025 12:21:31 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[checknotpass]'))
DROP VIEW [dbo].[checknotpass]
GO


/****** Object:  View [dbo].[checknotpass]    Script Date: 04/06/2025 12:21:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[checknotpass]
AS
SELECT      serial, customercode, accountcode, checkcode, comment, year, adate, state, type, mablag, bankname, checkdate
FROM          dbo.[check]
WHERE      (NOT (serial IN
                            (SELECT      serial
                               FROM          checkpass)))

GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[checkvagozar]'))
DROP VIEW [dbo].[checkvagozar]
GO

CREATE VIEW [dbo].[checkvagozar]
AS
SELECT     dbo.[check].serial
FROM         dbo.sanad INNER JOIN
                      dbo.[check] ON dbo.sanad.serialcheck = dbo.[check].serial AND dbo.sanad.codecustomer <> dbo.[check].customercode AND dbo.sanad.codecustomer > 0

GO

ALTER PROCEDURE [dbo].[reviewcheck]
AS 
INSERT INTO dbo.review
                (sharh, state, reviewdate, serial, [check], serialcheck)
SELECT 'چک شماره[' + RTRIM(dbo.[check].checkcode) + '] به تاريخ[' + SUBSTRING(STR(dbo.[check].checkdate, 8), 1, 4) + '/' + SUBSTRING(STR(dbo.[check].checkdate, 8), 5, 2) 
                + '/' + SUBSTRING(STR(dbo.[check].checkdate, 8), 7, 2) + ']به مبلغ[' + STR(dbo.[check].mablag) + '] به نام ' + RTRIM(dbo.customer.customername) + '   ' + RTRIM(dbo.[check].bankname) AS sharh, 
                CASE dbo.[CHECK].type WHEN 'دريافتني' THEN 'دريافتني پاس نشده' ELSE 'پرداختني پاس نشده' END AS state, dbo.[check].checkdate, dbo.[check].serial + ROUND(RAND(DATEPART(mm, GETDATE()) 
                * 100000 + DATEPART(ss, GETDATE()) * 1000 + DATEPART(ms, GETDATE())) * 1000, 0) AS Expr1, SIGN(1) AS Expr2, dbo.[check].serial
FROM   dbo.[check] INNER JOIN
                dbo.customer ON dbo.[check].customercode = dbo.customer.customercode
WHERE dbo.[check].serial IN (SELECT serial   FROM    checknotpass) and dbo.[check].serial  not in (select serial from checkvagozar)


GO



/****** Object:  View [dbo].[vsanad]    Script Date: 04/16/2025 12:39:46 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vsanad]'))
DROP VIEW [dbo].[vsanad]
GO

CREATE VIEW [dbo].[vsanad]
AS
SELECT     TOP (100) PERCENT s1.codesanad, s1.sdate, CASE WHEN s1.codemoen > 0 THEN ISNULL(m1.description, 'Unknown') WHEN s1.codecustomer > 0 THEN ISNULL(c1.customername, 'Unknown') 
                      ELSE 'Unknown' END AS description, s1.sharh, s1.bed, s1.best, s1.codemoen, s1.codecustomer, s1.serial, s1.serialfactor, s1.serialcheck, s1.serialmoen, s1.year, 0 AS RunningTotal, 
                      s1.savesanad
FROM         dbo.sanad AS s1 LEFT OUTER JOIN
                      dbo.moen AS m1 ON s1.codemoen = m1.codemoen LEFT OUTER JOIN
                      dbo.customer AS c1 ON s1.codecustomer = c1.customercode
ORDER BY s1.serial

GO
/****** Object:  Table [dbo].[temp_filtered_records]    Script Date: 04/16/2025 12:40:36 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_filtered_records]') AND type in (N'U'))
DROP TABLE [dbo].[temp_filtered_records]
GO


CREATE TABLE [dbo].[temp_filtered_records](
	[codesanad] [int] NULL,
	[sdate] [int] NULL,
	[description] [nvarchar](255) NULL,
	[sharh] [nvarchar](255) NULL,
	[bed] [numeric](19, 2) NULL,
	[best] [numeric](19, 2) NULL,
	[codemoen] [int] NULL,
	[codecustomer] [int] NULL,
	[serial] [int] NULL,
	[serialfactor] [int] NULL,
	[serialcheck] [int] NULL,
	[serialmoen] [int] NULL,
	[year] [int] NULL,
	[RunningTotal] [numeric](19, 2) NULL,
	[savesanad] [bit] NULL
) ON [PRIMARY]

GO

/****** Object:  StoredProcedure [dbo].[sbuykala1]    Script Date: 04/29/2025 09:13:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sbuykala1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sbuykala1]
GO


/****** Object:  StoredProcedure [dbo].[sbuykala1]    Script Date: 04/29/2025 09:13:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[sbuykala1](@todate int = 99999999)
AS
BEGIN
    SET NOCOUNT ON;

    -- جدول موقت برای ذخیره تمامی تراکنش‌ها
    CREATE TABLE #Transactions (
        RowNum INT IDENTITY(1,1),
        codekala INT,
        serial INT,
        transaction_type VARCHAR(20),
        quantity NUMERIC(18,4),
        unit_price NUMERIC(19,0)
    );

    -- درج تراکنش‌ها
    INSERT INTO #Transactions (codekala, serial, transaction_type, quantity, unit_price)
    SELECT 
        d.codekala,
        d.serial,
        CASE 
            WHEN h.buycode > 0 THEN 'PURCHASE'
            WHEN h.bbuycode > 0 THEN 'PURCHASE_RETURN'
            WHEN h.salescode > 0 THEN 'SALE'
            WHEN h.bsalescode > 0 THEN 'SALE_RETURN'
        END,
        d.number,
        d.fe
    FROM 
        dbo.dfactor d
        INNER JOIN dbo.hfactor h ON d.factorcode = h.factorcode
    WHERE 
        (h.buycode > 0 OR h.bbuycode > 0 OR h.salescode > 0 OR h.bsalescode > 0)
        AND h.fdate <= @todate
   ORDER BY 
    h.fdate, 
    h.ftime,
    -- اول خریدها، بعد فروش‌ها در صورت یکسان بودن تاریخ و زمان
    CASE 
        WHEN h.buycode > 0 THEN 1
        WHEN h.bbuycode > 0 THEN 2
        WHEN h.salescode > 0 THEN 3
        WHEN h.bsalescode > 0 THEN 4
    END,
    d.serial;
 
    -- جدول موقت برای وضعیت کالاها (شامل تعداد ورودی و خروجی)
    CREATE TABLE #KalaState (
        kalacode INT PRIMARY KEY,
        current_quantity NUMERIC(38,4),
        current_avg_price NUMERIC(24, 2),
        total_input NUMERIC(38,4) DEFAULT 0,
        total_output NUMERIC(38,4) DEFAULT 0
    );

    -- مقداردهی اولیه با داده‌های موجود
    INSERT INTO #KalaState (kalacode, current_quantity, current_avg_price)
    SELECT 
        kalacode,
        ISNULL(firststock, 0),
        CASE 
            WHEN ISNULL(firststock, 0) = 0 THEN 0.00
            ELSE CAST(firstprice AS NUMERIC(24, 2))
        END
    FROM 
        dbo.kala;

    -- پردازش تراکنش‌ها
    DECLARE 
        @codekala INT,
        @transaction_type VARCHAR(20),
        @quantity NUMERIC(18,4),
        @unit_price NUMERIC(19,0),
        @current_quantity NUMERIC(38,4),
        @current_avg_price NUMERIC(24, 2);

    DECLARE trans_cursor CURSOR FOR
        SELECT codekala, transaction_type, quantity, unit_price
        FROM #Transactions
        ORDER BY RowNum;

    OPEN trans_cursor;
    FETCH NEXT FROM trans_cursor INTO @codekala, @transaction_type, @quantity, @unit_price;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT 
            @current_quantity = current_quantity,
            @current_avg_price = current_avg_price
        FROM 
            #KalaState 
        WHERE 
            kalacode = @codekala;

        -- محاسبه تعداد ورودی و خروجی
        IF @transaction_type IN ('PURCHASE', 'SALE_RETURN')
        BEGIN
            UPDATE #KalaState
            SET 
                total_input = total_input + @quantity
            WHERE 
                kalacode = @codekala;
        END
        ELSE IF @transaction_type IN ('SALE', 'PURCHASE_RETURN')
        BEGIN
            UPDATE #KalaState
            SET 
                total_output = total_output + @quantity
            WHERE 
                kalacode = @codekala;
        END
     
        -- پردازش موجودی و میانگین قیمت
        IF @transaction_type = 'PURCHASE'
        BEGIN
            DECLARE @new_quantity NUMERIC(38,4) = @current_quantity + @quantity;
            DECLARE @new_avg_price NUMERIC(24, 2) = 
                CASE 
                    WHEN @new_quantity = 0 THEN 0.00
                    ELSE CAST( 
                        ( (@current_quantity * @current_avg_price) + (@quantity * @unit_price) ) / @new_quantity 
                        AS NUMERIC(24, 2) 
                    )
                END;

            UPDATE #KalaState
            SET 
                current_quantity = @new_quantity,
                current_avg_price = @new_avg_price
            WHERE 
                kalacode = @codekala;
        END
        ELSE IF @transaction_type = 'PURCHASE_RETURN'
        BEGIN
            DECLARE @updated_quantity_p NUMERIC(38,4) = @current_quantity - @quantity;
            DECLARE @updated_avg_price_p NUMERIC(24, 2) = 
                CASE 
                    WHEN @updated_quantity_p <= 0 THEN 0.00
                    ELSE @current_avg_price 
                END;

            UPDATE #KalaState
            SET 
                current_quantity = @updated_quantity_p,
                current_avg_price = @updated_avg_price_p
            WHERE 
                kalacode = @codekala;
        END
        ELSE IF @transaction_type IN ('SALE', 'SALE_RETURN')
        BEGIN
            DECLARE @updated_quantity_s NUMERIC(38,4) = 
                CASE 
                    WHEN @transaction_type = 'SALE' THEN @current_quantity - @quantity
                    ELSE @current_quantity + @quantity
                END;

            UPDATE #KalaState
            SET 
                current_quantity = @updated_quantity_s
            WHERE 
                kalacode = @codekala;
        END

        FETCH NEXT FROM trans_cursor INTO @codekala, @transaction_type, @quantity, @unit_price;
    END

    CLOSE trans_cursor;
    DEALLOCATE trans_cursor;

    -- به‌روزرسانی جدول اصلی kala
    UPDATE k
    SET 
        buy = CAST(ks.current_avg_price AS NUMERIC(24, 0)),
        numberinput = ks.total_input,
        numberoutput = ks.total_output
    FROM 
        dbo.kala k
        INNER JOIN #KalaState ks ON k.kalacode = ks.kalacode;

    DROP TABLE #Transactions;
    DROP TABLE #KalaState;
END;

GO


