/// <summary> /// Adds a single template to the implementation guide document. /// </summary> /// <param name="template">The template to add to the document</param> private void AddTemplate(Template template) { Log.For(this).Trace("BEGIN: Adding template '{0}'.", template.Oid); List <TemplateConstraint> templateConstraints = (from tc in this._tdb.TemplateConstraints where tc.TemplateId == template.Id select tc).ToList(); List <TemplateConstraint> rootConstraints = templateConstraints .Where(y => y.ParentConstraintId == null) .OrderBy(y => y.Order) .ToList(); GreenTemplate greenTemplate = template.GreenTemplates.FirstOrDefault(); string bookmarkId = template.Bookmark; string templateIdentifier = string.Format("identifier: {0}", template.Oid); if (!string.IsNullOrEmpty(template.PrimaryContext)) { templateIdentifier = string.Format("{0} (identifier: {1})", template.PrimaryContext, template.Oid); } this.templateCount++; string headingLevel = Properties.Settings.Default.TemplateHeaderStyle; if (exportSettings.AlphaHierarchicalOrder && template.ImpliedTemplateId != null && this.templates.Exists(y => y.Id == template.ImpliedTemplateId)) { headingLevel = Properties.Settings.Default.TemplateHeaderSecondLevelStyle; } StringBuilder lTitleBuilder = new StringBuilder(string.Format("{0}", template.Name.Substring(1))); bool lDirectlyOwnedTemplate = template.OwningImplementationGuideId == this.implementationGuide.Id; bool lStatusMatches = template.StatusId == template.OwningImplementationGuide.PublishStatusId; string lTemplateTitle = lTitleBuilder.ToString(); // Output the title of the template Paragraph pHeading = new Paragraph( new ParagraphProperties( new ParagraphStyleId() { Val = headingLevel })); this.hyperlinkTracker.AddAnchorAround(pHeading, bookmarkId, new Run( new Text(template.Name.Substring(0, 1))), new Run( new Text(lTemplateTitle))); if (!string.IsNullOrEmpty(template.Notes) && this.exportSettings.IncludeNotes) { this.commentManager.AddCommentRange(pHeading, template.Notes); } this.document.MainDocumentPart.Document.Body.AppendChild(pHeading); // Output the "bracket data" for the template string detailsText = string.Format("identifier: {0} ({1})", template.Oid, template.IsOpen == true ? "open" : "closed"); if (!string.IsNullOrEmpty(template.PrimaryContext)) { detailsText = string.Format("[{0}: identifier {1} ({2})]", template.PrimaryContext, template.Oid, template.IsOpen == true ? "open" : "closed"); } Paragraph pDetails = new Paragraph( new ParagraphProperties( new ParagraphStyleId() { Val = Properties.Settings.Default.TemplateLocationStyle }), DocHelper.CreateRun(detailsText)); this.document.MainDocumentPart.Document.Body.AppendChild(pDetails); //Output IG publish/draft info with "bracket data" format if (exportSettings.IncludeTemplateStatus) { string status = template.Status != null ? template.Status.Status : "Draft"; string igText = string.Format("{0} as part of {1}", status, template.OwningImplementationGuide.GetDisplayName()); Paragraph igDetails = new Paragraph( new ParagraphProperties( new ParagraphStyleId() { Val = Properties.Settings.Default.TemplateLocationStyle }), DocHelper.CreateRun(igText)); this.document.MainDocumentPart.Document.Body.AppendChild(igDetails); } // If we were told to generate context tables for the template... if (exportSettings.GenerateTemplateContextTable) { TemplateContextTable.AddTable(this._tdb, this.templateRelationships, this.tables, this.document.MainDocumentPart.Document.Body, template, this.templates, this.hyperlinkTracker); } // Output the template's descriptionz if (!string.IsNullOrEmpty(template.Description)) { OpenXmlElement element = template.Description.MarkdownToOpenXml(this._tdb, this.document.MainDocumentPart); OpenXmlHelper.Append(element, this.document.MainDocumentPart.Document.Body); } // If we were told to generate tables for the template... if (exportSettings.GenerateTemplateConstraintTable) { this.constraintTableGenerator.AddTemplateConstraintTable(this.schema, template, this.document.MainDocumentPart.Document.Body, templateIdentifier); } if (templateConstraints.Count(y => y.IsHeading) > 0) { Paragraph propertiesHeading = new Paragraph( new ParagraphProperties( new ParagraphStyleId() { Val = Properties.Settings.Default.PropertiesHeadingStyle }), DocHelper.CreateRun("Properties")); this.document.MainDocumentPart.Document.Body.AppendChild(propertiesHeading); } // Output the implied template conformance line if (template.ImpliedTemplate != null) { OpenXmlElement templateReference = !this.templates.Contains(template.ImpliedTemplate) ? (OpenXmlElement)DocHelper.CreateRun(template.ImpliedTemplate.Name) : (OpenXmlElement)this.hyperlinkTracker.CreateHyperlink(template.ImpliedTemplate.Name, template.ImpliedTemplate.Bookmark, Properties.Settings.Default.LinkStyle); Paragraph impliedConstraint = new Paragraph( new ParagraphProperties( new NumberingProperties( new NumberingLevelReference() { Val = 0 }, new NumberingId() { Val = GenerationConstants.BASE_TEMPLATE_INDEX + (int)template.Id })), DocHelper.CreateRun("Conforms to "), templateReference, DocHelper.CreateRun(" template "), DocHelper.CreateRun("(identifier: " + template.ImpliedTemplate.Oid + ")", style: Properties.Settings.Default.TemplateOidStyle), DocHelper.CreateRun(".")); this.document.MainDocumentPart.Document.Body.Append(impliedConstraint); } bool lCreateValueSetTables = exportSettings.DefaultValueSetMaxMembers > 0; IConstraintGenerator constraintGenerator = ConstraintGenerationFactory.NewConstraintGenerator( this.igSettings, this.document.MainDocumentPart, this.commentManager, this.figures, exportSettings.IncludeXmlSamples, _tdb, rootConstraints, templateConstraints, template, this.templates, Properties.Settings.Default.ConstraintHeadingStyle, exportSettings.SelectedCategories, this.hyperlinkTracker); constraintGenerator.GenerateConstraints(lCreateValueSetTables, this.exportSettings.IncludeNotes); // Add value-set tables if (lCreateValueSetTables) { var constraintValueSets = (from c in templateConstraints where c.ValueSet != null select new { ValueSet = c.ValueSet, ValueSetDate = c.ValueSetDate }) .Distinct(); foreach (var cConstraintValueSet in constraintValueSets) { DateTime?bindingDate = cConstraintValueSet.ValueSetDate != null ? cConstraintValueSet.ValueSetDate : this.implementationGuide.PublishDate; if (bindingDate == null) { bindingDate = DateTime.Now; } this.valueSetsExport.AddValueSet(cConstraintValueSet.ValueSet, bindingDate.Value); } } if (exportSettings.IncludeXmlSamples) { foreach (var lSample in template.TemplateSamples.OrderBy(y => y.Id)) { this.figures.AddSample(lSample.Name, lSample.XmlSample); } } Log.For(this).Trace("END: Adding template '{0}' with {1} constraints.", template.Oid, templateConstraints.Count); }
public Paragraph AddToDocParagraph(MainDocumentPart mainPart, HyperlinkTracker hyperlinkTracker, OpenXmlElement parent, int level, int id, string headingStyle) { // Add the heading if (this.IsHeading && !string.IsNullOrEmpty(this.Context)) { string headingTitle = this.Context; if (!string.IsNullOrEmpty(this.Category)) { headingTitle += " for " + this.Category; } Paragraph pHeading = new Paragraph( new ParagraphProperties( new ParagraphStyleId() { Val = headingStyle }), DocHelper.CreateRun(headingTitle)); parent.Append(pHeading); if (!string.IsNullOrEmpty(this.HeadingDescription)) { OpenXmlElement parsedHeadingDescription = this.HeadingDescription.MarkdownToOpenXml(this.tdb, mainPart); if (parsedHeadingDescription != null) { foreach (OpenXmlElement cParsedChild in parsedHeadingDescription.ChildElements) { OpenXmlElement cClonedParsedChild = cParsedChild.CloneNode(true); this.ApplyStyleToElement(cClonedParsedChild); parent.Append(cClonedParsedChild); } } } } // Add the description above the constraint definition if (!string.IsNullOrEmpty(this.Description)) { OpenXmlElement parsedDescription = this.Description.MarkdownToOpenXml(this.tdb, mainPart); if (parsedDescription != null) { foreach (OpenXmlElement cParsedChild in parsedDescription.ChildElements) { OpenXmlElement cClonedParsedChild = cParsedChild.CloneNode(true); this.ApplyStyleToElement(cClonedParsedChild); parent.Append(cClonedParsedChild); } } } // Add the constraint definition Paragraph para = new Paragraph( new ParagraphProperties( new NumberingProperties( new NumberingLevelReference() { Val = level }, new NumberingId() { Val = id }))); foreach (ConstraintPart cPart in this.parts) { switch (cPart.PartType) { case ConstraintPart.PartTypes.Keyword: para.Append( DocHelper.CreateRun(cPart.Text, style: Properties.Settings.Default.ConformanceVerbStyle)); break; case ConstraintPart.PartTypes.Context: para.Append( DocHelper.CreateRun(cPart.Text, style: Properties.Settings.Default.ConstraintContextStyle)); break; case ConstraintPart.PartTypes.Template: para.Append( DocHelper.CreateRun(cPart.Text, style: Properties.Settings.Default.TemplateOidStyle)); break; case ConstraintPart.PartTypes.Vocabulary: para.Append( DocHelper.CreateRun(cPart.Text, style: Properties.Settings.Default.VocabularyConstraintStyle)); break; case ConstraintPart.PartTypes.Link: hyperlinkTracker.AddHyperlink(para, cPart.Text, cPart.LinkDestination, Properties.Settings.Default.LinkStyle); break; case ConstraintPart.PartTypes.Constraint: var newRun = DocHelper.CreateRun(cPart.Text); if (cPart.IsAnchor) { hyperlinkTracker.AddAnchorAround(para, "C_" + this.Number, newRun); } else { para.Append(newRun); } break; case ConstraintPart.PartTypes.PrimitiveText: var element = cPart.Text.MarkdownToOpenXml(this.tdb, mainPart, styleKeywords: true); OpenXmlHelper.Append(element, para); break; default: para.Append( DocHelper.CreateRun(cPart.Text)); break; } } // Add the label after a line break on the run to the paragraph if (!string.IsNullOrEmpty(this.Label)) { string additionalLabel = string.Format("Note: {0}", this.Label); para.AppendChild( new Run( new Break())); para.AppendChild( DocHelper.CreateRun(additionalLabel)); } parent.Append(para); return(para); }
private void AttachFile(string filePathAndName, bool displayAsIcon) { FileInfo fileInfo = new FileInfo(filePathAndName); OpenXmlHelper.AppendEmbeddedObject(_mainDocumentPart, fileInfo, displayAsIcon); }
public void LoadDataFromExcelOriginalFileOpenXmlDocument() { var data = OpenXmlHelper.GetValueArrayFromExcelFile("ExCel2010-StdContacts.xlsx"); Assert.IsNotNull(data); }
public static ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority) { return(new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority, StopIfTrue = OpenXmlHelper.GetBooleanValue(((XLConditionalFormat)cf).StopIfTrueInternal, false) }); }
public string Importar(HttpPostedFileBase fileData, FormCollection forms, string tipo) { try { var openXmlHelper = new OpenXmlHelper(); var memoryStream = new MemoryStream(); fileData.InputStream.CopyTo(memoryStream); var listaDados = openXmlHelper.LerPlanilhaDados(memoryStream); int codigo = 0; tipo = tipo.Substring(0, tipo.IndexOf('?')); switch (EnumHelper.ParseEnumDescription <EnumTipoImportacao>(tipo)) { case EnumTipoImportacao.Fonte: FonteRepositorio.ExcluirTudo(WorkLifetimeManager.Value); foreach (var item in listaDados) { Int32.TryParse(item[0].ToString(), out codigo); FonteRepositorio.Salvar(WorkLifetimeManager.Value, new Fonte { Codigo = codigo, Descricao = item[1].ToString() }); } break; case EnumTipoImportacao.Linha: LinhaRepositorio.ExcluirTudo(WorkLifetimeManager.Value); foreach (var item in listaDados) { Int32.TryParse(item[0].ToString(), out codigo); LinhaRepositorio.Salvar(WorkLifetimeManager.Value, new Linha { Codigo = codigo, Descricao = item[1].ToString() }); } break; case EnumTipoImportacao.Produto: ProdutoRepositorio.ExcluirTudo(WorkLifetimeManager.Value); foreach (var item in listaDados) { Int32.TryParse(item[0].ToString(), out codigo); ProdutoRepositorio.Salvar(WorkLifetimeManager.Value, new Produto { Codigo = codigo, Descricao = item[1].ToString() }); } break; } WorkLifetimeManager.Value.Commit(); return("Dados importados com sucesso!"); } catch { WorkLifetimeManager.Value.Rollback(); return("Não foi possível importar os dados. Verifique a planilha e tente novamente!"); } }
public Paragraph ShuffleSentenceUnit(Paragraph xmlSentenceElement) { _sentence = new Sentence(xmlSentenceElement); Text[] sentenceArray = _sentence.SentenceArray; if (_sentence.UnitNotFoundInSentence( sentenceArray, element => element.InnerText.IsTimer())) { return(xmlSentenceElement); } _sentence.TimerUnitCount = 0; var timerUnits = GetTimerUnits( sentenceArray).ToArray <IMoveableUnit>(); _sentence.UnderlineJoinedSentenceUnit( sentenceArray, timerUnits[0].StartPosition, timerUnits[_sentence.TimerUnitCount - 1].EndPosition); Text[] timerUnitsInSerialNumberOrder = _sentence.GetMoveableUnitsInSerialNumberDescendingOrder( _sentence.TimerUnitCount, timerUnits, sentenceArray); Text[] newSentence; if (_sentence.HasVbVbaPastToTheLeft( sentenceArray, timerUnits[0].StartPosition)) { newSentence = MoveTimerUnitBeforeVbVbaPast( sentenceArray, timerUnitsInSerialNumberOrder, timerUnits[0].StartPosition); } else if (_sentence.HasDGToTheLeft( sentenceArray, timerUnits[0].StartPosition)) { int dGIndexPosition = Array.FindIndex(sentenceArray, i => i.IsDG()); newSentence = MoveTimerUnitBeforeDGUnit( sentenceArray, dGIndexPosition, timerUnitsInSerialNumberOrder); } else { var beforeTimer = sentenceArray.Take(timerUnits[0].StartPosition); newSentence = beforeTimer.Concat(timerUnitsInSerialNumberOrder).ToArray(); } newSentence = _sentence.RemoveAnyBlankSpaceFromEndOfUnit (newSentence .Concat( _sentence.GetSentenceBreaker(sentenceArray)).ToArray()); return(new Paragraph( OpenXmlHelper.BuildWordsIntoOpenXmlElement( newSentence))); }
private void ExportOrderMake(HttpContext context) { var bll = new OrderMake(); StringBuilder sqlWhere = null; ParamsHelper parms = null; #region 构造查询条件 var sOrderCode = context.Request.QueryString["OrderCode"]; var sCustomerCode = context.Request.QueryString["CustomerCode"]; var sStaffCode = context.Request.QueryString["StaffCode"]; var sPayWay = context.Request.QueryString["PayWay"]; var sServiceProduct = context.Request.QueryString["ServiceProduct"]; var sStartDate = context.Request.QueryString["StartDate"]; var sEndDate = context.Request.QueryString["EndDate"]; if (!string.IsNullOrWhiteSpace(sOrderCode)) { if (sqlWhere == null) { sqlWhere = new StringBuilder(500); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append("and OrderCode like @OrderCode "); var parm = new SqlParameter("@OrderCode", SqlDbType.VarChar, 36); parm.Value = parm.Value = "%" + sOrderCode + "%"; parms.Add(parm); } if (!string.IsNullOrWhiteSpace(sCustomerCode)) { if (sqlWhere == null) { sqlWhere = new StringBuilder(400); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append("and CustomerCode like @CustomerCode "); var parm = new SqlParameter("@CustomerCode", SqlDbType.VarChar, 36); parm.Value = parm.Value = "%" + sCustomerCode + "%"; parms.Add(parm); } if (!string.IsNullOrWhiteSpace(sStaffCode)) { if (sqlWhere == null) { sqlWhere = new StringBuilder(300); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append("and StaffCode like @StaffCode "); var parm = new SqlParameter("@StaffCode", SqlDbType.VarChar, 36); parm.Value = parm.Value = "%" + sStaffCode + "%"; parms.Add(parm); } if (!string.IsNullOrWhiteSpace(sPayWay)) { if (sqlWhere == null) { sqlWhere = new StringBuilder(200); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append("and PayWay like @PayWay "); var parm = new SqlParameter("@PayWay", SqlDbType.VarChar, 36); parm.Value = parm.Value = "%" + sPayWay + "%"; parms.Add(parm); } if (!string.IsNullOrWhiteSpace(sServiceProduct)) { if (sqlWhere == null) { sqlWhere = new StringBuilder(100); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append("and ServiceProduct like @ServiceProduct "); var parm = new SqlParameter("@ServiceProduct", SqlDbType.VarChar, 36); parm.Value = parm.Value = "%" + sServiceProduct + "%"; parms.Add(parm); } #region 开始与结束日期段 DateTime startDate = DateTime.MinValue; DateTime endDate = DateTime.MinValue; if (!string.IsNullOrWhiteSpace(sStartDate)) { DateTime.TryParse(sStartDate, out startDate); } if (!string.IsNullOrWhiteSpace(sEndDate)) { DateTime.TryParse(sEndDate, out endDate); } if (startDate != DateTime.MinValue && endDate != DateTime.MinValue) { if (sqlWhere == null) { sqlWhere = new StringBuilder(300); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and (RecordDate between @StartDate and @EndDate) "); var parm = new SqlParameter("@StartDate", SqlDbType.DateTime); parm.Value = startDate; parms.Add(parm); parm = new SqlParameter("@EndDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(endDate.ToString("yyyy-MM-dd") + " 23:59:59"); parms.Add(parm); } else { if (startDate != DateTime.MinValue) { if (sqlWhere == null) { sqlWhere = new StringBuilder(300); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and (RecordDate >= @StartDate) "); var parm = new SqlParameter("@StartDate", SqlDbType.DateTime); parm.Value = startDate; parms.Add(parm); } if (endDate != DateTime.MinValue) { if (sqlWhere == null) { sqlWhere = new StringBuilder(300); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and (RecordDate <= @EndDate) "); var parm = new SqlParameter("@EndDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(endDate.ToString("yyyy-MM-dd") + " 23:59:59"); parms.Add(parm); } } #endregion #endregion var ds = bll.GetExportData(sqlWhere == null ? null : sqlWhere.ToString(), parms == null ? null : parms.ToArray()); var dt = ds.Tables[0]; OpenXmlHelper.Export(context, dt); }
private void ExportDeviceBorrowRecord(HttpContext context) { try { StringBuilder sqlWhere = null; ParamsHelper parms = null; #region 构造查询条件 var keyword = context.Request.QueryString["Keyword"]; if (!string.IsNullOrWhiteSpace(keyword)) { if (parms == null) { parms = new ParamsHelper(); } if (sqlWhere == null) { sqlWhere = new StringBuilder(1000); } sqlWhere.Append("and (Customer like @Keyword or SerialNumber like @Keyword or DeviceModel like @Keyword or DevicePart like @Keyword or PartStatus like @Keyword or ProjectAbout like @Keyword or SaleMan like @Keyword or Register like @Keyword or Remark like @Keyword) "); var parm = new SqlParameter("@Keyword", SqlDbType.NVarChar, 20); parm.Value = parm.Value = "%" + keyword + "%"; parms.Add(parm); } var typeName = context.Request.QueryString["TypeName"]; if (!string.IsNullOrWhiteSpace(typeName)) { if (parms == null) { parms = new ParamsHelper(); } if (sqlWhere == null) { sqlWhere = new StringBuilder(100); } sqlWhere.Append("and FunType=@FunType "); var parm = new SqlParameter("@FunType", SqlDbType.NVarChar, 20); parm.Value = parm.Value = typeName; parms.Add(parm); } DateTime startDate = DateTime.MinValue; DateTime endDate = DateTime.MinValue; if (!string.IsNullOrWhiteSpace(context.Request.QueryString["StartDate"])) { DateTime.TryParse(context.Request.QueryString["StartDate"], out startDate); } if (!string.IsNullOrWhiteSpace(context.Request.QueryString["EndDate"])) { DateTime.TryParse(context.Request.QueryString["EndDate"], out endDate); } if (startDate != DateTime.MinValue && endDate != DateTime.MinValue) { if (sqlWhere == null) { sqlWhere = new StringBuilder(700); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and (RecordDate between @StartDate and @EndDate) "); var parm = new SqlParameter("@StartDate", SqlDbType.DateTime); parm.Value = startDate; parms.Add(parm); parm = new SqlParameter("@EndDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(endDate.ToString("yyyy-MM-dd") + " 23:59:59"); parms.Add(parm); } else { if (startDate != DateTime.MinValue) { if (sqlWhere == null) { sqlWhere = new StringBuilder(700); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and (RecordDate >= @StartDate) "); var parm = new SqlParameter("@StartDate", SqlDbType.DateTime); parm.Value = startDate; parms.Add(parm); } if (endDate != DateTime.MinValue) { if (sqlWhere == null) { sqlWhere = new StringBuilder(700); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and (RecordDate <= @EndDate) "); var parm = new SqlParameter("@EndDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(endDate.ToString("yyyy-MM-dd") + " 23:59:59"); parms.Add(parm); } } var backDate = DateTime.MinValue; if (!string.IsNullOrWhiteSpace(context.Request.QueryString["BackDate"])) { DateTime.TryParse(context.Request.QueryString["BackDate"], out backDate); } if (backDate != DateTime.MinValue) { if (sqlWhere == null) { sqlWhere = new StringBuilder(500); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and BackDate = @BackDate "); var parm = new SqlParameter("@BackDate", SqlDbType.DateTime); parm.Value = backDate; parms.Add(parm); } var isBack = false; if (!string.IsNullOrWhiteSpace(context.Request.QueryString["IsBack"]) && bool.TryParse(context.Request.QueryString["IsBack"], out isBack)) { if (sqlWhere == null) { sqlWhere = new StringBuilder(100); } if (parms == null) { parms = new ParamsHelper(); } sqlWhere.Append(@"and IsBack = @IsBack "); var parm = new SqlParameter("@IsBack", SqlDbType.Bit); parm.Value = isBack; parms.Add(parm); } #endregion var bll = new InfoneDeviceBorrowRecord(); var dt = bll.GetDsExport(sqlWhere == null ? "" : sqlWhere.ToString(), parms == null ? null : parms.ToArray()); OpenXmlHelper.Export(context, dt.Tables[0]); } catch (Exception ex) { context.Response.Write(ResResult.ResJsonString(false, ex.Message, "")); } }
private void ExportDeviceRepairRecord(HttpContext context) { try { #region 动态创建查询条件 var sqlWhere = new StringBuilder(1000); var parms = new ParamsHelper(); SqlParameter parm = null; var keyword = string.Empty; if (!string.IsNullOrWhiteSpace(context.Request.QueryString["keyword"])) { keyword = HttpUtility.UrlDecode(context.Request.QueryString["keyword"]).Trim(); } if (!string.IsNullOrWhiteSpace(keyword)) { sqlWhere.Append(@"and (drr.Customer like @Customer or drr.SerialNumber like @SerialNumber or drr.DeviceModel like @DeviceModel or drr.FaultCause like @FaultCause or drr.SolveMethod like @SolveMethod or drr.CustomerProblem like @CustomerProblem or drr.DevicePart like @DevicePart or drr.TreatmentSituation like @TreatmentSituation or drr.HandoverPerson like @HandoverPerson or drr.RegisteredPerson like @RegisteredPerson or drr.Remark like @Remark) "); parms.Add(new SqlParameter("@Customer", "%" + keyword + "%")); parms.Add(new SqlParameter("@SerialNumber", "%" + keyword + "%")); parms.Add(new SqlParameter("@DeviceModel", "%" + keyword + "%")); parms.Add(new SqlParameter("@FaultCause", "%" + keyword + "%")); parms.Add(new SqlParameter("@SolveMethod", "%" + keyword + "%")); parms.Add(new SqlParameter("@CustomerProblem", "%" + keyword + "%")); parms.Add(new SqlParameter("@DevicePart", "%" + keyword + "%")); parms.Add(new SqlParameter("@TreatmentSituation", "%" + keyword + "%")); parms.Add(new SqlParameter("@HandoverPerson", "%" + keyword + "%")); parms.Add(new SqlParameter("@RegisteredPerson", "%" + keyword + "%")); parms.Add(new SqlParameter("@Remark", "%" + keyword + "%")); } DateTime startDate = DateTime.MinValue; DateTime endDate = DateTime.MinValue; if (!string.IsNullOrWhiteSpace(context.Request.QueryString["startDate"])) { DateTime.TryParse(context.Request.QueryString["startDate"], out startDate); } if (!string.IsNullOrWhiteSpace(context.Request.QueryString["endDate"])) { DateTime.TryParse(context.Request.QueryString["endDate"], out endDate); } if (startDate != DateTime.MinValue && endDate != DateTime.MinValue) { sqlWhere.AppendFormat(@"and (drr.RecordDate between @StartDate and @EndDate) "); parm = new SqlParameter("@StartDate", SqlDbType.DateTime); parm.Value = startDate; parms.Add(parm); parm = new SqlParameter("@EndDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(endDate.ToString("yyyy-MM-dd") + " 23:59:59"); parms.Add(parm); } else { if (startDate != DateTime.MinValue) { sqlWhere.AppendFormat(@"and (drr.RecordDate >= @StartDate) "); parm = new SqlParameter("@StartDate", SqlDbType.DateTime); parm.Value = startDate; parms.Add(parm); } if (endDate != DateTime.MinValue) { sqlWhere.AppendFormat(@"and (drr.RecordDate <= @EndDate) "); parm = new SqlParameter("@EndDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(endDate.ToString("yyyy-MM-dd") + " 23:59:59"); parms.Add(parm); } } if (!string.IsNullOrWhiteSpace(context.Request.QueryString["backDate"])) { sqlWhere.AppendFormat(@"and drr.BackDate = @BackDate "); parm = new SqlParameter("@BackDate", SqlDbType.DateTime); parm.Value = DateTime.Parse(context.Request.QueryString["backDate"]); parms.Add(parm); } if (!string.IsNullOrWhiteSpace(context.Request.QueryString["whetherFix"])) { sqlWhere.AppendFormat(@"and drr.WhetherFix = @WhetherFix "); parm = new SqlParameter("@WhetherFix", SqlDbType.NVarChar, 20); parm.Value = HttpUtility.UrlDecode(context.Request.QueryString["whetherFix"].Trim()); parms.Add(parm); } if (!string.IsNullOrWhiteSpace(context.Request.QueryString["isBack"])) { sqlWhere.AppendFormat(@"and drr.IsBack = @IsBack "); parm = new SqlParameter("@IsBack", SqlDbType.Bit); parm.Value = context.Request.QueryString["isBack"].Trim() == "1"; parms.Add(parm); } #endregion var bll = new InfoneDeviceRepairRecord(); var dt = bll.GetExportToExcelData(sqlWhere == null ? "" : sqlWhere.ToString(), parms == null ? null : parms.ToArray()); OpenXmlHelper.Export(context, dt); } catch (Exception ex) { context.Response.Write(ResResult.ResJsonString(false, ex.Message, "")); } }
private void ImportProduct(HttpContext context, HttpPostedFile file) { var dt = OpenXmlHelper.Import(file.InputStream); var drc = dt.Rows; if (drc.Count == 0) { context.Response.Write(ResResult.ResJsonString(false, MC.Import_NotDataError, "")); return; } var currTime = DateTime.Now; var list = new List <ImportProductInfo>(); foreach (DataRow dr in drc) { var sort = 0; if (dr["排序"] != null) { Int32.TryParse(dr["排序"].ToString(), out sort); } var modelInfo = new ImportProductInfo(); modelInfo.Sort = sort; modelInfo.Coded = dr["物料代码"].ToString().Trim(); modelInfo.Name = dr["物料名称"].ToString().Trim(); modelInfo.FullName = dr["全名"].ToString().Trim(); modelInfo.SpeModels = dr["规格型号"].ToString().Trim(); modelInfo.Remark = dr["备注"].ToString().Trim(); modelInfo.CodeItems = modelInfo.Coded.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries); modelInfo.CodeItemsLen = modelInfo.CodeItems.Length; if (string.IsNullOrWhiteSpace(modelInfo.Coded) || string.IsNullOrWhiteSpace(modelInfo.Name)) { throw new ArgumentException("存在物料代码或物料名称为空字符串的行,请核对后再重试!"); } modelInfo.IsCategory = modelInfo.Remark.Contains("类"); list.Add(modelInfo); } var cBll = new Category(); var pBll = new Product(); var okIndex = 0; var userId = WebCommon.GetUserId(); var categoryList = list.Where(m => m.IsCategory).OrderBy(m => m.CodeItemsLen); var productList = list.Where(m => !m.IsCategory).OrderBy(m => m.CodeItemsLen); var categoryRoot = cBll.GetRootModel(); var cLen = categoryList.Count(); var pLen = productList.Count(); foreach (var item in categoryList) { var parentId = categoryRoot.Id; if (item.CodeItemsLen > 1) { var parentCode = ""; for (var j = 0; j < (item.CodeItemsLen - 1); j++) { if (j > 0) { parentCode += "."; } parentCode += item.CodeItems[j]; } var parentInfo = cBll.GetModelByCode(parentCode); if (parentInfo != null) { parentId = parentInfo.Id; } } var categoryInfo = cBll.GetModelByCode(item.Coded); if (categoryInfo == null) { var currId = Guid.NewGuid(); categoryInfo = new CategoryInfo(currId, userId, parentId, item.Coded, item.Name, string.Format("{0},{1}", currId, parentId), item.Sort, item.Remark, currTime); cBll.InsertByOutput(categoryInfo); okIndex++; } var currCategoryProductList = productList.Where(m => m.Coded.StartsWith(item.Coded) && m.CodeItemsLen == (item.CodeItemsLen + 1)); if (currCategoryProductList != null && currCategoryProductList.Count() > 0) { foreach (var pItem in currCategoryProductList) { if (!pBll.IsExistCode(pItem.Coded, Guid.Empty)) { var productInfo = new ProductInfo(Guid.NewGuid(), userId, categoryInfo.Id, Guid.Empty, pItem.Coded, pItem.Name, pItem.FullName, pItem.SpeModels, 0, "", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, pItem.Sort, pItem.Remark, false, currTime); pBll.InsertByOutput(productInfo); okIndex++; } } } } context.Response.Write(ResResult.ResJsonString(true, "导入成功", "")); }