public List<AscmJobLog> GetList(YnPage ynPage, string sortName, string sortOrder, string jobName, string queryWord)
{
List<AscmJobLog> list = null;
try
{
string sort = "";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmJobLog";
string where = "";
if (!string.IsNullOrEmpty(jobName))
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, " jobName=upper('" + jobName.Trim() + "')");
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
IList<AscmJobLog> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmJobLog>(sql + sort, sql, ynPage);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmJobLog>(ilist);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmJobLog)", ex);
throw ex;
}
return list;
}
public List<AscmMtlOnhandQuantitiesDetail> GetSumList(YnPage ynPage, int materialId)
{
List<AscmMtlOnhandQuantitiesDetail> list = null;
try
{
string hql = "select new AscmMtlOnhandQuantitiesDetail(inventoryItemId, subinventoryCode, sum(transactionQuantity)) from AscmMtlOnhandQuantitiesDetail where inventoryItemId = {0} group by inventoryItemId, subinventoryCode";
string sql = "select inventoryItemId, subinventoryCode, sum(transactionQuantity) from ASCM_MTL_ONHAND_QUANTITIES where inventoryItemId = {0} group by inventoryItemId, subinventoryCode";
if (materialId > 0)
{
hql = string.Format(hql, materialId.ToString());
sql = string.Format(sql, materialId.ToString());
}
object object1 = YnDaoHelper.GetInstance().nHibernateHelper.GetObjectBySQL("select count(*) from (" + sql + ")");
int count = 0;
int.TryParse(object1.ToString(), out count);
IList<AscmMtlOnhandQuantitiesDetail> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmMtlOnhandQuantitiesDetail>(hql, count, ynPage);
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmMtlOnhandQuantitiesDetail>(ilist);
SetMaterial(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("统计失败(Sum AscmMtlOnhandQuantitiesDetail)", ex);
throw ex;
}
return list;
}
public List<AscmMaterialItem> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther = "", bool isSetMaterialSubCategory = true)
{
List<AscmMaterialItem> list = null;
try
{
string sort = " order by docNumber ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmMaterialItem ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
queryWord = queryWord.Trim().Replace(" ", "%");
whereQueryWord = " (docNumber like '" + queryWord.Trim() + "%' or description like '" + queryWord.Trim() + "%')";
/*long _fValue = -1;
if (long.TryParse(queryWord, out _fValue))
{
whereQueryWord += "(docNumber like '" + queryWord + "%')";
}
else
{
whereQueryWord = " (description like '%" + queryWord.Trim() + "%')";
}*/
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
IList<AscmMaterialItem> ilist=null;
if(ynPage!=null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmMaterialItem>(sql + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmMaterialItem>(sql + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmMaterialItem>(ilist);
if (isSetMaterialSubCategory)
SetMaterialSubCategory(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmMaterialItem)", ex);
throw ex;
}
return list;
}
public List<AscmDeliveryOrderBatch> GetIncomingAcceptanceList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmDeliveryOrderBatch> list = null;
try
{
string sort = string.Empty;
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
sort = ""; //不能在里面加order ，否则效率非常低
string sql = "from AscmDeliveryOrderBatch";
string _materialId = "select materialId from AscmDeliveryOrderDetail where mainId in (select id from AscmDeliveryOrderMain where batchId =a.id) and rownum=1";
string detailCount = "select count(*) from AscmDeliveryOrderDetail where mainId in (select id from AscmDeliveryOrderMain where batchId =a.id)";
string totalNumber = "select sum(deliveryQuantity) from AscmDeliveryOrderDetail where mainId in (select id from AscmDeliveryOrderMain where batchId = a.id)";
string receivedQuantity = "select sum(receivedQuantity) from AscmDeliBatOrderLink where batchId=a.id";
string sql1 = "select new AscmDeliveryOrderBatch(a,(" + detailCount + "),(" + totalNumber + "),(" + _materialId + "),(" + receivedQuantity + ")) from AscmDeliveryOrderBatch a ";
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 (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
sql1 += " where " + where;
}
IList<AscmDeliveryOrderBatch> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmDeliveryOrderBatch>(sql1 + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmDeliveryOrderBatch>(sql1 + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmDeliveryOrderBatch>(ilist);
SetSupplier(list);
SetMaterial(list);
SetAssignWarelocation(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmDeliveryOrderBatch)", ex);
throw ex;
}
return list;
}
public List<AscmUserInfo> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, bool isSetDepartmentPosition = true, bool isLogisticsClassName = true)
{
List<AscmUserInfo> list = null;
try
{
string sort = " order by userId ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmUserInfo a";
string where = "", whereQueryWord = "", whereOrganization = " (organizationId=0 or organizationId is null) ";
if (!string.IsNullOrEmpty(queryWord))
whereQueryWord = " (userId like '%" + queryWord.Trim() + "%' or userName like '%" + queryWord.Trim() + "%' or description like '%" + queryWord.Trim() + "%')";
//if (!string.IsNullOrEmpty(organizationId))
// whereOrganization = " a.organizationId=" + organizationId;
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOrganization);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
IList<AscmUserInfo> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmUserInfo>(sql + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmUserInfo>(sql + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmUserInfo>(ilist);
List<YnFrame.Dal.Entities.YnUser> list1 = new List<YnFrame.Dal.Entities.YnUser>();
foreach (AscmUserInfo ascmUserInfo in list)
{
list1.Add((YnFrame.Dal.Entities.YnUser)ascmUserInfo);
}
//YnFrame.Services.YnUserService.GetInstance().SetRole(list1);
if (isSetDepartmentPosition)
YnFrame.Services.YnUserService.GetInstance().SetDepartmentPosition(list1);
if (isLogisticsClassName)
AscmUserInfoService.GetInstance().SetUserLogisticsClass(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find YnUser)", ex);
throw ex;
}
return list;
}
public List<AscmGetMaterialLog> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmGetMaterialLog> list = new List<AscmGetMaterialLog>();
try
{
string sort = "";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
else
{
sort = " order by id ";
}
string sql = " from AscmGetMaterialLog ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = "workerId like '%" + queryWord + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
if (!string.IsNullOrEmpty(sort))
sql += sort;
IList<AscmGetMaterialLog> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmGetMaterialLog>(sql, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmGetMaterialLog>(sql);
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmGetMaterialLog>(ilist);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Get AscmGetMaterialLog)", ex);
throw ex;
}
return list;
}
/// <summary>
/// 超期预警数据查询
/// 覃小华于2013/07/15修改
/// </summary>
/// <param name="ynPage"></param>
/// <param name="sortName"></param>
/// <param name="sortOrder"></param>
/// <param name="queryWord"></param>
/// <param name="whereOther"></param>
/// <param name="extendedTime"></param>
/// <returns></returns>
public List<Dal.SupplierPreparation.Entities.AscmContainer> Getlist(YnPage ynPage, string sortName, string sortOrder, string whereOther, string DateRequired)
{
List<Dal.SupplierPreparation.Entities.AscmContainer> list = null;
try
{
string strHql = @"select new AscmContainer(container,(suppli.name),(to_char(to_date(container.storeInTime,'yyyy-MM-dd HH24:mi:ss')+suppli.warnHours/24,'yyyy-MM-dd HH24:mi:ss')),(ceil(((to_date('" + System.DateTime.Now.ToString() + "','yyyy-MM-dd HH24:mi:ss')-suppli.warnHours/24)-to_date(container.storeInTime, 'yyyy-MM-dd HH24:mi:ss'))*24))) from AscmContainer container, AscmSupplier suppli where container.storeInTime is not null and container.supplierId=suppli.id and (suppli.warnHours is not null and suppli.warnHours!=0) and ceil(((to_date('" + System.DateTime.Now.ToString() + "','yyyy-MM-dd HH24:mi:ss')-suppli.warnHours/24)-to_date(container.storeInTime, 'yyyy-MM-dd HH24:mi:ss'))*24)>-25 and (container.place not in (select to_char(id) from AscmReadingHead where ip='0.0.0.0') or container.place is null)";
string strHql1 = "from AscmContainer container, AscmSupplier suppli where container.storeInTime is not null and container.supplierId=suppli.id and (suppli.warnHours is not null or suppli.warnHours!=0) and ceil(((to_date('" + System.DateTime.Now.ToString() + "','yyyy-MM-dd HH24:mi:ss')-suppli.warnHours/24)-to_date(container.storeInTime, 'yyyy-MM-dd HH24:mi:ss'))*24)>-25 and (container.place not in (select to_char(id) from AscmReadingHead where address= '容器监管中心' and ip='0.0.0.0') or container.place is null)";
if (!string.IsNullOrEmpty(DateRequired))
{
DateTime DatereadTime = new DateTime();
if (!string.IsNullOrEmpty(DateRequired) && DateTime.TryParse(DateRequired, out DatereadTime))
{
strHql = @"select new AscmContainer(container,(suppli.name),to_char(to_date(container.storeInTime,'yyyy-MM-dd HH24:mi:ss')+suppli.warnHours/24,'yyyy-MM-dd HH24:mi:ss'),(ceil(((to_date('" + DatereadTime.ToString() + "','yyyy-MM-dd HH24:mi:ss')-suppli.warnHours/24)-to_date(container.storeInTime, 'yyyy-MM-dd HH24:mi:ss'))*24))) from AscmContainer container, AscmSupplier suppli where container.storeInTime is not null and container.supplierId=suppli.id and (suppli.warnHours is not null and suppli.warnHours!=0) and ceil(((to_date('" + DatereadTime.ToString() + "','yyyy-MM-dd HH24:mi:ss')-suppli.warnHours/24)-to_date(container.storeInTime, 'yyyy-MM-dd HH24:mi:ss'))*24)>-25 and (container.place not in (select to_char(id) from AscmReadingHead where ip='0.0.0.0') or container.place is null)";
strHql1 = "from AscmContainer container, AscmSupplier suppli where container.storeInTime is not null and container.supplierId=suppli.id and (suppli.warnHours is not null and suppli.warnHours!=0) and ceil(((to_date('" + DatereadTime.ToString() + "','yyyy-MM-dd HH24:mi:ss')-suppli.warnHours/24)-to_date(container.storeInTime, 'yyyy-MM-dd HH24:mi:ss'))*24)>-25 and (container.place not in (select to_char(id) from AscmReadingHead where ip='0.0.0.0') or container.place is null)";
}
}
string sort = " order by store.id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string where = "";
//in (select id from AscmSupplier where warnHours is not null or warnHours!=0) and sn not in (SELECT containerId FROM AscmStoreInOut a WHERE readTime=(SELECT max(readTime) FROM AscmStoreInOut WHERE containerId=a.containerId) and direction='STOREOUT')";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
strHql += " and" + where;
strHql1 += " and" + where;
}
IList<Dal.SupplierPreparation.Entities.AscmContainer> ilist =
//YnDaoHelper.GetInstance().nHibernateHelper.Find<Dal.SupplierPreparation.Entities.AscmContainer>(strHql1);
YnDaoHelper.GetInstance().nHibernateHelper.Find<Dal.SupplierPreparation.Entities.AscmContainer>(strHql,
strHql1, ynPage);
if (ilist != null && ilist.Count>0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<Dal.SupplierPreparation.Entities.AscmContainer>(ilist);
SetAscmReadingHead(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWipEntities)", ex);
throw ex;
}
return list;
}
public List<AscmWipEntities> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmWipEntities> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmWipEntities";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
int index = queryWord.IndexOf('%');
if (index >= 0)
whereQueryWord = " (upper(name) like '" + queryWord.Trim().ToUpper() + "%')";
else
whereQueryWord = " (upper(name) like '%" + queryWord.Trim().ToUpper() + "%')";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
IList<AscmWipEntities> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWipEntities>(sql + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWipEntities>(sql + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmWipEntities>(ilist);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWipEntities)", ex);
throw ex;
}
return list;
}
public List<AscmSupplier> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord,string whereOther)
{
List<AscmSupplier> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmSupplier ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
queryWord = queryWord.Trim().Replace(" ", "%");
whereQueryWord = " (name like '%" + queryWord.Trim() + "%' or docNumber like '%" + queryWord.Trim() + "%'";
int _fValue = -1;
if (int.TryParse(queryWord, out _fValue))
{
whereQueryWord += " or id like '" + queryWord + "%'";
}
whereQueryWord += ")";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
//IList<AscmSupplier> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmSupplier>(sql + sort);
IList<AscmSupplier> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmSupplier>(sql + sort, sql, ynPage);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmSupplier>(ilist);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmSupplier)", ex);
throw ex;
}
return list;
}
public List<AscmDeliveryOrderMain> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmDeliveryOrderMain> list = null;
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 AscmDeliveryOrderMain";
string detailCount = "select count(*) from AscmDeliveryOrderDetail where mainId= a.id";
string totalNumber = "select sum(deliveryQuantity) from AscmDeliveryOrderDetail where mainId= a.id";
string sql1 = "select new AscmDeliveryOrderMain(a,(" + detailCount + "),(" + totalNumber + ")) from AscmDeliveryOrderMain a ";
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 (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
sql1 += " where " + where;
}
//IList<AscmDeliveryOrderMain> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmDeliveryOrderMain>(sql + sort);
IList<AscmDeliveryOrderMain> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmDeliveryOrderMain>(sql1 + sort, sql, ynPage);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmDeliveryOrderMain>(ilist);
SetSupplier(list);
SetBatch(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmDeliveryOrderMain)", ex);
throw ex;
}
return list;
}
public List<AscmRfid> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmRfid> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmRfid ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
queryWord = queryWord.Trim().Replace(" ", "%");
whereQueryWord = " (id like '%" + queryWord.Trim() + "%' or status like '%" + queryWord.Trim() + "%')";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
//IList<AscmEmployee> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmEmployee>(sql + sort);
IList<AscmRfid> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmRfid>(sql + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmRfid>(sql + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmRfid>(ilist);
//SetDepartment(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmRfid)", ex);
throw ex;
}
return list;
}
public List<AscmWmsBackInvoiceLink> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmWmsBackInvoiceLink> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmWmsBackInvoiceLink ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
IList<AscmWmsBackInvoiceLink> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWmsBackInvoiceLink>(sql + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWmsBackInvoiceLink>(sql + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmWmsBackInvoiceLink>(ilist);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWmsBackInvoiceLink)", ex);
throw ex;
}
return list;
}
public List<AscmWipScheduleGroups> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmWipScheduleGroups> list = null;
try
{
string sort = " order by scheduleGroupId ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmWipScheduleGroups ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
queryWord = queryWord.Trim().Replace(" ", "%");
whereQueryWord = " upper(scheduleGroupName) like '%" + queryWord.Trim().ToUpper() + "%'";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
IList<AscmWipScheduleGroups> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWipScheduleGroups>(sql + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWipScheduleGroups>(sql + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmWipScheduleGroups>(ilist);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWipScheduleGroups)", ex);
throw ex;
}
return list;
}
public List<AscmMesInteractiveLog> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmMesInteractiveLog> list = null;
try
{
string sort = string.Empty;
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmMesInteractiveLog ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " docNumber like '%" + queryWord.Trim() + "%'";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, " id in (select max(id) from AscmMesInteractiveLog group by docNumber) ");
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
IList<AscmMesInteractiveLog> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmMesInteractiveLog>(sql + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmMesInteractiveLog>(sql + sort);
if (ilist != null)
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmMesInteractiveLog>(ilist);
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmMesInteractiveLog)", ex);
throw ex;
}
return list;
}
public List<AscmMesTransStyle> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmMesTransStyle> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmMesTransStyle ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " code like '%" + queryWord.Trim() + "%'";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
IList<AscmMesTransStyle> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmMesTransStyle>(sql + sort, sql, ynPage);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmMesTransStyle>(ilist);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmMesTransStyle)", ex);
throw ex;
}
return list;
}
/// <summary>
/// 流转信息查询
/// 2013/07/16
/// </summary>
/// <param name="ynPage"></param>
/// <param name="sortName"></param>
/// <param name="sortOrder"></param>
/// <param name="queryWord"></param>
/// <returns></returns>
public List <AscmTagLog> GetFlowInfo(YnPage ynPage, string sortName, string sortOrder, string queryWord)
{
List <AscmTagLog> list = null;
try
{
string sort = " order by createTime desc";
//if (!string.IsNullOrEmpty(sortName))
//{
// sort = " order by " + sortName.Trim() + " ";
// if (!string.IsNullOrEmpty(sortOrder))
// sort += sortOrder.Trim();
//}
string strHql = "from AscmTagLog a where a.readTime=(select max(readTime) from AscmTagLog where epcId=a.epcId)";
// string where = "";
//where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where,queryWord);
if (!string.IsNullOrEmpty(queryWord))
{
strHql += " and " + queryWord;
}
IList <AscmTagLog> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmTagLog>(strHql + sort, strHql, ynPage);
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmTagLog>(ilist);
SetObjectId(list); //设置物件的ID
SetSupplier(list); //设置物件的供应商
SetAscmReadingHead(list); //设置地址
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmTagLog)", ex);
throw ex;
}
return(list);
}
public AscmWmsContainerDelivery GetCurrent(string containerSn, string sessionKey)
{
try
{
string sort = " order by id desc ";
string sql = "from AscmWmsContainerDelivery where containerSn='" + containerSn + "' and (status is null or status='') and createTime>='" + DateTime.Now.AddDays(-2).ToString("yyyy-MM-dd 00:00:00") + "'";
YnPage ynPage = new YnPage();
ynPage.SetPageSize(1);
ynPage.SetCurrentPage(1);
IList <AscmWmsContainerDelivery> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmWmsContainerDelivery>(sql + sort, sql, ynPage, sessionKey);
//IList<AscmContainerDelivery> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmContainerDelivery>(sql, sessionKey);
if (ilist != null && ilist.Count > 0)
{
return(ilist[0]);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWmsContainerDelivery)", ex);
throw ex;
}
return(null);
}
public AscmContainerDelivery GetCurrent(string warehouseId)
{
AscmContainerDelivery ascmContainerDelivery = null;
try
{
YnPage ynPage = new YnPage();
ynPage.SetPageSize(1);
ynPage.SetCurrentPage(1);
string sort = " order by modifyTime desc ";
string sql = "from AscmContainerDelivery where status='" + AscmContainerDelivery.StatusDefine.inWarehouseDoor + "' and batSumMainId in (select id from AscmDeliBatSumMain where warehouseId='" + warehouseId + "') and modifyTime is not null";
IList<AscmContainerDelivery> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmContainerDelivery>(sql + sort, sql, ynPage);
if (ilist != null && ilist.Count>0)
{
return ilist[0];
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmContainerDelivery)", ex);
throw ex;
}
return ascmContainerDelivery;
}
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;
}
public List <AscmMarkTaskLog> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, bool isSetWipEntities = true, bool isSetGetMaterialTask = true)
{
List <AscmMarkTaskLog> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
string hql = "from AscmMarkTaskLog";
string hql_Param = "select wipEntityId from AscmWipEntities where name like '{0}%'";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
hql_Param = string.Format(hql_Param, queryWord.Trim());
whereQueryWord = "wipEntityId in (" + hql_Param + ")";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
whereQueryWord = "isMark = 1";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
if (!string.IsNullOrEmpty(where))
{
hql += " where " + where;
}
if (!string.IsNullOrEmpty(sort))
{
hql += sort;
}
IList <AscmMarkTaskLog> ilist = null;
if (ynPage != null)
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmMarkTaskLog>(hql, hql, ynPage);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmMarkTaskLog>(hql);
}
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmMarkTaskLog>(ilist);
if (isSetWipEntities)
{
SetWipEntities(list);
}
if (isSetGetMaterialTask)
{
SetGetMaterialTask(list);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Get AscmMarkTaskLog)", ex);
throw ex;
}
return(list);
}
public List<AscmWmsPreparationDetail> GetList(YnPage ynPage, string sortName, string sortOrder, int mainId, string queryWord, string whereOther)
{
List<AscmWmsPreparationDetail> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmWmsPreparationDetail ";
string locationDocNumber = "select docNumber from AscmWarelocation where id=a.warelocationId";
string wipEntityName = "select name from AscmWipEntities where wipEntityId=a.wipEntityId";
string containerBindNumber = "select ascmPreparedQuantity from AscmWipRequirementOperations where wipEntityId=a.wipEntityId and inventoryItemId=a.materialId";
string sql1 = "select new AscmWmsPreparationDetail(a,(" + locationDocNumber + "),(" + wipEntityName + "),(" + containerBindNumber + ")) from AscmWmsPreparationDetail a";
string where = "", whereQueryWord = "";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, " mainId=" + mainId);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
sql1 += " where " + where;
}
IList<AscmWmsPreparationDetail> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWmsPreparationDetail>(sql1 + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWmsPreparationDetail>(sql1 + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmWmsPreparationDetail>(ilist);
SetMaterial(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWmsPreparationDetail)", ex);
throw ex;
}
return list;
}
public List <AscmLogisticsClassInfo> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOhter, bool isSetGroupLeader = true, bool isSetMonitorLeader = true)
{
List <AscmLogisticsClassInfo> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
string sql = " from AscmLogisticsClassInfo ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
string whereWord = string.Empty;
whereQueryWord = "groupLeader = '" + queryWord.Trim() + "'";
whereWord = YnBaseClass2.Helper.StringHelper.SqlWhereOrAdd(whereWord, whereQueryWord);
whereQueryWord = "monitorLeader = '" + queryWord.Trim() + "'";
whereWord = YnBaseClass2.Helper.StringHelper.SqlWhereOrAdd(whereWord, whereQueryWord);
whereWord = "(" + whereWord + ")";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereWord);
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOhter);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
if (!string.IsNullOrEmpty(sort))
{
sql += sort;
}
IList <AscmLogisticsClassInfo> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmLogisticsClassInfo>(sql, sql, ynPage);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmLogisticsClassInfo>(ilist);
if (isSetGroupLeader)
{
SetGroupLeader(list);
}
if (isSetMonitorLeader)
{
SetMonitorLeader(list);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Get AscmLogisticsClassInfo)", ex);
throw ex;
}
return(list);
}
public List <AscmWipRequirementOperations> GetSumList(YnPage ynPage, string sortName, string sortOrder, string queryWord, int organizationId, string whereOther, string whereWipDiscreteJobs, string supplySubinventory)
{
List <AscmWipRequirementOperations> list = null;
try
{
string whereWipEntityId = "", whereWipEntityId_Count = "";
/*
* List<AscmWipDiscreteJobs> listWipDiscreteJobs = AscmWipDiscreteJobsService.GetInstance().GetList(ynPage, "", "", queryWord, whereWipDiscreteJobs);
* foreach (AscmWipDiscreteJobs ascmWipDiscreteJobs in listWipDiscreteJobs)
* {
* if (whereWipEntityId != "")
* whereWipEntityId += ",";
* whereWipEntityId += ascmWipDiscreteJobs.wipEntityId;
* }
* if (!string.IsNullOrEmpty(whereWipEntityId))
* whereWipEntityId = "wipEntityId in ( " + whereWipEntityId + ")";
* else
* whereWipEntityId = "wipEntityId in ( -1)";
* */
if (!string.IsNullOrEmpty(whereWipDiscreteJobs))
{
whereWipEntityId = "wipEntityId in ( select wipEntityId from AscmWipDiscreteJobs where " + whereWipDiscreteJobs + ")";
whereWipEntityId_Count = "wipEntityId in ( select wipEntityId from Ascm_Wip_Discrete_Jobs where " + whereWipDiscreteJobs + ")";
}
string sort = " order by inventoryItemId ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
//sort = ""; //不能在里面加order ，否则效率非常低
//string sql = "select inventoryItemId from AscmWipRequirementOperations";
string sqlCount = "select inventoryItemId from ASCM_WIP_REQUIRE_OPERAT ";
//string requiredQuantitySum = "select sum(requiredQuantity) from AscmCuxWipReleaseLines where releaseHeaderId= a.releaseHeaderId";
//string totalNumber = "select sum(deliveryQuantity) from AscmDeliveryOrderDetail where mainId= a.id";
//string sql1 = "select new AscmWipRequirementOperations(a,(" + detailCount + "),(" + totalNumber + ")) from AscmWipRequirementOperations a ";
//string sql1 = "select new AscmWipRequirementOperations(inventoryItemId) from AscmWipRequirementOperations t ";//,(" + detailCount + ")
string sql1 = "select new AscmWipRequirementOperations(inventoryItemId,count(*),sum(requiredQuantity),sum(quantityIssued)) from AscmWipRequirementOperations";
string where = "", whereQueryWord = "", where_Count = "", whereSupplySubinventory = "";
//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);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereWipEntityId);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereSupplySubinventory);
where_Count = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where_Count, whereQueryWord);
where_Count = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where_Count, whereOther);
where_Count = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where_Count, whereWipEntityId_Count);
where_Count = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where_Count, whereSupplySubinventory);
where_Count = where_Count.Replace("AscmMaterialItem", "Ascm_Material_Item");
if (!string.IsNullOrEmpty(where))
{
sqlCount += " where " + where_Count;
sql1 += " where " + where;
}
sqlCount += " group by inventoryItemId ";
sql1 += " group by inventoryItemId ";
object object1 = YnDaoHelper.GetInstance().nHibernateHelper.GetObjectBySQL("select count(*) from (" + sqlCount + ")");
int count = 0;
int.TryParse(object1.ToString(), out count);
IList <AscmWipRequirementOperations> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmWipRequirementOperations>(sql1 + sort, count, ynPage);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmWipRequirementOperations>(ilist);
//SetWipEntities(list);
SetMaterial(list);
//现有量
SetQuantity(list, organizationId, supplySubinventory);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWipRequirementOperations)", ex);
throw ex;
}
return(list);
}
public List <AscmDeliveryOrderBatch> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List <AscmDeliveryOrderBatch> list = null;
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 AscmDeliveryOrderBatch";
string _materialId = "select materialId from AscmDeliveryOrderDetail where mainId in (select id from AscmDeliveryOrderMain where batchId =a.id) and rownum=1";
string detailCount = "select count(*) from AscmDeliveryOrderDetail where mainId in (select id from AscmDeliveryOrderMain where batchId =a.id)";
string totalNumber = "select sum(deliveryQuantity) from AscmDeliveryOrderDetail where mainId in (select id from AscmDeliveryOrderMain where batchId = a.id)";
//string appointmentStartTime = "select appointmentStartTime from AscmDeliveryNotifyMain where id = (select mainId from AscmDeliveryNotifyDetail where id=(select notifyDetailId from AscmDeliveryOrderDetail where mainId = (select id from AscmDeliveryOrderMain where batchId =a.id)))";
//string appointmentEndTime = "select appointmentEndTime from AscmDeliveryNotifyMain where id = (select mainId from AscmDeliveryNotifyDetail where id=(select notifyDetailId from AscmDeliveryOrderDetail where mainId = (select id from AscmDeliveryOrderMain where batchId =a.id)))";
string sql1 = "select new AscmDeliveryOrderBatch(a,(" + detailCount + "),(" + totalNumber + "),(" + _materialId + ")) from AscmDeliveryOrderBatch a ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " (barCode like '%" + queryWord.Trim() + "%')";
}
//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 (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
sql1 += " where " + where;
}
//IList<AscmDeliveryOrderBatch> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmDeliveryOrderBatch>(sql + sort);
IList <AscmDeliveryOrderBatch> ilist = null;
if (ynPage != null)
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmDeliveryOrderBatch>(sql1 + sort, sql, ynPage);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmDeliveryOrderBatch>(sql1 + sort);
}
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmDeliveryOrderBatch>(ilist);
SetSupplier(list);
SetMaterial(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmDeliveryOrderBatch)", ex);
throw ex;
}
return(list);
}
//查询所属部门用户@2014/4/25
public List <AscmUserInfo> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, string departName, bool isSetDepartmentPosition = true, bool isLogisticsClassName = true)
{
List <AscmUserInfo> list = null;
try
{
string hql = "select u.* from YnUser u, YnDepartmentPositionUserLink l, YnDepartment d";
string sort = " order by u.userId ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
string where = "", whereQueryWord = "", whereOrganization = " (u.organizationId=0 or u.organizationId is null) ";
whereQueryWord = "l.departmentId = d.id";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
whereQueryWord = "l.userId = u.userId";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
if (!string.IsNullOrEmpty(departName))
{
whereQueryWord = "d.name = '" + departName + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " (u.userId like '%" + queryWord.Trim() + "%' or u.userName like '%" + queryWord.Trim() + "%' or u.description like '%" + queryWord.Trim() + "%')";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOrganization);
if (!string.IsNullOrEmpty(where))
{
hql += " where " + where;
}
//string hql_Param = hql.Replace("u.*", "count(u.userId)");
//object obj = YnFrame.Dal.YnDaoHelper.GetInstance().nHibernateHelper.GetObjectBySQL(hql_Param);
//int count = 0;
//int.TryParse(obj.ToString(), out count);
//IList<AscmUserInfo> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmUserInfo>(hql, count, ynPage);
NHibernate.ISQLQuery query = YnDaoHelper.GetInstance().nHibernateHelper.FindBySQLQuery(hql);
IList <AscmUserInfo> ilist = query.AddEntity("u", typeof(AscmUserInfo)).List <AscmUserInfo>();
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmUserInfo>(ilist);
List <YnFrame.Dal.Entities.YnUser> list1 = new List <YnFrame.Dal.Entities.YnUser>();
foreach (AscmUserInfo ascmUserInfo in list)
{
list1.Add((YnFrame.Dal.Entities.YnUser)ascmUserInfo);
}
//YnFrame.Services.YnUserService.GetInstance().SetRole(list1);
if (isSetDepartmentPosition)
{
YnFrame.Services.YnUserService.GetInstance().SetDepartmentPosition(list1);
}
if (isLogisticsClassName)
{
AscmUserInfoService.GetInstance().SetUserLogisticsClass(list);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find YnUser)", ex);
throw ex;
}
return(list);
}
public List <AscmUserInfo> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, bool isSetDepartmentPosition = true, bool isLogisticsClassName = true)
{
List <AscmUserInfo> list = null;
try
{
string sort = " order by userId ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
string sql = "from AscmUserInfo a";
string where = "", whereQueryWord = "", whereOrganization = " (organizationId=0 or organizationId is null) ";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " (userId like '%" + queryWord.Trim() + "%' or userName like '%" + queryWord.Trim() + "%' or description like '%" + queryWord.Trim() + "%')";
}
//if (!string.IsNullOrEmpty(organizationId))
// whereOrganization = " a.organizationId=" + organizationId;
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOrganization);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
IList <AscmUserInfo> ilist = null;
if (ynPage != null)
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmUserInfo>(sql + sort, sql, ynPage);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmUserInfo>(sql + sort);
}
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmUserInfo>(ilist);
List <YnFrame.Dal.Entities.YnUser> list1 = new List <YnFrame.Dal.Entities.YnUser>();
foreach (AscmUserInfo ascmUserInfo in list)
{
list1.Add((YnFrame.Dal.Entities.YnUser)ascmUserInfo);
}
//YnFrame.Services.YnUserService.GetInstance().SetRole(list1);
if (isSetDepartmentPosition)
{
YnFrame.Services.YnUserService.GetInstance().SetDepartmentPosition(list1);
}
if (isLogisticsClassName)
{
AscmUserInfoService.GetInstance().SetUserLogisticsClass(list);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find YnUser)", ex);
throw ex;
}
return(list);
}
/// <summary>
/// 关联作业@2014-04-22
/// </summary>
/// <param name="ynPage"></param>
/// <param name="sortName"></param>
/// <param name="sortOrder"></param>
/// <param name="queryWord"></param>
/// <param name="queryStartTime"></param>
/// <param name="queryEndTime"></param>
/// <param name="materialId"></param>
/// <returns></returns>
public List <AscmDiscreteJobs> GetRelatedDiscreteJobs(YnPage ynPage, string sortName, string sortOrder, string queryWord, string queryStartTime, string queryEndTime, int materialId)
{
List <AscmDiscreteJobs> list = null;
try
{
//string hql_ParamData = "from AscmWipRequirementOperations where wipentityId in (select t1.wipentityId from AscmWipEntities t1, AscmDiscreteJobs t2 where t1.name = t2.jobId and t2.createTime > '{0}' and t2.createTime <= '{1}') and inventoryItemId = {2}";
string hql_ParamData = "select new AscmWipRequirementOperations(t1.wipEntityId) from AscmWipRequirementOperations t1, AscmWipEntities t2, AscmDiscreteJobs t3 where t2.name = t3.jobId and t1.wipEntityId = t2.wipEntityId and t3.jobDate >= '{0}' and t3.jobDate <= '{1}' and t1.inventoryItemId = {2}";
//string hql_ParamData = "select t1.wipEntityId from AscmWipRequirementOperations t1, AscmWipEntities t2, AscmDiscreteJobs t3 where t2.name = t3.jobId and t1.wipentityId = t2.wipentityId and t3.jobDate > '{0}' and t3.jobDate <= '{1}' and t1.inventoryItemId = {2}";
string hql = "from AscmDiscreteJobs where jobId in ({0})";
string hql_Param = "select name from AscmWipEntities";
hql_ParamData = string.Format(hql_ParamData, queryStartTime, queryEndTime, materialId);
IList <AscmWipRequirementOperations> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmWipRequirementOperations>(hql_ParamData);
if (ilist != null && ilist.Count > 0)
{
List <AscmWipRequirementOperations> newlist = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmWipRequirementOperations>(ilist);
var wipEntityIds = newlist.Select(P => P.wipEntityId).Distinct();
var count = wipEntityIds.Count();
string ids = string.Empty;
for (int i = 0; i < count; i++)
{
if (!string.IsNullOrEmpty(ids))
{
ids += ",";
}
ids += wipEntityIds.ElementAt(i);
}
string where = "", whereQueryWord = "";
whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids, "wipentityId");
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
if (!string.IsNullOrEmpty(where))
{
hql_Param += " where " + where;
hql = string.Format(hql, hql_Param);
}
IList <AscmDiscreteJobs> ilistAscmDiscreteJobs = null;
if (ynPage != null)
{
ilistAscmDiscreteJobs = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmDiscreteJobs>(hql, hql, ynPage);
}
else
{
ilistAscmDiscreteJobs = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmDiscreteJobs>(hql);
}
if (ilistAscmDiscreteJobs != null && ilistAscmDiscreteJobs.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmDiscreteJobs>(ilistAscmDiscreteJobs);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmDiscreteJobs)", ex);
throw ex;
}
return(list);
}
public List <AscmDiscreteJobs> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string queryStartTime, string queryEndTime, string queryType, string queryRanker, string userName)
{
List <AscmDiscreteJobs> list = null;
try
{
string sort = "", whereQueryWord = "", where = "", where_Param = "";
string userRole = AscmUserInfoService.GetInstance().GetUserRoleName(userName);
string userLogisticsName = AscmUserInfoService.GetInstance().GetUserLogisticsName(userName, userRole);
string hql = "from AscmDiscreteJobs";
string hql_ParamZ = "select zRankerName from AscmAllocateRule";
string hql_ParamD = "select dRankerName from AscmAllocateRule";
string sql = "select * from ASCM_DISCRETE_JOBS";
if (userRole == "领料员")
{
whereQueryWord = "workerName = '" + userName + "'";
where_Param = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where_Param, whereQueryWord);
}
else if (userRole == "总装排产员" || userRole == "电装排产员")
{
whereQueryWord = "workerId = '" + userName + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else
{
if (!string.IsNullOrEmpty(userLogisticsName))
{
IList <AscmLogisticsClassInfo> ilistAscmLogisticsClassInfo = AscmLogisticsClassInfoService.GetInstance().GetList("from AscmLogisticsClassInfo where logisticsClass in (" + userLogisticsName + ")", false, false);
string ids = string.Empty;
if (ilistAscmLogisticsClassInfo != null && ilistAscmLogisticsClassInfo.Count > 0)
{
foreach (AscmLogisticsClassInfo ascmlogisticsClassInfo in ilistAscmLogisticsClassInfo)
{
if (!string.IsNullOrEmpty(ids))
{
ids += ",";
}
ids += ascmlogisticsClassInfo.id;
}
}
whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids, "logisticsClassId");
where_Param = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where_Param, whereQueryWord);
}
}
if (!string.IsNullOrEmpty(where_Param))
{
string newCondition = string.Empty;
hql_ParamZ += " where " + where_Param;
hql_ParamZ = "workerId in (" + hql_ParamZ + ")";
newCondition = YnBaseClass2.Helper.StringHelper.SqlWhereOrAdd(newCondition, hql_ParamZ);
hql_ParamD += " where " + where_Param;
hql_ParamD = "workerId in (" + hql_ParamD + ")";
newCondition = YnBaseClass2.Helper.StringHelper.SqlWhereOrAdd(newCondition, hql_ParamD);
where += "(" + newCondition + ")";
}
if (!string.IsNullOrEmpty(queryType))
{
whereQueryWord = "identificationId = " + queryType;
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryStartTime) && !string.IsNullOrEmpty(queryEndTime))
{
queryStartTime = queryStartTime + " 00:00:00";
queryEndTime = queryEndTime + " 23:59:59";
whereQueryWord = "createTime > '" + queryStartTime + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
whereQueryWord = "createTime <= '" + queryEndTime + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (!string.IsNullOrEmpty(queryStartTime) && string.IsNullOrEmpty(queryEndTime))
{
whereQueryWord = "createTime like '" + queryStartTime + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (string.IsNullOrEmpty(queryStartTime) && !string.IsNullOrEmpty(queryEndTime))
{
whereQueryWord = "createTime like '" + queryEndTime + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryRanker))
{
whereQueryWord = "workerId = '" + queryRanker + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(where))
{
hql += " where " + where;
sql += " where " + where;
}
sql = sql.Replace("AscmAllocateRule", "ASCM_ALLOCATE_RULE");
object object1 = YnDaoHelper.GetInstance().nHibernateHelper.GetObjectBySQL("select count(*) from (" + sql + ")");
int count = 0;
int.TryParse(object1.ToString(), out count);
IList <AscmDiscreteJobs> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmDiscreteJobs>(hql, count, ynPage);
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmDiscreteJobs>(ilist);
SetRanker(list);
list = list.OrderBy(e => e.workerId).OrderBy(e => e.productLine).OrderBy(e => e.sequence).OrderBy(e => e.identificationId).OrderBy(e => e.id).ToList();
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmDiscreteJobs)", ex);
throw ex;
}
return(list);
}
//public List<AscmDiscreteJobs> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord)
//{
// List<AscmDiscreteJobs> list = null;
// try
// {
// string sort = "";
// if (!string.IsNullOrEmpty(sortName))
// {
// sort = "order by " + sortName.Trim() + " " ;
// if (!string.IsNullOrEmpty(sortOrder))
// sort += sortOrder.Trim();
// }
// string sql = "from AscmDiscreteJobs";
// IList<AscmDiscreteJobs> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmDiscreteJobs>(sql + sort, sql, ynPage);
// if (ilist != null)
// {
// list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmDiscreteJobs>(ilist);
// }
// }
// catch (Exception ex)
// {
// YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmDiscreteJobs)", ex);
// throw ex;
// }
// return list;
//}
public List <AscmDiscreteJobs> GetList(YnPage ynpage, string sortName, string sortOrder, string queryWord, string queryDate, string queryType, string queryRanker, string userLogistisClass, string userRole, string userName)
{
List <AscmDiscreteJobs> list = null;
try
{
string sort = "", whereQueryWord = "", where = "";
string sql = "from AscmDiscreteJobs";
if (!string.IsNullOrEmpty(sortName))
{
sort = "order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
if (!string.IsNullOrEmpty(queryDate))
{
whereQueryWord = "createTime like '%" + queryDate + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryType))
{
whereQueryWord = "identificationId = " + queryType;
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(userLogistisClass))
{
IList <AscmLogisticsClassInfo> ilistAscmLogisticsClassInfo = AscmLogisticsClassInfoService.GetInstance().GetList("from AscmLogisticsClassInfo where logisticsClass in (" + userLogistisClass + ")", false, false);
string ids = string.Empty;
if (ilistAscmLogisticsClassInfo != null && ilistAscmLogisticsClassInfo.Count > 0)
{
foreach (AscmLogisticsClassInfo ascmlogisticsClassInfo in ilistAscmLogisticsClassInfo)
{
if (!string.IsNullOrEmpty(ids))
{
ids += ",";
}
ids += ascmlogisticsClassInfo.id;
}
}
string newCondition = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids, "logisticsClassId");
if (!string.IsNullOrEmpty(newCondition))
{
string newsql = "from AscmAllocateRule where " + newCondition;
IList <AscmAllocateRule> ilistAscmAllocateRule = AscmAllocateRuleService.GetInstance().GetList(newsql, false, false, false);
ids = "";
if (ilistAscmAllocateRule != null && ilistAscmAllocateRule.Count > 0)
{
foreach (AscmAllocateRule ascmAllocateRule in ilistAscmAllocateRule)
{
if (!string.IsNullOrEmpty(ids) && (!string.IsNullOrEmpty(ascmAllocateRule.zRankerName) || !string.IsNullOrEmpty(ascmAllocateRule.dRankerName)))
{
ids += ",";
}
if (!string.IsNullOrEmpty(ascmAllocateRule.zRankerName))
{
ids += "'" + ascmAllocateRule.zRankerName + "'";
}
if (!string.IsNullOrEmpty(ids) && !string.IsNullOrEmpty(ascmAllocateRule.dRankerName))
{
ids += ",";
}
if (!string.IsNullOrEmpty(ascmAllocateRule.dRankerName))
{
ids += "'" + ascmAllocateRule.dRankerName + "'";
}
}
}
if (string.IsNullOrEmpty(queryRanker))
{
whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids, "workerId");
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else
{
if (ids.IndexOf(queryRanker) > -1)
{
whereQueryWord = "workerId like '%" + queryRanker + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else
{
whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids, "workerId");
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
}
}
}
else
{
if (userRole == "总装排产员" || userRole == "电装排产员")
{
whereQueryWord = "workerId = '" + userName + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (userRole == "领料员")
{
string newsql = "from AscmAllocateRule where workerName = '" + userName + "'";
string ids = string.Empty;
IList <AscmAllocateRule> ilistAscmAllocateRule = AscmAllocateRuleService.GetInstance().GetList(newsql, false, false, false);
if (ilistAscmAllocateRule != null && ilistAscmAllocateRule.Count > 0)
{
foreach (AscmAllocateRule ascmAllocateRule in ilistAscmAllocateRule)
{
if (!string.IsNullOrEmpty(ids))
{
ids += ",";
}
if (!string.IsNullOrEmpty(ascmAllocateRule.zRankerName))
{
ids += "'" + ascmAllocateRule.zRankerName + "'";
}
if (!string.IsNullOrEmpty(ascmAllocateRule.dRankerName))
{
ids += "'" + ascmAllocateRule.dRankerName + "'";
}
}
}
whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids, "workerId");
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
}
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where + " order by workerId,workerId,productLine,sequence,id";
IList <AscmDiscreteJobs> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmDiscreteJobs>(sql, sql, ynpage);
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmDiscreteJobs>(ilist);
SetRanker(list);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmDiscreteJobs)", ex);
throw ex;
}
return(list);
}
/// <summary>
/// 加载物料@2014-4-19
/// </summary>
/// <param name="ynPage"></param>
/// <param name="sortName"></param>
/// <param name="sortOrder"></param>
/// <param name="queryWord"></param>
/// <param name="whereOther"></param>
/// <param name="taskId"></param>
/// <param name="jobId"></param>
/// <param name="queryBomWarehouse"></param>
/// <param name="queryBomMtlCategory"></param>
/// <returns></returns>
public List <AscmWipRequirementOperations> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, string taskId, string jobId, string queryBomWarehouse, string queryBomMtlCategory, bool isSetOnhandQuantity = true)
{
List <AscmWipRequirementOperations> list = null;
try
{
string hql = "select new AscmWipRequirementOperations(awro, ami.docNumber, ami.description) from AscmWipRequirementOperations awro, AscmMaterialItem ami";
string sql = "select count(*) from AscmWipRequirementOperations awro";
string where = "", whereQueryWord = "", whereOtherWord = "";
string sort = " order by ami.docNumber";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
whereQueryWord = "awro.inventoryItemId = ami.id";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = "ami.description like '%" + queryWord + "%'";
whereOtherWord = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOtherWord, whereQueryWord);
}
if (!string.IsNullOrEmpty(taskId))
{
whereQueryWord = "awro.taskId = " + taskId;
whereOtherWord = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOtherWord, whereQueryWord);
}
if (!string.IsNullOrEmpty(jobId))
{
whereQueryWord = "awro.wipEntityId = " + jobId;
whereOtherWord = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOtherWord, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryBomWarehouse))
{
queryBomWarehouse = queryBomWarehouse.ToUpper().Trim();
whereQueryWord = "awro.supplySubinventory like '" + queryBomWarehouse + "%'";
whereOtherWord = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOtherWord, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryBomMtlCategory))
{
whereQueryWord = "ami.docNumber like '" + queryBomMtlCategory + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(whereOtherWord))
{
sql += " where " + whereOtherWord;
}
object obj = YnDaoHelper.GetInstance().nHibernateHelper.GetObject(sql);
int count = 0;
int.TryParse(obj.ToString(), out count);
if (!string.IsNullOrEmpty(where))
{
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOtherWord);
hql += " where " + where + sort;
}
IList <AscmWipRequirementOperations> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmWipRequirementOperations>(hql, count, ynPage);
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmWipRequirementOperations>(ilist);
if (isSetOnhandQuantity)
{
SetOnhandQuantity(list);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("获取失败(Get AscmWipRequirementOperationsList)", ex);
throw ex;
}
return(list);
}
public List<AscmWmsMtlReturnMain> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmWmsMtlReturnMain> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " docNumber like '%" + queryWord.Trim() + "%'";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
string sql = "from AscmWmsMtlReturnMain ";
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
IList<AscmWmsMtlReturnMain> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWmsMtlReturnMain>(sql + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWmsMtlReturnMain>(sql + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmWmsMtlReturnMain>(ilist);
SetTransactionReason(list);
SetWipEntities(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWmsMtlReturnMain)", ex);
throw ex;
}
return list;
}
//public List<AscmForklift> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string queryOtherWord)
//{
// List<AscmForklift> list = null;
// try
// {
// string sort = " order by id ";
// if (!string.IsNullOrEmpty(sortName))
// {
// sort = " order by " + sortName.Trim() + " ";
// if (!string.IsNullOrEmpty(sortOrder))
// sort += sortOrder.Trim();
// }
// string sql = "from AscmForklift";
// string where = "", whereQueryWord = "";
// if (!string.IsNullOrEmpty(queryWord))
// {
// whereQueryWord = "forkliftType = '" + queryWord.Trim() + "'";
// where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
// }
// if (!string.IsNullOrEmpty(queryOtherWord))
// {
// string sSql = " from YnUser where userName like '" + queryOtherWord + "%'";
// IList<YnUser> ilistYnUser = YnFrame.Dal.YnDaoHelper.GetInstance().nHibernateHelper.Find<YnUser>(sSql);
// if (ilistYnUser != null && ilistYnUser.Count > 0)
// {
// queryOtherWord = "";
// foreach (YnUser ynUser in ilistYnUser)
// {
// if (!string.IsNullOrEmpty(queryOtherWord))
// queryOtherWord += ",";
// queryOtherWord += "'" + ynUser.userName + "'";
// }
// }
// whereQueryWord = "workerId in (" + queryOtherWord + ")";
// where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
// }
// if (!string.IsNullOrEmpty(where))
// sql += " where " + where;
// IList<AscmForklift> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmForklift>(sql, sql, ynPage);
// if (ilist != null)
// {
// list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmForklift>(ilist);
// SetWorker(list);
// }
// }
// catch (Exception ex)
// {
// YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmForklift)",ex);
// throw ex;
// }
// return list;
//}
public List <AscmForklift> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, bool isSetWorker = true)
{
List <AscmForklift> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
string sql = "from AscmForklift";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
string whereOtherWord = "";
whereQueryWord = "userName like '" + queryWord + "%'";
List <AscmUserInfo> listAscmUserInfo = AscmUserInfoService.GetInstance().GetList(null, "", "", "", whereQueryWord);
string ids = string.Empty;
if (listAscmUserInfo != null && listAscmUserInfo.Count > 0)
{
foreach (AscmUserInfo ascmUserInfo in listAscmUserInfo)
{
if (!string.IsNullOrEmpty(ids))
{
ids += ",";
}
ids += ascmUserInfo.userId;
}
whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids, "workerId");
whereOtherWord = YnBaseClass2.Helper.StringHelper.SqlWhereOrAdd(whereOtherWord, whereQueryWord);
}
whereQueryWord = "forkliftNumber like '%" + queryWord + "%'";
whereOtherWord = YnBaseClass2.Helper.StringHelper.SqlWhereOrAdd(whereOtherWord, whereQueryWord);
whereQueryWord = "assetsId like '%" + queryWord + "%'";
whereOtherWord = YnBaseClass2.Helper.StringHelper.SqlWhereOrAdd(whereOtherWord, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOtherWord);
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
if (!string.IsNullOrEmpty(sort))
{
sql += sort;
}
IList <AscmForklift> ilist = null;
if (ynPage != null)
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmForklift>(sql, sql, ynPage);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmForklift>(sql);
}
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmForklift>(ilist);
if (isSetWorker)
{
SetWorker(list);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmForklift)", ex);
throw ex;
}
return(list);
}
public List<AscmWmsIncManAccDetail> GetList(YnPage ynPage, string sortName, string sortOrder, string whereOther)
{
List<AscmWmsIncManAccDetail> list = null;
try
{
string sort = " order by incManAccDetailId ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string where = "";
string sql = " from AscmWmsIncManAccDetail";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
IList<AscmWmsIncManAccDetail> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWmsIncManAccDetail>(sql + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWmsIncManAccDetail>(sql + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmWmsIncManAccDetail>(ilist);
SetMaterialItem(list);
SetWarelocation(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWmsIncManAccDetail)", ex);
throw ex;
}
return list;
}
public List <AscmWarelocation> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther,
bool isSetWorkshopBuilding = true, bool isSetWarehouse = true, bool isSetWarehouseUser = true)
{
List <AscmWarelocation> list = null;
try
{
string sort = " order by docNumber ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
string sql = "from AscmWarelocation ";
string totalNumber = "select sum(quantity) from AscmLocationMaterialLink where warelocationId=a.id";
string sql1 = "select new AscmWarelocation(a,(" + totalNumber + ")) from AscmWarelocation a";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " upper(docNumber) like '%" + queryWord.Trim().ToUpper() + "%' or upper(categoryCode) like '%" + queryWord.Trim().ToUpper() + "%'";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
sql1 += " where " + where;
}
IList <AscmWarelocation> ilist = null;
if (ynPage != null)
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmWarelocation>(sql1 + sort, sql, ynPage);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmWarelocation>(sql1 + sort);
}
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmWarelocation>(ilist);
if (isSetWorkshopBuilding)
{
SetWorkshopBuilding(list);
}
if (isSetWarehouse)
{
SetWarehouse(list);
}
if (isSetWarehouseUser)
{
SetWarehouseUser(list);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWarelocation)", ex);
throw ex;
}
return(list);
}
public List <AscmMaterialItem> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther = "", bool isSetMaterialSubCategory = true)
{
List <AscmMaterialItem> list = null;
try
{
string sort = " order by docNumber ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
string sql = "from AscmMaterialItem ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
queryWord = queryWord.Trim().Replace(" ", "%");
whereQueryWord = " (docNumber like '" + queryWord.Trim() + "%' or description like '" + queryWord.Trim() + "%')";
/*long _fValue = -1;
* if (long.TryParse(queryWord, out _fValue))
* {
* whereQueryWord += "(docNumber like '" + queryWord + "%')";
* }
* else
* {
* whereQueryWord = " (description like '%" + queryWord.Trim() + "%')";
* }*/
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
IList <AscmMaterialItem> ilist = null;
if (ynPage != null)
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmMaterialItem>(sql + sort, sql, ynPage);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmMaterialItem>(sql + sort);
}
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmMaterialItem>(ilist);
if (isSetMaterialSubCategory)
{
SetMaterialSubCategory(list);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmMaterialItem)", ex);
throw ex;
}
return(list);
}
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);
}
public List<AscmJob> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord)
{
List<AscmJob> list = null;
try
{
string sort = "";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmJob";
IList<AscmJob> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmJob>(sql + sort, sql, ynPage);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmJob>(ilist);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmJob)", ex);
throw ex;
}
return list;
}
public List<AscmMaterialSubCategory> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, bool isSetMaterialCategory = true)
{
List<AscmMaterialSubCategory> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmMaterialSubCategory ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " (subCategoryCode like '%" + queryWord.Trim() + "%')";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
if (!string.IsNullOrEmpty(sort))
sql += sort;
IList<AscmMaterialSubCategory> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmMaterialSubCategory>(sql, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmMaterialSubCategory>(sql);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmMaterialSubCategory>(ilist);
if (isSetMaterialCategory)
SetMaterialCategory(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Get AscmMaterialSubCategory)", ex);
throw ex;
}
return list;
}
public List<AscmAllocateRule> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, string userLogistisClass)
{
List<AscmAllocateRule> list = new List<AscmAllocateRule>();
try
{
string sort = "";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
else
{
sort = " order by id ";
}
string sql = " from AscmAllocateRule ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = "workerName = '" + queryWord.Trim() + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
if (!string.IsNullOrEmpty(sort))
sql += sort;
IList<AscmAllocateRule> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmAllocateRule>(sql,sql,ynPage);
if (ilist != null)
{
List<AscmAllocateRule> listAscmAllocateRule = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmAllocateRule>(ilist);
SetLogisticsClass(listAscmAllocateRule);
if (!string.IsNullOrEmpty(userLogistisClass))
{
foreach (AscmAllocateRule ascmAllocateRule in listAscmAllocateRule)
{
if (ascmAllocateRule.logisticsClassId > 0)
{
if (userLogistisClass.IndexOf(ascmAllocateRule.ascmLogisticsClassInfo.logisticsClass) > -1)
{
list.Add(ascmAllocateRule);
}
}
else if (ascmAllocateRule.logisticsClassId == 0)
{
list.Add(ascmAllocateRule);
}
}
}
else
{
list = listAscmAllocateRule;
}
SetWorker(list);
SetZRanker(list);
SetDRanker(list);
SetLogisticsClass(list);
SetLogisticsClassName(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Get AscmAllocateRule)", ex);
throw ex;
}
return list;
}
public List <AscmAllocateRule> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, string userLogistisClass)
{
List <AscmAllocateRule> list = new List <AscmAllocateRule>();
try
{
string sort = "";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
else
{
sort = " order by id ";
}
string sql = " from AscmAllocateRule ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = "workerName = '" + queryWord.Trim() + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
if (!string.IsNullOrEmpty(sort))
{
sql += sort;
}
IList <AscmAllocateRule> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmAllocateRule>(sql, sql, ynPage);
if (ilist != null)
{
List <AscmAllocateRule> listAscmAllocateRule = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmAllocateRule>(ilist);
SetLogisticsClass(listAscmAllocateRule);
if (!string.IsNullOrEmpty(userLogistisClass))
{
foreach (AscmAllocateRule ascmAllocateRule in listAscmAllocateRule)
{
if (ascmAllocateRule.logisticsClassId > 0)
{
if (userLogistisClass.IndexOf(ascmAllocateRule.ascmLogisticsClassInfo.logisticsClass) > -1)
{
list.Add(ascmAllocateRule);
}
}
else if (ascmAllocateRule.logisticsClassId == 0)
{
list.Add(ascmAllocateRule);
}
}
}
else
{
list = listAscmAllocateRule;
}
SetWorker(list);
SetZRanker(list);
SetDRanker(list);
SetLogisticsClass(list);
SetLogisticsClassName(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Get AscmAllocateRule)", ex);
throw ex;
}
return(list);
}
public List <AscmGetMaterialLog> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List <AscmGetMaterialLog> list = new List <AscmGetMaterialLog>();
try
{
string sort = "";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
else
{
sort = " order by id ";
}
string sql = " from AscmGetMaterialLog ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = "workerId like '%" + queryWord + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
if (!string.IsNullOrEmpty(sort))
{
sql += sort;
}
IList <AscmGetMaterialLog> ilist = null;
if (ynPage != null)
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmGetMaterialLog>(sql, sql, ynPage);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmGetMaterialLog>(sql);
}
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmGetMaterialLog>(ilist);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Get AscmGetMaterialLog)", ex);
throw ex;
}
return(list);
}
public List<AscmUnloadingPoint> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmUnloadingPoint> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmUnloadingPoint ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " (name like '%" + queryWord.Trim() + "%') or (warehouseId like '%" + queryWord.Trim() + "%')";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
IList<AscmUnloadingPoint> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmUnloadingPoint>(sql + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmUnloadingPoint>(sql + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmUnloadingPoint>(ilist);
SetWarehouse(list);
SetUnloadingPointController(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmUnloadingPoint)", ex);
throw ex;
}
return list;
}
public List <AscmMaterialSubCategory> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, bool isSetMaterialCategory = true)
{
List <AscmMaterialSubCategory> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
string sql = "from AscmMaterialSubCategory ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " (subCategoryCode like '%" + queryWord.Trim() + "%')";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
if (!string.IsNullOrEmpty(sort))
{
sql += sort;
}
IList <AscmMaterialSubCategory> ilist = null;
if (ynPage != null)
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmMaterialSubCategory>(sql, sql, ynPage);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmMaterialSubCategory>(sql);
}
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmMaterialSubCategory>(ilist);
if (isSetMaterialCategory)
{
SetMaterialCategory(list);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Get AscmMaterialSubCategory)", ex);
throw ex;
}
return(list);
}
/*
* 2014/5/12 by chenyao
*/
public List <AscmCuxWipReleaseHeaders> GetList(YnPage ynPage, string releaseNumber, string startScheduledStartDate, string endScheduledStartDate, int?statusType)
{
List <AscmCuxWipReleaseHeaders> list = null;
string sql = "select {0} from cux_wip_release_headers wrh,wip_discrete_jobs wdj,wip_entities we,mtl_system_items_b msib";
string where = string.Empty;
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, "wrh.wip_entity_id = wdj.wip_entity_id");
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, "wdj.wip_entity_id = we.wip_entity_id");
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, "wdj.primary_item_id = msib.inventory_item_id");
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, "wdj.organization_id = 775");
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, "wrh.organization_id = 775");
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, "wrh.release_type = 'ISSUE'");
if (!string.IsNullOrWhiteSpace(releaseNumber))
{
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, "wrh.release_number = '" + releaseNumber.Trim() + "'");
}
string whereStartScheduledStartDate = "", whereEndScheduledStartDate = "";
DateTime dtStartScheduledStartDate, dtEndScheduledStartDate;
if (!string.IsNullOrEmpty(startScheduledStartDate) && DateTime.TryParse(startScheduledStartDate, out dtStartScheduledStartDate))
{
whereStartScheduledStartDate = "wdj.scheduled_start_date >= to_date('" + dtStartScheduledStartDate.ToString("yyyy-MM-dd 00:00") + "', 'yyyy-mm-dd hh24:mi')";
whereEndScheduledStartDate = "wdj.scheduled_start_date < to_date('" + dtStartScheduledStartDate.AddDays(1).ToString("yyyy-MM-dd 00:00") + "', 'yyyy-mm-dd hh24:mi')";
}
if (!string.IsNullOrEmpty(endScheduledStartDate) && DateTime.TryParse(endScheduledStartDate, out dtEndScheduledStartDate))
{
whereEndScheduledStartDate = "wdj.scheduled_start_date < to_date('" + dtEndScheduledStartDate.AddDays(1).ToString("yyyy-MM-dd 00:00") + "', 'yyyy-mm-dd hh24:mi')";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereStartScheduledStartDate);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereEndScheduledStartDate);
if (statusType.HasValue)
{
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, "wdj.status_type = " + statusType.Value);
}
sql += " where " + where;
string selectCount = string.Format(sql, "count(1)");
sql = string.Format(sql, "wrh.release_header_id,wrh.release_number,wrh.wip_entity_id,we.wip_entity_name,wdj.scheduled_start_date,wdj.wip_supply_type,wdj.schedule_group_id,wdj.net_quantity,wdj.status_type,wdj.description,msib.segment1");
sql += " order by wrh.release_header_id";
OracleParameter[] commandParameters = new OracleParameter[] {
new OracleParameter {
ParameterName = "i_sql",
OracleDbType = OracleDbType.Varchar2,
Size = 2000,
Value = sql,
Direction = ParameterDirection.Input
},
new OracleParameter {
ParameterName = "i_sql_count",
OracleDbType = OracleDbType.Varchar2,
Size = 2000,
Value = selectCount,
Direction = ParameterDirection.Input
},
new OracleParameter {
ParameterName = "i_pagesize",
OracleDbType = OracleDbType.Int32,
Value = ynPage.pageSize,
Direction = ParameterDirection.Input
},
new OracleParameter {
ParameterName = "i_currentpage",
OracleDbType = OracleDbType.Int32,
Value = ynPage.currentPage,
Direction = ParameterDirection.Input
},
new OracleParameter {
ParameterName = "o_totalcount",
OracleDbType = OracleDbType.Int32,
Direction = ParameterDirection.Output
},
new OracleParameter {
ParameterName = "o_pagecount",
OracleDbType = OracleDbType.Int32,
Direction = ParameterDirection.Output
},
new OracleParameter {
ParameterName = "o_cursor",
OracleDbType = OracleDbType.RefCursor,
Direction = ParameterDirection.Output
}
};
try
{
ISession session = YnDaoHelper.GetInstance().nHibernateHelper.GetCurrentSession();
OracleCommand command = (OracleCommand)session.Connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "cux_ascm_interface_utl.sp_exec_page";
Array.ForEach <OracleParameter>(commandParameters, P => command.Parameters.Add(P));
OracleDataAdapter da = new OracleDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
{
list = new List <AscmCuxWipReleaseHeaders>();
foreach (DataRow dr in dt.Rows)
{
AscmCuxWipReleaseHeaders cuxWipReleaseHeaders = new AscmCuxWipReleaseHeaders();
cuxWipReleaseHeaders.releaseHeaderId = Convert.ToInt32(dr["release_header_id"]);
cuxWipReleaseHeaders.releaseNumber = dr["release_number"].ToString();
cuxWipReleaseHeaders.wipEntityId = Convert.ToInt32(dr["wip_entity_id"]);
cuxWipReleaseHeaders.wipEntityName = dr["wip_entity_name"].ToString();
cuxWipReleaseHeaders.scheduledStartDate = dr["scheduled_start_date"].ToString();
cuxWipReleaseHeaders.wipSupplyType = Convert.ToInt32(dr["wip_supply_type"]);
cuxWipReleaseHeaders.scheduleGroupId = Convert.ToInt32(dr["schedule_group_id"]);
cuxWipReleaseHeaders.netQuantity = Convert.ToDecimal(dr["net_quantity"]);
cuxWipReleaseHeaders.statusType = Convert.ToInt32(dr["status_type"]);
cuxWipReleaseHeaders.description = dr["description"].ToString();
cuxWipReleaseHeaders.primaryItemDocNumber = dr["segment1"].ToString();
list.Add(cuxWipReleaseHeaders);
}
}
int totalCount = 0;
int.TryParse(commandParameters[4].Value.ToString(), out totalCount);
ynPage.SetRecordCount(totalCount);
int pageCount = 0;
int.TryParse(commandParameters[5].Value.ToString(), out pageCount);
ynPage.pageCount = pageCount;
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public List<AscmContainerDelivery> GetCurrentList(string containerSn, string sessionKey)
{
List<AscmContainerDelivery> list = null;
try
{
string sort = " order by id desc ";
string sql = "from AscmContainerDelivery where containerSn='" + containerSn + "'";
//IList<AscmContainerDelivery> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmContainerDelivery>(sql);
YnPage ynPage = new YnPage();
ynPage.SetPageSize(1);
ynPage.SetCurrentPage(1);
IList<AscmContainerDelivery> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmContainerDelivery>(sql + sort, sql, ynPage, sessionKey);
//IList<AscmContainerDelivery> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmContainerDelivery>(sql, sessionKey);
if (ilist != null && ilist.Count > 0)
{
foreach (AscmContainerDelivery ascmContainerDelivery in ilist)
{
ascmContainerDelivery.ascmDeliBatSumMain = AscmDeliBatSumMainService.GetInstance().Get(ascmContainerDelivery.batSumMainId, sessionKey);
ascmContainerDelivery.ascmDeliveryOrderBatch = AscmDeliveryOrderBatchService.GetInstance().Get(ascmContainerDelivery.deliveryOrderBatchId, sessionKey);
}
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmContainerDelivery>(ilist);
//AscmDeliBatSumMain ascmDeliBatSumMain = YnDaoHelper.GetInstance().nHibernateHelper.Get<AscmDeliBatSumMain>(ilist[0].batSumMainId, sessionKey);
//if (ascmDeliBatSumMain != null && (ascmDeliBatSumMain.status == AscmDeliBatSumMain.StatusDefine.outPlant || ascmDeliBatSumMain.status == AscmDeliBatSumMain.StatusDefine.confirm))
//{
// sql = "from AscmContainerDelivery where batSumMainId =" + ilist[0].batSumMainId + " and containerSn='" + containerSn + "'";
// ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmContainerDelivery>(sql, sessionKey);
// if (ilist != null)
// {
// list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmContainerDelivery>(ilist);
// }
//}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmContainerDelivery)", ex);
throw ex;
}
return list;
}
public List <AscmCuxWipReleaseHeaders> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, string whereWipDiscreteJobs)
{
List <AscmCuxWipReleaseHeaders> list = null;
try
{
string whereWipEntityId = "";
if (!string.IsNullOrEmpty(whereWipDiscreteJobs))
{
//List<AscmWipDiscreteJobs> listWipDiscreteJobs = AscmWipDiscreteJobsService.GetInstance().GetList(ynPage, "", "", queryWord, whereWipDiscreteJobs);
List <AscmWipDiscreteJobs> listWipDiscreteJobs = AscmWipDiscreteJobsService.GetInstance().GetList(null, "", "", "", whereWipDiscreteJobs, false, false, false, false);
int iCount = listWipDiscreteJobs.Count();
string ids = string.Empty;
for (int i = 0; i < iCount; i++)
{
if (!string.IsNullOrEmpty(ids))
{
ids += ",";
}
ids += listWipDiscreteJobs[i].wipEntityId;
if ((i + 1) % 1000 == 0 || i + 1 == iCount)
{
if (!string.IsNullOrEmpty(ids))
{
if (!string.IsNullOrEmpty(whereWipEntityId))
{
whereWipEntityId += " or ";
}
whereWipEntityId += "wipEntityId in ( " + ids + ")";
}
ids = string.Empty;
}
}
if (string.IsNullOrEmpty(whereWipEntityId))
{
whereWipEntityId = "wipEntityId in ( -1)";
}
//foreach (AscmWipDiscreteJobs ascmWipDiscreteJobs in listWipDiscreteJobs)
//{
// if (whereWipEntityId != "")
// whereWipEntityId += ",";
// whereWipEntityId += ascmWipDiscreteJobs.wipEntityId;
//}
//if (!string.IsNullOrEmpty(whereWipEntityId))
// whereWipEntityId = "wipEntityId in ( " + whereWipEntityId + ")";
//else
// whereWipEntityId = "wipEntityId in ( -1)";
}
string sort = " order by releaseHeaderId ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
//sort = ""; //不能在里面加order ，否则效率非常低
string sql = "from AscmCuxWipReleaseHeaders";
//string detailCount = "select count(*) from AscmCuxWipReleaseLines where releaseHeaderId= a.releaseHeaderId";
//string totalNumber = "select sum(deliveryQuantity) from AscmDeliveryOrderDetail where mainId= a.id";
//string sql1 = "select new AscmCuxWipReleaseHeaders(a,(" + detailCount + "),(" + totalNumber + ")) from AscmCuxWipReleaseHeaders a ";
string sql1 = "select new AscmCuxWipReleaseHeaders(a) from AscmCuxWipReleaseHeaders a ";//,(" + detailCount + ")
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);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereWipEntityId);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
sql1 += " where " + where;
}
//IList<AscmCuxWipReleaseHeaders> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmCuxWipReleaseHeaders>(sql + sort);
IList <AscmCuxWipReleaseHeaders> ilist = null;
if (ynPage != null)
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmCuxWipReleaseHeaders>(sql1 + sort, sql, ynPage);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmCuxWipReleaseHeaders>(sql1 + sort);
}
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmCuxWipReleaseHeaders>(ilist);
SetWipEntities(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmCuxWipReleaseHeaders)", ex);
throw ex;
}
return(list);
}
public List<AscmWarelocation> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther,
bool isSetWorkshopBuilding = true, bool isSetWarehouse = true, bool isSetWarehouseUser = true)
{
List<AscmWarelocation> list = null;
try
{
string sort = " order by docNumber ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmWarelocation ";
string totalNumber = "select sum(quantity) from AscmLocationMaterialLink where warelocationId=a.id";
string sql1 = "select new AscmWarelocation(a,(" + totalNumber + ")) from AscmWarelocation a";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " upper(docNumber) like '%" + queryWord.Trim().ToUpper() + "%' or upper(categoryCode) like '%" + queryWord.Trim().ToUpper() + "%'";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
sql1 += " where " + where;
}
IList<AscmWarelocation> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWarelocation>(sql1 + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWarelocation>(sql1 + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmWarelocation>(ilist);
if (isSetWorkshopBuilding)
SetWorkshopBuilding(list);
if (isSetWarehouse)
SetWarehouse(list);
if (isSetWarehouseUser)
SetWarehouseUser(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWarelocation)", ex);
throw ex;
}
return list;
}
public List <AscmWmsLocationTransfer> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, bool IsSetMaterialItem = true, bool IsSetToWarelocation = true, bool IsSetFromWarelocation = true)
{
List <AscmWmsLocationTransfer> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
string sql = "from AscmWmsLocationTransfer ";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
IList <AscmWmsLocationTransfer> ilist = null;
if (ynPage != null)
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmWmsLocationTransfer>(sql + sort, sql, ynPage);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmWmsLocationTransfer>(sql + sort);
}
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmWmsLocationTransfer>(ilist);
if (IsSetMaterialItem)
{
SetMaterialItem(list);
}
if (IsSetToWarelocation)
{
SetToWarelocation(list);
}
if (IsSetFromWarelocation)
{
SetFromWarelocation(list);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWmsLocationTransfer)", ex);
throw ex;
}
return(list);
}
public List <AscmWipRequirementOperations> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, string whereWipDiscreteJobs)
{
List <AscmWipRequirementOperations> list = null;
try
{
string whereWipEntityId = "";
/*
* List<AscmWipDiscreteJobs> listWipDiscreteJobs = AscmWipDiscreteJobsService.GetInstance().GetList(ynPage, "", "", queryWord, whereWipDiscreteJobs);
* foreach (AscmWipDiscreteJobs ascmWipDiscreteJobs in listWipDiscreteJobs)
* {
* if (whereWipEntityId != "")
* whereWipEntityId += ",";
* whereWipEntityId += ascmWipDiscreteJobs.wipEntityId;
* }
*
* if (!string.IsNullOrEmpty(whereWipEntityId))
* whereWipEntityId = "wipEntityId in ( " + whereWipEntityId + ")";
* else
* = "wipEntityId in ( -1)";
*/
if (!string.IsNullOrEmpty(whereWipDiscreteJobs))
{
whereWipEntityId = "wipEntityId in ( select wipEntityId from AscmWipDiscreteJobs where " + whereWipDiscreteJobs + ")";
}
string sort = " order by wipEntityId,inventoryItemId ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
{
sort += sortOrder.Trim();
}
}
//sort = ""; //不能在里面加order ，否则效率非常低
string sql = "from AscmWipRequirementOperations";
//string detailCount = "select count(*) from AscmCuxWipReleaseLines where releaseHeaderId= a.releaseHeaderId";
//string totalNumber = "select sum(deliveryQuantity) from AscmDeliveryOrderDetail where mainId= a.id";
//string sql1 = "select new AscmWipRequirementOperations(a,(" + detailCount + "),(" + totalNumber + ")) from AscmWipRequirementOperations a ";
string sql1 = "select new AscmWipRequirementOperations(a) from AscmWipRequirementOperations a ";//,(" + detailCount + ")
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);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereWipEntityId);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
sql1 += " where " + where;
}
//IList<AscmWipRequirementOperations> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWipRequirementOperations>(sql + sort);
IList <AscmWipRequirementOperations> ilist = null;
if (ynPage != null)
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmWipRequirementOperations>(sql1 + sort, sql, ynPage);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmWipRequirementOperations>(sql1 + sort);
}
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmWipRequirementOperations>(ilist);
SetWipEntities(list);
SetMaterial(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmWipRequirementOperations)", ex);
throw ex;
}
return(list);
}
public List<AscmGetMaterialTask> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther)
{
List<AscmGetMaterialTask> list = null;
try
{
string sort = " order by logisticsClass,status,IdentificationId,taskTime,warehouserId,mtlCategoryStatus,productLine ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortName))
sort += sortOrder.Trim();
}
string sql = " from AscmGetMaterialTask";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryWord))
{
whereQueryWord = " (jobId like '%" + queryWord.Trim() + "%')";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
IList<AscmGetMaterialTask> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmGetMaterialTask>(sql + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmGetMaterialTask>(sql + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmGetMaterialTask>(ilist);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Get AscmGetMaterialTask)", ex);
throw ex;
}
return list;
}
/// <summary>
/// 汇总物料@2014-4-22
/// </summary>
/// <param name="ynPage"></param>
/// <param name="userName"></param>
/// <param name="queryStartTime"></param>
/// <param name="queryEndTime"></param>
/// <param name="queryUserName"></param>
/// <returns></returns>
public List <AscmWipRequirementOperations> GetSumList(YnPage ynPage, string userName, string queryStartTime, string queryEndTime, string queryUserName)
{
List <AscmWipRequirementOperations> list = null;
try
{
string sql = "select new AscmWipRequirementOperations(inventoryItemId, sum(requiredQuantity), sum(quantityIssued), sum(getMaterialQuantity)) from AscmWipRequirementOperations where taskId in ({0}) group by inventoryItemId";
string sql_page = "select inventoryItemId, sum(requiredQuantity), sum(quantityIssued), sum(getMaterialQuantity) from ASCM_WIP_REQUIRE_OPERAT where taskId in ({0}) group by inventoryItemId";
string userRole = AscmUserInfoService.GetInstance().GetUserRoleName(userName);
string userLogistisClass = AscmUserInfoService.GetInstance().GetUserLogisticsName(userName);
string sqlParam = "select id from AscmGetMaterialTask";
string where = "", whereQueryWord = "";
if (string.IsNullOrEmpty(queryStartTime) && string.IsNullOrEmpty(queryEndTime))
{
throw new Exception("汇总物料失败：请选择起止日期！");
}
if (!string.IsNullOrEmpty(queryStartTime) && !string.IsNullOrEmpty(queryEndTime))
{
queryStartTime = queryStartTime + " 00:00:00";
queryEndTime = queryEndTime + " 23:59:59";
whereQueryWord = "createTime >= '" + queryStartTime + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
whereQueryWord = "createTime <= '" + queryEndTime + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (!string.IsNullOrEmpty(queryStartTime) && string.IsNullOrEmpty(queryEndTime))
{
whereQueryWord = "createTime like '" + queryStartTime + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (string.IsNullOrEmpty(queryStartTime) && !string.IsNullOrEmpty(queryEndTime))
{
whereQueryWord = "createTime like '" + queryEndTime + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(userLogistisClass) && userRole == "领料员")
{
whereQueryWord = "workerId = '" + userName + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (!string.IsNullOrEmpty(userLogistisClass) && (userRole.IndexOf("班长") > -1 || userRole.IndexOf("组长") > -1))
{
whereQueryWord = "logisticsClass in (" + userLogistisClass + ")";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(where))
{
sqlParam += " where " + where;
}
string hql = string.Format(sql, sqlParam);
string sqlParam_pape = sqlParam.Replace("AscmGetMaterialTask", "ASCM_GETMATERIAL_TASK");
string Tsql = string.Format(sql_page, sqlParam_pape);
object object1 = YnDaoHelper.GetInstance().nHibernateHelper.GetObjectBySQL("select count(*) from (" + Tsql + ")");
int count = 0;
int.TryParse(object1.ToString(), out count);
IList <AscmWipRequirementOperations> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmWipRequirementOperations>(hql, count, ynPage);
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmWipRequirementOperations>(ilist);
SetMaterial(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("汇总失败(Sum AscmWipRequirementOperations)", ex);
throw ex;
}
return(list);
}
// 手动任务管理@2014-03-25
public List<AscmGetMaterialTask> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string whereOther, string queryStartTime, string queryEndTime, string queryStatus)
{
List<AscmGetMaterialTask> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortName))
sort += sortOrder.Trim();
}
string sql = " from AscmGetMaterialTask";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(queryStartTime) && !string.IsNullOrEmpty(queryEndTime))
{
queryStartTime = queryStartTime + " 00:00:00";
queryEndTime = queryEndTime + " 23:59:59";
whereQueryWord = "createTime > '" + queryStartTime + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
whereQueryWord = "createTime <= '" + queryEndTime + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (!string.IsNullOrEmpty(queryStartTime) && string.IsNullOrEmpty(queryEndTime))
{
whereQueryWord = "createTime like '" + queryStartTime + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (string.IsNullOrEmpty(queryStartTime) && !string.IsNullOrEmpty(queryEndTime))
{
whereQueryWord = "createTime like '" + queryEndTime + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryStatus))
{
whereQueryWord = "status = '" + queryStatus + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
whereQueryWord = "taskId like '%L%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
if (!string.IsNullOrEmpty(sort))
sql += sort;
IList<AscmGetMaterialTask> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmGetMaterialTask>(sql, sql, ynPage);
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmGetMaterialTask>(ilist);
SetRelatedMark(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Get AscmGetMaterialTask)", ex);
throw ex;
}
return list;
}
//车辆入厂
public void InOutPlant(int doorId, string readingHead, int driverId, bool inPlant, string direction, bool onTime, ref string allocateOutDoor)
{
try
{
AscmDriver ascmDriver = Get(driverId);
if (ascmDriver != null)
{
int batSumMainId = 0;
string batSumDocNumber = string.Empty;
string appointmentStartTime = string.Empty;
string appointmentEndTime = string.Empty;
SetSupplier(new List <AscmDriver> {
ascmDriver
});
List <AscmDeliBatSumMain> list = AscmDeliBatSumMainService.GetInstance().GetByDriverId(ascmDriver.id);
if (list != null)
{
foreach (AscmDeliBatSumMain ascmDeliBatSumMain in list)
{
if (inPlant)
{
ascmDeliBatSumMain.status = AscmDeliBatSumMain.StatusDefine.inPlant;
ascmDeliBatSumMain.toPlantTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
ascmDeliBatSumMain.allocateOutDoor = "北门";
if (ascmDeliBatSumMain.warehouseId != "W112材料")
{
#region 取上一次送货合单
YnPage ynPage = new YnPage();
ynPage.SetPageSize(1);
ynPage.SetCurrentPage(1);
//string sort = " order by toPlantTime desc ";
string where = " toPlantTime is not null and status='" + AscmDeliBatSumMain.StatusDefine.inPlant + "'";
List <AscmDeliBatSumMain> listAscmDeliBatSumMainPreviousInPlant = AscmDeliBatSumMainService.GetInstance().GetList(ynPage, "toPlantTime", "desc", where);
if (listAscmDeliBatSumMainPreviousInPlant != null && listAscmDeliBatSumMainPreviousInPlant.Count > 0)
{
AscmDeliBatSumMain ascmDeliBatSumMain_tmp = listAscmDeliBatSumMainPreviousInPlant[0];
if (ascmDeliBatSumMain_tmp.allocateOutDoor == "北门")
{
ascmDeliBatSumMain.allocateOutDoor = "西门";
}
}
#endregion
}
batSumMainId = ascmDeliBatSumMain.id;
batSumDocNumber = ascmDeliBatSumMain.docNumber;
appointmentStartTime = ascmDeliBatSumMain.appointmentStartTime;
appointmentEndTime = ascmDeliBatSumMain.appointmentEndTime;
allocateOutDoor = ascmDeliBatSumMain.allocateOutDoor;
}
else
{
}
DateTime createTime = DateTime.Now;
string description = ascmDriver.supplierName + ":" + ascmDriver.name + ":" + ascmDriver.plateNumber;
AscmTruckSwipeLog ascmTruckSwipeLog = AscmTruckSwipeLogService.GetInstance().GetAddLog(doorId, readingHead, ascmDriver.rfid, ascmDriver.supplierId, driverId,
ascmDriver.supplierName, ascmDriver.name, ascmDriver.plateNumber, true, description, createTime, direction, batSumMainId, batSumDocNumber,
onTime, appointmentStartTime, appointmentEndTime);
using (ITransaction tx = YnDaoHelper.GetInstance().nHibernateHelper.GetCurrentSession().BeginTransaction())
{
try
{
if (ascmTruckSwipeLog != null)
{
YnDaoHelper.GetInstance().nHibernateHelper.Save(ascmTruckSwipeLog);
}
if (ascmDeliBatSumMain != null)
{
YnDaoHelper.GetInstance().nHibernateHelper.Update(ascmDeliBatSumMain);
}
tx.Commit();//正确执行提交
}
catch (Exception ex)
{
tx.Rollback();//回滚
throw ex;
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
// 领料任务监控列表@2014-4-18
public List<AscmGetMaterialTask> GetMonitorTaskList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string userName, string queryStatus, string queryLine, string queryType, string queryStartDate, string queryEndDate, string taskString, string queryWarehouse, string queryFormat, string queryPerson, string queryStartJobDate, string queryEndJobDate, string queryWipEntity)
{
List<AscmGetMaterialTask> list = null;
try
{
string userRole = AscmUserInfoService.GetInstance().GetUserRoleName(userName);
string userLogisticsClass = AscmUserInfoService.GetInstance().GetUserLogisticsName(userName);
string sql = "from AscmGetMaterialTask";
string where = "", whereQueryWord = "";
if (!string.IsNullOrEmpty(userLogisticsClass) && (userRole.IndexOf("物流班长") > -1 || userRole.IndexOf("物流组长") > -1))
{
whereQueryWord = "logisticsClass = '" + userLogisticsClass + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
//string ids_userId = AscmAllocateRuleService.GetInstance().GetLogisticsRankerName(userLogisticsClass, userName);
//if (!string.IsNullOrEmpty(ids_userId))
// whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids_userId, "rankerId");
//where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (!string.IsNullOrEmpty(userLogisticsClass) && userRole == "领料员")
{
whereQueryWord = "workerId = '" + userName + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (string.IsNullOrEmpty(userLogisticsClass) && userRole.IndexOf("排产员") > -1)
{
whereQueryWord = "rankerId = '" + userName + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(taskString))
{
whereQueryWord = "status in ('" + AscmGetMaterialTask.StatusDefine.execute + "' , '" + AscmGetMaterialTask.StatusDefine.notExecute + "')";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
if (taskString == "ALL")
{
List<AscmWipRequirementOperations> listWipRequireOperat = AscmWipRequirementOperationsService.GetInstance().GetList("from AscmWipRequirementOperations where taskId > 0 and wmsPreparationQuantity > 0 order by wmsPreparationQuantity desc");
if (listWipRequireOperat != null && listWipRequireOperat.Count > 0)
{
string ids = string.Empty;
foreach (AscmWipRequirementOperations ascmWipRequirementOperations in listWipRequireOperat)
{
if (!string.IsNullOrEmpty(ids))
ids += ",";
ids += ascmWipRequirementOperations.taskId;
}
whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids, "id");
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
}
else
{
string[] taskArray = taskString.Split(',');
string ids = string.Empty;
foreach (string str in taskArray)
{
if (!string.IsNullOrEmpty(ids))
ids += ",";
ids += str.Substring(str.IndexOf('[') + 1, str.IndexOf(']') - 1);
}
whereQueryWord = "id in (" + ids + ")";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
}
else
{
if (string.IsNullOrEmpty(queryStartDate) && string.IsNullOrEmpty(queryEndDate) && string.IsNullOrEmpty(queryStartJobDate) && string.IsNullOrEmpty(queryEndJobDate) && string.IsNullOrEmpty(queryWipEntity))
throw new Exception("请选择作业起止日期或任务起止日期或作业号！");
string hql = "from AscmWipRequirementOperations where wipEntityId = {0}";
if (!string.IsNullOrEmpty(queryWipEntity))
{
hql = string.Format(hql, queryWipEntity);
IList<AscmWipRequirementOperations> ilistAscmWipRequirementOperation = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmWipRequirementOperations>(hql);
if (ilistAscmWipRequirementOperation != null && ilistAscmWipRequirementOperation.Count > 0)
{
List<AscmWipRequirementOperations> listAscmWipRequirementOperation = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmWipRequirementOperations>(ilistAscmWipRequirementOperation);
string ids = string.Empty;
var taskIds = listAscmWipRequirementOperation.Select(P => P.taskId).Distinct();
for (int i = 0; i < taskIds.Count(); i++)
{
if (!string.IsNullOrEmpty(ids) && taskIds.ElementAt(i).HasValue)
ids += ",";
ids += taskIds.ElementAt(i);
}
whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids, "id");
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
}
if (!string.IsNullOrEmpty(queryStartDate) && !string.IsNullOrEmpty(queryEndDate))
{
queryStartDate = queryStartDate + " 00:00:00";
queryEndDate = queryEndDate + " 23:59:59";
whereQueryWord = "createTime >= '" + queryStartDate + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
whereQueryWord = "createTime <= '" + queryEndDate + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (!string.IsNullOrEmpty(queryStartDate) && string.IsNullOrEmpty(queryEndDate))
{
whereQueryWord = "createTime like '" + queryStartDate + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (string.IsNullOrEmpty(queryStartDate) && !string.IsNullOrEmpty(queryEndDate))
{
whereQueryWord = "createTime like '" + queryEndDate + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryStartJobDate) && !string.IsNullOrEmpty(queryEndJobDate))
{
queryStartJobDate = queryStartJobDate + " 00:00:00";
queryEndJobDate = queryEndJobDate + " 23:59:59";
whereQueryWord = "dateReleased >= '" + queryStartJobDate + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
whereQueryWord = "dateReleased <= '" + queryEndJobDate + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (!string.IsNullOrEmpty(queryStartJobDate) && string.IsNullOrEmpty(queryEndJobDate))
{
whereQueryWord = "dateReleased like '" + queryStartJobDate + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (string.IsNullOrEmpty(queryStartJobDate) && !string.IsNullOrEmpty(queryEndJobDate))
{
whereQueryWord = "dateReleased like '" + queryEndJobDate + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryStatus))
{
whereQueryWord = "status = '" + queryStatus + "'";
}
else
{
whereQueryWord = "status in ('" + AscmGetMaterialTask.StatusDefine.execute + "','" + AscmGetMaterialTask.StatusDefine.notExecute + "')";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
if (!string.IsNullOrEmpty(queryLine))
{
whereQueryWord = "productline like '" + queryLine + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryType))
{
whereQueryWord = "IdentificationId =" + queryType;
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryStartDate) && !string.IsNullOrEmpty(queryEndDate))
{
queryStartDate = queryStartDate + " 00:00:00";
queryEndDate = queryEndDate + " 23:59:59";
whereQueryWord = "createTime >= '" + queryStartDate + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
whereQueryWord = "createTime <= '" + queryEndDate + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryFormat))
{
if (queryFormat == "SPECWAREHOUSE")
{
whereQueryWord = "mtlCategoryStatus is null and taskId like 'T%'";
}
else if (queryFormat == "TEMPTASK")
{
whereQueryWord = "mtlCategoryStatus is null and taskId like 'L%'";
}
else
{
whereQueryWord = "mtlCategoryStatus = '" + queryFormat + "'";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryWarehouse))
{
string warehouseString = queryWarehouse.Substring(0, 4).ToUpper();
whereQueryWord = "warehouserId like '" + warehouseString + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryPerson))
{
whereQueryWord = "workerId = '" + queryPerson + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
}
if (!string.IsNullOrEmpty(where))
sql += " where " + where + " order by status,IdentificationId,taskTime,warehouserId,mtlCategoryStatus,productLine";
IList<AscmGetMaterialTask> ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmGetMaterialTask>(sql);
if (ilist != null && ilist.Count > 0)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmGetMaterialTask>(ilist);
SetRanker(list);
SetWorker(list);
SetWarehousePlace(list);
SumQuantity(list);
//list = list.OrderBy(e => e.totalQuantityPreparationDiff).ToList<AscmGetMaterialTask>().OrderByDescending(e => e.statusInt).ToList<AscmGetMaterialTask>();
list = list.OrderByDescending(e => e.statusInt).ToList<AscmGetMaterialTask>();
//foreach (AscmGetMaterialTask ascmGetMaterialTask in list)
//{
// if (ascmGetMaterialTask.mtlCategoryStatus == MtlCategoryStatusDefine.mixStock)
// {
// ascmGetMaterialTask.totalGetMaterialQuantity = 0;
// ascmGetMaterialTask.totalRequiredQuantity = 0;
// ascmGetMaterialTask.totalWmsPreparationQuantity = 0;
// }
//}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error(ex.Message, ex);
throw ex;
}
return list;
}
public List<AscmDeliBatSumDetail> GetList(YnPage ynPage, string sortName, string sortOrder, int? mainId, string queryWord, string whereOther)
{
List<AscmDeliBatSumDetail> list = null;
try
{
string sort = " order by id ";
if (!string.IsNullOrEmpty(sortName))
{
sort = " order by " + sortName.Trim() + " ";
if (!string.IsNullOrEmpty(sortOrder))
sort += sortOrder.Trim();
}
string sql = "from AscmDeliBatSumDetail ";
string containerBindNumber = "select sum(quantity) from AscmContainerDelivery where deliveryOrderBatchId=a.batchId and batSumMainId=a.mainId";
string checkQuantity = "select sum(quantity) from AscmContainerDelivery where deliveryOrderBatchId=a.batchId and batSumMainId=a.mainId and status='" + AscmContainerDelivery.StatusDefine.inWarehouseDoor + "'";
string receivedQuantity = "select sum(receivedQuantity) from AscmDeliBatOrderLink where batchId=a.batchId";
string containerNumber = "select count(distinct containerSn) from AscmContainerDelivery where deliveryOrderBatchId=a.batchId and batSumMainId=a.mainId";
string sql1 = "select new AscmDeliBatSumDetail(a,(" + containerBindNumber + "),(" + checkQuantity + "),(" + receivedQuantity + "),(" + containerNumber + ")) from AscmDeliBatSumDetail a ";
string where = "", whereQueryWord = "";
if (mainId.HasValue)
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, " mainId=" + mainId);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereOther);
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
sql1 += " where " + where;
}
IList<AscmDeliBatSumDetail> ilist = null;
if (ynPage != null)
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmDeliBatSumDetail>(sql1 + sort, sql, ynPage);
else
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find<AscmDeliBatSumDetail>(sql1 + sort);
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList<AscmDeliBatSumDetail>(ilist);
SetDeliveryOrderBatch(list);
SetMaterial(list);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmDeliBatSumDetail)", ex);
throw ex;
}
return list;
}
public List <AscmMaterialItem> GetList(YnPage ynPage, string sortName, string sortOrder, string queryWord, string queryType, string queryStarDocnumber, string queryEndDocnumber, string zStatus, string dStatus, string wStatus, string queryDescribe)
{
List <AscmMaterialItem> list = null;
try
{
string sql = "from AscmMaterialItem";
string where = "", whereQueryWord = "";
whereQueryWord = "wipSupplyType < 4";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
if (!string.IsNullOrEmpty(zStatus))
{
if (zStatus != "qb")
{
if (zStatus != "kz")
{
whereQueryWord = "zMtlCategoryStatus = '" + zStatus + "'";
}
else
{
whereQueryWord = "zMtlCategoryStatus is null";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
}
if (!string.IsNullOrEmpty(dStatus))
{
if (dStatus != "qb")
{
if (zStatus != "kz")
{
whereQueryWord = "dMtlCategoryStatus = '" + dStatus + "'";
}
else
{
whereQueryWord = "dMtlCategoryStatus is null";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
}
if (!string.IsNullOrEmpty(wStatus))
{
if (wStatus != "qb")
{
if (zStatus != "kz")
{
whereQueryWord = "wMtlCategoryStatus = '" + wStatus + "'";
}
else
{
whereQueryWord = "wMtlCategoryStatus is null";
}
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
}
if (string.IsNullOrEmpty(zStatus) && string.IsNullOrEmpty(dStatus) && string.IsNullOrEmpty(wStatus))
{
whereQueryWord = "isFlag = 0";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryType))
{
whereQueryWord = "wipSupplyType = " + queryType;
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryDescribe))
{
whereQueryWord = "description like '%" + queryDescribe + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryStarDocnumber) && !string.IsNullOrEmpty(queryEndDocnumber))
{
if (queryStarDocnumber == queryEndDocnumber)
{
whereQueryWord = "docNumber like '" + queryStarDocnumber + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else
{
whereQueryWord = "docNumber > '" + queryStarDocnumber + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
whereQueryWord = "docNumber < '" + queryEndDocnumber + "'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
}
else if (!string.IsNullOrEmpty(queryStarDocnumber) && string.IsNullOrEmpty(queryEndDocnumber))
{
whereQueryWord = "docNumber like '" + queryStarDocnumber + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else if (string.IsNullOrEmpty(queryStarDocnumber) && !string.IsNullOrEmpty(queryEndDocnumber))
{
whereQueryWord = "docNumber like '" + queryEndDocnumber + "%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
else
{
whereQueryWord = "docNumber like '20%'";
where = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(where, whereQueryWord);
}
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where + " order by docNumber";
}
IList <AscmMaterialItem> ilist = null;
if (queryWord == "Export")
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmMaterialItem>(sql);
}
else
{
ilist = YnDaoHelper.GetInstance().nHibernateHelper.Find <AscmMaterialItem>(sql, sql, ynPage);
}
if (ilist != null)
{
list = YnBaseClass2.Helper.ConvertHelper.ConvertIListToList <AscmMaterialItem>(ilist);
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("查询失败(Find AscmMaterialItem)", ex);
throw ex;
}
return(list);
}
