public IEnumerable <CostPrice> GetCostPrices(DateTime fromdt, DateTime todt, string fromprdct, string toprdct, string fromsupp, string tosupp, string fromdept, string todept)
{
List <CostPrice> costPrices = new List <CostPrice>();
using (SqlConnection con = new SqlConnection(DbCon.connection))
{
try
{
string query = "select c.PROD_CD,p.ProdNm,c.BRCH_CD, p.ProdNm, c.INT_CP,c.NW_CP,c.USR_NM,c.CHG_DT from TBLCPHIST c inner join TblProd p on c.PROD_CD=p.ProdCd where PROD_CD between @fromprdct and @toprdct and SuppCd between @fromsupp and @tosupp and DeptCd between @fromdept and @todept and c.CHG_DT between @fromdate and @todate";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@fromprdct", fromprdct);
cmd.Parameters.AddWithValue("@toprdct", toprdct);
cmd.Parameters.AddWithValue("@fromdate", fromdt.Date);
cmd.Parameters.AddWithValue("@todate", todt.Date.AddHours(23).AddMinutes(59).AddSeconds(59));
cmd.Parameters.AddWithValue("@fromsupp", fromsupp);
cmd.Parameters.AddWithValue("@tosupp", tosupp);
cmd.Parameters.AddWithValue("@fromdept", fromdept);
cmd.Parameters.AddWithValue("@todept", todept);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataReader sql = cmd.ExecuteReader();
if (!sql.HasRows)
{
return(costPrices);
}
else
{
while (sql.Read())
{
CostPrice costPrice = new CostPrice();
costPrice.ProdCd = sql["PROD_CD"].ToString();
costPrice.ProdNm = sql["ProdNm"].ToString();
costPrice.Old = (decimal)sql["INT_CP"];
costPrice.New = (decimal)sql["Nw_Cp"];
costPrice.Brch_Cd = sql["BRCH_CD"].ToString();
costPrice.Chg_Dt = (DateTime)sql["Chg_Dt"];
costPrice.Usr_Nm = sql["USR_NM"].ToString();
costPrices.Add(costPrice);
}
}
}
catch (Exception exe)
{
MessageBox.Show(exe.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
return(costPrices);
}
public bool ChangeCP(CostPrice product)
{
using (SqlConnection con = new SqlConnection(DbCon.connection))
{
try
{
string updateproduct = "Update tblprod set Cp =@cp where prodcd =@prodcd";
string updatecpchange = "insert into tblcphist (PROD_CD,INT_CP,NW_CP,USR_NM,CHG_DT,BRCH_CD,CMPY_CD) VALUES(@PROD_CD,@INT_CP,@NW_CP,@USR_NM,@CHG_DT,@BRCH_CD,@CMPY_CD)";
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlTransaction transaction = con.BeginTransaction();
SqlCommand sql1 = new SqlCommand(updateproduct, con);
sql1.Parameters.AddWithValue("@cp", product.New);
sql1.Parameters.AddWithValue("@prodcd", product.ProdCd);
sql1.Transaction = transaction;
SqlCommand sql2 = new SqlCommand(updatecpchange, con);
sql2.Parameters.AddWithValue("@PROD_CD", product.ProdCd);
sql2.Parameters.AddWithValue("@INT_CP", product.Old);
sql2.Parameters.AddWithValue("@NW_CP", product.New);
sql2.Parameters.AddWithValue("@USR_NM", Properties.Settings.Default.USERNAME);
sql2.Parameters.AddWithValue("@BRCH_CD", "NRK");
sql2.Parameters.AddWithValue("@CMPY_CD", "TT");
sql2.Parameters.AddWithValue("@CHG_DT", DateTime.Now);
sql2.Transaction = transaction;
try
{
sql1.ExecuteNonQuery();
sql2.ExecuteNonQuery();
transaction.Commit();
return(true);
}
catch (Exception exe)
{
transaction.Rollback();
return(false);
}
}
catch (Exception)
{
return(false);
}
}
}