UploadingTrans.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using PX.Data;
using PX.Common;
using PX.Objects.GL;
using PX.Objects.IN;
using PX.Objects.CM;
using PX.Export;

namespace Performance
{
	public class ImportTest : PXGraph<ImportTest>
	{
		public const string SessionKey = "MyFileImportSessionKey";

		public PXFilter<Filter> Filter;
		public PXSelect<Details, Where<Details.batchNbr, Equal<BatchConstant>>> Transactions;
		public PXSelect<Details> NewFilePanel;

		public PXSave<Filter> Save;
		public PXCancel<Filter> Cancel;

		public PXAction<Filter> CleanUp;
		[PXUIField(DisplayName = "Clean Up", MapEnableRights = PXCacheRights.Select, MapViewRights = PXCacheRights.Select, Visible = true)]
		[PXButton()]
		public virtual IEnumerable cleanUp(PXAdapter adapter)
		{
			foreach (Details tran in Transactions.Select())
			{
				Transactions.Delete(tran);
			}
			this.Actions.PressSave();

			return adapter.Get();
		}

		public PXAction<Filter> UploadFileBatch;
		[PXUIField(DisplayName = "Upload File as Batches", MapEnableRights = PXCacheRights.Select, MapViewRights = PXCacheRights.Select, Visible = true)]
		[PXButton(CommitChanges = true)]
		public virtual IEnumerable uploadFileBatch(PXAdapter adapter)
		{
			if (this.NewFilePanel.AskExt() == WebDialogResult.OK)
			{
				PX.SM.FileInfo info = PXContext.SessionTyped<PXSessionStatePXData>().FileInfo[SessionKey] as PX.SM.FileInfo;
				System.Web.HttpContext.Current.Session.Remove(SessionKey);

				PX.Data.Update.PXUpdateLog.WriteMessage(String.Format("Starting. {0} Threads, File Name - {1}.----------------------------------------------------", Filter.Current.Threads ?? 1, info.FullName));

				Random rnd = new Random();
				PXLongOperation.StartOperation(this.UID, delegate() { ImportBatch(info, rnd.Next(1, 1000) * 10000); });
				for (int i = 1; i < (Filter.Current.Threads ?? 1); i++)
				{
					PXLongOperation.StartOperation(Guid.NewGuid(), delegate() { ImportBatch(info, rnd.Next(1, 1000) * 10000); });
				}				
			}

			return adapter.Get();
		}

		public PXAction<Filter> UploadFileStandalone;
		[PXUIField(DisplayName = "Upload File as Standalone", MapEnableRights = PXCacheRights.Select, MapViewRights = PXCacheRights.Select, Visible = true)]
		[PXButton(CommitChanges=true)]
		public virtual IEnumerable uploadFileStandalone(PXAdapter adapter)
		{
			if (this.NewFilePanel.AskExt() == WebDialogResult.OK)
			{
				PX.SM.FileInfo info = PXContext.SessionTyped<PXSessionStatePXData>().FileInfo[SessionKey] as PX.SM.FileInfo;
				System.Web.HttpContext.Current.Session.Remove(SessionKey);

				PX.Data.Update.PXUpdateLog.WriteMessage(String.Format("Starting. {0} Threads, File Name - {1}.----------------------------------------------------", Filter.Current.Threads ?? 1, info.FullName));

				Random rnd = new Random();
				PXLongOperation.StartOperation(this.UID, delegate() { ImportTran(info, rnd.Next(1, 1000) * 10000); });
				for (int i = 1; i < (Filter.Current.Threads ?? 1); i++)
				{
					PXLongOperation.StartOperation(Guid.NewGuid(), delegate() { ImportTran(info, rnd.Next(1, 1000) * 10000); });
				}	
			}
			return adapter.Get();
		}

		private static void ImportTran(PX.SM.FileInfo file, Int32 range = 1)
		{
			ImportTest graph = new ImportTest();
			Byte[] bytes = file.BinData;

			using(PX.Data.XLSXReader reader = new XLSXReader(bytes))
			{
				//Initialising Reader
				reader.Reset(); 
				//Creating a dictionary to find column index by name
				Dictionary<String, Int32> indexes = reader.IndexKeyPairs.ToDictionary(p => p.Value, p => p.Key);
				//Skipping first row with collumns names.
				reader.MoveNext();

				Int32 counter = range;
				DateTime start = DateTime.Now;
				while(reader.MoveNext())
				{
					Details row = graph.Transactions.Cache.CreateInstance() as Details;
					row.BatchNbr = "0";
					row.LineNbr = counter;
					row = graph.Transactions.Insert(row);

					//graph.Transactions.Cache.SetValueExt(row, "BranchID", reader.GetValue(indexes["Branch"]));
					graph.Transactions.Cache.SetValueExt(row, "LedgerID", "ACTUAL");
					graph.Transactions.Cache.SetValueExt(row, "AccountID", reader.GetValue(indexes["Account"]));
					graph.Transactions.Cache.SetValueExt(row, "SubID", reader.GetValue(indexes["Subaccount"]).Replace("-",""));

					row.UOM = String.IsNullOrWhiteSpace(reader.GetValue(indexes["UOM"])) ? null : reader.GetValue(indexes["UOM"]);
					row.TranDesc = reader.GetValue(indexes["Description"]);

					row.Qty = Decimal.Parse(reader.GetValue(indexes["Quantity"]));
					row.CreditAmt = Decimal.Parse(reader.GetValue(indexes["Debit Amount"]));
					row.DebitAmt = Decimal.Parse(reader.GetValue(indexes["Credit Amount"]));

					row = graph.Transactions.Update(row);
					counter++;
				
					if ((counter % 1000) == 0)
					{
						graph.Actions.PressSave();
						graph.Clear();
					}
				}

				graph.Actions.PressSave();
				PX.Data.Update.PXUpdateLog.WriteMessage(String.Format("Range {0}. Total - {1} seconds", range, (DateTime.Now - start).TotalSeconds));
			}
		}
		private static void ImportBatch(PX.SM.FileInfo file, Int32 range = 1)
		{
			JournalEntry graph = new JournalEntry();
			Byte[] bytes = file.BinData;

			using (PX.Data.XLSXReader reader = new XLSXReader(bytes))
			{
				//Initialising Reader
				reader.Reset();
				//Creating a dictionary to find column index by name
				Dictionary<String, Int32> indexes = reader.IndexKeyPairs.ToDictionary(p => p.Value, p => p.Key);
				//Skipping first row with collumns names.
				reader.MoveNext();

				graph.BatchModule.Insert();

				Int32 counter = 1;
				DateTime start = DateTime.Now;
				while (reader.MoveNext())
				{
					GLTran row = graph.GLTranModuleBatNbr.Cache.CreateInstance() as GLTran;
					row = graph.GLTranModuleBatNbr.Insert(row);

					//graph.Transactions.Cache.SetValueExt(row, "BranchID", reader.GetValue(indexes["Branch"]));
					graph.GLTranModuleBatNbr.Cache.SetValueExt(row, "AccountID", reader.GetValue(indexes["Account"]));
					graph.GLTranModuleBatNbr.Cache.SetValueExt(row, "SubID", reader.GetValue(indexes["Subaccount"]).Replace("-", ""));
					row.Qty = Decimal.Parse(reader.GetValue(indexes["Quantity"]));

					//row = graph.GLTranModuleBatNbr.Update(row);

					row.CuryDebitAmt = Decimal.Parse(reader.GetValue(indexes["Debit Amount"]));
					row.CuryCreditAmt = Decimal.Parse(reader.GetValue(indexes["Credit Amount"]));

					row = graph.GLTranModuleBatNbr.Update(row);
					if (row.ProjectID == null)
					{
						graph.GLTranModuleBatNbr.Cache.SetValueExt(row, "ProjectID", reader.GetValue(indexes["Project"]));
						row = graph.GLTranModuleBatNbr.Update(row);
					}

					counter++;
					if ((counter % 1000) == 0)
					{
						graph.Actions.PressSave();

						graph.Clear();
						graph.BatchModule.Insert();
					}
				}
				graph.Actions.PressSave();

				PX.Data.Update.PXUpdateLog.WriteMessage(String.Format("Range {0}. Total - {1} seconds", range, (DateTime.Now - start).TotalSeconds));
			}
		}
	}

	public class BatchConstant : Constant<String>
	{
		public BatchConstant()
			: base("0")
		{ }
	}

	public partial class Filter : PX.Data.IBqlTable
	{
		#region Threads
		public abstract class threads : PX.Data.IBqlField{}
		[PXInt(MinValue = 1, MaxValue = 4)]
		[PXUIField(DisplayName = "Threads ")]
		public virtual Int32? Threads { get; set; }
		#endregion
	}
	public partial class Details : PX.Data.IBqlTable
	{
		#region BatchNbr
		public abstract class batchNbr : PX.Data.IBqlField{	}
		[PXDBString(15, IsUnicode = true, IsKey = true)]
		[PXUIField(DisplayName = "Batch Number", Visibility = PXUIVisibility.Visible, Visible = false)]
		public virtual String BatchNbr { get; set; }
		#endregion
		#region LineNbr
		public abstract class lineNbr : PX.Data.IBqlField	{	}
		[PXDBInt(IsKey = true)]
		[PXDefault()]
		[PXUIField(DisplayName = "Line Nbr.")]
		public virtual Int32? LineNbr { get; set; }
		#endregion
		#region LedgerID
		public abstract class ledgerID : PX.Data.IBqlField	{	}
		[PXDBInt()]
		[PXDefault()]
		[PXSelector(typeof(Search4<Ledger.ledgerID,
			Where<Ledger.balanceType, NotEqual<BudgetLedger>>,
			Aggregate<GroupBy<Ledger.ledgerID>>>),
				SubstituteKey = typeof(Ledger.ledgerCD))]
		[PXUIField(DisplayName = "Ledger ID")]
		public virtual Int32? LedgerID { get; set; }
		#endregion
		#region AccountID
		public abstract class accountID : PX.Data.IBqlField	{	}
		[Account()]
		[PXDefault]
		public virtual Int32? AccountID { get; set; }
		#endregion
		#region SubID
		public abstract class subID : PX.Data.IBqlField	{	}
		[SubAccount(typeof(Details.accountID))]
		[PXDefault]
		public virtual Int32? SubID { get; set; }
		#endregion
		#region InventoryID
		public abstract class inventoryID : PX.Data.IBqlField	{	}
		[Inventory(Enabled = false, Visible = false)]
		public virtual Int32? InventoryID { get; set; }
		#endregion
		#region UOM
		public abstract class uOM : PX.Data.IBqlField	{	}
		[INUnit(typeof(Details.inventoryID))]
		public virtual String UOM { get; set; }
		#endregion
		#region Qty
		public abstract class qty : PX.Data.IBqlField	{	}
		[PXDBQuantity()]
		[PXDefault(TypeCode.Decimal, "0.0")]
		[PXUIField(DisplayName = "Quantity", Visibility = PXUIVisibility.Visible)]
		public virtual Decimal? Qty { get; set; }
		#endregion
		#region DebitAmt
		public abstract class debitAmt : PX.Data.IBqlField	{	}
		[PXDBBaseCury(typeof(Details.ledgerID))]
		[PXDefault(TypeCode.Decimal, "0.0")]
		[PXUIField(DisplayName = "Debit Amt")]
		public virtual Decimal? DebitAmt { get; set; }
		#endregion
		#region CreditAmt
		public abstract class creditAmt : PX.Data.IBqlField	{		}
		[PXDBBaseCury(typeof(Details.ledgerID))]
		[PXDefault(TypeCode.Decimal, "0.0")]
		[PXUIField(DisplayName = "Credit Amt")]
		public virtual Decimal? CreditAmt { get; set; }
		#endregion
		#region TranDesc
		public abstract class tranDesc : PX.Data.IBqlField	{	}
		[PXDBString(256, IsUnicode = true)]
		[PXUIField(DisplayName = "Transaction Description", Visibility = PXUIVisibility.Visible)]
		public virtual String TranDesc { get; set; }
		#endregion
		#region TranDate
		public abstract class tranDate : PX.Data.IBqlField	{	}
		[PXDBDate()]
		[PXDefault(typeof(AccessInfo.businessDate))]
		[PXUIField(DisplayName = "Transaction Date", Visibility = PXUIVisibility.Visible, Enabled = false)]
		public virtual DateTime? TranDate { get; set; }
		#endregion
	}
}