protected void ASPxGridView1_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string filter = e.Parameters;
ASPxTextBox oidCtr = this.ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox;
//ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
if (filter == "P")
{
#region Post
string sql = @"SELECT AcYear, AcPeriod, DocType, DocNo, DocDate, DocCurrency, DocExRate, PartyTo, OtherPartyName, DocAmt, LocAmt, AcCode, AcSource, ChqNo, ChqDate, ExportInd,
BankName, Remark FROM XAArReceipt";
sql += " WHERE SequenceId='" + oidCtr.Text + "'";
DataTable dt = Helper.Sql.List(sql);
int acYear = 0;
int acPeriod = 0;
string docN = "";
string docType = "";
string acSource = "";
string acCode = "";
decimal locAmt = 0;
decimal docAmt = 0;
decimal exRate = 0;
string currency = "";
DateTime docDt = DateTime.Today;
string remarks = "";
string partyTo = "";
string otherPartyName = "";
string chqNo = "";
if (dt.Rows.Count == 1)
{
acYear = SafeValue.SafeInt(dt.Rows[0]["AcYear"], 0);
acPeriod = SafeValue.SafeInt(dt.Rows[0]["AcPeriod"], 0);
acSource = dt.Rows[0]["AcSource"].ToString();
acCode = dt.Rows[0]["AcCode"].ToString();
docN = dt.Rows[0]["DocNo"].ToString();
docType = dt.Rows[0]["DocType"].ToString();
locAmt = SafeValue.SafeDecimal(dt.Rows[0]["LocAmt"].ToString(), 0);
docAmt = SafeValue.SafeDecimal(dt.Rows[0]["DocAmt"].ToString(), 0);
exRate = SafeValue.SafeDecimal(dt.Rows[0]["DocExRate"].ToString(), 0);
currency = dt.Rows[0]["DocCurrency"].ToString();
docDt = SafeValue.SafeDate(dt.Rows[0]["DocDate"], new DateTime(1900, 1, 1));
// partyId = dt.Rows[0][""].ToString();
remarks = dt.Rows[0]["Remark"].ToString();
partyTo = dt.Rows[0]["PartyTo"].ToString();
otherPartyName = dt.Rows[0]["OtherPartyName"].ToString();
chqNo = dt.Rows[0]["ChqNo"].ToString();
}
else
{
e.Result = "Can't find the ArReceipt!";
return;
}
string sqlDet = string.Format("select count(SequenceId) from XAArReceiptDet where RepId='{0}'", oidCtr.Text);
int detCnt = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sqlDet), 0);
if (detCnt == 0)
{
e.Result = "No Detail, Can't Post";
return;
}
sqlDet = string.Format("select max(docDate) from XAArReceiptDet where RepId='{0}'", oidCtr.Text);
DateTime maxLineDocDate = SafeValue.SafeDate(Manager.ORManager.ExecuteScalar(sqlDet), new DateTime(1900, 1, 1));
if (maxLineDocDate > new DateTime(2000, 1, 1) && maxLineDocDate > docDt)
{
e.Result = "The bill's Date can't be greater than receipt date.";
return;
}
//check account period
if (acYear < 1 || acPeriod < 1)
{
e.Result = "Account year or Period Invalid!";
return;
}
string sql1 = "select CloseInd from XXAccPeriod where Year='" + acYear + "' and Period ='" + acPeriod + "'";
string closeInd = SafeValue.SafeString(Manager.ORManager.ExecuteScalar(sql1), "");
if (closeInd == "")
{
e.Result = "Can't find this account period!";
return;
}
else if (closeInd == "Y")
{
e.Result = "The account period is closed!";
return;
}
//mast.amt det.amt is match
sql = string.Format("select sum(locamt) from XAArReceiptDet where RepId='{0}' and AcSource='CR'", oidCtr.Text);
decimal amt_detCr = SafeValue.SafeDecimal(Manager.ORManager.ExecuteScalar(sql), 0);
sql = string.Format("select sum(locamt) from XAArReceiptDet where RepId='{0}' and AcSource='DB'", oidCtr.Text);
decimal amt_detDb = SafeValue.SafeDecimal(Manager.ORManager.ExecuteScalar(sql), 0);
if (locAmt != amt_detCr - amt_detDb)
{
e.Result = "Amount can't match, can't post";
return;
}
sql = "select count(*) from XAArReceiptDet where AcCode='' and RepId='" + oidCtr.Text + "'";
detCnt = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql), 0);
if (detCnt > 0)
{
e.Result = "Some Item's Accode is blank, pls check";
return;
}
//delete old post data
sql = string.Format("SELECT SequenceId from XAGlEntry WHERE DocNo='{0}' and DocType='{1}'", docN, docType);
int glOldOid = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql), 0);
if (glOldOid > 0)
{
DeleteGl(glOldOid);
}
//Insert into gl entry
int glOid = 0;
try
{
C2.XAGlEntry gl = new XAGlEntry();
gl.AcPeriod = acPeriod;
gl.AcYear = acYear;
gl.ArApInd = "AR";
gl.DocType = docType;
gl.DocDate = docDt;
gl.DocNo = docN;
gl.CrAmt = docAmt;
gl.DbAmt = docAmt;
gl.CurrencyCrAmt = locAmt;
gl.CurrencyDbAmt = locAmt;
gl.CurrencyId = currency;
gl.EntryDate = DateTime.Now;
gl.ExRate = exRate;
gl.PostDate = DateTime.Now;
gl.PostInd = "N";
gl.Remark = remarks;
gl.UserId = HttpContext.Current.User.Identity.Name;
gl.CancelInd = "N";
gl.CancelDate = new DateTime(1900, 1, 1);
gl.PartyTo = partyTo;
gl.OtherPartyName = otherPartyName;
gl.ChqNo = chqNo;
gl.SupplierBillNo = "";
gl.SupplierBillDate = new DateTime(1900, 1, 1);
Manager.ORManager.StartTracking(gl, InitialState.Inserted);
Manager.ORManager.PersistChanges(gl);
glOid = gl.SequenceId;
//insert Detail
OPathQuery query = new OPathQuery(typeof(XAArReceiptDet), "RepId='" + oidCtr.Text + "'");
ObjectSet set = Manager.ORManager.GetObjectSet(query);
int index = 1;
XAGlEntryDet det1 = new XAGlEntryDet();
det1.AcCode = acCode;
det1.ArApInd = "AR";
det1.AcPeriod = acPeriod;
det1.AcSource = acSource;
det1.AcYear = acYear;
det1.CrAmt = 0;
det1.CurrencyCrAmt = 0;
det1.DbAmt = docAmt;
det1.CurrencyDbAmt = locAmt;
det1.CurrencyId = currency;
det1.DocNo = docN;
det1.DocType = docType;
det1.ExRate = exRate;
det1.GlLineNo = index;
det1.GlNo = gl.SequenceId;
det1.Remark = remarks;
Manager.ORManager.StartTracking(det1, InitialState.Inserted);
Manager.ORManager.PersistChanges(det1);
for (int i = 0; i < set.Count; i++)
{
try
{
index++;
XAArReceiptDet invDet = set[i] as XAArReceiptDet;
XAGlEntryDet det = new XAGlEntryDet();
det.AcCode = invDet.AcCode;
det.ArApInd = "AR";
det.AcPeriod = acPeriod;
det.AcSource = invDet.AcSource;
det.AcYear = acYear;
if (invDet.AcSource == "DB")
{
det.DbAmt = invDet.DocAmt;
det.CurrencyDbAmt = invDet.LocAmt;
det.CrAmt = 0;
det.CurrencyCrAmt = 0;
}
else// if (det.AcSource == "CR")
{
det.CrAmt = invDet.DocAmt;
det.CurrencyCrAmt = invDet.LocAmt;
det.DbAmt = 0;
det.CurrencyDbAmt = 0;
}
det.CurrencyId = invDet.Currency;
det.DocNo = docN;
det.DocType = docType;
det.ExRate = invDet.ExRate;
det.GlLineNo = index;
det.GlNo = gl.SequenceId;
det.Remark = invDet.Remark1;
Manager.ORManager.StartTracking(det, InitialState.Inserted);
Manager.ORManager.PersistChanges(det);
}
catch
{
e.Result = "Posting Error, Please repost!";
DeleteGl(glOid);
}
}
UpdateArInv(oidCtr.Text);
e.Result = "Post completely!";
}
catch
{
e.Result = "Posting Error, Please repost!";
DeleteGl(glOid);
}
#endregion
}
else if (filter == "DD")
{
#region delete receipt
try
{
string sql = "SELECT SequenceId,DocId, DocType FROM XAArReceiptDet where RepId ='" + oidCtr.Text + "'";
DataTable tab = Helper.Sql.List(sql);
for (int i = 0; i < tab.Rows.Count; i++)
{
try
{
string sequenceId = tab.Rows[i]["SequenceId"].ToString();
string docId = tab.Rows[i]["DocId"].ToString();
string docType = tab.Rows[i]["DocType"].ToString();
string sql_det = "delete from XAArReceiptDet where SequenceId='" + sequenceId + "'";
if (C2.Manager.ORManager.ExecuteCommand(sql_det) > 0)
{
UpdateMaster(docId, docType);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
else if (filter == "GainLoss")
{
#region GAIN LOSS
ASPxComboBox docType = this.ASPxGridView1.FindEditFormTemplateControl("cbo_DocType") as ASPxComboBox;
string sql = string.Format("select (select LocAmt from XaArReceipt where sequenceId='{0}')-iSNULL(sum(case when AcSource='CR' then -LocAmt else LocAmt end),0) As LocAmt from xaarreceiptdet where repid='{0}'", oidCtr.Text);
if (docType.Text == "RE")
{
sql = string.Format("select (select LocAmt from XaArReceipt where sequenceId='{0}')-iSNULL(sum(case when AcSource='DB' then -LocAmt else LocAmt end),0) As LocAmt from xaarreceiptdet where repid='{0}'", oidCtr.Text);
}
decimal balAmt = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql), 0);
if (balAmt > 0)
{
string gainAccCode = System.Configuration.ConfigurationManager.AppSettings["GainLoseAcCode"];
XAArReceiptDet det = new XAArReceiptDet();
det.AcCode = gainAccCode;
det.AcSource = "CR";
det.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
det.DocAmt = balAmt;
det.DocDate = new DateTime(1900, 1, 1);
det.DocId = 0;
det.DocNo = "";
det.DocType = "RE";
det.ExRate = 1;
det.LocAmt = balAmt;
det.Remark1 = "GAIN AND LOSS";
det.Remark2 = "";
det.Remark3 = "";
det.RepId = SafeValue.SafeInt(oidCtr.Text, 0);
ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
det.RepNo = docN.Text;
string sql_detCnt = "select count(DocNo) from XAArReceiptDet where RepId='" + oidCtr.Text + "'";
int lineNo = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql_detCnt), 0) + 1;
det.RepLineNo = lineNo;
det.RepType = "RE";
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
else if (balAmt < 0)
{
string gainAccCode = System.Configuration.ConfigurationManager.AppSettings["GainLoseAcCode"];
XAArReceiptDet det = new XAArReceiptDet();
det.AcCode = gainAccCode;
det.AcSource = "DB";
det.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
det.DocAmt = -balAmt;
det.DocDate = new DateTime(1900, 1, 1);
det.DocId = 0;
det.DocNo = "";
det.DocType = "RE";
det.ExRate = 1;
det.LocAmt = -balAmt;
det.Remark1 = "GAIN AND LOSS";
det.Remark2 = "";
det.Remark3 = "";
det.RepId = SafeValue.SafeInt(oidCtr.Text, 0);
ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
det.RepNo = docN.Text;
string sql_detCnt = "select count(DocNo) from XAArReceiptDet where RepId='" + oidCtr.Text + "'";
int lineNo = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql_detCnt), 0) + 1;
det.RepLineNo = lineNo;
det.RepType = "RE";
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
#endregion
}
else if (filter == "Generate")
{
string invN = C2Setup.GetNextNo("AR-RECEIPT");
string sql = string.Format("Update XAArReceipt set DocNo='{1}',GenerateInd='Y' where SequenceId='{0}'", oidCtr.Text, invN);
if (ConnectSql.ExecuteSql(sql) > -1)
{
C2Setup.SetNextNo("AR-RECEIPT", invN);
}
e.Result = invN;
}
}
protected void ASPxGridView1_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
//throw new Exception("123");
if (Request.QueryString["partyTo"] != null && Request.QueryString["no"] != null)
{
int repId = SafeValue.SafeInt(Request.QueryString["no"], 0);
//decimal locAmt = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar("select locAmt from XAArReceipt where SequenceId='" + repId + "'"), 0);
//decimal locAmt_det = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar("select sum(locAmt) from XAArReceiptDet where RepId='" + repId + "'"), 0);
//locAmt = locAmt - locAmt_det;
if (true)
{
try
{
for (int i = 0; i < list.Count; i++)
{
string docId = list[i].docId;
decimal payAmt = list[i].payAmt;
string sql = string.Format("SELECT SequenceId,DocAmt,LocAmt,BalanceAmt,AcCode, AcSource, DocType,DocNo,DocDate,PartyTo,CurrencyId, ExRate FROM XAArInvoice where SequenceId='{0}'", docId);
DataTable tab = C2.Manager.ORManager.GetDataSet(sql).Tables[0];
if (tab.Rows.Count == 1)
{
string docNo = tab.Rows[0]["DocNo"].ToString();
string docType = tab.Rows[0]["DocType"].ToString();
string acCode = tab.Rows[0]["AcCode"].ToString();
string acSource = tab.Rows[0]["AcSource"].ToString();
string currency = tab.Rows[0]["CurrencyId"].ToString();
decimal exRate = SafeValue.SafeDecimal(tab.Rows[0]["ExRate"], 1);
if (exRate == 0)
{
exRate = 1;
}
DateTime docDate = SafeValue.SafeDate(tab.Rows[0]["DocDate"], DateTime.Now);
string oid = tab.Rows[0]["SequenceId"].ToString();
decimal billDocAmt = SafeValue.SafeDecimal(tab.Rows[0]["DocAmt"], 0);
decimal billBalaceAmt = SafeValue.SafeDecimal(tab.Rows[0]["BalanceAmt"], 0);
decimal billLocAmt = SafeValue.SafeDecimal(tab.Rows[0]["LocAmt"], 0);
C2.XAArReceiptDet repDet = new XAArReceiptDet();
repDet.AcCode = acCode;
if (acSource == "CR")
{
repDet.AcSource = "DB";
}
else
{
repDet.AcSource = "CR";
}
repDet.Currency = currency;
repDet.DocAmt = payAmt;// payAmt;
repDet.DocDate = docDate;
repDet.DocId = SafeValue.SafeInt(docId, 0);
repDet.DocNo = docNo;
repDet.DocType = docType;
repDet.ExRate = exRate;
if (exRate == 1)
{
repDet.LocAmt = SafeValue.ChinaRound(payAmt * exRate, 2);
}
else
{
if (payAmt == billDocAmt)// full payment
{
repDet.LocAmt = billLocAmt;
}
else if (payAmt == billBalaceAmt)//pay all outstanding amt
{
if (docType == "CN")
{
repDet.LocAmt = billLocAmt - PayLocAmt_cn(oid);
}
else
{
repDet.LocAmt = billLocAmt - PayLocAmt(oid);
}
}
else
{
repDet.LocAmt = SafeValue.ChinaRound(payAmt * exRate, 2);//partal payment
}
}
repDet.RepLineNo = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar("select count(*) from XAArReceiptDet where RepId='" + repId + "'"), 0) + 1;
repDet.RepId = repId;
repDet.Remark1 = "Receipt for " + docType + "-" + docNo;
repDet.Remark2 = "Receipt made for party -" + acCode;
repDet.Remark3 = " ";
C2.Manager.ORManager.StartTracking(repDet, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(repDet);
//update to doc
int res = UpdateBalance(docId, docType);
}
}
e.Result = "";
}
catch (Exception ex) { throw new Exception(ex.Message + ex.StackTrace); }
}
else
{
e.Result = "The total amount must be match with the receipt amount!";
}
}
else
{
e.Result = "Please keyin select party ";
}
}
