public List <ViewItemSummary> GetSearchAllItems(string company, string category)
{
connection.ConnectionString = connectionString;
cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM ViewItemSummary WHERE CompanyName = @CompanyName AND CategoryName = @CategoryName ";
cmd.Connection = connection;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("CompanyName", company);
cmd.Parameters.AddWithValue("CategoryName", category);
connection.Open();
reader = cmd.ExecuteReader();
List <ViewItemSummary> seachItems = new List <ViewItemSummary>();
while (reader.Read())
{
ViewItemSummary viewItemSummary = new ViewItemSummary();
viewItemSummary.ItemName = reader["ItemName"].ToString();
viewItemSummary.CompanyName = reader["CompanyName"].ToString();
viewItemSummary.CategoryName = reader["CategoryName"].ToString();
viewItemSummary.AvailableQuantity = Convert.ToInt32(reader["AvailableQuantity"]);
viewItemSummary.ReorderLevel = Convert.ToInt32(reader["ReorderLevel"]);
seachItems.Add(viewItemSummary);
}
reader.Close();
connection.Close();
return(seachItems);
}
public List <ViewItemSummary> ViewItemSummaries(int companyId, int categoryId)
{
//string query =
// "Select i.ItemName,c.CompanyName,i.Quantity,i.ReorderLevel from Item_tb as i INNER JOIN Company_tb as c ON i.CompanyId=c.CompanyId WHERE i.CompanyId='" +
// companyId + "' AND i.CategoryId ='" + categoryId + "' ";
//string query =
// "Select i.ItemName,c.CompanyName,t.total_Quantity, st.StockOutQuantity,i.ReorderLevel from Item_tb as i INNER JOIN Company_tb as c ON i.CompanyId=c.CompanyId INNER JOIN Total AS t ON i.ItemId=t.ItemId INNER JOIN Total_StockOut AS st ON i.ItemId=st.ItemId WHERE i.CompanyId='" +
// companyId + "' AND i.CategoryId ='" + categoryId + "' ";
string query =
"Select i.ItemId, i.ItemName, c.CompanyName, ct.CategoryName , t.total_Quantity, st.StockOutQuantity,i.ReorderLevel from Item_tb as i LEFT JOIN Company_tb as c ON i.CompanyId=c.CompanyId LEFT JOIN Category AS ct ON i.CategoryId = ct.CategoryId LEFT JOIN Total AS t ON i.ItemId=t.ItemId LEFT JOIN Total_StockOut AS st ON i.ItemId=st.ItemId WHERE i.CompanyId='" +
companyId + "' AND i.CategoryId ='" + categoryId + "' ";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <ViewItemSummary> viewItemSummaries = new List <ViewItemSummary>();
while (reader.Read())
{
ViewItemSummary aViewItemSummary = new ViewItemSummary();
aViewItemSummary.ItemName = reader["ItemName"].ToString();
aViewItemSummary.CompanyName = reader["CompanyName"].ToString();
aViewItemSummary.CategoryName = reader["CategoryName"].ToString();
//aViewItemSummary.StockOutQuantity = (int) reader["StockOutQuantity"];
string stockOutQuantity = reader["StockOutQuantity"].ToString();
if (stockOutQuantity != "")
{
aViewItemSummary.StockOutQuantity = (int)reader["StockOutQuantity"];
}
else
{
aViewItemSummary.StockOutQuantity = 0;
}
string totalQuantity = reader["total_Quantity"].ToString();
if (totalQuantity != "")
{
aViewItemSummary.AvailableQuantity = (int)reader["total_Quantity"] - aViewItemSummary.StockOutQuantity;
}
else
{
aViewItemSummary.AvailableQuantity = 0;
}
aViewItemSummary.ReorderLevel = (int)reader["ReorderLevel"];
viewItemSummaries.Add(aViewItemSummary);
}
reader.Close();
connection.Close();
return(viewItemSummaries);
}
public static string GenerateItemSummaryPdf(ListView summaryListView)
{
string result = "";
try
{
SaveFileDialog svg = new SaveFileDialog();
svg.Filter = "PDF Files | *.pdf";
svg.DefaultExt = "pdf";
svg.ShowDialog();
Document document = new Document(PageSize.A4, 10, 10, 10, 10);
//PdfWriter writer =
PdfWriter.GetInstance(document, new FileStream(svg.FileName, FileMode.Create));
document.Open();
Paragraph para = new Paragraph("Stock Management System", FontFactory.GetFont("Papyrus", 20f, BaseColor.GRAY));
para.Alignment = Element.ALIGN_CENTER;
para.SpacingBefore = 10;
para.SpacingAfter = 20;
document.Add(para);
para = new Paragraph("Print Date: " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 7f, BaseColor.BLACK));
para.Alignment = Element.ALIGN_RIGHT;
para.SpacingBefore = 5;
para.SpacingAfter = 10;
document.Add(para);
para = new Paragraph("Item Summary", FontFactory.GetFont("Arial", 10f, BaseColor.BLACK));
para.Alignment = Element.ALIGN_CENTER;
para.SpacingBefore = 5;
para.SpacingAfter = 10;
document.Add(para);
PdfPTable table = new PdfPTable(6);
PdfPCell cell = new PdfPCell(new Phrase("SI", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 8f, BaseColor.WHITE)));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
cell.BackgroundColor = new BaseColor(0, 0, 0);
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Item Name", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 8f, BaseColor.WHITE)));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
cell.BackgroundColor = new BaseColor(0, 0, 0);
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Company", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 8f, BaseColor.WHITE)));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
cell.BackgroundColor = new BaseColor(0, 0, 0);
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Category", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 8f, BaseColor.WHITE)));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
cell.BackgroundColor = new BaseColor(0, 0, 0);
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Ava. Qty", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 8f, BaseColor.WHITE)));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
cell.BackgroundColor = new BaseColor(0, 0, 0);
table.AddCell(cell);
cell = new PdfPCell(new Phrase("Reorder L.", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 8f, BaseColor.WHITE)));
cell.HorizontalAlignment = Element.ALIGN_CENTER;
cell.BackgroundColor = new BaseColor(0, 0, 0);
table.AddCell(cell);
int rowcount = 0;
foreach (ListViewItem viewItem in summaryListView.Items)
{
ViewItemSummary item = (ViewItemSummary)viewItem.Tag;
table.AddCell((++rowcount).ToString());
table.AddCell(item.ItemName.Trim());
table.AddCell(item.CompanyName.Trim());
table.AddCell(item.CategoryName.Trim());
table.AddCell(item.AvailableQuantity.ToString());
table.AddCell(item.ReorderLevel.ToString());
}
document.Add(table);
document.Close();
result = "A PDF file has been generated";
}
catch (Exception ex)
{
result = "PDF file did not generate";
}
return(result);
}