private List<SelectListItem> GetVendor()
{
var user = (IARTAutomationApp.Models.UserMaster)Session["User"];
IARTDBNEWEntities db = new IARTDBNEWEntities();
List<SelectListItem> storeStatus = (from p in db.VendorMasters.Where(e => e.CustomerId == user.CustomerId).AsEnumerable()
select new SelectListItem
{
Text = p.VendorName,
Value = p.RecordId.ToString()
}).ToList();
storeStatus.Insert(0, new SelectListItem { Text = "Select Vendor", Value = "" });
return storeStatus;
}
private List <SelectListItem> GetStatus()
{
IARTDBNEWEntities db = new IARTDBNEWEntities();
List <SelectListItem> storeStatus = (from p in db.StatusMasters.AsEnumerable()
select new SelectListItem
{
Text = p.StatusName,
Value = p.RecordId.ToString()
}).ToList();
storeStatus.Insert(0, new SelectListItem {
Text = "Select Status", Value = ""
});
return(storeStatus);
}
private static List <SelectListItem> GetCountry()
{
IARTDBNEWEntities db = new IARTDBNEWEntities();
List <SelectListItem> storeStatus = (from p in db.COUNTRYLISTs.AsEnumerable()
select new SelectListItem
{
Text = p.NICENAME,
Value = p.ID.ToString()
}).ToList();
storeStatus.Insert(0, new SelectListItem {
Text = "Select Country", Value = ""
});
return(storeStatus);
}
private static List <SelectListItem> GetInvoice()
{
IARTDBNEWEntities db = new IARTDBNEWEntities();
List <SelectListItem> storeStatus = (from p in db.PurchaseOrders.AsEnumerable()
select new SelectListItem
{
Text = p.OrderNo,
Value = p.OrderNo.ToString()
}).ToList();
storeStatus.Insert(0, new SelectListItem {
Text = "Select Purchase Invoice", Value = ""
});
return(storeStatus);
}
private static List <SelectListItem> GetVendor()
{
IARTDBNEWEntities db = new IARTDBNEWEntities();
List <SelectListItem> storeStatus = (from p in db.VendorMasters.AsEnumerable()
select new SelectListItem
{
Text = p.VendorName,
Value = p.VendorName.ToString()
}).ToList();
storeStatus.Insert(0, new SelectListItem {
Text = "Select Vendor", Value = ""
});
return(storeStatus);
}
private List <SelectListItem> GetStore()
{
var user = (UserMaster)Session["User"];
IARTDBNEWEntities db = new IARTDBNEWEntities();
List <SelectListItem> storeStatus = (from p in db.StoreMasters.Where(e => e.CustomerId == user.CustomerId).AsEnumerable()
select new SelectListItem
{
Text = p.StoreName,
Value = p.RecordId.ToString()
}).ToList();
storeStatus.Insert(0, new SelectListItem {
Text = "Select Store", Value = ""
});
return(storeStatus);
}
private static List <SelectListItem> GetCity(int stateId)
{
IARTDBNEWEntities db = new IARTDBNEWEntities();
List <SelectListItem> storeStatus = (from p in db.CityMasters.AsEnumerable()
where p.StateId == stateId
select new SelectListItem
{
Text = p.City,
Value = p.Id.ToString()
}).ToList();
storeStatus.Insert(0, new SelectListItem {
Text = "Select City", Value = ""
});
return(storeStatus);
}
private List <SelectListItem> GetInvoice()
{
var user = (UserMaster)Session["User"];
IARTDBNEWEntities db = new IARTDBNEWEntities();
List <SelectListItem> storeStatus = (from p in db.PurchaseOrders.Where(e => e.CustomerId == user.CustomerId).AsEnumerable()
select new SelectListItem
{
Text = p.OrderNo,
Value = p.OrderNo.ToString()
}).ToList();
storeStatus.Insert(0, new SelectListItem {
Text = "Select Purchase Invoice", Value = ""
});
return(storeStatus);
}
public ActionResult EmployeeCPSReport(int?id)
{
IARTDBNEWEntities entities = new IARTDBNEWEntities();
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[18] {
new DataColumn("File No"), new DataColumn("Employee Code"),
new DataColumn("Title"),
new DataColumn("First Name"),
new DataColumn("MiddleName"),
new DataColumn("SurName"),
new DataColumn("Sex"),
new DataColumn("Date Of Birth"),
new DataColumn("Date Of Confirmation"),
new DataColumn("Date of Retirement"),
new DataColumn("Designation at Retirement"),
new DataColumn("Salary"), new DataColumn("Level"), new DataColumn("Date Of Retirement"), new DataColumn("PFA"), new DataColumn("PIN No."), new DataColumn("TEL No."), new DataColumn("Remark")
});
var employees = from a in db.EmployeeGIs
from b in db.EmployeePIs
from c in db.EmployeeSIs
where (a.EmployeeCode == b.EmployeeCode) && (b.EmployeeCode == c.EmployeeCode)
select new { empgi = a, emppi = b.MobileNo, empsi = c };
foreach (var emp in employees)
{
dt.Rows.Add(emp.empgi.File_No, emp.empgi.EmployeeCode, emp.empgi.Title, emp.empgi.First_Name, emp.empgi.Middle_Name, emp.empgi.Surname, emp.empgi.Sex, emp.empgi.DateOfBirth, emp.empgi.ConfirmationDate, emp.empgi.DateOfRetirement,
emp.empgi.Rank, emp.empsi.SalaryScale, emp.empgi.Grade_Level, emp.empgi.DateOfRetirement, emp.empsi.PFA, emp.empsi.RSAPinNo, emp.emppi, ""
);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.ShowRowColHeaders = true;
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "EmployeeCPSReport.xlsx"));
}
}
}
public JsonResult InsertCustomers(EmployeeAI obj, List <EmpAIAssociation> ObjAssociation, List <EmpAIConference> ObjConferenece)
{
int insertedRecords = 0;
try
{
using (IARTDBNEWEntities entities = new IARTDBNEWEntities())
{
int i = (from a in entities.EmployeeAIs where a.EmployeeCode == obj.EmployeeCode select a).Count();
if (i == 0)
{
entities.EmployeeAIs.Add(obj);
}
//Check for NULL.
if (ObjAssociation == null)
{
ObjAssociation = new List <EmpAIAssociation>();
}
//Loop and insert records.
foreach (EmpAIAssociation item in ObjAssociation)
{
entities.EmpAIAssociations.Add(item);
}
if (ObjConferenece == null)
{
ObjConferenece = new List <EmpAIConference>();
}
//Loop and insert records.
foreach (EmpAIConference item in ObjConferenece)
{
entities.EmpAIConferences.Add(item);
}
insertedRecords = entities.SaveChanges();
}
}
catch (Exception ext)
{
}
return(Json(insertedRecords));
}
public ActionResult EmployeeStaffNominalReport(int?id)
{
IARTDBNEWEntities entities = new IARTDBNEWEntities();
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[20]
{
new DataColumn("File_No"), new DataColumn("EmplyeeId"), new DataColumn("Name"), new DataColumn("Cadre"),
new DataColumn("Rank"), new DataColumn("DateOfBirth"), new DataColumn("FirstAppointmentLocation"),
new DataColumn("FirstAppointmentDate"), new DataColumn("LastPromotionDate"), new DataColumn("Qualification3"),
new DataColumn("StateOfOrigin"), new DataColumn("LGA"), new DataColumn("Sex"),
new DataColumn("SalaryScale"), new DataColumn("NhisNo"), new DataColumn("NhisProvider"),
new DataColumn("StationOfDeployment"), new DataColumn("PFA"), new DataColumn("MobileNo"), new DataColumn("DateOfRetirement")
});
var employees = from a in db.EmployeeGIs
from b in db.EmployeePIs
from c in db.EmployeeSIs
from d in db.EmployeeAIs
from e in db.EmployeeMIs
where (a.EmployeeCode == b.EmployeeCode) && (c.EmployeeCode == d.EmployeeCode) && (d.EmployeeCode == e.EmployeeCode) && (a.EmployeeCode == e.EmployeeCode)
select new { empgi = a, emppi = b, empsi = c, empai = d, empmi = e };
foreach (var emp in employees)
{
dt.Rows.Add(emp.empgi.File_No, emp.empgi.EmployeeCode, emp.empgi.First_Name, emp.empgi.Cadre, emp.empgi.Rank, emp.empgi.DateOfBirth, emp.empgi.FirstAppointmentLocation, emp.empgi.FirstAppointmentDate, emp.empgi.LastPromotionDate, emp.empai.Qualification3, emp.empgi.StateOfOrigin,
emp.empgi.LGA, emp.empgi.Sex, emp.empsi.SalaryScale, emp.empmi.NhisNo, emp.empmi.NhisProvider, emp.empgi.StationOfDeployment, emp.empsi.PFA, emp.emppi.MobileNo, emp.empgi.DateOfRetirement
);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "StaffNominalRoll.xlsx"));
}
}
}
public ActionResult EmployeeMIReport(int?id)
{
IARTDBNEWEntities entities = new IARTDBNEWEntities();
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[11] {
new DataColumn("File No"), new DataColumn("Employee Code"),
new DataColumn("Title"),
new DataColumn("First Name"),
new DataColumn("MiddleName"),
new DataColumn("SurName"),
new DataColumn("Sex"),
new DataColumn("NHIS No."),
new DataColumn("NHIS Provider"),
new DataColumn("Blood Group"),
new DataColumn("Genotype")
});
var employees = from a in db.EmployeeGIs
from b in db.EmployeePIs
from c in db.EmployeeSIs
from d in db.EmployeeMIs
where (a.EmployeeCode == b.EmployeeCode) && (c.EmployeeCode == d.EmployeeCode) && (a.EmployeeCode == d.EmployeeCode)
select new { empgi = a, emppi = b, empsi = c, empmi = d };
foreach (var emp in employees)
{
dt.Rows.Add(emp.empgi.File_No, emp.empgi.EmployeeCode, emp.empgi.Title, emp.empgi.First_Name, emp.empgi.Middle_Name, emp.empgi.Surname, emp.empgi.Sex, emp.empmi.NhisNo, emp.empmi.NhisProvider, emp.empmi.BloodGroup, emp.empmi.BloodGenotype);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.ShowRowColHeaders = true;
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "EmployeeMedicalRecordReport.xlsx"));
}
}
}
public ActionResult EmployeeGIReport(int?id)
{
IARTDBNEWEntities entities = new IARTDBNEWEntities();
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[18] {
new DataColumn("EmplyeeId"),
new DataColumn("Name"),
new DataColumn("Rank"),
new DataColumn("File_No"),
new DataColumn("Grade_Level"),
new DataColumn("Step"),
new DataColumn("Cadre"),
new DataColumn("DateOfBirth"),
new DataColumn("StateOfOrigin"),
new DataColumn("Religion"),
new DataColumn("StationOfDeployment"),
new DataColumn("DateOfRetirement"),
new DataColumn("LastPromotionDate"),
new DataColumn("Unit_Research"),
new DataColumn("Unit_Services"),
new DataColumn("Progrommes"),
new DataColumn("HomeTown"),
new DataColumn("LGA")
});
var employees = from emp in db.EmployeeGIs
select emp;
foreach (var emp in employees)
{
dt.Rows.Add(emp.EmployeeCode, emp.First_Name, emp.Rank, emp.File_No, emp.Grade_Level, emp.Step, emp.Cadre, emp.DateOfBirth, emp.StateOfOrigin, emp.Religion, emp.StationOfDeployment, emp.DateOfRetirement, emp.LastPromotionDate, emp.Unit_Research, emp.Unit_Services, emp.Home_Town, emp.LGA);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "EmployeeDetails.xlsx"));
}
}
}
public ActionResult EmployeeAIReport(int?id)
{
IARTDBNEWEntities entities = new IARTDBNEWEntities();
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[10] {
new DataColumn("File No"), new DataColumn("Employee Code"),
new DataColumn("Title"),
new DataColumn("First Name"),
new DataColumn("MiddleName"),
new DataColumn("SurName"),
new DataColumn("Sex"),
new DataColumn("Institute Attended"),
new DataColumn("Qualification"),
new DataColumn("Year Of Graduation")
});
var employees = from a in db.EmployeeGIs
from b in db.EmployeeAIs
where a.EmployeeCode == b.EmployeeCode
select new { empgi = a, empai = b };
foreach (var emp in employees)
{
dt.Rows.Add(emp.empgi.File_No, emp.empgi.EmployeeCode, emp.empgi.Title, emp.empgi.First_Name, emp.empgi.Middle_Name, emp.empgi.Surname, emp.empgi.Sex, emp.empai.InstitutionAttended1 + "," + emp.empai.InstitutionAttended2 + "," + emp.empai.InstitutionAttended3
, emp.empai.Qualification1 + "," + emp.empai.Qualification2 + "," + emp.empai.Qualification3, emp.empai.YearOfGraduation1 + "," + emp.empai.YearOfGraduation2 + "," + emp.empai.YearOfGraduation3);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.ShowRowColHeaders = true;
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "EmployeeAcademicReport.xlsx"));
}
}
}
public ActionResult ContractorAssessmentReport(int?id)
{
int SrNo = 1;
IARTDBNEWEntities entities = new IARTDBNEWEntities();
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[6] {
new DataColumn("SR.No"), new DataColumn("Name of Contractor"), new DataColumn("Name of Company"),
new DataColumn("Project Title"),
new DataColumn("LOT No."),
new DataColumn("Final Assessment Score")
});
var to = from a in db.PrequalificationScorings
select new { t = a };
foreach (var tinfo in to)
{
dt.Rows.Add(SrNo++, tinfo.t.ContractorName, tinfo.t.CompanyName, tinfo.t.ProjectTitle, tinfo.t.LotNo, tinfo.t.FinalScore);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.ShowRowColHeaders = true;
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ContractorAssessmentScoreReport.xlsx"));
}
}
}
public ActionResult EmployeePIReport(int?id)
{
IARTDBNEWEntities entities = new IARTDBNEWEntities();
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[54] {
new DataColumn("File No"), new DataColumn("Employee Code"),
new DataColumn("Title"),
new DataColumn("First Name"),
new DataColumn("MiddleName"),
new DataColumn("SurName"),
new DataColumn("Sex"),
new DataColumn("Qualification"),
new DataColumn("Date Of Birth"),
new DataColumn("Place Of Birth"),
new DataColumn("Marital Status"),
new DataColumn("Maiden Name"),
new DataColumn("Spouse Name"),
new DataColumn("State Of Origin"),
new DataColumn("LGA Of Origin"),
new DataColumn("Home Town"),
new DataColumn("Religion"),
new DataColumn("Blood Group"),
new DataColumn("Geno Type"),
new DataColumn("Contact Home Address"),
new DataColumn("Last Promotion"),
new DataColumn("Permanent Home Address"),
new DataColumn("State Of Residence"),
new DataColumn("LGA of Residence"), new DataColumn("Mobile No"), new DataColumn("Email"),
new DataColumn("FIRST APPT"), new DataColumn("DATE 0F FIRST APPT"), new DataColumn("PRESENT APPT"),
new DataColumn("Date of Present Appointment"), new DataColumn("Confirmation Date"),
new DataColumn("Date of Assumption of Duty"), new DataColumn("LAST PROMOTION"), new DataColumn("Job Town"),
new DataColumn("Bank Type"), new DataColumn("Bank Name"), new DataColumn("Bank Branch"), new DataColumn("Account Number"), new DataColumn("Bank Account Name"), new DataColumn("Pension Name"), new DataColumn("Pension Pin"), new DataColumn("Cadre/Job Schedule")
, new DataColumn("Department"), new DataColumn("Unit"), new DataColumn("Section"), new DataColumn("Salary Structure"), new DataColumn("Grade Level"), new DataColumn("Step"), new DataColumn("Establishment/Civil Service No"),
new DataColumn("NHIS NO"), new DataColumn("NHIS PROVIDER"), new DataColumn("STATION OF DEPLOYMENT"), new DataColumn("PFA"), new DataColumn("DATE OF RETIREMENT"),
});
var employees = from a in db.EmployeeGIs
from b in db.EmployeePIs
from c in db.EmployeeSIs
from d in db.EmployeeAIs
from e in db.EmployeeMIs
where (a.EmployeeCode == b.EmployeeCode) && (c.EmployeeCode == d.EmployeeCode) && (d.EmployeeCode == e.EmployeeCode) && (a.EmployeeCode == e.EmployeeCode)
select new { empgi = a, emppi = b, empsi = c, empai = d, empmi = e };
// S/N FILE NO Employee CODE Title (Mr, Mrs, Miss, DR. etc.) Surname First Name Middle Name SEX QUALIFICATION Date of Birth Place of Birth
//Marital Status Maiden Name Mother's Maiden Name Spouse Name State of Origin LGA of Origin Home town Religion
//Blood Group Genotype Contact Home Address LAST PROMOTION Permanent Home Address State of Residence
//LGA of Residence Mobile No Email FIRST APPT DATE 0F FIRST APPT PRESENT APPT Date of Present Appointment Confirmation Date Date of Assumption of Duty LAST PROMOTION Job Town
//Bank Type Bank Name Bank Branch Account Number Bank Account Name Pension Name Pension Pin Cadre/Job Schedule
//Department Unit Section Salary Structure Ministry/Agency Grade Level Step Establishment/Civil Service No
//NHIS NO NHIS PROVIDER STATION OF DEPLOYMENT PFA DATE OF RETIREMENT
foreach (var emp in employees)
{
dt.Rows.Add(emp.empgi.File_No, emp.empgi.EmployeeCode, emp.empgi.Title, emp.empgi.Surname, emp.empgi.First_Name, emp.empgi.Middle_Name, emp.empgi.Sex, emp.empai.Qualification3,
emp.empgi.DateOfBirth, emp.empgi.PlaceOfBirth, emp.empgi.Marital_Status, emp.empgi.Maiden_Name, emp.empgi.Spouse_Name, emp.empgi.StateOfOrigin, emp.empgi.LGA, emp.empgi.Home_Town, emp.empgi.Religion,
emp.empmi.BloodGroup, emp.empmi.BloodGenotype, emp.empgi.ContactHomeAddress, emp.empgi.LastPromotionDate, emp.emppi.PermanentAddress, emp.empgi.StateOfOrigin,
emp.empsi.BankType, emp.empsi.NameOfBanks, emp.empsi.BankBranch, emp.empsi.AccountNumber, emp.empsi.AccountName, emp.empsi.PFA, emp.empsi.RSAPinNo, emp.empgi.Cadre,
emp.empgi.Rank, emp.empgi.Unit_Research + "," + emp.empgi.Unit_Services, emp.empgi.Section, emp.empsi.SalaryScale, emp.empgi.Grade_Level, emp.empgi.Step, "N/A",
emp.empmi.NhisNo, emp.empmi.NhisProvider, emp.empgi.StationOfDeployment, emp.empsi.PFA, emp.empgi.DateOfRetirement
);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.ShowRowColHeaders = true;
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "EmployeePersonalInfoReport.xlsx"));
}
}
}
