//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);
    }
Exemple #2
0
    //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);
            }
        }
Exemple #4
0
 /// <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;
    }