public DataTable GetAseldTablePDF(AseldQuery aseld)
{
aseld.Replace4MySQL();
StringBuilder sbStr = new StringBuilder();
try
{
sbStr.AppendFormat("SELECT a.assg_id FROM aseld a WHERE a.wust_id<>'COM'");
if (aseld.start_dtim != DateTime.MinValue)
{
sbStr.AppendFormat(" AND a.create_dtim between '{0}' and '{1}'", CommonFunction.DateTimeToString(aseld.start_dtim), CommonFunction.DateTimeToString(aseld.change_dtim));
}
if (aseld.assg_id != string.Empty)
{
sbStr.AppendFormat(" and a.assg_id='{0}'", aseld.assg_id);
}
sbStr.Append(" GROUP BY a.assg_id;");
return _access.getDataTable(sbStr.ToString());
}
catch (Exception ex)
{
throw new Exception("AseldDao.GetAseldTablePDF-->" + ex.Message + sbStr.ToString(), ex);
}
}
public DataTable GetAseldTable(AseldQuery ase,out int total)
{
ase.Replace4MySQL();
total = 0;
string sql = "";
StringBuilder strAll = new StringBuilder();
StringBuilder strWhr = new StringBuilder();
StringBuilder strLimit=new StringBuilder();
StringBuilder strJoin=new StringBuilder();
strAll.Append("SELECT a.assg_id,p.product_id,p.product_name,pi.item_id,ps.spec_name AS spec,ps2.spec_name as spec_name2 ,SUM(out_qty)out_qty,SUM(act_pick_qty)act_pick_qty,SUM(a.ord_qty)ord_qty, a.create_dtim,");
strAll.Append(" case ic.lcat_id when 'S' then i.loc_id else IFNULL(ic.lcat_id,case p.product_mode when 2 then 'YY999999' when 3 then 'ZZ999999' else i.loc_id end ) end as loc_id,i.loc_id as loc_id1, ");
strAll.Append(" temp.parameterName,ic.lcat_id,ic.lcat_id as upc_id FROM aseld a ");
strJoin.Append(" inner JOIN product_item pi ON a.item_id=pi.item_id");
strJoin.Append(" inner JOIN product p ON p.product_id=pi.product_id");
strJoin.Append(" LEFT JOIN iplas i ON pi.item_id=i.item_id");
strJoin.Append(" left JOIN iloc ic ON ic.loc_id=i.loc_id");
strJoin.Append(" LEFT JOIN product_spec ps ON pi.spec_id_1= ps.spec_id ");
strJoin.Append(" LEFT JOIN product_spec ps2 ON pi.spec_id_2= ps2.spec_id");
strJoin.Append(" LEFT JOIN (select parameterCode,parameterName from t_parametersrc where parameterType ='product_mode') temp ON p.product_mode=temp.parameterCode");
strWhr.Append(" WHERE a.wust_id<>'COM'");
if (!string.IsNullOrEmpty(ase.assg_id))
{
strWhr.AppendFormat(" and a.assg_id='{0}' ", ase.assg_id);
}
if (ase.start_dtim!=DateTime.MinValue)
{
strWhr.AppendFormat(" and a.create_dtim between '{0}' and '{1}'", CommonFunction.DateTimeToString(ase.start_dtim), CommonFunction.DateTimeToString(ase.change_dtim));
}
strWhr.Append(" GROUP BY a.item_id,a.assg_id ORDER BY loc_id asc ");
if(ase.IsPage)
{
total = Convert.ToInt32(_access.getDataTable("SELECT count(item_id) FROM(SELECT a.item_id,case ic.lcat_id when 'S' then i.loc_id else IFNULL(ic.lcat_id,case p.product_mode when 2 then 'YY999999' when 3 then 'ZZ999999' else i.loc_id end ) end as loc_id FROM aseld a " + strJoin.ToString() + strWhr.ToString() + ") temp").Rows[0][0]);
strLimit.AppendFormat(" LIMIT {0},{1};",ase.Start,ase.Limit);
}
try
{
sql = strAll.ToString()+strJoin.ToString()+strWhr.ToString() + strLimit.ToString();
DataTable _dt = _access.getDataTable(sql);
foreach (DataRow item in _dt.Rows)
{
item["spec"] = string.IsNullOrEmpty(item["spec"].ToString()) ? item["spec_name2"] : (string.IsNullOrEmpty(item["spec_name2"].ToString()) ? item["spec"].ToString() : item["spec"].ToString() + "-" + item["spec_name2"].ToString());
}
return _dt;
}
catch (Exception ex)
{
throw new Exception("AseldDao.GetAseldTable-->" + ex.Message + sql, ex);
}
}