public Response GetAmigoDetailByRunDate(string dtmID)
{
try
{
RECEIPT_DETAILS oRecpt = new RECEIPT_DETAILS(Properties.Settings.Default.MyConnection);
string strMessage = "";
DataTable dt = oRecpt.GetAmigoDetailByRunDate(dtmID, out strMessage);
if (dt.Rows.Count > 0)
{
response.Status = 1;
//add column for BILL_COMPANY_NAME
dt.Columns.Add("BILL_COMPANY_NAME", typeof(System.String));
CUSTOMER_MASTER oCustomer = new CUSTOMER_MASTER(Properties.Settings.Default.MyConnection);
DataTable dtCustomer = oCustomer.getBillBankAccounts();
for (int i = 0; i < dt.Rows.Count; i++)//loop through Amigo Detail
{
//Customer Name from Receipt Detail
string CustomerName = dt.Rows[i]["CUSTOMER_NAME"] == null ? "" : dt.Rows[i]["CUSTOMER_NAME"].ToString();
//Bill Company Name from Customer Master
//Search with Customer Name
DataRow result = dtCustomer
.AsEnumerable()
.Where(myRow => (
(myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-1") == null ? null :myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-1").Trim()) == CustomerName ||
(myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-2") == null ? null : myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-2").Trim()) == CustomerName ||
(myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-3") == null ? null : myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-3").Trim()) == CustomerName ||
(myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-4") == null ? null : myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-4").Trim()) == CustomerName) &&
myRow.Field <DateTime>("EFFECTIVE_DATE") < DateTime.Now)
.OrderByDescending(s => s.Field <DateTime>("EFFECTIVE_DATE")).ThenByDescending(s => s.Field <int>("REQ_SEQ"))
.FirstOrDefault();
if (result != null)
{
//append BILL COMPANY NAME
dt.Rows[i]["BILL_COMPANY_NAME"] = result["BILL_COMPANY_NAME"].ToString();
}
}
}
else
{
if (strMessage == "")
{
response.Status = 1;
response.Message = "There is no data to display.";
}
else
{
response.Status = 0;
response.Message = strMessage;
}
}
response.Data = Utility.Utility_Component.DtToJSon(dt, "32Result");
return(response);
}
catch (Exception ex)
{
response.Status = 0;
response.Message = ex.Message + "\n" + ex.StackTrace;
return(response);
}
}
protected void seperateAmigo_NonAmigo()
{
CUSTOMER_MASTER oCust = new CUSTOMER_MASTER(Properties.Settings.Default.MyConnection);
string run_date = DateTime.Now.ToString();
DataTable dtCustomer = oCust.getBillBankAccounts();
for (int i = 0; i < dtUploadData.Rows.Count; i++)
{
string strCustomerName = (dtUploadData.Rows[i]["CUSTOMER_NAME"] != null ? dtUploadData.Rows[i]["CUSTOMER_NAME"].ToString().Trim() : "");
var query = dtCustomer.AsEnumerable().Where
(r => (r.Field <string>("BILL_BANK_ACCOUNT_NAME-1") == null ? "" : r.Field <string>("BILL_BANK_ACCOUNT_NAME-1").Trim()) == strCustomerName ||
(r.Field <string>("BILL_BANK_ACCOUNT_NAME-2") == null ? "" : r.Field <string>("BILL_BANK_ACCOUNT_NAME-2").Trim()) == strCustomerName ||
(r.Field <string>("BILL_BANK_ACCOUNT_NAME-3") == null ? "" : r.Field <string>("BILL_BANK_ACCOUNT_NAME-3").Trim()) == strCustomerName ||
(r.Field <string>("BILL_BANK_ACCOUNT_NAME-4") == null ? "" : r.Field <string>("BILL_BANK_ACCOUNT_NAME-4").Trim()) == strCustomerName
);
if (query.Any()) //Amigo
{
BOL_RECEIPT_DETAILS oReceipt = new BOL_RECEIPT_DETAILS();
oReceipt.DATA_CLASS = 1;
oReceipt.RECORD_CLASS = (dtUploadData.Rows[i]["RECORD_CLASS"] != null ? dtUploadData.Rows[i]["RECORD_CLASS"].ToString() : "");
oReceipt.TRANSACTION_DATE = Utility_Component.dtColumnToDateTime(dtUploadData.Rows[i]["TRANSACTION_DATE"] != null ? dtUploadData.Rows[i]["TRANSACTION_DATE"].ToString() : "");
oReceipt.TRANSACTION_CONTACT_NAME = (dtUploadData.Rows[i]["TRANSACTION_CONTACT_NAME"] != null ? dtUploadData.Rows[i]["TRANSACTION_CONTACT_NAME"].ToString() : "");
oReceipt.TRANSACTION_BANKS_NAME = (dtUploadData.Rows[i]["TRANSACTION_BANKS_NAME"] != null ? dtUploadData.Rows[i]["TRANSACTION_BANKS_NAME"].ToString() : "");
oReceipt.TRANSACTION_BRANCH_NAME = (dtUploadData.Rows[i]["TRANSACTION_BRANCH_NAME"] != null ? dtUploadData.Rows[i]["TRANSACTION_BRANCH_NAME"].ToString() : "");
oReceipt.TRANSACTION_ACCOUNT_NO_CLASS = (dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_NO_CLASS"] != null ? dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_NO_CLASS"].ToString() : "");
oReceipt.TRANSACTION_ACCOUNT_TYPE = (dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_TYPE"] != null ? dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_TYPE"].ToString() : "");
oReceipt.TRANSACTION_ACCOUNT_NO = (dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_NO"] != null ? dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_NO"].ToString() : "");
oReceipt.RESEND_INDICATION = (dtUploadData.Rows[i]["RESEND_INDICATION"] != null ? dtUploadData.Rows[i]["RESEND_INDICATION"].ToString() : "");
oReceipt.TRANSACTION_NAME = (dtUploadData.Rows[i]["TRANSACTION_NAME"] != null ? dtUploadData.Rows[i]["TRANSACTION_NAME"].ToString() : "");
oReceipt.TRANSACTION_NO = (dtUploadData.Rows[i]["TRANSACTION_NO"] != null ? dtUploadData.Rows[i]["TRANSACTION_NO"].ToString() : "");
oReceipt.TRANSACTION_DETAIL_CLASS = (dtUploadData.Rows[i]["TRANSACTION_DETAIL_CLASS"] != null ? dtUploadData.Rows[i]["TRANSACTION_DETAIL_CLASS"].ToString() : "");
oReceipt.HANDLING_DATE = Utility_Component.dtColumnToDateTime(dtUploadData.Rows[i]["HANDLING_DATE"] != null ? dtUploadData.Rows[i]["HANDLING_DATE"].ToString() : "");
oReceipt.DEPOSIT_DATE = Utility_Component.dtColumnToDateTime(dtUploadData.Rows[i]["DEPOSIT_DATE"] != null ? dtUploadData.Rows[i]["DEPOSIT_DATE"].ToString() : "");
oReceipt.DEPOSIT_AMOUNT = Utility_Component.dtColumnToDecimal(dtUploadData.Rows[i]["DEPOSIT_AMOUNT"] != null ? dtUploadData.Rows[i]["DEPOSIT_AMOUNT"].ToString() : "");
oReceipt.CHECK_CLASS = (dtUploadData.Rows[i]["CHECK_CLASS"] != null ? dtUploadData.Rows[i]["CHECK_CLASS"].ToString() : "");
oReceipt.CUSTOMER_NAME = (dtUploadData.Rows[i]["CUSTOMER_NAME"] != null ? dtUploadData.Rows[i]["CUSTOMER_NAME"].ToString() : "");
oReceipt.COLLECTION_NO_SHEETS = Utility_Component.dtColumnToInt(dtUploadData.Rows[i]["COLLECTION_NO_SHEETS"] != null ? dtUploadData.Rows[i]["COLLECTION_NO_SHEETS"].ToString() : "");
oReceipt.COLLECTION_NO = (dtUploadData.Rows[i]["COLLECTION_NO"] != null ? dtUploadData.Rows[i]["COLLECTION_NO"].ToString() : "");
oReceipt.CUSTOMER_NO = (dtUploadData.Rows[i]["CUSTOMER_NO"] != null ? dtUploadData.Rows[i]["CUSTOMER_NO"].ToString() : "");
oReceipt.BANK_NAME = (dtUploadData.Rows[i]["BANK_NAME"] != null ? dtUploadData.Rows[i]["BANK_NAME"].ToString() : "");
oReceipt.BRANCH_NAME = (dtUploadData.Rows[i]["BRANCH_NAME"] != null ? dtUploadData.Rows[i]["BRANCH_NAME"].ToString() : "");
oReceipt.TRANSACTION_FILE_NAME = (dtUploadData.Rows[i]["TRANSACTION_FILE_NAME"] != null ? dtUploadData.Rows[i]["TRANSACTION_FILE_NAME"].ToString() : "");
oReceipt.TRANSFER_MESSAGE = (dtUploadData.Rows[i]["TRANSFER_MESSAGE"] != null ? dtUploadData.Rows[i]["TRANSFER_MESSAGE"].ToString() : "");
oReceipt.NOTE = (dtUploadData.Rows[i]["NOTE"] != null ? dtUploadData.Rows[i]["NOTE"].ToString() : "");
oReceipt.NUMBER = (!String.IsNullOrEmpty(dtUploadData.Rows[i]["NUMBER"].ToString()) ? int.Parse(dtUploadData.Rows[i]["NUMBER"].ToString()) : 0);
oReceipt.RUN_DATE_TIME = run_date;
oReceipt.RUN_RESULT = 1;
oReceipt.DATA_MOVEMENT_INFORMATION = "";
oReceipt.PAYMENT_DAY = null;
oReceipt.TYPE_OF_ALLOCATION = 0;
oReceipt.ALLOCATED_QUANTITY = 0;
oReceipt.ALLOCATED_MONEY = 0;
oReceipt.ALLOCATED_COMPLETION_DATE = null;
lstAmigo.Add(oReceipt);
}
else //Non Amigo
{
BOL_RECEIPT_DETAILS_NON_AMIGO oReceipt_NonAmigo = new BOL_RECEIPT_DETAILS_NON_AMIGO();
oReceipt_NonAmigo.DATA_CLASS = 1;
oReceipt_NonAmigo.RECORD_CLASS = (dtUploadData.Rows[i]["RECORD_CLASS"] != null ? dtUploadData.Rows[i]["RECORD_CLASS"].ToString() : "");
oReceipt_NonAmigo.TRANSACTION_DATE = Utility_Component.dtColumnToDateTime(dtUploadData.Rows[i]["TRANSACTION_DATE"] != null ? dtUploadData.Rows[i]["TRANSACTION_DATE"].ToString() : "");
oReceipt_NonAmigo.TRANSACTION_CONTACT_NAME = (dtUploadData.Rows[i]["TRANSACTION_CONTACT_NAME"] != null ? dtUploadData.Rows[i]["TRANSACTION_CONTACT_NAME"].ToString() : "");
oReceipt_NonAmigo.TRANSACTION_BANKS_NAME = (dtUploadData.Rows[i]["TRANSACTION_BANKS_NAME"] != null ? dtUploadData.Rows[i]["TRANSACTION_BANKS_NAME"].ToString() : "");
oReceipt_NonAmigo.TRANSACTION_BRANCH_NAME = (dtUploadData.Rows[i]["TRANSACTION_BRANCH_NAME"] != null ? dtUploadData.Rows[i]["TRANSACTION_BRANCH_NAME"].ToString() : "");
oReceipt_NonAmigo.TRANSACTION_ACCOUNT_NO_CLASS = (dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_NO_CLASS"] != null ? dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_NO_CLASS"].ToString() : "");
oReceipt_NonAmigo.TRANSACTION_ACCOUNT_TYPE = (dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_TYPE"] != null ? dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_TYPE"].ToString() : "");
oReceipt_NonAmigo.TRANSACTION_ACCOUNT_NO = (dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_NO"] != null ? dtUploadData.Rows[i]["TRANSACTION_ACCOUNT_NO"].ToString() : "");
oReceipt_NonAmigo.RESEND_INDICATION = (dtUploadData.Rows[i]["RESEND_INDICATION"] != null ? dtUploadData.Rows[i]["RESEND_INDICATION"].ToString() : "");
oReceipt_NonAmigo.TRANSACTION_NAME = (dtUploadData.Rows[i]["TRANSACTION_NAME"] != null ? dtUploadData.Rows[i]["TRANSACTION_NAME"].ToString() : "");
oReceipt_NonAmigo.TRANSACTION_NO = (dtUploadData.Rows[i]["TRANSACTION_NO"] != null ? dtUploadData.Rows[i]["TRANSACTION_NO"].ToString() : "");
oReceipt_NonAmigo.TRANSACTION_DETAIL_CLASS = (dtUploadData.Rows[i]["TRANSACTION_DETAIL_CLASS"] != null ? dtUploadData.Rows[i]["TRANSACTION_DETAIL_CLASS"].ToString() : "");
oReceipt_NonAmigo.HANDLING_DATE = Utility_Component.dtColumnToDateTime(dtUploadData.Rows[i]["HANDLING_DATE"] != null ? dtUploadData.Rows[i]["HANDLING_DATE"].ToString() : "");
oReceipt_NonAmigo.DEPOSIT_DATE = Utility_Component.dtColumnToDateTime(dtUploadData.Rows[i]["DEPOSIT_DATE"] != null ? dtUploadData.Rows[i]["DEPOSIT_DATE"].ToString() : "");
oReceipt_NonAmigo.DEPOSIT_AMOUNT = Utility_Component.dtColumnToDecimal(dtUploadData.Rows[i]["DEPOSIT_AMOUNT"] != null ? dtUploadData.Rows[i]["DEPOSIT_AMOUNT"].ToString() : "");
oReceipt_NonAmigo.CHECK_CLASS = (dtUploadData.Rows[i]["CHECK_CLASS"] != null ? dtUploadData.Rows[i]["CHECK_CLASS"].ToString() : "");
oReceipt_NonAmigo.CUSTOMER_NAME = (dtUploadData.Rows[i]["CUSTOMER_NAME"] != null ? dtUploadData.Rows[i]["CUSTOMER_NAME"].ToString() : "");
oReceipt_NonAmigo.COLLECTION_NO_SHEETS = Utility_Component.dtColumnToInt(dtUploadData.Rows[i]["COLLECTION_NO_SHEETS"] != null ? dtUploadData.Rows[i]["COLLECTION_NO_SHEETS"].ToString() : "");
oReceipt_NonAmigo.COLLECTION_NO = (dtUploadData.Rows[i]["COLLECTION_NO"] != null ? dtUploadData.Rows[i]["COLLECTION_NO"].ToString() : "");
oReceipt_NonAmigo.CUSTOMER_NO = (dtUploadData.Rows[i]["CUSTOMER_NO"] != null ? dtUploadData.Rows[i]["CUSTOMER_NO"].ToString() : "");
oReceipt_NonAmigo.BANK_NAME = (dtUploadData.Rows[i]["BANK_NAME"] != null ? dtUploadData.Rows[i]["BANK_NAME"].ToString() : "");
oReceipt_NonAmigo.BRANCH_NAME = (dtUploadData.Rows[i]["BRANCH_NAME"] != null ? dtUploadData.Rows[i]["BRANCH_NAME"].ToString() : "");
oReceipt_NonAmigo.TRANSACTION_FILE_NAME = (dtUploadData.Rows[i]["TRANSACTION_FILE_NAME"] != null ? dtUploadData.Rows[i]["TRANSACTION_FILE_NAME"].ToString() : "");
oReceipt_NonAmigo.TRANSFER_MESSAGE = (dtUploadData.Rows[i]["TRANSFER_MESSAGE"] != null ? dtUploadData.Rows[i]["TRANSFER_MESSAGE"].ToString() : "");
oReceipt_NonAmigo.NOTE = (dtUploadData.Rows[i]["NOTE"] != null ? dtUploadData.Rows[i]["NOTE"].ToString() : "");
oReceipt_NonAmigo.NUMBER = (!String.IsNullOrEmpty(dtUploadData.Rows[i]["NUMBER"].ToString()) ? int.Parse(dtUploadData.Rows[i]["NUMBER"].ToString()) : 0);
oReceipt_NonAmigo.RUN_DATE_TIME = run_date;
oReceipt_NonAmigo.RUN_RESULT = 99;
oReceipt_NonAmigo.DATA_MOVEMENT_INFORMATION = "";
oReceipt_NonAmigo.PAYMENT_DAY = null;
oReceipt_NonAmigo.TYPE_OF_ALLOCATION = 0;
oReceipt_NonAmigo.ALLOCATED_QUANTITY = 0;
oReceipt_NonAmigo.ALLOCATED_MONEY = 0;
oReceipt_NonAmigo.ALLOCATED_COMPLETION_DATE = null;
lstNonAmigo.Add(oReceipt_NonAmigo);
}
}
}
public Response GetDataGridfor36(string strFrom, string stringTo)
{
try
{
RECEIPT_DETAILS oRecpt = new RECEIPT_DETAILS(Properties.Settings.Default.MyConnection);
string strMessage = "";
DateTime dtmFrom = DateTime.ParseExact(strFrom, "yyyyMMdd", CultureInfo.InvariantCulture);
DateTime dtmTo = DateTime.ParseExact(stringTo, "yyyyMMdd", CultureInfo.InvariantCulture);
DataTable dt = oRecpt.GetDateFor36_Grid(dtmFrom, dtmTo, out strMessage);
if (dt.Rows.Count > 0)
{
response.Status = 1;
//append columns to RECEIPT DETAIL Results
dt.Columns.Add("BILL_COMPANY_NAME", typeof(System.String));
dt.Columns.Add("BILL_CONTACT_NAME", typeof(System.String));
dt.Columns.Add("BILL_PHONE_NUMBER", typeof(System.String));
dt.Columns.Add("BILL_MAIL_ADDRESS", typeof(System.String));
//Get Data from CUSTOMER MASTER
CUSTOMER_MASTER oCustomer = new CUSTOMER_MASTER(Properties.Settings.Default.MyConnection);
DataTable dtCustomer = oCustomer.getBillBankAccounts();
for (int i = 0; i < dt.Rows.Count; i++) //loop through Amigo Detail
{
string CustomerName = dt.Rows[i]["CUSTOMER_NAME"].ToString(); //Customer Name from Receipt Detail
//Search with Customer Name
DataRow result = dtCustomer
.AsEnumerable()
.Where(myRow => (
(myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-1") == null ? null : myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-1").Trim()) == CustomerName ||
(myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-2") == null ? null : myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-2").Trim()) == CustomerName ||
(myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-3") == null ? null : myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-3").Trim()) == CustomerName ||
(myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-4") == null ? null : myRow.Field <string>("BILL_BANK_ACCOUNT_NAME-4").Trim()) == CustomerName) &&
myRow.Field <DateTime>("EFFECTIVE_DATE") < DateTime.Now)
.OrderByDescending(s => s.Field <DateTime>("EFFECTIVE_DATE")).ThenByDescending(s => s.Field <int>("REQ_SEQ"))
.FirstOrDefault();
if (result != null)
{
//insert columns values from CUSTOMER MASTER to RECEIPT DETAIL Result
dt.Rows[i]["BILL_COMPANY_NAME"] = result["BILL_COMPANY_NAME"].ToString();
dt.Rows[i]["BILL_CONTACT_NAME"] = result["BILL_CONTACT_NAME"].ToString();
dt.Rows[i]["BILL_PHONE_NUMBER"] = result["BILL_PHONE_NUMBER"].ToString();
dt.Rows[i]["BILL_MAIL_ADDRESS"] = result["BILL_MAIL_ADDRESS"].ToString();
}
}
response.Data = Utility.Utility_Component.DtToJSon(dt, "36Result");
}
else
{
if (strMessage == "")
{
response.Status = 1;
response.Message = "There has no data to display.";
}
else
{
response.Status = 0;
response.Message = strMessage;
}
}
response.Data = Utility.Utility_Component.DtToJSon(dt, "36Result");
return(response);
}
catch (Exception ex)
{
response.Status = 0;
response.Message = ex.Message + "\n" + ex.StackTrace;
return(response);
}
}