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);
}
