public static TRetunrLoadData LoadData_Indicator(CSearch itemSearch)
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
TRetunrLoadData result = new TRetunrLoadData();
List <TDataIndicator> lstData = new List <TDataIndicator>();
if (!UserAcc.UserExpired())
{
lstData = db.mTIndicator.Select(s => new TDataIndicator
{
nID = s.ID,
sIndicator = s.Indicator,
nOrder = s.nOrder,
sPermission = "1",
}).OrderBy(o => o.nOrder).ToList();
//#region//SORT
//int? nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
//switch ((itemSearch.sOrderBy + "").ToLower())
//{
// case SystemFunction.ASC:
// {
// switch (nSortCol)
// {
// case 1: lstData = lstData.OrderBy(o => o.sCompName).ToList(); break;
// case 2: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
// case 3: lstData = lstData.OrderBy(o => o.dUpdate).ToList(); break;
// case 4: lstData = lstData.OrderBy(o => o.nCountFacility).ToList(); break;
// }
// }
// break;
// case SystemFunction.DESC:
// {
// switch (nSortCol)
// {
// case 1: lstData = lstData.OrderByDescending(o => o.sCompName).ToList(); break;
// case 2: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
// case 3: lstData = lstData.OrderByDescending(o => o.dUpdate).ToList(); break;
// case 4: lstData = lstData.OrderByDescending(o => o.nCountFacility).ToList(); break;
// }
// }
// break;
//}
//#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
//foreach (var item in lstData)
//{
// item.sUpdate = item.dUpdate.DateString();
// item.sLink = "<a class='btn btn-warning' href='admin_company_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
//}
result.lstDataIndicator = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
lstData = db.mTCompany.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : true) && (!string.IsNullOrEmpty(sStatus) ? w.cActive.ToLower().Contains(sStatus) : true)).Select(s => new TDataTable
{
nCompID = s.ID,
sCompName = s.Name,
sStatus = s.cActive == "Y" ? "Active" : "Inactive",
dUpdate = s.dUpdate,
SearchBy = "",
}).OrderByDescending(o => o.dUpdate).ToList();
var lstDataFac = db.mTFacility.Where(w => w.cDel == "N").ToList();
var lstCompanyID_SearchBySub = db.mTFacility.Where(w => w.cActive == "Y" && w.cDel == "N" && w.nLevel == 2 && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : false)).Select(s => new { s.CompanyID }).Distinct().ToList();
var lstCompanyID_SearchBySubLve1 = db.mTFacility.Where(w => w.cActive == "Y" && w.cDel == "N" && w.nLevel == 1 && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : false)).Select(s => new { s.CompanyID }).Distinct().ToList();
if (lstCompanyID_SearchBySub.Any())
{
List <int> lstComID = new List <int>();
lstComID = lstCompanyID_SearchBySub.Select(s => s.CompanyID).ToList();
var lstCompany = db.mTCompany.Where(w => w.cDel == "N" && lstComID.Contains(w.ID)).Select(s => s.ID).ToList();
if (lstCompany.Any())
{
foreach (var item in lstCompany)
{
///// Check ว่ามีไอดีนี้ในบริษัทไหม ถ้าไม่มีค่อย แอด เพิ่ม
var q = lstData.FirstOrDefault(w => w.nCompID == item);
if (q == null)
{
List <TDataTable> lstDataSub2 = new List <TDataTable>();
lstDataSub2 = db.mTCompany.Where(w => w.cDel == "N" && w.ID == item).Select(s => new TDataTable
{
nCompID = s.ID,
sCompName = s.Name,
sStatus = s.cActive == "Y" ? "Active" : "Inactive",
dUpdate = s.dUpdate,
SearchBy = "Y",
}).OrderByDescending(o => o.dUpdate).ToList();
lstData.AddRange(lstDataSub2);
}
}
}
}
if (lstCompanyID_SearchBySubLve1.Any())
{
List <int> lstComID = new List <int>();
lstComID = lstCompanyID_SearchBySubLve1.Select(s => s.CompanyID).ToList();
var lstCompany = db.mTCompany.Where(w => w.cDel == "N" && lstComID.Contains(w.ID)).Select(s => s.ID).ToList();
if (lstCompany.Any())
{
foreach (var item in lstCompany)
{
///// Check ว่ามีไอดีนี้ในบริษัทไหม ถ้าไม่มีค่อย แอด เพิ่ม
var q = lstData.FirstOrDefault(w => w.nCompID == item);
if (q == null)
{
List <TDataTable> lstDataSub1 = new List <TDataTable>();
lstDataSub1 = db.mTCompany.Where(w => w.cDel == "N" && w.ID == item).Select(s => new TDataTable
{
nCompID = s.ID,
sCompName = s.Name,
sStatus = s.cActive == "Y" ? "Active" : "Inactive",
dUpdate = s.dUpdate,
SearchBy = "Y",
}).OrderByDescending(o => o.dUpdate).ToList();
lstData.AddRange(lstDataSub1);
}
}
}
}
foreach (var item in lstData)
{
string sLinkFacility = "";
item.nCountFacility = lstDataFac.Where(w => w.CompanyID == item.nCompID && w.cDel == "N" && (item.nCompID == 1 ? w.nLevel == 0 : w.nLevel == 1)).Count();
sLinkFacility = "<div class='btn-group'>" +
"<a class='btn btn-sm btn-primary' href='admin_facility_lst.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><span class='badge'>" + lstDataFac.Where(w => w.CompanyID == item.nCompID && w.cDel == "N" && (item.nCompID == 1 ? w.nLevel == 0 : w.nLevel == 1)).Count() + "</span></a>" +
"<a class='btn btn-sm btn-info' href='admin_facility_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><i class='fa fa-plus'></i></a>" +
"</div>";
item.sUpdate = item.dUpdate.DateString();
item.sLink = "<a class='btn btn-warning' href='admin_company_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
//item.sLinkFacility = "<a class='btn btn-info' href='admin_facility_lst.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><i class='fa fa-search'></i></a>";
item.sLinkFacility = sLinkFacility;
}
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sCompName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.dUpdate).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.nCountFacility).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sCompName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.dUpdate).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.nCountFacility).ToList(); break;
}
}
break;
}
#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
result.lstData = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
int nComID = SystemFunction.GetIntNullToZero(STCrypt.Decrypt(itemSearch.sComID));
lstData = db.mTFacility.Where(w => w.cDel == "N" && w.nLevel == 0 && w.CompanyID == nComID && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : true) && (!string.IsNullOrEmpty(sStatus) ? w.cActive.ToLower().Contains(sStatus) : true)).Select(s => new TDataTable
{
nCompanyID = s.CompanyID,
nFacilityID = s.ID,
sFacilityName = s.Name,
sStatus = s.cActive == "Y" ? "Active" : "Inactive",
dUpdate = s.dUpdate,
}).OrderByDescending(o => o.dUpdate).ToList();
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sFacilityName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.dUpdate).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.dUpdate).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sFacilityName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.dUpdate).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.dUpdate).ToList(); break;
}
}
break;
}
#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
foreach (var item in lstData)
{
item.sUpdate = item.dUpdate.DateString();
item.sLink = "<a class='btn btn-warning' href='admin_facility_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompanyID.ToString())) + "&&strFacID=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nFacilityID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
}
result.lstData = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static TRetunrLoadData LoadData_Facility(CSearch itemSearch, string sID)
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
TRetunrLoadData result = new TRetunrLoadData();
List <TDataFacility> lstData = new List <TDataFacility>();
if (!UserAcc.UserExpired())
{
string sOperationID = sID.STCDecrypt();
if (!string.IsNullOrEmpty(sOperationID))
{
int nOperationID = sOperationID.toIntNullToZero();
var lst_Facility = db.mTFacility_Operationtype.Where(w => w.nOperationtypeID == nOperationID).ToList();
if (lst_Facility != null)
{
lst_Facility.ForEach(f =>
{
var lst = db.mTFacility.Where(w => w.ID == f.nFacID).Select(s => new TDataFacility
{
nID = s.ID,
sName = s.Name,
}).ToList();
lstData.AddRange(lst);
});
}
}
//#region//SORT
//int? nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
//switch ((itemSearch.sOrderBy + "").ToLower())
//{
// case SystemFunction.ASC:
// {
// switch (nSortCol)
// {
// case 1: lstData = lstData.OrderBy(o => o.sCompName).ToList(); break;
// case 2: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
// case 3: lstData = lstData.OrderBy(o => o.dUpdate).ToList(); break;
// case 4: lstData = lstData.OrderBy(o => o.nCountFacility).ToList(); break;
// }
// }
// break;
// case SystemFunction.DESC:
// {
// switch (nSortCol)
// {
// case 1: lstData = lstData.OrderByDescending(o => o.sCompName).ToList(); break;
// case 2: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
// case 3: lstData = lstData.OrderByDescending(o => o.dUpdate).ToList(); break;
// case 4: lstData = lstData.OrderByDescending(o => o.nCountFacility).ToList(); break;
// }
// }
// break;
//}
//#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
//foreach (var item in lstData)
//{
// item.sUpdate = item.dUpdate.DateString();
// item.sLink = "<a class='btn btn-warning' href='admin_company_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
//}
result.lstDataFacility = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sCon = "";
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
if (!string.IsNullOrEmpty(sSearch))
{
sCon += @" AND sName LIKE '%" + sSearch + "%' ";
}
if (!string.IsNullOrEmpty(sStatus))
{
sCon += @" AND (LOWER(cActive) = '" + sStatus + @"') ";
}
var TB_Effluent_Product = db.TEffluent_OtherProduct_Point.ToList(); //IsUse
string _SQL = @" select nUnitID
,sName
,CASE WHEN cActive = 'Y' THEN 'Active' ELSE 'Inactive' END as sStatus
,dUpdate
from TM_Effluent_Unit
where cDel = 'N' " + sCon + @"
ORDER BY dUpdate DESC";
DataTable dt = CommonFunction.Get_Data(SystemFunction.strConnect, _SQL);
lstData = CommonFunction.ConvertDatableToList <TDataTable>(dt).ToList();
foreach (var item in lstData)
{
item.sUpdate = item.dUpdate.DateString();
item.sLink = "<a class='btn btn-warning' href='admin_master_effluent_otherunit_update.aspx?str=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nUnitID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
item.nIsUse = TB_Effluent_Product.Any(a => a.nUnitID == item.nUnitID) ? TB_Effluent_Product.Where(a => a.nUnitID == item.nUnitID).Count() : 0;
}
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.sUpdate).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.sUpdate).ToList(); break;
}
}
break;
}
#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
var lstDataFac = db.mTFacility.Where(w => w.cDel == "N").ToList();
result.lstData = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
var lstHistory = db.TViewMenu_History.ToList();
DateTime dStartValue = DateTime.ParseExact(itemSearch.sStartDate, "dd/MM/yyyy", new CultureInfo("en-US"));
DateTime dEndValue = DateTime.ParseExact(itemSearch.sEndDate, "dd/MM/yyyy", new CultureInfo("en-US"));
var lstMenu = db.TMenu.Where(w => w.cActive == "Y").ToList();
var lstUser = db.mTUser.ToList();
sslstData = new List <TDataTable>();
lstHistory = lstHistory.Where(w => (w.dAction.Value.Date >= dStartValue.Date && w.dAction.Value.Date <= dEndValue)).ToList();
var lstData = (from a in lstHistory
from b in lstMenu.Where(w => w.nMenuID == a.nMenuID)
from c in lstUser.Where(w => w.ID == a.nUserID)
select new TDataTable
{
nLogID = a.nLogID,
nMenuID = a.nMenuID,
nUserID = a.nUserID,
dActionDate = a.dAction,
Firstname = c.Firstname,
Username = c.Username,
Lastname = c.Lastname,
sMenu = b.sMenuName,
nLevel = b.nLevel,
nHead = b.sMenuHeadID
}).ToList();
lstData.ForEach(f =>
{
f.sAction = SystemFunction.CovertDateEn2Th(f.dActionDate.Value + "", "EN4");
if (f.nLevel == 2)
{
var mu = lstMenu.FirstOrDefault(w => w.nMenuID == f.nHead);
if (mu != null)
{
f.sMenu = mu.sMenuName;
var mu2 = lstMenu.FirstOrDefault(w => w.nMenuID == mu.sMenuHeadID);
if (mu != null)
{
f.sMenuHead = mu2.sMenuName;
}
}
}
else if (f.nLevel == 1)
{
var mu = lstMenu.FirstOrDefault(w => w.nMenuID == f.nHead);
if (mu != null)
{
f.sMenuHead = mu.sMenuName;
}
}
else
{
f.sMenuHead = f.sMenu;
f.sMenu = "";
}
});
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.Username).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.Firstname).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.sMenuHead).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.sMenu).ToList(); break;
case 5: lstData = lstData.OrderBy(o => o.dActionDate).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.Username).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.Firstname).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.sMenuHead).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.sMenu).ToList(); break;
case 5: lstData = lstData.OrderByDescending(o => o.dActionDate).ToList(); break;
}
}
break;
}
#endregion
sslstData = lstData;
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
result.lstData = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
if (!string.IsNullOrEmpty(sStatus))
{
sStatus = sStatus == "w" ? null : sStatus;
}
lstData = db.TContactUs.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.sContactName.ToLower().Contains(sSearch) : true) && (sStatus != "" ? w.cStatusAns == sStatus : true)).Select(s => new TDataTable
{
nContactID = s.nContactID,
sContactName = s.sContactName,
cStatusAns = s.cStatusAns == null ? "Wait" : s.cStatusAns == "1" ? "Read" : "Success",
cStatus = s.cStatusAns,
dCreate = s.dCreate,
dUpdate = s.dUpdate,
}).OrderByDescending(o => o.dUpdate).ToList();
//lstData = db.mTCompany.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : true)).Select(s => new TDataTable
//{
// nCompID = s.ID,
// sCompName = s.Name,
// sStatus = s.cActive == "Y" ? "Active" : "Inactive",
// dUpdate = s.dUpdate
//}).OrderByDescending(o => o.dUpdate).ToList();
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sContactName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.dCreate).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.cStatus).ToList(); break;
//case 4: lstData = lstData.OrderBy(o => o.nCountFacility).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sContactName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.dCreate).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.cStatus).ToList(); break;
//case 4: lstData = lstData.OrderByDescending(o => o.nCountFacility).ToList(); break;
}
}
break;
}
#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
foreach (var item in lstData)
{
var color = item.cStatus == null ? "warning" : item.cStatus == "1" ? "primary" : "success";
var Wordding = item.cStatus == null ? "Reply" : item.cStatus == "1" ? "Read" : "View";
var icon = item.cStatus == null ? "edit" : item.cStatus == "1" ? "search" : "search";
item.sUpdate = item.dUpdate.DateString();
item.sCreate = item.dCreate.DateString();
item.sLink = "<a class='btn btn-" + color + "' href='admin_ContactUs_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nContactID.ToString())) + "'><i class='fa fa-" + icon + "'></i> " + Wordding + "</a>";
}
result.lstData = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sCon = "";
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
if (!string.IsNullOrEmpty(sSearch))
{
sCon += @" AND (LOWER(w.sCode) LIKE '%" + sSearch + "%' OR LOWER(w.sName) LIKE '%" + sSearch + "%' OR LOWER(td.sName) LIKE '%" + sSearch + "%') ";
}
if (!string.IsNullOrEmpty(sStatus))
{
sCon += @" AND (LOWER(w.cActive) = '" + sStatus + @"') ";
}
var Data_type = db.TData_Type.AsEnumerable().Where(w => w.cActive == "Y").ToList(); //GetDataTYPE_Name
var TB_Waste_Product = db.TWaste_Product_data.ToList(); //IsUse
string _SQL = @" SELECT w.ID as nWasteID
,w.sCode as sCode
,w.sName as sName
,w.nTypeID as nTypeID
,w.dUpdate
,td.sName as sTypeName
,CASE WHEN w.cActive = 'Y' THEN 'Active' ELSE 'Inactive' END as sStatus
FROM TM_WasteDisposal w
LEFT JOIN TData_Type td ON w.nTypeID = td.nID
WHERE w.cDel = 'N' " + sCon + @"
ORDER BY w.dUpdate DESC ";
DataTable dt = CommonFunction.Get_Data(SystemFunction.strConnect, _SQL);
lstData = CommonFunction.ConvertDatableToList <TDataTable>(dt).ToList();
//lstData = db.TM_WasteDisposal.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.sName.ToLower().Contains(sSearch) || w.sCode.ToLower().Contains(sSearch) : true) && (!string.IsNullOrEmpty(sStatus) ? w.cActive.ToLower().Contains(sStatus) : true)).Select(s => new TDataTable
//{
// nWasteID = s.ID,
// sCode = s.sCode,
// sName = s.sName,
// nTypeID = s.nTypeID,
// sStatus = s.cActive == "Y" ? "Active" : "Inactive",
// dUpdate = s.dUpdate
//}).OrderByDescending(o => o.dUpdate).ToList();
foreach (var item in lstData)
{
item.sUpdate = item.dUpdate.DateString();
item.sLink = "<a class='btn btn-warning' href='admin_master_waste_update.aspx?str=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nWasteID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
//item.sTypeName = Data_type.Any(a => a.nID == item.nTypeID) ? Data_type.First(a => a.nID == item.nTypeID).sName : "";
item.nIsUse = TB_Waste_Product.Any(a => a.nDisposalID == item.nWasteID) ? TB_Waste_Product.Where(a => a.nDisposalID == item.nWasteID).Count() : 0;
}
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sTypeName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sName).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sTypeName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sName).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
}
}
break;
}
#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
var lstDataFac = db.mTFacility.Where(w => w.cDel == "N").ToList();
result.lstData = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static CReturnLoadData LoadData(CSearch itemSearch)
{
CReturnLoadData result = new CReturnLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
int nRoleID = UserAcc.GetObjUser().nRoleID;
List <TDataTable> lstData = new List <TDataTable>();
int nYear = itemSearch.sYear.toIntNullToZero();
#region SQL Sub-facility
string _sConditonSubFac = "";
if (!string.IsNullOrEmpty(itemSearch.sIndicatorID))
{
_sConditonSubFac += " AND TEPI.IDIndicator='" + CommonFunction.ReplaceInjection(itemSearch.sIndicatorID) + "'";
}
string sqlSubFac = @"SELECT T1.FormID,T1.sYear, ISNULL(T1.IDIndicator,0) 'IDIndicator',ISNULL(T1.OperationtypeID,0) 'OperationtypeID',ISNULL(T1.FacilityID,0) 'FacilityID',T1.sFacName,ISNULL(T1.nHeaderID,0) 'nHeaderID', T1.nQuarter FROM
(
--Q1
SELECT TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.Name 'sFacName',TFAC.nHeaderID,1 'nQuarter',CASE WHEN (COUNT(TWF.nMonth)) = 3 THEN 1 ELSE 0 END AS nStatus
FROM TEPI_Forms TEPI
INNER JOIN TEPI_Workflow TWF ON TEPI.FormID=TWF.FormID
INNER JOIN mTFacility TFAC ON TEPI.FacilityID=TFAC.ID AND TEPI.OperationtypeID=TFAC.OperationTypeID AND TFAC.cActive='Y' AND TFAC.cDel='N'
WHERE TEPI.sYear='{0}' AND TWF.nStatusID=5 AND TWF.nMonth IN (1,2,3) " + _sConditonSubFac + @"
GROUP BY TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.nHeaderID,TFAC.Name
UNION
--Q2
SELECT TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.Name 'sFacName',TFAC.nHeaderID,2 'nQuarter',CASE WHEN (COUNT(TWF.nMonth)) = 3 THEN 1 ELSE 0 END AS nStatus
FROM TEPI_Forms TEPI
INNER JOIN TEPI_Workflow TWF ON TEPI.FormID=TWF.FormID
INNER JOIN mTFacility TFAC ON TEPI.FacilityID=TFAC.ID AND TEPI.OperationtypeID=TFAC.OperationTypeID AND TFAC.cActive='Y' AND TFAC.cDel='N'
WHERE TEPI.sYear='{0}' AND TWF.nStatusID=5 AND TWF.nMonth IN (4,5,6) " + _sConditonSubFac + @"
GROUP BY TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.nHeaderID,TFAC.Name
UNION
--Q3
SELECT TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.Name 'sFacName',TFAC.nHeaderID,3 'nQuarter',CASE WHEN (COUNT(TWF.nMonth)) = 3 THEN 1 ELSE 0 END AS nStatus
FROM TEPI_Forms TEPI
INNER JOIN TEPI_Workflow TWF ON TEPI.FormID=TWF.FormID
INNER JOIN mTFacility TFAC ON TEPI.FacilityID=TFAC.ID AND TEPI.OperationtypeID=TFAC.OperationTypeID AND TFAC.cActive='Y' AND TFAC.cDel='N'
WHERE TEPI.sYear='{0}' AND TWF.nStatusID=5 AND TWF.nMonth IN (7,8,9) " + _sConditonSubFac + @"
GROUP BY TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.nHeaderID,TFAC.Name
UNION
--Q4
SELECT TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.Name 'sFacName',TFAC.nHeaderID,4 'nQuarter',CASE WHEN (COUNT(TWF.nMonth)) = 3 THEN 1 ELSE 0 END AS nStatus
FROM TEPI_Forms TEPI
INNER JOIN TEPI_Workflow TWF ON TEPI.FormID=TWF.FormID
INNER JOIN mTFacility TFAC ON TEPI.FacilityID=TFAC.ID AND TEPI.OperationtypeID=TFAC.OperationTypeID AND TFAC.cActive='Y' AND TFAC.cDel='N'
WHERE TEPI.sYear='{0}' AND TWF.nStatusID=5 AND TWF.nMonth IN (10,11,12) " + _sConditonSubFac + @"
GROUP BY TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.nHeaderID,TFAC.Name
) AS T1
WHERE T1.nStatus=1
";
#endregion
string _sqlSub = string.Format(sqlSubFac, nYear);
var lstDataSubFac = db.Database.SqlQuery <TDataSubFac>(_sqlSub).ToList();
#region SQL Main facility
string sqlMainFac = @"SELECT TFPTT.ID 'nPTTFacID',TFPTT.Name 'sPTTFacName',TFPTT.sMappingCodePTT
,TFGC.ID 'nGCFacID',TFGC.Name 'sGCFacName'
FROM mTFacility TFPTT
INNER JOIN mTFacility TFGC ON TFPTT.ID=TFGC.nHeaderID AND TFGC.nLevel=1 AND TFGC.cActive='Y' AND TFGC.cDel='N'
WHERE TFPTT.nLevel=0 and TFPTT.cActive='Y' and TFPTT.cDel='N'";
#endregion
var lstDataMainFac = db.Database.SqlQuery <TDataMainFaciltiy>(sqlMainFac).ToList();
var dataStausWF = db.TStatus_Workflow.Where(w => w.cTypeUse == "TNF" && w.cActive == "Y").ToList();
int?nIndID = itemSearch.sIndicatorID.toIntNull();
int?nStatusID = itemSearch.sStatus.toIntNull();
int?nFacID = itemSearch.sFacID.toIntNull();
lstData = (from mf in lstDataMainFac
from sf in lstDataSubFac.Where(w => w.nHeaderID == mf.nGCFacID)
from ind in db.mTIndicator.Where(w => w.ID == sf.IDIndicator)
from wf in db.TEPI_TransferPTT.Where(w => w.nYear == nYear && w.nFacilityID == sf.nHeaderID && w.nIndicatorID == sf.IDIndicator && w.nQuarter == sf.nQuarter).DefaultIfEmpty()
from st in dataStausWF.Where(w => (wf != null ? w.nStatustID == wf.nStatusID : false)).DefaultIfEmpty()
group new { mf, sf, st, ind } by new { mf.nGCFacID, mf.sGCFacName, mf.sPTTFacName, mf.sMappingCodePTT, sf.nQuarter, nIndID = ind.ID, ind.Indicator, nStatustID = (st != null ? st.nStatustID : 0), sStatusName = (st != null ? st.sShorttStatus : "Waiting Transfer"), dAction = wf != null ? wf.dAction : null } into grp
select new TDataTable
{
nYear = nYear,
nGCFacID = grp.Key.nGCFacID,
sGCFacName = grp.Key.sGCFacName,
sPTTFacilityCode = grp.Key.sMappingCodePTT,
sPTTFacilityName = grp.Key.sPTTFacName,
nQuarter = grp.Key.nQuarter,
nIndicatorID = grp.Key.nIndID,
sIndicatorName = grp.Key.Indicator,
nStatus = grp.Key.nStatustID,
sStatusName = grp.Key.sStatusName,
dAction = grp.Key.dAction
}).Where(w => (nIndID.HasValue ? w.nIndicatorID == nIndID : true) && (nStatusID.HasValue ? w.nStatus == nStatusID : true) && (nFacID.HasValue ? w.nGCFacID == nFacID : true))
.OrderByDescending(o => o.dAction).ThenBy(o => o.nYear).ThenBy(o => o.sGCFacName).ThenBy(o => o.nQuarter).ThenBy(o => o.sIndicatorName).ToList();
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.nYear).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.nQuarter).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.sGCFacName).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.sIndicatorName).ToList(); break;
case 5: lstData = lstData.OrderBy(o => o.sStatusName).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.nYear).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.nQuarter).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.sGCFacName).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.sIndicatorName).ToList(); break;
case 5: lstData = lstData.OrderByDescending(o => o.sStatusName).ToList(); break;
}
}
break;
}
#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
foreach (var item in lstData)
{
if (!string.IsNullOrEmpty(item.sPTTFacilityCode))
{
item.sBtnClass = "btn btn-primary";
item.sBtnText = "View Detail";
item.sWarningPTTCode = "N";
if (nRoleID == 4) //ENVI Corporate(L2)
{
if (item.nStatus == 0 || item.nStatus == 29 || item.nStatus == 33) //Waiting Submit/ Reject by PTT / Accept Edit Request by PTT
{
item.sLink = "epi_transfertoptt.aspx?stryear=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nYear + "")) + "&strfacid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nGCFacID + "")) + "&strindid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nIndicatorID + "")) + "&strq=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nQuarter + "")) + "&strmode=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("S"));
}
else if (item.nStatus == 28 || item.nStatus == 30)//Submitted to ptt / Approve by PTT
{
item.sBtnClass = "btn btn-warning";
item.sBtnText = "Request Edit";
item.sLink = "epi_transfertoptt.aspx?stryear=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nYear + "")) + "&strfacid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nGCFacID + "")) + "&strindid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nIndicatorID + "")) + "&strq=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nQuarter + "")) + "&strmode=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("R"));
}
else
{
item.sLink = "epi_transfertoptt.aspx?stryear=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nYear + "")) + "&strfacid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nGCFacID + "")) + "&strindid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nIndicatorID + "")) + "&strq=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("0")) + "&strmode=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("V"));
}
}
else
{
item.sLink = "epi_transfertoptt.aspx?stryear=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nYear + "")) + "&strfacid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nGCFacID + "")) + "&strindid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nIndicatorID + "")) + "&strq=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("0")) + "&strmode=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("V"));
}
}
else
{
item.sWarningPTTCode = "Y";
}
}
result.lstData = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_Failed;
}
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
int nRoleID = UserAcc.GetObjUser().nRoleID;
int nUserID = UserAcc.GetObjUser().nUserID;
string sCon = "";
string sSearch = itemSearch.sSearch.Trims().ToLower();
if (!string.IsNullOrEmpty(sSearch))
{
sCon += @" AND (tu.Firstname LIKE '%" + sSearch + "%' OR tu.Lastname LIKE '%" + sSearch + "%' OR tu.Username LIKE '%" + sSearch + "%') ";
}
if (!string.IsNullOrEmpty(itemSearch.sUserRole))
{
sCon += @" AND (tr.nRoleID = " + itemSearch.sUserRole + @") ";
}
if (!string.IsNullOrEmpty(itemSearch.sOperationSearch))
{
sCon += @" AND tf.OperationTypeID = " + itemSearch.sOperationSearch + @" ";
}
if (!string.IsNullOrEmpty(itemSearch.sFacilitySearch))
{
sCon += @" AND tmf.nFacilityID = " + itemSearch.sFacilitySearch + @" ";
}
if (!string.IsNullOrEmpty(itemSearch.sStatus))
{
sCon += @" AND tu.cActive = '" + itemSearch.sStatus + @"' ";
}
if (nRoleID == 6)
{
var Get_Fac = db.mTUser_FacilityPermission.Where(w => w.nUserID == nUserID && w.nRoleID == nRoleID).Select(s => s.nFacilityID).Distinct().ToList(); // User And Role Get Facility
if (Get_Fac.Any())
{
string Format = "";
Get_Fac.ForEach(f =>
{
int nFac = f;
Format += "," + nFac + "";
});
Format = Format.Remove(0, 1);
sCon += @" AND tmf.nFacilityID IN (" + Format + ") ";
}
//var Get_Operation = db.mTFacility.Where(w => Get_Fac.Contains(w.ID) && itemSearch.sOperationSearch != "" ? w.ID == int.Parse(itemSearch.sOperationSearch) : true).Select(s => s.OperationTypeID).Distinct().ToList();
var lst_User = db.mTUser_FacilityPermission.Where(w => Get_Fac.Contains(w.nFacilityID)).Select(s => s.nUserID).Distinct().ToList(); // User IN Facility Company_Role
if (lst_User.Any())
{
string Format = "";
lst_User.ForEach(f =>
{
int nUserId = f;
Format += "," + nUserId + "";
});
Format = Format.Remove(0, 1);
sCon += @" AND tu.ID IN (" + Format + ") ";
}
}
string _SQL = @" SELECT tu.ID as nID
,ISNULL(tu.Username,'') as Username
,ISNULL(tu.Firstname,'') as Firstname
,ISNULL(tu.Lastname,'') as Lastname
, ISNULL(tu.Company,'') as Company
, ISNULL(tu.Email,'') as Email
,CASE WHEN tu.cActive = 'Y' THEN 'Active' WHEN tu.cActive = 'N' THEN 'Inactive' ELSE '' END as sStatus
,tu.dUpdate as dUpdate
,ISNULL(tu.sUserCode,'') as sUserCode
,(SELECT COUNT(tr.nUID) FROM mTUserInRole tr WHERE tr.nUID = tu.ID) as nCountRole
FROM mTUser tu
LEFT JOIN mTUser_FacilityPermission tmf ON tmf.nUserID = tu.ID
LEFT JOIN TMenu_Permission tmu ON tmu.nUserID = tu.ID
LEFT JOIN mTFacility tf ON tf.ID = tmf.nFacilityID
LEFT JOIN mTUserInRole tr ON tr.nUID = tu.ID
WHERE tu.cDel = 'N' and tu.ID != 1 " + sCon + @"
GROUP BY tu.ID,tu.Username,tu.Firstname,tu.Lastname,tu.Company,tu.Email,tu.cActive,tu.dUpdate,tu.sUserCode
ORDER BY tu.dUpdate DESC ";
DataTable dt = CommonFunction.Get_Data(SystemFunction.strConnect, _SQL);
lstData = CommonFunction.ConvertDatableToList <TDataTable>(dt).ToList();
//lstData = db.mTUser.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.Firstname.ToLower().Contains(sSearch) || w.Lastname.ToLower().Contains(sSearch) : true)).Select(s => new TDataTable
//{
// //nCompID = s.ID,
// //sCompName = s.Name,
// //sStatus = s.cActive == "Y" ? "Active" : "Inactive",
// dUpdate = s.dUpdate,
// nID = s.ID,
// Firstname = (string.IsNullOrEmpty(s.Firstname)) ? "" : s.Firstname,
// Lastname = (string.IsNullOrEmpty(s.Lastname)) ? "" : s.Lastname,
// Username = (string.IsNullOrEmpty(s.Username)) ? "" : s.Username,
// Email = (string.IsNullOrEmpty(s.Email)) ? "" : s.Email,
// sUserCode = (string.IsNullOrEmpty(s.sUserCode)) ? "N/A" : s.sUserCode,
// Company = s.Company,
// sStatus = s.cActive == "Y" ? "Active" : "Inactive",
//}).OrderByDescending(o => o.dUpdate).ToList();
foreach (var item in lstData)
{
List <TDataRole> lstData1 = new List <TDataRole>();
item.sUpdate = item.dUpdate.DateString();
if (IsView)
{
item.sLink = "<a class='btn btn-primary' href='admin_user_info_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nID.ToString())) + "'><i class='fa fa-search'></i> View</a>";
}
else
{
item.sLink = "<a class='btn btn-warning' href='admin_user_info_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
}
}
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.Username).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.Firstname).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.Company).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.nCountRole).ToList(); break;
case 5: lstData = lstData.OrderBy(o => o.Email).ToList(); break;
case 6: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.Username).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.Firstname).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.Company).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.nCountRole).ToList(); break;
case 5: lstData = lstData.OrderByDescending(o => o.Email).ToList(); break;
case 6: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
}
}
break;
}
#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
result.lstData = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
//var Indicator = db.mTIndicator.ToList();
//var Facility = db.mTFacility.Where(w => w.cActive == "Y" && w.cDel == "N").ToList();
//var Operation = db.mOperationType.Where(w => w.cActive == "Y" && w.cDel == "N").ToList();
//var TbUser = db.mTUser.Where(w => w.cActive == "Y" && w.cDel == "N").ToList();
int Operation_ID = 0;
string sCon = "";
int nRoleID = UserAcc.GetObjUser().nRoleID;
int nUserID = UserAcc.GetObjUser().nUserID;
if (nRoleID == 6)
{
var Get_Fac = db.mTUser_FacilityPermission.Where(w => w.nUserID == nUserID && w.nRoleID == nRoleID).ToList(); // User And Role Get Facility
var lst_SubFac = Get_Fac.Select(id => id.nFacilityID).ToList(); //Facility ID in Company_admin
if (lst_SubFac.Any())
{
string Format = "";
lst_SubFac.ForEach(f =>
{
int FacID = f;
Format += "," + FacID + "";
});
Format = Format.Remove(0, 1);
sCon += @" AND mtf.IDFac IN (" + Format + ") ";
//sCon += @" AND tu.ID IN (" + Format + ") ";
}
}
string sSearch = itemSearch.sSearch.Trims().ToLower();
if (!string.IsNullOrEmpty(sSearch))
{
sCon += @" AND (LOWER(tu.Firstname) LIKE '%" + sSearch + "%' COLLATE THAI_CI_AI OR LOWER(tu.Lastname) LIKE '%" + sSearch + "%' COLLATE THAI_CI_AI OR LOWER(tu1.Firstname) LIKE '%" + sSearch + "%' COLLATE THAI_CI_AI OR LOWER(tu1.Lastname) LIKE '%" + sSearch + "%' COLLATE THAI_CI_AI) ";
}
if (!string.IsNullOrEmpty(itemSearch.sOperationID))
{
sCon += @" AND mf.OperationTypeID = " + itemSearch.sOperationID + @" ";
}
if (!string.IsNullOrEmpty(itemSearch.sFacilityID))
{
sCon += @" AND mtf.IDFac = " + itemSearch.sFacilityID + @" ";
}
if (!string.IsNullOrEmpty(itemSearch.sIndicatorID))
{
sCon += @" AND mtf.IDIndicator = " + itemSearch.sIndicatorID + @" ";
}
//&& (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : true)
string _SQL = @" SELECT mtf.IDFac as IDFac
,mtf.IDIndicator as IDIndicator
,mtf.L1 as L1
,mtf.L2 as L2
,mtf.dUpdate as dUpdate
,mti.Indicator as sGroupName
,mf.OperationTypeID as IDOperation
,ISNULL(mot.Name,'') as sOperationName
,ISNULL(mf.Name,'') as sFacilityName
,ISNULL(tu.Firstname,'') +' '+ ISNULL(tu.Lastname,'') AS sManagerName
,ISNULL(tu1.Firstname,'') +' '+ ISNULL(tu1.Lastname,'') AS sEnvironName
FROM mTWorkFlow mtf
INNER JOIN mTIndicator mti ON mtf.IDIndicator = mti.ID
INNER JOIN mTFacility mf ON mf.ID = mtf.IDFac AND mf.cActive = 'Y' AND mf.cDel = 'N'
INNER JOIN mOperationType mot ON mot.ID = mf.OperationTypeID AND mot.cActive = 'Y' AND mot.cDel = 'N'
LEFT JOIN mTUser tu ON tu.ID = mtf.L1 AND tu.cActive = 'Y' AND tu.cDel = 'N'
LEFT JOIN mTUser tu1 ON tu1.ID = mtf.L2 AND tu1.cActive = 'Y' AND tu1.cDel = 'N'
WHERE mtf.cDel ='N' " + sCon + @"
ORDER BY mtf.dUpdate DESC ";
DataTable dt = CommonFunction.Get_Data(SystemFunction.strConnect, _SQL);
lstData = CommonFunction.ConvertDatableToList <TDataTable>(dt).ToList();
//CommonFunction.
//lstData = db.mTWorkFlow.Where(w => w.cDel == "N").AsEnumerable().Select(s => new TDataTable
//{
// IDFac = s.IDFac,
// IDOperation = Operation_ID = Facility.Any(a => a.ID == s.IDFac && a.cDel == "N" && a.cActive == "Y") ? Facility.First(a => a.ID == s.IDFac && a.cDel == "N" && a.cActive == "Y").OperationTypeID : 0,
// IDIndicator = s.IDIndicator,
// sFacilityName = Facility.Any(a => a.ID == s.IDFac) ? Facility.First(a => a.ID == s.IDFac && a.cDel == "N" && a.cActive == "Y").Name : "",
// sGroupName = Indicator.Any(a => a.ID == s.IDIndicator) ? Indicator.First(a => a.ID == s.IDIndicator).Indicator : "",
// sOperationName = Operation.Any(a => a.ID == Operation_ID) ? Operation.First(a => a.ID == Operation_ID).Name : "",
// L1 = s.L1,
// L2 = s.L2,
// dUpdate = s.dUpdate,
// sManagerName = TbUser.Any(a => a.ID == s.L1) ? TbUser.First(a => a.ID == s.L1 && a.cActive == "Y" && a.cDel == "N").Firstname + ' ' + TbUser.First(a => a.ID == s.L1 && a.cActive == "Y" && a.cDel == "N").Lastname : "",
// sEnvironName = TbUser.Any(a => a.ID == s.L2) ? TbUser.First(a => a.ID == s.L2 && a.cActive == "Y" && a.cDel == "N").Firstname + ' ' + TbUser.First(a => a.ID == s.L2 && a.cActive == "Y" && a.cDel == "N").Lastname : "",
//}).OrderByDescending(o => o.dUpdate).ToList();
lstData = lstData.Where(w => w.sOperationName != "" && w.sFacilityName != "").ToList();
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sOperationName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sFacilityName).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.sGroupName).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.sManagerName).ToList(); break;
case 5: lstData = lstData.OrderBy(o => o.sEnvironName).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sOperationName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sFacilityName).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.sGroupName).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.sManagerName).ToList(); break;
case 5: lstData = lstData.OrderByDescending(o => o.sEnvironName).ToList(); break;
}
}
break;
}
#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
//foreach (var item in lstData)
//{
// item.sUpdate = item.dUpdate.DateString();
// item.sLink = "<a class='btn btn-warning' href='admin_company_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.IDOperation.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
//}
result.lstData = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities epi = new PTTGC_EPIEntities();
List <Data_Perm> lstPerm = new List <Data_Perm>();
int nRole = UserAcc.GetObjUser().nRoleID;
int nUserID = UserAcc.GetObjUser().nUserID;
string sSearch = CommonFunction.ReplaceInjection(itemSearch.sSearch.Trims().ToLower());
int nFacID = 0;
if (nRole == 3 || nRole == 4)
{
#region L1, L2
string sCondition = "";
if (itemSearch.nGroupIndID.HasValue)
{
sCondition += " AND f.IDIndicator=" + itemSearch.nGroupIndID.Value;
}
if (!string.IsNullOrEmpty(sSearch))
{
sCondition += " AND LOWER(mF.Name) LIKE '%" + sSearch + "%'";
}
if (itemSearch.nStatus.HasValue)
{
sCondition += " AND d.nHistoryStatusID =" + itemSearch.nStatus;
}
if (!string.IsNullOrEmpty(itemSearch.sFacID))
{
sCondition += " AND p.IDFac = " + itemSearch.sFacID;
}
#region SQL L1, L2
string _Query = "SELECT" +
" p.IDFac AS nFacilities" +
" ,p.IDIndicator AS nIndicator" +
" ,p.L1 AS nL1" +
" ,p.L2 AS nL2 " +
" ,f.FormID AS nFormID" +
" ,f.sYear AS sYear" +
" ,d.nMonth AS nMonth" +
" ,d.nHistoryStatusID AS nStatus" +
" ,d.nReportID AS nReportID" +
" ,mF.Name AS sNameFacilities" +
" ,mI.Indicator AS sNameIndicator" +
" ,CASE " +
" WHEN d.nMonth = 1 THEN 'Jan.'" +
" WHEN d.nMonth = 2 THEN 'Feb.'" +
" WHEN d.nMonth = 3 THEN 'Mar.'" +
" WHEN d.nMonth = 4 THEN 'Apr.'" +
" WHEN d.nMonth = 5 THEN 'May'" +
" WHEN d.nMonth = 6 THEN 'Jun.'" +
" WHEN d.nMonth = 7 THEN 'Jul.'" +
" WHEN d.nMonth = 8 THEN 'Aug.'" +
" WHEN d.nMonth = 9 THEN 'Sep.'" +
" WHEN d.nMonth = 10 THEN 'Oct.'" +
" WHEN d.nMonth = 11 THEN 'Nov.'" +
" WHEN d.nMonth = 12 THEN 'Dec.'" +
" ELSE 'Dec.'" +
" END sMonth" +
" ,d.nHistoryStatusID AS nStatus" +
" ,sf.sStatusName AS sStatus" +
" ,d.dAction" +
" FROM mTWorkFlow p" +
" INNER JOIN TEPI_Forms f ON f.FacilityID = p.IDFac AND f.IDIndicator = p.IDIndicator" +
" INNER JOIN TEPI_Workflow d ON d.FormID = f.FormID" +
" INNER JOIN mTFacility mF ON mF.ID = p.IDFac AND mF.cDel = 'N' AND mF.nLevel = 2" +
" INNER JOIN mTIndicator mI ON mI.ID = p.IDIndicator" +
" INNER JOIN TStatus_Workflow sf ON sf.nStatustID = d.nHistoryStatusID" +
" WHERE p.cDel = 'N' AND (p.L1 = " + nUserID + " OR p.L2 = " + nUserID + ") AND f.sYear=" + itemSearch.nYear + sCondition + " ORDER BY d.dAction DESC "; //f.sYear,d.nMonth,mF.Name,mI.Indicator
#endregion
lstPerm = epi.Database.SqlQuery <Data_Perm>(_Query).ToList();
int[] lstStatusL1 = { 1 };
int[] lstStatusL2 = { 4, 2 };
int[] lstStatusL0 = { 0, 8, 9, 24, 18 };
if (lstPerm.Any())
{
if (nRole == 3)//L1
{
lstPerm = lstPerm.Where(w => w.nL1 == nUserID && lstStatusL1.Contains(w.nStatus)).OrderByDescending(o => o.dAction).ThenBy(o => o.sYear).ThenBy(o => o.nMonth).ThenBy(o => o.sNameFacilities).ThenBy(o => o.sNameIndicator).ToList();
}
else if (nRole == 4)//L2
{
lstPerm = lstPerm.Where(w => w.nL2 == nUserID && lstStatusL2.Contains(w.nStatus)).OrderByDescending(o => o.dAction).ThenBy(o => o.sYear).ThenBy(o => o.nMonth).ThenBy(o => o.sNameFacilities).ThenBy(o => o.sNameIndicator).ToList();
}
}
#endregion
}
else if (nRole == 2)
{
#region L0
Func <int, string> GetMonthName = (month) =>
{
string sMonthName = "";
if (month >= 1 && month <= 12)
{
sMonthName = new DateTime(DateTime.Now.Year, month, 1).ToString("MMM", new CultureInfo("en-US"));
}
else
{
sMonthName = month + "";
}
return(sMonthName);
};
string sCondition = "";
if (itemSearch.nGroupIndID.HasValue)
{
sCondition += " AND TUF.nGroupIndicatorID=" + itemSearch.nGroupIndID.Value;
}
if (!string.IsNullOrEmpty(itemSearch.sFacID))
{
nFacID = int.Parse(itemSearch.sFacID);
}
//if (itemSearch.nStatus.HasValue)
//{
// sCondition += " AND TEPIWF.nHistoryStatusID =" + itemSearch.nStatus;
//}
#region sql
string sqlL0 = @"SELECT TF.ID 'nFacilityID',TF.OperationtypeID,TF.Name 'sFacilityName',TIND.ID 'nIndID',TIND.Indicator 'sIndicator'
,TEPI.FormID,TEPIWF.nReportID,TEPIWF.nMonth,TEPIWF.nHistoryStatusID ,TSWF.sStatusName,TEPIWF.dAction
FROM mTUser_FacilityPermission TUF
INNER JOIN mTFacility TF ON TUF.nFacilityID=TF.ID AND TF.nLevel=2
INNER JOIN mTFacility TFH ON TF.nHeaderID=TFH.ID AND TFH.nLevel=1
INNER JOIN mTIndicator TIND ON TUF.nGroupIndicatorID=TIND.ID
LEFT JOIN TEPI_Forms TEPI ON TUF.nGroupIndicatorID=TEPI.IDIndicator AND TF.ID=TEPI.FacilityID AND TF.OperationTypeID=TEPI.OperationTypeID AND TEPI.sYear='" + itemSearch.nYear + @"'
LEFT JOIN TEPI_Workflow TEPIWF ON TEPI.FormID=TEPIWF.FormID
LEFT JOIN TStatus_Workflow TSWF ON ISNULL(TEPIWF.nHistoryStatusID,0)=TSWF.nStatustID
WHERE TUF.nUserID=" + nUserID + " AND TUF.nRoleID=" + nRole + @" AND TUF.nPermission=2 AND TF.cDel='N' AND TF.cActive='Y' AND TFH.cDel='N' AND TFH.cActive='Y'
AND (TEPIWF.nHistoryStatusID IS NULL OR TEPIWF.nHistoryStatusID IN (0,8,9,24,18)) " + sCondition + @"
order by TEPIWF.dAction DESC";
#endregion
DataTable dt = new DataTable();
dt = CommonFunction.Get_Data(SystemFunction.strConnect, sqlL0);
var Query = dt.AsEnumerable().Select(s => new
{
nFacilityID = s.Field <int>("nFacilityID"),
OperationtypeID = s.Field <int>("OperationtypeID"),
sFacilityName = s.Field <string>("sFacilityName"),
nIndID = s.Field <int>("nIndID"),
sIndicator = s.Field <string>("sIndicator"),
FormID = s.Field <int?>("FormID"),
nReportID = s.Field <int?>("nReportID"),
nMonth = s.Field <int?>("nMonth"),
nStatusID = s.Field <int?>("nHistoryStatusID"),
sStatusName = s.Field <string>("sStatusName"),
dAction = s.Field <DateTime?>("dAction"),
});
lstPerm = Query.Where(w => w.FormID.HasValue).Select(s => new Data_Perm
{
nFacilities = s.nFacilityID,
nIndicator = s.nIndID,
sYear = itemSearch.nYear + "",
nFormID = s.FormID ?? 0,
nMonth = s.nMonth ?? 0,
sMonth = GetMonthName(s.nMonth ?? 0),
nReportID = s.nReportID ?? 0,
sNameFacilities = s.sFacilityName,
sNameIndicator = s.sIndicator,
nStatus = s.nStatusID ?? 0,
sStatus = s.sStatusName,
nOperationtypeID = s.OperationtypeID,
dAction = s.dAction
}).ToList();
var QueryNoAction = Query.Where(w => !w.FormID.HasValue).ToList();
foreach (var item in QueryNoAction)
{
for (var i = 1; i <= 12; i++)
{
lstPerm.Add(new Data_Perm
{
nFacilities = item.nFacilityID,
nOperationtypeID = item.OperationtypeID,
nIndicator = item.nIndID,
sYear = itemSearch.nYear + "",
nFormID = item.FormID ?? 0,
nMonth = i,
sMonth = GetMonthName(i),
nReportID = 0,
sNameFacilities = item.sFacilityName,
sNameIndicator = item.sIndicator,
nStatus = -1,
sStatus = "No Action",
dAction = null
});
}
}
lstPerm = lstPerm.Where(w => (itemSearch.nStatus != null ? w.nStatus == itemSearch.nStatus : true) && (!string.IsNullOrEmpty(itemSearch.sFacID) ? w.nFacilities == nFacID : true)).OrderByDescending(o => o.dAction).ThenBy(o => o.sYear).ThenBy(o => o.nMonth).ThenBy(o => o.sNameFacilities).ThenBy(o => o.sNameIndicator).ToList();
#endregion
}
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstPerm = lstPerm.OrderBy(o => int.Parse(o.sYear)).ToList(); break;
case 2: lstPerm = lstPerm.OrderBy(o => o.nMonth).ToList(); break;
case 3: lstPerm = lstPerm.OrderBy(o => o.sNameFacilities).ToList(); break;
case 4: lstPerm = lstPerm.OrderBy(o => o.sNameIndicator).ToList(); break;
//case 5: lstPerm = lstPerm.OrderBy(o => o.nStatus).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstPerm = lstPerm.OrderByDescending(o => int.Parse(o.sYear)).ToList(); break;
case 2: lstPerm = lstPerm.OrderByDescending(o => o.nMonth).ToList(); break;
case 3: lstPerm = lstPerm.OrderByDescending(o => o.sNameFacilities).ToList(); break;
case 4: lstPerm = lstPerm.OrderByDescending(o => o.sNameIndicator).ToList(); break;
//case 5: lstPerm = lstPerm.OrderByDescending(o => o.nStatus).ToList(); break;
}
}
break;
}
#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstPerm.Count);
lstPerm = lstPerm.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
foreach (var item in lstPerm)
{
if (nRole == 3 || nRole == 4)//L1,L2
{
item.sBtn = "<a class='btn btn-primary' href='epi_mytask_view.aspx?strid=" +
HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nFormID.ToString())) + "&strlevel=" +
HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(nRole.ToString())) + "'>" +
"<i class='fa fa-search'></i> View Detail</a>";
}
else if (nRole == 2)//L0
{
string shref = SystemFunction.ReturnPath(item.nIndicator, item.nOperationtypeID ?? 0, item.nFacilities + "", item.sYear, "");
item.sBtn = "<a class='btn btn-warning btn-sm' title='Action' href='" + shref + "'><i class='fa fa-edit'></i></a>";
}
}
result.lstData = lstPerm;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
//lstData = db.mTCompany.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : true)).Select(s => new TDataTable
//{
// nCompID = s.ID,
// sCompName = s.Name,
// sStatus = s.cActive == "Y" ? "Active" : "Inactive",
// dUpdate = s.dUpdate
//}).OrderByDescending(o => o.dUpdate).ToList();
var query = from oType in db.mOperationType.Where(w => w.cDel == "N" && w.cManage == "Y").OrderBy(o => o.ID)
from f in db.mTFacility.Where(w => w.OperationTypeID == oType.ID && w.cDel == "N" && w.cActive == "Y").DefaultIfEmpty()
from g in db.mTFacility_Operationtype.Where(w => w.nOperationtypeID == w.nOperationtypeID).DefaultIfEmpty()
where (!string.IsNullOrEmpty(sSearch) ? oType.Name.ToLower().Contains(sSearch) : true) && (!string.IsNullOrEmpty(sStatus) ? oType.cActive.ToLower().Contains(sStatus) : true)
group new { oType, g } by new { oType.ID, oType.Name, oType.Description, oType.sCode, oType.sRemark, oType.cActive, oType.dUpdate } into grp
select new
{
nID = grp.Key.ID,
sName = grp.Key.Name,
sRemark = grp.Key.sRemark,
sCode = grp.Key.sCode,
sStatus = grp.Key.cActive,
sDescription = grp.Key.Description,
dUpdate = grp.Key.dUpdate,
nFacility = grp.Where(w => w.g.nOperationtypeID == grp.Key.ID).Count(),
};
lstData = query.ToList().Select(s => new TDataTable
{
nID = s.nID,
sName = s.sName,
sDescription = s.sDescription,
nFacility = s.nFacility,
sCode = s.sCode,
sStatus = s.sStatus,
sRemark = s.sRemark,
dUpdate = s.dUpdate,
}).OrderByDescending(o => o.dUpdate).ToList();
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sDescription).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.nFacility).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sDescription).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.nFacility).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
}
}
break;
}
#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
// bool isView = hdfPrmsMenu.Value == 1;
foreach (var item in lstData)
{
if (IsView)
{
item.sLink = "<a class='btn btn-primary' href='admin_operation_type_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nID.ToString())) + "'><i class='fa fa-search'></i> View</a>";
}
else
{
item.sLink = "<a class='btn btn-warning' href='admin_operation_type_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
}
//item.sUpdate = item.dUpdate.DateString();
}
result.lstData = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}