private void BtnSearch_Click(object sender, EventArgs e)
{
try
{
MaterialSearchVO vo = new MaterialSearchVO();
vo.startDate = dtpStartDate.Value.ToShortDateString();
vo.endDate = dtpEndDate.Value.ToShortDateString();
if (cboFactory.Text != "선택")
{
vo.factory_name = cboFactory.Text;
}
if (cboProductType.Text != "선택")
{
vo.product_type = cboProductType.Text;
}
if (txtProduct.Text != "")
{
vo.product_name = txtProduct.Text;
}
StockService service = new StockService();
DataTable dt = service.GetInOutHistory(vo);
SetDataGrid();
dataGridView1.DataSource = dt;
}
catch (Exception err)
{
LoggingUtility.GetLoggingUtility(err.Message, Level.Error);
}
}
/// <summary>
/// 자재입고현황 목록 - warehouse_his로 하는 거 같음
/// </summary>
/// <param name="vo"></param>
/// <returns></returns>
public DataTable GetMaterialInList(MaterialSearchVO vo)
{
using (SqlCommand cmd = new SqlCommand())
{
StringBuilder sql = new StringBuilder();
//sql.Append($"select wh_id, h.w_id, wh_udate, product_codename, product_name, factory_name, company_name, wh_product_count, order_id from TBL_WAREHOUSE_HIS h inner join TBL_PRODUCT p on h.product_id = p.product_id inner join TBL_WAREHOUSE w on h.w_id = w.w_id inner join TBL_FACTORY f on f.factory_id = w.factory_id inner join TBL_COMPANY c on c.company_code = p.product_demand_com where wh_category = 'P_ORDER_IN' order by wh_udate desc");
//sql.Append($"select order_pdate,product_codename, product_name,company_name,o.order_serial, (order_count-order_qcount) as order_qcount, order_count, factory_name from TBL_ORDER o inner join TBL_PRODUCT p on o.product_id = p.product_id inner join TBL_COMPANY c on c.company_code = p.product_demand_com inner join TBL_FACTORY f on f.factory_code = p.product_in_sector where order_state = 'P_COMPLETE'");
sql.Append($"select order_pdate,product_codename, product_name,company_name,o.order_serial, (order_count-order_qcount) as order_qcount, factory_name from TBL_ORDER o inner join TBL_PRODUCT p on o.product_id = p.product_id inner join TBL_COMPANY c on c.company_code = p.product_demand_com inner join TBL_FACTORY f on f.factory_code = p.product_in_sector where CONVERT (DATETIME, order_pdate) >= CONVERT (DATETIME, @startDate) and CONVERT (DATETIME, order_pdate) <= CONVERT (DATETIME, @endDate)");
if (vo.company_name != null)
{
sql.Append(" and company_name = @company_name");
cmd.Parameters.AddWithValue("@company_name", vo.company_name);
}
if (vo.factory_name != null)
{
sql.Append($" and factory_name = @factory_name");
cmd.Parameters.AddWithValue("@factory_name", vo.factory_name);
}
if (vo.order_serial != null)
{
sql.Append($" and o.order_serial like '%{vo.order_serial}%'");
}
if (vo.product_name != null)
{
sql.Append($" and product_name like '%{vo.product_name}%'");
}
cmd.Connection = new SqlConnection(this.ConnectionString);
cmd.CommandText = sql.ToString();
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@startDate", vo.startDate);
cmd.Parameters.AddWithValue("@endDate", vo.endDate);
DataTable dataTable = new DataTable();
cmd.Connection.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
da.Dispose();
cmd.Connection.Close();
return(dataTable);
}
}
private void InOutList_Load(object sender, EventArgs e)
{
try
{
//창고 콤보박스 바인딩
StockService service = new StockService();
List <FactoryComboVO> fList = new List <FactoryComboVO>();
fList = service.GetFactory();
ComboUtil.ComboBinding(cboFactory, fList, "factory_code", "factory_name", "선택");
//품목유형 콤보박스 바인딩
List <CommonVO> productTypeList = new List <CommonVO>();
productTypeList = service.GetProductType("item_type");
ComboUtil.ComboBinding(cboProductType, productTypeList, "common_value", "common_name", "선택");
}
catch (Exception err)
{
LoggingUtility.GetLoggingUtility(err.Message, Level.Error);
}
SetLoad();
try
{
MaterialSearchVO vo = new MaterialSearchVO();
vo.startDate = dtpStartDate.Value.ToShortDateString();
vo.endDate = dtpEndDate.Value.ToShortDateString();
if (cboFactory.Text != "선택")
{
vo.factory_name = cboFactory.Text;
}
if (cboProductType.Text != "선택")
{
vo.product_type = cboProductType.Text;
}
if (txtProduct.Text != "")
{
vo.product_name = txtProduct.Text;
}
StockService service = new StockService();
DataTable dt = service.GetInOutHistory(vo);
SetDataGrid();
dataGridView1.DataSource = dt;
}
catch (Exception err)
{
LoggingUtility.GetLoggingUtility(err.Message, Level.Error);
}
}
public DataTable GetInOutHistory(MaterialSearchVO vo)
{
using (SqlCommand cmd = new SqlCommand())
{
StringBuilder sql = new StringBuilder();
sql.Append($"select wh_id, factory_name, product_codename, c1.common_name as product_type,product_name, order_id, wh_product_count,pp.price_present, (wh_product_count * pp.price_present) as totalprice, wh_udate, wh_comment, c.common_name as wh_category from TBL_WAREHOUSE_HIS h inner join TBL_PRODUCT p on h.product_id = p.product_id inner join TBL_P_PRICE pp on pp.product_id = p.product_id inner join TBL_COMMON_CODE c on c.common_value = h.wh_category inner join TBL_WAREHOUSE w on w.w_id = h.w_id inner join TBL_FACTORY f on f.factory_id = w.factory_id inner join TBL_COMMON_CODE c1 on c1.common_value = p.product_type where pp.price_edate = '9999-12-31' and CONVERT (DATETIME, wh_udate) >= CONVERT (DATETIME, @startDate) and CONVERT (DATETIME, wh_udate) <= CONVERT (DATETIME, @endDate)");
if (vo.factory_name != null)
{
sql.Append(" and factory_name = @factory_name");
cmd.Parameters.AddWithValue("@factory_name", vo.factory_name);
}
if (vo.product_type != null)
{
sql.Append($" and c1.common_name = @product_type");
cmd.Parameters.AddWithValue("@product_type", vo.product_type);
}
if (vo.product_name != null)
{
sql.Append($" and product_name like '%{vo.product_name}%'");
}
sql.Append($" order by wh_udate desc");
cmd.Parameters.AddWithValue("@startDate", vo.startDate);
cmd.Parameters.AddWithValue("@endDate", vo.endDate);
cmd.Connection = new SqlConnection(this.ConnectionString);
cmd.CommandText = sql.ToString();
cmd.CommandType = CommandType.Text;
DataTable dataTable = new DataTable();
cmd.Connection.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
da.Dispose();
cmd.Connection.Close();
return(dataTable);
}
}
private void BtnSearch_Click(object sender, EventArgs e)
{
try
{
MaterialSearchVO vo = new MaterialSearchVO();
vo.startDate = dtpStartDate.Value.ToShortDateString();
vo.endDate = dtpEndDate.Value.ToShortDateString();
if (cboCompany.Text != "선택")
{
vo.company_name = cboCompany.Text;
}
if (cboFactory.Text != "선택")
{
vo.factory_name = cboFactory.Text;
}
if (txtOrderSerial.Text != "")
{
vo.order_serial = txtOrderSerial.Text;
}
if (txtProduct.Text != "")
{
vo.product_name = txtProduct.Text;
}
//조회 버튼
MaterialLedgerService service = new MaterialLedgerService();
DataTable dt = service.GetMaterialInList(vo);
SetDataGrid();
dataGridView1.DataSource = dt;
}
catch (Exception err)
{
LoggingUtility.GetLoggingUtility(err.Message, Level.Error);
}
}
public DataTable GetMaterialInList(MaterialSearchVO vo)
{
MaterialLedgerDAC dac = new MaterialLedgerDAC();
return(dac.GetMaterialInList(vo));
}
public DataTable GetInOutHistory(MaterialSearchVO vo)
{
StockDAC dac = new StockDAC();
return(dac.GetInOutHistory(vo));
}
