public DataAccess()
{
objDataContext = new DriveEntities();
objDataContext.ContextOptions.LazyLoadingEnabled = true;
root = objDataContext.spare_group.FirstOrDefault(i => i.id == 1);
u = objDataContext.units.FirstOrDefault(i => i.id == 1);
}
public int SpareIncomeCreate(spare_income _obj, string AccountName, string WarehouseName, string CurrencyCode)
{
objDataContext = new DriveEntities();
_obj.is_remains_input = 0;
if (AccountName.Length > 0)
_obj.account = objDataContext.accounts.FirstOrDefault(i => i.name == AccountName);
_obj.warehouse = objDataContext.warehouses.FirstOrDefault(i => i.name == WarehouseName);
_obj.currency = objDataContext.currencies.FirstOrDefault(i => i.code == CurrencyCode);
objDataContext.AddTospare_income(_obj);
objDataContext.SaveChanges();
return _obj.id;
}
public void SaleDelete(int SaleID)
{
objDataContext = new DriveEntities();
// delete baskets
List<Basket> baskets = objDataContext.Baskets.Where(x => x.SaleID == SaleID).ToList();
List<int> spares = new List<int>();
foreach (Basket basket in baskets)
{
// delete offerings in basket
List<OfferingInBasketItem> links = objDataContext.OfferingInBasketItems.Where(x => x.BasketItemID == basket.ID).ToList();
foreach (OfferingInBasketItem link in links)
{
// delete spare in spare outgo
spare_in_spare_outgo siso = objDataContext.spare_in_spare_outgo.FirstOrDefault(x => x.id == link.OfferingOutgoID);
if (siso != null)
{
objDataContext.DeleteObject(siso);
}
// edit spare in spare income
spare_in_spare_income sisi = objDataContext.spare_in_spare_income.FirstOrDefault(x => x.id == link.OfferingIncomeID);
if (sisi != null)
{
sisi.QRest += link.Q;
}
objDataContext.DeleteObject(link);
}
spares.Add(basket.SpareID.Value);
objDataContext.DeleteObject(basket);
}
// delete sale
Sale s = objDataContext.Sales.FirstOrDefault(x => x.ID == SaleID);
if (s == null)
return;
objDataContext.DeleteObject(s);
objDataContext.SaveChanges();
foreach (int SpareID in spares)
SpareContainer.Instance.Update(SpareID);
}
public void RevisionDelete(int id)
{
objDataContext = new DriveEntities();
revision item = objDataContext.revisions.FirstOrDefault(i => i.id == id);
if (item == null)
return;
// сначала удалить все прикрепленные товары
OfferingsDeleteByRevisionId(id);
objDataContext.DeleteObject(item);
objDataContext.SaveChanges();
}
public revision RevisionCreate(revision _obj)
{
objDataContext = new DriveEntities();
objDataContext.AddTorevisions(_obj);
objDataContext.SaveChanges();
return _obj;
}
public void InOfferingDelete(int offeringId)
{
int SpareID = -1;
// получаем запись, которую собираемся удалять
spare_in_spare_income offering = objDataContext.spare_in_spare_income.FirstOrDefault(i => i.id == offeringId);
if (offering != null)
{
OfferingInBasketItem oibi = objDataContext.OfferingInBasketItems.FirstOrDefault(x => x.OfferingIncomeID == offeringId);
if (oibi != null)
{
objDataContext.DeleteObject(oibi);
objDataContext.SaveChanges();
}
if (offering.spare == null)
offering.spareReference.Load();
SpareID = offering.spare.id;
// удаляем связанные с этим приходом переоценки
OvOfferingDeleteBySISIID(offeringId);
// удаляем связанные с этим поступлением отгрузки
OutgoOfferingDeleteBySISIID(offeringId);
try
{
objDataContext = new DriveEntities();
offering = objDataContext.spare_in_spare_income.FirstOrDefault(i => i.id == offeringId);
if (offering != null)
objDataContext.DeleteObject(offering);
}
catch (Exception)
{
}
// delete offering in overpricing
objDataContext.SaveChanges();
SpareContainer.Instance.Update(SpareID);
}
}
public void FixIncomeQuantity(int SpareID, decimal NewQuantity)
{
objDataContext = new DriveEntities();
SpareView spare = SpareContainer.Instance.Spares.FirstOrDefault(x => x.id == SpareID);
decimal difference = NewQuantity - (decimal)spare.QRest;
// обновить записи о поступлениях
var incomes = from income in objDataContext.spare_in_spare_income where income.spare.id == SpareID orderby income.spare_income.si_date select income;
if (incomes.Count() == 0)
throw new Exception("Не найдено ни одного прихода по данному товару!");
List<spare_in_spare_income> sisiList = incomes.ToList();
// если было 0
if (spare.QRest == 0)
{
spare_in_spare_income income = sisiList.First();
income.QRest = NewQuantity;
objDataContext.SaveChanges();
}
else
{
// если было >0
List<spare_in_spare_income>.Enumerator incomeEnumerator = sisiList.GetEnumerator();
// если надо увеличить количество
if (difference > 0)
{
incomeEnumerator.MoveNext();
incomeEnumerator.Current.QRest += difference;
}
else
// если надо уменьшить количество
{
// если новое количество = 0
if (NewQuantity <= 0)
{
while (incomeEnumerator.MoveNext())
incomeEnumerator.Current.QRest = 0;
}
else
while (difference != 0)
{
incomeEnumerator.MoveNext();
// если в последнем приходе больше, чем разница
if (incomeEnumerator.Current.QRest > (-1) * difference)
{
incomeEnumerator.Current.QRest += difference;
difference = 0;
}
else
// если в последнем приходе меньше, чем разница
{
difference += (decimal)incomeEnumerator.Current.QRest;
incomeEnumerator.Current.QRest = 0;
}
}
}
objDataContext.SaveChanges();
}
// обновить запись в кэше (в БД)
//spare.QRest = (double)NewQuantity;
SpareContainer.Instance.Update(SpareID, true);
}
public void SpareOutgoDelete(int spareOutgoId)
{
objDataContext = new DriveEntities();
spare_outgo item = objDataContext.spare_outgo.FirstOrDefault(i => i.id == spareOutgoId);
// сначала удалить все прикрепленные товары
OutOfferingDeleteBySpareOutgoId(spareOutgoId);
objDataContext.DeleteObject(item);
objDataContext.SaveChanges();
}
public void OverpricingDelete(int id)
{
objDataContext = new DriveEntities();
overpricing item = objDataContext.overpricings.FirstOrDefault(i => i.id == id);
if (item == null)
return;
// откатить
if (item.commited.Value)
{
RecalcIncomesPricesBack(item.id);
}
// сначала удалить все прикрепленные товары
OfferingsDeleteByOverpricingId(id);
objDataContext.DeleteObject(item);
objDataContext.SaveChanges();
}
public overpricing OverpricingCreate(overpricing _obj)
{
objDataContext = new DriveEntities();
objDataContext.AddTooverpricings(_obj);
objDataContext.SaveChanges();
return _obj;
}
public void OutOfferingDelete(int offeringId)
{
objDataContext = new DriveEntities();
spare_in_spare_outgo offering = objDataContext.spare_in_spare_outgo.FirstOrDefault(i => i.id == offeringId);
if (offering == null)
return;
// delete linked basket item
OfferingInBasketItem oibi = objDataContext.OfferingInBasketItems.FirstOrDefault(x => x.OfferingOutgoID == offeringId);
if (oibi != null)
{
int BasketID = oibi.BasketItemID;
objDataContext.DeleteObject(oibi);
Basket basket = objDataContext.Baskets.FirstOrDefault(x => x.ID == BasketID);
if (basket != null)
objDataContext.DeleteObject(basket);
//objDataContext.SaveChanges();
}
if (offering.spare_in_spare_income == null)
offering.spare_in_spare_incomeReference.Load();
if (offering.spare_in_spare_income != null)
{
spare_in_spare_income sin = objDataContext.spare_in_spare_income.FirstOrDefault(s => s.id == offering.spare_in_spare_income.id);
offering.spare_outgoReference.Load();
spare_outgo outgo = objDataContext.spare_outgo.FirstOrDefault(i => i.id == offering.spare_outgo.id);
if (sin != null && outgo.isGhost != 1)
sin.QRest += offering.quantity;
}
objDataContext.DeleteObject(offering);
objDataContext.SaveChanges();
}
public void InvoiceEdit(invoice item, int AccountID)
{
objDataContext = new DriveEntities();
invoice original = objDataContext.invoices.FirstOrDefault(b => b.id == item.id);
if (original != null)
{
original.InvoiceDate = item.InvoiceDate;
original.InvoiceSum = item.InvoiceSum;
original.AccountBankName = item.AccountBankName;
original.BankAccountID = item.BankAccountID;
//original.AccountName = item.AccountName;
original.AccountUNN = item.AccountUNN;
original.AccountAddress = item.AccountAddress;
original.AccountBankMFO = item.AccountBankMFO;
original.InvoiceNumber = item.InvoiceNumber;
//original.account = item.account;
if (AccountID > 0)
{
original.account = objDataContext.accounts.FirstOrDefault(i => i.id == AccountID);
original.AccountName = original.account.name;
}
//original.base_doc = item.base_doc;
//original.base_doc_date = item.base_doc_date;
//original.currency = objDataContext.currencies.FirstOrDefault(i => i.code == CurrencyCode);
//original.si_date = item.si_date;
//original.warehouse = objDataContext.warehouses.FirstOrDefault(i => i.name == WarehouseName);
//original.cashless = item.cashless;
objDataContext.SaveChanges();
}
}
public void InvoiceDelete(int id)
{
objDataContext = new DriveEntities();
invoice item = objDataContext.invoices.FirstOrDefault(i => i.id == id);
if (item == null)
return;
// сначала удалить все прикрепленные товары
InvoiceOfferingsDelete(id);
objDataContext.DeleteObject(item);
objDataContext.SaveChanges();
}
public invoice InvoiceCreate(invoice _obj)
{
objDataContext = new DriveEntities();
if (objDataContext.invoices.Count() > 0)
_obj.InvoiceNumber = objDataContext.invoices.Max(x => x.InvoiceNumber) + 1;
else
_obj.InvoiceNumber = 1;
objDataContext.AddToinvoices(_obj);
objDataContext.SaveChanges();
//_obj.InvoiceNumber = _obj.id;
return _obj;
}
public void SpareIncomeDelete(int SpareIncomeID)
{
objDataContext = new DriveEntities();
spare_income item = objDataContext.spare_income.FirstOrDefault(i => i.id == SpareIncomeID);
if (item == null)
return;
// удалить все прикрепленные товары Spare In SpareIncome
//InOfferingDeleteBySpareIncomeId(spareIncomeId);
spare_in_spare_income sisi = objDataContext.spare_in_spare_income.FirstOrDefault(i => i.spare_income.id == SpareIncomeID);
while (sisi != null)
{
if (sisi.spare == null)
sisi.spareReference.Load();
int SpareID = sisi.spare.id;
int SpareInSpareIncomeID = sisi.id;
// удалить завязанные на данный приход Spare In SpareOutgo
//FixOfferingsOutsOnDeletingIncome(item.id);
List<spare_in_spare_outgo> sisos = objDataContext.spare_in_spare_outgo.Where(x => x.spare_in_spare_income.id == SpareInSpareIncomeID).ToList();
foreach (spare_in_spare_outgo siso in sisos)
{
siso.spare_in_spare_income = null;
}
objDataContext.SaveChanges();
objDataContext.DeleteObject(sisi);
objDataContext.SaveChanges();
SpareContainer.Instance.Update(SpareID);
sisi = objDataContext.spare_in_spare_income.FirstOrDefault(i => i.spare_income.id == SpareIncomeID);
}
// удалить SpareIncome
objDataContext = new DriveEntities();
item = objDataContext.spare_income.FirstOrDefault(i => i.id == SpareIncomeID);
if (item == null)
return;
objDataContext.DeleteObject(item);
objDataContext.SaveChanges();
}
public void SpareIncomeEdit(spare_income item, string AccountName, string WarehouseName, string CurrencyCode)
{
objDataContext = new DriveEntities();
spare_income original = objDataContext.spare_income.FirstOrDefault(b => b.id == item.id);
if (original != null)
{
if (AccountName.Length > 0)
original.account = objDataContext.accounts.FirstOrDefault(i => i.name == AccountName);
original.IDN = item.IDN;
original.num = item.num;
original.base_doc = item.base_doc;
original.base_doc_date = item.base_doc_date;
original.currency = objDataContext.currencies.FirstOrDefault(i => i.code == CurrencyCode);
original.si_date = item.si_date;
original.warehouse = objDataContext.warehouses.FirstOrDefault(i => i.name == WarehouseName);
original.cashless = item.cashless;
objDataContext.SaveChanges();
}
}
//public spare_in_overpricing OverpricingOfferingGet(int offeringId)
//{
// return objDataContext.spare_in_overpricing.FirstOrDefault(i => i.id == offeringId);
//}
public void OverpricingOfferingEdit(int ID, int i1, decimal d2, decimal d3)
{
objDataContext = new DriveEntities();
spare_in_overpricing s = objDataContext.spare_in_overpricing.FirstOrDefault(i => i.id == ID);
s.percentNew = i1;
s.priceNew = d2;
s.sumNew = d3;
objDataContext.SaveChanges();
}
public void CurrencyRatesEdit(List<currency_rate> lst)
{
objDataContext = new DriveEntities();
foreach (currency_rate r in lst)
{
currency_rate o = getCurrencyRate(r.currency.code, r.rate_date);
if (o != null)
o.rate = r.rate;
}
objDataContext.SaveChanges();
}
public void OverpricingOfferingEdit(SpareInOverpricingView siov)
{
objDataContext = new DriveEntities();
spare_in_overpricing s = objDataContext.spare_in_overpricing.FirstOrDefault(i => i.id == siov.id);
s.percentNew = siov.percentNew;
s.priceNew = siov.priceNew;
s.sumNew = siov.sumNew;
objDataContext.SaveChanges();
}
private void timer_Elapsed(object source, System.Timers.ElapsedEventArgs e)
{
lock (SpareContainer.Instance)
{
try
{
if (!ready())
return;
DriveEntities db = new DriveEntities();
if (LastCheckedID == 0)
LastCheckedID = db.SpareLogs.Max(x => x.ID);
var events = from s in db.SpareLogs where (s.UserSPID != this.SPID && s.ID > LastCheckedID) select s;
foreach (SpareLog log in events)
{
if (log.EventType == "U" || log.EventType == "I")
{
int SpareID = log.RecordID;
SpareContainer.Instance.Update(SpareID);
mainWindowObj.Dispatcher.BeginInvoke(System.Windows.Threading.DispatcherPriority.Normal,
new System.Windows.Threading.DispatcherOperationCallback(delegate
{
mainWindowObj.LoadSpares();
return null;
}), null);
}
else if (log.EventType == "D")
{
SpareContainer.Instance.Remove(log.RecordID);
mainWindowObj.Dispatcher.BeginInvoke(System.Windows.Threading.DispatcherPriority.Normal,
new System.Windows.Threading.DispatcherOperationCallback(delegate
{
mainWindowObj.LoadSpares();
return null;
}), null);
}
}
LastCheckedID = db.SpareLogs.Max(x => x.ID);
}
catch (Exception ex1)
{
this.Log(ex1.Message);
}
}
}
public SpareView GetSpareView(int id)
{
objDataContext = new DriveEntities();
return objDataContext.SpareViews.FirstOrDefault(s => s.id == id);
}
