public JDatatableResponse GetDatatable(JDatatableViewModel model)
{
JDatatableResponse result = new JDatatableResponse();
string[] arrOrderColumn = new string[] { "CreatedOn"
, "NIK"
, "Fullname"
, "Role"
, "ValidFrom"
, "ValidTo" };
if (_userAuth == null)
{
return(result);
}
DateTime today = DateTime.UtcNow.ToUtcID().Date;
IRepository <RHHeader> repo = _unitOfWork.GetRepository <RHHeader>();
repo.Includes = new string[] { "SLMObj1" };
repo.Condition = PredicateBuilder.True <RHHeader>().And(x => x.ValidTo >= today && x.SLM == _userAuth.NIK);
if (!string.IsNullOrEmpty(model.Keyword))
{
repo.Condition = repo.Condition.And(x => x.SLMObj1.FullName.Contains(model.Keyword));
}
SetDatatableRepository(model, arrOrderColumn, ref repo, ref result);
if (model.Length > -1 && result.TotalRecords == 0)
{
return(result);
}
List <RHHeader> listItem = repo.Find();
if (listItem == null)
{
return(result);
}
List <SalesmanViewModel> listData = new List <SalesmanViewModel>();
foreach (var item in listItem)
{
if (item.SLMObj1 == null)
{
continue;
}
listData.Add(GetSalesmanViewModel(item));
}
result.Data = listData;
return(result);
}
public ActionResult Datatable(JDatatableViewModel model)
{
if (!Request.IsAjaxRequest())
{
return(RedirectToAction("Index"));
}
JDatatableResponse resp = new JDatatableResponse();
CollectorBusiness business = new CollectorBusiness();
business.SetUserAuth(ViewBag.UserAuth);
resp = business.GetDatatableByQuery(model);
return(new MyJsonResult(resp, JsonRequestBehavior.AllowGet));
}
public ActionResult DatatableRayon(RayonDatatableViewModel model)
{
if (!Request.IsAjaxRequest())
{
return(RedirectToAction("Index"));
}
JDatatableResponse resp = new JDatatableResponse();
HierSalesBusiness business = new HierSalesBusiness();
business.SetUserAuth(ViewBag.UserAuth);
resp = business.GetDatatableCustomer(model);
return(new MyJsonResult(resp, JsonRequestBehavior.AllowGet));
}
public JDatatableResponse GetDatatableByQuery(JDatatableViewModel model)
{
JDatatableResponse result = new JDatatableResponse();
string[] arrOrderColumn = new string[] { "col.FULLNAME"
, "col.FULLNAME"
, "a.NIK"
, "col.Fullname"
, "col.Role"
, "col.ValidFrom"
, "col.ValidTo" };
if (_userAuth == null)
{
return(result);
}
string query = "SELECT [:SELECT]" + System.Environment.NewLine +
"FROM (" + System.Environment.NewLine +
" SELECT rth.Collector as NIK"+ System.Environment.NewLine +
" FROM RTHeader rth"+ System.Environment.NewLine +
" LEFT JOIN TCollector col ON col.NIK = rth.Collector"+ System.Environment.NewLine +
" WHERE 1=1"+ System.Environment.NewLine +
" AND rth.ValidTo >= GETDATE()" + System.Environment.NewLine +
" AND rth.Collector <> 0"+ System.Environment.NewLine +
" AND rth.Collector IS NOT NULL"+ System.Environment.NewLine +
" [:PRE_CONDITION]"+ System.Environment.NewLine +
//" UNION" + System.Environment.NewLine +
//" SELECT t.NIK" + System.Environment.NewLine +
//" FROM TCollector t" + System.Environment.NewLine +
//" WHERE 1=1" + System.Environment.NewLine +
//" AND CreatedBy = '"+_userAuth.NIK.ToString()+"'" + System.Environment.NewLine +
") a" + System.Environment.NewLine +
"LEFT JOIN TCollector col ON col.NIK = a.NIK" + System.Environment.NewLine +
"WHERE 1=1" + System.Environment.NewLine +
"[:CONDITION]";
string selectColumn = "DISTINCT a.NIK" + System.Environment.NewLine +
" ,col.FULLNAME" + System.Environment.NewLine +
" ,col.Role" + System.Environment.NewLine +
" ,col.ValidFrom" + System.Environment.NewLine +
" ,col.ValidTo";
string selectCount = "COUNT(DISTINCT a.NIK) AS NumRows";
if (_userAuth == null)
{
return(result);
}
string preCondition = "";
string condition = "";
Dictionary <string, object> dcParams = new Dictionary <string, object>();
if (RoleCode.AdminOperation.Equals(_userAuth.RoleCode))
{
}
else if (RoleCode.KaCab.Equals(_userAuth.RoleCode))
{
preCondition += " AND rth.Plant = @plant";
dcParams["@plant"] = _userAuth.Plant;
}
else
{
return(result);
}
if (!string.IsNullOrEmpty(model.Keyword))
{
model.Keyword = model.Keyword.ToLower();
condition += System.Environment.NewLine;
condition += " AND (a.NIK LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR LOWER(col.FULLNAME) LIKE '%[:KEYWORD]%')";
condition = condition.Replace("[:KEYWORD]", model.Keyword);
}
query = query.Replace("[:PRE_CONDITION]", preCondition);
query = query.Replace("[:CONDITION]", condition);
string query1 = query.Replace("[:SELECT]", selectCount);
string connString = GetConnectionString();
DataTable dataTable = SqlHelper.ExecuteQuery(connString, query1, dcParams);
if (dataTable != null && dataTable.Rows.Count > 0)
{
result.TotalRecords = Convert.ToInt64(dataTable.Rows[0]["NumRows"]);
result.TotalDisplayRecords = result.TotalRecords;
}
if (result.TotalRecords == 0)
{
return(result);
}
query1 = query = query.Replace("[:SELECT]", selectColumn);
if (model.IndexOrderCol <= arrOrderColumn.Length)
{
query1 += System.Environment.NewLine + string.Format("ORDER BY {0} {1}", arrOrderColumn[model.IndexOrderCol], model.OrderType.ToUpper());
}
if (model.Length > -1)
{
query1 += System.Environment.NewLine + "OFFSET " + model.Start + " ROWS" + System.Environment.NewLine +
"FETCH NEXT " + model.Length + " ROWS ONLY";
}
dataTable = SqlHelper.ExecuteQuery(connString, query1, dcParams);
List <CollectorViewModel> listData = new List <CollectorViewModel>();
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
listData.Add(GetCollectorViewModel(dr));
}
}
result.Data = listData;
return(result);
}
public JDatatableResponse GetDatatableByQuery(JDatatableViewModel model)
{
JDatatableResponse result = new JDatatableResponse();
string[] arrOrderColumn = new string[] { "asm.Fullname"
, "asm.Fullname"
, "asm.AllowedByNIK"
, "asm.UploadValidTo"
, "asm.NIK"
, "asm.Fullname"
, "asm.DefaultRayonType"
, "asm.Role"
, "asm.ValidFrom"
, "asm.ValidTo" };
if (_userAuth == null)
{
return(result);
}
string query = "SELECT [:SELECT]" + System.Environment.NewLine +
"FROM (" + System.Environment.NewLine +
" SELECT DISTINCT ASM"+ System.Environment.NewLine +
" FROM RTHeader"+ System.Environment.NewLine +
" WHERE 1=1"+ System.Environment.NewLine +
" AND ValidTo >= GETDATE()"+ System.Environment.NewLine +
" [:PRE_CONDITION]" + System.Environment.NewLine +
" UNION"+ System.Environment.NewLine +
" SELECT DISTINCT ASM"+ System.Environment.NewLine +
" FROM RHHeader"+ System.Environment.NewLine +
" WHERE 1=1"+ System.Environment.NewLine +
" AND ValidTo >= GETDATE()"+ System.Environment.NewLine +
" [:PRE_CONDITION]" + System.Environment.NewLine +
" UNION" + System.Environment.NewLine +
" SELECT DISTINCT NIK"+ System.Environment.NewLine +
" FROM ASM"+ System.Environment.NewLine +
" WHERE 1=1"+ System.Environment.NewLine +
" AND CreatedBy = '"+ _userAuth.NIK.ToString() + "'" + System.Environment.NewLine +
") t" + System.Environment.NewLine +
"LEFT JOIN ASM asm ON asm.NIK = t.ASM" + System.Environment.NewLine +
"WHERE 1=1" + System.Environment.NewLine +
" AND asm.NIK IS NOT NULL"+ System.Environment.NewLine +
" [:CONDITION]";
string selectColumn = "DISTINCT *";
string selectCount = "COUNT(DISTINCT ASM) AS NumRows";
if (_userAuth == null)
{
return(result);
}
string condition = "";
string preCondition = "";
Dictionary <string, object> dcParams = new Dictionary <string, object>();
if (RoleCode.NSM.Equals(_userAuth.RoleCode) || RoleCode.KaCab.Equals(_userAuth.RoleCode))
{
preCondition += "AND NSM = @nik";
dcParams["@nik"] = _userAuth.NIK;
}
else if (RoleCode.BUM.Equals(_userAuth.RoleCode))
{
//preCondition += " AND BUM = @nik";
//dcParams["@nik"] = _userAuth.NIK;
}
else
{
return(result);
}
if (!string.IsNullOrEmpty(model.Keyword))
{
model.Keyword = model.Keyword.ToLower();
condition += System.Environment.NewLine;
condition += "AND (asm.NIK LIKE '%[:KEYWORD]%'" + System.Environment.NewLine +
" OR LOWER(asm.FullName) LIKE '%[:KEYWORD]%'" + System.Environment.NewLine +
" OR LOWER(asm.DefaultRayonType) LIKE '%[:KEYWORD]%')";
condition = condition.Replace("[:KEYWORD]", model.Keyword);
}
query = query.Replace("[:PRE_CONDITION]", preCondition);
query = query.Replace("[:CONDITION]", condition);
string query1 = query.Replace("[:SELECT]", selectCount);
string connString = GetConnectionString();
DataTable dataTable = SqlHelper.ExecuteQuery(connString, query1, dcParams);
if (dataTable != null && dataTable.Rows.Count > 0)
{
result.TotalRecords = Convert.ToInt64(dataTable.Rows[0]["NumRows"]);
result.TotalDisplayRecords = result.TotalRecords;
}
if (result.TotalRecords == 0)
{
return(result);
}
query1 = query = query.Replace("[:SELECT]", selectColumn);
if (model.IndexOrderCol <= arrOrderColumn.Length)
{
query1 += System.Environment.NewLine + string.Format("ORDER BY {0} {1}", arrOrderColumn[model.IndexOrderCol], model.OrderType.ToUpper());
}
if (model.Length > -1)
{
query1 += System.Environment.NewLine + "OFFSET " + model.Start + " ROWS" + System.Environment.NewLine +
"FETCH NEXT " + model.Length + " ROWS ONLY";
}
dataTable = SqlHelper.ExecuteQuery(connString, query1, dcParams);
List <ASMViewModel> listData = new List <ASMViewModel>();
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
listData.Add(GetASMViewModel(dr));
}
}
result.Data = listData;
return(result);
}
public JDatatableResponse GetDatatableCustomer(RayonDatatableViewModel model)
{
JDatatableResponse result = new JDatatableResponse();
string[] arrOrderColumn = new string[] { "CreatedOn"
, "RayonCode"
, "CreatedOn"
, "CreatedOn"
, "Customer"
, "CustomerObj.CustomerName"
, "ValidFrom"
, "ValidTo" };
if (_userAuth == null)
{
return(result);
}
DateTime today = DateTime.UtcNow.ToUtcID().Date;
IRepository <RHDetail> repo = _unitOfWork.GetRepository <RHDetail>();
repo.Includes = new string[] { "CustomerObj" };
repo.Condition = PredicateBuilder.True <RHDetail>().And(x => x.RayonCode.Equals(model.RayonCode));
repo.Condition = repo.Condition.And(x => x.ValidTo >= today);
if (!string.IsNullOrEmpty(model.Keyword))
{
repo.Condition = repo.Condition.And(x => x.RayonCode.Contains(model.Keyword) ||
x.Customer.Contains(model.Keyword) ||
x.CustomerObj.CustomerName.Contains(model.Keyword));
}
SetDatatableRepository(model, arrOrderColumn, ref repo, ref result);
if (model.Length > -1 && result.TotalRecords == 0)
{
return(result);
}
List <RHDetail> listItem = repo.Find();
if (listItem == null)
{
return(result);
}
#region get salesman on a rayon
IRepository <RHHeader> repoRHH = _unitOfWork.GetRepository <RHHeader>();
repoRHH.Includes = new string[] { "SLMObj1" };
repoRHH.Condition = PredicateBuilder.True <RHHeader>().And(x => x.RayonCode.Equals(model.RayonCode) && x.ValidTo >= today);
repoRHH.OrderBy = new SqlOrderBy("CreatedOn", SqlOrderType.Descending);
RHHeader rhh = repoRHH.Find().FirstOrDefault();
#endregion
List <SalesCustomerViewModel> listData = new List <SalesCustomerViewModel>();
foreach (var item in listItem)
{
listData.Add(GetSalesRayonViewModel(item, rhh));
}
result.Data = listData;
return(result);
}
public JDatatableResponse GetDatatableByQuery(JDatatableViewModel model)
{
JDatatableResponse result = new JDatatableResponse();
string[] arrOrderColumn = new string[] { "rth.CreatedOn"
, "rth.RayonCode"
, "rth.Plant"
, "SLM"
, "slm.FullName"
, "FSS"
, "fss.FullName"
, "ASM"
, "asm.FullName"
, "rth.NSM"
, "nsm.FullName"
, "Collector"
, "col.FULLNAME" //,"CollectorObj.FULLNAME"
, "Fakturis"
, "ftr.FULLNAME" //,"FakturisObj.FULLNAME"
, "SPVFakturis"
, "spv.FULLNAME" //,"SPVFakturisObj.FULLNAME"
, "rth.ValidFrom"
, "rth.ValidTo" };
#region query
string query = "SELECT [:SELECT]" + System.Environment.NewLine +
"FROM RTHeader rth" + System.Environment.NewLine +
"LEFT JOIN NSM nsm ON nsm.NIK = rth.NSM" + System.Environment.NewLine +
" AND nsm.ValidTo >= GETDATE()"+ System.Environment.NewLine +
"LEFT JOIN ASM asm ON asm.NIK = rth.ASM" + System.Environment.NewLine +
" AND asm.ValidTo >= GETDATE()"+ System.Environment.NewLine +
"LEFT JOIN FSS fss ON fss.NIK = rth.FSS" + System.Environment.NewLine +
" AND fss.ValidTo >= GETDATE()"+ System.Environment.NewLine +
"LEFT JOIN SLM slm ON slm.NIK = rth.SLM" + System.Environment.NewLine +
" AND slm.ValidTo >= GETDATE()"+ System.Environment.NewLine +
"LEFT JOIN TCollector col ON col.NIK = rth.Collector" + System.Environment.NewLine +
" AND col.ValidTo >= GETDATE()"+ System.Environment.NewLine +
"LEFT JOIN TFakturis ftr ON ftr.NIK = rth.Fakturis" + System.Environment.NewLine +
" AND ftr.ValidTo >= GETDATE()"+ System.Environment.NewLine +
"LEFT JOIN TSPVFakturis spv ON spv.NIK = rth.SPVFakturis" + System.Environment.NewLine +
" AND spv.ValidTo >= GETDATE()"+ System.Environment.NewLine +
"WHERE 1=1" + System.Environment.NewLine +
" AND rth.ValidTo >= GETDATE()"+ System.Environment.NewLine +
"[:CONDITION]";
string selectColumn = "rth.RayonCode" + System.Environment.NewLine +
" ,rth.Plant" + System.Environment.NewLine +
" ,rth.NSM AS NSMNIK" + System.Environment.NewLine +
" ,nsm.FullName AS NSMFullName" + System.Environment.NewLine +
" ,rth.ASM AS ASMNIK" + System.Environment.NewLine +
" ,asm.FullName AS ASMFullName" + System.Environment.NewLine +
" ,rth.FSS AS FSSNIK" + System.Environment.NewLine +
" ,fss.FullName AS FSSFullName" + System.Environment.NewLine +
" ,rth.SLM AS SLMNIK" + System.Environment.NewLine +
" ,slm.FullName AS SLMFullName" + System.Environment.NewLine +
" ,rth.Collector AS CollectorNIK" + System.Environment.NewLine +
" ,col.FULLNAME AS CollectorFullName" + System.Environment.NewLine +
" ,rth.Fakturis AS FakturisNIK" + System.Environment.NewLine +
" ,ftr.FULLNAME AS FakturisFullName" + System.Environment.NewLine +
" ,rth.SPVFakturis AS SPVFakturisNIK" + System.Environment.NewLine +
" ,spv.FULLNAME AS SPVFakturisFullName" + System.Environment.NewLine +
" ,rth.ValidFrom" + System.Environment.NewLine +
" ,rth.ValidTo";
string selectCount = "COUNT(*) AS NumRows";
#endregion
if (_userAuth == null)
{
return(result);
}
string condition = "";
Dictionary <string, object> dcParams = new Dictionary <string, object>();
if (RoleCode.RM.Equals(_userAuth.RoleCode))
{
condition += " AND rth.NSM IN (" + System.Environment.NewLine +
" SELECT DISTINCT NSM"+ System.Environment.NewLine +
" FROM RHHeader"+ System.Environment.NewLine +
" WHERE 1=1"+ System.Environment.NewLine +
" AND ValidTo >= GETDATE()"+ System.Environment.NewLine +
" AND BUM = @nik"+ System.Environment.NewLine +
" )";
dcParams["@nik"] = _userAuth.NIK;
}
else if (RoleCode.NSM.Equals(_userAuth.RoleCode))
{
condition += " AND rth.NSM = @nik";
dcParams["@nik"] = _userAuth.NIK;
}
else if (RoleCode.ASM.Equals(_userAuth.RoleCode))
{
condition += " AND rth.ASM = @nik";
dcParams["@nik"] = _userAuth.NIK;
}
else if (RoleCode.FSS.Equals(_userAuth.RoleCode))
{
condition += " AND rth.FSS = @nik";
dcParams["@nik"] = _userAuth.NIK;
}
else if (RoleCode.SLM.Equals(_userAuth.RoleCode))
{
condition += " AND rth.FSS = @nik";
dcParams["@nik"] = _userAuth.NIK;
}
else if (RoleCode.KaCab.Equals(_userAuth.RoleCode) && _userAuth.Plant != null)
{
//condition += " AND (rth.Plant = @plant)";
//condition += " AND (rth.NSM = @nik /*OR rth.Plant = @plant*/)";
condition += " AND (rth.NSM = @nik)";
dcParams["@nik"] = _userAuth.NIK;
dcParams["@plant"] = _userAuth.Plant.Value;
}
else
{
return(result);
}
if (!string.IsNullOrEmpty(model.Keyword))
{
model.Keyword = model.Keyword.ToLower();
condition += System.Environment.NewLine;
condition += " AND (LOWER(rth.RayonCode) LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR rth.Plant LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR rth.NSM LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR LOWER(nsm.FullName) LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR rth.ASM LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR LOWER(asm.FullName) LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR rth.FSS LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR LOWER(fss.FullName) LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR rth.SLM LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR LOWER(slm.FullName) LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR rth.Collector LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR LOWER(col.FULLNAME) LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR rth.Fakturis LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR LOWER(ftr.FULLNAME) LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR rth.SPVFakturis LIKE '%[:KEYWORD]%'"+ System.Environment.NewLine +
" OR LOWER(spv.FULLNAME) LIKE '%[:KEYWORD]%')";
condition = condition.Replace("[:KEYWORD]", model.Keyword);
}
query = query.Replace("[:CONDITION]", condition);
string query1 = query.Replace("[:SELECT]", selectCount);
string connString = GetConnectionString();
DataTable dataTable = SqlHelper.ExecuteQuery(connString, query1, dcParams);
if (dataTable != null && dataTable.Rows.Count > 0)
{
result.TotalRecords = Convert.ToInt64(dataTable.Rows[0]["NumRows"]);
result.TotalDisplayRecords = result.TotalRecords;
}
if (result.TotalRecords == 0)
{
return(result);
}
query1 = query = query.Replace("[:SELECT]", selectColumn);
if (model.IndexOrderCol <= arrOrderColumn.Length)
{
query1 += System.Environment.NewLine + string.Format("ORDER BY {0} {1}", arrOrderColumn[model.IndexOrderCol], model.OrderType.ToUpper());
}
if (model.Length > -1)
{
query1 += System.Environment.NewLine + "OFFSET " + model.Start + " ROWS" + System.Environment.NewLine +
"FETCH NEXT " + model.Length + " ROWS ONLY";
}
dataTable = SqlHelper.ExecuteQuery(connString, query1, dcParams);
List <HierTagihViewModel> listData = new List <HierTagihViewModel>();
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
listData.Add(GetHierTagihViewModel(dr));
}
}
result.Data = listData;
return(result);
}
public JDatatableResponse GetDatatable(JDatatableViewModel model)
{
JDatatableResponse result = new JDatatableResponse();
string[] arrOrderColumn = new string[] { "CreatedOn"
, "RayonCode"
, "Plant"
, "NSM"
, "NSMObj.FullName"
, "ASM"
, "ASMObj.FullName"
, "FSS"
, "FSSObj.FullName"
, "SLM"
, "SLMObj.FullName"
, "Collector"
, "Collector" //,"CollectorObj.FULLNAME"
, "Fakturis"
, "Fakturis" //,"FakturisObj.FULLNAME"
, "SPVFakturis"
, "SPVFakturis" //,"SPVFakturisObj.FULLNAME"
, "ValidFrom"
, "ValidTo" };
if (_userAuth == null)
{
return(result);
}
IRepository <RTHeader> repo = _unitOfWork.GetRepository <RTHeader>();
repo.Includes = new string[] { "NSMObj", "ASMObj", "FSSObj", "SLMObj", /*"CollectorObj", "FakturisObj", "SPVFakturisObj"*/ };
repo.Condition = PredicateBuilder.True <RTHeader>();
if (RoleCode.NSM.Equals(_userAuth.RoleCode))
{
repo.Condition = repo.Condition.And(x => x.NSM == _userAuth.NIK);
}
else if (RoleCode.ASM.Equals(_userAuth.RoleCode))
{
repo.Condition = repo.Condition.And(x => x.ASM == _userAuth.NIK);
}
else if (RoleCode.FSS.Equals(_userAuth.RoleCode))
{
repo.Condition = repo.Condition.And(x => x.FSS == _userAuth.NIK);
}
else if (RoleCode.SLM.Equals(_userAuth.RoleCode))
{
repo.Condition = repo.Condition.And(x => x.SLM == _userAuth.NIK);
}
else if (RoleCode.KaCab.Equals(_userAuth.RoleCode))
{
repo.Condition = repo.Condition.And(x => x.Plant.Equals(_userAuth.Plant.Value.ToString()));
}
else
{
return(result);
}
DateTime today = DateTime.UtcNow.ToUtcID().Date;
repo.Condition = repo.Condition.And(x => x.ValidTo >= today);
if (!string.IsNullOrEmpty(model.Keyword))
{
repo.Condition = repo.Condition.And(x => x.RayonCode.Contains(model.Keyword) ||
x.Plant.Contains(model.Keyword) ||
x.NSM.ToString().Contains(model.Keyword) ||
x.NSMObj.FullName.Contains(model.Keyword) ||
x.ASM.ToString().Contains(model.Keyword) ||
x.ASMObj.FullName.Contains(model.Keyword) ||
x.FSS.ToString().Contains(model.Keyword) ||
x.FSSObj.FullName.Contains(model.Keyword) ||
x.SLM.ToString().Contains(model.Keyword) ||
x.SLMObj.FullName.Contains(model.Keyword) ||
x.Collector.ToString().Contains(model.Keyword)
//|| x.CollectorObj.FULLNAME.Contains(model.Keyword)
|| x.Fakturis.ToString().Contains(model.Keyword)
//|| x.FakturisObj.FULLNAME.Contains(model.Keyword)
|| x.SPVFakturis.ToString().Contains(model.Keyword));
//|| x.SPVFakturisObj.FULLNAME.Contains(model.Keyword));
}
SetDatatableRepository(model, arrOrderColumn, ref repo, ref result);
if (model.Length > -1 && result.TotalRecords == 0)
{
return(result);
}
List <RTHeader> listItem = repo.Find();
if (listItem == null)
{
return(result);
}
List <HierTagihViewModel> listData = new List <HierTagihViewModel>();
foreach (var item in listItem)
{
listData.Add(GetHierTagihViewModel(item));
}
result.Data = listData;
return(result);
}
public AlertMessage ExportTagihToExcel(JDatatableViewModel model)
{
AlertMessage alert = new AlertMessage();
model.Length = -1;
JDatatableResponse response = GetDatatableByQuery(model);
IWorkbook workbook = new XSSFWorkbook();
int rowIndex = 0;
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
string dateFormat = "yyyy/MM/dd";
ICreationHelper creationHelper = workbook.GetCreationHelper();
ICellStyle cellStyleDate = workbook.CreateCellStyle();
cellStyleDate.DataFormat = creationHelper.CreateDataFormat().GetFormat(dateFormat);
ICellStyle cellStyleText = workbook.CreateCellStyle();
cellStyleText.DataFormat = creationHelper.CreateDataFormat().GetFormat("text");
ICellStyle cellStyleNumber = workbook.CreateCellStyle();
cellStyleNumber.DataFormat = creationHelper.CreateDataFormat().GetFormat("0");
var fontBold = workbook.CreateFont();
fontBold.FontHeightInPoints = 11;
fontBold.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
ICellStyle cellStyleHeader = workbook.CreateCellStyle();
cellStyleHeader.SetFont(fontBold);
cellStyleHeader.BorderBottom = BorderStyle.Thin;
cellStyleHeader.FillForegroundColor = IndexedColors.Grey25Percent.Index;
cellStyleHeader.FillPattern = FillPattern.SolidForeground;
IRow row = sheet1.CreateRow(rowIndex);
row = sheet1.CreateRow(rowIndex);
int cellIndex = 0;
ICell cell = null;
#region cell header
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("RayonCode");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Plant");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("KaCab NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("KaCab Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("ASM NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("ASM Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Collector NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Collector Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("AR Collector NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("AR Collector Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SPV AR Collector NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SPV AR Collector Name");
cell.CellStyle = cellStyleHeader;
#endregion
rowIndex++;
#region cell data
List <HierTagihViewModel> listData = response.Data as List <HierTagihViewModel>;
if (listData != null)
{
foreach (var item in listData)
{
row = sheet1.CreateRow(rowIndex);
cellIndex = 0;
// RayonCode
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.RayonCode);
cell.CellStyle = cellStyleText;
// Plant
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.PlantCode);
cell.CellStyle = cellStyleNumber;
// NSM NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.NSMNik);
cell.CellStyle = cellStyleNumber;
// NSM Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.NSMFullname);
cell.CellStyle = cellStyleText;
// ASM NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.ASMNik);
cell.CellStyle = cellStyleNumber;
// ASM Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.ASMFullname);
cell.CellStyle = cellStyleText;
// FSS NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSSNik);
cell.CellStyle = cellStyleNumber;
// FSS Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSSFullname);
cell.CellStyle = cellStyleText;
// SLM NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLMNik);
cell.CellStyle = cellStyleNumber;
// SLM Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLMFullname);
cell.CellStyle = cellStyleText;
// Collector NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.CollectorNik ?? 0);
cell.CellStyle = cellStyleNumber;
// Collector Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.CollectorFullname);
cell.CellStyle = cellStyleText;
// AR Collector NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FakturisNik ?? 0);
cell.CellStyle = cellStyleNumber;
// AR Collector Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FakturisFullname);
cell.CellStyle = cellStyleText;
// SPV AR Collector NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SPVFakturisNik ?? 0);
cell.CellStyle = cellStyleNumber;
// SPV AR Collector Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SPVFakturisFullname);
cell.CellStyle = cellStyleText;
rowIndex++;
}
}
#endregion
#region auto size column
for (int i = 0; i < 16; i++)
{
sheet1.AutoSizeColumn(i);
}
#endregion
using (var ms = new MemoryStream())
{
workbook.Write(ms);
alert.Data = ms.ToArray();
}
alert.Status = 1;
return(alert);
}
public JDatatableResponse GetDatatableByQuery(JDatatableViewModel model)
{
JDatatableResponse result = new JDatatableResponse();
string[] arrOrderColumn = new string[] { "Fullname"
, "Fullname"
, "NIK"
, "Fullname"
, "Role"
, "ValidFrom"
, "ValidTo" };
if (_userAuth == null)
{
return(result);
}
string query = "SELECT [:SELECT]" + System.Environment.NewLine +
"FROM (" + System.Environment.NewLine +
" SELECT slm.NIK, slm.FullName, slm.Role, slm.ValidFrom, slm.ValidTo"+ System.Environment.NewLine +
" FROM RHHeader rhh"+ System.Environment.NewLine +
" LEFT JOIN SLM slm ON slm.NIK = rhh.SLM"+ System.Environment.NewLine +
" WHERE 1=1"+ System.Environment.NewLine +
" [:CONDITION]" + System.Environment.NewLine +
//" UNION" + System.Environment.NewLine +
//" SELECT NIK, FullName, Role, ValidFrom, ValidTo" + System.Environment.NewLine +
//" FROM SLM" + System.Environment.NewLine +
//" WHERE 1=1" + System.Environment.NewLine +
//" AND CreatedBy = '" +_userAuth.NIK+"'" + System.Environment.NewLine +
") a" + System.Environment.NewLine +
"WHERE 1=1" + System.Environment.NewLine +
" AND VALIDTO >= GETDATE()" + System.Environment.NewLine +
"[:CONDITION2]";
string selectColumn = "DISTINCT *";
string selectCount = "COUNT(DISTINCT NIK) AS NumRows";
if (_userAuth == null)
{
return(result);
}
string condition = "";
string condition2 = "";
Dictionary <string, object> dcParams = new Dictionary <string, object>();
if (RoleCode.BUM.Equals(_userAuth.RoleCode))
{
condition += " AND rhh.BUM = @nik";
dcParams["@nik"] = _userAuth.NIK;
}
else if (RoleCode.NSM.Equals(_userAuth.RoleCode) || RoleCode.KaCab.Equals(_userAuth.RoleCode))
{
condition += " AND rhh.NSM = @nik";
dcParams["@nik"] = _userAuth.NIK;
}
else if (RoleCode.ASM.Equals(_userAuth.RoleCode))
{
condition += " AND rhh.ASM = @nik";
dcParams["@nik"] = _userAuth.NIK;
}
else if (RoleCode.FSS.Equals(_userAuth.RoleCode))
{
condition += " AND rhh.FSS = @nik";
dcParams["@nik"] = _userAuth.NIK;
}
else
{
return(result);
}
if (!string.IsNullOrEmpty(model.Keyword))
{
model.Keyword = model.Keyword.ToLower();
condition2 += " AND (NIK LIKE '%[:KEYWORD]%'" + System.Environment.NewLine +
" OR LOWER(FullName) LIKE '%[:KEYWORD]%')";
condition2 = condition2.Replace("[:KEYWORD]", model.Keyword);
}
query = query.Replace("[:CONDITION]", condition);
query = query.Replace("[:CONDITION2]", condition2);
string query1 = query.Replace("[:SELECT]", selectCount);
string connString = GetConnectionString();
DataTable dataTable = SqlHelper.ExecuteQuery(connString, query1, dcParams);
if (dataTable != null && dataTable.Rows.Count > 0)
{
result.TotalRecords = Convert.ToInt64(dataTable.Rows[0]["NumRows"]);
result.TotalDisplayRecords = result.TotalRecords;
}
if (result.TotalRecords == 0)
{
return(result);
}
query1 = query = query.Replace("[:SELECT]", selectColumn);
if (model.IndexOrderCol <= arrOrderColumn.Length)
{
query1 += System.Environment.NewLine + string.Format("ORDER BY {0} {1}", arrOrderColumn[model.IndexOrderCol], model.OrderType.ToUpper());
}
if (model.Length > -1)
{
query1 += System.Environment.NewLine + "OFFSET " + model.Start + " ROWS" + System.Environment.NewLine +
"FETCH NEXT " + model.Length + " ROWS ONLY";
}
dataTable = SqlHelper.ExecuteQuery(connString, query1, dcParams);
List <SalesmanViewModel> listData = new List <SalesmanViewModel>();
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
listData.Add(GetSalesmanViewModel(dr));
}
}
result.Data = listData;
return(result);
}
protected void SetDatatableRepository <T>(JDatatableViewModel model, string[] arrOrderColumn, ref IRepository <T> repo, ref JDatatableResponse result)
{
long?totalRecords = null;
if (model.Length > -1) // get total records for pagging info only, model.length -1 = get all records
{
totalRecords = repo.Count();
if (totalRecords == null)
{
return;
}
result.TotalRecords = totalRecords.Value;
result.TotalDisplayRecords = totalRecords.Value;
}
SqlOrderBy orderBy = new SqlOrderBy()
{
Type = SqlOrderType.Descending,
Column = "CreatedDate"
};
if (model.IndexOrderCol <= arrOrderColumn.Length)
{
orderBy.Column = arrOrderColumn[model.IndexOrderCol];
orderBy.Type = model.OrderType.Equals(SqlOrderType.Ascending) ? SqlOrderType.Ascending : SqlOrderType.Descending;
}
repo.OrderBy = orderBy;
if (model.Length > -1)
{
repo.Limit = model.Length;
repo.Offset = model.Start;
}
}
public JDatatableResponse GetDatatable(TargetSalesDatatableViewModel model)
{
JDatatableResponse result = new JDatatableResponse();
string[] arrOrderColumn = new string[] { "RayonCode"
, "RayonCode"
, "SLM"
, "SLM"
, "FSS"
, "FSS"
, "AchiGroup"
, "Division"
, "Material"
, "Bulan"
, "Tahun"
, "Target" };
int month = 0;
int year = 0;
try
{
string[] arr = model.Periode.Split('-');
month = Convert.ToInt16(arr[0]);
year = Convert.ToInt16(arr[1]);
}
catch (Exception ex)
{
return(result);
}
if (_userAuth == null)
{
return(result);
}
List <RHHeader> rhHeader = GetRHHeaderByUserAuth();
var rCode = rhHeader.Select(r => r.RayonCode);
IRepository <SalesTarget> repo = _unitOfWork.GetRepository <SalesTarget>();
repo.Condition = PredicateBuilder.True <SalesTarget>();
repo.Condition = repo.Condition.And(x => x.Bulan == month && x.Tahun == year);
if (model.RayonCode.Equals("All"))
{
repo.Condition = repo.Condition.And(x => rCode.Contains(x.RayonCode));
}
else
{
repo.Condition = repo.Condition.And(x => x.RayonCode.Equals(model.RayonCode));
}
SetDatatableRepository(model, arrOrderColumn, ref repo, ref result);
if (model.Length > -1 && result.TotalRecords == 0)
{
return(result);
}
List <SalesTarget> listItem = repo.Find();
if (listItem == null)
{
return(result);
}
List <TargetSalesViewModel> listData = new List <TargetSalesViewModel>();
foreach (var item in listItem)
{
listData.Add(GetTargetSalesViewModel(item, rhHeader));
}
result.Data = listData;
return(result);
}
public AlertMessage ExportTargetSalesToExcel(TargetSalesDatatableViewModel model)
{
AlertMessage alert = new AlertMessage();
model.Length = -1;
JDatatableResponse response = GetDatatable(model);
IWorkbook workbook = new XSSFWorkbook();
int rowIndex = 0;
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
string dateFormat = "yyyy/MM/dd";
ICreationHelper creationHelper = workbook.GetCreationHelper();
ICellStyle cellStyleDate = workbook.CreateCellStyle();
cellStyleDate.DataFormat = creationHelper.CreateDataFormat().GetFormat(dateFormat);
ICellStyle cellStyleText = workbook.CreateCellStyle();
cellStyleText.DataFormat = creationHelper.CreateDataFormat().GetFormat("text");
ICellStyle cellStyleNumber = workbook.CreateCellStyle();
cellStyleNumber.DataFormat = creationHelper.CreateDataFormat().GetFormat("0");
var fontBold = workbook.CreateFont();
fontBold.FontHeightInPoints = 11;
fontBold.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
ICellStyle cellStyleHeader = workbook.CreateCellStyle();
cellStyleHeader.SetFont(fontBold);
cellStyleHeader.BorderBottom = BorderStyle.Thin;
cellStyleHeader.FillForegroundColor = IndexedColors.Grey25Percent.Index;
cellStyleHeader.FillPattern = FillPattern.SolidForeground;
IRow row = sheet1.CreateRow(rowIndex);
row = sheet1.CreateRow(rowIndex);
int cellIndex = 0;
ICell cell = null;
//
#region cell header
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS_Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM_Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("RayonCode");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("AchiGroup");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Division");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Material");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Bulan");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Tahun");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Target");
cell.CellStyle = cellStyleHeader;
#endregion
rowIndex++;
#region cell data
List <TargetSalesViewModel> listData = response.Data as List <TargetSalesViewModel>;
if (listData != null)
{
foreach (var item in listData)
{
row = sheet1.CreateRow(rowIndex);
cellIndex = 0;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSS_NIK);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSS_Name);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLM_NIK);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLM_Name);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.RayonCode);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.AchiGroup);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Division);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Material);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Bulan);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Tahun);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(double.Parse(item.Target));
cell.CellStyle = cellStyleNumber;
rowIndex++;
}
}
#endregion
#region auto size column
for (int i = 0; i < 13; i++)
{
sheet1.AutoSizeColumn(i);
}
#endregion
using (var ms = new MemoryStream())
{
workbook.Write(ms);
alert.Data = ms.ToArray();
}
alert.Status = 1;
return(alert);
}
