//end row commands
/// <summary>
/// flag quote as hidden from client vieww
/// </summary>
/// <param name="quoteid">Int32 unique id of quote</param>
protected bool hide_quote(Int32 quoteid)
{
//save log id to price table
int recordsaffected = 0;
SubSonic.Update upd1 = new SubSonic.Update(DAL.Pricer.Schemas.PriceValue);
recordsaffected = upd1.Set("client_visible").EqualTo(false)
.Where("quote_id").IsEqualTo(quoteid)
.Execute();
if (recordsaffected > 0)
{
return(true);
}
return(false);
}
//end default view
/// <summary>
/// update pricer with linked order id and order table with the quote id
/// </summary>
/// <param name="orderid">int32 orderid from grid</param>
/// <returns></returns>
protected bool save_to_quote(Int32 orderid, Int32 orderno, Int32 quoteid)
{
bool _result = false;
int recordsaffected = 0;
//save orderid to price values
if (orderid > 0 && quoteid > 0)
{
//append to audit log
UserClass _thisuser = (UserClass)Session["user"];
DAL.Pricer.PriceOrderLog _oq = new DAL.Pricer.PriceOrderLog();
_oq.CompanyId = _thisuser.CompanyId;
_oq.UserId = _thisuser.UserId;
_oq.QuoteId = quoteid;
_oq.LogDate = DateTime.Now;
_oq.OrderId = orderid;
_oq.OrderNo = orderno;
_oq.Save();
//get log id
Int32 _newid = (Int32)_oq.GetPrimaryKeyValue();
//save log id to price table
SubSonic.Update upd1 = new SubSonic.Update(DAL.Pricer.Schemas.PriceValue);
recordsaffected = upd1.Set("po_log_id").EqualTo(_newid)
.Where("quote_id").IsEqualTo(quoteid)
.Execute();
//save quote id to order table
SubSonic.Update upd2 = new SubSonic.Update(DAL.Logistics.Schemas.OrderTable);
recordsaffected = upd2.Set("quote_id").EqualTo(quoteid)
.Where("OrderID").IsEqualTo(orderid)
.Execute();
if (recordsaffected > 0)
{
_result = true;
}
}
return(_result);
}
private void SaveAll()
{
try
{
Utility.SetMsg(lblMsg, "", false);
decimal GiaDV = LayGiaDV();
int idThuoc = -1;
decimal GiaPhuThu = 0;
decimal GiaBHYT = LayGiaBHYT();
string KTH = "ALL";
foreach (Janus.Windows.GridEX.GridEXRow gridExRow in grdQhe.GetRows())
{
idThuoc = Utility.Int32Dbnull(gridExRow.Cells[QheDoituongThuoc.Columns.IdThuoc].Value, -1);
KTH = Utility.sDbnull(cboKhoaTH.SelectedValue, "-1") == "-1" ? "ALL" : Utility.sDbnull(cboKhoaTH.SelectedValue, "-1");
SqlQuery q =
new Select().From(QheDoituongThuoc.Schema).Where(QheDoituongThuoc.Columns.IdThuoc).
IsEqualTo(Utility.DecimaltoDbnull(gridExRow.Cells[QheDoituongThuoc.Columns.IdThuoc].Value, -1)).And(
QheDoituongThuoc.Columns.MaDoituongKcb).IsEqualTo(Utility.sDbnull(gridExRow.Cells[QheDoituongThuoc.Columns.MaDoituongKcb].Value, "-1"));
//.And(QheDoituongThuoc.Columns.MaKhoaThuchien).IsEqualTo(KTH);
//.Or(QheDoituongThuoc.Columns.MaDoituongKcb).IsEqualTo("BHYT");
GiaPhuThu = Utility.DecimaltoDbnull(gridExRow.Cells[QheDoituongThuoc.Columns.PhuthuTraituyen].Value, 0);
int v_IdLoaidoituongKcb = Utility.Int32Dbnull(gridExRow.Cells[QheDoituongThuoc.Columns.IdLoaidoituongKcb].Value, 0);
//Nếu có lưu đối tượng BHYT và tồn tại giá DV thì tự động tính phụ thu trái tuyến cho đối tượng BHYT đó
if (gridExRow.Cells[QheDoituongThuoc.Columns.IdLoaidoituongKcb].Value.ToString() == "0" && GiaDV > 0)
{
GiaBHYT = Utility.DecimaltoDbnull(gridExRow.Cells[QheDoituongThuoc.Columns.DonGia].Value, 0);
if (PropertyLib._QheGiaThuocProperties.TudongDieuChinhGiaPTTT)
GiaPhuThu = GiaDV - GiaBHYT > 0 ? GiaDV - GiaBHYT : 0;
}
//Nếu đối tượng BHYT có tồn tại thì update lại thông tin trong đó có giá phụ thu trái tuyến
if (q.GetRecordCount() > 0)
{
new Update(QheDoituongThuoc.Schema)
.Set(QheDoituongThuoc.Columns.NgaySua).EqualTo(globalVariables.SysDate)
.Set(QheDoituongThuoc.Columns.NguoiSua).EqualTo(globalVariables.UserName)
.Set(QheDoituongThuoc.Columns.IdLoaithuoc).EqualTo(
Utility.Int32Dbnull(gridExRow.Cells[QheDoituongThuoc.Columns.IdLoaithuoc].Value, -1))
.Set(QheDoituongThuoc.Columns.DonGia).EqualTo(
Utility.DecimaltoDbnull(gridExRow.Cells[QheDoituongThuoc.Columns.DonGia].Value, 0))
.Set(QheDoituongThuoc.Columns.PhuthuDungtuyen).EqualTo(
Utility.DecimaltoDbnull(gridExRow.Cells[QheDoituongThuoc.Columns.PhuthuDungtuyen].Value, 0))
.Set(QheDoituongThuoc.Columns.PhuthuTraituyen).EqualTo(GiaPhuThu)
.Set(QheDoituongThuoc.Columns.MaKhoaThuchien).EqualTo(KTH)
.Where(QheDoituongThuoc.Columns.IdQuanhe).IsEqualTo(
Utility.Int32Dbnull(gridExRow.Cells[QheDoituongThuoc.Columns.IdQuanhe].Value, "-1"))
.Execute();
}
else
{
DmucDoituongkcbCollection objectTypeCollection =
new DmucDoituongkcbController().FetchByQuery(
DmucDoituongkcb.CreateQuery().AddWhere(DmucDoituongkcb.Columns.MaDoituongKcb,
Comparison.Equals,
Utility.sDbnull(gridExRow.Cells[DmucDoituongkcb.Columns.MaDoituongKcb].Value, "-1")));
foreach (DmucDoituongkcb lObjectType in objectTypeCollection)
{
QheDoituongThuoc _newItems = new QheDoituongThuoc();
_newItems.IdDoituongKcb = lObjectType.IdDoituongKcb;
_newItems.IdLoaithuoc = Utility.Int16Dbnull(gridExRow.Cells[QheDoituongThuoc.Columns.IdLoaithuoc].Value, -1);
_newItems.IdThuoc = Utility.Int32Dbnull(gridExRow.Cells[QheDoituongThuoc.Columns.IdThuoc].Value, -1);
_newItems.TyleGiamgia = 0;
_newItems.KieuGiamgia = "%";
_newItems.DonGia = Utility.DecimaltoDbnull(gridExRow.Cells[QheDoituongThuoc.Columns.DonGia].Value, 0);
_newItems.PhuthuDungtuyen = Utility.DecimaltoDbnull(gridExRow.Cells[QheDoituongThuoc.Columns.PhuthuDungtuyen].Value, 0);
_newItems.PhuthuTraituyen = GiaPhuThu;
_newItems.IdLoaidoituongKcb = Utility.Int32Dbnull(gridExRow.Cells[QheDoituongThuoc.Columns.IdLoaidoituongKcb].Value, -1);
_newItems.MaDoituongKcb = lObjectType.MaDoituongKcb;
_newItems.NguoiTao = globalVariables.UserName;
_newItems.NgayTao = globalVariables.SysDate;
_newItems.MaKhoaThuchien = KTH;
_newItems.IsNew = true;
_newItems.Save();
gridExRow.BeginEdit();
gridExRow.Cells[QheDoituongThuoc.Columns.IdQuanhe].Value = _newItems.IdQuanhe;
gridExRow.EndEdit();
}
}
gridExRow.BeginEdit();
gridExRow.Cells[QheDoituongThuoc.Columns.PhuthuTraituyen].Value = GiaPhuThu;
gridExRow.EndEdit();
grdQhe.UpdateData();
//Nếu có chỉnh giá dịch vụ-->Tự động chỉnh giá danh mục thuốc
if (PropertyLib._QheGiaThuocProperties.TudongDieuChinhGiaDichVu)
{
SqlQuery sqlQuery = new Select().From(DmucDoituongkcb.Schema)
.Where(DmucDoituongkcb.Columns.IdLoaidoituongKcb).IsEqualTo(1)
.And(DmucDoituongkcb.Columns.MaDoituongKcb).IsEqualTo(Utility.sDbnull(gridExRow.Cells[QheDoituongThuoc.Columns.MaDoituongKcb].Value, "-1"));
DmucDoituongkcb objectType = sqlQuery.ExecuteSingle<DmucDoituongkcb>();
if (objectType != null)
{
new Update(DmucThuoc.Schema)
.Set(DmucThuoc.Columns.DonGia)
.EqualTo(Utility.DecimaltoDbnull(gridExRow.Cells[QheDoituongThuoc.Columns.DonGia].Value, 0))
.Where(DmucThuoc.Columns.IdThuoc)
.IsEqualTo(Utility.Int32Dbnull(gridExRow.Cells[QheDoituongThuoc.Columns.IdThuoc].Value, -1)).Execute();
}
}
}
new Update(DmucThuoc.Schema).Set(DmucThuoc.Columns.DonGia).EqualTo(GiaDV)
.Set(DmucThuoc.Columns.GiaBhyt).EqualTo(GiaBHYT)
.Where(DmucThuoc.Columns.IdThuoc).IsEqualTo(Utility.Int32Dbnull(grdList.CurrentRow.Cells[DmucThuoc.Columns.IdThuoc].Value, -1))
.Execute();
//Cập nhật giá BHYT cho các khoa khác
if (PropertyLib._QheGiaThuocProperties.TudongDieuChinhGiaBHYT)
{
if (GiaBHYT >= 0)
{
QheDoituongThuocCollection lstItems =
new Select().From(QheDoituongThuoc.Schema).
Where(QheDoituongThuoc.Columns.IdThuoc).
IsEqualTo(idThuoc)
.And(QheDoituongThuoc.MaKhoaThuchienColumn).IsNotEqualTo(KTH).ExecuteAsCollection<QheDoituongThuocCollection>();
foreach (QheDoituongThuoc item in lstItems)
{
int v_IdLoaidoituongKcb = item.IdLoaidoituongKcb;
if (v_IdLoaidoituongKcb == 1)
GiaDV = item.DonGia;
}
GiaPhuThu = 0;
foreach (QheDoituongThuoc item in lstItems)
{
int v_IdLoaidoituongKcb = item.IdLoaidoituongKcb;
if (v_IdLoaidoituongKcb.ToString() == "0" && GiaDV > 0)//Nếu là đối tượng BHYT
{
GiaPhuThu = GiaDV - GiaBHYT > 0 ? GiaDV - GiaBHYT : 0;
Update _update = new Update(QheDoituongThuoc.Schema).Set(QheDoituongThuoc.DonGiaColumn).EqualTo(GiaBHYT);
if (PropertyLib._QheGiaThuocProperties.TudongDieuChinhGiaPTTT)
_update.Set(QheDoituongThuoc.PhuthuTraituyenColumn).EqualTo(GiaPhuThu);
_update.Where(QheDoituongThuoc.IdLoaidoituongKcbColumn).IsEqualTo(0).And(QheDoituongThuoc.IdThuocColumn).IsEqualTo(idThuoc)
.And(QheDoituongThuoc.MaKhoaThuchienColumn).IsNotEqualTo(KTH)
.Execute();
}
}
}
}
Utility.SetMsg(lblMsg, "Bạn thực hiện cập nhập giá thành công",false);
}
catch (Exception exception)
{
Utility.SetMsg(lblMsg, "Lỗi trong quá trình cập nhập thông tin", false);
}
}
/// <summary>
/// Builds the update SQL.
/// </summary>
/// <returns></returns>
protected void UpdateRecord(string primaryKeyValue)
{
Update qryUpdate = new Update(TableSchema);
foreach(TableSchema.TableColumn col in TableSchema.Columns)
{
if(col.DataType != DbType.Binary && Utility.IsWritableColumn(col))
{
Control ctrl = FindControl(col.IsPrimaryKey ? PK_ID + col.ColumnName : col.ColumnName);
if(ctrl != null)
{
object oVal = Utility.GetDefaultControlValue(col, ctrl, false, true);
oVal = TransformBooleanAndDateValues(oVal, col.DataType);
qryUpdate.Set(col).EqualTo(oVal);
}
}
}
qryUpdate.Where(TableSchema.PrimaryKey).IsEqualTo(primaryKeyValue);
qryUpdate.Execute();
}
/// <summary>
/// deprecated version
/// </summary>
/// <param name="containerid"></param>
/// <param name="vesselid"></param>
/// <returns></returns>
protected bool change_vessel_deprecated(int containerid, int vesselid)
{
bool _changed = false;
DateTime _currentdate = DateTime.Now;
//for testing
containerid = 2332;
try
{
//for testing q1 ********
//SqlQuery _s1 = new Select(DAL.Logistics.Tables.OrderTable);
//_s1.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn);
//_s1.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn);
//_s1.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid);
//DataTable _dt1 = _s1.ExecuteDataSet().Tables[0];
string[] _cols1 = { "OrderTable.OrderID", "OrderTable.VesselID", "OrderTable.VesselLastUpdated" };
DataTable _dt = new Select(_cols1).From(DAL.Logistics.Tables.OrderTable)
.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn)
.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteDataSet().Tables[0];
if (_dt.Rows.Count > 0)
{
for (int _ix = 0; _ix < _dt.Rows.Count; _ix++)
{
int _id = wwi_func.vint(_dt.Rows[_ix]["OrderID"].ToString());
//update
OrderTable _tb = new OrderTable(_id);
_tb.VesselID = vesselid;
_tb.VesselLastUpdated = _currentdate;
_tb.Save();
}
}
//alternative method?
//IList<int> _ids = new Select().From(DAL.Logistics.Tables.OrderTable)
//.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn)
//.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
//.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteTypedList<int>();
//
//IList<int> _q1 = new SubSonic.Update(DAL.Logistics.Tables.OrderTable)
// .Set(OrderTable.Columns.VesselID).EqualTo(vesselid)
// .Set(OrderTable.Columns.VesselLastUpdated).EqualTo(_currentdate)
// .Where(OrderTable.Columns.OrderID).In(_ids).ExecuteTypedList<int>();
//for testing q2 *******
//Update _s2 = new Update(DAL.Logistics.Tables.OrderTable);
//_s2.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn);
//_s2.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn);
//_s2.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.OrderTable.VesselIDColumn);
//_s2.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn);
//_s2.InnerJoin(DAL.Logistics.VoyageETASubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn);
//_s2.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid);
//DataTable _dt2 = _s2.ExecuteDataSet().Tables[0];
//**********************
//can't use an update query here as there will likely be multiple orders to update and you will get 'multi-part identifier can't be bound'
//get data with reader as we need the ETS and ETA values
string[] _cols2 = { "OrderTable.OrderID", "VoyageETSSubTable.ETS", "VoyageETASubTable.ETA" };
_dt = new Select(_cols2).From(DAL.Logistics.Tables.OrderTable)
.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn)
.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.OrderTable.VesselIDColumn)
.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn)
.InnerJoin(DAL.Logistics.VoyageETASubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn)
.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteDataSet().Tables[0];
for (int _ix = 0; _ix < _dt.Rows.Count; _ix++)
{
int _id = wwi_func.vint(_dt.Rows[_ix]["OrderID"].ToString());
DateTime _ets = wwi_func.vdatetime(_dt.Rows[_ix]["Ets"].ToString());
DateTime _eta = wwi_func.vdatetime(_dt.Rows[_ix]["Eta"].ToString());
//update
OrderTable _tb = new OrderTable(_id);
_tb.Ets = _ets;
_tb.Eta = _eta;
_tb.Save();
}
//can't do this as causes error multi-part identifier can't be bound
//Update _q2 = new Update(DAL.Logistics.Tables.OrderTable);
// _q2.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn);
// _q2.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn);
// _q2.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.OrderTable.VesselIDColumn);
// _q2.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn);
// _q2.InnerJoin(DAL.Logistics.VoyageETASubTable .VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn);
// _q2.Set(DAL.Logistics.OrderTable.EtsColumn).EqualTo(DAL.Logistics.VoyageETSSubTable.EtsColumn);
// _q2.Set(DAL.Logistics.OrderTable.EtaColumn).EqualTo(DAL.Logistics.VoyageETASubTable.EtaColumn);
// _q2.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid);
//_test = _q2.ToString();
//_q2.Execute();
//for testing q3 *******
//SqlQuery _s3 = new Select(DAL.Logistics.Tables.ContainerTable);
//_s3.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid);
//DataTable _dt3 = _s3.ExecuteDataSet().Tables[0];
//**********************
Update _q3 = new Update(DAL.Logistics.Tables.ContainerTable);
_q3.Set(DAL.Logistics.ContainerTable.VoyageIDColumn).EqualTo(vesselid);
_q3.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid);
//_test = _q3.ToString();
_q3.Execute();
_changed = true;
}
catch (Exception ex)
{
string _er = ex.Message.ToString();
this.dxlblErr.Text = _er;
this.dxpnlErr.Visible = true;
}
return _changed;
}
//end package type
#endregion
#region move container and orders
/// <summary>
/// move container to a different vessel
/// 1. update Order table set VesselID = 'new vessel id', VesselLastUpdated = current date where containerid = N .Equivalent to access "ContainerVesselUpdateVesselQuery"
/// 2. update Order table set ETS = VoyageETSSubTable.ETS, ETA=VoyageETSSubTable.ETA where containerid = N .Equivalent to access "ContainerVesselUpdateDatesQuery"
/// 3. update Container table set VoyageID = 'new voyage id' where containerid = N .Equivalent to access ""ContainerVesselUpdateContainerQuery"
/// </summary>
/// <param name="containerid"></param>
protected bool change_vessel(int containerid, int vesselid)
{
bool _changed = false;
DateTime _currentdate = DateTime.Now;
//for testing
//containerid = 2332;
using (SharedDbConnectionScope _sc = new SharedDbConnectionScope())
{
using (System.Transactions.TransactionScope _ts = new System.Transactions.TransactionScope())
{
try
{
//for testing q1
//string _q1 = "SELECT o.OrderID, VesselId, VesselLastUpdated FROM OrderTable as o INNER JOIN " +
//"ContainerTable AS c INNER JOIN " +
//"ContainerSubTable AS s ON c.ContainerID = s.ContainerID ON o.OrderNumber = s.OrderNumber WHERE (c.ContainerID = 2332);";
//object[] _p1 = { containerid };
//int _result = new SubSonic.CodingHorror().ExecuteScalar<int>(_q1, _p1);
OrderTableCollection _q2 = new Select().From(DAL.Logistics.Tables.OrderTable)
.InnerJoin(DAL.Logistics.ContainerSubTable.OrderNumberColumn, DAL.Logistics.OrderTable.OrderNumberColumn)
.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteAsCollection<OrderTableCollection>();
for (int _ix = 0; _ix < _q2.Count; _ix++)
{
_q2[_ix].VesselID = vesselid;
_q2[_ix].VesselLastUpdated = _currentdate;
}
_q2.SaveAll();
//does not work because the triggers on the ordertable cause errors
//Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression
//1st statement paramatised and using subsonic.codinghorror, table aliases prevent 'multi-part identifier can't be bound' tsql error
//string _q1 = "UPDATE OrderTable " +
// "SET VesselID = @vesselid, VesselLastUpdated = @lastupdated " +
// "FROM OrderTable INNER JOIN " +
// "ContainerTable AS c INNER JOIN " +
// "ContainerSubTable AS s ON c.ContainerID = s.ContainerID ON OrderTable.OrderNumber = s.OrderNumber " +
// "WHERE (c.ContainerID = @containerid);";
//
//object[] _p1 = { vesselid, _currentdate, containerid };
//int _result = new SubSonic.CodingHorror().ExecuteScalar<int>(_q1, _p1);
//for testing q2
//SELECT c.ContainerID, ets.ets, eta.eta, OrderTable.OrderID FROM ContainerTable AS c INNER JOIN
//ContainerSubTable AS s ON c.ContainerID = s.ContainerID INNER JOIN
//VoyageTable AS v INNER JOIN VoyageETASubTable AS eta ON v.VoyageID = eta.VoyageID
//INNER JOIN VoyageETSSubTable AS ets ON v.VoyageID = ets.VoyageID INNER JOIN
//OrderTable ON v.VoyageID = OrderTable.VesselID ON s.OrderNumber = OrderTable.OrderNumber
//WHERE (c.ContainerID = @containerid);
//object[] _p2 = { containerid };
//_result = new SubSonic.CodingHorror().ExecuteScalar<int>(_q2, _p2);
//q2
string[] _cols2 = { "OrderTable.OrderID", "VoyageETSSubTable.ETS", "VoyageETASubTable.ETA" };
DataTable _dt = new Select(_cols2).From(DAL.Logistics.Tables.OrderTable)
.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn)
.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.OrderTable.VesselIDColumn)
.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn)
.InnerJoin(DAL.Logistics.VoyageETASubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn)
.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteDataSet().Tables[0];
if (_dt.Rows.Count > 0)
{
OrderTableCollection _ot = new OrderTableCollection();
for (int _ix = 0; _ix < _dt.Rows.Count; _ix++)
{
int _id = wwi_func.vint(_dt.Rows[_ix]["OrderID"].ToString());
DateTime _ets = wwi_func.vdatetime(_dt.Rows[_ix]["Ets"].ToString());
DateTime _eta = wwi_func.vdatetime(_dt.Rows[_ix]["Eta"].ToString());
//update
OrderTable _tb = new OrderTable(_id);
_tb.Ets = _ets;
_tb.Eta = _eta;
_ot.Add(_tb);
}
_ot.SaveAll();
}
//does not work because the triggers on the ordertable cause errors
//2nd statement paramatised and using subsonic.codinghorror, table aliases prevent 'multi-part identifier can't be bound' tsql error
//string _q2 = "UPDATE OrderTable " +
// "SET ETS = ets.ETS, ETA = eta.ETA " +
// "FROM ContainerTable AS c INNER JOIN " +
// "ContainerSubTable AS s ON c.ContainerID = s.ContainerID INNER JOIN " +
// "VoyageTable AS v INNER JOIN " +
// "VoyageETASubTable AS eta ON v.VoyageID = eta.VoyageID INNER JOIN " +
// "VoyageETSSubTable AS ets ON v.VoyageID = ets.VoyageID INNER JOIN " +
// "OrderTable ON v.VoyageID = OrderTable.VesselID ON s.OrderNumber = OrderTable.OrderNumber " +
// "WHERE (c.ContainerID = @containerid);";
//object[] _p2 = { containerid };
//_result = new SubSonic.CodingHorror().ExecuteScalar<int>(_q2, _p2);
//3rd statement
Update _q3 = new Update(DAL.Logistics.Tables.ContainerTable);
_q3.Set(DAL.Logistics.ContainerTable.VoyageIDColumn).EqualTo(vesselid);
_q3.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid);
//_test = _q3.ToString();
_q3.Execute();
//commit transaction
_ts.Complete();
_changed = true;
}
catch (Exception ex)
{
string _er = ex.Message.ToString();
this.dxlblErr.Text = _er;
this.dxpnlErr.ClientVisible = true;
}
}
}
return _changed;
}
//end package type
#endregion
#region mark loaded
/// <summary>
/// mark container as loaded on board
/// can't paramatise and use subsonic.codinghorror with table aliases prevent 'multi-part identifier can't be bound' tsql error and
/// can't use an update query if multiple records are to be updated as it crashes the triggers on OrderTable:
/// Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression
/// 1. update container table set loadedonboard = -1, updated = current date where containerid = N .Equivalent to access "UpdateContainerLoadedQuery"
/// 2. update all in order table set loadedonboard = -1 where containerid = N .Equivalent to access "UpdateOrderLoadedOnBoardQuery"
/// 3. update deliverysubtable table set currentstatusid=1 where currentstatusid=12, statusdate = current date, currentstatusdate = [VoyageETSSubTable.ETS]
/// where containerid = N .Equivalent to access "UpdateDeliveryStatusOnBoardQuery"
/// </summary>
/// <param name="containerid">int</param>
protected bool mark_loaded_on_board(int containerid)
{
//method using subsonic.codinghorror for update statements 2 and 3 as subsonic does not handle aliasing well
//and we need to get around the multi-part identifier can't be bound problem
//int _onboard = 0;
bool _onboard = true;
DateTime _currentdate = DateTime.Now;
int _result = 0;
int _newstatusid = 1;
int _currentstatusid = 12;
//containerid = 125;
//containerid 7 or 115 or 16316 or 125 was good for testing
using (SharedDbConnectionScope _sc = new SharedDbConnectionScope())
{
using (System.Transactions.TransactionScope _ts = new System.Transactions.TransactionScope())
{
using (SqlConnection _cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["PublishipSQLConnectionString"].ToString()))
{
try
{
//1. update container table
Update _q1 = new Update(DAL.Logistics.Tables.ContainerTable);
_q1.Set(DAL.Logistics.ContainerTable.LoadedOnBoardColumn).EqualTo(_onboard);
_q1.Set(DAL.Logistics.ContainerTable.UpdatedColumn).EqualTo(_currentdate);
_q1.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid);
//string _test = _q1.ToString();
_result = _q1.Execute();
//end
//2. update ordertable
OrderTableCollection _q2 = new Select().From(DAL.Logistics.Tables.OrderTable)
.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn)
.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteAsCollection<OrderTableCollection>();
for (int _ix = 0; _ix < _q2.Count; _ix++)
{
_q2[_ix].ShippedOnBoard = _onboard;
}
_q2.SaveAll();
//end
//3. get data we need ets from VoyageEtsSubtable
//used containerid 125 for testing
//return a datatable, can't use a reader here as you would get an error when you try and process the deliverysubtable
DeliverySubTableCollection _q3 = new DeliverySubTableCollection();
string[] _cols = { "DeliverySubTable.DeliveryID", "VoyageETSSubTable.ETS" };
DataTable _dt = new Select(_cols).From(DAL.Logistics.Tables.DeliverySubTable)
.InnerJoin(DAL.Logistics.ContainerSubTable.OrderNumberColumn, DAL.Logistics.DeliverySubTable.OrderNumberColumn)
.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
.InnerJoin(DAL.Logistics.OrderTable.OrderIDColumn, DAL.Logistics.ContainerSubTable.OrderIDColumn)
.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.ContainerTable.VoyageIDColumn)
.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn)
.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid)
.And(DAL.Logistics.DeliverySubTable.CurrentStatusIDColumn).IsEqualTo(_currentstatusid).ExecuteDataSet().Tables[0];
if (_dt.Rows.Count > 0)
{
for (int _ix = 0; _ix < _dt.Rows.Count; _ix++)
{
int _id = wwi_func.vint(_dt.Rows[_ix]["DeliveryID"].ToString());
DateTime _ets = wwi_func.vdatetime(_dt.Rows[_ix]["ETS"].ToString());
//update
DeliverySubTable _tb = new DeliverySubTable(_id);
_tb.CurrentStatusID = _newstatusid;
_tb.StatusDate = _currentdate;
_tb.CurrentStatusDate = _ets;
_q3.Add(_tb);
}
_q3.SaveAll();
}
//end
}
catch (Exception ex)
{
_onboard = false;
string _er = ex.Message.ToString();
this.dxlblErr.Text = _er;
this.dxpnlErr.ClientVisible = true;
}//end try/catch
}//end using SqlConnection
}//end using TransactionScope
}//end using SharedDbConnectionScope
return _onboard;
}
/// <summary>
/// deprecated code can't get this to work
/// </summary>
/// <param name="containerid"></param>
/// <returns></returns>
protected bool mark_loaded_on_board_deprecated(int containerid)
{
bool _onboard = true;
DateTime _currentdate = DateTime.Now;
int _result = 0;
int _statusid = 12;
//containerid 7 or 16316 was good for testing
using (SharedDbConnectionScope _sc = new SharedDbConnectionScope())
{
using (System.Transactions.TransactionScope _ts = new System.Transactions.TransactionScope())
{
try
{
//deprecated code
//SubSonic.Query _qry1 = new SubSonic.Query(DAL.Logistics.Tables.ContainerTable);
//_qry1.QueryType = QueryType.Update;
//_qry1.AddUpdateSetting("LoadedOnBoard", _onboard);
//_qry1.AddUpdateSetting("Updated", DateTime.Now.ToShortDateString());
//_qry1.WHERE("ContainerID", Comparison.Equals, containerid);
//*************
//for testing q1 ********
//SqlQuery _s1 = new Select(DAL.Logistics.Tables.ContainerTable);
//_s1.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid);
//DataTable _dt1 = _s1.ExecuteDataSet().Tables[0];
Update _q1 = new Update(DAL.Logistics.Tables.ContainerTable);
_q1.Set(DAL.Logistics.ContainerTable.LoadedOnBoardColumn).EqualTo(_onboard);
_q1.Set(DAL.Logistics.ContainerTable.UpdatedColumn).EqualTo(_currentdate);
_q1.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid);
//string _test = _q1.ToString();
_result = _q1.Execute();
//2. get associated orderid's
//OrderTableCollection _q2 = new Select().From(DAL.Logistics.Tables.OrderTable)
//.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn)
//.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
//.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteAsCollection<OrderTableCollection>();
//update records
// for (int _ix = 0; _ix < _q2.Count; _ix++)
// {
// _q2[_ix].ShippedOnBoard = _onboard;
//
// }
//
// _q2.SaveAll();
//2. get associated orderid's as typed list
IList<int> _s2 = new Select("OrderID").From(DAL.Logistics.Tables.OrderTable)
.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn)
.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid).ExecuteTypedList<int>();
for (int _ix = 0; _ix < _s2.Count; _ix++)
{
//Update _q2 = new Update(DAL.Logistics.Tables.OrderTable);
//_q2.Set(DAL.Logistics.OrderTable.ShippedOnBoardColumn).EqualTo(_onboard);
//_q2.Where(DAL.Logistics.OrderTable.OrderIDColumn).InValues.
}
//can't use IN for update as container id is a nullable coumn
//Update _q2 = new Update(DAL.Logistics.Tables.OrderTable);
//_q2.Set(DAL.Logistics.OrderTable.ShippedOnBoardColumn).EqualTo(_onboard);
//_q2.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).In(_s2);
//string _test = _q2.ToString();
//_q2.Execute();
//can't use an update query here as there will likely be multiple orders to update and you will get 'multi-part identifier can't be bound'
//Update _q2 = new Update(DAL.Logistics.Tables.OrderTable);
// _q2.Set(DAL.Logistics.OrderTable.ShippedOnBoardColumn).EqualTo(_onboard);
// _q2.From(DAL.Logistics.Tables.OrderTable);
// _q2.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn);
// _q2.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid); //.Execute();
// string _test = _q2.ToString();
//**********************
string[] _cols = { "DeliverySubTable.DeliveryID", "VoyageETSSubTable.ETS" };
//SqlQuery _s3 = new Select(_cols).From(DAL.Logistics.Tables.DeliverySubTable)
//.InnerJoin(DAL.Logistics.ContainerSubTable.OrderNumberColumn, DAL.Logistics.DeliverySubTable.OrderNumberColumn)
//.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
//.InnerJoin(DAL.Logistics.OrderTable.OrderIDColumn, DAL.Logistics.ContainerSubTable.OrderIDColumn)
//.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.ContainerTable.VoyageIDColumn)
//.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn)
//.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid)
//.And(DAL.Logistics.DeliverySubTable.CurrentStatusIDColumn).IsEqualTo(1);
//string _test = _s3.ToString();
///q3. get data we need ets from VoyageEtsSubtable so return a datareader and then build collection
IDataReader _rd = new Select(_cols).From(DAL.Logistics.Tables.DeliverySubTable)
.InnerJoin(DAL.Logistics.ContainerSubTable.OrderNumberColumn, DAL.Logistics.DeliverySubTable.OrderNumberColumn)
.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn)
.InnerJoin(DAL.Logistics.OrderTable.OrderIDColumn, DAL.Logistics.ContainerSubTable.OrderIDColumn)
.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.ContainerTable.VoyageIDColumn)
.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn)
.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid)
.And(DAL.Logistics.DeliverySubTable.CurrentStatusIDColumn).IsEqualTo(1).ExecuteReader();
DeliverySubTableCollection _q3 = new DeliverySubTableCollection();
_result = 0;
while (_rd.Read())
{
int _id = wwi_func.vint(_rd["DeliveryID"].ToString());
DateTime _dt = wwi_func.vdatetime(_rd["Ets"].ToString());
//update
DeliverySubTable _tb = new DeliverySubTable(_id);
_tb.CurrentStatusID = _statusid;
_tb.StatusDate = _currentdate;
_tb.CurrentStatusDate = _dt;
_q3.Add(_tb);
_result++;
}
if (_result > 0)
{
_q3.BatchSave();
}
//can't use a query here as there will likely be multiple orders to update and you will get 'multi-part identifier can't be bound'
//Update _q3 = new Update(DAL.Logistics.Tables.DeliverySubTable);
//_q3.InnerJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn);
// _q3.InnerJoin(DAL.Logistics.ContainerTable.ContainerIDColumn, DAL.Logistics.ContainerSubTable.ContainerIDColumn);
// _q3.InnerJoin(DAL.Logistics.DeliverySubTable.OrderNumberColumn, DAL.Logistics.ContainerSubTable.OrderNumberColumn);
// _q3.InnerJoin(DAL.Logistics.VoyageTable.VoyageIDColumn, DAL.Logistics.ContainerTable.VoyageIDColumn);
// _q3.InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn);
// _q3.Set(DAL.Logistics.DeliverySubTable.CurrentStatusIDColumn).EqualTo(12);
// _q3.Set(DAL.Logistics.DeliverySubTable.StatusDateColumn).EqualTo(_currentdate);
// _q3.Set(DAL.Logistics.DeliverySubTable.CurrentStatusDateColumn).EqualTo(DAL.Logistics.VoyageETSSubTable.EtsColumn);
// _q3.Where(DAL.Logistics.ContainerTable.ContainerIDColumn).IsEqualTo(containerid);
// _q3.And(DAL.Logistics.DeliverySubTable.CurrentStatusIDColumn).IsEqualTo(1);
//_test = _q3.ToString();
//_result = _q3.Execute();
//**********************
//commit transaction
_ts.Complete();
_onboard = true;
}
catch (Exception ex)
{
string _er = ex.Message.ToString();
this.dxlblErr.Text = _er;
this.dxpnlErr.ClientVisible = true;
}
}
}
return _onboard;
}
//end row commands
/// <summary>
/// flag quote as hidden from client vieww
/// </summary>
/// <param name="quoteid">Int32 unique id of quote</param>
protected bool hide_quote(Int32 quoteid)
{
//save log id to price table
int recordsaffected = 0;
SubSonic.Update upd1 = new SubSonic.Update(DAL.Pricer.Schemas.PriceValue);
recordsaffected = upd1.Set("client_visible").EqualTo(false)
.Where("quote_id").IsEqualTo(quoteid)
.Execute();
if (recordsaffected > 0) return true;
return false;
}
//end default view
/// <summary>
/// update pricer with linked order id and order table with the quote id
/// </summary>
/// <param name="orderid">int32 orderid from grid</param>
/// <returns></returns>
protected bool save_to_quote(Int32 orderid, Int32 orderno, Int32 quoteid)
{
bool _result = false;
int recordsaffected = 0;
//save orderid to price values
if (orderid > 0 && quoteid > 0)
{
//append to audit log
UserClass _thisuser = (UserClass)Session["user"];
DAL.Pricer.PriceOrderLog _oq = new DAL.Pricer.PriceOrderLog();
_oq.CompanyId = _thisuser.CompanyId;
_oq.UserId = _thisuser.UserId;
_oq.QuoteId = quoteid;
_oq.LogDate = DateTime.Now;
_oq.OrderId = orderid;
_oq.OrderNo = orderno;
_oq.Save();
//get log id
Int32 _newid = (Int32)_oq.GetPrimaryKeyValue();
//save log id to price table
SubSonic.Update upd1 = new SubSonic.Update(DAL.Pricer.Schemas.PriceValue);
recordsaffected = upd1.Set("po_log_id").EqualTo(_newid)
.Where("quote_id").IsEqualTo(quoteid)
.Execute();
//save quote id to order table
SubSonic.Update upd2 = new SubSonic.Update(DAL.Logistics.Schemas.OrderTable);
recordsaffected = upd2.Set("quote_id").EqualTo(quoteid)
.Where("OrderID").IsEqualTo(orderid)
.Execute();
if (recordsaffected > 0) { _result = true; }
}
return _result;
}
/// <summary>
/// if this quote linked to an order use this to clear the order number from price_values table
/// </summary>
/// <param name="quoteid">Int32 unique id of quote</param>
protected bool remove_pod(Int32 quoteid)
{
//save log id to price table
int recordsaffected = 0;
SubSonic.Update upd1 = new SubSonic.Update(DAL.Pricer.Schemas.PriceValue);
recordsaffected = upd1.Set("po_log_id").EqualTo(0)
.Where("quote_id").IsEqualTo(quoteid)
.Execute();
if(recordsaffected >0) return true;
return false;
}