public List<XObjs.ReportItem> getApplicationReportItem(string xcategory, string used_status, string xgt, string fromDate, string toDate)
{
List<XObjs.ReportItem> xlist = new List<XObjs.ReportItem>();
int sn = 1; string command_text = "";
SqlConnection connection = new SqlConnection(hf.ConnectXpay());
command_text += "select fee_list.item_code,fee_list.xdesc,fee_list.init_amt,fee_list.tech_amt, fee_details.xid AS fdID, fee_details.xqty,fee_details.tot_amt, ";
command_text += " twallet.xid,twallet.transID,twallet.xpay_status,twallet.xgt,twallet.xreg_date ,hwallet.xid AS hID,hwallet.used_status,hwallet.product_title, ";
command_text += " CAST(hwallet.transID AS nvarchar)+'-'+CAST(hwallet.fee_detailsID AS nvarchar)+'-'+CAST(hwallet.xid AS nvarchar) AS newtransID ";
command_text += " FROM fee_list LEFT OUTER JOIN fee_details ON fee_list.xid=fee_details.fee_listID ";
command_text += " LEFT OUTER JOIN twallet ON fee_details.twalletID=twallet.xid ";
command_text += " LEFT OUTER JOIN hwallet ON twallet.transID=hwallet.transID ";
command_text += " WHERE (twallet.xpay_status='1') AND (twallet.xgt='" + xgt + "') AND (fee_list.xcategory='" + xcategory + "') AND (hwallet.used_status='" + used_status + "') ";
command_text += " AND (twallet.xreg_date BETWEEN '" + fromDate + "' AND '" + toDate + "') ORDER BY twallet.xid DESC ";
SqlCommand command = new SqlCommand(command_text, connection);
connection.Open(); command.CommandTimeout = 0;
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
XObjs.ReportItem x = new XObjs.ReportItem();
x.sn = sn.ToString();
x.hID = reader["hID"].ToString();
x.fdID = reader["fdID"].ToString();
x.transID = reader["transID"].ToString();
x.newtransID = reader["newtransID"].ToString();
x.item_code = reader["item_code"].ToString();
x.item_desc = reader["xdesc"].ToString();
x.payment_date = reader["xreg_date"].ToString();
x.tech_amt = reader["tech_amt"].ToString();
x.init_amt = reader["init_amt"].ToString();
x.total_amt = reader["tot_amt"].ToString();
x.qty = reader["xqty"].ToString();
x.used_status = reader["used_status"].ToString();
x.product_title = reader["product_title"].ToString();
xlist.Add(x);
sn++;
}
reader.Close();
return xlist;
}
public List<XObjs.ReportItem> getChargeBackReportItemByMerchantRefD(string MerchantReference)
{
List<XObjs.ReportItem> xlist = new List<XObjs.ReportItem>();
int sn = 1; string command_text = "";
SqlConnection connection = new SqlConnection(hf.ConnectXpay());
command_text += "select fee_list.item_code,fee_list.xdesc,fee_list.init_amt,fee_list.tech_amt, fee_details.xid AS fdID, fee_details.xqty,fee_details.tot_amt, ";
command_text += " twallet.xid,twallet.applicantID,twallet.transID,twallet.xpay_status,twallet.xgt,twallet.xreg_date ,hwallet.xid AS hID,hwallet.used_status,hwallet.product_title, ";
command_text += " CAST(hwallet.transID AS nvarchar)+'-'+CAST(hwallet.fee_detailsID AS nvarchar)+'-'+CAST(hwallet.xid AS nvarchar) AS newtransID,InterSwitchPostFields.isw_conv_fee AS isw_amt ";
command_text += " FROM fee_list LEFT OUTER JOIN fee_details ON fee_list.xid=fee_details.fee_listID ";
command_text += " LEFT OUTER JOIN twallet ON fee_details.twalletID=twallet.xid ";
command_text += " LEFT OUTER JOIN hwallet ON twallet.transID=hwallet.transID ";
command_text += " left outer join InterSwitchPostFields on twallet.transID=InterSwitchPostFields.txn_ref ";
command_text += " WHERE (InterSwitchPostFields.MerchantReference='" + MerchantReference + "')";
SqlCommand command = new SqlCommand(command_text, connection);
connection.Open(); command.CommandTimeout = 0;
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
XObjs.ReportItem x = new XObjs.ReportItem();
x.sn = sn.ToString();
x.applicantID = reader["applicantID"].ToString();
x.hID = reader["hID"].ToString();
x.fdID = reader["fdID"].ToString();
x.transID = reader["transID"].ToString();
x.newtransID = reader["newtransID"].ToString();
x.item_code = reader["item_code"].ToString();
x.item_desc = reader["xdesc"].ToString();
x.payment_date = reader["xreg_date"].ToString();
x.tech_amt = reader["tech_amt"].ToString();
x.init_amt = reader["init_amt"].ToString();
x.total_amt = reader["tot_amt"].ToString();
x.isw_amt = reader["isw_amt"].ToString();
x.qty = reader["xqty"].ToString();
x.used_status = reader["used_status"].ToString();
x.product_title = reader["product_title"].ToString();
xlist.Add(x);
sn++;
}
reader.Close();
return xlist;
}
public List<XObjs.ReportItem> getPaymentReportItemISW(string xcategory, string xpay_status,string fromDate, string toDate, string merchant_type)
{
List<XObjs.ReportItem> xlist = new List<XObjs.ReportItem>();
int sn = 1; string command_text = "";
SqlConnection connection = new SqlConnection(hf.ConnectXpay());
command_text += "select InterSwitchPostFields.amount as 'full_amt',fee_list.item_code,fee_list.xdesc,fee_list.init_amt,fee_list.tech_amt, fee_details.xid AS fdID, fee_details.xqty,fee_details.tot_amt, ";
command_text += " twallet.xid,twallet.applicantID,twallet.transID,twallet.xpay_status,twallet.xgt,twallet.xreg_date ,hwallet.xid AS hID,hwallet.used_status,hwallet.product_title, ";
command_text += " CAST(hwallet.transID AS nvarchar)+'-'+CAST(hwallet.fee_detailsID AS nvarchar)+'-'+CAST(hwallet.xid AS nvarchar) AS newtransID,InterSwitchPostFields.isw_conv_fee AS isw_amt ";
command_text += " FROM fee_list LEFT OUTER JOIN fee_details ON fee_list.xid=fee_details.fee_listID ";
command_text += " LEFT OUTER JOIN twallet ON fee_details.twalletID=twallet.xid ";
command_text += " LEFT OUTER JOIN hwallet ON twallet.transID=hwallet.transID ";
command_text += " left outer join InterSwitchPostFields on twallet.transID=InterSwitchPostFields.txn_ref ";
if(xpay_status=="1")
{ command_text += " WHERE ( InterSwitchPostFields.trans_status='00') "; }
else
{ command_text += " WHERE ( InterSwitchPostFields.trans_status <> '00') "; }
if (xcategory != "all") { command_text += "AND (fee_list.xcategory='" + xcategory + "') "; }
command_text += " AND (SUBSTRING(InterSwitchPostFields.TransactionDate,1,10) BETWEEN '" + fromDate + "' AND '" + toDate + "') ";
if(merchant_type!="") { command_text += " AND (fee_list.xsync='" + merchant_type + "') "; }
else
{ }
command_text += " ORDER BY twallet.xid DESC";
SqlCommand command = new SqlCommand(command_text, connection);
connection.Open();command.CommandTimeout = 0;
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
XObjs.ReportItem x = new XObjs.ReportItem();
x.sn = sn.ToString();
x.full_amt = reader["full_amt"].ToString();
x.applicantID = reader["applicantID"].ToString();
x.hID = reader["hID"].ToString();
x.fdID = reader["fdID"].ToString();
x.transID = reader["transID"].ToString();
x.newtransID = reader["newtransID"].ToString();
x.item_code = reader["item_code"].ToString();
x.item_desc = reader["xdesc"].ToString();
x.payment_date = reader["xreg_date"].ToString();
x.tech_amt = reader["tech_amt"].ToString();
x.init_amt = reader["init_amt"].ToString();
x.total_amt = reader["tot_amt"].ToString();
x.isw_amt = reader["isw_amt"].ToString();
x.qty = reader["xqty"].ToString();
x.used_status = reader["used_status"].ToString();
x.product_title = reader["product_title"].ToString();
xlist.Add(x);
sn++;
}
reader.Close();
return xlist;
}