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(); } }