public IActionResult GetProductionReportList([FromBody] ProductionReportViewModel model) { if (model.ContractNumber == null || model.ContractNumber == "") { model.ContractNumber = "-1"; } if (model.ProductionVendor == null) { model.ProductionVendor = -1; } if (model.CampaignName == null || model.CampaignName == "") { model.CampaignName = ""; } if (model.DMA == null || model.DMA == 211) { model.DMA = -1; } model.SellerID = CurrentUserDetails.SellerID; var productionReportList = ReportService.GetProductionReport(model); return(Ok(productionReportList)); }
public List <ProductionReportViewModel> GetProductionReport(ProductionReportViewModel model) { string spName = "stp_GetProductionRequirementsReportEquinox"; var Params = new DynamicParameters(); Params.Add("PeriodStart", model.FromDateRunReport); Params.Add("PeriodEnd", model.ToDateRunReport); Params.Add("Advertiser", model.ProductionVendor); Params.Add("SellerID", model.SellerID); Params.Add("ContractNumber", model.ContractNumber); Params.Add("CampaignName", model.CampaignName); Params.Add("DMA", model.DMA); var data = _dapperRepository.ExecuteStoredProc <ProductionReportViewModel>(spName, Params).ToList(); return(data); }
public IActionResult ProductionRequirementExcel([FromBody] ProductionReportViewModel model) { if (model.ContractNumber == null || model.ContractNumber == "") { model.ContractNumber = "-1"; } if (model.ProductionVendor == null) { model.ProductionVendor = -1; } if (model.CampaignName == null || model.CampaignName == "") { model.CampaignName = ""; } if (model.DMA == null || model.DMA == 211) { model.DMA = -1; } model.SellerID = CurrentUserDetails.SellerID; var uploadPath = string.Format("uploads\\" + CurrentUserDetails.SellerID + "\\Excel\\"); var targetDirectory = Path.Combine(_iconfiguration["BManageFolder"], uploadPath); if (!Directory.Exists(targetDirectory)) { Directory.CreateDirectory(targetDirectory); } var filename = "ProductionRequirement" + DateTime.Now.ToShortDateString().Replace('/', '_') + ".xlsx"; var savePath = Path.Combine(targetDirectory, filename); var loadURL = _iconfiguration["BManageUrl"] + uploadPath + filename; DataTable prodRequireList = ReportService.ProductionRequirementExport(model); DataSet dsExportReport = new DataSet(); dsExportReport.Tables.Add(prodRequireList); ExcelHelper.ListToExcelInMultipleSheets(dsExportReport, savePath); return(Ok(loadURL)); }
public DataTable ProductionRequirementExport(ProductionReportViewModel model) { if (model.Program == null) { model.Program = getProgramFromContractNumber(model.ContractNumber, model.SellerID); } var strSQL = new StringBuilder(); strSQL.AppendFormat(@" CREATE TABLE #foo (Market NVARCHAR(max), StartDate NVARCHAR(MAX), EndDate NVARCHAR(MAX), MeidaType NVARCHAR(max),UnitID NVARCHAR(max),MediaQuantity NVARCHAR(max),Creative NVARCHAR(max),Company NVARCHAR (max), Contact NVARCHAR(max),artfilesrecevied NVARCHAR(max),proofsent NVARCHAR(max),proofapproved NVARCHAR(max),PLssent NVARCHAR(max),materialconfirmation NVARCHAR(max),POPsUpload NVARCHAR(max)) INSERT INTO #foo exec [stp_GetProductionRequirementsReportEquinox] @StartDate, @ToDate,@Advertiser,@SellerID,@ContractNumber,@CampaignName,@DMA SELECT 'Production Requirements','','','','','','','','','','','','','','' UNION ALL SELECT 'Program: ',@Program,'','','','','','','','','','','','','' UNION ALL SELECT '','','','','','','','','','','','','','','' UNION ALL SELECT 'Market','Start Date','End Date','Media Type','Unit ID','Media Quantity','CREATIVE','Company','Contact','artsfilesrecevied','proofsent','proofsapproved','PIsent','materialconfirmation','POPS Uploaded' UNION ALL SELECT Market,CAST(StartDate AS VARCHAR(max)),EndDate,MeidaType,UnitID,CAST(MediaQuantity AS VARCHAR(max)),Creative,Company,Contact,artfilesrecevied,proofsent,proofapproved,PLssent,materialconfirmation,POPsUpload FROM #foo DROP TABLE #foo "); var parameterList = new[] { new SqlParameter("@StartDate", SqlDbType.NVarChar), new SqlParameter("@ToDate", SqlDbType.NVarChar), new SqlParameter("@Advertiser", SqlDbType.Int), new SqlParameter("@SellerID", SqlDbType.NVarChar), new SqlParameter("@ContractNumber", SqlDbType.NVarChar), new SqlParameter("@CampaignName", SqlDbType.NVarChar), new SqlParameter("@DMA", SqlDbType.Int), new SqlParameter("@Program", SqlDbType.NVarChar), }; parameterList[0].Value = GetNullDate(model.FromDateRunReport); parameterList[1].Value = GetNullDate(model.ToDateRunReport); parameterList[2].Value = model.ProductionVendor; parameterList[3].Value = model.SellerID; parameterList[4].Value = model.ContractNumber; parameterList[5].Value = model.CampaignName; parameterList[6].Value = model.DMA; parameterList[7].Value = model.Program == null ? "" : model.Program; DataTable productionRequirementExportList = _dapperRepository.ExecuteDataTableQueryWithParameter(strSQL.ToString(), parameterList); return(productionRequirementExportList); }