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