原始数据

deal_code common '交易号'

shop_id common 商店ID

ddlx common '订单类型'

drlb common 导入类别

京东原始数据 -> project/config/yssj.conf.php

------------------------------------------------------

deal_code common '交易号'

ddlx common '订单类型'

popttdd common 京东妥投订单
popttdd => array('lsh','deal_code','order_type','ywlx','fylx','price','spbh','spje','sl','sjyh','bz')

pophk common 京东妥投订单
pophk => array('deal_code','time','ywdh','order_type','ywlx','fylx','sl','spje','price','spbh','spmc','sjyh','kdl','bz')

不跨月->原:UPDATE jingdong_yssj SET is_repeat='2' WHERE deal_code !='' AND ddlx='ttdd'
AND (drlb='' OR ((drlb='popttdd' OR drlb='pophk') AND price!=0)) AND deal_code
IN('1023987328','1028841957','1038527668','1073177903','1074728903','1077372644','1077529370','1087047116','1095715073','1263494817','1263717078','1264556459','1266371037','1266428611','1267233924','1290611522','1292578251','1294630473','1295576542','1296184616','1296842630','1297573367','1297853933','1298030228','1298270310','1298771382','1298825284','1299046199','1299741897','1300181939','1300258730','1300916966','1301065759','1301229403','1301357957','1301467670','1301474358','1301560490','1309641984','1309647072','1310372737','1311888446','1312178468','1312463119','1314368891','1314681712','1315667953','1315820413','1318919616','1318984693','1320228283','1332900845','1338330588','1339106208','1339233106','1347752987','1349141892','1350054009','1355159679','1355213731','1355272845','1355839045','1357382516','1357446632','1357698911','1357718671','1358168360','1358630709','1358670605','1358793500','1358936660','1359658068','1359818884','1360177689','1360207937','1360210167','1360787837','1361171743','1361214406','1361226727','1361240307','1361245939','1361333840','1361380205','1361510523','1361514682','1361531579','1361564607','1361573222','1361575233','1361592992','1361864892','1362408224','1362474573','1362495186','1362502591','1362513816','1362546180','1362555476','1362582048','1362583423','1362611566','1362640710','1362684302','1362797580','1362895886','1362902709','1362989198','1363007627','1363063130','1363073080','1363078323','1363149502','1363178495','1363200471','1363240397','1363246844','1363282857','1363300698','1363323647','1363350974','1363352613','1363491690','1363512447','1363533748','1363554036','1363565412','1363569570','1363587785','1363606626','1363614156','1363621011','1363629261','1363660149','1363692671','1363737109','1363741353','1363745545','1363766390','1363769098','1363834498','1363846951','1363847625','1363854363','1363871511','1363877223','1363896095','1363900340','1364022034','1364047400','1364133554','1364282201','1364884182','1364902883','1364909450','1364936715','1365036360','1365048449','1365104392','1365154294','1365171973','1365223567','1365246176','1365326646','1365364359','1365390526','1365465062','1365474838','1365659302','1365735879','1365815404','1365815465','1365944257','1366114699','1366257342','1366273706','1366288011','1366432973','1366548688','1366685944','1366689938','1366811796','1366955380','1366975013','1367079080','1367148917','1367264812','1367323915','1367324703','1367366301','1367564239','1367615700','1367644461','1367804418','1367807304','1367828587','1368858075','1369044031','1369072759','1369120311','1369133430','1369155768','1369284879','1369320760','1369384212','1369417648','1369457468','1369552613','1369599487','1369653705','1369711524','1369754388','1369788472','1369894363','1369895487','1370041717','1370354328','1370425735','1370429372','1370495019','1370544278','1370557219','1370567446','1370568603','1370586976','1370616340','1370647721','1370765640','1370834629','1370868003','1370869713','1371099674','1371242743','1371281135','1371307319','1371307632','1371566497','1371566780','1371626161','1371655579','1371745811','1371755493','1371806635','1371831847','1371836208','1371921000','1371936298','1371958508','1372052950','1372076859','1372236048','1372267286','1372289534','1372337971','1372338869','1372398841','1372437888','1372441731','1372464165','1372481569','1373063656','1373418431','1373502075','1376024888','1376585355','1376613159','1376699749','1376750473','1376787866','1377058285','1377219005','1377550331','1378140780','1378158269','1378316022','1378529156','1379302686','1380335900','1380337437','1380370527','1380512628','1381044103','1381047290','1381220462','1381240545','1381373364','1381458069','1381495911','1381604544','1381633015','1381720589','1381760215','1382333190','1382392677','1383650531','1383964688','1384030024','1384169686','1384336878','1384883735','1385209441','1385221480','1385388115','1385963726','1386390757','1387744251','1388089334','1388103847','1388369847','1388384044','1389110523','1389781719','1389932091','1390182859','1390507919','1390707042','1390986111','1391292004','1391378215','1391410728','1391559883','1391749277','1391849384','1391854060','1391956390','1391964672','1391995475','1392479126','1392532262','1392594728','1392604022','1392614727','1392688196','1392712258','1392736571','1392757407','1392769087','1392799779','1392822542','1392839132','1392874613','1392897129','1393026325','1393115893','1393118798','1393171448','1393202178','1393272662','1393291749','1393329614','1393386072','1393389253','1393394966','1393431964','1393601193','1393887894','1393925338','1393932697','1394008323','1394229039','1394265550','1394371459','1394423133','1394453994','1394507786','1394551958','1394764008','1394789270','1394855827','1394858923','1394864104','1394880739','1394881846','1394909211','1394970803','1394979364','1395087794','1395256791','1395277288','1395357221','1395400180','1395460674','1395472433','1395477524','1396027396','1396064603','1396187987','1396204118','1396493351','1396532476','1396609179','1396614383','1396650304','1396705068','1397614322','1397734251','1397827903','1398083034','1398329453','1398411014','1398628086','1398704332','1398949322','1399039581','1399094876','1399114130','1399353308','1399575269','1400032655','1400227726','1400420477','1402111639','1403326568','1403430754','1403448942','1403554053','1403575333','1403667142','1404326114','1404488382','1404821810','1405592641','1405738934','1405977355','1406015618','1406108592','1406125240','1406350469','1406690745','1406739326','1407486782','1407619541','1407633586','1409980891','1411179655','1411358831','1411500959','1412210095','1412779995','1413223513','1413250429','1414080205','1414685376','1414777525','1414828330','1414841189','1414846187','1414874693','1414919273','1415062654','1415615672','1415728976','1415929791','1416792422','1417449292','1417612490','1417742564','1418340457','1418484033','1418742158','1419427452','1419591941','1419828955','1419852658','1420017677','1420071315','1420474793','1420773367','1420887955','1420902316','1420914404','1420914959','1420919586','1420927064','1420966367','1421259542','1421306449','1421345742','1421402797','1421414312','1421438105','1421541990','1421600403','1421819556','1421949678','1422136723','1422146907','1422169020','1422297089','1422397801','1422438290','1423085608','1423247584','1423335181','1423410771','1423469575','1423498741','1423549169','1423806446','1423959724','1424034819','1424055670','1424368959','1424500574','1424576771','1424772754','1424859350','1424870617','1424889950','1424925412','1424927445','1424983057','1425040913','1425044275','1425081332','1425259004','1425313117','1425383881','1425389620','1425431650','1425467298','1425476015','1425482072','1425514619','1425597838','1425726848','1425727410','1425817815','1425908233','1426145883','1426147835','1426195058','1426237369','1426304619','1426499503','1426538875','1426550722','1426614011','1426645788','1426690196','1426728555','1426760496','1426796626','1426809921','1426951178','1427139385','1427695327','1428006359','1428017137','1428093398','1428258783','1428319568','1428337213','1428570996','1428574775','1428753074','1428761439','1428806755','1428852140','1428881128','1428931215','1428934883','1429031886','1429147976','1429170572','1429198898','1429257613','1429364895','1429515020','1429548438','1429775776','1429784804','1429835927','1429946285','1429986862','1430002523','1430002855','1430024431','1430174965','1430218746','1430230130','1430271709','1430279719','1430286057','1430333779','1430358497','1430569607','1430596787','1430851000','1431003324','1431028012','1431310607','1431382081','1431525838','1431609200','1431619163','1431817666','1431945516','1432792479','1432893123','1432914240','1433105194','1433185072','1433229297','1433516040','1433598338','1433664865','1433672156','1434172225','1434296491','1434314712','1434356240','1434429473','1434430242','1434637823','1435055803','1435568904','1435783966','1436168349','1436186562','1436807979','1436908770','1437114122','1437259352','1437336375','1437367220','1437499335','1437785514','1438511399','1439116635','1439437589','1439755519','1440393093','1440687043','1440873222','1440893983','1441144497','1441535884','1441580531','1441588107','1441787904','1442201225','1442415041','1442934313','1443037270','1443495308','1443794030','1444871188','1445339967','1446314038','1446480996','1446866352','1447726622','1448412059','1449777353','1450455030');

现:???
-----------------------------------------------------------------------------------------------------

tfdd common 京东退货订单
ttdd => array('stime','time','deal_code','amount','sjcxje','yhje','dpjqje','dpdqje','price','yj','yjsje')

poptkdd_other common 京东妥投订单
poptkdd_other => array('lsh','deal_code','order_type','ywlx','fylx','fyje','spbh','spje','sl','sjyh','bz')

pophk common 京东妥投订单
pophk => array('deal_code','time','ywdh','order_type','ywlx','fylx','sl','spje','price','spbh','spmc','sjyh','kdl','bz')

tkje common 退款金额

is_repeat common 是否重复

跨月-->原:UPDATE jingdong_yssj SET is_repeat='1' WHERE deal_code !='' AND ddlx='ttdd'
AND (drlb='' OR ((drlb='popttdd' OR drlb='pophk') AND price!=0)) AND id
IN('1439747','1439709','1439869','1440111','1440067','1439965','1440023','1440015','1440233','1443323','1443455','1443907','1431293','1431327','1431519','1432095','1433683','1432841','1432945','1433103','1433189','1433235','1433309','1433363','1433403','1433811','1433815','1433587','1433763','1433197','1433867','1433921','1433937','1433955','1433967','1431247','1433975','1443433','1432343','1434005','1434031','1434081','1434095','1434107','1432363','1434313','1434383','1433509','1434669','1434653','1434819','1434985','1435389','1435417','1435405','1443495','1433359','1436133','1443575','1443599','1443593','1434265','1443603','1443623','1443669','1443675','1443659','1436981','1437081','1431007','1436987','1431077','1443779','1437209','1437225','1432709','1437315','1437419','1431657','1432743','1431303','1437475','1443527','1443705','1443627','1437479','1443293','1430891','1443925','1443755','1437551','1443607','1437647','1437685','1443369','1431917','1437679','1432055','1431169','1437671','1443713','1430869','1431703','1431989','1431491','1431601','1443351','1443373','1431739','1432457','1443467','1437629','1430993','1443305','1431759','1443335','1433045','1437637','1432685','1443839','1443397','1443749','1437663','1431581','1443733','1432137','1437911','1432213','1443857','1437905','1431185','1431001','1443743','1431973','1443819','1431283','1443759','1431735','1432075','1436063','1437983','1437919','1443915','1431981','1431309','1430873','1443391','1437807','1435069','1430947','1431331','1437873','1437915','1430863','1430877','1431779','1430859','1431275','1443809','1443867','1443445','1438003','1431233','1430955','1435491','1431107','1437993','1431117','1438031','1431889','1432421','1433163','1443885','1438383','1432261','1433223','1438129','1443613','1434741','1443419','1431239','1431433','1431269','1431851','1431589','1432069','1431615','1431503','1430983','1430833','1431467','1432479','1438341','1443423','1432727','1431541','1431365','1438325','1434343','1438517','1432941','1432197','1432279','1431121','1430989','1443853','1430915','1430937','1438425','1431415','1436321','1432023','1438513','1431997','1438487','1431139','1431389','1431507','1438581','1432487','1432257','1438595','1438599','1443873','1432877','1431085','1432813','1431475','1432767','1432243','1431135','1443825','1443937','1438605','1432807','1432883','1433717','1431461','1431451','1432275','1432383','1438687','1431819','1431481','1431755','1438735','1432545','1431443','1438771','1432801','1431949','1432007','1432633','1435191','1431425','1438709','1433153','1433149','1438751','1438741','1432061','1432129','1434271','1432309','1438931','1433035','1438963','1433051','1432039','1439003','1433071','1431867','1432305','1432527','1432163','1433989','1433525','1432983','1439459','1432291','1433227','1432595','1432237','1432619','1432613','1432643','1432623','1432603','1432155','1432149','1432141','1432145','1432395','1432391','1432995','1432493','1434281','1432977','1432539','1432717','1434365','1432779','1440685','1432671','1432737','1440999','1437659','1433169','1441067','1436941','1432757','1441237','1432567','1433341','1433391','1441477','1435303','1433425','1436243','1441857','1435767','1441637','1441713','1432913','1434747','1433245','1434169','1433183','1433557','1441915','1435543','1434161','1433383','1433007','1433387','1433421','1433477','1433179','1434931','1433409','1441975','1433481','1434275','1436327','1434429','1434947','1441951','1434847','1435097','1434321','1433773','1434151','1433747','1434883','1434237','1435213','1442245','1435083','1436365','1435077','1433583','1434791','1435223','1435109','1439171','1442387','1433533','1434955','1433313','1433291','1434481','1442381','1433733','1435463','1433333','1442357','1436271','1442405','1434713','1434509','1442479','1434927','1434913','1434213','1435567','1442523','1435137','1434861','1435141','1433597','1434725','1435683','1435423','1435969','1435911','1435229','1435261','1435453','1435237','1435481','1442935','1443039','1435273','1435735','1434975','1443275','1443059','1439125','1440859','1435119','1434721','1443043','1436113','1436117','1436121','1436125','1436129','1435651','1435131','1435145','1437085','1435871','1435197','1436143','1436147','1436153','1436161','1436277','1435955','1439223','1437125','1436919','1437075','1435929','1436293','1435675','1439665','1435983','1436181','1437187','1438293','1437361','1436177','1436043','1436201','1436195','1437717','1436205','1436209','1437173','1437457','1439177','1437409','1436925','1437009','1437595','1437221','1437155','1437255','1437297','1438497','1437745','1437133','1437269','1436951','1437277','1437543','1439213','1437415','1443167','1437423','1437427','1438223','1437441','1437943','1438193','1439219','1437741','1438215','1438805','1437957','1441111','1438701','1438019','1438795','1438401','1438175','1439017','1439501','1437727','1439143','1440575','1439013','1437769','1439233','1437431','1439435','1437435','1438107','1438531','1438887','1439299','1440661','1443111','1438973','1439395','1440777','1437765','1438471','1438987','1438369','1438317','1440805','1438577','1439333','1440747','1438199','1438903','1439489','1439701','1439281','1440541','1438999','1438995','1438623','1437773','1438219','1439415','1438811','1440643','1439693','1438723','1439113','1439595','1438307','1441685','1441347','1440477','1438869','1438657','1439135','1439103','1442795','1439131','1438653','1439053','1440919','1439639','1439237','1438789','1440427','1439661','1439085','1439527','1441269','1441275','1439653','1439573','1441075','1441143','1438681','1439401','1440841','1439645','1438615','1441673','1441297','1439465','1438697','1442787','1441305','1442865','1440441','1440679','1440465','1439515','1442897','1438673','1440913','1439065','1439569','1441657','1438799','1439475','1441327','1440613','1440813','1440549','1439563','1442087','1440837','1441411','1439631','1441025','1440455','1441031','1440929','1441035','1441091','1441853','1441249','1439605','1441429','1439545','1441609','1439353','1439363','1441201','1443187','1440767','1441575','1441227','1441369','1441019','1442777','1440801','1439677','1441425','1441125','1440955','1440791','1441541','1441537','1441135','1441209','1441735','1442237','1442505','1442673','1441555','1442221','1441547','1442231','1442613','1441483','1442097','1442189','1443021','1443031','1442051','1441883','1442309','1442015','1442729','1443281','1441623','1441057','1442559','1442485','1443095','1442167','1442817','1442823','1442839','1442855','1443215');

-----------------------------------------------------------------------------------------------------------------

ddlx common '订单类型'

deal_code common '交易号'

drlb common 导入类别

tfdd common 京东退货订单
ttdd => array('stime','time','deal_code','amount','sjcxje','yhje','dpjqje','dpdqje','price','yj','yjsje')

poptkdd_other common 京东退货订单
poptkdd_other=>array('lsh','deal_code','order_type','ywlx','fylx','fyje','spbh','spje','sl','sjyh','bz')

pophk common 京东妥投订单
pophk => array('deal_code','time','ywdh','order_type','ywlx','fylx','sl','spje','price','spbh','spmc','sjyh','kdl','bz')

检查交易号重复->原:SELECT deal_code,id FROM jingdong_yssj
WHERE `shop_id`='9' AND `year`='2014' AND `month`='5' AND deal_code !='' AND ddlx='tfdd'
AND (drlb='' OR ((drlb='poptkdd_other' OR drlb='popfy') AND tkje!=0)) GROUP BY deal_code HAVING COUNT(deal_code)>1 ;

result:deal_code,id -->有值
-------------------------------------------------------------------------------------------------------------------------

不跨月->原:UPDATE jingdong_yssj SET is_repeat='2'
WHERE deal_code !='' AND ddlx='tfdd' AND (drlb='' OR ((drlb='poptkdd_other' OR drlb='popfy') AND tkje!=0))
AND deal_code IN('1339730485','1357681435','1363073080','1363896095','1365171973','1371958508','1411179655','1425313117');

---------------------------------------------------------------------------------------------------------------------------

跨月-->原:UPDATE jingdong_yssj SET is_repeat='1' WHERE deal_code !='' AND ddlx='tfdd' AND (drlb='' OR ((drlb='poptkdd_other' OR drlb='popfy') AND tkje!=0)) AND id IN('1443944','1444040','1443978','1444108','1444001','1444059','1444282','1444186');

-----------------------------------------------------------------------------------------------------------------------------

dpjqje common 店铺京券金额

dpdqje common 店铺东券金额

获取指定时间内的末插入数据->原:
SELECT deal_code,id,price,shop_id,YEAR,MONTH,dpjqje,dpdqje FROM `jingdong_yssj` WHERE `shop_id`='9' AND `year`='2014' AND `month`='5' AND deal_code !='' AND ddlx='ttdd' AND is_repeat!='2' AND (drlb='' OR ((drlb='popttdd' OR drlb='pophk') AND price!=0)) AND NOT EXISTS(SELECT 1 FROM `public_yszh` WHERE shop_id='9' AND jingdong_yssj.deal_code=public_yszh.deal_code );//完全不懂

result:deal_code,id,price,shop_id,YEAR,MONTH,dpjqje,dpdqje

------------------------------------------------------------------------------------------------------------------------------

-- 获取指定时间内的末插入数据->原:
SELECT deal_code,id,tkje AS price,shop_id,YEAR,MONTH FROM `jingdong_yssj` WHERE `shop_id`='9' AND `year`='2014' AND `month`='5' AND deal_code !='' AND ddlx='tfdd' AND is_repeat!='2' AND (drlb='' OR ((drlb='poptkdd_other' OR drlb='popfy') AND tkje!=0)) AND NOT EXISTS(SELECT 1 FROM `public_thdz2` WHERE shop_id='9' AND jingdong_yssj.deal_code=public_thdz2.deal_code );

result:deal_code,id,price,shop_id,YEAR,MONTH ->无值

------------------------------------------------------------------------------------------------------------------------------

spmc common 商品名称

spbh common 商品编号

kh common 货号

cate2 common 栏目2

cate3 common 栏目3

-- 获取指定时间内的末插入数据->原:
SELECT deal_code,spmc,tkje AS price,spbh,1,kh,cate2,cate3,id AS SIGN,shop_id,YEAR,MONTH FROM `jingdong_yssj` WHERE `shop_id`='9' AND `year`='2014' AND `month`='5' AND deal_code !='' AND ddlx='tfdd' AND (drlb='' OR ((drlb='poptkdd_other' OR drlb='popfy') AND tkje!=0)) AND NOT EXISTS(SELECT 1 FROM `public_thdz_goods2` WHERE shop_id='9' AND jingdong_yssj.id=public_thdz_goods2.sign );//完全不懂

result:deal_code,spmc,price,spbh,1,kh,cate2,cate3,SIGN,shop_id,YEAR,MONTH--没有值

------------------------------------------------------------------------------------------------------------------------------

SELECT deal_code,id,price FROM jingdong_yssj WHERE `shop_id`='9' AND `year`='2014' AND `month`='5' AND is_repeat != '0' AND deal_code !='' AND ddlx='ttdd' AND (drlb='' OR ((drlb='popttdd' OR drlb='pophk') AND price!=0)) ;-- 不跨月

result:deal_code,id,price -->有数值

------------------------------------------------------------------------------------------------------------------------------

SELECT deal_code,id,price FROM jingdong_yssj WHERE `shop_id`='9' AND `year`='2014' AND `month`='5' AND is_repeat='0' AND deal_code !='' AND ddlx='ttdd' AND (drlb='' OR ((drlb='popttdd' OR drlb='pophk') AND price!=0)) AND NOT EXISTS (SELECT 1 FROM public_yszh WHERE `shop_id`='9' AND `year`='2014' AND `month`='5' AND public_yszh.deal_code=jingdong_yssj.deal_code);-- 跨月

result:deal_code,id,tkje -->数值为空

-------------------------------------------------------------------------------------------------------------------------------

SELECT deal_code,id,tkje FROM jingdong_yssj WHERE `shop_id`='9' AND `year`='2014' AND `month`='5' AND is_repeat != '0' AND deal_code !='' AND ddlx='tfdd' AND (drlb='' OR ((drlb='poptkdd_other' OR drlb='popfy') AND tkje!=0)) ;-- 不跨月

result:deal_code,id,tkje -->有数值

-------------------------------------------------------------------------------------------------------------------------------

SELECT deal_code,id,tkje FROM jingdong_yssj WHERE `shop_id`='9' AND `year`='2014' AND `month`='5' AND is_repeat='0' AND deal_code !='' AND ddlx='tfdd' AND (drlb='' OR ((drlb='poptkdd_other' OR drlb='popfy') AND tkje!=0)) AND NOT EXISTS (SELECT 1 FROM public_thdz2 WHERE `shop_id`='9' AND `year`='2014' AND `month`='5' AND public_thdz2.deal_code=jingdong_yssj.deal_code);-- 跨月 ???

result:deal_code,id,tkje -->数值为空
--------------------------------------------------------------------------------------------------------------------------------

1.跨月:is_repeat='0' 不跨月 is_repeat != 0(is_repeat=1,is_repeat=2)
2.is_repeat !=0 整合时 金额累加
3.conf[]表示需要查找配置文件.
4.原始整合还包括退货对账-->对应表(public_thdz2)