public static List <InvoiceInfo> GetList_Bill(string InvoiceID, string BranchID, string CusID, string OrganizationCode, string InvoiceDateFrom, string InvoiceDateTo, string IsReturned, string IsCancelled)
{
using (DBHelper dbhlper = new DBHelper("[GetInvoiceListMasters]"))
{
DBHelper.AddPparameter("@InvoiceID", InvoiceID);
DBHelper.AddPparameter("@OrganizationCode", OrganizationCode);
DBHelper.AddPparameter("@BranchID", BranchID);
DBHelper.AddPparameter("@CusID", CusID);
DBHelper.AddPparameter("@InvoiceDateFrom", InvoiceDateFrom);
DBHelper.AddPparameter("@InvoiceDateTo", InvoiceDateTo);
using (DataSet ds = DBHelper.Execute_Query())
{
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <InvoiceInfo> list = new List <InvoiceInfo>();
InvoiceInfo obj = null;
foreach (DataRow dr in ds.Tables[0].Rows)
{
obj = new InvoiceInfo();
obj.InvoiceID = dr["InvoiceID"].ToString();
obj.InvoiceNumber = dr["InvoiceNumber"].ToString();
obj.AmountPayable = dr["AmountPayable"].ToString();
obj.InvoiceDate = dr["InvoiceDate"] != null?Convert.ToDateTime(dr["InvoiceDate"]).ToString("dd/MM/yyyy").Replace("-", "/") : "";
obj.InvoiceDueDate = dr["Duedate"] != null?Convert.ToDateTime(dr["Duedate"]).ToString("dd/MM/yyyy").Replace("-", "/") : "";
obj.BranchID = dr["BranchID"].ToString();
obj.CusID = dr["CusID"].ToString();
obj.CustomerName = dr["CustomerName"].ToString();
obj.OrganizationCode = dr["OrganizationCode"].ToString();
obj.OrganizationName = dr["OrganizationName"].ToString();
obj.BranchName = dr["BranchName"].ToString();
obj.LastModifiedBy = dr["LastModifiedBy"].ToString();
if (dr["LastModifiedOn"] != null && dr["LastModifiedOn"].ToString().Trim().Length > 0)
{
obj.LastModifiedOn = Convert.ToDateTime(dr["LastModifiedOn"]);
}
obj.ActivityType = dr["ActivityType"].ToString();
obj.IsCancelled = dr["IsCancelled"].ToString().Trim() == "Y";
obj.IsReturned = dr["IsReturned"].ToString().Trim() == "Y";
obj.AmountAfterDiscount = dr["AmountAfterDiscount"].ToString();
obj.SumAmount = dr["SumAmount"].ToString();
obj.AmountPayable = dr["AmountPayable"].ToString();
obj.TotalAmount = dr["TotalAmount"].ToString();
obj.AmountDue = dr["AmountDue"].ToString();
obj.AmountIncludeTax = dr["AmountIncludeTax"].ToString();
obj.AmountExcludeTax = dr["AmountExcludeTax"].ToString();
obj.TaxOnProduct = dr["TaxOnProduct"].ToString();
if (InvoiceID.Trim().ToString().Length > 0)
{
obj.ReturnedOn = dr["ReturnedOn"].ToString();
obj.BranchAddress = dr["BranchAddress"].ToString();
obj.BranchCountry = dr["BranchCountry"].ToString();
obj.BranchState = dr["BranchState"].ToString();
obj.ShipAddress = dr["ShipAddess"].ToString();
obj.ShipCity = dr["ShipCity"].ToString();
obj.ShipState = dr["ShipState"].ToString();
obj.ShipCountry = dr["ShipCountry"].ToString();
obj.CusEmail = dr["CusEmail"].ToString();
obj.CustomerName = dr["CustomerName"].ToString();
obj.ChangedCurrency = dr["ChangedCurrency"].ToString();
obj.ConversionRate = dr["ConversionRate"].ToString();
obj.PrevConversionRate = dr["PrevConversionRate"].ToString();
obj.DatauniqueID = dr["DataUniqueID"].ToString();
obj.IsActive = dr["IsActive"].ToString().Trim() == "Y";
/*Values from child table*/
obj.AmountIncludeTax = dr["AmountIncludeTax"].ToString();
obj.TaxOnProduct = dr["TaxOnProduct"].ToString();
obj.AmountExcludeTax = dr["AmountExcludeTax"].ToString();
/*Values from child table*/
if (ds.Tables.Count > 1 && ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
{
obj.InvoiceProductList = new List <InvoiceProductInfo>();
InvoiceProductInfo objInvoiceProductInfo = null;
foreach (DataRow cdr in ds.Tables[1].Rows)
{
objInvoiceProductInfo = new InvoiceProductInfo();
objInvoiceProductInfo.DataUniqueID = cdr["DataUniqueID"].ToString();
objInvoiceProductInfo.PricePerUnit = cdr["PricePerUnit"].ToString();
objInvoiceProductInfo.ProductId = cdr["ProductId"].ToString();
objInvoiceProductInfo.Quantity = cdr["Quantity"].ToString();
objInvoiceProductInfo.TaxOnProduct = cdr["TaxOnProduct"].ToString();
objInvoiceProductInfo.TotalAmount = cdr["TotalAmount"].ToString();
objInvoiceProductInfo.TotalAmountIncludeTax = cdr["AmountIncludeTax"].ToString();
obj.InvoiceProductList.Add(objInvoiceProductInfo);
}
}
}
list.Add(obj);
}
return(list);
}
}
}
return(null);
}
public static List <InvoiceInfo> GetList_Expense(string InvoiceID, string BranchID, string CusID, string OrganizationCode, string InvoiceDateFrom, string InvoiceDateTo, string IsReturned, string IsCancelled)
{
using (DBHelper dbhlper = new DBHelper("[GetExpenseListMasters]"))
{
DBHelper.AddPparameter("@InvoiceID", InvoiceID);
DBHelper.AddPparameter("@OrganizationCode", OrganizationCode);
DBHelper.AddPparameter("@BranchID", BranchID);
DBHelper.AddPparameter("@CusID", CusID);
DBHelper.AddPparameter("@InvoiceDateFrom", InvoiceDateFrom);
DBHelper.AddPparameter("@InvoiceDateTo", InvoiceDateTo);
using (DataSet ds = DBHelper.Execute_Query())
{
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <InvoiceInfo> list = new List <InvoiceInfo>();
InvoiceInfo obj = null;
foreach (DataRow dr in ds.Tables[0].Rows)
{
obj = new InvoiceInfo();
obj.InvoiceID = dr["InvoiceID"].ToString();
obj.InvoiceNumber = dr["InvoiceNumber"].ToString();
obj.AmountPayable = dr["AmountPayable"].ToString();
obj.InvoiceDate = dr["InvoiceDate"] != null?Convert.ToDateTime(dr["InvoiceDate"]).ToString("dd/MM/yyyy").Replace("-", "/") : "";
obj.InvoiceDueDate = dr["Duedate"] != null?Convert.ToDateTime(dr["Duedate"]).ToString("dd/MM/yyyy").Replace("-", "/") : "";
obj.BranchID = dr["BranchID"].ToString();
obj.CusID = dr["CusID"].ToString();
obj.CustomerName = dr["CustomerName"].ToString();
obj.OrganizationCode = dr["OrganizationCode"].ToString();
obj.OrganizationName = dr["OrganizationName"].ToString();
obj.BranchName = dr["BranchName"].ToString();
obj.LastModifiedBy = dr["LastModifiedBy"].ToString();
if (dr["LastModifiedOn"] != null && dr["LastModifiedOn"].ToString().Trim().Length > 0)
{
obj.LastModifiedOn = Convert.ToDateTime(dr["LastModifiedOn"]);
}
obj.ActivityType = dr["ActivityType"].ToString();
obj.IsCancelled = dr["IsCancelled"].ToString().Trim() == "Y";
obj.IsReturned = dr["IsReturned"].ToString().Trim() == "Y";
obj.AmountAfterDiscount = dr["AmountAfterDiscount"].ToString();
obj.SumAmount = dr["SumAmount"].ToString();
obj.AmountPayable = dr["AmountPayable"].ToString();
obj.TotalAmount = dr["TotalAmount"].ToString();
obj.AmountDue = dr["AmountDue"].ToString();
obj.ReturnedOn = dr["ReturnedOn"].ToString();
obj.BranchAddress = dr["BranchAddress"].ToString();
obj.BranchCountry = dr["BranchCountry"].ToString();
obj.BranchState = dr["BranchState"].ToString();
obj.ShipAddress = dr["ShipAddess"].ToString();
obj.ShipCity = dr["ShipCity"].ToString();
obj.ShipState = dr["ShipState"].ToString();
obj.ShipCountry = dr["ShipCountry"].ToString();
obj.CusEmail = dr["CusEmail"].ToString();
obj.CustomerName = dr["CustomerName"].ToString();
obj.ChangedCurrency = dr["ChangedCurrency"].ToString();
obj.ConversionRate = dr["ConversionRate"].ToString();
obj.PrevConversionRate = dr["PrevConversionRate"].ToString();
obj.DatauniqueID = dr["DataUniqueID"].ToString();
obj.IsActive = dr["IsActive"].ToString().Trim() == "Y";
obj.AmountIncludeTax = dr["AmountIncludeTax"].ToString();
/*obj.AmountExcludeTax = dr["AmountExcludeTax"].ToString();*/
obj.TaxOnProduct = dr["TaxOnProduct"].ToString();
if (InvoiceID.Trim().ToString().Length > 0 &&
(ds.Tables.Count > 1 && ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0))
{
obj.InvoiceProductList = new List <InvoiceProductInfo>();
InvoiceProductInfo objInvoiceProductInfo = null;
foreach (DataRow cdr in ds.Tables[1].Rows)
{
objInvoiceProductInfo = new InvoiceProductInfo();
objInvoiceProductInfo.DataUniqueID = cdr["DataUniqueID"].ToString();
objInvoiceProductInfo.PricePerUnit = cdr["PricePerUnit"].ToString();
objInvoiceProductInfo.ProductId = cdr["ProductId"].ToString();
objInvoiceProductInfo.Quantity = cdr["Quantity"].ToString();
objInvoiceProductInfo.TaxOnProduct = cdr["TaxOnProduct"].ToString();
objInvoiceProductInfo.TotalAmount = cdr["TotalAmount"].ToString();
objInvoiceProductInfo.TotalAmountIncludeTax = cdr["AmountIncludeTax"].ToString();
objInvoiceProductInfo.isBreakupNeed = cdr["isBreakupNeed"].ToString() == "Y";
obj.InvoiceProductList.Add(objInvoiceProductInfo);
}
}
if (ds.Tables.Count > 2 && ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
{
obj.ExpenseBreakupList = new List <ExpenseBreakupInfo>();
ExpenseBreakupInfo objExpenseBreakupInfo = null;
foreach (DataRow edr in ds.Tables[2].Rows)
{
objExpenseBreakupInfo = new ExpenseBreakupInfo();
objExpenseBreakupInfo.InvoiceProductID = edr["InvoiceProductID"].ToString();
objExpenseBreakupInfo.BreakupId = edr["BreakupId"].ToString();
objExpenseBreakupInfo.FromDate = edr["FromDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(edr["FromDate"]);
objExpenseBreakupInfo.Todate = edr["ToDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(edr["ToDate"]);
objExpenseBreakupInfo.Description = edr["Description"].ToString();
objExpenseBreakupInfo.Price = edr["Price"].ToString();
objExpenseBreakupInfo.Remarks = edr["Remarks"].ToString();
obj.ExpenseBreakupList.Add(objExpenseBreakupInfo);
}
}
// Modified on [18 Oct 2019] to add expense fieled - Start
/*if (InvoiceID.Trim().ToString().Length > 0 &&
* (ds.Tables.Count > 1 && ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0))
* {
* obj.ExpenseBreakupList = new List<ExpenseBreakupInfo>();
* ExpenseBreakupInfo objExpenseBreakupInfo = null;
*
* foreach (DataRow edr in ds.Tables[2].Rows)
* {
* objExpenseBreakupInfo = new ExpenseBreakupInfo();
*
* objExpenseBreakupInfo.ExpenseId = edr["ExpenseId"].ToString();
* objExpenseBreakupInfo.FromDate = edr["PricePerUnit"].ToString();
* objExpenseBreakupInfo.Todate = edr["ProductId"].ToString();
* objExpenseBreakupInfo.Quantity = edr["Quantity"].ToString();
* objExpenseBreakupInfo.TaxOnProduct = edr["TaxOnProduct"].ToString();
* objExpenseBreakupInfo.TotalAmount = edr["TotalAmount"].ToString();
*
* obj.InvoiceProductList.Add(objInvoiceProductInfo);
* }
* }
* // Modified on [02 Oct 2019] to add expense fieled - End
* }
*/
list.Add(obj);
}
return(list);
}
}
return(null);
}
}