/// <summary>
/// 採購單,列表頁
/// </summary>
/// <param name="query"></param>
/// <param name="totalcount"></param>
/// <returns></returns>
public List<IpoQuery> GetIpoList(IpoQuery query, out int totalcount)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
try
{
sql.Append(@" select i.row_id,mu.user_username,i.po_id,i.vend_id,i.buyer,i.sched_rcpt_dt,i.po_type,i.po_type_desc,i.cancel_dt,i.msg1,i.msg2,i.msg3,i.create_user,i.create_dtim,i.`status` ");
sqlCondi.Append(" from ipo i ");
//sqlCondi.Append(" left join vendor v on v.vendor_id=i.vend_id ");
sqlCondi.Append(" left join manage_user mu on mu.user_id=i.create_user ");
sqlCondi.Append(" where 1=1 and i.status=1 ");
if (!string.IsNullOrEmpty(query.po_id))
{
sqlCondi.AppendFormat(" and i.po_id ='{0}' ", query.po_id);
}
if (!string.IsNullOrEmpty(query.po_type))
{
sqlCondi.AppendFormat(" and i.po_type ='{0}' ", query.po_type);
}
if (query.start_time!=DateTime.MinValue)
{
sqlCondi.AppendFormat(" and i.create_dtim >= '{0}'", Common.CommonFunction.DateTimeToString(query.start_time));
}
if (query.end_time != DateTime.MinValue)
{
sqlCondi.AppendFormat(" and i.create_dtim <= '{0}'", Common.CommonFunction.DateTimeToString(query.end_time));
}
//常溫or冷凍
if (query.freight != 0)
{
sqlCondi.AppendFormat(@" and i.po_id in (
select DISTINCT ipod.po_id
FROM ipod
left join product_item pi on pi.item_id=ipod.prod_id
inner join product p on pi.product_id=p.product_id
left join delivery_freight_set_mapping dfsm on dfsm.product_freight_set=p.product_freight_set
left join iplas ipl on ipl.item_id=ipod.prod_id
LEFT JOIN manage_user mu on mu.user_id=ipod.change_user
WHERE dfsm.delivery_freight_set = '{0}' )", query.freight);
}
totalcount = 0;
sqlCondi.Append(" order by i.row_id desc ");
if (query.IsPage)
{
DataTable _dt = _access.getDataTable("select count(po_id) as totalCount " + sqlCondi.ToString());
if (_dt.Rows.Count > 0)
{
totalcount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
}
sqlCondi.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
}
sql.Append(sqlCondi.ToString());
return _access.getDataTableForObj<IpoQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("IpoDao.GetIpoList-->" + ex.Message + sql.ToString(), ex);
}
}
