static public List<Delivery> GetAvailableDelivery(SqlConnection connect,
ProductInfo prodInfo,
int shipDateoffset)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
List<Delivery> ret = new List<Delivery>();
DateTime now = DateTime.Now;
DateTime shipDate = new DateTime(now.Year, now.Month, now.Day); //now.AddDays(shipDateoffset);
DateTime shipDateEnd = shipDate.AddDays(shipDateoffset);
SqlCommand dbCmd = connect.CreateCommand();
dbCmd.CommandType = CommandType.Text;
dbCmd.CommandText = @"select DeliveryNo, PoNo,Qty from Delivery
where Model=@model and
ShipDate between @ShipDate and @ShipDateEnd and
Qty> 0 and
Status ='00'
order by ShipDate";
SQLHelper.createInputSqlParameter(dbCmd, "@model", 15, prodInfo.Model);
SQLHelper.createInputSqlParameter(dbCmd, "@ShipDate", shipDate);
SQLHelper.createInputSqlParameter(dbCmd, "@ShipDateEnd", shipDateEnd);
logger.DebugFormat("SQL {0}", dbCmd.CommandText);
logger.DebugFormat("SQL {0} {1}", "@model", prodInfo.Model);
logger.DebugFormat("SQL {0} {1}", "@ShipDate", shipDate.ToString());
logger.DebugFormat("SQL {0} {1}", "@ShipDateEnd", shipDateEnd.ToString());
SqlDataReader sdr = dbCmd.ExecuteReader();
while (sdr.Read())
{
Delivery delivery = new Delivery();
delivery.DeliveryNo = sdr.GetString(0).Trim();
delivery.PO = sdr.GetString(1).Trim();
delivery.Qty = sdr.GetInt32(2);
ret.Add(delivery);
}
sdr.Close();
return ret;
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
static public List<Delivery> GetAvailableDeliveryByCustPo(SqlConnection connect,
ProductInfo prodInfo,
int shipDateoffset)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
List<Delivery> ret = new List<Delivery>();
DateTime now = DateTime.Now;
DateTime shipDate = new DateTime(now.Year, now.Month, now.Day); //now.AddDays(shipDateoffset);
DateTime shipDateEnd = shipDate.AddDays(shipDateoffset);
SqlCommand dbCmd = connect.CreateCommand();
dbCmd.CommandType = CommandType.Text;
// dbCmd.CommandText = @"select DeliveryNo, PoNo,Qty from Delivery
// where Model=@model and
// ShipDate between @ShipDate and @ShipDateEnd and
// Qty> 0 and
// Status ='00'
// order by ShipDate";
dbCmd.CommandText = @"select a.DeliveryNo, b.FactoryPO, a.Qty , a.PoNo
from Delivery a,
DeliveryInfo c,
SpecialOrder b
where a.DeliveryNo =c.DeliveryNo and
c.InfoType='CustPo' and
c.InfoValue= b.FactoryPO and
b.Status in ('Created','Active') and
a.Model=@model and
a.ShipDate between @ShipDate and @ShipDateEnd and
a.Qty> 0 and
a.Status ='00'
order by ShipDate";
SQLHelper.createInputSqlParameter(dbCmd, "@model", 15, prodInfo.Model);
SQLHelper.createInputSqlParameter(dbCmd, "@ShipDate", shipDate);
SQLHelper.createInputSqlParameter(dbCmd, "@ShipDateEnd", shipDateEnd);
logger.DebugFormat("SQL {0}", dbCmd.CommandText);
logger.DebugFormat("SQL {0} {1}", "@model", prodInfo.Model);
logger.DebugFormat("SQL {0} {1}", "@ShipDate", shipDate.ToString());
logger.DebugFormat("SQL {0} {1}", "@ShipDateEnd", shipDateEnd.ToString());
SqlDataReader sdr = dbCmd.ExecuteReader();
while (sdr.Read())
{
Delivery delivery = new Delivery();
delivery.DeliveryNo = sdr.GetString(0).Trim();
delivery.PO = sdr.GetString(1).Trim();
delivery.Qty = sdr.GetInt32(2);
delivery.CustPo = delivery.PO;
delivery.HpPo = sdr.GetString(3).Trim();
delivery.isCustPo = true;
ret.Add(delivery);
}
sdr.Close();
return ret;
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
