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

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