Tag: dev

Live Hareket Goren istenmeyen malzemeleri toplu olarak sqlden silme

On By semt20In sentez, SqlLeave a comment 
Silinmesi isdenilen malzemelerin recidleri () lerin icine yazilir




--update Erp_InventoryPriceList set UnitSetItemId = null where InventoryId in
--()

--delete Erp_InventoryPriceList where InventoryId in
--()

--delete Erp_RecipeItem from Erp_RecipeItem RI 
--where RecipeId = (Select RecId from Erp_Recipe R where R.RecId = RI.RecipeId and R.InventoryId in
--()
--)

--delete Erp_Recipe  where InventoryId in
--()

--update Erp_Inventory Set RecipeUnitItemId = null where RecId in
--()

--update Erp_InventoryUnitItemSize set InventoryId = null where InventoryId in
--()

--delete Erp_InventoryUnitItemSize where InventoryId in
--()

--delete Erp_InventoryBarcode where InventoryId in
--()

--delete Erp_InventoryWarehouse where InventoryId in
--()

--delete Erp_Project where InventoryId in
--()

--delete Erp_FixedAssetDepreciation where InventoryId in
--()

--delete Erp_InventoryExplanation where InventoryId in
--()

--delete Erp_InventoryAlternative  where InventoryId in
--()

--delete Erp_FixedAssetExpense  where InventoryId in
--()

--delete Erp_InventoryAttachment where InventoryId in
--()

--delete Erp_InitialCostItem from Erp_InitialCostItem IC 
--where InitialCostId = (Select RecId from Erp_InitialCost I where I.RecId = IC.InitialCostId and I.InventoryId in
--()
--)

--delete Erp_InitialCost where InventoryId in
--()

--delete Erp_RecipeItem where InventoryId in
--()

--delete Erp_RouteItem from Erp_RouteItem RI where RouteId = (select RecId from Erp_Route R where RI.RouteId=R.RecId and R.InventoryId in
--()
--)

--delete Erp_Route where InventoryId in
--()

--delete Erp_Inventory where RecId in
--()

Sentez Live Dev – Sql Queryden Hareketlerin Silinmesi

On By semt20In sentez, SqlLeave a comment
Aşağıdaki sorgu liveda (peşin satışsız,ncrsız) kartlar dışında hareketleri siler:(malzeme fişleri ,irsaliyeler,faturalar,teklifler,sipariş,muhasebe fişi,banka çek fişleri).
Sildikten sonra shrink yapılmalı aşağıda nasıl yapıldığı anlatılmıştır.Yaklaşık 14 gblık datayı 700mba düşürdü.

 

 

 

delete Erp_InventoryReceiptItem
update Erp_Invoice set InventoryReceiptId = null
update Erp_InventoryReceiptAttachment set  InventoryReceiptId = null
delete Erp_InventoryReceiptAttachment
delete Erp_InvoiceAttachment
delete Erp_WorkOrderProduction
delete Erp_InventoryReceipt
delete Erp_Invoice
delete Erp_BankAccountTotal
delete Erp_BankCredit
delete Erp_BankReceiptItem
update Erp_ChequeReceipt set BankReceiptId=null
delete Erp_ChequeReceiptItem
delete Erp_ChequeReceiptAttachment
delete Erp_ChequeReceipt
delete Erp_BankReceiptAttachment
delete Erp_BankReceipt
delete Erp_CashTotalItem
delete Erp_CashTotal
delete Erp_Cheque
delete Erp_OrderReceiptItem
delete Erp_OrderReceiptAttachment
delete Erp_OrderReceipt
delete Erp_ContractItem
delete Erp_ContractAttachment
delete Erp_Contract
delete Erp_CurrentAccountReceiptItem
delete Erp_CurrentAccountReceiptAttachment
delete Erp_CurrentAccountReceipt
delete Erp_CurrentAccountTotal
delete Erp_QuotationReceiptItem
delete Erp_QuotationReceiptAttachment
delete Erp_QuotationReceipt
delete Erp_DemandReceiptItem
delete Erp_DemandReceiptAttachment
delete Erp_DemandReceipt
delete Erp_WorkOrderItem
delete Erp_WorkOrderAttachment
delete Erp_WorkOrderExplanation
delete Erp_WorkOrder
delete Erp_GLReceiptItem
delete Erp_GLReceipt
Delete Erp_InventoryTotal
delete Erp_ReceiptPaymentItem
delete Meta_ForexRate
delete Erp_ServiceTotal
Delete Erp_GLAccountTotal
delete Erp_BankAccountTotal
truncate table Log_Transaction

shrink işlemi:
USE LiveHareketler;
GO
ALTER DATABASE LiveHareketler
SET RECOVERY SIMPLE;
GO

--Datadakı log dosyasını shrınk yap yani database sağ tıkla task shrink files log->reorganize page 0 ve tamam ve sonrasındada yine databasee sağ tık task shrink database ok

ALTER DATABASE LiveHareketler
SET RECOVERY FULL;
GO

Sentez Live Erp Kesin Yevmiyeyi Silme

On By semt20In sentez, SqlLeave a comment
OIuşan Sorunlar:
– Kesin Yevmiye Tarihinden Önce İşlem Yapılamaz .
– entegrasyon fişi yevmiye madde numarasına sahip. Bu fiş üzerinden işlem yapamazsınız .

select RegBookNo,  * from Erp_GLReceipt –RegBookNo boş deilse yevmiye oluşturulmuş demektir

select * from Erp_GLRegBook –yevmiye oluşan fiş

update  Erp_GLReceipt set RegBookNo = null –yevmiye oluştuysa sil

Sentez Dev Mizan Excele

On By semt20In EXCEL, sentez, SqlLeave a comment
excelde
data -> from other source -> from microsoft query>new data source ilişklendirmeyi yap sorguyada aşağıdakileri yapıştır…

 

profilerda mizanı çekerken kod:

 

exec sp_executesql N' if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp create table #GLReceiptBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,ForexDebit decimal(28,8),ForexCredit decimal(28,8),ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8),ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8))  insert into #GLReceiptBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,ForexDebit,ForexCredit,ForexDebitBalance,ForexCreditBalance ,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance,InflowQty,OutflowQty) select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,isnull(sum(AR.Debit),0) Debit,0 Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,isnull(sum(AR.ForexAmount),0)ForexDebit,0 ForexCredit,isnull(sum(AR.ForexAmount),0)ForexDebitBalance,0 ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,isnull(sum(AR.Quantity),0)InflowQty,0 OutflowQty  from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId)  left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId)  where  A.CompanyId = 5 and  (A.IsDeleted is null or A.IsDeleted = 0)  and  (AR.IsDeleted is null or AR.IsDeleted = 0)  and AR.Debit 0     and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5)  and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2         group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName
 union all select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,0 Debit,isnull(sum(AR.Credit),0) Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,0 ForexDebit,isnull(sum(AR.ForexAmount),0) ForexCredit,0 ForexDebitBalance,isnull(sum(AR.ForexAmount),0) ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,0 InflowQty,isnull(sum(AR.Quantity),0) OutflowQty  from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId)  left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId)  where  A.CompanyId = 5 and  (A.IsDeleted is null or A.IsDeleted = 0)  and  (AR.IsDeleted is null or AR.IsDeleted = 0)  and AR.Credit 0     and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5)  and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2         group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp  create table #GLAccountBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,DBalance decimal(28,8),CBalance decimal(28,8),ForexDebit decimal(28,8),ForexCredit decimal(28,8) ,ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8),FDBalance decimal(28,8),FCBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8) ,ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8),FCDBalance decimal(28,8),FCCBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8)) insert into #GLAccountBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,DBalance,CBalance,ForexDebit,ForexCredit ,ForexDebitBalance,ForexCreditBalance,FDBalance,FCBalance,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance ,FCDBalance,FCCBalance,InflowQty,OutflowQty) select CompanyId,AccountId,AccountCode,AccountName,isnull(sum(Debit),0)Debit,isnull(sum(Credit),0)Credit,isnull(sum(DebitBalance),0)DebitBalance,isnull(sum(CreditBalance),0)CreditBalance ,case when isnull(sum(DebitBalance),0)>isnull(sum(CreditBalance),0) then round(isnull(sum(DebitBalance),0)-isnull(sum(CreditBalance),0),2) else 0 end DBalance ,case when isnull(sum(DebitBalance),0)isnull(sum(ForexCreditBalance),0) then round(isnull(sum(ForexDebitBalance),0)-isnull(sum(ForexCreditBalance),0),2) else 0 end FDBalance ,case when isnull(sum(ForexDebitBalance),0)isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateDebitBalance),0)-isnull(sum(ForexCalculateCreditBalance),0),2) else 0 end FCDBalance ,case when isnull(sum(ForexCalculateDebitBalance),0)<isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateCreditBalance),0)-isnull(sum(ForexCalculateDebitBalance),0),2) else 0 end FCCBalance ,isnull(sum(InflowQty),0)InflowQty,isnull(sum(OutflowQty),0)OutflowQty  from #GLReceiptBalanceTmp  group by CompanyId,AccountId,AccountCode,AccountName  having round(isnull(sum(Debit),0),2) round(isnull(sum(Credit),0),2) select A.CompanyId,'''' [Şirket],isnull((select MF.ForexCode from Meta_Forex MF where MF.RecId=A.ForexId),'''') [Dvz],'''' [HDvz],A.AccountCode [Hesap Kodu],A.AccountName [Hesap Adı],A.SpecialCode [Özel Kod],0.0 [Devir Borç],0.0 [Devir Alacak],sum(isnull(B.Debit,0)) [Borç],sum(isnull(B.Credit,0)) [Alacak],case when sum(isnull(B.DBalance,0)) > sum(isnull(B.CBalance,0)) then sum(isnull(B.DBalance,0)) - sum(isnull(B.CBalance,0)) when sum(isnull(B.DBalance,0)) sum(isnull(B.CBalance,0)) then ''BB'' when sum(isnull(B.DBalance,0)) sum(isnull(B.FCBalance,0)) then round(sum(isnull(B.FDBalance,0)) - sum(isnull(B.FCBalance,0)),2) when sum(isnull(B.FDBalance,0)) sum(isnull(B.FCBalance,0)) then ''BB'' when sum(isnull(B.FDBalance,0)) sum(isnull(B.FCCBalance,0)) then round(sum(isnull(B.FCDBalance,0)) - sum(isnull(B.FCCBalance,0)),2)when sum(isnull(B.FCDBalance,0)) sum(isnull(B.FCCBalance,0)) then ''BB'' when sum(isnull(B.FCDBalance,0)) < sum(isnull(B.FCCBalance,0)) then ''AB'' else '''' end  [Hesaplanan Döviz Bakiye Tipi],sum(isnull(B.FCDBalance,0)) [Hesaplanan Döviz Borç Bakiye],sum(isnull(B.FCCBalance,0)) [Hesaplanan Döviz Alacak Bakiye],sum(isnull(B.InflowQty,0)) [Giriş Miktarı],sum(isnull(B.OutflowQty,0)) [Çıkış Miktarı],sum(isnull(B.InflowQty,0)) - sum(isnull(B.OutflowQty,0)) [Kalan],-1 CAccount  from Erp_GLAccount A with (nolock)  left join #GLAccountBalanceTmp B on (B.AccountId in(select AA.RecId from Erp_GLAccount AA where AA.CompanyId=A.CompanyId and AA.AccountCode like rtrim(A.AccountCode)+''%'' ))  where  A.CompanyId = 5 and  (A.IsDeleted is null or A.IsDeleted = 0)    group by A.CompanyId,A.ForexId,A.AccountCode,A.AccountName,A.SpecialCode  having round(isnull(sum(B.Debit),0),2) round(isnull(sum(B.Credit),0),2) order by A.AccountCode

if object_id(''tempdb..#GLReceiptTmp'') is not null drop table #GLReceiptTmp if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp  if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp  ',N'@0ARErp_GLReceiptItemReceiptDate1 datetime,@0ARErp_GLReceiptItemReceiptDate2 datetime',@0ARErp_GLReceiptItemReceiptDate1='2017-01-01 00:00:00',@0ARErp_GLReceiptItemReceiptDate2='2018-12-31 23:59:00'