public static bool IsAvailable(string columnName, string value)
{
bool available = false;
if (!String.IsNullOrWhiteSpace(columnName) && value != null)
{
try
{
using (OracleConnection connection = new OracleConnection(CONNECTION_STRING))
{
OracleCommand command = new OracleCommand();
command.CommandText = String.Format("SELECT COUNT(*) FROM Users WHERE {0} LIKE :value",columnName);
command.Parameters.Add(":value", OracleDbType.NVarchar2).Value = value;
command.Connection = connection;
connection.Open();
int count = Convert.ToInt32(command.ExecuteScalar());
if (count==0)
available = true;
else
available = false;
}
}
catch (Exception e)
{
available = false;
Logger.LogException(e);
}
}
return available;
}
public object ExecuteScalar(string sql)
{
object value = null;
try
{
var con = CONNECTION.OpenCon();
var cmd = new OracleCommand(sql, con);
value = cmd.ExecuteScalar();
cmd.Dispose();
CONNECTION.CloseCon(con);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ExecuteScalar Error!",
Ex = ex,
});
return null;
}
return value;
}
protected void btnadd_Click(object sender, EventArgs e)
{
string ctc, ctd, dcc, poc;
string ad = "";
int nowh;
ctc = txtctc.Text;
ctd = txtctd.Text;
dcc = ddldcc.SelectedValue;
poc = txtpoc.Text;
if (txtnowh.Text == "")
{
nowh = 0;
}
else
{
nowh = int.Parse(txtnowh.Text);
}
int m;
string str = "select count(*) from ct where ctc='" + txtctc.Text + "' and dcc='"+ddldcc.Text+"'";
con.Open();
OracleCommand cmdcount = new OracleCommand(str, con);
m = int.Parse(cmdcount.ExecuteScalar().ToString());
if (m == 1)
{
lblctmsg.Text = "code already exists";
}
else
{
string s = "insert into ct values('" + ctc + "','" + ctd + "','" + poc + "','" + nowh + "','" + dcc + "')";
OracleCommand cmd = new OracleCommand(s, con);
cmd.ExecuteNonQuery();
lblctmsg.Text = " values inserted successfully ";
}
con.Close();
}
protected void btnadd_Click(object sender, EventArgs e)
{
string stcd, stdes;
stcd = txtstcd.Text;
stdes = txtstdes.Text;
int z;
string s = "select count(*) from Sm where stcd='" + stcd + "'";
con.Open();
OracleCommand cmdcount = new OracleCommand(s, con);
z = int.Parse(cmdcount.ExecuteScalar().ToString());
if (z == 1)
{
lblsmmsg.Text = "Code already exists";
}
else
{
string str = "insert into Sm values('" + stcd + "','" + stdes + "')";
OracleCommand cmd = new OracleCommand(str, con);
cmd.ExecuteNonQuery();
lblsmmsg.Text = "Values inserted Successfully";
txtstcd.Text = "";
txtstdes.Text = "";
}
con.Close();
}
internal List<LXJHGLInstance> ImportTasks(List<COMMON.LXJHGLInstance> tasks)
{
List<LXJHGLInstance> duplicated = new List<LXJHGLInstance>();
this.dbParam.Open();
OracleCommand queryCmd = new OracleCommand();
queryCmd.Connection = (OracleConnection)this.dbParam.Connection;
queryCmd.CommandText = "SELECT count(*) FROM dq_route_taskmng WHERE ID=:ID and VERSION=:VERSION";
queryCmd.Parameters.Add(":ID", OracleDbType.NVarchar2);
queryCmd.Parameters.Add(":VERSION", OracleDbType.Int32);
OracleCommand insertCmd = new OracleCommand();
insertCmd.Connection = (OracleConnection)this.dbParam.Connection;
insertCmd.CommandText = @"INSERT INTO dq_route_taskmng(ID,NAME,VERSION,RELEASER,RELEASETIME,TYPE,CREATOR,PLANEDTIME,TASKCREATIME,STATUS,DIFFICULTY)
values(:ID,:NAME,:VERSION,:RELEASER,:RELEASETIME,:TYPE,:CREATOR,:PLANEDTIME,:TASKCREATIME,:STATUS,:DIFFICULTY) ";
insertCmd.Parameters.Add(":ID", OracleDbType.NVarchar2);
insertCmd.Parameters.Add(":NAME", OracleDbType.NVarchar2);
insertCmd.Parameters.Add(":VERSION", OracleDbType.Int32);
insertCmd.Parameters.Add(":RELEASER", OracleDbType.NVarchar2);
insertCmd.Parameters.Add(":RELEASETIME", OracleDbType.Date);
insertCmd.Parameters.Add(":TYPE", OracleDbType.NVarchar2);
insertCmd.Parameters.Add(":CREATOR", OracleDbType.NVarchar2);
insertCmd.Parameters.Add(":PLANEDTIME", OracleDbType.Date);
insertCmd.Parameters.Add(":TASKCREATIME", OracleDbType.Date);
insertCmd.Parameters.Add(":STATUS", OracleDbType.Int32);
insertCmd.Parameters.Add(":DIFFICULTY", OracleDbType.Int32);
foreach (var task in tasks)
{
queryCmd.Parameters[":ID"].Value = task.Id;
queryCmd.Parameters[":VERSION"].Value = task.Version;
object cnt = queryCmd.ExecuteScalar();
if (cnt != null && Convert.ToInt32(cnt) > 0) duplicated.Add(task);
else
{
insertCmd.Parameters[":ID"].Value = task.Id;
insertCmd.Parameters[":NAME"].Value = task.Name;
insertCmd.Parameters[":VERSION"].Value = task.Version;
insertCmd.Parameters[":RELEASER"].Value = task.Releaser;
insertCmd.Parameters[":RELEASETIME"].Value = task.Releasetime;
insertCmd.Parameters[":TYPE"].Value = task.Type;
insertCmd.Parameters[":CREATOR"].Value = task.Creator;
insertCmd.Parameters[":PLANEDTIME"].Value = task.Planedtime;
insertCmd.Parameters[":TASKCREATIME"].Value = task.Taskcreatime;
insertCmd.Parameters[":STATUS"].Value = LXJHGLStatus.未分配;
insertCmd.Parameters[":DIFFICULTY"].Value = task.Difficulty;
insertCmd.ExecuteNonQuery();
}
}
this.dbParam.Commit();
this.dbParam.Close();
return duplicated;
}
protected void Button1_Click(object sender, EventArgs e)
{
string CS = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
OracleConnection conn = new OracleConnection(CS);
OracleCommand camd = new OracleCommand("select count(*) from USER_TABLE where U_NAME = '" + TextBox1.Text + "'", conn);
conn.Open();
temp = Convert.ToInt32(camd.ExecuteScalar().ToString());
if (temp == 1)
{
using (OracleCommand cmd = new OracleCommand("insert into ADDRESS (APP_ADD_ID,APP_NAME,FLOOR_NUM,ROAD,APP_ADD,AREA_NAME,CITY) values (U_ID_SEQ.NEXTVAL,'" + TextBoxAppName.Text + "','" + TextBoxFloor.Text + "','" + TextBoxRoad.Text + "','" + TextBoxAppArea.Text + "','" + TextBoxCity.Text + "','" + TextBoxCountry.Text + "')", conn))
{
cmd.ExecuteNonQuery();
using (OracleCommand comd = new OracleCommand("select APP_ADD_ID from ADDRESS where APP_NAME = '" + TextBoxAppName.Text + "'", conn))
{
using (OracleCommand oracle = new OracleCommand("select USER_ID from USER_TABLE where U_NAME='" + TextBox1.Text + "'", conn))
{
string u_id = oracle.ExecuteScalar().ToString();
Session["U_ID"] = u_id;
string id = comd.ExecuteScalar().ToString();
Session["ADDId"] = id;
Response.Redirect("~/Allapartments.aspx");
}
//string id = comd.ExecuteScalar().ToString();
//Session["ADDId"] = id;
//Response.Redirect("~/Allapartments.aspx");
}
}
}
else
{
Label1.ForeColor = System.Drawing.Color.Red;
Label1.Text = "Name is not correct";
}
//conn.Open();
//if (Convert.ToBoolean(camd.ExecuteNonQuery()))
//{
// //using(OracleCommand comd = new OracleCommand("select APP_ADD_ID from ADDRESS where APP_NAME = '" +TextBoxAppName.Text + "'"))))
// Label1.ForeColor = System.Drawing.Color.Green;
// Label1.Text = "Data Saved In The Database";
//}
//else
//{
// Label1.ForeColor = System.Drawing.Color.Red;
// Label1.Text = "Data Cannot Saved In The Database";
//}
}
protected object getValue(string query)
{
object result = new object();
OracleCommand cmd = new OracleCommand();
cmd.Connection = Connection.ConnectionData();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
Connection.OpenConnection();
result = cmd.ExecuteScalar();
Connection.CloseConnection();
return result;
}
protected void Page_Load(object sender, EventArgs e) {
int companyCount = 0;
using(var conn = new OracleConnection(ConfigTool.GetConnectionString("LOCAL_XE")))
using(var cmd = new OracleCommand("SELECT COUNT(*) FROM NH_COMPANY", conn)) {
conn.Open();
companyCount = cmd.ExecuteScalar().AsInt();
}
lblMessage.Text = "CompanyCount=" + companyCount;
lblMessage2.Text = "CompanyCount=" + Repository<Company>.Count();
}
private void FormTipster_Load(object sender, EventArgs e)
{
lblUser.Text = FormLogin.translator["Username " + FormLogin.currLanguage];
lblFirst.Text = FormLogin.translator["FirstName " + FormLogin.currLanguage];
lblSur.Text = FormLogin.translator["LastName " + FormLogin.currLanguage];
lblE.Text = FormLogin.translator["Email " + FormLogin.currLanguage];
lblM.Text = FormLogin.translator["Money " + FormLogin.currLanguage];
btnTransaction.Text = FormLogin.translator["SendMoney " + FormLogin.currLanguage];
using (OracleConnection conn = new OracleConnection(FormLogin.connString))
{
conn.Open();
string query = "select * from Tipster where idTipster = " + idTipster;
OracleCommand command = new OracleCommand(query, conn);
command.CommandType = CommandType.Text;
OracleDataReader reader = command.ExecuteReader();
reader.Read();
lblUsername.Text = reader.GetString(1);
lblFirstName.Text = reader.GetString(3);
lblSurname.Text = reader.GetString(4);
lblMoney.Text = reader.GetInt32(5).ToString();
lblEmail.Text = reader.GetString(6);
query = "select COUNT(*) + 1 FROM Tipster WHERE Money > " + Int32.Parse(lblMoney.Text);
command = new OracleCommand(query, conn);
command.CommandType = CommandType.Text;
lblRank.Text = command.ExecuteScalar().ToString();
reader.Close();
}
if (idTipster != FormLogin.IdLoggedTipster)
{
btnTransaction.Visible = true;
}
else
{
changeView();
}
setDataGrid();
}
private bool isRegulated()
{
if (username.Trim().Length == 0)
{
MessageBox.Show("Username is empty!");
return false;
}
if (password.Trim().Length == 0 || repPassword.Trim().Length == 0)
{
MessageBox.Show("One of the password fields is empty!");
return false;
}
if (!password.Equals(repPassword))
{
MessageBox.Show("Passwords do not match.");
return false;
}
username = username.Trim();
using (OracleConnection conn = new OracleConnection(FormLogin.connString)) // using avtomatski ja zatvora otvorenata konekcija
{
conn.Open();
// mozhebi bi bilo podobro da se realizira so funkcija na nivo na baza
string query = "SELECT COUNT(*) FROM Tipster WHERE UPPER(username) LIKE '" + username.ToUpper() + "'";
OracleCommand commCheck = new OracleCommand(query, conn);
commCheck.CommandType = CommandType.Text;
Object o = commCheck.ExecuteScalar();
int numRows = Int32.Parse(o.ToString());
if (numRows != 0)
{
MessageBox.Show("Already exists tipster with that username.");
conn.Clone(); // za sekoj slucaj
return false;
}
}
return true;
}
protected void btnsbmt_Click(object sender, EventArgs e)
{
string uid, pwd;
uid = txtaname.Text;
pwd = txtpwd.Text;
con.Open();
OracleCommand cmd = new OracleCommand("select count(*) from cou where userid='" + uid + "' and pwd='" + pwd + "'", con);
int c=int.Parse(cmd.ExecuteScalar().ToString());
if (c == 1)
{
Response.Redirect("http://localhost/P_CRMS/website/Home.aspx");
}
else
{
lblloginmsg.Text = "Enter correct userid and password";
}
}
public bool AddNewCategory(Categorie newCat)
{
bool success = false;
int newCatId = 0;
connection.Open();
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.CommandText = "SELECT MAX(CATEGORIEID) FROM CATEGORIE";
newCatId = (int)((decimal)command.ExecuteScalar() + 1);
newCat.CategorieID = newCatId;
command.CommandText = "INSERT INTO CATEGORIE VALUES (:id, :naam, :omschrijving, :parent)";
OracleParameter parID = new OracleParameter("id", newCat.CategorieID);
OracleParameter parNaam = new OracleParameter("naam", newCat.Categorienaam);
OracleParameter parOmschrijving = new OracleParameter("omschrijving", newCat.Omschrijving);
OracleParameter parParent = new OracleParameter("parent", newCat.SubcategorieVan);
command.Parameters.Add(parID);
command.Parameters.Add(parNaam);
command.Parameters.Add(parOmschrijving);
command.Parameters.Add(parParent);
try
{
if (command.ExecuteNonQuery() == 1)
{
success = true;
}
}
catch (OracleException ex)
{
Console.WriteLine("Record is not inserted into the database table.");
Console.WriteLine("Exception Message: " + ex.Message);
Console.WriteLine("Exception Source: " + ex.Source);
}
finally
{
connection.Close();
}
return success;
}
protected void btndlt_Click(object sender, EventArgs e)
{
con.Open();
string s = "select count(*) from Sm where stcd= '" + txtstcd.Text + "'";
OracleCommand cmd = new OracleCommand(s, con);
int c;
c = int.Parse(cmd.ExecuteScalar().ToString());
if (c == 1)
{
string str = "delete from Sm where stcd= '" + txtstcd.Text + "'";
OracleCommand cmddel = new OracleCommand(str, con);
cmddel.ExecuteNonQuery();
lblsmmsg.Text = "Values Deleted";
con.Close();
}
txtstcd.Text = "";
txtstdes.Text = "";
}
public static string ReadRecordValueFromEFTestDB(string tableName, string fieldName, string whereClause)
{
string ret = string.Empty;
try
{
using (OracleConnection conn = new OracleConnection(EF_SQL_ConnectionString))
{
conn.Open();
using (OracleCommand cmd = new OracleCommand(string.Format("SELECT t.{0} FROM {1} t where {2}", fieldName, tableName, whereClause), conn))
{
ret = cmd.ExecuteScalar().ToString();
}
conn.Close();
}
}
catch { }
return ret;
}
public static int ReadRecordCountFromEFTestDB(string tableName)
{
int ret = 0;
try
{
using(OracleConnection conn = new OracleConnection(EF_SQL_ConnectionString))
{
conn.Open();
using(OracleCommand cmd = new OracleCommand(string.Format("SELECT COUNT(*) FROM {0}", tableName), conn))
{
ret = Convert.ToInt32(cmd.ExecuteScalar());
}
conn.Close();
}
}
catch { }
return ret;
}
void validate()
{
try
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = connString;
conn.Open();
DatabaseAccessForReporting obj = new DatabaseAccessForReporting();
obj.EstablishConnection();
OracleCommand command = new OracleCommand("Select role from finlogin where username= :username and password= :password", conn);
command.Parameters.Add(":username", finUserName);
command.Parameters.Add(":password", finPwd);
finDept = command.ExecuteScalar().ToString();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public string ValidateWarehouseCode(string warehouseCode, string username, string password)
{
OracleConnection conn = null;
string sql = "select count(*) from sga_littmoden.wms_warehouse w where w.warehouse_code= '" + warehouseCode + "'";
string result = "OK";
try
{
conn = new OracleConnection("Data Source=orclmlx; User Id=mlxsga_protein_sivart; Password=mlxsga_protein_sivart;");
conn.Open();
OracleCommand cmd = new OracleCommand(sql, conn);
object res = cmd.ExecuteScalar();
if (res != null)
{
if (Convert.ToInt32(res) > 0)
{
//OK
}
else
{
result = "Error, warehouse code not found. Found " + res.ToString() + " results.";
}
}
else
{
result = "Error, in query (" + sql + ")";
}
cmd.Dispose();
}
catch (Exception ex)
{
result = "Exception, " + sql + " " + ex.ToString();
}
finally
{
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
return result;
}
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = Request.QueryString["id"];
string cs = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
OracleConnection conn = new OracleConnection(cs);
using (OracleCommand cmd = new OracleCommand("select APP_TYPE from APARTMENT where APP_ID = '" + Label1.Text + "'", conn))
{
conn.Open();
string type = cmd.ExecuteScalar().ToString();
TextBox1.Text = type;
}
using (OracleCommand cmd1 = new OracleCommand("select APP_SIZE from APARTMENT where APP_ID = '" + Label1.Text + "'", conn))
{
//conn.Open();
string type = cmd1.ExecuteScalar().ToString();
TextBox2.Text = type;
}
using (OracleCommand cmd2 = new OracleCommand("select APP_DATE from APARTMENT where APP_ID = '" + Label1.Text + "'", conn))
{
//conn.Open();
string type = cmd2.ExecuteScalar().ToString();
TextBox3.Text = type;
}
using (OracleCommand cmd3 = new OracleCommand("select PRICE from APARTMENT where APP_ID = '" + Label1.Text + "'", conn))
{
//conn.Open();
string type = cmd3.ExecuteScalar().ToString();
TextBox4.Text = type;
}
using (OracleCommand cmd4 = new OracleCommand("select NO_OF_ROOMS from APARTMENT where APP_ID = '" + Label1.Text + "'", conn))
{
//conn.Open();
string type = cmd4.ExecuteScalar().ToString();
TextBox5.Text = type;
}
using (OracleCommand cmd5 = new OracleCommand("select FEATURE from APARTMENT where APP_ID = '" + Label1.Text + "'", conn))
{
//conn.Open();
string type = cmd5.ExecuteScalar().ToString();
TextBox6.Text = type;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string cs = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
OracleConnection conn = new OracleConnection(cs);
using (OracleCommand cmd = new OracleCommand("delete from APARTMENT where APP_ID = '" + DropDownList1.SelectedItem.Text + "'", conn))
{
conn.Open();
cmd.ExecuteScalar();
//GridView1.DataBind();
using (OracleCommand comd = new OracleCommand("select * from APARTMENT where USER_ID = '" + Session["ID"] + "'", conn))
{
OracleDataAdapter da = new OracleDataAdapter(comd);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string CS = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
OracleConnection conn = new OracleConnection(CS);
using (OracleCommand cmd = new OracleCommand("select count(*) from USER_TABLE where U_NAME = '" + TextBox1.Text + "' ", conn))
{
conn.Open();
temp=Convert.ToInt32(cmd.ExecuteScalar().ToString());
if (temp == 1)
{
OracleCommand oraclecmd = new OracleCommand("select U_PASS from USER_TABLE where U_NAME = '" + TextBox1.Text + "' ", conn);
string pass = oraclecmd.ExecuteScalar().ToString();
if (pass == TextBox2.Text)
{
Label1.ForeColor = System.Drawing.Color.Green;
Label1.Text = "You are logged in";
Session["U_NAME"] = TextBox1.Text;
//string cs = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
//OracleConnection connn = new OracleConnection(cs);
using (OracleCommand comd = new OracleCommand("select USER_ID from USER_TABLE where U_NAME = '" + TextBox1.Text + "' ", conn))
{
//conn.Open();
string id = comd.ExecuteScalar().ToString();
Session["ID"] = id;
}
Response.Redirect("~/Address.aspx" );
}
else
{
Label1.ForeColor = System.Drawing.Color.Red;
Label1.Text = "Wrong Password";
}
}
else
{
Label1.ForeColor = System.Drawing.Color.Red;
Label1.Text = "Wrong Username";
}
}
}
public IDomainIdentifiable<long> Create(IDomainIdentifiable<long> entity)
{
_context.OpenConnection();
_context.BeginTransaction();
// get nextval
const string sqlGetNextId = "SELECT MAX(id) + 1 FROM muscle_group";
var command = new OracleCommand(sqlGetNextId, _context.DbConnection);
object obj = command.ExecuteScalar();
entity.Id = Convert.ToInt64(obj);
// insert
const string sqlInsert = "INSERT INTO muscle_group (id, name) VALUES (:id, :name)";
command = new OracleCommand(sqlInsert, _context.DbConnection);
AddIdParam(command, entity.Id);
AddColumnParams(command, (MuscleGroup)entity);
command.Prepare();
command.ExecuteNonQuery();
return entity;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (DBHelper.isDoubleDatabase)
{
tdIsDouble.InnerText = "√";
}
else
{
tdIsDouble.InnerText = "X";
}
Object obj;
obj = DBOpt.dbHelper.ExecuteScalar("select count(*) from DMIS_SYS_ROLE");
if (obj != null)
{
tdMainServerStatus.InnerText = "√";
}
else
{
tdMainServerStatus.InnerText = "X";
}
if (OracleHelper.isDoubleDatabase)
{
OracleConnection slaveConn = new OracleConnection(DBHelper.slaveConnectString);
OracleCommand slaveCmd = new OracleCommand();
try
{
if (slaveConn.State != ConnectionState.Open)
{
slaveConn.Open();
}
slaveCmd.Connection = slaveConn;
slaveCmd.CommandText = "select count(*) from DMIS_SYS_ROLE";
slaveCmd.CommandType = CommandType.Text;
obj = slaveCmd.ExecuteScalar();
if (obj != null)
{
tdSlaveServerStatus.InnerText = "√";
}
else
{
tdSlaveServerStatus.InnerText = "X";
}
}
catch (Exception ex)
{
tdSlaveServerStatus.InnerText = "×";
OracleHelper.LogError("备服务器", "测试", ex.Message);
}
finally
{
if (slaveConn.State == ConnectionState.Open)
{
slaveConn.Close();
}
}
}
}
}
private void Button2_Click(object sender, EventArgs e)
{
if (button2.Text == "Approve")
{
conn.Close();
conn.Open();
query = "select count(id_pegawai) from hak_akses where id_pegawai = '" + id_pegawai + "' and id_hak_akses = 'HA003'";
cmd = new OracleCommand(query, conn);
int bolehapprove = int.Parse(cmd.ExecuteScalar().ToString());
query = "select count(id_pegawai) from hak_akses where id_pegawai = '" + id_pegawai + "' and id_hak_akses = 'HA006'";
cmd = new OracleCommand(query, conn);
bolehapprove += int.Parse(cmd.ExecuteScalar().ToString());
if (bolehapprove > 0)
{
query = "Update order_header set status_order = 1 where id_order = '" + id_order + "' ";
cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
listorder lo = new listorder();
Hide();
lo.ShowDialog();
Close();
}
else
{
MessageBox.Show("Tidak Memiliki Akses");
}
conn.Close();
}
else if (button2.Text == "Make Order")
{
conn.Close();
conn.Open();
query = "Update order_header set status_order = 6 where id_order = '" + id_order + "' ";
cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
listorder lo = new listorder();
Hide();
lo.ShowDialog();
Close();
conn.Close();
}
else if (button2.Text == "Receive")
{
conn.Close();
conn.Open();
query = "Update order_header set status_order = 4 where id_order = '" + id_order + "' ";
cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
listorder lo = new listorder();
Hide();
lo.ShowDialog();
Close();
conn.Close();
}
}
void listaLokaciiNaracka_SelectedIndexChanged(object sender, EventArgs e)
{
Label pom = ((Label)tabela.FindControl("adresaLbl"));
Button kopce = (Button)vnes.FindControl("barajNarackaBtn");
DropDownList lok = ((DropDownList)vnes.FindControl("lokaciiLista"));
try
{
if (lok.Items[lok.SelectedIndex].Value != "0")
{
String komanda = "Select adresa from lokacija where lokacija.id=" + ((DropDownList)sender).Items[((DropDownList)sender).SelectedIndex].Value.ToString();
OCon.Open();
OracleCommand OCom = new OracleCommand(komanda, OCon);
String adresa = OCom.ExecuteScalar().ToString();
pom.Text = adresa;
OCon.Close();
}
}
catch (Exception ex)
{
OCon.Close();
}
this.barajNarackaBtn_Click(kopce, new EventArgs());
}
void potvrdiBtn_Click(object sender, EventArgs e)
{
DropDownList lok = ((DropDownList)tabela.FindControl("lokaciiLista"));
TextBox kom = ((TextBox)tabela.FindControl("komentarTexBox"));
try
{
OCon.Open();
String komanda = "Insert into naracka(komitent_id,korisnik_id,lokacija_id,datumnaracka,komentar) values(" + komitentId.Trim() + "," + korisnikId.Trim() + "," + lok.Items[lok.SelectedIndex].Value.ToString().Trim() + ",to_Date('" + DateTime.Now.ToShortDateString() + "','mm/dd/yyyy'),'" + kom.Text.ToString().Trim() + "')";
OracleCommand OCom = new OracleCommand(komanda, OCon);
OCom.ExecuteNonQuery();
komanda = "Select id from naracka where naracka.komitent_id=" + komitentId.Trim().ToString() + " and naracka.korisnik_id=" + korisnikId.Trim() + " and naracka.lokacija_id=" + lok.Items[lok.SelectedIndex].Value.ToString().Trim() + " order by id DESC";
OCom = new OracleCommand(komanda, OCon);
narackaId = OCom.ExecuteScalar().ToString();
if (info.ContainsKey("NarackaId"))
{
info["NarackaId"] = narackaId;
}
else
{
info.Add("NarackaId", narackaId);
}
stranica.Session["Korisnik"] = info;
}
catch (Exception ex)
{
OCon.Close();
}
stranica.Response.Redirect("Naracaj.aspx?Mod=1&A=2");
}
protected void btn1_Click(object sender, EventArgs e)
{
string cd, cname, add, hno, street, area, emailid;
long phno;
int fax, pincd;
cd = txtcd.Text;
cname = txtname.Text;
add = txtadd.Text;
hno = txtHno.Text;
street = txtStr.Text;
area = txtArea.Text;
if (txtpincd.Text == "")
{
pincd = 0;
}
else
{
pincd = int.Parse(txtpincd.Text);
}
if (txtphno.Text == "")
{
phno = 0;
}
else
{
phno = long.Parse(txtphno.Text);
}
emailid = txteid.Text;
if (txtfax.Text == "")
{
fax = 0;
}
else
{
fax = int.Parse(txtfax.Text);
}
int b;
string s = "select count(*) from WCCntrMaster where WARDCCD='" + txtcd.Text + "'";
con.Open();
OracleCommand cmdcount = new OracleCommand(s, con);
b = int.Parse(cmdcount.ExecuteScalar().ToString());
if (b == 1)
{
lblmsg.Text = "Code already exists";
txtcd.Text = "";
txtname.Text = "";
txtadd.Text = "";
txtHno.Text = "";
txtStr.Text = "";
txtArea.Text = "";
txtpincd.Text = "";
txtphno.Text = "";
txteid.Text = "";
txtfax.Text = "";
}
else
{
string str = "insert into WCCntrMaster values('" + cd + "','" + cname + "','" + add + "','" + hno + "','" + street + "','" + area + "'," + pincd + "," + phno + ",'" + emailid + "'," + fax + ")";
OracleCommand cmd = new OracleCommand(str, con);
cmd.ExecuteNonQuery();
lblmsg.Text = "Values inserted successfully";
txtcd.Text = "";
txtname.Text = "";
txtadd.Text = "";
txtHno.Text = "";
txtStr.Text = "";
txtArea.Text = "";
txtpincd.Text = "";
txtphno.Text = "";
txteid.Text = "";
txtfax.Text = "";
}
con.Close();
}
/// <summary>
/// 返回第一行第一列
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns></returns>
public string ExecuteScalar(string strSql)
{
try
{
if (string.IsNullOrEmpty(m_strConn))
{
GetDBConnection();
}
if (m_CurrentDataBaseType == DataBaseType.Oracle)
{
OracleCommand cmd = new OracleCommand(strSql);
cmd.CommandTimeout = 180;
using (OracleConnection conn = new OracleConnection(m_strConn))
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
string val = string.Empty;
object obj = cmd.ExecuteScalar();
if (obj != null)
{
val = obj.ToString();
}
conn.Close();
return(val);
}
}
else if (m_CurrentDataBaseType == DataBaseType.SqlServer)
{
SqlCommand cmd = new SqlCommand(strSql);
cmd.CommandTimeout = 180;
using (SqlConnection conn = new SqlConnection(m_strConn))
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
string val = string.Empty;
object obj = cmd.ExecuteScalar();
if (obj != null)
{
val = obj.ToString();
}
conn.Close();
return(val);
}
}
else if (m_CurrentDataBaseType == DataBaseType.Mysql)
{
MySqlCommand mcmd = new MySqlCommand(strSql);
mcmd.CommandTimeout = 180;
using (MySqlConnection conn = new MySqlConnection(m_strConn))
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
mcmd.Connection = conn;
string val = string.Empty;
object obj = mcmd.ExecuteScalar();
if (obj != null)
{
val = obj.ToString();
}
conn.Close();
return(val);
}
}
return(string.Empty);
}
catch (Exception err)
{
throw err;
}
}
public void BtnAdd_Click(object sender, EventArgs e)
{
// try
// {
if (IS_ADD_ACTIVE == "Enable")
{
OracleConnection conn = new OracleConnection(strConnString);
conn.Open();
int userID = Convert.ToInt32(Session["USER_ID"]);
string get_supplier_id = "select MS_PARTY_ID_SEQ.nextval from dual";
cmdu = new OracleCommand(get_supplier_id, conn);
int newSupplierID = Int16.Parse(cmdu.ExecuteScalar().ToString());
string ISActive = CheckIsActive.Checked ? "Enable" : "Disable";
string IsPurchaseActive = CheckIsPurchaseActive.Checked ? "Enable" : "Disable";
string IsSalesActive = CheckIsSalesActive.Checked ? "Enable" : "Disable";
string u_date = System.DateTime.Now.ToString("dd-MM-yyyy h:mm:ss tt");
string insert_user = "******";
cmdi = new OracleCommand(insert_user, conn);
OracleParameter[] objPrm = new OracleParameter[12];
objPrm[0] = cmdi.Parameters.Add("NoSupplierID", newSupplierID);
objPrm[1] = cmdi.Parameters.Add("TextSupplierName", TextSupplierName.Text);
objPrm[2] = cmdi.Parameters.Add("TextSupArabicName", TextSupArabicName.Text);
objPrm[3] = cmdi.Parameters.Add("TextSupVatNo", TextSupVatNo.Text);
objPrm[4] = cmdi.Parameters.Add("TextSup_Add_1", TextSup_Add_1.Text);
objPrm[5] = cmdi.Parameters.Add("TextSup_Add_2", TextSup_Add_2.Text);
objPrm[6] = cmdi.Parameters.Add("TextContactNo", TextContactNo.Text);
objPrm[7] = cmdi.Parameters.Add("TextIsActive", ISActive);
objPrm[8] = cmdi.Parameters.Add("TextIsPurchaseActive", IsPurchaseActive);
objPrm[9] = cmdi.Parameters.Add("TextIsSalesActive", IsSalesActive);
objPrm[10] = cmdi.Parameters.Add("u_date", u_date);
objPrm[11] = cmdi.Parameters.Add("NoCuserID", userID);
cmdi.ExecuteNonQuery();
cmdi.Parameters.Clear();
cmdi.Dispose();
foreach (ListItem li in DropDownRepresentativeID.Items)
{
if (li.Selected == true)
{
string RepresentativeID = li.Value;
string insert_pur_rep = " insert into MS_PARTY_REPRESENTATIVE (PARTY_ID, REPRESENTATIVE_ID) VALUES ( :NoPartyID, :NoRepresentativeID) ";
cmdi = new OracleCommand(insert_pur_rep, conn);
OracleParameter[] objPr = new OracleParameter[3];
objPr[0] = cmdi.Parameters.Add("NoPartyID", newSupplierID);
objPr[1] = cmdi.Parameters.Add("NoRepresentativeID", RepresentativeID);
cmdi.ExecuteNonQuery();
}
}
cmdi.Parameters.Clear();
cmdi.Dispose();
conn.Close();
alert_box.Visible = true;
alert_box.Controls.Add(new LiteralControl("Insert New Party Data Successfully"));
alert_box.Attributes.Add("class", "alert alert-success alert-dismissible");
clearText();
TextSupplierName.Focus();
Display();
}
else
{
Response.Redirect("~/PagePermissionError.aspx");
}
// }
// catch
// {
// Response.Redirect("~/ParameterError.aspx");
// }
}
public void BtnAdd_Click(object sender, EventArgs e)
{
try
{
if (IS_ADD_ACTIVE == "Enable")
{
OracleConnection conn = new OracleConnection(strConnString);
conn.Open();
int userID = Convert.ToInt32(Session["USER_ID"]);
int SupplierID = Convert.ToInt32(DropDownSupplierID.Text);
int SlipNo = Convert.ToInt32(TextSlipNo.Text);
int SupervisorID = Convert.ToInt32(DropDownSupervisorID.Text);
int ItemID = Convert.ToInt32(DropDownItemID.Text);
int SubItemID = Convert.ToInt32(DropDownSubItemID.Text);
string ItemName = DropDownItemID.SelectedItem.Text;
string SubItemName = "";
if (SubItemID == 0)
{
SubItemID = 0;
SubItemName = "";
}
else
{
SubItemID = Convert.ToInt32(DropDownSubItemID.Text);
SubItemName = DropDownSubItemID.SelectedItem.Text;
}
string ISActive = CheckIsActive.Checked ? "Enable" : "Disable";
string MakeEntryDate = EntryDate.Text;
string[] MakeEntryDateSplit = MakeEntryDate.Split('-');
String EntryDateTemp = MakeEntryDateSplit[0].Replace("/", "-");
DateTime EntryDateNewD = DateTime.ParseExact(EntryDateTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture);
string EntryDateNew = EntryDateNewD.ToString("dd-MM-yyyy");
string c_date = System.DateTime.Now.ToString("dd-MM-yyyy h:mm:ss tt");
string get_user_purchase_id = "select PF_PURCHASE_JWID_SEQ.nextval from dual";
cmdsp = new OracleCommand(get_user_purchase_id, conn);
int newPurchaseID = Int16.Parse(cmdsp.ExecuteScalar().ToString());
double ItemWeight = Convert.ToDouble(TextItemWeight.Text.Trim());
string insert_purchase = "insert into PF_PURCHASE_JW (PURCHASE_JW_ID, SLIP_NO, PARTY_ID, ITEM_ID, SUB_ITEM_ID, SUPERVISOR_ID, ITEM_WEIGHT, ENTRY_DATE, CREATE_DATE, C_USER_ID, IS_ACTIVE, DIVISION_ID) values ( :NoPurchaseID, :NoSlipID, :NoSupplierID, :NoItemID, :NoSubItemID, :NoSupervisorID, :TextItemWeight, TO_DATE(:EntryDate, 'DD/MM/YYYY'), TO_DATE(:c_date, 'DD-MM-YYYY HH:MI:SS AM'), :NoCuserID, :TextIsActive, 3)";
cmdi = new OracleCommand(insert_purchase, conn);
OracleParameter[] objPrm = new OracleParameter[11];
objPrm[0] = cmdi.Parameters.Add("NoPurchaseID", newPurchaseID);
objPrm[1] = cmdi.Parameters.Add("NoSlipID", SlipNo);
objPrm[2] = cmdi.Parameters.Add("NoSupplierID", SupplierID);
objPrm[3] = cmdi.Parameters.Add("NoItemID", ItemID);
objPrm[4] = cmdi.Parameters.Add("NoSubItemID", SubItemID);
objPrm[5] = cmdi.Parameters.Add("NoSupervisorID", SupervisorID);
objPrm[6] = cmdi.Parameters.Add("TextItemWeight", ItemWeight);
objPrm[7] = cmdi.Parameters.Add("EntryDate", EntryDateNew);
objPrm[8] = cmdi.Parameters.Add("c_date", c_date);
objPrm[9] = cmdi.Parameters.Add("NoCuserID", userID);
objPrm[10] = cmdi.Parameters.Add("TextIsActive", ISActive);
cmdi.ExecuteNonQuery();
cmdi.Parameters.Clear();
cmdi.Dispose();
conn.Close();
alert_box.Visible = true;
alert_box.Controls.Add(new LiteralControl("Insert new Job Work (Purchase) Successfully"));
alert_box.Attributes.Add("class", "alert alert-success alert-dismissible");
clearText();
Display();
}
else
{
Response.Redirect("~/PagePermissionError.aspx");
}
}
catch
{
Response.Redirect("~/ParameterError.aspx");
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static bool ExecuteSqlTran(string conStr, List <CommandInfo> cmdList)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo c in cmdList)
{
if (!String.IsNullOrEmpty(c.CommandText))
{
PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters);
if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (c.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须符合select count(..的格式");
//return false;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须大于0");
//return false;
}
if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须等于0");
//eturn false;
}
continue;
}
int res = cmd.ExecuteNonQuery();
if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须有影像行");
// return false;
}
}
}
tx.Commit();
return(true);
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw E;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["USER_NAME"] != null)
{
string requestedFile = Path.GetFileName(Request.Path);
OracleConnection conn = new OracleConnection(strConnString);
conn.Open();
string makeSQL = " SELECT NUPP.IS_PAGE_ACTIVE, NUPP.IS_ADD_ACTIVE, NUPP.IS_EDIT_ACTIVE, NUPP.IS_DELETE_ACTIVE, NUPP.IS_VIEW_ACTIVE FROM NRC_USER_PAGE_PERMISSION NUPP LEFT JOIN NRC_USER_PAGES NUP ON NUP.USER_PAGE_ID = NUPP.USER_PAGE_ID WHERE NUPP.USER_ID = '" + Session["USER_ID"] + "' AND NUP.IS_ACTIVE = 'Enable' AND NUP.PAGE_URL = '" + requestedFile + "' ";
cmdl = new OracleCommand(makeSQL);
oradata = new OracleDataAdapter(cmdl.CommandText, conn);
dt = new DataTable();
oradata.Fill(dt);
RowCount = dt.Rows.Count;
for (int i = 0; i < RowCount; i++)
{
IS_PAGE_ACTIVE = dt.Rows[i]["IS_PAGE_ACTIVE"].ToString();
IS_ADD_ACTIVE = dt.Rows[i]["IS_ADD_ACTIVE"].ToString();
IS_EDIT_ACTIVE = dt.Rows[i]["IS_EDIT_ACTIVE"].ToString();
IS_DELETE_ACTIVE = dt.Rows[i]["IS_DELETE_ACTIVE"].ToString();
IS_VIEW_ACTIVE = dt.Rows[i]["IS_VIEW_ACTIVE"].ToString();
}
if (IS_PAGE_ACTIVE == "Enable")
{
if (!IsPostBack)
{
DataTable dtPartyID = new DataTable();
DataSet dsp = new DataSet();
string makePartySQL = " SELECT * FROM HR_COUNTRIES ORDER BY COUNTRY_NAME ASC";
dsp = ExecuteBySqlString(makePartySQL);
dtPartyID = (DataTable)dsp.Tables[0];
DropDownCountryID.DataSource = dtPartyID;
DropDownCountryID.DataValueField = "COUNTRY_ID";
DropDownCountryID.DataTextField = "COUNTRY_NAME";
DropDownCountryID.DataBind();
DropDownCountryID.Items.Insert(0, new ListItem("Select Country Name", "0"));
DataTable dtRepID = new DataTable();
DataSet dsr = new DataSet();
string makeRepSQL = " SELECT REPRESENTATIVE_ID, NID_NO || ' : ' || REPRESENTATIVE_NAME AS REPRESENTATIVE_NAME FROM MS_REPRESENTATIVE ORDER BY REPRESENTATIVE_ID ASC";
dsr = ExecuteBySqlString(makeRepSQL);
dtRepID = (DataTable)dsr.Tables[0];
DropDownRepresentativeID.DataSource = dtRepID;
DropDownRepresentativeID.DataValueField = "REPRESENTATIVE_ID";
DropDownRepresentativeID.DataTextField = "REPRESENTATIVE_NAME";
DropDownRepresentativeID.DataBind();
DropDownRepresentativeID.Items.Insert(0, new ListItem("Select Representative Name", "0"));
string get_batch_id = " select LAST_NUMBER from all_sequences where sequence_name = 'MS_PARTY_ID_SEQ'";
cmdu = new OracleCommand(get_batch_id, conn);
TextSupplierID.Text = cmdu.ExecuteScalar().ToString();
TextSupplierName.Focus();
Display();
DisplayAddress();
alert_box.Visible = false;
}
IsLoad = false;
}
else
{
Response.Redirect("~/PagePermissionError.aspx");
}
}
else
{
Response.Redirect("~/Default.aspx");
}
}
internal ResultList <RubricaContattiType> GetContattiByParams(List <SendMail.Model.EntitaType> tEnt, Dictionary <SendMail.Model.FastIndexedAttributes, List <string> > pars, int da, int per, bool withEntita)
{
ResultList <RubricaContattiType> res = new ResultList <RubricaContattiType>();
if (da == 0)
{
++da;
}
res.Da = da;
string query = String.Format("SELECT VALUE(V0) FROM {0} V0", ((intoIPA) ? "V_RUBR_CONTATTI_IPA_OBJ" : "V_RUBR_CONTATTI_OBJ"));
string orderby = " order by {0} asc nulls last";
string[] oBy = new string[pars.Count];
if (pars != null && pars.Count != 0)
{
query += " WHERE ";
}
string[] wherePars = new string[pars.Count];
for (int i = 0; i < pars.Count; i++)
{
KeyValuePair <SendMail.Model.FastIndexedAttributes, List <string> > p = pars.ElementAt(i);
if (p.Value == null || p.Value.Count == 0)
{
throw new ArgumentException("Parametri non validi");
}
string qPar = null;
switch (p.Key)
{
case SendMail.Model.FastIndexedAttributes.FAX:
qPar = "V0.FAX =";
oBy[i] = "V0.fax";
break;
case SendMail.Model.FastIndexedAttributes.MAIL:
qPar = "V0.MAIL =";
oBy[i] = "V0.mail";
break;
case SendMail.Model.FastIndexedAttributes.TELEFONO:
qPar = "V0.TELEFONO =";
oBy[i] = "V0.telefono";
break;
case SendMail.Model.FastIndexedAttributes.COGNOME:
qPar = "V0.ENTITA_REF.COGNOME =";
oBy[i] = "V0.ENTITA_REF.COGNOME";
break;
case SendMail.Model.FastIndexedAttributes.RAGIONE_SOCIALE:
qPar = "V0.ENTITA_REF.RAGIONE_SOCIALE =";
oBy[i] = "V0.ENTITA_REF.RAGIONE_SOCIALE";
break;
case SendMail.Model.FastIndexedAttributes.UFFICIO:
qPar = "V0.ENTITA_REF.UFFICIO =";
oBy[i] = "V0.ENTITA_REF.UFFICIO";
break;
default:
throw new NotImplementedException("Tipo di rircerca non implementato");
}
string[] qCrt = new string[p.Value.Count];
for (int j = 0; j < p.Value.Count; j++)
{
qCrt[j] = String.Format("{0} '{1}'", qPar, p.Value[j]);
}
wherePars[i] = String.Format("({0})", String.Join(" OR ", qCrt));
}
query += String.Join(" AND ", wherePars);
string queryCount = query.Replace(" VALUE(V0) ", " count(*) ");
query += String.Format(orderby, String.Join(", ", oBy));
using (OracleCommand oCmd = base.CurrentConnection.CreateCommand())
{
oCmd.CommandText = queryCount;
try
{
int tot = Convert.ToInt32(oCmd.ExecuteScalar());
res.Totale = tot;
res.Per = (tot > per) ? per : tot;
}
catch
{
res.Per = res.Totale = 0;
res.List = null;
throw;
}
if (res.Totale > 0)
{
if (res.Per > 0)
{
oCmd.CommandText = OrderedTOracleDB.GetOrderedQuery(query, da, res.Per);
}
else
{
oCmd.CommandText = query;
}
try
{
using (OracleDataReader r = oCmd.ExecuteReader())
{
if (r.HasRows)
{
res.List = new List <RubricaContattiType>();
while (r.Read())
{
RubricaContattiType rc = r.GetValue(1) as RubricaContattiType;
if (withEntita)
{
rc.SetEntita(base.CurrentConnection);
}
res.List.Add(rc);
}
}
}
}
catch
{
res.List = null;
throw;
}
}
}
return(res);
}
public Table vratiNapraviNaracka()
{
tabela = new Table();
tabela.BorderStyle = BorderStyle.Solid;
tabela.CellPadding = 5;
for (int i = 1; i < 4; i++)
{
TableRow row = new TableRow();
for (int j = 0; j < 6; j++)
{
TableCell cell = new TableCell();
if (i == 3 && j == 1)
{
cell.ColumnSpan = 3;
row.Controls.Add(cell);
break;
}
row.Controls.Add(cell);
}
tabela.Controls.Add(row);
}
TableCell nova = new TableCell();
tabela.Rows[2].Controls.Add(nova);
tabela.Rows[0].Cells[0].Text = "Комитент:";
tabela.Rows[0].Cells[2].Text = "Корисник:";
tabela.Rows[0].Cells[4].Text = "Датум:";
tabela.Rows[1].Cells[0].Text = "Град";
tabela.Rows[1].Cells[2].Text = "Локација";
tabela.Rows[1].Cells[4].Text = "Адреса";
tabela.Rows[2].Cells[0].Text = "Коментар";
Label labela = new Label();
labela.ID = "komitentIDLbl";
labela.Text = komitentIme;
tabela.Rows[0].Cells[1].Controls.Add(labela);
labela = new Label();
labela.ID = "korisnikIDLbl";
labela.Text = korisnikIme;
tabela.Rows[0].Cells[3].Controls.Add(labela);
labela = new Label();
labela.ID = "datumLbl";
labela.Text = DateTime.Now.ToShortDateString();
tabela.Rows[0].Cells[5].Controls.Add(labela);
try
{
OCon.Open();
String komanda = "Select distinct grad.Ime,grad.Id from lokacija join grad on grad.id = lokacija.grad_id and lokacija.komitent_id=" + komitentId.ToString();
OracleCommand OCom = new OracleCommand(komanda, OCon);
OracleDataReader dr = OCom.ExecuteReader();
DropDownList lista = new DropDownList();
lista.ID = "gradoviLista";
lista.AutoPostBack = true;
lista.SelectedIndexChanged += new EventHandler(listaGradovi_SelectedIndexChanged);
while (dr.Read())
{
ListItem pom = new ListItem(dr["Ime"].ToString(), dr["ID"].ToString());
lista.Items.Add(pom);
}
tabela.Rows[1].Cells[1].Controls.Add(lista);
komanda = "Select distinct lokacija.Ime,lokacija.Id,lokacija.Adresa from lokacija where lokacija.grad_id = (Select id from grad where ime='" + lista.Items[0].Text.ToString() + "') and lokacija.komitent_id=" + komitentId;
OCom = new OracleCommand(komanda, OCon);
dr = OCom.ExecuteReader();
lista = new DropDownList();
lista.ID = "lokaciiLista";
lista.AutoPostBack = true;
lista.SelectedIndexChanged += new EventHandler(listaLokacii_SelectedIndexChanged);
while (dr.Read())
{
ListItem pom = new ListItem(dr["Ime"].ToString(), dr["ID"].ToString());
lista.Items.Add(pom);
}
tabela.Rows[1].Cells[3].Controls.Add(lista);
komanda = "Select adresa from lokacija where lokacija.id=" + lista.Items[0].Value.ToString();
OCom = new OracleCommand(komanda, OCon);
labela = new Label();
labela.ID = "adresaLbl";
labela.Text = OCom.ExecuteScalar().ToString();
tabela.Rows[1].Cells[5].Controls.Add(labela);
OCon.Close();
}
catch (Exception ex)
{
OCon.Close();
}
Button potvrdiBtn = new Button();
potvrdiBtn.Text = "Потврди";
potvrdiBtn.Click += new EventHandler(potvrdiBtn_Click);
tabela.Rows[2].Cells[2].Controls.Add(potvrdiBtn);
TextBox komentarTexBox = new TextBox();
komentarTexBox.ID = "komentarTexBox";
komentarTexBox.TextMode = TextBoxMode.MultiLine;
komentarTexBox.Width = Unit.Pixel(300);
komentarTexBox.Rows = 3;
komentarTexBox.MaxLength = 160;
tabela.Rows[2].Cells[1].Controls.Add(komentarTexBox);
return tabela;
}
protected void ReserveAppointment(string selectedSlot, string Selecteddoct, string selectedroom, DateTime startdate, DateTime enddate)
{
string insertapp;
insertapp = " insert into appointment ";
insertapp += "(patient_id, appointment_num, status, room, start_time, end_time ) ";
insertapp += "VALUES (";
insertapp += ":patient_id, :appointment_num, :status, :room, :start_time, :end_time )";
string selectSQL;
OracleConnection incon = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(insertapp, incon);
incon.Open();
//cmd.Connection = con;
selectSQL = "SELECT appointment_seq.NEXTVAL appointment FROM DUAL";
OracleCommand selcmdorder_id = new OracleCommand(selectSQL, incon);
int appointmentNum;
appointmentNum = Int16.Parse(selcmdorder_id.ExecuteScalar().ToString());
cmd.Parameters.Add(":patient_id", householdbox.SelectedItem.Value);
cmd.Parameters.Add(":appointment_num", appointmentNum);
cmd.Parameters.Add(":status", "Yes");
cmd.Parameters.Add(":room", selectedroom);
cmd.Parameters.Add(":start_time", startdate);
cmd.Parameters.Add(":end_time", enddate);
int added1 = 0;
try
{
added1 = cmd.ExecuteNonQuery();
insertResult.Text = ("Account created sucessfully");
}
catch (Exception err)
{
dberror.Text = "Error inserting record. ";
dberror.Text += err.Message;
}
string insertappser;
foreach (ListItem item in servicesbox.Items)
{
if (item.Selected)
{
insertappser = " insert into appointment_services ";
insertappser += "(appointment_num, service_id, dentist_id, service_start_time, service_end_time ) ";
insertappser += "VALUES (";
insertappser += ":appointment_num, :service_id, :dentist_id, :service_start_time, :service_end_time) ";
OracleCommand cmd1 = new OracleCommand(insertappser, incon);
// incon.Open();
cmd1.Parameters.Add(":appointment_num", appointmentNum);
cmd1.Parameters.Add(":service_id", item.Value);
cmd1.Parameters.Add(":dentist_id", Selecteddoct);
cmd1.Parameters.Add(":service_start_time", startdate);
cmd1.Parameters.Add(":service_end_time", enddate);
int added2 = 0;
try
{
added2 = cmd1.ExecuteNonQuery();
// insertResult.Text = ("Account created sucessfully");
}
catch (Exception err)
{
dberror.Text = "Error inserting record. ";
dberror.Text += err.Message;
}
}
}
string updatetemdentist;
updatetemdentist = "UPDATE tempdentistslot set ";
updatetemdentist += selectedSlot;
updatetemdentist += "= 'Not Available' ";
updatetemdentist += "WHERE appdate= :appdate and dentist_id=:dentist_id ";
OracleCommand cmd2 = new OracleCommand(updatetemdentist, incon);
cmd2.Parameters.Add(":appdate", Convert.ToDateTime(appdte.Text));
cmd2.Parameters.Add(":dentist_id", Selecteddoct);
// incon.Open();
int added3 = 0;
try
{
added3 = cmd2.ExecuteNonQuery();
// insertResult.Text = ("Account created sucessfully");
}
catch (Exception err)
{
dberror.Text = "Error inserting record. ";
dberror.Text += err.Message;
}
string updatetemp;
updatetemp = "UPDATE temp_room set ";
updatetemp += selectedSlot;
updatetemp += "= 'Not Available' ";
updatetemp += "WHERE app_date= :app_date and room_num=:room_num ";
OracleCommand cmd3 = new OracleCommand(updatetemp, incon);
// incon.Open();
cmd3.Parameters.Add(":app_date", Convert.ToDateTime(appdte.Text));
cmd3.Parameters.Add(":room_num", selectedroom);
int added4 = 0;
try
{
added4 = cmd3.ExecuteNonQuery();
// insertResult.Text = ("Account created sucessfully");
}
catch (Exception err)
{
dberror.Text = "Error inserting record. ";
dberror.Text += err.Message;
}
Response.Redirect("ConfirmationPage.aspx");
}
private void Button5_Click(object sender, EventArgs e)
{
conn.Close();
conn.Open();
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
query = "update order_detail set harga = " + dataGridView1.Rows[i].Cells[3].Value.ToString() + " where id_barang = '" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "' and id_order ='" + id_order + "'";
cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
}
query = "update order_header set subtotal = 0 where id_order ='" + id_order + "'";
cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
query = "update order_header set subtotal = subtotal+" + dataGridView1.Rows[i].Cells[3].Value.ToString() + " * " + dataGridView1.Rows[i].Cells[2].Value.ToString() + "*( 100 + " + dataGridView1.Rows[i].Cells[4].Value.ToString() + ")/100 where id_order ='" + id_order + "'";
cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
query = "update order_detail set pajak = " + dataGridView1.Rows[i].Cells[4].Value.ToString() + " where id_barang = '" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "' and id_order ='" + id_order + "'";
cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
query = "update order_detail set diskon = " + dataGridView1.Rows[i].Cells[5].Value.ToString() + " where id_barang = '" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "' and id_order ='" + id_order + "'";
cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
query = "update order_detail set total_kotor = " + dataGridView1.Rows[i].Cells[3].Value.ToString() + " * " + dataGridView1.Rows[i].Cells[2].Value.ToString() + " where id_order ='" + id_order + "' and id_barang = '" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "' ";
cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
query = "update order_detail set total_bersih = " + dataGridView1.Rows[i].Cells[3].Value.ToString() + " * " + dataGridView1.Rows[i].Cells[2].Value.ToString() + "*(( 100 + " + dataGridView1.Rows[i].Cells[4].Value.ToString() + ")/100) - " + dataGridView1.Rows[i].Cells[5].Value.ToString() + " where id_order ='" + id_order + "' and id_barang = '" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "'";
cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
}
MessageBox.Show("Berhasil Update");
load_barang();
query = "Select sum (total_bersih) from order_detail where id_order = '" + id_order + "' ";
conn.Open();
cmd = new OracleCommand(query, conn);
label13.Text = int.Parse(cmd.ExecuteScalar().ToString()).ToString("#,##0");
conn.Close();
}
public void run()
{
Exception exp = null;
int intRecordsAffected = 0;
string sql = "Update Shippers Set CompanyName=:CompName Where ShipperID = 2";
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
OracleCommand cmd = new OracleCommand("", con);
con.Open();
//get expected result
cmd.CommandText = "select count(*) from Shippers where ShipperID = 2";
int ExpectedRows = int.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = sql;
//Currently not running on DB2: .Net-Failed, GH:Pass
//if (con.Provider.IndexOf("IBMDADB2") >= 0) return ;
cmd.Parameters.Add(new OracleParameter());
cmd.Parameters[0].ParameterName = "CompName";
cmd.Parameters[0].OracleType = OracleType.VarChar; //System.InvalidOperationException:
cmd.Parameters[0].Size = 20; //System.InvalidOperationException
cmd.Parameters[0].SourceColumn = "CompanyName";
cmd.Parameters[0].Value = "Comp1";
try
{
BeginCase("Prepare Exception - missing OracleType");
cmd.Prepare();
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
cmd.Parameters[0].OracleType = OracleType.VarChar;
// try
// {
// BeginCase("Prepare Exception - missing Size");
// try
// {
// cmd.Parameters[0].Size = 0;
// cmd.Prepare();
// }
// catch (Exception ex) {exp = ex;}
// Compare(exp.GetType().FullName, typeof(InvalidOperationException).FullName );
// exp=null;
// }
// catch(Exception ex){exp = ex;}
// finally{EndCase(exp); exp = null;}
// cmd.Parameters[0].Size = 20;
try
{
BeginCase("Prepare Exception - missing Size");
try
{
con.Close();
cmd.Prepare();
}
catch (Exception ex) { exp = ex; }
Compare(exp.GetType().FullName, typeof(InvalidOperationException).FullName);
exp = null;
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
con.Open();
try
{
BeginCase("ExecuteNonQuery first time");
intRecordsAffected = cmd.ExecuteNonQuery();
Compare(intRecordsAffected, ExpectedRows);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("ExecuteNonQuery second time, chage value");
cmd.Parameters[0].Value = "Comp2";
intRecordsAffected = cmd.ExecuteNonQuery();
Compare(intRecordsAffected, ExpectedRows);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
public int getNumAvailableLargeRunsDB(DateTime start, DateTime end)
{
string conString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
OracleConnection con = new OracleConnection(conString);
string cmdStr = @"SELECT MIN((
(SELECT COUNT(*)
FROM HVK_RUN
WHERE RUN_SIZE = 'L'
) - (
CASE
WHEN REGULAR_RESERVATIONS >
(SELECT COUNT(*)
FROM HVK_RUN
WHERE RUN_SIZE = 'R'
)
THEN LARGE_RESERVATIONS + (REGULAR_RESERVATIONS -
(SELECT COUNT(*) FROM HVK_RUN WHERE RUN_SIZE = 'R'
))
ELSE LARGE_RESERVATIONS
END))) AS Available_Runs
FROM
(SELECT COUNT(
CASE p.DOG_SIZE
WHEN 'L'
THEN 1
ELSE NULL
END) AS LARGE_RESERVATIONS,
COUNT(
CASE p.DOG_SIZE
WHEN 'L'
THEN NULL
ELSE 1
END) AS REGULAR_RESERVATIONS
FROM hvk_reservation r
JOIN hvk_pet_reservation pr
ON r.RESERVATION_NUMBER = pr.RES_RESERVATION_NUMBER
JOIN hvk_pet p
ON pr.PET_PET_NUMBER = p.PET_NUMBER
RIGHT OUTER JOIN
(SELECT CAST(:endDate as DATE) - level + 1 AS DAY
FROM dual
CONNECT BY LEVEL <= CAST(:endDate as DATE) - CAST(:startDate as DATE) + 1
) Calendar
ON Calendar.DAY BETWEEN r.RESERVATION_START_DATE AND r.RESERVATION_END_DATE
GROUP BY DAY
)";
OracleCommand cmd = new OracleCommand(cmdStr, con);
cmd.BindByName = true;
cmd.Parameters.Add("endDate", end);
cmd.Parameters.Add("startDate", start);
int returned = -1;
try {
con.Open();
returned = Convert.ToInt32(cmd.ExecuteScalar());
} finally {
con.Close();
}
return(returned);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["emp_id"] == null)
{
Response.Redirect("EmpLogin.aspx");
}
string SelectSQL;
//string INSERTSQLACC = "";
SelectSQL = "select Emp_name FROM Clinic_employee ";
SelectSQL += "WHERE Emp_id = '" + Session["emp_id"].ToString() + "'";
OracleConnection con = new OracleConnection(connectionString); // create new connection
OracleCommand selcmd = new OracleCommand(SelectSQL, con); // open command with sql sttme aganist above connection
OracleDataReader reader = null; // reader for result
try
{
con.Open(); // open connection
reader = selcmd.ExecuteReader(); // execute connmand
reader.Read(); // reads result
HtmlGenericControl li = new System.Web.UI.HtmlControls.HtmlGenericControl("li"); //create html li tag
HtmlGenericControl a = new System.Web.UI.HtmlControls.HtmlGenericControl("a"); // create html a tag
a.InnerText = "Welcome " + reader["emp_name"]; //text
li.Controls.Add(a); //add a inside li tag
menuid.Controls.Add(li); // add li to ul tag
}
catch (Exception err)
{
dberror.Text = "Error selecting record. ";
dberror.Text += err.Message;
}
string selectinfo;
selectinfo = "select patient_info.Pf_name, appointment.patient_id ";
selectinfo += " from appointment , patient_info where ";
selectinfo += "appointment.PATIENT_ID=patient_info.PATIENT_ID ";
selectinfo += " and APPOINTMENT_NUM= :appointment_num ";
string selectSQL;
// OracleConnection inconpat = new OracleConnection(connectionString); // create new connection
OracleCommand selcmd1 = new OracleCommand(selectinfo, con); // open command with sql sttme aganist above connection
OracleDataReader reader1 = null; // reader for result
// con.Open();
selectSQL = "SELECT invoice_seq.NEXTVAL invoice_number FROM DUAL";
OracleCommand selcmdorder_id = new OracleCommand(selectSQL, con);
invoiceId = Int16.Parse(selcmdorder_id.ExecuteScalar().ToString());
selcmd1.Parameters.Add(":appointment_num", Request.QueryString["appnum"].ToString());
try
{
// con.Open(); // open connection
reader1 = selcmd1.ExecuteReader(); // execute connmand
reader1.Read(); // reads result
pn.Text = reader1["pf_name"].ToString();
pid.Text = reader1["patient_id"].ToString();
appnum.Text = Request.QueryString["appnum"].ToString();
inonum.Text = invoiceId.ToString();
todate.Text = DateTime.Now.ToString("MM/dd/yyyy h:mmtt");
}
catch (Exception err)
{
dberror.Text = "Error selecting record. ";
dberror.Text += err.Message;
}
finally
{
con.Close();
}
if (!this.IsPostBack)
{
FillInvoice();
}
}
public override void FetchAndFill(StringBuilder sb)
{
long maxUnix = (long)Math.Floor((GetMaxUnxi().AddHours(-8) - new DateTime(1970, 1, 1, 0, 0, 0)).TotalMilliseconds);
string jsonData = GetRemoteDataFromUrl(DataUrlWithUnix.Replace("{unix}", maxUnix.ToString()), sb);
PackageProduct <T> package = ConvertString2Object <T>(jsonData, sb);
if (package.data.Count == 0)
{
//sb.Append(_tablename + " data Sync Rows:0 \n");
return;
}
sb.Append("Source [Type: URL, Address: " + DataUrlWithUnix.Replace("{unix}", maxUnix.ToString()) + "\n");
if (package.code != 1)
{
throw new Exception("get data failed");
}
DataTable tb = CreateDataTableSchema(typeof(T));
tb = FillDataFromList(tb, package.data);
tb.TableName = _tablename;
int insertCount = 0;
int updateCount = 0;
//OracleConnection _conn = new OracleConnection(ConfigurationManager.AppSettings["CnECon"]);
using (var con = new OracleConnection(ConfigurationManager.AppSettings["CnECon"]))
{
con.Open();
OracleCommand cmd = new OracleCommand("", con);
foreach (DataRow dr in tb.Rows)
{
string existStr = "select count(*) from " + _tablename + " where id=" + dr["id"] +
" and priceDate='" + dr["priceDate"] + "'";
cmd.CommandText = existStr;
int cmdresult = 0;
try
{
object obj = cmd.ExecuteScalar();
if (Equals(obj, null) || (Equals(obj, DBNull.Value)))
{
}
else
{
cmdresult = int.Parse(obj.ToString());
}
}
catch (OracleException e)
{
con.Close();
throw new Exception(e.Message);
}
string operationSql;
if (cmdresult == 0)
{
insertCount++;
operationSql = CreateInsertSql(_tablename, tb.Columns, dr);
}
else
{
updateCount++;
operationSql = CreateUpdateSql(_tablename, new[] { "id", "priceDate" }, tb.Columns, dr);
}
cmd.CommandText = operationSql;
try
{
cmd.ExecuteNonQuery();
}
catch (OracleException e)
{
con.Close();
throw new Exception(e.Message);
}
}
con.Close();
}
sb.Append("Table " + _tablename + " Insert Rows:" + insertCount + ",Update Rows:" + updateCount + " \r\n");
}
public void BtnAdd_Click(object sender, EventArgs e)
{
try
{
if (IS_ADD_ACTIVE == "Enable")
{
OracleConnection conn = new OracleConnection(strConnString);
conn.Open();
int userID = Convert.ToInt32(Session["USER_ID"]);
int CategoryID = Convert.ToInt32(DropDownCategoryID.Text);
int InNatureID = Convert.ToInt32(DropDownInNatureID.Text);
string get_customer_id = "select MF_ITEM_ID_SEQ.nextval from dual";
cmdu = new OracleCommand(get_customer_id, conn);
int newItemID = Int16.Parse(cmdu.ExecuteScalar().ToString());
string ISActive = CheckIsActive.Checked ? "Enable" : "Disable";
string IsTransferActive = CheckIsTransferActive.Checked ? "Enable" : "Disable";
string IsPurchaseActive = CheckIsPurchaseActive.Checked ? "Enable" : "Disable";
string IsProductionActive = CheckIsProductionActive.Checked ? "Enable" : "Disable";
string IsFgActive = CheckIsFgActive.Checked ? "Enable" : "Disable";
string IsSalesActive = CheckIsSalesActive.Checked ? "Enable" : "Disable";
string c_date = System.DateTime.Now.ToString("dd-MM-yyyy h:mm:ss tt");
string insert_user = "******";
cmdi = new OracleCommand(insert_user, conn);
OracleParameter[] objPrm = new OracleParameter[13];
objPrm[0] = cmdi.Parameters.Add("NoItemID", newItemID);
objPrm[1] = cmdi.Parameters.Add("TextItemName", TextItemName.Text);
objPrm[2] = cmdi.Parameters.Add("TextItemCode", TextItemCode.Text);
objPrm[3] = cmdi.Parameters.Add("TextIsActive", ISActive);
objPrm[4] = cmdi.Parameters.Add("NoCategoryID", CategoryID);
objPrm[5] = cmdi.Parameters.Add("NoInNatureID", InNatureID);
objPrm[6] = cmdi.Parameters.Add("TextIsTransferActive", IsTransferActive);
objPrm[7] = cmdi.Parameters.Add("TextIsPurchaseActive", IsPurchaseActive);
objPrm[8] = cmdi.Parameters.Add("TextIsProductionActive", IsProductionActive);
objPrm[9] = cmdi.Parameters.Add("TextIsFgActive", IsFgActive);
objPrm[10] = cmdi.Parameters.Add("TextIsSalesActive", IsSalesActive);
objPrm[11] = cmdi.Parameters.Add("u_date", c_date);
objPrm[12] = cmdi.Parameters.Add("NoCuserID", userID);
cmdi.ExecuteNonQuery();
cmdi.Parameters.Clear();
cmdi.Dispose();
conn.Close();
alert_box.Visible = true;
alert_box.Controls.Add(new LiteralControl("Insert New Item Successfully"));
alert_box.Attributes.Add("class", "alert alert-success alert-dismissible");
clearText();
Display();
}
else
{
Response.Redirect("~/PagePermissionError.aspx");
}
}
catch
{
Response.Redirect("~/ParameterError.aspx");
}
}
protected void btnmdfy_Click(object sender, EventArgs e)
{
string ctc, ctd, dcc, poc;
int nowh;
ctc = txtctc.Text;
ctd = txtctd.Text;
dcc = ddldcc.Text;
poc = txtpoc.Text;
if (txtnowh.Text == "")
{
nowh = 0;
}
else
{
nowh = int.Parse(txtnowh.Text);
}
int n;
string s = "Select count(*) from ct where ctc='" + ctc + "' and dcc='" + dcc + "'";
con.Open();
OracleCommand cmdcount = new OracleCommand(s, con);
n = int.Parse(cmdcount.ExecuteScalar().ToString());
if (n != 1)
{
lblctmsg.Text = "Values cannot be modified";
}
else
{
string str = "update ct set ctd='" + ctd + "',poc='" + poc + "',nowh='" + nowh + "',dcc='" + dcc + "' where ctc='" + ctc + "' and dcc='" + dcc + "'";
OracleCommand cmd = new OracleCommand(str, con);
cmd.ExecuteNonQuery();
con.Close();
lblctmsg.Text = "Values modified Successfully";
}
}
public void run()
{
Exception exp = null;
//in DB2 when trying to fill an empty table - no table is loaded
OracleConnection conn = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
OracleDataAdapter oleDBda = new OracleDataAdapter();
oleDBda.SelectCommand = new OracleCommand("Select * from GH_EMPTYTABLE", conn);
DataSet ds = new DataSet();
oleDBda.Fill(ds);
try
{
BeginCase("Table count - fill with SP");
Compare(ds.Tables.Count, 1);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
//add for bug #2508 - OLEDBDataAdapter.Fill fills only the 1st result set, reported from an evaluation
if (ConnectedDataProvider.GetDbType(oleDBda.SelectCommand.Connection) == DataBaseServer.SQLServer)
//multiple commands can not be done with Oracle or DB2
{
//get excpected results
if (oleDBda.SelectCommand.Connection.State != ConnectionState.Open)
{
oleDBda.SelectCommand.Connection.Open();
}
OracleCommand cmd = new OracleCommand("", oleDBda.SelectCommand.Connection);
cmd.CommandText = "Select count(*) from Customers";
int TblResult0 = (int)cmd.ExecuteScalar();
cmd.CommandText = "Select count(*) from Categories";
int TblResult1 = (int)cmd.ExecuteScalar();
cmd.CommandText = "Select count(*) from Region";
int TblResult2 = (int)cmd.ExecuteScalar();
if (oleDBda.SelectCommand.Connection.State != ConnectionState.Closed)
{
oleDBda.SelectCommand.Connection.Close();
}
oleDBda.SelectCommand.CommandText = "Select * from Customers; " +
"Select * from Categories; " +
"Select * from Region";
ds = new DataSet();
oleDBda.Fill(ds);
try
{
BeginCase("Table count - Fill with query");
Compare(ds.Tables.Count, 3);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("Table 0 rows count");
Compare(ds.Tables[0].Rows.Count, TblResult0);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("Table 1 rows count");
Compare(ds.Tables[1].Rows.Count, TblResult1);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
try
{
BeginCase("Table 2 rows count");
Compare(ds.Tables[2].Rows.Count, TblResult2);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
}
}
protected void btndlt_Click(object sender, EventArgs e)
{
con.Open();
string s = "select count (*) from ct where ctc='" + txtctc.Text + "' and dcc='" + ddldcc.Text + "'";
OracleCommand cmd = new OracleCommand(s, con);
int p;
p = int.Parse(cmd.ExecuteScalar().ToString());
if (p == 1)
{
string r = "delete from ct where ctc='" + txtctc.Text + "'and dcc='" + ddldcc.Text + "'";
OracleCommand cmddel = new OracleCommand(r, con);
cmddel.ExecuteNonQuery();
con.Close();
lblctmsg.Text = "code deleted successfully";
}
else
{
lblctmsg.Text = "code doesnot exists";
}
}
/************************************************************
* Function name : SetCourseApplication
* Purpose : 수강신청(교육이력 Insert)
* Input : string[] rParams
* Output : String
*************************************************************/
public string SetCourseApplication(string[] rParams)
{
string xRtn = Boolean.FalseString;
Database db = base.GetDataBase("LMS");
OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection();
xCnnLMS.Open();
OracleTransaction xTransLMS = null;
OracleCommand xCmdLMS = null;
try
{
xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
xCmdLMS = base.GetSqlCommand(db);
xCmdLMS.Connection = xCnnLMS;
xCmdLMS.Transaction = xTransLMS;
string xFilePath = string.Empty;
string xSeq = GetMaxIDOfCode(new string[] { rParams[1], rParams[13] }, xCmdLMS);
try
{
string xSql = string.Empty;
//이러닝 과정에 대하여 수강신청 즉시 승인상태로 처리
xSql = "SELECT DECODE(TC.COURSE_TYPE,'000003','Y','N') CHK_VALUE ";
xSql += "FROM T_OPEN_COURSE TOC, T_COURSE TC ";
xSql += "WHERE TOC.COURSE_ID = TC.COURSE_ID ";
xSql += "AND TOC.OPEN_COURSE_ID = '" + rParams[1] + "' "; // 개설과정 ID
xCmdLMS.CommandText = xSql;
//이러닝 과정확인
string xElearningChk = (string)xCmdLMS.ExecuteScalar(); // Y OR N로 반환
xSql = " INSERT INTO t_course_result ( user_id , "; // 사용자 ID
xSql += " open_course_id, "; // 개설과정 ID
xSql += " course_result_seq, "; // 이수차수
xSql += " user_company_id, "; // 사용자 회사
xSql += " user_dept_code, "; // 사용자 부서
xSql += " user_duty_step, "; // 사용자 직급
xSql += " employed_state, "; // 재직, 채용예정자 구분
if (xElearningChk == "N") //이러닝과정 체크하여 이러닝 아닌경우는 전달받은 값처리하고, 이러닝은 승인처리(일자포함)
{
xSql += " approval_flg, "; // 승인여부
//xSql += " approval_dt, "; // 승인일자
}
else
{
xSql += " approval_flg, "; // 승인여부
xSql += " approval_dt, "; // 승인일자
}
//xSql += " approval_dt, "; // 승인일자
//xSql += " non_approval_cd, "; // 교육불가사유 코드
//xSql += " non_approval_remark, "; // 교육불가사유
xSql += " pass_flg, "; // 이수여부
xSql += " progress_rate, "; // 진도율
//xSql += " total_score, "; // 총 취득점수
//xSql += " report_score, "; // 과제점수
//xSql += " assess_score, "; // 기말고사 점수
//xSql += " last_subject_id, "; // 최종학습 과목 ID
//xSql += " last_contents_id, "; // 최종학습 컨텐츠 ID
xSql += " user_course_begin_dt, "; // 학습 시작일자
xSql += " user_course_end_dt, "; // 학습 종료일자
//xSql += " course_start_flg, "; // 교육 입과여부
//xSql += " order_flg, "; // 발령처리여부
//xSql += " non_pass_cd, "; // 미이수 사유코드
//xSql += " non_pass_remark, "; // 미이수사유 직접입력
xSql += " insurance_flg, "; // 고용보험 여부
if (!string.IsNullOrEmpty(rParams[12]))
{
xSql += " insurance_dt, "; // 피보험 취득일자
}
xSql += " confirm, "; // 확정/비확정 : 0 : 비확정, 1 : 확정
xSql += " ins_id, "; // 작성자 ID
xSql += " ins_dt, "; // 작성일자
xSql += " upt_id, "; // 수정자 ID
xSql += " upt_dt) "; // 수정일자
xSql += " VALUES ( ";
xSql += string.Format(" '{0}', ", rParams[0]); // 사용자 ID
xSql += string.Format(" '{0}', ", rParams[1]); // 개설과정 ID
xSql += string.Format(" {0}, ", xSeq); // 이수 차수
xSql += string.Format(" '{0}', ", rParams[2]); // 사용자 회사
xSql += string.Format(" '{0}', ", rParams[3]); // 사용자 부서
xSql += string.Format(" '{0}', ", rParams[4]); // 사용자 직급
xSql += string.Format(" '{0}', ", rParams[5]); // 재직, 채용예정자 구분
if (xElearningChk == "N") //이러닝과정 체크하여 이러닝 아닌경우는 전달받은 값처리하고, 이러닝은 승인처리(일자포함)
{
xSql += string.Format(" '{0}', ", rParams[6]); // 승인여부
//xSql += string.Format(" '{0}', ", ); // 승인일자
}
else
{
xSql += string.Format(" '{0}', ", "000001"); // 승인여부
xSql += " SYSDATE, ";
}
//xSql += string.Format(" '{0}', ", ); // 교육불가사유 코드
//xSql += string.Format(" '{0}', ", ); // 교육불가사유
xSql += string.Format(" '{0}', ", rParams[7]); // 이수여부
xSql += string.Format(" '{0}', ", rParams[8]); // 진도율
//xSql += string.Format(" '{0}', ", ); // 총 취득점수
//xSql += string.Format(" '{0}', ", ); // 과제점수
//xSql += string.Format(" '{0}', ", ); // 기말고사 점수
//xSql += string.Format(" '{0}', ", ); // 최종학습 과목 ID
//xSql += string.Format(" '{0}', ", ); // 최종학습 컨텐츠 ID
xSql += string.Format(" TO_DATE('{0}','YYYY.MM.DD'), ", rParams[9]); // 학습 시작일자
xSql += string.Format(" TO_DATE('{0}','YYYY.MM.DD'), ", rParams[10]); // 학습 종료일자
//xSql += string.Format(" '{0}', ", ); // 교육 입과여부
//xSql += string.Format(" '{0}', ", ); // 발령 처리여부
//xSql += string.Format(" '{0}', ", ); // 미이수사유 코드
//xSql += string.Format(" '{0}', ", ); // 미이수사유 직접입력
xSql += string.Format(" '{0}', ", rParams[11]); // 고용보험 여부
if (!string.IsNullOrEmpty(rParams[12]))
{
xSql += string.Format(" TO_DATE('{0}','YYYY.MM.DD'), ", rParams[12]); // 피보험 취득일자
}
xSql += " '1', "; // 확정/비확정 : 0 : 비확정, 1 : 확정
xSql += string.Format(" '{0}', ", rParams[13]); // 작성자 ID
xSql += string.Format(" {0}, ", "SYSDATE"); // 작성일자
xSql += string.Format(" '{0}', ", rParams[13]); // 수정자 ID
xSql += string.Format(" {0}) ", "SYSDATE"); // 수정일자
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xTransLMS);
#region 수강신청시 설문조사 대상에 Insert
string[] xParams = new string[2];
xParams[0] = rParams[1]; // 개설과정 ID
xParams[1] = rParams[0]; // 사용자(수강신청자) ID
COMMON.vp_l_common_md common = new CLT.WEB.BIZ.LMS.COMMON.vp_l_common_md();
common.SetSurveyTarget(xParams, db, xCmdLMS, xTransLMS);
#endregion 수강신청시 설문조사 대상에 Insert
xTransLMS.Commit(); // 트랜잭션 커밋
xRtn = Boolean.TrueString;
}
catch (Exception ex)
{
xTransLMS.Rollback(); // Exception 발생시 롤백처리...
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(xRtn);
}
void listaLokacii_SelectedIndexChanged(object sender, EventArgs e)
{
Label pom = ((Label)tabela.FindControl("adresaLbl"));
try
{
String komanda = "Select adresa from lokacija where lokacija.id=" + ((DropDownList)sender).Items[((DropDownList)sender).SelectedIndex].Value.ToString();
OCon.Open();
OracleCommand OCom = new OracleCommand(komanda, OCon);
String adresa = OCom.ExecuteScalar().ToString();
pom.Text = adresa;
OCon.Close();
}
catch (Exception ex)
{
OCon.Close();
}
}
static void Main(string[] args)
{
bool exit = false;
string todayDate = DateTime.Now.ToString("yyyy-MM-dd");
Path = System.Reflection.Assembly.GetExecutingAssembly().Location;
Path = Path.Substring(0, Path.LastIndexOf('\\') + 1);
//emailTo = "*****@*****.**";
emailTo_1 = "*****@*****.**";
emailTo = "[email protected];";
emailCC = "[email protected];";
//subjectMail = "ΠΡΟΣΦΟΡΑ: Urgent αρχείο για τα TID από τα TMS Πειραιώς και Εuronet (epos) ";
ConnectSHARPdb();
if (SHARPconn.State != ConnectionState.Open)
{
Console.WriteLine("ERROR: Unable to connect to SHARP.");
return;
}
OleDbConnection connZACRep = new OleDbConnection(connstrZACrep);
try
{
connZACRep.Open();
}
catch
{
Console.WriteLine("Cannot connect to OLEDB " + connstrZACrep);
exit = true;
}
if (exit == true)
{
return;
}
int TotalPir = 0;
int TotalPirRem = 0;
// Total Piraeus Terminals
OracleCommand sqlTotPi = new OracleCommand(" select count (*) FROM TERMINAL ", SHARPconn);
TotalPir = Convert.ToInt32(sqlTotPi.ExecuteScalar());
// Total remaining terminals with no latest fix
OracleCommand sqlTotRe = new OracleCommand(" select count(*) "+
" FROM TERMINAL " +
" where (TER_SUCC_FILE_DL is null or " +
" TO_CHAR(TER_SUCC_FILE_DL, 'YYYY/MM/DD')<='2018/06/26') " +
" and ter_tid not in (select tsa_terminal from term_stat_act where tsa_version like 'PR5.1.%.0021' and tsa_version>='PR5.1.10.0021')", SHARPconn);
TotalPirRem = Convert.ToInt32(sqlTotRe.ExecuteScalar());
// Clear temp file
OracleCommand sqlDltQry = new OracleCommand("TRUNCATE TABLE HOSTBASE_TEMP", SHARPconn);
sqlDltQry.ExecuteNonQuery();
sqlDltQry.Dispose();
OracleCommand sqlCursorFill = new OracleCommand(
" DECLARE "+
" Modems number(6,0) := 1; " +
" FromTM char(5) := '23:00'; " +
" ToTM char(5) := '07:00'; " +
" DaysTM number(2,0); " +
" Slice number(3,0) := 1; " +
" ElapsedTime number(2,0) := 1; " +
" terminals numeric(6,0); " +
" WinSlice int; " +
" Minutes int; " +
" TerminalsPerDay numeric(6,0); " +
" TmpBaseTime char(4); " +
" TmpBaseTime_1 char(2); " +
" TmpBaseTime_3 char(2); " +
" TmpBaseTime_tm char(6); " +
" FrmTime number(4,0):= cast(substr(FromTM,1,2) as number)*100 + cast(substr(FromTM,4,2) as number); " +
" ToTime number(4,0) := (cast(substr(ToTM,1,2) as number)*100) + (cast(substr(ToTM,4,2) as number)); " +
" Modem numeric(6,0); " +
" BaseTime numeric(4,0); " +
" BaseTime_tm numeric(6,0); " +
" NewBaseTime numeric(4,0); " +
" StartingDate char (10) := '" + todayDate + "' ; " +
" curdate date := to_date(StartingDate,'YYYY/MM/DD') ; " +
" tempdatemm char(2); " +
" tempdatemm2 char(2); " +
" tempdateYY char(2); " +
" tempdateYY_Check char(2); " +
" tempdateYY_CheckTmp char(2); " +
" tempdatedd char(2); " +
" tempdatedd2 char(2); " +
" tempdateHH char(2); " +
" tempdateMN char(2); " +
" termID terminal.TER_TID%type; " +
" tempdate#1 char(12); " +
" tempdate#2 char(6); " +
" tempdate#3 char(7); " +
" tempdatCreat char(10); " +
" CURSOR Update_Cursor " +
" IS " +
" select distinct TER_TID " +
" FROM TERMINAL where TER_TID not in (select TER_TID from TERMINAL where TER_START_FILE_DL > '26/06/18' and (TER_UNSUCC_FILE_DL < TER_START_FILE_DL or TER_UNSUCC_FILE_DL is null)) ; "+
" BEGIN " +
" select count(*) into terminals " +
" from TERMINAL " +
" where TER_TID not in " +
" (select TER_TID from TERMINAL " +
" where TER_START_FILE_DL > '26/06/18' and (TER_UNSUCC_FILE_DL < TER_START_FILE_DL or TER_UNSUCC_FILE_DL is null)); " +
" if (ToTime-FrmTime) > 0 " +
" then " +
" Minutes := (((ToTime - FrmTime)/100))*60 + " +
" mod(((ToTime-FrmTime)),100); " +
" else " +
" Minutes := (((2400+(ToTime-FrmTime))/100))*60+ " +
" mod((2400+(ToTime-FrmTime)),100); " +
" end if; " +
" TerminalsPerDay := (Minutes/ElapsedTime * Modems); " +
" if (Modems = 0) or (TerminalsPerDay = 0) " +
" then " +
" Winslice := 0; " +
" else " +
" WinSlice := (Minutes/(TerminalsPerDay/Modems)); " +
" end if; " +
" BaseTime:= FrmTime; " +
" Modem := 0; " +
" curdate := to_date(StartingDate,'YYYY/MM/DD') ; " +
" tempdateYY_Check := substr(('0'+rtrim(to_char(extract(YEAR from curdate)))),3,2); " +
" OPEN Update_Cursor; " +
" LOOP " +
" FETCH Update_Cursor INTO termID; " +
" EXIT WHEN Update_Cursor%NOTFOUND; " +
" Modem := Modem +1; " +
" BaseTime_tm := BaseTime + 10000; " +
" TmpBaseTime_tm := to_char(BaseTime_tm); " +
" TmpBaseTime := substr(TmpBaseTime_tm,2,4); " +
" tempdateYY := substr(('0'+rtrim(to_char(extract(YEAR from curdate)))),3,2); " +
" tempdatemm :=substr('0'||(to_char(extract(MONTH from curdate))),-2,2); " +
" tempdateDD :=substr('0' ||(to_char(extract(DAY from curdate))),-2,2); " +
" tempdateHH := substr(('0'+rtrim(to_char(to_number(FrmTime/100)))),-2,2); " +
" tempdateMN := substr((to_char(mod(((to_number(FrmTime))),100))),-2,2); " +
" tempdate#1 := tempdateYY||tempdatemm||tempdateDD||TmpBaseTime || '00'; " +
" tempdate#2 := tempdateYY||tempdatemm||tempdateDD; " +
" tempdate#3 := '1' || tempdateYY||tempdatemm||tempdateDD; " +
" INSERT INTO HOSTBASE_TEMP (F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15) " +
" VALUES ('LIP', termID , '*APPL',tempdate#1, '02', '2119894412FFFFFFFFFFFFFF',tempdate#2,'220000', 'N', '0','0', tempdate#3,'220000', 'TIGANIS', 'QPADEV000P'); " +
" IF Modem = Modems " +
" then " +
" BaseTime := Basetime + Winslice; " +
" Modem := 0; " +
" if basetime = 2360 " +
" then " +
" BaseTime := 0; " +
" basetime := BaseTime/2401 + mod((BaseTime),2401) ; " +
" curdate := curdate + 1; " +
" tempdateDD2 :=substr('0' ||(to_char(extract(DAY from curdate))),-2,2); " +
" tempdateYY_CheckTmp := substr(('0'+rtrim(to_char(extract(YEAR from curdate)))),3,2); " +
" if tempdateYY_Check <> tempdateYY_CheckTmp " +
" then " +
" tempdatCreat :='20'|| tempdateYY_CheckTmp||'/'||'01'||'/'||'01'; " +
" curdate := to_date(tempdatCreat,'YYYY/MM/DD'); " +
" tempdateYY_Check := tempdateYY_CheckTmp; " +
" end if; " +
" end if; " +
" if BaseTime = 60 " +
" then " +
" Basetime := 100; " +
" end if; " +
" TmpBaseTime_3 := SUBSTR(TmpBaseTime,3,2); " +
" TmpBaseTime_1 := SUBSTR(TmpBaseTime,1,2); " +
" If TmpBaseTime_1 <> '23' and TmpBaseTime_3 = '59' " +
" then " +
" BaseTime := (TmpBaseTime_1 * 100) + 100; " +
" end if; " +
" ELSE " +
" basetime := BaseTime; " +
" END IF; " +
" if BaseTime = 701 " +
" then " +
" Basetime := 2300; " +
" end if; " +
" END LOOP; " +
" CLOSE Update_Cursor; " +
" END; ", SHARPconn);
///sqlCursorFill.Parameters.Add("StartDate", OracleDbType.TimeStamp, todayDate, ParameterDirection.Input);
OracleDataReader rdrCursorFill = null;
rdrCursorFill = sqlCursorFill.ExecuteReader();
// Select data so to create host schdule excel
string sqlHostSele = " select * FROM HOSTBASE_TEMP order by f4 ";
OracleCommand sqlcmdHost_Excel = new OracleCommand();
sqlcmdHost_Excel.Connection = SHARPconn;
//////sqlcmdSel_Excel.CommandTimeout = 0;
sqlcmdHost_Excel.CommandType = CommandType.Text;
sqlcmdHost_Excel.CommandText = sqlHostSele;
OracleDataReader rdrHostToExcel = null;
rdrHostToExcel = sqlcmdHost_Excel.ExecuteReader();
//OleDbCommand xlCrtCmd = new OleDbCommand("CREATE TABLE [HostBaseSchedule] ([F1] char(255),[F2] char(255),[F3] char(255),[F4] char(255),[F5] char(255), " +
//" [F6] char(255),[F7] char(255),[F8] char(255),[F9] char(255),[F10] char(255),[F11] char(255),[F12] char(255), [F13] char(255),[F14] char(255),[F15] char(255) )");
OleDbCommand xlCrtCmd = new OleDbCommand("CREATE TABLE [HostBaseSchedule] ([F1] char(255),[F2] char(255),[F3] char(255),[F4] char(255),[F5] char(255), " +
" [F6] char(255),[F7] char(255),[F8] int,[F9] char(255),[F10] int,[F11] int,[F12] char(255), [F13] int,[F14] char(255),[F15] char(255) )");
OleDbConnection myConnection = new OleDbConnection(connectionString);
myConnection.Open();
xlCrtCmd.Connection = myConnection;
xlCrtCmd.ExecuteNonQuery();
while (rdrHostToExcel.Read())
{
// OleDbCommand myCommand = new OleDbCommand("Insert into [HostBaseSchedule] ([F1],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15]) " +
//" values ('" + rdrHostToExcel.GetValue(0).ToString() + "','" + rdrHostToExcel.GetValue(1).ToString() + "','" + rdrHostToExcel.GetValue(2).ToString().Replace('\'', ' ') + "','" + rdrHostToExcel.GetValue(3).ToString().Replace('\'', ' ') + "','"
// + rdrHostToExcel.GetValue(4).ToString().Replace('\'', ' ') + "','" + rdrHostToExcel.GetValue(5).ToString().Replace('\'', ' ') + "','" + rdrHostToExcel.GetValue(6).ToString() + "','" + rdrHostToExcel.GetValue(7).ToString() + "','"
// + rdrHostToExcel.GetValue(8).ToString() + "','" + rdrHostToExcel.GetValue(9).ToString() + "','" + rdrHostToExcel.GetValue(10).ToString() + "','" + rdrHostToExcel.GetValue(11).ToString() + "','"
// + rdrHostToExcel.GetValue(12).ToString() + "','" + rdrHostToExcel.GetValue(13).ToString() + "','" + rdrHostToExcel.GetValue(14).ToString() + "')");
OleDbCommand myCommand = new OleDbCommand("Insert into [HostBaseSchedule] ([F1],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15]) " +
" values ('" + rdrHostToExcel.GetValue(0).ToString() + "','" + rdrHostToExcel.GetValue(1).ToString() + "','" + rdrHostToExcel.GetValue(2).ToString().Replace('\'', ' ') + "','" + rdrHostToExcel.GetValue(3).ToString().Replace('\'', ' ') + "','"
+ rdrHostToExcel.GetValue(4).ToString().Replace('\'', ' ') + "','" + rdrHostToExcel.GetValue(5).ToString().Replace('\'', ' ') + "','" + rdrHostToExcel.GetValue(6).ToString() + "','" + Convert.ToInt32(rdrHostToExcel.GetValue(7)) + "','"
+ rdrHostToExcel.GetValue(8).ToString() + "','" + Convert.ToInt32(rdrHostToExcel.GetValue(9)) + "','" + Convert.ToInt32(rdrHostToExcel.GetValue(10)) + "','" + rdrHostToExcel.GetValue(11).ToString() + "','"
+ Convert.ToInt32(rdrHostToExcel.GetValue(12)) + "','" + rdrHostToExcel.GetValue(13).ToString() + "','" + rdrHostToExcel.GetValue(14).ToString() + "')");
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery();
}
myConnection.Close();
xlCrtCmd.Dispose();
rdrHostToExcel.Close();
Console.WriteLine("Schedule file Exported");
// Select data so to create Ingenico Last Call
string sqlHostSele2 =
" select TER_TID, "+
" TO_CHAR(TER_START_PARAM_DL, 'YYYY/MM/DD HH:mm:ss') START_PARAM_DL, " +
" TO_CHAR(TER_UNSUCC_PARAM_DL, 'YYYY/MM/DD HH:mm:ss') UNSUCC_PARAM_DL, " +
" TO_CHAR(TER_START_FILE_DL, 'YYYY/MM/DD HH:mm:ss') START_FILE_DL, " +
" TO_CHAR(TER_UNSUCC_FILE_DL, 'YYYY/MM/DD HH:mm:ss') UNSUCC_FILE_DL, " +
" TO_CHAR(TER_SUCC_FILE_DL, 'YYYY/MM/DD HH:mm:ss') SUCC_FILE_DL, " +
" (select tsa_version from TERM_STAT_ACT where tsa_terminal = TERMINAL.ter_tid) running_ver, " +
" (select tsa_timestamp from TERM_STAT_ACT where tsa_terminal = TERMINAL.ter_tid) time_stamp_upload_stat " +
" FROM TERMINAL " +
" where (TER_SUCC_FILE_DL is null or " +
" TO_CHAR(TER_SUCC_FILE_DL, 'YYYY/MM/DD')<='2018/06/26') " +
" and ter_tid not in (select tsa_terminal from term_stat_act where tsa_version like 'PR5.1.%.0021' and tsa_version>='PR5.1.10.0021') ";
//string sqlHostSele2 =
// " SELECT ter_tid,TER_START_FILE_DL,TER_UNSUCC_FILE_DL, TER_SUCC_FILE_DL " +
// " ,TER_START_PARAM_DL, TER_UNSUCC_PARAM_DL, TER_SUCC_PARAM_DL " +
// " FROM TERMINAL " +
// " WHERE TERMINAL.TER_TID NOT IN " +
// " (SELECT TERMINAL.TER_TID " +
// " FROM TERMINAL " +
// " WHERE TERMINAL.TER_START_FILE_DL > '26/06/18' " +
// " AND (TERMINAL.TER_UNSUCC_FILE_DL < TERMINAL.TER_START_FILE_DL " +
// " OR TERMINAL.TER_UNSUCC_FILE_DL IS NULL) " +
// " ) ";
OracleCommand sqlcmdHost_Excel2 = new OracleCommand();
sqlcmdHost_Excel2.Connection = SHARPconn;
sqlcmdHost_Excel2.CommandType = CommandType.Text;
sqlcmdHost_Excel2.CommandText = sqlHostSele2;
OracleDataReader rdrHostToExcel2 = null;
rdrHostToExcel2 = sqlcmdHost_Excel2.ExecuteReader();
OleDbCommand xlCrtCmd2 = new OleDbCommand("CREATE TABLE [IngenicoLastCall] ([TID] char(255),[START_PARAM_DL] char(255),[UNSUCC_PARAM_DL] char(255),[START_FILE_DL] char(255),[UNSUCC_FILE_DL] char(255), " +
" [SUCC_FILE_DL] char(255),[running_ver] char(255),[time_stamp_upload_stat] char(255) )");
OleDbConnection myConnection2 = new OleDbConnection(connectionString2);
myConnection2.Open();
xlCrtCmd2.Connection = myConnection2;
xlCrtCmd2.ExecuteNonQuery();
while (rdrHostToExcel2.Read())
{
OleDbCommand myCommand2 = new OleDbCommand("Insert into [IngenicoLastCall] ([TID] ,[START_PARAM_DL] ,[UNSUCC_PARAM_DL],[START_FILE_DL] ,[UNSUCC_FILE_DL] , [SUCC_FILE_DL] ,[running_ver] ,[time_stamp_upload_stat] ) " +
" values ('" + rdrHostToExcel2.GetValue(0).ToString() + "','" + rdrHostToExcel2.GetValue(1).ToString() + "','" + rdrHostToExcel2.GetValue(2).ToString() + "','" + rdrHostToExcel2.GetValue(3).ToString() + "','"
+ rdrHostToExcel2.GetValue(4).ToString() + "','" + rdrHostToExcel2.GetValue(5).ToString() + "','" + rdrHostToExcel2.GetValue(6).ToString() + "','" + rdrHostToExcel2.GetValue(7).ToString() + "')");
myCommand2.Connection = myConnection2;
myCommand2.ExecuteNonQuery();
}
////Console.Write('\r');
myConnection2.Close();
xlCrtCmd2.Dispose();
rdrHostToExcel2.Close();
Console.WriteLine("Ingenico Last Call file exported");
// Ingenico_MinMaxDate
// Export to txt file so import to ZACReporting/dbo.temp_tid file
string sqlHostSeleTxt =
" SELECT ter_tid "+
" FROM TERMINAL "+
" WHERE TERMINAL.TER_TID NOT IN "+
" (SELECT TERMINAL.TER_TID " +
" FROM TERMINAL " +
" WHERE TERMINAL.TER_START_FILE_DL > '26/06/18' " +
" AND (TERMINAL.TER_UNSUCC_FILE_DL < TERMINAL.TER_START_FILE_DL " +
" OR TERMINAL.TER_UNSUCC_FILE_DL IS NULL) " +
" ) ";
OracleCommand txtExp = new OracleCommand();
txtExp.Connection = SHARPconn;
txtExp.CommandType = CommandType.Text;
txtExp.CommandText = sqlHostSeleTxt;
OracleDataReader txtExpRdr = txtExp.ExecuteReader();
//using (System.IO.StreamWriter writer = new StreamWriter(csvExport,false, Encoding.Unicode))
using (System.IO.StreamWriter writer = new StreamWriter(csvExport, false))
{
while (txtExpRdr.Read())
{
//writer.WriteLine(txtExpRdr["ter_tid"] + "\t");
writer.WriteLine(txtExpRdr["ter_tid"]);
}
}
Console.WriteLine("File with TIDs exported");
//Clear temp file
OleDbCommand clearTmpFile = new OleDbCommand();
clearTmpFile.Connection = connZACRep;
clearTmpFile.CommandTimeout = 0;
clearTmpFile.CommandText = "delete from abc096.temp_tid";
clearTmpFile.ExecuteNonQuery();
// Import csv to zacreporting/dbo.temp_tid file
OleDbCommand inserTempTid = new OleDbCommand();
inserTempTid.Connection = connZACRep;
inserTempTid.CommandTimeout = 0;
inserTempTid.CommandText = "BULK INSERT [abc096].[temp_tid] " +
"FROM '" + @"\\grat1-dev-ap2t\d$\TransactReports\HostSchedule_Ingenico\TidsToFile.txt" + "' " +
"WITH " +
"( " +
" CODEPAGE = 1252, " +
" FIRSTROW = 1, " +
" FIELDTERMINATOR = '', " +
//" ROWTERMINATOR = '\n', " +
" TABLOCK " +
")";
try
{
inserTempTid.ExecuteNonQuery();
}
catch (OleDbException ex)
{
Console.WriteLine("Error description " + ex);
}
// Select data so to create Ingenico_MinMaxDate
string sqlHostSele3 =
" select tid, min(a.dtstamp) as Date_Min, max(a.dtstamp) as Date_Max, count(*) as Trx_Total " +
" from (select tid, DTSTAMP from abc096.IMP_TRANSACT_D_2018 a union select tid, DTSTAMP from abc096.IMP_TRANSACT_D_2018_B) a " +
" where exists(select TID from abc096.temp_tid b where a.tid = b.TID ) " +
" group by TID ";
OleDbCommand sqlcmdHost_Excel3 = new OleDbCommand();
sqlcmdHost_Excel3.Connection = connZACRep;
sqlcmdHost_Excel3.CommandTimeout = 3000;
sqlcmdHost_Excel3.CommandType = CommandType.Text;
sqlcmdHost_Excel3.CommandText = sqlHostSele3;
OleDbDataReader rdrHostToExcel3 = null;
rdrHostToExcel3 = sqlcmdHost_Excel3.ExecuteReader();
OleDbCommand xlCrtCmd3 = new OleDbCommand("CREATE TABLE [Ingenico_MinMaxDate] ([TID] char(255), [Min_date] char(255), [Max_date] char(255), [Trx_count] char(255))");
OleDbConnection myConnection3 = new OleDbConnection(connectionString3);
myConnection3.Open();
xlCrtCmd3.Connection = myConnection3;
xlCrtCmd3.ExecuteNonQuery();
while (rdrHostToExcel3.Read())
{
OleDbCommand myCommand3 = new OleDbCommand("Insert into [Ingenico_MinMaxDate] ([TID], [Min_date], [Max_date], [Trx_count]) " +
" values ('" + rdrHostToExcel3.GetValue(0).ToString() + "','" + rdrHostToExcel3.GetValue(1).ToString() + "','" + rdrHostToExcel3.GetValue(2).ToString() + "','" + rdrHostToExcel3.GetValue(3).ToString() + "')");
myCommand3.Connection = myConnection3;
myCommand3.ExecuteNonQuery();
}
myConnection3.Close();
xlCrtCmd3.Dispose();
rdrHostToExcel3.Close();
Console.WriteLine("Ingenico_MinMaxDate file exported");
SHARPconn.Close();
connZACRep.Close();
string pathFolder = @"C:\Users\lnestoras\Desktop\";
////string[] hostBaseExcel = Directory.GetFiles(pathFolder, @"ppirdf0p_NewKernel_" + timestamp + ".xlsx", SearchOption.TopDirectoryOnly);
string[] hostBaseExcel = Directory.GetFiles(pathFolder, "*", SearchOption.TopDirectoryOnly);
subjectMail = "Ingenico Host scheduling terminals";
mailBody += "Dimitri, " + "\n\r" +
"Below are the requested info, " + "\n\r" +
"Total Piraeus Bank Ingenico terminals: " + TotalPir + "\n\r" +
"Remaining (not updated) Piraeus Bank Ingenico terminals: " + TotalPirRem + "\n\r" +
"Attaching file for host base schedule. " + "\n\r" +
"Regards ";
MailClass.SendMailAttach(Path, subjectMail, mailBody, emailTo, emailCC, "", hostBaseExcel);
// Move files to Archive
string sourceDirectory = @"C:\Users\lnestoras\Desktop\Test\";
string targetDirectory = @"C:\Users\lnestoras\Desktop\Test\Archive\";
DirectoryInfo ingenicoSource = new DirectoryInfo(sourceDirectory);
DirectoryInfo ingenicoTarget = new DirectoryInfo(targetDirectory);
foreach (FileInfo fi in ingenicoSource.GetFiles())
{
//fi.CopyTo(Path.Combine(dccTarget.FullName, fi.Name), true);
fi.MoveTo(targetDirectory + fi.Name);
//Console.WriteLine("File {0} succesfully copied. ", fi.Name);
}
}
void insertBtn_Click(object sender, EventArgs e)
{
String[] pomString = ((Button)sender).ID.ToString().Split('.');
int red = Convert.ToInt32(pomString[2].ToString());
int tab = Convert.ToInt32(pomString[1].ToString());
TextBox pom = ((TextBox)tabelaN[tab].Rows[red].FindControl("kolicinaTextBox" + (red).ToString()));
try
{
String komanda;
String narackaId = info["NarackaId"].ToString();
OCon.Open();
komanda = "Select Kolicina from listaproizvodi where naracka_id=" + narackaId.ToString().Trim() + " and proizvod_id=" + tabelaN[tab].Rows[red].Cells[1].Text.ToString().Trim();
OracleCommand OCom = new OracleCommand(komanda, OCon);
int kol;
if (OCom.ExecuteScalar() != null)
{
kol = Convert.ToInt32(OCom.ExecuteScalar().ToString());
}
else
{
kol = -1;
}
if (pom.Text.ToString().Trim() != "" && Convert.ToInt64(pom.Text.ToString().Trim()) > 0)
{
if (kol == -1)
{
komanda = "Insert into listaproizvodi(NARACKA_ID,PROIZVOD_ID,DANOK_ID,KOLICINA,CENA) values(" + narackaId.ToString().Trim() + "," + tabelaN[tab].Rows[red].Cells[1].Text.ToString().Trim() + "," + tabelaN[tab].Rows[red].Cells[3].Text.ToString().Trim() + "," + pom.Text.Trim().ToString() + "," + tabelaN[tab].Rows[red].Cells[7].Text.ToString().Trim() + ")";
}
else
{
kol += Convert.ToInt32(pom.Text.Trim().ToString());
komanda = "Update listaproizvodi set kolicina =" + kol.ToString() + " where naracka_id=" + narackaId.ToString().Trim() + " and proizvod_id=" + tabelaN[tab].Rows[red].Cells[1].Text.ToString().Trim();
}
}
OCom = new OracleCommand(komanda, OCon);
OCom.ExecuteNonQuery();
OCon.Close();
}
catch (Exception ex)
{
OCon.Close();
}
//UpdatePanel podatociLista = ((UpdatePanel)strana.FindControl("narackaListaUP"));
podatociLista.Update();
stranica.Response.Redirect("Naracaj.aspx?Mod=1&A=2");
}
public ResultList <SimpleResultItem> LoadFieldsByParams(SendMail.Model.IndexedCatalogs ctg, IList <SendMail.Model.EntitaType> tEnt, KeyValuePair <SendMail.Model.FastIndexedAttributes, string> par, int da, int per)
{
if (String.IsNullOrEmpty(par.Value))
{
return(null);
}
if (tEnt == null)
{
tEnt = new List <SendMail.Model.EntitaType>();
}
if (tEnt.Count == 0)
{
tEnt.Add(SendMail.Model.EntitaType.ALL);
}
ResultList <SimpleResultItem> res = new ResultList <SimpleResultItem>();
res.Da = da;
string queryCountBase = "SELECT count(*) from ({0})";
string queryRubrica = "SELECT distinct r.RAGIONE_SOCIALE AS rag_soc"
+ ", r.DISAMB_PRE as prefix"
+ ", r.DISAMB_POST as suffix"
+ ", {0} as descr"
+ ", LISTAGG(r.ID_REFERRAL, ';') within group (order by r.ID_REFERRAL) over (partition by NVL(r.DISAMB_PRE,' ')||r.RAGIONE_SOCIALE||NVL(r.DISAMB_POST,' ')) AS ids" //(partition by {0})
+ ", 'R' as SRC"
+ ", REFERRAL_TYPE as subtype"
+ " FROM rubr_entita r {1}"
+ " WHERE {2}"
+ " order by 1";
string campi = "";
switch (par.Key)
{
case SendMail.Model.FastIndexedAttributes.RAGIONE_SOCIALE:
case SendMail.Model.FastIndexedAttributes.COGNOME:
case SendMail.Model.FastIndexedAttributes.FAX:
case SendMail.Model.FastIndexedAttributes.MAIL:
case SendMail.Model.FastIndexedAttributes.TELEFONO:
case SendMail.Model.FastIndexedAttributes.UFFICIO:
campi += par.Key.ToString();
break;
default:
throw new ArgumentException("Parametro non implementato");
}
string innerJoin = "";
if (par.Key.Equals(SendMail.Model.FastIndexedAttributes.FAX) ||
par.Key.Equals(SendMail.Model.FastIndexedAttributes.MAIL) ||
par.Key.Equals(SendMail.Model.FastIndexedAttributes.TELEFONO))
{
innerJoin = "INNER JOIN rubr_contatti c ON c.REF_ID_REFERRAL = r.id_referral";
}
string whereConds = null;
if (!tEnt.Contains(SendMail.Model.EntitaType.ALL) && !tEnt.Contains(SendMail.Model.EntitaType.UNKNOWN))
{
whereConds += "REFERRAL_TYPE in (";
whereConds += String.Join(", ", tEnt.Select(t => String.Format("'{0}'", t.ToString())).ToArray());
whereConds += ") and ";
}
whereConds += "length(" + par.Key.ToString() + ") >= " + par.Value.Length + " and ";
switch (par.Key)
{
case SendMail.Model.FastIndexedAttributes.COGNOME:
case SendMail.Model.FastIndexedAttributes.MAIL:
case SendMail.Model.FastIndexedAttributes.UFFICIO:
case SendMail.Model.FastIndexedAttributes.RAGIONE_SOCIALE:
whereConds += "lower(" + par.Key.ToString() + ") like '%" + par.Value.ToLower() + "%'";
break;
case SendMail.Model.FastIndexedAttributes.FAX:
case SendMail.Model.FastIndexedAttributes.TELEFONO:
whereConds += par.Key.ToString() + " like '%" + par.Value.ToLower() + "%'";
break;
default:
throw new ArgumentException("Parametro non implementato");
}
string query = string.Format(queryRubrica, campi, innerJoin, whereConds);
string queryCount = String.Format(queryCountBase, query);
using (OracleCommand oCmd = base.CurrentConnection.CreateCommand())
{
//count
int tot = 0;
oCmd.CommandText = queryCount;
try
{
tot = Convert.ToInt32(oCmd.ExecuteScalar());
res.Per = (tot > per) ? per : tot;
res.Totale = tot;
}
catch
{
tot = 0;
res.List = null;
}
if (tot > 0)
{
if (per > 0)
{
oCmd.CommandText = OrderedTOracleDB.GetOrderedQuery(query, da, per);
}
else
{
oCmd.CommandText = query;
}
try
{
using (OracleDataReader r = oCmd.ExecuteReader())
{
if (r.HasRows)
{
res.List = new List <SimpleResultItem>();
while (r.Read())
{
res.List.Add(
new SimpleResultItem(
r.GetValue("descr").ToString(),
r.GetValue("ids").ToString(),
String.Format("{0} {1} {2}", r.GetValue("prefix"), r.GetValue("rag_soc"), r.GetValue("suffix")),
r.GetValue("subtype").ToString(),
r.GetValue("SRC").ToString(),
100));
}
}
}
}
catch
{
res.List = null;
}
}
else if ((par.Key != SendMail.Model.FastIndexedAttributes.FAX) &&
(par.Key != SendMail.Model.FastIndexedAttributes.TELEFONO))
{
res = LoadSimilarityFieldsByParams(ctg, tEnt, par, 1, per);
}
}
return(res);
}
/// <summary>
/// Execute an OracleCommand (that returns a 1x1 resultset) against the specified OracleTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-OleDb command</param>
/// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
//execute the command & return the results
return cmd.ExecuteScalar();
}
private void Button4_Click(object sender, EventArgs e)
{
//add produsen
String nama_produsen = comboBox1.Text;
OracleCommand cmd = new OracleCommand("SELECT COUNT(*) FROM PRODUSEN WHERE NAMA_PRODUSEN='" + nama_produsen + "'", mainParent.oc);
int ada = Convert.ToInt32(cmd.ExecuteScalar().ToString());
String id_produsen = "";
if (ada > 0)
{
cmd = new OracleCommand("SELECT ID_PRODUSEN FROM PRODUSEN WHERE NAMA_PRODUSEN='" + nama_produsen + "'", mainParent.oc);
id_produsen = cmd.ExecuteScalar().ToString();
}
else
{
cmd = new OracleCommand("SELECT MAX(ID_PRODUSEN) FROM PRODUSEN", mainParent.oc);
int auto_inc;
if (cmd.ExecuteScalar() != null)
{
auto_inc = 0;
}
else
{
auto_inc = Convert.ToInt32(cmd.ExecuteScalar().ToString().Substring(1, 4)) + 1;
}
id_produsen = "P" + auto_inc.ToString().PadLeft(4, '0');
cmd = new OracleCommand("INSERT INTO PRODUSEN VALUES('" + id_produsen + "','" + nama_produsen + "')", mainParent.oc);
cmd.ExecuteNonQuery();
}
//Add isi_stok
String dd = DateTime.Now.Day.ToString();
String mm = DateTime.Now.Month.ToString();
String yyyy = DateTime.Now.Year.ToString();
String tanda = textBox3.Text;
cmd = new OracleCommand("INSERT INTO ISI_STOK VALUES('" + id_isi + "','" + id_produsen + "',TO_DATE(LPAD('" + dd + "',2,'0')||'/'||LPAD('" + mm + "',2,'0')||'/'||LPAD('" + yyyy + "',4,'0'),'DD/MM/YYYY'),'" + tanda + "')", mainParent.oc);
cmd.ExecuteNonQuery();
//Add Detail Stok
for (int i = 0; i < dataGridView1.RowCount; i++)
{
try
{
cmd = new OracleCommand("SELECT ID_SUPPLY FROM SUPPLY WHERE NAMA_SUPPLY='" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "'", mainParent.oc);
String id_supply = cmd.ExecuteScalar().ToString();
String total = dataGridView1.Rows[i].Cells[3].Value.ToString();
String harga_beli = dataGridView1.Rows[i].Cells[2].Value.ToString();
String expired = dataGridView1.Rows[i].Cells[5].Value.ToString();
cmd = new OracleCommand("INSERT INTO DTRANS_STOK VALUES('" + id_isi + "','" + id_supply + "'," + total + "," + harga_beli + ")", mainParent.oc);
cmd.ExecuteNonQuery();
cmd = new OracleCommand("INSERT INTO DSUPPLY VALUES('" + tanda + "','" + id_supply + "',TO_DATE('" + expired + "','DD/MM/YYYY')," + total + ")", mainParent.oc);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{}
}
MessageBox.Show("Berhasil melakukan transaksi isi stok");
resetData();
}