public List <OrderPurchase> GetEcommerceAccountByCompanyIDs(string[] companyIDs, int userID) { string SQLString = "select ebay_account.*, tblCompanyebayaccount.companyebayaccountid,tblCompanyebayaccount.AdminID,tblCompanyebayaccount.StatusID, tblCompany.companyname, c_User.FirstName, c_User.LastName from ebay_account,tblCompanyebayaccount,tblCompany,c_user "; SQLString += " where ebay_account.id=tblCompanyebayaccount.ebayAccountID and tblcompany.companyid=tblCompanyebayaccount.companyid and c_user.UserID=tblCompanyebayaccount.AdminId"; if (companyIDs != null && companyIDs.Length > 0 && !string.IsNullOrEmpty(companyIDs[0])) { SQLString += " AND tblCompanyebayaccount.CompanyID in (" + String.Join(",", companyIDs) + ")"; } else { //get the companys only the owner can access SQLString += " AND tblCompanyebayaccount.CompanyID IN (" + Helpers.Helpers.GetUserManagedCompanyString(userID.ToString()) + ")"; } using (MySqlDataAdapter adapter = new MySqlDataAdapter(SQLString, new MySqlConnection(Helpers.Helpers.GetERPConnectionString()))) { DataSet ds = new DataSet(); adapter.Fill(ds); DataTable tb = (DataTable)ds.Tables[0]; List <OrderPurchase> allECommerceAccounts = new List <OrderPurchase>(); if (tb != null && tb.Rows.Count > 0) { for (int i = 0; i < tb.Rows.Count; i++) { DataRow dr = tb.Rows[i]; allECommerceAccounts.Add(OrderPurchase.fillInObject(dr)); } } return(allECommerceAccounts); } }
public OrderPurchase GetPurchaseOrderByID(int ID) { string SQLString = "select ebay_account.*, tblCompanyebayaccount.companyebayaccountid,tblCompanyebayaccount.AdminID,tblCompanyebayaccount.StatusID, tblCompany.companyname, c_User.FirstName, c_User.LastName from ebay_account,tblCompanyebayaccount,tblCompany, c_User "; SQLString += " where ebay_account.id=tblCompanyebayaccount.ebayAccountID and tblcompany.companyid=tblCompanyebayaccount.companyid and c_User.UserID=tblCompanyebayaccount.AdminId"; SQLString += " AND ebay_account.id IN (" + ID + ")"; using (MySqlDataAdapter adapter = new MySqlDataAdapter(SQLString, new MySqlConnection(Helpers.Helpers.GetERPConnectionString()))) { DataSet ds = new DataSet(); adapter.Fill(ds); DataTable tb = (DataTable)ds.Tables[0]; List <OrderPurchase> allECommerceAccounts = new List <OrderPurchase>(); if (tb != null && tb.Rows.Count > 0) { DataRow dr = tb.Rows[0]; return(OrderPurchase.fillInObject(dr)); } return(null); } }
public PartialViewResult ReportView(string startDate, string endDate, string[] companyIDs, string[] WarehouseIDs, string[] purchasePlanIDs, string[] statusIDs) { Session["startDate"] = startDate; Session["endDate"] = endDate; Session["selectedCompanyIDs"] = companyIDs; Session["selectedWarehouseIDs"] = WarehouseIDs; Session["selectedECommerceStatusIDs"] = statusIDs; Session["selectedECommercePlanIDs"] = purchasePlanIDs; DateTime start = DateTime.Parse(startDate); DateTime end = DateTime.Parse(endDate); StringBuilder sbOperation = new StringBuilder(); sbOperation.Append("Select distinct purchase_order.*, tblCompany.CompanyName, c_status.name, ebay_store.store_name from purchase_order "); sbOperation.Append(" left outer JOIN tblCompany on tblCompany.CompanyID = purchase_order.PurchaseCompanyID "); sbOperation.Append(" INNER JOIN c_status on c_status.statusID = purchase_order.statusID "); sbOperation.Append(" LEFT OUTER JOIN ebay_store on ebay_store.id = purchase_order.io_warehouse "); sbOperation.Append(" where updateDate>='" + start.ToString("yyyy-MM-dd") + "' "); sbOperation.Append(" and updateDate<='" + end.ToString("yyyy-MM-dd") + "'"); // Add modality id to the where clause if appropriate if (companyIDs != null && companyIDs.Count() > 0 && !string.IsNullOrEmpty(companyIDs[0])) { sbOperation.Append(" AND tblCompany.CompanyID IN (" + String.Join(",", companyIDs) + ")"); } else { //get the companys only the owner can access sbOperation.Append(" AND tblCompany.CompanyID IN (" + GetUserManagedCompanyString() + ")"); } // Add modality id to the where clause if appropriate if (WarehouseIDs != null && WarehouseIDs.Count() > 0 && !string.IsNullOrEmpty(WarehouseIDs[0])) { sbOperation.Append(" AND ebay_store.id IN (" + String.Join(",", WarehouseIDs) + ")"); } if (statusIDs != null && statusIDs.Count() > 0 && !string.IsNullOrEmpty(statusIDs[0])) { sbOperation.Append(" AND c_status.StatusID IN (" + String.Join(",", statusIDs) + ")"); } //sbOperation.Append(" Order by tblRent.DueDate, tblRent.PaymentDate"); List <OrderPurchase> result = new List <OrderPurchase>(); using (MySqlDataAdapter adapter = new MySqlDataAdapter(sbOperation.ToString(), new MySqlConnection(Helpers.Helpers.GetERPConnectionString()))) { DataSet ds = new DataSet(); adapter.Fill(ds); DataTable tb = (DataTable)ds.Tables[0]; if (tb != null && tb.Rows.Count > 0) { for (int i = 0; i < tb.Rows.Count; i++) { DataRow dr = tb.Rows[i]; OrderPurchase row = new OrderPurchase(); row = OrderPurchase.fillInObject(dr); result.Add(row); } } } return(PartialView("ReportView", result)); }