private void updateOrder(SaleOrder model)
{
using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString()))
{
if (string.IsNullOrEmpty(model.storeId))
{
model.storeId = "0";
}
// Create the Command and Parameter objects.
SqlCommand cmd = new SqlCommand("", connection);
connection.Open();
cmd.CommandText = "Update tblEbayOrder set "
+ " account = '" + model.account + "' , "
+ " sku = '" + model.sku + "' , "
+ " quantity = " + model.quantity + " , "
+ " cost = " + model.cost + " , "
+ " sales = " + model.sales + " , "
+ " ebayFee = " + model.ebayFee + " , "
+ " paypalFee = " + model.paypalFee + " , "
+ " shippingFee = " + model.shippingFee + " , "
+ " internationalShippingFee = " + model.internationalShippingFee + " , "
+ " Date = '" + model.orderDate + "' , "
+ " profit = " + model.profit + " , "
+ " pieces = " + model.pieces + " , "
+ " discount = " + model.discount + " , "
+ " name = '" + model.name + "' , "
+ " state = '" + model.state + "' , "
+ " storeId = " + model.storeId + " "
+ " where orderNumber = " + model.ebay_id
+ " and sku='" + model.sku + "'";
cmd.ExecuteNonQuery();
connection.Close();
}
}
private SaleOrder getOrder(int number, string sku)
{
//// Execute the SQL query and get the results
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SunriseConnectionString"].ConnectionString))
{
SaleOrder row = new SaleOrder();
string sql = "select * FROM tblEbayOrder where orderNumber = " + number;
//+ " and sku = '" + sku + "'";
SqlCommand command = new SqlCommand(sql, connection);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()) // Read each result row and extract the data
{
row.ebay_id = reader[0].ToString();
row.sku = reader[1].ToString();
row.quantity = Int32.Parse(reader[3].ToString());
row.cost = double.Parse(reader[4].ToString());
row.sales = double.Parse(reader[5].ToString());
row.ebayFee = double.Parse(reader[6].ToString());
row.paypalFee = double.Parse(reader[7].ToString());
row.shippingFee = double.Parse(reader[8].ToString());
row.internationalShippingFee = double.Parse(reader[9].ToString());
row.orderDate = DateTime.Parse(reader[10].ToString());
row.profit = double.Parse(reader[11].ToString());
row.pieces = Int32.Parse(reader[12].ToString());
row.discount = Int32.Parse(reader[13].ToString());
row.name = reader[14].ToString();
row.state = reader[15].ToString();
row.storeId = reader[16].ToString();
}
reader.Close();
}
catch (Exception ex)
{
//display the exception message in viewbag
ViewBag.MyExeption = ex.Message;
ViewBag.MyExeptionCSS = "errorMessage";
}
return(row);
}
}
public PartialViewResult ReportView(string[] vendors, string[] skus, string[] accounts, string[] warehouses, string[] states, string startDate, string endDate, bool dispalyNegative)
{
//get the first day of the week for the start date
DateTime start_date = DateTime.Parse(startDate);
DateTime end_date = DateTime.Parse(endDate);
DateTime startOfWeek = start_date.AddDays(-(int)start_date.DayOfWeek);
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder selectClauseBuilder = new StringBuilder();
StringBuilder whereClauseBuilder = new StringBuilder();
sqlBuilder.Append(" select * FROM tblEbayOrder ");
if (start_date != null)
{
whereClauseBuilder.Append(" date>= '" + start_date + "'");
}
if (end_date != null)
{
whereClauseBuilder.Append(" AND date<= '" + end_date + "'");
}
// Define the base SQL query
if (skus != null && skus.Count() > 0 && !string.IsNullOrEmpty(skus[0]))
{
whereClauseBuilder.Append(" AND sku in ('" + String.Join("','", skus) + "')");
}
// Add the procedure code(s) to the where clause if appropriate
if (accounts != null && accounts.Count() > 0 && !string.IsNullOrEmpty(accounts[0]))
{
whereClauseBuilder.Append(" AND account in ('" + String.Join("','", accounts) + "')");
}
// Add site id to the where clause if appropriate
if (warehouses != null && warehouses.Count() > 0 && !string.IsNullOrEmpty(warehouses[0]))
{
whereClauseBuilder.Append(" AND warehouse in ('" + String.Join("','", warehouses) + "')");
}
// Add radiologist id to the where clause if appropriate
if (states != null && states.Count() > 0 && !string.IsNullOrEmpty(states[0]))
{
whereClauseBuilder.Append(" AND state in ('" + String.Join("','", states) + "')");
}
// Add Hour of the Day to the where clause if appropriate
if (vendors != null && vendors.Count() > 0 && !string.IsNullOrEmpty(vendors[0]))
{
whereClauseBuilder.Append(" AND vendor in ('" + String.Join("','", vendors) + "')");
}
if (dispalyNegative)
{
whereClauseBuilder.Append(" AND profit < = 0 ");
}
sqlBuilder.Append(" where " + whereClauseBuilder);
sqlBuilder.Append(" order by date, SKU");
List <Dictionary <string, object> > dataResult = new List <Dictionary <string, object> >();
List <SaleOrder> result = new List <SaleOrder>();
//// Execute the SQL query and get the results
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SunriseConnectionString"].ConnectionString))
{
SqlCommand command = new SqlCommand(sqlBuilder.ToString(), connection);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()) // Read each result row and extract the data
{
SaleOrder row = new SaleOrder();
row.ebay_id = reader[0].ToString();
row.sku = reader[1].ToString();
row.quantity = Int32.Parse(reader[3].ToString());
row.cost = double.Parse(reader[4].ToString());
row.sales = double.Parse(reader[5].ToString());
row.ebayFee = double.Parse(reader[6].ToString());
row.paypalFee = double.Parse(reader[7].ToString());
row.shippingFee = double.Parse(reader[8].ToString());
row.internationalShippingFee = double.Parse(reader[9].ToString());
row.orderDate = DateTime.Parse(reader[10].ToString());
row.profit = double.Parse(reader[11].ToString());
row.pieces = Int32.Parse(reader[12].ToString());
row.discount = Int32.Parse(reader[13].ToString());
row.name = reader[14].ToString();
row.state = reader[15].ToString();
row.storeId = reader[16].ToString();
result.Add(row);
}
reader.Close();
}
catch (Exception ex)
{
//display the exception message in viewbag
ViewBag.MyExeption = ex.Message;
ViewBag.MyExeptionCSS = "errorMessage";
}
}
ViewBag.TableCaption = reporttitle + ": ";
return(PartialView("ReportView", result)); // Data is returned in the ViewData objects!
}