private void BindDataGridView()
{
DECustomerReturn customerReturnDetail = new DECustomerReturn();
//customerReturnDetail.ReturnDate = Convert.ToDateTime(dtp_ReturnDate.Text);
DateTime dateTime_From = Convert.ToDateTime(dtp_ReturnDate.Text);
DateTime dateTime_To = Convert.ToDateTime(dtp_ReturnDate.Text).AddHours(23.9);
try
{
customerReturnDetail.Customer_Id = Convert.ToInt32(cbx_Customer.SelectedItem.Col4);
}
catch (Exception ex)
{
return;
}
BLLCustomerReturn obj_BLLCustomerReturnDetail = new BLLCustomerReturn();
DataTable dt_CustomerReturnDetail;
dt_CustomerReturnDetail = obj_BLLCustomerReturnDetail.LoadCustomerReturnDetailTableForAllDataByReturn(dateTime_From, dateTime_To, customerReturnDetail);
dgv_ProductList.DataSource = dt_CustomerReturnDetail;
NumberingTableForDataGridView(dt_CustomerReturnDetail);
this.dt_ProductList = dt_CustomerReturnDetail;
FormatDataGridView();
calculateTotal(dt_CustomerReturnDetail);
}
public int DeleteData(DECustomerReturn cusReturn)
{
DALCustomerReturn obj_DALCustomerReturn = new DALCustomerReturn();
int int_Result = obj_DALCustomerReturn.DeleteData(cusReturn);
obj_DALCustomerReturn = null;
return(int_Result);
}
private void save()
{
invoiceReturnDetail.CustomerReturn_Id = Guid.NewGuid();
invoiceReturnDetail.Customer_Id = Convert.ToInt32(cbx_Customer.SelectedItem.Col4);
invoiceReturnDetail.ReturnDate = Convert.ToDateTime(dtp_ReturnDate.Value);
invoiceReturnDetail.Product_Id = Convert.ToInt32(cbx_Product.SelectedItem.Col3);
if (rdo_isCarton.Checked == true)
{
invoiceReturnDetail.isCarton = true;
}
else
{
invoiceReturnDetail.isCarton = false;
}
invoiceReturnDetail.Qty = Convert.ToInt32(txt_Quantity.Text);
invoiceReturnDetail.Price = Convert.ToDecimal(txt_Rate.Text);
invoiceReturnDetail.Amount = Convert.ToDecimal(txt_Amount.Text);
invoiceReturnDetail.ModifiedDate = Convert.ToDateTime(lbl_Date.Text + " " + lbl_Time.Text);
BLLCustomerReturn obj_BLLInvoiceReturnDetail = new BLLCustomerReturn();
try
{
int int_Result = obj_BLLInvoiceReturnDetail.InsertData(invoiceReturnDetail);
if (int_Result > 0)
{
BindDataGridView();
clearProductDetail();
if (stockIn != null)
{
stockIn.RSearch();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
invoiceReturnDetail = new DECustomerReturn();
obj_BLLInvoiceReturnDetail = null;
cbx_Product.Focus();
}
}
public int InsertData(DECustomerReturn customerReturn, SqlConnection SqlCon, SqlTransaction tn)
{
int int_Result;
SqlCommand sqlCmd = new SqlCommand(" ", SqlCon, tn);
sqlCmd.CommandText = "INSERT tbl_CustomerReturn VALUES(@CustomerReturn_Id,@Customer_Id,@ReturnDate,@Product_Id,@isCarton,@Qty,@Price,@Amount,@Remark,@Active,@ModifiedBy,@ModifiedDate)";
DeclareSqlCmdParameter(sqlCmd, customerReturn);
int_Result = SqlConjunction.GetSQLTransVoid(sqlCmd);
sqlCmd = null;
return(int_Result);
}
private int DeleteData(DECustomerReturn cusReturn, SqlConnection SqlCon, SqlTransaction tn)
{
int int_Result;
SqlCommand sqlCmd = new SqlCommand(" ", SqlCon, tn);
sqlCmd.CommandText = "Delete From tbl_CustomerReturn where CustomerReturn_Id = @CustomerReturn_Id";
DeclareSqlCmdParameter(sqlCmd, cusReturn);
int_Result = SqlConjunction.GetSQLTransVoid(sqlCmd);
sqlCmd = null;
return(int_Result);
}
private SqlCommand DeclareSqlCmdParameter(SqlCommand SqlCmd, DECustomerReturn customerReturn)
{
SqlCmd.Parameters.AddWithValue("@CustomerReturn_Id", customerReturn.CustomerReturn_Id);
SqlCmd.Parameters.AddWithValue("@Customer_Id", customerReturn.Customer_Id);
SqlCmd.Parameters.AddWithValue("@ReturnDate", customerReturn.ReturnDate);
SqlCmd.Parameters.AddWithValue("@Product_Id", customerReturn.Product_Id);
SqlCmd.Parameters.AddWithValue("@isCarton", customerReturn.isCarton);
SqlCmd.Parameters.AddWithValue("@Qty", customerReturn.Qty);
SqlCmd.Parameters.AddWithValue("@Price", customerReturn.Price);
SqlCmd.Parameters.AddWithValue("@Amount", customerReturn.Amount);
SqlCmd.Parameters.AddWithValue("@Remark", customerReturn.Remark);
SqlCmd.Parameters.AddWithValue("@Active", customerReturn.Active);
SqlCmd.Parameters.AddWithValue("@ModifiedBy", customerReturn.ModifiedBy);
SqlCmd.Parameters.AddWithValue("@ModifiedDate", customerReturn.ModifiedDate);
return(SqlCmd);
}
private void delete()
{
invoiceReturnDetail.CustomerReturn_Id = new Guid(dgv_ProductList.CurrentRow.Cells[1].Value.ToString());
invoiceReturnDetail.Product_Id = Convert.ToInt32(dgv_ProductList.CurrentRow.Cells[4].Value.ToString());
if (dgv_ProductList.CurrentRow.Cells[8].Value.ToString().Equals("Cartons"))
{
invoiceReturnDetail.isCarton = true;
}
else
{
invoiceReturnDetail.isCarton = false;
}
invoiceReturnDetail.Qty = Convert.ToInt32(dgv_ProductList.CurrentRow.Cells[7].Value.ToString());
invoiceReturnDetail.Price = Convert.ToDecimal(dgv_ProductList.CurrentRow.Cells[9].Value.ToString());
invoiceReturnDetail.Amount = Convert.ToDecimal(dgv_ProductList.CurrentRow.Cells[10].Value.ToString());
BLLCustomerReturn obj_BLLCustomerReturn = new BLLCustomerReturn();
try
{
int int_Result = obj_BLLCustomerReturn.DeleteData(invoiceReturnDetail);
if (int_Result > 0)
{
BindDataGridView();
clearProductDetail();
if (stockIn != null)
{
stockIn.RSearch();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
invoiceReturnDetail = new DECustomerReturn();
obj_BLLCustomerReturn = null;
cbx_Product.Focus();
}
}
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 DataTable LoadCustomerReturnDetailTableForAllDataByReturnDateAndCustomerID(DateTime dateTime_From, DateTime dateTime_To, DECustomerReturn cusRetDetail)
{
DataTable dt_CustomerReturn;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "SELECT 0 As 'RNo',cusR.CustomerReturn_Id as 'cusR_Id',cusR.Customer_Id, cusR.ReturnDate,cusR.Product_Id as 'RProduct_Id',P.Product_Code as 'RProduct_Code',P.Product_Description as 'RProduct_Description',cusR.Qty as 'RQty',CASE WHEN cusR.isCarton = 'True' then 'Cartons' ELSE 'Pcs' END as 'RUnits',cusR.Price as 'RRate',cusR.Amount as 'RAmount',cusR.Active FROM tbl_CustomerReturn cusR LEFT JOIN tbl_Product P ON cusR.Product_Id = P.Product_Id where cusR.ReturnDate <='" + dateTime_To + "' And cusR.ReturnDate >= '" + dateTime_From + "' and cusR.Customer_Id = @Customer_Id order by cusR.ModifiedDate";
sqlCmd = DeclareSqlCmdParameter(sqlCmd, cusRetDetail);
dt_CustomerReturn = SqlConjunction.GetSQLDataTable(sqlCmd);
sqlCmd = null;
return(dt_CustomerReturn);
}
public DataTable LoadCustomerReturnDetailTableForAllDataByReturn(DateTime dateTime_From, DateTime dateTime_To, DECustomerReturn cusRetDetail)
{
DALCustomerReturn obj_DALCustomerReturn = new DALCustomerReturn();
DataTable dt_CustomerReturn = obj_DALCustomerReturn.LoadCustomerReturnDetailTableForAllDataByReturnDateAndCustomerID(dateTime_From, dateTime_To, cusRetDetail);
obj_DALCustomerReturn = null;
return(dt_CustomerReturn);
}