public static AdvancedArticle GetAdvancedArticle(int nodeID, string lang, int page, int version, bool isPublic)
{
ObjectSpace os = GetObjectSpace(isPublic);
OPathQuery oq = new OPathQuery(typeof(AdvancedArticle), "NodeID==" + nodeID + " && Lang=='" + lang + "' && Rank==" + page + " && Version==" + version, "");
return((AdvancedArticle)os.GetObject(oq));
}
internal string ConvertToXPath(OPathQuery query)
{
string xpath = String.Empty;
switch(query.QueryType)
{
case OPathQueryTypeEnum.Path:
if(query.Path == null)
throw new ArgumentNullException("query.Path");
if (query.Path.Identifiers.Count == 0)
throw new ArgumentNullException("path", "Must have at least one Identifier");
_ExprLevel.Push(PATH);
xpath = ConvertToXPath(query.Path, true, false, false);
break;
case OPathQueryTypeEnum.Expression:
if(query.Expression == null)
throw new ArgumentNullException("query.Expression");
_ExprLevel.Push(EXPR);
xpath = ConvertToXPath(query.Expression, true, false, false);
break;
}
return xpath;
}
private static void UpdateAcStatus_1(int year, int period, string acCode, string acType, string acDesc, string acDorc, decimal currentAmt)
{
OPathQuery query = new OPathQuery(typeof(XXAccStatus), string.Format("Year = '{0}' AND AcPeriod = '{1}' and AcCode='{2}'", year, period, acCode));
XXAccStatus acStatus = Manager.ORManager.GetObject(query) as XXAccStatus;
if (acStatus == null)
{
acStatus = new XXAccStatus();
acStatus.Year = Convert.ToInt32(year);
acStatus.AcCode = acCode;
acStatus.AcDesc = acDesc;
acStatus.AcDorc = acDorc;
acStatus.AcPeriod = Convert.ToInt32(period);
acStatus.CloseBal = currentAmt;
acStatus.CurrBal = currentAmt;
acStatus.OpenBal = 0;
Manager.ORManager.StartTracking(acStatus, InitialState.Inserted);
Manager.ORManager.PersistChanges(acStatus);
}
else
{
acStatus.CurrBal = currentAmt;
acStatus.CloseBal = acStatus.OpenBal + currentAmt;
Manager.ORManager.StartTracking(acStatus, InitialState.Updated);
Manager.ORManager.PersistChanges(acStatus);
}
if (period == 12)
{
year = year + 1;
period = 1;
}
else
period = period + 1;
query = new OPathQuery(typeof(XXAccStatus), string.Format("Year = '{0}' AND AcPeriod = '{1}' and AcCode='{2}'", year, period, acCode));
XXAccStatus acStatus1 = Manager.ORManager.GetObject(query) as XXAccStatus;
if (acStatus1 == null)
{
acStatus1 = new XXAccStatus();
acStatus1.Year = Convert.ToInt32(year);
acStatus1.AcCode = acCode;
acStatus1.AcDesc = acDesc;
acStatus1.AcDorc = acDorc;
acStatus1.AcPeriod = Convert.ToInt32(period);
acStatus1.CloseBal = 0;
acStatus1.CurrBal = 0;
acStatus1.OpenBal = acStatus.CloseBal;
Manager.ORManager.StartTracking(acStatus1, InitialState.Inserted);
Manager.ORManager.PersistChanges(acStatus1);
}
else
{
acStatus1.OpenBal = acStatus.CloseBal;
Manager.ORManager.StartTracking(acStatus1, InitialState.Updated);
Manager.ORManager.PersistChanges(acStatus1);
}
}
public string ConvertToXPath(string opath)
{
OPathQuery query = new OPathQuery(opath);
query.Compile();
if(query.HasErrors)
throw new OPathException(query);
return ConvertToXPath(query);
}
public string ConvertToScalarXPath(string opath)
{
OPathQuery query = new OPathQuery(opath, OPathQueryTypeEnum.Expression);
query.Compile();
if (query.HasErrors)
throw new OPathException(query);
return ConvertToXPath(query);
}
private static ObjectSet <T> InnerRetrieveMany(OPathQuery <T> query, bool useCache, params object[] parameters)
{
if (useCache)
{
throw new NotImplementedException("Caching not implemented.");
}
else
{
return(DataProvider.ObjectSpace.GetObjectSet <T>(query, parameters));
}
}
public static void DeleteForm(Node currentNode)
{
OPathQuery oq = new OPathQuery(typeof(Form), "NodeID==" + currentNode.Id, "");
Form f = (Form)SFGlobal.ObjectManager.GetObject(oq);
if (f != null)
{
SFGlobal.ObjectManager.MarkForDeletion(f);
SFGlobal.ObjectManager.PersistChanges(f);
}
}
// Needed for Password Charge and Password Reset
public override MembershipUser GetUser(string userName, bool userIsOnline)
{
OPathQuery query = new OPathQuery(typeof(C2.User), "Name='" + userName + "'", "Name");
C2.User user = C2.Manager.ORManager.GetObject <C2.User>(query);
if (user == null)
{
return(null);
}
return(new MembershipUser(this.Name, user.Name, user.SequenceId, user.Email, null, null, true, false,
DateTime.MinValue, DateTime.Today, DateTime.Today, DateTime.MinValue, DateTime.MinValue));
}
internal static ObjectExpression Parse(OPathQuery query, Mappings maps)
{
OPath opath = new OPath(maps);
int paramCount;
Expression exp = opath.Parse(query.ObjectType, query.WhereExpression, query.SortExpression, out paramCount);
ObjectExpression oe = new ObjectExpression(query.ObjectType, exp, maps, paramCount);
oe.baseQuery = query;
return(oe);
}
public override bool ValidateUser(string userName, string password)
{
OPathQuery query = new OPathQuery(typeof(C2.User), "Name='" + userName + "'", "Name");
C2.User user = C2.Manager.ORManager.GetObject(query) as C2.User;
if (user == null)
{
return(false);
}
Encryption.EncryptClass encrypt = new Encryption.EncryptClass();
return(SafeValue.SafeString(encrypt.DESEnCode(userName, password), "") == user.Pwd);
// return (user.Pwd == password);
}
/// <summary>
/// Parses an OPath query into an ObjectExpression using the mappings from the specified ObjectSpace.
/// </summary>
/// <param name="query">OPathQuery to be processed.</param>
/// <param name="os">ObjectSpaces containing the mappings to use.</param>
/// <returns>ObjectExpression containing the parser results, which are ready to be compiled.</returns>
public static ObjectExpression Parse(OPathQuery query, ObjectSpace os)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
if (os == null)
{
throw new ArgumentNullException("os");
}
return(Parse(query, os.context.Mappings));
}
public static Form GetForm(Node currentNode)
{
OPathQuery oq = new OPathQuery(typeof(Form), "NodeID==" + currentNode.Id, "");
Form f = (Form)SFGlobal.ObjectManager.GetObject(oq);
if (f != null)
{
return(f);
}
else
{
f = GetForm();
f.NodeID = currentNode.Id;
return(f);
}
}
public override string ResetPassword(string userName, string answer)
{
string password = "******";
System.Web.Security.Membership.GeneratePassword(
this.MinRequiredPasswordLength, this.MinRequiredNonAlphanumericCharacters);
OPathQuery query = new OPathQuery(typeof(C2.User), "Name='" + userName + "'", "Name");
C2.User user = C2.Manager.ORManager.GetObject <C2.User>(query);
if (user == null)
{
return(null);
}
user.Pwd = password;
C2.Manager.ORManager.StartTracking(user, InitialState.Updated);
C2.Manager.ORManager.PersistChanges(user);
return(password);
}
public override bool ChangePassword(string userName, string oldPassword, string newPassword)
{
if (!this.ValidPassword(newPassword))
{
return(false);
}
{
OPathQuery query = new OPathQuery(typeof(C2.User), "Name='" + userName + "'", "Name");
C2.User user = C2.Manager.ORManager.GetObject(query) as C2.User;
if (user == null)
{
return(false);
}
if (user.Pwd == oldPassword)
{
user.Pwd = newPassword;
C2.Manager.ORManager.StartTracking(user, InitialState.Updated);
C2.Manager.ORManager.PersistChanges(user);
return(true);
}
}
return(false);
}
private static void UpdateAcReturnEarning_1(int year)
{
// create a new gl year=new year, period=1,
//actype=p,
return;
string currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
C2.XAGlEntry inv = new XAGlEntry();
string sql1 = string.Format("select GlNo from XXAccPeriod where Year='{0}' and Period='1'", year + 1);
string oldGlNo = SafeValue.SafeString(Manager.ORManager.ExecuteScalar(sql1));
if (oldGlNo.Length > 0)
{
OPathQuery query = new OPathQuery(typeof(XAGlEntry), string.Format("DocNo = '{0}' AND DocType = 'GE'", oldGlNo));
inv = Manager.ORManager.GetObject(query) as XAGlEntry;
}
else
{
inv.ArApInd = "GL";
inv.DocNo = C2Setup.GetNextNo("GLENTRY");
inv.DocType = "GE";
inv.CancelDate = new DateTime(1900, 1, 1);
inv.CancelInd = "N";
inv.CrAmt = 0;
inv.CurrencyCrAmt = 0;
inv.CurrencyDbAmt = 0;
inv.CurrencyId = currency;
inv.ExRate = 1;
inv.DbAmt = 0;
inv.PostInd = "N";
inv.Remark = "";
inv.UserId = HttpContext.Current.User.Identity.Name;
inv.PartyTo = "";
inv.OtherPartyName = "";
inv.DocDate = new DateTime(year + 1, SafeValue.SafeInt(System.Configuration.ConfigurationManager.AppSettings["AccountFirstMonth"], 1), 1);
inv.AcYear = year + 1;
inv.AcPeriod = 1;
try
{
inv.PostDate = DateTime.Now;
inv.EntryDate = DateTime.Now;
C2.Manager.ORManager.StartTracking(inv, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(inv);
C2Setup.SetNextNo("GLENTRY", inv.DocNo);
}
catch(Exception ex)
{ throw new Exception(ex.Message + ex.StackTrace); }
}
//throw new Exception(inv.DocNo);
string sql = string.Format(@"SELECT ac.Code,ac.AcDorc,ac.AcType, ac.AcSubType, sta.OpenBal, sta.CurrBal, sta.CloseBal
FROM XXChartAcc AS ac INNER JOIN XXAccStatus AS sta ON ac.Code = sta.AcCode
WHERE ((ac.AcType = 'P') or ac.AcType='R') AND (sta.Year = '{0}') AND (sta.AcPeriod = '1') and sta.OpenBal<>0", year + 1);
decimal amt = 0;
DataTable tab = Helper.Sql.List(sql);
string code = C2.Manager.ORManager.ExecuteScalar("SELECT Code FROM XXChartAcc WHERE (AcType = 'R')").ToString();
int index = 0;
for (int i = 0; i < tab.Rows.Count; i++)
{
DataRow row = tab.Rows[i];
string subType = row["AcSubType"].ToString();
decimal closeBal = SafeValue.SafeDecimal(row["OpenBal"], 0);
if (row["AcType"].ToString() == "R")
{
//amt += closeBal;
continue;
}
else
{
if (subType == "S")
{
amt += closeBal;
}
else
{
amt -= closeBal;
}
}
string dbCrInd = row["AcDorc"].ToString();
decimal lineAmt = SafeValue.SafeDecimal(row["OpenBal"], 0);
if (dbCrInd == "DB")
{
dbCrInd = "CR";
}
else if (dbCrInd == "CR")
{
dbCrInd = "DB";
}
else
continue;
index++;
C2.XAGlEntryDet invDet = new XAGlEntryDet();
invDet.AcCode = row["Code"].ToString();
invDet.AcPeriod = 1;
invDet.AcSource = dbCrInd;
invDet.AcYear = year + 1;
invDet.ArApInd = inv.ArApInd;
invDet.CrAmt = 0;
invDet.CurrencyCrAmt = 0;
invDet.CurrencyDbAmt = 0;
invDet.DbAmt = 0;
if (invDet.AcSource == "DB")
{
invDet.CurrencyDbAmt = lineAmt;
invDet.DbAmt = lineAmt;
}
else
{
invDet.CrAmt = lineAmt;
invDet.CurrencyCrAmt = lineAmt;
}
invDet.DocNo = inv.DocNo;
invDet.DocType = inv.DocType;
invDet.CurrencyId = currency;
invDet.ExRate = 1;
invDet.GlLineNo = index;
invDet.GlNo = inv.SequenceId;
invDet.Remark = "";
C2.Manager.ORManager.StartTracking(invDet, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(invDet);
}
C2.XAGlEntryDet invDet_r = new XAGlEntryDet();
invDet_r.AcCode = code;
invDet_r.AcPeriod = 1;
invDet_r.AcSource = "CR";
invDet_r.AcYear = year + 1;
invDet_r.ArApInd = "GE";
invDet_r.CrAmt = amt;
invDet_r.CurrencyCrAmt = amt;
invDet_r.DbAmt = 0;
invDet_r.CurrencyDbAmt = 0;
invDet_r.CurrencyId = currency;
invDet_r.DocNo = inv.DocNo;
invDet_r.DocType = inv.DocType;
invDet_r.ExRate = 1;
invDet_r.GlLineNo =index + 1;
invDet_r.GlNo = inv.SequenceId;
invDet_r.Remark = "";
C2.Manager.ORManager.StartTracking(invDet_r, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(invDet_r);
sql = string.Format("Update XXAccPeriod Set GlNo='{1}' where Year='{0}' and Period='1'", year + 1, inv.DocNo);
C2.Manager.ORManager.ExecuteCommand(sql);
}
/// <summary>Retrieve the first instance of this class using an OPathQuery.</summary>
/// <param name="query">OPathQuery to execute.</param>
/// <param name="parameters">Parameter values to use when executing the query.</param>
public static T RetrieveFirst(OPathQuery <T> query, params object[] parameters)
{
return(InnerRetrieveOne(query, false, parameters));
}
/// <summary>Executes an OPathQuery against the database and returns a collection filled with the results.</summary>
/// <param name="opathQuery">OPathQuery to execute.</param>
/// <param name="parameters">Parameter values to use when executing the query.</param>
/// <returns>An IList filled with objects retrieved from the data store.</returns>
public static Collection <T> Retrieve(OPathQuery <T> opathQuery, params object[] parameters)
{
return(InnerRetrieveMany(opathQuery, false, parameters));
}
/// <summary>Executes an OPathQuery against the data store and returns an collection filled with the results.</summary>
/// <param name="opathQuery">OPathQuery to execute.</param>
/// <returns>An IList filled with objects retrieved from the data store.</returns>
public static Collection <T> Retrieve(OPathQuery <T> opathQuery)
{
return(InnerRetrieveMany(opathQuery, false));
}
protected void ASPxGridView1_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string filter = e.Parameters;
string[] _filter = filter.Split(',');
string p = _filter[0];
ASPxTextBox oidCtr = this.ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox;
ASPxTextBox docNoCtr = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
ASPxComboBox docType1 = this.ASPxGridView1.FindEditFormTemplateControl("cbo_DocType1") as ASPxComboBox;
//get informations from arinvoice
if (p.ToUpper() == "P")
{
#region Post
if (docNoCtr.Text.Trim().Length < 6)
{
e.Result = "Please Generate No First !";
return;
}
string sql = @"SELECT AcYear, AcPeriod, DocType, DocNo, DocDate, CurrencyId, ExRate, PartyTo, OtherPartyName, DocAmt, LocAmt, AcCode, AcSource, ChqNo, ChqDate, CloseInd, ExportInd,
BankRec, BankDate, Remark FROM XAApPayment";
sql += " WHERE SequenceId='" + oidCtr.Text + "'";
DataTable dt = Helper.Sql.List(sql);
int acYear = 0;
int acPeriod = 0;
string docN = "";
string docType = "";
string acSource = "";
string acCode = "";
decimal locAmt = 0;
decimal docAmt = 0;
decimal exRate = 0;
string currency = "";
DateTime docDt = DateTime.Today;
string partyTo = "";
string otherPartyName = "";
string remarks = "";
string chqNo = "";
if (dt.Rows.Count == 1)
{
acYear = SafeValue.SafeInt(dt.Rows[0]["AcYear"], 0);
acPeriod = SafeValue.SafeInt(dt.Rows[0]["AcPeriod"], 0);
acSource = dt.Rows[0]["AcSource"].ToString();
acCode = dt.Rows[0]["AcCode"].ToString();
docN = dt.Rows[0]["DocNo"].ToString();
docType = dt.Rows[0]["DocType"].ToString();
locAmt = SafeValue.SafeDecimal(dt.Rows[0]["LocAmt"].ToString(), 0);
docAmt = SafeValue.SafeDecimal(dt.Rows[0]["DocAmt"].ToString(), 0);
exRate = SafeValue.SafeDecimal(dt.Rows[0]["ExRate"].ToString(), 0);
currency = dt.Rows[0]["CurrencyId"].ToString();
docDt = SafeValue.SafeDate(dt.Rows[0]["DocDate"], new DateTime(1900, 1, 1));
// partyId = dt.Rows[0][""].ToString();
remarks = dt.Rows[0]["Remark"].ToString();
partyTo = dt.Rows[0]["PartyTo"].ToString();
otherPartyName = dt.Rows[0]["OtherPartyName"].ToString();
chqNo = dt.Rows[0]["ChqNo"].ToString();
}
else
{
e.Result = "Can't find the Payment!";
return;
}
string sqlDet = string.Format("select count(SequenceId) from XAApPaymentDet where PayId='{0}'", oidCtr.Text);
int detCnt = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sqlDet), 0);
if (detCnt == 0)
{
e.Result = "No Detail, Can't Post";
return;
}
sqlDet = string.Format("select max(docDate) from XAApPaymentDet where PayId='{0}'", oidCtr.Text);
DateTime maxLineDocDate = SafeValue.SafeDate(Manager.ORManager.ExecuteScalar(sqlDet), new DateTime(1900, 1, 1));
if (maxLineDocDate > new DateTime(2000, 1, 1) && maxLineDocDate > docDt)
{
e.Result = "The bill's Date can't be greater than payment date.";
return;
}
//check account period
if (acYear < 1 || acPeriod < 1)
{
e.Result = "Account year or Period Invalid!";
return;
}
string sql1 = "select CloseInd from XXAccPeriod where Year='" + acYear + "' and Period ='" + acPeriod + "'";
string closeInd = SafeValue.SafeString(Manager.ORManager.ExecuteScalar(sql1), "");
if (closeInd == "")
{
e.Result = "Can't find this account period!";
return;
}
else if (closeInd == "Y")
{
e.Result = "The account period is closed!";
return;
}
sql = string.Format("select sum(locamt) from XAApPaymentDet where (AcSource = 'DB') AND (PayId = '{0}')", oidCtr.Text);
decimal amt_detDb = SafeValue.SafeDecimal(Manager.ORManager.ExecuteScalar(sql), 0);
sql = string.Format("select sum(locamt) from XAApPaymentDet where(AcSource = 'CR') AND (PayId = '{0}')", oidCtr.Text);
decimal amt_detCr = SafeValue.SafeDecimal(Manager.ORManager.ExecuteScalar(sql), 0);
if (locAmt != amt_detDb - amt_detCr)
{
e.Result = "Amount can't match, can't post";
return;
}
sql = "select count(*) from XAApPaymentDet where AcCode='' and PayId='" + oidCtr.Text + "'";
detCnt = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql), 0);
if (detCnt > 0)
{
e.Result = "Some Item's Accode is blank, pls check";
return;
}
//delete old post data
sql = string.Format("SELECT SequenceId from XAGlEntry WHERE DocNo='{0}' and DocType='{1}'", docN, docType);
int glOldOid = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql), 0);
if (glOldOid > 0)
{
DeleteGl(glOldOid);
}
//Insert into gl entry
int glOid = 0;
try
{
C2.XAGlEntry gl = new XAGlEntry();
gl.AcPeriod = acPeriod;
gl.AcYear = acYear;
gl.ArApInd = "AP";
gl.DocType = docType;
gl.DocDate = docDt;
gl.DocNo = docN;
gl.CrAmt = docAmt;
gl.CurrencyCrAmt = locAmt;
gl.DbAmt = docAmt;
gl.CurrencyDbAmt = locAmt;
gl.CurrencyId = currency;
gl.EntryDate = DateTime.Now;
gl.ExRate = exRate;
gl.PostDate = DateTime.Now;
gl.PostInd = "N";
gl.Remark = remarks;
gl.UserId = HttpContext.Current.User.Identity.Name;
gl.CancelInd = "N";
gl.CancelDate = new DateTime(1900, 1, 1);
gl.PartyTo = partyTo;
gl.OtherPartyName = otherPartyName;
gl.ChqNo = chqNo;
gl.SupplierBillNo = "";
gl.SupplierBillDate = new DateTime(1900, 1, 1);
Manager.ORManager.StartTracking(gl, InitialState.Inserted);
Manager.ORManager.PersistChanges(gl);
glOid = gl.SequenceId;
//insert Detail
OPathQuery query = new OPathQuery(typeof(XAApPaymentDet), "PayId='" + oidCtr.Text + "'");
ObjectSet set = Manager.ORManager.GetObjectSet(query);
int index = 1;
XAGlEntryDet det1 = new XAGlEntryDet();
det1.AcCode = acCode;
det1.ArApInd = "AP";
det1.AcPeriod = acPeriod;
det1.AcSource = acSource;
det1.AcYear = acYear;
det1.CrAmt = docAmt;
det1.CurrencyCrAmt = locAmt;
det1.DbAmt = 0;
det1.CurrencyDbAmt = 0;
det1.CurrencyId = currency;
det1.DocNo = docN;
det1.DocType = docType;
det1.ExRate = exRate;
det1.GlLineNo = index;
det1.GlNo = gl.SequenceId;
det1.Remark = remarks;
Manager.ORManager.StartTracking(det1, InitialState.Inserted);
Manager.ORManager.PersistChanges(det1);
for (int i = 0; i < set.Count; i++)
{
try
{
index++;
XAApPaymentDet invDet = set[i] as XAApPaymentDet;
XAGlEntryDet det = new XAGlEntryDet();
det.AcCode = invDet.AcCode;
det.ArApInd = "AP";
det.AcPeriod = acPeriod;
det.AcSource = invDet.AcSource;
det.AcYear = acYear;
if (invDet.AcSource == "DB")
{
det.CrAmt = 0;
det.CurrencyCrAmt = 0;
det.DbAmt = invDet.DocAmt;
det.CurrencyDbAmt = invDet.LocAmt;
}
else if (invDet.AcSource == "CR")
{
det.CrAmt = invDet.DocAmt;
det.CurrencyCrAmt = invDet.LocAmt;
det.DbAmt = 0;
det.CurrencyDbAmt = 0;
}
det.CurrencyId = invDet.Currency;
det.DocNo = docN;
det.DocType = docType;
det.ExRate = invDet.ExRate;
det.GlLineNo = index;
det.GlNo = gl.SequenceId;
det.Remark = invDet.Remark1;
Manager.ORManager.StartTracking(det, InitialState.Inserted);
Manager.ORManager.PersistChanges(det);
}
catch
{
e.Result = "Posting Error, Please repost!";
DeleteGl(glOid);
}
}
UpdateApPayment(oidCtr.Text);
e.Result = "Post completely!";
}
catch
{
e.Result = "Posting Error, Please repost!";
DeleteGl(glOid);
}
#endregion
}
else if (p == "DD")
{
#region delete detail
try
{
string sql = "SELECT SequenceId,DocId, DocType FROM XAApPaymentDet where PayId='" + oidCtr.Text + "'";
DataTable tab = Helper.Sql.List(sql);
for (int i = 0; i < tab.Rows.Count; i++)
{
try
{
string sequenceId = tab.Rows[i]["SequenceId"].ToString();
string docId = tab.Rows[i]["DocId"].ToString();
string docType = tab.Rows[i]["DocType"].ToString();
string sql_det = "delete from XAApPaymentDet where SequenceId='" + sequenceId + "'";
if (C2.Manager.ORManager.ExecuteCommand(sql_det) > 0)
{
UpdateMaster(docId, docType);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
else if (filter == "GainLoss")
{
#region GAIN LOSS
string sql = string.Format("select sum(case when AcSource='DB' then -LocAmt else LocAmt end) As LocAmt from XAApPaymentDet where PayId='{0}'", oidCtr.Text);
decimal balAmt = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql), 0);
string gainAccCode = System.Configuration.ConfigurationManager.AppSettings["GainLoseAcCode"];
if (balAmt > 0)
{
XAApPaymentDet det = new XAApPaymentDet();
det.AcCode = gainAccCode;
det.AcSource = "DB";
det.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
det.DocAmt = balAmt;
det.DocDate = new DateTime(1900, 1, 1);
det.DocId = 0;
det.DocNo = "";
det.DocType = "PS";
det.ExRate = 1;
det.LocAmt = balAmt;
det.Remark1 = "GAIN AND LOSS";
det.Remark2 = "";
det.Remark3 = "";
det.PayId = SafeValue.SafeInt(oidCtr.Text, 0);
ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
det.PayNo = docN.Text;
string sql_detCnt = "select count(DocNo) from XAApPaymentDet where PayId='" + oidCtr.Text + "'";
int lineNo = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql_detCnt), 0) + 1;
det.PayLineNo = lineNo;
det.PayType = "PS";
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
else if (balAmt < 0)
{
XAApPaymentDet det = new XAApPaymentDet();
det.AcCode = gainAccCode;
det.AcSource = "CR";
det.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
det.DocAmt = -balAmt;
det.DocDate = new DateTime(1900, 1, 1);
det.DocId = 0;
det.DocNo = "";
det.DocType = "PS";
det.ExRate = 1;
det.LocAmt = -balAmt;
det.Remark1 = "GAIN AND LOSS";
det.Remark2 = "";
det.Remark3 = "";
det.PayId = SafeValue.SafeInt(oidCtr.Text, 0);
ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
det.PayNo = docN.Text;
string sql_detCnt = "select count(DocNo) from XAApPaymentDet where PayId='" + oidCtr.Text + "'";
int lineNo = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql_detCnt), 0) + 1;
det.PayLineNo = lineNo;
det.PayType = "PS";
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
#endregion
}
else if (filter == "Generate")
{
string invN = C2Setup.GetNextNo("AP-PAYMENT-" + docType1.Text);
string sql = string.Format("Update XAApPayment set DocNo='{1}',GenerateInd='Y' where SequenceId='{0}'", oidCtr.Text, invN);
if (ConnectSql.ExecuteSql(sql) > -1)
{
C2Setup.SetNextNo("AP-PAYMENT-" + docType1.Text, invN);
}
e.Result = invN;
}
}
public override EntitySet Load(OPathQuery query, string[] attributes, string orderby, int first, int max)
{
if (first <= 0)
throw new ArgumentException("first must be greater than 0");
if (max < 0)
throw new ArgumentException("max must be none negative");
if (max == 0 && first != 1)
throw new ArgumentException("max must be greater than zero if a first index is set");
SqlMapperTransformer transformer = new SqlMapperTransformer(_Mapping, _Model);
transformer.Dialect = _Dialect;
ISQLExpression exp = transformer.TransformToSql((Evaluant.OPath.Expressions.Path)query.Path.Clone(),
attributes,
orderby == null || orderby == String.Empty ? new string[0] : orderby.Split(','),
false);
if (first != 1 || max != 0)
{
OPath.Expressions.Function countFunction = new OPath.Expressions.Function(query.Path, Evaluant.OPath.Expressions.FunctionEnum.Count);
OPath.Expressions.Call callCount = new OPath.Expressions.Call("eval", new Evaluant.OPath.Expressions.Collections.ConstraintCollection(new OPath.Expressions.Constraint[] { countFunction }));
query.Expression = callCount;
int maxEntity = Convert.ToInt32(LoadScalar(query));
if (maxEntity < first)
return new EntitySet(); // paging out of range
if (max == 0)
max = maxEntity;
if (first + max > maxEntity)
max = maxEntity - first + 1;
exp = _Dialect.Page(exp, ((SelectStatement)exp).OrderByClause, first, max);
}
return LoadWithSql(exp, transformer.FullNameAttributes, transformer.ColumnAliasMapping, query.Path.Identifiers.Count > 1);
}
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
}
public ISQLExpression TransformScalar(OPathQuery query)
{
if (query.Expression == null)
throw new ArgumentNullException("query.Expression");
if (query.Expression.Operands.Count == 0)
throw new ArgumentNullException("query.Expression", "Must have at least one Operand");
_ExprLevel.Push(EXPR);
ISQLExpression select = ConvertToSQL(query.Expression, true, false, false);
if (select.GetType() != typeof(SelectStatement))
{
ISQLExpression exp = select;
select = new SelectStatement(null);
((SelectStatement)select).SelectList.Add(exp);
if (exp is SelectStatement)
((SelectStatement)select).FromClause = ((SelectStatement)exp).FromClause;
}
return select;
}
public override void LoadReference(IEnumerable entities, string[] references)
{
// references = null => don't load references
if (references == null)
return;
// Group entities by type
Hashtable orderedEntities = new Hashtable();
foreach (Entity e in entities)
{
if (!orderedEntities.ContainsKey(e.Type))
orderedEntities.Add(e.Type, new EntitySet());
((EntitySet)orderedEntities[e.Type]).Add(e);
e.RemoveReference(references);
}
// Process each entity by type
foreach (string entityType in orderedEntities.Keys)
{
Hashtable entityIndex = new Hashtable();
foreach (Entity entity in ((EntitySet)orderedEntities[entityType]))
entityIndex.Add(entity.Id, entity);
string[] ids = new string[((EntitySet)orderedEntities[entityType]).Count];
int i = 0;
foreach (Entity e in (EntitySet)orderedEntities[entityType])
ids[i++] = e.Id;
string id = String.Join("', '", ids);
Hashtable refs = GetReferenceMappings(entityType, references);
// Process each reference of the current entity type
foreach (string refName in refs.Keys)
{
ReferenceMapping rm = (ReferenceMapping)refs[refName];
Evaluant.Uss.Models.Reference referenceModel =
_Model.GetReference(rm.EntityParent.Type, refName.Substring(refName.LastIndexOf('.') + 1), true);
if (_Mapping.Entities[referenceModel.ParentType] == null)
referenceModel = new Evaluant.Uss.Models.Reference(referenceModel.Name, entityType, referenceModel.ChildType, referenceModel.IsComposition, referenceModel.FromMany, referenceModel.ToMany);
string parentType = entityType;
string opath = String.Concat(parentType, "[ id( '", id, "' ) ].", refName);
OPathQuery opathQuery = new OPathQuery(opath);
opathQuery.Compile();
if (opathQuery.HasErrors)
throw new OPathException(opathQuery);
SqlMapperTransformer transformer = new SqlMapperTransformer(_Mapping, _Model);
transformer.Dialect = _Dialect;
string childType = referenceModel.ChildType;
//StringCollection attributes = new StringCollection();
//if(_Model.GetEntity(rm.EntityChild) != null)
// attributes = GetInheritedAttributes(rm.EntityChild);
//string[] atts = new string[attributes.Count];
//for(i = 0; i < attributes.Count; i++)
// atts[i] = attributes[i];
string[] atts = new string[0];
UnionStatement exp = (UnionStatement)transformer.TransformToSql(opathQuery.Path, atts, new string[] { }, false);
// ToDo : Add expression for children types
IList childrenSubTypes = _Model.GetTree(childType);
foreach (SelectStatement select in exp.SelectExpressions)
{
EntityMapping entityChild = _Mapping.Entities[referenceModel.ChildType];
// Insert the ParentId field in case of generic reference mapping
// Then, we can add the OrderBy clause
for (int pmkIndex = 0; pmkIndex < rm.EntityParent.Ids.Count; pmkIndex++)
{
PrimaryKeyMapping pkm = rm.EntityParent.Ids[pmkIndex];
string parentIdAlias = SqlMapperTransformer.GetParentIdAlias(rm.EntityParent, pkm.Field);
if (!FieldExists(select.SelectList, parentIdAlias))
select.SelectList.Insert(2 + pmkIndex, new Column(pkm, select.TableAlias, parentIdAlias, parentIdAlias));
}
}
if (rm.OrderBy != null && rm.OrderBy != string.Empty)
{
exp.OrderByClause = new OrderByClause(exp);
string[] orderbies = rm.OrderBy.Split(',');
foreach (string orderby in orderbies)
{
string[] orderbyDirection = orderby.Split(' ');
if (orderbyDirection.Length == 1)
exp.OrderByClause.Add(new OrderByClauseColumn(orderbyDirection[0]));
else
exp.OrderByClause.Add(new OrderByClauseColumn(orderbyDirection[0], orderbyDirection[1].ToLower() == "desc"));
}
}
exp.SelectedAllColumns = true;
exp.TableAlias = "q";
_Connection.Open();
_Transaction = _Connection.BeginTransaction();
try
{
foreach (string q in _Dialect.RenderQueries(exp, Driver))
{
IDbCommand command = _Driver.CreateCommand(q, _Connection, _Transaction);
if (TraceSqlSwitch.Enabled)
{
TraceHelpler.Trace(command, _Dialect);
}
using (IDataReader reader = command.ExecuteReader())
{
EntityMapping entityChild = _Mapping.Entities[referenceModel.ChildType];
EntityMapping baseEntityChild = entityChild;
string parentId = String.Empty;
string refId = null; //String.Empty; Empty string can be the value
Entity parentEntity = null, reference = null;
while (reader.Read())
{
string trueType = reader[SqlMapperTransformer.TYPE_ALIAS].ToString();
if (baseEntityChild == null || baseEntityChild.Type != trueType)
entityChild = _Mapping.Entities[trueType];
if (entityChild == null)
throw new MappingNotFoundException(string.Format("The entity [{0}] could not be found the mapping", trueType));
int nbParentIds = _Mapping.Entities[referenceModel.ParentType].Ids.Count;
string[] newParentIds = new string[nbParentIds];
for (int indexId = 0; indexId < nbParentIds; indexId++)
newParentIds[indexId] = reader.GetValue(reader.GetOrdinal("ParentId" + (nbParentIds == 1 ? "" : indexId.ToString()))).ToString().Trim();
string newParentId = string.Join(SqlMapperProvider.IDSEP.ToString(), newParentIds);
int nbChildIds = entityChild.Ids.Count;
string[] newChildIds = new string[nbChildIds];
for (int indexId = 0; indexId < nbChildIds; indexId++)
{
if (entityChild.Ids[indexId].Generator.Name == Evaluant.Uss.SqlMapper.GeneratorMapping.GeneratorType.business)
newChildIds[indexId] = reader[entityChild.GetIdFieldAs(entityChild.Ids[indexId])].ToString().Trim();
else
newChildIds[indexId] = reader.GetValue(1 + nbParentIds + indexId).ToString().Trim();
}
string newReferenceId = string.Join(SqlMapperProvider.IDSEP.ToString(), newChildIds);
// Go the next parent
if (parentId != newParentId)
{
if (parentEntity != null)
parentEntity.State = State.UpToDate;
parentId = newParentId;
parentEntity = entityIndex[parentId] as Entity;
refId = null; //String.Empty; Empty string can be the value
}
// Create a new reference
if (refId != newReferenceId)
{
if (reference != null)
reference.State = State.UpToDate;
reference = new Entity(entityChild.Type);
refId = newReferenceId;
reference.Id = refId;
parentEntity.AddValue(refName, reference, State.UpToDate);
}
ImportAttributes(reader, new string[] { }, _Mapping.Entities[reference.Type], reference, transformer.ColumnAliasMapping);
entityChild = baseEntityChild;
}
if (parentEntity != null)
parentEntity.State = State.UpToDate;
if (reference != null)
reference.State = State.UpToDate;
// mark the reference as loaded for each parentEntity
foreach (Entity e in entities)
if (!e.InferredReferences.Contains(refName))
e.InferredReferences.Add(refName);
}
}
_Transaction.Commit();
}
catch (Exception e)
{
_Transaction.Rollback();
throw e;
}
finally
{
_Connection.Close();
}
}
}
}
public EntityMapping TransformToEntityMapping(OPathQuery query)
{
if (query.Path == null)
throw new ArgumentNullException("path");
if (query.Path.Identifiers.Count == 0)
throw new ArgumentNullException("path", "Must have at least one Identifier");
return _Mapping.Entities[query.Path.Identifiers[0].Value];
}
protected void ASPxGridView1_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string filter = e.Parameters;
ASPxTextBox oidCtr = this.ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox;
//ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
if (filter == "P")
{
SaveBill();
#region Post
string sql = @"SELECT AcYear, AcPeriod, DocType, DocNo, DocDate, DocCurrency, DocExRate, PartyTo, OtherPartyName, DocAmt, LocAmt, AcCode, AcSource, ChqNo, ChqDate, ExportInd,
BankName, Remark,GenerateInd FROM pay_doc";
sql += " WHERE Id='" + oidCtr.Text + "'";
DataTable dt = Manager.ORManager.GetDataSet(sql).Tables[0];
int acYear = 0;
int acPeriod = 0;
string docN = "";
string docType = "";
string acSource = "";
string acCode = "";
decimal locAmt = 0;
decimal docAmt = 0;
decimal exRate = 0;
string currency = "";
DateTime docDt = DateTime.Today;
string remarks = "";
string partyTo = "";
string otherPartyName = "";
string chqNo = "";
string generateInd = "";
if (dt.Rows.Count == 1)
{
acYear = SafeValue.SafeInt(dt.Rows[0]["AcYear"], 0);
acPeriod = SafeValue.SafeInt(dt.Rows[0]["AcPeriod"], 0);
acSource = dt.Rows[0]["AcSource"].ToString();
acCode = dt.Rows[0]["AcCode"].ToString();
docN = dt.Rows[0]["DocNo"].ToString();
docType = dt.Rows[0]["DocType"].ToString();
locAmt = SafeValue.SafeDecimal(dt.Rows[0]["LocAmt"].ToString(), 0);
docAmt = SafeValue.SafeDecimal(dt.Rows[0]["DocAmt"].ToString(), 0);
exRate = SafeValue.SafeDecimal(dt.Rows[0]["DocExRate"].ToString(), 0);
currency = dt.Rows[0]["DocCurrency"].ToString();
docDt = SafeValue.SafeDate(dt.Rows[0]["DocDate"], new DateTime(1900, 1, 1));
// partyId = dt.Rows[0][""].ToString();
remarks = dt.Rows[0]["Remark"].ToString();
partyTo = dt.Rows[0]["PartyTo"].ToString();
otherPartyName = dt.Rows[0]["OtherPartyName"].ToString();
chqNo = dt.Rows[0]["ChqNo"].ToString();
generateInd = SafeValue.SafeString(dt.Rows[0]["GenerateInd"]);
}
else
{
e.Result = "Can't find the ArReceipt!";
return;
}
if (generateInd.ToUpper() != "Y")
{
e.Result = "Have not generate no!";
return;
}
string sqlDet = string.Format("select count(Id) from pay_line where RepId='{0}'", oidCtr.Text);
int detCnt = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sqlDet), 0);
if (detCnt == 0)
{
e.Result = "No Detail, Can't Post";
return;
}
sqlDet = string.Format("select max(docDate) from pay_line where RepId='{0}'", oidCtr.Text);
DateTime maxLineDocDate = SafeValue.SafeDate(Manager.ORManager.ExecuteScalar(sqlDet), new DateTime(1900, 1, 1));
if (maxLineDocDate > new DateTime(2000, 1, 1) && maxLineDocDate > docDt)
{
e.Result = "The bill's Date can't be greater than receipt date.";
return;
}
//check account period
if (acYear < 1 || acPeriod < 1)
{
e.Result = "Account year or Period Invalid!";
return;
}
string sql1 = "select CloseInd from XXAccPeriod where Year='" + acYear + "' and Period ='" + acPeriod + "'";
string closeInd = SafeValue.SafeString(Manager.ORManager.ExecuteScalar(sql1), "");
if (closeInd == "")
{
e.Result = "Can't find this account period!";
return;
}
else if (closeInd == "Y")
{
e.Result = "The account period is closed!";
return;
}
//mast.amt det.amt is match
sql = string.Format("select sum(locamt) from pay_line where RepId='{0}' and AcSource='CR'", oidCtr.Text);
decimal amt_detCr = SafeValue.SafeDecimal(Manager.ORManager.ExecuteScalar(sql), 0);
sql = string.Format("select sum(locamt) from pay_line where RepId='{0}' and AcSource='DB'", oidCtr.Text);
decimal amt_detDb = SafeValue.SafeDecimal(Manager.ORManager.ExecuteScalar(sql), 0);
if (docType == "RE" && locAmt != amt_detCr - amt_detDb)
{
e.Result = "Amount can't match, can't post";
return;
}
else if (docType == "PC" && locAmt != -amt_detCr + amt_detDb)
{
e.Result = "Amount can't match, can't post";
return;
}
sql = "select count(*) from pay_line where AcCode='' and RepId='" + oidCtr.Text + "'";
detCnt = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql), 0);
if (detCnt > 0)
{
e.Result = "Some Item's Accode is blank, pls check";
return;
}
//delete old post data
sql = string.Format("SELECT SequenceId from XAGlEntry WHERE DocNo='{0}' and DocType='{1}'", docN, docType);
int glOldOid = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql), 0);
if (glOldOid > 0)
{
DeleteGl(glOldOid);
}
//Insert into gl entry
int glOid = 0;
try
{
C2.XAGlEntry gl = new XAGlEntry();
gl.AcPeriod = acPeriod;
gl.AcYear = acYear;
gl.ArApInd = "AR";
gl.DocType = docType;
gl.DocDate = docDt;
gl.DocNo = docN;
gl.CrAmt = docAmt;
gl.DbAmt = docAmt;
gl.CurrencyCrAmt = locAmt;
gl.CurrencyDbAmt = locAmt;
gl.CurrencyId = currency;
gl.EntryDate = DateTime.Now;
gl.ExRate = exRate;
gl.PostDate = DateTime.Now;
gl.PostInd = "N";
gl.Remark = remarks;
gl.UserId = HttpContext.Current.User.Identity.Name;
gl.CancelInd = "N";
gl.CancelDate = new DateTime(1900, 1, 1);
gl.PartyTo = partyTo;
gl.OtherPartyName = otherPartyName;
gl.ChqNo = chqNo;
gl.SupplierBillNo = "";
gl.SupplierBillDate = new DateTime(1900, 1, 1);
Manager.ORManager.StartTracking(gl, InitialState.Inserted);
Manager.ORManager.PersistChanges(gl);
glOid = gl.SequenceId;
//insert Detail
OPathQuery query = new OPathQuery(typeof(pay_line), "RepId='" + oidCtr.Text + "'");
ObjectSet set = Manager.ORManager.GetObjectSet(query);
int index = 1;
XAGlEntryDet det1 = new XAGlEntryDet();
det1.AcCode = acCode;
det1.ArApInd = "AR";
det1.AcPeriod = acPeriod;
det1.AcSource = acSource;
det1.AcYear = acYear;
det1.CrAmt = 0;
det1.CurrencyCrAmt = 0;
det1.DbAmt = docAmt;
det1.CurrencyDbAmt = locAmt;
det1.CurrencyId = currency;
det1.DocNo = docN;
det1.DocType = docType;
det1.ExRate = exRate;
det1.GlLineNo = index;
det1.GlNo = gl.SequenceId;
det1.Remark = remarks;
Manager.ORManager.StartTracking(det1, InitialState.Inserted);
Manager.ORManager.PersistChanges(det1);
for (int i = 0; i < set.Count; i++)
{
try
{
index++;
pay_line invDet = set[i] as pay_line;
XAGlEntryDet det = new XAGlEntryDet();
det.AcCode = invDet.AcCode;
det.ArApInd = "AR";
det.AcPeriod = acPeriod;
det.AcSource = invDet.AcSource;
det.AcYear = acYear;
if (invDet.AcSource == "DB")
{
det.DbAmt = invDet.DocAmt;
det.CurrencyDbAmt = invDet.LocAmt;
det.CrAmt = 0;
det.CurrencyCrAmt = 0;
}
else// if (det.AcSource == "CR")
{
det.CrAmt = invDet.DocAmt;
det.CurrencyCrAmt = invDet.LocAmt;
det.DbAmt = 0;
det.CurrencyDbAmt = 0;
}
det.CurrencyId = invDet.Currency;
det.DocNo = docN;
det.DocType = docType;
det.ExRate = invDet.ExRate;
det.GlLineNo = index;
det.GlNo = gl.SequenceId;
det.Remark = invDet.Remark1;
Manager.ORManager.StartTracking(det, InitialState.Inserted);
Manager.ORManager.PersistChanges(det);
}
catch
{
e.Result = "Posting Error, Please repost!";
DeleteGl(glOid);
}
}
UpdateArInv(oidCtr.Text);
EzshipLog.Log(docN, "", docType, "Post");
e.Result = "Post completely!";
}
catch
{
e.Result = "Posting Error, Please repost!";
DeleteGl(glOid);
}
#endregion
}
else if (filter == "DD")
{
#region delete receipt
try
{
string sql = "SELECT Id,DocId, DocType FROM pay_line where RepId ='" + oidCtr.Text + "'";
DataTable tab = C2.Manager.ORManager.GetDataSet(sql).Tables[0];
for (int i = 0; i < tab.Rows.Count; i++)
{
try
{
string id = tab.Rows[i]["Id"].ToString();
string docId = tab.Rows[i]["DocId"].ToString();
string docType = tab.Rows[i]["DocType"].ToString();
string sql_det = "delete from pay_line where Id='" + id + "'";
if (C2.Manager.ORManager.ExecuteCommand(sql_det) > 0)
{
UpdateMaster(docId, docType);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
else if (filter == "Reverse")
{
#region reverse receipt 0:period is closed? 1:unpost 2:exportind='N' 3: delete all item
try
{
string sql1 = string.Format("select CloseInd from xxaccperiod where Year=(select AcYear from pay_doc where Id='{0}') and Period=(select top(1)AcPeriod from pay_doc where SequenceId='{0}')", oidCtr.Text);
if (SafeValue.SafeString(ConnectSql.ExecuteScalar(sql1)) != "N")
{
throw new Exception("Acc Period is Closed!");
}
string sql_invoice = string.Format("select sequenceid from xaglentry where ArApInd='AR' and docno=(select docno from pay_doc where id='{0}') and DocType=(select doctype from pay_doc where id='{0}') ", oidCtr.Text);
DeleteGl(SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_invoice), 0));
sql_invoice = "update pay_doc set IsReturn='true', ExportInd='N' where Id='" + oidCtr.Text + "'";
Manager.ORManager.ExecuteCommand(sql_invoice);
string sql = "SELECT Id,DocId, DocType FROM pay_line where RepId ='" + oidCtr.Text + "'";
DataTable tab = C2.Manager.ORManager.GetDataSet(sql).Tables[0];
for (int i = 0; i < tab.Rows.Count; i++)
{
try
{
string id = tab.Rows[i]["Id"].ToString();
string docId = tab.Rows[i]["DocId"].ToString();
string docType = tab.Rows[i]["DocType"].ToString();
string sql_det = "delete from pay_line where Id='" + id + "'";
if (C2.Manager.ORManager.ExecuteCommand(sql_det) > 0)
{
UpdateMaster(docId, docType);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
else if (filter == "GainLoss")
{
#region GAIN LOSS
ASPxComboBox docType = this.ASPxGridView1.FindEditFormTemplateControl("cbo_DocType") as ASPxComboBox;
string sql = string.Format("select (select LocAmt from pay_doc where Id='{0}')-iSNULL(sum(case when AcSource='CR' then -LocAmt else LocAmt end),0) As LocAmt from pay_line where repid='{0}'", oidCtr.Text);
if (docType.Text == "RE")
{
sql = string.Format("select (select LocAmt from pay_doc where Id='{0}')-iSNULL(sum(case when AcSource='DB' then -LocAmt else LocAmt end),0) As LocAmt from pay_line where repid='{0}'", oidCtr.Text);
}
decimal balAmt = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql), 0);
if (balAmt > 0)
{
string gainAccCode = System.Configuration.ConfigurationManager.AppSettings["GainLoseAcCode"];
pay_line det = new pay_line();
det.AcCode = gainAccCode;
if (docType.Text == "RE")
{
det.AcSource = "CR";
}
else
{
det.AcSource = "DB";
}
det.DocType = docType.Text;
det.RepType = docType.Text;
det.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
det.DocAmt = balAmt;
det.DocDate = new DateTime(1900, 1, 1);
det.DocId = 0;
det.DocNo = "";
det.ExRate = 1;
det.LocAmt = balAmt;
det.Remark1 = "GAIN AND LOSS";
det.Remark2 = "";
det.Remark3 = "";
det.RepId = SafeValue.SafeInt(oidCtr.Text, 0);
ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
det.RepNo = docN.Text;
string sql_detCnt = "select count(DocNo) from pay_line where RepId='" + oidCtr.Text + "'";
int lineNo = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql_detCnt), 0) + 1;
det.RepLineNo = lineNo;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
else if (balAmt < 0)
{
string gainAccCode = System.Configuration.ConfigurationManager.AppSettings["GainLoseAcCode"];
pay_line det = new pay_line();
det.AcCode = gainAccCode;
if (docType.Text == "RE")
{
det.AcSource = "DB";
}
else
{
det.AcSource = "CR";
}
det.DocType = docType.Text;
det.RepType = docType.Text;
det.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
det.DocAmt = -balAmt;
det.DocDate = new DateTime(1900, 1, 1);
det.DocId = 0;
det.DocNo = "";
det.ExRate = 1;
det.LocAmt = -balAmt;
det.Remark1 = "GAIN AND LOSS";
det.Remark2 = "";
det.Remark3 = "";
det.RepId = SafeValue.SafeInt(oidCtr.Text, 0);
ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
det.RepNo = docN.Text;
string sql_detCnt = "select count(DocNo) from pay_line where RepId='" + oidCtr.Text + "'";
int lineNo = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql_detCnt), 0) + 1;
det.RepLineNo = lineNo;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
#endregion
}
else if (filter == "Generate")
{
string sql = @"SELECT DocType, DocDate FROM pay_doc WHERE Id='" + oidCtr.Text + "'";
DataTable dt = Manager.ORManager.GetDataSet(sql).Tables[0];
if (dt.Rows.Count == 1)
{
DateTime docDate = SafeValue.SafeDate(dt.Rows[0]["DocDate"], DateTime.Today);
string docType = SafeValue.SafeString(dt.Rows[0]["DocType"]);
string invN = C2Setup.GetNextNo(docType, "AR-RECEIPT", docDate);
sql = string.Format("Update pay_doc set DocNo='{1}',GenerateInd='Y' where Id='{0}'", oidCtr.Text, invN);
if (ConnectSql.ExecuteSql(sql) > -1)
{
C2Setup.SetNextNo(docType, "AR-RECEIPT", invN, docDate);
}
e.Result = invN;
}
}
}
/// <summary>
/// Returns an <see cref="OPathQuery{T}"/> object based on the given criteria.
/// </summary>
/// <returns>The <see cref="OPathQuery{T}"/> object.</returns>
public OPathQuery <T> GetOPathQuery()
{
OPathQuery <T> query = new OPathQuery <T>(_where, _sortby);
return(query);
}
