Skip to content

Instantly share code, notes, and snippets.

@MrToxy

MrToxy/CDPSI.UPDATE.SQL_PARSER

Created May 4, 2016 15:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save MrToxy/c7fd17d21c348e41247bd2fabdbfd20d to your computer and use it in GitHub Desktop.
Save MrToxy/c7fd17d21c348e41247bd2fabdbfd20d to your computer and use it in GitHub Desktop.
Download ZIP
This function is intended to parse sql commands from a .txt file
Raw
CDPSI.UPDATE.SQL_PARSER
public class CDPSI_UPDATE_SQL_PARSER
{
private const string DashComment = @"(^|\s+)--.*(\n|$)";
private const string SlashStarComment = @"\/\*.*?\*\/";
public string[] Parser(string caminho)
{
string text = File.ReadAllText(caminho);
var strip1 = Regex.Replace(text, SlashStarComment, " ", RegexOptions.Multiline);
var strip2 = Regex.Replace(strip1, DashComment, "\n", RegexOptions.Multiline);
// split into individual commands separated by '/'
var commands = strip2.Split(new[] { '/' }, StringSplitOptions.RemoveEmptyEntries);
return commands.Select(cmd => cmd.Split(new[] { '\n' })
.Select(l => l.Trim()))
.Select(lines => string.Join("\n", lines.Where(l => !string.IsNullOrWhiteSpace(l))))
.ToArray();
}
public string ParseStatemant(string comando)
{
string ComandoFinal = string.Empty;
parse:
ComandoFinal = comando.Remove(comando.IndexOf("/*") - 2, comando.IndexOf("*/") + 2);
if (ComandoFinal.Contains("/*"))
goto parse;
return ComandoFinal;
}
}
Raw
SQL Commands
UPDATE APPLCONFIG SET GGDSC='3.5.96' WHERE GGCOD='APP_NAME'
/
UPDATE APPLCONFIG SET GGDSC='3.5.96' WHERE GGCOD='NEG_NAME'
/
UPDATE APPLCONFIG SET GGDSC='3.5.96' WHERE GGCOD='DAT_NAME'
/
ALTER TABLE PROPOSTAS ADD (COEFGERAL_PLT NUMBER(3,6) DEFAULT 0)
/
ALTER TABLE ORC
ADD MercadoInt VARCHAR2(1)
ADD Coef_KrMo NUMBER(20,10)
ADD Coef_KrMt NUMBER(20,10)
ADD Coef_KrEq NUMBER(20,10)
ADD Coef_KrSb NUMBER(20,10)
ADD Coef_KrGb NUMBER(20,10)
ADD Coef_MDEmp NUMBER(20,10)
ADD Coef_MDLoc NUMBER(20,10)
ADD Abrv_MDLoc VARCHAR2(10)
ADD Dsc_MDLoc VARCHAR2(50)
ADD Arred_MDLoc VARCHAR2(1)
ADD Arred_NDecs NUMBER(1)
/
UPDATE Orc set MercadoInt='N', Coef_KrMo=1, Coef_KrMt=1, Coef_KrEq=1, Coef_KrSb=1, Coef_KrGb=1, Coef_MDEmp=1, Coef_MDLoc=1, Abrv_MDLoc='', Dsc_MDLoc='', Arred_MDLoc='N', Arred_NDecs=0 WHERE MercadoInt IS NULL
/
DROP VIEW VW_EXPSUBEEXCELL
/
CREATE OR REPLACE FORCE VIEW VW_EXPSUBEEXCELL (refcod,
refdsc,
qtdprc,
unidmovcod,
recurcod,
rgmcod,
recver,
nomesistema,
tprec,
pedido,
linpos,
orccod1,
orcver1,
nomesistema1,
subeorccod1,
lintp,
linver
)
AS
SELECT '' AS refcod, a.linindex || ' ' || a.lindsc AS refdsc, null AS qtdprc,
'' AS unidmovcod, '' AS recurcod, '' AS rgmcod, '' AS recver,
'' AS nomesistema, '4' AS tprec, 0 AS pedido, a.linpos,
grpl.orccod AS orccod1, grpl.orcver AS orcver1,
grpl.nomesistema AS nomesistema1, grpl.subeorccod AS subeorccod1,
a.lintp, a.linver
FROM articlin a, subeorclin grpl
WHERE grpl.orccod = a.orccod
AND grpl.orcver = a.orcver
AND grpl.nomesistema = a.nomesistema
AND grpl.lincod = a.lincod
AND grpl.linver = a.linver
AND a.lintp IN ('CP', 'CC')
UNION ALL
SELECT TO_CHAR (r.refcod) AS refcod,
a.linindex || ' ' || a.lindsc AS refdsc,
r.qtdprc AS qtdprc, TO_CHAR (r.unidmovcod) AS unidmovcod,
TO_CHAR (r.recurcod) AS recurcod, TO_CHAR (r.rgmcod) AS rgmcod,
TO_CHAR (r.recver) AS recver, TO_CHAR (r.nomesistema)
AS nomesistema,
'4' AS tprec, 0 AS pedido, a.linpos, grpl.orccod AS orccod1,
grpl.orcver AS orcver1, grpl.nomesistema AS nomesistema1,
grpl.subeorccod AS subeorccod1, a.lintp, a.linver
FROM articlin a, subeorclin grpl, recursos r
WHERE grpl.orccod = a.orccod
AND grpl.orcver = a.orcver
AND grpl.nomesistema = a.nomesistema
AND grpl.lincod = a.lincod
AND grpl.linver = a.linver
AND a.lintp IN ('C', 'D')
AND grpl.orccod = r.orccod
AND grpl.orcver = r.orcver
AND grpl.nomesistema = r.nomesistema
AND grpl.recurcod = r.recurcod
AND grpl.recver = r.recver
AND grpl.rgmcod = r.rgmcod
UNION ALL
SELECT TO_CHAR (r.refcod) AS refcod, r.refdsc AS refdsc,
grpl.qtd AS qtdprc, TO_CHAR (r.unidmovcod) AS unidmovcod,
TO_CHAR (r.recurcod) AS recurcod, TO_CHAR (r.rgmcod) AS rgmcod,
TO_CHAR (r.recver) AS recver, TO_CHAR (r.nomesistema)
AS nomesistema,
'4' AS tprec, 0 AS pedido, grpl.subeorclincod,
grpl.orccod AS orccod1, grpl.orcver AS orcver1,
grpl.nomesistema AS nomesistema1, grpl.subeorccod AS subeorccod1,
'' AS lintp, 0 AS linver
FROM subeorclin grpl, recursos r
WHERE grpl.orccod = r.orccod
AND grpl.orcver = r.orcver
AND grpl.nomesistema = r.nomesistema
AND grpl.recurcod = r.recurcod
AND grpl.recver = r.recver
AND grpl.rgmcod = r.rgmcod
AND grpl.lincod IS NULL
AND grpl.linver IS NULL;
/
--
/**/
DROP VIEW VW_PROPSRECEPS
/
CREATE OR REPLACE FORCE VIEW VW_PROPSRECEPS (orccod,
orcver,
nomesistema,
subeorccod,
subeorcdsc,
empcod,
empcodver,
empdsc,
pedtprec,
totproposta,
totdesconto,
totpropsel,
totmin,
pedcod
)
AS
SELECT
a.orccod, a.orcver, a.nomesistema, a.subeorccod, a.subeorcdsc,
p.empcod, p.empcodver, h.empdsc, b.pedtprec,
SUM ( (l.qtdorc * pp.indfis)
* (((p.prcunit * (1 + NVL(p.deconto,1))) * (1+NVL(p.coefgeral_plt,1))) * o.coef_krsb)
) TOTPROPOSTA,
SUM ( (CASE
WHEN NVL (p.deconto, 0) <> 0
THEN (l.qtdorc * pp.indfis)
* (((p.prcunit * nvl(p.deconto,1)) * nvl(p.COEFGERAL_PLT,1)) * NVL(o.coef_krsb,1))
ELSE 0
END
)
* (-1)
) TOTDESCONTO,
SUM ((CASE
WHEN p.empsel = 1
THEN (l.qtdorc * pp.indfis)
* (((p.prcunit * (1 + NVL(p.deconto,0))) * (1 + NVL(p.coefgeral_plt,0))) * o.coef_krsb
)
ELSE 0
END
)
) PROPSEL,
NVL (SUM ( (l.qtdorc * pp.indfis)
* (SELECT MIN (pp.prcunit * (1 + pp.deconto))
FROM propostas pp
WHERE p.orccod = pp.orccod
AND p.orcver = pp.orcver
AND p.nomesistema = pp.nomesistema
AND p.pedcod = pp.pedcod
AND p.recurcod = pp.recurcod
AND pp.prcunit > 0
AND p.prcunit = 0
AND NVL (p.obs, '@') = '@')
),
0
)
+ NVL (SUM ( (l.qtdorc * pp.indfis)
* (((p.prcunit * (1 + NVL(p.deconto,0))) * (1+NVL(p.coefgeral_plt,0))) * o.coef_krsb)
),
0
) TOTMIN,
b.pedcod
FROM subeorc a,
subeorclin s,
pedproposta b,
propostas p,
empresash h,
prcficha pp,
articlin l,
orc o
WHERE o.orccod = a.orccod
AND o.orcver = a.orcver
AND o.nomesistema = a.nomesistema
AND a.orccod = s.orccod
AND a.orcver = s.orcver
AND a.nomesistema = s.nomesistema
AND a.subeorccod = s.subeorccod
AND a.orccod = b.orccod
AND a.orcver = b.orcver
AND a.nomesistema = b.nomesistema
AND a.subeorccod = b.subeorccod
AND b.orccod = p.orccod
AND b.orcver = p.orcver
AND b.nomesistema = p.nomesistema
AND b.pedcod = p.pedcod
AND s.recurcod = p.recurcod
AND s.recver = p.recver
AND s.rgmcod = p.rgmcod
AND s.orccod = p.orccod
AND s.orcver = p.orcver
AND p.empcod = h.empcod
AND s.orccod = pp.orccod
AND s.orcver = pp.orcver
AND s.nomesistema = pp.nomesistema
AND s.recurcod = pp.recurcod
AND s.recver = pp.recver
AND s.rgmcod = pp.rgmcod
AND pp.orccod = l.orccod
AND pp.orcver = l.orcver
AND pp.nomesistema = l.nomesistema
AND pp.prccod = l.prccod
AND pp.prcver = l.prcver
AND l.linver = NVL (s.linver, 1)
GROUP BY a.orccod,
a.orcver,
a.nomesistema,
a.subeorccod,
a.subeorcdsc,
p.empcod,
p.empcodver,
b.pedtprec,
b.pedcod,
h.empdsc
/
---------------------------------------------------------------------
UPDATE CDPREPORTSQL
SET COMANDOSQL_FROM =
'SELECT DESCONTO,EMPCOD,EMPDSC,LINVER,NOMESISTEMA,OBS,ORCCOD,ORCVER,PEDCOD,PEDDSC,
-ROUND(PRCUNIT*#CAMBIO#,5) PRCUNIT,
ROUND(PRCUNITSEMDESC*#CAMBIO#,5) PRCUNITSEMDESC,
PROPCHECK,QTDGLOB,QTDPROP,REFCOD,REFDSC,EMPCODVER, COEFGERAL_PLT FROM #OWNER#.VW_PROPOSTAS',
COMANDOSQL_WHERE =
'WHERE ORCCOD=#ORCCOD# AND ORCVER=#ORCVER# AND NOMESISTEMA=#NOMESISTEMA# AND PEDCOD=#MYCOD#'
WHERE REPID = 'CDP0000057'
/
---------------------------------------------------------------------
DROP VIEW VW_PROPSRECEPSOUTROS
/
CREATE OR REPLACE FORCE VIEW VW_PROPSRECEPSOUTROS (orccod,
orcver,
nomesistema,
pedcod,
peddsc,
empcod,
empcodver,
empdsc,
pedtprec,
linver,
totproposta,
totdesconto,
totpropsel,
totmin
)
AS
SELECT b.orccod, b.orcver, b.nomesistema, b.pedcod, b.peddsc, p.empcod,
p.empcodver, h.empdsc, b.pedtprec, l.linver,
SUM ( ((l.qtdorc * pp.indfis) * ( (p.prcunit * (1 + p.deconto))) * (1 + p.coefgeral_plt)
* (CASE
WHEN r.tprec = '1'
THEN o.coef_krmo
WHEN r.tprec = '2'
THEN o.coef_krmt
WHEN r.tprec = '3'
THEN o.coef_kreq
WHEN r.tprec = '4'
THEN o.coef_krsb
ELSE 1
END
)
)
) totproposta,
SUM( (l.qtdorc * pp.indfis * p.prcunit ) - ((l.qtdorc * pp.indfis) * ( (p.prcunit * (1 + p.deconto))) * (1 + p.coefgeral_plt)
* (CASE
WHEN r.tprec = '1'
THEN o.coef_krmo
WHEN r.tprec = '2'
THEN o.coef_krmt
WHEN r.tprec = '3'
THEN o.coef_kreq
WHEN r.tprec = '4'
THEN o.coef_krsb
ELSE 1
END
)
)
) * -1 as TOTDESCONTO,
/* SUM
( (CASE
WHEN (NVL (p.deconto, 0) + NVL(p.coefgeral_plt,0)) <> 0
THEN (l.qtdorc * pp.indfis) * ((
(p.prcunit * (case when p.deconto <> 0 then p.deconto else 1 end)) *
case when p.coefgeral_plt <> 0 then p.coefgeral_plt else 0 end)
* (CASE
WHEN r.tprec = '1'
THEN o.coef_krmo
WHEN r.tprec = '2'
THEN o.coef_krmt
WHEN r.tprec = '3'
THEN o.coef_kreq
WHEN r.tprec = '4'
THEN o.coef_krsb
ELSE 1
END
)
)
ELSE 0
END
)
* (-1)
) totdesconto_old,*/
SUM
((CASE
WHEN p.empsel = 1
THEN ( (l.qtdorc * pp.indfis) * ((p.prcunit * (1 + p.deconto)) * (1 + p.coefgeral_plt))
* (CASE
WHEN r.tprec = '1'
THEN o.coef_krmo
WHEN r.tprec = '2'
THEN o.coef_krmt
WHEN r.tprec = '3'
THEN o.coef_kreq
WHEN r.tprec = '4'
THEN o.coef_krsb
ELSE 1
END
)
)
ELSE 0
END
)
) propsel,
NVL (SUM ( (l.qtdorc * pp.indfis) * (SELECT MIN ((pp.prcunit * (1 + pp.deconto)) * ( 1 + pp.coefgeral_plt))
FROM propostas pp
WHERE p.orccod = pp.orccod
AND p.orcver = pp.orcver
AND p.nomesistema = pp.nomesistema
AND p.pedcod = pp.pedcod
AND p.recurcod = pp.recurcod
AND pp.prcunit > 0
AND p.prcunit = 0
AND NVL (p.obs, '@') = '@')
),
0
)
+ NVL (SUM ( (l.qtdorc * pp.indfis) * (((p.prcunit * (1 + p.deconto)) * (1+p.coefgeral_plt))
* (CASE
WHEN r.tprec = '1'
THEN o.coef_krmo
WHEN r.tprec = '2'
THEN o.coef_krmt
WHEN r.tprec = '3'
THEN o.coef_kreq
WHEN r.tprec = '4'
THEN o.coef_krsb
ELSE 1
END
)
)
),
0
) totmin
FROM pedproposta b,
propostas p,
empresash h,
recursos r,
prcficha pp,
articlin l,
orc o
WHERE o.orccod = b.orccod
AND o.orcver = b.orcver
AND o.nomesistema = b.nomesistema
AND b.orccod = p.orccod
AND b.orcver = p.orcver
AND b.nomesistema = p.nomesistema
AND b.pedcod = p.pedcod
AND p.empcod = h.empcod
AND p.orccod = r.orccod
AND p.orcver = r.orcver
AND p.nomesistema = r.nomesistema
AND p.recurcod = r.recurcod
AND p.recver = r.recver
AND p.rgmcod = r.rgmcod
AND p.orccod = pp.orccod
AND p.orcver = pp.orcver
AND p.nomesistema = pp.nomesistema
AND p.recurcod = pp.recurcod
AND p.recver = pp.recver
AND p.rgmcod = pp.rgmcod
AND pp.orccod = l.orccod
AND pp.orcver = l.orcver
AND pp.nomesistema = l.nomesistema
AND pp.prccod = l.prccod
AND pp.prcver = l.prcver
and b.pedtprec = '2'
GROUP BY b.orccod,
b.orcver,
b.nomesistema,
p.empcod,
p.empcodver,
h.empdsc,
b.pedtprec,
l.linver,
b.pedcod,
b.peddsc
/
---------------------------------------------------------------------
DROP VIEW VW_PROPOSTAS
/
CREATE OR REPLACE FORCE VIEW VW_PROPOSTAS (orccod,
orcver,
nomesistema,
linver,
pedcod,
peddsc,
refcod,
refdsc,
prcunitsemdesc,
desconto,
qtdprop,
qtdglob,
prcunit,
obs,
propcheck,
empcod,
empcodver,
empdsc,
COEFGERAL_PLT,
PRC_DESC1,
PRC_DESC2
)
AS
SELECT a.orccod, a.orcver, a.nomesistema,
(CASE
WHEN p.pedtprec = 4
THEN s.linver
ELSE 0
END) as LinVer, p.pedcod, p.peddsc, r.refcod,
r.refdsc || '(' || a.unidmovcod || ')' refdsc,
b.prcunit AS prcunitsemdesc, b.deconto AS deconto,
fu_getqtdorc (a.orccod,
a.orcver,
a.nomesistema,
r.tprec,
a.recurcod,
a.recver,
a.rgmcod,
o.verlinbase,
'1'
) qtdprop,
fu_getqtdorc (a.orccod,
a.orcver,
a.nomesistema,
r.tprec,
a.recurcod,
a.recver,
a.rgmcod,
o.verlinbase,
'2'
) qtdglob,
((b.prcunit * (1 + b.deconto)) * (1 + b.COEFGERAL_PLT)) AS prcunit, b.obs AS obs,
b.propcheck AS propcheck, b.empcod, b.empcodver, h.empdsc,
b.COEFGERAL_PLT,
(b.prcunit * (1 + b.deconto)) AS PRC_DESC1,
((b.prcunit * (1 + b.deconto)) * (1 + b.COEFGERAL_PLT)) AS PRC_DESC2
FROM linped a,
propostas b,
recursos r,
empresash h,
pedproposta p,
subeorclin s,
orc o
WHERE a.pedcod = b.pedcod
AND a.orccod = b.orccod
AND a.orcver = b.orcver
AND a.nomesistema = b.nomesistema
AND a.recurcod = b.recurcod
AND a.rgmcod = b.rgmcod
AND a.recver = b.recver
AND a.orccod = r.orccod
AND a.orcver = r.orcver
AND a.nomesistema = r.nomesistema
AND a.recurcod = r.recurcod
AND a.rgmcod = r.rgmcod
AND a.recver = r.recver
AND a.orccod = o.orccod
AND a.orcver = o.orcver
AND a.nomesistema = o.nomesistema
AND b.empcod = h.empcod
AND a.orccod = p.orccod
AND a.orcver = p.orcver
AND a.nomesistema = p.nomesistema
AND a.pedcod = p.pedcod
AND a.orccod = s.orccod(+)
AND a.orcver = s.orcver(+)
AND a.nomesistema = s.nomesistema(+)
AND a.recurcod = s.recurcod(+)
AND a.rgmcod = s.rgmcod(+)
AND a.recver = s.recver(+)
/
INSERT INTO APPLCONFIG (GGCOD, GGTP, GGDSC, GGOBS, NIVEL)
VALUES ('MSF_FIXFFDATA', 'G', '1', '1-Grava a grid de folha de fecho antes de imprimir; 0-usa o que estiver de momento na BD...', 9)
/
INSERT INTO APPLCONFIG (GGCOD, GGTP, GGDSC, GGOBS, NIVEL)
VALUES ('MSF_SPECIFIC', 'G', 'MSF012010_X', 'Alterações á Folha de Fecho para a MSF ...', 9)
/
INSERT INTO APPLCONFIG (GGCOD, GGTP, GGDSC, GGOBS, NIVEL)
VALUES('MSF_PROPOSTAS', 'G', 'MSF082010_X', 'Alterações ao Ecran de Propostas para a MSF ...', 9)
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment