private void SavePayableDet(DataTable dt, string doNo, int docId) { if (dt.Rows.Count > 0) { XAApPayableDet det = null; for (int i = 0; i < dt.Rows.Count; i++) { det = new XAApPayableDet(); det.DocNo = doNo; det.DocType = "PL"; det.POlineId = SafeValue.SafeInt(dt.Rows[i]["Id"], 0); det.AcSource = "DB"; det.AcCode = ""; det.MastType = "WH"; det.MastRefNo = SafeValue.SafeString(dt.Rows[i]["PoNo"]); det.DocId = docId; det.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"]; det.Qty = SafeValue.SafeInt(dt.Rows[i]["Qty"], 0); det.Price = SafeValue.SafeDecimal(dt.Rows[i]["Price"]); det.SplitType = "SET"; det.Gst = SafeValue.SafeInt(dt.Rows[i]["Gst"], 0); det.GstType = dt.Rows[i]["GstType"].ToString(); det.DocAmt = SafeValue.SafeDecimal(dt.Rows[i]["DocAmt"]); det.LocAmt = SafeValue.SafeDecimal(dt.Rows[i]["LocAmt"]); det.GstAmt = SafeValue.SafeDecimal(dt.Rows[i]["GstAmt"]); det.ExRate = SafeValue.SafeDecimal(dt.Rows[i]["ExRate"]); try { C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted); C2.Manager.ORManager.PersistChanges(det); } catch { } } } }
protected void grid_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e) { if (Request.QueryString["id"] != null && Request.QueryString["no"] != null && Request.QueryString["typ"] != null) { string invNo = Request.QueryString["no"].ToString(); string arApInd = Request.QueryString["typ"].ToString().ToUpper(); int invId = SafeValue.SafeInt(Request.QueryString["id"], 0); string invId_old = e.Parameters; string refNo = ""; string jobNo = ""; string mastType = ""; string docType = ""; string arApInd_sch = this.cmb_ArAp.Text.ToUpper(); if (arApInd == "AR") { string sql_mast = string.Format("select DocType,MastRefNo,JobRefNo,MastType from XAArInvoice where SequenceId='{0}'", invId); DataTable tab_mast = Helper.Sql.List(sql_mast); if (tab_mast.Rows.Count == 1) { docType = SafeValue.SafeString(tab_mast.Rows[0]["DocType"]); refNo = SafeValue.SafeString(tab_mast.Rows[0]["MastRefNo"]); jobNo = SafeValue.SafeString(tab_mast.Rows[0]["JobRefNo"]); mastType = SafeValue.SafeString(tab_mast.Rows[0]["MastType"]); } sql_mast = ""; if (mastType == "SI") { sql_mast = string.Format("Select round(case when Weight/1000>volume then Weight/1000 else Volume end,3) FROM SeaImport where RefNo='{0}' and JobNo='{1}'", refNo, jobNo); } else if (mastType == "SE") { sql_mast = string.Format("Select round(case when Weight/1000>volume then Weight/1000 else Volume end,3) FROM SeaExport where RefNo='{0}' and JobNo='{1}'", refNo, jobNo); } else if (mastType.Length > 1 && mastType.Substring(0, 1) == "A") { sql_mast = string.Format("Select round(case when Weight/1000>volume then Weight/1000 else Volume end,3) FROM air_job where RefNo='{0}' and JobNo='{1}'", refNo, jobNo); } else if (mastType == "TPT" || mastType == "LH") { sql_mast = string.Format("Select round(case when wt/1000>m3 then wt/1000 else m3 end,3) FROM tpt_Job where JobNo='{0}'", refNo, jobNo); } decimal qty = 0; if (sql_mast.Length > 0) { qty = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql_mast), 0); } int index = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(string.Format("Select max(DocLineNo) FROM XAArInvoiceDet where DocId='{0}'", invId)), 0); #region ar pulling data for (int m = 0; m < list.Count; m++) { try { int sequenceId = list[m].docId; string sql = string.Format("select * from XaArInvoiceDet where SequenceId='{0}'", sequenceId); if (arApInd_sch == "AP") { sql = string.Format("select * from XaApPayableDet where SequenceId='{0}'", sequenceId); } DataTable tab = ConnectSql.GetTab(sql); for (int i = 0; i < tab.Rows.Count; i++) { string chgCode = tab.Rows[i]["ChgCode"].ToString(); string acCode = tab.Rows[i]["AcCode"].ToString(); string acSource = tab.Rows[i]["AcSource"].ToString(); string chgDes1 = tab.Rows[i]["ChgDes1"].ToString(); decimal price = SafeValue.SafeDecimal(tab.Rows[i]["Price"], 0); string unit = tab.Rows[i]["Unit"].ToString().ToUpper(); string currencyDes = tab.Rows[i]["Currency"].ToString(); decimal exRateDes = SafeValue.SafeDecimal(tab.Rows[i]["ExRate"], 0); decimal gst = SafeValue.SafeDecimal(tab.Rows[i]["Gst"], 0); string gstType = tab.Rows[i]["GstType"].ToString(); if (qty == 0) { qty = SafeValue.SafeInt(tab.Rows[i]["Qty"], 1); } XAArInvoiceDet det = new XAArInvoiceDet(); det.AcCode = GetArCodeByChgCode(chgCode); det.AcSource = "CR"; if (docType == "CN") { det.AcSource = "DB"; } det.ChgCode = chgCode; det.ChgDes1 = chgDes1; det.ChgDes2 = ""; det.ChgDes3 = ""; det.Currency = currencyDes; det.ExRate = exRateDes; det.Price = price; det.Qty = qty; det.Unit = unit; det.Gst = gst; det.GstType = gstType; decimal amt = SafeValue.ChinaRound(qty * price, 2); decimal gstAmt = SafeValue.ChinaRound(amt * gst, 2); decimal docAmt = amt + gstAmt; decimal locAmt = SafeValue.ChinaRound(docAmt * exRateDes, 2); det.GstAmt = gstAmt; det.DocAmt = docAmt; det.LocAmt = locAmt; det.DocId = invId; index++; det.DocLineNo = index; det.DocNo = invNo; det.DocType = docType; det.MastRefNo = refNo; det.JobRefNo = jobNo; det.MastType = mastType; det.SplitType = "SET"; C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted); C2.Manager.ORManager.PersistChanges(det); } UpdateArMaster(invId, docType); } catch (Exception ex) { } } #endregion } else { string sql_mast = string.Format("select DocType,MastRefNo,JobRefNo,MastType from XAApPayable where SequenceId='{0}'", invId); DataTable tab_mast = Helper.Sql.List(sql_mast); if (tab_mast.Rows.Count == 1) { docType = SafeValue.SafeString(tab_mast.Rows[0]["DocType"]); refNo = SafeValue.SafeString(tab_mast.Rows[0]["MastRefNo"]); jobNo = SafeValue.SafeString(tab_mast.Rows[0]["JobRefNo"]); mastType = SafeValue.SafeString(tab_mast.Rows[0]["MastType"]); } sql_mast = ""; if (mastType == "SI") { sql_mast = string.Format("Select round(case when Weight/1000>volume then Weight/1000 else Volume end,3) FROM SeaImport where RefNo='{0}' and JobNo='{1}'", refNo, jobNo); } else if (mastType == "SE") { sql_mast = string.Format("Select round(case when Weight/1000>volume then Weight/1000 else Volume end,3) FROM SeaExport where RefNo='{0}' and JobNo='{1}'", refNo, jobNo); } else if (mastType.Length > 1 && mastType.Substring(0, 1) == "A") { sql_mast = string.Format("Select round(case when Weight/1000>volume then Weight/1000 else Volume end,3) FROM air_job where RefNo='{0}' and JobNo='{1}'", refNo, jobNo); } else if (mastType == "TPT" || mastType == "LH") { sql_mast = string.Format("Select round(case when wt/1000>m3 then wt/1000 else m3 end,3) FROM tpt_Job where JobNo='{0}'", refNo, jobNo); } decimal qty = 0; if (sql_mast.Length > 0) { qty = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql_mast), 0); } int index = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(string.Format("Select max(DocLineNo) FROM XAApPayableDet where DocId='{0}'", invId)), 0); #region ap pulling data for (int m = 0; m < list.Count; m++) { try { int sequenceId = list[m].docId; string sql = string.Format("select * from XaArInvoiceDet where SequenceId='{0}'", sequenceId); if (arApInd_sch == "AP") { sql = string.Format("select * from XaApPayableDet where SequenceId='{0}'", sequenceId); } DataTable tab = ConnectSql.GetTab(sql); for (int i = 0; i < tab.Rows.Count; i++) { string chgCode = tab.Rows[i]["ChgCode"].ToString(); string acCode = tab.Rows[i]["AcCode"].ToString(); string acSource = tab.Rows[i]["AcSource"].ToString(); string chgDes1 = tab.Rows[i]["ChgDes1"].ToString(); decimal price = SafeValue.SafeDecimal(tab.Rows[i]["Price"], 0); string unit = tab.Rows[i]["Unit"].ToString().ToUpper(); string currencyDes = tab.Rows[i]["Currency"].ToString(); decimal exRateDes = SafeValue.SafeDecimal(tab.Rows[i]["ExRate"], 0); decimal gst = SafeValue.SafeDecimal(tab.Rows[i]["Gst"], 0); string gstType = tab.Rows[i]["GstType"].ToString(); if (qty == 0) { qty = SafeValue.SafeInt(tab.Rows[i]["Qty"], 1); } XAApPayableDet det = new XAApPayableDet(); det.AcCode = GetApCodeByChgCode(chgCode); det.AcSource = "DB"; if (docType == "SC") { det.AcSource = "CR"; } det.ChgCode = chgCode; det.ChgDes1 = chgDes1; det.ChgDes2 = ""; det.ChgDes3 = ""; det.Currency = currencyDes; det.ExRate = exRateDes; det.Price = price; det.Qty = qty; det.Unit = unit; det.Gst = gst; det.GstType = gstType; decimal amt = SafeValue.ChinaRound(qty * price, 2); decimal gstAmt = SafeValue.ChinaRound(amt * gst, 2); decimal docAmt = amt + gstAmt; decimal locAmt = SafeValue.ChinaRound(docAmt * exRateDes, 2); det.GstAmt = gstAmt; det.DocAmt = docAmt; det.LocAmt = locAmt; det.DocId = invId; index++; det.DocLineNo = index; det.DocNo = invNo; det.DocType = docType; det.MastRefNo = refNo; det.JobRefNo = jobNo; det.MastType = mastType; det.SplitType = "SET"; C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted); C2.Manager.ORManager.PersistChanges(det); } UpdateApMaster(invId, docType); } catch (Exception ex) { } } #endregion } } else { e.Result = "Error, Pls refresh your invoice"; } }
protected void ASPxGridView1_CustomDataCallback1(object sender, ASPxGridViewCustomDataCallbackEventArgs e) { string filter = e.Parameters; string[] _filter = filter.Split(','); string p = _filter[0]; #region Post //get informations from arinvoice ASPxTextBox oidCtr = this.ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox; if (p.ToUpper() == "P") { string sql = @"SELECT AcYear, AcPeriod, AcCode, AcSource, DocType, DocNo, DocDate, PartyTo, OtherPartyName, MastType, CurrencyId, ExRate, Term, Description, LocAmt, DocAmt,ChqNo as SupplierBillNo, ChqDate as SupplierBillDate FROM XAApPayable"; 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 supplierBillNo = ""; DateTime supplierBillDate = new DateTime(1900, 1, 1); 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)); remarks = dt.Rows[0]["Description"].ToString(); partyTo = dt.Rows[0]["PartyTo"].ToString(); otherPartyName = SafeValue.SafeString(dt.Rows[0]["OtherPartyName"], ""); supplierBillNo = dt.Rows[0]["SupplierBillNo"].ToString(); supplierBillDate = SafeValue.SafeDate(dt.Rows[0]["SupplierBillDate"], new DateTime(1900, 1, 1)); } else { e.Result = "Can't find the Voucher!"; return; } string sqlDet = string.Format("select count(SequenceId) from XAApPayableDet where DocId='{0}'", oidCtr.Text); int detCnt = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sqlDet), 0); if (detCnt == 0) { e.Result = "No Detail, Can't Post"; 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 = "select SUM(LocAmt) from XAApPayableDet where AcSource='DB' and DocId='" + oidCtr.Text + "'"; decimal amt_det = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql), 0); sql = "select SUM(LocAmt) from XAApPayableDet where AcSource='CR' and DocId='" + oidCtr.Text + "'"; amt_det -= SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql), 0); if (docAmt != amt_det) { e.Result = "Loc Amount can't match, can't post,Please first resave it,"; return; } sql = "select count(LocAmt) from XAApPayableDet where AcCode='' and DocId='" + 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.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 = supplierBillNo; gl.ChqDate = supplierBillDate; 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(XAApPayableDet), "DocId='" + 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); decimal gstCrAmt = 0; decimal gstDbAmt = 0; string gstAcc = SafeValue.SafeString(Manager.ORManager.ExecuteScalar("SELECT AcCode FROM XXGstAccount where GstSrc='AP'"), "4053"); for (int i = 0; i < set.Count; i++) { try { index++; XAApPayableDet invDet = set[i] as XAApPayableDet; XAGlEntryDet det = new XAGlEntryDet(); if (invDet.AcCode == gstAcc) { if (invDet.AcSource == "DB") { gstDbAmt += invDet.LocAmt; } else { gstCrAmt += invDet.LocAmt; } } else { 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 = SafeValue.ChinaRound(SafeValue.ChinaRound(invDet.Qty * invDet.Price, 2) * invDet.ExRate, 2); det.CurrencyDbAmt = SafeValue.ChinaRound(det.DbAmt * exRate, 2); gstDbAmt += invDet.GstAmt; } else { det.DbAmt = 0; det.CurrencyDbAmt = 0; det.CrAmt = SafeValue.ChinaRound(SafeValue.ChinaRound(invDet.Qty * invDet.Price, 2) * invDet.ExRate, 2); det.CurrencyCrAmt = SafeValue.ChinaRound(det.CrAmt * exRate, 2); gstCrAmt += invDet.GstAmt; } det.CurrencyId = invDet.Currency; det.DocNo = docN; det.DocType = docType; det.ExRate = invDet.ExRate; det.GlLineNo = index; det.GlNo = gl.SequenceId; det.Remark = invDet.ChgCode; Manager.ORManager.StartTracking(det, InitialState.Inserted); Manager.ORManager.PersistChanges(det); } } catch { e.Result = "Posting Error, Please repost!"; DeleteGl(glOid); } } if (gstDbAmt - gstCrAmt != 0) { XAGlEntryDet det = new XAGlEntryDet(); det.AcCode = gstAcc; det.ArApInd = "AP"; det.AcPeriod = acPeriod; det.AcSource = "DB"; det.AcYear = acYear; det.DbAmt = gstDbAmt - gstCrAmt; det.CurrencyDbAmt = gstDbAmt - gstCrAmt; det.CrAmt = 0; det.CurrencyCrAmt = 0; det.CurrencyId = System.Configuration.ConfigurationManager.AppSettings["Currency"]; det.DocNo = docN; det.DocType = docType; det.ExRate = 1; det.GlLineNo = index + 1; det.GlNo = gl.SequenceId; det.Remark = "GST"; Manager.ORManager.StartTracking(det, InitialState.Inserted); Manager.ORManager.PersistChanges(det); } UpdateArInv(oidCtr.Text); e.Result = "Post completely!"; } catch { e.Result = "Posting Error, Please repost!"; DeleteGl(glOid); } } else if (p == "V") { string sql = string.Format(@"SELECT count(*) FROM XAApPaymentDet WHERE (DocId = '{0}')", oidCtr.Text); int cnt = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql), 0); if (cnt > 0) { e.Result = "Have Pay this bill, can't void it."; return; } else { sql = string.Format("update XAApPayable set CancelInd='Y',Description=Description+' Void by {1}' where Sequenceid='{0}'", oidCtr.Text, HttpContext.Current.User.Identity.Name); int res = Manager.ORManager.ExecuteCommand(sql); if (res > 0) { e.Result = "Success"; } else { e.Result = "Fail"; } } } #endregion }
protected void grid_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e) { if (Request.QueryString["id"] != null && Request.QueryString["no"] != null && Request.QueryString["typ"] != null) { string invNo = Request.QueryString["no"].ToString(); string arApInd = Request.QueryString["typ"].ToString().ToUpper(); int invId = SafeValue.SafeInt(Request.QueryString["id"], 0); string invId_old = e.Parameters; string refNo = ""; string jobNo = ""; string mastType = ""; string docType = ""; string arApInd_sch = this.cmb_ArAp.Text.ToUpper(); if (arApInd == "AR") { string sql_mast = string.Format("select DocType,MastRefNo,JobRefNo,MastType from XAArInvoice where SequenceId='{0}'", invId); DataTable tab_mast = C2.Manager.ORManager.GetDataSet(sql_mast).Tables[0]; if (tab_mast.Rows.Count == 1) { docType = SafeValue.SafeString(tab_mast.Rows[0]["DocType"]); refNo = SafeValue.SafeString(tab_mast.Rows[0]["MastRefNo"]); jobNo = SafeValue.SafeString(tab_mast.Rows[0]["JobRefNo"]); mastType = SafeValue.SafeString(tab_mast.Rows[0]["MastType"]); } int index = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(string.Format("Select max(DocLineNo) FROM XAArInvoiceDet where DocId='{0}'", invId)), 0); #region ar pulling data for (int m = 0; m < list.Count; m++) { try { int sequenceId = list[m].docId; decimal qty = list[m].qty; decimal price = list[m].price; if (qty * price == 0) { continue; } decimal exRateDes = list[m].exRate; string sql = ""; if (arApInd_sch == "AR INVOICE") { sql = string.Format("select * from XaArInvoiceDet where SequenceId='{0}'", sequenceId); } if (arApInd_sch == "AP INVOICE") { sql = string.Format("select * from XaApPayableDet where SequenceId='{0}'", sequenceId); } if (arApInd_sch == "AR QUOTE") { sql = string.Format("select ChgCode,'' AS AcCode,'' AS AcSource,ChgDes AS ChgDes1,Unit,Currency,Gst,GstType from SeaQuoteDet1 where SequenceId='{0}'", sequenceId); } if (arApInd_sch == "AP QUOTE") { sql = string.Format("select ChgCode,'' AS AcCode,'' AS AcSource,ChgDes AS ChgDes1,Unit,Currency,Gst,GstType from SeaApQuoteDet1 where SequenceId='{0}'", sequenceId); } DataTable tab = ConnectSql.GetTab(sql); for (int i = 0; i < tab.Rows.Count; i++) { string chgCode = tab.Rows[i]["ChgCode"].ToString(); //string acCode = tab.Rows[i]["AcCode"].ToString(); //string acSource = tab.Rows[i]["AcSource"].ToString(); string chgDes1 = tab.Rows[i]["ChgDes1"].ToString(); string unit = tab.Rows[i]["Unit"].ToString().ToUpper(); string currencyDes = tab.Rows[i]["Currency"].ToString(); decimal gst = SafeValue.SafeDecimal(tab.Rows[i]["Gst"], 0); string gstType = tab.Rows[i]["GstType"].ToString(); XAArInvoiceDet det = new XAArInvoiceDet(); det.AcCode = GetArCodeByChgCode(chgCode); det.AcSource = "CR"; if (docType == "CN") { det.AcSource = "DB"; } det.ChgCode = chgCode; det.ChgDes1 = chgDes1; det.ChgDes2 = ""; det.ChgDes3 = ""; det.Currency = currencyDes; det.ExRate = exRateDes; det.Price = price; det.Qty = qty; det.Unit = unit; det.Gst = gst; det.GstType = gstType; decimal amt = SafeValue.ChinaRound(qty * price, 2); decimal gstAmt = SafeValue.ChinaRound(amt * gst, 2); decimal docAmt = amt + gstAmt; decimal locAmt = SafeValue.ChinaRound(docAmt * exRateDes, 2); det.GstAmt = gstAmt; det.DocAmt = docAmt; det.LocAmt = locAmt; det.DocId = invId; index++; det.DocLineNo = index; det.DocNo = invNo; det.DocType = docType; det.MastRefNo = refNo; det.JobRefNo = jobNo; det.MastType = mastType; det.SplitType = "Set"; C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted); C2.Manager.ORManager.PersistChanges(det); } UpdateArMaster(invId, docType); } catch (Exception ex) { } } #endregion } else { string sql_mast = string.Format("select DocType,MastRefNo,JobRefNo,MastType from XAApPayable where SequenceId='{0}'", invId); DataTable tab_mast = C2.Manager.ORManager.GetDataSet(sql_mast).Tables[0]; if (tab_mast.Rows.Count == 1) { docType = SafeValue.SafeString(tab_mast.Rows[0]["DocType"]); refNo = SafeValue.SafeString(tab_mast.Rows[0]["MastRefNo"]); jobNo = SafeValue.SafeString(tab_mast.Rows[0]["JobRefNo"]); mastType = SafeValue.SafeString(tab_mast.Rows[0]["MastType"]); } int index = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(string.Format("Select max(DocLineNo) FROM XAApPayableDet where DocId='{0}'", invId)), 0); #region ap pulling data for (int m = 0; m < list.Count; m++) { try { int sequenceId = list[m].docId; decimal qty = list[m].qty; decimal price = list[m].price; if (qty * price == 0) { continue; } decimal exRateDes = list[m].exRate; string sql = ""; if (arApInd_sch == "AR INVOICE") { sql = string.Format("select * from XaArInvoiceDet where SequenceId='{0}'", sequenceId); } if (arApInd_sch == "AP INVOICE") { sql = string.Format("select * from XaApPayableDet where SequenceId='{0}'", sequenceId); } if (arApInd_sch == "AR QUOTE") { sql = string.Format("select ChgCode,'' AS AcCode, Qty, Price, ExRate, '' AS AcSource,ChgDes AS ChgDes1,Unit,Currency,Gst,GstType from SeaQuoteDet1 where SequenceId='{0}'", sequenceId); } if (arApInd_sch == "AP QUOTE") { sql = string.Format("select ChgCode,'' AS AcCode, Qty, Price, ExRate, '' AS AcSource,ChgDes AS ChgDes1,Unit,Currency,Gst,GstType from SeaApQuoteDet1 where SequenceId='{0}'", sequenceId); } DataTable tab = ConnectSql.GetTab(sql); for (int i = 0; i < tab.Rows.Count; i++) { string chgCode = tab.Rows[i]["ChgCode"].ToString(); //string acCode = tab.Rows[i]["AcCode"].ToString(); //string acSource = tab.Rows[i]["AcSource"].ToString(); string chgDes1 = tab.Rows[i]["ChgDes1"].ToString(); string unit = tab.Rows[i]["Unit"].ToString().ToUpper(); string currencyDes = tab.Rows[i]["Currency"].ToString(); //decimal exRateDes = SafeValue.SafeDecimal(tab.Rows[i]["ExRate"], 0); decimal gst = SafeValue.SafeDecimal(tab.Rows[i]["Gst"], 0); string gstType = tab.Rows[i]["GstType"].ToString(); if (qty == 0) { qty = SafeValue.SafeInt(tab.Rows[i]["Qty"], 1); } XAApPayableDet det = new XAApPayableDet(); det.AcCode = GetApCodeByChgCode(chgCode); det.AcSource = "DB"; if (docType == "SC") { det.AcSource = "CR"; } det.ChgCode = chgCode; det.ChgDes1 = chgDes1; det.ChgDes2 = ""; det.ChgDes3 = ""; det.Currency = currencyDes; det.ExRate = exRateDes; det.Price = price; det.Qty = qty; det.Unit = unit; det.Gst = gst; det.GstType = gstType; decimal amt = SafeValue.ChinaRound(qty * price, 2); decimal gstAmt = SafeValue.ChinaRound(amt * gst, 2); decimal docAmt = amt + gstAmt; decimal locAmt = SafeValue.ChinaRound(docAmt * exRateDes, 2); det.GstAmt = gstAmt; det.DocAmt = docAmt; det.LocAmt = locAmt; det.DocId = invId; index++; det.DocLineNo = index; det.DocNo = invNo; det.DocType = docType; det.MastRefNo = refNo; det.JobRefNo = jobNo; det.MastType = mastType; det.SplitType = "Set"; C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted); C2.Manager.ORManager.PersistChanges(det); } UpdateApMaster(invId, docType); } catch (Exception ex) { } } #endregion } } else { e.Result = "Error, Pls refresh your invoice"; } }
protected void grid_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e) { if (Request.QueryString["id"] != null && Request.QueryString["no"] != null) { string invNo = Request.QueryString["no"].ToString(); int invId = SafeValue.SafeInt(Request.QueryString["id"], 0); string refNo = ""; string jobNo = ""; string mastType = ""; string docType = ""; string sql_mast = string.Format("select DocType,MastRefNo,JobRefNo,MastType from XAApPayable where SequenceId='{0}'", invId); DataTable tab_mast = C2.Manager.ORManager.GetDataSet(sql_mast).Tables[0]; if (tab_mast.Rows.Count == 1) { docType = SafeValue.SafeString(tab_mast.Rows[0]["DocType"]); refNo = SafeValue.SafeString(tab_mast.Rows[0]["MastRefNo"]); jobNo = SafeValue.SafeString(tab_mast.Rows[0]["JobRefNo"]); mastType = SafeValue.SafeString(tab_mast.Rows[0]["MastType"]); } if (mastType == "SI") { sql_mast = string.Format("Select round(case when Weight/1000>volume then Weight/1000 else Volume end,3) FROM SeaImport where RefNo='{0}' and JobNo='{1}'", refNo, jobNo); } else if (mastType == "SE") { sql_mast = string.Format("Select round(case when Weight/1000>volume then Weight/1000 else Volume end,3) FROM SeaExport where RefNo='{0}' and JobNo='{1}'", refNo, jobNo); } decimal qty = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql_mast), 0); int index = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(string.Format("Select max(DocLineNo) FROM XAApPayableDet where DocId='{0}'", invId)), 0); for (int i = 0; i < list.Count; i++) { try { index++; int sequenceId = list[i].docId; string sql = string.Format(@"SELECT ChgCode, ChgDes, Currency, ExRate,Price, Unit, MinAmt, Rmk, Qty, Amt, GstType, Gst FROM SeaApQuoteDet1 where SequenceId='{0}' order by QuoteLineNo", sequenceId); DataTable tab = C2.Manager.ORManager.GetDataSet(sql).Tables[0]; if (tab.Rows.Count == 1) { string chgCode = tab.Rows[0]["ChgCode"].ToString(); string acCode = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(string.Format("select ApCode from XXchgcode where ChgCodeId='{0}'", chgCode))); string acSource = "DB"; if (docType == "SC") { acSource = "CR"; } string chgDes1 = tab.Rows[0]["ChgDes"].ToString(); decimal price = SafeValue.SafeDecimal(tab.Rows[0]["Price"], 0); string unit = tab.Rows[0]["Unit"].ToString().ToUpper(); string currencyDes = tab.Rows[0]["Currency"].ToString(); decimal exRateDes = SafeValue.SafeDecimal(tab.Rows[0]["ExRate"], 0); decimal gst = SafeValue.SafeDecimal(tab.Rows[0]["Gst"], 0); string gstType = tab.Rows[0]["GstType"].ToString(); if (qty == 0) { qty = SafeValue.SafeInt(tab.Rows[i]["Qty"], 1); } XAApPayableDet det = new XAApPayableDet(); det.AcCode = acCode; det.AcSource = acSource; det.ChgCode = chgCode; det.ChgDes1 = chgDes1; det.ChgDes2 = ""; det.ChgDes3 = ""; det.Currency = currencyDes; det.ExRate = exRateDes; det.Price = price; det.Qty = qty; det.Unit = unit; det.Gst = gst; det.GstType = gstType; decimal amt = SafeValue.ChinaRound(qty * price, 2); decimal gstAmt = SafeValue.ChinaRound(amt * gst, 2); decimal docAmt = amt + gstAmt; decimal locAmt = SafeValue.ChinaRound(docAmt * exRateDes, 2); det.GstAmt = gstAmt; det.DocAmt = docAmt; det.LocAmt = locAmt; det.LineLocAmt = 0; det.DocId = invId; det.DocLineNo = index; det.DocNo = invNo; det.DocType = docType; det.MastRefNo = refNo; det.JobRefNo = jobNo; det.MastType = mastType; det.SplitType = "SET"; C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted); C2.Manager.ORManager.PersistChanges(det); } } catch { } } UpdateApMaster(invId, docType); } else { e.Result = "Error, Pls refresh your invoice"; } }
public static string EdiFile(string filePath) { XmlDocument xmlDoc = new XmlDocument(); string docNo = ""; string docType = ""; string partyId = ""; if (true) { xmlDoc.Load(filePath); XmlNodeList nodeList = xmlDoc.SelectSingleNode("Invoices").ChildNodes; foreach (XmlNode xn in nodeList) { XmlElement elem = (XmlElement)xn; C2.XAApPayable ap = null; string refType = ""; string runType = "AP-PAYABLE"; if (ap == null) { ap = new C2.XAApPayable(); docNo = C2Setup.GetNextNo(refType, runType, DateTime.Now); docType = elem.GetAttribute("DocType"); if (docType == "CN") { docType = "SC"; } else if (docType == "DN") { docType = "SD"; } else { docType = "PL"; } string des = ""; string supplierBillNo = elem.GetAttribute("DocNo"); string hblNo = elem.GetAttribute("HblNo"); string term = elem.GetAttribute("Term"); if (term.Length == 0) { term = "CASH"; } string currency = elem.GetAttribute("Currency"); DateTime supplierBillDate = SafeValue.SafeDate(elem.GetAttribute("DocDate"), DateTime.Today); decimal exRate = SafeValue.SafeDecimal(elem.GetAttribute("ExRate"), 0); decimal docAmt = SafeValue.SafeDecimal(elem.GetAttribute("DocAmt"), 0); decimal locAmt = SafeValue.SafeDecimal(elem.GetAttribute("LocAmt"), 0); ap.DocNo = docNo; ap.DocType = docType; ap.DocDate = DateTime.Today; ap.PartyTo = partyId; ap.SupplierBillDate = supplierBillDate; ap.SupplierBillNo = supplierBillNo; ap.Term = term; ap.ExRate = exRate; ap.CurrencyId = currency; string[] acPeriod = EzshipHelper.GetAccPeriod(DateTime.Today); ap.AcYear = SafeValue.SafeInt(acPeriod[0], DateTime.Today.Year); ap.AcPeriod = SafeValue.SafeInt(acPeriod[1], DateTime.Today.Month); ap.AcCode = getAcCodeByPartyId(partyId, currency); ap.AcSource = "CR"; if (docType == "SC") { ap.AcSource = "DB"; } ap.Description = des; ap.DocAmt = docAmt; ap.LocAmt = locAmt; ap.BalanceAmt = docAmt; string[] arr = getRefNoByHbl(hblNo); ap.MastRefNo = arr[0]; ap.JobRefNo = arr[1]; ap.MastType = arr[2]; ap.CancelDate = new DateTime(1900, 1, 1); ap.CancelInd = "N"; ap.ChqDate = new DateTime(1900, 1, 1); ap.ChqNo = ""; ap.ExportInd = "N"; ap.UserId = EzshipHelper.GetUserName(); ap.EntryDate = DateTime.Now; Manager.ORManager.StartTracking(ap, Wilson.ORMapper.InitialState.Inserted); Manager.ORManager.PersistChanges(ap); C2Setup.SetNextNo(refType, runType, ap.DocNo, ap.DocDate); XmlNodeList nodeListJob = elem.ChildNodes; int i = 1; foreach (XmlNode xnJob in nodeListJob) { XmlElement elemDet = (XmlElement)xnJob; string chgCode = elemDet.GetAttribute("ChargeCode"); string des1 = elem.InnerText; string currency_det = elem.GetAttribute("Currency"); decimal exRate_det = SafeValue.SafeDecimal(elem.GetAttribute("ExRate"), 0); decimal qty = SafeValue.SafeDecimal(elem.GetAttribute("Qty"), 0); decimal price = SafeValue.SafeDecimal(elem.GetAttribute("Price"), 0); string unit = elem.GetAttribute("Unit").Trim(); decimal gst = SafeValue.SafeDecimal(elem.GetAttribute("Gst"), 0); decimal gstAmt = SafeValue.SafeDecimal(elem.GetAttribute("GstAmt"), 0); decimal docAmt_det = SafeValue.SafeDecimal(elem.GetAttribute("Amt"), 0); decimal locAmt_det = SafeValue.SafeDecimal(elem.GetAttribute("LocAmt"), 0); decimal lineLocAmt_det = SafeValue.SafeDecimal(elem.GetAttribute("LineAmt"), 0); C2.XAApPayableDet det = new XAApPayableDet(); det.AcCode = getAcCodeByChgCode(chgCode); det.AcSource = "DB"; if (ap.AcSource == "DB") { det.AcSource = "CR"; } det.ChgCode = chgCode; det.ChgDes1 = des1; det.ChgDes2 = " "; det.ChgDes3 = " "; det.ChgDes4 = " "; det.Currency = currency_det; det.DocAmt = docAmt_det; det.DocId = ap.SequenceId; det.DocLineNo = i; det.DocNo = ap.DocNo; det.DocType = ap.DocType; det.ExRate = exRate_det; det.Gst = gst; det.GstAmt = gstAmt; det.GstType = "Z"; if (det.Gst > 0) { det.GstType = "S"; } det.JobRefNo = ap.JobRefNo; det.LineLocAmt = lineLocAmt_det; det.LocAmt = locAmt_det; det.MastRefNo = ap.MastRefNo; det.MastType = ap.MastType; det.SplitType = "WtM3"; det.Price = price; det.Qty = qty; det.SplitType = ""; det.Unit = unit; if (det.Unit.Length == 0) { det.Unit = " "; } Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted); Manager.ORManager.PersistChanges(det); } } } } return("DocType=" + docType + " , " + "DocNo=" + docNo); }