source: trunk/www/db_po/insert_po.php@ 19145

Last change on this file since 19145 was 18802, checked in by Daniela Dorner, 8 years ago
adapted to change of column fValidFrom in DB
  • Property svn:executable set to *
File size: 7.8 KB
Line 
1<?php
2{
3 ini_set("display_errors", "On");
4 ini_set("mysql.trace_mode", "On");
5
6 // init
7 //
8 // values which cannot be empty
9 if (empty($_GET["fUpdCase"]))
10 {
11 echo "Please specify what kind of change you want to do.<br>";
12 return;
13 }
14 if (empty($_GET["fIndex"]))
15 {
16 echo "Please give an index.<br>";
17 return;
18 }
19 if (empty($_GET["fProgram"]))
20 {
21 echo "Please select a program.<br>";
22 return;
23 }
24 if (empty($_GET["fKey1"]))
25 {
26 echo "Please select a key1.<br>";
27 return;
28 }
29 if (empty($_GET["fValue"]) && $_GET["fValue"]!=0)
30 {
31 echo "Please insert a value.<br>";
32 return;
33 }
34 if (empty($_GET["fDescription"]))
35 {
36 echo "Please insert a description.<br>";
37 return;
38 }
39 if (empty($_GET["fType"]))
40 {
41 echo "type empty";
42 return;
43 }
44 // values which may be empty
45 if (empty($_GET["fKey2"]))
46 $_GET["fKey2"]="";
47 if (empty($_GET["fOriginalIndex"]))
48 $_GET["fOriginalIndex"]="";
49 if (empty($_GET["fMin"]) && $_GET["fMin"]!=0)
50 $_GET["fMin"]="";
51 if (empty($_GET["fMax"]) && $_GET["fMax"]!=0)
52 $_GET["fMax"]="";
53
54 if (empty($_SERVER['PHP_AUTH_USER']))
55 {
56 // be careful, this output is evaluated by UpdateRow() in po.js
57 echo "user empty";
58 return;
59 }
60 include("db.php");
61 $db_id = mysqli_connect($host, $user, $pw, $db);
62 if (mysqli_connect_errno())
63 {
64 printf("mysql_connect returned the following error: %s\n", mysqli_connect_error());
65 die("");
66 }
67
68 //query old entry
69 $query1="SELECT fValue, fProgram, fKey1, fDescription, fType, fMin, fMax, fKey2 from ProgramOption WHERE fIndex='".$_GET["fIndex"]."';";
70 //compare new values to old values
71 //insert only, if at least one values is new
72 // update counter only if value is updated
73 if (!$result1=mysqli_query($db_id, $query1))
74 {
75 echo "Error sending query: ".$query1;
76 return;
77 }
78 $row1 = mysqli_fetch_row($result1);
79
80 if ($_GET["fUpdCase"]!=4
81 && strcmp($row1[0],$_GET["fValue"])==0
82 && strcmp($row1[1],$_GET["fProgram"])==0
83 && strcmp($row1[2],$_GET["fKey1"])==0
84 && strcmp($row1[7],$_GET["fKey2"])==0
85 && strcmp($row1[3],$_GET["fDescription"])==0
86 && strcmp($row1[4],$_GET["fType"])==0
87 //&& strcmp($row1[5],$_GET["fMin"])==0
88 //&& strcmp($row1[6],$_GET["fMax"])==0
89 )
90 {
91 // be careful, this output is evaluated by UpdateRow() in po.js
92 echo "no change";
93 mysqli_free_result($result1);
94 return;
95 }
96 else
97 mysqli_free_result($result1);
98
99 //check if key alread exists
100 if (empty($_GET["fKey2"]))
101 $query2="SELECT Count(*) from ProgramOption WHERE fKey1='".$_GET["fKey1"]."' AND ISNULL(fKey2)";
102 else
103 $query2="SELECT Count(*) from ProgramOption WHERE fKey1='".$_GET["fKey1"]."' AND fKey2='.".$_GET["fKey2"]."'";
104 $query2.=" AND fCounter=(Select Max(fCounter) from History)";
105 if (!$result2=mysqli_query($db_id, $query2))
106 {
107 echo "Error sending query: ".$query2;
108 return;
109 }
110 $row2 = mysqli_fetch_row($result2);
111 if (($_GET["fUpdCase"]==1 || $_GET["fUpdCase"]==5) && $row2[0]>0)
112 {
113 // be careful, this output is evaluated by UpdateRow() in po.js
114 echo "key exists";
115 mysqli_free_result($result2);
116 return;
117 }
118 else
119 mysqli_free_result($result2);
120
121 //check if program alread exists
122 $query3="SELECT Count(*) from ProgramOption WHERE fProgram='".$_GET["fProgram"]."'";
123 $query3.=" AND fCounter=(Select Max(fCounter) from History)";
124 if (!$result3=mysqli_query($db_id, $query3))
125 {
126 echo "Error sending query: ".$query3;
127 return;
128 }
129 $row3 = mysqli_fetch_row($result3);
130 if ($_GET["fUpdCase"]==5 && $row3[0]>0)
131 {
132 // be careful, this output is evaluated by UpdateRow() in po.js
133 echo "prog exists";
134 mysqli_free_result($result3);
135 return;
136 }
137 else
138 mysqli_free_result($result3);
139
140 //note for fType:
141 //SHOW COLUMNS FROM ProgramOption LIKE 'fType';
142
143 //new entry in table ProgramOption
144 //update only in case 3 if value is not changed
145 if ($_GET["fUpdCase"]==6)
146 $query0="DELETE FROM ProgramOption ";
147 else
148 {
149 if (strcmp($row1[0],$_GET["fValue"])==0 && $_GET["fUpdCase"]==3)
150 $query0="UPDATE ";
151 else
152 $query0="INSERT ";
153 $query0.=" ProgramOption SET ";
154 // set program, key, description, type
155 $query0.="fProgram='".$_GET["fProgram"]."'";
156 if (!empty($_GET["fOriginalIndex"]))
157 $query0.=", fOriginalIndex='".$_GET["fOriginalIndex"]."'";
158 if (!empty($_GET["fMin"]) || $_GET["fMin"]==0)
159 $query0.=", fMin='".$_GET["fMin"]."'";
160 if (!empty($_GET["fMax"]) || $_GET["fMax"]==0)
161 $query0.=", fMax='".$_GET["fMax"]."'";
162 $query0.=", fUser='".$_SERVER['PHP_AUTH_USER']."'";
163 $query0.=", fKey1='".$_GET["fKey1"]."'";
164 if (!empty($_GET["fKey2"]))
165 $query0.=", fKey2='.".$_GET["fKey2"]."'";
166 $query0.=", fDescription='".$_GET["fDescription"]."'";
167 $query0.=", fType='".$_GET["fType"]."'";
168 // set value
169 // change value in case is was
170 if ($_GET["fUpdCase"]==2 //inserted
171 || ($_GET["fUpdCase"]==3 && strcmp($row1[0],$_GET["fValue"])!=0))// changed
172 $query0.=", fValue='".$_GET["fValue"]."'";
173 // set to NULL in case it was
174 if ($_GET["fUpdCase"]==1 || $_GET["fUpdCase"]==5 //new insert
175 || $_GET["fUpdCase"]==4) //delete
176 $query0.=", fValue=NULL";
177 //increase counter in case value was
178 if ($_GET["fUpdCase"]==2//created
179 || ($_GET["fUpdCase"]==3 && strcmp($row1[0],$_GET["fValue"])!=0)//changed
180 || $_GET["fUpdCase"]==4)//deleted
181 {
182 $query0.=", fValidFrom=Now() ";
183 $query0.=", fCounter= (Select Max(fCounter) from History)+1 ";
184 }
185 else//else set current counter
186 $query0.=", fCounter= (Select Max(fCounter) from History) ";
187 }
188 //do update only for current row
189 if ((strcmp($row1[0],$_GET["fValue"])==0 && $_GET["fUpdCase"]==3) || $_GET["fUpdCase"]==6)
190 $query0.=" WHERE fIndex=".$_GET["fIndex"];
191 if ($_GET["fUpdCase"]==6)
192 $query0.=" AND fValidFrom='1970-01-01 00:00:00'";
193 $query0.=";";
194
195 //update counter in case value was
196 if ($_GET["fUpdCase"]==2 //created
197 || ($_GET["fUpdCase"]==3 && strcmp($row1[0],$_GET["fValue"])!=0)//changed
198 || $_GET["fUpdCase"]==4)//deleted
199 {
200 //update counter for other valid entries in ProgramOption
201 $query0.=" UPDATE ProgramOption SET ";
202 $query0.=" fCounter= (Select Max(fCounter) from History)+1 ";
203 $query0.=" WHERE fCounter= (Select Max(fCounter) from History) ";
204 if ($_GET["fUpdCase"]==3 || $_GET["fUpdCase"]==4)
205 $query0.=" AND NOT fIndex=".$_GET["fIndex"];
206 $query0.=" AND ((NOT ISNULL(fValue) AND NOT fValidFrom='1970-01-01 00:00:00') ";//normal entries, but not deleted ones
207 $query0.=" OR (ISNULL(fValue) AND fValidFrom='1970-01-01 00:00:00')); ";//new entries
208
209 //insert new counter to history
210 $query0.=" INSERT History SET ";
211 $query0.=" fCounter= (Select Max(fCounter) from ProgramOption) ";
212 $query0.=", fValidFrom=Now() ";
213 }
214
215 $result0=mysqli_multi_query($db_id, $query0);
216 if ($result0)
217 {
218 echo "Query \"" . $query0 . "\" was successful.";
219 return 0;
220 }
221 else
222 {
223 echo "Query \"" . $query0 . "\" was not successful.";
224 return 2;
225 }
226
227 mysqli_free_result($result0);
228 mysqli_close($db_id);
229
230 ini_set("display_errors", "Off");
231 ini_set("mysql.trace_mode", "Off");
232}
233?>
Note: See TracBrowser for help on using the repository browser.