アップロードファイルを指定してください
"; $intnodata = 1;$errcount++; } elseif(strtolower(substr($_FILES['userfile']['name'],-3)) <> "csv"){ //ファイル無し $errmsg = "
ＣＳＶファイルではありません。ＣＳＶファイルを指定してください。
ファイル名：".$_FILES['userfile']['name'].""; $intnodata = 1;$errcount++; } else { //ファイルの読み込み $fp=fopen($filename,"r"); if (!$fp){ print "
アップロードファイルが見つかりません
";
$errcount++;
} else {
$y=0;
while (($arraydata = fgetcsv_reg($fp, 0, ',','"')) !== FALSE) {
// while (($arraydata = fgetcsv($fp, 0, ',','"')) !== FALSE) {
// $arraydata = explode(",",Eucsjis($buffer));
//３カラムまでをチェックする
if (($y==0) and (Eucsjis($arraydata[0])=="注文番号") and (Eucsjis($arraydata[1])=="会員ID") and (Eucsjis($arraydata[2])=="要望等") and (Eucsjis($arraydata[3])=="お名前(姓)")) {
$errorcheck = "OK"; //正しいアップロードデータ
} elseif($y==0) {
$errorcheck = "NG"; //間違いアップロードデータ
}
//echo $errorcheck;
//受注数が0以上
if (($errorcheck == "OK") and ($arraydata[47] > 0) and ($y>0) and (($arraydata[35] == "1") or ($arraydata[35] == "6"))) { //$arraydata[34] =7は決済処理中 3キャンセル 1台引き ６カード決済済み
if(trim($arraydata[66]) == "") { //届け日日付0000-00-00対策
$strdata66 = "NULL";
} else {
$strdata66 = "'".$arraydata[66]."'";
}
if(trim($arraydata[21]) == "") { //誕生日日付0000-00-00対策
$strdata21 = "NULL";
} else {
$strdata21 = "'".$arraydata[21]."'";
}
/* if(strlen($arraydata[62].$arraydata[63]) > 32) { //文字列オーバー対策
if(strstr($arraydata[62]," ") == false){
} else {
}
$straddress2 = $arraydata[62];
} else {
}*/
$strsql ="INSERT INTO ".$company."orderdata2
(orderid_ec,
customerid_ec,
customer_master_name,
name1,
name2,
kana1,
kana2,
memo,
email,
totalamount,
taio_kubun,
orderdatetime,
paykubunname,
pay_kubun,
shohincd_ec,
shohincd,
shohinmei,
orderqty,
orderprice,
customername,
customernamekana,
sex,
birthday,
occupation,
tel,
customerid_temp,
postcode,
kenid,
address2,
address3,
address4,
deliverdate,
delivertime_ec,
delivertime,
operatedate,
cardstatus,
media,
shipping,
discount,
ad_kubun,
`creater`,
`createdate`
)
values
(
'".$arraydata[0]."',
'".$arraydata[1]."',
'".$arraydata[3].$arraydata[4]."',
'".$arraydata[3]."',
'".$arraydata[4]."',
'".$arraydata[5]."',
'".$arraydata[6]."',
'".$arraydata[2]."',
'".$arraydata[7]."',
'".$arraydata[31]."',
'".$arraydata[35]."',
'".$arraydata[36]."',
'".$arraydata[42]."',
'".GetList($codedata91,$arraydata[42])."',
'".$arraydata[43]."',
'".GetList($shohindata,$arraydata[43])."',
'".$arraydata[46]." ".$arraydata[48]."',
'".$arraydata[47]."',
'".$arraydata[49]."',
'".$arraydata[50].$arraydata[51]."',
'".$arraydata[52].$arraydata[53]."',
'".$arraydata[20]."',
".$strdata21.",
'".$arraydata[22]."',
'".$arraydata[54]."-".$arraydata[55]."-".$arraydata[56]."',
'".(int)$arraydata[8].$arraydata[9].$arraydata[10]."',
'".$arraydata[60]."-".$arraydata[61]."',
'".$arraydata[62]."',
'".$arraydata[63]."',
'".$arraydata[64]."',
'".$arraydata[65]."',
".$strdata66.",
'".$arraydata[67]."',
'".GetList($codedata90,$arraydata[67])."',
CURDATE(),
'".$arraydata[35]."',
'".$arraydata[69]."',
'".$arraydata[25]."',
'-".$arraydata[24]."',
'".$arraydata[71]."',
'".$userdata["tid"]."'
,now());";
$strsql = Eucsjis($strsql);
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
Testecho( $strsql);
$errcode =mysql_errno();
$intorderdata += mysql_affected_rows();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
if($errcode==1062) {
Testecho ("既に登録されています\n");
$errcount++;
$errmsg .= $y."行目 修正登録不可 ".Eucsjis($buffer)."
\n";
} elseif($errcode==1064) {
Testecho ("どこか、不正なデータがあり、登録に失敗しました。");
$errcount++;
$errmsg .= $y."行目 不正データ ".Eucsjis($buffer)."
\n";
} elseif($errcode==0) {
$okcount++;
} else {
$errcount++;
$errmsg .= $y."行目 不明エラー ".Eucsjis($buffer)."
\n";
}
Testecho ($y.$strsql." ERR=".$errcode."
") ; //debug
} elseif($errorcheck == "NG") {
$errcount++;
$errmsg .= "項目レイアウトが一致しないため登録できませんでした
\n";
} elseif($y == 0) {
$errcount++;
$errmsg .= "0行目はタイトルデータです
\n";
} elseif((int)$arraydata[47] == 0) {
$errcount++;
$errmsg .= $y."行目 受注数が0又は、空白のため登録できないデータがありました
\n";
} elseif (($arraydata[35] <> "1") AND ($arraydata[35] <> "6")) { //$arraydata[34] =7は決済処理中 3キャンセル
$errcount++;
$errmsg .= $y."行目 カード未決済またはキャンセルのデータです。
\n";
} elseif (strlen($buffer)==0) {
//空行
$errcount++;
$errmsg .= $y."行目 空行
\n";
} else {
if ($y==1) {
//1行目タイトル
$errmsg .= $y."行目 タイトル行で登録無し ".Eucsjis($buffer)."
\n";
} else {
//本部コードと商品コードが数値でない
$errcount++;
$errmsg .= $y."行目 不正データ：キー値誤り ".Eucsjis($buffer)."
\n";
}
}
$y++;
}
//退避注文データ
$strsql = "Truncate table `nagase_ordermain_2006`";
$result=@mysql_query($strsql,$my_con);
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$strsql = "INSERT INTO `nagase_ordermain_2006` SELECT `ordermainid`, `customerid`, `err_check`, `err_memo`, `customerid_ec`, `updatedate`, `memo`, `centerid`, `orderid2`, `orderid3`, `orderid4`, `checkedflg` FROM nagase_ordermain WHERE createdate > DATE_ADD(CURDATE(),INTERVAL -1 DAY)";
$result=@mysql_query($strsql,$my_con);
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
//取込済みのチェック
$strsql = "UPDATE nagase_ordermain_2006 as om,nagase_orderdata2 as od SET wcheck =2 WHERE om.orderid2 = od.orderid_ec";
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
/*
SELECT
sum(case when wcheck =0 then 1 else 0 end) as code1,
sum(case when wcheck =2 then 1 else 0 end) as code2
FROM (
Select wcheck,orderid_ec FROM `nagase_orderdata2` group by orderid_ec
) as b
*/
$datacheck = dbselect("sum(case when wcheck =0 then 1 else 0 end) as code1,sum(case when wcheck =2 then 1 else 0 end) as code2","(Select wcheck,orderid_ec FROM `nagase_orderdata2` group by orderid_ec) as b",-1,-1,-1);
$errmsg .= "合計 ".($datacheck[0][0] + $datacheck[0][1])."件の受注データを読み込みました。内訳（取込済 ".$datacheck[0][1]."件 新規 ".$datacheck[0][0]."件）
\n";
if($_POST[up2] == "100") { //実データの投入
//未処理の取込データをordermainに入れる orderkubun＝1
$strsql = "SELECT `orderid_ec`, `customerid_ec`, `memo`, `email`, `pay_kubun`, `customername`, `customernamekana`,concat(`customername`,' 様'), `tel`, `postcode`, `kenid`, `address2`, `address3`,`address4`, `deliverdate`, `delivertime`, `centerid`, `shozokuid`, `orderdatetime`, `creater`, now(),'1',media,ad_kubun ,2 FROM `nagase_orderdata2` WHERE wcheck = 0 and cardstatus <> 7 group by orderid_ec ORDER by orderid_ec";
$strsql = "INSERT INTO nagase_ordermain( `orderid2`, `customerid_ec`, `memo`, `email`, `pay_kubun`, `name`, `namekana`,`receipt_name`, `tel`, `postcode`, `address1`, `address2`, `address3`,`address4`, `deliverdate`, `delivertime`, `centerid`, `shozokuid`, `inputdatetime`, `creater`, `createdate`, `orderkubun`,media,ad_kubun,input_media) ".$strsql;
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$errmsg .= mysql_affected_rows()."件の受注データを取込みました。
\n";
//退避注文データ
$strsql = "Truncate table `nagase_ordermain_2006`";
$result=@mysql_query($strsql,$my_con);
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$strsql = "INSERT INTO `nagase_ordermain_2006` SELECT `ordermainid`, `customerid`, `err_check`, `err_memo`, `customerid_ec`, `updatedate`, `memo`, `centerid`, `orderid2`, `orderid3`, `orderid4`, `checkedflg` FROM nagase_ordermain WHERE createdate > DATE_ADD(CURDATE(),INTERVAL -1 DAY)";
$result=@mysql_query($strsql,$my_con);
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
//未処理の取込データにordermainidを反映する
$strsql = "UPDATE nagase_ordermain_2006 as om,nagase_orderdata2 as od SET od.wcheck =1,od.ordermainid = om.ordermainid WHERE od.wcheck = 0 and om.orderid2 = od.orderid_ec and om.updatedate > DATE_ADD(CURDATE(),INTERVAL -2 DAY)";
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
//未処理の取込データをordersheetに入れる
$strsql = "SELECT `ordermainid`, `shohincd`, `orderqty`,truncate(`orderprice` / `orderqty`,0) as n, `orderprice`, `creater`, `createdate` FROM `nagase_orderdata2` WHERE wcheck = 1 ORDER by ordermainid,nagase_orderdataid";
$strsql = "INSERT INTO nagase_ordersheet( `ordermainid`, `shohincd`, `orderqty`,`orderprice`, `price_total`, `creater`, `createdate`) ".$strsql;
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$errmsg .= mysql_affected_rows()."件の受注明細データを取込みました。
\n";
//未処理の送料データをordersheetに入れる
$strsql = "SELECT `ordermainid`, 'ZH930', 1,shipping as n, shipping as `orderprice`, `creater`, `createdate` FROM `nagase_orderdata2` WHERE wcheck = 1 and shipping > 0 group by orderid_ec ORDER by orderid_ec";
$strsql = "INSERT INTO nagase_ordersheet( `ordermainid`, `shohincd`, `orderqty`,`orderprice`, `price_total`, `creater`, `createdate`) ".$strsql;
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$errmsg .= mysql_affected_rows()."件の送料明細データを取込みました。
\n";
//未処理の値引きデータをordersheetに入れる
$strsql = "SELECT `ordermainid`, 'Z9900', 1,discount as n, discount as `orderprice`, `creater`, `createdate` FROM `nagase_orderdata2` WHERE wcheck = 1 and discount < 0 group by orderid_ec ORDER by orderid_ec";
$strsql = "INSERT INTO nagase_ordersheet( `ordermainid`, `shohincd`, `orderqty`,`orderprice`, `price_total`, `creater`, `createdate`) ".$strsql;
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$errmsg .= mysql_affected_rows()."件の値引き明細データを取込みました。
\n";
//未処理の取込データにorderidを反映する
$strsql = "UPDATE nagase_ordersheet as sh,nagase_orderdata2 as od SET od.wcheck =1,od.orderid = sh.orderid WHERE od.wcheck = 1 and sh.ordermainid = od.ordermainid and sh.shohincd = od.shohincd and sh.updatedate > DATE_ADD(CURDATE(),INTERVAL -2 DAY)";
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
} else {
$errmsg .= "データ取込は 0件です。取込みは更新するボックスにチェックしてください。
\n";
}
fclose ($fp);
//取込済みのチェック
$strsql = "UPDATE nagase_ordermain as om,customer as c SET om.customerid = c.customerid WHERE om.customerid_ec = c.customerid_ec and om.createdate > DATE_ADD(CURDATE(),INTERVAL -1 DAY) and c.customerid_ec is not null AND om.ordermainid > 412795";
//***** $result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
Testecho ("ROWS=".mysql_affected_rows().$strsql);
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$strsql = "UPDATE nagase_orderdata2 as o,customer as c SET customer_check=1 WHERE o.customerid_ec > 0";
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
Testecho ("ROWS=".mysql_affected_rows().$strsql);
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$strsql = "UPDATE nagase_orderdata2 as o,customer as c SET customer_check=2 WHERE c.customerid = o.customerid_temp and o.customerid_ec = 0";
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
Testecho ("ROWS=".mysql_affected_rows().$strsql);
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$strsql ="INSERT INTO `customer` (`customerid`,
`custname`,
`custname1`,
`custname2`,
`custkana1`,
`custkana2`,
`sex`,
`birthday`,
`postcode`,
`address1`,
`address2`,
`address3`,
`address4`,
`tel`,
`mail`,
`validflg`,
`occupation`,
`buy_first`,
`buy_last`,
`buy_times`,
`name_search`,
`tel_search`,
`updater`,
`updatedate`)
SELECT `customerid_temp`,
`customername`,
name1,
name2,
kana1,
kana2,
`sex`,
`birthday`,
`postcode`,
`kenid`,
`address2`,
`address3`,
`address4`,
`tel`,
`email`,
1,
`occupation`,
`orderdatetime`,
`orderdatetime`,
1,
concat(`customername`,kana1,kana2),
concat('0',`customerid_temp`),
`creater`,
`createdate`
FROM nagase_orderdata2 WHERE customerid_ec = 0 AND customer_check=0 GROUP BY customerid_temp";
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
Testecho ("ROWS=".mysql_affected_rows().$errcode.$strsql);
$strsql = "update `nagase_orderdata2` as od,`nagase_ordermain` as om set om.customerid = od.customerid_temp where om.orderid2 = od.orderid_ec";
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
Testecho ("ROWS=".mysql_affected_rows().$strsql);
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
//こちらは電話番号変更に対応できない $strsql = "update `nagase_ordermain` SET customerid = floor(replace(tel,'-','')) where orderid2 is null ";
$strsql = "update `nagase_ordermain` as om,customer as c SET om.customerid = c.customerid where om.operatedate is null and om.customerid is null and om.tel = c.tel ";
$result=@mysql_query($strsql,$my_con);//".trim($arraydata[27])."
$errcode =mysql_errno();
Testecho ("ROWS=".mysql_affected_rows().$strsql);
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
}
}
//サービスは０円 DW用
$strsql = "update `nagase_ordersheet` set `orderprice`=0,`price_total`=0 where shohincd in ('SV0113','SV0108') and createdate>curdate()";
$result=mysql_query($strsql,$my_con);
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$strsqlall .= "ROW=".mysql_affected_rows()." ".$strsql."\n";
$strsqljoin = dbselectin("SELECT code from `nagase_code` where cname like 'DW%'"," media in "); //DW
//メール便はサンプル
$strsql = "update nagase_ordermain as om,nagase_ordersheet as os set orderkubun=3,
checkuserflg=1,
pay_kubun =10,
nagase_flg = 1,
deliverdate = null,
om.checkuser = om.creater
where om.createdate> DATE_ADD(CURDATE(),INTERVAL -1 DAY) and
om.ordermainid = os.ordermainid and
shohincd in ('SV0113','SV0108') and
$strsqljoin
and om.memo like '%メール便%' AND om.ordermainid > 412795";
$result=mysql_query($strsql,$my_con);
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$strsqlall .= "ROW=".mysql_affected_rows()." ".$strsql."\n";
//オイル球はメディアタイプ2 DW
$strsql5 = "select customerid from v_oil";
$strsql = "update customer as c set media_type = 2 where c.buy_times = 0 ".dbselectin($strsql5," and customerid IN ");
$result=mysql_query($strsql,$my_con);
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
$strsqlall .= "ROW=".mysql_affected_rows()." ".$strsql."\n";
} elseif(($_POST[cancel]=="yes") and ($_POST[canceldate]<>"")) {
//ordersheetにしるしをつける orderkubun=1 =EC受注
$strsql = "UPDATE nagase_ordersheet as sheet,nagase_ordermain as main SET sheet.updater = 'cancel' where main.orderkubun = 1 and main.checkedflg<3 and main.updatedate >= '".$_POST[canceldate]."' and main.centerid ='c101' and main.ordermainid = sheet.ordermainid";
$result4 = @mysql_query($strsql,$my_con);
Testecho ("ROWS=".mysql_affected_rows().$strsql);
$errmsg .= "FLGを ".mysql_affected_rows()."件更新しました
";
$strsql = "DELETE FROM nagase_ordermain where orderkubun = 1 and checkedflg<3 and updatedate >= '".$_POST[canceldate]."' and centerid ='c101'";
$result4 = @mysql_query($strsql,$my_con);
$row4 = mysql_affected_rows();
$errcode =mysql_errno();
Testecho ("ROWS=".mysql_affected_rows().$strsql);
$errmsg .= "受注データ $row4 件削除しました。
";
$strsql = "DELETE FROM nagase_ordersheet where updatedate >= '".$_POST[canceldate]."' and updater ='cancel'";
$result4 = @mysql_query($strsql,$my_con);
$row4 = mysql_affected_rows();
$errcode =mysql_errno();
Testecho ("ROWS=".mysql_affected_rows().$strsql);
$errmsg .= "受注明細データ $row4 件削除しました。
";
$okcount++;
} else {
Testecho ("なし");
}
$whereselectin = "SELECT `customerid` FROM `nagasedb2`.nagase_ordermain as om inner join `nagasedb2`.nagase_ordersheet using(ordermainid) where om.createdate >= curdate() and shohincd ='OK0550'";
$strsqlwhereshozoku = dbselectin($whereselectin," customerid in ");
$strsql ="update `nagasedb2`.customer set media_type= 2 where".$strsqlwhereshozoku;
$result=@mysql_query($strsql,$my_con);
mysql_sql_log($strsql,mysql_affected_rows(),$_SERVER['SCRIPT_NAME'].":".__LINE__);
?>
| "; echo $errmsg; echo " |