257 lines
12 KiB
JavaScript
257 lines
12 KiB
JavaScript
$(document).ready(function () {
|
|
$("body").off("click", "#btnToArray");
|
|
$("body").off("click", "#btnConvert");
|
|
$("body").off("click", "#btnCSV");
|
|
|
|
|
|
|
|
var rows = [];
|
|
|
|
$("body").on("click", "#btnToArray", function() {
|
|
console.time("tableReadToArray");
|
|
console.log(rows.length + "Bauteile:\n" + HTMLTable2Array());
|
|
console.timeEnd("tableReadToArray");
|
|
});
|
|
|
|
$("body").on("click", "#btnConvert", function() {
|
|
allRowsConverten();
|
|
});
|
|
|
|
$("body").on("click", "#btnCSV", function() {
|
|
toCSV();
|
|
});
|
|
|
|
function HTMLTable2Array() {
|
|
$('table tr').each(function(i, n){
|
|
var $row = $(n);
|
|
rows.push({
|
|
TXLCode: $row.find('td:eq(0)').text().trim(),
|
|
OL1: $row.find('td:eq(1)').text().trim(),
|
|
OL2: $row.find('td:eq(2)').text().trim(),
|
|
OW1: $row.find('td:eq(3)').text().trim(),
|
|
OW2: $row.find('td:eq(4)').text().trim(),
|
|
R11: $row.find('td:eq(5)').text().trim(),
|
|
R12: $row.find('td:eq(6)').text().trim(),
|
|
R13: $row.find('td:eq(7)').text().trim(),
|
|
R21: $row.find('td:eq(8)').text().trim(),
|
|
R22: $row.find('td:eq(9)').text().trim(),
|
|
R23: $row.find('td:eq(10)').text().trim(),
|
|
R31: $row.find('td:eq(11)').text().trim(),
|
|
R32: $row.find('td:eq(12)').text().trim(),
|
|
R33: $row.find('td:eq(13)').text().trim(),
|
|
R41: $row.find('td:eq(14)').text().trim(),
|
|
R42: $row.find('td:eq(15)').text().trim(),
|
|
R43: $row.find('td:eq(16)').text().trim(),
|
|
LW: $row.find('td:eq(17)').text().trim(),
|
|
CW: $row.find('td:eq(18)').text().trim(),
|
|
MessDate: $row.find('td:eq(19)').text().trim(),
|
|
RauDate: $row.find('td:eq(20)').text().trim(),
|
|
InfoMessComm: $row.find('td:eq(21)').text().trim(),
|
|
InfoRauComm: $row.find('td:eq(22)').text().trim(),
|
|
Lieferbox: $row.find('td:eq(23)').text().trim()
|
|
});
|
|
});
|
|
return JSON.stringify(rows);
|
|
}
|
|
|
|
|
|
function allRowsConverten() {
|
|
var raureCnt = 0;
|
|
var rauOneCnt = 0;
|
|
var rauxlsCnt = 0;
|
|
var rauOtherCnt = 0;
|
|
|
|
var mesOtherCnt = 0;
|
|
var mesOneCnt = 0;
|
|
var mesXlsOrMM = 0;
|
|
|
|
var rauHandArr = [];
|
|
var mesHandArr =[];
|
|
|
|
var insertRec = 0;
|
|
var updateRec = 0;
|
|
var INSUP = 0;
|
|
var KEININSUP = 0;
|
|
var sql = [];
|
|
var handSql = [];
|
|
|
|
$.each(rows, function(i,e){
|
|
|
|
var mesErr = false;
|
|
var rauErr = false;
|
|
var raudate = e["RauDate"];
|
|
var messdate = e["MessDate"];
|
|
|
|
if ( raudate.startsWith("RE: ") ) {
|
|
rauTime = raudate.match(/\d{2}\:\d{2}\:\d{2}/g);
|
|
rauDate = raudate.match(/\d{2}\.\d{2}\.\d{4}/g);
|
|
e["RauDate"] = reverseDate(rauDate[0]) + " " + rauTime[0];
|
|
raureCnt++;
|
|
} else if ( raudate.match(/\b\d{2}\.\d{2}\.\d{4}/g) ) {
|
|
rauDate = raudate.match(/\d{2}\.\d{2}\.\d{4}/g);
|
|
e["RauDate"] = reverseDate(rauDate[0]) + " 23:59:59";
|
|
|
|
rauxlsCnt++;
|
|
} else if ( raudate == "1" ) {
|
|
e["RauDate"] = "1979-04-18 20:16:00";
|
|
rauOneCnt++;
|
|
} else {
|
|
rauHandArr.push({
|
|
txlcode: e["TXLCode"],
|
|
raudate: e["RauDate"]
|
|
});
|
|
rauOtherCnt++;
|
|
rauErr = true;
|
|
}
|
|
|
|
if ( Number(messdate) == 1 ) {
|
|
e["MessDate"] = "1979-04-18 20:16:00";
|
|
mesOneCnt++;
|
|
} else if ( messdate.match(/(Z:\\|Z:K|Vora|TME2).*/g) ) {
|
|
mesTime = messdate.match(/\d{2}\:\d{2}\:\d{2}/g);
|
|
mesDate = messdate.match(/\d{2}\.\d{2}\.\d{4}/g);
|
|
e["MessDate"] = reverseDate(mesDate[0]) + " " + mesTime[0];
|
|
mesXlsOrMM++;
|
|
} else {
|
|
mesHandArr.push({
|
|
txlcode: e["TXLCode"],
|
|
messdate: e["MessDate"]
|
|
});
|
|
mesOtherCnt++;
|
|
mesErr = true;
|
|
}
|
|
|
|
if ( rauErr || mesErr ) {
|
|
handSql.push(`INSERT INTO Bauteile ( TXLCode, Messdatum, Rauheitsdatum, Lieferbox, OL1, OL2, OW1, OW2, R11, R12, R13, R21, R22, R23, R31, R32, R33, R41, R42, R43, CW, LW, MessComm, RauComm ) VALUES ( "${e['TXLCode']}", "${e['MessDate']}", "${e['RauDate']}", ${e['Lieferbox']}, ${e['OL1']}, ${e['OL2']}, ${e['OW1']}, ${e['OW2']}, ${e['R11']}, ${e['R12']}, ${e['R13']}, ${e['R21']}, ${e['R22']}, ${e['R23']}, ${e['R31']}, ${e['R32']}, ${e['R33']}, ${e['R41']}, ${e['R42']}, ${e['R43']}, ${e['LW']}, ${e['CW']}, "${e['InfoMessComm']}", "${e['InfoRauComm']}") ON DUPLICATE KEY UPDATE TXLCode = "${e['TXLCode']}", Messdatum = "${e['MessDate']}", Rauheitsdatum = "${e['RauDate']}", Lieferbox = "${e['Lieferbox']}", OL1 = ${e['OL1']}, OL2 = ${e['OL2']}, OW1 = ${e['OW1']}, OW2 = ${e['OW2']}, R11 = ${e['R11']}, R12 = ${e['R12']}, R13 = ${e['R13']}, R21 = ${e['R21']}, R22 = ${e['R22']}, R23 = ${e['R23']}, R31 = ${e['R31']}, R32 = ${e['R32']}, R33 = ${e['R33']}, R41 = ${e['R41']}, R42 = ${e['R42']}, R43 = ${e['R43']}, CW = ${e['LW']}, LW = ${e['CW']}, MessComm = "${e['InfoMessComm']}", RauComm = "${e['InfoRauComm']}";`);
|
|
KEININSUP++;
|
|
} else {
|
|
sql.push(`INSERT INTO Bauteile ( TXLCode, Messdatum, Rauheitsdatum, Lieferbox, OL1, OL2, OW1, OW2, R11, R12, R13, R21, R22, R23, R31, R32, R33, R41, R42, R43, CW, LW, MessComm, RauComm ) VALUES ( "${e['TXLCode']}", "${e['MessDate']}", "${e['RauDate']}", ${e['Lieferbox']}, ${e['OL1']}, ${e['OL2']}, ${e['OW1']}, ${e['OW2']}, ${e['R11']}, ${e['R12']}, ${e['R13']}, ${e['R21']}, ${e['R22']}, ${e['R23']}, ${e['R31']}, ${e['R32']}, ${e['R33']}, ${e['R41']}, ${e['R42']}, ${e['R43']}, ${e['LW']}, ${e['CW']}, "${e['InfoMessComm']}", "${e['InfoRauComm']}") ON DUPLICATE KEY UPDATE TXLCode = "${e['TXLCode']}", Messdatum = "${e['MessDate']}", Rauheitsdatum = "${e['RauDate']}", Lieferbox = "${e['Lieferbox']}", OL1 = ${e['OL1']}, OL2 = ${e['OL2']}, OW1 = ${e['OW1']}, OW2 = ${e['OW2']}, R11 = ${e['R11']}, R12 = ${e['R12']}, R13 = ${e['R13']}, R21 = ${e['R21']}, R22 = ${e['R22']}, R23 = ${e['R23']}, R31 = ${e['R31']}, R32 = ${e['R32']}, R33 = ${e['R33']}, R41 = ${e['R41']}, R42 = ${e['R42']}, R43 = ${e['R43']}, CW = ${e['LW']}, LW = ${e['CW']}, MessComm = "${e['InfoMessComm']}", RauComm = "${e['InfoRauComm']}";`);
|
|
INSUP++;
|
|
}
|
|
});
|
|
|
|
var sfsd = sql.join("\n");
|
|
window.open(encodeURI("data:text/csv;charset=utf-8," + sfsd));
|
|
|
|
console.log("Upd: " + INSUP);
|
|
console.log("Err: " + KEININSUP);
|
|
// console.log(JSON.stringify(rauHandArr));
|
|
// console.log(JSON.stringify(mesHandArr));
|
|
|
|
$('#jsonDiv').text(JSON.stringify(sql));
|
|
}
|
|
|
|
function toCSV() {
|
|
var raureCnt = 0;
|
|
var rauOneCnt = 0;
|
|
var rauxlsCnt = 0;
|
|
var rauOtherCnt = 0;
|
|
|
|
var mesOtherCnt = 0;
|
|
var mesOneCnt = 0;
|
|
var mesXlsOrMM = 0;
|
|
|
|
var rauHandArr = [];
|
|
var mesHandArr =[];
|
|
|
|
var insertRec = 0;
|
|
var updateRec = 0;
|
|
var INSUP = 0;
|
|
var KEININSUP = 0;
|
|
var csv = [];
|
|
var handCsv = [];
|
|
|
|
$.each(rows, function(i,e){
|
|
|
|
var mesErr = false;
|
|
var rauErr = false;
|
|
var raudate = e["RauDate"];
|
|
var messdate = e["MessDate"];
|
|
|
|
if ( raudate.startsWith("RE: ") ) {
|
|
rauTime = raudate.match(/\d{2}\:\d{2}\:\d{2}/g);
|
|
rauDate = raudate.match(/\d{2}\.\d{2}\.\d{4}/g);
|
|
e["RauDate"] = reverseDate(rauDate[0]) + " " + rauTime[0];
|
|
raureCnt++;
|
|
} else if ( raudate.match(/\b\d{2}\.\d{2}\.\d{4}/g) ) {
|
|
rauDate = raudate.match(/\d{2}\.\d{2}\.\d{4}/g);
|
|
e["RauDate"] = reverseDate(rauDate[0]) + " 23:59:59";
|
|
|
|
rauxlsCnt++;
|
|
} else if ( raudate == "1" ) {
|
|
e["RauDate"] = "1979-04-18 20:16:00";
|
|
rauOneCnt++;
|
|
} else {
|
|
rauHandArr.push({
|
|
txlcode: e["TXLCode"],
|
|
raudate: e["RauDate"]
|
|
});
|
|
rauOtherCnt++;
|
|
rauErr = true;
|
|
}
|
|
|
|
if ( Number(messdate) == 1 ) {
|
|
e["MessDate"] = "1979-04-18 20:16:00";
|
|
mesOneCnt++;
|
|
} else if ( messdate.match(/(Z:\\|Z:K|Vora|TME2).*/g) ) {
|
|
mesTime = messdate.match(/\d{2}\:\d{2}\:\d{2}/g);
|
|
mesDate = messdate.match(/\d{2}\.\d{2}\.\d{4}/g);
|
|
e["MessDate"] = reverseDate(mesDate[0]) + " " + mesTime[0];
|
|
mesXlsOrMM++;
|
|
} else {
|
|
mesHandArr.push({
|
|
txlcode: e["TXLCode"],
|
|
messdate: e["MessDate"]
|
|
});
|
|
mesOtherCnt++;
|
|
mesErr = true;
|
|
}
|
|
|
|
if ( rauErr || mesErr ) {
|
|
//handSql.push(`INSERT INTO Bauteile ( TXLCode, Messdatum, Rauheitsdatum, Lieferbox, OL1, OL2, OW1, OW2, R11, R12, R13, R21, R22, R23, R31, R32, R33, R41, R42, R43, CW, LW, MessComm, RauComm ) VALUES ( "${e['TXLCode']}", "${e['MessDate']}", "${e['RauDate']}", "${e['Lieferbox']}", ${e['OL1']}, ${e['OL2']}, ${e['OW1']}, ${e['OW2']}, ${e['R11']}, ${e['R12']}, ${e['R13']}, ${e['R21']}, ${e['R22']}, ${e['R23']}, ${e['R31']}, ${e['R32']}, ${e['R33']}, ${e['R41']}, ${e['R42']}, ${e['R43']}, ${e['LW']}, ${e['CW']}, "${e['InfoMessComm']}", "${e['InfoRauComm']}") ON DUPLICATE KEY UPDATE TXLCode = "${e['TXLCode']}", Messdatum = "${e['MessDate']}", Rauheitsdatum = "${e['RauDate"']}", Lieferbox = "${e['Lieferbox']}", OL1 = ${e['OL1']}, OL2 = ${e['OL2']}, OW1 = ${e['OW1']}, OW2 = ${e['OW2']}, R11 = ${e['R11']}, R12 = ${e['R12']}, R13 = ${e['R13']}, R21 = ${e['R21']}, R22 = ${e['R22']}, R23 = ${e['R23']}, R31 = ${e['R31']}, R32 = ${e['R32']}, R33 = ${e['R33']}, R41 = ${e['R41']}, R42 = ${e['R42']}, R43 = ${e['R43']}, CW = ${e['LW']}, LW = ${e['CW']}, MessComm = "${e['InfoMessComm']}", RauComm = "${e['InfoRauComm']}";`);
|
|
handCsv.push(`"${e['TXLCode']}"; "${e['MessDate']}"; "${e['RauDate']}"; "${e['Lieferbox']}"; ${e['OL1']}; ${e['OL2']}; ${e['OW1']}; ${e['OW2']}; ${e['R11']}; ${e['R12']}; ${e['R13']}; ${e['R21']}; ${e['R22']}; ${e['R23']}; ${e['R31']}; ${e['R32']}; ${e['R33']}; ${e['R41']}; ${e['R42']}; ${e['R43']}; ${e['LW']}; ${e['CW']}; "${e['InfoMessComm']}"; "${e['InfoRauComm']}"`);
|
|
KEININSUP++;
|
|
} else {
|
|
//sql.push(`INSERT INTO Bauteile ( TXLCode, Messdatum, Rauheitsdatum, Lieferbox, OL1, OL2, OW1, OW2, R11, R12, R13, R21, R22, R23, R31, R32, R33, R41, R42, R43, CW, LW, MessComm, RauComm ) VALUES ( "${e['TXLCode']}", "${e['MessDate']}", "${e['RauDate']}", "${e['Lieferbox']}", ${e['OL1']}, ${e['OL2']}, ${e['OW1']}, ${e['OW2']}, ${e['R11']}, ${e['R12']}, ${e['R13']}, ${e['R21']}, ${e['R22']}, ${e['R23']}, ${e['R31']}, ${e['R32']}, ${e['R33']}, ${e['R41']}, ${e['R42']}, ${e['R43']}, ${e['LW']}, ${e['CW']}, "${e['InfoMessComm']}", "${e['InfoRauComm']}") ON DUPLICATE KEY UPDATE TXLCode = "${e['TXLCode']}", Messdatum = "${e['MessDate']}", Rauheitsdatum = "${e['RauDate"']}", Lieferbox = "${e['Lieferbox']}", OL1 = ${e['OL1']}, OL2 = ${e['OL2']}, OW1 = ${e['OW1']}, OW2 = ${e['OW2']}, R11 = ${e['R11']}, R12 = ${e['R12']}, R13 = ${e['R13']}, R21 = ${e['R21']}, R22 = ${e['R22']}, R23 = ${e['R23']}, R31 = ${e['R31']}, R32 = ${e['R32']}, R33 = ${e['R33']}, R41 = ${e['R41']}, R42 = ${e['R42']}, R43 = ${e['R43']}, CW = ${e['LW']}, LW = ${e['CW']}, MessComm = "${e['InfoMessComm']}", RauComm = "${e['InfoRauComm']}";`);
|
|
csv.push(`"${e['TXLCode']}"; "${e['MessDate']}"; "${e['RauDate']}"; "${e['Lieferbox']}"; ${e['OL1']}; ${e['OL2']}; ${e['OW1']}; ${e['OW2']}; ${e['R11']}; ${e['R12']}; ${e['R13']}; ${e['R21']}; ${e['R22']}; ${e['R23']}; ${e['R31']}; ${e['R32']}; ${e['R33']}; ${e['R41']}; ${e['R42']}; ${e['R43']}; ${e['LW']}; ${e['CW']}; "${e['InfoMessComm']}"; "${e['InfoRauComm']}"`);
|
|
INSUP++;
|
|
}
|
|
});
|
|
|
|
var sfsd = csv.join("\n");
|
|
window.open(encodeURI("data:text/csv;charset=utf-8," + sfsd));
|
|
|
|
console.log("Upd: " + INSUP);
|
|
console.log("Err: " + KEININSUP);
|
|
}
|
|
|
|
|
|
|
|
function getBauteilStatusNew(txlcode) {
|
|
|
|
myJSON = { func: "getBauteilStatusNew", TXLCode: txlcode };
|
|
myRowID = $.ajax({
|
|
url: "DBSachen.php",
|
|
data: { json: JSON.stringify(myJSON) },
|
|
async: false,
|
|
method: "POST"
|
|
});
|
|
return myRowID.responseText;
|
|
}
|
|
|
|
|
|
// http://stackoverflow.com/a/37151380/5690568
|
|
function reverseDate(str) {
|
|
|
|
var strArray = str.split(".");
|
|
strArray.reverse();
|
|
|
|
var strReverse = strArray.join("-");
|
|
|
|
return strReverse;
|
|
}
|
|
|
|
|
|
|
|
});
|