public List <StaffPlan> StaffPlanImplementation_GetList(int pageNumber, int pageSize, string filter, int LanguageCode, out int total, out ToTalMonth ListTotalMonth)
{
try
{
//UnitOfWork.ConnectionString = ERP.Framework.Common.Utils.GetSetting<string>("NovaonADConnection");
ListTotalMonth = new ToTalMonth();
var param = new DynamicParameters();
param.Add("@FilterField", filter);
param.Add("@OrderBy", "");
param.Add("@PageNumber", pageNumber);
param.Add("@PageSize", pageSize);
param.Add("@Type", 1);
param.Add("@LanguageID", LanguageCode);
param.Add("@TotalRecord", 0, DbType.Int32, ParameterDirection.InputOutput);
param.Add("@TotalRecordM1", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordM2", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordM3", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordM4", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordM5", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordM6", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordM7", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordM8", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordM9", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordM10", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordM11", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordM12", 0, DbType.Double, ParameterDirection.InputOutput);
param.Add("@TotalRecordSumMonth", 0, DbType.Double, ParameterDirection.InputOutput);
var list = UnitOfWork.Procedure <StaffPlan>("StaffPlanImplementation_GetList", param).ToList();
var userId = Global.CurrentUser.UserID;
param = HttpRuntime.Cache.Get("StaffPlanImplementation_GetList-" + ERP.Framework.Common.Utils.GetKeyFromParam(param as object) + "-" + userId + "-output") as DynamicParameters;
total = param.GetDataOutput <int>("@TotalRecord");
ListTotalMonth.SumM1 = param.GetDataOutput <double>("@TotalRecordM1");
ListTotalMonth.SumM2 = param.GetDataOutput <double>("@TotalRecordM2");
ListTotalMonth.SumM3 = param.GetDataOutput <double>("@TotalRecordM3");
ListTotalMonth.SumM4 = param.GetDataOutput <double>("@TotalRecordM4");
ListTotalMonth.SumM5 = param.GetDataOutput <double>("@TotalRecordM5");
ListTotalMonth.SumM6 = param.GetDataOutput <double>("@TotalRecordM6");
ListTotalMonth.SumM7 = param.GetDataOutput <double>("@TotalRecordM7");
ListTotalMonth.SumM8 = param.GetDataOutput <double>("@TotalRecordM8");
ListTotalMonth.SumM9 = param.GetDataOutput <double>("@TotalRecordM9");
ListTotalMonth.SumM10 = param.GetDataOutput <double>("@TotalRecordM10");
ListTotalMonth.SumM11 = param.GetDataOutput <double>("@TotalRecordM11");
ListTotalMonth.SumM12 = param.GetDataOutput <double>("@TotalRecordM12");
ListTotalMonth.SumMonth = param.GetDataOutput <double>("@TotalRecordSumMonth");
return(list);
}
catch (Exception ex)
{
total = 0;
ListTotalMonth = null;
return(null);
}
finally
{
//UnitOfWork.ConnectionString = null;
}
}
public ActionResult TableServerSideGetData(int pageIndex, int pageSize, string filter = "")
{
var db = new OrganizationUnitPlan_DAL();
ToTalMonth ToTalMonth = new ToTalMonth();
int total = 0;
int Languagecode = Global.CurrentLanguage;
var result = db.OrganizationUnitPlan_GetList(pageIndex, pageSize, filter, Languagecode, out total, out ToTalMonth);
var lstTotal = new TableColumnsTotalModel();
lstTotal.Total1 = "15";
lstTotal.Total2 = "25";
lstTotal.Total3 = "35";
return(Content(JsonConvert.SerializeObject(new
{
employees = result,
totalCount = total,
lstTotal = lstTotal,
ToTalMonth = ToTalMonth
})));
}
public ActionResult StaffPlanExportExcel(string filter = "")
{
DataTable dt = new DataTable("Grid");
if (filter.Contains("!!") == true)
{
filter = filter.Replace("!!", "%");
}
dt.Columns.AddRange(new DataColumn[]
{
new DataColumn(AppRes.Employee),
new DataColumn(AppRes.OrganizationUnit),
new DataColumn(AppRes.Currency),
new DataColumn(AppRes.Year),
new DataColumn(AppRes.Promotion_Status),
new DataColumn(AppRes.L1),
new DataColumn(AppRes.L2),
new DataColumn(AppRes.L3),
new DataColumn(AppRes.L4),
new DataColumn(AppRes.L5),
new DataColumn(AppRes.L6),
new DataColumn(AppRes.L7),
new DataColumn(AppRes.L8),
new DataColumn(AppRes.L9),
new DataColumn(AppRes.L10),
new DataColumn(AppRes.L11),
new DataColumn(AppRes.L12),
new DataColumn(AppRes.R_Total),
});
dt.Columns[0].DataType = typeof(string);
dt.Columns[1].DataType = typeof(string);
dt.Columns[2].DataType = typeof(string);
dt.Columns[3].DataType = typeof(string);
dt.Columns[4].DataType = typeof(string);
dt.Columns[5].DataType = typeof(double);
dt.Columns[6].DataType = typeof(double);
dt.Columns[7].DataType = typeof(double);
dt.Columns[8].DataType = typeof(double);
dt.Columns[9].DataType = typeof(double);
dt.Columns[10].DataType = typeof(double);
dt.Columns[11].DataType = typeof(double);
dt.Columns[12].DataType = typeof(double);
dt.Columns[13].DataType = typeof(double);
dt.Columns[14].DataType = typeof(double);
dt.Columns[15].DataType = typeof(double);
dt.Columns[16].DataType = typeof(double);
dt.Columns[17].DataType = typeof(double);
var db = new StaffPlan_DAL();
ToTalMonth ToTalMonth = new ToTalMonth();
int total = 0;
int LanguageCode = Global.CurrentUser.CurrentLanguageID;
var lstData = db.StaffPlan_GetList(1, 100000, filter, LanguageCode, out total, out ToTalMonth);
foreach (var item in lstData)
{
dt.Rows.Add(item.StaffName, item.OrganizationUnitName, item.CurrencyName, item.Year, item.StatusName, item.M1, item.M2, item.M3, item.M4, item.M5, item.M6, item.M7, item.M8, item.M9, item.M10, item.M11, item.M12, item.SumValue);
}
var wb = new XLWorkbook();
wb.Worksheets.Add(dt);
byte[] data = null;
using (var stream = new MemoryStream())
{
wb.SaveAs(stream);
data = stream.ToArray();
}
return(File(data, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", " StaffPlan.xlsx"));
}
public ActionResult DownloadTemplate()
{
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[]
{
new DataColumn(AppRes.OrganazationId),
new DataColumn(AppRes.Department),
new DataColumn(AppRes.ContractCode),
new DataColumn(AppRes.Year),
new DataColumn(AppRes.StatusCode),
new DataColumn(AppRes.L1),
new DataColumn(AppRes.L2),
new DataColumn(AppRes.L3),
new DataColumn(AppRes.L4),
new DataColumn(AppRes.L5),
new DataColumn(AppRes.L6),
new DataColumn(AppRes.L7),
new DataColumn(AppRes.L8),
new DataColumn(AppRes.L9),
new DataColumn(AppRes.L10),
new DataColumn(AppRes.L11),
new DataColumn(AppRes.L12),
new DataColumn(""),
new DataColumn(""),
new DataColumn(AppRes.ColumnName),
new DataColumn(AppRes.RequestContent),
new DataColumn(""),
new DataColumn(""),
new DataColumn(AppRes.ContractCode + " "),
new DataColumn(AppRes.ContactTypeID + " " + AppRes.Contract),
new DataColumn(""),
new DataColumn(""),
new DataColumn(AppRes.StatusCode + " "),
new DataColumn(AppRes.StatusList),
});
dt.Columns[0].DataType = typeof(string);
dt.Columns[1].DataType = typeof(string);
dt.Columns[2].DataType = typeof(string);
dt.Columns[3].DataType = typeof(string);
dt.Columns[4].DataType = typeof(string);
dt.Columns[5].DataType = typeof(string);
dt.Columns[6].DataType = typeof(string);
dt.Columns[7].DataType = typeof(string);
dt.Columns[8].DataType = typeof(string);
dt.Columns[9].DataType = typeof(string);
dt.Columns[10].DataType = typeof(string);
dt.Columns[11].DataType = typeof(string);
dt.Columns[12].DataType = typeof(string);
dt.Columns[13].DataType = typeof(string);
dt.Columns[14].DataType = typeof(string);
dt.Columns[15].DataType = typeof(string);
dt.Columns[16].DataType = typeof(string);
dt.Columns[17].DataType = typeof(string);
dt.Columns[18].DataType = typeof(string);
dt.Columns[19].DataType = typeof(string);
dt.Columns[20].DataType = typeof(string);
dt.Columns[21].DataType = typeof(string);
dt.Columns[22].DataType = typeof(string);
dt.Columns[23].DataType = typeof(string);
dt.Columns[24].DataType = typeof(string);
dt.Columns[25].DataType = typeof(string);
dt.Columns[26].DataType = typeof(string);
dt.Columns[27].DataType = typeof(string);
dt.Columns[28].DataType = typeof(string);
var db = new OrganizationUnitPlanImplementation_DAL();
ToTalMonth ToTalMonth = new ToTalMonth();
var commonDAL = new CommonDal();
var listContratType = commonDAL.GetsWhereParentIDnotTree(1949, Global.CurrentLanguage);
var listStatus = commonDAL.GetsWhereParentIDnotTree(88, Global.CurrentLanguage);
List <string> ListColumn = new List <string>()
{
AppRes.OrganazationId, AppRes.Department, AppRes.ContractCode, AppRes.Year, AppRes.StatusName, AppRes.L1, AppRes.L2, AppRes.L3, AppRes.L4, AppRes.L5, AppRes.L6, AppRes.L7, AppRes.L8, AppRes.L9
, AppRes.L10, AppRes.L11, AppRes.L12
};
List <string> ListRequest = new List <string>()
{
AppRes.RequestStaffCode, AppRes.RequestStaffName, AppRes.RequestContractCode, AppRes.RequestYear, AppRes.RequestStaffCode, AppRes.RequestMonthInYear, AppRes.RequestMonthInYear
, AppRes.RequestMonthInYear, AppRes.RequestMonthInYear, AppRes.RequestMonthInYear, AppRes.RequestMonthInYear, AppRes.RequestMonthInYear, AppRes.RequestMonthInYear
, AppRes.RequestMonthInYear, AppRes.RequestMonthInYear, AppRes.RequestMonthInYear, AppRes.RequestMonthInYear
};
int total = 0;
int LanguageCode = Global.CurrentUser.CurrentLanguageID;
var rowsNumber = 0;
if (ListColumn.Count > listContratType.Count)
{
rowsNumber = ListColumn.Count;
}
else
{
rowsNumber = listContratType.Count;
}
for (int i = 0; i < rowsNumber; i++)
{
dt.Rows.Add(string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, ListColumn.Count() > i ? ListColumn[i] : " ", ListRequest.Count() > i ? ListRequest[i] : " ", " ", " ", listContratType.Count() > i ? listContratType[i].GlobalListID.ToString() : " ", listContratType.Count > i ? (Global.CurrentLanguage == 5 ? listContratType[i].Name : listContratType[i].NameEN) : " ", " ", " ", listStatus.Count() > i ? listStatus[i].Value.ToString() : " ", listStatus.Count() > i?(Global.CurrentLanguage == 5 ? listStatus[i].Name : listStatus[i].NameEN) : " ");
}
var wb = new XLWorkbook();
wb.Worksheets.Add(dt);
wb.Worksheet("Grid").Column(18).Style.Fill.BackgroundColor = XLColor.White;
wb.Worksheet("Grid").Column(19).Style.Fill.BackgroundColor = XLColor.White;
wb.Worksheet("Grid").Column(22).Style.Fill.BackgroundColor = XLColor.White;
wb.Worksheet("Grid").Column(23).Style.Fill.BackgroundColor = XLColor.White;
wb.Worksheet("Grid").Column(26).Style.Fill.BackgroundColor = XLColor.White;
wb.Worksheet("Grid").Column(27).Style.Fill.BackgroundColor = XLColor.White;
byte[] data = null;
using (var stream = new MemoryStream())
{
wb.SaveAs(stream);
data = stream.ToArray();
}
return(File(data, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "TemplateOrganizationUnitPlanImplementation.xlsx"));
}
