public static void ObtenComboAjax(AjaxControlToolkit.ComboBox oComboAjax, System.Data.SqlClient.SqlDataReader dr)
{
if ((oComboAjax.Items.Count > 0))
{
oComboAjax.Items.Clear();
}//end if
try
{
oComboAjax.Items.Add("--");
if (!(dr == null))
{
while (dr.Read())
{
//Agrega elementos a la lista
ListItem newItem = new ListItem();
newItem.Value = dr.GetValue(0).ToString();
newItem.Text = dr.GetValue(1).ToString();
oComboAjax.Items.Add(new ListItem(newItem.Text, newItem.Value));
}
} //end if;
}
catch (Exception ex)
{
throw new Exception("Se ha producido un error en el metodo ObtenCombo ", ex);
} //end try
} //end ObtenCombo
public static void AgregaElementosAListas(System.Data.SqlClient.SqlDataReader sqldr, params ListBox[] oListBox)
{
try
{
if (sqldr.HasRows)
{
while (sqldr.Read())
{
for (int x = 0; x <= oListBox.Length - 1; x++)
{
ListItem obj = new ListItem();
obj.Value = sqldr.GetValue(0).ToString();
obj.Text = sqldr.GetValue(1).ToString();
oListBox[x].Items.Add(obj);
} //end for
} //end while
sqldr.Close();
sqldr = null;
}//end if
}
catch (Exception ex)
{
throw new Exception("Se ha producido un error en el metodo AgregaElementosAListas " + ex.Message);
} //end try
} //end void
protected void Page_Load(object sender, EventArgs e)
{
String q = Request.Params[0];
ora.Open();
cmd = new SqlCommand("select * from personalinfo where name like '%" + q + "%' or emailid like '%" + q + "%' or ccity like '%" + q + "%'" , ora);
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
string path,url,uid;
string media = dr.GetValue(13).ToString();
string username = dr.GetValue(2).ToString();
string email = dr.GetValue(6).ToString();
string country = dr.GetValue(16).ToString();
uid=dr.GetValue(1).ToString();
path = media.Substring(2);
url = "searchprofile.aspx?userid="+uid;
Response.Write("<a href = '" + url +"'><div class='display_box' align='left'><img src='" + path + "' style='width:50px; height:50px; float:left; margin-right:6px;' /><span class='name'>" + username + "</span> <br/>" + email + "<br/><span style='font-size:9px; color:#999999'>" + country + "</span></div></a>");
// Response.Write("<a href = '" + url +"'></a>");
}
}
catch (Exception ex)
{
}
ora.Close();
}
public override void fillData(SqlDataReader reader)
{
drivinglicence = (string)reader.GetValue(0);
drivinglicencetype = (string)reader.GetValue(1);
point = (int)reader.GetValue(2);
ID = (string)reader.GetValue(3);
}
}//end void
public static void ObtenLista(DropDownList ddl, System.Data.SqlClient.SqlDataReader dr)
{
if ((ddl.Items.Count > 0))
{
ddl.Items.Clear();
}//end if
try
{
ddl.Items.Add("--Seleccione--");
if (!(dr == null))
{
while (dr.Read())
{
//Agrega elementos a la lista
ListItem newItem = new ListItem();
newItem.Value = dr.GetValue(0).ToString();
newItem.Text = dr.GetValue(1).ToString();
ddl.Items.Add(new ListItem(newItem.Text, newItem.Value));
}
} //end if;
}
catch (Exception ex)
{
throw new Exception("Se ha producido un error en el metodo ObtenLista ", ex);
} //end try
} //end ObtenLista
protected void Button1_Click1(object sender, EventArgs e)
{
Panel3.Visible = true;
Label26.Text = Session["dn"].ToString();
cmd = new SqlCommand("select orderno,orderdate,deliverydate,name from booking where name='" + Session["nm"].ToString() + "' and Delivery='" + Labeldeliveryresult.Text + "' and distributorname='" + Session["dn"].ToString() + "'", con);
con.Open();
dr = cmd.ExecuteReader();
string s1 = "";
while (dr.Read())
{
s1 = dr.GetValue(0).ToString();
TextBox18.Text = dr.GetValue(1).ToString();
TextBox14.Text = dr.GetValue(2).ToString();
TextBox16.Text = dr.GetValue(3).ToString();
}
TextBox12.Text = s1;
TextBox13.Text = (int.Parse(s1) + 1000).ToString();
dr.Close();
cmd = new SqlCommand("select email,city from consumer where username='******'", con);
dr = cmd.ExecuteReader();
if (dr.Read())
{
TextBox17.Text = dr.GetValue(0).ToString();
TextBox19.Text = dr.GetValue(1).ToString();
}
}
protected void lbtnforgetpass_Click(object sender, EventArgs e)
{
if (txtforgrtid.Text == "")
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup",
"alert('Please Enter The User_ID.');", true);
return;
}
else
{
a = txtforgrtid.Text;
}
try
{
dr = cls.FillDataReader("select R_password , R_EmailId from Registerlist where R_logid='" + a + "'");
if (dr.Read())
{
if (!dr.IsDBNull(0))
{
pass = dr.GetValue(0).ToString();
}
if (!dr.IsDBNull(1))
{
emal = dr.GetValue(1).ToString();
}
}
dr.Close();
if (pass == null && emal == null)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup",
"alert('Not a valid User_ID.');", true);
txtforgrtid.Text = "";
return;
}
else
{
bool var = MailHelper.CheckForInternetConnection();
if (var == true)
{
MailHelper.SendMailMessage("*****@*****.**", emal, "Password", "Your Password:'******'");
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup",
"alert('Password have been sent to your registered email_ID.');", true);
txtforgrtid.Text = "";
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup",
"alert('Please Check Your Internet Connection.');", true);
txtforgrtid.Text = "";
}
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup",
"alert(" + ex + ");", true);
}
}
//you must override this function so that you can fill the object
public override void fillData(SqlDataReader reader)
{
if (reader.Read())
{
ID = (string)reader.GetValue(0);
name = (string)reader.GetValue(1);
gender = (string)reader.GetValue(2);
hometown = (string)reader.GetValue(3);
address = (string)reader.GetValue(4);
telephone = (string)reader.GetValue(5);
birthday = (DateTime)reader.GetValue(6);
crimestatus = (string)reader.GetValue(7);
if (reader[8].GetType() != typeof(DBNull))
{
byte[] bs = (byte[])reader[8];
MemoryStream memoryStream = new MemoryStream(bs);
photo = new Bitmap(memoryStream);
}
else
{
photo = null;
}
tax = (int)reader.GetValue(9);
}
}
private new List<BaseTable> ToSchema(SqlDataReader reader)
{
List<BaseTable> result = new List<BaseTable>();
while (reader.Read())
{
var table = new CarcassColorTable
{
Carcasscolorid = new Guid(reader.GetValue(0).ToString()),
Carcasscolorcolor = reader.GetValue(1).ToString()
};
table.UpdateFieldList.Clear();
result.Add(table);
}
return result;
}
internal override void Populate(SqlDataReader sqlDataReader)
{
for (int i = 0; i < sqlDataReader.FieldCount; i++)
{
switch (sqlDataReader.GetName(i))
{
case "ID":
ID = int.Parse(sqlDataReader.GetValue(i).ToString());
break;
case "Category":
Category = sqlDataReader.GetValue(i).ToString();
break;
}
}
}
public long GetMaxAutono()
{
try
{
System.Data.SqlClient.SqlDataReader dr = con.Getreader("select max(autono) from activities");
if (dr.Read())
{
if (dr.HasRows)
{
string data = dr.GetValue(0).ToString();
if (data == "")
{
return(0);
}
else
{
return(Convert.ToInt64(data));
}
}
}
return(-1);
}
catch
{
return(-1);
}
}
//GetUserFromReader
//A helper function that takes the current row from the OdbcDataReader
//and hydrates a MembershiUser from the values. Called by the
//MembershipUser.GetUser implementation.
private MembershipUser GetUserFromReader(System.Data.SqlClient.SqlDataReader reader)
{
object providerUserKey = reader.GetValue(1);
string username = reader.GetString(0);
//Additional comments on following lines have been removed. See the original VB file.
string email = "email";
string passwordQuestion = "";
string comment = "";
bool isApproved = true;
bool isLockedOut = false;
DateTime creationDate = new DateTime();
DateTime lastLoginDate = new DateTime();
DateTime lastActivityDate = new DateTime();
DateTime lastPasswordChangedDate = new DateTime();
DateTime lastLockedOutDate = new DateTime();
var u = new MembershipUser(this.Name,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate);
return(u);
}
public static NewMemberBalancePrediction Factory(SqlDataReader reader)
{
var newMemberId = reader.GetInt32(0);
var dependentId = reader.GetInt32(1);
var birthYear = reader.GetInt32(2);
var state = reader.GetString(3);
var lastCptCode = reader.GetString(4);
var cachedBalance = reader.GetString(5);
var recommendedBalance = reader.GetValue(6);
double d;
double.TryParse(recommendedBalance == null ? "0" : recommendedBalance.ToString(), out d);
var sufficientAmount = reader.GetInt32(7);
var serviceEnd = reader.GetDateTime(8);
return new NewMemberBalancePrediction
{
NewMemberId = newMemberId,
DependentId = dependentId,
BirthYear = birthYear,
State = state,
LastCptCode = lastCptCode,
CachedBalance = cachedBalance,
RecommendedBalance = Math.Round(d, 2),
SufficientAmount = sufficientAmount,
ServiceEnd = serviceEnd
};
}
public string NewBillNo()
{
string sql = "SELECT MAX(BillNo) AS MaxKey FROM Bill";
String str = "";
System.Data.SqlClient.SqlDataReader sdr = SqlHelper.ExecuteReader(this.Connection, CommandType.Text, sql);
while (sdr.Read())
{
if (sdr.GetValue(0) != DBNull.Value)
{
try
{
System.Int32 num = System.Int32.Parse(sdr["MaxKey"].ToString());
num++;
str = System.String.Format("{0:00000}", num);
}
catch { }
}
else
{
str = "00001";
}
}
sdr.Close();
return(str);
}
private Value GetValue(SqlDataReader reader, int column)
{
Value value = new Value();
value.Item = reader.GetValue(column);
Type type = value.Item.GetType();
if (reader.IsDBNull(column))
{
value.Type = ColumnType.Null;
}
else if(typeof(bool).IsAssignableFrom(type))
{
value.Type = ColumnType.Boolean;
}
else if(typeof(int).IsAssignableFrom(type))
{
value.Type = ColumnType.Integer;
}
else if (typeof(float).IsAssignableFrom(type) || typeof(double).IsAssignableFrom(type))
{
value.Type = ColumnType.Double;
}
else if (typeof(byte[]).IsAssignableFrom(type))
{
value.Type = ColumnType.Blob;
}
else
{
value.Type = ColumnType.String;
}
return value;
}
public static DateTime ValidateDataReader_T(SqlDataReader reader,string colname)
{
if(reader.GetValue(reader.GetOrdinal(colname))!=DBNull.Value)
return reader.GetDateTime(reader.GetOrdinal(colname));
else
return System.DateTime.MinValue;
}
static Document CreateDocument(SqlDataReader reader, IDictionary<int, List<string>> packageFrameworks)
{
var package = new Dictionary<string, string>();
for (var i = 0; i < reader.FieldCount; i++)
{
if (!reader.IsDBNull(i))
{
string name = reader.GetName(i);
object obj = reader.GetValue(i);
if (name == "key")
{
var key = (int)obj;
List<string> targetFrameworks;
if (packageFrameworks.TryGetValue(key, out targetFrameworks))
{
package.Add("supportedFrameworks", string.Join("|", targetFrameworks));
}
}
var value = (obj is DateTime) ? ((DateTime)obj).ToUniversalTime().ToString("O") : obj.ToString();
package.Add(name, value);
}
}
return DocumentCreator.CreateDocument(package);
}
/// <summary>
/// ��SqlDataReader�а�ȫ��ȡ����
/// </summary>
/// <param name="reader">���ݶ�ȡ��SqlDataReader</param>
/// <param name="colname">����</param>
/// <returns>���е��ַ������ݣ����Ϊ�գ���System.String.Empty</returns>
public static string ValidateDataReader_S(SqlDataReader reader,string colname)
{
if(reader.GetValue(reader.GetOrdinal(colname))!=DBNull.Value)
return reader.GetString(reader.GetOrdinal(colname));
else
return System.String.Empty;
}
public static int ValidateDataReader_N(SqlDataReader reader,string colname)
{
if(reader.GetValue(reader.GetOrdinal(colname))!=DBNull.Value)
return reader.GetInt32(reader.GetOrdinal(colname));
else
return System.Int32.MinValue;
}
// Populates the grid view of all the employers for Admin Secion
public List<Employer> GetListOfEmployersAdmin()
{
List<Employer> ListOfEmployersAdmin = new List<Employer>();
try
{
using (SqlConnection Cxn = new SqlConnection(CxnString))
{
using (SqlCommand Cmd = new SqlCommand("spGetEmployersAdmin", Cxn))
{
Cxn.Open();
dr = Cmd.ExecuteReader();
while (dr.Read())
{
Employer AdminEmployer = new Employer();
int empID = Convert.ToInt32(dr.GetValue(Convert.ToInt32(Emp_GetObject.SP_GetEmpID)));
string empUsername = dr.GetValue(Convert.ToInt32(Emp_GetObject.SP_GetEmpUsername)).ToString();
string empEmail = dr.GetValue(Convert.ToInt32(Emp_GetObject.SP_GetEmpEmail)).ToString();
string empPhone = dr.GetValue(Convert.ToInt32(Emp_GetObject.SP_GetEmpPhone)).ToString();
AdminEmployer.EmployerID = empID;
AdminEmployer.EmployerUsername = empUsername;
AdminEmployer.EmployerEmail = empEmail;
AdminEmployer.EmployerPhone = empPhone;
ListOfEmployersAdmin.Add(AdminEmployer);
}
Cxn.Close();
dr.Close();
}
}
}
catch (SqlException ex)
{
throw;
}
return ListOfEmployersAdmin;
}
public static CityInfo GetCityInfo(SqlDataReader reader)
{
var city = DataContractFactory.CreateCityInfo();
city.Id = reader.GetValue<long>(0);
city.Latitude = (double) reader.GetValue<int>(1)/GeoConstants.GeoFactor;
city.Longitude = (double) reader.GetValue<int>(2)/GeoConstants.GeoFactor;
city.Name = reader.GetValue<string>(3);
city.District = reader.GetValue<string>(4);
city.Region = reader.GetValue<string>(5);
city.Suffix = reader.GetValue<string>(6);
city.CladrCode = reader.GetValue<string>(7);
city.PostCode = reader.GetValue<string>(8);
city.Fullname = reader.GetValue<string>(9);
return city;
}
internal override void Populate(SqlDataReader sqlDataReader)
{
for (int i = 0; i < sqlDataReader.FieldCount; i++)
{
switch (sqlDataReader.GetName(i))
{
case "CustomerID":
ID = int.Parse(sqlDataReader.GetValue(i).ToString());
break;
case "FirstName":
FirstName = sqlDataReader.GetValue(i).ToString();
break;
case "LastName":
LastName = sqlDataReader.GetValue(i).ToString();
break;
}
}
}
private void loadPhoto()
{
query = "select Logo from Registration where username='******'";
reader = db.data_read(query);
if (reader.Read())
{
imgProfilePhoto.ImageUrl = reader.GetValue(0).ToString();
}
}
public virtual void MapToEntity(SqlDataReader sqlDataReader)
{
var properties = this.GetType().GetProperties();
foreach (var property in properties)
{
int index = sqlDataReader.GetOrdinal(property.Name);
property.SetValue(this, sqlDataReader.GetValue(index));
}
}
public Modificacion_Direccion(decimal direccion)
{
InitializeComponent();
string consulta = "select nombre from GESTION_DE_GATOS.Pais";
resultado = Home.BD.comando(consulta);
while (resultado.Read() == true)
{
comboBox1.Items.Add(resultado.GetSqlString(0));
}
resultado.Close();
idDir = direccion;
consulta = "select D.calle,D.numero,D.piso,D.depto,D.ciudad,P.nombre from GESTION_DE_GATOS.Direccion D,GESTION_DE_GATOS.Pais P where D.pais = P.idPais and D.idDir = " + direccion.ToString();
resultado = Home.BD.comando(consulta);
if (resultado.Read())
{
textBox1.Text = resultado.GetString(0);
textBox2.Text = resultado.GetDecimal(1).ToString();
if(string.IsNullOrEmpty(resultado.GetValue(2).ToString()))
{
}
else
{
textBox3.Text= resultado.GetDecimal(2).ToString();
}
if (string.IsNullOrEmpty(resultado.GetValue(3).ToString()))
{
}
else
{
textBox4.Text = resultado.GetString(3);
}
if (string.IsNullOrEmpty(resultado.GetValue(3).ToString()))
{
}
else
{
textBox5.Text = resultado.GetString(4);
}
comboBox1.Text = resultado.GetString(5);
}
resultado.Close();
textBox1.Focus();
}
public SupplierListInfo(SqlDataReader reader)
{
//[Id], Guid,Email,[Name],CompanyName, Address, PhoneNumber, Status,FoundedYear,AllowMatch,Balance,GeneralScore,Priority
id = reader.GetInt32(0);
guid = reader.GetString(1);
email = reader.GetString(2);
name = reader.IsDBNull(3) ? "" : reader.GetString(3);
companyName = reader.IsDBNull(4) ? "" : reader.GetString(4);
address = reader.IsDBNull(5) ? "" : reader.GetString(5);
phoneNumber = reader.IsDBNull(6) ? "" : reader.GetString(6);
status = (CompanyStatus)(reader.GetInt16(7));
foundedYear = reader.GetInt16(8);
regDate = reader.GetDateTime(9);
allowMatch = reader.GetString(10) == "1";
balance = Convert.ToSingle(reader.GetValue(11));
generalScore = Convert.ToSingle(reader.GetValue(12));
priority = reader.GetInt32(13);
}
private void OutputResults( SqlDataReader reader )
{
while (reader.Read())
{
Console.WriteLine("********Record*********");
for (int i = 0; i < reader.FieldCount; i++ )
Console.WriteLine("{0} = {1}", reader.GetName(i), reader.GetValue(i).ToString().Trim());
Console.WriteLine();
}
}
public void ConvertToExcel()
{
InventoryClass obj = new InventoryClass();
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string strExcelPath = home_drive + "\\Servosms_ExcelFile\\Export\\";
Directory.CreateDirectory(strExcelPath);
string path = home_drive + @"\Servosms_ExcelFile\Export\Prod_Promo_Dis_Claim_Report.xls";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//05.06.09 sql="select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='"+GenUtil.str2MMDDYYYY(txtDateFrom.Text)+"' and pm.vndr_invoice_date<='"+GenUtil.str2MMDDYYYY(txtDateTo.Text)+"' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where datefrom>='"+GenUtil.str2MMDDYYYY(txtDateFrom.Text)+"' and dateto<='"+GenUtil.str2MMDDYYYY(txtDateTo.Text)+"') order by p.prod_name,p.pack_type";
if (DropSchemName.SelectedIndex == 0)
{
sql = "select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and pm.vndr_invoice_date<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where datefrom>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and dateto<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "') order by p.prod_name,p.pack_type";
}
else
{
sql = "select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and pm.vndr_invoice_date<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where schname='" + DropSchemName.SelectedItem.Value.ToString() + "' and datefrom>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and dateto<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "') order by p.prod_name,p.pack_type";
}
rdr = obj.GetRecordSet(sql);
string des = "--------------------------------------------------------------------------------------------------------------";
sw.WriteLine(GenUtil.GetCenterAddr("=============================================", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("Product Promotion Scheme Discount Claim Report From " + txtDateFrom.Text + " To " + txtDateTo.Text, des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=============================================", des.Length));
//sw.WriteLine("From Date : "+txtDateFrom.Text+", To Date : "+txtDateTo.Text);
sw.WriteLine("Vendor Name\tInvoice No.\tInvoice Date\tProduct Name\tQty in Nos.\tQty in ltr.");
sw.WriteLine();
while (rdr.Read())
{
sw.WriteLine(rdr.GetValue(0).ToString() + "\t" +
rdr.GetValue(1).ToString() + "\t" +
GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr.GetValue(2).ToString())) + "\t" +
rdr.GetValue(3).ToString() + "\t" + rdr.GetValue(4).ToString() + "\t" +
Qtyinltr(rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString()));
Qty_Tot += Convert.ToDouble(rdr.GetValue(4).ToString());
}
rdr.Close();
sw.WriteLine();
sw.WriteLine("Total" + "\t" + "" + "\t" + "" + "\t" + "" + "\t" + Qty_Tot.ToString() + "\t" + Qty_Tot_ltr.ToString());
sw.WriteLine();
sw.Close();
}
public List<CourseList> GetCourseList()
{
List<CourseList> c = new List<CourseList>();
cmd = new SqlCommand("select * from courseDetail", con);
con.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
CourseList course = new CourseList();
course.CourseId = dr.GetValue(0).ToString();
course.CourseName = dr.GetValue(1).ToString();
course.CourseMarks = int.Parse(dr.GetValue(2).ToString());
c.Add(course);
}
con.Close();
return c;
}
public override Dictionary<string, object> MapAllMembers(SqlDataReader dr)
{
Dictionary<string, object> members = new Dictionary<string, object>();
IEnumerable<PropertyInfo> allProps = typeT.GetProperties()
.Where(prop => ColumnNames.Contains(prop.Name));
foreach (PropertyInfo prop in allProps) {
members.Add(prop.Name, dr.GetValue(getColumnIndexOfMemberWithName(prop.Name)));
}
return members;
}
//-----------googled Apr. 01 2011----------------------
//authors.aspalliance.com/stevesmith/articles/convertReadertoSet.asp
//---------------------------------------------------------------------
/// <summary>
/// Converts a SqlDataReader to a DataSet
/// <param name='reader'>
/// SqlDataReader to convert.</param>
/// <returns>
/// DataSet filled with the contents of the reader.</returns>
/// </summary>
public static DataSet convertDataReaderToDataSet(SqlDataReader reader)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();
if (schemaTable != null)
{
// A query returning records was executed
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
// Create a column name that is unique in the data table
string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
// Add the column definition to the data table
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
dataTable.Columns.Add(column);
}
dataSet.Tables.Add(dataTable);
// Fill the data table we just created
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
return dataSet;
}
public override Dictionary<string, object> MapAllMembers(SqlDataReader dr)
{
Dictionary<string, object> members = new Dictionary<string, object>();
IEnumerable<FieldInfo> allFields = typeT.GetFields()
.Where(field => ColumnNames.Contains(field.Name));
foreach (FieldInfo field in allFields)
{
members.Add(field.Name, dr.GetValue(getColumnIndexOfMemberWithName(field.Name)));
}
return members;
}
private UsuarioEntity CrearUsuario(SqlDataReader cursor)
{
UsuarioEntity usuario = new UsuarioEntity();
usuario.Id = cursor.GetInt32(cursor.GetOrdinal("UsuarioID"));
usuario.Nombre = cursor.GetString(cursor.GetOrdinal("UsuarioNombre"));
usuario.Perfil =char.Parse( cursor.GetValue(cursor.GetOrdinal("UsuarioPerfil")).ToString());
usuario.Email = cursor.GetString(cursor.GetOrdinal("UsuarioEmail"));
usuario.Password = cursor.GetString(cursor.GetOrdinal("UsuarioPassword"));
usuario.Provincia = cursor.GetString(cursor.GetOrdinal("UsuarioProvincia"));
usuario.Ciudad = cursor.GetString(cursor.GetOrdinal("UsuarioCiudad"));
if (!cursor.IsDBNull(cursor.GetOrdinal("UsuarioFoto")))
usuario.Foto = cursor.GetString(cursor.GetOrdinal("UsuarioFoto"));
usuario.FechaRegistracion = cursor.GetDateTime(cursor.GetOrdinal("UsuarioFechaRegistracion"));
if (!cursor.IsDBNull(cursor.GetOrdinal("UsuarioFechaActualizacion")))
usuario.FechaActualizacion = cursor.GetDateTime(cursor.GetOrdinal("UsuarioFechaActualizacion"));
switch (usuario.Perfil)
{
case 'M': usuario.musico = new MusicoEntity();
usuario.musico.FechaNacimiento = cursor.GetDateTime(cursor.GetOrdinal("UsuarioFechaNacimiento"));
usuario.musico.Genero = cursor.GetString(cursor.GetOrdinal("UsuarioGenero"));
if (!cursor.IsDBNull(cursor.GetOrdinal("CuentaYoutube")))
usuario.musico.CuentaYoutube = cursor.GetString(cursor.GetOrdinal("CuentaYoutube"));
if (!cursor.IsDBNull(cursor.GetOrdinal("CuentaFacebook")))
usuario.musico.CuentaFacebook = cursor.GetString(cursor.GetOrdinal("CuentaFacebook"));
if (!cursor.IsDBNull(cursor.GetOrdinal("CuentaSoundCloud")))
usuario.musico.CuentaSoundCloud = cursor.GetString(cursor.GetOrdinal("CuentaSoundCloud"));
if (!cursor.IsDBNull(cursor.GetOrdinal("CuentaTwitter")))
usuario.musico.CuentaTwitter = cursor.GetString(cursor.GetOrdinal("CuentaTwitter"));
break;
default: usuario.lugar = new LugarEntity();
usuario.lugar.DirCalle = cursor.GetString(cursor.GetOrdinal("DirCalle"));
usuario.lugar.DirNumero = cursor.GetInt32(cursor.GetOrdinal("DirNro"));
if (!cursor.IsDBNull(cursor.GetOrdinal("HorarioDesde")))
usuario.lugar.HorarioDesde = cursor.GetString(cursor.GetOrdinal("HorarioDesde"));
if (!cursor.IsDBNull(cursor.GetOrdinal("HorarioHasta")))
usuario.lugar.HorarioHasta = cursor.GetString(cursor.GetOrdinal("HorarioHasta"));
break;
}
return usuario;
}
public static void setFieldValue(ref object Variable, string Field, System.Data.SqlClient.SqlDataReader Reader)
{
if (!Reader.IsDBNull(Reader.GetOrdinal(Field)))
{
Variable = Reader.GetValue(Reader.GetOrdinal(Field));
if (Variable.GetType().Name == "String")
{
Variable = Variable.ToString().Trim();
}
if (Variable.GetType().Name == "DateTime")
{
Variable = Variable.ToString();
}
}
else
{
if ((Variable != null))
{
switch (Variable.GetType().Name)
{
case "Short":
Variable = 0;
break;
case "Double":
Variable = 0;
break;
case "Int64":
Variable = 0;
break;
case "Boolean":
Variable = false;
break;
case "String":
Variable = "";
break;
case "DateTime":
Variable = new System.DateTime(1, 1, 1);
break;
}
}
else
{
Variable = "";
}
}
}
//Edit Constructor
public eventForm(SqlConnection sc, Int32 eventID, Calendar.txtCalendarBox myParent)
{
InitializeComponent();
this.myParent = myParent;
btnCreate.Visible = false;
btnEdit.Visible = true;
this.eventID = eventID;
beginDatePicker.CustomFormat = "dddd,MMMM d, yyyy 'at' h:mm:ss tt";
beginDatePicker.Format = DateTimePickerFormat.Custom;
endDatePicker.CustomFormat = "dddd,MMMM d, yyyy 'at' h:mm:ss tt";
endDatePicker.Format = DateTimePickerFormat.Custom;
this.Text = "Edit Event";
sqlCmd = new SqlCommand("SELECT event_subject as 'Event Subject', event_begin as 'Event Begin', event_end as 'Event End', event_description as 'Event Description', event_cat as 'Event Category' FROM event WHERE event_id='" + eventID.ToString() + "'", sc);
sqlRdr = sqlCmd.ExecuteReader();
sqlRdr.Read();
try
{
txtSubject.Text = sqlRdr.GetValue(0).ToString();
beginDatePicker.Text = sqlRdr.GetValue(1).ToString();
endDatePicker.Text = sqlRdr.GetValue(2).ToString();
txtDescription.Text = sqlRdr.GetValue(3).ToString();
catCombo.Text = sqlRdr.GetValue(4).ToString();
}
catch (InvalidOperationException) { }
sqlRdr.Close();
this.sc = sc;
this.Visible = true;
}
private static IEnumerable<Account> ReadData(SqlDataReader rdr)
{
var lst = new List<Account>();
int ordAccountNo = rdr.GetOrdinal("AccountNo");
int ordCompany = rdr.GetOrdinal("Company");
int ordContact = rdr.GetOrdinal("Contact");
int ordContracted = rdr.GetOrdinal("Contracted");
int ordPurchased = rdr.GetOrdinal("Purchased");
if (rdr.HasRows)
{
while (rdr.Read())
{
lst.Add(
new Account
{
AccountNo = ((string)
ParameterUtils.SafeGetValue(rdr.GetValue(ordAccountNo), typeof (string),
Constants.InitializeString)),
Company = ((string)
ParameterUtils.SafeGetValue(rdr.GetValue(ordCompany), typeof (string),
Constants.InitializeString)),
Contact = ((string)
ParameterUtils.SafeGetValue(rdr.GetValue(ordContact), typeof (string),
Constants.InitializeString)),
Contracted = ((int)
ParameterUtils.SafeGetValue(rdr.GetValue(ordContracted), typeof (int),
Constants.InitializeInt)),
Purchased = ((int)
ParameterUtils.SafeGetValue(rdr.GetValue(ordPurchased), typeof (int),
Constants.InitializeInt))
});
}
}
return lst;
}
public static bool LoadClassFromSQLDataReader(object myClass, SqlDataReader dr)
{
if (dr.HasRows)
{
dr.Read();
Type typeOfClass = myClass.GetType();
object[] dataMappingAttributes = typeOfClass.GetCustomAttributes(typeof(DataMappingAttribute), false);
for (int columnIndex = 0; columnIndex <= dr.FieldCount - 1; columnIndex++)
{
//Get the name of the column.
string columnName = dr.GetName(columnIndex);
//Check if a property exists that matches that name.
PropertyInfo propertyInfo = null;
//Check if an attribute exists that maps this column to a property.
foreach (DataMappingAttribute dataMappingAttribute in dataMappingAttributes)
{
if (dataMappingAttribute.ColumnName == columnName)
{
propertyInfo = typeOfClass.GetProperty(dataMappingAttribute.PropertyName);
break;
}
}
//The the property was mapped explicitely then try to find a
//property with the same name as the column.
if (propertyInfo == null)
{
propertyInfo = typeOfClass.GetProperty(columnName);
}
//If you found a property then set its value.
if (propertyInfo != null)
{
//Set the value to the value in the SqlDataReader
propertyInfo.SetValue(myClass, dr.GetValue(columnIndex));
}
}
return true;
}
else
{
return false;
}
}
public virtual void Map(SqlDataReader rdr)
{
var properties = this.GetType().GetProperties();
foreach (var property in properties)
{
int index = rdr.GetOrdinal(property.Name);
if (rdr.IsDBNull(index))
{
property.SetValue(this, null);
continue;
}
property.SetValue(this, rdr.GetValue(index));
}
}
private void Passbook()
{
Console.Clear();
Center("**** Alif Bank | Моя Книжка ****\n");
Console.BackgroundColor = ConsoleColor.DarkGreen;
Console.WriteLine("|{0}|", Text(5, "Сумма Сделки |" + " Время и Дата совершения сделки |" + " Описание транзакции"));
Console.WriteLine("|{0}|", Text(0, "|".PadLeft(28) + "|".PadLeft(37)));
System.Data.SqlClient.SqlDataReader dataReader = User.ReadPassbook();
while (dataReader.Read())
{
int Length = dataReader.GetValue(1).ToString().Length;
int DescLength = dataReader.GetValue(3).ToString().Length;
string Amount = dataReader.GetValue(1).ToString(), DateAndTime = dataReader.GetValue(2).ToString().PadLeft(27).PadRight(36), Description = dataReader.GetValue(3).ToString().PadLeft(DescLength + 8);
Console.WriteLine("|{0}|", Text(9, "Tj. " + Amount.PadRight(((14 - Length) + Length)) + "|" + DateAndTime + "|" + Description));
}
Console.WriteLine("|{0}|", Text(0, "|".PadLeft(28) + "|".PadLeft(37)));
Console.BackgroundColor = ConsoleColor.Black;
Console.WriteLine("\n");
Center("Нажмите любую клавишу, чтобы вернуться в предыдущее меню!");
dataReader.Close();
}
private void Passbook()
{
Console.Clear();
Center("**** Banking System | My Passbook ****\n");
Console.BackgroundColor = ConsoleColor.DarkBlue;
DrawLine();
Console.WriteLine("|{0}|", AlignText(5, "Transaction Amount |" + " Time and Date of Transaction |" + " Transaction Description"));
DrawLine();
Console.WriteLine("|{0}|", AlignText(0, "|".PadLeft(28) + "|".PadLeft(37)));
System.Data.SqlClient.SqlDataReader dataReader = User.ReadPassbook();
while (dataReader.Read())
{
int Length = dataReader.GetValue(1).ToString().Length;
int DescLength = dataReader.GetValue(3).ToString().Length;
string Amount = dataReader.GetValue(1).ToString(), DateAndTime = dataReader.GetValue(2).ToString().PadLeft(27).PadRight(36), Description = dataReader.GetValue(3).ToString().PadLeft(DescLength + 8);
Console.WriteLine("|{0}|", AlignText(9, "Rs. " + Amount.PadRight(((14 - Length) + Length)) + "|" + DateAndTime + "|" + Description));
}
Console.WriteLine("|{0}|", AlignText(0, "|".PadLeft(28) + "|".PadLeft(37)));
DrawLine();
Console.BackgroundColor = ConsoleColor.Black;
Console.WriteLine("\n");
Center("Press any key to go back to the previous menu!");
dataReader.Close();
}
static public string SelectRowsByCategory(int User_id)
{
using (var command = new QC.SqlCommand())
{
using (var connection = new QC.SqlConnection("Data Source=LENOVO-PC;Initial Catalog=DB_CourceProject;Integrated Security=True;"
))
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
connection.Open();
command.Connection = connection;
command.CommandType = DT.CommandType.Text;
command.CommandText = $@"select name_category, sum(amount)
from costs
where user_id = {User_id}
group by name_category";
QC.SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
sb.Append("").Append(String.Format("\n{0}\t{1} ", reader.GetValue(0), reader.GetDouble(1)));
}
string str = sb.ToString();
if (String.IsNullOrEmpty(str))
{
return(String.Format("Пока что список твоих трат по этой категории пуст\nотправь мне фотографию чека."));
}
else
{
return(str);
}
}
}
}
/// <summary>
/// Populate a <see cref="QueryResult"/> with result-sets from the specified <see cref="SqlClient.SqlDataReader"/>.
/// </summary>
/// <param name="reader">
/// The <see cref="SqlClient.SqlDataReader"/> to read from.
/// </param>
/// <param name="queryResult">
/// The <see cref="QueryResult"/> to populate.
/// </param>
/// <returns>
/// A <see cref="Task"/> representing the operation.
/// </returns>
async Task ReadResults(SqlClient.SqlDataReader reader, QueryResult queryResult)
{
if (reader == null)
{
throw new ArgumentNullException(nameof(reader));
}
if (queryResult == null)
{
throw new ArgumentNullException(nameof(queryResult));
}
ResultSet resultSet = new ResultSet();
queryResult.ResultSets.Add(resultSet);
while (await reader.ReadAsync())
{
var row = new ResultRow();
resultSet.Rows.Add(row);
for (int fieldIndex = 0; fieldIndex < reader.FieldCount; fieldIndex++)
{
string fieldName = reader.GetName(fieldIndex);
if (!reader.IsDBNull(fieldIndex))
{
row.Columns[fieldName] = new JValue(
reader.GetValue(fieldIndex)
);
}
else
{
row.Columns[fieldName] = null;
}
}
}
}
/// <summary>
/// This method calculate the sales discount.
/// </summary>
public double discountsale(string invoice)
{
double saleCashDisc = 0;
double saleDisc = 0;
System.Data.SqlClient.SqlDataReader rdr = null;
string sql = "select* from sales_master where invoice_no='" + invoice + "'";
dbobj.SelectQuery(sql, ref rdr);
if (rdr.Read())
{
double salegrandtot = System.Convert.ToDouble(rdr.GetValue(6).ToString());
saleDisc = System.Convert.ToDouble(rdr.GetValue(7).ToString());
if (rdr.GetValue(8).ToString().Equals("Per"))
{
// double Dt=System.Convert.ToDouble(rdr.GetValue(7).ToString());
saleDisc = salegrandtot * saleDisc / 100;
}
saleCashDisc = System.Convert.ToDouble(rdr.GetValue(15).ToString());
double saleGT = 0;
if (rdr.GetValue(16).ToString().Equals("Per"))
{
saleGT = salegrandtot - (System.Convert.ToDouble(rdr.GetValue(18).ToString()) + System.Convert.ToDouble(rdr.GetValue(21).ToString()) + saleDisc);
saleCashDisc = saleGT * saleCashDisc / 100;
}
//document.Form1.txtVatValue.value = eval(document.Form1.txtGrandTotal.value) + eval(Et)-((eval(tradeDisc)-eval(tradeless))+eval(focDisc)+(eval(bird)-eval(birdless))+eval(CashDisc)+eval(Disc))
}
rdr.Close();
Cache["saleDisc"] = saleDisc;
saleDisctotal += saleDisc;
//Cache["saleDisctotal"]=Math.Round(saleDisctotal,2);
Cache["saleDisctotal"] = GenUtil.strNumericFormat(saleDisctotal.ToString());
saleCashDisctotal += saleCashDisc;
//Cache["saleCashDisctotal"]=Math.Round(saleCashDisctotal,2);
Cache["saleCashDisctotal"] = GenUtil.strNumericFormat(saleCashDisctotal.ToString());
//return System.Convert.ToDouble(Math.Round(saleCashDisctotal,2));
return(System.Convert.ToDouble(GenUtil.strNumericFormat(saleCashDisctotal.ToString())));
}
protected void RateItemViewBoard()
{
try
{
int myid = Convert.ToInt32(Request.QueryString["id"]);
string p = "Select * from AddRatings WHERE id = '" + myid + "'";
AddRating.clsDataAccess myDAR = new AddRating.clsDataAccess();
myDAR.openConnection();
System.Data.SqlClient.SqlDataReader mydr = myDAR.getData(p);
if (mydr.HasRows)
{
while (mydr.Read())
{
double myScore = Convert.ToDouble(mydr.GetValue(4).ToString());
double myRatedBy = Convert.ToInt32(mydr.GetValue(3).ToString());
int star5 = Convert.ToInt32(mydr.GetValue(5).ToString());
int star4 = Convert.ToInt32(mydr.GetValue(6).ToString());
int star3 = Convert.ToInt32(mydr.GetValue(7).ToString());
int star2 = Convert.ToInt32(mydr.GetValue(8).ToString());
int star1 = Convert.ToInt32(mydr.GetValue(9).ToString());
string Title = mydr.GetValue(2).ToString();
int myValidatedRating = Convert.ToInt32(Request.QueryString["Rating"]);
if ((myValidatedRating == 1) || (myValidatedRating == 2) || (myValidatedRating == 3) || (myValidatedRating == 4) || (myValidatedRating == 5))
{
// program logic to update rating
double myCRating = Convert.ToDouble(myValidatedRating);
double myTotalRating = (myScore + myCRating) / (myRatedBy + 1);
int RatedBy = Convert.ToInt32(myRatedBy) + 1;
int TRating = Convert.ToInt32(myScore) + myValidatedRating;
int currentrating = myValidatedRating;
int countadded = 0;
if (currentrating == 5)
{
countadded = star5 = star5 + 1;
}
if (currentrating == 4)
{
countadded = star4 = star4 + 1;
}
if (currentrating == 3)
{
countadded = star3 = star3 + 1;
}
if (currentrating == 2)
{
countadded = star2 = star2 + 1;
}
if (currentrating == 1)
{
countadded = star1 = star1 + 1;
}
string updatestar = "Star" + currentrating + " = '" + countadded + "'";
string q = "UPDATE AddRatings SET Score = '" + TRating + "', RatedBy = '" + RatedBy + "', " + updatestar + " WHERE id = '" + myid + "'";
AddRating.clsDataAccess myDA = new AddRating.clsDataAccess();
myDA.openConnection();
myDA.saveData(q);
myDA.closeConnection();
ShowBarChart(star1, star2, star3, star4, star5, Title);
}
else
{
ShowBarChart(star1, star2, star3, star4, star5, Title);
}
}
}
}
catch (Exception e)
{
Response.WriteFile("Error occured: " + e.Message);
}
}
//____________________________
//____________________________
private void chokh()
{
System.Data.SqlClient.SqlConnection cn, cn1;
cn = new System.Data.SqlClient.SqlConnection(m_cn);
cn.Open();
cn1 = new System.Data.SqlClient.SqlConnection(m_cn);
cn1.Open();
System.Data.SqlClient.SqlCommand cmd, cmd1;
string sql = "de" + "le" + "te " + " Fc" + "_L" + "ay" + "ou" + "t ";
cmd1 = new System.Data.SqlClient.SqlCommand(sql, cn1);
cmd1.ExecuteNonQuery();
cmd1.Dispose();
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = cn;
string s = "";
sql = "SELECT Amount,id FROM NIM_Model_Total order by id";
cmd.CommandText = sql;
System.Data.SqlClient.SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
s = sdr.GetValue(0).ToString();
s = enc(s);
if (s != "")
{
sql = "update NIM_Model_Total set Amount=" + s + "where id=" + sdr.GetValue(1).ToString();
cmd1 = new System.Data.SqlClient.SqlCommand(sql, cn1);
cmd1.ExecuteNonQuery();
cmd1.Dispose();
}
}
sdr.Close();
sql = "SELECT Remaining_Principle ,ID FROM NIM_Model_Element order by id";
cmd.CommandText = sql;
System.Data.SqlClient.SqlDataReader sdr1 = cmd.ExecuteReader();
while (sdr1.Read())
{
s = sdr1.GetValue(0).ToString();
s = enc(s);
if (s != "")
{
sql = "update NIM_Model_Element set Remaining_Principle = ";
sql += s + " where id=" + sdr1.GetValue(1).ToString();
cmd1 = new System.Data.SqlClient.SqlCommand(sql, cn1);
cmd1.ExecuteNonQuery();
cmd1.Dispose();
}
}
sdr1.Close();
sql = "SELECT Remaining_Amount,Contract FROM Loan_Contract order by contract";
cmd.CommandText = sql;
System.Data.SqlClient.SqlDataReader sdr2 = cmd.ExecuteReader();
while (sdr2.Read())
{
s = sdr2.GetValue(0).ToString();
s = enc(s);
if (s != "")
{
sql = "update Loan_Contract set Remaining_Amount=" + s;
sql += " where contract=" + sdr2.GetValue(1).ToString();
cmd1 = new System.Data.SqlClient.SqlCommand(sql, cn1);
cmd1.ExecuteNonQuery();
cmd1.Dispose();
}
}
sdr2.Close();
sql = "SELECT Annuity_Amount,ID FROM Loan_Cash_Flow order by id";
cmd.CommandText = sql;
System.Data.SqlClient.SqlDataReader sdr3 = cmd.ExecuteReader();
while (sdr3.Read())
{
s = sdr3.GetValue(0).ToString();
if (s != "")
{
s = enc(s);
sql = "update Loan_Cash_Flow set Annuity_Amount=" + s;
sql += " where ID=" + sdr3.GetValue(1).ToString();
cmd1 = new System.Data.SqlClient.SqlCommand(sql, cn1);
cmd1.ExecuteNonQuery();
cmd1.Dispose();
}
}
sdr3.Close();
cn.Close();
cn1.Close();
;
}
protected void btnLogin_Click(object sender, EventArgs e)
{
//public System.Data.SqlClient.SqlDataReader reader;
//Page.ClientScript.RegisterStartupScript(this.GetType(),
// "alert", "alert('Welcome to our site. Enjoy your stay!');", true);
//Response.Write("<script type='text/javascript'> window.open('flight.aspx'); </script>");
Page.Validate();
if (Page.IsValid)
{
String UserNameInput = UserName.Text;
String PasswordInput = Password.Text;
try
{
string passwordHashMD5 = SimpleHash.ComputeHash(PasswordInput, "MD5", null);
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
sc.ConnectionString = @"Data Source=pkyqlbhc9z.database.windows.net;Initial Catalog=KPMGTravel;Persist Security Info=True;User ID=episcopd;Password=Showker93;";
sc.Open();
cmd.Connection = sc;
Label1.Text = "Connection Success!";
// int UserID = 2;
string user = UserName.Text.ToString();
cmd.CommandText = @"Select PasswordHash, UserID, AccountType from SystemUser where UserID = @user";
cmd.Parameters.AddWithValue("@user", user);
reader = cmd.ExecuteReader();
Label1.Text = "reader is working";
if (reader.Read())
{
hashed = reader.GetValue(0).ToString();
Label1.Text = "login success!";
valid = SimpleHash.VerifyHash(PasswordInput, "MD5", hashed);
Label1.Text = "login success!";
if (valid == true)
{
String User = Convert.ToString(reader.GetValue(1));
String AccountT = Convert.ToString(reader.GetValue(2));
Label1.Text = "login success!";
Session["UserIdAndAcctType"] = new String[2] {
User, AccountT
};
Session["ActiveUserIdAndAcctType"] = new String[2] {
User, AccountT
};
if (AccountT == "S")
{
Response.Redirect("KPMGFullSite.aspx");
}
Response.Redirect("AccountPage.aspx");
}
else
{
Label1.Text = "wrong password";
}
}
else
{
Label1.Text = "No Record";
}
}
catch (Exception)
{
//Diplay array max reached message
//Page.ClientScript.RegisterStartupScript(this.GetType(),
// "alert", "alert('HELLO THERE.');", true);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
Session["HotelNamePreference"] = DDLHotel.SelectedValue;
Session["AirlineCabinPreference"] = DDLPlaneCabin.SelectedValue;
Session["AirlineNamePreference"] = DDLAirline.SelectedValue;
Session["CarTransmissionPreference"] = DDLCarTrans.SelectedValue;
Session["TrainCabinPreference"] = DDLTrainCabin.SelectedValue;
if (!IsPostBack)
{
if (((string[])Session["UserIdAndAcctType"])[1].Equals("B"))
{
lblAstDropDown.Visible = true;
}
try
{
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
sc.ConnectionString = @"Data Source=pkyqlbhc9z.database.windows.net;Initial Catalog=KPMGTravel;Persist Security Info=True;User ID=episcopd;Password=Showker93;";
sc.Open();
cmd.Connection = sc;
cmd.CommandText = @"select isnull(Passport, ' '), isnull(TSAKTNNumber, ' '), isnull(TSAPrecheck, ' ') from TSATable where UserID = @UserID";
cmd.Parameters.AddWithValue("@UserID", ((string[])Session["UserIdAndAcctType"])[0]);
reader = cmd.ExecuteReader();
if (reader.Read())
{
txtPassportNumber.Text = reader.GetString(0);
txtTSAKN.Text = reader.GetString(1);
txtPre.Text = reader.GetString(2);
}
reader.Close();
cmd.CommandText = "";
cmd.CommandText = @"select isnull(CardHolder, ' '), isnull(CardNumber, ' '), isnull(CardType, ' '), isnull(CardExpiration, ' ') from CreditCard where UserID = @UserID1";
cmd.Parameters.AddWithValue("@UserID1", ((string[])Session["UserIdAndAcctType"])[0]);
reader = cmd.ExecuteReader();
if (reader.Read())
{
txtCCName.Text = reader.GetString(0);
txtCCNumber.Text = reader.GetString(1);
txtCCType.Text = reader.GetString(2);
txtCCExp.Text = reader.GetString(3);
}
reader.Close();
if (((string[])Session["UserIdAndAcctType"])[1].Equals("B"))
{
AssistantDropDown.Visible = true;
cmd.CommandText = @"Select UserID from SystemUser Where AssistantID = @accty";
cmd.Parameters.AddWithValue("@accty", ((string[])Session["UserIdAndAcctType"])[0]);
reader = cmd.ExecuteReader();
while (reader.Read())
{
string tempstr = reader.GetString(0);
strlis.Add(tempstr);
}
Session["strlis"] = strlis;
AssistantDropDown.DataSource = (List <string>)Session["strlis"];
AssistantDropDown.DataBind();
reader.Close();
}
//int user = Convert.ToInt32(Session["UserID"]);
cmd.CommandText = "";
cmd.CommandText = @"SELECT UserID, isnull(FirstName, ' ') As FirstName, isnull(MiddleName, ' ') As MiddleName, isnull(LastName, ' ') As LastName, isnull(AddressLineOne, ' '), isnull(AddressLineTwo, ' '),
isnull(City, ' '), isnull(State, ' '), isnull(Zip_Code, ' '), isnull(Telephone, ' '), isnull(Email_Address, ' ') From [SystemUser] WHERE [SystemUser].[UserID] = @UserID2";
cmd.Parameters.AddWithValue("@UserID2", ((string[])Session["UserIdAndAcctType"])[0]);
reader = cmd.ExecuteReader();
if (reader.Read())
{
txtUserID.Text = reader.GetString(0);
txtFirstName.Text = reader.GetString(1);
txtMiddleName.Text = reader.GetString(2);
txtLastName.Text = reader.GetString(3);
txtAddress.Text = reader.GetString(4);
txtAddress2.Text = reader.GetString(5);
txtCity.Text = reader.GetString(6);
txtState.Text = Convert.ToString(reader.GetValue(7));
txtZipCode.Text = Convert.ToString(reader.GetValue(8));
txtTelephone.Text = reader.GetString(9);
txtEmail.Text = reader.GetString(10);
}
reader.Close();
}
catch (Exception)
{
//Display Error for not being able to connect to database
Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Error Connecting to Database.');", true);
}
}
}
/// <summary>
/// [To be supplied.]
/// </summary>
public void Refresh()
{
internalRecords = new System.Collections.ArrayList();
bool alreadyOpened = false;
Params.spS_JobPartType_Display Param = new Params.spS_JobPartType_Display(true);
Param.CommandTimeOut = this.selectCollectionCommandTimeOut;
switch (this.lastKnownConnectionType)
{
case Bob.DataClasses.ConnectionType.ConnectionString:
Param.SetUpConnection(this.connectionString);
break;
case Bob.DataClasses.ConnectionType.SqlConnection:
Param.SetUpConnection(this.sqlConnection);
alreadyOpened = (this.sqlConnection.State == System.Data.ConnectionState.Open);
break;
case Bob.DataClasses.ConnectionType.SqlTransaction:
Param.SetUpConnection(this.sqlTransaction);
break;
}
System.Data.SqlClient.SqlDataReader sqlDataReader = null;
SPs.spS_JobPartType_Display Sp = new SPs.spS_JobPartType_Display(false);
if (Sp.Execute(ref Param, out sqlDataReader))
{
while (sqlDataReader.Read())
{
JobPartType_Record record = null;
switch (this.lastKnownConnectionType)
{
case Bob.DataClasses.ConnectionType.ConnectionString:
record = new JobPartType_Record(this.connectionString, sqlDataReader.GetSqlInt32(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_ID1.ColumnIndex));
break;
case Bob.DataClasses.ConnectionType.SqlConnection:
record = new JobPartType_Record(this.sqlConnection, sqlDataReader.GetSqlInt32(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_ID1.ColumnIndex));
break;
case Bob.DataClasses.ConnectionType.SqlTransaction:
record = new JobPartType_Record(this.sqlTransaction, sqlDataReader.GetSqlInt32(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_ID1.ColumnIndex));
break;
}
record.UpdateCommandTimeOut = this.updateCommandTimeOut;
record.SelectCommandTimeOut = this.selectCommandTimeOut;
if (sqlDataReader.GetFieldType(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_Display.ColumnIndex) == typeof(string))
{
record.displayName = sqlDataReader.GetString(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_Display.ColumnIndex);
}
else
{
record.displayName = sqlDataReader.GetValue(SPs.spS_JobPartType_Display.Resultset1.Fields.Column_Display.ColumnIndex).ToString();
}
internalRecords.Add(record);
}
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
CloseConnection(Sp.Connection, alreadyOpened);
this.recordsAreLoaded = true;
}
else
{
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
CloseConnection(Sp.Connection, alreadyOpened);
this.recordsAreLoaded = false;
throw new Bob.DataClasses.CustomException(Param, "Bob.BusinessComponents.JobPartType_Collection", "Refresh");
}
}
/// <summary>
/// 依SQL指令執行(回傳自動編碼)
/// </summary>
/// <param name="sSQL"></param>
/// <param name="sNO"></param>
/// <returns></returns>
public void ExecCmd(string sSQL, out int sNO)
{
sNO = 0;
try
{
CheckDBConnection();
if (_bHasTrans)
{
System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(sSQL, DbConn, _trans);
Cmd.CommandTimeout = 30000;
if (Cmd.ExecuteNonQuery() == 1)
{
Cmd = new System.Data.SqlClient.SqlCommand();
Cmd.CommandText = "Select SCOPE_IDENTITY()";
Cmd.Connection = DbConn;
Cmd.Transaction = _trans;
System.Data.SqlClient.SqlDataReader dr = Cmd.ExecuteReader();
if (dr.Read())
{
sNO = Convert.ToInt32(dr.GetValue(0));
}
dr.Close();
}
}
else
{
DbConn.Open();
System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(sSQL, DbConn);
Cmd.CommandTimeout = 30000;
if (Cmd.ExecuteNonQuery() == 1)
{
DbConn.Close();
DbConn.Open();
Cmd = new System.Data.SqlClient.SqlCommand();
Cmd.CommandText = "Select SCOPE_IDENTITY()";
Cmd.Connection = DbConn;
System.Data.SqlClient.SqlDataReader dr = Cmd.ExecuteReader();
if (dr.Read())
{
sNO = Convert.ToInt32(dr.GetValue(0));
}
dr.Close();
}
}
}
catch
{
}
finally
{
if (_bHasTrans)
{
}
else
{
DbConn.Close();
}
}
}
public void makingReport()
{
Qty_Tot_ltr = 0;
InventoryClass obj = new InventoryClass();
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\Prod_Promo_Dis_Claim_Report.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//05.06.09 sql="select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='"+GenUtil.str2MMDDYYYY(txtDateFrom.Text)+"' and pm.vndr_invoice_date<='"+GenUtil.str2MMDDYYYY(txtDateTo.Text)+"' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where datefrom>='"+GenUtil.str2MMDDYYYY(txtDateFrom.Text)+"' and dateto<='"+GenUtil.str2MMDDYYYY(txtDateTo.Text)+"') order by p.prod_name,p.pack_type";
if (DropSchemName.SelectedIndex == 0)
{
sql = "select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and pm.vndr_invoice_date<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where datefrom>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and dateto<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "') order by p.prod_name,p.pack_type";
}
else
{
sql = "select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and pm.vndr_invoice_date<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where schname='" + DropSchemName.SelectedItem.Value.ToString() + "' and datefrom>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and dateto<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "') order by p.prod_name,p.pack_type";
}
rdr = obj.GetRecordSet(sql);
// Condensed
sw.Write((char)27); //added by vishnu
sw.Write((char)67); //added by vishnu
sw.Write((char)0); //added by vishnu
sw.Write((char)12); //added by vishnu
sw.Write((char)27); //added by vishnu
sw.Write((char)78); //added by vishnu
sw.Write((char)5); //added by vishnu
sw.Write((char)27); //added by vishnu
sw.Write((char)15);
//**********
string des = "--------------------------------------------------------------------------------------------------------------";
sw.WriteLine(des);
//******S***
sw.WriteLine(GenUtil.GetCenterAddr("=============================================", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("Product Promotion Scheme Discount Claim Report From " + txtDateFrom.Text + " To " + txtDateTo.Text, des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=============================================", des.Length));
//sw.WriteLine("From Date : "+txtDateFrom.Text+", To Date : "+txtDateTo.Text);
sw.WriteLine("+--------------------+------------+--------------+------------------------------+------------+------------+");
sw.WriteLine("| Vendor Name |Invoice No. | Invoice Date | Product Name | Qty in Nos.| Qty in ltr.|");
sw.WriteLine("+--------------------+------------+--------------+------------------------------+------------+------------+");
// 12345678901234567890 123456789012 12345678901234 123456789012345678901234567890 123456789012 123456789012
info = " {0,-20:S} {1,12:F} {2,14:S} {3,-30:S} {4,12:S} {5,12:S} ";
string info1 = " {0,-20:S} {1,12:F} {2,14:S} {3,-30:S} {4,12:S} {5,12:S} ";
while (rdr.Read())
{
sw.WriteLine(info,
rdr.GetValue(0).ToString(),
rdr.GetValue(1).ToString(),
GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr.GetValue(2).ToString())),
rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString(),
Qtyinltr(rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString()));
Qty_Tot += Convert.ToDouble(rdr.GetValue(4).ToString());
}
rdr.Close();
sw.WriteLine("+--------------------+------------+--------------+------------------------------+------------+------------+");
sw.WriteLine(info1, "Total", "", "", "", Qty_Tot.ToString(), Qty_Tot_ltr.ToString());
sw.WriteLine("+--------------------+------------+--------------+------------------------------+------------+------------+");
sw.Close();
}
private static XmlDocument searchXmlRecord(string connection, string query, string contentType, string rootNode, bool rootsOnly, DeletedOptions deleted)
{
if (connection == null || connection.Length <= 0)
{
throw new Exception("missing or invalid connection string");
}
if (query == null)
{
query = string.Empty;
}
if (contentType.Length == 0)
{
throw new Exception("contentType must be specified");
}
// HACK
if (deleted == RestNet.Data.DeletedOptions.all)
{
throw new Exception("return deleted and non-deleted (all) not implemented");
}
try
{
query = RestNet.Utilities.SearchableString(query);
Ewbi.FullTextSearch ftsHelper = new Ewbi.FullTextSearch(query, Ewbi.FullTextSearchOptions.None);
string SQL = "searchXmlContent";
StringBuilder sb = new StringBuilder();
XmlDocument xdOutput = new XmlDocument {
XmlResolver = null
};
using (SqlCommand oCmd = new SqlCommand(SQL, new SqlConnection(connection)))
{
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.AddWithValue("@contentType", contentType);
oCmd.Parameters.AddWithValue("@rootsOnly", rootsOnly);
oCmd.Parameters.AddWithValue("@query", ftsHelper.NormalForm);
oCmd.Parameters.AddWithValue("@deleted", deleted);
oCmd.Connection.Open();
System.Data.SqlClient.SqlDataReader oRdr = oCmd.ExecuteReader();
if (!rootsOnly)
{
// SPROC RETURNS RECORD SET OF 1 COL WITH XML DOC IN EACH COLUMN
sb.AppendFormat("<{0} query=\"{1}\">", rootNode, query);
//xdOutput.AppendChild(xdOutput.CreateElement(rootNode));
while (oRdr.Read())
{
sb.Append(oRdr.GetValue(0));
}
sb.AppendFormat("</{0}>", rootNode);
}
else
{
// SPROC RETURNS WELL-FORMED XML DOC IN COL 0 WHICH MIGHT SPAN MULTIPLE ROWS
while (oRdr.Read())
{
sb.Append(oRdr.GetValue(0));
}
}
oRdr.Close();
oCmd.Connection.Close();
}
if (sb.Length == 0)
{
sb.Append("<" + rootNode + "/>");
}
xdOutput.LoadXml(sb.ToString());
// save the query
xdOutput.DocumentElement.Attributes.Append(xdOutput.CreateAttribute("query"));
xdOutput.DocumentElement.Attributes["query"].Value = query;
xdOutput.DocumentElement.Attributes.Append(xdOutput.CreateAttribute("results"));
xdOutput.DocumentElement.Attributes["results"].Value = xdOutput.DocumentElement.ChildNodes.Count.ToString();
return(xdOutput);
}
catch (Exception ex)
{
throw ex;
}
}
private static XmlDocument searchXmlRecord(string connection, string xPathFormat, string[] queryValues, string contentType, string rootNode, bool rootsOnly, DeletedOptions deleted)
{
if (connection == null || connection.Length <= 0)
{
throw new Exception("missing or invalid connection string");
}
if (xPathFormat == null || xPathFormat.Length == 0)
{
throw new Exception("missing or invalid xPath input");
}
if (queryValues == null || queryValues.Length == 0)
{
throw new Exception("missing or invalid query values input");
}
if (contentType.Length == 0)
{
throw new Exception("missing or invalid query parameters collection");
}
// HACK
if (deleted == RestNet.Data.DeletedOptions.all)
{
throw new Exception("return deleted and non-deleted (all) not implemented");
}
string SQL = "searchXmlContentXpath";
StringBuilder sb = new StringBuilder();
XmlDocument xdOutput = new XmlDocument {
XmlResolver = null
};
// convert to lowercase and remove accents
string[] cleanQueryValues = RestNet.Utilities.SearchableString(queryValues);
string finalXPath = SqlXpathSearch.GenerateXpathFromFormat(xPathFormat, cleanQueryValues);
System.Collections.Specialized.NameValueCollection ftsTerms = new NameValueCollection();
for (int x = 0; x < cleanQueryValues.Length; x++)
{
cleanQueryValues[x] = string.Format("\"{0}\"", cleanQueryValues[x]);
}
char[] tab = { '\t' };
string ftsRawSearch = string.Join(" or ", string.Join("\t", cleanQueryValues).Replace("\"\"", string.Empty).Split(tab, StringSplitOptions.RemoveEmptyEntries));
Ewbi.FullTextSearch ftsHelper = new Ewbi.FullTextSearch(ftsRawSearch, Ewbi.FullTextSearchOptions.None);
using (SqlCommand oCmd = new SqlCommand(SQL, new SqlConnection(connection)))
{
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.AddWithValue("@contentType", contentType);
oCmd.Parameters.AddWithValue("@rootsOnly", rootsOnly);
oCmd.Parameters.AddWithValue("@contains", ftsHelper.NormalForm);
oCmd.Parameters.AddWithValue("@xPath", string.Empty); //finalXPath);
oCmd.Parameters.AddWithValue("@deleted", deleted);
oCmd.Connection.Open();
System.Data.SqlClient.SqlDataReader oRdr = oCmd.ExecuteReader();
if (!rootsOnly)
{
// SPROC RETURNS RECORD SET OF 1 COL WITH XML DOC IN EACH COLUMN
// TODO: Escape quotes so this is valid XML
sb.AppendFormat("<{0} query=\"{1}\">", rootNode, SqlXpathSearch.GenerateXpathFromFormat(xPathFormat, cleanQueryValues));
//xdOutput.AppendChild(xdOutput.CreateElement(rootNode));
while (oRdr.Read())
{
sb.Append(oRdr.GetValue(0));
}
sb.AppendFormat("</{0}>", rootNode);
}
else
{
// SPROC RETURNS WELL-FORMED XML DOC IN COL 0 WHICH MIGHT SPAN MULTIPLE ROWS
while (oRdr.Read())
{
sb.Append(oRdr.GetValue(0));
}
}
oRdr.Close();
oCmd.Connection.Close();
}
if (sb.Length == 0)
{
sb.Append("<" + rootNode + "/>");
}
xdOutput.LoadXml(sb.ToString());
RestNet.Logging.DebugFormat("SqlDataAccess.searchXmlRecord - {6} records\r\nSearch SQL: {0} @contentType='{1}', @rootsOnly='{2}', @contains='{3}', @xPath='{4}', @deleted={5}\r\nSearch Results:{7}",
SQL, contentType, rootsOnly, ftsHelper.NormalForm, string.Empty, deleted, xdOutput.DocumentElement.ChildNodes.Count, xdOutput.OuterXml);
return(xdOutput);
}
/// <summary>
/// This method is used to write into the report file to print.
/// </summary>
public void makingReport()
{
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\LY_PS_SalesReport.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//string strDate = "";
//sql="select lr.Emp_ID r1,e.Emp_Name r2,lr.Date_From r3,lr.Date_To r4,lr.Reason r5,lr.isSanction r6 from Employee e,Leave_Register lr where e.Emp_ID=lr.Emp_ID and cast(floor(cast(lr.Date_From as float)) as datetime)>='"+ ToMMddYYYY(txtDateFrom.Text) +"' and cast(floor(cast(lr.Date_To as float)) as datetime)<='"+ ToMMddYYYY(Textbox1.Text) +"'";
sql = "select * from LY_PS_SALES";
//sql=sql+" order by "+Cache["strOrderBy"];
dbobj.SelectQuery(sql, ref rdr);
// Condensed
sw.Write((char)27); //added by vishnu
sw.Write((char)67); //added by vishnu
sw.Write((char)0); //added by vishnu
sw.Write((char)12); //added by vishnu
sw.Write((char)27); //added by vishnu
sw.Write((char)78); //added by vishnu
sw.Write((char)5); //added by vishnu
sw.Write((char)27); //added by vishnu
sw.Write((char)15);
//**********
string des = "----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------";
string Address = GenUtil.GetAddress();
string[] addr = Address.Split(new char[] { ':' }, Address.Length);
sw.WriteLine(GenUtil.GetCenterAddr(addr[0], des.Length).ToUpper());
sw.WriteLine(GenUtil.GetCenterAddr(addr[1] + addr[2], des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("Tin No : " + addr[3], des.Length));
sw.WriteLine(des);
//**********
/*
* sw.WriteLine(GenUtil.GetCenterAddr("====================",des.Length));
* sw.WriteLine(GenUtil.GetCenterAddr("LY_PS_Sales REPORT",des.Length));
* sw.WriteLine(GenUtil.GetCenterAddr("====================",des.Length));
*
* sw.WriteLine("+------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+");
* sw.WriteLine("|Month | Primary Sales | Secondary Sales |");
* sw.WriteLine("+------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+");
* sw.WriteLine("| | Total | Pur | Gen |Greases| RO1 | RO2 | RO3 | RO4 | RO5 | IBP |Bazzar | OE | Fleet |Maruti |Eicher |Hyundai| Total |");
* sw.WriteLine("| | Pur | FOC | Oils | | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | | | | | | | | Sales |");
* sw.WriteLine("+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+");
* // 123456 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567
*/
if (rdr.HasRows)
{
sw.WriteLine(GenUtil.GetCenterAddr("====================", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("LY_PS_Sales REPORT", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("====================", des.Length));
sw.WriteLine("+------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+");
sw.WriteLine("|Month | Primary Sales | Secondary Sales |");
sw.WriteLine("+------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+");
sw.WriteLine("| | Total | Pur | Gen |Greases| RO1 | RO2 | RO3 | RO4 | RO5 | IBP |Bazzar | OE | Fleet |Maruti |Eicher |Hyundai| Total |");
sw.WriteLine("| | Pur | FOC | Oils | | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | | | | | | | | Sales |");
sw.WriteLine("+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+");
// 123456 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567
int i = 0;
info = " {0,-6:S} {1,7:F} {2,7:S} {3,7:S} {4,7:S} {5,7:S} {6,7:S} {7,7:S} {8,7:S} {9,7:S} {10,7:S} {11,7:S} {12,7:S} {13,7:S} {14,7:S} {15,7:S} {16,7:S} {17,7:S} {18,7:S} {19,7:S} {20,7:S} {21,7:S} {22,7:S}";
while (rdr.Read())
{
if (i < 12)
{
sw.WriteLine(info, rdr.GetValue(1).ToString(),
rdr.GetValue(2).ToString(),
rdr.GetValue(3).ToString(),
rdr.GetValue(4).ToString(),
rdr.GetValue(5).ToString(),
rdr.GetValue(6).ToString(),
rdr.GetValue(7).ToString(),
rdr.GetValue(8).ToString(),
rdr.GetValue(9).ToString(),
rdr.GetValue(10).ToString(),
rdr.GetValue(11).ToString(),
rdr.GetValue(12).ToString(),
rdr.GetValue(13).ToString(),
rdr.GetValue(14).ToString(),
rdr.GetValue(15).ToString(),
rdr.GetValue(16).ToString(),
rdr.GetValue(17).ToString(),
rdr.GetValue(18).ToString(),
rdr.GetValue(19).ToString(),
rdr.GetValue(20).ToString(),
rdr.GetValue(21).ToString(),
rdr.GetValue(22).ToString(),
rdr.GetValue(23).ToString()
);
}
else
{
sw.WriteLine("+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+");
sw.WriteLine(info, rdr.GetValue(1).ToString(),
rdr.GetValue(2).ToString(),
rdr.GetValue(3).ToString(),
rdr.GetValue(4).ToString(),
rdr.GetValue(5).ToString(),
rdr.GetValue(6).ToString(),
rdr.GetValue(7).ToString(),
rdr.GetValue(8).ToString(),
rdr.GetValue(9).ToString(),
rdr.GetValue(10).ToString(),
rdr.GetValue(11).ToString(),
rdr.GetValue(12).ToString(),
rdr.GetValue(13).ToString(),
rdr.GetValue(14).ToString(),
rdr.GetValue(15).ToString(),
rdr.GetValue(16).ToString(),
rdr.GetValue(17).ToString(),
rdr.GetValue(18).ToString(),
rdr.GetValue(19).ToString(),
rdr.GetValue(20).ToString(),
rdr.GetValue(21).ToString(),
rdr.GetValue(22).ToString(),
rdr.GetValue(23).ToString()
);
sw.WriteLine("+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+");
}
i++;
}
}
else
{
MessageBox.Show("Data Not Available");
return;
}
//sw.WriteLine("+------+-------+-------+-------+-------+----------+----------+----------+----------+----------+-----+-----+-----+-----+-----+-----+-----+");
dbobj.Dispose();
sw.Close();
}
/// <summary>
/// [To be supplied.]
/// </summary>
/// <returns>[To be supplied.]</returns>
public override string ToString()
{
if (!this.recordWasLoadedFromDB)
{
throw new ArgumentException("No record was loaded from the database. The DisplayName is not available.");
}
if (this.displayName == null)
{
bool alreadyOpened = false;
Params.spS_Customers_Display Param = new Params.spS_Customers_Display(true);
switch (this.lastKnownConnectionType)
{
case Bob.DataClasses.ConnectionType.ConnectionString:
Param.SetUpConnection(this.connectionString);
break;
case Bob.DataClasses.ConnectionType.SqlConnection:
Param.SetUpConnection(this.sqlConnection);
alreadyOpened = (this.sqlConnection.State == System.Data.ConnectionState.Open);
break;
case Bob.DataClasses.ConnectionType.SqlTransaction:
Param.SetUpConnection(this.sqlTransaction);
break;
}
Param.Param_CustomerID = this.col_CustomerID;
System.Data.SqlClient.SqlDataReader sqlDataReader = null;
SPs.spS_Customers_Display Sp = new SPs.spS_Customers_Display(false);
if (Sp.Execute(ref Param, out sqlDataReader))
{
if (sqlDataReader.Read())
{
if (!sqlDataReader.IsDBNull(SPs.spS_Customers_Display.Resultset1.Fields.Column_Display.ColumnIndex))
{
if (sqlDataReader.GetFieldType(SPs.spS_Customers_Display.Resultset1.Fields.Column_Display.ColumnIndex) == typeof(string))
{
this.displayName = sqlDataReader.GetString(SPs.spS_Customers_Display.Resultset1.Fields.Column_Display.ColumnIndex);
}
else
{
this.displayName = sqlDataReader.GetValue(SPs.spS_Customers_Display.Resultset1.Fields.Column_Display.ColumnIndex).ToString();
}
}
}
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
CloseConnection(Sp.Connection, alreadyOpened);
}
else
{
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
CloseConnection(Sp.Connection, alreadyOpened);
throw new Bob.DataClasses.CustomException(Param, "Bob.BusinessComponents.Customer", "ToString");
}
}
return(this.displayName);
}
/// <summary>
/// This method return the calculate the purchase discount.
/// </summary>
public double discountpurchase(string invoice)
{
double fixedDisc = 0;
double focDisc = 0;
double CashDisc = 0;
double Disc = 0;
System.Data.SqlClient.SqlDataReader rdr = null;
string sql = "select * from purchase_master where invoice_no='" + invoice + "'";
// Calls the sp_stockmovement for each product and create one stkmv temp. table.
dbobj.SelectQuery(sql, ref rdr);
if (rdr.Read())
{
double grandtot = System.Convert.ToDouble(rdr.GetValue(7).ToString());
//fixedDisc=System.Convert.ToDouble(rdr.GetValue(26).ToString());
fixedDisc = System.Convert.ToDouble(rdr.GetValue(27).ToString());
//if(rdr.GetValue(27).ToString().Equals("Per"))
// fixedDisc=grandtot*fixedDisc/100;
focDisc = System.Convert.ToDouble(rdr.GetValue(24).ToString());
if (rdr.GetValue(25).ToString().Equals("Per"))
{
focDisc = grandtot * focDisc / 100;
}
double ETFOC = (focDisc * 2) / 100;
Disc = System.Convert.ToDouble(rdr.GetValue(8).ToString());
if (rdr.GetValue(9).ToString().Equals("Per"))
{
// double Dt=System.Convert.ToDouble(rdr.GetValue(7).ToString());
Disc = grandtot * Disc / 100;
}
double etax = System.Convert.ToDouble(rdr.GetValue(22).ToString());
if (rdr.GetValue(23).ToString().Equals("Per"))
{
// double Dt=System.Convert.ToDouble(rdr.GetValue(7).ToString());
etax = grandtot * etax / 100;
}
CashDisc = System.Convert.ToDouble(rdr.GetValue(15).ToString());
double GT = 0;
if (rdr.GetValue(16).ToString().Equals("Per"))
{
GT = grandtot + etax - (System.Convert.ToDouble(rdr.GetValue(21).ToString()) + System.Convert.ToDouble(rdr.GetValue(19).ToString()) + focDisc + Disc + fixedDisc + ETFOC);
CashDisc = GT * CashDisc / 100;
}
//document.Form1.txtVatValue.value = eval(document.Form1.txtGrandTotal.value) + eval(Et)-((eval(tradeDisc)-eval(tradeless))+eval(focDisc)+(eval(bird)-eval(birdless))+eval(CashDisc)+eval(Disc))
}
rdr.Close();
Cache["focDisc"] = focDisc;
focDisctotal += focDisc;
//Cache["focDisctotal"]=Math.Round(focDisctotal,2);
Cache["focDisctotal"] = GenUtil.strNumericFormat(focDisctotal.ToString());
Cache["fixed"] = fixedDisc;
fixedDisctotal += fixedDisc;
//Cache["fixedDisctotal"]=Math.Round(fixedDisctotal,2);
Cache["fixedDisctotal"] = GenUtil.strNumericFormat(fixedDisctotal.ToString());
Cache["Disc"] = Disc;
Disctotal += Disc;
//Cache["Disctotal"]=Math.Round(Disctotal,2);
Cache["Disctotal"] = GenUtil.strNumericFormat(Disctotal.ToString());
CashDisctotal += CashDisc;
//Cache["CashDisctotal"]=Math.Round(CashDisctotal,2);
Cache["CashDisctotal"] = GenUtil.strNumericFormat(CashDisctotal.ToString());
//return System.Convert.ToDouble(Math.Round(CashDisc,2));
return(System.Convert.ToDouble(GenUtil.strNumericFormat(CashDisc.ToString())));
}
/// <summary>
/// An infinitely useful way to lookup a value in a database. It builds a query, submits, it, and captures the results.
/// </summary>
/// <param name="pTarget">The column to return</param>
/// <param name="pDomain">The table where the column is</param>
/// <param name="pCriteria">The filter to apply. It's everything but the WHERE keyword</param>
/// <param name="pAggregate">The GROUP BY clause, without the GROUP BY keyword</param>
/// <returns>The value in pTarget after the query is executed</returns>
public static object MyDLookup(String pTarget, String pDomain, String pCriteria, String pAggregate)
{
String criteria;
//bool keepGoing;
String aggregate;
String asName;
object result = null;
System.Data.SqlClient.SqlDataReader reader = null;
if (pAggregate.Trim().Length != 0)
{
aggregate = pAggregate; // MAX, MIN, etc.
asName = "foo"; // We need a unique name because this is a calculated field
}
else
{
aggregate = "";
asName = "foo"; // We can't use pTarget here because it causes an SQL "Circular Reference" error
}
// If the domain is a select statement, don't do anything to it. We can't perform an agregate function on SQL.
// Whoever calls this function should configure the SQL to have the desired record at the top of the cursor
// before calling this function.
if ((pDomain.Length > 7) && (pDomain.Substring(1, 6) == "SELECT"))
{
criteria = pDomain;
asName = pTarget;
}
else
{
criteria = "SELECT " + aggregate + "(" + pTarget + ") AS " + asName + " FROM " + pDomain;
if (pCriteria == "")
{
}
else
{
criteria = criteria + " WHERE " + pCriteria;
}
criteria = criteria + ";";
}
//keepGoing = true;
try {
//MySqlCommand command = new MySqlCommand(criteria, myConnection);
try { Config.myConnection.Close(); } catch (Exception e) { }
CheckConnection();
System.Data.SqlClient.SqlCommand command = Config.myConnection.CreateCommand();
command.CommandText = criteria;
reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (reader.HasRows)
{
reader.Read(); // Get the first row. We always assume it's the first row, in this function
result = reader.GetValue(0);
}
else
{
result = null;
}
reader.Close();
} catch (Exception e) {
Console.WriteLine(e.Message);
result = null; // null means we failed
try { reader.Close(); } catch (Exception ex) { }
}
return(result);
}
static public Dictionary <string, object> ToDataRow(this System.Data.SqlClient.SqlDataReader me)
{
string _message = string.Format("ToDictionary Call Failed");
try {
return(Enumerable.Range(0, me.FieldCount).ToDictionary(field => me.GetName(field).ToString(), field => me.GetValue(field)));
}
catch (Exception ex) {
// err.WriteError(_message, ProcessHelper.GetProcessName(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType), ex);
}
return(new Dictionary <string, object>());
}
