1<?php
2include_once('../adodb.inc.php');
3include_once('adodb-replicate.inc.php');
4
5set_time_limit(0);
6
7function IndexFilter($dtable, $idxname,$flds,$options)
8{
9	if (strlen($idxname) > 28) $idxname = substr($idxname,0,24).rand(1000,9999);
10	return $idxname;
11}
12
13function SelFilter($table, &$arr, $delfirst)
14{
15	return true;
16}
17
18function updatefilter($table, $fld, $val)
19{
20	return "nvl($fld, $val)";
21}
22
23
24function FieldFilter(&$fld,$mode)
25{
26	$uf = strtoupper($fld);
27	switch($uf) {
28		case 'SIZEFLD':
29			return 'Size';
30
31		case 'GROUPFLD':
32			return 'Group';
33
34		case 'GROUP':
35			if ($mode == 'SELECT') $fld = '"Group"';
36			return 'GroupFld';
37		case 'SIZE':
38			if ($mode == 'SELECT') $fld = '"Size"';
39			return 'SizeFld';
40	}
41	return $fld;
42}
43
44function ParseTable(&$table, &$pkey)
45{
46	$table = trim($table);
47	if (strlen($table) == 0) return false;
48	if (strpos($table, '#') !== false) {
49		$at = strpos($table, '#');
50		$table = trim(substr($table,0,$at));
51		if (strlen($table) == 0) return false;
52	}
53
54	$tabarr = explode(',',$table);
55	if (sizeof($tabarr) == 1) {
56		$table = $tabarr[0];
57		$pkey = '';
58		echo "No primary key for $table ****  **** <br>";
59	} else {
60		$table = trim($tabarr[0]);
61		$pkey = trim($tabarr[1]);
62		if (strpos($pkey,' ') !== false) echo "Bad PKEY for $table $pkey<br>";
63	}
64
65	return true;
66}
67
68global $TARR;
69
70function TableStats($rep, $table, $pkey)
71{
72global $TARR;
73
74	if (empty($TARR)) $TARR = array();
75	$cnt = $rep->connSrc->GetOne("select count(*) from $table");
76	if (isset($TARR[$table])) echo "<h1>Table $table repeated twice</h1>";
77	$TARR[$table] = $cnt;
78
79	if ($pkey) {
80		$ok = $rep->connSrc->SelectLimit("select $pkey from $table",1);
81		if (!$ok) echo "<h1>$table: $pkey does not exist</h1>";
82	} else
83		echo "<h1>$table: no primary key</h1>";
84}
85
86function CreateTable($rep, $table)
87{
88## CREATE TABLE
89	#$DB2->Execute("drop table $table");
90
91	$rep->execute = true;
92	$ok = $rep->CopyTableStruct($table);
93	if ($ok) echo "Table Created<br>\n";
94	else {
95		echo "<hr>Error: Cannot Create Table<hr>\n";
96	}
97	flush();@ob_flush();
98}
99
100function CopyData($rep, $table, $pkey)
101{
102	$dtable = $table;
103
104	$rep->execute = true;
105	$rep->deleteFirst = true;
106
107	$secs = time();
108	$rows = $rep->ReplicateData($table,$dtable,array($pkey));
109	$secs = time() - $secs;
110	if (!$rows || !$rows[0] || !$rows[1] || $rows[1] != $rows[2]+$rows[3]) {
111		echo "<hr>Error: "; var_dump($rows);  echo " (secs=$secs) <hr>\n";
112	} else
113		echo date('H:i:s'),': ',$rows[1]," record(s) copied, ",$rows[2]," inserted, ",$rows[3]," updated (secs=$secs)<br>\n";
114	flush();@ob_flush();
115}
116
117function MergeDataJohnTest($rep, $table, $pkey)
118{
119	$rep->SwapDBs();
120
121	$dtable = $table;
122	$rep->oracleSequence = 'LGBSEQUENCE';
123
124#	$rep->MergeSrcSetup($table, array($pkey),'UpdatedOn','CopiedFlag');
125	if (strpos($rep->connDest->databaseType,'mssql') !== false)  {  # oracle ==> mssql
126		$ignoreflds = array($pkey);
127		$ignoreflds[] = 'MSSQL_ID';
128		$set = 'MSSQL_ID=nvl($INSERT_ID,MSSQL_ID)';
129		$pkeyarr = array(array($pkey),false,array('MSSQL_ID'));# array('MSSQL_ID', 'ORA_ID'));
130	} else {  # mssql ==> oracle
131		$ignoreflds = array($pkey);
132		$ignoreflds[] = 'ORA_ID';
133		$set = '';
134		#$set = 'ORA_ID=isnull($INSERT_ID,ORA_ID)';
135		$pkeyarr = array(array($pkey),array('MSSQL_ID'));
136	}
137	$rep->execute = true;
138	#$rep->updateFirst = false;
139	$ok = $rep->Merge($table, $dtable, $pkeyarr, $ignoreflds, $set, 'UpdatedOn','CopiedFlag',array('Y','N','P','='), 'CopyDate');
140	var_dump($ok);
141
142	#$rep->connSrc->Execute("update JohnTest set name='Apple' where id=4");
143}
144
145$DB = ADONewConnection('odbtp');
146#$ok = $DB->Connect('localhost','root','','northwind');
147$ok = $DB->Connect('192.168.0.1','DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=natsoft;DATABASE=OIR;','','');
148$DB->_bindInputArray = false;
149
150$DB2 = ADONewConnection('oci8');
151$ok2 = $DB2->Connect('192.168.0.2','tnb','natsoft','RAPTOR','');
152
153if (!$ok || !$ok2) die("Failed connection DB=$ok DB2=$ok2<br>");
154
155$tables =
156"
157JohnTest,id
158";
159
160# net* are ERMS, need last updated field from LGBnet
161# tblRep* are tables insert or update from Juris, need last updated field also
162# The rest are lookup tables, can copy all from LGBnet
163
164$tablesOrig =
165"
166SysVoltSubLevel,id
167# Lookup table for Restoration Details screen
168sysefi,ID # (not identity)
169sysgenkva,ID #(not identity)
170sysrestoredby,ID  #(not identity)
171# Sel* table added on 24 Oct
172SELSGManufacturer,ID
173SelABCCondSizeLV,ID
174SelABCCondSizeMV,ID
175SelArchingHornSize,ID
176SelBallastSize,ID
177SelBallastType,ID
178SelBatteryType,ID #(not identity)
179SelBreakerCapacity,ID
180SelBreakerType,ID #(not identity)
181SelCBreakerManuf,ID
182SelCTRatio,ID #(not identity)
183SelCableBrand,ID
184SelCableSize,ID
185SelCableSizeLV,ID # (not identity)
186SelCapacitorSize,ID
187SelCapacitorType,ID
188SelColourCode,ID
189SelCombineSealingChamberSize,ID
190SelConductorBrand,ID
191SelConductorSize4,ID
192SelConductorSizeLV,ID
193SelConductorSizeMV,ID
194SelContactorSize,ID
195SelContractor,ID
196SelCoverType,ID
197SelCraddleSize,ID
198SelDeadEndClampBrand,ID
199SelDeadEndClampSize,ID
200SelDevTermination,ID
201SelFPManuf,ID
202SelFPillarRating,ID
203SelFalseTrue,ID
204SelFuseManuf,ID
205SelFuseType,ID
206SelIPCBrand,ID
207SelIPCSize,ID
208SelIgnitorSize,ID
209SelIgnitorType,ID
210SelInsulatorBrand,ID
211SelJoint,ID
212SelJointBrand,ID
213SelJunctionBoxBrand,ID
214SelLVBoardBrand,ID
215SelLVBoardSize,ID
216SelLVOHManuf,ID
217SelLVVoltage,ID
218SelLightningArresterBrand,ID
219SelLightningShieldwireSize,ID
220SelLineTapSize,ID
221SelLocation,ID
222SelMVVoltage,ID
223SelMidSpanConnectorsSize,ID
224SelMidSpanJointSize,ID
225SelNERManuf,ID
226SelNERType,ID
227SelNLinkSize,ID
228SelPVCCondSizeLV,ID
229SelPoleBrand,ID
230SelPoleConcreteSize,ID
231SelPoleSize,ID
232SelPoleSpunConcreteSize,ID
233SelPoleSteelSize,ID
234SelPoleType,ID
235SelPoleWoodSize,ID
236SelPorcelainFuseSize,ID
237SelRatedFaultCurrentBreaker,ID
238SelRatedVoltageSG,ID #(not identity)
239SelRelayType,ID # (not identity)
240SelResistanceValue,ID
241SelSGEquipmentType,ID  # (not identity)
242SelSGInsulationType,ID # (not identity)
243SelSGManufacturer,ID
244SelStayInsulatorSize,ID
245SelSuspensionClampBrand,ID
246SelSuspensionClampSize,ID
247SelTSwitchType,ID
248SelTowerType,ID
249SelTransformerCapacity,ID
250SelTransformerManuf,ID
251SelTransformerType,ID #(not identity)
252SelTypeOfArchingHorn,ID
253SelTypeOfCable,ID     #(not identity)
254SelTypeOfConductor,ID # (not identity)
255SelTypeOfInsulationCB,ID # (not identity)
256SelTypeOfMidSpanJoint,ID
257SelTypeOfSTJoint,ID
258SelTypeSTCable,ID
259SelUGVoltage,ID # (not identity)
260SelVoltageInOut,ID
261SelWireSize,ID
262SelWireType,ID
263SelWonpieceBrand,ID
264#
265# Net* tables added on 24 Oct
266NetArchingHorn,Idx
267NetBatteryBank,Idx # identity, FunctLocation Pri
268NetBiMetal,Idx
269NetBoxFuse,Idx
270NetCable,Idx # identity, FunctLocation Pri
271NetCapacitorBank,Idx # identity, FunctLocation Pri
272NetCircuitBreaker,Idx # identity, FunctLocation Pri
273NetCombineSealingChamber,Idx
274NetCommunication,Idx
275NetCompInfras,Idx
276NetControl,Idx
277NetCraddle,Idx
278NetDeadEndClamp,Idx
279NetEarthing,Idx
280NetFaultIndicator,Idx
281NetFeederPillar,Idx # identity, FunctLocation Pri
282NetGenCable,Idx # identity , FunctLocation Not Null
283NetGenerator,Idx
284NetGrid,Idx
285NetHVOverhead,Idx #identity, FunctLocation Pri
286NetHVUnderground,Idx #identity, FunctLocation Pri
287NetIPC,Idx
288NetInductorBank,Idx
289NetInsulator,Idx
290NetJoint,Idx
291NetJunctionBox,Idx
292NetLVDB,Idx #identity, FunctLocation Pri
293NetLVOverhead,Idx
294NetLVUnderground,Idx # identity, FunctLocation Not Null
295NetLightningArrester,Idx
296NetLineTap,Idx
297NetMidSpanConnectors,Idx
298NetMidSpanJoint,Idx
299NetNER,Idx # identity , FunctLocation Pri
300NetOilPump,Idx
301NetOtherComponent,Idx
302NetPole,Idx
303NetRMU,Idx # identity, FunctLocation Pri
304NetStreetLight,Idx
305NetStrucSupp,Idx
306NetSuspensionClamp,Idx
307NetSwitchGear,Idx # identity, FunctLocation Pri
308NetTermination,Idx
309NetTransition,Idx
310NetWonpiece,Idx
311#
312# comment1
313SelMVFuseType,ID
314selFuseSize,ID
315netRelay,Idx # identity, FunctLocation Pri
316SysListVolt,ID
317sysVoltLevel,ID_SVL
318sysRestoration,ID_SRE
319sysRepairMethod,ID_SRM # (not identity)
320
321sysInterruptionType,ID_SIN
322netTransformer,Idx # identity, FunctLocation Pri
323#
324#
325sysComponent,ID_SC
326sysCodecibs #-- no idea, UpdatedOn(the only column is unique),Ermscode,Cibscode is unique but got null value
327sysCodeno,id
328sysProtection,ID_SP
329sysEquipment,ID_SEQ
330sysAddress #-- no idea, ID_SAD(might be auto gen No)
331sysWeather,ID_SW
332sysEnvironment,ID_SE
333sysPhase,ID_SPH
334sysFailureCause,ID_SFC
335sysFailureMode,ID_SFM
336SysSchOutageMode,ID_SSM
337SysOutageType,ID_SOT
338SysInstallation,ID_SI
339SysInstallationCat,ID_SIC
340SysInstallationType,ID_SIT
341SysFaultCategory,ID_SF #(not identity)
342SysResponsible,ID_SR
343SysProtectionOperation,ID_SPO #(not identity)
344netCodename,CodeNo #(not identity)
345netSubstation,Idx #identity, FunctLocation Pri
346netLvFeeder,Idx # identity, FunctLocation Pri
347#
348#
349tblReport,ReportNo
350tblRepRestoration,ID_RR
351tblRepResdetail,ID_RRD
352tblRepFailureMode,ID_RFM
353tblRepFailureCause,ID_RFC
354tblRepRepairMethod,ReportNo # (not identity)
355tblInterruptionType,ID_TIN
356tblProtType,ID_PT #--capital letter
357tblRepProtection,ID_RP
358tblRepComponent,ID_RC
359tblRepWeather,ID_RW
360tblRepEnvironment,ID_RE
361tblRepSubstation,ID_RSS
362tblInstallationType,ID_TIT
363tblInstallationCat,ID_TIC
364tblFailureCause,ID_TFC
365tblFailureMode,ID_TFM
366tblProtection,ID_TP
367tblComponent,ID_TC
368tblProtdetail,Id # (Id)--capital letter for I
369tblInstallation,ID_TI
370#
371";
372
373
374$tables = explode("\n",$tables);
375
376$rep = new ADODB_Replicate($DB,$DB2);
377$rep->fieldFilter = 'FieldFilter';
378$rep->selFilter = 'SELFILTER';
379$rep->indexFilter = 'IndexFilter';
380
381if (1) {
382	$rep->debug = 1;
383	$DB->debug=1;
384	$DB2->debug=1;
385}
386
387#	$rep->SwapDBs();
388
389$cnt = sizeof($tables);
390foreach($tables as $k => $table) {
391	$pkey = '';
392	if (!ParseTable($table, $pkey)) continue;
393
394	#######################
395
396	$kcnt = $k+1;
397	echo "<h1>($kcnt/$cnt) $table -- $pkey</h1>\n";
398	flush();@ob_flush();
399
400	CreateTable($rep,$table);
401
402
403	# COPY DATA
404
405
406	TableStats($rep, $table, $pkey);
407
408	if ($table == 'JohnTest') MergeDataJohnTest($rep, $table, $pkey);
409	else CopyData($rep, $table, $pkey);
410
411}
412
413
414if (!empty($TARR)) {
415	ksort($TARR);
416	adodb_pr($TARR);
417	asort($TARR);
418	adodb_pr($TARR);
419}
420
421echo "<hr>",date('H:i:s'),": Done</hr>";
422