/// <summary>
///
/// </summary>
/// <param name="binder"></param>
/// <returns></returns>
private static BinderTableRecords GetBinderTableMeasurements(string binder)
{
DataTable dataTable = new DataTable();
BinderTableRecords binderTableRecord = null;
string sqlConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
try
{
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_AbleAsaGetBinderTableMeasurements";
sqlCommand.Parameters.Add("@Binder", SqlDbType.Char).Value = binder;
logger.Info("call sp_AbleAsaGetBinderTableMeasurements stored procedure ");
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
sqlDataAdapter.Fill(dataTable);
}
}
catch (Exception ex)
{
logger.Error("Exception in sp_AbleAsaGetBinderTableMeasurements: " + ex.Message);
}
}
}
foreach (DataRow row in dataTable.Rows)
{
int spineWidth = 0;
int boardHeight = 0;
int boardWidth = 0;
Int32.TryParse(row[0].ToString(), out spineWidth);
Int32.TryParse(row[1].ToString(), out boardHeight);
Int32.TryParse(row[2].ToString(), out boardWidth);
binderTableRecord = new BinderTableRecords();
binderTableRecord.MaxBoardHeight = boardHeight;
binderTableRecord.MaxBoardWidth = boardWidth;
binderTableRecord.MaxSpineWidth = spineWidth;
}
return(binderTableRecord);
}
private static void AddBillingSizeCosts(string OrderNumber, string CustomerNumber, int BillId, BinderTableRecords binderTableRecords)
{
DataTable dataTable = new DataTable();
string sqlConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
try
{
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_AbleAsaGetSizeDtlBillingRecords";
sqlCommand.Parameters.Add("@BillId", SqlDbType.Int).Value = BillId;
logger.Info("call sp_AbleAsaGetSizeDtlBillingRecords stored procedure ");
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
sqlDataAdapter.Fill(dataTable);
}
}
catch (Exception ex)
{
logger.Error("Exception in sp_AbleAsaGetSizeDtlBillingRecords: " + ex.Message);
}
}
}
//Process each row in the data table
foreach (DataRow row in dataTable.Rows)
{
int unitPrice = 0;
int volumes = 1;
int size = 1;
int price = 1;
int increment = 0;
string productCode = "";
int productCodeId = 0;
Int32.TryParse(row[0].ToString(), out size);
Int32.TryParse(row[1].ToString(), out volumes);
Int32.TryParse(row[2].ToString(), out price);
Int32.TryParse(row[3].ToString(), out increment);
unitPrice = price / volumes;
if (unitPrice > 0)
{
DetailBillingRecord detailBillingRecord = new DetailBillingRecord();
detailBillingRecord.RecordType = "DTL";
detailBillingRecord.OrderNumber = OrderNumber;
detailBillingRecord.CustomerNumber = CustomerNumber;
if (size == 1)
{
productCode = "H";
productCodeId = binderTableRecords.MaxBoardHeight / 640;
productCodeId = productCodeId + increment - 1;
}
else if (size == 2)
{
productCode = "W";
productCodeId = binderTableRecords.MaxBoardWidth / 640;
productCodeId = productCodeId + increment;
}
else
{
productCode = "T";
productCodeId = binderTableRecords.MaxSpineWidth / 640;
productCodeId = productCodeId + increment - 1;
}
if (productCodeId.ToString().Length == 2)
{
productCode += productCodeId.ToString();
productCode += "0";
}
else
{
productCode += "0";
productCode += productCodeId.ToString();
productCode += "0";
}
detailBillingRecord.ProductCode = productCode; //productCode;
detailBillingRecord.Quantity = volumes;
detailBillingRecord.UnitPrice = unitPrice; //unitPrice;
detailBillingRecord.JobQuantity = "000000";
detailBillingRecord.Filler = " ";
logger.Info("Add Size Charge: " + " Order Number:" + OrderNumber + " CustomerNumber:" + CustomerNumber +
" ProductCode:" + detailBillingRecord.ProductCode + " Quantity:" + volumes + " Unit Price:" + unitPrice);
detailBillingRecordList.Add(detailBillingRecord);
}
else
{
logger.Info("Add Size Charge: " + " Order Number:" + OrderNumber + " CustomerNumber:" + CustomerNumber + " ProductCode:" + productCode + " Unit Price: 0");
}
}
}
/// <summary>
///
/// </summary>
/// <param name="binder"></param>
/// <param name="lotId"></param>
/// <param name="billByDept"></param>
/// <param name="Department"></param>
public static void GetDetailBillingExport(string account, string binder, string lotId)
{
int TotalJobQuantity = 0;
string SubAccount = "";
string CustomerNumber = "";
string OrderNumber = "";
string JobId = "";
string BillDepartment = "";
int BillId = 0;
//RMH Added 04-09-2018
bool billByDept = false;
string Department = "";
List <string> departmentList = new List <string>();
DataTable dataTable = new DataTable();
string sqlConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
try
{
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_AbleAsaBillingExport";
sqlCommand.Parameters.Add("@Binder", SqlDbType.VarChar).Value = binder.TrimEnd();
sqlCommand.Parameters.Add("@LotId", SqlDbType.VarChar).Value = lotId.TrimEnd();
sqlCommand.Parameters.Add("@Account", SqlDbType.VarChar).Value = account.TrimEnd();//Added 04-02-2018
logger.Info("call sp_AbleAsaBillingExport stored procedure ");
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
sqlDataAdapter.Fill(dataTable);
}
}
catch (Exception ex)
{
logger.Error("Exception in sp_AbleAsaBillingExport: " + ex.Message);
}
}
}
//Get Job Quantity for VPQ and HDR Records
//MOVED TotalJobQuantity = CalculateTotalJobQuanity( billByDept, Department, dataTable);
foreach (DataRow row in dataTable.Rows)
{
//Get Job Quantity for VPQ and HDR Records
Department = row[8].ToString().Trim();
billByDept = row[9].ToString() == "True" ? true : false;
if (departmentList.Contains(Department, StringComparer.OrdinalIgnoreCase) == false)
{
departmentList.Add(Department);
TotalJobQuantity = CalculateTotalJobQuanity(billByDept, Department, dataTable);
//}
//Check for bill by department and substitute
if (billByDept == true && Department.Length > 0)
{
//Get Department subaccount and set customer number
SubAccount = GetSubAccount(account, Department);
CustomerNumber = SubAccount;
}
else
{
CustomerNumber = row[1].ToString().TrimEnd();
}
OrderNumber = row[0].ToString().TrimEnd();
//break;
//}
//Check for bill by department and substitute
//if (billByDept == true && Department.Length > 0)
//{
//Get Department subaccount
// SubAccount = GetSubAccount(account, Department);
// CustomerNumber = SubAccount;
//}
//Add VPQ record
AddOrderHeaderRecord(OrderNumber, CustomerNumber, TotalJobQuantity);
//Get Job Id
JobId = GetJobFromOrderNumber(OrderNumber);
//Add HDR record
AddJobHeaderRecord(JobId, CustomerNumber, TotalJobQuantity);
//NEW to get the actual detail records returned from the sp_AbleAsaBillingExport stored proc
GetDetailRecordsForExport(Department, billByDept, SubAccount, dataTable);
//Moved from below
if (billByDept == false)
{
BillDepartment = "TOTALS";
}
else
{
BillDepartment = Department;
}
BillId = GetBillId(account, lotId, BillDepartment);
//Get detail extras list
AddDetailExtrasRecords(OrderNumber, CustomerNumber, BillId);
//Add Binding costs
AddBindingCostsFromBilling(OrderNumber, CustomerNumber, BillId);
//Get Binder table measurements
BinderTableRecords binderTableRecords = GetBinderTableMeasurements(binder);
//Height Width and Spine Costs
AddBillingSizeCosts(OrderNumber, CustomerNumber, BillId, binderTableRecords);
//Check if bill department is false - exit so duplicates are not sent
if (billByDept == false)
{
break;
}
}
}
/**********************************************************************************************************
* //Process each row in the data table
* foreach (DataRow row in dataTable.Rows)
* {
* int quantity = 0;
* int minutes = 1;
* string queryDepartment = "";
*
* if (billByDept == true)
* {
* queryDepartment = row[8].ToString().Trim();
* }
*
* string product = row[7].ToString();
* string productCode = row[2].ToString().TrimEnd().ToUpper();
*
* int unitPrice = Int32.Parse(row[4].ToString());
*
* OrderNumber = row[0].ToString().TrimEnd();
*
* //If Leaf attachment and Product code is A change product code to 'NO'
* if ((product == "LEAF") && (productCode == "A"))
* {
* productCode = "NO";
* }
*
* //Unit price needs to be greater than zero unless the product code is Notch
* //03-27-2018 All CLASS products need to be output even if the unit price is zero
* //02-26-2018 if ((unitPrice > 0 && (product == "LEAF" && productCode == "NO")) && (product != "EXTRA"))
* if ((unitPrice > 0 && (product != "EXTRA")) || (product == "CLASS"))
* {
* //If bill by Department need to check that we are only counting the department for the current billing record
* if ((billByDept == false) || ((billByDept == true) && (queryDepartment == Department)))
* {
* DetailBillingRecord detailBillingRecord = new DetailBillingRecord();
*
* detailBillingRecord.RecordType = "DTL";
* detailBillingRecord.OrderNumber = OrderNumber;
* CustomerNumber = row[1].ToString().TrimEnd();
* if (billByDept == true)
* {
* CustomerNumber = SubAccount.TrimEnd();
* detailBillingRecord.CustomerNumber = CustomerNumber;
* }
* else
* detailBillingRecord.CustomerNumber = CustomerNumber;
*
* detailBillingRecord.ProductCode = productCode;
* int copies = Int32.Parse(row[5].ToString());
*
* quantity = Int32.Parse(row[3].ToString()) * copies * minutes;
*
* //Only Add the total quantity for the CLASS product - Total Quantity is used for the HDR Record
* if (product == "CLASS")
* TotalJobQuantity += quantity;
*
* detailBillingRecord.Quantity = quantity;
* detailBillingRecord.UnitPrice = unitPrice;
* detailBillingRecord.JobQuantity = "000000";
* detailBillingRecord.Filler = " ";
*
* detailBillingRecordList.Add(detailBillingRecord);
* logger.Info("Product: " + product + " ProductCode: " + productCode + " Quantity: " + quantity.ToString() + " OrderNumber: " + OrderNumber + " CustomerNumber: " + CustomerNumber);
* }
* }
* else
* logger.Info("Unit Price " + unitPrice.ToString() +" for Product: " + product + " ProductCode: " + productCode + " Quantity: " + quantity.ToString() + " OrderNumber: " + OrderNumber + " CustomerNumber: " + CustomerNumber);
* }
*
* if (billByDept == false)
* BillDepartment = "TOTALS";
* else
* BillDepartment = Department;
*
* BillId = GetBillId(account, lotId, BillDepartment);
* //Get detail extras list
* AddDetailExtrasRecords(OrderNumber, CustomerNumber, BillId);
*
* //Add Binding costs
* AddBindingCostsFromBilling(OrderNumber, CustomerNumber, BillId);
*
* //Get Binder table measurements
* BinderTableRecords binderTableRecords = GetBinderTableMeasurements(binder);
*
* //Height Width and Spine Costs
* AddBillingSizeCosts(OrderNumber, CustomerNumber, BillId, binderTableRecords);
* *****************/
}
