public DataTable LoadInvoiceReturnDetailTableForAllDataByInvoiceDate(DEInvoiceReturnDetail invoiceReturnDetail, DateTime dateTime_From, DateTime dateTime_To)
{
DALInvoiceReturnDetail obj_InvoiceReturnDetail = new DALInvoiceReturnDetail();
DataTable dt_InvoiceReturn = obj_InvoiceReturnDetail.LoadInvoiceReturnDetailTableForAllDataByInvoiceDate(invoiceReturnDetail, dateTime_From, dateTime_To);
obj_InvoiceReturnDetail = null;
return(dt_InvoiceReturn);
}
public int CancelReturnInvoice(DEInvoiceReturnDetail invoiceDetail)
{
DALInvoiceReturnDetail obj_DALInvoiceReturnDetail = new DALInvoiceReturnDetail();
int int_Result = obj_DALInvoiceReturnDetail.CancelReturnInvoice(invoiceDetail);
obj_DALInvoiceReturnDetail = null;
return(int_Result);
}
public int DeleteData(DEInvoiceReturnDetail invRetDetail)
{
DALInvoiceReturnDetail obj_DALInvoiceReturnDetail = new DALInvoiceReturnDetail();
int int_Result = obj_DALInvoiceReturnDetail.DeleteData(invRetDetail);
obj_DALInvoiceReturnDetail = null;
return(int_Result);
}
public DataTable LoadInvoiceReturnDetailTableForAllDataByInvoiceId(DEInvoiceReturnDetail invRetDetail)
{
DALInvoiceReturnDetail obj_InvoiceReturnDetail = new DALInvoiceReturnDetail();
DataTable dt_InvoiceReturn = obj_InvoiceReturnDetail.LoadInvoiceReturnDetailTableForAllDataByInvoiceId(invRetDetail);
obj_InvoiceReturnDetail = null;
return(dt_InvoiceReturn);
}
private SqlCommand DeclareSqlCmdParameter(SqlCommand SqlCmd, DEInvoiceReturnDetail invRetDetail)
{
SqlCmd.Parameters.AddWithValue("@InvoiceReturnDetail_Id", invRetDetail.InvoiceReturnDetail_Id);
SqlCmd.Parameters.AddWithValue("@Invoice_Id", invRetDetail.Invoice_Id);
SqlCmd.Parameters.AddWithValue("@Product_Id", invRetDetail.Product_Id);
SqlCmd.Parameters.AddWithValue("@isCarton", invRetDetail.isCarton);
SqlCmd.Parameters.AddWithValue("@Qty", invRetDetail.Qty);
SqlCmd.Parameters.AddWithValue("@Price", invRetDetail.Price);
SqlCmd.Parameters.AddWithValue("@Amount", invRetDetail.Amount);
SqlCmd.Parameters.AddWithValue("@Active", invRetDetail.Active);
SqlCmd.Parameters.AddWithValue("@ModifiedDate", invRetDetail.ModifiedDate);
return(SqlCmd);
}
private int InsertData(DEInvoiceReturnDetail invRetDetail, SqlConnection SqlCon, SqlTransaction tn)
{
int int_Result;
SqlCommand sqlCmd = new SqlCommand(" ", SqlCon, tn);
sqlCmd.CommandText = "INSERT tbl_InvoiceReturnDetail VALUES(@InvoiceReturnDetail_Id,@Invoice_Id,@Product_Id,@isCarton,@Qty,@Price,@Amount,@Active,@ModifiedDate)";
DeclareSqlCmdParameter(sqlCmd, invRetDetail);
int_Result = SqlConjunction.GetSQLTransVoid(sqlCmd);
sqlCmd = null;
return(int_Result);
}
public DataTable LoadInvoiceReturnDetailTableForAllDataByInvoiceDate(DEInvoiceReturnDetail invoiceReturnDetail, DateTime dateTime_From, DateTime dateTime_To)
{
DataTable dt_InvoiceDetail;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "SELECT 0 As 'RNo',invD.InvoiceReturnDetail_Id as 'RInvoiceDetail_Id',invD.Invoice_Id as 'RInvoice_Id',invD.Product_Id as 'RProduct_Id',P.Product_Code as 'RProduct_Code',P.Product_Description as 'RProduct_Description',invD.Qty as 'RQty',CASE WHEN invD.isCarton = 'True' then 'Cartons' ELSE 'Pcs' END as 'RUnits',invD.Price as 'RRate',invD.Amount as 'RAmount',invD.Active,invT.InvoiceType_Id as 'RInvoiceType_Id',invT.InvoiceType_Description as 'RInvoiceType_Description',Cus.Customer_Id as 'RCustomer_Id',Cus.Customer_Description as 'RCustomer_Description',inv.Invoice_No as 'RInvoice_No',inv.Invoice_Date as 'RInvoice_Date' FROM tbl_InvoiceReturnDetail invD LEFT JOIN tbl_Product P ON invD.Product_Id = P.Product_Id LEFT JOIN tbl_Invoice inv ON invD.Invoice_Id = inv.Invoice_Id LEFT JOIN tbl_Customer Cus ON inv.Customer_Id = Cus.Customer_Id LEFT JOIN tbl_InvoiceType invT ON inv.InvoiceType_Id = invT.InvoiceType_Id where (invD.Product_Id = @Product_Id) And (inv.Invoice_Date <='" + dateTime_To + "' And inv.Invoice_Date >= '" + dateTime_From + "') And (invD.Active = 'true') order by inv.Invoice_Date";
sqlCmd = DeclareSqlCmdParameter(sqlCmd, invoiceReturnDetail);
dt_InvoiceDetail = SqlConjunction.GetSQLDataTable(sqlCmd);
sqlCmd = null;
return(dt_InvoiceDetail);
}
private int updateActiveData(DEInvoiceReturnDetail invoiceDetail, SqlConnection SqlCon, SqlTransaction tn)
{
int int_Result;
SqlCommand sqlCmd = new SqlCommand(" ", SqlCon, tn);
sqlCmd.CommandText = "Update tbl_InvoiceReturnDetail Set Active = @Active where InvoiceReturnDetail_Id = @InvoiceReturnDetail_Id";
DeclareSqlCmdParameter(sqlCmd, invoiceDetail);
int_Result = SqlConjunction.GetSQLTransVoid(sqlCmd);
sqlCmd = null;
return(int_Result);
}
public DataTable LoadInvoiceReturnDetailTableForAllDataByInvoiceId(DEInvoiceReturnDetail invRetDetail)
{
DataTable dt_InvoiceReturnDetail;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "SELECT 0 As 'RNo',invRD.InvoiceReturnDetail_Id as 'RInvoiceDetail_Id',invRD.Invoice_Id as 'RInvoice_Id',invRD.Product_Id as 'RProduct_Id',P.Product_Code as 'RProduct_Code',P.Product_Description as 'RProduct_Description',invRD.Qty as 'RQty',CASE WHEN invRD.isCarton = 'True' then 'Cartons' ELSE 'Pcs' END as 'RUnits',invRD.Price as 'RRate',invRD.Amount as 'RAmount',invRD.Active FROM tbl_InvoiceReturnDetail invRD LEFT JOIN tbl_Product P ON invRD.Product_Id = P.Product_Id where invRD.Invoice_Id = @Invoice_Id order by invRD.ModifiedDate";
sqlCmd = DeclareSqlCmdParameter(sqlCmd, invRetDetail);
dt_InvoiceReturnDetail = SqlConjunction.GetSQLDataTable(sqlCmd);
sqlCmd = null;
return(dt_InvoiceReturnDetail);
}
private void save()
{
invoiceReturnDetail.InvoiceReturnDetail_Id = Guid.NewGuid();
invoiceReturnDetail.Invoice_Id = invoice.Invoice_Id;
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);
BLLInvoiceReturnDetail obj_BLLInvoiceReturnDetail = new BLLInvoiceReturnDetail();
try
{
int int_Result = obj_BLLInvoiceReturnDetail.InsertData(invoiceReturnDetail);
if (int_Result > 0)
{
BindDataGridView();
clearProductDetail();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
invoiceReturnDetail = new DEInvoiceReturnDetail();
obj_BLLInvoiceReturnDetail = null;
cbx_Product.Focus();
}
}
private void delete()
{
invoiceReturnDetail.InvoiceReturnDetail_Id = new Guid(dgv_ProductList.CurrentRow.Cells[1].Value.ToString());
invoiceReturnDetail.Invoice_Id = new Guid(dgv_ProductList.CurrentRow.Cells[2].Value.ToString());
invoiceReturnDetail.Product_Id = Convert.ToInt32(dgv_ProductList.CurrentRow.Cells[3].Value.ToString());
if (dgv_ProductList.CurrentRow.Cells[7].Value.ToString().Equals("Cartons"))
{
invoiceReturnDetail.isCarton = true;
}
else
{
invoiceReturnDetail.isCarton = false;
}
invoiceReturnDetail.Qty = Convert.ToInt32(dgv_ProductList.CurrentRow.Cells[6].Value.ToString());
invoiceReturnDetail.Price = Convert.ToDecimal(dgv_ProductList.CurrentRow.Cells[8].Value.ToString());
invoiceReturnDetail.Amount = Convert.ToDecimal(dgv_ProductList.CurrentRow.Cells[9].Value.ToString());
BLLInvoiceReturnDetail obj_BLLInvoiceReturnDetail = new BLLInvoiceReturnDetail();
try
{
int int_Result = obj_BLLInvoiceReturnDetail.DeleteData(invoiceReturnDetail);
if (int_Result > 0)
{
BindDataGridView();
clearProductDetail();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
invoiceReturnDetail = new DEInvoiceReturnDetail();
obj_BLLInvoiceReturnDetail = null;
cbx_Product.Focus();
}
}
private void search()
{
BLLInvoiceDetail obj_BLLInvoiceDetail = new BLLInvoiceDetail();
BLLInvoiceReturnDetail obj_BLLInvoiceReturnDetail = new BLLInvoiceReturnDetail();
BLLProduct obj_BLLProduct = new BLLProduct();
DEInvoiceDetail invDetail = new DEInvoiceDetail();
DEInvoiceReturnDetail invRDetail = new DEInvoiceReturnDetail();
DEProduct product = new DEProduct();
DateTime dateTime_From = Convert.ToDateTime(dtp_InvoiceDateFrom.Value);
DateTime dateTime_To = Convert.ToDateTime(dtp_InvoiceDateTo.Value.Date.AddHours(23.9));
try
{
invDetail.Product_Id = Convert.ToInt32(cbx_Product.SelectedItem.Col3);
invRDetail.Product_Id = Convert.ToInt32(cbx_Product.SelectedItem.Col3);
product.Product_Id = Convert.ToInt32(cbx_Product.SelectedItem.Col3);
}
catch (Exception ex)
{
invDetail.Product_Id = 0;
invRDetail.Product_Id = 0;
product.Product_Id = 0;
}
DataTable dt_Sale = new DataTable();
DataTable dt_Return = new DataTable();
dt_Sale = obj_BLLInvoiceDetail.LoadInvoiceDetailTableForAllDataByInvoiceDate(invDetail, dateTime_From, dateTime_To);
dt_Return = obj_BLLInvoiceReturnDetail.LoadInvoiceReturnDetailTableForAllDataByInvoiceDate(invRDetail, dateTime_From, dateTime_To);
obj_BLLProduct.LoadProductRow(product);
bindReport(product, dt_Sale, dt_Return);
}
private void BindDataGridView()
{
DEInvoiceReturnDetail invoiceReturnDetail = new DEInvoiceReturnDetail();
invoiceReturnDetail.Invoice_Id = invoice.Invoice_Id;
BLLInvoiceReturnDetail obj_BLLInvoiceReturnDetail = new BLLInvoiceReturnDetail();
DataTable dt_InvoiceReturnDetail;
dt_InvoiceReturnDetail = obj_BLLInvoiceReturnDetail.LoadInvoiceReturnDetailTableForAllDataByInvoiceId(invoiceReturnDetail);
dgv_ProductList.DataSource = dt_InvoiceReturnDetail;
NumberingTableForDataGridView(dt_InvoiceReturnDetail);
this.dt_ProductList = dt_InvoiceReturnDetail;
FormatDataGridView();
calculateTotal(dt_InvoiceReturnDetail);
}
private void bindInvoiceReport()
{
DataTable dt_Invoice;
DataTable dt_InvoiceDetail;
DataTable dt_InvoiceReturnDetail;
DEInvoice invoice = new DEInvoice();
invoice.Invoice_Id = invoice_Id;
DEInvoiceDetail invoiceDetail = new DEInvoiceDetail();
invoiceDetail.Invoice_Id = invoice_Id;
DEInvoiceReturnDetail invoiceReturnDetail = new DEInvoiceReturnDetail();
invoiceReturnDetail.Invoice_Id = invoice_Id;
BLLInvoice obj_BLLInvoice = new BLLInvoice();
BLLInvoiceDetail obj_BLLInvoiceDetail = new BLLInvoiceDetail();
BLLInvoiceReturnDetail obj_BLLInvoiceReturnDetail = new BLLInvoiceReturnDetail();
dt_Invoice = obj_BLLInvoice.LoadInvoiceTableForAllDataByInvoice_Id(invoice);
dt_InvoiceDetail = obj_BLLInvoiceDetail.LoadInvoiceDetailTableForAllDataByInvoiceId(invoiceDetail);
dt_InvoiceReturnDetail = obj_BLLInvoiceReturnDetail.LoadInvoiceReturnDetailTableForAllDataByInvoiceId(invoiceReturnDetail);
NumberingTableForDataGridView(dt_InvoiceDetail);
NumberingTableForDataGridView(dt_InvoiceReturnDetail);
rptv_ConsignInvoiceReport.Clear();
rptv_ConsignInvoiceReport.Reset();
rptv_ConsignInvoiceReport.ProcessingMode = ProcessingMode.Local;
LocalReport localReport = rptv_ConsignInvoiceReport.LocalReport;
if (chb_SmallSize.Checked == false)
{
localReport.ReportEmbeddedResource = "StockAndSale.WinUI.Reports.Classes.Rpt_ConsignInvoiceReport.rdlc";
}
else
{
localReport.ReportEmbeddedResource = "StockAndSale.WinUI.Reports.Classes.Rpt_ConsignInvoiceA5Report.rdlc";
}
ReportDataSource ds_Invoice = new ReportDataSource();
ds_Invoice.Name = "DS_InvoiceReport_dt_Invoice";
ds_Invoice.Value = dt_Invoice;
ReportDataSource ds_InvoiceDetail = new ReportDataSource();
ds_InvoiceDetail.Name = "DS_InvoiceReport_dt_InvoiceDetail";
ds_InvoiceDetail.Value = dt_InvoiceDetail;
ReportDataSource ds_InvoiceReturnDetail = new ReportDataSource();
ds_InvoiceReturnDetail.Name = "DS_InvoiceReport_dt_InvoiceReturnDetail";
ds_InvoiceReturnDetail.Value = dt_InvoiceReturnDetail;
ReportParameter parTotal = new ReportParameter();
parTotal.Name = "parTotal";
ReportParameter parAdvance = new ReportParameter();
parAdvance.Name = "parAdvance";
ReportParameter parDiscount = new ReportParameter();
parDiscount.Name = "parDiscount";
ReportParameter parCommercialTax = new ReportParameter();
parCommercialTax.Name = "parCommercialTax";
ReportParameter parDeliveryDiscount = new ReportParameter();
parDeliveryDiscount.Name = "parDeliveryDiscount";
ReportParameter parReturnAmount = new ReportParameter();
parReturnAmount.Name = "parReturnAmount";
ReportParameter parDifferenceTotal = new ReportParameter();
parDifferenceTotal.Name = "parDifferenceTotal";
ReportParameter parGrandTotal = new ReportParameter();
parGrandTotal.Name = "parGrandTotal";
ReportParameter parInvoiceNo = new ReportParameter();
parInvoiceNo.Name = "parInvoiceNo";
ReportParameter parInvoiceDate = new ReportParameter();
parInvoiceDate.Name = "parInvoiceDate";
ReportParameter parCustomerName = new ReportParameter();
parCustomerName.Name = "parCustomerName";
ReportParameter parCustomerShop = new ReportParameter();
parCustomerShop.Name = "parCustomerShop";
ReportParameter parAddress = new ReportParameter();
parAddress.Name = "parAddress";
ReportParameter parPhone = new ReportParameter();
parPhone.Name = "parPhone";
ReportParameter parPONumber = new ReportParameter();
parPONumber.Name = "parPONumber";
ReportParameter parPromDesc = new ReportParameter();
parPromDesc.Name = "parPromDesc";
ReportParameter parIsPromotion = new ReportParameter();
parIsPromotion.Name = "parIsPromotion";
ReportParameter parPaymentStatus = new ReportParameter();
parPaymentStatus.Name = "parPaymentStatus";
ReportParameter parPaymentDate = new ReportParameter();
parPaymentDate.Name = "parPaymentDate";
ReportParameter parInvoiceDueDate = new ReportParameter();
parInvoiceDueDate.Name = "parInvoiceDueDate";
ReportParameter parShowReturn = new ReportParameter();
parShowReturn.Name = "parShowReturn";
ReportParameter parShow = new ReportParameter();
parShow.Name = "parShowLetterHead";
parShow.Values.Add(chb_ShowLetterHead.Checked.ToString());
ReportParameter parShowFooter = new ReportParameter();
parShowFooter.Name = "parShowLetterFooter";
parShowFooter.Values.Add(chb_ShowLetterFooter.Checked.ToString());
if (chb_Return.Checked == true)
{
parShowReturn.Values.Add("true");
}
else
{
parShowReturn.Values.Add("false");
}
Boolean bool_HasRow = obj_BLLInvoice.LoadInvoiceRow(invoice);
if (true)
{
if (invoice.isPaid == true)
{
parPaymentStatus.Values.Add("Paid on :");
parPaymentDate.Values.Add(invoice.PayDate.ToLongDateString());
}
else
{
parPaymentStatus.Values.Add("No Paid :");
parPaymentDate.Values.Add("");
}
parInvoiceDueDate.Values.Add(invoice.Invoice_Due_Date.ToString());
parTotal.Values.Add(invoice.Total.ToString());
parAdvance.Values.Add(invoice.Advance.ToString());
parDiscount.Values.Add(invoice.Discount.ToString());
parCommercialTax.Values.Add(invoice.CommercialTax.ToString());
parDeliveryDiscount.Values.Add(invoice.DeliveryDiscount.ToString());
parGrandTotal.Values.Add(invoice.GrandTotal.ToString());
parInvoiceNo.Values.Add(invoice.Invoice_No.ToString());
parInvoiceDate.Values.Add(invoice.Invoice_Date.ToString());
parCustomerName.Values.Add(invoice.Customer_Name);
parCustomerShop.Values.Add(invoice.Customer_Description);
parAddress.Values.Add(invoice.Address);
parPhone.Values.Add(invoice.Phone);
parPONumber.Values.Add(invoice.PONumber);
parPromDesc.Values.Add(invoice.PromDesc);
parIsPromotion.Values.Add(invoice.IsPromotion.ToString());
Decimal dec_RetTotal = 0;
Decimal dec_DiffTotal = 0;
for (int i = 0; i < dt_InvoiceReturnDetail.Rows.Count; i++)
{
dec_RetTotal += Convert.ToDecimal(dt_InvoiceReturnDetail.Rows[i]["RAmount"].ToString());
}
if (dec_RetTotal != 0)
{
dec_DiffTotal = invoice.GrandTotal - dec_RetTotal;
}
parReturnAmount.Values.Add(dec_RetTotal.ToString());
parDifferenceTotal.Values.Add(dec_DiffTotal.ToString());
}
rptv_ConsignInvoiceReport.LocalReport.SetParameters(new ReportParameter[] { parTotal, parAdvance, parDiscount, parGrandTotal, parInvoiceNo, parInvoiceDate, parCustomerName, parCustomerShop, parAddress, parPhone, parInvoiceDueDate, parPaymentStatus, parPaymentDate, parShowReturn, parShow, parShowFooter, parPONumber, parPromDesc, parIsPromotion, parDeliveryDiscount, parCommercialTax, parReturnAmount, parDifferenceTotal }); // parPaymentStatus, parPaymentDate, parInvoiceDueDate });
localReport.DataSources.Add(ds_InvoiceDetail);
localReport.DataSources.Add(ds_Invoice);
localReport.DataSources.Add(ds_InvoiceReturnDetail);
rptv_ConsignInvoiceReport.RefreshReport();
}
public int InsertData(DEInvoiceReturnDetail invRetDetail)
{
int int_Result = 0;
DALProductInStore obj_DALProductInStore = new DALProductInStore();
DALProduct obj_DALProduct = new DALProduct();
DEProductInStore productInStore = new DEProductInStore();
productInStore.Product_Id = invRetDetail.Product_Id;
DEProduct product = new DEProduct();
product.Product_Id = invRetDetail.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(invRetDetail, SqlCon, tn);
int int_NoOfUnitsSale = 0;
if (invRetDetail.isCarton == true)
{
int_NoOfUnitsSale = (invRetDetail.Qty * product.NoOfUnitsPerCarton);
}
else
{
int_NoOfUnitsSale = invRetDetail.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);
}
}
