public void setBookForReader(dbBook book, dbReader reader, int days)
{
Conn.SQLDA.SelectCommand.CommandText = "select * from Reservation_R..ADVORDER where INV = '" + book.inv + "'";
DataSet DS = new DataSet();
int c = Conn.SQLDA.Fill(DS, "t");
if (c != 0)
{
MessageBox.Show("���� ��������� ����� �� ���������� � �������� � ������� " + DS.Tables["t"].Rows[0]["IDREADER"].ToString() + "! ������� ���������� ����� ���������!");
return;
}
Conn.SQLDA.InsertCommand = new SqlCommand();
Conn.SQLDA.InsertCommand.Connection = Conn.ZakazCon;
if (Conn.ZakazCon.State != ConnectionState.Open) Conn.ZakazCon.Open();
Conn.SQLDA.InsertCommand.CommandText = "insert into Reservation_R..ISSUED (IDMAIN,BAR,DATE_VOZV,IDREADER,IDEMP,DATE_ISSUE,IDMAIN_CONST, " +
" PENALTY, REMPENALTY, INV, STATUS, IDDATA) values (@IDMAIN,@BAR,@DATE_VOZV,@IDREADER,@IDEMP,@DATE_ISSUE,@IDMAIN_CONST, " +
"@PENALTY, @REMPENALTY, @INV, @STATUS,@IDDATA)";
Conn.SQLDA.InsertCommand.Parameters.Add("IDMAIN", SqlDbType.Int);
Conn.SQLDA.InsertCommand.Parameters.Add("BAR", SqlDbType.NVarChar);
Conn.SQLDA.InsertCommand.Parameters.Add("DATE_VOZV", SqlDbType.DateTime);
Conn.SQLDA.InsertCommand.Parameters.Add("IDREADER", SqlDbType.Int);
Conn.SQLDA.InsertCommand.Parameters.Add("IDEMP", SqlDbType.Int);
Conn.SQLDA.InsertCommand.Parameters.Add("DATE_ISSUE", SqlDbType.DateTime);
Conn.SQLDA.InsertCommand.Parameters.Add("IDMAIN_CONST", SqlDbType.NVarChar);
Conn.SQLDA.InsertCommand.Parameters.Add("PENALTY", SqlDbType.Bit);
Conn.SQLDA.InsertCommand.Parameters.Add("REMPENALTY", SqlDbType.Bit);
Conn.SQLDA.InsertCommand.Parameters.Add("INV", SqlDbType.NVarChar);
Conn.SQLDA.InsertCommand.Parameters.Add("IDDATA", SqlDbType.Int);
Conn.SQLDA.InsertCommand.Parameters.Add("STATUS", SqlDbType.Int);
Conn.SQLDA.InsertCommand.Parameters["IDMAIN"].Value = book.id;
Conn.SQLDA.InsertCommand.Parameters["BAR"].Value = book.barcode;
Conn.SQLDA.InsertCommand.Parameters["DATE_VOZV"].Value = DateTime.Now.AddDays(days).ToShortDateString();
Conn.SQLDA.InsertCommand.Parameters["IDREADER"].Value = reader.id;
Conn.SQLDA.InsertCommand.Parameters["IDEMP"].Value = F1.EmpID;
Conn.SQLDA.InsertCommand.Parameters["DATE_ISSUE"].Value = DateTime.Now.ToShortDateString();
Conn.SQLDA.InsertCommand.Parameters["IDMAIN_CONST"].Value = book.id;
Conn.SQLDA.InsertCommand.Parameters["PENALTY"].Value = false;
Conn.SQLDA.InsertCommand.Parameters["REMPENALTY"].Value = false;
Conn.SQLDA.InsertCommand.Parameters["INV"].Value = book.inv;
Conn.SQLDA.InsertCommand.Parameters["STATUS"].Value = 3;
Conn.SQLDA.InsertCommand.Parameters["IDDATA"].Value = book.iddata;
Conn.SQLDA.InsertCommand.ExecuteNonQuery();
//book = book.Remove(book.Length - 1, 1);
//reader = reader.Remove(0, 1);
//reader = reader.Remove(reader.Length - 1, 1);
/*Conn.SQLDA.SelectCommand.CommandText = "select * from Reservation_R..ISSUED where ID = -1";
Conn.SQLDA.SelectCommand.Connection = Conn.ZakazCon;
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(Conn.SQLDA);
int i = Conn.SQLDA.Fill(Zakaz);
DataRow row = Zakaz.Tables[0].NewRow();
row["IDMAIN"] = book.id;
row["BAR"] = book.barcode;
row["DATE_VOZV"] = DateTime.Now.AddDays(days).ToShortDateString();
row["IDREADER"] = reader.id;
row["IDEMP"] = F1.EmpID;
row["DATE_ISSUE"] = DateTime.Now.ToShortDateString();
row["IDMAIN_CONST"] = book.id;
row["PENALTY"] = false;
row["REMPENALTY"] = false;
row["INV"] = book.inv;
Zakaz.Tables[0].Rows.Add(row);
//Conn.SQLDA.SelectCommand.CommandText = "select * from ZAKAZ where ID = -1";
//Conn.SQLDA.SelectCommand.Connection = Conn.ZakazCon;
//SQLDA.InsertCommand = cmdBuilder.GetInsertCommand();
Conn.SQLDA.Update(Zakaz.Tables[0]);*/
}
public bool isReaderHaveRights(dbReader r)
{
//r = r.Remove(0, 1);
//r = r.Remove(r.Length - 1, 1);
CultureInfo ci = new CultureInfo("en-US");
string date = DateTime.Now.ToString("d", ci);//SELECT ReaderRight.* FROM ReaderRight WHERE (((ReaderRight.DataEndReaderRight)=#10/20/2008#) AND ((ReaderRight.IDReader)=1) AND ((ReaderRight.IDReaderRight)=1));
//Conn.ReaderDA.SelectCommand.CommandText = "SELECT ReaderRight.* FROM ReaderRight WHERE (((ReaderRight.DataEndReaderRight)>#" + date + "#) AND ((ReaderRight.IDReader)=" + r.id + ") AND ((ReaderRight.IDReaderRight)=1))";
Conn.ReaderDA.SelectCommand.CommandText = "SELECT ReaderRight.* FROM ReaderRight WHERE ReaderRight.IDReader=" + r.id + " AND ReaderRight.IDReaderRight=4";
//"select * from ReaderRight where IDReader = " + this.getDbReader(r).id + " and IDReaderRight = 1 and DateEndReaderRight > (#"+date +"#)";//������ �������
//int i = ReaderDA.Fill(ReaderMain, "dbr");
DataSet R = new DataSet();
return (Conn.ReaderDA.Fill(R) == 0) ? false : true;
}
public dbReader(dbReader Reader)
{
this.barcode = Reader.barcode;
this.FIO = Reader.FIO;
this.id = Reader.id;
this.Surname = Reader.Surname;
this.Name = Reader.Name;
this.SecondName = Reader.SecondName;
this.AbonType = Reader.AbonType;
this.IsWasInOldBase = Reader.IsWasInOldBase;
this.RegInMos = Reader.RegInMos;
}
public DataTable GetIssuedBooks(DateTime start_, DateTime finish_)
{
Conn.SQLDA.SelectCommand.CommandText = "select X.IDMAIN, X.PLAIN, Y.SORT, Y.MNFIELD,Y.MSFIELD, (count(Z.BAR)) as sp, Z.DATE_VOZV,Z.DATE_ISSUE,Z.IDREADER " +
" from BJACC..DATAEXTPLAIN X " +
" join BJACC..DATAEXT Y on Y.ID=X.IDDATAEXT " +
" join Reservation_R..ISSUED Z on Z.IDMAIN = Y.IDMAIN " +
" join Reservation_R..ISSUED ZZ on Z.IDMAIN = ZZ.IDMAIN_CONST " +
" where ((Y.MNFIELD = 200 and Y.MSFIELD = '$a') or (Y.MSFIELD = '$a' and Y.MNFIELD = 700) " +
" or (Y.MSFIELD = '$p' and Y.MNFIELD = 899 and Y.SORT collate Cyrillic_General_CI_AI = Z.INV)) and (Z.DATE_ISSUE between '" + start_.ToString("yyyyMMdd") + "' and '" + finish_.ToString("yyyyMMdd") + "') " +
" group by X.PLAIN, Y.SORT, Y.MNFIELD,Y.MSFIELD, X.IDMAIN,Z.DATE_VOZV,Z.DATE_ISSUE,Z.IDREADER order by X.IDMAIN"; //inner join TECHNOLOG..ZAKAZ Y on Y.BAR=Z.BAR";
//Conn.SQLDA.SelectCommand.CommandText = "select X.PREOPS, X.PREOPSAUTHOR,count(Z.BAR) as �������������� from technolog..zakaz Z inner join BRIT_SOVET..MAIN X on Z.IDMAIN_CONST = X.ID group by X.PREOPS,X.PREOPSAUTHOR";
//Conn.SQLDA.SelectCommand.CommandText = "select BRIT_SOVET..MAIN.PREOPS, BRIT_SOVET..MAIN.PREOPSAUTHOR from technolog..zakaz inner join BRIT_SOVET..MAIN on ZAKAZ.IDMAIN = MAIN.ID";
Conn.SQLDA.SelectCommand.Connection = Conn.ZakazCon;
DataSet R = new DataSet();
DataSet D = new DataSet();
R.Tables.Add("vperemeshku");
R.Tables.Add("distinct");
int i = Conn.SQLDA.Fill(R.Tables["vperemeshku"]);
Conn.SQLDA.SelectCommand.CommandText = "select distinct Y.IDMAIN from BJACC..DATAEXT Y inner join Reservation_R..ISSUED Z on Z.IDMAIN = Y.IDMAIN where Z.IDMAIN != 0 and Z.INV collate Cyrillic_General_CI_AI = Y.SORT and Y.MNFIELD = 899 and Y.MSFIELD = '$p' order by Y.IDMAIN";
Conn.SQLDA.SelectCommand.Connection = Conn.ZakazCon; //
i = Conn.SQLDA.Fill(R.Tables["distinct"]);
R.Tables.Add("postolbcam");
R.Tables["postolbcam"].Columns.Add("Zagl");
R.Tables["postolbcam"].Columns.Add("Avtor");
R.Tables["postolbcam"].Columns.Add("Inv");
R.Tables["postolbcam"].Columns.Add("sprash");
R.Tables["postolbcam"].Columns.Add("ZagSort");
R.Tables["postolbcam"].Columns.Add("AvtorSort");
R.Tables["postolbcam"].Columns.Add("NN");
R.Tables["postolbcam"].Columns.Add("FIO");
R.Tables["postolbcam"].Columns.Add("abn");
R.Tables["postolbcam"].Columns.Add("diss");
R.Tables["postolbcam"].Columns.Add("dvzv");
R.Tables["postolbcam"].Columns["diss"].DataType = typeof(DateTime);
R.Tables["postolbcam"].Columns["dvzv"].DataType = typeof(DateTime);
DataRow ARow = R.Tables["postolbcam"].NewRow();
string id = R.Tables["vperemeshku"].Rows[0]["IDMAIN"].ToString();
ARow["dvzv"] = DateTime.Parse(R.Tables["vperemeshku"].Rows[0]["DATE_VOZV"].ToString()).ToString();
ARow["diss"] = DateTime.Parse(R.Tables["vperemeshku"].Rows[0]["DATE_ISSUE"].ToString()).ToString();
dbReader rdr = new dbReader(int.Parse(R.Tables["vperemeshku"].Rows[0]["IDREADER"].ToString()));
ARow["NN"] = rdr.id;
ARow["FIO"] = rdr.FIO;
ARow["abn"] = R.Tables["vperemeshku"].Rows[0]["SORT"].ToString();//rdr.AbonType;
ARow["sprash"] = R.Tables["vperemeshku"].Rows[0]["sp"].ToString();
foreach (DataRow row in R.Tables["vperemeshku"].Rows)
{
if (id != row["IDMAIN"].ToString())
{
R.Tables["postolbcam"].Rows.Add(ARow);
ARow = R.Tables["postolbcam"].NewRow();
id = row["IDMAIN"].ToString();
ARow["sprash"] = row["sp"].ToString();
rdr = new dbReader(int.Parse(row["IDREADER"].ToString()));
ARow["NN"] = rdr.id;
ARow["FIO"] = rdr.FIO;
ARow["abn"] = row["SORT"].ToString();//rdr.AbonType;
}
switch (row["MNFIELD"].ToString() + row["MSFIELD"].ToString())
{
case "200$a":
ARow["Zagl"] = row["PLAIN"].ToString();
ARow["ZagSort"] = row["SORT"].ToString();
ARow["dvzv"] = DateTime.Parse(row["DATE_VOZV"].ToString()).ToString();
ARow["diss"] = DateTime.Parse(row["DATE_ISSUE"].ToString()).ToString();
break;
case "700$a":
ARow["Avtor"] = row["PLAIN"].ToString();
ARow["AvtorSort"] = row["SORT"].ToString();
break;
case "899$p":
ARow["Inv"] = row["SORT"].ToString();
break;
}
}
R.Tables["postolbcam"].Rows.Add(ARow);
return R.Tables["postolbcam"];
/*R.Tables.Add();
int i = Conn.SQLDA.Fill(R.Tables[0]);
return R;*/
}
internal void InsertActionRETURNED(dbReader reader, dbBook book)
{
Conn.SQLDA.InsertCommand = new SqlCommand();
Conn.SQLDA.InsertCommand.Connection = Conn.ZakazCon;
if (Conn.ZakazCon.State != ConnectionState.Open) Conn.ZakazCon.Open();
Conn.SQLDA.InsertCommand.CommandText = "insert into Reservation_R..ABONEMENTACTIONS (ACTIONTYPE,BAR,IDEMP,IDREADER,DATEACT) " +
" values (@ACTIONTYPE,@BAR,@IDEMP,@IDREADER,@DATEACT)";
Conn.SQLDA.InsertCommand.Parameters.Add("ACTIONTYPE", SqlDbType.Int);
Conn.SQLDA.InsertCommand.Parameters.Add("BAR", SqlDbType.NVarChar);
Conn.SQLDA.InsertCommand.Parameters.Add("IDEMP", SqlDbType.Int);
Conn.SQLDA.InsertCommand.Parameters.Add("IDREADER", SqlDbType.Int);
Conn.SQLDA.InsertCommand.Parameters.Add("DATEACT", SqlDbType.DateTime);
Conn.SQLDA.InsertCommand.Parameters["ACTIONTYPE"].Value = 2;
Conn.SQLDA.InsertCommand.Parameters["BAR"].Value = book.barcode;
Conn.SQLDA.InsertCommand.Parameters["IDEMP"].Value = this.F1.EmpID;
Conn.SQLDA.InsertCommand.Parameters["IDREADER"].Value = book.rid;
Conn.SQLDA.InsertCommand.Parameters["DATEACT"].Value = DateTime.Now;
try
{
Conn.SQLDA.InsertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ". �� ��������� ���������������� �������� - �������. ���������� � ������������.");
}
}
