private void AnalyzeExcelFile(HttpContext context)
{
string ProductsId = context.Request.Params["productsid"];
string PricingAction = context.Request.Params["pricingaction"];
if (!string.IsNullOrEmpty(ProductsId) && !string.IsNullOrEmpty(PricingAction))
{
if (GetStreamFromFile(context))
{
if (GetConfigurations())
{
if (GetExcelSheet())
{
if (ValidateSQHeader())
{
//Get all the branches
List<PricingIndexExcel> ListBranches = pricingReadExcel.GetValuesIndexExcel(PricingExcelStream, PricingSheetName, pricingConfig.ColumnBranch);
if (ValidateDualBranch(ListBranches))
{
ErrorCounter = 0;
//Get start and end rows index
int startInfoRow = int.Parse(pricingConfig.PricingHeader) + 1;
int endInfRow = ListBranches[ListBranches.Count - 1].Position;
//Validate the information
List<PricingExcelObj> ListPricingExcelObj = new List<PricingExcelObj>();
for (int i = startInfoRow; i < endInfRow + 1; i++)
{
PricingExcelObj PEO = new PricingExcelObj();
PEO.Prices = new List<SQFT>();
ValidateBranch(i, ref PEO);
ValidateAddINIFee(i, ref PEO);
ValidateRNLMult(i, ref PEO);
ValidateApps(i, ref PEO);
ValidatePrices(i, ref PEO);
ValidateExcessIncrement(i, ref PEO);
ValidateExcessValuePerIncrement(i, ref PEO);
ListPricingExcelObj.Add(PEO);
}
//Actions
switch (PricingAction)
{
case "validate":
if (ErrorCounter == 0)
{
if (ListPricingExcelObj.Count <= 250)
{
PricingResults.IsSuccess = true;
PricingResults.NumberOfRows = ListPricingExcelObj.Count.ToString();
PricingResults.ExcelTabName = PricingSheetName;
PricingResults.ExcelFileName = GlobalFileName;
}
else
{
PricingResults.IsSuccess = false;
AddError("There are more that 250 rows in the excel file");
}
}
else
{
PricingResults.IsSuccess = false;
}
break;
case "insert":
int CounterUploadesPricingData = 0;
int CounterRemovedPricingData = 0;
if (ErrorCounter <= 0)
{
PricingResults.IsSuccess = true;
SalesWebService SWS = new SalesWebService();
List<string> ListProducts = ProductsId.Split(',').ToList();
foreach (string prod in ListProducts)
{
int intProductID = int.Parse(prod);
Product cProduct = SWS.GetProductByID(intProductID);
CounterRemovedPricingData += SWS.DeletePricingDataByProductID(intProductID);
List<PricingRecord> ListnewPrices = new List<PricingRecord>();
if (cProduct.IsOneTime)//OneTime = true
{
ListnewPrices.Clear();
foreach (PricingExcelObj cPEO in ListPricingExcelObj)
{
ListnewPrices.Clear();
foreach (SQFT cPrice in cPEO.Prices)
{
PricingRecord newPrice = new PricingRecord();
newPrice.ProductID = intProductID;
newPrice.Branch = cPEO.BranchNumber;
newPrice.INI_Amount = 0;
newPrice.Reg_Amount = 0;
newPrice.Renewal_Amount = 0;
newPrice.OneTime_Amount = decimal.Parse(cPrice.Value);
newPrice.SqFtFrom = cPrice.SqFtFrom;
newPrice.SqFtTo = cPrice.SqTfTo;
newPrice.IsMultiplier = false;
newPrice.IsBaseRecord = false;
newPrice.CreatedBy = SPContext.Current.Web.CurrentUser.Name;
newPrice.DateCreated = DateTime.Now;
newPrice.LastUpdatedBy = SPContext.Current.Web.CurrentUser.Name;
newPrice.DateLastUpdated = DateTime.Now;
newPrice.Brand = "TG";
newPrice.FlatCostPerSqFt = cPEO.ExcessIncrement;
newPrice.FlatCostPerSqFtPrice = cPEO.ExcessValuePerIncrement;
newPrice.SpcFlatCostPerSqFt = 0;
newPrice.SpcFlatCostPerSqFtPrice = 0;
ListnewPrices.Add(newPrice);
}
SalesWSResult resultws = SWS.SendPricingRecords(ListnewPrices, intProductID);
CounterUploadesPricingData += resultws.CounterUpdated;
if (!resultws.IsSuccess)
{
PricingResults.IsSuccess = false;
AddError(resultws.Message);
}
SalesWSResult BPResult = SWS.UpdateBranchProduct(cPEO.BranchNumber, intProductID, int.Parse(cPEO.NumberAppas));
if (!BPResult.IsSuccess)
{
PricingResults.IsSuccess = false;
AddError(BPResult.Message);
}
}
//send to record
}
else
{
ListnewPrices.Clear();
foreach (PricingExcelObj cPEO in ListPricingExcelObj)
{
ListnewPrices.Clear();
foreach (SQFT cPrice in cPEO.Prices)
{
PricingRecord newPrice = new PricingRecord();
newPrice.ProductID = intProductID;
newPrice.Branch = cPEO.BranchNumber;
newPrice.INI_Amount = decimal.Parse(cPEO.AddINIFee);
newPrice.Reg_Amount = decimal.Parse(cPrice.Value);
newPrice.Renewal_Amount = decimal.Parse(cPrice.Value) * decimal.Parse(cPEO.RLNMult);
newPrice.OneTime_Amount = 0;
newPrice.SqFtFrom = cPrice.SqFtFrom;
newPrice.SqFtTo = cPrice.SqTfTo;
newPrice.IsMultiplier = false;
newPrice.IsBaseRecord = false;
newPrice.CreatedBy = SPContext.Current.Web.CurrentUser.Name;
newPrice.DateCreated = DateTime.Now;
newPrice.LastUpdatedBy = SPContext.Current.Web.CurrentUser.Name;
newPrice.DateLastUpdated = DateTime.Now;
newPrice.Brand = "TG";
newPrice.FlatCostPerSqFt = cPEO.ExcessIncrement;
newPrice.FlatCostPerSqFtPrice = cPEO.ExcessValuePerIncrement;
newPrice.SpcFlatCostPerSqFt = 0;
newPrice.SpcFlatCostPerSqFtPrice = 0;
ListnewPrices.Add(newPrice);
}
SalesWSResult resultws = SWS.SendPricingRecords(ListnewPrices, intProductID);
CounterUploadesPricingData += resultws.CounterUpdated;
if (!resultws.IsSuccess)
{
PricingResults.IsSuccess = false;
AddError(resultws.Message);
}
SalesWSResult BPResult = SWS.UpdateBranchProduct(cPEO.BranchNumber, intProductID, int.Parse(cPEO.NumberAppas));
if (!BPResult.IsSuccess)
{
PricingResults.IsSuccess = false;
AddError(BPResult.Message);
}
}
//send to record
}
}//End for eahc
PricingResults.NumberOfRows = ListPricingExcelObj.Count.ToString();
PricingResults.NumberofRemoved = CounterRemovedPricingData.ToString();
PricingResults.NumberofRowsPricingData = CounterUploadesPricingData.ToString();
}
break;
}
}
else//Fails validate branches dual
{
PricingResults.IsSuccess = false;
}
}
else//Fails to validated the header prices
{
PricingResults.IsSuccess = false;
}
}
else//Fails read sheets in file
{
PricingResults.IsSuccess = false;
}
}
else//Fail to get cconfigurations
{
PricingResults.IsSuccess = false;
}
}
else//Fails get stream
{
PricingResults.IsSuccess = false;
}
}
else//Productid or action empty
{
PricingResults.IsSuccess = false;
AddError("You need to select a action and products");
}
}
private void ValidateRNLMult(int rownumber, ref PricingExcelObj PEO)
{
string CellLocation = string.Format("{0}{1}", pricingConfig.ColumnRNLMult, rownumber.ToString());
string RNLMultValue = pricingReadExcel.GetIndividualCellValue(PricingExcelStream, PricingSheetName, CellLocation);
if (!string.IsNullOrEmpty(RNLMultValue))
{
int converter;
if (!int.TryParse(RNLMultValue, out converter) || int.Parse(RNLMultValue) < 0)
{
AddError(string.Format("The cell {0} is not valid", CellLocation));
}
else
{
PEO.RLNMult = RNLMultValue.ToString();
}
}
else
{
AddError(string.Format("The cell {0} is empty", CellLocation));
}
}
private void ValidateExcessValuePerIncrement(int rownumber, ref PricingExcelObj PEO)
{
string CellLocation = string.Format("{0}{1}", pricingConfig.ColumnExcessValuePerIncrement, rownumber.ToString());
string ExcessValuePerIncrementValue = pricingReadExcel.GetIndividualCellValue(PricingExcelStream, PricingSheetName, CellLocation);
if (!string.IsNullOrEmpty(ExcessValuePerIncrementValue))
{
decimal converter;
if (!decimal.TryParse(ExcessValuePerIncrementValue, out converter))
{
AddError(string.Format("The cell {0} is not valid", CellLocation));
}
else
{
PEO.ExcessValuePerIncrement = converter;
}
}
else
{
PEO.ExcessValuePerIncrement = 0;
}
}
private void ValidatePrices(int rownumber, ref PricingExcelObj PEO)
{
string tmpPrice = "-1";
//Normal
foreach (SQFT h in PricingFinalHeaders)
{
string CellLocation = string.Format("{0}{1}", h.Column, rownumber.ToString());
string PriceValue = pricingReadExcel.GetIndividualCellValue(PricingExcelStream, PricingSheetName, CellLocation);
if (!string.IsNullOrEmpty(PriceValue))
{
double converter;
if (double.TryParse(PriceValue, out converter))
{
if (converter > 0)
{
double tmpconverter;
if (double.TryParse(tmpPrice, out tmpconverter))
{
if (converter < tmpconverter)
{
AddError(string.Format("The cell {0} must not less that the before value ", CellLocation));
}
else
{
h.Value = converter.ToString();
SQFT hh = h;
PEO.Prices.Add(hh);
}
}
}
else
{
AddError(string.Format("The cell {0} is not allowed $0.00 or less", CellLocation));
}
tmpPrice = PriceValue;
}
else
{
tmpPrice = "0";
AddError(string.Format("The cell {0} is not valid", CellLocation));
}
}
else
{
tmpPrice = "0";
AddError(string.Format("The cell {0} is empty", CellLocation));
}
}
//Extra
foreach (SQFT h in PricingExtraHeaders)
{
string CellLocation = string.Format("{0}{1}", h.Column, rownumber.ToString());
string PriceValue = pricingReadExcel.GetIndividualCellValue(PricingExcelStream, PricingSheetName, CellLocation);
if (!string.IsNullOrEmpty(PriceValue))
{
AddError(string.Format("Invalid data found in column '{0}'", CellLocation));
}
}
}
private void ValidateApps(int rownumber, ref PricingExcelObj PEO)
{
string CellLocation = string.Format("{0}{1}", pricingConfig.ColumnNofApps, rownumber.ToString());
string AppsValue = pricingReadExcel.GetIndividualCellValue(PricingExcelStream, PricingSheetName, CellLocation);
if (!string.IsNullOrEmpty(AppsValue))
{
double converter;
if (!double.TryParse(AppsValue, out converter) || double.Parse(AppsValue) < 1)
{
AddError(string.Format("The cell {0} is not valid", CellLocation));
}
else
{
PEO.NumberAppas = AppsValue.ToString();
}
}
else
{
AddError(string.Format("The cell {0} is empty", CellLocation));
}
}