AzN4Djmz

1#!/usr/bin/perl
2
3use DBI;
4use strict;
5use warnings;
6use Time::Piece;
7
8my $driver   = "SQLite";
9my $database = "cartask.db";
10my $dsn = "DBI:$driver:dbname=$database";
11my $userid = "";
12my $password = "";
13my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) # connect to db
14   or die $DBI::errstr;
15print "Opened database successfully\n";
16
17my $stmt = qq(CREATE TABLE IF NOT EXISTS COMPANY
18   (VIN INT PRIMARY KEY,
19      PTS        BLOB,
20      RELEASE         TEXT,
21      DISPOSAL         TEXT);); #create a table if not exist do nothing
22
23my $rv = $dbh->do($stmt); 
24if($rv < 0) {
25   print $DBI::errstr;
26} else {
27   print "Table created successfully\n";
28}
29sub ADD { 				#create a function
30	print "Enter VIN: ";
31        my $VIN = <STDIN>;   		#read next row
32        chomp $VIN; 			#avoid \n on last field
33
34        print "Enter name of image to add(e.g., car.jpg): ";
35        my $IMG = <STDIN>;		#read next row
36        chomp $IMG; 			#avoid \n on last field
37
38        open IMAGE, $IMG or die $!; 	#we open an image
39        my ($image, $buff);		
40        while(read IMAGE, $buff, 1024) {#we read binary data from the image file 
41                $image .= $buff;
42        } 				
43
44        print "Enter date of release(e.g., YYYY-MM-DD): ";
45        my $RELstr = <STDIN>;		#read next row
46        my $REL = Time::Piece->strptime($RELstr, "%Y-%m-%d %H:%M:%S")->strftime('%Y-%m-%d %H:%M:%S'); #convert string date into type Time
47        chomp $REL; 			#avoid \n on last field
48
49        print "Enter date of disposal(e.g., YYYY-MM-DD): ";
50        my $DISstr = <STDIN>;		#read next row
51        my $DIS = Time::Piece->strptime($DISstr, "%Y-%m-%d %H:%M:%S")->strftime('%Y-%m-%d %H:%M:%S'); #convert string date into type Time
52        chomp $DIS;			#avoid \n on last field
53
54        my $sth = $dbh->prepare("INSERT INTO COMPANY(VIN, PTS, RELEASE, DISPOSAL) VALUES(?,?,?,?)");
55        $sth->execute($VIN, $image, $REL, $DIS);
56	#The two code lines prepare the SQL statement, bind the image data to the statement and execute it.	
57	print "Sucessfully added car into db\n";
58}
59
60sub SELECT { 								#create a function
61	print "Enter date to look between that date\n";			
62	print "Enter date of release: ";
63	my $RELIstr = <STDIN>;						#read next row
64	my $RELI = Time::Piece->strptime($RELIstr, "%Y-%m-%d %H:%M:%S")->strftime('%Y-%m-%d %H:%M:%S'); #convert string date into type Time
65	print "Enter date of disposal: ";
66	my $DISPstr = <STDIN>;						#read next row
67	my $DISP = Time::Piece->strptime($DISPstr, "%Y-%m-%d %H:%M:%S")->strftime('%Y-%m-%d %H:%M:%S'); #convert string date into type Time
68	my $sth = qq(SELECT * from COMPANY where RELEASE BETWEEN '$RELI' AND '$DISP' or DISPOSAL BETWEEN '$RELI' AND '$DISP' OR RELEASE < '$RELI' AND DISPOSAL > '$DISP');
69	my $stm = $dbh->prepare( $sth );
70	my $rv = $stm->execute() or die $DBI::errstr;
71	#The three code lines prepare the SQL statement and execute it.
72	if($rv < 0) {
73   	print $DBI::errstr;
74	}
75
76	while(my @row = $stm->fetchrow_array) { 			# loop over all row that we fit with our date and print them 
77      		print "IMG = ". $row[1] . "\n";
78      		print "ID(VIN) = ". $row[0] . "\n";
79      		print "Data of release = ". $row[2] ."\n";
80      		print "Date of disposal =  ". $row[3] ."\n\n";
81	}
82}
83
84while(1) { 								#
85	print "Enter number:\n";
86	print "1 add new value to db\n";
87	print "2 select value in db\n";
88	print "3 exit\n";
89	my $name = <STDIN>;
90	chomp $name;
91	if ($name eq '1') {
92		ADD();
93	}
94	elsif($name eq '2') { 
95		SELECT();
96	}
97	elsif($name eq '3') {
98		last;
99	}
100	else {
101	print "you need write down only number";
102	}
103		
104}
105print "Operation done successfully\n";
106$dbh->disconnect();