Re: Отчет по фактическим сдачам (SQL, PHP)
Всем привет!
У нас на предприятии вот уже пол года используются самописные на PHP отчеты по данным из технолоджикса.
Производительность и гибкость встроенных средств ТКС (и АПИ) не позволяет получать такие отчеты.
Для работы отчетов используется Apache Server под Windows, PHP и доступ через ADO(библиотека adodb497.zip)
Как выбирать данные - смотрим в профайлере SQL Server'a.
Вот пример последнего универсального отчета по фактическим сдачам, в котором можно выбрать необходимые столбцы:
<?php $dtn=$_GET["dtn"]; $dtk=$_GET["dtk"]; if (($dtn=='')&($dtk=='')) { $dtn='01.01.2008'; $dtk='01.01.2008'; $nf01=='on'; $nf02=='on'; $nf03=='on'; $nf04=='on'; $nf05=='on'; $nf06=='on'; $nf07=='on'; $nf08=='on'; $nf09=='on'; $nf10=='on'; $nf11=='on'; $nf12=='on'; $nf13=='on'; $nf14=='on'; $nf15=='on'; $nf16=='on'; $nf17=='on'; $nf18=='on'; $nf19=='on'; $nf20=='on'; } else { $nf01=$_GET["nf01"]; $nf02=$_GET["nf02"]; $nf03=$_GET["nf03"]; $nf04=$_GET["nf04"]; $nf05=$_GET["nf05"]; $nf06=$_GET["nf06"]; $nf07=$_GET["nf07"]; $nf08=$_GET["nf08"]; $nf09=$_GET["nf09"]; $nf10=$_GET["nf10"]; $nf11=$_GET["nf11"]; $nf12=$_GET["nf12"]; $nf13=$_GET["nf13"]; $nf14=$_GET["nf14"]; $nf15=$_GET["nf15"]; $nf16=$_GET["nf16"]; $nf17=$_GET["nf17"]; $nf18=$_GET["nf18"]; $nf19=$_GET["nf19"]; $nf20=$_GET["nf20"]; $ng01=$_GET["ng01"]; $ng02=$_GET["ng02"]; $ng03=$_GET["ng03"]; $ng04=$_GET["ng04"]; $ng05=$_GET["ng05"]; $ng06=$_GET["ng06"]; $ng07=$_GET["ng07"]; $ng08=$_GET["ng08"]; $ng09=$_GET["ng09"]; $ng10=$_GET["ng10"]; $ng11=$_GET["ng11"]; $ng12=$_GET["ng12"]; $ng13=$_GET["ng13"]; $ng14=$_GET["ng14"]; $ng15=$_GET["ng15"]; $ng16=$_GET["ng16"]; $ng17=$_GET["ng17"]; $ng18=$_GET["ng18"]; $ng19=$_GET["ng19"]; $ng20=$_GET["ng20"]; if ($nf01=='') {$nf01=='off';}; if ($nf02=='') {$nf02=='off';}; if ($nf03=='') {$nf03=='off';}; if ($nf04=='') {$nf04=='off';}; if ($nf05=='') {$nf05=='off';}; if ($nf06=='') {$nf06=='off';}; if ($nf07=='') {$nf07=='off';}; if ($nf08=='') {$nf08=='off';}; if ($nf09=='') {$nf09=='off';}; if ($nf10=='') {$nf10=='off';}; if ($nf11=='') {$nf11=='off';}; if ($nf12=='') {$nf12=='off';}; if ($nf13=='') {$nf13=='off';}; if ($nf14=='') {$nf14=='off';}; if ($nf15=='') {$nf15=='off';}; if ($nf16=='') {$nf16=='off';}; if ($nf17=='') {$nf17=='off';}; if ($nf18=='') {$nf18=='off';}; if ($nf19=='') {$nf19=='off';}; if ($nf20=='') {$nf20=='off';}; if ($ng01=='') {$ng01=='off';}; if ($ng02=='') {$ng02=='off';}; if ($ng03=='') {$ng03=='off';}; if ($ng04=='') {$ng04=='off';}; if ($ng05=='') {$ng05=='off';}; if ($ng06=='') {$ng06=='off';}; if ($ng07=='') {$ng07=='off';}; if ($ng08=='') {$ng08=='off';}; if ($ng09=='') {$ng09=='off';}; if ($ng10=='') {$ng10=='off';}; if ($ng11=='') {$ng11=='off';}; if ($ng12=='') {$ng12=='off';}; if ($ng13=='') {$ng13=='off';}; if ($ng14=='') {$ng14=='off';}; if ($ng15=='') {$ng15=='off';}; if ($ng16=='') {$ng16=='off';}; if ($ng17=='') {$ng17=='off';}; if ($ng18=='') {$ng18=='off';}; if ($ng19=='') {$ng19=='off';}; if ($ng20=='') {$ng20=='off';}; }; ?> <form action="ots.all.form.php" method="get"> <b>ДатаН</b> <input type="text" name="dtn" value=" <?php print "$dtn" ?> "><br> <b>ДатаК</b> <input type="text" name="dtk" value=" <?php print "$dtk" ?> "><br> <table border="1" cellspacing="0" cellpadding="0"> <tr> <th>Поле</th><th>Вывод на экран</th><th>Группировка</th> </tr> <tr> <td>№ ПСП</td><td align="center"> <input type="checkbox" name="nf01" <?php if ($nf01=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng01" <?php if ($ng01=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Заказ</td><td align="center"> <input type="checkbox" name="nf02" <?php if ($nf02=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng02" <?php if ($ng02=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Контрагент</td><td align="center"> <input type="checkbox" name="nf03" <?php if ($nf03=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng03" <?php if ($ng03=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Номенклатура</td><td align="center"> <input type="checkbox" name="nf04" <?php if ($nf04=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng04" <?php if ($ng04=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Наименование</td><td align="center"> <input type="checkbox" name="nf05" <?php if ($nf05=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng05" <?php if ($ng05=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>№ операции</td><td align="center"> <input type="checkbox" name="nf06" <?php if ($nf06=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng06" <?php if ($ng06=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Обозн.</td><td align="center"> <input type="checkbox" name="nf07" <?php if ($nf07=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng07" <?php if ($ng07=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Наименование операции</td><td align="center"> <input type="checkbox" name="nf08" <?php if ($nf08=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng08" <?php if ($ng08=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Дата ФИ</td><td align="center"> <input type="checkbox" name="nf09" <?php if ($nf09=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng09" <?php if ($ng09=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Начало работ</td><td align="center"> <input type="checkbox" name="nf10" <?php if ($nf10=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng10" <?php if ($ng10=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Окончание работ</td><td align="center"> <input type="checkbox" name="nf11" <?php if ($nf11=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng11" <?php if ($ng11=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Цех</td><td align="center"> <input type="checkbox" name="nf13" <?php if ($nf13=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng13" <?php if ($ng13=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Номер цеха</td><td align="center"> <input type="checkbox" name="nf14" <?php if ($nf14=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng14" <?php if ($ng14=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Название</td><td align="center"> <input type="checkbox" name="nf15" <?php if ($nf15=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng15" <?php if ($ng15=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Наименование оборудования</td><td align="center"> <input type="checkbox" name="nf16" <?php if ($nf16=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng16" <?php if ($ng16=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Оборудование</td><td align="center"> <input type="checkbox" name="nf17" <?php if ($nf17=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng17" <?php if ($ng17=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Таб №</td><td align="center"> <input type="checkbox" name="nf18" <?php if ($nf18=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng18" <?php if ($ng18=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Фамилия И. О.</td><td align="center"> <input type="checkbox" name="nf19" <?php if ($nf19=='on') print('checked'); ?> value="on"></td><td align="center"><input type="checkbox" name="ng19" <?php if ($ng19=='on') print('checked'); ?> value="on"></td> </tr> <tr> <td>Время по сотруднику</td><td align="center"> <input type="checkbox" name="nf20" <?php if ($nf20=='on') print('checked'); ?> value="on"></td><td align="center"> <select name="ng20"> <option value="sum">SUM</option> <option value="min">MIN</option> <option value="max">MAX</option> <option value="avg">AVG</option> </select> </td> </tr> </table> <input type="submit" value="Сформировать"> </form> <?php include('adodb/adodb.inc.php'); # load code common to ADOdb $dtnn=$dtn[6].$dtn[7].$dtn[8].$dtn[9].'-'.$dtn[3].$dtn[4].'-'.$dtn[0].$dtn[1]; $dtkk=$dtk[6].$dtk[7].$dtk[8].$dtk[9].'-'.$dtk[3].$dtk[4].'-'.$dtk[0].$dtk[1].' 23:59:59'; print '<h1></h1>'; print ''; print '<table border="1" cellspacing="0" cellpadding="0">'; print '<tr>'; print '<th>№ п/п</th>'; if ($nf01=='on') print '<th>№ ПСП</th>'; if ($nf02=='on') print '<th>Заказ</th>'; if ($nf03=='on') print '<th>Контрагент</th>'; if ($nf04=='on') print '<th>Номенклатура</th>'; if ($nf05=='on') print '<th>Наименование</th>'; if ($nf06=='on') print '<th>№ операции</th>'; if ($nf07=='on') print '<th>Обозн.</th>'; if ($nf08=='on') print '<th>Наименование операции</th>'; if ($nf09=='on') print '<th>Дата ФИ</th>'; if ($nf10=='on') print '<th>Начало работ</th>'; if ($nf11=='on') print '<th>Окончание работ</th>'; //if ($nf12=='on') print '<th></th>'; if ($nf13=='on') print '<th>Цех</th>'; if ($nf14=='on') print '<th>Номер цеха</th>'; if ($nf15=='on') print '<th>Название</th>'; if ($nf16=='on') print '<th>Наименование оборудования</th>'; if ($nf17=='on') print '<th>Оборудование</th>'; if ($nf18=='on') print '<th>Таб №</th>'; if ($nf19=='on') print '<th>Фамилия И. О.</th>'; if ($nf20=='on') print '<th>Время по сотруднику</th>'; print '</tr>'; $n=1; $db =& ADONewConnection('odbc_mssql'); $dsn = "Driver={SQL Server};Server=srv4;Database=volgaero;"; $db->Connect($dsn,'sa','sapwd'); $query_from.=" from n_ordfactwm /*таблица - время по сотрудникам*/ LEFT JOIN WORKMANS /*таблица - сотрудники*/ ON n_ordfactwm.WORKMAN_ID=WORKMANS.WORKMAN_ID LEFT JOIN n_ordfact ON n_ordfact.N_ORDFACT_ID = n_ordfactwm.N_ORDFACT_ID LEFt JOIN N_ORDTREETP ON N_ORDTREETP.n_ordtrtp_id=N_ORDFACT.n_ordtrtp_id and N_ORDTREETP.n_ord_id=N_ORDFACT.n_ord_id LEFT JOIN N_ORDTREE ON N_ORDTREE.n_ordtree_id=N_ORDTREETP.n_ordtr_id and N_ORDTREE.n_ord_id=N_ORDTREETP.n_ord_id LEFT JOIN NMK TRNMK /*Таблица номенклатуры*/ ON TRNMK.NMK_ID=N_ORDTREE.NMK_ID LEFT JOIN NMK Opernmk /*Таблица операции*/ ON (N_ORDTREETP.N_ORDTRTP_OPER = Opernmk.NMK_ID) LEFT JOIN CEHA TPCEHA ON (N_ORDTREETP.N_ORDTRTP_CEHA = TPCEHA.CEHA_ID) LEFT JOIN N_ORDS ON (N_ORDS.N_ORD_ID = n_ordfactwm.N_ORD_ID) LEFT JOIN norder_n_ords on norder_n_ords.n_ord_id=N_ords.n_ord_id left join norders on norders.norder_id=norder_n_ords.norder_id LEFT JOIN CONTRAGENT Contragent ON (NORDERS.CONTRAGENT_ID = Contragent.CONTRAGENT_ID) LEFT JOIN N_ORDSNMK M_TRNMK on M_TRNMK.nmk_id=N_ORDTREE.nmk_id LEFT JOIN MESURIMENT TR_MESUR on TR_MESUR.mesur_id=M_TRNMK.mesur_id LEFT JOIN blnmk_serialnum FACT_BLS ON FACT_BLS.serialnum_id=N_ORDFACT.n_ordfact_snum LEFT JOIN blnmk_part FACT_BLP ON FACT_BLP.blnmk_part_id=N_ORDFACT.n_ordfact_blp LEFT JOIN NMK Obornmk ON (N_ORDTREETP.N_ORDTRTP_OBOR = Obornmk.NMK_ID) where (n_ordfact.N_ORDFACT_DATE>='$dtnn') and (n_ordfact.N_ORDFACT_DATE<='$dtkk') "; $ngr=0; $query_gr=''; if ($ng01=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="N_ORDS.N_ORD_NOTE"; $ngr++; }; if ($ng02=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="norders.norder_name"; $ngr++; }; if ($ng03=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="Contragent.CONTRAGENT_NAME"; $ngr++; }; if ($ng04=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="TRNMK.NMK_NOTE"; $ngr++; }; if ($ng05=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="TRNMK.NMK_NAME"; $ngr++; }; if ($ng06=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="N_ORDTREETP.N_ORDTRTP_OPERPOS"; $ngr++; }; if ($ng07=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="Opernmk.NMK_NOTE"; $ngr++; }; if ($ng08=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="Opernmk.NMK_NAME"; $ngr++; }; if ($ng09=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="n_ordfact.N_ORDFACT_DATE"; $ngr++; }; if ($ng10=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="n_ordfact.N_ORDFACT_START"; $ngr++; }; if ($ng11=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="n_ordfact.N_ORDFACT_END"; $ngr++; }; if ($ng13=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="TPCEHA.CEHA_NUMBER"; $ngr++; }; if ($ng14=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="TPCEHA.CEHA_SECTOR"; $ngr++; }; if ($ng15=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="TPCEHA.CEHA_NAME"; $ngr++; }; if ($ng16=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="Obornmk.NMK_NOTE"; $ngr++; }; if ($ng17=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="Obornmk.NMK_NAME"; $ngr++; }; if ($ng18=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="WORKMANS.WORKMAN_TABEL"; $ngr++; }; if ($ng19=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="WORKMANS.WORKMAN_FIO"; $ngr++; }; if ($ng20=='on') { if ($ngr!=0) $query_gr.=","; else $query_gr.="group by "; $query_gr.="n_ordfactwm.N_ORDFACTWM_TR"; $ngr++; }; $query_select="select "; $npar=0; if ($nf01=='on') { if ($npar!=0) $query_select.=","; $query_select.="N_ORDS.N_ORD_NOTE"; $npar++; }; if ($nf02=='on') { if ($npar!=0) $query_select.=","; $query_select.="norders.norder_name"; $npar++; }; if ($nf03=='on') { if ($npar!=0) $query_select.=","; $query_select.="Contragent.CONTRAGENT_NAME"; $npar++; }; if ($nf04=='on') { if ($npar!=0) $query_select.=","; $query_select.="TRNMK.NMK_NOTE"; $npar++; }; if ($nf05=='on') { if ($npar!=0) $query_select.=","; $query_select.="TRNMK.NMK_NAME"; $npar++; }; if ($nf06=='on') { if ($npar!=0) $query_select.=","; $query_select.="N_ORDTREETP.N_ORDTRTP_OPERPOS"; $npar++; }; if ($nf07=='on') { if ($npar!=0) $query_select.=","; $query_select.="Opernmk.NMK_NOTE OPER_NOTE"; $npar++; }; if ($nf08=='on') { if ($npar!=0) $query_select.=","; $query_select.="Opernmk.NMK_NAME OPER_NAME"; $npar++; }; if ($nf09=='on') { if ($npar!=0) $query_select.=","; $query_select.="n_ordfact.N_ORDFACT_DATE"; $tips[$npar]='D'; $npar++; }; if ($nf10=='on') { if ($npar!=0) $query_select.=","; $query_select.="n_ordfact.N_ORDFACT_START"; $tips[$npar]='T'; $npar++; }; if ($nf11=='on') { if ($npar!=0) $query_select.=","; $query_select.="n_ordfact.N_ORDFACT_END"; $tips[$npar]='T'; $npar++; }; if ($nf13=='on') { if ($npar!=0) $query_select.=","; $query_select.="TPCEHA.CEHA_NUMBER TPCEHA_NUM"; $npar++; }; if ($nf14=='on') { if ($npar!=0) $query_select.=","; $query_select.="TPCEHA.CEHA_SECTOR TPCEHA_SEC"; $npar++; }; if ($nf15=='on') { if ($npar!=0) $query_select.=","; $query_select.="TPCEHA.CEHA_NAME TPCEHA_NAME"; $npar++; }; if ($nf16=='on') { if ($npar!=0) $query_select.=","; $query_select.="Obornmk.NMK_NOTE"; $npar++; }; if ($nf17=='on') { if ($npar!=0) $query_select.=","; $query_select.="Obornmk.NMK_NAME"; $npar++; }; if ($nf18=='on') { if ($npar!=0) $query_select.=","; $query_select.="WORKMANS.WORKMAN_TABEL"; $npar++; }; if ($nf19=='on') { if ($npar!=0) $query_select.=","; $query_select.="WORKMANS.WORKMAN_FIO"; $npar++; }; if ($nf20=='on') { if ($npar!=0) $query_select.=","; if ($ngr!=0) $query_select.=($ng20."("); $query_select.="(n_ordfactwm.N_ORDFACTWM_TR)"; if ($ngr!=0) $query_select.=")"; $tips[$npar]='N'; $npar++; }; // print "$query"; $rs = $db->Execute($query_select.$query_from.$query_gr); while (!$rs->EOF) { print '<tr>'; print '<td align="center">'.($n++).' </td>'; for($i=1;$i<=$npar;$i++) { $ts=$rs->fields[$i-1]; if ($tips[$i-1]=='N') $ts=str_replace('.',',',round($ts,2)); if ($tips[$i-1]=='D') $ts=$ts[8].$ts[9].'.'.$ts[5].$ts[6].'.'.$ts[0].$ts[1].$ts[2].$ts[3]; if ($tips[$i-1]=='T') $ts=substr($ts, 11, 8); print '<td>'.$ts.'</td>'; } print '</tr>'; $rs->MoveNext(); }; print '</table>'; ?>
Всем привет!