public static ResponseItem <bool> Create(ClientPayment client_payment)
{
var res = new ResponseItem <bool>();
try
{
using MySqlConnection connection = new MySqlConnection("server=localhost; Database=expenses; uid=admin; Pwd=database123;");
connection.Open();
MySqlCommand command = connection.CreateCommand();
MySqlTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
DateTime now = DateTime.Now;
var date = client_payment.date.ToString("yyyy-MM-dd", new CultureInfo("es-CO"));
var sql = $@"insert into expenses.clients_payments (id_client,value,date,created_on) values ({client_payment.id_client},{client_payment.value},'{date}','{now.ToString("yyyy/MM/dd HH:mm:ss", new CultureInfo("es-CO"))}');";
command.CommandText = sql;
var dr = command.ExecuteNonQuery();
transaction.Commit();
connection.Close();
res.success = true;
res.data = true;
res.messages.Add("Se agrego el pago correctamente.");
}
catch (Exception ex)
{
res.errors.Add(ex.Message);
res.errors.Add(ex.StackTrace);
}
return(res);
}
// GET: PaymentList
public ActionResult ClientPaymentList()
{
List <ClientPayment> clist = new List <ClientPayment>();
SqlConnection con = new SqlConnection(@"Data Source=(localdb)\MsSqlLocalDb;Initial Catalog=project;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from ClientPayment";
SqlDataReader dr = cmd.ExecuteReader();
//Models.Client o = new Models.Client();
while (dr.Read())
{
ClientPayment o = new ClientPayment();
o.Cid = Convert.ToInt32(dr["Cid"]);
o.Date = Convert.ToString(dr["Date"]);
o.Pid = Convert.ToInt32(dr["Pid"]);
o.Ammount = Convert.ToDouble(dr["Amount"]);
clist.Add(o);
}
return(View(clist));
}
public bool SaveClientPayment(ClientPayment item)
{
try
{
ctxWrite.ClientPayments_AddEdit(item.PaymentID, item.Invoice_ID, item.Client_ID, item.ExchangeCompany_ID, item.Currency_ID, item.PaymentValue, item.PaymentDate, item.ReceiverName, item.Notes, item.Store_ID, item.Other, item.System_Who_Add, item.System_LastAction_IP);
return(true);
}
catch { return(false); }
}
/// <summary>
/// getClientData: get a full set of data for a single client
/// </summary>
/// <param name="clientId"></param>
/// <param name="clientData"></param>
/// <param name="errText"></param>
/// <returns></returns>
public Boolean getClientData(String clientId, ref ClientData clientData, ref String errText)
{
try {
lock (_syncCall) {
UniSubroutine s = _sess.CreateUniSubroutine("u2_getClientData", 3);
s.SetArg(0, clientId);
s.Call();
if (String.IsNullOrEmpty(errText) == false)
{
ShowError(errText);
return(false);
}
UniDynArray da = s.GetArgDynArray(1);
clientData.ClientId = da.Extract(BookConst.CLIENTDATA_CLIENTID).StringValue;
clientData.Surname = da.Extract(BookConst.CLIENTDATA_SURNAME).StringValue;
clientData.Forename = da.Extract(BookConst.CLIENTDATA_FORENAME).StringValue;
clientData.Address = da.Extract(BookConst.CLIENTDATA_ADDRESS).StringValue.Replace(VM_STR, CRLF);
clientData.Country = da.Extract(BookConst.CLIENTDATA_COUNTRY).StringValue;
clientData.JoinDate = da.Extract(BookConst.CLIENTDATA_JOINDATE).StringValue;
clientData.OrderList.Clear();
clientData.PaymentList.Clear();
int noOrders = da.Dcount(BookConst.CLIENTDATA_ORDERIDS);
for (int i = 1; i <= noOrders; i++)
{
ClientOrder co = new ClientOrder();
co.OrderId = da.Extract(BookConst.CLIENTDATA_ORDERIDS, i).StringValue;
co.OrderStatus = da.Extract(BookConst.CLIENTDATA_ORDERSTATUS, i).StringValue;
co.OrderDate = da.Extract(BookConst.CLIENTDATA_ORDERDATE, i).StringValue;
co.OrderTotal = Utils.safeDouble(da.Extract(BookConst.CLIENTDATA_ORDERTOTAL, i).StringValue);
co.OrderTotal = co.OrderTotal / 100;
clientData.OrderList.Add(co);
}
int noPayments = da.Dcount(BookConst.CLIENTDATA_PAYMENTIDS);
for (int i = 1; i <= noPayments; i++)
{
ClientPayment cp = new ClientPayment();
cp.PaymentId = da.Extract(BookConst.CLIENTDATA_PAYMENTIDS, i).StringValue;
cp.PaymentDate = da.Extract(BookConst.CLIENTDATA_PAYMENTDATE, i).StringValue;
cp.PaymentAmount = Utils.safeInt(da.Extract(BookConst.CLIENTDATA_PAYMENTAMT, i).StringValue);
cp.PaymentAmount = cp.PaymentAmount / 100;
clientData.PaymentList.Add(cp);
}
}
} catch (Exception ex) {
ShowError(ex.Message);
return(false);
}
return(true);
}
public void UpdateClientPayment(ClientPayment clientPayment, int id, int concernId, string userName, int userId)
{
var client = _context.ClientPayments.FirstOrDefault(x => x.ClientPaymentId == id);
client.ClientId = clientPayment.ClientId;
client.PaymentDate = clientPayment.PaymentDate;
client.PaymentAmount = clientPayment.PaymentAmount;
client.PaymentType = clientPayment.PaymentType;
client.BankId = clientPayment.BankId;
client.PayeesName = clientPayment.PayeesName;
client.Notes = clientPayment.Notes;
client.Description = clientPayment.Description;
client.ModificationDate = DateTime.Now;
client.ModifierId = userId;
_context.SaveChanges();
}
public void AddClientPayment(ClientPayment clientPayment, int concernId, string userName, int userId)
{
var date = DateTime.Now;
using (DbContextTransaction transaction = _context.Database.BeginTransaction())
{
clientPayment.CreationDate = date;
clientPayment.CreatorId = userId;
clientPayment.ModificationDate = date;
clientPayment.ModifierId = userId;
clientPayment.ConcernId = concernId;
_context.ClientPayments.Add(clientPayment);
_context.SaveChanges();
transaction.Commit();
};
}
// GET: PaymentList
public ActionResult ClientPaymentList()
{
if (Session["id"] == null)
{
return(RedirectToAction("Login", "LoginId"));
}
try
{
List <ClientPayment> clist = new List <ClientPayment>();
SqlConnection con = new SqlConnection(@"Data Source=(localdb)\MsSqlLocalDb;Initial Catalog=project;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from ClientPayment";
SqlDataReader dr = cmd.ExecuteReader();
//Models.Client o = new Models.Client();
while (dr.Read())
{
ClientPayment o = new ClientPayment();
o.Cid = Convert.ToInt32(dr["Cid"]);
o.Date = Convert.ToString(dr["Date"]);
o.TransactionId = (dr["TransactionId"]).ToString();
o.Ammount = Convert.ToDouble(dr["Amount"]);
clist.Add(o);
}
return(View(clist));
}
catch (Exception e)
{
return(RedirectToAction("LoginId", "Login"));
}
}
/// <summary>
/// Add new ClientPayment to db.
/// </summary>
private void SaveClientPaymentInformation()
{
ClientPayment item = new ClientPayment();
try
{
if (hfID.Value != "")
{
item.PaymentID = Convert.ToInt32(hfID.Value);
}
item.Client_ID = Convert.ToInt32(ddlClients.SelectedValue);
item.Currency_ID = Convert.ToInt32(ddlCurrency.SelectedValue);
item.Notes = txtShortdesc.Text;
item.ReceiverName = txtReceivedName.Text;
if (ddlExchangeCo.SelectedIndex > 0)
{
item.ExchangeCompany_ID = Convert.ToInt32(ddlExchangeCo.SelectedValue);
}
if (ltlInvoiceID.Text != "")
{
item.Invoice_ID = Convert.ToInt32(ltlInvoiceID.Text);
}
if (txtDate.Text != "")
{
item.PaymentDate = Convert.ToDateTime(txtDate.Text);
}
if (txtPaymentValue.Text != "")
{
item.PaymentValue = Convert.ToDecimal(txtPaymentValue.Text);
}
//item.Active = cbActive.Checked;
item.Store_ID = 1; // Store sale from.
item.System_Who_Add = ClientSession.Current.loginId;
item.System_LastAction_IP = ClientSession.Current.IP;
// perform saving method.
if (new ClientPaymentsManager().SaveClientPayment(item))
{
// go to ClientPayments list.
string url = string.Format("client/{0}/payments/{1}.aspx", ddlClients.SelectedValue, MinutesuaeConcrete.ToSeoUrl(ddlClients.SelectedItem.Text)); //"ClientPaymentsView.aspx";
if (hfID.Value != "")
{
Response.Redirect(ResolveClientUrl(url));
}
else
{
Response.Redirect(ResolveClientUrl(url));
}
}
else
{
lblError.Text = Resources.AdminResources_en.ErrorSave;
}
}
catch { lblError.Text = Resources.AdminResources_en.ErrorSave; }
}
public bool Update(ClientPayment clientPayment)
{
return(Connection.Update(clientPayment));
}
public bool Delete(ClientPayment clientPayment)
{
return(Connection.Delete(clientPayment));
}
public bool Add(ClientPayment clientPayment)
{
return(Connection.Insert(clientPayment) != 0);
}
public static ResponseItem <ClientDetail> GetByClient(int id_client)
{
var res = new ResponseItem <ClientDetail>();
res.data = new ClientDetail();
res.data.payments = new List <ClientPayment>();
try
{
var client = Clients.GetById(id_client);
var payment_days = 1;
if (client.data.payment_type == 2)
{
payment_days = 7;
}
else if (client.data.payment_type == 3)
{
payment_days = 14;
}
else if (client.data.payment_type == 4)
{
payment_days = 30;
}
using MySqlConnection connection = new MySqlConnection("server=localhost; Database=expenses; uid=admin; Pwd=database123;");
connection.Open();
MySqlCommand command = connection.CreateCommand();
MySqlTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
var sql = $"select * from expenses.clients_payments where id_client = {id_client}";
command.CommandText = sql;
var dr = command.ExecuteReader();
while (dr.Read())
{
if (dr != null)
{
var client_payment = new ClientPayment();
client_payment.id_client_payment = Convert.ToInt32(dr["id_client_payment"]);
client_payment.id_client = id_client;
client_payment.value = Convert.ToSingle(dr["value"]);
client_payment.date = Convert.ToDateTime(dr["date"]);
res.data.payments.Add(client_payment);
}
}
res.data.id_client = id_client;
res.data.name = client.data.name;
res.data.phone = client.data.phone;
res.data.identification = client.data.identification;
res.data.interest_rate = client.data.interest_rate;
res.data.loan = client.data.loan;
res.data.time_limit = client.data.time_limit;
res.data.payment_type = client.data.payment_type;
res.data.payment_paid = res.data.payments.Sum(x => x.value);
var interest_value = client.data.loan * (client.data.interest_rate / 100);
res.data.payment_owed = (client.data.loan + interest_value) - res.data.payments.Sum(x => x.value);
res.data.payment_scheduled = (client.data.loan + interest_value) / (client.data.time_limit / payment_days);
res.success = true;
dr.Close();
transaction.Commit();
}
catch (Exception ex)
{
res.errors.Add(ex.Message);
res.errors.Add(ex.StackTrace);
}
return(res);
}
public ActionResult <ResponseItem <bool> > create([FromBody] ClientPayment client_payment)
{
var res = ClientsPayments.Create(client_payment);
return(res);
}
