private void btnSelect_Click(object sender, EventArgs e) { dgvStockStatus.Columns.Clear(); dgvStockStatus.DataSource = null; //조회 버튼 try { InventoryOrderMgtVO vo = new InventoryOrderMgtVO(); vo.start_date = dtpFromDate.Value.ToShortDateString(); vo.end_date = dtpToDate.Value.ToShortDateString(); if (cboFromFac.Text != "선택") { vo.fromFactory = cboFromFac.SelectedValue.ToString(); } if (txtProduct.Text != "") { vo.product_name = txtProduct.Text.Trim(); } ShipmentService service_shipment = new ShipmentService(); shipment_list = service_shipment.GetInventoryStatusByOrder(vo); SetDGV(_cboToFac); SetBottomStatusLabel($"{shipment_list.Count}건이 조회되었습니다."); } catch (Exception err) { LoggingUtility.GetLoggingUtility(err.Message, Level.Error); SetBottomStatusLabel("다시 검색하세요."); } }
/// <summary> /// 고객별 주문현황 /// </summary> /// <returns></returns> public List <ShipmentVO> GetInventoryStatusByOrder(InventoryOrderMgtVO vo) { using (SqlCommand cmd = new SqlCommand()) { StringBuilder sql = new StringBuilder(); sql.Append("select so_id,s.plan_id,company_code,company_type,p.product_name,p.product_id" + ", s.product_name as product_codename,so_ocount,so_ccount,so_edate,so_sdate" + ",(" + "select distinct factory_name from TBL_WAREHOUSE w inner join TBL_FACTORY f " + "on w.factory_id = f.factory_id where f.factory_id=5" + ") as from_wh" + ",(select distinct factory_type from TBL_WAREHOUSE w inner join TBL_FACTORY f " + "on w.factory_id = f.factory_id where f.factory_id=5) as from_wh_value" + ", w.w_count_present,so_pcount- so_ccount as so_pcount ,s.so_wo_id " + "from TBL_SO_MASTER s " + "inner join TBL_PRODUCT p on s.product_name = p.product_codename " + "inner join TBL_WAREHOUSE w on s.plan_id = w.plan_id " + "inner join TBL_FACTORY f on w.factory_id = f.factory_id where f.factory_id=" + "( select factory_id from TBL_FACTORY where factory_type='FAC400')" + " and CONVERT(DATETIME, so_edate) >= CONVERT(DATETIME, @startDate) " + "and CONVERT(DATETIME, so_edate) <= CONVERT(DATETIME, @endDate)"); if (vo.fromFactory != null) { sql.Append(" and f.factory_code = @from_wh"); cmd.Parameters.AddWithValue("@from_wh", vo.fromFactory); } if (vo.product_name != null) { sql.Append($" and p.product_name like '%{vo.product_name}%'"); // cmd.Parameters.AddWithValue("@order_id", vo.order_id); } sql.Append(" order by so_edate"); cmd.Parameters.AddWithValue("@startDate", vo.start_date); cmd.Parameters.AddWithValue("@endDate", vo.end_date); //string sql = "GetInventoryStatusByOrder"; cmd.Connection = new SqlConnection(this.ConnectionString); cmd.CommandText = sql.ToString(); cmd.CommandType = CommandType.Text; cmd.Connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); List <ShipmentVO> list = Helper.DataReaderMapToList <ShipmentVO>(reader); cmd.Connection.Close(); return(list); } }
public List <ShipmentVO> GetInventoryStatusByOrder(InventoryOrderMgtVO vo) { ShipmentDac dac = new ShipmentDac(); return(dac.GetInventoryStatusByOrder(vo)); }