public WalletModel.getWalletHistory GetWalletHistory(string CustomerId = "")
{
WalletModel.getWalletHistory objeWalletdt = new WalletModel.getWalletHistory();
DateTime dtCreatedon = DateTime.Now;
try
{
objeWalletdt.response = "1";
objeWalletdt.message = "Successfully";
objeWalletdt.WalletHistoryList = new List <WalletModel.WalletHistoryList>();
string WalletMasterQry = " SELECT wallet_id, wallet_amount, campaign_name, offer_id from WalletMaster " +
" WHERE ISNULL(is_apply_all_customer,0) = 1 " +
" AND ISNULL(is_active,0) = 1 " +
" AND GETDATE() >= start_date and GETDATE() <= end_date ";
DataTable dtWalletMasterQry = dbc.GetDataTable(WalletMasterQry);
if (dtWalletMasterQry != null && dtWalletMasterQry.Rows.Count > 0)
{
for (int iCtr = 0; iCtr < dtWalletMasterQry.Rows.Count; iCtr++)
{
string walletId = dtWalletMasterQry.Rows[iCtr]["wallet_id"].ToString();
string walletAmt = dtWalletMasterQry.Rows[iCtr]["wallet_amount"].ToString();
string campName = dtWalletMasterQry.Rows[iCtr]["campaign_name"].ToString();
string offerId = dtWalletMasterQry.Rows[iCtr]["offer_id"].ToString().TrimEnd();
string chkHistoryRecord = " SELECT * FROM tblWalletCustomerHistory " +
" WHERE customer_id = " + CustomerId +
" AND wallet_id = " + walletId;
string chkLinkRecord = " SELECT * FROM tblWalletCustomerLink " +
" WHERE customer_id = " + CustomerId +
" AND wallet_id = " + walletId;
DataTable dtHistoryRecord = dbc.GetDataTable(chkHistoryRecord);
DataTable dtLinkRecord = dbc.GetDataTable(chkLinkRecord);
int linkVAL = 0;
if (dtHistoryRecord == null || dtHistoryRecord.Rows.Count == 0)
{
string balHistory = " SELECT top 1 id,balance From tblWalletCustomerHistory " +
" WHERE customer_id=" + CustomerId +
"AND wallet_id not in(" + walletId + ")" +
" order by 1 desc";
DataTable dtmainBal = dbc.GetDataTable(balHistory);
Decimal balAmt = 0;
Decimal walletBalance = 0;
if (dtmainBal != null && dtmainBal.Rows.Count > 0)
{
walletBalance = Convert.ToDecimal(dtmainBal.Rows[0]["balance"]);
}
balAmt = Convert.ToDecimal(walletAmt) + walletBalance;
if ((dtHistoryRecord == null || dtHistoryRecord.Rows.Count == 0) && (dtLinkRecord == null || dtLinkRecord.Rows.Count == 0))
{
string[] para2 = { walletId, CustomerId, "1", dtCreatedon.ToString(), CustomerId };
string customerlinkinsertquery = "INSERT INTO [dbo].[tblWalletCustomerLink] ([wallet_id],[customer_id],[is_active],[created_date],[created_by]) VALUES (@1,@2,@3,@4,@5) SELECT SCOPE_IDENTITY();";
linkVAL = dbc.ExecuteQueryWithParamsId(customerlinkinsertquery, para2);
}
if (offerId == "1")
{
if (dtHistoryRecord == null || dtHistoryRecord.Rows.Count == 0)
{
string[] para3 = { walletId, CustomerId, linkVAL.ToString(), dtCreatedon.ToString(), campName, walletAmt.ToString(), "", "", "0", balAmt.ToString(), "1", dtCreatedon.ToString(), CustomerId };
string customerwallethistoryQuery = "INSERT INTO [dbo].[tblWalletCustomerHistory] ([wallet_id],[customer_id],[wallet_link_id],[Cr_date],[Cr_description],[Cr_amount],[Dr_date],[Dr_description],[Dr_amount],[balance],[is_active],[created_date],[created_by]) VALUES (@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13);";
dbc.ExecuteQueryWithParamsId(customerwallethistoryQuery, para3);
}
}
}
else
{
}
}
//objeWalletdt.WalletBalance = walletBalance;
}
string where = "";
if (CustomerId != "" && CustomerId != null)
{
where = "AND [O].CustomerId=" + CustomerId;
string balHistory = " SELECT top 1 id,balance From tblWalletCustomerHistory " +
" WHERE customer_id=" + CustomerId +
" order by 1 desc";
DataTable dtmainBal = dbc.GetDataTable(balHistory);
if (dtmainBal != null && dtmainBal.Rows.Count > 0)
{
string walletBalance = dtmainBal.Rows[0]["balance"].ToString();
objeWalletdt.WalletBalance = walletBalance;
}
string querymain = " SELECT [H].customer_id,CAST(H.Cr_date AS DATE) as Cr_date,CAST(H.Dr_date AS DATE) as Dr_date, " +
" (case isnull(H.order_id, 0) when 0 then isnull(H.Cr_description, '') else 'Order Id ' + CAST(O.Id AS varchar) end) AS OrderId, " +
" H.Cr_amount, H.Dr_amount, H.balance " +
" FROM tblWalletCustomerHistory H " +
" LEFT OUTER JOIN[Order] O ON H.customer_id = O.CustomerId AND O.Id = H.order_id " +
" WHERE H.customer_id = " + CustomerId +
" order by H.Id desc";
//string querymain = " SELECT x.CustomerId, x.Date, X.OrderId, x.Cr_amount, x.Dr_amount, x.balance FROM " +
// " ( " +
// " SELECT [O].CustomerId, H.created_date AS[Date], 'Order Id ' + CAST(O.Id AS varchar) AS OrderId, " +
// " H.Cr_amount, H.Dr_amount, H.balance " +
// " FROM[Order] O " +
// " INNER JOIN tblWalletCustomerHistory H ON H.customer_id = O.CustomerId " +
// " UNION ALL " +
// " SELECT H.customer_id, H.created_date AS[Date], W.campaign_name AS OrderId, " +
// " H.Cr_amount, H.Dr_amount, H.balance " +
// " FROM tblWalletCustomerHistory H " +
// " LEFT JOIN WalletMaster W ON W.wallet_id = H.wallet_id " +
// " ) x " +
// " WHERE X.CustomerId = " + CustomerId +
// " ORDER BY x.Date desc ";
//string querymain = "SELECT H.created_date AS [Date], O.Id AS OrderId, H.Cr_amount, " +
// " H.Dr_amount,H.balance " +
// " FROM[Order] O " +
// " INNER JOIN tblWalletCustomerHistory H ON H.customer_id = O.CustomerId " +
// where +
// " ORDER BY H.created_date desc ";
DataTable dtmain = dbc.GetDataTable(querymain);
if (dtmain != null && dtmain.Rows.Count > 0)
{
for (int i = 0; i < dtmain.Rows.Count; i++)
{
WalletModel.WalletHistoryList objHistory = new WalletModel.WalletHistoryList();
//string date = dtmain.Rows[i]["Date"].ToString();
string orderId = dtmain.Rows[i]["OrderId"].ToString();
string CrAmt = dtmain.Rows[i]["Cr_amount"].ToString();
string DrAmt = dtmain.Rows[i]["Dr_amount"].ToString();
string balance = dtmain.Rows[i]["balance"].ToString();
string crDate = dtmain.Rows[i]["Cr_date"] is DBNull ? string.Empty : Convert.ToDateTime(dtmain.Rows[i]["Cr_date"]).ToString("dd/MM/yyyy");
string drDate = dtmain.Rows[i]["Dr_date"] is DBNull ? string.Empty : Convert.ToDateTime(dtmain.Rows[i]["Dr_date"]).ToString("dd/MM/yyyy");
//objHistory.Date = date;
objHistory.Summary = orderId;
if (DrAmt != "0")
{
objHistory.CrDrAmount = DrAmt;
objHistory.type = "Debit";
}
else
{
objHistory.CrDrAmount = CrAmt;
objHistory.type = "Credit";
}
if (string.IsNullOrEmpty(crDate))
{
objHistory.Date = drDate;
}
else
{
objHistory.Date = crDate;
}
objHistory.Balance = balance;
objeWalletdt.WalletHistoryList.Add(objHistory);
}
objeWalletdt.response = CommonString.successresponse;
objeWalletdt.message = CommonString.successmessage;
}
else
{
objeWalletdt.response = CommonString.DataNotFoundResponse;
objeWalletdt.message = CommonString.DataNotFoundMessage;
}
}
return(objeWalletdt);
}
catch (Exception ex)
{
objeWalletdt.response = CommonString.Errorresponse;
objeWalletdt.message = ex.StackTrace + " " + ex.Message;
return(objeWalletdt);
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
if (!IsPostBack)
{
string CustomerId = clsCommon.getCurrentCustomer().id;
//DataTable dtWallet = dbc.GetDataTable("select CustomerId,(sum(Cr_Amount)-sum(Dr_Amount)) as avlbal,SUM(Dr_Amount)as Usedamt from Customer_Wallet_History where CustomerId=" + CustomerId + " group by customerid");
//DataTable dtWallet = dbc.GetDataTable("select Customer_Id,(sum(Cr_Amount)-sum(Dr_Amount)) as avlbal,SUM(Dr_Amount)as Usedamt from tblWalletCustomerHistory where Customer_Id=" + CustomerId + " group by Customer_Id");
//if (dtWallet.Rows.Count > 0)
//{
// lblusedbal.InnerHtml = dtWallet.Rows[0]["Usedamt"].ToString();
// lblavlbal.InnerHtml = dtWallet.Rows[0]["avlbal"].ToString();
//}
//else
//{
// lblusedbal.InnerHtml = "0.00";
// lblavlbal.InnerHtml = "0.00";
//}
string apiString = clsCommon.strApiUrl + "api/Wallet/GetWalletHistory?CustomerId=" + CustomerId;
string data = clsCommon.GET(apiString);
if (!String.IsNullOrEmpty(data))
{
WalletModel.getWalletHistory obj = JsonConvert.DeserializeObject <WalletModel.getWalletHistory>(data);
if (obj.response.Equals("1"))
{
walletbal.InnerHtml = Convert.ToDecimal(obj.WalletBalance).ToString("0.00");
string html = string.Empty;
foreach (var item in obj.WalletHistoryList)
{
string color = string.Empty;
string sign = string.Empty;
switch (item.type)
{
case "Debit":
color = "red;";
sign = "-₹ ";
break;
case "Credit":
color = "green;";
sign = "+₹ ";
break;
default:
color = "black;";
sign = "₹ ";
break;
}
html += "<a href=\"#\" class=\"list-group-item\">";
html += "<h5 class=\"list-group-item-heading\">" + item.Summary + "<span class=\"pull-right\" style=\"color:" + color + "\">" + sign + Convert.ToDecimal(item.CrDrAmount).ToString("0.00") + "</span></h5>";
html += "<p class=\"list-group-item-text\">" + item.Date + "<span class=\"pull-right\">Closing Balance:₹ " + Convert.ToDecimal(item.Balance).ToString("0.00") + "</span></p></a>";
}
wallethistory.InnerHtml = html;
}
}
}
}
catch (Exception ee)
{
}
}
