public async System.Threading.Tasks.Task ExecuteWithResult() { try { Console.WriteLine("Running resource import sample"); IAuthenticator authenticator = new FormsAuthenticator (DimeSchedulerCredentials.Uri, DimeSchedulerCredentials.User, DimeSchedulerCredentials.Password); DimeSchedulerClient client = new(DimeSchedulerCredentials.Uri, authenticator); IImportEndpoint importEndpoint = await client.Import.Request(); string[] departments = { "Sales", "Marketing", "R&D", "IT", "Warehouse", "Planning", "Accounting" }; string[] teams = { "Team A", "Team B", "Team C", "Team D", "Team E", "Team F" }; for (int i = 0; i < 20; i++) { Console.WriteLine("Importing resource " + i + 1); Import.Resource resourceRequest = ResourceFactory.Create(); ImportSet resourceImport = await importEndpoint.ProcessAsync(resourceRequest, TransactionType.Append); } Console.WriteLine("All done!"); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } }
public async System.Threading.Tasks.Task ExecuteWithResult() { try { IAuthenticator authenticator = new FormsAuthenticator (DimeSchedulerCredentials.Uri, DimeSchedulerCredentials.User, DimeSchedulerCredentials.Password); DimeSchedulerClient client = new(DimeSchedulerCredentials.Uri, authenticator); IImportEndpoint importEndpoint = await client.Import.Request(); Job jobRequest = CreateJobRequest(); ImportSet jobImport = await importEndpoint.ProcessAsync(jobRequest, TransactionType.Append); for (int i = 0; i < 10; i++) { Task taskRequest = CreateTaskRequest(jobRequest, i); Console.WriteLine($"Importing task {taskRequest.TaskNo} to DS"); ImportSet taskImport = await importEndpoint.ProcessAsync(taskRequest, TransactionType.Append); } } catch (Exception ex) { Console.WriteLine(ex.ToString()); } }
public async System.Threading.Tasks.Task Execute() { try { Console.WriteLine("Running appointment import sample"); IAuthenticator authenticator = new FormsAuthenticator (DimeSchedulerCredentials.Uri, DimeSchedulerCredentials.User, DimeSchedulerCredentials.Password); DimeSchedulerClient client = new(DimeSchedulerCredentials.Uri, authenticator); IImportEndpoint importEndpoint = await client.Import.Request(); Appointment appointmentRequest = new Faker <Appointment>() .RuleFor(x => x.SourceApp, f => "CRONUSBE") .RuleFor(x => x.SourceType, f => "JOB") .RuleFor(x => x.JobNo, f => "J00001") .RuleFor(x => x.TaskNo, f => "00001") .RuleFor(x => x.ResourceNo, f => "BULENS") .RuleFor(x => x.Start, f => DateTime.Now) .RuleFor(x => x.End, f => DateTime.Now.AddHours(4)) .RuleFor(x => x.Subject, f => f.Lorem.Paragraph().ClampLength(1, 100)) .RuleFor(x => x.Body, f => f.Lorem.Paragraph().ClampLength(1, 100)) .Generate(); ImportSet appointmentImport = await importEndpoint.ProcessAsync(appointmentRequest, TransactionType.Append); Console.WriteLine("All done!"); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } }
public ActionResult Import(ImportSet imp) { imp.TableName = "SaleOrders"; var op = _saleImportService.Import(imp, Request.Files, Request["FieldName"], Request["ColumnName"]); return(this.ToJsonOperateResult(op)); }
public ActionResult Import(ImportSet imp) { imp.TableName = "ProductRecord"; var op = ProductService.Import(imp, Request.Files, Request["FieldName"], Request["ColumnName"]); return(Content(op.ToJson())); }
public ActionResult Import(ImportSet imp) { imp.TableName = "ProductBrand"; var op = BrandService.Import(imp, Request.Files, Request["FieldName"], Request["ColumnName"]); return(new OpActionResult(op)); }
/// <summary> /// Will read Configuration file if passed, or default COBieAttributesFilters.config /// </summary> /// <param name="configFileName">Full path/name for config file</param> /// <param name="import"></param> private void FiltersHelperInit(string configFileName = null, ImportSet import = ImportSet.All) { //set default var sourceFile = configFileName ?? RoleFilter.Unknown.ToResourceName(); var config = GetConfig(sourceFile); //IfcProduct and IfcTypeObject filters if (import == ImportSet.All || import == ImportSet.IfcFilters) { IfcProductFilter = new ObjectFilter(config.GetSection("IfcElementInclusion")); IfcTypeObjectFilter = new ObjectFilter(config.GetSection("IfcTypeInclusion")); IfcTypeObjectFilter.FillPreDefinedTypes(config.GetSection("IfcPreDefinedTypeFilter")); IfcAssemblyFilter = new ObjectFilter(config.GetSection("IfcAssemblyInclusion")); } //Property name filters if (import == ImportSet.All || import == ImportSet.PropertyFilters) { ZoneFilter = new PropertyFilter(config.GetSection("ZoneFilter")); TypeFilter = new PropertyFilter(config.GetSection("TypeFilter")); SpaceFilter = new PropertyFilter(config.GetSection("SpaceFilter")); FloorFilter = new PropertyFilter(config.GetSection("FloorFilter")); FacilityFilter = new PropertyFilter(config.GetSection("FacilityFilter")); SpareFilter = new PropertyFilter(config.GetSection("SpareFilter")); ComponentFilter = new PropertyFilter(config.GetSection("ComponentFilter")); CommonFilter = new PropertyFilter(config.GetSection("CommonFilter")); } // API restructure: // a call to File.Delete(config.FilePath); has been removed // it is strange for the configuration reading routine to delete a configuration file }
public ActionResult Import(ImportSet imp) { imp.TableName = "Wholesaler"; imp.CompanyId = CommonService.CompanyId; var op = SupplierService.Import(imp, Request.Files, Request["FieldName"], Request["ColumnName"], 2); return(Content(op.ToJson())); }
public ActionResult ImportPrice(ImportSet imp) { imp.TableName = "ProductPriceUpdate"; imp.CompanyId = CommonService.CompanyId; var op = ProductService.ImportPrice(imp, Request.Files, Request["FieldName"], Request["ColumnName"]); return(Content(op.ToJson())); }
/// <summary> /// Choose a sample by passing an argument to this executable. /// Examples: /// - To run the resource import sample, run: Dime.Scheduler.Sdk.Samples.exe -r /// - To run the resource and task import samples, run: Dime.Scheduler.Sdk.Samples.exe -r -t /// </summary> /// <param name="args"></param> public static void Main(string[] args) { Parser .Default .ParseArguments <SampleOptions>(args) .WithParsed(async o => { if (o.Task) { TaskImportRequestSample taskImportSample = new(); await taskImportSample.ExecuteWithResult(); } if (o.ExchangeAppointment) { ExchangeAppointmentImportRequestSample sample = new(); ImportSet set = await sample.ExecuteWithResult(); } if (o.Message) { MessagesRequestSample messageRequestSample = new(); await messageRequestSample.Execute(); } if (o.Filter) { FilterImportRequestSample filterSample = new(); await filterSample.ExecuteWithResult(); } if (o.ResourceGpsTracking) { ResourceGpsTrackingImportRequestSample gpsTrackingSample = new(); await gpsTrackingSample.Execute(); } if (o.Notification) { NotificationImportRequestSample gpsTrackingSample = new(); await gpsTrackingSample.Execute(); } if (o.Project) { ProjectImportRequestSample projectImportRequestSample = new(); await projectImportRequestSample.Execute(); } if (o.Appointment) { AppointmentImportRequestSample appointmentImportRequestSample = new(); await appointmentImportRequestSample.Execute(); } }); Console.ReadLine(); }
/// <summary> /// 导入 /// </summary> /// <returns></returns> public ActionResult Import() { var obj = ImportSetService.GetOne("Devices"); if (obj == null) { obj = new ImportSet(); } obj.RefCreate = false; return(View(obj)); }
public static async Task Main(string[] args) { ImportRequestSample sample = new(); ImportSet set = await sample.ExecuteWithResult(); MessagesRequestSample messageRequestSample = new(); await messageRequestSample.Execute(); ResourcesRequestSample resourcesRequestSample = new(); await resourcesRequestSample.Execute(); Console.ReadLine(); }
public ActionResult UpLoad() { ImportSet importSet = new ImportSet(); List <ImportModel.rawImport> recordsIn = new List <ImportModel.rawImport>(); List <ImportModel.Batch> batchIn = new List <ImportModel.Batch>(); //List<ImportModel.Pannebakker> existingRecords = new List<ImportModel.Pannebakker>(); //List<ImportModel.rawImport> newRecords = new List<ImportModel.rawImport>(); try { importSet = ReadInputFile(); recordsIn = importSet.rawImport; batchIn = importSet.batchImport; // existingRecords = db.GetPannebakkers().ToList(); // newRecords = recordsIn.Union(existingRecords).ToList(); // newRecords = existingRecords.Union(recordsIn, new DTO.PbComparer()).ToList(); } catch (Exception ex) { //ViewBag.Error = ex.InnerException.Message; return(View("version")); } try { // "TRUNCATE TABLE [rawImport]"]"); RepDb.EmptyImport(); // db.BulkInsert<Pannebakker>(newRecords); RepDb.BulkInsertIntoImport(recordsIn); RepDb.BulkInsertGMBatch(batchIn); // RepDb.RemoveDuplicateNames(); //AddBatch(records); RepDb.MergeBatchToNames(); RepDb.RemoveDuplicateNames(); ViewBag.Title = "done"; Response.Write("<script>console.log('Data has been saved to db');</script>"); //return View("Index"); return(RedirectToAction("Index")); } catch (Exception ex) { ViewBag.Error = ex.InnerException.Message; return(View("shit")); } }
private ImportService.DTO.ImportSet ReadInputFile() { ImportService.DTO.ImportSet outputSet = new ImportSet(); List <ImportModel.rawImport> recordsIn = new List <ImportModel.rawImport>(); List <ImportModel.Batch> batchIn = new List <ImportModel.Batch>(); var fred = TempData["path"].ToString(); //var filesData = Directory.GetFiles(@fred); string path = Server.MapPath("~/App_Data/" + fred); //string path = Server.MapPath(fred.ToString()); var package = new OfficeOpenXml.ExcelPackage(new FileInfo(path)); OfficeOpenXml.ExcelWorksheet workSheet = package.Workbook.Worksheets[1]; for (int row = workSheet.Dimension.Start.Row; row <= workSheet.Dimension.End.Row; row++) { if (HasData(workSheet, row)) { ImportModel.rawImport obj = new ImportModel.rawImport(); ImportModel.Batch batch = new Batch(); obj.Sku = GetPBSKU(workSheet, row); batch.Sku = obj.Sku; obj.FormSizeCode = ""; obj.Name = GetName(workSheet, row); batch.Name = obj.Name; obj.FormSize = GetSize(workSheet, row) + " " + GetForm(workSheet, row); batch.FormSize = obj.FormSize; var price = GetPrice(workSheet, row); obj.Price = Convert.ToDecimal(price); batch.WholesalePrice = System.Convert.ToInt32(obj.Price * 100); batch.Active = true; batch.GrowingQuantity = System.Convert.ToInt32(GetStockLevel(workSheet, row)); batch.DateStamp = DateTime.Now; batch.AllocatedQuantity = 0; batch.ImageExists = false; batch.Location = GetLocation(workSheet, row); recordsIn.Add(obj); batchIn.Add(batch); } } outputSet.rawImport = recordsIn; outputSet.batchImport = batchIn; return(outputSet); }
/// <summary> /// Will read Configuration file if passed, or default COBieAttributesFilters.config /// </summary> /// <param name="configFileName">Full path/name for config file</param> private void FiltersHelperInit(ImportSet import, string configFileName = null) { string resFile = configFileName; //set default if (resFile == null) { resFile = "Xbim.COBieLiteUK.FilterHelper.COBieDefaultFilters.config"; } Configuration config = GetResourceConfig(resFile); //IfcProduct and IfcTypeObject filters if ((import == ImportSet.All) || (import == ImportSet.IfcFilters)) { IfcProductFilter = new ObjectFilter(config.GetSection("IfcElementInclusion")); IfcTypeObjectFilter = new ObjectFilter(config.GetSection("IfcTypeInclusion")); IfcTypeObjectFilter.FillPreDefinedTypes(config.GetSection("IfcPreDefinedTypeFilter")); IfcAssemblyFilter = new ObjectFilter(config.GetSection("IfcAssemblyInclusion")); } //Property name filters if ((import == ImportSet.All) || (import == ImportSet.PropertyFilters)) { ZoneFilter = new PropertyFilter(config.GetSection("ZoneFilter")); TypeFilter = new PropertyFilter(config.GetSection("TypeFilter")); SpaceFilter = new PropertyFilter(config.GetSection("SpaceFilter")); FloorFilter = new PropertyFilter(config.GetSection("FloorFilter")); FacilityFilter = new PropertyFilter(config.GetSection("FacilityFilter")); SpareFilter = new PropertyFilter(config.GetSection("SpareFilter")); ComponentFilter = new PropertyFilter(config.GetSection("ComponentFilter")); CommonFilter = new PropertyFilter(config.GetSection("CommonFilter")); } if (configFileName == null) { File.Delete(config.FilePath); } }
public OperateResult ImportSet(ImportSet obj, HttpFileCollectionBase httpFiles, string fieldName, string columnName, ref Dictionary <string, char> fieldCols, ref DataTable dt) { if (httpFiles.Count <= 0 || httpFiles[0].ContentLength <= 0) { return(OperateResult.Fail("请先选择Excel文件")); } var stream = httpFiles[0].InputStream; var ext = httpFiles[0].FileName.Substring(httpFiles[0].FileName.LastIndexOf(".")); if (!(ext.Equals(".xls", StringComparison.CurrentCultureIgnoreCase) || ext.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))) { return(OperateResult.Fail("请先选择Excel文件")); } var path = ""; var fullPath = FileHelper.SaveAttachPath(ref path, "temps"); httpFiles[0].SaveAs(fullPath + httpFiles[0].FileName); fieldCols = fieldCols ?? new Dictionary <string, char>(); if (!fieldName.IsNullOrEmpty() && !columnName.IsNullOrEmpty()) { var fields = fieldName.Split(','); var columns = columnName.Split(','); if (fields.Length != columns.Length) { return(OperateResult.Fail("配置的字段和列数不一致")); } for (var i = 0; i < fields.Length; i++) { if (columns[i].IsNullOrEmpty()) { continue; } fieldCols[fields[i]] = Convert.ToChar(columns[i]); } //if (fieldCols.Values.Distinct().Count() != fieldCols.Values.Count()) //{ // op.Message = "配置的列存在重复!"; // return op; //} obj.FieldJson = fieldCols.Select(o => new { o.Key, o.Value }).ToJson(); } _importSetRepository.AddOrUpdate(obj); dt = new ExportExcel().ToDataTable(stream, minRow: obj.MinRow, maxRow: obj.MaxRow.GetValueOrDefault()); if (dt == null || dt.Rows.Count <= 0) { return(OperateResult.Fail("无数据,无法导入!")); } #region 去掉空格 foreach (DataColumn dc in dt.Columns) { if (dc.DataType == typeof(string)) { foreach (DataRow dr in dt.Rows) { if (!(dr[dc] is DBNull)) { dr[dc] = dr[dc].ToString().Trim(); } } } } #endregion #region 允许配置在同一列 var cols = fieldCols.GroupBy(o => o.Value).Where(o => o.Count() > 1).ToList();//取重复列 foreach (var item in cols) { System.Diagnostics.Debug.WriteLine(item.Key);//重复列value var idx = Convert.ToInt32(item.Key) - 65; foreach (var subitem in item) { System.Diagnostics.Debug.WriteLine(subitem.Key);//重复列key var lastValue = Convert.ToChar(fieldCols.Values.OrderBy(o => o).LastOrDefault() + 1); if (dt.Columns[idx].CloneTo(subitem.Key)) { fieldCols[subitem.Key] = lastValue; } } } #endregion return(OperateResult.Success()); }
public async System.Threading.Tasks.Task Execute() { try { Console.WriteLine("Running resource import sample"); IAuthenticator authenticator = new FormsAuthenticator (DimeSchedulerCredentials.Uri, DimeSchedulerCredentials.User, DimeSchedulerCredentials.Password); DimeSchedulerClient client = new(DimeSchedulerCredentials.Uri, authenticator); IImportEndpoint importEndpoint = await client.Import.Request(); // Ensure the resource exists Import.Resource resourceRequest = ResourceFactory.Create(); resourceRequest.ResourceGpsTrackingEnabled = true; Random random = new(); string color = $"#{random.Next(0x1000000):X6}"; Pin pin = new() { Name = "Pin for " + resourceRequest.ResourceNo, Color = color }; await importEndpoint.ProcessAsync(pin, TransactionType.Append); resourceRequest.Pin = pin.Name; ImportSet resourceImport = await importEndpoint.ProcessAsync(resourceRequest, TransactionType.Append); List <(decimal, decimal)> coordinates = new() { (40.779555M, -73.973577M), // Central Park West & W 77th (40.785286M, -73.969350M), // Central Park West & W 86th (40.794248M, -73.962958M), // Central Park West & W 100th (40.800398M, -73.958047M), // Central Park West & Central Park North (40.799518M, -73.955355M), // Central Park North & Adam Clayton Powell Jr Blvd (40.798223M, -73.952388M), // Central Park North & Malcolm X Blvd (40.796643M, -73.949432M), // Central Park North & 5th Ave (40.794328M, -73.951068M), // 5th Ave & E 106th St (40.794328M, -73.951068M), // 5th Ave & E 101th St (40.786605M, -73.956753M), // 5th Ave & E 94th St (40.780868M, -73.960957M), // 5th Ave & E 85th St (40.773795M, -73.966148M), // 5th Ave & E 74th St (40.769919M, -73.968967M), // 5th Ave & E 68th St (40.765517M, -73.972142M), // 5th Ave & E 61th St (40.764316M, -73.973075M), // 5th Ave & E 50th St }; for (int i = 0; i < coordinates.Count; i++) { Console.WriteLine("Updating GPS location " + (i + 1)); (decimal lat, decimal lng) = coordinates.ElementAt(i); ResourceGpsTracking resourceGpsTrackingRequest = new() { ResourceNo = resourceRequest.ResourceNo, Latitude = lat, Longitude = lng }; ImportSet resourceCoordinatesImport = await importEndpoint.ProcessAsync(resourceGpsTrackingRequest, TransactionType.Append); Thread.Sleep(5000); // Sleep 2 seconds } Console.WriteLine("All done!"); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } }
public OutPutFilters(string configFileName, ILogger logger, RoleFilter roleFlags, ImportSet setsToImport = ImportSet.All) : base(configFileName, logger, roleFlags, setsToImport) { }
public static OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName, short businessType) { var op = new OpResult(); var errLs = new List <string>(); int count = 0; try { Dictionary <string, char> fieldCols = null; DataTable dt = null; op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt); if (!op.Successed) { return(op); } var supplierClass = SysDataDictService.FindList(o => o.DicPSN == (int)DicType.供应商分类); var otherClass = supplierClass.FirstOrDefault(o => o.Title.StartsWith("其")); var suppliers = GetList(); var users = UserInfoService.GetList(); var max = SysDataDictService.GetMaxSN; var clsIdx = Convert.ToInt32(fieldCols["ClassifyId"]) - 65; var titleIdx = Convert.ToInt32(fieldCols["Title"]) - 65; var fulltitleIdx = Convert.ToInt32(fieldCols["FullTitle"]) - 65; var linkmanIdx = Convert.ToInt32(fieldCols["Linkman"]) - 65; var designeeIdx = Convert.ToInt32(fieldCols["Designee"]) - 65; count = dt.Rows.Count; for (int i = dt.Rows.Count - 1; i >= 0; i--) { try { var dr = dt.Rows[i]; var text = dr[clsIdx].ToString(); if (text.IsNullOrEmpty()) { errLs.Add("行号[" + i + "]供应商分类为空!"); dt.Rows.RemoveAt(i);//去除不导入 continue; } var cls = supplierClass.FirstOrDefault(o => o.Title == text); if (cls != null) { dr[clsIdx] = cls.DicSN.ToString(); } else { if (obj.RefCreate) { var data = new Sys.Entity.SysDataDictionary() { DicPSN = (int)DicType.供应商分类, DicSN = max++, Status = true, Title = text, CompanyId = CommonService.CompanyId }; SysDataDictService.Add(data); supplierClass.Add(data); dr[clsIdx] = data.DicSN.ToString(); } else if (otherClass != null) { dr[clsIdx] = otherClass.DicSN.ToString(); } else { errLs.Add("供应商分类[" + text + "]不存在!"); dt.Rows.RemoveAt(i);//去除不导入 continue; } } text = dr[designeeIdx].ToString(); var user = users.FirstOrDefault(o => o.FullName == text); if (user != null) { dr[designeeIdx] = user.UID; } else { errLs.Add("指派人[" + text + "]不存在!"); dt.Rows.RemoveAt(i); continue; } text = dr[titleIdx].ToString().Trim(); var fulltext = dr[fulltitleIdx].ToString().Trim(); var linkman = dr[linkmanIdx].ToString().Trim(); if (text.IsNullOrEmpty()) { errLs.Add("行号[" + text + "]简称为空!"); dt.Rows.RemoveAt(i); } else if (fulltext.IsNullOrEmpty()) { errLs.Add("简称[" + text + "]全称为空!"); dt.Rows.RemoveAt(i); } else if (linkman.IsNullOrEmpty()) { errLs.Add("简称[" + text + "]联系人为空!"); dt.Rows.RemoveAt(i); } else if (suppliers.Any(o => o.Title == text)) { errLs.Add("简称[" + text + "]已存在!"); dt.Rows.RemoveAt(i); } else if (suppliers.Any(o => o.FullTitle == fulltext)) { errLs.Add("全称[" + fulltext + "]已存在!"); dt.Rows.RemoveAt(i); } else { suppliers.Add(new Supplier() { Title = text, FullTitle = fulltext }); } } catch (Exception e) { throw new Exception("供应商分类处理失败!", e); } } StringBuilder sb = new StringBuilder(); sb.Append("begin tran "); foreach (DataRow dr in dt.Rows) { sb.Append("insert into Supplier"); sb.Append("(CompanyId,BusinessType,Id,"); sb.Append(string.Join(",", fieldCols.Keys)); sb.Append(") values("); sb.AppendFormat("{0},", obj.CompanyId); sb.AppendFormat(businessType + ","); sb.AppendFormat("'{0}',", CommonRules.GUID); foreach (var de in fieldCols) { var index = Convert.ToInt32(de.Value) - 65; try { var text = dr[index].ToString(); sb.Append("'" + text + "',"); } catch (Exception e) { throw new Exception("列选择超过范围!", e); } } sb = sb.Remove(sb.Length - 1, 1); sb.Append(");"); } sb.Append(" commit tran"); op.Successed = new Pharos.Logic.DAL.CommonDAL()._db.ExecuteNonQueryText(sb.ToString(), null) > 0; log.WriteInsert(obj.TableName == "Supplier" ? "供应商导入" : "批发商导入", Pharos.Sys.LogModule.档案管理); } catch (Exception ex) { op.Message = ex.Message; op.Successed = false; Log.WriteError(ex); errLs.Add("导入出现异常!"); } return(CommonService.GenerateImportHtml(errLs, count)); }
public static OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName) { var op = new OpResult(); var errLs = new List <string>(); int count = 0, rowno = 0; var dt = new DataTable(); try { Dictionary <string, char> fieldCols = null; op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt); if (!op.Successed) { return(op); } var users = UserInfoService.GetList(); var barcodeIdx = Convert.ToInt32(fieldCols["Barcode"]) - 65; var barcodes = dt.AsEnumerable().Select(o => o[barcodeIdx].ToString()).Distinct().ToList(); var payments = new List <ConsumptionPayment>(); foreach (var de in fieldCols) { var idx = Convert.ToInt32(de.Value) - 65; var col = dt.Columns[idx]; if (!dt.Columns.Contains(de.Key)) { col.ColumnName = de.Key; } } dt.Columns.Add("ApiTitle"); dt.Columns.Add("ValuationType"); dt.Columns.Add("ProductCode"); dt.Columns.Add("BuyPrice"); dt.Columns.Add("StoreId"); dt.Columns.Add("SalesClassifyId2"); dt.Columns.Add("CreateUID2"); dt.Columns.Add("Salesman2"); dt.Columns.Add("InInventory"); dt.Columns.Add("Sort", typeof(int)); object ApiOrderSN = "", ApiTitle = "", ApiCode_11 = 0, ApiCode_12 = 0, ApiCode_20 = 0, ApiCode_21 = 0, ApiCode_19 = 0, PreferentialPrice = 0, Change = 0, WipeZero = 0, TotalAmount = 0, Type = 0; int sort = 1; if (!dt.Columns.Contains("ApiOrderSN")) { dt.Rows.Clear(); errLs.Add("销售流水号位置未指定!"); } else { var apiOrderSns = dt.AsEnumerable().Select(o => o["ApiOrderSN"].ToString()).Distinct().Where(o => !o.IsNullOrEmpty()).ToList(); payments = ConsumptionPaymentService.FindList(o => apiOrderSns.Contains(o.ApiOrderSN)); } if (!dt.Columns.Contains("SaleDate")) { dt.Columns.Add("SaleDate", typeof(DateTime)); foreach (DataRow dr in dt.Rows) { dr["SaleDate"] = DateTime.Now; } } for (int i = 0; i < dt.Rows.Count; i++) { var dr = dt.Rows[i]; var ApiOrderSNCur = dr.GetValue("ApiOrderSN"); var ChangeCur = dr.GetValue("Change"); var ApiCode_11Cur = dr.GetValue("ApiCode_11"); var ApiCode_12Cur = dr.GetValue("ApiCode_12"); var ApiCode_20Cur = dr.GetValue("ApiCode_20"); var ApiCode_21Cur = dr.GetValue("ApiCode_21"); var ApiCode_19Cur = dr.GetValue("ApiCode_19"); var PreferentialPriceCur = dr.GetValue("PreferentialPrice"); var WipeZeroCur = dr.GetValue("WipeZero"); var TotalAmountCur = dr.GetValue("TotalAmount"); var TypeCur = dr.GetValue("Type"); var apiTitleCur = ""; if (!(ApiCode_11Cur is DBNull)) { apiTitleCur += "现金,"; } if (!(ApiCode_12Cur is DBNull)) { apiTitleCur += "银联,"; } if (!(ApiCode_20Cur is DBNull)) { apiTitleCur += "支付宝,"; } if (!(ApiCode_21Cur is DBNull)) { apiTitleCur += "微信,"; } if (!(ApiCode_19Cur is DBNull)) { apiTitleCur += "即付宝,"; } if (dr.GetValue("ApiOrderSN") is DBNull) { dr["ApiOrderSN"] = ApiOrderSN; dr.SetValue("Change", Change); dr.SetValue("ApiCode_11", ApiCode_11); dr.SetValue("ApiCode_12", ApiCode_12); dr.SetValue("ApiCode_20", ApiCode_20); dr.SetValue("ApiCode_21", ApiCode_21); dr.SetValue("ApiCode_19", ApiCode_19); dr.SetValue("PreferentialPrice", PreferentialPrice); dr.SetValue("WipeZero", WipeZero); dr.SetValue("TotalAmount", TotalAmount); dr.SetValue("ApiTitle", ApiTitle); dr.SetValue("Type", Type); sort++; } else { ApiOrderSN = ApiOrderSNCur; Change = ChangeCur; ApiCode_11 = ApiCode_11Cur; ApiCode_12 = ApiCode_12Cur; ApiCode_20 = ApiCode_20Cur; ApiCode_21 = ApiCode_21Cur; ApiCode_19 = ApiCode_19Cur; PreferentialPrice = PreferentialPriceCur; WipeZero = WipeZeroCur; TotalAmount = TotalAmountCur; dr["ApiTitle"] = ApiTitle = apiTitleCur.TrimEnd(','); sort = 1; Type = TypeCur; } //dr["Receive"] = (ApiCode_11Cur is DBNull ? 0 : ApiCode_11Cur.ToType<decimal>()) + // (ApiCode_12Cur is DBNull ? 0 : ApiCode_12Cur.ToType<decimal>()) + // (ApiCode_20Cur is DBNull ? 0 : ApiCode_20Cur.ToType<decimal>()) + // (ApiCode_21Cur is DBNull ? 0 : ApiCode_21Cur.ToType<decimal>()) + // (ApiCode_19Cur is DBNull ? 0 : ApiCode_19Cur.ToType<decimal>()) - // (ChangeCur is DBNull ? 0 : ChangeCur.ToType<decimal>()); dr["StoreId"] = System.Web.HttpContext.Current.Request["storeId"]; dr["InInventory"] = System.Web.HttpContext.Current.Request["InInventory"]; dr["Sort"] = sort; } var removeDrs = new List <DataRow>(); var apisns = dt.AsEnumerable().GroupBy(o => o["ApiOrderSN"].ToString()).Select(o => o.Key).ToList(); var products = ProductService.GetProductsBybars(barcodes); count = apisns.Count; rowno = obj.MinRow; for (int i = 0; i < apisns.Count; i++) { var apiSn = apisns[i]; var drs = dt.Select("ApiOrderSN='" + apiSn + "'"); if (payments.Any(o => o.ApiOrderSN == apiSn)) { errLs.Add("行号[" + rowno + "] 流水号[" + apiSn + "]已存在!"); removeDrs.AddRange(drs); rowno += drs.Length; continue; } var receive = drs.Max(o => o.GetValue("Receive").ToType <decimal>()); int j = 0; decimal totalAmount = 0; bool haszs = false; foreach (DataRow dr in drs) { var text = dr["Barcode"].ToString(); if (text.IsNullOrEmpty()) { errLs.Add("行号[" + (rowno + j) + "]条码不存在!"); removeDrs.AddRange(drs); break; } //var pro = products.FirstOrDefault(o => o.Barcode == text || ("," + o.Barcodes + ",").Contains("," + text + ",")); var pro = products.Select(string.Format("Barcode='{0}' or ','+Barcodes+',' like '%,{0},%'", text)); if (pro.Length <= 0) { errLs.Add("行号[" + (rowno + j) + "] 流水号[" + apiSn + "] 条码[" + text + "]档案不存在!"); removeDrs.AddRange(drs); break; } dr["ProductCode"] = pro[0]["ProductCode"]; dr["BuyPrice"] = pro[0]["BuyPrice"]; dr["ValuationType"] = pro[0]["ValuationType"]; text = dr.GetValue("Type").ToString(); dr.SetValue("Type", text == "换货" ? "1" : text == "退货" ? "2" : text == "退单" ? "3" : "0"); text = dr.GetValue("CreateUID").ToString(); if (!text.IsNullOrEmpty() && users.Any(o => o.FullName == text || o.UserCode == text)) { var user = users.FirstOrDefault(o => o.FullName == text || o.UserCode == text); dr["CreateUID2"] = user == null ? "" : user.UID; if (user == null) { dr["CreateUID"] = ""; } } else { errLs.Add("行号[" + (rowno + j) + "] 流水号[" + apiSn + "] 收银员" + (text.IsNullOrEmpty() ? "为空" : "[" + text + "]档案不存在!")); removeDrs.AddRange(drs); break; } text = dr.GetValue("Salesman").ToString(); if (!text.IsNullOrEmpty() && users.Any(o => o.FullName == text || o.UserCode == text)) { var user = users.FirstOrDefault(o => o.FullName == text || o.UserCode == text); dr["Salesman2"] = user == null ? "" : user.UID; if (user == null) { dr["Salesman"] = ""; } } else { errLs.Add("行号[" + (rowno + j) + "] 流水号[" + apiSn + "] 导购员" + (text.IsNullOrEmpty() ? "为空" : "[" + text + "]档案不存在!")); removeDrs.AddRange(drs); break; } text = dr.GetValue("SalesClassifyId").ToString(); dr["SalesClassifyId2"] = text == "赠送" ? "49" : "47"; if (text == "赠送") { dr.SetValue("SubTotal", 0); dr.SetValue("ActualPrice", 0); if (receive == 0) { dr.SetValue("TotalAmount", 0); } haszs = true; } totalAmount += dr.GetValue("ActualPrice").ToType <decimal>() * dr.GetValue("PurchaseNumber").ToType <decimal>(); j++; } if (receive > 0 && haszs) { drs.Each(dr => { dr.SetValue("TotalAmount", totalAmount);//重算应付金额 }); } rowno += drs.Length; } foreach (var dr in removeDrs) { try { dt.Rows.Remove(dr); } catch { } } saleOrderCache.Set(CacheKey, dt); } catch (Exception ex) { op.Message = ex.Message; op.Successed = false; Log.WriteError(ex); errLs.Add("导入出现异常!"); } return(CommonService.GenerateImportHtml(errLs, count)); }
public static OpResult OutboundImport(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName) { var op = new OpResult(); var errLs = new List <string>(); int count = 0; var list = new List <OutboundList>(); try { Dictionary <string, char> fieldCols = null; DataTable dt = null; op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt); if (!op.Successed) { return(op); } var storeId = System.Web.HttpContext.Current.Request["StoreId"]; var products = new List <VwProduct>(); if (!storeId.IsNullOrEmpty()) { var ware = WarehouseService.Find(o => o.StoreId == storeId && o.CompanyId == CommonService.CompanyId); if (ware != null) { var categorySNs = ware.CategorySN.Split(',').Select(o => int.Parse(o)).ToList(); var childs = ProductCategoryService.GetChildSNs(categorySNs); var pros = BaseService <VwProduct> .FindList(o => o.CompanyId == CommonService.CompanyId && childs.Contains(o.CategorySN)); products.AddRange(pros); } } var barcodeIdx = fieldCols.GetValue("Barcode").ToType <int>() - 65; var outPriceIdx = fieldCols.GetValue("OutPrice").ToType <int>() - 65; var numberIdx = fieldCols.GetValue("OutboundNumber").ToType <int>() - 65; var memoIdx = fieldCols.GetValue("Memo").ToType <int>() - 65; count = dt.Rows.Count; for (int i = 0; i < dt.Rows.Count; i++) { var pos = i + obj.MinRow; var dr = dt.Rows[i]; var barcode = dr.GetValue(barcodeIdx).ToString().Trim(); if (barcode.IsNullOrEmpty()) { errLs.Add("行号[" + pos + "]条码为空!"); continue; } var pro = products.FirstOrDefault(o => o.Barcode == barcode || ("," + o.Barcodes + ",").Contains("," + barcode + ",")); if (pro == null) { errLs.Add("行号[" + pos + "]该门店下无此条码!"); continue; } var outPrice = dr.GetValue(outPriceIdx).ToType <decimal?>(); if (!outPrice.HasValue && outPriceIdx >= 0) { errLs.Add("行号[" + pos + "]该条码价格为空!"); continue; } var number = dr.GetValue(numberIdx).ToType <decimal?>(); if (!number.HasValue) { errLs.Add("行号[" + pos + "]该条码出库数量为空!"); continue; } list.Add(new OutboundList() { Barcode = barcode, ProductTitle = pro.Title, BuyPrice = pro.BuyPrice, OutboundNumber = number.Value, Unit = pro.SubUnit, SysPrice = pro.SysPrice, OutPrice = outPrice ?? pro.SysPrice, Memo = dr.GetValue(memoIdx).ToString() }); } } catch (Exception ex) { op.Message = ex.Message; op.Successed = false; Log.WriteError(ex); errLs.Add("导入出现异常!"); } return(CommonService.GenerateImportHtml(errLs, count, data: list, isSuccess: false)); }
public static OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName) { var op = new OpResult(); var errLs = new List <string>(); int count = 0; try { Dictionary <string, char> fieldCols = null; DataTable dt = null; op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt); if (!op.Successed) { return(op); } var brandClass = SysDataDictService.FindList(o => o.DicPSN == (int)DicType.品牌分类 && o.CompanyId == CommonService.CompanyId); var otherClass = brandClass.FirstOrDefault(o => o.Title.StartsWith("其")); var brands = GetAllProductBrands(null).ToList(); var max = SysDataDictService.GetMaxSN; var clsIdx = Convert.ToInt32(fieldCols["ClassifyId"]) - 65; var titleIdx = Convert.ToInt32(fieldCols["Title"]) - 65; count = dt.Rows.Count; for (int i = dt.Rows.Count - 1; i >= 0; i--) { try { var dr = dt.Rows[i]; var text = dr[clsIdx].ToString(); if (text.IsNullOrEmpty()) { continue; } var cls = brandClass.FirstOrDefault(o => o.Title == text); if (cls != null) { dr[clsIdx] = cls.DicSN.ToString(); } else { if (obj.RefCreate) { var data = new Sys.Entity.SysDataDictionary() { DicPSN = (int)DicType.品牌分类, DicSN = max++, Status = true, Title = text, CompanyId = CommonService.CompanyId }; SysDataDictService.Add(data); brandClass.Add(data); dr[clsIdx] = data.DicSN.ToString(); } else if (otherClass != null) { dr[clsIdx] = otherClass.DicSN.ToString(); } else { errLs.Add("品牌分类[" + text + "]不存在!"); dt.Rows.RemoveAt(i);//去除不导入 continue; } } text = dr[titleIdx].ToString().Trim(); if (brands.Any(o => o.Title == text)) { errLs.Add("品牌名称[" + text + "]已存在!"); dt.Rows.RemoveAt(i);//去除不导入 } else { brands.Add(new ProductBrand() { Title = text }); } } catch (Exception e) { throw new Exception("品牌分类处理失败!", e); } } max = MaxSN; StringBuilder sb = new StringBuilder(); sb.Append("begin tran "); foreach (DataRow dr in dt.Rows) { sb.Append("insert into "); sb.Append(obj.TableName); sb.Append("(CompanyId,BrandSN,State,"); sb.Append(string.Join(",", fieldCols.Keys)); sb.Append(") values("); sb.AppendFormat("{0},", obj.CompanyId); sb.AppendFormat("{0},", max++); sb.AppendFormat("1,"); foreach (var de in fieldCols) { var index = Convert.ToInt32(de.Value) - 65; try { var text = dr[index].ToString(); sb.Append("'" + text + "',"); } catch (Exception e) { throw new Exception("列选择超过范围!", e); } } sb = sb.Remove(sb.Length - 1, 1); sb.Append(");"); } sb.Append(" commit tran"); op.Successed = new Pharos.Logic.DAL.CommonDAL()._db.ExecuteNonQueryText(sb.ToString(), null) > 0; Log.WriteInsert("品牌导入", Sys.LogModule.档案管理); } catch (Exception ex) { op.Message = ex.Message; op.Successed = false; Log.WriteError(ex); errLs.Add("导入出现异常!"); } return(CommonService.GenerateImportHtml(errLs, count)); }
/// <summary> /// 导入 /// </summary> /// <param name="obj"></param> /// <param name="httpFiles"></param> /// <param name="fieldName"></param> /// <param name="columnName"></param> /// <returns></returns> public OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName) { var op = new OpResult(); var errLs = new List <string>(); int count = 0; try { Dictionary <string, char> fieldCols = null; DataTable dt = null; op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt); if (!op.Successed) { return(op); } //数据字典 var dataDictionary = SysDataDictionaryRepository.GetQuery(); //设备分类名称 var CIdx = Convert.ToInt32(fieldCols["CategoryId"]) - 65; //设备名称 var TitleIdx = Convert.ToInt32(fieldCols["Title"]) - 65; //品牌 var BrandIdx = Convert.ToInt32(fieldCols["Brand"]) - 65; //型号 var SpecIdx = Convert.ToInt32(fieldCols["Spec"]) - 65; count = dt.Rows.Count; for (int i = dt.Rows.Count - 1; i >= 0; i--) { try { var dr = dt.Rows[i]; #region 验证 //设备分类名称 var text = dr[CIdx].ToString().Trim(); if (text.IsNullOrEmpty()) { errLs.Add("行号[" + (i + 1) + "]设备分类为空!"); dt.Rows.RemoveAt(i); continue; } var dd = dataDictionary.Where(o => o.DicPSN == 197 && o.Title == text); if (!dd.Any()) { errLs.Add("行号[" + (i + 1) + "]设备分类在字典中不存在,请先添加!"); dt.Rows.RemoveAt(i); continue; } else { dr[CIdx] = dd.Select(o => o.DicSN).FirstOrDefault(); } //设备名称 var text2 = dr[TitleIdx].ToString().Trim(); if (text2.IsNullOrEmpty()) { errLs.Add("行号[" + (i + 1) + "]设备名称为空!"); dt.Rows.RemoveAt(i); continue; } //品牌 var text3 = dr[BrandIdx].ToString().Trim(); if (text3.IsNullOrEmpty()) { errLs.Add("行号[" + (i + 1) + "]品牌为空!"); dt.Rows.RemoveAt(i); continue; } //型号 var text4 = dr[SpecIdx].ToString().Trim(); if (text4.IsNullOrEmpty()) { errLs.Add("行号[" + (i + 1) + "]型号为空!"); dt.Rows.RemoveAt(i); continue; } //设备分类ID var cid = dataDictionary.Where(o => o.DicPSN == 197 && o.Title == text).Select(o => o.DicSN).FirstOrDefault(); var isExist = DevicesRepository.GetQuery(o => o.CategoryId == cid && o.Title == text2 && o.Brand == text3 && o.Spec == text4); if (isExist.Any()) { errLs.Add("行号[" + (i + 1) + "]设备记录已经存在!"); dt.Rows.RemoveAt(i); continue; } #endregion } catch (Exception e) { throw new Exception("创建相关记录失败," + e.Message, e); } } StringBuilder sb = new StringBuilder(); sb.Append("begin tran "); foreach (DataRow dr in dt.Rows) { Devices devices = new Devices(); devices.DeviceId = CommonService.GUID.ToUpper(); devices.CreateUID = CurrentUser.UID; devices.CreateDT = DateTime.Now; sb.Append("insert into "); sb.Append(obj.TableName); sb.Append("(Status,DeviceId,CreateDT,CreateUID,"); sb.Append(string.Join(",", fieldCols.Keys)); sb.Append(") values(1,'" + devices.DeviceId + "','" + devices.CreateDT + "','" + devices.CreateUID + "',"); foreach (var de in fieldCols) { var index = Convert.ToInt32(de.Value) - 65; try { var text = dr[index].ToString().Trim(); sb.Append("'" + text + "',"); } catch (Exception e) { throw new Exception("列选择超过范围!", e); } } sb = sb.Remove(sb.Length - 1, 1); sb.Append(");"); } sb.Append(" commit tran"); new DBFramework.DBHelper().ExecuteNonQueryText(sb.ToString(), null); } catch (Exception ex) { op.Message = ex.Message; op.Successed = false; LogEngine.WriteError(ex); errLs.Add("导入出现异常!"); } return(CommonService.GenerateImportHtml(errLs, count)); }
public static OpResult InboundImport(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName) { var op = new OpResult(); var errLs = new List <string>(); int count = 0; var list = new List <InboundList>(); try { Dictionary <string, char> fieldCols = null; DataTable dt = null; op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt); if (!op.Successed) { return(op); } var supplierId = System.Web.HttpContext.Current.Request["SupplierID"]; var storeId = System.Web.HttpContext.Current.Request["StoreId"]; var supplierBarcodes = new List <string>(); var storeBarcodes = new List <string>(); var products = new List <VwProduct>(); if (!supplierId.IsNullOrEmpty()) { var bars = BaseService <ProductMultSupplier> .FindList(o => o.SupplierId == supplierId).Select(o => o.Barcode).Distinct().ToList(); var pros = BaseService <VwProduct> .FindList(o => o.SupplierId == supplierId || bars.Contains(o.Barcode)); products.AddRange(pros); supplierBarcodes = pros.Select(o => o.Barcode).ToList(); supplierBarcodes.AddRange(pros.Where(o => !o.Barcodes.IsNullOrEmpty()).SelectMany(o => o.Barcodes.Split(','))); } if (!storeId.IsNullOrEmpty()) { var ware = WarehouseService.Find(o => o.StoreId == storeId && o.CompanyId == CommonService.CompanyId); if (ware != null) { var categorySNs = ware.CategorySN.Split(',').Select(o => int.Parse(o)).ToList(); var childs = ProductCategoryService.GetChildSNs(categorySNs); var pros = BaseService <VwProduct> .FindList(o => o.CompanyId == CommonService.CompanyId && childs.Contains(o.CategorySN)); storeBarcodes = pros.Select(o => o.Barcode).ToList(); storeBarcodes.AddRange(pros.Where(o => !o.Barcodes.IsNullOrEmpty()).SelectMany(o => o.Barcodes.Split(','))); products.AddRange(pros); } } var barcodeIdx = fieldCols.GetValue("Barcode").ToType <int>() - 65; var buyPriceIdx = fieldCols.GetValue("BuyPrice").ToType <int>() - 65; var numberIdx = fieldCols.GetValue("InboundNumber").ToType <int>() - 65; var proDateIdx = fieldCols.GetValue("ProducedDate").ToType <int>() - 65; var giftIdx = fieldCols.GetValue("IsGift").ToType <int>() - 65; var memoIdx = fieldCols.GetValue("Memo").ToType <int>() - 65; count = dt.Rows.Count; for (int i = 0; i < dt.Rows.Count; i++) { var pos = i + obj.MinRow; var dr = dt.Rows[i]; var barcode = dr.GetValue(barcodeIdx).ToString().Trim(); if (barcode.IsNullOrEmpty()) { errLs.Add("行号[" + pos + "]条码为空!"); continue; } if (!supplierBarcodes.Any(o => o == barcode)) { errLs.Add("行号[" + pos + "]该供应单位下无此条码!"); continue; } if (!storeBarcodes.Any(o => o == barcode)) { errLs.Add("行号[" + pos + "]该门店下无此条码!"); continue; } var buyPrice = dr.GetValue(buyPriceIdx).ToType <decimal?>(); if (!buyPrice.HasValue) { errLs.Add("行号[" + pos + "]该条码进价为空!"); continue; } var number = dr.GetValue(numberIdx).ToType <decimal?>(); if (!number.HasValue) { errLs.Add("行号[" + pos + "]该条码入库数量为空!"); continue; } var gift = dr.GetValue(giftIdx).ToString(); var proDate = dr.GetValue(proDateIdx).ToString().Trim(); DateTime t = DateTime.Now; if (!proDate.IsNullOrEmpty() && DateTime.TryParse(proDate, out t)) { proDate = t.ToString("yyyy-MM-dd"); } else { proDate = string.Empty; } var pro = products.FirstOrDefault(o => o.Barcode == barcode || ("," + o.Barcodes + ",").Contains("," + barcode + ",")); list.Add(new InboundList() { Barcode = barcode, ProductTitle = pro.Title, BuyPrice = buyPrice.Value, InboundNumber = number.Value, Unit = pro.SubUnit, ProducedDate = proDate, SysPrice = pro.SysPrice, Memo = dr.GetValue(memoIdx).ToString(), IsGift = (short)(gift == "赠品"?1:0) }); } } catch (Exception ex) { op.Message = ex.Message; op.Successed = false; Log.WriteError(ex); errLs.Add("导入出现异常!"); } return(CommonService.GenerateImportHtml(errLs, count, data: list, isSuccess: false)); }
/// <summary> /// Constructor for default set configFileName = null, or passed in configuration file path /// </summary> /// <param name="configFileName"></param> /// <param name="roleFlags"></param> /// <param name="setsToImport"></param> public OutPutFilters(string configFileName, RoleFilter roleFlags, ImportSet setsToImport = ImportSet.All) : this() { AppliedRoles = roleFlags; FiltersHelperInit(configFileName, setsToImport); }
public OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName) { var op = new OpResult(); var errLs = new List <string>(); int count = 0; try { Dictionary <string, char> fieldCols = null; DataTable dt = null; op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt); if (!op.Successed) { return(op); } var brandClass = DataDictRepository.GetQuery(o => o.DicPSN == 5).ToList(); var otherClass = brandClass.FirstOrDefault(o => o.Title.StartsWith("其")); var brands = GetList(); var clsIdx = Convert.ToInt32(fieldCols["ClassifyId"]) - 65; var titleIdx = Convert.ToInt32(fieldCols["Title"]) - 65; count = dt.Rows.Count; var max = DataDictRepository.GetQuery().Max(o => o.DicSN); for (int i = dt.Rows.Count - 1; i >= 0; i--) { try { var dr = dt.Rows[i]; var text = dr[clsIdx].ToString(); if (text.IsNullOrEmpty()) { continue; } var cls = brandClass.FirstOrDefault(o => o.Title == text); if (cls != null) { dr[clsIdx] = cls.DicSN.ToString(); } else { if (obj.RefCreate) { var data = new SysDataDictionary { DicPSN = 5, Status = true, Title = text, DicSN = max++, CreateDT = DateTime.Now, CreateUID = CurrentUser.UID }; DataDictRepository.Add(data); brandClass.Add(data); dr[clsIdx] = data.DicSN.ToString(); } else if (otherClass != null) { dr[clsIdx] = otherClass.DicSN.ToString(); } else { errLs.Add("行业分类[" + text + "]不存在!"); dt.Rows.RemoveAt(i);//去除不导入 continue; } } text = dr[titleIdx].ToString().Trim(); if (brands.Any(o => o.Title == text)) { errLs.Add("品牌名称[" + text + "]已存在!"); dt.Rows.RemoveAt(i);//去除不导入 } else { brands.Add(new ProductBrand() { Title = text }); } } catch (Exception e) { throw new Exception("品牌分类处理失败!", e); } } var maxsn = BrandRepository.GetQuery().Max(o => (int?)o.BrandSN).GetValueOrDefault(); StringBuilder sb = new StringBuilder(); sb.Append("begin tran "); foreach (DataRow dr in dt.Rows) { sb.Append("insert into "); sb.Append(obj.TableName); sb.Append("(BrandSN,State,Source,"); sb.Append(string.Join(",", fieldCols.Keys)); sb.Append(") values("); sb.AppendFormat("{0},", ++maxsn); sb.AppendFormat("1,0,"); foreach (var de in fieldCols) { var index = Convert.ToInt32(de.Value) - 65; try { var text = dr[index].ToString(); sb.Append("'" + text + "',"); } catch (Exception e) { throw new Exception("列选择超过范围!", e); } } sb = sb.Remove(sb.Length - 1, 1); sb.Append(");"); } sb.Append(" commit tran"); if (dt.Rows.Count > 0) { op.Successed = new DBHelper().ExecuteNonQueryText(sb.ToString(), null) > 0; LogEngine.WriteInsert("品牌导入", LogModule.档案管理); } } catch (Exception ex) { op.Message = ex.Message; op.Successed = false; LogEngine.WriteError(ex); errLs.Add("导入出现异常!"); } return(CommonService.GenerateImportHtml(errLs, count)); }
public Pharos.Utility.OpResult SaveOrUpdate(ImportSet model) { throw new NotImplementedException(); }
public OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName) { var op = new OpResult(); var errLs = new List <string>(); int count = 0; try { Dictionary <string, char> fieldCols = null; DataTable dt = null; op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt); if (!op.Successed) { return(op); } var categorys = ProductCategoryService.GetList(); var otherClass = categorys.FirstOrDefault(o => o.Title.StartsWith("其")); var brands = BrandService.GetList(); var otherBrand = brands.FirstOrDefault(o => o.Title.StartsWith("其")); var brandClass = DictionaryService.GetChildList(5); var otherBrandClass = brandClass.FirstOrDefault(o => o.Title.StartsWith("其"));//? var units = DictionaryService.GetSubUnitCategories(); var otherUnit = units.FirstOrDefault(o => o.Title.StartsWith("其")); var products = GetList(); var maxCate = ProductCategoryService.MaxSN() + 1; var maxBrand = BrandService.MaxSN() + 1; var maxDict = DictionaryService.MaxSN() + 1; var clsIdx = Convert.ToInt32(fieldCols["CategorySN"]) - 65; var brandIdx = Convert.ToInt32(fieldCols["BrandSN"]) - 65; var unitIdx = Convert.ToInt32(fieldCols["SubUnitId"]) - 65; var titleIdx = Convert.ToInt32(fieldCols["Barcode"]) - 65; count = dt.Rows.Count; for (int i = dt.Rows.Count - 1; i >= 0; i--) { try { var dr = dt.Rows[i]; #region 条码验证 var text = dr[titleIdx].ToString(); if (text.IsNullOrEmpty()) { errLs.Add("行号[" + i + "]条码为空!"); dt.Rows.RemoveAt(i); continue; } else if (products.Any(o => o.Barcode == text)) { errLs.Add("条码[" + text + "]已存在!"); dt.Rows.RemoveAt(i); continue; } else { products.Add(new ProductRecord() { Barcode = text }); } #endregion #region 处理类别 text = dr[clsIdx].ToString(); if (!text.IsNullOrEmpty() && text.Contains("/")) { var cates = text.Split('/'); var first = cates.FirstOrDefault(); var third = cates.LastOrDefault(); var second = ""; if (cates.Length == 3) { second = cates[1]; third = cates[2]; } else if (cates.Length == 2) { second = cates[1]; third = null; } else if (cates.Length > 3) { second = cates[1]; third = text.Replace(first + "/" + second + "/", ""); } var parent = categorys.FirstOrDefault(o => o.Title == first); var cls = parent != null?categorys.FirstOrDefault(o => o.CategoryPSN == parent.CategorySN && o.Title == second) : null; cls = cls != null && !third.IsNullOrEmpty() ? categorys.FirstOrDefault(o => o.CategoryPSN == cls.CategorySN && o.Title == third) : cls; if (cls != null) { dr[clsIdx] = cls.CategorySN.ToString(); } else { if (obj.RefCreate) { var list = new List <ProductCategory>(); cls = categorys.FirstOrDefault(o => o.Title == first); int psn = 0; short grade = 2; if (cls == null) { parent = new ProductCategory() { CategorySN = maxCate, Title = first, CategoryCode = 1, Grade = 1, State = 1, }; list.Add(parent); psn = parent.CategorySN; var child = new ProductCategory() { CategoryPSN = psn, CategorySN = ++maxCate, Title = second, CategoryCode = 1, Grade = grade, State = 1, }; list.Add(child); psn = child.CategorySN; grade = 3; if (!third.IsNullOrEmpty()) { child = new ProductCategory() { CategoryPSN = psn, CategorySN = ++maxCate, Title = third, CategoryCode = 1, Grade = grade, State = 1, }; psn = child.CategorySN; list.Add(child); } } else { psn = cls.CategorySN; cls = categorys.FirstOrDefault(o => o.Title == second); if (cls == null) { var maxcode = ProductCategoryService.MaxCode(psn); var child = new ProductCategory() { CategoryPSN = psn, CategorySN = ++maxCate, Title = second, CategoryCode = ++maxcode, Grade = grade, State = 1 }; list.Add(child); psn = child.CategorySN; grade = 3; } else { psn = cls.CategorySN; } if (!third.IsNullOrEmpty()) { cls = categorys.FirstOrDefault(o => o.Title == third); if (cls == null) { var maxcode = ProductCategoryService.MaxCode(psn); var child = new ProductCategory() { CategoryPSN = psn, CategorySN = ++maxCate, Title = third, CategoryCode = ++maxcode, Grade = grade, State = 1 }; list.Add(child); psn = child.CategorySN; } else { psn = cls.CategorySN; } } } ProductCategoryService.AddRange(list); categorys.AddRange(list); dr[clsIdx] = psn.ToString(); } else if (otherClass != null) { dr[clsIdx] = otherClass.CategorySN.ToString(); } else { errLs.Add("条码[" + dr[titleIdx] + "]类别不存在!"); dt.Rows.RemoveAt(i);//去除不导入 continue; } } } else { errLs.Add("条码[" + dr[titleIdx] + "]类别不存在!"); dt.Rows.RemoveAt(i);//去除不导入 continue; } #endregion #region 处理品牌 text = dr[brandIdx].ToString(); if (!text.IsNullOrEmpty()) { var cls = brands.FirstOrDefault(o => o.Title == text); if (cls != null) { dr[brandIdx] = cls.BrandSN.ToString(); } else { if (obj.RefCreate) { if (otherBrandClass == null) { errLs.Add("条码[" + dr[titleIdx] + "]品牌分类不存在!"); dt.Rows.RemoveAt(i); continue; } var data = new ProductBrand() { //BrandSN = maxBrand++, Title = text, ClassifyId = otherBrandClass.DicSN, JianPin = "", State = 1 }; BrandService.SaveOrUpdate(data); brands.Add(data); dr[brandIdx] = data.BrandSN.ToString(); } else if (otherBrand != null) { dr[brandIdx] = otherBrand.BrandSN.ToString(); } else { errLs.Add("条码[" + dr[titleIdx] + "]品牌不存在!"); dt.Rows.RemoveAt(i); continue; } } } #endregion #region 处理单位 text = dr[unitIdx].ToString(); if (!text.IsNullOrEmpty()) { var cls = units.FirstOrDefault(o => o.Title == text); if (cls != null) { dr[unitIdx] = cls.DicSN.ToString(); } else { if (obj.RefCreate) { var data = new SysDataDictionary() { DicPSN = 4, //DicSN = maxDict++, Status = true, Title = text, }; DictionaryService.SaveOrUpdate(data); units.Add(data); dr[unitIdx] = data.DicSN.ToString(); } else if (otherUnit != null) { dr[unitIdx] = otherUnit.DicSN.ToString(); } else { errLs.Add("条码[" + dr[titleIdx] + "]单位不存在!"); dt.Rows.RemoveAt(i); continue; } } } #endregion } catch (Exception e) { throw new Exception("创建相关记录失败," + e.Message, e); } } StringBuilder sb = new StringBuilder(); sb.Append("begin tran "); foreach (DataRow dr in dt.Rows) { sb.Append("insert into "); sb.Append(obj.TableName); sb.Append("(State,"); sb.Append(string.Join(",", fieldCols.Keys)); sb.Append(") values(1,"); foreach (var de in fieldCols) { var index = Convert.ToInt32(de.Value) - 65; try { var text = dr[index].ToString().Trim(); sb.Append("'" + text + "',"); } catch (Exception e) { throw new Exception("列选择超过范围!", e); } } sb = sb.Remove(sb.Length - 1, 1); sb.Append(");"); } sb.Append(" commit tran"); if (dt.Rows.Count > 0) { new DBFramework.DBHelper().ExecuteNonQueryText(sb.ToString(), null); } } catch (Exception ex) { op.Message = ex.Message; op.Successed = false; LogEngine.WriteError(ex); errLs.Add("导入出现异常!"); } return(CommonService.GenerateImportHtml(errLs, count)); }
public OpResult ImportSet(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName, ref Dictionary <string, char> fieldCols, ref System.Data.DataTable dt) { var op = new OpResult(); if (httpFiles.Count <= 0 || httpFiles[0].ContentLength <= 0) { op.Message = "请先选择Excel文件"; return(op); } var stream = httpFiles[0].InputStream; var ext = httpFiles[0].FileName.Substring(httpFiles[0].FileName.LastIndexOf(".")); if (!(ext.Equals(".xls", StringComparison.CurrentCultureIgnoreCase) || ext.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))) { op.Message = "请先选择Excel文件"; return(op); } fieldCols = fieldCols ?? new Dictionary <string, char>(); if (!fieldName.IsNullOrEmpty() && !columnName.IsNullOrEmpty()) { var fields = fieldName.Split(','); var columns = columnName.Split(','); if (fields.Length != columns.Length) { op.Message = "配置的字段和列数不一致!"; return(op); } for (var i = 0; i < fields.Length; i++) { if (columns[i].IsNullOrEmpty()) { continue; } fieldCols[fields[i]] = Convert.ToChar(columns[i]); } obj.FieldJson = fieldCols.Select(o => new { o.Key, o.Value }).ToJson(); } if (obj.Id == 0) { if (!ImportSetRepository.GetQuery(o => o.TableName == obj.TableName).Any()) { ImportSetRepository.Add(obj); } } else { var res = ImportSetRepository.Get(obj.Id); obj.ToCopyProperty(res); } ImportSetRepository.SaveChanges(); dt = new ExportExcel().ToDataTable(stream, minRow: obj.MinRow, maxRow: obj.MaxRow.HasValue ? obj.MaxRow.Value : Int32.MaxValue); if (dt == null || dt.Rows.Count <= 0) { op.Message = "无数据,无法导入!"; op.Successed = false; return(op); } #region 允许配置在同一列 var cols = fieldCols.GroupBy(o => o.Value).Where(o => o.Count() > 1).ToList();//取重复列 foreach (var item in cols) { System.Diagnostics.Debug.WriteLine(item.Key);//重复列value var idx = Convert.ToInt32(item.Key) - 65; foreach (var subitem in item) { System.Diagnostics.Debug.WriteLine(subitem.Key);//重复列key var lastValue = Convert.ToChar(fieldCols.Values.OrderBy(o => o).LastOrDefault() + 1); if (dt.Columns[idx].CloneTo(subitem.Key)) { fieldCols[subitem.Key] = lastValue; } } } #endregion op.Successed = true; return(op); }
/// <summary> /// Constructor for default set configFileName = null, or passed in configuration file path /// </summary> /// <param name="configFileName"></param> public OutPutFilters(string configFileName, RoleFilter role, ImportSet import = ImportSet.All) : this() { AppliedRoles = role; FiltersHelperInit(import, configFileName); }