public void InitReportResult(ref IncentiveCollectionReportViewModel result, string p, int?plant = null)
{
result = new IncentiveCollectionReportViewModel()
{
//FormattedMonthYear = DateTime.UtcNow.ToUtcID().ToString("MM-yyyy"),
FormattedMonthYear = "",
Plant = plant
};
if (!string.IsNullOrEmpty(p))
{
result.FormattedMonthYear = p;
}
try
{
string[] arr = result.FormattedMonthYear.Split('-');
result.Month = Convert.ToInt16(arr[0]);
result.Year = Convert.ToInt16(arr[1]);
}
catch (Exception ex)
{
}
}
public AlertMessage ExportReport(string p, int?plant)
{
AlertMessage alert = new AlertMessage();
if (!IsAccessible(ModuleCode.IncentiveCollection))
{
alert.Text = StaticMessage.ERR_ACCESS_DENIED;
return(alert);
}
IncentiveCollectionReportViewModel model = GetReport(p, plant);
if (model == null)
{
alert.Text = StaticMessage.ERR_DATA_NOT_FOUND;
return(alert);
}
string dateFormat = "yyyy/MM/dd";
IWorkbook workbook = new XSSFWorkbook();
Dictionary <string, ICellStyle> dcCellStyle = GetDcCellStyle(workbook);
CreateSheetRaw(ref workbook, dcCellStyle, model.ListRawSLM, "SLM Raw");
if (model.IsEnableSummarySLM)
{
CreateSheetReportSummary(ref workbook, dcCellStyle, model.ListSummarySLM, "Incentive SLM");
}
CreateSheetRaw(ref workbook, dcCellStyle, model.ListRawFSS, "FSS Raw");
if (model.IsEnableSummaryFSS)
{
CreateSheetReportSummary(ref workbook, dcCellStyle, model.ListSummaryFSS, "Incentive FSS");
}
if (model.IsQuarter)
{
CreateSheetRaw(ref workbook, dcCellStyle, model.ListRawASM, "ASM Raw");
if (model.IsEnableSummaryASM)
{
CreateSheetReportSummary(ref workbook, dcCellStyle, model.ListSummaryASM, "Incentive ASM");
}
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
alert.Data = ms.ToArray();
}
alert.Status = 1;
return(alert);
}
public IncentiveCollectionReportViewModel GetReportFakturis(string p, string b)
{
IncentiveCollectionReportViewModel result = null;
InitReportResult(ref result, p, string.IsNullOrEmpty(b) ? 0 : int.Parse(b));
if (result.Year == 0 || result.Month == 0)
{
return(result);
}
string connString = GetConnectionString();
List <SqlParameter> listSqlParam = new List <SqlParameter>()
{
new SqlParameter("@inMonth", result.Month),
new SqlParameter("@inYear", result.Year),
};
if (RoleCode.KaCab.Equals(_userAuth.RoleCode))
{
listSqlParam.Add(new SqlParameter("@plant", _userAuth.Plant));
}
if (RoleCode.AdminOperation.Equals(_userAuth.RoleCode))
{
if (result.Plant != 0)
{
listSqlParam.Add(new SqlParameter("@plant", result.Plant));
}
}
#region report Collector
string spName = "sp_apl_getCollectionFAK_v2";
if (string.IsNullOrEmpty(spName))
{
return(result);
}
DataTableCollection dtCollection = SqlHelper.ExecuteProcedureWithReturnMultipleTable(connString, spName, listSqlParam);
if (dtCollection != null)
{
result.ListRawData = GetCollectionReportRaw(dtCollection[0]);
if (dtCollection.Count > 1)
{
result.ListSummaryData = GetReportListSummary(dtCollection[1]);
}
}
#endregion
return(result);
}
public ActionResult Index(string p, string b)
{
IncentiveCollectionBusiness business = new IncentiveCollectionBusiness();
business.SetUserAuth(ViewBag.UserAuth);
IncentiveCollectionReportViewModel model = business.GetReportSPVFakturis(p, b);
ViewBag.ListBranch = business.GetListBranch();
return(View(model));
}
public ActionResult Index()
{
IncentiveCollectionBusiness business = new IncentiveCollectionBusiness();
business.SetUserAuth(ViewBag.UserAuth);
IncentiveCollectionReportViewModel model = new IncentiveCollectionReportViewModel();
ViewBag.ListBranch = business.GetListBranch();
return(View(model));
}
public ActionResult Index(string p, int?b)
{
IncentiveCollectionBusiness business = new IncentiveCollectionBusiness();
business.SetUserAuth(ViewBag.UserAuth);
if (RoleCode.NSM.Equals(ViewBag.UserAuth.RoleCode))
{
ViewBag.ListBranch = business.GetListBranch();
}
IncentiveCollectionReportViewModel model = business.GetReport(p, b);
return(View(model));
}
public AlertMessage ExportReportSPVFakturis(string p, string b)
{
AlertMessage alert = new AlertMessage();
if (!IsAccessible(ModuleCode.IncentiveCollectionSPVFakturis))
{
alert.Text = StaticMessage.ERR_ACCESS_DENIED;
return(alert);
}
IncentiveCollectionReportViewModel model = GetReportSPVFakturis(p, b);
if (model == null)
{
alert.Text = StaticMessage.ERR_DATA_NOT_FOUND;
return(alert);
}
IWorkbook workbook = new XSSFWorkbook();
Dictionary <string, ICellStyle> dcCellStyle = GetDcCellStyle(workbook);
CreateSheetRaw(ref workbook, dcCellStyle, model.ListRawData, "SPV Fakturis Raw");
CreateSheetReportSummary(ref workbook, dcCellStyle, model.ListSummaryData, "SPV Fakturis");
using (var ms = new MemoryStream())
{
workbook.Write(ms);
alert.Data = ms.ToArray();
}
alert.Status = 1;
return(alert);
}
public IncentiveCollectionReportViewModel GetReport(string p, int?plant)
{
IncentiveCollectionReportViewModel result = null;
InitReportResult(ref result, p, plant);
if (result.Year == 0 || result.Month == 0)
{
return(result);
}
if (result.Month % 3 == 0)
{
result.IsQuarter = true;
}
if (RoleCode.NSM.Equals(_userAuth.RoleCode) && plant == null)
{
//return result;
}
string connString = GetConnectionString();
List <SqlParameter> listSqlParam = new List <SqlParameter>()
{
new SqlParameter("@inMonth", result.Month),
new SqlParameter("@inYear", result.Year),
};
string code = GetSalesGroupRayonCode();
string strListNSM = "";
if (RoleCode.RM.Equals(_userAuth.RoleCode))
{
strListNSM = GetStrListNSM(result.Year, result.Month, "BUM", "@nik", _userAuth.NIK.ToString());
if (string.IsNullOrEmpty(strListNSM))
{
return(result);
}
listSqlParam.Add(new SqlParameter("@listNIK", strListNSM));
}
else if (RoleCode.NSM.Equals(_userAuth.RoleCode))
{
strListNSM = _userAuth.NIK.ToString();
if (string.IsNullOrEmpty(strListNSM))
{
return(result);
}
if (code == "MDD" || code == "SD1" || code == "SD2")
{
listSqlParam.Add(new SqlParameter("@nik", strListNSM));
}
}
else if (RoleCode.KaCab.Equals(_userAuth.RoleCode))
{
strListNSM = _userAuth.NIK.ToString();
listSqlParam.Add(new SqlParameter("@listNIK", strListNSM));
}
#region report SLM
string spSLM = null;
#region GetSPAdminExclusiveSLM
if (RoleCode.AdminExclusive.Equals(_userAuth.RoleCode))
{
spSLM = "sp_apl_getCollectionSLMRayonType";
listSqlParam.Add(new SqlParameter("@nik", _userAuth.NIK));
code = GetRayonTypeByNIK(_userAuth.NIK);
}
#endregion
else
{
spSLM = GetProcedureNameSLM(code);
}
if (string.IsNullOrEmpty(spSLM))
{
return(result);
}
if (SalesGroupRayonCode.DcEnableSummarySLM.ContainsKey(code))
{
result.IsEnableSummarySLM = SalesGroupRayonCode.DcEnableSummarySLM[code];
}
DataTableCollection dtCollectionSLM = SqlHelper.ExecuteProcedureWithReturnMultipleTable(connString, spSLM, listSqlParam);
if (dtCollectionSLM != null)
{
result.ListRawSLM = GetCollectionReportRaw(dtCollectionSLM[0]);
if (result.IsEnableSummarySLM)
{
result.ListSummarySLM = GetReportListSummary(dtCollectionSLM[1]);
}
if (RoleCode.AdminExclusive.Equals(_userAuth.RoleCode))
{
result.ListSummarySLM = GetReportListSummary(dtCollectionSLM[1]);
result.IsEnableSummarySLM = true;
}
}
#endregion
#region report FSS
string spFSS = null;
#region GetSPAdminExclusiveFSS
if (RoleCode.AdminExclusive.Equals(_userAuth.RoleCode))
{
spFSS = "sp_apl_getCollectionFSSRayonType";
//listSqlParam.Add(new SqlParameter("@nik", _userAuth.NIK));
code = GetRayonTypeByNIK(_userAuth.NIK);
}
#endregion
else
{
spFSS = GetProcedureNameFSS(code);
}
if (SalesGroupRayonCode.DcEnableSummaryFSS.ContainsKey(code))
{
result.IsEnableSummaryFSS = SalesGroupRayonCode.DcEnableSummaryFSS[code];
}
DataTableCollection dtCollectionFSS = SqlHelper.ExecuteProcedureWithReturnMultipleTable(connString, spFSS, listSqlParam);
if (dtCollectionFSS != null)
{
result.ListRawFSS = GetCollectionReportRaw(dtCollectionFSS[0]);
if (result.IsEnableSummaryFSS)
{
result.ListSummaryFSS = GetReportListSummary(dtCollectionFSS[1]);
}
if (RoleCode.AdminExclusive.Equals(_userAuth.RoleCode))
{
result.ListSummaryFSS = GetReportListSummary(dtCollectionFSS[1]);
result.IsEnableSummaryFSS = true;
}
}
#endregion
#region report ASM
if (result.IsQuarter)
{
string spASM = GetProcedureNameASM(code);
#region GetSPAdminExclusiveASM
if (RoleCode.AdminExclusive.Equals(_userAuth.RoleCode))
{
spASM = "sp_apl_getCollectionFSSRayonType";
listSqlParam.Add(new SqlParameter("@nik", _userAuth.NIK));
code = GetRayonTypeByNIK(_userAuth.NIK);
}
#endregion
if (SalesGroupRayonCode.DcEnableSummaryASM.ContainsKey(code))
{
result.IsEnableSummaryASM = SalesGroupRayonCode.DcEnableSummaryASM[code];
}
listSqlParam = new List <SqlParameter>()
{
new SqlParameter("@inMonth", result.Month),
new SqlParameter("@inYear", result.Year),
};
if (spASM == "sp_apl_getCollectionASM_All_v2")
{
listSqlParam.Add(new SqlParameter("@listNIK", strListNSM));
}
else
{
listSqlParam.Add(new SqlParameter("@nik", _userAuth.NIK));
}
DataTableCollection dtCollectionASM = SqlHelper.ExecuteProcedureWithReturnMultipleTable(connString, spASM, listSqlParam);
if (dtCollectionASM != null)
{
result.ListRawASM = GetCollectionReportRaw(dtCollectionASM[0]);
if (result.IsEnableSummaryASM)
{
result.ListSummaryASM = GetReportListSummary(dtCollectionASM[1]);
}
}
}
#endregion
return(result);
}