public ActionResult GetDropdown_Agency()
{
List <M_Agency> a = (from c in db.M_Agency where c.AgencyCode == "BIPH" select c).ToList();
List <M_Agency> b = (from c in db.M_Agency where c.AgencyCode != "BIPH" orderby c.AgencyName ascending select c).ToList();
List <M_Agency> li = new List <M_Agency>();
M_Agency AllAgency = new M_Agency();
AllAgency.AgencyCode = "AGENCY";
AllAgency.AgencyName = "All Agencies";
AllAgency.IsDeleted = false;
AllAgency.Status = true;
foreach (M_Agency i in a)
{
li.Add(i);
}
li.Add(AllAgency);
foreach (M_Agency i in b)
{
li.Add(i);
}
var list = (from w in li
where w.IsDeleted == false && w.Status == true
select new { text = w.AgencyCode + " - " + w.AgencyName, value = w.AgencyCode }).Distinct().ToList();
return(Json(new { list = list }, JsonRequestBehavior.AllowGet));
}
public ActionResult EditAgency(M_Agency data)
{
try
{
M_Agency agency = new M_Agency();
agency = (from u in db.M_Agency.ToList()
where u.ID == data.ID
select u).FirstOrDefault();
agency.AgencyName = data.AgencyName;
agency.Address = data.Address;
agency.ISO_OT = data.ISO_OT;
agency.ISO_CS = data.ISO_CS;
agency.ISO_DTR = data.ISO_DTR;
agency.Status = data.Status;
agency.TelNo = data.TelNo;
//agency.EmailAddress = data.EmailAddress;
agency.UpdateID = user.UserName;
agency.UpdateDate = DateTime.Now;
M_Agency checker = (from c in db.M_Agency
where c.AgencyName == data.AgencyName &&
c.Address == data.Address &&
c.ISO_OT == data.ISO_OT &&
c.ISO_CS == data.ISO_CS &&
c.ISO_DTR == data.ISO_DTR &&
c.Status == data.Status &&
c.TelNo == data.TelNo &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.Entry(agency).State = EntityState.Modified;
db.SaveChanges();
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Agency";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
public ActionResult DeleteAgency(int ID)
{
M_Agency agency = new M_Agency();
agency = (from u in db.M_Agency.ToList()
where u.ID == ID
select u).FirstOrDefault();
agency.IsDeleted = true;
agency.UpdateDate = DateTime.Now;
agency.UpdateID = user.UserName;
db.Entry(agency).State = EntityState.Modified;
db.SaveChanges();
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
public ActionResult UploadImagePackage(int AgencyID)
{
try
{
#region Save to Server
//bool isSuccess = false;
//string serverMessage = string.Empty;
//var fileOne = Request.Files[0] as HttpPostedFileBase;
// uploadPath = Server.MapPath(@"~/PictureResources/AgencyLogo/");
// newFileOne = Path.Combine(uploadPath, fileOne.FileName);
//fileOne.SaveAs(newFileOne);
#endregion
#region Save to Server
bool isSuccess = false;
string serverMessage = string.Empty;
var fileOne = Request.Files[0] as HttpPostedFileBase;
string uploadPath = Server.MapPath(@"~/PictureResources/AgencyLogo/");
string newFileOne = Path.Combine(uploadPath, fileOne.FileName);
//fileOne.SaveAs(newFileOne);
fileOne.SaveAs(HttpContext.Server.MapPath("~/PictureResources/AgencyLogo/") + Path.GetFileName(Regex.Replace(fileOne.FileName, @"\s+", "")));
#endregion
#region ImageSet
M_Agency pack = (from c in db.M_Agency where c.ID == AgencyID select c).FirstOrDefault();
//pack.Logo = fileOne.FileName;
string[] data = fileOne.FileName.Split('\\');
pack.Logo = Regex.Replace(data[data.Length - 1], @"\s+", "");//fileOne.FileName;
db.Entry(pack).State = EntityState.Modified;
db.SaveChanges();
#endregion
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Agency";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { wew = "" }, JsonRequestBehavior.AllowGet));
}
public ActionResult CreateAgency(M_Agency data)
{
try
{
data.CreateID = user.UserName;
data.CreateDate = DateTime.Now;
data.UpdateID = user.UserName;
data.UpdateDate = DateTime.Now;
M_Agency checker = (from c in db.M_Agency
where c.AgencyName == data.AgencyName &&
c.Address == data.Address &&
c.ISO_OT == data.ISO_OT &&
c.ISO_CS == data.ISO_CS &&
c.ISO_DTR == data.ISO_DTR &&
c.Status == data.Status &&
c.IsDeleted == false
select c).FirstOrDefault();
if (checker == null)
{
db.M_Agency.Add(data);
db.SaveChanges();
return(Json(new { msg = "Success" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { msg = "Failed" }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Master - Agency";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
return(Json(new { msg = err.Message }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult DownloadTemplate(string Agency)
{
try
{
long? lineID, a;
string searchnow = System.Web.HttpContext.Current.Session["Searchvalueot"].ToString();
string lineid = "";
if (System.Web.HttpContext.Current.Session["lINEID"] == null)
{
lineID = null;
}
else
{
a = null;
lineid = System.Web.HttpContext.Current.Session["lINEID"].ToString();
lineID = (lineid == "") ? a : Convert.ToInt64(lineid);
}
Agency = (Agency == "") ? "BIPH" : Agency;
M_Agency AgencyDetails = (from c in db.M_Agency where c.AgencyCode == Agency select c).FirstOrDefault();
string templateFilename = "StandardizeCS_template.xlsx";
string dir = Path.GetTempPath();
string filename = string.Format("StandardizeCS_template.xlsx");
FileInfo newFile = new FileInfo(Path.Combine(dir, filename));
string apptemplatePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"TemplateFiles\StandardTemplate\", templateFilename);
FileInfo templateFile = new FileInfo(apptemplatePath);
M_Employee_Master_List current = (from c in db.M_Employee_Master_List where c.EmpNo == user.UserName select c).FirstOrDefault();
ObjectParameter totalCount = new ObjectParameter("TotalCount", typeof(int));
using (ExcelPackage package = new ExcelPackage(newFile, templateFile)) //-- With template.
{
List <GET_Employee_OTFiling_Result> list = new List <GET_Employee_OTFiling_Result>();
list = db.GET_Employee_OTFiling(Agency, user.CostCode, lineID, "", 0, 100000, "", totalCount).ToList();
if (!string.IsNullOrEmpty(searchnow))//filter
{
#region null remover
list = list.Where(xx => xx.EmpNo != null).ToList();
list = list.Where(xx => xx.First_Name != null).ToList();
list = list.Where(xx => xx.Family_Name != null).ToList();
#endregion
list = list.Where(x => x.First_Name.ToLower().Contains(searchnow.ToLower()) ||
x.Family_Name.ToLower().Contains(searchnow.ToLower()) ||
x.EmpNo.Contains(searchnow)
).ToList <GET_Employee_OTFiling_Result>();
}
int start = 14;
ExcelWorksheet ExportData = package.Workbook.Worksheets["Standardized-CS Form"];
if (list.Count < 30)
{
for (int i = 0; i < list.Count; i++)
{
ExportData.Cells["B" + start].Value = list[i].EmpNo;
ExportData.Cells["C" + start].Value = list[i].Family_Name + ", " + list[i].First_Name;
start++;
}
}
ExportData.Cells["C5"].Value = current.Department;
ExportData.Cells["J5"].Value = user.Section;
ExportData.Cells["C1"].Value = AgencyDetails.AgencyName;
ExportData.Cells["C2"].Value = AgencyDetails.Address;
ExportData.Cells["C3"].Value = AgencyDetails.TelNo;
ExportData.Cells["H51"].Value = AgencyDetails.ISO_CS;
string path = Server.MapPath(@"/PictureResources/AgencyLogo/" + AgencyDetails.Logo);
#region IMAGE
using (System.Drawing.Image image = System.Drawing.Image.FromFile(path))
{
var excelImage = ExportData.Drawings.AddPicture("logohere", image);
excelImage.SetSize(140, 69);
excelImage.SetPosition(0, 0, 0, 10);
}
#endregion
return(File(package.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename));
}
}
catch (Exception err) {
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - Change Schedule";
error.ErrorLog = err.Message;
error.DateLog = db.TT_GETTIME().FirstOrDefault();//DateTime.Now;;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
return(Json(new { }, JsonRequestBehavior.AllowGet));
}
public void SendTheMail(string RefNo)
{
try
{
List <string> AgencyList = new List <string>();
AgencyList = (from c in db.AF_DTRfiling where c.DTR_RefNo == RefNo select c.BIPH_Agency).ToList();
AgencyList = AgencyList.Distinct().ToList();
foreach (string Agency in AgencyList)
{
long? lineID, a;
string lineid = "";
if (System.Web.HttpContext.Current.Session["lINEID"] == null)
{
lineID = null;
}
else
{
a = null;
lineid = System.Web.HttpContext.Current.Session["lINEID"].ToString();
lineID = (lineid == "") ? a : Convert.ToInt64(lineid);
}
string Agencycode = (Agency == "") ? "BIPH" : Agency;
M_Agency AgencyDetails = (from c in db.M_Agency where c.AgencyCode == Agencycode select c).FirstOrDefault();
string templateFilename = "";
templateFilename = "StandardizeDTR_template.xlsx";
string dir = Path.GetTempPath();
string datetimeToday = DateTime.Now.ToString("yyMMddhhmmss");
string filename = string.Format("StandardizeDTR_template{0}.xlsx", datetimeToday);
FileInfo newFile = new FileInfo(Path.Combine(dir, filename));
FileInfo newFilecopy = new FileInfo(Path.Combine(dir, filename));
string apptemplatePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"TemplateFiles\StandardTemplate\", templateFilename);
FileInfo templateFile = new FileInfo(apptemplatePath);
M_Employee_Master_List current = (from c in db.M_Employee_Master_List where c.EmpNo == user.UserName select c).FirstOrDefault();
using (ExcelPackage package = new ExcelPackage(newFile, templateFile)) //-- With template.
{
List <GET_AF_DTRExport_Result> list = db.GET_AF_DTRExport(RefNo, Agency).ToList();
int start = 12;
ExcelWorksheet ExportData = package.Workbook.Worksheets["Standardized-DTR Form"];
for (int i = 0; i < list.Count; i++)
{
ExportData.Cells["B" + start].Value = list[i].EmployeeNo;
ExportData.Cells["C" + start].Value = list[i].Family_Name + ", " + list[i].First_Name;
ExportData.Cells["D" + start].Value = list[i].Concerns;
ExportData.Cells["E" + start].Value = list[i].Reason;
ExportData.Cells["F" + start].Value = list[i].DateFrom;
ExportData.Cells["G" + start].Value = list[i].DateTo;
ExportData.Cells["H" + start].Value = list[i].Timein;
ExportData.Cells["I" + start].Value = list[i].TimeOut;
ExportData.Cells["J" + start].Value = list[i].EmployeeAccept;
start++;
}
ExportData.Cells["C5"].Value = current.Department;
ExportData.Cells["J6"].Value = user.Section;
ExportData.Cells["J5"].Value = DateTime.Now.ToShortDateString();
ExportData.Cells["C1"].Value = AgencyDetails.AgencyName;
ExportData.Cells["C2"].Value = AgencyDetails.Address;
ExportData.Cells["C3"].Value = AgencyDetails.TelNo;
ExportData.Cells["I51"].Value = AgencyDetails.ISO_DTR;
string path = Server.MapPath(@"/PictureResources/AgencyLogo/" + AgencyDetails.Logo);
#region IMAGE
using (System.Drawing.Image image = System.Drawing.Image.FromFile(path))
{
var excelImage = ExportData.Drawings.AddPicture("logohere", image);
excelImage.SetSize(140, 69);
excelImage.SetPosition(0, 0, 1, 1);
}
#endregion
//string paths = @"\\192.168.200.100\Published Files\Brothers_AMS\" + filename;
//Stream stream = System.IO.File.Create(paths);
//package.SaveAs(stream);
//stream.Close();
//try
//{
// db.AF_SendAgency("*****@*****.**", filename);
//}
//catch (Exception err) { }
}
}
}
catch (Exception err)
{
Error_Logs error = new Error_Logs();
error.PageModule = "Application Form - CS";
error.ErrorLog = err.Message;
error.DateLog = DateTime.Now;
error.Username = user.UserName;
db.Error_Logs.Add(error);
db.SaveChanges();
}
}
