DBIScript =
`/* ImportEcr2012.sql : Reprendre toutes écritures 2012,
injecter dans écritures
mettre à jour références comptes DB/CR
Mettre à jour code catégorie selon Categorie de compte */
/* SQL-92 Table Creation Script with DBISAM Extensions */
DROP TABLE IF EXISTS "\Memory\EcrituresNew";
CREATE TABLE IF NOT EXISTS "\Memory\EcrituresNew"
(
"UId" AUTOINC,
"DateEcr" DATE,
"DB" VARCHAR(10),
"CR" VARCHAR(10),
"Ref" VARCHAR(10),
"Libelle" VARCHAR(100),
"MntEcr" MONEY,
PRIMARY KEY ("RecordID") COMPRESS NONE
TEXT INDEX ("Libelle")
STOP WORDS ':TEXT_INDEXPARAMS=_DBI_TIPS_FR :TEXT_INDEXRULES=CH_FR'
SPACE CHARS #1+#2+#3+#4+#5+#6+#7+#8+#9+#10+#11+#12+#13+
#14+#15+#16+#17+#18+#19+#20+#21+#22+#23+
#24+#25+#26+#27+#28+#29+#30+#31+#32+'*+'+
',-./:;<=>\`'
INCLUDE CHARS '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_ab' +
'cdefghijklmnopqrstuvwxyz€‚ƒ„…†‡ˆ‰Š‹ŒŽ'+
'‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·'+
'¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖרÙÚÛÜÝÞß'+
'àáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþ'
LOCALE CODE 4108
USER MAJOR VERSION 1
);
import table "\Memory\EcrituresNew"
from
"C:\DBI_DB\Cresus08\Ecritures12.txt"
DELIMITER #9
Columns (DateEcr,DB,CR,Ref,Libelle,MntEcr)
DATE 'dd.mm.yy'
DECIMAL '.';
DROP INDEX IF EXISTS Ecritures.ixDB;
DROP INDEX IF EXISTS Ecritures.ixCR;
DROP INDEX IF EXISTS Ecritures.ixRef;
delete FROM Ecritures
where DateEcr>='2012-01-01';
INSERT INTO
"Ecritures" (DateEcr,DB,CR,Ref,Libelle,MntEcr)
SELECT DateEcr,DB,CR,Ref,Libelle,MntEcr
FROM "\Memory\EcrituresNew";
CREATE NOCASE INDEX IF NOT EXISTS "ixDB" ON "Ecritures" ("DB");
CREATE NOCASE INDEX IF NOT EXISTS "ixCR" ON "Ecritures" ("CR");
update Ecritures E
set E.DBUId=P1.UId,
E.CRUId=P2.UId
from
Ecritures
left join Plan P1 on (E.DB=P1.CompteUnique)
left join Plan P2 on (E.CR=P2.CompteUnique);
update Ecritures E
set CatCodes=P1.CatCode+P2.CatCode
from
Ecritures
left join Plan P1 on (E.DBUId=P1.UId)
left join Plan P2 on (E.CRUId=P2.UId);
update Ecritures E
set CatCodes=P1.CatCode+P2.CatCode
from
Ecritures
left join Plan P1 on (E.DBUId=P1.UId)
left join Plan P2 on (E.CRUId=P2.UId);
update Ecritures
set Ref='0'+Ref
where length(Ref)<6 and SUBSTRING(Ref,1,1) between '1' and '9';
CREATE INDEX IF NOT EXISTS "ixRef" ON "Ecritures" ("Ref");
/* Clean up */
DROP TABLE IF EXISTS "\Memory\EcrituresNew";`;