vineri, 23 iulie 2010
CurosrAdapter
http://www.articlesbase.com/programming-articles/building-data-aware-applications-with-visual-foxpro-part-3-universal-data-access-2783573.html
Combo RowSourceType = 3 si RowSource = Procedura(Functie)
RowSourceType = 3
SowSource = GetGestiuneFactura()
FUNCTION GetGestiuneFactura
SELECT SPACE(0) AS DenGEST, SPACE(0) AS ContGEST, 0 AS IdGEST FROM Gestiuni
UNION SELECT Denumire AS DenGEST, Cont AS ContGEST, Id AS IdGEST FROM Gestiuni
INTO CURSOR cGestiuneFactura
RETURN [cGestiuneFactura]
SowSource = GetGestiuneFactura()
FUNCTION GetGestiuneFactura
SELECT SPACE(0) AS DenGEST, SPACE(0) AS ContGEST, 0 AS IdGEST FROM Gestiuni
UNION SELECT Denumire AS DenGEST, Cont AS ContGEST, Id AS IdGEST FROM Gestiuni
INTO CURSOR cGestiuneFactura
RETURN [cGestiuneFactura]
ReadWindowsScreenResolution
#DEFINE DRIVERVERSION 0
#DEFINE TECHNOLOGY 2
#DEFINE HORZSIZE 4
#DEFINE VERTSIZE 6
#DEFINE HORZRES 8
#DEFINE VERTRES 10
#DEFINE LOGPIXELSX 88
#DEFINE LOGPIXELSY 90
#DEFINE BITSPIXEL 12
#DEFINE PLANES 14
#DEFINE NUMBRUSHES 16
#DEFINE NUMPENS 18
#DEFINE NUMFONTS 22
#DEFINE NUMCOLORS 24
#DEFINE ASPECTX 40
#DEFINE ASPECTY 42
#DEFINE ASPECTXY 44
#DEFINE CLIPCAPS 36
#DEFINE SIZEPALETTE 104
#DEFINE NUMRESERVED 106
#DEFINE COLORRES 108
#DEFINE PHYSICALWIDTH 110
#DEFINE PHYSICALHEIGHT 111
#DEFINE PHYSICALOFFSETX 112
#DEFINE PHYSICALOFFSETY 113
#DEFINE SCALINGFACTORX 114
#DEFINE SCALINGFACTORY 115
#DEFINE RASTERCAPS 38
#DEFINE CURVECAPS 28
#DEFINE LINECAPS 30
#DEFINE POLYGONALCAPS 32
#DEFINE TEXTCAPS 34
DO decl
LOCAL hdc
hdc = GetDC(0) && entire screen
CREATE CURSOR csResult (name C(30), prm N(12))
= AddLog("Driver version", GetDeviceCaps(hdc, DRIVERVERSION))
= AddLog("Device technology", GetDeviceCaps(hdc, TECHNOLOGY))
= AddLog("Physical screen, width, mm", GetDeviceCaps(hdc, HORZSIZE))
= AddLog("Physical screen, height, mm", GetDeviceCaps(hdc, VERTSIZE))
= AddLog("Screen, width, pixels", GetDeviceCaps(hdc, HORZRES))
= AddLog("Screen, height, pixels", GetDeviceCaps(hdc, VERTRES))
= AddLog("Pixels per inch, X", GetDeviceCaps(hdc, LOGPIXELSX))
= AddLog("Pixels per inch, Y", GetDeviceCaps(hdc, LOGPIXELSY))
= AddLog("Bits per pixel", GetDeviceCaps(hdc, BITSPIXEL))
= AddLog("Color planes", GetDeviceCaps(hdc, PLANES))
= AddLog("Device-spec. brushes", GetDeviceCaps(hdc, NUMBRUSHES))
= AddLog("Device-spec. pens", GetDeviceCaps(hdc, NUMPENS))
= AddLog("Device-spec. fonts", GetDeviceCaps(hdc, NUMFONTS))
= AddLog("Colors in table", GetDeviceCaps(hdc, NUMCOLORS))
= AddLog("Pixel relative width", GetDeviceCaps(hdc, ASPECTX))
= AddLog("Pixel relative height", GetDeviceCaps(hdc, ASPECTY))
= AddLog("Pixel relative diagonal", GetDeviceCaps(hdc, ASPECTXY))
= AddLog("Clipping capab.", GetDeviceCaps(hdc, CLIPCAPS))
= AddLog("Entries in sys.palette", GetDeviceCaps(hdc, SIZEPALETTE))
= AddLog("Res.entries in sys.palette", GetDeviceCaps(hdc, NUMRESERVED))
= AddLog("Actual color resolution", GetDeviceCaps(hdc, COLORRES))
= AddLog("Physical page width", GetDeviceCaps(hdc, PHYSICALWIDTH))
= AddLog("Physical page height", GetDeviceCaps(hdc, PHYSICALHEIGHT))
= AddLog("Physical offset X", GetDeviceCaps(hdc, PHYSICALOFFSETX))
= AddLog("Physical offset Y", GetDeviceCaps(hdc, PHYSICALOFFSETY))
= AddLog("Printer scaling factor X", GetDeviceCaps(hdc, SCALINGFACTORX))
= AddLog("Printer scaling factor Y", GetDeviceCaps(hdc, SCALINGFACTORY))
= AddLog("Raster caps", GetDeviceCaps(hdc, RASTERCAPS))
= AddLog("Curve caps", GetDeviceCaps(hdc, CURVECAPS))
= AddLog("Line caps", GetDeviceCaps(hdc, LINECAPS))
= AddLog("Polygon caps", GetDeviceCaps(hdc, POLYGONALCAPS))
= AddLog("Text caps", GetDeviceCaps(hdc, TEXTCAPS))
= ReleaseDC(0, hdc)
GO TOP
BROWSE NORMAL NOWAIT
PROCEDURE AddLog(lcName, lnPrm)
INSERT INTO csResult VALUES(m.lcName, m.lnPrm)
PROCEDURE decl
DECLARE INTEGER GetDC IN user32 INTEGER hwnd
DECLARE INTEGER ReleaseDC IN user32 INTEGER hwnd, INTEGER hdc
DECLARE INTEGER GetDeviceCaps IN gdi32 INTEGER hdc, INTEGER nIndex
#DEFINE TECHNOLOGY 2
#DEFINE HORZSIZE 4
#DEFINE VERTSIZE 6
#DEFINE HORZRES 8
#DEFINE VERTRES 10
#DEFINE LOGPIXELSX 88
#DEFINE LOGPIXELSY 90
#DEFINE BITSPIXEL 12
#DEFINE PLANES 14
#DEFINE NUMBRUSHES 16
#DEFINE NUMPENS 18
#DEFINE NUMFONTS 22
#DEFINE NUMCOLORS 24
#DEFINE ASPECTX 40
#DEFINE ASPECTY 42
#DEFINE ASPECTXY 44
#DEFINE CLIPCAPS 36
#DEFINE SIZEPALETTE 104
#DEFINE NUMRESERVED 106
#DEFINE COLORRES 108
#DEFINE PHYSICALWIDTH 110
#DEFINE PHYSICALHEIGHT 111
#DEFINE PHYSICALOFFSETX 112
#DEFINE PHYSICALOFFSETY 113
#DEFINE SCALINGFACTORX 114
#DEFINE SCALINGFACTORY 115
#DEFINE RASTERCAPS 38
#DEFINE CURVECAPS 28
#DEFINE LINECAPS 30
#DEFINE POLYGONALCAPS 32
#DEFINE TEXTCAPS 34
DO decl
LOCAL hdc
hdc = GetDC(0) && entire screen
CREATE CURSOR csResult (name C(30), prm N(12))
= AddLog("Driver version", GetDeviceCaps(hdc, DRIVERVERSION))
= AddLog("Device technology", GetDeviceCaps(hdc, TECHNOLOGY))
= AddLog("Physical screen, width, mm", GetDeviceCaps(hdc, HORZSIZE))
= AddLog("Physical screen, height, mm", GetDeviceCaps(hdc, VERTSIZE))
= AddLog("Screen, width, pixels", GetDeviceCaps(hdc, HORZRES))
= AddLog("Screen, height, pixels", GetDeviceCaps(hdc, VERTRES))
= AddLog("Pixels per inch, X", GetDeviceCaps(hdc, LOGPIXELSX))
= AddLog("Pixels per inch, Y", GetDeviceCaps(hdc, LOGPIXELSY))
= AddLog("Bits per pixel", GetDeviceCaps(hdc, BITSPIXEL))
= AddLog("Color planes", GetDeviceCaps(hdc, PLANES))
= AddLog("Device-spec. brushes", GetDeviceCaps(hdc, NUMBRUSHES))
= AddLog("Device-spec. pens", GetDeviceCaps(hdc, NUMPENS))
= AddLog("Device-spec. fonts", GetDeviceCaps(hdc, NUMFONTS))
= AddLog("Colors in table", GetDeviceCaps(hdc, NUMCOLORS))
= AddLog("Pixel relative width", GetDeviceCaps(hdc, ASPECTX))
= AddLog("Pixel relative height", GetDeviceCaps(hdc, ASPECTY))
= AddLog("Pixel relative diagonal", GetDeviceCaps(hdc, ASPECTXY))
= AddLog("Clipping capab.", GetDeviceCaps(hdc, CLIPCAPS))
= AddLog("Entries in sys.palette", GetDeviceCaps(hdc, SIZEPALETTE))
= AddLog("Res.entries in sys.palette", GetDeviceCaps(hdc, NUMRESERVED))
= AddLog("Actual color resolution", GetDeviceCaps(hdc, COLORRES))
= AddLog("Physical page width", GetDeviceCaps(hdc, PHYSICALWIDTH))
= AddLog("Physical page height", GetDeviceCaps(hdc, PHYSICALHEIGHT))
= AddLog("Physical offset X", GetDeviceCaps(hdc, PHYSICALOFFSETX))
= AddLog("Physical offset Y", GetDeviceCaps(hdc, PHYSICALOFFSETY))
= AddLog("Printer scaling factor X", GetDeviceCaps(hdc, SCALINGFACTORX))
= AddLog("Printer scaling factor Y", GetDeviceCaps(hdc, SCALINGFACTORY))
= AddLog("Raster caps", GetDeviceCaps(hdc, RASTERCAPS))
= AddLog("Curve caps", GetDeviceCaps(hdc, CURVECAPS))
= AddLog("Line caps", GetDeviceCaps(hdc, LINECAPS))
= AddLog("Polygon caps", GetDeviceCaps(hdc, POLYGONALCAPS))
= AddLog("Text caps", GetDeviceCaps(hdc, TEXTCAPS))
= ReleaseDC(0, hdc)
GO TOP
BROWSE NORMAL NOWAIT
PROCEDURE AddLog(lcName, lnPrm)
INSERT INTO csResult VALUES(m.lcName, m.lnPrm)
PROCEDURE decl
DECLARE INTEGER GetDC IN user32 INTEGER hwnd
DECLARE INTEGER ReleaseDC IN user32 INTEGER hwnd, INTEGER hdc
DECLARE INTEGER GetDeviceCaps IN gdi32 INTEGER hdc, INTEGER nIndex
CA - Cursor Adapter
CLEAR ALL
PUBLIC XC
XC= SQLSTRINGCONNECT("Driver={SQL Server Native Client 10.0}; Server=vicos-pc\sqlicas;database=test2010;Trusted_connection=yes;")
LOCAL aa as CursorAdapter
SIC="%531%"
aa= CREATEOBJECT("CursorAdapter")
aa.datasource=xc
aa.DataSourceType="ODBC"
aa.AllowDelete =.T.
aa.Allowinsert=.T.
aa.AllowUpdate =.T.
aa.SelectCmd ="SELECT * from Conturi WHERE cont LIKE ?SIC "
aa.Alias="test"
aa.BufferModeOverride=5
IF !aa. CursorFill()
WAIT WINDOW SYS(2018)
RETURN .F.
ENDIF
BEGIN TRANSACTION
SELECT test
i=1
SCAN
IF i=10
EXIT
ENDIF
REPLACE cont WITH "VICOS_CA_TEST"
i = i + 1
ENDSCAN
IF !TABLEUPDATE(.T.)
MESSAGEBOX("EROARE: Actualizarea a esuat!", 16, "Eroare")
ENDIF
BROWSE
ROLLBACK
BROWSE
SQLDISCONNECT(xc)
PUBLIC XC
XC= SQLSTRINGCONNECT("Driver={SQL Server Native Client 10.0}; Server=vicos-pc\sqlicas;database=test2010;Trusted_connection=yes;")
LOCAL aa as CursorAdapter
SIC="%531%"
aa= CREATEOBJECT("CursorAdapter")
aa.datasource=xc
aa.DataSourceType="ODBC"
aa.AllowDelete =.T.
aa.Allowinsert=.T.
aa.AllowUpdate =.T.
aa.SelectCmd ="SELECT * from Conturi WHERE cont LIKE ?SIC "
aa.Alias="test"
aa.BufferModeOverride=5
IF !aa. CursorFill()
WAIT WINDOW SYS(2018)
RETURN .F.
ENDIF
BEGIN TRANSACTION
SELECT test
i=1
SCAN
IF i=10
EXIT
ENDIF
REPLACE cont WITH "VICOS_CA_TEST"
i = i + 1
ENDSCAN
IF !TABLEUPDATE(.T.)
MESSAGEBOX("EROARE: Actualizarea a esuat!", 16, "Eroare")
ENDIF
BROWSE
ROLLBACK
BROWSE
SQLDISCONNECT(xc)
joi, 22 iulie 2010
http://www.foxite.com/articles/read.aspx?id=53&document=creating-using-ca-classes-a-simple-tutorial-part-ii
http://www.foxite.com/articles/read.aspx?id=53&document=creating-using-ca-classes-a-simple-tutorial-part-ii
ADO Jumpstart for Microsoft Visual FoxPro Developers
http://msdn.microsoft.com/en-us/library/ms917355.aspx
WinApiSupport.prg
DEFINE CLASS WinApiSupport AS Custom
* Converts VFP number to the Long integer
FUNCTION Num2Long(tnNum)
LOCAL lcStringl
lcString = SPACE(4)
=RtlPL2PS(@lcString, BITOR(tnNum,0), 4)
RETURN lcString
ENDFUNC
* Convert Long integer into VFP numeric variable
FUNCTION Long2Num(tcLong)
LOCAL lnNum
lnNum = 0
= RtlS2PL(@lnNum, tcLong, 4)
RETURN lnNum
ENDFUNC
* Return Number from a pointer to DWORD
FUNCTION Long2NumFromBuffer(tnPointer)
LOCAL lnNum
lnNum = 0
= RtlP2PL(@lnNum, tnPointer, 4)
RETURN lnNum
ENDFUNC
* Convert Short integer into VFP numeric variable
FUNCTION Short2Num(tcLong)
LOCAL lnNum
lnNum = 0
= RtlS2PL(@lnNum, tcLong, 2)
RETURN lnNum
ENDFUNC
* Retrieve zero-terminated string from a buffer into VFP variable
FUNCTION StrZFromBuffer(tnPointer)
LOCAL lcStr, lnStrPointer
lcStr = SPACE(4096)
lnStrPointer = 0
= RtlP2PL(@lnStrPointer, tnPointer, 4)
lstrcpy(@lcStr, lnStrPointer)
RETURN LEFT(lcStr, AT(CHR(0),lcStr)-1)
ENDFUNC
* Return a string from a pointer to LPWString (Unicode string)
FUNCTION StrZFromBufferW(tnPointer)
Local lcResult, lnStrPointer, lnSen
lnStrPointer = This.Long2NumFromBuffer(tnPointer)
lnSen = lstrlenW(lnStrPointer) * 2
lcResult = Replicate(chr(0), lnSen)
= RtlP2PS(@lcResult, lnStrPointer, lnSen)
lcResult = StrConv(StrConv(lcResult, 6), 2)
RETURN lcResult
ENDFUNC
* Retrieve zero-terminated string
FUNCTION StrZCopy(tnPointer)
LOCAL lcStr, lnStrPointer
lcStr = SPACE(4096)
lstrcpy(@lcStr, tnPointer)
RETURN LEFT(lcStr, AT(CHR(0),lcStr)-1)
ENDFUNC
ENDDEFINE
*------------------------------------------------------------------------
FUNCTION RtlPL2PS(tcDest, tnSrc, tnLen)
DECLARE RtlMoveMemory IN WIN32API AS RtlPL2PS STRING @Dest, Long @Source, Long Length
RETURN RtlPL2PS(@tcDest, tnSrc, tnLen)
FUNCTION RtlS2PL(tnDest, tcSrc, tnLen)
DECLARE RtlMoveMemory IN WIN32API AS RtlS2PL Long @Dest, String Source, Long Length
RETURN RtlS2PL(@tnDest, @tcSrc, tnLen)
FUNCTION RtlP2PL(tnDest, tnSrc, tnLen)
DECLARE RtlMoveMemory IN WIN32API AS RtlP2PL Long @Dest, Long Source, Long Length
RETURN RtlP2PL(@tnDest, tnSrc, tnLen)
FUNCTION RtlP2PS(tcDest, tnSrc, tnLen)
DECLARE RtlMoveMemory IN WIN32API AS RtlP2PS STRING @Dest, Long Source, Long Length
RETURN RtlP2PS(@tcDest, tnSrc, tnLen)
FUNCTION lstrcpy (tcDest, tnSrc)
DECLARE lstrcpy IN WIN32API STRING @lpstring1, INTEGER lpstring2
RETURN lstrcpy (@tcDest, tnSrc)
FUNCTION lstrlenW(tnSrc)
DECLARE Long lstrlenW IN WIN32API Long src
RETURN lstrlenW(tnSrc)
* Converts VFP number to the Long integer
FUNCTION Num2Long(tnNum)
LOCAL lcStringl
lcString = SPACE(4)
=RtlPL2PS(@lcString, BITOR(tnNum,0), 4)
RETURN lcString
ENDFUNC
* Convert Long integer into VFP numeric variable
FUNCTION Long2Num(tcLong)
LOCAL lnNum
lnNum = 0
= RtlS2PL(@lnNum, tcLong, 4)
RETURN lnNum
ENDFUNC
* Return Number from a pointer to DWORD
FUNCTION Long2NumFromBuffer(tnPointer)
LOCAL lnNum
lnNum = 0
= RtlP2PL(@lnNum, tnPointer, 4)
RETURN lnNum
ENDFUNC
* Convert Short integer into VFP numeric variable
FUNCTION Short2Num(tcLong)
LOCAL lnNum
lnNum = 0
= RtlS2PL(@lnNum, tcLong, 2)
RETURN lnNum
ENDFUNC
* Retrieve zero-terminated string from a buffer into VFP variable
FUNCTION StrZFromBuffer(tnPointer)
LOCAL lcStr, lnStrPointer
lcStr = SPACE(4096)
lnStrPointer = 0
= RtlP2PL(@lnStrPointer, tnPointer, 4)
lstrcpy(@lcStr, lnStrPointer)
RETURN LEFT(lcStr, AT(CHR(0),lcStr)-1)
ENDFUNC
* Return a string from a pointer to LPWString (Unicode string)
FUNCTION StrZFromBufferW(tnPointer)
Local lcResult, lnStrPointer, lnSen
lnStrPointer = This.Long2NumFromBuffer(tnPointer)
lnSen = lstrlenW(lnStrPointer) * 2
lcResult = Replicate(chr(0), lnSen)
= RtlP2PS(@lcResult, lnStrPointer, lnSen)
lcResult = StrConv(StrConv(lcResult, 6), 2)
RETURN lcResult
ENDFUNC
* Retrieve zero-terminated string
FUNCTION StrZCopy(tnPointer)
LOCAL lcStr, lnStrPointer
lcStr = SPACE(4096)
lstrcpy(@lcStr, tnPointer)
RETURN LEFT(lcStr, AT(CHR(0),lcStr)-1)
ENDFUNC
ENDDEFINE
*------------------------------------------------------------------------
FUNCTION RtlPL2PS(tcDest, tnSrc, tnLen)
DECLARE RtlMoveMemory IN WIN32API AS RtlPL2PS STRING @Dest, Long @Source, Long Length
RETURN RtlPL2PS(@tcDest, tnSrc, tnLen)
FUNCTION RtlS2PL(tnDest, tcSrc, tnLen)
DECLARE RtlMoveMemory IN WIN32API AS RtlS2PL Long @Dest, String Source, Long Length
RETURN RtlS2PL(@tnDest, @tcSrc, tnLen)
FUNCTION RtlP2PL(tnDest, tnSrc, tnLen)
DECLARE RtlMoveMemory IN WIN32API AS RtlP2PL Long @Dest, Long Source, Long Length
RETURN RtlP2PL(@tnDest, tnSrc, tnLen)
FUNCTION RtlP2PS(tcDest, tnSrc, tnLen)
DECLARE RtlMoveMemory IN WIN32API AS RtlP2PS STRING @Dest, Long Source, Long Length
RETURN RtlP2PS(@tcDest, tnSrc, tnLen)
FUNCTION lstrcpy (tcDest, tnSrc)
DECLARE lstrcpy IN WIN32API STRING @lpstring1, INTEGER lpstring2
RETURN lstrcpy (@tcDest, tnSrc)
FUNCTION lstrlenW(tnSrc)
DECLARE Long lstrlenW IN WIN32API Long src
RETURN lstrlenW(tnSrc)
EnvironmentVariablesUsingWMI
SET MEMOWIDTH TO 1024
CLEAR
LOCAL loWMI, loEnvCol, loEnvVar
loLocator = CREATEOBJECT('WbemScripting.SWbemLocator')
loWMI = loLocator.ConnectServer()
loEnvCol = loWMI.ExecQuery("select * from Win32_Environment")
FOR EACH loEnvVar in loEnvCol
WITH loEnvVar
? .Name, .SystemVariable AT 25, .UserName, .VariableValue
ENDWITH
ENDFOR
CLEAR
LOCAL loWMI, loEnvCol, loEnvVar
loLocator = CREATEOBJECT('WbemScripting.SWbemLocator')
loWMI = loLocator.ConnectServer()
loEnvCol = loWMI.ExecQuery("select * from Win32_Environment")
FOR EACH loEnvVar in loEnvCol
WITH loEnvVar
? .Name, .SystemVariable AT 25, .UserName, .VariableValue
ENDWITH
ENDFOR
GetColumnValue (ComboBox)
*-- Returns a value of a specified column number passed as a parameter.
LPARAMETERS tnColumn
LOCAL lnOldBoundColumn, luRetVal
*-- Save the old bound column
lnOldBoundColumn = This.BoundColumn
*-- Make the requested column the new bound column
This.BoundColumn = tnColumn
*-- Retrieve the value
luRetVal = This.Value
*-- Reset the previously bound column condition
This.BoundColumn = lnOldBoundColumn
RETURN luRetVal
************************************************************
* Method: CComboBox.GetColumnValue()
*
*) Description:
*) Returns a value of a specified column number
*) passed as a parameter.
*
*@ Inputs:
*@ 1. - number of the column containing the
*@ value you want
*@ 2. This.Value - value of the bound column
*
* Outputs: None
*$ Usage:
*$.GetColumnValue()
*
*% Example:
*% This.GetColumnValue(2) - returns the value in the
*% second column of the combo box.
*
* Returns: Unknown Type - can be any value in the combo box
* Assumptions: None
* Rules:
* 1. You must specify a column that exists. No validation
* for number of columns is performed. If you specify
* the 3rd column and there are only two ... error.
*
* Constraints:
* Performance: None
* Enviornmental: None
*? Notes: None
* Local Routines: None
*-- Process:
*-- 1. Save the old bound column
*-- 2. Make the requested column the new bound column
*-- 3. Retrieve the value
*-- 4. Reset the previously bound column condition
*
* Change Log:
* CREATED Thursday, 11/02/95 22:29:26 - CTB:
************************************************************
LPARAMETERS tnColumn
LOCAL lnOldBoundColumn, luRetVal
*-- Save the old bound column
lnOldBoundColumn = This.BoundColumn
*-- Make the requested column the new bound column
This.BoundColumn = tnColumn
*-- Retrieve the value
luRetVal = This.Value
*-- Reset the previously bound column condition
This.BoundColumn = lnOldBoundColumn
RETURN luRetVal
************************************************************
* Method: CComboBox.GetColumnValue()
*
*) Description:
*) Returns a value of a specified column number
*) passed as a parameter.
*
*@ Inputs:
*@ 1.
*@ value you want
*@ 2. This.Value - value of the bound column
*
* Outputs: None
*$ Usage:
*$
*
*% Example:
*% This.GetColumnValue(2) - returns the value in the
*% second column of the combo box.
*
* Returns: Unknown Type - can be any value in the combo box
* Assumptions: None
* Rules:
* 1. You must specify a column that exists. No validation
* for number of columns is performed. If you specify
* the 3rd column and there are only two ... error.
*
* Constraints:
* Performance: None
* Enviornmental: None
*? Notes: None
* Local Routines: None
*-- Process:
*-- 1. Save the old bound column
*-- 2. Make the requested column the new bound column
*-- 3. Retrieve the value
*-- 4. Reset the previously bound column condition
*
* Change Log:
* CREATED Thursday, 11/02/95 22:29:26 - CTB:
************************************************************
miercuri, 21 iulie 2010
GetColumnNumber
LPARAMETERS tcCaption
*-- Validate the input parameter
IF EMPTY(tcCaption)
RETURN 0
ENDIF
LOCAL lnColumn, lcCaption, lnRetVal, lnColumnControl, loCurrentColumn, loCurrentControl
* LOCAL/PRIVATE VARIABLE DESCRIPTIONS
* lnColumn - counting variable used to iterate through all
* grid columns
* lcCaption - column header caption for which to search
* lnRetVal - contains the column number with a matching header
* caption
* lnColumnControl - counting variable used to iterate through
* all controls in a column
* loCurrentColumn - object reference to the column object
* currently being searched.
* loCurrentControl - object reference to the column control
* currently being searched.
lcCaption = UPPER(tcCaption)
lnRetVal = 0
*-- Iterate through all columns
FOR lnColumn = 1 TO This.ColumnCount
*-- Create a LOCAL reference to the current column
loCurrentColumn = This.Columns(lnColumn)
*-- Iterate through all column controls,
*-- looking for header objects
FOR lnColumnControl = 1 TO loCurrentColumn.ControlCount
*-- Create a LOCAL reference to current column control
loCurrentControl = loCurrentColumn.Controls(lnColumnControl)
*-- If we found the header object
IF UPPER(loCurrentControl.BaseClass) = "HEADER"
*-- If the caption of the header matches the
*-- specified caption ... we found our column number
IF UPPER(loCurrentControl.Caption) == lcCaption
lnRetVal = lnColumn
EXIT
ENDIF
ENDIF
ENDFOR
*-- lnRetVal will contain the column number IF we have a match
IF lnRetVal > 0
EXIT
ENDIF
ENDFOR
RETURN lnRetVal
* Method: CGrid.GetColumnNumber()
*) Description:
*) Returns a column number when all that is known is the
*) caption of the column's header.
*@ Inputs:
*@ 1. - caption of the column's header
*@ 2. This.ColumnCount - reference to the column object
*@ 3. This.Columns - number of columns in the grid
*@ 4. This.ControlCount - number of controls in the column
*@ 5. This.BaseClass - baseclass of the column
*@ 6. This.Caption - header caption
*@
* Outputs: None
*$ Usage:
*$.GetColumnNumber()
*% Example:
*% grdCustomer("Customer ID") - returns the column number
*% of the column that contains the Customer ID caption
*% in the Customer grid.
* Returns: NUMERIC - number of the column that has a header
* with the specified text
* 0 - IF no match is found.
* Assumptions: None
* Rules: None
* Constraints:
* Performance: None
* Enviornmental: None
*? Notes: None
* Local Routines: None
*-- Process:
*-- 1. Validate the input parameter
*-- 2. Declare LOCAL variables
*-- 3. FOR all columns in the grid
*-- 4. Create a LOCAL reference to the current column
*-- 5. FOR all controls in the column
*-- 6. Create a LOCAL reference to current column control
*-- 7. IF we found the header object
*-- 8. IF the caption of the header matches the
*-- specified caption ... we found our column
*-- number
*-- EXIT from the control loop
*-- ENDIF
*-- ENDIF
*-- ENDFOR
*-- 9. If a column number is found, EXIT from the loop
*-- 10. ENDFOR
* Change Log:
* CREATED Thursday, 11/02/95 22:41:04 - CTB:
*-- Validate the input parameter
IF EMPTY(tcCaption)
RETURN 0
ENDIF
LOCAL lnColumn, lcCaption, lnRetVal, lnColumnControl, loCurrentColumn, loCurrentControl
* LOCAL/PRIVATE VARIABLE DESCRIPTIONS
* lnColumn - counting variable used to iterate through all
* grid columns
* lcCaption - column header caption for which to search
* lnRetVal - contains the column number with a matching header
* caption
* lnColumnControl - counting variable used to iterate through
* all controls in a column
* loCurrentColumn - object reference to the column object
* currently being searched.
* loCurrentControl - object reference to the column control
* currently being searched.
lcCaption = UPPER(tcCaption)
lnRetVal = 0
*-- Iterate through all columns
FOR lnColumn = 1 TO This.ColumnCount
*-- Create a LOCAL reference to the current column
loCurrentColumn = This.Columns(lnColumn)
*-- Iterate through all column controls,
*-- looking for header objects
FOR lnColumnControl = 1 TO loCurrentColumn.ControlCount
*-- Create a LOCAL reference to current column control
loCurrentControl = loCurrentColumn.Controls(lnColumnControl)
*-- If we found the header object
IF UPPER(loCurrentControl.BaseClass) = "HEADER"
*-- If the caption of the header matches the
*-- specified caption ... we found our column number
IF UPPER(loCurrentControl.Caption) == lcCaption
lnRetVal = lnColumn
EXIT
ENDIF
ENDIF
ENDFOR
*-- lnRetVal will contain the column number IF we have a match
IF lnRetVal > 0
EXIT
ENDIF
ENDFOR
RETURN lnRetVal
* Method: CGrid.GetColumnNumber()
*) Description:
*) Returns a column number when all that is known is the
*) caption of the column's header.
*@ Inputs:
*@ 1.
*@ 2. This.ColumnCount - reference to the column object
*@ 3. This.Columns - number of columns in the grid
*@ 4. This.ControlCount - number of controls in the column
*@ 5. This.BaseClass - baseclass of the column
*@ 6. This.Caption - header caption
*@
* Outputs: None
*$ Usage:
*$
*% Example:
*% grdCustomer("Customer ID") - returns the column number
*% of the column that contains the Customer ID caption
*% in the Customer grid.
* Returns: NUMERIC - number of the column that has a header
* with the specified text
* 0 - IF no match is found.
* Assumptions: None
* Rules: None
* Constraints:
* Performance: None
* Enviornmental: None
*? Notes: None
* Local Routines: None
*-- Process:
*-- 1. Validate the input parameter
*-- 2. Declare LOCAL variables
*-- 3. FOR all columns in the grid
*-- 4. Create a LOCAL reference to the current column
*-- 5. FOR all controls in the column
*-- 6. Create a LOCAL reference to current column control
*-- 7. IF we found the header object
*-- 8. IF the caption of the header matches the
*-- specified caption ... we found our column
*-- number
*-- EXIT from the control loop
*-- ENDIF
*-- ENDIF
*-- ENDFOR
*-- 9. If a column number is found, EXIT from the loop
*-- 10. ENDFOR
* Change Log:
* CREATED Thursday, 11/02/95 22:41:04 - CTB:
joi, 15 iulie 2010
buffering
Prima sugestie ar fi sa folosesti indecsi compusi (au extensia .cdx si contin toate cheile de indexare intr-un singur fisier). Index On cod Tag iVal si respectiv Index on Nume Tag iNume. Exista doua dezavantaje: 1. Indexarea trebuie sa fie facuta cu tabela deschisa exclusiv (dar operatiunea asta o faci o data cand creezi tabela si eventual daca iti crapa fisierul de index, deci nu e o problema de care te lovesti in fiecare zi), si 2. Nu poti sa indexezi o tabela deschisa in Table Buffering mode. Dar aceste doua dezavantaje sunt, dupa parerea mea, acoperite cu varf si indesat de avantajele pe care le obtii: unul ar fi ca indexul se deschide automat odata cu tabela si poti sa faci SET ORDER TO iVal si respectiv SET ORDER TO iNume oricand doresti, fara sa intampini eroarea aia pe care o ai acum. Altul ar fi ca poti folosi toate comenzile legate de indecsi (ATagInfo, de exemplu). Altul ar fi acela ca ori de cate ori modifici, stergi sau adaugi o inregistrare fisierul de index se actualizeaza automat, pe toate cheile de indexare, si nu ai nevoie sa faci reindexarea (care mananca timp) decat in situatii cu totul exceptionale.
Cat despre diferenta dintre Row si Table buffering, uite care-i treaba, folosind propriile mele cuvinte (s-ar putea sa difere de definitiile de prin carti/help):
Folosirea buffering-ului inseamna sa-l pui pe VFP sa creeze un spatiu intre tine si tabela de pe disc. Aparent USE tabela deschide tabela si poti sa inserezi, stergi sau modifica inregistrari. Totusi nu se intampla asa; tu de fapt scrii in spatiul ala, numit buffer. Daca modifici o inregistrare in buffer, ea nu se scrie in tabela decat la TableUpdate(). Daca modifici in buffer si dai QUIT, de exemplu, tabela ramane nemodificata (chestie foarte desteapta, de altfel. In FPD programatorii au folosit tot felul de tehnici pentru a-si proteja tabelele: tabele temporare, care la sfarsitul operatiunii erau varsate pe disc, in tabelele finale, sau SCATTER/GATHER, folosind variabile de memorie pe post de campuri. Toate astea numai pentru a reduce manevrele cu tabela "de baza", reducand astfel sansele ca ea sa crape).
Well... Buffering-ul este exact "tamponul" dintre aplicatie si tabela. Scrii in buffer pana te plictisesti, si cand te-ai plictisit, TABLEUPDATE() sau TABLEREVERT(). Ghici ce face fiecare dintre ele :).
Mai departe. Buffering-ul asta e de mai multe feluri: optimistic/pessimistic si respectiv row/table. Rezulta 4 combinatii, la care se adauga a 5-a - no buffering.
Daca-i pesimistic, la citirea inregistrarii se plaseaza un LOCK() pe inregistrarea respectiva. De remarcat faptul ca pot sa am buffering pe tabela sau pe rand si lock-ul se plaseaza corespunzator (pe inregistrare sau pe toata tabela).
Daca-i optimistic, lock-ul se plaseaza doar la tentativa de scriere in tabela, ceea ce permite ca mai multi utilizatori sa citeasca tabela simultan, si sa scrie cand doresc, dar care ridica o problema importanta: cum rezolvi conflictele de actualizare. Well.. asta-i alta discutie deja.
Daca-i table, bufferul este cat tabela. Cu alte cuvinte, daca scrii intr-o inregistrare, poti sa te muti la urmatoarea, si apoi la urmatoarea, etc, pana termini de actualizat toate inregistrarile, iar la sfarsit ii trantesti un TABLEUPDATE() peste ochi si gata - se salveaza. (daca nu se salveaza, TableUpdate() intoarce .F., iar daca executi imediat un AError(laErrorArray), in array-ul laErrorArray ai pe pozitia a 2-a mesajul de eroare).
Daca-i row, bufferul e doar de un rand. Scrii in inregistrarea aia, si daca ii dai TABLEUPDATE() sau TABLEREVERT() se comporta corect. In schimb, problema MAJORA (si am folosit majuscule) este ca daca nu dai nici tableupdate, nici tablerevert, dar te muti la alta inregistrare, VFP executa un TABLEUPDATE() automat, ceea ce poate sa nu-ti convina. Daca introduci o factura, de exemplu, factura aia trebuie salvata toata sau deloc (header-ul facturii, respectiv pozitiile din factura). In scenariul asta, tableupdate() executat de VFP cand vrea muschiu' lui este reteta sigura pentru date inconsistente.
Mai mult (hehe, ca in reclamele alea idioate: but wait! there's more!): Este posibil ca mutarea pointerului de inregistrare (care determina tableupdate necontrolat) sa se execute in situatii greu de anticipat. De exemplu, IndexSeek() muta pointerul. Quite nashpa, I'd say.
Ca atare, recomandarea mea ar fi sa nu folosesti Row Buffering. Cu exceptia indexarii (care vrea row buffering) tot ce poti face in Row Buffering poti face si in Table Buffering. Daca ai nevoie de indexare, atunci CursorSetProp("Buffering", 3), INDEX ON, CursorSetProp("Buffering",5) si-ai rezolvat problema.
Cat despre diferenta dintre Row si Table buffering, uite care-i treaba, folosind propriile mele cuvinte (s-ar putea sa difere de definitiile de prin carti/help):
Folosirea buffering-ului inseamna sa-l pui pe VFP sa creeze un spatiu intre tine si tabela de pe disc. Aparent USE tabela deschide tabela si poti sa inserezi, stergi sau modifica inregistrari. Totusi nu se intampla asa; tu de fapt scrii in spatiul ala, numit buffer. Daca modifici o inregistrare in buffer, ea nu se scrie in tabela decat la TableUpdate(). Daca modifici in buffer si dai QUIT, de exemplu, tabela ramane nemodificata (chestie foarte desteapta, de altfel. In FPD programatorii au folosit tot felul de tehnici pentru a-si proteja tabelele: tabele temporare, care la sfarsitul operatiunii erau varsate pe disc, in tabelele finale, sau SCATTER/GATHER, folosind variabile de memorie pe post de campuri. Toate astea numai pentru a reduce manevrele cu tabela "de baza", reducand astfel sansele ca ea sa crape).
Well... Buffering-ul este exact "tamponul" dintre aplicatie si tabela. Scrii in buffer pana te plictisesti, si cand te-ai plictisit, TABLEUPDATE() sau TABLEREVERT(). Ghici ce face fiecare dintre ele :).
Mai departe. Buffering-ul asta e de mai multe feluri: optimistic/pessimistic si respectiv row/table. Rezulta 4 combinatii, la care se adauga a 5-a - no buffering.
Daca-i pesimistic, la citirea inregistrarii se plaseaza un LOCK() pe inregistrarea respectiva. De remarcat faptul ca pot sa am buffering pe tabela sau pe rand si lock-ul se plaseaza corespunzator (pe inregistrare sau pe toata tabela).
Daca-i optimistic, lock-ul se plaseaza doar la tentativa de scriere in tabela, ceea ce permite ca mai multi utilizatori sa citeasca tabela simultan, si sa scrie cand doresc, dar care ridica o problema importanta: cum rezolvi conflictele de actualizare. Well.. asta-i alta discutie deja.
Daca-i table, bufferul este cat tabela. Cu alte cuvinte, daca scrii intr-o inregistrare, poti sa te muti la urmatoarea, si apoi la urmatoarea, etc, pana termini de actualizat toate inregistrarile, iar la sfarsit ii trantesti un TABLEUPDATE() peste ochi si gata - se salveaza. (daca nu se salveaza, TableUpdate() intoarce .F., iar daca executi imediat un AError(laErrorArray), in array-ul laErrorArray ai pe pozitia a 2-a mesajul de eroare).
Daca-i row, bufferul e doar de un rand. Scrii in inregistrarea aia, si daca ii dai TABLEUPDATE() sau TABLEREVERT() se comporta corect. In schimb, problema MAJORA (si am folosit majuscule) este ca daca nu dai nici tableupdate, nici tablerevert, dar te muti la alta inregistrare, VFP executa un TABLEUPDATE() automat, ceea ce poate sa nu-ti convina. Daca introduci o factura, de exemplu, factura aia trebuie salvata toata sau deloc (header-ul facturii, respectiv pozitiile din factura). In scenariul asta, tableupdate() executat de VFP cand vrea muschiu' lui este reteta sigura pentru date inconsistente.
Mai mult (hehe, ca in reclamele alea idioate: but wait! there's more!): Este posibil ca mutarea pointerului de inregistrare (care determina tableupdate necontrolat) sa se execute in situatii greu de anticipat. De exemplu, IndexSeek() muta pointerul. Quite nashpa, I'd say.
Ca atare, recomandarea mea ar fi sa nu folosesti Row Buffering. Cu exceptia indexarii (care vrea row buffering) tot ce poti face in Row Buffering poti face si in Table Buffering. Daca ai nevoie de indexare, atunci CursorSetProp("Buffering", 3), INDEX ON, CursorSetProp("Buffering",5) si-ai rezolvat problema.
Calcul Venit SQL
Alter
procedure [dbo].[psCalcul_venit_net]
@DataJ datetime, @DataS datetime, @MarcaJos char(6), @LocmJos char(9), @Inversare int
As
Begin
declare @HostID char(8), @Salar_minim float, @Indref_somaj float, @Ore_luna int,
@Sind_proc int, @Proc_sind float, @pCAS_ind float, @pCASS_ind float, @pSomaj_ind float, @pSomajI float, @pCAS_gr3 float, @pCAS_gr2 float, @pCAS_gr1 float,
@pCCI float, @Coef_CAS float, @Coef_CCI float, @pCASSU float, @pSomajU float, @pFond_gar float, @pFambp float, @Calcul_ITM int, @pITM float,
@Buget int, @Inst_publ int, @CASS_colab int, @NuITM_colab int, @NuITM_pens int,
@CCI_colabP int, @CCI_colabO int, @Chind_pont int, @Chind_lunacrt int, @Chind_vnet int, @NuRoT_BI int,
@Comp_sal_net int, @Salar_net_valuta int,
@NuCAS_H int, @NuCASS_H int, @Imps_H int, @CASSimps_K int, @Somaj_K int, @NuASS_J int, @CAS_J int, @NuASS_N int, @NuASSA_N int, @CorU_RP int, @CAS_U int,
@Venit_brut_cu_ded float, @Venit_brut_fara_ded float, @Dafora int, @Pasmatex int, @Drumor int, @Plastidr int
Set @HostID=isnull((select convert(char(8), abs(convert(int, host_id())))),'')
Set @Salar_minim=dbo.iauParLN(@DataS,'PS','S-MIN-BR')
Set @Indref_somaj=dbo.iauParLN(@DataS,'PS','SOMAJ-ISR')
Set @Ore_luna=dbo.iauParLN(@DataS,'PS','ORE_LUNA')
Exec Luare_date_par 'PS', 'SIND%', @Sind_proc OUTPUT, @Proc_sind OUTPUT, ''
Set @pCAS_ind=dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCASS_ind=dbo.iauParLN(@DataS,'PS','CASSIND')
Set @pSomaj_ind=dbo.iauParLN(@DataS,'PS','SOMAJIND')
Set @pCAS_gr3=dbo.iauParLN(@DataS,'PS','CASGRUPA3')-dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCAS_gr2=dbo.iauParLN(@DataS,'PS','CASGRUPA2')-dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCAS_gr1=dbo.iauParLN(@DataS,'PS','CASGRUPA1')-dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCCI=dbo.iauParLN(@DataS,'PS','COTACCI')
Set @Coef_CAS=dbo.iauParN('PS','COEFCAS')
Set @Coef_CAS=@Coef_CAS/1000000
Set @Coef_CCI=dbo.iauParN('PS','COEFCCI')
Set @Coef_CCI=@Coef_CCI/1000000
Set @pCASSU=dbo.iauParLN(@DataS,'PS','CASSUNIT')
Set @pSomajU=dbo.iauParLN(@DataS,'PS','3.5%SOMAJ')
Set @pFond_gar=dbo.iauParLN(@DataS,'PS','FONDGAR')
Set @pFambp=dbo.iauParLN(@DataS,'PS','0.5%ACCM')
Set @Calcul_ITM=dbo.iauParL('PS','1%-CAMERA')
Set @pITM=dbo.iauParLN(@DataS,'PS','1%-CAMERA')
Set @Buget=dbo.iauParL('PS','UNITBUGET')
Set @Inst_publ=dbo.iauParL('PS','INSTPUBL')
Set @CASS_colab=dbo.iauParL('PS','CALFASC')
Set @NuITM_colab=dbo.iauParL('PS','NCALPCMC')
Set @NuITM_pens=dbo.iauParL('PS','NCALPCMPE')
Set @CCI_colabP=dbo.iauParL('PS','CCICOLAB')
Set @CCI_colabO=dbo.iauParL('PS','CCICOLABO')
Set @Chind_pont=dbo.iauParL('PS','CHINDPON')
Set @Chind_lunacrt=dbo.iauParL('PS','CHINDLCRT')
Set @Chind_vnet=dbo.iauParL('PS','CHINDVEN')
Set @NuRoT_BI=dbo.iauParL('PS','BAZANEROT')
Set @Comp_sal_net=dbo.iauParL('PS','COMPSALN')
Set @Salar_net_valuta=dbo.iauParL('PS','SALNETV')
Set @NuCAS_H=dbo.iauParL('PS','NUCAS-H')
Set @NuCASS_H=dbo.iauParL('PS','NUASS-H')
Set @Imps_H=dbo.iauParL('PS','IMPSEP-H')
Set @NuASS_J=dbo.iauParL('PS','NUASS-J')
Set @CAS_J=dbo.iauParL('PS','CAS-J')
Set @CASSimps_K=dbo.iauParL('PS','ASSIMPS-K')
Set @Somaj_K=dbo.iauParL('PS','SOMAJ-K')
Set @NuASS_N=dbo.iauParL('PS','NUASS-N')
Set @NuASSA_N=dbo.iauParL('PS','NUASSA-N')
Set @CorU_RP=dbo.iauParL('PS','ADRPL-U')
Set @CAS_U=dbo.iauParL('PS','CALCAS-U')
Set @Venit_brut_cu_ded=dbo.iauParN('PS','VBCUDEDP')
Set @Venit_brut_fara_ded=dbo.iauParN('PS','VBFDEDP')
Set @Dafora=dbo.iauParL('SP','DAFORA')
Set @Pasmatex=dbo.iauParL('SP','PASMATEX')
Set @Drumor=dbo.iauParL('SP','DRUMOR')
Set @Plastidr=dbo.iauParL('SP','PLASTIDR')
declare @Data datetime, @Marca char(6), @Ore_lucrate int, @Ore_noapte int,
@ore_RN int, @Ore_IT int, @Ore_obligatii int, @Ore_CFS int, @Ore_CO int, @Ore_CM int, @Invoiri int, @Nemotivate int, @Ore_supl int,
@Ore_justif int, @Baza_somaj_1 float, @Ind_FAMBP float, @CMunitate float, @CMcas float,
@Diurna float, @Suma_impoz float, @Cons_admin float, @Suma_imp_sep float,
@Aj_deces float, @Venit_total float, @Spor_specific float, @Spor_cond_1 float, @Spor_cond_2 float,
@Spor_cond_3 float, @Spor_cond_4 float, @Spor_cond_5 float, @Spor_cond_6 float,
@Salar_orar float, @RegimL float, @Locm char(9),
@CorQ float, @CorT float, @CorU float, @Ret_sindicat float,
@RegimL_pontaj float, @RegimL_brut float, @GrupaM_pontaj char(1), @Somaj_1P int, @As_sanatateP float,
@Tip_impozitareP char(1), @GrupaMP char(1), @Salar_de_incadrareP float,
@Salar_de_bazaP float, @Tip_salarizareP char(1), @Ind_condP float,
@Spor_vechimeP float, @Spor_de_noapteP float, @Spor_sist_prgP float,
@Spor_functie_suplP float, @Spor_specificP float, @Spor_conditii_1P float, @Spor_conditii_2P float,
@Spor_conditii_3P float, @Spor_conditii_4P float, @Spor_conditii_5P float, @Spor_conditii_6P float,
@Tip_colabP char(3), @Alte_surseP char(1), @Grad_invalidP char(1), @Tip_ded_somajP float,
@Data_angajarii datetime, @Plecat char(1), @Mod_angajareP char(1),
@Data_plecP datetime, @Sindicalist char(1),
@DataI_conv_somaj datetime, @DataE_conv_somaj datetime, @Nr_pers_intr int,
@Baza_cond_normale float, @Baza_cond_deosebite float, @Baza_cond_speciale float,
@Indcm_unit_19 float, @Indcm_cas_19 float, @Ore_luna_cm int, @Indcm float,
@Indcm_cas_18 float, @Zcm_18 int, @Zcm_18_ant int, @Baza_casi_ant float,
@Baza_cascm_ant float, @Zcm_2341011 int, @Indcm_234 float, @Indcm_unit_234 float,
@Zcm15 int, @Zcm_8915 int, @Indcm_8915 float, @Zcm_78 int, @Indcm_78 float, @Indcm_somaj float,
@Ingrijire_copil_sarcina int, @Actionar char(1),
@uMarca2CNP int, @uMarca2CNPCM int, @CNP char(13),
@Pensie_max_ded float, @Pensie_ded_lunar float, @Pensie_ded_ant float, @Pensie_luna float,
@Salarii_comp float, @Alocatii_hrana float, @Somaj_tehnic float, @Ore_somaj_tehnic int, @Suma_neimp float
declare @Baza_CASCM float, @Baza_CASCM_CN float, @Baza_CASCM_CD float, @Baza_CASCM_CS float, @CASCM float,
@Baza_CAS_CN float, @Baza_CAS_CD float, @Baza_CAS_CS float, @CCI_Fambp float,
@Ore_somaj int, @Zile_CM_susp int, @IndCM_susp float, @Baza_somajI float, @SomajI float, @Baza_CASSI float, @CASSI float, @CASS_Fambp_sal float, @CASS_Fambp_ang float,
@Baza_CAS_ind float, @CAS_ind float, @Venit_deducere float, @Ded_baza decimal(10), @Venit_net_in_imp float,
@Ded_pensie float, @Ded_sindicat float, @Venit_baza float, @Impozit float, @Baza_impO float, @Impozit_sep float,
@Baza_impH float, @Impozit_H float, @Baza_N float, @VENIT_NET float,
@CAS_unit float, @Baza_CASS_unit float, @CASS_unit float, @Baza_somajU float, @Somaj_unit float,
@Baza_CCI float, @CCI float, @Baza_Fambp float, @Fambp float, @Baza_FondGar float, @Fond_gar float, @ITM float,
@Rest_de_plata float, @Coef_tot_ded int, @Zile_ded_somaj int, @ZileSC_ded_somaj int, @Ded_somaj float,
@Baza_ITM float, @Baza_CAS_cond_normale float, @Baza_CAS_cond_deoseb float, @Baza_CAS_cond_spec float
exec psCreare_cursor_venit_net @DataJ, @DataS, @MarcaJos, @LocmJos
declare cursor_net cursor for
Select Data, Marca, Ore_lucrate, Ore_noapte, Ore_RN, Ore_IT, Ore_obligatii,
Ore_CFS, Ore_CO, Ore_CM, Invoiri, Nemotivate, Ore_supl, Ore_justif,
Baza_somaj_1, Ind_FAMBP, CMunitate, CMCAS, Diurna, Suma_impoz, Cons_admin,
Suma_imp_sep, Aj_deces, Venit_total, Spor_specific, Spor_cond_1, Spor_cond_2, Spor_cond_3, Spor_cond_4, Spor_cond_5, Spor_cond_6,
Salar_orar, RegimL, Locm, CorQ, CorT, CorU, Ret_sindicat, RegimL_pontaj, GrupaM_pontaj,
Somaj_1P, As_sanatateP, Tip_impozitareP, GrupaM_pers, Salar_de_incadrareP, Salar_de_bazaP,
Tip_salarizareP, Ind_condP, Spor_vechimeP, Spor_de_noapteP, Spor_sist_prgP, Spor_functie_suplP,
Spor_specificP, Spor_conditii_1P, Spor_conditii_2P, Spor_conditii_3P, Spor_conditii_4P,
Spor_conditii_5P, Spor_conditii_6P, Tip_colabP, Alte_surseP, Grad_invalidP, Tip_ded_somajP,
Data_angajarii, Plecat, Mod_angajareP, Data_plecP, Sindicalist,
DataI_conv_somaj, DataE_conv_somaj, Nr_pers_intr,
Baza_cond_normale, Baza_cond_deosebite, Baza_cond_speciale,
Indcm_unit_19, Indcm_cas_19, Ore_luna_cm, Indcm, Indcm_cas_18, Zcm_18, Zcm_18_ant, Baza_casi_ant,
Baza_cascm_ant, Zcm_2341011, Indcm_234, Indcm_unit_234, Zcm15, Zcm_8915, Indcm_8915,
Zcm_78, Indcm_78, Indcm_somaj, Ingrijire_copil_sarcina, Actionar,
uMarca2CNP, uMarca2CNPCM, CNP, Pensie_max_ded, Pensie_ded_lunar,
Pensie_ded_ant, Pensie_luna, Salarii_comp, Alocatii_hrana, Somaj_tehnic, Ore_somaj_tehnic, Suma_neimp
from ##cursor_net
where hostid=@HostID
open cursor_net
fetch next from cursor_net into @Data, @Marca, @Ore_lucrate, @Ore_noapte, @Ore_RN, @Ore_IT, @Ore_obligatii,
@Ore_CFS, @Ore_CO, @Ore_CM, @Invoiri, @Nemotivate, @Ore_supl, @Ore_justif,
@Baza_somaj_1, @Ind_FAMBP, @CMunitate, @CMCAS, @Diurna, @Suma_impoz, @Cons_admin,
@Suma_imp_sep, @Aj_deces, @Venit_total, @Spor_specific, @Spor_cond_1, @Spor_cond_2, @Spor_cond_3, @Spor_cond_4, @Spor_cond_5, @Spor_cond_6,
@Salar_orar, @RegimL, @Locm, @CorQ, @CorT, @CorU, @Ret_sindicat, @RegimL_pontaj, @GrupaM_pontaj,
@Somaj_1P, @As_sanatateP, @Tip_impozitareP, @GrupaMP, @Salar_de_incadrareP, @Salar_de_bazaP,
@Tip_salarizareP, @Ind_condP, @Spor_vechimeP, @Spor_de_noapteP, @Spor_sist_prgP, @Spor_functie_suplP,
@Spor_specificP, @Spor_conditii_1P, @Spor_conditii_2P, @Spor_conditii_3P, @Spor_conditii_4P,
@Spor_conditii_5P, @Spor_conditii_6P, @Tip_colabP, @Alte_surseP, @Grad_invalidP, @Tip_ded_somajP,
@Data_angajarii, @Plecat, @Mod_angajareP, @Data_plecP, @Sindicalist,
@DataI_conv_somaj, @DataE_conv_somaj, @Nr_pers_intr,
@Baza_cond_normale, @Baza_cond_deosebite, @Baza_cond_speciale,
@Indcm_unit_19, @Indcm_cas_19, @Ore_luna_cm, @Indcm, @Indcm_cas_18, @Zcm_18, @Zcm_18_ant, @Baza_casi_ant,
@Baza_cascm_ant, @Zcm_2341011, @Indcm_234, @Indcm_unit_234, @Zcm15, @Zcm_8915, @Indcm_8915,
@Zcm_78, @Indcm_78, @Indcm_somaj, @Ingrijire_copil_sarcina, @Actionar,
@uMarca2CNP, @uMarca2CNPCM, @CNP, @Pensie_max_ded, @Pensie_ded_lunar,
@Pensie_ded_ant, @Pensie_luna, @Salarii_comp, @Alocatii_hrana, @Somaj_tehnic, @Ore_somaj_tehnic, @Suma_neimp
While @@fetch_status = 0
Begin
update net set Venit_total=0, VENIT_NET=0, Impozit=0, Rest_de_plata=0, Asig_sanatate_din_net=0,
Pensie_suplimentara_3=0, Somaj_1=0, Asig_sanatate_din_impozit=0, Asig_sanatate_din_CAS=0, Coef_tot_ded=0,
VEN_NET_IN_IMP=0, Ded_baza=0, Ded_suplim=0, VENIT_BAZA=0, Chelt_prof=0,
Debite_externe=0, Rate=0, Debite_interne=0, Cont_curent=0, CAS=0, Somaj_5=0, Fond_de_risc_1=0,
Camera_de_munca_1=0, Asig_sanatate_pl_unitate=0, Baza_CAS=0
where Data=@Data and Marca=@Marca
update net set Somaj_5=0, Ded_baza=0, Ded_suplim=0
where Data=dbo.bom(@Data) and Marca=@Marca
Set @Baza_CASCM=(case when @uMarca2CNPCM>0 and charindex(@GrupaMP,'NDS')=0 then 0 else
(@Zcm_18+@Zcm15-@Zcm_18_ant)*@Salar_minim/((case when @Dafora=1 then @Ore_luna_cm else @Ore_luna end)/8)+@Baza_cascm_ant end)
Set @Baza_CASCM_CN=(case when @GrupaM_pontaj='N' or @GrupaMP in ('N','P') then @Baza_CASCM else 0 end)
Set @Baza_CASCM_CD=(case when @GrupaM_pontaj='D' or @GrupaMP='D' then @Baza_CASCM else 0 end)
Set @Baza_CASCM_CS=(case when @GrupaM_pontaj='S' or @GrupaMP='S' then @Baza_CASCM else 0 end)
Set @CASCM=round(@Baza_CASCM_CS*@pCAS_gr1/100+@Baza_CASCM_CD*@pCAS_gr2/100+@Baza_CASCM_CN*@pCAS_gr3/100,0)
Set @CCI_Fambp=round(@Ind_FAMBP*@pCCI/100,0)
Set @Baza_CAS_cond_normale=round(@Baza_cond_normale*@Coef_CAS,0)
Set @Baza_CAS_cond_deoseb=round(@Baza_cond_deosebite*@Coef_CAS,0)
Set @Baza_CAS_cond_spec=round(@Baza_cond_speciale*@Coef_CAS,0)
Select @Zile_CM_susp=0, @IndCM_susp=0
if @Indcm<>0 or @Zcm_18+@Zcm_2341011+@Zcm15<>0
Begin
exec calcul_ZileCM_suspendare @Marca, @DataJ, @DataS, @Zile_CM_susp output, @IndCM_susp output
End
if @Venit_total>0
Begin
Set @Ore_somaj=@Ore_justif-@Zcm_78*@RegimL-(case when @Zile_CM_susp>0 then @Zile_CM_susp*@RegimL else 0 end)
-@Ore_CFS-@Invoiri-@Nemotivate-@Ore_somaj_tehnic
Set @pSomajI=(case when @Somaj_1P=1 then @pSomaj_ind/100 else 0 end)
Set @Baza_somajI=@Venit_total-@Indcm_78-(case when @IndCM_susp=0 then @Indcm_somaj else @IndCM_susp end)-@Salarii_comp-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)
-(case when @CASSimps_K=1 and @Somaj_K=0 then @Cons_admin else 0 end)-@Somaj_tehnic
Select @Baza_somajI=0 where @Ore_somaj=0
Set @SomajI=(case when @Baza_somajI*@pSomajI>0 and @Baza_somajI*@pSomajI<1 then 1 else round(@Baza_somajI*@pSomajI,0) end) End Set @Baza_CASSI=round(@Venit_total-(case when not((@Comp_sal_net=1 or @Salar_net_valuta=1) and @Inversare=1) then @Indcm_unit_19+@CMUnitate+@Indcm_cas_19+@CMCAS else 0 end) -(case when @NuASS_J=1 then @Diurna else 0 end)-(case when @NuCASS_H=1 then @Suma_impoz else 0 end)-(case when @NuASSA_N=1 then @Suma_neimp else 0 end)-@Somaj_tehnic,0) Set @CASSI=(case when @As_sanatateP<>0 then (case when @Baza_CASSI*@As_sanatateP/10/100>0 and @Baza_CASSI*@As_sanatateP/10/100<1 then 1 else round(@Baza_CASSI*@As_sanatateP/10/100,0) end)+@CorT end) Set @CASS_Fambp_sal=round(@Ind_Fambp*@pCASS_ind/100,0) Set @CASS_Fambp_ang=round(@Indcm_unit_234*@pCASS_ind/100,0) Select @Baza_CAS_ind=@Venit_total-(case when not((@Comp_sal_net=1 or @Salar_net_valuta=1) and @Inversare=1) then @Indcm_unit_19+@CMUnitate+@Indcm_cas_19+@CMCAS else 0 end) -(case when @CAS_J=1 or @Pasmatex=1 then 0 else (case when @Dafora=1 then -1 else 1 end)*@Diurna end) -(case when @CASSimps_K=1 and @GrupaMP<>'P' then @Cons_admin else 0 end)-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)+
(case when @CAS_U=1 then @CorU else 0 end)-@Somaj_tehnic
where @Baza_CAS_cond_normale+@Baza_CAS_cond_deoseb+@Baza_CAS_cond_spec<>0
Set @Baza_CAS_ind=round(@Baza_CAS_ind+@Baza_CASCM,0)
Set @CAS_ind=(case when (@Baza_CAS_ind-@Baza_CASI_ant)*@pCAS_ind/100+@Baza_CASI_ant*@pCAS_ind/100>0 and
(@Baza_CAS_ind-@Baza_CASI_ant)*@pCAS_ind/100+@Baza_CASI_ant*@pCAS_ind/100<1 then 1 else round((@Baza_CAS_ind-@Baza_CASI_ant)*@pCAS_ind/100,0)+round(@Baza_CASI_ant*@pCAS_ind/100,0) end) -- Deducere personala Set @Ded_baza=0 if @GrupaMP not in ('O','P') and @Tip_colabP<>'FDP'
Begin
Set @Venit_deducere=@Venit_total-(case when @Imps_H=1 then @Suma_impoz else 0 end)-(case when @Buget=1 and @Actionar='1' then @Suma_imp_sep else 0 end)
exec calcul_deducere @Venit_deducere, @Nr_pers_intr, @Ded_baza output
If @Ore_justif<@Ore_luna and @Chind_pont=1 Begin Set @Ded_baza=@Ded_baza*@Ore_justif/(@Ore_luna*(case when @GrupaMP='C' then @RegimL_pontaj/8 else 1 end)) Select @Ded_baza=(round(@Ded_baza,0,1)+1)*10 where @Venit_deducere>@Venit_brut_cu_ded and @Venit_deducere<3000 and @Ded_baza>@Venit_brut_fara_ded and @Ded_baza % 10<>0
End
Select @Ded_baza=0 where day(@Data_angajarii)<>1 and month(@Data_angajarii)=month(@DataS) and year(@Data_angajarii)=year(@DataS) and @Drumor=0 and @Chind_lunacrt=0
and (@Dafora=0 or @Tip_ded_somajP=6)
End
Set @Baza_impO=(case when @Buget=1 and @Actionar='1' then round(@Suma_imp_sep*(1-@As_sanatateP/10/100-@pCAS_ind/100),0) else 0 end)
Set @Baza_impH=(case when @Imps_H=1 then round(@Suma_impoz*(case when @NuCASS_H=1 then 1 else 1-@As_sanatateP/10/100 end),0) else 0 end)
Set @Baza_N=round(@Suma_neimp*(1-(case when @NuASS_N=1 then 0 else @As_sanatateP/10/100+@pCAS_ind/100 end)),0)
Select @Venit_net_in_imp=@Venit_total-@SomajI-@CASSI-@CAS_ind-@Baza_N-@Baza_impH-@Baza_impO where @Venit_total>0
Select @Ded_baza=@Venit_net_in_imp where @Chind_vnet=1 and @Ded_baza>@Venit_net_in_imp and @Ded_baza<>0
-- Pensie facultativa
Set @Ded_sindicat=@Ret_sindicat*(case when @Sind_proc=1 and @Proc_sind>1 then 1/@Proc_sind else 1 end)
Set @Ded_pensie=0
if @Pensie_max_ded-@Pensie_ded_ant>0 and not(@Ded_baza>@Venit_net_in_imp)
Begin
Set @Ded_pensie=dbo.valoare_minima((case when @Pensie_ded_lunar=0 then @Pensie_luna else @Pensie_ded_lunar end),@Pensie_max_ded-@Pensie_ded_ant,@Ded_pensie)
Set @Ded_pensie=(case when @Venit_net_in_imp-@Ded_baza-@Ded_sindicat<@Ded_pensie then @Venit_net_in_imp-@Ded_baza-@Ded_sindicat else @Ded_pensie end) Select @Ded_pensie=0 where @Ded_pensie<0 End -- Venit baza de calcul Set @Venit_baza=@Venit_net_in_imp-@Ded_sindicat-@Ded_baza-@Ded_pensie-@Somaj_tehnic Select @Venit_baza=@Venit_baza-(@Indcm_8915-@Zcm_8915*@Salar_minim/((case when @Dafora=1 then @Ore_luna_cm else @Ore_luna end)/8)*@pCAS_ind/100) where @Indcm_8915<>0
Select @Venit_baza=0 where @Venit_baza<0 Select @Venit_baza=(case when @NuRoT_BI=1 then round(@Venit_baza,0) else round(@Venit_baza,0,1) end) -- Impozit Set @Impozit=0 Select @Impozit=dbo.fCalcul_impozit_salarii(@Venit_baza, 0, @Impozit) where @Venit_baza>0 and @Tip_impozitareP<>'3' and @Grad_invalidP not in ('1','2')
Select @Impozit=0 where @Tip_impozitareP='3' or (@Grad_invalidP in ('1','2'))
Set @Impozit_sep=0
If @Suma_imp_sep>0 and @Buget=1 and @Actionar='1' and @Tip_impozitareP<>'3' and @Grad_invalidP not in ('1','2')
Begin
Select @Impozit_sep=dbo.fCalcul_impozit_salarii(@Baza_impO,0,@Impozit_sep)
Set @Impozit=@Impozit+@Impozit_sep
Set @Venit_baza=@Venit_baza+@Baza_impO
End
If @Suma_impoz>0 and @Imps_H=1 and @Tip_impozitareP<>'3' and @Grad_invalidP not in ('1','2')
Begin
Select @Impozit_H=dbo.fCalcul_impozit_salarii(@Baza_impH,0,@Impozit_H)
Set @Impozit=@Impozit+@Impozit_H
Set @Venit_baza=@Venit_baza+@Baza_impH
End
-- Venit net
Select @Venit_net=0
Select @VENIT_NET=@Venit_net_in_imp-@Impozit+@Baza_N+@Baza_impH+@Baza_impO where @Venit_total>0
Select @VENIT_NET=@Suma_neimp where @Venit_total>0 and @NuASS_N=1
Select @VENIT_NET=@VENIT_NET+round(@CorU*(1-(case when @VENIT_NET=0 then @pCAS_ind/100 else 0 end)),0) where @CorU_RP=1 and @CAS_U=1 and @CorU<>0
Select @VENIT_NET=@Venit_net_in_imp+round(@Suma_imp_sep*(1-@As_sanatateP/10/100-@pCAS_ind/100),0) where @Suma_imp_sep>0 and @Buget=1 and @Actionar='1'
Select @VENIT_NET=0 where @Dafora=1 and @Venit_net_in_imp<0 -- Deducere somaj Set @Ded_somaj=0 if (@Mod_angajareP='N' and @Tip_ded_somajP in (1,2,3,4) or @Mod_angajareP='D' and @Tip_ded_somajP=7) and dbo.eom(@DataE_conv_somaj)>=@DataS
Begin
Select @Zile_ded_somaj=dbo.Zile_lucratoare((case when day(@DataI_conv_somaj)<>1 and month(@DataI_conv_somaj)=month(@DataS) and year(@DataI_conv_somaj)=year(@DataS) then @DataI_conv_somaj else @DataJ end),
(case when day(@DataE_conv_somaj)<>1 and month(@DataE_conv_somaj)=month(@DataS) and year(@DataE_conv_somaj)=year(@DataS) then @DataE_conv_somaj else (case when @Plecat=1 and month(@Data_plecP)=month(@DataS) and year(@Data_plecP)=year(@DataS) then @Data_plecP else @DataS end) end))
where (day(@DataI_conv_somaj)<>1 and month(@DataI_conv_somaj)=month(@DataS) and year(@DataI_conv_somaj)=year(@DataS) or day(@DataE_conv_somaj)<>1 and month(@DataE_conv_somaj)=month(@DataS) and year(@DataE_conv_somaj)=year(@DataS)
or @Plecat=1 and month(@Data_plecP)=month(@DataS) and year(@Data_plecP)=year(@DataS))
Select @ZileSC_ded_somaj=(@Ore_CM+@Ore_CFS+@Invoiri+@Nemotivate+@Ore_somaj_tehnic+(case when @Tip_ded_somajP=7 then @Ore_CO else 0 end))/(case when @RegimL=0 then 8 else @RegimL end)
Select @Ded_somaj=(case when @Tip_ded_somajP in (1,2) then 1 when @Tip_ded_somajP=3 then 1.2 when @Tip_ded_somajP=4 then 1.5 when @Tip_ded_somajP=7 then 0.5 else 0 end)*
(case when dbo.eom(@DataI_conv_somaj)=@DataS or @Tip_ded_somajP in (1,7) then
(case when @DataI_conv_somaj>='10/14/2008' and not(@Tip_ded_somajP in (2,3,4) and @Data_angajarii<'10/14/2008') then @Indref_somaj else @Salar_minim end) else dbo.iauParLN(@Data_angajarii,'PS',(case when @DataI_conv_somaj>='10/14/2008' and not(@Tip_ded_somajP in (2,3,4) and @Data_angajarii<'10/14/2008') then 'SOMAJ-ISR' else 'S-MIN-BR' end)) end)*(case when @GrupaMP='C' then @RegimL_pontaj/8 else 1 end)* (case when @Zile_ded_somaj<>0 or @ZileSC_ded_somaj<>0 then (case when @Zile_ded_somaj<>0 then (case when @Zile_ded_somaj-@ZileSC_ded_somaj>0 then @Zile_ded_somaj-@ZileSC_ded_somaj else 0 end) else @Ore_luna/8-@ZileSC_ded_somaj end)/(@Ore_luna/8) else 1 end)
End
-- Contributii angajator
Set @CAS_unit=round(@Baza_CAS_cond_spec*@pCAS_gr1/100+@Baza_CAS_cond_deoseb*@pCAS_gr2/100+@Baza_CAS_cond_normale*@pCAS_gr3/100,2)
Set @Baza_CASS_unit=@Venit_total-(@Indcm_cas_19+@CMCAS)-(case when @Plastidr=1 and @CASSimps_K=1 then @Cons_admin else 0 end)-(case when @NuCASS_H=1 then @Suma_impoz else 0 end)-@Somaj_tehnic
Select @Baza_CASS_unit=0 where (@CASS_colab=0 or @As_sanatateP=0) and @GrupaMP in ('O','P')
Set @CASS_unit=round(@Baza_CASS_unit*@pCASSU/100,2)
Set @Baza_somajU=@Baza_somajI
Select @Baza_somajU=0 where (@GrupaMP in ('O','P') or @Pasmatex=1 or @Tip_ded_somajP=5) and @Somaj_1P=0
Set @Somaj_unit=round(@Baza_somajU*@pSomajU/100,2)
Set @Baza_FondGar=@Baza_somajI
Select @Baza_FondGar=0 where @GrupaMP in ('O','P') or @Inst_publ=1
Set @Fond_gar=round(@Baza_FondGar*@pFond_gar/100,2)
Set @Baza_CCI=@Venit_total-(@Indcm_cas_19+@CMCAS)+@Ind_Fambp-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)-
(case when @CASSimps_K=1 then @Cons_admin else 0 end)-@Alocatii_hrana-@Somaj_tehnic
Select @Baza_CCI=(case when @Baza_CCI>12*@Salar_minim then 12*@Salar_minim else @Baza_CCI end) where @GrupaMP='O' and @CCI_colabO=1 or @GrupaMP='P' and @CCI_colabP=1
Select @Baza_CCI=0 where @GrupaMP='O' and @CCI_colabO=0 or @GrupaMP='P' and @CCI_colabP=0
Set @CCI=round(@Baza_CCI*@pCCI/100,2)
Set @Baza_Fambp=@Baza_CAS_ind
Select @Baza_Fambp=0 where @GrupaMP='O'
Set @Fambp=round(@Baza_Fambp*@pFambp/100,2)
Set @Baza_ITM=@Venit_total-(@Indcm_cas_19+@CMCAS)-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)-(case when @CASSimps_K=1 then @Cons_admin else 0 end)
Select @Baza_ITM=0 where @NuITM_colab=1 and @GrupaMP in ('O','P') or @NuITM_pens=1 and @Tip_ded_somajP=5
Set @ITM=round(@Baza_ITM*@pITM/100,2)
Set @Rest_de_plata=@Venit_net
Set @Coef_tot_ded=0
exec scriuNet_salarii @DataS, @DataS, @Marca, @Locm, @VENIT_TOTAL, 0, 0, 0, 0, 0, @Impozit, @CAS_ind, @SomajI, @CASS_Fambp_sal, @CASSI,
@Baza_somajI, @VENIT_NET, 0, 0, 0, 0, 0, 0, @REST_DE_PLATA, @CAS_unit, @Somaj_unit, @Fambp, @ITM, @CASS_unit, @Coef_tot_ded, @Grad_invalidP, @Tip_ded_somajP, @Alte_surseP,
@VENIT_NET_IN_IMP, @Ded_baza, @CCI, @VENIT_BAZA, @Ded_somaj, @Baza_CAS_ind, @Baza_CAS_cond_normale, @Baza_CAS_cond_deoseb, @Baza_CAS_cond_spec
exec scriuNet_salarii @DataJ, @DataJ, @Marca, @Locm, 0, 0, 0, 0, 0, 0, @Impozit_sep, 0, 0, @CASS_Fambp_ang, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, @CASCM, @Fond_gar, 0, 0, 0, 0, '', 0, 0,
0, @Ded_pensie, @CCI_Fambp, 0, 0, @Baza_CCI, @Baza_CASCM_CN, @Baza_CASCM_CD, @Baza_CASCM_CS
Select @Baza_CCI=@Baza_CCI*@Coef_CCI-@Ind_Fambp
fetch next from cursor_net into @Data, @Marca, @Ore_lucrate, @Ore_noapte, @Ore_RN, @Ore_IT, @Ore_obligatii,
@Ore_CFS, @Ore_CO, @Ore_CM, @Invoiri, @Nemotivate, @Ore_supl, @Ore_justif,
@Baza_somaj_1, @Ind_FAMBP, @CMunitate, @CMCAS, @Diurna, @Suma_impoz, @Cons_admin,
@Suma_imp_sep, @Aj_deces, @Venit_total, @Spor_specific, @Spor_cond_1, @Spor_cond_2, @Spor_cond_3, @Spor_cond_4, @Spor_cond_5, @Spor_cond_6,
@Salar_orar, @RegimL, @Locm, @CorQ, @CorT, @CorU, @Ret_sindicat, @RegimL_pontaj, @GrupaM_pontaj,
@Somaj_1P, @As_sanatateP, @Tip_impozitareP, @GrupaMP, @Salar_de_incadrareP, @Salar_de_bazaP,
@Tip_salarizareP, @Ind_condP, @Spor_vechimeP, @Spor_de_noapteP, @Spor_sist_prgP, @Spor_functie_suplP,
@Spor_specificP, @Spor_conditii_1P, @Spor_conditii_2P, @Spor_conditii_3P, @Spor_conditii_4P,
@Spor_conditii_5P, @Spor_conditii_6P, @Tip_colabP, @Alte_surseP, @Grad_invalidP, @Tip_ded_somajP,
@Data_angajarii, @Plecat, @Mod_angajareP, @Data_plecP, @Sindicalist,
@DataI_conv_somaj, @DataE_conv_somaj, @Nr_pers_intr,
@Baza_cond_normale, @Baza_cond_deosebite, @Baza_cond_speciale,
@Indcm_unit_19, @Indcm_cas_19, @Ore_luna_cm, @Indcm, @Indcm_cas_18, @Zcm_18, @Zcm_18_ant, @Baza_casi_ant,
@Baza_cascm_ant, @Zcm_2341011, @Indcm_234, @Indcm_unit_234, @Zcm15, @Zcm_8915, @Indcm_8915,
@Zcm_78, @Indcm_78, @Indcm_somaj, @Ingrijire_copil_sarcina, @Actionar,
@uMarca2CNP, @uMarca2CNPCM, @CNP, @Pensie_max_ded, @Pensie_ded_lunar,
@Pensie_ded_ant, @Pensie_luna, @Salarii_comp, @Alocatii_hrana, @Somaj_tehnic, @Ore_somaj_tehnic, @Suma_neimp
End
drop table ##cursor_net
close cursor_net
Deallocate cursor_net
End
--exec psCalcul_venit_net '12/01/2009', '12/31/2009', '', '', 0
--drop table ##cursor_net
/*
select a.marca, a.Baza_CAS_cond_norm, b.Baza_CAS_cond_norm from net a, net1209 b
where a.data='12/31/2009' and a.data=b.data and a.marca=b.marca
and a.Baza_CAS_cond_norm<>b.Baza_CAS_cond_norm
*/
procedure [dbo].[psCalcul_venit_net]
@DataJ datetime, @DataS datetime, @MarcaJos char(6), @LocmJos char(9), @Inversare int
As
Begin
declare @HostID char(8), @Salar_minim float, @Indref_somaj float, @Ore_luna int,
@Sind_proc int, @Proc_sind float, @pCAS_ind float, @pCASS_ind float, @pSomaj_ind float, @pSomajI float, @pCAS_gr3 float, @pCAS_gr2 float, @pCAS_gr1 float,
@pCCI float, @Coef_CAS float, @Coef_CCI float, @pCASSU float, @pSomajU float, @pFond_gar float, @pFambp float, @Calcul_ITM int, @pITM float,
@Buget int, @Inst_publ int, @CASS_colab int, @NuITM_colab int, @NuITM_pens int,
@CCI_colabP int, @CCI_colabO int, @Chind_pont int, @Chind_lunacrt int, @Chind_vnet int, @NuRoT_BI int,
@Comp_sal_net int, @Salar_net_valuta int,
@NuCAS_H int, @NuCASS_H int, @Imps_H int, @CASSimps_K int, @Somaj_K int, @NuASS_J int, @CAS_J int, @NuASS_N int, @NuASSA_N int, @CorU_RP int, @CAS_U int,
@Venit_brut_cu_ded float, @Venit_brut_fara_ded float, @Dafora int, @Pasmatex int, @Drumor int, @Plastidr int
Set @HostID=isnull((select convert(char(8), abs(convert(int, host_id())))),'')
Set @Salar_minim=dbo.iauParLN(@DataS,'PS','S-MIN-BR')
Set @Indref_somaj=dbo.iauParLN(@DataS,'PS','SOMAJ-ISR')
Set @Ore_luna=dbo.iauParLN(@DataS,'PS','ORE_LUNA')
Exec Luare_date_par 'PS', 'SIND%', @Sind_proc OUTPUT, @Proc_sind OUTPUT, ''
Set @pCAS_ind=dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCASS_ind=dbo.iauParLN(@DataS,'PS','CASSIND')
Set @pSomaj_ind=dbo.iauParLN(@DataS,'PS','SOMAJIND')
Set @pCAS_gr3=dbo.iauParLN(@DataS,'PS','CASGRUPA3')-dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCAS_gr2=dbo.iauParLN(@DataS,'PS','CASGRUPA2')-dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCAS_gr1=dbo.iauParLN(@DataS,'PS','CASGRUPA1')-dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCCI=dbo.iauParLN(@DataS,'PS','COTACCI')
Set @Coef_CAS=dbo.iauParN('PS','COEFCAS')
Set @Coef_CAS=@Coef_CAS/1000000
Set @Coef_CCI=dbo.iauParN('PS','COEFCCI')
Set @Coef_CCI=@Coef_CCI/1000000
Set @pCASSU=dbo.iauParLN(@DataS,'PS','CASSUNIT')
Set @pSomajU=dbo.iauParLN(@DataS,'PS','3.5%SOMAJ')
Set @pFond_gar=dbo.iauParLN(@DataS,'PS','FONDGAR')
Set @pFambp=dbo.iauParLN(@DataS,'PS','0.5%ACCM')
Set @Calcul_ITM=dbo.iauParL('PS','1%-CAMERA')
Set @pITM=dbo.iauParLN(@DataS,'PS','1%-CAMERA')
Set @Buget=dbo.iauParL('PS','UNITBUGET')
Set @Inst_publ=dbo.iauParL('PS','INSTPUBL')
Set @CASS_colab=dbo.iauParL('PS','CALFASC')
Set @NuITM_colab=dbo.iauParL('PS','NCALPCMC')
Set @NuITM_pens=dbo.iauParL('PS','NCALPCMPE')
Set @CCI_colabP=dbo.iauParL('PS','CCICOLAB')
Set @CCI_colabO=dbo.iauParL('PS','CCICOLABO')
Set @Chind_pont=dbo.iauParL('PS','CHINDPON')
Set @Chind_lunacrt=dbo.iauParL('PS','CHINDLCRT')
Set @Chind_vnet=dbo.iauParL('PS','CHINDVEN')
Set @NuRoT_BI=dbo.iauParL('PS','BAZANEROT')
Set @Comp_sal_net=dbo.iauParL('PS','COMPSALN')
Set @Salar_net_valuta=dbo.iauParL('PS','SALNETV')
Set @NuCAS_H=dbo.iauParL('PS','NUCAS-H')
Set @NuCASS_H=dbo.iauParL('PS','NUASS-H')
Set @Imps_H=dbo.iauParL('PS','IMPSEP-H')
Set @NuASS_J=dbo.iauParL('PS','NUASS-J')
Set @CAS_J=dbo.iauParL('PS','CAS-J')
Set @CASSimps_K=dbo.iauParL('PS','ASSIMPS-K')
Set @Somaj_K=dbo.iauParL('PS','SOMAJ-K')
Set @NuASS_N=dbo.iauParL('PS','NUASS-N')
Set @NuASSA_N=dbo.iauParL('PS','NUASSA-N')
Set @CorU_RP=dbo.iauParL('PS','ADRPL-U')
Set @CAS_U=dbo.iauParL('PS','CALCAS-U')
Set @Venit_brut_cu_ded=dbo.iauParN('PS','VBCUDEDP')
Set @Venit_brut_fara_ded=dbo.iauParN('PS','VBFDEDP')
Set @Dafora=dbo.iauParL('SP','DAFORA')
Set @Pasmatex=dbo.iauParL('SP','PASMATEX')
Set @Drumor=dbo.iauParL('SP','DRUMOR')
Set @Plastidr=dbo.iauParL('SP','PLASTIDR')
declare @Data datetime, @Marca char(6), @Ore_lucrate int, @Ore_noapte int,
@ore_RN int, @Ore_IT int, @Ore_obligatii int, @Ore_CFS int, @Ore_CO int, @Ore_CM int, @Invoiri int, @Nemotivate int, @Ore_supl int,
@Ore_justif int, @Baza_somaj_1 float, @Ind_FAMBP float, @CMunitate float, @CMcas float,
@Diurna float, @Suma_impoz float, @Cons_admin float, @Suma_imp_sep float,
@Aj_deces float, @Venit_total float, @Spor_specific float, @Spor_cond_1 float, @Spor_cond_2 float,
@Spor_cond_3 float, @Spor_cond_4 float, @Spor_cond_5 float, @Spor_cond_6 float,
@Salar_orar float, @RegimL float, @Locm char(9),
@CorQ float, @CorT float, @CorU float, @Ret_sindicat float,
@RegimL_pontaj float, @RegimL_brut float, @GrupaM_pontaj char(1), @Somaj_1P int, @As_sanatateP float,
@Tip_impozitareP char(1), @GrupaMP char(1), @Salar_de_incadrareP float,
@Salar_de_bazaP float, @Tip_salarizareP char(1), @Ind_condP float,
@Spor_vechimeP float, @Spor_de_noapteP float, @Spor_sist_prgP float,
@Spor_functie_suplP float, @Spor_specificP float, @Spor_conditii_1P float, @Spor_conditii_2P float,
@Spor_conditii_3P float, @Spor_conditii_4P float, @Spor_conditii_5P float, @Spor_conditii_6P float,
@Tip_colabP char(3), @Alte_surseP char(1), @Grad_invalidP char(1), @Tip_ded_somajP float,
@Data_angajarii datetime, @Plecat char(1), @Mod_angajareP char(1),
@Data_plecP datetime, @Sindicalist char(1),
@DataI_conv_somaj datetime, @DataE_conv_somaj datetime, @Nr_pers_intr int,
@Baza_cond_normale float, @Baza_cond_deosebite float, @Baza_cond_speciale float,
@Indcm_unit_19 float, @Indcm_cas_19 float, @Ore_luna_cm int, @Indcm float,
@Indcm_cas_18 float, @Zcm_18 int, @Zcm_18_ant int, @Baza_casi_ant float,
@Baza_cascm_ant float, @Zcm_2341011 int, @Indcm_234 float, @Indcm_unit_234 float,
@Zcm15 int, @Zcm_8915 int, @Indcm_8915 float, @Zcm_78 int, @Indcm_78 float, @Indcm_somaj float,
@Ingrijire_copil_sarcina int, @Actionar char(1),
@uMarca2CNP int, @uMarca2CNPCM int, @CNP char(13),
@Pensie_max_ded float, @Pensie_ded_lunar float, @Pensie_ded_ant float, @Pensie_luna float,
@Salarii_comp float, @Alocatii_hrana float, @Somaj_tehnic float, @Ore_somaj_tehnic int, @Suma_neimp float
declare @Baza_CASCM float, @Baza_CASCM_CN float, @Baza_CASCM_CD float, @Baza_CASCM_CS float, @CASCM float,
@Baza_CAS_CN float, @Baza_CAS_CD float, @Baza_CAS_CS float, @CCI_Fambp float,
@Ore_somaj int, @Zile_CM_susp int, @IndCM_susp float, @Baza_somajI float, @SomajI float, @Baza_CASSI float, @CASSI float, @CASS_Fambp_sal float, @CASS_Fambp_ang float,
@Baza_CAS_ind float, @CAS_ind float, @Venit_deducere float, @Ded_baza decimal(10), @Venit_net_in_imp float,
@Ded_pensie float, @Ded_sindicat float, @Venit_baza float, @Impozit float, @Baza_impO float, @Impozit_sep float,
@Baza_impH float, @Impozit_H float, @Baza_N float, @VENIT_NET float,
@CAS_unit float, @Baza_CASS_unit float, @CASS_unit float, @Baza_somajU float, @Somaj_unit float,
@Baza_CCI float, @CCI float, @Baza_Fambp float, @Fambp float, @Baza_FondGar float, @Fond_gar float, @ITM float,
@Rest_de_plata float, @Coef_tot_ded int, @Zile_ded_somaj int, @ZileSC_ded_somaj int, @Ded_somaj float,
@Baza_ITM float, @Baza_CAS_cond_normale float, @Baza_CAS_cond_deoseb float, @Baza_CAS_cond_spec float
exec psCreare_cursor_venit_net @DataJ, @DataS, @MarcaJos, @LocmJos
declare cursor_net cursor for
Select Data, Marca, Ore_lucrate, Ore_noapte, Ore_RN, Ore_IT, Ore_obligatii,
Ore_CFS, Ore_CO, Ore_CM, Invoiri, Nemotivate, Ore_supl, Ore_justif,
Baza_somaj_1, Ind_FAMBP, CMunitate, CMCAS, Diurna, Suma_impoz, Cons_admin,
Suma_imp_sep, Aj_deces, Venit_total, Spor_specific, Spor_cond_1, Spor_cond_2, Spor_cond_3, Spor_cond_4, Spor_cond_5, Spor_cond_6,
Salar_orar, RegimL, Locm, CorQ, CorT, CorU, Ret_sindicat, RegimL_pontaj, GrupaM_pontaj,
Somaj_1P, As_sanatateP, Tip_impozitareP, GrupaM_pers, Salar_de_incadrareP, Salar_de_bazaP,
Tip_salarizareP, Ind_condP, Spor_vechimeP, Spor_de_noapteP, Spor_sist_prgP, Spor_functie_suplP,
Spor_specificP, Spor_conditii_1P, Spor_conditii_2P, Spor_conditii_3P, Spor_conditii_4P,
Spor_conditii_5P, Spor_conditii_6P, Tip_colabP, Alte_surseP, Grad_invalidP, Tip_ded_somajP,
Data_angajarii, Plecat, Mod_angajareP, Data_plecP, Sindicalist,
DataI_conv_somaj, DataE_conv_somaj, Nr_pers_intr,
Baza_cond_normale, Baza_cond_deosebite, Baza_cond_speciale,
Indcm_unit_19, Indcm_cas_19, Ore_luna_cm, Indcm, Indcm_cas_18, Zcm_18, Zcm_18_ant, Baza_casi_ant,
Baza_cascm_ant, Zcm_2341011, Indcm_234, Indcm_unit_234, Zcm15, Zcm_8915, Indcm_8915,
Zcm_78, Indcm_78, Indcm_somaj, Ingrijire_copil_sarcina, Actionar,
uMarca2CNP, uMarca2CNPCM, CNP, Pensie_max_ded, Pensie_ded_lunar,
Pensie_ded_ant, Pensie_luna, Salarii_comp, Alocatii_hrana, Somaj_tehnic, Ore_somaj_tehnic, Suma_neimp
from ##cursor_net
where hostid=@HostID
open cursor_net
fetch next from cursor_net into @Data, @Marca, @Ore_lucrate, @Ore_noapte, @Ore_RN, @Ore_IT, @Ore_obligatii,
@Ore_CFS, @Ore_CO, @Ore_CM, @Invoiri, @Nemotivate, @Ore_supl, @Ore_justif,
@Baza_somaj_1, @Ind_FAMBP, @CMunitate, @CMCAS, @Diurna, @Suma_impoz, @Cons_admin,
@Suma_imp_sep, @Aj_deces, @Venit_total, @Spor_specific, @Spor_cond_1, @Spor_cond_2, @Spor_cond_3, @Spor_cond_4, @Spor_cond_5, @Spor_cond_6,
@Salar_orar, @RegimL, @Locm, @CorQ, @CorT, @CorU, @Ret_sindicat, @RegimL_pontaj, @GrupaM_pontaj,
@Somaj_1P, @As_sanatateP, @Tip_impozitareP, @GrupaMP, @Salar_de_incadrareP, @Salar_de_bazaP,
@Tip_salarizareP, @Ind_condP, @Spor_vechimeP, @Spor_de_noapteP, @Spor_sist_prgP, @Spor_functie_suplP,
@Spor_specificP, @Spor_conditii_1P, @Spor_conditii_2P, @Spor_conditii_3P, @Spor_conditii_4P,
@Spor_conditii_5P, @Spor_conditii_6P, @Tip_colabP, @Alte_surseP, @Grad_invalidP, @Tip_ded_somajP,
@Data_angajarii, @Plecat, @Mod_angajareP, @Data_plecP, @Sindicalist,
@DataI_conv_somaj, @DataE_conv_somaj, @Nr_pers_intr,
@Baza_cond_normale, @Baza_cond_deosebite, @Baza_cond_speciale,
@Indcm_unit_19, @Indcm_cas_19, @Ore_luna_cm, @Indcm, @Indcm_cas_18, @Zcm_18, @Zcm_18_ant, @Baza_casi_ant,
@Baza_cascm_ant, @Zcm_2341011, @Indcm_234, @Indcm_unit_234, @Zcm15, @Zcm_8915, @Indcm_8915,
@Zcm_78, @Indcm_78, @Indcm_somaj, @Ingrijire_copil_sarcina, @Actionar,
@uMarca2CNP, @uMarca2CNPCM, @CNP, @Pensie_max_ded, @Pensie_ded_lunar,
@Pensie_ded_ant, @Pensie_luna, @Salarii_comp, @Alocatii_hrana, @Somaj_tehnic, @Ore_somaj_tehnic, @Suma_neimp
While @@fetch_status = 0
Begin
update net set Venit_total=0, VENIT_NET=0, Impozit=0, Rest_de_plata=0, Asig_sanatate_din_net=0,
Pensie_suplimentara_3=0, Somaj_1=0, Asig_sanatate_din_impozit=0, Asig_sanatate_din_CAS=0, Coef_tot_ded=0,
VEN_NET_IN_IMP=0, Ded_baza=0, Ded_suplim=0, VENIT_BAZA=0, Chelt_prof=0,
Debite_externe=0, Rate=0, Debite_interne=0, Cont_curent=0, CAS=0, Somaj_5=0, Fond_de_risc_1=0,
Camera_de_munca_1=0, Asig_sanatate_pl_unitate=0, Baza_CAS=0
where Data=@Data and Marca=@Marca
update net set Somaj_5=0, Ded_baza=0, Ded_suplim=0
where Data=dbo.bom(@Data) and Marca=@Marca
Set @Baza_CASCM=(case when @uMarca2CNPCM>0 and charindex(@GrupaMP,'NDS')=0 then 0 else
(@Zcm_18+@Zcm15-@Zcm_18_ant)*@Salar_minim/((case when @Dafora=1 then @Ore_luna_cm else @Ore_luna end)/8)+@Baza_cascm_ant end)
Set @Baza_CASCM_CN=(case when @GrupaM_pontaj='N' or @GrupaMP in ('N','P') then @Baza_CASCM else 0 end)
Set @Baza_CASCM_CD=(case when @GrupaM_pontaj='D' or @GrupaMP='D' then @Baza_CASCM else 0 end)
Set @Baza_CASCM_CS=(case when @GrupaM_pontaj='S' or @GrupaMP='S' then @Baza_CASCM else 0 end)
Set @CASCM=round(@Baza_CASCM_CS*@pCAS_gr1/100+@Baza_CASCM_CD*@pCAS_gr2/100+@Baza_CASCM_CN*@pCAS_gr3/100,0)
Set @CCI_Fambp=round(@Ind_FAMBP*@pCCI/100,0)
Set @Baza_CAS_cond_normale=round(@Baza_cond_normale*@Coef_CAS,0)
Set @Baza_CAS_cond_deoseb=round(@Baza_cond_deosebite*@Coef_CAS,0)
Set @Baza_CAS_cond_spec=round(@Baza_cond_speciale*@Coef_CAS,0)
Select @Zile_CM_susp=0, @IndCM_susp=0
if @Indcm<>0 or @Zcm_18+@Zcm_2341011+@Zcm15<>0
Begin
exec calcul_ZileCM_suspendare @Marca, @DataJ, @DataS, @Zile_CM_susp output, @IndCM_susp output
End
if @Venit_total>0
Begin
Set @Ore_somaj=@Ore_justif-@Zcm_78*@RegimL-(case when @Zile_CM_susp>0 then @Zile_CM_susp*@RegimL else 0 end)
-@Ore_CFS-@Invoiri-@Nemotivate-@Ore_somaj_tehnic
Set @pSomajI=(case when @Somaj_1P=1 then @pSomaj_ind/100 else 0 end)
Set @Baza_somajI=@Venit_total-@Indcm_78-(case when @IndCM_susp=0 then @Indcm_somaj else @IndCM_susp end)-@Salarii_comp-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)
-(case when @CASSimps_K=1 and @Somaj_K=0 then @Cons_admin else 0 end)-@Somaj_tehnic
Select @Baza_somajI=0 where @Ore_somaj=0
Set @SomajI=(case when @Baza_somajI*@pSomajI>0 and @Baza_somajI*@pSomajI<1 then 1 else round(@Baza_somajI*@pSomajI,0) end) End Set @Baza_CASSI=round(@Venit_total-(case when not((@Comp_sal_net=1 or @Salar_net_valuta=1) and @Inversare=1) then @Indcm_unit_19+@CMUnitate+@Indcm_cas_19+@CMCAS else 0 end) -(case when @NuASS_J=1 then @Diurna else 0 end)-(case when @NuCASS_H=1 then @Suma_impoz else 0 end)-(case when @NuASSA_N=1 then @Suma_neimp else 0 end)-@Somaj_tehnic,0) Set @CASSI=(case when @As_sanatateP<>0 then (case when @Baza_CASSI*@As_sanatateP/10/100>0 and @Baza_CASSI*@As_sanatateP/10/100<1 then 1 else round(@Baza_CASSI*@As_sanatateP/10/100,0) end)+@CorT end) Set @CASS_Fambp_sal=round(@Ind_Fambp*@pCASS_ind/100,0) Set @CASS_Fambp_ang=round(@Indcm_unit_234*@pCASS_ind/100,0) Select @Baza_CAS_ind=@Venit_total-(case when not((@Comp_sal_net=1 or @Salar_net_valuta=1) and @Inversare=1) then @Indcm_unit_19+@CMUnitate+@Indcm_cas_19+@CMCAS else 0 end) -(case when @CAS_J=1 or @Pasmatex=1 then 0 else (case when @Dafora=1 then -1 else 1 end)*@Diurna end) -(case when @CASSimps_K=1 and @GrupaMP<>'P' then @Cons_admin else 0 end)-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)+
(case when @CAS_U=1 then @CorU else 0 end)-@Somaj_tehnic
where @Baza_CAS_cond_normale+@Baza_CAS_cond_deoseb+@Baza_CAS_cond_spec<>0
Set @Baza_CAS_ind=round(@Baza_CAS_ind+@Baza_CASCM,0)
Set @CAS_ind=(case when (@Baza_CAS_ind-@Baza_CASI_ant)*@pCAS_ind/100+@Baza_CASI_ant*@pCAS_ind/100>0 and
(@Baza_CAS_ind-@Baza_CASI_ant)*@pCAS_ind/100+@Baza_CASI_ant*@pCAS_ind/100<1 then 1 else round((@Baza_CAS_ind-@Baza_CASI_ant)*@pCAS_ind/100,0)+round(@Baza_CASI_ant*@pCAS_ind/100,0) end) -- Deducere personala Set @Ded_baza=0 if @GrupaMP not in ('O','P') and @Tip_colabP<>'FDP'
Begin
Set @Venit_deducere=@Venit_total-(case when @Imps_H=1 then @Suma_impoz else 0 end)-(case when @Buget=1 and @Actionar='1' then @Suma_imp_sep else 0 end)
exec calcul_deducere @Venit_deducere, @Nr_pers_intr, @Ded_baza output
If @Ore_justif<@Ore_luna and @Chind_pont=1 Begin Set @Ded_baza=@Ded_baza*@Ore_justif/(@Ore_luna*(case when @GrupaMP='C' then @RegimL_pontaj/8 else 1 end)) Select @Ded_baza=(round(@Ded_baza,0,1)+1)*10 where @Venit_deducere>@Venit_brut_cu_ded and @Venit_deducere<3000 and @Ded_baza>@Venit_brut_fara_ded and @Ded_baza % 10<>0
End
Select @Ded_baza=0 where day(@Data_angajarii)<>1 and month(@Data_angajarii)=month(@DataS) and year(@Data_angajarii)=year(@DataS) and @Drumor=0 and @Chind_lunacrt=0
and (@Dafora=0 or @Tip_ded_somajP=6)
End
Set @Baza_impO=(case when @Buget=1 and @Actionar='1' then round(@Suma_imp_sep*(1-@As_sanatateP/10/100-@pCAS_ind/100),0) else 0 end)
Set @Baza_impH=(case when @Imps_H=1 then round(@Suma_impoz*(case when @NuCASS_H=1 then 1 else 1-@As_sanatateP/10/100 end),0) else 0 end)
Set @Baza_N=round(@Suma_neimp*(1-(case when @NuASS_N=1 then 0 else @As_sanatateP/10/100+@pCAS_ind/100 end)),0)
Select @Venit_net_in_imp=@Venit_total-@SomajI-@CASSI-@CAS_ind-@Baza_N-@Baza_impH-@Baza_impO where @Venit_total>0
Select @Ded_baza=@Venit_net_in_imp where @Chind_vnet=1 and @Ded_baza>@Venit_net_in_imp and @Ded_baza<>0
-- Pensie facultativa
Set @Ded_sindicat=@Ret_sindicat*(case when @Sind_proc=1 and @Proc_sind>1 then 1/@Proc_sind else 1 end)
Set @Ded_pensie=0
if @Pensie_max_ded-@Pensie_ded_ant>0 and not(@Ded_baza>@Venit_net_in_imp)
Begin
Set @Ded_pensie=dbo.valoare_minima((case when @Pensie_ded_lunar=0 then @Pensie_luna else @Pensie_ded_lunar end),@Pensie_max_ded-@Pensie_ded_ant,@Ded_pensie)
Set @Ded_pensie=(case when @Venit_net_in_imp-@Ded_baza-@Ded_sindicat<@Ded_pensie then @Venit_net_in_imp-@Ded_baza-@Ded_sindicat else @Ded_pensie end) Select @Ded_pensie=0 where @Ded_pensie<0 End -- Venit baza de calcul Set @Venit_baza=@Venit_net_in_imp-@Ded_sindicat-@Ded_baza-@Ded_pensie-@Somaj_tehnic Select @Venit_baza=@Venit_baza-(@Indcm_8915-@Zcm_8915*@Salar_minim/((case when @Dafora=1 then @Ore_luna_cm else @Ore_luna end)/8)*@pCAS_ind/100) where @Indcm_8915<>0
Select @Venit_baza=0 where @Venit_baza<0 Select @Venit_baza=(case when @NuRoT_BI=1 then round(@Venit_baza,0) else round(@Venit_baza,0,1) end) -- Impozit Set @Impozit=0 Select @Impozit=dbo.fCalcul_impozit_salarii(@Venit_baza, 0, @Impozit) where @Venit_baza>0 and @Tip_impozitareP<>'3' and @Grad_invalidP not in ('1','2')
Select @Impozit=0 where @Tip_impozitareP='3' or (@Grad_invalidP in ('1','2'))
Set @Impozit_sep=0
If @Suma_imp_sep>0 and @Buget=1 and @Actionar='1' and @Tip_impozitareP<>'3' and @Grad_invalidP not in ('1','2')
Begin
Select @Impozit_sep=dbo.fCalcul_impozit_salarii(@Baza_impO,0,@Impozit_sep)
Set @Impozit=@Impozit+@Impozit_sep
Set @Venit_baza=@Venit_baza+@Baza_impO
End
If @Suma_impoz>0 and @Imps_H=1 and @Tip_impozitareP<>'3' and @Grad_invalidP not in ('1','2')
Begin
Select @Impozit_H=dbo.fCalcul_impozit_salarii(@Baza_impH,0,@Impozit_H)
Set @Impozit=@Impozit+@Impozit_H
Set @Venit_baza=@Venit_baza+@Baza_impH
End
-- Venit net
Select @Venit_net=0
Select @VENIT_NET=@Venit_net_in_imp-@Impozit+@Baza_N+@Baza_impH+@Baza_impO where @Venit_total>0
Select @VENIT_NET=@Suma_neimp where @Venit_total>0 and @NuASS_N=1
Select @VENIT_NET=@VENIT_NET+round(@CorU*(1-(case when @VENIT_NET=0 then @pCAS_ind/100 else 0 end)),0) where @CorU_RP=1 and @CAS_U=1 and @CorU<>0
Select @VENIT_NET=@Venit_net_in_imp+round(@Suma_imp_sep*(1-@As_sanatateP/10/100-@pCAS_ind/100),0) where @Suma_imp_sep>0 and @Buget=1 and @Actionar='1'
Select @VENIT_NET=0 where @Dafora=1 and @Venit_net_in_imp<0 -- Deducere somaj Set @Ded_somaj=0 if (@Mod_angajareP='N' and @Tip_ded_somajP in (1,2,3,4) or @Mod_angajareP='D' and @Tip_ded_somajP=7) and dbo.eom(@DataE_conv_somaj)>=@DataS
Begin
Select @Zile_ded_somaj=dbo.Zile_lucratoare((case when day(@DataI_conv_somaj)<>1 and month(@DataI_conv_somaj)=month(@DataS) and year(@DataI_conv_somaj)=year(@DataS) then @DataI_conv_somaj else @DataJ end),
(case when day(@DataE_conv_somaj)<>1 and month(@DataE_conv_somaj)=month(@DataS) and year(@DataE_conv_somaj)=year(@DataS) then @DataE_conv_somaj else (case when @Plecat=1 and month(@Data_plecP)=month(@DataS) and year(@Data_plecP)=year(@DataS) then @Data_plecP else @DataS end) end))
where (day(@DataI_conv_somaj)<>1 and month(@DataI_conv_somaj)=month(@DataS) and year(@DataI_conv_somaj)=year(@DataS) or day(@DataE_conv_somaj)<>1 and month(@DataE_conv_somaj)=month(@DataS) and year(@DataE_conv_somaj)=year(@DataS)
or @Plecat=1 and month(@Data_plecP)=month(@DataS) and year(@Data_plecP)=year(@DataS))
Select @ZileSC_ded_somaj=(@Ore_CM+@Ore_CFS+@Invoiri+@Nemotivate+@Ore_somaj_tehnic+(case when @Tip_ded_somajP=7 then @Ore_CO else 0 end))/(case when @RegimL=0 then 8 else @RegimL end)
Select @Ded_somaj=(case when @Tip_ded_somajP in (1,2) then 1 when @Tip_ded_somajP=3 then 1.2 when @Tip_ded_somajP=4 then 1.5 when @Tip_ded_somajP=7 then 0.5 else 0 end)*
(case when dbo.eom(@DataI_conv_somaj)=@DataS or @Tip_ded_somajP in (1,7) then
(case when @DataI_conv_somaj>='10/14/2008' and not(@Tip_ded_somajP in (2,3,4) and @Data_angajarii<'10/14/2008') then @Indref_somaj else @Salar_minim end) else dbo.iauParLN(@Data_angajarii,'PS',(case when @DataI_conv_somaj>='10/14/2008' and not(@Tip_ded_somajP in (2,3,4) and @Data_angajarii<'10/14/2008') then 'SOMAJ-ISR' else 'S-MIN-BR' end)) end)*(case when @GrupaMP='C' then @RegimL_pontaj/8 else 1 end)* (case when @Zile_ded_somaj<>0 or @ZileSC_ded_somaj<>0 then (case when @Zile_ded_somaj<>0 then (case when @Zile_ded_somaj-@ZileSC_ded_somaj>0 then @Zile_ded_somaj-@ZileSC_ded_somaj else 0 end) else @Ore_luna/8-@ZileSC_ded_somaj end)/(@Ore_luna/8) else 1 end)
End
-- Contributii angajator
Set @CAS_unit=round(@Baza_CAS_cond_spec*@pCAS_gr1/100+@Baza_CAS_cond_deoseb*@pCAS_gr2/100+@Baza_CAS_cond_normale*@pCAS_gr3/100,2)
Set @Baza_CASS_unit=@Venit_total-(@Indcm_cas_19+@CMCAS)-(case when @Plastidr=1 and @CASSimps_K=1 then @Cons_admin else 0 end)-(case when @NuCASS_H=1 then @Suma_impoz else 0 end)-@Somaj_tehnic
Select @Baza_CASS_unit=0 where (@CASS_colab=0 or @As_sanatateP=0) and @GrupaMP in ('O','P')
Set @CASS_unit=round(@Baza_CASS_unit*@pCASSU/100,2)
Set @Baza_somajU=@Baza_somajI
Select @Baza_somajU=0 where (@GrupaMP in ('O','P') or @Pasmatex=1 or @Tip_ded_somajP=5) and @Somaj_1P=0
Set @Somaj_unit=round(@Baza_somajU*@pSomajU/100,2)
Set @Baza_FondGar=@Baza_somajI
Select @Baza_FondGar=0 where @GrupaMP in ('O','P') or @Inst_publ=1
Set @Fond_gar=round(@Baza_FondGar*@pFond_gar/100,2)
Set @Baza_CCI=@Venit_total-(@Indcm_cas_19+@CMCAS)+@Ind_Fambp-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)-
(case when @CASSimps_K=1 then @Cons_admin else 0 end)-@Alocatii_hrana-@Somaj_tehnic
Select @Baza_CCI=(case when @Baza_CCI>12*@Salar_minim then 12*@Salar_minim else @Baza_CCI end) where @GrupaMP='O' and @CCI_colabO=1 or @GrupaMP='P' and @CCI_colabP=1
Select @Baza_CCI=0 where @GrupaMP='O' and @CCI_colabO=0 or @GrupaMP='P' and @CCI_colabP=0
Set @CCI=round(@Baza_CCI*@pCCI/100,2)
Set @Baza_Fambp=@Baza_CAS_ind
Select @Baza_Fambp=0 where @GrupaMP='O'
Set @Fambp=round(@Baza_Fambp*@pFambp/100,2)
Set @Baza_ITM=@Venit_total-(@Indcm_cas_19+@CMCAS)-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)-(case when @CASSimps_K=1 then @Cons_admin else 0 end)
Select @Baza_ITM=0 where @NuITM_colab=1 and @GrupaMP in ('O','P') or @NuITM_pens=1 and @Tip_ded_somajP=5
Set @ITM=round(@Baza_ITM*@pITM/100,2)
Set @Rest_de_plata=@Venit_net
Set @Coef_tot_ded=0
exec scriuNet_salarii @DataS, @DataS, @Marca, @Locm, @VENIT_TOTAL, 0, 0, 0, 0, 0, @Impozit, @CAS_ind, @SomajI, @CASS_Fambp_sal, @CASSI,
@Baza_somajI, @VENIT_NET, 0, 0, 0, 0, 0, 0, @REST_DE_PLATA, @CAS_unit, @Somaj_unit, @Fambp, @ITM, @CASS_unit, @Coef_tot_ded, @Grad_invalidP, @Tip_ded_somajP, @Alte_surseP,
@VENIT_NET_IN_IMP, @Ded_baza, @CCI, @VENIT_BAZA, @Ded_somaj, @Baza_CAS_ind, @Baza_CAS_cond_normale, @Baza_CAS_cond_deoseb, @Baza_CAS_cond_spec
exec scriuNet_salarii @DataJ, @DataJ, @Marca, @Locm, 0, 0, 0, 0, 0, 0, @Impozit_sep, 0, 0, @CASS_Fambp_ang, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, @CASCM, @Fond_gar, 0, 0, 0, 0, '', 0, 0,
0, @Ded_pensie, @CCI_Fambp, 0, 0, @Baza_CCI, @Baza_CASCM_CN, @Baza_CASCM_CD, @Baza_CASCM_CS
Select @Baza_CCI=@Baza_CCI*@Coef_CCI-@Ind_Fambp
fetch next from cursor_net into @Data, @Marca, @Ore_lucrate, @Ore_noapte, @Ore_RN, @Ore_IT, @Ore_obligatii,
@Ore_CFS, @Ore_CO, @Ore_CM, @Invoiri, @Nemotivate, @Ore_supl, @Ore_justif,
@Baza_somaj_1, @Ind_FAMBP, @CMunitate, @CMCAS, @Diurna, @Suma_impoz, @Cons_admin,
@Suma_imp_sep, @Aj_deces, @Venit_total, @Spor_specific, @Spor_cond_1, @Spor_cond_2, @Spor_cond_3, @Spor_cond_4, @Spor_cond_5, @Spor_cond_6,
@Salar_orar, @RegimL, @Locm, @CorQ, @CorT, @CorU, @Ret_sindicat, @RegimL_pontaj, @GrupaM_pontaj,
@Somaj_1P, @As_sanatateP, @Tip_impozitareP, @GrupaMP, @Salar_de_incadrareP, @Salar_de_bazaP,
@Tip_salarizareP, @Ind_condP, @Spor_vechimeP, @Spor_de_noapteP, @Spor_sist_prgP, @Spor_functie_suplP,
@Spor_specificP, @Spor_conditii_1P, @Spor_conditii_2P, @Spor_conditii_3P, @Spor_conditii_4P,
@Spor_conditii_5P, @Spor_conditii_6P, @Tip_colabP, @Alte_surseP, @Grad_invalidP, @Tip_ded_somajP,
@Data_angajarii, @Plecat, @Mod_angajareP, @Data_plecP, @Sindicalist,
@DataI_conv_somaj, @DataE_conv_somaj, @Nr_pers_intr,
@Baza_cond_normale, @Baza_cond_deosebite, @Baza_cond_speciale,
@Indcm_unit_19, @Indcm_cas_19, @Ore_luna_cm, @Indcm, @Indcm_cas_18, @Zcm_18, @Zcm_18_ant, @Baza_casi_ant,
@Baza_cascm_ant, @Zcm_2341011, @Indcm_234, @Indcm_unit_234, @Zcm15, @Zcm_8915, @Indcm_8915,
@Zcm_78, @Indcm_78, @Indcm_somaj, @Ingrijire_copil_sarcina, @Actionar,
@uMarca2CNP, @uMarca2CNPCM, @CNP, @Pensie_max_ded, @Pensie_ded_lunar,
@Pensie_ded_ant, @Pensie_luna, @Salarii_comp, @Alocatii_hrana, @Somaj_tehnic, @Ore_somaj_tehnic, @Suma_neimp
End
drop table ##cursor_net
close cursor_net
Deallocate cursor_net
End
--exec psCalcul_venit_net '12/01/2009', '12/31/2009', '', '', 0
--drop table ##cursor_net
/*
select a.marca, a.Baza_CAS_cond_norm, b.Baza_CAS_cond_norm from net a, net1209 b
where a.data='12/31/2009' and a.data=b.data and a.marca=b.marca
and a.Baza_CAS_cond_norm<>b.Baza_CAS_cond_norm
*/
miercuri, 14 iulie 2010
Listbox cu SQLEXEC
Pentru asta poti folosi un remote view, un cursoradapter sau o comanda Select executata pe server cu SQLEXEC.
Sa zicem ca vrem sa aducem in listbox inregistrari din tabela articole, ordonate dupa articol.
Sa zicem ca am stabilit o conexiune la server al carei handle il punem in variabila hh.
Sa zicem ca vrem sa returnam rezultatul interogarii in cursorul crslist1
Comanda ar fi asta:
SQLEXEC(hh,[Select articol, idarticol from articole order by articol],[crslist1])
listbox.rowsource='crslist1.articol,idarticol'
listbox.rowsourcetype=6 &&fields
Dar VFP este un mediu de programare facut sa scuteasca programatorul de scris mii de pagini de cod.
Incearca asta:
listbox.rowsource='SQLEXEC(hh,[Select articol, idarticol from articole order by articol],[crslist1])'
listbox.rowsourcetype=3 &&sql statement
Daca ai mai adaugat sau elimita articole in tabela articole, dai comanda
listbox.requery
Asta e tot in ceea ce priveste popularea unui listbox sau combobox cu date de pe un server sql.
Desigur, ramane sa pui numarul de coloane, boundcolumn, columnwidths si ce mai e nevoie, dar, pentru date, sunt suficiente cele 3 linii de mai sus.
Conexiunea se poate stabili in application.init iar connection handle stocat in application.connhandle, sau in dataenvironment.beforeopentables iar connexion handle intr-o proprietate a formului.
Sa zicem ca vrem sa aducem in listbox inregistrari din tabela articole, ordonate dupa articol.
Sa zicem ca am stabilit o conexiune la server al carei handle il punem in variabila hh.
Sa zicem ca vrem sa returnam rezultatul interogarii in cursorul crslist1
Comanda ar fi asta:
SQLEXEC(hh,[Select articol, idarticol from articole order by articol],[crslist1])
listbox.rowsource='crslist1.articol,idarticol'
listbox.rowsourcetype=6 &&fields
Dar VFP este un mediu de programare facut sa scuteasca programatorul de scris mii de pagini de cod.
Incearca asta:
listbox.rowsource='SQLEXEC(hh,[Select articol, idarticol from articole order by articol],[crslist1])'
listbox.rowsourcetype=3 &&sql statement
Daca ai mai adaugat sau elimita articole in tabela articole, dai comanda
listbox.requery
Asta e tot in ceea ce priveste popularea unui listbox sau combobox cu date de pe un server sql.
Desigur, ramane sa pui numarul de coloane, boundcolumn, columnwidths si ce mai e nevoie, dar, pentru date, sunt suficiente cele 3 linii de mai sus.
Conexiunea se poate stabili in application.init iar connection handle stocat in application.connhandle, sau in dataenvironment.beforeopentables iar connexion handle intr-o proprietate a formului.
Buffering
Un cursor cu buffering=5 (table buffering) poate rezolva mai simplu problema. Pentru a elimina o inregistrare e suficienta o comanda TABLEREVERT(.f.). La sfarsitul editarii, TABLEUPDATE(.T.) comite bufferul in cursor.
CREATE CURSOR cc (f1 i, poz i)
CURSORSETPROP("Buffering",5)
FOR ii=1 TO 20
INSERT INTO cc (f1) VALUES (ii)
next
BROWSE LAST
SET SQLBUFFERING ON
GO 7
lnpoz=ABS(RECNO()) && Inregistrarile adaugate in buffer au recno() negativ
SELECT ABS(RECNO()) as rec, * FROM cc WHERE ABS(RECNO())=lnpoz INTO CURSOR cy ORDER BY 1
TABLEREVERT(.t.,'cc')
INSERT INTO cc (f1, poz) SELECT f1, rec FROM cx
APPEND BLANK IN cc
INSERT INTO cc (f1, poz) SELECT f1, rec FROM cy
SELECT cc
BROWSE nowait
SET SQLBUFFERING OFF &&daca e cazul
CREATE CURSOR cc (f1 i, poz i)
CURSORSETPROP("Buffering",5)
FOR ii=1 TO 20
INSERT INTO cc (f1) VALUES (ii)
next
BROWSE LAST
SET SQLBUFFERING ON
GO 7
lnpoz=ABS(RECNO()) && Inregistrarile adaugate in buffer au recno() negativ
SELECT ABS(RECNO()) as rec, * FROM cc WHERE ABS(RECNO())
TABLEREVERT(.t.,'cc')
INSERT INTO cc (f1, poz) SELECT f1, rec FROM cx
APPEND BLANK IN cc
INSERT INTO cc (f1, poz) SELECT f1, rec FROM cy
SELECT cc
BROWSE nowait
SET SQLBUFFERING OFF &&daca e cazul
Conversie Visual FoxPro in C#
http://profox.ro/Forum/tabid/55/forumid/24/threadid/18955/scope/posts/Default.aspx
Abonați-vă la:
Postări (Atom)