// Retrieve manufactures list from database public List <ManufacturerProperties> getManufacturersList(string status) { ManufacturerProperties manufacturerProperties; List <ManufacturerProperties> manufacturersList = new List <ManufacturerProperties>(); DataSet resultDataset; try { string selectQueryString = "SELECT MANUFACTURER_ID, MANUFACTURER_NAME, MANUFACTURER_DETAIL, MANUFACTURER_STATUS FROM MANUFACTURER WHERE MANUFACTURER_STATUS=@STATUS"; List <KeyValuePair <string, string> > tableParamsValues = new List <KeyValuePair <string, string> >(); tableParamsValues.Add(new KeyValuePair <string, string>("@STATUS", status)); resultDataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, tableParamsValues); if (resultDataset != null) { foreach (DataRow resultrow in resultDataset.Tables[0].Rows) { manufacturerProperties = new ManufacturerProperties(); manufacturerProperties.Manufacturer_Id = resultrow["Manufacturer_Id"].ToString(); manufacturerProperties.Manufacturer_Name = resultrow["Manufacturer_Name"].ToString(); manufacturerProperties.Manufacturer_Detail = resultrow["Manufacturer_Detail"].ToString(); manufacturerProperties.Manufacturer_Status = resultrow["manufacturer_Status"].ToString(); manufacturersList.Add(manufacturerProperties); } } }catch (Exception exp) { Console.WriteLine(exp.StackTrace); } return(manufacturersList); }
public List <OrderReturnProperties> getMultipleReturnOrderInfo(string status) { DataSet dataset = new DataSet(); String selectQueryString = "SELECT RETURN_ORDER_ID,ORDER_ID,VENDOR_ID,USER_ID,PRODUCT_ID,PRODUCT_TYPE,COUNT,PRICE_PER_UNIT,RETURN_DESCRIPTION,RETURN_STATUS,CREATED_BY FROM RETURN_ORDER WHERE RETURN_STATUS=@RETURN_STATUS;"; List <OrderReturnProperties> orderReturnsList = new List <OrderReturnProperties>(); List <KeyValuePair <string, string> > queryParameter = new List <KeyValuePair <string, string> >(); queryParameter.Add(new KeyValuePair <string, string>("@RETURN_STATUS", status)); dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, queryParameter); if (dataset != null) { foreach (DataRow row in dataset.Tables[0].Rows) { OrderReturnProperties returnOrders = new OrderReturnProperties(); returnOrders.Return_Order_Id = row["Return_Order_ID"].ToString(); returnOrders.Order_Id = row["Order_Id"].ToString(); returnOrders.Vendor_Id = row["Vendor_Id"].ToString(); returnOrders.User_Id = row["User_Id"].ToString(); returnOrders.Product_Id = row["Product_Id"].ToString(); returnOrders.Product_Type = row["Product_Type"].ToString(); returnOrders.Count = Convert.ToInt16(row["Count"].ToString()); returnOrders.Price_Per_Unit = Convert.ToDouble(row["Price_Per_Unit"].ToString()); returnOrders.Return_Description = row["Return_Description"].ToString(); returnOrders.Return_Status = row["Return_status"].ToString(); returnOrders.Created_By = row["Created_By"].ToString(); orderReturnsList.Add(returnOrders); } } return(orderReturnsList); }
//Get single user profile details public UserInformationProperties getSingleUserInfo(string userId) { DataSet dataset = new DataSet(); UserInformationProperties userinformation = new UserInformationProperties(); string selectQueryString = "SELECT NAME,CONTACT,ADDRESS,EMAIL_ID,USER_ID,PASSWORD,ROLE,IS_VENDOR,VENDOR_ID,VENDOR_NAME,STATUS,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE FROM USER_TABLE WHERE USER_ID=@USER_ID"; List <KeyValuePair <string, string> > tableQueryData = new List <KeyValuePair <string, string> >(); tableQueryData.Add(new KeyValuePair <string, string>("@USER_ID", userId)); dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, tableQueryData); if (dataset != null) { foreach (DataRow row in dataset.Tables[0].Rows) { userinformation.Name = row["Name"].ToString(); userinformation.Contact = row["Contact"].ToString(); userinformation.Address = row["Address"].ToString(); userinformation.EMail_Id = row["Email_id"].ToString(); userinformation.User_Id = row["User_Id"].ToString(); userinformation.Password = row["Password"].ToString(); userinformation.Role = row["Role"].ToString(); userinformation.Is_Vendor = row["Is_Vendor"].ToString(); userinformation.Vendor_ID = row["Vendor_Id"].ToString(); userinformation.Vendor_Name = row["Vendor_Name"].ToString(); userinformation.Status = row["Status"].ToString(); userinformation.Created_By = row["Created_By"].ToString(); userinformation.Created_date = row["Created_date"].ToString(); userinformation.Modified_By = row["Modified_By"].ToString(); userinformation.Modified_Date = row["Modified_Date"].ToString(); } } return(userinformation); }
//retrieving single vendor profile information from vendor table public CustomerProperties getSingleCustomerInfo(string vendorId) { DataSet dataset = new DataSet(); CustomerProperties vendorInfo = new CustomerProperties(); string selectQueryString = "SELECT VENDOR_NAME,VENDOR_ID,VENDOR_ADDRESS,VENDOR_CONTACT,VENDOR_EMAIL_ID,VENDOR_STATUS,VENDOR_DISCOUNT_ID,VENDOR_BALANCE_AMOUNT FROM VENDOR WHERE VENDOR_ID=@VENDOR_ID"; List <KeyValuePair <string, string> > tableQueryData = new List <KeyValuePair <string, string> >(); tableQueryData.Add(new KeyValuePair <string, string>("@VENDOR_ID", vendorId)); dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, tableQueryData); if (dataset != null) { foreach (DataRow row in dataset.Tables[0].Rows) { vendorInfo.Vendor_Name = row["Vendor_Name"].ToString(); vendorInfo.Vendor_Id = row["Vendor_Id"].ToString(); vendorInfo.Vendor_Address = row["Vendor_Address"].ToString(); vendorInfo.Vendor_Contact = row["Vendor_Contact"].ToString(); vendorInfo.Vendor_EMail_Id = row["Vendor_Email_Id"].ToString(); vendorInfo.Vendor_Status = row["Vendor_Status"].ToString(); vendorInfo.Vendor_Discount_ID = row["Vendor_Discount_ID"].ToString(); vendorInfo.Vendor_Balance_Amount = Convert.ToDouble(row["Vendor_Balance_Amount"].ToString()); } } return(vendorInfo); }
//Retrieve all the discount information from discount table public List <DiscountProperties> getDiscountList() { System.Data.DataSet dataset = new System.Data.DataSet(); List <DiscountProperties> discountList = new List <DiscountProperties>(); string selectQueryString = "SELECT DISCOUNT_ID,DISCOUNT_RATE,CREATED_BY,CREATED_DATE,MODIFY_BY,MODIFY_DATE FROM DISOCUNT"; dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, null); if (dataset != null) { foreach (System.Data.DataRow row in dataset.Tables[0].Rows) { DiscountProperties discountData = new DiscountProperties(); discountData.Discount_Id = row["Discount_Id"].ToString(); discountData.Discount_Rate = row["Discount_rate"].ToString(); discountData.audit_Information.createdBy = row["Created_by"].ToString(); discountData.audit_Information.createdOn = row["Created_date"].ToString(); discountData.audit_Information.modifiedBy = row["Modify_by"].ToString(); discountData.audit_Information.modifiedOn = row["Modify_date"].ToString(); discountList.Add(discountData); } } return(discountList); }
//retrive Product details from database public Dictionary <string, List <ProductProperties> > getProducts() { DataSet resultDataset; Dictionary <string, List <ProductProperties> > productCollection = new Dictionary <string, List <ProductProperties> >(); try { List <ProductProperties> productList = new List <ProductProperties>(); string selectQueryString = "SELECT PRODUCT.PRODUCT_ID,PRODUCT_TYPE,PRODUCT.PRODUCT_NAME,PRODUCT.MINIMUM_COUNT,PRODUCT.PRODUCT_CURRENT_COUNT,PRODUCT.PRICE_PER_UNIT,MANUFACTURER.MANUFACTURER_ID, MANUFACTURER.MANUFACTURER_NAME FROM PRODUCT INNER JOIN MANUFACTURER ON PRODUCT.MANUFACTURER_ID = MANUFACTURER.MANUFACTURER_ID WHERE MANUFACTURER.MANUFACTURER_STATUS='ACTIVE' ORDER BY MANUFACTURER.MANUFACTURER_NAME;"; resultDataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, null); if (resultDataset != null) { foreach (DataRow resultrow in resultDataset.Tables[0].Rows) { ProductProperties productProperties = new ProductProperties(); productProperties.Product_Id = resultrow["Product_id"].ToString(); productProperties.Product_Type = resultrow["Product_type"].ToString(); productProperties.Product_Name = resultrow["Product_name"].ToString(); productProperties.Minimum_Count = int.Parse(resultrow["Minimum_count"].ToString()); productProperties.Product_Current_Count = int.Parse(resultrow["Product_Current_Count"].ToString()); productProperties.Price_Per_Unit = double.Parse(resultrow["Price_per_unit"].ToString()); productProperties.Manufacturer_Id = resultrow["Manufacturer_id"].ToString(); productProperties.Manufacturer_Name = resultrow["Manufacturer_Name"].ToString(); productList.Add(productProperties); _product_type.Add(productProperties.Product_Type); _product_list.Add(productProperties); } } else { productList = null; } //LINQ query to get the distict string value of manufacturers in List collection type List <string> manufacturer_id = (from manId in productList select manId.Manufacturer_Id).Distinct().ToList(); foreach (string manf_id in manufacturer_id) { List <ProductProperties> tempProductList = new List <ProductProperties>(); foreach (ProductProperties props in productList) { if (manf_id.Equals(props.Manufacturer_Id)) { tempProductList.Add(props); } } productCollection.Add(manf_id, tempProductList); } _inventory_list = productCollection; }catch (Exception exp) { Console.WriteLine(exp.StackTrace); } return(productCollection); }
public List <OrderProperties> getMultipleOrdersInfo(string vendorInfo, string orderStatus) { DataSet dataset = new DataSet(); string selectQueryString = null; if (orderStatus == null) { selectQueryString = "SELECT ORDER_ID,VENDOR_ID,MANUFACTURER_NAME,PRODUCT_ID,PRODUCT_TYPE,PRODUCT_NAME,COUNT,PRICE_PER_UNIT,TOTAL_PRICE,PAID_AMOUNT,BALANCE_AMOUNT,DISCOUNT_RATE,ORDER_STATUS,DESCRIPTION,CREATED_DATE FROM SALES_ORDER WHERE VENDOR_ID=@VENDOR_ID ORDER BY ORDER_ID"; List <KeyValuePair <string, string> > queryParameter = new List <KeyValuePair <string, string> >(); queryParameter.Add(new KeyValuePair <string, string>("@VENDOR_ID", vendorInfo.ToString())); dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, queryParameter); } else if (vendorInfo == null) { selectQueryString = "SELECT ORDER_ID,VENDOR_ID,MANUFACTURER_NAME,PRODUCT_ID,PRODUCT_TYPE,PRODUCT_NAME,COUNT,PRICE_PER_UNIT,TOTAL_PRICE,PAID_AMOUNT,BALANCE_AMOUNT,DISCOUNT_RATE,ORDER_STATUS,DESCRIPTION,CREATED_DATE FROM SALES_ORDER WHERE ORDER_STATUS=@ORDER_STATUS ORDER BY ORDER_ID"; List <KeyValuePair <string, string> > queryParameter = new List <KeyValuePair <string, string> >(); queryParameter.Add(new KeyValuePair <string, string>("@ORDER_STATUS", orderStatus)); dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, queryParameter); } else if ((vendorInfo != null) && (orderStatus != null)) { selectQueryString = "SELECT ORDER_ID,VENDOR_ID,MANUFACTURER_NAME,PRODUCT_ID,PRODUCT_TYPE,PRODUCT_NAME,COUNT,PRICE_PER_UNIT,TOTAL_PRICE,PAID_AMOUNT,BALANCE_AMOUNT,DISCOUNT_RATE,ORDER_STATUS,DESCRIPTION,CREATED_DATE FROM SALES_ORDER WHERE VENDOR_ID=@VENDOR_ID AND ORDER_STATUS=@ORDER_STATUS ORDER BY ORDER_ID"; List <KeyValuePair <string, string> > queryParameter = new List <KeyValuePair <string, string> >(); queryParameter.Add(new KeyValuePair <string, string>("@VENDOR_ID", vendorInfo.ToString())); queryParameter.Add(new KeyValuePair <string, string>("@ORDER_STATUS", orderStatus.ToString())); dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, queryParameter); } List <OrderProperties> listOfOrders = new List <OrderProperties>(); if (dataset != null) { foreach (DataRow row in dataset.Tables[0].Rows) { OrderProperties orderPropereties = new OrderProperties(); orderPropereties.Order_Id = row["Order_Id"].ToString(); orderPropereties.Vendor_Id = row["Vendor_Id"].ToString(); orderPropereties.Manufacturer_Name = row["Manufacturer_Name"].ToString(); orderPropereties.Product_Id = row["Product_Id"].ToString(); orderPropereties.Product_Name = row["Product_Name"].ToString(); orderPropereties.Product_Type = row["Product_Type"].ToString(); orderPropereties.Count = Convert.ToInt16(row["Count"].ToString()); orderPropereties.Price_Per_Unit = Convert.ToDouble(row["Price_Per_Unit"].ToString()); orderPropereties.Total_Price = Convert.ToDouble(row["Total_Price"].ToString()); orderPropereties.Paid_Amount = Convert.ToDouble(row["Paid_Amount"].ToString()); orderPropereties.Balance_Amount = Convert.ToDouble(row["Balance_Amount"].ToString()); orderPropereties.Description = row["Description"].ToString(); orderPropereties.Order_Status = row["Order_Status"].ToString(); orderPropereties.OrderDate = row["Created_Date"].ToString().Substring(0, 11); orderPropereties.Discount_Rate = row["Discount_Rate"].ToString(); listOfOrders.Add(orderPropereties); } } return(listOfOrders); }
//retrieve products of a manufacturer from database public List <ProductProperties> getManufacturerProductsList(ProductProperties manufacturer) { DataSet resultDataset; List <ProductProperties> manufacturerProducts = new List <ProductProperties>(); try { string selectQueryString = "SELECT PRODUCT.PRODUCT_ID,PRODUCT_TYPE,PRODUCT.PRODUCT_NAME,PRODUCT.PRODUCT_CURRENT_COUNT,PRODUCT.PRICE_PER_UNIT,PRODUCT.MINIMUM_COUNT,MANUFACTURER.MANUFACTURER_ID, MANUFACTURER.MANUFACTURER_NAME,MANUFACTURER_DETAIL FROM PRODUCT INNER JOIN MANUFACTURER ON PRODUCT.MANUFACTURER_ID = MANUFACTURER.MANUFACTURER_ID WHERE MANUFACTURER.MANUFACTURER_ID = @MANFACTURERID ORDER BY MANUFACTURER.MANUFACTURER_NAME;"; List <KeyValuePair <string, string> > tableQueryData = new List <KeyValuePair <string, string> >(); tableQueryData.Add(new KeyValuePair <string, string>("@MANFACTURERID", manufacturer.Manufacturer_Id)); resultDataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, tableQueryData); }catch (Exception ex) { Console.WriteLine(ex.StackTrace); } return(manufacturerProducts); }
//Retrieve multiple user information from database public List <UserInformationProperties> getMultipleUserInfo(string userStatus) { DataSet dataset = new DataSet(); List <UserInformationProperties> usersList = new List <UserInformationProperties>(); string selectQueryString = null; if (userStatus.Equals("ALL")) { selectQueryString = "SELECT NAME,CONTACT,ADDRESS,EMAIL_ID,USER_ID,ROLE,IS_VENDOR,VENDOR_ID,VENDOR_NAME,STATUS,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE FROM USER_TABLE"; dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, null); } else { selectQueryString = "SELECT NAME,CONTACT,ADDRESS,EMAIL_ID,ROLE,USER_ID,IS_VENDOR,VENDOR_ID,VENDOR_NAME,STATUS,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE FROM USER_TABLE WHERE STATUS=@STATUS"; List <KeyValuePair <string, string> > tableQueryData = new List <KeyValuePair <string, string> >(); tableQueryData.Add(new KeyValuePair <string, string>("@STATUS", userStatus)); dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, tableQueryData); } if (dataset != null) { foreach (DataRow row in dataset.Tables[0].Rows) { UserInformationProperties userinformation = new UserInformationProperties(); userinformation.Name = row["Name"].ToString(); userinformation.Contact = row["Contact"].ToString(); userinformation.Address = row["Address"].ToString(); userinformation.EMail_Id = row["Email_id"].ToString(); userinformation.User_Id = row["User_Id"].ToString(); userinformation.Role = row["Role"].ToString(); userinformation.Is_Vendor = row["Is_Vendor"].ToString(); userinformation.Vendor_ID = row["Vendor_Id"].ToString(); userinformation.Vendor_Name = row["Vendor_Name"].ToString(); userinformation.Status = row["Status"].ToString(); userinformation.Created_By = row["Created_By"].ToString(); userinformation.Created_date = row["Created_date"].ToString(); userinformation.Modified_By = row["Modified_By"].ToString(); userinformation.Modified_Date = row["Modified_Date"].ToString(); usersList.Add(userinformation); } } return(usersList); }
public static DiscountProperties getDiscountOfCustomer(string vendor_Discount_Id) { System.Data.DataSet dataset = new System.Data.DataSet(); DiscountProperties discount = new DiscountProperties(); string selectQueryString = "SELECT DISCOUNT_RATE,DISCOUNT_ID FROM DISCOUNT WHERE DISCOUNT_ID=@DISCOUNT_ID;"; List <KeyValuePair <string, string> > parameters = new List <KeyValuePair <string, string> >(); parameters.Add(new KeyValuePair <string, string>("@DISCOUNT_ID", vendor_Discount_Id)); dataset = DatabaseConnectionHandler.executeSelectQuery(selectQueryString, parameters); if (dataset != null) { foreach (System.Data.DataRow row in dataset.Tables[0].Rows) { discount.Discount_Id = row["Discount_Id"].ToString(); discount.Discount_Rate = row["Discount_Rate"].ToString(); } } return(discount); }
//Validate user login with database public UserInformationProperties validateUserLogin(LoginProperties userCreds) { DataSet reader; List <KeyValuePair <string, string> > tableQueryData = new List <KeyValuePair <string, string> >(); UserInformationProperties userProfileInformation = new UserInformationProperties(); try { string queryString = "SELECT NAME,CONTACT,ADDRESS,EMAIL_ID,USER_ID,PASSWORD,ROLE,IS_VENDOR,VENDOR_ID,VENDOR_NAME,STATUS FROM USER_TABLE WHERE USER_ID=@USERID AND PASSWORD=@PASSWORD AND STATUS='ACTIVE'"; tableQueryData.Add(new KeyValuePair <string, string>("@USERID", userCreds.Username)); tableQueryData.Add(new KeyValuePair <string, string>("@PASSWORD", userCreds.Password)); reader = DatabaseConnectionHandler.executeSelectQuery(queryString, tableQueryData); if (reader != null) { userProfileInformation = getSingleUserInfo(userCreds.Username); } } catch (Exception ex) { Console.WriteLine("Login issue occured", ex.Message); } return(userProfileInformation); }