private int DeleteTblRecInvHeaderProd(TblRecInvHeaderProd row, int index, string company)
{
using (var entity = new WorkFlowManagerDBEntities())
{
var query = (from e in entity.TblRecInvHeaderProds
where e.Iserial == row.Iserial
select e).SingleOrDefault();
entity.CommandTimeout = 0;
if (query != null)
{
entity.DeleteObject(query);
using (var context = new ccnewEntities(GetSqlConnectionString(company)))
{
var ledgerheader = context.TblLedgerHeaders.FirstOrDefault(w => w.TblJournalLink == query.Iserial && w.TblTransactionType == 100);
if (ledgerheader != null)
{
context.DeleteObject(ledgerheader);
}
}
}
entity.SaveChanges();
}
return(row.Iserial);
}
private TblRecInvHeaderProd UpdateOrInsertTblRecInvHeaderProds(TblRecInvHeaderProd newRow, bool save, int index, out int outindex, string company)
{
outindex = index;
using (var entity = new WorkFlowManagerDBEntities())
{
if (save)
{
entity.TblRecInvHeaderProds.AddObject(newRow);
}
else
{
var oldRow = (from e in entity.TblRecInvHeaderProds
where e.Iserial == newRow.Iserial
select e).SingleOrDefault();
if (oldRow != null)
{
GenericUpdate(oldRow, newRow, entity);
}
}
entity.SaveChanges();
return(newRow);
}
}
private TblRecInvHeaderProd GetTblRecieveDetail(List <int?> headerProds, TblRecInvHeaderProd tblRecInvHeaderProd, string company)
{
using (var entity = new WorkFlowManagerDBEntities())
{
tblRecInvHeaderProd.Code = HandelSequence(tblRecInvHeaderProd.Code, company);
tblRecInvHeaderProd.CreationDate = DateTime.Now;
entity.TblRecInvHeaderProds.AddObject(tblRecInvHeaderProd);
entity.SaveChanges();
foreach (var headerProd in headerProds)
{
entity.TblRecInvHeaderLinkProds.AddObject(new TblRecInvHeaderLinkProd
{
TblRecInvHeaderProd = tblRecInvHeaderProd.Iserial,
TblRecInvHeaderTypeProd = tblRecInvHeaderProd.TblRecInvHeaderTypeProd,
TblPurchaseReceiveHeader = (int)headerProd
});
}
string comand = "select tblPurchaseReceiveDetail.BatchNo,TblPurchaseOrderDetailRequest.ItemId Style,TblPurchaseOrderDetailRequest.FabricColor TblColor,TblPurchaseOrderDetailRequest.Size SizeCode,TblPurchaseOrderHeaderRequest.TblCurrency Currency" +
",CAST( isnull(SUM(TblPurchaseReceiveDetail.Qty*TblPurchaseReceiveDetail.Cost)/SUM(TblPurchaseReceiveDetail.Qty),0) AS DECIMAL(19,4)) Cost" +
",CAST(sum(TblPurchaseReceiveDetail.Qty) AS DECIMAL(19,4)) Quantity from TblPurchaseReceiveDetail" +
" inner join TblPurchaseOrderDetailRequest on TblPurchaseOrderDetailRequest.Iserial= TblPurchaseReceiveDetail.TblPurchaseOrderDetailRequest" +
" inner join TblPurchaseOrderHeaderRequest on TblPurchaseOrderDetailRequest.TblPurchaseOrderHeaderRequest=TblPurchaseOrderHeaderRequest.Iserial" +
" where TblPurchaseReceiveDetail.TblPurchaseReceiveHeader in ({0}) " +
" group by TblPurchaseReceiveDetail.BatchNo,TblPurchaseOrderDetailRequest.ItemId,TblPurchaseOrderDetailRequest.FabricColor,Size,TblPurchaseOrderHeaderRequest.TblCurrency ";
comand = comand.Replace("{0}", string.Join(",", headerProds));
List <RecInvDataTable> List = entity.ExecuteStoreQuery <RecInvDataTable>(comand).ToList();
foreach (var row in List)
{
var firstOrDefault = entity.Fabric_UnitID.FirstOrDefault(x => x.Fabric_Code == row.Style);
if (firstOrDefault != null)
{
var styleIserial = firstOrDefault;
var newRow = new TblRecInvMainDetailProd
{
ItemType = firstOrDefault.Type,
Cost = (double)row.Cost,
TblCurrency = row.Currency,
Qty = (double)row.Quantity,
TblItem = styleIserial.Iserial,
TblRecInvHeaderProd = tblRecInvHeaderProd.Iserial,
SizeCode = row.SizeCode,
TblColor = row.TblColor,
BatchNo = row.BatchNo,
ExchangeRate = 1
};
entity.TblRecInvMainDetailProds.AddObject(newRow);
}
}
IQueryable <TblPurchaseReceiveDetail> query = entity.TblPurchaseReceiveDetails.Include("TblPurchaseOrderDetailRequest1").Where(x => headerProds.Contains(x.TblPurchaseReceiveHeader));
foreach (var row in query)
{
var firstOrDefault = entity.Fabric_UnitID.FirstOrDefault(x => x.Fabric_Code == row.TblPurchaseOrderDetailRequest1.ItemId);
if (firstOrDefault != null)
{
var recDetail = new TblRecInvDetailProd
{
Cost = row.Cost,
TblRecInvHeaderProd = tblRecInvHeaderProd.Iserial,
Dserial = row.Iserial,
Flg = 0,
Glserial = (int)row.TblPurchaseReceiveHeader,
Tblitem = firstOrDefault.Iserial,
ItemType = firstOrDefault.Type,
TblColor = row.TblPurchaseOrderDetailRequest1.FabricColor,
BatchNo = row.BatchNo,
SizeCode = row.TblPurchaseOrderDetailRequest1.Size,
ExchangeRate = 1
};
entity.TblRecInvDetailProds.AddObject(recDetail);
}
}
entity.SaveChanges();
}
return(tblRecInvHeaderProd);
}
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);
}
}
}
