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