بدست آوردن موجودی کالا از اسناد ورود و خروج

[ghasem110deh]

سلام به همه ...
دوستان واسه بدست آوردن موجودی کالا من یه فیلد از نوع int به جدول کالا اضافه میکردم که موجودی رو با اضافه یا کسر کردن مقدار ورودی و خروجی تو اون فیلد ثبت میکردم
اما یکی دو جا خوندم که درست نیست که فیلد موجودی کالا توی جدول کالا داشته باشیم !!!

پس چطور بدست بیاریم !؟
میشه از اسناد ورود خروج بدست آورد ؟

طراحی دیتابیس از خود برنامه سخت تره :(

[golbafan]

سلام
همیشه سعی کنید فقط داده های خام رو که قابل محاسبه نیستند در دیتابیس قرار بدید

مثال sqlserver


Create Table TranTest
(
SlNo Int Identity(1,1) Not Null Primary Key,
Debit Money Not Null,
Credit Money Not Null
)

کوئری:

Select Do.SlNo,Do.Debit,Do.Credit,
(Select SUM(Debit-Credit) From TranTest As D1 Where D1.SlNo <= Do.SlNo) As Balance From TranTest As Do


مثال کوئری


SlNo
Debit
Credit
Balance


1
200000

200000


2

5000
195000


3
5900

200900


4

6000
194900




مثال mysql
set @Balanc := 0;
select *,(@Balanc := @Balanc + credit - debt) AS Balanced
FROM (SELECT * from testtable)) as v11;

[SabaSabouhi]

سلام به همه ...
دوستان واسه بدست آوردن موجودی کالا من یه فیلد از نوع int به جدول کالا اضافه میکردم که موجودی رو با اضافه یا کسر کردن مقدار ورودی و خروجی تو اون فیلد ثبت میکردم
اما یکی دو جا خوندم که درست نیست که فیلد موجودی کالا توی جدول کالا داشته باشیم !!!

پس چطور بدست بیاریم !؟
میشه از اسناد ورود خروج بدست آورد ؟

طراحی دیتابیس از خود برنامه سخت تره :(

سلام
ضمن این که نوشته‌ی دوست خوبم golbafan رو تایید می‌کنم، اما شما می‌تونی تحت عنوان denormalization ستون یا
ستون‌هایی رو به جدول‌ها اضافه کنی برای مقاصدی مانند سرعت در گزارش‌گیری. البته مسوولیت صحت این مقادیر رو
هم باید بر عهده بگیری.
برای محاسبه‌ی موجودی کالا و ثبت در جدول به‌ترین کار تولید یک Trigger روی جدول کاردکس ( یا «کارت کالا» ) هست
که با هر نوبت ویرایش کاردکس موجودی اصلاح شود.
روی Trigger بودن این ویرایش به این دلیل هست که اگر به هر دلیلی موجودی کالا و جمع کاردکس مطابقت نداشته باشه
کل سیستم شما زیر سوال می‌ره. و تنها راه اطمینان 100% اینه که روی جدول کاردکس Trigger بگذاری.

صبا صبوحی

[ghasem110deh]

با تشکر از هر دو بزرگوار :)

پس این اشتباهه که مقادیری که توی برنامه (حالا هرجا) میشه بدست آورد توی دیتابیس ذخیره کرد !
اینم یه جور نرمال سازی میشه ؟


برای محاسبه‌ی موجودی کالا و ثبت در جدول به‌ترین کار تولید یک Trigger روی جدول کاردکس ( یا «کارت کالا» ) هست
که با هر نوبت ویرایش کاردکس موجودی اصلاح شود.

این بار دوم که میخوام تریگر بسازم ولی نمیتونم !

http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

http://www.codeproject.com/Articles/25600/Triggers-SQL-Server

https://sqlite.org/lang_createtrigger.html

البته منظورم ایجاد کردنش نیست ، مثل پروسیجر دیگه ...
این که چطور اون کاری که میخوام رو پیاده کنم که درست انجام بده ؟!

[SabaSabouhi]

با تشکر از هر دو بزرگوار :)

پس این اشتباهه که مقادیری که توی برنامه (حالا هرجا) میشه بدست آورد توی دیتابیس ذخیره کرد !
اینم یه جور نرمال سازی میشه ؟



سلام
بله، نگهداری اطلاعات اضافی و قابل محاسبه در حالت عادی صحیح نیست، اما بعضی وقت‌ها تحت عنوان Denormal کردن می‌تونی
ازش استفاده کنی. بعضی وقت‌های جلوی خیلی از join ها رو می‌گیره و سرعت کار رو بالا می‌بره.



این بار دوم که میخوام تریگر بسازم ولی نمیتونم !

البته منظورم ایجاد کردنش نیست ، مثل پروسیجر دیگه ...
این که چطور اون کاری که میخوام رو پیاده کنم که درست انجام بده ؟!

اول از کارهای ساده شروع کن، نوشتن تریگر خیلی جالبه.
چند تا قاعده مهم رو باید رعایت کنی.
1. به هیج عنوان از متغیر محلی استفاده نکن. هنگام کارهای جمعی شدیداً مشکل‌زا خواهد بود.
2. برای اطلاع از رکوردهای جدید از جدول مجازی inserted و برای رکوردهای حذف شده از جدول deleted استفاده کن.
3. توجه داشته باش که عمل update ترکیبی از حذف رکورد قبلی و اضافه کردن رکورد جدید خواهد بود.
4. تا جایی که مقدوره از ویرایش جدولی که برای تریگر نوشتی خودداری کن ( recursive نشه )

مثلاً اگه فرض کنیم جدول کاردکس دو ستون Incomming و Outgoing رو برای «وارده» و «صادره» داشته باشه
و جدول Inventory ستونی به عنوان Stock برای «موجودی» داشته باشه و هر دو جدول کلید GoodId رو برای کد
کالا داشته باشن. می‌تونی این کد رو در Trigger جدول GoodCard ( جدول کاردکس ) بنویسی:


‌UPDATE Inventory
SET Stock = Stock + Changes
FROM Inventory y
JOIN ( SELECT GoodId, Changes = Sum( Incomming - Outgoing )
FROM inserted ) i on i.GoodId = y.GoodId

البته من تو این مثال فرض کردم که جدول کاردکس فقط ورودی داره و هیچ سطری اجازه‌ی ویرایش شدن نداره.

صبا صبوحی

[golbafan]

سلام
شما الان 2 نوع سناریوی مختلف رو اینجا دیدی

1- سناریوی استفاده از view و کوئری که من خدمتتون عرض کردم
2- سناریوی تریگر که جناب صبوحی فرمودند

====

1- سناریوی اول مواقعی بکار میره که حجم دیتابیس و همچنین سرعت ورود اطلاعات مهم هست و داده ها تعدادشون بالاست ولی گزارش گیری اگر چند ثانیه بیشتر طول بکشه مشکلی نیست
2- سناریوی دوم مواقعی بکار میره که تعداد ورودی ها زیاد نیست و نیازی نیست هنگام ورود داده ها سرعت بالا باشه (مثلا از ایمپورت انبوه دیتا نمیخوایم استفاده کنیم) اما سرعت گزارش گیری برامون اهمیت بیشتری داره

[ghasem110deh]

هنوز درگیر طراحی دیتابیسم (خدا کنه آخرش چیز خوبی از آب دربیاد)
و چون نمیخوام مطالب رو گم کنم ، سوالات مربوط هم همینجا می پرسم ...
با تشکر
--------
الان دارم فقط مطلب در مورد انبارداری و طراحی دیتابیس میخونم !
واسه دیتابیس به این مطلب برخوردم : بدنه یا اسکلت (Staging layer)

تو این لایه از دیتابیس باید این حتما رعایت بشه : Avoid using indexes on staging tables to minimize I/O operations
اجتناب از استفاده از شاخص در صحنه جداول برای به حداقل رساندن I / O عملیات.

یعنی چی ؟
چیکار باید انجام داد که عملیات ورودی/خروجی رو به حداقل رسوند !؟

[golbafan]

سلام
این لایه میانی دیتابیس شماست که کارش برقراری ارتباط بهتر بین شما و داده های دخیره شده روی هارد هست. وقتی میخوای از ETL (برداشتن داده/جابجایی/بارگذاری) استفاده کنی معمولا برای بهینه تر شدن کار از این لایه استفاده میشه
این لایه برای برنامه های کلاینت / سرور و تحت شبکه بیشترین کاربرد رو داره چون باعث پایداری داده های شما میشه و حین انتقال از دستشون نمیدی
یکی دیگه از مزایای استفاده از این لایه، بحث هماهنگی، صف بندی دیتا و سینک شدن ارتباطات شبکه هست که بحث مفصلیه...
یک مثال خوب برای این موضوع ، مبحث ODBC هست که حتما باهاش آشنا هستید
حالا شما در صورت استفاده از دیتابیس های قوی امروزی oracle/mssql/mysql این کار رو اتومات انجام میدید

[golbafan]

یک مثال میزنم برای دوستان که بیشتر متوجه بشن (Staging layer)

برای مثال در نرم افزار های مخابراتی که کارشون لاگ کردن تماس ها هست (مخصوصا در تلفن های اعتباری) از این لایه حتما باید استفاده بشه چون در هر ثانیه باید تعداد زیادی دیتا رو در دیتابیس ذخیره کنن
حالا اگر روی این جداول بخوان ایندکس بزارن ، دیتابیس اونقدر کند میشه که عملا غیر قابل استفاده میشه و باید هزینه خیلی زیادی برای خرید سرور های قویتر بکنن!

خب راه حل اینه که بیایم دیتا رو بدون در نظر گرفتن هیچ شرط خاصی در انتهای جداول در این ناحیه قرار بدیم. (بعد در فرصتی که داریم داده ها رو (بطور اتومات و یا دستی) بطور طبقه بندی شده در میاریم و میریزیم در دیتابیس اصلی برای سرچ کردن و این حرفا...) اینجوری سرعت کار خیلی بالا میره و حجم دیتابیس میاد پایین. مثلا در mysql از انجین myIsam برای این کار استفاده میشه. در انجین اصلی (innodb ) ما اگر count بگیریم برای 1 میلیارد دیتا ممکنه چند ثانیه طول بکشه ولی در myisam در حد میلی ثانیه هست!

[ghasem110deh]

بازم سلام :)
--------------
از کلیات دیتابیس که بگذریم (هنوز نمیدونم جواب میده یا نه)
الان تو دو جای دیتابیس هنگ کردم :
1- جدول کالا مستقیم با جدول انبار مجازی مرتبط میشه یا از طریق جدول انبار مرتبط میشه ؟
2- کارتکس کالا با رسید و حواله (از سند انبار) مرتبط هستش ، حالا باید یک جدول کارتکس داشته باشیم یا میشه از این دو جدول گزارش رو گرفت (نظر خودم روی گرفتن گزارش از جدول اسناد هستش)

و مطلب دیگه که خودنم توی انبارداری lifo , fifo هستش که تقریبا گرفتن اصل موضوع چیه اما سوال اینه کا این رو میشه با استفاده تاریخ از سند رسید و حواله بدست آورد ؟
یعنی باید اینطور عمل کرد که کالاهای مربوط به فلان سند (تاریخ رسید) که زودتر وارد انبار شده زودتر هم خارج بشه ، و اینکه توی بدست آوردن ارزش ریالی چطور باید عمل کرد ؟

و رابطه سال مالی با انبار و انتقال از دوره قبل رو هیچی متوجه نشدم :)

[golbafan]

سلام
1- اگر منظورت از جدول انبار مجازی همون حالت Staging layer هست، باید بگم که نباید هیچ ریلیشنی براش ایجاد کنید
2- من view ایجاد میکنم
3- بحث مفصلی هست... یکم باید تحقیق کنی. راستش الان حسش نیست توضیح بدم . اگر مطلب رو پیدا نکردید من بعدش عرض میکنم خدمتت

[SabaSabouhi]

بازم سلام :)
یعنی باید اینطور عمل کرد که کالاهای مربوط به فلان سند (تاریخ رسید) که زودتر وارد انبار شده زودتر هم خارج بشه ، و اینکه توی بدست آوردن ارزش ریالی چطور باید عمل کرد ؟


سلام
در مورد ارزش ریالی خیلی ساده هست،
1. روش میانگین
کالا که وارد انبار می‌شه، ارزش ریالی اون کالا تو انبار رو افزایش می‌ده. مثلاً برای چیپس مزمز توی انبار یه ارزش کل داریم.
با ورود کالا با ضرب تعداد کالا در هزینه‌ی تمام شده‌ی کالا یک عدد بدست میاد که اون رو با ارزش کالا در انبار جمع می‌کنیم.
هنگام خروج کالا رو با ارزش جمع ارزش اون کالا تقسیم بر موجودی کالا خارج می‌کنیم.
2. روش fifo
تو این روش باید برای هر رسید انبار یک ستون مانده‌ی تعدادی داشته باشی. تو این حالت هنگام ورود کالا به انبار کار خاصی
انجام نمی‌دی، اما برای خروج کالا از انبار یک لیست از رسیدهای مانده‌دار ( مانده صفر نباشه ) با اولویت تاریخ و ساعت ورود
می‌گیری و تو یه حلقه مقدار خروجی کالا رو از مانده‌ها کم می‌کنی.
مثال:
رسید اول چیپس 100 تا به قیمت تمام‌شده ( ربطی به قیمت فروش نداره ) مثلا 840 تومان
تا الان 96 عدد فروخته شده، پس ستون مانده 4 هست
رسید دوم 100 تا به قیمت تمام شده‌ی 880 تومان. پس ستون مانده 100 هست.

الان می‌خوایم 10 عدد چیپس رو از انبار خارج کنیم. با روشی که گفتیم اول باید 4 عدد به قیمت تمام شده‌ی 840 و
سپس 6 عدد با قیمت تمام‌ شده‌ی 880 رو خارج کنیم.
پس در نتیجه مانده‌ی رسید اول صفر می‌شه و مانده‌ی رسید دوم می‌شه 94
ارزش حواله‌ی خروجی ما هم می‌شه 4*840 + 6*880 . ضمن این که تو حواله به هیچ عنوان بهای تمام‌شده‌ی واحد نداریم
یعنی میانگین گیری انجام نمی‌شه. این حواله به ارزش حاصل عبارت بالا کالا از انبار خارج کرده و سود حاصل از این فروش به
تفاوت مبلغ قید شده و بهای فروش هست که اگه هر بسته 1000 تومان فروخته شده باشه به راحتی می‌شه سود رو محاسبه کرد.
3. روش lifo
دقیقاً مثل حالت بالا هست با این تفاوت که تو لیست کردن رسیدها برعکس مرتب می‌کنیم.

امیدوارم که مفید واقع شده باشه
صبا صبوحی

[SabaSabouhi]

بازم سلام :)
و رابطه سال مالی با انبار و انتقال از دوره قبل رو هیچی متوجه نشدم :)


سلام
می‌تونه رابطه‌ای وجود داشته باشه و می‌تونه نداشته باشه.
بعضی مدیر مالی‌ها انبار رو در پایان سال نمی‌بندن و به صورت پیوسته عمل می‌کنن. که در این حالت هیچ رابطه‌ای بین
سال مالی و انبار وجود نخواهد داشت.
اما سایر مدیران مالی ( که تعدادشون ظاهراً بیشتر هم هست ) دوست دارن که انبار در پایان سال مالی بسته شده و
دوباره باز بشه.
نکته: معمولاً بسته شدن انبار در پایان دوره‌ی مالی همراه با «انبارگردانی» هست. اما این همیشگی نیست.
مثلاً تو بحث خدمات پس از فروش خودرو به هیچ عنوان امکان‌پذیر نیست که در پایان سال مالی که معمولاً منطبق بر سال شمسی
هست انبار گردانی انجام بشه، چون مراجعه برای سرویس خودرو در پایان سال به دلیل مسافرت‌های نوروزی «حداکثر» هست و
هیچ شرکت خدمات پس از فروش خودرو حاضر نیست که نزدیک پایان دوره‌ی مالی انبارگردانی انجام بده ( که می‌تونه یک ماه طول بکشه )
به همین دلیل ترجیح می‌دن که انبار پیوسته باشه و انبارگردانی در زمان «حداقل» مراجعه مشتریان انجام بشه.

وقتی که انبار پیوسته نباشه و مرتبط باشه با سال مالی، در پایان دوره‌ی مالی ( معمولاً اسفند ) یک انبارگردانی انجام می‌دن و
پس از تعدیل موجودی، انبار رو می‌بندن و به دوره‌ی بعدی منتقل می‌کنن. این کار درست مثل این می‌مونه که با یک حواله کل
موجودی رو از انبار خارج کنن و با یک رسید همون موجودی رو دوباره وارد انبار کنن. شاید این کار به نظر کار بیهوده‌ای بیاد اما
این جریان تو سیستم حسابداری یک گردش ایجاد می‌کنه که برای مدیران مالی قابل ارزش‌گذاری هست و از این گردش برای
تولید گزارش تزار مالی استفاده می‌کنن.
نکته این بستن و باز کردن هم اینه که بستن انبار در سال مالی قبل انجام می‌شه و باز کردن انبار در سال مالی جدید. این طور
تصور کنید که انبار رو در آخرین دقیقه‌ی 29 اسفند می‌بندین و موجودی رو خالی می‌کنین و در اولین دقیقه‌ی 1 فروردین تمام
اون موجودی رو وارد انبار می‌کنین.
نگران منطق کار هم نباش، چون تو سیستم انبار هیچ منطقی پشت این قضیه نیست و لازمه به سیستم حسابداری آشنا باشی
چون این کار اونجا معنی پیدا می‌کنه.

ببخشید اگه نمی‌تونم بیش‌تر از این توضیح بدم.
صبا صبوحی

[ghasem110deh]

سلام به همه ...
دوستان چطور میتونم روش استاندارد کدگذاری کالا رو پیاده کنم ؟!

تو انبارداری یکی از روش هاش ظاهرا اینطوریه :
دو رقم اول کد انبار
سه رقم دوم کد گروه کالا
سه رقم آخر کد کالا

چطوری باید این رو توی دیتابیس پیاده کرد ؟
اصلا ربطی به دیتابیس داره ... یا توی برنامه باید مدیریت بشه ؟

[ghasem110deh]

سلام به همه ...
باز تو دیتابیس به مشکل خوردم باز : (عکس ضمیمه)
واسه اسناد ورود و خروج یه جدول بیشتر در نظر نگرفتم که نوع سند رو با یه فیلد از نوع bool میشه تشخیص داد

الان میشه با یه پروسیجر کالا ها رو بر اساس آیدی کالا گروه بندی (group by) کرد
و بعد از طریق جمع و تفریق تعداد موجودی کالا رو بدست آورد ؟

------------------------------------------------------------------

البته باید فیلد تاریخ هم اضافه بشه که لیست بر حسب تاریخ مرتب بشه !
و نحوه پیاده کردن Lifo یا Fifo رو هم فکر نکنم بشه رو این دیتابیس پیاده کرد ...

با این کد فقط میشه مقدار کل یه کالا رو گرفت و اگه type_bool رو 0 کنی ... مقدار خروجی بدست میاد
ولی چطور اینا رو از هم تفریق کنم ؟


select sum(Quantity) from View_Inventory where [Type_Bool] = 1 group by [Commodity_Id]

[ghasem110deh]

دوستان من با این پروسیجر (من دراوردی) رفتم جلو ولی اصل کار که موجودی هست رو نشون نمیده !
یا بهتر خطا میده : (خروجی هم تصویر ضمیمه)


USE [Bonakdar_DB]
GO
/****** Object: StoredProcedure [dbo].[SP_Inventory] Script Date: 06/11/2015 11:35:36 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_Inventory]
AS
DECLARE @income INT = (select sum(Quantity) from View_Inventory where [Type_Bool] = 1 )
DECLARE @outcome INT = (select sum(Quantity) from View_Inventory where [Type_Bool] = 0 )
DECLARE @uotput TABLE (
commo_Id int,
nam nvarchar(20),
quant int,
uni nvarchar(50),
typ nvarchar(10),
dat datetime )
INSERT @uotput
SELECT [Commodity_Id], [Name], [Quantity], [Unit], [Type], [Date] FROM View_Inventory
BEGIN
SET NOCOUNT ON;
SELECT commo_Id, nam, quant, (@income - @outcome) AS inventory, uni, typ, dat FROM @uotput ORDER BY dat
END

[reza_ali202000]

اونجایی که دستور جمع مقادیر رو گذاشتی اگه یکی از فیلدها نال باشه جوابت میشه نال.
برا رفع این مشکل کافیه تبدیلش کنی به صفر اگه نال بود.
فک کنم دستورش هم بلد باشید.

[ghasem110deh]

دوستان با این ریلیشن و جداول میشه موجودی کالا رو بدست آورد :

http://s3.picofile.com/file/8221993526/inventory_table.png

view ایجاد شده :
http://s6.picofile.com/file/8221970118/3.png

اینم پروسیجر که از view_inventory که ایجاد شده اطلاعات رو میخونه :


USE [Bonakdar_DB]
GO
/****** Object: StoredProcedure [dbo].[SP_Inventory] Script Date: 10/11/2015 09:01:48 ق.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_Inventory]
AS
DECLARE @income INT = (select sum(ISNULL(Quantity), 0) from View_Inventory where Type_Bool = 1 group by Commodity_Id)
DECLARE @outcome INT = (select sum(ISNULL(Quantity), 0) from View_Inventory where Type_Bool = 0 group by Commodity_Id)
DECLARE @uotput TABLE (
commo_Id int,
nam nvarchar(20),
quant int,
uni nvarchar(50),
typ nvarchar(10),
dat datetime )
INSERT @uotput
SELECT Commodity_Id, [Name], Quantity, Unit, [Type], [Date] FROM View_Inventory
BEGIN
SET NOCOUNT ON;
SELECT commo_Id, nam, quant, ISNULL((@income - @outcome), 0) AS inventory, uni, typ, dat FROM @uotput group by commo_Id ORDER BY dat
END


خطاهای زیر رو به ترتیب اگه فقط در select آخر آیدی کالا و حاصل تفریق ورودی و خروجی باشه خطای اول و اگه همه فیلدها رو select کنم خطای دومی رو میده :


--------------------------- errorMsg 512, Level 16, State 1, Procedure SP_Inventory, Line 3Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Msg 512, Level 16, State 1, Procedure SP_Inventory, Line 4Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
(4 row(s) affected)



------------------------ error
Msg 8120, Level 16, State 1, Procedure SP_Inventory, Line 16Column '@uotput.nam' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[ghasem110deh]

به لطف آقای reza_ali202000 و راهنمایی ایشون (در واقع کد رو نوشتن) مشکل بر طرف شد !