public static PurchaseIndentRegister GetDetails(int Id)
{
DBManager db = new DBManager();
try
{
#region query
db.Open();
string query = @"select pir.Pi_Id,pir.Indent_No,pir.Request_Date,pir.Tax_amount,pir.Gross_Amount,
pir.Net_Amount,pir.Narration,pir.Round_Off
,pir.Due_Date,pir.Priorities,pir.Supplier_Mail,pid.Instance_Id,pid.Item_Id,pid.Gross_Amount [P_GrossAmount],pid.Net_Amount [P_NetAmount],
pid.Tax_Amount [P_TaxAmount],pid.Rate [CostPrice],pid.Qty,tax.Percentage [taxPercentage],pid.Mrp,itm.Name [itemName],itm.Item_Code
,isnull(pir.Cost_Center_Id,0)[Cost_Center_Id],isnull(pir.Job_Id,0)[Job_Id],cost.Fcc_Name[Cost_Center],j.Job_Name[Job]
,cm.name[Company],cm.Country_Id,cm.State_Id,cm.Address1[Comp_Address1],cm.Address2[Comp_Address2],cm.Reg_Id1[Comp_RegId],cm.Mobile_No1[Comp_Phone]
,coun.Name[Country],st.Name[State],cm.Logo,cm.Email,pid.Description,pir.TandC,pir.Payment_Terms
from TBL_PURCHASE_INDENT_REGISTER pir with(nolock)
left join TBL_PURCHASE_INDENT_DETAILS pid on pid.pi_id=pir.pi_id
left join TBL_TAX_MST tax on tax.Tax_Id=pid.Tax_Id
left join TBL_ITEM_MST itm on itm.Item_Id=pid.Item_Id
left join tbl_Fin_CostCenter cost on cost.Fcc_ID=pir.Cost_Center_Id
left join TBL_JOB_MST j on j.Job_Id=pir.Job_Id
inner join tbl_company_mst cm on cm.Company_Id=pir.Company_Id
left join TBL_COUNTRY_MST coun on coun.Country_Id=cm.Country_Id
left join TBL_STATE_MST st on st.state_Id=cm.State_Id
where pir.Pi_Id=@Pi_Id";
#endregion query
db.CreateParameters(1);
db.AddParameters(0, "@Pi_Id", Id);
DataTable dt = db.ExecuteQuery(CommandType.Text, query);
if (dt != null)
{
DataRow row = dt.Rows[0];
PurchaseIndentRegister register = new PurchaseIndentRegister();
register.ID = row["Pi_Id"] != DBNull.Value ? Convert.ToInt32(row["Pi_Id"]) : 0;
register.IndentNo = Convert.ToString(row["Indent_No"]);
register.CostCenter = Convert.ToString(row["Cost_Center"]);
register.JobName = Convert.ToString(row["Job"]);
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.EntryDate = row["Request_Date"] != DBNull.Value ? Convert.ToDateTime(row["Request_Date"]) : DateTime.MinValue;
register.EntryDateString = row["Request_Date"] != DBNull.Value ? Convert.ToDateTime(row["Request_Date"]).ToString("dd/MMM/yyyy") : string.Empty;
register.Priority = row["Priorities"] != DBNull.Value ? Convert.ToBoolean(row["Priorities"]) : false;
register.DueDateString = row["Due_Date"] != DBNull.Value ? Convert.ToDateTime(row["Due_Date"]).ToString("dd/MMM/yyyy") : string.Empty;
register.Gross = row["Gross_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Gross_Amount"]) : 0;
register.TaxAmount = row["Tax_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Tax_Amount"]) : 0;
register.NetAmount = row["Net_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Net_Amount"]) : 0;
register.Narration = Convert.ToString(row["Narration"]);
register.Company = Convert.ToString(row["Company"]);
register.CompanyAddress1 = Convert.ToString(row["Comp_Address1"]);
register.CompanyAddress2 = Convert.ToString(row["Comp_Address2"]);
register.CompanyPhone = Convert.ToString(row["Comp_Phone"]);
register.CompanyRegId = Convert.ToString(row["Comp_RegId"]);
register.CompanyCountry = Convert.ToString(row["Country"]);
register.CompanyState = Convert.ToString(row["State"]);
register.CompanyEmail = Convert.ToString(row["Email"]);
register.CompanyLogo = Convert.ToBase64String(row["logo"] as byte[]);
register.RoundOff = row["Round_Off"] != DBNull.Value ? Convert.ToDecimal(row["Round_Off"]) : 0;
register.TermsandConditon = Convert.ToString(row["TandC"]);
register.Payment_Terms = Convert.ToString(row["Payment_Terms"]);
DataTable inProducts = dt.AsEnumerable().Where(x => x.Field <int>("Pi_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.ItemID = rowItem["Item_Id"] != DBNull.Value ? Convert.ToInt32(rowItem["Item_Id"]) : 0;
item.Name = rowItem["ItemName"].ToString();
item.Description = Convert.ToString(rowItem["Description"]);
item.MRP = rowItem["Mrp"] != DBNull.Value ? Convert.ToDecimal(rowItem["Mrp"]) : 0;
item.CostPrice = rowItem["CostPrice"] != DBNull.Value ? Convert.ToDecimal(rowItem["CostPrice"]) : 0;
item.TaxPercentage = rowItem["TaxPercentage"] != DBNull.Value ? Convert.ToDecimal(rowItem["TaxPercentage"]) : 0;
item.Gross = rowItem["P_GrossAmount"] != DBNull.Value ? Convert.ToDecimal(rowItem["P_GrossAmount"]) : 0;
item.NetAmount = rowItem["P_NetAmount"] != DBNull.Value ? Convert.ToDecimal(rowItem["P_NetAmount"]) : 0;
item.TaxAmount = rowItem["P_TaxAmount"] != DBNull.Value ? Convert.ToDecimal(rowItem["P_TaxAmount"]) : 0;
item.Quantity = rowItem["Qty"] != DBNull.Value ? Convert.ToDecimal(rowItem["Qty"]) : 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, "PurchaseIdent | GetDetails(int Id)");
return(null);
}
finally
{
db.Close();
}
}
public static List <PurchaseIndentRegister> GetDetailsIndent(int LocationID)
{
DBManager db = new DBManager();
try
{
#region query
db.Open();
string query = @"select m.Pi_Id [PurchaseRequestId],m.Due_Date,m.Request_Status,m.Round_Off,isnull(m.Indent_No,0)[Request_No],t.Percentage[TaxPercentage],
m.Priorities,isnull(l.Name,0)[location],L.Company_Id,isnull(c.Name,0)[company],it.Item_Code,m.Gross_Amount,m.Tax_amount,
isnull(m.Narration,0)[Narration],m.Net_Amount,us.Location_Id,m.Request_Date,d.Pid_Id [DetailsId],it.Item_Id,
it.Name[ItemName],d.Mrp,d.Rate[CostPrice],d.Qty,d.Tax_Amount[P_TaxAmount],d.Gross_Amount[P_GrossAmount],
d.Net_Amount[P_NetAmount],d.Request_Status[p_RequestStatus],d.instance_id,m.Created_Date,isnull(l.Address1,0)[Loc_Address1],
isnull(l.Address2,0)[Loc_Address2],isnull(l.Contact,0)[Loc_Phone],m.Round_Off,isnull(c.Address1,0)[Comp_Address1],isnull(c.Address2,0)[Comp_Address2],isnull(c.Mobile_No1,0)[Comp_Phone]
,l.Reg_Id1[Location_RegistrationId],c.Reg_Id1[Company_registrationId],c.Email[Company_Email],c.Logo[Comp_Logo]
from TBL_PURCHASE_INDENT_REGISTER m with(nolock)
join TBL_PURCHASE_INDENT_DETAILS d with(nolock) on m.Pi_Id = d.Pi_Id
left outer join TBL_ITEM_MST it with(nolock) on it.Item_Id = d.Item_Id
inner join TBL_USER_MST us on us.User_Id=m.Created_By
inner join TBL_LOCATION_MST L with(nolock) ON L.Location_Id=us.Location_Id
inner join TBL_COMPANY_MST C with(nolock) on C.Company_Id=L.Company_Id
inner join TBL_TAX_MST t with(nolock) on t.Tax_Id=d.Tax_Id
where us.Location_Id=@Location_Id order by m.Created_Date desc";
#endregion query
db.CreateParameters(1);
db.AddParameters(0, "@Location_Id", LocationID);
DataTable dt = db.ExecuteQuery(CommandType.Text, query);
if (dt != null)
{
List <PurchaseIndentRegister> result = new List <PurchaseIndentRegister>();
for (int i = 0; i < dt.Rows.Count;)
{
DataRow row = dt.Rows[i];
PurchaseIndentRegister register = new PurchaseIndentRegister();
register.ID = row["PurchaseRequestId"] != DBNull.Value ? Convert.ToInt32(row["PurchaseRequestId"]) : 0;
register.LocationId = row["Location_Id"] != DBNull.Value ? Convert.ToInt32(row["Location_Id"]) : 0;
register.IndentNo = Convert.ToString(row["Request_No"]);
register.EntryDate = row["Request_Date"] != DBNull.Value ? Convert.ToDateTime(row["Request_Date"]) : DateTime.MinValue;
register.EntryDateString = row["Request_Date"] != DBNull.Value ? Convert.ToDateTime(row["Request_Date"]).ToString("dd/MMM/yyyy") : string.Empty;
register.Priority = row["Priorities"] != DBNull.Value ? Convert.ToBoolean(row["Priorities"]) : false;
register.DueDateString = row["Due_Date"] != DBNull.Value ? Convert.ToDateTime(row["Due_Date"]).ToString("dd/MMM/yyyy") : string.Empty;
register.Gross = row["Gross_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Gross_Amount"]) : 0;
register.TaxAmount = row["Tax_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Tax_Amount"]) : 0;
register.NetAmount = row["Net_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Net_Amount"]) : 0;
register.Narration = Convert.ToString(row["Narration"]);
register.CompanyId = row["Company_Id"] != DBNull.Value ? Convert.ToInt32(row["Company_Id"]) : 0;
register.Location = Convert.ToString(row["Location"]);
register.LocationRegId = Convert.ToString(row["Location_RegistrationId"]);
register.CompanyRegId = Convert.ToString(row["Company_registrationId"]);
register.LocationAddress1 = Convert.ToString(row["Loc_Address1"]);
register.LocationAddress2 = Convert.ToString(row["Loc_Address2"]);
register.LocationPhone = Convert.ToString(row["Loc_Phone"]);
register.Company = Convert.ToString(row["Company"]);
register.CompanyEmail = Convert.ToString(row["Company_Email"]);
register.CompanyAddress1 = Convert.ToString(row["Comp_Address1"]);
register.CompanyAddress2 = Convert.ToString(row["Comp_Address2"]);
register.CompanyPhone = Convert.ToString(row["Comp_Phone"]);
register.RequestStatus = row["Request_Status"] != DBNull.Value ? Convert.ToInt32(row["Request_Status"]) : 0;
register.RoundOff = row["Round_Off"] != DBNull.Value ? Convert.ToDecimal(row["Round_Off"]) : 0;
DataTable inProducts = dt.AsEnumerable().Where(x => x.Field <int>("PurchaseRequestId") == 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["DetailsId"] != DBNull.Value ? Convert.ToInt32(rowItem["DetailsId"]) : 0;
item.ItemID = rowItem["Item_Id"] != DBNull.Value ? Convert.ToInt32(rowItem["Item_Id"]) : 0;
item.Name = rowItem["ItemName"].ToString();
item.MRP = rowItem["Mrp"] != DBNull.Value ? Convert.ToDecimal(rowItem["Mrp"]) : 0;
item.CostPrice = rowItem["CostPrice"] != DBNull.Value ? Convert.ToDecimal(rowItem["CostPrice"]) : 0;
item.TaxPercentage = rowItem["TaxPercentage"] != DBNull.Value ? Convert.ToDecimal(rowItem["TaxPercentage"]) : 0;
item.Gross = rowItem["P_GrossAmount"] != DBNull.Value ? Convert.ToDecimal(rowItem["P_GrossAmount"]) : 0;
item.NetAmount = rowItem["P_NetAmount"] != DBNull.Value ? Convert.ToDecimal(rowItem["P_NetAmount"]) : 0;
item.TaxAmount = rowItem["P_TaxAmount"] != DBNull.Value ? Convert.ToDecimal(rowItem["P_TaxAmount"]) : 0;
item.Quantity = rowItem["Qty"] != DBNull.Value ? Convert.ToDecimal(rowItem["Qty"]) : 0;
item.ItemCode = Convert.ToString(rowItem["Item_Code"]);
item.Status = rowItem["p_RequestStatus"] != DBNull.Value ? Convert.ToInt32(rowItem["p_RequestStatus"]) : 0;
products.Add(item);
dt.Rows.RemoveAt(0);
}
register.Products = products;
result.Add(register);
}
return(result);
}
return(null);
}
catch (Exception ex)
{
Application.Helper.LogException(ex, "PurchaseIndent | GetDetails(int LocationID)");
return(null);
}
finally
{
db.Close();
}
}
public static List <PurchaseIndentRegister> GetDetails(DateTime?from, DateTime?to)
{
DBManager db = new DBManager();
try
{
#region query
db.Open();
string query = @"select pir.Pi_Id,pir.Indent_No,pir.Request_Date,pir.Tax_amount,pir.Gross_Amount,
pir.Net_Amount,pir.Narration,pir.Round_Off
,pir.Due_Date,pir.Priorities,pir.Supplier_Mail,pir.SupplierBCC_Mail,pir.SupplierCC_Mail,isnull(pir.Cost_Center_Id,0)[Cost_Center_Id],isnull(pir.Job_Id,0)[Job_Id],
cost.Fcc_Name[Cost_Center],j.Job_Name[Job],pir.TandC,pir.Payment_Terms from TBL_PURCHASE_INDENT_REGISTER pir with(nolock)
left join tbl_Fin_CostCenter cost on cost.Fcc_ID=pir.Cost_Center_Id
left join TBL_JOB_MST j on j.Job_Id=pir.Job_Id
where {#daterangefilter#} order by pir.Request_Date desc";
#endregion query
if (from != null && to != null)
{
query = query.Replace("{#daterangefilter#}", " pir.Request_Date>=@fromdate and pir.Request_Date<=@todate ");
}
else
{
to = DateTime.UtcNow;
from = new DateTime(to.Value.Year, to.Value.Month, 01);
query = query.Replace("{#daterangefilter#}", " pir.Request_Date>=@fromdate and pir.Request_Date<=@todate ");
}
db.CreateParameters(2);
db.AddParameters(0, "@fromdate", from);
db.AddParameters(1, "@todate", to);
DataTable dt = db.ExecuteQuery(CommandType.Text, query);
if (dt != null)
{
List <PurchaseIndentRegister> result = new List <PurchaseIndentRegister>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
PurchaseIndentRegister register = new PurchaseIndentRegister();
register.ID = row["Pi_Id"] != DBNull.Value ? Convert.ToInt32(row["Pi_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.IndentNo = Convert.ToString(row["Indent_No"]);
register.CostCenter = Convert.ToString(row["Cost_Center"]);
register.JobName = Convert.ToString(row["Job"]);
register.SupplierMail = Convert.ToString(row["Supplier_Mail"]);
register.SupplierMailBCC = Convert.ToString(row["SupplierBCC_Mail"]);
register.SupplierMailCC = Convert.ToString(row["SupplierCC_Mail"]);
register.EntryDate = row["Request_Date"] != DBNull.Value ? Convert.ToDateTime(row["Request_Date"]) : DateTime.MinValue;
register.EntryDateString = row["Request_Date"] != DBNull.Value ? Convert.ToDateTime(row["Request_Date"]).ToString("dd/MMM/yyyy") : string.Empty;
register.Priority = row["Priorities"] != DBNull.Value ? Convert.ToBoolean(row["Priorities"]) : false;
register.DueDateString = row["Due_Date"] != DBNull.Value ? Convert.ToDateTime(row["Due_Date"]).ToString("dd/MMM/yyyy") : string.Empty;
register.Gross = row["Gross_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Gross_Amount"]) : 0;
register.TaxAmount = row["Tax_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Tax_Amount"]) : 0;
register.NetAmount = row["Net_Amount"] != DBNull.Value ? Convert.ToDecimal(row["Net_Amount"]) : 0;
register.Narration = Convert.ToString(row["Narration"]);
register.RoundOff = row["Round_Off"] != DBNull.Value ? Convert.ToDecimal(row["Round_Off"]) : 0;
register.TermsandConditon = Convert.ToString(row["TandC"]);
register.Payment_Terms = Convert.ToString(row["Payment_Terms"]);
result.Add(register);
}
return(result);
}
return(null);
}
catch (Exception ex)
{
Application.Helper.LogException(ex, "PurchaseIndent | GetDetails( DateTime? from, DateTime? to)");
return(null);
}
finally
{
db.Close();
}
}