protected void ASPxGridView1_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e) { string filter = e.Parameters; string[] _filter = filter.Split(','); string p = _filter[0]; ASPxTextBox oidCtr = this.ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox; ASPxTextBox docNoCtr = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox; ASPxComboBox docType1 = this.ASPxGridView1.FindEditFormTemplateControl("cbo_DocType1") as ASPxComboBox; //get informations from arinvoice if (p.ToUpper() == "P") { #region Post if (docNoCtr.Text.Trim().Length < 6) { e.Result = "Please Generate No First !"; return; } string sql = @"SELECT AcYear, AcPeriod, DocType, DocNo, DocDate, CurrencyId, ExRate, PartyTo, OtherPartyName, DocAmt, LocAmt, AcCode, AcSource, ChqNo, ChqDate, CloseInd, ExportInd, BankRec, BankDate, Remark FROM XAApPayment"; 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 partyTo = ""; string otherPartyName = ""; string remarks = ""; 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]["ExRate"].ToString(), 0); currency = dt.Rows[0]["CurrencyId"].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 Payment!"; return; } string sqlDet = string.Format("select count(SequenceId) from XAApPaymentDet where PayId='{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 XAApPaymentDet where PayId='{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 payment 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; } sql = string.Format("select sum(locamt) from XAApPaymentDet where (AcSource = 'DB') AND (PayId = '{0}')", oidCtr.Text); decimal amt_detDb = SafeValue.SafeDecimal(Manager.ORManager.ExecuteScalar(sql), 0); sql = string.Format("select sum(locamt) from XAApPaymentDet where(AcSource = 'CR') AND (PayId = '{0}')", oidCtr.Text); decimal amt_detCr = SafeValue.SafeDecimal(Manager.ORManager.ExecuteScalar(sql), 0); if (locAmt != amt_detDb - amt_detCr) { e.Result = "Amount can't match, can't post"; return; } sql = "select count(*) from XAApPaymentDet where AcCode='' and PayId='" + 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 = "AP"; gl.DocType = docType; gl.DocDate = docDt; gl.DocNo = docN; gl.CrAmt = docAmt; gl.CurrencyCrAmt = locAmt; gl.DbAmt = docAmt; 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(XAApPaymentDet), "PayId='" + oidCtr.Text + "'"); ObjectSet set = Manager.ORManager.GetObjectSet(query); int index = 1; XAGlEntryDet det1 = new XAGlEntryDet(); det1.AcCode = acCode; det1.ArApInd = "AP"; det1.AcPeriod = acPeriod; det1.AcSource = acSource; det1.AcYear = acYear; det1.CrAmt = docAmt; det1.CurrencyCrAmt = locAmt; det1.DbAmt = 0; det1.CurrencyDbAmt = 0; 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++; XAApPaymentDet invDet = set[i] as XAApPaymentDet; XAGlEntryDet det = new XAGlEntryDet(); det.AcCode = invDet.AcCode; det.ArApInd = "AP"; det.AcPeriod = acPeriod; det.AcSource = invDet.AcSource; det.AcYear = acYear; if (invDet.AcSource == "DB") { det.CrAmt = 0; det.CurrencyCrAmt = 0; det.DbAmt = invDet.DocAmt; det.CurrencyDbAmt = invDet.LocAmt; } else if (invDet.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); } } UpdateApPayment(oidCtr.Text); e.Result = "Post completely!"; } catch { e.Result = "Posting Error, Please repost!"; DeleteGl(glOid); } #endregion } else if (p == "DD") { #region delete detail try { string sql = "SELECT SequenceId,DocId, DocType FROM XAApPaymentDet where PayId='" + 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 XAApPaymentDet 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 string sql = string.Format("select sum(case when AcSource='DB' then -LocAmt else LocAmt end) As LocAmt from XAApPaymentDet where PayId='{0}'", oidCtr.Text); decimal balAmt = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql), 0); string gainAccCode = System.Configuration.ConfigurationManager.AppSettings["GainLoseAcCode"]; if (balAmt > 0) { XAApPaymentDet det = new XAApPaymentDet(); 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 = "PS"; det.ExRate = 1; det.LocAmt = balAmt; det.Remark1 = "GAIN AND LOSS"; det.Remark2 = ""; det.Remark3 = ""; det.PayId = SafeValue.SafeInt(oidCtr.Text, 0); ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox; det.PayNo = docN.Text; string sql_detCnt = "select count(DocNo) from XAApPaymentDet where PayId='" + oidCtr.Text + "'"; int lineNo = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql_detCnt), 0) + 1; det.PayLineNo = lineNo; det.PayType = "PS"; C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted); C2.Manager.ORManager.PersistChanges(det); } else if (balAmt < 0) { XAApPaymentDet det = new XAApPaymentDet(); 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 = "PS"; det.ExRate = 1; det.LocAmt = -balAmt; det.Remark1 = "GAIN AND LOSS"; det.Remark2 = ""; det.Remark3 = ""; det.PayId = SafeValue.SafeInt(oidCtr.Text, 0); ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox; det.PayNo = docN.Text; string sql_detCnt = "select count(DocNo) from XAApPaymentDet where PayId='" + oidCtr.Text + "'"; int lineNo = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql_detCnt), 0) + 1; det.PayLineNo = lineNo; det.PayType = "PS"; C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted); C2.Manager.ORManager.PersistChanges(det); } #endregion } else if (filter == "Generate") { string invN = C2Setup.GetNextNo("AP-PAYMENT-" + docType1.Text); string sql = string.Format("Update XAApPayment set DocNo='{1}',GenerateInd='Y' where SequenceId='{0}'", oidCtr.Text, invN); if (ConnectSql.ExecuteSql(sql) > -1) { C2Setup.SetNextNo("AP-PAYMENT-" + docType1.Text, invN); } e.Result = invN; } }
protected void ASPxGridView1_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e) { if (Request.QueryString["partyTo"] != null && Request.QueryString["no"] != null) { string payId = Request.QueryString["no"].ToString(); //throw new Exception(list.Count.ToString()); for (int i = 0; i < list.Count; i++) { try { int docId = list[i].docId; decimal payAmt = list[i].payAmt; string sql = string.Format("SELECT SequenceId,DocAmt,LocAmt, DocDate, AcCode, AcSource, DocType,DocNo, PartyTo, CurrencyId, ExRate FROM XAApPayment where SequenceId='{0}'", docId); DataTable tab = Helper.Sql.List(sql); 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"], 0); DateTime docDate = SafeValue.SafeDate(tab.Rows[0]["DocDate"], DateTime.Now); string partyTo = tab.Rows[0]["PartyTo"].ToString(); string oid = tab.Rows[0]["SequenceId"].ToString(); decimal billDocAmt = SafeValue.SafeDecimal(tab.Rows[0]["DocAmt"], 0); decimal billLocAmt = SafeValue.SafeDecimal(tab.Rows[0]["LocAmt"], 0); C2.XAApPaymentDet repDet = new XAApPaymentDet(); repDet.AcCode = acCode; repDet.AcSource = "DB"; repDet.Currency = currency; repDet.DocAmt = payAmt; repDet.LocAmt = payAmt; repDet.DocDate = docDate; repDet.DocId = 0; repDet.DocNo = docNo; repDet.DocType = docType; repDet.ExRate = exRate; repDet.MastType = "PC"; repDet.MastRefNo = docNo; repDet.PayLineNo = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar("select count(*) from XAApPaymentDet where PayId='" + payId + "'"), 0) + 1; repDet.PayId = SafeValue.SafeInt(payId, 0); repDet.PayNo = ""; repDet.PayType = ""; repDet.Remark1 = "Payment for CLAIM-" + docNo; repDet.Remark2 = "Payment made for AcCode -" + 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); } } catch (Exception ex) { throw new Exception(ex.Message + ex.StackTrace); } } } else { e.Result = "The amount must be match with the receipt amount!"; } }
protected void ASPxGridView1_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e) { 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 = Helper.Sql.List(sql); 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.XAApPaymentDet repDet = new XAApPaymentDet(); 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.PayLineNo = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar("select count(*) from XAApPaymentDet where PayId='" + repId + "'"), 0) + 1; repDet.PayId = repId; repDet.Remark1 = "Payment for " + docType + "-" + docNo; repDet.Remark2 = "Payment 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 { } } else { e.Result = "The total amount must be match with the receipt amount!"; } } else { e.Result = "Please keyin select party "; } }
protected void ASPxGridView1_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e) { if (Request.QueryString["partyTo"] != null && Request.QueryString["no"] != null) { string payId = Request.QueryString["no"].ToString(); //decimal locAmt = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar("select locAmt from XAApPayment where SequenceId='" + payId + "'"), 0); //decimal locAmt_det = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar("select sum(locAmt) from XAApPaymentDet where PayId='" + payId + "'"), 0); //locAmt = locAmt - locAmt_det; if (true)//locAmt == totPayAmt) { for (int i = 0; i < list.Count; i++) { try { int docId = list[i].docId; decimal payAmt = list[i].payAmt; string sql = string.Format("SELECT SequenceId,AcCode, AcSource, DocType,DocNo,DocDate,SupplierBillNo,SupplierBillDate,PartyTo, CurrencyId, ExRate,DocAmt,LocAmt,BalanceAmt FROM XAApPayable where SequenceId='{0}'", docId); DataTable tab = C2.Manager.ORManager.GetDataSet(sql).Tables[0]; if (tab.Rows.Count == 1) { string oid = tab.Rows[0]["SequenceId"].ToString(); 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"], 0); DateTime docDate = SafeValue.SafeDate(tab.Rows[0]["DocDate"], DateTime.Now); string partyTo = tab.Rows[0]["PartyTo"].ToString(); string supplierBillNo = tab.Rows[0]["SupplierBillNo"].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); DateTime supplierBillDate = SafeValue.SafeDate(tab.Rows[0]["SupplierBillDate"], DateTime.Now); C2.XAApPaymentDet repDet = new XAApPaymentDet(); repDet.AcCode = acCode; repDet.AcSource = "CR"; repDet.Currency = currency; repDet.DocAmt = payAmt; repDet.DocDate = docDate; repDet.DocId = docId; repDet.DocNo = docNo; repDet.DocType = docType; repDet.ExRate = exRate; if (exRate == 1) { repDet.LocAmt = payAmt * exRate; } else { if (payAmt == billDocAmt)// full payment { repDet.LocAmt = billLocAmt; } else if (payAmt == billBalaceAmt)//pay all outstanding amt { repDet.LocAmt = billLocAmt - PayLocAmt(oid); } else { repDet.LocAmt = payAmt * exRate;//partal payment } } repDet.PayLineNo = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar("select count(*) from XAApPaymentDet where PayId='" + payId + "'"), 0) + 1; repDet.PayId = SafeValue.SafeInt(payId, 0); repDet.PayNo = SafeValue.SafeString(Manager.ORManager.ExecuteScalar("select DocNo from xaappayable where sequenceid=" + payId), ""); repDet.PayType = "SR"; repDet.Remark1 = "Payment for SC-" + supplierBillNo; repDet.Remark2 = "Payment made for AcCode -" + 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); } } catch { } } } else { e.Result = "The amount must be match with the receipt amount!"; } } }