/// <summary>
/// 採購單,列表頁
/// </summary>
/// <param name="query"></param>
/// <param name="totalcount"></param>
/// <returns></returns>
public List<IpoNvdLogQuery> GetIpoNvdLogList(IpoNvdLogQuery query, out int totalCount)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder conndSql = new StringBuilder();
try
{
sql.Append(@"select inl.row_id,inl.`work_id`, inl.`ipo_id`, inl.item_id, i.upc_id,ip.loc_id, `add_qty`, `made_date`, `cde_date`,pe.pwy_dte_ctl,mu.user_username as create_user_string, inl.`create_datetime`
FROM ipo_nvd_log inl LEFT JOIN iupc i on inl.item_id=i.item_id
INNER JOIN manage_user mu on mu.user_id=inl.create_user
left join iplas ip on ip.item_id=inl.item_id
left join product_ext pe on pe.item_id=inl.item_id
where 1=1 ");
if (!string.IsNullOrEmpty(query.work_id))
{
conndSql.AppendFormat(" and inl.work_id ='{0}' ", query.work_id);
}
if (!string.IsNullOrEmpty(query.ipo_id))
{
conndSql.AppendFormat(" and inl.ipo_id ='{0}' ", query.ipo_id);
}
if (!string.IsNullOrEmpty(query.upc_id))
{
conndSql.AppendFormat(" and i.upc_id ='{0}' ", query.upc_id);
}
if (query.item_id !=0)
{
conndSql.AppendFormat(" and inl.item_id ='{0}' ", query.item_id);
}
if (!string.IsNullOrEmpty(query.loc_id))
{
conndSql.AppendFormat(" and ip.loc_id ='{0}' ", query.loc_id);
}
if (query.start_time!=DateTime.MinValue)
{
conndSql.AppendFormat(" and inl.create_datetime >= '{0}'", Common.CommonFunction.DateTimeToString(query.start_time));
}
if (query.end_time != DateTime.MinValue)
{
conndSql.AppendFormat(" and inl.create_datetime <= '{0}'", Common.CommonFunction.DateTimeToString(query.end_time));
}
conndSql.Append("group by inl.row_id order by inl.create_datetime desc ");
sql = sql.Append(conndSql.ToString());
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = new DataTable();
_dt = _access.getDataTable(@"select count(tab.row_id) as totalCount from (select inl.row_id FROM ipo_nvd_log inl LEFT JOIN iupc i on inl.item_id = i.item_id
INNER JOIN manage_user mu on mu.user_id=inl.create_user
left join iplas ip on ip.item_id=inl.item_id
where 1=1 " + conndSql.ToString() + ") tab");
if (_dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
sql.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
return _access.getDataTableForObj<IpoNvdLogQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("IpoNvdLogDao-->GetIpoNvdLogList-->" + ex.Message + sql.ToString(), ex);
}
}
public string InsertIpoNvdLogSql(IpoNvdLogQuery query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@" INSERT INTO `ipo_nvd_log` (`work_id`, `ipo_id`, `item_id`, `add_qty`, `made_date`, `cde_date`, `create_user`, `create_datetime`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}');", query.work_id, query.ipo_id, query.item_id, query.add_qty, query.made_date.ToString("yyyy-MM-dd"), query.cde_date.ToString("yyyy-MM-dd"), query.create_user, CommonFunction.DateTimeToString(DateTime.Now));
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("IpoNvdDao.InsertIpoNvdLogSql-->" + ex.Message + sql.ToString(), ex);
}
}
