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)
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
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
alter 
GO
F  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
/****** Object:  StoredProcedure [dbo].[sbuykala]    Script Date: 07/04/2024 10:56: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: 07/04/2024 10:56: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