private SqlCommand DeclareSqlCmdParameter(SqlCommand SqlCmd, DEProductInStore productInStore)
{
SqlCmd.Parameters.AddWithValue("@Product_Id", productInStore.Product_Id);
SqlCmd.Parameters.AddWithValue("@NoOfUnits", productInStore.NoOfUnits);
return(SqlCmd);
}
public DataTable LoadProductInStoreTableByProductId(DEProductInStore productInStore)
{
DALProductInStore obj_DALProductInStore = new DALProductInStore();
DataTable dt_ProductInStore = obj_DALProductInStore.LoadProductInStoreTableByProductId(productInStore);
obj_DALProductInStore = null;
return(dt_ProductInStore);
}
public DataTable LoadProductInStoreTableByProductId(DEProductInStore productInStore)
{
DataTable dt_ProductInStore;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "SELECT NoOfUnits From tbl_ProductInStore where Product_Id = @Product_Id";
sqlCmd = DeclareSqlCmdParameter(sqlCmd, productInStore);
dt_ProductInStore = SqlConjunction.GetSQLDataTable(sqlCmd);
return(dt_ProductInStore);
}
public int UpdateData(DEProductInStore productInStore, SqlConnection SqlCon, SqlTransaction tn)
{
int int_Result;
SqlCommand sqlCmd = new SqlCommand(" ", SqlCon, tn);
sqlCmd.CommandText = "Update tbl_ProductInStore SET NoOfUnits = @NoOfUnits where Product_Id = @Product_Id";
DeclareSqlCmdParameter(sqlCmd, productInStore);
int_Result = SqlConjunction.GetSQLTransVoid(sqlCmd);
sqlCmd = null;
return(int_Result);
}
public int InsertData(DEProductInStore productInStore, SqlConnection SqlCon, SqlTransaction tn)
{
int int_Result;
SqlCommand sqlCmd = new SqlCommand(" ", SqlCon, tn);
sqlCmd.CommandText = "INSERT tbl_ProductInStore VALUES(@Product_Id,@NoOfUnits)";
DeclareSqlCmdParameter(sqlCmd, productInStore);
int_Result = SqlConjunction.GetSQLTransVoid(sqlCmd);
sqlCmd = null;
return(int_Result);
}
public int InsertData(DECustomerReturn customerReturn)
{
int int_Result = 0;
DALProductInStore obj_DALProductInStore = new DALProductInStore();
DALProduct obj_DALProduct = new DALProduct();
DEProductInStore productInStore = new DEProductInStore();
productInStore.Product_Id = customerReturn.Product_Id;
DEProduct product = new DEProduct();
product.Product_Id = customerReturn.Product_Id;
obj_DALProduct.LoadProductRow(product);
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(customerReturn, SqlCon, tn);
int int_NoOfUnitsSale = 0;
if (customerReturn.isCarton == true)
{
int_NoOfUnitsSale = (customerReturn.Qty * product.NoOfUnitsPerCarton);
}
else
{
int_NoOfUnitsSale = customerReturn.Qty;
}
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_NoOfUnitsInStore + int_NoOfUnitsSale);
obj_DALProductInStore.UpdateData(productInStore, SqlCon, tn);
}
else
{
productInStore.NoOfUnits = (int_NoOfUnitsSale * (-1));
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);
}
}
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);
}
}
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 CancelDetailInvoice(DEInvoiceDetail invoiceDetail)
{
int int_Result = 0;
DALProductInStore obj_DALProductInStore = new DALProductInStore();
DALProduct obj_DALProduct = new DALProduct();
DEProductInStore productInStore = new DEProductInStore();
productInStore.Product_Id = invoiceDetail.Product_Id;
DEProduct product = new DEProduct();
product.Product_Id = invoiceDetail.Product_Id;
obj_DALProduct.LoadProductRow(product);
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
{
int int_NoOfUnitsSale = 0;
if (invoiceDetail.isCarton)
{
int_NoOfUnitsSale = invoiceDetail.Qty * product.NoOfUnitsPerCarton;
}
else
{
int_NoOfUnitsSale = invoiceDetail.Qty;
}
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_NoOfUnitsInStore + int_NoOfUnitsSale);
obj_DALProductInStore.UpdateData(productInStore, SqlCon, tn);
}
else
{
}
this.updateActiveData(invoiceDetail, 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);
}
}