"); $y++; } //引当OKはフラグを3に引き上げる $strsql ="UPDATE `".$company."ordersheet` SET kariflg=3 WHERE karihikiate = 0 and karihikiate2 = 0 and kariflg in(1,2)"; Testecho ("LINE ".__LINE__." ".$y." ".$strsql); //debug $result=@mysql_query($strsql,$my_con); mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); $strsqlkeppina ="SELECT sheet.shohincd FROM `".$company."ordermain` as main,`".$company."ordersheet` as sheet,`".$company."shohinmaster` AS sm WHERE main.ordermainid=sheet.ordermainid and sm.ryutu_type = 1 and sm.shohincd = sheet.shohincd and sheet.kariflg in(1,2) and $strsqlwhere GROUP BY shohincd"; Testecho ("LINE ".__LINE__." ".$y." ".$strsqlkeppina); //debug $strsqlkeppin = dbselectin($strsqlkeppina," and sheet.shohincd in "); Testecho ("LINE ".__LINE__." ".$y." ".$strsqlkeppin); //debug //欠品が出た商品は出荷データにデータを入れる $strsql ="INSERT INTO `".$company."shukka` (orderid,shozokuid,gyotaiid,shohincd,centerid,shukkaqty,orderqty,operatedate,shukkadate,deliverdate, loccode,picktimes,lotzan,zanzaiko) SELECT sheet.orderid,main.shozokuid,main.gyotaiid,sheet.shohincd,main.centerid,0,karihikiate,main.operatedate,main.deliverdate,main.deliverdate ,'入荷予定無し',$_POST[picktimes],0,0 FROM `".$company."ordermain` as main,`".$company."ordersheet` as sheet WHERE main.ordermainid=sheet.ordermainid AND karihikiate>0 AND $strsqlwhere $strsqlkeppin $strsqldailyexecute2";//karihikiateは引き当てられなかった数//orderqty - karihikiateは引き当てられた数 Testecho ("xxxxxxxxxLINE ".__LINE__." ".$y." ".$strsql); //debug $result=@mysql_query($strsql,$my_con); mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); //仮引当NGは、欠品対応テーブルに書き込み //対象：欠品が出た商品を注文した全ての注文データ //INSERT INTO 欠品T　SELECT 注文データ WHERE 作業日、欠品が出た商品 //欠品が出た商品=SELECT 商品 FROM 注文 kariflg=2 GROUP BY 商品 $strsql ="INSERT INTO `".$company."keppin` (orderid,shozokuid,gyotaiid,shohincd,centerid,orderqty,shukkaqty,keppinqty,operatedate,deliverdate,picktimes) SELECT sheet.orderid,main.shozokuid,main.gyotaiid,sheet.shohincd,main.centerid,sheet.orderqty,sheet.orderqty - sheet.karihikiate,sheet.karihikiate,main.operatedate,main.deliverdate, $_POST[picktimes] FROM `".$company."ordermain` as main,`".$company."ordersheet` as sheet WHERE main.ordermainid=sheet.ordermainid AND $strsqlwhere .$strsqldailyexecute2 $strsqlkeppin"; Testecho ("LINE ".__LINE__." ".$y." ".$strsql); //debug $result=@mysql_query($strsql,$my_con); mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); //入荷予定を書き込む $result=@mysql_query($strsqlkeppina,$my_con); //欠品商品データ while ($data = mysql_fetch_array($result)) { $strsql= "SELECT shohincd,sum(comeinqty) as qty,comeindate,comeinflg FROM `".$company."comeindata` as cd WHERE cd.comeindate >= ".$excutedate." and cd.comeindate <= ".$excutedate2." and cd.centerid='".$centerid."' and cd.shohincd = '".$data["shohincd"]."' and comeinqty > 0 GROUP BY cd.shohincd,cd.comeindate,comeinflg order by comeindate"; $result2 = @mysql_query($strsql,$my_con); //入荷予定データ $comeindata = "入荷予定
"; while ($data2 = mysql_fetch_array($result2)) { if($data2[comeinflg] > 0) { $flg = "[済]
"; } else { $flg = "[予定]
"; } $comeindata .= $data2["comeindate"]." [".$data2["qty"]."] ".$flg; } $strsql= "UPDATE `".$company."shukka` SET loccode='".$comeindata."' WHERE gyotaiid = '".$_POST[gyotai]."' and shohincd = '".$data["shohincd"]."' and centerid = '".$centerid."' and shukkaqty = 0 and operatedate = ".$excutedate.""; Testecho ("LINE ".__LINE__." ".$y." ".$strsql); //debug //入荷予定があるものは書き換える if($comeindata <> "入荷予定
") { $result3=@mysql_query($strsql,$my_con); mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); } } //出荷データにバッチ回数をセットする 受注データの所属店と所属マスタのバッチ回数を取得する /*$strsql= "SELECT shozokuid,deliverdate,picktimes FROM `".$company."ordermain` as main INNER JOIN `".$company."shozokumaster` as sho USING(shozokuid) WHERE operatedate=$excutedate GROUP BY main.shozokuid"; $result=@mysql_query($strsql,$my_con); //在庫データ Testecho ("LINE ".__LINE__." ".$y." ".$strsql); //debug mysql_sql_log2($strsql,$_SERVER['SCRIPT_NAME'].":".__LINE__); while ($data = mysql_fetch_array($result)) { $strsql= "UPDATE `".$company."shukka` SET picktimes='".$data["picktimes"]."' WHERE shozokuid='".$data["shozokuid"]."' AND operatedate=$excutedate"; Testecho ("LINE ".__LINE__." ".$y." ".$strsql); //debug $result2=@mysql_query($strsql,$my_con); mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); } */ //ステータスデータに引当件数と欠品件数をセットする $data= dbselect("count(shukkaid)","`".$company."shukka`","operatedate = ".$excutedate." and centerid='".$centerid."' and gyotaiid='".$_POST[gyotai]."' and picktimes='".$_POST[picktimes]."'",-1,-1); $data2= dbselect("SUM(a.b)","(SELECT count(distinct(shohincd)) AS b FROM `".$company."keppin` WHERE operatedate =".$excutedate." and centerid='".$centerid."' and gyotaiid='".$_POST[gyotai]."' and picktimes='".$_POST[picktimes]."' GROUP BY shohincd) as a",-1,-1,-1); if((int)$data[0][0]==0) { $status = 5; //引当件数無しは出荷実績対応済みとして更新 } elseif((int)$data2[0][0]==0) { $status = 4; //欠品無しは欠品対応済みとして更新 } else { $status = 3; //欠品有りは仮引当済みとして更新 } $strsql= "UPDATE `".$company."status` SET hikiateqty='".(int)$data[0][0]."',keppinqty='".(int)$data2[0][0]."',keppinfixqty=0,status=".$status." WHERE statusid='".$_POST[id]."'"; Testecho ("LINE ".__LINE__." ".$y." ".$strsql); //debug $result=@mysql_query($strsql,$my_con); mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); Testecho ("仮引当がステータスデータに実施されました。".$errcode." ".$strsql."\n
"); #debug $strsql = "UPDATE `".$company."status` SET status=4 where keppinqty=keppinfixqty AND status=3 AND statusid='".$_POST[id]."'"; $result=mysql_query($strsql,$my_con); Testecho ($y." $errcode ".$strsql); #debug mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); //出荷データに納品先IDをつける $strsql = "UPDATE `".$company."shukka` as shu, `".$company."shozokumaster` as sho SET `shu`.`deliveryid` = `sho`.`deliveryid`, `shu`.`route` = `sho`.`route` WHERE `shu`.`shozokuid` = `sho`.`shozokuid` and `shu`.`deliveryid` is null"; $result=mysql_query($strsql,$my_con); Testecho ($y." $errcode ".$strsql); #debug mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); //出荷実績データを取り消し $strsql="DELETE FROM `".$company."shukkafix` WHERE operatedate = ".$excutedate." and ".$strsqlwhere2; Testecho ("LINE ".__LINE__." ".$y." ".$strsql); //debug $result=@mysql_query($strsql,$my_con); mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); //出荷実績データを作成する(フラグを1=未確定で作成する） $strsql = "INSERT INTO `".$company."shukkafix` (`shukkaid`, `orderid`, `shozokuid`, `shohincd`, `centerid`, `loccode`, `gyotaiid`, `lotnum`, `stockdate`, `shomi`, `shukkaqty`, `orderqty`,`picktimes`, `operatedate`, `shukkadate`,`deliverdate`,`memo`,`deliveryid`, `creater`, `createdate`,`price_piece`,`price_total`) SELECT `shukkaid`, `orderid`, `shozokuid`, `shohincd`, `centerid`, `loccode`, `gyotaiid`, `lotnum`, `stockdate`, `shomi`, shukkaqty,orderqty,`picktimes`, `operatedate`, `shukkadate`,`deliverdate`,`memo`, `deliveryid`, '".$userdata["tid"]."',now(),`price_piece`,`price_total` FROM `".$company."shukka` WHERE operatedate = ".$excutedate." and centerid='".$centerid."' and gyotaiid='".$_POST[gyotai]."' and picktimes='".$_POST[picktimes]."'"; $result=mysql_query($strsql,$my_con); Testecho ($y." $errcode ".$strsql); #debug mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); //直送注文データ作成 $strsql = "INSERT into `".$company."orderdirect` (orderid,shozokuid,shohincd,supplyerid,gyotaiid,orderqty,shukkaqty,operatedate) SELECT sheet.orderid,main.shozokuid,sheet.shohincd,makercode,main.gyotaiid,sheet.orderqty,sheet.orderqty,operatedate FROM `".$company."ordermain` AS main INNER JOIN `".$company."ordersheet` AS sheet USING(ordermainid), `".$company."shohinmaster` AS sm WHERE main.operatedate =".$excutedate." and sm.shohincd = sheet.shohincd and orderid_flg <= 10 and sm.ryutu_type = 2 GROUP BY shohincd,shozokuid"; //$result=@mysql_query($strsql,$my_con); //Testecho ("直送型追加されました。".$errcode." ".$strsql."\n
"); #debug //受注明細FLG更新 $strsql = "UPDATE `".$company."ordermain` as main ,`".$company."ordersheet` as sheet,`".$company."shohinmaster` AS sm SET sheet.orderid_flg = 11 WHERE main.ordermainid = sheet.ordermainid and sm.shohincd = sheet.shohincd and main.gyotaiid='".$_POST[gyotai]."' and main.centerid='".$centerid."' and sheet.orderid_flg <10 and sm.ryutu_type = 1 and main.operatedate <=".$excutedate ; $result=@mysql_query($strsql,$my_con); mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); Testecho ("作業中ステータスに受注明細データに実施されました。".$errcode." ROWS=".mysql_affected_rows()." ".$strsql."\n
"); #debug //受注明細FLG更新 $strsql = "UPDATE `".$company."ordermain` as main ,`".$company."ordersheet` as sheet,`".$company."shohinmaster` AS sm SET sheet.orderid_flg = 31 WHERE main.ordermainid = sheet.ordermainid and sm.shohincd = sheet.shohincd and main.gyotaiid='".$_POST[gyotai]."' and main.centerid='".$centerid."' and sm.ryutu_type = 2 and main.operatedate <=".$excutedate ; $result=@mysql_query($strsql,$my_con); mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__); Testecho ("直送依頼済が受注明細データに実施されました。".$errcode." ROWS=".mysql_affected_rows()." ".$strsql."\n
"); #debug ?>