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; }