Question

PHP, HTML5

Accounting Import

The Mammoth Island Tourism Company is undergoing a project to digitize a large quantity of paper documents, sales records going back 100 years.
They’ve already commissioned a company to digitize the documents into a text format, but now they require an application to extract a small amount of useful information from the documents.
Unfortunately, their accounting format changed several times over the course of business, so the application must be intelligent enough to find the data.
For this application, the client would pay $490.

Interface

Provide an interface where the user can upload a document of any text or csv file type. Three sample datasets are provided for you.

When the file is uploaded, check that it contains the proper type of information – text. Other types, such as images, should not be allowed.

Once the file is uploaded, store the file in a secure location.

File Parsing

Use any methods at your disposal to read the contents of the document
Certain types of information should be gathered and stored for output.

Phone Number – any data that resembles a phone number (with or without area code) should be extracted for the data.
B3.

Account Number – The client uses a specific format to record account numbers. This format is two letters followed by eight numbers
(prior to 1920 they used two letters followed by four numbers).


Sales amount – the amount of each sale should be extracted if known.

data_1914.txt

10/20/1914, Sold to Farnsworth for $12.78, VE5373
5/2/1914, Darcie Barker, $0.97 sold, WN4122
1/1/1914 (CG2204), Daniel, traded pigs worth $7.94
7/18/1914 Latashia Goldstein, ph. 513-0190, OX2156  $58.10
1/31/1914 Verlene Dickinson, JG9670 price not recorded

data_1938.csv

Customer	ActRef	tele	Total	Date
Becker	KW37749876	433-3431	$1.79	11/17/38
Ferris	PS75344973	838-9495	$74.29	3/5/38
Alonzo	YB22356502	633-8635	$171.73	3/30/38
Fultz	VS12702734	968-7651	$8.51	4/13/38
Estrada	NG57202870	909-2601	$28.40	7/18/38
Christian	PW26261775	336-7487	$62.29	3/30/38
Gilchrist	DG74291195	569-3558	$67.73	6/15/38
Andrade	DP88009441	110-6774	$168.94	7/13/38
Culpepper	VW26510507	870-6483	$32.31	8/23/38
Bollinger	AU52849187	316-4614	$125.13	2/3/38
Forbes	CV99714005	359-7954	$115.82	11/8/38
Frantz	IL87423619	424-6335	$67.23	10/13/38
Barrera	MD77991820	312-2671	$55.75	12/1/38
Brunson	JQ10021685	333-2355	$129.24	8/15/38
Burrell	JV19475662	280-1447	$159.74	5/15/38
Ely	BO26578567	611-3813	$67.79	3/7/38
Beard	PX17737465	272-8510	$108.70	12/15/38
Grayson	GH35585663	119-8614	$18.40	4/6/38
Dupree	FW48708763	400-7324	$89.12	6/21/38
Beaty	IG18365372	652-9484	$182.58	7/31/38
Castillo	ZX35309452	587-3079	$107.22	3/24/38
Brewer	VH68557195	765-6322	$175.87	12/20/38
Atwood	KM36381396	893-8443	$190.71	3/15/38
Childers	SL95951973	885-1774	$162.10	1/27/38
Bravo	BP48706583	641-3583	$7.43	3/5/38
Bassett	XR35526858	558-8701	$49.60	12/3/38
Bullock	ZK29459849	224-1429	$52.57	5/22/38
Delvalle	SF27056636	145-1627	$169.53	6/10/38
Bryson	HE60283997	644-4631	$67.49	9/4/38
Crocker	QI11130784	791-1154	$156.12	10/2/38
Gaye	ZX89555454	400-6030	$47.12	7/1/38
Buford	QC18450718	704-3688	$185.77	4/17/38
Easter	HB76267072	641-2625	$165.10	6/19/38
Carpenter	NJ47701986	746-1477	$36.27	9/29/38
Flanagan	QL40119055	987-6896	$30.90	3/9/38
Conner	AZ75193831	372-2482	$171.68	11/14/38
Downey	AV96350502	938-3724	$63.73	1/27/38
Field	GV88902011	456-4180	$111.26	1/8/38
Benavidez	GN58258130	735-9624	$90.36	4/3/38
Daley	CA88215077	746-7540	$75.48	2/17/38
Fournier	HR57654575	706-1542	$160.61	3/1/38
Crow	NI98289769	873-7585	$19.60	8/20/38
Dent	HH82555104	673-4005	$39.93	9/4/38
Graf	BU44705117	318-1473	$120.53	12/14/38
Cabrera	UT14179947	939-1345	$86.38	3/23/38
Chappell	BM55779074	920-4068	$132.66	7/25/38
Cornelius	BU22686515	770-3456	$100.58	9/24/38

data_1952.csv

LastName	FirstName	Phone	Acct	Amount	Date
Cloud	Lennie	[phone]	SD32748200	$62.60	12/12/52
Bird	Bridgett	[phone]	QT62121363	$819.74	6/3/52
Bullard	Yukiko	[phone]	XI71419317	$399.70	4/23/52
Gaston	Gricelda	[phone]	KB25168634	$761.37	11/10/52
Curran	Marybeth	[phone]	GS27656664	$271.66	12/3/52
Graham	Jesse	[phone]	OS43923854	$539.79	9/3/52
Dorman	Denver	[phone]	IE38271069	$723.84	3/29/52
Duffy	Davida	[phone]	XK69920126	$374.11	9/24/52
Bonilla	Gertrudis	[phone]	RA66777672	$53.99	9/5/52
Gleason	Lasonya	[phone]	XZ72026932	$993.78	8/2/52
Fowler	Thomasine	[phone]	KV98215691	$471.76	4/12/52
Braun	Kandi	[phone]	MY92012572	$971.40	8/25/52
Culver	Isobel	[phone]	YE46326415	$504.91	8/2/52
Garza	Coralie	[phone]	CT56216137	$155.30	5/29/52
Garvin	Rosalba	[phone]	XL99747835	$7.70	7/22/52
Bass	Willene	[phone]	EE23060513	$912.52	4/16/52
Cherry	Shonna	[phone]	YH63485833	$298.50	11/3/52
Barton	Ilene	[phone]	WQ87994441	$311.45	9/23/52
Daley	Carma	[phone]	VX16280329	$941.78	6/21/52
Cottrell	Collen	[phone]	NU59547697	$88.47	11/14/52
Baird	Willis	[phone]	NH76289131	$46.00	3/11/52
Anaya	Janis	[phone]	WO30458968	$129.11	8/23/52
Gordon	Glenn	[phone]	GO15909512	$5.24	1/17/52
Frank	Ned	[phone]	GJ39632163	$579.24	7/7/52
Bender	Larissa	[phone]	QK24407868	$572.97	2/16/52
Andrews	Andre	[phone]	WI36805141	$920.27	11/24/52
Elmore	Natacha	[phone]	MO42520894	$516.25	7/8/52
Brunner	Eric	[phone]	RD46148816	$222.58	6/1/52
Denney	Dolly	[phone]	CL89918731	$408.40	12/29/52
Camp	Bryant	[phone]	UR44467423	$429.66	5/23/52
Bowser	Billie	[phone]	WT94045377	$632.64	2/10/52
Frazier	Vivien	[phone]	ZC37857131	$973.37	1/30/52
Drew	Ken	[phone]	VB48943203	$440.40	12/12/52
Dobson	Latrisha	[phone]	QT99586651	$367.82	1/1/52
Gonzalez	Asley	[phone]	ZG69949860	$242.54	1/8/52
Fields	Sibyl	[phone]	EQ32775781	$570.20	3/5/52
Ervin	Robena	[phone]	DD91688279	$486.39	9/3/52
Deaton	Wendolyn	[phone]	TQ39891166	$939.27	12/1/52
Flint	Val	[phone]	OH55745466	$506.80	4/19/52
Dwyer	Shantel	[phone]	FV36451329	$987.99	11/11/52
Battle	Tim	[phone]	BG91667736	$482.30	1/1/52
Dickerson	Luella	[phone]	GM16281661	$199.96	5/17/52
Amos	Katherina	[phone]	UL97498802	$367.68	11/14/52
Berman	Many	[phone]	YR43800386	$952.59	8/19/52
Canfield	Lincoln	[phone]	MK12186071	$482.88	2/28/52
Biggs	Otis	[phone]	IM15206613	$259.65	1/29/52
Barron	Narcisa	[phone]	QF85885562	$373.56	11/22/52
Allen	Kevin	[phone]	TF44517735	$396.45	1/28/52
Bowen	Cristi	[phone]	RK50366094	$692.83	9/26/52
Gooden	Jacqui	[phone]	MR75928449	$87.87	8/25/52
Ervin	Janice	[phone]	LH69521263	$675.46	11/14/52
Crowe	Branda	[phone]	GN41641082	$797.77	3/23/52
Bentley	Loreta	[phone]	QM98200111	$227.45	9/23/52
Bridges	Thora	[phone]	HY68618136	$377.44	2/6/52
Andersen	Hunter	[phone]	KM37181533	$540.74	3/2/52
Diggs	Wilber	[phone]	VA42740686	$885.11	7/31/52
Gallant	Ross	[phone]	GQ96210531	$932.30	9/5/52
Crist	Silvia	[phone]	FG69201952	$573.90	10/29/52
Carranza	Kaye	[phone]	BS94471194	$941.70	2/17/52
Goodwin	Errol	[phone]	RR65548779	$979.92	4/12/52
Cheatham	Jon	[phone]	ME86017666	$151.98	7/20/52
Bullock	Juliann	[phone]	TQ11308364	$79.77	3/9/52
Goldstein	Logan	[phone]	OD91867546	$607.50	7/15/52
Daniel	Shara	[phone]	TX51241726	$260.45	4/16/52
Faulkner	Betsy	[phone]	JG49363981	$773.10	12/31/52
Blanton	Devorah	[phone]	EJ98382954	$963.78	12/17/52
Agee	Kathlyn	[phone]	TO75951072	$59.50	8/15/52
Colon	Sharice	[phone]	BJ87317821	$779.59	2/11/52
Espinoza	Dani	[phone]	FP63860845	$62.43	3/11/52
Case	Mallie	[phone]	DJ92062431	$408.97	4/11/52
Ellison	Kelvin	[phone]	EJ91177172	$888.84	3/14/52
French	Mckinley	[phone]	IR62109871	$59.54	9/8/52
Burton	Krista	[phone]	WW59262734	$933.50	11/17/52
Banks	Milo	[phone]	TC88239868	$368.43	12/13/52
Deal	Huong	[phone]	UK81771435	$978.34	10/6/52
Bynum	Loise	[phone]	RM68037608	$746.15	5/10/52
Chung	Gudrun	[phone]	KV17844389	$273.35	12/15/52
Goodwin	Penni	[phone]	OR15637798	$559.98	5/26/52
Casey	Judith	[phone]	LF80962174	$116.93	8/10/52
Buford	Rogelio	[phone]	GK12567993	$733.30	1/12/52
Funk	Ronald	[phone]	OV86214287	$781.82	12/15/52
Garrett	Mariko	[phone]	YE67130251	$840.68	10/20/52
Barr	Numbers	[phone]	EA25708427	$889.63	4/18/52
Connor	Chris	[phone]	FJ60665155	$511.31	9/30/52
Coley	Sydney	[phone]	YS21557399	$197.36	12/5/52
Boyer	Murray	[phone]	YK70209446	$438.64	5/16/52
Cody	Isela	[phone]	LE30056709	$554.46	5/9/52
Chandler	Collette	[phone]	QR13661460	$953.71	7/16/52
Donald	Louetta	[phone]	DB86963673	$865.30	2/25/52
Faulk	Glennie	[phone]	EG14255403	$543.10	6/25/52
Barrera	Johanna	[phone]	IA16027525	$323.25	1/3/52
Carson	Taren	[phone]	BB79467632	$896.34	2/12/52
Deloach	Lauren	[phone]	NG11584399	$287.28	6/24/52
Broughton	Remona	[phone]	HI30882321	$362.66	10/28/52
Chaney	Renita	[phone]	RM74636335	$314.41	6/1/52
Bruce	Phebe	[phone]	TF99188405	$164.92	12/6/52
Finn	Lucinda	[phone]	YV86552937	$439.23	6/21/52
Crisp	Kathyrn	[phone]	CU10440842	$838.00	11/13/52
Escamilla	Zola	[phone]	UJ79837751	$307.87	5/21/52
Aldrich	Terresa	[phone]	MS82367152	$736.99	4/26/52
Anders	Leola	[phone]	ZP21862867	$241.26	9/7/52
Cheney	Christinia	[phone]	ZG58579357	$76.23	9/2/52
Andrade	Sherika	[phone]	NV37246113	$929.99	6/6/52
Cohen	Carry	[phone]	CT89191190	$408.88	7/11/52
Burroughs	So	[phone]	HD35006136	$337.82	7/7/52
Corbett	Kara	[phone]	HB11769401	$11.58	1/12/52
Blount	Ophelia	[phone]	RN14379973	$801.61	10/30/52
Banda	Yuonne	[phone]	ND35387619	$697.74	2/17/52
Betts	Lynsey	[phone]	ET96303673	$923.42	8/30/52
Burrell	Dannie	[phone]	JL98291339	$408.78	11/16/52
Goss	Eartha	[phone]	IS39713761	$60.15	2/14/52
Doe	Elbert	[phone]	YW47906684	$895.34	8/3/52
Dowd	Mollie	[phone]	FL62940071	$387.66	12/5/52
Bryan	Halley	[phone]	KD95214880	$581.17	3/30/52
Fischer	Natividad	[phone]	OJ18140162	$432.30	5/25/52
Galvan	Jammie	[phone]	UU83904764	$637.43	9/2/52
Deloach	Easter	[phone]	CL23445309	$707.77	12/27/52
Dominguez	Tasia	[phone]	CJ69470186	$779.26	6/22/52
Gabriel	Devora	[phone]	OJ34209798	$534.46	12/15/52
Dunn	Patty	[phone]	WG70412056	$308.78	7/21/52
Fish	Tressa	[phone]	BN67007643	$685.10	7/8/52
Fagan	Annette	[phone]	XS93653551	$619.14	12/9/52
Burleson	Randolph	[phone]	YC41080386	$411.10	5/9/52
Baum	Annmarie	[phone]	KL94155277	$967.43	1/24/52
Benson	Caitlyn	[phone]	NP38555595	$129.69	9/17/52
Devore	Carl	[phone]	JJ84156947	$825.16	8/13/52
Chang	Susana	[phone]	BZ21714765	$987.65	7/1/52
Alaniz	Roxana	[phone]	SL41710877	$698.35	4/20/52
Giles	Camila	[phone]	CB55120826	$389.20	12/13/52
Fortner	Elizebeth	[phone]	OK73349838	$69.70	9/21/52
Enriquez	Sandee	[phone]	AK31565268	$597.44	5/28/52
Buckner	Harland	[phone]	TH83429148	$11.80	10/18/52
Carr	Anabel	[phone]	SI55060544	$970.85	6/25/52
Collier	Kina	[phone]	MQ47774389	$603.51	11/14/52
Garvin	Tameka	[phone]	GU48960388	$689.70	2/9/52
Churchill	Shenna	[phone]	RL79150533	$707.21	3/1/52
Easley	Cordie	[phone]	QY70133930	$559.18	7/7/52
Bobo	Danika	[phone]	QL75792152	$560.55	5/18/52
Bates	Matha	[phone]	FK45236603	$804.60	4/5/52
Boykin	Jannette	[phone]	NP13533697	$119.95	3/3/52
Cornish	Carolee	[phone]	HF37883924	$92.45	6/25/52
Briggs	Camilla	[phone]	YR83480689	$862.54	2/1/52
Eubanks	Moses	[phone]	EO61448227	$840.33	9/9/52
Bernard	Sharleen	[phone]	IT67219285	$15.47	5/28/52
Bartley	Rebekah	[phone]	RF44675396	$521.91	4/3/52
Gentile	Magdalen	[phone]	WA16762257	$476.10	5/1/52
Biddle	Normand	[phone]	GC27224716	$854.21	8/10/52
Fournier	Zulema	[phone]	DW23040271	$225.47	12/23/52
Archuleta	Stacee	[phone]	AO30413985	$501.36	10/28/52
Blocker	Deshawn	[phone]	JW60372780	$174.91	11/19/52
Angel	Brant	[phone]	FU11871999	$315.64	6/29/52
Flowers	Linh	[phone]	BL76826948	$44.56	11/17/52
Brennan	Latina	[phone]	CG89142590	$256.63	2/3/52
Cruz	Astrid	[phone]	UV46494175	$77.99	10/6/52
Betancourt	Georgetta	[phone]	FE15638343	$513.32	9/5/52
Bonner	Charlott	[phone]	SE43286034	$611.18	2/12/52
Coffman	Ezra	[phone]	OG72384422	$578.66	9/1/52
Craft	Yon	[phone]	FW35379821	$301.35	3/25/52

Output

Provide simple information about the data file: filename and record count

List all records with corresponding account, phone, and amount.
(Leave blank if some information cannot be determined.)

Provide the total sale amount from the entire document.

Sample Output

  

File Name	Records	Total
data_1938.csv	48	$4,512

Account	Phone	Amount
KW37749876	433-3431	$1.79
PS75344973	838-9495	$74.29
YB22356502	633-8635	$171.73
VS12702734	968-7651	$8.51
NG57202870	909-2601	$28.40
PW26261775	336-7487	$62.29
DG74291195	569-3558	$67.73
DP88009441	110-6774	$168.94
VW26510507	870-6483	$32.31
AU52849187	316-4614	$125.13
CV99714005	359-7954	$115.82
IL87423619	424-6335	$67.23
MD77991820	312-2671	$55.75
JQ10021685	333-2355	$129.24
JV19475662	280-1447	$159.74
BO26578567	611-3813	$67.79
PX17737465	272-8510	$108.70
GH35585663	119-8614	$18.40
FW48708763	400-7324	$89.12
IG18365372	652-9484	$182.58
ZX35309452	587-3079	$107.22
VH68557195	765-6322	$175.87
KM36381396	893-8443	$190.71
SL95951973	885-1774	$162.10
BP48706583	641-3583	$7.43
XR35526858	558-8701	$49.60
ZK29459849	224-1429	$52.57
SF27056636	145-1627	$169.53
HE60283997	644-4631	$67.49
QI11130784	791-1154	$156.12
ZX89555454	400-6030	$47.12
QC18450718	704-3688	$185.77
HB76267072	641-2625	$165.10
NJ47701986	746-1477	$36.27
QL40119055	987-6896	$30.90
AZ75193831	372-2482	$171.68
AV96350502	938-3724	$63.73
GV88902011	456-4180	$111.26
GN58258130	735-9624	$90.36
CA88215077	746-7540	$75.48
HR57654575	706-1542	$160.61
NI98289769	873-7585	$19.60
HH82555104	673-4005	$39.93
BU44705117	318-1473	$120.53
UT14179947	939-1345	$86.38
BM55779074	920-4068	$132.66
BU22686515	770-3456	$100.58

Answer 1

<html>
<body>

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post" enctype="multipart/form-data">
Select file to upload:
<input type="file" name="sel_file" id="fileToUpload">
<input type="submit" value="submit" name="submit">
</form>

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db="test";
// Create connection
$conn = new mysqli($servername, $username, $password,$db);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$count=0;
echo "Connected successfully";
   if(isset($_POST['submit']))
   {
       $fname=$_FILES['sel_file']['name'];
       echo 'upload file : '.$fname;
       $chk_ext=explode(".",$fname);

       if(strtolower(end($chk_ext))=="csv")
       {
           $filename=$_FILES['sel_file']['tmp_name'];
           $handle=fopen($filename,"r");
           while(($data=fgetcsv($handle,1000,","))!==FALSE)
           {
               $sql="INSERT INTO student(Customer,ActRef,tele,Total,Date)values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";

               mysql_query($sql)or die(mysql_error());

               $count++;
           }
           fclose($handle);
           echo "imported";
       }
       else
       {
           echo"invalid file";
       }
   }
?>
</body>
</html>