SORUN: CARİ – MALZEME – BANKA HESAP HAREKET GÖRMEMESİNE RAĞMEN SİLİNEMEMESİ. (LOGO GO3)
TRACE KONTROLLERİ YAPILARAK İLGİLİ TABLOLARIN REFERANSLARI KONTROL EDİLDİ VE KAYIT YOKDU:
UPDATE LG_122_CLCARD SET USEDINPERIODS='0' WHERE ( LOGICALREF = 575)
UPDATE LG_122_ITEMS SET USEDINPERIODS='0' WHERE ( LOGICALREF = 1999)
UPDATE LG_122_BANKACC SET USEDINPERIODS='0' WHERE ( LOGICALREF = 4)
YUKARDAKİ KODLAR ÇALIŞTIRILIP KONTROLLER SAĞLANDI…
Collation sorunundan dolayı karşıma çıktı:
Ayrıca yine veritabanı altında Programmability->Store Procedures altından getParameters ve WOR_ ile başlayan prosedürleri, Functions->Scalar-Valued Functions altından GetWebOrderDateInt, Views altındadan VGetdate viewini siliniz. Sildikten sonra yeniden tabloların oluşturulmasını sağlayabilirsiniz?(Mobile Sales Genel Ayarlar açılarak veritabanı tablolarını ekleyebilirsiniz.)
bunlarıda sil:
spWebOrderParseBarkod
splitString
--CREATE PROCEDURE [dbo].[P_HTML]
--as
declare @bodyOzet varchar(max)
declare @bodyDetay varchar(max)
/*
CREATE TABLE [dbo].[HTML](
[HTMLCODE] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--DOSYAYA KAYDETMESI ICIN AYAR ACILMALI
sp_configure 'show advanced options', '1'
RECONFIGURE
GO
-- this enables xp_cmdshell
sp_configure 'xp_cmdshell', '1'
RECONFIGURE
GO
--DOSYAYA KAYDETME KOMUTU
declare @sql varchar(8000)
select @sql = 'bcp "select * from [DATABASE].[dbo].HTML" queryout E:\SONUC.html -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
--ISLEM SORGUSU
exec dbo.P_HTML
*/
set @bodyDetay = cast( (
select td = cast(day(Tarih) as varchar)+'/'+cast(month(Tarih) as varchar)+'/'+cast(Year(Tarih) as varchar)+'</td><td>' + UrunKodu + '</td><td>' + UrunAdi + '</td><td>' + Barkod + '</td><td>' + cast( Miktar as varchar(30) ) + '</td><td>' + cast( SatirNetTutar as varchar(30) )
from (
select top 100 percent
Tarih= (select SF.DATE FROM StockFiches SF WHERE SF.Id=SL.StockFicheId),
UrunKodu= (select P.Code FROM Products P WHERE P.Id=SL.StockId),
UrunAdi= (select P.ProductName FROM Products P WHERE P.Id=SL.StockId),
Barkod= (select P.Barcode FROM Barcodes P WHERE P.ProductId=SL.StockId),
Miktar= SL.Amount,
SatirNetTutar=SL.LineNet
from StockLines SL
) as d order by Tarih desc
for xml path( 'tr' ), type ) as varchar(max) )
set @bodyDetay = N'Malzeme Bazlı Satışlar ('+cast(GETDATE() as varchar)+') <table cellpadding="2" cellspacing="0" border="1">'
+ '<tr><th>Tarih</th><th>Ürün Kodu</th><th>Ürün Adı</th><th>Barkod</th><th>Miktar</th><th>Tutar</th></tr>'
+ replace( replace( @bodyDetay, '<', '<' ), '>', '>' )
+ '</table>'
/*aşağısı özet*/
set @bodyOzet = cast( (
select td = cast(day(Tarih) as varchar)+'/'+cast(month(Tarih) as varchar)+'/'+cast(Year(Tarih) as varchar)+'</td><td>' + OdemeTuru + '</td><td>' + Cast(Toplam as Varchar) + '</td><td>' + Cast(IndirimToplam as Varchar) + '</td><td>' + cast( NetToplam as varchar(30) )
from (
select Tarih,OdemeTuru,Sum(Toplam) Toplam, Sum(IndirimToplam) IndirimToplam,Sum(NetToplam) NetToplam from (
SELECT
CONVERT(DATE, F.CreatedDate) AS Tarih
, CASE F.IO WHEN 0 THEN L.Total ELSE 0 - L.Total END AS Toplam
, CASE F.IO WHEN 0 THEN L.TotalDiscount ELSE 0 - L.TotalDiscount END AS IndirimToplam
, CASE F.IO WHEN 0 THEN L.LineNet ELSE 0 - L.LineNet END AS NetToplam
, L.VatInc KDVDH
, L.Vat KDV
, CASE F.PayType
WHEN 0 THEN 'Nakit Kasa'
WHEN 1 THEN 'Banka Pos'
WHEN 2 THEN 'Kredi Kartı'
WHEN 3 THEN 'İkram'
END OdemeTuru
FROM StockLines L
Left Join StockFiches F ON F.Id = L.StockFicheId
WHERE
F.FicheType in (0,1)
) as t group by Tarih,OdemeTuru
) as d order by Tarih desc
for xml path( 'tr' ), type ) as varchar(max) )
set @bodyOzet = N'Özet Bazlı Satışlar ('+cast(GETDATE() as varchar)+') <table cellpadding="2" cellspacing="0" border="1">'
+ '<tr><th>Tarih</th><th>Odeme Turu</th><th>Toplam</th><th>Indirim Toplam</th><th>NetToplam</th></tr>'
+ replace( replace( @bodyOzet, '<', '<' ), '>', '>' )
+ '</table><br>' + @bodyDetay
truncate table HTML
insert into HTML(HTMLCODE) VALUES(@bodyOzet)
GO
/*birinci yöntem logo ve sql server üzerinden:
hangi firma silinecekse dönemi silinir sonra firması silinir
örnek ilk önce 531li viewleri sil
sonrada tablolardan 531 sil
*/
/*İŞLEM ÖNCESİ VERİTABANININ YEDEK ALINMASI TAVSİYE EDİLİR*/
/*ÖNCE SYSDEN İLGİLİ FİRMANIN DÖNEMİ ÇIKARILIR VE SONRASINDA FİRMA ÇIKAR YAPILIR*/
/*AŞAĞIDAKİ İŞLEMLERE FİRMAYA BAĞLI VİEW,TABLO,PROSEDUR,FONKSIYONLARI SİLMEKTEDİR*/
DECLARE @firmno varchar(3)='999' /*İŞLEM YAPILMASI İSTENİLEN FİRMA NOSU YAZ*/
DECLARE @view_name SYSNAME
DECLARE @table_name SYSNAME
DECLARE @procedure_name SYSNAME
DECLARE @function_name SYSNAME
/*VIEWLERI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE 'LV_' + @firmno + '%' AND xtype='V' )
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @view_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP VIEW '+ @view_name)
FETCH NEXT FROM Logo_objs into @view_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*VIEWLERI SIL BİTİŞ*/
/*TABLOLARI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE 'LG_' + @firmno + '%' AND XTYPE = 'U')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP TABLE '+ @table_name)
FETCH NEXT FROM Logo_objs into @table_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*TABLOLARI SIL BİTİŞ*/
/*PROSEDURLERİ SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE '%' + @firmno + '%' AND XTYPE = 'P')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @procedure_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP PROCEDURE '+ @procedure_name)
FETCH NEXT FROM Logo_objs into @procedure_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*PROSEDURLERİ SIL BİTİŞ*/
/*FONKSİYONLARI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE (name like 'LG_' + @firmno + '%' AND type ='FN')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @function_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP FUNCTION '+ @function_name)
FETCH NEXT FROM Logo_objs into @function_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*FONKSİYONLARI SIL BİTİŞ*/
Sub FormShow()
AppTables[12].GetIntFieldR(RecHandle,"LOGICALREF", ORDFICHEREF) 'ORFICHE
'STR(ORDFICHEREF,ORDFICHEREFSTR) 'STRYE CEVRILMESSE WARNDA ACIKLAMA: EKLENEMEZ
'warn("ORDFICEHREF:"+ORDFICHEREFSTR)
'CustTables[3].GetIntField("MUHUR_DURUMU", MUHUR_DURUMU) 'ORFICHE
MUHUR_DURUMU=0
DbErr=0
CustTables[3].Search(2,2,ORDFICHEREF)
if DbErr=0 Then
CustTables[3].GetIntField("MUHUR_DURUMU",MUHUR_DURUMU)
end if
if MUHUR_DURUMU>0 Then
SetNumProp("chkMuhurDurumu", 1, MUHUR_DURUMU) 'SetNumProp(chckgroupboxname,itemid,1=checked/0=unchecked)
end if
'STR(MUHUR_DURUMU,MUHUR_DURUMUSTR) 'STRYE CEVRILMESSE WARNDA ACIKLAMA: EKLENEMEZ
'warn("MUHUR_DURUMU:"+MUHUR_DURUMUSTR)
End
Sub AfterSave()
tmp=0
AppTables[12].GetIntFieldR(RecHandle,"LOGICALREF", ORDFICHEREF) 'ORFICHE
GetNumProp("chkMuhurDurumu",1, tmp)
'warn(tmp)
'DB'ye Yazılıyor
DbErr=0
CustTables[3].Search(2,2,ORDFICHEREF)
if DbErr<>0 Then
CustTables[3].NewRecord()
CustTables[3].SetIntField("MUHUR_DURUMU",tmp)
End if
DBErr=0
CustTables[3].UpdateRecord()
if DBErr<>0 then
Warn(DBErr)
end if
End Sub
using System;
namespace SQL_HELPER_APP
{
public partial class SQLTABLESCRIPT : DevExpress.XtraEditors.XtraForm
{
public SQLTABLESCRIPT()
{
InitializeComponent();
}
private void simpleButton1_Click(object sender, EventArgs e)
{
memoEdit1.Text = "";
string IMAGEFIELD = "0";
memoEdit1.Text += "CREATE TABLE [dbo].[" + textEdit1.Text + "](" + Environment.NewLine;
memoEdit1.Text += "[" + textEdit2.Text + "] [INT] IDENTITY(1,1) NOT NULL," + Environment.NewLine;
for (int i = 0; i < gridView1.RowCount; i++)
{
string NAME = "[" + gridView1.GetRowCellValue(i, "NAME").ToString() + "]";
string TYPE = "[" + gridView1.GetRowCellValue(i, "TYPE").ToString() + "]";
string LENGTH = gridView1.GetRowCellValue(i, "LENGTH").ToString();
string ISNULL = gridView1.GetRowCellValue(i, "ISNULL").ToString();
ISNULL = ISNULL == "True" ? " NULL," : ",";
if (IMAGEFIELD == "0")
{
IMAGEFIELD = TYPE == "[Image]" ? "1" : "0";
}
switch (TYPE)
{
case "[Datetimeoffset]": TYPE = "[DATETIMEOFFSET](7)"; break;
case "[Decimal]": TYPE = "[DECIMAL](18, 0)"; break;
case "[Nchar]": TYPE = "[NCHAR](10)"; break;
case "[Numeric]": TYPE = "[NUMERIC](18, 0)"; break;
case "[Nvarchar]": TYPE = "[NVARCHAR](50)"; break;
case "[Nvarchar(max)]": TYPE = "[NVARCHAR](MAX)"; break;
case "[Time]": TYPE = "[TIME](7)"; break;
case "[Varbinary]": TYPE = "[VARBINARY](50)"; break;
case "[Varbinary(max)]": TYPE = "[VARBINARY](MAX)"; break;
case "[Varchar]": TYPE = "[VARCHAR](255)"; break;
case "[Varchar(max)]": TYPE = "[VARCHAR](MAX)"; break;
default: break;
}
memoEdit1.Text += NAME + " " + TYPE + ISNULL;
memoEdit1.Text += Environment.NewLine;
}
memoEdit1.Text += "CONSTRAINT [PK_" + textEdit1.Text + "] PRIMARY KEY CLUSTERED " + Environment.NewLine;
memoEdit1.Text += "(" + Environment.NewLine;
memoEdit1.Text += "[" + textEdit2.Text + "] ASC" + Environment.NewLine;
memoEdit1.Text += ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" + Environment.NewLine;
memoEdit1.Text += IMAGEFIELD == "1" ? ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]" + Environment.NewLine : ") ON [PRIMARY] " + Environment.NewLine;
}
private void repositoryItemButtonEdit1_Click(object sender, EventArgs e)
{
gridView1.DeleteSelectedRows();
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SQL_HELPER_APP
{
public class FIELDS
{
public string NAME { get; set; } = string.Empty;
public string TYPE { get; set; } = "Varchar";
public string LENGTH { get; set; } = "255";
public bool ISNULL { get; set; } =true;
}
}
this.repositoryItemComboBoxTYPE.Items.AddRange(new object[] {
"Bigint",
"Binary",
"Bit",
"Char",
"Cursor",
"Date",
"Datetime",
"Datetime2",
"Datetimeoffset",
"Decimal",
"Float",
"Image",
"Int",
"Money",
"Nchar",
"Ntext",
"Numeric",
"Nvarchar",
"Nvarchar(max)",
"Real",
"Smalldatetime",
"Smallint",
"Smallmoney",
"Sql_variant",
"Text",
"Time",
"Timestamp",
"Tinyint",
"Uniqueidentifier",
"Varbinary",
"Varbinary(max)",
"Varchar",
"Varchar(max)",
"Xml"});
this.repositoryItemComboBoxTYPE.Name = "repositoryItemComboBoxTYPE";
--log dosyasını bul
SELECT name FROM sys.master_files WHERE type_desc = 'LOG' and physical_name like '%Jam%'
ALTER DATABASE DevJamieS
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (DevJamieS_log, 1)
GO
ALTER DATABASE DevJamieS
SET RECOVERY FULL
------------
USE LKSDB;
GO
ALTER DATABASE LKSDB
SET RECOVERY SIMPLE;
GO
--datadakı log dosyasını shrınk yap
ALTER DATABASE LKSDB
SET RECOVERY FULL;
GO
EXEC sp_msforeachdb ' Declare @logname varchar(500) = '''';
IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'')
begin
set @logname = (Select name From [?].Sys.database_files where type=1)
EXEC(''Use [?];
ALTER DATABASE ?
SET RECOVERY SIMPLE;
DBCC SHRINKFILE (['' + @logname + ''] ,1)
ALTER DATABASE ?
SET RECOVERY FULL
'')
end
'
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ADI)
FROM ZV_LOGO_MALZEME GROUP BY ADI
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT KODU,' + @cols + ' from
(
select KODU,ADI
from ZV_LOGO_MALZEME
) x
pivot
(
COUNT(ADI)
for ADI in (' + @cols + ')
) p
'
execute(@query)
--------------
İKİ FARKLI TABLODAN
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(DEFINITION_)
FROM LG_SLSMAN GROUP BY DEFINITION_
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT STGRPCODE,' + @cols + ' from
(
select LG_001_ITEMS.STGRPCODE,DEFINITION_
from LG_SLSMAN
CROSS JOIN LG_001_ITEMS
) x
pivot
(
MIN(DEFINITION_)
for DEFINITION_ in (' + @cols + ')
) p
'
execute(@query)
// MessageBox.Show(Text_malzeme_grubu.EditValue.ToString());
// string connectionString = null;
SqlConnection connectionString = new SqlConnection("Server = " + ondegerler.SQLBAGLANTISI_KAYNAK + "; Database = " + ondegerler.SQLBAGLANTISI_VERITABANI + "; User Id = " + ondegerler.SQLBAGLANTISI_KULLANICI + "; Password = " + ondegerler.SQLBAGLANTISI_PAROLA);
connectionString.Open();
FileInfo file = new FileInfo(Application.StartupPath + "\\tbl_cari_kartlari_duzenle_kaydet.txt");
string duzenle = file.OpenText().ReadToEnd();
//MessageBox.Show(silinecekkod.ToString());
SqlCommand sqlcom = new SqlCommand(duzenle, connectionString);
sqlcom.Parameters.AddWithValue("@cari_turu", Text_cari_turu.Text.Trim());
sqlcom.Parameters.AddWithValue("@cari_adi", Text_cari_adi.Text.Trim());
sqlcom.Parameters.AddWithValue("@irtibat", Text_irtibat.Text.Trim());
sqlcom.Parameters.AddWithValue("@adres", Text_adres.Text.Trim());
sqlcom.Parameters.AddWithValue("@paremetre", text_paremetre.Text.Trim());
sqlcom.Parameters.AddWithValue("@cari_kodu", Text_cari_kodu.Text.Trim());
sqlcom.ExecuteNonQuery();
connectionString.Close();
MessageBox.Show("Cari Başarıyla Düzenlendi!", "CARİ KARTI DÜZENLEME", MessageBoxButtons.OK, MessageBoxIcon.Information);
/*txt file =*/
update tbl_cariler
set cari_turu=@cari_turu , cari_adi = @cari_adi ,irtibat=@irtibat , adres = @adres, paremetre = @paremetre
where cari_kodu = @cari_kodu
Blog of Sem 