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 + ". �� ��������� ���������������� �������� - �������. ���������� � ������������."); } }