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);
}
