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);
        }