C# BAŞKA FORMDAN BİLGİ ALMAK
private void SICILLIST()
{
using (SICILLERBORDRO siciller = new SICILLERBORDRO(lbl_FIRMNR.Text))
{
if (siciller.ShowDialog() == DialogResult.OK)
{
be_SICILNO.EditValue = siciller.VALUES[0].ToString();
be_ADSOYAD.EditValue = siciller.VALUES[1].ToString();
}
}
}
private void be_SICILNO_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
{
SICILLIST();
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Data.SqlClient;
namespace BMS_INSAAT_MODULU.FORM
{
public partial class SICILLERBORDRO : DevExpress.XtraEditors.XtraForm
{
public object[] VALUES { get; set; }
public SICILLERBORDRO(string FIRMANR)
{
InitializeComponent();
BMS_DLL.CFGGETSET.AYARLARIYUKLE();
gridControl1.DataSource = BMS_DLL.SQL.SELECT2("select LOGICALREF ID, CODE SICILNO,NAME+' '+SURNAME ADSOYAD from BM_PERSON WHERE FIRMNR=" + FIRMANR, new SqlConnection(string.Format(@"Server={0}; Database={1}; User Id ={2};Password ={3}", BMS_DLL.CFGICERIK.BMSDBSERVER, BMS_DLL.CFGICERIK.BMSDBDATABASE, BMS_DLL.CFGICERIK.BMSDBUSERNAME, BMS_DLL.CFGICERIK.BMSDBPASSWORD)));
}
private object[] GET_SELECTED_ITEMS(int ROWHANLE)
{
try
{
return new object[] { ((string)this.gridView1.GetRowCellValue(ROWHANLE, "SICILNO")), ((string)this.gridView1.GetRowCellValue(ROWHANLE, "ADSOYAD")) };
}
catch
{
return null;
}
}
private void gridView1_DoubleClick(object sender, EventArgs e)
{
this.VALUES = this.GET_SELECTED_ITEMS(this.gridView1.FocusedRowHandle);
if (this.VALUES != null)
{
base.DialogResult = DialogResult.OK;
base.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
BMS_DLL.DX.DXGRIDEXCELEKAYDET(gridView1, true);
}
}
}
logo tanımlı alanlı satış sipariş fişi raporu
SELECT T.*,
(SELECT CODE FROM LG_419_ITEMS I WHERE I.LOGICALREF=ORL.STOCKREF) MALZEMEHIZMETKODU
,(SELECT NAME FROM LG_419_ITEMS I WHERE I.LOGICALREF=ORL.STOCKREF) MALZEMEHIZMETADI, TEXTFLDS1 D_ARACPLAKASI,
(select CATDESC from LG_CATEGLISTS WHERE CATEGID=10853 AND TAG= numflds3) D_KULLANIMTIPI,
(select CATDESC from LG_CATEGLISTS WHERE CATEGID=10855 AND TAG= numflds5) D_ARACMODELI,
TEXTFLDS5 D_MOTORNO,
TEXTFLDS9 D_MENSEULKE,
(select CATDESC from LG_CATEGLISTS WHERE CATEGID=10861 AND TAG= numflds11) D_RENK,
(select CATDESC from LG_CATEGLISTS WHERE CATEGID=10863 AND TAG= numflds13) D_YAKITTURU,
(select CATDESC from LG_CATEGLISTS WHERE CATEGID=10865 AND TAG= numflds15) D_VITESTURU,
(select CATDESC from LG_CATEGLISTS WHERE CATEGID=10852 AND TAG= numflds2) D_ARACTIPI,
(select CATDESC from LG_CATEGLISTS WHERE CATEGID=10854 AND TAG= numflds4) D_ARACMARKASI,
TEXTFLDS6 D_SASENO,TEXTFLDS8 D_ARACSAHIBI,TEXTFLDS10 D_URETIMYILI,TEXTFLDS12 D_RENKKODU,
(select CATDESC from LG_CATEGLISTS WHERE CATEGID=10864 AND TAG= numflds14) D_DIREKSIYON,
TEXTFLDS16 D_MOTORGUCU
FROM (
SELECT
ORF.LOGICALREF,
(SELECT CODE FROM LG_419_CLCARD CLC WHERE CLC.LOGICALREF=ORF.CLIENTREF) CARIHESAPKODU,
(SELECT DEFINITION_ FROM LG_419_CLCARD CLC WHERE CLC.LOGICALREF=ORF.CLIENTREF) CARIHESAPADI,
ORF.FICHENO FISNO,
ORF.DATE_ FISTARIHI,
DF.TEXTFLDS1 DOSYASIGORTAB, DF.TEXTFLDS3 SIGORTASIRKETI, DF.TEXTFLDS5 ARAC, DF.NUMFLDS7 SIGORTALI_K,DF.TEXTFLDS9 POLICENO,0 POLICEBITISTARIHI,DF.TEXTFLDS13 HASARYERI,DF.TEXTFLDS15 EKPERTIZNO,DF.TEXTFLDS17 EXPERTIZYERI,
(select CATDESC from LG_CATEGLISTS WHERE CATEGID=10819 AND TAG= numflds19) BOLGE
,DF.TEXTFLDS2 MAGDURARACBILGISI,DF.TEXTFLDS5 ARACSURUCUSU,DF.TEXTFLDS6 SIGORTALI, NUMFLDS7 SIGORTABEDELI,
CASE WHEN NUMFLDS10>0 THEN DBO.LG_INTTODATE(NUMFLDS10) ELSE 0 END POLICEBASTARIH,
CASE WHEN NUMFLDS11>0 THEN DBO.LG_INTTODATE(NUMFLDS11) ELSE 0 END POLICEBITTARIH,
CASE WHEN NUMFLDS12>0 THEN DBO.LG_INTTODATE(NUMFLDS12) ELSE 0 END HASARTARIHI,
CASE WHEN NUMFLDS14>0 THEN DBO.LG_INTTODATE(NUMFLDS14) ELSE 0 END EKSPTALEPTARIHI,
CASE WHEN NUMFLDS16>0 THEN DBO.LG_INTTODATE(NUMFLDS16) ELSE 0 END EKSPERTIZTARIH,
(select CATDESC from LG_CATEGLISTS WHERE CATEGID=10818 AND TAG= numflds18) POLISRAPORU,
(select CATDESC from LG_CATEGLISTS WHERE CATEGID=10820 AND TAG= numflds20) ALKOLDURUMU
FROM LG_419_01_ORFICHE ORF
LEFT JOIN LG_419_01_DEFNFLDSTRANV DF ON DF.PARENTREF=ORF.LOGICALREF AND LEVEL_=0
WHERE ORF.TRCODE=1
) AS T
LEFT JOIN LG_419_01_DEFNFLDSTRANV DF ON DF.PARENTREF=T.LOGICALREF AND LEVEL_=1
LEFT JOIN LG_419_01_ORFLINE ORL ON ORL.ORDFICHEREF=T.LOGICALREF
--select DISTINCT CAPTION,CATEGID from L_DEFNFLDSD D
--left JOIN LG_CATEGLISTS C ON CONVERT(VARCHAR,C.CATEGID)='108'+CONVERT(VARCHAR,NR) where D.TYP=3
--order by CAPTION,CATDESC
LOGO SQL INT TO DATE
CREATE FUNCTION [dbo].[LG_INTTODATE](@LDATE INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @DATE DATETIME,
@DD INT,
@MM INT,
@YYYY INT,
@DATESTR VARCHAR(12)
SELECT @DD = ((@LDATE%65536)/256)
SELECT @MM = (@LDATE%65536)%256
SELECT @YYYY = (@LDATE/65536)
SELECT @DATESTR = CAST(@DD AS VARCHAR(2))+'-'+
CAST(@MM AS VARCHAR(2))+'-'+
CAST(@YYYY AS VARCHAR(4))
SELECT @DATE = CONVERT(DATETIME,@DATESTR,102)
RETURN(@DATE)
END
-- SELECT DBO.LG_INTTODATE(190054657)
--RESULT 2900-01-01 00:00:00.000
RESTORE FROM BAK WITH QUERY SQL SERVER
USE [master]
RESTORE DATABASE [MYDATA]
FROM DISK = N'l.bak'
WITH FILE = 1,
MOVE N'LEVENT_Data' TO N'F:\LOGO\DB\LEVENT\LEVENT_Data.mdf',
MOVE N'LEVENT_Log' TO N'F:\LOGO\DB\LEVENT\LEVENT_Log.ldf',
NOUNLOAD, REPLACE, STATS = 1
GO
RESTORE FILELISTONLY FROM DISK = 'serkan.BAK' WITH FILE = 1
LOGO ERP KATEGORI BAZLI KARLILIK
--CREATE VIEW [dbo].[KATEGORI_KARLILIK_318_2] AS
SELECT SOURCEINDEX,
(SELECT NAME FROM L_CAPIWHOUSE W WHERE W.FIRMNR=318 AND W.NR =SOURCEINDEX ) SOURCEINDEXNAME,
T.ANAGRUP ANAGRUPKOD,
ANAGRUPACIKLAMA,
AYLAR,YIL,SUM(MIKTAR) MIKTAR,SUM(TUTAR) TUTAR ,SUM(COST) COST, SUM(TUTAR)-SUM(COST) KAR, ISNULL(SUM(KAR)/NULLIF(SUM(COST),0),0) ORAN from (
SELECT TOP (100) PERCENT TRCODE, SOURCEINDEX,
left((SELECT SPECODE NAME FROM LG_318_ITEMS WHERE LOGICALREF=LG_318_01_STLINE.STOCKREF),2) ANAGRUP,
(SELECT DEFINITION_ FROM LG_318_SPECODES S WHERE S.SPECODE=left((SELECT SPECODE NAME FROM LG_318_ITEMS WHERE LOGICALREF=LG_318_01_STLINE.STOCKREF),2)AND CODETYPE=1 AND SPECODETYPE=1 ) ANAGRUPACIKLAMA,
--(SELECT TOP 1 DEFINITION_ FROM LG_318_SPECODES S WHERE '1'=left((SELECT CAST(SPECODE AS VARCHAR) FROM LG_318_ITEMS WHERE LOGICALREF=LG_318_01_STLINE.STOCKREF),2) AND CODETYPE=1 AND SPECODE=1 ) ANAGRUPACIKLAMA,
--(SELECT CODE + ' - ' + NAME FROM LG_318_ITEMS WHERE LOGICALREF=LG_318_01_STLINE.STOCKREF) AS MALZEME,
MONTH(DATE_) AS AYLAR, YEAR(DATE_) AS YIL,
CASE WHEN TRCODE IN (7,8) THEN SUM(AMOUNT) ELSE SUM(AMOUNT)*-1 END AS MIKTAR,
CASE WHEN TRCODE IN (7,8) THEN SUM(VATMATRAH) ELSE SUM(VATMATRAH)*-1 END AS TUTAR,
CASE WHEN TRCODE IN (7,8) THEN SUM(OUTCOST * AMOUNT) ELSE SUM(OUTCOST * AMOUNT)*-1 END AS COST,
CASE WHEN TRCODE IN (7,8) THEN (SUM(VATMATRAH) - SUM(OUTCOST * AMOUNT)) ELSE (SUM(VATMATRAH) - SUM(OUTCOST * AMOUNT)) *-1 END AS KAR
FROM dbo.LG_318_01_STLINE
WHERE (TRCODE IN (7, 8,2,3)) AND (LINETYPE=0) AND (CANCELLED = 0) AND YEAR(DATE_)=2018
GROUP BY TRCODE, STOCKREF, MONTH(DATE_), YEAR(DATE_), SOURCEINDEX
) AS T WHERE ANAGRUP<>'KD'
--ORDER BY AYLAR
GROUP BY SOURCEINDEX,T.ANAGRUP, ANAGRUPACIKLAMA,
AYLAR,YIL
GO
C# SQL TABLE SCRIPT GENERATOR
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";
Object Realtime Aktiflik
Kullanımda olan Logo Objects’lere aşağıdaki sorgu ile erişebilirsiniz.
select count(distinct program_name) from master.dbo.sysprocesses where program_name like 'LOGO_ERPOBJECT%'
DEVEXPRESS RUNTIME HIDDEN EXPORT TO XLS
private void simpleButton1_Click_1(object sender, EventArgs e)
{
//XLWorkbook wb = new XLWorkbook();
//DataTable dt = BMS_DLL.SQL.SELECT("select * from BMS_MS_RECORDS");
//wb.Worksheets.Add(dt, "WorksheetName");
//wb.SaveAs(@"HelloWorld.xls");
DataTableToExcel(BMS_DLL.SQL.SELECT("select * from BMS_MS_RECORDS"), "HelloWorld.xls");
}
public void DataTableToExcel(DataTable dt, string PATH)
{
GridControl GC = new GridControl();
GridView GV = new GridView();
GC.ViewCollection.Add(GV);
GC.MainView = GV;
GC.BindingContext = new BindingContext();
GC.DataSource = dt;
GV.PopulateColumns();
GC.ForceInitialize();
GV.ExportToXls(PATH);
}
Blog of Sem 