public int getARN_NO(int invoice_no)
{
int return_val = 0;
SQL = " select isnull(arn_no,0) as arn_no from import_hawb where elt_account_number = "
+ elt_account_number + " and invoice_no=" + invoice_no;
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(SQL, Con);
customerCreditRecord cCRec = new customerCreditRecord();
GeneralUtility gUtil = new GeneralUtility();
try
{
ad.Fill(dt);
if (dt.Rows.Count > 0)
{
return_val = Int32.Parse(dt.Rows[0]["arn_no"].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return(return_val);
}
public ArrayList getSalesPersonList()
{
ArrayList lstSalesPerson = new ArrayList();
SQL = "select code as person, description from all_code where elt_account_number = " + elt_account_number + " and type=22 order by code";
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(SQL, Con);
customerCreditRecord cCRec = new customerCreditRecord();
GeneralUtility gUtil = new GeneralUtility();
SalesPersonRecord spRec;
spRec = new SalesPersonRecord();
spRec.person = "";
spRec.description = "Select One";
lstSalesPerson.Add(spRec);
try
{
ad.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
spRec = new SalesPersonRecord();
spRec.person = dt.Rows[0]["person"].ToString();
spRec.description = dt.Rows[0]["description"].ToString();
lstSalesPerson.Add(spRec);
}
}
catch (Exception ex)
{
throw ex;
}
return(lstSalesPerson);
}
public Decimal getcustomerCredit(int customer_acct)
{
Decimal credit = 0;
Decimal debit = 0;
SQL = "select sum(isnull(credit_amount, 0)) as credit,sum(isnull(debit_amount, 0)) as debit"
+ " from all_accounts_journal where (tran_type='CCR'OR tran_type='PMT') AND gl_account_name='Customer Credit' and elt_account_number = "
+ elt_account_number + " and customer_number=" + customer_acct;
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(SQL, Con);
customerCreditRecord cCRec = new customerCreditRecord();
GeneralUtility gUtil = new GeneralUtility();
try
{
ad.Fill(dt);
gUtil.removeNull(ref dt);
if (dt.Rows.Count == 1)
{
credit = Decimal.Parse(dt.Rows[0]["credit"].ToString());
debit = Decimal.Parse(dt.Rows[0]["debit"].ToString());
}
else
{
credit = 0;
debit = 0;
}
}
catch (Exception ex2)
{
throw ex2;
}
return(-(credit + debit));
}
public int getNextEntryNo(int customer_acct)
{
int next_entry = 0;
SQL = "select max(entry_no) as entry_no from customer_credit_info where elt_account_number = " + elt_account_number + " and customer_no=" + customer_acct;
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(SQL, Con);
customerCreditRecord cCRec = new customerCreditRecord();
GeneralUtility gUtil = new GeneralUtility();
if (dt.Rows.Count > 0)
{
try
{
ad.Fill(dt);
next_entry = Int32.Parse(dt.Rows[0]["entry_no"].ToString());
}
catch (Exception ex)
{
throw ex;
}
}
return(next_entry + 1);
}
public ARNRecord getARNRecord(int invoice_no, string iType)
{
SQL = " select * from import_hawb where elt_account_number = "
+ elt_account_number + " and invoice_no=" + invoice_no + " and iType='" + iType + "'";
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(SQL, Con);
customerCreditRecord cCRec = new customerCreditRecord();
GeneralUtility gUtil = new GeneralUtility();
ARNRecord ARNRec = new ARNRecord();
try
{
ad.Fill(dt);
gUtil.removeNull(ref dt);
if (dt.Rows.Count > 0)
{
ARNRec.arr_code = dt.Rows[0]["arr_code"].ToString();
ARNRec.arr_port = dt.Rows[0]["arr_port"].ToString();
ARNRec.broker_info = dt.Rows[0]["broker_info"].ToString();
ARNRec.broker_name = dt.Rows[0]["broker_name"].ToString();
ARNRec.cargo_location = dt.Rows[0]["cargo_location"].ToString();
ARNRec.consignee_info = dt.Rows[0]["consignee_info"].ToString();
ARNRec.consignee_name = dt.Rows[0]["consignee_name"].ToString();
ARNRec.container_location = dt.Rows[0]["container_location"].ToString();
ARNRec.CreatedBy = dt.Rows[0]["CreatedBy"].ToString();
ARNRec.CreatedDate = dt.Rows[0]["CreatedDate"].ToString();
ARNRec.customer_ref = dt.Rows[0]["customer_ref"].ToString();
ARNRec.delivery_place = dt.Rows[0]["delivery_place"].ToString();
ARNRec.dep_code = dt.Rows[0]["dep_code"].ToString();
ARNRec.dep_port = dt.Rows[0]["dep_port"].ToString();
ARNRec.desc1 = dt.Rows[0]["desc1"].ToString();
ARNRec.desc2 = dt.Rows[0]["desc2"].ToString();
ARNRec.desc3 = dt.Rows[0]["desc3"].ToString();
ARNRec.desc4 = dt.Rows[0]["desc4"].ToString();
ARNRec.desc5 = dt.Rows[0]["desc5"].ToString();
ARNRec.destination = dt.Rows[0]["destination"].ToString();
ARNRec.eta = dt.Rows[0]["eta"].ToString();
ARNRec.eta2 = dt.Rows[0]["eta2"].ToString();
ARNRec.etd = dt.Rows[0]["etd"].ToString();
ARNRec.etd2 = dt.Rows[0]["etd2"].ToString();
ARNRec.flt_no = dt.Rows[0]["flt_no"].ToString();
ARNRec.free_date = dt.Rows[0]["free_date"].ToString();
ARNRec.go_date = dt.Rows[0]["go_date"].ToString();
ARNRec.hawb_num = dt.Rows[0]["hawb_num"].ToString();
ARNRec.igSub_HAWB = dt.Rows[0]["igSub_HAWB"].ToString();
ARNRec.is_default_rate = dt.Rows[0]["is_default_rate"].ToString();
ARNRec.is_org_merged = dt.Rows[0]["is_org_merged"].ToString();
ARNRec.it_date = dt.Rows[0]["it_date"].ToString();
ARNRec.it_entry_port = dt.Rows[0]["it_entry_port"].ToString();
ARNRec.it_number = dt.Rows[0]["it_number"].ToString();
ARNRec.iType = dt.Rows[0]["iType"].ToString();
ARNRec.mawb_num = dt.Rows[0]["mawb_num"].ToString();
ARNRec.ModifiedBy = dt.Rows[0]["ModifiedBy"].ToString();
ARNRec.ModifiedDate = dt.Rows[0]["ModifiedDate"].ToString();
ARNRec.notify_info = dt.Rows[0]["notify_info"].ToString();
ARNRec.notify_name = dt.Rows[0]["notify_name"].ToString();
ARNRec.prepaid_collect = dt.Rows[0]["prepaid_collect"].ToString();
ARNRec.prepared_by = dt.Rows[0]["prepared_by"].ToString();
ARNRec.process_dt = dt.Rows[0]["process_dt"].ToString();
ARNRec.processed = dt.Rows[0]["processed"].ToString();
ARNRec.remarks = dt.Rows[0]["remarks"].ToString();
ARNRec.SalesPerson = dt.Rows[0]["SalesPerson"].ToString();
ARNRec.scale1 = dt.Rows[0]["scale1"].ToString();
ARNRec.shipper_info = dt.Rows[0]["shipper_info"].ToString();
ARNRec.shipper_name = dt.Rows[0]["shipper_name"].ToString();
ARNRec.sub_mawb1 = dt.Rows[0]["sub_mawb1"].ToString();
ARNRec.sub_mawb2 = dt.Rows[0]["sub_mawb2"].ToString();
ARNRec.tran_dt = dt.Rows[0]["tran_dt"].ToString();
ARNRec.uom = dt.Rows[0]["uom"].ToString();
ARNRec.scale2 = dt.Rows[0]["scale2"].ToString();
ARNRec.pickup_date = dt.Rows[0]["pickup_date"].ToString();
ARNRec.term = Int32.Parse(dt.Rows[0]["term"].ToString());
ARNRec.agent_elt_acct = Int32.Parse(dt.Rows[0]["agent_elt_acct"].ToString());
ARNRec.agent_org_acct = Int32.Parse(dt.Rows[0]["agent_org_acct"].ToString());
//ARNRec.arn_no = Int32.Parse(dt.Rows[0]["arn_no"].ToString());
ARNRec.broker_acct = Int32.Parse(dt.Rows[0]["broker_acct"].ToString());
ARNRec.consignee_acct = Int32.Parse(dt.Rows[0]["consignee_acct"].ToString());
ARNRec.invoice_no = Int32.Parse(dt.Rows[0]["invoice_no"].ToString());
ARNRec.shipper_acct = Int32.Parse(dt.Rows[0]["shipper_acct"].ToString());
ARNRec.sec = Int32.Parse(dt.Rows[0]["sec"].ToString());
ARNRec.notify_acct = Int32.Parse(dt.Rows[0]["notify_acct"].ToString());
ARNRec.chg_wt = Decimal.Parse(dt.Rows[0]["chg_wt"].ToString());
ARNRec.fc_charge = Decimal.Parse(dt.Rows[0]["fc_charge"].ToString());
ARNRec.fc_rate = Decimal.Parse(dt.Rows[0]["fc_rate"].ToString());
ARNRec.freight_collect = Decimal.Parse(dt.Rows[0]["freight_collect"].ToString());
ARNRec.gross_wt = Decimal.Parse(dt.Rows[0]["gross_wt"].ToString());
ARNRec.oc_collect = Decimal.Parse(dt.Rows[0]["oc_collect"].ToString());
ARNRec.pieces = Decimal.Parse(dt.Rows[0]["pieces"].ToString());
ARNRec.total_other_charge = Decimal.Parse(dt.Rows[0]["total_other_charge"].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return(ARNRec);
}
public bool insert_customer_credit(customerCreditRecord ccRec)//农贰调老 版快/Refund 老 版快
{
int return_val;
ccRec.replaceQuote();
ArrayList AAJEntryList = ccRec.all_accounts_journal_list;
if (AAJEntryList == null)
{
AAJEntryList = new ArrayList();
}
for (int i = 0; i < AAJEntryList.Count; i++)
{
AAJMgr.checkInitial_Acct_Record((AllAccountsJournalRecord)AAJEntryList[i]);
}
int next_tran_seq_no = AAJMgr.getNextTranSeqNumber();
int entry_no = getNextEntryNo(ccRec.customer_no);
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
if (AAJEntryList.Count > 0)
{
for (int i = 0; i < AAJEntryList.Count; i++)
{
((AllAccountsJournalRecord)AAJEntryList[i]).replaceQuote();
if (((AllAccountsJournalRecord)AAJEntryList[i]).tran_num == null || ((AllAccountsJournalRecord)AAJEntryList[i]).tran_num == 0)//Only when there is no entry
{
((AllAccountsJournalRecord)AAJEntryList[i]).tran_num = entry_no;
}
SQL = "INSERT INTO [all_accounts_journal] ";
SQL += "( elt_account_number, ";
SQL += "tran_num,";
SQL += "gl_account_number,";
SQL += "gl_account_name,";
SQL += "tran_seq_num,";
SQL += "tran_type,";
SQL += "tran_date,";
SQL += "Customer_Number,";
SQL += "Customer_Name,";
SQL += "debit_amount,";
SQL += "credit_amount,";
SQL += "balance,";
SQL += "previous_balance,";
SQL += "gl_balance,";
SQL += "gl_previous_balance)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_num;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_account_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_account_name;
SQL += "','" + next_tran_seq_no++;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_type;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_date;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).customer_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).customer_name;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).debit_amount;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).credit_amount;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).previous_balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_previous_balance;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
}
SQL = "INSERT INTO [customer_credit_info] ";
SQL += "(elt_account_number, ";
SQL += "customer_no,";
SQL += "customer_name,";
SQL += "tran_date,";
SQL += "memo,";
SQL += "entry_no,";
SQL += "ref_no,";
SQL += "invoice_no,";
SQL += "credit)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + ccRec.customer_no;
SQL += "','" + ccRec.customer_name;
SQL += "','" + ccRec.tran_date;
SQL += "','" + ccRec.memo;
SQL += "','" + entry_no;
SQL += "','" + ccRec.ref_no;
SQL += "','" + ccRec.invoice_no;
SQL += "','" + ccRec.credit;
SQL += "')";
Cmd.CommandText = SQL;
return_val = Cmd.ExecuteNonQuery();
try
{
trans.Commit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
Con.Close();
}
if (return_val == 1)
{
return(true);
}
else
{
return(false);
}
}
