public ActionResult DownloadLetter(string ObjectName, string Language, string LetterName) { string Path = "/SpecialData/HrLetters/Uploaded/" + Language + "/" + LetterName; string pathName = Server.MapPath(Path); string ListPath = Server.MapPath("/App_Data/" + string.Format("{0}{1}", Language, ObjectName) + ".mdb"); if (!System.IO.File.Exists(ListPath)) { ListPath = Server.MapPath("/App_Data/" + string.Format("{0}{1}", "en-GB", ObjectName) + ".mdb"); } MailMergeViewmodel mail = new MailMergeViewmodel(); if (System.IO.File.Exists(pathName)) { FileInfo fl = new FileInfo(pathName); using (WordprocessingDocument docPKG = WordprocessingDocument.Open(pathName, true)) { MailMerge mymerge = docPKG.MainDocumentPart.DocumentSettingsPart.Settings.Elements <MailMerge>().FirstOrDefault(); var Relation = docPKG.MainDocumentPart.DocumentSettingsPart.AddExternalRelationship("http://schemas.openxmlformats.org/officeDocument/2006/relationships/mailMergeSource", new Uri(ListPath)); string Connectionval = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" + ListPath + ";Mode=Read;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False"; if (mymerge != null) { mymerge.ConnectString = new ConnectString() { Val = Connectionval }; mymerge.DataSourceReference = new DataSourceReference() { Id = Relation.Id }; } else { MailMerge m = new MailMerge(); string xmlnsw = @"http://schemas.openxmlformats.org/wordprocessingml/2006/main"; m.MainDocumentType = new MainDocumentType(); m.MainDocumentType.SetAttribute(new DocumentFormat.OpenXml.OpenXmlAttribute("val", xmlnsw, "formatLetters")); m.LinkToQuery = new LinkToQuery(); m.DataType = new DataType(); m.DataType.SetAttribute(new DocumentFormat.OpenXml.OpenXmlAttribute("val", xmlnsw, "native")); m.ConnectString = new ConnectString(); m.ConnectString.SetAttribute(new DocumentFormat.OpenXml.OpenXmlAttribute("val", xmlnsw, Connectionval)); m.Query = new Query(); m.Query.SetAttribute(new DocumentFormat.OpenXml.OpenXmlAttribute("val", xmlnsw, "SELECT * FROM `Office Address List` ")); m.ViewMergedData = new ViewMergedData(); m.DataSourceObject = new DataSourceObject(); m.DataSourceObject.UdlConnectionString = new UdlConnectionString(); m.DataSourceObject.UdlConnectionString.SetAttribute(new DocumentFormat.OpenXml.OpenXmlAttribute("val", xmlnsw, Connectionval)); m.DataSourceObject.DataSourceTableName = new DataSourceTableName(); m.DataSourceObject.DataSourceTableName.SetAttribute(new DocumentFormat.OpenXml.OpenXmlAttribute("val", xmlnsw, "Office Address List")); m.DataSourceObject.ColumnDelimiter = new ColumnDelimiter(); m.DataSourceObject.ColumnDelimiter.SetAttribute(new DocumentFormat.OpenXml.OpenXmlAttribute("val", xmlnsw, "9")); if (m.DataSourceReference == null) { m.DataSourceReference = new DataSourceReference(); m.DataSourceReference.Id = Relation.Id; } m.ViewMergedData.Val = true; docPKG.MainDocumentPart.DocumentSettingsPart.Settings.InsertAt <MailMerge>(m, 0); } docPKG.Save(); docPKG.Dispose(); } mail.Path = Path; mail.Exist = true; } else { mail.Path = ""; mail.Exist = false; } return(Json(mail)); }
public MailMergeViewmodel MergeData(string objectName, int Id, int EmpId, string NLanguage) { MailMergeViewmodel mail = new MailMergeViewmodel(); //mail.Exist = false; string FilePath = ""; // set default language for employee lang or user lang Language = Language == null ? NLanguage : Language; //setup temp folder on server string CopyPath = "/SpecialData/TempFolder"; string NoPage = MsgUtils.Instance.Trls("NoPage", Language); string ServerCopyPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SpecialData\\TempFolder"); if (Id != 0 && objectName != null) { MailMergeArrayViewModel model = BackListDataAndFields(objectName, Id, EmpId); string FileName = _hrUnitOfWork.Repository <PagePrint>().Where(a => a.ObjectName == objectName && a.CompanyId == 0 && a.Culture == Language && a.Version == 0).FirstOrDefault()?.LetterTempl; if (!string.IsNullOrEmpty(FileName)) { FilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SpecialData\\HrLetters\\Uploaded\\" + Language + "\\" + FileName); if (!Directory.Exists(ServerCopyPath)) { Directory.CreateDirectory(ServerCopyPath); } else { //Empty Exist files in TempFolder string[] files = Directory.GetFiles(ServerCopyPath); for (int i = 0; i < files.Length; i++) { System.IO.File.Delete(files[i]); } } ServerCopyPath += "\\" + FileName; try { if (System.IO.File.Exists(ServerCopyPath)) { System.IO.File.Delete(ServerCopyPath); } if (System.IO.File.Exists(FilePath)) { mail.Exist = true; System.IO.File.Copy(FilePath, ServerCopyPath); } else { mail.Error = NoPage; } } catch (Exception ex) { mail.Error = MsgUtils.Instance.Trls("InUse", Language); } if (!mail.Exist) { return(mail); } else { using (WordprocessingDocument docPKG = WordprocessingDocument.Open(ServerCopyPath, true)) { var AllText = docPKG.MainDocumentPart.Document.Descendants <Text>(); int cc = -1; string FieldName = ""; string value = ""; string key = ""; foreach (var item in AllText) { cc++; int Lastindex = 0; FieldName = ""; if (item.Text.Contains("«") && item.Text.Contains("»")) { value = item.Text.Replace("»", "").Replace("«", ""); if (model.Fields.ContainsValue(value)) { key = model.Fields.FirstOrDefault(x => x.Value == value).Key; item.Text = model.Data.FirstOrDefault(x => x.Key == key).Value; } } else if (item.Text.Contains("«")) { Lastindex = AllText.ToList().FindIndex(cc, a => a.Text.Contains("»")); for (int y = cc; y <= Lastindex; y++) { FieldName += AllText.ElementAt(y).Text; AllText.ElementAt(y).Text = ""; } item.Text.Replace("»", "").Replace("«", ""); value = FieldName.Replace("»", "").Replace("«", ""); if (model.Fields.ContainsValue(value)) { key = model.Fields.FirstOrDefault(x => x.Value == value).Key; item.Text = model.Data.FirstOrDefault(x => x.Key == key).Value; } } } docPKG.MainDocumentPart.DocumentSettingsPart.Settings.Elements <MailMerge>().FirstOrDefault().Remove(); docPKG.MainDocumentPart.Document.Save(); docPKG.Dispose(); } mail.Path = CopyPath + "/" + FileName; mail.Exist = true; mail.ServerFilePath = ServerCopyPath; } } else { mail.Error = NoPage; } } else { if (EmpId == 0) { mail.Error = MsgUtils.Instance.Trls("NoEmp", Language); } else { mail.Error = NoPage; } } return(mail); }
public ActionResult MergeLetters(int EmpId, string FileName) { MailMergeViewmodel mail = new MailMergeViewmodel(); List <string> ArryList; if (User.Identity.RTL()) { ArryList = new List <string>() { "اسم_الموظف", "الرقم_القومى", "تاريخ_اصدار_البطاقة", "عنوان_الموظف", "رقم_جواز_السفر", "تاريخ_اصدار_الجواز", "AddressingNo", "مدة_العقد", "تاريخ_بداية_التعيين", "«تاريخ_نهاية_التعيين", "الوظيفة", "الراتب", "البدلات", "العملة", "عدد_تذاكر_السفر", "قيمة_تذكرة_السفر", "من_دولة", "الى_دولة", "مدة_الاجازة", "يوم_التعيين", "SuggestedJob", "الفرع", "رقم_المحمول", "تليفون_المنزل", "الكفيل", "الادارة", "تاريخ_التكليف", "الموقع_الوظيفى", "حالة_التعيين", "الدرجة_المالية", "المسار_الوظيفى", "مدة_تعيين_الموظف", "مدة_التحاق_الموظف", "الجنسية", "اسم_الشركة", "نوع_الموظف", "مدة_الخبرة", "اخر_مؤهل_دراسى", "تاريخ_الميلاد", "الحالة_الاجتماعية", "الديانة", "الحالة_العسكرية", "تاريخ_تحرير_العقد", "يوم_تاريخ_تحرير_العقد»", "كورسات_التدريب", "المؤهلات" } } ; else { ArryList = new List <string>() { "EmployeeName", "NationalId", "NationalIdDate", "EmployeeAddress", "PassportNo", "PassportIssueDate", "AddressingNo", "ContractPeriod", "EmploymentStartDate", "EmploymentEndDate", "Job", "Salary", "Allowances", "Currency", "TicketCnt", "TicketAmt", "FromCountry", "ToCountry", "SeasonHolidayPeriod", "EmploymentDay", "SuggestedJob", "Branch", "Mobile", "HomeTelephone", "Kafel", "Department", "AssignDate", "Position", "AssignStatus", "PayrollGrad", "CareerPath", "JoinWorkingPeriod", "EmployWorkingPeriod", "Nationality", "CompanyName", "Gender", "ExpPeriod", "LastQual", "BirthDate", "MaritalStatus", "Religion", "MilitaryStatus", "ContractDate", "ContractDay", "TrainingCrs", "Qualifications" } }; string[] EmpData = _hrUnitOfWork.EmployeeRepository.GetEmpMergeData(EmpId, CompanyId, Language); string job = EmpData[20]; string File = ""; bool ExistFile = false; string Path = ""; string CopyPath = ""; if (FileName == "0") { Path = Server.MapPath(string.Format("../SpecialData/Contracts/{0}/{1}", CompanyId.ToString(), job)); if (Directory.Exists(Path)) { var Files = Directory.GetFiles(Path); File = Files.FirstOrDefault(); if (File != null) { ExistFile = true; File = File.Split('\\').LastOrDefault(); } } if (ExistFile) { Path = string.Format("{0}/{1}", Path, File); } else { string CompanyContract = _PersonSetup.ContractTempl; string PersonnelPath = Server.MapPath(string.Format("../SpecialData/Contracts/{0}/{1}", CompanyId.ToString(), CompanyContract)); if (CompanyContract != null && System.IO.File.Exists(PersonnelPath)) { Path = PersonnelPath; ExistFile = true; } } if (!ExistFile) { mail.Path = ""; mail.Exist = ExistFile; return(Json(mail)); } CopyPath = "/SpecialData/TempFolder/Contract.docx"; } else { Path = Server.MapPath("/SpecialData/HrLetters/Uploaded"); if (Directory.Exists(Path)) { if (System.IO.File.Exists(Path.Insert(Path.Length, "/" + FileName))) { ExistFile = true; Path = Server.MapPath("/SpecialData/HrLetters/Uploaded/" + FileName); } } if (!ExistFile) { mail.Path = ""; mail.Exist = ExistFile; return(Json(mail)); } CopyPath = "/SpecialData/TempFolder/Letter.docx"; } if (!Directory.Exists(Server.MapPath("/SpecialData/TempFolder"))) { Directory.CreateDirectory(Server.MapPath("/SpecialData/TempFolder")); } try { if (System.IO.File.Exists(Server.MapPath(CopyPath))) { System.IO.File.Delete(Server.MapPath(CopyPath)); } System.IO.File.Copy(Path, Server.MapPath(CopyPath)); } catch (Exception ex) { mail.Path = "InUse"; } if (mail.Path == "InUse") { return(Json(mail)); } using (WordprocessingDocument docPKG = WordprocessingDocument.Open(Server.MapPath(CopyPath), true)) { var xx = docPKG.MainDocumentPart.Document.Descendants <Text>(); int cc = -1; string FieldName = ""; foreach (var item in xx) { cc++; int Lastindex = 0; FieldName = ""; if (item.Text.Contains("«") && item.Text.Contains("»")) { int index = ArryList.FindIndex(f => f == item.Text.Replace("»", "").Replace("«", "")); if (index >= 0) { item.Text = EmpData[index]; } } else if (item.Text.Contains("«")) { Lastindex = xx.ToList().FindIndex(cc, a => a.Text.Contains("»")); for (int y = cc; y <= Lastindex; y++) { FieldName += xx.ElementAt(y).Text; xx.ElementAt(y).Text = ""; } int index = ArryList.FindIndex(f => f == FieldName.Replace("»", "").Replace("«", "")); if (index >= 0) { item.Text = EmpData[index]; } } } docPKG.MainDocumentPart.Document.Save(); } mail.Path = CopyPath; mail.Exist = ExistFile; return(Json(mail)); }