AMBAR – Blog of Sem

Amaç: Logoda cari hesap kartında ilgili carilere işyeri ambarlar set edilip faturada cari seçerken otomatik olarak set edilmiş bilgileri almak.

Tiger ent version 2.95

' ///////////////////////////////////////////
' LOD Source Code Text File
' Date :2.12.2024 09:19:21
' ///////////////////////////////////////////

' ///////////////////////////////////////////
' File Name   : FRM_CariHesapKarti
' Name : FRM_CariHesapKarti
' Code Type : Event
' ///////////////////////////////////////////
Sub FormShow()
HideControl("_N_Isyeri_Nr")
HideControl("_N_Ambar_Nr")
HideControl("_T_Isyeri_Name")
HideControl("_T_Ambar_Name")

arrIsyeri = Application.GetFirmDivisions(Application.CompanyId) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-application
 for i = 0 to arrIsyeri.Size-1
 _dNr = arrIsyeri[i].Nr
 _dName = arrIsyeri[i].Name
 AddItem("_C_Isyeri",_dName,_dNr) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-form
 next
getIsyeriName()


arrAmbar = Application.GetFirmWarehouses(Application.CompanyId, 0, 0) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-application
 for i = 0 to arrAmbar.Size-1
 _wNr = arrAmbar[i].Nr
 _wName = arrAmbar[i].Name
 AddItem("_C_Ambar",_wName,_wNr) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-form
 next
getAmbarName()


End Sub

Sub ComboChange(ctrl as String, ID as Integer, index as Integer)
IF ctrl="_C_Ambar" THEN
 GetTextVal("_C_Ambar",t_C_Ambar) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-form   
 SetIntVal("_N_Ambar_Nr",index)
 SetTextVal("_T_Ambar_Name",t_C_Ambar)
END IF

IF ctrl="_C_Isyeri" THEN
 GetTextVal("_C_Isyeri",t_C_Isyeri) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-form   
 SetIntVal("_N_Isyeri_Nr",index)
 SetTextVal("_T_Isyeri_Name",t_C_Isyeri)
END IF
End Sub


' ///////////////////////////////////////////
' File Name   : FRM_CariHesapKarti
' Name : FRM_CariHesapKarti
' Code Type : Module
' ///////////////////////////////////////////
Sub getIsyeriName()
 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-baslangic
 CreateQuery(QryDName)
 GetIntVal("_N_Isyeri_Nr",v_N_Isyeri_Nr)
 SetIntVal("_C_Isyeri",v_N_Isyeri_Nr)
 STR(v_N_Isyeri_Nr,STR_v_N_Isyeri_Nr)
 STR(Application.CompanyId,STR_ApplicationCompanyId)
 SqlDName = "select TOP 1 NAME from L_CAPIDIV WHERE FIRMNR="+STR_ApplicationCompanyId+ " AND NR="+STR_v_N_Isyeri_Nr
 QryDName.SetSQLText(SqlDName)
 FResult = ""
 If QryDName.ExecuteDirect() Then
  Res = QryDName.First()
  Do While Res=1 
   QryDName.GetFieldValue(1, 4, FResult) 
   Res=QryDName.Next()
  Loop 
 end if
 QryDName.Clear()
 SetTextVal("_T_Isyeri_Name",FResult)
End Sub

Sub getAmbarName()
 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-baslangic
 CreateQuery(QryWHName)
 GetIntVal("_N_Ambar_Nr",v_N_Ambar_Nr)    
 SetIntVal("_C_Ambar",v_N_Ambar_Nr)
 STR(v_N_Ambar_Nr,STR_v_N_Ambar_Nr)
 STR(Application.CompanyId,STR_ApplicationCompanyId)
 SqlWHName = "select TOP 1 NAME from L_CAPIWHOUSE WHERE FIRMNR="+STR_ApplicationCompanyId+ " AND NR="+STR_v_N_Ambar_Nr
 QryWHName.SetSQLText(SqlWHName)
 FResult = ""
 If QryWHName.ExecuteDirect() Then
  Res = QryWHName.First()
  Do While Res=1 
   QryWHName.GetFieldValue(1, 4, FResult) 
   Res=QryWHName.Next()
  Loop 
 end if
 QryWHName.Clear()
 SetTextVal("_T_Ambar_Name",FResult)
End Sub




' ///////////////////////////////////////////
' File Name   : FRM_SatisFaturasi
' Name : CUSTENTR2
' Code Type : Event
' ///////////////////////////////////////////
Sub FormShow()
 AppTables[14].GetIntFieldR(RecHandle,"LOGICALREF",_invoiceRef)
 isNewInvoiceFiche = true
    IF _invoiceRef > 0 THEN
        isNewInvoiceFiche = false
    END IF
End Sub

Sub ControlExit(ctrl as String, index as Integer)  
    IF ctrl = "clCodeEdit" OR ctrl = "TitleEdit" THEN       
      setIsyeriByClient()
      setAmbarByClient()
    END IF 
End Sub


' ///////////////////////////////////////////
' File Name   : FRM_SatisFaturasi
' Name : CUSTENTR2
' Code Type : Module
' ///////////////////////////////////////////
Sub setIsyeriByClient()
 AppTables[14].GetIntFieldR(RecHandle,"CLIENTREF",_clientRef)
 IF _clientRef=0 THEN
  EXIT SUB
 END IF
 STR(_clientRef,STR_clientRef)
 ClCardIsyeriRelationTable = Application.GetCustTableName(1, Application.CompanyId, Application.FiscPerdId) 'https://docs.logo.com.tr/public/wua/logo-objects/logo-objects-kuetuephanesi/unityapplication/getcusttablename
 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-baslangic
 CreateQuery(QryClCardIsyeriRelationTable)
 SqlClCardIsyeriRelationTable = "select TOP 1 _ISYERINR from "+ClCardIsyeriRelationTable+" WHERE PARLOGREF='"+STR_clientRef+"' "
 QryClCardIsyeriRelationTable.SetSQLText(SqlClCardIsyeriRelationTable)
 FResult = 0
 If QryClCardIsyeriRelationTable.ExecuteDirect() Then
  Res = QryClCardIsyeriRelationTable.First()
  Do While Res=1 
   QryClCardIsyeriRelationTable.GetFieldValue(1, 1, FResult) 
   Res=QryClCardIsyeriRelationTable.Next()
  Loop 
 end if
 QryClCardIsyeriRelationTable.Clear()
 if FResult > 0 then
  Confirmed("İşyeri Cari Bağlantısından Gelsinmi ? ", _ask)
  IF _ask = 1 THEN
   SetIntVal("branchComb",FResult)
  END IF
 END IF
 
 
End Sub

Sub setAmbarByClient()
 AppTables[14].GetIntFieldR(RecHandle,"CLIENTREF",_clientRef)
 IF _clientRef=0 THEN
  EXIT SUB
 END IF
 STR(_clientRef,STR_clientRef)
 ClCardAmbarRelationTable = Application.GetCustTableName(1, Application.CompanyId, Application.FiscPerdId) 'https://docs.logo.com.tr/public/wua/logo-objects/logo-objects-kuetuephanesi/unityapplication/getcusttablename
 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-baslangic
 CreateQuery(QryClCardAmbarRelationTable)
 SqlClCardAmbarRelationTable = "select TOP 1 _AMBARNR from "+ClCardAmbarRelationTable+" WHERE PARLOGREF='"+STR_clientRef+"' "
 QryClCardAmbarRelationTable.SetSQLText(SqlClCardAmbarRelationTable)
 FResult = 0
 If QryClCardAmbarRelationTable.ExecuteDirect() Then
  Res = QryClCardAmbarRelationTable.First()
  Do While Res=1 
   QryClCardAmbarRelationTable.GetFieldValue(1, 1, FResult) 
   Res=QryClCardAmbarRelationTable.Next()
  Loop 
 end if
 QryClCardAmbarRelationTable.Clear()
 if FResult > 0 then
  Confirmed("Ambar Cari Bağlantısından Gelsinmi ? ", _ask)
  IF _ask = 1 THEN
   SetIntVal("invenComb",FResult)

   MGridName = "InvoiceGrid"
   _satirSayisi = MGridLines(MGridName)
   For I=0 to _satirSayisi - 1
    GetGridRowHandle(MGridName,i,_recH) 
    SetCellInt(MGridName,I,40,FResult)
    InvalidateGrid(MGridName)
    RefreshControl(MGridName,1) 
   Next I
  END IF
 END IF
End Sub

DECLARE @columns NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)

SELECT @columns = STRING_AGG(QUOTENAME(NR), ',') 
FROM L_CAPIWHOUSE 
WHERE FIRMNR = 123
select @columns='[-1],'+@columns

SET @sql = '
SELECT LOGICALREF,
       CODE,
       DESCRIPTION,
       UNIT, ' + @columns + '
FROM (
    SELECT TF.LOGICALREF,
           TF.CODE,
           TF.NAME AS DESCRIPTION,
           US.NAME AS UNIT,
           WH.NR AS WAREHOUSE,
           ISNULL(SUM(ST.ONHAND), 0) AS STOCK
    FROM (
        SELECT DISTINCT LOGICALREF, CODE, NAME, UNITSETREF
        FROM LG_123_ITEMS
    ) AS TF
    --LEFT JOIN LG_123_ITMUNITA IUA ON IUA.ITEMREF = TF.LOGICALREF
    --LEFT JOIN LG_123_UNITSETL US ON US.LOGICALREF = IUA.UNITLINEREF
    LEFT JOIN LG_123_UNITSETL US ON US.UNITSETREF = TF.UNITSETREF AND US.MAINUNIT = 1
    CROSS JOIN (SELECT W.NR,W.FIRMNR FROM L_CAPIWHOUSE W UNION ALL SELECT -1 AS NR,123 AS FIRMNR) WH
    LEFT JOIN LV_123_01_STINVTOT ST ON ST.STOCKREF = TF.LOGICALREF AND ST.INVENNO = WH.NR
    WHERE WH.FIRMNR = 123
    GROUP BY TF.LOGICALREF, TF.CODE, TF.NAME, US.NAME, WH.NR
) AS SourceTable
PIVOT (
    MAX(STOCK)
    FOR WAREHOUSE IN (' + @columns + ')
) AS PivotTable
ORDER BY LOGICALREF;'

-- 3. Dinamik SQL'i çalıştırın.
EXEC sp_executesql @sql

Blog of Sem

Tag: AMBAR

LOD CARİ İŞYERİ AMBAR FATURA

LOGO SQL MALZEME STOKLARI

LOGO SQL FATURAYA BAGLI IRSALIYELERIN ESITLIK KONTROLLERI