Exemple #1
0
        /// <summary>
        /// 重载 解析Excel
        /// </summary>
        /// <param name="fileAddress"></param>
        /// <returns></returns>
        public override string LoadExcel(string fileAddress)
        {
            int    rowIndex = 2;
            string error    = "success";

            var _shipPlanBizService  = Smart.Instance.Pro_ShipPlanBizService;
            var _shipPlansBizService = Smart.Instance.Pro_ShipPlansBizService;
            int mainId = 0;

            try
            {
                var    goodsBizSer = Smart.Instance.Base_GoodsBizService;
                string strConn;
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileAddress + "; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                DataTable sheetNames = conn.GetOleDbSchemaTable
                                           (OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                foreach (DataRow rowTable in sheetNames.Rows)
                {
                    string sheetName = rowTable["TABLE_NAME"].ToString();
                    using (conn = new OleDbConnection(strConn))
                    {
                        conn.Open();
                        OleDbCommand objCommand = new OleDbCommand(string.Format("select * from [" + sheetName + "]"), conn);

                        Pro_ShipPlanMain mainModel = new Pro_ShipPlanMain();
                        using (OleDbDataReader dataReader = objCommand.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                if (dataReader.FieldCount < 30)
                                {
                                    error = "解析错误,Excel 列头必须是30列(9列+21天),请下载模板 进行对比!";
                                    break;
                                }
                                if (rowIndex == 2)
                                {
                                    mainModel.ProNo        = "20180401001";
                                    mainModel.Updater      = mainModel.Creater = CurrentUser.Name;
                                    mainModel.RowState     = 1;
                                    mainModel.PlanFromDate = Convert.ToDateTime(dataReader[9]);
                                    mainModel.PlanFromTo   = Convert.ToDateTime(dataReader[9 + 20]);
                                    mainId = Smart.Instance.Pro_ShipPlanMainBizService.AddGetId(mainModel);
                                }
                                else if (rowIndex > 2)
                                {
                                    var model = new Pro_ShipPlan();
                                    model.ScheduleNo = dataReader[0].ToString();
                                    if (string.IsNullOrEmpty(model.ScheduleNo))
                                    {
                                        continue;
                                    }
                                    model.Term         = Convert.ToInt32(dataReader[1]);
                                    model.EditionNo    = dataReader[2].ToString();
                                    model.CityNo       = dataReader[3].ToString();
                                    model.ShipDetailNo = dataReader[4].ToString();
                                    model.ShipTo       = dataReader[5].ToString();
                                    model.ShipToName   = dataReader[6].ToString();
                                    model.GoodNo       = dataReader[7].ToString();
                                    model.GoodName     = dataReader[8].ToString();
                                    model.MainId       = mainId;
                                    var shipId = _shipPlanBizService.AddGetId(model);

                                    for (int i = 9; i <= 9 + 20; i++)
                                    {
                                        var value = dataReader[i].ToString().Trim();
                                        if (!string.IsNullOrEmpty(value))
                                        {
                                            var sonModel = new Pro_ShipPlans();
                                            sonModel.PlanId   = shipId;
                                            sonModel.PlanNum  = Convert.ToInt32(value);
                                            sonModel.PlanDate = mainModel.PlanFromDate.AddDays(i - 9);
                                            _shipPlansBizService.Add(sonModel);
                                        }
                                    }
                                }
                                rowIndex++;
                            }
                        }
                        conn.Close();
                    }
                    break;
                }
            }
            catch (Exception ex)
            {
                error = "Excel解析错误,成功" + (rowIndex - 1) + "条,错误行号:" + rowIndex + ",请检查 数据格式。";
                LogHelper.WriteLog("返货单导入错误", ex);
            }
            return(error);
        }
Exemple #2
0
        /// <summary>
        /// 获取当前Bom 是否有下级商品信息 需要排产
        /// </summary>
        /// <param name="query"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public int CreateNextBomList(QueryCondition query, string name)
        {
            var           mainId     = query.GetCondition("MainId").Value;
            var           parameters = WriteAdoTemplate.CreateDbParameters();
            StringBuilder strSql     = new StringBuilder();

            //获取BOM下一级的子产品 并剔除最后一级别
            strSql.AppendFormat(@"select distinct ShipTo,ShipToName,SonGoodNo as GoodNo,SonGoodName as GoodName,ParentGoodNo,ParentGoodName,BiLi
from(select ID,ShipTo,ShipToName,GoodNo,GoodName FROM Pro_SchedulingGoods  where  
SLineId IN (select ID from [dbo].[Pro_SchedulingLine] WHERE MainId={0})
) AS TB inner join [dbo].[Base_Bom] on TB.GoodNo=Base_Bom.ParentGoodNo where exists(select 1 from Base_Bom b where b.ParentGoodNo=Base_Bom.SonGoodNo)", mainId);

            var list = ReadAdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, strSql.ToString(), MapRowByCreateNextBom, parameters);

            //如果检测到有下一级的BOM信息
            if (list.Count > 0)
            {
                strSql = new StringBuilder();
                strSql.AppendFormat(@"select GoodNo,ShipTo,SDate,sum(SNum) as SNums from [dbo].[Pro_SchedulingGoodsNum]
left join Pro_SchedulingGoods on Pro_SchedulingGoods.Id=Pro_SchedulingGoodsNum.SGoodId
 where SGoodId in
(select TB.ID
from(select ID,ShipTo,ShipToName,GoodNo,GoodName FROM Pro_SchedulingGoods  where  
SLineId IN (select ID from [dbo].[Pro_SchedulingLine] WHERE MainId={0})
) AS TB inner join [dbo].[Base_Bom] on TB.GoodNo=Base_Bom.ParentGoodNo)   
and SType in (2,3,4)
group by GoodNo,ShipTo,SDate", mainId);
                //排产信息 将早中晚 汇总在一起
                var num_list = ReadAdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, strSql.ToString(), MapRow_BomNum, parameters).ToList();
                if (num_list.Count > 0)
                { //主信息
                    var scheduling_model = GetAllDomain(QueryCondition.Instance.AddEqual("Id", mainId)).ToList()[0];

                    Pro_ShipPlanMain mainModel = new Pro_ShipPlanMain();

                    mainModel.Updater      = mainModel.Creater = name;
                    mainModel.RowState     = 1;
                    mainModel.PlanFromDate = scheduling_model.PlanFromDate;
                    mainModel.PlanFromTo   = scheduling_model.PlanToDate;
                    var main_Id = new Pro_ShipPlanMainDao().AddGetId(mainModel);

                    var _shipPlanBizService  = new Pro_ShipPlanDao();
                    var _shipPlansBizService = new Pro_ShipPlansDao();
                    foreach (var good in list)
                    {
                        var model = new Pro_ShipPlan();
                        model.ScheduleNo = "";

                        model.Term         = 0;
                        model.EditionNo    = "";
                        model.CityNo       = "";
                        model.ShipDetailNo = "";
                        model.ShipTo       = good.ShipTo;
                        model.ShipToName   = good.ShipToName;
                        model.GoodNo       = good.GoodNo;
                        model.GoodName     = good.GoodName;
                        model.MainId       = main_Id;

                        //获取goodNO+ shipto的商品 如果没有对应的排产信息就不要出来了
                        var good_num_list = num_list.FindAll(t => t.GoodNo == good.ParentGoodNo && t.ShipTo == good.ShipTo);
                        if (good_num_list.Count == 0)
                        {
                            continue;
                        }
                        var shipId = _shipPlanBizService.AddGetId(model);



                        for (int i = 0; i < good_num_list.Count; i++)
                        {
                            var goodNum  = good_num_list[i];
                            var sonModel = new Pro_ShipPlans();
                            sonModel.PlanId   = shipId;
                            sonModel.PlanNum  = Convert.ToInt32((goodNum.SNum ?? 0) * good.BiLi);
                            sonModel.PlanDate = goodNum.SDate;
                            _shipPlansBizService.Add(sonModel);
                        }
                    }
                    return(main_Id);
                }
                else
                {
                    //说明没有下一级的BOM 信息
                    return(-1);
                }
            }
            //说明没有下一级的BOM 信息
            return(-1);
        }