noalyss Version-9
stock.class.php
Go to the documentation of this file.
1<?php
2
3/*
4 * This file is part of NOALYSS.
5 *
6 * NOALYSS is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 2 of the License, or
9 * (at your option) any later version.
10 *
11 * NOALYSS is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
15 *
16 * You should have received a copy of the GNU General Public License
17 * along with NOALYSS; if not, write to the Free Software
18 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
19 */
20
21// Copyright Author Dany De Bontridder danydb@aevalys.eu
22
23/**
24 * @file
25 * @brief manage the stocks
26 *
27 *
28 */
29
30/**
31 * @class Stock
32 * @brief manage the stocks
33 */
34require_once NOALYSS_INCLUDE.'/database/stock_sql.class.php';
35
36class Stock extends Stock_Sql
37{
38
39 /**
40 * show history of all the stock movement
41 * @param $p_array usually contains $_GET
42 */
43 function history($p_array)
44 {
45
47 $p_url = HtmlInput::get_to_string(array("gDossier", "ac", "wcard", "wdate_start", "wdate_end", "wrepo",
48 "wamount_start", "wamount_end", "wcode_stock", "wdirection"));
49
50 $tb = new Sort_Table();
51 $tb->add("Date", $p_url, " order by real_date asc", "order by real_date desc", "da", "dd");
52 $tb->add("Code Stock", $p_url, " order by sg_code asc", "order by sg_code desc", "sa", "sd");
53 $tb->add("Dépôt", $p_url, " order by r_name asc", "order by r_name desc", "ra", "rd");
54 $tb->add("Fiche", $p_url, " order by 2 asc", "order by 2 desc", "fa", "fd");
55 $tb->add("Commentaire", $p_url, " order by coalesce(sg_comment,jr_comment) asc", "order by coalesce(sg_comment,jr_comment) desc", "ca", "cd");
56 $tb->add("Montant", $p_url, " order by j_montant asc", "order by j_montant desc", "ja", "jd");
57 $tb->add("Quantité", $p_url, " order by sg_quantity asc", "order by sg_quantity desc", "qa", "qd");
58 $tb->add("IN/OUT", $p_url, " order by (case when sg_type='c' then 'OUT' when sg_type='c' then 'IN' end ) asc", "order by (case when sg_type='c' then 'OUT' when sg_type='c' then 'IN' end ) desc", "ta", "td");
59 $order = (isset($p_array['ord'])) ? $p_array['ord'] : 'da';
60
61 $sql.=$tb->get_sql_order($order);
62 $step = $_SESSION[SESSION_KEY.'g_pagesize'];
63 $page = (isset($_GET['offset'])) ? $_GET['page'] : 1;
64 $offset = (isset($_GET['offset'])) ? $_GET['offset'] : 0;
65
66 $res = $this->cn->exec_sql($sql);
67
69
70 $nav_bar = navigation_bar($offset, $max_row, 0, $page);
71
72 if ($step != -1)
73 $res = $this->cn->exec_sql($sql . " , sg_id asc limit " . $step . " offset " . $offset);
75
76 $this->search_box_button();
77 $this->search_box($p_array);
78 require_once NOALYSS_TEMPLATE.'/stock_histo.php';
81 }
82
84 {
85 echo '<form style="display:inline" method="GET" action="export.php">';
86 echo HtmlInput::get_to_hidden(array("gDossier", "wcard", "wdate_start", "wdate_end", "wrepo",
87 "wamount_start", "wamount_end", "wcode_stock", "wdirection"));
88 echo HtmlInput::hidden('act','CSV:StockHisto');
89 echo HtmlInput::submit('stockhisto','Export CSV');
90 echo '</form>';
91 }
93 {
94 echo '<form style="display:inline" method="GET" action="export.php">';
95 echo HtmlInput::get_to_hidden(array("gDossier", "state_exercice"));
96 echo HtmlInput::hidden('act','CSV:StockResmList');
97
98 echo HtmlInput::submit('stockresm','Export CSV');
99 echo '</form>';
100 }
102 {
103 $bt = HtmlInput::button("Recherche", "Recherche", ' onclick="$(\'histo_search_d\').show();"');
104 echo $bt;
105 }
106
108 {
109 // Declaration
110 global $g_user;
111 $wrepo = HtmlInput::select_stock($this->cn, "wrepo", 'R');
112 $wrepo->value[] = array('value' => -1, 'label' => 'Tous les dépôts');
113
114 $wdate_start = new IDate('wdate_start');
115 $wdate_end = new IDate('wdate_end');
116 $wamount_start = new INum('wamount_start');
117 $wamount_end = new INum('wamount_end');
118 $wcard = new ICard('wcard');
119 $wcode_stock = new ICard('wcode_stock');
120 $wdirection = new ISelect("wdirection");
121
122 // value
123 $wrepo->selected = HtmlInput::default_value("wrepo", -1, $p_array);
124
125 // Date start / end
126 $exercice = $g_user->get_exercice();
127 $periode = new Periode($this->cn);
128 list($periode_start, $periode_end) = $periode->get_limit($exercice);
129
130 $wdate_start->value = HtmlInput::default_value("wdate_start", $periode_start->first_day(), $p_array);
131 $wdate_end->value = HtmlInput::default_value("wdate_end", $periode_end->last_day(), $p_array);
132 //amounts
133 $wamount_start->value = HtmlInput::default_value("wamount_start", 0, $p_array);
134 $wamount_end->value = HtmlInput::default_value("wamount_end", 0, $p_array);
135
136 //Card
137 $wcard->extra = "all";
138 $wcard->set_attribute("typecard", "all");
139 $wcard->value = HtmlInput::default_value("wcard", "", $p_array);
140
141 //Card stock
142 $wcode_stock->extra = " [sql] fd_id=500000 ";
143 $wcode_stock->set_attribute("typecard", "[sql] fd_id=500000");
144 $wcode_stock->value = HtmlInput::default_value("wcard", "", $p_array);
145
146 // Repository
147 $wcode_stock->value = HtmlInput::default_value("wcode_stock", "", $p_array);
148
149 //Direction
150 $wdirection->value = array(
151 array('value' => "-1", 'label' => "Tout"),
152 array('value' => "c", 'label' => "OUT"),
153 array('value' => "d", 'label' => "IN")
154 );
155 $wdirection->selected = HtmlInput::default_value("wdirection", "-1", $p_array);
156
157 require_once NOALYSS_TEMPLATE.'/stock_histo_search.php';
158 }
159
161 {
162 global $cn,$g_user;
163 $profile=$g_user->get_profile();
164 $sql = "
165 select sg_id,
166 sg.f_id,
167 (select ad_value from fiche_Detail as fd1 where ad_id=1 and fd1.f_id=jx.f_id) as fname,
168 (select ad_value from fiche_Detail as fd1 where ad_id=23 and fd1.f_id=jx.f_id) as qcode,
169 sg_code,
170 coalesce(sg_comment,jr_comment) as ccomment,
171 sg_exercice,
172 r_name,
173 sg.r_id,
174 j_montant,
175 jr_date,
176 sg_quantity,
177 case when sg_type='c' then 'OUT' when sg_type='d' then 'IN' end as direction,
178 jr_internal,
179 jr_id,
180 coalesce(sg_date,jr_date) as real_date,
181 to_char(coalesce(sg_date,jr_date),'DD.MM.YY') as cdate
182 from stock_goods as sg
183 join stock_repository as sr on (sg.r_id=sr.r_id)
184 left join jrnx as jx on (sg.j_id=jx.j_id)
185 left join jrn as j on (j.jr_grpt_id=jx.j_grpt)
186 where
187 sg.r_id in (select r_id from profile_sec_repository where p_id = $profile)";
188 $and = " and ";
189 $clause = "";
190 if (isset($p_array['wdate_start']) && $p_array['wdate_start'] != '')
191 {
192 $clause = $and." to_date('" . sql_string($p_array['wdate_start']) . "','DD.MM.YYYY')<=coalesce(sg_date,jr_date) ";
193 }
194 if (isset($p_array['wdate_end']) && $p_array['wdate_end'] != '')
195 {
196 $clause.=$and . " to_date('" . sql_string($p_array['wdate_end']) . "','DD.MM.YYYY')>=coalesce(sg_date,jr_date) ";
197 }
198 if (isset($p_array['wamount_start']) && $p_array['wamount_start'] != '' && isNumber($p_array['wamount_start']) == 1
199 && $p_array['wamount_start'] != 0 )
200 {
201 $clause.=$and . " j_montant >= " . sql_string($p_array['wamount_start']);
202 }
203 if (isset($p_array['wamount_end'])
204 && $p_array['wamount_end'] != ''
205 && $p_array['wamount_end'] != 0
206 && isNumber($p_array['wamount_end']) == 1)
207 {
208 $clause.=$and . " j_montant <= " . sql_string($p_array['wamount_end']);
209 }
210 if (isset($p_array['wcard']) && $p_array['wcard'] != '')
211 {
212 $f = new Fiche($this->cn);
213 $f->get_by_qcode($p_array['wcard'], false);
214 if ($f->id != 0)
215 {
216 $clause.=$and . " sg.f_id = " . sql_string($f->id);
217 }
218 }
219 if (isset($p_array['wcode_stock']) && $p_array['wcode_stock'] != "")
220 {
221 $clause.=$and . " upper(sg_code) = upper('" . sql_string(trim($p_array['wcode_stock'])) . "')";
222 }
223 if (isset($p_array['wrepo']) && $p_array['wrepo'] != -1)
224 {
225 $clause.=$and . " sg.r_id = " . sql_string($p_array['wrepo']);
226 }
227 if (isset($p_array['wdirection']) && $p_array['wdirection'] != -1)
228 {
229 $clause.=$and . " sg.sg_type = '" . sql_string($p_array['wdirection']) . "'";
230 }
231
232 return $sql . $clause;
233 }
234
236 {
237 global $cn, $g_user;
239 // Build condition
240 $a_repository = $g_user->get_available_repository('R');
241 $a_code = $cn->get_array("select distinct sg_code from tmp_stockgood_detail where s_id=$1", array($tmp_id));
242 if (isset($p_array['present']))
243 {
244 $present = $p_array['present'];
245 }
246 else
247 {
248 $present = 'T';
249 }
250 if ($present == 'T')
251 {
252 require_once NOALYSS_TEMPLATE.'/stock_summary_table.php';
253 }
254 if ($present == 'L')
255 {
256 require_once NOALYSS_TEMPLATE.'/stock_summary_list.php';
258
259 }
261 }
263 {
264 global $cn,$g_user;
265 // exercice
266 if (isset ($p_array['state_exercice']))
267 {
268 $periode=new Periode($cn);
269 $date= format_date($p_array['state_exercice'], 'YYYY-MM-DD');
270 $periode->find_periode($date);
271
272 } else {
273 $periode_id=$g_user->get_periode();
275 }
276 $periode->load();
277 $tmp_id = $cn->get_next_seq("public.tmp_stockgood_s_id_seq");
278 $cn->exec_sql("delete from tmp_stockgood where s_date < now() - interval '2 days' ");
279 $cn->exec_sql("insert into tmp_stockgood(s_id) values ($1)", array($tmp_id));
280
281 // get all readable repository
282 $a_repository = $g_user->get_available_repository('R');
283
284 // From ACH : IN
285 $sql_repo_detail = "
286 insert into tmp_stockgood_detail(s_id,sg_code,s_qin,r_id,f_id)
287 select $tmp_id,trim(sg_code), coalesce(sum(sg_quantity),0) as qin,r_id,f_id
288 from stock_goods
289 where
290 sg_type='d'
291 and j_id is not null
292 and j_id in (select j_id from jrnx where j_tech_per in (select p_id from parm_periode where p_exercice=$2))
293 and r_id in (select r_id from profile_sec_repository where p_id=$1)
294 group by r_id,trim(sg_code),f_id
295 ";
296
297 // From VEN : out
298 $cn->exec_sql($sql_repo_detail, array($g_user->get_profile(), $periode->p_exercice));
299 $sql_repo_detail = "
300 insert into tmp_stockgood_detail(s_id,sg_code,s_qout,r_id,f_id)
301 select $tmp_id,trim(sg_code) , coalesce(sum(sg_quantity),0) as qout,r_id,f_id
302 from stock_goods as sg
303 where
304 sg_type='c'
305 and sg.j_id is not null
306 and sg.j_id in (select j_id from jrnx where j_tech_per in (select p_id from parm_periode where p_exercice=$2))
307 and sg.r_id in (select r_id from profile_sec_repository where p_id=$1)
308 group by r_id,trim(sg_code),f_id
309 ";
310 $cn->exec_sql($sql_repo_detail, array($g_user->get_profile(), $periode->p_exercice));
311
312 // From INV IN
313 $sql_repo_detail = "
314 insert into tmp_stockgood_detail(s_id,sg_code,s_qin,r_id,f_id)
315 select $tmp_id,trim(sg_code) , coalesce(sum(sg_quantity),0) as qin,r_id,f_id
316 from stock_goods as sg
317 where
318 sg_type='d'
319 and j_id is null
320 and sg_date >= ( select min(p_start) from parm_periode where p_exercice=$2)
321 and sg_date <= ( select max(p_end) from parm_periode where p_exercice=$2)
322 and sg.r_id in (select r_id from profile_sec_repository where p_id=$1)
323 group by r_id,trim(sg_code),f_id
324 ";
325 // From INV: OUT
326 $cn->exec_sql($sql_repo_detail, array($g_user->get_profile(), $periode->p_exercice));
327 $sql_repo_detail = "
328 insert into tmp_stockgood_detail(s_id,sg_code,s_qout,r_id,f_id)
329 select $tmp_id,trim(sg_code), coalesce(sum(sg_quantity),0) as qout,r_id,f_id
330 from stock_goods
331 where
332 sg_type='c'
333 and j_id is null
334 and r_id in (select r_id from profile_sec_repository where p_id=$1)
335 and sg_date >= ( select min(p_start) from parm_periode where p_exercice=$2)
336 and sg_date <= ( select max(p_end) from parm_periode where p_exercice=$2)
337 group by r_id,trim(sg_code),f_id
338 ";
339 $cn->exec_sql($sql_repo_detail, array($g_user->get_profile(), $periode->p_exercice));
340 return $tmp_id;
341 }
342
343
344}
345
346?>
format_date($p_date, $p_from_format='YYYY-MM-DD', $p_to_format='DD.MM.YYYY')
format the date, when taken from the database the format is MM-DD-YYYY
Definition: ac_common.php:852
isNumber($p_int)
Definition: ac_common.php:215
sql_string($p_string)
Fix the problem with the quote char for the database.
Definition: ac_common.php:511
global $g_user
if no group available , then stop
catch(Exception $e) $exercice
$periode_id
$input_from cn
Definition: balance.inc.php:66
$_GET['qcode']
static num_row($ret)
wrapper for the function pg_num_rows
define Class fiche and fiche def, those class are using class attribut. When adding or modifing new c...
Definition: fiche.class.php:38
static get_to_string($array, $start="?")
transform $_GET data to string
static select_stock($p_cn, $p_name, $p_mode)
Create an ISelect object containing the available repository for reading or writing.
static button($p_name, $p_value, $p_javascript="", $p_class="smallbutton")
static default_value($ind, $default, $array)
return default if the value if the value doesn't exist in the array
static print_window()
Javascript to print the current window.
static hidden($p_name, $p_value, $p_id="")
static get_to_hidden($array)
transform $_GET data to hidden
static submit($p_name, $p_value, $p_javascript="", $p_class="smallbutton")
Input HTML for the card show buttons, in the file, you have to add card.js How to use :
Html Input : Input a date format dd.mm.yyyy The property title should be set to indicate what it is e...
Definition: idate.class.php:34
This class handles only the numeric input, the input will call a javascript to change comma to period...
Definition: inum.class.php:42
Html Input , create a tag <SELECT> ... </SELECT> if readonly == true then display the label correspon...
For the periode tables parm_periode and jrn_periode.
Description of class_syn_sort_table.
ORM abstract of the table public.stock_repository.
manage the stocks
Definition: stock.class.php:37
create_query_histo($p_array)
history($p_array)
show history of all the stock movement
Definition: stock.class.php:43
summary($p_array)
export_stock_summary_list_form()
Definition: stock.class.php:92
export_stock_histo_form()
Definition: stock.class.php:83
build_tmp_table($p_array)
search_box_button()
search_box($p_array)
$periode_start
Definition: fiche.inc.php:50
$periode_end
Definition: fiche.inc.php:51
if($g_parameter->MY_STOCK=='N') if(isset( $_POST[ 'add_stock'])) if(isset($_POST['mod_stock'])) $tb
$p_url
navigation_bar($p_offset, $p_line, $p_size=0, $p_page=1, $p_javascript="")
Create a navigation_bar (pagesize)
Definition: user_common.php:76