public static List <ZirSarfaslService> GetZirSarfasls(Expression <Func <zirsarfasls, bool> > where = null)
{
using (var context = new DbAtiran2Entities())
{
return(_getZirSarfasls(context, where));
}
}
public static List <GroupSarfaslServes> GetGroupSarfaslses(Expression <Func <GroupSarfasl, bool> > where = null)
{
using (var context = new DbAtiran2Entities())
{
return(_getGroupSarfaslses(context, where));
}
}
private static List <SZAservice> _getActZirSarfasls(DbAtiran2Entities context,
Expression <Func <act_zirsarfasls, bool> > where = null)
{
IQueryable <act_zirsarfasls> result = context.Set <act_zirsarfasls>().AsNoTracking();
if (where != null)
{
result = result.Where(where);
}
//var x = (from read in context.act_zirsarfasls
// select new
// {
// ALi=read.zirsarfasls.sarfasls.rdf,
// }).ToList();
int Row = 2;
return(result.ToList().OrderBy(r => r.date).Select(a => new SZAservice()
{
Arow = Row++,
AID = a.rdf,
AZirSarfaslID = a.rdf_zirsarfasls,
Adate = a.date,
Abed = a.bed,
Abes = a.bes,
Abed_bes = (a.bed - a.bes) > 0?"بد": (a.bed - a.bes) == 0?"--":"بس",
Adescription = a.dis,
Akind = a.kind,
AkindName = KindName.FirstOrDefault(k => k.Key == a.kind).Value,
Asanadno = a.sanadno,
Auser = a.user
}).ToList());
}
//public static List<SZAservice> GetActZirSarfasls(Expression<Func<act_zirsarfasls, bool>> where = null)
//{
// using (var context = new DbAtiran2Entities())
// {
// return _getActZirSarfasls(context, where);
// }
//}
//public static List<SZAservice> GetSarfaslseServis(List<int> listSarfaslID, List<int> listZirsarfaslID, string FromDate, string ToDate)
//{//return new List<SarfaslService>();
// using (var context = new DbAtiran2Entities())
// {
// string listS = "";
// foreach (int s in listSarfaslID)
// {
// listS += s + ",";
// }
// string listZ = "";
// foreach (int z in listZirsarfaslID)
// {
// listZ += z + ",";
// }
// //return new List<SarfaslService>();
// var result = context.USP_GetSarfaslseServis(listS, listZ, FromDate, ToDate);
// int Row = 1;
// return result.Select(r => new SZAservice()
// {
// Srow = Row++,
// SID = r.rdf,
// SName = r.name,
// SGroupSarfaslID = r.GroupSarfaslID,
// Sbed = r.Bed ?? 0,
// Sbes = r.Bes ?? 0,
// SMan = r.Man ?? 0,
// Sbed_bes = (r.Man > 0) ? "بد" : (r.Man == 0) ? "--" : "بس",
// SMan_Befor = (r.Man_All ?? 0),
// Sbed_bes_Befor = ((r.Man_All ?? 0) > 0) ? "بد" : ((r.Man_All ?? 0) == 0) ? "--" : "بس",
// Swho_def = r.who_def,
// Shas_dar = (r.has_dar.ToLower() == "m") ? "ماليات" : (r.has_dar.ToLower() == "h") ? "هزينه" : (r.has_dar.ToLower() == "d") ? "دارايي" : (r.has_dar.ToLower() == "b") ? "بدون ماليات" : ""
// }).ToList();
// #region MyRegion
// //////////////////////////////////////////////////////////////////////////////////////////////
// //var result = context.act_zirsarfasls.AsNoTracking().Join(context.zirsarfasls.AsNoTracking(),
// // a => a.rdf_zirsarfasls,
// // z => z.rdf,
// // (a, z) => new { a, z }).Join(context.sarfasls.AsNoTracking(), az => az.z.rdf_sarfasl, s => s.rdf,
// // (az, s) => new { az, s });
// //if (listZirsarfaslID.Any())
// //{
// // result = result.Where(r => listZirsarfaslID.Contains(r.az.z.rdf));
// //}
// //if (listSarfaslID.Any())
// //{
// // result = result.Where(r => listSarfaslID.Contains(r.s.rdf));
// //}
// //else if (listZirsarfaslID.Any())
// //{
// // listSarfaslID = context.zirsarfasls.Where(z => listZirsarfaslID.Contains(z.rdf))
// // .GroupBy(z => z.rdf_sarfasl).Select(z => z.Key).ToList();
// //}
// //else
// //{
// // listSarfaslID = context.sarfasls.Select(s => s.rdf).ToList();
// //}
// //result = result.Where(r => r.az.a.date.CompareTo(FromDate) >= 0 && r.az.a.date.CompareTo(ToDate) <= 0);
// ////var ali = result.GroupBy(r2 => r2.s.rdf).Select(
// //// g => new
// ////{
// //// sarfasl = g.Select(r=>r.s),
// //// man = g.Sum(r1 => r1.az.a.bed - r1.az.a.bes)
// //// }).ToList();
// //int Row = 1;
// //var result1 = result.GroupBy(r2 => new { r2.s }).ToList().Select(
// // g => new SarfaslService()
// // {
// // row = Row++,
// // ID = g.Key.s.rdf,
// // Name = g.Key.s.name,
// // GroupSarfaslID = g.Key.s.GroupSarfaslID,
// // Man = g.Sum(r1 => r1.az.a.bed - r1.az.a.bes)
// // }).ToList();
// //if (listSarfaslID.Count > result1.Count)
// //{
// // var Sarfasls = context.sarfasls.AsNoTracking().ToList();
// // foreach (int id in listSarfaslID)
// // {
// // if (!result1.Any(r => r.ID == id))
// // {
// // var Safasl = Sarfasls.First(s => s.rdf == id);
// // result1.Add(new SarfaslService()
// // {
// // row = result1.Count + 1,
// // ID = Safasl.rdf,
// // Name = Safasl.name,
// // GroupSarfaslID = Safasl.GroupSarfaslID,
// // Man = 0
// // });
// // }
// // }
// //}
// //return result1;
// #endregion
// }
// //return (from read in result select new SarfaslService
// //{
// // GroupSarfaslID = read.GroupSarfaslID
// //}).ToList();
//}
public static List <SZAservice> GetSZAServices(string FromDate, string ToDate, List <int> listZirsarfaslID, List <int> ListSarfaslID, List <int> ListGroupSarfaslID)
{
using (var context = new DbAtiran2Entities())
{
string listG = "";
string listS = "";
string listZ = "";
foreach (int g in ListGroupSarfaslID)
{
listG += g + ",";
}
foreach (int s in ListSarfaslID)
{
listS += s + ",";
}
foreach (int z in listZirsarfaslID)
{
listZ += z + ",";
}
List <GroupSarfaslServes> Group = GetGroupSarfaslses();
var result = context.USP_GetDataForSarfasl(FromDate, ToDate, listG, listS, listZ);
int rowZ = 1;
return(result.Select(r => new SZAservice()
{
Zrow = rowZ++,
ZID = r.ZID,
ZName = r.ZName,
ZSarfaslID = r.ZSarfaslID,
Zbed = r.Zbed ?? 0,
Zbes = r.Zbes ?? 0,
ZMan = r.ZMan ?? 0,
Zbed_bes = (r.ZMan > 0) ? "بد" : (r.ZMan == 0) ? "--" : "بس",
ZMan_Befor = (r.ZMan_All ?? 0) - (r.ZMan ?? 0),
Zbed_bes_Befor = (((r.ZMan_All ?? 0) - (r.ZMan ?? 0)) > 0) ? "بد" : (((r.ZMan_All ?? 0) - (r.ZMan ?? 0)) == 0) ? "--" : "بس",
ZMan_All = (r.ZMan_All ?? 0),
Zbed_bes_All = ((r.ZMan_All ?? 0) > 0) ? "بد" : ((r.ZMan_All ?? 0) == 0) ? "--" : "بس",
Zhas_dar = (r.Zhas_dar.ToLower() == "m") ? "ماليات" : (r.Zhas_dar.ToLower() == "h") ? "هزينه" : (r.Zhas_dar.ToLower() == "d") ? "دارايي" : (r.Zhas_dar.ToLower() == "b") ? "بدون ماليات" : "",
ZActive = r.ZActive,
SID = r.SID,
SGroupSarfaslName = Group.First(s => s.ID == r.SGroupSarfaslID).Name,
SGroupSarfaslID = r.SGroupSarfaslID,
SName = r.SName,
Sbed = r.Sbed ?? 0,
Sbes = r.Sbes ?? 0,
SMan = r.SMan ?? 0,
Sbed_bes = (r.SMan > 0) ? "بد" : (r.SMan == 0) ? "--" : "بس",
SMan_Befor = (r.SMan_All ?? 0) - (r.SMan ?? 0),
Sbed_bes_Befor = ((r.SMan_All ?? 0 - (r.SMan ?? 0)) > 0) ? "بد" : (((r.SMan_All ?? 0) - (r.SMan ?? 0)) == 0) ? "--" : "بس",
SMan_All = (r.SMan_All ?? 0),
Sbed_bes_All = ((r.SMan_All ?? 0) > 0) ? "بد" : ((r.SMan_All ?? 0) == 0) ? "--" : "بس",
Shas_dar = (r.Shas_dar.ToLower() == "m") ? "ماليات" : (r.Shas_dar.ToLower() == "h") ? "هزينه" : (r.Shas_dar.ToLower() == "d") ? "دارايي" : (r.Shas_dar.ToLower() == "b") ? "بدون ماليات" : "",
Swho_def = r.Swho_def
}).ToList());
}
}
private static List <SarfaslService> _getSarfaslses(DbAtiran2Entities context,
Expression <Func <sarfasls, bool> > where = null)
{
IQueryable <sarfasls> result = context.Set <sarfasls>().AsNoTracking();
if (where != null)
{
result = result.Where(where);
}
int Row = 1;
return(result.ToList().Select(s => new SarfaslService()
{
row = Row++,
ID = s.rdf,
GroupSarfaslID = s.GroupSarfaslID,
Name = s.name
}).ToList());
}
private static List <GroupSarfaslServes> _getGroupSarfaslses(DbAtiran2Entities context,
Expression <Func <GroupSarfasl, bool> > where = null)
{
IQueryable <GroupSarfasl> result = context.Set <GroupSarfasl>().AsNoTracking();
if (where != null)
{
result = result.Where(where);
}
int Row = 1;
return(result.ToList().Select(g => new GroupSarfaslServes()
{
row = Row++,
ID = g.GroupSarfaslID,
Name = g.GroupSarfaslName,
Active = g.Active
}).ToList());
}
private static List <ZirSarfaslService> _getZirSarfasls(DbAtiran2Entities context,
Expression <Func <zirsarfasls, bool> > where = null)
{
IQueryable <zirsarfasls> result = context.Set <zirsarfasls>().AsNoTracking();
if (where != null)
{
result = result.Where(where);
}
int Row = 1;
return(result.ToList().Select(z => new ZirSarfaslService()
{
row = Row++,
ID = z.rdf,
SarfaslID = z.rdf_sarfasl,
Name = z.name,
has_dar = z.has_dar,
Active = z.Active
}).ToList());
}
//public static List<SZAservice> GetZirSarfaslServices(List<int> listZirsarfaslID, int sarfaslID, string FromDate, string ToDate)
//{
// using (var context = new DbAtiran2Entities())
// {
// string listZ = "";
// ObjectResult<USP_GetZirSarfaslServices_Result> result;
// foreach (int z in listZirsarfaslID)
// {
// listZ += z + ",";
// }
// result = context.USP_GetZirSarfaslServices(listZ, sarfaslID, FromDate, ToDate);
// int Row = 1;
// return result.Select(r => new SZAservice()
// {
// Zrow = Row++,
// ZID = r.rdf,
// ZName = r.name,
// ZSarfaslID = r.rdf_sarfasl,
// Zbed = r.Bed ?? 0,
// Zbes = r.Bes ?? 0,
// ZMan = r.Man ?? 0,
// Zbed_bes = (r.Man > 0) ? "بد" : (r.Man == 0) ? "--" : "بس",
// ZMan_Befor = (r.Man_All ?? 0),
// Zbed_bes_Befor = ((r.Man_All ?? 0) > 0) ? "بد" : ((r.Man_All ?? 0) == 0) ? "--" : "بس",
// Zhas_dar = (r.has_dar.ToLower() == "m") ? "ماليات" : (r.has_dar.ToLower() == "h") ? "هزينه" : (r.has_dar.ToLower() == "d") ? "دارايي" : (r.has_dar.ToLower() == "b") ? "بدون ماليات" : "",
// ZActive = r.Active
// }).ToList();
// #region MyRegion
// //return new List<ZirSarfaslService>();
// //var result = context.act_zirsarfasls.AsNoTracking().Join(context.zirsarfasls.AsNoTracking(),
// // a => a.rdf_zirsarfasls,
// // z => z.rdf,
// // (a, z) => new { a, z }).Where(az => az.z.rdf_sarfasl == sarfaslID).Where(az => az.z.rdf_sarfasl == sarfaslID);
// //var ZirsarfaslID = context.zirsarfasls.AsNoTracking().Where(z => z.rdf_sarfasl == sarfaslID).Select(z => z.rdf).ToList();
// //if (listZirsarfaslID.Any())
// //{
// // listZirsarfaslID = listZirsarfaslID.Where(z => ZirsarfaslID.Contains(z)).ToList();
// // if (listZirsarfaslID.Any())
// // {
// // result = result.Where(az => listZirsarfaslID.Contains(az.z.rdf));
// // }
// // else
// // {
// // result = result.Where(az => az.z.rdf == null);
// // listZirsarfaslID = ZirsarfaslID;
// // }
// //}
// //else
// //{
// // listZirsarfaslID = ZirsarfaslID;
// //}
// //result = result.Where(r => r.a.date.CompareTo(FromDate) >= 0 && r.a.date.CompareTo(ToDate) <= 0);
// ////var ali = result.GroupBy(r2 => new { r2.z }).ToList();
// //int Row = 1;
// //var result1 = result.GroupBy(r2 => new { r2.z }).ToList().Select(
// // g => new ZirSarfaslService()
// // {
// // row = Row++,
// // ID = g.Key.z.rdf,
// // Name = g.Key.z.name,
// // SarfaslID = g.Key.z.rdf_sarfasl,
// // Active = g.Key.z.Active,
// // has_dar = g.Key.z.has_dar,
// // Man = g.Sum(r1 => r1.a.bed - r1.a.bes)
// // }).ToList();
// //if (listZirsarfaslID.Count > result1.Count)
// //{
// // var ZirSarfasls = context.zirsarfasls.AsNoTracking().ToList();
// // foreach (int id in listZirsarfaslID)
// // {
// // if (!result1.Any(r => r.ID == id))
// // {
// // var ZirSafasl = ZirSarfasls.First(z => z.rdf == id);
// // result1.Add(new ZirSarfaslService()
// // {
// // row = result1.Count + 1,
// // ID = ZirSafasl.rdf,
// // Name = ZirSafasl.name,
// // SarfaslID = ZirSafasl.rdf_sarfasl,
// // Active = ZirSafasl.Active,
// // has_dar = ZirSafasl.has_dar,
// // Man = 0
// // });
// // }
// // }
// //}
// //return result1;
// #endregion
// }
//}
public static List <SZAservice> GetActZirSarfaslServices(string FromDate, string ToDate, int sarfaslID, List <int> listZirsarfasl)
{
string listZ = "";
foreach (int z in listZirsarfasl)
{
listZ += z + ",";
}
using (var context = new DbAtiran2Entities())
{
var result = context.USP_GetDataStimulSoft_Sarfasl_ZirSarfasl(FromDate, ToDate, sarfaslID, listZ).OrderBy(r => r.date).OrderBy(r => r.rdfzirsarfasl).ToList();
int Row = 0;
int backZirsarfaslID = 0;
int aid = 0;
List <SZAservice> Man_Befor = result.GroupBy(g => new { g.bed_gh, g.bes_gh, g.NameZirsarfasl, g.rdfzirsarfasl, g.NameSarfasl, g.rdfsarfasl })
.Select(r => new SZAservice()
{
Arow = 0,
AID = --aid,
Adescription = "حساب قبلي زيرسرفصل " + r.Key.NameZirsarfasl,
Abed = r.Key.bed_gh,
Abes = r.Key.bes_gh,
ZID = r.Key.rdfzirsarfasl,
ZName = r.Key.NameZirsarfasl,
Zbed = r.Key.bed_gh,
Zbes = r.Key.bes_gh,
SID = r.Key.rdfsarfasl,
SName = r.Key.NameSarfasl
}).ToList();
List <SZAservice> result1 = result.Select(r => new SZAservice()
{
Arow = (backZirsarfaslID == r.rdfzirsarfasl) ? ++Row : Row = 1,
AID = r.rdf,
Asanadno = r.sanadno,
Adate = r.date,
Adescription = r.dis,
Abed = r.bed,
Abes = r.bes,
AZirSarfaslID = backZirsarfaslID = r.rdfzirsarfasl,
Akind = r.kind,
AkindName = KindName.FirstOrDefault(k => k.Key == r.kind).Value,
Auser = r.user,
ZID = r.rdfzirsarfasl,
ZName = r.NameZirsarfasl,
Zbed = r.bed_gh,
Zbes = r.bes_gh,
SID = r.rdfsarfasl,
SName = r.NameSarfasl
}).ToList();
result1.AddRange(Man_Befor);
return(result1.OrderBy(o => o.Arow).OrderBy(o => o.ZID).ToList());
}
//List<SZAservice> result = new List<SZAservice>()
//{
// new SZAservice()
// {
// Arow = 1,
// AID = 0,
// Adescription = "مانده از قبل"
// }
//};
//using (var context = new DbAtiran2Entities())
//{
// if (zirSarfaslID != -1)
// {
// result.AddRange(_getActZirSarfasls(context, a => a.rdf_zirsarfasls == zirSarfaslID && a.date.CompareTo(FromDate) >= 0 && a.date.CompareTo(ToDate) <= 0));
// var Befor = context.act_zirsarfasls.AsNoTracking()
// .Where(a => a.rdf_zirsarfasls == zirSarfaslID && a.date.CompareTo(FromDate) < 0).Select(a => new { a.bed, a.bes }).ToList();
// var bed = result.First(r => r.AID == 0).Abed = Befor.Sum(b => b.bed);
// var bes = result.First(r => r.AID == 0).Abes = Befor.Sum(b => b.bes);
// result.First(r => r.AID == 0).Abed_bes = (bed - bes > 0) ? "بد" : (bed - bes == 0) ? "--" : "بس";
// }
// else if (sarfaslID != -1)
// {
// var ZirsarfaslID = context.zirsarfasls.AsNoTracking().Where(z => z.rdf_sarfasl == sarfaslID).Select(z => z.rdf).ToList();
// if (listZirsarfasl.Any())
// {
// var listZirsarfasl1 = listZirsarfasl.Where(z => ZirsarfaslID.Contains(z)).ToList();
// if (listZirsarfasl1.Any())
// {
// result.AddRange(_getActZirSarfasls(context, a => listZirsarfasl1.Contains(a.rdf_zirsarfasls) && a.date.CompareTo(FromDate) >= 0 && a.date.CompareTo(ToDate) <= 0));
// var Befor = context.act_zirsarfasls.AsNoTracking()
// .Where(a => listZirsarfasl1.Contains(a.rdf_zirsarfasls) && a.date.CompareTo(FromDate) < 0).Select(a => new { a.bed, a.bes }).ToList();
// var bed = result.First(r => r.AID == 0).Abed = Befor.Sum(b => b.bed);
// var bes = result.First(r => r.AID == 0).Abes = Befor.Sum(b => b.bes);
// result.First(r => r.AID == 0).Abed_bes = (bed - bes > 0) ? "بد" : (bed - bes == 0) ? "--" : "بس";
// }
// //return _getActZirSarfasls(context, a => listZirsarfasl.Contains(a.rdf_zirsarfasls) && a.date.CompareTo(FromDate) >= 0 && a.date.CompareTo(ToDate) <= 0);
// }
// else
// {
// result.AddRange(_getActZirSarfasls(context, a => ZirsarfaslID.Contains(a.rdf_zirsarfasls) && a.date.CompareTo(FromDate) >= 0 && a.date.CompareTo(ToDate) <= 0));
// var Befor = context.act_zirsarfasls.AsNoTracking()
// .Where(a => ZirsarfaslID.Contains(a.rdf_zirsarfasls) && a.date.CompareTo(FromDate) < 0).Select(a => new { a.bed, a.bes }).ToList();
// var bed = result.First(r => r.AID == 0).Abed = Befor.Sum(b => b.bed);
// var bes = result.First(r => r.AID == 0).Abes = Befor.Sum(b => b.bes);
// result.First(r => r.AID == 0).Abed_bes = (bed - bes > 0) ? "بد" : (bed - bes == 0) ? "--" : "بس";
// }
// }
// return result;
//}
}
