/// <summary>
/// Returns a list of orderlinelists based on input parameters
/// </summary>
/// <param name="orderLineId"></param>
/// <param name="uniqueProductId"></param>
/// <returns>OrderLineList orderLineList</returns>
public OrderLineList Read(int orderLineId, int uniqueProductId)
{
OrderLineList orderLineList = new OrderLineList();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM OrderLineList WHERE id = @orderLineId, uniqueProductId = @uniqueProductId";
cmd.Parameters.AddWithValue("orderLineId", orderLineId);
cmd.Parameters.AddWithValue("uniqueProductId", uniqueProductId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
orderLineList = new OrderLineList
{
_id = reader.GetInt32(reader.GetOrdinal("id")),
_uniqueProductId = reader.GetInt32(reader.GetOrdinal("uniqueProductId"))
};
}
}
}
return(orderLineList);
}
/// <summary>
/// Creates an instance of OrderLine in database
/// </summary>
/// <param name="orderLine"></param>
/// <returns>int id</returns>
public int Create(OrderLine orderLine)
{
int id;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO OrderLine(orderId) VALUES (@orderId); " +
"SELECT SCOPE_IDENTITY()";
cmd.Parameters.AddWithValue("orderId", orderLine._orderId);
id = Convert.ToInt32(cmd.ExecuteScalar());
foreach (UniqueProduct p in orderLine._uniqueProducts)
{
OrderLineList oll = new OrderLineList
{
_uniqueProductId = p._id,
_orderLineId = id
};
_dbOrderLineList.Create(oll);
}
}
}
return(id);
}
/// <summary>
/// Returns all orderLineLists
/// </summary>
/// <returns>List<OrderLineList> orderLineLists</returns>
public List <OrderLineList> GetAll()
{
List <OrderLineList> orderLineLists = new List <OrderLineList>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "select * from OrderLineList";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
OrderLineList orderLineList = new OrderLineList
{
_orderLineId = reader.GetInt32(reader.GetOrdinal("orderLineId")),
_uniqueProductId = reader.GetInt32(reader.GetOrdinal("productId"))
};
orderLineLists.Add(orderLineList);
}
}
}
return(orderLineLists);
}
private void button1_Click(object sender, EventArgs e)
{
OrderLineList oll = new OrderLineList();
oll.Clear();
DateTime todayDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day);
string bob = "";
Order o = new Order
(1, bob, dateTimePicker1.Value.ToString("yyyy-MM-dd"), 1);
ol.UpdateOrder(o);
MessageBox.Show("Stock Received");
this.Close();
}
/// <summary>
/// Deletes an OrderLineList based on input parameters
/// </summary>
/// <param name="orderLineList"></param>
public void Delete(OrderLineList orderLineList)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "DELETE FROM OrderLineList where id = @id, uniqueProductId = @uniqueProductId";
cmd.Parameters.AddWithValue("orderLineId", orderLineList._orderLineId);
cmd.Parameters.AddWithValue("uniqueProductId", orderLineList._uniqueProductId);
cmd.ExecuteNonQuery();
}
}
}
public ViewOrder(int _id)
{
InitializeComponent();
id = _id;
string s = "";
pl = new OrderLineList(id, s);
o = ol.ViewAOrder(id);
dataGridView1.DataSource = pl;
foreach (Supplier u in sl)
{
if(o.Supplier_ID == u.Supplier_id){
textBox2.Text = u.Name;
}
}
textBox1.Text = o.DatePlaced;
}
public ReceiveStock(int _id)
{
InitializeComponent();
id = _id;
string s = "";
pl = new OrderLineList(id, s);
o = ol.ViewAOrder(id);
dataGridView1.DataSource = pl;
foreach (Supplier u in sl)
{
if (o.Supplier_ID == u.Supplier_id)
{
textBox1.Text = u.Name;
}
}
dateTimePicker1.Value = Convert.ToDateTime(o.DatePlaced);
}
/// <summary>
/// Creates an instance of an orderLineList in the database
/// </summary>
/// <param name="orderLineList"></param>
/// <returns>int id</returns>
public int Create(OrderLineList orderLineList)
{
int id;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO OrderLineList(orderLineId, uniqueProductId) " +
"VALUES (@orderLineId, @uniqueProductId); " +
"SELECT SCOPE_IDENTITY()";
cmd.Parameters.AddWithValue("orderLineId", orderLineList._orderLineId);
cmd.Parameters.AddWithValue("uniqueProductId", orderLineList._uniqueProductId);
id = Convert.ToInt32(cmd.ExecuteScalar());
}
}
return(id);
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string str = "Are you sure you want\nto update this order? \nYes to confirm,\nNo to edit details,\nCancel to exit";
string form = "UpdateOrder";
OrderLineList oll = new OrderLineList();
oll.Clear();
DateTime todayDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day);
//string bob = "";
//Order o = new Order
// (1, bob, dateTimePicker1.Value.ToString("yyyy-MM-dd"), 1);
//ol.UpdateOrder(o);
for (int i = 0; i < dataGridView1.RowCount - 1; i++)
{
oll.UpdateOrderLine(new OrderLine(
Convert.ToInt32(dataGridView1.Rows[0].Cells[0].Value),
Convert.ToInt32(dataGridView1.Rows[0].Cells[1].Value),
Convert.ToInt32(dataGridView1.Rows[0].Cells[2].Value),
Convert.ToInt32(dataGridView1.Rows[0].Cells[3].Value)
));
}
ConfirmationMessage a = new ConfirmationMessage(str, form);
a.ShowDialog();
MessageBox.Show("Your Order has been updated");
this.Close();
}
catch (Exception d)
{
MessageBox.Show("ERROR: " + d);
}
}
internal void InitializeCompleted()
{
if (!DesignerProperties.IsInDesignTool)
{
Types = new ObservableCollection <Transactions>();
if (LoggedUserInfo.CurrLang == 0)
{
Types.Add(new Transactions {
TransactionId = 0, TransactionName = "أمر الشراء"
});
Types.Add(new Transactions {
TransactionId = 1, TransactionName = "صباغة"
});
Types.Add(new Transactions {
TransactionId = 2, TransactionName = "Transfer"
});
}
else
{
Types.Add(new Transactions {
TransactionId = 0, TransactionName = "Purshase Order"
});
Types.Add(new Transactions {
TransactionId = 1, TransactionName = "Dyeing"
});
Types.Add(new Transactions {
TransactionId = 2, TransactionName = "Transfer"
});
}
Client = new CRUD_ManagerServiceClient();
GetItemPermissions(PermissionItemName.Reservation.ToString());
ObservableCollection <GetItemOnhand_Result> OnHandList = new ObservableCollection <GetItemOnhand_Result>();
Client.GetReservationMainDetailByFabricCompleted += (s, f) =>
{
if (f.OnHandList != null)
{
OnHandList = f.OnHandList;
}
TransactionHeader = new TblReservationHeaderViewModel();
foreach (var variable in f.Result)
{
TransactionHeader.TransactionMainDetails.Add(ReservationMappers.MaptoViewModel(variable, f.mainFabricList, OnHandList));
}
Loading = false;
};
Client.GetAllSeasonsCompleted += (s, sv) =>
{
foreach (var row in sv.Result)
{
if (SeasonList.All(x => x.Iserial != row.Iserial))
{
SeasonList.Add(new TblLkpSeason().InjectFrom(row) as TblLkpSeason);
}
}
};
Client.GetAllSeasonsAsync();
Client.GetAllBrandsCompleted += (s, sv) =>
{
BrandsList.Clear();
foreach (var variable in sv.Result.OrderBy(x => x.Brand_Code))
{
BrandsList.Add(variable);
}
};
Client.GetAllBrandsAsync(0);
TransactionGuid = Guid.NewGuid().ToString();
TransactionHeader = new TblReservationHeaderViewModel {
DocDate = DateTime.Now.Date
};
SelectedMainDetails = new TblReservationMainDetailsViewModel();
Client.GetReservationHeaderListCompleted += (d, i) =>
{
foreach (var item in i.Result)
{
TransactionHeaderList.Add(ReservationMappers.MaptoViewModel(item));
}
};
lkpClient.GetTblBrandSectionLinkCompleted += (x, sv) =>
{
BrandSectionList.Clear();
foreach (var row in sv.Result)
{
BrandSectionList.Add(row.TblLkpBrandSection1);
}
};
SalesorderList = new ObservableCollection <CRUD_ManagerServiceSalesOrderDto>();
SalesorderList.CollectionChanged += SalesorderList_CollectionChanged;
Client.GetPurchaseOrderSalesOrdersCompleted += (d, s) =>
{
foreach (var variable in s.Result)
{
if (!SalesorderList.Any(
x =>
x.SalesOrder.Contains(variable.SalesOrder) &&
x.SalesOrderColor.Contains(variable.SalesOrderColor)) && !SelectedMainDetails.ReservationDetailsViewModelList.Any(x => x.SalesOrder.Contains(variable.SalesOrder) &&
x.SalesOrderColor.Contains(variable.SalesOrderColor)))
{
SalesorderList.Add(variable);
}
}
};
Client.GetPurchaseOrderLinesCompleted += (s, f) =>
{
foreach (var item in f.Result.Where(item => !OrderLineList.Contains(FabricInspectionMapper.MapToOrderLine(item))))
{
OrderLineList.Add(FabricInspectionMapper.MapToOrderLine(item));
}
Loading = false;
};
Client.GetRecivedDyedOrdersCompleted += (s, f) =>
{
foreach (var item in f.Result.Where(item => !OrderLineList.Contains(FabricInspectionMapper.MapToOrderLine(item))))
{
OrderLineList.Add(FabricInspectionMapper.MapToOrderLine(item));
}
};
Client.GetTransferInventDimLinesCompleted += (s, f) =>
{
foreach (var item in f.Result.Where(item => !OrderLineList.Contains(FabricInspectionMapper.MapToOrderLine(item))))
{
OrderLineList.Add(FabricInspectionMapper.MapToOrderLine(item));
}
};
Client.GetReservationMainDetailsCompleted += (s, f) =>
{
foreach (var variable in f.Result)
{
if (variable.Inspected)
{
Client.GetResInspectionListAsync(variable.LineNum, TransactionHeader.TransOrder);
}
//f.mainFabricList;
TransactionHeader.TransactionMainDetails.Add(ReservationMappers.MaptoViewModel(variable, f.mainFabricList));
}
};
Client.SaveReservationCompleted += (a, f) =>
{
if (f.ErrorExists)
{
var visList = TransactionHeader.TransactionMainDetails.Where(x => f.Result.All(s => s.Fabric == x.Fabric && s.FabricColor == x.FabricColor));
foreach (var item in TransactionHeader.TransactionMainDetails)
{
item.ErrorVisbility = visList.Contains(item) ? Visibility.Visible : Visibility.Collapsed;
}
}
else
{
var tblFabricInspectionDetail = f.Result.FirstOrDefault();
if (tblFabricInspectionDetail != null)
{
TransactionHeader.Iserial = tblFabricInspectionDetail.Tbl_ReservationHeader;
}
TransactionHeader.TransactionMainDetails.Clear();
foreach (var item in f.Result)
{
TransactionHeader.TransactionMainDetails.Add((TblReservationMainDetailsViewModel) new TblReservationMainDetailsViewModel().InjectFrom(item));
}
var currentUi = Thread.CurrentThread.CurrentUICulture;
if (currentUi.DisplayName == "العربية")
{
MessageBox.Show("Saved");
}
else
{
MessageBox.Show("Saved");
}
}
};
Client.GenerateReservationFromPlanCompleted += (s, sv) => Client.GetReservationMainDetailsAsync(TransactionHeader.Iserial);
Client.GetSalesOrderReservationCompleted += (s, sv) =>
{
GenericMapper.InjectFromObCollection(SelectedMainDetails.ReservationDetailsViewModelList, sv.Result);
foreach (var item in (SelectedMainDetails.ReservationDetailsViewModelList))
{
item.Tbl_ReservationMainDetails = SelectedMainDetails.Iserial;
}
};
Client.ReservationLineNumCompleted += (s, p) =>
{
var lines = new ObservableCollection <decimal>(p.Result);
if (TransactionHeader.TransactionMainDetails.Count() != 0)
{
foreach (var variable in TransactionHeader.TransactionMainDetails.Select(x => x.LineNum))
{
lines.Add(variable);
}
}
if (SortBy == null)
{
SortBy = "it.PURCHQTY";
}
if (TransactionHeader.TransactionType == 0)
{
Client.GetPurchaseOrderLinesAsync(OrderLineList.Count, PageSize, "ccm", TransactionHeader.TransOrder, lines, "it.PURCHQTY desc", Filter, ValuesObjects);
}
if (TransactionHeader.TransactionType == 1)
{
Client.GetRecivedDyedOrdersAsync(OrderLineList.Count, PageSize, TransactionHeader.TransOrder, lines, "it.PURCHQTY desc", Filter, ValuesObjects);
}
else if (TransactionHeader.TransactionType == 2)
{
Client.GetTransferInventDimLinesAsync(OrderLineList.Count, PageSize, "ccm", TransactionHeader.TransOrder, lines, "it.PURCHQTY desc", Filter, ValuesObjects);
}
};
}
}
public override void PerformSqlTask()
{
OrderList[] orderTable;
OrderLineList[] orderLinesTable;
DateTime startingTime;
Random rnd;
string salespersonID;
int arrPos1 = 0;
int arrPos2 = 0;
try
{
using (var con = new NpgsqlConnection(_connectionstring))
{
con.Open();
// Since we are working with PG composite types, we need to make sure we map these types.
// We also need to make sure that the full path to the type is specified. i.e. "website.order_list"
con.MapComposite <OrderList>("website.order_list");
con.MapComposite <OrderLineList>("website.order_line_list");
while (!_erroroccurred)
{
try
{
startingTime = DateTime.Now;
rnd = new Random();
using (var selectCommand = new NpgsqlCommand("SELECT person_id FROM application.people WHERE is_employee <> false ORDER BY random() LIMIT 1;", con))
{
using (var reader = selectCommand.ExecuteReader())
{
reader.Read();
salespersonID = reader.GetValue(0).ToString();
}
}
using (var selectCommand = new NpgsqlCommand("SELECT 1 AS order_reference, c.customer_id, c.primary_contact_person_id AS contact_person_id,current_date + 1 AS expected_delivery_date,cast(floor(random() * 10000) + 1 as varchar(20)) AS customer_purchase_order_number,false AS is_undersupply_backordered,'Auto-generated' AS comments,c.delivery_address_line_1 || ', ' || c.delivery_address_line_2 AS delivery_instructions FROM sales.customers AS c OFFSET floor(random() * (SELECT count(1) FROM sales.customers)) LIMIT 1;", con))
{
using (var reader = selectCommand.ExecuteReader())
{
reader.Read();
orderTable = new OrderList[1];
orderTable[arrPos1++] = new OrderList
{
OrderReference = (int)reader.GetValue(0),
CustomerId = (int)reader.GetValue(1),
ContactPersonId = (int)reader.GetValue(2),
ExpectedDeliveryDate = (DateTime)reader.GetValue(3),
CustomerPurchaseOrderNumber = (string)reader.GetValue(4),
IsUndersupplyBackordered = (bool)reader.GetValue(5),
Comments = (string)reader.GetValue(6),
DeliveryInstructions = (string)reader.GetValue(7)
};
}
}
using (var selectCommand = new NpgsqlCommand(@"WITH si AS
(
SELECT random() as rand1,
random() as rand2,
stock_item_id,
stock_item_name,
is_chiller_stock
FROM warehouse.stock_items
),
si1 AS
(
SELECT 1 AS order_reference,
si.stock_item_id,
si.stock_item_name AS description,
floor(rand1 * 10) + 1 AS quantity
FROM si
WHERE is_chiller_stock = false
ORDER BY rand2
LIMIT 7
),
si2 AS
(
SELECT 1 AS order_reference,
si.stock_item_id,
si.stock_item_name AS description,
floor(rand1 * 10) + 1 AS quantity
FROM si
WHERE is_chiller_stock = true
AND
true = :boolvalue
ORDER BY rand2
LIMIT 1
)
SELECT *
FROM si1
UNION ALL
SELECT *
FROM si2; ", con))
{
NpgsqlDataReader reader;
if (rnd.Next(1, 100) < 4)
{
selectCommand.Parameters.Add(new NpgsqlParameter("boolvalue", NpgsqlDbType.Boolean)
{
Value = "true"
});
reader = selectCommand.ExecuteReader();
orderLinesTable = new OrderLineList[8];
}
else
{
selectCommand.Parameters.Add(new NpgsqlParameter("boolvalue", NpgsqlDbType.Boolean)
{
Value = "false"
});
reader = selectCommand.ExecuteReader();
orderLinesTable = new OrderLineList[7];
}
while (reader.Read())
{
orderLinesTable[arrPos2++] = new OrderLineList
{
OrderReference = (int)reader.GetValue(0),
StockItemId = (int)reader.GetValue(1),
Description = (string)reader.GetValue(2),
Quantity = Convert.ToInt32(reader.GetValue(3))
};
}
reader.Close();
}
using (var insertCommand = new NpgsqlCommand("select website.insert_customer_orders(@p1::website.order_list[],@p2::website.order_line_list[],@p3,@p4)", con))
{
insertCommand.Parameters.Add(new NpgsqlParameter("p1", NpgsqlDbType.Composite)
{
Value = orderTable,
SpecificType = typeof(OrderList[])
});
insertCommand.Parameters.Add(new NpgsqlParameter("p2", NpgsqlDbType.Composite)
{
Value = orderLinesTable,
SpecificType = typeof(OrderLineList[])
});
insertCommand.Parameters.AddWithValue("p3", Convert.ToInt32(salespersonID));
insertCommand.Parameters.AddWithValue("p4", Convert.ToInt32(salespersonID));
insertCommand.ExecuteNonQuery();
}
_parentform.UpdateTotals((int)DateTime.Now.Subtract(startingTime).TotalMilliseconds);
}
catch (Exception ex)
{
HandleException(ex);
}
// reset array positions
arrPos1 = 0;
arrPos2 = 0;
}
}
}
catch (Exception ex)
{
HandleException(ex);
}
}