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;
