public static DebitNote GetDetails(int Id, int LocationId)
{
DBManager db = new DBManager();
try
{
#region Query
string query = @"select pr.Pr_Id,isnull(pr.Return_Date,0) Return_Date,pr.Location_Id,pr.Supplier_Id,pr.Bill_No,pr.Return_Type,
pr.Tax_Amount,it.Name[Item],sup.Name[Supplier],pr.Gross_Amount,pr.Round_Off,pr.Net_Amount,pr.Other_Charges,pr.Narration,
pr.[Status],prd.pr_Id,prd.item_id,prd.Quantity,prd.MRP,prd.Rate[Selling_Price],pr.Tax_Amount[P_Tax_Amount],prd.Instance_Id,
prd.Gross_Amount[P_Gross_Amount],prd.Net_Amount[P_Net_Amount],l.Name[Location],cm.Name[Company],tx.Percentage[Tax_Percentage],
it.Name[Item],it.Item_Code,l.Address1[Loc_Address1],l.Address2[Loc_Address2],l.Contact[Loc_Phone],l.Reg_Id1[Loc_RegId],
cm.Address1[Company_Address1],cm.Address2[Company_Address2],cm.Mobile_No1[Company_Phone],cm.Reg_Id1[Company_RegId],
sup.Taxno1[Cus_taxNo],cm.Logo[Company_Logo],cm.Email[Company_Email],isnull(pr.Cost_Center_Id,0)[Cost_Center_Id],
isnull(pr.Job_Id,0)[Job_Id],cost.fcc_Name[Cost_Center],j.job_name[Job],pr.TandC,pr.Payment_Terms,pr.Salutation,
pr.Contact_Name,pr.Contact_Address1,pr.Contact_Address2,pr.Contact_City,pr.Contact_Email,pr.Contact_Zipcode,
pr.Contact_Phone1,pr.Contact_Phone2,pr.Country_Id,pr.State_Id,coun.Name[Sup_Country],st.Name[Sup_State]
from TBL_PURCHASE_RETURN_REGISTER pr with(nolock)
left join TBL_PURCHASE_RETURN_DETAILS prd with(nolock) on prd.pr_Id=pr.pr_Id
left join TBL_LOCATION_MST l with(nolock) on l.Location_Id=pr.Location_Id
left join TBL_SUPPLIER_MST sup on sup.Supplier_Id=pr.Supplier_Id
left join TBL_COMPANY_MST cm with(nolock) on cm.Company_Id=l.Company_Id
left join TBL_TAX_MST tx with(nolock) on tx.Tax_Id=prd.Tax_Id
left join TBL_ITEM_MST it with(nolock) on it.Item_Id=prd.Item_Id
left join TBL_COUNTRY_MST coun on coun.Country_Id=pr.Country_ID
left join TBL_STATE_MST st on st.State_Id=pr.State_ID
left join tbl_fin_CostCenter cost on cost.Fcc_ID=pr.Cost_Center_Id
left join TBL_JOB_MST j on j.job_id=pr.job_id
where pr.Location_Id=@Location_Id and pr.Pr_Id=@Pe_Id order by pr.Created_Date desc";
#endregion Query
db.CreateParameters(2);
db.AddParameters(0, "@Location_Id", LocationId);
db.AddParameters(1, "@Pe_Id", Id);
db.Open();
DataTable dt = db.ExecuteQuery(CommandType.Text, query);
if (dt != null)
{
DataRow row = dt.Rows[0];
DebitNote register = new DebitNote();
register.ID = row["Pr_Id"] != DBNull.Value ? Convert.ToInt32(row["Pr_Id"]) : 0;
register.CostCenterId = row["Cost_Center_Id"] != DBNull.Value ? Convert.ToInt32(row["Cost_Center_Id"]) : 0;
register.JobId = row["Job_Id"] != DBNull.Value ? Convert.ToInt32(row["Job_Id"]) : 0;
register.LocationId = row["Location_Id"] != DBNull.Value ? Convert.ToInt32(row["Location_Id"]) : 0;
register.SupplierId = row["supplier_id"] != DBNull.Value ? Convert.ToInt32(row["supplier_id"]) : 0;
register.BillNo = Convert.ToString(row["Bill_No"]);
register.CostCenter = Convert.ToString(row["Cost_Center"]);
register.JobName = Convert.ToString(row["Job"]);
register.ReturnDateString = row["Return_Date"] != DBNull.Value ? Convert.ToDateTime(row["Return_Date"]).ToString("dd/MMM/yyyy") : string.Empty;
register.TaxAmount = row["Tax_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Tax_Amount"]) : 0;
register.Gross = row["Gross_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Gross_Amount"]) : 0;
register.Supplier = Convert.ToString(row["Supplier"]);
register.OtherCharges = row["Other_Charges"] != DBNull.Value ? Convert.ToDecimal(row["Other_Charges"]) : 0;
register.RoundOff = row["Round_Off"] != DBNull.Value ? Convert.ToDecimal(row["Round_Off"]) : 0;
register.NetAmount = row["Net_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Net_Amount"]) : 0;
register.Narration = Convert.ToString(row["Narration"]);
register.TermsandConditon = Convert.ToString(row["TandC"]);
register.Status = row["Status"] != DBNull.Value ? Convert.ToInt32(row["Status"]) : 0;
List <Entities.Master.Address> addresslist = new List <Master.Address>();
Entities.Master.Address address = new Master.Address();
address.ContactName = Convert.ToString(row["Contact_Name"]);
address.Address1 = Convert.ToString(row["Contact_Address1"]);
address.Address2 = Convert.ToString(row["Contact_Address2"]);
address.City = Convert.ToString(row["Contact_City"]);
address.Email = Convert.ToString(row["Contact_Email"]);
address.Phone1 = Convert.ToString(row["Contact_Phone1"]);
address.Phone2 = Convert.ToString(row["Contact_Phone2"]);
address.Zipcode = Convert.ToString(row["Contact_Zipcode"]);
address.Salutation = Convert.ToString(row["Salutation"]);
address.State = Convert.ToString(row["Sup_State"]);
address.Country = Convert.ToString(row["Sup_Country"]);
address.CountryID = row["Country_ID"] != DBNull.Value ? Convert.ToInt32(row["Country_ID"]) : 0;
address.StateID = row["State_ID"] != DBNull.Value ? Convert.ToInt32(row["State_ID"]) : 0;
addresslist.Add(address);
register.BillingAddress = addresslist;
DataTable inProducts = dt.AsEnumerable().Where(x => x.Field <int>("Pr_Id") == register.ID).CopyToDataTable();
List <Item> products = new List <Item>();
for (int j = 0; j < inProducts.Rows.Count; j++)
{
DataRow rowItem = inProducts.Rows[j];
Item item = new Item();
item.InstanceId = rowItem["instance_Id"] != DBNull.Value ? Convert.ToInt32(rowItem["instance_Id"]) : 0;
item.DetailsID = rowItem["Pr_Id"] != DBNull.Value ? Convert.ToInt32(rowItem["Pr_Id"]) : 0;
item.ItemID = rowItem["Item_Id"] != DBNull.Value ? Convert.ToInt32(rowItem["Item_Id"]) : 0;
item.Name = Convert.ToString(rowItem["Item"]);
item.MRP = rowItem["Mrp"] != DBNull.Value ? Convert.ToDecimal(rowItem["Mrp"]) : 0;
item.CostPrice = Convert.ToDecimal(rowItem["Selling_Price"]);
item.TaxPercentage = rowItem["Tax_Percentage"] != DBNull.Value ? Convert.ToDecimal(rowItem["Tax_Percentage"]) : 0;
item.Gross = rowItem["Gross_Amount"] != DBNull.Value ? Convert.ToDecimal(rowItem["Gross_Amount"]) : 0;
item.NetAmount = rowItem["Net_Amount"] != DBNull.Value ? Convert.ToDecimal(rowItem["Net_Amount"]) : 0;
item.TaxAmount = rowItem["Tax_Amount"] != DBNull.Value ? Convert.ToDecimal(rowItem["Tax_Amount"]) : 0;
item.Quantity = rowItem["Quantity"] != DBNull.Value ? Convert.ToDecimal(rowItem["Quantity"]) : 0;
item.ItemCode = Convert.ToString(rowItem["Item_Code"]);
products.Add(item);
dt.Rows.RemoveAt(0);
}
register.Products = products;
return(register);
}
return(null);
}
catch (Exception ex)
{
Application.Helper.LogException(ex, "DebitNote | GetDetails(int Id,int LocationId)");
return(null);
}
}
public static List <DebitNote> GetDetails(int LocationID, int?SupplierId, DateTime?from, DateTime?to)
{
DBManager db = new DBManager();
try
{
#region Query
string query = @"select pr.Pr_Id,isnull(pr.Return_Date,0) Return_Date,pr.Location_Id,pr.Supplier_Id,pr.Bill_No,pr.Return_Type,pr.Tax_Amount,it.Name[Item],sup.Name[Supplier]
,pr.Gross_Amount,pr.Round_Off,pr.Net_Amount,pr.Other_Charges,pr.Narration,pr.[Status],prd.pr_Id,prd.item_id,prd.Quantity
,prd.MRP,prd.Rate[Selling_Price],pr.Tax_Amount[P_Tax_Amount],prd.Instance_Id,prd.Gross_Amount[P_Gross_Amount],prd.Net_Amount[P_Net_Amount],l.Name[Location],
cm.Name[Company],tx.Percentage[Tax_Percentage],
it.Name[Item],it.Item_Code,
l.Address1[Loc_Address1],l.Address2[Loc_Address2],l.Contact[Loc_Phone],l.Reg_Id1[Loc_RegId],
cm.Address1[Company_Address1],cm.Address2[Company_Address2],cm.Mobile_No1[Company_Phone],cm.Reg_Id1[Company_RegId],
sup.Address1[Cus_Address1],sup.Address2[Cus_Address2],sup.Phone1[Cus_Phone],sup.Taxno1[Cus_taxNo],sup.Name[Customer]
,cm.Logo[Company_Logo],cm.Email[Company_Email],isnull(pr.Cost_Center_Id,0)[Cost_Center_Id],isnull(pr.Job_Id,0)[Job_Id],cost.fcc_Name[Cost_Center],j.job_name[Job]
,sup.Email[Cust_Email],pr.TandC,pr.Payment_Terms from TBL_PURCHASE_RETURN_REGISTER pr with(nolock)
left join TBL_PURCHASE_RETURN_DETAILS prd with(nolock) on prd.pr_Id=pr.pr_Id
left join TBL_LOCATION_MST l with(nolock) on l.Location_Id=pr.Location_Id
left join TBL_SUPPLIER_MST sup on sup.Supplier_Id=pr.Supplier_Id
left join TBL_COMPANY_MST cm with(nolock) on cm.Company_Id=l.Company_Id
left join TBL_TAX_MST tx with(nolock) on tx.Tax_Id=prd.Tax_Id
left join TBL_ITEM_MST it with(nolock) on it.Item_Id=prd.Item_Id
left join tbl_fin_CostCenter cost on cost.Fcc_ID=pr.Cost_Center_Id
left join TBL_JOB_MST j on j.job_id=pr.job_id
where pr.Location_Id=@Location_Id {#supplierfilter#} {#daterangefilter#} order by pr.Return_Date desc";
#endregion Query
if (from != null && to != null)
{
query = query.Replace("{#daterangefilter#}", " and pr.Return_Date>=@fromdate and pr.Return_Date<=@todate ");
}
else
{
to = DateTime.UtcNow;
from = new DateTime(to.Value.Year, to.Value.Month, 01);
query = query.Replace("{#daterangefilter#}", " and pr.Return_Date>=@fromdate and pr.Return_Date<=@todate ");
}
if (SupplierId != null && SupplierId > 0)
{
query = query.Replace("{#supplierfilter#}", " and pr.Supplier_Id=@Supplier_Id ");
}
else
{
query = query.Replace("{#supplierfilter#}", string.Empty);
}
db.CreateParameters(4);
db.AddParameters(0, "@Location_Id", LocationID);
db.AddParameters(1, "@fromdate", from);
db.AddParameters(2, "@todate", to);
db.AddParameters(3, "@Supplier_Id", SupplierId);
db.Open();
DataTable dt = db.ExecuteQuery(CommandType.Text, query);
if (dt != null)
{
List <DebitNote> result = new List <DebitNote>();
for (int i = 0; i < dt.Rows.Count;)
{
DataRow row = dt.Rows[i];
DebitNote register = new DebitNote();
register.ID = row["Pr_Id"] != DBNull.Value ? Convert.ToInt32(row["Pr_Id"]) : 0;
register.CostCenterId = row["Cost_Center_Id"] != DBNull.Value ? Convert.ToInt32(row["Cost_Center_Id"]) : 0;
register.JobId = row["Job_Id"] != DBNull.Value ? Convert.ToInt32(row["Job_Id"]) : 0;
register.LocationId = row["Location_Id"] != DBNull.Value ? Convert.ToInt32(row["Location_Id"]) : 0;
register.SupplierId = row["supplier_id"] != DBNull.Value ? Convert.ToInt32(row["supplier_id"]) : 0;
register.BillNo = Convert.ToString(row["Bill_No"]);
register.CostCenter = Convert.ToString(row["Cost_Center"]);
register.JobName = Convert.ToString(row["Job"]);
register.ReturnDateString = row["Return_Date"] != DBNull.Value ? Convert.ToDateTime(row["Return_Date"]).ToString("dd/MMM/yyyy") : string.Empty;
register.TaxAmount = row["Tax_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Tax_Amount"]) : 0;
register.Gross = row["Gross_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Gross_Amount"]) : 0;
register.Supplier = Convert.ToString(row["Supplier"]);
register.OtherCharges = row["Other_Charges"] != DBNull.Value ? Convert.ToDecimal(row["Other_Charges"]) : 0;
register.RoundOff = row["Round_Off"] != DBNull.Value ? Convert.ToDecimal(row["Round_Off"]) : 0;
register.NetAmount = row["Net_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Net_Amount"]) : 0;
register.Narration = Convert.ToString(row["Narration"]);
register.TermsandConditon = Convert.ToString(row["TandC"]);
register.Status = row["Status"] != DBNull.Value ? Convert.ToInt32(row["Status"]) : 0;
DataTable inProducts = dt.AsEnumerable().Where(x => x.Field <int>("Pr_Id") == register.ID).CopyToDataTable();
List <Item> products = new List <Item>();
for (int j = 0; j < inProducts.Rows.Count; j++)
{
DataRow rowItem = inProducts.Rows[j];
Item item = new Item();
item.InstanceId = rowItem["instance_Id"] != DBNull.Value ? Convert.ToInt32(rowItem["instance_Id"]) : 0;
item.DetailsID = rowItem["Pr_Id"] != DBNull.Value ? Convert.ToInt32(rowItem["Pr_Id"]) : 0;
item.ItemID = rowItem["Item_Id"] != DBNull.Value ? Convert.ToInt32(rowItem["Item_Id"]) : 0;
item.Name = Convert.ToString(rowItem["Item"]);
item.MRP = rowItem["Mrp"] != DBNull.Value ? Convert.ToDecimal(rowItem["Mrp"]) : 0;
item.CostPrice = Convert.ToDecimal(rowItem["Selling_Price"]);
item.TaxPercentage = rowItem["Tax_Percentage"] != DBNull.Value ? Convert.ToDecimal(rowItem["Tax_Percentage"]) : 0;
item.Gross = rowItem["Gross_Amount"] != DBNull.Value ? Convert.ToDecimal(rowItem["Gross_Amount"]) : 0;
item.NetAmount = rowItem["Net_Amount"] != DBNull.Value ? Convert.ToDecimal(rowItem["Net_Amount"]) : 0;
item.TaxAmount = rowItem["Tax_Amount"] != DBNull.Value ? Convert.ToDecimal(rowItem["Tax_Amount"]) : 0;
item.Quantity = rowItem["Quantity"] != DBNull.Value ? Convert.ToDecimal(rowItem["Quantity"]) : 0;
item.ItemCode = Convert.ToString(rowItem["Item_Code"]);
products.Add(item);
dt.Rows.RemoveAt(0);
}
register.Products = products;
result.Add(register);
}
return(result);
}
return(null);
}
catch (Exception ex)
{
Application.Helper.LogException(ex, "DebitNote | GetDetails(int LocationID, int? SupplierId, DateTime? from, DateTime? to)");
return(null);
}
}