yilv_new/AA/WangToTempDb/PO_PomainUpLoad.cs

using cuidian.Common;
using cuidian.Sql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WdtUtils;
using WdtUtils.Load;
using WdtUtils.Proxy;

namespace WangToTempDb
{ /// <summary>
  /// 旺店通采购订单上传中间库
  /// </summary>
    public class PO_PomainUpLoad : BaseUpLoad
    {
        //private string __sql = @"if not exists (select * from PO_Pomain a inner join PO_Podetails b on a.POID=b.POID where a.POID= '{0}') insert into PO_Pomain(POID,cPOID,cState,cVenCode,cAuditDate,cMemo,dPODate,cModifyTime,direction,iVTid) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}') insert into PO_Podetails(ID,POID,cInvCode,iQuantity,iTaxPrice,iPerTaxRate) values('{10}','{0}','{11}','{12}','{13}','{14}')";

        private string __sql = @"if not exists (select POID from PO_Pomain where POID= {0}) insert into PO_Pomain(POID,cPOID,cState,cVenCode,cMemo,dPODate,cModifyTime,direction,iVTid) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')";
        //避免只能插入一条子表数据
        private string sql = @" if not exists (select POID from PO_Podetails where POID= {0}) ";
        private string newSql = @"insert into PO_Podetails(POID,ID,cInvCode,iQuantity,iTaxPrice,iPerTaxRate) values('{0}','{1}','{2}','{3}','{4}','{5}')";
        protected override Result _LoadFirstPage()
        {
            PO_PomainProxy tmp = new PO_PomainProxy();
            Result rtn = _load.Load<PO_PomainProxy>(_args, ref tmp);

            _proxy = tmp;

            return rtn;
        }

        protected override void _SetLoader()
        {
            _load = new PO_PomainLoad();
        }

        protected override void _ImportData()
        {
            _operType = "插入采购订单中间表数据";

            Purchase_List[] entitys = ((PO_PomainProxy)_proxy).purchase_list;

            if (entitys == null || entitys.Length == 0) return;

            StringBuilder sb = new StringBuilder();

            foreach (Purchase_List item in entitys)
            {
                //sb.AppendLine(string.Format(__sql, new object[] { item.purchase_id,item.purchase_no,0,item.provider_no,item.check_time,item.remark ,item.created ,item.modified,1,8173,item.details_list[0].rec_id,item.details_list[0].spec_no,item.details_list[0].num ,item.details_list[0].price,item.details_list[0].tax }));
                //item.check_time= Convert.ToInt32(item.check_time.Split('-')[0]) < 1753 ? DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") : item.check_time;

                sb.AppendLine(string.Format(__sql, new object[] { item.purchase_id, _ConvertStr(item.purchase_no), 0, _ConvertStr(item.provider_no), _ConvertStr(item.remark), item.created, item.modified, 1, "8173" }));
                sb.AppendLine(string.Format(sql, new object[] { item.purchase_id }));
                Details_List[] dl = item.details_list;
                int dlCount = 1;
                double dlSum = Convert.ToDouble(item.goods_amount)+ Convert.ToDouble(item.post_fee);
                if (dl.Length==0)
                {
                    continue;
                }
                sb.AppendLine("begin");
                foreach (Details_List ty in dl)
                {
                    if (dlCount != dl.Count())
                    {
                        double dlPrice = Convert.ToDouble(((Convert.ToDouble(ty.amount) / (Convert.ToDouble(item.goods_amount) == 0 ? 1 : Convert.ToDouble(item.goods_amount))) * Convert.ToDouble(item.post_fee) / Convert.ToDouble(ty.num) + Convert.ToDouble(ty.price)).ToString("f4"));
                        dlSum = dlSum - dlPrice * Convert.ToDouble(ty.num);
                        sb.AppendLine(string.Format(newSql, new object[] { item.purchase_id, ty.rec_id, _ConvertStr(ty.spec_no), _ConvertNotStr(ty.num), dlPrice, _ConvertNotStr(ty.tax) }));
                    }else
                    {
                        
                        sb.AppendLine(string.Format(newSql, new object[] { item.purchase_id, ty.rec_id, _ConvertStr(ty.spec_no), _ConvertNotStr(ty.num), (Convert.ToDouble(dlSum) / (Convert.ToDouble(ty.num)==0?1: Convert.ToDouble(ty.num))), _ConvertNotStr(ty.tax) }));
                    }
                    dlCount = dlCount + 1;

                }
                sb.AppendLine("end");

                _InsertIntoTempDb(sb.ToString(), item.purchase_no);
            }

            
        }

        protected override void _DeleteDup()
        {
            _operType = "删除采购订单中间表重复数据";
            //string sql = "delete a from {0}..PO_Pomain a,PO_Podetails b,{1}..PO_Pomain c,PO_Podetails d where a.POID = b.POID and b.POID=c.POID and c.POID=d.POID";

            string sql1 = "delete a from {0}..PO_Pomain a, {1}..PO_Pomain b where a.POID = b.cdefine5 ";
            string sql2 = "delete a from {0}..PO_Podetails a, {1}..PO_Podetails b where a.POID = b.cdefine27 ";
            DbUtils.ExecuteNonQuery(string.Format(sql1, _tempDb.Database, _targetDb.Database), _tempDb);
            DbUtils.ExecuteNonQuery(string.Format(sql2, _tempDb.Database, _targetDb.Database), _tempDb);
        }

        protected override void _LoadNextPage()
        {
            _proxy = _load.Load<PO_PomainProxy>(_args);
        }
    }
}