}//parseExMsg
//------------------------------------------------------------------------------------------------------------------//
/// <summary>
/// Make sure all objects are released for garbage collection.
/// </summary>
public void DestroyAllObjects()
{
if (invoiceRecord != null)
{
Marshal.FinalReleaseComObject(invoiceRecord);
invoiceRecord = null;
}//If
if (invoiceItem != null)
{
Marshal.FinalReleaseComObject(invoiceItem);
invoiceItem = null;
}//If
if (ws != null)
{
ws.Disconnect();
Marshal.FinalReleaseComObject(ws);
ws = null;
}//If
if (sdo != null)
{
Marshal.FinalReleaseComObject(sdo);
sdo = null;
}//If
}//DestroyAllObjects
public static InvoiceRecord GetModelBySql(string strWhere)
{
InvoiceRecord model = null;
string sql = " select * from InvoiceRecord ";
if (strWhere != "")
{
sql += " where " + strWhere;
}
DataTable dt = DBHelper.GetDataSet(sql);
if (dt.Rows.Count > 0)
{
model = new InvoiceRecord();
DataRow dr = dt.Rows[0];
model.ID = Convert.ToInt32(dr["Id"]);
model.recordDate = Convert.ToDateTime(dr["recordDate"]);
model.makeDate = Convert.ToDateTime(dr["makeDate"]);
model.makeMan = dr["makeMan"].ToString();
model.makeId = Convert.ToInt32(dr["makeId"]);
model.makeRemark = dr["makeRemark"].ToString();
model.amount = Convert.ToDouble(dr["amount"]);
model.cusId = Convert.ToInt32(dr["cusId"]);
model.cusName = dr["cusName"].ToString();
}
return(model);
}
public bool checkIfExistInvoice(InvoiceRecord ivRec)
{
SQL = "select count(invoice_no) from invoice where elt_account_number = " + elt_account_number + " and invoice_no=" + ivRec.Invoice_no;
Cmd = new SqlCommand(SQL, Con);
int rowCount = 0;
try
{
Con.Open();
rowCount = Int32.Parse(Cmd.ExecuteScalar().ToString());
}
catch (Exception ex)
{
//10
throw ex;
}
finally
{
Con.Close();
}
if (rowCount > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Creates a reversal for a given document AND set the "isreversed" flag to "true"
/// for this document
/// </summary>
/// <param name="invoice">The invoice that has to be reverted</param>
public async Task <InvoiceRecord> CreateReversalInvoiceAsync(InvoiceRecord invoice)
{
var queryParameters = new DynamicParameters();
queryParameters.Add("@TransactionDocumentId", invoice.TransactionDocumentId);
queryParameters.Add("@DocumentTypeId", invoice.TransactionDocumentTypeId);
queryParameters.Add("@DocumentDate", invoice.InvoiceDate);
queryParameters.Add("@DocumentReference", invoice.DocumentReference);
queryParameters.Add("@YearNumberForReversed", invoice.YearNumber);
queryParameters.Add(DataVersionIdParameter, null);
queryParameters.Add("@CompanyId", invoice.CompanyId);
queryParameters.Add("@InsertedTransactionDocumentId", 0, DbType.Int64, ParameterDirection.Output);
InvoiceRecord savedInvoice = new InvoiceRecord();
using (
var grid = await ExecuteQueryMultipleAsync(
StoredProcedureNames.CreateReversalInvoice,
queryParameters,
true))
{
savedInvoice = (await grid.ReadAsync <InvoiceRecord>()).First();
if (!grid.IsConsumed)
{
savedInvoice.Document = (await grid.ReadAsync <DocumentRecord>()).FirstOrDefault();
savedInvoice.IsPosted = (await grid.ReadAsync <bool>()).First();
}
}
savedInvoice.CompanyId = invoice.CompanyId;
return(savedInvoice);
}
public static int Add(InvoiceRecord model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into InvoiceRecord (");
strSql.Append("recordDate,makeDate,makeMan,makeId,makeRemark,amount,cusId,cusName");
strSql.Append(") values (");
strSql.Append("@recordDate,@makeDate,@makeMan,@makeId,@makeRemark,@amount,@cusId,@cusName");
strSql.Append("); select @@IDENTITY");
SqlParameter[] sp = new SqlParameter[]
{
new SqlParameter("@recordDate", model.recordDate),
new SqlParameter("@makeDate", model.makeDate),
new SqlParameter("@makeMan", model.makeMan),
new SqlParameter("@makeId", model.makeId),
new SqlParameter("@makeRemark", model.makeRemark),
new SqlParameter("@amount", model.amount),
new SqlParameter("@cusId", model.cusId),
new SqlParameter("@cusName", model.cusName)
};
object result = DBHelper.GetSingle(strSql.ToString(), sp);
return(result == null ? 0 : Convert.ToInt32(result));
}
private InvoiceSummary GetInvoiceSummary(InvoiceRecord invoiceRecord)
{
return(new InvoiceSummary
{
TotalNetValue = invoiceRecord.InvoiceSummary.TotalNetValue.Value,
TotalVatAmount = invoiceRecord.InvoiceSummary.TotalVatAmount.Value,
TotalGrossValue = invoiceRecord.InvoiceSummary.TotalGrossValue.Value,
IncomeClassification = GetIncomeClassifications(invoiceRecord.InvoiceSummary.InvoiceRecordIncomeClassification)
});
}
private InvoiceDetail[] GetInvoiceDetails(InvoiceRecord invoiceRecord)
{
var invoiceDetails = new List <InvoiceDetail>();
foreach (var invoiceDetail in invoiceRecord.InvoiceDetails)
{
invoiceDetails.Add(GetInvoiceDetail(invoiceDetail));
}
return(invoiceDetails.ToArray());
}
public IActionResult ConvertToInvoice(Guid id)
{
var orgId = getOrg();
var inv = _context.InvoiceRecords.Where(x => x.OrganisationId == orgId).OrderByDescending(x => x.DateCreated).ToList();
var quote = _context.QuoteRecords.Where(x => x.Id == id).FirstOrDefault();
var quoteList = _context.QuoteItems.Where(x => x.QuoteRecordId == id).ToList();
InvoiceRecord inR = new InvoiceRecord()
{
Id = Guid.NewGuid(),
CustomerId = quote.CustomerId,
QuoteNo = quote.QuoteNo,
OrganisationId = orgId,
SubTotal = quote.SubTotal,
Tax = quote.Tax,
Total = quote.Total
};
if (inv.Count > 0)
{
inR.InvoiceNo = inv.FirstOrDefault().InvoiceNo + 1;
}
else
{
inR.InvoiceNo = 1;
}
_context.Add(inR);
_context.SaveChanges();
InvoiceItem invoiceItem;
foreach (var item in quoteList)
{
invoiceItem = new InvoiceItem()
{
Id = Guid.NewGuid(),
InvoiceRecordId = inR.Id,
OrganisationId = orgId,
ProductServiceId = item.ProductServiceId,
Quantity = item.Quantity,
UnitCost = item.UnitCost,
TotalCost = item.TotalCost,
};
_context.Add(invoiceItem);
_context.SaveChanges();
}
StatusMessage = "Conversion successful. Check your Invoice List.";
return(RedirectToAction("Index"));
}
public static InvoiceRecord ParseLine(string line)
{
var splittedLine = Regex.Split(line, ";(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
var invoiceRecord = new InvoiceRecord(splittedLine[CEPINDEX].Trim(), Int32.Parse(splittedLine[PAGENUMINDEX].Trim().Replace("\"", string.Empty)));
invoiceRecord.Name = splittedLine[NAMEINDEX].Trim().Replace("\"", string.Empty);
invoiceRecord.Address = splittedLine[ADDRESSINDEX].Trim().Replace("\"", string.Empty);
invoiceRecord.Neighbourhood = splittedLine[HOODINDEX].Trim().Replace("\"", string.Empty);
invoiceRecord.City = splittedLine[CITYINDEX].Trim().Replace("\"", string.Empty);
invoiceRecord.State = splittedLine[STATEINDEX].Trim().Replace("\"", string.Empty);
invoiceRecord.Amount = decimal.Parse(splittedLine[AMOUNTINDEX].Trim().Replace("\"", string.Empty));
return(invoiceRecord);
}
public IActionResult AddInvoice(Guid?id)
{
var orgId = getOrg();
var inv = _context.InvoiceRecords.Where(x => x.OrganisationId == orgId).OrderByDescending(x => x.DateCreated).ToList();
if (id == null)
{
InvoiceRecord ir = new InvoiceRecord()
{
Id = Guid.NewGuid(),
OrganisationId = orgId
};
if (inv.Count > 0)
{
ir.InvoiceNo = inv.FirstOrDefault().InvoiceNo + 1;
}
else
{
ir.InvoiceNo = 1;
}
_context.Add(ir);
_context.SaveChanges();
id = ir.Id;
return(RedirectToAction("AddInvoice", new { id = ir.Id }));
}
var cInvoiceRecord = inv.Where(x => x.Id == id).FirstOrDefault();
InvoiceViewModel iVM = new InvoiceViewModel();
iVM.Id = cInvoiceRecord.Id;
iVM.InvoiceNo = cInvoiceRecord.InvoiceNo;
iVM.InoviceTitle = cInvoiceRecord.InvoiceTitle;
iVM.Customer = _context.Customers.Where(x => x.Id == cInvoiceRecord.CustomerId).FirstOrDefault();
iVM.Organisation = _context.Organisations.Where(x => x.Id == orgId).FirstOrDefault();
iVM.Tax = cInvoiceRecord.Tax;
iVM.SubTotal = cInvoiceRecord.SubTotal;
iVM.Total = cInvoiceRecord.Total;
iVM.InvoiceItems = _context.InvoiceItems.Where(x => x.InvoiceRecordId == id).Include(x => x.ProductService).ToList();
iVM.DateCreated = cInvoiceRecord.DateCreated;
iVM.InvoiceOtherItems = _context.InvoiceOtherItems.Where(x => x.InvoiceRecordId == id).Include(x => x.OtherItem).ToList();
return(View(iVM));
}
public bool updateInvoiceRecordFromReceivePayment(ref InvoiceRecord ivRec)
{
bool return_val = false;
try
{
if (checkIfExistInvoice(ivRec))
{
SQL = "update invoice set ";
SQL += "amount_paid= '" + ivRec.amount_paid + "' ,";
SQL += "balance= '" + ivRec.balance + "' ,";
SQL += "deposit_to= '" + ivRec.deposit_to + "' ,";
SQL += "lock_ar= 'Y' ,";
if (ivRec.balance == 0)
{
SQL += "pay_status= 'P' ,";
}
else
{
SQL += "pay_status= 'A' ,";
}
SQL += "pmt_method= '" + ivRec.pmt_method + "'";
SQL += " WHERE elt_account_number = " + elt_account_number + " and invoice_no=" + ivRec.Invoice_no;
Cmd = new SqlCommand(SQL, Con);
try
{
Con.Open();
Cmd.ExecuteNonQuery();
updateNextInvoiceNo(ivRec.Invoice_no);
return_val = true;
}
catch (Exception ex)
{
//12
throw ex;
}
finally
{
Con.Close();
}
}
}
catch (Exception ex)
{
//13
throw ex;
}
return(return_val);
}
public async Task <InvoiceRecord> CreateDraftReversalInvoice(InvoiceRecord invoice)
{
var queryParameters = new DynamicParameters();
queryParameters.Add("@TransactionDocumentId", invoice.TransactionDocumentId);
queryParameters.Add("@DocumentTypeId", invoice.TransactionDocumentTypeId);
queryParameters.Add("@DocumentDate", invoice.InvoiceDate);
queryParameters.Add("@DocumentReference", invoice.DocumentReference);
queryParameters.Add("@YearNumberForReversed", invoice.YearNumber);
queryParameters.Add(DataVersionIdParameter, null);
queryParameters.Add("@CompanyId", invoice.CompanyId);
var draftInvoice = await ExecuteQueryAsync <InvoiceRecord>(StoredProcedureNames.CreateDraftReversalInvoice, queryParameters, true);
return(draftInvoice.FirstOrDefault());
}
private Invoice GetInvoice(InvoiceRecord invoiceRecord)
{
return(new Invoice
{
InvoiceMarkSpecified = invoiceRecord.InvoiceRegistrationNumber.IsDefined(),
InvoiceMark = invoiceRecord.InvoiceRegistrationNumber.GetOrDefault(),
InvoiceCancellationMarkSpecified = invoiceRecord.CancelledByInvoiceRegistrationNumber.IsDefined(),
InvoiceCancellationMark = invoiceRecord.CancelledByInvoiceRegistrationNumber.GetOrDefault(),
InvoiceId = invoiceRecord.InvoiceIdentifier.GetOrDefault(),
InvoiceIssuer = GetInvoiceParty(invoiceRecord.Issuer),
InvoiceCounterpart = GetInvoiceParty(invoiceRecord.Counterpart),
PaymentMethods = GetInvoicePaymentMethods(invoiceRecord.PaymentMethods),
InvoiceSummary = GetInvoiceSummary(invoiceRecord),
InvoiceDetails = GetInvoiceDetails(invoiceRecord),
InvoiceHeader = GetInvoiceHeader(invoiceRecord)
});
}
private InvoiceHeader GetInvoiceHeader(InvoiceRecord invoiceRecord)
{
var invoiceHeader = new InvoiceHeader
{
InvoiceType = invoiceRecord.InvoiceHeader.BillType.ConvertToEnum <InvoiceType>(),
IssueDate = invoiceRecord.InvoiceHeader.InvoiceIssueDate,
SerialNumber = invoiceRecord.InvoiceHeader.InvoiceSerialNumber.Value,
Series = invoiceRecord.InvoiceHeader.InvoiceSeries.Value,
CurrencySpecified = invoiceRecord.InvoiceHeader.CurrencyCode.IsDefined(),
ExchangeRateSpecified = invoiceRecord.InvoiceHeader.ExchangeRate.IsDefined(),
ExchangeRate = invoiceRecord.InvoiceHeader.ExchangeRate.GetOrDefault()
};
if (invoiceRecord.InvoiceHeader.CurrencyCode.IsDefined())
{
invoiceHeader.Currency = (Currency)Enum.Parse(typeof(Currency), invoiceRecord.InvoiceHeader.CurrencyCode.Value, true);
}
return(invoiceHeader);
}
public static bool Update(InvoiceRecord model)
{
string sql = "update InvoiceRecord set recordDate=@recordDate,makeDate=@makeDate,makeMan=@makeMan,makeId=@makeId,makeRemark=@makeRemark,amount=@amount,cusId=@cusId,cusName=@cusName where Id=@Id";
SqlParameter[] sp = new SqlParameter[]
{
new SqlParameter("@Id", model.ID),
new SqlParameter("@recordDate", model.recordDate),
new SqlParameter("@makeDate", model.makeDate),
new SqlParameter("@makeMan", model.makeMan),
new SqlParameter("@makeId", model.makeId),
new SqlParameter("@makeRemark", model.makeRemark),
new SqlParameter("@amount", model.amount),
new SqlParameter("@cusId", model.cusId),
new SqlParameter("@cusName", model.cusName)
};
int result = DBHelper.ExecuteCommand(sql, sp);
return(result > 0);
}
public InvoiceRecord GenerateInvoice(int appointmentId)
{
InvoiceRecord invoiceRecord = new InvoiceRecord();
try
{
invoiceRecord.AppointmentId = appointmentId;
invoiceRecord.TotalCost = GetInvoiceCost(appointmentId);
invoiceRecord.InvoiceDateTime = DateTime.Now;
db.InvoiceRecord.Add(invoiceRecord);
db.SaveChanges();
}
catch (Exception e)
{
var ravenClient = new RavenClient("https://[email protected]/1820886");
ravenClient.Capture(new SentryEvent(e));
return(null);
}
return(invoiceRecord);
}
/// <summary>
/// 新增一条记录并返回新增的id
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Add(InvoiceRecord model)
{
return(InvoiceRecordService.Add(model));
}
public async Task CreateInvoiceAsync(InvoiceRecord invoice, IEnumerable <InvoiceLineRecord> invoiceLines)
{
var queryParameters = new DynamicParameters();
queryParameters.Add("@R_ExternalInvoice", invoice.ExternalInvoiceRef);
queryParameters.Add("@TotalGoodsValue", invoice.TotalGoodsValue);
queryParameters.Add("@TotalInvoiceValue", invoice.TotalInvoiceValue);
queryParameters.Add("@InvoiceDate", invoice.InvoiceDate);
queryParameters.Add("@DueDate", invoice.DueDate);
queryParameters.Add("@M_Currency", invoice.Currency);
queryParameters.Add("@E_InvoiceType", (int)invoice.InvoiceType);
queryParameters.Add("@CompanyId", invoice.CompanyId);
queryParameters.Add("@M_CounterParty", invoice.CounterpartyCode);
queryParameters.Add("@M_PaymentTerms", invoice.PaymentTerms);
queryParameters.Add("@E_QuantityToInvoiceType", invoice.QuantityToInvoiceType);
queryParameters.Add("@ExternalInhouse", (int)invoice.ExternalInhouse);
queryParameters.Add("@TotalInvoiceQuantity", invoice.TotalGoodsValue);
queryParameters.Add("@Template", invoice.Template);
queryParameters.Add("@InvoiceLines", ToArrayTVP(invoiceLines));
queryParameters.Add("@PhysicalDocumentId", null);
queryParameters.Add("@DocumentReference", invoice.DocumentReference);
queryParameters.Add("@YearNumber", invoice.YearNumber);
queryParameters.Add("@Year", invoice.InvoiceDate.Year);
queryParameters.Add("@TransactionDocumentTypeId", invoice.TransactionDocumentTypeId);
queryParameters.Add("@AuthorizedForPosting", invoice.AuthorizedForPosting);
queryParameters.Add("@InvoicedocumentTypeId", (int)invoice.InvoiceDocumentType);
queryParameters.Add("@BankAccountId", invoice.BankAccountId);
queryParameters.Add("@AgreementDate", invoice.AgreementDate);
queryParameters.Add("@TransactionDocumentId", 0, DbType.Int64, ParameterDirection.Output);
queryParameters.Add("@PricingOptionId", invoice.PricingOptionId);
queryParameters.Add(DataVersionIdParameter, null);
InvoiceRecord savedInvoice = new InvoiceRecord();
using (
var grid = await ExecuteQueryMultipleAsync(
StoredProcedureNames.CreateInvoice,
queryParameters,
true))
{
savedInvoice = (await grid.ReadAsync <InvoiceRecord>()).First();
savedInvoice.Document = (await grid.ReadAsync <DocumentRecord>()).First();
}
savedInvoice.InvoiceLabel = invoice.DocumentReference;
invoice.CostAlternativeCode = savedInvoice.CostAlternativeCode;
invoice.DepartmentAlternativeCode = savedInvoice.DepartmentAlternativeCode;
invoice.C2CCode = savedInvoice.C2CCode;
invoice.TaxInterfaceCode = savedInvoice.TaxInterfaceCode;
invoice.NominalAlternativeAccount = savedInvoice.NominalAlternativeAccount;
invoice.InvoiceId = savedInvoice.InvoiceId;
invoice.InvoiceLabel = savedInvoice.InvoiceLabel;
invoice.ExternalInvoiceRef = savedInvoice.ExternalInvoiceRef;
invoice.TotalGoodsValue = savedInvoice.TotalGoodsValue;
invoice.TotalInvoiceValue = savedInvoice.TotalInvoiceValue;
invoice.InvoiceDate = savedInvoice.InvoiceDate;
invoice.DueDate = savedInvoice.DueDate;
invoice.Currency = savedInvoice.Currency;
invoice.InvoiceType = savedInvoice.InvoiceType;
invoice.CounterpartyCode = savedInvoice.CounterpartyCode;
invoice.PaymentTerms = savedInvoice.PaymentTerms;
invoice.QuantityToInvoiceType = savedInvoice.QuantityToInvoiceType;
invoice.ExternalInhouse = savedInvoice.ExternalInhouse;
invoice.TotalInvoiceQuantity = savedInvoice.TotalInvoiceQuantity;
invoice.TransactionDocumentId = savedInvoice.TransactionDocumentId;
}
public static string TransformRecordToCsvLine(InvoiceRecord invoiceRecord)
{
return($"\"{invoiceRecord.Name}\";\"{invoiceRecord.CEP}\";\"{invoiceRecord.Address}\";\"{invoiceRecord.Neighbourhood}\";\"{invoiceRecord.City}\";\"{invoiceRecord.State}\";\"{invoiceRecord.Amount}\";\"{invoiceRecord.PagesNumber}\"");
}
public InvoiceRecord getInvoiceRecord(int invoice_no)
{
SQL = "select * from Invoice where elt_account_number = " + elt_account_number + " and invoice_no=" + invoice_no;
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(SQL, Con);
InvoiceRecord ivRec = new InvoiceRecord();
try
{
ad.Fill(dt);
if (dt.Rows.Count > 0)
{
GeneralUtility util = new GeneralUtility();
util.removeNull(ref dt);
ivRec.accounts_receivable = Int32.Parse(dt.Rows[0]["accounts_receivable"].ToString());
ivRec.agent_profit = Decimal.Parse(dt.Rows[0]["agent_profit"].ToString());
ivRec.air_ocean = dt.Rows[0]["air_ocean"].ToString();
ivRec.inland_type = dt.Rows[0]["inland_type"].ToString(); //added by stanley on 12/14
ivRec.amount_charged = Decimal.Parse(dt.Rows[0]["amount_charged"].ToString());
ivRec.amount_paid = Decimal.Parse(dt.Rows[0]["amount_paid"].ToString());
ivRec.Arrival_Dept = dt.Rows[0]["Arrival_Dept"].ToString();
ivRec.balance = Decimal.Parse(dt.Rows[0]["balance"].ToString());
ivRec.Carrier = dt.Rows[0]["Carrier"].ToString();
ivRec.consignee = dt.Rows[0]["consignee"].ToString();
ivRec.Customer_info = dt.Rows[0]["Customer_info"].ToString();
ivRec.Customer_Name = dt.Rows[0]["Customer_Name"].ToString();
ivRec.Customer_Number = Int32.Parse(dt.Rows[0]["Customer_Number"].ToString());
// ivRec.deposit_to = Int32.Parse((dt.Rows[0]["deposit_to"].ToString()));
ivRec.Description = dt.Rows[0]["Description"].ToString();
ivRec.dest = dt.Rows[0]["dest"].ToString();
ivRec.entry_date = dt.Rows[0]["entry_date"].ToString();
ivRec.entry_no = dt.Rows[0]["entry_no"].ToString();
ivRec.existing_credits = Decimal.Parse(dt.Rows[0]["existing_credits"].ToString());
ivRec.hawb_num = dt.Rows[0]["hawb_num"].ToString();
ivRec.import_export = dt.Rows[0]["import_export"].ToString();
ivRec.in_memo = dt.Rows[0]["in_memo"].ToString();
ivRec.invoice_date = ((DateTime)dt.Rows[0]["invoice_date"]).ToShortDateString();
ivRec.Invoice_no = Int32.Parse(dt.Rows[0]["invoice_no"].ToString());
ivRec.invoice_type = dt.Rows[0]["invoice_type"].ToString();
ivRec.is_org_merged = dt.Rows[0]["is_org_merged"].ToString();
ivRec.lock_ap = dt.Rows[0]["lock_ap"].ToString();
ivRec.lock_ar = dt.Rows[0]["lock_ar"].ToString();
ivRec.mawb_num = dt.Rows[0]["mawb_num"].ToString();
ivRec.origin = dt.Rows[0]["origin"].ToString();
ivRec.Origin_Dest = dt.Rows[0]["Origin_Dest"].ToString();
ivRec.pay_status = dt.Rows[0]["pay_status"].ToString();
ivRec.pmt_method = dt.Rows[0]["pmt_method"].ToString();
ivRec.received_amt = Decimal.Parse(dt.Rows[0]["received_amt"].ToString());
ivRec.ref_no = dt.Rows[0]["ref_no"].ToString();
ivRec.ref_no_Our = dt.Rows[0]["ref_no_Our"].ToString();
ivRec.AMS_No = dt.Rows[0]["AMS_No"].ToString(); //added by stanley on 12/13/2007
ivRec.inland_type = dt.Rows[0]["inland_type"].ToString(); //added by stanley on 12/14/2007
ivRec.remarks = dt.Rows[0]["remarks"].ToString();
ivRec.sale_tax = Decimal.Parse(dt.Rows[0]["sale_tax"].ToString());
ivRec.shipper = dt.Rows[0]["shipper"].ToString();
ivRec.subtotal = Decimal.Parse(dt.Rows[0]["subtotal"].ToString());
ivRec.term_curr = Int32.Parse(dt.Rows[0]["term_curr"].ToString());
ivRec.term30 = dt.Rows[0]["term30"].ToString();
ivRec.term60 = dt.Rows[0]["term60"].ToString();
ivRec.term90 = dt.Rows[0]["term90"].ToString();
ivRec.Total_Charge_Weight = dt.Rows[0]["Total_Charge_Weight"].ToString();
ivRec.total_cost = Decimal.Parse(dt.Rows[0]["total_cost"].ToString());
ivRec.Total_Gross_Weight = dt.Rows[0]["Total_Gross_Weight"].ToString();
ivRec.Total_Pieces = dt.Rows[0]["Total_Pieces"].ToString();
DataTable dtIVCh = IVChManager.getIVChargeItems(invoice_no);
DataTable dtIVCost = IVCostManager.getIVCostItems(invoice_no);
ArrayList dtBDetail = bdMgr.getBillDetailListForInvoice(invoice_no);
ivRec.setChargeItemListWithDataTable(dtIVCh);
ivRec.setCostItemListWithDataTable(dtIVCost);
ivRec.BillDetailList = dtBDetail;
}
}
catch (Exception ex)
{
//15
throw ex;
}
return(ivRec);
}
public bool updateInvoiceRecord(ref InvoiceRecord ivRec, string tran_type)
{
bool return_val = false;
int loopV = 0;
ivRec.replaceQuote();
int invoice_no = ivRec.Invoice_no;
ARStatusManager ARSMgr = new ARStatusManager(elt_account_number);
bool ar_lock = ARSMgr.FindIfPaymentProcessed(invoice_no);
//UPDATE IV CHARGE ITEMS IF AR_NOT PROCESSED
ArrayList AAJEntryList = ivRec.AllAccountsJournalList;
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
try
{
if (!ar_lock)
{
//1) DELETE PREV IV CHARGE ITEMS WITH INVOICE_NO
SQL = "DELETE FROM invoice_charge_item WHERE elt_account_number =";
SQL += elt_account_number + " and invoice_no="
+ invoice_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//2) INSERT IV CHARGE ITEM
ArrayList chList = ivRec.ChargeItemList;
for (int i = 0; i < chList.Count; i++)
{
IVChargeItemRecord IVChR = (IVChargeItemRecord)chList[i];
IVChR.replaceQuote();
IVChR.Invoice_no = invoice_no;
SQL = "INSERT INTO [invoice_charge_item] ";
SQL += "( elt_account_number, ";
SQL += "invoice_no,";
SQL += "item_id,";
SQL += "item_no,";
SQL += "item_desc,";
SQL += "qty,";
SQL += "charge_amount,";
SQL += "import_export,";
SQL += "mb_no,";
SQL += "hb_no,";
SQL += "iType)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + IVChR.Invoice_no;
SQL += "','" + IVChR.Item_id;
SQL += "','" + IVChR.Item_no;
SQL += "','" + IVChR.Item_desc;
SQL += "','" + IVChR.Qty;
SQL += "','" + IVChR.Charge_amount;
SQL += "','" + IVChR.Import_export;
SQL += "','" + IVChR.Mb_no;
SQL += "','" + IVChR.Hb_no;
SQL += "','" + IVChR.IType;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
}
//INSERT IV COST ITEMS
//1) DELETE UNLOCKED PREV IV COST ITEMS WITH INVOICE_NO
SQL = "DELETE FROM invoice_cost_item WHERE elt_account_number =";
SQL += elt_account_number + " and invoice_no="
+ invoice_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//2) INSERT IV_COST_ITEM
ArrayList cstList = ivRec.CostItemList;
for (int i = 0; i < cstList.Count; i++)
{
IVCostItemRecord IVCostR = (IVCostItemRecord)cstList[i];
IVCostR.replaceQuote();
SQL = "INSERT INTO [invoice_Cost_item] ";
SQL += "( elt_account_number, ";
SQL += "invoice_no,";
SQL += "item_id,";
SQL += "item_no,";
SQL += "item_desc,";
SQL += "qty,";
SQL += "ref_no,";
SQL += "vendor_no,";
SQL += "Cost_amount,";
SQL += "import_export,";
SQL += "mb_no,";
SQL += "hb_no,";
SQL += "iType)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + invoice_no;
SQL += "','" + IVCostR.Item_id;
SQL += "','" + IVCostR.Item_no;
SQL += "','" + IVCostR.Item_desc;
SQL += "','" + IVCostR.Qty;
SQL += "','" + IVCostR.Ref_no;
SQL += "','" + IVCostR.Vendor_no;
SQL += "','" + IVCostR.Cost_amount;
SQL += "','" + IVCostR.Import_export;
SQL += "','" + IVCostR.Mb_no;
SQL += "','" + IVCostR.Hb_no;
SQL += "','" + IVCostR.IType;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//DELETE BILL_DETAIL WHITH INVOICE_NO THAT DOES NOT HAVE ANY BILL_NO ASSINGED
SQL = "DELETE FROM bill_detail WHERE elt_account_number =";
SQL += elt_account_number + " and invoice_no="
+ invoice_no + " and isnull(bill_number,0) =0";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//INSERT BILL DETAILS WITH BILL_NUMBER BEING 0 AND WHITH INVOICE_NO THAT DOES NOT HAVE ANY BILL_NO ASSINGED
ArrayList bdList = ivRec.BillDetailList;
for (int i = 0; i < bdList.Count; i++)
{
BillDetailRecord bDRec = (BillDetailRecord)bdList[i];
bDRec.replaceQuote();
if (bDRec.bill_number == 0)
{
SQL = "INSERT INTO [bill_detail] ";
SQL += "( elt_account_number, ";
SQL += "item_ap,";
SQL += "item_id,";
SQL += "item_no,";
SQL += "item_amt,";
SQL += "is_manual,";
SQL += "item_expense_acct,";
SQL += "tran_date,";
SQL += "invoice_no,";
SQL += "agent_debit_no,";
SQL += "mb_no,";
SQL += "hb_no,";
SQL += "ref,";
SQL += "iType,";
SQL += "import_export,";
SQL += "vendor_number)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + bDRec.item_ap;
SQL += "','" + bDRec.item_id;
SQL += "','" + bDRec.item_no;
SQL += "','" + bDRec.item_amt;
SQL += "','" + bDRec.is_manual;
SQL += "','" + bDRec.item_expense_acct;
SQL += "','" + bDRec.tran_date;
SQL += "','" + invoice_no;
SQL += "','" + bDRec.agent_debit_no;
SQL += "','" + bDRec.mb_no;
SQL += "','" + bDRec.hb_no;
SQL += "','" + bDRec.ref_no;
SQL += "','" + bDRec.iType;
SQL += "','" + bDRec.import_export;
SQL += "','" + bDRec.vendor_number;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
}
//DELETE ALL ENTRY WITH TRAN_NO BEING INVOICE_NO AND TRAN_TYPE BEING "INV";
SQL = "DELETE FROM invoice_header WHERE elt_account_number =";
SQL += elt_account_number + " and invoice_no="
+ invoice_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//Insert IV Headers
ArrayList ihList = ivRec.InvoiceHeaders;
if (tran_type == "INV")
{
loopV = ihList.Count;
}
for (int i = 0; i < loopV; i++)
{
//ihList.Count
IVHeaderRecord ihRec = (IVHeaderRecord)ihList[i];
SQL = "INSERT INTO [invoice_header] ";
SQL += "( elt_account_number, ";
SQL += "Carrier,";
SQL += "ChargeableWeight,";
SQL += "Consignee,";
SQL += "Destination,";
SQL += "ETA,";
SQL += "ETD,";
SQL += "FILE_NO,";
SQL += "GrossWeight,";
SQL += "hawb,";
SQL += "invoice_no,";
SQL += "mawb,";
SQL += "Origin,";
SQL += "Pieces,";
SQL += "Shipper,";
SQL += "unit)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + ihRec.Carrier;
SQL += "','" + ihRec.ChargeableWeight;
SQL += "','" + ihRec.Consignee;
SQL += "','" + ihRec.Destination;
SQL += "','" + ihRec.ETA;
SQL += "','" + ihRec.ETD;
SQL += "','" + ihRec.FILE;
SQL += "','" + ihRec.GrossWeight;
SQL += "','" + ihRec.hawb;
SQL += "','" + invoice_no;
SQL += "','" + ihRec.mawb;
SQL += "','" + ihRec.Origin;
SQL += "','" + ihRec.Pieces;
SQL += "','" + ihRec.Shipper;
SQL += "','" + ihRec.unit;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//DELETE ALL ENTRY WITH TRAN_NO BEING INVOICE_NO AND TRAN_TYPE BEING "INV";
SQL = "DELETE FROM all_accounts_journal WHERE elt_account_number =";
SQL += elt_account_number + " and tran_num="
+ invoice_no + " and tran_type='" + tran_type + "'";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
int next_tran_seq_no = 0;
SQL = "select max(tran_seq_num) from all_accounts_journal where elt_account_number = "
+ elt_account_number;
Cmd.CommandText = SQL;
int current = 0;
string id_str = Cmd.ExecuteScalar().ToString();
if (id_str != "")
{
current = Int32.Parse(id_str);
}
else
{
current = 0;
}
next_tran_seq_no = current + 1;
//INSERT AAJ ENTRY WITH TRAN_TYPE BEING INV AND TRAN_NO BEING INVOICE_NO
for (int i = 0; i < AAJEntryList.Count; i++)
{
((AllAccountsJournalRecord)AAJEntryList[i]).replaceQuote();
((AllAccountsJournalRecord)AAJEntryList[i]).tran_num = invoice_no;
SQL = "INSERT INTO [all_accounts_journal] ";
SQL += "( elt_account_number, ";
SQL += "tran_num,";
SQL += "gl_account_number,";
SQL += "gl_account_name,";
SQL += "tran_seq_num,";
SQL += "air_ocean,";
//SQL += "inland_type,"; //added by stanley on 12/14
SQL += "tran_type,";
SQL += "tran_date,";
SQL += "Customer_Number,";
SQL += "Customer_Name,";
SQL += "debit_amount,";
SQL += "credit_amount,";
SQL += "balance,";
SQL += "previous_balance,";
SQL += "gl_balance,";
SQL += "gl_previous_balance)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_num;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_account_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_account_name;
SQL += "','" + next_tran_seq_no++;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).air_ocean;
//SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).inland_type;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_type;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_date;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).customer_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).customer_name;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).debit_amount;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).credit_amount;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).previous_balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_previous_balance;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
SQL = "update invoice set ";
SQL += "accounts_receivable= '" + ivRec.accounts_receivable + "', ";
SQL += "agent_profit= '" + ivRec.agent_profit + "' ,";
SQL += "air_ocean= '" + ivRec.air_ocean + "' ,";
SQL += "inland_type= '" + ivRec.inland_type + "' ,"; //added by stanley on 12/14
SQL += "amount_charged= '" + ivRec.amount_charged + "' ,";
SQL += "amount_paid= '" + ivRec.amount_paid + "' ,";
SQL += "Arrival_Dept= '" + ivRec.Arrival_Dept + "' ,";
Decimal balace = ivRec.amount_charged - ivRec.amount_paid;
SQL += "balance= '" + balace.ToString() + "' ,";
SQL += "Carrier= '" + ivRec.Carrier + "' ,";
SQL += "consignee= '" + ivRec.consignee + "' ,";
SQL += "Customer_info= '" + ivRec.Customer_info + "' ,";
SQL += "Customer_Name= '" + ivRec.Customer_Name + "' ,";
SQL += "Customer_Number= '" + ivRec.Customer_Number + "' ,";
SQL += "deposit_to= '" + ivRec.deposit_to + "' ,";
SQL += "Description= '" + ivRec.Description + "' ,";
SQL += "dest= '" + ivRec.dest + "' ,";
SQL += "entry_date= '" + ivRec.entry_date + "' ,";
SQL += "entry_no= '" + ivRec.entry_no + "' ,";
SQL += "existing_credits= '" + ivRec.existing_credits + "' ,";
SQL += "hawb_num= '" + ivRec.hawb_num + "' ,";
SQL += "import_export= '" + ivRec.import_export + "' ,";
SQL += "in_memo= '" + ivRec.in_memo + "' ,";
SQL += "invoice_date= '" + System.DateTime.Parse(ivRec.invoice_date) + "' ,";
SQL += "invoice_no= '" + ivRec.Invoice_no + "' ,";
SQL += "invoice_type= '" + ivRec.invoice_type + "' ,";
SQL += "is_org_merged= '" + ivRec.is_org_merged + "' ,";
SQL += "lock_ap= '" + ivRec.lock_ap + "' ,";
SQL += "lock_ar= '" + ivRec.lock_ar + "' ,";
SQL += "mawb_num= '" + ivRec.mawb_num + "' ,";
SQL += "origin= '" + ivRec.origin + "' ,";
SQL += "Origin_Dest= '" + ivRec.Origin_Dest + "' ,";
SQL += "pay_status= '" + ivRec.pay_status + "' ,";
SQL += "pmt_method= '" + ivRec.pmt_method + "' ,";
SQL += "received_amt= '" + ivRec.received_amt + "' ,";
SQL += "ref_no= '" + ivRec.ref_no + "' ,";
SQL += "ref_no_Our= '" + ivRec.ref_no_Our + "' ,";
SQL += "AMS_No= '" + ivRec.AMS_No + "' ,";//added by stanley on 12/13/2007
SQL += "remarks= '" + ivRec.remarks + "' ,";
SQL += "sale_tax= '" + ivRec.sale_tax + "' ,";
SQL += "shipper= '" + ivRec.shipper + "' ,";
SQL += "subtotal= '" + ivRec.subtotal + "' ,";
SQL += "term_curr= '" + ivRec.term_curr + "' ,";
SQL += "term30= '" + ivRec.term30 + "' ,";
SQL += "term60= '" + ivRec.term60 + "' ,";
SQL += "term90= '" + ivRec.term90 + "' ,";
SQL += "Total_Charge_Weight= '" + ivRec.Total_Charge_Weight + "' ,";
SQL += "total_cost= '" + ivRec.total_cost + "' ,";
SQL += "Total_Gross_Weight= '" + ivRec.Total_Gross_Weight + "' ,";
SQL += "Total_Pieces= '" + ivRec.Total_Pieces + "' ";
SQL += " WHERE elt_account_number = " + elt_account_number + " and invoice_no=" + ivRec.Invoice_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
trans.Commit();
return_val = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
Con.Close();
}
return(return_val);
}
public bool insertPaymentRecord(ref PaymentRecord pRec, string tran_type)
{
bool return_val = false;
pRec.replaceQuote();
int payment_no = getNewpaymentNumber();
ArrayList AAJEntryList = pRec.AllAccountsJournalList;
ArrayList pdList = pRec.PaymentDetailList;
ArrayList IVList = pRec.InvoiceList;
setTranNoForAllAccountsJournalEntries(pRec.AllAccountsJournalList, payment_no);
for (int i = 0; i < AAJEntryList.Count; i++)
{
AAJMgr.checkInitial_Acct_Record((AllAccountsJournalRecord)AAJEntryList[i]);
}
int tran_seq_no = AAJMgr.getNextTranSeqNumber();
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
try
{
//-------------------------UPDATE PAYMENT DETAILS-------------------------
//1) DELETE PAYMENT DETAIL LIST
SQL = "DELETE FROM customer_payment_detail WHERE elt_account_number =";
SQL += elt_account_number + " and payment_no="
+ payment_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//2) INSERT PAYMENT DETAIL LIST
for (int i = 0; i < pdList.Count; i++)
{
PaymentDetailRecord pdRec = (PaymentDetailRecord)pdList[i];
pdRec.payment_no = payment_no;
SQL = "INSERT INTO [customer_payment_detail] ";
SQL += "( elt_account_number, ";
SQL += "amt_due,";
SQL += "item_id,";
SQL += "invoice_date,";
SQL += "invoice_no,";
SQL += "orig_amt,";
SQL += "payment,";
SQL += "type,";
SQL += "payment_no)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + pdRec.amt_due;
SQL += "','" + i;
SQL += "','" + pdRec.invoice_date;
SQL += "','" + pdRec.invoice_no;
SQL += "','" + pdRec.orig_amt;
SQL += "','" + pdRec.payment;
SQL += "','" + pdRec.type;
SQL += "','" + pdRec.payment_no;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//----------------UPDATE LIST OF INVOICE RECORD
for (int i = 0; i < IVList.Count; i++)
{
InvoiceRecord ivRec = (InvoiceRecord)IVList[i];
SQL = "update invoice set ";
SQL += "amount_paid= '" + ivRec.amount_paid + "' ,";
SQL += "balance= '" + ivRec.balance + "' ,";
SQL += "deposit_to= '" + ivRec.deposit_to + "' ,";
SQL += "lock_ar= 'Y' ,";
if (ivRec.balance == 0)
{
SQL += "pay_status= 'P' ,";
}
else
{
SQL += "pay_status= 'A' ,";
}
SQL += "pmt_method= '" + ivRec.pmt_method + "'";
SQL += " WHERE elt_account_number = " + elt_account_number + " and invoice_no=" + ivRec.Invoice_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//----------UPDATE ALL ACCOUNT JOURNAL ENTRIES
//1) DELETE AAJ ENTRIES WITH THE SAME PAYMNET NO
SQL = "Delete FROM all_accounts_journal WHERE elt_account_number = "
+ elt_account_number + " AND tran_num = " + payment_no + " AND tran_type = '" + tran_type + "'";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//2)INSERT AAJ ENTRIES
for (int i = 0; i < AAJEntryList.Count; i++)
{
((AllAccountsJournalRecord)AAJEntryList[i]).replaceQuote();
((AllAccountsJournalRecord)AAJEntryList[i]).tran_num = payment_no;
SQL = "INSERT INTO [all_accounts_journal] ";
SQL += "( elt_account_number, ";
SQL += "tran_num,";
SQL += "gl_account_number,";
SQL += "gl_account_name,";
SQL += "tran_seq_num,";
SQL += "tran_type,";
SQL += "tran_date,";
SQL += "Customer_Number,";
SQL += "Customer_Name,";
SQL += "debit_amount,";
SQL += "credit_amount,";
SQL += "balance,";
SQL += "previous_balance,";
SQL += "gl_balance,";
SQL += "gl_previous_balance)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_num;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_account_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_account_name;
SQL += "','" + tran_seq_no++;
SQL += "','" + "PMT";
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_date;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).customer_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).customer_name;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).debit_amount;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).credit_amount;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).previous_balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_previous_balance;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//INSERT PAYMNET RECORD
SQL = "INSERT INTO [customer_payment] ";
SQL += "(elt_account_number,";
SQL += "payment_no,";
SQL += "accounts_receivable,";
SQL += "added_amt,";
SQL += "balance,";
SQL += "branch,";
SQL += "customer_name,";
SQL += "customer_number,";
SQL += "deposit_to,";
SQL += "existing_credits,";
SQL += "payment_date,";
SQL += "pmt_method,";
SQL += "received_amt,";
SQL += "ref_no,";
SQL += "unapplied_amt)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + payment_no;
SQL += "','" + pRec.accounts_receivable;
SQL += "','" + pRec.added_amt;
SQL += "','" + pRec.balance;
SQL += "','" + pRec.branch;
SQL += "','" + pRec.customer_name;
SQL += "','" + pRec.customer_number;
SQL += "','" + pRec.deposit_to;
SQL += "','" + pRec.existing_credits;
SQL += "','" + pRec.payment_date;
SQL += "','" + pRec.pmt_method;
SQL += "','" + pRec.received_amt;
SQL += "','" + pRec.ref_no;
SQL += "','" + pRec.unapplied_amt;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
trans.Commit();
return_val = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
Con.Close();
}
pRec.payment_no = payment_no;
return(return_val);
}
//Email Templates for Account Module,
public async Task SendGridInvoicesAsync(string emailAdd, string subject, string message, string firstname, string template, string organisation, InvoiceRecord invoiceRecord, Customer customer)
{
string domain = "http://erp4sme.azurewebsites.net";
EmailTemplateHelper EmailHelper = new EmailTemplateHelper();
var body = EmailHelper.GetTemplate(template).Replace("#FirstName", firstname).Replace("#HostDomain", domain).Replace("#Organisation", organisation).Replace("#CustomersFirstname", customer.CustomerName).Replace("#DueDate", invoiceRecord.DueDate.ToString("dd MMM, yyyy")).Replace("#Amount", invoiceRecord.Total.ToString());
//string apiKey
var client = new SendGridClient(apiKey);
var msg = new SendGridMessage()
{
From = new EmailAddress("*****@*****.**", "ERP4SME"),
Subject = "New Invoice on ERP4SME.",
//PlainTextContent = "You have been registered on ERP4SME platform. Kindly use the link below to create your account.",
HtmlContent = body
};
msg.AddTo(new EmailAddress(emailAdd, emailAdd));
await client.SendEmailAsync(msg);
//var response =
}
}//readInvoiceData
//-------------------------------------------------------------------------------------------------------//
/// <summary>
/// Get Last price of the product that this customer bought.
/// </summary>
/// <param name="customerCode">Customer Code</param>
/// <param name="productCode">Product Code</param>
/// <returns></returns>
public Sale GetLastPriceData(string customerCode, string productCode)
{
///Start date is lookBackYrs year ago.
int lookBackYrs = (int)Settings.Default.invoiceLookBackYrs;
DateTime startDate = DateTime.Now.AddYears(-lookBackYrs);
int invNum = -1;
//string cusCode = String.Empty;
double salePrice = -1;
int currCode = -1;
double xRate = -1;
DateTime invDate = new DateTime();
try
{
sdo = new SDOEngine();
//Try a connection, will throw an exception if it fails
ws = (WorkSpace)sdo.Workspaces.Add("App Server Update");
ws.Disconnect();
ws.Connect(sageUsrSet.sageDBDir, sageUsrSet.sageUsername, sageUsrSet.sagePassword, "UniqueUpdater");
//Create instances of the objects
invoiceRecord = (InvoiceRecord)ws.CreateObject("InvoiceRecord");
invoiceItem = (InvoiceItem)ws.CreateObject("InvoiceItem");
var cs = (string)SDOHelper.Read(invoiceRecord, "ACCOUNT_REF");
SDOHelper.Write(invoiceRecord, "ACCOUNT_REF", customerCode);
invoiceRecord.Find(false);
invNum = (int)SDOHelper.Read(invoiceRecord, "INVOICE_NUMBER");
//Start at last Invoice
invoiceRecord.MoveLast();
do
{
//Invoice info
invDate = (DateTime)SDOHelper.Read(invoiceRecord, "INVOICE_DATE");
cs = (string)SDOHelper.Read(invoiceRecord, "ACCOUNT_REF");
//Only read invoice details if it is recent enough.
if (invDate < startDate)
break;
invNum = (int)SDOHelper.Read(invoiceRecord, "INVOICE_NUMBER");
currCode = (sbyte)SDOHelper.Read(invoiceRecord, "CURRENCY");
xRate = (double)SDOHelper.Read(invoiceRecord, "FOREIGN_RATE");
//Link Items to Record
invoiceItem = invoiceRecord.Link;
var lastPriceResult = invoiceItem.FindFirst("STOCK_CODE", productCode);
if (lastPriceResult)
{
double netAmount = (double)SDOHelper.Read(invoiceItem, "NET_AMOUNT");
double qty = (double)SDOHelper.Read(invoiceItem, "QTY_ORDER");
salePrice = netAmount / qty;
return new Sale(invDate, salePrice, productCode);
}//If
} while (invoiceRecord.MovePrev());
}
catch (Exception e)
{
string eString = "Problem reading Invoice Data"
+ "\r\n ----------------- \r\n"
+ e.GetType() + "\r\n" + e.Message
+ "\r\n ----------------- \r\n"
+ "\r\nInvoice No.: " + invNum + ", Product: " + productCode + ", Customer : " + customerCode
+ "\r\nSale Price : " + salePrice + "\r\n";
throw new Exception(eString);
}
finally
{
DestroyAllObjects();
}//Finally
return null;
}//readInvoiceData
public bool insertInvoiceRecord(ref InvoiceRecord ivRec, string tran_type)
{
ivRec.replaceQuote();
bool return_val = false;
int loopV = 0;
int invoice_no = getNewInvoiceNo();
ArrayList AAJEntryList = ivRec.AllAccountsJournalList;
for (int i = 0; i < AAJEntryList.Count; i++)
{
this.aajMgr.checkInitial_Acct_Record((AllAccountsJournalRecord)AAJEntryList[i]);
}
int next_tran_seq_no = this.aajMgr.getNextTranSeqNumber();
//INSERT IV CHARGE ITEMS
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
//try
// {
ArrayList chList = ivRec.ChargeItemList;
for (int i = 0; i < chList.Count; i++)
{
IVChargeItemRecord IVChR = (IVChargeItemRecord)chList[i];
IVChR.replaceQuote();
IVChR.Invoice_no = invoice_no;
SQL = "INSERT INTO [invoice_charge_item] ";
SQL += "( elt_account_number, ";
SQL += "invoice_no,";
SQL += "item_id,";
SQL += "item_no,";
SQL += "item_desc,";
SQL += "qty,";
SQL += "charge_amount,";
SQL += "import_export,";
SQL += "mb_no,";
SQL += "hb_no,";
SQL += "iType)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + invoice_no;
SQL += "','" + IVChR.Item_id;
SQL += "','" + IVChR.Item_no;
SQL += "','" + IVChR.Item_desc;
SQL += "','" + IVChR.Qty;
SQL += "','" + IVChR.Charge_amount;
SQL += "','" + IVChR.Import_export;
SQL += "','" + IVChR.Mb_no;
SQL += "','" + IVChR.Hb_no;
SQL += "','" + IVChR.IType;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//INSERT IV COST ITEMS
ArrayList cstList = ivRec.CostItemList;
int bill_no = 0;
for (int i = 0; i < cstList.Count; i++)
{
IVCostItemRecord IVCostR = (IVCostItemRecord)cstList[i];
IVCostR.replaceQuote();
SQL = "INSERT INTO [invoice_Cost_item] ";
SQL += "( elt_account_number, ";
SQL += "invoice_no,";
SQL += "item_id,";
SQL += "item_no,";
SQL += "item_desc,";
SQL += "qty,";
SQL += "ref_no,";
SQL += "vendor_no,";
SQL += "Cost_amount,";
SQL += "import_export,";
SQL += "mb_no,";
SQL += "hb_no,";
SQL += "iType)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + invoice_no;
SQL += "','" + IVCostR.Item_id;
SQL += "','" + IVCostR.Item_no;
SQL += "','" + IVCostR.Item_desc;
SQL += "','" + IVCostR.Qty;
SQL += "','" + IVCostR.Ref_no;
SQL += "','" + IVCostR.Vendor_no;
SQL += "','" + IVCostR.Cost_amount;
SQL += "','" + IVCostR.Import_export;
SQL += "','" + IVCostR.Mb_no;
SQL += "','" + IVCostR.Hb_no;
SQL += "','" + IVCostR.IType;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//INSERT BILL DETAILS WITH BILL_NUMBER BEING 0
ArrayList bdList = ivRec.BillDetailList;
for (int i = 0; i < bdList.Count; i++)
{
BillDetailRecord bDRec = (BillDetailRecord)bdList[i];
bDRec.replaceQuote();
SQL = "INSERT INTO [bill_detail] ";
SQL += "( elt_account_number, ";
SQL += "item_ap,";
SQL += "item_id,";
SQL += "item_no,";
SQL += "item_amt,";
SQL += "is_manual,";
SQL += "item_expense_acct,";
SQL += "tran_date,";
SQL += "invoice_no,";
SQL += "agent_debit_no,";
SQL += "mb_no,";
SQL += "hb_no,";
SQL += "ref,";
SQL += "iType,";
SQL += "import_export,";
SQL += "bill_number,";
SQL += "vendor_number)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + bDRec.item_ap;
SQL += "','" + bDRec.item_id;
SQL += "','" + bDRec.item_no;
SQL += "','" + bDRec.item_amt;
SQL += "','" + bDRec.is_manual;
SQL += "','" + bDRec.item_expense_acct;
SQL += "','" + bDRec.tran_date;
SQL += "','" + invoice_no;
SQL += "','" + bDRec.agent_debit_no;
SQL += "','" + bDRec.mb_no;
SQL += "','" + bDRec.hb_no;
SQL += "','" + bDRec.ref_no;
SQL += "','" + bDRec.iType;
SQL += "','" + bDRec.import_export;
SQL += "','" + bDRec.bill_number;
SQL += "','" + bDRec.vendor_number;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
SQL = "DELETE FROM invoice_header WHERE elt_account_number =";
SQL += elt_account_number + " and invoice_no="
+ invoice_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//Insert IV Headers
//INSERT BILL DETAILS WITH BILL_NUMBER BEING 0
ArrayList ihList = ivRec.InvoiceHeaders;
if (tran_type == "INV")
{
loopV = ihList.Count;
}
for (int i = 0; i < loopV; i++)
{
//ihList.Count
IVHeaderRecord ihRec = (IVHeaderRecord)ihList[i];
SQL = "INSERT INTO [invoice_header] ";
SQL += "( elt_account_number, ";
SQL += "Carrier,";
SQL += "ChargeableWeight,";
SQL += "Consignee,";
SQL += "Destination,";
SQL += "ETA,";
SQL += "ETD,";
SQL += "FILE_NO,";
SQL += "GrossWeight,";
SQL += "hawb,";
SQL += "invoice_no,";
SQL += "mawb,";
SQL += "Origin,";
SQL += "Pieces,";
SQL += "Shipper,";
SQL += "unit)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + ihRec.Carrier;
SQL += "','" + ihRec.ChargeableWeight;
SQL += "','" + ihRec.Consignee;
SQL += "','" + ihRec.Destination;
SQL += "','" + ihRec.ETA;
SQL += "','" + ihRec.ETD;
SQL += "','" + ihRec.FILE;
SQL += "','" + ihRec.GrossWeight;
SQL += "','" + ihRec.hawb;
SQL += "','" + invoice_no;
SQL += "','" + ihRec.mawb;
SQL += "','" + ihRec.Origin;
SQL += "','" + ihRec.Pieces;
SQL += "','" + ihRec.Shipper;
SQL += "','" + ihRec.unit;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//INSERT AAJ ENTRY WITH TRAN_TYPE BEING INV AND TRAN_NO BEING INVOICE_NO
for (int i = 0; i < AAJEntryList.Count; i++)
{
((AllAccountsJournalRecord)AAJEntryList[i]).replaceQuote();
((AllAccountsJournalRecord)AAJEntryList[i]).tran_num = invoice_no;
SQL = "INSERT INTO [all_accounts_journal] ";
SQL += "( elt_account_number, ";
SQL += "tran_num,";
SQL += "air_ocean,";
//SQL += "inland_type,"; //added by stanley on 12/14
SQL += "gl_account_number,";
SQL += "gl_account_name,";
SQL += "tran_seq_num,";
SQL += "tran_type,";
SQL += "tran_date,";
SQL += "Customer_Number,";
SQL += "Customer_Name,";
SQL += "debit_amount,";
SQL += "credit_amount,";
SQL += "balance,";
SQL += "previous_balance,";
SQL += "gl_balance,";
SQL += "gl_previous_balance)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_num;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).air_ocean;
//SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).inland_type; //added by stanley on 12/14
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_account_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_account_name;
SQL += "','" + next_tran_seq_no++;
SQL += "','" + tran_type;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_date;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).customer_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).customer_name;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).debit_amount;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).credit_amount;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).previous_balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_previous_balance;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//INSERT INVOICE RECORD
SQL = "INSERT INTO [invoice] ";
SQL += "( elt_account_number, ";
SQL += "accounts_receivable,";
SQL += "agent_profit, ";
SQL += "air_ocean, ";
SQL += "inland_type, "; //added by stanley on 12/14
SQL += "amount_charged,";
SQL += "amount_paid, ";
SQL += "Arrival_Dept, ";
SQL += "balance, ";
SQL += "Carrier, ";
SQL += "consignee, ";
SQL += "Customer_info, ";
SQL += "Customer_Name, ";
SQL += "Customer_Number, ";
SQL += "deposit_to, ";
SQL += "Description, ";
SQL += "dest,";
if (ivRec.entry_date != null)
{
SQL += "entry_date, ";
}
SQL += "entry_no, ";
SQL += "existing_credits, ";
SQL += "hawb_num, ";
SQL += "import_export, ";
SQL += "in_memo, ";
SQL += "invoice_date, ";
SQL += "invoice_no, ";
SQL += "invoice_type, ";
SQL += "is_org_merged, ";
SQL += "lock_ap, ";
SQL += "lock_ar, ";
SQL += "mawb_num, ";
SQL += "origin, ";
SQL += "Origin_Dest, ";
SQL += "pay_status, ";
SQL += "pmt_method, ";
SQL += "received_amt, ";
SQL += "ref_no, ";
SQL += "ref_no_Our, ";
SQL += "AMS_No, ";//added by stanley on 12/13/2007
SQL += "remarks, ";
SQL += "sale_tax, ";
SQL += "shipper, ";
SQL += "subtotal, ";
SQL += "term_curr, ";
SQL += "term30, ";
SQL += "term60, ";
SQL += "term90, ";
SQL += "Total_Charge_Weight,";
SQL += "total_cost,";
SQL += "Total_Gross_Weight,";
SQL += "Total_Pieces ) ";
SQL += "VALUES";
SQL += "('" + base.elt_account_number;
SQL += "','" + ivRec.accounts_receivable;
SQL += "','" + ivRec.agent_profit;
SQL += "','" + ivRec.air_ocean;
SQL += "','" + ivRec.inland_type; //added by stanley on 12/14
SQL += "','" + ivRec.amount_charged;
SQL += "','" + ivRec.amount_paid;
SQL += "','" + ivRec.Arrival_Dept;
SQL += "','" + ivRec.balance;
SQL += "','" + ivRec.Carrier;
SQL += "','" + ivRec.consignee;
SQL += "','" + ivRec.Customer_info;
SQL += "','" + ivRec.Customer_Name;
SQL += "','" + ivRec.Customer_Number;
SQL += "','" + ivRec.deposit_to;
SQL += "','" + ivRec.Description;
SQL += "','" + ivRec.dest;
if (ivRec.entry_date != null)
{
SQL += "','" + ivRec.entry_date;
}
SQL += "','" + ivRec.entry_no;
SQL += "','" + ivRec.existing_credits;
SQL += "','" + ivRec.hawb_num;
SQL += "','" + ivRec.import_export;
SQL += "','" + ivRec.in_memo;
SQL += "','" + ivRec.invoice_date;
SQL += "','" + invoice_no;
SQL += "','" + "I";
SQL += "','" + ivRec.is_org_merged;
SQL += "','" + ivRec.lock_ap;
SQL += "','" + ivRec.lock_ar;
SQL += "','" + ivRec.mawb_num;
SQL += "','" + ivRec.origin;
SQL += "','" + ivRec.Origin_Dest;
SQL += "','" + ivRec.pay_status;
SQL += "','" + ivRec.pmt_method;
SQL += "','" + ivRec.received_amt;
SQL += "','" + ivRec.ref_no; // REF NO POINTS TO INVOICE!
SQL += "','" + ivRec.ref_no_Our;
SQL += "','" + ivRec.AMS_No; //add by stanley on 12/13/2007
SQL += "','" + ivRec.remarks;
SQL += "','" + ivRec.sale_tax;
SQL += "','" + ivRec.shipper;
SQL += "','" + ivRec.subtotal;
SQL += "','" + ivRec.term_curr;
SQL += "','" + ivRec.term30;
SQL += "','" + ivRec.term60;
SQL += "','" + ivRec.term90;
SQL += "','" + ivRec.Total_Charge_Weight;
SQL += "','" + ivRec.total_cost;
SQL += "','" + ivRec.Total_Gross_Weight;
SQL += "','" + ivRec.Total_Pieces;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
trans.Commit();
//UPDATE NEXT INVOICE NUMBER
ivRec.Invoice_no = invoice_no;
updateNextInvoiceNo(invoice_no);
return_val = true;
// }
// catch (Exception ex)
//{
// trans.Rollback();
//throw ex;
//}
//finally
//{
// Con.Close();
//}
return(return_val);
}
/// <summary>
/// 更新一条记录
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool Update(InvoiceRecord model)
{
return(InvoiceRecordService.Update(model));
}
public bool CancelPayment(PaymentRecord pRec, string tran_type, string is_credit_back)
{
Decimal totalPayment = 0;
bool return_val = false;
int payment_no = pRec.payment_no;
ArrayList pdList = pdMgr.getPaymentDetailList(payment_no);
//Get invoice list from payment detail
ArrayList IRecList = new ArrayList();
for (int i = 0; i < pdList.Count; i++)
{
int invoice_no = ((PaymentDetailRecord)pdList[i]).invoice_no;
InvoiceRecord IRec = ivMgr.getInvoiceRecord(invoice_no);
IRec.amount_paid -= ((PaymentDetailRecord)pdList[i]).payment;
totalPayment += ((PaymentDetailRecord)pdList[i]).payment;
IRec.balance += ((PaymentDetailRecord)pdList[i]).payment;
if (IRec.amount_charged == IRec.balance)
{
IRec.lock_ar = "N";
}
//ivRec.pmt_method meaningless
IRec.pay_status = "A";
IRecList.Add(IRec);
}
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
try
{
//UPDATE INVOICE LIST
for (int i = 0; i < IRecList.Count; i++)
{
InvoiceRecord ivRec = (InvoiceRecord)IRecList[i];
SQL = "update invoice set ";
SQL += "amount_paid= '" + ivRec.amount_paid + "' ,";
SQL += "balance= '" + ivRec.balance + "' ,";
SQL += "deposit_to= '" + ivRec.deposit_to + "' ,";
if (ivRec.amount_paid == 0)//WHEN NOTHING PAID
{
SQL += "lock_ar= 'N' ,";
SQL += "pay_status= 'A' ,";
}
else if (ivRec.amount_paid == ivRec.amount_charged)//WHEN EVERYTHING'S PAID
{
SQL += "lock_ar= 'Y' ,";
SQL += "pay_status= 'p' ,";
}
else//WHEN PARTIALLY PAID
{
SQL += "lock_ar= 'Y' ,";
SQL += "pay_status= 'A' ,";
}
SQL += "pmt_method= '" + ivRec.pmt_method + "'";
SQL += " WHERE elt_account_number = " + elt_account_number + " and invoice_no=" + ivRec.Invoice_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//DELETE PAYMENT DETAIL LIST
SQL = "DELETE FROM customer_payment_detail WHERE elt_account_number =";
SQL += elt_account_number + " and payment_no="
+ payment_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//DELETE AAJ ENTRIES
SQL = "Delete FROM all_accounts_journal WHERE elt_account_number = "
+ elt_account_number + " AND tran_num = " + payment_no + " AND tran_type = '" + tran_type + "'";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//DELETE PAYMENT
SQL = "delete from customer_payment where elt_account_number = " + elt_account_number + " and payment_no=" + payment_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
if (is_credit_back == "Y")
{
SQL = "INSERT INTO [customer_credit_info] ";
SQL += "(elt_account_number, ";
SQL += "customer_no,";
SQL += "tran_date,";
SQL += "memo,";
SQL += "credit)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + pRec.customer_number;
SQL += "','" + DateTime.Today.ToShortDateString();
SQL += "','" + "Refund From Customer Payment";
SQL += "','" + totalPayment;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
Con.Close();
}
return(return_val);
}
}//CTOR
//-------------------------------------------------------------------------------------------------------//
/// <summary>
/// Reads all invoices and stores it's values in a List.
/// </summary>
/// <param name="customersFileName"></param>
public MyDictionary<MyDictionary<List<Sale>>> ReadInvoiceData()
{
MyDictionary<MyDictionary<List<Sale>>> customerActivity = new MyDictionary<MyDictionary<List<Sale>>>(StringComparer.InvariantCultureIgnoreCase);
///Start date is lookBackYrs year ago.
int lookBackYrs = (int)Settings.Default.invoiceLookBackYrs;
DateTime startDate = DateTime.Now.AddYears(-lookBackYrs);
int invNum = -1;
string cusCode = String.Empty;
string stockCode = String.Empty;
double salePrice = -1;
int currCode = -1;
double xRate = -1;
DateTime invDate = new DateTime();
MyDictionary<List<Sale>> productActivity;
try
{
sdo = new SDOEngine();
//Try a connection, will throw an exception if it fails
ws = (WorkSpace)sdo.Workspaces.Add("App Server Update");
ws.Connect(sageUsrSet.sageDBDir, sageUsrSet.sageUsername, sageUsrSet.sagePassword, "UniqueUpdater");
//Create instances of the objects
invoiceRecord = (InvoiceRecord)ws.CreateObject("InvoiceRecord");
invoiceItem = (InvoiceItem)ws.CreateObject("InvoiceItem");
//Start at last Invoice
invoiceRecord.MoveLast();
do
{
//Invoice info
invDate = (DateTime)SDOHelper.Read(invoiceRecord, "INVOICE_DATE");
//Only read invoice details if it is recent enough.
if (invDate >= startDate)
{
invNum = (Int32)SDOHelper.Read(invoiceRecord, "INVOICE_NUMBER");
cusCode = (String)SDOHelper.Read(invoiceRecord, "ACCOUNT_REF");
currCode = (SByte)SDOHelper.Read(invoiceRecord, "CURRENCY");
xRate = (Double)SDOHelper.Read(invoiceRecord, "FOREIGN_RATE");
//Check for cusCode entry in customerActivity.
if (!customerActivity.ContainsKey(cusCode))
{
productActivity = new MyDictionary<List<Sale>>();
customerActivity[cusCode] = productActivity;
}
else
{
productActivity = customerActivity[cusCode];
}//Else
//Link Items to Record
invoiceItem = invoiceRecord.Link;
//Invoice Item info.
invoiceItem.MoveFirst();
do
{
stockCode = (String)SDOHelper.Read(invoiceItem, "STOCK_CODE");
double netAmount = (Double)SDOHelper.Read(invoiceItem, "NET_AMOUNT");
double qty = (Double)SDOHelper.Read(invoiceItem, "QTY_ORDER");
//if (currCode != baseCurrCode)
//{
// salePrice = (netAmount * xRate) / qty;
//}
//else
//{
// salePrice = netAmount / qty;
//}//Else
salePrice = netAmount / qty;
//If Customer or Product were not on lists then skip row.
if (!productMap.ContainsKey(stockCode) || !customerMap.ContainsKey(cusCode))
continue;
List<Sale> salesList;
//Check for stockCode entry in productActivity.
if (!productActivity.ContainsKey(stockCode))
{
salesList = new List<Sale>();
productActivity[stockCode] = salesList;
}
else
{
salesList = productActivity[stockCode];
}//Else
//Retrieve sale and add it to salesList.
Sale sale = new Sale(invDate, salePrice, stockCode);
salesList.Add(sale);
salesList.Sort();
} while (invoiceItem.MoveNext());
}//If date
} while (invoiceRecord.MovePrev());
}
catch (Exception e)
{
string eString = "Problem reading Invoice Data"
+ "\r\n ----------------- \r\n"
+ e.GetType() + "\r\n" + e.Message
+ "\r\n ----------------- \r\n"
+ "\r\nInvoice No.: " + invNum + ", Product: " + stockCode + ", Customer : " + cusCode
+ "\r\nSale Price : " + salePrice + "\r\n";
throw new Exception(eString);
}
finally
{
DestroyAllObjects();
}//Finally
return customerActivity;
}//readInvoiceData
