public DataTable postBrandVendorData(ProductsVendorWisePurchaseReport objProductsVendorWisePurchaseReport) { SqlCommand command = new SqlCommand(); SqlConnection conn = new SqlConnection(strConn); conn.Open(); command.Connection = conn; command.CommandType = CommandType.StoredProcedure; command.CommandText = "BrandVendor_Wise_Purchase_Report"; command.Parameters.AddWithValue("@SellerId", objProductsVendorWisePurchaseReport.sellerId); command.Parameters.AddWithValue("@SubCategoryId", objProductsVendorWisePurchaseReport.subcategoryId); command.Parameters.AddWithValue("@BrandId", objProductsVendorWisePurchaseReport.brandId); command.Parameters.AddWithValue("@CategoryId", objProductsVendorWisePurchaseReport.categoryId); command.Parameters.AddWithValue("@OrderDate", objProductsVendorWisePurchaseReport.startDate); command.Parameters.AddWithValue("@DeliveryDate", objProductsVendorWisePurchaseReport.endDate); command.Parameters.AddWithValue("@VendorId", objProductsVendorWisePurchaseReport.vendorId); SqlDataAdapter adapter = new SqlDataAdapter(command); DataSet fileData = new DataSet(); adapter.Fill(fileData, "fileData"); conn.Close(); DataTable firstTable = fileData.Tables[0]; return(firstTable); }
public DataTable postBrandVendorOrderWiseData(ProductsVendorWisePurchaseReport objProductsVendorWisePurchaseReport) { SqlCommand command = new SqlCommand(); SqlConnection conn = new SqlConnection(strConn); conn.Open(); command.Connection = conn; command.CommandType = CommandType.StoredProcedure; command.CommandText = "BrandVendorOrderWisePurchaseReport"; command.Parameters.AddWithValue("@SellerId", objProductsVendorWisePurchaseReport.sellerId); command.Parameters.AddWithValue("@BrandId", objProductsVendorWisePurchaseReport.brandId); command.Parameters.AddWithValue("@OrderDate", objProductsVendorWisePurchaseReport.startDate); command.Parameters.AddWithValue("@DeliveryDate", objProductsVendorWisePurchaseReport.endDate); command.Parameters.AddWithValue("@VendorId", objProductsVendorWisePurchaseReport.vendorId); SqlDataAdapter adapter = new SqlDataAdapter(command); DataSet fileData = new DataSet(); adapter.Fill(fileData, "fileData"); conn.Close(); DataTable dt = fileData.Tables[0]; DataTable orderTable = fileData.Tables[1]; DataTable table = new DataTable(); table.Columns.Add("Brand", typeof(string)); table.Columns.Add("BrandID", typeof(string)); table.Columns.Add("Varient", typeof(string)); table.Columns.Add("BuyingPrice", typeof(string)); table.Columns.Add("Discount", typeof(int)); table.Columns.Add("ProductVarientId", typeof(string)); table.Columns.Add("ProductId", typeof(int)); table.Columns.Add("TotalQuantityOrder", typeof(int)); table.Columns.Add("TotalFinalPrice", typeof(int)); table.Columns.Add("TotalDiscountPrice", typeof(int)); table.Columns.Add("FinalPurchaseAmountAns", typeof(int)); table.Columns.Add("TotalBuyingPrice", typeof(int)); table.Columns.Add("OrderNo", typeof(int)); table.Columns.Add("OrderDate", typeof(string)); table.Columns.Add("TotalOrderAmtAns", typeof(int)); table.Columns.Add("varientCount", typeof(int)); for (int i = 0; i < dt.Rows.Count; i++) { string strBrandName = dt.Rows[i]["BrandName"].ToString(); string strBrandd = dt.Rows[i]["BrandIDD"].ToString(); string strVarientName = dt.Rows[i]["Varient"].ToString(); string strBuyingPrice = dt.Rows[i]["BuyingPrice"].ToString(); string strDiscount = dt.Rows[i]["Discount"].ToString(); string strProductVarientId = dt.Rows[i]["ProductVarientId"].ToString(); string strProductId = dt.Rows[i]["ProductId"].ToString(); string strOrderNo = dt.Rows[i]["OrderNo"].ToString(); int intBrandId = Convert.ToInt32(dt.Rows[i]["BrandIDD"].ToString()); int intOrderNo = Convert.ToInt32(dt.Rows[i]["OrderNo"].ToString()); string currentVarient = (dt.Rows[i]["Varient"].ToString()); int totalQuantityOrder = 0; int totalFinalPrice = 0; int totalDiscountPrice = 0; int finalPurchaseAmount = 0; int totalBuyingPrice = 0; int totalOrderAmt = 0; string strOrderDate = ""; int varientCnt = 0; for (int j = 0; j < orderTable.Rows.Count; j++) { if (intOrderNo == Convert.ToInt32(orderTable.Rows[j]["OrderNo"].ToString())) { strOrderNo = orderTable.Rows[j]["OrderNo"].ToString(); strOrderDate = orderTable.Rows[j]["OrderDate"].ToString(); totalOrderAmt += Convert.ToInt32(orderTable.Rows[j]["FinalPrice"].ToString()); if (intBrandId == Convert.ToInt32(orderTable.Rows[j]["BrandId"].ToString())) { varientCnt++; totalQuantityOrder += Convert.ToInt32(orderTable.Rows[j]["PurchaseQuantity"].ToString()); totalFinalPrice += Convert.ToInt32(orderTable.Rows[j]["FinalPrice"].ToString()); totalBuyingPrice = totalBuyingPrice + Convert.ToInt32(orderTable.Rows[j]["BuyingPrice"].ToString()) * Convert.ToInt32(orderTable.Rows[j]["PurchaseQuantity"].ToString()); totalDiscountPrice = totalDiscountPrice + Convert.ToInt32(orderTable.Rows[j]["Discount"].ToString()) * Convert.ToInt32(orderTable.Rows[j]["PurchaseQuantity"].ToString()); finalPurchaseAmount = totalBuyingPrice - totalDiscountPrice; } } } table.Rows.Add(strBrandName, strBrandd, strVarientName, strBuyingPrice, strDiscount, strProductVarientId, strProductId, totalQuantityOrder, totalFinalPrice, totalDiscountPrice, finalPurchaseAmount, totalBuyingPrice, strOrderNo, strOrderDate, totalOrderAmt, varientCnt); } return(table); }