21-11-21, 11:56 PM
في هذا المقال سأعمل كشف حساب للصندوق بتاريخ محدد مع وضع رصيد سابق للتاريخ المحدد عن طريق الاستعلام :
سكربت إنشاء الجدول :
دفتر اليومية كامل :
كشف حساب الصندوق :
كشف حساب للصندوق و عرض الحساب المقابل له :
PHP كود :
use QtnDB1;
-- حذف جدول ان وجد
if OBJECT_ID('tempdb..#temp') is not null
begin
drop table #temp
PRINT 'Exist'
end
-- إنشاء الجدولمختصر عن دفتر اليومية
CREATE TABLE #temp
(
[DebitAccount] [decimal](18, 2) NOT NULL,
[CreditAccount] [decimal](18, 2) NOT NULL,
[BalanceAccount] [decimal](18, 2) NOT NULL,
[AccountID] [int] NOT NULL,
[AccountName] [nvarchar](50) NOT NULL,
[AccountStatement] [nvarchar](50) NOT NULL,
[AccountDate] [date] NOT NULL,
[AutoNumbering] [int] NOT NULL,
[AccountNum] [int] NOT NULL,
) ON [PRIMARY]
-- تفريغ الجدول
delete from #temp
-- هذا إستعلام إضافة سجل رصيد الزبون قبل البحث بين تاريخين
insert into #temp (DebitAccount,CreditAccount,BalanceAccount,AccountID,AccountName,AccountStatement,AccountDate,AutoNumbering,AccountNum)(Select Sum(DebitAccount)As DebitAccount,Sum(CreditAccount)As CreditAccount,Sum(DebitAccount)-Sum(CreditAccount)As BalanceAccount,0,N'رصيد سابق',N'رصيد سابق','2020-09-09',1,0 From DayBook Where (AccountNum IN(SELECT AccountNum FROM daybook AS DAY_BOOK_1
WHERE AccountDate < '2020-09-10') And AccountID != 1811)
group by AccountID,AccountName,AccountNum)
-- جلب عدد سجلات الجدول المؤقت
DECLARE @RecCount int
set @RecCount= (Select count(*) from #temp)
SELECT DebitAccount, CreditAccount, Sum(DebitAccount)-Sum(CreditAccount)As BalanceAccount, AccountID, AccountName, AccountStatement,
AccountMoneyName, AccountDate, AccountNum, Accounting, AutoNumbering FROM dbo.daybook WHERE (AccountID = 1811)
group by DebitAccount, CreditAccount,AccountID, AccountName, AccountStatement,AccountMoneyName, AccountDate, AccountNum, Accounting, AutoNumbering
-- جلب سجل الرصيد و معلوماته
select Sum(DebitAccount)As DebitAccount,Sum(CreditAccount)As CreditAccount,Sum(DebitAccount)-Sum(CreditAccount)As BalanceAccount,0 as AccountID,N'رصيد سابق'as AccountName,N'رصيد سابق'as AccountStatement,''as AccountMoneyName,'2020-09-09'as AccountDate,0 as AccountNum,1 as Accounting,0 as AutoNumbering from #temp
-- دمج النتيجتين الخاصتين بكشف الحساب
UNION ALL
SELECT DebitAccount, CreditAccount, Sum(DebitAccount)-Sum(CreditAccount)As BalanceAccount, AccountID, AccountName, AccountStatement,
AccountMoneyName, AccountDate, AccountNum, Accounting, AutoNumbering FROM daybook WHERE (AccountNum IN(SELECT AccountNum FROM daybook AS DAY_BOOK_1
WHERE (AccountID = 1811))) AND (AccountID != 1811) And AccountDate > '2020-09-10'
group by DebitAccount, CreditAccount,AccountID, AccountName, AccountStatement,AccountMoneyName, AccountDate, AccountNum, Accounting, AutoNumbering;
سكربت إنشاء الجدول :
PHP كود :
USE [QtnDB1]
GO
/****** Object: Table [dbo].[daybook] Script Date: 10/23/2021 2:32:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[daybook](
[DebitAccount] [decimal](18, 2) NOT NULL,
[CreditAccount] [decimal](18, 2) NOT NULL,
[BalanceAccount] [decimal](18, 2) NOT NULL,
[AccountID] [int] NOT NULL,
[AccountName] [nvarchar](50) NOT NULL,
[AccountStatement] [nvarchar](50) NULL,
[AccountMoneyName] [int] NOT NULL,
[AccountDate] [date] NOT NULL,
[AccountNum] [int] NOT NULL,
[Accounting] [int] NOT NULL,
[AutoNumbering] [int] IDENTITY(1,1) NOT NULL,
[IsDeleteed] [bit] NULL,
CONSTRAINT [PK_daybook] PRIMARY KEY CLUSTERED
(
[AutoNumbering] 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
دفتر اليومية كامل :
كشف حساب الصندوق :
كشف حساب للصندوق و عرض الحساب المقابل له :
سبحان الله وبحمده سبحان الله العظيم و الحمد لله ولا اله الا الله والله اكبر