private void updateNewArrival()
{
BLLStockIn obj_BLLStockIn = new BLLStockIn();
try
{
int int_Result = obj_BLLStockIn.UpdateData(arrival);
if (int_Result > 0)
{
MessageBox.Show(UIConstantMessage.Const_strSaveSuccessfully);
BindDataGridView();
clearScreen();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
arrival = new DEStockIn();
obj_BLLStockIn = null;
}
}
private void BindDataGridView()
{
DEStockIn stockIn = new DEStockIn();
stockIn.StockInDate = Convert.ToDateTime(dtp_FilteredInDate.Value.Date.ToString());
BLLStockIn obj_BLLStockIn = new BLLStockIn();
DataTable dt_StockIn;
if (txt_FilteredCode.Text.Trim().Length <= 0)
{
dt_StockIn = obj_BLLStockIn.LoadStockTableForAllData(stockIn);
}
else
{
stockIn.Product_Code = txt_FilteredCode.Text;
dt_StockIn = obj_BLLStockIn.LoadStockTableForAllDataByInDateAndProductCode(stockIn);
}
dgv_ArrivalProduct.DataSource = dt_StockIn;
NumberingTableForDataGridView(dt_StockIn);
FormatDataGridView();
}
public FrmProductArrival(DEStockIn stockIn)
{
InitializeComponent();
arrival = stockIn;
dtp_FilteredInDate.Value = arrival.StockInDate;
this.bindProductCombo();
this.DisplayData(arrival);
}
public void DisplayData(DEStockIn arrival)
{
dtp_InDate.Value = arrival.StockInDate;
cbx_Product.SelectedValue = arrival.Product_Id.ToString();
cbx_Product.Enabled = false;
txt_Description.Text = arrival.Product_Description;
txt_NoOfCartons.Text = arrival.NoOfCartons.ToString();
txt_NoOfUnits.Text = arrival.NoOfUnits.ToString();
}
public DataTable LoadStockTableForAllDataByInDateAndProductCode(DEStockIn stockIn)
{
DALStockIn obj_DALStockIn = new DALStockIn();
DataTable dt_StockIn = obj_DALStockIn.LoadStockTableForAllDataByInDateAndProductCode(stockIn);
obj_DALStockIn = null;
return(dt_StockIn);
}
public DataTable LoadStockTableForAllData(DEStockIn stockIn)
{
DALStockIn obj_DALStockIn = new DALStockIn();
DataTable dt_StockIn = obj_DALStockIn.LoadStockTableForAllData(stockIn);
obj_DALStockIn = null;
return(dt_StockIn);
}
public Boolean LoadStockInRow(DEStockIn stockIn)
{
DALStockIn obj_DALStockIn = new DALStockIn();
Boolean bool_HasRow = obj_DALStockIn.LoadStockInRow(stockIn);
obj_DALStockIn = null;
return(bool_HasRow);
}
public int UpdateData(DEStockIn stockIn)
{
DALStockIn obj_DALStockIn = new DALStockIn();
int int_Result = obj_DALStockIn.UpdateData(stockIn);
obj_DALStockIn = null;
return(int_Result);
}
public DataTable LoadNoOfUnitsOfProductStockIn(DEStockIn stockIn, SqlConnection con, SqlTransaction tn)
{
DataTable dt_StockIn;
SqlCommand sqlCmd = new SqlCommand(" ", con, tn);
sqlCmd.CommandText = "SELECT ((st.NoOfCartons * p.NoOfUnitsPerCarton)+st.NoOfUnits),p.NoOfUnitsPerCarton From tbl_StockIn st LEFT JOIN tbl_Product p ON st.Product_Id = p.Product_Id where st.StockInId = @StockInId";
sqlCmd = DeclareSqlCmdParameter(sqlCmd, stockIn);
dt_StockIn = SqlConjunction.GetSQLTransDataTable(sqlCmd);
return(dt_StockIn);
}
public int UpdateData(DEStockIn stockIn, SqlConnection SqlCon, SqlTransaction tn)
{
int int_Result;
SqlCommand sqlCmd = new SqlCommand(" ", SqlCon, tn);
sqlCmd.CommandText = "Update tbl_StockIn SET StockInId = @StockInId, StockInDate = @StockInDate, Product_Id = @Product_Id, NoOfCartons = @NoOfCartons,NoOfUnits = @NoOfUnits , Carton_Price_Buying = @Carton_Price_Buying, TotalAmount = @TotalAmount,Active = @Active, ModifiedBy = @ModifiedBy,ModifiedDate = @ModifiedDate where StockInId = @StockInId";
DeclareSqlCmdParameter(sqlCmd, stockIn);
int_Result = SqlConjunction.GetSQLTransVoid(sqlCmd);
sqlCmd = null;
return(int_Result);
}
public DataTable LoadStockTableForAllData(DEStockIn stockIn)
{
DataTable dt_StockIn;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "SELECT 0 As 'No',StIn.StockInId,StIn.StockInDate,StIn.Product_Id,P.Product_Code,P.Product_Description,StIn.NoOfCartons,StIn.NoOfUnits FROM tbl_StockIn StIn LEFT JOIN tbl_Product P ON StIn.Product_Id = P.Product_Id where StIn.StockInDate = @StockInDate ";
sqlCmd = DeclareSqlCmdParameter(sqlCmd, stockIn);
dt_StockIn = SqlConjunction.GetSQLDataTable(sqlCmd);
sqlCmd = null;
return(dt_StockIn);
}
private SqlCommand DeclareSqlCmdParameter(SqlCommand SqlCmd, DEStockIn stockIn)
{
SqlCmd.Parameters.AddWithValue("@StockInId", stockIn.StockInId);
SqlCmd.Parameters.AddWithValue("@StockInDate", stockIn.StockInDate);
SqlCmd.Parameters.AddWithValue("@Product_Id", stockIn.Product_Id);
SqlCmd.Parameters.AddWithValue("@Product_Code", stockIn.Product_Code);
SqlCmd.Parameters.AddWithValue("@NoOfCartons", stockIn.NoOfCartons);
SqlCmd.Parameters.AddWithValue("@NoOfUnits", stockIn.NoOfUnits);
SqlCmd.Parameters.AddWithValue("@Carton_Price_Buying", stockIn.Carton_Price_Buying);
SqlCmd.Parameters.AddWithValue("@TotalAmount", stockIn.TotalAmount);
SqlCmd.Parameters.AddWithValue("@Active", stockIn.Active);
SqlCmd.Parameters.AddWithValue("@ModifiedBy", stockIn.ModifiedBy);
SqlCmd.Parameters.AddWithValue("@ModifiedDate", stockIn.ModifiedDate);
return(SqlCmd);
}
public int InsertData(DEStockIn stockIn, SqlConnection SqlCon, SqlTransaction tn)
{
int int_Result;
SqlCommand sqlCmd = new SqlCommand(" ", SqlCon, tn);
sqlCmd.CommandText = "INSERT tbl_StockIn VALUES(@StockInId,@StockInDate,@Product_Id,@NoOfCartons,@NoOfUnits,@Carton_Price_Buying,@TotalAmount,@Active,@ModifiedBy,@ModifiedDate)";
DeclareSqlCmdParameter(sqlCmd, stockIn);
int_Result = SqlConjunction.GetSQLTransVoid(sqlCmd);
sqlCmd = null;
return(int_Result);
}
public Boolean LoadStockInRow(DEStockIn stockIn)
{
Boolean bool_HasRows = false;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "SELECT StIn.StockInId,StIn.StockInDate,StIn.Product_Id,P.Product_Code,P.Product_Description,StIn.NoOfCartons,StIn.NoOfUnits FROM tbl_StockIn StIn LEFT JOIN tbl_Product P ON StIn.Product_Id = P.Product_Id where StIn.StockInId = @StockInId ";
sqlCmd = DeclareSqlCmdParameter(sqlCmd, stockIn);
SqlConnection con = new SqlConnection(SqlConjunction.DataConn);
SqlDataReader sqlDataReader = SqlConjunction.GetSQLExecuteReader(sqlCmd, con);
while (sqlDataReader.Read())
{
stockIn.StockInId = sqlDataReader.GetGuid(0);
stockIn.StockInDate = sqlDataReader.GetDateTime(1);
stockIn.Product_Id = sqlDataReader.GetInt32(2);
stockIn.Product_Code = sqlDataReader.GetString(3);
stockIn.Product_Description = sqlDataReader.GetString(4);
stockIn.NoOfCartons = sqlDataReader.GetInt32(5);
stockIn.NoOfUnits = sqlDataReader.GetInt32(6);
}
if (sqlDataReader.HasRows)
{
bool_HasRows = true;
}
else
{
bool_HasRows = false;
}
con.Close();
sqlCmd = null;
return(bool_HasRows);
}
private void button2_Click(object sender, EventArgs e)
{
if (dgv_Result.CurrentRow != null)
{
DEStockIn stockIn = new DEStockIn();
stockIn.StockInId = new Guid(dgv_Result.CurrentRow.Cells[1].Value.ToString());
stockIn.StockInDate = Convert.ToDateTime(dgv_Result.CurrentRow.Cells[2].Value.ToString());
stockIn.Product_Id = Convert.ToInt32(dgv_Result.CurrentRow.Cells[3].Value.ToString());
stockIn.Product_Code = Convert.ToString(dgv_Result.CurrentRow.Cells[4].Value.ToString());
stockIn.Product_Description = Convert.ToString(dgv_Result.CurrentRow.Cells[5].Value.ToString());
stockIn.NoOfCartons = Convert.ToInt32(dgv_Result.CurrentRow.Cells[6].Value.ToString());
stockIn.NoOfUnits = Convert.ToInt32(dgv_Result.CurrentRow.Cells[7].Value.ToString());
FrmProductArrival frm_ProductArrival = new FrmProductArrival(stockIn, this);
frm_ProductArrival.ShowDialog();
// frm_ProductArrival.DisplayData(stockIn);
}
else
{
MessageBox.Show("Please Select Row");
}
}
private void btn_Reset_Click(object sender, EventArgs e)
{
arrival = new DEStockIn();
clearScreen();
}
public int UpdateData(DEStockIn stockIn)
{
int int_Result = 0;
DALProductInStore obj_DALProductInStore = new DALProductInStore();
DEProductInStore productInStore = new DEProductInStore();
productInStore.Product_Id = stockIn.Product_Id;
SqlConnection SqlCon = new SqlConnection(SqlConjunction.DataConn);
try
{
if (SqlCon.State != ConnectionState.Open)
{
SqlCon.Open();
}
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
SqlTransaction tn = SqlCon.BeginTransaction();
{
try
{
DataTable dt_OriginalNoOfUnitsInStore = obj_DALProductInStore.LoadProductInStoreTableByProductId(productInStore, SqlCon, tn);
if (dt_OriginalNoOfUnitsInStore.Rows.Count > 0)
{
int int_OriginalNoOfUnitsInStore = Convert.ToInt32(dt_OriginalNoOfUnitsInStore.Rows[0][0].ToString());
DataTable dt_StockInOriginalNoOfUnits = this.LoadNoOfUnitsOfProductStockIn(stockIn, SqlCon, tn);
int int_StockInOriginalNoOfUnist = Convert.ToInt32(dt_StockInOriginalNoOfUnits.Rows[0][0].ToString());
int int_NoOfUnitsPerCarton = Convert.ToInt32(dt_StockInOriginalNoOfUnits.Rows[0][1].ToString());
this.UpdateData(stockIn, SqlCon, tn);
productInStore.NoOfUnits = (int_OriginalNoOfUnitsInStore - int_StockInOriginalNoOfUnist) + ((stockIn.NoOfCartons * int_NoOfUnitsPerCarton) + stockIn.NoOfUnits);
obj_DALProductInStore.UpdateData(productInStore, SqlCon, tn);
}
tn.Commit();
int_Result = 1;
}
catch (Exception ex)
{
tn.Rollback();
int_Result = 0;
throw (ex);
}
finally
{
obj_DALProductInStore = null;
SqlCon.Close();
tn.Dispose();
}
return(int_Result);
}
}
public int InsertData(DEStockIn stockIn)
{
int int_Result = 0;
DALProductInStore obj_DALProductInStore = new DALProductInStore();
DEProductInStore productInStore = new DEProductInStore();
productInStore.Product_Id = stockIn.Product_Id;
SqlConnection SqlCon = new SqlConnection(SqlConjunction.DataConn);
try
{
if (SqlCon.State != ConnectionState.Open)
{
SqlCon.Open();
}
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
SqlTransaction tn = SqlCon.BeginTransaction();
{
try
{
this.InsertData(stockIn, SqlCon, tn);
DataTable dt_TempNoOfUnitProductStockIn = this.LoadNoOfUnitsOfProductStockIn(stockIn, SqlCon, tn);
if (dt_TempNoOfUnitProductStockIn.Rows.Count > 0) /// no of units of product stock in ရလာမယ္
{
int int_NoOfUnits = Convert.ToInt32(dt_TempNoOfUnitProductStockIn.Rows[0][0].ToString());
DataTable dt_ProductInStoreTemp = obj_DALProductInStore.LoadProductInStoreTableByProductId(productInStore, SqlCon, tn);
if (dt_ProductInStoreTemp.Rows.Count > 0)
{
int int_NoOfUnitsInStore = Convert.ToInt32(dt_ProductInStoreTemp.Rows[0][0].ToString());
productInStore.NoOfUnits = (int_NoOfUnits + int_NoOfUnitsInStore);
obj_DALProductInStore.UpdateData(productInStore, SqlCon, tn);
}
else
{
productInStore.NoOfUnits = int_NoOfUnits;
obj_DALProductInStore.InsertData(productInStore, SqlCon, tn);
}
}
tn.Commit();
int_Result = 1;
}
catch (Exception ex)
{
tn.Rollback();
int_Result = 0;
throw (ex);
}
finally
{
obj_DALProductInStore = null;
SqlCon.Close();
tn.Dispose();
}
return(int_Result);
}
}