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