public IActionResult GetShippingOrderList([FromBody] ShippingOrderViewModel model)
{
if (model.ContractNumber == null || model.ContractNumber == "")
{
model.ContractNumber = "-1";
}
if (model.ProductionVendor == null)
{
model.ProductionVendor = -1;
}
if (model.VendorRep == null)
{
model.VendorRep = -1;
}
if (model.Campaign == null || model.Campaign == "")
{
model.Campaign = "";
}
model.SellerID = CurrentUserDetails.SellerID;
var shippingOrderList = ReportService.GetShippingOrder(model);
return(Ok(shippingOrderList));
}
public DataTable shippingRequirementExport(ShippingOrderViewModel model)
{
var strSQL = new StringBuilder();
strSQL.AppendFormat(@"
CREATE TABLE #foo (ContractNumber NVARCHAR(max), Advertiser NVARCHAR(MAX), Market NVARCHAR(MAX), PostingDate NVARCHAR(max),MediaType NVARCHAR(max),Size NVARCHAR
(max), NumberOfUnitsToShip NVARCHAR(max),Creative NVARCHAR(max),Vendor NVARCHAR(max),ShippingAddress NVARCHAR(max),City NVARCHAR(max),State NVARCHAR(max),Zip NVARCHAR(max),Contact NVARCHAR(max),Phone NVARCHAR(max),ShippingType NVARCHAR(max))
INSERT INTO #foo
exec [stp_GetShippingOrdersReportEquinox] @StartDate , @ToDate ,@Advertiser,@AdvertiserContactID,@SellerID,@ContractNumber,@OrderType, @CampaignName
SELECT '','','','',@OrderType + 'Order','','','','','','','','','','',''
UNION ALL
SELECT '','','','','Attention: ','','','','Shipping Instruction:','','','','','Emailed: ','',''
UNION ALL
SELECT 'Contract#','Advertiser','Market','Posting Date','Media Type','Size','#Of Units To Ship','Creative','Vendor','ShippingAddress','City','State','Zip','Contact','Phone','Shipping Type'
UNION ALL
SELECT ContractNumber,Advertiser,Market,PostingDate,MediaType,Size,NumberOfUnitsToShip,Creative,Vendor,ShippingAddress,City,State,Zip,Contact,Phone,ShippingType FROM #foo
DROP TABLE #foo ");
var parameterList = new[]
{
new SqlParameter("@StartDate", SqlDbType.DateTime),
new SqlParameter("@ToDate", SqlDbType.DateTime),
new SqlParameter("@Advertiser", SqlDbType.Int),
new SqlParameter("@AdvertiserContactID", SqlDbType.Int),
new SqlParameter("@SellerID", SqlDbType.Int),
new SqlParameter("@ContractNumber", SqlDbType.NVarChar),
new SqlParameter("@OrderType", SqlDbType.NVarChar),
new SqlParameter("@CampaignName", SqlDbType.NVarChar)
};
parameterList[0].Value = GetNullDate(model.FromDateRunReport);
parameterList[1].Value = GetNullDate(model.ToDateRunReport);
parameterList[2].Value = model.ProductionVendor;
parameterList[3].Value = model.VendorRep;
parameterList[4].Value = model.SellerID;
parameterList[5].Value = model.ContractNumber;
parameterList[6].Value = model.OrderType;
parameterList[7].Value = model.Campaign;
DataTable shippingOrderExportList = _dapperRepository.ExecuteDataTableQueryWithParameter(strSQL.ToString(), parameterList);
return(shippingOrderExportList);
}
public List <ShippingOrderViewModel> GetShippingOrder(ShippingOrderViewModel model)
{
string spName = "stp_GetShippingOrdersReportEquinox";
var Params = new DynamicParameters();
Params.Add("PeriodStart", model.FromDateRunReport);
Params.Add("PeriodEnd", model.ToDateRunReport);
Params.Add("Advertiser", model.ProductionVendor);
Params.Add("AdvertiserContactID", model.VendorRep);
Params.Add("SellerID", model.SellerID);
Params.Add("ContractNumber", model.ContractNumber);
Params.Add("OrderType", model.OrderType);
Params.Add("CampaignName", model.Campaign);
var data = _dapperRepository.ExecuteStoredProc <ShippingOrderViewModel>(spName, Params).ToList();
return(data);
}
public IActionResult shippingOrdersExcel([FromBody] ShippingOrderViewModel model)
{
if (model.ContractNumber == null || model.ContractNumber == "")
{
model.ContractNumber = "-1";
}
if (model.ProductionVendor == null)
{
model.ProductionVendor = -1;
}
if (model.VendorRep == null)
{
model.VendorRep = -1;
}
if (model.Campaign == null || model.Campaign == "")
{
model.Campaign = "";
}
model.SellerID = CurrentUserDetails.SellerID;
DataTable shipOrderList = ReportService.shippingRequirementExport(model);
var uploadPath = string.Format("uploads\\" + CurrentUserDetails.SellerID + "\\Excel\\");
var targetDirectory = Path.Combine(_iconfiguration["BManageFolder"], uploadPath);
if (!Directory.Exists(targetDirectory))
{
Directory.CreateDirectory(targetDirectory);
}
var filename = "ShippingOrderExport" + DateTime.Now.ToShortDateString().Replace('/', '_') + ".xlsx";
var savePath = Path.Combine(targetDirectory, filename);
var loadURL = _iconfiguration["BManageUrl"] + uploadPath + filename;
DataSet dsExportReport = new DataSet();
dsExportReport.Tables.Add(shipOrderList);
ExcelHelper.ListToExcelInMultipleSheets(dsExportReport, savePath);
return(Ok(loadURL));
}
