public AscmDeliveryNotifyMain Get(int id) { AscmDeliveryNotifyMain ascmDeliveryNotify = null; try { ascmDeliveryNotify = YnDaoHelper.GetInstance().nHibernateHelper.Get <AscmDeliveryNotifyMain>(id); } catch (Exception ex) { YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Get AscmDeliveryNotifyMain)", ex); throw ex; } return(ascmDeliveryNotify); }
public void Update(AscmDeliveryNotifyMain ascmDeliveryNotify) { using (ITransaction tx = YnDaoHelper.GetInstance().nHibernateHelper.GetCurrentSession().BeginTransaction()) { try { YnDaoHelper.GetInstance().nHibernateHelper.Update <AscmDeliveryNotifyMain>(ascmDeliveryNotify); tx.Commit();//正确执行提交 } catch (Exception ex) { tx.Rollback();//回滚 YnBaseClass2.Helper.LogHelper.GetLog().Error("修改失败(Update AscmDeliveryNotifyMain)", ex); throw ex; } } }
public List <AscmDeliveryNotifyMain> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther) { List <AscmDeliveryNotifyMain> list = new List <AscmDeliveryNotifyMain>(); try { string sort = " order by id "; if (!string.IsNullOrEmpty(sortName)) { sort = " order by " + sortName.Trim() + " "; if (!string.IsNullOrEmpty(sortOrder)) { sort += sortOrder.Trim(); } } sort = ""; //不能在里面加order ,否则效率非常低 //string sql = "from AscmDeliveryNotifyMain ";//where id>36614045 //string detailCount = "select count(*) from AscmDeliveryNotifyDetail where mainId= a.id"; //string containerBindNumber = "select t1.quantity from AscmContainerDelivery t1, AscmDeliveryOrderMain t2, AscmDeliveryOrderDetail t3, AscmDeliveryNotifyDetail t4 where t3.notifyDetailId = t4.id and t2.id = t3.mainId and t1.deliveryOrderBatchId=t2.batchId and t4.mainId = a.id"; //string containerBindNumber = "select sum(t1.quantity) from AscmContainerDelivery t1, (select distinct t2.batchId, t4.mainId from AscmDeliveryOrderMain t2, AscmDeliveryOrderDetail t3, AscmDeliveryNotifyDetail t4 where t3.notifyDetailId = t4.id and t2.id = t3.mainId) t where t1.deliveryOrderBatchId=t.batchId and t.mainId = a.id"; ////string containerBindNumber = "select sum(t1.quantity) from AscmContainerDelivery t1 where t1.deliveryOrderBatchId in " // + "(select distinct t2.batchId from AscmDeliveryOrderMain t2, AscmDeliveryOrderDetail t3, AscmDeliveryNotifyDetail t4 " // + "where t3.notifyDetailId = t4.id and t2.id = t3.mainId and t4.mainId = a.id)"; //string receiveTime = "select t.acceptTime from AscmDeliBatSumMain t, AscmDeliBatSumDetail t1, AscmDeliveryOrderMain t2, AscmDeliveryOrderDetail t3, AscmDeliveryNotifyDetail t4 where t.id = t1.mainId and t1.batchId = t2.batchId and t2.id = t3.mainId and t3.notifyDetailId = t4.id and t4.mainId = a.id"; //string totalNumber = "select sum(deliveryQuantity) from AscmDeliveryOrderDetail where mainId= a.id"; //string sql1 = "select new AscmDeliveryNotifyMain(a,(" + detailCount + "),(" + containerBindNumber + "),(" + receiveTime + ")) from AscmDeliveryNotifyMain a "; //string sql1 = "select new AscmDeliveryNotifyMain(a,(" + detailCount + ")) from AscmDeliveryNotifyMain a left j "; //string sql = "select count(a.*) from Ascm_Delivery_Notify_Main a left join Ascm_Delivery_Notify_Detail b on a.id = b.mainId left join Ascm_Delivery_Order_Detail c on b.id = c.notifydetailid left join Ascm_Delivery_Order_Main d on c.mainid = d.id left join ascm_deli_bat_order_link e on d.batchid = e.batchid "; //string sql = "select count(1) from (select distinct a.id from Ascm_Delivery_Notify_Main a left join Ascm_Delivery_Notify_Detail b on a.id = b.mainId left join Ascm_Delivery_Order_Detail c on b.id = c.notifydetailid left join Ascm_Delivery_Order_Main d on c.mainid = d.id left join ascm_deli_bat_order_link e on d.batchid = e.batchid left join ascm_deli_bat_sum_detail f on e.batchid = f.batchid left join ascm_deli_bat_sum_main g on f.mainid = g.id "; //保存ERP上获取的总接收数 //List<AscmDeliveryNotifyMain> listAscmDeliveryNotifyMain = AscmDeliveryNotifyMainService.GetInstance().GetList(null, "", "", "", whereOther); //if (listAscmDeliveryNotifyMain != null && listAscmDeliveryNotifyMain.Count > 0) // AscmDeliveryNotifyMainService.GetInstance().Update(listAscmDeliveryNotifyMain); string sql = "select count(1) from (select distinct a.id from Ascm_Delivery_Notify_Main a "; string sql1 = "select distinct a.id ids,a.*,e.batchid,g.accepttime from Ascm_Delivery_Notify_Main a left join Ascm_Delivery_Notify_Detail b on a.id = b.mainId left join Ascm_Delivery_Order_Detail c on b.id = c.notifydetailid left join Ascm_Delivery_Order_Main d on c.mainid = d.id left join ascm_deli_bat_order_link e on d.batchid = e.batchid left join ascm_deli_bat_sum_detail f on e.batchid = f.batchid left join ascm_deli_bat_sum_main g on f.mainid = g.id "; string sql_other = "select t.*, rownum rn from ({0}) t"; string where = "", whereQueryWord = ""; if (!string.IsNullOrEmpty(queryWord)) { whereQueryWord = " (name like '%" + queryWord.Trim() + "%' or description like '%" + queryWord.Trim() + "%')"; } where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord); where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther); if (where.IndexOf("a.totalReceiveQuantity") > -1) { where = where.Replace("a.totalReceiveQuantity", "nvl(a.totalReceiveQuantity,0)"); } if (!string.IsNullOrEmpty(where)) { sql += " where " + where + ")"; sql1 += " where " + where; sql1 = string.Format(sql_other, sql1); } if (whereOther == "") { sql1 = "select * from (" + sql1 + " where rownum <=" + ynPage.currentPage * ynPage.pageSize + ") where rn >" + (ynPage.currentPage - 1) * ynPage.pageSize; } else { sql1 = "select * from (" + sql1 + " where rownum <=" + ynPage.currentPage * ynPage.pageSize + ") where rn >" + (ynPage.currentPage - 1) * ynPage.pageSize; } ArrayList arrayList = (ArrayList)YnDaoHelper.GetInstance().nHibernateHelper.ExecuteReader(sql); for (int i = 0; i < arrayList.Count; i++) { Object[] o = (Object[])arrayList[i]; ynPage.recordCount = int.Parse(o[0].ToString()); } //IList<AscmDeliveryNotifyMain> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmDeliveryNotifyMain>(sql + sort); ArrayList arrayList1 = (ArrayList)YnDaoHelper.GetInstance().nHibernateHelper.ExecuteReader(sql1); for (int i = 0; i < arrayList1.Count; i++) { Object[] o = (Object[])arrayList1[i]; AscmDeliveryNotifyMain a = new AscmDeliveryNotifyMain(); int j = 1; a.id = int.Parse(o[j + 0].ToString()); a.organizationId = int.Parse(o[j + 1].ToString() == "" ? "0" : o[j + 1].ToString()); a.createUser = o[j + 2].ToString(); a.createTime = o[j + 3].ToString(); a.modifyUser = o[j + 4].ToString(); a.modifyTime = o[j + 5].ToString(); a.docNumber = o[j + 6].ToString(); a.supplierId = int.Parse(o[j + 7].ToString() == "" ? "0" : o[j + 7].ToString()); a.warehouseId = o[j + 8].ToString(); a.materialId = int.Parse(o[j + 9].ToString() == "" ? "0" : o[j + 9].ToString()); a.releasedQuantity = int.Parse(o[j + 10].ToString() == "" ? "0" : o[j + 10].ToString()); a.promisedQuantity = int.Parse(o[j + 11].ToString() == "" ? "0" : o[j + 11].ToString()); a.deliveryQuantity = int.Parse(o[j + 12].ToString() == "" ? "0" : o[j + 12].ToString()); a.cancelQuantity = int.Parse(o[j + 13].ToString() == "" ? "0" : o[j + 13].ToString()); a.status = o[j + 14].ToString(); a.releasedTime = o[j + 15].ToString(); a.needTime = o[j + 16].ToString(); a.promisedTime = o[j + 17].ToString(); a.confirmTime = o[j + 18].ToString(); a.comments = o[j + 19].ToString(); a.purchasingAgentId = int.Parse(o[j + 20].ToString() == "" ? "0" : o[j + 20].ToString()); a.wipEntityId = int.Parse(o[j + 21].ToString() == "" ? "0" : o[j + 21].ToString()); a.departmentId = int.Parse(o[j + 22].ToString() == "" ? "0" : o[j + 22].ToString()); a.locationId = int.Parse(o[j + 23].ToString() == "" ? "0" : o[j + 23].ToString()); a.fdSourceType = o[j + 24].ToString(); a.appointmentStartTime = o[j + 25].ToString(); a.appointmentEndTime = o[j + 26].ToString(); a.totalReceiveQuantity = int.Parse(o[j + 28].ToString() == "" ? "0" : o[j + 28].ToString()); string sql2 = "select sum(a.quantity) from ascm_container_delivery a where a.DELIVERYORDERBATCHID = '" + o[j + 29].ToString() + "'"; ArrayList arrayList2 = (ArrayList)YnDaoHelper.GetInstance().nHibernateHelper.ExecuteReader(sql2); for (int i1 = 0; i1 < arrayList2.Count; i1++) { Object[] o1 = (Object[])arrayList2[i1]; a.containerBindQuantity = int.Parse(o1[0].ToString() == "" ? "0" : o1[0].ToString()); } a.receiveTime = o[j + 30].ToString(); list.Add(a); } //IList<AscmDeliveryNotifyMain> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmDeliveryNotifyMain>(sql1 + sort, sql, ynPage); //if (ilist != null) //{ //list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmDeliveryNotifyMain>(ilist); SetSupplier(list); SetMaterial(list); SetCreateUser(list); SetWipEntities(list); SetBomDepartments(list); SetHrLocations(list); SetLookupValues(list); getShipmentQuantities(list); //} } catch (Exception ex) { YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmDeliveryNotifyMain)", ex); throw ex; } return(list); }