marți, 2 noiembrie 2010
http://oakleafblog.blogspot.com/2007/05/linq-to-datasets-rehabilitates-vb-bang.html
http://oakleafblog.blogspot.com/2007/05/linq-to-datasets-rehabilitates-vb-bang.html
sâmbătă, 9 octombrie 2010
joi, 7 octombrie 2010
http://www.tek-tips.com/faqs.cfm?fid=4260 (Sincronizare 2 Griduri)
http://www.tek-tips.com/faqs.cfm?fid=4260
joi, 23 septembrie 2010
marți, 21 septembrie 2010
treeview
Function TVLister
Lparameters toTV
Local lnIndex,lnLastIndex
_Cliptext=''
With toTV
lnIndex = .Nodes(1).Root.FirstSibling.Index
lnLastIndex = .Nodes(1).Root.LastSibling.Index
_GetSubNodes(m.lnIndex,m.toTV,m.lnIndex)
Do While m.lnIndex # m.lnLastIndex
lnIndex = .Nodes(lnIndex).Next.Index
_GetSubNodes(m.lnIndex,m.toTV,m.lnIndex)
Enddo
EndWith
Return _Cliptext
Function _GetSubNodes
Lparameters tnIndex, toTV, tnRootIndex
Local lnIndex, lnLastIndex
With toTV
WriteNode(m.tnIndex,m.toTV, m.tnRootIndex)
If .Nodes(m.tnIndex).Children > 0
lnIndex = .Nodes(m.tnIndex).Child.Index
lnLastIndex = .Nodes(m.tnIndex).Child.LastSibling.Index
_GetSubNodes(m.lnIndex,m.toTV,m.tnRootIndex)
Do While m.lnIndex # m.lnLastIndex
lnIndex = .Nodes(lnIndex).Next.Index
_GetSubNodes(m.lnIndex,m.toTV,m.tnRootIndex)
Enddo
Endif
Endwith
Function WriteNode
Lparameters tnCurIndex, toTV,tnRootIndex
Local lnRootIndex, lnIndex, lcPrefix, lcKey, lnLevel
lnIndex = m.tnCurIndex
With toTV
lcPrefix = '+-' + .Nodes(m.lnIndex).Text
lnLevel = 0
Do While lnIndex # tnRootIndex
lnIndex = .Nodes(m.lnIndex).Parent.Index
lcPrefix = Iif(.Nodes(m.lnIndex).LastSibling.Index = m.lnIndex,' ','|')+Space(3)+m.lcPrefix
lnLevel = m.lnLevel + 1
EndDo
_Cliptext = _Cliptext + m.lcPrefix + Chr(13)
Endwith
Lparameters toTV
Local lnIndex,lnLastIndex
_Cliptext=''
With toTV
lnIndex = .Nodes(1).Root.FirstSibling.Index
lnLastIndex = .Nodes(1).Root.LastSibling.Index
_GetSubNodes(m.lnIndex,m.toTV,m.lnIndex)
Do While m.lnIndex # m.lnLastIndex
lnIndex = .Nodes(lnIndex).Next.Index
_GetSubNodes(m.lnIndex,m.toTV,m.lnIndex)
Enddo
EndWith
Return _Cliptext
Function _GetSubNodes
Lparameters tnIndex, toTV, tnRootIndex
Local lnIndex, lnLastIndex
With toTV
WriteNode(m.tnIndex,m.toTV, m.tnRootIndex)
If .Nodes(m.tnIndex).Children > 0
lnIndex = .Nodes(m.tnIndex).Child.Index
lnLastIndex = .Nodes(m.tnIndex).Child.LastSibling.Index
_GetSubNodes(m.lnIndex,m.toTV,m.tnRootIndex)
Do While m.lnIndex # m.lnLastIndex
lnIndex = .Nodes(lnIndex).Next.Index
_GetSubNodes(m.lnIndex,m.toTV,m.tnRootIndex)
Enddo
Endif
Endwith
Function WriteNode
Lparameters tnCurIndex, toTV,tnRootIndex
Local lnRootIndex, lnIndex, lcPrefix, lcKey, lnLevel
lnIndex = m.tnCurIndex
With toTV
lcPrefix = '+-' + .Nodes(m.lnIndex).Text
lnLevel = 0
Do While lnIndex # tnRootIndex
lnIndex = .Nodes(m.lnIndex).Parent.Index
lcPrefix = Iif(.Nodes(m.lnIndex).LastSibling.Index = m.lnIndex,' ','|')+Space(3)+m.lcPrefix
lnLevel = m.lnLevel + 1
EndDo
_Cliptext = _Cliptext + m.lcPrefix + Chr(13)
Endwith
http://weblogs.foxite.com/andykramek/archive/2009/04/25/8332.aspx
http://weblogs.foxite.com/andykramek/archive/2009/04/25/8332.aspx
luni, 20 septembrie 2010
HaHaHA
GUVERNUL ROMÂNIEI hotăreşte:
Art.1 Începînd cu data de 01.06.2010,pensionarii pot traversa pe culoarea roşie
a semaforului.
Art.2 Dacă în maximun 30 zile nu se vor obţine rezultate
mulţumitoare, începînd cu data de 01.07.2010 traversarea pe
culoarea rosie a semaforului devine OBLIGATORIE.
Art. 3 Pentru că tot nu se întrevede o relansare economică, de la 01.01.2011
măsura se poate extinde şi asupra bugetarilor.
Art. 4 Începînd cu data de 01.06.2010 Guvernul României oferă slujbe gratuite
tuturor şomerilor.
Slujbele vor fi oficiate de preoţi,episcopi,arhiepiscopi si de
Preafericitul, preşedinte al României.
Masuri anticriza
Propunem Guvernului urmatoarele masuri pentru iesirea din criza:
1. Reangajarea obligatorie a pensionarilor pentru a putea finaliza marile
realizari incepute in Epoca de Aur: Canalul Dunare – Marea Neagra , Dunare
–Bucuresti, constructia unei noi Case a Poporului, etc.
2. Impozitarea consumului de hartie igienica in functie de straturi deoarece in
asa vremuri cacacioase nu se poate trece cu vederea un asemenea potential . Cei
care nu maninca nimic si in consecinta nu “produc” vor plati un impozit
forfetar.
3. Taxa pe aer la un nivel de 0,04 euro/mc consumat. Numarul de mc consumati va
fi stabilit de catre specialistii Guvernului impreuna cu Medicii de familie, in
functie de capacitatea pulmonara a fiecarei persoane.
Astmatici au reducere de la 25% pina la 100% in cazul cind se opresc din
respirat mai mult de 15 minute.
4. Impozitarea pe kilogram. Orice roman peste 35 de kg este un “gras nesimtit
‘’ pe principiul lui Basescu “ slabutul il duce in spate pe cel gras “
5. Taxa pe vanturi trase, ca prea s-au obisnuit romanii sa-si dea aere. Se vor
monta detectoare la fundul fiecarui contribuabil care vor inregistra numarul
vanturilor trase, intensitatea sunetului, intensitatea mirosului.
Taxa va adauga 24% la factura de gaz si va fi obligatorie ca taxa “radio –Tv” de
pe factura de curent.
6. Taxa pe a doua limba vorbita. Daca au bani si timp sa invete o alta limba in
afara de romana sa le fie rusine si sa plateasca pentru asta pentru ca sigur o
fac intentionat ca sa poata pleca din tara . Taxa va fi de 50 % din valoarea
totala a alocatiei care a primit-o persoana respectiva pina la
virsta de 18 ani de la stat. Sunt scutiti de taxa ungurii din Harghita,Covasna
si Mures deoarece ei oricum nu stiu decit ungureste deci nu se pune problema ca
ar sti mai mult de o limba.
7. Orice barbat care are mai mult de 124899 de fire de par va plati o taxa pe
fir de par echivalent a 10 euro / 1000 fire de par . Peste 20000 de fire in plus
se va considera ca ai un alt cap si se va impozita dublu. Cei cu chelie
proeminenta si /sau cu suvita peste chelie sint scutiti.
8. Persoanele care isi rod unghiile vor achita TVA care va fi calculat la 100
de grame de unghii la un pret care va fi stabilit de catre guvern deoarece
consuma un aliment si e normal sa plateasca.
9. Persoanele cu dizabilitati care umbla in carucioare pe strazi (scaune
electrice conform D-soarei Elena Basescu) vor achita taxa de drum ca orice
autovehicul. Cei care nu au carucioare electrice, ci din acelea impinse de o
persoana (insotitor cu retributia micsorata cu 15%) vor plati “taxa de atelaj
impins/tras de animal de povara” (bou/vaca in cazul nostru) . Daca vor fi prinsi
cu taxa neplatita li se va confisca caruciorul si vor fi trimisi acasa pe jos.
10. Taxa de caldura pentru vara deoarece orice cetatean care tine geamul deschis
primeste gratuit incalzire prin bunavointa Sfintului Guvern si nu este normal.
Astfel pentru fiecare geam deschis vara , la caldura peste 18 grade se va plati
o “taxa de geam deschis” in valoare de 12 euro/geam.
La mai mult de 3 geamuri deschise se va considera a doua casa si se va impozita
dublu. Pentru geamurile inchise se va acorda o reducere de 50% dar numai pina la
24 de grade.
11. Sugarii si bebelusii care folosesc pampers (nesimtitii!!!) vor plati o taxa
de poluare in functie de gradul de periculozitate care il prezinta fiecare din
reziduurile acumulate in scutec.
Pentru stabilirea gradului de periculozitate in care se incadreaza fiecare
bebelus se vor trimite obligatoriu pina la 1 iulie 2010 pentru fiecare copil
intre 0 si 2 ani mostre din “produsul” rezultat si acumulat in pampersi la
sediul Guvernului pentru a fi analizate. Ulterior fiecare parinte va primi acasa
o declaratie de impozitare pe care o va achita in 24 de ore la cea mai apropiata
agentie CEC .
12. SI NU IN ULTIMUL RAND TAXA PE PROSTIE , CA SUNTEM ATAT DE PROSTI SI
ÎI RABDAM SA-SI BATA JOC DE NOI !!!!!!!
Art.1 Începînd cu data de 01.06.2010,pensionarii pot traversa pe culoarea roşie
a semaforului.
Art.2 Dacă în maximun 30 zile nu se vor obţine rezultate
mulţumitoare, începînd cu data de 01.07.2010 traversarea pe
culoarea rosie a semaforului devine OBLIGATORIE.
Art. 3 Pentru că tot nu se întrevede o relansare economică, de la 01.01.2011
măsura se poate extinde şi asupra bugetarilor.
Art. 4 Începînd cu data de 01.06.2010 Guvernul României oferă slujbe gratuite
tuturor şomerilor.
Slujbele vor fi oficiate de preoţi,episcopi,arhiepiscopi si de
Preafericitul, preşedinte al României.
Masuri anticriza
Propunem Guvernului urmatoarele masuri pentru iesirea din criza:
1. Reangajarea obligatorie a pensionarilor pentru a putea finaliza marile
realizari incepute in Epoca de Aur: Canalul Dunare – Marea Neagra , Dunare
–Bucuresti, constructia unei noi Case a Poporului, etc.
2. Impozitarea consumului de hartie igienica in functie de straturi deoarece in
asa vremuri cacacioase nu se poate trece cu vederea un asemenea potential . Cei
care nu maninca nimic si in consecinta nu “produc” vor plati un impozit
forfetar.
3. Taxa pe aer la un nivel de 0,04 euro/mc consumat. Numarul de mc consumati va
fi stabilit de catre specialistii Guvernului impreuna cu Medicii de familie, in
functie de capacitatea pulmonara a fiecarei persoane.
Astmatici au reducere de la 25% pina la 100% in cazul cind se opresc din
respirat mai mult de 15 minute.
4. Impozitarea pe kilogram. Orice roman peste 35 de kg este un “gras nesimtit
‘’ pe principiul lui Basescu “ slabutul il duce in spate pe cel gras “
5. Taxa pe vanturi trase, ca prea s-au obisnuit romanii sa-si dea aere. Se vor
monta detectoare la fundul fiecarui contribuabil care vor inregistra numarul
vanturilor trase, intensitatea sunetului, intensitatea mirosului.
Taxa va adauga 24% la factura de gaz si va fi obligatorie ca taxa “radio –Tv” de
pe factura de curent.
6. Taxa pe a doua limba vorbita. Daca au bani si timp sa invete o alta limba in
afara de romana sa le fie rusine si sa plateasca pentru asta pentru ca sigur o
fac intentionat ca sa poata pleca din tara . Taxa va fi de 50 % din valoarea
totala a alocatiei care a primit-o persoana respectiva pina la
virsta de 18 ani de la stat. Sunt scutiti de taxa ungurii din Harghita,Covasna
si Mures deoarece ei oricum nu stiu decit ungureste deci nu se pune problema ca
ar sti mai mult de o limba.
7. Orice barbat care are mai mult de 124899 de fire de par va plati o taxa pe
fir de par echivalent a 10 euro / 1000 fire de par . Peste 20000 de fire in plus
se va considera ca ai un alt cap si se va impozita dublu. Cei cu chelie
proeminenta si /sau cu suvita peste chelie sint scutiti.
8. Persoanele care isi rod unghiile vor achita TVA care va fi calculat la 100
de grame de unghii la un pret care va fi stabilit de catre guvern deoarece
consuma un aliment si e normal sa plateasca.
9. Persoanele cu dizabilitati care umbla in carucioare pe strazi (scaune
electrice conform D-soarei Elena Basescu) vor achita taxa de drum ca orice
autovehicul. Cei care nu au carucioare electrice, ci din acelea impinse de o
persoana (insotitor cu retributia micsorata cu 15%) vor plati “taxa de atelaj
impins/tras de animal de povara” (bou/vaca in cazul nostru) . Daca vor fi prinsi
cu taxa neplatita li se va confisca caruciorul si vor fi trimisi acasa pe jos.
10. Taxa de caldura pentru vara deoarece orice cetatean care tine geamul deschis
primeste gratuit incalzire prin bunavointa Sfintului Guvern si nu este normal.
Astfel pentru fiecare geam deschis vara , la caldura peste 18 grade se va plati
o “taxa de geam deschis” in valoare de 12 euro/geam.
La mai mult de 3 geamuri deschise se va considera a doua casa si se va impozita
dublu. Pentru geamurile inchise se va acorda o reducere de 50% dar numai pina la
24 de grade.
11. Sugarii si bebelusii care folosesc pampers (nesimtitii!!!) vor plati o taxa
de poluare in functie de gradul de periculozitate care il prezinta fiecare din
reziduurile acumulate in scutec.
Pentru stabilirea gradului de periculozitate in care se incadreaza fiecare
bebelus se vor trimite obligatoriu pina la 1 iulie 2010 pentru fiecare copil
intre 0 si 2 ani mostre din “produsul” rezultat si acumulat in pampersi la
sediul Guvernului pentru a fi analizate. Ulterior fiecare parinte va primi acasa
o declaratie de impozitare pe care o va achita in 24 de ore la cea mai apropiata
agentie CEC .
12. SI NU IN ULTIMUL RAND TAXA PE PROSTIE , CA SUNTEM ATAT DE PROSTI SI
ÎI RABDAM SA-SI BATA JOC DE NOI !!!!!!!
sâmbătă, 18 septembrie 2010
http://www.gpureview.com/show_cards.php?card1=431&card2=621
http://www.gpureview.com/show_cards.php?card1=431&card2=621
luni, 13 septembrie 2010
http://webcache.googleusercontent.com/search?q=cache:Buu9vDyjgXUJ:webspace.ulbsibiu.ro/florin.martin/attachments/lab_VFP%2520-%2520SQL%2520v3.doc+vfp+INSERT+SQL+record+pointer+problem&cd=3&hl=ro&ct=clnk&gl=ro
http://webcache.googleusercontent.com/search?q=cache:Buu9vDyjgXUJ:webspace.ulbsibiu.ro/florin.martin/attachments/lab_VFP%2520-%2520SQL%2520v3.doc+vfp+INSERT+SQL+record+pointer+problem&cd=3&hl=ro&ct=clnk&gl=ro
sâmbătă, 11 septembrie 2010
Get primary key columns
declare @TableName varchar(128)
select @TableName = 'mytbl'
select c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
select @TableName = 'mytbl'
select c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
joi, 9 septembrie 2010
DRAG AND DROP http://www.aksel.com/whitepapers/dragdrop.htm
http://www.aksel.com/whitepapers/dragdrop.htm
http://www.garfieldhudson.com/FreeVideos.aspx
Video VFP
http://www.garfieldhudson.com/FreeVideos.aspx
http://www.garfieldhudson.com/FreeVideos.aspx
joi, 19 august 2010
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
marți, 15 iunie 2010
SQL Server - prima sau ultima zi din Luna
-- DECLARE @R AS DATETIME
-- EXECUTE dbo.FirstLastMonthDayDate @R=@R,@FL=2, @D='20100615'
-- EXECUTE dbo.FirstLastMonthDayDate @R=@R,@FL=1, @D='20100615'
CREATE PROCEDURE FirstLastMonthDayDate
@d AS DATETIME=NULL,
@FL AS INT=1,
@R AS DATETIME OUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @d IS NULL
BEGIN
SELECT @d=GETDATE()
END
IF @FL=1
BEGIN
-- Return the first day date
SELECT @R=dateadd(day,-(datepart(day,@d)-1),@d)
END
ELSE
BEGIN
-- Return the last day date
SELECT @R=dateadd(day,-(datepart(day,dateadd(mm,1,@d))),dateadd(mm,1,@d))
END
SELECT @R
END
-- EXECUTE dbo.FirstLastMonthDayDate @R=@R,@FL=2, @D='20100615'
-- EXECUTE dbo.FirstLastMonthDayDate @R=@R,@FL=1, @D='20100615'
CREATE PROCEDURE FirstLastMonthDayDate
@d AS DATETIME=NULL,
@FL AS INT=1,
@R AS DATETIME OUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @d IS NULL
BEGIN
SELECT @d=GETDATE()
END
IF @FL=1
BEGIN
-- Return the first day date
SELECT @R=dateadd(day,-(datepart(day,@d)-1),@d)
END
ELSE
BEGIN
-- Return the last day date
SELECT @R=dateadd(day,-(datepart(day,dateadd(mm,1,@d))),dateadd(mm,1,@d))
END
SELECT @R
END
Automated SQL Job to Backup All Database(s)
USE [msdb]
GO
IF EXISTS (SELECT name FROM msdb.sys.objects WHERE name = 'usp_CreateBackupJobs' AND type = 'P')
DROP PROCEDURE [dbo].[usp_CreateBackupJobs]
GO
/****** Object: StoredProcedure [dbo].[usp_CreateBackupJobs] Script Date: 10/02/2008 06:04:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CreateBackupJobs]
/************************************************************************************************************************/
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
/*
@BackupServerNameInput VARCHAR(50)= N'MYBACKUPSERVER', -- Type the centralized backup server name
@BackupShareInput VARCHAR(200) = N'\\MYBACKUPSERVER\MYSHAREFOLDER', -- Type the IP address of the backup server's backup NIC and the sharename
@DoAFullBackupInput INT = 1 -- If this value is set to 1 a full backup of all the new databases will be taken
*/
@BackupServerNameInput VARCHAR(50)= N'LOCALSQLSERVER', -- Type the centralized backup server name
@BackupShareInput VARCHAR(200) = N'D:\ICAS\BACKUP', -- Type the IP address of the backup server's backup NIC and the sharename
@DoAFullBackupInput INT = 1 -- If this value is set to 1 a full backup of all the new databases will be taken
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 end
-----------------------------------------------------------------------------------------------------------
/************************************************************************************************************************/
AS
BEGIN
DECLARE @ServerName VARCHAR(30)
DECLARE @CI VARCHAR(50)
DECLARE @DbName VARCHAR(100)
DECLARE Database_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM master.dbo.sysdatabases
DECLARE @BackupServerName VARCHAR(50)
DECLARE @ShareName VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @DoAFullBackup int
DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @MakeTheJob int
DECLARE @CommandString VARCHAR(4000)
DECLARE @foldermissing int
DECLARE @jobId BINARY(16)
DECLARE @JobName VARCHAR(200)
DECLARE @ReturnCode INT
DECLARE @Backup_Var VARCHAR(100)
DECLARE JobName_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM msdb.dbo.sysjobs
DECLARE @JobName2 VARCHAR(200)
SET @BackupServerName = @BackupServerNameInput
SET @BackupShare = @BackupShareInput
SET @DoAFullBackup = @DoAFullBackupInput
/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY('MachineName'))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY('InstanceName'))
IF @CI IS NULL
SET @CI = @ServerName
ELSE
SET @CI = @ServerName + '_' + @CI
/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MakeTheJob = 0
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @JobName = 'Backup_' + @DbName + '_Full --> ' + @BackupServerName
/*Check if the a full should be made - start*/
IF (@DbName <> 'tempdb' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
/*Check if the a full should be made - end*/
/*Create the Full Backup job - start */
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Full_Backup_Device'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Full Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Full_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
backup database @Db_Name TO DISK= @BackupShare with init'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup',
@enabled=1,
@freq_type=8,
@freq_interval=32,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080929,
@active_end_date=99991231,
@active_start_time=190000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
/*Run a full backup - start*/
IF (@DoAFullBackup = 1)
BEGIN
--PRINT @DbName + ' Nu skal der laves en full backup'
EXEC sp_start_job @job_name = @JobName
END
/*Run a full backup - end*/
END
/*Create the Full Backup job - end */
/*Create the Diff Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Diff --> ' + @BackupServerName
IF (@DbName <> 'tempdb' AND @DbName <> 'pubs' AND @DbName <> 'Northwind' AND @DbName <> 'AccentureWorks' AND @DbName <> 'master' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Diff_Backup_Device'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Diff Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Diff_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
backup database @Db_Name TO DISK=@BackupShare with differential'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Diff Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup',
@enabled=1,
@freq_type=8,
@freq_interval=95,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080213,
@active_end_date=99991231,
@active_start_time=200000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
END
/*Create the Diff Backup job - end*/
/*Create the Log Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Log --> ' + @BackupServerName
IF ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) <> 'SIMPLE') AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName))--((SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Backup_' + @DbName + '_Log') <> 'Backup_' + @DbName + '_Log') -- Checks the recovery model
SET @MakeTheJob = 1
ELSE
BEGIN
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName) AND ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) = 'SIMPLE')
BEGIN
SET @jobId = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_id=@JobId, @delete_unused_schedule=1
END
END
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Log Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Log_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
BACKUP LOG @Db_Name TO DISK=@BackupShare'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=2,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080213,
@active_end_date=99991231,
@active_start_time=010000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
END
/*Create the Log Backup job - end*/
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
FETCH NEXT FROM Database_Cursor INTO @DbName
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
/*****************************************************************************************************************************/
OPEN JobName_Cursor
FETCH NEXT FROM JobName_Cursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
IF len(@JobName) < 7 SET @Backup_Var = 'STOP' ELSE SET @Backup_Var = (SELECT LEFT(@JobName, 7)) IF (@Backup_Var = 'Backup_') BEGIN SET @JobName2 = (SELECT LEFT(@JobName, len(@JobName)- 5 - len(@BackupServerName))) IF (SELECT RIGHT(@JobName2, 3)) = 'Log' BEGIN SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-4)) SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7)) END ELSE BEGIN SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-5)) SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7)) END IF NOT EXISTS(SELECT name FROM master.sys.databases WHERE name = @JobName2) BEGIN SET @JobID = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName) EXEC msdb.dbo.sp_delete_job @job_id=@JobID, @delete_unused_schedule=1 PRINT @JobName + ' is deleted' END END FETCH NEXT FROM JobName_Cursor INTO @JobName END CLOSE JobName_Cursor DEALLOCATE JobName_Cursor /*****************************************************************************************************************************/ END GO /******************************************************************************************************************************************/ /* */ /* Here begins the job creation */ /* */ /******************************************************************************************************************************************/ USE [master] DECLARE @BackupServerName VARCHAR(50) DECLARE @BackupShare VARCHAR(1000) --DECLARE @Backup_Var VARCHAR(100) --DECLARE JobName_Cursor CURSOR FOR SELECT name FROM msdb.dbo.sysjobs --DECLARE @JobName2 VARCHAR(200) DECLARE @JobName VARCHAR(200) DECLARE @CommandString VARCHAR(4000) ----------------------------------------------------------------------------------------------------------- -- Input section 2/2 start - The section below is the one you need to look at each time you run the script ----------------------------------------------------------------------------------------------------------- /* SET @BackupServerName = N'MYBACKUPSERVER' -- Type the backup server name SET @BackupShare = N'\\MYBACKUPSERVER\MYSHAREFOLDER' -- Type the IP address of the backup server's backup NIC and the sharename */ SET @BackupServerName = N'LOCALSQLSERVER' -- Type the backup server name SET @BackupShare = N'D:\ICAS\BACKUP' -- Type the IP address of the backup server's backup NIC and the sharename --SET @DoAFullBackup = 0 -- If this value is set to 1 a full backup of all the new databases will be taken ----------------------------------------------------------------------------------------------------------- -- Input section 2/2 end ----------------------------------------------------------------------------------------------------------- USE [msdb] /****** Object: Job [Create_Backup_Jobs] Script Date: 10/02/2008 05:34:18 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [BACKUP] Script Date: 10/02/2008 05:34:18 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Create_Backup_Jobs',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Creates backup jobs for all the databases',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Enable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Enable XP_cmdshell',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=5,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 1
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Backup Folders] Script Date: 10/02/2008 05:34:19 ******/
SET @CommandString = N'DECLARE @ServerName VARCHAR(50)
DECLARE @CI VARCHAR(100)
DECLARE @DbName VARCHAR(100)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @ShareName VARCHAR(200)
DECLARE @CommandString VARCHAR(500)
DECLARE @foldermissing int
DECLARE Database_Cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases
-----------------------------------------------------------------------------------------------------------
-- Input section start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
SET @BackupShare = N''' + @BackupShare + ''' -- Type the IP address of the backup server''s backup NIC and the sharename
-----------------------------------------------------------------------------------------------------------
-- Input section end
-----------------------------------------------------------------------------------------------------------
/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY(''MachineName''))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY(''InstanceName''))
IF @CI IS NULL
SET @CI = @ServerName
ELSE
SET @CI = @ServerName + ''_'' + @CI
/* Get server and instance name end */
/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ShareName = @BackupShare + ''\'' + @CI + ''\'' + @DbName
/*Check if the backupfolder exists and if it doesn''t - create it - start*/
SET @CommandString = ''dir '' + @ShareName
EXEC @foldermissing = master..xp_cmdshell @CommandString
IF @foldermissing = 1
BEGIN
SET @CommandString = ''mkdir '' + @ShareName
EXEC master..xp_cmdshell @CommandString
END
FETCH NEXT FROM Database_Cursor INTO @DbName
END
/*Check if the backupfolder exists and if it doesn''t - create it - end*/
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
go'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Folders',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=5,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@CommandString,
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 0
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Backup Jobs] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Jobs',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command= N'EXEC msdb.dbo.usp_CreateBackupJobs',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell on failure] Script Date: 10/03/2008 10:59:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell on failure',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=2,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''XP_cmdshell'', 0
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081002,
@active_end_date=99991231,
@active_start_time=170000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081002,
@active_end_date=99991231,
@active_start_time=55710,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
go
/* Uncomment the below code if you like to create Backup jobs now*************/
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
EXEC sp_start_job Create_Backup_Jobs
GO
IF EXISTS (SELECT name FROM msdb.sys.objects WHERE name = 'usp_CreateBackupJobs' AND type = 'P')
DROP PROCEDURE [dbo].[usp_CreateBackupJobs]
GO
/****** Object: StoredProcedure [dbo].[usp_CreateBackupJobs] Script Date: 10/02/2008 06:04:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CreateBackupJobs]
/************************************************************************************************************************/
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
/*
@BackupServerNameInput VARCHAR(50)= N'MYBACKUPSERVER', -- Type the centralized backup server name
@BackupShareInput VARCHAR(200) = N'\\MYBACKUPSERVER\MYSHAREFOLDER', -- Type the IP address of the backup server's backup NIC and the sharename
@DoAFullBackupInput INT = 1 -- If this value is set to 1 a full backup of all the new databases will be taken
*/
@BackupServerNameInput VARCHAR(50)= N'LOCALSQLSERVER', -- Type the centralized backup server name
@BackupShareInput VARCHAR(200) = N'D:\ICAS\BACKUP', -- Type the IP address of the backup server's backup NIC and the sharename
@DoAFullBackupInput INT = 1 -- If this value is set to 1 a full backup of all the new databases will be taken
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 end
-----------------------------------------------------------------------------------------------------------
/************************************************************************************************************************/
AS
BEGIN
DECLARE @ServerName VARCHAR(30)
DECLARE @CI VARCHAR(50)
DECLARE @DbName VARCHAR(100)
DECLARE Database_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM master.dbo.sysdatabases
DECLARE @BackupServerName VARCHAR(50)
DECLARE @ShareName VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @DoAFullBackup int
DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @MakeTheJob int
DECLARE @CommandString VARCHAR(4000)
DECLARE @foldermissing int
DECLARE @jobId BINARY(16)
DECLARE @JobName VARCHAR(200)
DECLARE @ReturnCode INT
DECLARE @Backup_Var VARCHAR(100)
DECLARE JobName_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM msdb.dbo.sysjobs
DECLARE @JobName2 VARCHAR(200)
SET @BackupServerName = @BackupServerNameInput
SET @BackupShare = @BackupShareInput
SET @DoAFullBackup = @DoAFullBackupInput
/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY('MachineName'))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY('InstanceName'))
IF @CI IS NULL
SET @CI = @ServerName
ELSE
SET @CI = @ServerName + '_' + @CI
/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MakeTheJob = 0
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @JobName = 'Backup_' + @DbName + '_Full --> ' + @BackupServerName
/*Check if the a full should be made - start*/
IF (@DbName <> 'tempdb' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
/*Check if the a full should be made - end*/
/*Create the Full Backup job - start */
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Full_Backup_Device'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Full Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Full_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
backup database @Db_Name TO DISK= @BackupShare with init'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup',
@enabled=1,
@freq_type=8,
@freq_interval=32,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080929,
@active_end_date=99991231,
@active_start_time=190000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
/*Run a full backup - start*/
IF (@DoAFullBackup = 1)
BEGIN
--PRINT @DbName + ' Nu skal der laves en full backup'
EXEC sp_start_job @job_name = @JobName
END
/*Run a full backup - end*/
END
/*Create the Full Backup job - end */
/*Create the Diff Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Diff --> ' + @BackupServerName
IF (@DbName <> 'tempdb' AND @DbName <> 'pubs' AND @DbName <> 'Northwind' AND @DbName <> 'AccentureWorks' AND @DbName <> 'master' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Diff_Backup_Device'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Diff Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Diff_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
backup database @Db_Name TO DISK=@BackupShare with differential'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Diff Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup',
@enabled=1,
@freq_type=8,
@freq_interval=95,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080213,
@active_end_date=99991231,
@active_start_time=200000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
END
/*Create the Diff Backup job - end*/
/*Create the Log Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Log --> ' + @BackupServerName
IF ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) <> 'SIMPLE') AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName))--((SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Backup_' + @DbName + '_Log') <> 'Backup_' + @DbName + '_Log') -- Checks the recovery model
SET @MakeTheJob = 1
ELSE
BEGIN
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName) AND ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) = 'SIMPLE')
BEGIN
SET @jobId = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_id=@JobId, @delete_unused_schedule=1
END
END
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Log Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Log_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
BACKUP LOG @Db_Name TO DISK=@BackupShare'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=2,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080213,
@active_end_date=99991231,
@active_start_time=010000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
END
/*Create the Log Backup job - end*/
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
FETCH NEXT FROM Database_Cursor INTO @DbName
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
/*****************************************************************************************************************************/
OPEN JobName_Cursor
FETCH NEXT FROM JobName_Cursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
IF len(@JobName) < 7 SET @Backup_Var = 'STOP' ELSE SET @Backup_Var = (SELECT LEFT(@JobName, 7)) IF (@Backup_Var = 'Backup_') BEGIN SET @JobName2 = (SELECT LEFT(@JobName, len(@JobName)- 5 - len(@BackupServerName))) IF (SELECT RIGHT(@JobName2, 3)) = 'Log' BEGIN SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-4)) SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7)) END ELSE BEGIN SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-5)) SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7)) END IF NOT EXISTS(SELECT name FROM master.sys.databases WHERE name = @JobName2) BEGIN SET @JobID = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName) EXEC msdb.dbo.sp_delete_job @job_id=@JobID, @delete_unused_schedule=1 PRINT @JobName + ' is deleted' END END FETCH NEXT FROM JobName_Cursor INTO @JobName END CLOSE JobName_Cursor DEALLOCATE JobName_Cursor /*****************************************************************************************************************************/ END GO /******************************************************************************************************************************************/ /* */ /* Here begins the job creation */ /* */ /******************************************************************************************************************************************/ USE [master] DECLARE @BackupServerName VARCHAR(50) DECLARE @BackupShare VARCHAR(1000) --DECLARE @Backup_Var VARCHAR(100) --DECLARE JobName_Cursor CURSOR FOR SELECT name FROM msdb.dbo.sysjobs --DECLARE @JobName2 VARCHAR(200) DECLARE @JobName VARCHAR(200) DECLARE @CommandString VARCHAR(4000) ----------------------------------------------------------------------------------------------------------- -- Input section 2/2 start - The section below is the one you need to look at each time you run the script ----------------------------------------------------------------------------------------------------------- /* SET @BackupServerName = N'MYBACKUPSERVER' -- Type the backup server name SET @BackupShare = N'\\MYBACKUPSERVER\MYSHAREFOLDER' -- Type the IP address of the backup server's backup NIC and the sharename */ SET @BackupServerName = N'LOCALSQLSERVER' -- Type the backup server name SET @BackupShare = N'D:\ICAS\BACKUP' -- Type the IP address of the backup server's backup NIC and the sharename --SET @DoAFullBackup = 0 -- If this value is set to 1 a full backup of all the new databases will be taken ----------------------------------------------------------------------------------------------------------- -- Input section 2/2 end ----------------------------------------------------------------------------------------------------------- USE [msdb] /****** Object: Job [Create_Backup_Jobs] Script Date: 10/02/2008 05:34:18 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [BACKUP] Script Date: 10/02/2008 05:34:18 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Create_Backup_Jobs',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Creates backup jobs for all the databases',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Enable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Enable XP_cmdshell',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=5,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 1
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Backup Folders] Script Date: 10/02/2008 05:34:19 ******/
SET @CommandString = N'DECLARE @ServerName VARCHAR(50)
DECLARE @CI VARCHAR(100)
DECLARE @DbName VARCHAR(100)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @ShareName VARCHAR(200)
DECLARE @CommandString VARCHAR(500)
DECLARE @foldermissing int
DECLARE Database_Cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases
-----------------------------------------------------------------------------------------------------------
-- Input section start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
SET @BackupShare = N''' + @BackupShare + ''' -- Type the IP address of the backup server''s backup NIC and the sharename
-----------------------------------------------------------------------------------------------------------
-- Input section end
-----------------------------------------------------------------------------------------------------------
/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY(''MachineName''))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY(''InstanceName''))
IF @CI IS NULL
SET @CI = @ServerName
ELSE
SET @CI = @ServerName + ''_'' + @CI
/* Get server and instance name end */
/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ShareName = @BackupShare + ''\'' + @CI + ''\'' + @DbName
/*Check if the backupfolder exists and if it doesn''t - create it - start*/
SET @CommandString = ''dir '' + @ShareName
EXEC @foldermissing = master..xp_cmdshell @CommandString
IF @foldermissing = 1
BEGIN
SET @CommandString = ''mkdir '' + @ShareName
EXEC master..xp_cmdshell @CommandString
END
FETCH NEXT FROM Database_Cursor INTO @DbName
END
/*Check if the backupfolder exists and if it doesn''t - create it - end*/
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
go'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Folders',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=5,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@CommandString,
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 0
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Backup Jobs] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Jobs',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command= N'EXEC msdb.dbo.usp_CreateBackupJobs',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell on failure] Script Date: 10/03/2008 10:59:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell on failure',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=2,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''XP_cmdshell'', 0
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081002,
@active_end_date=99991231,
@active_start_time=170000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081002,
@active_end_date=99991231,
@active_start_time=55710,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
go
/* Uncomment the below code if you like to create Backup jobs now*************/
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
EXEC sp_start_job Create_Backup_Jobs
SQL SERVER - Verifica ce modificari s-au facut dupa o anumita ora
SELECT name,
TYPE,
type_desc,
create_date,
modify_date
FROM sys.objects
WHERE TYPE IN ('U','V','PK','F','D','P')
AND modify_date >= Dateadd(HOUR,21,Cast((Cast(Getdate() - 1 AS VARCHAR(12))) AS SMALLDATETIME))
ORDER BY modify_date
--U - Table
--V - View
--PK - Primary Key
--F - Foreign Key
--D - Default Constraint
--P - Procedure
TYPE,
type_desc,
create_date,
modify_date
FROM sys.objects
WHERE TYPE IN ('U','V','PK','F','D','P')
AND modify_date >= Dateadd(HOUR,21,Cast((Cast(Getdate() - 1 AS VARCHAR(12))) AS SMALLDATETIME))
ORDER BY modify_date
--U - Table
--V - View
--PK - Primary Key
--F - Foreign Key
--D - Default Constraint
--P - Procedure
SQL SERVER - Cautare sir de caractere in toate coloanele
USE test2010
--initialize transaction
set transaction isolation level read uncommitted
set nocount on
--initial declarations
declare @rowID int, @maxRowID int
declare @sql nvarchar(4000)
declare @searchValue varchar(100)
declare @statements table (rowID int, SQL varchar(8000))
create table #results (tableName varchar(250), tableSchema varchar(250), columnName varchar(250))
set @rowID = 1
set @searchValue = 'CACAO'
--create CTE table holding metadata
;with MyInfo (tableName, tableSchema, columnName) as (
select table_name, table_schema, column_name from information_schema.columns where data_type not in ('image','text','timestamp','binary','uniqueidentifier')
)
--create search strings
insert into @statements
select row_number() over (order by tableName, columnName) as rowID, 'insert into #results select distinct '''+tableName+''', '''+tableSchema+''', '''+columnName+''' from ['+tableSchema+'].['+tableName+'] where convert(varchar,['+columnName+']) like ''%'+@searchValue+'%''' from myInfo
--initialize while components and process search strings
select @maxRowID = max(rowID) from @statements
while @rowID <= @maxRowID
begin
select @sql = sql from @statements where rowID = @rowID
exec sp_executeSQL @sql
set @rowID = @rowID + 1
end
--view results and cleanup
select * from #results
drop table #results
--initialize transaction
set transaction isolation level read uncommitted
set nocount on
--initial declarations
declare @rowID int, @maxRowID int
declare @sql nvarchar(4000)
declare @searchValue varchar(100)
declare @statements table (rowID int, SQL varchar(8000))
create table #results (tableName varchar(250), tableSchema varchar(250), columnName varchar(250))
set @rowID = 1
set @searchValue = 'CACAO'
--create CTE table holding metadata
;with MyInfo (tableName, tableSchema, columnName) as (
select table_name, table_schema, column_name from information_schema.columns where data_type not in ('image','text','timestamp','binary','uniqueidentifier')
)
--create search strings
insert into @statements
select row_number() over (order by tableName, columnName) as rowID, 'insert into #results select distinct '''+tableName+''', '''+tableSchema+''', '''+columnName+''' from ['+tableSchema+'].['+tableName+'] where convert(varchar,['+columnName+']) like ''%'+@searchValue+'%''' from myInfo
--initialize while components and process search strings
select @maxRowID = max(rowID) from @statements
while @rowID <= @maxRowID
begin
select @sql = sql from @statements where rowID = @rowID
exec sp_executeSQL @sql
set @rowID = @rowID + 1
end
--view results and cleanup
select * from #results
drop table #results
Sql Server - Tabele fara Index
--Tables without PK on them
SELECT c.name, b.name
FROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = 'U'
AND NOT EXISTS
(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = 'PK')
--To Find Heap tables.
select a.name, b.object_id, b.object_id from sys.tables a
inner join sys.indexes b
on a.object_id = b.object_id
and b.type_desc='HEAP'
SELECT c.name, b.name
FROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = 'U'
AND NOT EXISTS
(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = 'PK')
--To Find Heap tables.
select a.name, b.object_id, b.object_id from sys.tables a
inner join sys.indexes b
on a.object_id = b.object_id
and b.type_desc='HEAP'
Calculeaza MAX(Camp Caracter)
SELECT MAX(CONVERT(int, dbo.STRFILTER(NumarDoc, '0123456789')))+1 FROM Intrari
-- CHRTRAN() User-Defined Function
-- Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
-- CHRTRAN (@cExpressionSearched, @cSearchExpression, @cReplacementExpression)
-- Return Values nvarchar
-- Parameters
-- @cSearchedExpression Specifies the expression in which CHRTRAN( ) replaces characters.
-- @cSearchExpression Specifies the expression containing the characters CHRTRAN( ) looks for in @cSearchedExpression.
-- @cReplacementExpression Specifies the expression containing the replacement characters.
-- If a character in cSearchExpression is found in cSearchedExpression, the character in @cSearchedExpression is replaced by a character from @cReplacementExpression
-- that is in the same position in @cReplacementExpression as the respective character in @cSearchExpression.
-- If @cReplacementExpression has fewer characters than @cSearchExpression, the additional characters in @cSearchExpression are deleted from @cSearchedExpression.
-- If @cReplacementExpression has more characters than @cSearchExpression, the additional characters in @cReplacementExpression are ignored.
-- Remarks
-- CHRTRAN() translates the character expression @cSearchedExpression using the translation expressions @cSearchExpression and @cReplacementExpression and returns the resulting character string.
-- CHRTRAN is similar to a function Oracle PL/SQL TRANSLATE
-- Example
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZ') -- Displays XBYDZF
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZQRST') -- Displays XBYDZF
-- See Also STRFILTER()
-- UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro
/* -- this algorithm does not work as
-- select dbo.CHRTRAN2('eaba','ba','a') -- Displays e Error !!!
-- select dbo.CHRTRAN('eaba','ba','a') -- Displays ea Correctly
while @i <= @len select @cExpressionSearched = replace(cast(@cExpressionSearched as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(substring(@cSearchExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN, cast(substring(@cReplacementExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN ) , @i = @i + 1 return @cExpressionSearched */ CREATE function CHRTRAN (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(256), @cReplacementExpression nvarchar(256)) returns nvarchar(4000) as begin declare @len smallint, @i smallint, @j smallint, @cExpressionTranslated nvarchar(4000) select @cExpressionTranslated = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode while @i <= @len begin select @j = dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default) if @j > 0
select @cExpressionTranslated = @cExpressionTranslated + substring(@cReplacementExpression, @j , 1)
else
select @cExpressionTranslated = @cExpressionTranslated + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end
return @cExpressionTranslated
end
GO
-- STRFILTER() User-Defined Function
-- Removes all characters from a string except those specified.
-- STRFILTER(@cExpressionSearched, @cSearchExpression)
-- Return Values nvarchar
-- Parameters
-- @cExpressionSearched Specifies the character string to search.
-- @cSearchExpression Specifies the characters to search for and retain in cString.
-- Remarks
-- STRFILTER( ) removes all the characters from @cExpressionSearched that are not in @cSearchExpression, then returns the characters that remain.
-- Example
-- select dbo.STRFILTER('asdfghh5hh1jk6f3b7mn8m3m0m6','0123456789') -- Displays 516378306
-- select dbo.STRFILTER('ABCDABCDABCD', 'AB') -- Displays ABABAB
-- See Also CHRTRAN()
-- UDF the name and functionality of which correspond to the same functions of Foxtools ( Foxtools is a Visual FoxPro API library)
CREATE function STRFILTER (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @len smallint, @i smallint, @StrFiltred nvarchar(4000)
select @StrFiltred = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while @i <= @len begin if dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default) > 0
select @StrFiltred = @StrFiltred + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end
return @StrFiltred
end
GO
-- Is similar to the built-in function Transact-SQL charindex, but regardless of collation settings,
-- executes case-sensitive search
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
CREATE function CHARINDEX_BIN(@expression1 nvarchar(4000), @expression2 nvarchar(4000), @start_location smallint = 1)
returns nvarchar(4000)
as
begin
return charindex( cast(@expression1 as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(@expression2 as nvarchar(4000)) COLLATE Latin1_General_BIN, @start_location )
end
GO
-- CHRTRAN() User-Defined Function
-- Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
-- CHRTRAN (@cExpressionSearched, @cSearchExpression, @cReplacementExpression)
-- Return Values nvarchar
-- Parameters
-- @cSearchedExpression Specifies the expression in which CHRTRAN( ) replaces characters.
-- @cSearchExpression Specifies the expression containing the characters CHRTRAN( ) looks for in @cSearchedExpression.
-- @cReplacementExpression Specifies the expression containing the replacement characters.
-- If a character in cSearchExpression is found in cSearchedExpression, the character in @cSearchedExpression is replaced by a character from @cReplacementExpression
-- that is in the same position in @cReplacementExpression as the respective character in @cSearchExpression.
-- If @cReplacementExpression has fewer characters than @cSearchExpression, the additional characters in @cSearchExpression are deleted from @cSearchedExpression.
-- If @cReplacementExpression has more characters than @cSearchExpression, the additional characters in @cReplacementExpression are ignored.
-- Remarks
-- CHRTRAN() translates the character expression @cSearchedExpression using the translation expressions @cSearchExpression and @cReplacementExpression and returns the resulting character string.
-- CHRTRAN is similar to a function Oracle PL/SQL TRANSLATE
-- Example
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZ') -- Displays XBYDZF
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZQRST') -- Displays XBYDZF
-- See Also STRFILTER()
-- UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro
/* -- this algorithm does not work as
-- select dbo.CHRTRAN2('eaba','ba','a') -- Displays e Error !!!
-- select dbo.CHRTRAN('eaba','ba','a') -- Displays ea Correctly
while @i <= @len select @cExpressionSearched = replace(cast(@cExpressionSearched as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(substring(@cSearchExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN, cast(substring(@cReplacementExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN ) , @i = @i + 1 return @cExpressionSearched */ CREATE function CHRTRAN (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(256), @cReplacementExpression nvarchar(256)) returns nvarchar(4000) as begin declare @len smallint, @i smallint, @j smallint, @cExpressionTranslated nvarchar(4000) select @cExpressionTranslated = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode while @i <= @len begin select @j = dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default) if @j > 0
select @cExpressionTranslated = @cExpressionTranslated + substring(@cReplacementExpression, @j , 1)
else
select @cExpressionTranslated = @cExpressionTranslated + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end
return @cExpressionTranslated
end
GO
-- STRFILTER() User-Defined Function
-- Removes all characters from a string except those specified.
-- STRFILTER(@cExpressionSearched, @cSearchExpression)
-- Return Values nvarchar
-- Parameters
-- @cExpressionSearched Specifies the character string to search.
-- @cSearchExpression Specifies the characters to search for and retain in cString.
-- Remarks
-- STRFILTER( ) removes all the characters from @cExpressionSearched that are not in @cSearchExpression, then returns the characters that remain.
-- Example
-- select dbo.STRFILTER('asdfghh5hh1jk6f3b7mn8m3m0m6','0123456789') -- Displays 516378306
-- select dbo.STRFILTER('ABCDABCDABCD', 'AB') -- Displays ABABAB
-- See Also CHRTRAN()
-- UDF the name and functionality of which correspond to the same functions of Foxtools ( Foxtools is a Visual FoxPro API library)
CREATE function STRFILTER (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @len smallint, @i smallint, @StrFiltred nvarchar(4000)
select @StrFiltred = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while @i <= @len begin if dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default) > 0
select @StrFiltred = @StrFiltred + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end
return @StrFiltred
end
GO
-- Is similar to the built-in function Transact-SQL charindex, but regardless of collation settings,
-- executes case-sensitive search
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
CREATE function CHARINDEX_BIN(@expression1 nvarchar(4000), @expression2 nvarchar(4000), @start_location smallint = 1)
returns nvarchar(4000)
as
begin
return charindex( cast(@expression1 as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(@expression2 as nvarchar(4000)) COLLATE Latin1_General_BIN, @start_location )
end
GO
marți, 1 iunie 2010
Backup SQL SERVER
Local loserver, lnCounter, loBackupDevice
loserver=Createobject("SQLDMO.SqlServer")
loserver.Connect("MiServidor","MiUserName","MiPassword")
If loserver.BackupDevices.Count = 0
loBackupDevice = Createobject("SQLDMO.BackupDevice")
With loBackupDevice
.Name = "MiNuevoBackup"
.PhysicalLocation = "C:\MisBackups\BackupFile.bak"
.Type = 2 && SQLDMODevice_DiskDump
Endwith
loserver.BackupDevices.Add(loBackupDevice)
Else
For lnCounter = 1 To loserver.BackupDevices.Count
? loserver.BackupDevices(lnCounter).PhysicalLocation
Next lnCounter
Endif
loserver=Createobject("SQLDMO.SqlServer")
loserver.Connect("MiServidor","MiUserName","MiPassword")
If loserver.BackupDevices.Count = 0
loBackupDevice = Createobject("SQLDMO.BackupDevice")
With loBackupDevice
.Name = "MiNuevoBackup"
.PhysicalLocation = "C:\MisBackups\BackupFile.bak"
.Type = 2 && SQLDMODevice_DiskDump
Endwith
loserver.BackupDevices.Add(loBackupDevice)
Else
For lnCounter = 1 To loserver.BackupDevices.Count
? loserver.BackupDevices(lnCounter).PhysicalLocation
Next lnCounter
Endif
Compactare Baza de date ACCESS din VFP
OJE = CreateObject ("JRO.JetEngine)
oJE.CompactDatabase("Data Source =" + "d:\DB_OLD.mdb", "Data Source =" + "D:\DB_NEW.mdb)
oJE.CompactDatabase("Data Source =" + "d:\DB_OLD.mdb", "Data Source =" + "D:\DB_NEW.mdb)
Windows User is Administrator?
Declare Integer IsUserAnAdmin In shell32
RETURN IIF(IsUserAnAdmin () = 1, "DA","NU")
RETURN IIF(IsUserAnAdmin () = 1, "DA","NU")
sâmbătă, 29 mai 2010
Grid Reconstruction
1. Grid reconstructs self always when RecordSource alias closed. If this is a view, reconstruction usually do not happens when you requery view, however, there are some reports that this could happen, so test carefully your application to see if view requery do not cause the grid reconstruction. If it is SQL statement, it happens when you assing anotehr SQL statement or just close alias used to store results of the query for grid. It also happens when you use SQL Pass-Through to query data into the alias that used as a record source of grid.
To avoid reconstruction when refreshing grid's record source, you require to assign empty string (not a one space - " ", but empty string - "") to the Record source before any of grid's record source refresh actions described above. If you already do that, just check your code if you do that in correct order or any other thing does not spoil the correct order of refreshing process. After refresh assign record source to grid again. Reconstruction in such case does not happen, however, another problem arises - all grid's columns re-bound to the control sources automatically by the phisical fields order. Following is a sample of how to fix that by little of code.
* remember control sources in the column's comment field
with {grid}
local nColumnIndex
for m.nColumnIndex = 1 to .ColumnCount
.Columns(m.nColumnIndex).Comment = .Columns(m.nColumnIndex).ControlSource
endfor
* prepare grid for refreshing of the Record Source
.RecordSource = ""
endwith
* do refreshing of the record source
...........
with {grid}
* restore record source
.RecordSource = "{RecordSourceName}"
* restore control sources
for m.nColumnIndex = 1 to .ColumnCount
.Columns(m.nColumnIndex).ControlSource = .Columns(m.nColumnIndex).Comment
endfor
endwith
In above code {grid} is a reference to the grid object, {RecordSourceName} is a name of the alias used as a record source or SQL statement.
Significant note: do not do any refreshing of the visible controls or grid on the form after statement 'RecordSource=""' and up to full restore of the control sources. Otherwise you will meet a problem with the error message like 'Type is not supported by control' in case you use costom controls in grid columns. This because after spoiling control sources incorrect field types might be used for column. For example, when you have a checkbox in the grid column, after refresh of record source column with checkbox often get a character field control source. If then you refresh a grid, you will get an error or something weird might happen like crashes or bad refresh.
With views, this is the most common situation because grid used often to display data dynamically, so it requires to be refreshed by another data. REFRESH() command for view does not cause reconstruction, however it could be used to refresh data only record-by record. For real refresh requery is needed. The main mistake here also is just requery view and leave it as is. Its a single command, so programmers often do this in many places without aware that they're doing something bad. After that, when reconstruction behavior observed for some view requery, programmer starts to find all places where this view is requeried. It might be in many places across forms and classes that starts to be a big problem. Tip: put data requery (and all other actions with data) in one place - class method or function. Always suggest that any data function might require in future some additional code, even when it is as simple as a requery of view. This way you will help youself to save a time to find all places where some action done with data in case you need to modify something. Grid reconstruction is one of such cases that you cannot avoid when it appears.
NEW!
Another approach to prevent grid reconstruction is to use BeforeRowColChange event of the grid.
BeforeRowColChange event is fired each time when grid is going to be reconstructed. It happens in any case include when grid alias closed, view requeried etc. despite grid visibility, focus and grid configuration. The most amazing is that putting NODEFAULT in this event for duration of data changes prevents grid reconstruction at all! Example:
thisform.GridRefreshing = .T. && tell all grid controls that grid data going to be requeried
... do data requery
thisform.Grid.RecordSource = thisform.Grid.RecordSource
thisform.Refresh && or grid refresh
DOEVENTS
&& after this moment grid stops to reconstruct self
thisform.GridRefreshing = .F.
In the BeforeRowColChange of grid class event put following:
if PEMStatus(thisform,"GridRefreshing",5) AND thisform.GridRefreshing
nodefault
return
endif
You can put above code in the grid class so this functionality will be generic.
The best thing is that this method do not require to organize restoring of the control sources of all columns. However, sometimes ot is require to set focus outside of grid and set it back to grid, because the current cell in grid might show asterisks ('*******') when avoiding reconstruction this way.
Unfortunately, there are no way to know the reason why BeforeRowColChange event is called to distinguish if it is called for recornstruction or it is called for movement between cells or some other actions with grid. Just use a flag for that as in the sample.
2. Reconstruction happens when grid is initialized and record source property is empty or record source does not exists (alias is not open). In this case grid reconstructs self and use current alias as a record source if opened (or keep self empty if no alias opened in current workarea, but all columns destroyed anyway). If you need to open record source in some other event than Load event of the form (before grid initializing), use following technique.
In the Load event of form create an empty cursor with the same structure as a record source for grid; record source property of grid should use that empty cursor. Then, when you open real data, assign empty string to grid's record source, open data and then assign again real data alias as described in the paragraph 1. For case you need a generic container with grid, put an invisible custom control that will create empty cursor in its Init event. However, assure that Init event of that control fires BEFORE Init event of the grid, otherwise reconstruction will happen.
3. Grid reconstructs self when column count changed to 0 or -1. I hope you never do this, do you? ;)
4. Grid reconstructs self when alias used as record source and it goes out of scope. This usually happens when record source assigned in one data session, but grid really initialized in another
data session, so when it tries to refresh self, another data session used where record source does not exists. This may occur also in other situations when programmer uses data sessions switching extensively.
Another popular approach to eliminate the grid reconstruction problem is dynamic grid creation. Make a custom grid class with all your code and columns definitions. When requery data, remove grid control from form, requery data, then add grid to form again in run-time. This requires to handle first adding of grid, set some properties of grid etc etc.
You can also create grid object in run-time and populate it by the custom controls using code. (Note that you can define custom header class.) However, after grid reconstructs self, you need to add these custom controls to grid again. This approach used in case when grid reconstruction is not avoidable, for example, in the administrative programs - to show any table content in the same grid, but also allow some functionality in grid like editboxes to see memo fields, sorting by click on headers etc. The sample code of creating grid in run-time and add some custom controls you can see in the FAQ#721
I hope this help you to figuire out a problem with your grid. Good luck and don't be frustrated, grid is very good control in VFP with no analogue!
To avoid reconstruction when refreshing grid's record source, you require to assign empty string (not a one space - " ", but empty string - "") to the Record source before any of grid's record source refresh actions described above. If you already do that, just check your code if you do that in correct order or any other thing does not spoil the correct order of refreshing process. After refresh assign record source to grid again. Reconstruction in such case does not happen, however, another problem arises - all grid's columns re-bound to the control sources automatically by the phisical fields order. Following is a sample of how to fix that by little of code.
* remember control sources in the column's comment field
with {grid}
local nColumnIndex
for m.nColumnIndex = 1 to .ColumnCount
.Columns(m.nColumnIndex).Comment = .Columns(m.nColumnIndex).ControlSource
endfor
* prepare grid for refreshing of the Record Source
.RecordSource = ""
endwith
* do refreshing of the record source
...........
with {grid}
* restore record source
.RecordSource = "{RecordSourceName}"
* restore control sources
for m.nColumnIndex = 1 to .ColumnCount
.Columns(m.nColumnIndex).ControlSource = .Columns(m.nColumnIndex).Comment
endfor
endwith
In above code {grid} is a reference to the grid object, {RecordSourceName} is a name of the alias used as a record source or SQL statement.
Significant note: do not do any refreshing of the visible controls or grid on the form after statement 'RecordSource=""' and up to full restore of the control sources. Otherwise you will meet a problem with the error message like 'Type is not supported by control' in case you use costom controls in grid columns. This because after spoiling control sources incorrect field types might be used for column. For example, when you have a checkbox in the grid column, after refresh of record source column with checkbox often get a character field control source. If then you refresh a grid, you will get an error or something weird might happen like crashes or bad refresh.
With views, this is the most common situation because grid used often to display data dynamically, so it requires to be refreshed by another data. REFRESH() command for view does not cause reconstruction, however it could be used to refresh data only record-by record. For real refresh requery is needed. The main mistake here also is just requery view and leave it as is. Its a single command, so programmers often do this in many places without aware that they're doing something bad. After that, when reconstruction behavior observed for some view requery, programmer starts to find all places where this view is requeried. It might be in many places across forms and classes that starts to be a big problem. Tip: put data requery (and all other actions with data) in one place - class method or function. Always suggest that any data function might require in future some additional code, even when it is as simple as a requery of view. This way you will help youself to save a time to find all places where some action done with data in case you need to modify something. Grid reconstruction is one of such cases that you cannot avoid when it appears.
NEW!
Another approach to prevent grid reconstruction is to use BeforeRowColChange event of the grid.
BeforeRowColChange event is fired each time when grid is going to be reconstructed. It happens in any case include when grid alias closed, view requeried etc. despite grid visibility, focus and grid configuration. The most amazing is that putting NODEFAULT in this event for duration of data changes prevents grid reconstruction at all! Example:
thisform.GridRefreshing = .T. && tell all grid controls that grid data going to be requeried
... do data requery
thisform.Grid.RecordSource = thisform.Grid.RecordSource
thisform.Refresh && or grid refresh
DOEVENTS
&& after this moment grid stops to reconstruct self
thisform.GridRefreshing = .F.
In the BeforeRowColChange of grid class event put following:
if PEMStatus(thisform,"GridRefreshing",5) AND thisform.GridRefreshing
nodefault
return
endif
You can put above code in the grid class so this functionality will be generic.
The best thing is that this method do not require to organize restoring of the control sources of all columns. However, sometimes ot is require to set focus outside of grid and set it back to grid, because the current cell in grid might show asterisks ('*******') when avoiding reconstruction this way.
Unfortunately, there are no way to know the reason why BeforeRowColChange event is called to distinguish if it is called for recornstruction or it is called for movement between cells or some other actions with grid. Just use a flag for that as in the sample.
2. Reconstruction happens when grid is initialized and record source property is empty or record source does not exists (alias is not open). In this case grid reconstructs self and use current alias as a record source if opened (or keep self empty if no alias opened in current workarea, but all columns destroyed anyway). If you need to open record source in some other event than Load event of the form (before grid initializing), use following technique.
In the Load event of form create an empty cursor with the same structure as a record source for grid; record source property of grid should use that empty cursor. Then, when you open real data, assign empty string to grid's record source, open data and then assign again real data alias as described in the paragraph 1. For case you need a generic container with grid, put an invisible custom control that will create empty cursor in its Init event. However, assure that Init event of that control fires BEFORE Init event of the grid, otherwise reconstruction will happen.
3. Grid reconstructs self when column count changed to 0 or -1. I hope you never do this, do you? ;)
4. Grid reconstructs self when alias used as record source and it goes out of scope. This usually happens when record source assigned in one data session, but grid really initialized in another
data session, so when it tries to refresh self, another data session used where record source does not exists. This may occur also in other situations when programmer uses data sessions switching extensively.
Another popular approach to eliminate the grid reconstruction problem is dynamic grid creation. Make a custom grid class with all your code and columns definitions. When requery data, remove grid control from form, requery data, then add grid to form again in run-time. This requires to handle first adding of grid, set some properties of grid etc etc.
You can also create grid object in run-time and populate it by the custom controls using code. (Note that you can define custom header class.) However, after grid reconstructs self, you need to add these custom controls to grid again. This approach used in case when grid reconstruction is not avoidable, for example, in the administrative programs - to show any table content in the same grid, but also allow some functionality in grid like editboxes to see memo fields, sorting by click on headers etc. The sample code of creating grid in run-time and add some custom controls you can see in the FAQ#721
I hope this help you to figuire out a problem with your grid. Good luck and don't be frustrated, grid is very good control in VFP with no analogue!
Abonați-vă la:
Postări (Atom)