private void btnOk_Click(object sender, EventArgs e)
{
string Login = tbLogin.Text;
string Pass = mtbPass.Text;
bool passOk;
using (FbConnection fbBD = new FbConnection(FBDB.GetConnFBstring()))
{
if (fbBD.State == ConnectionState.Closed)
{
fbBD.Open();
}
FbCommand sqlReqest = new FbCommand("select u.pswd,u.typ_pswd from USERS u where u.logname='" + Login + "'", fbBD);
FbDataReader r = sqlReqest.ExecuteReader();
if (r.Read())
{
string PassBD = decrypt_pwd(r.GetString(0));
if (Pass == PassBD)
{
passOk = true;
}
else
{
passOk = false;
};
}
else
{
passOk = false;
};
r.Close();
sqlReqest.Dispose();
fbBD.Close();
}
if (passOk)
{
this.DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show("Пользователь с таким логином/паролем не найден");
};
}
public static void rasesnall(int god, int mes, int org_id)
{
List <Int32> sotrlist = new List <Int32>();
using (FbConnection fbBD = new FbConnection(FBDB.GetConnFBstring()))
{
fbBD.Open();
using (FbCommand sqlreq = new FbCommand("select sotrid from sumnu where god_v=@g and mes_v<=@m group by sotrid ", fbBD))
{
sqlreq.Parameters.AddWithValue("@g", Convert.ToString(god));
sqlreq.Parameters.AddWithValue("@m", Convert.ToString(mes));
FbDataReader r = sqlreq.ExecuteReader();
sotrlist.Clear();
while (r.Read())
{
sotrlist.Add(r.GetInt32(0));
}
;
};
fbBD.Close();
};
esn_list aresn = new esn_list();
esn_list aresnpred = new esn_list();
aresnpred.get_esn(212, new DateTime(god, mes, 1), new DateTime(god, mes, 31), true, true, false);
aresn.build_esn(212, god, mes);
int i;
for (i = 0; i < sotrlist.Count; i++)
{
esn_list aresn1 = aresn.getone(sotrlist[i]);
esn_list aresnpred1 = aresnpred.getone(sotrlist[i]);
rasesnone(god, mes, sotrlist[i], aresn1, aresnpred1);
}
;
}
public void build_esn(int org_id, int god, int mes)
{
this.Clear();
using (FbConnection fbBD = new FbConnection(FBDB.GetConnFBstring()))
{
fbBD.Open();
//Все суммы
string ssql = " SELECT s.sotrid,SUM(s.summa),s.esn_prov,s.schet,s.EX_SPRRASH_ID , dd.squad_id , s.god_v, s.mes_v,s.dts_id "
+ " FROM sumnu s,nachisl n,sotrudn so, dlg_to_struct dd "
+ " WHERE s.mes_v<=@mes_v AND s.god_v=@god_v AND s.nu_id=n.n_id and dd.unique_id=s.dts_id and dd.squad_id in (select newstruct.st_uid from newstruct where newstruct.st_org=@org) "
+ " AND NOT n.privid IN (58,581,1001,-98,-99,-50) AND n.u_n='+' AND n.is_virtual='F' AND s.sotrid=so.sotrudn_id "
+ " GROUP BY s.sotrid,s.esn_prov,s.schet,s.EX_SPRRASH_ID , dd.squad_id, s.god_v, s.mes_v ,s.dts_id "
+ " HAVING SUM(s.summa) IS NOT NULL and SUM(s.summa)<>0 "
+ " ORDER BY s.sotrid ";
using (FbCommand sqlReq = new FbCommand(ssql, fbBD))
{
sqlReq.Parameters.AddWithValue("@mes_v", mes);
sqlReq.Parameters.AddWithValue("@god_v", god);
sqlReq.Parameters.AddWithValue("@org", org_id);
FbDataReader r = sqlReq.ExecuteReader();
Zap_arESNall(0, org_id, god, mes, r);
r.Close();
r.Dispose();
// База ПФ
ssql = " SELECT s.sotrid,SUM(s.summa),s.esn_prov,s.schet,s.EX_SPRRASH_ID , dd.squad_id , s.god_v, s.mes_v,s.dts_id "
+ " FROM sumnu s,nachisl n,sotrudn so, dlg_to_struct dd "
+ " WHERE s.mes_v<=@mes_v AND s.god_v=@god_v AND s.nu_id=n.n_id and dd.unique_id=s.dts_id and dd.squad_id in (select newstruct.st_uid from newstruct where newstruct.st_org=@org) "
+ " AND NOT n.privid IN (58,581,1001,-98,-99,-50) AND n.u_n='+' AND n.is_virtual='F' AND s.sotrid=so.sotrudn_id "
+ " AND n.fp='T' "
+ " GROUP BY s.sotrid,s.esn_prov,s.schet,s.EX_SPRRASH_ID , dd.squad_id, s.god_v, s.mes_v ,s.dts_id "
+ " HAVING SUM(s.summa) IS NOT NULL and SUM(s.summa)<>0 "
+ " ORDER BY s.sotrid ";
sqlReq.CommandText = ssql;
sqlReq.Parameters.AddWithValue("@mes_v", mes);
sqlReq.Parameters.AddWithValue("@god_v", god);
sqlReq.Parameters.AddWithValue("@org", org_id);
r = sqlReq.ExecuteReader();
Zap_arESNall(1, org_id, god, mes, r);
r.Close();
r.Dispose();
//Матпомощь ПФ
ssql = " SELECT s.sotrid,SUM(s.summa),s.esn_prov,s.schet,s.EX_SPRRASH_ID , dd.squad_id , s.god_v, s.mes_v,s.dts_id "
+ " FROM sumnu s,nachisl n,sotrudn so, dlg_to_struct dd "
+ " WHERE s.mes_v<=@mes_v AND s.god_v=@god_v AND s.nu_id=n.n_id and dd.unique_id=s.dts_id AND n.fp='T' and dd.squad_id in (select newstruct.st_uid from newstruct where newstruct.st_org=@org) "
+ " AND(n.privid in (19,20,21,22,55,306)) AND NOT n.privid IN (-98,-99,-50) AND "
+ " s.sotrid=so.sotrudn_id "
+ " GROUP BY s.sotrid,s.esn_prov,s.schet,s.EX_SPRRASH_ID , dd.squad_id, s.god_v, s.mes_v ,s.dts_id "
+ " HAVING SUM(s.summa) IS NOT NULL ";
sqlReq.CommandText = ssql;
sqlReq.Parameters.AddWithValue("@mes_v", mes);
sqlReq.Parameters.AddWithValue("@god_v", god);
sqlReq.Parameters.AddWithValue("@org", org_id);
r = sqlReq.ExecuteReader();
Zap_arESNall(2, org_id, god, mes, r);
r.Close();
r.Dispose();
}
fbBD.Close();
}
}
public void get_esn(int org_id, DateTime db, DateTime de, bool naritog, bool notsumpfspf2, bool onlylockinmonth)
{
string qstr, period;
int wmonths = db.Month;
int wmonthe = de.Month;
int god = db.Year;
if (onlylockinmonth)
{
period = " e.year_=@year_ AND ((e.month_ BETWEEN @month_s AND @month_e1) or (e.month_=@month_e and e.lck=1)) ";
}
else
{
period = " e.year_=@year_ AND e.month_ BETWEEN @month_s AND @month_e ";
};
using (FbConnection fbBD = new FbConnection(FBDB.GetConnFBstring()))
{
fbBD.Open();
if (naritog)
{
qstr = "SELECT sum(e.all_sum) as all_sum, sum(e.baza_pf) as baza_pf,sum(e.baza_foms) as baza_foms,sum(e.baza_fss_osn) as baza_fss_osn, " //3
+ " sum(e.mp_pf) as mp_pf, sum(e.mp_fssosn) as mp_fssosn, sum(e.mp_foms) as mp_foms, " //6
+ " sum(e.baza_fss_pr) as baza_fss_pr,sum(e.fss_pr) as fss_pr,sum(e.fss_osn) as fss_osn, " //9
+ " sum(e.ffoms) as ffoms,sum(e.tfoms) as tfoms,sum(e.pf2) as pf2, " //12
+ " sum(e.pf3) as pf3, e.sotr_id,e.org_id, sum(e.rash_ss) as rash_ss,e.schet, " //17
+ " e.defprov, e.prov921,e.prov923,e.prov922,e.prov901,e.prov911,e.prov903,e.prov904,e.squadid, " //26
+ " sum(e.ne_pf) as ne_pf, sum(e.ne_fssosn) as ne_fssosn, sum(e.ne_foms) as ne_foms, e.SPRRASH_ID,0,e.tipsotr, " //32
+ " sum(e.pf_dopsotr) as pf_dopsotr,e.prov924,0,sum(e.mp_fsstr), e.tag ,sum(e.pf1) as pf1,sum(e.baza_rv3) as baza_rv3, " // //39
+ " sum(e.pf_rv3) as pf_rv3, 0, e.dts_id, sum(e.pf1_dopart)" //50
+ " FROM esn e "
+ " WHERE not e.all_sum is null and " + period;
qstr = qstr + " and e.org_id = " + org_id.ToString();
qstr = qstr + " group by e.sotr_id,e.org_id,e.schet, e.tag, " +
" e.defprov, e.prov921,e.prov923,e.prov922,e.prov901,e.prov911,e.prov903,e.prov904,e.squadid,e.SPRRASH_ID,e.prov924,e.tipsotr, e.dts_id";
this.Clear();
int t;
for (t = wmonths; t <= wmonthe; t++)
{
using (FbCommand sqlReq = new FbCommand(qstr, fbBD))
{
sqlReq.Parameters.AddWithValue("@month_s", 1);
sqlReq.Parameters.AddWithValue("@month_e", t);
sqlReq.Parameters.AddWithValue("@year_", god);
if (onlylockinmonth)
{
sqlReq.Parameters.AddWithValue("@month_e1", t);
}
;
FbDataReader r = sqlReq.ExecuteReader();
////////////
while (r.Read())
{
Esn_Class ecl = new Esn_Class();
ecl.zapolnesn(notsumpfspf2, r, god, t);
this.Add(ecl);
}
;
r.Close();
r.Dispose();
}
//////////////////
}
}
else
{
qstr = "SELECT e.all_sum, e.baza_pf,e.baza_foms,e.baza_fss_osn, "
+ " e.mp_pf, e.mp_fssosn, e.mp_foms, "
+ " e.baza_fss_pr,e.fss_pr,e.fss_osn, "
+ " e.ffoms,e.tfoms,e.pf2, e.pf3, "
+ " e.sotr_id,e.org_id, e.rash_ss,e.schet, "
+ " e.defprov, e.prov921,e.prov923,e.prov922,e.prov901,e.prov911,e.prov903,e.prov904,e.squadid, e.ne_pf,e.ne_fssosn,e.ne_foms, e.SPRRASH_ID,"
+ " e.month_,e.tipsotr, e.pf_dopsotr,e.prov924,e.id,e.mp_fsstr, e.tag, e.pf1, e.baza_rv3,e.pf_rv3, e.lck, e.dts_id, e.pf1_dopart " //51
+ " FROM esn e "
+ " WHERE not e.all_sum is null and " + period;
qstr = qstr + " and e.org_id = " + org_id.ToString();
using (FbCommand sqlReq = new FbCommand(qstr, fbBD))
{
sqlReq.Parameters.AddWithValue("@month_s", wmonths);
sqlReq.Parameters.AddWithValue("@month_e", wmonthe);
sqlReq.Parameters.AddWithValue("@year_", god);
if (onlylockinmonth)
{
sqlReq.Parameters.AddWithValue("@month_e1", wmonthe);
}
;
FbDataReader r = sqlReq.ExecuteReader();
////////////
this.Clear();
while (r.Read())
{
Esn_Class ecl = new Esn_Class();
ecl.zapolnesn(notsumpfspf2, r, god, r.GetInt32(31));
this.Add(ecl);
}
;
r.Close();
r.Dispose();
}
};
fbBD.Close();
}
}