private void InsertInv_det(int invId, string invNo, string mastType, int qty, decimal price, string sku, string arCode, string des, string lotNo)
{
XAArInvoiceDet det = new XAArInvoiceDet();
det.ChgCode = sku;
det.ChgDes1 = des;
det.AcCode = arCode;
det.Qty = qty;
det.Price = price;
det.DocId = invId;
det.DocNo = invNo;
det.DocLineNo = SafeValue.SafeInt(ConnectSql.ExecuteScalar("select count(*) from XAArInvoiceDet where DocId='" + invId + "'"), 0) + 1;
det.DocType = "IV";
det.MastRefNo = "";
det.JobRefNo = lotNo;
det.MastType = mastType;
det.AcSource = "CR";
det.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
det.ExRate = 1;
det.Gst = 0;
det.GstType = "E";
det.GstAmt = 0;
det.DocAmt = SafeValue.ChinaRound(det.Qty * det.Price, 2);
det.LocAmt = det.DocAmt;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
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 grid_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
if (e.Parameters != null)
{
string quoteId = e.Parameters;
string sql = string.Format("SELECT SequenceId,DocNo, DocFromDate, DocToDate, CurrencyId, Term, Pol, Pod, Description FROM SeaQuote where SequenceId='{0}'", quoteId);
string currency = "SGD";
decimal exRate = 1;
string term = "CASH";
string refType = Request.QueryString["JobType"].ToString();
string refNo = Request.QueryString["RefN"].ToString();
string jobNo = Request.QueryString["JobN"].ToString();
string partyTo = "";
string sql_Job = "";
if (refType == "SI")
{
sql_Job = string.Format(@"SELECT job.CustomerId, cust.TermId, ref.ExRate FROM SeaImport AS job INNER JOIN XXParty AS cust ON job.CustomerId = cust.PartyId INNER JOIN
SeaImportRef AS ref ON ref.RefNo = job.RefNo
WHERE (job.RefNo = '{0}') AND (job.JobNo = '{1}')", refNo, jobNo);
}
else
{
sql_Job = string.Format(@"SELECT job.CustomerId, cust.TermId, ref.ExRate FROM SeaExport AS job INNER JOIN XXParty AS cust ON job.CustomerId = cust.PartyId
INNER JOIN SeaExportRef AS ref ON ref.RefNo = job.RefNo
WHERE (job.RefNo = '{0}') AND (job.JobNo = '{1}')", refNo, jobNo);
}
DataTable tab = C2.Manager.ORManager.GetDataSet(sql_Job).Tables[0];
if (tab.Rows.Count == 1)
{
partyTo = SafeValue.SafeString(tab.Rows[0][0], "");
term = SafeValue.SafeString(tab.Rows[0][1], "");
exRate = SafeValue.SafeDecimal(tab.Rows[0][2], 1);
}
string counterType = "AR-IV";
XAArInvoice inv = new XAArInvoice();
string invN = C2Setup.GetNextNo(counterType);
inv = new XAArInvoice();
inv.PartyTo = partyTo;
inv.DocType = "IV";
inv.DocNo = invN.ToString();
inv.DocDate = DateTime.Today;
string[] currentPeriod = EzshipHelper.GetAccPeriod(inv.DocDate);
inv.AcYear = SafeValue.SafeInt(currentPeriod[1], inv.DocDate.Year);
inv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], inv.DocDate.Month);
inv.Term = term;
//
int dueDay = SafeValue.SafeInt(term.ToUpper().Replace("DAYS", "").Trim(), 0);
inv.DocDueDate = inv.DocDate.AddDays(dueDay);//SafeValue.SafeDate(dueDt.Text, DateTime.Now);
inv.Description = "";
inv.CurrencyId = currency;
inv.ExRate = SafeValue.SafeDecimal(exRate, 1);
if (inv.ExRate <= 0)
{
inv.ExRate = 1;
}
inv.AcCode = EzshipHelper.GetAccArCode(inv.PartyTo, inv.CurrencyId);
if (inv.AcCode == "")
{
throw new Exception("Please frist set account code!");
}
inv.AcSource = "DB";
inv.MastType = refType;
inv.MastRefNo = refNo;
inv.JobRefNo = jobNo;
inv.ExportInd = "N";
inv.UserId = HttpContext.Current.User.Identity.Name;
inv.EntryDate = DateTime.Now;
inv.CancelDate = new DateTime(1900, 1, 1);
inv.CancelInd = "N";
try
{
C2.Manager.ORManager.StartTracking(inv, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(inv);
C2Setup.SetNextNo(invN, counterType);
sql = string.Format(@"SELECT QuoteLineNo, ChgCode, Currency, Price, Unit, MinAmt, Rmk, Qty, Amt, gsttype, gst FROM SeaQuoteDet1 Where QuoteId='{0}' order by QuoteLineNo", quoteId);
tab = C2.Manager.ORManager.GetDataSet(sql).Tables[0];
int index = 1;
for (int i = 0; i < tab.Rows.Count; i++)
{
try
{
string chgCode = SafeValue.SafeString(tab.Rows[i]["ChgCode"], "").Trim();
if (chgCode.Length == 0)
{
continue;
}
string chgDes1 = " ";
decimal qty = SafeValue.SafeDecimal(tab.Rows[i]["Qty"], 1);
decimal price = SafeValue.SafeDecimal(tab.Rows[i]["Price"], 0);
string unit = tab.Rows[i]["Unit"].ToString();
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();
string impExpInd = "Import";
if (refType == "SE")
{
impExpInd = "Export";
}
string sql_chgCode = string.Format("SELECT ArCode, ChgcodeDes,GstTypeId, GstP FROM XXChgCode WHERE (ImpExpInd = '{1}' or ImpExpInd='Full') AND (ChgcodeId = '{0}')", chgCode, impExpInd);
// DataTable
DataTable tab_chgCode = C2.Manager.ORManager.GetDataSet(sql_chgCode).Tables[0];
if (tab_chgCode.Rows.Count == 1)
{
det.AcCode = SafeValue.SafeString(tab_chgCode.Rows[0]["ArCode"], "5001");
chgDes1 = SafeValue.SafeString(tab_chgCode.Rows[0]["ChgcodeDes"], " ");
det.Gst = SafeValue.SafeDecimal(tab_chgCode.Rows[0]["GstP"], 0);
det.GstType = SafeValue.SafeString(tab_chgCode.Rows[0]["GstTypeId"], "Z");
}
else
{
det.Gst = gst;
det.GstType = gstType;
string sql_acCode = "select AcCode from XXGstAcount where GstSrc='AR'";
det.AcCode = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sql_acCode));
}
det.AcSource = "CR";
det.ChgCode = chgCode;
det.ChgDes1 = chgDes1;
det.ChgDes2 = "";
det.ChgDes3 = "";
det.Currency = currencyDes;
if (currency == currencyDes)
{
det.ExRate = 1;
}
else
{
det.ExRate = exRate;
}
det.Price = price;
det.Qty = qty;
det.Unit = unit;
decimal amt = SafeValue.ChinaRound(det.Qty * det.Price, 2);
decimal gstAmt = SafeValue.ChinaRound(amt * det.Gst, 2);
decimal docAmt = amt + gstAmt;
decimal locAmt = SafeValue.ChinaRound(docAmt * det.ExRate, 2);
det.GstAmt = gstAmt;
det.DocAmt = docAmt;
det.LocAmt = locAmt;
det.DocId = inv.SequenceId;
det.DocLineNo = index;
det.DocNo = invN;
det.DocType = "IV";
det.MastType = refType;
det.MastRefNo = refNo;
det.JobRefNo = jobNo;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
index++;
}
catch { }
}
UpdateMaster(inv.SequenceId);
e.Result = inv.DocNo;
}
catch
{
e.Result = "Fail";
}
}
}
protected void ASPxGridView1_CustomDataCallback1(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string filter = e.Parameters;
string[] _filter = filter.Split(',');
string p = _filter[0];
string s = _filter[1];
ASPxTextBox docId = this.ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox;
//get informations from arinvoice
if (p.ToUpper() == "P")
{
#region Post
string sql = @"SELECT AcYear, AcPeriod, AcCode, AcSource, DocType, DocNo, DocDate, DocDueDate, PartyTo, MastType, CurrencyId, ExRate,
Term, Description, DocAmt, LocAmt
FROM XAArInvoice";
sql += " WHERE SequenceId='" + docId.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 = "";
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();
docAmt = SafeValue.SafeDecimal(dt.Rows[0]["DocAmt"].ToString(), 0);
locAmt = SafeValue.SafeDecimal(dt.Rows[0]["LocAmt"].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();
}
else
{
e.Result = "Can't find the Ar Invoice!";
return;
}
string sqlDet = string.Format("select count(SequenceId) from XAArInvoiceDet where DocId='{0}'", docId.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 < 2000 || 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 XAArInvoiceDet where AcSource='CR' and DocId='" + docId.Text + "'";
decimal amt_det = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql), 0);
sql = "select SUM(LocAmt) from XAArInvoiceDet where AcSource='DB' and DocId='" + docId.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 XAArInvoiceDet where AcCode='' and DocId='" + docId.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.GlNo = GetNo("GLENTRY");
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.SupplierBillNo = "";
gl.SupplierBillDate = new DateTime(1900, 1, 1);
Manager.ORManager.StartTracking(gl, InitialState.Inserted);
Manager.ORManager.PersistChanges(gl);
glOid = gl.SequenceId;
// SetNo(gl.GlNo, "GLENTRY");
//insert Detail
OPathQuery query = new OPathQuery(typeof(XAArInvoiceDet), "DocId='" + docId.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 = glOid;//gl.GlNo;
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='AR'"), "2033");
for (int i = 0; i < set.Count; i++)
{
try
{
index++;
XAArInvoiceDet invDet = set[i] as XAArInvoiceDet;
XAGlEntryDet det = new XAGlEntryDet();
if (invDet.AcCode == gstAcc)
{
if (invDet.AcSource == "CR")
{
gstCrAmt += invDet.LocAmt;
}
else
{
gstDbAmt += invDet.LocAmt;
}
}
else
{
det.AcCode = invDet.AcCode;
det.ArApInd = "AR";
det.AcPeriod = acPeriod;
det.AcSource = invDet.AcSource;
det.AcYear = acYear;
if (det.AcSource == "CR")
{
det.CrAmt = SafeValue.ChinaRound(SafeValue.ChinaRound(invDet.Qty * invDet.Price, 2) * invDet.ExRate, 2);
det.CurrencyCrAmt = SafeValue.ChinaRound(det.CrAmt * exRate, 2);
det.DbAmt = 0;
det.CurrencyDbAmt = 0;
gstCrAmt += invDet.GstAmt;
}
else
{
det.DbAmt = SafeValue.ChinaRound(SafeValue.ChinaRound(invDet.Qty * invDet.Price, 2) * invDet.ExRate, 2);
det.CurrencyDbAmt = SafeValue.ChinaRound(det.DbAmt * exRate, 2);
det.CrAmt = 0;
det.CurrencyCrAmt = 0;
gstDbAmt += 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 (gstCrAmt - gstDbAmt != 0)
{
XAGlEntryDet det = new XAGlEntryDet();
det.AcCode = gstAcc;
det.ArApInd = "AR";
det.AcPeriod = acPeriod;
det.AcSource = "CR";
det.AcYear = acYear;
det.CrAmt = gstCrAmt - gstDbAmt;
det.CurrencyCrAmt = gstCrAmt - gstDbAmt;
det.DbAmt = 0;
det.CurrencyDbAmt = 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(docId.Text);
//EzshipLog.Log(docN, "", docType, "Post");
e.Result = "Post completely!";
}
catch (Exception ex)
{
e.Result = "Posting Error, Please repost!";
DeleteGl(glOid);
}
#endregion
}
if (p.ToUpper() == "V")
{
#region Void
string sql = "update XAArInvoice set CancelInd=case when CancelInd='N' then 'Y' else 'N' end where SequenceId=" + docId.Text + " and DocAmt=BalanceAmt";
int res = Manager.ORManager.ExecuteCommand(sql);
if (res > 0)
{
ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
ASPxComboBox DocType = this.ASPxGridView1.FindEditFormTemplateControl("cbo_DocType") as ASPxComboBox;
ASPxButton action = this.ASPxGridView1.FindEditFormTemplateControl("btn_void") as ASPxButton;
//EzshipLog.Log(docN.Text, "", DocType.Text, action.Text);
e.Result = "Success";
}
else
{
e.Result = "Already payment, can not void! ";
}
#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 sql_mast = string.Format("select 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)
{
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 XAArInvoiceDet 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 AcCode, AcSource, ChgCode, ChgDes1, GstType, Qty, Price, Unit, Currency,
ExRate, Gst, GstAmt, DocAmt, LocAmt FROM XAArInvoiceDet where SequenceId='{0}' order by DocLineNo", sequenceId);
DataTable tab = C2.Manager.ORManager.GetDataSet(sql).Tables[0];
if (tab.Rows.Count == 1)
{
string chgCode = tab.Rows[0]["ChgCode"].ToString();
string acCode = tab.Rows[0]["AcCode"].ToString();
string acSource = tab.Rows[0]["AcSource"].ToString();
string chgDes1 = tab.Rows[0]["ChgDes1"].ToString();
//decimal qty = SafeValue.SafeDecimal(tab.Rows[0]["Qty"], 1);
decimal price = SafeValue.SafeDecimal(tab.Rows[0]["Price"], 0);
string unit = tab.Rows[0]["Unit"].ToString();
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();
XAArInvoiceDet det = new XAArInvoiceDet();
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 gstAmt = SafeValue.SafeDecimal(tab.Rows[0]["GstAmt"], 0);
decimal docAmt = SafeValue.SafeDecimal(tab.Rows[0]["DocAmt"], 0);
decimal locAmt = SafeValue.SafeDecimal(tab.Rows[0]["LocAmt"], 0);
det.GstAmt = gstAmt;
det.DocAmt = docAmt;
det.LocAmt = locAmt;
det.DocId = invId;
det.DocLineNo = index;
det.DocNo = invNo;
det.DocType = "IV";
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 { }
}
UpdateMaster(invId);
}
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 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"]);
}
//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 XAArInvoiceDet where DocId='{0}'", invId)), 0);
for (int i = 0; i < list.Count; i++)
{
try
{
index++;
int sequenceId = list[i].docId;
decimal qty = list[i].qty;
decimal price = list[i].price;
decimal exRateDes = list[i].exRate;
if (qty * price == 0)
{
continue;
}
string sql = string.Format(@"SELECT ChgCode, ChgDes, Currency, ExRate,Price, Unit, MinAmt, Rmk, Qty, Amt, GstType, Gst
FROM SeaQuoteDet1 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 ArCode from XXchgcode where ChgCodeId='{0}'", chgCode)));
string acSource = "CR";
string chgDes1 = tab.Rows[0]["ChgDes"].ToString();
if (chgDes1.Trim().Length == 0)
{
chgDes1 = D.Text("select ChgCodeDes from XxChgCode where ChgCodeId='" + chgCode + "'");
}
//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);
XAArInvoiceDet det = new XAArInvoiceDet();
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 { }
}
UpdateArMaster(invId, docType);
}
else
{
e.Result = "Error, Pls refresh your invoice";
}
}
