public static IEnumerable <string> GetLookupByName(string tablename, string fieldname)
{
List <string> result = new List <string>();
LookUpMetaData lookUpMetaData = GetAllLookups(string.Format(" where NAMEENG='{0}'", tablename)).FirstOrDefault();
var dbman = new DBManager(DataProvider.Firebird);
dbman.ConnectionString = Entrence.ConnectionString;
if (lookUpMetaData != null)
{
LookupModel lookupModel = new LookupModel(GetLookUpFields(lookUpMetaData.Id).ToList(), lookUpMetaData);
try
{
dbman.Open();
dbman.ExecuteReader(CommandType.Text, string.Format("select \"{0}\" from \"{1}\" where FIRMAID={2}", fieldname, lookUpMetaData.Tablename, ConfigTempoSinglenton.GetInstance().CurrentFirma.Id));
while (dbman.DataReader.Read())
{
result.Add(dbman.DataReader[fieldname].ToString());
}
}
catch (Exception ex)
{
Logger.Instance().WriteLogError(ex.Message, "public static IEnumerable<string> GetLookupByName(string tablename, string fieldname)");
}
finally
{
dbman.Dispose();
}
}
return(result);
}
internal static IEnumerable <IEnumerable <string> > GetDetailsContoToAccEx(int id, int typ, string filter)
{
List <AccItemSaldo> rez = new List <AccItemSaldo>();
List <List <string> > rez1 = new List <List <string> >();
List <string> titles = new List <string>();
var dbman = new DBManager(DataProvider.Firebird);
dbman.ConnectionString = Entrence.ConnectionString;
try
{
string s =
string.Format(
"SELECT c.\"Id\",c.\"Oborot\",c.\"Date\",m.LOOKUPFIELDKEY,m.LOOKUPID,m.\"VALUE\",lf.\"Name\",m.VALUEDATE,c.\"DebitAccount\",m.LOOKUPVAL FROM \"conto\" c inner join CONTOMOVEMENT m on m.CONTOID=c.\"Id\"inner join \"lookupsfield\" lf on m.ACCFIELDKEY=lf.\"Id\" where (c.\"FirmId\"={0} and c.\"Date\">='1.1.{1}' and c.\"Date\"<='31.12.{1}' and m.ACCID={2}) order by c.\"Id\",m.SORTORDER",
ConfigTempoSinglenton.GetInstance().CurrentFirma.Id,
ConfigTempoSinglenton.GetInstance().WorkDate.Year, id);
dbman.Open();
dbman.ExecuteReader(CommandType.Text, s);
string command =
string.Format("SELECT count(ca.\"AnaliticalNameID\") " +
" FROM \"accounts\" a " +
"inner join \"analiticalaccount\" aa on a.\"AnaliticalNum\"=aa.\"Id\"" +
"inner join \"analiticalaccounttype\" aat on aa.\"TypeID\"=aat.\"Id\"" +
"inner join \"conectoranaliticfield\" ca on aa.\"Id\"=ca.\"AnaliticalNameID\"" +
"inner join \"lookupsfield\" af on af.\"Id\"=ca.\"AnaliticalFieldId\" " +
"left outer join MAPACCTOLOOKUP l on l.ACCOUNTS_ID=a.\"Id\" and l.ANALITIC_ID=aa.\"Id\" and l.ANALITIC_FIELD_ID=ca.\"AnaliticalFieldId\"" +
" where a.\"Id\"={0}", id);
int count = (int)dbman.ExecuteScalar(CommandType.Text, command);
bool change = false;
bool first = true;
bool firstrow = true;
bool ima = false;
AccItemSaldo row = new AccItemSaldo();
row.Type = typ;
int oldid = 0, newid = 0;
int chikiriki = 0;
while (dbman.DataReader.Read())
{
ima = true;
newid = int.Parse(dbman.DataReader["Id"].ToString());
if (first)
{
first = false;
oldid = newid;
int smetka = 0;
if (int.TryParse(dbman.DataReader["DebitAccount"].ToString(), out smetka))
{
if (smetka == id)
{
row.IsDebit = true;
row.Type = typ;
row.Od = decimal.Parse(dbman.DataReader["Oborot"].ToString());
}
else
{
row.Type = typ;
row.Oc = decimal.Parse(dbman.DataReader["Oborot"].ToString());
}
}
}
if (oldid != newid)
{
change = true;
}
if (change)
{
if (firstrow)
{
firstrow = false;
}
rez.Add(row);
row = new AccItemSaldo();
oldid = newid;
change = false;
int smetka;
if (int.TryParse(dbman.DataReader["DebitAccount"].ToString(), out smetka))
{
if (smetka == id)
{
row.IsDebit = true;
row.Type = typ;
row.Od = decimal.Parse(dbman.DataReader["Oborot"].ToString());
}
else
{
row.Type = typ;
row.Oc = decimal.Parse(dbman.DataReader["Oborot"].ToString());
}
}
chikiriki = 0;
}
if (chikiriki < count)
{
string value = dbman.DataReader["VALUE"].ToString();
string lvalue = dbman.DataReader["LOOKUPVAL"].ToString();
string name = dbman.DataReader["Name"].ToString();
row.Details = string.Format("{0}|{1} ", row.Details, value);
if (!String.IsNullOrWhiteSpace(lvalue) && !name.Contains("Дата "))
{
row.Details = string.Format("{0}|{1} ", row.Details, lvalue);
}
if (firstrow)
{
titles.Add(name);
}
chikiriki++;
}
}
if (ima)
{
rez.Add(row);
}
else
{
return(null);
}
}
catch (Exception ex)
{
Logger.Instance().WriteLogError(ex.Message, "internal static IEnumerable<IEnumerable<string>> GetDetailsContoToAcc(int id,int typ,string filter)");
dbman.RollBackTransaction();
}
finally
{
dbman.Dispose();
}
titles.Add("НС");
titles.Add("ОД");
titles.Add("ОК");
titles.Add("КС");
rez1.Add(titles);
var query = (from t in rez
group t by new { t.Details, t.Type }
into grp
select new AccItemSaldo
{
Details = grp.Key.Details,
Type = grp.Key.Type,
//Nsc=grp.Sum(t => t.Nsc),
//Nsd = grp.Sum(t => t.Nsc),
Oc = grp.Sum(t => t.Oc),
Od = grp.Sum(t => t.Od),
//Ksd = grp.Sum(t => t.Ksd),
//Ksc = grp.Sum(t => t.Ksc),
}).ToList();
//
var rezi = GetAllAnaliticSaldos(id, Entrence.CurrentFirma.Id);
foreach (AccItemSaldo accItemSaldo in query)
{
var saldo =
rezi.FirstOrDefault(
m => m.NameContragent == accItemSaldo.NameContragent && m.NumInvoise == accItemSaldo.NInvoise);
if (saldo != null)
{
accItemSaldo.Nsd = saldo.BeginSaldoDebit;
accItemSaldo.Nsc = saldo.BeginSaldoCredit;
}
}
//foreach (var items in rezi)
//{
// var saldo =
// query.FirstOrDefault(
// m => m.Details == items.Details);
// if (saldo == null)
// {
// var item = new AccItemSaldo();
// item.NInvoise = items.NumInvoise;
// item.NameContragent = items.NameContragent;
// item.Nsc = items.BeginSaldoCredit;
// item.Nsd = items.BeginSaldoDebit;
// item.Data = items.Date.ToShortDateString();
// item.Type = typ;
// item.Details = items.Details;
// query.Add(item);
// }
//}
if (!string.IsNullOrWhiteSpace(filter))
{
foreach (var item in query.Where(e => e.Details != null && e.Details.StartsWith(filter)).OrderBy(e => e.Details))
{
var det = item.Details.Split('|');
List <string> newrow = det.Skip(1).ToList();
decimal saldo = 0;
if (item.Type == 1)
{
saldo = item.Nsd - item.Nsc;
}
else
{
saldo = item.Nsc - item.Nsd;
}
newrow.Add(saldo.ToString(Vf.LevFormatUI));
newrow.Add(item.Od.ToString(Vf.LevFormatUI));
newrow.Add(item.Oc.ToString(Vf.LevFormatUI));
decimal ksaldo = 0;
if (item.Type == 1)
{
ksaldo = item.Nsd + item.Od - item.Nsc - item.Oc;
}
else
{
ksaldo = item.Nsc + item.Oc - item.Nsd - item.Od;
}
newrow.Add(ksaldo.ToString(Vf.LevFormatUI));
rez1.Add(newrow);
}
}
else
{
foreach (var item in query.OrderBy(e => e.Details))
{
if (item.Details != null)
{
var det = item.Details.Split('|');
List <string> newrow = det.Skip(1).ToList();
decimal saldo = 0;
if (item.Type == 1)
{
saldo = item.Nsd - item.Nsc;
}
else
{
saldo = item.Nsc - item.Nsd;
}
newrow.Add(saldo.ToString(Vf.LevFormatUI));
newrow.Add(item.Od.ToString(Vf.LevFormatUI));
newrow.Add(item.Oc.ToString(Vf.LevFormatUI));
decimal ksaldo = 0;
if (item.Type == 1)
{
ksaldo = item.Nsd + item.Od - item.Nsc - item.Oc;
}
else
{
ksaldo = item.Nsc + item.Oc - item.Nsd - item.Od;
}
newrow.Add(ksaldo.ToString(Vf.LevFormatUI));
rez1.Add(newrow);
}
}
}
return(rez1);
}