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--

   --αڱ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 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;
        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='0',errorinfo='ظ' 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,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_customerprovince,v_customercity,v_customeraddress1,v_customerzipcode,v_shopid,v_shopname,v_moneytype,0,0,v_rmbexpressmoney,sysdate,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='ظ~' 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,sysdate,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;

                                --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,sysdate,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='쳣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='ظ~~' 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,sysdate,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;

                                --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,sysdate,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;
