public virtual ActionResult OutboundShipmentExcel(string id)
{
var result = new ExcelResult("OutboundShipment_" + id);
result.AddWorkSheet(_repos.Value.GetDetailsOfOutboundShipment(id, null).Select(p => new ParentShipmentContentModel(p)).ToArray(), "Shipment Details", "Details of Shipment " + id);
return(result);
}
public virtual ActionResult PoImportedExcel(string id, string pk1)
{
var result = new ExcelResult("PO_" + id);
result.AddWorkSheet(_repos.Value.GetInOrderBucketPickslipsOfPo(id, pk1).Select(p => new PickslipHeadlineModel(p)).ToList(), "Pickslips", "List of Pickslips in PO " + id);
return(result);
}
public virtual ActionResult CartonLocationExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("id");
}
//var palletCartons = _repos.Value.GetCartonsOfLocationOnPallet(id).Select(p => new CartonLocationPalletModel
//{
// CartonCount = p.CartonCount,
// DistinctSKUs = p.DistinctSKUs,
// PalletId = p.PalletId,
// SKUQuantity = p.SKUQuantity
//}).ToArray();
var nonPalletCartons = _repos.Value.GetCartonsAtLocation(id).Take(GlobalConstants.MAX_EXCEL_ROWS).Select(p => new CartonAtLocationModel
{
CartonId = p.CartonId,
//DistinctSKUs = p.DistinctSKUs,
SKUQuantity = p.SKUQuantity
}).ToArray();
var result = new ExcelResult("CartonLocation_" + id);
//result.AddWorkSheet(palletCartons, "Pallets on Location", "List on Pallets on Location " + id);
result.AddWorkSheet(nonPalletCartons, "Cartons on Location", "List of Non Pallet Cartons on location " + id);
return(result);
}
public virtual ActionResult SkuLocationExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("id");
}
var loc = _repos.Value.GetSkuLocation2(id);
if (loc == null)
{
throw new NotImplementedException("What should we do here?");
}
var allSku = loc.SkusAtLocation.Select(p => new SkuLocationSkuModel
{
//Upc = p.Upc,
Style = p.Style,
Color = p.Color,
Dimension = p.Dimension,
SkuSize = p.SkuSize,
Pieces = p.Pieces,
//IsAssigned = loc.AssignedSkuId == p.SkuId
}).ToList();
var allPallets = _repos.Value.GetPalletsOfSkuLocation(id).Select(p => new SkuLocationPalletModel(p)).ToArray();
var assignUnassignAudit = _repos.Value.GetLocAssignUnassignAudit(id).Select(p => new LocationAuditModel(p)).ToArray();
var inventoryAudit = _repos.Value.GetLocationsInventoryAudit(id).Select(p => new LocationAuditModel(p)).ToArray();
var result = new ExcelResult("SkuLocation_" + id);
result.AddWorkSheet(allSku, "Inventory", "List on content on location " + id);
result.AddWorkSheet(allPallets, "Pallets", "List of Pallets on location " + id);
result.AddWorkSheet(assignUnassignAudit, "Assignment Audit", "SKU assignment audot of location " + id);
result.AddWorkSheet(inventoryAudit, "Inventory Audit", "Inventory Audit of location" + id);
return(result);
}
public virtual ActionResult InboundShipmentDetail(ShipmentSkuFilterModel filters, bool exportExcel = false)
{
int nMaxRowsToShow = 2000;
var shipmentLists = GetInboundShipmentDetailModels(filters, nMaxRowsToShow);
var model = new IntransitShipmentSkuListViewModel
{
Filters = filters
};
model.ShipmentLists = shipmentLists;
//model.TotalShipmentRows = shipmentLists.Select(m => m.Shipments[0].TotalShipmentCount).FirstOrDefault();
model.MaxRowsToShow = shipmentLists.Select(m => m.Shipments).Distinct().Count();
model.SewingPlantList = (from item in _service.GetSewingPlantList()
select new SelectListItem
{
Text = string.Format("{0}:{1}", item.SewingPlantCode, item.PlantName),
Value = item.SewingPlantCode
}).ToArray();
if (exportExcel)
{
var result = new ExcelResult("Shipment_SKU_Detail");
result.AddWorkSheet(model.TransferShipmentList, "Transfers", "List of Building Transfer Shipment");
result.AddWorkSheet(model.UnknownShipmentList, "Unknown", "List of Unknown Shipment");
result.AddWorkSheet(model.VendorShipmentList, "Vendors", "List of Vendor Shipment");
return(result);
}
return(View(Views.IntransitShipmentSkuList, model));
}
public virtual ActionResult IntransitShipmentExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("id");
}
var shipmentSku = _service.GetInboundShipmentInfo(id).Select(p => new ShipmentSkuModel
{
Style = p.Style,
Color = p.Color,
Dimension = p.Dimension,
SkuSize = p.SkuSize,
ReceivedPieces = p.ReceivedPieces,
ExpectedPieces = p.ExpectedPieces,
UnderReceviedPieces = p.UnderReceviedPieces,
OverReceviedPieces = p.OverReceviedPieces,
ExpectedCartonCount = p.ExpectedCartonCount,
ReceivedCartonCount = p.ReceivedCartonCount,
UnderReceviedCartonCount = p.UnderReceviedCartonCount,
OverReceviedCartonCount = p.OverReceviedCartonCount,
MinMergedToBuddyShipment = p.MinMergedToBuddyShipment,
MaxMergedToBuddyShipment = p.MaxMergedToBuddyShipment,
MinMergedInBuddyShipment = p.MinMergedInBuddyShipment,
MaxMergedInBuddyShipment = p.MaxMergedInBuddyShipment,
CountMergedToBuddyShipment = p.CountMergedToBuddyShipment,
CountMergedInBuddyShipment = p.CountMergedInBuddyShipment,
CtnsReceivedInOtherShipment = p.CtnsReceivedInOtherShipment,
PcsReceivedInOtherShipment = p.PcsReceivedInOtherShipment
}).OrderByDescending(m => m.ExpectedPieces - m.ReceivedPieces).ToList();
var result = new ExcelResult("IntransitShipment_" + id);
result.AddWorkSheet(shipmentSku, "SKU", "List of SKU in shipment " + id);
return(result);
}
public virtual ActionResult PoExcel(string id, string pk1, int?pk2)
{
var result = new ExcelResult("PO_" + id);
result.AddWorkSheet(_repos.Value.GetPickslips(id, pk1, pk2, GlobalConstants.MAX_EXCEL_ROWS).Select(p => new PickslipHeadlineModel(p)).ToList(), "Pickslips", "List of Pickslips in PO " + id);
return(result);
}
public virtual ActionResult IntransitShipmentListExcel(ShipmentListFilterModel filters)
{
string heading;
switch (filters.Source)
{
case null:
heading = "All Shipments";
break;
case ShipmentSourceType.Vendor:
heading = "Vendor Shipments";
break;
case ShipmentSourceType.Transfer:
heading = "Transfer Shipments";
break;
default:
throw new NotImplementedException();
}
var list = GetShipmentModels(filters, Helpers.GlobalConstants.MAX_EXCEL_ROWS);
var result = new ExcelResult("Inbound Shipment Summary");
//TODO: include filters in heading
result.AddWorkSheet(list, "Shipments", heading);
return(result);
}
public virtual ActionResult PickslipImportedExcel(long id)
{
var result = new ExcelResult("Pickslip_" + id);
result.AddWorkSheet(_repos.Value.GetSkuOfImportedPickslip(id).Select(p => new PickslipSkuModel(p)).ToArray(), "SKUs", "List od SKUs in Pickslip " + id);
return(result);
}
public MethodResult Compare(List <ExcelTest_CoordinateInfo> info, ExcelResult expected, ExcelResult actual, string testName, string testOutputDirectory, string errorFileName)
{
var expectedWS = expected.ExcelPackage.Workbook.Worksheets[1];
var actualWS = actual.ExcelPackage.Workbook.Worksheets[1];
var mainDataComparisonResults = new List <ExcelComparison>();
foreach (var i in info)
{
var xs = this.Compare(i, expectedWS, actualWS);
mainDataComparisonResults.AddRange(xs);
}
var isOk = mainDataComparisonResults.All(a => a.ExpectedAndActual_AreEqual);
if (isOk)
{
return(MethodResult.Ok(Status.Success, "Files are the same."));
}
var message = new StringBuilder();
var errorCount = mainDataComparisonResults.Where(a => !a.ExpectedAndActual_AreEqual).Count();
var fullCount = mainDataComparisonResults.Count();
var percentFailed = Math.Round(((double)errorCount / fullCount) * 100);
message.AppendLine($"All numeric cells are expected to be identical. { percentFailed }% ({ errorCount } of { fullCount }) error rate.");
foreach (var excelComparisonResult in mainDataComparisonResults.Where(a => !a.ExpectedAndActual_AreEqual))
{
excelComparisonResult.Message = $"{ this.ExcelService.ColumnLetter_FromColumnNumber(excelComparisonResult.Column) }{ excelComparisonResult.Row } Expected: { excelComparisonResult.Expected } Actual: { excelComparisonResult.Actual }";
message.AppendLine(excelComparisonResult.Message);
}
var _message = message.ToString();
this.CreateOutputWorksheet(mainDataComparisonResults, actual, testName, testOutputDirectory, errorFileName, _message);
return(MethodResult.Fail($"Files are not the same. Failure: { percentFailed }%.\r\nDetails here: { testOutputDirectory }/{ errorFileName }"));
}
public virtual ActionResult PickslipExcel(long id)
{
var result = new ExcelResult("Pickslip_" + id);
result.AddWorkSheet(_repos.Value.GetBoxes(id, GlobalConstants.MAX_EXCEL_ROWS).Select(p => new BoxHeadlineModel(p)).ToList(), "Boxes", "List of boxes of Pickslip " + id);
result.AddWorkSheet(_repos.Value.GetPickslipSku(id).Select(p => new PickslipSkuModel(p)).ToArray(), "SKUs", "List of SKUs in Pickslip " + id);
return(result);
}
// GET: GetExcel
public ExcelResult Index()
{
var result = new ExcelResult();
result.Path = "~/Download/Book1.xlsx";
return(result);
}
public void Save(ExcelResult excelResult)
{
if (string.IsNullOrEmpty(excelResult.Directory))
{
throw new Exception("excelResult.Directory cannot be null or empty.");
}
var filename = cleanseFilename(excelResult.Filename);
FileUtility.WriteFile <ExcelService>(filename, excelResult.Directory, excelResult.ExcelPackage.GetAsByteArray());
}
public static ExcelDocument LoadMainProfileDB(MainProfile mainProfile)
{
List <ExcelResult> answerListContent = new List <ExcelResult>();
List <ExcelProfile> profilesListContent = new List <ExcelProfile>();
using (profileContext db = new profileContext())
{
mainProfile = db.MainProfile.SingleOrDefault(p => p == mainProfile);
db.Entry(mainProfile).Collection(t => t.Profile).Load();
db.Entry(mainProfile).Collection(t => t.Questioned).Load();
foreach (var profileItem in mainProfile.Profile)
{
db.Entry(profileItem).Collection(t => t.Question).Load();
db.Entry(profileItem).Collection(t => t.Result).Load();
db.Entry(profileItem).Reference(t => t.Type).Load();
List <ExcelQuestion> questions = new List <ExcelQuestion>();
foreach (var questionItem in profileItem.Question)
{
if (questionItem.LeftLimit == null && questionItem.RightLimit == null)
{
questions.Add(new ExcelQuestion {
Id = questionItem.SerialNumber, Content = questionItem.Content, LeftLimit = "", RightLimit = ""
});
}
else
{
questions.Add(new ExcelQuestion {
Id = questionItem.SerialNumber, Content = questionItem.Content, LeftLimit = questionItem.LeftLimit, RightLimit = questionItem.RightLimit
});
}
}
ExcelProfile excelProfile = new ExcelProfile {
Id = profileItem.SerialNumber, Answers = profileItem.Answer, Name = profileItem.Name, Type = profileItem.Type.Type, Questions = questions
};
profilesListContent.Add(excelProfile);
foreach (var resultItem in profileItem.Result)
{
string questionedId = mainProfile.Questioned.SingleOrDefault(q => q.Id == resultItem.QuestionedId).Number;
ExcelResult excelResult = new ExcelResult
{
Id = questionedId,
ProfileNum = resultItem.Profile.SerialNumber,
QuestionNum = resultItem.Question.SerialNumber,
Answer = resultItem.Answer
};
answerListContent.Add(excelResult);
}
}
return(new ExcelDocument {
DocumentName = mainProfile.Name, AnswerListContent = answerListContent, ProfilesListContent = profilesListContent
});
}
}
public virtual ActionResult CustomerExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("id");
}
var orders = _repos.Value.GetRecentOrders(id, GlobalConstants.MAX_EXCEL_ROWS).Select(p => new RecentPoModel(p)).ToArray();
var result = new ExcelResult("Customer_" + id);
result.AddWorkSheet(orders, "Recent PO", "Active POs of Customer " + id);
return(result);
}
public virtual ActionResult CartonPalletExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("id");
}
var allCartons = _repos.Value.GetCartonsOfPallet(id).Select(p => new CartonHeadlineModel(p)).ToList();
var result = new ExcelResult("CartonPallet_" + id);
result.AddWorkSheet(allCartons, "Cartons", "List of Cartons on Pallet " + id);
return(result);
}
public virtual ActionResult CartonExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("id");
}
var result = new ExcelResult("Carton_" + id);
var processList = _repos.Value.GetCartonHistory(id).Select(p => new CartonProcessModel(p)).ToArray();
result.AddWorkSheet(processList, "Carton History", "History of Carton " + id);
return(result);
}
public virtual ActionResult CartonAreaExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("id");
}
var areaInventory = _repos.Value.GetCartonAreaInventory(id).Select(p => new CartonAreaInventoryModel(p)).ToArray();
var result = new ExcelResult("CartonArea_" + id);
result.AddWorkSheet(areaInventory, "Inventory", "Inventory in Area " + id);
return(result);
}
public virtual ActionResult ReturnExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("id");
}
var returnRecipts = _repos.Value.GetReturnInfo(id, GlobalConstants.MAX_EXCEL_ROWS).Select(p => new ReturnReceiptModel(p)).ToArray();
var result = new ExcelResult("Return_Receipts " + id);
result.AddWorkSheet(returnRecipts, "Return Receipt", "List of Return Receipt " + id);
return(result);
}
public virtual ActionResult MasterBolExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("id");
}
var ShipmentList = _repos.Value.GetMasterBolShipments(id).Select(p => new MasterBolShipmentModel(p)).ToList();
var result = new ExcelResult("MasterBOL_" + id);
result.AddWorkSheet(ShipmentList, "Master BOL Shipment List", "Shipment in Master BOL " + id);
return(result);
}
public virtual ActionResult SkuExcel(int id)
{
var result = new ExcelResult("SKU_" + id);
var items = _repos.Value.GetSkuInventoryByArea(id);
var query = from row in items
select new SkuInventoryModel(row);
result.AddWorkSheet(query.ToArray(), "Inventory", "Inventory of SKU " + id + " per Area/Vwh");
result.AddWorkSheet(_repos.Value.GetRecentOrders(id, GlobalConstants.MAX_EXCEL_ROWS).Select(p => new RecentPoModel(p)).ToArray(),
"Recent PO", "Active POs of SKU " + id);
return(result);
}
public ExcelResult CopyExcelFile(ExcelResult fileToCopy, string newFileName)
{
//gotta read it again, because the stream will be closed
using (var file = this.ReadFile(fileToCopy.Filename, fileToCopy.Directory)) {
var result = new ExcelResult
{
Filename = newFileName,
Directory = fileToCopy.Directory,
ExcelPackage = file.ExcelPackage
};
this.Save(result);
return(result);
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="controller"></param>
/// <param name="content"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static ActionResult Excel(this Controller controller, object model, String fileName)
{
controller.ViewData.Model = model;
var result = new ExcelResult(fileName)
{
ViewName = null,
MasterName = null,
ViewData = controller.ViewData,
TempData = controller.TempData,
ViewEngineCollection = controller.ViewEngineCollection
};
return(result);
}
public virtual ActionResult BoxExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("id");
}
var result = new ExcelResult("Box_" + id);
result.AddWorkSheet(_repos.Value.GetBoxSku(id, null).Select(p => new BoxSkuModel(p)).ToArray(),
"SKU", "List of SKU in Box " + id);
result.AddWorkSheet(_repos.Value.GetBoxProcesssHistory(id).Select(p => new BoxAuditModel(p)).ToArray(),
"Audit", "Audit of Box " + id);
return(result);
}
public virtual ActionResult ReturnReceiptExcel(string id)
{
var returnSku = _repos.Value.GetReturnReceiptInfo(id).Where(p => !string.IsNullOrWhiteSpace(p.Upc)).Select(p => new ReturnSkuModel
{
Upc = p.Upc,
Style = p.Style,
Color = p.Color,
Dimension = p.Dimension,
SkuSize = p.SkuSize,
RetailPrice = (p.RetailPrice * p.Quantity),
Pieces = p.Quantity
}).ToList();
var result = new ExcelResult("ReturnReceipt_" + id);
result.AddWorkSheet(returnSku, "SKU", "List of KU in Return Receipt " + id);
return(result);
}
public static List <ExcelResult> GetResultsEP(string pathToExcelFile, string sheetName)
{
List <ExcelResult> excelAnswer = new List <ExcelResult>();
var ids = GetColumn(2, 1, pathToExcelFile, sheetName);
var profileNums = GetColumn(2, 2, pathToExcelFile, sheetName);
var questionNums = GetColumn(2, 3, pathToExcelFile, sheetName);
var answers = GetColumn(2, 4, pathToExcelFile, sheetName);
if (ids.Count == profileNums.Count && profileNums.Count == questionNums.Count && questionNums.Count == answers.Count())
{
for (int i = 0; i < ids.Count(); i++)
{
int profileNum;
int questionNum;
try
{
profileNum = Convert.ToInt32(profileNums[i]);
questionNum = Convert.ToInt32(questionNums[i]);
}
catch (Exception)
{
throw new Exception("Первый столбец в листе \"" + sheetName + "\" должен содержать только цифры.");
}
ExcelResult answer = new ExcelResult()
{
Id = ids[i].ToString(),
ProfileNum = profileNum,
QuestionNum = questionNum,
Answer = answers[i].ToString().ToLower()
};
excelAnswer.Add(answer);
}
}
else
{
throw new Exception("Лист \"" + sheetName + "\" не соответствует формату.");
}
return(excelAnswer);
}
public virtual ActionResult BoxPalletExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("id");
}
var result = new ExcelResult("BoxPallet_" + id);
var boxes = _repos.Value.GetBoxesOfPallet(id, GlobalConstants.MAX_EXCEL_ROWS);
result.AddWorkSheet(boxes.Select(p => new BoxHeadlineModel(p)).ToList(), "Boxes", "List of Boxes on Pallet " + id);
var allSku = _repos.Value.GetBoxSku(null, id).Select(p => new BoxSkuModel(p))
.OrderBy(p => p.Style)
.ThenBy(p => p.Color)
.ThenBy(p => p.Dimension)
.ThenBy(p => p.SkuSize)
.ToArray();
result.AddWorkSheet(allSku, "SKU", "List of SKUs on Pallet " + id);
var history = _repos.Value.GetBoxPalletHistory(id).Select(p => new BoxPalletHistoryModel(p)).ToArray();
result.AddWorkSheet(history, "History", "Audit Entries for Pallet " + id);
return(result);
}
public void TestActionResult_FormatFromFile()
{
ExcelResult<PersonMoney> result = new ExcelResult<PersonMoney>(
new ExportModel<PersonMoney>()
{
Renderer = new STExcel2003Renderer<PersonMoney>("Templates")
{
dataSource = DataSourcePersons,
StringTemplateFileName = "PersonMoneyExcel2003"
}.ApplyCodeFormat(false)
}
);
result.ExecuteResult(_controllerContext);
MemoryStream ms = _controllerContext.HttpContext.Response.OutputStream as MemoryStream;
ms.Flush();
ms.Position = 0;
StreamReader sr = new StreamReader(ms);
Assert.AreEqual(verificationData, sr.ReadToEnd());
}
public ExcelResult ExcelResultCreate(string ExcelData, string ExcelName)
{
ExcelResult Result = new ExcelResult() { ExecelData = ExcelData, ExecelName = ExcelName };
return Result;
}
public ActionResult ScoreToExcel(int testid, List<int> userids)
{
ActionResult action = View("ErrorDetail", (object)Constants.DefaultCannotExportFile);
var common = new CommonService();
var package = common.ScoreToExcel(testid, userids);
if (package != null)
{
var result = new ExcelResult();
result.Package = package;
result.FileName = "OATS_StudentScore_Test_" + testid + ".xlsx";
action = result;
}
return action;
}
/// <summary>
/// 批量更新或插入。
/// </summary>
/// <param name="dt"></param>
/// <param name="excelRow"></param>
/// <returns></returns>
public static bool AcceptChanges(MDataTable dt, MDataRow excelRow, string objName = null)
{
if (excelRow == null)
{
MDataTable dtImportUnique = GridConfig.GetList(objName, GridConfig.SelectType.ImportUnique);
string[] names = null;
if (dtImportUnique != null && dtImportUnique.Rows.Count > 0)
{
names = new String[dtImportUnique.Rows.Count];
for (int i = 0; i < dtImportUnique.Rows.Count; i++)
{
names[i] = dtImportUnique.Rows[i].Get <string>("Field");
}
}
return(dt.AcceptChanges(AcceptOp.Auto, null, names));
}
bool result = true;
//获取相关配置
string[] tables = excelRow.Get <string>(Config_Excel.TableNames).Split(',');
MDataTable configTable = GetExcelInfo(excelRow.Get <string>(Config_Excel.ExcelID));
Dictionary <string, string> rowPrimaryValue = new Dictionary <string, string>(); //存档每个表每行的主键值。
Dictionary <string, string> wherePrimaryValue = new Dictionary <string, string>(); //存档where条件对应的主键值。
using (MAction action = new MAction(tables[0]))
{
action.SetAopOff();
action.BeginTransation();
AppConfig.Debug.OpenDebugInfo = false;
IExcelConfig excelConfigExtend = ExcelConfigFactory.GetExcelConfigExtend();
foreach (var table in tables)
{
GC.Collect();//后面的Fill查询代码循环上万次会增长太多内存，提前调用，能降低内存。
action.ResetTable(table);
for (int i = 0; i < dt.Rows.Count; i++)
{
action.Data.Clear();
var row = dt.Rows[i];
foreach (var cell in row) //遍历所有数据行
{
if (cell.Struct.TableName == table) //过滤出属于本表的字段。
{
string[] items = cell.ColumnName.Split('.');
string columnName = items[items.Length - 1];
action.Set(columnName, cell.Value);
}
}
#region 检测是否需要插入外键。
MDataTable foreignTable = configTable.FindAll("TableName='" + table + "' and IsForeignkey=1");
if (foreignTable != null)
{
foreach (var foreignRow in foreignTable.Rows)
{
string formatter = foreignRow.Get <string>("Formatter");
string fTableName = foreignRow.Get <string>("ForeignTable");
if (string.IsNullOrEmpty(formatter))
{
//获取主键外值键
string key = fTableName + i;
if (rowPrimaryValue.ContainsKey(key))
{
string value = rowPrimaryValue[key];
action.Set(foreignRow.Get <string>("Field"), value);
}
}
else // 从其它自定义列取值。
{
MDataCell cell = row[formatter];
cell = cell ?? row[fTableName + "." + formatter];
if (cell != null)
{
action.Set(foreignRow.Get <string>("Field"), cell.Value);
}
}
}
foreignTable = null;
}
#endregion
#region //获取唯一联合主键,检测是否重复
string where = string.Empty;
List <MDataRow> rowList = configTable.FindAll("TableName='" + table + "' and IsUnique=1");
if (rowList != null && rowList.Count > 0)
{
bool IsUniqueOr = excelRow.Get <bool>("IsUniqueOr");
List <MDataCell> cells = new List <MDataCell>();
string errText = string.Empty;
int errorCount = 0;
foreach (var item in rowList)
{
var cell = action.Data[item.Get <string>(Config_ExcelInfo.Field)];
if (cell != null)
{
if (cell.IsNullOrEmpty) // 唯一主键是必填写字段
{
errorCount++;
errText += "[第" + (i + 1) + "行数据]：" + cell.Struct.ColumnName + "[" + cell.Struct.Description + "]不允许为空！\r\n";
}
else
{
cells.Add(cell);
}
}
}
if (errorCount > 0)
{
if (!IsUniqueOr || errorCount == rowList.Count)
{
result = false;
dt.DynamicData = new Exception(errText);
goto err;
}
}
MDataCell[] item2s = cells.ToArray();
where = action.GetWhere(!IsUniqueOr, item2s);
item2s = null;
rowList = null;
}
if (!string.IsNullOrEmpty(where))
{
action.SetSelectColumns(action.Data.PrimaryCell.ColumnName);
if (action.Fill(where))//根据条件查出主键ID
{
string key = table + where;
if (wherePrimaryValue.ContainsKey(key))
{
rowPrimaryValue.Add(table + i, wherePrimaryValue[key]);//记录上一个主键值。
}
else
{
rowPrimaryValue.Add(table + i, action.Get <string>(action.Data.PrimaryCell.ColumnName));//记录上一个主键值。
}
if (action.Data.GetState() == 2)
{
ExcelResult eResult = excelConfigExtend.BeforeUpdate(action.Data, row);
if (eResult == ExcelResult.Ignore || (eResult == ExcelResult.Default && action.Update(where)))
{
continue;//已经存在了，更新，准备下一条。
}
else
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
else
{
continue;//已经存在了，同时没有可更新字段
}
}
else if (!string.IsNullOrEmpty(action.DebugInfo))//产生错误信息，发生异常
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
#endregion
if (action.Data.GetState() == 0)
{
continue;//没有可映射插入的列。
}
//插入前，调用函数（插入特殊主键等值）
string errMsg;
ExcelResult excelResult = excelConfigExtend.BeforeInsert(action.Data, row, out errMsg);
if (excelResult == ExcelResult.Ignore)
{
continue;
}
if (excelResult == ExcelResult.Error || !action.Insert(InsertOp.ID))
{
result = false;
action.RollBack();
if (string.IsNullOrEmpty(errMsg))
{
errMsg = "[第" + (i + 1) + "行数据]：" + action.DebugInfo;
}
dt.DynamicData = new Exception(errMsg);
excelConfigExtend.OnInsertError(errMsg, dt);
goto err;
}
//插入后事件（可以触发其它事件）
excelConfigExtend.AfterInsert(action.Data, row, i == dt.Rows.Count - 1);
string primaryKey = action.Get <string>(action.Data.PrimaryCell.ColumnName);
rowPrimaryValue.Add(table + i, primaryKey);//记录上一个主键值。
if (!wherePrimaryValue.ContainsKey(table + where))
{
wherePrimaryValue.Add(table + where, primaryKey);
}
}
}
err:
action.EndTransation();
excelConfigExtend.Dispose();
}
return(result);
}
public ExcelResult Post(ExportToExcelInput input)
{
try
{
string startDate = input.StartDate;
string endDate = input.EndDate;
string customerNumber = input.CustomerNumber;
int size = input.Size == 0 ? 100 : input.Size;
int page = input.Page;
string threadID = !(string.IsNullOrEmpty(input.ThreadId)) ? input.ThreadId : "";
string poNumber = !(string.IsNullOrEmpty(input.PONumber)) ? input.PONumber : "";
serviceCaller.size = size;
serviceCaller.from = page;
ExcelResult result = new ExcelResult();
var lstResult = new List <GetExcelResult>();
var pos = serviceCaller.GetPOS(input);
var poNumbersSource = JsonConvert.DeserializeObject <PurchaseOrderResult>(pos.Result);
var poNumbers = poNumbersSource.hits.hits.Where(x => x._source.ThreadID != null).Select(x => x._source.ThreadID).ToList();
result.Total = poNumbersSource.hits.total;
var response = serviceCaller.SearchForGivenInput(startDate, endDate, customerNumber, poNumbers, threadID: threadID);
var objResult = JsonConvert.DeserializeObject <PurchaseOrderResult>(response.Result);
if (objResult != null && objResult.hits != null & objResult.hits.hits != null)
{
//int total = objResult.hits.total;
//response = serviceCaller.SearchForGivenInput(startDate, endDate, customerNumber, poNumbers, total);
//objResult = JsonConvert.DeserializeObject<PurchaseOrderResult>(response.Result);
if (objResult != null && objResult.hits != null & objResult.hits.hits != null)
{
var group = (from p in objResult.hits.hits.ToList()
//where string.IsNullOrEmpty(id) || p._source.Identity.Contains(id)
group p by new { p._source.ThreadID, p._source.PONumber } into g
select new { ThreadId = g.Key.ThreadID, PONumber = g.Key.PONumber, POResult = g.ToList() }).ToList();
foreach (var item in group)
{
if (!string.IsNullOrEmpty(input.PONumber))
{
if (!item.PONumber.Equals(input.PONumber, StringComparison.OrdinalIgnoreCase))
{
break;
}
}
var selectedSendPurchaseOrder = item.POResult.Where(x => x._type == CONSTANTS.sendPurchaseOrderstatus).ToList();
var date = item.POResult.Max(x => x._source.Date);
if (selectedSendPurchaseOrder != null && selectedSendPurchaseOrder.Count > 0)
{
foreach (var sendPO in selectedSendPurchaseOrder)
{
var lstOfSteps = purchaseOrderStatusRetriever.GetStepsForExcel(item.POResult.ToList(), sendPO);
lstResult.Add(new GetExcelResult
{
lstOfSteps = lstOfSteps,
PONumber = item.PONumber,
ThreadID = item.ThreadId,
Date = date
});
}
}
else
{
var lstOfSteps = purchaseOrderStatusRetriever.GetStepsForExcel(item.POResult.ToList(), null);
lstResult.Add(new GetExcelResult
{
lstOfSteps = lstOfSteps,
PONumber = item.PONumber,
ThreadID = item.ThreadId,
Date = date
});
}
}
//result.Total = objResult.hits.total;
result.lst = lstResult;
}
}
return(result);
}
catch (Exception ex)
{
return(new ExcelResult {
Message = ex.Message + Environment.NewLine + ex.StackTrace
});
//System.IO.File.AppendAllText(@"E:\inetpub\wwwroot\b2bdiceapiprod\bin\\ErrorText.txt", ex.Message + DateTime.Now + Environment.NewLine);
// throw;
}
}
public void TestActionResult_FormatFromCode()
{
ExcelResult<PersonMoney> result = new ExcelResult<PersonMoney>(
new ExportModel<PersonMoney>()
{
Renderer = new STExcel2003Renderer<PersonMoney>("Templates")
{
dataSource = DataSourcePersons
}.ApplyCodeFormat(true)
}
.AddColumns(x =>
{
x.For("Number").named("Money");
x.For(val => val.DepositDate).named("dt").Format("{0:yyyyMMdd}");
x.For(val => val.Name).Visible(false);
}
));
result.ExecuteResult(_controllerContext);
MemoryStream ms = _controllerContext.HttpContext.Response.OutputStream as MemoryStream;
ms.Flush();
ms.Position = 0;
StreamReader sr = new StreamReader(ms);
Assert.AreEqual(verificationData, sr.ReadToEnd());
}
public async Task <APIResult> GetTaskListByDisIdForExcel(string disCode, string startTime, string endTime, string status, string pid)
{
try
{
if (startTime == null)
{
startTime = "";
}
if (endTime == null)
{
endTime = "";
}
if (status == null)
{
status = "";
}
string spName = @"up_RMMT_TOU_TaskListByDisIdForExcel_R";
DynamicParameters dp = new DynamicParameters();
dp.Add("@DisCode", disCode, DbType.Int64);
dp.Add("@StartTime", startTime, DbType.String);
dp.Add("@EndTime", endTime, DbType.String);
dp.Add("@Status", status, DbType.String);
dp.Add("@Pid", pid, DbType.Int64);
using (var conn = new SqlConnection(DapperContext.Current.SqlConnection))
{
conn.Open();
var list = await conn.QueryMultipleAsync(spName, dp, null, null, CommandType.StoredProcedure);
var r1 = list.ReadAsync().Result;
var r2 = list.ReadAsync().Result;
List <ResultExcelDto> lst = JsonConvert.DeserializeObject <List <ResultExcelDto> >(JsonConvert.SerializeObject(r1));
List <LosePic> lst2 = JsonConvert.DeserializeObject <List <LosePic> >(JsonConvert.SerializeObject(r2));
ExcelResult er = new ExcelResult();
er.ResultList = new List <ResultExcelDto>();
er.LPicList = new List <LosePic>();
foreach (var item in lst)
{
er.ResultList.Add(item);
}
foreach (var item in lst2)
{
er.LPicList.Add(item);
}
string message = "";
if (lst.Count() == 0)
{
message = "没有数据";
}
APIResult result = new APIResult {
Body = CommonHelper.EncodeDto <ExcelResult>(er), ResultCode = ResultType.Success, Msg = message
};
return(result);
}
}
catch (Exception ex)
{
return(new APIResult {
Body = "", ResultCode = ResultType.Failure, Msg = ex.Message
});
}
}
