﻿CREATE OR REPLACE PROCEDURE "PROC_MOVE_TEMP_TO_ORDER"   (
    v_groupid              in  varchar2,
    v_RESULT               out varchar2,
    v_DESCR                  out varchar2
)
--Version 1.0
--2011-12-10 by qinli xubo yanghua
--??????db_order_temp??????????????db_order,db_sell
--???????CSV/XLS????????
As
    v_sequenceid    varchar2(50):='';
   v_tradeid        varchar2(200):='';
   v_customerid        varchar2(200):='';
   v_customername        varchar2(500):='';
   v_customertel        varchar2(500):='';
   v_customeremail        varchar2(200):='';
   v_customeraddress1        varchar2(1000):='';
   v_customeraddress2        varchar2(1000):='';
   v_customercity        varchar2(500):='';
   v_customerprovince        varchar2(500):='';
   v_customerzipcode        varchar2(200):='';
   v_customercountry        varchar2(1000):='';
   v_productid        varchar2(200):='';
   v_ordernum        varchar2(200):='';
   v_sellpricestr        varchar2(200):='';
   v_expressstr        varchar2(200):='';
   v_paypalid        varchar2(200):='';
   v_transactionid        varchar2(200):='';
   v_variationdetails        varchar2(200):='';
   v_productname        varchar2(1000):='';
   v_itemid        varchar2(200):='';
   v_insurance        varchar2(200):='';
   v_postageservice        varchar2(200):='';
   v_sline    varchar2(2000):='';
   v_recordnum    number(10):=0;
   v_oper    varchar2(50):='';
   v_shopid    varchar2(50):='';
   v_shopname    varchar2(100):='';
   v_shopitemid    varchar2(100):='';
   v_checkflag    varchar2(10):='';
   v_errorinfo    varchar2(500):='';
   v_moneytype    varchar2(100):='';
   v_moneyrate    number(10,4):='';
   v_originsellprice    number(14,4):=0;
   v_rmbsellprice    number(12,4):=0;
   v_rmbamount    number(12,4):=0;
   v_originexpress    number(14,4):=0;
   v_rmbexpressmoney    number(12,4):=0;
   v_insurancemoney    number(14,4):=0;
   v_rmbinsurancemoney    number(12,4):=0;
   v_corpid    varchar2(100):='';
   v_orderid  varchar2(100):='';
   v_pickupoper   varchar2(100):='';--??
   v_packagingid   varchar2(100):='';--??id
   v_packagingname   varchar2(200):='';--??name
   v_packagingweight  number(14,4):=0;--????
   v_packagingclass   varchar2(100):='';--????

   v_status varchar2(100):= '';--??--
   v_orderExist   number(2):=0;--????????
   v_productExist   number(4):=0;--????????
   v_sellExist     number(4):=0;--??????????
   v_bindFlag   number(4):=0;--??????
   v_costprice number(12,4):=0; --??
   v_bindproductid  varchar2(100):= '';--?????id
   v_bindproductname varchar2(100):= '';--??????--
   v_bindcostprice  number(12,4):=0; --????????
   v_bindCount number(4):=0;--????????--
   v_bindnum  number(6):=0;--????--
   v_sql_bindlist varchar2(2048) := '';
   v_shoppl varchar2(200) :='';--?????shiliang??--
   v_content varchar2(4000) :='';--??????? hj 20130315--
	v_ordertime  date:=sysdate;--订单时间fst---
   --???????db_order_temp??checkflag=0???(checkflag=1???????),?recordnum???????field25--
   cursor c_rec is select sequenceid,tradeid,customerid,customername,customertel,customeremail,customeraddress1,customeraddress2,customercity,customerprovince,customerzipcode,customercountry,productid,ordernum,sellpricestr,expressstr,paypalid,transactionid,variationdetails,productname,itemid,insurance,postageservice,sline,recordnum,oper,shopid,shopname,shopitemid,checkflag,errorinfo,moneytype,moneyrate,originsellprice,rmbsellprice,rmbamount,originexpress,rmbexpressmoney,insurancemoney,rmbinsurancemoney,corpid,field25,content,opertime from db_order_temp where checkflag='0' and groupid=v_groupid order by recordnum;

   TYPE REF_CURSOR IS REF CURSOR;
   c_bindlist REF_CURSOR;
begin
     open c_rec;
     loop
        <<c_rec_mark>>
        fetch c_rec into v_sequenceid,v_tradeid,v_customerid,v_customername,v_customertel,v_customeremail,v_customeraddress1,v_customeraddress2,v_customercity,v_customerprovince,v_customerzipcode,v_customercountry,v_productid,v_ordernum,v_sellpricestr,v_expressstr,v_paypalid,v_transactionid,v_variationdetails,v_productname,v_itemid,v_insurance,v_postageservice,v_sline,v_recordnum,v_oper,v_shopid,v_shopname,v_shopitemid,v_checkflag,v_errorinfo,v_moneytype,v_moneyrate,v_originsellprice,v_rmbsellprice,v_rmbamount,v_originexpress,v_rmbexpressmoney,v_insurancemoney,v_rmbinsurancemoney,v_corpid,v_shoppl,v_content,v_ordertime;
        exit when c_rec%notfound;
            v_orderExist := 0;
            -----------????????------------------------
            --????????db_order
            if length(v_customername) > 0 then  ----????db_order
                  --?????tradeid,?shopid??
                  select count(*) into v_orderExist from db_order where tradeid=v_tradeid and shoptypeid = v_shopid;
                  if v_orderExist > 0 then  --??????
                     update db_order_temp set checkflag='1',errorinfo='exist tradeid' where sequenceid = v_sequenceid;
                     commit;
                  else   --???????,????
                     if length(v_paypalid) > 0 then
                        v_status := '已支付';
                     else
                        v_status := '新订单';
                     end if;
                     --????
                     insert into db_order (orderid,corpid,tradeid,groupid,customerid,customername,customertype,customertel1,customeremail,customerqq,customercountry,customerprovince,customercity,customeraddress,customerzipcode,shoptypeid,shoptype,moneytype,moneyask,moneyaction,moneyexpressask,ordertime,uploadtime,paytime,expressweight,payoper,oper,opertime,status,insurance,postageservice,reserve20,content)
                        values(v_sequenceid,v_corpid,v_tradeid,v_groupid,v_customerid,v_customername,v_shopname,v_customertel,v_customeremail,v_customercountry,v_customercountry,v_customerprovince,v_customercity,v_customeraddress1,v_customerzipcode,v_shopid,v_shopname,v_moneytype,0,0,v_rmbexpressmoney,v_ordertime,sysdate,null,0,null,v_oper,sysdate,v_status,v_rmbinsurancemoney,v_postageservice,v_shoppl,v_content);

                     commit;
                  end if;

                  if v_productid is null or length(v_productid) <= 0 then --?????ID,?????????????,?????db_order
                     update db_order_temp set checkflag='2' where checkflag='0' and sequenceid = v_sequenceid;
                     commit;
                  else --??,?????db_sell
                     --???tradeid,?shopid???,???????id
                     begin
                        select orderid into v_orderid from db_order where tradeid=v_tradeid and shoptypeid = v_shopid;
                        --??DbSell??????(????:???????,?tradeid,?transactionid??)
                        v_sellExist := 0;
                        select count(*) into v_sellExist from db_sell where tradeid=v_tradeid and shoptype=v_shopname and transactionid=v_transactionid;
                        if v_sellExist > 0 then   --DbSell??
                            update db_order_temp set checkflag='1',errorinfo='Sell info exist' where sequenceid = v_sequenceid;
                            commit;
                        else
                            ------------------------????------------------------------
                            -------------???:????????--------------------------
                            v_bindFlag := 0;
                            select count(*) into v_bindFlag from db_bindlist where bindid=v_productid;
                            if v_bindFlag > 0 then--??????
                                v_bindCount := 0;
                                v_bindFlag := 1;
                                v_sql_bindlist := 'select a.alias1,a.num,b.name,b.costprice,b.oper2,b.packagingid,b.packagingname,b.packagingweight,b.iclass from db_bindlist a left join db_product b on a.alias1= b.sid where a.bindid='''|| v_productid ||'''';
                                open c_bindlist for v_sql_bindlist;
                                loop
                                   <<c_bindlist_mark>>
                                      v_bindproductid := '';
                                      v_bindnum := 1;
                                      v_bindproductname := '';
                                      v_bindcostprice := 0;
                                   fetch c_bindlist into v_bindproductid,v_bindnum,v_bindproductname,v_bindcostprice,v_pickupoper,v_packagingid,v_packagingname,v_packagingweight,v_packagingclass;
                                   exit when c_bindlist%notfound;
                                      v_bindCount := v_bindCount +1;
                                      --??????????--
                                      if length(v_bindproductname) > 0 then  --???v_bindproductname,??????--
                                          --DbSell??--
                                          insert into db_sell (sid,corpid,orderid,productid,productname,ordernum,costprice,sellprice,amount,customerid,ordertime,alertflag,shoptype,updateflag,oper,opertime,flag,descr2,originsellprice,moneytype,tradeid,transactionid,groupid,shoptypeid,ebayitemid,finalvaluefee,feedback,oper1,packagingid,packagingname,packagingweight,packagingclass,reserve10)
                                              values(v_groupid||v_recordnum||v_bindCount,v_corpid,v_orderid,v_bindproductid,v_bindproductname,v_ordernum*v_bindnum,v_bindcostprice,v_rmbsellprice/v_bindnum,v_rmbamount,v_customerid,v_ordertime,0,v_shopname,'0',v_oper,sysdate,'1',v_variationdetails,v_originsellprice/v_bindnum,v_moneytype,v_tradeid,v_transactionid,v_groupid,v_shopid,v_itemid,'0','0',v_pickupoper,v_packagingid,v_packagingname,v_packagingweight,v_packagingclass,v_shoppl);

                                          --???????????????,??????????1???,?2??????0--
                                          v_rmbsellprice := 0;
                                          v_rmbamount := 0;
                                          v_originsellprice := 0;
                                          --??DbOrder????
                                          update db_order set oper1=v_pickupoper where orderid=v_orderid and oper1 is null;
                                          commit;
                                      end if;
                                end loop;
                                close c_bindlist;

                                update db_order_temp set checkflag='2' where checkflag='1' and sequenceid = v_sequenceid;
                                commit;
                            else  ---????????--------
                                --????????,?????,?????-----------
                                v_productExist := 0;
                                select count(*) into v_productExist from db_product where (sid=v_productid or alias1 like '%,'||v_productid||',%') and corpid=v_corpid;
                                if v_productExist <= 0 then
                                   insert into db_product (sequenceid,sid,corpid,name,descr,status,stockprice,costprice,sellprice,batchprice1,weight,buynum,savenum,ordernum,alertnum,openflag,oper,opertime)
                                   values(v_productid,v_productid,v_corpid,nvl(v_productname,'自动创建商品名称'),'自动创建','自动创建',0,0,v_rmbsellprice,0,0,0,0,0,0,'1',v_oper,sysdate);
                                   commit;
                                end if;-----????
                                --??????
                                v_costprice := 0;
                                v_pickupoper :='';
                                select sid,name,costprice,oper2,packagingid,packagingname,packagingweight,iclass into v_productid,v_productname,v_costprice,v_pickupoper,v_packagingid,v_packagingname,v_packagingweight,v_packagingclass from db_product where (sid=v_productid or alias1 like '%,'||v_productid||',%') and corpid=v_corpid and rownum<=1;

                                --DbSell??
                                insert into db_sell (sid,corpid,orderid,productid,productname,ordernum,costprice,sellprice,amount,customerid,ordertime,alertflag,shoptype,updateflag,oper,opertime,flag,descr2,originsellprice,moneytype,tradeid,transactionid,groupid,shoptypeid,ebayitemid,finalvaluefee,feedback,oper1,packagingid,packagingname,packagingweight,packagingclass,reserve10)
                                   values(v_groupid||v_recordnum,v_corpid,v_orderid,v_productid,v_productname,v_ordernum,v_costprice,v_rmbsellprice,v_rmbamount,v_customerid,v_ordertime,0,v_shopname,'0',v_oper,sysdate,'1',v_variationdetails,v_originsellprice,v_moneytype,v_tradeid,v_transactionid,v_groupid,v_shopid,v_itemid,'0','0',v_pickupoper,v_packagingid,v_packagingname,v_packagingweight,v_packagingclass,v_shoppl);

                                update db_order_temp set checkflag='2' where checkflag='0' and sequenceid = v_sequenceid;
                                commit;

                                --??DbOrder????--
                                update db_order set oper1=v_pickupoper where orderid=v_orderid and oper1 is null;
                                commit;
                            end if;
                        end if;
                     exception
                     when no_data_found then
                         update db_order_temp set checkflag='1',errorinfo='???OrderID~' where sequenceid = v_sequenceid;
                         commit;
                     when others then
                         update db_order_temp set checkflag='1',errorinfo='Exception BBB,' where sequenceid = v_sequenceid;
                         commit;
                     end;
                  end if;

                  update db_order_temp set checkflag='2' where checkflag='0' and sequenceid = v_sequenceid;
                  commit;
            else    ---????db_order,????db_sell
                  if v_productid is null or length(v_productid) <= 0 then --?????ID,??????
                     update db_order_temp set checkflag='1',errorinfo='??ID???' where checkflag='0' and sequenceid = v_sequenceid;
                     commit;
                  else --??,?????db_sell
                     --????db_order????id
                     begin
                        select orderid into v_orderid from db_order where tradeid=v_tradeid and shoptypeid = v_shopid;
                        --??DbSell??????(????:???????,?tradeid,?transactionid,????????)
                        v_sellExist := 0;
                        select count(*) into v_sellExist from db_sell where tradeid=v_tradeid and shoptype=v_shopname and transactionid=v_transactionid and groupid != v_groupid;
                        if v_sellExist > 0 then   --DbSell??--
                            update db_order_temp set checkflag='1',errorinfo='sell info exist' where sequenceid = v_sequenceid;
                            commit;
                        else
                            ------------------------????------------------------------
                            -------------???:????????--------------------------
                            v_bindFlag := 0;
                            select count(*) into v_bindFlag from db_bindlist where bindid=v_productid;
                            if v_bindFlag > 0 then--??????--
                                v_bindCount := 0;
                                v_bindFlag := 1;
                                v_sql_bindlist := 'select a.alias1,a.num,b.name,b.costprice,b.oper2,b.packagingid,b.packagingname,b.packagingweight,b.iclass from db_bindlist a left join db_product b on a.alias1= b.sid where a.bindid='''|| v_productid ||'''';
                                open c_bindlist for v_sql_bindlist;
                                loop
                                   <<c_bindlist_mark>>
                                      v_bindproductid := '';
                                      v_bindnum := 1;
                                      v_bindproductname := '';
                                      v_bindcostprice := 0;
                                   fetch c_bindlist into v_bindproductid,v_bindnum,v_bindproductname,v_bindcostprice,v_pickupoper,v_packagingid,v_packagingname,v_packagingweight,v_packagingclass;
                                   exit when c_bindlist%notfound;
                                      v_bindCount := v_bindCount +1;
                                      --??????????
                                      if length(v_bindproductname) > 0 then  --???v_bindproductname,???????                                          --DbSell??
                                          insert into db_sell (sid,corpid,orderid,productid,productname,ordernum,costprice,sellprice,amount,customerid,ordertime,alertflag,shoptype,updateflag,oper,opertime,flag,descr2,originsellprice,moneytype,tradeid,transactionid,groupid,shoptypeid,ebayitemid,finalvaluefee,feedback,oper1,packagingid,packagingname,packagingweight,packagingclass,reserve10)
                                              values(v_groupid||v_recordnum||v_bindCount,v_corpid,v_orderid,v_bindproductid,v_bindproductname,v_ordernum*v_bindnum,v_bindcostprice,v_rmbsellprice/v_bindnum,v_rmbamount,v_customerid,v_ordertime,0,v_shopname,'0',v_oper,sysdate,'1',v_variationdetails,v_originsellprice/v_bindnum,v_moneytype,v_tradeid,v_transactionid,v_groupid,v_shopid,v_itemid,'0','0',v_pickupoper,v_packagingid,v_packagingname,v_packagingweight,v_packagingclass,v_shoppl);

                                          --???????????????,??????????1???,?2??????0
                                          v_rmbsellprice := 0;
                                          v_rmbamount := 0;
                                          v_originsellprice := 0;

                                          --??DbOrder????--
                                          update db_order set oper1=v_pickupoper where orderid=v_orderid and oper1 is null;
                                          commit;
                                      end if;
                                end loop;
                                close c_bindlist;

                                update db_order_temp set checkflag='2' where checkflag='1' and sequenceid = v_sequenceid;
                                commit;
                            else  ---????????--------
                                --????????,?????,?????-----------
                                v_productExist := 0;
                                select count(*) into v_productExist from db_product where (sid=v_productid or alias1 like '%,'||v_productid||',%') and corpid=v_corpid;
                                if v_productExist <= 0 then
                                   insert into db_product (sequenceid,sid,corpid,name,descr,status,stockprice,costprice,sellprice,batchprice1,weight,buynum,savenum,ordernum,alertnum,openflag,oper,opertime)
                                   values(v_productid,v_productid,v_corpid,v_productname,'自动创建','自动创建',0,0,v_rmbsellprice,0,0,0,0,0,0,'1',v_oper,sysdate);
                                   commit;
                                end if;-----????--
                                --??????--
                                v_costprice := 0;
                                v_pickupoper :='';
                                select sid,name,costprice,oper2,packagingid,packagingname,packagingweight,iclass into v_productid,v_productname,v_costprice,v_pickupoper,v_packagingid,v_packagingname,v_packagingweight,v_packagingclass from db_product where (sid=v_productid or alias1 like '%,'||v_productid||',%') and corpid=v_corpid and rownum<=1;

                                --DbSell??--
                                insert into db_sell (sid,corpid,orderid,productid,productname,ordernum,costprice,sellprice,amount,customerid,ordertime,alertflag,shoptype,updateflag,oper,opertime,flag,descr2,originsellprice,moneytype,tradeid,transactionid,groupid,shoptypeid,ebayitemid,finalvaluefee,feedback,oper1,packagingid,packagingname,packagingweight,packagingclass,reserve10)
                                   values(v_groupid||v_recordnum,v_corpid,v_orderid,v_productid,v_productname,v_ordernum,v_costprice,v_rmbsellprice,v_rmbamount,v_customerid,v_ordertime,0,v_shopname,'0',v_oper,sysdate,'1',v_variationdetails,v_originsellprice,v_moneytype,v_tradeid,v_transactionid,v_groupid,v_shopid,v_itemid,'0','0',v_pickupoper,v_packagingid,v_packagingname,v_packagingweight,v_packagingclass,v_shoppl);

                                update db_order_temp set checkflag='2' where checkflag='0' and sequenceid = v_sequenceid;
                                commit;

                                --??DbOrder????--
                                update db_order set oper1=v_pickupoper where orderid=v_orderid and oper1 is null;
                                commit;
                            end if;
                        end if;
                     exception
                     when no_data_found then
                         update db_order_temp set checkflag='1',errorinfo='???OrderID!' where sequenceid = v_sequenceid;
                         commit;
                     when others then
                         update db_order_temp set checkflag='1',errorinfo='??????AAA,' where sequenceid = v_sequenceid;
                         commit;
                     end;
                  end if;

                  update db_order_temp set checkflag='2' where checkflag='0' and sequenceid = v_sequenceid;
                  commit;
            end if;
     end loop;
     close c_rec;

     commit;--??-----
     v_RESULT :='0';
     v_DESCR := '导入成功';
     return;
exception
when others then
      --dbms_output.put_line(sqlcode||':'||sqlerrm);
      v_RESULT:='1';
      v_DESCR:='导入失败';
     return;
end;
