public IEnumerable <TranlogViewModel> getUserLoyaltyRewardTransactions(string id, int pagenumber, int pagelength)
{
int offset = 0;
if (pagenumber != 1)
{
offset = (pagenumber - 1) * pagelength;
}
// Specify connection options and open an connection
NpgsqlConnection conn = new NpgsqlConnection(_configuration.GetConnectionString("PostgreSqlDb"));
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand("Select COUNT(*) OVER() AS TotalCount, t.id, c.realid,t.loyalty,t.dsamount,t.dsbalance,t.irc,t.itc,t.date ,CASE WHEN t.itc='wcredit' THEN t.loyaltyamount ELSE 0 end as CreditAmount, CASE WHEN t.itc<>'wcredit' THEN t.loyaltyamount ELSE 0 end DebitAmount from public.tranlog t inner join public.cardholder c on t.cardholder = c.id where t.loyaltyamount is not null and c.realId=" + "'" + id + "'" + " order by id desc OFFSET " + offset + " LIMIT " + pagelength, conn);
// Execute a query
NpgsqlDataReader dr = cmd.ExecuteReader();
var detail = new List <TranlogViewModel>();
// Read all rows
while (dr.Read())
{
TranlogViewModel model = new TranlogViewModel();
model.realid = dr["realid"].ToString();
if (!(dr["dsbalance"] is DBNull))
{
model.dsbalance = Convert.ToDecimal(dr["dsbalance"]);
}
else
{
model.dsbalance = 0;
}
if (!(dr["dsamount"] is DBNull))
{
model.dsamount = Convert.ToDecimal(dr["dsamount"]);
}
else
{
model.dsamount = 0;
}
model.id = Convert.ToInt64(dr["id"]);
model.TotalCount = Convert.ToInt32(dr["TotalCount"]);
model.date = Convert.ToDateTime(dr["date"]);
model.CreditAmount = Convert.ToDecimal(dr["CreditAmount"]);
model.DebitAmount = Convert.ToDecimal(dr["DebitAmount"]);
detail.Add(model);
}
// Close connection
conn.Close();
return(detail);
}
public IEnumerable <TranlogViewModel> gettranslog(string id)
{
// Specify connection options and open an connection
NpgsqlConnection conn = new NpgsqlConnection(_configuration.GetConnectionString("PostgreSqlDb"));
conn.Open();
// id = "0b592fc3-7829-4e39-b48b-e0c71cf75665";
// Define a query
// NpgsqlCommand cmd = new NpgsqlCommand("Select t.id, c.realid,t.loyalty,t.dsamount,t.irc,t.itc,t.date from public.tranlog t inner join public.cardholder c on t.cardholder = c.id where c.realId= "+id +"and t.date="+DateTime.Now, conn);
NpgsqlCommand cmd = new NpgsqlCommand("select *,2000 - (sumamt ) total from (Select sum(abs(t.dsamount)) sumamt, c.realid from public.tranlog t inner join public.cardholder c on t.cardholder = c.id where Date(t.date)= current_date and c.realid=" + "'" + id + "'" + " group by c.realid)b", conn);
// NpgsqlCommand cmd = new NpgsqlCommand("select *,2000 - (sumamt) total from (Select sum(abs(t.dsamount)) sumamt, c.realid from public.tranlog t inner join public.cardholder c on t.cardholder = c.id where Date(t.date)= current_date group by c.realid)b join(select realid, dsbalance from tranlog join (Select max(t.id) mid, c.realid from public.tranlog t inner join public.cardholder c on t.cardholder = c.id where Date(t.date)= current_date group by c.realid)a on a.mid=id)c on c.realid=b.realid where c.realid=" + "'" +id +"'",conn);
// Execute a query
NpgsqlDataReader dr = cmd.ExecuteReader();
var detail = new List <TranlogViewModel>();
// Read all rows
while (dr.Read())
{
TranlogViewModel model = new TranlogViewModel();
model.realid = dr["realid"].ToString();
model.loyalty = dr["sumamt"].ToString();
// if (!(dr["dsbalance"] is DBNull))
// model.dsbalance = Convert.ToDecimal(dr["dsbalance"]);
// else
// model.dsbalance = 0;
//if (!(dr["dsamount"] is DBNull))
// model.dsamount = Convert.ToDecimal(dr["dsamount"]);
//else
// model.dsamount = 0;
// model.irc = dr["irc"].ToString();
model.total = dr["total"].ToString();
// model.date = Convert.ToDateTime(dr["date"]);
detail.Add(model);
}
// Close connection
conn.Close();
return(detail);
}
private void gettranlog()
{
string maxtranid = GetMaxTranlogid();
IConfiguration Configuration = new ConfigurationBuilder().AddJsonFile("appsettings.json", optional: true, reloadOnChange: true).AddEnvironmentVariables().Build();
// Specify connection options and open an connection
NpgsqlConnection conn = new NpgsqlConnection(Configuration.GetConnectionString("PostgreSqlDb"));
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT t.id, c.realid, t.dstxnid, t.loyalty, t.dsamount, t.dsbalance, t.irc, t.itc, t.date ,t.Pcode from public.tranlog t inner join public.cardholder c on t.cardholder = c.id where t.irc = '0000' and t.loyalty = '1' and t.voidcount < '1' and t.reversalcount < '1' AND t.itc in ('sale' ,'refund' ,'capture') AND t.id >" + maxtranid + " order by t.id ", conn);
/// NpgsqlCommand cmd = new NpgsqlCommand("SELECT t.id, c.realid, t.dstxnid, t.loyalty, t.dsamount, t.dsbalance, t.irc, t.itc, t.date, t.Pcode from public.tranlog t inner join public.cardholder c on t.cardholder = c.id where t.irc = '0000' and t.loyalty = '1' AND t.itc in ('sale' ,'refund' ,'capture') AND t.id >" + maxtranid + " order by t.id ", conn);
// Execute a query
NpgsqlDataReader dr = cmd.ExecuteReader();
var detail = new List <TranlogViewModel>();
// Read all rows
while (dr.Read())
{
TranlogViewModel model = new TranlogViewModel();
model.id = Convert.ToInt64(dr["id"]);
model.realid = dr["realid"].ToString();
model.loyalty = dr["loyalty"].ToString();
if (!(dr["dsbalance"] is DBNull))
{
model.dsbalance = Convert.ToDecimal(dr["dsbalance"]);
}
else
{
model.dsbalance = 0;
}
if (!(dr["dsamount"] is DBNull))
{
model.dsamount = Convert.ToDecimal(dr["dsamount"]);
}
else
{
model.dsamount = 0;
}
model.irc = dr["irc"].ToString();
model.itc = dr["itc"].ToString();
model.date = Convert.ToDateTime(dr["date"]);
var usertype = "";
if (dr["Pcode"].ToString() == "credit")
{
usertype = GetUserType(dr["realid"].ToString());
}
else
{
usertype = "regular";
}
using (StreamWriter sw = File.AppendText(path))
{
sw.WriteLine(dr["id"].ToString() + " : " + dr["realid"].ToString());
sw.WriteLine(usertype + " : " + dr["Pcode"].ToString());
}
InserAndCalculateUserLoyaltyPoints(model.realid, dr["dstxnid"].ToString(), model.dsamount, model.date.ToString(), model.id, usertype);
detail.Add(model);
}
// Close connection
conn.Close();
conn.Dispose();
}
