public List <ItemDetails> GetItemsByID(List <int> ItemIds) { QuotationDataAccess obj = new QuotationDataAccess(); var result = obj.GetItemsById(ItemIds); return(MapItemDetails(result)); }
public Quotation GetQuotationVersionDetails(string custId, string version) { QuotationDataAccess obj = new QuotationDataAccess(); var result = obj.GetQuotationVersionDetails(custId, version); Quotation quotation = new Quotation(); List <QuotationItem> quotationItems = new List <QuotationItem>(); foreach (DataRow dr in result.Rows) { //quotationItems = new List<QuotationItem>(); QuotationItem quotationItem = new QuotationItem(); quotationItem.Name = dr["QuotationId"].ToString(); quotationItem.IsItem = Convert.ToBoolean(dr["IsItem"].ToString()); quotationItem.ItemId = Convert.ToInt32(dr["ItemId"]); quotationItem.Margin = float.Parse(dr["ItemMargin"].ToString()); quotationItem.Name = dr["Name"].ToString(); quotationItem.Quantity = dr["Quantity"].ToString(); quotationItem.Description = dr["Description"].ToString(); quotationItem.UnitRate = float.Parse(dr["UnitRate"].ToString()); quotationItem.Total = float.Parse(dr["Rate"].ToString()); quotationItem.Unit = dr["Unit"].ToString(); quotationItems.Add(quotationItem); } quotation.GrossMargin = float.Parse(result.Rows[0]["GrossMargin"].ToString()); quotation.Total = float.Parse(result.Rows[0]["Total"].ToString()); quotation.Version = Convert.ToInt32(result.Rows[0]["VersionNumber"].ToString()); quotation.Items = quotationItems; return(quotation); }
public List <Item> GetAllItems(string searchTerm) { QuotationDataAccess obj = new QuotationDataAccess(); var result = obj.GetAllItems(searchTerm); return(MapItem(result)); }
public bool AddItem(string name, string description, string unit, string specification, int unitRate, int labour, int addedBy, int margin, int tax, List <int> comboItems) { QuotationDataAccess obj = new QuotationDataAccess(); var result = obj.AddItem(name, description, unit, specification, unitRate, labour, addedBy, margin, tax, comboItems); return(result); }
public ItemDetails GetItemDetails(string itemId) { QuotationDataAccess obj = new QuotationDataAccess(); var result = obj.GetItemDetails(itemId); ItemDetails data = new ItemDetails(); if (result != null && result.Rows.Count > 0) { List <int> childs = new List <int>(); foreach (DataRow dn in result.Rows) { childs.Add(Convert.ToInt32(dn["ChildItemId"])); } data.ChildItems = childs; DataRow dr = result.Rows[0]; data.Name = dr["Name"].ToString(); data.Description = dr["Description"].ToString(); data.Unit = dr["Unit"].ToString(); data.Specification = dr["Specification"].ToString(); data.UnitRate = Convert.ToInt32(dr["UnitRate"]); data.ItemId = Convert.ToInt32(dr["ItemId"]); data.Labour = Convert.ToInt32(dr["Labour"]); data.Margin = Convert.ToInt32(dr["Margin"]); data.Tax = Convert.ToInt32(dr["Tax"]); data.IsComboItem = Convert.ToBoolean(dr["IsComboItem"]); } return(data); }
public bool SaveQuotation(WebAPI.Models.Quotation quotation) { QuotationDataAccess obj = new QuotationDataAccess(); var result = obj.SaveQuotation(quotation); return(result); //return true; }
public List <QuotationView> GetQuotationVersion(string custId) { QuotationDataAccess obj = new QuotationDataAccess(); var result = obj.GetQuotationUserVersions(custId); List <QuotationView> quotations = new List <QuotationView>(); foreach (DataRow dr in result.Rows) { QuotationView quotation = new QuotationView(); quotation.QuotationId = Convert.ToInt64(dr["QuotationId"]); quotation.Version = Convert.ToInt32(dr["VersionNumber"]); quotations.Add(quotation); } return(quotations); }
public List <QuotationView> GetAllQuotations(string CustName, int numberOfRecords, int pageNumber) { QuotationDataAccess obj = new QuotationDataAccess(); var result = obj.GetAllQuotations(CustName, numberOfRecords, pageNumber); List <QuotationView> quotations = new List <QuotationView>(); foreach (DataRow dr in result.Rows) { QuotationView quotation = new QuotationView(); quotation.QuotationId = Convert.ToInt64(dr["QuotationId"]); quotation.CustomerId = Convert.ToInt64(dr["CustomerId"]); quotation.Version = Convert.ToInt32(dr["VersionNumber"]); quotation.CustomerName = dr["Name"].ToString(); quotations.Add(quotation); } return(quotations); }
public List <ItemDetails> GetItems(string searchTerm, int numberOfRecords, int pageNumber) { QuotationDataAccess obj = new QuotationDataAccess(); var result = obj.GetAllItems(searchTerm, numberOfRecords, pageNumber); List <ItemDetails> data = new List <ItemDetails>(); if (result != null && result.Rows.Count > 0) { foreach (DataRow dr in result.Rows) { ItemDetails det = new ItemDetails(); det.ItemId = Convert.ToInt32(dr["ItemId"]); det.Name = dr["Name"].ToString(); det.UnitRate = Convert.ToInt32(dr["UnitRate"]); det.TotalCount = Convert.ToInt32(dr["TotalCount"]); det.IsComboItem = Convert.ToBoolean(dr["IsComboItem"]); det.Unit = dr["Unit"].ToString(); data.Add(det); } } return(data); }
public byte[] GetQuotationExport(string custId, string version, bool isCustomer, bool isSupplyLabourTogether, string customerName) { QuotationDataAccess obj = new QuotationDataAccess(); var result = obj.GetQuotationVersionDetails(custId, version, isCustomer); byte[] resultArray = null; using (ExcelPackage package = new ExcelPackage()) { int startRowFrom = 2; string unitName = string.Empty; ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("Electrical"); int itemCOunt = 0; workSheet.Cells["A2"].Value = "SL. No"; workSheet.Cells["B2"].Value = "Description"; workSheet.Cells["C2"].Value = "Unit"; workSheet.Cells["D2"].Value = "QTY"; workSheet.Cells["E2"].Value = "Rate"; workSheet.Cells["F2"].Value = "Amount"; //workSheet.Cells["A2"].Style.Font.Size = 15; //workSheet.Cells["B2"].Style.Font.Size = 15; //workSheet.Cells["C2"].Style.Font.Size = 15; //workSheet.Cells["D2"].Style.Font.Size = 15; //workSheet.Cells["E2"].Style.Font.Size = 15; //workSheet.Cells["F2"].Style.Font.Size = 15; workSheet.Cells["A2"].Style.Font.Bold = true; workSheet.Cells["B2"].Style.Font.Bold = true; workSheet.Cells["C2"].Style.Font.Bold = true; workSheet.Cells["D2"].Style.Font.Bold = true; workSheet.Cells["E2"].Style.Font.Bold = true; workSheet.Cells["F2"].Style.Font.Bold = true; int itemProp = 0; if (!isSupplyLabourTogether) { foreach (DataRow dr in result.Rows) { startRowFrom++; itemProp++; if (Convert.ToBoolean(dr["IsItem"])) { itemCOunt++; itemProp = 0; workSheet.Cells["A" + startRowFrom].Value = itemCOunt.ToString(); workSheet.Cells["B" + startRowFrom].Value = dr["Name"].ToString(); unitName = dr["Unit"].ToString(); workSheet.Cells["C" + startRowFrom].Value = string.Empty; workSheet.Cells["D" + startRowFrom].Value = float.Parse(dr["Quantity"].ToString()); var rate = float.Parse(dr["ItemTotal"].ToString()); var qty = float.Parse(dr["Quantity"].ToString()); rate = rate / qty; workSheet.Cells["E" + startRowFrom].Value = rate; workSheet.Cells["F" + startRowFrom].Value = float.Parse(dr["ItemTotal"].ToString()); startRowFrom++; workSheet.Cells["B" + startRowFrom].Value = dr["Description"].ToString(); startRowFrom++; workSheet.Cells["B" + startRowFrom].Value = dr["Specification"].ToString(); } else { // itemCOunt++; if (itemProp == 1 || itemProp == 2) { if (itemProp == 1) { workSheet.Cells["B" + startRowFrom].Value = "Supply"; } else { workSheet.Cells["B" + startRowFrom].Value = "Labour"; } workSheet.Cells["C" + startRowFrom].Value = unitName; workSheet.Cells["D" + startRowFrom].Value = float.Parse(dr["Quantity"].ToString()); var rate = float.Parse(dr["ItemTotal"].ToString()); var qty = float.Parse(dr["Quantity"].ToString()); rate = rate / qty; workSheet.Cells["E" + startRowFrom].Value = rate; workSheet.Cells["F" + startRowFrom].Value = float.Parse(dr["ItemTotal"].ToString()); } } } startRowFrom++; workSheet.Cells["B" + startRowFrom].Value = "TOTAL : "; workSheet.Cells["C" + startRowFrom].Value = float.Parse(result.Rows[0]["Total"].ToString()); // add the content into the Excel file //workSheet.Cells["A" + startRowFrom].LoadFromDataTable(result, true); // autofit width of cells with small content int columnIndex = 1; foreach (DataColumn column in result.Columns) { ExcelRange columnCells = workSheet.Cells[1, columnIndex, workSheet.Dimension.End.Row, columnIndex]; int maxLength = columnCells.Max(cell => cell.Value == null ? 0 : cell.Value.ToString().Count()); if (maxLength < 30) { workSheet.Column(columnIndex).AutoFit(); } if (columnIndex == 6) { break; } } workSheet.Column(2).Width = 50; // format header - bold, yellow on black using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, 6]) { r.Style.Font.Color.SetColor(System.Drawing.Color.White); r.Style.Font.Bold = true; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad")); } // format cells - add borders using (ExcelRange r = workSheet.Cells[2, 1, 2 + (result.Rows.Count + (result.Rows.Count * 2 / 3)), 6]) { r.Style.Border.Top.Style = ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; r.Style.Border.Left.Style = ExcelBorderStyle.Thin; r.Style.Border.Right.Style = ExcelBorderStyle.Thin; r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black); } } else { float prevRate = 0; float prevtotal = 0; int prevIndex = 2; foreach (DataRow dr in result.Rows) { itemProp++; if (Convert.ToBoolean(dr["IsItem"])) { startRowFrom++; itemCOunt++; itemProp = 0; prevtotal = 0; prevRate = 0; workSheet.Cells["A" + startRowFrom].Value = itemCOunt.ToString(); workSheet.Cells["B" + startRowFrom].Value = dr["Name"].ToString(); unitName = dr["Unit"].ToString(); workSheet.Cells["C" + startRowFrom].Value = dr["Unit"].ToString(); workSheet.Cells["D" + startRowFrom].Value = float.Parse(dr["Quantity"].ToString()); var rate = float.Parse(dr["ItemTotal"].ToString()); var qty = float.Parse(dr["Quantity"].ToString()); rate = rate / qty; workSheet.Cells["E" + startRowFrom].Value = rate; workSheet.Cells["F" + startRowFrom].Value = float.Parse(dr["ItemTotal"].ToString()); startRowFrom++; workSheet.Cells["B" + startRowFrom].Value = dr["Description"].ToString(); startRowFrom++; workSheet.Cells["B" + startRowFrom].Value = dr["Specification"].ToString(); } } startRowFrom++; workSheet.Cells["B" + startRowFrom].Value = "TOTAL : "; workSheet.Cells["C" + startRowFrom].Value = float.Parse(result.Rows[0]["Total"].ToString()); // add the content into the Excel file //workSheet.Cells["A" + startRowFrom].LoadFromDataTable(result, true); // autofit width of cells with small content int columnIndex = 1; foreach (DataColumn column in result.Columns) { ExcelRange columnCells = workSheet.Cells[1, columnIndex, workSheet.Dimension.End.Row, columnIndex]; int maxLength = columnCells.Max(cell => cell.Value == null ? 0 : cell.Value.ToString().Count()); if (maxLength < 30) { workSheet.Column(columnIndex).AutoFit(); } if (columnIndex == 6) { break; } } workSheet.Column(2).Width = 50; // format header - bold, yellow on black using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, 6]) { r.Style.Font.Color.SetColor(System.Drawing.Color.White); r.Style.Font.Bold = true; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad")); } // format cells - add borders using (ExcelRange r = workSheet.Cells[2, 1, result.Rows.Count + 2, 6]) { r.Style.Border.Top.Style = ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; r.Style.Border.Left.Style = ExcelBorderStyle.Thin; r.Style.Border.Right.Style = ExcelBorderStyle.Thin; r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black); } } // removed ignored columns //for (int i = dataTable.Columns.Count - 1; i >= 0; i--) //{ // if (i == 0 && showSrNo) // { // continue; // } // if (!columnsToTake.Contains(dataTable.Columns[i].ColumnName)) // { // workSheet.DeleteColumn(i + 1); // } //} if (!String.IsNullOrEmpty(customerName)) { workSheet.Cells["A1"].Value = customerName; workSheet.Cells["A1"].Style.Font.Size = 20; workSheet.InsertColumn(1, 1); workSheet.InsertRow(1, 1); workSheet.Column(1).Width = 5; } resultArray = package.GetAsByteArray(); } return(resultArray); }