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);
}
}
