Пример #1
0
        public async Task <FuncResult> DeleteData(string key)
        {
            FuncResult fr = new FuncResult()
            {
                IsSuccess = true, Message = "操作成功!"
            };

            try
            {
                if (string.IsNullOrWhiteSpace(key))
                {
                    fr.IsSuccess = false;
                    fr.Message   = "未接收到参数信息!";
                }
                //var _key = Convert.ToDecimal(key);
                ApdFctLandTown2 town2 = context.ApdFctLandTown2.FirstOrDefault(f => f.RecordId.Equals(key));
                if (town2 == null)
                {
                    fr.IsSuccess = false;
                    fr.Message   = "异常参数,未找到数据!";
                }
                List <ApdFctLandTown> listtown = context.ApdFctLandTown.Where(f => f.T2Id.Equals(key)).ToList();

                //删除
                context.ApdFctLandTown2.Remove(town2);
                context.ApdFctLandTown.RemoveRange(listtown);


                using (IDbContextTransaction trans = context.Database.BeginTransaction())
                {
                    try
                    {
                        await context.SaveChangesAsync();

                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        fr.IsSuccess = false;
                        fr.Message   = $"{ex.InnerException},{ex.Message}";
                        throw new Exception("error", ex);
                    }
                }
                return(fr);
            }
            catch (Exception ex)
            {
                throw new Exception("error", ex);
            }
        }
Пример #2
0
        public FuncResult Import(string year)
        {
            FuncResult result = new FuncResult()
            {
                IsSuccess = true, Message = "操作成功!"
            };

            try
            {
                var         excelfile = Request.Form.Files[0];
                List <Demo> datalist  = new List <Demo>();
                if (excelfile.Length > 0)
                {
                    DataTable dt = new DataTable();
                    string    strMsg;
                    string    filename = excelfile.FileName;//文件名即为区域名
                    //利用IFormFile里面的OpenReadStream()方法直接读取文件流
                    dt = ExcelHelper.ExcelToDatatable(excelfile.OpenReadStream(), Path.GetExtension(excelfile.FileName), out strMsg);
                    if (!string.IsNullOrEmpty(strMsg))
                    {
                        result.IsSuccess = false;
                        result.Message   = strMsg;
                        return(result);
                    }
                    if (dt.Rows.Count > 0)
                    {
                        var listorgan = context.ApdDimOrg.ToList();
                        //需要导入到数据库的数据
                        datalist = JsonConvert.DeserializeObject <List <Demo> >(JsonConvert.SerializeObject(dt));

                        #region 部分数据格式判断
                        //需要导入到数据库的数据
                        var dynamiclist = JsonConvert.DeserializeObject <List <dynamic> >(JsonConvert.SerializeObject(dt));
                        var prefilter   = dynamiclist.Where(f => f.G10 != null && f.G11 != null && f.G12 != null).ToList();
                        if (prefilter == null || prefilter.Count() <= 0)
                        {
                            result.IsSuccess = false;
                            result.Message   = "未选择正确的Excel文件或选择的Excel文件无可导入数据!";
                            return(result);
                        }

                        /*
                         * 1、筛选数据前,检查数据格式,只需要检测数值类型的列
                         * **/

                        int    count   = 1;//错误列号(对应实际列7)
                        string colname = "";
                        for (int i = 0; i < prefilter.Count(); i++)
                        {
                            try
                            {
                                var current = prefilter[i];

                                var g_10 = current.G10;
                                if (g_10 == "")
                                {
                                    continue;
                                }
                                colname = "G10";
                                Convert.ToDecimal(g_10);

                                var g_11 = current.G11;
                                if (g_11 == "")
                                {
                                    continue;
                                }
                                colname = "G11";
                                Convert.ToDecimal(g_11);

                                var g_12 = current.G12;
                                if (g_12 == "")
                                {
                                    continue;
                                }
                                colname = "G12";
                                Convert.ToDecimal(g_12);

                                var g_13 = current.G13;
                                if (g_13 == "")
                                {
                                    continue;
                                }
                                colname = "G13";
                                Convert.ToDecimal(g_13);

                                var g_14 = current.G14;
                                if (g_14 == "")
                                {
                                    continue;
                                }
                                colname = "G14";
                                Convert.ToDecimal(g_14);

                                var g_15 = current.G15;
                                if (g_15 == "")
                                {
                                    continue;
                                }
                                colname = "G15";
                                Convert.ToDecimal(g_15);

                                count++;
                            }
                            catch (Exception ex)
                            {
                                LogService.WriteError(ex);
                                result.IsSuccess = false;
                                result.Message   = $"第{count + 6}行,{colname}列数据异常!";
                                return(result);
                            }
                        }


                        #endregion

                        List <Demo> filterdata = datalist.Where(f => f.G10 != null && f.G11 != null && f.G12 != null).ToList();
                        if (filterdata == null || filterdata.Count <= 0)
                        {
                            result.IsSuccess = false;
                            result.Message   = "未选择正确的Excel文件或选择的Excel文件无可导入数据!";
                            return(result);
                        }
                        //处理筛选过后的数据
                        string g1 = string.Empty;
                        string g3 = string.Empty;
                        foreach (var item in filterdata)
                        {
                            if (!string.IsNullOrEmpty(item.G1))
                            {
                                g1 = item.G1;
                            }
                            if (string.IsNullOrEmpty(item.G1))
                            {
                                //为上一个有值的g1
                                item.G1 = g1;
                            }

                            if (!string.IsNullOrEmpty(item.G3))
                            {
                                g3 = item.G3;
                            }
                            if (string.IsNullOrEmpty(item.G3))
                            {
                                //为上一个有值的g3
                                item.G3 = g3;
                            }
                        }

                        var groupdata_1 = filterdata.GroupBy(g => new { g.G1, g.G3, g.G10, g.G11, g.G12 }).Select(s => new ReflctModel1
                        {
                            OWNERSHIPLAND = Convert.ToDecimal(s.Key.G10),
                            PROTECTIONLAN = Convert.ToDecimal(s.Key.G11),
                            REDUCELAND    = Convert.ToDecimal(s.Key.G12),
                            ORGANIZATION  = s.Key.G1,
                            ORGCODE       = s.Key.G3
                        });

                        var groupdata_2 = filterdata.GroupBy(g => new { g.G1, g.G3, g.G13, g.G14, g.G15, g.G16 }).Select(s => new ReflctModel2
                        {
                            FACTLAND     = Convert.ToDecimal(s.Key.G13),
                            RENTLAND     = Convert.ToDecimal(s.Key.G14),
                            LEASELAND    = Convert.ToDecimal(s.Key.G15),
                            REMARK       = s.Key.G16,
                            ORGANIZATION = s.Key.G1,
                            ORGCODE      = s.Key.G3
                        }).Where(f => (f.FACTLAND != null && f.RENTLAND != null && f.LEASELAND != null) == true);

                        string currenttown2key = "";//本次apd_fct_town2表的主键

                        //删除当前年份已有数据
                        //物理删除
                        var formatyear   = Convert.ToDecimal(year);
                        var alreadytown2 = context.ApdFctLandTown2.Where(f => f.PeriodYear.Equals(formatyear));
                        var alreadytown  = context.ApdFctLandTown.Where(f => alreadytown2.Select(g => g.RecordId).Contains(f.T2Id));
                        context.ApdFctLandTown2.RemoveRange(alreadytown2);
                        context.ApdFctLandTown.RemoveRange(alreadytown);

                        //t2作为t1的主表
                        foreach (var item in groupdata_2)
                        {
                            var currentorganization = listorgan.FirstOrDefault(f => f.OrgCode.Equals(item.ORGCODE));
                            if (currentorganization == null)
                            {
                                result.IsSuccess = false;
                                result.Message   = $"此机构号:{item.ORGCODE}找不到对应机构,导入失败!";
                                return(result);
                            }
                            //bool isalreadyexport = isAlreadyExport(item.ORGCODE, year);
                            //if (isalreadyexport)
                            //{
                            //    //删除(添加删除标记字段)
                            //    //物理删除
                            //    var formatyear = Convert.ToDecimal(year);
                            //    var alreadytown2 = context.ApdFctLandTown2.Where(f => f.OrgCode.Equals(item.ORGCODE) && f.PeriodYear.Equals(formatyear));
                            //    var alreadytown = context.ApdFctLandTown.Where(f => alreadytown2.Select(g => g.RecordId).Contains(f.T2Id));
                            //    context.ApdFctLandTown2.RemoveRange(alreadytown2);
                            //    context.ApdFctLandTown.RemoveRange(alreadytown);
                            //}
                            ApdFctLandTown2 t2 = new ApdFctLandTown2()
                            {
                                OrgCode        = item.ORGCODE,
                                FactLand       = item.FACTLAND,
                                RentLand       = item.RENTLAND,
                                LeaseLand      = item.LEASELAND,
                                Remark         = item.REMARK,
                                PeriodYear     = Convert.ToDecimal(year),
                                RecordId       = Guid.NewGuid().ToString(),
                                CreatedBy      = Convert.ToDecimal(HttpContext.CurrentUser(cache).Id),
                                CreationDate   = DateTime.Now,
                                LastUpdateDate = DateTime.Now,
                                LastUpdatedBy  = Convert.ToDecimal(HttpContext.CurrentUser(cache).Id),
                                Count          = groupdata_1.Where(f => f.ORGCODE.Equals(item.ORGCODE)).Count()
                            };
                            context.ApdFctLandTown2.Add(t2);

                            foreach (var itemdetail in groupdata_1.Where(f => f.ORGCODE.Equals(item.ORGCODE)))
                            {
                                ApdFctLandTown t1 = new ApdFctLandTown()
                                {
                                    OrgCode        = itemdetail.ORGCODE,
                                    OwnershipLand  = itemdetail.OWNERSHIPLAND,
                                    ProtectionLand = itemdetail.PROTECTIONLAN,
                                    ReduceLand     = itemdetail.REDUCELAND,
                                    CreatedBy      = Convert.ToDecimal(HttpContext.CurrentUser(cache).Id),
                                    CreationDate   = DateTime.Now,
                                    LastUpdateDate = DateTime.Now,
                                    LastUpdatedBy  = Convert.ToDecimal(HttpContext.CurrentUser(cache).Id),
                                    PeriodYear     = DateTime.Now.Year,
                                    RecordId       = Guid.NewGuid().ToString(),
                                    T2Id           = t2.RecordId
                                };
                                context.ApdFctLandTown.Add(t1);
                            }
                        }



                        using (IDbContextTransaction trans = context.Database.BeginTransaction())
                        {
                            try
                            {
                                context.SaveChangesAsync();
                                trans.Commit();
                            }
                            catch (Exception ex)
                            {
                                trans.Rollback();
                                throw new Exception("error", ex);
                            }
                        }

                        //将groupdata_1、groupdata_2写入到两张表
                    }
                    else
                    {
                        result.IsSuccess = false;
                        result.Message   = "excel无数据!";
                    }
                }



                return(result);
            }
            catch (Exception ex)
            {
                LogService.WriteError(ex);
                result.IsSuccess = false;
                result.Message   = $"{ex.Message},{ex.InnerException}";
                return(result);
            }
        }