internal void save(InvoiceObj obj, bool insert) { string txtQuery = string.Empty; if (insert) { // insert txtQuery = string.Format("INSERT INTO {0} (invoice_id,invoice_name,purpose,purpose_code,issue_date,seller_id,buyer_id,tax_code,tax_rate,basis_amount,line_total" + ",original,difference,discount,discount_rate,tax_total,grand_total,remark,service_charge,service_charge_rate) VALUES ", this.tableName); string values = string.Format("(@invoice_id,@invoice_name,@purpose,@purpose_code,@issue_date,@seller_id,@buyer_id,@tax_code,@tax_rate,@basis_amount,@line_total" + ",@original,@difference,@discount,@discount_rate,@tax_total,@grand_total,@remark,@service_charge,@service_charge_rate)"); txtQuery = txtQuery + values; } else { //update txtQuery = string.Format("UPDATE {0} SET ", this.tableName); string values = string.Format("invoice_name=@invoice_name,purpose=@purpose,purpose_code=@purpose_code,issue_date=@issue_date,seller_id=@seller_id" + ",buyer_id=@buyer_id,tax_code=@tax_code,tax_rate=@tax_rate,basis_amount=@basis_amount,line_total=@line_total" + ",original=@original,difference=@difference,discount=@discount,discount_rate=@discount_rate,tax_total=@tax_total,grand_total=@grand_total,remark=@remark" + ",service_charge=@service_charge,service_charge_rate=@service_charge_rate "); string condition = string.Format("WHERE invoice_id=@invoice_id"); txtQuery = txtQuery + values + condition; } using (SQLiteConnection c = new SQLiteConnection(sqlite.ConnectionString)) { c.Open(); using (SQLiteCommand cmd = new SQLiteCommand(txtQuery, c)) { cmd.Parameters.AddWithValue("@invoice_id", obj.invoiceId); cmd.Parameters.AddWithValue("@invoice_name", obj.invoiceName); cmd.Parameters.AddWithValue("@purpose", obj.purpose); cmd.Parameters.AddWithValue("@purpose_code", obj.purposeCode); cmd.Parameters.AddWithValue("@issue_date", obj.issueDate); cmd.Parameters.AddWithValue("@seller_id", obj.sellerId); cmd.Parameters.AddWithValue("@buyer_id", obj.buyerId); cmd.Parameters.AddWithValue("@tax_code", obj.taxCode); cmd.Parameters.AddWithValue("@tax_rate", obj.taxRate); cmd.Parameters.AddWithValue("@basis_amount", obj.basisAmount); cmd.Parameters.AddWithValue("@line_total", obj.lineTotal); cmd.Parameters.AddWithValue("@original", obj.original); cmd.Parameters.AddWithValue("@difference", obj.difference); cmd.Parameters.AddWithValue("@discount", obj.discount); cmd.Parameters.AddWithValue("@discount_rate", obj.discount_rate); cmd.Parameters.AddWithValue("@tax_total", obj.taxTotal); cmd.Parameters.AddWithValue("@grand_total", obj.grandTotal); cmd.Parameters.AddWithValue("@remark", obj.remark); cmd.Parameters.AddWithValue("@service_charge", obj.service_charge); cmd.Parameters.AddWithValue("@service_charge_rate", obj.service_charge_rate); try { cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } } } }
internal InvoiceObj find(string invoice_id) { string txtQuery = string.Format("SELECT * FROM {0} WHERE invoice_id = @invoice_id LIMIT 1", this.tableName); try { InvoiceObj data = new InvoiceObj(); using (SQLiteConnection c = new SQLiteConnection(sqlite.ConnectionString)) { c.Open(); using (SQLiteCommand cmd = new SQLiteCommand(txtQuery, c)) { cmd.Parameters.AddWithValue("@invoice_id", invoice_id); using (SQLiteDataReader dr = cmd.ExecuteReader()) { if (dr.Read()) { data.invoiceId = dr["invoice_id"].ToString(); data.invoiceName = dr["invoice_name"].ToString(); data.purpose = dr["purpose"].ToString(); data.purposeCode = dr["purpose_code"].ToString(); data.issueDate = dr["issue_date"].ToString(); data.sellerId = Convert.ToInt32(dr["seller_id"]); data.buyerId = Convert.ToInt32(dr["buyer_id"]); data.taxCode = dr["tax_code"].ToString(); data.taxRate = Convert.ToDouble(dr["tax_rate"]); data.basisAmount = Convert.ToDouble(dr["basis_amount"]); data.lineTotal = Convert.ToDouble(dr["line_total"]); data.original = Convert.ToDouble(dr["original"]); data.difference = Convert.ToDouble(dr["difference"]); data.discount = Convert.ToDouble(dr["discount"]); data.taxTotal = Convert.ToDouble(dr["tax_total"]); data.grandTotal = Convert.ToDouble(dr["grand_total"]); data.remark = dr["remark"].ToString(); data.discount_rate = Convert.ToDouble(dr["discount_rate"]); data.service_charge = Convert.ToDouble(dr["service_charge"]); data.service_charge_rate = Convert.ToDouble(dr["service_charge_rate"]); } else { return(null); } } } } return(data); } catch (Exception ex) { throw ex; } }
private bool saveData() { try { setRef1(); validateData(); bool isNew = false; InvoiceObj obj = new InvoiceDao().find(this.invoiceID); if (obj == null) { obj = new InvoiceObj(); obj.invoiceId = this.invoiceID; isNew = true; } obj.invoiceName = INVOICE_NAME; if (purposeCbb.SelectedValue.ToString() == "DBNG99" || purposeCbb.SelectedValue.ToString() == "DBNS99") { obj.purpose = otherPurposeTb.Text; } else { obj.purpose = purposeCbb.Text; } obj.purposeCode = purposeCbb.SelectedValue.ToString(); obj.taxCode = "VAT"; obj.taxRate = Convert.ToDouble(vatTb.Text); obj.lineTotal = Convert.ToDouble(lineTotalTb.Text); obj.discount = 0.0; obj.taxTotal = Convert.ToDouble(taxTotalTb.Text); obj.grandTotal = Convert.ToDouble(grandTotalTb.Text); obj.remark = remarkTb.Text; obj.difference = Convert.ToDouble(diffValueTb.Text); obj.basisAmount = obj.difference; obj.original = Convert.ToDouble(originalValueTotal.Text); obj.issueDate = DateTime.Now.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("en-US")); BuyerObj buyer = (BuyerObj)nameCbb.SelectedItem; if (buyer == null) { buyer = new BuyerObj(); } buyer.name = nameCbb.Text; buyer.address1 = address1Tb.Text; buyer.houseNo = houseNoTb.Text; buyer.zipCode = zipcodeTb.Text; buyer.taxId = taxIdTb.Text; buyer.taxType = getTaxTypeSchemaID(taxIdType.SelectedIndex); if (is_branch.IsChecked.Value) { buyer.isBranch = true; buyer.branchId = branchNoTb.Text; } else { buyer.isBranch = false; buyer.branchId = "00000"; } buyer.email = emailTb.Text; buyer.contactPerson = contactTb.Text; buyer.phoneNo = phoneNoTb.Text; buyer.phoneExt = phoneExtTb.Text; buyer.provinceCode = ((AddressCodeListObj)provinceCbb.SelectedItem).code.Substring(0, 2); buyer.provinceName = ((AddressCodeListObj)provinceCbb.SelectedItem).changwat_th; buyer.districtCode = ((AddressCodeListObj)districtCbb.SelectedItem).code.Substring(0, 4); buyer.districtName = ((AddressCodeListObj)districtCbb.SelectedItem).amphoe_th; buyer.subdistrcitCode = ((AddressCodeListObj)subDistrictCbb.SelectedItem).code.Substring(0, 6); buyer.subdistrictName = ((AddressCodeListObj)subDistrictCbb.SelectedItem).tambon_th; obj.sellerId = saveContact(seller); obj.buyerId = saveContact(buyer); saveReferece(); saveInvoiceItem(); new InvoiceDao().save(obj, isNew); return(true); } catch (Exception ex) { createBtn.IsEnabled = true; new AlertBox(ex.Message).ShowDialog(); return(false); } }
public async Task <InvoiceRespObj> Handle(GetInvoiceDetailQuery request, CancellationToken cancellationToken) { var response = new InvoiceRespObj { Status = new APIResponseStatus { Message = new APIResponseMessage() } }; var supplierList = await _supRepo.GetAllSupplierAsync(); var query = _dataContext.inv_invoice.FromSqlRaw($"SELECT * FROM dbo.inv_invoice WHERE InvoiceId = {request.InvoiceId}").FirstOrDefault(); //var query = await _invoiceService.GetInvoiceAsync(request.InvoiceId); InvoiceObj item = new InvoiceObj(); List <InvoiceObj> resp = new List <InvoiceObj>(); if (query != null) { item = new InvoiceObj { LPONumber = query.LPONumber, InvoiceNumber = query.InvoiceNumber, Supplier = supplierList.FirstOrDefault(s => s.SupplierId == query.SupplierId)?.Name, InvoiceId = query.InvoiceId, RequestDate = query.RequestDate, Location = query.Address, AmountPayable = query.AmountPayable, DescriptionOfRequest = query.Description, PaymentOutstanding = query.AmountPayable, ExpectedDeliveryDate = query.DeliveryDate, PaymentTermId = query.PaymentTermId, AmountPaid = 0, Amount = query.Amount, LpoId = query.LPOId, SupplierId = query.SupplierId, Tax = _dataContext.cor_paymentterms.FirstOrDefault(q => q.PaymentTermId == query.PaymentTermId).TaxPercent, }; var alreadyPaidPhases = _dataContext.cor_paymentterms.Where(a => a.LPOId == item.LpoId && a.ProposedBy == (int)Proposer.STAFF && a.PaymentStatus == (int)PaymentStatus.Paid).ToList(); if (alreadyPaidPhases.Count() > 0) { item.AmountPaid = alreadyPaidPhases.Sum(f => f.NetAmount); item.PaymentOutstanding = (item.AmountPayable - alreadyPaidPhases.Sum(f => f.NetAmount)); } item.Phase = _dataContext.cor_paymentterms.FirstOrDefault(q => q.PaymentTermId == item.PaymentTermId).Phase; //item.Tax = (lpo.Tax / _dataContext.cor_paymentterms.Count(r => r.LPOId == item.LpoId && r.ProposedBy == (int)Proposer.STAFF)); item.GrossAmount = item.Amount; item.PaymentTerms = _dataContext.cor_paymentterms.Where(r => r.ProposedBy == (int)Proposer.STAFF && r.LPOId == item.LpoId).Select(c => new PaymentTermsObj { BidAndTenderId = c.BidAndTenderId, Comment = c.Comment, Completion = c.Completion, Amount = c.Amount, NetAmount = c.NetAmount, Payment = c.Payment, PaymentStatus = c.PaymentStatus, PaymentTermId = c.PaymentTermId, Phase = c.Phase, ProjectStatusDescription = c.ProjectStatusDescription, Status = c.Status, PhaseTax = c.TaxPercent, //Convert.ToDouble(lpo.Tax / _dataContext.cor_paymentterms.Count(r => r.LPOId == item.LpoId && r.ProposedBy == (int)Proposer.STAFF)), PaymentStatusName = Convert.ToString((PaymentStatus)c.PaymentStatus), ProposedBy = c.ProposedBy, StatusName = Convert.ToString((JobProgressStatus)c.Status) }).ToList(); resp.Add(item); } response.Invoices = resp; response.Status.IsSuccessful = true; return(response); }
private bool saveData() { try { setRef1(); validateData(); bool isNew = false; InvoiceObj obj = new InvoiceDao().find(this.invoiceID); if (obj == null) { obj = new InvoiceObj(); obj.invoiceId = this.invoiceID; isNew = true; } if (is_replace.IsChecked.Value) { obj.purpose = purposeCbb.Text; obj.issueDate = docDateTb.Text; obj.purposeCode = purposeCbb.SelectedValue.ToString(); if (purposeCbb.SelectedValue.ToString() == "TIVC99") { obj.purpose = otherPurposeTb.Text; } else { obj.purpose = purposeCbb.Text; } } else { obj.issueDate = DateTime.Now.ToString("dd/MM/yyyy", new CultureInfo("en-US")); } obj.invoiceName = "ใบกำกับภาษี"; obj.taxCode = "VAT"; obj.taxRate = Convert.ToDouble(vatTb.Text); obj.basisAmount = Convert.ToDouble(basisAmountTb.Text); obj.lineTotal = Convert.ToDouble(lineTotalTb.Text); obj.discount = Convert.ToDouble(extraDiscountTb.Text); obj.discount_rate = Convert.ToDouble(extraDiscountRateTb.Text); obj.taxTotal = Convert.ToDouble(taxTotalTb.Text); obj.grandTotal = Convert.ToDouble(grandTotalTb.Text); obj.service_charge = Convert.ToDouble(serviceChargeTb.Text); obj.service_charge_rate = Convert.ToDouble(serviceChargeRateTb.Text); //obj.issueDate = DateTime.Now.ToString(); obj.remark = remarkTb.Text; BuyerObj buyer = (BuyerObj)nameCbb.SelectedItem; if (buyer == null) { buyer = new BuyerObj(); } buyer.name = nameCbb.Text; buyer.address1 = address1Tb.Text; buyer.houseNo = houseNoTb.Text; buyer.zipCode = zipcodeTb.Text; buyer.taxId = taxIdTb.Text; if (is_branch.IsChecked.Value) { buyer.isBranch = true; buyer.branchId = branchNoTb.Text; } else { buyer.isBranch = false; buyer.branchId = "00000"; } buyer.email = emailTb.Text; buyer.contactPerson = contactTb.Text; buyer.phoneNo = phoneNoTb.Text; buyer.phoneExt = phoneExtTb.Text; buyer.provinceCode = ((AddressCodeListObj)provinceCbb.SelectedItem).code.Substring(0, 2); buyer.provinceName = ((AddressCodeListObj)provinceCbb.SelectedItem).changwat_th; buyer.districtCode = ((AddressCodeListObj)amphoeCbb.SelectedItem).code.Substring(0, 4); buyer.districtName = ((AddressCodeListObj)amphoeCbb.SelectedItem).amphoe_th; buyer.subdistrcitCode = ((AddressCodeListObj)tambonCbb.SelectedItem).code.Substring(0, 6); buyer.subdistrictName = ((AddressCodeListObj)tambonCbb.SelectedItem).tambon_th; obj.sellerId = saveContact(seller); obj.buyerId = saveContact(buyer); saveReferece(); saveInvoiceItem(); new InvoiceDao().save(obj, isNew); return(true); } catch (Exception ex) { createBtn.IsEnabled = true; new AlertBox(ex.Message).ShowDialog(); return(false); } }