· 6 years ago · Oct 31, 2019, 12:28 PM
1<?php
2$zeroNumbers = array();
3
4if (isset($_POST['insertData'])) {
5 $sql = "SELECT Total, Comments,AccountNumber, Receipt FROM transactions";
6 $result = $conn->query($sql);
7
8 $val_arry = array();
9 if ($result->num_rows > 0) {
10 while($row = $result->fetch_assoc()) {
11 if ($row['AccountNumber'] == 0) {
12 array_push($zeroNumbers, $row);
13 continue;
14 }
15 }
16 }
17
18 if (!$zeroNumbers) {
19 $intisql2 = "DROP TABLE IF EXISTS `journalentry`";
20 $conn->query($intisql2);
21
22 $createTable2 = "CREATE TABLE IF NOT EXISTS `journalentry` (
23 `TxnID` varchar(36) NOT NULL,
24 `TimeCreated` datetime DEFAULT NULL,
25 `TimeModified` datetime DEFAULT NULL,
26 `EditSequence` varchar(16) DEFAULT NULL,
27 `TxnNumber` int(11) DEFAULT NULL,
28 `TxnDate` datetime DEFAULT NULL,
29 `RefNumber` varchar(25) DEFAULT NULL,
30 `Memo` varchar(1000) DEFAULT NULL,
31 `IsAdjustment` varchar(5) DEFAULT NULL,
32 `IsHomeCurrencyAdjustment` varchar(5) DEFAULT NULL,
33 `IsAmountsEnteredInHomeCurrency` varchar(5) DEFAULT NULL,
34 `CurrencyRef_ListID` varchar(36) DEFAULT NULL,
35 `CurrencyRef_FullName` varchar(64) DEFAULT NULL,
36 `ExchangeRate` decimal(16,6) DEFAULT NULL,
37 `UserData` varchar(255) DEFAULT NULL,
38 `CustomField1` varchar(50) DEFAULT NULL,
39 `CustomField2` varchar(50) DEFAULT NULL,
40 `CustomField3` varchar(50) DEFAULT NULL,
41 `CustomField4` varchar(50) DEFAULT NULL,
42 `CustomField5` varchar(50) DEFAULT NULL,
43 `CustomField6` varchar(50) DEFAULT NULL,
44 `CustomField7` varchar(50) DEFAULT NULL,
45 `CustomField8` varchar(50) DEFAULT NULL,
46 `CustomField9` varchar(50) DEFAULT NULL,
47 `CustomField10` varchar(50) DEFAULT NULL,
48 `CustomField11` varchar(50) DEFAULT NULL,
49 `CustomField12` varchar(50) DEFAULT NULL,
50 `CustomField13` varchar(50) DEFAULT NULL,
51 `CustomField14` varchar(50) DEFAULT NULL,
52 `CustomField15` varchar(50) DEFAULT NULL,
53 `Status` varchar(10) DEFAULT NULL,
54 `Name` varchar(31) DEFAULT NULL,
55 `TxnType` varchar(200) DEFAULT NULL,
56 `ExternalGUID` varchar(40) DEFAULT NULL,
57 PRIMARY KEY (`TxnID`),
58 UNIQUE KEY `journalentryIdIndex` (`TxnID`)
59 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
60
61 $conn->query($createTable2);
62
63 $rnd1 = rand(10, 99);
64 $rnd2 = rand();
65 $TxnID = $rnd1.'-'.$rnd2;
66
67 /* $history = $conn->query("SELECT DISTINCT(created) AS created FROM transaction_history ORDER BY created DESC LIMIT 0,1");
68 while($row = $history->fetch_assoc()) {
69 $created = $row['created'];
70 } */
71
72 /* $conn->query("UPDATE transaction_history SET TXNID = '$TxnID' WHERE created = '$created'"); */
73
74 $intisql1 = "DROP TABLE IF EXISTS `journaldebitlinedetail`";
75 $conn->query($intisql1);
76
77 $createTable = "CREATE TABLE IF NOT EXISTS `journaldebitlinedetail` (
78 `TxnLineID` varchar(36) DEFAULT NULL,
79 `AccountRef_ListID` varchar(36) DEFAULT NULL,
80 `AccountRef_FullName` varchar(159) DEFAULT NULL,
81 `Amount` decimal(16,6) DEFAULT NULL,
82 `TaxAmount` decimal(16,6) DEFAULT NULL,
83 `Memo` varchar(1000) DEFAULT NULL,
84 `EntityRef_ListID` varchar(36) DEFAULT NULL,
85 `EntityRef_FullName` varchar(209) DEFAULT NULL,
86 `ClassRef_ListID` varchar(36) DEFAULT NULL,
87 `ClassRef_FullName` varchar(159) DEFAULT NULL,
88 `ItemSalesTaxRef_ListID` varchar(36) DEFAULT NULL,
89 `ItemSalesTaxRef_FullName` varchar(159) DEFAULT NULL,
90 `BillableStatus` varchar(255) DEFAULT NULL,
91 `CustomField1` varchar(50) DEFAULT NULL,
92 `CustomField2` varchar(50) DEFAULT NULL,
93 `CustomField3` varchar(50) DEFAULT NULL,
94 `CustomField4` varchar(50) DEFAULT NULL,
95 `CustomField5` varchar(50) DEFAULT NULL,
96 `CustomField6` varchar(50) DEFAULT NULL,
97 `CustomField7` varchar(50) DEFAULT NULL,
98 `CustomField8` varchar(50) DEFAULT NULL,
99 `CustomField9` varchar(50) DEFAULT NULL,
100 `CustomField10` varchar(50) DEFAULT NULL,
101 `CustomField11` varchar(50) DEFAULT NULL,
102 `CustomField12` varchar(50) DEFAULT NULL,
103 `CustomField13` varchar(50) DEFAULT NULL,
104 `CustomField14` varchar(50) DEFAULT NULL,
105 `CustomField15` varchar(50) DEFAULT NULL,
106 `IDKEY` varchar(255) DEFAULT NULL,
107 `GroupIDKEY` varchar(255) DEFAULT NULL,
108 `SeqNum` int(11) DEFAULT NULL,
109 KEY `journaldebitlinedetailIdIndex` (`IDKEY`)
110 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
111
112 $conn->query($createTable);
113
114 $sql = "SELECT Total, Comments,AccountNumber, Receipt, Date FROM transactions";
115 $result = $conn->query($sql);
116 $maxDate = 0;
117
118 $val_arry = array();
119 if ($result->num_rows > 0) {
120 while($row = $result->fetch_assoc()) {
121 if (strtotime($row['Date']) > $maxDate) {
122 $maxDate = strtotime($row['Date']);
123 }
124
125 $getAccount = "SELECT `ListID` FROM `account` where `AccountNumber` = ".abs($row['AccountNumber']);
126 $result2 = $conn->query($getAccount);
127 $row2 = $result2->fetch_assoc();
128
129 if ($result2->num_rows > 0) {
130
131 $sqlInsert = "INSERT into journaldebitlinedetail (TxnLineID, AccountRef_ListID, AccountRef_FullName, Amount, TaxAmount, Memo, EntityRef_ListID, EntityRef_FullName, ClassRef_ListID, ClassRef_FullName, ItemSalesTaxRef_ListID, ItemSalesTaxRef_FullName, BillableStatus, CustomField1, CustomField2, CustomField3, CustomField4, CustomField5, CustomField6, CustomField7, CustomField8, CustomField9, CustomField10, CustomField11, CustomField12, CustomField13, CustomField14, CustomField15, IDKEY, GroupIDKEY, SeqNum)
132 values (
133 NULL, '".$row2['ListID']."', NULL, '". abs($row['Total']) ."', NULL, '". $row['Comments'] . ' - POS RECIPET #' . $row['Receipt'] ."', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '".$TxnID."', NULL, NULL
134 )";
135 $conn->query($sqlInsert);
136 } else{
137
138 $sqlInsert = "INSERT into journaldebitlinedetail (TxnLineID, AccountRef_ListID, AccountRef_FullName, Amount, TaxAmount, Memo, EntityRef_ListID, EntityRef_FullName, ClassRef_ListID, ClassRef_FullName, ItemSalesTaxRef_ListID, ItemSalesTaxRef_FullName, BillableStatus, CustomField1, CustomField2, CustomField3, CustomField4, CustomField5, CustomField6, CustomField7, CustomField8, CustomField9, CustomField10, CustomField11, CustomField12, CustomField13, CustomField14, CustomField15, IDKEY, GroupIDKEY, SeqNum)
139 values (
140 NULL, NULL, NULL, '". abs($row['Total']) ."', NULL, '". $row['Comments'] . ' - POS RECIPET #' . $row['Receipt'] ."', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '".$TxnID."', NULL, NULL
141 )";
142 $conn->query($sqlInsert);
143 }
144
145 /* $sqlInsert = "INSERT into journaldebitlinedetail (TxnLineID, AccountRef_ListID, AccountRef_FullName, Amount, TaxAmount, Memo, EntityRef_ListID, EntityRef_FullName, ClassRef_ListID, ClassRef_FullName, ItemSalesTaxRef_ListID, ItemSalesTaxRef_FullName, BillableStatus, CustomField1, CustomField2, CustomField3, CustomField4, CustomField5, CustomField6, CustomField7, CustomField8, CustomField9, CustomField10, CustomField11, CustomField12, CustomField13, CustomField14, CustomField15, IDKEY, GroupIDKEY, SeqNum)
146 values (
147 NULL, '8000001E-1571435203', 'cash draw', '". abs($row['Total']) ."', NULL, '". $row['Comments'] ."', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '".$TxnID."', NULL, NULL
148 )";
149 $conn->query($sqlInsert);
150 */
151 }
152 }
153
154 $sqlInsert2 = "INSERT into `journalentry` (`TxnID`,`TimeCreated`,`TimeModified`,`EditSequence`,`TxnNumber`,`TxnDate`,`RefNumber`,`Memo`,`IsAdjustment`,`IsHomeCurrencyAdjustment`,`IsAmountsEnteredInHomeCurrency`,`CurrencyRef_ListID`,`CurrencyRef_FullName`,`ExchangeRate`,`UserData`,`CustomField1`,`CustomField2`,`CustomField3`,`CustomField4`,`CustomField5`,`CustomField6`,`CustomField7`,`CustomField8`,`CustomField9`,`CustomField10`,`CustomField11`,`CustomField12`,`CustomField13`,`CustomField14`,`CustomField15`,`Status`,`Name`,`TxnType`,`ExternalGUID`)
155 VALUES ('". $TxnID ."',NULL,NULL,NULL,NULL,'" . date('Y-m-t H:i:s', $maxDate) . "',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 'ADD',NULL,NULL,NULL)";
156 $conn->query($sqlInsert2);
157
158 $intisql3 = "DROP TABLE IF EXISTS `journalcreditlinedetail`";
159 $conn->query($intisql3);
160
161 $createTable3 = "CREATE TABLE `journalcreditlinedetail` (
162 `TxnLineID` varchar(36) DEFAULT NULL,
163 `AccountRef_ListID` varchar(36) DEFAULT NULL,
164 `AccountRef_FullName` varchar(159) DEFAULT NULL,
165 `Amount` decimal(16,6) DEFAULT NULL,
166 `TaxAmount` decimal(16,6) DEFAULT NULL,
167 `Memo` varchar(1000) DEFAULT NULL,
168 `IsAdjustment` varchar(5) DEFAULT NULL,
169 `EntityRef_ListID` varchar(36) DEFAULT NULL,
170 `EntityRef_FullName` varchar(209) DEFAULT NULL,
171 `ClassRef_ListID` varchar(36) DEFAULT NULL,
172 `ClassRef_FullName` varchar(159) DEFAULT NULL,
173 `ItemSalesTaxRef_ListID` varchar(36) DEFAULT NULL,
174 `ItemSalesTaxRef_FullName` varchar(159) DEFAULT NULL,
175 `BillableStatus` varchar(255) DEFAULT NULL,
176 `CustomField1` varchar(50) DEFAULT NULL,
177 `CustomField2` varchar(50) DEFAULT NULL,
178 `CustomField3` varchar(50) DEFAULT NULL,
179 `CustomField4` varchar(50) DEFAULT NULL,
180 `CustomField5` varchar(50) DEFAULT NULL,
181 `CustomField6` varchar(50) DEFAULT NULL,
182 `CustomField7` varchar(50) DEFAULT NULL,
183 `CustomField8` varchar(50) DEFAULT NULL,
184 `CustomField9` varchar(50) DEFAULT NULL,
185 `CustomField10` varchar(50) DEFAULT NULL,
186 `CustomField11` varchar(50) DEFAULT NULL,
187 `CustomField12` varchar(50) DEFAULT NULL,
188 `CustomField13` varchar(50) DEFAULT NULL,
189 `CustomField14` varchar(50) DEFAULT NULL,
190 `CustomField15` varchar(50) DEFAULT NULL,
191 `IDKEY` varchar(255) DEFAULT NULL,
192 `GroupIDKEY` varchar(255) DEFAULT NULL,
193 `SeqNum` int(11) DEFAULT NULL,
194 KEY `journalcreditlinedetailIdIndex` (`IDKEY`)
195 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
196
197 $conn->query($createTable3);
198
199 $query = "SELECT AccountNumber, Total from transactions";
200 $result = mysqli_query($conn, $query);
201 $data = array();
202 $accNum = array();
203 $total = 0;
204 while($row = mysqli_fetch_assoc($result)) {
205 array_push($data, $row);
206 array_push($accNum, $row['AccountNumber']);
207 }
208
209 $uniqueAcc = array_unique($accNum);
210 $final = array();
211 $object = array();
212 foreach($uniqueAcc as $acc) {
213 foreach($data as $item) {
214 if ($acc == $item['AccountNumber'] ) {
215 $total = $total + abs($item['Total']);
216 }
217 }
218 $object[0] = $acc;
219 $object[1] = $total;
220 array_push($final,$object);
221 $object = array();
222 $total = 0;
223 }
224
225 foreach($final as $item) {
226
227
228 $getAccount = "SELECT FullName, ListID FROM account where AccountNumber = '".$item[0]."'";
229
230 $result = $conn->query($getAccount);
231 $row = $result->fetch_assoc();
232
233 $fullNames = explode(':', $row['FullName']);
234 if (isset($fullNames[1])) {
235 $row['FullName'] = $fullNames[1];
236 }
237
238 if ($result->num_rows > 0) {
239
240 $sql_insert3 = "INSERT into `journalcreditlinedetail`(`TxnLineID`,`AccountRef_ListID`,`AccountRef_FullName`,`Amount`,`TaxAmount`,`Memo`,`IsAdjustment`,`EntityRef_ListID`,`EntityRef_FullName`,`ClassRef_ListID`,`ClassRef_FullName`,`ItemSalesTaxRef_ListID`,`ItemSalesTaxRef_FullName`,`BillableStatus`,`CustomField1`,`CustomField2`,`CustomField3`,`CustomField4`,`CustomField5`,`CustomField6`,`CustomField7`,`CustomField8`,`CustomField9`,`CustomField10`,`CustomField11`,`CustomField12`,`CustomField13`,`CustomField14`,`CustomField15`,`IDKEY`,`GroupIDKEY`,`SeqNum`) values
241 (NULL,'80000335-1367349320',NULL,'". $item[1] ."',NULL, 'Total Transactions for ". $item[0] . ' ' . $row['FullName'] ."',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'". $TxnID ."',NULL,NULL)";
242
243 $conn->query($sql_insert3);
244 }else{
245
246 $sql_insert3 = "INSERT into `journalcreditlinedetail`(`TxnLineID`,`AccountRef_ListID`,`AccountRef_FullName`,`Amount`,`TaxAmount`,`Memo`,`IsAdjustment`,`EntityRef_ListID`,`EntityRef_FullName`,`ClassRef_ListID`,`ClassRef_FullName`,`ItemSalesTaxRef_ListID`,`ItemSalesTaxRef_FullName`,`BillableStatus`,`CustomField1`,`CustomField2`,`CustomField3`,`CustomField4`,`CustomField5`,`CustomField6`,`CustomField7`,`CustomField8`,`CustomField9`,`CustomField10`,`CustomField11`,`CustomField12`,`CustomField13`,`CustomField14`,`CustomField15`,`IDKEY`,`GroupIDKEY`,`SeqNum`) values
247 (NULL,NULL,NULL,'". $item[1] ."',NULL, 'Total Transactions for ". $item[0] . ' ' . $row['FullName'] . "',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'". $TxnID ."',NULL,NULL)";
248 $conn->query($sql_insert3);
249 }
250
251 }
252 }
253}
254
255if (isset($_POST["import"])) {
256
257 $fileName = $_FILES["file"]["tmp_name"];
258 //$filterfileName = $_FILES["filterfile"]["tmp_name"];
259 $created = date('Y-m-d H:i:s');
260
261 if ($_FILES["file"]["size"] > 0) {
262
263// Transaction formatting
264 $handle = fopen($fileName, "r");
265 $intisql = "DROP TABLE IF EXISTS `transactions`";
266
267 $conn->query($intisql);
268 $creattablesql = "CREATE TABLE `transactions` (
269 `Receipt` varchar(1024) NOT NULL,
270 `AccountName` varchar(1024) NOT NULL,
271 `Total` double NOT NULL,
272 `Comments` varchar(1024) NOT NULL,
273 `Date` varchar(1024) NOT NULL,
274 `Time` varchar(1024) NOT NULL,
275 `AccountNumber` varchar(11) NOT NULL,
276 `Filename` varchar(1024) NOT NULL
277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
278 $conn->query($creattablesql);
279
280// Change CSV Format
281 $list = array();
282 $row = 0;
283 $data1 = 0;
284 if ($handle !== FALSE) {
285 while (($data1 = fgetcsv($handle, 10000, ",")) !== FALSE) {
286 // echo count($data); exit;
287 $row++;
288 if ($row < 8 ) continue;
289 else {
290 for ($c=0; $c < count($data1); $c++) {
291 if ($data1[$c] == '') continue;
292 array_push($list, $data1[$c]);
293 }
294 array_push($list, 'n');
295 }
296 }
297 fclose($handle);
298 }
299
300 $data = array();
301 $object = array();
302
303 foreach($list as $item) {
304 if ($item == 'n') {
305 array_push($data,$object);
306 $object = array();
307 } else {
308 array_push($object, $item);
309 }
310 }
311
312 array_splice($data, count($data) - 2, 2);
313
314 foreach($data as $key=>$column) {
315 $cnt = count($column);
316
317 for ($i = $cnt; $i < 8; $i++)
318 {
319 $column[$i] = "##";
320 }
321
322 $temp = str_replace(',', '', $column[2]);
323 $total = (float)$temp;
324 $accNumber = (int)$column[6];
325
326 $sqlInsert = "INSERT into transactions (Receipt, AccountName, Total,Comments, Date, Time, AccountNumber, Filename)
327 values ('" . $column[0] . "','" . $column[1] . "','" . $total . "','" . $column[3] . "','" . $column[4] . "','" . $column[5] . "','" . $accNumber . "','" . $_FILES["file"]["name"] . "')";
328 $result = mysqli_query($conn, $sqlInsert);
329
330 /* $sqlInsert = "INSERT into transaction_history (Receipt, AccountName, Total,Comments, Date, Time, AccountNumber, Filename, created)
331 values ('" . $column[0] . "','" . $column[1] . "','" . $total . "','" . $column[3] . "','" . $column[4] . "','" . $column[5] . "','" . $accNumber . "','" . $_FILES["file"]["name"] . "', '$created')";
332 $resultHistory = mysqli_query($conn, $sqlInsert); */
333 }
334
335// Filter CSV File Import
336 // $filterfile = fopen($filterfileName, "r");
337 // $intisql = "DROP TABLE IF EXISTS `filters`";
338
339 // $conn->query($intisql);
340 // $creattablesql = "CREATE TABLE `filters` (
341 // `filter_key` varchar(5000) NOT NULL,
342 // `number` char(10) NOT NULL
343 // ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
344 // $conn->query($creattablesql);
345
346 // while (($column = fgetcsv($filterfile, 10000, ",")) !== FALSE) {
347 // $sqlInsert = "INSERT into filters (filter_key, number)
348 // values ('" . $column[0] . "','" . $column[1]."')";
349 // $result_filter = mysqli_query($conn, $sqlInsert);
350 // }
351 if (! empty($result) ) {
352 $type = "success";
353 $message = "CSV Data Imported into the Database";
354 } else {
355 $type = "error";
356 $message = "Problem in Importing CSV Data";
357 }
358 }
359
360 function escape_regex ($value) {
361 $value = str_replace('$', '\\\$', $value);
362 $value = str_replace('.', '\\\.', $value);
363 $value = str_replace('(', '\\\(', $value);
364 $value = str_replace(')', '\\\)', $value);
365 return $value;
366 }
367
368 $sql = "SELECT * FROM filters";
369 $result = $conn->query($sql);
370
371 $filters = array();
372 if ($result->num_rows > 0) {
373 while($row = $result->fetch_assoc()) {
374 $each_key = explode('|',$row["filter_key"]);
375 $filternumber = $row['number'];
376 foreach($each_key as $value)
377 {
378 $value = escape_regex($value);
379 $sql1 = "SELECT * FROM transactions WHERE Comments REGEXP '[[:<:]]".$value."[[:>:]]'";
380
381 $res = $conn->query($sql1);
382 if ($res->num_rows > 0) {
383 $comments = array();
384 while($row1 = $res->fetch_assoc()) {
385 array_push($comments, $row1['Comments']);
386 }
387 $resultUpdate = "(";
388 foreach ($comments as $index => $comment) {
389 if ($index == count($comments) - 1) {
390 $resultUpdate .= "'$comment'";
391 } else {
392 $resultUpdate .= "'$comment', ";
393 }
394 }
395 $resultUpdate .= ")";
396 $updatesql = "UPDATE transactions SET AccountNumber='".$filternumber."' WHERE Comments IN $resultUpdate";
397 $conn->query($updatesql);
398 /* $updatesql = "UPDATE transaction_history SET AccountNumber='".$filternumber."' WHERE Comments IN $resultUpdate";
399 $conn->query($updatesql); */
400 } else {
401 $sql1 = "SELECT * FROM transactions WHERE Comments REGEXP '^" .$value . "$'";
402 $res = $conn->query($sql1);
403 if ($res->num_rows > 0) {
404 $comments = array();
405 while($row1 = $res->fetch_assoc()) {
406 array_push($comments, $row1['Comments']);
407 }
408 $resultUpdate = "(";
409 foreach ($comments as $index => $comment) {
410 if ($index == count($comments) - 1) {
411 $resultUpdate .= "'$comment'";
412 } else {
413 $resultUpdate .= "'$comment', ";
414 }
415 }
416 $resultUpdate .= ")";
417 $updatesql = "UPDATE transactions SET AccountNumber='".$filternumber."' WHERE Comments IN $resultUpdate";
418 $conn->query($updatesql);
419 /* $updatesql = "UPDATE transaction_history SET AccountNumber='".$filternumber."' WHERE Comments IN $resultUpdate";
420 $conn->query($updatesql); */
421 }
422 }
423 }
424 }
425 }
426}
427?>
428<!DOCTYPE html>
429<html>
430
431<head>
432<script src="jquery-3.2.1.min.js"></script>
433<script type="text/javascript">
434 $(document).ready(function () {
435 $('.account-number-td').click(function () {
436 $(this).find('.account-number').hide();
437 $(this).find('.account-number-input').show();
438 });
439
440 $('.account-number-input').blur(function () {
441 var accountNumber = $(this).val().trim();
442 if (accountNumber == '') {
443 alert('You must enter number, cannot be blank');
444 return false;
445 }
446 var self = this;
447 var comment = $(this).closest('tr').find('.transaction-comments').text();
448 $.ajax({
449 type: 'POST',
450 url: 'update-filter.php',
451 data: {
452 account_number: accountNumber,
453 comment: comment
454 },
455 dataType: 'json',
456 success: function (response) {
457 if (response.status == 'success') {
458 $(self).hide();
459 $(self).parent().find('.account-number').text(accountNumber).show();
460 $('#userTable tbody tr').each(function () {
461 if($(this).find('.transaction-comments').text() == comment) {
462 $(this).find('.account-number').text(accountNumber);
463 $(this).find('.account-number-input').text(accountNumber);
464 }
465 });
466 } else if (response.status == 'error') {
467 alert(response.message);
468 }
469 }
470 });
471 });
472 });
473</script>
474
475<style>
476body {
477 font-family: Arial;
478 width: 550px;
479}
480
481.outer-scontainer {
482 background: #b0cea4;
483 border: #e0dfdf 1px solid;
484 padding: 20px;
485 border-radius: 2px;
486 width: 100vh;
487}
488
489.input-row {
490 margin-top: 0px;
491 margin-bottom: 20px;
492}
493
494.btn-submit {
495 background: #333;
496 border: #1d1d1d 1px solid;
497 color: #f0f0f0;
498 font-size: 0.9em;
499 width: 100px;
500 border-radius: 2px;
501 cursor: pointer;
502}
503
504.outer-scontainer table {
505 border-collapse: collapse;
506 width: 100%;
507}
508
509.outer-scontainer th {
510 border: 1px solid #dddddd;
511 padding: 8px;
512 text-align: left;
513}
514
515.outer-scontainer td {
516 border: 1px solid #dddddd;
517 padding: 8px;
518 text-align: left;
519}
520
521#response {
522 padding: 10px;
523 margin-bottom: 10px;
524 border-radius: 2px;
525 display:none;
526}
527
528.success {
529 background: #c7efd9;
530 border: #bbe2cd 1px solid;
531}
532
533.error {
534 background: #fbcfcf;
535 border: #f3c6c7 1px solid;
536}
537
538div#response.display-block {
539 display: block;
540}
541
542.btn {
543 background: lightblue;
544 border: none;
545 width: 135px;
546 height: 35px;
547 box-shadow: 3px 3px 3px grey;
548 color: azure;
549}
550
551.btn-group {
552 display: flex;
553 width: 300px;
554 justify-content: space-between;
555}
556
557</style>
558<script type="text/javascript">
559$(document).ready(function() {
560 $("#frmCSVImport").on("submit", function () {
561
562 $("#response").attr("class", "");
563 $("#response").html("");
564 var fileType = ".csv";
565 var regex = new RegExp("([a-zA-Z0-9\s_\\.\-:])+(" + fileType + ")$");
566 if (!regex.test($("#file").val().toLowerCase())) {
567 $("#response").addClass("error");
568 $("#response").addClass("display-block");
569 $("#response").html("Invalid File. Upload : <b>" + fileType + "</b> Files.");
570 return false;
571 }
572 return true;
573 });
574});
575</script>
576</head>
577
578<body>
579
580 <?php
581 if ($zeroNumbers) {
582 ?>
583 <div class="outer-scontainer">
584 <h4>You cannot insert because these items do not have account number</h4>
585 <table id='userTable'>
586 <thead>
587 <tr>
588 <th>Comments</th>
589 <th>AccountNumber</th>
590 </tr>
591 </thead>
592 <tbody>
593 <?php
594 foreach ($zeroNumbers as $row) {
595 ?>
596 <tr>
597 <td class="transaction-comments"><?php echo $row['Comments']; ?></td>
598 <td class="account-number-td">
599 <span class="account-number"><?php echo $row['AccountNumber']; ?></span>
600 <input type="text" class="account-number-input" value="<?php echo $row['AccountNumber']; ?>" style="display: none" />
601 </td>
602 </tr>
603 <?php
604 }
605 ?>
606 </tbody>
607 </table>
608 <div class="btn-group" style="margin-top: 10px">
609 <div class="insert-data">
610 <form method='post' enctype="multipart/form-data" name="frmInsertData" id="frmInsertData">
611 <input type="submit" name='insertData' value='INSERT DATA' class="btn" />
612 </form>
613 </div>
614 </div>
615 </div>
616 <?php
617 exit;
618 }
619 ?>
620
621 <h2>TIJULE Automation for POS Expense Accounts Beta 1</h2>
622
623 <div id="response" class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>"><?php if(!empty($message)) { echo $message; } ?></div>
624 <div class="outer-scontainer">
625 <div class="row">
626
627 <form class="form-horizontal" action="" method="post"
628 name="frmCSVImport" id="frmCSVImport" enctype="multipart/form-data">
629 <div class="input-row">
630 <label class="col-md-4 control-label">Choose <strong>Transation CSV</strong>
631 File</label> <input type="file" name="file"
632 id="file" accept=".csv">
633 <br><br>
634 <!-- <label class="col-md-4 control-label">Choose <strong>Filter CSV</strong>
635 File</label> <input type="file" name="filterfile"
636 id="filterfile" accept=".csv"> -->
637 <button type="submit" id="submit" name="import"
638 class="btn btn-submit">IMPORT</button>
639 <br />
640 </div>
641 </form>
642 </div>
643 <div class="btn-group">
644 <div class="insert-data">
645 <form method='post' enctype="multipart/form-data" name="frmInsertData" id="frmInsertData">
646 <input type="submit" name='insertData' value='INSERT DATA' class="btn" />
647 </form>
648 </div>
649 <div class="export">
650 <form method='post' action='export.php'>
651 <input type="submit" name='export' value='CSV Export' class="btn" />
652 </form>
653 </div>
654 <div class="export">
655 <form method='post' action='filteredit.php'>
656 <input type="submit" name='filter' value='Filter Page' class="btn" />
657 </form>
658 <div class="export">
659 <form method='post' action='export_pdf.php'>
660 <input type="submit" name='export' value='PDF Export' class="btn" />
661 </form>
662 </div>
663 </div>
664 </div>
665 <br>
666
667 <?php
668 $sqlSelect = "SELECT t.*, a.FullName FROM transactions t LEFT JOIN account a ON STRCMP(t.AccountNumber, a.AccountNumber) = 0 ORDER BY t.AccountNumber";
669 $result = mysqli_query($conn, $sqlSelect);
670
671 if (mysqli_num_rows($result) > 0) {
672 ?>
673 <table id='userTable'>
674 <thead>
675 <tr>
676 <th>Receipt</th>
677 <th>AccountName</th>
678 <th>Total</th>
679 <th>Comments</th>
680 <th>Date</th>
681 <th>Time</th>
682 <th>AccountNumber</th>
683 <th>Account Full Name</th>
684 <th>Filename</th>
685 </tr>
686 </thead>
687 <?php
688 while ($row = mysqli_fetch_array($result)) {
689 ?>
690
691 <tbody>
692 <tr>
693 <td><?php echo $row['Receipt']; ?></td>
694 <td><?php echo $row['AccountName']; ?></td>
695 <td><?php echo number_format( $row['Total'], 2 ); ?></td>
696 <td class="transaction-comments"><?php echo $row['Comments']; ?></td>
697 <td><?php echo $row['Date']; ?></td>
698 <td><?php echo $row['Time']; ?></td>
699 <td class="account-number-td">
700 <span class="account-number"><?php echo $row['AccountNumber']; ?></span>
701 <input type="text" class="account-number-input" value="<?php echo $row['AccountNumber']; ?>" style="display: none" />
702 </td>
703 <td><?php echo $row['FullName']; ?></td>
704 <td><?php echo $row['Filename']; ?></td>
705 </tr>
706 <?php
707 }
708 ?>
709 </tbody>
710 </table>
711 <?php } ?>
712 </div>
713</body>
714
715</html>