public void SaveOldSubDetailRow(TblGlRuleDetailViewModel oldRow)
{
if (oldRow != null)
{
var valiationCollection = new List <ValidationResult>();
var isvalid = Validator.TryValidateObject(oldRow,
new ValidationContext(oldRow, null, null), valiationCollection, true);
if (isvalid)
{
var save = oldRow.Iserial == 0;
if (AllowUpdate != true && !save)
{
MessageBox.Show(strings.AllowUpdateMsg);
return;
}
if (Loading == false)
{
Loading = true;
var saveRow = new TblGlRuleDetail();
saveRow.InjectFrom(oldRow);
Glclient.UpdateOrInsertTblGlRuleDetailsAsync(saveRow, save, SelectedDetailRow.DetailsList.IndexOf(oldRow),
LoggedUserInfo.DatabasEname);
}
}
}
}
private int DeleteTblGlRuleDetail(TblGlRuleDetail row, int index, string company)
{
using (var entity = new ccnewEntities(GetSqlConnectionString(company)))
{
var query = (from e in entity.TblGlRuleDetails
where e.Iserial == row.Iserial
select e).SingleOrDefault();
if (query != null)
{
entity.DeleteObject(query);
}
entity.SaveChanges();
}
return(row.Iserial);
}
private TblGlRuleDetail UpdateOrInsertTblGlRuleDetails(TblGlRuleDetail newRow, bool save, int index, out int outindex, string company)
{
outindex = index;
using (var entity = new ccnewEntities(GetSqlConnectionString(company)))
{
if (save)
{
entity.TblGlRuleDetails.AddObject(newRow);
}
else
{
var oldRow = (from e in entity.TblGlRuleDetails
where e.Iserial == newRow.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
GenericUpdate(oldRow, newRow, entity);
}
}
entity.SaveChanges();
return(newRow);
}
}
private TblRecInvHeaderProd PostInv(TblRecInvHeaderProd row, int user, string company)
{
using (var scope = new TransactionScope())
{
short?Lang = 0;
using (var entity = new WorkFlowManagerDBEntities())
{
var firstOrDefault = entity.TblAuthUsers.FirstOrDefault(x => x.Iserial == user);
if (firstOrDefault != null)
{
Lang = firstOrDefault.CurrLang;
}
entity.CommandTimeout = 0;
var query = entity.TblRecInvHeaderProds.FirstOrDefault(x => x.Iserial == row.Iserial);
string desc = "Purchase TransNo " + row.SupplierInv;
if (row.TblRecInvHeaderTypeProd == 2)
{
desc = "Return Purchase TransNo " + row.SupplierInv;
}
if (Lang == 0)
{
desc = "فاتورة المشتريات رقم " + row.SupplierInv;
if (row.TblRecInvHeaderTypeProd == 2)
{
desc = " فاتورة مرتجع المشتريات رقم " + row.SupplierInv;
}
}
var markuptrans =
entity.TblMarkupTransProds.Include("TblMarkupProd1.TblMarkupGroupProd1")
.Where(x => x.TblRecInv == row.Iserial && x.Type == 0);
var cost = entity.TblRecInvMainDetailProds.Where(x => x.TblRecInvHeaderProd == row.Iserial).Sum(w => w.Cost * w.Qty);
double totalWithItemEffect = 0;
double totalWithoutItemEffect = 0;
foreach (var variable in markuptrans)
{
if (variable.TblMarkupProd1.ItemEffect == false)
{
if (variable.MiscValueType == 0)
{
totalWithoutItemEffect = (double)(totalWithoutItemEffect + (cost * (variable.MiscValue / 100)));
}
else
{
totalWithoutItemEffect = (double)(totalWithoutItemEffect + variable.MiscValue);
}
}
else
{
if (variable.MiscValueType == 0)
{
totalWithItemEffect = (double)(totalWithItemEffect + (cost * (variable.MiscValue / 100)));
}
else
{
totalWithItemEffect = (double)(totalWithItemEffect + variable.MiscValue);
}
}
}
if (query != null)
{
query.MiscWithoutItemEffect = totalWithoutItemEffect;
query.Misc = totalWithItemEffect;
if (totalWithItemEffect != 0)
{
var queryDetail =
entity.TblRecInvMainDetailProds.Where(x => x.TblRecInvHeaderProd == row.Iserial).ToList();
foreach (var variable in queryDetail)
{
variable.Misc = (variable.Cost / cost) * totalWithItemEffect;
}
}
entity.SaveChanges();
if (query != null)
{
query.Status = 1;
query.TblUser = user;
query.PostDate = DateTime.Now;
using (var db = new ccnewEntities(GetSqlConnectionString(company)))
{
var journal = db.tblChainSetupTests.FirstOrDefault(x => x.sGlobalSettingCode == "GLPurchaseJournal").sSetupValue;
var tablename = db.tblChainSetupTests.FirstOrDefault(
x => x.sGlobalSettingCode == "GlItemGroupTableName").sSetupValue;
int journalint = db.TblJournals.FirstOrDefault(x => x.Code == journal).Iserial;
var newLedgerHeaderProdRow = new TblLedgerHeader
{
CreatedBy = user,
CreationDate = DateTime.Now,
Description = desc,
DocDate = row.TransDate,
TblJournal = journalint,
TblTransactionType = 100,
TblJournalLink = query.Iserial
};
int temp;
var glserive = new GlService();
glserive.UpdateOrInsertTblLedgerHeaders(newLedgerHeaderProdRow, true, 0, out temp, user, company);
var sqlParam = new List <SqlParameter>
{
new SqlParameter
{
ParameterName = "Table_Name",
Value = tablename,
SqlDbType = SqlDbType.NVarChar
},
new SqlParameter
{
ParameterName = "Iserial",
Value = row.Iserial.ToString(CultureInfo.InvariantCulture),
SqlDbType = SqlDbType.NVarChar
},
};
var list = entity.ExecuteStoreQuery <GlGroupsDtp>("exec GlRecinvPostingToGl @Table_Name, @Iserial",
sqlParam.ToArray()).ToList();
#region MarkUp
foreach (var rr in markuptrans)
{
var currencyrow = db.TblCurrencyTests.First(w => w.Iserial == rr.TblCurrency);
var glAccount =
db.Entities.FirstOrDefault(
x => x.Iserial == rr.TblMarkupProd && x.scope == 0 && x.TblJournalAccountType == 9).AccountIserial;
var vendorAccountMarkUp =
db.Entities.FirstOrDefault(
x => x.Iserial == rr.EntityAccount && x.scope == 0 && x.TblJournalAccountType == rr.TblJournalAccountType);
var drorCr = true;
double?total = 0;
if (rr.MiscValueType == 0)
{
total = (total + cost * (rr.MiscValue / 100)) * rr.TblMarkupProd1.TblMarkupGroupProd1.Direction;
}
else
{
total = (total + rr.MiscValue) * rr.TblMarkupProd1.TblMarkupGroupProd1.Direction;
}
if (total > 0)
{
drorCr = false;
}
var markupdes = rr.TblMarkupProd1.Ename + row.SupplierInv;
if (Lang == 0)
{
markupdes = rr.TblMarkupProd1.Aname + row.SupplierInv;
}
decimal totalModified = (decimal)total;
if (total < 0)
{
totalModified = (decimal)(total * -1);
}
var markupVendorDiscount = new TblLedgerMainDetail();
markupVendorDiscount = new TblLedgerMainDetail
{
Amount = totalModified,
Description = markupdes,
ExchangeRate = rr.ExchangeRate,
TblCurrency = rr.TblCurrency,
TransDate = row.TransDate,
TblJournalAccountType = 0,
EntityAccount = glAccount,
GlAccount = glAccount,
TblLedgerHeader = newLedgerHeaderProdRow.Iserial,
PaymentRef = query.SupplierInv,
DrOrCr = !drorCr
};
if (row.TblRecInvHeaderTypeProd == 2)
{
markupVendorDiscount.DrOrCr = !markupVendorDiscount.DrOrCr;
}
glserive.UpdateOrInsertTblLedgerMainDetails(markupVendorDiscount, true, 000, out temp, company,
user);
if (glAccount != 0)
{
var markupVendor = new TblLedgerMainDetail
{
Amount = totalModified,
Description = markupdes,
ExchangeRate = rr.ExchangeRate,
TblCurrency = rr.TblCurrency,
TransDate = row.TransDate,
TblJournalAccountType = rr.TblJournalAccountType,
EntityAccount = vendorAccountMarkUp.Iserial,
GlAccount = vendorAccountMarkUp.AccountIserial,
TblLedgerHeader = newLedgerHeaderProdRow.Iserial,
PaymentRef = query.SupplierInv,
DrOrCr = drorCr
};
if (row.TblRecInvHeaderTypeProd == 2)
{
markupVendor.DrOrCr = !markupVendor.DrOrCr;
}
glserive.UpdateOrInsertTblLedgerMainDetails(markupVendor, true, 000, out temp, company, user);
foreach (var variable in list)
{
var costcenter = new TblGlRuleDetail();
costcenter = glserive.FindCostCenterByType(costcenter, 0, (int)variable.GroupName,
company);
var markupVendorLedgerCostCenter = new TblLedgerDetailCostCenter
{
Ratio = 0,
TblLedgerMainDetail = markupVendor.Iserial,
Amount = (double)(markupVendor.Amount * variable.CostPercentage),
TblCostCenter = costcenter.TblCostCenter,
TblCostCenterType = costcenter.TblCostCenter1.TblCostCenterType,
};
glserive.UpdateOrInsertTblLedgerDetailCostCenters(markupVendorLedgerCostCenter, true, 000,
out temp, user, company);
}
}
}
#endregion MarkUp
foreach (var rr in list.GroupBy(x => x.GroupName))
{
glserive.PostInvPurchaseAndTax(query, newLedgerHeaderProdRow, rr, company, user, list, desc);
}
glserive.CorrectLedgerHeaderRouding(newLedgerHeaderProdRow.Iserial, company, user);
}
entity.SaveChanges();
scope.Complete();
}
return(query);
}
return(null);
}
}
}
internal void GetDailySalesCommision(
int store, DateTime from, DateTime to, int userIserial, string company)
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
int transactionType = 10;
int journalint = Convert.ToInt32(
GetRetailChainSetupByCode("SalesCommissionJournalIserial", company).sSetupValue); // Code = 1
var commissionAccount =
GetRetailChainSetupByCode("SalesCommissionAccount", company);
var taxAccount =
GetRetailChainSetupByCode("EarnTaxAccount", company);
Entity salesCommissionEntity, taxEntity;
List <TblLedgerHeader> oldLedgers;
List <GetSalesDailyCommission_Result> query;
using (var context = new ccnewEntities(SharedOperation.GetSqlConnectionString(company)))
{
context.CommandTimeout = 0;
salesCommissionEntity = context.Entities.FirstOrDefault(e => e.scope == 0 &&
e.Code == commissionAccount.sSetupValue &&
e.TblJournalAccountType == 15);
taxEntity = context.Entities.FirstOrDefault(e => e.scope == 0 &&
e.Code == taxAccount.sSetupValue &&
e.TblJournalAccountType == 0);
try
{
query = context.GetSalesDailyCommission(store, from, to).ToList();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
//query = context.tblsalesdailycommisions.Where(r =>
// r.DocDate >= from && r.DocDate <= to);
// var dquery = context.GetSalesDailyCommission(store, from, to).ToList().FirstOrDefault(x => x.tblstore == 173);
}
//var transactionOptions = new TransactionOptions
//{
// IsolationLevel = IsolationLevel.ReadCommitted,
// Timeout = TimeSpan.FromSeconds(1200) //assume 10 min is the timeout time
//};
//using (var scope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromSeconds(1200)))
{
using (var context = new ccnewEntities(SharedOperation.GetSqlConnectionString(company)))
{
context.CommandTimeout = 0;
// Old Ledgers to be deleted
oldLedgers =
context.TblLedgerHeaders.Where(
x => x.TblTransactionType == transactionType && x.DocDate.Value >= from && x.DocDate.Value <= to).ToList();
// Delete old ledgers
foreach (var variable in oldLedgers)
{
context.TblLedgerHeaders.DeleteObject(variable);
}
context.SaveChanges();
}
foreach (var item in query.GroupBy(r => new { r.docdate }))
// Parallel.ForEach(query.GroupBy(r => new { r.docdate }), item =>
{
using (var context = new ccnewEntities(SharedOperation.GetSqlConnectionString(company)))
{
context.CommandTimeout = 0;
var description = string.Format("اثبات عمولات بيعية" + " {0}", item.Key.docdate.Value.ToString("dd/MM/yyyy"));
int sequence = 25;
var seq = context.TblSequences.FirstOrDefault(s => s.Iserial == sequence);
var newLedgerHeaderRow = new TblLedgerHeader
{
CreatedBy = userIserial,
CreationDate = DateTime.Now,
Description = description,
DocDate = item.Key.docdate,
TblJournal = journalint,
TblTransactionType = transactionType,
TblJournalLink = 0//item.Iserial
};
newLedgerHeaderRow.Code = SharedOperation.HandelSequence(seq, company, 0, 0, 0);
int tmp;
newLedgerHeaderRow = UpdateOrInsertTblLedgerHeaders(newLedgerHeaderRow, true, 0, out tmp, userIserial, company);//context,
decimal taxTotal = 0;
foreach (var innerItem in item)
{
var storeRec = context.TblStores
.Include(nameof(TblStore.TblStoreCommission))
.FirstOrDefault(s => s.iserial == innerItem.tblstore);
decimal taxPercent =
storeRec.TblStoreCommission.ManagerComm * (innerItem.MaxCommision ?? 0) / 100 * storeRec.TblStoreCommission.ManagerTax +
storeRec.TblStoreCommission.AssistantComm * (innerItem.MaxCommision ?? 0) / 100 * storeRec.TblStoreCommission.AssistantTax +
storeRec.TblStoreCommission.SalesManComm * (innerItem.MaxCommision ?? 0) / 100 * storeRec.TblStoreCommission.SalesManTax;
decimal commissionPercentTotal =
storeRec.TblStoreCommission.ManagerComm * (innerItem.MaxCommision ?? 0) / 100 +
storeRec.TblStoreCommission.AssistantComm * (innerItem.MaxCommision ?? 0) / 100 +
storeRec.TblStoreCommission.SalesManComm * (innerItem.MaxCommision ?? 0) / 100;
//decimal taxPercent =
// storeRec.TblStoreCommission.ManagerComm / 100 * storeRec.TblStoreCommission.ManagerTax +
// storeRec.TblStoreCommission.AssistantComm / 100 * storeRec.TblStoreCommission.AssistantTax +
// storeRec.TblStoreCommission.SalesManComm / 100 * storeRec.TblStoreCommission.SalesManTax;
//decimal commissionPercentTotal =
// storeRec.TblStoreCommission.ManagerComm / 100 +
// storeRec.TblStoreCommission.AssistantComm / 100 +
// storeRec.TblStoreCommission.SalesManComm / 100;
decimal commissionTotal = (innerItem.NetSalesAfterVAT ?? 0) * commissionPercentTotal;
if (commissionTotal == 0)
{
continue;
}
// Commission Entity
var commissionLedgerDetail = new TblLedgerMainDetail
{
Amount = commissionTotal,
Description = description,
ExchangeRate = 1,
TblCurrency = 1,
TransDate = innerItem.docdate,
TblJournalAccountType = 15,
EntityAccount = salesCommissionEntity.Iserial, //Entity Iserial
GlAccount = salesCommissionEntity.AccountIserial, // Account Iserial
TblLedgerHeader = newLedgerHeaderRow.Iserial,
PaymentRef = "", //innerItem.Iserial.ToString(),
DrOrCr = true // true debit or false for credit
};
UpdateOrInsertTblLedgerMainDetails(commissionLedgerDetail, true, 0, out tmp, company, userIserial); //context,
decimal itemTax = (innerItem.NetSalesAfterVAT ?? 0) * taxPercent;
// Store Entity
var storeEntity = context.Entities.FirstOrDefault(e => e.scope == 0 &&
e.Code == storeRec.code && e.TblJournalAccountType == 14);
if (storeEntity == null)
{
throw new Exception("store with code " + storeRec.code + " is not linked to Payable ");
}
var storeLedgerDetail = new TblLedgerMainDetail
{
Amount = commissionTotal - itemTax,
Description = description,
ExchangeRate = 1,
TblCurrency = 1,
TransDate = innerItem.docdate,
TblJournalAccountType = 14,
EntityAccount = storeEntity.Iserial, //Entity Iserial
GlAccount = storeEntity.AccountIserial, // Account Iserial
TblLedgerHeader = newLedgerHeaderRow.Iserial,
PaymentRef = "", //innerItem.Iserial.ToString(),
DrOrCr = false // true debit or false for credit
};
UpdateOrInsertTblLedgerMainDetails(storeLedgerDetail, true, 0, out tmp, company, userIserial); //context,
// Cost Center
var storeCostcenter = new TblGlRuleDetail();
storeCostcenter = FindCostCenterByType(storeCostcenter, 8, innerItem.tblstore ?? 0, company); //, context
CreateTblLedgerDetailCostCenter(company, commissionLedgerDetail.Amount ?? 0,
commissionLedgerDetail, storeCostcenter); //, context
taxTotal += itemTax;
}
// Tax Entity
var taxLedgerDetail = new TblLedgerMainDetail
{
Amount = taxTotal,//item.Sum(r => r.Commision) * 0.05M,
Description = description,
ExchangeRate = 1,
TblCurrency = 1,
TransDate = item.Key.docdate,
TblJournalAccountType = 0,
EntityAccount = taxEntity.Iserial, //Entity Iserial
GlAccount = taxEntity.AccountIserial, // Account Iserial
TblLedgerHeader = newLedgerHeaderRow.Iserial,
PaymentRef = "0", //innerItem.Iserial.ToString(),
DrOrCr = false // true debit or false for credit
};
UpdateOrInsertTblLedgerMainDetails(taxLedgerDetail, true, 0, out tmp, company, userIserial); //context,
}
}//);
}
//scope.Complete();
stopwatch.Stop();
Console.Error.WriteLine("Sequential loop time in milliseconds: {0}",
stopwatch.ElapsedMilliseconds);
}
