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