public List<XObjs.ReportItemGenISW> getPaymentReportItemGenISWByTransID(string transID, string merchant_type)
{
List<XObjs.ReportItemGenISW> xlist = new List<XObjs.ReportItemGenISW>();
int sn = 1; string command_text = "";
SqlConnection connection = new SqlConnection(hf.ConnectXpay());
command_text += "select CONVERT(varchar, CAST( CAST(InterSwitchPostFields.amount AS int)*.01 as money),1) as 'full_amt', twallet.xid as 'twallet_xid', ";
command_text += " SUBSTRING(InterSwitchPostFields.TransactionDate,1,19) as TransactionDate,twallet.transID,applicant.xname,applicant.address,applicant.xemail,applicant.xmobile,InterSwitchPostFields.isw_conv_fee AS isw_amt FROM InterSwitchPostFields ";
command_text += " LEFT OUTER JOIN twallet ON InterSwitchPostFields.txn_ref=twallet.transID ";
command_text += " LEFT OUTER JOIN applicant ON applicant.xid=twallet.applicantID ";
if (merchant_type != "")
{
command_text += " WHERE ( InterSwitchPostFields.txn_ref='" + transID + "') AND (fee_list.xsync='" + merchant_type + "') AND (InterSwitchPostFields.trans_status='00') ORDER BY twallet.xid DESC";
}
else
{
command_text += " WHERE ( InterSwitchPostFields.txn_ref='" + transID + "') AND (InterSwitchPostFields.trans_status='00') 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.ReportItemGenISW x = new XObjs.ReportItemGenISW();
x.sn = sn.ToString();
x.full_amt = reader["full_amt"].ToString();
x.isw_amt = reader["isw_amt"].ToString();
x.twallet_xid = reader["twallet_xid"].ToString();
x.TransactionDate = reader["TransactionDate"].ToString();
x.transID = reader["transID"].ToString();
x.applicant_name = reader["xname"].ToString();
x.applicant_address = reader["address"].ToString();
x.applicant_xemail = reader["xemail"].ToString();
x.applicant_xmobile = reader["xmobile"].ToString();
xlist.Add(x);
sn++;
}
reader.Close();
return xlist;
}
public List<XObjs.ReportItemGenISW> getPaymentReportItemGenISW(string xpay_status, string fromDate, string toDate, string merchant_type, string xorder)
{
List<XObjs.ReportItemGenISW> xlist = new List<XObjs.ReportItemGenISW>();
int sn = 1; string command_text = "";
SqlConnection connection = new SqlConnection(hf.ConnectXpay());
command_text += "select CONVERT(varchar, CAST( CAST(InterSwitchPostFields.amount AS int)*.01 as money),1) as 'full_amt', twallet.xid as 'twallet_xid', ";
// command_text += " SUBSTRING(InterSwitchPostFields.TransactionDate,1,19) as TransactionDate,twallet.transID,applicant.xname,applicant.address,applicant.xemail,applicant.xmobile,InterSwitchPostFields.isw_conv_fee AS isw_amt,fee_details.tech_amt *convert(int,fee_details.xqty) as tech_amt ,fee_details.xqty as Qty ,fee_list.init_amt as Cld_Fees,InterSwitchPostFields.pay_ref as pay_ref ,fee_list.item_code as item_code ,fee_list.xdesc as xdesc FROM InterSwitchPostFields ";
command_text += " SUBSTRING(InterSwitchPostFields.TransactionDate,1,19) as TransactionDate,(hwallet.transid + '-' + hwallet.fee_detailsID + '-' + cast( hwallet.xid as varchar) ) as transid,applicant.xname,applicant.address,applicant.xemail,applicant.xmobile,InterSwitchPostFields.isw_conv_fee AS isw_amt,fee_details.tech_amt *convert(int,fee_details.xqty) as tech_amt ,fee_details.xqty as Qty ,fee_list.init_amt as Cld_Fees,InterSwitchPostFields.pay_ref as pay_ref ,fee_list.item_code as item_code ,fee_list.xdesc as xdesc FROM InterSwitchPostFields ";
command_text += " LEFT OUTER JOIN twallet ON InterSwitchPostFields.txn_ref=twallet.transID ";
command_text += " LEFT OUTER JOIN hwallet ON hwallet.transID=twallet.transID ";
command_text += " LEFT OUTER JOIN applicant ON applicant.xid=twallet.applicantID ";
command_text += " LEFT OUTER JOIN fee_details ON fee_details.twalletID=twallet.xid ";
command_text += " LEFT OUTER JOIN fee_list ON fee_details.fee_listID=fee_list.xid ";
if (xpay_status == "1")
{ command_text += " WHERE ( InterSwitchPostFields.trans_status='00') "; }
else
{ command_text += " WHERE ( InterSwitchPostFields.trans_status <> '00') "; }
command_text += " AND (SUBSTRING(InterSwitchPostFields.TransactionDate,1,10) BETWEEN '" + fromDate + "' AND '" + toDate + "') ";
command_text += " ORDER BY twallet.xid "+xorder;
SqlCommand command = new SqlCommand(command_text, connection);
connection.Open(); command.CommandTimeout = 0;
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
XObjs.ReportItemGenISW x = new XObjs.ReportItemGenISW();
x.sn = sn.ToString();
x.full_amt = reader["full_amt"].ToString();
x.isw_amt = reader["isw_amt"].ToString();
x.twallet_xid = reader["twallet_xid"].ToString();
x.TransactionDate = reader["TransactionDate"].ToString();
x.transID = reader["transID"].ToString();
x.applicant_name = reader["xname"].ToString();
x.applicant_address = reader["address"].ToString();
x.applicant_xemail = reader["xemail"].ToString();
x.applicant_xmobile = reader["xmobile"].ToString();
x.tech_amt = reader["tech_amt"].ToString();
x.payment_ref = reader["pay_ref"].ToString();
x.Qty = reader["Qty"].ToString();
x.Cld_Fees = reader["Cld_Fees"].ToString();
x.item_code = reader["item_code"].ToString();
x.item_description = reader["xdesc"].ToString();
// x.used_status = reader["used_status"].ToString();
xlist.Add(x);
sn++;
}
reader.Close();
return xlist;
}