public ArticleDataExportModel GetExportArticlesData(string nodeCode)
{
var articleDataExportModel = new ArticleDataExportModel();
//var articlePara = new List<SqlParameter>();
//articlePara.Add(new SqlParameter { ParameterName = "@nodeCode", SqlDbType = System.Data.SqlDbType.VarChar, Direction = System.Data.ParameterDirection.Input, Value = nodeCode });
//DbCommand cmd = EntityDataExtensions.CreateStoreCommand(SpectrumContext, "dbo.ExportArticleData", System.Data.CommandType.StoredProcedure, articlePara.ToArray());
//// Use a connection scope to manage the lifetime of the connection
//using (var connectionScope = cmd.Connection.CreateConnectionScope())
//{
// using (var reader = cmd.ExecuteReader())
// {
// // Materialize the recipe.
// var articleDetails = reader.Materialize<ArticleDetails>().Bind<ArticleDetails>(SpectrumContext);
// // Move on to the categories resultset and attach it to the recipe.
// // Also bind it to the datacontext, so the object tracking works correctly.
// reader.NextResult();
// articleDataExportModel.TaxDetails = reader.Materialize<TaxDetails>().Bind<TaxDetails>(SpectrumContext);
// // Materialize the recipe ingredient information and attach it to the recipe.
// // Also bind it to the datacontext, so the object tracking works correctly.
// reader.NextResult();
// var charDetails = reader.Materialize<CharDetails>().Bind<CharDetails>(SpectrumContext);
// // Materialize the ingredients and attach them to the datacontext to enable object tracking.
// reader.NextResult();
// var salesDetails = reader.Materialize<SalesDetails>().Bind<SalesDetails>(SpectrumContext);
// // Iterate over the ingredient information and attach the ingredients to the records
// foreach (var item in articleDataExportModel.SalesDetails)
// {
// // Attach the related ingredient to the ingredient reference property
// //item.IngredientReference.Attach(ingredients.FirstOrDefault(
// //ingredient => ingredient.IngredientId == item.IngredientId));
// }
// reader.NextResult();
// var purchaseDetails = reader.Materialize<PurchaseDetails>().Bind<PurchaseDetails>(SpectrumContext);
// // Iterate over the ingredients in the recipe and attach the related unit information
// foreach (var ingredient in articleDataExportModel.PurchaseDetails.Select(item => item.ArticleCode))
// {
// // Attach the related unit to the unit reference property
// //ingredient.UnitReference.Attach(units.FirstOrDefault(unit => unit.UnitId == ingredient.UnitId));
// }
// }
//}
return(articleDataExportModel);
}
private void btnGetData_Click(object sender, EventArgs e)
{
try
{
if (IsFormValidate())
{
int gridCount = gridBarcode.Rows.Count;
if (gridCount > 1)
{
gridBarcode.Rows.RemoveRange(1, gridBarcode.Rows.Count - 1);
}
ArticleManager articleHierarchyManager = new ArticleManager();
ArticleDataExportModel articleDataExportModel = articleHierarchyManager.GetArticleExportData(NodeCode, CommonModel.SiteCode);
if (cboBarCodeType.SelectedValue != null)
{
// articleDataExportModel.ArticleDetails = articleDataExportModel.ArticleDetails.Where(x => x.DISCRIPTION == cboBarCodeType.SelectedValue.ToString()).ToList();
articleDataExportModel.ArticleDetails = articleDataExportModel.ArticleDetails.Where(x => x.BarcodeType == cboBarCodeType.SelectedValue.ToString()).ToList(); //vipin
}
if (articleDataExportModel.ArticleDetails.Count > 0)
{
btnExport.Visible = true;
foreach (var item in articleDataExportModel.ArticleDetails)
{
gridBarcode.Rows.Add();
gridBarcode.Rows[gridBarcode.Rows.Count - 1][(int)enumBarcode.ItemCode] = item.ArticleCode.ToString();
// gridBarcode.Rows[gridBarcode.Rows.Count - 1][(int)enumBarcode.EAN] = item.EAN.ToString();
gridBarcode.Rows[gridBarcode.Rows.Count - 1][(int)enumBarcode.EAN] = item.Barcode.ToString();
gridBarcode.Rows[gridBarcode.Rows.Count - 1][(int)enumBarcode.ItemDescription] = item.ArticleShortName.ToString();
// gridBarcode.Rows[gridBarcode.Rows.Count - 1][(int)enumBarcode.BarcodeType] = item.DISCRIPTION.ToString();
gridBarcode.Rows[gridBarcode.Rows.Count - 1][(int)enumBarcode.BarcodeType] = item.BarcodeType.ToString(); //vipin
gridBarcode.Rows[gridBarcode.Rows.Count - 1][(int)enumBarcode.Exclude] = false;
}
}
else
{
btnExport.Visible = false;
CommonFunc.ShowMessage("Data Not Exist", MessageType.Information);
}
}
}
catch (System.Exception ex)
{
CommonFunc.ShowMessage(ex.Message, MessageType.Information);
Logger.Log(ex.Message, Logger.LogingLevel.Error);
}
}
private void btnExport_Click(object sender, EventArgs e)
{
try
{
ArticleManager articleHierarchyManager = new ArticleManager();
ArticleDataExportModel articleDataExportModel = articleHierarchyManager.GetArticlesForBarcodeExportData(NodeCode, CommonModel.SiteCode);
if (cboBarCodeType.SelectedValue != null)
{
// articleDataExportModel.ArticleDetails = articleDataExportModel.ArticleDetails.Where(x => x.DISCRIPTION == cboBarCodeType.SelectedValue.ToString()).ToList();
articleDataExportModel.ArticleDetails = articleDataExportModel.ArticleDetails.Where(x => x.BarcodeType == cboBarCodeType.SelectedValue.ToString()).ToList(); //vipin
}
var barCodeList = (from result in articleDataExportModel.ArticleDetails
select new BarcodeModelExcel
{
ArticleName = result.ArticleName,
ArticleCode = result.ArticleCode,
// BARCODE = result.EAN,
BARCODE = result.Barcode, // added by vipin
SellingPrice = result.SellingPrice,
MRP = result.MRP,
UOM = result.BaseUoM,
NetWeight = result.NetWeight,
ExpiryDate = ""
}).ToList();
DataTable dtExport = CommonFunc.ConvertListToDataTable(barCodeList);
dtExport.Columns[0].ColumnName = "Article Name";
dtExport.Columns[1].ColumnName = "Article Code";
dtExport.Columns[2].ColumnName = "BARCODE";
dtExport.Columns[3].ColumnName = "Selling Price";
dtExport.Columns[4].ColumnName = "MRP";
dtExport.Columns[5].ColumnName = "UOM";
dtExport.Columns[6].ColumnName = "Net Weight";
dtExport.Columns[7].ColumnName = "Expiry Date";
dtExport.Columns.Add("IsExclude");
var deleteArticleCodeRow = new List <ItemExclude>();
for (int i = 1; i < gridBarcode.Rows.Count - 1; i++)
{
ItemExclude itemExclude = new ItemExclude();
if (gridBarcode.Rows[i][(int)enumBarcode.Exclude].ToString().ToLower() == "true")
{
itemExclude.ArticleCode = gridBarcode.Rows[i][(int)enumBarcode.ItemCode].ToString();
deleteArticleCodeRow.Add(itemExclude);
}
}
foreach (DataRow dr in dtExport.Rows)
{
foreach (var item in deleteArticleCodeRow)
{
if (dr["Article Code"].ToString() == item.ArticleCode)
{
dr["IsExclude"] = "Exclude";
break;
}
else
{
dr["IsExclude"] = "Include";
}
}
}
DataView dv = new DataView(dtExport);
dv.RowFilter = "IsExclude = 'Include'";
DataTable dt = new DataTable();
dt = dv.ToTable();
dt.Columns.Remove("IsExclude");
Cursor.Current = Cursors.WaitCursor;
DialogResult resultShow = fbd.ShowDialog();
if (resultShow == DialogResult.OK)
{
string path = Path.Combine(fbd.SelectedPath, "ArticelHierarchy" + DateTime.Now.ToString("dd-MM-yyyy-hhmm") + ".xls").ToString();
bool IsExported = ConvertListToExcel.DatatableToExcel(dt, path);
System.Diagnostics.Process.Start(path);
if (IsExported)
{
MessageBox.Show("Exported Successfully");
}
}
Cursor.Current = Cursors.Default;
}
catch (System.Exception ex)
{
CommonFunc.ShowMessage(ex.Message, MessageType.Information);
Logger.Log(ex.Message, Logger.LogingLevel.Error);
}
}
public static Boolean ArticleDataExportListToExcel(ArticleDataExportModel articleDataExportModel, string FilePathName)
{
Boolean result = false;
//Application ExcelApp = new Application();
//Workbook ExcelWorkBook = null;
//Worksheet ExcelWorkSheet = null;
//ExcelApp.Visible = false;
//// Get a new workbook.
//ExcelWorkBook = ExcelApp.Workbooks.Add(Missing.Value);
////ExcelWorkBook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//try
//{
// IList<PurchaseDetails> listPurchaseDetails = new List<PurchaseDetails>();
// listPurchaseDetails = articleDataExportModel.PurchaseDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// ExcelWorkSheet.Name = "PurchaseDetails";
// ListsToExcel(listPurchaseDetails, ref ExcelWorkSheet);
// IList<SalesDetails> listSalesDetails = new List<SalesDetails>();
// listSalesDetails = articleDataExportModel.SalesDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// ExcelWorkSheet.Name = "SalesDetails";
// ListsToExcel(listSalesDetails, ref ExcelWorkSheet);
// IList<CharDetails> listCharDetails = new List<CharDetails>();
// listCharDetails = articleDataExportModel.CharDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// ExcelWorkSheet.Name = "CharDetails";
// ListsToExcel(listCharDetails, ref ExcelWorkSheet);
// IList<TaxDetails> listTaxDetails = new List<TaxDetails>();
// listTaxDetails = articleDataExportModel.TaxDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// ExcelWorkSheet.Name = "TaxDetails";
// ListsToExcel(listTaxDetails, ref ExcelWorkSheet);
// IList<ArticleDetails> listArticleDetails = new List<ArticleDetails>();
// listArticleDetails = articleDataExportModel.ArticleDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// ExcelWorkSheet.Name = "ArticleDetails";
// ListsToExcel(listArticleDetails, ref ExcelWorkSheet);
// ExcelWorkBook.SaveAs(FilePathName);
// ExcelWorkBook.Close();
// ExcelApp.Quit();
// Marshal.ReleaseComObject(ExcelWorkSheet);
// Marshal.ReleaseComObject(ExcelWorkBook);
// Marshal.ReleaseComObject(ExcelApp);
// result = true;
C1XLBook ExcelWorkBook = new C1XLBook(); //vipin on 02-04-2016
try
{
IList <ArticleDetails> listArticleDetails = new List <ArticleDetails>();
listArticleDetails = articleDataExportModel.ArticleDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
XLSheet ExcelWorkSheet4 = ExcelWorkBook.Sheets[0];
// ExcelWorkSheet4.Name = "ArticleDetails";Article Data
ExcelWorkSheet4.Name = "Article Data";
ListsToExcelUsingC1(listArticleDetails, ref ExcelWorkSheet4, "Article Data");
IList <TaxDetails> listTaxDetails = new List <TaxDetails>();
listTaxDetails = articleDataExportModel.TaxDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
XLSheet ExcelWorkSheet3 = ExcelWorkBook.Sheets.Add();
ExcelWorkSheet3.Name = "Article Tax";
ListsToExcelUsingC1(listTaxDetails, ref ExcelWorkSheet3, "Article Tax");
IList <CharDetails> listCharDetails = new List <CharDetails>();
listCharDetails = articleDataExportModel.CharDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
XLSheet ExcelWorkSheet2 = ExcelWorkBook.Sheets.Add();
ExcelWorkSheet2.Name = "Article Char";
ListsToExcelUsingC1(listCharDetails, ref ExcelWorkSheet2, "Article Char");
IList <SalesDetails> listSalesDetails = new List <SalesDetails>();
listSalesDetails = articleDataExportModel.SalesDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
XLSheet ExcelWorkSheet1 = ExcelWorkBook.Sheets.Add();
ExcelWorkSheet1.Name = "Barcode Pricing";
ListsToExcelUsingC1(listSalesDetails, ref ExcelWorkSheet1, "Barcode Pricing");
IList <PurchaseDetails> listPurchaseDetails = new List <PurchaseDetails>();
listPurchaseDetails = articleDataExportModel.PurchaseDetails;
// ExcelWorkSheet = ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
// Worksheet ExcelWorkSheet = ExcelWorkBook.Worksheets[1];
XLSheet ExcelWorkSheet0 = ExcelWorkBook.Sheets.Add();
ExcelWorkSheet0.Name = "Add. Purchase UOMs";
ListsToExcelUsingC1(listPurchaseDetails, ref ExcelWorkSheet0, "Add. Purchase UOMs");
ExcelWorkBook.Save(FilePathName);
// ExcelWorkBook.Close();
// xlApp.Quit();
// Marshal.ReleaseComObject(ExcelWorkSheet);
// Marshal.ReleaseComObject(ExcelWorkBook);
// Marshal.ReleaseComObject(xlApp);
result = true;
}
catch (Exception exHandle)
{
Logging.Logger.Log(exHandle, Logging.Logger.LogingLevel.Error);
throw exHandle;
}
finally
{
foreach (Process process in Process.GetProcessesByName("Excel"))
{
process.Kill();
}
}
return(result);
}