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); } }