Tag: LOGO ERP

LOGO OBJECT İLE SORGU İŞLEMİ

On By semt20In LOGO ERP, LOGO OBJECTSLeave a comment

TIGER 3 ENTERPRISE

NEDEN İHTİYAÇ DUYULDU: LOGO SYSDE FİRMALARA FARKLI VERİTABANLARI ÜZERİNDEN İŞLEM YAPILABİLMEKTE. DOLAYISIYLA İLGİLİ VERİTABANINDAN OBJECT İLE SORGU KONTROLÜ YAPILIP ONA GÖRE DEVAM EDİLMESİ GEREKMEKTE(PROJE TABLOSU).

ÖNCE SORGUYU CEKMEK İSDEDİGİM TABLO İSMİ TESPİT EDİLİR-GetTableName(https://docs.logo.com.tr/public/wua/logo-objects/logo-objects-kuetuephanesi/unityapplication/gettablename)

string projectTable = AppUnity.GetTableName(229, firmNr, 0);
bool isProjectExists = ObjectProjectCodeControl(projectTable, L.PROJEKODU);
bool canContinue = true;
                        if (!isProjectExists)
                        {
                            canContinue = false;
                            WRITELOG(firmNr.ToString() + " PROJE KODU BULUNAMADI : " + L.PROJEKODU, null);
                        }
private static bool ObjectProjectCodeControl(string table, string code)
{
    bool isExists = false;
    string sql = "SELECT LOGICALREF FROM " + table + " WHERE CODE = '" + code + "'";
    string resultTxt = "";
    UnityObjects.Query Qry = AppUnity.NewQuery();
    Qry.Statement = sql;
    if (Qry.OpenDirect())
    {
        bool res = Qry.First();
        while (res)
        {
            resultTxt = Qry.QueryFields[0].Value.ToString();
            isExists = true;
            res = Qry.Next();
        }
    }
    else
    {
        MessageBox.Show(Qry.DBErrorDesc.ToString());
    }
    Qry.Close();
    return isExists;
}

LOGO Banka İşlem Fişi Kesilmiştir Detay

On By semt20In LOGO ERP, LOGO OBJECTS, SqlLeave a comment

Logoda kk fişi silerken banka işlem fişi kesilmiştir hatası veren fişlerin bağlarını bulmak için : 

 SELECT * FROM (
 SELECT 
	DISTINCT
	Tarih,
	CariFisKKFisNo=(SELECT CLFICHE.FICHENO FROM LG_301_01_CLFICHE CLFICHE WHERE CLFICHE.LOGICALREF=CariFisKKFisRef),
	CariFisMuhasebeFisNo=(SELECT EMFICHE.FICHENO FROM LG_301_01_EMFICHE EMFICHE WHERE EMFICHE.LOGICALREF=CariFisMuhasebeFisRef),
	BankaFisNo,
	BankaFisMuhasebeFisNo=(SELECT EMFICHE.FICHENO FROM LG_301_01_EMFICHE EMFICHE WHERE EMFICHE.LOGICALREF=BankaFisMuhasebeFisRef)
 FROM (
	 SELECT 
	 Tarih=BNFICHE.DATE_,
	 CariFisKKFisRef=BNFLINE.CRCARDFCREF,
	 CariFisMuhasebeFisRef=(SELECT CLFICHE.ACCFICHEREF FROM LG_301_01_CLFICHE CLFICHE WHERE CLFICHE.LOGICALREF=(CASE WHEN ISNULL(BNFICHE.CRCARDFCREF,'')=0 THEN BNFLINE.CRCARDFCREF ELSE BNFICHE.CRCARDFCREF END)),
	 BankaFisNo=BNFICHE.FICHENO,
	 BankaFisMuhasebeFisRef=(SELECT EMFICHE.LOGICALREF FROM LG_301_01_EMFICHE EMFICHE WHERE EMFICHE.LOGICALREF=BNFICHE.ACCFICHEREF)
	 FROM 
		LG_301_01_BNFICHE BNFICHE LEFT JOIN LG_301_01_BNFLINE BNFLINE ON BNFLINE.SOURCEFREF=BNFICHE.LOGICALREF
	  WHERE BNFICHE.TRCODE=1 AND BNFICHE.MODULENR=7
  ) AS TT
  ) AS TF WHERE CariFisKKFisNo='BMS.0073921'

LOGO OBJECTS INVOICE ITEXT

On By semt20In C SHARP, LOGO OBJECTSLeave a comment

Logo objectse invoice fatura atarken ITEXT her 255 karaktere ¦ konması gerekmektedir .

İlgili fonksiyon: 

    public static string SplitString(string input)
        {
            int chunkSize = 255;
            List<string> chunks = new List<string>();
            for (int i = 0; i < input.Length; i += chunkSize)
            {
                int remaining = Math.Min(chunkSize, input.Length - i);
                chunks.Add(input.Substring(i, remaining));
            }
            return string.Join("¦", chunks);
        }

            F.DataFields.FieldByName("ITEXT").Value = SplitString(description);

LOGO YANSITMA ŞABLONU QUERYISI

On By semt20In LOGO ERP, LOGO OBJECTS1 Comment 
DECLARE @GIDER_BASL_HESAP_KODU VARCHAR(50), @GIDER_BITIS_HESAP_KODU VARCHAR(50), @GIDER_HESAP_KODU VARCHAR(50), @GIDER_YANSITMA_HESAP_KODU VARCHAR(50), @GELIR_YANSITMA_HESAP_KODU VARCHAR(50), @TARIH_BASL VARCHAR(50), @TARIH_BITIS VARCHAR(50), @FIRMNR VARCHAR(3),
@ACIKLAMA_YANSITMA1  VARCHAR(150),@ACIKLAMA_YANSITMA2  VARCHAR(150),@ACIKLAMA_GELIRLERIKAPAT   VARCHAR(150),@ACIKLAMA_GIDERLERIKAPAT    VARCHAR(150)
SET @FIRMNR='212';
SET @GIDER_HESAP_KODU = '690.01.01.000'
SET @GIDER_BASL_HESAP_KODU='720.01.01.001'
SET @GIDER_BITIS_HESAP_KODU='720.01.01.002'
SET @GIDER_YANSITMA_HESAP_KODU = '721.01.01.001'
SET @GELIR_YANSITMA_HESAP_KODU = '620.01.01.001'
SET @TARIH_BASL= '1-1-2018'
SET @TARIH_BITIS='12-31-2018'
SET @ACIKLAMA_YANSITMA1='24.10.2019 tarihli dönem içi yansıtma kaydı';
SET @ACIKLAMA_YANSITMA2='24.10.2019 tarihli dönem sonu yansıtma kaydı';
SET @ACIKLAMA_GELIRLERIKAPAT='24.10.2019 Tarihli Gelir Kapatma Kaydı';
SET @ACIKLAMA_GIDERLERIKAPAT='24.10.2019 Tarihli Gider Kapatma Kaydı';
--YANSITMA1
SELECT * FROM (
SELECT *
FROM
(
    SELECT '1-YANSITMA1' TUR,
           @GELIR_YANSITMA_HESAP_KODU BORC_HESAP,
@GIDER_YANSITMA_HESAP_KODU  ALACAK_HESAP,@ACIKLAMA_YANSITMA1 ACIKLAMA,
           BRANCH ISYERINR,
           (
               SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
           ) ISYERI,
           GLTRN.TRCURR,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN))) REPORTNET,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) TRNET_TUTAR,   CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) SON
    FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
    WHERE (GLTRN.DATE_ >= CONVERT(DATETIME, @TARIH_BASL, 101))
          AND (GLTRN.DATE_ <= CONVERT(DATETIME, @TARIH_BITIS, 101))
          AND (GLTRN.CANCELLED = 0)
          AND (GLTRN.TRCODE NOT IN ( 5 ))
          AND --
        ACCOUNTCODE
          BETWEEN @GIDER_BASL_HESAP_KODU AND @GIDER_BITIS_HESAP_KODU
    GROUP BY GLTRN.TRCURR,
             BRANCH
) AS T
WHERE T.TRNET_TUTAR 0
--YANSITMA2
UNION ALL
SELECT *
FROM
(
    SELECT '2-YANSITMA2' TUR,
            @GIDER_YANSITMA_HESAP_KODU BORC,
           ACCOUNTCODE ALACAK,@ACIKLAMA_YANSITMA2 ACIKLAMA,
           BRANCH ISYERINR,
           (
               SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
           ) ISYERI,
           GLTRN.TRCURR,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN))) REPORTNET,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) TRNET_TUTAR,   CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) SON
    FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
    WHERE (GLTRN.DATE_ >= CONVERT(DATETIME,@TARIH_BASL, 101))
          AND (GLTRN.DATE_ <= CONVERT(DATETIME,@TARIH_BITIS, 101))
          AND (GLTRN.CANCELLED = 0)
          AND (GLTRN.TRCODE NOT IN ( 5 ))
          AND --
        ACCOUNTCODE
         BETWEEN @GIDER_BASL_HESAP_KODU AND @GIDER_BITIS_HESAP_KODU
    GROUP BY GLTRN.TRCURR,ACCOUNTCODE,BRANCH
) AS T
WHERE T.TRNET_TUTAR 0
--GELİRLERİ KAPAT
UNION ALL
SELECT  * from (
SELECT *
FROM
(
    SELECT '3-GELİRLERİ KAPAT' TUR,
            @GIDER_HESAP_KODU BORC,
           ACCOUNTCODE ALACAK,@ACIKLAMA_GELIRLERIKAPAT ACIKLAMA,
           BRANCH ISYERINR,
           (
               SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
           ) ISYERI,
           GLTRN.TRCURR,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN))) REPORTNET,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) TRNET_TUTAR,
    CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT))- CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) SON
    FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
    WHERE (GLTRN.DATE_ >= CONVERT(DATETIME,@TARIH_BASL, 101))
          AND (GLTRN.DATE_ <= CONVERT(DATETIME, @TARIH_BITIS, 101))
          AND (GLTRN.CANCELLED = 0)
          AND (GLTRN.TRCODE NOT IN ( 5 ))
          AND
    (ACCOUNTCODE LIKE  LEFT(@GIDER_HESAP_KODU,1) +'%' AND ACCOUNTCODE<@GIDER_HESAP_KODU)
    GROUP BY ACCOUNTCODE,GLTRN.TRCURR,BRANCH
) AS T
WHERE
 (DEBIT-CREDIT>0)  ) AS T2    WHERE SON>0
--GIDERLERİ KAPAT
UNION ALL
SELECT  * from ( 
SELECT *
FROM
(
    SELECT '4-GIDERLERİ KAPAT' TUR,
            ACCOUNTCODE   BORC,
           @GIDER_HESAP_KODU  ALACAK,@ACIKLAMA_GIDERLERIKAPAT ACIKLAMA,
           BRANCH ISYERINR,
           (
               SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
           ) ISYERI,
           GLTRN.TRCURR,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
           ABS(CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN)))) REPORTNET,
           ABS(CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN)))) TRNET_TUTAR,
   CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT))- CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) SON
    FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
    WHERE (GLTRN.DATE_ >= CONVERT(DATETIME, @TARIH_BASL, 101))
          AND (GLTRN.DATE_ <= CONVERT(DATETIME,@TARIH_BITIS, 101))
          AND (GLTRN.CANCELLED = 0)
          AND (GLTRN.TRCODE NOT IN ( 5 ))
          AND --
          (ACCOUNTCODE LIKE  LEFT(@GIDER_HESAP_KODU,1) +'%' AND ACCOUNTCODE<@GIDER_HESAP_KODU)
    GROUP BY ACCOUNTCODE,GLTRN.TRCURR,BRANCH
) AS T
WHERE
T.CREDIT-T.DEBIT>0   ) AS T2   
 ) AS YANSITMASABLONU ORDER BY TUR,YANSITMASABLONU.ISYERINR,YANSITMASABLONU.TRCURR