Début 20241030 14144181
PASSER PROLOGUE
20241030 14144181	Début gproc_NewConnecte
20241030 14144220	Fin gproc_NewConnecte

gproc_GET_Chantier_Conducteur
SELECT paprf 
FROM chdev.paperm 
WHERE 	pamat = (SELECT cacdt FROM chdev.cachan WHERE casec='BMC24911')
gproc_PUT_RecepCommandeComptoir
INSERT INTO chdev.harec (
caso, caag, hrdat,tgid, fousuf, hrlib, hartid, hafid, hrbl,
hrcpt,	hrnat, hrsec, hrqte, huniid, hrpri, hrmtt, hrval,
hrtpr, hrimo, hcdid, hclnum, hrcomm,
dcrt, hcrt, pcrt, pgcrt, 
dmaj, hmaj, pmaj, pgmaj,
hrtac, hrnpie, hrfac, hrhajn,hrhanp, hrhanl, hrhanf, hrori,
hrvald,hrvalh,hrvalp)
VALUES ('SD','BM','2024-10-28',11118,'001','Divers bordures béton',2884,0,'BL ESSAI','601100','2351','BMC24911',1,8,25.5,25.5,'O','N','N',1027,1,'',20241030,141411,'CFINSB','HARMOV3M',20241030,141411,'CFINSB','HARMOV3M',0,'','N','',0,0,'',0,'0001-01-01','00:00:00','')gproc_UPD_QteR_LigCdeHA
UPDATE %1hacdli
SET hclqtr	=hclqtr+%2,
dmaj	=	%3,
hmaj	=	%4,
pmaj	=	'%5',
pgmaj	=	'%6',
hclsld  =   '%7'
WHERE
hcdid	=	%8
AND
hclnum	=	%9gproc_UPD_QteR_LigCdeHA
UPDATE chdev.hacdli
SET hclqtr	=hclqtr+1,
dmaj	=	20241030,
hmaj	=	141411,
pmaj	=	'CFINSB',
pgmaj	=	'HARMOV3M',
hclsld  =   'N'
WHERE
hcdid	=	1027
AND
hclnum	=	1
gproc_GET_CommandeHA
SELECT 
hcdid AS nidCde,
caso AS sCodeSte,
caag AS sCodeAgence,
hcdnum AS nNumCde,
hcddat AS dCommande,
hcdref AS sRefCommande,
tgid AS nidFournisseur,
fousuf AS sSufFournisseur,
hcdlib AS sLibelle,
hcdfrf AS sRefFou,
hcdfcl AS sClientFou,
hcddal AS dLivraison,
hcdsec AS sSection,
hcdmtt AS rMontant,
hcdenv AS sModeEnvoi,
hcdimo AS sImmo,
hcdsld AS sSoldee,
hcdfac AS sFacturee,
hcdann AS sAnnulee,
hcdfor AS sForcee,
hcdsta AS sStatut,
hcddav AS dValidation,
hcdhev AS hValidation,
hcdprv AS pValidation,
hcdiln AS nNoteInfoLegale,
hcdild AS dInfolegale,
hcdilp AS sProfilInfolegale,
dcrt AS nDateCrea,
hcrt AS nHeureCrea,
pcrt AS sUtilCrea,
pgcrt AS sProgCrea,
dmaj AS nDateMAJ,
hmaj AS nHeureMAJ,
pmaj AS sUtilMAJ,
pgmaj AS sProgMAJ,
hcdbni AS sBlocNoteInterne,
hcdbne AS sBlocNoteImpression,
hcdtyp AS sTypeCommande,
hcddev AS sNDevis,
hsrvid AS sService,
hcdmtr AS rMttReceptionne,
hcdmtf AS rMttFacture,
anoma AS sNomAgence
FROM 
chdev.hacde 
LEFT JOIN chdev.agence ON acaag = caag
WHERE
1=1  AND hcddal BETWEEN '1900-01-01' and '2099-12-31' AND hcdid=1027 

GETAdress
SELECT 
*
FROM 
chdev.hacdad
WHERE
hcdid = 1027	gproc_GET_LigCdeHA
SELECT 
l.hcdid AS nidCde,
l.hclnum AS nNumLigne,
l.hcllib AS sLibelle,
l.hartid AS nIdArticle,
l.hafid AS nIdArtFou,
l.hclrff AS sRefFour,
l.hclqte AS rQte,
l.huniid AS nIdUnite,
l.hclpri AS rPU,
l.hclrem AS rRemise,
l.hclmtt AS rMontant,
l.hclsec AS sSection,
l.hclnat AS sNature,
l.hclimo AS sImmo,
l.hcldli AS dLivraison,
l.hclqtr AS rQteReceptionnee,
l.hclmtr AS rMontantReceptionne,
l.hclqtf AS rQteFacturee,
l.hclsld AS sSoldee ,
l.hclfac AS sFacturee,
l.dcrt AS nDateCrea,
l.hcrt AS nHeureCrea,
l.pcrt AS sUtilCrea,
l.pgcrt AS sProgCrea,
l.dmaj AS nDateMAJ,
l.hmaj AS nHeureMAJ,
l.pmaj AS sUtilMAJ,
l.pgmaj AS sProgMAJ,
hartg 
FROM chdev.hacdli L 
LEFT JOIN chdev.hart A on l.hartid = a.hartid
WHERE 1=1  AND hcdid =1027gproc_GET_ListeRecep
SELECT 	hrecid,
r.caso,
r.caag,
hrdat,
r.tgid,
r.fousuf,
hrlib,
r.hartid,
r.hafid,
hrbl,
hrcpt,
hrnat,
hrsec,
hrtac,
hrqte,
r.huniid,
hrpri,
hrmtt,
case when hrval='O' then 1 else 0 end,
case when hrtpr='O' then 1 else 0 end,
hrnpie,
case when hrfac='O' then 1 else 0 end,
hrhajn,
hrhanp,
hrhanl,
hrhanf,
case when hrimo='O' then 1 else 0 end,
r.hcdid,
hclnum,
hrori,
hrcomm,
r.dcrt,
r.hcrt,
r.pcrt,
r.pgcrt,
r.dmaj,
r.hmaj,
r.pmaj,
r.pgmaj,
c.hacali,
f.hafali,
s.hasfli,
t.hatyli,
VALUE(h.hafrfo,''),
fo.foulbc,
r.hrvald,
RIGHT('0'||hour(r.hrvalh),2)||right('0'||minute(r.hrvalh),2)||right('0'||second(r.hrvalh),2) as hrvalh,
r.hrvalp,
ch.cacno,
cd.hcdref,
cd.hcdnum,
a.hafaid,
fo.foucpt,
cd.hsrvid
FROM chdev.harec r
LEFT JOIN chdev.hart a 	 ON a.hartid=r.hartid
LEFT JOIN chdev.harcat c ON a.hacaid = c.hacaid
LEFT JOIN chdev.harfam f ON a.hafaid = f.hafaid
LEFT JOIN chdev.harssf s ON a.hasfid = s.hasfid
LEFT JOIN chdev.hartyp t ON a.hatyid = t.hatyid
LEFT JOIN chdev.hartfo  h ON r.hafid  = h.hafid 
LEFT JOIN chdev.hacde  cd ON r.hcdid  = cd.hcdid
LEFT JOIN chdev.tifou fo  ON fo.caso=r.caso and fo.caag=r.caag and fo.tgid=r.tgid and CASE WHEN trim(r.fousuf)='' then '001' ELSE r.fousuf END = fo.fousuf
LEFT JOIN chdev.cachan ch on trim(ch.casec)=  trim(hrsec)  
WHERE 1=1  AND r.hcdid = 1027 AND r.hclnum=1 AND hrdat>='1900-01-01' AND hrdat<='2099-12-31' AND ((hrval='O' AND hrfac = 'N') OR hrfac='O')gproc_GET_Fournisseur
 SELECT T.TGID AS sCodeFourni, Ent.ENRSOC AS sNomFourni,VALUE(LO.enlogo,'') AS sLogoFourn,Etb.ETSIRN AS sCodeSiren, A.ADLIG1 as sAdresse1, A.ADLIG2 as sAdresse2, A.ADCPST as nCodePostal , A.ADVILL as sNomVille,ctmail,fou.fousuf, T.TGTYPT,T.ETID, Etb.TYETID, TE.TYETLB, Etb.ETSIRT, Etb.ETDCOM, Etb.AdId FROM chdev.titier t  LEFT OUTER JOIN chdev.tietb Etb on Etb.etid		= T.etid  LEFT OUTER JOIN chdev.tiadr A on A.adid			= Etb.adid  LEFT OUTER JOIN chdev.tientr Ent on Ent.EnId	= Etb.EnId  LEFT OUTER JOIN chdev.tityet TE on TE.tyetid	= Etb.Tyetid  LEFT OUTER JOIN chdev.tienlo LO on lo.enid			= Ent.EnId   LEFT OUTER JOIN chdev.ticont CT on ct.tgid = t.tgid and svceid=12  LEFT OUTER JOIN chdev.tifou FOU on fou.tgid= t.tgid AND fou.caag='BM' WHERE t.tgid = 11118 FETCH FIRST 1 ROW ONLY

gproc_GET_Acheteur
SELECT 
sywus,sywuad,swnom,swpre,swmal
FROM 
chdev.sywuser
WHERE
sywus = 'CFINSB'		gproc_GET_ListeRecep
SELECT 	hrecid,
r.caso,
r.caag,
hrdat,
r.tgid,
r.fousuf,
hrlib,
r.hartid,
r.hafid,
hrbl,
hrcpt,
hrnat,
hrsec,
hrtac,
hrqte,
r.huniid,
hrpri,
hrmtt,
case when hrval='O' then 1 else 0 end,
case when hrtpr='O' then 1 else 0 end,
hrnpie,
case when hrfac='O' then 1 else 0 end,
hrhajn,
hrhanp,
hrhanl,
hrhanf,
case when hrimo='O' then 1 else 0 end,
r.hcdid,
hclnum,
hrori,
hrcomm,
r.dcrt,
r.hcrt,
r.pcrt,
r.pgcrt,
r.dmaj,
r.hmaj,
r.pmaj,
r.pgmaj,
c.hacali,
f.hafali,
s.hasfli,
t.hatyli,
VALUE(h.hafrfo,''),
fo.foulbc,
r.hrvald,
RIGHT('0'||hour(r.hrvalh),2)||right('0'||minute(r.hrvalh),2)||right('0'||second(r.hrvalh),2) as hrvalh,
r.hrvalp,
ch.cacno,
cd.hcdref,
cd.hcdnum,
a.hafaid,
fo.foucpt,
cd.hsrvid
FROM chdev.harec r
LEFT JOIN chdev.hart a 	 ON a.hartid=r.hartid
LEFT JOIN chdev.harcat c ON a.hacaid = c.hacaid
LEFT JOIN chdev.harfam f ON a.hafaid = f.hafaid
LEFT JOIN chdev.harssf s ON a.hasfid = s.hasfid
LEFT JOIN chdev.hartyp t ON a.hatyid = t.hatyid
LEFT JOIN chdev.hartfo  h ON r.hafid  = h.hafid 
LEFT JOIN chdev.hacde  cd ON r.hcdid  = cd.hcdid
LEFT JOIN chdev.tifou fo  ON fo.caso=r.caso and fo.caag=r.caag and fo.tgid=r.tgid and CASE WHEN trim(r.fousuf)='' then '001' ELSE r.fousuf END = fo.fousuf
LEFT JOIN chdev.cachan ch on trim(ch.casec)=  trim(hrsec)  
WHERE 1=1  AND r.hcdid = 1027 AND ((hrval='O' AND hrfac = 'N') OR hrfac='O')
gproc_UPD_MttsCommande

1
SELECT hrfac, SUM(hrmtt) AS MttLig,
SUM(hrqte) AS QteRecp
FROM chdev.harec
WHERE hcdid=1027 
AND   hclnum=1
AND   hrval='O'	  	   	
GROUP BY hrfac
UPDATE chdev.hacdli
SET hclqtr=1,
hclmtr=25.5,
hclqtf=0,
hclmtf=0,
dmaj=20241030,
hmaj=141443,
pmaj='DEVUSRBC',
pgmaj='HARMOV3M'
WHERE 
hcdid=1027 
AND
hclnum=1

SELECT SUM(hclmtr),SUM(hclqtf)
FROM chdev.hacdli
WHERE hcdid=1027 
UPDATE chdev.hacde
SET hcdmtr=25.5,
hcdmtf=0,
dmaj=20241030,
hmaj=141443,
pmaj='DEVUSRBC',
pgmaj='HARMOV3M'
WHERE 
hcdid=1027 gproc_UPD_Etat_Commande

UPDATE chdev.hacde
SET 
hcdsta  = 'REC',
hcdsld  = 'N',
dmaj	= 20241028	,
hmaj	= 164236, 
pmaj	= 'CFINSB',
pgmaj	= 'HARMOV3M'
WHERE
hcdid = 1027		 gproc_PUT_HistoCommande
INSERT INTO chdev.incdeh 
(caso, caag, insco, inncde, inchve, inwfty, inchac,inchda, inchua,inchdp, inwfni,dcrt,hcrt,pcrt,dmaj,hmaj,pmaj)	
VALUES 
('SD','BM','CHA',246,1,'HADEM','REC','2024-10-30 14:14:43.00000','DEVUSRBC','0001-01-01',0,20241030,141443,'DEVUSRBC',20241030,141443,'DEVUSRBC')