}//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