public async Task <JsonResult> Get_Orders([FromBody] Order_Filter obj)
{
#region where_clouse
string where_clouse = string.Empty;
if (!string.IsNullOrEmpty(obj.PaymentType) && obj.PaymentType != "all")
{
where_clouse += string.Format(" AND t1.PaymentType='{0}' ", obj.PaymentType);
}
if (!string.IsNullOrEmpty(obj.CartType) && obj.CartType != "all")
{
where_clouse += string.Format(" AND t1.CartType='{0}' ", obj.CartType);
}
if (!string.IsNullOrEmpty(obj.Product_Category) && obj.Product_Category != "ALL")
{
where_clouse += string.Format(" AND t4.Product_Type='{0}' ", obj.Product_Category);
}
if (!string.IsNullOrEmpty(obj.Product_SKU))
{
where_clouse += string.Format(" AND t4.SKU='{0}' ", obj.Product_SKU);
}
if (!string.IsNullOrEmpty(obj.OrderNumber))
{
where_clouse += string.Format(" AND t1.Id='{0}' ", obj.OrderNumber);
}
if (obj.Product_SubCategoryId.HasValue)
{
where_clouse += string.Format(" AND t4.Product_TypeId='{0}' ", obj.Product_SubCategoryId.Value);
}
if (obj.Platform_Id.HasValue)
{
where_clouse += string.Format(" AND t4.Product_TypeId='{0}' ", obj.Platform_Id.Value);
}
if (obj.BeginValue.HasValue)
{
where_clouse += string.Format(" AND t1.Total_Sum >= {0} ", obj.BeginValue.Value);
}
if (obj.EndValue.HasValue)
{
where_clouse += string.Format(" AND t1.Total_Sum <= {0} ", obj.EndValue.Value);
}
if (obj.StartDate.HasValue)
{
where_clouse += string.Format(" AND CAST(t1.OrderDate as DATE) >= CAST('{0}' as DATE) ", obj.StartDate.Value.ToString("MM/dd/yyyy"));
}
if (obj.EndDate.HasValue)
{
where_clouse += string.Format(" AND CAST(t1.OrderDate as DATE) <= CAST('{0}' as DATE) ", obj.EndDate.Value.ToString("MM/dd/yyyy"));
}
#endregion
#region Query Builder
string query = string.Empty;
query += " SELECT COUNT(t1.Id) as Total FROM [Shopping_Cart_Tracking_Tbl] t1 ";
query += " LEFT JOIN Shopping_Cart_Items_Tbl t4 on t1.Id=t4.Tracking_Id ";
query += " WHERE t1.[Status] != 'Ready' AND t1.IsDeleted=0 ";
query += where_clouse;
query += "; ";
query += " SELECT DISTINCT(t1.Id), t1.*, t2.Email, CONCAT(t2.FirstName, ' ', t2.LastName) as Name, ";
query += " CONCAT(t3.First_Name, ' ', t3.Last_Name, '<br/>', t3.Address_Line, ' ', t3.Area, '<br/>', t3.City, ' ', t3.[State], '<br/>', t3.Country, ' ', t3.ZipCode, '<br/>Mobile: ', t3.Mobile) as 'Address' ";
query += " FROM [Shopping_Cart_Tracking_Tbl] t1 ";
query += " LEFT JOIN UsersTbl t2 on t1.UserId=t2.Id ";
query += " LEFT JOIN AddressTbl t3 on t1.Shipping_Address_Id=t3.Id ";
query += " LEFT JOIN Shopping_Cart_Items_Tbl t4 on t1.Id=t4.Tracking_Id ";
query += " WHERE t1.[Status] != 'Ready' AND t1.IsDeleted=0 ";
query += where_clouse;
query += " ORDER by t1.OrderDate DESC ";
query += string.Format(" OFFSET {0} ROWS ", obj.offset);
query += string.Format(" FETCH NEXT {0} ROWS ONLY ", obj.rows);
query += "; ";
#endregion
var result = await _shop_repo.GetPaginated_By_Custom_QueryAsync(query);
return(Json(result));
}
public async Task <FileResult> Export_Order_Canceleation_List([FromForm] Order_Filter obj)
{
#region where_clouse
string where_clouse = string.Empty;
if (!string.IsNullOrEmpty(obj.PaymentType) && obj.PaymentType != "all")
{
where_clouse += string.Format(" AND t3.PaymentType='{0}' ", obj.PaymentType);
}
if (!string.IsNullOrEmpty(obj.Product_Category) && obj.Product_Category != "ALL")
{
where_clouse += string.Format(" AND t2.Product_Type='{0}' ", obj.Product_Category);
}
if (!string.IsNullOrEmpty(obj.Product_SKU))
{
where_clouse += string.Format(" AND t1.SKU='{0}' ", obj.Product_SKU);
}
if (!string.IsNullOrEmpty(obj.OrderNumber))
{
where_clouse += string.Format(" AND t1.Tracking_Id='{0}' ", obj.OrderNumber);
}
if (obj.Product_SubCategoryId.HasValue)
{
where_clouse += string.Format(" AND t2.Product_TypeId='{0}' ", obj.Product_SubCategoryId.Value);
}
if (obj.Platform_Id.HasValue)
{
where_clouse += string.Format(" AND t2.Product_TypeId='{0}' ", obj.Platform_Id.Value);
}
if (obj.BeginValue.HasValue)
{
where_clouse += string.Format(" AND t2.TotalPrice >= {0} ", obj.BeginValue.Value);
}
if (obj.EndValue.HasValue)
{
where_clouse += string.Format(" AND t2.TotalPrice <= {0} ", obj.EndValue.Value);
}
if (obj.StartDate.HasValue)
{
where_clouse += string.Format(" AND CAST(t1.Resuest_Date as DATE) >= CAST('{0}' as DATE) ", obj.StartDate.Value.ToString("MM/dd/yyyy"));
}
if (obj.EndDate.HasValue)
{
where_clouse += string.Format(" AND CAST(t1.Resuest_Date as DATE) <= CAST('{0}' as DATE) ", obj.EndDate.Value.ToString("MM/dd/yyyy"));
}
#endregion
#region Query Builder
string query = string.Empty;
query += " SELECT COUNT(t1.Id) as Total FROM Cancelation_Request_Tbl t1 ";
query += " LEFT JOIN Shopping_Cart_Items_Tbl t2 on t1.SKU=t2.SKU AND t1.Tracking_Id=t2.Tracking_Id ";
query += " LEFT JOIN Shopping_Cart_Tracking_Tbl t3 on t1.Tracking_Id=t3.Id ";
query += " LEFT JOIN UsersTbl t4 on t3.UserId=t4.Id ";
query += " WHERE t1.Id IS NOT NULL ";
query += where_clouse;
query += "; ";
query += " SELECT DISTINCT t1.Id, t1.SKU, t1.[Status], t1.Tracking_Id, t1.Resuest_Date, ";
query += " t2.Product_Id, t2.Product_Name, t2.Product_Type, t2.Product_TypeId, t2.Product_TypeName, ";
query += " t2.Quantity, t2.PageUrl, t2.ImageUrl, t2.TotalPrice, t3.PaymentType, ";
query += " CONCAT(t4.FirstName, ' ', t4.LastName) as Name, t4.Email ";
query += " FROM Cancelation_Request_Tbl t1 ";
query += " LEFT JOIN Shopping_Cart_Items_Tbl t2 on t1.SKU=t2.SKU AND t1.Tracking_Id=t2.Tracking_Id ";
query += " LEFT JOIN Shopping_Cart_Tracking_Tbl t3 on t1.Tracking_Id=t3.Id ";
query += " LEFT JOIN UsersTbl t4 on t3.UserId=t4.Id ";
query += " WHERE t1.Id IS NOT NULL ";
query += where_clouse;
query += " ORDER by t1.Resuest_Date DESC ";
//query += string.Format(" OFFSET {0} ROWS ", obj.offset);
//query += string.Format(" FETCH NEXT {0} ROWS ONLY ", obj.rows);
query += "; ";
#endregion
var result = await _order_repo.Get_Cancelation_Orders_By_Query(query);
var data = Utility.ExportExcel(result.PagedSet.ToList(), "CanceledOrder");
return(File(data, "text/xlsx", $"CanceledOrder.xlsx"));
}
public async Task <IActionResult> Get_CSV_Of_shoppingorder([FromForm] Order_Filter obj)
{
#region where_clouse
string where_clouse = string.Empty;
if (!string.IsNullOrEmpty(obj.PaymentType) && obj.PaymentType != "all")
{
where_clouse += string.Format(" AND t1.PaymentType='{0}' ", obj.PaymentType);
}
if (!string.IsNullOrEmpty(obj.CartType) && obj.CartType != "all")
{
where_clouse += string.Format(" AND t1.CartType='{0}' ", obj.CartType);
}
if (!string.IsNullOrEmpty(obj.Product_Category) && obj.Product_Category != "ALL")
{
where_clouse += string.Format(" AND t4.Product_Type='{0}' ", obj.Product_Category);
}
if (!string.IsNullOrEmpty(obj.Product_SKU))
{
where_clouse += string.Format(" AND t4.SKU='{0}' ", obj.Product_SKU);
}
if (!string.IsNullOrEmpty(obj.OrderNumber))
{
where_clouse += string.Format(" AND t1.Id='{0}' ", obj.OrderNumber);
}
if (obj.Product_SubCategoryId.HasValue)
{
where_clouse += string.Format(" AND t4.Product_TypeId='{0}' ", obj.Product_SubCategoryId.Value);
}
if (obj.Platform_Id.HasValue)
{
where_clouse += string.Format(" AND t4.Product_TypeId='{0}' ", obj.Platform_Id.Value);
}
if (obj.BeginValue.HasValue)
{
where_clouse += string.Format(" AND t1.Total_Sum >= {0} ", obj.BeginValue.Value);
}
if (obj.EndValue.HasValue)
{
where_clouse += string.Format(" AND t1.Total_Sum <= {0} ", obj.EndValue.Value);
}
if (obj.StartDate.HasValue)
{
where_clouse += string.Format(" AND CAST(t1.OrderDate as DATE) >= CAST('{0}' as DATE) ", obj.StartDate.Value.ToString("MM/dd/yyyy"));
}
if (obj.EndDate.HasValue)
{
where_clouse += string.Format(" AND CAST(t1.OrderDate as DATE) <= CAST('{0}' as DATE) ", obj.EndDate.Value.ToString("MM/dd/yyyy"));
}
#endregion
#region Query Builder
string query = string.Empty;
query += " SELECT DISTINCT t1.Id,CONCAT(t2.FirstName, ' ', t2.LastName) as Name,Total_Item,Total_Price,Tax_Rate,Status,Total_Sum,Tax_Amount,ShippingCharge,";
query += " CONCAT(t3.First_Name, ' ', t3.Last_Name, ', ', t3.Address_Line, ' ', t3.Area, ', ', t3.City, ' ', t3.[State], ', ', t3.Country, ' ', t3.ZipCode, ', Mobile: ', t3.Mobile) as 'Address' ";
query += " ,Payment_Status,Auth_Status,Auth_StatusTxt,Auth_Code,Auth_Message,Transaction_Id,Transaction_Date,case when OrderPlaced = 1 then 'Yes' else 'No' end as OrderPlaced,CONVERT(char(10), OrderDate, 126) as [OrderDate],PaymentType,CartType, ";
query += " case when IsDeleted = 1 then 'Yes' else 'No' end as IsDeleted,Total_Deduction,CreditUsed,PromoCode,Email ";
query += " FROM[Shopping_Cart_Tracking_Tbl] t1 ";
query += " LEFT JOIN UsersTbl t2 on t1.UserId = t2.Id ";
query += " LEFT JOIN AddressTbl t3 on t1.Shipping_Address_Id = t3.Id ";
query += " LEFT JOIN Shopping_Cart_Items_Tbl t4 on t1.Id = t4.Tracking_Id ";
query += " WHERE t1.[Status] != 'Ready' AND t1.IsDeleted=0 ";
query += where_clouse;
//query += " ORDER by t1.OrderDate DESC ";
#endregion
var result = await _shop_repo.GetShoppingorder_By_Custom_QueryAsync(query);
var data = Utility.ExportExcel(result.PagedSet.ToList(), "ShoppingOrder");
return(File(data, "text/xlsx", $"ShoppingOrder.xlsx"));
}
